APPS.PQH_GSP_GRD_STEP_REMOVE SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 14

         SELECT object_version_number
           FROM ben_copy_entity_results
          WHERE copy_entity_result_id = p_copy_entity_result_id;

Line: 35

         SELECT dml_operation
           FROM ben_copy_entity_results
          WHERE copy_entity_result_id = p_copy_entity_result_id;

Line: 51

   PROCEDURE delete_rec (
      p_copy_entity_result_id   IN   NUMBER,
      p_effective_date          IN   DATE,
      p_object_version_number   in   NUMBER default null
   )
   IS
      l_ovn    NUMBER;

Line: 59

      hr_utility.set_location ('Entering  delete_rec', 10);

Line: 65

      ben_copy_entity_results_api.delete_copy_entity_results (
         p_copy_entity_result_id      => p_copy_entity_result_id,
         p_effective_date             => p_effective_date,
         p_object_version_number      => l_ovn
      );

Line: 71

      hr_utility.set_location ('Leaving delete_rec', 100);

Line: 74

         hr_utility.set_location ('Errors in delete_rec ...', 100);

Line: 76

   END delete_rec;

Line: 82

   PROCEDURE update_rec (
      p_copy_entity_result_id   IN   NUMBER,
      p_effective_date          IN   DATE
   )
   IS
      l_ovn    NUMBER;

Line: 89

      hr_utility.set_location ('Entering update_rec', 10);

Line: 91

      ben_copy_entity_results_api.update_copy_entity_results (
         p_copy_entity_result_id      => p_copy_entity_result_id,
         p_effective_date             => p_effective_date,
         p_information104             => 'UNLINK',
         p_object_version_number      => l_ovn,
         p_information323             => NULL
      );

Line: 99

      hr_utility.set_location ('Leaving update_rec', 100);

Line: 102

         hr_utility.set_location ('Errors in update_rec ...', 150);

Line: 103

   END update_rec;

Line: 119

         SELECT opt.copy_entity_txn_id, opt.information256 -- Pay Scale Cer Id
           FROM ben_copy_entity_results opt
          WHERE opt.copy_entity_result_id = p_opt_result_id
            AND opt.table_alias = 'OPT'
            AND NVL (opt.information104, 'PPP') <> 'UNLINK'
	    AND opt.copy_entity_txn_id = p_copy_entity_txn_id;

Line: 128

         SELECT COUNT (opt.copy_entity_result_id)
           FROM ben_copy_entity_results opt
          WHERE opt.information256 = l_scale_cer_id
          AND opt.copy_entity_txn_id  = l_cet_id
          AND opt.table_alias = 'OPT';

Line: 137

         SELECT copy_entity_result_id
           FROM ben_copy_entity_results
          WHERE information258 = p_scale_cer_id
          AND copy_entity_txn_id  = l_cet_id;

Line: 154

            delete_rec ( p_copy_entity_result_id      => l_scale_cer_id,
                         p_effective_date             => p_effective_date);

Line: 158

               UPDATE ben_copy_entity_results
                  SET information98 = NULL,
                      information255 = NULL,
                      information258 = NULL,
                      information259 = NULL,
                      information262 = NULL
                WHERE copy_entity_result_id = rec_plip_cer_id.copy_entity_result_id;

Line: 165

               hr_utility.set_location ('Plip updated successfully', 40);

Line: 188

         SELECT stdrate.copy_entity_result_id, stdrate.object_version_number
           FROM ben_copy_entity_results stdrate
          WHERE stdrate.table_alias IN ('HRRATE', 'ABR')
            AND stdrate.information278 = p_opt_result_id
            AND stdrate.copy_entity_txn_id = p_copy_entity_txn_id;

Line: 196

         SELECT crrate.copy_entity_result_id, crrate.object_version_number
           FROM ben_copy_entity_results crrate
          WHERE crrate.copy_entity_txn_id = p_copy_entity_txn_id
            AND crrate.table_alias = 'CRRATE'
            AND crrate.information169 = p_opt_result_id;

Line: 205

         delete_rec ( p_copy_entity_result_id      => rec_std_rates.copy_entity_result_id,
                      p_effective_date             => p_effective_date,
                      p_object_version_number      => rec_std_rates.object_version_number);

