APPS.GHR_PAY_CALC SQL Statements

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

Line: 36

select range_or_match
from   pay_user_tables
where  user_table_id = l_user_table_id;

Line: 291

  SELECT 1
  FROM   hr_all_positions_f pos
  WHERE  p_pay_calc_data.position_id = pos.position_id
  AND    p_pay_calc_data.effective_date BETWEEN pos.effective_start_date
                                        and     pos.effective_end_date;

Line: 494

  SELECT lpa.adjustment_percentage
  FROM   ghr_locality_pay_areas_f lpa
        ,ghr_duty_stations_f      dst
  WHERE  dst.duty_station_id = p_duty_station_id
  AND    NVL(p_effective_date,TRUNC(sysdate))  between dst.effective_start_date and dst.effective_end_date
  AND    dst.locality_pay_area_id = lpa.locality_pay_area_id
  AND    NVL(p_effective_date,TRUNC(sysdate))  between lpa.effective_start_date and lpa.effective_end_date;

Line: 521

  SELECT NVL(lpa.leo_adjustment_percentage, lpa.adjustment_percentage) adj_percentage
  FROM   ghr_locality_pay_areas_f lpa
        ,ghr_duty_stations_f      dst
  WHERE  dst.duty_station_id = p_duty_station_id
  AND    NVL(p_effective_date,TRUNC(sysdate))  between dst.effective_start_date and dst.effective_end_date
  AND    dst.locality_pay_area_id = lpa.locality_pay_area_id
  AND    NVL(p_effective_date,TRUNC(sysdate))  between lpa.effective_start_date and lpa.effective_end_date;

Line: 530

  SELECT lpa.adjustment_percentage adj_percentage
  FROM   ghr_locality_pay_areas_f lpa
        ,ghr_duty_stations_f      dst
  WHERE  dst.duty_station_id = p_duty_station_id
  AND    NVL(p_effective_date,TRUNC(sysdate))  between dst.effective_start_date and dst.effective_end_date
  AND    dst.locality_pay_area_id = lpa.locality_pay_area_id
  AND    NVL(p_effective_date,TRUNC(sysdate))  between lpa.effective_start_date and lpa.effective_end_date;

Line: 540

  SELECT leo_pay_area_code
  FROM   ghr_duty_stations_f
  WHERE  duty_station_id = p_duty_station_id
  AND    NVL(p_effective_date,TRUNC(sysdate))
         between effective_start_date and effective_end_date;

Line: 577

  SELECT uta.user_table_name
  FROM   pay_user_tables uta
  WHERE  uta.user_table_id = p_user_table_id;

Line: 602

SELECT pei_information3 grade,
       pei_information4 step,
       pei_information5 pay_plan,
	   pei_information9 temp_step
FROM per_people_extra_info
where person_id = c_person_id
and   information_type ='GHR_US_RETAINED_GRADE'
and   pei_information_category = 'GHR_US_RETAINED_GRADE'
and   c_effective_date  BETWEEN NVL(fnd_date.canonical_to_date(pei_information1),c_effective_date)
                          AND   NVL(fnd_date.canonical_to_date(pei_information2),c_effective_date);

Line: 614

SELECT pa_notification_id, to_step_or_rate
FROM ghr_pa_requests
WHERE pa_request_id = c_pa_request_id;

Line: 749

  SELECT cin.value basic_pay
        ,cin.effective_start_date
        ,cin.effective_end_date
  FROM   pay_user_column_instances_f cin
        ,pay_user_rows_f             urw
        ,pay_user_columns            col
  WHERE col.user_table_id = p_user_table_id
  AND   col.user_column_name = p_step_or_rate
  AND   urw.user_table_id = p_user_table_id
  -- Bug# 5132113 getting the value of GS when pay plan is GP
  AND   urw.row_low_range_or_name = decode(p_pay_plan,'GP','GS',p_pay_plan)||'-'||p_grade_or_level
  AND   NVL(p_effective_date,TRUNC(SYSDATE)) BETWEEN urw.effective_start_date AND urw.effective_end_date
  AND   cin.user_row_id = urw.user_row_id
  AND   cin.user_column_id = col.user_column_id
  AND   NVL(p_effective_date,TRUNC(SYSDATE)) BETWEEN cin.effective_start_date AND cin.effective_end_date;

Line: 822

  SELECT cin.value basic_pay
  FROM   pay_user_column_instances_f cin
        ,pay_user_rows_f             urw
        ,pay_user_columns            col
        ,pay_user_tables             utb
  WHERE utb.user_table_name = ghr_pay_calc.l_standard_table_name
  AND   col.user_table_id = utb.user_table_id
  AND   col.user_column_name = p_step_or_rate
  AND   urw.user_table_id = utb.user_table_id
  AND   urw.row_low_range_or_name = p_pay_plan||'-'||p_grade_or_level
  AND   NVL(p_effective_date,TRUNC(SYSDATE)) BETWEEN urw.effective_start_date AND urw.effective_end_date
  AND   cin.user_row_id = urw.user_row_id
  AND   cin.user_column_id = col.user_column_id
  AND   NVL(p_effective_date,TRUNC(SYSDATE)) BETWEEN cin.effective_start_date AND cin.effective_end_date;

