[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
modified Insert_row, Update_Row and
Lock_Row to include new columns.
15-SEP-95 SSETHI Removed calls to
app_exception.raise_exception
3 Oct 95 N Simpson Added code to Insert/Update/Delete Row
procedures to validate SSP Periods of Incapacity
for Work.
6-OCT-95 S Sethi Added constraint exception handling to
insert and update statements.
12-OCT-95 N Simpson Commented out references to objects which will
not be delivered until SSP delivery. These
commented out bits should be uncommented at that
time.
13-OCT-95 J Thuringer Replaced 'PACKAGE_NAME' variable by
hardcoded 'PER_ABSENCE_ATTENDANCES_PKG';
110.5 07-SEP-1998 JARTHURT Added parameters to insert, update
and lock routine to deal with the new
DDF fields
110.6 22-DEC-1998 725730 A.Myers Added facility to update MPP date if
it is not null. Added procedures
get_mat_details, Delete_Row overload
and get_earliest_abs_start_date.
22-DEC-1998 673835 A.Myers C.Tredwin fix forward ported.
Altered is_emp_entitled. Changed
cursor to pick up element entries for
the correct element type.
110.7 19-Jan-1999 n/a C.Tredwin Replaced get_net_accrual call in
is_emp_entitled with new pto accrual
procedure per_accrual_calc_functions.
get_net_accrual.
110.8 19-Jan-1999 n/a C.Tredwin Correction to 110.7
110.9 19-Jan-1999 n/a C.Tredwin Correction to 110.8
110.10 20-Jan-1999 n/a C.Tredwin Corrected call to get_net_accrual,
the function having changed as a
result of the pto accruals upgrade.
115.9 11-Mar-1999 835944 S.Billing Altered cursor c15,
added employment category to link
selection criteria
115.10 12-Oct-1999 SCNair Date track position related changes
115.16 22-Oct-1999 Ctredwin Added Batch_Id to insert and
update procedures.
115.16 07-Feb-2000 1184545 CTredwin Amended get_defaults to calculate
days in hours value correctly, ie
(end date - start date + 1) * 24
115.17 24-Jul-2000 1337672 CTredwin Amended get_defaults again to
correct default hours calculation
115.19 19-Oct-2000 1408379 GPerry Fixed WWBUG 1408379.
Provided hooks to OAB.
115.20 24-Jan-2002 2189311 Kparekh Took out the default null values from
the 'insert_row' procedure to match
the parameter values in the header.
115.21 09-JUL-2002 mmudigon Absences related changes
Added new proc populate_ben_absence_rec.
Also added a call to ler_chk in
delete_row
115.22 11-JUL-2002 mmudigon Added dbdrv commands
115.24 23-JUL-2003 2829746 ablinko amendments for SAP/SPP
115.25 25-Aug-2003 3111653 skota added to_number() for explicit
conversion. Seems implicit conversion
is not taking place in 10G
115.26 06-NOV-2003 3156665 kjagadee Modified per_absence_attendances_pkg.get_defaults
procedure to calculate the drfault hours in a day
as 24 hours.
115.27 29-JAN-2004 3387265 smparame Modified inti_form procedure.
upper(formula_type_name) is changed to formula
name in the cursor c21 to avoid FTS.
115.28 27-APR-2004 3580603 kthampan Change the condition in the if statement
in procedure default_MPP_start_date.
115.29 29-JUN-2004 3648445 kjagadee Performance repository fixes.
115.30 14-JUL-2004 3694165 ablinko Now calls earliest_ppp_start_date with
actual_birth_date instead of due_date
115.32 17-Nov-2010 9897338 npannamp Modified default_MPP_start_date and
reset_MPP_start_date_on_delete procedures
for ASPP Legislative changes.
115.34 14-Dec-2010 9897338 npannamp Added chk_apl_empl_continuity procedure
for ASPP Legislative changes.
120.0.12000000.4 05-04-2011 9715011 lbodired Modified the procedure is_emp_entitled
120.0.12000000.5 07-12-2011 12850183 rajganga Modified default_MPP_start_date procedure to
include validations for APL duration.
120.0.12000000.6 05-Jan-2011 13486124 npannamp Added check for Adjusted Service date in procedure
chk_apl_empl_continuity.
-------+------------+---------+----------+------------------------------------------------
*/
g_package varchar2(33) := ' per_absence_attendances_pkg.';
select date_start, rowid
from per_absence_attendances
where maternity_id = p_maternity_id;
procedure MPP_update_mode (p_update_mode in number) is
--
l_proc varchar2 (72) := g_package||'MPP_update_mode';
if p_update_mode = 1
then
g_mpp_update_mode := TRUE;
g_mpp_update_mode := FALSE;
end MPP_update_mode;
procedure reset_MPP_start_date_on_delete (p_maternity_id in number) is
--
cursor csr_MPP is
select MPP_start_date,
leave_type,
object_version_number,
decode(leave_type,
'AD',ssp_sap_pkg.earliest_APP_start_date(due_date),
'PA',ssp_pad_pkg.earliest_PPP_start_date(due_date),
'PB',ssp_pab_pkg.earliest_PPP_start_date(actual_birth_date),
'AB',ssp_apab_pkg.earliest_ASPPP_start_date(actual_birth_date),
'AA',ssp_apad_pkg.earliest_ASPPP_start_date(placement_date),
ssp_smp_pkg.earliest_MPP_start_date (due_date))
earliest_MPP_start_date
from ssp_maternities
where maternity_id = p_maternity_id;
l_proc varchar2 (72) := g_package||'reset_MPP_start_date_on_delete';
if g_mpp_update_mode = TRUE
then
--
-- If we are deleting an absence, then reset the MPP start date IF there are
-- still absences left to provide data to do this.
--
open csr_MPP;
g_mpp_updated_date := l_default_MPP_start_date;
end reset_MPP_start_date_on_delete;
select MPP_start_date,
object_version_number,
decode(leave_type,
'AD',ssp_sap_pkg.earliest_APP_start_date(due_date),
'PA',ssp_pad_pkg.earliest_PPP_start_date(due_date),
'PB',ssp_pab_pkg.earliest_PPP_start_date(actual_birth_date),
'AB',ssp_apab_pkg.earliest_ASPPP_start_date(actual_birth_date),
'AA',ssp_apad_pkg.earliest_ASPPP_start_date(placement_date),
ssp_smp_pkg.earliest_MPP_start_date (due_date))
earliest_MPP_start_date,
ssp_smp_pkg.qualifying_week (due_date) QW,
nvl(mpp_start_date,hr_general.end_of_time) MPP,
person_id,
leave_type,
placement_date,
due_date,
actual_birth_date,
partner_stat_pay_start_date,
partner_return_to_work,
partner_death_date
from ssp_maternities
where maternity_id = p_maternity_id;
l_mpp_updated_date date := g_mpp_updated_date;
select decode(l_maternity.partner_death_date,null,to_number (ele1.element_information12),to_number (ele1.element_information13))
into l_apl_max_weeks
from pay_element_types_f ele1
where ele1.element_name = ssp_apab_pkg.c_APAB_element_name
and l_maternity.due_date between ele1.effective_start_date
and ele1.effective_end_date;
select decode(l_maternity.partner_death_date,null,to_number (ele1.element_information11),to_number (ele1.element_information12))
into l_apl_max_weeks
from pay_element_types_f ele1
where ele1.element_name = ssp_apad_pkg.c_APAD_element_name
and l_maternity.due_date between ele1.effective_start_date
and ele1.effective_end_date;
g_mpp_update_mode = TRUE
)
-- and the absence does not end before the earliest MPP start date
and l_end_date >= l_maternity.earliest_MPP_start_date
then
get_earliest_abs_start_date(p_maternity_id,
l_earliest_abs_start_date,
l_earliest_abs_rowid,
l_nos_absences);
g_mpp_updated_date := l_default_MPP_start_date;
select *
from per_absence_attendances
where absence_attendance_id = p_absence_attendance_id;
PROCEDURE Insert_Row(X_Rowid IN OUT NOCOPY VARCHAR2,
X_Absence_Attendance_Id IN OUT NOCOPY NUMBER,
X_Business_Group_Id NUMBER,
X_Absence_Attendance_Type_Id NUMBER,
X_Abs_Attendance_Reason_Id NUMBER,
X_Person_Id NUMBER,
X_Authorising_Person_Id NUMBER,
X_Replacement_Person_Id NUMBER,
X_Period_Of_Incapacity_Id NUMBER,
X_Absence_Days NUMBER,
X_Absence_Hours NUMBER,
X_Comments VARCHAR2,
X_Date_End DATE,
X_Date_Notification DATE,
X_Date_Projected_End DATE,
X_Date_Projected_Start DATE,
X_Date_Start DATE,
X_Occurrence NUMBER,
X_Ssp1_Issued VARCHAR2,
X_Time_End VARCHAR2,
X_Time_Projected_End VARCHAR2,
X_Time_Projected_Start VARCHAR2,
X_Time_Start VARCHAR2,
X_Attribute_Category VARCHAR2,
X_Attribute1 VARCHAR2,
X_Attribute2 VARCHAR2,
X_Attribute3 VARCHAR2,
X_Attribute4 VARCHAR2,
X_Attribute5 VARCHAR2,
X_Attribute6 VARCHAR2,
X_Attribute7 VARCHAR2,
X_Attribute8 VARCHAR2,
X_Attribute9 VARCHAR2,
X_Attribute10 VARCHAR2,
X_Attribute11 VARCHAR2,
X_Attribute12 VARCHAR2,
X_Attribute13 VARCHAR2,
X_Attribute14 VARCHAR2,
X_Attribute15 VARCHAR2,
X_Attribute16 VARCHAR2,
X_Attribute17 VARCHAR2,
X_Attribute18 VARCHAR2,
X_Attribute19 VARCHAR2,
X_Attribute20 VARCHAR2 ,
X_Linked_Absence_id NUMBER,
X_Sickness_Start_Date DATE,
X_Sickness_End_Date DATE,
X_Accept_Late_Notif_Flag VARCHAR2,
x_reason_for_late_notification VARCHAR2,
X_Pregnancy_Related_Illness VARCHAR2,
X_Maternity_Id NUMBER,
X_Batch_Id NUMBER default null,
X_Abs_Information_Category VARCHAR2,
X_Abs_Information1 VARCHAR2,
X_Abs_Information2 VARCHAR2,
X_Abs_Information3 VARCHAR2,
X_Abs_Information4 VARCHAR2,
X_Abs_Information5 VARCHAR2,
X_Abs_Information6 VARCHAR2,
X_Abs_Information7 VARCHAR2,
X_Abs_Information8 VARCHAR2,
X_Abs_Information9 VARCHAR2,
X_Abs_Information10 VARCHAR2,
X_Abs_Information11 VARCHAR2,
X_Abs_Information12 VARCHAR2,
X_Abs_Information13 VARCHAR2,
X_Abs_Information14 VARCHAR2,
X_Abs_Information15 VARCHAR2,
X_Abs_Information16 VARCHAR2,
X_Abs_Information17 VARCHAR2,
X_Abs_Information18 VARCHAR2,
X_Abs_Information19 VARCHAR2,
X_Abs_Information20 VARCHAR2,
X_Abs_Information21 VARCHAR2,
X_Abs_Information22 VARCHAR2,
X_Abs_Information23 VARCHAR2,
X_Abs_Information24 VARCHAR2,
X_Abs_Information25 VARCHAR2,
X_Abs_Information26 VARCHAR2,
X_Abs_Information27 VARCHAR2,
X_Abs_Information28 VARCHAR2,
X_Abs_Information29 VARCHAR2,
X_Abs_Information30 VARCHAR2
) IS
CURSOR C IS SELECT rowid FROM per_absence_attendances
WHERE absence_attendance_id = X_absence_attendance_id;
CURSOR C2 IS SELECT per_absence_attendances_s.nextval FROM sys.dual;
select 1
from per_absence_attendances
where person_id = X_person_id
and sickness_start_date is not null
and sickness_start_date > X_sickness_start_date;
INSERT INTO per_absence_attendances(
absence_attendance_id,
business_group_id,
absence_attendance_type_id,
abs_attendance_reason_id,
person_id,
authorising_person_id,
replacement_person_id,
period_of_incapacity_id,
absence_days,
absence_hours,
comments,
date_end,
date_notification,
date_projected_end,
date_projected_start,
date_start,
occurrence,
ssp1_issued,
time_end,
time_projected_end,
time_projected_start,
time_start,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
attribute16,
attribute17,
attribute18,
attribute19,
attribute20,
Linked_Absence_id,
Sickness_Start_Date,
Sickness_End_Date,
Accept_Late_Notification_Flag,
reason_for_notification_delay,
Pregnancy_Related_Illness,
Maternity_Id,
Batch_Id,
abs_information_category,
abs_information1,
abs_information2,
abs_information3,
abs_information4,
abs_information5,
abs_information6,
abs_information7,
abs_information8,
abs_information9,
abs_information10,
abs_information11,
abs_information12,
abs_information13,
abs_information14,
abs_information15,
abs_information16,
abs_information17,
abs_information18,
abs_information19,
abs_information20,
abs_information21,
abs_information22,
abs_information23,
abs_information24,
abs_information25,
abs_information26,
abs_information27,
abs_information28,
abs_information29,
abs_information30
) VALUES (
X_Absence_Attendance_Id,
X_Business_Group_Id,
X_Absence_Attendance_Type_Id,
X_Abs_Attendance_Reason_Id,
X_Person_Id,
X_Authorising_Person_Id,
X_Replacement_Person_Id,
X_Period_Of_Incapacity_Id,
X_Absence_Days,
X_Absence_Hours,
X_Comments,
X_Date_End,
X_Date_Notification,
X_Date_Projected_End,
X_Date_Projected_Start,
X_Date_Start,
X_Occurrence,
X_Ssp1_Issued,
X_Time_End,
X_Time_Projected_End,
X_Time_Projected_Start,
X_Time_Start,
X_Attribute_Category,
X_Attribute1,
X_Attribute2,
X_Attribute3,
X_Attribute4,
X_Attribute5,
X_Attribute6,
X_Attribute7,
X_Attribute8,
X_Attribute9,
X_Attribute10,
X_Attribute11,
X_Attribute12,
X_Attribute13,
X_Attribute14,
X_Attribute15,
X_Attribute16,
X_Attribute17,
X_Attribute18,
X_Attribute19,
X_Attribute20,
X_Linked_Absence_id,
X_Sickness_Start_Date,
X_Sickness_End_Date,
X_Accept_Late_Notif_Flag,
x_reason_for_late_notification,
X_Pregnancy_Related_Illness,
X_Maternity_Id,
X_Batch_Id,
X_Abs_Information_Category,
X_Abs_Information1,
X_Abs_Information2,
X_Abs_Information3,
X_Abs_Information4,
X_Abs_Information5,
X_Abs_Information6,
X_Abs_Information7,
X_Abs_Information8,
X_Abs_Information9,
X_Abs_Information10,
X_Abs_Information11,
X_Abs_Information12,
X_Abs_Information13,
X_Abs_Information14,
X_Abs_Information15,
X_Abs_Information16,
X_Abs_Information17,
X_Abs_Information18,
X_Abs_Information19,
X_Abs_Information20,
X_Abs_Information21,
X_Abs_Information22,
X_Abs_Information23,
X_Abs_Information24,
X_Abs_Information25,
X_Abs_Information26,
X_Abs_Information27,
X_Abs_Information28,
X_Abs_Information29,
X_Abs_Information30
);
END Insert_Row;
SELECT *
FROM per_absence_attendances
WHERE rowid = chartorowid(X_Rowid)
FOR UPDATE of absence_attendance_id NOWAIT;
PROCEDURE Update_Row(X_Rowid VARCHAR2,
X_Absence_Attendance_Id NUMBER,
X_Business_Group_Id NUMBER,
X_Absence_Attendance_Type_Id NUMBER,
X_Abs_Attendance_Reason_Id NUMBER,
X_Person_Id NUMBER,
X_Authorising_Person_Id NUMBER,
X_Replacement_Person_Id NUMBER,
X_Period_Of_Incapacity_Id NUMBER,
X_Absence_Days NUMBER,
X_Absence_Hours NUMBER,
X_Comments VARCHAR2,
X_Date_End DATE,
X_Date_Notification DATE,
X_Date_Projected_End DATE,
X_Date_Projected_Start DATE,
X_Date_Start DATE,
X_Occurrence NUMBER,
X_Ssp1_Issued VARCHAR2,
X_Time_End VARCHAR2,
X_Time_Projected_End VARCHAR2,
X_Time_Projected_Start VARCHAR2,
X_Time_Start VARCHAR2,
X_Attribute_Category VARCHAR2,
X_Attribute1 VARCHAR2,
X_Attribute2 VARCHAR2,
X_Attribute3 VARCHAR2,
X_Attribute4 VARCHAR2,
X_Attribute5 VARCHAR2,
X_Attribute6 VARCHAR2,
X_Attribute7 VARCHAR2,
X_Attribute8 VARCHAR2,
X_Attribute9 VARCHAR2,
X_Attribute10 VARCHAR2,
X_Attribute11 VARCHAR2,
X_Attribute12 VARCHAR2,
X_Attribute13 VARCHAR2,
X_Attribute14 VARCHAR2,
X_Attribute15 VARCHAR2,
X_Attribute16 VARCHAR2,
X_Attribute17 VARCHAR2,
X_Attribute18 VARCHAR2,
X_Attribute19 VARCHAR2,
X_Attribute20 VARCHAR2 ,
X_Linked_Absence_id NUMBER ,
X_Sickness_Start_Date DATE,
X_Sickness_End_Date DATE,
X_Accept_Late_Notif_Flag VARCHAR2,
x_reason_for_late_notification VARCHAR2,
X_Pregnancy_Related_Illness VARCHAR2,
X_Maternity_Id NUMBER,
X_Abs_Information_Category VARCHAR2,
X_Abs_Information1 VARCHAR2,
X_Abs_Information2 VARCHAR2,
X_Abs_Information3 VARCHAR2,
X_Abs_Information4 VARCHAR2,
X_Abs_Information5 VARCHAR2,
X_Abs_Information6 VARCHAR2,
X_Abs_Information7 VARCHAR2,
X_Abs_Information8 VARCHAR2,
X_Abs_Information9 VARCHAR2,
X_Abs_Information10 VARCHAR2,
X_Abs_Information11 VARCHAR2,
X_Abs_Information12 VARCHAR2,
X_Abs_Information13 VARCHAR2,
X_Abs_Information14 VARCHAR2,
X_Abs_Information15 VARCHAR2,
X_Abs_Information16 VARCHAR2,
X_Abs_Information17 VARCHAR2,
X_Abs_Information18 VARCHAR2,
X_Abs_Information19 VARCHAR2,
X_Abs_Information20 VARCHAR2,
X_Abs_Information21 VARCHAR2,
X_Abs_Information22 VARCHAR2,
X_Abs_Information23 VARCHAR2,
X_Abs_Information24 VARCHAR2,
X_Abs_Information25 VARCHAR2,
X_Abs_Information26 VARCHAR2,
X_Abs_Information27 VARCHAR2,
X_Abs_Information28 VARCHAR2,
X_Abs_Information29 VARCHAR2,
X_Abs_Information30 VARCHAR2
) IS
cursor csr_absence is
select *
from per_absence_attendances
where absence_attendance_id = X_absence_attendance_id;
select *
from per_absence_attendances
where rowid = chartorowid(X_rowid);
UPDATE per_absence_attendances
SET
absence_attendance_id = X_Absence_Attendance_Id,
business_group_id = X_Business_Group_Id,
absence_attendance_type_id = X_Absence_Attendance_Type_Id,
abs_attendance_reason_id = X_Abs_Attendance_Reason_Id,
person_id = X_Person_Id,
authorising_person_id = X_Authorising_Person_Id,
replacement_person_id = X_Replacement_Person_Id,
period_of_incapacity_id = X_Period_Of_Incapacity_Id,
absence_days = X_Absence_Days,
absence_hours = X_Absence_Hours,
comments = X_Comments,
date_end = X_Date_End,
date_notification = X_Date_Notification,
date_projected_end = X_Date_Projected_End,
date_projected_start = X_Date_Projected_Start,
date_start = X_Date_Start,
occurrence = X_Occurrence,
ssp1_issued = X_Ssp1_Issued,
time_end = X_Time_End,
time_projected_end = X_Time_Projected_End,
time_projected_start = X_Time_Projected_Start,
time_start = X_Time_Start,
attribute_category = X_Attribute_Category,
attribute1 = X_Attribute1,
attribute2 = X_Attribute2,
attribute3 = X_Attribute3,
attribute4 = X_Attribute4,
attribute5 = X_Attribute5,
attribute6 = X_Attribute6,
attribute7 = X_Attribute7,
attribute8 = X_Attribute8,
attribute9 = X_Attribute9,
attribute10 = X_Attribute10,
attribute11 = X_Attribute11,
attribute12 = X_Attribute12,
attribute13 = X_Attribute13,
attribute14 = X_Attribute14,
attribute15 = X_Attribute15,
attribute16 = X_Attribute16,
attribute17 = X_Attribute17,
attribute18 = X_Attribute18,
attribute19 = X_Attribute19,
attribute20 = X_Attribute20 ,
Linked_Absence_id = X_Linked_Absence_Id,
Sickness_Start_Date = X_Sickness_Start_Date,
Sickness_End_Date = X_Sickness_End_Date,
Accept_Late_Notification_Flag = X_Accept_Late_Notif_Flag,
reason_for_notification_delay = x_reason_for_late_notification,
Pregnancy_Related_Illness = X_Pregnancy_Related_Illness,
Maternity_Id = X_Maternity_Id,
Batch_Id = NULL,
abs_information_category = X_Abs_Information_Category,
abs_information1 = X_Abs_Information1,
abs_information2 = X_Abs_Information2,
abs_information3 = X_Abs_Information3,
abs_information4 = X_Abs_Information4,
abs_information5 = X_Abs_Information5,
abs_information6 = X_Abs_Information6,
abs_information7 = X_Abs_Information7,
abs_information8 = X_Abs_Information8,
abs_information9 = X_Abs_Information9,
abs_information10 = X_Abs_Information10,
abs_information11 = X_Abs_Information11,
abs_information12 = X_Abs_Information12,
abs_information13 = X_Abs_Information13,
abs_information14 = X_Abs_Information14,
abs_information15 = X_Abs_Information15,
abs_information16 = X_Abs_Information16,
abs_information17 = X_Abs_Information17,
abs_information18 = X_Abs_Information18,
abs_information19 = X_Abs_Information19,
abs_information20 = X_Abs_Information20,
abs_information21 = X_Abs_Information21,
abs_information22 = X_Abs_Information22,
abs_information23 = X_Abs_Information23,
abs_information24 = X_Abs_Information24,
abs_information25 = X_Abs_Information25,
abs_information26 = X_Abs_Information26,
abs_information27 = X_Abs_Information27,
abs_information28 = X_Abs_Information28,
abs_information29 = X_Abs_Information29,
abs_information30 = X_Abs_Information30
WHERE rowid = chartorowid(X_rowid);
END Update_Row;
PROCEDURE Delete_Row(X_Rowid VARCHAR2) IS
--
cursor csr_current_absence is
select *
from per_absence_attendances
where rowid = X_ROWID;
DELETE FROM per_absence_attendances
WHERE rowid = chartorowid(X_Rowid);
hr_utility.set_message_token('PROCEDURE', 'delete_row');
END Delete_Row;
PROCEDURE Delete_Row(X_Rowid VARCHAR2, p_maternity_id in number) IS
--
begin
--
Delete_Row(X_Rowid);
reset_MPP_start_date_on_delete (p_maternity_id);
END Delete_Row;
select 'x'
from per_all_people_f pp
where nvl(p_date_start,nvl(p_proj_start,p_sess)) >=
(select min(ppf.effective_start_date)
from per_all_people_f ppf
where pp.person_id = ppf.person_id
and pp.current_employee_flag = ppf.current_employee_flag)
and nvl(p_date_end,nvl(p_proj_end,p_sess)) <=
(select max(ppf.effective_end_date)
from per_all_people_f ppf
where pp.person_id = ppf.person_id
and pp.current_employee_flag = ppf.current_employee_flag)
-- where nvl(p_date_start,nvl(p_proj_start,p_sess)) between
-- pp.effective_start_date and pp.effective_end_date
-- and nvl(p_date_end,nvl(p_proj_end,p_sess)) between
-- pp.effective_start_date and pp.effective_end_date
and pp.person_id = p_auth_id
and pp.current_employee_flag = 'Y';
select 'x'
from per_all_people_f pp
where nvl(p_date_start,nvl(p_date_proj_start,p_sess)) >=
(select min(ppf.effective_start_date)
from per_all_people_f ppf
where pp.person_id = ppf.person_id
and pp.current_employee_flag = ppf.current_employee_flag)
and nvl(p_date_end,nvl(p_date_proj_end,p_sess)) <=
(select max(ppf.effective_end_date)
from per_all_people_f ppf
where pp.person_id = ppf.person_id
and pp.current_employee_flag = ppf.current_employee_flag)
-- where nvl(p_date_start,nvl(p_date_proj_start,p_sess)) between
-- pp.effective_start_date and pp.effective_end_date
-- and nvl(p_date_end,nvl(p_date_proj_end,p_sess)) between
-- pp.effective_start_date and pp.effective_end_date
and pp.person_id = p_rep_id
and pp.current_employee_flag = 'Y';
select 'x'
from per_periods_of_service p
where ((trunc(p.actual_termination_date) < p_date
and p.actual_termination_date is not null)
or (trunc(p.projected_termination_date) < p_date
and p.projected_termination_date is not null))
and p.person_id = p_rep_per;
select 1
from hr_positions_f pp,
per_assignments_f asg
where asg.person_id = p_psn_id
and nvl(p_dstart,nvl(p_proj_start,p_sess)) between
asg.effective_start_date and asg.effective_end_date
and pp.position_id = asg.position_id
and nvl(p_dstart,nvl(p_proj_start,p_sess)) between
pp.effective_start_date and pp.effective_end_date
and pp.replacement_required_flag = 'Y';
select 'x'
from per_absence_attendance_types
where absence_attendance_type_id = p_abat_type
and date_effective <= p_dstart
and (nvl(date_end,p_eot) >= p_dend
or p_dend is null);
select 'x'
from per_absence_attendance_types
where absence_attendance_type_id = p_abat_type
and date_effective <= p_proj_start
and (nvl(date_end,p_eot) >= p_proj_end
or p_proj_end is null);
select 1
from per_absence_attendances t,
per_absence_attendance_types a,
per_absence_attendance_types b
where (p_abat_id is null
or (p_abat_id is not null and p_abat_id <> t.absence_attendance_id))
and t.person_id = p_per_id
and p_abat_type = a.absence_attendance_type_id
and t.absence_attendance_type_id = b.absence_attendance_type_id
and a.absence_category = 'S'
and b.absence_category = 'S'
and t.date_end = p_dstart -1;
select 'x'
from per_absence_attendances t,
per_absence_attendance_types a,
per_absence_attendance_types b
where (p_abat_id is null
or (p_abat_id is not null and p_abat_id <> t.absence_attendance_id))
and t.person_id = p_per_id
and p_abat_type = a.absence_attendance_type_id
and t.absence_attendance_type_id = b.absence_attendance_type_id
and a.absence_category = 'S'
and b.absence_category = 'S'
and p_dend is null
and t.date_end is null;
select 'x'
from per_absence_attendances t,
per_absence_attendance_types a,
per_absence_attendance_types b
where (p_abat_id is null
or (p_abat_id is not null and p_abat_id <> t.absence_attendance_id))
and t.person_id = p_per_id
and t.absence_attendance_type_id = a.absence_attendance_type_id
and a.absence_category = 'S'
and p_abat_type = b.absence_attendance_type_id
and b.absence_category = 'S'
and ((p_dstart between t.date_start and nvl(t.date_end,p_eot))
or (t.date_start between p_dstart and nvl(p_dend,p_eot)));
select 1
from per_absence_attendances t,
per_absence_attendance_types a,
per_absence_attendance_types b
where (p_abat_id is null
or (p_abat_id is not null and p_abat_id <> t.absence_attendance_id))
and t.person_id = p_per_id
and t.absence_attendance_type_id = a.absence_attendance_type_id
and p_abat_type = b.absence_attendance_type_id
and ((p_dstart between t.date_start and nvl(t.date_end,p_eot))
or (t.date_start between p_dstart and nvl(p_dend,p_eot)));
select e.element_entry_id,
e.element_link_id,
a.payroll_id,
t.start_date,
t.end_date
from per_time_periods t,
pay_element_entries_f e,
per_assignments_f a
where a.person_id = p_per_id
and a.primary_flag = 'Y'
and p_sdstart between a.effective_start_date and a.effective_end_date
and e.assignment_id = a.assignment_id
and e.creator_type = 'A'
and e.creator_id = p_abat_id
and p_sdstart between e.effective_start_date and e.effective_end_date
and t.payroll_id = a.payroll_id
and p_sdstart between t.start_date and t.end_date;
select meaning
from hr_lookups
where lookup_type = 'ABSENCE_CATEGORY'
and lookup_code = p_abcat;
select (((substr(p_tend,1,2) * 60) + substr(p_tend,4,2)) -
((substr(p_tstart,1,2) * 60) + substr(p_tstart,4,2))) / 60,
(p_dend - p_dstart + 1) * 24,
(p_dend - p_dstart) + 1
from sys.dual;
select asg.assignment_id,
l.element_link_id,
asg.payroll_id,
'X'
from per_assignments_f asg,
pay_element_types_f t,
pay_element_links_f l
where l.business_group_id + 0 = p_bgroup_id
and t.element_type_id = p_eltype
and asg.person_id = p_per_id
and asg.primary_flag = 'Y'
and p_dstart between asg.effective_start_date and asg.effective_end_date
and t.element_type_id = l.element_type_id
and ((l.payroll_id is not null
and l.payroll_id = asg.payroll_id)
or (l.link_to_all_payrolls_flag = 'Y'
and asg.payroll_id is not null)
or (l.payroll_id is null
and l.link_to_all_payrolls_flag = 'N'))
and (l.organization_id = asg.organization_id
or l.organization_id is null)
and (l.position_id = asg.position_id
or l.position_id is null)
and (l.grade_id = asg.grade_id
or l.grade_id is null)
and (l.location_id = asg.location_id
or l.location_id is null)
and (l.job_id = asg.job_id
or l.job_id is null)
and (l.employment_category = asg.employment_category
or l.employment_category is null)
and p_dstart between l.effective_start_date and l.effective_end_date
and p_dstart between t.effective_start_date and t.effective_end_date
and (l.people_group_id is null
or exists
(select 1
from pay_assignment_link_usages_f lu1
where lu1.assignment_id = asg.assignment_id
and lu1.element_link_id = l.element_link_id
and p_sess between lu1.effective_start_date and lu1.effective_end_date));
select pettl.element_name
from per_absence_attendance_types paa,
pay_element_types_f pet,
pay_element_types_f_tl pettl
where pet.element_type_id = p_eletype
and paa.absence_attendance_type_id = p_abat_type
and pet.element_type_id = pettl.element_type_id
and nvl(p_dstart,paa.date_effective)
between pet.effective_start_date and pet.effective_end_date
and pettl.language = userenv('LANG');
select start_date,
end_date,
'X'
from per_time_periods
where payroll_id = p_cpay_id
and p_dstart between start_date and end_date;
select di.user_name
from ff_database_items di,
ff_user_entities ue
where di.user_entity_id = ue.user_entity_id
and ue.creator_type = 'A'
and ue.creator_id = p_abat_type;
select assignment_id
from per_assignments
where person_id = p_per_id
and primary_flag = 'Y';
select nvl(sum(fnd_number.canonical_to_number(
nvl(pev.screen_entry_value,'0.0'))),0)
from pay_element_entry_values_f pev,
pay_element_entries_f pee,
pay_link_input_values_f lnk,
per_absence_attendance_types abs
where abs.absence_attendance_type_id = p_abs_type_id
and lnk.input_value_id = abs.input_value_id
and pev.input_value_id + 0 = abs.input_value_id
and pee.element_link_id = lnk.element_link_id
and pev.element_entry_id = pee.element_entry_id
and pee.assignment_id = p_ass_id
and to_char(pev.effective_start_date, 'YYYY') =
to_char(p_session_date, 'YYYY')
and to_char(pev.effective_end_date,'YYYY') =
to_char(p_session_date, 'YYYY')
and pev.effective_start_date = pee.effective_start_date
and pev.effective_end_date = pee.effective_end_date
and pee.effective_start_date between
lnk.effective_start_date and lnk.effective_end_date;
select pap.accrual_plan_id,
pap.accrual_category,
pap.accrual_units_of_measure,
asg.payroll_id,
asg.business_group_id
from pay_element_entry_values_f pev,
pay_element_entries_f pee,
pay_input_values_f piv,
pay_accrual_plans pap,
per_absence_attendance_types abst,
per_all_assignments_f asg
where pee.assignment_id = p_ass_id
and abst.absence_attendance_type_id = p_abs_att_type_id
and pap.pto_input_value_id = abst.input_value_id
and pee.element_entry_id = pev.element_entry_id
and piv.input_value_id = pev.input_value_id
and pap.accrual_plan_element_type_id = piv.element_type_id
and piv.element_type_id = pee.element_type_id
and asg.assignment_id = pee.assignment_id
and p_calculation_date between pee.effective_start_date
and pee.effective_end_date
and p_calculation_date between asg.effective_start_date and asg.effective_end_date; -- Fix for the bug 9715011
select formula_type_id
from ff_formula_types
where formula_type_name = 'QuickPaint';
select greatest(date_start),
actual_termination_date
from per_periods_of_service
where person_id = p_per_id
and date_start <= p_sess;
select 'Y'
from per_assignments
where primary_flag = 'Y'
and person_id = p_per_id
and payroll_id is not null;
select nvl(max(occurrence),0) + 1
from per_absence_attendances
where business_group_id + 0 = p_bgroup_id
and absence_attendance_type_id = p_abat_type
and person_id = p_per_id;
select 1
from per_periods_of_service pos
where pos.person_id = p_per_id
and pos.date_start > p_curr_date_end
and p_proj_start between
pos.date_start AND nvl(pos.actual_termination_date,hr_general.end_of_time);
select h.due_date, h.mpp_start_date
from ssp_maternities h
where h.maternity_id = p_maternity_id;
select h.due_date
from ssp_maternities h
where h.maternity_id = p_maternity_id;
select h.due_date
from ssp_maternities h
where h.person_id = p_person_id
and nvl(h.leave_type,'MA') = p_leave_type;
select ssp_smp_pkg.qualifying_week (mat.due_date) QW,
nvl(mat.mpp_start_date,hr_general.end_of_time) MPP,
nvl(mat.leave_type,'MA') leave_type
from ssp_maternities MAT
where mat.maternity_id = p_maternity_id;
select decode(mat.leave_type,
'AA',ssp_APAD_pkg.continuous_employment_date(mat.matching_date),
'AB',ssp_APAB_pkg.continuous_employment_date(mat.due_date)) continous_emp_date,
person_id
from ssp_maternities MAT
where mat.maternity_id = p_maternity_id
and mat.leave_type in ('AA','AB');
select 1
from per_periods_of_service
where person_id = l_person_id
/* Bug Fix 13486124, included Adjusted service date in the check
and date_start <= continous_emp_date
*/
and least(nvl(adjusted_svc_date,date_start),date_start) <= continous_emp_date
and nvl (actual_termination_date, hr_general.end_of_time)
> p_date_start;
select h.rowid
from ssp_maternities h
where h.person_id = p_person_id;
select h.rowid
from ssp_medicals h
where h.absence_attendance_id = p_absence_attendance_id
and h.medical_type = p_medical_type;