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;