AR Reversed Receipts Generate the Reversed Receipts Report to review all receipts that have been reversed. A receipt is reversed when a customer stops the payment or there are Non Sufficient Funds.
#sqlquery
SELECT
--SQL4Fusion (An Orbit Analytics Project).
--This query retrieves detailed information about reversed customer receipts.
HAOU.NAME ORGANIZATION_NAME,
SUBSTR (ACRA.RECEIPT_NUMBER, 1, 15) RECEIPT_NUMBER,
ACRA.CURRENCY_CODE,
NVL(ACRHA.AMOUNT,0) + NVL(ACRHA.FACTOR_DISCOUNT_AMOUNT,0) AMOUNT,
NVL(ACRHA.ACCTD_AMOUNT,0) + NVL(ACRHA.ACCTD_FACTOR_DISCOUNT_AMOUNT,0) FUNCTIONAL_AMOUNT,
ACRA.STATUS RECEIPT_STATUS,
SUBSTR (HP.PARTY_NAME, 1, 50) CUSTOMER_NAME,
SUBSTR (HCA.ACCOUNT_NUMBER, 1, 30) CUSTOMER_ACCOUNT_NUMBER,
HP.CATEGORY_CODE CUSTOMER_CATEGORY_CODE,
HCA.CUSTOMER_TYPE,
SUBSTR (HCSUA.LOCATION, 1, 20) LOCATION,
SUBSTR (AL.MEANING, 1, 8) PAYMENT_TYPE,
APSA.DUE_DATE MATURITY_DATE,
SUBSTR (AL2.MEANING, 1, 12) REVERSAL_REASON,
ACRA.REVERSAL_REASON_CODE,
ACRHA.GL_DATE REVERSAL_GL_DATE,
ACRHA.NOTE_STATUS,
HCSUA.TAX_CLASSIFICATION,
HCA.TAX_ROUNDING_RULE,
ACRA.REVERSAL_CATEGORY,
HCSUA.SITE_USE_CODE,
SUBSTR (CBBV.BANK_NAME, 1, 60) BANK_NAME,
SUBSTR (NVL (CBA.BANK_ACCOUNT_NAME, 'NONE'),1,19 ) BANK_ACCOUNT_NAME,
CBBV.BANK_INSTITUTION_TYPE
FROM
AR_CASH_RECEIPTS_ALL ACRA,
HZ_CUST_ACCOUNTS HCA,
HZ_PARTIES HP,
AR_LOOKUPS AL,
AR_LOOKUPS AL2,
AR_LOOKUPS AL3,
AR_PAYMENT_SCHEDULES_ALL APSA,
AR_CASH_RECEIPT_HISTORY_ALL ACRHA,
CE_BANK_ACCT_USES_ALL CBAUA,
CE_BANK_ACCOUNTS CBA,
CE_BANK_BRANCHES_V CBBV,
HZ_CUST_SITE_USES_ALL HCSUA,
HR_ALL_ORGANIZATION_UNITS HAOU
WHERE
1 = 1
AND ACRA.PAY_FROM_CUSTOMER = HCA.CUST_ACCOUNT_ID
AND HCA.PARTY_ID = HP.PARTY_ID
AND ACRA.REVERSAL_CATEGORY = AL.LOOKUP_CODE
AND AL.LOOKUP_TYPE = 'REVERSAL_CATEGORY_TYPE'
AND ACRA.REVERSAL_REASON_CODE = AL2.LOOKUP_CODE
AND AL2.LOOKUP_TYPE = 'CKAJST_REASON'
AND ACRA.TYPE = AL3.LOOKUP_CODE
AND AL3.LOOKUP_TYPE = 'PAYMENT_CATEGORY_TYPE'
AND ACRA.CASH_RECEIPT_ID = APSA.CASH_RECEIPT_ID(+)
AND ACRA.CASH_RECEIPT_ID = ACRHA.CASH_RECEIPT_ID(+)
--AND ACRHA.FIRST_POSTED_RECORD_FLAG = 'Y'
AND ACRA.REMIT_BANK_ACCT_USE_ID = CBAUA.BANK_ACCT_USE_ID(+)
AND CBAUA.BANK_ACCOUNT_ID = CBA.BANK_ACCOUNT_ID(+)
AND CBA.BANK_BRANCH_ID = CBBV.BRANCH_PARTY_ID
AND ACRA.CUSTOMER_SITE_USE_ID = HCSUA.SITE_USE_ID(+)
AND HAOU.ORGANIZATION_ID = ACRA.ORG_ID
AND ACRHA.STATUS = 'REVERSED'