Oracle Item Conversions

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%';