DROP TABLE SHIVAJI_NAGAR_SUPPLIERS;
/*
CREATE TABLE SHIVAJI_NAGAR_SUPPLIERS
(
--SUPPLIER_ID NUMBER DEFAULT NULL,
SUPPLIER_NAME VARCHAR2 (250),
ALTERNATE_NAME VARCHAR2 (250),
PAN_NUMBER VARCHAR2 (25),
SUPPLIER_TYPE VARCHAR2 (25),
PAYMENT_TERM VARCHAR2 (25),
OPERATING_UNIT VARCHAR2 (30),
VENDOR_SITE VARCHAR2 (30),
COUNTRY VARCHAR2 (25),
ADDRESS_LINE1 VARCHAR2 (30),
CITY VARCHAR2 (25),
STATE VARCHAR2 (25),
CURRENCY VARCHAR2 (25),
PAYMENT_METHOD VARCHAR2 (25)
);
*/
SELECT * FROM SHIVAJI_NAGAR_SUPPLIERS;
CREATE TABLE SHIVAJI_NAGAR_SUPPLIERS
(
--SUPPLIER_ID NUMBER DEFAULT NULL,
SUPPLIER_NAME VARCHAR2 (250),
ALTERNATE_NAME VARCHAR2 (250),
PAN_NUMBER VARCHAR2 (25)
) ;
ALTER TABLE SHIVAJI_NAGAR_SUPPLIERS ADD VENDOR_ID NUMBER DEFAULT NULL;
ALTER TABLE SHIVAJI_NAGAR_SUPPLIERS ADD STATUS VARCHAR2(10) Default 'N';
ALTER TABLE SHIVAJI_NAGAR_SUPPLIERS ADD ERROR_MESSAGE VARCHAR2(250);
ALTER TABLE SHIVAJI_NAGAR_SUPPLIERS ADD VENDOR_SITE_ID NUMBER DEFAULT NULL;
--VENDOR ID UPDATATION
DECLARE
CURSOR C1 IS SELECT * FROM CKBALLAPURA_SUPPLIERS;
BEGIN
FOR rec IN c1
LOOP
UPDATE SHIVAJI_NAGAR_SUPPLIERS
SET VENDOR_ID =REC.VENDOR_ID
where PAN_NUMBER = rec.PAN_NUMBER ;
COMMIT;
END LOOP;
END;
---shivaji nagar specific suppliers-----
DECLARE
l_vendor_rec AP_VENDOR_PUB_PKG.r_vendor_rec_type;
l_vendor_site_rec AP_VENDOR_PUB_PKG.r_vendor_site_rec_type;
l_vendor_contact_rec_type AP_VENDOR_PUB_PKG.r_vendor_contact_rec_type;
l_return_status VARCHAR2 (2000);
l_msg_count NUMBER;
l_msg_data VARCHAR2 (2000);
l_vendor_id NUMBER;
l_party_id NUMBER;
l_vendor_site_id NUMBER;
l_location_id NUMBER;
l_party_site_id NUMBER;
l_vendor_contact_id NUMBER;
l_per_party_id NUMBER;
l_rel_party_id NUMBER;
l_rel_id NUMBER;
l_org_contact_id NUMBER;
--->> Supplier Header Data
-- p_vendor_number VARCHAR2(240) := '1234';
p_vendor_name VARCHAR2 (240);
p_enabled_flag VARCHAR2 (240) := 'Y';
p_vendor_type_code VARCHAR2 (240);
p_invoice_currency VARCHAR2 (240);
p_payment_currency VARCHAR2 (240);
p_term_id NUMBER := 10002;
p_payment_method VARCHAR2 (240);
v_api_error VARCHAR2 (500);
v_error_msg VARCHAR2 (500) := NULL;
API_ERROR EXCEPTION;
CURSOR Cur_Supp IS
SELECT *
FROM SHIVAJI_NAGAR_SUPPLIERS
WHERE VENDOR_ID IS NULL;
BEGIN
FND_GLOBAL.APPS_INITIALIZE (0, 50861, 7000); --Chikkaballapura
FOR i IN Cur_Supp
LOOP
BEGIN
-- l_vendor_rec.segment1 := p_vendor_number;
l_vendor_rec.vendor_name := i.supplier_name;
l_vendor_rec.vendor_name_alt := i.alternate_name;
l_vendor_rec.vat_registration_num := i.PAN_NUMBER;
l_vendor_rec.vendor_type_lookup_code := 'VENDOR';--i.SUPPLIER_TYPE;
l_vendor_rec.start_date_active := SYSDATE;
l_vendor_rec.invoice_currency_code := 'INR';--i.currency;
l_vendor_rec.payment_currency_code := 'INR';--i.currency;
l_vendor_rec.terms_id := 10000;
l_vendor_rec.ext_payee_rec.default_pmt_method := 'Cheque';--i.PAYMENT_METHOD;
AP_VENDOR_PUB_PKG.CREATE_VENDOR (
p_api_version => 1.0,
--p_init_msg_list => 'F',
--p_commit => 'T',
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_vendor_rec => l_vendor_rec,
x_vendor_id => l_vendor_id,
x_party_id => l_party_id);
COMMIT;
BEGIN
UPDATE SHIVAJI_NAGAR_SUPPLIERS
SET status = 'P',
VENDOR_ID = l_vendor_id,
error_message = NULL
WHERE SUPPLIER_NAME = i.supplier_name;
EXCEPTION
WHEN OTHERS
THEN
v_api_error := SQLERRM;
v_error_msg := v_error_msg || ' ' || v_api_error;
UPDATE SHIVAJI_NAGAR_SUPPLIERS
SET status = 'E',
error_message = v_error_msg
WHERE SUPPLIER_NAME = i.supplier_name;
COMMIT;
END;
END;
END LOOP;
EXCEPTION
WHEN API_ERROR
THEN
FOR I IN 1 .. l_msg_count
LOOP
DBMS_OUTPUT.put_line (
SUBSTR (FND_MSG_PUB.Get (p_encoded => FND_API.G_FALSE),
1,
255));
DBMS_OUTPUT.put_line ('Error Msg : ' || l_msg_data);
END LOOP;
WHEN OTHERS
THEN
FOR I IN 1 .. l_msg_count
LOOP
DBMS_OUTPUT.put_line (
SUBSTR (FND_MSG_PUB.Get (p_encoded => FND_API.G_FALSE),
1,
255));
DBMS_OUTPUT.put_line ('Error Msg : ' || l_msg_data);
END LOOP;
END;