取客户信息SQL

--客户信息  
SELECT hou.name 业务实体
      ,hca.account_number 客户编号
      ,hca.cust_account_id
      ,hp.party_name 客户名称
      ,arp_addr_pkg.format_address(hl.address_style
                                  ,hl.address1
                                  ,hl.address2
                                  ,hl.address3
                                  ,hl.address4
                                  ,hl.city
                                  ,hl.county
                                  ,hl.state
                                  ,hl.province
                                  ,hl.postal_code
                                  ,ftv.territory_short_name) 地址
      ,flv.meaning 用途
      ,gcc_rec.concatenated_segments 应收帐户
      ,gcc_rev.concatenated_segments 收入帐户
FROM   hz_parties               hp
      ,hz_party_sites           hps
      ,hz_locations             hl
      ,hz_cust_accounts         hca
      ,hz_cust_acct_sites_all   hcas
      ,hz_cust_site_uses_all    hcs
      ,fnd_lookup_values        flv
      ,fnd_territories_vl       ftv
      ,gl_code_combinations_kfv gcc_rev
      ,gl_code_combinations_kfv gcc_rec
      ,hr_operating_units       hou
WHERE  hp.party_id = hca.party_id
AND    hp.party_id = hps.party_id
AND    hps.location_id = hl.location_id
AND    hca.cust_account_id = hcas.cust_account_id
AND    hps.party_site_id = hcas.party_site_id
AND    hl.country = ftv.territory_code(+)
AND    hcas.cust_acct_site_id = hcs.cust_acct_site_id
AND    flv.lookup_code = hcs.site_use_code
AND    hcs.gl_id_rec = gcc_rec.code_combination_id(+)
AND    hcs.gl_id_rev = gcc_rev.code_combination_id(+)
AND    hou.organization_id = hcas.org_id
AND    hca.status = 'A'
AND    hps.status = 'A'
AND    hcs.status = 'A'
AND    flv.lookup_type = 'SITE_USE_CODE'
AND    flv.lookup_code = 'BILL_TO'
AND    flv.language = 'ZHS'
AND    hou.organization_id = 81 --p_org_id  
AND    hca.cust_account_id = 21046 --p_customer_id  
ORDER  BY hou.name
         ,hca.account_number;