Create Custom Table : XX_ITEM_INTERFACE_STG_DEBUG
Insert Item information:
SET DEFINE OFF;
Insert into XX_ITEM_INTERFACE_STG_DEBUG
(SEGMENT1, DESCRIPTION, PRIMARY_UOM_CODE, PROCESS_QUALITY_ENABLED_FLAG, PROCESS_COSTING_ENABLED_FLAG,
RECIPE_ENABLED_FLAG, PROCESS_EXECUTION_ENABLED_FLAG, I_STSTAUS)
Values
('0628411080', 'TRANSFORMER CAPACITY-220 VA', 'PCS', 'Y', 'Y',
'Y', 'Y', NULL);
COMMIT;
Run Procedure as bellow :
/* Cursor Declaration ***/
declare
cursor c1 is select * from XX_ITEM_INTERFACE_STG_DEBUG ;
/* Variable Declaration **/
l_org_id number(3);
l_uom varchar2(15);
l_suom varchar2(15);
l_item_type varchar2(30);
l_desc varchar2(30);
l_temp_id varchar2(3);
l_sale_ac number(16);
l_exp_ac number(16);
l_cost_sale_ac number(16);
l_item_number varchar2(100);
l_flag varchar2(4) default'A';
l_msg varchar2(200);
begin
--delete from MTL_SYSTEM_ITEMS_INTERFACE;
--commit;
for x1 in c1 loop
/* Validation For Organization */
-- begin
-- select organization_id into l_org_id
-- from ORG_ORGANIZATION_DEFINITIONS
-- where organization_code = x1.organization_code;
-- exception
-- when others then
-- l_flag :='E';
-- l_msg :='Item Interface'||'Organization Id'||l_org_id||' not in system';
-- fnd_file.PUT_LINE(fnd_file.log,'Error Occured'||l_msg);
-- end;
/* Validation for Item Number */
if x1.SEGMENT1 is null then
l_flag :='E';
l_msg :='Item number should not be NULL';
fnd_file.PUT_LINE(fnd_file.log,'Error Occured--'||l_msg);
end if;
/* Validation for Description */
if x1.DESCRIPTION is null then
l_flag :='E';
l_msg :='Item description should not be NULL';
fnd_file.PUT_LINE(fnd_file.log,'Error Occured'||l_msg|| x1.SEGMENT1);
end if;
/* Validation for Unit Of Measure */
if x1.PRIMARY_UOM_CODE is not null then
begin
select UOM_CODE into l_uom
from mtl_units_of_measure
where UOM_CODE =x1.PRIMARY_UOM_CODE;
exception
when others then
l_flag :='E';
l_msg := x1.SEGMENT1||'---'||l_uom ||'UOM is not Valid';
fnd_file.PUT_LINE(fnd_file.log,'Error Occured'||l_msg);
end;
else
l_msg :='Primary UOM should not be NULL ';
fnd_file.PUT_LINE(fnd_file.log,'Error Occured'||l_msg);
end if;
begin
select COST_OF_SALES_ACCOUNT into l_cost_sale_ac
from MTL_PARAMETERS
where ORGANIZATION_ID = 102;
exception
when no_data_found then
l_flag :='E';
l_msg :='Cost of Sales Account should not be Null';
fnd_file.PUT_LINE(fnd_file.log,'Error Occured'||l_msg);
when others then
l_flag :='E';
l_msg :='Cost of Sales Account is not Valid';
fnd_file.PUT_LINE(fnd_file.log,'Error Occured'||l_msg);
end;
begin
select SALES_ACCOUNT into l_sale_ac
from MTL_PARAMETERS
where ORGANIZATION_ID = 102;
exception
when no_data_found then
l_flag :='E';
l_msg :='Sales Account should not be Null';
fnd_file.PUT_LINE(fnd_file.log,'Error Occured'||l_msg);
when others then
l_flag :='E';
l_msg :='Sales Account is not Valid';
fnd_file.PUT_LINE(fnd_file.log,'Error Occured'||l_msg);
end;
begin
select EXPENSE_ACCOUNT into l_exp_ac
from MTL_PARAMETERS
where ORGANIZATION_ID = 102;
exception
when no_data_found then
l_flag :='E';
l_msg :='Expence Account should not be Null';
fnd_file.PUT_LINE(fnd_file.log,'Error Occured'||l_msg);
when others then
l_flag :='E';
l_msg :='Expence Account is not Valid';
fnd_file.PUT_LINE(fnd_file.log,'Error Occured'||l_msg);
end;
if l_flag!='E' then
insert into MTL_SYSTEM_ITEMS_INTERFACE
( ORGANIZATION_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
DESCRIPTION,
SEGMENT1,
PRIMARY_UOM_CODE,
TEMPLATE_ID,
SUMMARY_FLAG,
ENABLED_FLAG,
SALES_ACCOUNT,
COST_OF_SALES_ACCOUNT,
EXPENSE_ACCOUNT,
PROCESS_QUALITY_ENABLED_FLAG,
PROCESS_COSTING_ENABLED_FLAG,
RECIPE_ENABLED_FLAG,
PROCESS_EXECUTION_ENABLED_FLAG,
RESTRICT_SUBINVENTORIES_CODE,
RESTRICT_LOCATORS_CODE ,
LOCATION_CONTROL_CODE ,
SET_PROCESS_ID,
PROCESS_FLAG,
TRANSACTION_TYPE)
values( 102,
sysdate,
fnd_global.USER_ID,
sysdate,
fnd_global.USER_ID,
fnd_global.LOGIN_ID,
x1.DESCRIPTION,
x1.SEGMENT1,
l_uom,
-- l_item_type,
2,
'Y',
'Y',
l_sale_ac,
l_cost_sale_ac,
l_exp_ac,
x1.PROCESS_QUALITY_ENABLED_FLAG,
x1.PROCESS_COSTING_ENABLED_FLAG,
x1.RECIPE_ENABLED_FLAG,
x1.PROCESS_EXECUTION_ENABLED_FLAG,
1,
1,
2,
2,
1,
'CREATE'
);
else
update XX_ITEM_INTERFACE_STG_DEBUG set I_STSTAUS=l_msg where SEGMENT1= x1.SEGMENT1;
end if;
end loop;
commit;
end;
/
Then run Import Item program.
