new routing creation

CREATE OR REPLACE PROCEDURE APPS.MFZ_create_routing (p_create_routing IN VARCHAR2)
   IS
      l_routings_type           apps.gmd_routings%ROWTYPE;
      l_routings_step_tab       apps.gmd_routings_pub.gmd_routings_step_tab;
      l_routings_step_dep_tab   apps.gmd_routings_pub.gmd_routings_step_dep_tab;
      l_return_status           VARCHAR2 (1);
      l_msg_count               NUMBER;
      l_msg_data                VARCHAR2 (2000);
      l_count                   NUMBER;
      l_msg_ind                 VARCHAR2 (30);
      l_routing_id              NUMBER;
      l_oprn_id                 NUMBER;
      ln_dep_type               NUMBER;
      ln_suc_rec_cnt            NUMBER                                   := 0;
      ln_rej_rec_cnt            NUMBER                                   := 0;
      ln_lcu_count              NUMBER;
      ln_progress               NUMBER                                   := 0;
      ln_count                  NUMBER;
      ln_organization_id        NUMBER;
      lc_routing_uom            VARCHAR2 (7);
      l_msg                     varchar2(2000);
      l_user_id                 NUMBER  := 1110; --NUMBER                                   := 1187;
      l_TEST           VARCHAR2 (10);
      
      
      /*
      DROP TABLE APPS.XXBLR_OPM_ROUTING_STG CASCADE CONSTRAINTS;

CREATE TABLE APPS.XXBLR_OPM_ROUTING_STG
(
  SR_NO                  VARCHAR2(20 BYTE),
  NEW_ROUTING_NO         VARCHAR2(20 BYTE),
  ROUTING_VERSION        VARCHAR2(20 BYTE),
  ROUTING_DESCRIPTION    VARCHAR2(250 BYTE),
  ROUTING_CLASS          VARCHAR2(20 BYTE),
  ROUTING_CLASS_DESC     VARCHAR2(20 BYTE),
  GD_VALID_DATE          DATE                   DEFAULT to_date(sysdate,'dd-mm-yy')
  ,              --, to_char(to_date(valid_from,'dd-mm-yy'),'dd-mon-yy') valid_from --valid_from,
  VALID_TO               VARCHAR2(20 BYTE),
  ROUTING_QTY            FLOAT(126),
  ROUTING_UOM            VARCHAR2(20 BYTE),
  OWNER_ORG_CODE         VARCHAR2(20 BYTE),
  ORGANIZATION_ID        NUMBER,
  TOTAL_LOSS             NUMBER,
  RECORD_STATUS          CHAR(1 BYTE)           DEFAULT 'N',
  VALID_FROM             DATE,
  PROCESS_OUTPUT_QTY     NUMBER(10),
  OPRN_ID                NUMBER,
  STEP_NUMBER            NUMBER,
  NEW_OPRN_NO            VARCHAR2(250 BYTE),
  ERROR_MESSAGE_ROUTING  VARCHAR2(2000 BYTE),
  ERROR_CODE_ROUTING     VARCHAR2(250 BYTE),
  RECORD_STATUS_ROUTING  VARCHAR2(250 BYTE)
)
TABLESPACE APPS_TS_TX_DATA
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
LOGGING
NOCOMPRESS
NOCACHE;


 Insert into XXBLR_OPM_ROUTING_STG
select distinct 10, FORMULA_NO, FORMULA_VERS, replace(FORMULA_DESC1,'Formula for ','Routing For ') FORMULA_DES, null, null, TO_DATE('10/Nov/22', 'DD/MON/YY'), null,
FORMULA_TYPE, 'PCS','FMO', 370, SCALE_TYPE,'1', TO_DATE('10/Nov/22', 'DD/MON/YY'), 1, 5740,1,'PI6.PCS_PACK', null, null,null form_org_id from fm_form_mst a , gmd_routings b
where a.formula_no = b.routing_no(+) and b.routing_no is null --and YIELD_UOM='PCS'
  and exists (select 1 from fm_matl_dtl aa where a.FORMULA_ID=aa.FORMULA_ID and aa.line_type=1 and DETAIL_UOM='PCS' );


Insert into XXBLR_OPM_STEP_DEP_STG
select distinct 'FMO', FORMULA_NO, '1','1','1','1','1', null, null,null form_org_id from fm_form_mst a , gmd_routings b
where a.formula_no = b.routing_no(+) and b.routing_no is null --and YIELD_UOM='PCS'
  and exists (select 1 from fm_matl_dtl aa where a.FORMULA_ID=aa.FORMULA_ID and aa.line_type=1 and DETAIL_UOM='PCS' );
 
  commit;


*/
      
