[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
updates
11 Oct 05 npershad 4621910 Fixed two problems with date conversion,
one in the cursor csr_existing_entries
and twice when calling insert_element_entry
09 Feb 06 kthampan 4891953 Fixed performance bug.
23 Aug 06 Kthampan 5482199 Change from per_people_f and per_assignments_f
to per_all_people_f and per_all_assignments_f
19 Sep 06 Kthampan 5547703 Amend pad_control to call generate_payments
with insert-mode if absence is > 0 and
also change csr_check_if_existing_entries
not to reference from per_absence_attendances
table
12 Mar 07 Kthampan 5925760 Amend the cursor csr_existing_entries to
use ssp_pad_pkg.c_week_commencing_name
*/
--------------------------------------------------------------------------------
g_package varchar2(33) := ' ssp_pad_pkg.'; -- Global package name
select absence.absence_attendance_id,
absence.date_start,
nvl (absence.date_end, hr_general.end_of_time) date_end,
absence.date_notification,
absence.accept_late_notification_flag
from per_absence_attendances ABSENCE
where absence.maternity_id = p_maternity_id
and absence.absence_attendance_id =
(select min(paa_tab.absence_attendance_id)
from per_absence_attendances paa_tab
where paa_tab.maternity_id = absence.maternity_id);
select maternity.person_id,
maternity.due_date,
ssp_pad_pkg.MATCHING_WEEK_OF_ADOPTION (MATCHING_DATE) MW,
MATCHING_DATE MATCHING_DATE,
PLACEMENT_DATE PLACEMENT_DATE,
DISRUPTED_PLACEMENT_DATE DISRUPTED_PLACEMENT_DATE,
maternity.maternity_id,
maternity.actual_birth_date,
maternity.live_birth_flag,
maternity.start_date_with_new_employer,
maternity.MPP_start_date APP_start_date,
maternity.notification_of_birth_date,
maternity.start_date_maternity_allowance,
maternity.pay_SMP_as_lump_sum pay_PAD_as_lump_sum,
person.date_of_death,
service.date_start,
nvl (service.final_process_date, hr_general.end_of_time) FINAL_PROCESS_DATE
from ssp_maternities MATERNITY,
per_all_people_f PERSON,
per_periods_of_service SERVICE
where person.person_id = maternity.person_id
and person.person_id = service.person_id
and maternity.maternity_id = p_maternity_id
and service.date_start between person.effective_start_date
and person.effective_end_date
and service.date_start = (select max(serv.date_start)
from per_periods_of_service serv
where serv.person_id = person.person_id);
select 1
from ssp_maternities
where person_id = p_person_id;
select average_earnings_amount
from ssp_earnings_calculations
where person_id = person.person_id
and effective_date = l_effective_date;
select count (*)
from per_absence_attendances
where person_id = person.person_id
and maternity_id = p_maternity_id
and absence_attendance_id =
(select min(paa_tab.absence_attendance_id)
from per_absence_attendances paa_tab
where paa_tab.maternity_id = p_maternity_id);
select /*+ ORDERED use_nl(paa,paaf,etype,entry) */
entry.element_entry_id,
entry.effective_start_date
from per_all_assignments_f PAAF,
pay_element_types_f ETYPE,
pay_element_entries_f ENTRY
where PAAF.person_id = person.person_id
and ETYPE.element_name = c_PAD_element_name
and ETYPE.legislation_code = 'GB'
and ENTRY.element_type_id = ETYPE.element_type_id
and ENTRY.creator_type = c_PAD_creator_type
and ENTRY.creator_id = p_maternity_id
and ENTRY.assignment_id = PAAF.assignment_id
and not exists (
--
-- Do not select entries which have already had reversal action
-- taken against them because they are effectively cancelled out.
--
select 1
from pay_element_entries_f ENTRY2
where entry.element_entry_id = entry2.target_entry_id
and entry.assignment_id = entry2.assignment_id)
--
and not exists (
--
-- Do not select reversal entries
--
select 1
from pay_element_links_f LINK,
pay_element_types_f TYPE
where link.element_link_id = entry.element_link_id
and entry.effective_start_date between link.effective_start_date and link.effective_end_date
and link.element_type_id = type.element_type_id
and link.effective_start_date between type.effective_start_date and type.effective_end_date
and type.element_name = c_PAD_Corr_element_name);
select stoppage_id
from ssp_stoppages
where user_entered <>'Y'
and override_stoppage <> 'Y'
and maternity_id = p_maternity_id;
select actual_termination_date
from per_periods_of_service
where person_id = person.person_id
-- 3516539 Added condition to obtain termination date for latest assignment
and period_of_service_id = (select max(period_of_service_id)
from per_periods_of_service ppos
where ppos.person_id = person.person_id);
select 1
from per_periods_of_service
where person_id = person.person_id
and date_start <= ssp_pad_pkg.continuous_employment_date
(person.matching_date)
and nvl (actual_termination_date, hr_general.end_of_time)
>= person.MW;
select ppf.date_of_death
from per_all_people_f ppf
where ppf.person_id = person.person_id
and ppf.date_of_death is not null;
select entry.element_entry_id,
entry.element_link_id,
entry.assignment_id,
entry.effective_start_date,
entry.effective_end_date,
-- if in future we get two different rates then a decode can be added here
l_high_rate RATE,
/*to_date (ssp_smp_support_pkg.value
(entry.element_entry_id,
ssp_pad_pkg.c_week_commencing_name),
'DD-MON-YYYY') WEEK_COMMENCING,*/
fnd_date.chardate_to_date(ssp_smp_support_pkg.value
(entry.element_entry_id,
ssp_pad_pkg.c_week_commencing_name)) WEEK_COMMENCING,
to_number(ssp_smp_support_pkg.value (entry.element_entry_id,
ssp_pad_pkg.c_amount_name)) AMOUNT,
to_number(ssp_smp_support_pkg.value (entry.element_entry_id,
ssp_pad_pkg.c_recoverable_amount_name)) RECOVERABLE_AMOUNT
from pay_element_entries_f ENTRY,
per_all_assignments_f asg
where creator_type = c_PAD_creator_type
and creator_id = p_maternity_id
and asg.person_id = person.person_id
and asg.assignment_id = entry.assignment_id
and entry.effective_start_date between asg.effective_start_date
and asg.effective_end_date
and not exists (
--
-- Do not select entries which have already had reversal action taken
-- against them because they are effectively cancelled out.
--
select 1
from pay_element_entries_f ENTRY2
where entry.element_entry_id= entry2.target_entry_id
and entry.assignment_id = entry2.assignment_id)
--
and not exists (
--
-- Do not select reversal entries
--
select 1
from pay_element_links_f LINK,
pay_element_types_f TYPE
where link.element_link_id = entry.element_link_id
and entry.effective_start_date between link.effective_start_date
and link.effective_end_date
and link.element_type_id = type.element_type_id
and link.effective_start_date between type.effective_start_date
and type.effective_end_date
and type.element_name = c_PAD_Corr_element_name);
select count (*)
from per_absence_attendances
where person_id = person.person_id
and maternity_id = p_maternity_id
and absence_attendance_id =
(select min(paa_tab.absence_attendance_id)
from per_absence_attendances paa_tab
where paa_tab.maternity_id = p_maternity_id);
select stoppage_id
from ssp_stoppages
where user_entered <>'Y'
and override_stoppage <> 'Y'
and maternity_id = p_maternity_id;
and ssp_smp_pkg.g_smp_update = 'N')
or (old_entry.effective_start_date
= hypothetical_entry.effective_start_date (entry_number)
and old_entry.week_commencing
= hypothetical_entry.week_commencing (entry_number)
and not hypothetical_entry.stopped (entry_number) = 'TRUE'
and ssp_smp_pkg.g_smp_update = 'Y'));
hr_utility.trace (l_proc||' unprocessed - update it');
hr_entry_api.update_element_entry (
p_dt_update_mode => 'CORRECTION',
p_session_date => old_entry.effective_start_date,
p_element_entry_id => old_entry.element_entry_id,
p_input_value_id1 => g_PAD_element.rate_id,
p_input_value_id2 => g_PAD_element.amount_id,
p_input_value_id3 => g_PAD_element.recoverable_amount_id,
p_entry_value1=> hypothetical_entry.rate (entry_number),
p_entry_value2=> hypothetical_entry.amount(entry_number),
p_entry_value3=>
hypothetical_entry.recoverable_amount (entry_number));
hr_entry_api.delete_element_entry (
p_dt_delete_mode => 'ZAP',
p_session_date => old_entry.effective_start_date,
p_element_entry_id => old_entry.element_entry_id);
' Inserting CORRECTION entry for week commencing ' ||
to_char (old_entry.week_commencing));
hr_entry_api.insert_element_entry (
p_effective_start_date=> old_entry.effective_start_date,
p_effective_end_date => old_entry.effective_end_date,
p_element_entry_id => l_dummy,
p_target_entry_id => old_entry.element_entry_id,
p_assignment_id => old_entry.assignment_id,
p_element_link_id => old_entry.element_link_id,
p_creator_type => c_PAD_creator_type,
p_creator_id => p_maternity_id,
p_entry_type => c_PAD_entry_type,
p_input_value_id1=> g_PAD_correction_element.rate_id,
p_input_value_id2=> g_PAD_correction_element.week_commencing_id,
p_input_value_id3=> g_PAD_correction_element.amount_id,
p_input_value_id4=> g_PAD_correction_element.recoverable_amount_id,
p_entry_value1=> old_entry.rate,
-- p_entry_value2=> old_entry.week_commencing,
-- p_entry_value2 => to_char(old_entry.week_commencing,'DD-MON-YYYY'),
p_entry_value2 => fnd_date.date_to_chardate(old_entry.week_commencing),
p_entry_value3=> old_entry.amount * -1,
p_entry_value4=> old_entry.recoverable_amount * -1);
hr_utility.trace('Deleting an absence so don''t insert entries');
hr_entry_api.insert_element_entry (
p_effective_start_date =>
hypothetical_entry.effective_start_date (new_entry),
p_effective_end_date =>
hypothetical_entry.effective_end_date (new_entry),
p_element_entry_id => l_dummy,
p_assignment_id => hypothetical_entry.assignment_id (new_entry),
p_element_link_id => hypothetical_entry.element_link_id (new_entry),
p_creator_type => c_PAD_creator_type,
p_creator_id => p_maternity_id,
p_entry_type => c_PAD_entry_type,
p_input_value_id1 => g_PAD_element.rate_id,
p_input_value_id2 => g_PAD_element.week_commencing_id,
p_input_value_id3 => g_PAD_element.amount_id,
p_input_value_id4 => g_PAD_element.recoverable_amount_id,
p_entry_value1 => hypothetical_entry.rate (new_entry),
-- p_entry_value2 => hypothetical_entry.week_commencing (new_entry),
p_entry_value2 => to_char(hypothetical_entry.week_commencing(new_entry),'DD-MON-YYYY'),
p_entry_value3 => hypothetical_entry.amount (new_entry),
p_entry_value4 =>
hypothetical_entry.recoverable_amount (new_entry));
select 1
from ssp_stoppages STP,
ssp_withholding_reasons WRE
where stp.override_stoppage <> 'Y'
--
-- and the stoppage ovelaps the period or the stoppage is for
-- death and is prior to the period
--
and ((wre.reason <> employee_died
and stp.withhold_from <= p_end_date
and nvl (stp.withhold_to, hr_general.end_of_time)
>= p_start_date)
--
or (wre.reason = employee_died
and stp.withhold_from < p_start_date))
--
and stp.maternity_id = p_maternity_id
and stp.reason_id = wre.reason_id;
select meaning
from hr_lookups
where lookup_type = 'SPP_RATES'
and lookup_code = p_rate_band;
select 1
from ssp_maternities
where maternity_id = p_maternity_id;
select entry.element_entry_id,
entry.effective_start_date
from per_absence_attendances PAA,
per_all_assignments_f PAAF,
pay_element_entries_f entry
where PAA.maternity_id = p_maternity_id
and PAAF.person_id = PAA.person_id
and entry.creator_type = 'M'
and entry.creator_id = p_maternity_id
and entry.assignment_id = paaf.assignment_id;
select count(*)
from ssp_maternities mat,
per_absence_attendances ab
where mat.maternity_id = p_maternity_id
and ab.person_id = mat.person_id
and ab.maternity_id = mat.maternity_id;
hr_entry_api.delete_element_entry (
p_dt_delete_mode => 'ZAP',
p_session_date => obsolete.effective_start_date,
p_element_entry_id => obsolete.element_entry_id);