[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
variables prior to insert into entry
values in insert_element_entry calls.
29 Nov 95 N Simpson Changed value placed in qualifying days
entry value from qualifying days in the
calendar week to qualifying days in the
absence period.
5 Dec 95 N Simpson Removed test for length of absence from
cursor PIW because it was restricting
unnecessarily to absences longer than
the PIW threshhold. If an absence has
a linked_absence_id then it must be a
PIW anyway, even if it is too short to
be a PIW in its own right.
9 Feb 96 N Simpson When calculating amount of SSP, add
0.004 before rounding instead of 0.0049
so that decimal places after the third
one are ignored.
4 Apr 96 N Simpson Modified absence_is_a_PIW to take
account of contiguous absences AFTER
the one being checked as well as
those prior to it.
Modified linked_absence_id to cascade
a linked_absence_id to contiguous
absences which become part of a PIW
by virtue of the insertion of a new
absence which takes the total unbroken
absence period over the threshhold.
9 Apr 96 N Simpson New procedure update_linked_absence_IDs
24 Aug 96 C Barbieri Changed the select stmt in the
csr_missing_links cursor definition in
order to use index per_absence_attendan
ces_fk4.
13 Nov 96 C Barbieri Bug. 418895: changed the check about the
SSP entitlement. Now when an absence is
entered that is not in a PIW, we delete
all the old entries and stoppages.
21 Jan 97 C Barbieri Bg 441738: Changed this_week.date_earned
to pick up the absence start_date,
instead of the previous Sunday.
Change List
===========
Version Date BugNo Author Comment
-------+----------+----------+----------+-------------------------------------
30.42 02-May-97 467870 Khabibul Changes in check_for_break_in_linked_PIW
csr_previous_absence. Removed NVL around
the indexed column and replaced it with
'OR'. The sql is then more efficient,
with no full table scan.
***Same fix was applied to version 30.41
which was a backport of Prod14 (taken
from 30.12) ****
This is the latest version of package
for any latest Prod releases. ****
Also added new 'Change List' header to
comply with PKM standards.
30.43 09-jun-97 502108 RThirlby Forced SSP_rate_figure to be based
on the sickness_start_date instead
of the PIW_start_date.
30.44 18-jul-97 513292 RThirlby Altered qualifying_days_in_period to
return default BG qualifying pattern
if a person has a default as well as
personal pattern at any time during
their absence (linked or not).
30.45 12-Sep-97 550269 RThirlby Explicitly open csr_qualifying_pattern
to prevent invalid_cursor error.
30.46 29-Sep-97 504386 RThirlby Call qualifying_days_in_period for the
absence as a whole so that the pl/sql
table in hr_calendar_pkg is populated
once for the whole sickness absence.
30.47 03-Dec-97 589806 RThirlby Made allowance for open ended absence
in call to qualifying_days_in_period.
30.48 05-Dec-97 593097 RThirlby Forces end_date to be the next
Saturday, after the Sunday before
the sickness_start_date.
30.49 11-dec-97 555505 RThirlby Added <= rather than < so that one
day absences are accounted for.
30.50 24-mar-98 563202 RThirlby Performance fix on cursor PIW and
csr_qualifying_pattern.
30.51 04-Jun-98 668368 AMyers Change so that previous stoppages are
retrieved if the id is either the PIW
OR the absence id and actioned
30.52 19-Jun-98 563202 AParkes Re-wrote absence_is_a_PIW to avoid
calling unnecessary sql.
Changed csr_existing_entries to avoid
using dt views and stop calling
ssp_smp_support_pkg.value
Added history for 30.51
30.53 19-AUG-98 563202 A.Myers Loads of changes to improve efficiency,
/701750 such as using pl/sql tables, creating
calendar for the whole PIW, not just
for each absence. Also added standard
trace.
110.13 16-AUG-99 886707 MVilrokx The initialisation of l_stopage_found
parameter was not correct. Moved it
inside the loop so it is reset every
time. This assurs that a wating days
are still generated after a user
created stoppage.
115.9 08-NOV-99 1020757 MVilrokx Fixed two problems with date conversion,
one in the cursor csr_existing_entries
and twice when calling insert_element_entry
115.10 04-AUG-00 1320737 DFoster Amended the logic of the
check_disqualifying_period which was
causing the update of the MPP start
date to be calculated incorrectly where
a period of sickness went over the sixth
week prior to the EWC
115.11 14-AUG-00 1304683 DFoster Amended the check_average_earnings
procedure so that it calls the new
version of the procedure to calculate
average earnings so that Sicknesses
and Maternities can be dealt with
differently.
115.14 31-JUL-01 1754802 GButler Changed ssp_is_installed procedure.
Procedure was checking PAY_LEGISLATION_
RULES table for evidence that ssp was
installed. This check was made obsolete
in 10.7 and was causing incorrect results
to be returned when checking for SSP
installation
115.16 22-FEB-02 1310170 ABhaduri Added cursor csr_previous_reason to populate
the ssp_smp_support_pkg.reason_for_no_earnings
variable when the employee is rehired and the
absence is getting saved for the second time.
115.21 05-NOV-02 2620894 srjadhav Changed the code so that MPP start date is
the next day after first working day
of (EWC - sickness_trigger_weeks) week.
115.22 12-DEC-02 AMills nocopy addition.
115.23 27-JAN-03 GButler further nocopy change to check_average
_earnings to include 2nd dummy variable
to act as placeholder for OUT param from
ssp_ern_ins
115.24 03-FEB-03 ABlinko added further restriction to maternity_details
cursor. Only returns records with a leave_type
of MA or null
115.25 12-MAY-03 GButler Changes for SSP Legislative Requirements 2003.
Commented out stoppage code for Fixed Contract Too
Short stoppage and changed check_service procedure
accordingly. Also added new check_link_letter
procedure
115.26 13-JUN-03 GButler Bug 2984845 - changes to do_PIW_calculations to
fix issue where PIW lasted max time stoppage not
being raised correctly
115.27 21-JUL-03 ASENGAR BUG 2984577- Added exception evidence_not_available
to the procedure ENTITLED_TO_SSP to raise
exception when sickness end date is not mentioned.
115.28 07-AUG-03 SSEKHAR BUG 2984458- Added another column to csr_person
cursor(leaving_reason) to give a stoppage as
'Employee Died' when the employee has deceased.
115.29 08-AUG-03 SSEKHAR BUG 2984458- The check_sql utility was not run
last time when the file was arcsed in.
115.32 21-OCT-03 ASENGAR BUG 2984577- Added code to handle linked absences
when evidence is not present.
115.33 24-OCT-03 ABLINKO BUG 3208325- Removed hardcoded SATURDAY and
SUNDAY references
115.34 03-DEC-03 SSEKHAR BUG 2984458 - Added condition to check if the
Employee is deceased before raising the exception
piw_max_length_reached
115.35 02-MAR-04 ABLINKO BUG 3456918 - Added rtrim when deriving
l_saturday and l_sunday
115.36 22-MAR-04 3466672 ABLINKO Added procedure medical_control
115.37 08-JUL-04 3750125 ABLINKO Added g_absence_del to limit use of
medical_control during absence deletion
115.38 12-JUL-04 ABLINKO check_evidence now resets last_entitled_day to
stoppage start date - 1
115.39 21-JUL-04 3769536 ABLINKO Amended person_control to work with open-ended
absences
115.40 12-APR-06 5126163 KTHAMPAN Amended do_PIW_calculation to check for total_SSP_weeks
115.41 04-AUG-06 5444012 KTHAMPAN Amended do_PIW_calculation to reset days_remaining
115.42 23-AUG-06 5482199 KTHAMPAN Statutory Changes for 2007.
115.45 25-AUG-06 5482199 KTHAMPAN Amend function check_average_earnings to
update the effective_date of the earnings calculations
for employee over 65.
Also change function check_employee_too_old to
check for stoppage on the main absence.
115.46 31-AUG-06 5504380 KTHAMPAN Added overload function linked_piw_start_date
and linked_piw_end_date.
115.49 06-SEP-06 5510601 KTHAMPAN Amend linked_PIW_start_date/end_date functions.
Added overload function linked_PIW_start_date,
linked_PIW_end_date and absence_is_a_PIW.
5517272 KTHAMPAN Amend check_age to pass the least of PIW_end_date
and 1 0ctober 2006 to the stoppage end date.
115.51 08-SEP-06 5517272 KTHAMPAN Amend the check_age to use the absence end date
instead of 1 October 2006.
115.52 09-SEP-06 5517272 HWINSOR Changed nocopy declaration
115.53 19-SEP-06 5550795 KTHAMPAN Amend the check_age
115.54 12-OCT-06 5583730 NPERSHAD Modified the function absence_is_a_PIW with 4 parameters.
115.55 09-DEC-06 5706912 KTHAMPAN Amend procedure ins_ssp_temp_affected_rows_PIW,
update_linked_absence_ids and person_control
115.56 19-MAR-07 5932995 HWINSOR Arcs in KTHAMPAN changes. Employees starting
post 1-Oct-06 have correct PIW start dates.
115.57 23-DEC-07 6658285 PBALU Changed a For loop in qualifying_days_in_period to use
PL/sql table instead of cursor as the cursor is opened/closed
inside the for loop.
115.58 19-Sep-08 6860926 EMUNISEK Changed the less than zero condition on weeks_remaining to
account for the rounding error of decimal values
115.60 03-Feb-09 7688727 NPANNAMP When creating SSP correction element previously LSP was passed
now it is changed to Final Process date similar to SMP/SAP corrections
115.61 09-Apr-09 8356706 NPANNAMP variables l_saturday, l_sunday moved from package body level to
ENTITLED_TO_SSP function variables, to avoid issues of package state
not resetting when used in OA Pages.
115.62 23-Sep-11 12552547 PBALU Sickness absence should not be linked with absences taken in different period of service.
===============================================================================
*/
g_package constant varchar2 (31) := 'SSP_SSP_pkg.';
select person.date_of_birth,
person.date_of_death,
person.person_id,
person.sex,
person.full_name,
service.date_start,
service.prior_employment_SSP_weeks,
service.prior_employment_SSP_paid_to,
nvl (service.projected_termination_date,
hr_general.end_of_time) PROJECTED_TERMINATION_DATE,
nvl (service.actual_termination_date,
hr_general.end_of_time) ACTUAL_TERMINATION_DATE,
nvl (service.last_standard_process_date,
hr_general.end_of_time) LAST_STANDARD_PROCESS_DATE,
/* 7688727 begin */
nvl (service.final_process_date,
hr_general.end_of_time) FINAL_PROCESS_DATE,
/* 7688727 End */
service.leaving_reason
from per_all_people_f PERSON,
per_periods_of_service SERVICE
where person.person_id = p_person_id
and service.person_id = person.person_id
and p_date_start between service.date_start
and nvl (service.actual_termination_date,
hr_general.end_of_time);
select *
from per_absence_attendances
where absence_attendance_id = p_absence_attendance_id;
select sickness_start_date,
nvl(sickness_end_date,hr_general.end_of_time) sickness_end_date,
business_group_id,
absence_attendance_id,
pregnancy_related_illness
from per_absence_attendances
where (g_PIW_id = absence_attendance_id and linked_absence_id is null)
or g_PIW_ID = linked_absence_id
order by sickness_start_date;
procedure update_linked_absence_ids is
--
-- Updates linked_absence_IDs where the absence has either become part of a
-- series or dropped out of a series due to date changes on the absence.
--
l_proc constant varchar2 (72) := g_package||'update_linked_absence_ids';
select absence.*
from per_absence_attendances ABSENCE,
ssp_temp_affected_rows TRANSACTION
where
( (transaction.PIW_ID = absence.absence_attendance_id and
absence.linked_absence_id IS NULL)
or
transaction.piw_id = absence.linked_absence_id)
and transaction.locked = to_char(userenv('sessionid'));
update per_absence_attendances
set linked_absence_id = l_new_linked_absence_id
where absence_attendance_id = new_link.absence_attendance_id;
update ssp_temp_affected_rows
set PIW_ID = l_new_linked_absence_id
where PIW_ID = new_link.absence_attendance_id;
end update_linked_absence_ids;
select absence_attendance_id,
sickness_start_date,
sickness_end_date
from per_absence_attendances
where person_id = p_person_id
and sickness_start_date is not null
and sickness_start_date < p_sickness_start_date
and linked_absence_id is null
order by sickness_start_date desc
for update;
select absence_attendance_id
from per_absence_attendances
where sickness_start_date = x_sickness_start_date
and person_id = p_person_id;
update per_absence_attendances
set linked_absence_id = l_PIW_ID
where current of csr_contiguous_absences;
select nvl(sum(nvl(least(sickness_end_date,p_end),p_end) -
greatest(sickness_start_date,p_start) + 1),0)
from per_absence_attendances
where person_id = p_person_id
and sickness_start_date <= p_end
and nvl(sickness_end_date,hr_general.end_of_time) >= p_start;
select least(min(sickness_start_date),p_start),
greatest(max(nvl(sickness_end_date,hr_general.end_of_time)),p_end)
from per_absence_attendances
where person_id = p_person_id
and (sickness_end_date = p_start2 or sickness_start_date = p_end2);
select nvl(sum(nvl(least(sickness_end_date,p_end),p_end) -
greatest(sickness_start_date,p_start) + 1),0)
from per_absence_attendances
where person_id = p_person_id
and sickness_start_date <= p_end
and nvl(sickness_end_date,hr_general.end_of_time) >= p_start;
select least(min(sickness_start_date),p_start),
greatest(max(nvl(sickness_end_date,hr_general.end_of_time)),p_end)
from per_absence_attendances
where person_id = p_person_id
and (sickness_end_date = p_start2 or sickness_start_date = p_end2);
select linked_absence_id
from per_absence_attendances
where absence_attendance_id = p_absence_attendance_id;
select abs.linked_absence_id,
abs.sickness_start_date,
abs.sickness_end_date
from per_absence_attendances ABS
where abs.sickness_start_date is not null
and person_id = p_person_id
and abs.sickness_start_date = (
--
select min (abs2.sickness_start_date)
from per_absence_attendances ABS2
where abs2.sickness_start_date >= p_old_sickness_end_date
and person_id = p_person_id);
select 1
from per_absence_attendances
where linked_absence_id = p_absence_attendance_id;
select max (sickness_end_date)
from per_absence_attendances
where p_linked_absence_id = linked_absence_id
and sickness_end_date <= p_sickness_start_date;
select min (sickness_start_date)
from per_absence_attendances
where (l_PIW_id = linked_absence_id and absence_attendance_id is null)
or (l_PIW_id = absence_attendance_id and linked_absence_id is null)
and sickness_start_date >= p_sickness_end_date;
select months_between(p_date,date_of_birth)/12
from per_all_people_f
where person_id = p_person_id
and effective_end_date = (select max(effective_end_date)
from per_all_people_f
where person_id = p_person_id);
select months_between(p_date,p_date_of_birth)/12
from dual;
select linked_absence_id
from per_absence_attendances
where absence_attendance_id = p_absence_id;
select sickness_start_date
from per_absence_attendances
where absence_attendance_id = p_absence_id;
select sickness_end_date
from per_absence_attendances
where (absence_attendance_id = p_absence_id
or
linked_absence_id = p_absence_id)
order by sickness_end_date desc;
select sickness_start_date,
sickness_end_date,
absence_attendance_id
from per_absence_attendances
where person_id = p_person_id
and sickness_start_date is not null
and sickness_start_date < p_sickness_start_date
and (p_start_date is null or
sickness_start_date >= p_start_date)
order by sickness_start_date desc;
select sickness_start_date,
sickness_end_date,
absence_attendance_id
from per_absence_attendances
where person_id = p_person_id
and sickness_start_date is not null
and sickness_start_date < p_sickness_start_date
and (p_start_date is null or
sickness_start_date >= p_start_date)
order by sickness_start_date desc;
select sickness_start_date,
sickness_end_date,
absence_attendance_id
from per_absence_attendances per,
per_periods_of_service service
where per.person_id = p_person_id
and per.sickness_start_date is not null
and per.sickness_start_date < p_sickness_start_date
and (p_start_date is null or
per.sickness_start_date >= p_start_date)
and per.sickness_start_date between service.DATE_START and nvl(service.actual_termination_date,hr_general.end_of_time)
and service.PERIOD_OF_SERVICE_ID = l_PERIOD_OF_SERVICE_ID
order by sickness_start_date desc;
select PERIOD_OF_SERVICE_ID from per_periods_of_service service
where PERSON_ID = p_person_id
and p_start_date between DATE_START and nvl(service.actual_termination_date,hr_general.end_of_time);
select sickness_start_date,
sickness_end_date,
absence_attendance_id
from per_absence_attendances
where person_id = p_person_id
and sickness_start_date is not null
and sickness_start_date < p_sickness_start_date
and (p_start_date is null or
sickness_start_date >= p_start_date)
order by sickness_start_date desc;
select sickness_start_date,
sickness_end_date,
absence_attendance_id
from per_absence_attendances per,
per_periods_of_service service
where per.person_id = p_person_id
and per.sickness_start_date is not null
and per.sickness_start_date < p_sickness_start_date
and (p_start_date is null or
per.sickness_start_date >= p_start_date)
and per.person_id = service.person_id
and per.sickness_start_date between service.DATE_START and nvl(service.actual_termination_date,hr_general.end_of_time)
and service.PERIOD_OF_SERVICE_ID = l_PERIOD_OF_SERVICE_ID
order by sickness_start_date desc;
select PERIOD_OF_SERVICE_ID
from per_periods_of_service service
where PERSON_ID = p_person_id
and p_start_date between DATE_START and nvl(service.actual_termination_date,hr_general.end_of_time);
select sickness_start_date,
sickness_end_date,
absence_attendance_id
from per_absence_attendances
where person_id = p_person_id
and sickness_start_date is not null
and sickness_start_date > p_sickness_start_date
and (p_start_date is null or
sickness_start_date < p_start_date)
order by sickness_start_date;
select sickness_start_date,
sickness_end_date,
absence_attendance_id
from per_absence_attendances
where person_id = p_person_id
and sickness_start_date is not null
and sickness_start_date > p_sickness_start_date
and (p_start_date is null or
sickness_start_date < p_start_date)
order by sickness_start_date;
select sickness_start_date,
sickness_end_date,
absence_attendance_id
from per_absence_attendances
where person_id = p_person_id
and sickness_start_date is not null
and sickness_start_date > p_sickness_start_date
and (p_start_date is null or
sickness_start_date < p_start_date)
order by sickness_start_date;
select start_date
, end_date
from hr_calendar_usages
where purpose_usage_id||null = l_purpose_usage_id
and primary_key_value = l_primary_key_value
and end_date >= p_period_from
and start_date <= p_period_to;
select application_id
into l_pa_appid
from fnd_application
where upper(application_short_name) = upper('SSP');
select stp.*
from ssp_stoppages STP
where stp.absence_attendance_id = g_PIW_id
and ((stp.override_stoppage <> 'Y' and stp.user_entered <> 'Y')
or
(stp.withhold_from > PIW_end_date));
select nvl(sickness_end_date, l_01_October_06 - 1)
from per_absence_attendances
where ((sickness_end_date is null and sickness_start_date < l_01_October_06)
or
sickness_end_date < l_01_October_06)
and person_id = p_absence.person_id
and sickness_start_date is not null
order by sickness_end_date DESC;
select nvl (min (withhold_from),hr_general.end_of_time) -1
from ssp_stoppages
where absence_attendance_id = g_PIW_id
and override_stoppage <> 'Y'
and withhold_to is null;
select withhold_from, withhold_to
from ssp_stoppages
where absence_attendance_id = g_PIW_id
and override_stoppage = 'N'
and withhold_to is not null
and ((p_effective_start_date <= withhold_from and
p_effective_end_date >= nvl(withhold_to,hr_general.end_of_time)
)
or
(p_effective_end_date >= withhold_from and
p_effective_start_date <= nvl(withhold_to,hr_general.end_of_time)
))
union all
select withhold_from, withhold_to
from ssp_stoppages ss,
per_absence_attendances paa
where paa.linked_absence_id = g_PIW_id
and paa.absence_attendance_id = ss.absence_attendance_id
and override_stoppage = 'N'
and withhold_to is not null
and ((p_effective_start_date <= withhold_from and
p_effective_end_date >= nvl(withhold_to,hr_general.end_of_time)
)
or
(p_effective_end_date >= withhold_from and
p_effective_start_date <= nvl(withhold_to,hr_general.end_of_time)
));
select piv.default_value,
piv.effective_start_date,
piv.effective_end_date
from pay_input_values_f PIV,
pay_element_types_f ELT
where elt.element_type_id = piv.element_type_id
and elt.element_type_id = g_SSP_legislation.element_type_id
and p_effective_date between piv.effective_start_date and piv.effective_end_date
and p_effective_date between elt.effective_start_date and elt.effective_end_date
and piv.input_value_id = g_SSP_legislation.rate_id;
select maternity_id,
object_version_number,
MPP_start_date,
due_date, -- Need due_date to decide on value of sickness_trigger_weeks
ssp_smp_pkg.expected_week_of_confinement(due_date) EWC
from ssp_maternities
where person_id = p_absence.person_id
and nvl(leave_type,'MA') = 'MA';
hr_utility.trace(l_proc||'About to update MPP Start Date');
hr_utility.trace(l_proc||'About to update MPP Start Date');
hr_utility.trace(l_proc||'About to update MPP Start Date');
select 1
from ssp_stoppages stp,
ssp_withholding_reasons rea
where stp.absence_attendance_id = g_PIW_id
and stp.withhold_from <= p_start
and stp.withhold_to >= p_end
and stp.reason_id = rea.reason_id
and rea.reason = 'Employee too old';
select average_earnings_amount
from ssp_earnings_calculations
where person_id = p_absence.person_id
and effective_date = PIW_start_date;
select 1
from ssp_medicals
where absence_attendance_id = c_absence_id
and evidence_status = 'CURRENT';
select org_information1 EVIDENCE_REQUIRED
from hr_organization_information
where organization_id = c_business_id
and org_information_context = 'Sickness Control Rules';
select fnd_date.canonical_to_date(ppf.per_information11)
from per_all_people_f ppf
where ppf.person_id = p_absence.person_id
and ppf.per_information_category = 'GB'
and sysdate between ppf.effective_start_date and ppf.effective_end_date;
select entry.element_entry_id,
entry.element_link_id,
entry.assignment_id,
entry.effective_start_date,
entry.effective_end_date,
max(decode(inp.name,c_rate_name,
to_number(hr_chkfmt.changeformat(eev.screen_entry_value,inp.uom,
g_SSP_correction.input_currency_code)),null)) RATE,
/* Use the SSP_correction's input_currency_code as *
* the SSP_legislation global may not be populated */
-------------------------------------------------------------------------
-- Changes for bug 1020757: --
-- --
-- The changeformat function will return the screen_entry_value in the --
-- external display format. This is not always 'DD-MON-YYYY' so that --
-- should not be hardcoded. Better to use fnd_date.chardate_to_date --
-- which will convert the display format to a date. --
-- This will fix bug 1020757. --
-------------------------------------------------------------------------
/* max(decode(inp.name,c_from_name, */
/* to_date(hr_chkfmt.changeformat(eev.screen_entry_value,inp.uom, */
/* g_SSP_correction.input_currency_code),'DD-MON-YYYY'),null)) DATE_FROM, */
/* max(decode(inp.name,c_to_name, */
/* to_date(hr_chkfmt.changeformat(eev.screen_entry_value,inp.uom, */
/* g_SSP_correction.input_currency_code),'DD-MON-YYYY'),null)) DATE_TO, */
max(decode(inp.name,c_from_name,
fnd_date.chardate_to_date(hr_chkfmt.changeformat(eev.screen_entry_value,
inp.uom,g_SSP_correction.input_currency_code)),null)) DATE_FROM,
max(decode(inp.name,c_to_name,
fnd_date.chardate_to_date(hr_chkfmt.changeformat(eev.screen_entry_value,
inp.uom,g_SSP_correction.input_currency_code)),null)) DATE_TO,
-- End of Changes for bug 1020757
------------------------------------------------------------------------
max(decode(inp.name,c_amount_name,
to_number(hr_chkfmt.changeformat(eev.screen_entry_value,inp.uom,
g_SSP_correction.input_currency_code)),null)) AMOUNT,
max(decode(inp.name,c_withheld_days_name,
to_number(hr_chkfmt.changeformat(eev.screen_entry_value,inp.uom,
g_SSP_correction.input_currency_code)),null)) WITHHELD_DAYS,
max(decode(inp.name,c_SSP_weeks_name,
to_number(hr_chkfmt.changeformat(eev.screen_entry_value,inp.uom,
g_SSP_correction.input_currency_code)),null)) SSP_WEEKS,
max(decode(inp.name,c_SSP_days_due_name,
to_number(hr_chkfmt.changeformat(eev.screen_entry_value,inp.uom,
g_SSP_correction.input_currency_code)),null)) SSP_DAYS_DUE,
max(decode(inp.name,c_qualifying_days_name,
to_number(hr_chkfmt.changeformat(eev.screen_entry_value,inp.uom,
g_SSP_correction.input_currency_code)),null)) QUALIFYING_DAYS
from pay_input_values_f inp,
pay_element_entry_values_f eev,
pay_element_entries_f ENTRY,
per_all_assignments_f ASGT /* adding this join speeds up the entry query */
where creator_type = c_SSP_creator_type
and creator_id = g_PIW_id
and asgt.person_id = absentee.person_id
and asgt.assignment_id = entry.assignment_id
and entry.effective_start_date between asgt.effective_start_date
and asgt.effective_end_date
and eev.element_entry_id = entry.element_entry_id
and eev.effective_start_date between entry.effective_start_date
and entry.effective_end_date
and eev.input_value_id +0 = inp.input_value_id
and inp.name in (c_rate_name,c_from_name,c_to_name,c_amount_name,
c_withheld_days_name,c_SSP_weeks_name,
c_SSP_days_due_name,c_qualifying_days_name)
and inp.effective_start_date <= eev.effective_end_date
and inp.effective_end_date >= eev.effective_start_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 inp.element_type_id <> g_SSP_correction.element_type_id
/* Do not select reversal entries */
group by entry.element_entry_id, entry.element_link_id, entry.assignment_id,
entry.effective_start_date, entry.effective_end_date;
hr_utility.trace(l_proc||' OLD: Before calling hr_entry_api.insert_element_entry');
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_SSP_creator_type,
p_creator_id => g_PIW_id,
p_entry_type => 'E',
p_input_value_id1 => g_SSP_correction.rate_id,
p_input_value_id2 => g_SSP_correction.from_id,
p_input_value_id3 => g_SSP_correction.to_id,
p_input_value_id4 => g_SSP_correction.amount_id,
p_input_value_id5 => g_SSP_correction.withheld_days_id,
p_input_value_id6 => g_SSP_correction.SSP_weeks_id,
p_input_value_id7 => g_SSP_correction.SSP_days_due_id,
p_input_value_id8 => g_SSP_correction.qualifying_days_id,
p_entry_value1 => old_entry.rate,
-- The following two lines will implicitly convert the dates
-- to varchar values. This will use the default format (NLS
-- setting) for a date which is what the date_from and
-- data_to UoM is set to in 11i (=Date).
p_entry_value2 => old_entry.date_from,
p_entry_value3 => old_entry.date_to,
p_entry_value4 => old_entry.amount * -1,
p_entry_value5 => old_entry.withheld_days * -1,
p_entry_value6 => old_entry.SSP_weeks * -1,
p_entry_value7 => old_entry.SSP_days_due * -1,
p_entry_value8 => old_entry.qualifying_days * -1);
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);
<>
--
-- Now go through the new entries, inserting any which are not marked as
-- having been dealt with already.
--
hr_utility.set_location (l_proc,4);
hr_utility.trace(l_proc||' NEW: Before calling hr_entry_api.insert_element_entry');
hr_entry_api.insert_element_entry (
p_effective_start_date => new_entry.effective_start_date (Y),
p_effective_end_date => new_entry.effective_end_date (Y),
p_element_entry_id => l_dummy,
p_assignment_id => new_entry.assignment_id (Y),
p_element_link_id => new_entry.element_link_id (Y),
p_creator_type => c_SSP_creator_type,
p_creator_id => g_PIW_id,
p_entry_type => 'E',
p_input_value_id1 => g_SSP_legislation.rate_id,
p_input_value_id2 => g_SSP_legislation.from_id,
p_input_value_id3 => g_SSP_legislation.to_id,
p_input_value_id4 => g_SSP_legislation.amount_id,
p_input_value_id5 => g_SSP_legislation.withheld_days_id,
p_input_value_id6 => g_SSP_legislation.SSP_weeks_id,
p_input_value_id7 => g_SSP_legislation.SSP_days_due_id,
p_input_value_id8 => g_SSP_legislation.qualifying_days_id,
p_entry_value1 => new_entry.rate (Y),
-- Fix for bug 1020757:
-- This explicit conversion is not possible anymore since we don't know
-- what the format is going to be. We need to use fnd_date,date_to_chardate
-- p_entry_value2 => to_char(new_entry.date_from(Y),'DD-MON-YYYY'),
-- p_entry_value3 => to_char(new_entry.date_to(Y),'DD-MON-YYYY'),
p_entry_value2 => fnd_date.date_to_chardate(new_entry.date_from(Y)),
p_entry_value3 => fnd_date.date_to_chardate(new_entry.date_to(Y)),
p_entry_value4 => new_entry.amount (Y),
p_entry_value5 => new_entry.withheld_days (Y),
p_entry_value6 => new_entry.SSP_weeks (Y),
p_entry_value7 => new_entry.SSP_days_due (Y),
p_entry_value8 => new_entry.qualifying_days (Y));
hr_utility.trace('inserting record for absence '||to_char(p_absence_id)||
'deleting is '||l_deleting_ch);
insert into ssp_temp_affected_rows (PIW_id, p_deleting, locked)
select p_absence_id, l_deleting_ch, userenv('sessionid')
from sys.dual
where not exists
(select null
from ssp_temp_affected_rows t2
where t2.PIW_id = p_absence_id);
select nvl (linked_absence_id, absence_attendance_id) PIW_ID
from per_absence_attendances
where sickness_start_date
<= (p_date_start + g_SSP_legislation.linking_period_days)
and person_id = p_person_id;
select linked_absence_id,
absence_attendance_id,
sickness_start_date,
sickness_end_date
from per_absence_attendances
where person_id = p_person_id
and sickness_start_date is not null;
delete ssp_temp_affected_rows
where PIW_id = g_PIW_id;
select *
from per_absence_attendances
where sickness_start_date = p_effective_date
and person_id = p_person_id;
select count(*)
from ssp_withholding_reasons swr,
ssp_stoppages stp
where stp.absence_attendance_id = p_absence_attendance_id
and stp.reason_id = swr.reason_id
and upper(swr.reason) = 'RE-HIRED EMPLOYEE,PLEASE CHECK';