This AR Debit Memo Register query retrieves detailed information about customer transactions of type Debit Memo that are marked as complete.
#sqlquery
SELECT
--SQL4Fusion (An Orbit Analytics Project)
--This query retrieves detailed information about customer transactions of type Debit Memo that are marked as complete
HAOU.NAME ORGANIZATION_NAME,
RCTA.TRX_NUMBER TRANSACTION_NUMBER,
RCTA.TRX_DATE TRANSACTION_DATE,
RCTA.INVOICE_CURRENCY_CODE ,
RCTTA.NAME TRANSACTION_TYPE_NAME,
RCTTA.TYPE TRANSACTION_TYPE,
RCTA.STATUS_TRX TRANSACTION_STATUS,
HP.PARTY_NAME CUSTOMER_NAME,
HCA.STATUS CUSTOMER_ACCOUNT_STATUS,
HCA.CUSTOMER_TYPE,
HCA.ACCOUNT_NAME CUSTOMER_ACCOUNT_NAME,
HCA.ACCOUNT_NUMBER CUSTOMER_NUMBER,
RCTTA.POST_TO_GL POSTABLE_FLAG,
RCTLG.GL_DATE,
NVL (RCTLG.AMOUNT, 0) ENTERED_AMOUNT,
NVL (RCTLG.ACCTD_AMOUNT, 0) FUNCTIONAL_AMOUNT,
HCA.TAX_ROUNDING_RULE,
RCTA.FOB_POINT,
RCTA.REASON_CODE,
RCTA.DEFAULT_TAX_EXEMPT_FLAG,
RCTLG.SET_OF_BOOKS_ID LEDGER_ID,
HAOU.ORGANIZATION_ID ,
RCTA.CUSTOMER_TRX_ID,
HP.PARTY_ID,
RCTA.CREATED_BY
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
WHERE
1 = 1
AND RCTA.CUSTOMER_TRX_ID = RCTLG.CUSTOMER_TRX_ID(+)
AND RCTA.CUST_TRX_TYPE_SEQ_ID = RCTTA.CUST_TRX_TYPE_SEQ_ID
AND RCTLG.ACCOUNT_CLASS(+) = 'REC'
AND RCTLG.ACCOUNT_SET_FLAG(+) = 'N'
AND RCTA.BILL_TO_CUSTOMER_ID = HCA.CUST_ACCOUNT_ID
AND HCA.PARTY_ID(+) = HP.PARTY_ID
AND RCTLG.ORG_ID =HAOU.ORGANIZATION_ID
AND RCTA.COMPLETE_FLAG = 'Y'
AND RCTTA.NAME='Debit Memo'