/* Formatted on 5/24/2018 12:34:40 PM (QP5 v5.163.1008.3004) */
CREATE OR REPLACE PACKAGE ebiz_ven_contact
IS
PROCEDURE xxven_contact_creation;
END ebiz_ven_contact;
/
CREATE OR REPLACE PACKAGE BODY ebiz_ven_contact
IS
PROCEDURE xxven_contact_creation
IS
ln_vendor_id NUMBER;
ln_vendor_site_id NUMBER;
ln_vendor_count NUMBER;
ln_vendor_site_count NUMBER;
ln_vendor_site_code VARCHAR2(100);
CURSOR ebiz_vendor_contact
IS
SELECT * FROM ebiz_cas_payee_secure;
CURSOR ebiz_vendor_sites (p_vendor_id NUMBER)
IS
SELECT vendor_site_id
FROM ap_supplier_sites_all
WHERE vendor_id = p_vendor_id;
BEGIN
FOR cur_ebiz_vc IN ebiz_vendor_contact
LOOP
SELECT COUNT (1)
INTO ln_vendor_count
FROM ap_suppliers
WHERE vendor_name = cur_ebiz_vc.ebiz_payee_name_code;
IF ln_vendor_count > 0
THEN
SELECT vendor_id
INTO ln_vendor_id
FROM ap_suppliers
WHERE vendor_name = cur_ebiz_vc.ebiz_payee_name_code;
SELECT COUNT (*)
INTO ln_vendor_site_counT
FROM ap_supplier_sites_all
WHERE vendor_id = ln_vendor_id;
FOR ebiz_vsites_cur IN ebiz_vendor_sites (ln_vendor_id)
LOOP
SELECT vendor_site_code
INTO ln_vendor_site_code
FROM ap_supplier_sites_all
WHERE vendor_id = ln_vendor_id and vendor_site_id=ebiz_vsites_cur.vendor_site_id;
INSERT
INTO AP_SUP_SITE_CONTACT_INT (vendor_contact_interface_id,
vendor_id,
vendor_site_id,
vendor_site_code,
FIRST_NAME,
MIDDLE_NAME,
LAST_NAME,
AREA_CODE,org_id)
VALUES (AP_SUP_SITE_CONTACT_INT_S.NEXTVAL,
ln_vendor_id,
ebiz_vsites_cur.vendor_site_id,
ln_vendor_site_code,
'ebiz-FIRST1',
'ebiz-FIRST2',
'ebiz-FIRST3',
cur_ebiz_vc.CONTACT_ADDRESS_POSTCODE,204);
END LOOP;
END IF;
END LOOP;
END xxven_contact_creation;
END ebiz_ven_contact;
/
SHOW ERR
