Query to get cancelled ar invoices in oracle apps

Query to get cancelled ar invoices in oracle apps

In this post , We will be discuss about Query to get cancelled ar invoices in oracle apps. In Oracle Payables we can cancel the ar invoice in oracle apps r12 but in Oracle AR we cannot cancel the AR invoice. So, either we can Delete that AR Invoice in oracle apps r12. Oracle system allow us the options to delete the AR transaction from the System but we cannot delete each AR transaction in Oracle. These are some steps and rule which we need to do to cancel or delete an ar invoice in oracle apps r12.Here below I will share the sql query to get cancelled ar invoices in oracle apps which helps to provide the Credit Memo apply to the Standard AR Invoice.

 

 

Rules For How to cancel ar invoice in oracle apps r12

 

If the accounting for that ar invoice has been happened then we cannot delete that transaction so in this case how you can cancel this AR transaction. There are two ways in this.

1. Create the Credit Memo for the same customer and then apply this Credit memo to this AR Invoice by which you can reduce or nullify the debit of the customers.

2. Do the adjustment in this AR invoice but this is not the recommendable step in the Invoice amount is big.

 

 Sample Query to get cancelled ar invoices in oracle apps

SELECT a2.TRX_NUMBER,a2.TRX_DATE,A3.NAME "Invoice Type", A1.CUSTOMER_TRX_ID,a4.TRX_NUMBER "Applied TRX Number",a4.TRX_DATE "Applied TRX Date",A3.NAME "Applied Invoice Type",APPLIED_CUSTOMER_TRX_ID,LINE_APPLIED,AMOUNT_APPLIED FROM

 

AR_RECEIVABLE_APPLICATIONS_ALL A1,

RA_CUSTOMER_TRX_aLL A2,ra_cust_trx_types_all A3 , RA_CUSTOMER_TRX_aLL A4,ra_cust_trx_types_all A5

WHERE A1.CUSTOMER_TRX_ID=A2.CUSTOMER_TRX_ID

AND A2.CUST_TRX_TYPE_ID=A3.CUST_TRX_TYPE_ID

and A1.APPLIED_CUSTOMER_TRX_ID=A4.CUSTOMER_TRX_ID

AND A4.CUST_TRX_TYPE_ID=A5.CUST_TRX_TYPE_ID

and a1.CASH_RECEIPT_ID is null

and a1.org_id=:P_ORG_ID_ID