Protected by Copyscape Web Copyright Protection Software

Search This Blog

Thursday, May 30, 2013

How to get Customer Address Details in Oracle apps?

The following Query will provide the customer address details in oracle applications.

SELECT  hca.account_number customer_number,
                hp.party_name customer_name,
                hps.party_site_number site_number, hl.address1 address1,
                hl.address2 address2, hl.address3 address3,
                hl.address4 address4, hl.city city,
                hl.postal_code postal_code, hl.state state,
                ftt.territory_short_name country,
                hcsua1.LOCATION bill_to_location,
                hcsua2.LOCATION ship_to_location
  FROM hz_parties hp,
                hz_party_sites hps,
                hz_cust_accounts hca,
                hz_cust_acct_sites_all hcasa1,
                hz_cust_site_uses_all hcsua1,
                hz_locations hl,
                fnd_territories_tl ftt,
                hz_cust_acct_sites_all hcasa2,
                hz_cust_site_uses_all hcsua2
  WHERE hp.party_id = hps.party_id(+)
       AND hp.party_id = hca.party_id(+)
       AND hcasa1.party_site_id(+) = hps.party_site_id
       AND hcasa2.party_site_id(+) = hps.party_site_id
       AND hcsua1.cust_acct_site_id(+) = hcasa1.cust_acct_site_id
       AND hcsua2.cust_acct_site_id(+) = hcasa2.cust_acct_site_id
       AND hcsua1.site_use_code(+) = 'bill_to'
       AND hcsua2.site_use_code(+) = 'ship_to'
       AND hcasa1.org_id(+) = fnd_profile.VALUE ('org_id')
       AND hcasa2.org_id(+) = fnd_profile.VALUE ('org_id')
       AND hps.location_id = hl.location_id
       AND hl.country = ftt.territory_code
       AND ftt.LANGUAGE = USERENV ('lang')
  ORDER BY customer_number;