eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.PER_UTILITY_FUNCTIONS SQL Statements

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

Line: 64


select start_date
,      end_date
,      period_num
from   per_time_periods
where  payroll_id = P_Payroll_ID
and    P_Date_In_Period between start_date and end_date;

Line: 116


select annual_rate
,      upper_limit
,      nvl(ceiling, 99999999)
,      nvl(max_carry_over, 99999999)
from   pay_accrual_bands
where  P_Number_Of_Years >= lower_limit
and    P_Number_Of_Years <  upper_limit
and    accrual_plan_id = P_Plan_ID;

Line: 372


select a.effective_start_date
,      a.effective_end_date
,      b.per_system_status
from   per_all_assignments_f a
,      per_assignment_status_types b
where  a.assignment_id = P_Assignment_ID
and    a.assignment_status_type_id = b.assignment_status_type_id
and    P_Effective_Date
        between a.effective_start_date and a.effective_end_date;

Line: 429


select count(*)
from per_time_periods ptp
where ptp.payroll_id = P_Payroll_ID
and ptp.end_date between
to_date('01/01/'||to_char(P_Calculation_Date, 'YYYY'), 'DD/MM/YYYY')
and to_date('31/12/' || to_char(P_Calculation_Date, 'YYYY'), 'DD/MM/YYYY');

Line: 437


select start_date
from per_time_periods
where payroll_id = p_payroll_id
and end_date = (select min(end_date)
                from per_time_periods
                where payroll_id = p_payroll_id
                and to_char(end_date, 'YYYY') = to_char(p_calculation_date, 'YYYY'));

Line: 458


  select max(end_date)
    into l_max_ed_cur_year
    from per_time_periods
   where payroll_id = p_payroll_id
     and to_char(end_date, 'YYYY') = to_char(p_calculation_date, 'YYYY');

Line: 467


    select min(start_date)
      into l_start_date
      from per_time_periods
     where payroll_id = p_payroll_id
       and end_date >= p_calculation_date;

Line: 513


   ncr.accrual_plan_id (PAY_NET_CALCULATION_RULES_FK1) which is more selective
3. By disabling the Primary Key join to the table the query can be driven off the
   element entry route which is more selective when driving through subsequent tables.
4. Added this predicate to help in the filtering
5. Very poor filter so disabled to drive of the source_id which is more selective
6. Same as above
7. Same as above
*/
/* modified the following cursor to improve performance
cursor c_get_date is
select nvl(min(pee1.effective_start_date), P_Accrual_Start_Date)
from pay_element_links_f pel1,
     pay_input_values_f piv,
     pay_net_calculation_rules ncr,
     pay_accrual_plans pap,
     pay_element_links_f pel2,
     pay_element_entries_f pee1, -- Change (1)
     pay_element_entries_f pee2
where pee1.element_link_id = pel1.element_link_id
and pel1.element_type_id = piv.element_type_id
and piv.input_value_id = ncr.input_value_id
and ncr.accrual_plan_id + 0 = pap.accrual_plan_id -- Change (2)
and pap.accrual_plan_element_type_id = pel2.element_type_id
and pel2.element_link_id = pee2.element_link_id
and pee1.assignment_id = p_assignment_id
and pee2.assignment_id = p_assignment_id
and pap.accrual_plan_id + 0 = p_accrual_plan_id -- Change (3)
and pee1.effective_start_date <= p_accrual_start_date - 1 -- Change (4)
and pee1.effective_end_date between p_turn_of_year_date
                            and p_accrual_start_date - 1
and not exists (select 1
                from pay_run_results prr
                where prr.source_id = pee1.element_entry_id
                and prr.element_type_id + 0 = pel1.element_type_id -- Change (5)
                and prr.status in ('P', 'PA')
                )
and not exists (select 1
                from pay_run_results prr,
                     pay_run_result_values rrv
                where prr.run_result_id = rrv.run_result_id
                and prr.source_id = pee2.element_entry_id
                and prr.element_type_id + 0 = pap.tagging_element_type_id -- Change (6)
                and rrv.result_value = pee1.element_entry_id
               );

Line: 560


select  /*+ index(pee1 PAY_ELEMENT_ENTRIES_F_N53) use_nl(ncr)*/
nvl(min(pee1.effective_start_date), P_Accrual_Start_Date)
from
     pay_input_values_f piv,
     pay_net_calculation_rules ncr,
     pay_accrual_plans pap,
     pay_element_entries_f pee1,
     pay_element_entries_f pee2
     where
 pee1.element_type_id = piv.element_type_id
and piv.input_value_id = ncr.input_value_id
and ncr.accrual_plan_id +0 = pap.accrual_plan_id
and pap.accrual_plan_element_type_id = pee1.element_type_id
and pee1.element_type_id = pee2.element_type_id
and pee1.assignment_id = p_assignment_id
and pee2.assignment_id = p_assignment_id
and pap.accrual_plan_id  = p_accrual_plan_id
and pee1.effective_start_date <= p_accrual_start_date
and pee1.effective_end_date between p_turn_of_year_date
                            and p_accrual_start_date
and not exists (select 1
                from pay_run_results prr
                where prr.source_id = pee1.element_entry_id
                and prr.element_type_id + 0 = pee1.element_type_id -- fix new
                and prr.status in ('P', 'PA')
                )
and not exists (select 1
                from pay_run_results prr,
                     pay_run_result_values rrv
                where prr.run_result_id = rrv.run_result_id
                and prr.source_id = pee2.element_entry_id
                and prr.element_type_id + 0 = pap.tagging_element_type_id
                and rrv.result_value = pee1.element_entry_id
               );

