Item with Item Category Interface

This script will help to migrate Item Master with Item Category details
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

CREATE OR REPLACE PROCEDURE XXX_Item_Master_Org_Api AS

l_err_flag varchar2(3);
l_err_msg varchar2(2500);
l_count number (2);
l_category_set_id number (20);
l_category_id number (20);
l_org_id number (4);
l_template_name varchar2(250);
l_uom varchar2(20);
l_item_type varchar2(20);
l_organization_id number (10);

--CURSOR TO FETCH THE DATA FROM STAGING TABLE
cursor c1 is
select *
from xxx_item_master_stg
where nvl(verify_flag,'N') = 'N' ;

begin

for c_rec in c1 loop

begin
--- initialize the variables
l_err_flag :='Y';
l_err_msg := null;

---*********** MASTER ORGANIZATION VALIDATION
begin
select organization_id
into l_organization_id
from org_organization_definitions
where upper(organization_name) = upper(trim('XXX ITEM MASTER'));
exception
when others then
l_err_flag := 'Y';
l_err_msg := 'INVALID ORGANIZATION' ;
end ;

---*********** ITEM TYPE VALIDATION
begin
select lookup_code
into l_item_type
from fnd_lookup_values
where lookup_type = 'ITEM_TYPE'
and upper(meaning) = trim(upper(c_rec.item_type));
exception
when others then
l_err_flag := 'N';
l_err_msg := l_err_msg || 'INVALID ITEM TYPE' ;
end ;

---*************** ITEM CODE VALIDATION

begin
l_count := 0;
select count(*)
into l_count
from mtl_system_items_b
where upper(trim(segment1)) = upper(trim(c_rec.item_code))
and organization_id = l_organization_id;
if l_count > 0 then
l_err_flag := 'N';
l_err_msg :=l_err_msg || 'ITEM ALREADY EXISTING' ;
end if;

----*********** VALIDATE THE DESCRIPTION
if trim(c_rec.description) is null then
l_err_flag := 'N';
l_err_msg := l_err_msg || 'INVALID DESCRIPTION';
end if;
end;


--- ITEM TEMPLATE VALIDATION

begin
select template_name
into l_template_name
from mtl_item_templates
where upper(trim(template_name)) = upper(trim(c_rec.template_name));
exception
when others then
l_err_flag := 'N';
l_err_msg := l_err_msg||'INVALID TEMPLATE NAME';
end ;
end;

--- PRIMARY UOM VALIDATION
begin
select unit_of_measure
into l_uom
from mtl_units_of_measure
where upper(trim(uom_code)) = upper(trim(c_rec.uom)); ---eg litre, numbers
exception
when others then
l_err_flag := 'N';
l_err_msg := l_err_msg||'INVALID UOM';
end;

--- ITEM CATEGORY VALIDATION
begin
select category_id
into l_category_id
from mtl_categories_v
where segment1 =trim(c_rec.cat_segment1)
and segment2 = trim(c_rec.cat_segment2) ;
exception
when others then
l_err_flag := 'N';
l_err_msg :=l_err_msg||'INVALID CATEGORY';
end;

---************** ITEM CATEGORY SET VALIDATION
begin
select category_set_id
into l_category_set_id
from mtl_category_sets_tl
where category_set_name = trim(c_rec.cat_set_name);
exception
when others then
l_err_flag := 'N';
l_err_msg :=l_err_msg||'INVALID CATEGORY SET';
end;


--- ITEM CATEGORY COMBINATION VALIDATION
begin
l_count := 0;
select count(*)
into l_count
from mtl_category_set_valid_cats_v
where category_set_id = l_category_set_id
and category_id = l_category_id ;
if l_count = 0 then
l_err_flag := 'N';
l_err_msg :=l_err_msg || 'INVALID CATEGORY COMBINATION' ;
end if;
exception
when others then
l_err_flag := 'N';
l_err_msg :=l_err_msg||'INVALID CATEGORY COMBINATION';
end;



---- IF ALL VALIDATIONS ARE CORRECT, THEN INSERT DATA INTO INTERFACE TABLE
---- THEN BY USING IMPORT ITEM CONCURRENT PGM, IMPORT ITEMS FROM INTERFACE TABLE INTO BASE TABLE

if l_err_flag='Y' then
begin

insert into mtl_system_items_interface
(
item_type
,process_flag
,segment1
,description
,primary_unit_of_measure
,set_process_id
,template_name
,organization_id
,transaction_type
)
values
(
l_item_type
,1
,c_rec.item_code
,c_rec.description
,l_uom
,3
,l_template_name
,l_organization_id
,'CREATE'
);


insert into mtl_item_categories_interface
(
item_number
,category_set_id
,category_id
,process_flag
,organization_id
,set_process_id
,transaction_type
)
values
(
c_rec.item_code
,l_category_set_id
,l_category_id
,1
,l_organization_id
,3
,'CREATE'
);

update xxx_item_master_stg
set verify_flag = 'Y',
err_msg = null
where item_code = c_rec.item_code ;
commit;
exception
when others then
l_err_msg := sqlerrm;
update xxx_item_master_stg
set verify_flag = 'N'
,err_msg = l_err_msg
where item_code = c_rec.item_code ;
end;

commit;

else

update xxx_item_master_stg
set verify_flag = 'N'
,err_msg = l_err_msg
where item_code = c_rec.item_code ;

end if;
commit;
end loop;

commit;

exception
when others then
dbms_output.put_line(sqlerrm);

end xxx_item_master_org_api;
/