Cash Management -Transaction Available for Reconciliation Report

We have received a requirement from Cash Management to develop a query that fetches 

Un-reconciled transactions for Bank Statement Lines and System Transactions.

Navigation: Cash Management → Bank Statements and Reconciliation → Manual Reconciliation

We have developed a query to retrieve data from the Bank Statement Lines and the System Transactions.

1. Bank Statement Lines:

---------------------------------------------------------------------------------------------------------------------

SELECT cba.bank_account_name,
       csh.statement_number
       Statement_ID,
       csl.line_number
       Line_Number,
       To_char(csl.booking_date, 'DD-Mon-yyyy', 'NLS_DATE_LANGUAGE=ENGLISH')
       BOOKING_DATE,
       To_char(csl.value_date, 'DD-Mon-yyyy', 'NLS_DATE_LANGUAGE=ENGLISH')
       VALUE_DATE,
       csl.trx_type,
       csl.amount,
       (SELECT trx_type
        FROM   ce_transaction_codes
        WHERE  transaction_code_id = csl.trx_code_id)
       TRX_CODE_ID,
       csl.end_to_end_id,
       csl.instruction_identification,
       NULL
       Additional_information,
       csl.accnt_servicer_ref,
       csl.structured_payment_reference,
       csl.customer_reference,
       csh.statement_header_id
FROM   ce_statement_headers csh,
       ce_bank_accounts cba,
       ce_statement_lines csl
WHERE  csh.bank_account_id = cba.bank_account_id
       -- AND cba.BANK_ACCOUNT_ID = 300000094427003
       AND csh.recon_status_code = 'INCOMPLETE'
       AND csh.statement_header_id = csl.statement_header_id
       AND csl.recon_status = 'UNR'
       AND CBA.bank_account_name = :P_BANK_ACCOUNT
-- AND (CBA.BANK_ACCOUNT_NAME IN (:P_BANK_ACCOUNT) OR LEAST(:P_BANK_ACCOUNT) IS NULL)
ORDER  BY csl.booking_date 

---------------------------------------------------------------------------------------------------------------------

2. System Transactions:

Source: Journal

SELECT cat.journal_batch_name,
       To_char(cat.trx_date, 'DD-Mon-yyyy', 'NLS_DATE_LANGUAGE=ENGLISH')
       effective_date,
       To_char(gjl.effective_date, 'DD-Mon-yyyy', 'NLS_DATE_LANGUAGE=ENGLISH')
       Accounting_Date,
       NULL
       line_type_id,
       cat.journal_line_description,
       cat.trx_currency_code
       CURRENCY,
       To_char(cat.recon_match_amount,
       fnd_currency.Get_format_mask(cba.currency_code,
       30))                              amount,
       cat.trx_amount
       Accounted_amount,
       ------------------------------------------------------------------------
       cat.bank_account_id,
       cat.transaction_source,
       cat.transaction_id,
       cat.recon_match_amount,
       cat.transaction_number,
       cat.status,
       cat.recon_flag,
       cat.journal_name,
       cat.trx_line_id,
       cat.journal_batch_id,
       cba.bank_account_name,
       cba.bank_account_num,
       gjl.entered_dr,
       gjl.entered_cr,
       gjl.accounted_dr,
       gjl.accounted_cr,
       'Journal'                                                         Source
------------------------------------------------------------------------
FROM   ce_available_transactions_v cat,
       ce_bank_accounts cba,
       gl_je_headers gjh,
       gl_je_lines gjl
WHERE  1 = 1
       -- and cat.journal_name='EBS Nov-24 Conversion Conversion USSYS11976'
       -- and cat.BANK_ACCOUNT_ID=300000094426999
       AND cba.bank_account_id = cat.bank_account_id
       AND CAT.recon_flag = 'N'
       AND CAT.transaction_source = 'ORA_GL' -----> Journals
       AND cat.journal_batch_id = gjh.je_batch_id
       AND gjh.je_header_id = gjl.je_header_id
       AND gjl.je_line_num = cat.trx_line_id
       AND CBA.bank_account_name = :P_BANK_ACCOUNT
       -- AND (CBA.BANK_ACCOUNT_NAME IN (:P_BANK_ACCOUNT) OR LEAST(:P_BANK_ACCOUNT) IS NULL)
       -- AND NVL(:P_SOURCE, 'Journal') = 'Journal'
       AND ( CAT.transaction_source IN ( :P_SOURCE )
              OR Least(:P_SOURCE) IS NULL )
       AND gjl.effective_date BETWEEN Nvl(:p_date_from, gjl.effective_date) AND
                                      Nvl(
                                      :p_date_to, gjl.effective_date)
