AR Customer Tables and their joining in R12

AR Customer Tables and their joining in R12

1. HZ_PARTIES

2. HZ_PARTY_SITES

3. HZ_CUST_ACCOUNTS

4. HZ_CUST_SITE_USES_ALL

5. HZ_CUST_ACCT_SITES_ALL

6. IBY_EXT_BANK_ACCOUNTS

7. IBY_EXTERNAL_PAYERS_ALL

8. IBY_PMT_INSTR_USES_ALL



SELECT   HP.PARTY_ID
,HP.PARTY_NUMBER
,HP.PARTY_NAME
,HP.PARTY_TYPE
,HP.CUSTOMER_KEY
,HP.ADDRESS1||' '||HP.ADDRESS2||'  '||HP.CITY ADDRESS
,HPS.PARTY_SITE_ID
,HPS.PARTY_SITE_NUMBER
,HCA.CUST_ACCOUNT_ID
,HCA.ACCOUNT_NUMBER
,HCA.CUSTOMER_TYPE
,HCA.APPLICATION_ID
,HCASA.CUST_ACCT_SITE_ID
,HCASA.STATUS
,HCASA.ORG_ID
,HCASA.BILL_TO_FLAG
,HCASA.SHIP_TO_FLAG
,HCSUA.SITE_USE_ID
,HCSUA.CUST_ACCT_SITE_ID
,HCSUA.SITE_USE_CODE
,HCSUA.LOCATION A
,HL.LOCATION_ID
,HL.ADDRESS1||'  '||HL.ADDRESS2||' '||HL.ADDRESS3||' '||HL.CITY ADDRESS1
FROM HZ_PARTIES HP
,HZ_PARTY_SITES HPS
,HZ_CUST_ACCOUNTS HCA
,HZ_CUST_ACCT_SITES_ALL HCASA
,HZ_CUST_SITE_USES_ALL HCSUA
,HZ_LOCATIONS HL
WHERE 1=1
AND HP.PARTY_ID = HPS.PARTY_ID
AND HPS.PARTY_ID = HCA.PARTY_ID
AND HPS.PARTY_SITE_ID = HCASA.PARTY_SITE_ID
AND HCA.APPLICATION_ID = HCASA.APPLICATION_ID
AND HCA.CUST_ACCOUNT_ID = HCASA.CUST_ACCOUNT_ID
AND HCASA.CUST_ACCT_SITE_ID = HCSUA.CUST_ACCT_SITE_ID
AND HPS.LOCATION_ID = HL.LOCATION_ID
AND HP.PARTY_NAME = 'BHARAT PVT LTD';



SELECT ACCOUNT.EXT_BANK_ACCOUNT_ID
,ACCOUNT.BRANCH_ID
,ACCOUNT.BANK_ID
,ACCOUNT.BANK_ACCOUNT_NUM
,ACCOUNT.CURRENCY_CODE
,ACC_INSTR.INSTRUMENT_PAYMENT_USE_ID
,ACC_INSTR.PAYMENT_FLOW
,ACC_INSTR.EXT_PMT_PARTY_ID
,ACC_INSTR.PAYMENT_FUNCTION
,ACC_INSTR.ORDER_OF_PREFERENCE
,EXT_PAYER.EXT_PAYER_ID
,EXT_PAYER.ORG_TYPE
,EXT_PAYER.ORG_ID
,EXT_PAYER.BANK_CHARGE_BEARER_CODE
FROM IBY_EXT_BANK_ACCOUNTS   ACCOUNT
  , IBY_PMT_INSTR_USES_ALL  ACC_INSTR
  , IBY_EXTERNAL_PAYERS_ALL EXT_PAYER
  , HZ_CUST_ACCOUNTS HCA
WHERE 1= 1
AND ACCOUNT.EXT_BANK_ACCOUNT_ID = ACC_INSTR.INSTRUMENT_ID
AND ACC_INSTR.EXT_PMT_PARTY_ID  = EXT_PAYER.EXT_PAYER_ID
AND EXT_PAYER.CUST_ACCOUNT_ID   = HCA.CUST_ACCOUNT_ID
AND HCA.CUST_ACCOUNT_ID = &CUST_ACCOUNT_ID;