List of Credit Memos applied against AR Transaction and Vice-Versa

Background:

• One or many Credit Memos can be applied against a single AR Transaction/Invoice
• A single Credit Memo can be applied for One or many AR Transactions/Invoices

Scripts: 

1. Provide AR Transaction to the following script.
It lists all CM's applied against a single AR Transaction.

SELECT rcta2.trx_number CMs_Applied_to_AR_Trx_No
  FROM RA_CUSTOMER_TRX_ALL rcta1,
       RA_CUSTOMER_TRX_ALL rcta2,
       AR_RECEIVABLE_APPLICATIONS_ALL araa
 WHERE     rcta1.trx_number = '&Enter_AR_Txn_No'
       AND rcta1.customer_trx_id = araa.APPLIED_CUSTOMER_TRX_ID
       AND rcta2.customer_trx_id = araa.customer_trx_id;

2. Provide Credit Memo number to the following query
It lists all AR Transactions/Invoices applied with this Credit Memo given

SELECT rcta2.trx_number AR_Trx_Nos
  FROM RA_CUSTOMER_TRX_ALL rcta1,
       RA_CUSTOMER_TRX_ALL rcta2,
       AR_RECEIVABLE_APPLICATIONS_ALL araa
 WHERE     rcta1.trx_number = '&Enter_Credit_Memo'
       AND rcta1.customer_trx_id = araa.customer_trx_id
       AND rcta2.customer_trx_id = araa.APPLIED_CUSTOMER_TRX_ID;


ref: http://orclapp.blogspot.ae/2013/05/list-of-credit-memos-applied-against-ar.html