Fusion AR Lockbox Integration with OIC

What is Lockbox in Oracle Fusion Receivables? (Receivables Auto Lockbox)

Lockbox is a service offering from banks to enterprises who have large customer base and have various payment agreements.

Basically instead to directly paying to the enterprise the customer pay to the banks and banks deposit in the company’s bank account.

This is used for bulk receipt handling from different customers.

Oracle Fusion Cloud Receivables provides a FBDI template to enter the Payment details from different customers which can be upload to Cloud receivables to create Receipts and Accounting.

There are different formats in which the File is sent by the bank to companies that have to be consumed in Fusion receivables.

Refer to this documentation for more details.

LockBox Process Flow

Once valid data set is entered in the 9 sheet of the xslm Lockbox template it has to generate a zip file which has the data in CSV format. This file needs to be uploaded to UCM and a ESS job need to be run to process the file to create receipts.

Let’s break down the Lockbox Integration with OIC process in steps.

• Lockbox FBDI File to be entered
• Hit Generate File on First Sheet of the xsl template
• Save the zip file in a local folder . This file has a CSV file inside.
• Upload the File to UCM. Go to Navitagor > Tools> Import and Export>

• Run ESS Job Load Interface File for Import
• In that you can Select the Generate Upload File in UCM
• Run Process Receipts through lockbox with required parameters.

A Successful run would create Receipts in Receivables.

ESS jobs

The Ess Jobs and Sub process which are going to run

Using OIC as Integration tool to Automate the process

There will be scenarios to automate the lockbox FBDI file to upload to Fusion AR. To achieve that using OIC the approach would be

Assuming you have a UI to enter the basic values to be put in Excel template , and with the data entered we need to create a Lockbox zip with CSV file.

The idea would be to use Each Sheet (1 – 9) as a schema in OIC and use stage file to create and append the file sheet by sheet.

Something like this.

Read sheet 1 data > Write Sheet 1 data to stage file X.CSV

Read Sheet 2 Data > Append Sheet 2 data to stage file X.CSV

.

.

Read Sheet 9 Data > Write Sheet 9 data to stage file X.CSV

Finally Zip the file X.CSV and upload to UCM via ERP adapter and Call ESS job Process Receipts through lockbox.

Stage File configuration

Creating CSV Schema for each sheet.

Save the sheet headers as CSV file for each sheet , so you will have 8 CSV file from Record1.csv to Record9.csv

Record 1 – Transmission Header CSV Schema

Copy the below format in notepad and save as Record 1 – Transmission Header schema file (e.g Record1.csv)

Record Type,Destination Bank Account,Bank Origination Number,Deposit Date,Deposit Time,Transmission Record Count,Transmission Amount,Payment Interface Flexfield Segment 1,Payment Interface Flexfield Segment 2,Payment Interface Flexfield Segment 3,Payment Interface Flexfield Segment 4,Payment Interface Flexfield Segment 5,Payment Interface Flexfield Segment 6,Payment Interface Flexfield Segment 7,Payment Interface Flexfield Segment 8,Payment Interface Flexfield Segment 9,Payment Interface Flexfield Segment 10,Payment Interface Flexfield Segment 11,Payment Interface Flexfield Segment 12,Payment Interface Flexfield Segment 13,Payment Interface Flexfield Segment 14,Payment Interface Flexfield Segment 15,END
1,,,,,,,,,,,,,,,,,,,,,,END

Record 2 – Service Header CSV schema

Copy the below format in notepad and save as Record 1 – Transmission Header schema file (e.g Record1.csv)

Record Type,Deposit Date,Deposit Time,Destination Bank Account,Bank Origination Number,Payment Interface Flexfield Segment 1,Payment Interface Flexfield Segment 2,Payment Interface Flexfield Segment 3,Payment Interface Flexfield Segment 4,Payment Interface Flexfield Segment 5,Payment Interface Flexfield Segment 6,Payment Interface Flexfield Segment 7,Payment Interface Flexfield Segment 8,Payment Interface Flexfield Segment 9,Payment Interface Flexfield Segment 10,Payment Interface Flexfield Segment 11,Payment Interface Flexfield Segment 12,Payment Interface Flexfield Segment 13,Payment Interface Flexfield Segment 14,Payment Interface Flexfield Segment 15,END
2,,,,,,,,,,,,,,,,,,,,END

Record 3 – Batch Header

Copy the below format in notepad and save as Record 1 – Transmission Header schema file (e.g Record1.csv)

