Supplier Additional Information Migration
SUPPLIER ADDITIONAL INFORMATION
******************************************There is no interface tables to upload supplier additional information. Only we can do is directly migrate data into addinfo tables.
Two tables are there in vendor additional info
a) JA_IN_PO_VENDOR_SITES
b) JA_IN_VENDOR_TDS_INFO_HDR
When we create addinfo for vendors, we have to create one null site information for every vendors. To migrate data into vendor addinfo pls follow the following steps
Steps
------1. Data Template for AddInfo :-
VENDOR_NAME
VENDOR_SITE
EXCISE_DUTY_RANGE
EXCISE_DUTY_DIVISION
EXCISE_DUTY_REG_NO
CST_REG_NO
ST_REG_NO
VAT_REG_NO
SERVICE_TAX_REGNO
PAN_NO TDS_TAX_NAME
TDS_SECTION
TDS_VENDOR_TYPE
VERIFY_FLAG
ERROR_MESSAGE
NVL_SITE_VERIFY_FLAG
2. Create staging table :-
CREATE TABLE XXX_VENDOR_ADDINFO_STG
(
VENDOR_NAME VARCHAR2(100),
VENDOR_SITE VARCHAR2(15),
EXCISE_DUTY_RANGE VARCHAR2(50),
EXCISE_DUTY_DIVISION VARCHAR2(50),
EXCISE_DUTY_REG_NO VARCHAR2(50),
CST_REG_NO VARCHAR2(50),
ST_REG_NO VARCHAR2(50),
VAT_REG_NO VARCHAR2(50),
SERVICE_TAX_REGNO VARCHAR2(50),
PAN_NO VARCHAR2(30),
TDS_TAX_NAME VARCHAR2(150),
TDS_SECTION VARCHAR2(50),
TDS_VENDOR_TYPE VARCHAR2(50),
VERIFY_FLAG CHAR(1),
ERROR_MESSAGE VARCHAR2(2500),
NVL_SITE_VERIFY_FLAG CHAR(1)
)
3. Upload data from excel to staging table by Toad, Sql Loder
4. Run the following script to migrate data from staging table to base tables
CREATE OR REPLACE procedure xxx_vendor_addinfo_prc
as
l_verify_flag char(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_tds_vendor_type varchar2(50);
l_cnt_nvl_site number(3);
l_cnt_site number(3);
cursor c_addinfo is
select *
from xxx_vendor_addinfo_stg
where verify_flag = 'N' ;
begin
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 name like 'xxx Operating Unit';
EXCEPTION
WHEN OTHERS THEN
l_verify_flag := 'N';
l_error_message := l_error_message||'Operating Unit is not valid...';
END;
BEGIN
select user_id
into l_user_id
from fnd_user
where user_name = 'KPMG';
EXCEPTION
WHEN OTHERS THEN
l_verify_flag := 'N';
l_error_message := l_error_message||'User Name is not Valid...';
END;
BEGIN
select vendor_id,vendor_type_lookup_code
into l_vendor_id, l_vendor_type
from po_vendors
where 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...';
END;
BEGIN
select vendor_site_id
into l_vendor_site_id
from po_vendor_sites_all
where upper(vendor_site_code) = trim(upper(c1.vendor_site))
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...';
END;
BEGIN
select tax_id
into l_tax_id
from ja_in_tax_codes
where upper(tax_name) = trim(upper(c1.tds_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...';
END;
BEGIN
select distinct section_type,
section_code,
vendor_type_lookup_code
into l_section_type,
l_section_code,
l_tds_vendor_type
from JAI_AP_TDS_THHOLD_HDRS
where upper(section_code) = upper(trim(c1.tds_section))
and upper(vendor_type_lookup_code) = upper(trim(c1.tds_vendor_type))
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...';
END;
BEGIN
select count(*)
into l_cnt_site
from JA_IN_PO_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 JA_IN_PO_VENDOR_SITES...';
l_cnt_site := 0;
END IF;
EXCEPTION
WHEN OTHERS THEN
l_cnt_site := 0;
END;
BEGIN
select count(*)
into l_cnt_site
from JA_IN_VENDOR_TDS_INFO_HDR
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 JA_IN_VENDOR_TDS_INFO_HDR...';
END IF;
EXCEPTION
WHEN OTHERS THEN
l_cnt_site := 0;
END;
IF l_verify_flag <> 'N' THEN
BEGIN
select count(*)
into l_cnt_nvl_site
from JA_IN_PO_VENDOR_SITES
where vendor_site_id = 0
and vendor_id = l_vendor_id ;
EXCEPTION
WHEN OTHERS THEN
l_cnt_nvl_site := 0;
END;
IF l_cnt_nvl_site = 0 then
BEGIN
insert into JA_IN_PO_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
)
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)
) ;
UPDATE xxx_vendor_addinfo_stg
set nvl_site_verify_flag = 'Y'
where vendor_name = c1.vendor_name
and vendor_site = c1.vendor_site;
EXCEPTION
WHEN OTHERS THEN
l_error_message := SQLERRM ;
l_verify_flag := 'N';
UPDATE xxx_vendor_addinfo_stg
set nvl_site_verify_flag = 'N',
error_message = l_error_message
where vendor_name = c1.vendor_name
and vendor_site = c1.vendor_site;
END;
IF l_verify_flag <> 'N' THEN
BEGIN
insert into JA_IN_VENDOR_TDS_INFO_HDR
(
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
)
values
(
l_vendor_id
,0
,trim(c1.pan_no)
,sysdate
,l_user_id
,sysdate
,l_user_id
,-1
,''
,''
,'Y'
,l_tds_vendor_type
) ;
UPDATE xxx_vendor_addinfo_stg
set nvl_site_verify_flag = 'Y'
where vendor_name = c1.vendor_name
and vendor_site = c1.vendor_site;
EXCEPTION
WHEN OTHERS THEN
l_error_message := SQLERRM;
l_verify_flag := 'N';
UPDATE xxx_vendor_addinfo_stg
set nvl_site_verify_flag = 'N',
error_message = l_error_message
where vendor_name = c1.vendor_name
and vendor_site = c1.vendor_site;
END;
END IF;
END IF;
BEGIN
insert into JA_IN_PO_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
)
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)
) ;
UPDATE xxx_vendor_addinfo_stg
set verify_flag = 'Y'
where vendor_name = c1.vendor_name
and vendor_site = c1.vendor_site;
EXCEPTION
WHEN OTHERS THEN
l_error_message := SQLERRM;
l_verify_flag := 'N';
UPDATE xxx_vendor_addinfo_stg
set verify_flag = 'N',
error_message = l_error_message
where vendor_name = c1.vendor_name
and vendor_site = c1.vendor_site;
END;
BEGIN
insert into JA_IN_VENDOR_TDS_INFO_HDR
(
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
)
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_tds_vendor_type
) ;
UPDATE xxx_vendor_addinfo_stg
set verify_flag = 'Y'
where vendor_name = c1.vendor_name
and vendor_site = c1.vendor_site;
EXCEPTION
WHEN OTHERS THEN
l_error_message := SQLERRM;
l_verify_flag := 'N';
UPDATE xxx_vendor_addinfo_stg
set verify_flag = 'N',
error_message = l_error_message
where vendor_name = c1.vendor_name
and vendor_site = c1.vendor_site;
END;
ELSE
UPDATE xxx_vendor_addinfo_stg
set verify_flag = 'N',
error_message = l_error_message
where vendor_name = c1.vendor_name
and vendor_site = c1.vendor_site;
END IF;
commit;
END LOOP;
end xxx_vendor_addinfo_prc;
/
******************************************There is no interface tables to upload supplier additional information. Only we can do is directly migrate data into addinfo tables.
Two tables are there in vendor additional info
a) JA_IN_PO_VENDOR_SITES
b) JA_IN_VENDOR_TDS_INFO_HDR
When we create addinfo for vendors, we have to create one null site information for every vendors. To migrate data into vendor addinfo pls follow the following steps
Steps
------1. Data Template for AddInfo :-
VENDOR_NAME
VENDOR_SITE
EXCISE_DUTY_RANGE
EXCISE_DUTY_DIVISION
EXCISE_DUTY_REG_NO
CST_REG_NO
ST_REG_NO
VAT_REG_NO
SERVICE_TAX_REGNO
PAN_NO TDS_TAX_NAME
TDS_SECTION
TDS_VENDOR_TYPE
VERIFY_FLAG
ERROR_MESSAGE
NVL_SITE_VERIFY_FLAG
2. Create staging table :-
CREATE TABLE XXX_VENDOR_ADDINFO_STG
(
VENDOR_NAME VARCHAR2(100),
VENDOR_SITE VARCHAR2(15),
EXCISE_DUTY_RANGE VARCHAR2(50),
EXCISE_DUTY_DIVISION VARCHAR2(50),
EXCISE_DUTY_REG_NO VARCHAR2(50),
CST_REG_NO VARCHAR2(50),
ST_REG_NO VARCHAR2(50),
VAT_REG_NO VARCHAR2(50),
SERVICE_TAX_REGNO VARCHAR2(50),
PAN_NO VARCHAR2(30),
TDS_TAX_NAME VARCHAR2(150),
TDS_SECTION VARCHAR2(50),
TDS_VENDOR_TYPE VARCHAR2(50),
VERIFY_FLAG CHAR(1),
ERROR_MESSAGE VARCHAR2(2500),
NVL_SITE_VERIFY_FLAG CHAR(1)
)
3. Upload data from excel to staging table by Toad, Sql Loder
4. Run the following script to migrate data from staging table to base tables
CREATE OR REPLACE procedure xxx_vendor_addinfo_prc
as
l_verify_flag char(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_tds_vendor_type varchar2(50);
l_cnt_nvl_site number(3);
l_cnt_site number(3);
cursor c_addinfo is
select *
from xxx_vendor_addinfo_stg
where verify_flag = 'N' ;
begin
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 name like 'xxx Operating Unit';
EXCEPTION
WHEN OTHERS THEN
l_verify_flag := 'N';
l_error_message := l_error_message||'Operating Unit is not valid...';
END;
BEGIN
select user_id
into l_user_id
from fnd_user
where user_name = 'KPMG';
EXCEPTION
WHEN OTHERS THEN
l_verify_flag := 'N';
l_error_message := l_error_message||'User Name is not Valid...';
END;
BEGIN
select vendor_id,vendor_type_lookup_code
into l_vendor_id, l_vendor_type
from po_vendors
where 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...';
END;
BEGIN
select vendor_site_id
into l_vendor_site_id
from po_vendor_sites_all
where upper(vendor_site_code) = trim(upper(c1.vendor_site))
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...';
END;
BEGIN
select tax_id
into l_tax_id
from ja_in_tax_codes
where upper(tax_name) = trim(upper(c1.tds_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...';
END;
BEGIN
select distinct section_type,
section_code,
vendor_type_lookup_code
into l_section_type,
l_section_code,
l_tds_vendor_type
from JAI_AP_TDS_THHOLD_HDRS
where upper(section_code) = upper(trim(c1.tds_section))
and upper(vendor_type_lookup_code) = upper(trim(c1.tds_vendor_type))
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...';
END;
BEGIN
select count(*)
into l_cnt_site
from JA_IN_PO_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 JA_IN_PO_VENDOR_SITES...';
l_cnt_site := 0;
END IF;
EXCEPTION
WHEN OTHERS THEN
l_cnt_site := 0;
END;
BEGIN
select count(*)
into l_cnt_site
from JA_IN_VENDOR_TDS_INFO_HDR
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 JA_IN_VENDOR_TDS_INFO_HDR...';
END IF;
EXCEPTION
WHEN OTHERS THEN
l_cnt_site := 0;
END;
IF l_verify_flag <> 'N' THEN
BEGIN
select count(*)
into l_cnt_nvl_site
from JA_IN_PO_VENDOR_SITES
where vendor_site_id = 0
and vendor_id = l_vendor_id ;
EXCEPTION
WHEN OTHERS THEN
l_cnt_nvl_site := 0;
END;
IF l_cnt_nvl_site = 0 then
BEGIN
insert into JA_IN_PO_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
)
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)
) ;
UPDATE xxx_vendor_addinfo_stg
set nvl_site_verify_flag = 'Y'
where vendor_name = c1.vendor_name
and vendor_site = c1.vendor_site;
EXCEPTION
WHEN OTHERS THEN
l_error_message := SQLERRM ;
l_verify_flag := 'N';
UPDATE xxx_vendor_addinfo_stg
set nvl_site_verify_flag = 'N',
error_message = l_error_message
where vendor_name = c1.vendor_name
and vendor_site = c1.vendor_site;
END;
IF l_verify_flag <> 'N' THEN
BEGIN
insert into JA_IN_VENDOR_TDS_INFO_HDR
(
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
)
values
(
l_vendor_id
,0
,trim(c1.pan_no)
,sysdate
,l_user_id
,sysdate
,l_user_id
,-1
,''
,''
,'Y'
,l_tds_vendor_type
) ;
UPDATE xxx_vendor_addinfo_stg
set nvl_site_verify_flag = 'Y'
where vendor_name = c1.vendor_name
and vendor_site = c1.vendor_site;
EXCEPTION
WHEN OTHERS THEN
l_error_message := SQLERRM;
l_verify_flag := 'N';
UPDATE xxx_vendor_addinfo_stg
set nvl_site_verify_flag = 'N',
error_message = l_error_message
where vendor_name = c1.vendor_name
and vendor_site = c1.vendor_site;
END;
END IF;
END IF;
BEGIN
insert into JA_IN_PO_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
)
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)
) ;
UPDATE xxx_vendor_addinfo_stg
set verify_flag = 'Y'
where vendor_name = c1.vendor_name
and vendor_site = c1.vendor_site;
EXCEPTION
WHEN OTHERS THEN
l_error_message := SQLERRM;
l_verify_flag := 'N';
UPDATE xxx_vendor_addinfo_stg
set verify_flag = 'N',
error_message = l_error_message
where vendor_name = c1.vendor_name
and vendor_site = c1.vendor_site;
END;
BEGIN
insert into JA_IN_VENDOR_TDS_INFO_HDR
(
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
)
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_tds_vendor_type
) ;
UPDATE xxx_vendor_addinfo_stg
set verify_flag = 'Y'
where vendor_name = c1.vendor_name
and vendor_site = c1.vendor_site;
EXCEPTION
WHEN OTHERS THEN
l_error_message := SQLERRM;
l_verify_flag := 'N';
UPDATE xxx_vendor_addinfo_stg
set verify_flag = 'N',
error_message = l_error_message
where vendor_name = c1.vendor_name
and vendor_site = c1.vendor_site;
END;
ELSE
UPDATE xxx_vendor_addinfo_stg
set verify_flag = 'N',
error_message = l_error_message
where vendor_name = c1.vendor_name
and vendor_site = c1.vendor_site;
END IF;
commit;
END LOOP;
end xxx_vendor_addinfo_prc;
/
No comments:
Post a Comment