OracleEBSR12客户表结构
R12 客户表结构
客户表/联系⼈/PARTY关联
HZ_PARTIES
客户账户表
HZ_CUST_ACCOUNTS
例⼦：
SELECT hp.party_number --客户注册标识
, hp.party_name --组织名/客户
, hp.known_as --别名
,
hpanization_name_phonetic --名称拼⾳
, acc.account_number --帐号
, aning sales_channel_code --销售渠道
, acc.account_name --账记说明
, aning customer_class_code --分类
, ig_system_reference --参考
, aning status --状态
, aning customer_type --账户类型
, acc.attribute_category --上下⽂
, acc.attribute1 --注册
, acc.attribute2 --⼈员推⼴
,
acc.attribute3 --特殊要求
我的初三生活, acc.Attribute4 --发货单是否打印价格
, acc.Attribute5 --所属利润
FROM hz_parties hp
, hz_cust_accounts acc
, fnd_lookup_values flv_sale --销售渠道
, fnd_lookup_values flv_customer --分类
, fnd_lookup_values flv_status --状态
, fnd_lookup_values flv_type --账户类型
WHERE hp.party_id = acc.party_id
中国贪官录AND acc.sales_channel_code = flv_sale.lookup_code
AND flv_sale.lookup_type ='SALES_CHANNEL'
AND flv_sale.LANGUAGE = urenv('LANG')
AND acc.customer_class_code = flv_customer.lookup_code
AND flv_customer.lookup_type ='CUSTOMER CLASS'
AND flv_customer.LANGUAGE = urenv('LANG')
AND acc.status = flv_status.lookup_code
AND flv_status.lookup_type ='HZ_CPUI_REGISTRY_STATUS'
AND flv_status.LANGUAGE = urenv('LANG')
AND acc.customer_type = flv_type.lookup_code
AND flv_type.lookup_type ='CUSTOMER_TYPE'
AND flv_type.LANGUAGE = urenv('LANG')
AND hp.party_id = hz_parties.party_id;
帐户配置⽂件
HZ_CUSTOMER_PROFILES
字段
cust_account_role_id --oe_order_headers.sold_to_contract_id
cust_account_id
site_u_id --客户头的该字段为空
--客户地点层为hz_cust_site_us_all.site_u_id
配置⽂件⾦额
HZ_CUST_PROFILE_AMTS --客户头层/客户地点层
关联：hz_customer_profiles.cust_account_profile_id
客户联系⼈
HZ_CUST_ACCOUNT_ROLES --客户头层/地点层
cust_account_id
cust_acct_site_id --头层该字段为空
party_id --类型为 PARTY_RELATIONSHIP 的 PARTY_ID
role_type --CONTACT
以头层的联系⼈为例
SELECT hp_per.*
FROM hz_cust_account_roles rol
, hz_parties hp_rel
, hz_relationships rel
,
hz_parties hp_per
WHERE rol.party_id = hp_rel.party_id
AND hp_rel.party_id = rel.party_id
AND rel.object_type ='PERSON'
lationship_code ='CONTACT'
AND rel.object_id= hp_per.party_id
AND rol.cust_acct_site_id IS NULL--头层
AND rol.cust_account_id = hz_cust_accounts.cust_account_id;
联系⽅式
HZ_CONTACT_POINTS
字段
owner_table_name HZ_PARTIES/HZ_PARTY_SITES
owner_table_id PARTY_ID/PARTY_SITE_ID
客户地点层的联系⽅式，直接⽤party_site_id 关联 owner_table_id 即可
客户头层的联系⽅式，要⽤ HZ_RELATIONSHIPS 表转换⼀下，与 hz_relationships. Party_id 关联客户联系⼈下⾯的联系⽅式，要⽤HZ_CUST_ACCOUNT_ROLES的PARTY_ID关联owner_table_id 例⼦：
客户头层
SELECT con.*
FROM hz_parties hp
, hz_relationships rel
, hz_contact_points con
WHERE hp.party_id = rel.subject_id
AND rel.subject_type ='ORGANIZATION'
AND rel.party_id = con.owner_table_id
AND con.owner_table_name ='HZ_PARTIES'
AND hp.party_id = hz_parties.party_id;
客户地点层
SELECT*
FROM hz_contact_points con余光中情诗
WHERE con.owner_table_id = hz_party_sites.party_site_id;
客户联系⼈下的联系⽅式
SELECT*
FROM hz_contact_points c
WHERE c.owner_table_id = hz_cust_account_roles.party_id
客户的税
HZ_CODE_ASSIGNMENTS 会计分类/客户头层/地点层
字段
OWNER_TABLE_NAME 关联表名/'ZX_PARTY_TAX_PROFILE'
OWNER_TABLE_ID 关联表主键/PARTY_TAX_PROFILE_ID
CLASS_CODE 会计分类代码
ZX_PARTY_TAX_PROFILE 供应商的税的配置⽂件
字段
PARTY_TYPE_CODE 类型 THIRD_PARTY/THIRD_PARTY_SITE
PARTY_ID 关联表 HZ_PARTIES/HZ_PARTY_SITES
头层: PARTY_TYPE_CODE ='THIRD_PARTY'
AND PARTY_ID = HZ_PARTIES.PARTY_ID
地点层: PARTY_TYPE_CODE ='THIRD_PARTY_SITE'
AND PARTY_ID = HZ_PARTY_SITES.PARTY_SITE_ID
REP_REGISTRATION_NUMBER 纳税登记编号
PARTY_TAX_PROFILE_ID 主键
HZ_CLASS_CODE_DENORM 会计分类描述
ZX_EXEMPTIONS 客户免税/客户头层/地点层
字段
大通湖PARTY_TAX_PROFILE_ID 关联 ZX_PARTY_TAX_PROFILE.PARTY_TAX_PROFILE_ID
客户地点
芝士火腿HZ_PARTY_SITES
地点地址
HZ_LOCATIONS
客户地点帐户表
HZ_CUST_ACCT_SITES_ALL
客户地点业务⽬的
HZ_CUST_SITE_USES_ALL
滞纳费⽤
HZ_CUSTOMER_PROFILES
由销售订单分析客户结构
SELECT h.sold_from_org_id --业务实体/ORG ID
,
h.sold_to_org_id --客户
, h.ship_from_org_id --发货仓库
, h.ship_to_org_id --收货⽅
, h.invoice_to_org_id
, h.sold_to_contact_id
FROM oe_order_headers_all h ;
--业务实体
SELECT org.NAME
FROM hr_organization_units org
WHERE organization_id = oe_order_headers_all.sold_from_org_id;
--客户想歌词
SELECT hz.party_name
FROM hz_cust_accounts acc
, hz_parties hz
WHERE acc.party_id = hz.party_id
AND acc.cust_account_id = oe_order_headers_all.sold_to_org_id;
--发货仓库
SELECT para.Organization_Code,para.*
FROM mtl_parameters para
WHERE paraanization_id = oe_order_headers_all.ship_from_org_id; SELECT*
马仁奇峰风景区
FROM org_organization_definitions org
WHERE organization_id = oe_order_headers_all.ship_from_org_id;
-
-地点详细信息
SELECT loc.*
FROM hz_parties hp
, hz_party_sites hps
, hz_locations loc
WHERE hp.party_id = hps.party_id
AND hps.location_id = loc.location_id
AND hp.party_id =5042;
--业务⽬的
SELECT hp.party_name --客户
, hp.party_number --注册表标识
,
us.site_u_code
, acnt.account_number --账号
, aning businesspurpo --业务⽬的
, us.location --地点
, acnt.account_name --帐户说明
, decode(loc.address1,NULL,loc.address1,loc.address1 ||',') ||
decode(loc.city,NULL,loc.city,loc.city ||',') ||
decode(loc.state,NULL,loc.state,loc.state ||',') ||
decode(loc.postal_code,NULL,'',loc.postal_code) address --地点地址
, hps.party_site_number --地点说明
, us.payment_term_id --付款条件
,
site.cust_acct_site_id
, acnt.cust_account_id
, us.site_u_id
FROM hz_parties hp
, hz_cust_accounts acnt
, hz_cust_acct_sites_all site
, hz_cust_site_us_all us
, hz_party_sites hps
, hz_locations loc
, fnd_lookup_values flv
WHERE hp.party_id = acnt.party_id
AND acnt.cust_account_id = site.cust_account_id
AND site.cust_acct_site_id = us.cust_acct_site_id
清洗AND hps.party_site_id = site.party_site_id
AND loc.location_id = hps.location_id
AND us.site_u_code = flv.lookup_code
AND flv.lookup_type ='SITE_USE_CODE'
AND flv.LANGUAGE = urenv('LANG')
AND hp.party_id =5042
AND hps.party_site_id =3023;
--联系⼈电话/地点层
SELECT phone.phone_number
FROM hz_contact_points phone
WHERE phone.owner_table_name ='HZ_PARTY_SITES'
AND phone.owner_table_id = :hz_party_sites.party_sites_id
--联系⼈/地点层
SELECT hpsub.party_name
FROM hz_cust_account_roles hcar
, hz_relationships hr
, hz_parties hpsub
WHERE hcar.party_id = hr.party_id
AND hr.subject_id = hpsub.party_id
le_type ='CONTACT'
AND hr.directional_flag ='F'
AND hcar.cust_account_role_id = e_order_headers_all.sold_to_contact_id AND hpsub.status ='A';
View Code
