[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
27 Nov 95 N Simpson Removed temporary code from last update
and changed criteria for selecting
payroll periods in get_entry_details
30 Nov 95 N Simpson Reversed comparison between
p_last_process_date and p_date_earned
in get_entry_details cursor
csr_next_available_period. It was,
incorrectly, ignoring date earned
because date earned was always less than
the end of time.
5 dec 95 N Simpson Added function average_earnings_error.
6 Dec 95 N Simpson Added reference to ssp_smp_support_pkg.
reason_for_no_earnings
19 Jan 96 N Simpson Added functions start_of_week and
end_of_week.
08-Jan-98 RThirlby 608724 30.24 Altered non-translateable MON format
for dates to MM.
Parameter p_deleting added to procedure
recalculate_ssp_and_smp. If p_deleting
logic to get rid of orphaned stoppages.
Part of SMP entries problem.
24-Mar-98 RThirlby 563202 30.25 Performance fix to csr_stoppage.
19-AUG-98 A.Myers 701750 30.26 Amended cursors for affected rows, as
the "where not exists" did not work.
This existence checking is now done in
SSP_SSP_PKG/SSP_SMP_PKG on the actual
row insert. Added p_deleting to SMP call
to SMP_control.
06-JAN-2000 ILeath 30.27/ Add call to stop_if_director and
110.7 check_payroll_installed within
recalculate_ssp_and_smp. To
ensure that message within
average_earnings_error is always
set. Also default if null
message to 'Cannot derive new
emps pay'.
12-APR-2000 A.Mills 30.28 Changed NI_Lower_Earnings_Limit
=110.8 function to retrieve the Weekly
LEL figure from the Global
'NI_WEEKLY_LEL' rather than the
User Table, which becomes obsolete
as at 6-APR-2000. Bug 871095.
05-DEC-2001 GButler 115.7 Added new procedure update_ssp_smp_entries
to allow automatic recalculation of SSP/
SMP entries over tax year end following
legislative updates to the corresponding
SSP/SMP rates. Procedure is called from
perleggb.sql script after seed data
install completed
31-DEC-2001 ABHADURI 115.8 Added a condition to inform user that
employee has been re-hired within 8 weeks.
15-JAN-2002 GBUTLER 115.9 Updated update_ssp_smp_entries procedure
to exclude all terminated employees whose
final process date has already passed
02-FEB-2002 GBUTLER 115.10 Bug 2189501. Updated SSP and SMP queries
to better handle employee terminations and
exclude deceased employees for SMP. Also
added exceptions to handle cases where no
element entries can be found in new tax year
05-FEB-2002 GBUTLER 115.12 Updated queries to retrieve SSP/SMP entries in
new tax year so that entries retrieved relate to
people who would be retrieved by the main SSP/SMP
queries
14-FEB-2002 GBUTLER 115.13 Added close statements to cursors
26-FEB-2002 GBUTLER 115.14 Altered update_ssp_smp_entries by adding sub-blocks
into loops to detect errors as they occur but not to
halt update process because of them. Added p_update_
error boolean flag to alert user to absences that
could not be updated
02-DEC-2002 ABLINKO 115.16 Bug 2690305. New SAP/SPP functionality
10-DEC-2002 GBUTLER 115.17 Bug 2702282. Commented out section for SMP rate updates
for TYE 2002/3
17-DEC-2002 ABLINKO 115.18 gscc fix
09-jan-2003 vmkhande 115.19 bug 2706844
Effective start date now retuned from
get_entry_details will be the max of
assignment start date and payroll
period start date.
24-JAN-2003 GButler 115.20 nocopy fixes
06-MAR-2003 GButler 115.21 Bug 1681054. Change to csr_assignment cursor in
get_entry_details to exclude benefits assignments
24-OCT-2003 ABlinko 115.22 Replaced hardcoded SATURDAY and SUNDAY references
08-DEC-2003 RMakhija 115.24 Uncommented SMP element entry update when rate
changes in next tax year. Also added similar
Functionality for SAP, SPP Birth and SPP Adoption.
17-DEC-2003 RMakhija 115.25 Added detection of SMP/SAP/SPP standard rate
changes and SMP Higher Rate changes to auto
update element entries in next tax year.
12-FEB-2004 RMakhija 115.26 Bug 3437026. Updated csr_affected_leave
cursor in update_ssp_smp_entries procedure.
02-MAR-2004 ABlinko 115.27 Bug 3456918 - Added rtrim when deriving
l_saturday_txt and l_sunday_txt
21-MAR-2006 Kthampan 115.28 Bug 5105039 - Passing the correct date when
fetching element link.
31-JUL-2006 Kthampan 115.29 Bug 5346648 - Update procedure get_entry_details
to re-fetch the effective_start/end date again
if the assignment start date is > the
period start_date.
23-AUG-06 KThampan 115.30 Bug 5482199 - Statutory changes for 2007
115.31 Change cursor csr_payroll_period to check
for period.cut_off_date when payment is not
in lump sum.
19-SEP-06 KThampan 115.33 Bug 5547703 - Amend recalculate_SSP_and_SMP
only to delete stoppage when absence
record = 0
20-OCT-06 KThampan 115.34 Amend cursor csr_payroll_period to use
p_date_earned <= period.end_date instead of
p_date_earned <= nvl(cut_off_date,end_date)
09-DEC-06 KThampan 115.35 Amend procedure recalculate_SSP_and_SMP to only
process rows within the same session id
12-MAR-07 KThampan 115.36 Added distinct when select input id for SSP
cursor csr_first_new_ssp_entry
21-MAR-07 KThampan 115.37 Amended cursor csr_affected_absences and
csr_affected_leave to check for period of
service id
20-FEB-07 pbalu 115.38 Added Multi threaded update_ssp_smp_entries
as part of 6800788.
27-FEB-07 pbalu 115.39 Error flag is not set in the multithreaded
update_ssp_smp_entries
25-AUG-08 pbalu 115.40 Changed the cursor csr_payroll_period to
take care of positive cutoff period for bug 6959669
*/
--------------------------------------------------------------------------------
g_package constant varchar2 (31) := 'ssp_smp_support_pkg.';
-- Selects an entry value for a given
-- element entry and named input value
--
select entry.screen_entry_value,
inp.uom,
ele.input_currency_code,
inp.input_value_id
from pay_element_entry_values_f entry,
pay_input_values_f inp,
pay_element_types_f ele
where entry.element_entry_id = p_element_entry_id
and inp.name = p_input_value_name
and entry.input_value_id = inp.input_value_id
and ele.element_type_id = inp.element_type_id
and ele.effective_start_date <= inp.effective_end_date
and ele.effective_end_date >= inp.effective_start_date
and inp.effective_start_date <= entry.effective_end_date
and inp.effective_end_date >= entry.effective_start_date;
select to_number(ni.global_value) LEL
from ff_globals_f ni
where ni.global_name = 'NI_WEEKLY_LEL'
and ni.business_group_id is null
and ni.legislation_code = 'GB'
and p_effective_date between ni.effective_start_date
and ni.effective_end_date;
select 1
from pay_run_results
where source_id = p_element_entry_id
and status <> 'U';
select input_value_id
from pay_input_values_f
where name = p_input_value_name
and element_type_id = p_element_type_id;
select reason_id
from ssp_withholding_reasons
where upper (reason) = upper (p_reason)
and element_type_id = p_element_type_id;
select 1
from ssp_stoppages
where ((p_absence_attendance_id is not null
and p_absence_attendance_id = absence_attendance_id)
or (p_maternity_id is not null
and p_maternity_id = maternity_id))
and reason_id||null = p_reason_id
and override_stoppage = 'Y';
-- Find all maternity ids which have been inserted by row triggers
-- because a change to one of their SMP parameters occurred. Exclude
-- rows which are already being processed (because the process may
-- cause the row triggers to fire and call this procedure recursively),
-- and rows which are duplicated.
--
select tar1.maternity_id, nvl(tar1.p_deleting, 'N') l_deleting
from ssp_temp_affected_rows TAR1
where tar1.maternity_id is not null
--and nvl (tar1.locked, 'NULL') <> 'Y'
and tar1.locked = to_char(userenv('sessionid'))
for update;
select leave_type
from ssp_maternities
where maternity_id = p_maternity_id;
-- Find all PIW IDs which have been inserted by row triggers
-- because a change to one of their SSP parameters occurred.
-- Exclusion of duplicates now done in ssp_ssp_pkg.SSP-Control as
-- the previous "not exists" did not work... the data is already
-- selected before they can be updated.
--
select tar1.PIW_id, nvl(tar1.locked,'NULL') locked
from ssp_temp_affected_rows TAR1
where tar1.PIW_id is not null
-- and nvl (tar1.locked,'NULL') <> 'Y'
and tar1.locked = to_char(userenv('sessionid'))
for update;
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;
row_deleted exception;
pragma exception_init (row_deleted, -8006);
ssp_ssp_pkg.update_linked_absence_IDs;
update ssp_temp_affected_rows
set locked = 'Y'
where current of csr_affected_PIWs;
update ssp_temp_affected_rows
set locked = 'Y'
where current of csr_affected_maternities;
delete ssp_stoppages
where maternity_id = each_maternity.maternity_id;
delete ssp_temp_affected_rows
where locked = to_char(userenv('sessionid'))
or locked is null
or locked not in (select to_char(AUDSID) from v$session);
when mutating_table or row_deleted then
--
-- If we get a mutating table restriction then we must be firing this code
-- recursively (eg the user deleted an absence which cascaded to delete the
-- stoppages for it; both the absence deletion and the stoppage deletion
select effective_date
from fnd_sessions
where session_id = userenv ('sessionid');
select assignment_id,
payroll_id
from per_all_assignments_f
where person_id = p_person_id
and primary_flag = 'Y'
and assignment_type = 'E'
and least(p_date,p_lsp) between effective_start_date and effective_end_date;
select min(effective_start_date),
max(nvl(effective_end_date,hr_general.end_of_time))
from per_all_assignments_f
where assignment_id = p_asg_id
and primary_flag = 'Y'
and assignment_type = 'E'
and payroll_id = p_pay_id;
select nvl(max(period.start_date),to_date('01/01/0001','DD/MM/YYYY'))
from per_time_periods period
where period.payroll_id = p_payroll_id
and period.prd_information_category = 'GB'
and period.prd_information1 = 'Closed';
select min(period.start_date),
min(nvl(period.end_date,hr_general.end_of_time))
from per_time_periods period
where period.payroll_id = l_payroll_id
and period.start_date > l_closed_period
--6959669 begin
-- and nvl(period.cut_off_date,period.end_date) <= l_last_process_date
and least(nvl(period.cut_off_date,period.end_date),period.end_date) <= l_last_process_date
--6959669 end
and (nvl(period.cut_off_date,period.end_date) >= l_effective_date
or ( l_effective_date > l_last_process_date
and l_last_process_date between period.start_date and period.end_date))
and ( l_pay_as_lump_sum = 'Y'
or( l_pay_as_lump_sum = 'N'
and ( p_date_earned <= period.end_date
or l_last_process_date < p_date_earned)));
procedure update_ssp_smp_entries (P_UPDATE_ERROR OUT NOCOPY boolean) is
cursor csr_affected_absences is
select nvl(paa.linked_absence_id,paa.absence_attendance_id) as absence_id,
paa.person_id,
nvl(paa.date_start,paa.sickness_start_date) as absence_start_date,
nvl(paa.date_end,paa.sickness_end_date) as absence_end_date
from per_absence_attendances paa,
per_absence_attendance_types paat
where paa.absence_attendance_type_id = paat.absence_attendance_type_id
and paat.absence_category = 'S'
and
/* SSP absences which span tax years or start in old tax year and are open-ended */
(
((paa.date_start between hr_gbnicar.uk_tax_yr_start(sysdate) and hr_gbnicar.uk_tax_yr_end(sysdate))
and (paa.date_end > hr_gbnicar.uk_tax_yr_end(sysdate) or paa.date_end is null)
)
or
((paa.sickness_start_date between hr_gbnicar.uk_tax_yr_start(sysdate) and hr_gbnicar.uk_tax_yr_end(sysdate))
and (paa.sickness_end_date > hr_gbnicar.uk_tax_yr_end(sysdate) or paa.sickness_end_date is null)
)
or
/* SSP absences which start in the new tax year */
(
paa.date_start >= hr_gbnicar.uk_tax_yr_end(sysdate)
or paa.date_projected_start >= hr_gbnicar.uk_tax_yr_end(sysdate)
)
)
/* Do not retrieve terminated employees whose actual termination dates have passed or are null */
and not exists
( select 1
from per_all_people_f ppf,
per_person_types ppt,
per_periods_of_service pps
where ppf.person_id = pps.person_id
and ppt.person_type_id = ppf.person_type_id
and ppt.system_person_type = 'EX_EMP'
and nvl(pps.actual_termination_date,to_date('01/01/0001','DD/MM/YYYY')) <= sysdate
and ppf.person_id = paa.person_id
and pps.date_start = (select max(date_start)
from per_periods_of_service pos
where pos.person_id = pps.person_id)
and ppf.effective_start_date >= pps.date_start
and pps.date_start <= paa.sickness_start_date)
order by nvl(paa.linked_absence_id,paa.absence_attendance_id);
select paa.maternity_id,
paa.person_id,
paa.date_start,
paa.date_end,
paa.date_projected_start,
paa.date_projected_end
from per_absence_attendances paa,
ssp_maternities mat
where paa.maternity_id = mat.maternity_id
and nvl(mat.leave_type, 'MA') = p_leave_type
and
/* SMP absences which span tax years or start in old tax year and are open-ended */
(
((paa.date_start between hr_gbnicar.uk_tax_yr_start(sysdate) and hr_gbnicar.uk_tax_yr_end(sysdate))
and (paa.date_end > hr_gbnicar.uk_tax_yr_end(sysdate) or paa.date_end is null)
)
or
((paa.date_projected_start between hr_gbnicar.uk_tax_yr_start(sysdate) and hr_gbnicar.uk_tax_yr_end(sysdate))
and (paa.date_projected_end > hr_gbnicar.uk_tax_yr_end(sysdate) or paa.date_projected_end is null)
and paa.date_start IS NULL -- use projected dates only when actual dates not available
)
or
/* SMP absences which start in the new tax year */
(
paa.date_start >= hr_gbnicar.uk_tax_yr_end(sysdate)
or (paa.date_projected_start >= hr_gbnicar.uk_tax_yr_end(sysdate)
and paa.date_start IS NULL) -- use projected dates only when actual dates not available
)
)
/* Do not retrieve employees whose final process dates have passed */
and not exists
( select 1
from per_all_people_f ppf,
per_person_types ppt,
per_periods_of_service pps
where ppf.person_id = pps.person_id
and ppt.person_type_id = ppf.person_type_id
and ppt.system_person_type = 'EX_EMP'
and pps.final_process_date <= sysdate
and ppf.person_id = paa.person_id
and pps.date_start = (select max(date_start)
from per_periods_of_service pos
where pos.person_id = pps.person_id)
and ppf.effective_start_date >= pps.date_start
and pps.date_start <= paa.date_start)
/* Do not retrieve employees who are deceased */
and not exists
( select 1
from per_all_people_f ppf,
per_periods_of_service pps
where ppf.person_id = pps.person_id
and pps.leaving_reason = 'D'
and ppf.person_id = paa.person_id)
order by paa.maternity_id;
l_update_error boolean := FALSE;
/* Function to check if SSP entries in new tax year have already been updated */
/* with new SSP rates and recalculated - returns TRUE if so, FALSE if not */
function ssp_entries_already_updated return boolean is
cursor csr_new_ssp_rate is
select piv.default_value
from pay_input_values_f piv,
pay_element_types_f petf
where petf.element_type_id = piv.element_type_id
and piv.name = 'Rate'
and petf.element_name = 'Statutory Sick Pay'
and piv.effective_start_date > hr_gbnicar.uk_tax_yr_end(sysdate);
select peev1.screen_entry_value
from pay_element_entry_values_f peev1,
pay_element_entry_values_f peev2,
pay_input_values_f piv,
pay_element_entries_f peef,
pay_element_links_f pelf,
pay_element_types_f petf
where piv.input_value_id = peev1.input_value_id
and peev1.element_entry_id = peev2.element_entry_id
and peev1.element_entry_id = peef.element_entry_id
and peef.element_link_id = pelf.element_link_id
and pelf.element_type_id = petf.element_type_id
and piv.name = 'Rate'
and petf.element_name = 'Statutory Sick Pay'
and piv.effective_start_date > hr_gbnicar.uk_tax_yr_end(sysdate)
and peev2.element_entry_value_id =
(select peev3.element_entry_value_id
from pay_element_entry_values_f peev3
where input_value_id =
(select distinct input_value_id
from pay_input_values_f piv,
pay_element_types_f petf
where petf.element_type_id = piv.element_type_id
and petf.element_name = 'Statutory Sick Pay'
and piv.name = 'From')
and peev3.screen_entry_value >
fnd_date.date_to_canonical(hr_gbnicar.uk_tax_yr_end(sysdate))
/* Retrieve only those entries that will be retrieved by main SSP query */
and peev3.element_entry_id in
( select peef1.element_entry_id
from pay_element_entries_f peef1,
per_all_assignments_f paf,
per_all_people_f ppf,
per_person_types ppt,
per_periods_of_service pps
where peef1.assignment_id = paf.assignment_id
and paf.person_id = ppf.person_id
and ppf.person_id = pps.person_id
and ppt.person_type_id = ppf.person_type_id
and peev3.screen_entry_value between fnd_date.date_to_canonical(ppf.effective_start_date)
and fnd_date.date_to_canonical(ppf.effective_end_date)
and peev3.screen_entry_value between fnd_date.date_to_canonical(paf.effective_start_date)
and fnd_date.date_to_canonical(paf.effective_end_date)
and ppt.system_person_type = 'EMP'
and nvl(pps.actual_termination_date,to_date('31/12/4712','DD/MM/YYYY')) >= sysdate
)
and rownum = 1);
hr_utility.trace('Entering ssp_entries_already_updated function');
end ssp_entries_already_updated;
/* Function to check if SMP entries in new tax year have already updated */
/* with new SMP rates and recalculated - returns TRUE if so, FALSE if not */
function smp_rate_changed return boolean is
/* Check for SMP rates beginning on or after April 1st */
cursor csr_new_smp_rate is
select petf.element_information10, petf.element_information9, petf.element_information16
from pay_element_types_f petf
where petf.element_name = 'Statutory Maternity Pay'
and petf.effective_start_date >= trunc(hr_gbnicar.uk_tax_yr_end(sysdate)) - 4
order by effective_start_date;
select peev1.screen_entry_value
from pay_element_entry_values_f peev1,
pay_element_entry_values_f peev2,
pay_element_entry_values_f peev3,
pay_input_values_f piv,
pay_element_entries_f peef,
pay_element_links_f pelf,
pay_element_types_f petf
where piv.input_value_id = peev1.input_value_id
and peev1.element_entry_id = peev2.element_entry_id
and peev1.element_entry_id = peev3.element_entry_id
and peev1.element_entry_id = peef.element_entry_id
and peef.element_link_id = pelf.element_link_id
and pelf.element_type_id = petf.element_type_id
and piv.name = 'Amount'
and petf.element_name = 'Statutory Maternity Pay'
and petf.effective_start_date > hr_gbnicar.uk_tax_yr_end(sysdate) - 5
-- Time restriction - only rows after tax year end
and peev2.input_value_id =
(select input_value_id
from pay_input_values_f piv
where petf.element_type_id = piv.element_type_id
and upper(piv.name) = upper('Week commencing')
)
and peev2.screen_entry_value > fnd_date.date_to_canonical(hr_gbnicar.uk_tax_yr_end(sysdate) - 5)
-- Retrieve only those entries that main SMP query will retrieve
and exists
( select 1
from per_all_assignments_f paf,
per_all_people_f ppf,
per_person_types ppt,
per_periods_of_service pps
where peef.assignment_id = paf.assignment_id
and paf.person_id = ppf.person_id
and ppf.person_id = pps.person_id
and ppt.person_type_id = ppf.person_type_id
and peev2.screen_entry_value between fnd_date.date_to_canonical(ppf.effective_start_date)
and fnd_date.date_to_canonical(ppf.effective_end_date)
and peev2.screen_entry_value between fnd_date.date_to_canonical(paf.effective_start_date)
and fnd_date.date_to_canonical(paf.effective_end_date)
and nvl(pps.final_process_date,to_date('31/12/4712','DD/MM/YYYY')) >= sysdate
)
and peev3.input_value_id =
(select input_value_id
from pay_input_values_f piv
where petf.element_type_id = piv.element_type_id
and upper(piv.name) = upper('Rate'))
-- Rate restriction - only retrieve entries on LOW rate for SMP
and upper(peev3.screen_entry_value) = upper('Low')
-- Get first row that matches all of the above criteria
and rownum = 1;
select petf.element_information10, petf.element_information9, petf.element_information16
from pay_element_types_f petf
where petf.element_name = 'Statutory Maternity Pay'
and petf.effective_start_date < trunc(hr_gbnicar.uk_tax_yr_end(sysdate)) - 4
order by effective_start_date desc;
/* Function to check if SAP entries in new tax year have already updated */
/* with new SAP rates and recalculated - returns TRUE if so, FALSE if not */
function sap_rate_changed return boolean is
/* Check for SAP rates beginning on or after April 1st */
cursor csr_new_sap_rate is
select petf.element_information5, petf.element_information7
from pay_element_types_f petf
where petf.element_name = 'Statutory Adoption Pay'
and petf.effective_start_date >= trunc(hr_gbnicar.uk_tax_yr_end(sysdate)) - 4
order by effective_start_date;
select peev1.screen_entry_value
from pay_element_entry_values_f peev1,
pay_element_entry_values_f peev2,
pay_input_values_f piv,
pay_element_entries_f peef,
pay_element_links_f pelf,
pay_element_types_f petf
where piv.input_value_id = peev1.input_value_id
and peev1.element_entry_id = peev2.element_entry_id
and peev1.element_entry_id = peef.element_entry_id
and peef.element_link_id = pelf.element_link_id
and pelf.element_type_id = petf.element_type_id
and piv.name = 'Amount'
and petf.element_name = 'Statutory Adoption Pay'
and petf.effective_start_date > hr_gbnicar.uk_tax_yr_end(sysdate) - 5
-- Time restriction - only rows after tax year end
and peev2.input_value_id =
(select input_value_id
from pay_input_values_f piv
where petf.element_type_id = piv.element_type_id
and upper(piv.name) = upper('Week commencing')
)
and peev2.screen_entry_value > fnd_date.date_to_canonical(hr_gbnicar.uk_tax_yr_end(sysdate) - 5)
-- Retrieve only those entries that main sap query will retrieve
and exists
( select 1
from per_all_assignments_f paf,
per_all_people_f ppf,
per_person_types ppt,
per_periods_of_service pps
where peef.assignment_id = paf.assignment_id
and paf.person_id = ppf.person_id
and ppf.person_id = pps.person_id
and ppt.person_type_id = ppf.person_type_id
and peev2.screen_entry_value between fnd_date.date_to_canonical(ppf.effective_start_date)
and fnd_date.date_to_canonical(ppf.effective_end_date)
and peev2.screen_entry_value between fnd_date.date_to_canonical(paf.effective_start_date)
and fnd_date.date_to_canonical(paf.effective_end_date)
and nvl(pps.final_process_date,to_date('31/12/4712','DD/MM/YYYY')) >= sysdate
)
-- Get first row that matches all of the above criteria
and rownum = 1;
select petf.element_information5, petf.element_information7
from pay_element_types_f petf
where petf.element_name = 'Statutory Adoption Pay'
and petf.effective_start_date < hr_gbnicar.uk_tax_yr_end(sysdate) - 4
order by effective_start_date desc;
/* Function to check if SPPA entries in new tax year have already updated */
/* with new SPPA rates and recalculated - returns TRUE if so, FALSE if not */
function sppa_rate_changed return boolean is
/* Check for SPPA rates beginning on or after April 1st */
cursor csr_new_sppa_rate is
select petf.element_information6, petf.element_information8
from pay_element_types_f petf
where petf.element_name = 'Statutory Paternity Pay Adoption'
and petf.effective_start_date >= trunc(hr_gbnicar.uk_tax_yr_end(sysdate)) - 4
order by effective_start_date;
select peev1.screen_entry_value
from pay_element_entry_values_f peev1,
pay_element_entry_values_f peev2,
pay_input_values_f piv,
pay_element_entries_f peef,
pay_element_links_f pelf,
pay_element_types_f petf
where piv.input_value_id = peev1.input_value_id
and peev1.element_entry_id = peev2.element_entry_id
and peev1.element_entry_id = peef.element_entry_id
and peef.element_link_id = pelf.element_link_id
and pelf.element_type_id = petf.element_type_id
and piv.name = 'Amount'
and petf.element_name = 'Statutory Paternity Pay Adoption'
and petf.effective_start_date > hr_gbnicar.uk_tax_yr_end(sysdate) - 5
-- Time restriction - only rows after tax year end
and peev2.input_value_id =
(select input_value_id
from pay_input_values_f piv
where petf.element_type_id = piv.element_type_id
and upper(piv.name) = upper('Week commencing')
)
and peev2.screen_entry_value > fnd_date.date_to_canonical(hr_gbnicar.uk_tax_yr_end(sysdate) - 5)
-- Retrieve only those entries that main sppa query will retrieve
and exists
( select 1
from per_all_assignments_f paf,
per_all_people_f ppf,
per_person_types ppt,
per_periods_of_service pps
where peef.assignment_id = paf.assignment_id
and paf.person_id = ppf.person_id
and ppf.person_id = pps.person_id
and ppt.person_type_id = ppf.person_type_id
and peev2.screen_entry_value between fnd_date.date_to_canonical(ppf.effective_start_date)
and fnd_date.date_to_canonical(ppf.effective_end_date)
and peev2.screen_entry_value between fnd_date.date_to_canonical(paf.effective_start_date)
and fnd_date.date_to_canonical(paf.effective_end_date)
and nvl(pps.final_process_date,to_date('31/12/4712','DD/MM/YYYY')) >= sysdate
)
-- Get first row that matches all of the above criteria
and rownum = 1;
select petf.element_information6, petf.element_information8
from pay_element_types_f petf
where petf.element_name = 'Statutory Paternity Pay Adoption'
and petf.effective_start_date < trunc(hr_gbnicar.uk_tax_yr_end(sysdate)) - 4
order by effective_start_date desc;
/* Function to check if SPPB entries in new tax year have already updated */
/* with new SPPB rates and recalculated - returns TRUE if so, FALSE if not */
function sppb_rate_changed return boolean is
/* Check for SPPB rates beginning on or after April 1st */
cursor csr_new_sppb_rate is
select petf.element_information6, petf.element_information9
from pay_element_types_f petf
where petf.element_name = 'Statutory Paternity Pay Birth'
and petf.effective_start_date >= trunc(hr_gbnicar.uk_tax_yr_end(sysdate)) - 4
order by effective_start_date;
select peev1.screen_entry_value
from pay_element_entry_values_f peev1,
pay_element_entry_values_f peev2,
pay_input_values_f piv,
pay_element_entries_f peef,
pay_element_links_f pelf,
pay_element_types_f petf
where piv.input_value_id = peev1.input_value_id
and peev1.element_entry_id = peev2.element_entry_id
and peev1.element_entry_id = peef.element_entry_id
and peef.element_link_id = pelf.element_link_id
and pelf.element_type_id = petf.element_type_id
and piv.name = 'Amount'
and petf.element_name = 'Statutory Paternity Pay Birth'
and petf.effective_start_date > hr_gbnicar.uk_tax_yr_end(sysdate) - 5
-- Time restriction - only rows after tax year end
and peev2.input_value_id =
(select input_value_id
from pay_input_values_f piv
where petf.element_type_id = piv.element_type_id
and upper(piv.name) = upper('Week commencing')
)
and peev2.screen_entry_value > fnd_date.date_to_canonical(hr_gbnicar.uk_tax_yr_end(sysdate) - 5)
-- Retrieve only those entries that main SPPB query will retrieve
and exists
( select 1
from per_all_assignments_f paf,
per_all_people_f ppf,
per_person_types ppt,
per_periods_of_service pps
where peef.assignment_id = paf.assignment_id
and paf.person_id = ppf.person_id
and ppf.person_id = pps.person_id
and ppt.person_type_id = ppf.person_type_id
and peev2.screen_entry_value between fnd_date.date_to_canonical(ppf.effective_start_date)
and fnd_date.date_to_canonical(ppf.effective_end_date)
and peev2.screen_entry_value between fnd_date.date_to_canonical(paf.effective_start_date)
and fnd_date.date_to_canonical(paf.effective_end_date)
and nvl(pps.final_process_date,to_date('31/12/4712','DD/MM/YYYY')) >= sysdate
)
-- Get first row that matches all of the above criteria
and rownum = 1;
select petf.element_information6, petf.element_information9
from pay_element_types_f petf
where petf.element_name = 'Statutory Paternity Pay Birth'
and petf.effective_start_date < trunc(hr_gbnicar.uk_tax_yr_end(sysdate)) - 4
order by effective_start_date desc;
hr_utility.trace('Entering: '||g_package||'.update_ssp_smp_entries');
savepoint pre_update_status;
/* Check first whether SSP element entries already updated for new tax year */
/* SSP update block */
begin
savepoint pre_ssp_update_status;
if not ssp_entries_already_updated
then
hr_utility.trace('SSP element entries not updated, updating ....');
l_update_error := true;
hr_utility.trace('Updated entries for '||l_count||' absences');
hr_utility.trace('SSP element entries already updated for new tax year');
hr_utility.trace('Unexpected error occurred inside SSP update block');
rollback to pre_ssp_update_status;
l_update_error := true;
end; /* SSP update block */
/* Now check SMP entries to see if they have been updated */
/* SMP update block */
-- 05/12/2003 rmakhija: Uncommented following section for TYE 2003/4
begin
--
savepoint pre_smp_update_status;
l_update_error := true;
hr_utility.trace('Updated entries for '||l_mat_count||' maternities');
hr_utility.trace('SMP element entries already updated for new tax year');
hr_utility.trace('Unexpected error occurred inside SMP update block');
rollback to pre_smp_update_status;
l_update_error := true;
end; /* SMP update block */
savepoint pre_sap_update_status;
l_update_error := true;
hr_utility.trace('Updated entries for '||l_adop_count||' adoptions');
hr_utility.trace('SAP element entries already updated for new tax year');
hr_utility.trace('Unexpected error occurred inside SAP update block');
rollback to pre_sap_update_status;
l_update_error := true;
end; /* SAP update block */
savepoint pre_sppa_update_status;
l_update_error := true;
hr_utility.trace('Updated entries for '||l_pat_adop_count||' paternity adoptions');
hr_utility.trace('SPP Adoption element entries already updated for new tax year');
hr_utility.trace('Unexpected error occurred inside SPP ADoption update block');
rollback to pre_sppa_update_status;
l_update_error := true;
end; /* SPP Adoption update block */
savepoint pre_sppb_update_status;
l_update_error := true;
hr_utility.trace('Updated entries for '||l_pat_birth_count||' paternity births');
hr_utility.trace('SPP Birth element entries already updated for new tax year');
hr_utility.trace('Unexpected error occurred inside SPP Birth update block');
rollback to pre_sppb_update_status;
l_update_error := true;
end; /* SPP Birth update block */
hr_utility.trace('Update of SSP, SMP, SAP, SPP Adoption and SPP Birth entries complete');
p_update_error := l_update_error;
hr_utility.trace('Leaving: '||g_package||'.update_ssp_smp_entries');
hr_utility.trace('Unexpected error occurred inside SSP/SMP element entries update procedure');
rollback to pre_update_status;
p_update_error := true;
end update_ssp_smp_entries;
procedure update_ssp_smp_entries (P_UPDATE_ERROR OUT NOCOPY boolean, p_job_err OUT NOCOPY l_job_err_typ) is
cursor csr_affected_absences is
select nvl(paa.linked_absence_id,paa.absence_attendance_id) as absence_id,
paa.person_id,
nvl(paa.date_start,paa.sickness_start_date) as absence_start_date,
nvl(paa.date_end,paa.sickness_end_date) as absence_end_date
from per_absence_attendances paa,
per_absence_attendance_types paat
where paa.absence_attendance_type_id = paat.absence_attendance_type_id
and paat.absence_category = 'S'
and
/* SSP absences which span tax years or start in old tax year and are open-ended */
(
((paa.date_start between hr_gbnicar.uk_tax_yr_start(sysdate) and hr_gbnicar.uk_tax_yr_end(sysdate))
and (paa.date_end > hr_gbnicar.uk_tax_yr_end(sysdate) or paa.date_end is null)
)
or
((paa.sickness_start_date between hr_gbnicar.uk_tax_yr_start(sysdate) and hr_gbnicar.uk_tax_yr_end(sysdate))
and (paa.sickness_end_date > hr_gbnicar.uk_tax_yr_end(sysdate) or paa.sickness_end_date is null)
)
or
/* SSP absences which start in the new tax year */
(
paa.date_start >= hr_gbnicar.uk_tax_yr_end(sysdate)
or paa.date_projected_start >= hr_gbnicar.uk_tax_yr_end(sysdate)
)
)
/* Do not retrieve terminated employees whose actual termination dates have passed or are null */
and not exists
( select 1
from per_all_people_f ppf,
per_person_types ppt,
per_periods_of_service pps
where ppf.person_id = pps.person_id
and ppt.person_type_id = ppf.person_type_id
and ppt.system_person_type = 'EX_EMP'
and nvl(pps.actual_termination_date,to_date('01/01/0001','DD/MM/YYYY')) <= sysdate
and ppf.person_id = paa.person_id
and pps.date_start = (select max(date_start)
from per_periods_of_service pos
where pos.person_id = pps.person_id)
and ppf.effective_start_date >= pps.date_start
and pps.date_start <= paa.sickness_start_date)
order by nvl(paa.linked_absence_id,paa.absence_attendance_id);
select paa.maternity_id,
paa.person_id,
paa.date_start,
paa.date_end,
paa.date_projected_start,
paa.date_projected_end
from per_absence_attendances paa,
ssp_maternities mat
where paa.maternity_id = mat.maternity_id
and nvl(mat.leave_type, 'MA') = p_leave_type
and
/* SMP absences which span tax years or start in old tax year and are open-ended */
(
((paa.date_start between hr_gbnicar.uk_tax_yr_start(sysdate) and hr_gbnicar.uk_tax_yr_end(sysdate))
and (paa.date_end > hr_gbnicar.uk_tax_yr_end(sysdate) or paa.date_end is null)
)
or
((paa.date_projected_start between hr_gbnicar.uk_tax_yr_start(sysdate) and hr_gbnicar.uk_tax_yr_end(sysdate))
and (paa.date_projected_end > hr_gbnicar.uk_tax_yr_end(sysdate) or paa.date_projected_end is null)
and paa.date_start IS NULL -- use projected dates only when actual dates not available
)
or
/* SMP absences which start in the new tax year */
(
paa.date_start >= hr_gbnicar.uk_tax_yr_end(sysdate)
or (paa.date_projected_start >= hr_gbnicar.uk_tax_yr_end(sysdate)
and paa.date_start IS NULL) -- use projected dates only when actual dates not available
)
)
/* Do not retrieve employees whose final process dates have passed */
and not exists
( select 1
from per_all_people_f ppf,
per_person_types ppt,
per_periods_of_service pps
where ppf.person_id = pps.person_id
and ppt.person_type_id = ppf.person_type_id
and ppt.system_person_type = 'EX_EMP'
and pps.final_process_date <= sysdate
and ppf.person_id = paa.person_id
and pps.date_start = (select max(date_start)
from per_periods_of_service pos
where pos.person_id = pps.person_id)
and ppf.effective_start_date >= pps.date_start
and pps.date_start <= paa.date_start)
/* Do not retrieve employees who are deceased */
and not exists
( select 1
from per_all_people_f ppf,
per_periods_of_service pps
where ppf.person_id = pps.person_id
and pps.leaving_reason = 'D'
and ppf.person_id = paa.person_id)
order by paa.maternity_id;
l_update_error boolean := FALSE;
/* Function to check if SSP entries in new tax year have already been updated */
/* with new SSP rates and recalculated - returns TRUE if so, FALSE if not */
function ssp_entries_already_updated return boolean is
cursor csr_new_ssp_rate is
select piv.default_value
from pay_input_values_f piv,
pay_element_types_f petf
where petf.element_type_id = piv.element_type_id
and piv.name = 'Rate'
and petf.element_name = 'Statutory Sick Pay'
and piv.effective_start_date > hr_gbnicar.uk_tax_yr_end(sysdate);
select peev1.screen_entry_value
from pay_element_entry_values_f peev1,
pay_element_entry_values_f peev2,
pay_input_values_f piv,
pay_element_entries_f peef,
pay_element_links_f pelf,
pay_element_types_f petf
where piv.input_value_id = peev1.input_value_id
and peev1.element_entry_id = peev2.element_entry_id
and peev1.element_entry_id = peef.element_entry_id
and peef.element_link_id = pelf.element_link_id
and pelf.element_type_id = petf.element_type_id
and piv.name = 'Rate'
and petf.element_name = 'Statutory Sick Pay'
and piv.effective_start_date > hr_gbnicar.uk_tax_yr_end(sysdate)
and peev2.element_entry_value_id =
(select peev3.element_entry_value_id
from pay_element_entry_values_f peev3
where input_value_id =
(select distinct input_value_id
from pay_input_values_f piv,
pay_element_types_f petf
where petf.element_type_id = piv.element_type_id
and petf.element_name = 'Statutory Sick Pay'
and piv.name = 'From')
and peev3.screen_entry_value >
fnd_date.date_to_canonical(hr_gbnicar.uk_tax_yr_end(sysdate))
/* Retrieve only those entries that will be retrieved by main SSP query */
and peev3.element_entry_id in
( select peef1.element_entry_id
from pay_element_entries_f peef1,
per_all_assignments_f paf,
per_all_people_f ppf,
per_person_types ppt,
per_periods_of_service pps
where peef1.assignment_id = paf.assignment_id
and paf.person_id = ppf.person_id
and ppf.person_id = pps.person_id
and ppt.person_type_id = ppf.person_type_id
and peev3.screen_entry_value between fnd_date.date_to_canonical(ppf.effective_start_date)
and fnd_date.date_to_canonical(ppf.effective_end_date)
and peev3.screen_entry_value between fnd_date.date_to_canonical(paf.effective_start_date)
and fnd_date.date_to_canonical(paf.effective_end_date)
and ppt.system_person_type = 'EMP'
and nvl(pps.actual_termination_date,to_date('31/12/4712','DD/MM/YYYY')) >= sysdate
)
and rownum = 1);
hr_utility.trace('Entering ssp_entries_already_updated function');
end ssp_entries_already_updated;
/* Function to check if SMP entries in new tax year have already updated */
/* with new SMP rates and recalculated - returns TRUE if so, FALSE if not */
function smp_rate_changed return boolean is
/* Check for SMP rates beginning on or after April 1st */
cursor csr_new_smp_rate is
select petf.element_information10, petf.element_information9, petf.element_information16
from pay_element_types_f petf
where petf.element_name = 'Statutory Maternity Pay'
and petf.effective_start_date >= trunc(hr_gbnicar.uk_tax_yr_end(sysdate)) - 4
order by effective_start_date;
select peev1.screen_entry_value
from pay_element_entry_values_f peev1,
pay_element_entry_values_f peev2,
pay_element_entry_values_f peev3,
pay_input_values_f piv,
pay_element_entries_f peef,
pay_element_links_f pelf,
pay_element_types_f petf
where piv.input_value_id = peev1.input_value_id
and peev1.element_entry_id = peev2.element_entry_id
and peev1.element_entry_id = peev3.element_entry_id
and peev1.element_entry_id = peef.element_entry_id
and peef.element_link_id = pelf.element_link_id
and pelf.element_type_id = petf.element_type_id
and piv.name = 'Amount'
and petf.element_name = 'Statutory Maternity Pay'
and petf.effective_start_date > hr_gbnicar.uk_tax_yr_end(sysdate) - 5
-- Time restriction - only rows after tax year end
and peev2.input_value_id =
(select input_value_id
from pay_input_values_f piv
where petf.element_type_id = piv.element_type_id
and upper(piv.name) = upper('Week commencing')
)
and peev2.screen_entry_value > fnd_date.date_to_canonical(hr_gbnicar.uk_tax_yr_end(sysdate) - 5)
-- Retrieve only those entries that main SMP query will retrieve
and exists
( select 1
from per_all_assignments_f paf,
per_all_people_f ppf,
per_person_types ppt,
per_periods_of_service pps
where peef.assignment_id = paf.assignment_id
and paf.person_id = ppf.person_id
and ppf.person_id = pps.person_id
and ppt.person_type_id = ppf.person_type_id
and peev2.screen_entry_value between fnd_date.date_to_canonical(ppf.effective_start_date)
and fnd_date.date_to_canonical(ppf.effective_end_date)
and peev2.screen_entry_value between fnd_date.date_to_canonical(paf.effective_start_date)
and fnd_date.date_to_canonical(paf.effective_end_date)
and nvl(pps.final_process_date,to_date('31/12/4712','DD/MM/YYYY')) >= sysdate
)
and peev3.input_value_id =
(select input_value_id
from pay_input_values_f piv
where petf.element_type_id = piv.element_type_id
and upper(piv.name) = upper('Rate'))
-- Rate restriction - only retrieve entries on LOW rate for SMP
and upper(peev3.screen_entry_value) = upper('Low')
-- Get first row that matches all of the above criteria
and rownum = 1;
select petf.element_information10, petf.element_information9, petf.element_information16
from pay_element_types_f petf
where petf.element_name = 'Statutory Maternity Pay'
and petf.effective_start_date < trunc(hr_gbnicar.uk_tax_yr_end(sysdate)) - 4
order by effective_start_date desc;
/* Function to check if SAP entries in new tax year have already updated */
/* with new SAP rates and recalculated - returns TRUE if so, FALSE if not */
function sap_rate_changed return boolean is
/* Check for SAP rates beginning on or after April 1st */
cursor csr_new_sap_rate is
select petf.element_information5, petf.element_information7
from pay_element_types_f petf
where petf.element_name = 'Statutory Adoption Pay'
and petf.effective_start_date >= trunc(hr_gbnicar.uk_tax_yr_end(sysdate)) - 4
order by effective_start_date;
select peev1.screen_entry_value
from pay_element_entry_values_f peev1,
pay_element_entry_values_f peev2,
pay_input_values_f piv,
pay_element_entries_f peef,
pay_element_links_f pelf,
pay_element_types_f petf
where piv.input_value_id = peev1.input_value_id
and peev1.element_entry_id = peev2.element_entry_id
and peev1.element_entry_id = peef.element_entry_id
and peef.element_link_id = pelf.element_link_id
and pelf.element_type_id = petf.element_type_id
and piv.name = 'Amount'
and petf.element_name = 'Statutory Adoption Pay'
and petf.effective_start_date > hr_gbnicar.uk_tax_yr_end(sysdate) - 5
-- Time restriction - only rows after tax year end
and peev2.input_value_id =
(select input_value_id
from pay_input_values_f piv
where petf.element_type_id = piv.element_type_id
and upper(piv.name) = upper('Week commencing')
)
and peev2.screen_entry_value > fnd_date.date_to_canonical(hr_gbnicar.uk_tax_yr_end(sysdate) - 5)
-- Retrieve only those entries that main sap query will retrieve
and exists
( select 1
from per_all_assignments_f paf,
per_all_people_f ppf,
per_person_types ppt,
per_periods_of_service pps
where peef.assignment_id = paf.assignment_id
and paf.person_id = ppf.person_id
and ppf.person_id = pps.person_id
and ppt.person_type_id = ppf.person_type_id
and peev2.screen_entry_value between fnd_date.date_to_canonical(ppf.effective_start_date)
and fnd_date.date_to_canonical(ppf.effective_end_date)
and peev2.screen_entry_value between fnd_date.date_to_canonical(paf.effective_start_date)
and fnd_date.date_to_canonical(paf.effective_end_date)
and nvl(pps.final_process_date,to_date('31/12/4712','DD/MM/YYYY')) >= sysdate
)
-- Get first row that matches all of the above criteria
and rownum = 1;
select petf.element_information5, petf.element_information7
from pay_element_types_f petf
where petf.element_name = 'Statutory Adoption Pay'
and petf.effective_start_date < hr_gbnicar.uk_tax_yr_end(sysdate) - 4
order by effective_start_date desc;
/* Function to check if SPPA entries in new tax year have already updated */
/* with new SPPA rates and recalculated - returns TRUE if so, FALSE if not */
function sppa_rate_changed return boolean is
/* Check for SPPA rates beginning on or after April 1st */
cursor csr_new_sppa_rate is
select petf.element_information6, petf.element_information8
from pay_element_types_f petf
where petf.element_name = 'Statutory Paternity Pay Adoption'
and petf.effective_start_date >= trunc(hr_gbnicar.uk_tax_yr_end(sysdate)) - 4
order by effective_start_date;
select peev1.screen_entry_value
from pay_element_entry_values_f peev1,
pay_element_entry_values_f peev2,
pay_input_values_f piv,
pay_element_entries_f peef,
pay_element_links_f pelf,
pay_element_types_f petf
where piv.input_value_id = peev1.input_value_id
and peev1.element_entry_id = peev2.element_entry_id
and peev1.element_entry_id = peef.element_entry_id
and peef.element_link_id = pelf.element_link_id
and pelf.element_type_id = petf.element_type_id
and piv.name = 'Amount'
and petf.element_name = 'Statutory Paternity Pay Adoption'
and petf.effective_start_date > hr_gbnicar.uk_tax_yr_end(sysdate) - 5
-- Time restriction - only rows after tax year end
and peev2.input_value_id =
(select input_value_id
from pay_input_values_f piv
where petf.element_type_id = piv.element_type_id
and upper(piv.name) = upper('Week commencing')
)
and peev2.screen_entry_value > fnd_date.date_to_canonical(hr_gbnicar.uk_tax_yr_end(sysdate) - 5)
-- Retrieve only those entries that main sppa query will retrieve
and exists
( select 1
from per_all_assignments_f paf,
per_all_people_f ppf,
per_person_types ppt,
per_periods_of_service pps
where peef.assignment_id = paf.assignment_id
and paf.person_id = ppf.person_id
and ppf.person_id = pps.person_id
and ppt.person_type_id = ppf.person_type_id
and peev2.screen_entry_value between fnd_date.date_to_canonical(ppf.effective_start_date)
and fnd_date.date_to_canonical(ppf.effective_end_date)
and peev2.screen_entry_value between fnd_date.date_to_canonical(paf.effective_start_date)
and fnd_date.date_to_canonical(paf.effective_end_date)
and nvl(pps.final_process_date,to_date('31/12/4712','DD/MM/YYYY')) >= sysdate
)
-- Get first row that matches all of the above criteria
and rownum = 1;
select petf.element_information6, petf.element_information8
from pay_element_types_f petf
where petf.element_name = 'Statutory Paternity Pay Adoption'
and petf.effective_start_date < trunc(hr_gbnicar.uk_tax_yr_end(sysdate)) - 4
order by effective_start_date desc;
/* Function to check if SPPB entries in new tax year have already updated */
/* with new SPPB rates and recalculated - returns TRUE if so, FALSE if not */
function sppb_rate_changed return boolean is
/* Check for SPPB rates beginning on or after April 1st */
cursor csr_new_sppb_rate is
select petf.element_information6, petf.element_information9
from pay_element_types_f petf
where petf.element_name = 'Statutory Paternity Pay Birth'
and petf.effective_start_date >= trunc(hr_gbnicar.uk_tax_yr_end(sysdate)) - 4
order by effective_start_date;
select peev1.screen_entry_value
from pay_element_entry_values_f peev1,
pay_element_entry_values_f peev2,
pay_input_values_f piv,
pay_element_entries_f peef,
pay_element_links_f pelf,
pay_element_types_f petf
where piv.input_value_id = peev1.input_value_id
and peev1.element_entry_id = peev2.element_entry_id
and peev1.element_entry_id = peef.element_entry_id
and peef.element_link_id = pelf.element_link_id
and pelf.element_type_id = petf.element_type_id
and piv.name = 'Amount'
and petf.element_name = 'Statutory Paternity Pay Birth'
and petf.effective_start_date > hr_gbnicar.uk_tax_yr_end(sysdate) - 5
-- Time restriction - only rows after tax year end
and peev2.input_value_id =
(select input_value_id
from pay_input_values_f piv
where petf.element_type_id = piv.element_type_id
and upper(piv.name) = upper('Week commencing')
)
and peev2.screen_entry_value > fnd_date.date_to_canonical(hr_gbnicar.uk_tax_yr_end(sysdate) - 5)
-- Retrieve only those entries that main SPPB query will retrieve
and exists
( select 1
from per_all_assignments_f paf,
per_all_people_f ppf,
per_person_types ppt,
per_periods_of_service pps
where peef.assignment_id = paf.assignment_id
and paf.person_id = ppf.person_id
and ppf.person_id = pps.person_id
and ppt.person_type_id = ppf.person_type_id
and peev2.screen_entry_value between fnd_date.date_to_canonical(ppf.effective_start_date)
and fnd_date.date_to_canonical(ppf.effective_end_date)
and peev2.screen_entry_value between fnd_date.date_to_canonical(paf.effective_start_date)
and fnd_date.date_to_canonical(paf.effective_end_date)
and nvl(pps.final_process_date,to_date('31/12/4712','DD/MM/YYYY')) >= sysdate
)
-- Get first row that matches all of the above criteria
and rownum = 1;
select petf.element_information6, petf.element_information9
from pay_element_types_f petf
where petf.element_name = 'Statutory Paternity Pay Birth'
and petf.effective_start_date < trunc(hr_gbnicar.uk_tax_yr_end(sysdate)) - 4
order by effective_start_date desc;
hr_utility.trace('Entering: '||g_package||'.update_ssp_smp_entries');
savepoint pre_update_status;
/* Check first whether SSP element entries already updated for new tax year */
/* SSP update block */
begin
savepoint pre_ssp_update_status;
if not ssp_entries_already_updated
then
hr_utility.trace('SSP element entries not updated, updating ....');
l_update_error := true;
hr_utility.trace('Updated entries for '||l_count||' absences');
hr_utility.trace('SSP element entries already updated for new tax year');
hr_utility.trace('Unexpected error occurred inside SSP update block');
rollback to pre_ssp_update_status;
l_update_error := true;
end; /* SSP update block */
/* Now check SMP entries to see if they have been updated */
/* SMP update block */
-- 05/12/2003 rmakhija: Uncommented following section for TYE 2003/4
begin
--
savepoint pre_smp_update_status;
l_update_error := true;
hr_utility.trace('Updated entries for '||l_mat_count||' maternities');
hr_utility.trace('SMP element entries already updated for new tax year');
hr_utility.trace('Unexpected error occurred inside SMP update block');
rollback to pre_smp_update_status;
l_update_error := true;
end; /* SMP update block */
savepoint pre_sap_update_status;
l_update_error := true;
hr_utility.trace('Updated entries for '||l_adop_count||' adoptions');
hr_utility.trace('SAP element entries already updated for new tax year');
hr_utility.trace('Unexpected error occurred inside SAP update block');
rollback to pre_sap_update_status;
l_update_error := true;
end; /* SAP update block */
savepoint pre_sppa_update_status;
l_update_error := true;
hr_utility.trace('Updated entries for '||l_pat_adop_count||' paternity adoptions');
hr_utility.trace('SPP Adoption element entries already updated for new tax year');
hr_utility.trace('Unexpected error occurred inside SPP ADoption update block');
rollback to pre_sppa_update_status;
l_update_error := true;
end; /* SPP Adoption update block */
savepoint pre_sppb_update_status;
l_update_error := true;
hr_utility.trace('Updated entries for '||l_pat_birth_count||' paternity births');
hr_utility.trace('SPP Birth element entries already updated for new tax year');
hr_utility.trace('Unexpected error occurred inside SPP Birth update block');
rollback to pre_sppb_update_status;
l_update_error := true;
end; /* SPP Birth update block */
p_update_error := l_update_error;
select nvl(FAILURES,0) into l_fail from dba_jobs
where job = l_tbl_job(I).job_no and rownum = 1;
p_update_error := true;
hr_utility.trace('Update of SSP, SMP, SAP, SPP Adoption and SPP Birth entries complete');
hr_utility.trace('Leaving: '||g_package||'.update_ssp_smp_entries');
hr_utility.trace('Unexpected error occurred inside SSP/SMP element entries update procedure');
rollback to pre_update_status;
p_update_error := true;
end update_ssp_smp_entries;