Thursday 11 January 2018

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;

5 comments:

Anonymous said...

Good Blog Thanks For Sharing this informative airtrl/
Oracle Fusion HCM Online Training
Oracle Fusion SCM Online Training
Oracle Fusion Financials Online Training
Big Data and Hadoop Training In Hyderabad

Anonymous said...

Thanks for sharing This informative article. It would be helpful to all ,Keep sharing.
Oracle Fusion HCM Online Training

shaik shah said...

Good Blog thanks for sharing this informative article. It would be helpful for improving their knowledge.
Oracle Fusion HCM Online Training

yamuna said...

Nice blog, thanks for sharing this information.
Oracle Fusion HCM Online Training

Lakshmi Satya said...

Such a Nice blog,Very useful article,Thanks for sharing this information.
Oracle Fusion Financials Training | Oracle Cloud Financials Training

Post a Comment

Contact us for any Collaboration, Project Support & On Job Support Work

Name

Email *

Message *