Thursday, April 18, 2019

SQL to extract Bill-to Customer Site Number And Ship-to Customer Site Number


a) Bill-To site number:
SELECT fss.SET_CODE,
  fss.SET_NAME, hcsu.location -- Site number shown in AR transaction page
FROM hz_cust_accounts hca,
hz_cust_acct_sites_all hcsa,
hz_cust_site_uses_all hcsu,
FND_SETID_SETS fss
WHERE hca.cust_account_id = hcsa.cust_account_id
AND hcsa.cust_acct_site_id = hcsu.cust_acct_site_id
AND HCSU.SITE_USE_CODE = 'BILL_TO'
and hcsa.set_id = fss.set_id
and hca.account_number = '<Customer Account Number>'
--and hcsu.location='<Site Number>';

b) Ship-To site number:
SELECT fss.SET_CODE,
  fss.SET_NAME, hps.party_site_number -- Ship-To Site number shown in AR transaction page
FROM hz_cust_accounts hca,
hz_cust_acct_sites_all hcsa,
hz_cust_site_uses_all hcsu,
hz_party_sites hps,
FND_SETID_SETS fss
WHERE hca.cust_account_id = hcsa.cust_account_id
AND hcsa.cust_acct_site_id = hcsu.cust_acct_site_id
and hcsa.party_site_id = hps.party_site_id
AND HCSU.SITE_USE_CODE = 'SHIP_TO'
and hcsa.set_id = fss.set_id
and hca.account_number = '<Customer Account Number>'
--and hps.party_site_number='<Site Number>';