ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN D45FEBAA5E2C46D2A17BF91F9017ACC0Y select null action_, null status_, null message_, null request_id_, null modified_columns_, x.* from ( select cshi.bank_account_num bank_account_number, cshi.statement_number, cshi.statement_date, cshi.currency_code currency, cshi.control_begin_balance, cshi.control_end_balance, csli.line_number, csli.trx_date transaction_date, csli.trx_code transaction_code, csli.amount, csli.bank_trx_number bank_transaction_number, csli.trx_text transaction_description, csli.invoice_text, csli.customer_text, csli.effective_date value_date, :auto_reconcile auto_reconcile, :gl_date gl_date, rowidtochar(cshi.rowid) header_row_id, rowidtochar(csli.rowid) line_row_id, row_number() over (order by cshi.bank_account_num,cshi.statement_number,csli.line_number) upload_row from ce_statement_headers_int cshi, ce_statement_lines_interface csli where 1=1 and nvl(cshi.record_status_flag,'I')<>'T' and cshi.bank_account_num=csli.bank_account_num(+) and cshi.statement_number=csli.statement_number(+) ) x Initial version. Loads bank statements through the bank statement open interface, runs the Bank Statement Import and optionally AutoReconciliation. U A xxen_ce_upload.upload_bank_statement xxen_ce_upload.do_ce_import xxen_ce_upload.validate_bank_statement( p_bank_account_number=>:$column$.bank_account_number, p_statement_number=>:$column$.statement_number ) US CE Bank Statement Upload CE Bank Statement Upload ======================== Loads bank statements (header and lines) into Oracle Cash Management through the bank statement open interface and runs Oracle's Bank Statement Import automatically after upload. Optionally, AutoReconciliation runs in the same step to match the imported statement lines against AR receipts, AP payments and GL journal lines according to the bank account's transaction codes and matching rules, in the same way as the standard 'Bank Statement Import & AutoReconciliation' concurrent program. Each row represents one statement line. Statement header information (Account Number, Statement Number, Statement Date, Currency, Control Balances) repeats on every row and the statement header is created once per statement number. Line numbers are assigned automatically per statement when left blank. Credit and debit control totals are computed automatically from the entered lines and their transaction code types. Upload Mode =========== Create ------ Opens an empty spreadsheet to enter new bank statements, for example pasted from a bank portal export. Create, Update -------------- Additionally returns statement lines that are still sitting in the bank statement open interface and did not import. Correct them and re-upload; corrected lines replace the previously staged interface records and are imported again. Notes ===== - Statements that are already imported into Cash Management cannot be loaded again under the same statement number. - Rows failing the import show the Cash Management interface error messages in the result file. The standard Bank Statement Import Execution Report provides the same details. Enginatics Upload 1=1 :auto_reconcile :gl_date 1 10 1=1 :p_upload_mode like '%'||xxen_upload.action_update LOV Blitz Upload Mode E811F942597A2075E0530100007FF4F2 Y select xxen_upload.action_create id, xxen_upload.action_meaning(xxen_upload.action_create) value, null description from dual union all select xxen_upload.action_create || ',' || xxen_upload.action_update id, xxen_upload.action_meaning(xxen_upload.action_create)||', '||xxen_upload.action_meaning(xxen_upload.action_update) value, null description from dual C Y US Upload Mode 2 20 :auto_reconcile LOV Oracle Yes_No Y select lookup_code id, meaning value, null description from fnd_lookups where fnd_lookups.lookup_type='YES_NO' order by value,description N Y US AutoReconciliation Set to Yes to run Oracle's AutoReconciliation right after the Bank Statement Import, matching the imported statement lines against open transactions. 3 25 :gl_date Date US GL Date Accounting date for the import and for AutoReconciliation clearing accounting. Defaults to the latest uploaded statement date when left blank. 4 30 1=1 cshi.bank_account_num=:bank_account_number LOV custom Y select cba.bank_account_num value, cba.bank_account_name||' ('||cbbv.bank_name||' '||cbbv.bank_branch_name||', '||cba.currency_code||')' description from ce_bank_accounts cba, ce_bank_branches_v cbbv where cba.bank_branch_id=cbbv.branch_party_id(+) order by cba.bank_account_num US Account Number Restricts the downloaded interface statement lines to one bank account in Create, Update mode. 5 40 1=1 cshi.statement_number=:statement_number Char US Statement Number Restricts the downloaded interface statement lines to one statement number in Create, Update mode. 1 ACTION_ Char varchar2 2 STATUS_ Char varchar2 3 MESSAGE_ Char varchar2 4 REQUEST_ID_ Char varchar2 Y 5 MODIFIED_COLUMNS_ Char varchar2 Y 6 BANK_ACCOUNT_NUMBER LOV custom varchar2 30 Y select cba.bank_account_num value, cba.bank_account_name||' ('||cbbv.bank_name||' '||cbbv.bank_branch_name||', '||cba.currency_code||')' description from ce_bank_accounts cba, ce_bank_branches_v cbbv where cba.bank_branch_id=cbbv.branch_party_id(+) order by cba.bank_account_num Y 7 STATEMENT_NUMBER Char varchar2 50 Y 8 STATEMENT_DATE Date date Y 9 CURRENCY LOV custom varchar2 15 Y select fcv.currency_code value, fcv.name description from fnd_currencies_vl fcv where fcv.enabled_flag='Y' and fcv.currency_flag='Y' order by fcv.currency_code select distinct cba.currency_code from ce_bank_accounts cba where cba.bank_account_num=:$column$.bank_account_number 10 CONTROL_BEGIN_BALANCE Number number 11 CONTROL_END_BALANCE Number number 12 LINE_NUMBER Number number 13 TRANSACTION_DATE Date date Y 14 TRANSACTION_CODE LOV custom varchar2 30 Y select ctc.trx_code value, min(xxen_util.meaning(ctc.trx_type,'BANK_TRX_TYPE',260)||': '||ctc.description) description from ce_transaction_codes ctc, ce_bank_accounts cba where cba.bank_account_num=:$column$.bank_account_number and ctc.bank_account_id=cba.bank_account_id group by ctc.trx_code order by ctc.trx_code 15 AMOUNT Number number Y 16 BANK_TRANSACTION_NUMBER Char varchar2 240 17 TRANSACTION_DESCRIPTION Char varchar2 255 18 INVOICE_TEXT Char varchar2 70 19 CUSTOMER_TEXT Char varchar2 80 20 VALUE_DATE Date date 21 AUTO_RECONCILE Char varchar2 1 :$flex$.AutoReconciliation Y 22 GL_DATE Date date :$flex$.GL_Date Y 23 HEADER_ROW_ID Char varchar2 Y Y 24 LINE_ROW_ID Char varchar2 Y Y 25 UPLOAD_ROW Number number Y U 1 status_ out Char STATUS_CODE_ U 2 message_ out Char MESSAGE_ U 3 request_id_ out Number REQUEST_ID_ U 4 p_bank_account_number in Char BANK_ACCOUNT_NUMBER U 5 p_statement_number in Char STATEMENT_NUMBER U 6 p_statement_date in Date STATEMENT_DATE U 7 p_currency in Char Y U 8 p_control_begin_balance in Number Y U 9 p_control_end_balance in Number Y U 10 p_line_number in Number Y LINE_NUMBER U 11 p_transaction_date in Date TRANSACTION_DATE U 12 p_transaction_code in Char Y TRANSACTION_CODE U 13 p_amount in Number AMOUNT U 14 p_bank_transaction_number in Char Y U 15 p_transaction_description in Char Y TRANSACTION_DESCRIPTION U 16 p_invoice_text in Char Y INVOICE_TEXT U 17 p_customer_text in Char Y CUSTOMER_TEXT U 18 p_value_date in Date Y U 19 p_header_row_id in Char Y U 20 p_line_row_id in Char Y U 21 p_header_row_id_out out Char U 22 p_line_row_id_out out Char U 23 p_line_number_out out Number P 1 status_ out Char STATUS_CODE_ P 2 message_ out Char MESSAGE_ P 3 request_id_ out Number REQUEST_ID_ P 4 p_bank_account_number in Char BANK_ACCOUNT_NUMBER P 5 p_auto_reconcile in Char AUTO_RECONCILE P 6 p_gl_date in Date GL_DATE Error SQL select xu.action_, xxen_upload.status_meaning(xxen_upload.status_error) status_, coalesce( ( select substr(listagg(nvl(fnm.message_text,clie.message_name),chr(10)) within group (order by clie.rowid),1,4000) from ce_line_interface_errors clie, fnd_new_messages fnm where clie.bank_account_num=xu.bank_account_number and clie.statement_number=xu.statement_number and clie.line_number=xu.line_number and clie.message_name=fnm.message_name(+) and fnm.application_id(+)=260 and fnm.language_code(+)=userenv('lang') ), ( select substr(listagg(nvl(fnm.message_text,chie.message_name),chr(10)) within group (order by chie.rowid),1,4000) from ce_header_interface_errors chie, fnd_new_messages fnm where chie.bank_account_num=xu.bank_account_number and chie.statement_number=xu.statement_number and chie.message_name=fnm.message_name(+) and fnm.application_id(+)=260 and fnm.language_code(+)=userenv('lang') ), xu.message_, 'Statement line was not imported. Check the Bank Statement Import Execution report.' ) message_, xu.request_id_, xu.modified_columns_, xu.bank_account_number, xu.statement_number, xu.statement_date, xu.currency, xu.control_begin_balance, xu.control_end_balance, xu.line_number, xu.transaction_date, xu.transaction_code, xu.amount, xu.bank_transaction_number, xu.transaction_description, xu.invoice_text, xu.customer_text, xu.value_date, xu.auto_reconcile, xu.gl_date, xu.header_row_id, xu.line_row_id, xu.upload_row from xxen_ce_bank_statement_2985_u xu where nvl(xu.status_code_,xxen_upload.status_error)<>xxen_upload.status_success or not exists ( select null from fnd_concurrent_requests fcr, ce_bank_accounts cba, ce_statement_headers csh, ce_statement_lines csl where to_number(xu.request_id_)=fcr.request_id and xu.bank_account_number=cba.bank_account_num and cba.bank_account_id=csh.bank_account_id and xu.statement_number=csh.statement_number and csh.statement_header_id=csl.statement_header_id and xu.line_number=csl.line_number and csl.creation_date>=fcr.actual_start_date ) Order By upload_row, bank_account_number, statement_number, line_number Success SQL select xu.action_, xxen_upload.status_meaning(xu.status_code_) status_, 'Statement line imported. Line status: '||xxen_util.meaning(csl.status,'STATEMENT_LINE_STATUS',260)||'.' message_, xu.request_id_, xu.modified_columns_, cba.bank_account_num bank_account_number, csh.statement_number, csh.statement_date, nvl(csh.currency_code,cba.currency_code) currency, csh.control_begin_balance, csh.control_end_balance, csl.line_number, csl.trx_date transaction_date, coalesce(csl.trx_code,(select ctc.trx_code from ce_transaction_codes ctc where csl.trx_code_id=ctc.transaction_code_id)) transaction_code, csl.amount, csl.bank_trx_number bank_transaction_number, csl.trx_text transaction_description, csl.invoice_text, csl.customer_text, csl.effective_date value_date, xu.auto_reconcile, xu.gl_date, xu.header_row_id, xu.line_row_id, xu.upload_row from xxen_ce_bank_statement_2985_u xu, fnd_concurrent_requests fcr, ce_bank_accounts cba, ce_statement_headers csh, ce_statement_lines csl where xu.status_code_=xxen_upload.status_success and to_number(xu.request_id_)=fcr.request_id and xu.bank_account_number=cba.bank_account_num and cba.bank_account_id=csh.bank_account_id and xu.statement_number=csh.statement_number and csh.statement_header_id=csl.statement_header_id and xu.line_number=csl.line_number and csl.creation_date>=fcr.actual_start_date