create or replace PACKAGE apps.XX_UPDATE_VEN_SITE_PKG
IS
--|===========================================================================|
--| TouchTunes |
--| |
--| Description : This package is used to update the supplier site details |
--| as per the business request, for detailed information |
--| please refer the JIRA ticket number #XXXX-8. |
--| |
--| Program Name : XX_UPDATE_VEN_SITE_PKG |
--| Module Name : AP |
--| |
--| Modification History: |
--| Name DATE Description Version |
--| --------------- ---------- ------------------------- -------- |
--| XXXXXXXXXXX 20-Jan-20 Created initial version V1.0 |
--|===========================================================================|
PROCEDURE UPDATE_VENDOR_SITE_DET (ERRBUF OUT VARCHAR2
, RETCODE OUT VARCHAR2
, P_ORG_ID IN NUMBER);
END XX_UPDATE_VEN_SITE_PKG;
/
create or replace PACKAGE BODY apps.XX_UPDATE_VEN_SITE_PKG
IS
--|===========================================================================|
--| TouchTunes |
--| |
--| Description : This package is used to update the supplier site details |
--| as per the business request, for detailed information |
--| please refer the JIRA ticket number #XXXXX-8. |
--| |
--| Program Name : XX_UPDATE_VEN_SITE_PKG |
--| Module Name : AP |
--| |
--| Modification History: |
--| Name DATE Description Version |
--| --------------- ---------- ------------------------- -------- |
--| XXXXXXXXXX 20-Jan-20 Created initial version V1.0 |
--|===========================================================================|
PROCEDURE UPDATE_VENDOR_SITE_DET (ERRBUF OUT VARCHAR2
, RETCODE OUT VARCHAR2
, P_ORG_ID IN NUMBER)
IS
l_vendor_site_rec ap_vendor_pub_pkg.r_vendor_site_rec_type;
l_return_status VARCHAR2(10);
l_msg_count NUMBER;
l_msg_data VARCHAR2(1000);
l_vendor_site_id NUMBER;
l_party_site_id NUMBER;
l_location_id NUMBER;
CURSOR lcu_sup_site_det
IS
SELECT
APS.vendor_id ,
-- APS.vendor_name "Supplier Name" ,
--APS.segment1 "Supplier Num" ,
APSS.VENDOR_SITE_ID ,
APSS.vendor_site_code vendor_site_code ,
APSS.ADDRESS_LINE1,
APSS.COUNTRY,
APSS.ORG_ID,
APSS.PURCHASING_SITE_FLAG,
APSS.RFQ_ONLY_SITE_FLAG,
APSS.PAY_SITE_FLAG,
--hou.name "Operating Unit Name" ,
(SELECT NAME FROM HR_OPERATING_UNITS WHERE ORGANIZATION_ID = APSS.ORG_ID ) NAME,
--APSS.PARTY_SITE_ID,
DECODE( P_ORG_ID , 261 ,41498, 102,19389, 262, 41471, APSS.SHIP_TO_LOCATION_ID) SHIP_TO_LOCATION_ID,
DECODE( P_ORG_ID , 261 ,41498, 102,19389, 262, 41471, APSS.BILL_TO_LOCATION_ID) BILL_TO_LOCATION_ID,
-- BILL_TO.LOCATION_CODE "BILL TO LOC",
-- SHIP_TO.LOCATION_CODE "SHIP TO LOC",
-- apss.ship_via_lookup_code "Ship Via",
--APSS.FREIGHT_TERMS_LOOKUP_CODE FREIGHT
DECODE( P_ORG_ID , 261 , 'UPS' ,apss.ship_via_lookup_code) ship_via_lookup_code ,
DECODE( P_ORG_ID , 261 , 'UPS GROUND' , APSS.FREIGHT_TERMS_LOOKUP_CODE) FREIGHT_TERMS_LOOKUP_CODE
FROM APPS.HR_EMPLOYEES HE,
APPS.HR_LOCATIONS_V SHIP_TO,
APPS.HR_LOCATIONS_V BILL_TO,
apps.AP_SUPPLIER_SITES_ALL APSS,
ap.AP_SUPPLIERS APS,
apps.hz_parties hp
WHERE HE.EMPLOYEE_ID(+) = APS.EMPLOYEE_ID
and aps.party_id=hp.party_id
AND SHIP_TO.LOCATION_ID(+) = APSS.SHIP_TO_LOCATION_ID
AND BILL_TO.LOCATION_ID = APSS.BILL_TO_LOCATION_ID
AND NVL(APSS.INACTIVE_DATE,SYSDATE+1) >= SYSDATE
AND APS.VENDOR_ID = APSS.VENDOR_ID
AND NVL(APS.END_DATE_ACTIVE,SYSDATE+1) >= SYSDATE
AND NVL(APS.ENABLED_FLAG,'Y') = 'Y'
AND APSS.ORG_ID = P_ORG_ID
--AND APSS.VENDOR_ID = 39 -- 546934 -- 547920
--AND BILL_TO.LOCATION_CODE = 'XXXXXX'
ORDER BY APS.vendor_id ,
APSS.VENDOR_SITE_ID ,
APSS.vendor_site_code;
BEGIN
FOR rec_sup_site_det IN lcu_sup_site_det LOOP
--Required
l_vendor_site_rec.vendor_id := rec_sup_site_det.vendor_id ;
l_vendor_site_rec.VENDOR_SITE_ID := rec_sup_site_det.VENDOR_SITE_ID ;
l_vendor_site_rec.vendor_site_code := rec_sup_site_det.vendor_site_code ;
l_vendor_site_rec.address_line1 := rec_sup_site_det.address_line1 ; --
l_vendor_site_rec.country := rec_sup_site_det.country ;
l_vendor_site_rec.org_id := rec_sup_site_det.org_id ;
l_vendor_site_rec.purchasing_site_flag := rec_sup_site_det.purchasing_site_flag ;
l_vendor_site_rec.pay_site_flag := rec_sup_site_det.pay_site_flag ;
l_vendor_site_rec.rfq_only_site_flag := rec_sup_site_det.rfq_only_site_flag ;
l_vendor_site_rec.SHIP_TO_LOCATION_ID := rec_sup_site_det.SHIP_TO_LOCATION_ID ;
l_vendor_site_rec.BILL_TO_LOCATION_ID := rec_sup_site_det.BILL_TO_LOCATION_ID ;
l_vendor_site_rec.SHIP_VIA_LOOKUP_CODE := rec_sup_site_det.SHIP_VIA_LOOKUP_CODE ;
l_vendor_site_rec.FREIGHT_TERMS_LOOKUP_CODE := rec_sup_site_det.FREIGHT_TERMS_LOOKUP_CODE ;
--pos_vendor_pub_pkg.create_vendor_site
--(
--p_vendor_site_rec => l_vendor_site_rec,
--x_return_status => l_return_status,
--x_msg_count => l_msg_count,
--x_msg_data => l_msg_data,
--x_vendor_site_id => l_vendor_site_id,
--x_party_site_id => l_party_site_id,
--x_location_id => l_location_id
--);
pos_vendor_pub_pkg.Update_Vendor_Site(p_vendor_site_rec => l_vendor_site_rec ,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
if l_return_status = 'S' then
dbms_output.put_line('Vendor Id: '||l_vendor_site_rec.vendor_id || ', Vendor Site Code: '||l_vendor_site_rec.vendor_site_code || ' , Org Name '|| rec_sup_site_det.NAME|| ', Ship to and Bill to: '||l_vendor_site_rec.SHIP_TO_LOCATION_ID);
fnd_file.put_line(fnd_file.output, 'Vendor Id: '||l_vendor_site_rec.vendor_id || ', Vendor Site Code: '||l_vendor_site_rec.vendor_site_code || ' , Org Name '|| rec_sup_site_det.NAME|| ', Ship to and Bill to: '||l_vendor_site_rec.SHIP_TO_LOCATION_ID );
-- dbms_output.put_line('Vendor Id: '||l_vendor_site_rec.vendor_id || ', Vendor Site Code: '||l_vendor_site_rec.vendor_site_code || ' , Org Name '|| rec_sup_site_det.NAME);
-- fnd_file.put_line(fnd_file.output, 'Vendor Id: '||l_vendor_site_rec.vendor_id || ', Vendor Site Code: '||l_vendor_site_rec.vendor_site_code || ' , Org Name '|| rec_sup_site_det.NAME );
else
-- if l_return_status <> 'S' then
dbms_output.put_line('Update failed for Vendor Id: '||l_vendor_site_rec.vendor_id || ', Vendor Site Code: '||l_vendor_site_rec.vendor_site_code || ' , Org Name '|| rec_sup_site_det.NAME);
fnd_file.put_line(fnd_file.log, 'Update failed for Vendor Id: '||l_vendor_site_rec.vendor_id || ', Vendor Site Code: '||l_vendor_site_rec.vendor_site_code || ' , Org Name '|| rec_sup_site_det.NAME );
dbms_output.put_line(' Error Message: '|| l_msg_data );
fnd_file.put_line(fnd_file.log, ' Error Message: '|| l_msg_data );
end if;
END LOOP;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line ('Error Message: '|| SQLCODE || ' , '|| SQLERRM );
fnd_file.put_line( fnd_file.log ,'Error Message: '|| SQLCODE || ' , '|| SQLERRM );
END UPDATE_VENDOR_SITE_DET;
END XX_UPDATE_VEN_SITE_PKG;
/
IS
--|===========================================================================|
--| TouchTunes |
--| |
--| Description : This package is used to update the supplier site details |
--| as per the business request, for detailed information |
--| please refer the JIRA ticket number #XXXX-8. |
--| |
--| Program Name : XX_UPDATE_VEN_SITE_PKG |
--| Module Name : AP |
--| |
--| Modification History: |
--| Name DATE Description Version |
--| --------------- ---------- ------------------------- -------- |
--| XXXXXXXXXXX 20-Jan-20 Created initial version V1.0 |
--|===========================================================================|
PROCEDURE UPDATE_VENDOR_SITE_DET (ERRBUF OUT VARCHAR2
, RETCODE OUT VARCHAR2
, P_ORG_ID IN NUMBER);
END XX_UPDATE_VEN_SITE_PKG;
/
create or replace PACKAGE BODY apps.XX_UPDATE_VEN_SITE_PKG
IS
--|===========================================================================|
--| TouchTunes |
--| |
--| Description : This package is used to update the supplier site details |
--| as per the business request, for detailed information |
--| please refer the JIRA ticket number #XXXXX-8. |
--| |
--| Program Name : XX_UPDATE_VEN_SITE_PKG |
--| Module Name : AP |
--| |
--| Modification History: |
--| Name DATE Description Version |
--| --------------- ---------- ------------------------- -------- |
--| XXXXXXXXXX 20-Jan-20 Created initial version V1.0 |
--|===========================================================================|
PROCEDURE UPDATE_VENDOR_SITE_DET (ERRBUF OUT VARCHAR2
, RETCODE OUT VARCHAR2
, P_ORG_ID IN NUMBER)
IS
l_vendor_site_rec ap_vendor_pub_pkg.r_vendor_site_rec_type;
l_return_status VARCHAR2(10);
l_msg_count NUMBER;
l_msg_data VARCHAR2(1000);
l_vendor_site_id NUMBER;
l_party_site_id NUMBER;
l_location_id NUMBER;
CURSOR lcu_sup_site_det
IS
SELECT
APS.vendor_id ,
-- APS.vendor_name "Supplier Name" ,
--APS.segment1 "Supplier Num" ,
APSS.VENDOR_SITE_ID ,
APSS.vendor_site_code vendor_site_code ,
APSS.ADDRESS_LINE1,
APSS.COUNTRY,
APSS.ORG_ID,
APSS.PURCHASING_SITE_FLAG,
APSS.RFQ_ONLY_SITE_FLAG,
APSS.PAY_SITE_FLAG,
--hou.name "Operating Unit Name" ,
(SELECT NAME FROM HR_OPERATING_UNITS WHERE ORGANIZATION_ID = APSS.ORG_ID ) NAME,
--APSS.PARTY_SITE_ID,
DECODE( P_ORG_ID , 261 ,41498, 102,19389, 262, 41471, APSS.SHIP_TO_LOCATION_ID) SHIP_TO_LOCATION_ID,
DECODE( P_ORG_ID , 261 ,41498, 102,19389, 262, 41471, APSS.BILL_TO_LOCATION_ID) BILL_TO_LOCATION_ID,
-- BILL_TO.LOCATION_CODE "BILL TO LOC",
-- SHIP_TO.LOCATION_CODE "SHIP TO LOC",
-- apss.ship_via_lookup_code "Ship Via",
--APSS.FREIGHT_TERMS_LOOKUP_CODE FREIGHT
DECODE( P_ORG_ID , 261 , 'UPS' ,apss.ship_via_lookup_code) ship_via_lookup_code ,
DECODE( P_ORG_ID , 261 , 'UPS GROUND' , APSS.FREIGHT_TERMS_LOOKUP_CODE) FREIGHT_TERMS_LOOKUP_CODE
FROM APPS.HR_EMPLOYEES HE,
APPS.HR_LOCATIONS_V SHIP_TO,
APPS.HR_LOCATIONS_V BILL_TO,
apps.AP_SUPPLIER_SITES_ALL APSS,
ap.AP_SUPPLIERS APS,
apps.hz_parties hp
WHERE HE.EMPLOYEE_ID(+) = APS.EMPLOYEE_ID
and aps.party_id=hp.party_id
AND SHIP_TO.LOCATION_ID(+) = APSS.SHIP_TO_LOCATION_ID
AND BILL_TO.LOCATION_ID = APSS.BILL_TO_LOCATION_ID
AND NVL(APSS.INACTIVE_DATE,SYSDATE+1) >= SYSDATE
AND APS.VENDOR_ID = APSS.VENDOR_ID
AND NVL(APS.END_DATE_ACTIVE,SYSDATE+1) >= SYSDATE
AND NVL(APS.ENABLED_FLAG,'Y') = 'Y'
AND APSS.ORG_ID = P_ORG_ID
--AND APSS.VENDOR_ID = 39 -- 546934 -- 547920
--AND BILL_TO.LOCATION_CODE = 'XXXXXX'
ORDER BY APS.vendor_id ,
APSS.VENDOR_SITE_ID ,
APSS.vendor_site_code;
BEGIN
FOR rec_sup_site_det IN lcu_sup_site_det LOOP
--Required
l_vendor_site_rec.vendor_id := rec_sup_site_det.vendor_id ;
l_vendor_site_rec.VENDOR_SITE_ID := rec_sup_site_det.VENDOR_SITE_ID ;
l_vendor_site_rec.vendor_site_code := rec_sup_site_det.vendor_site_code ;
l_vendor_site_rec.address_line1 := rec_sup_site_det.address_line1 ; --
l_vendor_site_rec.country := rec_sup_site_det.country ;
l_vendor_site_rec.org_id := rec_sup_site_det.org_id ;
l_vendor_site_rec.purchasing_site_flag := rec_sup_site_det.purchasing_site_flag ;
l_vendor_site_rec.pay_site_flag := rec_sup_site_det.pay_site_flag ;
l_vendor_site_rec.rfq_only_site_flag := rec_sup_site_det.rfq_only_site_flag ;
l_vendor_site_rec.SHIP_TO_LOCATION_ID := rec_sup_site_det.SHIP_TO_LOCATION_ID ;
l_vendor_site_rec.BILL_TO_LOCATION_ID := rec_sup_site_det.BILL_TO_LOCATION_ID ;
l_vendor_site_rec.SHIP_VIA_LOOKUP_CODE := rec_sup_site_det.SHIP_VIA_LOOKUP_CODE ;
l_vendor_site_rec.FREIGHT_TERMS_LOOKUP_CODE := rec_sup_site_det.FREIGHT_TERMS_LOOKUP_CODE ;
--pos_vendor_pub_pkg.create_vendor_site
--(
--p_vendor_site_rec => l_vendor_site_rec,
--x_return_status => l_return_status,
--x_msg_count => l_msg_count,
--x_msg_data => l_msg_data,
--x_vendor_site_id => l_vendor_site_id,
--x_party_site_id => l_party_site_id,
--x_location_id => l_location_id
--);
pos_vendor_pub_pkg.Update_Vendor_Site(p_vendor_site_rec => l_vendor_site_rec ,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
if l_return_status = 'S' then
dbms_output.put_line('Vendor Id: '||l_vendor_site_rec.vendor_id || ', Vendor Site Code: '||l_vendor_site_rec.vendor_site_code || ' , Org Name '|| rec_sup_site_det.NAME|| ', Ship to and Bill to: '||l_vendor_site_rec.SHIP_TO_LOCATION_ID);
fnd_file.put_line(fnd_file.output, 'Vendor Id: '||l_vendor_site_rec.vendor_id || ', Vendor Site Code: '||l_vendor_site_rec.vendor_site_code || ' , Org Name '|| rec_sup_site_det.NAME|| ', Ship to and Bill to: '||l_vendor_site_rec.SHIP_TO_LOCATION_ID );
-- dbms_output.put_line('Vendor Id: '||l_vendor_site_rec.vendor_id || ', Vendor Site Code: '||l_vendor_site_rec.vendor_site_code || ' , Org Name '|| rec_sup_site_det.NAME);
-- fnd_file.put_line(fnd_file.output, 'Vendor Id: '||l_vendor_site_rec.vendor_id || ', Vendor Site Code: '||l_vendor_site_rec.vendor_site_code || ' , Org Name '|| rec_sup_site_det.NAME );
else
-- if l_return_status <> 'S' then
dbms_output.put_line('Update failed for Vendor Id: '||l_vendor_site_rec.vendor_id || ', Vendor Site Code: '||l_vendor_site_rec.vendor_site_code || ' , Org Name '|| rec_sup_site_det.NAME);
fnd_file.put_line(fnd_file.log, 'Update failed for Vendor Id: '||l_vendor_site_rec.vendor_id || ', Vendor Site Code: '||l_vendor_site_rec.vendor_site_code || ' , Org Name '|| rec_sup_site_det.NAME );
dbms_output.put_line(' Error Message: '|| l_msg_data );
fnd_file.put_line(fnd_file.log, ' Error Message: '|| l_msg_data );
end if;
END LOOP;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line ('Error Message: '|| SQLCODE || ' , '|| SQLERRM );
fnd_file.put_line( fnd_file.log ,'Error Message: '|| SQLCODE || ' , '|| SQLERRM );
END UPDATE_VENDOR_SITE_DET;
END XX_UPDATE_VEN_SITE_PKG;
/
