This AR Receipts Awaiting Bank Clearance query generates the Receipts Awaiting Bank Clearance Report to view a list of receipts that have not cleared your remittance bank. It includes manual and automatic receipts that would require the bank clearance step for receipt class.
#sqlquery
SELECT
--SQL4Fusion (An Orbit Analytics Project)
--This SQL query generates a Remitted Receipts Report that includes receipts currently not in clearing status.
HAOU.NAME ORGANIZATION_NAME,
APSA.DUE_DATE MATURITY_DATE,
ACRA.RECEIPT_NUMBER ,
CBA.BANK_ACCOUNT_NUM REMIT_ACCOUNT_NUMBER,
CBA.BANK_ACCOUNT_NAME REMIT_ACCOUNT_NAME,
ACRHA.TRX_DATE REMIT_DATE,
AL.MEANING REMITTANCE_METHOD,
ARM.NAME PAYMENT_METHOD,
ACRA.CURRENCY_CODE CURRENCY,
NVL (ACRA.AMOUNT, 0) AMOUNT,
ACRA.FACTOR_DISCOUNT_AMOUNT BANK_CHARGES,
CBA.BANK_ACCOUNT_ID ,
CBAUA.ORG_ID,
ACRA.CASH_RECEIPT_ID,
APSA.CLASS PAYMENT_SCHEDULE,
APSA.FOLLOW_UP_CODE_LAST,
ACRHA.NOTE_STATUS,
ACRA.REVERSAL_CATEGORY,
ACRA.REVERSAL_REASON_CODE,
ACRHA.STATUS
FROM
AR_CASH_RECEIPTS_ALL ACRA,
AR_CASH_RECEIPT_HISTORY_ALL ACRHA,
AR_PAYMENT_SCHEDULES_ALL APSA,
AR_RECEIPT_METHODS ARM,
AR_RECEIPT_CLASSES ARC,
AR_BATCHES_ALL ABA,
CE_BANK_ACCT_USES_ALL CBAUA,
CE_BANK_ACCOUNTS CBA,
AR_LOOKUPS AL,
HR_ALL_ORGANIZATION_UNITS HAOU
WHERE
1 = 1
AND ACRHA.STATUS = 'REMITTED'
AND ACRHA.CURRENT_RECORD_FLAG = 'Y'
AND ACRHA.POSTABLE_FLAG = 'Y'
AND ACRA.CASH_RECEIPT_ID = ACRHA.CASH_RECEIPT_ID
AND ACRA.CASH_RECEIPT_ID = APSA.CASH_RECEIPT_ID(+)
AND ACRA.RECEIPT_METHOD_ID = ARM.RECEIPT_METHOD_ID
AND ARM.RECEIPT_CLASS_ID = ARC.RECEIPT_CLASS_ID
AND ACRHA.BATCH_ID = ABA.BATCH_ID(+)
AND ACRA.REMIT_BANK_ACCT_USE_ID= CBAUA.BANK_ACCT_USE_ID(+)
AND CBAUA.BANK_ACCOUNT_ID = CBA.BANK_ACCOUNT_ID(+)
AND ABA.REMIT_METHOD_CODE = AL.LOOKUP_CODE(+)
AND AL.LOOKUP_TYPE(+) = 'REMITTANCE_METHOD'
AND CBAUA.ORG_ID = HAOU.ORGANIZATION_ID(+)
AND APSA.ORG_ID = HAOU.ORGANIZATION_ID
AND ACRA.REVERSAL_DATE IS NULL
AND ARC.CLEAR_FLAG IN ('Y', 'S')
ORDER BY
ACRA.RECEIPT_NUMBER,
ACRA.CASH_RECEIPT_ID