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.PAY_FR_OVERTIME SQL Statements

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

Line: 70


select nvl(to_number(p.prl_information8),0) upper_compensation_threshold
,      greatest(start_of_year
               ,p.effective_start_date
               ,ee.effective_start_date) start_date
,      least(end_of_year
            ,p.effective_end_date
            ,ee.effective_end_date) end_date
from   pay_element_entries_f ee
,      pay_element_links_f el
,      pay_element_types_f et
,      pay_payrolls_f p
where  et.element_name = 'FR_OVERTIME'
and    p_effective_date
          between et.effective_start_date and et.effective_end_date
and    el.element_type_id = et.element_type_id
and    p_effective_date
          between el.effective_start_date and el.effective_end_date
and    el.element_link_id = ee.element_link_id
and    ee.assignment_id = p_assignment_id
and    p.effective_start_date <= ee.effective_end_date
and    p.effective_end_date >= ee.effective_start_date
and    fnd_number.canonical_to_number(ee.ENTRY_INFORMATION1) = p.payroll_id
and    p.effective_start_date <= end_of_year
and    p.effective_end_date >= start_of_year
and    ee.effective_start_date <= end_of_year
and    ee.effective_end_date >= start_of_year
order by greatest(start_of_year,p.effective_start_date,ee.effective_start_date);

Line: 162


Select to_char(SCHEME.PAYROLL_ID)
,PRL_INFORMATION1 -- Overtime_Scheme_Type
,PRL_INFORMATION2 -- Threshold
,PRL_INFORMATION3 -- Annual_Quota
,PRL_INFORMATION4 -- Weekly_Offset_Threshold
,nvl(TARGET.ENTRY_INFORMATION2,PRL_INFORMATION5) -- Bonification_Method
,nvl(TARGET.ENTRY_INFORMATION3,PRL_INFORMATION6) -- Majoration_Method
,PRL_INFORMATION7 -- Weekly_Compensation_Threshold
,'0' -- Upper_Compensation_Threshold
,PRL_INFORMATION9 -- Lower_Compensation_Factor
,PRL_INFORMATION10 -- Higher_Compensation_Factor
,PRL_INFORMATION11 -- Overtime_Band_Table
,PRL_INFORMATION12 -- Regularisation_Period_Type
,PRL_INFORMATION13 -- Regularisation_Period_Weeks
,PRL_INFORMATION14 -- Regularisation_Threshold
,PRL_INFORMATION15 -- Regularisation_Offset
,PRL_INFORMATION16 -- Regularisation_Payment_Basis
,nvl(TARGET.ENTRY_INFORMATION4,PRL_INFORMATION17) -- Regularisation_Start_Date
,nvl(PRL_INFORMATION18,'-1') -- Overtime Formula ID
,nvl(PRL_INFORMATION19,'-1') -- Regularisation Formula ID
from pay_element_entries_f                  TARGET
,       pay_element_links_f                    LINK
,       pay_element_types_f                    ELEMENT
,       pay_payrolls_f                         SCHEME
where   TARGET.assignment_id   = p_assignment_id
and     p_date BETWEEN TARGET.effective_start_date
                 AND TARGET.effective_end_date
and     TARGET.element_link_id = LINK.element_link_id
and     p_date BETWEEN LINK.effective_start_date
                 AND LINK.effective_end_date
and     LINK.element_type_id = ELEMENT.element_type_id
and     p_date BETWEEN ELEMENT.effective_start_date
                 AND ELEMENT.effective_end_date
and     ELEMENT.element_name = 'FR_OVERTIME'
and     fnd_number.canonical_to_number(TARGET.ENTRY_INFORMATION1)
                                            = SCHEME.payroll_id
and     p_date BETWEEN SCHEME.effective_start_date
                AND SCHEME.effective_end_date;

Line: 202


select r.ROW_LOW_RANGE_OR_NAME
,    decode(scheme.overtime_scheme_type,'F',ci1.VALUE,null) hours_value
,    decode(scheme.overtime_scheme_type,'P',ci1.VALUE,null) working_hours_value
,      ci2.VALUE factor_value
from   pay_user_rows_f r
,      pay_user_column_instances_f ci1
,      pay_user_column_instances_f ci2
,      pay_user_columns c1
,      pay_user_columns c2
where  r.user_table_id = p_band_table_id
and    p_date BETWEEN r.effective_start_date and r.effective_end_date
and    c1.user_table_id = p_band_table_id
and    c1.user_column_id = ci1.user_column_id
and    ci1.user_row_id = r.user_row_id
and    p_date BETWEEN ci1.effective_start_date and ci1.effective_end_date
and    c1.user_column_name = decode(scheme.overtime_scheme_type,
                                    'F','HOURS',
                                    'P','WORKING_HOURS_PERCENTAGE')
