---Customer Party
SELECT * FROM hz_parties
---Customer Accounts (Customer Party --> Customer Account)
SELECT * FROM HZ_CUST_ACCOUNTS
/**---Join with Customer Party ***/
SELECT
HP.PARTY_ID,
HP.PARTY_NUMBER,
HP.PARTY_NAME,
HP.PARTY_TYPE,
HCA.ACCOUNT_NAME ,
HCA.ACCOUNT_NUMBER ,
HCA.ACCOUNT_TERMINATION_DATE ,
HCA.STATUS
FROM HZ_CUST_ACCOUNTS HCA, HZ_PARTIES HP
WHERE 1=1
AND HP.PARTY_ID = HCA.PARTY_ID
---Customer Account Sites (Customer Party --> Customer Account-->Customer Account Sites)
SELECT * FROM HZ_CUST_ACCT_SITES_ALL
/*Join between Customer Parties, Accounts and Sites **/
SELECT
HP.PARTY_ID,
HP.PARTY_NUMBER,
HP.PARTY_NAME,
HP.PARTY_TYPE,
HCA.ACCOUNT_NAME ,
HCA.ACCOUNT_NUMBER ,
HCA.ACCOUNT_TERMINATION_DATE ,
HCA.STATUS ,
HPS.PARTY_SITE_NUMBER ,
HPS.PARTY_SITE_NAME ,
HCASA.CUSTOMER_CATEGORY_CODE
FROM HZ_CUST_ACCOUNTS HCA, HZ_PARTIES HP , HZ_PARTY_SITES HPS, HZ_CUST_ACCT_SITES_ALL HCASA
WHERE 1=1
AND HP.PARTY_ID = HCA.PARTY_ID
AND HCASA.CUST_ACCOUNT_ID = HCA.CUST_ACCOUNT_ID
AND HPS.PARTY_SITE_ID = HCASA.PARTY_SITE_ID
---Customer Account Sites (Customer Party --> Customer Account-->Customer Account Sites--> Customer Account Site Uses All)
SELECT * FROM HZ_CUST_SITE_USES_ALL
/*Join between Customer Parties, Accounts, Sites and Site Uses **/
SELECT
HP.PARTY_ID,
HP.PARTY_NUMBER,
HP.PARTY_NAME,
HP.PARTY_TYPE,
HCA.ACCOUNT_NAME ,
HCA.ACCOUNT_NUMBER ,
HCA.ACCOUNT_TERMINATION_DATE ,
HCA.STATUS ,
HPS.PARTY_SITE_NUMBER ,
HPS.PARTY_SITE_NAME ,
HCASA.CUSTOMER_CATEGORY_CODE ,
HCSUA.PRIMARY_FLAG,
HCSUA.SITE_USE_CODE,
HCSUA.LOCATION SITE_USE_NO
FROM HZ_CUST_ACCOUNTS HCA, HZ_PARTIES HP , HZ_PARTY_SITES HPS, HZ_CUST_ACCT_SITES_ALL HCASA , HZ_CUST_SITE_USES_ALL HCSUA
WHERE 1=1
AND HP.PARTY_ID = HCA.PARTY_ID
AND HCASA.CUST_ACCOUNT_ID = HCA.CUST_ACCOUNT_ID
AND HPS.PARTY_SITE_ID = HCASA.PARTY_SITE_ID
AND HCASA.CUST_ACCT_SITE_ID = HCSUA.CUST_ACCT_SITE_ID