--===============================
--Cursor to get new records count
--===============================
      CURSOR lcu_count
      IS
         
   --   create table mfz_opm_routing_stg as select * FROM xxblr_opm_routing_stg
      
         SELECT COUNT (*)
           FROM xxblr_opm_routing_stg
          WHERE record_status ='1' and RECORD_STATUS_ROUTING is null
          
          ;-- gc_validation_flag;

--===============================
--Cursor to get Routing Data
--===============================
      CURSOR lcu_routing_data
      IS                                            
         
         SELECT   xors.sr_no
                , xors.new_routing_no routing_no
                , xors.routing_version
                , xors.routing_description
                , xors.routing_class
                , xors.routing_class_desc
                , gd_valid_date valid_from
                --, to_char(to_date(xors.valid_from,'dd-mm-yy'),'dd-mon-yy') valid_from --xors.valid_from
                , xors.valid_to
                , xors.routing_qty
                , xors.routing_uom
                , xors.owner_org_code
                , xors.organization_id
                ,
                --substr(xors.total_loss,1,instr(xors.total_loss,'%')-1)
                 total_loss --xors.total_loss
            FROM xxblr_opm_routing_stg xors
           WHERE 1=1        and RECORD_STATUS_ROUTING is null           
        GROUP BY  xors.sr_no
                , xors.new_routing_no
                , xors.routing_version
                , xors.routing_description
                , xors.routing_class
                , xors.routing_class_desc
                , xors.gd_valid_date
                , xors.valid_to
                , xors.routing_qty
                , xors.routing_uom
                , xors.owner_org_code
                , xors.organization_id
                , xors.total_loss
        ORDER BY  new_routing_no    
            
        ;

--============================
--Cursor to get Step  data
--============================
      CURSOR lcu_step_data (p_routing_num VARCHAR2)--,p_step_num NUMBER)
      IS                                                                                                                            
     
 SELECT  new_routing_no,
              new_oprn_no,
              step_number,
              oprn_id,
              process_output_qty
         FROM xxblr_opm_routing_stg
        WHERE 1=1 and RECORD_STATUS_ROUTING is null
       --   AND step_number = p_step_num
          AND new_routing_no  = p_routing_num
     GROUP BY new_routing_no,
              new_oprn_no,
              step_number,
              oprn_id,
              process_output_qty
     ORDER BY step_number;

--=================================
--Cursor to get Step Depency data
--=================================
      CURSOR lcu_step_dep_data (p_routing_no varchar2)
      IS                
      
       SELECT  organization_code
             , new_routing_no routing_no
             , routing_version
             , previous_step
             , routing_step
             , dependency_type
             , transfer_percent
             , error_code
             , record_status
             , error_message
         FROM  xxblr_opm_step_dep_stg
        WHERE  1=1  
          AND  new_routing_no    =  p_routing_no
     GROUP BY  organization_code
             , new_routing_no
             , routing_version
             , previous_step
             , routing_step
             , dependency_type
             , transfer_percent
             , error_code
             , record_status
             , error_message
     ORDER BY previous_step;

      TYPE tbl_routing_data IS TABLE OF lcu_routing_data%ROWTYPE INDEX BY BINARY_INTEGER;
      lt_per                    tbl_routing_data;

