This AR Bank Risk Report query retrieves a Factored Receipt Remittance Report, focusing on cash receipts that have been factored and subsequently cleared.
#sqlquery
SELECT
-- SQL4Fusion (An Orbit Analytics Project)
-- This query retrieves factored receipt remittance details.
HAOU.NAME ORGANIZATION_NAME,
CBBV.BANK_NAME,
CBBV.BANK_BRANCH_NAME,
CBA.BANK_ACCOUNT_NAME,
CBA.BANK_ACCOUNT_NUM BANK_ACCOUNT_NUMBER,
CBBV.ADDRESS_LINE1,
CBBV.ADDRESS_LINE2,
CBBV.ADDRESS_LINE3,
CBBV.ZIP,
CBBV.CITY,
CBBV.STATE,
CBBV.COUNTRY,
ABA.NAME BATCH_NAME,
ACRHA.TRX_DATE TRANSACTION_DATE,
ACRA.RECEIPT_NUMBER,
APSA.DUE_DATE,
DECODE (
IFPAIV.INSTRUMENT_TYPE,
'BANKACCOUNT',
IFPAIV.BANK_NAME,
'CREDITCARD',
IFPAIV.CARD_ISSUER_NAME,
'DEBITCARD',
IFPAIV.CARD_ISSUER_NAME,
IFPAIV.INSTRUMENT_TYPE
) CREDIT_BANK_NAME,
ACRA.CURRENCY_CODE,
NVL (ACRA.AMOUNT, 0) RECEIPT_AMOUNT,
(
ACRHA.ACCTD_AMOUNT + NVL (
ACRHA.ACCTD_FACTOR_DISCOUNT_AMOUNT,
0
)
) TOTAL_CASH_RECEIPT_AMOUNT,
ACRHA.ORG_ID,
CBBV.BANK_PARTY_ID,
--CBBV.BRANCH_PARTY_ID,
--ACR.ROWID ACRA_ROW_ID,
--APSA.ROWID APSA_ROW_ID,
--ABA.ROWID ABA_ROW_ID,
CBBV.BANK_INSTITUTION_TYPE ,
APSA.CLASS PAYMENT_SCHEDULE_CLASS,
APSA.FOLLOW_UP_CODE_LAST,
ACRHA.NOTE_STATUS,
ACRA.REVERSAL_CATEGORY,
ACRA.REVERSAL_REASON_CODE,
APSA.STATUS PAYMENT_SCHEDULE_STATUS,
ACRA.STATUS CASH_RECEIPT_STATUS,
ACRHA.STATUS CASH_RECEIPT_HISTORY_STATUS
FROM
CE_BANK_BRANCHES_V CBBV,
CE_BANK_ACCOUNTS CBA,
AR_CASH_RECEIPT_HISTORY_ALL ACRHA,
AR_CASH_RECEIPTS_ALL ACRA,
AR_PAYMENT_SCHEDULES_ALL APSA,
HR_ALL_ORGANIZATION_UNITS HAOU,
AR_BATCHES_ALL ABA,
IBY_FNDCPT_PAYER_ASSGN_INSTR_V IFPAIV,
IBY_TRXN_EXTENSIONS_V ITEV,
CE_BANK_ACCT_USES_ALL CBAUA
WHERE
1 = 1
AND CBBV.BRANCH_PARTY_ID = CBA.BANK_BRANCH_ID
AND ACRHA.CASH_RECEIPT_ID = ACRA.CASH_RECEIPT_ID
--AND CBA.BANK_BRANCH_ID = CBBV.BRANCH_PARTY_ID
AND ACRA.PAYMENT_TRXN_EXTENSION_ID = ITEV.TRXN_EXTENSION_ID(+)
AND ITEV.INSTRUMENT_ID = IFPAIV.INSTRUMENT_ID(+)
AND ITEV.INSTR_ASSIGNMENT_ID = IFPAIV.INSTR_ASSIGNMENT_ID(+)
AND ACRA.CASH_RECEIPT_ID = APSA.CASH_RECEIPT_ID(+)
AND HAOU.ORGANIZATION_ID = ACRHA.ORG_ID
AND ACRA.REMIT_BANK_ACCT_USE_ID = CBAUA.BANK_ACCT_USE_ID
AND CBA.BANK_ACCOUNT_ID = CBAUA.BANK_ACCOUNT_ID
AND ACRHA.CURRENT_RECORD_FLAG = 'Y'
AND ACRHA.STATUS = 'CLEARED'
AND ACRHA.BATCH_ID=ABA.BATCH_ID
/*AND (ACRA.CASH_RECEIPT_ID, ABA.BATCH_ID) IN
(SELECT DISTINCT ACRHA2.CASH_RECEIPT_ID, ACRHA2.BATCH_ID
FROM AR_CASH_RECEIPT_HISTORY_ALL ACRHA2
WHERE ACRHA2.STATUS = 'REMITTED')
AND ACRHA.FACTOR_FLAG = 'Y'*/