Upload Items into Master and child Orgs

[keerti_rose]

Hi Friends,

I am going to do Item Conversion.
Can any one Explain me How to upload Items information.
As per my Knowledge First we have to upload the data into Master org and then we have to upload child org.
How we have to do this?.

[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;