获取客户信息SQL

/*取客户信息SQL*/

--客户信息

SELECT hou.name 业务实体,

       hca.account_number 客户编号,

       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 = 1041 --p_customer_id

 ORDER BY 1, 2;
巴特西

获取客户信息SQL

最新文章

热门文章
