Table Script:
DROP TABLE XXSCHEMA.XX_VENDOR_MASTER_DATA CASCADE CONSTRAINTS;
CREATE TABLE XXSCHEMA.XX_VENDOR_MASTER_DATA
(
VENDOR_NAME VARCHAR2(250 BYTE),
VENDOR_ID NUMBER,
VENDOR_SITE_CODE VARCHAR2(15 BYTE),
ORG_ID NUMBER,
LIABILITY_ACCOUNT VARCHAR2(207 BYTE),
NEW_LIABILITY_ACCOUNT VARCHAR2(207 BYTE),
PRE_PAYMENT_ACCOUNT VARCHAR2(207 BYTE),
NEW_PRE_PAYMENT_ACCOUNT VARCHAR2(207 BYTE),
TAX_CLASSIFICATION_CODE VARCHAR2(207 BYTE),
NEW_TAX_CLASSIFICATION_CODE VARCHAR2(30 BYTE),
INVOICE_WITHHOLDING_TAX_GROUP VARCHAR2(25 BYTE),
NEW_INVOICE_WHT_TAX_GROUP VARCHAR2(25 BYTE),
SUPPLIER_TYPE VARCHAR2(80 BYTE),
NEW_SUPPLIER_TYPE VARCHAR2(80 BYTE),
TAX_APPLICABILITY VARCHAR2(80 BYTE),
SUPP_STATUS_FLAG VARCHAR2(1 BYTE),
SUPP_STATUS_MSG VARCHAR2(2000 BYTE),
SUPP_SITE_STATUS_FLAG VARCHAR2(1 BYTE),
SUPP_SITE_STATUS_MSG VARCHAR2(2000 BYTE)
);
DROP SYNONYM APPS.XX_VENDOR_MASTER_DATA;
CREATE OR REPLACE SYNONYM APPS.XX_VENDOR_MASTER_DATA FOR XXSCHEMA.XX_VENDOR_MASTER_DATA;
API Script:
-----------------------------------------------------------------------------------------------
DECLARE
v_api_version NUMBER;
v_init_msg_list VARCHAR2(200);
v_commit VARCHAR2(200);
v_validation_level NUMBER;
x_return_status VARCHAR2(200);
x_msg_count NUMBER;
v_vendor NUMBER;
x_msg_data VARCHAR2(200);
l_vendor_site_rec apps.ap_vendor_pub_pkg.r_vendor_site_rec_type;
l_existing_vendor_site_rec ap_supplier_sites_all%rowtype;
l_vendor_site_id NUMBER;
l_liability_ccid gl_code_combinations_kfv.code_combination_id%TYPE;
l_prepay_ccid gl_code_combinations_kfv.code_combination_id%TYPE;
l_ap_awt_group_id ap_awt_groups.group_id%TYPE;
l_allow_awt_flag ap_suppliers.allow_awt_flag%TYPE;
v_calling_prog VARCHAR2(200);
l_msg VARCHAR2(200);
l_status_flag VARCHAR(1):='N';
l_status_msg VARCHAR2(2000) := NULL;
CURSOR c
IS
SELECT DISTINCT
vendor_id
,vendor_site_code
,new_tax_classification_code
,new_invoice_wht_tax_group
,new_liability_account
,new_pre_payment_account
FROM xx_vendor_master_data
WHERE NVL(supp_status_flag, 'N') = 'S'
AND NVL(supp_site_status_flag, 'N') IN ('N', 'E')
--AND vendor_id = 7216
--AND vendor_id IN (5041)--5059)
;
BEGIN
FOR i IN c
LOOP
l_status_flag := 'N';
l_status_msg := NULL;
l_liability_ccid := NULL;
l_prepay_ccid := NULL;
l_ap_awt_group_id := NULL;
l_allow_awt_flag := 'N';
BEGIN
SELECT vendor_site_id
INTO l_vendor_site_id
FROM ap_supplier_sites_all
WHERE vendor_id = i.vendor_id
AND vendor_site_code = i.vendor_site_code
AND org_id = 101
;
EXCEPTION
WHEN NO_DATA_FOUND THEN
l_vendor_site_id := null;
l_status_flag := 'E';
l_status_msg := 'Unable to find vendor_site_code. ';
WHEN OTHERS THEN
l_vendor_site_id := NULL;
l_status_flag := 'E';
l_status_msg := 'Error while validating vendor_site_code. error message: ' || SQLERRM;
END;
BEGIN
SELECT code_combination_id
INTO l_liability_ccid
FROM gl_code_combinations_kfv
WHERE concatenated_segments = TRIM(i.new_liability_account)
;
EXCEPTION
WHEN NO_DATA_FOUND THEN
l_liability_ccid := NULL;
l_status_flag := 'E';
l_status_msg := l_status_msg||'-Unable to find new_liability_account. ';
WHEN OTHERS THEN
l_liability_ccid := NULL;
l_status_flag := 'E';
l_status_msg := l_status_msg||'-Error while validating new_liability_account. error message: ' || SQLERRM;
END;
BEGIN
SELECT code_combination_id
INTO l_prepay_ccid
FROM gl_code_combinations_kfv
WHERE concatenated_segments = TRIM(i.new_pre_payment_account)
;
EXCEPTION
WHEN NO_DATA_FOUND THEN
l_prepay_ccid := NULL;
l_status_flag := 'E';
l_status_msg := l_status_msg||'-Unable to find new_pre_payment_account. ';
WHEN OTHERS THEN
l_prepay_ccid := NULL;
l_status_flag := 'E';
l_status_msg := l_status_msg||'-Error while validating new_pre_payment_account. error message: ' || SQLERRM;
END;
IF TRIM(i.new_invoice_wht_tax_group) IS NOT NULL THEN
BEGIN
SELECT group_id
INTO l_ap_awt_group_id
FROM ap_awt_groups
WHERE name = TRIM(i.new_invoice_wht_tax_group)
;
l_allow_awt_flag := 'Y';
EXCEPTION
WHEN NO_DATA_FOUND THEN
l_ap_awt_group_id := NULL;
l_status_flag := 'E';
l_status_msg := l_status_msg||'-Unable to find new_invoice_wht_tax_group. ';
WHEN OTHERS THEN
l_ap_awt_group_id := NULL;
l_status_flag := 'E';
l_status_msg := l_status_msg||'-Error while validating new_invoice_wht_tax_group. error message: ' || SQLERRM;
END;
END IF;
dbms_output.put_line('l_status_flag = ' || l_status_flag);
dbms_output.put_line('l_status_msg = ' || l_status_msg);
IF l_status_flag <> 'E' THEN
fnd_global.apps_initialize(0, 50240, 200);
mo_global.init('SQLAP');
mo_global.set_policy_context('S', 101);
v_api_version := 1.0;
v_init_msg_list := fnd_api.g_true;
v_commit := fnd_api.g_true;
v_validation_level := fnd_api.g_valid_level_full;
--l_vendor_site_id := j.vendor_site_id; -- to be end dated
v_calling_prog := 'Supplier Tax code update';
l_vendor_site_rec.vendor_site_id := l_existing_vendor_site_rec.vendor_site_id;
l_vendor_site_rec.last_update_date := sysdate;
--l_vendor_site_rec.last_updated_by := 6147023;
l_vendor_site_rec.vendor_id := l_existing_vendor_site_rec.vendor_id;
l_vendor_site_rec.org_id := l_existing_vendor_site_rec.org_id;
l_vendor_site_rec.accts_pay_code_combination_id := l_liability_ccid;
l_vendor_site_rec.prepay_code_combination_id := l_prepay_ccid;
l_vendor_site_rec.vat_code := i.new_tax_classification_code;
l_vendor_site_rec.awt_group_id := l_ap_awt_group_id;
l_vendor_site_rec.allow_awt_flag := l_allow_awt_flag;
--l_vendor_site_rec.auto_tax_calc_flag := 'y';
--l_vendor_site_rec.vat_registration_num := i.tax_registration_no;
ap_vendor_pub_pkg.update_vendor_site_public(p_api_version => v_api_version
,p_init_msg_list => v_init_msg_list
,p_commit => v_commit
,p_validation_level => v_validation_level
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
,p_vendor_site_rec => l_vendor_site_rec
,p_vendor_site_id => l_vendor_site_id
,p_calling_prog => v_calling_prog
);
dbms_output.put_line('x_return_status = ' || x_return_status);
dbms_output.put_line('x_msg_count = ' || x_msg_count);
dbms_output.put_line('x_msg_data = ' || x_msg_data);
IF (x_return_status <> fnd_api.g_ret_sts_success)
THEN
FOR i1 IN 1 .. fnd_msg_pub.count_msg
LOOP
l_msg := fnd_msg_pub.get(p_msg_index => i1,
p_encoded => fnd_api.g_false);
DBMS_OUTPUT.put_line('The API call failed with error ' || l_msg);
UPDATE xx_vendor_master_data
SET supp_site_status_flag = 'E'
,supp_site_status_msg = l_msg
WHERE vendor_id = i.vendor_id
AND vendor_site_code = i.vendor_site_code;
END LOOP;
ELSE
DBMS_OUTPUT.put_line('The API call ended with SUCESSS status');
UPDATE xx_vendor_master_data
SET supp_site_status_flag = 'S'
,supp_site_status_msg = 'Update Successful'
WHERE vendor_id = i.vendor_id
AND vendor_site_code = i.vendor_site_code
;
END IF;
ELSE
UPDATE xx_vendor_master_data
SET supp_site_status_flag = 'E'
,supp_site_status_msg = l_status_msg
WHERE vendor_id = i.vendor_id
AND vendor_site_code = i.vendor_site_code
;
END IF;
COMMIT;
END LOOP;
END;
No comments:
Post a Comment