Record Type,Batch Name,Currency,Conversion Rate Type,Conversion Rate,Receipt Method,Lockbox Number,Deposit Date,Deposit Time,Batch Record Count,Batch Amount,Comments,Payment Interface Flexfield Segment 1,Payment Interface Flexfield Segment 2,Payment Interface Flexfield Segment 3,Payment Interface Flexfield Segment 4,Payment Interface Flexfield Segment 5,Payment Interface Flexfield Segment 6,Payment Interface Flexfield Segment 7,Payment Interface Flexfield Segment 8,Payment Interface Flexfield Segment 9,Payment Interface Flexfield Segment 10,Payment Interface Flexfield Segment 11,Payment Interface Flexfield Segment 12,Payment Interface Flexfield Segment 13,Payment Interface Flexfield Segment 14,Payment Interface Flexfield Segment 15,Payment Interface Flexfield Context,END
3,FM001,USD,Corporate,,EFT (Automatic),44336,210427,,1,132.00,,,,,,,,,,,,,,,,,,END

Record 4 – Overflow Payment

Copy the below format in notepad and save as Record 4 – Overflow Payment schema file (e.g Record1.csv)

Record Type,Batch Name,*Item Number,Overflow Indicator,*Overflow Sequence,Transit Routing Number,Customer Bank Account,Currency,Conversion Rate Type,Conversion Rate,Customer Account Number,Customer Site,Customer Bank Branch,Customer Bank,Lockbox Number,Deposit Date,Deposit Time,Transaction Reference 1,Transaction Installment 1,Transaction Reference Date 1,Transaction Currency 1,Cross-Currency Rate 1,Applied Amount 1,Allocated Receipt Amount 1,Customer Reference 1,Transaction Reference 2,Transaction Installment 2,Transaction Reference Date 2,Transaction Currency 2,Cross-Currency Rate 2,Applied Amount 2,Allocated Receipt Amount 2,Customer Reference 2,Transaction Reference 3,Transaction Installment 3,Transaction Reference Date 3,Transaction Currency 3,Cross-Currency Rate 3,Applied Amount 3,Allocated Receipt Amount 3,Customer Reference 3,Transaction Reference 4,Transaction Installment 4,Transaction Reference Date 4,Transaction Currency 4,Cross-Currency Rate 4,Applied Amount 4,Allocated Receipt Amount 4,Customer Reference 4,Transaction Reference 5,Transaction Installment 5,Transaction Reference Date 5,Transaction Currency 5,Cross-Currency Rate 5,Applied Amount 5,Allocated Receipt Amount 5,Customer Reference 5,Transaction Reference 6,Transaction Installment 6,Transaction Reference Date 6,Transaction Currency 6,Cross-Currency Rate 6,Applied Amount 6,Allocated Receipt Amount 6,Customer Reference 6,Transaction Reference 7,Transaction Installment 7,Transaction Reference Date 7,Transaction Currency 7,Cross-Currency Rate 7,Applied Amount 7,Allocated Receipt Amount 7,Customer Reference 7,Transaction Reference 8,Transaction Installment 8,Transaction Reference Date 8,Transaction Currency 8,Cross-Currency Rate 8,Applied Amount 8,Allocated Receipt Amount 8,Customer Reference 8,Payment Interface Flexfield Segment 1,Payment Interface Flexfield Segment 2,Payment Interface Flexfield Segment 3,Payment Interface Flexfield Segment 4,Payment Interface Flexfield Segment 5,Payment Interface Flexfield Segment 6,Payment Interface Flexfield Segment 7,Payment Interface Flexfield Segment 8,Payment Interface Flexfield Segment 9,Payment Interface Flexfield Segment 10,Payment Interface Flexfield Segment 11,Payment Interface Flexfield Segment 12,Payment Interface Flexfield Segment 13,Payment Interface Flexfield Segment 14,Payment Interface Flexfield Segment 15,END
4,,1,,1,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,END

Record 5 – Lockbox Header

Copy the below format in notepad and save as Record 5 – Lockbox Header schema file (e.g Record1.csv)

Record Type,Currency,Receipt Method,Lockbox Number,Deposit Date,Deposit Time,Lockbox Batch Count,Lockbox Record Count,Lockbox Amount,Destination Bank Account,Bank Origination Number,Payment Interface Flexfield Segment 1,Payment Interface Flexfield Segment 2,Payment Interface Flexfield Segment 3,Payment Interface Flexfield Segment 4,Payment Interface Flexfield Segment 5,Payment Interface Flexfield Segment 6,Payment Interface Flexfield Segment 7,Payment Interface Flexfield Segment 8,Payment Interface Flexfield Segment 9,Payment Interface Flexfield Segment 10,Payment Interface Flexfield Segment 11,Payment Interface Flexfield Segment 12,Payment Interface Flexfield Segment 13,Payment Interface Flexfield Segment 14,Payment Interface Flexfield Segment 15,END
5,USD,EFT (Automatic),44336,,,,,,,287,,,,,,,,,,,,,,,,END

