[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT papf.original_date_of_hire
FROM per_all_people_f papf,
per_all_assignments_f pasg
WHERE pasg.assignment_id = p_assignment_id
AND pasg.Business_group_id= p_business_group_id
AND p_absence_start_date BETWEEN pasg.effective_start_date and pasg.effective_end_date
AND papf.person_id = pasg.person_id
AND papf.Business_group_id=p_Business_group_id
AND p_absence_start_date BETWEEN papf.effective_start_date and papf.effective_end_date;
SELECT global_value
FROM ff_globals_f
WHERE global_name = c_global_name
AND legislation_code = 'FR'
AND c_date_earned BETWEEN effective_start_date AND effective_end_date;
SELECT papf.original_date_of_hire
FROM per_all_people_f papf,
per_all_assignments_f pasg
WHERE pasg.assignment_id = p_assignment_id
AND pasg.Business_group_id= p_Business_group_id
AND p_absence_start_date BETWEEN pasg.effective_start_date and pasg.effective_end_date
AND papf.person_id = pasg.person_id
AND papf.Business_group_id=p_Business_group_id
AND p_absence_start_date BETWEEN papf.effective_start_date and papf.effective_end_date;
select db.defined_balance_id
from pay_defined_balances db
, pay_balance_dimensions bd
, pay_balance_types bt
where db.balance_type_id = bt.balance_type_id
and db.balance_dimension_id = bd.balance_dimension_id
and bt.balance_name = p_balance_name
and bt.legislation_code = 'FR'
and bd.database_item_suffix = '_ASG_PTD'
and bd.legislation_code = 'FR';
select sum(decode(trunc(spact.effective_date,'YYYY'),c_start_yr1,fnd_number.canonical_to_number(TARGET.result_value))) pymt_yr1,
sum(decode(trunc(spact.effective_date,'YYYY'),c_start_yr2,fnd_number.canonical_to_number(TARGET.result_value))) pymt_yr2
from pay_run_result_values TARGET
, pay_balance_feeds_f FEED
, pay_run_results RR
, pay_assignment_actions ASSACT
, pay_payroll_actions PACT
, pay_balance_types bal
--
, pay_assignment_actions sasact
, pay_payroll_actions spact
, pay_entry_process_details proc
where ASSACT.assignment_id = P_Assignment_id
and BAL.balance_name = c_balance_name
and BAL.balance_type_id = FEED.balance_type_id
and FEED.balance_type_id +0
= bal.balance_type_id + 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 PACT.action_type <> 'V'
and NOT EXISTS
(SELECT NULL
FROM pay_payroll_actions RPACT
, pay_assignment_actions RASSACT
, pay_action_interlocks RINTLK
where ASSACT.assignment_action_id = RINTLK.locked_action_id
and RINTLK.locking_action_id = RASSACT.assignment_action_id
and RPACT.payroll_action_id = RASSACT.payroll_action_id
and RPACT.action_type = 'V' )
and PACT.effective_date
between trunc(p_period_end_date,'MM')
and last_day(p_period_end_date)
--
and sasact.payroll_action_id = spact.payroll_action_id
and spact.effective_date between c_start_yr2 and c_end_yrs
and proc.source_asg_action_id = sasact.assignment_action_id
and rr.element_entry_id = proc.element_entry_id
and proc.retro_component_id is not null
group by trunc(spact.effective_date,'YYYY');
SELECT pabs.person_id person,
pabs.abs_information1 parent_abs,
papf.original_date_of_hire hiredate,
pabs.date_start abs_start
FROM per_absence_attendances pabs,
per_all_people_f papf
WHERE pabs.absence_attendance_id = p_absence_id
AND pabs.business_group_id = p_business_group_id
AND pabs.abs_information_category ='FR_S'
AND papf.person_id = pabs.person_id
AND papf.business_group_id = p_business_group_id
AND p_start_date BETWEEN papf.effective_start_date AND papf.effective_end_date;
SELECT pabs.date_start abs_start_date,
pabs.abs_information8 ijss_cal,
nvl(fnd_date.canonical_to_date(pabs.abs_information7),hr_general.end_of_time) elig_dt
FROM per_absence_attendances pabs
WHERE pabs.absence_attendance_id = c_absence_id
AND pabs.business_group_id = p_business_group_id
AND pabs.abs_information_category ='FR_S';
SELECT count(*)
FROM per_absence_attendances pabs_hol,
per_absence_attendance_types pabt
WHERE pabs_hol.person_id = c_person_id
AND pabs_hol.business_group_id = p_business_group_id
AND c_hol_date BETWEEN pabs_hol.date_start AND pabs_hol.date_end
AND pabt.absence_attendance_type_id = pabs_hol.absence_attendance_type_id
AND pabt.absence_category in ('FR_MAIN_HOLIDAY','FR_ADDITIONAL_HOLIDAY','FR_RTT_HOLIDAY') ;
SELECT nvl(min('Y'),'N') maternity_related
FROM per_absence_attendances pabs,
per_absence_attendance_types pabt
WHERE pabs.person_id = c_person_id
AND (c_sick_start_date - 3 ) BETWEEN pabs.date_start AND pabs.date_end
AND pabt.absence_attendance_type_id = pabs.absence_attendance_type_id
AND pabt.absence_category = 'M';
SELECT pdb.defined_balance_id
FROM pay_balance_types pbt,
pay_balance_dimensions pbd,
pay_defined_balances pdb
WHERE pdb.balance_type_id = pbt.balance_type_id
AND pdb.balance_dimension_id = pbd.balance_dimension_id
AND pbt.balance_name = 'FR_SICKNESS_IJSS_REFERENCE_SALARY'
AND pbd.database_item_suffix = '_ASG_PTD'
AND pdb.legislation_code = 'FR';
SELECT global_value
FROM ff_globals_f
WHERE global_name = c_global_name
AND legislation_code = 'FR'
AND c_date_earned BETWEEN effective_start_date AND effective_end_date;
SELECT count(*)
FROM per_contact_relationships
WHERE person_id = c_person_id
AND business_group_id = p_business_group_id
AND c_abs_start_date BETWEEN nvl(date_start, hr_general.start_of_time) and nvl(date_end, hr_general.end_of_time)
AND dependent_flag ='Y';
SELECT row_low_range_or_name
FROM pay_user_tables put, pay_user_rows_f purf
WHERE put.user_table_name = c_table_name
AND put.user_table_id = purf.user_table_id
AND c_effective_date between effective_start_date and effective_end_date;
g_overlap.DELETE;
SELECT abs_information9 SMID_6,
abs_information10 SMID_12,
abs_information11 hrs_200,
abs_information12 hrs_800
FROM per_absence_Attendances
WHERE absence_Attendance_id = p_absence_id
AND business_group_id = p_business_group_id
AND abs_information_category ='FR_S';
SELECT global_value
FROM ff_globals_f
WHERE global_name = c_global_name
AND legislation_code = 'FR'
AND c_date_earned BETWEEN effective_start_date AND effective_end_date;
SELECT date_start, date_end,absence_attendance_id,abs_information2
FROM per_absence_attendances
WHERE person_id = p_person_id
AND ( absence_attendance_id = p_parent_abs_id
OR abs_information1 = to_char(p_parent_abs_id))
AND business_group_id = p_business_group_id
AND abs_information_category = 'FR_S'
ORDER BY date_start ;
SELECT date_start, date_end,abs_information2
FROM per_absence_attendances
WHERE person_id = p_person_id
AND (absence_attendance_id = p_parent_abs_id
OR abs_information1 = to_char(p_parent_abs_id))
AND business_group_id = p_business_group_id
AND abs_information_category = 'FR_S'
AND date_start <= p_period_end_date
ORDER BY date_start desc;
SELECT nvl(min('Y'),'N') maternity_related
FROM per_absence_attendances pabs,
per_absence_attendance_types pabt
WHERE pabs.person_id = c_person_id
AND (c_sick_start_date - 3 ) BETWEEN pabs.date_start AND pabs.date_end
AND pabt.absence_attendance_type_id = pabs.absence_attendance_type_id
AND pabt.absence_category = 'M';
select paa.absence_attendance_id
, to_number(paa.abs_information1) parent_absence_id
, paa.date_start
, paa.date_end
, paa.date_end - paa.date_start + 1 duration
, null effective_start_date
, null effective_end_date
, paa.abs_information2 subrogated
, paa.abs_information8 estimated
, nvl(inc.inc_information1,'N') work_incident
, paa.person_id
, paa.business_group_id
from per_absence_attendances paa
, pay_element_entries_f pee
, per_work_incidents inc
where pee.element_entry_id = p_element_entry_id
and paa.absence_attendance_id = pee.creator_id
and pee.creator_type = 'A'
and decode(paa.abs_information_category,'FR_S',to_number(paa.abs_information6),null) = inc.incident_id(+);
select min(effective_start_date)
, max(effective_end_date)
from pay_element_entries_f
where element_entry_id = p_element_entry_id;
select paa.absence_attendance_id
, 0 parent_absence_id
, paa.date_start
, paa.date_end
, paa.date_end - paa.date_start + 1 duration
, null effective_start_date
, null effective_end_date
, paa.abs_information2 subrogated
, paa.abs_information8 estimated
, nvl(inc.inc_information1,'N') work_incident
, paa.person_id
, paa.business_group_id
from per_absence_attendances paa
, per_work_incidents inc
where paa.absence_attendance_id = p_absence_attendance_id
and paa.abs_information6 = to_char(inc.incident_id(+));
select absence_attendance_id
, to_number(abs_information1) parent_absence_id
, date_start
, date_end
, date_end - date_start + 1 duration
, null effective_start_date
, null effective_end_date
, null subrogated
, null estimated
, null work_incident
, person_id
, business_group_id
from per_absence_attendances
where abs_information1 = to_char(p_parent_absence_id)
and date_end <= p_max_end_date
and person_id = p_person_id
and business_group_id = p_business_group_id
order by date_start;
SELECT global_value
FROM ff_globals_f
WHERE global_name = c_global_name
AND legislation_code = 'FR'
AND c_date_earned BETWEEN effective_start_date AND effective_end_date;
Select peef.element_entry_id,
peef.effective_start_date,
peef.effective_end_date,
peef.assignment_id,
ceil(months_between(peef.effective_end_date,peef.effective_start_date)) Period,
--
paa.date_start Abs_start_date,
paa.date_end Abs_end_date,
nvl(paa.abs_information8,'N') IJSS_estimate
from
pay_element_entries_f peef
,pay_element_links_f pelf
,pay_element_types_f petf
,per_absence_attendances paa
where peef.assignment_id = p_asg.assignment_id
and peef.element_link_id = pelf.element_link_id
and pelf.element_type_id = petf.element_type_id
and p_asg.action_start_date between pelf.effective_start_date and pelf.effective_end_date
and petf.element_name = 'FR_SICKNESS_INFORMATION'
and p_asg.action_start_date between petf.effective_start_date and petf.effective_end_date
and peef.effective_start_date < trunc(p_sickness_start_date,'MONTH')
and paa.absence_attendance_id = peef.creator_id
-- added clause for selecting within a year
and paa.date_start >= add_months(p_asg.action_start_date, -12)
and peef.creator_type = 'A' ;
Select 'Y'
from pay_payroll_actions ppa
,pay_assignment_actions paa
,pay_run_results prr
where ppa.payroll_id + 0 = p_asg.payroll_id
-- and ppa.date_earned < trunc(p_sickness_start_date,'MONTH')
-- and to_char(ppa.date_earned,'MON-YYYY') = to_char(c_period,'MON-YYYY')
and paa.assignment_id + 0 = c_assignment_id
and ppa.payroll_action_id = paa.payroll_action_id
and ppa.action_type in ('R','Q')
and prr.status in ('P','PA')
-- and paa.action_status = 'C'
and paa.assignment_action_id = prr.assignment_action_id
and prr.source_id = c_element_entry_id;
Select min(fnd_date.canonical_to_date(prrv_pst.result_value)) Pmt_start_dt,
max(fnd_date.canonical_to_date(prrv_pet.result_value)) Pmt_end_dt,
piv_pst.input_value_id pmt_start_input_id,
piv_pet.input_value_id pmt_end_input_id
--
From pay_run_result_values prrv_pst,
pay_run_result_values prrv_pet,
pay_input_values_f piv_pst,
pay_input_values_f piv_pet,
pay_run_results prr_pst,
pay_run_results prr_pet,
pay_element_types_f peltf,
pay_assignment_actions pact,
pay_payroll_actions ppac
--
Where prrv_pst.result_value between fnd_date.date_to_canonical(c_abs_start_date) and fnd_date.date_to_canonical(c_abs_end_date)
and prrv_pet.result_value between fnd_date.date_to_canonical(c_abs_start_date) and fnd_date.date_to_canonical(c_abs_end_date)
--
and prrv_pst.input_value_id = piv_pst.input_value_id
and prrv_pst.run_result_id = prr_pst.run_result_id
--
and peltf.element_name = 'FR_SICKNESS_CPAM_PROCESS'
and c_abs_start_date between peltf.effective_start_date and peltf.effective_end_date
and piv_pst.element_type_id = peltf.element_type_id
and piv_pst.name = 'Payment From Date'
and c_abs_start_date between piv_pst.effective_start_date and piv_pst.effective_end_date
----
and prr_pst.element_type_id = peltf.element_type_id
and prr_pst.assignment_action_id = pact.assignment_action_id
----
and pact.assignment_id = p_asg.assignment_id
and pact.action_status ='C'
and prr_pst.status in ('P','PA')
and prr_pet.status in ('P','PA')
and pact.payroll_action_id = ppac.payroll_action_id
and ppac.action_type in ('R','Q')
and ppac.payroll_id = p_asg.payroll_id
and ppac.date_earned between c_abs_start_date and p_asg.action_end_date
--
and prrv_pet.input_value_id = piv_pet.input_value_id
and prrv_pet.run_result_id = prr_pet.run_result_id
--
and piv_pet.element_type_id = peltf.element_type_id
and piv_pet.name = 'Payment To Date'
and c_abs_start_date between piv_pet.effective_start_date and piv_pet.effective_end_date
--
and prr_pet.element_type_id = peltf.element_type_id
and prr_pet.assignment_action_id = pact.assignment_action_id
group by piv_pst.input_value_id,piv_pet.input_value_id;
Select peval_pst.screen_entry_value pmt_start_date,
peval_pet.screen_entry_value pmt_end_date
--
from pay_element_entry_values_f peval_pst,
pay_element_entry_values_f peval_pet,
pay_element_entries_f pentf,
pay_element_links_f plink,
pay_element_types_f peltf
--
where pentf.assignment_id = p_asg.assignment_id
and pentf.element_link_id = plink.element_link_id
and pentf.effective_start_date between p_asg.action_start_date and p_asg.action_end_date
and plink.element_type_id = peltf.element_type_id
and peltf.element_name = 'FR_SICKNESS_CPAM_PROCESS'
and p_asg.action_start_date between peltf.effective_start_date and peltf.effective_end_date
and p_asg.action_start_date between plink.effective_start_date and plink.effective_end_date
and peval_pst.element_entry_id = pentf.element_entry_id
and peval_pst.input_value_id = c_pst_input_value_id
and peval_pet.element_entry_id = pentf.element_entry_id
and peval_pet.input_value_id = c_pet_input_value_id
and peval_pst.screen_entry_value > fnd_date.date_to_canonical(c_prev_pmt_end_dt)
and peval_pet.screen_entry_value <= fnd_date.date_to_canonical(c_abs_end_date)
and p_asg.action_end_date between peval_pst.effective_start_date and peval_pst.effective_end_date
and p_asg.action_end_date between peval_pet.effective_start_date and peval_pet.effective_end_date;
select PTP.start_date,
PTP.end_date
from pay_payroll_actions PPA,
per_time_periods PTP
where ppa.payroll_action_id = c_payroll_action_id
and ppa.business_group_id = c_business_group_id
and ppa.payroll_id = c_payroll_id
and ppa.payroll_id = ptp.payroll_id
and c_payment_start_date between ptp.start_date and ptp.end_date
and c_payment_end_date between ptp.start_date and ptp.end_date ;
SELECT pdb.defined_balance_id
FROM pay_balance_types pbt,
pay_balance_dimensions pbd,
pay_defined_balances pdb
WHERE pdb.balance_type_id = pbt.balance_type_id
AND pdb.balance_dimension_id = pbd.balance_dimension_id
AND pbt.balance_name = p_balance_name
AND pbd.database_item_suffix = '_ASG_PTD'
AND pdb.legislation_code = 'FR';
select max(e.element_type_id)
,max(decode(i.name,'Parent Absence ID',i.input_value_id,null))
,max(decode(i.name,'Guarantee Type',i.input_value_id,null))
,max(decode(i.name,'Guarantee ID',i.input_value_id,null))
,max(decode(i.name,'GI Payment',i.input_value_id,null))
,max(decode(i.name,'Net',i.input_value_id,null))
,max(decode(i.name,'Adjustment',i.input_value_id,null))
,max(decode(i.name,'IJSS Gross',i.input_value_id,null))
,max(decode(i.name,'Best Method',i.input_value_id,null))
,max(decode(i.name,'Band1',i.input_value_id,null))
,max(decode(i.name,'Band2',i.input_value_id,null))
,max(decode(i.name,'Band3',i.input_value_id,null))
,max(decode(i.name,'Band4',i.input_value_id,null))
,max(decode(i.name,'Payment Start Date',i.input_value_id,null))
,max(decode(i.name,'Payment End Date',i.input_value_id,null))
into g_gi_info_element_type_id
,g_gi_info_absence_id_iv_id
,g_gi_info_guarantee_type_iv_id
,g_gi_info_guarantee_id_iv_id
,g_gi_info_gi_payment_iv_id
,g_gi_info_net_iv_id
,g_gi_info_adjustment_iv_id
,g_gi_info_ijss_gross_iv_id
,g_gi_info_best_method_iv_id
,g_gi_info_band1_iv_id
,g_gi_info_band2_iv_id
,g_gi_info_band3_iv_id
,g_gi_info_band4_iv_id
,g_gi_info_start_date_iv_id
,g_gi_info_end_date_iv_id
from pay_element_types_f e,
pay_input_values_f i
where e.element_name = 'FR_SICKNESS_GI_INFO'
and e.legislation_code = 'FR'
and e.element_type_id = i.element_type_id
and p_effective_date between e.effective_start_date and e.effective_end_date
and p_effective_date between i.effective_start_date and i.effective_end_date;
select max(e.element_type_id)
,max(decode(i.name,'Parent Absence ID',i.input_value_id,null))
,max(decode(i.name,'Guarantee Type',i.input_value_id,null))
,max(decode(i.name,'Guarantee ID',i.input_value_id,null))
,max(decode(i.name,'GI Payment',i.input_value_id,null))
,max(decode(i.name,'Net',i.input_value_id,null))
,max(decode(i.name,'Adjustment',i.input_value_id,null))
,max(decode(i.name,'IJSS Gross',i.input_value_id,null))
,max(decode(i.name,'Best Method',i.input_value_id,null))
,max(decode(i.name,'Band1',i.input_value_id,null))
,max(decode(i.name,'Band2',i.input_value_id,null))
,max(decode(i.name,'Band3',i.input_value_id,null))
,max(decode(i.name,'Band4',i.input_value_id,null))
,max(decode(i.name,'Payment Start Date',i.input_value_id,null))
,max(decode(i.name,'Payment End Date',i.input_value_id,null))
into g_gi_i_r_element_type_id
,g_gi_i_r_absence_id_iv_id
,g_gi_i_r_guarantee_type_iv_id
,g_gi_i_r_guarantee_id_iv_id
,g_gi_i_r_gi_payment_iv_id
,g_gi_i_r_net_iv_id
,g_gi_i_r_adjustment_iv_id
,g_gi_i_r_ijss_gross_iv_id
,g_gi_i_r_best_method_iv_id
,g_gi_i_r_band1_iv_id
,g_gi_i_r_band2_iv_id
,g_gi_i_r_band3_iv_id
,g_gi_i_r_band4_iv_id
,g_gi_i_r_start_date_iv_id
,g_gi_i_r_end_date_iv_id
from pay_element_types_f e,
pay_input_values_f i
where e.element_name = 'FR_SICKNESS_GI_INFO_RETRO'
and e.legislation_code = 'FR'
and e.element_type_id = i.element_type_id
and p_effective_date between e.effective_start_date and e.effective_end_date
and p_effective_date between i.effective_start_date and i.effective_end_date;
select /*+ORDERED */
assact.action_sequence action_sequence,
nvl(sum(decode(target.input_value_id,
g_gi_info_net_iv_id,fnd_number.canonical_to_number(target.result_value),
g_gi_i_r_net_iv_id, fnd_number.canonical_to_number(target.result_value),
0)),0) previous_net,
nvl(sum(decode(target.input_value_id,
g_gi_info_gi_payment_iv_id,
fnd_number.canonical_to_number(target.result_value),
g_gi_i_r_gi_payment_iv_id,
fnd_number.canonical_to_number(target.result_value),
0)),0) previous_payment,
nvl(sum(decode(target.input_value_id,
g_gi_info_adjustment_iv_id,
fnd_number.canonical_to_number(target.result_value),
g_gi_i_r_adjustment_iv_id,
fnd_number.canonical_to_number(target.result_value),
0)),0) previous_adjustment,
nvl(sum(decode(target.input_value_id,
g_gi_info_ijss_gross_iv_id,
fnd_number.canonical_to_number(target.result_value),
g_gi_i_r_ijss_gross_iv_id,
fnd_number.canonical_to_number(target.result_value),
0)),0) previous_IJSS_gross,
max(decode(target.input_value_id,
g_gi_info_end_date_iv_id,
fnd_date.canonical_to_date(target.result_value),
g_gi_i_r_end_date_iv_id,
fnd_date.canonical_to_date(target.result_value),
null)) payment_end_date,
max(decode(target.input_value_id,
g_gi_info_best_method_iv_id,target.result_value,
g_gi_i_r_best_method_iv_id, target.result_value,null)) best_method,
nvl(ee.creator_id,0) retro_asg_action,
epd.adjustment_type retro_adj_type
from pay_assignment_actions assact
,pay_payroll_actions pact
,pay_run_results rr
,pay_run_result_values guarantee_id
,pay_run_result_values parent_absence_id
,pay_run_result_values target
,pay_entry_process_details epd
,pay_element_entries_f ee
where assact.assignment_id = p_assignment_id
and assact.payroll_action_id = pact.payroll_action_id
and pact.action_type in ('R', 'Q', 'B')
and pact.date_earned >= p_parent_absence_start_date
and assact.assignment_action_id = rr.assignment_action_id
and rr.element_type_id in (g_gi_info_element_type_id,
g_gi_i_r_element_type_id)
and rr.status in ('P','PA')
and epd.element_entry_id(+) = rr.element_entry_id
and epd.retro_component_id(+) is not null
and ee.element_entry_id(+) = epd.element_entry_id
and target.run_result_id = rr.run_result_id
and target.input_value_id in (g_gi_info_net_iv_id,
g_gi_i_r_net_iv_id,
g_gi_info_gi_payment_iv_id,
g_gi_i_r_gi_payment_iv_id,
g_gi_info_adjustment_iv_id,
g_gi_i_r_adjustment_iv_id,
g_gi_info_ijss_gross_iv_id,
g_gi_i_r_ijss_gross_iv_id,
g_gi_info_end_date_iv_id,
g_gi_i_r_end_date_iv_id,
g_gi_info_best_method_iv_id,
g_gi_i_r_best_method_iv_id)
and target.result_value is not null
and parent_absence_id.run_result_id = rr.run_result_id
and parent_absence_id.input_value_id in (g_gi_info_absence_id_iv_id,
g_gi_i_r_absence_id_iv_id)
and parent_absence_id.result_value = p_parent_absence_id
and guarantee_id.run_result_id = rr.run_result_id
and guarantee_id.input_value_id in (g_gi_info_guarantee_id_iv_id,
g_gi_i_r_guarantee_id_iv_id)
and guarantee_id.result_value = p_guarantee_id
and NOT EXISTS
(SELECT 1
FROM pay_payroll_actions RPACT
, pay_assignment_actions RASSACT
, pay_action_interlocks RINTLK
where ASSACT.assignment_action_id = RINTLK.locked_action_id
and RINTLK.locking_action_id = RASSACT.assignment_action_id
and RPACT.payroll_action_id = RASSACT.payroll_action_id
and RPACT.action_type = 'V' )
group by assact.action_sequence, rr.run_result_id,
ee.creator_id,epd.adjustment_type
order by 1,6,8,9 desc;
select /*+ORDERED*/
fnd_date.canonical_to_date(rrv_end.result_value) payment_end_date
,nvl(sum(decode(target.input_value_id,
g_gi_info_band1_iv_id,target.result_value,
g_gi_i_r_band1_iv_id, target.result_value,0)),0)
,nvl(sum(decode(target.input_value_id,
g_gi_info_band2_iv_id,target.result_value,
g_gi_i_r_band2_iv_id, target.result_value,0)),0)
,nvl(sum(decode(target.input_value_id,
g_gi_info_band3_iv_id,target.result_value,
g_gi_i_r_band3_iv_id, target.result_value,0)),0)
,nvl(sum(decode(target.input_value_id,
g_gi_info_band4_iv_id,target.result_value,
g_gi_i_r_band4_iv_id, target.result_value,0)),0)
INTO l_payment_end_date
,l_overlap_band1_days
,l_overlap_band2_days
,l_overlap_band3_days
,l_overlap_band4_days
from pay_assignment_actions assact,
pay_payroll_actions pact,
pay_run_results rr,
pay_run_result_values rrv_end,
pay_run_result_values rrv_start,
pay_run_result_values rrv_guarantee,
pay_run_result_values target
where assact.assignment_id = P_asg.assignment_id
and assact.action_status = 'C'
and assact.payroll_action_id = pact.payroll_action_id
and pact.action_type in ('R','Q','B','V')
and assact.assignment_action_id = rr.assignment_action_id
and rr.element_type_id in (g_gi_info_element_type_id,
g_gi_i_r_element_type_id)
and rr.status in ('P','PA')
and rr.run_result_id = rrv_start.run_result_id
and rrv_start.input_value_id in (g_gi_info_start_date_iv_id,
g_gi_i_r_start_date_iv_id )
and rrv_start.result_value < l_date_from_chr
and rr.run_result_id = rrv_end.run_result_id
and rrv_end.input_value_id in (g_gi_info_end_date_iv_id,
g_gi_i_r_end_date_iv_id)
and rrv_end.result_value >= l_date_from_chr
and rr.run_result_id = rrv_guarantee.run_result_id
and rrv_guarantee.input_value_id in (g_gi_info_guarantee_id_iv_id,
g_gi_i_r_guarantee_id_iv_id )
and rrv_guarantee.result_value = l_ov_gi_id_chr
and target.run_result_id = rr.run_result_id
and target.input_value_id in (g_gi_info_band1_iv_id,
g_gi_info_band2_iv_id,
g_gi_info_band3_iv_id,
g_gi_info_band4_iv_id,
g_gi_i_r_band1_iv_id,
g_gi_i_r_band2_iv_id,
g_gi_i_r_band3_iv_id,
g_gi_i_r_band4_iv_id)
and target.result_value is not null
group by fnd_date.canonical_to_date(rrv_end.result_value);
select /*+ ORDERED */
nvl(sum(decode(target.input_value_id,
g_gi_info_band1_iv_id,target.result_value,
g_gi_i_r_band1_iv_id, target.result_value,0)),0)
,nvl(sum(decode(target.input_value_id,
g_gi_info_band2_iv_id,target.result_value,
g_gi_i_r_band2_iv_id, target.result_value,0)),0)
,nvl(sum(decode(target.input_value_id,
g_gi_info_band3_iv_id,target.result_value,
g_gi_i_r_band3_iv_id, target.result_value,0)),0)
,nvl(sum(decode(target.input_value_id,
g_gi_info_band4_iv_id,target.result_value,
g_gi_i_r_band4_iv_id, target.result_value,0)),0)
from pay_assignment_actions assact
,pay_payroll_actions pact
,pay_run_results rr
,pay_run_result_values guarantee_id
,pay_run_result_values payment_date
,pay_run_result_values target
where assact.assignment_id = p_asg.assignment_id
and assact.payroll_action_id = pact.payroll_action_id
and pact.action_type in ('R', 'Q', 'V', 'B')
and assact.assignment_action_id = rr.assignment_action_id
and rr.element_type_id in (g_gi_info_element_type_id,
g_gi_i_r_element_type_id)
and rr.run_result_id = target.run_result_id
and rr.status in ('P','PA')
and target.result_value is not null
and target.input_value_id in (g_gi_info_band1_iv_id,
g_gi_info_band2_iv_id,
g_gi_info_band3_iv_id,
g_gi_info_band4_iv_id,
g_gi_i_r_band1_iv_id,
g_gi_i_r_band2_iv_id,
g_gi_i_r_band3_iv_id,
g_gi_i_r_band4_iv_id)
and rr.run_result_id = guarantee_id.run_result_id
and guarantee_id.input_value_id in (g_gi_info_guarantee_id_iv_id,
g_gi_i_r_guarantee_id_iv_id)
and guarantee_id.result_value = p_gi_id_chr
and rr.run_result_id = payment_date.run_result_id
and payment_date.input_value_id in (g_gi_info_end_date_iv_id,
g_gi_i_r_end_date_iv_id)
and payment_date.result_value between p_date_from_chr and p_date_to_chr;
SELECT TRUNC((MONTHS_BETWEEN(paa.date_start,
decode(ps.adjusted_svc_date,NULL, ps.date_start, ps.adjusted_svc_date)
)/12), 4)
--paa.date_start
INTO l_svc_in_years
--l_parent_absence_start_date
FROM per_absence_attendances paa,
per_periods_of_service ps,
per_all_assignments_f pas
WHERE ps.person_id = pas.person_id
AND pas.assignment_id = l_assgt_id
AND paa.absence_attendance_id = l_parent_absence_id
AND paa.person_id = pas.person_id
AND l_parent_absence_start_date between pas.effective_start_date and pas.effective_end_date ;
l_bands.DELETE; -- Clearing the l_bands table of the current GI rows
SELECT pdb.defined_balance_id
FROM pay_balance_types_tl pbt,
pay_balance_dimensions pbd,
pay_defined_balances pdb
WHERE pdb.balance_type_id = pbt.balance_type_id
AND pdb.balance_dimension_id = pbd.balance_dimension_id
AND pbt.balance_name = p_balance_name
AND pbd.dimension_name = 'Assignment Proration Run To Date'
AND (( pdb.legislation_code = 'FR') or
(pdb.business_group_id = p_asg.business_group_id));
select ENT.element_entry_id
,fnd_number.canonical_to_number(G.screen_entry_value) guarantee_id
,max(decode(ENT_PT.input_value_id,g_ben_guarantee_type_iv_id,ENT_PT.screen_entry_value,null)) guarantee_type
,fnd_number.canonical_to_number(max(decode(ENT_PT.input_value_id,g_ben_waiting_days_iv_id,ENT_PT.screen_entry_value,null))) waiting_days
,max(decode(ENT_PT.input_value_id,g_ben_duration_iv_id,ENT_PT.screen_entry_value,null)) duration
,fnd_number.canonical_to_number(max(decode(ENT_PT.input_value_id,g_ben_band1_iv_id,ENT_PT.screen_entry_value,null))) band1
,fnd_number.canonical_to_number(max(decode(ENT_PT.input_value_id,g_ben_band1_rate_iv_id,ENT_PT.screen_entry_value,null))) b1_rate
,fnd_number.canonical_to_number(max(decode(ENT_PT.input_value_id,g_ben_band2_iv_id,ENT_PT.screen_entry_value,null))) band2
,fnd_number.canonical_to_number(max(decode(ENT_PT.input_value_id,g_ben_band2_rate_iv_id,ENT_PT.screen_entry_value,null))) b2_rate
,fnd_number.canonical_to_number(max(decode(ENT_PT.input_value_id,g_ben_band3_iv_id,ENT_PT.screen_entry_value,null))) band3
,fnd_number.canonical_to_number(max(decode(ENT_PT.input_value_id,g_ben_band3_rate_iv_id,ENT_PT.screen_entry_value,null))) b3_rate
,fnd_number.canonical_to_number(max(decode(ENT_PT.input_value_id,g_ben_band4_iv_id,ENT_PT.screen_entry_value,null))) band4
,fnd_number.canonical_to_number(max(decode(ENT_PT.input_value_id,g_ben_band4_rate_iv_id,ENT_PT.screen_entry_value,null))) b4_rate
,max(decode(ENT_PT.input_value_id,g_ben_balance_iv_id,ENT_PT.screen_entry_value,null)) balance
from pay_element_entry_values_f ENT_PT
,pay_element_entry_values_f G
,pay_element_entries_f ENT
,pay_element_links_f EL
where ENT_PT.element_entry_id = ENT.element_entry_id
and G.element_entry_id = ENT.element_entry_id
and G.input_value_id = g_ben_guarantee_id_iv_id
and ENT.assignment_id = p_assignment_id
and EL.element_type_id = g_ben_element_type_id
and EL.element_link_id = ENT.element_link_id
and p_effective_date between
EL.effective_start_date and EL.effective_end_date
and p_effective_date between
ENT.effective_start_date and ENT.effective_end_date
and ENT.effective_start_date = ENT_PT.effective_start_date
and ENT.effective_end_date = ENT_PT.effective_end_date
and ENT.effective_start_date = G.effective_start_date
and ENT.effective_end_date = G.effective_end_date
group by fnd_number.canonical_to_number(G.screen_entry_value), ENT.element_entry_id
order by 2,1;
select max(e.element_type_id)
,max(decode(i.name,'Absence ID',i.input_value_id,null))
,max(decode(i.name,'Guarantee ID',i.input_value_id,null))
,max(decode(i.name,'Guarantee Type',i.input_value_id,null))
,max(decode(i.name,'Waiting Days',i.input_value_id,null))
,max(decode(i.name,'Band Expiry Duration',i.input_value_id,null))
,max(decode(i.name,'Band1',i.input_value_id,null))
,max(decode(i.name,'Band1 Rate',i.input_value_id,null))
,max(decode(i.name,'Band2',i.input_value_id,null))
,max(decode(i.name,'Band2 Rate',i.input_value_id,null))
,max(decode(i.name,'Band3',i.input_value_id,null))
,max(decode(i.name,'Band3 Rate',i.input_value_id,null))
,max(decode(i.name,'Band4',i.input_value_id,null))
,max(decode(i.name,'Band4 Rate',i.input_value_id,null))
,max(decode(i.name,'Balance Name',i.input_value_id,null))
into g_ben_element_type_id
,g_ben_absence_id_iv_id
,g_ben_guarantee_id_iv_id
,g_ben_guarantee_type_iv_id
,g_ben_waiting_days_iv_id
,g_ben_duration_iv_id
,g_ben_band1_iv_id
,g_ben_band1_rate_iv_id
,g_ben_band2_iv_id
,g_ben_band2_rate_iv_id
,g_ben_band3_iv_id
,g_ben_band3_rate_iv_id
,g_ben_band4_iv_id
,g_ben_band4_rate_iv_id
,g_ben_balance_iv_id
from pay_element_types_f e,
pay_input_values_f i
where e.element_name = 'FR_SICK_BENEFIT'
and e.legislation_code = 'FR'
and e.element_type_id = i.element_type_id
and g_absence_calc.IJSS_payment_start_date between e.effective_start_date and e.effective_end_date
and g_absence_calc.IJSS_payment_start_date between i.effective_start_date and i.effective_end_date;
l_bands.DELETE; -- Clearing the l_bands table of the current CAGI
select pabs.absence_attendance_id
, to_number(pabs.abs_information1) parent_absence_id
, pabs.date_start
, pabs.date_end
, pabs.date_end - pabs.date_start + 1 duration
, null effective_start_date
, null effective_end_date
, nvl(abs_information2, 'Y') subrogated
, nvl(pabs.abs_information8, 'N') estimated
, nvl(pwi.inc_information1,'N') work_incident
from per_absence_attendances pabs,
per_all_assignments_f pasg,
per_absence_attendance_types pabt,
per_work_incidents pwi
where pabs.business_group_id = p_business_group_id
and pabs.abs_information_category ='FR_S'
and pabs.date_start <= p_payment_start_date
and pabs.date_end >= p_payment_end_date
and decode(pabs.abs_information_category,'FR_S',to_number(pabs.abs_information6)) = pwi.incident_id(+)
and pabs.person_id = pwi.person_id(+)
and pabs.absence_Attendance_type_id = pabt.absence_attendance_type_id
and pabt.absence_category = 'S'
and pabs.date_start between pasg.effective_start_date and pasg.effective_end_date
and pasg.primary_flag = 'Y'
and pasg.person_id = pabs.person_id
and pasg.assignment_id = p_assignment_id;
select paa.absence_attendance_id
, 0 parent_absence_id
, paa.date_start
, paa.date_end
, paa.date_end - paa.date_start + 1 duration
, null effective_start_date
, null effective_end_date
, nvl(abs_information2, 'Y') subrogated
, nvl(paa.abs_information8,'N') estimated
, nvl(inc.inc_information1,'N') work_incident
from per_absence_attendances paa
, per_work_incidents inc
where paa.absence_attendance_id = p_absence_attendance_id
and paa.abs_information6 = to_char(inc.incident_id(+));
select a.absence_attendance_id
, to_number(a.abs_information1) parent_absence_id
, a.date_start
, a.date_end
, a.date_end - a.date_start + 1 duration
, null effective_start_date
, null effective_end_date
, null estimated
, null work_incident
from per_absence_attendances a,
per_absence_attendances p
where a.abs_information1 = to_char(p_parent_absence_id)
and p.absence_attendance_id = p_parent_absence_id
and a.person_id = p.person_id
and a.date_end <= p_max_end_date
order by a.date_start;
select db.defined_balance_id
from pay_defined_balances db
, pay_balance_dimensions bd
, pay_balance_types bt
where db.balance_type_id = bt.balance_type_id
and db.balance_dimension_id = bd.balance_dimension_id
and bt.balance_name = p_balance_name
and bt.legislation_code = 'FR'
and bd.database_item_suffix = '_ASG_PTD'
and bd.legislation_code = 'FR';
SELECT 'H'
FROM per_absence_attendances pabs_hol,
per_absence_attendance_types pabt,
per_absence_attendances pabs_sick
WHERE pabs_sick.absence_attendance_id = p_absence_id
AND pabs_sick.business_group_id = p_business_group_id
AND pabs_hol.person_id = pabs_sick.person_id
AND pabs_hol.business_group_id = p_business_group_id
AND c_hol_date BETWEEN pabs_hol.date_start AND pabs_hol.date_end
AND pabt.absence_attendance_type_id = pabs_hol.absence_attendance_type_id
AND pabt.absence_category in ('FR_MAIN_HOLIDAY','FR_ADDITIONAL_HOLIDAY','FR_RTT_HOLIDAY') ;
g_overlap.DELETE;
select name,
uom,
substr(screen_entry_value,1,10) screen_entry_value
from pay_element_entry_values_f ev,
pay_input_values_f i
where ev.element_entry_id = p_element_entry_id
and ev.input_value_id + 0 = i.input_value_id
and p_effective_date between
ev.effective_start_date and ev.effective_end_date
and p_effective_date between
i.effective_start_date and i.effective_end_date
order by display_sequence;
select name,
uom,
substr(result_value,1,10) result_value
from pay_run_result_values rv,
pay_input_values_f i
where rv.run_result_id = p_run_result_id
and rv.input_value_id + 0 = i.input_value_id
order by display_sequence;
select name,
uom
from
pay_input_values_f i
where i.element_type_id = p_element_type_id
and p_effective_date between
i.effective_start_date and i.effective_end_date
order by display_sequence;