APPS.GHR_FORMULA_FUNCTIONS SQL Statements

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

Line: 24

      select style,region_2
      from   per_addresses_v
      where  person_id = l_person_id
      and    primary_flag = 'Y';

Line: 30

      select distinct 'N'
      from   ghr_plan_service_areas_f
      where  plan_short_code = l_plan_code
      and    p_effective_date between effective_start_date and effective_end_date;

Line: 37

      select 'Y'
      from   ghr_plan_service_areas_f
      where  plan_short_code = l_plan_code
      and    ds_state_code   = l_ds_code
      and    p_effective_date between effective_start_date and effective_end_date;

Line: 44

      select 'Y'
      from   ghr_plan_service_areas_f
      where  plan_short_code  = l_plan_code
      and    state_short_name = l_state_code
      and    p_effective_date between effective_start_date and effective_end_date;

Line: 55

       select element_name
       from   pay_element_types_f elt
       where  element_type_id in
           (select element_type_id
            from   pay_element_links_f
            where  element_link_id in
                (select element_link_id
                 from   pay_element_entries_f
                 where  assignment_id = p_asg_id
                 and    p_effective_date between effective_start_date and effective_end_date)
           and p_effective_date between effective_start_date and effective_end_date)
       and upper(element_name) =
      upper(pqp_fedhr_uspay_int_utils.return_new_element_name
         ('Health Benefits Pre tax',p_business_group_id,p_effective_date,NULL))
       and p_effective_date between effective_start_date and effective_end_date
       and (elt.business_group_id is null or elt.business_group_id= p_business_group_id );

Line: 80

       select element_name
       from   pay_element_types_f elt
       where  element_type_id in
           (select element_type_id
            from   pay_element_links_f
            where  element_link_id in
                (select element_link_id
                 from   pay_element_entries_f
                 where  assignment_id = p_asg_id
                 and    p_effective_date between effective_start_date and effective_end_date)
           and p_effective_date between effective_start_date and effective_end_date)
       and upper(element_name) =
                        upper(pqp_fedhr_uspay_int_utils.return_new_element_name
                         ('Health Benefits',p_business_group_id,p_effective_date,NULL))
       and p_effective_date between effective_start_date and effective_end_date
       and (elt.business_group_id is null or elt.business_group_id= p_business_group_id);

Line: 125

    select asg.person_id,
           asg.location_id
     into  v_person_id,
           v_location_id
     from  per_all_people_f per,
           per_assignments_f asg
   where   asg.assignment_id = p_asg_id
     and   asg.business_group_id = p_business_group_id
     and   p_effective_date between asg.effective_start_date and asg.effective_end_date
     and   per.person_id = asg.person_id
     and   per.business_group_id = p_business_group_id
     and   p_effective_date between per.effective_start_date and per.effective_end_date;

Line: 143

    select short_code into v_plan_short_code
    from   ben_pl_f
    where  pl_id = p_pl_id
    and    p_effective_date between effective_start_date and effective_end_date;

Line: 284

    select short_code from ben_pl_f
    where  pl_id = p_pl_id
    and    p_effective_date between effective_start_date and effective_end_date;

Line: 316

    select short_code from ben_opt_f
    where  opt_id = p_opt_id
    and    p_effective_date between effective_start_date and effective_end_date;

Line: 349

    select trunc(effective_date) session_date
      from fnd_sessions
      where session_id = (select userenv('sessionid') from dual);

Line: 353

    select person_id from
    per_assignments_f
    where assignment_id     = p_assignment_id
    and business_group_id   = p_business_group_id
    and primary_flag        = 'Y'
    and assignment_type    <> 'B'
    and l_session_date
    between effective_start_date
    and effective_end_date;

Line: 363

    SELECT pty.system_person_type
    FROM per_people_f ppf, per_person_types pty
    WHERE  ppf.person_id = l_person_id
    AND    l_session_date
    BETWEEN ppf.effective_start_date AND ppf.effective_end_date
    AND    ppf.person_type_id = pty.person_type_id
    AND    pty.business_group_id = p_business_group_id
    AND    pty.active_flag = 'Y';

Line: 573

     Select person_id,payroll_id
     from   per_all_assignments_f
     where  assignment_id = p_asg_id
     and    trunc(p_effective_date) between effective_start_date and effective_end_date;

