Showing posts with label BOM conversion. Show all posts
Showing posts with label BOM conversion. Show all posts

Friday, September 24, 2010

Bills Of Material(BOM) Conversion

CREATE OR REPLACE PACKAGE BODY
                    APPS.ftxs_118c_bom_conv_pkg
AS
/*****************************************************************
*  
******************************************************************
* $Header: $
******************************************************************
*
*   Filename: ftxs_118c_bom_conv_pkg.pkb
*
*   Purpose : This Package contains procedures and
*          to be used for Bom and Routing  Conversion.
*
*****************************************************************/

--**********************************************************************************************
  --This Procedure is used to validate the data in the FTXS_118C_BOM_HEADER_TEMP Staging Table ---
  --**********************************************************************************************
PROCEDURE  validate_headers
as
l_head_count   number;
v_org      varchar2(20);
l_err_msg     varchar2(1000);
l_item       number;
l_valid_head_count number;
l_org_id     number;
l_err_count  number;


CURSOR  bom_head_cur  IS SELECT fbh.* , rowid row_id
                        FROM FTXS_118C_BOM_HEADER_TEMP  fbh
                        WHERE status_flag = 'N';
   
CURSOR  bom_head_err_cur  IS SELECT fbh.* , rowid row_id
                        FROM FTXS_118C_BOM_HEADER_TEMP  fbh
                        WHERE status_flag = 'E';
                   
BEGIN
  l_head_count := 0;
  l_valid_head_count := 0;
  l_err_count  :=0;
  FOR bom_head_rec in bom_head_cur
  LOOP
  l_head_count := l_head_count + 1;
  l_item := null;
  l_err_msg := NULL;
  v_org := 0;
  l_item := 0;
 
   BEGIN
   
 
    
         --  fetch the organization_code
        BEGIN
         SELECT organization_code INTO v_org  FROM
         ftxs_118c_inv_org_map
           WHERE  source_inv_org =bom_head_rec. organization_code;
        EXCEPTION
          WHEN no_data_found THEN
             l_err_msg := 'No organization_code has mapped for this ' || ' - '||bom_head_rec. organization_code;
           WHEN others THEN
             l_err_msg := SQLCODE || ' - ' || SQLERRM;
        END; 
           --   Validate Organization_code
          
      BEGIN     
         SELECT organization_id INTO l_org_id
         FROM mtl_parameters WHERE organization_code = v_org;
      EXCEPTION
        WHEN  no_data_found THEN
          l_err_msg := l_err_msg || ' - '||bom_head_rec.organization_code||'Organization code does not exists ';

         
        WHEN others THEN
          l_err_msg := l_err_msg || ' - '||bom_head_rec.organization_code||SQLCODE||'-'||SQLERRM;

      END;
     
     
      --  Validate item Number
     
      Begin
    
        SELECT  inventory_item_id INTO
                l_item
          FROM  mtl_system_items_b
          WHERE organization_id = l_org_id
          AND  segment1 = bom_head_rec.item_number
          AND   bom_enabled_flag = 'Y';
         
      EXCEPTION
        WHEN  no_data_found THEN
          l_err_msg := l_err_msg || ' - '|| bom_head_rec.item_number||'Item Number does not exists ';

         
        WHEN others THEN
          l_err_msg := l_err_msg || ' - '||bom_head_rec.item_number||SQLCODE||'-'||SQLERRM;

      END;   
     
     
     IF l_err_msg is NULL THEN     
      l_valid_head_count := l_valid_head_count + 1;
     
      UPDATE  FTXS_118C_BOM_HEADER_TEMP 
      SET    status_flag = 'V'
            ,err_msg = null
      WHERE  rowid = bom_head_rec.row_id;
     
     ELSE
      UPDATE  FTXS_118C_BOM_HEADER_TEMP 
      SET    status_flag = 'E'
            ,err_msg = l_err_msg
      WHERE  rowid = bom_head_rec.row_id;
      l_err_count := l_err_count + 1;
     
      END IF;
     
   EXCEPTION
     
  
     
   WHEN others THEN
  
      UPDATE  FTXS_118C_BOM_HEADER_TEMP 
      SET    status_flag= 'E'
            ,err_msg = l_err_msg
      WHERE  rowid = bom_head_rec.row_id;
      l_err_count := l_err_count + 1;
   END;  
  
   IF MOD(l_valid_head_count,100) = 0 THEN
  
     COMMIT;
    
   END IF; 
  
  END LOOP;
 
  COMMIT;
    Fnd_file.put_line(fnd_file.log,'**************************************************');     
    Fnd_file.put_line(fnd_file.log,'Total no of records inserted:'||l_valid_head_count);
    Fnd_file.put_line(fnd_file.log,'Total no of records errored :'||l_err_count);
    Fnd_file.put_line(fnd_file.log,'**************************************************');    
 
 
 
  IF l_err_count >=1 then
    Fnd_file.put_line(fnd_file.log,'**************************************************');
    FOR bom_head_err_rec IN bom_head_err_cur
    LOOP
      Fnd_file.put_line(fnd_file.log,bom_head_err_rec.err_msg);
    END LOOP; 
  Fnd_file.put_line(fnd_file.log,'**************************************************'); 
  END IF;
 
 END validate_headers;
   


