R12客户关系

客户表/联系人/PARTY关联
 

  HZ_PARTIES

客户账户表
   HZ_CUST_ACCOUNTS

例子：
   SELECThp.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.meaningcustomer_type       --账户类型
       ,acc.attribute_category               --上下文
       ,acc.attribute1                       --注册
       ,acc.attribute2                       --人员推广
       ,acc.attribute3                       --特殊要求
       ,acc.Attribute4                       --发货单是否打印价格
       ,acc.Attribute5                       --所属利润
    FROMhz_parties       hp
       , hz_cust_accounts  acc
       , fnd_lookup_valuesflv_sale     --销售渠道
       , fnd_lookup_values flv_customer  --分类
       , fnd_lookup_valuesflv_status   --状态
       , fnd_lookup_valuesflv_type     --账户类型
    WHEREhp.party_id             = acc.party_id
     AND acc.sales_channel_code   =flv_sale.lookup_code
     ANDflv_sale.lookup_type    = 'SALES_CHANNEL'
     ANDflv_sale.LANGUAGE       = userenv('LANG')
     AND acc.customer_class_code  =flv_customer.lookup_code
     AND flv_customer.lookup_type = 'CUSTOMER CLASS'
     ANDflv_customer.LANGUAGE   = userenv('LANG')
     ANDacc.status              = flv_status.lookup_code
     AND flv_status.lookup_type   ='HZ_CPUI_REGISTRY_STATUS'
     ANDflv_status.LANGUAGE     = userenv('LANG')
     ANDacc.customer_type       = flv_type.lookup_code
     ANDflv_type.lookup_type    = 'CUSTOMER_TYPE'
     ANDflv_type.LANGUAGE       = userenv('LANG')
     ANDhp.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
    WHERErol.party_id         = hp_rel.party_id
     ANDhp_rel.party_id      = rel.party_id
     ANDrel.object_type      = 'PERSON'
     AND rel.relationship_code = 'CONTACT'
     ANDrel.object_id        = hp_per.party_id
     AND rol.cust_acct_site_id ISNULL             --头层
     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.*
    FROMhz_parties       hp
       , hz_relationships  rel
       , hz_contact_points con
    WHEREhp.party_id         = rel.subject_id
     ANDrel.subject_type    = 'ORGANIZATION'
     ANDrel.party_id        = con.owner_table_id
     AND con.owner_table_name = 'HZ_PARTIES'
     ANDhp.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
    WHEREc.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

由销售订单分析客户结构
SELECTh.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.*
  FROMhz_parties    hp
    , hz_party_sites hps
    , hz_locations   loc
WHEREhp.party_id    = hps.party_id
   AND hps.location_id =loc.location_id
   ANDhp.party_id    = 5042;

--业务目的
SELECThp.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      
  FROMhz_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
WHEREhp.party_id           = acnt.party_id
   ANDacnt.cust_account_id   =site.cust_account_id
   AND site.cust_acct_site_id =uses.cust_acct_site_id
   ANDhps.party_site_id     = site.party_site_id
   ANDloc.location_id       = hps.location_id
   ANDuses.site_use_code    = flv.lookup_code
   ANDflv.lookup_type       = 'SITE_USE_CODE'
   ANDflv.LANGUAGE          = userenv('LANG')
   ANDhp.party_id           = 5042
   ANDhps.party_site_id     = 3023;

--联系人电话/地点层
SELECT phone.phone_number
  FROM hz_contact_points phone
WHERE phone.owner_table_name = 'HZ_PARTY_SITES'
   ANDphone.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
WHEREhcar.party_id            = hr.party_id
   ANDhr.subject_id            = hpsub.party_id
   ANDhcar.role_type           = 'CONTACT'
   ANDhr.directional_flag      = 'F'
   AND hcar.cust_account_role_id= oe_order_headers_all.sold_to_contact_id
   ANDhpsub.status             = 'A';