Line: 579

     select enrt_cvg_strt_dt,rt_val
     from   ben_prtt_enrt_rslt_f perf , ben_prtt_rt_val prv
     where  perf.person_id = l_person_id
     and    perf.pgm_id    = p_pgm_id
     and    perf.pl_id     = p_pl_id
     and    perf.prtt_enrt_rslt_id = prv.prtt_enrt_rslt_id
     and    trunc(l_effective_date) between perf.effective_start_date and perf.effective_end_date
     and    perf.enrt_cvg_thru_dt = hr_api.g_eot
     and    prv.rt_end_dt = hr_api.g_eot
     and    perf.prtt_enrt_rslt_stat_cd is null;

Line: 677

     Select person_id
     from   per_all_assignments_f
     where  assignment_id = p_asg_id
     and    trunc(p_effective_date) between effective_start_date and effective_end_date;

Line: 683

     select rt_val,enrt_cvg_strt_dt
     from   ben_prtt_rt_val , ben_prtt_enrt_rslt_f
     where  ben_prtt_rt_val.prtt_enrt_rslt_id = ben_prtt_enrt_rslt_f.prtt_enrt_rslt_id
     and    ben_prtt_enrt_rslt_f.person_id = l_person_id
     and    ben_prtt_enrt_rslt_f.pgm_id    = p_pgm_id
     and    ben_prtt_enrt_rslt_f.pl_id     = p_pl_id
     and    trunc(l_effective_date) between ben_prtt_enrt_rslt_f.effective_start_date
                                    and ben_prtt_enrt_rslt_f.effective_end_date
     and    ben_prtt_enrt_rslt_f.enrt_cvg_thru_dt = hr_api.g_eot
     and    ben_prtt_rt_val.rt_end_dt = hr_api.g_eot
     and    ben_prtt_enrt_rslt_f.prtt_enrt_rslt_stat_cd is null;

Line: 829

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

Line: 894

     Select name from ben_opt_f
     where  opt_id = p_opt_id
     and    business_group_id = p_business_group_id
     and    trunc(p_effective_date) between effective_start_date and effective_end_date;

Line: 900

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

Line: 1030

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

Line: 1040

     select name
     from   ben_opt_f
     where  opt_id = p_opt_id
     and    trunc(p_effective_date) between effective_start_date and effective_end_date;

Line: 1148

     Select person_id,payroll_id
     from   per_all_assignments_f
     where  assignment_id = p_asg_id
     and    trunc(p_effective_date) between effective_start_date and effective_end_date;

Line: 1155

     select end_date,regular_payment_date
     from   per_time_periods
     where  payroll_id = l_payroll_id
     and    to_char(p_effective_date,'YYYY') = to_char(regular_payment_date,'YYYY')
     order by start_date desc;

Line: 1163

     Select date_of_birth
     from   per_all_people_f
     where  person_id = l_person_id
     and    trunc(l_current_check_date) between effective_start_date and effective_end_date;

Line: 1169

     select yrp.start_date,
            yrp.end_date
       from ben_yr_perd yrp,
            ben_popl_yr_perd cpy
      where cpy.pgm_id = p_pgm_id
        and cpy.yr_perd_id = yrp.yr_perd_id
        and l_current_check_date between yrp.start_date and yrp.end_date;

Line: 1179

    select pgm_id
    from   ben_pgm_f
    where  name = 'Federal Thrift Savings Plan (TSP)'
    and    business_group_id = p_business_group_id
    and    trunc(p_effective_date) between effective_start_date and effective_end_date;

Line: 1188

     select rt_strt_dt
     from   ben_prtt_enrt_rslt_f perf, ben_prtt_rt_val prv
     where  perf.person_id = l_person_id
     and    perf.pgm_id    = p_pgm_id
     and    perf.prtt_enrt_rslt_id = prv.prtt_enrt_rslt_id
     and    trunc(p_effective_date) between perf.effective_start_date and perf.effective_end_date
     and    perf.enrt_cvg_thru_dt = hr_api.g_eot
     and    prv.rt_end_dt = hr_api.g_eot
     and    perf.prtt_enrt_rslt_stat_cd is null;

