Oracle Interface to Upload Supplier Communication Details

Oracle Interface to Upload Supplier Communication Details

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;