ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN 82288223EF4A3869E053B46B63588994 -- This Query populates Detail Information of Unreconciled Bank and Book Transactions. -- First 3 sqls populate bank amount details while, the other 3 sqls populate book amount details -- Following SQL fetches bank amounts for unreconciled lines --> SELECT &gc_origin_bank_stmt ORIGIN , DECODE(csl.trx_type, 'CREDIT', &gc_trx_type_receipt, 'DEBIT', &gc_trx_type_payment, csl.trx_type) TRX_TYPE , csl.bank_trx_number DOCNUMBER , NVL(csl.effective_date,csl.trx_date) TRX_DATE , 0 BOOKS , NVL(SUM(DECODE(csl.trx_type, 'DEBIT', -1 * csl.amount, csl.amount)),0) BANK FROM ce_statement_headers csh , ce_statement_lines csl WHERE csl.statement_header_id= csh.statement_header_id AND csl.status = 'UNRECONCILED' AND csh.bank_account_id = :P_BANK_ACCOUNT_ID AND TRUNC(csl.trx_date) BETWEEN :gc_from_date AND :gc_to_date GROUP BY csl.trx_type , csl.bank_trx_number , csl.effective_date , csl.trx_date UNION ALL -- This SQL fetches bank amounts for lines that at the 'Period To' parameter time were -- unreconciled, but reconciled at report submission time SELECT &gc_origin_bank_stmt ORIGIN , DECODE(csl.trx_type, 'CREDIT', &gc_trx_type_receipt, 'DEBIT', &gc_trx_type_payment, csl.trx_type) TRX_TYPE , csl.bank_trx_number DOCNUMBER , NVL(csl.effective_date,csl.trx_date) TRX_DATE , 0 BOOKS , NVL(SUM(DECODE(csl.trx_type, 'DEBIT', -1 * csl.amount, csl.amount)),0) BANK FROM ce_statement_headers csh , ce_statement_reconciliations csr , ce_statement_lines csl WHERE csl.statement_header_id = csh.statement_header_id AND csl.status = 'RECONCILED' AND csh.bank_account_id= :P_BANK_ACCOUNT_ID AND TRUNC(csl.trx_date) BETWEEN :gc_from_date AND :gc_to_date AND csl.statement_line_id = csr.statement_line_id AND csr.current_record_flag = 'Y' AND csr.status_flag = 'M' --Changed the column created_date to effective_date AND TRUNC(csl.effective_date) > :gc_to_date GROUP BY csl.trx_type , csl.bank_trx_number , csl.effective_date , csl.trx_date UNION ALL -- If a bank statement line is partly reconciled, the line status is 'reconciled'. Still, this line -- should appear in this part (the unreconciled bank amount). This SQL fetches amounts for those lines. SELECT &gc_origin_bank_stmt ORIGIN , DECODE(csl.trx_type, 'CREDIT', &gc_trx_type_receipt, 'DEBIT', &gc_trx_type_payment, csl.trx_type) TRX_TYPE , csl.bank_trx_number DOCNUMBER , NVL(csl.effective_date,csl.trx_date) TRX_DATE , 0 BOOKS , NVL(SUM(DECODE(csl.trx_type, 'DEBIT', -1 * (csl.amount - csr.amount), (csl.amount - csr.amount))),0) BANK FROM ce_statement_headers csh , ce_statement_reconciliations csr , ce_statement_lines csl WHERE csl.statement_header_id = csh.statement_header_id AND csl.status ='RECONCILED' AND csh.bank_account_id = :P_BANK_ACCOUNT_ID AND TRUNC(csl.trx_date) BETWEEN :gc_from_date AND :gc_to_date AND csl.statement_line_id = csr.statement_line_id AND csl.amount > csr.amount --Changed the column created_date to effective_date AND TRUNC(csl.effective_date) BETWEEN :gc_from_date AND :gc_to_date AND csr.current_record_flag = 'Y' AND csr.status_flag = 'M' GROUP BY csl.trx_type , csl.bank_trx_number , csl.effective_date , csl.trx_date UNION ALL -- This SQL fetches book amounts for unreconciled lines SELECT &gc_origin_accounted ORIGIN , catv.type_meaning TRX_TYPE , catv.trx_number DOCNUMBER , catv.trx_date TRX_DATE , NVL(SUM(DECODE(catv.type_meaning, 'Debit', catv.bank_account_amount, 'Receipt', catv.bank_account_amount, -1 * catv.bank_account_amount)),0) BOOKS , 0 BANK FROM ce_available_transactions_v catv WHERE application_id <> 260 AND catv.bank_account_id = :P_BANK_ACCOUNT_ID AND catv.trx_date BETWEEN :gc_from_date AND :gc_to_date --Added the Currency Condition AND catv.currency_code = :gc_currency_code GROUP BY catv.type_meaning ,catv.trx_number ,catv.trx_date UNION ALL -- This SQL fetches book amounts for lines that at the 'Period To' parameter time were -- unreconciled, but reconciled at report submission time --> SELECT &gc_origin_accounted ORIGIN , crtv.type_meaning TRX_TYPE , crtv.trx_number DOCNUMBER , crtv.trx_date TRX_DATE , NVL(SUM(DECODE(crtv.type_meaning, 'Debit', crtv.bank_account_amount, 'Receipt', crtv.bank_account_amount, -1 * crtv.bank_account_amount)),0) BOOKS , 0 BANK FROM ce_reconciled_transactions_v crtv WHERE crtv.bank_account_id = :P_BANK_ACCOUNT_ID AND crtv.trx_date BETWEEN :gc_from_date AND :gc_to_date AND TRUNC(crtv.cleared_date) > :gc_to_date --Added the Currency Condition AND crtv.currency_code = :gc_currency_code --Commented the Status condition is false --AND crtv.status = 'RECONCILED' GROUP BY crtv.type_meaning ,crtv.trx_number ,crtv.trx_date UNION ALL -- If a line is partly reconciled, the line status is 'reconciled'. Still, this line should appear -- in this part (the unreconciled amount). This SQL fetches book amounts for those lines. SELECT &gc_origin_accounted ORIGIN , crtv.type_meaning TRX_TYPE , crtv.trx_number DOCNUMBER , crtv.trx_date TRX_DATE , NVL(SUM(DECODE(crtv.type_meaning, 'Debit', (crtv.bank_account_amount - crtv.actual_amount_cleared), 'Receipt', (crtv.bank_account_amount - crtv.actual_amount_cleared), -1 * (crtv.bank_account_amount - crtv.actual_amount_cleared))),0) BOOKS , 0 BANK FROM ce_reconciled_transactions_v crtv WHERE crtv.bank_account_id = :P_BANK_ACCOUNT_ID AND crtv.trx_date BETWEEN :gc_from_date AND :gc_to_date AND crtv.bank_account_amount > crtv.actual_amount_cleared --Added the Currency Condition AND crtv.currency_code = :gc_currency_code --Commented the Status condition is false --AND crtv.status = 'RECONCILED' GROUP BY crtv.type_meaning ,crtv.trx_number ,crtv.trx_date ORDER BY 3, 4 Y CE CEBNKSTMTRECON AR CE إسرائيل - تسوية كشف حساب البنك Application: إدارة النقدية Source: إسرائيل - تسوية كشف حساب البنك Short Name: CEBNKSTMTRECON DB package: CE_BNK_STMT_RECON_RPT_PKG D CE Israel - Kontoauszugsabstimmung Application: Cash Management Source: Israel - Kontoauszugsabstimmung Short Name: CEBNKSTMTRECON DB package: CE_BNK_STMT_RECON_RPT_PKG F CE Israël - Rapprochement des relevés bancaires Application: Cash Management Source: Israël - Rapprochement des relevés bancaires Short Name: CEBNKSTMTRECON DB package: CE_BNK_STMT_RECON_RPT_PKG US CE Israel - Bank Statement Reconciliation Application: Cash Management Source: Israel - Bank Statement Reconciliation Short Name: CEBNKSTMTRECON DB package: CE_BNK_STMT_RECON_RPT_PKG ZHS CE 以色列 - 银行对帐单调节 Application: 现金管理系统 Source: 以色列 - 银行对帐单调节 Short Name: CEBNKSTMTRECON DB package: CE_BNK_STMT_RECON_RPT_PKG BI Publisher &gc_origin_accounted &gc_origin_bank_stmt &gc_trx_type_payment &gc_trx_type_receipt :gc_bank_account_name :gc_currency_code :gc_from_date :gc_origin_accounted :gc_origin_bank_stmt :gc_to_date :gc_trx_type_payment :gc_trx_type_receipt :gn_sob_id :p_acc_period_from :p_acc_period_to :p_bank_account_id :p_close_balance 1 10 :p_bank_account_id LOV Oracle CE_BANK_ACC_NAME Y select bank_account_id id, bank_account_name value, null description from ce_internal_bank_accounts_v where (inactive_date is null or inactive_date>(select sysdate from dual)) order by value,description Y AR اسم حساب البنك D Bezeichnung Bankkonto F Intitulé du compte bancaire US Bank Account Name ZHS 银行帐户名 2 20 :p_acc_period_from LOV Oracle GL_SRS_ALL_PERIODS Y select period_name id, period_name value, null description from gl_period_statuses where application_id=101 and ledger_id=fnd_profile.value('GL_SET_OF_BKS_ID') order by period_year desc,period_num desc Y AR من الفترة المحاسبية D Von Buchungsperiode F Période comptable - De US From Accounting Period ZHS 自会计期 3 30 :p_acc_period_to LOV Oracle GL_SRS_ALL_PERIODS Y select period_name id, period_name value, null description from gl_period_statuses where application_id=101 and ledger_id=fnd_profile.value('GL_SET_OF_BKS_ID') order by period_year desc,period_num desc Y AR إلى الفترة المحاسبية D In Buchungsperiode F Période comptable - A US To Accounting Period ZHS 至会计期 4 40 :p_close_balance Number AR رصيد الإقفال D Endsaldo F Solde de clôture US Closing Balance ZHS 期末余额