1. Using Financials for the Americas
  2. Electronic File Transfer for Payments

Electronic File Transfer for Payments

Banks in Brazil offer companies the ability to exchange electronic files with the banks to perform payments. An electronic file consists of a set of records with information related to the payment types such as direct account credit, available electronic transfer (TED), credit order document (DOC), collection document payment, and concessionaire payment.

A standard flat file layout (CNAB standard) defined for the banks is used. However, the banks may create different layouts to achieve their needs.

The Electronic File Transfer process includes:

  • Configuring and generating electronic payment files.
  • Electronic payment file structure.
  • Integration - Creating electronic payment files to payer bank specified formats.
  • Bank return files import

Configuring and Generating Electronic Payment Files

The Create and Extract Brazil Electronic Payment Files process is composed of a onetime configuration that provides all the required configuration data. You can run the Payment Process Request process to create payment files.

This image outlines the overall steps to generate electronic payment files:This image lists the steps to generate electronic payment files

The main configuration steps to configure and generate electronic payment files for Brazil are as follows:

  • Payment formats for Brazil
  • Payment methods for Brazil
  • Payment process profile

    Once the configuration is done, the Submit Payment Process Request job can be executed.

Brazil Electronic Payment File Structure

The flat file layout can be split into three parts:

  • File Header Register: One line in the file identified by 0. The header has the company information.
  • Services Batches: Contains detailed information of payments made to each third-party supplier. Services batches can be more than one line in the file and are represented by these three parts:
    • Batch Service Header Register identified by 1.
    • Batch Service Detail Register identified by 3.
    • Batch Service Trailer Register identified by 5.
      Note: One Batch Service Header Register can be only one payment method in the batch, so if the first-party has two payments in the EFT, one DOC and another TED, the flat file has two Batch Service Header Register for each payment type. However, if the first-party has two payments in the EFT, but the payment method is the same, in this case DOC, the flat file has one Batch Service Header Register.
  • File Trailer Register: One line in the file identified by 9.

Additional Fields Included on Electronic Payment File

This table provides the Brazil Banks CNAB standard fields in addition to the regular fields present in Oracle Fusion Cloud Payment Files so that both users and implementors can have a full set of data to create files.

XML Element Description
JLBR_PAYER_BRANCH_NUM Internal Bank Branch Number
JLBR_PAYER_BRANCH_DGT Internal Bank Branch Digit Code
JLBR_PAYER_ACCT_NUM Internal Account Number
JLBR_PAYER_ACCT_DGT Internal Account Digit Code
JLBR_BANK_S File Sequence
JLBR_COMPLEMENT_ACCT Complementary Account Number
JLBR_OU_REG_NUMBER First Party Registration Number (CNPJ)
JLBR_COUNT_CR_CC Number of lines for Direct Supplier Bank Account Transfer
JLBR_COUNT_CR_CC_TIT Number of lines for Direct Supplier Bank Account Transfer (same root CNPJ)
JLBR_COUNT_CR_CP Number of lines for Direct Supplier Bank Account Transfer - Savings account
JLBR_COUNT_CR_CP_TIT Number of lines for Direct Supplier Bank Account Transfer (same root CNPJ) - Saving account
JLBR_COUNT_CR_CC_RT Real Time - Number of lines for Direct Supplier Bank Account Transfer
JLBR_COUNT_CR_CC_TIT_RT Real Time - Number of lines for Direct Supplier Bank Account Transfer (same root CNPJ)
JLBR_COUNT_CR_CP_RT Real Time - Number of lines for Direct Supplier Bank Account Transfer - Savings account
JLBR_COUNT_CR_CP_TIT_RT Real Time - Number of lines for Direct Supplier Bank Account Transfer (same root CNPJ) - Saving account
JLBR_COUNT_DOC_D Number of lines for DOC type D transfer
JLBR_COUNT_DOC_C Number of lines for DOC type C transfer
JLBR_COUNT_TED_OT Number of lines for TED transfer for other CNPJs
JLBR_COUNT_TED_MT Number of lines for TED transfer for same root CNPJ
JLBR_COUNT_CHEQUE Number of lines for Check payment
JLBR_COUNT_ORDEM Number of lines for Payment Order
JLBR_COUNT_TIT_COBR Number of lines for titulo de cobranca payment - same bank
JLBR_COUNT_TIT_COBR_OB Number of lines for titulo de cobranca payment - other bank
JLBR_COUNT_TIT_CONCESS Number of lines for Concessionaires
JLBR_COUNT_ALL Total number of lines within the file
JLBR_SUM_CR_CC Total amount for Direct Supplier Bank Account Transfer
JLBR_SUM_CR_CC_TIT Total amount for Direct Supplier Bank Account Transfer (same root CNPJ)
JLBR_SUM_CR_CP Total amount for Direct Supplier Bank Account Transfer - Savings account
JLBR_SUM_CR_CP_TIT Total amount for Direct Supplier Bank Account Transfer (same root CNPJ) - Saving account
JLBR_SUM_CR_CC_RT Real Time - Total amount for Direct Supplier Bank Account Transfer
JLBR_SUM_CR_CC_TIT_RT Real Time - Total amount for Direct Supplier Bank Account Transfer (same root CNPJ)
JLBR_SUM_CR_CP_RT Real Time - Total amount for Direct Supplier Bank Account Transfer - Savings account
JLBR_SUM_CR_CP_TIT_RT Real Time - Total amount for Direct Supplier Bank Account Transfer (same root CNPJ) - Saving account
JLBR_SUM_DOC_D Total amount for DOC type D transfer
JLBR_SUM_DOC_C Total amount for DOC type C transfer
JLBR_SUM_TED_OT Total amount for TED transfer for other CNPJs
JLBR_SUM_TED_MT Total amount for TED transfer for same root CNPJ
JLBR_SUM_CHEQUE Total amount for Check payment
JLBR_SUM_ORDEM Total amount for Payment Order
JLBR_SUM_TIT_COBR Total amount for titulo de cobranca payment - same bank
JLBR_SUM_TIT_COBR_OB Total amount for titulo de cobranca payment - other bank
JLBR_SUM_TIT_CONCESS Total amount for Concessionaires
JLBR_SUM_DOC_TED Total amount of lines with DOC and TED payment types
JLBR_SUM_ALL Total amount of lines within the file

