[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select /*+ USE_NL(pap, asg, pee) */
pap.accrual_plan_id accrual_plan_id
,pap.accrual_plan_name accrual_plan_name
,pap.accrual_category accrual_category
,pap.accrual_plan_element_type_id accrual_plan_element_type_id
,pee.effective_start_date effective_start_date
,asg.payroll_id payroll_id
from pay_accrual_plans pap
,pay_element_entries_f pee
,per_all_assignments_f asg
where pap.business_group_id + 0 = p_business_group_id
and asg.assignment_id = p_assignment_id
and p_date_earned between asg.effective_start_date and asg.effective_end_date
and pee.element_link_id in (select element_link_id
from pay_element_links_f
where element_type_id = pap.accrual_plan_element_type_id
)
and pee.assignment_id = p_assignment_id
and p_date_earned between pee.effective_start_date and pee.effective_end_date;
select null
from per_periods_of_service pds
,PER_ALL_ASSIGNMENTS_F ASG
where pds.period_of_service_id = asg.period_of_service_id
and asg.assignment_id = p_assignment_id
and pds.actual_termination_date
between asg.effective_start_date and asg.effective_end_Date
and pds.actual_termination_date is not null;
g_fr_payslip_info.DELETE;
select null
from per_periods_of_service pds
,PER_ALL_ASSIGNMENTS_F ASG
where pds.period_of_service_id = asg.period_of_service_id
and asg.assignment_id = p_assignment_id
and pds.actual_termination_date
between asg.effective_start_date and asg.effective_end_Date
and pds.actual_termination_date is not null
;
select max(paa.action_sequence)
from pay_assignment_actions paa
,pay_payroll_actions ppa
where ppa.payroll_action_id = paa.payroll_action_id
and paa.assignment_id = p_assignment_id
and p_paid_days_to >= ppa.effective_date
and ppa.action_type in ('Q','R');
select sum(prrv_days.result_value)
from pay_run_result_values prrv_days
,pay_run_result_values prrv_accrual
,pay_run_result_values prrv_plan
,pay_run_results prr
,pay_assignment_actions paa
where prrv_days.run_result_id = prr.run_result_id
and prrv_plan.run_result_id = prr.run_result_id
and prrv_accrual.run_result_id = prr.run_result_id
and prrv_days.input_value_id = l_fr_pay_info.pay_total_days_input_ID
and prrv_accrual.input_value_id = l_fr_pay_info.pay_accrual_date_input_ID
and prrv_plan.input_value_id = l_fr_pay_info.pay_plan_input_ID
and prr.assignment_action_id = paa.assignment_action_id
and paa.assignment_id = p_assignment_id
and prr.element_type_id = l_fr_pay_info.pay_element_id
and prr.status in ('P','PA')
and prrv_plan.result_value = l_accrual_plan_id
and paa.action_sequence < l_action_sequence
/* restrict to just this accrual year, otherwise all taken across all plan years will be added in */
and prrv_accrual.result_value between fnd_date.date_to_canonical(l_fr_plan_info.accrual_year_start)
and fnd_date.date_to_canonical(l_fr_plan_info.accrual_year_end);
select sum(prrv_days.result_value)
from pay_run_result_values prrv_days
,pay_run_result_values prrv_accrual
,pay_run_result_values prrv_plan
,pay_run_results prr
,pay_assignment_actions paa
where prrv_days.run_result_id = prr.run_result_id
and prrv_plan.run_result_id = prr.run_result_id
and prrv_accrual.run_result_id = prr.run_result_id
and prrv_days.input_value_id = l_fr_pay_r_info.pay_total_days_input_ID
and prrv_accrual.input_value_id = l_fr_pay_r_info.pay_accrual_date_input_ID
and prrv_plan.input_value_id = l_fr_pay_r_info.pay_plan_input_ID
and prr.assignment_action_id = paa.assignment_action_id
and paa.assignment_id = p_assignment_id
and prr.element_type_id = l_fr_pay_r_info.pay_element_id
and prr.status in ('P','PA')
and prrv_plan.result_value = l_accrual_plan_id
and paa.action_sequence < l_action_sequence
/* restrict to just this accrual year, otherwise all taken across all plan years will be added in */
and prrv_accrual.result_value between fnd_date.date_to_canonical(l_fr_plan_info.accrual_year_start)
and fnd_date.date_to_canonical(l_fr_plan_info.accrual_year_end);
select sum(prrv_days.result_value)
from pay_run_result_values prrv_days
,pay_run_result_values prrv_accrual
,pay_run_result_values prrv_plan
,pay_run_results prr
,pay_assignment_actions paa
where prrv_days.run_result_id = prr.run_result_id
and prrv_plan.run_result_id = prr.run_result_id
and prrv_accrual.run_result_id = prr.run_result_id
and prrv_days.input_value_id = l_fr_pay_info.pay_total_days_input_ID
and prrv_accrual.input_value_id = l_fr_pay_info.pay_accrual_date_input_ID
and prrv_plan.input_value_id = l_fr_pay_info.pay_plan_input_ID
and prrv_plan.result_value = l_accrual_plan_id
and prr.status in ('P','PA')
and paa.action_sequence >= l_action_sequence
and prr.assignment_action_id = paa.assignment_action_id
and paa.assignment_id = p_assignment_id
and prr.element_type_id = l_fr_pay_info.pay_element_id;
select sum(prrv_days.result_value)
from pay_run_result_values prrv_days
,pay_run_result_values prrv_accrual
,pay_run_result_values prrv_plan
,pay_run_results prr
,pay_assignment_actions paa
where prrv_days.run_result_id = prr.run_result_id
and prrv_plan.run_result_id = prr.run_result_id
and prrv_accrual.run_result_id = prr.run_result_id
and prrv_days.input_value_id = l_fr_pay_r_info.pay_total_days_input_ID
and prrv_accrual.input_value_id = l_fr_pay_r_info.pay_accrual_date_input_ID
and prrv_plan.input_value_id = l_fr_pay_r_info.pay_plan_input_ID
and prrv_plan.result_value = l_accrual_plan_id
and paa.action_sequence >= l_action_sequence
and prr.assignment_action_id = paa.assignment_action_id
and paa.assignment_id = p_assignment_id
and prr.status in ('P','PA')
and prr.element_type_id = l_fr_pay_r_info.pay_element_id;
select min(paa.action_sequence)
from pay_assignment_actions paa
,pay_payroll_actions ppa
where ppa.payroll_action_id = paa.payroll_action_id
and paa.assignment_id = p_assignment_id
and p_paid_days_to <= ppa.effective_date
and ppa.action_type in ('Q','R');
select max(paa.action_sequence)
from pay_assignment_actions paa
,pay_payroll_actions ppa
where ppa.payroll_action_id = paa.payroll_action_id
and paa.assignment_id = p_assignment_id
and p_paid_days_to >= ppa.effective_date
and ppa.action_type in ('Q','R');
select sum(prrv_days.result_value)
from pay_run_result_values prrv_days
,pay_run_result_values prrv_accrual
,pay_run_result_values prrv_plan
,pay_run_results prr
,pay_assignment_actions paa
where prrv_days.run_result_id = prr.run_result_id
and prrv_plan.run_result_id = prr.run_result_id
and prrv_accrual.run_result_id = prr.run_result_id
and prrv_days.input_value_id = l_fr_pay_info.pay_total_days_input_ID
and prrv_accrual.input_value_id = l_fr_pay_info.pay_accrual_date_input_ID
and prrv_plan.input_value_id = l_fr_pay_info.pay_plan_input_ID
and prr.assignment_action_id = paa.assignment_action_id
and paa.assignment_id = p_assignment_id
and prrv_plan.result_value = l_accrual_plan_id
and prr.element_type_id = l_fr_pay_info.pay_element_id
and prr.status in ('P','PA')
and paa.action_sequence <= l_action_sequence
and prrv_accrual.result_value between fnd_date.date_to_canonical(l_fr_plan_info.accrual_year_start)
and fnd_date.date_to_canonical(l_fr_plan_info.accrual_year_end);
select sum(prrv_days.result_value)
from pay_run_result_values prrv_days
,pay_run_result_values prrv_accrual
,pay_run_result_values prrv_plan
,pay_run_results prr
,pay_assignment_actions paa
where prrv_days.run_result_id = prr.run_result_id
and prrv_plan.run_result_id = prr.run_result_id
and prrv_accrual.run_result_id = prr.run_result_id
and prrv_days.input_value_id = l_fr_pay_r_info.pay_total_days_input_ID
and prrv_accrual.input_value_id = l_fr_pay_r_info.pay_accrual_date_input_ID
and prrv_plan.input_value_id = l_fr_pay_r_info.pay_plan_input_ID
and prr.assignment_action_id = paa.assignment_action_id
and paa.assignment_id = p_assignment_id
and prrv_plan.result_value = l_accrual_plan_id
and prr.element_type_id = l_fr_pay_r_info.pay_element_id
and prr.status in ('P','PA')
and paa.action_sequence <= l_action_sequence
and prrv_accrual.result_value between fnd_date.date_to_canonical(l_fr_plan_info.accrual_year_start)
and fnd_date.date_to_canonical(l_fr_plan_info.accrual_year_end);
select sum(prrv_days.result_value)
from pay_run_result_values prrv_days
,pay_run_result_values prrv_accrual
,pay_run_results prr
,pay_assignment_actions paa
,pay_payroll_actions ppa
where prrv_days.run_result_id = prr.run_result_id
and prrv_accrual.run_result_id = prr.run_result_id
and prrv_days.input_value_id = l_fr_plan_info.term_days_iv_ID
and prrv_accrual.input_value_id = l_fr_plan_info.term_accrual_date_iv_ID
and prr.assignment_action_id = paa.assignment_action_id
and paa.assignment_id = p_assignment_id
and prr.element_type_id = l_fr_plan_info.term_element_id
and prr.status in ('P','PA')
and ppa.payroll_action_id = paa.payroll_action_id
-- termination payments always exist over all time for an accrual year
-- and p_paid_days_to >= ppa.effective_date
-- restrict to just this accrual year,
-- otherwise all taken across all plan years will be added in
-- this cursor is used by the accruals calculation
and prrv_accrual.result_value between
fnd_date.date_to_canonical(l_fr_plan_info.accrual_year_start)
and fnd_date.date_to_canonical(l_fr_plan_info.accrual_year_end);
select sum(fnd_number.canonical_to_number(pevm.screen_entry_value))
,sum(fnd_number.canonical_to_number(pevp.screen_entry_value))
,sum(fnd_number.canonical_to_number(pevc.screen_entry_value))
,sum(fnd_number.canonical_to_number(pevs.screen_entry_value))
,sum(fnd_number.canonical_to_number(pevy.screen_entry_value))
from pay_element_entry_values_f pevm
,pay_element_entry_values_f pevp
,pay_element_entry_values_f pevc
,pay_element_entry_values_f pevs
,pay_element_entry_values_f pevy
,pay_element_entry_values_f pevdate
,pay_element_entries_f pee
where pevm.input_value_id = p_type_m_iv_id
and pevp.input_value_id = p_type_p_iv_id
and pevs.input_value_id = p_type_s_iv_id
and pevc.input_value_id = p_type_c_iv_id
and pevy.input_value_id = p_type_y_iv_id
and pevdate.input_value_id = p_type_accrual_date_iv_id
and pee.element_entry_id = pevm.element_entry_id
and pee.element_entry_id = pevp.element_entry_id
and pee.element_entry_id = pevs.element_entry_id
and pee.element_entry_id = pevc.element_entry_id
and pee.element_entry_id = pevy.element_entry_id
and pee.element_entry_id = pevdate.element_entry_id
and pevdate.screen_entry_value between fnd_date.date_to_canonical(l_fr_plan_info.accrual_year_start)
and fnd_date.date_to_canonical(l_fr_plan_info.accrual_year_end)
and pee.assignment_id = p_assignment_id ;
select sum(pevm.screen_entry_value)
from pay_element_entry_values_f pevm
,pay_element_entry_values_f pevdate
,pay_element_entries_f pee
where pevm.input_value_id = p_type_m_iv_id
and pevdate.input_value_id = p_type_accrual_date_iv_id
and pee.element_entry_id = pevm.element_entry_id
and pee.element_entry_id = pevdate.element_entry_id
and pevdate.screen_entry_value = fnd_date.date_to_canonical(l_fr_plan_info.accrual_year_end)
and pee.assignment_id = p_assignment_id
and pee.effective_start_date between p_legal_period_end and
add_months(l_fr_plan_info.accrual_year_end,12);
select sum(prrv_days.result_value), sum(prrv_pay.result_value)
from pay_run_result_values prrv_days
,pay_run_result_values prrv_pay
,pay_run_result_values prrv_accrual
,pay_run_result_values prrv_flag
,pay_run_result_values prrv_plan
,pay_run_results prr
,pay_assignment_actions paa
where prrv_days.run_result_id = prr.run_result_id
and prrv_pay.run_result_id = prr.run_result_id
and prrv_plan.run_result_id = prr.run_result_id
and prrv_flag.run_result_id = prr.run_result_id
and prrv_accrual.run_result_id = prr.run_result_id
and prrv_days.input_value_id = l_fr_pay_info.pay_total_days_input_ID
and prrv_pay.input_value_id = l_fr_pay_info.pay_payment_input_ID
and prrv_accrual.input_value_id = l_fr_pay_info.pay_accrual_date_input_ID
and prrv_plan.input_value_id = l_fr_pay_info.pay_plan_input_ID
and prrv_flag.input_value_id = l_fr_pay_info.pay_flag_input_ID
and prr.assignment_action_id = paa.assignment_action_id
and paa.assignment_id = p_assignment_id
and prr.element_type_id = l_fr_pay_info.pay_element_id
and prr.status in ('P','PA')
and prrv_accrual.result_value
between fnd_date.date_to_canonical(l_accrued_start_date)
and fnd_date.date_to_canonical(l_accrued_end_date);
select sum(prrv_days.result_value), sum(prrv_pay.result_value)
from pay_run_result_values prrv_days
,pay_run_result_values prrv_pay
,pay_run_result_values prrv_accrual
,pay_run_result_values prrv_flag
,pay_run_result_values prrv_plan
,pay_run_results prr
,pay_assignment_actions paa
where prrv_days.run_result_id = prr.run_result_id
and prrv_pay.run_result_id = prr.run_result_id
and prrv_plan.run_result_id = prr.run_result_id
and prrv_flag.run_result_id = prr.run_result_id
and prrv_accrual.run_result_id = prr.run_result_id
and prrv_days.input_value_id = l_fr_pay_r_info.pay_total_days_input_ID
and prrv_pay.input_value_id = l_fr_pay_r_info.pay_payment_input_ID
and prrv_accrual.input_value_id = l_fr_pay_r_info.pay_accrual_date_input_ID
and prrv_plan.input_value_id = l_fr_pay_r_info.pay_plan_input_ID
and prrv_flag.input_value_id = l_fr_pay_r_info.pay_flag_input_ID
and prr.assignment_action_id = paa.assignment_action_id
and paa.assignment_id = p_assignment_id
and prr.element_type_id = l_fr_pay_r_info.pay_element_id
and prr.status in ('P','PA')
and prrv_accrual.result_value
between fnd_date.date_to_canonical(l_accrued_start_date)
and fnd_date.date_to_canonical(l_accrued_end_date);
select sum(prrv_pay.result_value)
from pay_run_result_values prrv_pay
,pay_run_result_values prrv_accrual
,pay_run_result_values prrv_plan
,pay_run_results prr
,pay_assignment_actions paa
where prrv_pay.run_result_id = prr.run_result_id
and prrv_plan.run_result_id = prr.run_result_id
and prrv_accrual.run_result_id = prr.run_result_id
and prrv_pay.input_value_id = l_fr_pay_info.pay_reg_payment_input_ID
and prrv_accrual.input_value_id = l_fr_pay_info.pay_reg_date_input_ID
and prrv_plan.input_value_id = l_fr_pay_info.pay_reg_plan_input_ID
and prr.assignment_action_id = paa.assignment_action_id
and paa.assignment_id = p_assignment_id
and prr.element_type_id = l_fr_pay_info.pay_reg_element_id
and prr.status in ('P','PA')
and prrv_accrual.result_value
between fnd_date.date_to_canonical(l_accrued_start_date)
and fnd_date.date_to_canonical(l_accrued_end_date);
select sum(prrv_pay.result_value)
from pay_run_result_values prrv_pay
,pay_run_result_values prrv_accrual
,pay_run_result_values prrv_plan
,pay_run_results prr
,pay_assignment_actions paa
where prrv_pay.run_result_id = prr.run_result_id
and prrv_plan.run_result_id = prr.run_result_id
and prrv_accrual.run_result_id = prr.run_result_id
and prrv_pay.input_value_id = l_fr_pay_r_info.pay_reg_payment_input_ID
and prrv_accrual.input_value_id = l_fr_pay_r_info.pay_reg_date_input_ID
and prrv_plan.input_value_id = l_fr_pay_r_info.pay_reg_plan_input_ID
and prr.assignment_action_id = paa.assignment_action_id
and paa.assignment_id = p_assignment_id
and prr.element_type_id = l_fr_pay_r_info.pay_reg_element_id
and prr.status in ('P','PA')
and prrv_accrual.result_value
between fnd_date.date_to_canonical(l_accrued_start_date)
and fnd_date.date_to_canonical(l_accrued_end_date);
select sum(nvl(prrv_pay.result_value,0)) payments
from pay_run_result_values prrv_pay
,pay_run_result_values prrv_accrual
,pay_run_result_values prrv_plan
,pay_element_types_f petf
,pay_input_values_f pivf_pay
,pay_input_values_f pivf_accrual
,pay_input_values_f pivf_plan
,pay_run_results prr
,pay_assignment_actions paa
where prrv_pay.run_result_id = prr.run_result_id
and prrv_plan.run_result_id = prr.run_result_id
and prrv_accrual.run_result_id = prr.run_result_id
and prrv_pay.input_value_id = pivf_pay.input_value_id
and prrv_accrual.input_value_id = pivf_accrual.input_value_id
and prrv_plan.input_value_id = pivf_plan.input_value_id
--
and petf.element_name = 'FR_TERMINATION_REGULARIZE'
and petf.legislation_code = 'FR'
and pivf_pay.element_type_id = petf.element_type_id
and prr.status in ('P','PA')
and pivf_pay.name = 'Pay Value'
and pivf_accrual.element_type_id= petf.element_type_id
and pivf_plan.element_type_id = petf.element_type_id
and pivf_accrual.name = 'Accrual Date'
and pivf_plan.name = 'Accrual Plan ID'
--
and prr.assignment_action_id = paa.assignment_action_id
and prrv_plan.result_value = p_accrual_plan_id
and paa.action_sequence <= nvl(l_action_sequence, paa.action_sequence)
and paa.assignment_id = p_assignment_id
and prr.element_type_id = petf.element_type_id
and prrv_accrual.result_value
between fnd_date.date_to_canonical(l_accrued_start_date)
and fnd_date.date_to_canonical(l_accrued_end_date);
Select nvl(INFORMATION30, 'N') reg_flg
from pay_accrual_plans
where ACCRUAL_PLAN_ID = p_accrual_plan_id
and INFORMATION_CATEGORY in ('FR_FR_MAIN_HOLIDAY', 'FR_FR_RTT_HOLIDAY', 'FR_FR_ADDITIONAL_HOLIDAY');
select effective_date
from fnd_sessions
where session_id = USERENV('SESSIONID');
select sum(nvl(prrv_pay.result_value,0)) payments
from pay_run_result_values prrv_pay
,pay_run_result_values prrv_accrual
,pay_run_result_values prrv_plan
,pay_element_types_f petf
,pay_input_values_f pivf_pay
,pay_input_values_f pivf_accrual
,pay_input_values_f pivf_plan
,pay_run_results prr
,pay_assignment_actions paa
where prrv_pay.run_result_id = prr.run_result_id
and prrv_plan.run_result_id = prr.run_result_id
and prrv_accrual.run_result_id = prr.run_result_id
and prrv_pay.input_value_id = pivf_pay.input_value_id
and prrv_accrual.input_value_id = pivf_accrual.input_value_id
and prrv_plan.input_value_id = pivf_plan.input_value_id
--
and petf.element_name = 'FR_TERMINATION_REGULARIZE'
and petf.legislation_code = 'FR'
and pivf_pay.element_type_id = petf.element_type_id
and pivf_pay.name = 'Pay Value'
and pivf_accrual.element_type_id= petf.element_type_id
and pivf_plan.element_type_id = petf.element_type_id
and pivf_accrual.name = 'Accrual Date'
and pivf_plan.name = 'Accrual Plan ID'
--
and prr.assignment_action_id = paa.assignment_action_id
and prrv_plan.result_value = p_accrual_plan_id
and paa.action_sequence <= nvl(l_action_sequence, paa.action_sequence)
and paa.assignment_id = p_assignment_id
and prr.element_type_id = petf.element_type_id
and prr.status in ('P','PA')
and prrv_accrual.result_value between fnd_date.date_to_canonical(l_fr_plan_info.accrual_year_start)
and fnd_date.date_to_canonical(l_fr_plan_info.accrual_year_end);
select sum(prrv_pay.result_value)
from pay_run_result_values prrv_pay
,pay_run_result_values prrv_accrual
,pay_run_result_values prrv_plan
,pay_run_results prr
,pay_assignment_actions paa
where prrv_pay.run_result_id = prr.run_result_id
and prrv_plan.run_result_id = prr.run_result_id
and prrv_accrual.run_result_id = prr.run_result_id
and prrv_pay.input_value_id = l_fr_pay_info.pay_reg_payment_input_ID
and prrv_accrual.input_value_id = l_fr_pay_info.pay_reg_date_input_ID
and prrv_plan.input_value_id = l_fr_pay_info.pay_reg_plan_input_ID
and prr.assignment_action_id = paa.assignment_action_id
and prrv_plan.result_value = p_accrual_plan_id
and paa.action_sequence <= nvl(l_action_sequence, paa.action_sequence)
and paa.assignment_id = p_assignment_id
and prr.element_type_id = l_fr_pay_info.pay_reg_element_id
and prr.status in ('P','PA')
and prrv_accrual.result_value between fnd_date.date_to_canonical(l_fr_plan_info.accrual_year_start)
and fnd_date.date_to_canonical(l_fr_plan_info.accrual_year_end);
select sum(prrv_days.result_value), sum(prrv_pay.result_value)
from pay_run_result_values prrv_days
,pay_run_result_values prrv_pay
,pay_run_result_values prrv_accrual
,pay_run_result_values prrv_plan
,pay_run_results prr
,pay_assignment_actions paa
where prrv_days.run_result_id = prr.run_result_id
and prrv_pay.run_result_id = prr.run_result_id
and prrv_plan.run_result_id = prr.run_result_id
and prrv_accrual.run_result_id = prr.run_result_id
and prrv_days.input_value_id = l_fr_pay_info.pay_total_days_input_ID
and prrv_pay.input_value_id = l_fr_pay_info.pay_payment_input_ID
and prrv_accrual.input_value_id = l_fr_pay_info.pay_accrual_date_input_ID
and prrv_plan.input_value_id = l_fr_pay_info.pay_plan_input_ID
and prr.assignment_action_id = paa.assignment_action_id
and paa.assignment_id = p_assignment_id
and prr.element_type_id = l_fr_pay_info.pay_element_id
and prr.status in ('P','PA')
and prrv_plan.result_value = p_accrual_plan_id
and paa.action_sequence <= l_action_sequence
and prrv_accrual.result_value between fnd_date.date_to_canonical(l_fr_plan_info.accrual_year_start)
and fnd_date.date_to_canonical(l_fr_plan_info.accrual_year_end);
select sum(prrv_days.result_value), sum(prrv_pay.result_value)
from pay_run_result_values prrv_days
,pay_run_result_values prrv_pay
,pay_run_result_values prrv_accrual
,pay_run_result_values prrv_plan
,pay_run_results prr
,pay_assignment_actions paa
where prrv_days.run_result_id = prr.run_result_id
and prrv_pay.run_result_id = prr.run_result_id
and prrv_plan.run_result_id = prr.run_result_id
and prrv_accrual.run_result_id = prr.run_result_id
and prrv_days.input_value_id = l_fr_pay_r_info.pay_total_days_input_ID
and prrv_pay.input_value_id = l_fr_pay_r_info.pay_payment_input_ID
and prrv_accrual.input_value_id = l_fr_pay_r_info.pay_accrual_date_input_ID
and prrv_plan.input_value_id = l_fr_pay_r_info.pay_plan_input_ID
and prr.assignment_action_id = paa.assignment_action_id
and paa.assignment_id = p_assignment_id
and prr.element_type_id = l_fr_pay_r_info.pay_element_id
and prr.status in ('P','PA')
and prrv_plan.result_value = p_accrual_plan_id
and paa.action_sequence <= l_action_sequence
and prrv_accrual.result_value between fnd_date.date_to_canonical(l_fr_plan_info.accrual_year_start)
and fnd_date.date_to_canonical(l_fr_plan_info.accrual_year_end);
select max(paa.action_sequence)
from pay_assignment_actions paa
,pay_payroll_Actions ppa
where ppa.payroll_action_id = paa.payroll_action_id
and paa.assignment_id = p_assignment_id;
select piv.input_value_id, pet.element_type_id
from pay_element_types_f pet
,pay_input_values_f piv
where piv.element_Type_id = pet.element_type_id
and pet.legislation_code = 'FR'
-- and piv.legislation_code = 'FR'
and pet.element_name = p_element_name
and piv.name = p_input_name;
SELECT min(pee.effective_start_date) start_date
from pay_element_entries_f pee
where pee.element_entry_id = p_element_entry_id;
select piv.input_value_id, pet.element_type_id
from pay_element_types_f pet
,pay_input_values_f piv
where piv.element_Type_id = pet.element_type_id
and pet.legislation_code = 'FR'
-- and piv.legislation_code = 'FR'
and pet.element_name = p_element_name
and piv.name = p_input_name;
select fnd_number.canonical_to_number(substr(max(lpad(paa.action_sequence,15,'0')||
paa.assignment_action_id),16))
from pay_assignment_actions paa,
pay_payroll_actions ppa
where paa.assignment_id = l_assignment_id
and ppa.payroll_action_id = paa.payroll_action_id
and ppa.effective_date between l_fr_plan_info.accrual_year_start and l_fr_plan_info.accrual_year_end
and ppa.action_type in ('R', 'Q', 'I', 'V', 'B');
select balance_type_id
from pay_balance_types
where balance_name = 'FR_SUBJECT_TO_EARNINGS_DEDUCTION'
and legislation_code = 'FR';
select accrual_formula_id, co_formula_id
from PAY_ACCRUAL_PLANS
where ACCRUAL_PLAN_ID = p_plan_id
and ACCRUAL_CATEGORY in ('FR_MAIN_HOLIDAY', 'FR_RTT_HOLIDAY', 'FR_ADDITIONAL_HOLIDAY');
Select nvl(sum(fnd_number.canonical_to_number(TARGET.result_value) * FEED.scale),0)
from
pay_run_result_values TARGET
,pay_balance_feeds_f FEED
,pay_run_results RR
,pay_assignment_actions ASSACT
,pay_assignment_actions BAL_ASSACT
,pay_payroll_actions PACT
,pay_payroll_actions BACT
,per_time_periods PTP
where BAL_ASSACT.assignment_action_id = l_latest_aa /*B1 */
and BAL_ASSACT.payroll_action_id = BACT.payroll_action_id
and FEED.balance_type_id = l_balance_type_id /*U1*/
+ decode(TARGET.input_value_id, null, 0, 0)
and FEED.input_value_id = TARGET.input_value_id
and nvl(TARGET.result_value, '0') <> '0'
and TARGET.run_result_id = RR.run_result_id
and RR.assignment_action_id = ASSACT.assignment_action_id
and ASSACT.payroll_action_id = PACT.payroll_action_id
and PACT.effective_date between
FEED.effective_start_date and FEED.effective_end_date
and RR.status in ('P','PA')
and ASSACT.action_sequence <= BAL_ASSACT.action_sequence
and ASSACT.assignment_id = BAL_ASSACT.assignment_id
and PTP.time_period_id = BACT.time_period_id
/* Year To Date */
and PACT.effective_date > (
select nvl(min(PTP1.end_date),l_fr_plan_info.accrual_year_start)
from PAY_ACCRUAL_PLANS PAP1
,per_time_periods PTP1
where PAP1.ACCRUAL_PLAN_ID = l_fr_plan_info.accrual_plan_id
and pap1.information1 is not null
AND PTP1.payroll_id = BACT.payroll_id
and l_fr_plan_info.accrual_year_start -1
between PTP1.start_date and PTP1.end_date);
select pee.assignment_id assignment_id
from pay_element_entries_f pee
where pee.element_type_id = l_fr_plan_info.accrual_plan_element_id
and pee.assignment_id = nvl(c_assignment_id,pee.assignment_id)
and l_calculation_date between pee.effective_start_date
and pee.effective_end_date;
select ptp.start_date
,ptp.end_date
from per_time_periods ptp
where ptp.payroll_id = p_payroll_id
and p_effective_date between ptp.start_date and ptp.end_date;
select payroll_id, assignment_number
from per_all_assignments_f
where assignment_id = p_assignment_id
and (p_effective_date between effective_start_date and effective_end_date);
select max(effective_end_date)
from per_all_assignments_f
where assignment_id = p_assignment_id;
Select ppos.date_start
From per_all_assignments_f pasg,
per_periods_of_service ppos
Where ppos.period_of_service_id = pasg.period_of_service_id
and pasg.assignment_id = c_assignment_id
and c_accrual_date between pasg.effective_start_date
and pasg.effective_end_date;
hr_entry_api.insert_element_entry(
p_effective_start_date => l_pay_period_start_date,
p_effective_end_date => l_pay_period_end_date,
p_element_entry_id => l_new_ee_id,
p_assignment_id => l_asg.assignment_id,
p_element_link_id => l_type_link_id,
p_creator_type => 'F',
p_entry_type => 'E',
p_num_entry_values => l_index,
p_input_value_id_tbl => inp_value_id_tbl,
p_entry_value_tbl => scr_valuetbl);
select piv1.input_value_id acp_plan_id
from pay_input_values_f piv1
where piv1.element_type_id = p_element_type_id
and piv1.display_sequence = 10;
select piv1.input_value_id reference_salary
,piv2.input_value_id reference_days
from pay_input_values_f piv1
,pay_input_values_f piv2
,pay_input_values_f piv
where piv1.element_type_id = piv2.element_type_id
and piv1.element_type_id = piv.element_type_id
and piv.input_value_id = p_ent_accrual_date_iv
and piv1.display_sequence = 80
and piv2.display_sequence = 90;
select nvl(pap.information1,6) accrual_start_month
,pap.information2 entitlement_offset
,pap.information3 entitlement_duration
,pap.information4 working_days
,pap.information5 protected_days
,pap.information6 accounting_method
,pap.information7 main_holiday_acc_plan_id
,pap.information8 ent_m_iv_id
,pap.information9 ent_p_iv_id
,pap.information10 ent_c_iv_id
,pap.information11 ent_s_iv_id
,pap.information12 ent_y_iv_id
,pap.information13 obs_m_iv_id
,pap.information14 obs_p_iv_id
,pap.information15 obs_c_iv_id
,pap.information16 obs_s_iv_id
,pap.information17 obs_y_iv_id
,pap.information18 adj_m_iv_id
,pap.information19 adj_p_iv_id
,pap.information20 adj_c_iv_id
,pap.information21 adj_s_iv_id
,pap.information22 adj_y_iv_id
,pap.information23 ent_accrual_date_iv_id
,pap.information24 obs_accrual_date_iv_id
,pap.information25 adj_accrual_date_iv_id
,pap.information26 working_days_iv_id
,pap.information27 protected_days_iv_id
-- lines added for termination processing
,pap.information28 term_days_iv_id
,pap.information29 term_accrual_date_iv_id
--
,pap.accrual_plan_element_type_id accrual_plan_element_id
,piv.element_type_id holiday_element_id
,pivE.element_type_id ENT_element_id
,pivO.element_type_id OBS_element_id
,pivA.element_type_id ADJ_element_id
-- added for termination processing
,pivT.element_type_id TERM_element_id
--
,pap.business_group_id business_Group_id
from pay_accrual_plans pap
,pay_input_values_f piv
,pay_input_values_f pivE
,pay_input_values_f pivO
,pay_input_values_f pivA
-- added for termination processing
,pay_input_values_f pivT
where pap.accrual_plan_id = p_accrual_plan_id
and piv.input_value_id = pap.pto_input_value_id
and pivE.input_value_id = pap.information8
and pivO.input_value_id = pap.information13
and pivA.input_value_id = pap.information18
-- Added for termination processing
and pivT.input_value_id(+) = pap.information28;
select pap.information1 accrual_start_month
,pap.information2 entitlement_offset
,pap.information3 entitlement_duration
,pap.information4 working_days
,pap.information5 protected_days
,pap.information6 accounting_method
,pap.information7 main_holiday_acc_plan_id
,pap.information8 ent_m_iv_id
,pap.information9 ent_p_iv_id
,pap.information10 ent_c_iv_id
,pap.information11 ent_s_iv_id
,pap.information12 ent_y_iv_id
,pap.information13 obs_m_iv_id
,pap.information14 obs_p_iv_id
,pap.information15 obs_c_iv_id
,pap.information16 obs_s_iv_id
,pap.information17 obs_y_iv_id
,pap.information18 adj_m_iv_id
,pap.information19 adj_p_iv_id
,pap.information20 adj_c_iv_id
,pap.information21 adj_s_iv_id
,pap.information22 adj_y_iv_id
,pap.information23 ent_accrual_date_iv_id
,pap.information24 obs_accrual_date_iv_id
,pap.information25 adj_accrual_date_iv_id
,pap.accrual_plan_element_type_id accrual_plan_element_id
,pap.accrual_plan_id accrual_plan_id
,pap.information26 working_days_iv_id
,pap.information27 protected_days_iv_id
,pap.information28 term_days_iv_id
,pap.information29 term_accrual_date_iv_id
,pivT.element_type_id term_element_id
,piv.element_Type_id holiday_element_id
,pivE.element_type_id ENT_element_id
,pivO.element_type_id OBS_element_id
,pivA.element_type_id ADJ_element_id
,pap.business_group_id business_Group_id
from pay_accrual_plans pap
,per_absence_attendances paa
,per_absence_attendance_types pat
,pay_element_entries_f pee
,pay_input_values_f piv
,pay_input_values_f pivE
,pay_input_values_f pivO
,pay_input_values_f pivA
,pay_input_values_f pivT
where paa.absence_attendance_type_id = pat.absence_attendance_type_id
and pivE.input_value_id = pap.information8
and pivO.input_value_id = pap.information13
and pivA.input_value_id = pap.information18
and pat.input_value_id = pap.pto_input_value_id
and paa.absence_Attendance_id = pee.creator_id
and pee.creator_type = 'A'
and piv.input_value_id = pap.pto_input_value_id
and pivT.input_value_id(+) = pap.information28
and pee.element_entry_id = c_element_entry_id;
select piv.input_value_id input_value_id
from pay_input_values_f piv
where piv.element_type_id = p_element_type_id
and piv.display_sequence = 40;
select sum(pevm.screen_entry_value)
,sum(pevp.screen_entry_value)
from pay_element_entry_values_f pevm
,pay_element_entry_values_f pevp
,pay_element_entry_values_f pevdate
,pay_element_entries_f pee
where pevm.input_value_id = c_type_m_iv_id
and pevp.input_value_id = c_type_p_iv_id
and pevdate.input_value_id = c_type_accrual_date_iv_id
and pee.element_entry_id = pevm.element_entry_id
and pee.element_entry_id = pevp.element_entry_id
and pee.element_entry_id = pevdate.element_entry_id
and pevdate.screen_entry_value between fnd_date.date_to_canonical(c_start_date) and fnd_date.date_to_canonical(c_end_date)
and pee.assignment_id = p_assignment_id ;
select sum(to_number(nvl(paa.abs_information2,'0'))) /* main days booked */
,sum(to_number(nvl(paa.abs_information3,'0'))) /* protected days booked */
,sum(to_number(nvl(paa.abs_information4,'0'))) /* conventional days booked */
,sum(to_number(nvl(paa.abs_information5,'0'))) /* Seniority days booked */
,sum(to_number(nvl(paa.abs_information6,'0'))) /* Young mothers days booked */
from per_absence_attendances paa
,pay_element_entries_f pee
where pee.element_link_id in (select element_link_id
from pay_element_links_f
where element_type_id = p_holiday_element_id
)
and pee.creator_type = 'A'
and paa.absence_Attendance_id = pee.creator_id
and paa.abs_information1 between fnd_date.date_to_canonical(p_accrual_year_start)
and fnd_date.date_to_canonical(p_accrual_year_end)
and pee.assignment_id = p_assignment_id;
select count(distinct(d.person_id))
from per_all_people_f d
,per_all_people_f p
,per_contact_relationships pcr
,per_all_assignments_f asg
where asg.assignment_id = P_assignment_id
and asg.business_group_id = p_business_Group_id
and asg.person_id = p.person_id
and trunc(sysdate) between asg.effective_start_date and asg.effective_end_date
and trunc(sysdate) between p.effective_start_date and p.effective_end_date
and trunc(sysdate) between d.effective_start_date and d.effective_end_date
and asg.person_id = pcr.person_id
and pcr.contact_person_id = d.person_id
and add_months(d.date_of_birth, 12 * 16) > P_child_age_date
and add_months(p.date_of_birth, 12 * 21) > add_months(P_child_age_date, -12)
and p.current_employee_flag = 'Y'
and p.sex = 'F'
and nvl(pcr.date_end, P_child_age_date) >= P_child_age_date
AND EXISTS
( SELECT pst.INFORMATION3 from per_shared_types pst
WHERE pcr.contact_type = pst.system_type_cd
AND pst.lookup_type = 'CONTACT'
AND pst.INFORMATION3 = 'Y'
AND ( pst.business_group_id = asg.business_group_id
OR pst.business_group_id IS NULL)
);
select peevM.effective_start_Date start_date
,peevM.effective_end_date end_date
,peevM.screen_entry_value main_rate
,peevP.screen_entry_value protected_rate
from
pay_element_entries_f pee
,pay_element_entry_values_f peevM
,pay_element_entry_values_f peevP
where
pee.element_entry_id = peevM.element_entry_id
and pee.element_entry_id = peevP.element_entry_id(+)
and pee.element_link_id in (select element_link_id
from pay_element_links_f
where element_type_id = l_accrual_plan_element_id
)
and peevM.input_value_id = l_working_days_iv_id
and peevP.input_value_id(+) = l_protected_days_iv_id
and pee.effective_start_Date = peevM.effective_start_Date
and pee.effective_end_Date = peevM.effective_end_Date
and pee.effective_start_Date = peevP.effective_start_Date(+)
and pee.effective_end_Date = peevP.effective_end_Date(+)
and pee.assignment_id = p_assignment_id
Order by
peevM.effective_start_Date;
g_rate_tab.Delete;
select max(pee.effective_end_Date)
from pay_element_entry_values_f pevd
,pay_element_entries_f pee
where pevd.input_value_id = p_ent_accrual_date_iv_id
and pee.element_entry_id = pevd.element_entry_id
and pevd.screen_entry_value between fnd_date.date_to_canonical(p_accrual_start_date)
and fnd_date.date_to_canonical(p_accrual_end_Date)
and pee.assignment_id = p_assignment_id;
select sum(fnd_number.canonical_to_number(pevn.screen_entry_value)),
sum(fnd_number.canonical_to_number(pevs.screen_entry_value))
from pay_element_entry_values_f pevn
,pay_element_entry_values_f pevd
,pay_element_entry_values_f pevs
,pay_element_entries_f pee
where pevn.input_value_id = p_day_input_id
and pevd.input_value_id = p_date_input_id
and pevs.input_value_id = p_sal_input_id
and pee.element_entry_id = pevn.element_entry_id
and pee.element_entry_id = pevs.element_entry_id
and pee.element_entry_id = pevd.element_entry_id
and pevd.screen_entry_value between fnd_date.date_to_canonical(p_accrual_start_date)
and fnd_date.date_to_canonical(p_accrual_end_Date)
and pee.assignment_id = p_assignment_id;
select piv1.input_value_id
,piv2.input_value_id
,piv3.input_value_id
,pet.element_type_id
from pay_element_types_f pet
,pay_input_values_f piv1
,pay_input_values_f piv2
,pay_input_values_f piv3
where piv1.element_Type_id = pet.element_type_id
and piv2.element_Type_id = pet.element_type_id
and piv3.element_Type_id = pet.element_type_id
and pet.legislation_code = 'FR'
-- and piv1.legislation_code = 'FR'
-- and piv2.legislation_code = 'FR'
-- and piv3.legislation_code = 'FR'
and pet.business_group_id is null
and piv1.business_group_id is null
and piv2.business_group_id is null
and piv3.business_group_id is null
and pet.element_name = 'FR_HOLIDAY_PAY'
and piv1.name = 'Rate'
and piv2.name = 'Protected Days Paid'
and piv3.name = 'Absence Attendance ID';
select sum(decode(prrvm.input_value_id,l_days_input_id, prrvm.result_value))
,sum(decode(prrvm.input_value_id,l_protected_days_input_id, prrvm.result_value))
from pay_run_result_values prrvm
,pay_run_result_values prrva
,pay_run_results prr
,pay_assignment_actions paa
,pay_payroll_actions ppa
where prrvm.run_result_id = prr.run_result_id
and prrva.run_result_id = prr.run_result_id
and prrvm.input_value_id in(l_days_input_id, l_protected_days_input_id)
and prrva.input_value_id = l_absence_attendance_input_id
and prrva.result_value = l_absence_attendance_id
and prr.assignment_action_id = paa.assignment_action_id
and prr.element_type_id = l_paid_element_type_id
and prr.status in ('P','PA')
and paa.payroll_action_id = ppa.payroll_Action_id
and ppa.effective_date >= l_start_date
and paa.assignment_id = l_assignment_id;
select date_start - 366
from per_absence_attendances
where absence_attendance_id = p_absence_attendance_id;
select piv1.input_value_id acp_plan_id
from pay_input_values_f piv1
where piv1.element_type_id = p_element_type_id
and piv1.display_sequence = 10;
select piv1.input_value_id reference_salary
,piv2.input_value_id reference_days
from pay_input_values_f piv1
,pay_input_values_f piv2
,pay_input_values_f piv
where piv1.element_type_id = piv2.element_type_id
and piv1.element_type_id = piv.element_type_id
and piv.input_value_id = p_ent_accrual_date_iv
and piv1.display_sequence = 80
and piv2.display_sequence = 90;
select nvl(pap.information1,6) accrual_start_month
,pap.information2 entitlement_offset
,pap.information3 entitlement_duration
,pap.information4 working_days
,pap.information5 protected_days
,pap.information6 accounting_method
,pap.information7 main_holiday_acc_plan_id
,pap.information8 ent_m_iv_id
,pap.information9 ent_p_iv_id
,pap.information10 ent_c_iv_id
,pap.information11 ent_s_iv_id
,pap.information12 ent_y_iv_id
,pap.information13 obs_m_iv_id
,pap.information14 obs_p_iv_id
,pap.information15 obs_c_iv_id
,pap.information16 obs_s_iv_id
,pap.information17 obs_y_iv_id
,pap.information18 adj_m_iv_id
,pap.information19 adj_p_iv_id
,pap.information20 adj_c_iv_id
,pap.information21 adj_s_iv_id
,pap.information22 adj_y_iv_id
,pap.information23 ent_accrual_date_iv_id
,pap.information24 obs_accrual_date_iv_id
,pap.information25 adj_accrual_date_iv_id
,pap.information26 working_days_iv_id
,pap.information27 protected_days_iv_id
,pap.accrual_plan_element_type_id accrual_plan_element_id
,piv.element_type_id holiday_element_id
,pivE.element_type_id ENT_element_id
,pivO.element_type_id OBS_element_id
,pivA.element_type_id ADJ_element_id
,pap.business_group_id business_Group_id
from pay_accrual_plans pap
,pay_input_values_f piv
,pay_input_values_f pivE
,pay_input_values_f pivO
,pay_input_values_f pivA
where pap.accrual_plan_id = p_accrual_plan_id
and piv.input_value_id = pap.pto_input_value_id
and pivE.input_value_id = pap.information8
and pivO.input_value_id = pap.information13
and pivA.input_value_id = pap.information18;
select pap.information1 accrual_start_month
,pap.information2 entitlement_offset
,pap.information3 entitlement_duration
,pap.information4 working_days
,pap.information5 protected_days
,pap.information6 accounting_method
,pap.information7 main_holiday_acc_plan_id
,pap.information8 ent_m_iv_id
,pap.information9 ent_p_iv_id
,pap.information10 ent_c_iv_id
,pap.information11 ent_s_iv_id
,pap.information12 ent_y_iv_id
,pap.information13 obs_m_iv_id
,pap.information14 obs_p_iv_id
,pap.information15 obs_c_iv_id
,pap.information16 obs_s_iv_id
,pap.information17 obs_y_iv_id
,pap.information18 adj_m_iv_id
,pap.information19 adj_p_iv_id
,pap.information20 adj_c_iv_id
,pap.information21 adj_s_iv_id
,pap.information22 adj_y_iv_id
,pap.information23 ent_accrual_date_iv_id
,pap.information24 obs_accrual_date_iv_id
,pap.information25 adj_accrual_date_iv_id
,pap.accrual_plan_element_type_id accrual_plan_element_id
,pap.accrual_plan_id accrual_plan_id
,pap.information26 working_days_iv_id
,pap.information27 protected_days_iv_id
,piv.element_Type_id holiday_element_id
,pivE.element_type_id ENT_element_id
,pivO.element_type_id OBS_element_id
,pivA.element_type_id ADJ_element_id
,pap.business_group_id business_Group_id
from pay_accrual_plans pap
,per_absence_attendances paa
,per_absence_attendance_types pat
,pay_element_entries_f pee
,pay_input_values_f piv
,pay_input_values_f pivE
,pay_input_values_f pivO
,pay_input_values_f pivA
where paa.absence_attendance_type_id = pat.absence_attendance_type_id
and pivE.input_value_id = pap.information8
and pivO.input_value_id = pap.information13
and pivA.input_value_id = pap.information18
and pat.input_value_id = pap.pto_input_value_id
and paa.absence_Attendance_id = pee.creator_id
and pee.creator_type = 'A'
and piv.input_value_id = pap.pto_input_value_id
and pee.element_entry_id = c_element_entry_id;
select pee.effective_start_date
,pee.effective_end_date
,abs.absence_attendance_id
,fnd_date.canonical_to_date(abs.abs_information1) /* date accrued */
,abs.abs_information2 /* main days */
,abs.abs_information3 /* seniority */
,abs.abs_information4 /* young mothers */
,abs.abs_information5 /* conventional days */
,abs.abs_information6 /* protected days */
,pee.assignment_id
from pay_element_entries_f pee
,per_absence_attendances abs
where pee.element_entry_id = c_element_entry_id
and pee.creator_id = abs.absence_attendance_id;
select effective_date
from fnd_sessions
where session_id = USERENV('SESSIONID');
select target.org_information3
,target.org_information4
,target.org_information5
,target.org_information6
,target.org_information7
from hr_organization_information target
,per_all_assignments_f assign
WHERE p_date_earned BETWEEN assign.effective_start_date AND assign.effective_end_date
AND assign.assignment_id = p_assignment_id
AND assign.establishment_id = target.organization_id
AND target.org_information_context = 'FR_ESTAB_ACCRUAL_RATE'
AND fnd_date.date_to_canonical(p_date_earned)
BETWEEN target.org_information1
AND nvl(target.org_information2, '4712/12/31 00:00:00');
select target.org_information3
,target.org_information4
,target.org_information5
,target.org_information6
,target.org_information7
from hr_organization_units comp
,hr_organization_information target
,hr_organization_information estab
,per_all_assignments_f assign
WHERE p_date_earned BETWEEN assign.effective_start_date AND assign.effective_end_date
AND assign.assignment_id = p_assignment_id
AND assign.establishment_id = estab.organization_id
AND target.organization_id = comp.organization_id
AND estab.org_information1 = to_char(comp.organization_id)
AND target.org_information_context = 'FR_COMP_ACCRUAL_RATE'
AND fnd_date.date_to_canonical(p_date_earned)
BETWEEN target.org_information1
AND nvl(target.org_information2, '4712/12/31 00:00:00');
select pap.accrual_plan_id
from pay_accrual_plans pap
,pay_element_entries_f pee
,pay_element_links_f pel
where pee.element_entry_id = P_ELEMENT_ENTRY_ID
and pee.element_link_id = pel.element_link_id
and pel.element_type_id = pap.accrual_plan_element_type_id;
select 'TRUE'
from per_all_assignments_f asg,
per_contracts_f pcf
where p_date_earned >= asg.effective_start_date
and p_assignment_id = asg.assignment_id
and asg.contract_id = pcf.contract_id
and p_date_earned between pcf.effective_start_date
and pcf.effective_end_date
and pcf.reference = p_reference
and pcf.ctr_information2 = 'FIXED_TERM';
select pcf.contract_id, pcf.status
from per_all_assignments_f asg,
per_contracts_f pcf
where p_assignment_id = asg.assignment_id
and asg.contract_id = pcf.contract_id
and p_date_earned >= asg.effective_start_date
and p_date_earned between pcf.effective_start_date
and pcf.effective_end_date
and pcf.reference = p_reference;
select 0, pev_def.screen_entry_value
from pay_element_entries_f pee
,pay_element_entry_values_f pev_ref
,pay_input_values_f piv_ref
,pay_element_types_f pet
,pay_input_values_f piv_def
,pay_element_entry_values_f pev_def
where pet.element_name = 'FR_FIXED_TERM_CONTRACT_INDEMNITY'
and pet.legislation_code = 'FR'
and pet.business_group_id is null
and pet.element_type_id = piv_ref.element_type_id
and piv_def.name = 'Deferred Payment'
and piv_def.legislation_code = 'FR'
and piv_def.business_group_id is null
and pev_def.input_value_id = piv_def.input_Value_id
and pee.element_entry_id = pev_def.element_entry_id
and pev_ref.screen_entry_value = p_reference
and piv_ref.name = 'Contract Reference'
and piv_ref.legislation_code = 'FR'
and piv_ref.business_group_id is null
and pev_ref.input_value_id = piv_ref.input_Value_id
and pee.element_entry_id = pev_ref.element_entry_id
and pee.assignment_id = p_assignment_id
and p_date_earned between pev_def.effective_start_date
and pev_def.effective_end_date
and p_date_earned between piv_def.effective_start_date
and piv_def.effective_end_date
and p_date_earned between pev_ref.effective_start_date
and pev_ref.effective_end_date
and p_date_earned between piv_ref.effective_start_date
and piv_ref.effective_end_date
and p_date_earned between pet.effective_start_date
and pet.effective_end_date
and p_date_earned between pee.effective_start_date
and pee.effective_end_date;
select 1
from per_absence_attendances paa
,pay_element_entries_f pee
,per_absence_attendance_types pat
where pee.creator_type = 'A'
and paa.absence_attendance_id = pee.creator_id
and fnd_date.date_to_canonical (paa.date_start) between fnd_date.date_to_canonical(p_start_date) and fnd_date.date_to_canonical(p_end_date)
and pee.assignment_id = p_assignment_id
and paa.absence_attendance_type_id = pat.absence_attendance_type_id
and pat.absence_category = 'S';
select NVL(sum(paa.abs_information2), -1)
from per_absence_attendances paa
,pay_element_entries_f pee
where pee.element_link_id in (select element_link_id
from pay_element_links_f
where element_type_id = p_holiday_Element_id
)
and pee.creator_type = 'A'
and paa.absence_Attendance_id = pee.creator_id
and fnd_date.date_to_canonical(paa.date_start) between fnd_date.date_to_canonical(p_start_date)
and fnd_date.date_to_canonical(p_end_date)
and pee.assignment_id = p_assignment_id;
select 1
from per_absence_attendances paa
,pay_element_entries_f pee
where pee.element_link_id in (select element_link_id
from pay_element_links_f
where element_type_id = p_holiday_element_id
)
and pee.creator_type = 'A'
and paa.absence_Attendance_id = pee.creator_id
and fnd_date.date_to_canonical(paa.date_start) >= fnd_date.date_to_canonical(p_start_date)
and fnd_date.date_to_canonical(paa.date_end) <= fnd_date.date_to_canonical(p_end_date)
and paa.absence_days >= p_no_absences
and pee.assignment_id = p_assignment_id;
select nvl(sum(pevm.screen_entry_value),0)
from pay_element_entry_values_f pevm
,pay_element_entries_f pee
,per_all_assignments_f pasg
where pevm.input_value_id = c_type_m_iv_id
and pee.element_entry_id = pevm.element_entry_id
and pevm.effective_start_date between c_start_date and c_end_date
and pee.effective_start_date between c_start_date and c_end_date
and pee.assignment_id = pasg.assignment_id
and pasg.person_id = p_person_id;
Select max(ppos.date_start)
From per_periods_of_service ppos
Where ppos.person_id = c_person_id
and ppos.date_start <= p_abs_date_start;
Select sum(date_end-date_start+1)
from per_absence_attendances
where person_id = p_person_id
and date_end <= p_abs_date_end
and date_start >= p_hire_date
and absence_attendance_type_id = p_absence_attendance_type_id;
select min(effective_start_date)as min_effective_start_date
from per_all_assignments_f
where assignment_id=c_assignment_id;
select contract_id,effective_end_date,effective_start_date
from per_all_assignments_f
where c_min_effective_start_date between effective_start_date and effective_end_date
and assignment_id=c_assignment_id ;
select effective_start_date, ctr_information2 con_catg
from per_contracts_f
where contract_id=c_contract_id
and ctr_information_category = 'FR';
select max(effective_end_date)
from per_contracts_f contr
where contract_id=c_contract_id;
select min(effective_end_date),min(effective_start_date) INTO p_contract_end_date,p_contract_start_date
from per_all_assignments_f paf
where paf.assignment_id=assignment_id ;
Select decode(con.ctr_information12,'HOUR', con.ctr_information13, asg.frequency) frequency,
decode(con.ctr_information12,'HOUR', fnd_number.canonical_to_number(con.ctr_information11), asg.normal_hours) hours,
substr(hruserdt.get_table_value(p_business_group_id, 'FR_CIPDZ', 'CIPDZ',nvl(asg.employment_category,'FR'),p_effective_date),1,1) cipdz_catg
from per_all_assignments_f asg,
per_contracts_f con
where asg.assignment_id = p_assignment_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 asg.contract_id = con.contract_id
and con.business_group_id = p_business_group_id
and p_effective_date between
con.effective_start_date and con.effective_end_date;