Supplier Conversion

Supplier Conversion / Interface :
=================================
-- Used to import the Suppliers from Legacy to Oracle Applications
-- Using Open Interface Approach
   Using API Approach

Open Interface Approach :
-------------------------
Flat File
   |
   |-> SQL *LOader
   |
Staging Table
   |
   |-> PLSQL Package
   |
Interface Table
   |
   |-> Standard Conc Programs
   |
Base Tables (AP_SUPPLIERS / HZ_PARTIES
    AP_SUPPLIER_SITES_ALL / HZ_PARTY_SITES
    AP_SUPPLIER_CONTACTS / PO_VENDOR_CONTACTS
                    )
Interface Tables :
------------------
AP_SUPPLIERS_INT
AP_SUPPLIER_SITES_INT
AP_SUP_SITE_CONTACT_INT

Error Table :
-------------
AP_SUPPLIER_INT_REJECTIONS

Standard Programs :
-------------------
Suppliers Open Interface Import
Supplier Sites Open Interface Import
Supplier Contacts Open Interface Import


API Approach :
--------------
-- Application Program Interface
-- Oracle Standard Package

Process :
---------
Flat File
   |
   |-> SQL *LOader
   |
Staging Table
   |
   |-> PLSQL Package
   |
  API
   |
   |
Base Tables (AP_SUPPLIERS / HZ_PARTIES
    AP_SUPPLIER_SITES_ALL / HZ_PARTY_SITES
    AP_SUPPLIER_CONTACTS / PO_VENDOR_CONTACTS
                    )

API :
-----
AP_VENDOR_PUB_PKG

Creation :
----------
create_vendor
create_vendor_site
create_vendor_contact

Updation :
----------
update_vendor
update_vendor_site
update_vendor_contact

Record Types :
--------------
3
1st Rec Type for Suppliers
2nd Rec Type for Supplier Sites
3rd Rec Type for Supplier Contacts


Supplier :
----------
Supplier Name
Supplier Type
Alias
Purchase Check Box
Receipt Check Box

Supplier Site :
---------------
Site Code
Address Line1
Address Line2
City
State
County
Country
Postal Code
Operating Unit
Supplier Name


Contacts :
----------
Title
First Name
Middle Name
Last Name - *
Job Title
Phone Area Code
Phone Number
Supplier SITE


Back End Mandatory Columns :
----------------------------
AP_SUPPLIERS :
--------------
VENDOR_ID
LAST_UPDATE_DATE
LAST_UPDATED_BY
SEGMENT1
SUMMARY_FLAG
ENABLED_FLAG
CREATION_DATE
CREATED_BY

AP_SUPPLIER_SITES_ALL :
-----------------------
VENDOR_SITE_ID
LAST_UPDATE_DATE
LAST_UPDATED_BY
VENDOR_ID
VENDOR_SITE_CODE
CREATION_DATE
CREATED_BY

AP_SUPPLIER_CONTACTS :
----------------------
VENDOR_CONTACT_ID
LAST_UPDATE_DATE
LAST_UPDATED_BY
CREATION_DATE
CREATED_BY
VENDOR_SITE_ID


Single Insert for Supplier Header :
-----------------------------------
VENDOR_ID
LAST_UPDATE_DATE
LAST_UPDATED_BY
SEGMENT1
SUMMARY_FLAG
ENABLED_FLAG
CREATION_DATE
CREATED_BY
VENDOR_NAME
VENDOR_TYPE_LOOKUP_CODE


DECLARE

V_SUPP_REC AP_VENDOR_PUB_PKG.r_vendor_rec_type;
V_SUPP_SITE_REC AP_VENDOR_PUB_PKG.r_vendor_site_rec_type;

v_return_status VARCHAR2(1);
v_msg_count NUMBER;
v_msg_data varchar2(2000);
v_vendor_id NUMBER;
v_party_id NUMBER;
v_msg varchar2(2000);
v_site_return_status VARCHAR2(1);
v_site_msg_count NUMBER;
v_site_msg_data varchar2(2000);
v_vendor_site_id NUMBER;
v_party_site_id NUMBER;
v_location_id NUMBER;
v_site_msg varchar2(2000);

