Oracle Supplier Conversion in R12

Supplier Conversion in Oracle R12:

Supplier conversion program will load the supplier master, supplier site and contact data from flat file into staging table by using Sql*Loader ,validate the staging data and then insert into interface tables, finally validated data will import into oracle supplier standard tables by using oracle supplier open interface import programs.

Interface Tables:

AP_SUPPLIERS_INT

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

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

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

Error Tables:

          AP_SUPPLIER_INT_REJECTIONS

Validate Rules:

Vendor Number                            Should be unique , check in AP_SUPPLIERS table.

Terms Name                                 Check the name is exist in AP_TERMS_VL table.

Pay Group                                     Check the name in FND_LOOKUP_VALUES_VL ('PAY GROUP').

Vendor Type                                 Check the type in PO_LOOKUP_CODES('VENDOR TYPE')

Vendor Site Code                         Check for duplicate records in AP_SUPPLIER_SITES_ALL table.

Country Code                               Check the country is exist in FND_TERRITORIES_VL table.

Payment Method                          Check the 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

Base Tables:

AP_SUPPLIERS

AP_SUPPLIER_SITES_ALL
AP_SUPPLIER_CONTACTS

If you want sample scripts, mail me at patchavaanil27@gmail.com with subject as blog request.