R12客户表结构分析

客户表/联系人/PARTY关联
   HZ_PARTIES

客户账户表
   HZ_CUST_ACCOUNTS

例子：

				<!--



Code highlighting produced by Actipro CodeHighlighter (freeware)

http://www.CodeHighlighter.com/



-->   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;
			

帐户配置文件
   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
   以头层的联系人为例

					<!--



Code highlighting produced by Actipro CodeHighlighter (freeware)

http://www.CodeHighlighter.com/



-->   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
   例子：
   客户头层

						<!--



Code highlighting produced by Actipro CodeHighlighter (freeware)

http://www.CodeHighlighter.com/



-->   

   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;
					

						<!--



Code highlighting produced by Actipro CodeHighlighter (freeware)

http://www.CodeHighlighter.com/



-->   --客户地点层

   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

								<!--



Code highlighting produced by Actipro CodeHighlighter (freeware)

http://www.CodeHighlighter.com/



-->--由销售订单分析客户结构

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;--业务实体 
							

								<!--



Code highlighting produced by Actipro CodeHighlighter (freeware)

http://www.CodeHighlighter.com/



-->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';