Oracle Cash Management Bank Statement Interface

How to upload paid data files into Oracle Cash Management

Here i will explain how to upload paid data from data file into Oracle Cash Management via interface tables for reconciliation using 'Bank Statement Import & AutoReconciliation program'

Steps to perform-

• Input paid files(header+trailer) from bank will come to unix directory
• Load records from files to temp tables(header+trailer) using sql loader
• Do some validation if required
• After succesful validation load data from temp tables to Oracle Cash Management interface tables

          CE_STATEMENT_HEADERS_INT_ALL
          CE_STATEMENT_LINES_INTERFACE

• Launch standard import program to load data into Oracle base tables and reconcile with Oracle Account Payables

Here we have used fixed length based data file. This file we were getting daily from bank containing that days trasaction

Format of data :

Header file-
Bank Account Number position (1:13)
Bank Transaction Number(Check Number on AP) postion (14:23)
Check Amount Position (24:33)
Bank Clearing Date Position  (34:41)

Trailer File-
Day of month position (1:8)
Total Transaction count position (21:30)
Totol Amount Cleared position  (9:20)

Temp Tables-

XX_TEMP_CM_IMPORT_TBL

Load data into this table from header file using sql loader.
Populate batch_id with sequence value. To assign one unique value to each data file.
U can populate status column with N

 XX_TEMP_TRAILER_TBL

Load data into this table from trailer file using sql loader.
Populate batch_id with above sequence value. To link header and trailer.
Source ciolumn u can keep blank.

If required you can perform some control level validation if required.

After successful validations, you can go for insertion into interface tables.

-- Insert the Header Record

  -- Added control_begin_balance always 0
  -- Changed control_total_cr to control_total_dr

--Insert 1 record into header interface table by pulling data from trailer table,
You can dervie ,BANK_ACCOUNT_NUM,,BANK_NAME,,BANK_BRANCH_NAME by making distinct on header table.
--Populate remining column as per your requirement

SELECT MONTH_DATE
       INTO V_STATEMENT_DATE
       FROM XX_TEMP_TRAILER_TBL
       WHERE BATCH_ID = P_BATCH_ID;


 INSERT INTO CE.CE_STATEMENT_HEADERS_INT_ALL
       (
      STATEMENT_NUMBER
     ,BANK_ACCOUNT_NUM
     ,BANK_NAME
     ,BANK_BRANCH_NAME
     ,STATEMENT_DATE
     ,CURRENCY_CODE
     ,CONTROL_BEGIN_BALANCE
     ,CONTROL_TOTAL_DR
     ,CONTROL_END_BALANCE
     ,CREATED_BY
     ,CREATION_DATE
     ,LAST_UPDATED_BY
     ,LAST_UPDATE_DATE
     ,ORG_ID
     ,RECORD_STATUS_FLAG
      )
VALUES
      (
     TO_CHAR(TO_DATE(V_STATEMENT_DATE, 'MM/DD/YY'), 'YYYY-MM-DD')--Keep Unique
     ,v_BANK_ACCOUNT_NUM
     ,V_BANK_NAME
     ,V_BRANCH_NAME
     ,TO_DATE(V_STATEMENT_DATE, 'MM/DD/YY') --Keep unique
     ,'USD'
     ,0
     ,P_TOTAL_DOLLAR
     ,-P_TOTAL_DOLLAR
     ,1
     ,SYSDATE
     ,1
     ,SYSDATE
     ,2 -- ORG ID DEFAULTED
     ,'N'
       );

Create cusror on temp table XX_TEMP_CM_IMPORT_TBL

CURSOR curheader(c_batch_id IN xx_temp_trailer_tbl.batch_id%TYPE) IS

    SELECT otcit.rowid

          ,otcit.bank_account_num

          ,otcit.bank_trx_num

          ,otcit.amount

          ,otcit.trx_date

   FROM xx_temp_cm_import_tbl otcit

   WHERE otcit.batch_id = c_batch_id;

-- INSERT THE LINE RECORD

INSERT INTO CE.CE_STATEMENT_LINES_INTERFACE
                (
   STATEMENT_NUMBER
   ,BANK_ACCOUNT_NUM
   ,BANK_TRX_NUMBER
   ,LINE_NUMBER
   ,TRX_DATE
   ,TRX_CODE
   ,AMOUNT
   ,CREATED_BY
   ,CREATION_DATE
   ,LAST_UPDATED_BY
   ,LAST_UPDATE_DATE
             )