BEGIN
V_SUPP_REC.VENDOR_NAME := 'RTL Supplier 126';
V_SUPP_REC.VENDOR_ID := 676770;
V_SUPP_REC.VENDOR_TYPE_LOOKUP_CODE := 'SUPPLIER';
V_SUPP_REC.segment1 := '676770';
V_SUPP_REC.SUMMARY_FLAG := 'Y';
V_SUPP_REC.ENABLED_FLAG := 'Y';
V_SUPP_REC.INVOICE_CURRENCY_CODE := 'USD';
V_SUPP_REC.PAYMENT_CURRENCY_CODE := 'USD';

AP_VENDOR_PUB_PKG.Create_Vendor
( p_api_version           => 1.0,
  p_init_msg_list => FND_API.G_FALSE,
p_commit     => FND_API.G_TRUE,
p_validation_level => FND_API.G_VALID_LEVEL_FULL,
x_return_status => v_return_status,
x_msg_count => v_msg_count,
x_msg_data => v_msg_data,
p_vendor_rec => V_SUPP_REC,
x_vendor_id => v_vendor_id,
x_party_id => v_party_id
);

COMMIT;
dbms_output.put_line('API Return Status : '||v_return_status);
dbms_output.put_line('v_msg_data : '||v_msg_data);
dbms_output.put_line('v_msg_count : '||v_msg_count);
dbms_output.put_line('Vendor Id : '||v_vendor_id);
dbms_output.put_line('Party Id : '||v_party_id);

for i in 1..v_msg_count
loop
 v_msg := apps.fnd_msg_pub.Get
(   p_msg_index    => i,
       p_encoded    => FND_API.G_TRUE
);
dbms_output.put_line('Error Message : '||v_msg);
end loop;

------- Supplier Sites --------
V_SUPP_SITE_REC.VENDOR_SITE_ID := 676768;
V_SUPP_SITE_REC.VENDOR_SITE_CODE := 'RTL_SITE_126';
V_SUPP_SITE_REC.VENDOR_ID := v_vendor_id;
V_SUPP_SITE_REC.address_line1 := 'Canoga Annex';
V_SUPP_SITE_REC.city := 'Canoga Annex';
V_SUPP_SITE_REC.country := 'US';
V_SUPP_SITE_REC.county := 'Los Angeles';
V_SUPP_SITE_REC.state := 'CA';
V_SUPP_SITE_REC.zip := '60065';
V_SUPP_SITE_REC.org_id := 204;

AP_VENDOR_PUB_PKG.Create_Vendor_site
( p_api_version           => 1.0,
  p_init_msg_list => FND_API.G_FALSE,
p_commit     => FND_API.G_TRUE,
p_validation_level => FND_API.G_VALID_LEVEL_FULL,
x_return_status => v_site_return_status,
x_msg_count => v_site_msg_count,
x_msg_data => v_site_msg_data,
p_vendor_site_rec => V_SUPP_SITE_REC,
x_vendor_site_id => v_vendor_site_id,
x_party_site_id => v_party_site_id,
x_location_id => v_location_id
);

COMMIT;

dbms_output.put_line('API Return Status : '||v_site_return_status);
dbms_output.put_line('v_msg_data : '||v_site_msg_data);
dbms_output.put_line('v_msg_count : '||v_site_msg_count);
dbms_output.put_line('Vendor Site Id : '||v_vendor_site_id);
dbms_output.put_line('Party Site Id : '||v_party_site_id);
dbms_output.put_line('Location Id : '||v_location_id);

for i in 1..v_site_msg_count
loop
 v_site_msg := apps.fnd_msg_pub.Get
(   p_msg_index    => i,
       p_encoded    => FND_API.G_TRUE
);
dbms_output.put_line('Error Message : '||v_site_msg);
end loop;

END;