OPM step dependency api validations and Code

 PROCEDURE validate_step_dep (p_validate_step_dep VARCHAR2)

   IS
      --Loacal Variables
      lc_routing_num        VARCHAR2 (20);
      ln_max_routing_vers   NUMBER;
      ln_routing_vers       NUMBER;
      ln_routing_num        VARCHAR2(35);
      ln_bulk_error_cnt     NUMBER;
      ln_count              NUMBER;
      ln_dep_type           NUMBER;


    --==================================
    --Cursor to get Step Dependency Data
    --==================================
      CURSOR lcu_step_dep_data
      IS
        SELECT  transaction_id
              , organization_code
              , new_routing_no routing_no
              , routing_version
              , previous_step
              , routing_step
              , dependency_type
              , transfer_percent
              , process_flag
              , error_code
              , record_status
              , error_message
              , last_updated_by
              , created_by
              , last_update_date
              , creation_date
              , last_update_login
           FROM xxblr_opm_step_dep_stg
          WHERE 1=1
            AND (   NVL(record_status, 'N') = gc_newrecord_flag
                 OR NVL(record_status, 'N') = gc_validation_error_flag) 
       ORDER BY transaction_id;
          
          
    --===============================
    --Cursor to get Step Dep Number
    --===============================
      CURSOR lcu_step_data (cp_routing_no VARCHAR2)
      IS
         SELECT  new_routing_no routing_no
               , previous_step
               , routing_step
               , dependency_type
               , transfer_percent
               , process_flag
               , error_code
               , record_status
               , error_message
               , last_updated_by
               , created_by
               , last_update_date
               , creation_date
               , last_update_login
           FROM  xxblr_opm_step_dep_stg
          WHERE  1=1
            AND (   NVL(record_status, 'N') = gc_newrecord_flag
                 OR NVL(record_status, 'N') = gc_validation_error_flag
                ) 
            AND new_routing_no    = cp_routing_no;

    --===============================
    --Cursor to get Routing Number
    --===============================
      CURSOR lcu_routing_num (cp_routing_num VARCHAR2)
      IS
         SELECT routing_no
           FROM apps.fm_rout_hdr
          WHERE 1=1
            AND routing_no = cp_routing_num;
         
     --===============================
    --Cursor to get Step Dependency
    --===============================                    
        CURSOR lcu_dep_type(p_dep_type varchar2)
        IS        
        SELECT lookup_code             
          FROM apps.fnd_lookup_values
         WHERE 1=1
           AND lookup_type    = 'DEP_TYPE'                              
           AND (   UPPER(meaning)      = UPPER (p_dep_type)
                OR UPPER(lookup_code)  = UPPER(p_dep_type)
               );

    --======================================
    --Cursor to get Routing num with version
    --======================================
      CURSOR lcu_routing_numvers (
                                 cp_routing_num    VARCHAR2,
                                 cp_routing_vers   NUMBER
                                 )
      IS                                   
         SELECT routing_no,routing_vers
           FROM apps.fm_rout_hdr          
          WHERE 1=1
            AND routing_no   =  cp_routing_num
            AND routing_vers =  cp_routing_vers;

    --===============================
    --Cursor to get Max Routing Version
    --===============================
      CURSOR max_routing_vers
      IS
         SELECT MAX (routing_vers)
           FROM apps.fm_rout_hdr;

      TYPE tbl_step_dep_type IS TABLE OF lcu_step_dep_data%ROWTYPE INDEX BY BINARY_INTEGER;
      lt_per                tbl_step_dep_type;

    
   BEGIN
      apps.fnd_file.put_line (apps.fnd_file.LOG, RPAD ('*', 80, '*'));
      apps.fnd_file.put_line(apps.fnd_file.LOG,'   --------- Executing VALIDATE_STEP_DEP ---------');
      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 VALIDATE_STEP_DEP ---------');
      apps.fnd_file.put_line (apps.fnd_file.output, RPAD (' ', 80, ' '));
  
      SELECT COUNT (*)
        INTO ln_count
        FROM xxblr_opm_step_dep_stg
       WHERE 1=1
         AND (   record_status = gc_newrecord_flag
              OR record_status = gc_validation_error_flag
             );
        
     IF ln_count > 0 THEN
             apps.fnd_file.put_line (apps.fnd_file.LOG,'---Validate Step Dep Data--');
             apps.fnd_file.put_line (apps.fnd_file.LOG,'---Loaded Records:' || ln_count);

          OPEN lcu_step_dep_data;
          LOOP
             FETCH lcu_step_dep_data BULK COLLECT INTO lt_per LIMIT 50000;
             apps.fnd_file.put_line (apps.fnd_file.LOG, 'Track1');

             FOR i IN lt_per.FIRST .. lt_per.LAST
             LOOP
                apps.fnd_file.put_line (apps.fnd_file.LOG, 'Track 2');

                --===============================
                -- Applying Transformation rules
                --===============================
                apps.fnd_file.put_line (apps.fnd_file.LOG,'Validation for Routing no');
                apps.fnd_file.put_line (apps.fnd_file.LOG, 'Track2');

                IF lt_per (i).routing_no IS NULL
                THEN
                   lt_per (i).record_status     := gc_error_flag;
                   gc_error_code                := '-PAT04';
                   gc_error_message             := ' ,ROUTING_NUMBER is NULL';
                   lt_per (i).error_code        := gc_error_code;
                   lt_per (i).error_message     := gc_error_message;
                END IF;

                IF lt_per (i).routing_version IS NULL
                THEN
                   lt_per (i).routing_version :=  1;
                END IF;

                OPEN lcu_routing_numvers (lt_per (i).routing_no,lt_per (i).routing_version);
                FETCH lcu_routing_numvers
                 INTO ln_routing_num,ln_routing_vers;
                CLOSE lcu_routing_numvers;

                apps.fnd_file.put_line (apps.fnd_file.LOG,'Track versioon :' || ln_routing_vers);

                IF ln_routing_num IS NOT NULL
                THEN
                   apps.fnd_file.put_line (apps.fnd_file.LOG, 'Trck_A');
                   lt_per (i).record_status     := gc_error_flag;
                   gc_error_code                := '-PAT04';
                   gc_error_message             := ' ,ROUTING_NUMBER ALREADY EXISTED';
                   lt_per (i).error_code        := gc_error_code;
                   lt_per (i).error_message     := gc_error_message;
                END IF;

                apps.fnd_file.put_line (apps.fnd_file.LOG,'TRACK 3:' || lt_per (i).routing_no);
                                             
                IF lt_per (i).previous_step IS NULL 
                THEN
                   apps.fnd_file.put_line (apps.fnd_file.LOG, 'Trck_B');
                   lt_per (i).record_status   := gc_error_flag;
                   gc_error_code              := '-PAT04';
                   gc_error_message           := ' ,PREVIOUS STEP IS NULL';
                   lt_per (i).error_code      := gc_error_code;
                   lt_per (i).error_message   := gc_error_message;
                END IF;

                IF lt_per (i).routing_step IS NULL
                THEN
                   apps.fnd_file.put_line (apps.fnd_file.LOG, 'Trck_C');
                   lt_per (i).record_status   := gc_error_flag;
                   gc_error_code              := '-PAT04';
                   gc_error_message           := ' ,ROUTING STEP IS NULL';
                   lt_per (i).error_code      := gc_error_code;
                   lt_per (i).error_message   := gc_error_message;
                END IF;

                IF lt_per (i).dependency_type IS NULL THEN
                   apps.fnd_file.put_line (apps.fnd_file.LOG, 'Trck_D');
                   lt_per (i).record_status   := gc_error_flag;
                   gc_error_code              := '-PAT04';
                   gc_error_message           := ' ,DEPENDENCY TYPE IS NULL';
                   lt_per (i).error_code      := gc_error_code;
                   lt_per (i).error_message   := gc_error_message;
                ELSE
                   ln_dep_type   :=NULL;
                   OPEN lcu_dep_type(lt_per (i).dependency_type);
                   FETCH lcu_dep_type INTO ln_dep_type;
                   CLOSE lcu_dep_type;              
                   apps.fnd_file.put_line(apps.fnd_file.LOG,'DEP TYPE:'||ln_dep_type);
                   IF  ln_dep_type IS NULL THEN
                       lt_per (i).record_status   := gc_error_flag;
                       gc_error_code              := '-PAT04';
                       gc_error_message           := ' ,DEPENDENCY TYPE IS INVALID';
                       lt_per (i).error_code      := gc_error_code;
                       lt_per (i).error_message   := gc_error_message;
                   ELSE
                       lt_per(i).dependency_type  := ln_dep_type;
                   END IF;
                END IF;

                IF lt_per (i).transfer_percent IS NULL
                THEN
                    apps.fnd_file.put_line (apps.fnd_file.LOG, 'Trck_E');
                   lt_per (i).record_status   := gc_error_flag;
                   gc_error_code              := '-PAT04';
                   gc_error_message           := ' ,TRANSFER PERCENT IS NULL';
                   lt_per (i).error_code      := gc_error_code;
                   lt_per (i).error_message   := gc_error_message;
                END IF;
                                                          
                     --=================================
                       -- Identifying the success records
                    --=================================
                   IF lt_per (i).record_status <> gc_error_flag THEN
                      apps.fnd_file.put_line (apps.fnd_file.LOG, 'Trck_E');
                      lt_per (i).record_status := gc_validation_flag;
                      lt_per (i).error_message := 'All Validations Passed';
                   ELSE
                      apps.fnd_file.put_line (apps.fnd_file.LOG, 'Trck_VE');
                      lt_per (i).record_status := gc_validation_error_flag;
                   END IF;

                  
                apps.fnd_file.put_line (apps.fnd_file.LOG, 'Track 3.10');
                --===================================================================
                --Bulk Update the records withe status flag, validation error message
                --===================================================================
                ln_bulk_error_cnt := 0;

                BEGIN
                     FORALL i IN lt_per.FIRST .. lt_per.LAST SAVE EXCEPTIONS
                      UPDATE xxblr_opm_step_dep_stg
                         SET record_status      = lt_per (i).record_status,
                             error_message      = lt_per (i).error_message,
                             error_code         = lt_per (i).error_code,
                             dependency_type    = lt_per (i).dependency_type,
                             last_update_date   = gd_current_date,
                             last_updated_by    = gn_user_id,
                             last_update_login  = gn_login_id
                       WHERE transaction_id     = lt_per (i).transaction_id;
                   COMMIT;
                EXCEPTION
                WHEN OTHERS THEN
                      apps.fnd_file.put_line(apps.fnd_file.LOG,'   Bulk Update Error Message :-> '|| SQLERRM);
                      ln_bulk_error_cnt := SQL%BULK_EXCEPTIONS.COUNT;

                      FOR i IN 1 .. ln_bulk_error_cnt
                      LOOP
                         apps.fnd_file.put_line(apps.fnd_file.LOG,'Error# '|| i|| ' at iteration# '|| SQL%BULK_EXCEPTIONS (i).ERROR_INDEX);
                         apps.fnd_file.put_line(apps.fnd_file.LOG,'Error Message is '|| SQLERRM(-SQL%BULK_EXCEPTIONS (i).ERROR_CODE));
                      END LOOP;
                END;
             END LOOP;

          EXIT WHEN lcu_step_dep_data%NOTFOUND;
          END LOOP;
          CLOSE lcu_step_dep_data;
     ELSE
         apps.fnd_file.put_line (apps.fnd_file.LOG,'NO DATA FOUND TO BE PROCESSED :> ');
         apps.fnd_file.put_line(apps.fnd_file.LOG,'   --------- Procedure VALIDATE_STEP_DEP Exit ---------');
     END IF;
   EXCEPTION
      WHEN OTHERS
      THEN
         apps.fnd_file.put_line (apps.fnd_file.LOG,'MAIN-ERR-VLD-STEP-DEP :> ' || SQLERRM || ', ' || SQLCODE);
         apps.fnd_file.put_line(apps.fnd_file.LOG,'   --------- Procedure VALIDATE_STEP_DEP Exit ---------');
         apps.fnd_file.put_line (apps.fnd_file.LOG, RPAD (' ', 80, ' '));
         apps.fnd_file.put_line (apps.fnd_file.LOG, RPAD ('*', 80, '*'));
   END validate_step_dep;

