The blog covers the Supplier Conversion Process in Oracle Payables R2. The Supplier Conversion Process in Oracle Payables is one of the most commonly used for Supplier Conversion / Migration Process. The Supplier Conversion / Migration Program loads the Supplier Master Data, Supplier Sites Data and Supplier Contacts from source data files into Supplier Staging Tables in Oracle Payables, Validates the Supplier Staging Data and loads into Supplier Interface Tables.
Open Interface Approach :
Flat File
-> processed using SQL *Loader
Staging Table
-> processed using PLSQL Package
Interface Table
-> processed using Standard Concurrent Programs
Prerequisites for Supplier Conversion Process
The below given setup should be available before Supplier Conversion Program runs
- Set of Books Setup
- Payment Terms Setup
- Pay Groups Setup
- GL Code Combinations (CCID) Setup
- Supplier Setup
- Bank Accounts Setup
- Currency Setup
- Employee Setup ( if Employee is considered as Supplier)
Supplier Data Load for Supplier Conversion Process
The below given approach can be used for load Supplier Data Load for the Supplier Conversion Program
- SQL Loader: SQL Loader helps in loading the bulk records into Oracle Tables
- APEX: Oracle Application Express could be another option for bulk upload for supplier records
Supplier Validation in Supplier Conversion Process
The below given are the validations that needs to be considered when running the Supplier Conversion Program
- Duplicate Vendors NOT to be processed from AP_SUPPLIERS Table
- Duplicate Vendor Name NOT to be processed from AP_SUPPLIERS_INT and AP_SUPPLIERS Table
- Retrieve the valid Payment Terms from AP_TERMS_TL table
- Retrieve the valid Pay Groups from FND_LOOKUP_VALUES_VL table where lookup_type=‘PAY GROUP’
- Retrieve the valid Vendor Type from PO_LOOKUP_CODES table where lookup_type=‘VENDOR TYPE’
- Retrieve the valid Employee records from PER_ALL_PEOPLE_F table
- Duplicate Vendors NOT to be processed from AP_SUPPLIERS Table
- Duplicate Supplier Site Code NOT to be processed from AP_SUPPLIERS_SITES_ALL Table
- Retrieve the valid Country Code records from FND_TERRITORIES_TL table
- Retrieve the Payment Method records from IBY_PAYMENT_METHODS_VL table
Supplier Interface Programs
The below given interface programs are to be used as part of Supplier Conversion
- Supplier Open Interface Import
- Supplier Sites Open Interface Import
- Supplier Site Contacts Open Interface Import
Supplier Interface Tables
- The below given interface tables are to be used as part of Supplier Conversion
- AP_SUPPLIERS_INT
- AP_SUPPLIER_SITES_INT
- AP_SUP_SITE_CONTACT_INT
- AP_SUPPLIER_INT_REJECTIONS ( list down the rejected records for Supplier Master, Supplier Sties and Supplier Contact as part of Supplier Conversion Process)
AP_SUPPLIERS_INT : Oracle Payables provides the AP_SUPPLIERS_INT as the Open Interface Table which holds the Supplier Master data. When the Supplier Open Interface Import Program is submitted, then each record having unique VENDOR_INTERFACE_ID in AP_SUPPLIERS_INT is processed and loaded into PO_VENDORS Table
| AP_SUPPLIERS_INT Table Column | AP_SUPPLIERS_INT Column Description |
| VENDOR_INTERFACE_ID | Unique record identifier generated using AP_SUPPLIERS_INT_S.NEXTVAL |
| SEGMENT1 | Supplier Number |
| VENDOR_NAME | Supplier Name |
| VENDOR_TYPE_LOOKUP_CODE | Provides the Supplier Type |
| SHIP_TO_LOCATION_CODE | Default ship-to-location name |
| BILL_TO_LOCATION_CODE | Default bill-to-location name |
| TERMS_NAME | Provides the Supplier Payment Terms |
| TAX_VERIFICATION_DATE | Provides the Tax verification date(1099) |
| VAT_REGISTRATION_NUM | Provides the Tax Registration Number |
| PAY_GROUP_LOOKUP_CODE | Provides the Supplier Pay Group |
| INVOICE_CURRENCY_CODE | Default Invoice Currency Code |
| PAYMENT_CURRENCY_CODE | Provides the Supplier Payment Currency Code |
| NUM_1099 | Provides the Tax Identification date(1099) |
| VAT_CODE | Provides the default Invoice Tax Code |
| HOLD_FLAG | ‘Y’ if Supplier is on Purchasing Hold |
| SUMMARY_FLAG | Key flex field summary flag |
| ENABLED_FLAG | Key flex field enable flag |
| EMPLOYEE_ID | provides Employee_id if supplier is an employee |
| ATTRIBUTE1 -15 | Descriptive Flex field Segments |
AP_SUPPLIER_SITES_INT : Oracle Payables provides the AP_SUPPLIER_SITES_INT as the Open Interface Table which holds the Supplier Site Code data. When the Supplier Site Open Interface Import Program is submitted, then each record having unique VENDOR_ID in AP_SUPPLIER_SITES_INT is processed and loaded into PO_VENDOR_SITES_ALL Table
| AP_SUPPLIER_SITES_INT Table Column | AP_SUPPLIER_SITES_INT Table Column Description |
| VENDOR_SITE_INTERFACE_ID | Unique record identifier generated using AP_SUPPLIER_SITES_INT_S.NEXTVAL |
| VENDOR_SITE_CODE | Provides the Supplier Site Name |
| ADDRESS_LINE1 | Provides the Supplier Address |
| ADDRESS_LINE2 | Provides the Supplier Address |
| ADDRESS_LINE3 | Provides the Supplier Address |
| CITY | Provides the Supplier City |
| STATE | Provides the Supplier State |
| ZIP | Provides the Supplier State Zip Code |
| COUNTRY | Provides the Supplier Country |
| PHONE | Provides the Supplier Phone Number |
| FAX | Provides the Supplier Fax Number |
| SHIP_TO_LOCATION_CODE | Default ship-to-location name |
| BILL_TO_LOCATION_CODE | Default bill-to-location name |
| PAYMENT_METHOD_LOOKUP_CODE | Provides the Supplier Payment Method |
| VAT_CODE | Provides the Supplier Invoice Tax Code |
| PAY_GROUP_LOOKUP_CODE | Provides the Supplier Payment Group |
| TERMS_NAME | Provides the Supplier Payment Terms |
| INVOICE_CURRENCY_CODE | Provides the Default Supplier Invoice Currency Code |
| PAYMENT_CURRENCY_CODE | Provides the Supplier Invoice Payment Currency Code |
| EMAIL_ADDRESS | Provides the Supplier Email Address |
| PURCHASING_SITE_FLAG | ‘Y if the Supplier Site is allowed for Purchasing |
| AUTO_TAX_CALC_FLAG | Level of automatic tax calculation for supplier |
| HOLD_ALL_PAYMENTS_FLAG | ‘Y’ if Supplier Payments are on HOLD |
AP_SUP_SITE_CONTACT_INT: Oracle Payables provides the AP_SUP_SITE_CONTACT_INT as the Open Interface Table which holds the Supplier Site Code data. When the Supplier Site Open Interface Import Program is submitted, then each supplier contact record is mapped to Supplier Site using the VENDOR_SITE_CODE and ORG_ID to create a Supplier Contact into PO_VENDOR_CONTACTS Table
| AP_SUP_SITE_CONTACT_INT Table Column | AP_SUP_SITE_CONTACT_INT Table Column Description |
| VENDOR_INTERFACE_ID | Supplier interface record unique identifier |
| VENDOR_CONTACT_INTERFACE_ID | Unique record identifier generated using AP_SUP_SITE_CONTACT_INT_S.NEXTVAL |
| VENDOR_SITE_CODE | Provides the Supplier Site Name |
| FIRST_NAME | Provides the Supplier First Name |
| LAST_NAME | Provides the Supplier Last Name |
| AREA_CODE | Provides the Supplier Area Code |
| PHONE | Provides the Supplier Phone Number |
| FIRST_NAME_ALT | Provides the Alternate Name for Supplier First Name |
| LAST_NAME_ALT | Provides the Alternate Name for Supplier Last Name |
| EMAIL_ADDRESS | Provides the Supplier Email Address |
| FAX | Provides the Supplier Fax Number |
| VENDOR_ID | Provides the Supplier Unique Id |