Line: 1200

     select pl_id,oipl_id
     from   ben_prtt_enrt_rslt_f perf
     where  person_id = l_person_id
     and    pgm_id    = l_tsp_pgm_id
     --and    pl_id     = l_pl_id
     and    trunc(l_db_next_pay_start_date) between effective_start_date and effective_end_date
     and    enrt_cvg_thru_dt = hr_api.g_eot
     and    prtt_enrt_rslt_stat_cd is null;

Line: 1210

     select name from ben_opt_f
     where  opt_id in (select opt_id from ben_oipl_f
                       where  oipl_id = l_oipl_id
                       and    p_effective_date between effective_start_date and
effective_end_date)
     and    p_effective_date between effective_start_date and effective_end_date;

Line: 1218

     select start_date,end_date,regular_payment_date
     from   per_time_periods
     where  payroll_id = l_payroll_id
     and p_effective_date between start_date and end_date
     --and    end_date = trunc(p_effective_date)
     order by start_date ;

Line: 1226

     select start_date,end_date,regular_payment_date
     from   per_time_periods
     where  payroll_id = l_payroll_id
     and    start_date >= trunc(p_effective_date)
     order by start_date ;

Line: 1390

     SELECT ghr_ss_views_pkg.get_ele_entry_value_ason_date (eef.element_entry_id, 'Enrollment', eef.effective_start_date) enrollment
     FROM   pay_element_entries_f eef,
            pay_element_types_f elt
     WHERE  assignment_id = p_asg_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_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,
                                                               p_business_group_id,
                                                               p_effective_date))
                          IN  ('HEALTH BENEFITS','HEALTH BENEFITS PRE TAX')  ;

Line: 1437

     SELECT ghr_ss_views_pkg.get_ele_entry_value_ason_date (eef.element_entry_id,
                                                           'Temps Total Cost',
                                                           p_effective_date - 1) temps_cost
     FROM   pay_element_entries_f eef,
            pay_element_types_f elt
     WHERE  assignment_id = p_asg_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_effective_date - 1) between eef.effective_start_date
                                   and eef.effective_end_date
     AND    upper(pqp_fedhr_uspay_int_utils.return_old_element_name(elt.element_name,
                                                               p_business_group_id,
                                                               p_effective_date))
                          IN  ('HEALTH BENEFITS','HEALTH BENEFITS PRE TAX')  ;

Line: 1486

     Select person_id
     from   per_all_assignments_f
     where  assignment_id = p_asg_id
     and    trunc(p_effective_date) between effective_start_date and effective_end_date;

Line: 1492

     select name
     from   ben_ler_f
     where  ler_id = p_ler_id
     and    trunc(p_effective_date) between effective_start_date and effective_end_date;

Line: 1498

     select name
     from   ben_opt_f
     where  opt_id = p_opt_id
     and    trunc(p_effective_date) between effective_start_date and effective_end_date;

Line: 1504

     select name
     from   ben_pl_f
     where  pl_id = p_pl_id
     and    trunc(p_effective_date) between effective_start_date and effective_end_date;

Line: 1612

     Select person_id
     from   per_all_assignments_f
     where  assignment_id = p_asg_id
     and    trunc(p_effective_date) between effective_start_date and effective_end_date;

Line: 1618

    select to_date(pei_information14,'yyyy/mm/dd hh24:mi:ss') agency_date
    from   ghr_people_extra_info_h_v
    where  pa_history_id =
           (select ghr_ss_views_pkg.get_people_ei_id_ason_date(v_person_id,
                                                              'GHR_US_PER_BENEFIT_INFO',
                                                               p_effective_date) from dual);

Line: 1650

     Select person_id
     from   per_all_assignments_f
     where  assignment_id = p_asg_id
     and    trunc(p_effective_date) between effective_start_date and effective_end_date;

Line: 1656

    select to_date(pei_information15,'yyyy/mm/dd hh24:mi:ss') emp_date
    from   ghr_people_extra_info_h_v
    where  pa_history_id =
           (select ghr_ss_views_pkg.get_people_ei_id_ason_date(v_person_id,
                                                              'GHR_US_PER_BENEFIT_INFO',
                                                               p_effective_date) from dual);

Line: 1689

     Select person_id
     from   per_all_assignments_f
     where  assignment_id = p_asg_id
     and    trunc(p_effective_date) between effective_start_date and effective_end_date;

