APPS.GHR_MASS_AWARDS_ELIG SQL Statements

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

Line: 32

l_asg_select         long;

Line: 44

l_kff_select         long;

Line: 45

l_rating_select      long;

Line: 46

l_pos_grp1_select    long;

Line: 47

l_pos_grp2_select    long;

Line: 48

l_pos_grd_select     long;

Line: 49

l_loc_ddf_select     long;

Line: 75

  Select effective_date
  from   ghr_mass_awards
  where  mass_award_id =  p_mass_award_id;

Line: 80

  select gdf.segment1 pay_plan,
         gdf.segment2 grade_or_level
  from   per_grades  grd,
         per_grade_definitions gdf
  where  grd.grade_id             =  c_grade_id
  and    grd.grade_definition_id  = gdf.grade_definition_id;

Line: 89

  Select  ppf.business_group_id
  from    per_people_f ppf
  where   ppf.person_id   =  l_per_id
  and     l_effective_date
  between ppf.effective_start_date
  and     ppf.effective_end_date;

Line: 98

    Select    dsf.duty_station_code
    from      ghr_duty_stations_f dsf
    where     dsf.duty_station_id =
              (select lei.lei_information3
               from   hr_location_extra_info lei
               where  lei.location_id = l_loc_id
              )
    and       l_effective_date
    between   dsf.effective_start_date
    and       dsf.effective_end_date;

Line: 111

        select      pos.position_extra_info_id
            from        per_position_extra_info pos
            where       pos.position_id          =  c_position_id
            and         pos.information_type     =  'GHR_US_POS_VALID_GRADE';

Line: 120

   p_asg_select      out NOCOPY long) is


  Cursor c_eff_date is
    Select n.code
    from   ghr_mass_awards m,
           ghr_nature_of_actions n
    where  mass_award_id = p_mass_award_id
           and n.nature_of_action_id = m.nature_of_action_id;

Line: 131

      select     val.relational_operator,
                 val.value
       from      ghr_mass_award_criteria_cols col,
                 ghr_mass_award_criteria_vals val
       Where     col.table_name                  = 'ASSIGNMENT'
       and       col.column_name                 = 'Organization'
       and       val.mass_award_id               =  p_mass_award_id
       and       val.mass_award_criteria_col_id  =  col.mass_award_criteria_col_id
       order by  val.relational_operator;

Line: 141

   l_select          long;

Line: 152

     l_select     :=      ' Select   asg1.person_id,asg1.assignment_id,asg1.position_id , asg1.location_id,' ||
                          ' asg1.job_id , asg1.grade_id ,  org.name ' ||
                          ' from     per_assignments_f asg1,  ' ||
                          '          hr_organization_units org   ' ||
                          ' where    ' ||   'to_date(' || '''' || l_effective_date || '''' ||
                          ',' || '''' || 'DD-MON-YY' || ''''||  ')' ||
                          ' between  asg1.effective_start_date and  asg1.effective_end_date  ' ||
                          ' and      asg1.organization_id  = org.organization_id   ' ||
                          ' and      asg1.assignment_type =  ' ||
                          '''' || 'E'  || '''' ||
                          ' and      asg1.position_id is not null ' ;

Line: 178

             l_select := l_select || '  and (' ;

Line: 180

             l_select := l_select || ' and ';

Line: 183

           l_select := l_select || ' or ' ;

Line: 187

       l_select :=  l_select ||
                    'upper(org.name)' || ' ' || l_operator ||  '  ' ||
                     'upper('    ||  l_pre || asg_values.value || l_suf ||
                     ')'      ;

Line: 195

         l_Select :=  l_select ||   ')  or 1 = 0 ' ;

Line: 198

       l_select :=  l_select || ' and 1 = 1 ';

Line: 208

     l_select := l_select ||' and ghr_pa_requests_pkg.get_personnel_system_indicator ('||
                 ' asg1.position_id,'||
	      'to_date(' || '''' || l_effective_date || '''' ||
                          ',' || '''' || 'DD-MON-YY' || ''''||  '))'||
			  '<>'||''''||'00'||'''';

Line: 217

           l_select := l_select ||' and ghr_pa_requests_pkg.get_personnel_system_indicator ('||
                       ' asg1.position_id,'||
	      'to_date(' || '''' || l_effective_date || '''' ||
                          ',' || '''' || 'DD-MON-YY' || ''''||  '))'||
			  '='||''''||'00'||'''';

Line: 230

     p_asg_select := l_select;

