Query To Get AR Receivable Credit Memo Details In Oracle Apps R12

 

This query is used to get Receivable AR credit memo details in Oracle Apps R12, we create credit memo in AR to reduce the liability for the customer, AR Credit memo will reduce down the outstanding for the customer.

Some important Receivable AR credit memo table in Oracle Apps R12

ORG_ORGANIZATION_DEFINITIONS

AR_CUSTOMERS

HZ_CUST_ACCOUNTS

HZ_PARTIES

RA_CUST_TRX_TYPES_ALL

HZ_CUST_SITE_USES_ALL

RA_CUSTOMER_TRX_ALL

RA_CUSTOMER_TRX_LINES_ALL

RA_CUST_TRX_LINE_GL_DIST_ALL

<div><br class="Apple-interchange-newline">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;</div>

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44

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;