Query to find ar credit memo in oracle apps

Query to find ar credit memo in oracle apps

In this post , we will be share the SQL query to extract the AR credit memo in oracle apps. We know the functioning of the credit memos in AR for example when we have to reduce the liability for customers then we do create the credit memo. This helps to reduce the outstanding amount for the customers. here below i will try to share the detail level sql query which will help to extract the AR credit memos in oracle apps. 

Important Tables use in the sql query to find ar credit memo in oracle apps

These are the most important tables which most commonly used in the AR Invoice reports.

1.ORG_ORGANIZATION_DEFINITIONS

2.ar_customers

3.HZ_CUST_ACCOUNTS

4.HZ_PARTIES HP,

5.RA_CUST_TRX_TYPES_ALL

6.HZ_CUST_SITE_USES_ALL

7.RA_CUSTOMER_TRX_ALL

8.RA_CUSTOMER_TRX_LINES_ALL

9.RA_CUST_TRX_LINE_GL_DIST_ALL

Detail SQL Query to find ar credit memo in oracle apps

Here below is the complete sql query which help to extract the complete detail details for the AR credit memo Invoices in oracle apps r12.

SELECT

 RCT.TRX_NUMBER "TRX NUMBER",

 RCT.TRX_DATE "TRX DATE",

 RCG.GL_DATE,

 RCT.PURCHASE_ORDER,

 OOD.ORGANIZATION_NAME,

 HCSUA.LOCATION,

 RCL.DESCRIPTION,

 HCA.ACCOUNT_NUMBER,

 HP.PARTY_NAME,

 RTT.NAME TRANSACTION_NAME,

 DECODE(RTT.TYPE,'CM','Credit Memo',RTT.TYPE) TRANSACTION_TYPE,

RCL.LINE_TYPE AR_LINE_TYPE,

 sum((DECODE(RCT.INVOICE_CURRENCY_CODE,'INR',RCG.AMOUNT*1,RCG.AMOUNT*RCT.EXCHANGE_RATE))) TOTAL_INV_AMOUNT

 FROM

apps.ORG_ORGANIZATION_DEFINITIONS OOD,

apps.ar_customers ac,

apps.HZ_CUST_ACCOUNTS HCA,

apps.HZ_PARTIES HP,

apps.RA_CUST_TRX_TYPES_ALL RTT,

apps.HZ_CUST_SITE_USES_ALL HCSUA,

apps.RA_CUSTOMER_TRX_ALL RCT,

apps.RA_CUSTOMER_TRX_LINES_ALL RCL,

apps.RA_CUST_TRX_LINE_GL_DIST_ALL RCG

 WHERE

 RCT.CUSTOMER_TRX_ID = RCL.CUSTOMER_TRX_ID

 AND RCL.CUSTOMER_TRX_LINE_ID = RCG.CUSTOMER_TRX_LINE_ID

 AND RCT.BILL_TO_CUSTOMER_ID = HCA.CUST_ACCOUNT_ID

 and HCA.CUST_ACCOUNT_ID=ac.customer_id

 AND HCA.PARTY_ID = HP.PARTY_ID

 AND RTT.TYPE='CM'

 AND RCT.CUST_TRX_TYPE_ID = RTT.CUST_TRX_TYPE_ID

 AND TO_NUMBER(RCT.INTERFACE_HEADER_ATTRIBUTE10) = OOD.ORGANIZATION_ID (+)

 AND RCT.BILL_TO_SITE_USE_ID=HCSUA.SITE_USE_ID

 GROUP BY

 RCT.CUSTOMER_TRX_ID,RCT.TRX_NUMBER,

 RCT.CUST_TRX_TYPE_ID,RCT.TRX_DATE,RCG.GL_DATE,

 RCT.CUSTOMER_TRX_ID,RCT.PURCHASE_ORDER,OOD.ORGANIZATION_NAME,

 RCL.DESCRIPTION,

 HCA.ACCOUNT_NUMBER,

 HP.PARTY_NAME,

HCSUA.LOCATION,

 RTT.NAME,RTT.TYPE,RCL.LINE_TYPE

Query to find ar credit memo in oracle apps