OPM routing api validations and Code

PROCEDURE validate_routing_data (p_validate_routing VARCHAR2)
   IS
      --Local Variables
      ln_routing_no         VARCHAR2 (25);
      ln_max_routing_vers   NUMBER;
      ln_bulk_error_cnt     NUMBER;
      ln_valrec_cnt         NUMBER;
      ln_trnsfrec_cnt       NUMBER;
      ln_oprn_id            NUMBER;
      lc_routing_class      VARCHAR2 (10);
      lc_routing_num        VARCHAR2 (15);
      lc_rout_uom           VARCHAR2 (15);
      ln_organizatioin_id   NUMBER;

      CURSOR lcu_count
      IS
         SELECT COUNT (1)
           FROM xxblr_opm_routing_stg
          WHERE 1=1
            AND record_status_routing = gc_newrecord_flag
            AND routing_no IS NOT NULL;

--===============================
--Cursor to get Routing Data
--===============================
      CURSOR lcu_routiing_data
      IS
         SELECT  transaction_id
               , sr_no
               , new_routing_no routing_no
               , routing_version
               , routing_description
               , routing_class
               , routing_class_desc
               , new_oprn_no oprn_no
               , oprn_vers
               , valid_from
               , valid_to
               , routing_qty
               , routing_uom
               , owner_org_code
               , step_number
               , organization_id
               , oprn_id
               , process_flag
               , error_message_routing error_message
           FROM  xxblr_opm_routing_stg
          WHERE  1=1
            AND (   NVL(record_status_routing, 'N') = gc_newrecord_flag
                 OR NVL(record_status_routing, 'N') = gc_validation_error_flag
                )                     
       ORDER BY  transaction_id,routing_no;
        