Line: 1695

    select to_date(pei_information10,'yyyy/mm/dd hh24:mi:ss') coe_date
    from   ghr_people_extra_info_h_v
    where  pa_history_id =
           (select ghr_ss_views_pkg.get_people_ei_id_ason_date(v_person_id,
                                                              'GHR_US_PER_BENEFIT_INFO',
                                                               p_effective_date) from dual);

Line: 1735

     Select person_id,payroll_id
     from   per_all_assignments_f
     where  assignment_id = p_asg_id
     and    trunc(p_effective_date) between effective_start_date and effective_end_date;

Line: 1742

     select decode(PER.CURRENT_EMPLOYEE_FLAG,'Y',PPS.DATE_START,null) hire_date
     from per_all_people_f per, per_periods_of_service pps
     where per.person_id = v_person_id
     and   per.person_id = pps.person_id
     and   PER.EMPLOYEE_NUMBER IS NOT NULL
     and   PPS.DATE_START = (SELECT MAX(PPS1.DATE_START)
                             FROM   PER_PERIODS_OF_SERVICE PPS1
                             WHERE  PPS1.PERSON_ID = PER.PERSON_ID
                               AND  PPS1.DATE_START <=  PER.EFFECTIVE_END_DATE) ;

Line: 1754

    select 'Y'
    from   per_all_assignments_f
    where  person_id = v_person_id
    and    (p_effective_date - 30) between effective_start_date and effective_end_date
    and assignment_type <> 'B';

Line: 1762

            (PER.EMPLOYEE_NUMBER IS NOT NULL AND PPS.DATE_START = (SELECT MAX(PPS1.DATE_START)
             FROM PER_PERIODS_OF_SERVICE PPS1
             WHERE PPS1.PERSON_ID = PER.PERSON_ID
             AND PPS1.DATE_START <= PER.EFFECTIVE_END_DATE))) AND ((PER.NPW_NUMBER IS NULL) OR
               (PER.NPW_NUMBER IS NOT NULL AND PPP.DATE_START =
                 (SELECT MAX(PPP1.DATE_START) FROM PER_PERIODS_OF_PLACEMENT PPP1
                    WHERE PPP1.PERSON_ID = PER.PERSON_ID AND PPP1.DATE_START <= PER.EFFECTIVE_END_DATE)))
*/
     -- get latest rehire or transfer date
     Cursor c_get_latest_hire_noac is
     select noa_family_code,first_noa_code
     from   ghr_pa_requests
     where  person_id = v_person_id
     and    noa_family_code in ('APP','CONV_APP')
     and    nvl(effective_date,hr_api.g_date) = trunc(p_effective_date);

Line: 1780

     select start_date
     from   per_time_periods
     where  payroll_id  = v_payroll_id
     and    start_date >= trunc(p_effective_date)
     order by start_date ;

Line: 1870

     Select person_id,payroll_id
     from   per_all_assignments_f
     where  assignment_id = p_asg_id
     and    trunc(p_effective_date) between effective_start_date and effective_end_date;

Line: 1876

     select rt_strt_dt,rt_val
     from   ben_prtt_enrt_rslt_f perf , ben_prtt_rt_val prv
     where  perf.person_id = l_person_id
     and    perf.pgm_id    = p_pgm_id
     and    perf.pl_id     = p_pl_id
     and    perf.prtt_enrt_rslt_id = prv.prtt_enrt_rslt_id
     and    trunc(l_effective_date) between perf.effective_start_date and perf.effective_end_date
     and    perf.enrt_cvg_thru_dt = hr_api.g_eot
     and    prv.rt_end_dt = hr_api.g_eot
     and    perf.prtt_enrt_rslt_stat_cd is null;

Line: 1889

     select regular_payment_date,end_date
     from   per_time_periods
     where  payroll_id  = l_payroll_id
     and    start_date >= trunc(l_rt_strt_dt)
     order by start_date ;

Line: 1993

     select payroll_id
     from   per_assignments_f
     where  assignment_id = p_asg_id
     and    p_effective_date between effective_start_date and effective_end_date;

Line: 1999

     select start_date,end_date,regular_payment_date
     from   per_time_periods
     where  payroll_id = l_payroll_id
     and    start_date = trunc(p_payroll_period_start_date)
     order by start_date ;

Line: 2006

     Select date_of_birth
     from   per_all_people_f
     where  person_id = p_person_id
     and    trunc(l_current_check_date) between effective_start_date and effective_end_date;