Line: 932

  SELECT utb.user_table_id
  FROM   pay_user_tables  utb
  WHERE utb.user_table_name = ghr_pay_calc.l_standard_table_name;

Line: 1027

  SELECT utb.user_table_id
  FROM   pay_user_tables  utb
  WHERE utb.user_table_name = p_user_table_name;

Line: 1033

  select 1 from pay_user_tables
  where substr(user_table_name,1,4) in ('999B','999C','999D','999E','999F')
  and user_table_id = l_user_table_id;

Line: 1040

  select 1 from pay_user_tables
  where substr(user_table_name,1,4) = 'ESSL'
  and user_table_id = l_user_table_id;

Line: 1054

  select adjustment_percentage from ghr_locality_pay_areas_f
  where locality_pay_area_code = 'WA'
  AND NVL(p_pay_calc_data.effective_date,TRUNC(sysdate))  between effective_start_date and effective_end_date;

Line: 1059

select duty_station_code from ghr_duty_stations_f
where substr(duty_station_code,1,2) between 'AA' and 'ZZ'
and substr(duty_station_code,2,1)<> 'Q'
and duty_station_id = p_duty_station_id
AND NVL(p_effective_date,TRUNC(sysdate))  between effective_start_date and effective_end_date;

Line: 1619

  SELECT ppi.ppi_percentage
  FROM   ghr_premium_pay_indicators ppi
  WHERE  code = p_ppi_code;

Line: 1800

select 1
from  per_person_types pet1,
      per_people_f     per1
where pet1.person_type_id = per1.person_type_id
and   per1.person_id      = p_person_id
and   nvl(p_effective_date,trunc(sysdate))
      between per1.effective_start_date and per1.effective_end_date
and   pet1.system_person_type = 'EX_EMP';

Line: 1815

   select pa_notification_id
   from ghr_pa_requests
   where pa_request_id = l_ghr_pa_request_rec.altered_pa_request_id;

Line: 1841

   SELECT from_pay_basis
   FROM ghr_pa_requests
   WHERE pa_request_id = c_pa_request_id;

Line: 1846

   SELECT altered_pa_request_id
   FROM ghr_pa_requests
   WHERE pa_request_id = c_pa_request_id;

Line: 1851

   SELECT pa_notification_id
   FROM ghr_pa_requests
   WHERE pa_request_id = c_alt_pa_request_id;

Line: 1858

SELECT assignment_id
FROM   per_all_assignments_f
WHERE  person_id = c_person_id
AND    assignment_type = 'E'
AND    c_effective_date between effective_start_date and effective_end_date;

Line: 1867

    select 'Y'
    from   ghr_pay_plans
    where  equivalent_pay_plan = 'ES'
    and    pay_plan            = l_pay_plan;

Line: 1877

  select 1 from pay_user_tables
  where substr(user_table_name,1,4) = 'ESSL'
  and user_table_id = l_user_table_id;

Line: 1893

    SELECT grd.grade_id  grade_id
    FROM per_grades grd,
         per_grade_definitions gdf
     WHERE  gdf.segment1 = v_pay_plan
    AND gdf.segment2 = v_grade_or_level
    AND grd.grade_definition_id = gdf.grade_definition_id
    AND grd.business_group_id = l_business_group_id;

Line: 1910

select equivalent_pay_plan from ghr_pay_plans
where PAY_PLAN = l_pay_plan;

Line: 1917

    SELECT   lc.locality_pay_area_code
    FROM     ghr_duty_stations_f ds, ghr_locality_pay_areas_f lc
    WHERE    ds.duty_station_id = NVL(p_duty_station_id,l_ghr_pa_request_rec.duty_station_id)
    AND      ds.locality_pay_area_id = lc.locality_pay_area_id
    AND      p_effective_date between  ds.effective_start_date and  ds.effective_end_date
    AND      p_effective_date between  lc.effective_start_date and  lc.effective_end_date;

Line: 3323

  SELECT utb.user_table_id
  FROM   pay_user_tables  utb
  WHERE utb.user_table_name = ghr_pay_calc.l_standard_table_name;

Line: 3459

  SELECT max(urw.ROW_HIGH_RANGE) ROW_HIGH_RANGE
        ,min(urw.ROW_LOW_RANGE_OR_NAME) ROW_LOW_RANGE_OR_NAME
  FROM   pay_user_column_instances_f cin
        ,pay_user_rows_f             urw
        ,pay_user_columns            col
  WHERE col.user_table_id = p_user_table_id
  AND   col.user_column_name = p_pay_plan||'-'||p_grade_or_level
  AND   urw.user_table_id = p_user_table_id
  AND   cin.user_row_id = urw.user_row_id
  AND   cin.user_column_id = col.user_column_id
  AND   NVL(p_effective_date,TRUNC(SYSDATE))
        BETWEEN urw.effective_start_date AND urw.effective_end_date
  AND   NVL(p_effective_date,TRUNC(SYSDATE))
        BETWEEN cin.effective_start_date AND cin.effective_end_date;