--**********************************************************************************************
--This is Procedure is used to move the data from staging table to interface table
--*********************************************************************************************
PROCEDURE Insert_headers
 AS
    l_userid   NUMBER:=fnd_global.user_id;
    l_count     NUMBER;
    v_date      DATE := SYSDATE;
    l_err_msg    varchar2(1000);
    l_err_count number;



  CURSOR ins_headers_cur IS SELECT fbh.* ,rowid row_id
                             FROM FTXS_118C_BOM_HEADER_TEMP fbh
                             WHERE status_flag = 'V';

   CURSOR ins_headers_err_cur IS SELECT fbh.* ,rowid row_id
                             FROM FTXS_118C_BOM_HEADER_TEMP fbh
                             WHERE  status_flag = 'E';

 BEGIN

   l_count := 0;
   l_err_count :=0;
  
  
 


    FOR  ins_headers_rec in ins_headers_cur
    LOOP
     BEGIN
       l_err_msg := null;

     INSERT INTO bom_bill_of_mtls_interface
                (organization_code
                ,assembly_type
                ,process_flag
                ,item_number
                ,transaction_type
                ,last_update_date
                ,last_updated_by
                ,creation_date
                ,created_by
                ,last_update_login
                )
        VALUES (ins_headers_rec.organization_code
               ,ins_headers_rec.assembly_type
               ,1
               ,ins_headers_rec.item_number
               ,'CREATE'
               ,v_date
               ,l_userid
               ,v_date
               ,l_userid
               ,l_userid
               );
              
               l_count := l_count + 1;
          
         IF mod(l_count,100) = 0 THEN
  
           COMMIT;
    
         END IF;      
              
     EXCEPTION
       WHEN others THEN
          l_err_msg := SQLCODE || ' - '|| SQLERRM;
         
                UPDATE  FTXS_118C_BOM_HEADER_TEMP 
                  SET    status_flag= 'E'
                         ,err_msg = l_err_msg
                  WHERE  rowid = ins_headers_rec.row_id;
               l_err_count := l_err_count + 1;
         
     END;    
              
              
              
    END LOOP;
    COMMIT;
    Fnd_file.put_line(fnd_file.log,'**************************************************');     
    Fnd_file.put_line(fnd_file.log,'Total no of records inserted:'||l_count);
    Fnd_file.put_line(fnd_file.log,'Total no of records errored :'||l_err_count);
    Fnd_file.put_line(fnd_file.log,'**************************************************');
       
  IF l_err_count >=1 then
    Fnd_file.put_line(fnd_file.log,'**************************************************');
    FOR ins_headers_err_rec IN ins_headers_err_cur
    LOOP
      Fnd_file.put_line(fnd_file.log,ins_headers_err_rec.err_msg);
    END LOOP; 
    Fnd_file.put_line(fnd_file.log,'**************************************************'); 
  END IF;
 
 
    END Insert_headers;

                  
 --**********************************************************************************************
 --This Procedure is used to validate the data in the FTXS_118C_BOM_COMPS_TEMP Staging Table --- 
 --**********************************************************************************************                    
PROCEDURE  validate_bom_comp
AS
l_comp_count   number;
v_org        varchar2(20);
l_err_msg     varchar2(1000);
l_item       number;
l_org_id     number;
l_valid_comp_count number;
v_num_comp_inv_id  number;
l_err_count    number;