--===============================
--Cursor to get Fouting Num
--===============================
      CURSOR lcu_routing_num (cp_routing_num VARCHAR2)
      IS
         SELECT routing_no
           FROM apps.fm_rout_hdr
          WHERE 1=1
            AND routing_no = cp_routing_num;

--======================================
--Cursor to get Routing Num With Version
--======================================
      CURSOR lcu_routing_no (cp_routing_num VARCHAR2, cp_routing_vers NUMBER)
      IS
         SELECT routing_no
           FROM apps.fm_rout_hdr
          WHERE 1=1
            AND routing_no   = cp_routing_num
            AND routing_vers = cp_routing_vers;

--===============================
--Cursor to get Routing Max Version
--==================================
      CURSOR max_routing_vers
      IS
         SELECT MAX (routing_vers)
           FROM apps.fm_rout_hdr;

--===============================
--Cursor to get Org Code
--===============================
      CURSOR lcu_org_code (cp_organization_code VARCHAR2)
      IS
         SELECT organization_id
           FROM apps.org_organization_definitions
          WHERE 1=1
            AND UPPER(organization_code) = UPPER(cp_organization_code);

--===============================
--Cursor to get Routing Class
--===============================
      CURSOR lcu_rout_class (cp_routing_class VARCHAR2)
      IS
         SELECT routing_class
           FROM apps.fm_rout_cls
          WHERE 1=1
            AND UPPER(routing_class_desc) = UPPER(cp_routing_class);