Line: 235

    p_kff_select     out nocopy long,
    p_pos_exists     out nocopy boolean,
    p_job_exists     out nocopy boolean
    ) is

   l_select          long;

Line: 255

     select  val.relational_operator,
             val.value,
             col.table_name,
             col.column_name
     from    ghr_mass_award_criteria_cols   col,
             ghr_mass_award_criteria_vals   val
     Where   val.mass_award_id               =  p_mass_award_id
     and     col.table_name                  = 'POSITION_KFF'
     and     val.mass_award_criteria_col_id  = col.mass_award_criteria_col_id
   union all
     Select  val.relational_operator,
             val.value,
             col.table_name,
             col.column_name
     from    ghr_mass_award_criteria_cols   col,
             ghr_mass_award_criteria_vals   val
     Where   val.mass_award_id               =  p_mass_award_id
     and     col.table_name                  = 'JOB_KFF'
     and     val.mass_award_criteria_col_id  = col.mass_award_criteria_col_id
     order by 3,1 ;

Line: 279

       l_select  := 'Select 1 from dual where  ( 1  = 1  ' ;

Line: 321

              l_select  :=  l_select || ' ) '  ||  ' and (  '
                                || 'substr(ghr_api.get_position_agency_code_pos('
                                || ':pos_id'
                                || ',' || ':business_group_id'
                                || ',' || ':effective_date'
                                || '),1,2 )'
                                || l_operator || ' ' || l_pre || pos_kff_values.value || l_suf;

Line: 330

               l_select   :=  l_select  ||  ' ) ' ||  ' and (   '
                                  ||  l_col_name || '(:pos_id'  || ', ' || ':business_group_id' || ',' || ':effective_date' || ' ) '  || ' '
                                  || l_operator ||    ' ' || l_pre || pos_kff_values.value || l_suf;

Line: 338

             l_select   :=  l_select  ||  ' )'   ||  ' and (   '
                                ||  l_col_name || '(:job_id'  || ', ' || ':business_group_id'  || ' ) '  || ' '
                                || l_operator ||    ' ' || l_pre || pos_kff_values.value || l_suf;

Line: 348

               l_select :=   l_select || '  or  '
                                 || 'substr(ghr_api.get_position_agency_code_pos('
                                 || ':pos_id'
                                 || ', ' || ':business_group_id'
                                 || ', '
                                 || ' :effective_date' ||'),1,2 )'
                                 || l_operator ||   '  ' || l_pre || pos_kff_values.value || l_suf  ;

Line: 356

               l_select :=   l_select  || '  or  '
                                 ||  l_col_name || '(:pos_id'   ||  ', ' || ':business_group_id' ||  ',' || ':effective_date' ||  ' ) '   || ' '
                                 || l_operator ||   '  ' || l_pre || pos_kff_values.value || l_suf  ;

Line: 363

             l_select :=   l_select  || '  or  '
                               ||  l_col_name || '(:job_id'   ||  ', ' || ':business_group_id' || ' ) '   || ' '
                               || l_operator ||   '  ' || l_pre || pos_kff_values.value || l_suf  ;

Line: 370

      l_select := l_select || ' ) ';

Line: 375

      p_kff_select  :=  l_select;

Line: 380

    p_kff_select    := null;

Line: 389

    p_pos_grp2_select   out nocopy long,
    p_poc_exists        out nocopy boolean
   ) is


   l_select  long;

Line: 404

     Select  val.relational_operator,
             val.value,
             col.column_name
     from    ghr_mass_award_criteria_cols col,
             ghr_mass_award_criteria_vals val
     Where   val.mass_award_id       = p_mass_award_id
     and     col.table_name          = 'POSITION_EXTRA_INFO'
     and     val.mass_award_criteria_col_id  = col.mass_award_criteria_col_id
     and     ( col.column_name = 'Position Occupied'
     )  order by 3,1;

Line: 416

    l_select  := 'select 1 from dual where (1 = 1';

Line: 434

          l_select      :=  l_select || ' ) and (' ||
                           ':POC' || l_operator || '   '     || l_pre ||  pos_grp2_rec.value || l_suf ;

Line: 440

          l_select   :=   l_select  || '  or  '   ||
                          ':POC' ||  l_operator || '   '   || l_pre ||  pos_grp2_rec.value || l_suf ;

Line: 446

   l_select     := l_select    || ' ) ';

Line: 448

   p_pos_grp2_select  :=  l_select;

Line: 453

    p_pos_grp2_select   := null;