CURSOR  bom_comp_cur  IS SELECT fbc.* 
                               ,rowid row_id
                        FROM FTXS_118C_BOM_COMPS_TEMP  fbc
                        WHERE status_flag='N';
CURSOR  bom_comp_err_cur  IS SELECT fbc.* 
                               ,rowid row_id
                        FROM FTXS_118C_BOM_COMPS_TEMP  fbc
                        WHERE status_flag='E';
                       
BEGIN
  l_comp_count := 0;
  l_valid_comp_count := 0;
  l_err_count := 0;
  FOR bom_comp_rec in bom_comp_cur
  LOOP
  l_comp_count := l_comp_count + 1;
  l_err_msg := NULL;
  v_org := NULL;
  l_item := NULL;
  l_org_id := NULL;
  v_num_comp_inv_id:=NULL;
  l_err_count := 0;
 
   BEGIN
   
         --  fetch the organization_code
        BEGIN
         SELECT organization_code INTO v_org  FROM
         ftxs_118c_inv_org_map
           WHERE source_inv_org =bom_comp_rec. organization_code;
        EXCEPTION
          WHEN no_data_found THEN
             l_err_msg := 'No organization_code has mapped for this ' || ' - '||bom_comp_rec. organization_code;
           WHEN others THEN
             l_err_msg := bom_comp_rec. organization_code || ' - ' || SQLCODE || ' - ' || SQLERRM;
        END; 
 
      --   Validate Organization_code
      BEGIN
     
         SELECT organization_id INTO l_org_id
         FROM mtl_parameters WHERE organization_code = v_org;
          
      EXCEPTION
        WHEN  no_data_found THEN
          l_err_msg := l_err_msg || ' - ' ||bom_comp_rec.organization_code|| ' - '||'Organization code does not exists ';

         
        WHEN others THEN
          l_err_msg := l_err_msg || ' - ' ||bom_comp_rec.organization_code|| ' - ' ||SQLCODE||'-'||SQLERRM;

      END;
     
     
      --  Validate Assembly item Number ---------
     
      BEGIN
    
        SELECT  inventory_item_id INTO
                l_item
          FROM  mtl_system_items_b
          WHERE  organization_id = l_org_id
          AND   segment1 = bom_comp_rec.item_number
          AND   bom_enabled_flag = 'Y';
         
      EXCEPTION
        WHEN  no_data_found THEN
          l_err_msg := l_err_msg || ' - ' ||bom_comp_rec.item_number||'Item Number does not exists ';

         
        WHEN others THEN
          l_err_msg := l_err_msg|| ' - ' ||bom_comp_rec.item_number|| ' - '||SQLCODE||'-'||SQLERRM;

      END;   
     
     
      --validate  component item number----------------------------
     
     BEGIN
         SELECT inventory_item_id
         INTO v_num_comp_inv_id
         FROM mtl_system_items_b
         WHERE segment1 = bom_comp_rec.component_item_number
         AND organization_id = l_org_id
         AND bom_enabled_flag = 'Y';
    
     EXCEPTION
       WHEN  no_data_found THEN
          l_err_msg := l_err_msg|| ' - ' ||bom_comp_rec.component_item_number||'Error in getting the inventory item id for the component item Number ';

         
        WHEN others THEN
          l_err_msg := l_err_msg|| ' - ' ||bom_comp_rec.organization_code||SQLCODE||'-'||SQLERRM;

      END;
 
    IF l_err_msg IS NULL THEN
        l_valid_comp_count := l_valid_comp_count + 1;
     
      UPDATE  FTXS_118C_BOM_COMPS_TEMP 
      SET    status_flag = 'V'
            ,err_msg = null
      WHERE  rowid = bom_comp_rec.row_id;
    ELSE
      UPDATE  FTXS_118C_BOM_COMPS_TEMP 
      SET    status_flag = 'E'
            ,err_msg = l_err_msg
      WHERE  rowid = bom_comp_rec.row_id;
      l_err_count:=l_err_count + 1;
     
     
    END IF; 
     
   EXCEPTION
     
     
   WHEN others THEN
  
      UPDATE  FTXS_118C_BOM_COMPS_TEMP 
      SET    status_flag = 'E'
            ,err_msg = l_err_msg
      WHERE  rowid = bom_comp_rec.row_id;
      l_err_count :=l_err_count + 1;
     
   END;  
  
   IF MOD(l_valid_comp_count,100) = 0 THEN
  
     COMMIT;
    
   END IF; 
  
  END LOOP;
 
  COMMIT;
  Fnd_file.put_line(fnd_file.log,'**************************************************');  
  Fnd_file.put_line(fnd_file.log,'Total no of records fetched :'||l_comp_count);
  Fnd_file.put_line(fnd_file.log,'Total no of valid records     :'||l_valid_comp_count);
  Fnd_file.put_line(fnd_file.log,'Total no of errored records   :'||l_err_count); 
  Fnd_file.put_line(fnd_file.log,'**************************************************');  
       
  IF l_err_count >=1 THEN
    Fnd_file.put_line(fnd_file.log,'**************************************************');
    FOR bom_comp_err_rec IN bom_comp_err_cur
    LOOP
      Fnd_file.put_line(fnd_file.log,bom_comp_err_rec.err_msg);
    END LOOP; 
    Fnd_file.put_line(fnd_file.log,'**************************************************'); 
  END IF;

 
 END validate_bom_comp;
   

 --**********************************************************************************************
