[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE insert_person_absence_changes
(p_absence_attendance_id IN number
,p_effective_date IN date
,p_person_id IN number
,p_date_projected_start IN date
,p_date_start IN date
,p_abs_information1 IN varchar2
,p_date_projected_end IN date
,p_date_end IN date) IS
--
v_update_type varchar2(30) := 'START';
insert into per_nl_absence_changes
(absence_attendance_id
,date_changed
,update_type
,reported_indicator
,person_id
,sickness_start_date
,percentage_sick
,recovery_date
)
values
(p_absence_attendance_id
,p_effective_date
,v_update_type
,v_reported_indicator
,p_person_id
,nvl(p_date_start,p_date_projected_start)
,TO_NUMBER(p_abs_information1,'990.90') /* Bug 4375570 */
,nvl(p_date_end,p_date_projected_end)
);
END insert_person_absence_changes;
PROCEDURE update_person_absence_changes
(p_absence_attendance_id IN number
,p_effective_date IN date
,p_date_end IN date
,p_date_projected_end IN date
,p_date_start IN date
,p_date_projected_start IN date
,p_abs_information1 IN varchar2) IS
--
v_update_type varchar2(30) := NULL;
select person_id
,date_end
,date_projected_end
,date_start
,date_projected_start
,abs_information1
from per_absence_attendances
where absence_attendance_id = p_absence_attendance_id;
-- DATE_PROJECTED_END, update type in
-- PER_NL_ABSENCE_CHANGES is 'END'
--
v_update_type := 'END';
-- DATE_PROJECTED_START or ABS_INFORMATION1 update type in
-- PER_NL_ABSENCE_CHANGES is 'UPDATE'
--
v_update_type := 'UPDATE';
if v_update_type is not null then
insert into per_nl_absence_changes
(absence_attendance_id
,date_changed
,update_type
,reported_indicator
,person_id
,sickness_start_date
,percentage_sick
,recovery_date
)
values
(p_absence_attendance_id
,p_effective_date
,v_update_type
,v_reported_indicator
,l_rec.person_id
,nvl(p_date_start,p_date_projected_start)
,decode(p_abs_information1,NULL,NULL,TO_NUMBER(l_rec.abs_information1,'990.90')) /* Bug 4375570 */ -- 8342503
,nvl(p_date_end,p_date_projected_end)
);
END update_person_absence_changes;
PROCEDURE delete_person_absence_changes
(p_absence_attendance_id IN number) IS
--
-- This cursor fetchs old data from PER_ABSENCE_ATTENDANCES
-- required to maitain the details of the deleted absence.
--
cursor cur_per_abs_chags is
select person_id
,date_end
,date_projected_end
,date_start
,date_projected_start
,abs_information1
from per_absence_attendances
where absence_attendance_id = p_absence_attendance_id;
v_update_type varchar2(30) := 'DELETE';
select nvl(effective_date,trunc(sysdate))
into v_effective_date
from fnd_sessions
where userenv('sessionid') = session_id;
insert into per_nl_absence_changes
(absence_attendance_id
,date_changed
,update_type
,reported_indicator
,person_id
,sickness_start_date
,percentage_sick
,recovery_date
)
values
(p_absence_attendance_id
,v_effective_date
,v_update_type
,v_reported_indicator
,l_rec.person_id
,nvl(l_rec.date_start,l_rec.date_projected_start)
,TO_NUMBER(l_rec.abs_information1,'990.90') /* BUG 4375570 */
,nvl(l_rec.date_end,l_rec.date_projected_end)
);
END delete_person_absence_changes;
DELETE from per_nl_absence_changes pnac
WHERE months_between(trunc(v_effective_date), pnac.date_changed) > 12
AND person_id in ( SELECT person_id
FROM per_all_people_f
WHERE business_group_id = p_business_group_id);
procedure update_reported_absence_chgs
(p_effective_date IN date
,p_prev_rep_chg IN varchar2
,p_structure_version_id IN number
,p_top_org_id IN number) is
--
-- This cursor will fetch same rows as that of the rows
-- fetched by the report 'Daily Sick and Recovery Report'
--
cursor cur_upd is
select
paaf.person_id person_id
,paaf.effective_start_date effective_start_date
,paaf.effective_end_date effective_end_date
from per_all_assignments_f paaf
where paaf.primary_flag = 'Y'
and paaf.organization_id in
(SELECT pose1.organization_id_child
FROM (SELECT pose.organization_id_child ,
pose.organization_id_parent
FROM per_org_structure_elements pose
WHERE pose.org_structure_version_id = p_structure_version_id
) pose1
CONNECT BY PRIOR pose1.organization_id_child =
pose1.organization_id_parent
START WITH pose1.organization_id_parent = p_top_org_id
UNION
SELECT p_top_org_id
from dual
);
update per_nl_absence_changes pnac
set pnac.reported_indicator = 'Y'
WHERE pnac.person_id = rec_upd.person_id
AND nvl(p_effective_date,pnac.sickness_start_date )
BETWEEN rec_upd.effective_start_date AND rec_upd.effective_end_date
AND pnac.reported_indicator = decode(p_prev_rep_chg, 'Y',pnac.reported_indicator, 'N')
AND pnac.date_changed = nvl(p_effective_date, pnac.date_changed);
end update_reported_absence_chgs;
select paat.absence_category
from per_absence_attendances paa,
per_absence_attendance_types paat
where paa.absence_attendance_type_id
= paat.absence_attendance_type_id
and paa.absence_attendance_id = p_absence_attendance_id
and paa.business_group_id = paat.business_group_id;
select PEI_INFORMATION1, PEI_INFORMATION2, PEI_INFORMATION3
from per_people_extra_info pei
where pei.person_id = l_person_id
and pei.information_type = 'NL_DS_SICK_INFO';
select aei.aei_INFORMATION3
from per_assignment_extra_info aei
where aei.assignment_id = l_assignment_id
AND aei_information_category = 'NL_TML'
and l_effective_date between fnd_date.canonical_to_date(aei_information1) and
NVL(fnd_date.canonical_to_date(aei_information2), to_date('31-12-4712','DD-MM-YYYY'));