External Bank Import Process

Process of importing external bank account data in R12

Oracle supports many Open Interface Programs for importing data from any third party systems. For importing bank account information Oracle provides interface programs named ‘Customer Interface’ for importing Customer bank accounts and ‘Supplier Open Interface/Supplier site interface ’ for importing Supplier bank accounts.

This document helps in understanding the process of importing the external bank accounts data for customers or suppliers.

Importing Supplier bank accounts

The data to be imported is to be inserted in the interface tables first. After inserting the data in the interface tables,the ‘Supplier Open Interface Import’ program is run for inserting the data in the R12 tables.An important point to be noted with regard to the supplier bank accounts – the bank accounts data related to the suppliers can only be imported along with the supplier or supplier site. The bank account details cannot be imported individually. After creating the supplier or supplier site, the bank account can only be added from the user interface.

The supplier bank accounts can be assigned at the Supplier or Supplier Site or Address or Address Operating Unit level from the user interface. The import program supports assigning Supplier bank accounts at Supplier or at Supplier Site level only.

The main interface tables used for importing Suppliers, Supplier sites and Bank accounts data are as follows -

AP_SUPPLIERS_INT

IBY_TEMP_EXT_BANK_ACCTS.

AP_SUPPLIER_SITES_INT

The following scenarios are covered in this document –

a) Importing the bank account at Supplier level along with Supplier data

b) Importing the bank account at site level along with Supplier and Supplier site

i. Importing bank account at site along with supplier and site details.

ii. Importing bank account along with supplier site for an existing Supplier.

c) Importing the bank accounts for Employee type Supplier

The detailed steps for each of the scenarios have been mentioned below.

Importing the bank account at Supplier level along with Supplier data

Please follow the below mentioned steps for importing the bank account at supplier level.

Step 1 –

Insert the Suppliers data in the interface table AP_SUPPLIERS_INT.

Step 2 –

While inserting the data in the table AP_SUPPLIERS_INT, the data in the column

VENDOR_INTERFACE_ID should be generated sequentially. This is the unique identifier for

records in this table

The column AP_SUPPLIERS_INT.VENDOR_INTERFACE_ID is used to link the supplier with

the respective site or bank account details.

The sequence AP_SUPPLIERS_INT_S is used to generate the sequential number.

Step 3 –

The bank account details should be inserted in the interface table

IBY_TEMP_EXT_BANK_ACCTS

Step 4 –

While inserting the data in the table IBY_TEMP_EXT_BANK_ACCTS, as the bank account data is being assigned at supplier level, the column IBY_TEMP_EXT_BANK_ACCTS.CALLING_APP_UNIQUE_APP_REF1 should be updated

with the value from its corresponding suppliers

AP_SUPPLIERS_INT.VENDOR_INTERFACE_ID

Step 5 –

While inserting the data in IBY_TEMP_EXT_BANK_ACCTS and AP_SUPPLIERS_INT, the value for the column ‘Status’ should be updated as ‘NEW’ (case sensitive) in both the tables.After inserting the data in the tables AP_SUPPLIERS_INT and IBY_TEMP_EXT_BANK_ACCTS the data is ready to be imported.

Step 6 –

Navigate to the Payables >> Other: Requests >> Run -‘Submit Request’. The following screen is displayed.

Step 7 –

Select ‘Single Request’ and select ‘OK’ button.

concurrent program name : supplier open interface import

Step 8

In the request name, search and select the program with name ‘Supplier Open Interface

Step 9 –

Navigate to the parameters LOV and select the options based on the imported data.

Step 10 –

Parameters: Batch size indicates the number of records to be imported in one import program.

The default value is 1000 and can be modified.

Step 11 –

Parameters: Print exceptions only indicate whether the log file should contain only the exception

details. Default value is ‘No’ and can be modified to ‘Yes’.

Step 12 –

Parameters: Debug switch indicate whether the log file should contain the debug details. Default

value is ‘No’ and can be modified to ‘Yes’.

Step 13 –

Parameters: Trace switch indicate whether the trace file of the program should be generated.

