Hi! This is post will show you important customer tables in Oracle apps along with their joins.
Important Customer tables:
HZ_PARTIES
HZ_CUST_ACCOUNTS
HZ_PARTY_SITES
HZ_CUST_ACCT_SITES_ALL
HZ_CUST_SITE_USES_ALL
HZ_LOCATIONS
Joins:
HZ_PARTIES, HZ_PARTY_SITES - party_id
HZ_PARTIES, HZ_CUST_ACCOUNTS - party_id
HZ_CUST_ACCOUNTS, HZ_CUST_ACCT_SITES_ALL - cust_account_id
HZ_PARTY_SITES, HZ_CUST_ACCT_SITES_ALL - party_site_id
HZ_CUST_ACCT_SITES_ALL, HZ_CUST_SITE_USES_ALL - cust_acct_site_id
HZ_PARTY_SITES, HZ_LOCATIONS - location_id.
Queries to get ship to and bill to address details:
Ship to address:
SELECT hp.PARTY_NAME customer,
hl.ADDRESS1,
hl.ADDRESS2,
hl.CITY,
hl.POSTAL_CODE,
hl.STATE,
hl.PROVINCE,
hl.COUNTRY
FROM hz_parties hp,
hz_cust_accounts hca,
hz_cust_acct_sites_all hcasa,
hz_cust_site_uses_all hcsua,
hz_party_sites hps,
hz_locations hl
WHERE hp.party_id = hca.party_id
AND hca.cust_account_id = hcasa.cust_account_id
AND hcasa.CUST_ACCT_SITE_ID = hcsua.CUST_ACCT_SITE_ID
AND hp.party_id = hps.party_id
AND hps.party_site_id = hcasa.party_site_id
AND hl.location_id = hps.location_id
AND hp.party_name= <party_name>
AND hcsua.site_use_code = 'SHIP_TO'
Bill to address:
SELECT hp.PARTY_NAME customer,
hl.ADDRESS1,
hl.ADDRESS2,
hl.CITY,
hl.POSTAL_CODE,
hl.STATE,
hl.PROVINCE,
hl.COUNTRY
FROM hz_parties hp,
hz_cust_accounts hca,
hz_cust_acct_sites_all hcasa,
hz_cust_site_uses_all hcsua,
hz_party_sites hps,
hz_locations hl
WHERE hp.party_id = hca.party_id
AND hca.cust_account_id = hcasa.cust_account_id
AND hcasa.CUST_ACCT_SITE_ID = hcsua.CUST_ACCT_SITE_ID
AND hp.party_id = hps.party_id
AND hps.party_site_id = hcasa.party_site_id
AND hl.location_id = hps.location_id
AND hp.party_name = <party_name>
AND hcsua.site_use_code = 'BILL_TO';
Hope this helps you. Great learning ahead!!
