R12客户表结构分析
客户表/联系人/PARTY关联
HZ_PARTIES
客户账户表
HZ_CUST_ACCOUNTS
例子：
HZ_PARTIES
客户账户表
HZ_CUST_ACCOUNTS
例子：
select hp.party_number --客户注册标识
,
hp.party_name --组织名/客户
,
hp.known_as --别名
,
hp.organization_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_sale.language = userenv('LANG')
and acc.customer_class_code = flv_customer.lookup_code
and flv_customer.lookup_type = 'CUSTOMER CLASS'
and flv_customer.language = userenv('LANG')
and acc.status = flv_status.lookup_code
and flv_status.lookup_type = 'HZ_CPUI_REGISTRY_STATUS'
and flv_status.language = userenv('LANG')
and acc.customer_type = flv_type.lookup_code
and flv_type.lookup_type = 'CUSTOMER_TYPE'
and flv_type.language = userenv('LANG')
and hp.party_id = hz_parties.party_id;
,
hp.party_name --组织名/客户
,
hp.known_as --别名
,
hp.organization_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_sale.language = userenv('LANG')
and acc.customer_class_code = flv_customer.lookup_code
and flv_customer.lookup_type = 'CUSTOMER CLASS'
and flv_customer.language = userenv('LANG')
and acc.status = flv_status.lookup_code
and flv_status.lookup_type = 'HZ_CPUI_REGISTRY_STATUS'
and flv_status.language = userenv('LANG')
and acc.customer_type = flv_type.lookup_code
and flv_type.lookup_type = 'CUSTOMER_TYPE'
and flv_type.language = 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;
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
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 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 org.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 para.organization_id = oe_order_headers_all.ship_from_org_id;
select *
from org_organization_definitions org
where org.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 --注册表标识
,
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 flv.language = 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 = :oe_order_headers_all.sold_to_contact_id
and hpsub.status = 'A';
from hr_organization_units org
where org.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 para.organization_id = oe_order_headers_all.ship_from_org_id;
select *
from org_organization_definitions org
where org.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 --注册表标识
,
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 flv.language = 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 = :oe_order_headers_all.sold_to_contact_id
and hpsub.status = 'A';
