--------------------------------1. Data Template for Supplier Additional Info :---------------------------
SL_NO
VENDOR_NAME
VENDOR_SITE_CODE
EXCISE_DUTY_REG_NO
EXCISE_DUTY_RANGE
EXCISE_DUTY_DIVISION
EXCISE_DUTY_COMM
EC_CODE
TAX_NAME
ST_REG_NO
CST_REG_NO
VAT_REG_NO
SERVICE_TAX_REGNO
PAN_NO
TAN_NO
WARD_NO
SECTION_CODE
TDS_VENDOR_TYPE_LOOKUP_CODE
VERIFY_FLAG
NVL_SITE_VERIFY_FLAG
ERROR_MESSAGE
---------------------------2. Create staging table :------------------------------------
CREATE TABLE XX_SUPPLIERS_ADDINFO
(
SL_NO NUMBER NOT NULL,
VENDOR_NAME VARCHAR2(240 BYTE) NOT NULL,
VENDOR_SITE_CODE VARCHAR2(15 BYTE) NOT NULL,
EXCISE_DUTY_REG_NO VARCHAR2(50 BYTE),
EXCISE_DUTY_RANGE VARCHAR2(50 BYTE),
EXCISE_DUTY_DIVISION VARCHAR2(50 BYTE),
EXCISE_DUTY_COMM VARCHAR2(50 BYTE),
EC_CODE VARCHAR2(50 BYTE),
TAX_NAME VARCHAR2(50 BYTE),
ST_REG_NO VARCHAR2(50 BYTE),
CST_REG_NO VARCHAR2(50 BYTE),
VAT_REG_NO VARCHAR2(50 BYTE),
SERVICE_TAX_REGNO VARCHAR2(50 BYTE),
PAN_NO VARCHAR2(30 BYTE),
TAN_NO VARCHAR2(30 BYTE),
WARD_NO VARCHAR2(30 BYTE),
SECTION_CODE VARCHAR2(30 BYTE),
TDS_VENDOR_TYPE_LOOKUP_CODE VARCHAR2(30 BYTE),
VERIFY_FLAG VARCHAR2(1 BYTE),
NVL_SITE_VERIFY_FLAG VARCHAR2(1 BYTE),
ERROR_MESSAGE VARCHAR2(100 BYTE)
)
---------------------------3. Create Procedure :------------------------------------
CREATE OR REPLACE PROCEDURE APPS.XXCONA_SUPPLER_ADDINFO(
errbuf OUT VARCHAR2,
retcode OUT NUMBER,
P_ORG_ID IN NUMBER
)
AS
/********************Suppliers Additional Information ***********************/
L_VERIFY_FLAG VARCHAR2(1);
L_ERROR_MESSAGE VARCHAR2(2500);
L_VENDOR_ID NUMBER(10);
L_VENDOR_SITE_ID NUMBER(10);
L_ORG_ID NUMBER(10);
L_USER_ID NUMBER(10);
L_VENDOR_TYPE VARCHAR2(50);
L_TAX_ID NUMBER(10);
L_SECTION_TYPE VARCHAR2(25);
L_SECTION_CODE VARCHAR2(30);
L_CNT_NVL_SITE NUMBER(3);
L_CNT_SITE NUMBER(3);
CURSOR C_ADDINFO IS
SELECT *
FROM XX_SUPPLIERS_ADDINFO
WHERE VERIFY_FLAG = 'N' ;
BEGIN
DBMS_OUTPUT.PUT_LINE ('Upload Program Start');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'Checking Start-----');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'Operating Unit-----'||P_ORG_ID);
FOR c1 IN C_ADDINFO
LOOP
l_verify_flag := 'Y';
l_error_message := null;
l_cnt_nvl_site := 0;
l_cnt_site := 0;
BEGIN
SELECT ORGANIZATION_ID INTO l_org_id
FROM HR_OPERATING_UNITS
WHERE ORGANIZATION_ID = P_ORG_ID; -- NAME like P_ORG_CODE;
EXCEPTION
WHEN OTHERS THEN
l_verify_flag := 'N';
l_error_message := l_error_message||' Operating Unit is not valid...';
DBMS_OUTPUT.PUT_LINE ('Operating Unit is '||l_org_id);
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'Operating Unit is '||l_org_id);
END;
l_user_id := APPS.FND_PROFILE.VALUE ('user_id');
BEGIN
SELECT VENDOR_ID,VENDOR_TYPE_LOOKUP_CODE
INTO l_vendor_id,L_VENDOR_TYPE
FROM APPS.AP_SUPPLIERS
WHERE TRIM(upper(vendor_name)) = trim(upper(c1.vendor_name));
EXCEPTION
WHEN OTHERS Then
l_verify_flag := 'N';
l_error_message := l_error_message||'Vendor Name is not valid...';
DBMS_OUTPUT.PUT_LINE ('Vendor ID '||l_vendor_id ||' Vendor Type '||l_vendor_type);
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'Vendor ID '||l_vendor_id ||' Vendor Type '||l_vendor_type);
END;
BEGIN
SELECT VENDOR_SITE_ID INTO l_vendor_site_id
FROM AP_SUPPLIER_SITES_ALL
WHERE TRIM(upper(vendor_site_code)) = trim(upper(c1.vendor_site_code))
AND VENDOR_ID = l_vendor_id
AND ORG_ID = l_org_id;
EXCEPTION
WHEN OTHERS Then
l_verify_flag := 'N';
l_error_message := l_error_message||'Vendor Site not valid...';
DBMS_OUTPUT.PUT_LINE ('Vendor Site ID '||l_vendor_site_id );
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'Vendor Site ID '||l_vendor_site_id);
END;
BEGIN
SELECT TAX_ID INTO l_tax_id
FROM JAI_CMN_TAXES_ALL
WHERE upper(tax_name) = trim(upper(c1.tax_name))
AND org_id = l_org_id
AND nvl(end_date,sysdate) >= sysdate;
EXCEPTION
WHEN NO_DATA_FOUND THEN
null;
WHEN OTHERS THEN
l_verify_flag := 'N';
l_error_message := l_error_message||'TDS Tax is not Valid...';
DBMS_OUTPUT.PUT_LINE ('TAX ID '||l_tax_id );
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'TAX ID '||l_tax_id);
END;
BEGIN
SELECT DISTINCT SECTION_TYPE, SECTION_CODE, VENDOR_TYPE_LOOKUP_CODE
INTO l_section_type, l_section_code, l_vendor_type
FROM JAI_AP_TDS_THHOLD_HDRS
WHERE TRIM(UPPER(SECTION_CODE)) = UPPER(TRIM(C1.SECTION_CODE))
AND TRIM(UPPER(VENDOR_TYPE_LOOKUP_CODE)) = UPPER(TRIM(C1.TDS_VENDOR_TYPE_LOOKUP_CODE))
AND EXCEPTION_SETUP_FLAG = 'N';
EXCEPTION
WHEN NO_DATA_FOUND THEN
null;
WHEN OTHERS THEN
l_verify_flag := 'N';
l_error_message := l_error_message||'Tds Section or TDS Vendor Type is not valid...';
DBMS_OUTPUT.PUT_LINE ('VENDOR_TYPE_LOOKUP_CODE '||l_vendor_type );
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'VENDOR_TYPE_LOOKUP_CODE '||l_vendor_type);
END;
BEGIN
SELECT COUNT(*) INTO l_cnt_site
FROM JAI_CMN_VENDOR_SITES
WHERE vendor_site_id = l_vendor_site_id
AND vendor_id = l_vendor_id ;
IF l_cnt_site > 0 then
l_verify_flag := 'N' ;
l_error_message := l_error_message||'Site already existing in JAI_CMN_VENDOR_SITES...';
l_cnt_site := 0;
END IF;
EXCEPTION
WHEN OTHERS THEN
l_cnt_site := 0;
DBMS_OUTPUT.PUT_LINE ('NO of Site already existing '||l_cnt_site||l_error_message );
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'NO of Site already existing '||l_cnt_site||l_error_message );
END;
BEGIN
SELECT COUNT(*) INTO L_CNT_SITE
FROM JAI_AP_TDS_VENDOR_HDRS
WHERE VENDOR_SITE_ID = l_vendor_site_id
AND VENDOR_ID = l_vendor_id ;
IF L_CNT_SITE > 0 THEN
L_VERIFY_FLAG := 'N' ;
l_error_message := l_error_message||'Site already existing in JAI_AP_TDS_VENDOR_HDRS...';
END IF;
EXCEPTION
WHEN OTHERS THEN
l_cnt_site := 0;
DBMS_OUTPUT.PUT_LINE ('NO of Site already existing '||l_cnt_site||l_error_message );
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'NO of Site already existing '||l_cnt_site||l_error_message );
END;
IF l_verify_flag <> 'N' THEN
BEGIN
SELECT COUNT(*) INTO L_CNT_NVL_SITE
FROM JAI_CMN_VENDOR_SITES
WHERE VENDOR_SITE_ID = 0
AND VENDOR_ID = l_vendor_id ;
EXCEPTION
WHEN OTHERS THEN
l_cnt_nvl_site := 0;
END;
DBMS_OUTPUT.PUT_LINE ('NO of Supplier upload '||L_CNT_NVL_SITE );
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'NO of Supplier upload '||L_CNT_NVL_SITE);
IF l_cnt_nvl_site = 0 then
BEGIN
DBMS_OUTPUT.PUT_LINE ('Null Supplier site insert JAI_CMN_VENDOR_SITES ');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'Null Supplier site insert JAI_CMN_VENDOR_SITES');
INSERT INTO JAI_CMN_VENDOR_SITES
(
VENDOR_ID
,VENDOR_SITE_ID
,creation_date
,created_by
,last_update_date
,last_updated_by
,last_update_login
,approved_invoice_flag
,EXCISE_DUTY_RANGE
,EXCISE_DUTY_DIVISION
,EXCISE_DUTY_REG_NO
,cst_reg_no
,st_reg_no
,vat_reg_no
,SERVICE_TAX_REGNO
,EXCISE_DUTY_COMM
,EC_CODE
)
VALUES
(
l_vendor_id
,0
,sysdate
,l_user_id
,sysdate
,l_user_id
,-1
,'Y'
,trim(c1.excise_duty_range)
,trim(c1.excise_duty_division)
,trim(c1.excise_duty_reg_no)
,trim(c1.CST_REG_NO)
,trim(c1.ST_REG_NO)
,trim(c1.VAT_REG_NO)
,trim(c1.SERVICE_TAX_REGNO)
,trim(c1.EXCISE_DUTY_COMM)
,NULL
) ;
UPDATE XX_SUPPLIERS_ADDINFO
SET nvl_site_verify_flag = 'Y'
WHERE vendor_name = c1.vendor_name
AND VENDOR_SITE_CODE = c1.VENDOR_SITE_CODE;
EXCEPTION
WHEN OTHERS THEN
l_error_message := SQLERRM ;
l_verify_flag := 'N';
DBMS_OUTPUT.PUT_LINE ('Error is '||l_error_message);
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'Error is '||l_error_message);
UPDATE XX_SUPPLIERS_ADDINFO
SET nvl_site_verify_flag = 'N',
error_message = l_error_message
WHERE vendor_name = c1.vendor_name
AND vendor_site_code = c1.vendor_site_code;
END;
IF l_verify_flag <> 'N' THEN
BEGIN
INSERT INTO JAI_AP_TDS_VENDOR_HDRS
(
vendor_id
,vendor_site_id
,pan_no
,creation_date
,created_by
,last_update_date
,last_updated_by
,last_update_login
,section_type
,section_code
,confirm_pan_flag
,tds_vendor_type_lookup_code
,TAN_NO
)
VALUES
(
l_vendor_id
,0
,trim(c1.pan_no)
,sysdate
,l_user_id
,sysdate
,l_user_id
,-1
,''
,''
,'Y'
,l_vendor_type
,trim(c1.TAN_NO)
) ;
UPDATE XX_SUPPLIERS_ADDINFO
set nvl_site_verify_flag = 'Y'
where vendor_name = c1.vendor_name
and vendor_site_code = c1.vendor_site_code;
EXCEPTION
WHEN OTHERS THEN
l_error_message := SQLERRM;
l_verify_flag := 'N';
DBMS_OUTPUT.PUT_LINE ('Error is '||l_error_message);
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'Error is '||l_error_message);
UPDATE XX_SUPPLIERS_ADDINFO
set nvl_site_verify_flag = 'N',
error_message = l_error_message
where vendor_name = c1.vendor_name
and vendor_site_code = c1.vendor_site_code;
END;
END IF;
END IF;
BEGIN
INSERT INTO JAI_CMN_VENDOR_SITES J
(
vendor_id
,vendor_site_id
,creation_date
,created_by
,last_update_date
,last_updated_by
,last_update_login
,approved_invoice_flag
,excise_duty_range
,excise_duty_division
,excise_duty_reg_no
,cst_reg_no
,st_reg_no
,vat_reg_no
,service_tax_regno
,EXCISE_DUTY_COMM
,EC_CODE
)
values
(
l_vendor_id
,l_vendor_site_id
,sysdate
,l_user_id
,sysdate
,l_user_id
,-1
,'Y'
,trim(c1.excise_duty_range)
,trim(c1.excise_duty_division)
,trim(c1.excise_duty_reg_no)
,trim(c1.cst_reg_no)
,trim(c1.st_reg_no)
,trim(c1.vat_reg_no)
,trim(c1.service_tax_regno)
,trim(c1.EXCISE_DUTY_COMM)
,trim(c1.EC_CODE)
) ;
UPDATE XX_SUPPLIERS_ADDINFO
set verify_flag = 'Y'
where vendor_name = c1.vendor_name
and vendor_site_code = c1.vendor_site_code;
EXCEPTION
WHEN OTHERS THEN
l_error_message := SQLERRM;
l_verify_flag := 'N';
DBMS_OUTPUT.PUT_LINE ('Error is '||l_error_message);
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'Error is '||l_error_message);
UPDATE XX_SUPPLIERS_ADDINFO
set verify_flag = 'N',
error_message = l_error_message
where vendor_name = c1.vendor_name
and vendor_site_code = c1.vendor_site_code;
END;
BEGIN
insert into JAI_AP_TDS_VENDOR_HDRS
(
tax_id
,vendor_id
,vendor_site_id
,pan_no
,creation_date
,created_by
,last_update_date
,last_updated_by
,last_update_login
,section_type
,section_code
,confirm_pan_flag
,tds_vendor_type_lookup_code
,TAN_NO
)
values
(
l_tax_id
,l_vendor_id
,l_vendor_site_id
,trim(c1.pan_no)
,sysdate
,l_user_id
,sysdate
,l_user_id
,-1
,l_section_type
,l_section_code
,'Y'
,l_vendor_type
,trim(c1.TAN_NO)
) ;
UPDATE XX_SUPPLIERS_ADDINFO
set verify_flag = 'Y'
where vendor_name = c1.vendor_name
and vendor_site_code = c1.vendor_site_code;
EXCEPTION
WHEN OTHERS THEN
l_error_message := SQLERRM;
l_verify_flag := 'N';
DBMS_OUTPUT.PUT_LINE ('Error is '||l_error_message);
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'Error is '||l_error_message);
UPDATE XX_SUPPLIERS_ADDINFO
set verify_flag = 'N',
error_message = l_error_message
where vendor_name = c1.vendor_name
and vendor_site_code = c1.vendor_site_code;
END;
ELSE
DBMS_OUTPUT.PUT_LINE ('NO of Supplier upload '||L_CNT_NVL_SITE );
UPDATE XX_SUPPLIERS_ADDINFO
set verify_flag = 'N',
error_message = l_error_message
where vendor_name = c1.vendor_name
and vendor_site_code = c1.vendor_site_code;
END IF;
Commit;
END LOOP;
DBMS_OUTPUT.PUT_LINE ('Upload Program End');
END XXCONA_SUPPLER_ADDINFO;
/