This AR Credit Memo Register query retrieves details of Credit Memos from Oracle Receivables, including transaction, GL, and customer information.
#sqlquery
SELECT
--SQL4Fusion (An Orbit Analytics Project)
--This query retrieves customer credit memo transactions, including both receivable and revenue accounting details
HAOU.NAME ORGANIZATION_NAME,
RCTA.TRX_NUMBER TRANSACTION_NUMBER,
RCTA.TRX_DATE TRANSACTION_DATE,
RCTTA.NAME TRANSACTION_TYPE,
RCTTA.TYPE TRANSACTION_CLASS,
RCTTA.POST_TO_GL POSTABLE_FLAG,
RCTLG.GL_DATE ,
RCTA.INVOICE_CURRENCY_CODE,
NVL (RCTLG.AMOUNT, 0) ENTERED_AMOUNT,
NVL (RCTLG.ACCTD_AMOUNT, 0) FUNCTIONAL_AMOUNT,
HP.PARTY_NAME CUSTOMER_NAME,
HCA.ACCOUNT_NAME CUSTOMER_ACCOUNT_NAME,
HCA.ACCOUNT_NUMBER CUSTOMER_NUMBER,
GCC.SEGMENT1||'-'||GCC.SEGMENT2||'-'||GCC.SEGMENT3||'-'||GCC.SEGMENT4||'-'||GCC.SEGMENT5 GL_ACCOUNT,
HCA.TAX_ROUNDING_RULE,
HCA.STATUS CUSTOMER_ACCOUNT_STATUS,
HCA.CUSTOMER_TYPE,
RCTA.FOB_POINT,
RCTA.STATUS_TRX TRANSACTION_STATUS,
RCTA.REASON_CODE,
RCTA.DEFAULT_TAX_EXEMPT_FLAG
FROM
RA_CUSTOMER_TRX_ALL RCTA,
RA_CUST_TRX_LINE_GL_DIST_ALL RCTLG,
RA_CUST_TRX_TYPES_ALL RCTTA,
HZ_CUST_ACCOUNTS HCA,
HZ_PARTIES HP,
HR_ALL_ORGANIZATION_UNITS HAOU,
GL_CODE_COMBINATIONS GCC
WHERE
1 = 1
AND RCTA.CUSTOMER_TRX_ID = RCTLG.CUSTOMER_TRX_ID(+)
AND RCTLG.ACCOUNT_CLASS = 'REC'
AND RCTLG.ACCOUNT_SET_FLAG = 'N'
AND RCTA.CUST_TRX_TYPE_SEQ_ID =RCTTA.CUST_TRX_TYPE_SEQ_ID
AND RCTA.BILL_TO_CUSTOMER_ID = HCA.CUST_ACCOUNT_ID
AND HCA.PARTY_ID= HP.PARTY_ID
AND RCTA.COMPLETE_FLAG = 'Y'
AND RCTA.ORG_ID = HAOU.ORGANIZATION_ID
AND RCTLG.CODE_COMBINATION_ID=GCC.CODE_COMBINATION_ID
AND RCTTA.NAME = 'Credit Memo'
UNION
SELECT
HAOU.NAME ORGANIZATION_NAME,
RCTA.TRX_NUMBER TRANSACTION_NUMBER,
RCTA.TRX_DATE TRANSACTION_DATE,
RCTTA.NAME TRANSACTION_TYPE,
RCTTA.TYPE TRANSACTION_CLASS,
RCTTA.POST_TO_GL POSTABLE_FLAG,
RCTLG.GL_DATE ,
RCTA.INVOICE_CURRENCY_CODE,
NVL (RCTLG.AMOUNT, 0) ENTERED_AMOUNT,
NVL (RCTLG.ACCTD_AMOUNT, 0) FUNCTIONAL_AMOUNT,
HP.PARTY_NAME CUSTOMER_NAME,
HCA.ACCOUNT_NAME CUSTOMER_ACCOUNT_NAME,
HCA.ACCOUNT_NUMBER CUSTOMER_NUMBER,
GCC.SEGMENT1||'-'||GCC.SEGMENT2||'-'||GCC.SEGMENT3||'-'||GCC.SEGMENT4||'-'||GCC.SEGMENT5 GL_ACCOUNT,
HCA.TAX_ROUNDING_RULE,
HCA.STATUS CUSTOMER_ACCOUNT_STATUS,
HCA.CUSTOMER_TYPE,
RCTA.FOB_POINT,
RCTA.STATUS_TRX TRANSACTION_STATUS,
RCTA.REASON_CODE,
RCTA.DEFAULT_TAX_EXEMPT_FLAG
FROM
RA_CUSTOMER_TRX_ALL RCTA,
RA_CUST_TRX_LINE_GL_DIST_ALL RCTLG,
RA_CUST_TRX_TYPES_ALL RCTTA,
HZ_CUST_ACCOUNTS HCA,
HZ_PARTIES HP,
HR_ALL_ORGANIZATION_UNITS HAOU,
GL_CODE_COMBINATIONS GCC
WHERE
1 = 1
AND RCTA.CUSTOMER_TRX_ID = RCTLG.CUSTOMER_TRX_ID(+)
AND RCTLG.ACCOUNT_CLASS = 'REV'
AND RCTLG.ACCOUNT_SET_FLAG = 'N'
AND RCTA.CUST_TRX_TYPE_SEQ_ID =RCTTA.CUST_TRX_TYPE_SEQ_ID
AND RCTA.BILL_TO_CUSTOMER_ID = HCA.CUST_ACCOUNT_ID
AND HCA.PARTY_ID= HP.PARTY_ID
AND RCTA.COMPLETE_FLAG = 'Y'
AND RCTA.ORG_ID = HAOU.ORGANIZATION_ID
AND RCTLG.CODE_COMBINATION_ID=GCC.CODE_COMBINATION_ID
AND RCTTA.NAME = 'Credit Memo'