--This is Procedure is used to move the data from staging table to interface tables
--*********************************************************************************************
 PROCEDURE Insert_bom_comp
 as

  l_userid   NUMBER:=fnd_global.user_id;
  v_date      date := SYSDATE;
  l_count     number;
  l_err_msg    varchar2(1000);
  l_err_count   number;
 
 

  CURSOR ins_comp_cur IS SELECT fbc.* ,rowid row_id
                             FROM FTXS_118C_BOM_COMPS_TEMP fbc
                             WHERE status_flag = 'V';
  CURSOR ins_comp_err_cur IS SELECT fbc.* ,rowid row_id
                             FROM FTXS_118C_BOM_COMPS_TEMP fbc
                             WHERE status_flag = 'E';


 BEGIN

   l_count := 0;
   l_err_count := NULL;
  


    FOR  ins_comp_rec IN ins_comp_cur
    LOOP
   
    BEGIN
     l_err_msg := NULL;

    INSERT INTO bom_inventory_comps_interface
                ( organization_code   
                 ,assembly_item_number         
                 ,operation_seq_num    
                 ,component_item_number
                 ,component_quantity    
                 ,wip_supply_type       
                 ,supply_subinventory 
                 ,process_flag        
                 ,transaction_type
                 ,last_update_date
                 ,last_updated_by
                 ,creation_date
                 ,created_by
                 ,last_update_login
                )
        VALUES (ins_comp_rec.organization_code
               ,ins_comp_rec.item_number
               ,ins_comp_rec.operation_seq_num
               ,ins_comp_rec.component_item_number
               ,ins_comp_rec.component_quantity
               ,ins_comp_rec.wip_supply_type
              ,ins_comp_rec.supply_subinventory
               ,1
               ,'CREATE'
               ,v_date
               ,l_userid
               ,v_date
               ,l_userid
               ,l_userid
               );
              
               l_count := l_count + 1;
    
   
       IF MOD(l_count,100) = 0 THEN
  
         COMMIT;
    
       END IF; 
    EXCEPTION
      WHEN others THEN
        l_err_msg := l_err_msg || ' - ' ||SQLCODE|| ' - ' ||SQLERRM;         
      UPDATE  FTXS_118C_BOM_COMPS_TEMP 
      SET    status_flag = 'E'
            ,err_msg = l_err_msg
      WHERE  rowid = ins_comp_rec.row_id;
      l_err_count := l_err_count +1;
              
    END;          
    End LOOP;
    Fnd_file.put_line(fnd_file.log,'**************************************************');     
    Fnd_file.put_line(fnd_file.log,'Total no of records inserted:'||l_count);
    Fnd_file.put_line(fnd_file.log,'Total no of records errored :'||l_err_count);
    Fnd_file.put_line(fnd_file.log,'**************************************************');
       
  IF l_err_count >=1 then
    Fnd_file.put_line(fnd_file.log,'**************************************************');
    FOR ins_comp_err_rec IN ins_comp_err_cur
    LOOP
      Fnd_file.put_line(fnd_file.log,ins_comp_err_rec.err_msg);
    END LOOP; 
    Fnd_file.put_line(fnd_file.log,'**************************************************'); 
  END IF;
    
 END Insert_bom_comp;
   
   
   