Record 6 – Payment

Copy the below format in notepad and save as Record 6 – Payment schema file (e.g Record1.csv)

Record Type,Batch Name,Item Number,Remittance Amount,Transit Routing Number,Customer Bank Account,Receipt Number,R
eceipt Date,Currency,Conversion Rate Type,Conversion Rate,Customer Account Number,Customer Site,Customer Bank Branch,Customer Bank,Receipt Method,Remittance Bank Branch,Remittance Bank,Lockbox Number,Deposit Date,Deposit Time,Anticipated Clearing Date,Transaction Reference 1,Transaction Installment 1,Transaction Reference Date 1,Transaction Currency 1,Cross-Currency Rate 1,Applied Amount 1,Allocated Receipt Amount 1,Customer Reference 1,Transaction Reference 2,Transaction Installment 2,Transaction Reference Date 2,Transaction Currency 2,Cross-Currency Rate 2,Applied Amount 2,Allocated Receipt Amount 2,Customer Reference 2,Transaction Reference 3,Transaction Installment 3,Transaction Reference Date 3,Transaction Currency 3,Cross-Currency Rate 3,Applied Amount 3,Allocated Receipt Amount 3,Customer Reference 3,Transaction Reference 4,Transaction Installment 4,Transaction Reference Date 4,Transaction Currency 4,Cross-Currency Rate 4,Applied Amount 4,Allocated Receipt Amount 4,Customer Reference 4,Transaction Reference 5,Transaction Installment 5,Transaction Reference Date 5,Transaction Currency 5,Cross-Currency Rate 5,Applied Amount 5,Allocated Receipt Amount 5,Customer Reference 5,Transaction Reference 6,Transaction Installment 6,Transaction Reference Date 6,Transaction Currency 6,Cross-Currency Rate 6,Applied Amount 6,Allocated Receipt Amount 6,Customer Reference 6,Transaction Reference 7,Transaction Installment 7,Transaction Reference Date 7,Transaction Currency 7,Cross-Currency Rate 7,Applied Amount 7,Allocated Receipt Amount 7,Customer Reference 7,Transaction Reference 8,Transaction Installment 8,Transaction Reference Date 8,Transaction Currency 8,Cross-Currency Rate 8,Applied Amount 8,Allocated Receipt Amount 8,Customer Reference 8,Comments,Payment Interface Flexfield Segment 1,Payment Interface Flexfield Segment 2,Payment Interface Flexfield Segment 3,Payment Interface Flexfield Segment 4,Payment Interface Flexfield Segment 5,Payment Interface Flexfield Segment 6,Payment Interface Flexfield Segment 7,Payment Interface Flexfield Segment 8,Payment Interface Flexfield Segment 9,Payment Interface Flexfield Segment 10,Payment Interface Flexfield Segment 11,Payment Interface Flexfield Segment 12,Payment Interface Flexfield Segment 13,Payment Interface Flexfield Segment 14,Payment Interface Flexfield Segment 15,Payment Interface Flexfield Context,END
6,FM001,1,132.00,,,FM102,210427,USD,Corporate,,59300,,,,EFT (Automatic),,,,210427,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,END

Record 7 – Batch Trailer

Copy the below format in notepad and save as Record 7 – Batch Trailer schema file (e.g Record1.csv)

Record Type,*Batch Name,Currency,Conversion Rate Type,Conversion Rate,Receipt Method,Lockbox Number,Deposit Date,Deposit Time,Batch Record Count,Batch Amount,Comments,Payment Interface Flexfield Segment 1,Payment Interface Flexfield Segment 2,Payment Interface Flexfield Segment 3,Payment Interface Flexfield Segment 4,Payment Interface Flexfield Segment 5,Payment Interface Flexfield Segment 6,Payment Interface Flexfield Segment 7,Payment Interface Flexfield Segment 8,Payment Interface Flexfield Segment 9,Payment Interface Flexfield Segment 10,Payment Interface Flexfield Segment 11,Payment Interface Flexfield Segment 12,Payment Interface Flexfield Segment 13,Payment Interface Flexfield Segment 14,Payment Interface Flexfield Segment 15,Payment Interface Flexfield Context,END
7,FM001,USD,Corporate,,EFT (Automatic),44336,210427,,1,5016.00,,,,,,,,,,,,,,,,,,END

Record 8 – Lockbox Trailer

Copy the below format in notepad and save as Record 8 – Lockbox Trailer schema file (e.g Record1.csv)

