Posted: Sat Jul 12, 2008 5:16 am
by sreekee
I am sending the item conversion program.please go throug it it may help.
create or replace package body XXSRE_items_import_pkg is
/******************************************************************************************
* Program Name: XXSRE_items_import_pkg
* Short Name :
* File Name : Import_item_final.sql *
* Developer : Prasad, Rambabu, Sreekanth *
* Description : This package Header is to load items from flat file *
* *
* Parameters : *
* Return value: *
* Restart : *
* *
*
**************************************************************************************************/
procedure XXSRE_items_import(errbuf out varchar2,
retcode out number,
p_val_proc_flag in varchar2,-- processed 'P', Validated 'V'
p_org_code in varchar2) is
l_err_status varchar2(1) := 'N';
l_err_stat varchar2(1) := 'N';
l_mst_org_id number;
l_mst_org_code varchar2(3);
l_org_id number;
l_org_code varchar2(3);
l_item_id number;
l_unit_list_price number := 0;
l_uom varchar2(100);
l_uom_code varchar2(100);
l_icond varchar2(20);
l_iclas varchar2(100);
l_mst_exists varchar2(1);
l_desc varchar2(240);
l_template_id number;
l_item_type varchar2(100);
l_status varchar2(2000);
L_UNIT_WEIGHT number;
L_UNIT_VOLUME number;
l_sqlerrm varchar2(200);
l_sqlcode varchar2(20);
l_cat_set_id number;
l_cat_id number;
cursor c1 is
select rowid, sis.* from XXSRE_ITEM_STAGING sis where S_process_flag='N'
and S_organization_code=(select organization_code from mtl_parameters where organization_code= p_org_code);
begin
if p_val_proc_flag in ('P','V') then
null;
else
retcode:= 2;
APPS.FND_FILE.PUT_LINE(APPS.FND_FILE.LOG,'Validate / Process flag Invalid' ||p_val_proc_flag);
l_err_stat := 'Y';
end if;
--finding the master organization id and the code (taking V1 as our master organization)
dbms_output.put_line('master org finding');
begin
select distinct a.master_organization_id,b.organization_code
into l_mst_org_id, l_mst_org_code
from oe_system_parameters_all a,
mtl_parameters b
where b.organization_id =a.master_organization_id
and organization_code = 'V1';
dbms_output.put_line('master org found'||l_mst_org_id||l_mst_org_code);
exception
when no_data_found then
retcode:= 1;
l_err_stat := 'Y';
end;
-- finding the child organization, here we will be using the the organization we are gona pass throught the parameter
dbms_output.put_line('finding child org');
begin
select organization_id,organization_code
into l_org_id,l_org_code
from mtl_parameters
where organization_code = p_org_code;
dbms_output.put_line('org code and id ' ||l_org_id||l_org_code);
exception
when no_data_found then
retcode:= 2;
dbms_output.put_line('child org not found');
l_err_stat := 'Y';
end;
dbms_output.put_line('eRROR STATUS'||l_err_stat);
if l_err_stat = 'N' then --l_err_stat
--loop begins here for the validation (we will using the records in the cursor)
for c1_rec in c1 loop
dbms_output.put_line('Loop Begins');
l_err_status := 'N';
l_item_id := null;
l_unit_list_price := 0;
l_uom := null;
l_icond := null;
l_iclas := null;
l_mst_exists := 'N';
l_uom_code := null;
l_desc := null;
--checking for the items which are already present in the child organization and getting the inventory_item_id
dbms_output.put_line('Finding Item in Child org');
begin
select inventory_item_id
into l_item_id
from mtl_system_items_b
where segment1 = c1_rec.s_segment1
and organization_id = l_org_id;
dbms_output.put_line('Item exist in Child org');
exception
when no_data_found then
null;
--updating the staging table if the item already exists
when others then
update xxsre_item_staging
set s_process_flag = 'E',
s_error_mesg = 'item' ||c1_rec.s_segment1||' Exists in org: '||l_org_code
where rowid = c1_rec.rowid;
l_err_status := 'Y';
end;
--checking for the items which are already present in the master organization and getting the list_price_per_unit and the description
dbms_output.put_line('Finding Item in master org');
begin
select nvl(list_price_per_unit,0),description
into l_unit_list_price,l_desc
from mtl_system_items_b
where segment1 = c1_rec.s_segment1
and organization_id = l_mst_org_id;
dbms_output.put_line('Item Does not exist in master org');
l_mst_exists := 'Y';
exception
when no_data_found then
null;
end;
------------------------------------------------------
-- UOM
------------------------------------------------------
--validating for the uom and code
dbms_output.put_line('UOM SECTION');
begin
select unit_of_measure,uom_code
into l_uom,l_uom_code
from mtl_units_of_measure_vl
where uom_code = c1_rec.S_PRIMARY_UNIT_CODE;
exception
when no_data_found then
retcode:= 1;
dbms_output.put_line('UOM: ' ||c1_rec.S_PRIMARY_UNIT_CODE||' Does Not Exist');
update iappz_item_stag
set s_process_flag = 'E',
s_error_mesg = 'UOM: ' ||c1_rec.S_PRIMARY_UNIT_CODE||' Does Not Exist'
where rowid = c1_rec.rowid;
l_err_status := 'Y';
end;
------------------------------------------------------
-- Item Status
------------------------------------------------------
--checking if the item is active or not
dbms_output.put_line('item status'||c1_rec.S_INVENTORY_ITEM_STSTUS);
CASE c1_rec.S_INVENTORY_ITEM_STSTUS
WHEN 'Active' THEN l_icond := 'Active';
else l_icond := 'P';
END CASE;
dbms_output.put_line('Status of the item:' ||l_icond);
--------------------------------------------------------------
-------------- Item Type
--------------------------------------------------------------
--here we are find wheather the item is external or internal using fnd_lookup_values
dbms_output.put_line('item type SECTION');
begin
select lookup_code
into l_item_type
from fnd_lookup_values
where lookup_type = 'ITEM_TYPE'
AND LOOKUP_CODE = upper(c1_rec.S_ITEM_TYPE);
dbms_output.put_line(c1_rec.S_ITEM_TYPE);
--l_upd_xref := 'Y';
exception
when no_data_found then
retcode:= 2;
dbms_output.put_line('Item Type not found: ');
update iappz_item_stag
set s_process_flag = 'E',
s_error_mesg = 'ITEM Type not found: '||c1_rec.s_item_type
where rowid = c1_rec.rowid;
l_err_status := 'Y';
end;
--------------------------------------------------------------
-------------- Item Template
--------------------------------------------------------------
--validating and getting the template id for the Finished goods and purchase
dbms_output.put_line('item template SECTION');
begin
select template_id
into l_template_id
from mtl_item_templates
where template_name = decode(c1_rec.s_item_type,'FG','Finished Good','P','Purchased Item','****');
exception
when no_data_found then
retcode:= 1;
dbms_output.put_line('Item Template not found: ');
update iappz_item_stag
set s_process_flag = 'E',
s_error_mesg = 'Item Template not found:'
where rowid = c1_rec.rowid;
l_err_status := 'Y';
end;
dbms_output.put_line('item template'||l_template_id);
--------------------------------------------------------------
-------------- Category SECTION
--------------------------------------------------------------
--finding the category_set_id and category_id for item category
--hard coding the the category_set_name and category_id are hardcoded
Dbms_output.put_line ('finding Category SECTION');
if l_org_code ='M1' then
begin
select b.category_set_id,mc.category_id
into l_cat_set_id,l_cat_id
from mtl_category_sets_b b,
mtl_category_sets_tl tl,
mtl_categories_b mc
where b.category_set_id = tl.category_set_id
and upper(tl.category_set_name) = upper('Product Family')
and mc.category_id = 2031;
Dbms_output.put_line ('Product Family' ||l_cat_set_id||l_cat_id );
exception
when no_data_found then
retcode:= 1;
Dbms_output.put_line ('Product Family:' ||' Category not found');
l_err_status := 'Y';
end;
end if;
--we will be inserting the items into two organization (item should be created in master org and assigned to child org)
------------------------------------------------------
-- Insert into Item Interface begin
-- ORG Level
------------------------------------------------------
dbms_output.put_line('Inserting into Org level');
if l_err_status = 'N' then -- l_err_status
if p_val_proc_flag = 'P' then -- p_val_proc_flag
if l_org_code != 'V1' then
begin
insert into MTL_SYSTEM_ITEMS_INTERFACE
(segment1,
description,
primary_unit_of_Measure,
primary_uom_code,
item_type,
TEMPLATE_ID,
inventory_item_status_code,
organization_id,
organization_code,
full_lead_time,
list_price_per_unit,
process_flag,
transaction_type,
WEIGHT_UOM_CODE,
UNIT_WEIGHT,
VOLUME_UOM_CODE,
UNIT_VOLUME,
RETURNABLE_FLAG,
PREPROCESSING_LEAD_TIME,
--SECONDARY_INVENTORY_NAME,
INVENTORY_ITEM_FLAG,
default_so_source_type,
inventory_item_id
)
values
(C1_REC.S_SEGMENT1,
C1_REC.S_DESCRIPTION,
l_uom,
l_uom_code,
l_item_type,
l_template_id,
l_icond,
l_org_id,
l_org_code,
C1_REC.S_FULL_LEAD_TIME,
C1_REC.S_LIST_PRICE_PER_UNIT,
1,
C1_REC.S_TRANSACTION_TYPE,
C1_REC.S_WEIGHT_UOM,
C1_REC.S_UNIT_WEIGHT,
C1_REC.S_VOLUME_UOM,
C1_REC.S_UNIT_VOLUME,
C1_REC.s_RETURNABLE_FLAG,
C1_REC.s_PREPROCESSING_LEAD_TIME,
--l_rec_subi_code_x,
C1_REC.S_INVENTORY_ITEM_FLAG,
'EXTERNAL',
l_item_id
);
exception
when others then
l_sqlerrm := substr(sqlerrm,1,200);
l_sqlcode := to_char(sqlcode);
retcode:= 2;
--updating the staging table for errors
update xxsre_item_staging
set S_process_flag = 'E',
S_error_mesg = 'Error on insert of MTL_SYSTEM_ITEMS_INTERFACE '||' Error: '||l_sqlcode||' : '||l_sqlerrm
where rowid = c1_rec.rowid;
l_err_status := 'Y';
end;
end if; -- p_val_proc_flag
end if; ---- l_err_status
end if;
dbms_output.put_line('org level inserted');
-------------------------------------------------------
------------------------------------------------------
-- Insert into Item Interface begin
-- master Level
------------------------------------------------------
dbms_output.put_line('INSERT org level UPDATE SECTION');
if l_mst_exists = 'N' then
begin
insert into MTL_SYSTEM_ITEMS_INTERFACE
(segment1,
description,
primary_unit_of_Measure,
primary_uom_code,
item_type,
TEMPLATE_ID,
inventory_item_status_code,
-- organization_id,
organization_code,
full_lead_time,
list_price_per_unit,
process_flag,
transaction_type,
WEIGHT_UOM_CODE,
UNIT_WEIGHT,
VOLUME_UOM_CODE,
UNIT_VOLUME,
RETURNABLE_FLAG,
PREPROCESSING_LEAD_TIME,
--SECONDARY_INVENTORY_NAME,
INVENTORY_ITEM_FLAG,
default_so_source_type,
inventory_item_id
)
values
(C1_REC.S_SEGMENT1,
C1_REC.S_DESCRIPTION,
l_uom,
l_uom_code,
l_item_type,
l_template_id,
l_icond,
--l_org_id,
l_mst_org_code,
C1_REC.S_FULL_LEAD_TIME,
C1_REC.S_LIST_PRICE_PER_UNIT,
1,
C1_REC.S_TRANSACTION_TYPE,
C1_REC.S_WEIGHT_UOM,
C1_REC.S_UNIT_WEIGHT,
C1_REC.S_VOLUME_UOM,
C1_REC.S_UNIT_VOLUME,
C1_REC.s_RETURNABLE_FLAG,
C1_REC.s_PREPROCESSING_LEAD_TIME,
--l_rec_subi_code_x,
C1_REC.S_INVENTORY_ITEM_FLAG,
'EXTERNAL',
l_item_id
);
exception
when others then
l_sqlerrm := substr(sqlerrm,1,200);
l_sqlcode := to_char(sqlcode);
retcode:= 2;
dbms_output.put_line('Error on insert of MTL_SYSTEM_ITEMS_INTERFACE '||' Error: '||l_sqlcode||' : '||l_sqlerrm);
update xxsre_item_staging
set S_process_flag = 'E',
S_error_mesg = 'Error on insert of MTL_SYSTEM_ITEMS_INTERFACE '||' Error: '||l_sqlcode||' : '||l_sqlerrm
where rowid = c1_rec.rowid;
l_err_status := 'Y';
end;
end if;
dbms_output.put_line('master level inserted');
--------------------------------------------------------------
-------------- INSERT CATEGORY SECTION
--------------------------------------------------------------
if l_mst_exists = 'N' then --MST ORG Exists
begin
insert into MTL_ITEM_CATEGORIES_INTERFACE (
INVENTORY_ITEM_ID,
CATEGORY_SET_ID,
CATEGORY_ID,
last_update_date,
creation_date,
ORGANIZATION_ID,
PROCESS_FLAG,
CATEGORY_SET_NAME,
CATEGORY_NAME,
ORGANIZATION_CODE,
ITEM_NUMBER,
TRANSACTION_TYPE,
SET_PROCESS_ID)
values (null,
l_cat_set_id,
l_cat_id,
trunc(sysdate),
trunc(sysdate),
l_mst_org_id,
'1',
'Product Family',
null,
l_mst_org_code,
c1_rec.s_segment1,
'CREATE',
-11);
exception
when others then
l_sqlerrm := substr(sqlerrm,1,200);
l_sqlcode := to_char(sqlcode);
retcode:= 2;
dbms_output.put_line('Error on insert of MTL_ITEM_CATEGORIES_INTERFACE '||' Error: '||l_sqlcode||' : '||l_sqlerrm);
update xxsre_item_staging
set s_process_flag = 'E',
s_error_mesg = 'Error on insert of MTL_ITEM_CATEGORIES_INTERFACE '||' Error: '||l_sqlcode||' : '||l_sqlerrm
where rowid = c1_rec.rowid;
l_err_status := 'Y';
end;
end if; --MST ORG Exists
--FINALLY UPDATING THE STAGING TABLE FOR PROCESSED AND ERROR FLAG
------------------------------------------------------------
----------- UPDATE ITEM CONV STAGING TABLE SUCCESS
------------------------------------------------------------
if l_err_status = 'N' then
update xxsre_item_staging
set s_process_flag = 'P'
where rowid = c1_rec.rowid;
end if;
commit;
END LOOP;
end if;
dbms_output.put_line('Completed');
EXCEPTION
WHEN others THEN
l_sqlerrm := substr(sqlerrm,1,200);
l_sqlcode := to_char(sqlcode);
retcode:= 2;
dbms_output.put_line('Other '||' Error: '||l_sqlcode||' : '||l_sqlerrm);
l_err_status := 'Y';
end;
end XXSRE_items_import_pkg;