--**********************************************************************************************
 --This Procedure is used to validate the data in the FTXS_118C_ROUTING_HEADER_TEMP Staging Table ---
 --**********************************************************************************************
PROCEDURE  validate_rout_headers
AS
l_rout_count   number;
V_org         varchar2(10);
l_err_msg     varchar2(1000);
l_item         number;
l_valid_rout_count number;
l_err_count    number;
l_org_id      number;

CURSOR  bom_rout_head_cur  IS SELECT frh.*
                              ,rowid row_id
                        FROM FTXS_118C_ROUTING_HEADER_TEMP  frh
                        WHERE  status_flag='N';
                       
CURSOR  bom_rout_head_err_cur  IS SELECT frh.*
                              ,rowid row_id
                        FROM FTXS_118C_ROUTING_HEADER_TEMP  frh
                       WHERE status_flag='E';                       
                       
BEGIN
  l_rout_count := 0;
  l_valid_rout_count := 0;
  l_err_count := 0;
  FOR bom_rout_head_rec IN bom_rout_head_cur
  LOOP
  l_rout_count  := l_rout_count  + 1;
  l_err_msg := NULL;
  v_org := NULL;
  l_item := NULL;
  l_org_id := NULL;
 
   BEGIN
  
              --  fetch the organization_code
        BEGIN
         SELECT  organization_code INTO v_org  FROM
         ftxs_118c_inv_org_map
           WHERE source_inv_org =bom_rout_head_rec.organization_code;
        EXCEPTION
          WHEN no_data_found THEN
             l_err_msg := 'No organization_code has mapped for this ' || ' - '||bom_rout_head_rec.organization_code;
           WHEN others THEN
             l_err_msg := SQLCODE || ' - ' || SQLERRM;
        END; 
           --   Validate Organization_code
          
      BEGIN  
        
         SELECT organization_id INTO l_org_id
         FROM mtl_parameters where organization_code = v_org;
      Exception
        WHEN  no_data_found THEN
          l_err_msg := l_err_msg || ' - '||bom_rout_head_rec.organization_code||'Organization code does not exists ';

         
        WHEN others THEN
          l_err_msg := l_err_msg || ' - '||bom_rout_head_rec.organization_code||SQLCODE||'-'||SQLERRM;

      END;
     
  
   
 
   
     
      --  Validate assembly item Number -------
     
     
     
      BEGIN
    
        SELECT  inventory_item_id INTO
                l_item
          FROM  mtl_system_items_b
          WHERE  organization_id = l_org_id
          AND   segment1 = bom_rout_head_rec.item_number
          AND   bom_enabled_flag = 'Y';
         
      EXCEPTION
        WHEN  no_data_found THEN
          l_err_msg := l_err_msg || ' - ' ||bom_rout_head_rec.item_number||'Item Number does not exists ';

         
        WHEN others THEN
          l_err_msg := l_err_msg|| ' - ' ||bom_rout_head_rec.item_number|| ' - '||SQLCODE||'-'||SQLERRM;

      END;   
  
     
     IF l_err_msg IS NULL THEN
           
      l_valid_rout_count := l_valid_rout_count + 1;
     
      UPDATE   FTXS_118C_ROUTING_HEADER_TEMP
      SET   status_flag = 'V'
            ,err_msg = null
      WHERE  rowid = bom_rout_head_rec.row_id;
     ELSE
      UPDATE   FTXS_118C_ROUTING_HEADER_TEMP 
      SET    status_flag = 'E'
            ,err_msg = l_err_msg
      WHERE  rowid = bom_rout_head_rec.row_id;
      l_err_count := l_err_count+1;
     
     END IF;
         
     
   EXCEPTION
     
   WHEN others THEN
  
      UPDATE   FTXS_118C_ROUTING_HEADER_TEMP
      SET   status_flag = 'E'
            ,err_msg = l_err_msg
      WHERE  rowid = bom_rout_head_rec.row_id;
      l_err_count :=l_err_count + 1;
     
   END;  
  
   IF MOD(l_valid_rout_count,100) = 0 THEN
  
     COMMIT;
    
   END IF; 
  
  END LOOP;
 
  COMMIT;

  Fnd_file.put_line(fnd_file.log,'**************************************************');  
  Fnd_file.put_line(fnd_file.log,'Total no of records fetched   :'||l_rout_count);
  Fnd_file.put_line(fnd_file.log,'Total no of valid records     :'||l_valid_rout_count);
  Fnd_file.put_line(fnd_file.log,'Total no of errored records   :'||l_err_count); 
  Fnd_file.put_line(fnd_file.log,'**************************************************');  
       
  IF l_err_count >=1 THEN
    Fnd_file.put_line(fnd_file.log,'**************************************************');
    FOR bom_rout_head_err_rec IN bom_rout_head_err_cur
    LOOP 
      Fnd_file.put_line(fnd_file.log,bom_rout_head_err_rec.err_msg);
    END LOOP; 
    Fnd_file.put_line(fnd_file.log,'**************************************************'); 
  END IF;
 
 END  validate_rout_headers;
   
 --**********************************************************************************************
