In this post , We will be discuss about Oracle Interface to Upload Supplier Communication Details.
Oracle Interface
to Upload Supplier Contact Information in Oracle Apps
Through Interface
PROCEDURE xx_supplier_contacts
IS
CURSOR cur_supplier_site
IS
SELECT *
FROM xx_supp_stag_all
WHERE entry_id IN (
SELECT MAX (entry_id)
FROM xx_supp_stag_all
WHERE NVL (vailidate_flag_process, 'E') = 'P'
AND flag_process = 'P'
AND nvl(load_flag_contact,'NULL')='NULL'
GROUP BY org_code,
supplier_name,
supplier_site_name,
country) AND NVL (vailidate_flag_process, 'E') = 'P'
AND FLAG_PROCESS = 'P'
AND nvl(load_flag_contact,'NULL')='NULL' ;
CURSOR operating_org_id (v_operating_unit VARCHAR2)
IS
SELECT organization_id
FROM hr_operating_units
WHERE UPPER (NAME) = UPPER (v_operating_unit);
v_vendor_id NUMBER;
v_vendor_site_id NUMBER;
v_interface_id NUMBER;
v_cont_num NUMBER;
v_sqlerrm VARCHAR2 (2000);
p_organization_id NUMBER;
BEGIN
---------------------------Loop Start--------------- <<supp_cont>>
FOR i IN cur_supplier_site
LOOP
---------------Refresh vairiable
---------
v_vendor_id := NULL;
v_vendor_site_id := NULL;
v_interface_id := NULL;
v_cont_num := NULL;
v_sqlerrm := NULL;
p_organization_id := NULL;
OPEN operating_org_id (i.org_code);
FETCH operating_org_id
INTO p_organization_id;
IF operating_org_id%NOTFOUND
THEN
p_organization_id := NULL;
CLOSE operating_org_id;
END IF;
CLOSE operating_org_id;
-------------for vendor_id ----------
BEGIN
SELECT vendor_id
INTO v_vendor_id
FROM po_vendors
WHERE UPPER (vendor_name) = UPPER (i.supplier_name);
EXCEPTION
WHEN NO_DATA_FOUND
THEN
v_vendor_id := NULL;
WHEN OTHERS
THEN
v_vendor_id := NULL;
END;
-------------for vendor_site_id
----------
IF v_vendor_id IS NOT NULL
THEN
BEGIN
SELECT vendor_site_id
INTO v_vendor_site_id
FROM po_vendor_sites_all
WHERE vendor_id = v_vendor_id
AND UPPER (vendor_site_code) = UPPER (i.supplier_site_name)
AND org_id = p_organization_id;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
v_vendor_site_id := NULL;
WHEN OTHERS
THEN
v_vendor_site_id := NULL;
END;
ELSE
v_vendor_site_id := NULL;
END IF;
-------------for vendor_interface_id ----------
BEGIN
SELECT vendor_interface_id
INTO v_interface_id
FROM ap_suppliers_int
WHERE UPPER (vendor_name) = UPPER (i.supplier_name);
EXCEPTION
WHEN NO_DATA_FOUND
THEN
v_interface_id := NULL;
WHEN OTHERS
THEN
v_interface_id := NULL;
END;
--------------COUNT CONTACT REC FOR sUPPLIER-------------
IF v_vendor_id IS NOT NULL
THEN
BEGIN
SELECT COUNT (*)
INTO v_cont_num
FROM po_vendor_contacts
WHERE vendor_id = v_vendor_id;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
v_cont_num := NULL;
WHEN OTHERS
THEN
v_cont_num := NULL;
END;
ELSE
v_cont_num := NULL;
END IF;
IF v_cont_num = 0
THEN
BEGIN
INSERT INTO ap_sup_site_contact_int
(vendor_interface_id, vendor_id,
vendor_contact_interface_id,
vendor_site_id, org_id,
title, first_name, middle_name,
last_name, area_code,
phone, alt_area_code,
alt_phone, fax_area_code,
fax, email_address
)
VALUES (v_interface_id, v_vendor_id,
ap_sup_site_contact_int_s.NEXTVAL,
v_vendor_site_id, p_organization_id,
NVL (i.title, '.'), i.first_name, i.middle_name,
NVL (i.last_name, '.'), i.std_code,
SUBSTR (i.phone_number, 1, 15), i.std_code,
SUBSTR (i.phone_number, 1, 15), i.std_code,
i.fax_number, i.email_address
);
UPDATE xx_supp_stag_all
SET load_flag_contact = 'P'
WHERE entry_id = i.entry_id;
GOTO next_rec;
EXCEPTION
WHEN OTHERS
THEN
v_sqlerrm := SQLERRM;
UPDATE xx_supp_stag_all
SET load_flag_contact = 'E',
error_message = v_sqlerrm
WHERE entry_id = i.entry_id;
GOTO next_rec;
END;
END IF;
<<next_rec>>
COMMIT;
END LOOP supp_cont;
END xx_supplier_contacts;
No comments:
Post a Comment