xx_ap_supp_api_pkg
CREATE OR REPLACE PACKAGE apps.xx_ap_supp_api_pkg
IS
PROCEDURE xx_ap_supp_api_main (
v_error_buf OUT VARCHAR2,
v_ret_code OUT NUMBER
);
PROCEDURE xx_ap_supp_api_prc;
PROCEDURE xx_ap_supp_site_api_prc;
procedure xx_ap_supp_contacts_api_prc;
v_process_flag VARCHAR2 (40) := 'N';
v_error_message VARCHAR2 (1000);
p_api_version NUMBER := 1.0;
p_init_msg_list VARCHAR2 (200) := 'T';
p_commit VARCHAR2 (200) := 'T';
p_validation_level NUMBER := fnd_api.g_valid_level_full;
x_return_status VARCHAR2 (200) := NULL;
x_msg_count NUMBER := NULL;
x_msg_data VARCHAR2 (200) := NULL;
END xx_ap_supp_api_pkg;
/
CREATE OR REPLACE PACKAGE BODY apps.xx_ap_supp_api_pkg
IS
PROCEDURE xx_ap_supp_api_main (
v_error_buf OUT VARCHAR2,
v_ret_code OUT NUMBER
)
IS
BEGIN
xx_ap_supp_api_prc;
xx_ap_supp_site_api_prc;
xx_ap_supp_contacts_api_prc;
END;
PROCEDURE xx_ap_supp_api_prc
IS
CURSOR xx_supp_cur
IS
SELECT xas.ROWID rid, xas.*
FROM xx_ap_suppliers_stg xas
WHERE process_flag = 'N';
v_supp_count NUMBER;
v_organization_id hr_operating_units.organization_id%TYPE;
v_vendor_type_lookup_code fnd_lookup_values.lookup_code%TYPE;
v_term_id ap_terms_tl.term_id%TYPE;
v_vendor_number ap_suppliers.segment1%TYPE;
v_freight_terms_lookup_code fnd_lookup_values.lookup_code%TYPE;
v_invoice_currency_code fnd_currencies.currency_code%TYPE;
v_payment_currency_code fnd_currencies.currency_code%TYPE;
v_pay_group_lookup_code fnd_lookup_values.lookup_code%TYPE;
v_payment_method_lookup_code fnd_lookup_values.lookup_code%TYPE;
x_vendor_id NUMBER;
x_party_id NUMBER;
p_vendor_rec ap_vendor_pub_pkg.r_vendor_rec_type;
v_msg_index_out NUMBER;
BEGIN
FOR xx_supp IN xx_supp_cur
LOOP
fnd_file.put_line (fnd_file.LOG, 'ENTER INTO xx_supp LOOP');
BEGIN
SELECT COUNT (*)
INTO v_supp_count
FROM ap_suppliers
WHERE TRIM (UPPER (vendor_name)) =
TRIM (UPPER (xx_supp.vendor_name));
IF v_supp_count > 0
THEN
v_process_flag := 'N';
v_error_message :=
v_error_message || 'Vendor is already existing';
END IF;
EXCEPTION
WHEN OTHERS
THEN
NULL;
END;
---------------VALIDATION STARTS----------------------------
/* ----------- OPERATING UNIT VALIDATION STARTS---------------------
BEGIN
SELECT organization_id
INTO v_organization_id
FROM hr_operating_units
WHERE NAME = xx_supp.operating_unit;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
NULL;
fnd_file.put_line (fnd_file.LOG,
'NO DATA FOUND FOR OPERATING UNIT'
);
v_process_flag := 'E';
v_error_message :=
v_error_message
|| '-'
|| 'NO DATA FOUND FOR OPERATING UNIT';
WHEN TOO_MANY_ROWS
THEN
fnd_file.put_line (fnd_file.LOG,
xx_supp.operating_unit
|| '-'
|| 'THERE ARE MORE THAN ONE OPERATING UNIT'
);
v_process_flag := 'E';
v_error_message :=
v_error_message
|| '-'
|| 'THERE ARE MORE THAN ONE OPERATING UNIT';
WHEN OTHERS
THEN
--fnd_file.put_line (SQLCODE ||'-'||SQLERRM);
v_process_flag := 'E';
v_error_message :=
v_error_message || '-' || 'IN OTHERS FOR OPERATING UNIT';
END;
----------- OPERATING UNIT VALIDATION ENDS---------------------*/
----------VENDOR TYPE LOOKUP CODE VALIDATION----------------
BEGIN
fnd_file.put_line (fnd_file.LOG,
'PROCESS_FLAG IS:' || v_process_flag
);
fnd_file.put_line (fnd_file.LOG,
'ENTER INTO VENDOR TYPE LOOKUP CODE VALDATION'
);
SELECT lookup_code
INTO v_vendor_type_lookup_code
FROM fnd_lookup_values
WHERE lookup_type = 'VENDOR TYPE'
AND meaning = xx_supp.vendor_type_lookup_code;
fnd_file.put_line (fnd_file.LOG,
'VENDOR TYPE LOOKUP CODE IS:'
|| v_vendor_type_lookup_code
);
fnd_file.put_line (fnd_file.LOG,
'PROCESS_FLAG IS:' || v_process_flag
);
EXCEPTION
WHEN NO_DATA_FOUND
THEN
fnd_file.put_line
(fnd_file.LOG,
'NO DATA FOUND FOR VENDOR TYPE LOOKUP CODE '
);
v_process_flag := 'E';
v_error_message := v_error_message || 'NO DATA FOUND';
WHEN TOO_MANY_ROWS
THEN
fnd_file.put_line
(fnd_file.LOG,
'TOO MANY VALUES FOR VENDOR TYPE LOOKUP CODE '
);
v_process_flag := 'E';
v_error_message :=
v_error_message
|| 'TOO MANY VALUES FOR VENDOR TYPE LOOKUP CODE ';
WHEN OTHERS
THEN
fnd_file.put_line (fnd_file.LOG, 'OTHER THAN THE EXCEPTION');
v_process_flag := 'E';
v_error_message :=
v_error_message || 'OTHER THAN THE EXCEPTION';
END;
--------- END VENDOR TYPE LOOKUP CODE VALIDATION-------------
--------------------- TERMS NAME VALIDATION --------------------------
BEGIN
fnd_file.put_line (fnd_file.LOG,
'PROCESS_FLAG IS:' || v_process_flag
);
fnd_file.put_line (fnd_file.LOG,
'ENTER INTO TERMS NAME VALDATION'
);
SELECT term_id
INTO v_term_id
FROM ap_terms_tl
WHERE NAME = xx_supp.terms_name;
fnd_file.put_line (fnd_file.LOG, 'TERMS ID IS:' || v_term_id);
fnd_file.put_line (fnd_file.LOG,
'PROCESS_FLAG IS:' || v_process_flag
);
EXCEPTION
WHEN NO_DATA_FOUND
THEN
fnd_file.put_line (fnd_file.LOG,
'NO DATA FOUND FOR TERMS NAME '
);
v_process_flag := 'E';
v_error_message := v_error_message || 'NO DATA FOUND';
WHEN TOO_MANY_ROWS
THEN
fnd_file.put_line (fnd_file.LOG,
'TOO MANY VALUES FOR TERMS NAME '
);
v_process_flag := 'E';
v_error_message :=
v_error_message || 'TOO MANY VALUES FOR TERMS NAME ';
WHEN OTHERS
THEN
fnd_file.put_line (fnd_file.LOG,
'OTHER EXCEPTION FOR TERMS NAME'
);
v_process_flag := 'E';
v_error_message :=
v_error_message || 'OTHER EXCEPTION FOR TERMS NAME ';
END;
---------END TERMS NAME VALIDATION-------------
---------SEGEMENT1 (VENDOR NUMBER )VALIDATION----------------
BEGIN
fnd_file.put_line (fnd_file.LOG,
'PROCESS_FLAG IS:' || v_process_flag
);
fnd_file.put_line (fnd_file.LOG,
'ENTER INTO VENDOR NUMBER VALDATION'
);
SELECT next_auto_supplier_num
INTO v_vendor_number
FROM ap_product_setup;
fnd_file.put_line (fnd_file.LOG,
'VENDOR NUMBER IS:' || v_vendor_number
);
fnd_file.put_line (fnd_file.LOG,
'PROCESS_FLAG IS:' || v_process_flag
);
EXCEPTION
WHEN NO_DATA_FOUND
THEN
fnd_file.put_line (fnd_file.LOG,
'NO DATA FOUND FOR VENDOR NUMBER '
);
v_process_flag := 'E';
v_error_message := v_error_message || 'NO DATA FOUND';
WHEN TOO_MANY_ROWS
THEN
fnd_file.put_line (fnd_file.LOG,
'TOO MANY VALUES FOR VENDOR NUMBER '
);
v_process_flag := 'E';
v_error_message :=
v_error_message || 'TOO MANY VALUES FOR VENDOR NUMBER ';
WHEN OTHERS
THEN
fnd_file.put_line (fnd_file.LOG,
'OTHER EXCEPTION FOR VENDOR NUMBER '
);
v_process_flag := 'E';
v_error_message :=
v_error_message || 'OTHER EXCEPTION FOR VENDOR NUMBER ';
END;
---------END SEGEMENT1 (VENDOR NUMBER )VALIDATION-------------
/* ---------FREIGHT TERMS VALIDATION----------------
BEGIN
fnd_file.put_line (fnd_file.LOG,
'PROCESS_FLAG IS:' || v_process_flag
);
fnd_file.put_line (fnd_file.LOG,
'ENTER INTO FREIGHT TERMS VALDATION'
);
SELECT lookup_code
INTO v_freight_terms_lookup_code
FROM fnd_lookup_values
WHERE lookup_type = 'FREIGHT TERMS'
AND meaning = xx_supp.freight_terms_lookup_code;
fnd_file.put_line (fnd_file.LOG,
'FREIGHT TERMS IS:'
|| v_freight_terms_lookup_code
);
fnd_file.put_line (fnd_file.LOG,
'PROCESS_FLAG IS:' || v_process_flag
);
EXCEPTION
WHEN NO_DATA_FOUND
THEN
fnd_file.put_line (fnd_file.LOG,
'NO DATA FOUND FOR FREIGHT TERMS '
);
v_process_flag := 'E';
v_error_message := v_error_message || 'NO DATA FOUND';
WHEN TOO_MANY_ROWS
THEN
fnd_file.put_line (fnd_file.LOG,
'TOO MANY VALUES FOR FREIGHT TERMS '
);
v_process_flag := 'E';
v_error_message :=
v_error_message || 'TOO MANY VALUES FOR FREIGHT TERMS ';
WHEN OTHERS
THEN
fnd_file.put_line (fnd_file.LOG,
'OTHER EXCEPTION FOR FREIGHT TERMS '
);
v_process_flag := 'E';
v_error_message :=
v_error_message || 'OTHER EXCEPTION FOR FREIGHT TERMS ';
END;
---------END FREIGHT TERMS VALIDATION------------- */
---------INVOICE CURRENCY CODE VALIDATION----------------
BEGIN
fnd_file.put_line (fnd_file.LOG,
'PROCESS_FLAG IS:' || v_process_flag
);
fnd_file.put_line
(fnd_file.LOG,
'ENTER INTO INVOICE CURRENCY CODE VALDATION'
);
SELECT currency_code
INTO v_invoice_currency_code
FROM fnd_currencies
WHERE currency_code = xx_supp.invoice_currency_code;
fnd_file.put_line (fnd_file.LOG,
'INVOICE CURRENCY CODE IS:'
|| v_invoice_currency_code
);
fnd_file.put_line (fnd_file.LOG,
'PROCESS_FLAG IS:' || v_process_flag
);
EXCEPTION
WHEN NO_DATA_FOUND
THEN
fnd_file.put_line
(fnd_file.LOG,
'NO DATA FOUND FOR INVOICE CURRENCY CODE '
);
v_process_flag := 'E';
v_error_message := v_error_message || 'NO DATA FOUND';
WHEN TOO_MANY_ROWS
THEN
fnd_file.put_line
(fnd_file.LOG,
'TOO MANY VALUES FOR INVOICE CURRENCY CODE '
);
v_process_flag := 'E';
v_error_message :=
v_error_message
|| 'TOO MANY VALUES FOR INVOICE CURRENCY CODE ';
WHEN OTHERS
THEN
fnd_file.put_line
(fnd_file.LOG,
'OTHER EXCEPTION FOR INVOICE CURRENCY CODE '
);
v_process_flag := 'E';
v_error_message :=
v_error_message
|| 'OTHER EXCEPTION FOR INVOICE CURRENCY CODE ';
END;
---------END INVOICE CURRENCY CODE VALIDATION-------------
---------PAYMENT CURRENCY CODE VALIDATION----------------
BEGIN
fnd_file.put_line (fnd_file.LOG,
'PROCESS_FLAG IS:' || v_process_flag
);
fnd_file.put_line
(fnd_file.LOG,
'ENTER INTO PAYMENT CURRENCY CODE VALDATION'
);
SELECT currency_code
INTO v_payment_currency_code
FROM fnd_currencies
WHERE currency_code = xx_supp.payment_currency_code;
fnd_file.put_line (fnd_file.LOG,
'PAYMENT CURRENCY CODE IS:'
|| v_payment_currency_code
);
fnd_file.put_line (fnd_file.LOG,
'PROCESS_FLAG IS:' || v_process_flag
);
EXCEPTION
WHEN NO_DATA_FOUND
THEN
fnd_file.put_line
(fnd_file.LOG,
'NO DATA FOUND FOR PAYMENT CURRENCY CODE '
);
v_process_flag := 'E';
v_error_message := v_error_message || 'NO DATA FOUND';
WHEN TOO_MANY_ROWS
THEN
fnd_file.put_line
(fnd_file.LOG,
'TOO MANY VALUES FOR PAYMENT CURRENCY CODE '
);
v_process_flag := 'E';
v_error_message :=
v_error_message
|| 'TOO MANY VALUES FOR PAYMENT CURRENCY CODE ';
WHEN OTHERS
THEN
fnd_file.put_line
(fnd_file.LOG,
'OTHER EXCEPTION FOR PAYMENT CURRENCY CODE '
);
v_process_flag := 'E';
v_error_message :=
v_error_message
|| 'OTHER EXCEPTION FOR PAYMENT CURRENCY CODE ';
END;
---------END PAYMENT CURRENCY CODE VALIDATION-------------
---------PAY GROUP LOOKUP CODE VALIDATION----------------
BEGIN
fnd_file.put_line (fnd_file.LOG,
'PROCESS_FLAG IS:' || v_process_flag
);
fnd_file.put_line (fnd_file.LOG,
'ENTER INTO PAY GROUP LOOKUP CODE VALDATION'
);
SELECT lookup_code
INTO v_pay_group_lookup_code
FROM fnd_lookup_values
WHERE lookup_type = 'PAY GROUP'
AND meaning = xx_supp.pay_group_lookup_code;
fnd_file.put_line (fnd_file.LOG,
'PAY GROUP LOOKUP CODE IS:'
|| v_pay_group_lookup_code
);
fnd_file.put_line (fnd_file.LOG,
'PROCESS_FLAG IS:' || v_process_flag
);
EXCEPTION
WHEN NO_DATA_FOUND
THEN
fnd_file.put_line
(fnd_file.LOG,
'NO DATA FOUND FOR PAY GROUP LOOKUP CODE '
);
v_process_flag := 'E';
v_error_message := v_error_message || 'NO DATA FOUND';
WHEN TOO_MANY_ROWS
THEN
fnd_file.put_line
(fnd_file.LOG,
'TOO MANY VALUES FOR PAY GROUP LOOKUP CODE '
);
v_process_flag := 'E';
v_error_message :=
v_error_message
|| 'TOO MANY VALUES FOR PAY GROUP LOOKUP CODE ';
WHEN OTHERS
THEN
fnd_file.put_line
(fnd_file.LOG,
'OTHER EXCEPTION FOR PAY GROUP LOOKUP CODE '
);
v_process_flag := 'E';
v_error_message :=
v_error_message
|| 'OTHER EXCEPTION FOR PAY GROUP LOOKUP CODE ';
END;
---------END PAY GROUP LOOKUP CODE VALIDATION-------------
/* ---------PAYMENT METHOD LOOKUP CODE VALIDATION----------------
BEGIN
fnd_file.put_line (fnd_file.LOG,
'PROCESS_FLAG IS:' || v_process_flag
);
fnd_file.put_line
(fnd_file.LOG,
'ENTER INTO PAYMENT METHOD LOOKUP CODE VALDATION'
);
SELECT lookup_code
INTO v_payment_method_lookup_code
FROM fnd_lookup_values
WHERE lookup_type = 'PAYMENT METHOD'
AND meaning = xx_supp.payment_method_lookup_code;
fnd_file.put_line (fnd_file.LOG,
'PAY GROUP LOOKUP CODE IS:'
|| v_payment_method_lookup_code
);
fnd_file.put_line (fnd_file.LOG,
'PROCESS_FLAG IS:' || v_process_flag
);
EXCEPTION
WHEN NO_DATA_FOUND
THEN
fnd_file.put_line
(fnd_file.LOG,
'NO DATA FOUND FOR PAYMENT METHOD LOOKUP CODE '
);
v_process_flag := 'E';
v_error_message := v_error_message || 'NO DATA FOUND';
WHEN TOO_MANY_ROWS
THEN
fnd_file.put_line
(fnd_file.LOG,
'TOO MANY VALUES FOR PAYMENT METHOD LOOKUP CODE '
);
v_process_flag := 'E';
v_error_message :=
v_error_message
|| 'TOO MANY VALUES FOR PAYMENT METHOD LOOKUP CODE ';
WHEN OTHERS
THEN
fnd_file.put_line
(fnd_file.LOG,
'OTHER EXCEPTION FOR PAYMENT METHOD LOOKUP CODE '
);
v_process_flag := 'E';
v_error_message :=
v_error_message
|| 'OTHER EXCEPTION FOR PAYMENT METHOD LOOKUP CODE ';
END;
---------END PAYMENT METHOD LOOKUP CODE VALIDATION------------- */
IF v_process_flag = 'N'
THEN
p_vendor_rec.vendor_interface_id := ap_suppliers_int_s.NEXTVAL;
p_vendor_rec.vendor_type_lookup_code := v_vendor_type_lookup_code;
p_vendor_rec.vendor_name := xx_supp.vendor_name;
p_vendor_rec.vendor_name_alt := xx_supp.vendor_name_alt;
p_vendor_rec.segment1 := v_vendor_number;
p_vendor_rec.terms_id := v_term_id;
p_vendor_rec.pay_group_lookup_code := v_pay_group_lookup_code;
p_vendor_rec.invoice_currency_code := v_invoice_currency_code;
p_vendor_rec.payment_currency_code := v_payment_currency_code;
p_vendor_rec.summary_flag := xx_supp.summary_flag;
p_vendor_rec.enabled_flag := xx_supp.enabled_flag;
p_vendor_rec.women_owned_flag := 'N';
p_vendor_rec.small_business_flag := 'Y';
ap_vendor_pub_pkg.create_vendor (p_api_version,
p_init_msg_list,
p_commit,
p_validation_level,
x_return_status,
x_msg_count,
x_msg_data,
p_vendor_rec,
x_vendor_id,
x_party_id
);
fnd_file.put_line (fnd_file.LOG,
'RETURN STATUS :' || x_return_status
);
fnd_file.put_line (fnd_file.LOG,
'MESSAGE COUNT :' || TO_CHAR (x_msg_count)
);
fnd_file.put_line (fnd_file.LOG, 'MESSAGE :' || x_msg_data);
fnd_file.put_line (fnd_file.LOG,
'VENDOR ID :' || TO_CHAR (x_vendor_id)
);
fnd_file.put_line (fnd_file.LOG,
'PARTY ID :' || TO_CHAR (x_party_id)
);
COMMIT;
IF x_return_status = 'S'
THEN
UPDATE xx_ap_supplier_stg xas
SET xas.process_flag = 'I'
,xas.ERROR_MESSAGE= 'Record Inserted'
,xas.VENOR_ID = x_vendor_id
WHERE xas.vendor_name = xx_supp.vendor_name;
UPDATE xx_ap_supplier_site_stg xass
SET xass.vendor_id = x_vendor_id
WHERE xass.vendor_name = p_vendor_rec.vendor_name;
COMMIT;
ELSE
ROLLBACK;
END IF;
IF x_msg_count > 0
THEN
FOR v_index IN 1 .. x_msg_count
LOOP
fnd_msg_pub.get (p_msg_index => v_index,
p_encoded => 'F',
p_data => x_msg_data,
p_msg_index_out => v_msg_index_out
);
x_msg_data := SUBSTR (x_msg_data, 1, 200);
fnd_file.put_line (fnd_file.LOG,
'x_msg_data :' || x_msg_data
);
END LOOP;
END IF;
ELSE
UPDATE xx_ap_supplier_stg xass
SET xass.process_flag = 'E'
WHERE xass.vendor_name = xx_supp.vendor_name;
END IF;
END LOOP;
END;
PROCEDURE xx_ap_supp_site_api_prc
IS
CURSOR xx_supp_site_cur
IS
SELECT xass.ROWID rid, xass.*
FROM xx_ap_supplier_site_stg xass
WHERE process_flag = 'N';
v_supp_count NUMBER;
v_organization_id hr_operating_units.organization_id%TYPE;
v_operating_unit hr_operating_units.NAME%TYPE;
v_country fnd_territories.territory_code%TYPE;
x_vendor_site_id NUMBER;
x_party_site_id NUMBER;
x_location_id NUMBER;
p_vendor_site_rec ap_vendor_pub_pkg.r_vendor_site_rec_type;
v_msg_index_out NUMBER;
BEGIN
FOR xx_supp_site IN xx_supp_site_cur
LOOP
fnd_file.put_line (fnd_file.LOG, 'ENTER INTO xx_supp_site LOOP');
BEGIN
SELECT COUNT (*)
INTO v_supp_count
FROM ap_supplier_sites_all
WHERE TRIM (UPPER (vendor_site_code)) =
TRIM (UPPER (xx_supp_site.vendor_site_code));
IF v_supp_count > 0
THEN
v_process_flag := 'N';
v_error_message :=
v_error_message || 'Vendor site is already existing';
END IF;
EXCEPTION
WHEN OTHERS
THEN
NULL;
END;
---------------VALIDATION STARTS----------------------------
---------- OPERATING UNIT VALIDATION STARTS---------------------
BEGIN
SELECT organization_id, NAME
INTO v_organization_id, v_operating_unit
FROM hr_operating_units
WHERE NAME = xx_supp_site.operating_unit;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
NULL;
fnd_file.put_line (fnd_file.LOG,
'NO DATA FOUND FOR OPERATING UNIT'
);
v_process_flag := 'E';
v_error_message :=
v_error_message || '-' || 'NO DATA FOUND FOR OPERATING UNIT';
WHEN TOO_MANY_ROWS
THEN
fnd_file.put_line (fnd_file.LOG,
xx_supp_site.operating_unit
|| '-'
|| 'THERE ARE MORE THAN ONE OPERATING UNIT'
);
v_process_flag := 'E';
v_error_message :=
v_error_message
|| '-'
|| 'THERE ARE MORE THAN ONE OPERATING UNIT';
WHEN OTHERS
THEN
--fnd_file.put_line (SQLCODE ||'-'||SQLERRM);
v_process_flag := 'E';
v_error_message :=
v_error_message || '-' || 'IN OTHERS FOR OPERATING UNIT';
END;
----------- OPERATING UNIT VALIDATION ENDS---------------------
----------- country VALIDATION STARTS---------------------
BEGIN
SELECT territory_code
INTO v_country
FROM fnd_territories
WHERE nls_territory = xx_supp_site.country;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
NULL;
fnd_file.put_line (fnd_file.LOG,
'NO DATA FOUND FOR OPERATING UNIT'
);
v_process_flag := 'E';
v_error_message :=
v_error_message || '-' || 'NO DATA FOUND FOR OPERATING UNIT';
WHEN TOO_MANY_ROWS
THEN
fnd_file.put_line (fnd_file.LOG,
xx_supp_site.country
|| '-'
|| 'THERE ARE MORE THAN ONE OPERATING UNIT'
);
v_process_flag := 'E';
v_error_message :=
v_error_message
|| '-'
|| 'THERE ARE MORE THAN ONE OPERATING UNIT';
WHEN OTHERS
THEN
--fnd_file.put_line (SQLCODE ||'-'||SQLERRM);
v_process_flag := 'E';
v_error_message :=
v_error_message || '-' || 'IN OTHERS FOR OPERATING UNIT';
END;
----------- V_COUNTRY VALIDATION ENDS---------------------
IF v_process_flag = 'N'
THEN
p_vendor_site_rec.vendor_interface_id :=
ap_suppliers_int_s.CURRVAL;
p_vendor_site_rec.vendor_site_interface_id :=
ap_supplier_sites_int_s.NEXTVAL;
p_vendor_site_rec.vendor_id := xx_supp_site.vendor_id;
p_vendor_site_rec.vendor_site_code :=
xx_supp_site.vendor_site_code;
p_vendor_site_rec.address_line1 := xx_supp_site.address_line1;
p_vendor_site_rec.address_line2 := xx_supp_site.address_line2;
p_vendor_site_rec.address_line3 := xx_supp_site.address_line3;
p_vendor_site_rec.city := xx_supp_site.city;
p_vendor_site_rec.state := xx_supp_site.state;
--p_vendor_site_rec.county := 'HYD';
p_vendor_site_rec.country := v_country;
p_vendor_site_rec.org_name := v_operating_unit;
p_vendor_site_rec.org_id := v_organization_id;
x_vendor_site_id := NULL;
x_party_site_id := NULL;
x_location_id := NULL;
ap_vendor_pub_pkg.create_vendor_site (p_api_version,
p_init_msg_list,
p_commit,
p_validation_level,
x_return_status,
x_msg_count,
x_msg_data,
p_vendor_site_rec,
x_vendor_site_id,
x_party_site_id,
x_location_id
);
fnd_file.put_line (fnd_file.LOG,
'RETURN STATUS :' || x_return_status
);
fnd_file.put_line (fnd_file.LOG,
'MESSAGE COUNT :' || TO_CHAR (x_msg_count)
);
fnd_file.put_line (fnd_file.LOG, 'MESSAGE :' || x_msg_data);
fnd_file.put_line (fnd_file.LOG,
'VENDOR SITE ID :'
|| TO_CHAR (x_vendor_site_id)
);
fnd_file.put_line (fnd_file.LOG,
'PARTY SITE ID :' || TO_CHAR (x_party_site_id)
);
fnd_file.put_line (fnd_file.LOG,
'LOCATION ID :' || TO_CHAR (x_location_id)
);
COMMIT;
IF x_return_status = 'S'
THEN
UPDATE xx_ap_supplier_site_stg xass
SET xass.process_flag = 'I',
xass.error_message = 'Record Inserted'
WHERE xass.vendor_site_code = xx_supp_site.vendor_site_code;
UPDATE xx_ap_sup_site_contact_stg xassc
SET xassc.vendor_id = xx_supp_site.vendor_id,
xassc.party_site_id = x_party_site_id,
xassc.org_party_site_id = x_party_site_id
WHERE xassc.vendor_site_code = xx_supp_site.vendor_site_code;
COMMIT;
ELSE
ROLLBACK;
END IF;
IF x_msg_count > 0
THEN
FOR v_index IN 1 .. x_msg_count
LOOP
fnd_msg_pub.get (p_msg_index => v_index,
p_encoded => 'F',
p_data => x_msg_data,
p_msg_index_out => v_msg_index_out
);
x_msg_data := SUBSTR (x_msg_data, 1, 200);
fnd_file.put_line (fnd_file.LOG,
'x_msg_data :' || x_msg_data
);
END LOOP;
END IF;
ELSE
UPDATE xx_ap_supplier_site_stg xass
SET xass.process_flag = 'E',
xass.error_message = 'Error Record'
WHERE xass.vendor_site_code = xx_supp_site.vendor_site_code;
END IF;
END LOOP;
END;
PROCEDURE xx_ap_supp_contacts_api_prc
IS
CURSOR xx_supp_cont_cur
IS
SELECT xasc.ROWID rid, xasc.*
FROM xx_ap_sup_site_contact_stg xasc
WHERE xasc.process_flag = 'N';
p_vendor_contact_rec ap_vendor_pub_pkg.r_vendor_contact_rec_type;
v_organization_id hr_operating_units.organization_id%TYPE;
x_vendor_contact_id NUMBER;
x_per_party_id NUMBER;
x_rel_party_id NUMBER;
x_rel_id NUMBER;
x_org_contact_id NUMBER;
x_party_site_id NUMBER;
v_msg_index_out NUMBER;
BEGIN
FOR xx_supp_cont IN xx_supp_cont_cur
LOOP
fnd_file.put_line (fnd_file.LOG, 'ENTER INTO xx_supp_cont LOOP');
---------------VALIDATION STARTS----------------------------
---------- OPERATING UNIT VALIDATION STARTS---------------------
BEGIN
SELECT organization_id
INTO v_organization_id
FROM hr_operating_units
WHERE NAME = xx_supp_cont.operating_unit;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
NULL;
fnd_file.put_line (fnd_file.LOG,
'NO DATA FOUND FOR OPERATING UNIT'
);
v_process_flag := 'E';
v_error_message :=
v_error_message || '-' || 'NO DATA FOUND FOR OPERATING UNIT';
WHEN TOO_MANY_ROWS
THEN
fnd_file.put_line (fnd_file.LOG,
xx_supp_cont.operating_unit
|| '-'
|| 'THERE ARE MORE THAN ONE OPERATING UNIT'
);
v_process_flag := 'E';
v_error_message :=
v_error_message
|| '-'
|| 'THERE ARE MORE THAN ONE OPERATING UNIT';
WHEN OTHERS
THEN
--fnd_file.put_line (SQLCODE ||'-'||SQLERRM);
v_process_flag := 'E';
v_error_message :=
v_error_message || '-' || 'IN OTHERS FOR OPERATING UNIT';
END;
----------- OPERATING UNIT VALIDATION ENDS---------------------
IF v_process_flag = 'N'
THEN
p_vendor_contact_rec.vendor_contact_interface_id :=
ap_sup_site_contact_int_s.NEXTVAL;
p_vendor_contact_rec.vendor_interface_id :=
ap_suppliers_int_s.CURRVAL;
p_vendor_contact_rec.vendor_site_code :=
xx_supp_cont.vendor_site_code;
p_vendor_contact_rec.person_first_name := xx_supp_cont.first_name;
p_vendor_contact_rec.person_middle_name :=
xx_supp_cont.middle_name;
p_vendor_contact_rec.person_last_name := xx_supp_cont.last_name;
p_vendor_contact_rec.phone := xx_supp_cont.phone;
p_vendor_contact_rec.email_address := xx_supp_cont.email_address;
p_vendor_contact_rec.org_id := v_organization_id;
p_vendor_contact_rec.party_site_id := xx_supp_cont.party_site_id;
p_vendor_contact_rec.org_party_site_id :=
xx_supp_cont.org_party_site_id;
p_vendor_contact_rec.vendor_id := xx_supp_cont.vendor_id;
p_vendor_contact_rec.vendor_site_id :=
xx_supp_cont.vendor_site_id;
--p_vendor_contact_rec.org_contact_id := xx_supp_cont.org_contact_id;
x_vendor_contact_id := NULL;
x_per_party_id := NULL;
x_rel_party_id := NULL;
x_rel_id := NULL;
x_org_contact_id := NULL;
x_party_site_id := NULL;
fnd_msg_pub.initialize;
ap_vendor_pub_pkg.create_vendor_contact (p_api_version,
p_init_msg_list,
p_commit,
p_validation_level,
x_return_status,
x_msg_count,
x_msg_data,
p_vendor_contact_rec,
x_vendor_contact_id,
x_per_party_id,
x_rel_party_id,
x_rel_id,
x_org_contact_id,
x_party_site_id
);
fnd_file.put_line (fnd_file.LOG,
'AP_SUP_SITE_CONTACT_INT_S.NEXTVAL :'
|| ap_sup_site_contact_int_s.NEXTVAL
);
fnd_file.put_line (fnd_file.LOG,
'RETURN STATUS :' || x_return_status
);
fnd_file.put_line (fnd_file.LOG,
'MESSAGE COUNT :' || TO_CHAR (x_msg_count)
);
fnd_file.put_line (fnd_file.LOG, 'MESSAGE :' || x_msg_data);
fnd_file.put_line (fnd_file.LOG,
'VENDOR contact ID :' || x_vendor_contact_id
);
fnd_file.put_line (fnd_file.LOG,
'per_party_id :' || x_per_party_id
);
fnd_file.put_line (fnd_file.LOG,
'rel_party_id :' || x_rel_party_id
);
fnd_file.put_line (fnd_file.LOG, 'rel_id :' || x_rel_id);
fnd_file.put_line (fnd_file.LOG,
'org_contact_id :' || x_org_contact_id
);
fnd_file.put_line (fnd_file.LOG,
'party_site_id :' || x_party_site_id
);
COMMIT;
IF x_return_status = 'S'
THEN
UPDATE xx_ap_sup_site_contact_stg xassc
SET xassc.process_flag = 'I',
xassc.error_message = 'Record Insert',
xassc.vendor_contact_id = x_vendor_contact_id
WHERE xassc.vendor_site_code = xx_supp_cont.vendor_site_code;
COMMIT;
ELSE
ROLLBACK;
END IF;
IF x_msg_count > 0
THEN
FOR v_index IN 1 .. x_msg_count
LOOP
fnd_msg_pub.get (p_msg_index => v_index,
p_encoded => 'F',
p_data => x_msg_data,
p_msg_index_out => v_msg_index_out
);
x_msg_data := SUBSTR (x_msg_data, 1, 200);
fnd_file.put_line (fnd_file.LOG,
'x_msg_data :' || x_msg_data
);
END LOOP;
END IF;
ELSE
UPDATE xx_ap_sup_site_contact_stg xassc
SET xassc.process_flag = 'E',
xassc.error_message = 'Error Record'
WHERE xassc.vendor_site_code = xx_supp_cont.vendor_site_code;
END IF;
END LOOP;
END;
END xx_ap_supp_api_pkg;
/
Supplier API Single Insert
DECLARE
p_api_version NUMBER;
p_init_msg_list VARCHAR2 (200);
p_commit VARCHAR2 (200);
p_validation_level NUMBER;
x_return_status VARCHAR2 (200);
x_msg_count NUMBER;
x_msg_data VARCHAR2 (200);
x_vendor_id NUMBER;
x_party_id NUMBER;
p_vendor_rec ap_vendor_pub_pkg.r_vendor_rec_type;
v_msg_index_out NUMBER;
BEGIN
p_api_version := 1.0;
p_init_msg_list := 'T';
p_commit := 'T';
p_validation_level := fnd_api.g_valid_level_full;
x_return_status := NULL;
x_msg_count := NULL;
x_msg_data := NULL;
p_vendor_rec.vendor_type_lookup_code := 'Internal';
p_vendor_rec.vendor_name := 'Andhra Jyothi';
p_vendor_rec.women_owned_flag := 'N';
p_vendor_rec.small_business_flag := 'Y';
-- p_vendor_rec.segment1 := '001';--insert non duplicate number
SELECT next_auto_supplier_num
INTO p_vendor_rec.segment1
FROM ap_product_setup;
ap_vendor_pub_pkg.create_vendor (p_api_version,
p_init_msg_list,
p_commit,
p_validation_level,
x_return_status,
x_msg_count,
x_msg_data,
p_vendor_rec,
x_vendor_id,
x_party_id
);
DBMS_OUTPUT.put_line ('RETURN STATUS :' || x_return_status);
DBMS_OUTPUT.put_line ('MESSAGE COUNT :' || TO_CHAR (x_msg_count));
DBMS_OUTPUT.put_line ('MESSAGE :' || x_msg_data);
DBMS_OUTPUT.put_line ('VENDOR ID :' || TO_CHAR (x_vendor_id));
DBMS_OUTPUT.put_line ('PARTY ID :' || TO_CHAR (x_party_id));
COMMIT;
IF x_return_status = 'S'
THEN
COMMIT;
ELSE
ROLLBACK;
END IF;
IF x_msg_count > 0
THEN
FOR v_index IN 1 .. x_msg_count
LOOP
fnd_msg_pub.get (p_msg_index => v_index,
p_encoded => 'F',
p_data => x_msg_data,
p_msg_index_out => v_msg_index_out
);
x_msg_data := SUBSTR (x_msg_data, 1, 200);
DBMS_OUTPUT.put_line ('x_msg_data :' || x_msg_data);
END LOOP;
END IF;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line ('SQL ERROR :' || SQLERRM);
END;
/
/*************************************************************************
RETURN STATUS :S
MESSAGE COUNT :0
MESSAGE :
VENDOR ID :49170
PARTY ID :416680
PL/SQL procedure successfully completed.
**************************************************************************/
DECLARE
p_api_version NUMBER;
p_init_msg_list VARCHAR2 (200);
p_commit VARCHAR2 (200);
p_validation_level NUMBER;
x_return_status VARCHAR2 (200);
x_msg_count NUMBER;
x_msg_data VARCHAR2 (200);
x_vendor_site_id NUMBER;
x_party_site_id NUMBER;
x_location_id NUMBER;
p_vendor_site_rec ap_vendor_pub_pkg.r_vendor_site_rec_type;
v_msg_index_out NUMBER;
BEGIN
p_api_version := 1.0;
p_init_msg_list := 'T';
p_commit := 'T';
p_validation_level := fnd_api.g_valid_level_full;
x_return_status := NULL;
x_msg_count := NULL;
x_msg_data := NULL;
p_vendor_site_rec.vendor_id := 49170;
p_vendor_site_rec.vendor_site_code := 'ABN SITE';
p_vendor_site_rec.area_code := '040';
p_vendor_site_rec.phone := '23066699';
p_vendor_site_rec.fax_area_code := '040';
p_vendor_site_rec.fax := '23066688';
p_vendor_site_rec.address_line1 := 'VV NAGAR';
p_vendor_site_rec.address_line2 := 'KKP';
p_vendor_site_rec.address_line3 := 'KUKATPALLY';
p_vendor_site_rec.city := 'HYDERABAD';
p_vendor_site_rec.state := 'AP';
p_vendor_site_rec.county := 'HYD';
p_vendor_site_rec.country := 'IN';
p_vendor_site_rec.org_name := 'Vision Operations';
p_vendor_site_rec.org_id := 204;
x_vendor_site_id := NULL;
x_party_site_id := NULL;
x_location_id := NULL;
ap_vendor_pub_pkg.create_vendor_site (p_api_version,
p_init_msg_list,
p_commit,
p_validation_level,
x_return_status,
x_msg_count,
x_msg_data,
p_vendor_site_rec,
x_vendor_site_id,
x_party_site_id,
x_location_id
);
DBMS_OUTPUT.put_line ('RETURN STATUS :' || x_return_status);
DBMS_OUTPUT.put_line ('MESSAGE COUNT :' || TO_CHAR (x_msg_count));
DBMS_OUTPUT.put_line ('MESSAGE :' || x_msg_data);
DBMS_OUTPUT.put_line ('VENDOR SITE ID :' || TO_CHAR (x_vendor_site_id));
DBMS_OUTPUT.put_line ('PARTY SITE ID :' || TO_CHAR (x_party_site_id));
DBMS_OUTPUT.put_line ('LOCATION ID :' || TO_CHAR (x_location_id));
COMMIT;
IF x_return_status = 'S'
THEN
COMMIT;
ELSE
ROLLBACK;
END IF;
IF x_msg_count > 0
THEN
FOR v_index IN 1 .. x_msg_count
LOOP
fnd_msg_pub.get (p_msg_index => v_index,
p_encoded => 'F',
p_data => x_msg_data,
p_msg_index_out => v_msg_index_out
);
x_msg_data := SUBSTR (x_msg_data, 1, 200);
DBMS_OUTPUT.put_line ('x_msg_data :' || x_msg_data);
END LOOP;
END IF;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line ('SQL ERROR :' || SQLERRM);
END;
/
/*************************************************************************
RETURN STATUS :S
MESSAGE COUNT :0
MESSAGE :
VENDOR SITE ID :7171
PARTY SITE ID :234632
LOCATION ID :26635
PL/SQL procedure successfully completed.
**************************************************************************/
DECLARE
p_api_version NUMBER;
p_init_msg_list VARCHAR2 (200);
p_commit VARCHAR2 (200);
p_validation_level NUMBER;
x_return_status VARCHAR2 (200);
x_msg_count NUMBER;
x_msg_data VARCHAR2 (200);
p_vendor_contact_rec ap_vendor_pub_pkg.r_vendor_contact_rec_type;
x_vendor_contact_id NUMBER;
x_per_party_id NUMBER;
x_rel_party_id NUMBER;
x_rel_id NUMBER;
x_org_contact_id NUMBER;
x_party_site_id NUMBER;
v_msg_index_out NUMBER;
BEGIN
p_api_version := 1.0;
p_init_msg_list := 'T';
p_commit := 'T';
p_validation_level := fnd_api.g_valid_level_full;
x_return_status := NULL;
x_msg_count := NULL;
x_msg_data := NULL;
p_vendor_contact_rec.vendor_contact_id :=
ap_sup_site_contact_int_s.NEXTVAL;
p_vendor_contact_rec.person_first_name := 'RADHA';
p_vendor_contact_rec.person_middle_name := 'KRISHNA';
p_vendor_contact_rec.person_last_name := 'VEMURI';
p_vendor_contact_rec.phone := '9948012318';
p_vendor_contact_rec.email_address := 'radhakrishna@abn.com';
p_vendor_contact_rec.url := 'http://abn.com';
p_vendor_contact_rec.org_id := 204;
p_vendor_contact_rec.party_site_id := 234632;
p_vendor_contact_rec.org_party_site_id := 234632;
p_vendor_contact_rec.vendor_id := 49170;
x_vendor_contact_id := NULL;
x_per_party_id := NULL;
x_rel_party_id := NULL;
x_rel_id := NULL;
x_org_contact_id := NULL;
x_party_site_id := NULL;
fnd_msg_pub.initialize;
ap_vendor_pub_pkg.create_vendor_contact (p_api_version,
p_init_msg_list,
p_commit,
p_validation_level,
x_return_status,
x_msg_count,
x_msg_data,
p_vendor_contact_rec,
x_vendor_contact_id,
x_per_party_id,
x_rel_party_id,
x_rel_id,
x_org_contact_id,
x_party_site_id
);
DBMS_OUTPUT.put_line ( 'AP_SUP_SITE_CONTACT_INT_S.NEXTVAL :'
|| ap_sup_site_contact_int_s.NEXTVAL
);
DBMS_OUTPUT.put_line ('RETURN STATUS :' || x_return_status);
DBMS_OUTPUT.put_line ('MESSAGE COUNT :' || TO_CHAR (x_msg_count));
DBMS_OUTPUT.put_line ('MESSAGE :' || x_msg_data);
DBMS_OUTPUT.put_line ('VENDOR contact ID :' || x_vendor_contact_id);
DBMS_OUTPUT.put_line ('per_party_id :' || x_per_party_id);
DBMS_OUTPUT.put_line ('rel_party_id :' || x_rel_party_id);
DBMS_OUTPUT.put_line ('rel_id :' || x_rel_id);
DBMS_OUTPUT.put_line ('org_contact_id :' || x_org_contact_id);
DBMS_OUTPUT.put_line ('party_site_id :' || x_party_site_id);
COMMIT;
IF x_return_status = 'S'
THEN
COMMIT;
ELSE
ROLLBACK;
END IF;
IF x_msg_count > 0
THEN
FOR v_index IN 1 .. x_msg_count
LOOP
fnd_msg_pub.get (p_msg_index => v_index,
p_encoded => 'F',
p_data => x_msg_data,
p_msg_index_out => v_msg_index_out
);
x_msg_data := SUBSTR (x_msg_data, 1, 200);
DBMS_OUTPUT.put_line ('x_msg_data :' || x_msg_data);
END LOOP;
END IF;
END;
/
/*************************************************************************
AP_SUP_SITE_CONTACT_INT_S.NEXTVAL :16002
RETURN STATUS :S
MESSAGE COUNT :0
MESSAGE :
VENDOR contact ID :25949
per_party_id :416682
rel_party_id :416683
rel_id :381368
org_contact_id :169423
party_site_id :234632
PL/SQL procedure successfully completed.
**************************************************************************/
No comments:
Post a Comment