[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT 'X' dum
FROM pay_element_entries_f pee, pay_element_links_f pel, pay_element_types_f pet
WHERE pee.element_link_id = pel.element_link_id
AND pet.element_type_id = pel.element_type_id
AND pet.element_name = 'FR_SICKNESS_CPAM_INFO'
AND pee.element_entry_id = p_element_entry_id;
SELECT max(e.element_type_id)
,max(decode(i.name,'Payment From Date',i.input_value_id,null))
,max(decode(i.name,'Payment To Date',i.input_value_id,null))
,max(decode(i.name,'Days',i.input_value_id,null))
--,max(decode(i.name,'Subrogated',i.input_value_id,null))
,max(decode(i.name,'Gross Amount',i.input_value_id,null))
,max(decode(i.name,'Net Amount',i.input_value_id,null))
,max(decode(i.name,'Gross Daily Rate',i.input_value_id,null))
,max(decode(i.name,'Net Daily Rate',i.input_value_id,null))
FROM pay_element_types_f e,
pay_input_values_f i
WHERE e.element_name = p_element_type
and e.legislation_code = 'FR'
and e.element_type_id = i.element_type_id
and p_effective_start_date between e.effective_start_date and e.effective_end_date
and p_effective_start_date between i.effective_start_date and i.effective_end_date;
SELECT max(decode(eev.input_value_id,g_info_pymt_frm_dt_iv_id
,to_date(eev.screen_entry_value,'YYYY/MM/DD HH24:MI:SS')
,NULL)) Frm_dt,
max(decode(eev.input_value_id,g_info_pymt_to_dt_iv_id
,to_date(eev.screen_entry_value,'YYYY/MM/DD HH24:MI:SS')
,NULL)) To_dt,
max(decode(eev.input_value_id,g_info_days_iv_id
,to_number(eev.screen_entry_value)
,NULL)) Days,
-- max(decode(eev.input_value_id,g_info_subrogated_iv_id,eev.screen_entry_value,NULL)) Subrogated,
max(decode(eev.input_value_id,g_info_gross_amt_iv_id
,to_number(eev.screen_entry_value)
,NULL)) Gross_Amount,
max(decode(eev.input_value_id,g_info_net_amt_iv_id
,to_number(eev.screen_entry_value)
,NULL)) Net_Amount,
max(decode(eev.input_value_id,g_info_gross_rt_iv_id
,to_number(eev.screen_entry_value)
,NULL)) Gross_Daily_Rate,
max(decode(eev.input_value_id,g_info_net_rt_iv_id
,to_number(eev.screen_entry_value)
,NULL)) Net_Daily_Rate
FROM pay_element_types_f pet, pay_element_entries_f pee,
pay_input_values_f piv, pay_element_entry_values_f eev
WHERE pee.element_entry_id = eev.element_entry_id
and pet.element_type_id = piv.element_type_id
and piv.input_value_id = eev.input_value_id
and pet.element_name = 'FR_SICKNESS_CPAM_INFO'
and pee.element_entry_id = p_element_entry_id;
SELECT pel.element_link_id
FROM pay_element_types_f pet, pay_element_links_f pel, pay_element_links_f pel1
WHERE pet.element_type_id = pel.element_type_id
AND pel.business_group_id = pel1.business_group_id
AND pet.element_type_id = p_element_type_id
AND p_effective_start_date BETWEEN pel.effective_start_date AND pel.effective_end_date
AND pel1.element_link_id = p_source_element_link_id;
SELECT pev.effective_start_date
FROM pay_element_entries_f pee, pay_element_links_f pel, pay_element_types_f pet,
pay_input_values_f piv, pay_element_entry_values_f pev
WHERE pee.element_link_id = pel.element_link_id
AND pet.element_type_id = pel.element_type_id
AND pee.element_entry_id= pev.element_entry_id
AND pet.element_type_id = piv.element_type_id
AND pev.input_value_id = piv.input_value_id
AND piv.name IN ('Payment From Date', 'Payment To Date')
AND pee.assignment_id = p_assignment_id
AND fnd_date.canonical_to_date(pev.screen_entry_value) BETWEEN p_start_date AND p_end_date
AND pet.element_name = 'FR_SICKNESS_CPAM_INFO'
AND pee.element_entry_id <> p_curr_entry_id;
SELECT paa.person_id
, paa.absence_attendance_id
, to_number(paa.abs_information1) parent_absence_id
, paa.date_start date_start
, paa.date_end date_end
, (paa.date_end - paa.date_start + 1) duration
, nvl(decode(paa.abs_information1,NULL,paa.abs_information8,
paa_p.abs_information8),'N') pay_estimate
, fnd_date.canonical_to_date(decode(paa.abs_information1,NULL,paa.abs_information7,
paa_p.abs_information7)) ijss_ineligible_date
FROM per_absence_attendances paa,
per_absence_attendances paa_p,
per_all_people_f pap,
per_all_assignments_f pasg
WHERE pasg.person_id = pap.person_id
AND pap.person_id = paa.person_id
AND paa.abs_information1 = paa_p.absence_attendance_id(+)
AND p_start_date between pasg.effective_start_date and pasg.effective_end_date
AND p_start_date between pap.effective_start_date and pap.effective_end_date
AND ( ( (paa.date_end between p_start_date and p_end_date)
OR (paa.date_start between p_start_date and p_end_date))
OR
( (p_start_date between paa.date_start and paa.date_end)
OR (p_end_date between paa.date_start and paa.date_end)))
AND pasg.assignment_id = p_assignment_id
AND paa.abs_information_category = 'FR_S'
ORDER BY paa.date_start ;
SELECT ptp.start_date, ptp.end_date
FROM per_time_periods ptp, per_all_assignments_f pasg
WHERE pasg.payroll_id = ptp.payroll_id
AND p_start_date between pasg.effective_start_date and pasg.effective_end_date
AND (ptp.start_date BETWEEN p_start_date AND p_end_date
OR ptp.end_date BETWEEN p_start_date AND p_end_date)
AND pasg.assignment_id = p_assignment_id;
SELECT 'X' dum
FROM pay_element_entries_f pee, pay_element_links_f pel, pay_element_types_f pet
WHERE pee.element_link_id = pel.element_link_id
AND pet.element_type_id = pel.element_type_id
AND pet.element_name = 'FR_SICKNESS_CPAM_PROCESS'
AND pee.creator_type IN ('F','H')
AND pee.element_entry_id = p_element_entry_id;
hr_entry_api.insert_element_entry
(
p_effective_start_date => l_effective_start_date,
p_effective_end_date => l_effective_end_date,
--
-- Element Entry Table
--
p_element_entry_id => l_process_element_entry_id,
p_assignment_id => p_assignment_id,
p_element_link_id => rec_element_link.element_link_id,
p_creator_type => 'S',
p_entry_type => p_entry_type,
p_subpriority => to_number(substr(to_char(GREATEST(rec_time_periods.start_date,rec_absences.date_start,rec_input_values.Frm_dt),'J'),4,4)),
p_date_earned => p_date_earned,
-- Element Entry Values Table
--
p_input_value_id1 => g_proc_pymt_frm_dt_iv_id,
p_input_value_id2 => g_proc_pymt_to_dt_iv_id,
--p_input_value_id3 => g_proc_subrogated_iv_id,
p_input_value_id4 => g_proc_gross_rt_iv_id,
p_input_value_id5 => g_proc_net_rt_iv_id,
p_input_value_id6 => g_proc_days_iv_id,
p_entry_value1 => GREATEST(rec_time_periods.start_date,rec_absences.date_start,rec_input_values.Frm_dt),
p_entry_value2 => LEAST(rec_time_periods.end_date,rec_absences.date_end, rec_input_values.To_dt),
--p_entry_value3 => hr_general.decode_lookup('YES_NO', rec_input_values.Subrogated),
p_entry_value4 => rec_input_values.Gross_daily_rate,
p_entry_value5 => rec_input_values.Net_daily_rate,
p_entry_value6 => (LEAST(rec_time_periods.end_date,rec_absences.date_end, rec_input_values.To_dt) -
GREATEST(rec_time_periods.start_date,rec_absences.date_start,rec_input_values.Frm_dt)) +1,
p_entry_information_category=> l_entry_information_category,
p_entry_information1 => p_element_entry_id,
p_entry_information2 => rec_absences.absence_attendance_id
);
hr_entry_api.insert_element_entry
(
p_effective_start_date => l_effective_start_date,
p_effective_end_date => l_effective_end_date,
--
-- Element Entry Table
--
p_element_entry_id => l_process_element_entry_id,
p_assignment_id => p_assignment_id,
p_element_link_id => rec_element_link.element_link_id,
p_creator_type => 'S',
p_entry_type => p_entry_type,
p_subpriority => to_number(substr(to_char(GREATEST(rec_absences.date_start,rec_input_values.Frm_dt),'J'),4,4)),
p_date_earned => p_date_earned,
-- Element Entry Values Table
--
p_input_value_id1 => g_proc_pymt_frm_dt_iv_id,
p_input_value_id2 => g_proc_pymt_to_dt_iv_id,
--p_input_value_id3 => g_proc_subrogated_iv_id,
p_input_value_id4 => g_proc_gross_rt_iv_id,
p_input_value_id5 => g_proc_net_rt_iv_id,
p_input_value_id6 => g_proc_days_iv_id,
p_entry_value1 => GREATEST(rec_absences.date_start,rec_input_values.Frm_dt),
p_entry_value2 => LEAST(rec_absences.date_end, rec_input_values.To_dt),
--p_entry_value3 => hr_general.decode_lookup('YES_NO', rec_input_values.Subrogated),
p_entry_value4 => rec_input_values.Gross_daily_rate,
p_entry_value5 => rec_input_values.Net_daily_rate,
p_entry_value6 => (LEAST(rec_absences.date_end, rec_input_values.To_dt) - GREATEST(rec_absences.date_start,rec_input_values.Frm_dt)) +1,
p_entry_information_category=> l_entry_information_category,
p_entry_information1 => p_element_entry_id,
p_entry_information2 => rec_absences.absence_attendance_id
);
UPDATE PAY_ELEMENT_ENTRY_VALUES_F SET screen_entry_value = rec_input_values.Days
WHERE element_entry_id = p_element_entry_id
AND input_value_id = g_info_days_iv_id;
PROCEDURE CPAM_INFO_UPDATE(
p_effective_start_date IN DATE
,p_effective_end_date IN DATE
,p_element_entry_id IN NUMBER
,p_date_earned IN DATE
,p_entry_type_o IN VARCHAR2
,p_effective_start_date_o IN DATE
,p_assignment_id_o IN NUMBER
,p_element_link_id_o IN NUMBER
,p_date_earned_o IN DATE
) IS
--
rec_info_entry c_info_entry%ROWTYPE;
hr_utility.set_location('Leaving : pay_fr_cpam_preprocessor.cpam_info_update' , 10);
CPAM_INFO_DELETE(
p_element_entry_id => p_element_entry_id,
p_element_link_id_o => p_element_link_id_o,
p_effective_start_date_o => p_effective_start_date_o,
p_assignment_id_o => p_assignment_id_o,
p_datetrack_mode => 'ZAP'
);
END CPAM_INFO_UPDATE;
PROCEDURE CPAM_INFO_DELETE(
p_element_entry_id IN NUMBER,
p_element_link_id_o IN NUMBER,
p_effective_start_date_o IN DATE,
p_assignment_id_o IN NUMBER ,
p_datetrack_mode IN VARCHAR2
) IS
rec_info_entry c_info_entry%ROWTYPE;
SELECT pee.element_entry_id
FROM pay_element_entries_f pee
WHERE pee.entry_information_category = 'FR_CPAM PROCESS INFORMATION'
AND pee.assignment_id = p_assignment_id
AND pee.entry_information1 = to_char(p_element_entry_id);
hr_utility.set_location('Leaving : pay_fr_cpam_preprocessor.cpam_info_delete' , 10);
hr_utility.trace(' Preproc :: DT DELETE MODE ='||p_datetrack_mode||' for INFO entry'||p_element_entry_id||' eff start date='||p_effective_start_date_o);
hr_entry_api.delete_element_entry(
p_dt_delete_mode => p_datetrack_mode,
p_session_date => p_effective_start_date_o,
p_element_entry_id => rec_linked_process_entries.element_entry_id);
hr_utility.trace(' Preproc : DELETED entry='||rec_linked_process_entries.element_entry_id);
END CPAM_INFO_DELETE;