VALUES
          (
   TO_CHAR(TO_DATE(V_STATEMENT_DATE, 'MM/DD/YY'), 'YYYY-MM-DD')--pass like above
   ,CE_LINE_REC.BANK_ACCOUNT_NUM
   ,CE_LINE_REC.BANK_TRX_NUMBER
   ,V_LINE_COUNT   --create variable n increment by 1 in cursor.1,2,3,4,......
   ,CE_LINE_REC.TRX_DATE
   ,'PMT'
   ,CE_LINE_REC.AMOUNT
   ,1
   ,SYSDATE
   ,1
   ,SYSDATE);

After insertion into interface tables, go for import program launch 'Bank Statement Import and AutoReconciliation program'

Variables Declaration:

   l_req_id fnd_concurrent_requests.request_id%TYPE;
   l_option   varchar2(15) := 'ZALL';
   l_statement_number  varchar2(15);
   l_statement_number_from varchar2(15);
   l_statement_number_to varchar2(15);
   l_statement_date  varchar2(15);
   l_statement_date_from varchar2(15);
   l_statement_date_to   varchar2(15);
   l_gl_date    varchar2(19);
   l_bank_branch_id             number;
   l_bank_account_id  number;
   l_bank_account_num  number;
   l_bank_account_name  varchar2(50);
   l_nsf_handling  varchar2(15) := 'NO_ACTION';
   l_debug   varchar2(15) := 'N';
   l_batch_id   number;
   l_ret_value                  boolean;
   l_phase  varchar2(15);
   l_status varchar2(15);
   l_devstatus varchar2(15);
   l_devphase  varchar2(15);
   l_message   varchar2(200);
   l_ret  boolean;


select to_char(sysdate, 'YYYY/MM/DD')||' '||'00:00:00'
   into l_gl_date
   from dual;
  
select max(batch_id)
  into l_batch_id
from xx_temp_cm_import_tbl
where status_ind = 'C';

 select bank_account_num
  into l_bank_account_num
  from xx_temp_cm_import_tbl
  where status_ind = 'C'
  and batch_id = l_batch_id
  group by bank_account_num;

  select a.bank_account_id,a.bank_account_num,a.bank_account_name,b.bank_branch_id
  into l_bank_account_id,l_bank_account_num,l_bank_account_name,l_bank_branch_id
  from ap.ap_bank_accounts_all a ,ap.ap_bank_branches b
  where
    a.bank_branch_id= b.bank_branch_id
    and     a.bank_account_num =
     (select distinct bank_account_num
      from ocap_temp_cm_import_tbl c
      where a.bank_account_num = c.bank_account_num
      and   batch_id = l_batch_id);

select max(statement_number), to_char(statement_date, 'DD-MON-YYYY')
   into l_statement_number, l_statement_date
   from ce.ce_statement_headers_int_all a
   where bank_account_num in    (select bank_account_num
      from ocap_temp_cm_import_tbl b
      where a.bank_account_num = b.bank_account_num
      and b.batch_id = l_batch_id)
   and record_status_flag = 'N'
   group by statement_date;

  l_statement_number_from := l_statement_number;
  l_statement_number_to := l_statement_number;
  l_statement_date_from := l_statement_date;
  l_statement_date_to := l_statement_date;
 
l_req_id := fnd_request.submit_request (
               'CE',
               'ARPLABIR',  --'Bank Statement Import & AutoReconciliation' program
               NULL,
               NULL,
               FALSE,
               l_option,
               l_bank_branch_id,
               l_bank_account_id,
               l_statement_number_from,
               l_statement_number_to,
               NULL,
               NULL,
               l_gl_date,
               NULL,
               NULL,
               l_nsf_handling,
               l_debug,
               NULL,
               NULL);

  l_ret := fnd_concurrent.wait_for_request
              (  request_id => l_req_id,
                         phase => l_phase,
                         status => l_status,
                 dev_phase => l_devphase,
                 dev_status => l_devstatus,
                    message => l_message);

After successful completion data will load into base tables.

• CE_STATEMENT_HEADERS_ALL
• CE_STATEMENT_LINES

 select * from ce.CE_STATEMENT_HEADERS_ALL  order by statement_header_id desc;;

select * from ce.CE_STATEMENT_LINES order by statement_header_id desc;;

 

A bank statement line’s status can be one of the following:
• Reconciled: Some payments, receipts, or miscellaneous transactions have been matched against this line. The statement line may not be fully reconciled.
• Unreconciled: No transactions have been matched.
• Error: This status is a warning that the line cannot be reconciled because of a bank error in the statement that cannot be resolved.The transaction will remain unreconcilable because there is no
corresponding transaction to which the line can be reconciled.
• External: The line does not correspond to any transaction in

After successful reconcillition, Oracle Account payables data will get updated.
It will update satus_lookup_code and cleared_date of ap_checks_all table.

select status_lookup_code,cleared_date,a.* from ap.ap_checks_all a order by check_id desc;