[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
22 Aug 96 C Barbieri Deleted function maternity_leave_exists.
With Oracle 7.3.2 it is not possible to
reference a function that returns a
BOOLEAN inside a SELECT statement.
DATE AUTHOR VERSION BUG NO DESCRIPTION
---- ------ ------- ------ -----------
06 Jan 97 M Fender 434233 Fully qualified call to function within
cursor period_of_service with package
name. This is a workaround to a bug in
PL/SQL which is fixed in version 2.3.3.
See related bug 410159 for details.
04-Feb-97 RThirlby 30.34 447690 Altered function continous_employment-
_date to return correct date.
18-Apr-97 RThirlby 30.35 479378 Cursor csr_personal_details altered to
allow for rehired employees - added
subquery with max function on date_start
12-Dec-97 RThirlby 30.36 590966 Fix.
08-Jan-98 RThirlby 30.37 SMP Entries problem solved - on absence
delete the entries were not deleted, or
correction entries were not created if
the entries had been through a payroll
run.
06-Apr-98 AParkes 30.38 648313 Prevented raising of no_data_found in
new_entries block. Changed hr_trace
outputs to prevent errors.
08-Apr-98 AParkes 30.39 653276 Performance fix to csr_existing_entries
cursor; drove from per_assignments using
selection of correction entries; used
altered to update existing unprocessed
entries covering the same week, but
having incorrect amounts.
30-JUL-98 A.Myers 30.41 705553 Added date formatting around week-
commencing passed in attribute 2 to
procedure insert_element_entry (2 calls)
31-JUL-98 A.Myers 30.42 701750 Only inserting temp_affected row if it
does not already exist (new procedure).
01-NOV-99 M.Vilrokx 110.8 960689 Added service.date_start to the cursor
csr_personal_details. This date will be
passed to ssp_ern_ins.ins in stead of the
QW date if the latter is smaller than the
service.date_start. When this is not done
the ssp_ern_ins.ins procedure will try to
validate the QW and raise error 35049
because it is before the effective start
date of the person.
06-JAN-2000 ILeath 110.9 1021179 Remove the stoppage for
check_birth_confirmation. A stoppage
should not be created if the woman
notifies her employer of the date the
baby was born.
11-AUG-2000 DFoster 110.10 1304683 Amended the average_earnings function so
that it calls the nwe version of the
calculate_average_earnings which can
treat Sicknesses and Maternities
differently.
30-MAY-2002 SMRobins 115.9 Changes to add changes for APR 2003
legislation. Added woman.MPP_start_date
to call to Calculate_SMP_amounts, and
so extra p_MPP_start_date parameter
to Calculate_SMP_amounts procedure.
Then code splits within
Calculate_SMP_amounts dependent on
whether MPP start date before or
after 06-APR-2003. The code for prior
to 06-APR-2003 can be removed once
this date has elapsed.
28-OCT-2002 SHVEERAB 115.10 2620611 Do not create stoppage for pre-matured
birth of baby.
06-NOV-2002 GButler 115.12 2649315 Changes to EXPECTED_WEEK_OF_CONFINEMENT,
CONTINUOUS_EMPLOYMENT_DATE
functions and CHECK_DEATH procedure to resolve
translation issues
13-NOV-2002 GButler 115.13 2620413 Change to entitled_to_smp, check on
creation of late notification stoppages
15-NOV-2002 Bhaskar 115.14 2663735 New procedure check_employment_qw is
created to check for employee
terminated in qualifying week.
15-NOV-2002 Bhaskar 115.14 2663899 Check for employee death in the
MPP pay period.
22-NOV-2002 BTHAMMIN 115.16 2663899 SMP was paid one week more than
required
27-JAN-2003 GButler 115.17 nocopy fix to average_earnings - 2nd
dummy variable created as placeholder
to OUT param from ssp_ern_ins
24-FEB-2003 ABlinko 115.18 2811430 Amended csr_maternity in earnings_control
for SAP/SPP
17-APR-2003 MMAhmad 115.19 2801805 Amended code for Late Absence Notification
14-MAY-2003 GButler 115.20 2939058 Changes to csr_period_of_service_qw in
check_employment_qw to resolve bug
25-AUG-2003 asengar 115.21 3111736 Added to_number to make it compatible for 10g
25-FEB-2004 asengar 115.22 3436510 Cursor period_of_service and Procedure
3429978 entitled_to_SMP have been modified.
29-MAR-2004 skhandwa 115.23 3510141 Added condition to generate Late evidence stoppage
15-JUN-2004 ssekhar 115.24 3693735 Added to_number to make it compatible for 10g
12-JUL-2004 ablinko 115.25 3682122 Changes for recalculating lump sum updates
11-OCT-2005 npershad 115.26 4621910 Fixed two problems with date conversion,
one in the cursor csr_existing_entries
and twice when calling insert_element_entry
09-FEB-2006 kthampan 115.27 4891953 Fixed performance bug.
21-MAR-2006 kthampan 115.28 5105039 Added function to calculate max SMP paid date.
16-JUN-2006 ajeyam 115.29 5210118 In check_continuity_rule procedure,
period_of_service cursor changed to fetch the
latest start date for rehired persons.
23-AUG-2006 kthampan 115.30 5482199 Change from per_people_f and per_assignments_f
to per_all_people_f and per_all_assignments_f
19-SEP-2006 kthampan 115.31 5547703 Amend smp_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
19-OCT-2006 kthampan 115.32 5604330 Amend check_death to create the stoppage based
on the '7 day rolling weeks', currently it
will always created using the next Sunday after
the date of death.
09-DEC-2006 kthampan 115.33 5706912 Amend procedure ins_ssp_temp_affected_rows_mat
and person_control
06-JAN-2009 npannamp 115.34 7680593 In csr_existing_entries cursor, trimmed
the Group seperator before calling to_number.
'HR: Number Separator' profile causing issue.
15-May-2009 pbalu 115.35 8470655 Problems with 'Some work done' is corrected.
20-May-2009 pbalu 115.36 8470655 Changes for Code review coments
20-May-2009 npannamp 115.38 12949461 check_average_earnings procedure modified to get
NI Lel value as on QW + 6 instead of as on QW.
*/
--------------------------------------------------------------------------------
g_package varchar2(33) := ' ssp_smp_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
order by absence.date_start;
select maternity.person_id,
maternity.due_date,
ssp_smp_pkg.qualifying_week (due_date) QW,
ssp_smp_pkg.expected_week_of_confinement (due_date) EWC,
maternity.maternity_id,
maternity.actual_birth_date,
maternity.live_birth_flag,
maternity.start_date_with_new_employer,
maternity.MPP_start_date,
maternity.notification_of_birth_date,
maternity.start_date_maternity_allowance,
maternity.pay_SMP_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
--
-- Bug 2663899
-- When employee is terminated, the person.date_of_death
-- is null in the old record. Check the dates with
-- actual termination date+1
--
and nvl(service.actual_termination_date+1,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 = woman.person_id
and effective_date = l_effective_date;
select count (*)
from per_absence_attendances
where person_id = woman.person_id
and 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 = woman.person_id
and ETYPE.element_name = c_SMP_element_name
and ETYPE.legislation_code = 'GB'
and ENTRY.element_type_id = ETYPE.element_type_id
and entry.creator_type = c_SMP_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_SMP_Corr_element_name);
select stoppage_id
from ssp_stoppages
where user_entered <>'Y'
and override_stoppage <> 'Y'
and maternity_id = p_maternity_id;
select 1
from per_periods_of_service
where person_id = woman.person_id
and ssp_smp_pkg.continuous_employment_date(woman.due_date) >=
(select max(date_start)
from per_periods_of_service
where person_id = woman.person_id
);
select nvl(ser.actual_termination_date, hr_general.end_of_time) termination_date
,leaving_reason leaving_reason
from per_periods_of_service ser
where ser.person_id = woman.person_id
and ssp_smp_pkg.continuous_employment_date(woman.due_date)
between ser.date_start and nvl(ser.actual_termination_date, hr_general.end_of_time);
select upper(meaning)
from hr_lookups
where lookup_type = 'LEAV_REAS'
and lookup_code = p_leaving_reason
and enabled_flag = 'Y' ;
select to_char(mpp_start_date,'DAY')
from dual;
select *
from ssp_medicals
where maternity_id = woman.maternity_id
and evidence_status = 'CURRENT';
select entry.element_entry_id,
entry.element_link_id,
entry.assignment_id,
entry.effective_start_date,
entry.effective_end_date,
decode(ssp_smp_support_pkg.value(entry.element_entry_id,
ssp_smp_pkg.c_rate_name),'HIGH',l_high_rate,l_low_rate) RATE,
/*to_date (ssp_smp_support_pkg.value
(entry.element_entry_id,
ssp_smp_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_smp_pkg.c_week_commencing_name)) WEEK_COMMENCING,
-- Start Bug Fix for 7680593
/*
to_number(ssp_smp_support_pkg.value (entry.element_entry_id,
ssp_smp_pkg.c_amount_name)) AMOUNT,
to_number(ssp_smp_support_pkg.value (entry.element_entry_id,
ssp_smp_pkg.c_recoverable_amount_name)) RECOVERABLE_AMOUNT
*/
to_number(replace(ssp_smp_support_pkg.value (entry.element_entry_id,
ssp_smp_pkg.c_amount_name), l_group_separator, '')) AMOUNT,
to_number(replace(ssp_smp_support_pkg.value (entry.element_entry_id,
ssp_smp_pkg.c_recoverable_amount_name), l_group_separator, '')) RECOVERABLE_AMOUNT
-- End Bug Fix for 7680593
from pay_element_entries_f ENTRY,
per_all_assignments_f asg
where creator_type = c_SMP_creator_type
and creator_id = p_maternity_id
and asg.person_id = woman.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_SMP_Corr_element_name);
select count (*)
from per_absence_attendances
where person_id = woman.person_id
and 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 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 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_SMP_element.rate_id,
p_input_value_id2 => g_SMP_element.amount_id,
p_input_value_id3 => g_SMP_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_utility.trace('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_SMP_creator_type,
p_creator_id => p_maternity_id,
p_entry_type => c_SMP_entry_type,
p_input_value_id1=> g_SMP_correction_element.rate_id,
p_input_value_id2=> g_SMP_correction_element.week_commencing_id,
p_input_value_id3=> g_SMP_correction_element.amount_id,
p_input_value_id4=> g_SMP_correction_element.recoverable_amount_id,
p_entry_value1=> old_entry.rate,
--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_SMP_creator_type,
p_creator_id => p_maternity_id,
p_entry_type => c_SMP_entry_type,
p_input_value_id1 => g_SMP_element.rate_id,
p_input_value_id2 => g_SMP_element.week_commencing_id,
p_input_value_id3 => g_SMP_element.amount_id,
p_input_value_id4 => g_SMP_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
-- Bug 2663899
and stp.withhold_from <= p_end_date))
--
and stp.maternity_id = p_maternity_id
and stp.reason_id = wre.reason_id;
select meaning
from hr_lookups
where lookup_type = 'SMP_RATES'
and lookup_code = p_rate_band;
select 1
from ssp_maternities
where maternity_id = p_maternity_id;
select /*+ ORDERED use_nl(paa,paaf,etype,entry) */
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);
g_smp_update := 'N';
insert into ssp_temp_affected_rows (MATERNITY_ID, p_deleting, locked)
select p_maternity_id, l_deleting_ch, userenv('sessionid')
from sys.dual
where not exists
(select null
from ssp_temp_affected_rows t2
where t2.maternity_id = p_maternity_id);
g_smp_update := 'N';
g_smp_update := 'Y';
select mat.maternity_id
from ssp_maternities mat
where mat.person_id = p_person_id
and p_effective_date = decode(mat.leave_type,
'AD',ssp_sap_pkg.MATCHING_WEEK_OF_ADOPTION(mat.matching_date),
'PA',ssp_pad_pkg.MATCHING_WEEK_OF_ADOPTION(mat.matching_date),
'PB',ssp_pab_pkg.QUALIFYING_WEEK(mat.due_date),
'AA',ssp_apad_pkg.MATCHING_WEEK_OF_ADOPTION(mat.matching_date),
'AB',ssp_apab_pkg.QUALIFYING_WEEK(mat.due_date),
ssp_smp_pkg.QUALIFYING_WEEK(mat.due_date))
and exists (select 1
from per_absence_attendances abs
where abs.person_id = p_person_id
and abs.maternity_id = mat.maternity_id);
select maternity.maternity_id
from ssp_maternities MATERNITY,
pay_element_types_f ELEMENT
where maternity.person_id = p_person_id
and element.legislation_code = 'GB'
and element.element_name = c_SMP_element_name
and maternity.due_date between element.effective_start_date
and element.effective_end_date
and p_date_of_death <= maternity.MPP_start_date
+ (element.element_information6 * 7)
and EXISTS (
select 1
from per_absence_attendances ABSENCE
where ABSENCE.maternity_id = MATERNITY.maternity_id
);
select mpp_start_date, due_date
from ssp_maternities
where maternity_id = p_maternity_id;
select to_number(element_information4)
from pay_element_types_f
where element_name = c_SMP_element_name
and l_due_date between effective_start_date and effective_end_date;
select to_number(element_information4)
from pay_element_types_f
where element_name = c_SMP_element_name
and p_due_date between effective_start_date and effective_end_date;