Integration - Creating Electronic Payment Files to Payer Bank Specific Formats

Oracle Financials Cloud provides an out-of-the-box payment file extract to export the payments from Oracle Cloud to the on premises financial system. For additional bank specific files formats, the implementation team must devise a strategy and build the export file using the Business Intelligence Publisher (BI Publisher) based on payments that are created. The following figure illustrates this flow:

To create user-defined BI Publisher reports that support bank specific formats, follow these steps:

  1. Sign in to BI Publisher Enterprise.
  2. Navigate to your report in the catalog.
  3. Click More to see all options.
  4. Click Customize.

    BI Publisher creates a copy of the report in the Custom folder, after the identical folder hierarchy and opens the report copy in the report editor.

  5. Create a user-defined layout for this report and save it.

    You can use the List View to:

    • define properties of the layout
    • Enable the custom layout as the default for this report
    • Inactivate the original layouts so your users no longer see them.
    For more information on setting the layout properties, see Configuring the Layout Settings Using the List View in the Oracle Fusion Middleware Report Designer’s Guide for Oracle Business Intelligence Publisher guide at http://docs.oracle.com.
  6. Apply security to the custom report.

BI Publisher creates an identical folder hierarchy for the user-defined copy of the report. The catalog permissions present on the original folders and report are not copied to the user-defined report or the folder hierarchy. You must manually reapply the catalog permissions to secure the user-defined report identically to the original report.

Payment File Flow Automation using Web Service

You can automate data extract from the user-defined BI Publisher reports using Oracle ERP Integration Web Services. The approach is to automate end-to-end orchestration using Oracle ERP Integration Services and avoid high touch integration scenarios for handling individual transactions. This image shows the Payment File Flow Automation using Web Service flow.

This image shows the Payment File Flow Automation using Web Service flow.Follow these steps:
  1. Create a user-defined BI Publisher report, or use an existing one from the previous topic, and the respective Enterprise Scheduler (ESS) job.
  2. Activate the Oracle ERP Integration Service to initiate the respective ESS job.
  3. Oracle ERP Cloud notifies the job completion through asynchronous callback.
  4. The callback provides the status and information to download the extracted data file from Oracle ERP Cloud.
  5. Identify the errors and take the appropriate action, such as process data extracted for downstream applications.For more information on Oracle ERP Integration Services, see the SOAP Web Services for Oracle Financials Cloud guide at http://docs.oracle.com.

Bank Return Files Import

The Brazil Bank Return Files Import and Validation process starts with users receiving the Payer Banks Processing Return files with the respective formats. Users must implement a process to execute a conversion to the Oracle Financials Cloud Import format and generate a .csv file following that format. The file should be transmitted to the Oracle WebCenter Content. The Load and Import process and Validate process must be executed.

Follow these steps:
  1. Download the Payments Bank Returns Import template (PayablesBankReturns.xlsm) from the Oracle Help Center, File-Based Data Import guide for Payments section.
    • Option 1: Manually populate the .xlsm sheet with the bank returns from the external files received from the bank third party system. Save a copy of the file and click Generate CSV File.
    • Option 2: Build the external process to create the .csv files based on the Bank Returns files.
  2. Transfer the .csv data file to the Oracle WebCenter Content server.

To load data into the interface tables, follow these steps:

  1. Sign in to Oracle Fusion Applications.
  2. Navigate to Navigator > Tools > Scheduled Processes.
  3. Click Schedule New Process.
  4. Search and select the load interface file for the import job.
  5. When the Process Details page appears:
    • Select the target import process Validate and Import Bank Returns.
    • Enter the data file name.
    • Click Submit.
  6. Search and select the Validate and Import Bank Returns job.
  7. Click Submit.

Bank Returns Flow Automation Using Web Service

The following diagram outlines the steps involved in the automated data import process:

Figure: Inbound data integration orchestration flow

To automatically import data into Oracle ERP Cloud, perform the following steps:

  1. Create the data file using the PayablesBankReturns.xlsm template.
  2. Activate the Oracle ERP Integration Service to initiate import.
  3. Provide notification of the process completion through asynchronous callback.
  4. Deliver the import status and information using callback to access logs or errors.
  5. Review any errors, if applicable, and take the appropriate action for error resolution.
    Note: After completion, Oracle ERP Cloud extracts data from the interface and error tables, includes the ESS job log files, and uploads the files to the Oracle WebCenter Content server in a ZIP format. Once uploaded successfully to the respective Oracle WebCenter Content account, the data from the interface and error tables is purged.