[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
session date - it's used to select a single
row from datetracked join tables in the
base view for PERWSEAD, so should not be left
NULL.
rfine 70.5 23-Nov-95 Added new SSP-related args to call to
per_absence_attendances_pkg.insert_row. They
are defined as DEFAULTs, which means they
shouldn't really be necessary, but they appear
to be.
ctredwin 110.2 16-Aug-99 Bug 953648. Handle null value from cursor
when fetching occurrence number during
insert.
ctredwin 110.3 01-Oct-99 Added insert_abs_for_bee,
insert_validate_for_bee, and validation
procedures.
ctredwin 115.5 07-Feb-00 Bug 1184545. Use derived start and end times
in validation.
ctredwin 115.6 24-Jul-99 Bug 1337672. Altered message name in check
duration procedure.
dcasemor 115.7 28-Aug-01 Bug 1668275. Replaced the table handler call
'insert_row' with the create absence API.
This was only changed for insert_abs_for_bee
because paymix is no longer used in 11i.
dcasemor 115.8 21-Dec-01 Passed out nocopy an additional warning to indicate
that the absence API has already created the
element entry.
dcasemor 115.9 28-Dec-01 Added check_dates_entered procedure.
dcasemor 115.10 21-May-02 Bug 2377104. Passed p_create_element_entry
to the absence API as false so that BEE
creates the element entries. Warnings of
EE_CREATED_BY_ABSENCE are no longer passed
back to BEE (so it always creates the EE).
dcasemor 115.11 14-Aug-02 GSCC compliance - added WHENEVER OSERROR...
adudekul 115.13 18-FEB-04 Bug 3307340. Modified procedure INSER_VALIDATE_FOR_BEE
to treat the errors as warnings which are raised
for sickness overlap.
kjagadee 115.14 23-FEB-04 Added overloaded proc for insert_abs_for_bee
kjagadee 115.15 05-APR-04 Bug 3506133, Modified procedure insert_abs_for_bee
(one which is called from BEE)
Added new private proc insert_absence_element.
Added package variable g_package.
kjagadee 115.16 19-MAY-04 Bug 3626565, Modified CHK_ABSENCE_INPUT to relax
the input validation, so that user can enter
negative absence duration through BEE.
SuSivasu 115.17 20-Aug-04 Bug 3812684. Assed support for sickness start and
end date for UK's sickness attendance types.
smparame 115.18 05-Oct-04 Bug 3900409. Replaced call to hr_cal_abs_dur_pkg.
calculate_absence_duration in check_duration with
per_abs_bus.calculate_absence_duration.
*/
--
-- Package Variables
--
g_package varchar2(33) := ' per_absence_attendances_pkg3.';
select 'x'
from per_absence_attendance_types
where absence_attendance_type_id = p_abs_type_id
and date_effective <= p_date_start
and (nvl(date_end,p_eot) >= p_date_end or
p_date_end is null);
select piv.element_type_id,
abt.hours_or_days
from per_absence_attendance_types abt,
pay_input_values_f piv
where abt.absence_attendance_type_id = p_abs_type_id
and abt.input_value_id = piv.input_value_id
and p_date_start between piv.effective_start_date
and piv.effective_end_date;
select legislation_code
from per_business_groups
where business_group_id = p_business_group_id;
select 'x'
from per_absence_attendances t,
per_absence_attendance_types a,
per_absence_attendance_types b
where t.person_id = p_person_id
and p_abs_type_id = 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_date_end 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 t.person_id = p_person_id
and p_abs_type_id = b.absence_attendance_type_id
and t.absence_attendance_type_id = a.absence_attendance_type_id
and a.absence_category = 'S'
and b.absence_category = 'S'
and ((p_date_start between t.date_start and nvl(t.date_end,p_eot))
or (t.date_start between p_date_start and nvl(p_date_end,p_eot)));
procedure insert_absence_element(
p_line_record in pay_batch_lines%Rowtype,
p_asg_act_id in number,
p_absence_attendance_id in number,
p_absence_att_type_id in number,
p_entry_values_count in number,
p_date_start in date,
p_date_end in date,
p_passed_inp_tbl in hr_entry.number_table,
p_passed_val_tbl in hr_entry.varchar2_table
) is
--
l_proc varchar2(72) := g_package||'insert_absence_element';
select hl.lookup_code
from hr_lookups hl
where hl.lookup_type = 'ELE_ENTRY_REASON'
and hl.meaning = p_meaning;
hr_entry_api.insert_element_entry(
p_effective_start_date => l_effective_start_date,
p_effective_end_date => l_effective_end_date,
p_element_entry_id => l_element_entry_id,
p_assignment_id => p_line_record.assignment_id,
p_element_link_id => l_element_link_id,
p_creator_type => 'A',
p_creator_id => p_absence_attendance_id,
p_entry_type => 'E',
p_cost_allocation_keyflex_id => p_line_record.cost_allocation_keyflex_id,
p_reason => l_reason,
p_subpriority => p_line_record.subpriority,
p_date_earned => p_line_record.date_earned,
p_personal_payment_method_id => p_line_record.personal_payment_method_id,
p_attribute_category => p_line_record.attribute_category,
p_attribute1 => p_line_record.attribute1,
p_attribute2 => p_line_record.attribute2,
p_attribute3 => p_line_record.attribute3,
p_attribute4 => p_line_record.attribute4,
p_attribute5 => p_line_record.attribute5,
p_attribute6 => p_line_record.attribute6,
p_attribute7 => p_line_record.attribute7,
p_attribute8 => p_line_record.attribute8,
p_attribute9 => p_line_record.attribute9,
p_attribute10 => p_line_record.attribute10,
p_attribute11 => p_line_record.attribute11,
p_attribute12 => p_line_record.attribute12,
p_attribute13 => p_line_record.attribute13,
p_attribute14 => p_line_record.attribute14,
p_attribute15 => p_line_record.attribute15,
p_attribute16 => p_line_record.attribute16,
p_attribute17 => p_line_record.attribute17,
p_attribute18 => p_line_record.attribute18,
p_attribute19 => p_line_record.attribute19,
p_attribute20 => p_line_record.attribute20,
p_entry_information_category => p_line_record.entry_information_category,
p_entry_information1 => p_line_record.entry_information1,
p_entry_information2 => p_line_record.entry_information2,
p_entry_information3 => p_line_record.entry_information3,
p_entry_information4 => p_line_record.entry_information4,
p_entry_information5 => p_line_record.entry_information5,
p_entry_information6 => p_line_record.entry_information6,
p_entry_information7 => p_line_record.entry_information7,
p_entry_information8 => p_line_record.entry_information8,
p_entry_information9 => p_line_record.entry_information9,
p_entry_information10 => p_line_record.entry_information10,
p_entry_information11 => p_line_record.entry_information11,
p_entry_information12 => p_line_record.entry_information12,
p_entry_information13 => p_line_record.entry_information13,
p_entry_information14 => p_line_record.entry_information14,
p_entry_information15 => p_line_record.entry_information15,
p_entry_information16 => p_line_record.entry_information16,
p_entry_information17 => p_line_record.entry_information17,
p_entry_information18 => p_line_record.entry_information18,
p_entry_information19 => p_line_record.entry_information19,
p_entry_information20 => p_line_record.entry_information20,
p_entry_information21 => p_line_record.entry_information21,
p_entry_information22 => p_line_record.entry_information22,
p_entry_information23 => p_line_record.entry_information23,
p_entry_information24 => p_line_record.entry_information24,
p_entry_information25 => p_line_record.entry_information25,
p_entry_information26 => p_line_record.entry_information26,
p_entry_information27 => p_line_record.entry_information27,
p_entry_information28 => p_line_record.entry_information28,
p_entry_information29 => p_line_record.entry_information29,
p_entry_information30 => p_line_record.entry_information30,
p_num_entry_values => l_count,
p_input_value_id_tbl => l_passed_inp_tbl,
p_entry_value_tbl => l_passed_val_tbl);
update pay_element_entries_f
set source_id = p_asg_act_id
where element_entry_id = l_element_entry_id;
hr_entry_api.delete_element_entry(
p_dt_delete_mode => 'DELETE',
p_session_date => p_date_end,
p_element_entry_id => l_element_entry_id);
end insert_absence_element;
select increasing_or_decreasing_flag
from per_absence_attendance_types
where absence_attendance_type_id = p_abs_type_id;
PROCEDURE insert_validate_for_bee(p_session_date in date,
p_date_start in date,
p_date_end in date,
p_time_start in varchar2,
p_time_end in varchar2,
p_absence_days in number,
p_absence_hours in number,
p_abs_type_id in number,
p_person_id in number,
p_assignment_id in number,
p_business_group_id in number,
p_warning_table out nocopy t_message_table,
p_error_table out nocopy t_message_table
) IS
l_warning varchar2(30) := null;
end insert_validate_for_bee;
PROCEDURE insert_abs_for_paymix(p_session_date in date,
p_absence_att_type_id in number,
p_assignment_id in number,
p_absence_days in number,
p_absence_hours in number,
p_date_start in date,
p_date_end in date) IS
--
-- Retrieves additional data from per_assignments_f table.
--
CURSOR C1 IS
SELECT a.business_group_id,
a.person_id,
a.payroll_id
FROM per_assignments_f a
WHERE a.assignment_id = p_assignment_id
AND p_session_date between a.effective_start_date
AND a.effective_end_date;
select iv.element_type_id,
a.hours_or_days,
a.increasing_or_decreasing_flag,
a.input_value_id
FROM pay_input_values_f iv,
per_absence_attendance_types a
WHERE a.absence_attendance_type_id = p_absence_att_type_id
AND a.input_value_id = iv.input_value_id (+);
SELECT max(occurrence)
FROM per_absence_attendances
WHERE person_id = l_person_id
AND absence_attendance_type_id = p_absence_att_type_id;
hr_utility.set_message_token('PROCEDURE','insert_abs_for_paymix');
per_absence_attendances_pkg.insert_row
(X_Rowid => l_row_id,
X_Absence_Attendance_Id => l_absence_att_id,
X_Business_Group_Id => l_business_group_id,
X_Absence_Attendance_Type_Id => p_absence_att_type_id,
X_Abs_Attendance_Reason_Id => null,
X_Person_Id => l_person_id,
X_Authorising_Person_Id => null,
X_Replacement_Person_Id => null,
X_Period_Of_Incapacity_Id => null,
X_Absence_Days => p_absence_days,
X_Absence_Hours => p_absence_hours,
X_Comments => null,
X_Date_End => p_date_end,
X_Date_Notification => p_session_date,
X_Date_Projected_End => null,
X_Date_Projected_Start => null,
X_Date_Start => p_date_start,
X_Occurrence => l_max_occurrence,
X_Ssp1_Issued => null,
X_Time_End => null,
X_Time_Projected_End => null,
X_Time_Projected_Start => null,
X_Time_Start => null,
X_Attribute_Category => null,
X_Attribute1 => null,
X_Attribute2 => null,
X_Attribute3 => null,
X_Attribute4 => null,
X_Attribute5 => null,
X_Attribute6 => null,
X_Attribute7 => null,
X_Attribute8 => null,
X_Attribute9 => null,
X_Attribute10 => null,
X_Attribute11 => null,
X_Attribute12 => null,
X_Attribute13 => null,
X_Attribute14 => null,
X_Attribute15 => null,
X_Attribute16 => null,
X_Attribute17 => null,
X_Attribute18 => null,
X_Attribute19 => null,
X_Attribute20 => null,
X_Linked_Absence_id => null,
X_Sickness_Start_Date => null,
X_Sickness_End_Date => null,
X_Accept_Late_Notif_Flag => null,
X_reason_for_late_notification => null,
X_Pregnancy_Related_Illness => null,
X_Maternity_Id => null,
X_Abs_Information_Category => null,
X_Abs_Information1 => null,
X_Abs_Information2 => null,
X_Abs_Information3 => null,
X_Abs_Information4 => null,
X_Abs_Information5 => null,
X_Abs_Information6 => null,
X_Abs_Information7 => null,
X_Abs_Information8 => null,
X_Abs_Information9 => null,
X_Abs_Information10 => null,
X_Abs_Information11 => null,
X_Abs_Information12 => null,
X_Abs_Information13 => null,
X_Abs_Information14 => null,
X_Abs_Information15 => null,
X_Abs_Information16 => null,
X_Abs_Information17 => null,
X_Abs_Information18 => null,
X_Abs_Information19 => null,
X_Abs_Information20 => null,
X_Abs_Information21 => null,
X_Abs_Information22 => null,
X_Abs_Information23 => null,
X_Abs_Information24 => null,
X_Abs_Information25 => null,
X_Abs_Information26 => null,
X_Abs_Information27 => null,
X_Abs_Information28 => null,
X_Abs_Information29 => null,
X_Abs_Information30 => null);
per_absence_attendances_pkg2.insert_element
(p_effective_start_date => l_date_start,
p_effective_end_date => l_date_end,
p_element_entry_id => l_element_entry_id,
p_assignment_id => p_assignment_id,
p_element_link_id => l_element_link_id,
p_creator_id => l_absence_att_id,
p_creator_type => 'A',
p_entry_type => 'E',
p_input_value_id1 => l_input_value_id,
p_entry_value1 => l_entry_value);
END insert_abs_for_paymix;
PROCEDURE insert_abs_for_bee(p_session_date in date,
p_absence_att_type_id in number,
p_assignment_id in number,
p_batch_id in number,
p_absence_days in number,
p_absence_hours in number,
p_date_start in date,
p_date_end in date,
p_absence_attendance_id out nocopy number,
p_warning_table out nocopy t_message_table,
p_error_table out nocopy t_message_table
) is
--
l_absence_att_id number;
SELECT a.business_group_id,
a.person_id,
a.payroll_id
FROM per_assignments_f a
WHERE a.assignment_id = p_assignment_id
AND p_session_date between a.effective_start_date
AND a.effective_end_date;
SELECT nvl(nvl(asg.time_normal_start, pbg.default_start_time), '00:00'),
nvl(nvl(asg.time_normal_finish, pbg.default_end_time), '23:59')
FROM per_assignments_f asg,
per_business_groups pbg
WHERE asg.assignment_id = p_assignment_id
AND asg.business_group_id = pbg.business_group_id
AND p_session_date between asg.effective_start_date
and asg.effective_end_date;
SELECT null
FROM per_absence_attendance_types paat,
per_business_groups_perf pbg
WHERE pbg.business_group_id = l_business_group_id
AND pbg.legislation_code = 'GB'
AND paat.absence_attendance_type_id = p_absence_att_type_id
AND paat.absence_category='S';
hr_utility.set_message_token('PROCEDURE','insert_abs_for_bee');
insert_validate_for_bee(p_session_date => p_session_date,
p_date_start => p_date_start,
p_date_end => p_date_end,
p_time_start => l_time_start,
p_time_end => l_time_end,
p_absence_days => p_absence_days,
p_absence_hours => p_absence_hours,
p_abs_type_id => p_absence_att_type_id,
p_person_id => l_person_id,
p_assignment_id => p_assignment_id,
p_business_group_id => l_business_group_id,
p_warning_table => l_warning_table,
p_error_table => l_error_table
);
END insert_abs_for_bee;
procedure insert_abs_for_bee(
p_absence_att_type_id in number,
p_batch_id in number,
p_asg_act_id in number,
p_entry_values_count in number,
p_hours_or_days in varchar2,
p_format in varchar2,
p_value in varchar2,
p_date_start in date,
p_date_end in date,
p_line_record in pay_batch_lines%Rowtype,
p_passed_inp_tbl in hr_entry.number_table,
p_passed_val_tbl in hr_entry.varchar2_table,
p_absence_attendance_id out nocopy number,
p_warning_table out nocopy t_message_table,
p_error_table out nocopy t_message_table
) is
--
l_proc varchar2(72) := g_package||'insert_abs_for_bee';
per_absence_attendances_pkg3.insert_abs_for_bee(
p_session_date => p_line_record.effective_date,
p_absence_att_type_id => p_absence_att_type_id,
p_absence_attendance_id => p_absence_attendance_id,
p_batch_id => p_batch_id,
p_assignment_id => p_line_record.assignment_id,
p_absence_days => l_absence_days,
p_absence_hours => l_absence_hours,
p_date_start => p_date_start,
p_date_end => p_date_end,
p_warning_table => p_warning_table,
p_error_table => p_error_table);
insert_absence_element(
p_line_record => p_line_record,
p_asg_act_id => p_asg_act_id,
p_absence_attendance_id => p_absence_attendance_id,
p_absence_att_type_id => p_absence_att_type_id,
p_entry_values_count => p_entry_values_count,
p_date_start => p_date_start,
p_date_end => p_date_end,
p_passed_inp_tbl => p_passed_inp_tbl,
p_passed_val_tbl => p_passed_val_tbl);
end insert_abs_for_bee;