Record Type,Currency,Receipt Method,*Lockbox Number,Deposit Date,Deposit Time,Lockbox Batch Count,Lockbox Record Count,Lockbox Amount,Destination Bank Account,Bank Origination Number,Payment Interface Flexfield Segment 1,Payment Interface Flexfield Segment 2,Payment Interface Flexfield Segment 3,Payment Interface Flexfield Segment 4,Payment Interface Flexfield Segment 5,Payment Interface Flexfield Segment 6,Payment Interface Flexfield Segment 7,Payment Interface Flexfield Segment 8,Payment Interface Flexfield Segment 9,Payment Interface Flexfield Segment 10,Payment Interface Flexfield Segment 11,Payment Interface Flexfield Segment 12,Payment Interface Flexfield Segment 13,Payment Interface Flexfield Segment 14,Payment Interface Flexfield Segment 15,END
8,,EFT (Automatic),44336,,,,,,,287,,,,,,,,,,,,,,,,END

Record 9 – Transmission Trailer

Copy the below format in notepad and save as Record 9 – Transmission Trailer schema file (e.g Record1.csv)

Record Type,Destination Bank Account,Bank Origination Number,Deposit Date,Deposit Time,Transmission Record Count,Transmission Amount,Payment Interface Flexfield Segment 1,Payment Interface Flexfield Segment 2,Payment Interface Flexfield Segment 3,Payment Interface Flexfield Segment 4,Payment Interface Flexfield Segment 5,Payment Interface Flexfield Segment 6,Payment Interface Flexfield Segment 7,Payment Interface Flexfield Segment 8,Payment Interface Flexfield Segment 9,Payment Interface Flexfield Segment 10,Payment Interface Flexfield Segment 11,Payment Interface Flexfield Segment 12,Payment Interface Flexfield Segment 13,Payment Interface Flexfield Segment 14,Payment Interface Flexfield Segment 15,END
9,,,,,,,,,,,,,,,,,,,,,,END

A correctly generated CSV file with basic minimum data will look like this

How to Run the 2nd ESS job Process Receipts Through Lockbox Integration in OIC

To Run the Process Receipts through lockbox ESS job automatically a valid properties files need to be created.

You can enter {req_id} as a parameter value for the process id in the manifest file – it will automatically take the value at runtime.

Creating properties File

The file name should be of pattern similar to zip file.

eg A.Zip will have A.properties

Query the ESS Job in FSM > Manage Enterprise Scheduler Job Definitions and Job Sets for Financial, Supply Chain Management, and Related Applications > Process Receipts Through Lockbox

How to Create Properties File Schema

The importBulkData operation requires a property file to be submitted, which includes the job definition, package names, and job parameters of the object being imported. As part of the data ZIP file, you need to generate and include the Job Properties File.

The Job Properties File is formatted as a CSV file with the following structure:

<job package name>, <job definition name>, <ZIP file prefix>, <Param1>, …, <ParamN>

Note the Path and Job package name are same . check in user properties tab

You can choose any name for the file, but the extension must be “.properties”.

Copy the below format in notepad and save as schema file (e.g properties_schema.csv)

path,Jobname,zipname,Param1,Param2,Param3,param4,Param5,Param6,Param7,param8,Param9,Param10,Param11,param12,Param13,Param14,Param15,param16,Param17

Sample data for properties file

/oracle/apps/ess/financials/receivables/receipts/lockboxes/,ProcessLockboxesMasterEss,WPSlockbox,Y,{req_id},TR2,N,,,105,Y,300000000043,20-05-04,A,N,N,N,N,3333333333,1

Create the properties as per the image. Param1 to Param 17 can be seen from the ESS Job which is Run

Log File of Successful completion of ESS job

********************Input Parameters*************************

New Transmission                  : Y

Transmission Id                   : 

Original Request Id               : 2944223

Transmission Name                 : TR1

Submit Import                     : N

Data File                         : 

Control File                      : 

Transmission Format Id            : 105

Submit Validation                 : Y

Lockbox Id                        : 300000047311686

Accounting Date                   : 2020-05-04

Report Format                     : A

Complete Batches Only             : N

Pay Unrelated Invoices            : N

Allow Invalid Trx Number          : N

Submit PostBatch                  : N

Org Id                            : 300000046987012

Submission Type                   : S

Total Workers                     : 1

*************************************************************

 

Submitting Execution report 2944233

Troubleshooting Errors

There are various errors which we can face , we will need to review error logs to fix them.

The output to the run is pushed to UCM with file name ImportBulkData_processLockboxesMasterEss_RequestID.ZIP. When we download to local system and extract the zip we see all the logs and failed records.

Sample Working LockBox FBDI Worksheet

Here is a sample working lockbox FBDI . You will need to change the value as per your master data existing in your environment like Lockbox number, Batch number, Bank, Receipt Number etc.

This is macros disabled file , copy the sheet values to the latest FBDI template downloaded from Oracle documentation.