ORDER  BY gjl.effective_date 

---------------------------------------------------------------------------------------------------------------------

2. System Transactions:

Source: Payables

SELECT CAT.cparty_name
       SUPPLIER_NAME,
       -- CAT.TRX_DATE PAYMENT_DATE, 
       To_char(CAT.trx_date, 'DD-Mon-yyyy', 'NLS_DATE_LANGUAGE=ENGLISH')
       PAYMENT_DATE,
       To_char(CAT.recon_match_date, 'DD-Mon-yyyy', 'NLS_DATE_LANGUAGE=ENGLISH')
       invoice_date,
       NULL
       Maturity_Date,
       CAT.pay_receipt_method,
       CAT.transaction_number
       PAYMENT_NUMBER,
       CAT.trx_currency_code,
       CAT.trx_amount,
       CAT.trx_amount * -1
       ACCOUNT_AMOUNT,
       cat.transaction_source,
       CAT.status,
       CAT.cparty_site
       SUPPLIER_SITE,
       cat.recon_flag,
       'Payables'                                                        source
FROM   ce_available_transactions_v cat,
       ce_bank_accounts cba
WHERE  1 = 1
       -- AND cat.transaction_number='5028345'
       AND cat.recon_flag = 'N'
       AND cat.transaction_source = 'AP'
       AND cba.bank_account_id = cat.bank_account_id
       AND CBA.bank_account_name = :P_BANK_ACCOUNT
       -- AND (CBA.BANK_ACCOUNT_NAME IN (:P_BANK_ACCOUNT) OR LEAST(:P_BANK_ACCOUNT) IS NULL)
       -- AND NVL(:P_SOURCE, 'Payables') = 'Payables'
       -- AND CAT.TRANSACTION_SOURCE IN (:P_SOURCE)
       AND ( CAT.transaction_source IN ( :P_SOURCE )
              OR Least(:P_SOURCE) IS NULL )
       AND CAT.trx_date BETWEEN Nvl(:p_date_from, CAT.trx_date) AND
                                Nvl(:p_date_to, CAT.trx_date)
ORDER  BY CAT.recon_match_date 

---------------------------------------------------------------------------------------------------------------------

2. System Transactions:

Source: Receivables

SELECT cat.cparty_name
       CUSTOMER_NAME,
       To_char(CAT.trx_date, 'DD-Mon-yyyy', 'NLS_DATE_LANGUAGE=ENGLISH')
       Remit_Date,
       NULL
       Maturity_Date,
       CAT.pay_receipt_method,
       CAT.transaction_number
       RECEIPT_NUMBER,
       CAT.trx_currency_code
       CURRENCY,
       CAT.trx_amount                                                    AMOUNT,
       CAT.trx_amount
       ACCOUNT_AMOUNT,
       CAT.receipt_batch_name
       RECEIPT_BATCH_NUMBER,
       CAT.receipt_class_name,
       To_char(CAT.recon_match_date, 'DD-Mon-yyyy', 'NLS_DATE_LANGUAGE=ENGLISH')
       RECON_MATCH_DATE,
       CAT.transaction_source,
       cat.recon_flag,
       'Receivable'                                                      Source
FROM   ce_available_transactions_v cat,
       ce_bank_accounts cba
WHERE  1 = 1
       -- AND cat.transaction_number='5028345'
       AND cat.recon_flag = 'N'
       AND cat.transaction_source = 'AR'
       AND cba.bank_account_id = cat.bank_account_id
       AND CBA.bank_account_name = :P_BANK_ACCOUNT
       -- AND (CBA.BANK_ACCOUNT_NAME IN (:P_BANK_ACCOUNT) OR LEAST(:P_BANK_ACCOUNT) IS NULL)
       -- AND CBA.BANK_ACCOUNT_ID = 300000094427003
       -- AND CAT.TRANSACTION_SOURCE IN (:P_SOURCE)
       AND ( CAT.transaction_source IN ( :P_SOURCE )
              OR Least(:P_SOURCE) IS NULL )
-- AND NVL(:P_SOURCE, 'Receivable') = 'Receivable'
-- and CAT.TRX_DATE between nvl(:p_date_from,CAT.TRX_DATE) and nvl(:p_date_to,CAT.TRX_DATE)
-- ORDER BY CAT.TRANSACTION_NUMBER
ORDER  BY CAT.trx_date

---------------------------------------------------------------------------------------------------------------------

                                                                    **Thank You**

---------------------------------------------------------------------------------------------------------------------