Line: 596


select 1
from pay_element_entries_f pee,
     pay_element_links_f pel,
     pay_accrual_plans pap
where pap.accrual_plan_id = p_accrual_plan_id
and   pee.assignment_id = p_assignment_id
and   pap.accrual_plan_element_type_id = pel.element_type_id
and   pel.element_link_id = pee.element_link_id
and   exists (select 1
              from pay_run_results prr
              where prr.source_id = pee.element_entry_id
              and prr.element_type_id + 0 = pel.element_type_id -- Change (7)
              and prr.status in ('P', 'PA')
              );

Line: 670


   ncr.accrual_plan_id (PAY_NET_CALCULATION_RULES_FK1) which is more selective
3. Very poor filter so disabled to drive of the source_id which is more selective
4. Same as above
5. Bulk collect is used to store the values into PL/SQL tables
*/
/*
modified the cursor to improve performance
cursor c_get_element (p_entry_id number,
                      p_effective_date date) is
select distinct pee1.element_entry_id
from pay_element_links_f pel1,
     pay_input_values_f piv,
     pay_net_calculation_rules ncr,
     pay_accrual_plans pap,
     pay_element_links_f pel2,
     pay_element_entries_f pee1,  -- Change (1)
     pay_element_entries_f pee2
where pee1.element_link_id = pel1.element_link_id
and pel1.element_type_id = piv.element_type_id
and piv.input_value_id = ncr.input_value_id
and ncr.accrual_plan_id + 0 = pap.accrual_plan_id -- Change (2)
and pap.accrual_plan_element_type_id = pel2.element_type_id
and pel2.element_link_id = pee2.element_link_id
and pee1.assignment_id = p_assignment_id +
                         decode (pel1.element_link_id, 0, 0, 0)
and pee2.assignment_id = p_assignment_id
and pee1.effective_end_date < p_effective_date
and pee2.element_entry_id = p_entry_id
and not exists (select 1
                from pay_run_results prr
                where prr.source_id = pee1.element_entry_id
                and prr.element_type_id + 0 = pel1.element_type_id -- Change (3)
                and prr.status in ('P', 'PA')
                )
and not exists (select 1
                from pay_run_results prr,
                     pay_run_result_values rrv
                where prr.run_result_id = rrv.run_result_id
                and prr.source_id = pee2.element_entry_id
                and prr.element_type_id + 0 = pap.tagging_element_type_id -- Change (4)
                and rrv.result_value = pee1.element_entry_id
               );

Line: 716


select  /*+ index(pee1 PAY_ELEMENT_ENTRIES_F_N53)*/
       distinct pee1.element_entry_id
from
     pay_input_values_f piv,
     pay_net_calculation_rules ncr,
     pay_accrual_plans pap,
     pay_element_entries_f pee1,
     pay_element_entries_f pee2
where
 pee1.element_type_id = piv.element_type_id
and piv.input_value_id = ncr.input_value_id
and ncr.accrual_plan_id +0 = pap.accrual_plan_id
and pap.accrual_plan_element_type_id = pee2.element_type_id
and pee1.element_type_id = pee2.element_type_id
and pee1.assignment_id = p_assignment_id
and pee2.assignment_id = p_assignment_id
and pee1.effective_end_date < p_effective_date
and pee2.element_entry_id = p_entry_id
and not exists (select 1
                from pay_run_results prr
                where prr.source_id = pee1.element_entry_id
                and prr.element_type_id + 0 = pee1.element_type_id
                and prr.status in ('P', 'PA')
                )
and not exists (select 1
                from pay_run_results prr,
                     pay_run_result_values rrv
                where prr.run_result_id = rrv.run_result_id
                and prr.source_id = pee2.element_entry_id
                and prr.element_type_id + 0 = pap.tagging_element_type_id
                and rrv.result_value = pee1.element_entry_id
               );

Line: 752


select ptp.start_date
from per_time_periods ptp,
     pay_payroll_actions ppa,
     pay_assignment_actions paa
where paa.payroll_action_id = ppa.payroll_action_id
and ppa.time_period_id = ptp.time_period_id
and paa.assignment_action_id = p_assignment_action_id;

Line: 835


    g_element_entries.delete(l_count);

Line: 986


select payroll_id
from   per_all_assignments_f
where  assignment_id = P_asg_ID
and    P_Date_In_Period between effective_start_date
                            and effective_end_date;

Line: 1032


select start_date
,      end_date
,      period_num
from   per_time_periods
where  payroll_id = P_Payroll_ID
and    P_Date_In_Period between start_date and end_date;

Line: 1082


     select parameter_name
           ,parameter_value
       from pay_action_parameters
      where parameter_name = p_prm_name;

Line: 1218


     select legislation_code
       from per_business_groups
       where organization_id = p_business_group_id;

Line: 1249


  select e.event_group_id,
         e.event_group_name,
         e.business_group_id,
         e.legislation_code
  from   pay_event_groups e
  where  e.event_group_name = p_event_name;

Line: 1353


  select max(actual_termination_date)
  FROM   per_all_assignments_f asg,
         per_periods_of_service pps
  where  asg.assignment_id = P_Assignment_id
    and  asg.period_of_service_id = pps.period_of_service_id;

Line: 1360


  select max(EFFECTIVE_END_DATE)
  FROM   per_all_assignments_f asg
  where  asg.assignment_id = P_Assignment_id
  and asg.assignment_type<>'B';

Line: 1406


select min(start_date),max(end_date)
from   per_time_periods
where  payroll_id = P_Payroll_ID;