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;