APPS.GHR_CREATE_PTNL_LIFE_EVENTS SQL Statements

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

Line: 48

  select lei.lei_information3,
         dut.duty_station_code
  from   hr_location_extra_info lei,
         per_all_assignments_f asg,
         ghr_duty_stations_v dut
  where  p_pa_request_rec.employee_assignment_id = asg.assignment_id
  and    p_pa_request_rec.effective_date between
         asg.effective_Start_date and asg.effective_end_date
  and    asg.location_id = lei.location_id
  and    lei.information_type = 'GHR_US_LOC_INFORMATION'
  and    lei.lei_information3 =  dut.duty_station_id
  and    p_pa_request_rec.effective_date between
         dut.effective_start_date and asg.effective_end_date;

Line: 66

  select business_group_id
  from   per_all_people_f
  where  person_id = p_pa_request_rec.person_id
  and    p_pa_request_rec.effective_date
  between effective_start_date and effective_end_date;

Line: 75

  select ler.ler_id
  from   ben_ler_f ler
  where  ler.business_group_id = l_business_group_id
  and    upper(ler.name)       = upper(l_life_event) ;

Line: 83

  select 1 from ben_ptnl_ler_for_per
  where person_id       =  p_pa_request_rec.person_id
  and lf_evt_ocrd_dt    =  p_pa_request_rec.effective_date
  and ler_id            =  l_ler_id
  and PTNL_LER_FOR_PER_STAT_CD = 'UNPROCD'
  and business_group_id = l_business_group_id;

Line: 93

     SELECT ghr_ss_views_pkg.get_ele_entry_value_ason_date (eef.element_entry_id,
                                                           'Enrollment',
                                                            p_pa_request_rec.effective_date) enrollment,
       decode(upper(pqp_fedhr_uspay_int_utils.return_old_element_name(elt.element_name,
                    l_business_group_id,
                    p_pa_request_rec.effective_date)),'HEALTH BENEFITS','After','HEALTH BENEFITS PRE TAX','Pre') ben_type

     FROM   pay_element_entries_f eef,
            pay_element_types_f elt
     WHERE  assignment_id = p_pa_request_rec.employee_assignment_id
     AND    elt.element_type_id = eef.element_type_id
     AND    eef.effective_start_date BETWEEN elt.effective_start_date  AND
            elt.effective_end_date
     and    p_pa_request_rec.effective_date  between eef.effective_start_date
                                   and eef.effective_end_date
     AND    upper(pqp_fedhr_uspay_int_utils.return_old_element_name(elt.element_name,
                                                               l_business_group_id,
                                                               p_pa_request_rec.effective_date))
                          IN  ('HEALTH BENEFITS','HEALTH BENEFITS PRE TAX')  ;

Line: 114

     select 1
     from   per_all_people_f , per_person_types
     where  person_id = p_pa_request_rec.person_id
     and    trunc (p_pa_request_rec.effective_date - 1) between
            effective_start_date and effective_end_date
     and    per_all_people_f.business_group_id = per_person_types.business_group_id
     and    per_all_people_f.person_type_id = per_person_types.person_type_id
     and    system_person_type = 'EX_EMP';

Line: 124

     select 1
     from   ghr_pa_requests pa
     where  pa.noa_family_code = 'SEPARATION'
     and    pa.effective_date = p_pa_request_rec.effective_date - 1
     and    pa.person_id = p_pa_request_rec.person_id
     and    exists (select '1'
	             from ghr_pa_history pah
  		     where pah.pa_request_id = pa.pa_request_id);

Line: 136

   select  pa.duty_station_code, pa.work_schedule, pa.effective_date
   from    ghr_pa_requests pa
   where   pa.noa_family_code = 'SEPARATION'
     and   pa.effective_date < p_pa_request_rec.effective_date
     and   pa.person_id = p_pa_request_rec.person_id
     and   exists (select '1'
	             from ghr_pa_history pah
  		     where pah.pa_request_id = pa.pa_request_id)
   order by pa.effective_date desc ;