Supplier Conversion in R12
December 21, 2010 6 Comments
Supplier Conversion in R12
Vendor conversion program will load the Supplier Master, Sites and Contacts data from data files to the staging tables, validate the data and then load the data into Interface tables, finally Validated data will import into Oracle Supplier Standard Tables by using Oracle Standard Supplier Import Programs.
Pre-requisites setup’s are: Payment terms, Pay Groups, CCID, Supplier classifications, Bank Accounts , Employees (if employees have to set up as vendors).
The Interface Tables are:
- AP_SUPPLIERS_INT
- AP_SUPPLIER_SITES_INT
- AP_SUP_SITE_CONTACT_INT
AP_SUPPLIERS_INT:
This is the open interface table for AP Suppliers. It holds Supplier information which is loaded by the user for import. The columns in the table map to corresponding columns in the PO_VENDORS table. The Oracle Payables application uses this information to create a new Supplier record when the Supplier Open Interface Import program is submitted. Each row in the table will be identified by a unique identifier, the VENDOR_INTERFACE_ID.
Mandatory Columns:
- VENDOR_INTERFACE_ID (ap_suppliers_int_s.NEXTVAL)- Supplier interface record unique identifier
- VENDOR_NAME – Supplier name
Other important columns:
- SEGMENT1 – Supplier Number
- VENDOR_TYPE_LOOKUP_CODE – Supplier type
- SHIP_TO_LOCATION_CODE – Default ship-to-location name
- BILL_TO_LOCATION_CODE – Default bill-to-location name
- TERMS_NAME – Payment terms name
- TAX_VERIFICATION_DATE – Tax verification date(1099)
- VAT_REGISTRATION_NUM – Tax registration number
- ATTRIBUTE1 -15 – Descriptive Flexfield Segments
- PAY_GROUP_LOOKUP_CODE – Payment group type
- INVOICE_CURRENCY_CODE – Default currency unique identifier
- PAYMENT_CURRENCY_CODE – Default payment currency unique identifier
- NUM_1099 – Tax identification number (1099)
- VAT_CODE – Default invoice tax code
- HOLD_FLAG – Indicates whether or not the supplier is on purchasing hold
- SUMMARY_FLAG – Key flexfield summary flag
- ENABLED_FLAG – Key flexfield enable flag
- EMPLOYEE_ID – Employee unique identifier if supplier is an employee
AP_SUPPLIER_SITES_INT:
This is the open interface table for AP Supplier Sites. It holds Supplier Site information which is loaded by the user for import. The columns in the table map to corresponding columns in PO_VENDOR_SITES_ALL table. The Oracle Payables application uses this information to create a new Supplier Site record when the Supplier Sites Open Interface Import program is submitted. Each row in the table will be joined to the appropriate Supplier using the VENDOR_ID column.
Mandatory Columns:
- VENDOR_SITE_INTERFACE_ID (ap_supplier_sites_int_s.NEXTVAL) – Supplier Site interface record unique identifier
- VENDOR_SITE_CODE – Supplier Site name
Other important columns:
- ADDRESS_LINE1 – First line of supplier address
- ADDRESS_LINE2 – Second line of supplier address
- ADDRESS_LINE3 – Third line of supplier address
- CITY – City name
- STATE – State name or abbreviation
- ZIP – Postal code
- COUNTRY – Country name
- PHONE – Phone number
- FAX – Supplier site facsimile number
- SHIP_TO_LOCATION_CODE – Default ship-to-location name
- BILL_TO_LOCATION_CODE – Default bill-to-location name
- PAYMENT_METHOD_LOOKUP_CODE – Default payment method type
- VAT_CODE – Invoice default tax code
- PAY_GROUP_LOOKUP_CODE – Payment group type
- TERMS_NAME – Payment terms name
- INVOICE_CURRENCY_CODE – Default currency unique identifier
- PAYMENT_CURRENCY_CODE – Default payment currency unique identifier
- EMAIL_ADDRESS – E-mail address of the supplier contact
- PURCHASING_SITE_FLAG – Indicates whether purchasing is allowed from this site
- AUTO_TAX_CALC_FLAG – Level of automatic tax calculation for supplier
- HOLD_ALL_PAYMENTS_FLAG – Indicates if Oracle Payables should place payments for this supplier on hold
AP_SUP_SITE_CONTACT_INT:
This is the open interface table for AP Supplier Site Contacts. It holds Supplier contact data. The columns in the table map to corresponding columns in PO_VENDOR_CONTACTS table. The Oracle Payables application uses this information to create a new Supplier Contact record when the Supplier Sites Open Interface Import program is submitted. Each row in the table will be joined to the appropriate Supplier Site using the VENDOR_SITE_CODE and ORG_ID.
Mandatory Columns:
- VENDOR_INTERFACE_ID – Supplier interface record unique identifier
- VENDOR_CONTACT_INTERFACE_ID (AP_SUP_SITE_CONTACT_INT_S.NEXTVAL) – Vendor Contact Interface Identifier
- VENDOR_SITE_CODE – Supplier Site name
Other important columns:
- FIRST_NAME – Contact First name
- LAST_NAME – Contact last name
- AREA_CODE – Area code of contact phone number
- PHONE – Contact phone number
- FIRST_NAME_ALT – Alternate Supplier contact first name
- LAST_NAME_ALT – Alternate Supplier contact last name
- EMAIL_ADDRESS – Email address for the Supplier Site contact
- FAX – Facsimile number for the Supplier Site contact
- VENDOR_ID – Supplier unique identifier
Validations:
- Vendor Number (Check for duplicate records in ap_suppliers table)
- Vendor Name (Check for duplicate records in staging as well as in ap_suppliers table)
- Terms Name (Check for proper record in ap_terms_tl table)
- Pay Group (Check for proper record in fnd_lookup_values_vl table where lookup_type = ‘PAY GROUP’)
- Employee Id (Check for proper employee record in per_all_people_f table)
- Vendor Type (Check for proper record in po_lookup_codes table where lookup_type = ‘VENDOR TYPE’)
- Vendor Site Code (Check for duplicate records in ap_supplier_sites_all table)
- Country Code ( Check for proper country code in fnd_territories_vl table)
- Payment Method (Check for proper payment method in iby_payment_methods_vl table)
Interface programs:
- Supplier Open Interface Import
- Supplier Sites Open Interface Import
- Supplier Site Contacts Open Interface Import
The data inserted via these interfaces are automatically populated into TCA tables.
Note: AP_SUPPLIER_INT_REJECTIONS table contains suppliers, sites, contacts rejections information.
Excellent information about R12 vendor importing.
Nice Article!
I have a doubt.
I didnot populate the ship to location and bill to location in the interfce table.But when the supplier was created,bill to and ship to have been populated in AP_SUPPLIERS.
So,where is this defauled from?
Kindly reply.
Hi Sankuls,
Although not mandatory, ship to location and bill to location is always required to populate from data files. Since you didn’t populate them, they might be coming from setup. Please review.
Thanks
Dibyajyoti Koch
Few more info:
We can also use request set “Supplier Open Interface Request Set” in place of running each import program individually.
You can use either automatic Supplier Number assignment or include your own values during import. Use the SEGMENT1 field to contain your desired Supplier Number and change the Supplier Number Entry option to MANUAL in the Payables System Setup screen.
If an invalid value is encountered when the concurrent process is evaluating each row, the ‘REJECT_CODE’ column is populated with the relevant rejection message and the ‘STATUS’ is updated to ‘REJECTED’. Processing on that row then stops and the program moves on to the next row.
Hi,
In 11i we use to have an extra paramater ‘Group id’ for Supplier Open Interface and Supplier Site Open Interface nut in R12 this parameter is not there in both the interfaces. My client uses group id to identify records for different processes and upload them separately for every process (based on group id). Not sure how to handle this on R12 as there is no group_id parameter available.
Any pointers or help on this will be highly appreciated.
Thanks,
Kamal
Hello,
I’m using supplier conversion to import the suppliers from legacy system to Oracle, but I ran into some problems.
1. Once a supplier is imported, it is not possible to update that supplier with supplier conversion. Is this correct? How do I need to update then a supplier?
2. Although i fill in the tax registration number in the ap_supplier_sites_int interface file (field VAT_REGISTRATION_NUM), the tax registration number is not filled in the supplier site tax details (Same for field rounding rule (field AP_TAX_ROUNDING_RULE),
3.How can i set (with the supplier interface) the Rounding Level, Rounding Rule, Default Reporting Country Name, and Default Reporting Registration Number on the Tax Details Page
4. How can I create a new regime code for a supplier with the supplier interface?
5. How can I create a new Fiscal classification type code for a supplier with the supplier interface?
Any help will be highly appreciated.