Item Conversion
Item conversion is nothing but importing the item and its details from staging table to interface table with some validations and then from interface table to base table. Items from staging table to interface table is done by below procedure with 3 validations and run the concurrent program(Import Items) to import the items from interface table to base table.
Staging table creation: -
create table Item_staging_tbl(segment1 varchar2(40),
description varchar2(150),
template_name varchar2(40),
organization_code varchar2(3),
process_flag char(1),
error_message varchar2(2000),
creation_date date,
created_by number,
last_update_date date,
last_updated_by number);
Inserting records into staging table:-
INSERT INTO Item_staging_tbl (SEGMENT1,DESCRIPTION,TEMPLATE_NAME,ORGANIZATION_CODE,process_flag,creation_date,created_by,last_update_date,last_updated_by)
vALUES('909090','Tower','Purchased Item','V1','N',SYSDATE,'-1',sysdate,'-1');
INSERT INTO Item_staging_tbl (SEGMENT1,DESCRIPTION,TEMPLATE_NAME,ORGANIZATION_CODE,process_flag,creation_date,created_by,last_update_date,last_updated_by)
VALUES('909091','Leg','Purchased Item','V1','N',SYSDATE,'-1',sysdate,'-1');
INSERT INTO Item_staging_tbl (SEGMENT1,DESCRIPTION,TEMPLATE_NAME,ORGANIZATION_CODE,process_flag,creation_date,created_by,last_update_date,last_updated_by)
VALUES('909092','Bracing','Purchased Item','V1','N',SYSDATE,'-1',sysdate,'-1');
INSERT INTO Item_staging_tbl (SEGMENT1,DESCRIPTION,TEMPLATE_NAME,ORGANIZATION_CODE,process_flag,creation_date,created_by,last_update_date,last_updated_by)
VALUES('909093','Stub','Purchased Item','V1','N',SYSDATE,'-1',sysdate,'-1');
COMMIT;
SELECT * FROM Item_staging_tbl;
----------------------------------------------------------------------------------------
-----------------------------------------Package Specification------------------------------------
create or replace package Pkg_Item_import
is
procedure Prc_item_import(errbuf out varchar2,
retcode out number);
end;
/
------------------------------------------PAckage Body-----------------------------------------------
package body Pkg_Item_import
is
procedure Prc_item_import(errbuf out varchar2,
retcode out number)
is
n_segment1 varchar2(40);
n_organization_id number;
lv_template_name varchar2(40);
n_error_count number:=0;
g_error_message varchar2(2000):='';
n_record_count number:=0;
n_master_count number:=0;
n_child_count number:=0;
n_request_id number := 0;
xx_org varchar2(40):=NULL;
no_org number:=0;
begin
for i in(select a.rowid row_id,a.* from ITEM_STAGING_TBL a where a.process_flag='N')
loop
no_org:=0;
n_master_count:=0;
DBMS_OUTPUT.PUT_LINE('1. '||I.ORGANIZATION_CODE);
select organization_id into n_organization_id from mtl_parameters where organization_code = i.organization_code;
FOR ix in (select organization_code from Item_staging_tbl xx where xx.segment1 = i.segment1)loop
IF n_master_count<>0 THEN
CONTINUE;
ELSE
DBMS_OUTPUT.PUT_LINE('2. '||IX.ORGANIZATION_CODE);
n_error_count:=0;
n_segment1:=0;
begin
select organization_code into xx_org from mtl_parameters where organization_id =
(select master_organization_id from mtl_parameters where organization_code = i.organization_code);
exception when others
then
n_error_count:=1;
end;
DBMS_OUTPUT.PUT_LINE('3. '||XX_ORG);
if xx_org = ix.organization_code then
no_org:=1;
DBMS_OUTPUT.PUT_LINE('Entered if');
lv_template_name := null;
n_segment1 := null;
n_record_count:=n_record_count+1;
--------------------------------Item Code is Existed or Not----------------------------------
Begin
select 1 into n_segment1
from mtl_system_items_b msib
where segment1 = i.segment1
and organization_id = n_organization_id;
if n_segment1<>0 then
n_error_count:=1;
g_error_message:='Item Code is Existed';
DBMS_OUTPUT.PUT_LINE('N_SEGMENT1: '||n_error_count);
end if;
Exception
when others then
n_error_count:=0;
null;
End;
----------------------------------------Tempalate Name is Existed or not-----------------------------------------
Begin
select template_name into lv_template_name
from mtl_item_templates
where template_name = i.template_name;
Exception
when others then
n_error_count:=1;
g_error_message:=g_error_message||','||'Template name is not existed';
DBMS_OUTPUT.PUT_LINE('n_template: '||n_error_count);
End;
------------------------------------------------------------------------------------------------------
DBMS_OUTPUT.PUT_LINE('ERROR COUNT: '||n_error_count);
if n_error_count <> 0 then
continue;
else
n_master_count:=n_master_count+1;
insert into mtl_system_items_interface(
segment1,
description,
template_name,
organization_code,
organization_id,
transaction_type,
process_flag,
creation_date,
created_by,
last_update_date,
last_updated_by
)
values(
i.segment1,
i.description,
i.template_name,
i.organization_code,
(select organization_id from mtl_parameters where organization_code = i.organization_code),
'CREATE',
1,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.user_id
);
if sql%rowcount<>0 then
update Item_staging_tbl set process_flag = 'S'
where rowid = i.row_id;
commit;
end if;
end if;
end if;-- organization verification ends here
end if;-- record insertion verification ends here
end loop;
if n_master_count = 0 then
update Item_staging_tbl set process_flag = 'E', ERROR_MESSAGE = g_error_message
where rowid = i.row_id;
end if;
if no_org = 0 then
update Item_staging_tbl set process_flag = 'E', ERROR_MESSAGE = error_message||' '||'This item has no Master Org'
where rowid = i.row_id;
end if;
end loop;
if n_record_count=0 then
retcode:=1;
end if;
fnd_file.put_line(fnd_file.log,'**********************Covad Item Import Interface program started**********************');
fnd_file.put_line(fnd_file.log,'Total master records processed: '||n_master_count);
fnd_file.put_line(fnd_file.log,'Total Child records processed: '||n_child_count);
fnd_file.put_line(fnd_file.log,'**********************Covad Item Import Interface program ended**********************');
n_request_id := fnd_request.submit_request('INV', 'INCOIN', 'To submit item import from backend', null, false,
fnd_profile.value('MFG_ORGANIZATION_ID'),
1,
1,
1,
1,
null,
1);
commit;
end Prc_item_import;
end Pkg_Item_import;
/
Now run the below pl/sql to import the items from staging table to interface table.
declare
a varchar2(32767);
b number;
begin
covad_item_import_pkg.covad_item_import_proc(a,b);
dbms_output.put_line(a||b);
end;
/
Now check the items in mtl_system_items_interface which are loaded from the above procedure by using below select query.
SELECT * FROM mtl_system_items_interface;
Now to import the items from interface table to base table run the below concurrent program
GOTO INVENTORY-> ITEMS -> IMPORT -> Import Items(all organizations make this field to no and submit the concurrent program)
check the items whether created or not
select * From mtl_system_items_b where segment1 like'90909%';
No comments:
Post a Comment