--This is Procedure is used to move the data from staging table to interface tables
--*********************************************************************************************
 PROCEDURE Insert_rout_headers
 AS

 l_userid   NUMBER:=0;
  v_date      date := SYSDATE;
  l_count     number;
  l_err_msg      varchar2(1000); 
  l_err_count    number;
 
 

  CURSOR ins_rout_header_cur IS SELECT frh.* ,rowid row_id
                             FROM  FTXS_118C_ROUTING_HEADER_TEMP frh
                             WHERE status_flag = 'V';

  CURSOR ins_rout_header_err_cur IS SELECT frh.* ,rowid row_id
                                FROM  FTXS_118C_ROUTING_HEADER_TEMP frh
                                WHERE status_flag = 'E';


 BEGIN

   l_count := 0;
   l_userid := fnd_global.user_id;
   l_err_count :=0;


    FOR  ins_rout_header_rec IN ins_rout_header_cur
    LOOP
   
    BEGIN
    l_err_msg := NULL;

    INSERT INTO bom_op_routings_interface
                (organization_code     
                ,assembly_item_number       
                ,routing_type        
                ,process_flag
                ,transaction_type
                ,last_update_date
                ,last_updated_by
                ,creation_date
                ,created_by
                ,last_update_login
                )
        VALUES (ins_rout_header_rec.organization_code
               ,ins_rout_header_rec.item_number
               ,ins_rout_header_rec.routing_type
               ,1
               ,'CREATE'
               ,v_date
               ,l_userid
               ,v_date
               ,l_userid
               ,l_userid
               );
              
               l_count := l_count + 1;
    EXCEPTION
       WHEN  others THEN
          UPDATE   FTXS_118C_ROUTING_HEADER_TEMP
          SET    status_flag = 'E'
            ,err_msg = l_err_msg
          WHERE  rowid = ins_rout_header_rec.row_id;
          l_err_count := l_err_count + 1;
           
    END;         
              
    END LOOP;
   
    Fnd_file.put_line(fnd_file.log,'**************************************************');     
    Fnd_file.put_line(fnd_file.log,'Total no of records inserted:'||l_count);
    Fnd_file.put_line(fnd_file.log,'Total no of records errored :'||l_err_count);
    Fnd_file.put_line(fnd_file.log,'**************************************************');
       
  IF l_err_count >=1 THEN
    Fnd_file.put_line(fnd_file.log,'**************************************************');
    FOR ins_rout_header_err_rec IN ins_rout_header_err_cur
    LOOP
      Fnd_file.put_line(fnd_file.log,ins_rout_header_err_rec.err_msg);
    END LOOP; 
    Fnd_file.put_line(fnd_file.log,'**************************************************'); 
  END IF;
  
    
    END Insert_rout_headers;
   
   
--**********************************************************************************************
 --This Procedure is used to validate the data in the FTXS_118C_ROUTING_OPS_TEMP Staging Table ---
 --**********************************************************************************************   
 PROCEDURE  validate_routing_ops
AS
l_rout_ops_count   number;
V_org        varchar2(20);
l_err_msg     varchar2(1000);
l_item       number;
l_valid_rout_ops_count number;
v_num_dept_id   number;
l_org_id       number;
l_err_count   number;

CURSOR  bom_routing_ops_cur  IS SELECT fro.*    
                              ,rowid row_id
                        FROM  FTXS_118C_ROUTING_OPS_TEMP  fro
                        WHERE status_flag='N';
                       