Line: 214

         delete_rec ( p_copy_entity_result_id      => rec_crrates.copy_entity_result_id,
                      p_effective_date             => p_effective_date,
            p_object_version_number      => rec_crrates.object_version_number
         );

Line: 263

      IF l_dml_operation = 'INSERT' THEN
         -- 1) Purge ABR,HRRATE, CRRATE
         hr_utility.set_location ( 'Calling purge_opt_abr_hrrate_crrate... :', 30);

Line: 285

         delete_rec (
            p_copy_entity_result_id      => p_copy_entity_result_id,
            p_effective_date             => p_effective_date
         );

Line: 289

      ELSE -- l_dml_operation is UPDATE/UPD_INS/COPIED


         pqh_gsp_grd_step_remove.unlink_opt_abr_hrrate_crrate (
            p_opt_result_id           => p_copy_entity_result_id,
            p_copy_entity_txn_id      => p_copy_entity_txn_id,
            p_effective_date          => p_effective_date
         );

Line: 297

         update_rec (
            p_copy_entity_result_id      => p_copy_entity_result_id,
            p_effective_date             => p_effective_date
         );

Line: 321

         SELECT elp.copy_entity_result_id
           FROM ben_copy_entity_results elp
          WHERE elp.copy_entity_txn_id = p_copy_entity_txn_id
            AND elp.gs_parent_entity_result_id = p_copy_entity_result_id
            AND elp.table_alias = 'ELP';

Line: 335

            pqh_gsp_prgrules.delete_eligibility (
               p_copy_entity_txn_id         => p_copy_entity_txn_id,
               p_copy_entity_result_id      => elp_recs.copy_entity_result_id
            );

Line: 355

    if oipl.dml_operation = 'INSERT'
      1) Purge Rates Std Rates : ABR,HRRATE,GSRATE Crit Rates : CRRATE,GRRATE
      2) Purge Elig Profile  call procedure remove_elig_profile
      3) purge OIPL

   else if opt.dml_operation in ('COPIED','UPD_INS','UPDATE')
      1) Mark for deletion call procedure remove_elig_profile
      2) oipl.information104 = UNLINK

  */
   PROCEDURE remove_oipl_step_flavour (
      p_copy_entity_txn_id      IN   NUMBER,
      p_copy_entity_result_id   IN   NUMBER,
      p_effective_date          IN   DATE,
      p_remove_opt              IN   VARCHAR2 DEFAULT 'Y'
   )
   IS
      l_point_or_step   VARCHAR2 (20);

Line: 377

         SELECT oipl.information262
           FROM ben_copy_entity_results oipl
          WHERE oipl.copy_entity_txn_id = p_copy_entity_txn_id
            AND oipl.table_alias = 'COP'
            AND oipl.copy_entity_result_id = p_copy_entity_result_id;

Line: 397

         IF l_dml_operation = 'INSERT'  OR l_dml_operation ='COPIED' OR l_dml_operation = 'UPD_INS' THEN
            --  1) Call Pqh_Gsp_Grd_Step_Remove.remove_opt
            IF p_remove_opt = 'Y' THEN
               hr_utility.set_location ('Calling remove_opt... :', 60);

Line: 417

            delete_rec (
               p_copy_entity_result_id      => p_copy_entity_result_id,
               p_effective_date             => p_effective_date
            );

Line: 421

         ELSE -- dml_operation = COPIED/UPD_INS/UPDATE
            --  1) Mark for deletion call procedure remove_elig_profile
            pqh_gsp_grd_step_remove.remove_elig_profile (
               p_copy_entity_txn_id         => p_copy_entity_txn_id,
               p_copy_entity_result_id      => p_copy_entity_result_id
            );

Line: 437

            update_rec (
               p_copy_entity_result_id      => p_copy_entity_result_id,
               p_effective_date             => p_effective_date
            );

Line: 451

     if oipl.dml_operation = 'INSERT'
        1) Purge Standard Rates and Criteria Rates
              1.a) Purge Standard Rates i.e ABR, HRRATE
              1.b) Purge Criteria Rates i.e CRRATE
        2) Purge Elig Profile  call procedure Pqh_Gsp_Grd_Step_Remove.remove_elig_profile
        3) purge OIPL
     else if opt.dml_operation in ('COPIED','UPD_INS','UPDATE')
        1) Mark for deletion call procedure Pqh_Gsp_Grd_Step_Remove.remove_elig_profile
        2) oipl.information104 = UNLINK
  */
   PROCEDURE remove_oipl_point_flavour (
      p_copy_entity_txn_id      IN   NUMBER,
      p_copy_entity_result_id   IN   NUMBER,
      p_effective_date          IN   DATE
   )
   IS
      l_point_or_step   VARCHAR2 (20);