and    c2.user_table_id = p_band_table_id
and    c2.user_column_id = ci2.user_column_id
and    ci2.user_row_id = r.user_row_id
and    p_date BETWEEN ci2.effective_start_date and ci2.effective_end_date
and    c2.user_column_name = 'FACTOR'
order by r.display_sequence;

Line: 563


select /*+ORDERED index(pet PAY_ELEMENT_TYPES_F_UK2) */
      max(decode(pet.element_name,p_orig_ele,pet.element_type_id)) orig_ele_id,
      max(decode(pet.element_name,p_retr_ele,pet.element_type_id)) retr_ele_id
from   pay_element_types_f pet
where  pet.element_name in (p_orig_ele,p_retr_ele)
and    pet.legislation_code = 'FR'
and    pet.business_group_id is null
and    p_date_earned between pet.effective_start_date
                         and pet.effective_end_date;

Line: 577


select /*+ordered use_nl(i i2) */ rr.result_value,
       sum(fnd_number.canonical_to_number(rr2.result_value))
from pay_assignment_actions a
,pay_run_results r
,pay_input_values_f i
,pay_run_result_values rr
,pay_input_values_f i2
,pay_run_result_values rr2
where i.element_type_id = r.element_type_id
and   i.name = 'End Date'
and   i.business_group_id is null
and   i.legislation_code = 'FR'
and   p_date_earned
       between i.effective_start_date and i.effective_end_date
and   i2.element_type_id = r.element_type_id
and   i2.name = 'Processing Sequence'
and   i2.business_group_id is null
and   i2.legislation_code = 'FR'
and   p_date_earned
       between i2.effective_start_date and i2.effective_end_date
and   a.assignment_id = p_assignment_id
and   a.assignment_action_id = r.assignment_action_id
and   r.element_type_id in (p_orig_ele_id,p_retr_ele_id)
and   rr.run_result_id = r.run_result_id
and   i.input_value_id = rr.input_value_id
and   rr2.run_result_id = r.run_result_id
and   i2.input_value_id = rr2.input_value_id
and rr.result_value <= p_range_end_chr
and rr.result_value >= p_range_start_chr
and   r.status in ('P','PA')
group by rr.result_value
having sum(fnd_number.canonical_to_number(rr2.result_value)) >0
order by rr.result_value desc;

Line: 659


select max(start_date)
from per_time_periods
where payroll_id = p_payroll_id
and   p_date >= start_date;

Line: 666


select max(end_date)
from per_time_periods
where payroll_id = p_payroll_id
and   p_date >= end_date;

Line: 760


select 'Y'
from pay_element_entries_f ee
,    pay_element_links_f el
,    pay_element_types_f et
where ee.assignment_id = p_assignment_id
and   ee.element_link_id = el.element_link_id
and   el.element_type_id = et.element_type_id
and   et.element_name in ('FR_OVERTIME_WEEK','FR_OVERTIME_EXCEPTION_WEEK')
and   exists
   (select null
    from pay_element_entry_values_f eev
    ,    pay_input_values_f iv
    where eev.input_value_id = iv.input_value_id
    and   eev.element_entry_id = ee.element_entry_id
    and   iv.name = 'Start Date'
    and   eev.screen_entry_value =
             fnd_date.date_to_canonical(p_week_start_date)
    )
and   exists
   (select null
    from pay_element_entry_values_f eev
    ,    pay_input_values_f iv
    where eev.input_value_id = iv.input_value_id
    and   eev.element_entry_id = ee.element_entry_id
    and   iv.name = 'End Date'
    and   eev.screen_entry_value =
              fnd_date.date_to_canonical(p_week_end_date)
    );

Line: 815


select 'Y'
from per_time_periods o
where o.payroll_id = p_overtime_payroll_id
and   o.start_date = p_week_start_date
and   o.end_date   = p_week_end_date;

Line: 856


select o.start_date,o.end_date
from per_time_periods o
,    per_time_periods p
where o.prd_information2 = p.time_period_id
and p.start_date = p_payroll_start_date
and o.payroll_id = p_overtime_payroll_id
order by o.start_date;

Line: 903