Default value is ‘No’ and can be modified to ‘Yes’.

Step 14 –

Import options LOV: Default value is ‘All’. If the program is to import only the records with the

status ‘New’, select the option ‘New’. If the program is run to import only the rejected

transactions of the earlier run, select the option ‘Rejected’. If the request program is to import all

the records in the table, select the option ‘All’.

Step 15 –

Select ‘Submit’ button.

Step 16 –

The program ‘Supplier Open Interface Import’ automatically calls the ‘Create external bank

account’ (IBY_EXT_BANKACCT_PUB.CREATE_EXT_BANK_ACCT) program and imports

the bank accounts data related to the supplier.

On successful import of the records the column ‘Status’ will be updated from ‘NEW’ to

‘PROCESSED’. If a record is not imported due to any error, the field will be updated as

‘REJECTED’.

Step 17 –

To track the status of the request program, navigate to the ‘View requests’ screen

Step 18 –

When the Phase column shows ‘Completed’ and the status column shows ‘Normal’, select the

‘View Output’ button to know the number of records that have been imported.

Step 19 –

Select the ‘View Log’ button to go through the error messages if any.

After Successful import of Supplier/Supplier Site with the bank account details, following IBY Tables are

being populated.

IBY_EXTERNAL_PAYEES_ALL: A record is created in this table with Payee id (equivalent to

HZ_PARTIES.PARTY_ID).

IBY_EXT_BANK_ACCOUNTS: A record is inserted in this table with the bank account details

provided in IBY_TEMP_EXT_BANK_ACCTS.

IBY_PMT_INSTR_USES_ALL: For every external bank account created, a record is inserted in this

table. This table also holds relationship between External Payee and External Bank Account

Note:

2) While importing the bank account along with the supplier, if there is some error in the bank account

details, the supplier data will only be created without the bank details.

3) Bank account can be created without bank and branch details if the bank is not used for making

international payments. In other words bank account will be created without the bank and branch if

Bank_id and Branch_id is not inserted in IBY_TEMP_EXT_BANK_ACCTS table and

FOREIGN_PAYMENT_USE_FLAG is ‘No’.

Importing the bank account at site level along with Supplier and Supplier site

While importing the bank details for a supplier site, there are two scenarios –

a) Importing bank account at site along with supplier and site details.

b) Importing bank account along with supplier site for an existing Supplier.

Importing Bank account at site along with supplier and site details.

The additional interface table for storing the supplier sites data is AP_SUPPLIER_SITES_INT.

Step 1 –

Insert the data in the AP_SUPPLIERS_INT and AP_SUPPLIER_SITES_INT.

Step 2 –

While inserting the data in the table AP_SUPPLIERS_INT, the data in the column VENDOR_INTERFACE_ID should be generated sequentially. This is the unique identifier for records in this table In the scenario, VENDOR_INTERFACE_ID is used to link the supplier with the  respective site details

Step 3 –

While inserting data in the table AP_SUPPLIER_SITES_INT, the column AP_SUPPLIER_SITES_INT.VENDOR_INTERFACE_ID should be filled in with the

respective suppliers record value of the AP_SUPPLIER_INT.VENDOR_INTERFACE_ID

Step 4 –

Insert the bank account details in the interface table IBY_TEMP_EXT_BANK_ACCTS.

Step 5 –

As the bank account data is being assigned at supplier site level, while inserting the data in the table IBY_TEMP_EXT_BANK_ACCTS, the column IBY_TEMP_EXT_BANK_ACCTS.CALLING_APP_UNIQUE_APP_

REF2 should be updated with AP_SUPPLIER_SITES_INT.VENDOR_SITE_INTERFACE_ID.

VENDOR_SITE_INTERFACE_ID helps in linking the bank details with its corresponding Supplier site.

Step 6 –

While inserting the data in AP_SUPPLIERS_INT, AP_SUPPLIER_ SITES_INT and

IBY_TEMP_EXT_BANK_ACCTS, the value for the column ‘Status’ should be updated as ‘NEW’.

