[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
INSERT, UPDATE and DELETE assignments and all
associated tables :
PER_ASSIGNMENTS_F
PER_SECONDARY_ASSIGNMENT_STATUSES
PER_ASSIGNMENT_BUDGET_VALUES_F
Change List
-----------
Version Date Author ER/CR No. Description of Change
-------+---------+----------+---------+--------------------------
70.0 19-NOV-92 SZWILLIA Date Created
70.8 30-DEC-92 SZWILLIA Added error locations.
70.9 08-FEB-93 JHOBBS Changed secondary_asg.. to
secondary_ass...
70.10 11-FEB-93 JRHODES When searching for an assignment
TERM_ASSIGN row that has been brought
about by the Employee Termination
the date to look for should be
ACTUAL_TERMINATION_DATE + 1 (the day
after actual termination date).
70.11 15-FEB-93 JRHODES In check_term setting of END_DATE
for DELETE_NEXT_CHANGE was incorrect.
It now only resets the End DATE to the
ATD when the current en date is after
the ATD.
70.12 16-FEB-93 JHOBBS Added maintain_alu_asg procedure for
maintaining alus for the assignment.
70.13 03-MAR-93 JHOBBS Removed maintain_alu_asg. It is now in
hrentmnt.
70.14 10-MAR-93 JRHODES Included extra Set_location calls.
All dates are passed in as type DATE.
Select for Update when date effectively
updating the Assignment.
Added CURSOR to select for
update Assignment rows in
DO_PRIMARY_UPDATE
70.23 11-MAR-93 NKHAN Added 'exit' to end of code
70.24 25-MAR-93 JRHODES Added procedure 'tidy_up_ref_int'
70.25 27-MAY-93 JRHODES PER_SECONDARY_ASS_STATUSES are now
removed when they start after an
assignment end date.
70.26 02-JUN-93 JRHODES PER_SECONDARY_ASS_STATUSES are removed
totally on ZAP.
The ref int check on LETTER REQUESTS
is no longer required - they are
Auto Shut Down.
70.27 07-JUN-93 JRHODES New Procedure 'call_terminate_entries'
70.31 11-OCT-93 JRHODES Added extra columns to
Insert statement in do_primary_update
Bug No 240
70.32 12-OCT-93 JRHODES Fixed referential integrity checking
omission when assignment
is ENDED by check_term.
80.3 15-OCT-93 JRHODES Added check_for_cobra
80.4 04-NOV-93 PBARRY Added pay_proposals check for
del_ref_int_delete.
80.5 09-DEC-93 JRhodes New Procedure 'test_for_cancel_reterm'
80.6 03-Feb-93 JRhodes Bug 370
70.32 16-JUN-94 PShergill Fixed 220466 added ASS_ATTRIBUTE21..30
70.33 04-Jun-94 JRhodes Added Validate_Pos
70.34 26-AUG-94 JRhodes WWBUG# 232359 -
added check to del_ref_int_check
to ensure that Employee ASG cannot be
removed if they have an earlier
Applicant ASG
70.39 28-OCT-94 RFine Amended load_budget_values to prevent
one from being loaded if it already
exists.
70.40 23-NOV-94 RFine Suppressed index on business_group_id
70.41 02-MAR-95 JRhodes Added order by to select of
periods of service - fix to 265262
70.42 25-MAY-95 JRhodes 273820
Fixed insert into budget values
statement to make better use of
default_budget_values view
70.43 21-JUL-95 AForte Changed tokenised messages to
AMills hard coded messages.
From HR_6401_ASS_DEL_ASS (tokenised)
To
HR_7625_ASS_DEL_APP_ASS
HR_ 7630_ASS_EVE_DEL_ASS
HR_7633_ASS_EVE_END_ASS
HR_7634_ASS_LET_DEL_ASS
HR_7637_ASS_EVE_END_ASS
HR_7638_ASS_COST_DEL_ASS
HR_7641_ASS_COST_END_ASS
HR_7642_ASS_INF_DEL_ASS
HR_7652_ASS_STAT_DEL_ASS
HR_7655_ASS_SATA_END_ASS
HR_7656_ASS_PAY_DEL_ASS
HR_7659_ASS_PAY_END_ASS
HR_7664_ASS_ASS_DEL_ASS
HR_7667_ASS_ASS_END_ASS
HR_7668_ASS_COBR_DEL_ASS
HR_7671_ASS_COBR_END_ASS
HR_7672_ASS_COBRA_DEL_ASS
HR_7675_ASS_COBRA_END_ASS
70.44 12-AUG-94 RFine 306211 In the procedure check_ass_for_primary,
NVL null ATD to EOT - 1 instead of EOT.
This is because the code adds 1 to the
value at certain points, and trying to
add 1 to EOT raises an ORA-1841 error.
So use Dec 30 instead of Dec 31.
70.45 02-JUL-96 SXShah Added call to ota_predel_asg_validation
for OTA to perform referential integrity
checks.
70.46 17-Oct-96 VTreiger 306710 Changed call to terminate_entries_and_
alus.
70.49 17-Jan-97 JAlloun 424224 Amended cursor get_candidate_primary_ass, so the
effective_start_date is between the session date
and the actual termination date.
Also the per_system_status = ACTIVE_ASSIGN for
the particular assignment.
70.50 18-Mar-98 fychu 642566 1) Removed code in del_ref_int_check
procedure. APP-07642 error message
is no longer issued if there is
per_assignment_extra_info exists on
a delete.
2) Added code to del_ref_int_delete
procedure to remove
per_assignment_extra_info records
when an assignment is deleted.
110.4 16-APR-1998 SASmith Due to date tracking of the per_assignment_budget_values_f
table the following changes have been made.
1.load_budget_values parameters changed to include
effective start and end dates and also changed to ensure these
are added when the row is inserted into the db.
2.Procedure del_ref_int_delete. remove the current zap of
per_assignment_budget_values_f and include a delete,zap
and future logic instead. Also change to the '_F' table.
3.delete_ass_ref_int - change to reference the '_F' per_assignment_
budget_values_f.
4.tidy_up_ref_int - new logic to handle change in assignment
effective end date to ensure this cascades to the assignment_
budget_values.
NOTE: As part of these changes no change has been made to del_ref
_int_check. The reason for pointing this out nocopy is potentially a change could
have been included so that any forward dated changes would not
allow the user to delete the row. It was decided that as per_assignment_
budget_values is essentially an attribute of assignment then deletes
should be cascaded.
110.6 23-JUN-1998 mshah 682452 Corrected typo in call to message numbers 7630 and 7633: '...7630)...'
was changed to '...7630...'.
110.7 22-DEC-1998 bgoodsel 679966 Removed restriction in not exists... sub-query that causes
duplicate rows in some circumstances
110.8 07-MAY-1999 achauhan For Bug# 787633, in del_ref_int_check, added the join to
per_assignments_f to check for the primary assignment. If
it is not a primary assignment then let it get purged.
For Bug# 785427,if the federal tax record exists then the
state, county and city tax records also exist (due to the
defaulting of tax records). So, delete from all 4 table.
In addition, delete from the table pay_us_asg_reporting as
well.
110.9 24-MAY-1999 HWinsor 896943 Added new columns into do_primary_update.
110.10 02-OCT-2001 vsjain Added new parameters for collective_agreement module
Like notice period, notice_period_uom, work_at_home,
employee category and job source
115.12 26-Nov-2001 HSAJJA Added procedure load_assignment_allocation
115.13 26-Nov-2001 HSAJJA Added dbdrv command
115.14 30-Nov-2001 HSAJJA Added per_dflt_asg_cost_alloc_ff
function used to load dflt asg cost
allocations using Fast Formula
115.17 22-JAN-2002 HSAJJA Changed id_flex_num to to_char(id_flex_num) in
cursor c_cost_allocation_keyflex functions
load_assignment_allocation and
per_dflt_asg_cost_alloc_ff
115.18 13-FEB-02 M Bocutt 1681015 Changed tidy_up_ref_int so that in FUTURE
mode costing records are only opened out
if there are no future dated records
present. This prevents overlapping
records which total over 100%. New OUT
parameter added to pass this back to
caller.
115.19 26-FEB-02 MGettins Added update_primary_cwk as part of
of the contingent labour project.
08-MAR-02 adhunter Overloaded gen_new_ass_number
115.20 25-Jun-2002 HSAJJA Changed proportion decimal pt
from 2 to 4 in
per_dflt_asg_cost_alloc_ff and
load_assignment_allocation
115.22 08-AUG-02 irgonzal 2468916 Modified update_primary procedure.
First call to do_primary_update procedure
converts current primary asg. to a
secondary asg; int his case, the primary flag
procedure to ensure tax records get updated
properly.
115.24 04-OCT-02 adhunter 2537091 modify tidy_up_ref_int to remove future payment meths
and end date the "current" one. Remove paymeth check
from del_ref_int_check
115.25 21-NOV-02 dcasemor 2643203 Changed check_ass_for_primary and
get_new_primary_assignment
to handle contingent workers.
115.26 13-nov-02 raranjan Made nocopy changes.
115.27 07-Nov-02 dcasemor 2468916 Cascaded same change as in 115.22
to update_primary_cwk. This will
go in patch 2643203.
115.28 07-Nov-02 dcasemor 2643203 Changed check_term so that it supports
all date-track operations for CWKs.
115.29 09-Jan-03 dcasemor 2643203 Changed validate_pos so that it
checks for periods of placements in
the case of contingent workers.
115.30 17-Jan-03 dcasemor 2643203 Added 10 missing columns to INSERT
statement in do_primary_update.
115.31 24-Feb-03 MGettins 2806210 Updated tidy_up_ref_int procedure to
end date Assignment Rate records.
115.32 28-Feb-03 dcasemor 2806210 Further changed validate_pos.
115.33 20-Mar-03 skota 2854295 Modified the code that end-dates the
grade step records.
115.34 29-MAy-03 adudekul 2956160 In del_ref_int_check procedure,
excluded X or BEE type pay assignment
actions while checking for future pay
actions for an Assignment.
115.35 27-AUG-03 bsubrama 306713 In check_term added a check for
NEXT_CHANGE / FUTURE_CHANGE
115.36 27-FEB-04 kjagadee 3335915 Modified proc del_ref_int_delete
115.37 09-Mar-04 njaladi 2371510 Modified proc gen_new_ass_number
to restrict the new assignment number
being generated to length of 30.
115.38 05-May-04 njaladi 3584122 Modified update_primary and update_primary_cwk
procedure.Reverted Back the change done in
115.22 and 115.27 to consider
p_new_primary_flag instead of 'N'
115.39 01-Dec-04 jpthomas 4040403 Modified the procedure DEL_REF_INT_DELETE() in
the package HR_ASSIGNMENT to implement the
DELETE_NEXT_CHANGE and FUTURE_CHANGE for the
Assignment Budget Values records.
115.40 27-DEC-04 kramajey 4071460 Modified the check_hours procedure to
to enable the proper validation
if hours is selected as frequency
115.41 16-Feb-05 jpthomas 4186091 Backedout the changes made for the bug 4040403
115.42 13-Jun-05 hsajja Changed cursors c0, c1, c2 in procedure
load_assignment_allocation
115.43 25-Jan-06 bshukla 4946199 Modified DEL_REF_INT_CHECK()
115.44 02-Mar-06 risgupta 4232539 used per_all_assignments_f in place of
per_assignments_f in the function
validate_ass_number of procedure
gen_new_ass_number.
115.47 12-Sep-06 ghshanka 5498344 Modified the procedure gen_new_ass_number.
115.48 26-Oct-06 agolechh 5619940 Modified the procedure gen_probation_end.
115.51 05-nov-07 sidsaxen 6598795 Created update_assgn_context_value and
get_assgn_dff_value new procedures
115.54 05-nov-07 pchowdav 6711256 Modified the procedure gen_new_ass_number.
115.55 02-Jun-08 brsinha 7112709 Modified the procedure del_ref_int_delete.
Added the IF condition for FUTURE delete mode.
115.56 03-Jun-08 brsinha 7112709 Fix the compilation error.
================================================================= */
--
--
-- Package Variables
--
g_package varchar2(33) := ' hr_assignment.';
p_assignment_id - assignment_id or NULL if in insert mode
p_probation_period - probation period, NULL if validating DATE_END
p_probation_unit - probation unit, NULL if validating DATE_END
p_start_date - Validation start date of the assignment
p_date_probation_end - User entered date or NULL when default required
*/
PROCEDURE gen_probation_end
( p_assignment_id IN INTEGER
, p_probation_period IN NUMBER
, p_probation_unit IN VARCHAR2
, p_start_date IN DATE
, p_date_probation_end IN OUT NOCOPY DATE
) IS
-----------------------------------------------------------
-- DECLARE THE LOCAL VARIABLES
-----------------------------------------------------------
check_date NUMBER;
select v_date_probation_end - min(effective_start_date)
into check_date
from per_assignments_f
where assignment_id = p_assignment_id
and assignment_type = 'E';
select nvl(max(assignment_sequence),0) +1
into p_assignment_sequence
from per_assignments_f
where person_id = p_person_id
and assignment_type = p_assignment_type;
select 'Y'
into duplicate
from sys.dual
where exists
( select 'Y'
from per_all_assignments_f
-- from per_assignments_f commented for bug 4232539
where ((p_assignment_id is null)
or
( p_assignment_id is not null
and assignment_id <> p_assignment_id))
and business_group_id + 0 = p_business_group_id
and assignment_type = p_assignment_type
and assignment_number = p_assignment_number);
/* select max(
case
when replace(assignment_number,p_worker_number) is null then 2
else to_number(replace(assignment_number,p_worker_number||'-'))
end
) into ass_seq
from per_all_assignments_f
where person_id = (select distinct person_id
from per_all_people_f
where employee_number = p_worker_number
and business_group_id = p_business_group_id)
and business_group_id + 0 = p_business_group_id;*/
select nvl(max(
case
when replace(assignment_number,p_worker_number) is null then 2
else to_number(replace(assignment_number,p_worker_number||'-'))
end
),2) into ass_seq
from per_all_assignments_f
where person_id = (select distinct person_id
from per_all_people_f
where decode(p_assignment_type,'E',employee_number,'C',npw_number) = p_worker_number --Modified for bug 6711256
and business_group_id = p_business_group_id)
and business_group_id + 0 = p_business_group_id
and instr(assignment_number,p_worker_number||'-',1) > 0;
If an Update Override, Delete Next Change or Future Change Delete
will remove terminated assignments or end dates after
assignment status changes of TERM_ASSIGN then the end date may need
to be fixed to either the Actual Termination Date or the Final
Process Date or the Employees Period of Service. This procedure
determines the requirement and returns an new End Date if one is
required.
PARAMETERS
p_period_of_service_id - Employee's Current Period of Service ID
p_assignment_id - Assignment ID
p_sdate - Start Date of current Assignment row
p_edate - End Date of current Assignment row
p_current_status - The PER_SYSTEM_STATUS of the current row
p_mode - FUTURE_CHANGES, DELETE_NEXT_CHANGE,
UPDATE_OVERRIDE
p_newdate - The New ASsignment End Date
*/
PROCEDURE check_term
(
p_period_of_service_id IN INTEGER
,p_assignment_id IN INTEGER
,p_sdate IN DATE
,p_edate IN DATE
,p_current_status IN VARCHAR2
,p_mode IN VARCHAR2
,p_newdate OUT NOCOPY DATE
) IS
p_atd DATE;
SELECT paaf.person_id
,paaf.assignment_type
,paaf.period_of_placement_date_start
FROM per_all_assignments_f paaf
WHERE paaf.assignment_id = p_assignment_id
AND paaf.assignment_type IN ('E', 'C');
SELECT actual_termination_date
,NVL(final_process_date, hr_api.g_eot)
FROM per_periods_of_service
WHERE period_of_service_id = p_period_of_service_id
UNION
SELECT pdp.actual_termination_date
,NVL(pdp.final_process_date, hr_api.g_eot)
FROM per_periods_of_placement pdp
WHERE pdp.person_id = l_person_id
AND pdp.date_start = l_pdp_date_start;
select max(effective_end_date)
into p_ass_end_date
from per_assignments_f
where assignment_id = p_assignment_id;
select min(a.effective_start_date)
into p_first_term_date
from per_assignments_f a
where a.assignment_id = p_assignment_id
and exists ( select null
from per_assignment_status_types s
where s.assignment_status_type_id
= a.assignment_status_type_id
and s.per_system_status = 'TERM_ASSIGN');
IF p_mode = 'UPDATE_OVERRIDE' AND
p_current_status = 'TERM_ASSIGN' THEN
--
IF p_first_term_date IS NULL OR
(p_first_term_date IS NOT NULL AND p_first_term_date > p_start_date)
THEN
p_first_term_date := p_start_date;
IF (p_mode = 'DELETE_NEXT_CHANGE' or p_mode = 'FUTURE_CHANGE') and p_ass_end_date = p_atd then
hr_utility.set_message(801,'HR_6200_EMP_ASS_TERM_EXISTS');
IF (p_mode = 'UPDATE_OVERRIDE' or p_mode = 'FUTURE_CHANGE')
AND p_start_date < p_atd + 1 THEN
--
hr_utility.set_location('hr_assignment.check_term',6);
select 'Y'
into p_flag
from per_assignments_f a
where a.assignment_id = p_assignment_id
and a.effective_start_date = p_atd + 1
and exists
(select null
from per_assignment_status_types s
where s.assignment_status_type_id
= a.assignment_status_type_id
and s.per_system_status = 'TERM_ASSIGN');
IF p_mode = 'UPDATE_OVERRIDE' THEN
hr_utility.set_location('hr_assignment.check_term',9);
p_mode = 'DELETE_NEXT_CHANGE' THEN
hr_utility.set_location('hr_assignment.check_term',12);
select 'Y'
, a.effective_start_date
, a.effective_end_date
into p_flag
, p_next_eff_start_date
, p_next_eff_end_date
from per_assignments_f a
where a.assignment_id = p_assignment_id
and a.effective_start_date = p_end_date + 1
and exists
(select null
from per_assignment_status_types s
where s.assignment_status_type_id
= a.assignment_status_type_id
and s.per_system_status = 'TERM_ASSIGN');
END IF; -- (p_mode = 'UPDATE_OVERRIDE')
p_mode - FUTURE_CHANGES, DELETE_NEXT_CHANGE,
UPDATE_OVERRIDE
*/
PROCEDURE warn_del_term
(
p_assignment_id IN INTEGER
,p_mode IN VARCHAR2
,p_effective_start_date IN DATE
,p_effective_end_date IN DATE
) IS
--
p_term_found VARCHAR2(1);
select 'Y'
into p_term_found
from sys.dual
where exists
(select null
from per_assignments_f a
, per_assignment_status_types s
where a.assignment_id = p_assignment_id
and a.effective_start_date
> p_effective_start_date
and a.effective_start_date =
decode(p_mode,'DELETE_NEXT_CHANGE',
p_effective_end_date + 1
,a.effective_start_date)
and s.assignment_status_type_id = a.assignment_status_type_id
and s.per_system_status = 'TERM_ASSIGN');
delete_ass_ref_int
DESCRIPTION
Determines whether there are any dependent records for the Assignment.
If any are found then delete them.
The following tables are examined
PER_SPINAL_POINT_PLACEMENTS
PER_SECONDARY_ASS_STATUSES
PER_ASSIGNMENT_BUDGET_VALUES
PARAMETERS
p_business_group_id - Business Group ID
p_assignment_id - Assignment ID
*/
PROCEDURE delete_ass_ref_int
(
p_business_group_id IN INTEGER
,p_assignment_id IN INTEGER
) IS
del_flag VARCHAR2(1);
hr_utility.set_location('hr_assignment.delete_ass_ref_int',1);
SELECT 'Y'
into del_flag
FROM SYS.DUAL
WHERE EXISTS
(SELECT NULL
FROM PER_SPINAL_POINT_PLACEMENTS_F P
WHERE P.business_group_id + 0 = p_business_group_id
AND P.ASSIGNMENT_ID = p_assignment_id);
hr_utility.set_location('hr_assignment.delete_ass_ref_int',2);
DELETE FROM PER_SPINAL_POINT_PLACEMENTS_F P
WHERE P.business_group_id + 0 = p_business_group_id
AND P.ASSIGNMENT_ID = p_assignment_id;
hr_utility.set_location('hr_assignment.delete_ass_ref_int',3);
SELECT 'Y'
into del_flag
from sys.dual
WHERE EXISTS
(SELECT NULL
FROM PER_SECONDARY_ASS_STATUSES S
WHERE S.business_group_id + 0 = p_business_group_id
AND S.ASSIGNMENT_ID = p_assignment_id);
hr_utility.set_location('hr_assignment.delete_ass_ref_int',4);
DELETE FROM PER_SECONDARY_ASS_STATUSES
WHERE business_group_id + 0 = p_business_group_id
AND ASSIGNMENT_ID = p_assignment_id;
hr_utility.set_location('hr_assignment.delete_ass_ref_int',5);
SELECT 'Y'
into del_flag
from sys.dual
WHERE EXISTS
(SELECT NULL
FROM PER_ASSIGNMENT_BUDGET_VALUES_F BV
WHERE BV.business_group_id + 0 = p_business_group_id
AND BV.ASSIGNMENT_ID = p_assignment_id);
hr_utility.set_location('hr_assignment.delete_ass_ref_int',6);
DELETE FROM PER_ASSIGNMENT_BUDGET_VALUES_F BV
WHERE BV.business_group_id + 0 = p_business_group_id
AND BV.ASSIGNMENT_ID = p_assignment_id;
END delete_ass_ref_int;
select actual_termination_date
into p_actual_termination_date
from per_periods_of_service
where period_of_service_id = p_period_of_service_id;
UPDATE_OVERRIDE ==> Validation Start Date
Otherwise ==> Effective Start Date
p_edate - Effective End Date of the current row
p_mode - The DT_UPDATE_MODE or DT_DELETE_MODE
p_primary_flag - The Primary Flag Value for the current assignment
p_change_flag - An indicator to detect whether primary changes are
required.
p_new_primary_flag - The value that the current assignment will have
after the operation
p_primary_date_from- The date from which changes to other assignments
must be catered for
*/
PROCEDURE check_future_primary
(
p_assignment_id IN INTEGER
,p_sdate IN DATE
,p_edate IN DATE
,p_mode IN VARCHAR2
,p_primary_flag IN VARCHAR2
,p_change_flag IN OUT NOCOPY VARCHAR2
,p_new_primary_flag IN OUT NOCOPY VARCHAR2
,p_primary_date_from OUT NOCOPY DATE
) IS
p_start_date DATE;
select 'Y'
into p_change_flag
from sys.dual
where exists
(select null
from per_assignments_f
where assignment_id = p_assignment_id
and primary_flag <> p_new_primary_flag
and effective_start_date >
decode(p_mode,'ZAP',effective_start_date-1,p_start_date)
and effective_start_date =
decode(p_mode,'DELETE_NEXT_CHANGE',p_end_date+1
,effective_start_date));
select min(effective_start_date)
into p_primary_date_from_d
from per_assignments_f
where assignment_id = p_assignment_id
and primary_flag = 'Y';
p_sdate - The validation start date of the updated record
*/
PROCEDURE check_ass_for_primary
(
p_period_of_service_id IN INTEGER
,p_assignment_id IN INTEGER
,p_sdate IN DATE
) IS
p_atd DATE;
SELECT paaf.person_id
,paaf.assignment_type
,paaf.period_of_placement_date_start
FROM per_all_assignments_f paaf
WHERE paaf.assignment_id = p_assignment_id
AND paaf.assignment_type IN ('E', 'C');
SELECT NVL(actual_termination_date, to_date('30/12/4712','DD/MM/YYYY'))
,NVL(final_process_date,to_date('31/12/4712','DD/MM/YYYY'))
FROM per_periods_of_service
WHERE period_of_service_id = p_period_of_service_id
UNION
SELECT NVL(pdp.actual_termination_date,to_date('30/12/4712','DD/MM/YYYY'))
,NVL(pdp.final_process_date,to_date('31/12/4712','DD/MM/YYYY'))
FROM per_periods_of_placement pdp
WHERE pdp.person_id = l_person_id
AND pdp.date_start = l_pdp_date_start;
select max(effective_end_date)
into p_ass_end_date
from per_assignments_f
where assignment_id = p_assignment_id;
select min(a.effective_start_date)
into p_first_term_date
from per_assignments_f a
where a.assignment_id = p_assignment_id
and exists ( select null
from per_assignment_status_types s
where s.assignment_status_type_id
= a.assignment_status_type_id
and s.per_system_status = 'TERM_ASSIGN');
PROCEDURE update_primary_cwk
(p_assignment_id IN INTEGER
,p_person_id IN NUMBER
,p_pop_date_start IN DATE
,p_new_primary_ass_id IN INTEGER
,p_sdate IN DATE
,p_new_primary_flag IN VARCHAR2
,p_mode IN VARCHAR2
,p_last_updated_by IN INTEGER
,p_last_update_login IN INTEGER ) IS
--
l_start_date DATE;
l_proc VARCHAR2(72) := g_package||'update_primary_cwk';
SELECT assignment_id
FROM per_assignments_f
WHERE assignment_id NOT IN (p_assignment_id,p_new_primary_ass_id)
AND person_id = p_person_id
AND period_of_placement_date_start = p_pop_date_start
AND effective_end_date >= l_start_date;
do_primary_update
(p_assignment_id
,p_sdate
,p_new_primary_flag -- Bug 3584122 'N'-- Bug 2468916 p_new_primary_flag
,'Y'
,p_last_updated_by
,p_last_update_login);
do_primary_update
(p_new_primary_ass_id
,p_sdate
,'Y'
,'N'
,p_last_updated_by
,p_last_update_login);
do_primary_update
(ass_rec.assignment_id
,p_sdate
,'N'
,'N'
,p_last_updated_by
,p_last_update_login);
END update_primary_cwk;
update_primary
DESCRIPTION
For the Current Assignment, if the operation is not ZAP then updates
all the future rows to the NEW_PRIMARY_FLAG value.
For other assignments,
if the other assignment is the new primary then ensure that there
is a record starting on the correct date with Primary Flag = 'Y'
and update all other future changes to the same Primary value.
For any other assignments
if the assignment is primary on the date in question then
ensure that that there is a row on this date with primary
flag = 'N' and that all future changes are set to 'N'
otherwise
ensure that all future primary flags are set to 'N'.
NB. This uses several calls to DO_PRIMARY_UPDATE which handles the
date effective insert for an individual assignment row if one
is required.
PARAMETERS
p_assignment_id - The current assignment
p_period_of_service_id - The current Period of Service
p_new_primary_ass_id - The Assignment ID that will be primary after
the operation
p_sdate - The date from which changes are to be made
p_new_primary_flag - The current assignment primary flag after the
operation
p_mode - The DT_DELETE_MODE or DT_UPDATE_MODE
*/
PROCEDURE update_primary
(
p_assignment_id IN INTEGER
,p_period_of_service_id IN INTEGER
,p_new_primary_ass_id IN INTEGER
,p_sdate IN DATE
,p_new_primary_flag IN VARCHAR2
,p_mode IN VARCHAR2
,p_last_updated_by IN INTEGER
,p_last_update_login IN INTEGER
) IS
p_start_date DATE;
select assignment_id
from per_assignments_f
where assignment_id not in (P_ASSIGNMENT_ID,P_NEW_PRIMARY_ASS_ID)
and period_of_service_id = P_PERIOD_OF_SERVICE_ID
and effective_end_date >= P_START_DATE;
hr_utility.set_location('hr_assignment.update_primary',1);
do_primary_update(p_assignment_id
,p_sdate
,p_new_primary_flag -- Bug 3584122 'N'-- Bug 2468916 p_new_primary_flag
,'Y'
,p_last_updated_by
,p_last_update_login
);
hr_utility.set_location('hr_assignment.update_primary',2);
do_primary_update(p_new_primary_ass_id
,p_sdate
,'Y'
,'N'
,p_last_updated_by
,p_last_update_login
);
hr_utility.set_location('hr_assignment.update_primary',3);
do_primary_update(ass_rec.assignment_id
,p_sdate
,'N'
,'N'
,p_last_updated_by
,p_last_update_login
);
END update_primary;
do_primary_update
DESCRIPTION
Performs updates on the Assignment to set the Primary Flag to the value
passed in to the procedure.
If a Primary Flag is to be reset on the Date passed in and a row does
not start on this date then a date effective insert is performed.
PARAMETERS
p_assignment_id - The assignment to be updated
p_sdate - The date from which to update
p_primary_flag - The primary flag value
p_current_ass - Whether the assignment is the current one (Y/N)
*/
PROCEDURE do_primary_update
(
p_assignment_id IN INTEGER
,p_sdate IN DATE
,p_primary_flag IN VARCHAR2
,p_current_ass IN VARCHAR2
,p_last_updated_by IN INTEGER
,p_last_update_login IN INTEGER
) IS
--
x VARCHAR2(30);
CURSOR select_ass_for_update IS
select *
from per_assignments_f
where assignment_id = P_ASSIGNMENT_ID
and ((p_current_ass <> 'Y'
and (P_START_DATE
between effective_start_date
and effective_end_date
or P_START_DATE < effective_start_date))
or (p_current_ass = 'Y'
and P_START_DATE < effective_start_date))
for update;
hr_utility.set_location('hr_assignment.do_primary_update',1);
hr_utility.set_location('hr_assignment.do_primary_update',2);
FOR ass_rec IN select_ass_for_update LOOP
NULL;
hr_utility.set_location('hr_assignment.do_primary_update',3);
update per_assignments_f
set primary_flag = P_PRIMARY_FLAG
, last_updated_by = P_LAST_UPDATED_BY
, last_update_login = P_LAST_UPDATE_LOGIN
, last_update_date = sysdate
where assignment_id = P_ASSIGNMENT_ID
and effective_start_date = P_START_DATE;
hr_utility.set_location('hr_assignment.do_primary_update',4);
hr_utility.set_message_token('PROCEDURE','DO_PRIMARY_UPDATE');
hr_utility.set_location('hr_assignment.do_primary_update',5);
insert into per_assignments_f
(
ASSIGNMENT_ID
,EFFECTIVE_START_DATE
,EFFECTIVE_END_DATE
,BUSINESS_GROUP_ID
,GRADE_ID
,POSITION_ID
,JOB_ID
,ASSIGNMENT_STATUS_TYPE_ID
,PAYROLL_ID
,LOCATION_ID
,PERSON_ID
,ORGANIZATION_ID
,PEOPLE_GROUP_ID
,SOFT_CODING_KEYFLEX_ID
,VACANCY_ID
,ASSIGNMENT_SEQUENCE
,ASSIGNMENT_TYPE
,MANAGER_FLAG
,PRIMARY_FLAG
,APPLICATION_ID
,ASSIGNMENT_NUMBER
,CHANGE_REASON
,COMMENT_ID
,DATE_PROBATION_END
,DEFAULT_CODE_COMB_ID
,FREQUENCY
,INTERNAL_ADDRESS_LINE
,NORMAL_HOURS
,PERIOD_OF_SERVICE_ID
,PROBATION_PERIOD
,PROBATION_UNIT
,RECRUITER_ID
,SET_OF_BOOKS_ID
,SPECIAL_CEILING_STEP_ID
,SUPERVISOR_ID
,TIME_NORMAL_FINISH
,TIME_NORMAL_START
,PERSON_REFERRED_BY_ID
,RECRUITMENT_ACTIVITY_ID
,SOURCE_ORGANIZATION_ID
,SOURCE_TYPE
,PAY_BASIS_ID
,EMPLOYMENT_CATEGORY
,PERF_REVIEW_PERIOD
,PERF_REVIEW_PERIOD_FREQUENCY
,SAL_REVIEW_PERIOD
,SAL_REVIEW_PERIOD_FREQUENCY
,CONTRACT_ID
,CAGR_ID_FLEX_NUM
,CAGR_GRADE_DEF_ID
,ESTABLISHMENT_ID
,COLLECTIVE_AGREEMENT_ID
,NOTICE_PERIOD
,NOTICE_PERIOD_UOM
,WORK_AT_HOME
,EMPLOYEE_CATEGORY
,JOB_POST_SOURCE_NAME
,REQUEST_ID
,PROGRAM_APPLICATION_ID
,PROGRAM_ID
,PROGRAM_UPDATE_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
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_LOGIN
,CREATED_BY
,CREATION_DATE
,BARGAINING_UNIT_CODE
,LABOUR_UNION_MEMBER_FLAG
,HOURLY_SALARIED_CODE
,TITLE
,PERIOD_OF_PLACEMENT_DATE_START
,VENDOR_ID
,VENDOR_EMPLOYEE_NUMBER
,VENDOR_ASSIGNMENT_NUMBER
,ASSIGNMENT_CATEGORY
,PROJECT_TITLE
)
select
ASSIGNMENT_ID
,EFFECTIVE_START_DATE
,p_start_date - 1
,BUSINESS_GROUP_ID
,GRADE_ID
,POSITION_ID
,JOB_ID
,ASSIGNMENT_STATUS_TYPE_ID
,PAYROLL_ID
,LOCATION_ID
,PERSON_ID
,ORGANIZATION_ID
,PEOPLE_GROUP_ID
,SOFT_CODING_KEYFLEX_ID
,VACANCY_ID
,ASSIGNMENT_SEQUENCE
,ASSIGNMENT_TYPE
,MANAGER_FLAG
,PRIMARY_FLAG
,APPLICATION_ID
,ASSIGNMENT_NUMBER
,CHANGE_REASON
,COMMENT_ID
,DATE_PROBATION_END
,DEFAULT_CODE_COMB_ID
,FREQUENCY
,INTERNAL_ADDRESS_LINE
,NORMAL_HOURS
,PERIOD_OF_SERVICE_ID
,PROBATION_PERIOD
,PROBATION_UNIT
,RECRUITER_ID
,SET_OF_BOOKS_ID
,SPECIAL_CEILING_STEP_ID
,SUPERVISOR_ID
,TIME_NORMAL_FINISH
,TIME_NORMAL_START
,PERSON_REFERRED_BY_ID
,RECRUITMENT_ACTIVITY_ID
,SOURCE_ORGANIZATION_ID
,SOURCE_TYPE
,PAY_BASIS_ID
,EMPLOYMENT_CATEGORY
,PERF_REVIEW_PERIOD
,PERF_REVIEW_PERIOD_FREQUENCY
,SAL_REVIEW_PERIOD
,SAL_REVIEW_PERIOD_FREQUENCY
,CONTRACT_ID
,CAGR_ID_FLEX_NUM
,CAGR_GRADE_DEF_ID
,ESTABLISHMENT_ID
,COLLECTIVE_AGREEMENT_ID
,NOTICE_PERIOD
,NOTICE_PERIOD_UOM
,WORK_AT_HOME
,EMPLOYEE_CATEGORY
,JOB_POST_SOURCE_NAME
,REQUEST_ID
,PROGRAM_APPLICATION_ID
,PROGRAM_ID
,PROGRAM_UPDATE_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
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_LOGIN
,CREATED_BY
,CREATION_DATE
,BARGAINING_UNIT_CODE
,LABOUR_UNION_MEMBER_FLAG
,HOURLY_SALARIED_CODE
,TITLE
,PERIOD_OF_PLACEMENT_DATE_START
,VENDOR_ID
,VENDOR_EMPLOYEE_NUMBER
,VENDOR_ASSIGNMENT_NUMBER
,ASSIGNMENT_CATEGORY
,PROJECT_TITLE
from per_assignments_f
where assignment_id = P_ASSIGNMENT_ID
and P_START_DATE
between effective_start_date and effective_end_date
and primary_flag <> P_PRIMARY_FLAG ;
hr_utility.set_location('hr_assignment.do_primary_update',6);
update per_assignments_f
set effective_start_date = P_START_DATE
, primary_flag = P_PRIMARY_FLAG
, last_updated_by = P_LAST_UPDATED_BY
, last_update_login = P_LAST_UPDATE_LOGIN
, last_update_date = sysdate
where assignment_id = P_ASSIGNMENT_ID
and P_START_DATE
between effective_start_date and effective_end_date
and primary_flag <> P_PRIMARY_FLAG;
hr_utility.set_location('hr_assignment.do_primary_update',7);
hr_utility.set_message_token('PROCEDURE','DO_PRIMARY_UPDATE');
hr_utility.set_location('hr_assignment.do_primary_update',8);
update per_assignments_f
set primary_flag = P_PRIMARY_FLAG
, last_updated_by = P_LAST_UPDATED_BY
, last_update_login = P_LAST_UPDATE_LOGIN
, last_update_date = sysdate
where assignment_id = P_ASSIGNMENT_ID
and effective_start_date > P_START_DATE;
END do_primary_update;
SELECT paaf.person_id
,paaf.assignment_type
,paaf.period_of_placement_date_start
FROM per_all_assignments_f paaf
WHERE paaf.assignment_id = p_assignment_id
AND paaf.assignment_type IN ('E', 'C');
SELECT NVL(actual_termination_date, hr_api.g_eot)
,NVL(final_process_date, hr_api.g_eot)
FROM per_periods_of_service
WHERE period_of_service_id = p_period_of_service_id
UNION
SELECT NVL(pdp.actual_termination_date, hr_api.g_eot)
,NVL(pdp.final_process_date, hr_api.g_eot)
FROM per_periods_of_placement pdp
WHERE pdp.person_id = l_person_id
AND pdp.date_start = l_pdp_date_start;
select to_char(a.assignment_id)
from per_assignments_f a,
per_assignment_status_types ast
where assignment_id <> p_assignment_id
and a.effective_start_date <= p_start_date
and a.effective_end_date >= p_start_date
and a.assignment_status_type_id = ast.assignment_status_type_id
and ((a.period_of_service_id = p_period_of_service_id and
a.assignment_type = 'E' and
ast.per_system_status = 'ACTIVE_ASSIGN')
or (a.period_of_placement_date_start = l_pdp_date_start and
a.person_id = l_person_id and
a.assignment_type = 'C' and
ast.per_system_status = 'ACTIVE_CWK'))
and exists
(select null
from per_assignments_f a2
where a2.assignment_id = a.assignment_id
and a2.effective_end_date >= p_fpd)
and not exists
(select null
from per_assignments_f a3
where a3.assignment_id = a.assignment_id
and a3.effective_start_date between p_start_date and p_atd
and exists
(select null
from per_assignment_status_types s
where s.assignment_status_type_id = a3.assignment_status_type_id
and s.per_system_status = 'TERM_ASSIGN'));
insert into per_assignment_budget_values_f
(assignment_budget_value_id
,business_group_id
,assignment_id
,last_update_date
,last_updated_by
,last_update_login
,created_by
,creation_date
,unit
,value
,effective_start_date
,effective_end_date)
select per_assignment_budget_values_s.nextval
, pabv1.business_group_id
, p_assignment_id
, sysdate
, p_userid
, p_login
, p_userid
, sysdate
, pabv1.unit
, pabv1.value
, p_effective_start_date
, p_effective_end_date
from per_default_budget_values pabv1
where pabv1.business_group_id = p_business_group_id
and not exists (select 'already there'
from per_assignment_budget_values_f pabv2
where pabv2.assignment_id = p_assignment_id
and pabv2.unit = pabv1.unit );
For 'END' (date effective delete)
PER_EVENTS
PER_LETTER_REQUEST_LINES
PAY_COST_ALLOCATIONS_F
PAY_PERSONAL_PAYMENT_METHODS_F
PAY_ASSIGNMENT_ACTIONS
PER_COBRA_COV_ENROLLMENTS
PER_COBRA_COVERAGE_BENEFITS_F
Determines whether the delete operation is permissible
PARAMETERS
p_assignment_id - The current assignment
p_mode - The mode of operation (ZAP or END)
p_edate - The date the assignment is ENDed
only required for 'END'
*/
PROCEDURE del_ref_int_check
(
p_assignment_id IN INTEGER
,p_mode IN VARCHAR2
,p_edate IN DATE
) IS
p_end_date DATE;
select 'Y'
into p_del_flag
from sys.dual
where exists (
select null
from PER_ASSIGNMENTS_F A
, FND_SESSIONS S
where a.assignment_id = p_assignment_id
and a.assignment_type = 'E'
and effective_date
between a.effective_start_date and a.effective_end_date
and session_id = userenv('SESSIONID')
and exists
(select null
from PER_ASSIGNMENTS_F B
where b.assignment_id = p_assignment_id
and b.assignment_type = 'A'
and b.effective_end_date < a.effective_start_date)
);
select 'Y'
into p_del_flag
from sys.dual
where exists (
select null
from PER_EVENTS
where assignment_id = p_assignment_id
and (p_mode = 'ZAP'
or (p_mode = 'END'
and date_start > p_end_date)));
/* Took out nocopy the check on letter requests as they are now Auto Deleted
- 2/6/93
*/
/*
hr_utility.set_location('hr_assignment.del_ref_int_check',2);
select 'Y'
into p_del_flag
from sys.dual
where exists (
select null
from PER_LETTER_REQUEST_LINES
where assignment_id = p_assignment_id
and (p_mode = 'ZAP'
or (p_mode = 'END'
and date_from > p_end_date)));
select 'Y'
into p_del_flag
from sys.dual
where exists (
select null
from PAY_COST_ALLOCATIONS_F
where assignment_id = p_assignment_id
and (p_mode = 'ZAP'
or (p_mode = 'END'
and effective_start_date > p_end_date)));
N.B. PER_SECONDARY_ASS_STATUSES rows will now be deleted if they
started after the Assignment End Date - changed 27/5/93.
--
BEGIN
select 'Y'
into p_del_flag
from sys.dual
where exists (
select null
from PER_SECONDARY_ASS_STATUSES
where assignment_id = p_assignment_id
and p_mode = 'ZAP');
/* 2537091: PPMs will be deleted if they started after end date of assignment
changed 04-OCT-2002
--
BEGIN
select 'Y'
into p_del_flag
from sys.dual
where exists (
select null
from PAY_PERSONAL_PAYMENT_METHODS_F
where assignment_id = p_assignment_id
and (p_mode = 'ZAP'
or (p_mode = 'END'
and effective_start_date > p_end_date)));
select 'Y'
into p_del_flag
from sys.dual
where exists
(select null
from pay_payroll_actions ps
, pay_assignment_actions aa
where aa.assignment_id = P_ASSIGNMENT_ID
and ps.payroll_action_id = aa.payroll_action_id
and ps.action_type not in ('X','BEE') --Added for bug2956160
and (P_MODE = 'ZAP'
or (P_MODE = 'END' and
ps.effective_date > P_END_DATE)));
select 'Y'
into p_del_flag
from dual
where exists
(select null
from pay_payroll_actions ps
, pay_assignment_actions aa
where aa.assignment_id = P_ASSIGNMENT_ID
and ps.action_type not in ('X','BEE') --Added for bug2956160
and ps.payroll_action_id = aa.payroll_action_id);
select 'Y'
into p_del_flag
from sys.dual
where exists
(select null
from pay_payroll_actions ps
, pay_assignment_actions aa
where aa.assignment_id = P_ASSIGNMENT_ID
and ps.payroll_action_id = aa.payroll_action_id
and ps.action_type not in ('X','BEE') --Added for bug2956160
and ps.effective_date > P_END_DATE);
select 'Y'
into p_del_flag
from sys.dual
where exists (
select null
from hr_assignment_set_amendments asa
where asa.assignment_id = p_assignment_id
and asa.include_or_exclude = 'I'
and not exists (
select null
from hr_assignment_set_amendments asa2
where asa2.assignment_set_id = asa.assignment_set_id
and asa2.assignment_id <> asa.assignment_id)
);
select 'Y'
into p_del_flag
from sys.dual
where exists (
select null
from PER_COBRA_COV_ENROLLMENTS
where assignment_id = p_assignment_id
and (p_mode = 'ZAP'
or (p_mode = 'END'
and coverage_start_date > p_end_date)));
select 'Y'
into p_del_flag
from dual
where exists
(select null
from per_cobra_cov_enrollments e
, per_cobra_coverage_benefits_f b
where e.assignment_id = P_ASSIGNMENT_ID
and e.cobra_coverage_enrollment_id
= b.cobra_coverage_enrollment_id
and (p_mode = 'ZAP'
or (p_mode = 'END'
and b.effective_end_date > P_END_DATE)));
/* In the select below, added the join to
per_assignments_f to check for the primary assignment. If
it is not a primary assignment then let it get purged.
This has beeen changed for Bug# 787633 */
select 'Y'
into p_del_flag
from sys.dual
where exists (
select null
from PAY_US_EMP_FED_TAX_RULES_F pef,
per_assignments_f paf
where pef.assignment_id = p_assignment_id
and paf.assignment_id = pef.assignment_id
and paf.primary_flag = 'Y'
and (p_mode = 'ZAP'
or (p_mode = 'END'
and pef.effective_start_date > p_end_date)));
del_ref_int_delete
DESCRIPTION
Performs Third Party Delete on data that is not checked in
del_ref_in_check. Removes data from the following tables
For 'ZAP'
HR_ASSIGNMENT_SET_AMENDMENTS
PER_ASSIGNMENT_BUDGET_VALUES_F
PER_SPINAL_POINT_PLACEMENTS_F
PER_PAY_PROPOSALS
For 'END' (performs a date effective delete)
PER_SPINAL_POINT_PLACEMENTS_F
PER_ASSIGNMENT_BUDGET_VALUES_F
For 'FUTURE' (including FUTURE_CHANGES, DELETE_NEXT_CHANGE,
UPDATE_OVERRIDE)
PER_SPINAL_POINT_PLACEMENTS_F
PER_ASSIGNMENT_BUDGET_VALUES_F
PARAMETERS
p_assignment_id - The current assignment
p_grade_id - The current grade ('FUTURE' only')
p_mode - The mode of operation (ZAP, END or FUTURE)
p_edate - For END the date the assignment is ENDed
For FUTURE the date the change applies from
For ZAP not required
p_last_updated_by
p_last_update_login
*/
-- Change to include table per_assignment_budget_values_f in END and FUTURE logic. Now
-- required as this table is datetracked.
-- 16-APR-1998 : SASmith
PROCEDURE del_ref_int_delete
(
p_assignment_id IN INTEGER
,p_grade_id IN INTEGER
,p_mode IN VARCHAR2
,p_edate IN DATE
,p_last_updated_by IN INTEGER
,p_last_update_login IN INTEGER
,p_calling_proc IN VARCHAR2
,p_val_st_date IN DATE
,p_val_end_date IN DATE
,p_datetrack_mode IN VARCHAR2
,p_future_spp_warning OUT NOCOPY BOOLEAN
) IS
p_del_flag VARCHAR2(1);
l_update number;
select spp.placement_id
from per_spinal_point_placements_f spp
where spp.assignment_id = p_assignment_id
and p_val_st_date between spp.effective_start_date
and spp.effective_end_date;
select spp1.placement_id
from per_spinal_point_placements_f spp1
where spp1.assignment_id = p_assignment_id
and spp1.effective_start_date > p_val_st_date;
select spp2.placement_id
from per_spinal_point_placements_f spp2
where spp2.assignment_id = p_assignment_id
and spp2.effective_start_date > p_val_st_date
and spp2.parent_spine_id = l_old_parent_spine_id
and spp2.effective_end_date <= p_val_end_date;
select spp.effective_end_date,
spp.placement_id,
spp.object_version_number
from per_spinal_point_placements_f spp
where spp.assignment_id = p_assignment_id
and p_new_date between spp.effective_start_date
and spp.effective_end_date;
select spp.effective_end_date
from per_spinal_point_placements_f spp
where spp.placement_id = p_placement_id
and l_edate between spp.effective_start_date
and spp.effective_end_date;
hr_utility.set_location('hr_assignment.del_ref_int_delete',1);
SELECT 'Y'
into p_del_flag
FROM SYS.DUAL
WHERE EXISTS
(SELECT NULL
FROM PER_SPINAL_POINT_PLACEMENTS_F P
WHERE P.ASSIGNMENT_ID = p_assignment_id
AND (p_mode = 'ZAP'
OR (p_mode = 'END'
AND EFFECTIVE_END_DATE > p_end_date)
OR (p_mode = 'FUTURE'
AND P.EFFECTIVE_START_DATE >= p_end_date
AND NOT EXISTS
(SELECT NULL
FROM PER_SPINAL_POINT_STEPS_F S
, PER_GRADE_SPINES_F GS
WHERE GS.GRADE_SPINE_ID = S.GRADE_SPINE_ID
AND S.STEP_ID = P.STEP_ID
AND GS.GRADE_ID = NVL(p_grade_id,-1)))));
select min(effective_start_date)
into l_min_start_date
from per_spinal_point_placements_f
where assignment_id = p_assignment_id;
and p_datetrack_mode = 'DELETE_NEXT_CHANGE' then
hr_assignment_internal.delete_first_spp
(p_effective_date => p_edate,
p_assignment_id => p_assignment_id,
p_validation_start_date => p_val_st_date,
p_validation_end_date => p_val_end_date,
p_future_spp_warning => l_future_spp_warning
);
hr_utility.set_location('hr_assignment.delete_ass_ref_int',2);
fetch csr_grade_step into l_update;
hr_sp_placement_api.delete_spp
(p_effective_date => l_min_start_date
,p_datetrack_mode => l_datetrack_mode
,p_placement_id => l_placement_id
,p_object_version_number => l_object_version_number
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date);
/* DELETE FROM PER_SPINAL_POINT_PLACEMENTS_F P
WHERE P.ASSIGNMENT_ID = p_assignment_id;
fetch csr_grade_step into l_update;
if p_calling_proc = 'POST_UPDATE' and
p_datetrack_mode <> hr_api.g_update_override then -- Bug 3335915
--
-- get the placement_id and object_version_number for
-- the current record as of the effective date
--
select spp.placement_id,spp.object_version_number,spp.effective_start_date,spp.parent_spine_id
into l_placement_id,l_object_version_number,l_date_temp,l_old_parent_spine_id
from per_spinal_point_placements_f spp
where spp.assignment_id = p_assignment_id
and p_val_st_date between spp.effective_start_date
and spp.effective_end_date;
select pgs.parent_spine_id
into l_parent_spine_id
from per_grade_spines_f pgs
where pgs.grade_id = p_grade_id
and P_edate between pgs.effective_start_date
and pgs.effective_end_date;
select min(psp.sequence)
into l_sequence
from per_spinal_points psp,
per_spinal_point_steps_f sps
where psp.parent_spine_id = l_parent_spine_id
and psp.spinal_point_id = sps.spinal_point_id
and P_edate between sps.effective_start_date
and sps.effective_end_date;
select sps.step_id
into l_min_step_id
from per_spinal_point_steps_f sps,
per_spinal_points psp
where sps.spinal_point_id = psp.spinal_point_id
and psp.parent_spine_id = l_parent_spine_id
and psp.sequence = l_sequence;
l_datetrack_mode := 'UPDATE_OVERRIDE';
l_datetrack_mode := 'UPDATE';
hr_sp_placement_api.update_spp
(p_effective_date => p_edate
,p_datetrack_mode => l_datetrack_mode
,p_placement_id => l_placement_id
,p_object_version_number => l_object_version_number
,p_step_id => l_min_step_id
,p_auto_increment_flag => 'N'
,p_reason => ''
,p_increment_number => NULL
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date);
hr_utility.set_location('Deleteing the next change.',15);
/*if p_datetrack_mode <> 'UPDATE_OVERRIDE' then
l_new_date := p_val_st_date;
l_datetrack_mode := 'DELETE_NEXT_CHANGE';
hr_sp_placement_api.delete_spp(
p_effective_date => p_edate
,p_datetrack_mode => l_datetrack_mode
,p_placement_id => l_placement_id
,p_object_version_number => l_object_version_number
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date);
hr_utility.set_location('hr_assignment.delete_ass_ref_int',3);
DELETE FROM PER_SPINAL_POINT_PLACEMENTS_F P
WHERE P.ASSIGNMENT_ID = p_assignment_id
AND P.EFFECTIVE_START_DATE >= p_end_date
AND NOT EXISTS
(SELECT NULL
FROM PER_SPINAL_POINT_STEPS_F S
, PER_GRADE_SPINES_F GS
WHERE GS.GRADE_SPINE_ID = S.GRADE_SPINE_ID
AND S.STEP_ID = P.STEP_ID
AND GS.GRADE_ID = NVL(p_grade_id,-1));
hr_utility.set_location('hr_assignment.delete_ass_ref_int',4);
DELETE FROM PER_SPINAL_POINT_PLACEMENTS_F P
WHERE P.ASSIGNMENT_ID = p_assignment_id
AND P.EFFECTIVE_START_DATE > p_end_date;
fetch csr_grade_step into l_update;
select spp.placement_id,spp.object_version_number,spp.effective_start_date
into l_placement_id,l_object_version_number,l_date_temp
from per_spinal_point_placements_f spp
where spp.assignment_id = p_assignment_id
and p_end_date between spp.effective_start_date
and spp.effective_end_date;
l_datetrack_mode := 'DELETE';
hr_sp_placement_api.delete_spp
(p_effective_date => P_edate
,p_datetrack_mode => l_datetrack_mode
,p_placement_id => l_placement_id
,p_object_version_number => l_object_version_number
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date);
/*hr_utility.set_location('hr_assignment.delete_ass_ref_int',5);
UPDATE PER_SPINAL_POINT_PLACEMENTS_F
SET EFFECTIVE_END_DATE = p_end_date
, LAST_UPDATED_BY = p_last_updated_by
, LAST_UPDATE_LOGIN = p_last_update_login
, LAST_UPDATE_DATE = sysdate
WHERE ASSIGNMENT_ID = p_assignment_id
AND p_end_date
BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE;
SELECT 'Y'
into p_del_flag
FROM SYS.DUAL
WHERE EXISTS
(SELECT NULL
FROM PER_ASSIGNMENT_BUDGET_VALUES_F ABV
WHERE ABV.ASSIGNMENT_ID = p_assignment_id
AND (p_mode = 'ZAP'
OR (p_mode = 'END'
AND ABV.EFFECTIVE_END_DATE > p_end_date)
OR (p_mode = 'FUTURE'
--AND ABV.EFFECTIVE_START_DATE >= p_end_date))); -- this condition will never satisfy, as when end dating,
DELETE FROM PER_ASSIGNMENT_BUDGET_VALUES_F ABV
WHERE ABV.ASSIGNMENT_ID = p_assignment_id;
hr_utility.set_location('hr_assignment.del_ref_int_delete',40);
DELETE FROM PER_ASSIGNMENT_BUDGET_VALUES_F ABV
WHERE ABV.ASSIGNMENT_ID = p_assignment_id
AND ABV.EFFECTIVE_START_DATE > p_end_date;
hr_utility.set_location('hr_assignment.del_ref_int_delete',45);
UPDATE PER_ASSIGNMENT_BUDGET_VALUES_F
SET EFFECTIVE_END_DATE = p_end_date
, LAST_UPDATED_BY = p_last_updated_by
, LAST_UPDATE_LOGIN = p_last_update_login
, LAST_UPDATE_DATE = sysdate
WHERE ASSIGNMENT_ID = p_assignment_id
AND p_end_date
BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE;
hr_utility.set_location('hr_assignment.del_ref_int_delete',46);
DELETE FROM PER_ASSIGNMENT_BUDGET_VALUES_F ABV
WHERE ABV.ASSIGNMENT_ID = p_assignment_id
AND ABV.EFFECTIVE_START_DATE > p_end_date;
hr_utility.set_location(' No of rows deleted '||sql%rowcount,48);
hr_utility.set_location('hr_assignment.del_ref_int_delete',49);
select effective_end_date into l_ass_end_date
from per_all_assignments_f
where assignment_id = p_assignment_id
and p_end_date between EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE;
UPDATE PER_ASSIGNMENT_BUDGET_VALUES_F
SET EFFECTIVE_END_DATE = l_ass_end_date
, LAST_UPDATED_BY = p_last_updated_by
, LAST_UPDATE_LOGIN = p_last_update_login
, LAST_UPDATE_DATE = sysdate
WHERE ASSIGNMENT_ID = p_assignment_id
AND p_end_date
BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE;
hr_utility.set_location('No of rows updated '||sql%rowcount,51);
hr_utility.set_location('hr_assignment.del_ref_int_delete',5);
SELECT 'Y'
into p_del_flag
FROM SYS.DUAL
WHERE EXISTS
(SELECT NULL
FROM HR_ASSIGNMENT_SET_AMENDMENTS
WHERE ASSIGNMENT_ID = p_assignment_id);
hr_utility.set_location('hr_assignment.delete_ass_ref_int',6);
DELETE FROM HR_ASSIGNMENT_SET_AMENDMENTS
WHERE ASSIGNMENT_ID = p_assignment_id;
hr_utility.set_location('hr_assignment.del_ref_int_delete',9);
SELECT 'Y'
into p_del_flag
FROM SYS.DUAL
WHERE EXISTS
(SELECT NULL
FROM PER_SECONDARY_ASS_STATUSES
WHERE ASSIGNMENT_ID = p_assignment_id);
hr_utility.set_location('hr_assignment.delete_ass_ref_int',10);
DELETE FROM PER_SECONDARY_ASS_STATUSES
WHERE ASSIGNMENT_ID = p_assignment_id;
hr_utility.set_location('hr_assignment.del_ref_int_delete',11);
SELECT 'Y'
into p_del_flag
FROM SYS.DUAL
WHERE EXISTS
(SELECT NULL
FROM PER_PAY_PROPOSALS
WHERE ASSIGNMENT_ID = p_assignment_id);
hr_utility.set_location('hr_assignment.delete_ass_ref_int',12);
DELETE FROM PER_PAY_PROPOSALS
WHERE ASSIGNMENT_ID = p_assignment_id;
hr_utility.set_location('hr_assignment.del_ref_int_delete',11);
select 'Y'
into p_del_flag
from sys.dual
where exists (
select null
from PAY_US_EMP_FED_TAX_RULES_F pef
where pef.assignment_id = p_assignment_id);
hr_utility.set_location('hr_assignment.delete_ass_ref_int',12);
tax records). So, delete from all 4 table. In addition, delete
from the table pay_us_asg_reporting as well */
DELETE FROM PAY_US_ASG_REPORTING
WHERE ASSIGNMENT_ID = p_assignment_id;
DELETE FROM PAY_US_EMP_CITY_TAX_RULES_F
WHERE ASSIGNMENT_ID = p_assignment_id;
DELETE FROM PAY_US_EMP_COUNTY_TAX_RULES_F
WHERE ASSIGNMENT_ID = p_assignment_id;
DELETE FROM PAY_US_EMP_STATE_TAX_RULES_F
WHERE ASSIGNMENT_ID = p_assignment_id;
DELETE FROM PAY_US_EMP_FED_TAX_RULES_F
WHERE ASSIGNMENT_ID = p_assignment_id;
hr_utility.set_location('hr_assignment.del_ref_int_delete',14);
SELECT 'Y'
into p_del_flag
FROM SYS.DUAL
WHERE EXISTS
(SELECT NULL
FROM PER_ASSIGNMENT_EXTRA_INFO
WHERE ASSIGNMENT_ID = p_assignment_id);
hr_utility.set_location('hr_assignment.delete_ass_ref_int',16);
DELETE FROM PER_ASSIGNMENT_EXTRA_INFO
WHERE ASSIGNMENT_ID = p_assignment_id;
DELETE FROM PAY_US_EMP_CITY_TAX_RULES_F
WHERE ASSIGNMENT_ID = p_assignment_id
AND EFFECTIVE_START_DATE > p_end_date;
UPDATE PAY_US_EMP_CITY_TAX_RULES_F
SET EFFECTIVE_END_DATE = p_end_date
, LAST_UPDATED_BY = p_last_updated_by
, LAST_UPDATE_LOGIN = p_last_update_login
, LAST_UPDATE_DATE = sysdate
WHERE ASSIGNMENT_ID = p_assignment_id
AND p_end_date BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE;
DELETE FROM PAY_US_EMP_COUNTY_TAX_RULES_F
WHERE ASSIGNMENT_ID = p_assignment_id
AND EFFECTIVE_START_DATE > p_end_date;
UPDATE PAY_US_EMP_COUNTY_TAX_RULES_F
SET EFFECTIVE_END_DATE = p_end_date
, LAST_UPDATED_BY = p_last_updated_by
, LAST_UPDATE_LOGIN = p_last_update_login
, LAST_UPDATE_DATE = sysdate
WHERE ASSIGNMENT_ID = p_assignment_id
AND p_end_date BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE;
DELETE FROM PAY_US_EMP_STATE_TAX_RULES_F
WHERE ASSIGNMENT_ID = p_assignment_id
AND EFFECTIVE_START_DATE > p_end_date;
UPDATE PAY_US_EMP_STATE_TAX_RULES_F
SET EFFECTIVE_END_DATE = p_end_date
, LAST_UPDATED_BY = p_last_updated_by
, LAST_UPDATE_LOGIN = p_last_update_login
, LAST_UPDATE_DATE = sysdate
WHERE ASSIGNMENT_ID = p_assignment_id
AND p_end_date BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE;
DELETE FROM PAY_US_EMP_FED_TAX_RULES_F
WHERE ASSIGNMENT_ID = p_assignment_id
AND EFFECTIVE_START_DATE > p_end_date;
UPDATE PAY_US_EMP_FED_TAX_RULES_F
SET EFFECTIVE_END_DATE = p_end_date
, LAST_UPDATED_BY = p_last_updated_by
, LAST_UPDATE_LOGIN = p_last_update_login
, LAST_UPDATE_DATE = sysdate
WHERE ASSIGNMENT_ID = p_assignment_id
AND p_end_date BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE;
END del_ref_int_delete;
This is the case when a FUTURE_CHANGE of DELETE_NEXT_CHANGE is going
to open the assignment out nocopy beyond its current End Date. The procedure
resets the End Dates of any child rows to be that on the Assignment. In
the case of Costing records dates are only changed if there are not
future records.
The following tables are affected.
PAY_COST_ALLOCATIONS_F
PER_SECONDARY_ASS_STATUSES
PAY_PERSONAL_PAYMENT_METHODS_F
PER_ASSIGNMENT_BUDGET_VALUES_F
PARAMETERS
p_assignment_id - Assignment ID
p_mode - 'END' or 'FUTURE'
p_new_end_date - The new end date of the parent Assignment
p_old_end_date - The Assignment End Date before the operation
p_last_updated_by
p_last_update_login
p_cost_warning - Pass back warning if future costing records
exist. Can only set to TRUE if mode is
FUTURE.
*/
--
PROCEDURE tidy_up_ref_int
(p_assignment_id IN INTEGER
,p_mode IN VARCHAR2
,p_new_end_date IN DATE
,p_old_end_date DATE
,p_last_updated_by INTEGER
,p_last_update_login INTEGER
,p_cost_warning OUT NOCOPY BOOLEAN) IS
--
p_del_flag VARCHAR2(1) := 'N';
SELECT pgr.grade_rule_id,
pgr.object_version_number
FROM pay_grade_rules_f pgr
WHERE pgr.grade_or_spinal_point_id = p_assignment_id
AND pgr.rate_type = 'A'
AND p_new_end_date BETWEEN pgr.effective_start_date
AND pgr.effective_end_date
AND p_mode='END';
SELECT pgr.grade_rule_id,
pgr.object_version_number,
pgr.effective_start_date
FROM pay_grade_rules_f pgr
WHERE pgr.grade_or_spinal_point_id = p_assignment_id
AND pgr.rate_type = 'A'
AND p_new_end_date < pgr.effective_start_date
AND p_mode='END';
select 'Y'
into p_del_flag
from sys.dual
where exists (
select null
from per_secondary_ass_statuses
where assignment_id = p_assignment_id
and ((p_mode = 'END'
and p_new_end_date
between START_DATE and nvl(END_DATE,
to_date('31/12/4712','DD/MM/YYYY')))
or
(p_mode = 'FUTURE'
and p_old_end_date = nvl(END_DATE,
to_date('31/12/4712','DD/MM/YYYY')))));
update per_secondary_ass_statuses
set END_DATE = decode(p_new_end_date,to_date('31/12/4712','DD/MM/YYYY'),
null,p_new_end_date)
, last_updated_by = P_LAST_UPDATED_BY
, last_update_login = P_LAST_UPDATE_LOGIN
, last_update_date = sysdate
where assignment_id = p_assignment_id
and ((p_mode = 'END'
and p_new_end_date
between START_DATE and nvl(END_DATE,
to_date('31/12/4712','DD/MM/YYYY')))
or
(p_mode = 'FUTURE'
and p_old_end_date = nvl(END_DATE,
to_date('31/12/4712','DD/MM/YYYY'))));
select 'Y'
into p_del_flag
from sys.dual
where exists (
select null
from per_secondary_ass_statuses
where assignment_id = p_assignment_id
and p_mode = 'END'
and p_new_end_date < START_DATE);
delete from per_secondary_ass_statuses
where assignment_id = p_assignment_id
and p_mode = 'END'
and p_new_end_date < START_DATE;
select 'Y'
into p_del_flag
from sys.dual
where exists (
select null
from pay_cost_allocations_f
where assignment_id = p_assignment_id
and ((p_mode = 'END'
and p_new_end_date
between effective_start_date and effective_end_date)
or
(p_mode = 'FUTURE'
and p_old_end_date = effective_end_date)));
update pay_cost_allocations_f
set effective_end_date = p_new_end_date
, last_updated_by = P_LAST_UPDATED_BY
, last_update_login = P_LAST_UPDATE_LOGIN
, last_update_date = sysdate
where assignment_id = p_assignment_id
and ((p_mode = 'END'
and p_new_end_date
between effective_start_date and effective_end_date));
** When dealing with delete FUTURE_CHANGE, only open out
** the costing record if no future costing record exists.
** If they do leaving the costing alone and display
** message to the user informing them of the situation.
*/
select count(*)
into l_exists
from pay_cost_allocations_f
where assignment_id = p_assignment_id
and effective_start_date > p_old_end_date;
update pay_cost_allocations_f
set effective_end_date = p_new_end_date
, last_updated_by = P_LAST_UPDATED_BY
, last_update_login = P_LAST_UPDATE_LOGIN
, last_update_date = sysdate
where assignment_id = p_assignment_id
and (p_mode = 'FUTURE'
and p_old_end_date = effective_end_date);
select 'Y'
into p_del_flag
from sys.dual
where exists (
select null
from per_assignment_budget_values_f abv
where abv.assignment_id = p_assignment_id
and p_mode = 'END'
and p_new_end_date < abv.effective_start_date);
delete from per_assignment_budget_values_f abv
where abv.assignment_id = p_assignment_id
and p_mode = 'END'
and p_new_end_date < abv.effective_start_date;
select 'Y'
into p_del_flag
from sys.dual
where exists (
select null
from per_assignment_budget_values_f abv
where abv.assignment_id = p_assignment_id
and p_mode = 'END'
and p_new_end_date between abv.effective_start_date and abv.effective_end_date);
update per_assignment_budget_values_f abv
set abv.effective_end_date = p_new_end_date
, abv.last_updated_by = P_LAST_UPDATED_BY
, abv.last_update_login = P_LAST_UPDATE_LOGIN
, abv.last_update_date = sysdate
where abv.assignment_id = p_assignment_id
and p_mode = 'END'
and p_new_end_date between abv.effective_start_date and abv.effective_end_date;
select max(effective_end_date)
from pay_us_emp_fed_tax_rules_f
where assignment_id = p_assignment_id;
pay_us_update_tax_rec_pkg.reverse_term_emp_tax_records(p_assignment_id, l_end_date);
select ppm.personal_payment_method_id,ppm.object_version_number
from pay_personal_payment_methods_f ppm
where ppm.assignment_id = p_assignment_id
and p_new_end_date between ppm.effective_start_date and ppm.effective_end_date
and p_mode='END';
select ppm.personal_payment_method_id,ppm.object_version_number,ppm.effective_start_date
from pay_personal_payment_methods_f ppm
where ppm.assignment_id = p_assignment_id
and p_new_end_date < ppm.effective_start_date
and p_mode='END';
hr_personal_pay_method_api.delete_personal_pay_method
(p_effective_date => p_new_end_date
,p_datetrack_delete_mode => 'DELETE'
,p_personal_payment_method_id => l_curr_rec.personal_payment_method_id
,p_object_version_number => l_curr_rec.object_version_number
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date
);
hr_personal_pay_method_api.delete_personal_pay_method
(p_effective_date => l_fut_rec.effective_start_date
,p_datetrack_delete_mode => 'ZAP'
,p_personal_payment_method_id => l_fut_rec.personal_payment_method_id
,p_object_version_number => l_fut_rec.object_version_number
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date
);
hr_rate_values_api.delete_rate_value
(p_validate => FALSE
,p_grade_rule_id => crec_current_asg_rates.grade_rule_id
,p_datetrack_mode => hr_api.g_delete
,p_effective_date => p_new_end_date
,p_object_version_number => crec_current_asg_rates.object_version_number
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date);
hr_rate_values_api.delete_rate_value
(p_validate => FALSE
,p_grade_rule_id => crec_future_asg_rates.grade_rule_id
,p_datetrack_mode => hr_api.g_zap
,p_effective_date => crec_future_asg_rates.effective_start_date
,p_object_version_number => crec_future_asg_rates.object_version_number
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date);
select 'Y'
into term_exists
from sys.dual
where exists
(select null
from per_assignments_f a
, per_assignment_status_types s
where a.assignment_id = p_assignment_id
and a.effective_start_date < p_start_date
and a.assignment_status_type_id = s.assignment_status_type_id
and s.per_system_status = 'TERM_ASSIGN');
select tp.end_date
into p_last_standard_date
from per_assignments_f a
, per_time_periods tp
where a.assignment_id = p_assignment_id
and a.effective_end_date = p_start_date - 1
and a.payroll_id = tp.payroll_id
and p_start_date - 1 between tp.start_date and tp.end_date;
select min(a.effective_start_date)
into l_leading_date
from per_assignments_f a
, per_assignment_status_types s
where a.assignment_id = p_assignment_id
and (l_ignore_val_start_date = 'N' or
(l_ignore_val_start_date = 'Y' and
effective_start_date <> p_validation_start_date ))
and a.assignment_status_type_id = s.assignment_status_type_id
and s.per_system_status = 'TERM_ASSIGN';
select tp.end_date
into l_new_lspd
from per_assignments_f a
, per_time_periods tp
where a.assignment_id = p_assignment_id
and a.effective_end_date = l_default_lspd
and a.payroll_id = tp.payroll_id
and l_default_lspd between tp.start_date and tp.end_date;
if the mode is CORRECTION,UPDATE,UPDATE-OVERRIDE or UPDATE_INSERT
AND
if the current assignment status type is TERM_ASSIGN and the old
assignment status type is not TERM_ASSIGN i.e. this operation is actually
altering the assignment status type
AND
there is a leading TERM_ASSIGN out nocopy of all the TERM_ASSIGNS other
than the one currently being created
then if this leading TERM_ASSIGN is after the validation start date
of the current modification then cancellation of entries
will be required
ELSE
if the mode is UPDATE-OVERRIDE
AND
the assignment status is not being changed by this operation
AND
there is a leading TERM_ASSIGN other out nocopy of all the TERM_ASSIGNS
then if this leading TERM_ASSIGN is after the validation start
date of the current operation then cancellation of entries
will be required
ELSE
if the mode is DELETE-NEXT-CHANGE or FUTURE-CHANGES-DELETE
AND
there is a leading TERM_ASSIGN other out nocopy of all the TERM_ASSIGNS
then if this leading TERM_ASSIGN is on or after the validation start
date of the current operation then cancellation of entries
will be required
-----------------------------------------------------------------*/
--
hr_utility.set_location('hr_assignment.test_for_cancel_reterm',1);
,'UPDATE'
,'UPDATE_OVERRIDE'
,'UPDATE_CHANGE_INSERT' )
and p_current_status_type = 'TERM_ASSIGN'
and p_current_status_type <> p_old_status_type
and leading_term_assign('Y') then
--
if l_leading_date > p_validation_start_date then
cancel_required;
p_mode = 'UPDATE_OVERRIDE'
and p_current_status_type <> 'TERM_ASSIGN'
and leading_term_assign('N') then
--
if l_leading_date > p_validation_start_date then
cancel_required;
p_mode in ('DELETE_NEXT_CHANGE'
,'FUTURE_CHANGE')
and leading_term_assign('N') then
--
if l_leading_date between p_validation_start_date
and p_validation_end_date then
cancel_required;
If the operation is a DELETE_NEXT_CHANGE that will remove the
leading TERM_ASSIGN and leave a subsequent TERM_ASSIGN as the
new leading one then the re-termination process is required
as though the new leaading TERM_ASSIGN were being created for
the first time
N.B. This is only necessary if the leading TERM_ASSIGN has been
removed (i.e. if all the conditions for CANCEL_REQUIRED to
be called are met)
-----------------------------------------------------------------*/
--
if p_mode = 'DELETE_NEXT_CHANGE' then
begin
select min(a.effective_start_date)
into l_new_leading_date
from per_assignments_f a
, per_assignment_status_types s
where a.assignment_id = p_assignment_id
and effective_start_date > p_validation_end_date
and a.assignment_status_type_id = s.assignment_status_type_id
and s.per_system_status = 'TERM_ASSIGN';
select 'Y'
into l_cobra_term_exists
from sys.dual
where exists
(select null
from per_cobra_cov_enrollments e
where e.assignment_id = p_assignment_id
and exists
(select null
from per_assignments_f a
where a.assignment_id = p_assignment_id
and a.effective_start_date between p_sdate and p_edate
and exists ( select null
from per_assignment_status_types s
where s.assignment_status_type_id
= a.assignment_status_type_id
and s.per_system_status = 'TERM_ASSIGN')
and a.effective_start_date + 1 = e.qualifying_date));
SELECT p.date_start date_start, p.period_of_service_id
FROM per_periods_of_service p
WHERE p.person_id = to_number(p_person_id)
AND fnd_date.canonical_to_date(p_new_date) BETWEEN
p.date_start AND NVL(p.final_process_date, hr_api.g_eot)
UNION
SELECT pdp.date_start date_start, pdp.period_of_placement_id
FROM per_periods_of_placement pdp
WHERE pdp.person_id = to_number(p_person_id)
AND fnd_date.canonical_to_date(p_new_date) BETWEEN
pdp.date_start AND NVL(pdp.final_process_date, hr_api.g_eot)
ORDER BY date_start DESC;
select ff.formula_id
from ff_formulas_f ff where upper(ff.formula_name) = p_formula_name
and business_group_id = p_business_group_id
and p_effective_date between effective_start_date and effective_end_date;
select effective_date
from fnd_sessions
where session_id = userenv('sessionid');
select *
from pay_cost_allocation_keyflex
where cost_allocation_keyflex_id = p_cost_allocation_keyflex_id
and to_char(id_flex_num) in (select cost_allocation_structure
from per_business_groups
where business_group_id = p_business_group_id);
insert into fnd_sessions (SESSION_ID, EFFECTIVE_DATE) values(userenv('sessionid'), trunc(p_effective_date));
select 'x'
from pay_cost_allocations_f
where assignment_id = p_assignment_id;
select *
from pay_cost_allocation_keyflex
where cost_allocation_keyflex_id = p_cost_allocation_keyflex_id
and to_char(id_flex_num) in (select cost_allocation_structure
from per_business_groups
where business_group_id = p_business_group_id);
select *
from (
select
hr_psf_shd.system_availability_status(budget_unit1_id) unit1,
hr_psf_shd.system_availability_status(budget_unit2_id) unit2,
hr_psf_shd.system_availability_status(budget_unit3_id) unit3
from pqh_budgets
where p_effective_date between budget_start_date and budget_end_date
and position_control_flag = 'Y'
and budgeted_entity_cd = 'POSITION'
and business_group_id = p_business_group_id
)
where unit1 = 'MONEY' or unit2 = 'MONEY' or unit3 = 'MONEY';
select src.cost_allocation_keyflex_id,
sum((decode(unit1,l_money,nvl(bset.budget_unit1_value,0),0)
+ decode(unit2,l_money,nvl(bset.budget_unit2_value,0),0)
+ decode(unit3,l_money,nvl(bset.budget_unit3_value,0),0))
* (ele.distribution_percentage * src.distribution_percentage/10000)) proportion
from pqh_budget_fund_srcs src, pqh_budget_elements ele, pqh_budget_sets bset,
pqh_budget_periods per, pqh_budget_details det, per_time_periods stp, per_time_periods etp
where
det.position_id = p_position_id and
det.budget_detail_id= per.budget_detail_id
and per.budget_period_id = bset.budget_period_id
and per.start_time_period_id = stp.time_period_id
and per.end_time_period_id = etp.time_period_id
and p_effective_date
between stp.start_date and etp.end_date
and bset.budget_set_id = ele.budget_set_id
and ele.budget_element_id = src.budget_element_id
and src.cost_allocation_keyflex_id is not null
and det.budget_version_id in
(select budget_version_id
from pqh_budget_versions ver, pqh_budgets bgt
where ver.budget_id = bgt.budget_id
and bgt.position_control_flag = 'Y'
and bgt.budgeted_entity_cd = 'POSITION'
and bgt.business_group_id = p_business_group_id
and p_effective_date
between ver.date_from and ver.date_to
and p_effective_date
between bgt.budget_start_date and bgt.budget_end_date
and (hr_psf_shd.system_availability_status(budget_unit1_id) = 'MONEY'
or hr_psf_shd.system_availability_status(budget_unit2_id) = 'MONEY'
or hr_psf_shd.system_availability_status(budget_unit3_id) = 'MONEY')
)
group by src.cost_allocation_keyflex_id;
select
decode(unit1,l_money,nvl(per.budget_unit1_value,0),0)+
decode(unit2,l_money,nvl(per.budget_unit2_value,0),0)+
decode(unit3,l_money,nvl(per.budget_unit3_value,0),0) period_value
from pqh_budget_periods per, pqh_budget_details det,
per_time_periods stp, per_time_periods etp
where
det.position_id = p_position_id and
det.budget_detail_id= per.budget_detail_id
and per.start_time_period_id = stp.time_period_id
and per.end_time_period_id = etp.time_period_id
and p_effective_date
between stp.start_date and etp.end_date
and det.budget_version_id in
(select budget_version_id
from pqh_budget_versions ver, pqh_budgets bgt
where ver.budget_id = bgt.budget_id
and bgt.position_control_flag = 'Y'
and bgt.budgeted_entity_cd = 'POSITION'
and bgt.business_group_id = p_business_group_id
and p_effective_date
between ver.date_from and ver.date_to
and p_effective_date
between bgt.budget_start_date and bgt.budget_end_date
and (hr_psf_shd.system_availability_status(budget_unit1_id) = 'MONEY'
or hr_psf_shd.system_availability_status(budget_unit2_id) = 'MONEY'
or hr_psf_shd.system_availability_status(budget_unit3_id) = 'MONEY')
);
PROCEDURE update_assgn_context_value(
p_business_group_id IN number
,p_person_id IN number
,p_assignment_id IN number
,p_effective_start_date IN date)
IS
l_context_val varchar2(150);
l_proc_name varchar2(100):='update_assgn_context_val :';
select nvl(DEFAULT_CONTEXT_FIELD_NAME,'-100')
from FND_DESCRIPTIVE_FLEXS_VL
where DESCRIPTIVE_FLEXFIELD_NAME='PER_ASSIGNMENTS';
select ass_attribute_category
from per_all_assignments_f
where business_group_id = p_business_group_id
and person_id = p_person_id
and assignment_id = p_assignment_id
and effective_start_date = p_effective_start_date
and primary_flag = 'Y';
hr_utility.set_location(l_proc_name||' selecting records ',30);
select g_rec.'||l_context_val||' into :2 from dual;
update per_all_assignments_f
set ass_attribute_category = l_output_context_val
where business_group_id = p_business_group_id
and person_id = p_person_id
and assignment_id = p_assignment_id
and effective_start_date = p_effective_start_date;
hr_utility.set_location('dynamic sql updated '||sql%rowcount||' records',50);
END update_assgn_context_value;
SELECT PA.ASSIGNMENT_ID ,
'11111111111' as row_id ,
PA.EFFECTIVE_START_DATE ,
DECODE(PA.EFFECTIVE_END_DATE , TO_DATE('4712/12/31', 'YYYY/MM/DD'), to_date(NULL), PA.EFFECTIVE_END_DATE) D_EFFECTIVE_END_DATE ,
PA.EFFECTIVE_END_DATE ,
PA.BUSINESS_GROUP_ID + 0 BUSINESS_GROUP_ID ,
PA.GRADE_ID ,
GDT.NAME GRADE_NAME ,
PA.POSITION_ID ,
HR_GENERAL.DECODE_POSITION_LATEST_NAME(PA.POSITION_ID) POSITION_NAME ,
PA.JOB_ID ,
JBT.NAME JOB_NAME ,
PA.ASSIGNMENT_STATUS_TYPE_ID ,
NVL(AMDTL.USER_STATUS, STTL.USER_STATUS) USER_STATUS ,
NVL(AMD.PER_SYSTEM_STATUS, ST.PER_SYSTEM_STATUS) PER_SYSTEM_STATUS ,
PA.PAYROLL_ID ,
PAY.PAYROLL_NAME ,
PA.LOCATION_ID ,
LOCTL.LOCATION_CODE ,
PA.SUPERVISOR_ID ,
SUP.FULL_NAME SUPERVISOR_NAME ,
NVL(SUP.EMPLOYEE_NUMBER, SUP.NPW_NUMBER) SUPERVISOR_EMPLOYEE_NUMBER ,
PA.SPECIAL_CEILING_STEP_ID ,
PSP.SPINAL_POINT ,
PSPS.SEQUENCE SPINAL_POINT_STEP_SEQUENCE ,
PA.PERSON_ID ,
PA.ORGANIZATION_ID ,
OTL.NAME ORGANIZATION_NAME ,
PA.PEOPLE_GROUP_ID ,
PA.ASSIGNMENT_SEQUENCE ,
PA.PRIMARY_FLAG ,
PA.ASSIGNMENT_NUMBER ,
PA.CHANGE_REASON ,
DECODE(PA.ASSIGNMENT_TYPE ,'E', HR_GENERAL.DECODE_LOOKUP('EMP_ASSIGN_REASON', PA.CHANGE_REASON) ,'C', HR_GENERAL.DECODE_LOOKUP('CWK_ASSIGN_REASON', PA.CHANGE_REASON)) CHANGE_REASON_MEANING ,
PA.COMMENT_ID ,
COM.COMMENT_TEXT ,
PA.DATE_PROBATION_END ,
PA.DATE_PROBATION_END D_DATE_PROBATION_END ,
PA.FREQUENCY ,
HR_GENERAL.DECODE_LOOKUP('FREQUENCY', PA.FREQUENCY) FREQUENCY_MEANING ,
PA.INTERNAL_ADDRESS_LINE ,
PA.MANAGER_FLAG ,
PA.NORMAL_HOURS ,
PA.PROBATION_PERIOD ,
PA.PROBATION_UNIT ,
HR_GENERAL.DECODE_LOOKUP('QUALIFYING_UNITS', PA.PROBATION_UNIT) PROBATION_UNIT_MEANING ,
PA.TIME_NORMAL_FINISH ,
PA.TIME_NORMAL_START ,
PA.BARGAINING_UNIT_CODE ,
HR_GENERAL.DECODE_LOOKUP('BARGAINING_UNIT_CODE', PA.BARGAINING_UNIT_CODE) BARGAINING_UNIT_CODE_MEANING ,
PA.LABOUR_UNION_MEMBER_FLAG ,
PA.HOURLY_SALARIED_CODE ,
HR_GENERAL.DECODE_LOOKUP('HOURLY_SALARIED_CODE', PA.HOURLY_SALARIED_CODE) ,
PA.LAST_UPDATE_DATE ,
PA.LAST_UPDATED_BY ,
PA.LAST_UPDATE_LOGIN ,
PA.CREATED_BY ,
PA.CREATION_DATE ,
PA.SAL_REVIEW_PERIOD ,
HR_GENERAL.DECODE_LOOKUP('FREQUENCY', PA.SAL_REVIEW_PERIOD_FREQUENCY) SAL_REV_PERIOD_FREQ_MEANING ,
PA.SAL_REVIEW_PERIOD_FREQUENCY ,
PA.PERF_REVIEW_PERIOD ,
HR_GENERAL.DECODE_LOOKUP('FREQUENCY', PA.PERF_REVIEW_PERIOD_FREQUENCY) PERF_REV_PERIOD_FREQ_MEANING ,
PA.PERF_REVIEW_PERIOD_FREQUENCY ,
PA.PAY_BASIS_ID ,
PB.NAME SALARY_BASIS ,
PB.PAY_BASIS PAY_BASIS ,
PA.RECRUITER_ID ,
PA.PERSON_REFERRED_BY_ID ,
PA.RECRUITMENT_ACTIVITY_ID ,
PA.SOURCE_ORGANIZATION_ID ,
PA.SOFT_CODING_KEYFLEX_ID ,
PA.VACANCY_ID ,
PA.ASSIGNMENT_TYPE ,
PA.APPLICATION_ID ,
PA.DEFAULT_CODE_COMB_ID ,
PA.PERIOD_OF_SERVICE_ID ,
PA.SET_OF_BOOKS_ID ,
GL.NAME D_SET_OF_BOOKS ,
GL.CHART_OF_ACCOUNTS_ID GL_KEYFLEX_STRUCTURE ,
PA.SOURCE_TYPE ,
PA.REQUEST_ID ,
PA.PROGRAM_APPLICATION_ID ,
PA.PROGRAM_ID ,
PA.PROGRAM_UPDATE_DATE ,
PA.ASS_ATTRIBUTE_CATEGORY ,
PA.ASS_ATTRIBUTE1 ,
PA.ASS_ATTRIBUTE2 ,
PA.ASS_ATTRIBUTE3 ,
PA.ASS_ATTRIBUTE4 ,
PA.ASS_ATTRIBUTE5 ,
PA.ASS_ATTRIBUTE6 ,
PA.ASS_ATTRIBUTE7 ,
PA.ASS_ATTRIBUTE8 ,
PA.ASS_ATTRIBUTE9 ,
PA.ASS_ATTRIBUTE10 ,
PA.ASS_ATTRIBUTE11 ,
PA.ASS_ATTRIBUTE12 ,
PA.ASS_ATTRIBUTE13 ,
PA.ASS_ATTRIBUTE14 ,
PA.ASS_ATTRIBUTE15 ,
PA.ASS_ATTRIBUTE16 ,
PA.ASS_ATTRIBUTE17 ,
PA.ASS_ATTRIBUTE18 ,
PA.ASS_ATTRIBUTE19 ,
PA.ASS_ATTRIBUTE20 ,
PA.ASS_ATTRIBUTE21 ,
PA.ASS_ATTRIBUTE22 ,
PA.ASS_ATTRIBUTE23 ,
PA.ASS_ATTRIBUTE24 ,
PA.ASS_ATTRIBUTE25 ,
PA.ASS_ATTRIBUTE26 ,
PA.ASS_ATTRIBUTE27 ,
PA.ASS_ATTRIBUTE28 ,
PA.ASS_ATTRIBUTE29 ,
PA.ASS_ATTRIBUTE30 ,
PA.EMPLOYMENT_CATEGORY ,
DECODE(PA.ASSIGNMENT_TYPE, 'E', HR_GENERAL.DECODE_LOOKUP('EMP_CAT', PA.EMPLOYMENT_CATEGORY) ,'C', HR_GENERAL.DECODE_LOOKUP('CWK_ASG_CATEGORY', PA.EMPLOYMENT_CATEGORY)) EMPLOYMENT_CATEGORY_MEANING ,
PA.ESTABLISHMENT_ID ,
PA.COLLECTIVE_AGREEMENT_ID ,
PA.CONTRACT_ID ,
PA.CAGR_GRADE_DEF_ID ,
PA.CAGR_ID_FLEX_NUM ,
CA.NAME AGREEMENT_NAME ,
O1.NAME ESTABLISHMENT_NAME ,
CO.REFERENCE REFERENCE ,
PA.NOTICE_PERIOD ,
PA.NOTICE_PERIOD_UOM ,
HR_GENERAL.DECODE_LOOKUP('QUALIFYING_UNITS', PA.NOTICE_PERIOD_UOM) NOTICE_PERIOD_UOM_MEANING ,
PA.EMPLOYEE_CATEGORY ,
HR_GENERAL.DECODE_LOOKUP('EMPLOYEE_CATG', PA. EMPLOYEE_CATEGORY) EMPLOYEE_CATEGORY_MEANING ,
PA.WORK_AT_HOME ,
PA.JOB_POST_SOURCE_NAME ,
PA.TITLE ,
PA.PROJECT_TITLE ,
PA.PERIOD_OF_PLACEMENT_DATE_START ,
PA.VENDOR_ID ,
POV.VENDOR_NAME ,
PA.VENDOR_SITE_ID ,
POVS.VENDOR_SITE_CODE ,
PA.PO_HEADER_ID ,
POH.SEGMENT1 PO_NUMBER ,
PA.PO_LINE_ID ,
POL.LINE_NUM PO_LINE_NUMBER ,
PA.PROJECTED_ASSIGNMENT_END ,
PA.VENDOR_EMPLOYEE_NUMBER ,
PA.VENDOR_ASSIGNMENT_NUMBER ,
PA.ASSIGNMENT_CATEGORY ,
PA.GRADE_LADDER_PGM_ID ,
PA.SUPERVISOR_ASSIGNMENT_ID ,
PGM.NAME GRADE_LADDER_NAME ,
PA2.ASSIGNMENT_NUMBER SUPERVISOR_ASSIGNMENT_NUMBER
FROM PER_ALL_ASSIGNMENTS_F PA ,
PER_ALL_ASSIGNMENTS_F PA2 ,
PER_GRADES PG ,
PER_JOBS J ,
PER_GRADES_TL GDT ,
PER_JOBS_TL JBT ,
PER_ASSIGNMENT_STATUS_TYPES ST ,
PER_ASSIGNMENT_STATUS_TYPES_TL STTL ,
PER_ASS_STATUS_TYPE_AMENDS AMD ,
PER_ASS_STATUS_TYPE_AMENDS_TL AMDTL ,
PAY_ALL_PAYROLLS_F PAY ,
HR_LOCATIONS_ALL_TL LOCTL ,
HR_LOCATIONS_NO_JOIN LOC ,
PER_ALL_PEOPLE_F SUP ,
PER_SPINAL_POINT_STEPS_F PSPS ,
PER_SPINAL_POINTS PSP ,
HR_ALL_ORGANIZATION_UNITS O ,
HR_ALL_ORGANIZATION_UNITS_TL OTL ,
HR_COMMENTS COM ,
GL_SETS_OF_BOOKS GL ,
PER_PAY_BASES PB ,
FND_SESSIONS FND ,
PER_COLLECTIVE_AGREEMENTS CA ,
PER_CONTRACTS_F CO ,
HR_ALL_ORGANIZATION_UNITS O1 ,
BEN_PGM_F PGM ,
PO_VENDORS POV ,
PO_VENDOR_SITES_ALL POVS ,
PO_HEADERS_ALL POH ,
PO_LINES_ALL POL
WHERE PA.ASSIGNMENT_TYPE IN ( 'E','C')
AND PA.ORGANIZATION_ID = O.ORGANIZATION_ID
AND PA.GRADE_ID = PG.GRADE_ID (+)
AND PA.GRADE_ID =GDT.GRADE_ID (+)
AND GDT.LANGUAGE(+) = userenv('LANG')
AND PA.JOB_ID = JBT.JOB_ID (+)
AND JBT.LANGUAGE(+) = userenv('LANG')
AND PA.JOB_ID = J.JOB_ID (+)
AND PA.ASSIGNMENT_STATUS_TYPE_ID = ST.ASSIGNMENT_STATUS_TYPE_ID
AND PA.ASSIGNMENT_STATUS_TYPE_ID = AMD.ASSIGNMENT_STATUS_TYPE_ID (+)
AND PA.BUSINESS_GROUP_ID + 0 = AMD.BUSINESS_GROUP_ID (+) + 0
AND PA.PAYROLL_ID = PAY.PAYROLL_ID (+)
AND PA.LOCATION_ID = LOC.LOCATION_ID (+)
AND PA.SUPERVISOR_ID = SUP.PERSON_ID (+)
AND PA.SPECIAL_CEILING_STEP_ID = PSPS.STEP_ID (+)
AND PSPS.SPINAL_POINT_ID = PSP.SPINAL_POINT_ID (+)
AND PA.SET_OF_BOOKS_ID = GL.SET_OF_BOOKS_ID (+)
AND PA.COMMENT_ID = COM.COMMENT_ID (+)
AND PA.PAY_BASIS_ID = PB.PAY_BASIS_ID (+)
AND (
( PA2.EFFECTIVE_START_DATE IS NULL
AND PA2.EFFECTIVE_END_DATE IS NULL
)
OR (PA2.EFFECTIVE_START_DATE IS NOT NULL
AND PA2.EFFECTIVE_END_DATE IS NOT NULL
AND PA2.EFFECTIVE_END_DATE =
(SELECT MAX(PA3.EFFECTIVE_END_DATE)
FROM PER_ALL_ASSIGNMENTS_F PA3
WHERE PA3.ASSIGNMENT_ID = PA.SUPERVISOR_ASSIGNMENT_ID
)
)
)
AND O.organization_id = OTL.organization_id
AND OTL.language = userenv('LANG')
AND ST.assignment_status_type_id = STTL.assignment_status_type_id
AND STTL.language = userenv('LANG')
AND AMD.ass_status_type_amend_id = AMDTL.ass_status_type_amend_id (+)
AND DECODE(amdtl.ass_status_type_amend_id,NULL,'1',AMDTL.language) = DECODE(amdtl.ass_status_type_amend_id,NULL,'1',userenv('LANG'))
AND LOC.location_id = LOCTL.location_id (+)
AND DECODE(LOCTL.location_id,NULL,'1',loctl.language) = DECODE(LOCTL.location_id,NULL,'1',userenv('LANG'))
AND PA.ESTABLISHMENT_ID = O1.ORGANIZATION_ID (+)
AND CA.COLLECTIVE_AGREEMENT_ID (+) = PA.COLLECTIVE_AGREEMENT_ID
AND CO.CONTRACT_ID (+) = PA.CONTRACT_ID
AND PA.GRADE_LADDER_PGM_ID = PGM.PGM_ID (+)
AND PA.VENDOR_ID = POV.VENDOR_ID (+)
AND PA.VENDOR_SITE_ID = POVS.VENDOR_SITE_ID (+)
AND PA.PO_HEADER_ID = POH.PO_HEADER_ID (+)
AND PA.PO_LINE_ID = POL.PO_LINE_ID (+)
AND PA2.ASSIGNMENT_ID(+) = PA.SUPERVISOR_ASSIGNMENT_ID
AND PA.BUSINESS_GROUP_ID = P_BUSINESS_GROUP_ID
AND PA.PERSON_ID = P_PERSON_ID
AND PA.ASSIGNMENT_ID = P_ASSIGNMENT_ID
AND PA.EFFECTIVE_START_DATE = P_EFFECTIVE_START_DATE;