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:
-----------------------------------------------------------------------------------------------
--SET serveroutput on;
DECLARE
p_api_version NUMBER;
p_init_msg_list VARCHAR2(200);
p_commit VARCHAR2(200);
p_validation_level NUMBER;
x_return_status VARCHAR2(200);
x_msg_count NUMBER;
x_msg_data VARCHAR2(200);
lr_vendor_rec apps.ap_vendor_pub_pkg.r_vendor_rec_type;
lr_existing_vendor_rec ap_suppliers%ROWTYPE;
l_msg VARCHAR2(200);
p_vendor_id NUMBER;
l_new_supplier_type fnd_lookup_values.lookup_code%type;
l_allow_awt_flag ap_suppliers.allow_awt_flag%TYPE;
l_ap_awt_group_id ap_awt_groups.group_id%TYPE;
l_status_flag VARCHAR(1):='N';
l_status_msg VARCHAR2(2000) := NULL;
CURSOR c_vendor_type IS
SELECT DISTINCT vendor_id
,supplier_type
,new_supplier_type
,new_invoice_wht_tax_group
FROM xx_vendor_master_data
WHERE NVL(supp_status_flag, 'N') IN ('N', 'E')
--AND vendor_id = 7216
AND vendor_id IN (5041)--5059)
;
BEGIN
-- Initialize apps session
fnd_global.apps_initialize(0, 50240, 200); --user_id,
mo_global.init('SQLAP');
fnd_client_info.set_org_context(101);
-- Assign Basic Values
p_api_version := 1.0;
p_init_msg_list := fnd_api.g_true;
p_commit := fnd_api.g_true;
p_validation_level := fnd_api.g_valid_level_full;
FOR j in c_vendor_type
LOOP
p_vendor_id := j.vendor_id;
l_status_flag := 'N';
l_status_msg := NULL;
l_ap_awt_group_id := NULL;
l_allow_awt_flag := 'N';
-- gather vendor details
BEGIN
SELECT *
INTO lr_existing_vendor_rec
FROM ap_suppliers asa
WHERE asa.vendor_id = p_vendor_id;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.put_line('Unable to derive the supplier information for vendor id:' ||
p_vendor_id);
END;
IF j.supplier_type <> j.new_supplier_type THEN
BEGIN
SELECT lookup_code
INTO l_new_supplier_type
FROM fnd_lookup_values
WHERE meaning = j.new_supplier_type
AND lookup_type = 'VENDOR TYPE'
AND enabled_flag = 'Y'
AND language = userenv('LANG');
EXCEPTION
WHEN NO_DATA_FOUND THEN
l_new_supplier_type := null;
l_status_flag := 'E';
l_status_msg := 'Supplier Type does not exists in VENDOR TYPE Lookup: ' || SQLERRM;
WHEN OTHERS THEN
l_new_supplier_type := null;
l_status_flag := 'E';
l_status_msg := 'Error while validating new supplier type. error message: ' || SQLERRM;
END;
ELSE
NULL;
END IF;
IF TRIM(j.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(j.new_invoice_wht_tax_group)
;
l_allow_awt_flag := 'Y';
EXCEPTION
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);
lr_vendor_rec.vendor_id := lr_existing_vendor_rec.vendor_id;
-----------------------------------------------
--Deactivate Vendor
-- lr_vendor_rec.end_date_active := SYSDATE;
-- lr_vendor_rec.enabled_flag := 'N';
-----------------------------------------------
lr_vendor_rec.vendor_type_lookup_code := l_new_supplier_type;
lr_vendor_rec.allow_awt_flag := l_allow_awt_flag;
lr_vendor_rec.awt_group_id := l_ap_awt_group_id;
IF l_status_flag != 'E'
THEN
ap_vendor_pub_pkg.update_vendor(p_api_version => p_api_version
,p_init_msg_list => p_init_msg_list
,p_commit => p_commit
,p_validation_level => p_validation_level
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
,p_vendor_rec => lr_vendor_rec
,p_vendor_id => p_vendor_id
);
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 i IN 1 .. fnd_msg_pub.count_msg
LOOP
l_msg := fnd_msg_pub.get(p_msg_index => i
,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_status_flag = 'E'
,supp_status_msg = l_msg
WHERE vendor_id = j.vendor_id;
END LOOP;
ELSE
DBMS_OUTPUT.put_line('The API call ended with SUCESSS status');
UPDATE xx_vendor_master_data
SET supp_status_flag = 'S'
,supp_status_msg = 'Update Successful'
WHERE vendor_id = j.vendor_id;
END IF;
ELSE
UPDATE xx_vendor_master_data
SET supp_status_flag = 'E'
,supp_status_msg = l_status_msg
WHERE vendor_id = j.vendor_id;
END IF;
END LOOP;
END;
/
No comments:
Post a Comment