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.

Item Assignment in Oracle Apps R12

Item Assignment to another inventory organization

There are two ways to assign the master item for inventory child organization.

Through master item form directly assigning to child organization. In case if we have multiple child organizations then we have to chose through API.

Below is an example of how to assign an organizations by using Plsql APIs.

'PVMDL012','PVTTV012','PVRCF012','PVSER012','PVCE2012','PVSEC012','PVITM012' all this items are represented in master organization (i.e. 118 in below example) and now my business requirement to assign 667 (As below example) inventory organization.

Declare

l_mesg VARCHAR2(1000);

l_count number;

cursor c1 is SELECT segment1

from apps.mtl_system_items_b

where segment1 in ('PVMDL012'

,'PVTTV012'

,'PVRCF012'

,'PVSER012'

,'PVCE2012'

,'PVSEC012'

,'PVITM012'

)

and organization_id = 118;--118 is a master organization 

BEGIN

 fnd_global.apps_initialize (user_id           => 78847,

                               resp_id           => 60823,

                               resp_appl_id      => 401

                              );

for f1 in c1

loop

EGO_ITEM_PUB.Assign_Item_To_Org(

  p_api_version => 1.0

 ,p_Item_Number => f1.segment1

 ,p_Organization_Id => 669

 ,x_return_status => l_mesg

 ,x_msg_count => l_count);

Commit;

IF l_count =1 THEN

 dbms_output.put_line( 'Error in API is '|| l_mesg);

ELSIF l_count >1 THEN

 LOOP

l_count := l_count+1;

l_mesg := FND_MSG_PUB.Get(FND_MSG_PUB.G_NEXT,FND_API.G_FALSE);

if l_mesg is NULL Then

EXIT;

END IF;

dbms_output.put_line('Message' || l_count ||' ---'||l_mesg);

END LOOP;

END IF;

end loop;

END;

Item Import in Oracle Apps R12

ITEM CONVERSION:

Item import in Oracle Apps R12.If you want to download the script click here.

You can import legacy system items into oracle system by using below item interface tables and validation rules.

Interface Tables:

MTL_SYSTEM_ITEMS_INTERFACE

        MTL_ITEM_CATEGORIES_INTERFACE

Error Table:

MTL_INTERFACE_ERRORS

Download the Script:

If you want to download the script click here.

Validation Rules:

Organization Code                                ORG_ORGANIZATION_DEFINITIONS

                                                                            (OR) MTL_PARAMETERS

Transaction Type                                  It Should not be null ,either CREATE or UPDATE.

Encumbrance/Expense                          GL_CODE_COMBINATIONS

Sales Accounts                                     

Primary UOM                                      MTL_UNITS_OF_MEASURE_TL

Item Type                                             FND_COMMON_TABLE 

                                                                            (Lookup_Type='ITEM_TYPE')

Item Category                                       MTL_CATEGORIES

Item Number                                         Item Number should be Unique (MTL_SYSTEM_ITEMS_B).

Description                                            Item Description should not be null.

Process Flag                                          Process_Flag should be 1.

Concurrent Program:

                                                              Import Items

Enter the parameters as below:

                                               All Organizations :Yes/No

                                               Validate Items :Yes/No

                                                Process Items :Yes/No

                                                Delete Process Rows: Yes/No

                                                Process Set : Enter the SET_PROCESS_ID for the records

                                                Create or Update Items : 1 (Create Items) or 2 (Update Items)

Once successfully completion of above concurrent program ,we have to check Process_Flag in interface table if it is 7 (Import Succeeded) or 4 (Import Failed) or 3 (Validation Failed).