Wednesday, July 25, 2012

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


No comments:

Post a Comment

Best Blogger TipsGet Flower Effect