Join between ar_cash_receipts_all and ra_customer_trx_all

Join between ar_cash_receipts_all and ra_customer_trx_all

ar_cash_receipts_all tables store the Customer Receipt or we can say payments in oracle apps and ra_customer_trx_all table store the Customer Invoices in the Oracle Apps. When we receipt the Customer Receipts , then we don’t enter any Invoice number for against which AR Invoices we are getting this payment but we apply this payment or receipt to the Customer Invoice to make it close. So the join between  ar_cash_receipts_all and ra_customer_trx_all only works when we apply the Customer Receipt with the Customer Invoice. Here below is the example of the SQL query using Joins between ar_cash_receipts_all and ra_customer_trx_all.

 

Tables Joins between ar_cash_receipts_all and ra_customer_trx_all

AR_CASH_RECEIPTS_ALL   ==>   CASH_RECEIPT_ID

AR_RECEIVABLE_APPLICATIONS_ALL ==> CASH_RECEIPT_ID , APPLIED_CUSTOMER_TRX_ID

RA_CUSTOMER_TRX_ALL ==>  CUSTOMER_TRX_ID join between ar_cash_receipts_all and ra_customer_trx_all

SQL Query Using Join between ar_cash_receipts_all and ra_customer_trx_all

 

SELECT ACRA.AMOUNT "Receipt Amount",

ACRA.CURRENCY_CODE,

ACRA.TYPE,

ACRA.RECEIPT_NUMBER,

ACRA.RECEIPT_DATE,

RCTA.TRX_NUMBER "AR Invoice",

RCTA.TRX_DATE  "AR Invoice Date",

ARAA.AMOUNT_APPLIED

 FROM

AR_RECEIVABLE_APPLICATIONS_ALL ARAA ,

 AR_CASH_RECEIPTS_ALL ACRA,

 RA_CUSTOMER_TRX_ALL RCTA

WHERE  ARAA.CASH_RECEIPT_ID=ACRA.CASH_RECEIPT_ID

AND ACRA.RECEIPT_NUMBER =: P_RECEIPT_NUMBER

AND ARAA.APPLIED_CUSTOMER_TRX_ID=RCTA.CUSTOMER_TRX_ID;