--      TYPE tbl_stepdep_data IS TABLE OF lcu_step_dep_data%ROWTYPE INDEX BY BINARY_INTEGER;
--      ct_per                    tbl_stepdep_data;
    


 
   BEGIN
   
   
      fnd_global.apps_initialize (user_id           => 1187,
                               resp_id           => 22882,
                               resp_appl_id      => 552
                              );




      apps.fnd_file.put_line (apps.fnd_file.LOG, RPAD ('*', 80, '*'));
      apps.fnd_file.put_line(apps.fnd_file.LOG,'   --------- Executing CREATE_ROUTING ---------');
      apps.fnd_file.put_line (apps.fnd_file.LOG, RPAD (' ', 80, ' '));
      apps.fnd_file.put_line (apps.fnd_file.output, RPAD ('*', 80, '*'));
      apps.fnd_file.put_line(apps.fnd_file.output,'   --------- Executing CREATE_ROUTING ---------');
      apps.fnd_file.put_line (apps.fnd_file.output, RPAD (' ', 80, ' '));

      OPEN lcu_count;
      FETCH lcu_count INTO ln_lcu_count;
      CLOSE lcu_count;

      apps.fnd_file.put_line (apps.fnd_file.LOG,'Number of Records Loaded:' || ln_lcu_count);

        -------------------------------------------------------------------------
        -- Bulk Insert   Data into Qualification.
        --------------------------------------------------------------------------
      OPEN lcu_routing_data;
      LOOP
         lt_per.DELETE;

         FETCH lcu_routing_data BULK COLLECT INTO lt_per LIMIT 50000;

         IF lt_per.COUNT > 0
         THEN
            apps.fnd_file.put_line (apps.fnd_file.LOG,'COUNT-->' || lt_per.COUNT);
           
           
            FOR i IN lt_per.FIRST .. lt_per.LAST
            LOOP
               apps.fnd_file.put_line (apps.fnd_file.LOG,'TRACK2');
               l_routings_type := NULL;
               l_routings_type.routing_id               := NULL;
               l_routings_type.routing_no               := lt_per (i).routing_no;
               l_routings_type.routing_vers             := lt_per (i).routing_version;
               l_routings_type.routing_desc             := lt_per (i).routing_description;
               l_routings_type.routing_class            := NULL; --lt_per (i).routing_class;
               l_routings_type.routing_qty              := lt_per (i).routing_qty;
               l_routings_type.item_um                  := NULL;
               l_routings_type.delete_mark              := 0;
               l_routings_type.text_code                := NULL;
               l_routings_type.inactive_ind             := 0;
               l_routings_type.in_use                   := 0;
               l_routings_type.creation_date            := sysdate;
               l_routings_type.created_by               := 1110; --l_user_id;
               l_routings_type.last_update_login        := 76532411; --l_user_id;
               l_routings_type.last_update_date         := sysdate;
               l_routings_type.last_updated_by          := 1110; -- l_user_id;
               l_routings_type.effective_start_date     := sysdate; -- to_date(lt_per (i).valid_from);
               l_routings_type.effective_end_date       := NULL; --lt_per (i).valid_to;
               l_routings_type.owner_id                 := 1110; -- l_user_id;
               l_routings_type.project_id               := NULL;
               l_routings_type.routing_status           := 3; --gn_status;
               l_routings_type.process_loss             := 0; --lt_per(i).total_loss;
               l_routings_type.enforce_step_dependency  := 1;     
               l_routings_type.owner_organization_id    := 370; --lt_per (i).organization_id;
               l_routings_type.routing_uom              := lt_per (i).routing_uom;
               l_routings_type.contiguous_ind           := 0;
               l_routings_type.fixed_process_loss       := 0; --NULL;
               l_routings_type.fixed_process_loss_uom   := lt_per (i).routing_uom; --NULL;
                           
               l_count := 0;
               l_routings_step_tab.DELETE;
              
               apps.fnd_file.put_line (apps.fnd_file.LOG,'TRACK3');
              
               FOR step_rec IN lcu_step_data(lt_per (i).routing_no)--,lt_per (i).step_number)
               LOOP
                
                  apps.fnd_file.put_line (apps.fnd_file.LOG,'lcu_step_data :'   ||l_count);               
                  apps.fnd_file.put_line (apps.fnd_file.LOG,'STEP NUMBER:'      ||step_rec.step_number);
                  l_count :=l_count + 1;
               
                  l_routings_step_tab (l_count).routing_id            := NULL; --l_routing_id;
                  l_routings_step_tab (l_count).routingstep_no        := 1; --step_rec.step_number;
                  l_routings_step_tab (l_count).routingstep_id        := NULL;
                  l_routings_step_tab (l_count).oprn_id               := step_rec.oprn_id;
                  l_routings_step_tab (l_count).step_qty              := 1000; --step_rec.process_output_qty;
                  l_routings_step_tab (l_count).steprelease_type      := 1;
                  l_routings_step_tab (l_count).text_code             := NULL;
                  l_routings_step_tab (l_count).last_updated_by       :=  1110; --l_user_id;
                  l_routings_step_tab (l_count).created_by            := 1110; -- l_user_id;
                  l_routings_step_tab (l_count).last_update_date      := sysdate;
                  l_routings_step_tab (l_count).creation_date         := sysdate;
                 l_routings_step_tab (l_count).last_update_login     := 76532411;-- l_user_id;
                  l_routings_step_tab (l_count).minimum_transfer_qty  := NULL;
                                     
                  --apps.fnd_file.put_line (apps.fnd_file.LOG,'COUNT-->' || ct_per.COUNT);

               END LOOP;
               apps.fnd_file.put_line (apps.fnd_file.LOG,'TRACK3');
               ln_count :=0;   l_TEST := '227';
               l_routings_step_dep_tab.DELETE;
              
               FOR ct_per IN lcu_step_dep_data(lt_per(i).routing_no)
               LOOP
                  ln_count  := ln_count + 1;
                                        
                  apps.fnd_file.put_line (apps.fnd_file.LOG,'lcu_step_dep_data: ' ||ln_count);
                  apps.fnd_file.put_line (apps.fnd_file.LOG,'ROUTING STEP NO:'    ||ct_per.routing_step);                                             
                  apps.fnd_file.put_line (apps.fnd_file.LOG,'PREVIOUS STEP NO:'   ||ct_per.previous_step);

                  --Step Dependency
                  l_routings_step_dep_tab  (ln_count).routingstep_no           := 1; --ct_per.routing_step;
                  l_routings_step_dep_tab  (ln_count).dep_routingstep_no       :=0;-- ct_per.previous_step;
                  l_routings_step_dep_tab  (ln_count).routing_id               := NULL;--l_routing_id;
                  l_routings_step_dep_tab  (ln_count).dep_type                 := ct_per.dependency_type;
                  l_routings_step_dep_tab  (ln_count).rework_code              := NULL;
                  l_routings_step_dep_tab  (ln_count).standard_delay           := 0;
                  l_routings_step_dep_tab  (ln_count).minimum_delay            := 0;
                  l_routings_step_dep_tab  (ln_count).max_delay                := NULL;
                  l_routings_step_dep_tab  (ln_count).transfer_qty             := 300;
                  l_routings_step_dep_tab  (ln_count).item_um                  := NULL;
                  l_routings_step_dep_tab  (ln_count).text_code                := NULL;
                  l_routings_step_dep_tab  (ln_count).last_updated_by          := 1110;-- l_user_id;
                  l_routings_step_dep_tab  (ln_count).created_by               := 1110; -- l_user_id;
                  l_routings_step_dep_tab  (ln_count).last_update_date         := sysdate;
                  l_routings_step_dep_tab  (ln_count).creation_date            := sysdate;
                  l_routings_step_dep_tab  (ln_count).last_update_login        :=  76532411;-- l_user_id;
                  l_routings_step_dep_tab  (ln_count).transfer_pct             := ct_per.transfer_percent;
                  l_routings_step_dep_tab  (ln_count).chargeable_ind           := 0;
                  l_routings_step_dep_tab  (ln_count).routingstep_no_uom       := lt_per (i).routing_uom;
                                      
               END LOOP;                    

               BEGIN
                  apps.gmd_routings_pub.insert_routing
                                             (  p_api_version                => 1.0
                                              , p_init_msg_list              => TRUE
                                              , p_commit                     => FALSE
                                              , p_routings                   => l_routings_type
                                              , p_routings_step_tbl          => l_routings_step_tab
                                              , p_routings_step_dep_tbl      => l_routings_step_dep_tab
                                              , x_message_count              => l_msg_count
                                              , x_message_list               => l_msg_data
                                              , x_return_status              => l_return_status
                                             );
                                             
                                             l_TEST := '274';
                                             
                  apps.fnd_file.put_line (apps.fnd_file.LOG,'RETURN_STATUS:-'|| l_return_status);
                  
                   l_TEST := '278';
                   
               EXCEPTION
                  WHEN OTHERS
                  THEN
                     apps.fnd_file.put_line(apps.fnd_file.LOG,'API RAISED ERROR: '|| SQLCODE|| '-'|| SQLERRM);
                     apps.fnd_file.put_line (apps.fnd_file.LOG,'RETURN_STATUS:-'|| l_return_status);
               END;

               IF l_return_status <> 'S' THEN
                   ln_rej_rec_cnt := ln_rej_rec_cnt + 1;
                   l_msg := '';
                   FOR i IN 1 .. l_msg_count
                   LOOP
                       l_msg := SUBSTR(l_msg || ' |M- ' || SUBSTR(apps.fnd_msg_pub.get(l_msg_count - i + 1, 'F'), 0, 200), 1, 2000);
                   END LOOP;
                   l_msg_data := l_msg;
                   apps.fnd_file.put_line(apps.fnd_file.LOG,'Error in OPRN INSERT API : ' || l_msg);     

               ELSE
                   ln_suc_rec_cnt := ln_suc_rec_cnt + 1;
               END IF;

               apps.fnd_file.put_line (apps.fnd_file.LOG,'TRACK 14');

                 BEGIN
                 
                   UPDATE  xxblr_opm_routing_stg
                      SET  record_status_routing    = 'TEST: ' ||   l_TEST || ' : ' || l_return_status
                         , error_code_routing       = l_msg_count
                         , error_message_routing    = l_msg_data
                    WHERE  new_routing_no           = lt_per(i).routing_no
                      AND  routing_version          = lt_per(i).routing_version;                     
                                                     
                 EXCEPTION
                   WHEN OTHERS THEN
                      apps.fnd_file.put_line(apps.fnd_file.LOG,'Error occured in staging table updation:'||sqlcode||sqlerrm);
                 END;     

                
                 BEGIN
                  
                   UPDATE  xxblr_opm_step_dep_stg
                      SET  record_status    = l_return_status
                         , error_code       = l_msg_count
                         , error_message    = l_msg_data
                    WHERE  new_routing_no   = lt_per(i).routing_no
                      AND  routing_version  = lt_per(i).routing_version;
                                                     
                                                    
                 EXCEPTION
                     WHEN OTHERS THEN
                      apps.fnd_file.put_line(apps.fnd_file.LOG,'Error occured in staging table updation:'||sqlcode||sqlerrm);
                 END;     
                
                 COMMIT;
                    
                 
                 
            END LOOP;
         END IF;

         apps.fnd_file.put_line (apps.fnd_file.LOG,' +----------------------------------------------------------+');
         apps.fnd_file.put_line (apps.fnd_file.LOG,' | Total records processed :           '|| ln_suc_rec_cnt);
         apps.fnd_file.put_line (apps.fnd_file.LOG,' | Total records rejected  :           '|| ln_rej_rec_cnt);
         apps.fnd_file.put_line (apps.fnd_file.LOG,' +----------------------------------------------------------+');
         apps.fnd_file.put_line (apps.fnd_file.LOG, ' ');
        
      EXIT WHEN lcu_routing_data%NOTFOUND;
      END LOOP;

     CLOSE lcu_routing_data;
   EXCEPTION
      WHEN OTHERS THEN
         apps.fnd_file.put_line (apps.fnd_file.LOG,'MAIN-ERR-CREATE-ROUT :> ' || SQLERRM || ', ' || SQLCODE);
         apps.fnd_file.put_line (apps.fnd_file.LOG,'   --------- Procedure CREATE_ROUTING Exit ---------');
         apps.fnd_file.put_line (apps.fnd_file.LOG, RPAD (' ', 80, ' '));
         apps.fnd_file.put_line (apps.fnd_file.LOG, RPAD ('*', 80, '*'));
   END mfz_create_routing;
/