CREATE OR REPLACE PACKAGE BODY xxbr_ap_suppliers_pkg
AS
PROCEDURE xxbr_ap_supp_cv_main (p_action IN VARCHAR2)
IS
BEGIN
-- Call validate procedure to validate the Supplier information
IF p_action = 'validate'
THEN
xxbr_ap_supp_cv_validate;
ELSE
NULL;
END IF;
-- Call Loaded procedure to load the data into Supplier interface table
IF p_action = 'load'
THEN
xxbr_ap_supp_cv_load;
ELSE
NULL;
END IF;
END xxbr_ap_supp_cv_main;
-- End Main Procedure XXBR_AP_SUPP_CV_MAIN For Supplier
PROCEDURE xxbr_ap_supp_cv_validate
IS
-- Variable declaration
l_i_accepted NUMBER := 0;
l_i_rejected NUMBER := 0;
l_ic_accepted NUMBER := 0;
l_ic_rejected NUMBER := 0;
l_sqlcode NUMBER;
l_sqlerrm VARCHAR2 (1000);
l_return_status VARCHAR2 (1);
l_i_err_msg VARCHAR2 (7000);
l_count NUMBER := 0;
ln_vendor_id NUMBER;
lv_supplier_type VARCHAR2 (1000);
lv_invoice_currency VARCHAR2 (3);
lv_payment_currency VARCHAR2 (3);
lv_terms_name VARCHAR2 (100);
ln_term_id NUMBER;
ln_set_of_books_id NUMBER;
ln_employee_id NUMBER;
lv_ship_to_location_id NUMBER;
ln_vat_code VARCHAR2 (200);
lv_terms_date_basis VARCHAR2 (50);
lv_var_registration_number NUMBER;
lv_match_option VARCHAR2 (50);
l_final_error_msg VARCHAR2 (100);
lv_error_code VARCHAR2 (100);
-- Variable declaration for Bank Validation
p_bank_country_code hz_parties.country%TYPE;
p_bank_name hz_parties.party_name%TYPE;
p_bank_number hz_organization_profiles.bank_or_branch_number%TYPE;
x_bank_id NUMBER;
x_end_date DATE;
ln_bank_id NUMBER;
-- Variable declaration for Bank Branch Validation
x_branch_id NUMBER;
p_bank_id NUMBER;
p_branch_name hz_parties.party_name%TYPE;
p_branch_number hz_organization_profiles.bank_or_branch_number%TYPE;
-- Variable declaration for Bank Account Validation
p_ext_bank_acct_rec iby_ext_bankacct_pub.extbankacct_rec_type;
l_dup_acct_id NUMBER;
l_dup_start_date DATE;
l_dup_end_date DATE;
x_response iby_fndcpt_common_pub.result_rec_type;
x_return_status VARCHAR2 (1000);
x_msg_count NUMBER;
x_msg_data VARCHAR2 (1000);
l_bank_account_num_len NUMBER;
CURSOR ap_supplier_cur
IS
SELECT *
FROM xxbr_ap_supp_cv_stg
WHERE process_flag = 'N' AND rec_type IN ('D', 'd');
supplier_tab xxbr_ap_supp_cv_stg%ROWTYPE;
BEGIN
FOR pick_data IN ap_supplier_cur
LOOP
l_return_status := 'Y';
l_i_err_msg := NULL;
l_final_error_msg := NULL;
lv_error_code := NULL;
x_branch_id := NULL;
/*+-----------------------------------------------------+
-- ======= Validation for Supplier Name PR05 ===========
+-------------------------------------------------------+ */
BEGIN
SELECT COUNT (vendor_id)
INTO ln_vendor_id
FROM apps.ap_suppliers
WHERE (UPPER (vendor_name) IN TRIM (UPPER (pick_data.vendor_name))
);
IF ln_vendor_id <> 0
THEN
l_return_status := 'E';
l_i_err_msg :=
pick_data.vendor_name || ' Vendor already exists.';
lv_error_code := 'Error-PR05';
END IF;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
ln_vendor_id := NULL;
WHEN OTHERS
THEN
l_return_status := 'E';
l_sqlcode := SQLCODE;
l_sqlerrm := SUBSTR (SQLERRM, 1, 1000);
END;
IF l_return_status = 'Y'
THEN
BEGIN
UPDATE xxbr_ap_supp_cv_stg
SET process_flag = 'V',
record_status = 'Validated'
WHERE pk_supplier = pick_data.pk_supplier;
EXCEPTION
WHEN OTHERS
THEN
l_sqlcode := SQLCODE;
l_sqlerrm := SUBSTR (SQLERRM, 1, 1000);
END;
ELSE
BEGIN
l_final_error_msg := 'Y';
UPDATE xxbr_ap_supp_cv_stg
SET error_message = l_i_err_msg,
process_flag = 'E',
record_status = 'Failure',
ERROR_CODE = lv_error_code
WHERE pk_supplier = pick_data.pk_supplier;
EXCEPTION
WHEN OTHERS
THEN
l_sqlcode := SQLCODE;
l_sqlerrm := SUBSTR (SQLERRM, 1, 1000);
END;
END IF;
/*+-------------------------------------------------------+
-- ========= End of Validation for Supplier Name =========
+--------------------------------------------------------+ */
/*+------------------------------------------------------+
---Start of Validation for Classification Supplier type PR07---
+---------------------------------------------------------+*/
BEGIN
SELECT lookup_code
INTO lv_supplier_type
FROM apps.fnd_lookup_values
WHERE UPPER (lookup_code) = TRIM (UPPER (pick_data.vendor_type))
AND lookup_type = 'VENDOR TYPE'
AND LANGUAGE = USERENV ('LANG');
EXCEPTION
WHEN NO_DATA_FOUND
THEN
l_return_status := 'E';
l_i_err_msg :=
l_i_err_msg
|| ''
|| 'Vendor Type '
|| pick_data.vendor_type
|| ' Not Valid for '
|| pick_data.vendor_type;
lv_error_code := lv_error_code || '' || 'Error-PR07';
WHEN OTHERS
THEN
l_return_status := 'E';
l_sqlcode := SQLCODE;
l_sqlerrm := SUBSTR (SQLERRM, 1, 1000);
END;
IF l_return_status = 'Y'
THEN
BEGIN
UPDATE xxbr_ap_supp_cv_stg
SET process_flag = 'V',
record_status = 'Validated'
WHERE pk_supplier = pick_data.pk_supplier;
EXCEPTION
WHEN OTHERS
THEN
l_sqlcode := SQLCODE;
l_sqlerrm := SUBSTR (SQLERRM, 1, 1000);
END;
ELSE
BEGIN
l_final_error_msg := 'Y';
UPDATE xxbr_ap_supp_cv_stg
SET error_message = l_i_err_msg,
process_flag = 'E',
record_status = 'Failure',
ERROR_CODE = lv_error_code
WHERE pk_supplier = pick_data.pk_supplier;
EXCEPTION
WHEN OTHERS
THEN
l_sqlcode := SQLCODE;
l_sqlerrm := SUBSTR (SQLERRM, 1, 1000);
END;
END IF;
/*+------------------------------------------------------+
-- ========= End of Validation for Supplier Type =========
+---------------------------------------------------------+ */
/*+-------------------------------------------------------+
-----Start of Validation for Invoice Currency PR08--------
+----------------------------------------------------------+*/
BEGIN
IF pick_data.invoice_currency IS NOT NULL
THEN
SELECT currency_code
INTO lv_invoice_currency
FROM apps.fnd_currencies
WHERE currency_code = pick_data.invoice_currency;
END IF;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
l_return_status := 'E';
l_i_err_msg :=
l_i_err_msg
|| ''
|| 'Invoice Currency '
|| pick_data.invoice_currency
|| ' Not Valid for '
|| pick_data.vendor_name;
lv_error_code := lv_error_code || '' || 'Error-PR08';
WHEN OTHERS
THEN
l_return_status := 'E';
l_sqlcode := SQLCODE;
l_sqlerrm := SUBSTR (SQLERRM, 1, 1000);
END;
IF l_return_status = 'Y'
THEN
BEGIN
UPDATE xxbr_ap_supp_cv_stg
SET process_flag = 'V',
record_status = 'Validated'
WHERE pk_supplier = pick_data.pk_supplier;
EXCEPTION
WHEN OTHERS
THEN
l_sqlcode := SQLCODE;
l_sqlerrm := SUBSTR (SQLERRM, 1, 1000);
END;
ELSE
BEGIN
l_final_error_msg := 'Y';
UPDATE xxbr_ap_supp_cv_stg
SET error_message = l_i_err_msg,
process_flag = 'E',
record_status = 'Failure',
ERROR_CODE = lv_error_code
WHERE pk_supplier = pick_data.pk_supplier;
EXCEPTION
WHEN OTHERS
THEN
l_sqlcode := SQLCODE;
l_sqlerrm := SUBSTR (SQLERRM, 1, 1000);
END;
END IF;
/*+---------------------------------------------------------+
-- ====== End of Validation for Invoice Currency ==========
+----------------------------------------------------------+ */
/*+--------------------------------------------------------+
------Start of Validation for Payment Currency PR09---------
+------------------------------------------------------------+*/
BEGIN
IF pick_data.payment_currency IS NOT NULL
THEN
SELECT currency_code
INTO lv_payment_currency
FROM apps.fnd_currencies
WHERE currency_code = pick_data.payment_currency;
END IF;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
l_return_status := 'E';
l_i_err_msg :=
l_i_err_msg
|| ''
|| 'Payment Currency '
|| pick_data.payment_currency
|| ' Not Valid for '
|| pick_data.vendor_name;
lv_error_code := lv_error_code || '' || 'Error-PR09';
WHEN OTHERS
THEN
l_return_status := 'E';
l_sqlcode := SQLCODE;
l_sqlerrm := SUBSTR (SQLERRM, 1, 1000);
END;
IF l_return_status = 'Y'
THEN
BEGIN
UPDATE xxbr_ap_supp_cv_stg
SET process_flag = 'V',
record_status = 'Validated'
WHERE pk_supplier = pick_data.pk_supplier;
EXCEPTION
WHEN OTHERS
THEN
l_sqlcode := SQLCODE;
l_sqlerrm := SUBSTR (SQLERRM, 1, 1000);
END;
ELSE
BEGIN
l_final_error_msg := 'Y';
UPDATE xxbr_ap_supp_cv_stg
SET error_message = l_i_err_msg,
process_flag = 'E',
record_status = 'Failure',
ERROR_CODE = lv_error_code
WHERE pk_supplier = pick_data.pk_supplier;
EXCEPTION
WHEN OTHERS
THEN
l_sqlcode := SQLCODE;
l_sqlerrm := SUBSTR (SQLERRM, 1, 1000);
END;
END IF;
/*+--------------------------------------------------------+
-- ========== End of Validation for Payment Currency ========
+--------------------------------------------------------+ */
/*+--------------------------------------------------------+
---Start of Validation for Set of Books Name Ledger Name PR10---
+-----------------------------------------------------------+*/
BEGIN
IF pick_data.set_of_books_name IS NOT NULL
THEN
SELECT ledger_id
INTO ln_set_of_books_id
FROM gl_ledgers
WHERE NAME = pick_data.set_of_books_name;
END IF;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
l_return_status := 'E';
l_i_err_msg :=
l_i_err_msg
|| ''
|| 'Set of Book '
|| pick_data.set_of_books_name
|| ' Not Valid for '
|| pick_data.vendor_name;
lv_error_code := lv_error_code || '' || 'Error-PR10';
WHEN OTHERS
THEN
l_return_status := 'E';
l_sqlcode := SQLCODE;
l_sqlerrm := SUBSTR (SQLERRM, 1, 1000);
END;
IF l_return_status = 'Y'
THEN
BEGIN
UPDATE xxbr_ap_supp_cv_stg
SET process_flag = 'V',
record_status = 'Validated',
set_of_books_id = ln_set_of_books_id
WHERE pk_supplier = pick_data.pk_supplier;
EXCEPTION
WHEN OTHERS
THEN
l_sqlcode := SQLCODE;
l_sqlerrm := SUBSTR (SQLERRM, 1, 1000);
END;
ELSE
BEGIN
l_final_error_msg := 'Y';
UPDATE xxbr_ap_supp_cv_stg
SET error_message = l_i_err_msg,
process_flag = 'E',
record_status = 'Failure',
ERROR_CODE = lv_error_code
WHERE pk_supplier = pick_data.pk_supplier;
EXCEPTION
WHEN OTHERS
THEN
l_sqlcode := SQLCODE;
l_sqlerrm := SUBSTR (SQLERRM, 1, 1000);
END;
END IF;
/*+-------------------------------------------------------+
-- === End of Validation for Set of Books Name =========
+---------------------------------------------------------+ */
/*+------------------------------------------------------+
-------Start of Validation for Terms Data Basis PR12------
+---------------------------------------------------------+*/
BEGIN
IF pick_data.terms_date_basis IS NOT NULL
THEN
SELECT lookup_code
INTO lv_terms_date_basis
FROM fnd_lookup_values
WHERE lookup_type = 'TERMS DATE BASIS'
AND lookup_code = pick_data.terms_date_basis;
END IF;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
l_return_status := 'E';
l_i_err_msg :=
l_i_err_msg
|| ''
|| 'Terms Data Basis '
|| pick_data.terms_date_basis
|| ' Not Valid for '
|| pick_data.vendor_name;
lv_error_code := lv_error_code || '' || 'Error-PR12';
WHEN OTHERS
THEN
l_return_status := 'E';
l_sqlcode := SQLCODE;
l_sqlerrm := SUBSTR (SQLERRM, 1, 1000);
END;
IF l_return_status = 'Y'
THEN
BEGIN
UPDATE xxbr_ap_supp_cv_stg
SET process_flag = 'V',
record_status = 'Validated'
WHERE pk_supplier = pick_data.pk_supplier;
EXCEPTION
WHEN OTHERS
THEN
l_sqlcode := SQLCODE;
l_sqlerrm := SUBSTR (SQLERRM, 1, 1000);
END;
ELSE
BEGIN
l_final_error_msg := 'Y';
UPDATE xxbr_ap_supp_cv_stg
SET error_message = l_i_err_msg,
process_flag = 'E',
record_status = 'Failure',
ERROR_CODE = lv_error_code
WHERE pk_supplier = pick_data.pk_supplier;
EXCEPTION
WHEN OTHERS
THEN
l_sqlcode := SQLCODE;
l_sqlerrm := SUBSTR (SQLERRM, 1, 1000);
END;
END IF;
/*+-------------------------------------------------------+
-- ====== End of Validation for Terms Data Basis =========
/*+-------------------------------------------------------+
/*+-------------------------------------------------------+
-- ======= Validation for VAT Registration Number PR13====
/*+-------------------------------------------------------+
BEGIN
IF pick_data.vat_registration_number IS NOT NULL THEN
SELECT count(vat_registration_num)
into lv_var_registration_number
FROM ap_suppliers
WHERE UPPER(vat_registration_num) = TRIM(UPPER (pick_data.vat_registration_number));
END IF;
IF lv_var_registration_number <> 0 then
l_return_status := 'E';
l_i_err_msg := 'There is currently another supplier in the system with the same VAT Registration Number.';
lv_error_code:= lv_error_code ||''||'Error-PR13';
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
lv_var_registration_number := NULL;
WHEN OTHERS THEN
l_return_status := 'E';
l_sqlcode :=SQLCODE;
l_sqlerrm :=SUBSTR(SQLERRM,1,1000);
END;
IF l_return_status='Y' THEN
BEGIN
UPDATE XXBR_AP_SUPP_CV_STG
SET process_flag = 'V'
,record_status = 'Validated'
WHERE pk_supplier = pick_data.pk_supplier;
EXCEPTION
WHEN OTHERS THEN
l_sqlcode :=SQLCODE;
l_sqlerrm :=SUBSTR(SQLERRM,1,1000);
END;
ELSE
BEGIN
l_final_error_msg:='Y';
UPDATE XXBR_AP_SUPP_CV_STG
SET error_message = l_i_err_msg
,process_flag = 'E'
,record_status = 'Failure'
,error_code = lv_error_code
WHERE pk_supplier = pick_data.pk_supplier;
EXCEPTION
WHEN OTHERS THEN
l_sqlcode :=SQLCODE;
l_sqlerrm :=SUBSTR(SQLERRM,1,1000);
END;
END IF;
/*+-------------------------------------------------------+
-- === End of Validation for VAT Registration Number ======
/*+-------------------------------------------------------+/
/*+-------------------------------------------------------+
----Start of Validation for Ship to Location Code PR14 ---
/*+-------------------------------------------------------+
BEGIN
IF pick_data.ship_to_location_code IS NOT NULL THEN
SELECT DISTINCT LOCATION_id
INTO lv_ship_to_location_id
FROM HR_LOCATIONS_ALL
WHERE SHIP_TO_SITE_FLAG ='Y'
AND LOCATION_CODE = pick_data.ship_to_location_code;
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
l_return_status := 'E';
l_i_err_msg := l_i_err_msg ||'' || 'Ship to Location Code '|| pick_data.ship_to_location_code ||
' Not Valid for '|| pick_data.vendor_name;
lv_error_code:= lv_error_code ||''||'Error-PR14';
WHEN OTHERS THEN
l_return_status := 'E';
l_sqlcode :=SQLCODE;
l_sqlerrm :=SUBSTR(SQLERRM,1,1000);
END;
IF l_return_status='Y' THEN
BEGIN
UPDATE XXBR_AP_SUPP_CV_STG
SET process_flag = 'V'
,record_status = 'Validated'
,ship_to_location_id = lv_ship_to_location_id
WHERE pk_supplier = pick_data.pk_supplier;
EXCEPTION
WHEN OTHERS THEN
l_sqlcode :=SQLCODE;
l_sqlerrm :=SUBSTR(SQLERRM,1,1000);
END;
ELSE
BEGIN
l_final_error_msg:='Y';
UPDATE XXBR_AP_SUPP_CV_STG
SET error_message = l_i_err_msg
,process_flag = 'E'
,record_status = 'Failure'
,error_code = lv_error_code
WHERE pk_supplier = pick_data.pk_supplier;
EXCEPTION
WHEN OTHERS THEN
l_sqlcode :=SQLCODE;
l_sqlerrm :=SUBSTR(SQLERRM,1,1000);
END;
END IF;
/*+--------------------------------------------------------+
--==== End of Validation for Ship to Location Code ==========
+---------------------------------------------------------+ */
/*+--------------------------------------------------------+
------- Start of Validation for Match Option PR15-----------
+----------------------------------------------------------+*/
BEGIN
IF pick_data.match_option IS NOT NULL
THEN
SELECT lookup_code
INTO lv_match_option
FROM fnd_lookups
WHERE lookup_type = 'POS_INVOICE_MATCH_OPTION'
AND UPPER (lookup_code) = UPPER (pick_data.match_option);
END IF;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
l_return_status := 'E';
l_i_err_msg :=
l_i_err_msg
|| ''
|| 'Match Option '
|| pick_data.match_option
|| ' Not Valid for '
|| pick_data.vendor_name;
lv_error_code := lv_error_code || '' || 'Error-PR15';
WHEN OTHERS
THEN
l_return_status := 'E';
l_sqlcode := SQLCODE;
l_sqlerrm := SUBSTR (SQLERRM, 1, 1000);
END;
IF l_return_status = 'Y'
THEN
BEGIN
UPDATE xxbr_ap_supp_cv_stg
SET process_flag = 'V',
record_status = 'Validated',
ship_to_location_id = lv_ship_to_location_id
WHERE pk_supplier = pick_data.pk_supplier;
EXCEPTION
WHEN OTHERS
THEN
l_sqlcode := SQLCODE;
l_sqlerrm := SUBSTR (SQLERRM, 1, 1000);
END;
ELSE
BEGIN
l_final_error_msg := 'Y';
UPDATE xxbr_ap_supp_cv_stg
SET error_message = l_i_err_msg,
process_flag = 'E',
record_status = 'Failure',
ERROR_CODE = lv_error_code
WHERE pk_supplier = pick_data.pk_supplier;
EXCEPTION
WHEN OTHERS
THEN
l_sqlcode := SQLCODE;
l_sqlerrm := SUBSTR (SQLERRM, 1, 1000);
END;
END IF;
/*+-------------------------------------------------+
-- ========= End of Validation for Match Option ===========
+-------------------------------------------------------+ */
/*+------------------------------------------------------+
---Start of Validation for Employee Name if Vendor_type_lookup_code = 'EMPLOYEE' PR16------
+----------------------------------------------------------+*/
BEGIN
IF pick_data.vendor_type = 'EMPLOYEE'
AND pick_data.employee_number IS NOT NULL
THEN
SELECT person_id
INTO ln_employee_id
FROM per_all_people_f
WHERE employee_number = pick_data.employee_number;
END IF;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
l_return_status := 'E';
l_i_err_msg :=
l_i_err_msg
|| ''
|| 'Employee Number '
|| pick_data.employee_number
|| ' Not Valid for '
|| pick_data.vendor_name
|| '. Please enter valid employee number';
lv_error_code := lv_error_code || '' || 'Error-PR16';
WHEN OTHERS
THEN
l_return_status := 'E';
l_sqlcode := SQLCODE;
l_sqlerrm := SUBSTR (SQLERRM, 1, 1000);
END;
IF l_return_status = 'Y'
THEN
BEGIN
UPDATE xxbr_ap_supp_cv_stg
SET process_flag = 'V',
record_status = 'Validated',
employee_id = ln_employee_id
WHERE pk_supplier = pick_data.pk_supplier;
EXCEPTION
WHEN OTHERS
THEN
l_sqlcode := SQLCODE;
l_sqlerrm := SUBSTR (SQLERRM, 1, 1000);
END;
ELSE
BEGIN
l_final_error_msg := 'Y';
UPDATE xxbr_ap_supp_cv_stg
SET error_message = l_i_err_msg,
process_flag = 'E',
record_status = 'Failure',
ERROR_CODE = lv_error_code
WHERE pk_supplier = pick_data.pk_supplier;
EXCEPTION
WHEN OTHERS
THEN
l_sqlcode := SQLCODE;
l_sqlerrm := SUBSTR (SQLERRM, 1, 1000);
END;
END IF;
/*+--------------------------------------------------------+
-- ====End of Validation for Employee Name if Vendor_type_lookup_code = 'EMPLOYEE' ==
+--------------------------------------------------------+ */
/*+-----------------------------------------------------+
----Start of Validation if Vendor_type = 'EMPLOYEE' then employee_number can not be null PR29----
+--------------------------------------------------------+*/
BEGIN
IF pick_data.vendor_type = 'EMPLOYEE'
AND pick_data.employee_number IS NULL
THEN
l_return_status := 'E';
l_i_err_msg :=
l_i_err_msg
|| ''
|| 'For vendor type = EMPLOYEE, employee_number can not be null. Please enter valid employee number';
END IF;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
l_return_status := 'E';
l_i_err_msg :=
l_i_err_msg
|| ''
|| 'For vendor type = EMPLOYEE, employee_number can not be null. Please enter valid employee number';
lv_error_code := lv_error_code || '' || 'Error-PR29';
WHEN OTHERS
THEN
l_return_status := 'E';
l_sqlcode := SQLCODE;
l_sqlerrm := SUBSTR (SQLERRM, 1, 1000);
END;
IF l_return_status = 'Y'
THEN
BEGIN
UPDATE xxbr_ap_supp_cv_stg
SET process_flag = 'V',
record_status = 'Validated',
employee_id = ln_employee_id
WHERE pk_supplier = pick_data.pk_supplier;
EXCEPTION
WHEN OTHERS
THEN
l_sqlcode := SQLCODE;
l_sqlerrm := SUBSTR (SQLERRM, 1, 1000);
END;
ELSE
BEGIN
l_final_error_msg := 'Y';
UPDATE xxbr_ap_supp_cv_stg
SET error_message = l_i_err_msg,
process_flag = 'E',
record_status = 'Failure',
ERROR_CODE = lv_error_code
WHERE pk_supplier = pick_data.pk_supplier;
EXCEPTION
WHEN OTHERS
THEN
l_sqlcode := SQLCODE;
l_sqlerrm := SUBSTR (SQLERRM, 1, 1000);
END;
END IF;
/*+------------------------------------------------------+
--===End of Validation if Vendor_type = 'EMPLOYEE' then employee_number can not be null ===
+----------------------------------------------------------+ */
/*+-------------------------------------------------------+
-----Start of Validation if Vendor_type = 'VENDOR' then employee_number should be null PR30----
+---------------------------------------------------------+*/
BEGIN
IF pick_data.vendor_type = 'VENDOR'
AND pick_data.employee_number IS NOT NULL
THEN
l_return_status := 'E';
l_i_err_msg :=
l_i_err_msg
|| ''
|| 'For vendor type = VENDOR, employee_number should be null. Please do not enter employee number';
END IF;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
l_return_status := 'E';
l_i_err_msg :=
l_i_err_msg
|| ''
|| 'For vendor type = VENDOR, employee_number should be null. Please do not enter employee number';
lv_error_code := lv_error_code || '' || 'Error-PR30';
WHEN OTHERS
THEN
l_return_status := 'E';
l_sqlcode := SQLCODE;
l_sqlerrm := SUBSTR (SQLERRM, 1, 1000);
END;
IF l_return_status = 'Y'
THEN
BEGIN
UPDATE xxbr_ap_supp_cv_stg
SET process_flag = 'V',
record_status = 'Validated',
employee_id = ln_employee_id
WHERE pk_supplier = pick_data.pk_supplier;
EXCEPTION
WHEN OTHERS
THEN
l_sqlcode := SQLCODE;
l_sqlerrm := SUBSTR (SQLERRM, 1, 1000);
END;
ELSE
BEGIN
l_final_error_msg := 'Y';
UPDATE xxbr_ap_supp_cv_stg
SET error_message = l_i_err_msg,
process_flag = 'E',
record_status = 'Failure',
ERROR_CODE = lv_error_code
WHERE pk_supplier = pick_data.pk_supplier;
EXCEPTION
WHEN OTHERS
THEN
l_sqlcode := SQLCODE;
l_sqlerrm := SUBSTR (SQLERRM, 1, 1000);
END;
END IF;
/*+-----------------------------------------------------+
-- ====End of Validation if Vendor_type = 'VENDOR' then employee_number should be null ======
+--------------------------------------------------------+ */
/*+------------------------------------------------------+
------Start of Validation for Hold Future Payments Flag PR11--
+----------------------------------------------------------+*/
BEGIN
IF pick_data.hold_future_payments_flag IS NOT NULL
THEN
IF pick_data.hold_future_payments_flag NOT IN ('Y', 'N')
THEN
l_return_status := 'E';
l_i_err_msg :=
l_i_err_msg
|| ''
|| 'Hold Future Payments Flag '
|| pick_data.hold_future_payments_flag
|| ' Not Valid for '
|| pick_data.vendor_name
|| '. Please enter Y or N.';
lv_error_code := lv_error_code || '' || 'Error-PR11';
END IF;
END IF;
IF l_return_status = 'Y'
THEN
BEGIN
UPDATE xxbr_ap_supp_cv_stg
SET process_flag = 'V',
record_status = 'Validated'
WHERE pk_supplier = pick_data.pk_supplier;
EXCEPTION
WHEN OTHERS
THEN
l_sqlcode := SQLCODE;
l_sqlerrm := SUBSTR (SQLERRM, 1, 1000);
END;
ELSE
BEGIN
l_final_error_msg := 'Y';
UPDATE xxbr_ap_supp_cv_stg
SET error_message = l_i_err_msg,
process_flag = 'E',
record_status = 'Failure',
ERROR_CODE = lv_error_code
WHERE pk_supplier = pick_data.pk_supplier;
EXCEPTION
WHEN OTHERS
THEN
l_sqlcode := SQLCODE;
l_sqlerrm := SUBSTR (SQLERRM, 1, 1000);
END;
END IF;
END;
/*+---------------------------------------------------------+
-- === End of Validation for Hold Future Payments Flag =====
+---------------------------------------------------------+ */
/*+--------------------------------------------------------+
---Start of Validation for Auto Calculate Interest Flag PR17----
+----------------------------------------------------------+*/
BEGIN
IF pick_data.auto_calculate_interest_flag IS NOT NULL
THEN
IF pick_data.auto_calculate_interest_flag NOT IN ('Y', 'N')
THEN
l_return_status := 'E';
l_i_err_msg :=
l_i_err_msg
|| ''
|| 'Auto Calculate Interest Flag '
|| pick_data.auto_calculate_interest_flag
|| ' Not Valid for '
|| pick_data.vendor_name
|| '. Please enter Y or N.';
lv_error_code := lv_error_code || '' || 'Error-PR17';
END IF;
END IF;
IF l_return_status = 'Y'
THEN
BEGIN
UPDATE xxbr_ap_supp_cv_stg
SET process_flag = 'V',
record_status = 'Validated'
WHERE pk_supplier = pick_data.pk_supplier;
EXCEPTION
WHEN OTHERS
THEN
l_sqlcode := SQLCODE;
l_sqlerrm := SUBSTR (SQLERRM, 1, 1000);
END;
ELSE
BEGIN
l_final_error_msg := 'Y';
UPDATE xxbr_ap_supp_cv_stg
SET error_message = l_i_err_msg,
process_flag = 'E',
record_status = 'Failure',
ERROR_CODE = lv_error_code
WHERE pk_supplier = pick_data.pk_supplier;
EXCEPTION
WHEN OTHERS
THEN
l_sqlcode := SQLCODE;
l_sqlerrm := SUBSTR (SQLERRM, 1, 1000);
END;
END IF;
END;
/*+--------------------------------------------------------+
-- === End of Validation for Auto Calculate Interest Flag=====
+-----------------------------------------------------------+ */
/*+-----------------------------------------------------+
--------Start of Validation for One Time Flag PR18---------
+---------------------------------------------------------+*/
BEGIN
IF pick_data.one_time_flag IS NOT NULL
THEN
IF pick_data.one_time_flag NOT IN ('Y', 'N')
THEN
l_return_status := 'E';
l_i_err_msg :=
l_i_err_msg
|| ''
|| 'One Time Flag '
|| pick_data.one_time_flag
|| ' Not Valid for '
|| pick_data.vendor_name
|| '. Please enter Y or N.';
lv_error_code := lv_error_code || '' || 'Error-PR18';
END IF;
END IF;
IF l_return_status = 'Y'
THEN
BEGIN
UPDATE xxbr_ap_supp_cv_stg
SET process_flag = 'V',
record_status = 'Validated'
WHERE pk_supplier = pick_data.pk_supplier;
EXCEPTION
WHEN OTHERS
THEN
l_sqlcode := SQLCODE;
l_sqlerrm := SUBSTR (SQLERRM, 1, 1000);
END;
ELSE
BEGIN
l_final_error_msg := 'Y';
UPDATE xxbr_ap_supp_cv_stg
SET error_message = l_i_err_msg,
process_flag = 'E',
record_status = 'Failure',
ERROR_CODE = lv_error_code
WHERE pk_supplier = pick_data.pk_supplier;
EXCEPTION
WHEN OTHERS
THEN
l_sqlcode := SQLCODE;
l_sqlerrm := SUBSTR (SQLERRM, 1, 1000);
END;
END IF;
END;
/*+--------------------------------------------------------------------------------------+
-- ================== End of Validation for One Time Flag ==============
+--------------------------------------------------------------------------------------+ */
/*+--------------------------------------------------------------------------------------+
------------------- Start of Validation for Bank exists or not PR23------------------------
+--------------------------------------------------------------------------------------+*/
BEGIN
IF pick_data.bank_name IS NOT NULL
--AND pick_data.bank_number IS NOT NULL
THEN
p_bank_country_code := 'GB';
-- This hardcoding needs to be removed and this field needs to be populated as per Supplier country
p_bank_name := pick_data.bank_name;
p_bank_number := pick_data.bank_number;
ce_bank_pub.check_bank_exist (p_bank_country_code,
p_bank_name,
p_bank_number,
x_bank_id,
x_end_date
);
COMMIT;
END IF;
IF x_bank_id IS NULL
THEN
l_return_status := 'E';
l_i_err_msg :=
l_i_err_msg
|| ''
|| 'The Bank '
|| pick_data.bank_name
|| ' does not exist in the system. Please create the bank first before creating ext bank account for vendor '
|| pick_data.vendor_name;
lv_error_code := lv_error_code || '' || 'Error-PR23';
END IF;
EXCEPTION
WHEN OTHERS
THEN
l_return_status := 'E';
l_sqlcode := SQLCODE;
l_sqlerrm := SUBSTR (SQLERRM, 1, 1000);
END;
IF l_return_status = 'Y'
THEN
BEGIN
UPDATE xxbr_ap_supp_cv_stg
SET process_flag = 'V',
record_status = 'Validated',
bank_id = x_bank_id
WHERE pk_supplier = pick_data.pk_supplier;
EXCEPTION
WHEN OTHERS
THEN
l_sqlcode := SQLCODE;
l_sqlerrm := SUBSTR (SQLERRM, 1, 1000);
END;
ELSE
BEGIN
l_final_error_msg := 'Y';
UPDATE xxbr_ap_supp_cv_stg
SET error_message = l_i_err_msg,
process_flag = 'E',
record_status = 'Failure',
ERROR_CODE = lv_error_code
WHERE pk_supplier = pick_data.pk_supplier;
EXCEPTION
WHEN OTHERS
THEN
l_sqlcode := SQLCODE;
l_sqlerrm := SUBSTR (SQLERRM, 1, 1000);
END;
END IF;
COMMIT;
/*+--------------------------------------------------------------------------------------+
-- ====== End of Validation for Bank exists or not ============
+--------------------------------------------------------------------------------------+ */
/*+--------------------------------------------------------------------------------------+
------- Start of Validation for Bank Branch exists or not PR22-----------
+--------------------------------------------------------------------------------------+*/
BEGIN
IF pick_data.bank_branch_name IS NOT NULL
THEN
p_bank_country_code := 'GB';
-- This hardcoding needs to be removed and this field needs to be populated as per Supplier country
p_branch_name := pick_data.bank_branch_name;
p_branch_number := pick_data.bank_number;
BEGIN
SELECT party_id
INTO ln_bank_id
FROM hz_parties
WHERE party_name = pick_data.bank_name;
EXCEPTION
WHEN OTHERS
THEN
NULL;
END;
--p_bank_id := 7169;
p_bank_id := ln_bank_id;
ce_bank_pub.check_branch_exist (p_bank_id,
p_branch_name,
p_branch_number,
x_branch_id,
x_end_date
);
COMMIT;
END IF;
IF x_branch_id IS NULL
THEN
l_return_status := 'E';
l_i_err_msg :=
l_i_err_msg
|| ''
|| 'The branch '
|| pick_data.bank_branch_name
|| ' does not exist for bank '
|| pick_data.bank_name
|| ' in the system. Please create the bank branch first before creating ext bank account for vendor '
|| pick_data.vendor_name;
lv_error_code := lv_error_code || '' || 'Error-PR22';
END IF;
EXCEPTION
WHEN OTHERS
THEN
l_return_status := 'E';
l_sqlcode := SQLCODE;
l_sqlerrm := SUBSTR (SQLERRM, 1, 1000);
END;
IF l_return_status = 'Y'
THEN
BEGIN
UPDATE xxbr_ap_supp_cv_stg
SET process_flag = 'V',
record_status = 'Validated',
branch_id = x_branch_id
WHERE pk_supplier = pick_data.pk_supplier;
EXCEPTION
WHEN OTHERS
THEN
l_sqlcode := SQLCODE;
l_sqlerrm := SUBSTR (SQLERRM, 1, 1000);
END;
ELSE
BEGIN
l_final_error_msg := 'Y';
UPDATE xxbr_ap_supp_cv_stg
SET error_message = l_i_err_msg,
process_flag = 'E',
record_status = 'Failure',
ERROR_CODE = lv_error_code
WHERE pk_supplier = pick_data.pk_supplier;
EXCEPTION
WHEN OTHERS
THEN
l_sqlcode := SQLCODE;
l_sqlerrm := SUBSTR (SQLERRM, 1, 1000);
END;
END IF;
COMMIT;
/*+--------------------------------------------------------------------------------------+
-- ==== End of Validation for Bank Branch exists or not ========
+--------------------------------------------------------------------------------------+ */
/*+--------------------------------------------------------------------------------------+
----- Start of Validation for Bank Account Number Length PR31-----------
+--------------------------------------------------------------------------------------+*/
BEGIN
IF pick_data.bank_account_number IS NOT NULL
THEN
SELECT LENGTH (pick_data.bank_account_number)
INTO l_bank_account_num_len
FROM DUAL;
COMMIT;
END IF;
IF l_bank_account_num_len > 8
THEN
l_return_status := 'E';
l_i_err_msg :=
l_i_err_msg
|| ''
|| 'Please enter a maximum of 8 characters for the Account Number for Account '
|| pick_data.bank_account_number
|| 'for bank '
|| pick_data.bank_name
|| ' for vendor '
|| pick_data.vendor_name;
lv_error_code := lv_error_code || '' || 'Error-PR31';
END IF;
EXCEPTION
WHEN OTHERS
THEN
l_return_status := 'E';
l_sqlcode := SQLCODE;
l_sqlerrm := SUBSTR (SQLERRM, 1, 1000);
END;
IF l_return_status = 'Y'
THEN
BEGIN
UPDATE xxbr_ap_supp_cv_stg
SET process_flag = 'V',
record_status = 'Validated',
bank_account_id = l_dup_acct_id
WHERE pk_supplier = pick_data.pk_supplier;
EXCEPTION
WHEN OTHERS
THEN
l_sqlcode := SQLCODE;
l_sqlerrm := SUBSTR (SQLERRM, 1, 1000);
END;
ELSE
BEGIN
l_final_error_msg := 'Y';
UPDATE xxbr_ap_supp_cv_stg
SET error_message = l_i_err_msg,
process_flag = 'E',
record_status = 'Failure',
ERROR_CODE = lv_error_code
WHERE pk_supplier = pick_data.pk_supplier;
EXCEPTION
WHEN OTHERS
THEN
l_sqlcode := SQLCODE;
l_sqlerrm := SUBSTR (SQLERRM, 1, 1000);
END;
END IF;
COMMIT;
/*+----------------------------------------------------+
--==== End of Validation for Bank Account Number Length ====
+--------------------------------------------------------+ */
/*+-----------------------------------------------------+
--- Start of Validation for Bank Account should not exist PR27---
+-----------------------------------------------------------+*/
BEGIN
IF pick_data.bank_account_name IS NOT NULL
AND pick_data.bank_account_number IS NOT NULL
THEN
p_ext_bank_acct_rec.bank_id := ln_bank_id;
p_ext_bank_acct_rec.branch_id := x_branch_id;
p_ext_bank_acct_rec.bank_account_name :=
pick_data.bank_account_name;
p_ext_bank_acct_rec.bank_account_num :=
pick_data.bank_account_number;
--p_ext_bank_acct_rec.acct_owner_party_id := 7162;
p_ext_bank_acct_rec.currency := 'GBP';
p_ext_bank_acct_rec.country_code := 'GB';
iby_ext_bankacct_pub.check_ext_acct_exist
(p_api_version => '1.0',
p_init_msg_list => 'T',
p_ext_bank_acct_rec => p_ext_bank_acct_rec,
x_acct_id => l_dup_acct_id,
x_start_date => l_dup_start_date,
x_end_date => l_dup_end_date,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
x_response => x_response
);
COMMIT;
END IF;
IF l_dup_acct_id IS NOT NULL
THEN
l_return_status := 'E';
l_i_err_msg :=
l_i_err_msg
|| ''
|| 'The Account '
|| pick_data.bank_account_number
|| ' exist for bank '
|| pick_data.bank_name
|| ' in the system for vendor '
|| pick_data.vendor_name;
lv_error_code := lv_error_code || '' || 'Error-PR27';
END IF;
EXCEPTION
WHEN OTHERS
THEN
l_return_status := 'E';
l_sqlcode := SQLCODE;
l_sqlerrm := SUBSTR (SQLERRM, 1, 1000);
END;
IF l_return_status = 'Y'
THEN
BEGIN
UPDATE xxbr_ap_supp_cv_stg
SET process_flag = 'V',
record_status = 'Validated',
bank_account_id = l_dup_acct_id
WHERE pk_supplier = pick_data.pk_supplier;
EXCEPTION
WHEN OTHERS
THEN
l_sqlcode := SQLCODE;
l_sqlerrm := SUBSTR (SQLERRM, 1, 1000);
END;
ELSE
BEGIN
l_final_error_msg := 'Y';
UPDATE xxbr_ap_supp_cv_stg
SET error_message = l_i_err_msg,
process_flag = 'E',
record_status = 'Failure',
ERROR_CODE = lv_error_code
WHERE pk_supplier = pick_data.pk_supplier;
EXCEPTION
WHEN OTHERS
THEN
l_sqlcode := SQLCODE;
l_sqlerrm := SUBSTR (SQLERRM, 1, 1000);
END;
END IF;
COMMIT;
/*+--------------------------------------------------------------------------------------+
-- ============= End of Validation for Bank Account should not exist =======
+--------------------------------------------------------------------------------------+ */
/*+--------------------------------------------------------------------------------------+
------------------- Start of Validation for Payment Terms PR06---------------
+--------------------------------------------------------------------------------------+*/
BEGIN
IF pick_data.terms_name IS NOT NULL
THEN
SELECT NAME, term_id
INTO lv_terms_name, ln_term_id
FROM apps.ap_terms_tl
WHERE UPPER (NAME) = UPPER (pick_data.terms_name)
AND LANGUAGE = USERENV ('LANG');
END IF;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
l_return_status := 'E';
l_i_err_msg :=
l_i_err_msg
|| ''
|| 'Payment Term '
|| pick_data.terms_name
|| ' Not Valid for '
|| pick_data.vendor_name;
lv_error_code := lv_error_code || '' || 'Error-PR06';
WHEN OTHERS
THEN
l_return_status := 'E';
l_sqlcode := SQLCODE;
l_sqlerrm := SUBSTR (SQLERRM, 1, 1000);
END;
IF l_return_status = 'Y'
THEN
BEGIN
UPDATE xxbr_ap_supp_cv_stg
SET process_flag = 'V',
record_status = 'Validated',
term_id = ln_term_id
WHERE pk_supplier = pick_data.pk_supplier;
EXCEPTION
WHEN OTHERS
THEN
l_sqlcode := SQLCODE;
l_sqlerrm := SUBSTR (SQLERRM, 1, 1000);
END;
ELSE
BEGIN
l_final_error_msg := 'Y';
UPDATE xxbr_ap_supp_cv_stg
SET error_message = l_i_err_msg,
process_flag = 'E',
record_status = 'Failure',
ERROR_CODE = lv_error_code
WHERE pk_supplier = pick_data.pk_supplier;
EXCEPTION
WHEN OTHERS
THEN
l_sqlcode := SQLCODE;
l_sqlerrm := SUBSTR (SQLERRM, 1, 1000);
END;
END IF;
-------------------------------------------------------------------------------------------------------------
IF l_final_error_msg IS NULL
THEN
BEGIN
UPDATE xxbr_ap_supp_cv_stg
SET process_flag = 'V',
record_status = 'Validated'
-- ,term_id = ln_term_id
WHERE pk_supplier = pick_data.pk_supplier;
EXCEPTION
WHEN OTHERS
THEN
l_sqlcode := SQLCODE;
l_sqlerrm := SUBSTR (SQLERRM, 1, 1000);
END;
ELSE
BEGIN
UPDATE xxbr_ap_supp_cv_stg
SET -- error_message = l_i_err_msg
process_flag = 'E',
record_status = 'Failure'
-- ,error_code = 'VE'
WHERE pk_supplier = pick_data.pk_supplier;
EXCEPTION
WHEN OTHERS
THEN
l_sqlcode := SQLCODE;
l_sqlerrm := SUBSTR (SQLERRM, 1, 1000);
END;
END IF;
------------------------------------------------------------------------------------------------------------
END LOOP;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line ('NO Data Found');
COMMIT;
END xxbr_ap_supp_cv_validate;
/*+--------------------------------------------------------------------------------------+
-- ============== End of Validation for Payment Terms ==================
+--------------------------------------------------------------------------------------+ */
PROCEDURE xxbr_ap_supp_cv_load
IS
CURSOR ap_supplier_details_cur
IS
SELECT *
FROM xxbr_ap_supp_cv_stg
WHERE process_flag = 'V';
-- Variable Declaration
l_sqlerrm VARCHAR2 (1000);
l_i_err_msg VARCHAR2 (1000);
ln_sup_int_id NUMBER;
ln_user_id NUMBER
:= TO_NUMBER (apps.fnd_profile.VALUE ('USER_ID'));
ld_sysdate DATE := SYSDATE;
--ln_resp_id := TO_NUMBER(apps.fnd_profile.value('RESP_ID'));
--ln_resp_appl_id :=TO_NUMBER(apps.fnd_profile.value('RESP_APPL_ID'));
ln_iby_temp_ext NUMBER;
BEGIN
FOR load_data IN ap_supplier_details_cur
LOOP
BEGIN
l_sqlerrm := NULL;
SELECT apps.ap_suppliers_int_s.NEXTVAL
INTO ln_sup_int_id
FROM DUAL;
/*+--------------------------------------------------------------------------------------+
-- =============== Inserting records into Supplier Interface Table ===========
+--------------------------------------------------------------------------------------+ */
INSERT INTO apps.ap_suppliers_int
(vendor_interface_id, vendor_name,
vendor_name_alt, enabled_flag,
segment1, vendor_type_lookup_code,
status, invoice_currency_code,
payment_currency_code, terms_id,
party_orig_system_reference,
set_of_books_id,
hold_future_payments_flag,
employee_id, customer_num,
one_time_flag,
ship_to_location_id,
ship_to_location_code,
remittance_email,
auto_calculate_interest_flag,
--vat_code,
start_date_active, standard_industry_class,
terms_date_basis,
vat_registration_num,
tax_reporting_name,
match_option, last_update_date, last_updated_by,
creation_date, created_by
)
VALUES (ln_sup_int_id, load_data.vendor_name,
load_data.vendor_alternate_name, 'Y',
load_data.supplier_number, load_data.vendor_type,
'NEW', load_data.invoice_currency,
load_data.payment_currency, load_data.term_id,
load_data.supplier_number,
load_data.set_of_books_id,
load_data.hold_future_payments_flag,
load_data.employee_id, load_data.customer_number,
load_data.one_time_flag,
load_data.ship_to_location_id,
load_data.ship_to_location_code,
load_data.remittance_email,
load_data.auto_calculate_interest_flag,
--load_data.vat_code,
load_data.start_date_active, load_data.sic_code,
load_data.terms_date_basis,
load_data.vat_registration_number,
load_data.tax_reporting_name,
load_data.match_option, ld_sysdate, ln_user_id,
ld_sysdate, ln_user_id
);
/*+--------------------------------------------------------------------------------------+
-- ============== Inserting records into Supplier BANK Interface Table =======
+--------------------------------------------------------------------------------------+ */
SELECT iby_temp_ext_bank_accts_s.NEXTVAL
INTO ln_iby_temp_ext
FROM DUAL;
INSERT INTO iby_temp_ext_bank_accts
(calling_app_unique_ref1, country_code, bank_id,
branch_id, status, temp_ext_bank_acct_id,
last_update_date, last_updated_by, creation_date,
created_by, object_version_number,
bank_account_name,
bank_account_num, currency_code
) --,ext_payee_id, account_owner_party_id)
VALUES (ln_sup_int_id, 'GB', load_data.bank_id,
load_data.branch_id, 'NEW', ln_iby_temp_ext,
SYSDATE, 1215, SYSDATE,
1215, 1,
load_data.bank_account_name,
load_data.bank_account_number, 'GBP'
);
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line ('Error Occured' || SQLERRM);
l_sqlerrm := SQLERRM;
--Update staging table with errors
UPDATE xxbr_ap_supp_cv_stg
SET process_flag = 'E',
record_status = 'Error Inserting Iface table',
error_message = l_sqlerrm
WHERE pk_supplier = load_data.pk_supplier;
END;
END LOOP;
COMMIT;
END xxbr_ap_supp_cv_load;
--//===============================================================
--This Procedure call Supplier Open Interface Import Program
--//================================================================
PROCEDURE xxbr_ap_supp_cv_import
IS
CURSOR ap_supplier_details_cur
IS
SELECT *
FROM xxbr_ap_supp_cv_stg
WHERE process_flag = 'V';
----- Variable Declaration
l_request_id NUMBER;
ln_user_id NUMBER;
ln_resp_id NUMBER;
ln_resp_appl_id NUMBER;
x_req_return_status BOOLEAN;
x_req_phase VARCHAR2 (30);
x_req_status VARCHAR2 (30);
x_req_dev_phase VARCHAR2 (30);
x_req_dev_status VARCHAR2 (30);
x_req_message VARCHAR2 (50);
l_sqlerrm VARCHAR2 (2000);
lv_vendor_id NUMBER;
lv_int_reject_lookup_code VARCHAR2 (100);
lv_int_record_count NUMBER;
ln_org_id NUMBER;
BEGIN
ln_user_id := TO_NUMBER (apps.fnd_profile.VALUE ('USER_ID'));
ln_resp_id := TO_NUMBER (apps.fnd_profile.VALUE ('RESP_ID'));
ln_resp_appl_id := TO_NUMBER (apps.fnd_profile.VALUE ('RESP_APPL_ID'));
apps.fnd_global.apps_initialize (1215, 20640, 200);
--apps.fnd_global.apps_initialize(1318,50554,200);
--apps.fnd_global.apps_initialize(ln_user_id,ln_resp_id,ln_resp_appl_id);
--mo_global.init('AP');
-------------- Getting the Operating Unit while submitting the request for Supplier --------
/*BEGIN
SELECT DISTINCT org_id
INTO ln_org_id
FROM XXBR_AP_SUPP_CV_STG
WHERE ROWNUM =1;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Error Occured'||SQLERRM);
-- l_sqlerrm:=SQLERRM;
--Update staging table with errors
END;
*/
-- fnd_request.set_org_id(org_id => ln_org_id);
fnd_request.set_org_id (org_id => 107);
COMMIT;
l_request_id :=
fnd_request.submit_request
(application => 'SQLAP',
program => 'APXSUIMP',
description => 'Supplier Open Interface Import',
start_time => SYSDATE,
sub_request => FALSE,
argument1 => 'NEW',
argument2 => 10000,
argument3 => 'N',
argument4 => 'N',
argument5 => 'N'
);
COMMIT;
DBMS_OUTPUT.put_line (l_request_id);
IF l_request_id = 0
THEN
DBMS_OUTPUT.put_line ('Request not submitted');
ELSE
x_req_return_status :=
fnd_concurrent.wait_for_request (l_request_id,
20,
0,
x_req_phase,
x_req_status,
x_req_dev_phase,
x_req_dev_status,
x_req_message
);
END IF;
COMMIT;
IF x_req_return_status = TRUE
THEN
DBMS_OUTPUT.put_line ('Request completed successfully');
--- Checking if there are any records in the interface table with the status 'NEW'
BEGIN
SELECT COUNT (vendor_interface_id)
INTO lv_int_record_count
FROM ap_suppliers_int
WHERE status = 'NEW';
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line ('Error Occured' || SQLERRM);
l_sqlerrm := SQLERRM;
END;
IF lv_int_record_count > 0
THEN
l_request_id :=
fnd_request.submit_request
(application => 'SQLAP',
program => 'APXSUIMP',
description => 'Supplier Open Interface Import',
start_time => SYSDATE,
sub_request => FALSE,
argument1 => 'NEW',
argument2 => 10000,
argument3 => 'N',
argument4 => 'N',
argument5 => 'N'
);
COMMIT;
DBMS_OUTPUT.put_line (l_request_id);
COMMIT;
END IF;
IF l_request_id = 0
THEN
DBMS_OUTPUT.put_line ('Request not submitted');
ELSE
x_req_return_status :=
fnd_concurrent.wait_for_request (l_request_id,
20,
0,
x_req_phase,
x_req_status,
x_req_dev_phase,
x_req_dev_status,
x_req_message
);
END IF;
COMMIT;
IF x_req_return_status = TRUE
THEN
DBMS_OUTPUT.put_line ('Request completed successfully');
BEGIN
FOR load_data IN ap_supplier_details_cur
LOOP
BEGIN
l_sqlerrm := NULL;
BEGIN
SELECT vendor_id
INTO lv_vendor_id
FROM ap_suppliers
WHERE vendor_name = load_data.vendor_name;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line ('Error Occured' || SQLERRM);
l_sqlerrm := SQLERRM;
END;
IF lv_vendor_id IS NOT NULL
THEN
UPDATE xxbr_ap_supp_cv_stg
SET process_flag = 'P',
record_status = 'Successfully Processed'
WHERE pk_supplier = load_data.pk_supplier
AND process_flag = 'V';
ELSE
BEGIN
SELECT rej.reject_lookup_code
INTO lv_int_reject_lookup_code
FROM ap_supplier_int_rejections rej,
ap_suppliers_int INT
WHERE rej.parent_id = INT.vendor_interface_id
AND parent_table = 'AP_SUPPLIERS_INT'
AND INT.segment1 = load_data.supplier_number
AND INT.status NOT IN ('NEW')
AND ROWNUM = 1;
-- Added this condition so that when multiple errors are coming it should not go in exception;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line ('Error Occured' || SQLERRM
);
l_sqlerrm := SQLERRM;
END;
IF lv_int_reject_lookup_code IS NOT NULL
THEN
UPDATE xxbr_ap_supp_cv_stg
SET process_flag = 'E',
record_status = 'Error',
ERROR_CODE = 'VE',
error_message = lv_int_reject_lookup_code
WHERE pk_supplier = load_data.pk_supplier;
ELSE
UPDATE xxbr_ap_supp_cv_stg
SET process_flag = 'E',
record_status = 'Error',
ERROR_CODE = 'VE'
-- , error_message = l_sqlerrm
WHERE pk_supplier = load_data.pk_supplier;
END IF;
END IF;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line ('Error Occured' || SQLERRM);
l_sqlerrm := SQLERRM;
--Update staging table with errors
UPDATE xxbr_ap_supp_cv_stg
SET process_flag = 'E',
ERROR_CODE = 'VE',
record_status = 'Error Updating Staging tbl',
error_message = l_sqlerrm
WHERE pk_supplier = load_data.pk_supplier;
END;
END LOOP;
END;
COMMIT;
END IF;
END IF;
END xxbr_ap_supp_cv_import;
--**************************************************************************
---------------------------------- SUPPLIER SITES ------------------------------------------------
--****************************************************************************
PROCEDURE xxbr_ap_supp_site_cv_main (p_action IN VARCHAR2)
IS
BEGIN
-- Call validate procedure to validate the Supplier Sites information
IF p_action = 'validate'
THEN
xxbr_ap_supp_site_cv_validate;
ELSE
NULL;
END IF;
-- Call Insert procedure to load the data into Supplier Sites interface table
IF p_action = 'load'
THEN
xxbr_ap_supp_site_cv_load;
ELSE
NULL;
END IF;
END xxbr_ap_supp_site_cv_main;
--//===========================================================
--// This is validation Procedure for Supplier Site Information
--//===========================================================
PROCEDURE xxbr_ap_supp_site_cv_validate
IS
--Variable declaration
l_sqlcode NUMBER;
l_sqlerrm VARCHAR2 (1000);
l_return_status VARCHAR2 (1);
l_i_err_msg VARCHAR2 (1000);
l_count NUMBER := 0;
ln_vendor_id NUMBER;
lv_supplier_type VARCHAR2 (1000);
lv_invoice_currency VARCHAR2 (3);
lv_payment_currency VARCHAR2 (3);
lv_terms_name VARCHAR2 (100);
ln_term_id NUMBER;
lv_operating_unit_name VARCHAR2 (100);
l_org_id NUMBER;
lv_dup_site NUMBER;
lv_err_msg VARCHAR2 (1000);
lv_ship_to_location_id NUMBER;
lv_bill_to_location_id NUMBER;
lv_tolerance_id NUMBER;
lv_match_option VARCHAR2 (100);
l_final_error_msg VARCHAR2 (100);
lv_territory_code VARCHAR2 (100);
lv_error_code VARCHAR2 (1000);
-- Variable declaration for Bank Validation
p_bank_country_code hz_parties.country%TYPE;
p_bank_name hz_parties.party_name%TYPE;
p_bank_number hz_organization_profiles.bank_or_branch_number%TYPE;
x_bank_id NUMBER;
x_end_date DATE;
ln_bank_id NUMBER;
-- Variable declaration for Bank Branch Validation
x_branch_id NUMBER;
p_bank_id NUMBER;
p_branch_name hz_parties.party_name%TYPE;
p_branch_number hz_organization_profiles.bank_or_branch_number%TYPE;
-- Variable declaration for Bank Account Validation
p_ext_bank_acct_rec iby_ext_bankacct_pub.extbankacct_rec_type;
l_dup_acct_id NUMBER;
l_dup_start_date DATE;
l_dup_end_date DATE;
x_response iby_fndcpt_common_pub.result_rec_type;
x_return_status VARCHAR2 (1000);
x_msg_count NUMBER;
x_msg_data VARCHAR2 (1000);
l_bank_account_num_len NUMBER;
CURSOR ap_supp_sites_cur
IS
SELECT *
FROM xxbr_ap_supp_site_cv_stg
WHERE process_flag = 'N' AND rec_type IN ('D', 'd');
supplier_sites_tab xxbr_ap_supp_site_cv_stg%ROWTYPE;
BEGIN
FOR pick_data_sites IN ap_supp_sites_cur
LOOP
l_return_status := 'Y';
l_i_err_msg := NULL;
l_final_error_msg := NULL;
lv_error_code := NULL;
/*+--------------------------------------------------------------------------------------+
-- ================== Validation for Operating Unit exists or not PR15 =======
+--------------------------------------------------------------------------------------+ */
BEGIN
SELECT NAME, organization_id
INTO lv_operating_unit_name, l_org_id
FROM apps.hr_operating_units
WHERE NAME = (TRIM (pick_data_sites.operating_unit_name));
EXCEPTION
WHEN NO_DATA_FOUND
THEN
l_return_status := 'E';
l_i_err_msg :=
l_i_err_msg
|| ''
|| pick_data_sites.operating_unit_name
|| ' Operating Unit does not exists.Please check Operating Unit Name';
lv_error_code := lv_error_code || '' || 'Error-PR15';
WHEN OTHERS
THEN
l_return_status := 'E';
l_sqlcode := SQLCODE;
l_sqlerrm := SUBSTR (SQLERRM, 1, 1000);
END;
IF l_return_status = 'Y'
THEN
BEGIN
UPDATE xxbr_ap_supp_site_cv_stg
SET process_flag = 'V',
record_status = 'Validated',
org_id = l_org_id
WHERE pk_supplier_site = pick_data_sites.pk_supplier_site;
EXCEPTION
WHEN OTHERS
THEN
l_sqlcode := SQLCODE;
l_sqlerrm := SUBSTR (SQLERRM, 1, 1000);
END;
ELSE
BEGIN
l_final_error_msg := 'Y';
UPDATE xxbr_ap_supp_site_cv_stg
SET error_message = l_i_err_msg,
process_flag = 'E',
record_status = 'Failure',
ERROR_CODE = lv_error_code
WHERE pk_supplier_site = pick_data_sites.pk_supplier_site;
EXCEPTION
WHEN OTHERS
THEN
l_sqlcode := SQLCODE;
l_sqlerrm := SUBSTR (SQLERRM, 1, 1000);
END;
END IF;
/*+--------------------------------------------------------------------------------------+
-- ================== End of Validation for Operating Unit exists or not ======
+--------------------------------------------------------------------------------------+ */
/*+--------------------------------------------------------------------------------------+
-- ================== Validation for Supplier exists or not PR18 ============
+--------------------------------------------------------------------------------------+ */
BEGIN
SELECT vendor_id
INTO ln_vendor_id
FROM apps.ap_suppliers
WHERE (UPPER (vendor_name) IN TRIM
(UPPER
(pick_data_sites.vendor_name)
)
);
EXCEPTION
WHEN NO_DATA_FOUND
THEN
l_return_status := 'E';
l_i_err_msg :=
l_i_err_msg
|| ''
|| pick_data_sites.vendor_name
|| ' Vendor does not exists.Please create supplier first before creating Supplier Site';
lv_error_code := lv_error_code || '' || 'Error-PR18';
WHEN OTHERS
THEN
l_return_status := 'E';
l_sqlcode := SQLCODE;
l_sqlerrm := SUBSTR (SQLERRM, 1, 1000);
END;
IF l_return_status = 'Y'
THEN
BEGIN
UPDATE xxbr_ap_supp_site_cv_stg
SET process_flag = 'V',
record_status = 'Validated',
vendor_id = ln_vendor_id
WHERE pk_supplier_site = pick_data_sites.pk_supplier_site;
EXCEPTION
WHEN OTHERS
THEN
l_sqlcode := SQLCODE;
l_sqlerrm := SUBSTR (SQLERRM, 1, 1000);
END;
ELSE
BEGIN
l_final_error_msg := 'Y';
UPDATE xxbr_ap_supp_site_cv_stg
SET error_message = l_i_err_msg,
process_flag = 'E',
record_status = 'Failure',
ERROR_CODE = lv_error_code
WHERE pk_supplier_site = pick_data_sites.pk_supplier_site;
EXCEPTION
WHEN OTHERS
THEN
l_sqlcode := SQLCODE;
l_sqlerrm := SUBSTR (SQLERRM, 1, 1000);
END;
END IF;
/*+--------------------------------------------------------------------------------------+
-- ================== End of Validation for Supplier exists or not ===========
+--------------------------------------------------------------------------------------+ */
/*+--------------------------------------------------------------------------------------+
------------------- Start of Validation for Bank exists or not PR24------------------------
+--------------------------------------------------------------------------------------+*/
BEGIN
IF pick_data_sites.bank_name IS NOT NULL
-- AND pick_data_sites.bank_number IS NOT NULL
THEN
p_bank_country_code := 'GB';
-- This hardcoding needs to be removed and this field needs to be populated as per Supplier country
p_bank_name := pick_data_sites.bank_name;
p_bank_number := pick_data_sites.bank_number;
ce_bank_pub.check_bank_exist (p_bank_country_code,
p_bank_name,
p_bank_number,
x_bank_id,
x_end_date
);
COMMIT;
END IF;
IF x_bank_id IS NULL
THEN
l_return_status := 'E';
l_i_err_msg :=
l_i_err_msg
|| '|'
|| 'The Bank '
|| pick_data_sites.bank_name
|| ' does not exist in the system. Please create the bank first before creating ext bank account for vendor '
|| pick_data_sites.vendor_name;
lv_error_code := lv_error_code || '' || 'Error-PR24';
END IF;
EXCEPTION
WHEN OTHERS
THEN
l_return_status := 'E';
l_sqlcode := SQLCODE;
l_sqlerrm := SUBSTR (SQLERRM, 1, 1000);
END;
IF l_return_status = 'Y'
THEN
BEGIN
UPDATE xxbr_ap_supp_site_cv_stg
SET process_flag = 'V',
record_status = 'Validated',
bank_id = x_bank_id
WHERE pk_supplier_site = pick_data_sites.pk_supplier_site;
EXCEPTION
WHEN OTHERS
THEN
l_sqlcode := SQLCODE;
l_sqlerrm := SUBSTR (SQLERRM, 1, 1000);
END;
ELSE
BEGIN
l_final_error_msg := 'Y';
UPDATE xxbr_ap_supp_site_cv_stg
SET error_message = l_i_err_msg,
process_flag = 'E',
record_status = 'Failure',
ERROR_CODE = lv_error_code
WHERE pk_supplier_site = pick_data_sites.pk_supplier_site;
EXCEPTION
WHEN OTHERS
THEN
l_sqlcode := SQLCODE;
l_sqlerrm := SUBSTR (SQLERRM, 1, 1000);
END;
END IF;
COMMIT;
/*+--------------------------------------------------------------------------------------+
-- ================== End of Validation for Bank exists or not ==============
+--------------------------------------------------------------------------------------+ */
/*+--------------------------------------------------------------------------------------+
------------------- Start of Validation for Bank Branch exists or not PR23----------------
+--------------------------------------------------------------------------------------+*/
BEGIN
IF pick_data_sites.bank_branch_name IS NOT NULL
THEN
p_bank_country_code := 'GB';
-- This hardcoding needs to be removed and this field needs to be populated as per Supplier country
p_branch_name := pick_data_sites.bank_branch_name;
p_branch_number := pick_data_sites.bank_number;
BEGIN
SELECT party_id
INTO ln_bank_id
FROM hz_parties
WHERE party_name = pick_data_sites.bank_name;
EXCEPTION
WHEN OTHERS
THEN
NULL;
END;
--p_bank_id := 7169;
p_bank_id := ln_bank_id;
ce_bank_pub.check_branch_exist (p_bank_id,
p_branch_name,
p_branch_number,
x_branch_id,
x_end_date
);
COMMIT;
END IF;
IF x_branch_id IS NULL
THEN
l_return_status := 'E';
l_i_err_msg :=
l_i_err_msg
|| ''
|| 'The branch '
|| pick_data_sites.bank_branch_name
|| ' does not exist for bank '
|| pick_data_sites.bank_name
|| ' in the system. Please create the bank branch first before creating ext bank account for vendor '
|| pick_data_sites.vendor_name;
lv_error_code := lv_error_code || '' || 'Error-PR23';
END IF;
EXCEPTION
WHEN OTHERS
THEN
l_return_status := 'E';
l_sqlcode := SQLCODE;
l_sqlerrm := SUBSTR (SQLERRM, 1, 1000);
END;
IF l_return_status = 'Y'
THEN
BEGIN
UPDATE xxbr_ap_supp_site_cv_stg
SET process_flag = 'V',
record_status = 'Validated',
branch_id = x_branch_id
WHERE pk_supplier_site = pick_data_sites.pk_supplier_site;
EXCEPTION
WHEN OTHERS
THEN
l_sqlcode := SQLCODE;
l_sqlerrm := SUBSTR (SQLERRM, 1, 1000);
END;
ELSE
BEGIN
l_final_error_msg := 'Y';
UPDATE xxbr_ap_supp_site_cv_stg
SET error_message = l_i_err_msg,
process_flag = 'E',
record_status = 'Failure',
ERROR_CODE = lv_error_code
WHERE pk_supplier_site = pick_data_sites.pk_supplier_site;
EXCEPTION
WHEN OTHERS
THEN
l_sqlcode := SQLCODE;
l_sqlerrm := SUBSTR (SQLERRM, 1, 1000);
END;
END IF;
COMMIT;
/*+--------------------------------------------------------------------------------------+
-- =========== End of Validation for Bank Branch exists or not ==============
+--------------------------------------------------------------------------------------+ */
/*+--------------------------------------------------------------------------------------+
------------------- Start of Validation for Bank Account Number Length PR30-------
+--------------------------------------------------------------------------------------+*/
BEGIN
IF pick_data_sites.bank_account_number IS NOT NULL
THEN
SELECT LENGTH (pick_data_sites.bank_account_number)
INTO l_bank_account_num_len
FROM DUAL;
COMMIT;
END IF;
IF l_bank_account_num_len > 8
THEN
l_return_status := 'E';
l_i_err_msg :=
l_i_err_msg
|| ''
|| 'Please enter a maximum of 8 characters for the Account Number for Account '
|| pick_data_sites.bank_account_number
|| 'for bank '
|| pick_data_sites.bank_name
|| ' for vendor '
|| pick_data_sites.vendor_name;
lv_error_code := lv_error_code || '' || 'Error-PR30';
END IF;
EXCEPTION
WHEN OTHERS
THEN
l_return_status := 'E';
l_sqlcode := SQLCODE;
l_sqlerrm := SUBSTR (SQLERRM, 1, 1000);
END;
IF l_return_status = 'Y'
THEN
BEGIN
UPDATE xxbr_ap_supp_site_cv_stg
SET process_flag = 'V',
record_status = 'Validated',
bank_account_id = l_dup_acct_id
WHERE pk_supplier_site = pick_data_sites.pk_supplier_site;
EXCEPTION
WHEN OTHERS
THEN
l_sqlcode := SQLCODE;
l_sqlerrm := SUBSTR (SQLERRM, 1, 1000);
END;
ELSE
BEGIN
l_final_error_msg := 'Y';
UPDATE xxbr_ap_supp_site_cv_stg
SET error_message = l_i_err_msg,
process_flag = 'E',
record_status = 'Failure',
ERROR_CODE = lv_error_code
WHERE pk_supplier_site = pick_data_sites.pk_supplier_site;
EXCEPTION
WHEN OTHERS
THEN
l_sqlcode := SQLCODE;
l_sqlerrm := SUBSTR (SQLERRM, 1, 1000);
END;
END IF;
COMMIT;
/*+--------------------------------------------------------------------------------------+
-- =============== End of Validation for Bank Account Number Length =======
+--------------------------------------------------------------------------------------+ */
/*+--------------------------------------------------------------------------------------+
------------------- Start of Validation for Bank Account should not exist PR28
+--------------------------------------------------------------------------------------+*/
BEGIN
IF pick_data_sites.bank_account_name IS NOT NULL
AND pick_data_sites.bank_account_number IS NOT NULL
THEN
p_ext_bank_acct_rec.bank_id := ln_bank_id;
p_ext_bank_acct_rec.branch_id := x_branch_id;
p_ext_bank_acct_rec.bank_account_name :=
pick_data_sites.bank_account_name;
p_ext_bank_acct_rec.bank_account_num :=
pick_data_sites.bank_account_number;
--p_ext_bank_acct_rec.acct_owner_party_id := 7162;
p_ext_bank_acct_rec.currency := 'GBP';
p_ext_bank_acct_rec.country_code := 'GB';
iby_ext_bankacct_pub.check_ext_acct_exist
(p_api_version => '1.0',
p_init_msg_list => 'T',
p_ext_bank_acct_rec => p_ext_bank_acct_rec,
x_acct_id => l_dup_acct_id,
x_start_date => l_dup_start_date,
x_end_date => l_dup_end_date,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
x_response => x_response
);
COMMIT;
END IF;
IF l_dup_acct_id IS NOT NULL
THEN
l_return_status := 'E';
l_i_err_msg :=
l_i_err_msg
|| ''
|| 'The Account '
|| pick_data_sites.bank_account_number
|| ' already exist for bank '
|| pick_data_sites.bank_name
|| ' in the system for vendor '
|| pick_data_sites.vendor_name;
lv_error_code := lv_error_code || '' || 'Error-PR28';
END IF;
EXCEPTION
WHEN OTHERS
THEN
l_return_status := 'E';
l_sqlcode := SQLCODE;
l_sqlerrm := SUBSTR (SQLERRM, 1, 1000);
END;
IF l_return_status = 'Y'
THEN
BEGIN
UPDATE xxbr_ap_supp_site_cv_stg
SET process_flag = 'V',
record_status = 'Validated'
WHERE pk_supplier_site = pick_data_sites.pk_supplier_site;
EXCEPTION
WHEN OTHERS
THEN
l_sqlcode := SQLCODE;
l_sqlerrm := SUBSTR (SQLERRM, 1, 1000);
END;
ELSE
BEGIN
l_final_error_msg := 'Y';
UPDATE xxbr_ap_supp_site_cv_stg
SET error_message = l_i_err_msg,
process_flag = 'E',
record_status = 'Failure',
ERROR_CODE = lv_error_code,
bank_account_id = l_dup_acct_id
WHERE pk_supplier_site = pick_data_sites.pk_supplier_site;
EXCEPTION
WHEN OTHERS
THEN
l_sqlcode := SQLCODE;
l_sqlerrm := SUBSTR (SQLERRM, 1, 1000);
END;
END IF;
COMMIT;
/*+--------------------------------------------------------------------------------------+
-- ============= End of Validation for Bank Account should not exist ==========
+--------------------------------------------------------------------------------------+ */
/*+--------------------------------------------------------------------------------------+
-- ============ Validation for Supplier Site Address Country PR14============
+--------------------------------------------------------------------------------------+ */
BEGIN
SELECT territory_code
INTO lv_territory_code
FROM apps.fnd_territories
WHERE (UPPER (territory_code) IN TRIM
(UPPER
(pick_data_sites.country)
)
);
EXCEPTION
WHEN NO_DATA_FOUND
THEN
l_return_status := 'E';
l_i_err_msg :=
l_i_err_msg
|| ''
|| 'Country Code '
|| pick_data_sites.country
|| ' Not Valid for '
|| pick_data_sites.vendor_name;
lv_error_code := lv_error_code || '' || 'Error-PR14';
WHEN OTHERS
THEN
l_return_status := 'E';
l_sqlcode := SQLCODE;
l_sqlerrm := SUBSTR (SQLERRM, 1, 1000);
END;
IF l_return_status = 'Y'
THEN
BEGIN
UPDATE xxbr_ap_supp_site_cv_stg
SET process_flag = 'V',
record_status = 'Validated'
WHERE pk_supplier_site = pick_data_sites.pk_supplier_site;
EXCEPTION
WHEN OTHERS
THEN
l_sqlcode := SQLCODE;
l_sqlerrm := SUBSTR (SQLERRM, 1, 1000);
END;
ELSE
BEGIN
l_final_error_msg := 'Y';
UPDATE xxbr_ap_supp_site_cv_stg
SET error_message = l_i_err_msg,
process_flag = 'E',
record_status = 'Failure',
ERROR_CODE = lv_error_code
WHERE pk_supplier_site = pick_data_sites.pk_supplier_site;
EXCEPTION
WHEN OTHERS
THEN
l_sqlcode := SQLCODE;
l_sqlerrm := SUBSTR (SQLERRM, 1, 1000);
END;
END IF;
/*+--------------------------------------------------------------------------------------+
-- ============= End of Validation for Supplier Site Address Country =========
+--------------------------------------------------------------------------------------+ */
/*+--------------------------------------------------------------------------------------+
-------------------Start of Validation for Ship to Location Code PR31-------------------
+--------------------------------------------------------------------------------------+*/
BEGIN
IF pick_data_sites.ship_to_location_code IS NOT NULL
THEN
SELECT DISTINCT location_id
INTO lv_ship_to_location_id
FROM hr_locations_all
WHERE ship_to_site_flag = 'Y'
AND location_code =
pick_data_sites.ship_to_location_code;
END IF;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
l_return_status := 'E';
l_i_err_msg :=
l_i_err_msg
|| ''
|| 'Ship to Location Code '
|| pick_data_sites.ship_to_location_code
|| ' Not Valid for '
|| pick_data_sites.vendor_name;
lv_error_code := lv_error_code || '' || 'Error-PR31';
WHEN OTHERS
THEN
l_return_status := 'E';
l_sqlcode := SQLCODE;
l_sqlerrm := SUBSTR (SQLERRM, 1, 1000);
END;
IF l_return_status = 'Y'
THEN
BEGIN
UPDATE xxbr_ap_supp_site_cv_stg
SET process_flag = 'V',
record_status = 'Validated'
-- ,ship_to_location_id = lv_ship_to_location_id
WHERE pk_supplier_site = pick_data_sites.pk_supplier_site;
EXCEPTION
WHEN OTHERS
THEN
l_sqlcode := SQLCODE;
l_sqlerrm := SUBSTR (SQLERRM, 1, 1000);
END;
ELSE
BEGIN
l_final_error_msg := 'Y';
UPDATE xxbr_ap_supp_site_cv_stg
SET error_message = l_i_err_msg,
process_flag = 'E',
record_status = 'Failure',
ERROR_CODE = lv_error_code
WHERE pk_supplier_site = pick_data_sites.pk_supplier_site;
EXCEPTION
WHEN OTHERS
THEN
l_sqlcode := SQLCODE;
l_sqlerrm := SUBSTR (SQLERRM, 1, 1000);
END;
END IF;
/*+--------------------------------------------------------------------------------------+
-- ================ End of Validation for Ship to Location Code ===========
+--------------------------------------------------------------------------------------+ */
/*+--------------------------------------------------------------------------------------+
-------------------Start of Validation for Bill to Location Code PR32-----------------------------
+--------------------------------------------------------------------------------------+*/
BEGIN
IF pick_data_sites.bill_to_location_code IS NOT NULL
THEN
SELECT DISTINCT location_id
INTO lv_bill_to_location_id
FROM hr_locations_all
WHERE bill_to_site_flag = 'Y'
AND location_code =
pick_data_sites.bill_to_location_code;
END IF;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
l_return_status := 'E';
l_i_err_msg :=
l_i_err_msg
|| ''
|| 'Ship to Location Code '
|| pick_data_sites.ship_to_location_code
|| ' Not Valid for '
|| pick_data_sites.vendor_name;
lv_error_code := lv_error_code || '' || 'Error-PR32';
WHEN OTHERS
THEN
l_return_status := 'E';
l_sqlcode := SQLCODE;
l_sqlerrm := SUBSTR (SQLERRM, 1, 1000);
END;
IF l_return_status = 'Y'
THEN
BEGIN
UPDATE xxbr_ap_supp_site_cv_stg
SET process_flag = 'V',
record_status = 'Validated'
-- ,ship_to_location_id = lv_ship_to_location_id
WHERE pk_supplier_site = pick_data_sites.pk_supplier_site;
EXCEPTION
WHEN OTHERS
THEN
l_sqlcode := SQLCODE;
l_sqlerrm := SUBSTR (SQLERRM, 1, 1000);
END;
ELSE
BEGIN
l_final_error_msg := 'Y';
UPDATE xxbr_ap_supp_site_cv_stg
SET error_message = l_i_err_msg,
process_flag = 'E',
record_status = 'Failure',
ERROR_CODE = lv_error_code
WHERE pk_supplier_site = pick_data_sites.pk_supplier_site;
EXCEPTION
WHEN OTHERS
THEN
l_sqlcode := SQLCODE;
l_sqlerrm := SUBSTR (SQLERRM, 1, 1000);
END;
END IF;
/*+--------------------------------------------------------------------------------------+
-- ================== End of Validation for Bill to Location Code ===========
+--------------------------------------------------------------------------------------+ */
/*+--------------------------------------------------------------------------------------+
------------------- Start of Validation for Match Option PR13-----------------------------
+--------------------------------------------------------------------------------------+*/
BEGIN
IF pick_data_sites.match_option IS NOT NULL
THEN
SELECT lookup_code
INTO lv_match_option
FROM fnd_lookups
WHERE lookup_type = 'POS_INVOICE_MATCH_OPTION'
AND UPPER (lookup_code) =
UPPER (pick_data_sites.match_option);
END IF;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
l_return_status := 'E';
l_i_err_msg :=
l_i_err_msg
|| ''
|| 'Match Option '
|| pick_data_sites.match_option
|| ' Not Valid for '
|| pick_data_sites.vendor_name;
lv_error_code := lv_error_code || '' || 'Error-PR13';
WHEN OTHERS
THEN
l_return_status := 'E';
l_sqlcode := SQLCODE;
l_sqlerrm := SUBSTR (SQLERRM, 1, 1000);
END;
IF l_return_status = 'Y'
THEN
BEGIN
UPDATE xxbr_ap_supp_site_cv_stg
SET process_flag = 'V',
record_status = 'Validated'
WHERE pk_supplier_site = pick_data_sites.pk_supplier_site;
EXCEPTION
WHEN OTHERS
THEN
l_sqlcode := SQLCODE;
l_sqlerrm := SUBSTR (SQLERRM, 1, 1000);
END;
ELSE
BEGIN
l_final_error_msg := 'Y';
UPDATE xxbr_ap_supp_site_cv_stg
SET error_message = l_i_err_msg,
process_flag = 'E',
record_status = 'Failure',
ERROR_CODE = lv_error_code
WHERE pk_supplier_site = pick_data_sites.pk_supplier_site;
EXCEPTION
WHEN OTHERS
THEN
l_sqlcode := SQLCODE;
l_sqlerrm := SUBSTR (SQLERRM, 1, 1000);
END;
END IF;
/*+--------------------------------------------------------------------------------------+
-- ================== End of Validation for Match Option =================
+--------------------------------------------------------------------------------------+ */
/*+--------------------------------------------------------------------------------------+
-------------------Start of Validation for Tolerance Name PR33-----------------------------
+--------------------------------------------------------------------------------------+*/
BEGIN
IF pick_data_sites.tolerance_name IS NOT NULL
THEN
SELECT DISTINCT tolerance_id
INTO lv_tolerance_id
FROM ap_tolerance_templates
WHERE tolerance_type = 'GOODS'
AND tolerance_name = pick_data_sites.tolerance_name;
END IF;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
l_return_status := 'E';
l_i_err_msg :=
l_i_err_msg
|| ''
|| 'Tolerance '
|| pick_data_sites.tolerance_name
|| ' Not Valid for '
|| pick_data_sites.vendor_name;
lv_error_code := lv_error_code || '' || 'Error-PR33';
WHEN OTHERS
THEN
l_return_status := 'E';
l_sqlcode := SQLCODE;
l_sqlerrm := SUBSTR (SQLERRM, 1, 1000);
END;
IF l_return_status = 'Y'
THEN
BEGIN
UPDATE xxbr_ap_supp_site_cv_stg
SET process_flag = 'V',
record_status = 'Validated'
-- ,ship_to_location_id = lv_ship_to_location_id
WHERE pk_supplier_site = pick_data_sites.pk_supplier_site;
EXCEPTION
WHEN OTHERS
THEN
l_sqlcode := SQLCODE;
l_sqlerrm := SUBSTR (SQLERRM, 1, 1000);
END;
ELSE
BEGIN
l_final_error_msg := 'Y';
UPDATE xxbr_ap_supp_site_cv_stg
SET error_message = l_i_err_msg,
process_flag = 'E',
record_status = 'Failure',
ERROR_CODE = lv_error_code
WHERE pk_supplier_site = pick_data_sites.pk_supplier_site;
EXCEPTION
WHEN OTHERS
THEN
l_sqlcode := SQLCODE;
l_sqlerrm := SUBSTR (SQLERRM, 1, 1000);
END;
END IF;
/*+--------------------------------------------------------------------------------------+
-- ================== End of Validation for Tolerance Name ================
+--------------------------------------------------------------------------------------+ */
/*+--------------------------------------------------------------------------------------+
-------------------Start of Validation for Gapless Invoice Number Flag PR34-----------------------------
+--------------------------------------------------------------------------------------+*/
BEGIN
IF pick_data_sites.gapless_inv_number_flag IS NOT NULL
THEN
IF pick_data_sites.gapless_inv_number_flag NOT IN ('Y', 'N')
THEN
l_return_status := 'E';
l_i_err_msg :=
l_i_err_msg
|| ''
|| 'One Time Flag '
|| pick_data_sites.gapless_inv_number_flag
|| ' Not Valid for '
|| pick_data_sites.vendor_name
|| '. Please enter Y or N.';
lv_error_code := lv_error_code || '' || 'Error-PR34';
END IF;
END IF;
IF l_return_status = 'Y'
THEN
BEGIN
UPDATE xxbr_ap_supp_site_cv_stg
SET process_flag = 'V',
record_status = 'Validated'
WHERE pk_supplier_site = pick_data_sites.pk_supplier_site;
EXCEPTION
WHEN OTHERS
THEN
l_sqlcode := SQLCODE;
l_sqlerrm := SUBSTR (SQLERRM, 1, 1000);
END;
ELSE
BEGIN
l_final_error_msg := 'Y';
UPDATE xxbr_ap_supp_site_cv_stg
SET error_message = l_i_err_msg,
process_flag = 'E',
record_status = 'Failure',
ERROR_CODE = lv_error_code
WHERE pk_supplier_site = pick_data_sites.pk_supplier_site;
EXCEPTION
WHEN OTHERS
THEN
l_sqlcode := SQLCODE;
l_sqlerrm := SUBSTR (SQLERRM, 1, 1000);
END;
END IF;
END;
/*+--------------------------------------------------------------------------------------+
-- ================== End of Validation for Gapless Invoice Number Flag ======
+--------------------------------------------------------------------------------------+ */
/*+--------------------------------------------------------------------------------------+
-------------------Start of Validation for Invoice Currency PR16---------------
+--------------------------------------------------------------------------------------+*/
BEGIN
IF pick_data_sites.invoice_currency IS NOT NULL
THEN
SELECT currency_code
INTO lv_invoice_currency
FROM apps.fnd_currencies
WHERE currency_code = pick_data_sites.invoice_currency;
END IF;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
l_return_status := 'E';
l_i_err_msg :=
l_i_err_msg
|| ''
|| 'Invoice Currency '
|| pick_data_sites.invoice_currency
|| ' Not Valid for '
|| pick_data_sites.vendor_name;
lv_error_code := lv_error_code || '' || 'Error-PR16';
WHEN OTHERS
THEN
l_return_status := 'E';
l_sqlcode := SQLCODE;
l_sqlerrm := SUBSTR (SQLERRM, 1, 1000);
END;
IF l_return_status = 'Y'
THEN
BEGIN
UPDATE xxbr_ap_supp_site_cv_stg
SET process_flag = 'V',
record_status = 'Validated'
WHERE pk_supplier_site = pick_data_sites.pk_supplier_site;
EXCEPTION
WHEN OTHERS
THEN
l_sqlcode := SQLCODE;
l_sqlerrm := SUBSTR (SQLERRM, 1, 1000);
END;
ELSE
BEGIN
l_final_error_msg := 'Y';
UPDATE xxbr_ap_supp_site_cv_stg
SET error_message = l_i_err_msg,
process_flag = 'E',
record_status = 'Failure',
ERROR_CODE = lv_error_code
WHERE pk_supplier_site = pick_data_sites.pk_supplier_site;
EXCEPTION
WHEN OTHERS
THEN
l_sqlcode := SQLCODE;
l_sqlerrm := SUBSTR (SQLERRM, 1, 1000);
END;
END IF;
/*+--------------------------------------------------------------------------------------+
-- ======== End of Validation for Invoice Currency =======================
+--------------------------------------------------------------------------------------+ */
/*+--------------------------------------------------------------------------------------+
-------------------Start of Validation for Payment Currency PR17---------------
+--------------------------------------------------------------------------------------+*/
BEGIN
IF pick_data_sites.payment_currency IS NOT NULL
THEN
SELECT currency_code
INTO lv_payment_currency
FROM apps.fnd_currencies
WHERE currency_code = pick_data_sites.payment_currency;
END IF;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
l_return_status := 'E';
l_i_err_msg :=
l_i_err_msg
|| ''
|| 'Payment Currency '
|| pick_data_sites.payment_currency
|| ' Not Valid for '
|| pick_data_sites.vendor_name;
lv_error_code := lv_error_code || '' || 'Error-PR17';
WHEN OTHERS
THEN
l_return_status := 'E';
l_sqlcode := SQLCODE;
l_sqlerrm := SUBSTR (SQLERRM, 1, 1000);
END;
IF l_return_status = 'Y'
THEN
BEGIN
UPDATE xxbr_ap_supp_site_cv_stg
SET process_flag = 'V',
record_status = 'Validated'
WHERE pk_supplier_site = pick_data_sites.pk_supplier_site;
EXCEPTION
WHEN OTHERS
THEN
l_sqlcode := SQLCODE;
l_sqlerrm := SUBSTR (SQLERRM, 1, 1000);
END;
ELSE
BEGIN
l_final_error_msg := 'Y';
UPDATE xxbr_ap_supp_site_cv_stg
SET error_message = l_i_err_msg,
process_flag = 'E',
record_status = 'Failure',
ERROR_CODE = lv_error_code
WHERE pk_supplier_site = pick_data_sites.pk_supplier_site;
EXCEPTION
WHEN OTHERS
THEN
l_sqlcode := SQLCODE;
l_sqlerrm := SUBSTR (SQLERRM, 1, 1000);
END;
END IF;
/*+--------------------------------------------------------------------------------------+
-- ========= End of Validation for Payment Currency ======================
+--------------------------------------------------------------------------------------+ */
/*+--------------------------------------------------------------------------------------+
-------------------Start of Validation for Terms Data Basis PR10-----------------------------
+--------------------------------------------------------------------------------------+*/
BEGIN
IF pick_data_sites.terms_date_basis IS NOT NULL
THEN
SELECT lookup_code
INTO lv_terms_date_basis
FROM fnd_lookup_values
WHERE lookup_type = 'TERMS DATE BASIS'
AND lookup_code = pick_data_sites.terms_date_basis;
END IF;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
l_return_status := 'E';
l_i_err_msg :=
l_i_err_msg
|| ''
|| 'Terms Data Basis '
|| pick_data_sites.terms_date_basis
|| ' Not Valid for '
|| pick_data_sites.vendor_name;
lv_error_code := lv_error_code || '' || 'Error-PR10';
WHEN OTHERS
THEN
l_return_status := 'E';
l_sqlcode := SQLCODE;
l_sqlerrm := SUBSTR (SQLERRM, 1, 1000);
END;
IF l_return_status = 'Y'
THEN
BEGIN
UPDATE xxbr_ap_supp_site_cv_stg
SET process_flag = 'V',
record_status = 'Validated'
WHERE pk_supplier_site = pick_data_sites.pk_supplier_site;
EXCEPTION
WHEN OTHERS
THEN
l_sqlcode := SQLCODE;
l_sqlerrm := SUBSTR (SQLERRM, 1, 1000);
END;
ELSE
BEGIN
l_final_error_msg := 'Y';
UPDATE xxbr_ap_supp_site_cv_stg
SET error_message = l_i_err_msg,
process_flag = 'E',
record_status = 'Failure',
ERROR_CODE = lv_error_code
WHERE pk_supplier_site = pick_data_sites.pk_supplier_site;
EXCEPTION
WHEN OTHERS
THEN
l_sqlcode := SQLCODE;
l_sqlerrm := SUBSTR (SQLERRM, 1, 1000);
END;
END IF;
/*+--------------------------------------------------------------------------------------+
-- ================== End of Validation for Terms Data Basis ===============
+--------------------------------------------------------------------------------------+ */
/*+--------------------------------------------------------------------------------------+
------------------- Start of Validation for Payment Terms PR08---------------
+--------------------------------------------------------------------------------------+*/
BEGIN
IF pick_data_sites.terms_name IS NOT NULL
THEN
SELECT NAME, term_id
INTO lv_terms_name, ln_term_id
FROM apps.ap_terms_tl
WHERE UPPER (NAME) = UPPER (pick_data_sites.terms_name)
AND LANGUAGE = USERENV ('LANG');
END IF;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
l_return_status := 'E';
l_i_err_msg :=
l_i_err_msg
|| ''
|| 'Payment Term '
|| pick_data_sites.terms_name
|| ' Not Valid for '
|| pick_data_sites.vendor_name;
lv_error_code := lv_error_code || '' || 'Error-PR08';
WHEN OTHERS
THEN
l_return_status := 'E';
l_sqlcode := SQLCODE;
l_sqlerrm := SUBSTR (SQLERRM, 1, 1000);
END;
IF l_return_status = 'Y'
THEN
BEGIN
UPDATE xxbr_ap_supp_site_cv_stg
SET process_flag = 'V',
record_status = 'Validated',
term_id = ln_term_id
WHERE pk_supplier_site = pick_data_sites.pk_supplier_site;
EXCEPTION
WHEN OTHERS
THEN
l_sqlcode := SQLCODE;
l_sqlerrm := SUBSTR (SQLERRM, 1, 1000);
END;
ELSE
BEGIN
l_final_error_msg := 'Y';
UPDATE xxbr_ap_supp_site_cv_stg
SET error_message = l_i_err_msg,
process_flag = 'E',
record_status = 'Failure',
ERROR_CODE = lv_error_code
WHERE pk_supplier_site = pick_data_sites.pk_supplier_site;
EXCEPTION
WHEN OTHERS
THEN
l_sqlcode := SQLCODE;
l_sqlerrm := SUBSTR (SQLERRM, 1, 1000);
END;
END IF;
/*+--------------------------------------------------+
-- ==== End of Validation for Payment Terms ============
+------------------------------------------------------+ */
/*+-----------------------------------------------------+
-- ========= Validation for Supplier site PR07 ===========
+--------------------------------------------------------+ */
BEGIN
IF ln_vendor_id IS NOT NULL
THEN
SELECT COUNT (*)
INTO lv_dup_site
FROM apps.ap_supplier_sites_all
WHERE TRIM (UPPER (vendor_site_code)) =
TRIM (UPPER (pick_data_sites.vendor_site_code))
AND vendor_id = ln_vendor_id
AND org_id = l_org_id;
END IF;
IF lv_dup_site > 0
THEN
l_return_status := 'E';
l_i_err_msg :=
l_i_err_msg
|| ''
|| 'Vendor Site '
|| pick_data_sites.vendor_site_code
|| ' already exists for '
|| pick_data_sites.vendor_name;
lv_error_code := lv_error_code || '' || 'Error-PR07';
END IF;
EXCEPTION
WHEN OTHERS
THEN
l_return_status := 'E';
l_sqlcode := SQLCODE;
l_sqlerrm := SUBSTR (SQLERRM, 1, 1000);
END;
IF l_return_status = 'Y'
THEN
BEGIN
UPDATE xxbr_ap_supp_site_cv_stg
SET process_flag = 'V',
record_status = 'Validated'
WHERE pk_supplier_site = pick_data_sites.pk_supplier_site;
EXCEPTION
WHEN OTHERS
THEN
l_sqlcode := SQLCODE;
l_sqlerrm := SUBSTR (SQLERRM, 1, 1000);
END;
ELSE
BEGIN
l_final_error_msg := 'Y';
UPDATE xxbr_ap_supp_site_cv_stg
SET error_message = l_i_err_msg,
process_flag = 'E',
record_status = 'Failure',
ERROR_CODE = lv_error_code
WHERE pk_supplier_site = pick_data_sites.pk_supplier_site;
EXCEPTION
WHEN OTHERS
THEN
l_sqlcode := SQLCODE;
l_sqlerrm := SUBSTR (SQLERRM, 1, 1000);
END;
END IF;
-----------------------------------------------------------------------------------------------------------------
IF l_final_error_msg IS NULL
THEN
BEGIN
UPDATE xxbr_ap_supp_site_cv_stg
SET process_flag = 'V',
record_status = 'Validated'
WHERE pk_supplier_site = pick_data_sites.pk_supplier_site;
EXCEPTION
WHEN OTHERS
THEN
l_sqlcode := SQLCODE;
l_sqlerrm := SUBSTR (SQLERRM, 1, 1000);
END;
ELSE
BEGIN
UPDATE xxbr_ap_supp_site_cv_stg
SET -- error_message = l_i_err_msg
process_flag = 'E',
record_status = 'Failure'
-- ,error_code = 'VE'
WHERE pk_supplier_site = pick_data_sites.pk_supplier_site;
EXCEPTION
WHEN OTHERS
THEN
l_sqlcode := SQLCODE;
l_sqlerrm := SUBSTR (SQLERRM, 1, 1000);
END;
END IF;
-----------------------------------------------------------------------------------------------------------------
/*+--------------------------------------------------------------------------------------+
-- ============= End of Validation for Supplier site ======================
+--------------------------------------------------------------------------------------+ */
END LOOP;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line ('NO Data Found');
COMMIT;
END xxbr_ap_supp_site_cv_validate;
/*+--------------------------------------------------------------------------------------+
-- =========== End of Validation for Payment Terms ======================
+--------------------------------------------------------------------------------------+ */
--//===============================================================
--//This Procedure insert into Supplier Sites interface tables
--//===============================================================
PROCEDURE xxbr_ap_supp_site_cv_load
IS
CURSOR ap_supplier_details_cur
IS
SELECT *
FROM xxbr_ap_supp_site_cv_stg
WHERE process_flag = 'V';
-- Variable Declaration
l_sqlerrm VARCHAR2 (1000);
l_i_err_msg VARCHAR2 (1000);
ln_sup_int_id NUMBER;
ln_sup_site_int_id NUMBER;
ln_user_id NUMBER
:= TO_NUMBER (apps.fnd_profile.VALUE ('USER_ID'));
ld_sysdate DATE := SYSDATE;
--ln_resp_id := TO_NUMBER(apps.fnd_profile.value('RESP_ID'));
--ln_resp_appl_id :=TO_NUMBER(apps.fnd_profile.value('RESP_APPL_ID'));
ln_iby_temp_ext NUMBER;
BEGIN
FOR load_data IN ap_supplier_details_cur
LOOP
BEGIN
l_sqlerrm := NULL;
SELECT apps.ap_supplier_sites_int_s.NEXTVAL
INTO ln_sup_site_int_id
FROM DUAL;
INSERT INTO apps.ap_supplier_sites_int
(status,
--vendor_interface_id,
vendor_id, vendor_site_interface_id,
vendor_site_code, address_line1,
address_line2, address_line3,
address_line4, city,
state, country, zip,
org_id, ship_to_location_code,
bill_to_location_code,
retainage_rate,
gapless_inv_num_flag,
invoice_currency_code,
payment_currency_code,
tolerance_name, match_option,
terms_name, terms_date_basis,
pay_site_flag,
primary_pay_site_flag, last_update_date,
last_updated_by, creation_date, created_by
)
VALUES ('NEW',
--10001,
load_data.vendor_id, ln_sup_site_int_id,
load_data.vendor_site_code, load_data.address_line1,
load_data.address_line2, load_data.address_line3,
load_data.address_line4, load_data.city,
load_data.county, load_data.country, load_data.zip,
load_data.org_id, load_data.ship_to_location_code,
load_data.bill_to_location_code,
load_data.retainage_rate,
load_data.gapless_inv_number_flag,
load_data.invoice_currency,
load_data.payment_currency,
load_data.tolerance_name, load_data.match_option,
load_data.terms_name, load_data.terms_date_basis,
load_data.pay_site_flag,
load_data.primary_pay_site_flag, ld_sysdate,
1215, ld_sysdate, 1215
);
/*+--------------------------------------------------------------------------------------+
--============ Inserting records into Supplier Site Bank Interface Table ==========
+--------------------------------------------------------------------------------------+ */
SELECT iby_temp_ext_bank_accts_s.NEXTVAL
INTO ln_iby_temp_ext
FROM DUAL;
INSERT INTO iby_temp_ext_bank_accts
(calling_app_unique_ref2, country_code, bank_id,
branch_id, status, temp_ext_bank_acct_id,
last_update_date, last_updated_by, creation_date,
created_by, object_version_number,
bank_account_name,
bank_account_num, currency_code
) --,ext_payee_id, account_owner_party_id)
VALUES (ln_sup_site_int_id, 'GB', load_data.bank_id,
load_data.branch_id, 'NEW', ln_iby_temp_ext,
SYSDATE, 1215, SYSDATE,
1215, 1,
load_data.bank_account_name,
load_data.bank_account_number, 'GBP'
);
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line ('Error Occured' || SQLERRM);
l_sqlerrm := SQLERRM;
--Update staging table with errors
UPDATE xxbr_ap_supp_site_cv_stg
SET process_flag = 'E',
record_status = 'Error Inserting Iface table',
error_message = l_sqlerrm
WHERE pk_supplier_site = load_data.pk_supplier_site;
END;
END LOOP;
COMMIT;
END xxbr_ap_supp_site_cv_load;
--//==================================================================
--// This Procedure call Supplier Sites Open Interface Import Program
--//==================================================================
PROCEDURE xxbr_ap_supp_site_cv_import
IS
CURSOR ap_supplier_details_cur
IS
SELECT *
FROM xxbr_ap_supp_site_cv_stg
WHERE process_flag = 'V';
----- Variable Declaration
l_request_id NUMBER;
ln_user_id NUMBER;
ln_resp_id NUMBER;
ln_resp_appl_id NUMBER;
x_req_return_status BOOLEAN;
x_req_phase VARCHAR2 (30);
x_req_status VARCHAR2 (30);
x_req_dev_phase VARCHAR2 (30);
x_req_dev_status VARCHAR2 (30);
x_req_message VARCHAR2 (50);
l_sqlerrm VARCHAR2 (2000);
lv_vendor_site_id NUMBER;
lv_int_reject_lookup_code VARCHAR2 (100);
lv_int_record_count NUMBER;
ln_org_id NUMBER;
BEGIN
ln_user_id := TO_NUMBER (apps.fnd_profile.VALUE ('USER_ID'));
ln_resp_id := TO_NUMBER (apps.fnd_profile.VALUE ('RESP_ID'));
ln_resp_appl_id := TO_NUMBER (apps.fnd_profile.VALUE ('RESP_APPL_ID'));
apps.fnd_global.apps_initialize (1215, 20640, 200);
--apps.fnd_global.apps_initialize(1318,50554,200);
--apps.fnd_global.apps_initialize(ln_user_id,ln_resp_id,ln_resp_appl_id);
--mo_global.init('AP');
COMMIT;
---------------------- Getting the Operating Unit while submitting the request for Supplier --------
BEGIN
SELECT DISTINCT org_id
INTO ln_org_id
FROM xxbr_ap_supp_site_cv_stg
WHERE ROWNUM = 1 AND org_id IS NOT NULL;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line ('Error Occured in getting org id' || SQLERRM
);
-- l_sqlerrm:=SQLERRM;
--Update staging table with errors
END;
fnd_request.set_org_id (org_id => ln_org_id);
COMMIT;
l_request_id :=
fnd_request.submit_request
(application => 'SQLAP',
program => 'APXSSIMP',
description => 'Supplier Sites Open Interface Import',
start_time => SYSDATE,
sub_request => FALSE,
argument1 => 'NEW',
argument2 => 10000,
argument3 => 'N',
argument4 => 'N',
argument5 => 'N'
);
DBMS_OUTPUT.put_line (l_request_id);
COMMIT;
IF l_request_id = 0
THEN
DBMS_OUTPUT.put_line ('Request not submitted');
ELSE
x_req_return_status :=
fnd_concurrent.wait_for_request (l_request_id,
20,
0,
x_req_phase,
x_req_status,
x_req_dev_phase,
x_req_dev_status,
x_req_message
);
END IF;
COMMIT;
IF x_req_return_status = TRUE
THEN
DBMS_OUTPUT.put_line ('Request completed successfully');
--==============================================================
--- Checking if there are any records in the interface table with the status 'NEW'
BEGIN
SELECT COUNT (vendor_interface_id)
INTO lv_int_record_count
FROM ap_supplier_sites_int
WHERE status = 'NEW';
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line ('Error Occured' || SQLERRM);
l_sqlerrm := SQLERRM;
END;
IF lv_int_record_count > 0
THEN
l_request_id :=
apps.fnd_request.submit_request ('SQLAP',
'APXSSIMP',
'',
'',
FALSE,
'ALL',
'1000',
'N',
'N',
'N'
);
DBMS_OUTPUT.put_line (l_request_id);
DBMS_OUTPUT.put_line (l_request_id);
COMMIT;
END IF;
IF l_request_id = 0
THEN
DBMS_OUTPUT.put_line ('Request not submitted');
ELSE
x_req_return_status :=
fnd_concurrent.wait_for_request (l_request_id,
20,
0,
x_req_phase,
x_req_status,
x_req_dev_phase,
x_req_dev_status,
x_req_message
);
END IF;
COMMIT;
IF x_req_return_status = TRUE
THEN
DBMS_OUTPUT.put_line ('Request completed successfully');
--===============================================================
COMMIT;
BEGIN
FOR load_data IN ap_supplier_details_cur
LOOP
BEGIN
l_sqlerrm := NULL;
BEGIN
SELECT vendor_site_id
INTO lv_vendor_site_id
FROM ap_supplier_sites_all
WHERE vendor_id =
(SELECT vendor_id
FROM ap_suppliers
WHERE vendor_name = load_data.vendor_name)
AND vendor_site_code = load_data.vendor_site_code
AND org_id = load_data.org_id;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line
( 'Error Occured in checking if site exists in system'
|| SQLERRM
);
l_sqlerrm := SQLERRM;
END;
DBMS_OUTPUT.put_line (load_data.vendor_name);
DBMS_OUTPUT.put_line (load_data.vendor_site_code);
DBMS_OUTPUT.put_line (lv_vendor_site_id);
IF lv_vendor_site_id IS NOT NULL
THEN
UPDATE xxbr_ap_supp_site_cv_stg
SET process_flag = 'P',
record_status = 'Successfully Processed'
WHERE pk_supplier_site = load_data.pk_supplier_site;
ELSE
BEGIN
SELECT rej.reject_lookup_code
INTO lv_int_reject_lookup_code
FROM ap_supplier_int_rejections rej,
ap_supplier_sites_int INT
WHERE rej.parent_id = INT.vendor_site_interface_id
AND parent_table = 'AP_SUPPLIER_SITES_INT'
AND INT.vendor_id =
(SELECT DISTINCT vendor_id
FROM ap_suppliers
WHERE vendor_name =
load_data.vendor_name)
AND INT.vendor_site_code =
load_data.vendor_site_code
AND INT.status NOT IN ('NEW');
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line ('Error Occured' || SQLERRM
);
l_sqlerrm := SQLERRM;
END;
IF lv_int_reject_lookup_code IS NOT NULL
THEN
UPDATE xxbr_ap_supp_site_cv_stg
SET process_flag = 'E',
ERROR_CODE = 'VE',
record_status = 'Error',
error_message = lv_int_reject_lookup_code
WHERE pk_supplier_site =
load_data.pk_supplier_site;
ELSE
UPDATE xxbr_ap_supp_site_cv_stg
SET process_flag = 'E',
record_status = 'Error',
ERROR_CODE = 'VE'
-- , error_message = l_sqlerrm
WHERE pk_supplier_site =
load_data.pk_supplier_site;
END IF;
END IF;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line ('Error Occured' || SQLERRM);
l_sqlerrm := SQLERRM;
--Update staging table with errors
UPDATE xxbr_ap_supp_site_cv_stg
SET process_flag = 'E',
ERROR_CODE = 'VE',
record_status = 'Error Update Staging tbl',
error_message = l_sqlerrm
WHERE pk_supplier_site = load_data.pk_supplier_site;
END;
END LOOP;
END;
COMMIT;
END IF;
END IF;
END xxbr_ap_supp_site_cv_import;
--***************************************************************************
------------------------ SUPPLIER CONTACTS ----------------------------------------
--***************************************************************************
PROCEDURE xxbr_ap_supp_cont_cv_main (p_action IN VARCHAR2)
IS
BEGIN
-- Call validate procedure to validate the Supplier Sites information
IF p_action = 'validate'
THEN
xxbr_ap_supp_cont_cv_validate;
ELSE
NULL;
END IF;
-- Call Insert procedure to load the data into Supplier Sites interface table
IF p_action = 'load'
THEN
xxbr_ap_supp_cont_cv_load;
ELSE
NULL;
END IF;
END xxbr_ap_supp_cont_cv_main;
--//==========================================================
--// This is validation Procedure for Supplier Contacts Information
--//==========================================================
PROCEDURE xxbr_ap_supp_cont_cv_validate
IS
--Variable declaration
l_i_accepted NUMBER := 0;
l_i_rejected NUMBER := 0;
l_ic_accepted NUMBER := 0;
l_ic_rejected NUMBER := 0;
l_sqlcode NUMBER;
l_sqlerrm VARCHAR2 (1000);
l_return_status VARCHAR2 (1);
l_i_err_msg VARCHAR2 (1000);
l_count NUMBER := 0;
ln_vendor_id NUMBER;
lv_vendor_site_id NUMBER;
lv_operating_unit_name VARCHAR2 (100);
l_org_id NUMBER;
lv_err_msg VARCHAR2 (1000);
l_final_error_msg VARCHAR2 (100);
lv_vendor_contact_id NUMBER;
CURSOR ap_supp_contacts_cur
IS
SELECT *
FROM xxbr_ap_supp_cont_cv_stg
WHERE process_flag = 'N' AND rec_type IN ('D', 'd');
supplier_contacts_tab xxbr_ap_supp_cont_cv_stg%ROWTYPE;
BEGIN
FOR pick_data_contacts IN ap_supp_contacts_cur
LOOP
l_return_status := 'Y';
l_i_err_msg := NULL;
l_final_error_msg := NULL;
/*+--------------------------------------------------------------------------------------+
-- ================== Validation for Operating Unit exists or not ===========
+--------------------------------------------------------------------------------------+ */
BEGIN
SELECT NAME, organization_id
INTO lv_operating_unit_name, l_org_id
FROM apps.hr_operating_units
WHERE NAME = (TRIM (pick_data_contacts.operating_unit_name));
EXCEPTION
WHEN NO_DATA_FOUND
THEN
l_return_status := 'E';
l_i_err_msg :=
l_i_err_msg
|| ' | '
|| pick_data_contacts.operating_unit_name
|| ' Operating Unit does not exist.Please check Operating Unit Name';
WHEN OTHERS
THEN
l_return_status := 'E';
l_sqlcode := SQLCODE;
l_sqlerrm := SUBSTR (SQLERRM, 1, 1000);
END;
IF l_return_status = 'Y'
THEN
BEGIN
UPDATE xxbr_ap_supp_cont_cv_stg
SET process_flag = 'V',
record_status = 'Validated',
org_id = l_org_id
WHERE pk_supplier_cont_number =
pick_data_contacts.pk_supplier_cont_number;
EXCEPTION
WHEN OTHERS
THEN
l_sqlcode := SQLCODE;
l_sqlerrm := SUBSTR (SQLERRM, 1, 1000);
END;
ELSE
BEGIN
l_final_error_msg := 'Y';
UPDATE xxbr_ap_supp_cont_cv_stg
SET error_message = l_i_err_msg,
process_flag = 'E',
record_status = 'Failure',
ERROR_CODE = 'VE'
WHERE pk_supplier_cont_number =
pick_data_contacts.pk_supplier_cont_number;
EXCEPTION
WHEN OTHERS
THEN
l_sqlcode := SQLCODE;
l_sqlerrm := SUBSTR (SQLERRM, 1, 1000);
END;
END IF;
/*+--------------------------------------------------------------------------------------+
--============= End of Validation for Operating Unit exists or not ========
+--------------------------------------------------------------------------------------+ */
/*+--------------------------------------------------------------------------------------+
-- ================== Validation for Supplier exists or not ================
+--------------------------------------------------------------------------------------+ */
BEGIN
SELECT vendor_id
INTO ln_vendor_id
FROM apps.ap_suppliers
WHERE (UPPER (vendor_name) IN TRIM
(UPPER
(pick_data_contacts.vendor_name
)
)
);
EXCEPTION
WHEN NO_DATA_FOUND
THEN
l_return_status := 'E';
l_i_err_msg :=
l_i_err_msg
|| ' | '
|| pick_data_contacts.vendor_name
|| ' Vendor does not exist.Please create supplier first before creating Supplier Contact';
WHEN OTHERS
THEN
l_return_status := 'E';
l_sqlcode := SQLCODE;
l_sqlerrm := SUBSTR (SQLERRM, 1, 1000);
END;
IF l_return_status = 'Y'
THEN
BEGIN
UPDATE xxbr_ap_supp_cont_cv_stg
SET process_flag = 'V',
record_status = 'Validated',
vendor_id = ln_vendor_id
WHERE pk_supplier_cont_number =
pick_data_contacts.pk_supplier_cont_number;
EXCEPTION
WHEN OTHERS
THEN
l_sqlcode := SQLCODE;
l_sqlerrm := SUBSTR (SQLERRM, 1, 1000);
END;
ELSE
BEGIN
l_final_error_msg := 'Y';
UPDATE xxbr_ap_supp_cont_cv_stg
SET error_message = l_i_err_msg,
process_flag = 'E',
record_status = 'Failure',
ERROR_CODE = 'VE'
WHERE pk_supplier_cont_number =
pick_data_contacts.pk_supplier_cont_number;
EXCEPTION
WHEN OTHERS
THEN
l_sqlcode := SQLCODE;
l_sqlerrm := SUBSTR (SQLERRM, 1, 1000);
END;
END IF;
/*+--------------------------------------------------------------------------------------+
-- ============ End of Validation for Supplier exists or not ================
+--------------------------------------------------------------------------------------+ */
/*+--------------------------------------------------------------------------------------+
-- ================== Validation for Supplier site ======================
+--------------------------------------------------------------------------------------+ */
BEGIN
IF ln_vendor_id IS NOT NULL
THEN
SELECT vendor_site_id
INTO lv_vendor_site_id
FROM apps.ap_supplier_sites_all
WHERE TRIM (UPPER (vendor_site_code)) =
TRIM (UPPER (pick_data_contacts.vendor_site_code))
AND vendor_id = ln_vendor_id
AND org_id = l_org_id;
END IF;
EXCEPTION
WHEN OTHERS
THEN
l_return_status := 'E';
l_i_err_msg :=
l_i_err_msg
|| ' | '
|| 'Vendor Site '
|| pick_data_contacts.vendor_site_code
|| ' does not exist for '
|| pick_data_contacts.vendor_name;
END;
IF l_return_status = 'Y'
THEN
BEGIN
UPDATE xxbr_ap_supp_cont_cv_stg
SET process_flag = 'V',
record_status = 'Validated',
vendor_site_id = lv_vendor_site_id
WHERE pk_supplier_cont_number =
pick_data_contacts.pk_supplier_cont_number;
EXCEPTION
WHEN OTHERS
THEN
l_sqlcode := SQLCODE;
l_sqlerrm := SUBSTR (SQLERRM, 1, 1000);
END;
ELSE
BEGIN
l_final_error_msg := 'Y';
UPDATE xxbr_ap_supp_cont_cv_stg
SET error_message = l_i_err_msg,
process_flag = 'E',
record_status = 'Failure',
ERROR_CODE = 'VE'
WHERE pk_supplier_cont_number =
pick_data_contacts.pk_supplier_cont_number;
EXCEPTION
WHEN OTHERS
THEN
l_sqlcode := SQLCODE;
l_sqlerrm := SUBSTR (SQLERRM, 1, 1000);
END;
END IF;
/*+--------------------------------------------------------------------------------------+
-- ================ End of Validation for Supplier site ==================
+--------------------------------------------------------------------------------------+ */
/*+--------------------------------------------------------------------------------------+
-- === Validation for combination of first_name,last_name,phone,email_address =====
+--------------------------------------------------------------------------------------+ */
/*
BEGIN
IF pick_data_contacts.last_name IS NOT NULL
THEN
SELECT party_id
INTO lv_vendor_contact_id
from
(select hp.party_id
, hp.person_first_name
, hp.person_last_name
, hcpe.email_address
, hcpp.phone_number primary_phone_number
from hz_parties hp
, fnd_user fu
, hz_relationships hzr
, hz_parties hzr_hp
, hz_party_usg_assignments hpua
, HZ_CONTACT_POINTS hcpp
, HZ_CONTACT_POINTS hcpe
where hp.party_id = hzr.subject_id
and hzr.object_id = ln_vendor_id --8110-- :1 -- party of supplier
and hzr.relationship_type = 'CONTACT'
and hzr.relationship_code = 'CONTACT_OF'
and hzr.subject_type ='PERSON'
and hzr.object_type = 'ORGANIZATION'
-- commenting for Bug 6076854 --
and (hzr.end_date is null or hzr.end_date > sysdate)
and hzr.status = 'A'
and hzr_hp.party_id = hzr.party_id
and fu.person_party_id (+) = hp.party_id
and hp.party_id not in ( select contact_party_id
from pos_contact_requests pcr, pos_supplier_mappings psm
where pcr.request_status='PENDING'
and psm.mapping_id = pcr.mapping_id
and psm.PARTY_ID = ln_vendor_id--8110--:2
and contact_party_id is not null )
and hpua.party_id = hp.party_id
and hpua.status_flag = 'A'
and hpua.party_usage_code = 'SUPPLIER_CONTACT'
And hcpp.OWNER_TABLE_NAME(+) = 'HZ_PARTIES'
And hcpp.OWNER_TABLE_ID(+) = hzr.PARTY_ID
And hcpp.PHONE_LINE_TYPE(+) = 'GEN'
And hcpp.CONTACT_POINT_TYPE(+) = 'PHONE'
-- BUG 8275853,8517744 AND hcpp.primary_flag(+)='Y' -- BUG 8275853,8517744
And hcpe.OWNER_TABLE_NAME(+) = 'HZ_PARTIES'
and hcpe.OWNER_TABLE_ID(+) = hzr.PARTY_ID
And hcpe.CONTACT_POINT_TYPE(+) = 'EMAIL'
-- BUG 8275853,8517744 AND hcpe.primary_flag(+)='Y' -- BUG 8275853,8517744
And hcpe.status (+)='A' And hcpp.status (+)='A' -- commenting for Bug 6076854 --
and (hpua.effective_end_date is null OR hpua.effective_end_date > sysdate))
WHERE NVL(person_first_name,'-')||NVL(person_last_name,'-')||NVL(email_address,'-')||NVL(primary_phone_number,'-')
= NVL(pick_data_contacts.first_name,'-')||NVL(pick_data_contacts.last_name,'-')||NVL(pick_data_contacts.email_address,'-')||NVL(pick_data_contacts.phone,'-') ;
END IF;
dbms_output.put_line(lv_vendor_contact_id);
dbms_output.put_line(pick_data_contacts.first_name);
dbms_output.put_line(pick_data_contacts.last_name);
dbms_output.put_line(pick_data_contacts.email_address);
dbms_output.put_line(pick_data_contacts.phone);
EXCEPTION
WHEN NO_DATA_FOUND
THEN
l_return_status := 'E';
l_i_err_msg := l_i_err_msg || ' | ' || 'Incorrect Supplier Site Contact Info';
WHEN OTHERS THEN
l_return_status := 'E';
l_i_err_msg :=SUBSTR(SQLERRM,1,1000);
END;
IF l_return_status='Y' THEN
BEGIN
UPDATE XXBR_AP_SUPP_CONT_CV_STG
SET process_flag = 'V'
,record_status = 'Validated'
,vendor_site_id = lv_vendor_site_id
WHERE PK_SUPPLIER_CONT_NUMBER = pick_data_contacts.PK_SUPPLIER_CONT_NUMBER;
EXCEPTION
WHEN OTHERS THEN
l_sqlcode :=SQLCODE;
l_sqlerrm :=SUBSTR(SQLERRM,1,1000);
END;
ELSE
BEGIN
l_final_error_msg:='Y';
UPDATE XXBR_AP_SUPP_CONT_CV_STG
SET error_message = l_i_err_msg
,process_flag = 'E'
,record_status = 'Failure'
,error_code = 'VE'
WHERE PK_SUPPLIER_CONT_NUMBER = pick_data_contacts.PK_SUPPLIER_CONT_NUMBER;
EXCEPTION
WHEN OTHERS THEN
l_sqlcode :=SQLCODE;
l_sqlerrm :=SUBSTR(SQLERRM,1,1000);
END;
END IF;
*/
/*+--------------------------------------------------------------------------------------+
-- =========== End of Validation for Validation for combination of first_name,last_name,phone,email_address =================
+--------------------------------------------------------------------------------------+ */
/*+--------------------------------------------------------------------------------------+
-- ======= Validation for Supplier Contact Last Name ========
+--------------------------------------------------------------------------------------+ */
BEGIN
IF pick_data_contacts.last_name IS NOT NULL
THEN
BEGIN
UPDATE xxbr_ap_supp_cont_cv_stg
SET process_flag = 'V',
record_status = 'Validated',
vendor_site_id = lv_vendor_site_id
WHERE pk_supplier_cont_number =
pick_data_contacts.pk_supplier_cont_number;
EXCEPTION
WHEN OTHERS
THEN
l_sqlcode := SQLCODE;
l_sqlerrm := SUBSTR (SQLERRM, 1, 1000);
END;
ELSE
l_i_err_msg :=
l_i_err_msg
|| ' | '
|| 'Last Name can not be null for Vendor Contact for Vendor Site '
|| pick_data_contacts.vendor_site_code
|| ' for Vendor name '
|| pick_data_contacts.vendor_name;
BEGIN
l_final_error_msg := 'Y';
UPDATE xxbr_ap_supp_cont_cv_stg
SET error_message = l_i_err_msg,
process_flag = 'E',
record_status = 'Failure',
ERROR_CODE = 'VE'
WHERE pk_supplier_cont_number =
pick_data_contacts.pk_supplier_cont_number;
EXCEPTION
WHEN OTHERS
THEN
l_sqlcode := SQLCODE;
l_sqlerrm := SUBSTR (SQLERRM, 1, 1000);
END;
END IF;
EXCEPTION
WHEN OTHERS
THEN
l_sqlcode := SQLCODE;
l_sqlerrm := SUBSTR (SQLERRM, 1, 1000);
END;
------------------------------------------------------------------------------------
IF l_final_error_msg IS NULL
THEN
BEGIN
UPDATE xxbr_ap_supp_cont_cv_stg
SET process_flag = 'V',
record_status = 'Validated'
-- ,vendor_site_id = lv_vendor_site_id
WHERE pk_supplier_cont_number =
pick_data_contacts.pk_supplier_cont_number;
EXCEPTION
WHEN OTHERS
THEN
l_sqlcode := SQLCODE;
l_sqlerrm := SUBSTR (SQLERRM, 1, 1000);
END;
ELSE
BEGIN
UPDATE xxbr_ap_supp_cont_cv_stg
SET -- error_message = l_i_err_msg
process_flag = 'E',
record_status = 'Failure'
-- ,error_code = 'VE'
WHERE pk_supplier_cont_number =
pick_data_contacts.pk_supplier_cont_number;
EXCEPTION
WHEN OTHERS
THEN
l_sqlcode := SQLCODE;
l_sqlerrm := SUBSTR (SQLERRM, 1, 1000);
END;
END IF;
------------------------------------------------------------------------------------
/*+--------------------------------------------------------------------------------------+
-- ======== End of Validation for Supplier Contact Last Name ===============
+--------------------------------------------------------------------------------------+ */
END LOOP;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line ('NO Data Found');
COMMIT;
END xxbr_ap_supp_cont_cv_validate;
--//=================================================================
--// This Procedure insert into Supplier Contacts interface tables
--//=================================================================
PROCEDURE xxbr_ap_supp_cont_cv_load
IS
CURSOR ap_supplier_details_cur
IS
SELECT *
FROM xxbr_ap_supp_cont_cv_stg
WHERE process_flag = 'V';
-- Variable Declaration
l_sqlerrm VARCHAR2 (1000);
l_i_err_msg VARCHAR2 (1000);
ln_sup_int_id NUMBER;
ln_sup_cont_int_id NUMBER;
ln_user_id NUMBER
:= TO_NUMBER (apps.fnd_profile.VALUE ('USER_ID'));
ld_sysdate DATE := SYSDATE;
--ln_resp_id := TO_NUMBER(apps.fnd_profile.value('RESP_ID'));
--ln_resp_appl_id :=TO_NUMBER(apps.fnd_profile.value('RESP_APPL_ID'));
BEGIN
FOR load_data IN ap_supplier_details_cur
LOOP
BEGIN
l_sqlerrm := NULL;
SELECT apps.ap_sup_site_contact_int_s.NEXTVAL
INTO ln_sup_cont_int_id
FROM DUAL;
INSERT INTO apps.ap_sup_site_contact_int
(vendor_site_id, vendor_id,
org_id, vendor_contact_interface_id,
first_name,
first_name_alt, last_name,
contact_name_alt, prefix,
area_code,
alt_area_code,
phone, alt_phone,
fax_area_code,
fax, email_address,
mail_stop, status, last_update_date,
last_updated_by, creation_date, created_by
)
VALUES (load_data.vendor_site_id, load_data.vendor_id,
load_data.org_id, ln_sup_cont_int_id,
load_data.first_name,
load_data.first_name_alternate, load_data.last_name,
load_data.contact_name_alternate, load_data.prefix,
TRIM (load_data.area_code),
TRIM (load_data.alt_area_code),
TRIM (load_data.phone), TRIM (load_data.alt_phone),
TRIM (load_data.fax_area_code),
TRIM (load_data.fax), load_data.email_address,
load_data.mail_stop, 'NEW', ld_sysdate,
ln_user_id, ld_sysdate, ln_user_id
);
COMMIT;
BEGIN
UPDATE xxbr_ap_supp_cont_cv_stg
SET process_flag = 'P',
record_status = 'Successfully Processed'
WHERE pk_supplier_cont_number =
load_data.pk_supplier_cont_number;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line ('Error Occured' || SQLERRM);
l_sqlerrm := SQLERRM;
--Update staging table with errors
UPDATE xxbr_ap_supp_cont_cv_stg
SET process_flag = 'E',
record_status = 'Error Update staging tbl',
error_message = l_sqlerrm
WHERE pk_supplier_cont_number =
load_data.pk_supplier_cont_number;
END;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line ('Error Occured' || SQLERRM);
l_sqlerrm := SQLERRM;
--Update staging table with errors
UPDATE xxbr_ap_supp_cont_cv_stg
SET process_flag = 'E',
record_status = 'Error Inserting Iface table',
error_message = l_sqlerrm
WHERE pk_supplier_cont_number =
load_data.pk_supplier_cont_number;
END;
END LOOP;
COMMIT;
END xxbr_ap_supp_cont_cv_load;
--//======================================================
--//This Procedure call Supplier Sites Open Interface Import Program
--//======================================================
PROCEDURE xxbr_ap_supp_cont_cv_import
IS
----- Variable Declaration
l_request_id NUMBER;
ln_user_id NUMBER;
ln_resp_id NUMBER;
ln_resp_appl_id NUMBER;
ln_org_id NUMBER;
x_req_return_status BOOLEAN;
x_req_phase VARCHAR2 (30);
x_req_status VARCHAR2 (30);
x_req_dev_phase VARCHAR2 (30);
x_req_dev_status VARCHAR2 (30);
x_req_message VARCHAR2 (50);
BEGIN
ln_user_id := TO_NUMBER (apps.fnd_profile.VALUE ('USER_ID'));
ln_resp_id := TO_NUMBER (apps.fnd_profile.VALUE ('RESP_ID'));
ln_resp_appl_id := TO_NUMBER (apps.fnd_profile.VALUE ('RESP_APPL_ID'));
apps.fnd_global.apps_initialize (1215, 20640, 200);
COMMIT;
--apps.fnd_global.apps_initialize(1318,50554,200);
--apps.fnd_global.apps_initialize(ln_user_id,ln_resp_id,ln_resp_appl_id);
--mo_global.init('AP');
/*L_REQUEST_ID := APPS.FND_REQUEST.SUBMIT_REQUEST
('SQLAP',
'APXSCIMP',
'',
'',
FALSE,
'NEW',
'1000',
'N','N','N');
dbms_output.put_line(L_REQUEST_ID);
commit;
*/
------ Getting the Operating Unit while submitting the request for Supplier Site Contacts --------
BEGIN
SELECT DISTINCT org_id
INTO ln_org_id
FROM xxbr_ap_supp_cont_cv_stg
WHERE ROWNUM = 1 AND org_id IS NOT NULL;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line ('Error Occured' || SQLERRM);
-- l_sqlerrm:=SQLERRM;
--Update staging table with errors
END;
fnd_request.set_org_id (org_id => ln_org_id);
COMMIT;
l_request_id :=
fnd_request.submit_request
(application => 'SQLAP',
program => 'APXSCIMP',
description => 'Supplier Site Contacts Open Interface Import',
start_time => SYSDATE,
sub_request => FALSE,
argument1 => 'NEW',
argument2 => 10000,
argument3 => 'N',
argument4 => 'N',
argument5 => 'N'
);
DBMS_OUTPUT.put_line (l_request_id);
COMMIT;
IF l_request_id = 0
THEN
DBMS_OUTPUT.put_line ('Request not submitted');
ELSE
x_req_return_status :=
fnd_concurrent.wait_for_request (l_request_id,
20,
0,
x_req_phase,
x_req_status,
x_req_dev_phase,
x_req_dev_status,
x_req_message
);
END IF;
COMMIT;
IF x_req_return_status = TRUE
THEN
DBMS_OUTPUT.put_line ('Request completed successfully');
END IF;
END xxbr_ap_supp_cont_cv_import;
END xxbr_ap_suppliers_pkg;
No comments:
Post a Comment