Oracle_EBS_R12_客户表结构

R12 客户表结构

客户表/联系人/PARTY关联

HZ_PARTIES

客户账户表

HZ_CUST_ACCOUNTS

例子：

SELECT hp.party_number --客户注册标识

, hp.party_name --组织名/客户

, hp.known_as --别名

, https://www.360docs.net/doc/8518100959.html,anization_name_phonetic --名称拼音

, acc.account_number --帐号

, flv_sale.meaning sales_channel_code --销售渠道

, acc.account_name --账记说明

, flv_customer.meaning customer_class_code --分类

, acc.orig_system_reference --参考

, flv_status.meaning status --状态

, flv_type.meaning 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_https://www.360docs.net/doc/8518100959.html,NGUAGE = userenv('LANG')

AND acc.customer_class_code = flv_customer.lookup_code

AND flv_customer.lookup_type = 'CUSTOMER CLASS'

AND flv_https://www.360docs.net/doc/8518100959.html,NGUAGE = userenv('LANG')

AND acc.status = flv_status.lookup_code

AND flv_status.lookup_type = 'HZ_CPUI_REGISTRY_STATUS' AND flv_https://www.360docs.net/doc/8518100959.html,NGUAGE = userenv('LANG')

AND acc.customer_type = flv_type.lookup_code

AND flv_type.lookup_type = 'CUSTOMER_TYPE'

AND flv_https://www.360docs.net/doc/8518100959.html,NGUAGE = userenv('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_use_id --客户头的该字段为空

--客户地点层为hz_cust_site_uses_all.site_use_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'

AND rel.relationship_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 https://www.360docs.net/doc/8518100959.html,

FROM hr_organization_units org

WHERE https://www.360docs.net/doc/8518100959.html,anization_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 https://www.360docs.net/doc/8518100959.html,anization_Code,para.*

FROM mtl_parameters para

WHERE https://www.360docs.net/doc/8518100959.html,anization_id = oe_order_headers_all.ship_from_org_id; SELECT *

FROM org_organization_definitions org

WHERE https://www.360docs.net/doc/8518100959.html,anization_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 --注册表标识

, uses.site_use_code

, acnt.account_number --账号

, flv.meaning businesspurpose --业务目的

, uses.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 --地点说明

, uses.payment_term_id --付款条件

, site.cust_acct_site_id

, acnt.cust_account_id

, uses.site_use_id

FROM hz_parties hp

, hz_cust_accounts acnt

, hz_cust_acct_sites_all site

, hz_cust_site_uses_all uses

, 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 = uses.cust_acct_site_id

AND hps.party_site_id = site.party_site_id

AND loc.location_id = hps.location_id

AND uses.site_use_code = flv.lookup_code

AND flv.lookup_type = 'SITE_USE_CODE'

AND https://www.360docs.net/doc/8518100959.html,NGUAGE = userenv('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

AND hcar.role_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';