[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
70.1 18-FEB-93 SZWILLIA Corrected deletes from
PER_BOOKINGS and PER_EVENTS.
Also, added future actions
check to cancellations.
Added messages
70.2 11-MAR-93 NKHAN Added 'exit' to end
70.3 26-MAR-93 SZWILLIA Addition of maintain_entries_asg
to cancel_termination.
70.4 04-MAY-93 SZWILLIA Check on employee_shutdown
modified to deal with FPD on
the same date as ATD - AFTER
termination.
80.1 15-OCT-93 JRHODES Added check_cobra_benefits
80.2 29-OCT-93 JHOBBS B258 Corrected cursor rec_entries in
terminate_entries_and_alus so that it
only selects recurring entries.
80.3 03-NOV-93 JHOBBS Altered terminate_entries_and_alus so
that nonrecurring entries are shortened
if they exist past the final process
date. Pay proposals are also now
removed if there are no entries for it.
80.4 01-JUN-94 JHOBBS G847 Corrected check_for_future_actions so
that it can only return one row if
future actions exist. The new SQL
should be more efficient.
#218334 Added check_for_future_person_type
which is used to validate the
cancellation of a termination. It
checks to see if there are future
person types which are not EX_EMP. If
the cancellation was allowed to take
place overlapping periods of service
etc ... would be created.
70.13 16-JUN-94 PSHERGILL fix 220466 added ATTRIBUTE21..30 and
ASS_ATTRIBUTE21..30
70.15 14-JUL-94 SZWILLIA #225892 Problem which came to light on 7.1.3,
related to PL/SQL bug number 225900.
Explicit OPEN of cursor prior to
cursor for loop causes oracle error
when terminating from PERPETEM. However
PERPETEM, although trapping the oracle
error was not failing.
70.16 23-NOV-94 RFINE G1725 Suppressed index on business_group_id
70.17 20-APR-95 TMathers 265290,269193,271941 Fixed these WWBUGS
Added code to check for Future
Person rows and insert/deletes to
per_person_list_changes. Also added
flag to allow only some of Term
details to be deleted.
70.18 25-APR-95 TMathers 276096 Added legislation code to terminate_alus
and terminate employee so that
If legislation is 'US' and Last_standard
process is null the package will
not fall over.
70.19 12-MAY-95 TMathers 281104 added rest of fields save notified
and projected to clear_details update.
70.20 03-JUL-95 RFine 288341 Fixed error in check put in revision
70.18. The lack of an NVL meant that
leg_code <> 'US' was failing when it
was null. Removed the check in
question, as it is now unnecessary.
70.21 07-JUL-95 RFine 289454 Allow cancel termination to succeed if
future person type changes are to
EX_EMP_APL as well as to EX_EMP,
providing both types don't exist for
the same person. This allows
terminations to be cancelled for
Employee-Applicants.
70.22 24-JUL-95 TMathers 294069 Update LAST_UPDATE_DATE on
pay_element_entries_f with
sysdate.
70.23 26-JUL-95 RFine 291763 Added missing column PAY_BASIS_ID to
the SQL statement which inserts the new
term_assign row in per_assignments_f.
It was missing, with the effective
result that terminating the person also
cleared the assignments' pay basis.
70.24 29-AUG-95 RFine 270356 Get the default TERM_ASSIGN status
immediately before inserting a
TERM_ASSIGN assignment record, if one
hasn't already been passed in.
70.25 19-Sep-95 TMathers 307957 Added and Assignment_type = 'E'
to update cursor of assignments.
70.27 31-Oct-95 TMathers 314277 Changed updates and checks for
delete_entries to use the p_term_date
a la termination rule instead of
p_final_process_date.
70.28 23-Jan-96 MSwanson 317302 Add 'not exists' to insert on
per_person_list_changes and update
where record does exist.
70.29 08-Mar-96 VTreiger 314277 Changed the logic of closing down
the non-recurring element
entries when the final process
date is entered before the end of
the payroll period.
334654 Setting the employee assignment
334681 status to TERM_ASSIGN is not allowed
if there are certain types of future
non-recurring element entries which
have not been processed in a payroll
run.
311763 Delete of future spinal point
placements.
70.30 13-Mar-1996 VTreiger Restored code in terminate employee
procedure to state from 70.28
70.31 18-Mar-1996 Vtreiger Fixed a bug in post_update for
terminate_employee procedure.
70.32 15-Apr-1996 VTreiger 354874 Included employment category in the
explicit insert for per_assignments_f.
70.33 15-May-1996 VTreiger 364214 Included additional criteria for
function check_for_future_actions
70.34 22-May-1996 VTreiger 364214 Restored back to version 70.32.
70.35 04-Jun-1996 VTreiger 364214 Added function check_for_compl_actions.
This function returns :
Y - if there are any assignment
actions after the Final Process Date.
W - if there exists any completed assignment
action after Actual Termination Date but
before Final Process Date.
N - in all other cases.
New function call is used in procedures :
terminate_employee,employee_shutdown,
cancel_termination.
70.36 28-Jun-1996 VTreiger 375157 Commented out nocopy removal of pay proposals
when non-recurring entries are processed
while employee termination.
70.37 11-Oct-1996 VTreiger 441206 Suffix value is saved at termination.
70.38 17-Oct-1996 VTreiger 306710 Changed procedure terminate_entries_and
alus to return the value of entries
changed warning as 'N' or 'Y' or 'S'.
398699 Commented out nocopy contents of procedure
check_cobra_benefits to return false
always.
70.39 01-Nov-1996 VTreiger 306710 Added procedure terminate_entries_
and_alus overload.
70.40 03-APR-1998 DShannon 519738 Added calls to delete_covered_dependents and
delete_beneficiaries when entry is terminated
- for both recurring and non-recurring entries
110.3 30-APR-1998 SASmith Changes made due to date tracking of assignment_budget_
values_f.
Addition of processing in :
1. delete_assign_fpd - include deletion/update of
assignment_budget_values_f
2. cancel_termination - inclusing of update to assignment_budget
values_f when the termination is cancelled.
115.1 16-OCT-1998 CCarter New parameters added to insert into per_people_f
in the terminate employee procedure for OAB.
115.5 19-Jul-1999 mmillmor 895018 removed some bad code and altered to use
internal security procedures in terminate
115.6 27-Sep-1999 alogue Changed insert into per_People to per_all_people_f
inside terminate_employee.
115.8 27-OCT-1999 rmonge Added code fixes for bug 920233 and 969491
Bug fix 920233 resolves the problem
with tax records not reversed when reverse
termination process is done on a terminated
employee, and bug 969491
fixes tax records not end dated when the
employee is terminated.
115.9 06-MAR-2000 Rvydyana Leapfroged version created for v115.6
with backported changes for 1176101
115.10 06-MAR-2000 Rvydyana Leapfroged version of v115.8 with code fix
for 1176101 ie. Added call to
ben_dt_trg_handle in cancel termination.
115.11 16-NOV-1999 I Harding 1076054 Added extra attributes to insert into
PER_ASSIGNMENTS_F during the post update
work following a termination of an
employee. Requested by John Rhodes
following French Phase 1.
115.12 13-MAR-2000 CSimpson Added town_of_birth, region_of_birth,
country_of_birth and global_person_id attribs
into per_all_people_f insert in terminate_employee.
115.13 26-Jul-2000 mmillmor 996015 Added code to delete pay proposals at the same
time as element entries
115.14 17-Aug-2000 VTreiger 1363723 Commented out nocopy copying of ATTRIBUTEx columns
from EMP record to EX_EMP record.
115.15 04-Aug-2000 Dscully 1348553 Fixed end dating of tax records.
115.16 12-Sep-2000 MReid 889806 Excluded Magnetic Reports from check for
future dated completed actions.
115.17 15-SEP-00 GPERRY Added hook points to benefits code whenerever
per_periods_of_service is inserted or updated.
Fixed WWBUG 1390173
115.18 15-SEP-00 GPERRY Leapfrog of 115.16 with fix for
WWBUG 1390173.
115.19 06-OCT-00 TMATHERS 11.5.3 incompatibility, 1348553 is patchset B for
payroll, so need to back out nocopy that change to work
woth base 11.5.3.
115.20 06-OCT-00 TMATHERS Leapfrog of 115.18 including AND requiring fix
for 1348553, new paramater to
pay_us_update_tax_rec_pkg
115.21 19-OCT-00 GPERRY Fixed WWBUG 1408379.
Added support for life event triggers for OAB.
115.22 23-OCT-00 SBIRNAGE Added an extra line to two where clauses to fix bug
1406063.
115.23 29-MAR-01 vshukhat Bug 1711085. Commented out nocopy code that disables
last_standard_process for US legislature.
115.24 09-APR-01 Reverted back some previous changes.
115.25 09-MAY-01 MGettins Reverse out nocopy changes for bug 1363723
115.26 10-jul-01 rvydyana TAR 1660650.999
115.27 06-Jul-01 pbodla - Bug 1877018 : added final_process_date
before calling ben_pps_ler.ler_chk
to detect potential life events.
- Bug 1854968 : Pass old actual termination
date to ben_dt_trgr_handle.periods_of_service
call.
115.28 02-oct-01 vsjain Added notice_period, notice_period_uom,
work_at_home,job_post_source_name,
employee_category to terminate employee proc
115.29 LEAPFROG VERSION BASED ON 115.27
115.30 05-Oct-01 mbocutt 1570258 Prevent delete of events which are 'I'nterview
as these may be linked to reviews and pay
proposals.
115.31 30-Oct-01 mbocutt 1271513 Change cancel terminate code to correctly
handle future dated assignments on reverse
termination.
115.32 01-Nov-01 mbocutt Added som emissed variable declarations.
115.33 05-NOV-01 kmullapu Modified INSERT INTO PER_ALL_PEOPLE_F in
terminate_employee to include
PER_INFORMATION 21 TO 30
115.35 22-AUG-02 adhunter correct gscc warning. change msg name
HR_6519_CANC_ACTIONS_EXIST
to HR_6519_EMP_CANC_ACTIONS_EXIST
115.38 05-DEC-02 pkakar added nocopy to parameters
115.39 16-DEC-02 dcasemor 2711532 Added predicate to exclude BEE
payroll actions to:
- check_for_compl_actions
- check_for_future_actions
115.40 10-FEB-02 vramanai 2784295 Removed the code which requires LSPD
to be not null for non-US legislations
115.42 3-Mar-03 mbocutt 2820633 Changes to reverse term code so that the
assignment update made during termination on
ATD is removed if there are no differences
between it and the previous DT instance of
the assignment. Modified behaviour introduced
in fix for bug 1271513.
115.43 11-Mar-03 mbocutt 2843882 The above fix did not work when ATD=FPD. Adjus
ted fix to handle this case.
115.44 Sept-2003 mbocutt Ex-person security enhancements.
Remove call to copy_to_person_list_changes and
other direct refs to PER_PERSON_LIST_CHANGES.
This file is now dependent on other
security changes delivered in Nov 2003 FP.
115.45 17-Oct-2003 smparame 3077943 Procedure Cancel_termination modified.
l_final_process_date is set to
p_actual_termination_date + 1 if it is equal
to p_actual_termination_date before resetting
assignment records.
115.46 19-FEB-04 smparame 3446768 Cursor rec_entries in terminate_entries_and_alus
procedure modified to increase performance.
115.47 11-MAR-04 adudekul 3100620. In proc cancel_termination,
changed warning message HR_6519_EMP_CANC_ACTIONS_EXIST
to PER_449053_EMP_TERM_FUT_ERROR.
115.48 11-MAR-04 adudekul 3100620. Modified proc check_for_compl_actions.
115.49 02-APR-04 smparame 3077943. Changes made to the 3077943 are reverted.
115.50 04-Apr-04 bsubrama 1370960. Added code to revert the roles when
termination is reversed.
115.51 23-Aug-2004 njaladi 3735333. Modified proc terminate_employee
and delete_assign_atd for better performance.
115.52 16-SEP-2004 adudekul 3889294. In procedure cancel_termination, added
code to manipulate the PTU records, legislation
specific actions and hr workflow sync code.
115.53 14-SEP-2004 smparame 3983715 Cursor rec_entries in
terminate_entries_and_alus procedure
modified to increase performance.
115.54 16-Jun-2005 ghshanka 4436297 cursor 'future_person_types'
in the funtion 'check_for_future_person_type' is
modified to query from per_person_type_usages_f table.
115.55 27-jun-2005 njaladi Backed out changes done in 115.54 and this version
is same as 115.53.
115.58 13-Jan-2006 ghshanka 4919804 Modified the procedure cancel_termination to call
the procedure pay_pog_all_assignments_pkg.after_delete
115.58 16-jan-2006 ghshanka 4919804 Modified the call to pay_pog_all_assignments_pkg.after_delete
by passing the end of time value to p_effective_end_date parameter.
115.58 17-Jan-2006 ghshanka 4919804 Modified the procedure cancel_termination to call
the procedure pay_pog_all_assignments_pkg.after_delete when
Finalprocessdate is not null.
115.59 22-Mar-2006 LSilveir 4449472 Overloaded terminate_entries_and_alus and added
new ALU validation into delete_alus.
115.60 04-Apr-2006 ghshanka 4457651 modified the function check_for_future_person_type
by adding a new cursor to check for COMBINATION of
future person types of type 'CWK and EMP'.
115.61 24-Apr-2006 asgugupt 5152164 modified the procedure CANCEL_TERMINATION
115.62 09-May-2006 ghshanka 5152164 modified the procedure CANCEL_TERMINATION
115.63 19-May-2006 ggnanagu Added the call to adjust_salary_proposals
in per_saladmin_utility to fix bug 5200269
115.65 14-JUN-2006 avarri 4371218 Modified cancel_termination to fix 4371218
115.66 17-JUL-2006 agolechh 4308892 This version of hrempter calls pay_element_entry_api
instead of performing direct DML statements on
pay_element_entries_f.
115.67 26-Jul-2006 thabara 5368246 Modified terminate_entries_and_alus and
delete_alus to end date ALUs with final
process date regardless of the
termination rule.
115.68 20-Sep-2006 SNukala 5507290 Modified cancel_termination and added call to
maintain EX-EMP incase of canceling termination
Leaving reason 'Retirement' type ex-employee.
115.69 10-AUG-2007 pchowdav 6313195 Reverted back the changes done for
bug 4371218.
115.71 07-FEB-2008 ckesanap 6801103 Modified cursor rec_entries in delete_entries(). Also
added a condition - to call pay_element_entry_api in
DELETE mode only if p_term_date is not equal to
max_effective_end_date for the record.
================================================================= */
g_package varchar2(33) := ' hrempter.'; -- Global package name
PROCEDURE delete_assign_atd(p_assignment_id NUMBER
,p_actual_termination_date DATE)
IS
--
l_proc varchar2(72):=g_package||'delete_assign_atd';
hr_utility.trace('Entered delete_assign_atd for assign '||p_assignment_id);
DELETE per_bookings pb
WHERE event_id in ( SELECT event_id
FROM per_events pev
WHERE pev.assignment_id = p_assignment_id
AND pev.date_start > p_actual_termination_date
);
DELETE per_events pev
WHERE pev.assignment_id = p_assignment_id
AND pev.date_start > p_actual_termination_date
AND pev.event_or_interview = 'E';
DELETE per_letter_request_lines lrl
WHERE lrl.assignment_id = p_assignment_id
AND lrl.date_from > p_actual_termination_date;
end delete_assign_atd;
PROCEDURE delete_assign_fpd(p_assignment_id NUMBER
,p_final_process_date DATE)
IS
--
l_proc varchar2(72):=g_package||'delete_assign_fpd';
select *
from per_assignment_budget_values_f abv
where abv.assignment_id = p_assignment_id
and p_final_process_date
between abv.effective_start_date
and abv.effective_end_date;
hr_utility.trace('Entered delete_assign_fpf for assign '||p_assignment_id);
UPDATE per_secondary_ass_statuses sas
SET sas.end_date = p_final_process_date
WHERE sas.assignment_id = p_assignment_id
AND sas.end_date IS NULL;
DELETE per_secondary_ass_statuses sas
WHERE sas.assignment_id = p_assignment_id
AND sas.start_date > p_final_process_date;
UPDATE pay_personal_payment_methods_f ppm
SET ppm.effective_end_date = p_final_process_date
WHERE ppm.assignment_id = p_assignment_id
AND p_final_process_date
BETWEEN ppm.effective_start_date
AND ppm.effective_end_date;
DELETE pay_personal_payment_methods_f ppm
WHERE ppm.assignment_id = p_assignment_id
AND ppm.effective_start_date > p_final_process_date;
UPDATE pay_cost_allocations_f pca
SET pca.effective_end_date = p_final_process_date
WHERE pca.assignment_id = p_assignment_id
AND p_final_process_date
BETWEEN pca.effective_start_date
AND pca.effective_end_date;
DELETE pay_cost_allocations_f pca
WHERE pca.assignment_id = p_assignment_id
AND pca.effective_start_date > p_final_process_date;
UPDATE per_spinal_point_placements_f spp
SET spp.effective_end_date = p_final_process_date
WHERE spp.assignment_id = p_assignment_id
AND p_final_process_date
BETWEEN spp.effective_start_date
AND spp.effective_end_date;
DELETE per_spinal_point_placements_f spp
WHERE spp.assignment_id = p_assignment_id
AND spp.effective_start_date > p_final_process_date;
DELETE per_assignment_budget_values_f abv
WHERE abv.assignment_id = p_assignment_id
AND abv.effective_start_date > p_final_process_date;
update per_assignment_budget_values_f abv
set abv.effective_end_date = p_final_process_date
where abv.assignment_budget_value_id = l_c1.assignment_budget_value_id
and abv.effective_start_date = l_c1.effective_start_date
and abv.effective_end_date = l_c1.effective_end_date;
end delete_assign_fpd;
PROCEDURE delete_employee_atd(p_person_id NUMBER
,p_actual_termination_date DATE)
IS
--
l_proc varchar2(72):=g_package||'delete_employee_atd';
hr_utility.trace('Entered delete_employee_atd for person '||p_person_id);
DELETE per_absence_attendances paa
WHERE paa.person_id = p_person_id
AND paa.date_start > p_actual_termination_date;
DELETE per_bookings pb
WHERE pb.person_id = p_person_id
AND EXISTS (SELECT ''
FROM per_events pev
WHERE pev.date_start > p_actual_termination_date
AND pev.emp_or_apl = 'E'
AND pev.event_or_interview = 'E'
AND pb.event_id = pev.event_id);
DELETE per_bookings pb
WHERE pb.person_id = p_person_id
AND EXISTS (SELECT ''
FROM per_events pev
WHERE pev.event_id = pb.event_id
AND pev.date_start > p_actual_termination_date
AND pev.event_or_interview = 'I'
AND pev.assignment_id IS NOT NULL
AND pev.assignment_id NOT IN (SELECT assignment_id
FROM per_assignments_f
WHERE person_id = p_person_id
)
);
DELETE per_letter_request_lines lrl
WHERE lrl.person_id = p_person_id
AND lrl.date_from > p_actual_termination_date;
end delete_employee_atd;
PROCEDURE delete_de_assign(p_assignment_id NUMBER
,p_delete_date DATE )
IS
--
l_proc varchar2(72):=g_package||'delete_de_assign';
hr_utility.trace('Entered delete_de_assign for '||p_assignment_id);
DELETE per_assignments_f ass
WHERE ass.assignment_id = p_assignment_id
AND ass.effective_start_date > p_delete_date;
end delete_de_assign;
SELECT max(ass.effective_end_date)
INTO l_max_end_date
FROM per_assignments_f ass
WHERE ass.assignment_id = p_assignment_id;
SELECT 'Y'
INTO l_action_chk
FROM sys.dual
WHERE exists
(SELECT null
FROM pay_payroll_actions pac,
pay_assignment_actions act,
per_assignments_f asg
WHERE asg.person_id = p_person_id
AND act.assignment_id = asg.assignment_id
AND pac.payroll_action_id = act.payroll_action_id
AND pac.action_type <> 'BEE'
AND pac.effective_date > p_action_date);
SELECT 'Y'
INTO l_action_chk
FROM sys.dual
WHERE exists
(SELECT null
FROM pay_payroll_actions pac,
pay_assignment_actions act,
per_assignments_f asg
WHERE asg.person_id = p_person_id
AND act.assignment_id = asg.assignment_id
AND pac.payroll_action_id = act.payroll_action_id
AND pac.action_type NOT IN ('X','BEE') -- Bug 889806, 2711532
AND pac.effective_date > p_fpr_date);
SELECT 'W'
INTO l_action_chk
FROM sys.dual
WHERE exists
(SELECT null
FROM pay_payroll_actions pac,
pay_assignment_actions act,
per_assignments_f asg
WHERE asg.person_id = p_person_id
AND act.assignment_id = asg.assignment_id
AND pac.payroll_action_id = act.payroll_action_id
AND pac.action_status = 'C'
AND pac.action_type <> 'BEE'
AND (pac.effective_date BETWEEN l_action_date AND p_fpr_date));
SELECT 'Y'
INTO l_action_chk
FROM sys.dual
WHERE exists
(select '1'
from per_people_f ppf
where ppf.person_id = p_person_id
and p_action_date < ppf.effective_start_date);
SELECT ppt.system_person_type
FROM per_people_f ppf,
per_person_types ppt
WHERE ppf.person_type_id = ppt.person_type_id
AND ppf.person_id = p_person_id
AND ppf.business_group_id + 0 = ppt.business_group_id + 0
AND p_action_date < ppf.effective_start_date;
SELECT ppt.system_person_type
FROM
per_person_types ppt ,
per_person_type_usages_f pptf
WHERE
pptf.person_id = p_person_id
AND p_action_date < pptf.effective_start_date
AND pptf.person_type_id = ppt.person_type_id
and ppt.business_group_id=l_bus_id
and ppt.system_person_type like 'CWK';
select business_group_id
from per_people_f ppf
where person_id=p_person_id
and p_action_date < ppf.effective_start_date;
PROCEDURE delete_entries(p_assignment_id NUMBER,
p_term_date DATE,
p_term_rule VARCHAR2,
p_final_process_date DATE,
p_entries_changed_warning IN OUT
VARCHAR2) IS
--
-- Cursor to return all recurring element entries for the assignment that
-- are for an element with the correct post termination rule and also exists
-- after the termination date
-- bug fix 3446768. Cursor modified to increase
-- performance.
-- bug fix 3983715. Cursor modified to improve execution
-- time.cursor split into two.
-- bug fix 4308892. Cursor modified to return min ee.effective_start_date,
-- which is required for element entry API call.
-- 6801103. Modified the effective_end_date condition.
CURSOR rec_entries(p_assignment_id NUMBER,
p_term_date DATE
) IS
SELECT ee.element_entry_id,
ee.element_link_id,
ee.element_type_id,
MIN(ee.effective_start_date) effective_start_date,
MAX(ee.effective_end_date) effective_end_date
FROM pay_element_entries_f ee
WHERE ee.assignment_id = p_assignment_id
AND ee.effective_end_date >= p_term_date
AND ee.entry_type = 'E'
GROUP BY ee.element_entry_id, ee.element_link_id,ee.element_type_id;
SELECT 'Y'
FROM pay_element_types_f et
WHERE et.element_type_id = p_element_type_id
AND et.post_termination_rule = p_term_rule
AND et.processing_type = 'R';
SELECT ee.element_entry_id,
ee.element_link_id,
min(ee.effective_start_date) effective_start_date,
max(ee.effective_end_date) effective_end_date
FROM pay_element_entries_f ee
WHERE ee.assignment_id = p_assignment_id
AND ee.effective_end_date > p_term_date
AND ((ee.entry_type <> 'E')
OR (ee.entry_type = 'E'
AND EXISTS (SELECT NULL
FROM pay_element_links_f el,
pay_element_types_f et
WHERE el.element_link_id = ee.element_link_id
AND et.element_type_id = el.element_type_id
AND et.processing_type = 'N')))
AND EXISTS (SELECT NULL
FROM pay_element_links_f el,
pay_element_types_f et
WHERE el.element_link_id = ee.element_link_id
AND et.element_type_id = el.element_type_id
AND et.post_termination_rule = p_term_rule)
GROUP BY ee.element_entry_id, ee.element_link_id;
SELECT ptp.end_date
FROM per_time_periods ptp,
per_all_assignments_f paaf
WHERE paaf.assignment_id = p_asg_id
AND p_start_date BETWEEN paaf.effective_start_date AND paaf.effective_end_date
AND paaf.payroll_id = ptp.payroll_id
AND p_start_date BETWEEN ptp.start_date AND ptp.end_date;
select ppp.pay_proposal_id
, ppp.multiple_components
from per_pay_proposals ppp
, per_all_assignments_f asg
, per_pay_bases ppb
, pay_element_types_f pet
, pay_input_values_f piv
where ppp.assignment_id=p_assignment_id
and asg.assignment_id=p_assignment_id
and ppp.change_date between asg.effective_start_date and asg.effective_end_date
and ppb.pay_basis_id=asg.pay_basis_id
and ppb.input_value_id=piv.input_value_id
and ppp.change_date between piv.effective_start_date and piv.effective_end_date
and piv.element_type_id=pet.element_type_id
and ppp.change_date between pet.effective_start_date and pet.effective_end_date
and pet.post_termination_rule = p_term_rule
and ppp.change_date>p_term_date;
l_last_update_date constant date := trunc(sysdate);
l_delete_warning boolean;
l_proc varchar2(72):=g_package||'delete_entries';
SELECT el.element_type_id
INTO l_element_type_id
FROM pay_element_links_f el
WHERE el.element_link_id = rec_entry.element_link_id
AND rec_entry.effective_end_date BETWEEN el.effective_start_date
AND el.effective_end_date;
SELECT 'Y' INTO l_ret_chng FROM dual WHERE EXISTS
(SELECT 'Y' FROM pay_element_entry_values_f eev
WHERE eev.element_entry_id = rec_entry.element_entry_id
AND eev.effective_start_date > p_term_date);
SELECT 'Y' INTO l_ret_chng FROM dual WHERE EXISTS
(SELECT 'Y' FROM pay_element_entries_f ee
WHERE ee.element_entry_id = rec_entry.element_entry_id
AND ee.effective_start_date > p_term_date);
SELECT 'Y' INTO l_ret_chng FROM dual WHERE EXISTS
(SELECT 'Y' FROM pay_element_entry_values_f eev
WHERE eev.element_entry_id = rec_entry.element_entry_id
AND eev.effective_end_date > p_term_date);
SELECT 'Y' INTO l_ret_chng FROM dual WHERE EXISTS
(SELECT 'Y' FROM pay_element_entries_f ee
WHERE ee.element_entry_id = rec_entry.element_entry_id
AND ee.effective_end_date > p_term_date);
SELECT 'Y' INTO l_ret_chng FROM dual WHERE EXISTS
(SELECT 'Y' FROM pay_element_entries_f ee
WHERE ee.element_entry_id = rec_entry.element_entry_id
AND ee.creator_type = 'SP'
AND ee.effective_start_date > p_term_date);
SELECT 'Y' INTO l_ret_chng FROM dual WHERE EXISTS
(SELECT 'Y' FROM pay_element_entries_f ee
WHERE ee.element_entry_id = rec_entry.element_entry_id
AND ee.creator_type = 'SP'
AND ee.effective_end_date > p_term_date);
SELECT el.element_type_id, et.process_in_run_flag
INTO l_element_type_id, l_process_in_run_flag
FROM pay_element_links_f el, pay_element_types_f et
WHERE el.element_link_id = nonrec_entry.element_link_id
AND et.element_type_id = el.element_type_id
AND nonrec_entry.effective_end_date BETWEEN el.effective_start_date
AND el.effective_end_date
/* Bug 1406063 */
AND nonrec_entry.effective_end_date BETWEEN et.effective_start_date
AND et.effective_end_date;
SELECT 'Y' INTO l_ret_chng FROM dual WHERE EXISTS
(SELECT 'Y' FROM pay_element_entry_values_f eev
WHERE eev.element_entry_id = nonrec_entry.element_entry_id);
SELECT 'Y' INTO l_ret_chng FROM dual WHERE EXISTS
(SELECT 'Y' FROM pay_element_entries_f ee
WHERE ee.element_entry_id = nonrec_entry.element_entry_id);
SELECT 'Y' INTO l_ret_chng FROM dual WHERE EXISTS
(SELECT 'Y' FROM pay_element_entries_f ee
WHERE ee.element_entry_id = nonrec_entry.element_entry_id
AND ee.creator_type = 'SP');
SELECT 'Y' INTO l_ret_chng FROM dual WHERE EXISTS
(SELECT 'Y' FROM pay_element_entry_values_f eev
WHERE eev.element_entry_id = nonrec_entry.element_entry_id
AND eev.effective_end_date = nonrec_entry.effective_end_date);
SELECT 'Y' INTO l_ret_chng FROM dual WHERE EXISTS
(SELECT 'Y' FROM pay_element_entries_f ee
WHERE ee.element_entry_id = nonrec_entry.element_entry_id
AND ee.effective_end_date = nonrec_entry.effective_end_date);
SELECT 'Y' INTO l_ret_chng FROM dual WHERE EXISTS
(SELECT 'Y' FROM pay_element_entries_f ee
WHERE ee.element_entry_id = nonrec_entry.element_entry_id
AND ee.creator_type = 'SP'
AND ee.effective_end_date = nonrec_entry.effective_end_date);
SELECT 'Y' INTO l_ret_chng FROM dual WHERE EXISTS
(SELECT 'Y' FROM pay_element_entry_values_f eev
WHERE eev.element_entry_id = nonrec_entry.element_entry_id
AND eev.effective_end_date = nonrec_entry.effective_end_date);
SELECT el.element_type_id
INTO l_element_type_id
FROM pay_element_links_f el
WHERE el.element_link_id = rec_entry.element_link_id
AND rec_entry.effective_end_date BETWEEN el.effective_start_date
AND el.effective_end_date;
hr_entry.delete_covered_dependants(
p_element_entry_id => rec_entry.element_entry_id,
p_end_date => p_term_date,
p_validation_start_date => p_term_date);
hr_entry.delete_beneficiaries(
p_element_entry_id => rec_entry.element_entry_id,
p_end_date => p_term_date,
p_validation_start_date => p_term_date);
** Original code, below, performs a delete from PAY_ELEMENT_ENTRIES_F
** and PAY_ELEMENT_ENTRY_VALUES_F where the entry effective start
** date is greater than the termination date (i.e. the entry starts
** after the employee is terminated). Therefore the entry, and its
** values, can be completely removed.
** The DML statements can be replaced with a single call to
** pay_element_entry_api.delete_element_entry using the 'ZAP' mode.
**
--
DELETE FROM pay_element_entry_values_f eev
WHERE eev.element_entry_id = rec_entry.element_entry_id
AND eev.effective_start_date > p_term_date;
DELETE FROM pay_element_entries_f ee
WHERE ee.element_entry_id = rec_entry.element_entry_id
AND ee.effective_start_date > p_term_date;
select object_version_number
into l_ee_object_version_number
from pay_element_entries_f
where element_entry_id = rec_entry.element_entry_Id
and effective_start_date = rec_entry.effective_start_date;
pay_element_entry_api.delete_element_entry(
p_validate => false,
p_datetrack_delete_mode => 'ZAP',
p_effective_date => rec_entry.effective_start_date,
p_element_entry_id => rec_entry.element_entry_id,
p_object_version_number => l_ee_object_version_number,
p_effective_start_date => l_ee_effective_start_date,
p_effective_end_date => l_ee_effective_end_date,
p_delete_warning => l_delete_warning
);
** Original code, below, updates PAY_ELEMENT_ENTRIES_F and
** PAY_ELEMENT_ENTRY_VALUES_F, setting the effective_end_date
** of the entry end date to the termination date where the
** entry end date falls some point after the termination date.
** The DML statements can be replaced with a single call to
** pay_element_entry_api.delete_element_entry using the 'DELETE' mode.
**
--
UPDATE pay_element_entry_values_f eev
SET eev.effective_end_date = p_term_date
WHERE eev.element_entry_id = rec_entry.element_entry_id
AND eev.effective_end_date > p_term_date;
UPDATE pay_element_entries_f ee
SET ee.effective_end_date = p_term_date
, ee.last_update_date = l_last_update_date
WHERE ee.element_entry_id = rec_entry.element_entry_id
AND ee.effective_end_date > p_term_date;
select object_version_number
into l_ee_object_version_number
from pay_element_entries_f
where element_entry_id = rec_entry.element_entry_Id
and effective_start_date = rec_entry.effective_start_date;
pay_element_entry_api.delete_element_entry(
p_validate => false,
p_datetrack_delete_mode => 'DELETE',
p_effective_date => p_term_date,
p_element_entry_id => rec_entry.element_entry_id,
p_object_version_number => l_ee_object_version_number,
p_effective_start_date => l_ee_effective_start_date,
p_effective_end_date => l_ee_effective_end_date,
p_delete_warning => l_delete_warning
);
SELECT el.element_type_id, et.process_in_run_flag
INTO l_element_type_id, l_process_in_run_flag
FROM pay_element_links_f el, pay_element_types_f et
WHERE el.element_link_id = nonrec_entry.element_link_id
AND et.element_type_id = el.element_type_id
AND nonrec_entry.effective_end_date BETWEEN el.effective_start_date
AND el.effective_end_date
/* Bug 1406063 */
AND nonrec_entry.effective_end_date BETWEEN et.effective_start_date
AND et.effective_end_date;
hr_entry.delete_covered_dependants(
p_element_entry_id => nonrec_entry.element_entry_id,
p_end_date => p_term_date,
p_validation_start_date => p_term_date);
hr_entry.delete_beneficiaries(
p_element_entry_id => nonrec_entry.element_entry_id,
p_end_date => p_term_date,
p_validation_start_date => p_term_date);
DELETE FROM pay_run_results rr
WHERE rr.source_type = 'E'
AND rr.source_id = nonrec_entry.element_entry_id
AND rr.status not like 'P%';
** Original code, below, performs a delete from PAY_ELEMENT_ENTRIES_F
** and PAY_ELEMENT_ENTRY_VALUES_F since the entry effective start
** date is greater than the termination date (i.e. the entry starts
** after the employee is terminated). Therefore the entry, and its
** values, can be completely removed.
** The DML statements can be replaced with a single call to
** pay_element_entry_api.delete_element_entry using the 'ZAP' mode.
**
DELETE FROM pay_element_entry_values_f eev
WHERE eev.element_entry_id = nonrec_entry.element_entry_id;
DELETE FROM pay_element_entries_f ee
WHERE ee.element_entry_id = nonrec_entry.element_entry_id;
select object_version_number
into l_ee_object_version_number
from pay_element_entries_f
where element_entry_id = nonrec_entry.element_entry_Id
and effective_start_date = nonrec_entry.effective_start_date;
pay_element_entry_api.delete_element_entry(
p_validate => false,
p_datetrack_delete_mode => 'ZAP',
p_effective_date => nonrec_entry.effective_start_date,
p_element_entry_id => nonrec_entry.element_entry_id,
p_object_version_number => l_ee_object_version_number,
p_effective_start_date => l_ee_effective_start_date,
p_effective_end_date => l_ee_effective_end_date,
p_delete_warning => l_delete_warning
);
hr_entry.delete_covered_dependants(
p_element_entry_id => nonrec_entry.element_entry_id,
p_end_date => p_term_date,
p_validation_start_date => p_term_date);
hr_entry.delete_beneficiaries(
p_element_entry_id => nonrec_entry.element_entry_id,
p_end_date => p_term_date,
p_validation_start_date => p_term_date);
hr_entry.delete_covered_dependants(
p_element_entry_id => nonrec_entry.element_entry_id,
p_end_date => p_term_date,
p_validation_start_date => p_final_process_date);
hr_entry.delete_beneficiaries(
p_element_entry_id => nonrec_entry.element_entry_id,
p_end_date => p_term_date,
p_validation_start_date => p_final_process_date);
** Original code, below, updates PAY_ELEMENT_ENTRIES_F and
** PAY_ELEMENT_ENTRY_VALUES_F, setting the effective_end_date
** of the entry end date to the termination date where the
** entry end date falls some point after the termination date.
** The DML statements can be replaced with a single call to
** pay_element_entry_api.delete_element_entry using the 'DELETE' mode.
**
UPDATE pay_element_entries_f ee
SET ee.effective_end_date = p_final_process_date
, ee.last_update_date = l_last_update_date
WHERE ee.element_entry_id = nonrec_entry.element_entry_id
AND ee.effective_end_date = nonrec_entry.effective_end_date;
UPDATE pay_element_entry_values_f eev
SET eev.effective_end_date = p_final_process_date
WHERE eev.element_entry_id = nonrec_entry.element_entry_id
AND eev.effective_end_date = nonrec_entry.effective_end_date;
select object_version_number
into l_ee_object_version_number
from pay_element_entries_f
where element_entry_id = nonrec_entry.element_entry_Id
and effective_start_date = nonrec_entry.effective_start_date;
pay_element_entry_api.delete_element_entry(
p_validate => false,
p_datetrack_delete_mode => 'DELETE',
p_effective_date => p_final_process_date,
p_element_entry_id => nonrec_entry.element_entry_id,
p_object_version_number => l_ee_object_version_number,
p_effective_start_date => l_ee_effective_start_date,
p_effective_end_date => l_ee_effective_end_date,
p_delete_warning => l_delete_warning
);
DELETE FROM per_pay_proposal_components
WHERE pay_proposal_id=pay_rec.pay_proposal_id;
DELETE FROM per_pay_proposals
WHERE pay_proposal_id=pay_rec.pay_proposal_id;
END delete_entries;
PROCEDURE delete_alus(p_assignment_id NUMBER,
p_term_date DATE,
--p_term_rule VARCHAR2,
--
-- 115.59 (START)
--
--p_changes IN OUT NOCOPY VARCHAR2) IS
p_changes IN OUT NOCOPY VARCHAR2,
p_alu_change_warning IN OUT NOCOPY VARCHAR2) IS
--
-- 115.59 (END)
--
--
l_alu_change VARCHAR2(1) := 'N';
l_proc varchar2(72):=g_package||'delete_alus';
SELECT NULL
FROM pay_payroll_actions ppa
,pay_assignment_actions paa
WHERE ppa.payroll_action_id = paa.payroll_action_id
AND ppa.action_type IN ('R','Q','V','B')
AND paa.assignment_id = p_assignment_id
AND NVL(ppa.date_earned,ppa.effective_date) > p_term_date;
DELETE FROM pay_assignment_link_usages_f alu
WHERE alu.assignment_id = p_assignment_id
AND alu.effective_start_date > p_term_date;
UPDATE pay_assignment_link_usages_f alu
SET alu.effective_end_date = p_term_date
WHERE alu.assignment_id = p_assignment_id
AND alu.effective_end_date > p_term_date;
END delete_alus;
delete_entries(p_assignment_id,
p_actual_term_date,
'A',
p_final_process_date,
l_cur_entries);
delete_alus(p_assignment_id,
p_actual_term_date,
'A',
l_cur_alus,
p_alu_change_warning);
delete_entries(p_assignment_id,
p_last_standard_date,
'L',
p_final_process_date,
l_cur_entries);
delete_alus(p_assignment_id,
p_last_standard_date,
'L',
l_cur_alus,
p_alu_change_warning);
delete_entries(p_assignment_id,
p_final_process_date,
'A',
p_final_process_date,
l_cur_entries);
delete_entries(p_assignment_id,
p_final_process_date,
'L',
p_final_process_date,
l_cur_entries);
delete_entries(p_assignment_id,
p_final_process_date,
'F',
p_final_process_date,
l_cur_entries);
delete_alus(p_assignment_id,
p_final_process_date,
--'F',
l_cur_alus,
p_alu_change_warning);
SELECT *
FROM per_people_f pp
WHERE pp.person_id = p_person_id
FOR UPDATE;
SELECT *
FROM per_assignments_f ass
WHERE ass.person_id = p_person_id
AND p_actual_termination_date
BETWEEN ass.effective_start_date
AND ass.effective_end_date
AND assignment_type = 'E'
FOR UPDATE;
select legislation_code
from per_business_groups
where business_group_id = p_business_group_id;
if p_trigger = 'PRE_UPDATE' then
hr_utility.set_location(l_proc,2);
SELECT current_employee_flag
INTO l_current_employee_flag
FROM per_people_f pp
WHERE pp.person_id = p_person_id
AND p_actual_termination_date + 1
BETWEEN pp.effective_start_date
AND pp.effective_end_date;
SELECT person_type_id
, c_emp_rec.current_applicant_flag
, null
, c_emp_rec.current_applicant_flag
INTO l_person_type_id
, l_current_applicant_flag
, l_current_employee_flag
, l_current_emp_or_apl_flag
FROM per_person_types
WHERE business_group_id = p_business_group_id --#3735333
AND default_flag = 'Y'
AND active_flag = 'Y'
AND ((c_emp_rec.current_applicant_flag = 'Y'
AND system_person_type = 'EX_EMP_APL')
OR (c_emp_rec.current_applicant_flag IS NULL
AND system_person_type = 'EX_EMP'));
INSERT INTO PER_ALL_PEOPLE_F
(person_id
,effective_start_date
,effective_end_date
,business_group_id
,person_type_id
,last_name
,start_date
,applicant_number
,comment_id
,current_applicant_flag
,current_emp_or_apl_flag
,current_employee_flag
,date_employee_data_verified
,date_of_birth
,email_address
,employee_number
,expense_check_send_to_address
,first_name
,full_name
,known_as
,marital_status
,middle_names
,nationality
,national_identifier
,previous_last_name
,registered_disabled_flag
,sex
,title
,suffix
,vendor_id
-- ,work_telephone
,request_id
,program_application_id
,program_id
,program_update_date
,attribute_category
,attribute1
,attribute2
,attribute3
,attribute4
,attribute5
,attribute6
,attribute7
,attribute8
,attribute9
,attribute10
,attribute11
,attribute12
,attribute13
,attribute14
,attribute15
,attribute16
,attribute17
,attribute18
,attribute19
,attribute20
,attribute21
,attribute22
,attribute23
,attribute24
,attribute25
,attribute26
,attribute27
,attribute28
,attribute29
,attribute30
,last_update_date
,last_updated_by
,last_update_login
,created_by
,creation_date
,per_information_category
,per_information1
,per_information2
,per_information3
,per_information4
,per_information5
,per_information6
,per_information7
,per_information8
,per_information9
,per_information10
,per_information11
,per_information12
,per_information13
,per_information14
,per_information15
,per_information16
,per_information17
,per_information18
,per_information19
,per_information20
,per_information21
,per_information22
,per_information23
,per_information24
,per_information25
,per_information26
,per_information27
,per_information28
,per_information29
,per_information30
,work_schedule
,correspondence_language
,student_status
,fte_capacity
,on_military_service
,second_passport_exists
,background_check_status
,background_date_check
,blood_type
,last_medical_test_date
,last_medical_test_by
,rehire_recommendation
,rehire_reason
,resume_exists
,resume_last_updated
,office_number
,internal_location
,mailstop
,honors
,pre_name_adjunct
,hold_applicant_date_until
,benefit_group_id
,receipt_of_death_cert_date
,coord_ben_med_pln_no
,coord_ben_no_cvg_flag
,uses_tobacco_flag
,dpdnt_adoption_date
,dpdnt_vlntry_svce_flag
,date_of_death
,original_date_of_hire
,town_of_birth
,region_of_birth
,country_of_birth
,global_person_id
)
VALUES
( c_emp_rec.person_id
, p_actual_termination_date + 1
, c_emp_rec.effective_end_date
, c_emp_rec.business_group_id
, l_person_type_id
, c_emp_rec.last_name
, c_emp_rec.start_date
, c_emp_rec.applicant_number
, c_emp_rec.comment_id
, l_current_applicant_flag
, l_current_emp_or_apl_flag
, l_current_employee_flag
, c_emp_rec.date_employee_data_verified
, c_emp_rec.date_of_birth
, c_emp_rec.email_address
, c_emp_rec.employee_number
, c_emp_rec.expense_check_send_to_address
, c_emp_rec.first_name
, c_emp_rec.full_name
, c_emp_rec.known_as
, c_emp_rec.marital_status
, c_emp_rec.middle_names
, c_emp_rec.nationality
, c_emp_rec.national_identifier
, c_emp_rec.previous_last_name
, c_emp_rec.registered_disabled_flag
, c_emp_rec.sex
, c_emp_rec.title
, c_emp_rec.suffix
, c_emp_rec.vendor_id
-- , c_emp_rec.work_telephone
, c_emp_rec.request_id
, c_emp_rec.program_application_id
, c_emp_rec.program_id
, c_emp_rec.program_update_date
, c_emp_rec.attribute_category
, c_emp_rec.attribute1
, c_emp_rec.attribute2
, c_emp_rec.attribute3
, c_emp_rec.attribute4
, c_emp_rec.attribute5
, c_emp_rec.attribute6
, c_emp_rec.attribute7
, c_emp_rec.attribute8
, c_emp_rec.attribute9
, c_emp_rec.attribute10
, c_emp_rec.attribute11
, c_emp_rec.attribute12
, c_emp_rec.attribute13
, c_emp_rec.attribute14
, c_emp_rec.attribute15
, c_emp_rec.attribute16
, c_emp_rec.attribute17
, c_emp_rec.attribute18
, c_emp_rec.attribute19
, c_emp_rec.attribute20
, c_emp_rec.attribute21
, c_emp_rec.attribute22
, c_emp_rec.attribute23
, c_emp_rec.attribute24
, c_emp_rec.attribute25
, c_emp_rec.attribute26
, c_emp_rec.attribute27
, c_emp_rec.attribute28
, c_emp_rec.attribute29
, c_emp_rec.attribute30
, SYSDATE
, -1
, -1
, c_emp_rec.created_by
, c_emp_rec.creation_date
, c_emp_rec.per_information_category
, c_emp_rec.per_information1
, c_emp_rec.per_information2
, c_emp_rec.per_information3
, c_emp_rec.per_information4
, c_emp_rec.per_information5
, c_emp_rec.per_information6
, c_emp_rec.per_information7
, c_emp_rec.per_information8
, c_emp_rec.per_information9
, c_emp_rec.per_information10
, c_emp_rec.per_information11
, c_emp_rec.per_information12
, c_emp_rec.per_information13
, c_emp_rec.per_information14
, c_emp_rec.per_information15
, c_emp_rec.per_information16
, c_emp_rec.per_information17
, c_emp_rec.per_information18
, c_emp_rec.per_information19
, c_emp_rec.per_information20
, c_emp_rec.per_information21
, c_emp_rec.per_information22
, c_emp_rec.per_information23
, c_emp_rec.per_information24
, c_emp_rec.per_information25
, c_emp_rec.per_information26
, c_emp_rec.per_information27
, c_emp_rec.per_information28
, c_emp_rec.per_information29
, c_emp_rec.per_information30
, c_emp_rec.work_schedule
, c_emp_rec.correspondence_language
, c_emp_rec.student_status
, c_emp_rec.fte_capacity
, c_emp_rec.on_military_service
, c_emp_rec.second_passport_exists
, c_emp_rec.background_check_status
, c_emp_rec.background_date_check
, c_emp_rec.blood_type
, c_emp_rec.last_medical_test_date
, c_emp_rec.last_medical_test_by
, c_emp_rec.rehire_recommendation
, c_emp_rec.rehire_reason
, c_emp_rec.resume_exists
, c_emp_rec.resume_last_updated
, c_emp_rec.office_number
, c_emp_rec.internal_location
, c_emp_rec.mailstop
, c_emp_rec.honors
, c_emp_rec.pre_name_adjunct
, c_emp_rec.hold_applicant_date_until
, c_emp_rec.benefit_group_id
, c_emp_rec.receipt_of_death_cert_date
, c_emp_rec.coord_ben_med_pln_no
, c_emp_rec.coord_ben_no_cvg_flag
, c_emp_rec.uses_tobacco_flag
, c_emp_rec.dpdnt_adoption_date
, c_emp_rec.dpdnt_vlntry_svce_flag
, c_emp_rec.date_of_death
, c_emp_rec.original_date_of_hire
, c_emp_rec.town_of_birth
, c_emp_rec.region_of_birth
, c_emp_rec.country_of_birth
, c_emp_rec.global_person_id
);
UPDATE per_people_f pp
SET pp.effective_end_date = p_actual_termination_date
WHERE CURRENT OF c_employee;
elsif p_trigger = 'POST_UPDATE' then
--
--
hr_utility.set_location(l_proc,50);
SELECT per_system_status
INTO l_per_system_status
FROM per_assignment_status_types
WHERE assignment_status_type_id = c_ass_rec.assignment_status_type_id;
hrempter.delete_assign_atd(c_ass_rec.assignment_id
,p_actual_termination_date);
hrempter.delete_assign_fpd(c_ass_rec.assignment_id
,p_final_process_date);
pay_us_update_tax_rec_pkg.terminate_emp_tax_records
(c_ass_rec.assignment_id
,p_final_process_date
,p_actual_termination_date);
hrempter.delete_de_assign(c_ass_rec.assignment_id
,p_final_process_date);
UPDATE per_assignments_f
SET effective_end_date = p_final_process_date
WHERE CURRENT OF c_assignment;
hrempter.delete_de_assign(c_ass_rec.assignment_id
,p_actual_termination_date);
select stt.assignment_status_type_id
into l_assignment_status_type_id
from per_assignment_status_types stt,
per_ass_status_type_amends sta
where nvl(sta.per_system_status,stt.per_system_status) =
'TERM_ASSIGN'
and stt.assignment_status_type_id =
sta.assignment_status_type_id (+)
and sta.business_group_id(+) = c_ass_rec.BUSINESS_GROUP_ID
and nvl(stt.business_group_id, c_ass_rec.BUSINESS_GROUP_ID) =
c_ass_rec.BUSINESS_GROUP_ID
and nvl(sta.active_flag,stt.active_flag) = 'Y'
and nvl(sta.default_flag, stt.default_flag) = 'Y';
INSERT INTO per_assignments_f
( ASSIGNMENT_ID
, EFFECTIVE_START_DATE
, EFFECTIVE_END_DATE
, BUSINESS_GROUP_ID
, RECRUITER_ID
, GRADE_ID
, POSITION_ID
, JOB_ID
, ASSIGNMENT_STATUS_TYPE_ID
, PAYROLL_ID
, LOCATION_ID
, SUPERVISOR_ID
, SPECIAL_CEILING_STEP_ID
, PERSON_ID
, ORGANIZATION_ID
, PEOPLE_GROUP_ID
, SOFT_CODING_KEYFLEX_ID
, VACANCY_ID
, ASSIGNMENT_SEQUENCE
, ASSIGNMENT_TYPE
, PRIMARY_FLAG
, APPLICATION_ID
, ASSIGNMENT_NUMBER
, CHANGE_REASON
, COMMENT_ID
, DATE_PROBATION_END
, DEFAULT_CODE_COMB_ID
, EMPLOYMENT_CATEGORY
, FREQUENCY
, INTERNAL_ADDRESS_LINE
, MANAGER_FLAG
, NORMAL_HOURS
, PERIOD_OF_SERVICE_ID
, PROBATION_PERIOD
, PROBATION_UNIT
, SET_OF_BOOKS_ID
, TIME_NORMAL_FINISH
, TIME_NORMAL_START
, PAY_BASIS_ID
, REQUEST_ID
, BARGAINING_UNIT_CODE
, LABOUR_UNION_MEMBER_FLAG
, HOURLY_SALARIED_CODE
, PROGRAM_APPLICATION_ID
, PROGRAM_ID
, PROGRAM_UPDATE_DATE
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, LAST_UPDATE_LOGIN
, CREATED_BY
, CREATION_DATE
, ASS_ATTRIBUTE_CATEGORY
, ASS_ATTRIBUTE1
, ASS_ATTRIBUTE2
, ASS_ATTRIBUTE3
, ASS_ATTRIBUTE4
, ASS_ATTRIBUTE5
, ASS_ATTRIBUTE6
, ASS_ATTRIBUTE7
, ASS_ATTRIBUTE8
, ASS_ATTRIBUTE9
, ASS_ATTRIBUTE10
, ASS_ATTRIBUTE11
, ASS_ATTRIBUTE12
, ASS_ATTRIBUTE13
, ASS_ATTRIBUTE14
, ASS_ATTRIBUTE15
, ASS_ATTRIBUTE16
, ASS_ATTRIBUTE17
, ASS_ATTRIBUTE18
, ASS_ATTRIBUTE19
, ASS_ATTRIBUTE20
, ASS_ATTRIBUTE21
, ASS_ATTRIBUTE22
, ASS_ATTRIBUTE23
, ASS_ATTRIBUTE24
, ASS_ATTRIBUTE25
, ASS_ATTRIBUTE26
, ASS_ATTRIBUTE27
, ASS_ATTRIBUTE28
, ASS_ATTRIBUTE29
, ASS_ATTRIBUTE30
, CAGR_GRADE_DEF_ID
, CAGR_ID_FLEX_NUM
, COLLECTIVE_AGREEMENT_ID
, CONTRACT_ID
, ESTABLISHMENT_ID
, OBJECT_VERSION_NUMBER
, PERF_REVIEW_PERIOD
, PERF_REVIEW_PERIOD_FREQUENCY
, PERSON_REFERRED_BY_ID
, RECRUITMENT_ACTIVITY_ID
, SAL_REVIEW_PERIOD
, SAL_REVIEW_PERIOD_FREQUENCY
, SOURCE_ORGANIZATION_ID
, SOURCE_TYPE
, TITLE
, NOTICE_PERIOD
, NOTICE_PERIOD_UOM
, WORK_AT_HOME
, EMPLOYEE_CATEGORY
, JOB_POST_SOURCE_NAME )
VALUES
( c_ass_rec.ASSIGNMENT_ID
, p_actual_termination_date + 1
, l_effective_end_date
, c_ass_rec.BUSINESS_GROUP_ID
, c_ass_rec.RECRUITER_ID
, c_ass_rec.GRADE_ID
, c_ass_rec.POSITION_ID
, c_ass_rec.JOB_ID
, nvl (p_assignment_status_type_id, l_assignment_status_type_id)
, c_ass_rec.PAYROLL_ID
, c_ass_rec.LOCATION_ID
, c_ass_rec.SUPERVISOR_ID
, c_ass_rec.SPECIAL_CEILING_STEP_ID
, c_ass_rec.PERSON_ID
, c_ass_rec.ORGANIZATION_ID
, c_ass_rec.PEOPLE_GROUP_ID
, c_ass_rec.SOFT_CODING_KEYFLEX_ID
, c_ass_rec.VACANCY_ID
, c_ass_rec.ASSIGNMENT_SEQUENCE
, c_ass_rec.ASSIGNMENT_TYPE
, c_ass_rec.PRIMARY_FLAG
, c_ass_rec.APPLICATION_ID
, c_ass_rec.ASSIGNMENT_NUMBER
, c_ass_rec.CHANGE_REASON
, c_ass_rec.COMMENT_ID
, c_ass_rec.DATE_PROBATION_END
, c_ass_rec.DEFAULT_CODE_COMB_ID
, c_ass_rec.EMPLOYMENT_CATEGORY
, c_ass_rec.FREQUENCY
, c_ass_rec.INTERNAL_ADDRESS_LINE
, c_ass_rec.MANAGER_FLAG
, c_ass_rec.NORMAL_HOURS
, c_ass_rec.PERIOD_OF_SERVICE_ID
, c_ass_rec.PROBATION_PERIOD
, c_ass_rec.PROBATION_UNIT
, c_ass_rec.SET_OF_BOOKS_ID
, c_ass_rec.TIME_NORMAL_FINISH
, c_ass_rec.TIME_NORMAL_START
, c_ass_rec.PAY_BASIS_ID
, c_ass_rec.REQUEST_ID
, c_ass_rec.BARGAINING_UNIT_CODE
, c_ass_rec.LABOUR_UNION_MEMBER_FLAG
, c_ass_rec.HOURLY_SALARIED_CODE
, c_ass_rec.PROGRAM_APPLICATION_ID
, c_ass_rec.PROGRAM_ID
, c_ass_rec.PROGRAM_UPDATE_DATE
, SYSDATE
, -1
, -1
, c_ass_rec.CREATED_BY
, c_ass_rec.CREATION_DATE
, c_ass_rec.ASS_ATTRIBUTE_CATEGORY
, c_ass_rec.ASS_ATTRIBUTE1
, c_ass_rec.ASS_ATTRIBUTE2
, c_ass_rec.ASS_ATTRIBUTE3
, c_ass_rec.ASS_ATTRIBUTE4
, c_ass_rec.ASS_ATTRIBUTE5
, c_ass_rec.ASS_ATTRIBUTE6
, c_ass_rec.ASS_ATTRIBUTE7
, c_ass_rec.ASS_ATTRIBUTE8
, c_ass_rec.ASS_ATTRIBUTE9
, c_ass_rec.ASS_ATTRIBUTE10
, c_ass_rec.ASS_ATTRIBUTE11
, c_ass_rec.ASS_ATTRIBUTE12
, c_ass_rec.ASS_ATTRIBUTE13
, c_ass_rec.ASS_ATTRIBUTE14
, c_ass_rec.ASS_ATTRIBUTE15
, c_ass_rec.ASS_ATTRIBUTE16
, c_ass_rec.ASS_ATTRIBUTE17
, c_ass_rec.ASS_ATTRIBUTE18
, c_ass_rec.ASS_ATTRIBUTE19
, c_ass_rec.ASS_ATTRIBUTE20
, c_ass_rec.ASS_ATTRIBUTE21
, c_ass_rec.ASS_ATTRIBUTE22
, c_ass_rec.ASS_ATTRIBUTE23
, c_ass_rec.ASS_ATTRIBUTE24
, c_ass_rec.ASS_ATTRIBUTE25
, c_ass_rec.ASS_ATTRIBUTE26
, c_ass_rec.ASS_ATTRIBUTE27
, c_ass_rec.ASS_ATTRIBUTE28
, c_ass_rec.ASS_ATTRIBUTE29
, c_ass_rec.ASS_ATTRIBUTE30
, c_ass_rec.CAGR_GRADE_DEF_ID
, c_ass_rec.CAGR_ID_FLEX_NUM
, c_ass_rec.COLLECTIVE_AGREEMENT_ID
, c_ass_rec.CONTRACT_ID
, c_ass_rec.ESTABLISHMENT_ID
, c_ass_rec.OBJECT_VERSION_NUMBER
, c_ass_rec.PERF_REVIEW_PERIOD
, c_ass_rec.PERF_REVIEW_PERIOD_FREQUENCY
, c_ass_rec.PERSON_REFERRED_BY_ID
, c_ass_rec.RECRUITMENT_ACTIVITY_ID
, c_ass_rec.SAL_REVIEW_PERIOD
, c_ass_rec.SAL_REVIEW_PERIOD_FREQUENCY
, c_ass_rec.SOURCE_ORGANIZATION_ID
, c_ass_rec.SOURCE_TYPE
, c_ass_rec.TITLE
, c_ass_rec.NOTICE_PERIOD
, c_ass_rec.NOTICE_PERIOD_UOM
, c_ass_rec.WORK_AT_HOME
, c_ass_rec.EMPLOYEE_CATEGORY
, c_ass_rec.JOB_POST_SOURCE_NAME);
UPDATE per_assignments_f ass
SET ass.effective_end_date = p_actual_termination_date
WHERE CURRENT OF c_assignment;
hrempter.delete_employee_atd(p_person_id
,p_actual_termination_date);
SELECT ass.assignment_id
FROM per_assignments_f ass
WHERE ass.person_id = p_person_id
AND p_final_process_date
BETWEEN ass.effective_start_date
AND ass.effective_end_date
FOR UPDATE;
if p_trigger = 'PRE_UPDATE' then
--
hr_utility.set_location(l_proc,2);
SELECT actual_termination_date
INTO l_actual_termination_date
FROM per_periods_of_service
WHERE person_id = p_person_id
AND final_process_date IS NULL
AND p_final_process_date BETWEEN date_start
AND to_date('31/12/4712','DD/MM/YYYY');
SELECT current_employee_flag
INTO l_current_employee_flag
FROM per_people_f pp
WHERE pp.person_id = p_person_id
AND ((p_final_process_date = l_actual_termination_date
AND p_final_process_date + 1 BETWEEN pp.effective_start_date
AND pp.effective_end_date)
OR (p_final_process_date > l_actual_termination_date
AND p_final_process_date
BETWEEN pp.effective_start_date
AND pp.effective_end_date));
elsif p_trigger = 'POST_UPDATE' then
--
--
hr_utility.set_location(l_proc,15);
hrempter.delete_assign_fpd(c_ass_rec.assignment_id
,p_final_process_date);
hrempter.delete_de_assign(c_ass_rec.assignment_id
,p_final_process_date);
UPDATE per_assignments_f ass
SET ass.effective_end_date = p_final_process_date
WHERE CURRENT OF c_assignment;
pay_us_update_tax_rec_pkg.terminate_emp_tax_records
(c_ass_rec.assignment_id
,p_final_process_date);
select *
from per_all_assignments_f
where assignment_id = c_assignment_id
and c_effective_date between effective_start_date
and effective_end_date;
SELECT assignment_id
, assignment_status_type_id
, business_group_id
FROM per_assignments_f ass
WHERE ass.person_id = p_person_id
AND ass.effective_end_date = p_actual_termination_date
FOR UPDATE;
SELECT assignment_id
, assignment_status_type_id
, business_group_id
, effective_start_date
, effective_end_date
, payroll_id
, object_version_number
FROM per_assignments_f ass
WHERE ass.person_id = p_person_id
AND ass.effective_end_date = p_actual_termination_date
FOR UPDATE;
select *
from per_periods_of_service
where person_id = p_person_id
and actual_termination_date = p_actual_termination_date;
select role_id
,object_version_number
,old_end_date from
per_roles
where person_id = p_person_id
and end_date = p_actual_termination_date
for update nowait;
select *
from per_assignment_budget_values_f
where assignment_id = p_assignment_id
and effective_end_date = l_final_process_date;
select legislation_code
from per_business_groups
where business_group_id = p_business_group_id;
select '1'
from user_objects
where object_name = p_pkg_name
and object_type = 'PACKAGE';
SELECT pos.period_of_service_id
, pos.business_group_id
, pos.leaving_reason
, pos.date_start
, pos.final_process_date
, pos.last_standard_process_date
INTO l_period_of_service_id
, l_business_group_id
, l_old_leaving_reason
, l_date_start
, l_final_process_date
, l_last_standard_process_date
FROM per_periods_of_service pos
WHERE pos.person_id = p_person_id
AND pos.actual_termination_date = p_actual_termination_date;
/* UPDATE per_contracts_f pc
SET pc.effective_end_date = to_date('31/12/4712','DD/MM/YYYY')
WHERE pc.person_id = p_person_id
AND p_actual_termination_date
BETWEEN pc.effective_start_date
AND pc.effective_end_date;
DELETE from per_contracts_f pc
WHERE pc.person_id = p_person_id
AND pc.effective_start_date > p_actual_termination_date;*/
UPDATE per_all_people_f pp
SET pp.effective_end_date = to_date('31/12/4712','DD/MM/YYYY')
WHERE pp.person_id = p_person_id
AND p_actual_termination_date
BETWEEN pp.effective_start_date
AND pp.effective_end_date;
DELETE per_all_people_f pp
WHERE pp.person_id = p_person_id
AND pp.effective_start_date > p_actual_termination_date;
UPDATE per_periods_of_service pos
SET pos.actual_termination_date = null
, pos.last_standard_process_date = null
, pos.final_process_date = null
, pos.termination_accepted_person_id = null
, pos.leaving_reason = null
, pos.accepted_termination_date = null
WHERE pos.person_id = p_person_id
AND pos.actual_termination_date = p_actual_termination_date;
UPDATE per_periods_of_service pos
SET pos.actual_termination_date = null
, pos.last_standard_process_date = null
, pos.final_process_date = null
, pos.termination_accepted_person_id = null
, pos.leaving_reason = null
, pos.notified_termination_date = null
, pos.projected_termination_date = null
, pos.accepted_termination_date = null
WHERE pos.person_id = p_person_id
AND pos.actual_termination_date = p_actual_termination_date;
SELECT per_system_status
INTO l_per_system_status
FROM per_assignment_status_types
WHERE assignment_status_type_id = c_ass_rec.assignment_status_type_id;
UPDATE per_assignments_f ass
SET ass.effective_end_date = l_effective_end_date
WHERE assignment_id = c_ass_rec.assignment_id
AND effective_end_date = l_max_end_date;
pay_pog_all_assignments_pkg.after_delete
(p_effective_date => l_session_date
,p_datetrack_mode => 'DELETE_NEXT_CHANGE'
,p_validation_start_date => c_ass_rec.effective_START_DATE
,p_validation_end_date => c_ass_rec.effective_end_date
,P_ASSIGNMENT_ID => c_ass_rec.assignment_id
,P_EFFECTIVE_END_DATE => l_effective_end_of_time
,P_EFFECTIVE_START_DATE => c_ass_rec.effective_START_DATE
,P_OBJECT_VERSION_NUMBER => l_object_version_number
,P_ORG_NOW_NO_MANAGER_WARNING => null
,P_APPLICANT_RANK_O => null
,P_APPLICATION_ID_O => null
,P_ASSIGNMENT_CATEGORY_O => null
, P_ASSIGNMENT_NUMBER_O => null
,P_ASSIGNMENT_SEQUENCE_O => null
,P_ASSIGNMENT_STATUS_TYPE_ID_O => null
,P_ASSIGNMENT_TYPE_O => null
,P_ASS_ATTRIBUTE1_O => null
,P_ASS_ATTRIBUTE10_O => null
,P_ASS_ATTRIBUTE11_O => null
,P_ASS_ATTRIBUTE12_O => null
,P_ASS_ATTRIBUTE13_O => null
,P_ASS_ATTRIBUTE14_O => null
,P_ASS_ATTRIBUTE15_O => null
,P_ASS_ATTRIBUTE16_O => null
,P_ASS_ATTRIBUTE17_O => null
,P_ASS_ATTRIBUTE18_O => null
,P_ASS_ATTRIBUTE19_O => null
,P_ASS_ATTRIBUTE2_O => null
,P_ASS_ATTRIBUTE20_O => null
,P_ASS_ATTRIBUTE21_O => null
,P_ASS_ATTRIBUTE22_O => null
,P_ASS_ATTRIBUTE23_O => null
,P_ASS_ATTRIBUTE24_O => null
,P_ASS_ATTRIBUTE25_O => null
,P_ASS_ATTRIBUTE26_O => null
,P_ASS_ATTRIBUTE27_O => null
,P_ASS_ATTRIBUTE28_O => null
,P_ASS_ATTRIBUTE29_O => null
,P_ASS_ATTRIBUTE3_O => null
,P_ASS_ATTRIBUTE30_O =>null
,P_ASS_ATTRIBUTE4_O =>null
,P_ASS_ATTRIBUTE5_O => null
,P_ASS_ATTRIBUTE6_O => null
,P_ASS_ATTRIBUTE7_O => null
,P_ASS_ATTRIBUTE8_O => null
,P_ASS_ATTRIBUTE9_O => null
,P_ASS_ATTRIBUTE_CATEGORY_O => null
,P_BARGAINING_UNIT_CODE_O => null
,P_BUSINESS_GROUP_ID_O => c_ass_rec.business_group_id
,P_CAGR_GRADE_DEF_ID_O => null
,P_CAGR_ID_FLEX_NUM_O => null
,P_CHANGE_REASON_O => null
,P_COLLECTIVE_AGREEMENT_ID_O => null
,P_COMMENT_ID_O => null
,P_CONTRACT_ID_O => null
,P_DATE_PROBATION_END_O => null
,P_DEFAULT_CODE_COMB_ID_O => null
,P_EFFECTIVE_END_DATE_O => l_effective_end_date1
,P_EFFECTIVE_START_DATE_O => l_effective_start_date1
,P_EMPLOYEE_CATEGORY_O => null
,P_EMPLOYMENT_CATEGORY_O => null
,P_ESTABLISHMENT_ID_O => null
,P_FREQUENCY_O => null
,P_GRADE_ID_O => null
,P_HOURLY_SALARIED_CODE_O => null
,P_INTERNAL_ADDRESS_LINE_O => null
,P_JOB_ID_O => null
,P_JOB_POST_SOURCE_NAME_O => null
,P_LABOUR_UNION_MEMBER_FLAG_O => null
,P_LOCATION_ID_O => null
,P_MANAGER_FLAG_O => null
,P_NORMAL_HOURS_O => null
,P_NOTICE_PERIOD_O => null
,P_NOTICE_PERIOD_UOM_O => null
,P_OBJECT_VERSION_NUMBER_O => null
,P_ORGANIZATION_ID_O => null
,P_PAYROLL_ID_O => c_ass_rec.payroll_id
,P_PAY_BASIS_ID_O => null
,P_PEOPLE_GROUP_ID_O => null
,P_PERF_REVIEW_PERIOD_O => null
,P_PERF_REVIEW_PERIOD_FREQUEN_O => null
,P_PERIOD_OF_SERVICE_ID_O => null
,P_PERSON_ID_O => null
,P_PERSON_REFERRED_BY_ID_O => null
,P_PLACEMENT_DATE_START_O => null
,P_POSITION_ID_O => null
,P_POSTING_CONTENT_ID_O => null
,P_PRIMARY_FLAG_O => null
,P_PROBATION_PERIOD_O => null
,P_PROBATION_UNIT_O => null
,P_PROGRAM_APPLICATION_ID_O => null
,P_PROGRAM_ID_O => null
,P_PROGRAM_UPDATE_DATE_O => null
,P_PROJECT_TITLE_O => null
,P_RECRUITER_ID_O => null
,P_RECRUITMENT_ACTIVITY_ID_O => null
,P_REQUEST_ID_O => null
,P_SAL_REVIEW_PERIOD_O => null
,P_SAL_REVIEW_PERIOD_FREQUEN_O => null
,P_SET_OF_BOOKS_ID_O => null
,P_SOFT_CODING_KEYFLEX_ID_O => null
,P_SOURCE_ORGANIZATION_ID_O => null
,P_SOURCE_TYPE_O => null
,P_SPECIAL_CEILING_STEP_ID_O => null
,P_SUPERVISOR_ID_O => null
,P_TIME_NORMAL_FINISH_O => null
,P_TIME_NORMAL_START_O => null
,P_TITLE_O => null
,P_VACANCY_ID_O => null
,P_VENDOR_ASSIGNMENT_NUMBER_O => null
,P_VENDOR_EMPLOYEE_NUMBER_O => null
,P_VENDOR_ID_O => null
,P_WORK_AT_HOME_O => null
,P_GRADE_LADDER_PGM_ID_O => null
,P_SUPERVISOR_ASSIGNMENT_ID_O => null
,P_VENDOR_SITE_ID_O => null
,P_PO_HEADER_ID_O => null
,P_PO_LINE_ID_O => null
,P_PROJECTED_ASSIGNMENT_END_O => null
);
UPDATE per_assignments_f ass
SET ass.assignment_status_type_id = l_asg_status_type_id
WHERE assignment_id = c_ass_rec.assignment_id
AND effective_start_date >= p_actual_termination_date;
select effective_end_date
into l_new_effective_end_date
from per_all_assignments_f
where assignment_id = c_ass_rec.assignment_id
and effective_start_date = p_actual_termination_date+1;
update per_all_assignments_f
set effective_end_date = l_new_effective_end_date
where current of c_assignment;
delete from per_all_assignments_f
where assignment_id = c_ass_rec.assignment_id
and effective_start_date = p_actual_termination_date +1;
pay_pog_all_assignments_pkg.after_delete
(p_effective_date => l_session_date
,p_datetrack_mode => 'DELETE_NEXT_CHANGE'
,p_validation_start_date => c_ass_rec.effective_START_DATE
,p_validation_end_date => c_ass_rec.effective_end_date
,P_ASSIGNMENT_ID => c_ass_rec.assignment_id
,P_EFFECTIVE_END_DATE => l_effective_end_of_time
,P_EFFECTIVE_START_DATE => c_ass_rec.effective_START_DATE
,P_OBJECT_VERSION_NUMBER => l_object_version_number
,P_ORG_NOW_NO_MANAGER_WARNING => null
,P_APPLICANT_RANK_O => null
,P_APPLICATION_ID_O => null
,P_ASSIGNMENT_CATEGORY_O => null
, P_ASSIGNMENT_NUMBER_O => null
,P_ASSIGNMENT_SEQUENCE_O => null
,P_ASSIGNMENT_STATUS_TYPE_ID_O => null
,P_ASSIGNMENT_TYPE_O => null
,P_ASS_ATTRIBUTE1_O => null
,P_ASS_ATTRIBUTE10_O => null
,P_ASS_ATTRIBUTE11_O => null
,P_ASS_ATTRIBUTE12_O => null
,P_ASS_ATTRIBUTE13_O => null
,P_ASS_ATTRIBUTE14_O => null
,P_ASS_ATTRIBUTE15_O => null
,P_ASS_ATTRIBUTE16_O => null
,P_ASS_ATTRIBUTE17_O => null
,P_ASS_ATTRIBUTE18_O => null
,P_ASS_ATTRIBUTE19_O => null
,P_ASS_ATTRIBUTE2_O => null
,P_ASS_ATTRIBUTE20_O => null
,P_ASS_ATTRIBUTE21_O => null
,P_ASS_ATTRIBUTE22_O => null
,P_ASS_ATTRIBUTE23_O => null
,P_ASS_ATTRIBUTE24_O => null
,P_ASS_ATTRIBUTE25_O => null
,P_ASS_ATTRIBUTE26_O => null
,P_ASS_ATTRIBUTE27_O => null
,P_ASS_ATTRIBUTE28_O => null
,P_ASS_ATTRIBUTE29_O => null
,P_ASS_ATTRIBUTE3_O => null
,P_ASS_ATTRIBUTE30_O =>null
,P_ASS_ATTRIBUTE4_O =>null
,P_ASS_ATTRIBUTE5_O => null
,P_ASS_ATTRIBUTE6_O => null
,P_ASS_ATTRIBUTE7_O => null
,P_ASS_ATTRIBUTE8_O => null
,P_ASS_ATTRIBUTE9_O => null
,P_ASS_ATTRIBUTE_CATEGORY_O => null
,P_BARGAINING_UNIT_CODE_O => null
,P_BUSINESS_GROUP_ID_O => c_ass_rec.business_group_id
,P_CAGR_GRADE_DEF_ID_O => null
,P_CAGR_ID_FLEX_NUM_O => null
,P_CHANGE_REASON_O => null
,P_COLLECTIVE_AGREEMENT_ID_O => null
,P_COMMENT_ID_O => null
,P_CONTRACT_ID_O => null
,P_DATE_PROBATION_END_O => null
,P_DEFAULT_CODE_COMB_ID_O => null
,P_EFFECTIVE_END_DATE_O => l_final_process_date
,P_EFFECTIVE_START_DATE_O => l_effective_start_date1
,P_EMPLOYEE_CATEGORY_O => null
,P_EMPLOYMENT_CATEGORY_O => null
,P_ESTABLISHMENT_ID_O => null
,P_FREQUENCY_O => null
,P_GRADE_ID_O => null
,P_HOURLY_SALARIED_CODE_O => null
,P_INTERNAL_ADDRESS_LINE_O => null
,P_JOB_ID_O => null
,P_JOB_POST_SOURCE_NAME_O => null
,P_LABOUR_UNION_MEMBER_FLAG_O => null
,P_LOCATION_ID_O => null
,P_MANAGER_FLAG_O => null
,P_NORMAL_HOURS_O => null
,P_NOTICE_PERIOD_O => null
,P_NOTICE_PERIOD_UOM_O => null
,P_OBJECT_VERSION_NUMBER_O => null
,P_ORGANIZATION_ID_O => null
,P_PAYROLL_ID_O => c_ass_rec.payroll_id
,P_PAY_BASIS_ID_O => null
,P_PEOPLE_GROUP_ID_O => null
,P_PERF_REVIEW_PERIOD_O => null
,P_PERF_REVIEW_PERIOD_FREQUEN_O => null
,P_PERIOD_OF_SERVICE_ID_O => null
,P_PERSON_ID_O => null
,P_PERSON_REFERRED_BY_ID_O => null
,P_PLACEMENT_DATE_START_O => null
,P_POSITION_ID_O => null
,P_POSTING_CONTENT_ID_O => null
,P_PRIMARY_FLAG_O => null
,P_PROBATION_PERIOD_O => null
,P_PROBATION_UNIT_O => null
,P_PROGRAM_APPLICATION_ID_O => null
,P_PROGRAM_ID_O => null
,P_PROGRAM_UPDATE_DATE_O => null
,P_PROJECT_TITLE_O => null
,P_RECRUITER_ID_O => null
,P_RECRUITMENT_ACTIVITY_ID_O => null
,P_REQUEST_ID_O => null
,P_SAL_REVIEW_PERIOD_O => null
,P_SAL_REVIEW_PERIOD_FREQUEN_O => null
,P_SET_OF_BOOKS_ID_O => null
,P_SOFT_CODING_KEYFLEX_ID_O => null
,P_SOURCE_ORGANIZATION_ID_O => null
,P_SOURCE_TYPE_O => null
,P_SPECIAL_CEILING_STEP_ID_O => null
,P_SUPERVISOR_ID_O => null
,P_TIME_NORMAL_FINISH_O => null
,P_TIME_NORMAL_START_O => null
,P_TITLE_O => null
,P_VACANCY_ID_O => null
,P_VENDOR_ASSIGNMENT_NUMBER_O => null
,P_VENDOR_EMPLOYEE_NUMBER_O => null
,P_VENDOR_ID_O => null
,P_WORK_AT_HOME_O => null
,P_GRADE_LADDER_PGM_ID_O => null
,P_SUPERVISOR_ASSIGNMENT_ID_O => null
,P_VENDOR_SITE_ID_O => null
,P_PO_HEADER_ID_O => null
,P_PO_LINE_ID_O => null
,P_PROJECTED_ASSIGNMENT_END_O => null
);
UPDATE per_secondary_ass_statuses sas
SET sas.end_date = null
WHERE sas.assignment_id = c_ass_rec.assignment_id
AND sas.end_date = l_final_process_date;
UPDATE pay_personal_payment_methods_f ppm
SET ppm.effective_end_date = l_effective_end_date
WHERE ppm.assignment_id = c_ass_rec.assignment_id
AND ppm.effective_end_date = l_final_process_date;
UPDATE pay_cost_allocations_f pca
SET pca.effective_end_date = l_effective_end_date
WHERE pca.assignment_id = c_ass_rec.assignment_id
AND pca.effective_end_date = l_final_process_date;
UPDATE per_spinal_point_placements_f spp
SET spp.effective_end_date = l_effective_end_date
WHERE spp.assignment_id = c_ass_rec.assignment_id
AND spp.effective_end_date = l_final_process_date;
pay_us_update_tax_rec_pkg.reverse_term_emp_tax_records
(c_ass_rec.assignment_id
,l_final_process_date);
update per_assignment_budget_values_f abv
set abv.effective_end_date = l_effective_end_date
where abv.assignment_id = c_ass_rec.assignment_id
and abv.assignment_budget_value_id = l_c2.assignment_budget_value_id
and abv.effective_end_date = l_final_process_date;
,'DELETE_NEXT_CHANGE'
,null
,null);
per_supplementary_role_api.update_supplementary_role(
p_effective_date => p_actual_termination_date
,p_role_id => roles_rec.role_id
,p_object_version_number => roles_rec.object_version_number
,p_end_date => roles_rec.old_end_date
,p_old_end_date => null
);