In My scenario it is a conversion. Just one time loading Suppliers in Oracle Apps.
In short I will explain and attached the code below:
This is only one file having Supplier Headers, Sites and Supplier Contacts.
Based on the file developed one Stage table to store till Import into Interface Tables:
/* Stage Table */
create table XX_SUPP_INT_STG
(
vendor_name VARCHAR2(240),
vendor_name_alt VARCHAR2(320),
legacy_vendor_num VARCHAR2(30),
vendor_type VARCHAR2(30),
organization_name VARCHAR2(240),
country VARCHAR2(25),
address_line1 VARCHAR2(240),
address_line2 VARCHAR2(240),
address_line3 VARCHAR2(240),
address_line4 VARCHAR2(240),
city VARCHAR2(60),
state VARCHAR2(60),
province VARCHAR2(60),
postal_code VARCHAR2(20),
vendor_site_code VARCHAR2(15),
purchasing_site VARCHAR2(1),
pay_site VARCHAR2(1),
phone_area_code VARCHAR2(10),
phone_number VARCHAR2(15),
fax_area_code VARCHAR2(10),
fax VARCHAR2(15),
site_email VARCHAR2(2000),
allow_wht_tax_header_level VARCHAR2(1),
invoice_wht_tax_group_hdr NUMBER(15),
allow_wht_tax_site_level VARCHAR2(1),
invoice_wht_tax_group_site NUMBER(15),
calc_tax_site_level VARCHAR2(1),
vat_reg_number VARCHAR2(20),
vat_code VARCHAR2(30),
hold_all_pay_hdr VARCHAR2(1),
hold_unmatch_inv_hdr VARCHAR2(1),
hold_unvali_inv_hdr VARCHAR2(1),
hold_reason_hdr VARCHAR2(240),
hold_all_pay_site VARCHAR2(1),
hold_unmatch_inv_site VARCHAR2(1),
hold_unvali_inv_site VARCHAR2(1),
hold_reason_site VARCHAR2(240),
pay_group VARCHAR2(25),
prefix VARCHAR2(60),
title VARCHAR2(30),
first_name VARCHAR2(15),
middle_name VARCHAR2(15),
last_name VARCHAR2(15),
contact_email VARCHAR2(2000),
department VARCHAR2(60),
contact_fax VARCHAR2(40),
contact_phone VARCHAR2(40),
contact_alt_phone VARCHAR2(40)
) ;
We have interface tables for Supplier Headers, Sites and Contacts:
AP_SUPPLIERS_INT
AP_SUPPLIER_SITES_INT
AP_SUPPLIER_SITES_INT
Base Tables:
AP_SUPPLIERS / PO_VENDORS
AP_SUPPLIER_SITES / PO_VENDOR_SITES_ALL
AP_SUPPLIER_CONTACTS / PO_VENDOR_CONTANCTS
I have above stage table columns in my excel data file, it is in .xls ( we can have in .csv also. if not in these two types, we need to format the file as per our process.
Dump the excel file Data into Stage table directly ( I have done this step using PL/SQL Developer).
After I have data in my stage table: I run the below code. Because of I have done this conversion one time only, it will never use in future, but if it is multi time process (Interface), just you can create the package or procedure, register in application as per your requirement you can go ahead and you can add more validations before importing into interface table and stage table with respect your business need.
======================================================================
DECLARE
CURSOR C_SUPP_DATA is(
SELECT distinct VENDOR_NAME,
VENDOR_NAME_ALT,
LEGACY_VENDOR_NUM,
VENDOR_TYPE,
PAY_GROUP,
ALLOW_WHT_TAX_HEADER_LEVEL,
INVOICE_WHT_TAX_GROUP_HDR,
HOLD_ALL_PAY_HDR,
HOLD_UNMATCH_INV_HDR,
HOLD_UNVALI_INV_HDR,
HOLD_REASON_HDR
FROM XX_SUPP_INT_STG);
CURSOR C_SITE_DATA(P_VENDOR_NAME varchar2) is(
SELECT distinct COUNTRY,
ADDRESS_LINE1,
ADDRESS_LINE2,
ADDRESS_LINE3,
ADDRESS_LINE4,
CITY,
STATE,
PROVINCE,
POSTAL_CODE,
VENDOR_SITE_CODE,
PURCHASING_SITE,
PAY_SITE,
PHONE_AREA_CODE,
PHONE_NUMBER,
FAX_AREA_CODE,
FAX,
SITE_EMAIL,
ALLOW_WHT_TAX_HEADER_LEVEL,
INVOICE_WHT_TAX_GROUP_HDR,
ALLOW_WHT_TAX_SITE_LEVEL,
INVOICE_WHT_TAX_GROUP_SITE,
CALC_TAX_SITE_LEVEL,
VAT_REG_NUMBER,
VAT_CODE,
HOLD_ALL_PAY_HDR,
HOLD_UNMATCH_INV_HDR,
HOLD_UNVALI_INV_HDR,
HOLD_REASON_HDR,
HOLD_ALL_PAY_SITE,
HOLD_UNMATCH_INV_SITE,
HOLD_UNVALI_INV_SITE,
HOLD_REASON_SITE,
PAY_GROUP,
ORGANIZATION_NAME
FROM XX_SUPP_INT_STG
where VENDOR_NAME = P_VENDOR_NAME);
CURSOR C_CONTACT_DATA(P_VENDOR_NAME varchar2, P_VENDOR_SITE varchar2) is(
select distinct PREFIX,
TITLE,
FIRST_NAME,
MIDDLE_NAME,
LAST_NAME,
CONTACT_EMAIL,
DEPARTMENT,
CONTACT_FAX,
CONTACT_PHONE,
CONTACT_ALT_PHONE
from XX_SUPP_INT_STG
where VENDOR_NAME = P_VENDOR_NAME
and VENDOR_SITE_CODE = P_VENDOR_SITE);
lc_site_org_id po_vendor_sites_all.org_id%type; -- := 50168; /* this need to remove*/
lc_vendor_interface_id ap_suppliers_int.vendor_interface_id%type;
lc_vendor_site_interface_id ap_supplier_sites_int.vendor_site_interface_id%type;
lc_vendor_contact_interface_id ap_sup_site_contact_int.vendor_contact_interface_id%type;
lc_vendor_cnt NUMBER := 0;
lc_user_id NUMBER;
BEGIN
FOR i IN C_SUPP_DATA LOOP
/* Main Loop */
lc_vendor_interface_id := ap_suppliers_int_s.nextval;
begin
INSERT INTO ap.ap_suppliers_int
(vendor_interface_id,
vendor_name,
vendor_name_alt,
vendor_type_lookup_code,
allow_awt_flag,
awt_group_id,
hold_all_payments_flag,
hold_unmatched_invoices_flag,
hold_future_payments_flag,
hold_reason,
pay_group_lookup_code,
attribute10)
VALUES
(lc_vendor_interface_id,
i.vendor_name,
i.vendor_name_alt,
i.vendor_type,
i.allow_wht_tax_header_level,
i.invoice_wht_tax_group_hdr,
i.hold_all_pay_hdr,
i.hold_unmatch_inv_hdr,
i.hold_unvali_inv_hdr,
i.hold_reason_hdr,
i.pay_group,
i.legacy_vendor_num );
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(i.vendor_name ||
' Error occured occured at Supplier Header ' ||
SQLCODE || ' - ' || SQLERRM);
end;
FOR j IN C_SITE_DATA(i.vendor_name) LOOP
BEGIN
SELECT organization_id
INTO lc_site_org_id
FROM hr_operating_units
WHERE NAME = j.organization_name ;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.PUT_LINE(j.organization_name, 'invalid Organization');
END;
begin
lc_vendor_site_interface_id := ap_supplier_sites_int_s.nextval;
INSERT INTO ap.ap_supplier_sites_int
(vendor_site_interface_id,
vendor_interface_id,
vendor_site_code,
address_line1,
address_line2,
address_line3,
address_line4,
city,
state,
country,
zip,
phone,
org_id,
province,
purchasing_site_flag,
pay_site_flag,
area_code,
fax_area_code,
fax,
email_address,
allow_awt_flag,
awt_group_id,
auto_tax_calc_flag,
vat_registration_num,
vat_code,
hold_all_payments_flag,
hold_future_payments_flag,
HOLD_REASON)
VALUES
(lc_vendor_site_interface_id,
lc_vendor_interface_id,
j.vendor_site_code,
j.address_line1,
j.address_line2,
j.address_line3,
j.address_line4,
j.city,
j.state,
j.country,
j.postal_code,
j.phone_number,
lc_site_org_id,
j.province,
j.purchasing_site,
j.pay_site,
j.phone_area_code,
j.fax_area_code,
j.fax,
j.site_email,
j.allow_wht_tax_site_level,
j.invoice_wht_tax_group_site,
j.calc_tax_site_level,
j.vat_reg_number,
j.vat_code,
j.hold_all_pay_site,
j.hold_unvali_inv_site,
j.HOLD_REASON_SITE);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(i.vendor_name || ' ' || j.vendor_site_code ||
' Error occured occured at Supplier Site ' ||
SQLCODE || ' - ' || SQLERRM);
end;
for k in C_CONTACT_DATA(i.vendor_name, j.vendor_site_code) LOOP
IF k.last_name IS NULL THEN --last name condition
DBMS_OUTPUT.PUT_LINE(i.vendor_name||''||j.vendor_site_code||''||'Contact not created because LAST NAME is null ');
ELSE
begin
lc_vendor_contact_interface_id :=ap_sup_site_contact_int_s.nextval ;
INSERT INTO ap.ap_sup_site_contact_int ss
(vendor_interface_id,
vendor_contact_interface_id,
vendor_site_code,
first_name,
middle_name,
last_name,
area_code,
phone,
first_name_alt,
last_name_alt,
email_address,
fax,
prefix,
title,
department,
alt_phone,
org_id)
VALUES
(lc_vendor_interface_id,
lc_vendor_contact_interface_id,
j.vendor_site_code,
k.first_name,
k.middle_name,
k.last_name,
'',--area_code
k.contact_phone,
NULL,
NULL,
k.contact_email,
k.contact_fax,
k.prefix,
k.title,
k.department,
NVL(k.contact_alt_phone,k.contact_phone),
lc_site_org_id);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(i.vendor_name || ' ' ||
j.vendor_site_code || ' ' || k.first_name ||
' Error occured at Supplier Contact ' ||
SQLCODE || ' - ' || SQLERRM);
END;
END IF; --last name condition
END LOOP; -- Contact Loop
END LOOP; -- Site Loop
END LOOP; -- Main Loop
COMMIT;
DBMS_OUTPUT.PUT_LINE('Interface ID: '||lc_vendor_interface_id);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Unhandled Exception occured at Supplier Header ' ||
SQLCODE || ' - ' || SQLERRM);
END;
======================================================================
After data inserted into Interface tables, we need to run the concurrent programs one after another to get the data into base tables, as I said above one time process so, I did not created one package and call everything there, but you can do that, if required.
Supplier Open Interface Import
Supplier Sites Open Interface Import
Supplier Contacts Open Interface Import
Once above programs completed Normal, you can see your migrated data in below base tables based on my legacy_vendor_number i.e
AP_SUPPLIERS / PO_VENDORS (attribute10 in this table )
AP_SUPPLIER_SITES / PO_VENDOR_SITES_ALL
AP_SUPPLIER_CONTACTS / PO_VENDOR_CONTANCTS
In short I will explain and attached the code below:
This is only one file having Supplier Headers, Sites and Supplier Contacts.
Based on the file developed one Stage table to store till Import into Interface Tables:
/* Stage Table */
create table XX_SUPP_INT_STG
(
vendor_name VARCHAR2(240),
vendor_name_alt VARCHAR2(320),
legacy_vendor_num VARCHAR2(30),
vendor_type VARCHAR2(30),
organization_name VARCHAR2(240),
country VARCHAR2(25),
address_line1 VARCHAR2(240),
address_line2 VARCHAR2(240),
address_line3 VARCHAR2(240),
address_line4 VARCHAR2(240),
city VARCHAR2(60),
state VARCHAR2(60),
province VARCHAR2(60),
postal_code VARCHAR2(20),
vendor_site_code VARCHAR2(15),
purchasing_site VARCHAR2(1),
pay_site VARCHAR2(1),
phone_area_code VARCHAR2(10),
phone_number VARCHAR2(15),
fax_area_code VARCHAR2(10),
fax VARCHAR2(15),
site_email VARCHAR2(2000),
allow_wht_tax_header_level VARCHAR2(1),
invoice_wht_tax_group_hdr NUMBER(15),
allow_wht_tax_site_level VARCHAR2(1),
invoice_wht_tax_group_site NUMBER(15),
calc_tax_site_level VARCHAR2(1),
vat_reg_number VARCHAR2(20),
vat_code VARCHAR2(30),
hold_all_pay_hdr VARCHAR2(1),
hold_unmatch_inv_hdr VARCHAR2(1),
hold_unvali_inv_hdr VARCHAR2(1),
hold_reason_hdr VARCHAR2(240),
hold_all_pay_site VARCHAR2(1),
hold_unmatch_inv_site VARCHAR2(1),
hold_unvali_inv_site VARCHAR2(1),
hold_reason_site VARCHAR2(240),
pay_group VARCHAR2(25),
prefix VARCHAR2(60),
title VARCHAR2(30),
first_name VARCHAR2(15),
middle_name VARCHAR2(15),
last_name VARCHAR2(15),
contact_email VARCHAR2(2000),
department VARCHAR2(60),
contact_fax VARCHAR2(40),
contact_phone VARCHAR2(40),
contact_alt_phone VARCHAR2(40)
) ;
We have interface tables for Supplier Headers, Sites and Contacts:
AP_SUPPLIERS_INT
AP_SUPPLIER_SITES_INT
AP_SUPPLIER_SITES_INT
Base Tables:
AP_SUPPLIERS / PO_VENDORS
AP_SUPPLIER_SITES / PO_VENDOR_SITES_ALL
AP_SUPPLIER_CONTACTS / PO_VENDOR_CONTANCTS
I have above stage table columns in my excel data file, it is in .xls ( we can have in .csv also. if not in these two types, we need to format the file as per our process.
Dump the excel file Data into Stage table directly ( I have done this step using PL/SQL Developer).
After I have data in my stage table: I run the below code. Because of I have done this conversion one time only, it will never use in future, but if it is multi time process (Interface), just you can create the package or procedure, register in application as per your requirement you can go ahead and you can add more validations before importing into interface table and stage table with respect your business need.
======================================================================
DECLARE
CURSOR C_SUPP_DATA is(
SELECT distinct VENDOR_NAME,
VENDOR_NAME_ALT,
LEGACY_VENDOR_NUM,
VENDOR_TYPE,
PAY_GROUP,
ALLOW_WHT_TAX_HEADER_LEVEL,
INVOICE_WHT_TAX_GROUP_HDR,
HOLD_ALL_PAY_HDR,
HOLD_UNMATCH_INV_HDR,
HOLD_UNVALI_INV_HDR,
HOLD_REASON_HDR
FROM XX_SUPP_INT_STG);
CURSOR C_SITE_DATA(P_VENDOR_NAME varchar2) is(
SELECT distinct COUNTRY,
ADDRESS_LINE1,
ADDRESS_LINE2,
ADDRESS_LINE3,
ADDRESS_LINE4,
CITY,
STATE,
PROVINCE,
POSTAL_CODE,
VENDOR_SITE_CODE,
PURCHASING_SITE,
PAY_SITE,
PHONE_AREA_CODE,
PHONE_NUMBER,
FAX_AREA_CODE,
FAX,
SITE_EMAIL,
ALLOW_WHT_TAX_HEADER_LEVEL,
INVOICE_WHT_TAX_GROUP_HDR,
ALLOW_WHT_TAX_SITE_LEVEL,
INVOICE_WHT_TAX_GROUP_SITE,
CALC_TAX_SITE_LEVEL,
VAT_REG_NUMBER,
VAT_CODE,
HOLD_ALL_PAY_HDR,
HOLD_UNMATCH_INV_HDR,
HOLD_UNVALI_INV_HDR,
HOLD_REASON_HDR,
HOLD_ALL_PAY_SITE,
HOLD_UNMATCH_INV_SITE,
HOLD_UNVALI_INV_SITE,
HOLD_REASON_SITE,
PAY_GROUP,
ORGANIZATION_NAME
FROM XX_SUPP_INT_STG
where VENDOR_NAME = P_VENDOR_NAME);
CURSOR C_CONTACT_DATA(P_VENDOR_NAME varchar2, P_VENDOR_SITE varchar2) is(
select distinct PREFIX,
TITLE,
FIRST_NAME,
MIDDLE_NAME,
LAST_NAME,
CONTACT_EMAIL,
DEPARTMENT,
CONTACT_FAX,
CONTACT_PHONE,
CONTACT_ALT_PHONE
from XX_SUPP_INT_STG
where VENDOR_NAME = P_VENDOR_NAME
and VENDOR_SITE_CODE = P_VENDOR_SITE);
lc_site_org_id po_vendor_sites_all.org_id%type; -- := 50168; /* this need to remove*/
lc_vendor_interface_id ap_suppliers_int.vendor_interface_id%type;
lc_vendor_site_interface_id ap_supplier_sites_int.vendor_site_interface_id%type;
lc_vendor_contact_interface_id ap_sup_site_contact_int.vendor_contact_interface_id%type;
lc_vendor_cnt NUMBER := 0;
lc_user_id NUMBER;
BEGIN
FOR i IN C_SUPP_DATA LOOP
/* Main Loop */
lc_vendor_interface_id := ap_suppliers_int_s.nextval;
begin
INSERT INTO ap.ap_suppliers_int
(vendor_interface_id,
vendor_name,
vendor_name_alt,
vendor_type_lookup_code,
allow_awt_flag,
awt_group_id,
hold_all_payments_flag,
hold_unmatched_invoices_flag,
hold_future_payments_flag,
hold_reason,
pay_group_lookup_code,
attribute10)
VALUES
(lc_vendor_interface_id,
i.vendor_name,
i.vendor_name_alt,
i.vendor_type,
i.allow_wht_tax_header_level,
i.invoice_wht_tax_group_hdr,
i.hold_all_pay_hdr,
i.hold_unmatch_inv_hdr,
i.hold_unvali_inv_hdr,
i.hold_reason_hdr,
i.pay_group,
i.legacy_vendor_num );
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(i.vendor_name ||
' Error occured occured at Supplier Header ' ||
SQLCODE || ' - ' || SQLERRM);
end;
FOR j IN C_SITE_DATA(i.vendor_name) LOOP
BEGIN
SELECT organization_id
INTO lc_site_org_id
FROM hr_operating_units
WHERE NAME = j.organization_name ;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.PUT_LINE(j.organization_name, 'invalid Organization');
END;
begin
lc_vendor_site_interface_id := ap_supplier_sites_int_s.nextval;
INSERT INTO ap.ap_supplier_sites_int
(vendor_site_interface_id,
vendor_interface_id,
vendor_site_code,
address_line1,
address_line2,
address_line3,
address_line4,
city,
state,
country,
zip,
phone,
org_id,
province,
purchasing_site_flag,
pay_site_flag,
area_code,
fax_area_code,
fax,
email_address,
allow_awt_flag,
awt_group_id,
auto_tax_calc_flag,
vat_registration_num,
vat_code,
hold_all_payments_flag,
hold_future_payments_flag,
HOLD_REASON)
VALUES
(lc_vendor_site_interface_id,
lc_vendor_interface_id,
j.vendor_site_code,
j.address_line1,
j.address_line2,
j.address_line3,
j.address_line4,
j.city,
j.state,
j.country,
j.postal_code,
j.phone_number,
lc_site_org_id,
j.province,
j.purchasing_site,
j.pay_site,
j.phone_area_code,
j.fax_area_code,
j.fax,
j.site_email,
j.allow_wht_tax_site_level,
j.invoice_wht_tax_group_site,
j.calc_tax_site_level,
j.vat_reg_number,
j.vat_code,
j.hold_all_pay_site,
j.hold_unvali_inv_site,
j.HOLD_REASON_SITE);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(i.vendor_name || ' ' || j.vendor_site_code ||
' Error occured occured at Supplier Site ' ||
SQLCODE || ' - ' || SQLERRM);
end;
for k in C_CONTACT_DATA(i.vendor_name, j.vendor_site_code) LOOP
IF k.last_name IS NULL THEN --last name condition
DBMS_OUTPUT.PUT_LINE(i.vendor_name||''||j.vendor_site_code||''||'Contact not created because LAST NAME is null ');
ELSE
begin
lc_vendor_contact_interface_id :=ap_sup_site_contact_int_s.nextval ;
INSERT INTO ap.ap_sup_site_contact_int ss
(vendor_interface_id,
vendor_contact_interface_id,
vendor_site_code,
first_name,
middle_name,
last_name,
area_code,
phone,
first_name_alt,
last_name_alt,
email_address,
fax,
prefix,
title,
department,
alt_phone,
org_id)
VALUES
(lc_vendor_interface_id,
lc_vendor_contact_interface_id,
j.vendor_site_code,
k.first_name,
k.middle_name,
k.last_name,
'',--area_code
k.contact_phone,
NULL,
NULL,
k.contact_email,
k.contact_fax,
k.prefix,
k.title,
k.department,
NVL(k.contact_alt_phone,k.contact_phone),
lc_site_org_id);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(i.vendor_name || ' ' ||
j.vendor_site_code || ' ' || k.first_name ||
' Error occured at Supplier Contact ' ||
SQLCODE || ' - ' || SQLERRM);
END;
END IF; --last name condition
END LOOP; -- Contact Loop
END LOOP; -- Site Loop
END LOOP; -- Main Loop
COMMIT;
DBMS_OUTPUT.PUT_LINE('Interface ID: '||lc_vendor_interface_id);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Unhandled Exception occured at Supplier Header ' ||
SQLCODE || ' - ' || SQLERRM);
END;
======================================================================
After data inserted into Interface tables, we need to run the concurrent programs one after another to get the data into base tables, as I said above one time process so, I did not created one package and call everything there, but you can do that, if required.
Supplier Open Interface Import
Supplier Sites Open Interface Import
Supplier Contacts Open Interface Import
Once above programs completed Normal, you can see your migrated data in below base tables based on my legacy_vendor_number i.e
AP_SUPPLIERS / PO_VENDORS (attribute10 in this table )
AP_SUPPLIER_SITES / PO_VENDOR_SITES_ALL
AP_SUPPLIER_CONTACTS / PO_VENDOR_CONTANCTS
No comments:
Post a Comment