After inserting the data in the tables AP_SUPPLIERS_INT, AP_SUPPLIER_SITES_INT and IBY_TEMP_EXT_BANK_ACCTS the data is ready to be imported.

Step 7 –

Navigate to the Payables >> Other: Requests >> Run -‘Submit Request’ screen and run the program with name ‘Supplier Open Interface Import’.

The other steps to run the ‘Supplier Open Interface Import’ are same as explained for importing Bank accounts details at supplier level.

Importing bank account along with supplier site for an existing Supplier

The interface table for storing the supplier sites data is AP_SUPPLIER_SITES_INT and the bank account data is IBY_TEMP_EXT_BANK_ACCTS.

Step 1 –

Insert the data in the AP_SUPPLIER_SITES_INT.

Step 2 –

While inserting the data in the AP_SUPPLIER_SITES_INT, the column VENDOR_ID

should be updated with the respective supplier for which the supplier site is being created.

The column VENDOR_ID is used to link the supplier with the respective Supplier site details.

Step 3 –

The bank account details should be inserted in the interface table IBY_TEMP_EXT_BANK_ACCTS.

Step 4 –

As the bank account data is being assigned at supplier site level, while inserting the data  in the IBY_TEMP_EXT_BANK_ACCTS the column ‘CALLING_APP_UNIQUE_APP_REF2' should be updated with the respective  VENDOR_SITE_INTERFACE_ID.

The field AP_SUPPLIER_ SITES_INT is used to link the supplier site with the respective  bank account details.

Step 5 –

While inserting the data in AP_SUPPLIER_ SITES_INT and IBY_TEMP_EXT_BANK_ACCTS, the value for the column ‘Status’ should be updated as ‘NEW’.

After inserting the data in the tables AP_SUPPLIER_ SITES_INT and IBY_TEMP_EXT_BANK_ACCTS the data is ready to be imported.

Step 6 –

Navigate to the Payables >> Other: Requests >> Run -‘Submit Request’ screen and run

the program with name ‘Supplier Sites Open Interface Import’.

The other steps to run the ‘Supplier Open Interface Import’ are same as explained for importing Bank

accounts details at supplier level.

 Importing the bank accounts for Employee type Supplier

External bank account can be imported for Employee type suppliers while importing the supplier data for the employee.

Prerequisite

Employee record should have been created in the HR.

The interface tables for storing the supplier, supplier site and the bank account details remain the same - AP_SUPPLIER_SITES_INT, AP_SUPPLIER_SITES_INT and IBY_TEMP_EXT_BANK_ACCTS respectively.

Step 1 –

Insert the data in the AP_SUPPLIERS_INT.

Step 2 –

As the data is inserted for an employee type of supplier, the AP_SUPPLIER_INT.EMPLOYEE_ID should be updated with employee Id and VENDOR_TYPE_LOOKUP_CODE should be updated as ‘EMPLOYEE’.

Step 3 –

Insert the data in the AP_SUPPLIER_SITES_INT.

Step 4 –

As the data is imported for an employee type supplier, the AP_SUPPLIER_SITES_INT.VENDOR_SITE_CODE should be updated as ‘HOME’ or ‘OFFICE’ or ‘PROVISIONAL’ (please note that values are case sensitive and should be

given in capitals).

Step 5 –

Insert the bank account details in the interface table IBY_TEMP_EXT_BANK_ACCTS.

Step 6 –

As the bank account data is being assigned at supplier site level, while inserting the data in the table IBY_TEMP_EXT_BANK_ACCTS, the column IBY_TEMP_EXT_BANK_ACCTS.CALLING_APP_UNIQUE_APP_

REF2 should be updated with AP_SUPPLIER_SITES_INT.VENDOR_SITE_INTERFACE_ID.

VENDOR_SITE_INTERFACE_ID helps in linking the bank details with its

corresponding Supplier site.

Step 7 –

Navigate to the Payables >> Other: Requests >> Run -‘Submit Request’ screen and run the program with name ‘Supplier Open Interface Import’.

The other steps to run the ‘Supplier Open Interface Import’ are same as explained for importing Bank

accounts details at supplier level.