select o.start_date,o.end_date
from per_time_periods o
,    per_time_periods p
where o.prd_information2 = p.time_period_id
and p.payroll_id = p_payroll_id
and p.start_date = p_payroll_start_date
and o.payroll_id = p_overtime_payroll_id
order by o.start_date;

Line: 960


    select ff.formula_id,
         ff.effective_start_date
    from   ff_formulas_f ff
    where  ff.formula_id = p_formula_id
    and    p_effective_date
       between ff.effective_start_date and ff.effective_end_date
    and    ff.business_group_id = p_business_group_id;

Line: 1122


    select ff.formula_id,
         ff.effective_start_date
    from   ff_formulas_f ff
    where  ff.formula_id = p_formula_id
    and    p_effective_date
       between ff.effective_start_date and ff.effective_end_date;

Line: 1346


select /*+ORDERED index(pet PAY_ELEMENT_TYPES_F_UK2) */
      max(decode(pet.element_name,p_orig_ele,pet.element_type_id)) orig_ele_id,
      max(decode(pet.element_name,p_retr_ele,pet.element_type_id)) retr_ele_id
from   pay_element_types_f pet
where  pet.element_name in (p_orig_ele,p_retr_ele)
and    pet.legislation_code = 'FR'
and    pet.business_group_id is null
and    p_date_earned between pet.effective_start_date
                         and pet.effective_end_date;

Line: 1359


select /*+ordered use_nl(i i2) */
       decode(sum(rr2.result_value),null,'N',0,'N','Y')
from pay_assignment_actions a
,pay_run_results r
,pay_input_values_f i
,pay_run_result_values rr
,pay_input_values_f i2
,pay_run_result_values rr2
where i.element_type_id = r.element_type_id
and   i.name = 'End Date'
and   i.business_group_id is null
and   i.legislation_code = 'FR'
and   p_date_earned
       between i.effective_start_date and i.effective_end_date
and   i2.element_type_id = r.element_type_id
and   i2.name = 'Processing Sequence'
and   i2.business_group_id is null
and   i2.legislation_code = 'FR'
and   p_date_earned
       between i2.effective_start_date and i2.effective_end_date
and   a.assignment_id = p_assignment_id
and   a.assignment_action_id = r.assignment_action_id
and   r.element_type_id in (p_orig_ele_id,p_retr_ele_id)
and   rr.run_result_id = r.run_result_id
and   i.input_value_id = rr.input_value_id
and   rr2.run_result_id = r.run_result_id
and   i2.input_value_id = rr2.input_value_id
and   rr.result_value = p_week_end
and   r.status in ('P','PA');

Line: 1428


select /*+ORDERED index(pet PAY_ELEMENT_TYPES_F_UK2) */ pet.element_type_id,
       max(decode(piv.name,p_value_input,piv.input_value_id)) value_iv,
       max(decode(piv.name,p_start_input,piv.input_value_id)) start_iv,
       max(decode(piv.name,p_end_input,  piv.input_value_id)) end_iv
from   pay_element_types_f pet,
       pay_input_values_f  piv
where  pet.element_name = p_element
and   (pet.legislation_code = 'FR' or
       pet.business_group_id = p_business_group_id)
and    p_date_earned between pet.effective_start_date
                         and pet.effective_end_date
and    piv.element_type_id = pet.element_type_id
and    p_date_earned between piv.effective_start_date
                         and piv.effective_end_date
and    piv.name in (p_value_input,p_start_input,p_end_input)
group  by pet.element_type_id;

Line: 1449


select /*+ORDERED */
sum(to_number(rr.result_value))
from pay_assignment_actions a
,    pay_run_results r
,    pay_run_result_values rrsd
,    pay_run_result_values rred
,    pay_run_result_values rr
where a.assignment_id = p_assignment_id
and   a.assignment_action_id = r.assignment_action_id
and   r.element_type_id = p_element_type_id
and   rr.run_result_id = r.run_result_id
and   rr.input_value_id = p_value_iv
and   rrsd.run_result_id = r.run_result_id
and   rrsd.input_value_id = p_start_iv
and   rred.run_result_id = r.run_result_id
and   rred.input_value_id = p_end_iv
and   rred.result_value <= p_end_date_chr
and   rrsd.result_value >= p_start_date_chr
and   r.status in ('P','PA');

Line: 1508


select normal_hours,frequency
from per_all_assignments_f
where assignment_id = p_assignment_id
and   p_effective_date
   between effective_start_date and effective_end_date;