CE AutoReconciliation Execution - draft
Description
Categories: BI Publisher
Application: Cash Management
Source: AutoReconciliation Execution Report (XML) - Not Supported: Reserved For Future Use
Short Name: CEXINERR_XML
DB package: CE_CEXINERR_XMLP_PKG
Source: AutoReconciliation Execution Report (XML) - Not Supported: Reserved For Future Use
Short Name: CEXINERR_XML
DB package: CE_CEXINERR_XMLP_PKG
Run
CE AutoReconciliation Execution - draft and other Oracle EBS reports with Blitz Report™ on our demo environment
SELECT l1.meaning error_type, 'D' break_group, sh.statement_number statement_no, sh.doc_sequence_value doc_sequence_value, sl.line_number line_no, sh.statement_date statement_date, sl.trx_date trx_date, ba.bank_account_num bank_acc, ba.bank_account_name bank_account_name, ba.currency_code acc_currency, NULL message_name, nvl(sl.amount,0) amount, acr.currency_code C_CURRENCY_CODE, l2.meaning trx_type, acr.receipt_number trx_no, 'CE' application_short_name, bb.branch_party_id bank_branch_id, CE_CEXINERR_XMLP_PKG.c_prt_message_nameformula(NULL) C_PRT_MESSAGE_NAME FROM ce_lookups l1, ce_lookups l2, ce_bank_accts_gt_v ba, ce_bank_branches_v bb, ce_statement_headers sh, ce_statement_lines sl, ar_cash_receipts_all acr, ce_222_reconciled_v cr WHERE l1.lookup_type = 'ABR_REPORT_EXCEPTIONS' AND l1.lookup_code = 'MISC_RECEIPT_CREATED' AND l2.lookup_type = 'BANK_TRX_TYPE' AND l2.lookup_code = sl.trx_type AND bb.branch_party_id = ba.bank_branch_id AND bb.branch_party_id = :P_BANK_BRANCH_ID AND ba.bank_account_id = sh.bank_account_id AND sh.statement_header_id = sl.statement_header_id AND ba.bank_account_id = DECODE(:P_BANK_ACCOUNT_ID, NULL, ba.bank_account_id, :P_BANK_ACCOUNT_ID) AND to_char(sh.statement_date,'YYYY/MM/DD') >= nvl(to_char(:P_STAT_DATE_FROM,'YYYY/MM/DD'), to_char(sh.statement_date,'YYYY/MM/DD') ) AND to_char(sh.statement_date,'YYYY/MM/DD') <= nvl(to_char(:P_STAT_DATE_TO,'YYYY/MM/DD'), to_char(sh.statement_date,'YYYY/MM/DD') ) AND sh.statement_number >= nvl(:P_STAT_NUMBER_FROM,sh.statement_number) AND sh.statement_number <= nvl(:P_STAT_NUMBER_TO,sh.statement_number) and acr.cash_receipt_id = cr.cash_receipt_id and cr.statement_line_id = sl.statement_line_id and acr.comments = 'Created by Auto Bank Rec' UNION ALL SELECT l1.description error_type, 'C' break_group, sh.statement_number statement_no, sh.doc_sequence_value doc_sequence_value, sl.line_number line_no, sh.statement_date statement_date, sl.trx_date trx_date, ba.bank_account_num bank_acc, ba.bank_account_name bank_account_name, ba.currency_code acc_currency, e.message_name message_name, nvl(sl.amount,0) amount, nvl(sl.currency_code,ba.currency_code) C_CURRENCY_CODE, l2.meaning trx_type, sl.bank_trx_number trx_no, NVL(e.application_short_name,'CE') application_short_name, bb.branch_party_id bank_branch_id, CE_CEXINERR_XMLP_PKG.c_prt_message_nameformula(e.message_name) C_PRT_MESSAGE_NAME FROM ce_lookups l1, ce_lookups l2, ce_bank_accts_gt_v ba, ce_bank_branches_v bb, ce_statement_headers sh, ce_statement_lines sl, ce_reconciliation_errors e WHERE l2.lookup_type = 'BANK_TRX_TYPE' AND l2.lookup_code = sl.trx_type AND l1.lookup_type = 'ABR_REPORT_EXCEPTIONS' AND l1.lookup_code='RECONCILIATION' AND bb.branch_party_id = ba.bank_branch_id AND bb.branch_party_id = :P_BANK_BRANCH_ID AND ba.bank_account_id = sh.bank_account_id AND ba.account_classification = 'INTERNAL' AND sh.statement_header_id = sl.statement_header_id AND sh.statement_header_id = e.statement_header_id AND sl.statement_line_id = e.statement_line_id AND e.statement_line_id is not null and sl.status != 'EXTERNAL' AND ba.bank_account_id = DECODE(:P_BANK_ACCOUNT_ID, NULL, ba.bank_account_id, :P_BANK_ACCOUNT_ID) AND to_char(sh.statement_date,'YYYY/MM/DD') >= nvl(to_char(:P_STAT_DATE_FROM,'YYYY/MM/DD'), to_char(sh.statement_date,'YYYY/MM/DD') ) AND to_char(sh.statement_date,'YYYY/MM/DD') <= nvl(to_char(:P_STAT_DATE_TO,'YYYY/MM/DD'), to_char(sh.statement_date,'YYYY/MM/DD') ) AND sh.statement_number >= nvl(:P_STAT_NUMBER_FROM,sh.statement_number) AND sh.statement_number <= nvl(:P_STAT_NUMBER_TO,sh.statement_number) UNION ALL SELECT l1.description error_type, 'C' break_group, sh.statement_number statement_no, sh.doc_sequence_value doc_sequence_value, 0 line_no, sh.statement_date statement_date, to_date(NULL) trx_date, ba.bank_account_num bank_acc, ba.bank_account_name bank_account_name, ba.currency_code acc_currency, e.message_name message_name, to_number(NULL) amount, ba.currency_code C_CURRENCY_CODE, NULL trx_type, NULL trx_no, NVL(e.application_short_name,'CE') application_short_name, bb.branch_party_id bank_branch_id, CE_CEXINERR_XMLP_PKG.c_prt_message_nameformula(e.message_name) C_PRT_MESSAGE_NAME FROM ce_lookups l1, ce_bank_accts_gt_v ba, ce_bank_branches_v bb, ce_statement_headers sh, ce_reconciliation_errors e WHERE l1.lookup_type = 'ABR_REPORT_EXCEPTIONS' AND l1.lookup_code='RECONCILIATION' AND bb.branch_party_id = ba.bank_branch_id AND bb.branch_party_id = :P_BANK_BRANCH_ID AND ba.bank_account_id = sh.bank_account_id AND ba.account_classification = 'INTERNAL' AND sh.statement_header_id = e.statement_header_id AND e.statement_line_id is null AND ba.bank_account_id = DECODE(:P_BANK_ACCOUNT_ID, NULL, ba.bank_account_id, :P_BANK_ACCOUNT_ID) AND to_char(sh.statement_date,'YYYY/MM/DD') >= nvl(to_char(:P_STAT_DATE_FROM,'YYYY/MM/DD'), to_char(sh.statement_date,'YYYY/MM/DD') ) AND to_char(sh.statement_date,'YYYY/MM/DD') <= nvl(to_char(:P_STAT_DATE_TO,'YYYY/MM/DD'), to_char(sh.statement_date,'YYYY/MM/DD') ) AND sh.statement_number >= nvl(:P_STAT_NUMBER_FROM,sh.statement_number) AND sh.statement_number <= nvl(:P_STAT_NUMBER_TO,sh.statement_number) UNION ALL (SELECT l.description error_type, 'A' break_group, e.statement_number statement_no, to_number(NULL) doc_sequence_value, 1 line_no, sh.statement_date statement_date, sh.statement_date trx_date, e.bank_account_num bank_acc, ba.bank_account_name bank_account_name, ba.currency_code acc_currency, e.message_name message_name, 0 amount, ' ' C_CURRENCY_CODE, ' ' trx_type, ' ' trx_no, NVL(e.application_short_name,'CE') application_short_name, bb.branch_party_id bank_branch_id, CE_CEXINERR_XMLP_PKG.c_prt_message_nameformula(e.message_name) C_PRT_MESSAGE_NAME FROM ce_lookups l, ce_statement_headers_int sh, ce_header_interface_errors e, ce_bank_branches_v bb, ce_bank_accts_gt_v ba WHERE l.lookup_type = 'ABR_REPORT_EXCEPTIONS' AND l.lookup_code='STATEMENT' AND sh.bank_account_num = e.bank_account_num AND sh.statement_number = e.statement_number AND sh.bank_branch_name = bb.bank_branch_name AND e.bank_account_num = NVL(ba.bank_account_num, e.bank_account_num) AND ba.bank_branch_id = bb.branch_party_id AND bb.bank_branch_name = DECODE(:C_BANK_BRANCH_NAME_DSP, :C_ALL_TRANSLATION, bb.bank_branch_name, :C_BANK_BRANCH_NAME_DSP) AND ba.bank_account_id = NVL(:P_BANK_ACCOUNT_ID, ba.bank_account_id) AND ba.account_classification = 'INTERNAL' AND to_char(sh.statement_date,'YYYY/MM/DD') >= nvl(to_char(:P_STAT_DATE_FROM,'YYYY/MM/DD'), to_char(sh.statement_date,'YYYY/MM/DD') ) AND to_char(sh.statement_date,'YYYY/MM/DD') <= nvl(to_char(:P_STAT_DATE_TO,'YYYY/MM/DD'), to_char(sh.statement_date,'YYYY/MM/DD') ) AND sh.statement_number >= nvl(:P_STAT_NUMBER_FROM,sh.statement_number) AND sh.statement_number <= nvl(:P_STAT_NUMBER_TO,sh.statement_number) UNION SELECT l.description error_type, 'A' break_group, e.statement_number statement_no, to_number(NULL) doc_sequence_value, 1 line_no, sh.statement_date statement_date, sh.statement_date trx_date, e.bank_account_num bank_acc, ba.bank_account_name bank_account_name, ba.currency_code acc_currency, e.message_name message_name, 0 amount, ' ' C_CURRENCY_CODE, ' ' trx_type, ' ' trx_no, NVL(e.application_short_name,'CE') application_short_name, bb.branch_party_id bank_branch_id, CE_CEXINERR_XMLP_PKG.c_prt_message_nameformula(e.message_name) C_PRT_MESSAGE_NAME FROM ce_lookups l, ce_statement_headers sh, ce_header_interface_errors e, ce_bank_branches_v bb, ce_bank_accts_gt_v ba WHERE l.lookup_type = 'ABR_REPORT_EXCEPTIONS' AND l.lookup_code='STATEMENT' AND e.bank_account_num = ba.bank_account_num AND e.statement_number = sh.statement_number AND sh.bank_account_id = ba.bank_account_id AND ba.bank_branch_id = bb.branch_party_id AND bb.bank_branch_name = DECODE(:C_BANK_BRANCH_NAME_DSP, :C_ALL_TRANSLATION, bb.bank_branch_name, :C_BANK_BRANCH_NAME_DSP) AND ba.bank_account_id = NVL(:P_BANK_ACCOUNT_ID, ba.bank_account_id) AND ba.account_classification = 'INTERNAL' AND to_char(sh.statement_date,'YYYY/MM/DD') >= nvl(to_char(:P_STAT_DATE_FROM,'YYYY/MM/DD'), to_char(sh.statement_date,'YYYY/MM/DD') ) AND to_char(sh.statement_date,'YYYY/MM/DD') <= nvl(to_char(:P_STAT_DATE_TO,'YYYY/MM/DD'), to_char(sh.statement_date,'YYYY/MM/DD') ) AND sh.statement_number >= nvl(:P_STAT_NUMBER_FROM,sh.statement_number) AND sh.statement_number <= nvl(:P_STAT_NUMBER_TO,sh.statement_number) ) UNION ALL SELECT l.description error_type, 'B' break_group, e.statement_number statement_no, to_number(NULL) doc_sequence_value, e.line_number line_no, sh.statement_date statement_date, sl.trx_date trx_date, e.bank_account_num bank_acc, ba.bank_account_name bank_account_name, ba.currency_code acc_currency, e.message_name message_name, nvl(sl.amount,0) amount, NVL(curr.currency_code, ba.CURRENCY_CODE) C_CURRENCY_CODE, ' ' trx_type, ' ' trx_no, NVL(e.application_short_name,'CE') application_short_name, bb.branch_party_id bank_branch_id, CE_CEXINERR_XMLP_PKG.c_prt_message_nameformula(e.message_name) C_PRT_MESSAGE_NAME FROM fnd_currencies curr, ce_lookups l, ce_statement_headers_int sh, ce_statement_lines_interface sl, ce_line_interface_errors e, ce_bank_branches_v bb, ce_bank_accts_gt_v ba WHERE curr.currency_code(+) = sl.currency_code AND l.lookup_type = 'ABR_REPORT_EXCEPTIONS' AND l.lookup_code='LINE' AND sh.bank_account_num = sl.bank_account_num AND sh.statement_number = sl.statement_number AND sl.line_number = e.line_number AND sl.statement_number = e.statement_number AND sl.bank_account_num = e.bank_account_num AND bb.bank_branch_name = sh.bank_branch_name AND bb.branch_party_id = ba.bank_branch_id AND e.bank_account_num = NVL(ba.bank_account_num, e.bank_account_num) AND bb.bank_branch_name = DECODE(:C_BANK_BRANCH_NAME_DSP, :C_ALL_TRANSLATION, bb.bank_branch_name, :C_BANK_BRANCH_NAME_DSP) AND ba.bank_account_id = NVL(:P_BANK_ACCOUNT_ID, ba.bank_account_id) AND ba.account_classification = 'INTERNAL' AND to_char(sh.statement_date,'YYYY/MM/DD') >= nvl(to_char(:P_STAT_DATE_FROM,'YYYY/MM/DD'), to_char(sh.statement_date,'YYYY/MM/DD') ) AND to_char(sh.statement_date,'YYYY/MM/DD') <= nvl(to_char(:P_STAT_DATE_TO,'YYYY/MM/DD'), to_char(sh.statement_date,'YYYY/MM/DD') ) AND sh.statement_number >= nvl(:P_STAT_NUMBER_FROM,sh.statement_number) AND sh.statement_number <= nvl(:P_STAT_NUMBER_TO,sh.statement_number) ORDER BY 5 |
Parameter Name | SQL text | Validation | |
---|---|---|---|
Bank Branch Name |
|
LOV Oracle | |
Bank Account Number |
|
LOV Oracle | |
Statement Number From |
|
LOV Oracle | |
Statement Number To |
|
LOV Oracle | |
Statement Date From |
|
Date | |
Statement Date To |
|
Date |