APPS.GHR_PER_SUM SQL Statements

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

Line: 317

  SELECT gdf.segment1 pay_plan
        ,gdf.segment2 grade_or_level
        ,grd.name     grade_name
  FROM  per_grade_definitions gdf
       ,per_grades            grd
  WHERE grd.grade_id = p_grade_id
  AND   grd.grade_definition_id = gdf.grade_definition_id;

Line: 348

  SELECT pei.pei_information1 date_from
        ,pei.pei_information2 date_to
  FROM   per_people_extra_info pei
  WHERE  pei.person_extra_info_id = p_person_extra_info_id;

Line: 384

  SELECT 1
  FROM   per_people_extra_info pei
  WHERE  pei.person_id             = p_person_id
  AND    pei.information_type      = 'GHR_US_RETAINED_GRADE'
  AND    pei.person_extra_info_id <> NVL(p_person_extra_info_id,-999);

Line: 403

  SELECT poi.description
  FROM   ghr_pois poi
  WHERE  poi.personnel_office_id = p_personnel_office_id;

Line: 424

  SELECT dst.duty_station_code
        ,dstv.duty_station_desc
        ,lpa.short_name
        ,lpa.adjustment_percentage
  FROM   ghr_locality_pay_areas_f lpa
        ,ghr_duty_stations_v      dstv
        ,ghr_duty_stations_f      dst
        ,hr_location_extra_info   lei
  WHERE lei.location_id = p_location_id
  AND   lei.information_type = 'GHR_US_LOC_INFORMATION'
  AND   dst.duty_station_id = lei.lei_information3
  AND   NVL(p_effective_date,TRUNC(sysdate)) BETWEEN dst.effective_start_date AND dst.effective_end_date
  AND   dstv.duty_station_id = dst.duty_station_id
  AND   NVL(p_effective_date,TRUNC(sysdate)) BETWEEN dstv.effective_start_date AND dstv.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: 461

  SELECT org.name
  FROM   hr_organization_units org
  WHERE  org.organization_id = p_org_id;

Line: 466

  SELECT oi.org_information5  org_line1
        ,oi.org_information6  org_line2
        ,oi.org_information7  org_line3
        ,oi.org_information8  org_line4
        ,oi.org_information9  org_line5
        ,oi.org_information10 org_line6
  FROM  hr_organization_information oi
  WHERE oi.organization_id = p_org_id
  AND   oi.org_information_context = 'GHR_US_ORG_REPORTING_INFO';

Line: 506

  SELECT  eev.screen_entry_value
         ,eev.effective_start_date
  FROM    pay_element_types_f        elt
         ,pay_input_values_f         ipv
         ,pay_element_entries_f      ele
         ,pay_element_entry_values_f eev
  WHERE  p_effective_date BETWEEN elt.effective_start_date AND elt.effective_end_date
  AND    p_effective_date BETWEEN ipv.effective_start_date AND ipv.effective_end_date
  AND    p_effective_date BETWEEN ele.effective_start_date AND ele.effective_end_date
  AND    p_effective_date BETWEEN eev.effective_start_date AND eev.effective_end_date
  AND    elt.element_type_id    = ipv.element_type_id
  AND    upper(elt.element_name)= upper(p_element_name)
  AND    ipv.input_value_id     = eev.input_value_id
  AND    ele.assignment_id      = p_assignment_id
  AND    ele.element_entry_id+0 = eev.element_entry_id
  AND    upper(ipv.name )       = upper(p_input_value_name)
--  AND    NVL(elt.business_group_id,0)  = NVL(ipv.business_group_id,0)
  AND    (elt.business_group_id is NULL or elt.business_group_id  = p_bg_id);

Line: 528

       Select distinct business_group_id bg
       from per_assignments_f
       where assignment_id = p_assignment_id
       and   p_eff_date between effective_start_date
             and effective_end_date;

Line: 601

    SELECT fcl.lookup_code
    FROM   fnd_common_lookups fcl
    WHERE  fcl.APPLICATION_ID = 800
    AND fcl.LOOKUP_TYPE = 'GHR_US_TSP_STATUS'
    AND fcl.meaning = p_status;

Line: 626

  SELECT  eev.screen_entry_value
         ,eev.effective_start_date
  FROM   pay_element_entry_values_f eev
         ,pay_input_values_f ipv
  WHERE  p_input_value_name = ipv.name
  AND    p_effective_date BETWEEN ipv.effective_start_date AND ipv.effective_end_date
  AND    ipv.input_value_id = eev.input_value_id
  AND    p_element_entry_id = eev.element_entry_id
  AND    p_effective_date BETWEEN eev.effective_start_date AND eev.effective_end_date;

Line: 653

  SELECT 1
  FROM   per_info_type_security its
  WHERE  its.application_id       = p_application_id
  AND    its.responsibility_id    = p_responsibility_id
  AND    its.info_type_table_name = p_info_type_table_name
  AND    its.information_type     = p_information_type;

Line: 673

    SELECT hrw.workflow_id
    FROM   hr_workflows hrw
    WHERE  hrw.workflow_name = p_workflow_name;

Line: 690

    SELECT rpa.first_noa_code
    FROM   ghr_pa_requests rpa
    WHERE  rpa.pa_request_id = p_pa_request_id;

Line: 708

    SELECT rpa.second_noa_code
    FROM   ghr_pa_requests rpa
    WHERE  rpa.pa_request_id = p_pa_request_id;

Line: 728

 SELECT per.date_of_birth
,ast.per_system_status
FROM per_assignment_status_types ast
,per_assignments_f           asg
,per_people_f                per
WHERE asg.assignment_id = p_assignment_id
AND asg.person_id = per.person_id
AND p_effective_date BETWEEN asg.effective_start_date AND asg.effective_end_date
AND ast.assignment_status_type_id = asg.assignment_status_type_id
AND asg.primary_flag = 'Y'
AND asg.assignment_type = 'E';

Line: 755

 select current_employee_flag from per_all_people_f
 where person_id = p_person_id
 and p_effective_date
  between effective_start_date and effective_end_date;

Line: 775

 select assignment_id from per_all_assignments_f paf
 where paf.person_id = p_person_id
 and p_effective_date
  between paf.effective_start_date and paf.effective_end_date;

Line: 795

 select start_date
 from per_time_periods ptp
 where payroll_id in (select payroll_id
                      from per_assignments_f
                      where assignment_id = p_assignment_id
                      and trunc(p_effective_date) between effective_start_date
                                                  and     effective_end_date)
 and trunc(p_effective_date) between start_date and end_date;