CURSOR  bom_routing_ops_err_cur  IS SELECT fro.*    
                              ,rowid row_id
                        FROM  FTXS_118C_ROUTING_OPS_TEMP  fro
                        WHERE status_flag='E';                       
                       
BEGIN
  l_rout_ops_count := 0;
  l_valid_rout_ops_count := 0;
  l_err_count := 0;
  FOR bom_routing_ops_rec in bom_routing_ops_cur
  LOOP
  l_rout_ops_count  :=l_rout_ops_count  + 1;
  l_err_msg := NULL;
  v_org := NULL;
  l_item := NULL;
  l_org_id := NULL;
  v_num_dept_id:=NULL;
 
   BEGIN
   
                 --  fetch the organization_code
        BEGIN
         SELECT organization_code INTO v_org  FROM
         ftxs_118c_inv_org_map
           WHERE source_inv_org =bom_routing_ops_rec.organization_code;
        EXCEPTION
          WHEN no_data_found THEN
             l_err_msg := 'No organization_code has mapped for this ' || ' - '||bom_routing_ops_rec.organization_code;
           WHEN others THEN
             l_err_msg := SQLCODE || ' - ' || SQLERRM;
        END; 
           --   Validate Organization_code
          
      BEGIN     
         SELECT organization_id INTO l_org_id
         FROM mtl_parameters WHERE organization_code = v_org;
      Exception
        WHEN  no_data_found THEN
          l_err_msg := l_err_msg || ' - '||bom_routing_ops_rec.organization_code||'Organization code does not exists ';

         
        WHEN others THEN
          l_err_msg := l_err_msg || ' - '||bom_routing_ops_rec.organization_code||SQLCODE||'-'||SQLERRM;

      END;

  
       
      --  Validate assembly item Number ----------------------------------------------
     
     
      BEGIN
    
        SELECT  inventory_item_id INTO
                l_item
          FROM  mtl_system_items_b
          WHERE  organization_id = l_org_id
          AND   segment1 = bom_routing_ops_rec.item_number
          AND   bom_enabled_flag = 'Y';
         
      Exception
        WHEN  no_data_found THEN
          l_err_msg := l_err_msg || ' - ' ||bom_routing_ops_rec.item_number||'Item Number does not exists ';

         
        WHEN others THEN
          l_err_msg := l_err_msg|| ' - ' ||bom_routing_ops_rec.item_number|| ' - '||SQLCODE||'-'||SQLERRM;

      END;   
  
     
     
      ---validate department code----------------------------------------
       
      BEGIN
    
        SELECT  department_id INTO
                v_num_dept_id
          FROM bom_departments
          WHERE department_code=bom_routing_ops_rec.department_code
          AND  organization_id = l_org_id;
         
         
      EXCEPTION
        WHEN  no_data_found THEN
          l_err_msg := bom_routing_ops_rec.department_code||'department code does not exists ';

         
        WHEN others THEN
          l_err_msg :=bom_routing_ops_rec.department_code||SQLCODE||'-'||SQLERRM;

      END;   
     
     
    IF l_err_msg IS NULL THEN  
      l_valid_rout_ops_count := l_valid_rout_ops_count + 1;
     
      UPDATE   FTXS_118C_ROUTING_OPS_TEMP
      SET    status_flag = 'V'
            ,err_msg = NULL
      WHERE  rowid = bom_routing_ops_rec.row_id;
    ELSE
             UPDATE   FTXS_118C_ROUTING_OPS_TEMP
      SET    status_flag = 'E'
            ,err_msg = l_err_msg
      WHERE  rowid = bom_routing_ops_rec.row_id;
      l_err_count := l_err_count +1;
    END IF;
     
   EXCEPTION
     
   WHEN others THEN
  
      UPDATE  FTXS_118C_ROUTING_OPS_TEMP
      SET    status_flag = 'E'
            ,err_msg = l_err_msg
      WHERE  rowid = bom_routing_ops_rec.row_id;
      l_err_count := l_err_count + 1;
     
   End;  
  
   IF MOD(l_valid_rout_ops_count,100) = 0 THEN
  
     COMMIT;
    
   END IF; 
  
  END LOOP;
 
  COMMIT;

  Fnd_file.put_line(fnd_file.log,'**************************************************');  
  Fnd_file.put_line(fnd_file.log,'Total no of records fetched   :'||l_rout_ops_count);
  Fnd_file.put_line(fnd_file.log,'Total no of valid records     :'||l_valid_rout_ops_count);
  Fnd_file.put_line(fnd_file.log,'Total no of errored records   :'||l_err_count); 
  Fnd_file.put_line(fnd_file.log,'**************************************************');  
       
  IF l_err_count >=1 then
    Fnd_file.put_line(fnd_file.log,'**************************************************');
    FOR bom_routing_ops_err_rec IN bom_routing_ops_err_cur
    LOOP
      Fnd_file.put_line(fnd_file.log,bom_routing_ops_err_rec.err_msg);
    END LOOP; 
    Fnd_file.put_line(fnd_file.log,'**************************************************'); 
  END IF;
 
 END  validate_routing_ops;


   
 --**********************************************************************************************
