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;
/

No comments:

Post a Comment