Line: 460

    p_pos_grp1_select   out nocopy long,
    p_poi_exists        out nocopy boolean,
    p_ofs_exists       out nocopy boolean,
    p_ors_exists      out nocopy boolean
   ) is


   l_select  long;

Line: 477

     Select  val.relational_operator,
             val.value,
             col.column_name
     from    ghr_mass_award_criteria_cols col,
             ghr_mass_award_criteria_vals val
     Where   val.mass_award_id       = p_mass_award_id
     and     col.table_name          = 'POSITION_EXTRA_INFO'
     and     val.mass_award_criteria_col_id  = col.mass_award_criteria_col_id
     and     ( col.column_name = 'Personnel Office ID'
     or        col.column_name = 'Office Symbol'
     or        col.column_name = 'Organization Structure ID'
     ) order by 3,1;

Line: 491

    l_select  := 'select 1 from dual where (1 = 1';

Line: 509

          l_select      :=  l_select || ' ) and (' ||
                           ':POI' || l_operator || '   '     || l_pre ||  pos_grp1_rec.value || l_suf ;

Line: 513

          l_select      :=  l_select || ' ) and (' ||
                           ':OFS' ||   l_operator  || '   '    || l_pre ||  pos_grp1_rec.value || l_suf ;

Line: 517

          l_select      :=  l_select || ' ) and (' ||
                           ':ORS' ||  l_operator || '   '   || l_pre ||  pos_grp1_rec.value || l_suf ;

Line: 523

          l_select   :=   l_select  || '  or  '   ||
                          ':POI' ||  l_operator || '   '   || l_pre ||  pos_grp1_rec.value || l_suf ;

Line: 527

          l_select      :=  l_select || ' or  ' ||
                           ':OFS' ||  l_operator  || '   '   || l_pre ||  pos_grp1_rec.value || l_suf ;

Line: 531

          l_select      :=  l_select || '  or  ' ||
                           ':ORS' ||  l_operator  || '   '  || l_pre ||  pos_grp1_rec.value || l_suf ;

Line: 537

   l_select     := l_select    || ' ) ';

Line: 539

   p_pos_grp1_select  :=  l_select;

Line: 546

   p_pos_grp1_select   := null;

Line: 555

    p_pos_grd_select   out nocopy long,
    p_pay_plan_exists  out nocopy boolean,
    p_grade_exists     out nocopy boolean
   ) is


   l_select  long;

Line: 571

     Select  val.relational_operator,
             val.value,
             col.column_name
     from    ghr_mass_award_criteria_cols col,
             ghr_mass_award_criteria_vals val
     Where   val.mass_award_id       = p_mass_award_id
     and     col.table_name          = 'POSITION_EXTRA_INFO'
     and     val.mass_award_criteria_col_id  = col.mass_award_criteria_col_id
     and     ( col.column_name = 'Pay Plan'
     or        col.column_name = 'Grade Or Level'
     ) order by 3,1;

Line: 584

    l_select  := 'select 1 from dual where (1 = 1';

Line: 605

          l_select      :=  l_select || ' ) and (' ||
                           ':PP' || l_operator || '   '     || l_pre ||  pos_grp1_rec.value || l_suf ;

Line: 609

          l_select      :=  l_select || ' ) and (' ||
                           ':GRD' ||   l_operator  || '   '    || l_pre ||  pos_grp1_rec.value || l_suf ;

Line: 615

          l_select   :=   l_select  || '  or  '   ||
                          ':PP' ||  l_operator || '   '   || l_pre ||  pos_grp1_rec.value || l_suf ;

Line: 619

          l_select      :=  l_select || ' or  ' ||
                           ':GRD' ||  l_operator  || '   '   || l_pre ||  pos_grp1_rec.value || l_suf ;

Line: 625

   l_select     := l_select    || ' ) ';

Line: 627

   p_pos_grd_select  :=  l_select;

Line: 632

    p_pos_grd_select   := null;

Line: 641

   p_loc_ddf_select  out nocopy long,
   p_lei_exists      out nocopy boolean
  )
  is

   l_select          long;

Line: 658

    Select  val.relational_operator,
             val.value,
             col.column_name
     from    ghr_mass_award_criteria_cols col,
             ghr_mass_award_criteria_vals val
     Where   val.mass_award_id       = p_mass_award_id
     and     col.table_name          = 'LOCATION_EXTRA_INFO'
     and     val.mass_award_criteria_col_id  = col.mass_award_criteria_col_id
     order by 3,1;

Line: 670

     l_select   :=   'Select 1 from dual  where  1 = 1  ' ;

