Create table for supplier site
create table XXAP_SUPPLIER_SITE_STAGE
(
SUPPLIER_NAME VARCHAR2(100)
,SUPP_SITE_CODE VARCHAR2(100)
,ADDRESS1 VARCHAR2(100)
,ADDRESS2 VARCHAR2(100)
,CITY VARCHAR2(50)
,COUNTRY VARCHAR2(50)
,CURRENCY_CODE VARCHAR2(50)
,PHONE_NUMBER VARCHAR2(50)
,FAX VARCHAR2(50)
,EMAIL VARCHAR2(50)
,VENDOR_INTERFACE_ID NUMBER(15)
,AREA_CODE VARCHAR2(15)
,FIRST_NAME_ALT VARCHAR2(200)
,LAST_NAME_ALT VARCHAR2(200)
,FIRST_NAME VARCHAR2(20)
,LAST_NAME VARCHAR2(20)
);
load data
infile '------------------------------------------------------'
insert into table XXAP_SUPPLIER_SITE_STAGE
fields terminated by ','
optionally enclosed by '"'
trailing nullcols
(SUPPLIER_NAME
,SUPP_SITE_CODE
,ADDRESS1
,ADDRESS2
,CITY
,COUNTRY
,CURRENCY_CODE
,PHONE_NUMBER
,FAX
,EMAIL
,VENDOR_INTERFACE_ID
,AREA_CODE
,FIRST_NAME_ALT
,LAST_NAME_ALT
,FIRST_NAME
,LAST_NAME)
Create a procedure for SUPPLIER LOAD:
CREATE OR REPLACE PROCEDURE XXAP_SUPPLIER_LOAD(x_errbuf OUT VARCHAR2,
x_retcode OUT NUMBER,
p_array_size IN PLS_INTEGER DEFAULT 300)
IS
TYPE test_array IS TABLE OF xxap_supplier_site_stage%ROWTYPE INDEX BY BINARY_INTEGER;
--v_vendor_interface_id test_array;
-- v_supplier_name test_array;
v_vendors test_array ;
v_vendor_type VARCHAR2(30);
v_interface_id NUMBER;
BEGIN
SELECT * BULK COLLECT INTO v_vendors FROM xxap_supplier_site_stage ORDER BY supp_site_code;
FOR i IN 1..v_vendors.COUNT
LOOP
SELECT ap_suppliers_int_s.NEXTVAL INTO v_interface_id FROM dual;
INSERT INTO ap_suppliers_int (vendor_interface_id,vendor_name) VALUES (v_interface_id ,INITCAP(v_vendors(i).supplier_name));
END LOOP;
END ;
/
create a procedure for SUPPLIERS CONTACT LOAD:
CREATE OR REPLACE PROCEDURE XXAP_SUPPLIERS_CONTACT_LOAD(x_errbuf OUT VARCHAR2, x_retcode OUT NUMBER)
IS
TYPE test_array IS TABLE OF xxap_supplier_site_stage%ROWTYPE INDEX BY BINARY_INTEGER;
v_vendor_id VARCHAR2(30);
v_vendor_int_id VARCHAR2(30);
v_vendors test_array ;
--v_vendor_site_id number;
BEGIN
--open c1;
SELECT * BULK COLLECT INTO v_vendors FROM xxap_supplier_site_stage ORDER BY supp_site_code;
FOR i IN 1..v_vendors.COUNT
LOOP
BEGIN
SELECT vendor_site_code
INTO v_vendor_int_id
FROM ap_supplier_sites_int a
WHERE a. vendor_site_code = v_vendors(I).supp_site_code;
EXCEPTION WHEN OTHERS THEN
NULL;
END;
--SELECT ap_sup_site_contact_int_seq.NEXTVAL INTO v_vendor_site_id FROM dual;
INSERT INTO ap_sup_site_contact_int
(vendor_site_code
,first_name
,last_name
,area_code
,phone
,first_name_alt
,last_name_alt
,email_address
,fax
,org_id
--,vendor_id
--creation_date
)
VALUES
(
v_vendor_int_id
,v_vendors(i).first_name
,v_vendors(i).last_name
,v_vendors(i).area_code
,v_vendors(i).phone_number
,v_vendors(i).first_name_alt
,v_vendors(i).last_name_alt
,v_vendors(i).email
,v_vendors(i).fax
,104
--,v_vendor_id
--SYSDATE
);
END LOOP;
END;
/
create a procedure for SUPPLIER SITE LOAD:
CREATE OR REPLACE PROCEDURE XXAP_SUPPLIER_SITE_LOAD(x_errbuf OUT VARCHAR2, x_retcode OUT NUMBER)
IS
TYPE test_array IS TABLE OF xxap_supplier_site_stage%ROWTYPE INDEX BY BINARY_INTEGER;
v_vendor_id VARCHAR2(100);
v_vendor_int_id VARCHAR2(100);
v_vendors test_array ;
--vv_vendor_interface_id number;
-- CURSOR C1
-- IS
-- SELECT *
-- FROM xxap_supplier_site_stage
-- order by SUPP_SITE_CODE;
BEGIN
--open c1;
SELECT * BULK COLLECT INTO v_vendors FROM xxap_supplier_site_stage ORDER BY supp_site_code;
FOR i IN 1..v_vendors.COUNT
LOOP
BEGIN
SELECT vendor_id
INTO v_vendor_id
FROM po_vendors
WHERE UPPER(vendor_name) = UPPER(v_vendors(i).supplier_name);
EXCEPTION WHEN OTHERS THEN
NULL;
END;
/* BEGIN
SELECT vendor_interface_id
INTO v_vendor_int_id
FROM ap_suppliers_int
WHERE UPPER(vendor_name) = UPPER(v_vendors(I).supplier_name);
EXCEPTION WHEN OTHERS THEN
NULL;
END;*/
--v_vendor_id(C_REC):=vv_vendor_id;
--v_VENDOR_INT_ID(C_REC):= vv_VENDOR_INT_ID;
--end loop;
--BEGIN
-- open C_REC;
-- FETCH C_REC BULK COLLECT INTO v_vendor_id,v_VENDOR_INT_ID;
-- for i in 1 .. v_VENDOR_INT_ID.count
-- LOOP
-- SELECT ap_supplier_sites_int_s.NEXTVAL INTO v_vendor_int_id FROM dual;
INSERT INTO ap_supplier_sites_int
(vendor_interface_id
--,vendor_site_interface_id
,vendor_id
,vendor_site_code
,address_line1
,address_line2
,city
,country
,phone
,fax
,email_address
,invoice_currency_code
,payment_currency_code
,org_id
,pay_site_flag
,purchasing_site_flag
)
VALUES
( v_vendors(i).VENDOR_INTERFACE_ID
--,ap_suppliers_int_s.nextval
,v_vendor_id
,v_vendors(i).supp_site_code
,v_vendors(i).address1
,v_vendors(i).address2
,v_vendors(i).city
,v_vendors(i).country
,SUBSTR(v_vendors(i).phone_number,1,15)
,SUBSTR(v_vendors(i).fax,1,15)
,v_vendors(i).email
,'USD' --C_REC.CURRENCY_CODE
,'USD' --C_REC.CURRENCY_CODE
,104
,'Y'
,'Y'
);
END LOOP;
-- close C1;
COMMIT;
EXCEPTION
WHEN too_many_rows THEN
v_vendor_id :=NULL;
WHEN no_data_found THEN
v_vendor_id :=NULL;
-- END LOOP;
-- END;
END;
/
Ur's
AmarAlam
