/* Formatted on 10/12/2020 7:56:18 PM (QP5 v5.277) */
DECLARE
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_id NUMBER;
p_count NUMBER;
v_api_error VARCHAR2 (500);
v_error_msg VARCHAR2 (500) := NULL;
l_msg VARCHAR2 (200);
x_party_site_id NUMBER;
l_party_site_id NUMBER;
x_location_id NUMBER;
l_location_id NUMBER;
p_code_combination_id NUMBER;
p_code_combination_id1 NUMBER;
p_segments APPS.FND_FLEX_EXT.SEGMENTARRAY;
p_segments1 APPS.FND_FLEX_EXT.SEGMENTARRAY;
bool boolean;
p_api_version NUMBER;
p_init_msg_list VARCHAR2(200);
p_commit VARCHAR2(200);
p_validation_level NUMBER;
z_segment3 varchar2(500);
z_segment10 varchar2(500);
l_segment2 varchar2(500):=NULL;
l_segment9 varchar2(500) :=NULL;
L_SHIP_TO_LOCATION_ID NUMBER;
l_BILL_TO_LOCATION_ID number;
l_hz_location_id number;
l_ccid number;
l_data_Set number:=null;
CURSOR l_data
IS
SELECT xass.vendor_site_id,
xass.vendor_id,
xas.new_vendor_id,
xass.vendor_site_code,
xas.vendor_type_lookup_code,
xass.address_line1,
xass.address_line2,
xass.address_line3 ,--
xass.ADDRESS_STYLE ,--
xass.zip ,--
xass.province ,--
xass.city,
xass.country,
xass.area_code,
xass.phone,
xass.fax,
xass.fax_area_code,
xass.term_date_basis,
xas.vendor_name,
xass.accts_pay_code_combination_id,
xss.segment1,
xss.segment2,
xss.segment3,
xss.segment4,
xss.segment5,
xss.segment6,
xss.segment7,
xss.segment8, --'I0000000'
xss.segment9,
xss.segment10,
xss.segment11,
xss.segment12,
xass.prepay_code_combination_id,
xss.segment13,
xss.segment14,
xss.segment15,
xss.segment16,
xss.segment17,
xss.segment18,
xss.segment19,
xss.segment20 ,--'I0000000' segment20,
xss.segment21,
xss.segment22,
xss.segment23,
xss.segment24,
xass.pay_group_lookup_code,
xass.payment_priority,
(SELECT DISTINCT term_id
FROM ap_terms_tl
WHERE name = xass.terms_name)
terms_id,
xass.terms_name,
xass.pay_date_basis_lookup_code,
xass.invoice_currency_code,
xass.attribute9,
xass.attribute10,
xass.attribute11,
xass.attribute12,
xass.attribute13,
xass.attribute14,
xass.attribute15,
xass.validation_number,
xass.org_id org_id_old,
(select organization_id from hr_organization_units where name=xass.org_name) org_id,
xass.org_name,
xass.language,
xass.location_id,
--(select location_id from hz_locations where address1= xass.location_name and rownum=1) new_location_id,
xass.location_name,
xass.party_site_id,
xass.tca_sync_city,
xass.tca_sync_country,
xass.new_vendor_site_id,
xass.status,
xass.ALLOW_AWT_FLAG ,
xass.PURCHASING_SITE_FLAG ,
xass.RFQ_ONLY_SITE_FLAG ,
xass.PAY_SITE_FLAG ,
xass.primary_pay_site_flag,
xass.SHIP_TO_LOCATION_ID ,
xass.SHIP_TO_LOCATION_NAME,
xass.BILL_TO_LOCATION_ID
FROM XX.XX_AP_SUPPLIER_SITES_ALL xass, XX.XX_AP_SUPPLIERS xas, xx.xx_supplier_sites_detail xss
WHERE xass.vendor_id =xas.vendor_id and xas.new_vendor_id is not null
AND xss.vendor_id=xas.vendor_id
AND xss.VENDOR_SITE_ID=xass.VENDOR_SITE_ID
AND xass.status is null;
BEGIN
p_api_version := 1.0;
p_init_msg_list := fnd_api.g_true;
p_commit := fnd_api.g_true;
p_validation_level := fnd_api.g_valid_level_full;
fnd_global.apps_initialize (user_id => 1110,
resp_id => 50779,
resp_appl_id => 200);
mo_global.init ('SQLAP');
--fnd_client_info.set_org_context (101);
FOR n_data IN l_data
LOOP
-- --------------
-- Required
-- --------------
IF n_data.vendor_type_lookup_code ='EMPLOYEE' and n_data.org_id_old = 81 THEN
l_segment2 :='33502';
l_segment9 :='19501';
SELECT LOCATION_ID INTO l_location_id FROM HR_LOCATIONS
WHERE LOCATION_CODE= -- default location_code
L_SHIP_TO_LOCATION_ID :=l_location_id;
l_BILL_TO_LOCATION_ID :=l_location_id;
ELSIF n_data.vendor_type_lookup_code ='EMPLOYEE' and n_data.org_id_old = 141 THEN
l_segment2 :='33502001';
l_segment9 :='19501001';
SELECT location_id INTO l_location_id FROM HR_LOCATIONS
WHERE LOCATION_CODE= -- default location_code
L_SHIP_TO_LOCATION_ID :=l_location_id;
l_BILL_TO_LOCATION_ID :=l_location_id;
ELSE
/* Formatted on 12/7/2020 12:39:03 AM (QP5 v5.277) */
BEGIN
SELECT location_id
INTO l_location_id
FROM HR_LOCATIONS
WHERE LOCATION_CODE = -- default location_code
L_SHIP_TO_LOCATION_ID := l_location_id;
l_BILL_TO_LOCATION_ID := l_location_id;
DBMS_OUTPUT.PUT_lINE ('1-location_id=' || l_location_id);
EXCEPTION
WHEN OTHERS
THEN
L_SHIP_TO_LOCATION_ID := NULL;
l_BILL_TO_LOCATION_ID := NULL;
DBMS_OUTPUT.PUT_lINE ('2-location_id=' || L_SHIP_TO_LOCATION_ID);
END;
END IF;
-- z_segment3 := xx_get_cc_segment3_po (n_data.segment3);
p_segments (1) := n_data.segment1;
p_segments (2) := n_data.segment2;--NVL(l_segment2,n_data.segment2); --l_segment2
p_segments (3) := n_data.segment3;--NVL(z_segment3,n_data.segment3);
p_segments (4) := n_data.segment4;
p_segments (5) := n_data.segment5;
p_segments (6) := n_data.segment6;
p_segments (7) := n_data.segment7;
p_segments (8) := n_data.segment8;
p_segments (9) := n_data.segment9;
p_segments (10) := n_data.segment10;
p_segments (11) := n_data.segment11;
p_segments (12) := n_data.segment12;
p_code_combination_id :=
xx_get_ccid (50388,
SYSDATE,
12,
p_segments);
dbms_output.put_line('ccid1 : ' || p_code_combination_id || ' - ' || fnd_flex_ext.get_message || 'vendor_site_id='|| n_data.vendor_site_id);
--z_segment10 := xx_get_cc_segment3_po (n_data.segment10);
p_segments1 (1) := n_data.segment13;
p_segments1 (2) := n_data.segment14;--NVL(l_segment9,n_data.segment9); --l_segment9
p_segments1 (3) := n_data.segment15;-- NVL(z_segment10,n_data.segment10);
p_segments1 (4) := n_data.segment16;
p_segments1 (5) := n_data.segment17;
p_segments1 (6) := n_data.segment18;
p_segments1 (7) := n_data.segment19;
p_segments1 (8) := n_data.segment20;
p_segments1 (9) := n_data.segment21;
p_segments1 (10) := n_data.segment22;
p_segments1 (11) := n_data.segment23;
p_segments1 (12) := n_data.segment24;
p_code_combination_id1 :=
xx_get_ccid (50388,
SYSDATE,
12,
p_segments1);
dbms_output.put_line('ccid2 : ' || p_code_combination_id1 || ' - ' || fnd_flex_ext.get_message|| 'vendor_site_id='|| n_data.vendor_site_id);
IF p_code_combination_id IS NOT NULL OR p_code_combination_id1 IS NOT NULL THEN
l_vendor_site_rec.VENDOR_SITE_CODE := n_data.VENDOR_SITE_CODE;
l_vendor_site_rec.VENDOR_ID := n_data.new_vendor_id;
l_vendor_site_rec.ADDRESS_LINE1 := n_data.ADDRESS_LINE1;
l_vendor_site_rec.ADDRESS_LINE2 := n_data.ADDRESS_LINE2;
l_vendor_site_rec.address_line3 := n_data.address_line3;
l_vendor_site_rec.ADDRESS_STYLE := n_data.ADDRESS_STYLE;
l_vendor_site_rec.zip := n_data.zip;
l_vendor_site_rec.province := n_data.province;
l_vendor_site_rec.CITY := n_data.CITY;
l_vendor_site_rec.country := n_data.COUNTRY;
l_vendor_site_rec.AREA_CODE := n_data.AREA_CODE;
l_vendor_site_rec.PHONE := n_data.PHONE;
l_vendor_site_rec.FAX := n_data.FAX;
l_vendor_site_rec.FAX_AREA_CODE := n_data.FAX_AREA_CODE;
l_vendor_site_rec.TERMS_DATE_BASIS := n_data.TERM_DATE_BASIS;
l_vendor_site_rec.ACCTS_PAY_CODE_COMBINATION_ID := p_code_combination_id ;
l_vendor_site_rec.PREPAY_CODE_COMBINATION_ID := p_code_combination_id1;
l_vendor_site_rec.PAY_GROUP_LOOKUP_CODE := n_data.PAY_GROUP_LOOKUP_CODE;
l_vendor_site_rec.PAYMENT_PRIORITY := n_data.PAYMENT_PRIORITY;
l_vendor_site_rec.TERMS_ID := n_data.TERMS_ID;
l_vendor_site_rec.PAY_DATE_BASIS_LOOKUP_CODE := n_data.PAY_DATE_BASIS_LOOKUP_CODE;
l_vendor_site_rec.INVOICE_CURRENCY_CODE := n_data.INVOICE_CURRENCY_CODE;
l_vendor_site_rec.ATTRIBUTE9 := n_data.ATTRIBUTE9;
l_vendor_site_rec.ATTRIBUTE10 := n_data.ATTRIBUTE10;
l_vendor_site_rec.ATTRIBUTE11 := n_data.ATTRIBUTE11;
l_vendor_site_rec.ATTRIBUTE12 := n_data.ATTRIBUTE12;
l_vendor_site_rec.ATTRIBUTE13 := n_data.ATTRIBUTE13;
l_vendor_site_rec.ATTRIBUTE14 := n_data.ATTRIBUTE14;
l_vendor_site_rec.ATTRIBUTE15 := n_data.ATTRIBUTE15;
l_vendor_site_rec.VALIDATION_NUMBER := n_data.VALIDATION_NUMBER;
l_vendor_site_rec.ORG_ID := n_data.ORG_ID;
-- l_vendor_site_rec.org_name := n_data.org_name;
l_vendor_site_rec.LANGUAGE := n_data.LANGUAGE;
-- l_vendor_site_rec.LOCATION_ID := n_data.new_LOCATION_ID;
l_vendor_site_rec.PURCHASING_SITE_FLAG := n_data.PURCHASING_SITE_FLAG;
l_vendor_site_rec.PAY_SITE_FLAG := n_data.PAY_SITE_FLAG;
l_vendor_site_rec.RFQ_ONLY_SITE_FLAG := n_data.RFQ_ONLY_SITE_FLAG;
l_vendor_site_rec.ALLOW_AWT_FLAG := n_data.ALLOW_AWT_FLAG;
l_vendor_site_rec.primary_pay_site_flag := n_data.primary_pay_site_flag;
l_vendor_site_rec.SHIP_TO_LOCATION_ID := L_SHIP_TO_LOCATION_ID;
l_vendor_site_rec.BILL_TO_LOCATION_ID := L_BILL_TO_LOCATION_ID;
l_vendor_site_rec.ext_payee_rec.default_pmt_method := 'EFT';
-- -------------
-- Optional
-- --------------
AP_VENDOR_PUB_PKG.Create_Vendor_Site (
p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
p_commit => p_commit,
p_validation_level => p_validation_level,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_vendor_site_rec => l_vendor_site_rec,
x_vendor_site_id => l_vendor_site_id,
x_party_site_id => l_party_site_id,
x_location_id => l_hz_location_id);
IF l_return_status IN ('S') THEN
UPDATE XX.XX_AP_SUPPLIER_SITES_ALL
SET new_vendor_site_id = l_vendor_site_id,new_party_site_id = l_party_site_id , new_location_id=l_hz_location_id, status = 'UPDATE'
WHERE vendor_site_id = n_data.vendor_site_id;
COMMIT;
-- fnd_msg_pub.initialize;
DBMS_OUTPUT.put_line ('l_vendor_site_id = ' || l_vendor_site_id);
DBMS_OUTPUT.put_line ('l_party_site_id = ' || l_party_site_id);
DBMS_OUTPUT.put_line ('l_hz_location_id = ' || l_hz_location_id);
DBMS_OUTPUT.put_line ('l_return_status = ' || l_return_status);
DBMS_OUTPUT.put_line ('l_msg_count = ' || l_msg_count);
DBMS_OUTPUT.put_line ('l_msg_data = ' || l_msg_data);
END IF;
IF (l_return_status != 'S')
THEN
DBMS_OUTPUT.put_line (' Error in Creating Employee Type Supplier..');
IF (l_msg_count = 1)
THEN
DBMS_OUTPUT.put_line ('x_msg_data ' || l_msg_data);
ELSIF (l_msg_count > 1)
THEN
LOOP
p_count := p_count + 1;
l_msg_data :=
fnd_msg_pub.get (fnd_msg_pub.g_next, fnd_api.g_false);
IF (l_msg_data IS NULL)
THEN
EXIT;
END IF;
DBMS_OUTPUT.put_line (
' Error Message' || p_count || ' —' || l_msg_data);
END LOOP;
END IF;
ELSE
DBMS_OUTPUT.put_line ('Vendor_site_id:' || l_vendor_site_id);
END IF;
END IF;
END LOOP;
EXCEPTION
WHEN OTHERS
THEN
ROLLBACK;
v_api_error := SQLERRM;
v_error_msg := v_error_msg || ' ' || v_api_error;
DBMS_OUTPUT.PUT_LINE (SQLERRM);
END;
/
No comments:
Post a Comment