--This is Procedure is used to move the data from staging table to interface tables
--*********************************************************************************************
 PROCEDURE Insert_routing_ops
 as

 l_userid   NUMBER:=0;
  v_date      date := sysdate;
  l_count     number;
  l_err_msg   varchar2(1000);
  l_err_count  number;
 
 
 

  CURSOR ins_routing_ops_cur IS SELECT fro.* ,rowid row_id
                             FROM  FTXS_118C_ROUTING_OPS_TEMP fro
                             WHERE status_flag = 'V';
  CURSOR ins_routing_ops_err_cur IS SELECT fro.* ,rowid row_id
                             FROM  FTXS_118C_ROUTING_OPS_TEMP fro
                             WHERE status_flag = 'E';



 BEGIN

   l_count := 0;
   l_userid := fnd_global.user_id;
   l_err_count := 0;
 

    FOR  ins_routing_ops_rec IN ins_routing_ops_cur
    LOOP
   
    BEGIN
    l_err_msg  := NULL;

    INSERT INTO bom_op_sequences_interface
                (organization_code    
                ,assembly_item_number        
                ,operation_seq_num    
                ,department_code     
                ,effectivity_date     
                ,process_flag
                ,transaction_type
                ,last_update_date
                ,last_updated_by
                ,creation_date
                ,created_by
                ,last_update_login
                )
        VALUES (ins_routing_ops_rec.organization_code
               ,ins_routing_ops_rec.item_number
               ,ins_routing_ops_rec.operation_seq_num 
               ,ins_routing_ops_rec.department_code
               ,ins_routing_ops_rec.effectivity_date
               ,1
               ,'CREATE'
               ,v_date
               ,l_userid
               ,v_date
               ,l_userid
               ,l_userid
               );
              
               l_count := l_count + 1;
              
    EXCEPTION
      WHEN others THEN
                UPDATE  FTXS_118C_ROUTING_OPS_TEMP
               SET    status_flag = 'E'
                     ,err_msg = l_err_msg
              WHERE    rowid = ins_routing_ops_rec.row_id;
              l_err_count:=l_err_count+1;
      
    END;          
    END LOOP;
     
   Fnd_file.put_line(fnd_file.log,'**************************************************');     
    Fnd_file.put_line(fnd_file.log,'Total no of records inserted:'||l_count);
    Fnd_file.put_line(fnd_file.log,'Total no of records errored :'||l_err_count);
    Fnd_file.put_line(fnd_file.log,'**************************************************');
       
  IF l_err_count >=1 THEN
    Fnd_file.put_line(fnd_file.log,'**************************************************');
    FOR ins_routing_ops_err_rec IN ins_routing_ops_err_cur
    LOOP
      Fnd_file.put_line(fnd_file.log,ins_routing_ops_err_rec.err_msg);
    END LOOP; 
    Fnd_file.put_line(fnd_file.log,'**************************************************'); 
  END IF;
    
    END Insert_routing_ops;
   
   
   
   
   
   
PROCEDURE  main_procedure(errbuf  OUT varchar2
                         ,retcode OUT number) is
BEGIN
           
       
       validate_headers;
      
       Insert_headers;
      
       validate_bom_comp;
      
       Insert_bom_comp;
      
        validate_rout_headers;
       
        Insert_rout_headers;
       
        validate_routing_ops;
       
        Insert_routing_ops;
       
  EXCEPTION
    WHEN others THEN
    fnd_file.put_line( fnd_file.log,' error occoured while inserting data into interface table '||SQLCODE||SQLERRM);
            

      
END  main_procedure;


END;
/