Line: 472

         SELECT oipl.information262
           FROM ben_copy_entity_results oipl
          WHERE oipl.copy_entity_txn_id = p_copy_entity_txn_id
            AND oipl.table_alias = 'COP'
            AND oipl.copy_entity_result_id = p_copy_entity_result_id;

Line: 491

         IF l_dml_operation = 'INSERT' OR l_dml_operation ='COPIED' OR l_dml_operation = 'UPD_INS' THEN
            --  1) Purge Standard Rates : ABR, HRRATE and Criteria Rates : CRRATE
        /*    hr_utility.set_location ( 'Calling purge_opt_abr_hrrate_crrate ... :', 70);

Line: 508

            delete_rec (
               p_copy_entity_result_id      => p_copy_entity_result_id,
               p_effective_date             => p_effective_date
            );

Line: 512

         ELSE -- dml_operation = COPIED/UPD_INS/UPDATE
            --  1) Mark for deletion call procedure Pqh_Gsp_Grd_Step_Remove.remove_elig_profile
            pqh_gsp_grd_step_remove.remove_elig_profile (
               p_copy_entity_txn_id         => p_copy_entity_txn_id,
               p_copy_entity_result_id      => p_copy_entity_result_id
            );

Line: 519

            update_rec (
               p_copy_entity_result_id      => p_copy_entity_result_id,
               p_effective_date             => p_effective_date
            );

Line: 582

         SELECT pln.copy_entity_result_id
           FROM ben_copy_entity_results pln
          WHERE pln.gs_mirror_src_entity_result_id = p_copy_entity_result_id
            AND pln.copy_entity_txn_id = p_copy_entity_txn_id
            AND pln.table_alias = 'PLN';

Line: 589

         SELECT oipl.copy_entity_result_id
           FROM ben_copy_entity_results oipl
          WHERE oipl.gs_parent_entity_result_id = p_copy_entity_result_id
            AND oipl.table_alias = 'COP'
            AND oipl.copy_entity_txn_id = p_copy_entity_txn_id;

Line: 623

      IF l_dml_operation = 'INSERT' THEN
         --  1) Purge Elig Profile  call procedure remove_elig_profile
            hr_utility.set_location ('Calling remove_elig_profile... :', 70);

Line: 635

               update ben_copy_entity_results
               set gs_mirror_src_entity_result_id =  null
               where copy_entity_result_id = l_pln_cer_id;

Line: 641

            delete_rec ( p_copy_entity_result_id      => p_copy_entity_result_id,
                         p_effective_date             => p_effective_date);

Line: 645

      ELSE -- dml_operation = UPDATE/COPIED/UPD_INS
         -- Mark PLIP for Deletion
         update_rec (
            p_copy_entity_result_id      => p_copy_entity_result_id,
            p_effective_date             => p_effective_date
         );

Line: 666

         SELECT stdrate.copy_entity_result_id, stdrate.object_version_number
           FROM ben_copy_entity_results stdrate
          WHERE stdrate.table_alias IN ('HRRATE', 'ABR')
            AND stdrate.information278 = p_opt_result_id
            AND stdrate.copy_entity_txn_id = p_copy_entity_txn_id;

Line: 675

         SELECT crrate.copy_entity_result_id, crrate.object_version_number
           FROM ben_copy_entity_results crrate
          WHERE crrate.copy_entity_txn_id = p_copy_entity_txn_id
            AND crrate.table_alias = 'CRRATE'
            AND crrate.information169 = p_opt_result_id;

Line: 686

         update_rec (
            p_copy_entity_result_id      => rec_std_rates.copy_entity_result_id,
            p_effective_date             => p_effective_date
         );

Line: 697

         update_rec (
            p_copy_entity_result_id      => rec_crrates.copy_entity_result_id,
            p_effective_date             => p_effective_date
         );