--===============================
--Cursor to get Routing UOM
--===============================
      CURSOR lcu_rout_uom (p_uom_code VARCHAR2)
      IS
         SELECT uom_code
           FROM apps.mtl_units_of_measure
          WHERE 1=1
            AND UPPER(uom_code) = UPPER(p_uom_code);

--=================================
--Cursor to get Count Valid Records
--=================================
      CURSOR lcu_valrec_cnt (cp_record_status VARCHAR2)
      IS
         SELECT COUNT (1)
           FROM xxblr_opm_routing_stg
          WHERE 1=1
            AND record_status_routing = gc_newrecord_flag;

--=================================
--Cursor to get oprn ID
--=================================
      CURSOR lcu_oprn_id (p_oprn_no VARCHAR2,p_oprn_vers VARCHAR2)
      IS
         SELECT oprn_id
           FROM apps.gmd_operations_b
          WHERE 1=1
            AND oprn_no   = p_oprn_no
            AND oprn_vers = p_oprn_vers;

      TYPE per_tbl_type IS TABLE OF lcu_routiing_data%ROWTYPE INDEX BY BINARY_INTEGER;
      lt_per                per_tbl_type;
     
   BEGIN
      apps.fnd_file.put_line (apps.fnd_file.LOG, RPAD ('*', 80, '*'));
      apps.fnd_file.put_line(apps.fnd_file.LOG,'   --------- Executing VALIDATE_ROUTING_DATA ---------');
      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 VALIDATE_ROUTING_DATA ---------');
      apps.fnd_file.put_line (apps.fnd_file.output, RPAD (' ', 80, ' '));

      OPEN lcu_routiing_data;

      LOOP
         lt_per.DELETE;

         FETCH lcu_routiing_data BULK COLLECT INTO lt_per LIMIT 50000;

         FOR i IN lt_per.FIRST .. lt_per.LAST
         LOOP
           
            --===============================
            -- Applying Transformation rules
            --===============================
            ln_routing_no           := NULL;
            ln_max_routing_vers     := NULL;
            ln_bulk_error_cnt       := NULL;
            ln_valrec_cnt           := NULL;
            ln_trnsfrec_cnt         := NULL;
            lc_routing_class        := NULL;
            lc_routing_num          := NULL;
           

            apps.fnd_file.put_line (apps.fnd_file.LOG, 'Validation for Operation no:'||lt_per (i).oprn_no);
           
            IF lt_per (i).record_status_oprn != gc_success_flag THEN
               lt_per (i).record_status     := gc_error_flag;
               gc_error_code                := '-PAT04';
               gc_error_message             := ' ,OPRN NO DOESNOT EXISTS';
               lt_per (i).error_code        := gc_error_code;
               lt_per (i).error_message     := gc_error_message;           
            END IF;

            apps.fnd_file.put_line (apps.fnd_file.LOG, 'Validation for Routing no:'|| lt_per (i).routing_no);
           
             OPEN lcu_oprn_id (lt_per (i).oprn_no,lt_per (i).oprn_vers);
            FETCH lcu_oprn_id
             INTO ln_oprn_id;
            CLOSE lcu_oprn_id;
           
           apps.fnd_file.put_line (apps.fnd_file.LOG,'TRACK1');
            IF ln_oprn_id IS NULL THEN
               lt_per (i).record_status     := gc_error_flag;
               gc_error_code                := '-PAT04';
               gc_error_message             := ' ,OPRN NO and OPRN VERSION are INVALID for ROUTING';
               lt_per (i).error_code        := gc_error_code;
               lt_per (i).error_message     := gc_error_message;
            ELSE
               lt_per (i).oprn_id := ln_oprn_id;
            END IF;

            IF lt_per (i).routing_no IS NULL THEN
               lt_per (i).record_status     := gc_error_flag;
               gc_error_code                := '-PAT04';
               gc_error_message             := ' ,ROUTING_NUMBER is NULL';
               lt_per (i).error_code        := gc_error_code;
               lt_per (i).error_message     := gc_error_message;
            END IF;
            apps.fnd_file.put_line (apps.fnd_file.LOG,'TRACK2');

            IF lt_per (i).routing_version IS NULL THEN

               lt_per (i).routing_version   := 1;
            END IF;
            apps.fnd_file.put_line (apps.fnd_file.LOG,'TRACK3');
            
             OPEN lcu_routing_no (lt_per (i).routing_no,lt_per (i).routing_version);
            FETCH lcu_routing_no
             INTO ln_routing_no;
            CLOSE lcu_routing_no;

            apps.fnd_file.put_line (apps.fnd_file.LOG,'Track versioon :' ||ln_routing_no);

            IF ln_routing_no IS NOT NULL THEN
               lt_per (i).record_status     := gc_error_flag;
               gc_error_code                := '-PAT04';
               gc_error_message             := ', ROUTING ALREADY EXISTED ';
               lt_per (i).error_code        := gc_error_code;
               lt_per (i).error_message     := gc_error_message;
            END IF;

            apps.fnd_file.put_line (apps.fnd_file.LOG,'routing Desc'|| lt_per (i).routing_description);

            IF lt_per (i).routing_description IS NULL THEN
               lt_per (i).record_status     := gc_error_flag;
               gc_error_code                := '-PAT04';
               gc_error_message             := ',Routing Description  is NULL ';
               lt_per (i).ERROR_CODE        := gc_error_code;
               lt_per (i).error_message     := gc_error_message;
            END IF;

            apps.fnd_file.put_line (apps.fnd_file.LOG, 'Track4');

            IF lt_per (i).valid_from IS NULL THEN
                lt_per (i).valid_from := to_date('01-Jan-2011');
            END IF;

            apps.fnd_file.put_line (apps.fnd_file.LOG, 'Track5');

            IF lt_per (i).routing_qty IS NULL THEN
               lt_per (i).record_status     := gc_error_flag;
               gc_error_code                := '-PAT04';
               gc_error_message             := ',Routing Qty  is NULL ';
               lt_per (i).ERROR_CODE        := gc_error_code;
               lt_per (i).error_message     := gc_error_message;
            END IF;

            IF lt_per (i).routing_uom IS NULL THEN
               lt_per (i).record_status     := gc_error_flag;
               gc_error_code                := '-PAT04';
               gc_error_message             := ',Routing UOM IS NULL';
               lt_per (i).error_code        := gc_error_code;
               lt_per (i).error_message     := gc_error_message;
            ELSE
               lc_rout_uom := NULL;
               OPEN lcu_rout_uom (lt_per (i).routing_uom);
               FETCH lcu_rout_uom INTO lc_rout_uom;
               CLOSE lcu_rout_uom;
                             
               IF  lc_rout_uom IS NULL THEN
                   lt_per (i).record_status     := gc_error_flag;
                   gc_error_code                := '-PAT04';
                   gc_error_message             := ',ROUTING UOM IS INVALID';
                   lt_per (i).error_code        := gc_error_code;
                   lt_per (i).error_message     := gc_error_message;
               ELSE
               lt_per (i).routing_uom   := lc_rout_uom;
               END IF;
            END IF;

            IF lt_per (i).routing_class_desc IS NULL
            THEN
               lt_per (i).record_status     := gc_error_flag;
               gc_error_code                := '-PAT04';
               gc_error_message             := ',Routing Class is NULL';
               lt_per (i).error_code        := gc_error_code;
               lt_per (i).error_message     := gc_error_message;
            ELSE
               lc_routing_class := NULL;
               OPEN lcu_rout_class (lt_per (i).routing_class_desc);
               FETCH lcu_rout_class INTO lc_routing_class;
               CLOSE lcu_rout_class;
              
               IF lc_routing_class IS NULL THEN
                  lt_per (i).record_status  := gc_error_flag;
                  gc_error_code             := '-PAT04';
                  gc_error_message          := ',ROUTING CLASS IS INVALID';
                  lt_per (i).ERROR_CODE     := gc_error_code;
                  lt_per (i).error_message  := gc_error_message;
               ELSE
                  lt_per (i).routing_class  := lc_routing_class;
               END IF;
            END IF;

            IF lt_per (i).owner_org_code IS NULL THEN
               lt_per (i).record_status     := gc_error_flag;
               gc_error_code                := '-PAT04';
               gc_error_message             := ',ORGANIZATION CODE IS NULL';
               lt_per (i).ERROR_CODE        := gc_error_code;
               lt_per (i).error_message     := gc_error_message;
            ELSE
               ln_organizatioin_id := NULL;
               OPEN lcu_org_code (lt_per (i).owner_org_code);
               FETCH lcu_org_code INTO ln_organizatioin_id;
               CLOSE lcu_org_code;

               IF ln_organizatioin_id IS NULL THEN
                  lt_per (i).record_status  := gc_error_flag;
                  gc_error_code             := '-PAT04';
                  gc_error_message          := ',ORGANIZATION CODE IS INVALID';
                  lt_per (i).ERROR_CODE     := gc_error_code;
                  lt_per (i).error_message  := gc_error_message;
               ELSE
                  lt_per (i).organization_id := ln_organizatioin_id;
               END IF;
            END IF;

            --=================================
               -- Identifying the success records
            --=================================
            IF lt_per (i).record_status <> gc_error_flag
            THEN
               lt_per (i).record_status := gc_validation_flag;
               lt_per (i).error_message := 'All Validations Passed';
            ELSE
               lt_per (i).record_status := gc_validation_error_flag;
            END IF;
         END LOOP;

         apps.fnd_file.put_line (apps.fnd_file.LOG, 'Track6');
        --===================================================================
        --Bulk Update the records withe status flag, validation error message
        --===================================================================
         ln_bulk_error_cnt := 0;

         BEGIN
            FORALL i IN lt_per.FIRST .. lt_per.LAST SAVE EXCEPTIONS
               UPDATE xxblr_opm_routing_stg
                  SET record_status_routing         =   lt_per (i).record_status,
                      error_message_routing         =   lt_per (i).error_message,
                      error_code_routing            =   lt_per (i).error_code,
                      routing_version               =   lt_per (i).routing_version,
                      routing_class                 =   lt_per (i).routing_class,
                      routing_uom                   =   lt_per (i).routing_uom,
                      oprn_id                       =   lt_per (i).oprn_id,
                      organization_id               =   lt_per (i).organization_id,
                      last_update_date              =   gd_current_date,
                      last_updated_by               =   gn_user_id,
                      last_update_login             =   gn_login_id
                WHERE transaction_id                =   lt_per (i).transaction_id;
            COMMIT;
         EXCEPTION
            WHEN OTHERS
            THEN
               apps.fnd_file.put_line (apps.fnd_file.LOG,'   Bulk Update Error Message :-> '|| SQLERRM);
               ln_bulk_error_cnt := SQL%BULK_EXCEPTIONS.COUNT;

               FOR i IN 1 .. ln_bulk_error_cnt
               LOOP
                  apps.fnd_file.put_line(apps.fnd_file.LOG,'Error# '|| i|| ' at iteration# '|| SQL%BULK_EXCEPTIONS (i).ERROR_INDEX);
                  apps.fnd_file.put_line(apps.fnd_file.LOG,'Error Message is '|| SQLERRM(-SQL%BULK_EXCEPTIONS (i).ERROR_CODE));
               END LOOP;
         END;

         EXIT WHEN lcu_routiing_data%NOTFOUND;
      END LOOP;

      CLOSE lcu_routiing_data;

     --=============================================
    --Cursor to get count of the validated records
    --=============================================
      OPEN lcu_valrec_cnt ('V');                        --gc_validation_flag);
      FETCH lcu_valrec_cnt
       INTO ln_valrec_cnt;
      CLOSE lcu_valrec_cnt;

      apps.fnd_file.put_line (apps.fnd_file.LOG,'   Number Of Records Validated       :-> '|| ln_valrec_cnt);
      apps.fnd_file.put_line (apps.fnd_file.LOG,'   Number Of Records Failured        :-> '|| (ln_trnsfrec_cnt - ln_valrec_cnt));
   EXCEPTION
      WHEN OTHERS
      THEN
         apps.fnd_file.put_line (apps.fnd_file.LOG,'MAIN-ERR-VLD-ROUT-DATA :> ' || SQLERRM || ', ' || SQLCODE );
         apps.fnd_file.put_line(apps.fnd_file.LOG,'   --------- Procedure VALIDATE_ROUTING_DATA Exit ---------');
         apps.fnd_file.put_line (apps.fnd_file.LOG, RPAD (' ', 80, ' '));
         apps.fnd_file.put_line (apps.fnd_file.LOG, RPAD ('*', 80, '*'));
   END validate_routing_data;

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;