Line: 684

           l_select := l_select || ' and  ' ;

Line: 686

           l_select := l_select || ' or ' ;

Line: 690

       l_select :=  l_select ||
                    'upper( ' || ':DSC' || ')' || ' ' || l_operator ||  '  ' ||
                     'upper('    ||  l_pre || loc_ddf_values.value || l_suf ||
                     ')'    ;

Line: 698

       l_Select :=  l_select ||   ' or 1 = 0 ' ;

Line: 700

       l_select :=  l_select || ' and 1 = 1 ';

Line: 703

     p_loc_ddf_select  := l_select;

Line: 709

   p_loc_ddf_select  := null;

Line: 717

   p_rating_select      out nocopy long,
   p_rating_exists      out nocopy boolean
  )
  is

   l_select          long;

Line: 732

    Select  val.relational_operator,
             val.value,
             col.column_name
     from    ghr_mass_award_criteria_cols col,
             ghr_mass_award_criteria_vals val
     Where   val.mass_award_id       = p_mass_award_id
     and     col.table_name          = 'PERSON_SIT'
     and     val.mass_award_criteria_col_id  = col.mass_award_criteria_col_id
     order by 3,1;

Line: 745

     l_select   :=   'Select 1 from dual  where  1 = 1 ' ;

Line: 758

           l_select := l_select || ' and  ' ;

Line: 760

           l_select := l_select || ' or ' ;

Line: 763

       l_select :=  l_select ||
                    'upper( ' || ':RATING' || ')' || ' ' || l_operator ||  '  ' ||
                     'upper('    ||  l_pre || rating_values.value || l_suf ||
                     ')'     ;

Line: 771

       l_Select :=  l_select ||   ' or 1 = 0 ' ;

Line: 773

       l_select :=  l_select || ' and 1 = 1 ';

Line: 776

     p_rating_select  := l_select;

Line: 782

   p_rating_select      := null;

Line: 797

                p_asg_select       =>  l_asg_select
                );

Line: 800

                        p_kff_select     => l_kff_select,
                        p_pos_exists     => l_pos_exists,
                        p_job_exists     => l_job_exists
                        );

Line: 806

                     p_pos_grp1_select   => l_pos_grp1_select,
                     p_poi_exists        => l_poi_exists,
                     p_ofs_exists        => l_ofs_exists,
                     p_ors_exists        => l_ors_exists
                     );

Line: 814

                     p_pos_grp2_select   => l_pos_grp2_select,
                     p_poc_exists        => l_poc_exists
                     );

Line: 819

                     p_pos_grd_select    => l_pos_grd_select,
                     p_pay_plan_exists   => l_pay_plan_exists,
                     p_grade_exists      => l_grade_exists
                     );

Line: 825

                     p_loc_ddf_select    => l_loc_ddf_select,
                     p_lei_exists        => l_lei_exists
                     );

Line: 832

                   p_rating_select   =>  l_rating_select,
                   p_rating_exists   =>  l_rating_exists);

Line: 838

  DBMS_SQL.PARSE(l_cursor_id,l_asg_select,DBMS_SQL.NATIVE);

Line: 865

     DBMS_SQL.PARSE(l_cursor_kff_id,l_kff_select,DBMS_SQL.NATIVE);

Line: 888

         DBMS_SQL.PARSE(l_cursor_pos_grp1_id,l_pos_grp1_select,DBMS_SQL.NATIVE);

Line: 913

              DBMS_SQL.PARSE(l_cursor_pos_grp2_id,l_pos_grp2_select,DBMS_SQL.NATIVE);

Line: 935

              DBMS_SQL.PARSE(l_cursor_pos_grd_id,l_pos_grd_select,DBMS_SQL.NATIVE);

Line: 978

                 DBMS_SQL.PARSE(l_cursor_loc_ddf_id,l_loc_ddf_select,DBMS_SQL.NATIVE);

Line: 991

                     DBMS_SQL.PARSE(l_cursor_rating_id,l_rating_select,DBMS_SQL.NATIVE);

Line: 1056

         Elsif l_status  = 'DESELECTED' OR l_status =  'DESELECTED PRG:'  then
             l_desel_ctr :=  l_desel_ctr + 1;

Line: 1101

          'Deselected  : ' || to_char(l_desel_ctr)    || '  '  || l_new_line ||
          'Failure     : ' || to_char(l_err_man_ctr)  || '  '  || l_new_line ||
          'Failure - Retained for Resubmission : '    || to_char(l_err_mass_ctr);