Line: 3517

   SELECT paf.assignment_id, paf.position_id
   FROM per_assignments_f paf
   WHERE paf.person_id = c_person_id
   AND trunc(nvl(c_effective_date,sysdate))between paf.effective_start_date and paf.effective_end_date
   AND paf.primary_flag = 'Y'
   AND paf.assignment_type <> 'B';

Line: 3617

  SELECT utb.user_table_id
  FROM   pay_user_tables  utb
  WHERE utb.user_table_name = ghr_pay_calc.l_standard_table_name;

Line: 3630

   SELECT paf.assignment_id
   FROM per_assignments_f paf
   WHERE paf.person_id = c_person_id
   AND trunc(nvl(c_effective_date,sysdate))between paf.effective_start_date and paf.effective_end_date
   AND paf.primary_flag = 'Y'
   AND paf.assignment_type <> 'B';

Line: 3794

  SELECT utb.user_table_id
  FROM   pay_user_tables  utb
  WHERE utb.user_table_name = ghr_pay_calc.l_standard_table_name;

Line: 3807

   SELECT paf.assignment_id
   FROM per_assignments_f paf
   WHERE paf.person_id = c_person_id
   AND trunc(nvl(c_effective_date,sysdate))between paf.effective_start_date and paf.effective_end_date
   AND paf.primary_flag = 'Y'
   AND paf.assignment_type <> 'B';

Line: 3936

   SELECT 1
   FROM   ghr_pay_plans ppl
   WHERE  ppl.pay_plan = p_pay_plan
   AND    ppl.equivalent_pay_plan = 'GS';

Line: 3942

   SELECT 1
   FROM   ghr_pay_plans ppl
   WHERE  ppl.pay_plan = p_pay_plan
   AND    ppl.equivalent_pay_plan = 'FW';

Line: 4035

          SELECT cin.value basic_pay
                ,cin.effective_start_date
                ,cin.effective_end_date
                ,col.user_column_name step
          FROM   pay_user_column_instances_f cin
                ,pay_user_rows_f             urw
                ,pay_user_columns            col
          WHERE col.user_table_id = p_user_table_id
         -- AND   col.user_column_name = p_step_or_rate
          AND   urw.user_table_id = p_user_table_id
           --Bug# 7046241 added decode for pay plan GP
          AND   urw.row_low_range_or_name = decode(p_pay_plan,'GP','GS',p_pay_plan)||'-'||p_grade_or_level
          AND   NVL(p_effective_date,TRUNC(SYSDATE)) BETWEEN urw.effective_start_date AND urw.effective_end_date
          AND   cin.user_row_id = urw.user_row_id
          AND   cin.user_column_id = col.user_column_id
          AND   NVL(p_effective_date,TRUNC(SYSDATE)) BETWEEN cin.effective_start_date AND cin.effective_end_date;

Line: 4146

    SELECT utb.user_table_id user_table_id, utb.user_table_name user_table_name
    FROM   pay_user_tables  utb
    WHERE  utb.user_table_name = p_user_table_name;

Line: 4154

    SELECT grd.grade_id  grade_id
    FROM per_grades grd,
         per_grade_definitions gdf
     WHERE  gdf.segment1 = v_pay_plan
    AND gdf.segment2 = v_grade_or_level
    AND grd.grade_definition_id = gdf.grade_definition_id
    AND grd.business_group_id = l_business_group_id;

Line: 4183

         select equivalent_pay_plan
         from   ghr_pay_plans
         where  pay_plan = p_pay_plan;

Line: 4190

         select paf.position_id,
	        paf.grade_id
         from per_all_assignments_f paf
         where paf.person_id = c_person_id
         and trunc(nvl(c_effective_date,sysdate))between paf.effective_start_date and paf.effective_end_date
         and paf.primary_flag = 'Y'
         and paf.assignment_type <> 'B';

Line: 4200

         select from_position_id,
	        from_pay_plan  --Added for Bug # 10368444
         from   ghr_pa_requests
         where  pa_request_id = p_pa_request_id;

Line: 4206

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

Line: 4576

	-- GPPA Update46. Added 890 in the NOT IN list.
        IF p_pay_calc_data.noa_code NOT IN ('894','890') then
            p_pay_calc_out_data.open_pay_fields := TRUE;

Line: 4819

	       --6814842 modified to update position pay table and pay_table_upd_flag
	       --Bug# 10043212 added to set the position pay table only if the grade does not exist
  	       IF NOT(l_pp_grd_exists)  THEN
                  l_position_pay_table := l_default_pay_table;

Line: 4877

	    -- as it need to be updated based on locality pay or special pay which ever is higher
	    IF NOT(l_pp_grd_exists)  THEN
               l_position_pay_table := l_default_pay_table;

Line: 5358

SELECT equivalent_pay_plan,maximum_step
		FROM ghr_pay_plans
		WHERE pay_plan = pc_pay_plan;

Line: 5363

  SELECT utb.user_table_id
  FROM   pay_user_tables  utb
  WHERE utb.user_table_name = p_user_table_name;