Translate

Wednesday, February 6, 2019

OPM operation uploading through api script


 PROCEDURE create_operation (p_create_operation VARCHAR2)
   IS
      l_operations_tbl             apps.gmd_operations%ROWTYPE;
      l_oprn_activities_tbl_type   apps.gmd_operations_pub.gmd_oprn_activities_tbl_type;
      l_oprn_resources_tbl_type    apps.gmd_operation_resources_pub.gmd_oprn_resources_tbl_type;
      l_count                      NUMBER;
      l_msg_ind                    VARCHAR2 (50);
      l_return_status              VARCHAR2 (1);
      l_msg_count                  NUMBER;
      l_msg_data                   VARCHAR2 (2000);
      ln_oprn_line_id              NUMBER;
      ln_operation_id              NUMBER;
      ln_err_count                 NUMBER;  
      ln_suc_rec_cnt               NUMBER                                := 0;
      ln_rej_rec_cnt               NUMBER                                := 0;
      ln_progress                  NUMBER                                := 0;    
      ln_organization_id           NUMBER;
      ln_count                     NUMBER;
      lc_rsrc_process_uom          VARCHAR2 (20);
      lc_oper_class                VARCHAR2 (20);
      lc_process_usage_uom         VARCHAR2 (7);
      lc_resource_process_uom      VARCHAR2 (7);
      lc_process_qty_uom           VARCHAR2 (15);
      lc_activity                  VARCHAR2 (25);
      lc_resources                 VARCHAR2 (25);

       --===============================
        --Cursor to get new records count
        --===============================
      CURSOR lcu_count
      IS
         SELECT COUNT (*)
           FROM xxblr_opm_routing_stg
          WHERE 1=1
            AND record_status = gc_validation_flag;

    --==================================
    --Cursor to get Valid Operation data
    --==================================
      CURSOR lcu_oprn_data
      IS                               
      SELECT  xors.new_oprn_no oprn_no
            , xors.oprn_vers
            , xors.owner_org_code
            , SUBSTR(xors.oprn_desc,1,40) oprn_desc
            , xors.oprn_class_desc
            , xors.oprn_class
            , gd_valid_date oper_valid_from
            , xors.process_output_qty
            , xors.process_qty_uom
            , xors.organization_id
            , min(transaction_id) min_txn_id       
        FROM  xxblr_opm_routing_stg xors
       WHERE 1=1
         AND (new_oprn_no, oprn_vers) IN (
                                       SELECT  new_oprn_no                 
                                             , oprn_vers                  
                                         FROM
                                             (
                                               ( SELECT  new_oprn_no                 
                                                       , oprn_vers                   
                                                   FROM  xxblr_opm_routing_stg
                                                  WHERE  1=1
                                                    AND  NVL (record_status, 'N') = gc_validation_flag
                                               GROUP BY  new_oprn_no
                                                       , oprn_vers                 
                                                )
                                                MINUS
                                               ( SELECT  new_oprn_no                 
                                                       , oprn_vers                   
                                                   FROM  xxblr_opm_routing_stg
                                                  WHERE  1=1
                                                    AND  (   NVL (record_status, 'N')  = gc_newrecord_flag
                                                          OR NVL (record_status, 'N')  = gc_validation_error_flag
                                                          OR NVL (record_status, 'N')  = gc_error_flag
                                                          OR NVL (record_status, 'N')  = gc_success_flag
                                                          OR NVL (record_status, 'N')  = gc_item_error_flag
                                                          )
                                               GROUP BY   new_oprn_no
                                                        , oprn_vers                 
                                                )
                                              )
                                      )              
        GROUP BY  xors.new_oprn_no
                , xors.oprn_vers
                , xors.owner_org_code
                , xors.oprn_desc
                , xors.oprn_class_desc
                , xors.oprn_class
                --, xors.oper_valid_from
                , xors.process_output_qty
                , xors.process_qty_uom
                , xors.organization_id
        ORDER BY  min_txn_id; 
              
         --=================================
        --Cursor to get Valid Activity data
        --=================================
      CURSOR lcu_act_data (p_oprn_no VARCHAR2,p_oprn_vers NUMBER)
      IS
         SELECT  new_oprn_no oprn_no
               , activity
               , oprn_vers
               , min(transaction_id) min_txn_id       
           FROM  xxblr_opm_routing_stg
          WHERE  1=1
            AND  NVL(record_status, 'N') =  gc_validation_flag
            AND  UPPER(new_oprn_no)      =  UPPER (p_oprn_no)
            AND  UPPER(oprn_vers)        =  UPPER (p_oprn_vers)
       GROUP BY  new_oprn_no, activity,oprn_vers
       ORDER BY  min_txn_id;
   
    --==================================
    --Cursor to get Valid Resource data
    --==================================
      CURSOR lcu_rsrc_data (p_oprn_no VARCHAR2, p_oprn_vers NUMBER)
      IS
         SELECT  new_oprn_no oprn_no
               , activity
               , oprn_vers
               , resources
               , resource_code
               , resource_count
               , resource_usage
               , usage_uom
               , scale_type
               , component_class
               , owner_org_code
               , process_output_qty
               , process_qty_uom
               , cost_analysis_code
               , DECODE(plan_type,'Primary',1,DECODE(plan_type,'Auxiliary',2,NULL)) plan_type
               , offset_interval
           FROM  xxblr_opm_routing_stg
          WHERE  1=1
            AND  NVL(record_status, 'N') =     gc_validation_flag
            AND  new_oprn_no             =     p_oprn_no
            AND  oprn_vers               =     p_oprn_vers
       ORDER BY transaction_id;


    --==================================
    --Cursor to get Activity data
    --==================================
      CURSOR lcu_act (p_activity VARCHAR2)
      IS
         SELECT activity
           FROM apps.gmd_activities_vl
          WHERE 1=1
            AND UPPER (activity) = UPPER (p_activity);


      TYPE tbl_operation_data IS TABLE OF lcu_oprn_data%ROWTYPE INDEX BY BINARY_INTEGER;
      lt_oprn_data                 tbl_operation_data;
      l_msg VARCHAR2(2000);
     
   BEGIN
      apps.fnd_file.put_line (apps.fnd_file.LOG, RPAD ('*', 80, '*'));
      apps.fnd_file.put_line(apps.fnd_file.LOG,'   --------- Executing CREATE_OPERATION ---------');
      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_OPERATION ---------');
      apps.fnd_file.put_line (apps.fnd_file.output, RPAD (' ', 80, ' '));

       OPEN lcu_count;
      FETCH lcu_count
       INTO ln_count;
      CLOSE lcu_count;

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

        -------------------------------------------------------------------------
        -- Bulk Insert   Data into Operation.
        --------------------------------------------------------------------------
      BEGIN 
          OPEN lcu_oprn_data;
          LOOP
             lt_oprn_data.DELETE; 
             gn_bulk_err := 0;

             FETCH lcu_oprn_data BULK COLLECT INTO lt_oprn_data LIMIT 50000;

             IF lt_oprn_data.COUNT > 0
             THEN
                apps.fnd_file.put_line (apps.fnd_file.LOG,'OPERATION COUNT-->' || lt_oprn_data.COUNT);
                apps.fnd_file.put_line (apps.fnd_file.LOG, 'OPRN  TRACK 1.0');
                l_count := 0;
            
                BEGIN
               
                   FOR i IN 1 .. lt_oprn_data.COUNT
                   LOOP
                     
                      l_count := l_count + 1;                            
                          
                      apps.fnd_file.put_line (apps.fnd_file.LOG, 'TRACK 2.0 - OPRN DATA ASSIGNING - START: '||lt_oprn_data (i).oprn_no);
                  
                      l_operations_tbl.oprn_no                 := lt_oprn_data (i).oprn_no;
                      l_operations_tbl.oprn_vers               := lt_oprn_data (i).oprn_vers;
                      l_operations_tbl.process_qty_uom         := lt_oprn_data (i).process_qty_uom;
                      l_operations_tbl.oprn_class              := lt_oprn_data (i).oprn_class;
                      l_operations_tbl.poc_ctl_class           := NULL;
                      l_operations_tbl.in_use                  := NULL;
                      l_operations_tbl.inactive_ind            := 0;
                      l_operations_tbl.delete_mark             := 1;
                      l_operations_tbl.creation_date           := gd_sysdate;
                      l_operations_tbl.created_by              := l_user_id;
                      l_operations_tbl.last_updated_by         := l_user_id;
                      l_operations_tbl.last_update_date        := gd_sysdate;
                      l_operations_tbl.last_update_login       := l_user_id;
                      l_operations_tbl.effective_start_date    := to_date(lt_oprn_data (i).oper_valid_from);
                      l_operations_tbl.operation_status        := gn_status;
                      l_operations_tbl.owner_organization_id   := lt_oprn_data (i).organization_id;
                      l_operations_tbl.oprn_desc               := lt_oprn_data (i).oprn_desc;

                      apps.fnd_file.put_line (apps.fnd_file.LOG, 'TRACK 2.1 - OPRN DATA(LOOP) ASSIGNED - END');                     
                   
                      l_oprn_activities_tbl_type.DELETE;
                      ln_count := 0;                     
                      apps.fnd_file.put_line (apps.fnd_file.LOG, 'TRACK 3.0 --> ACTVTY DATA LOOP - START: ' || ln_count);

                      FOR lt_act_data IN lcu_act_data(lt_oprn_data(i).oprn_no,lt_oprn_data(i).oprn_vers)
                      LOOP
                         ln_count := ln_count + 1;
                         
                     --    apps.fnd_file.put_line (apps.fnd_file.LOG, 'TRACK 3.1 --> IN ACTVTY DATA LOOP ' ||lt_act_data.activity);
                           
                         l_oprn_activities_tbl_type (ln_count).activity                 := lt_act_data.activity;
                         l_oprn_activities_tbl_type (ln_count).offset_interval          := 0;
                         l_oprn_activities_tbl_type (ln_count).activity_factor          := 1;
                         l_oprn_activities_tbl_type (ln_count).delete_mark              := 0;
                         l_oprn_activities_tbl_type (ln_count).text_code                := NULL;
                         l_oprn_activities_tbl_type (ln_count).creation_date            := gd_sysdate;
                         l_oprn_activities_tbl_type (ln_count).created_by               := l_user_id;
                         l_oprn_activities_tbl_type (ln_count).last_update_date         := gd_sysdate;
                         l_oprn_activities_tbl_type (ln_count).last_update_login        := l_user_id;
                         l_oprn_activities_tbl_type (ln_count).sequence_dependent_ind   := 0;
                         l_oprn_activities_tbl_type (ln_count).break_ind                := 0;
                         l_oprn_activities_tbl_type (ln_count).max_break                := NULL;
                         l_oprn_activities_tbl_type (ln_count).material_ind             := 0;
                           
                      END LOOP;
                      apps.fnd_file.put_line (apps.fnd_file.LOG, 'TRACK 3.2 --> ACTVTY DATA LOOP - END: ' || ln_count);
                     
                      l_oprn_resources_tbl_type.DELETE;
                      l_count  := 0;
                      apps.fnd_file.put_line (apps.fnd_file.LOG, 'TRACK 5.0 --> RESOURCE DATA LOOP - START:' || ln_count || ':' || l_count);

                      FOR lt_rsrc_data IN lcu_rsrc_data(lt_oprn_data (i).oprn_no,lt_oprn_data (i).oprn_vers)
                      LOOP
                          
                          l_count := l_count + 1;
                        
                       --   apps.fnd_file.put_line (apps.fnd_file.LOG, 'TRACK 5.0 --> IN RESOURCE DATA LOOP:' ||lt_rsrc_data.resource_code);    
                         
                         
                          l_oprn_resources_tbl_type (l_count).oprn_line_id         := NULL; --ln_oprn_line_id;
                          l_oprn_resources_tbl_type (l_count).resources            := lt_rsrc_data.resource_code;
                          l_oprn_resources_tbl_type (l_count).resource_usage       := lt_rsrc_data.resource_usage;
                          l_oprn_resources_tbl_type (l_count).resource_count       := lt_rsrc_data.resource_count;
                          l_oprn_resources_tbl_type (l_count).resource_usage_uom   := NULL;--lc_resource_process_uom;
                          l_oprn_resources_tbl_type (l_count).process_qty          := lt_rsrc_data.process_output_qty;
                          l_oprn_resources_tbl_type (l_count).resource_process_uom := lt_rsrc_data.process_qty_uom;
                          l_oprn_resources_tbl_type (l_count).prim_rsrc_ind        := lt_rsrc_data.plan_type;
                          l_oprn_resources_tbl_type (l_count).scale_type           := lt_rsrc_data.scale_type;                               
                          l_oprn_resources_tbl_type (l_count).cost_analysis_code   := lt_rsrc_data.cost_analysis_code;
                          l_oprn_resources_tbl_type (l_count).cost_cmpntcls_id     := NULL;
                          l_oprn_resources_tbl_type (l_count).offset_interval      := 0; --lt_rsrc_data.offset_interval;      -- * ---
                          l_oprn_resources_tbl_type (l_count).min_capacity         := NULL;
                          l_oprn_resources_tbl_type (l_count).max_capacity         := NULL;
                          l_oprn_resources_tbl_type (l_count).resource_capacity_uom:= NULL;
                          l_oprn_resources_tbl_type (l_count).attribute_category   := NULL;
                          l_oprn_resources_tbl_type (l_count).activity             := lt_rsrc_data.activity;
                             
                      END LOOP;
                      apps.fnd_file.put_line (apps.fnd_file.LOG, 'TRACK 5.0 --> RESOURCE DATA LOOP - END:' || ln_count || ':' || l_count);
                     
                 
                      BEGIN
                        apps.fnd_file.put_line (apps.fnd_file.LOG, 'CALLING API - ' || lt_oprn_data (i).oprn_no);
                       -- apps.fnd_file.put_line (apps.fnd_file.LOG, 'TRACK 5.0 --> ACTIVITY COUNT:' ||l_oprn_activities_tbl_type.COUNT);  
                       -- apps.fnd_file.put_line (apps.fnd_file.LOG, 'TRACK 5.0 --> RESOURCE COUNT:' ||l_oprn_resources_tbl_type.COUNT);
                       
                        apps.gmd_operations_pub.insert_operation
                                  (  p_api_version        => '1.0'
                                   , p_init_msg_list      => TRUE
                                   , p_commit             => FALSE
                                   , p_operations         => l_operations_tbl
                                   , p_oprn_actv_tbl      => l_oprn_activities_tbl_type
                                   , x_message_count      => l_msg_count
                                   , x_message_list       => l_msg_data
                                   , x_return_status      => l_return_status
                                   , p_oprn_rsrc_tbl      => l_oprn_resources_tbl_type
                                  );

                      EXCEPTION
                        WHEN OTHERS THEN
                           apps.fnd_file.put_line(apps.fnd_file.LOG,'OPRN INSERT API RAISED exception: '|| SQLCODE|| '-'|| SQLERRM);
                           apps.fnd_file.put_line(apps.fnd_file.LOG,'TRACK 13.2.A'|| l_return_status);
                           apps.fnd_file.put_line(apps.fnd_file.LOG,'TRACK 13.2.B data:'|| l_msg_data);
                           apps.fnd_file.put_line(apps.fnd_file.LOG,'TRACK 13.2.C:'     || l_msg_count);
                           l_return_status := 'U';
                      END;

                      apps.fnd_file.put_line(apps.fnd_file.LOG,'OPRN INSERT API : ' || l_return_status || ' :'|| l_msg_data || ' :' || l_msg_count);     

                       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 || ' | ' || 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;
                          
                      BEGIN
                         UPDATE  xxblr_opm_routing_stg
                            SET  record_status    = l_return_status
                               , error_code       = l_msg_count
                               , error_message    = l_msg_data
                          WHERE  new_oprn_no      = lt_oprn_data(i).oprn_no
                            AND  oprn_vers        = lt_oprn_data(i).oprn_vers;
                                             
                         COMMIT;   
                                            
                      EXCEPTION
                           WHEN OTHERS THEN
                            apps.fnd_file.put_line(apps.fnd_file.LOG,'Error occured in staging table updation:'||sqlcode||sqlerrm);
                      END;     
               
                   END LOOP;
               
                EXCEPTION
                   WHEN OTHERS THEN
                      apps.fnd_file.put_line (apps.fnd_file.LOG,'ERROR ON OPRN DATA :> ' || SQLERRM || ', ' || SQLCODE);
                END;
               
             END IF;
           
             apps.fnd_file.put_line(apps.fnd_file.LOG, ' ');
             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, ' ');
             apps.fnd_file.put_line(apps.fnd_file.LOG, 'TRACK 16');
           
             EXIT WHEN lcu_oprn_data%NOTFOUND;
          END LOOP;
          CLOSE lcu_oprn_data;
      EXCEPTION
      WHEN OTHERS THEN
      apps.fnd_file.put_line (apps.fnd_file.LOG,'MAIN-ERR-CRT-OPRN-CURR :> ' || SQLERRM || ', ' || SQLCODE);
      END;
                           
   EXCEPTION
      WHEN OTHERS
      THEN
         apps.fnd_file.put_line (apps.fnd_file.LOG,'MAIN-ERR-CRT-OPRN :> ' || SQLERRM || ', ' || SQLCODE);
         apps.fnd_file.put_line(apps.fnd_file.LOG,'   --------- Procedure CREATE_OPERATION Exit ---------');
         apps.fnd_file.put_line (apps.fnd_file.LOG, RPAD (' ', 80, ' '));
         apps.fnd_file.put_line (apps.fnd_file.LOG, RPAD ('*', 80, '*'));
   END create_operation;
-

No comments:

Post a Comment

Text Message

Subscribe to: Post Comments (Atom)

Outside Processing Setups and Cycle in Oracle EBS R12

  OSP Cycle This post is about Oracle Outside Processing Cycle. Outside processing is used when we don't have in house resources to comp...