1 PACKAGE BODY hr_assignment AS
2 /* $Header: peassign.pkb 120.21.12020000.2 2012/07/05 01:30:58 amnaraya ship $ */
3 /*
4 ******************************************************************
5 * *
6 * Copyright (C) 1992 Oracle Corporation UK Ltd., *
7 * Chertsey, England. *
8 * *
9 * All rights reserved. *
10 * *
11 * This material has been provided pursuant to an agreement *
12 * containing restrictions on its use. The material is also *
13 * protected by copyright law. No part of this material may *
14 * be copied or distributed, transmitted or transcribed, in *
15 * any form or by any means, electronic, mechanical, magnetic, *
16 * manual, or otherwise, or disclosed to third parties without *
17 * the express written permission of Oracle Corporation UK Ltd, *
18 * Oracle Park, Bittams Lane, Guildford Road, Chertsey, Surrey, *
19 * England. *
20 * *
21 ****************************************************************** */
22 /*
23 Name : hr_assignment (BODY)
24
25 Description : This package defines procedures required to
26 INSERT, UPDATE and DELETE assignments and all
27 associated tables :
28
29 PER_ASSIGNMENTS_F
30 PER_SECONDARY_ASSIGNMENT_STATUSES
31 PER_ASSIGNMENT_BUDGET_VALUES_F
32
33
34
35 Change List
36 -----------
37
38 Version Date Author ER/CR No. Description of Change
39 -------+---------+----------+---------+--------------------------
40 70.0 19-NOV-92 SZWILLIA Date Created
41 70.8 30-DEC-92 SZWILLIA Added error locations.
42 70.9 08-FEB-93 JHOBBS Changed secondary_asg.. to
43 secondary_ass...
44 70.10 11-FEB-93 JRHODES When searching for an assignment
45 TERM_ASSIGN row that has been brought
46 about by the Employee Termination
47 the date to look for should be
48 ACTUAL_TERMINATION_DATE + 1 (the day
49 after actual termination date).
50 70.11 15-FEB-93 JRHODES In check_term setting of END_DATE
51 for DELETE_NEXT_CHANGE was incorrect.
52 It now only resets the End DATE to the
53 ATD when the current en date is after
54 the ATD.
55 70.12 16-FEB-93 JHOBBS Added maintain_alu_asg procedure for
56 maintaining alus for the assignment.
57 70.13 03-MAR-93 JHOBBS Removed maintain_alu_asg. It is now in
58 hrentmnt.
59 70.14 10-MAR-93 JRHODES Included extra Set_location calls.
60 All dates are passed in as type DATE.
61 Select for Update when date effectively
62 updating the Assignment.
63 Added CURSOR to select for
64 update Assignment rows in
65 DO_PRIMARY_UPDATE
66 70.23 11-MAR-93 NKHAN Added 'exit' to end of code
67 70.24 25-MAR-93 JRHODES Added procedure 'tidy_up_ref_int'
68 70.25 27-MAY-93 JRHODES PER_SECONDARY_ASS_STATUSES are now
69 removed when they start after an
70 assignment end date.
71 70.26 02-JUN-93 JRHODES PER_SECONDARY_ASS_STATUSES are removed
72 totally on ZAP.
73 The ref int check on LETTER REQUESTS
74 is no longer required - they are
75 Auto Shut Down.
76 70.27 07-JUN-93 JRHODES New Procedure 'call_terminate_entries'
77 70.31 11-OCT-93 JRHODES Added extra columns to
78 Insert statement in do_primary_update
79 Bug No 240
80 70.32 12-OCT-93 JRHODES Fixed referential integrity checking
81 omission when assignment
82 is ENDED by check_term.
83 80.3 15-OCT-93 JRHODES Added check_for_cobra
84 80.4 04-NOV-93 PBARRY Added pay_proposals check for
85 del_ref_int_delete.
86 80.5 09-DEC-93 JRhodes New Procedure 'test_for_cancel_reterm'
87 80.6 03-Feb-93 JRhodes Bug 370
88 70.32 16-JUN-94 PShergill Fixed 220466 added ASS_ATTRIBUTE21..30
89 70.33 04-Jun-94 JRhodes Added Validate_Pos
90 70.34 26-AUG-94 JRhodes WWBUG# 232359 -
91 added check to del_ref_int_check
92 to ensure that Employee ASG cannot be
93 removed if they have an earlier
94 Applicant ASG
95 70.39 28-OCT-94 RFine Amended load_budget_values to prevent
96 one from being loaded if it already
97 exists.
98 70.40 23-NOV-94 RFine Suppressed index on business_group_id
99 70.41 02-MAR-95 JRhodes Added order by to select of
100 periods of service - fix to 265262
101 70.42 25-MAY-95 JRhodes 273820
102 Fixed insert into budget values
103 statement to make better use of
104 default_budget_values view
105 70.43 21-JUL-95 AForte Changed tokenised messages to
106 AMills hard coded messages.
107 From HR_6401_ASS_DEL_ASS (tokenised)
108 To
109 HR_7625_ASS_DEL_APP_ASS
110 HR_ 7630_ASS_EVE_DEL_ASS
111 HR_7633_ASS_EVE_END_ASS
112 HR_7634_ASS_LET_DEL_ASS
113 HR_7637_ASS_EVE_END_ASS
114 HR_7638_ASS_COST_DEL_ASS
115 HR_7641_ASS_COST_END_ASS
116 HR_7642_ASS_INF_DEL_ASS
117 HR_7652_ASS_STAT_DEL_ASS
118 HR_7655_ASS_SATA_END_ASS
119 HR_7656_ASS_PAY_DEL_ASS
120 HR_7659_ASS_PAY_END_ASS
121 HR_7664_ASS_ASS_DEL_ASS
122 HR_7667_ASS_ASS_END_ASS
123 HR_7668_ASS_COBR_DEL_ASS
124 HR_7671_ASS_COBR_END_ASS
125 HR_7672_ASS_COBRA_DEL_ASS
126 HR_7675_ASS_COBRA_END_ASS
127 70.44 12-AUG-94 RFine 306211 In the procedure check_ass_for_primary,
128 NVL null ATD to EOT - 1 instead of EOT.
129 This is because the code adds 1 to the
130 value at certain points, and trying to
131 add 1 to EOT raises an ORA-1841 error.
132 So use Dec 30 instead of Dec 31.
133 70.45 02-JUL-96 SXShah Added call to ota_predel_asg_validation
134 for OTA to perform referential integrity
135 checks.
136 70.46 17-Oct-96 VTreiger 306710 Changed call to terminate_entries_and_
137 alus.
138 70.49 17-Jan-97 JAlloun 424224 Amended cursor get_candidate_primary_ass, so the
139 effective_start_date is between the session date
140 and the actual termination date.
141 Also the per_system_status = ACTIVE_ASSIGN for
142 the particular assignment.
143
144 70.50 18-Mar-98 fychu 642566 1) Removed code in del_ref_int_check
145 procedure. APP-07642 error message
146 is no longer issued if there is
147 per_assignment_extra_info exists on
148 a delete.
149 2) Added code to del_ref_int_delete
150 procedure to remove
151 per_assignment_extra_info records
152 when an assignment is deleted.
153 110.4 16-APR-1998 SASmith Due to date tracking of the per_assignment_budget_values_f
154 table the following changes have been made.
155 1.load_budget_values parameters changed to include
156 effective start and end dates and also changed to ensure these
157 are added when the row is inserted into the db.
158 2.Procedure del_ref_int_delete. remove the current zap of
159 per_assignment_budget_values_f and include a delete,zap
160 and future logic instead. Also change to the '_F' table.
161 3.delete_ass_ref_int - change to reference the '_F' per_assignment_
162 budget_values_f.
163 4.tidy_up_ref_int - new logic to handle change in assignment
164 effective end date to ensure this cascades to the assignment_
165 budget_values.
166 NOTE: As part of these changes no change has been made to del_ref
167 _int_check. The reason for pointing this out nocopy is potentially a change could
168 have been included so that any forward dated changes would not
169 allow the user to delete the row. It was decided that as per_assignment_
170 budget_values is essentially an attribute of assignment then deletes
171 should be cascaded.
172 110.6 23-JUN-1998 mshah 682452 Corrected typo in call to message numbers 7630 and 7633: '...7630)...'
173 was changed to '...7630...'.
174
175 110.7 22-DEC-1998 bgoodsel 679966 Removed restriction in not exists... sub-query that causes
176 duplicate rows in some circumstances
177 110.8 07-MAY-1999 achauhan For Bug# 787633, in del_ref_int_check, added the join to
178 per_assignments_f to check for the primary assignment. If
179 it is not a primary assignment then let it get purged.
180 For Bug# 785427,if the federal tax record exists then the
181 state, county and city tax records also exist (due to the
182 defaulting of tax records). So, delete from all 4 table.
183 In addition, delete from the table pay_us_asg_reporting as
184 well.
185 110.9 24-MAY-1999 HWinsor 896943 Added new columns into do_primary_update.
186 110.10 02-OCT-2001 vsjain Added new parameters for collective_agreement module
187 Like notice period, notice_period_uom, work_at_home,
188 employee category and job source
189 115.12 26-Nov-2001 HSAJJA Added procedure load_assignment_allocation
190 115.13 26-Nov-2001 HSAJJA Added dbdrv command
191 115.14 30-Nov-2001 HSAJJA Added per_dflt_asg_cost_alloc_ff
192 function used to load dflt asg cost
193 allocations using Fast Formula
194 115.17 22-JAN-2002 HSAJJA Changed id_flex_num to to_char(id_flex_num) in
195 cursor c_cost_allocation_keyflex functions
196 load_assignment_allocation and
197 per_dflt_asg_cost_alloc_ff
198 115.18 13-FEB-02 M Bocutt 1681015 Changed tidy_up_ref_int so that in FUTURE
199 mode costing records are only opened out
200 if there are no future dated records
201 present. This prevents overlapping
202 records which total over 100%. New OUT
203 parameter added to pass this back to
204 caller.
205 115.19 26-FEB-02 MGettins Added update_primary_cwk as part of
206 of the contingent labour project.
207 08-MAR-02 adhunter Overloaded gen_new_ass_number
208 115.20 25-Jun-2002 HSAJJA Changed proportion decimal pt
209 from 2 to 4 in
210 per_dflt_asg_cost_alloc_ff and
211 load_assignment_allocation
212 115.22 08-AUG-02 irgonzal 2468916 Modified update_primary procedure.
213 First call to do_primary_update procedure
214 converts current primary asg. to a
215 secondary asg; int his case, the primary flag
216 has to be 'N'. Second call will convert
217 the new asg. into a primary asignment.
218 The p_new_primary_flag parameter is being
219 ignored.
220 115.23 15-AUG-02 irgonzal 2437795 Modified tidy_up_ref_int procedure and
221 added call to reverse_term_emp_tax_records
222 procedure to ensure tax records get updated
223 properly.
224 115.24 04-OCT-02 adhunter 2537091 modify tidy_up_ref_int to remove future payment meths
225 and end date the "current" one. Remove paymeth check
226 from del_ref_int_check
227 115.25 21-NOV-02 dcasemor 2643203 Changed check_ass_for_primary and
228 get_new_primary_assignment
229 to handle contingent workers.
230 115.26 13-nov-02 raranjan Made nocopy changes.
231 115.27 07-Nov-02 dcasemor 2468916 Cascaded same change as in 115.22
232 to update_primary_cwk. This will
233 go in patch 2643203.
234 115.28 07-Nov-02 dcasemor 2643203 Changed check_term so that it supports
235 all date-track operations for CWKs.
236 115.29 09-Jan-03 dcasemor 2643203 Changed validate_pos so that it
237 checks for periods of placements in
238 the case of contingent workers.
239 115.30 17-Jan-03 dcasemor 2643203 Added 10 missing columns to INSERT
240 statement in do_primary_update.
241 115.31 24-Feb-03 MGettins 2806210 Updated tidy_up_ref_int procedure to
242 end date Assignment Rate records.
243 115.32 28-Feb-03 dcasemor 2806210 Further changed validate_pos.
244 115.33 20-Mar-03 skota 2854295 Modified the code that end-dates the
245 grade step records.
246 115.34 29-MAy-03 adudekul 2956160 In del_ref_int_check procedure,
247 excluded X or BEE type pay assignment
248 actions while checking for future pay
249 actions for an Assignment.
250 115.35 27-AUG-03 bsubrama 306713 In check_term added a check for
251 NEXT_CHANGE / FUTURE_CHANGE
252 115.36 27-FEB-04 kjagadee 3335915 Modified proc del_ref_int_delete
253 115.37 09-Mar-04 njaladi 2371510 Modified proc gen_new_ass_number
254 to restrict the new assignment number
255 being generated to length of 30.
256 115.38 05-May-04 njaladi 3584122 Modified update_primary and update_primary_cwk
257 procedure.Reverted Back the change done in
258 115.22 and 115.27 to consider
259 p_new_primary_flag instead of 'N'
260 115.39 01-Dec-04 jpthomas 4040403 Modified the procedure DEL_REF_INT_DELETE() in
261 the package HR_ASSIGNMENT to implement the
262 DELETE_NEXT_CHANGE and FUTURE_CHANGE for the
263 Assignment Budget Values records.
264 115.40 27-DEC-04 kramajey 4071460 Modified the check_hours procedure to
265 to enable the proper validation
266 if hours is selected as frequency
267 115.41 16-Feb-05 jpthomas 4186091 Backedout the changes made for the bug 4040403
268 115.42 13-Jun-05 hsajja Changed cursors c0, c1, c2 in procedure
269 load_assignment_allocation
270 115.43 25-Jan-06 bshukla 4946199 Modified DEL_REF_INT_CHECK()
271 115.44 02-Mar-06 risgupta 4232539 used per_all_assignments_f in place of
272 per_assignments_f in the function
273 validate_ass_number of procedure
274 gen_new_ass_number.
275 115.47 12-Sep-06 ghshanka 5498344 Modified the procedure gen_new_ass_number.
276 115.48 26-Oct-06 agolechh 5619940 Modified the procedure gen_probation_end.
277 115.51 05-nov-07 sidsaxen 6598795 Created update_assgn_context_value and
278 get_assgn_dff_value new procedures
279 115.54 05-nov-07 pchowdav 6711256 Modified the procedure gen_new_ass_number.
280 115.55 02-Jun-08 brsinha 7112709 Modified the procedure del_ref_int_delete.
281 Added the IF condition for FUTURE delete mode.
282 115.56 03-Jun-08 brsinha 7112709 Fix the compilation error.
283 115.57 27-oct-08 sidsaxen 7503993 Modified query in gen_new_ass_number
284 to improve performance.
285 115.58 20-may-08 sathkris 8252045 Modified the cursor csr_dff_context
286 in the procedure update_assgn_context_value
287 115.59 31-AUG-09 ktithy 8710298 Removed hr_utility.set_location calls.
288 115.60 17-DEC-10 vepravee 9669561 Modified the update of
289 PER_ASSIGNMENT_BUDGET_VALUES_F in the
290 procedure del_ref_int_delete.
291 115.61 19-JUL-11 vepravee 11078262 Modified the delete of
292 PER_ASSIGNMENT_BUDGET_VALUES_F in the
293 procedure del_ref_int_delete.
294 115.62 10-OCT-11 srannama 12820636 Modified procedure do_primary_update
295 for Bug 12820636.
296 ================================================================= */
297 --
298 --
299 -- Package Variables
300 --
301 g_package varchar2(33) := ' hr_assignment.';
302 --
303 ----------------------- gen_probation_end ----------------------------
304 /*
305 NAME
306 gen_probation_end
307 DESCRIPTION
308
309 PARAMETERS
310 p_assignment_id - assignment_id or NULL if in insert mode
311 p_probation_period - probation period, NULL if validating DATE_END
312 p_probation_unit - probation unit, NULL if validating DATE_END
313 p_start_date - Validation start date of the assignment
314 p_date_probation_end - User entered date or NULL when default required
315 */
316 PROCEDURE gen_probation_end
317 ( p_assignment_id IN INTEGER
318 , p_probation_period IN NUMBER
319 , p_probation_unit IN VARCHAR2
320 , p_start_date IN DATE
321 , p_date_probation_end IN OUT NOCOPY DATE
322 ) IS
323 -----------------------------------------------------------
324 -- DECLARE THE LOCAL VARIABLES
325 -----------------------------------------------------------
326 check_date NUMBER;
327 v_start_date DATE;
328 v_date_probation_end DATE;
329 --
330 BEGIN
331 --
332 hr_utility.set_location('hr_assignment.gen_probation_end',1);
333 --
334 v_start_date := p_start_date;
335 v_date_probation_end := p_date_probation_end;
336 --
337 IF v_date_probation_end IS NULL THEN
338 --
339 -- generate new default probation end date
340 --
341 hr_utility.set_location('hr_assignment.gen_probation_end',2);
342 --
343 /*------ changes made for bug 5619940 ---- */
344 IF p_probation_period = 0
345 and ( p_probation_unit = 'D'
346 or p_probation_unit = 'W'
347 or p_probation_unit = 'M'
348 or p_probation_unit = 'Y'
349 ) THEN
350 v_date_probation_end := v_start_date ;
351 /*------ changes end for bug 5619940 ---- */
352 ELSIF p_probation_unit = 'D' THEN
353 v_date_probation_end := v_start_date + p_probation_period -1;
354 ELSIF
355 p_probation_unit = 'W' THEN
356 v_date_probation_end := v_start_date + (p_probation_period * 7) -1;
357 ELSIF
358 p_probation_unit = 'M' THEN
359 v_date_probation_end := ADD_MONTHS(v_start_date
360 ,p_probation_period) -1;
361 ELSIF
362 p_probation_unit = 'Y' THEN
363 v_date_probation_end :=ADD_MONTHS(v_start_date
364 ,12*p_probation_period) -1;
365 END IF;
366
367
368 --
369 --
370 ELSIF
371 p_assignment_id IS NULL THEN
372 --
373 hr_utility.set_location('hr_assignment.gen_probation_end',3);
374 --
375 -- If the Assignment is a new one
376 -- ensure that the DATE_PROBATION_END is on or after the assignment
377 -- start date
378 --
379 IF v_date_probation_end < v_start_date THEN
380 hr_utility.set_message(801,'HR_6150_EMP_ASS_PROB_END');
381 hr_utility.raise_error;
382 END IF;
383 --
384 ELSE
385 --
386 -- If checking the validity of the DATE_PROBATION_END when the
387 -- assignment already exists
388 -- ensure that DATE_PROBATION_END is on or after the earliest effective
389 -- date for the assignment.
390 --
391 BEGIN
392 --
393 hr_utility.set_location('hr_assignment.gen_probation_end',4);
394 select v_date_probation_end - min(effective_start_date)
395 into check_date
396 from per_assignments_f
397 where assignment_id = p_assignment_id
398 and assignment_type = 'E';
399 --
400 EXCEPTION
401 WHEN NO_DATA_FOUND THEN NULL;
402 --
403 END;
404 --
405 hr_utility.set_location('hr_assignment.gen_probation_end',5);
406 --
407 IF check_date < 0 THEN
408 hr_utility.set_message(801,'HR_6150_EMP_ASS_PROB_END');
409 hr_utility.raise_error;
410 END IF;
411 --
412 END IF;
413 --
414 p_date_probation_end := v_date_probation_end;
415 --
416 END gen_probation_end; ---------------------------------------------
417 --
418 --
419 ----------------------- gen_new_ass_sequence -------------------------
420 /*
421 NAME
422 gen_new_ass_sequence
423 DESCRIPTION
424 Generates a new assignment sequence for Applicant and Employee
425 Assignments.
426 */
427 PROCEDURE gen_new_ass_sequence
428 ( p_person_id in number
429 , p_assignment_type in varchar2
430 , p_assignment_sequence in out nocopy number
431 ) is
432 --
433 begin
434 --
435 hr_utility.set_location('hr_assignment.gen_new_ass_sequence',1);
436 select nvl(max(assignment_sequence),0) +1
437 into p_assignment_sequence
438 from per_assignments_f
439 where person_id = p_person_id
440 and assignment_type = p_assignment_type;
441 --
442 end gen_new_ass_sequence; ------------------------------------------
443 --
444 --
445 ----------------------- gen_new_ass_number ---------------------------
446 /*
447 NAME
448 gen_new_ass_number
449 DESCRIPTION
450 If an Assignment Number is passed to the procedure it validates
451 that it is a unique number within the business group.
452
453 If no Assignment Number is passed to the procedure then it determines
454 the value of the newxt assignment number. If the assignment sequence
455 is 1 then it is just the value of the employee number otherwise it is
456 the employee number || assignment sequence. If the generated assignment
457 number is not unique then the assignment sequence is incremented until
458 a valid assignment number is generated.
459 */
460 PROCEDURE gen_new_ass_number
461 ( p_assignment_id IN NUMBER
462 , p_business_group_id IN NUMBER
463 , p_employee_number IN VARCHAR2
464 , p_assignment_sequence IN NUMBER
465 , p_assignment_number IN OUT NOCOPY VARCHAR2
466 ) IS
467 begin
468 gen_new_ass_number
469 ( p_assignment_id => p_assignment_id
470 , p_business_group_id => p_business_group_id
471 , p_worker_number => p_employee_number
472 , p_assignment_type => 'E'
473 , p_assignment_sequence => p_assignment_sequence
474 , p_assignment_number => p_assignment_number
475 );
476 end gen_new_ass_number;
477 --
478 --
479 ----------------------- gen_new_ass_number ----OVERLOADED-------------
480 /*
481 NAME
482 gen_new_ass_number
483 DESCRIPTION
484 If an Assignment Number is passed to the procedure it validates
485 that it is a unique number within the business group.
486
487 If no Assignment Number is passed to the procedure then it determines
488 the value of the newxt assignment number. If the assignment sequence
489 is 1 then it is just the value of the worker number otherwise it is
490 the worker number || assignment sequence. If the generated assignment
491 number is not unique then the assignment sequence is incremented until
492 a valid assignment number is generated.
493 */
494 PROCEDURE gen_new_ass_number
495 ( p_assignment_id IN NUMBER
496 , p_business_group_id IN NUMBER
497 , p_worker_number IN VARCHAR2
498 , p_assignment_type IN VARCHAR2
499 , p_assignment_sequence IN NUMBER
500 , p_assignment_number IN OUT NOCOPY VARCHAR2
501 ) IS
502 -----------------------------------------------------------
503 -- DECLARE THE LOCAL VARIABLES
504 -----------------------------------------------------------
505 loop_count INTEGER;
506 ass_seq NUMBER;
507 ----------------------------------------------------------------
508 -- DECLARE THE SUB-PROGRAMS
509 -----------------------------------------------------------------
510 -- VALIDATE THAT THE ASSIGNMENT NUMBER IS UNIQUE FOR THE PERSON
511 --
512 FUNCTION validate_ass_number
513 ( p_assignment_id INTEGER
514 , p_business_group_id INTEGER
515 , p_assignment_number VARCHAR2
516 ) RETURN BOOLEAN IS
517 --
518 duplicate VARCHAR2(1);
519 --
520 BEGIN
521 --
522 duplicate := 'N';
523 --
524 BEGIN
525 --
526 hr_utility.set_location('hr_assignment.gen_new_ass_number',1);
527 select 'Y'
528 into duplicate
529 from sys.dual
530 where exists
531 ( select 'Y'
532 from per_all_assignments_f
533 -- from per_assignments_f commented for bug 4232539
534 where ((p_assignment_id is null)
535 or
536 ( p_assignment_id is not null
537 and assignment_id <> p_assignment_id))
538 and business_group_id + 0 = p_business_group_id
539 and assignment_type = p_assignment_type
540 and assignment_number = p_assignment_number);
541 --
542 EXCEPTION
543 WHEN NO_DATA_FOUND THEN NULL;
544 --
545 END;
546 --
547 RETURN (duplicate = 'N');
548 --
549 END validate_ass_number;
550 --
551 BEGIN
552 --
553 loop_count := 100;
554 ass_seq := p_assignment_sequence;
555 --
556 IF p_assignment_number IS NOT NULL THEN
557 --
558 hr_utility.set_location('hr_assignment.gen_new_ass_number',2);
559 IF validate_ass_number(p_assignment_id
560 ,p_business_group_id
561 ,p_assignment_number) THEN
562 NULL;
563 ELSE
564 hr_utility.set_message(801,'HR_6146_EMP_ASS_DUPL_NUMBER');
565 hr_utility.raise_error;
566 END IF;
567 --
568 ELSE
569 --
570 hr_utility.set_location('hr_assignment.gen_new_ass_number',3);
571 WHILE loop_count > 0 LOOP
572 --
573 IF ass_seq = 1 THEN
574 -- p_assignment_number := p_worker_number;
575 p_assignment_number := substr(p_worker_number,1,30); --2371510
576 ELSE
577 -- fix for the bug 5498344
578 -- initialized the sequence with 2 so that the assignments numbers are generated correctly
579 -- when whiring exemp with more than one application and hiring into the last one
580 if loop_count = 100 then -- added fix
581
582 --start changes for bug 6328981
583 -- ass_seq :=2;
584 begin
585 /* select max(
586 case
587 when replace(assignment_number,p_worker_number) is null then 2
588 else to_number(replace(assignment_number,p_worker_number||'-'))
589 end
590 ) into ass_seq
591 from per_all_assignments_f
592 where person_id = (select distinct person_id
593 from per_all_people_f
594 where employee_number = p_worker_number
595 and business_group_id = p_business_group_id)
596 and business_group_id + 0 = p_business_group_id;*/
597 --Added for bug 6633320
598 --start changes for bug 7503993
599 If p_assignment_type = 'E' then
600 select nvl(max(
601 case
602 when replace(assignment_number,p_worker_number) is null then 2
603 else to_number(replace(assignment_number,p_worker_number||'-'))
604 end
605 ),2) into ass_seq
606 from per_all_assignments_f
607 where person_id = (select distinct person_id
608 from per_all_people_f
609 where employee_number = p_worker_number
610 and business_group_id = p_business_group_id)
611 and business_group_id = p_business_group_id
612 and instr(assignment_number,p_worker_number||'-',1) > 0;
613 elsif p_assignment_type = 'C' then
614 select nvl(max(
615 case
616 when replace(assignment_number,p_worker_number) is null then 2
617 else to_number(replace(assignment_number,p_worker_number||'-'))
618 end
619 ),2) into ass_seq
620 from per_all_assignments_f
621 where person_id = (select distinct person_id
622 from per_all_people_f
623 where npw_number = p_worker_number
624 and business_group_id = p_business_group_id)
625 and business_group_id = p_business_group_id
626 and instr(assignment_number,p_worker_number||'-',1) > 0;
627 end if;
628 --end changes for bug 7503993
629 exception
630 when no_data_found then
631 ass_seq :=2;
632 when others then
633 ass_seq :=2;
634 end;
635
636 --end changes for bug 6328981
637 end if;
638 -- end of fix 5498344
639 --
640 --2371510
641 p_assignment_number := substr(p_worker_number,1,29-length(TO_CHAR(ass_seq)))||'-'||TO_CHAR(ass_seq);
642 -- p_assignment_number := p_worker_number||'-'||TO_CHAR(ass_seq);
643 END IF;
644 --
645 hr_utility.set_location('hr_assignment.gen_new_ass_number',4);
646 IF validate_ass_number(p_assignment_id
647 ,p_business_group_id
648 ,p_assignment_number) THEN
649 EXIT;
650 ELSE
651 ass_seq := ass_seq + 1;
652 loop_count := loop_count - 1;
653 END IF;
654 --
655 END LOOP;
656 --
657 hr_utility.set_location('hr_assignment.gen_new_ass_number',5);
658 IF loop_count = 0 THEN
659 hr_utility.set_message(801,'HR_6148_EMP_ASS_LOOP_OUT');
660 hr_utility.raise_error;
661 END IF;
662 --
663 END IF;
664 --
665 END gen_new_ass_number; ---------------------------------------------
666 --
667 --
668 ----------------------- check_hours ----------------------------------
669 /*
670 NAME
671 check_hours
672 DESCRIPTION
673 Validation to ensure that the normal working hours do not exceed
674 the maximum availble for the Frequency.
675 PARAMETERS
676 p_frequency - Standard Conditions PER field
677 - only D,W,M,Y are valid values
678 p_normal_hours - Standard Conditions WORKING HOURS field
679 */
680 PROCEDURE check_hours
681 ( p_frequency IN VARCHAR2
682 , p_normal_hours IN NUMBER
683 ) IS
684 -----------------------------------------------------------
685 -- DECLARE THE LOCAL VARIABLES
686 -----------------------------------------------------------
687 no_of_hours NUMBER;
688 --
689 BEGIN
690 --
691 hr_utility.set_location('hr_assignment.check_hours',1);
692 IF p_frequency = 'D' THEN
693 no_of_hours := 24;
694 ELSIF p_frequency = 'W' THEN
695 no_of_hours := 168;
696 ELSIF p_frequency = 'M' THEN
697 no_of_hours := 744;
698 ELSIF p_frequency = 'Y' THEN
699 no_of_hours := 8784;
700 ELSIF p_frequency = 'H' THEN
701 no_of_hours := 1;
702 ELSE
703 no_of_hours := 0;
704 END IF;
705 --
706 IF no_of_hours - p_normal_hours < 0 THEN
707 hr_utility.set_message(801,'HR_6015_ALL_FORMAT_WKG_HRS');
708 hr_utility.raise_error;
709 END IF;
710 --
711 END check_hours; -----------------------------------------------------
712 --
713 --
714 ------------------- check_term -----------------------------
715 /*
716 NAME
717 check_term
718 DESCRIPTION
719 If an Update Override, Delete Next Change or Future Change Delete
720 will remove terminated assignments or end dates after
721 assignment status changes of TERM_ASSIGN then the end date may need
722 to be fixed to either the Actual Termination Date or the Final
723 Process Date or the Employees Period of Service. This procedure
724 determines the requirement and returns an new End Date if one is
725 required.
726 PARAMETERS
727 p_period_of_service_id - Employee's Current Period of Service ID
728 p_assignment_id - Assignment ID
729 p_sdate - Start Date of current Assignment row
730 p_edate - End Date of current Assignment row
731 p_current_status - The PER_SYSTEM_STATUS of the current row
732 p_mode - FUTURE_CHANGES, DELETE_NEXT_CHANGE,
733 UPDATE_OVERRIDE
734 p_newdate - The New ASsignment End Date
735 */
736 PROCEDURE check_term
737 (
738 p_period_of_service_id IN INTEGER
739 ,p_assignment_id IN INTEGER
740 ,p_sdate IN DATE
741 ,p_edate IN DATE
742 ,p_current_status IN VARCHAR2
743 ,p_mode IN VARCHAR2
744 ,p_newdate OUT NOCOPY DATE
745 ) IS
746 p_atd DATE;
747 p_fpd DATE;
748 p_ass_end_date DATE;
749 p_first_term_date DATE;
750 p_start_date DATE;
751 p_end_date DATE;
752 p_new_ass_end_date DATE;
753 p_flag VARCHAR2(1);
754 p_next_eff_start_date DATE;
755 p_next_eff_end_date DATE;
756 l_person_id NUMBER;
757 l_assignment_type per_all_assignments_f.assignment_type%TYPE;
758 l_pdp_date_start DATE;
759
760 --
761 -- Fetch the person ID and assignment type so the
762 -- period of placement can be obtained for
763 -- contingent workers.
764 --
765 CURSOR csr_get_assignment_info IS
766 SELECT paaf.person_id
767 ,paaf.assignment_type
768 ,paaf.period_of_placement_date_start
769 FROM per_all_assignments_f paaf
770 WHERE paaf.assignment_id = p_assignment_id
771 AND paaf.assignment_type IN ('E', 'C');
772
773 --
774 -- Get the termination dates for the period of placement and
775 -- period of service.
776 --
777 CURSOR csr_get_term_dates IS
778 SELECT actual_termination_date
779 ,NVL(final_process_date, hr_api.g_eot)
780 FROM per_periods_of_service
781 WHERE period_of_service_id = p_period_of_service_id
782 UNION
783 SELECT pdp.actual_termination_date
784 ,NVL(pdp.final_process_date, hr_api.g_eot)
785 FROM per_periods_of_placement pdp
786 WHERE pdp.person_id = l_person_id
787 AND pdp.date_start = l_pdp_date_start;
788
789
790 BEGIN
791 --
792 hr_utility.set_location('hr_assignment.check_term',1);
793 p_start_date := p_sdate;
794 p_end_date := p_edate;
795 p_new_ass_end_date := null;
796
797 --
798 -- Fetch the desired assignment details.
799 --
800 OPEN csr_get_assignment_info;
801 FETCH csr_get_assignment_info INTO l_person_id
802 ,l_assignment_type
803 ,l_pdp_date_start;
804 CLOSE csr_get_assignment_info;
805
806 --
807 -- Fetch the termination dates.
808 --
809 OPEN csr_get_term_dates;
810 FETCH csr_get_term_dates INTO p_atd
811 ,p_fpd;
812 CLOSE csr_get_term_dates;
813
814 hr_utility.set_location('hr_assignment.check_term',2);
815
816 IF p_atd IS NULL THEN null;
817 ELSE
818 --
819 -------------------------------------
820 -- Get the Effective End Date of the Assignment
821 -------------------------------------
822 hr_utility.set_location('hr_assignment.check_term',3);
823 --
824 select max(effective_end_date)
825 into p_ass_end_date
826 from per_assignments_f
827 where assignment_id = p_assignment_id;
828 --
829 -------------------------------------
830 -- Get the Start Date of the First TERM_ASSIGN status.
831 --
832 -- If the mode is UPDATE_OVERRIDE and the current status is TERM_ASSIGN
833 -- then compare the session date with the earliest TERM_ASSIGN date
834 -- and store the earliest.
835 -------------------------------------
836 hr_utility.set_location('hr_assignment.check_term',4);
837 --
838 select min(a.effective_start_date)
839 into p_first_term_date
840 from per_assignments_f a
841 where a.assignment_id = p_assignment_id
842 and exists ( select null
843 from per_assignment_status_types s
844 where s.assignment_status_type_id
845 = a.assignment_status_type_id
846 and s.per_system_status = 'TERM_ASSIGN');
847 --
848 hr_utility.set_location('hr_assignment.check_term',5);
849 IF p_mode = 'UPDATE_OVERRIDE' AND
850 p_current_status = 'TERM_ASSIGN' THEN
851 --
852 IF p_first_term_date IS NULL OR
853 (p_first_term_date IS NOT NULL AND p_first_term_date > p_start_date)
854 THEN
855 p_first_term_date := p_start_date;
856 END IF;
857 --
858 END IF;
859 --
860 ---------------------------------------------------------------
861 -- If the mode is UPDATE_OVERRIDE or FUTURE_CHANGE
862 -- then establish whether this will remove a TERM_ASSIGN
863 -- status on the day after ACTUAL_TERMINATION_DATE
864 --
865 -- If it does then issue an error
866 --
867 -- NB The same check is performed slightly differntly
868 -- for DELETE_NEXT_CHANGE below
869 ---------------------------------------------------------------
870
871 -- Bug 306713 Start
872
873 IF (p_mode = 'DELETE_NEXT_CHANGE' or p_mode = 'FUTURE_CHANGE') and p_ass_end_date = p_atd then
874 hr_utility.set_message(801,'HR_6200_EMP_ASS_TERM_EXISTS');
875 hr_utility.raise_error;
876 end if;
877
878 -- Bug 306713 End
879
880 IF (p_mode = 'UPDATE_OVERRIDE' or p_mode = 'FUTURE_CHANGE')
881 AND p_start_date < p_atd + 1 THEN
882 --
883 hr_utility.set_location('hr_assignment.check_term',6);
884 --
885 p_flag := 'N';
886 --
887 BEGIN
888 --
889 hr_utility.set_location('hr_assignment.check_term',7);
890 --
891 select 'Y'
892 into p_flag
893 from per_assignments_f a
894 where a.assignment_id = p_assignment_id
895 and a.effective_start_date = p_atd + 1
896 and exists
897 (select null
898 from per_assignment_status_types s
899 where s.assignment_status_type_id
900 = a.assignment_status_type_id
901 and s.per_system_status = 'TERM_ASSIGN');
902 EXCEPTION
903 WHEN NO_DATA_FOUND THEN NULL;
904 END;
905 --
906 hr_utility.set_location('hr_assignment.check_term',8);
907 IF p_flag = 'Y' THEN
908 hr_utility.set_message(801,'HR_6200_EMP_ASS_TERM_EXISTS');
909 hr_utility.raise_error;
910 END IF;
911 END IF;
912 --
913 ---------------------------------------------------------------
914 -- If mode is UPDATE_OVERRIDE then
915 -- if end date of assignment is before actual_termination_date
916 -- then don't do anything
917 -- otherwise
918 -- if the session date is on or after the first termination date
919 -- then don't do anything
920 -- otherwise
921 -- set the new_assignment_end_date = actual_termination_date
922 ---------------------------------------------------------------
923 IF p_mode = 'UPDATE_OVERRIDE' THEN
924 hr_utility.set_location('hr_assignment.check_term',9);
925 IF p_ass_end_date <= p_atd THEN
926 NULL;
927 ELSE
928 hr_utility.set_location('hr_assignment.check_term',10);
929 IF p_first_term_date <= p_start_date THEN
930 NULL;
931 ELSE
932 p_new_ass_end_date := p_atd;
933 END IF;
934 END IF;
935 --
936 ---------------------------------------------------------------
937 -- If mode is FUTURE_CHANGE then
938 -- if the first termination date is on or before the current start date
939 -- then open the assignment up to the final process date
940 -- otherwise
941 -- open the assignment up to the actual term date.
942 ---------------------------------------------------------------
943 ELSIF
944 p_mode = 'FUTURE_CHANGE' THEN
945 hr_utility.set_location('hr_assignment.check_term',11);
946 IF p_first_term_date <= p_start_date THEN
947 p_new_ass_end_date := p_fpd;
948 ELSE
949 p_new_ass_end_date := p_atd;
950 END IF;
951 --
952 ---------------------------------------------------------------
953 -- If mode is DELETE_NEXT_CHANGE then
954 -- IF the current row is the last for this assignment
955 -- then the end date will be removed by the DELETE NEXT CHANGE
956 -- in this case make sure the end date is reset correctly
957 -- i.e.
958 -- if the first termination date is on or before the current start date
959 -- then open the assignment up to the final process date
960 -- otherwise
961 -- open the assignment up to the actual term date.
962 -- END IF;
963 --
964 -- Otherwise
965 -- read the row that is going to be removed
966 -- if its status is TERM_ASSIGN then store the effective start date
967 -- and effective end date
968 -- otherwise end the step because the delete will remove an
969 -- innocuous change.
970 --
971 -- If the effective start date is the same as the actual term date
972 -- then issue an error because we are trying to remove a change
973 -- brought about by Termination of Employee
974 --
975 -- Otherwise
976 -- If the first termination date is on or before the current start
977 -- date then open up the assignment to the final process date
978 -- otherwise
979 -- we will be removing the first TERM_ASSIGN
980 -- if the row is be removed is the last one then
981 -- if its end date is after the ATD then
982 -- set new end date = actual termination date
983 -- otherwise don't do anything
984 -- otherwise
985 -- a TERM_ASSIGN record will be left in the future,
986 -- if this is after the atd then there is an invlaid situation
987 -- where the active assignment runs past the ATD
988 -- therefore in this case issue an error.
989 ---------------------------------------------------------------
990 ELSIF
991 p_mode = 'DELETE_NEXT_CHANGE' THEN
992 hr_utility.set_location('hr_assignment.check_term',12);
993 IF p_end_date = p_ass_end_date THEN
994 hr_utility.set_location('hr_assignment.check_term',13);
995 IF p_first_term_date <= p_start_date THEN
996 p_new_ass_end_date := p_fpd;
997 ELSE
998 p_new_ass_end_date := p_atd;
999 END IF;
1000 ELSE
1001 --
1002 p_flag := 'N';
1003 --
1004 BEGIN
1005 --
1006 hr_utility.set_location('hr_assignment.check_term',14);
1007 --
1008 select 'Y'
1009 , a.effective_start_date
1010 , a.effective_end_date
1011 into p_flag
1012 , p_next_eff_start_date
1013 , p_next_eff_end_date
1014 from per_assignments_f a
1015 where a.assignment_id = p_assignment_id
1016 and a.effective_start_date = p_end_date + 1
1017 and exists
1018 (select null
1019 from per_assignment_status_types s
1020 where s.assignment_status_type_id
1021 = a.assignment_status_type_id
1022 and s.per_system_status = 'TERM_ASSIGN');
1023 EXCEPTION
1024 WHEN NO_DATA_FOUND THEN NULL;
1025 END;
1026 --
1027 hr_utility.set_location('hr_assignment.check_term',15);
1028 IF p_flag = 'Y' THEN
1029 hr_utility.set_location('hr_assignment.check_term',16);
1030 IF p_next_eff_start_date = p_atd + 1 THEN
1031 hr_utility.set_message(801,'HR_6200_EMP_ASS_TERM_EXISTS');
1032 hr_utility.raise_error;
1033 ELSE
1034 IF p_first_term_date <= p_start_date THEN
1035 hr_utility.set_location('hr_assignment.check_term',17);
1036 p_new_ass_end_date := p_fpd;
1037 ELSE
1038 IF p_next_eff_end_date = p_ass_end_date THEN
1039 hr_utility.set_location('hr_assignment.check_term',18);
1040 IF p_ass_end_date > p_atd THEN
1041 p_new_ass_end_date := p_atd;
1042 ELSE
1043 NULL;
1044 END IF; -- (p_ass_end_date > p_atd)
1045 ELSE
1046 IF p_next_eff_end_date >= p_atd + 1 THEN
1047 hr_utility.set_message(801,'HR_6320_EMP_ASS_AFTER_ATD');
1048 hr_utility.raise_error;
1049 END IF; -- (p_next_eff_end_date >= p_atd + 1)
1050 END IF; -- (p_next_eff_end_date = p_ass_end_date)
1051 END IF; -- (p_first_term_date <= p_start_date)
1052 END IF; -- (p_next_eff_start_date = p_atd + 1)
1053 END IF; -- (p_flag = 'Y')
1054 END IF; -- (p_end_date = p_ass_end_date)
1055 --
1056 END IF; -- (p_mode = 'UPDATE_OVERRIDE')
1057 --
1058 END IF; -- (p_atd IS NULL)
1059 --
1060 hr_utility.set_location('hr_assignment.check_term',19);
1061 IF p_new_ass_end_date IS NOT NULL THEN
1062 ------------------------------------------------------------
1063 -- First check whether setting this end date will invalidate
1064 -- any child rows.
1065 ------------------------------------------------------------
1066 hr_assignment.del_ref_int_check
1067 ( p_assignment_id
1068 , 'END'
1069 , p_new_ass_end_date);
1070 p_newdate := p_new_ass_end_date;
1071 END IF;
1072 --
1073 END check_term;
1074 --
1075 --
1076 ------------------- warn_del_term ----------------------------
1077 /*
1078 NAME
1079 warn_del_term
1080 DESCRIPTION
1081 If the operation will remove an assignment with TERM_ASSIGN status
1082 then a warning will be issued from the form. This procedure
1083 determines whether such an operation will take place.
1084 PARAMETERS
1085 p_assignment_id - Assignment ID
1086 p_effective_start_date - Start Date of current Assignment row
1087 p_effective_end_date - End Date of current Assignment row
1088 p_mode - FUTURE_CHANGES, DELETE_NEXT_CHANGE,
1089 UPDATE_OVERRIDE
1090 */
1091 PROCEDURE warn_del_term
1092 (
1093 p_assignment_id IN INTEGER
1094 ,p_mode IN VARCHAR2
1095 ,p_effective_start_date IN DATE
1096 ,p_effective_end_date IN DATE
1097 ) IS
1098 --
1099 p_term_found VARCHAR2(1);
1100 local_warning exception;
1101 --
1102 BEGIN
1103 --
1104 p_term_found := 'N';
1105 --
1106 begin
1107 --
1108 hr_utility.set_location('hr_assignment.warn_del_term',1);
1109 --
1110 select 'Y'
1111 into p_term_found
1112 from sys.dual
1113 where exists
1114 (select null
1115 from per_assignments_f a
1116 , per_assignment_status_types s
1117 where a.assignment_id = p_assignment_id
1118 and a.effective_start_date
1119 > p_effective_start_date
1120 and a.effective_start_date =
1121 decode(p_mode,'DELETE_NEXT_CHANGE',
1122 p_effective_end_date + 1
1123 ,a.effective_start_date)
1124 and s.assignment_status_type_id = a.assignment_status_type_id
1125 and s.per_system_status = 'TERM_ASSIGN');
1126 --
1127 exception
1128 when NO_DATA_FOUND then null;
1129 end;
1130 --
1131 hr_utility.set_location('hr_assignment.warn_del_term',2);
1132 if p_term_found = 'Y' then
1133 raise local_warning;
1134 end if;
1135 --
1136 EXCEPTION
1137 when local_warning then
1138 hr_utility.set_warning;
1139 END warn_del_term;
1140 --
1141 --
1142 ------------------- delete_ass_ref_int ----------------------------
1143 /*
1144 NAME
1145 delete_ass_ref_int
1146 DESCRIPTION
1147 Determines whether there are any dependent records for the Assignment.
1148 If any are found then delete them.
1149 The following tables are examined
1150 PER_SPINAL_POINT_PLACEMENTS
1151 PER_SECONDARY_ASS_STATUSES
1152 PER_ASSIGNMENT_BUDGET_VALUES
1153
1154 PARAMETERS
1155 p_business_group_id - Business Group ID
1156 p_assignment_id - Assignment ID
1157 */
1158 PROCEDURE delete_ass_ref_int
1159 (
1160 p_business_group_id IN INTEGER
1161 ,p_assignment_id IN INTEGER
1162 ) IS
1163 del_flag VARCHAR2(1);
1164 --
1165 BEGIN
1166 --
1167 -- del_flag := 'N';
1168 --
1169 BEGIN
1170 hr_utility.set_location('hr_assignment.delete_ass_ref_int',1);
1171 --
1172 SELECT 'Y'
1173 into del_flag
1174 FROM SYS.DUAL
1175 WHERE EXISTS
1176 (SELECT NULL
1177 FROM PER_SPINAL_POINT_PLACEMENTS_F P
1178 WHERE P.business_group_id + 0 = p_business_group_id
1179 AND P.ASSIGNMENT_ID = p_assignment_id);
1180 --
1181 EXCEPTION
1182 WHEN NO_DATA_FOUND THEN NULL;
1183 END;
1184 --
1185 IF del_flag = 'Y' THEN
1186 hr_utility.set_location('hr_assignment.delete_ass_ref_int',2);
1187 --
1188 DELETE FROM PER_SPINAL_POINT_PLACEMENTS_F P
1189 WHERE P.business_group_id + 0 = p_business_group_id
1190 AND P.ASSIGNMENT_ID = p_assignment_id;
1191 --
1192 END IF;
1193 --
1194 del_flag := 'N';
1195 --
1196 BEGIN
1197 --
1198 hr_utility.set_location('hr_assignment.delete_ass_ref_int',3);
1199 --
1200 SELECT 'Y'
1201 into del_flag
1202 from sys.dual
1203 WHERE EXISTS
1204 (SELECT NULL
1205 FROM PER_SECONDARY_ASS_STATUSES S
1206 WHERE S.business_group_id + 0 = p_business_group_id
1207 AND S.ASSIGNMENT_ID = p_assignment_id);
1208 --
1209 EXCEPTION
1210 WHEN NO_DATA_FOUND THEN NULL;
1211 END;
1212 --
1213 IF del_flag = 'Y' THEN
1214 hr_utility.set_location('hr_assignment.delete_ass_ref_int',4);
1215 --
1216 DELETE FROM PER_SECONDARY_ASS_STATUSES
1217 WHERE business_group_id + 0 = p_business_group_id
1218 AND ASSIGNMENT_ID = p_assignment_id;
1219 --
1220 END IF;
1221 --
1222 del_flag := 'N';
1223 --
1224 BEGIN
1225 hr_utility.set_location('hr_assignment.delete_ass_ref_int',5);
1226 --
1227 SELECT 'Y'
1228 into del_flag
1229 from sys.dual
1230 WHERE EXISTS
1231 (SELECT NULL
1232 FROM PER_ASSIGNMENT_BUDGET_VALUES_F BV
1233 WHERE BV.business_group_id + 0 = p_business_group_id
1234 AND BV.ASSIGNMENT_ID = p_assignment_id);
1235 --
1236 EXCEPTION
1237 WHEN NO_DATA_FOUND THEN NULL;
1238 END;
1239 --
1240 IF del_flag = 'Y' THEN
1241 hr_utility.set_location('hr_assignment.delete_ass_ref_int',6);
1242 --
1243 DELETE FROM PER_ASSIGNMENT_BUDGET_VALUES_F BV
1244 WHERE BV.business_group_id + 0 = p_business_group_id
1245 AND BV.ASSIGNMENT_ID = p_assignment_id;
1246 --
1247 END IF;
1248 --
1249 END delete_ass_ref_int;
1250 --
1251 --
1252 ------------------- get_act_term_date -----------------------------
1253 /*
1254 NAME
1255 get_act_term_date
1256 DESCRIPTION
1257 Returns the Actual Termination Date of the Employee Period of Service.
1258
1259 PARAMETERS
1260 p_period_of_service_id
1261 p_actual_termination_date
1262 */
1263 PROCEDURE get_act_term_date
1264 (
1265 p_period_of_service_id IN INTEGER
1266 ,p_actual_termination_date OUT NOCOPY DATE
1267 ) IS
1268 --
1269 BEGIN
1270 --
1271 -------------------------------------------------
1272 -- Retrieve the ACTUAL TERMINATION DATE for the Period of Service
1273 -------------------------------------------------
1274 hr_utility.set_location('hr_assignment.get_act_term_date',1);
1275 --
1276 select actual_termination_date
1277 into p_actual_termination_date
1278 from per_periods_of_service
1279 where period_of_service_id = p_period_of_service_id;
1280 --
1281 END get_act_term_date;
1282 --
1283 --
1284 ------------------- check_future_primary --------------------------
1285 /*
1286 NAME
1287 check_future_primary
1288 DESCRIPTION
1289 Checks to see whether the operation will remove a row
1290 that has a primary flag value differnet to the current one.
1291 If such a row is found then the P_CHANGE_FLAG is set to 'Y' and
1292 the date from which changes to other assignment primary flag
1293 changes must be catered for is determined and passed back in
1294 P_PRIMARY_DATE_FROM.
1295 PARAMETERS
1296 p_assignment_id - The current assignment to be checked
1297 p_sdate - The start date of the current row
1298 NB this depends on the Mode
1299 UPDATE_OVERRIDE ==> Validation Start Date
1300 Otherwise ==> Effective Start Date
1301 p_edate - Effective End Date of the current row
1302 p_mode - The DT_UPDATE_MODE or DT_DELETE_MODE
1303 p_primary_flag - The Primary Flag Value for the current assignment
1304 p_change_flag - An indicator to detect whether primary changes are
1305 required.
1306 p_new_primary_flag - The value that the current assignment will have
1307 after the operation
1308 p_primary_date_from- The date from which changes to other assignments
1309 must be catered for
1310 */
1311 PROCEDURE check_future_primary
1312 (
1313 p_assignment_id IN INTEGER
1314 ,p_sdate IN DATE
1315 ,p_edate IN DATE
1316 ,p_mode IN VARCHAR2
1317 ,p_primary_flag IN VARCHAR2
1318 ,p_change_flag IN OUT NOCOPY VARCHAR2
1319 ,p_new_primary_flag IN OUT NOCOPY VARCHAR2
1320 ,p_primary_date_from OUT NOCOPY DATE
1321 ) IS
1322 p_start_date DATE;
1323 p_end_date DATE;
1324 p_primary_date_from_d DATE;
1325 --
1326 l_change_flag VARCHAR2(2000) := p_change_flag ;
1327 l_new_primary_flag VARCHAR2(2000) := p_new_primary_flag ;
1328 l_primary_date_from DATE := p_primary_date_from ;
1329
1330 --
1331 BEGIN
1332 --
1333 hr_utility.set_location('hr_assignment.check_future_primary',1);
1334 p_start_date := p_sdate;
1335 p_end_date := p_edate;
1336 --
1337 -------------------------------------
1338 --
1339 -------------------------------------
1340 p_change_flag := 'N';
1341 -------------------------------------
1342 -- If the mode is ZAP then the new primary flag is effectively 'N' i.e.
1343 -- it cannot be 'Y'
1344 --
1345 -- Otherwise the new primary flag for the current assignment is that passed
1346 -- into the procedure
1347 -- Also the date from which primary flag changes are to effective from
1348 -- is set set to the start date of the current record
1349 -- see below for ZAP.
1350 -------------------------------------
1351 IF p_mode = 'ZAP' THEN
1352 p_new_primary_flag := 'N';
1353 ELSE
1354 p_new_primary_flag := p_primary_flag;
1355 p_primary_date_from := p_sdate;
1356 END IF;
1357 --
1358 -------------------------------------
1359 -- Search the appropriate row(s) (depending on the mode) for
1360 -- a change of primary flag that will removed as a result of the
1361 -- current operation
1362 -- i.e ZAP - All rows
1363 -- NEXT_CHANGE - the next row
1364 -- FUTURE_CHANGES and UPDATE_OVERRIDE - All rows in future
1365 -------------------------------------
1366 begin
1367 hr_utility.set_location('hr_assignment.check_future_primary',2);
1368 --
1369 select 'Y'
1370 into p_change_flag
1371 from sys.dual
1372 where exists
1373 (select null
1374 from per_assignments_f
1375 where assignment_id = p_assignment_id
1376 and primary_flag <> p_new_primary_flag
1377 and effective_start_date >
1378 decode(p_mode,'ZAP',effective_start_date-1,p_start_date)
1379 and effective_start_date =
1380 decode(p_mode,'DELETE_NEXT_CHANGE',p_end_date+1
1381 ,effective_start_date));
1382 exception
1383 when NO_DATA_FOUND then NULL;
1384 end;
1385 --
1386 ---------------------------------------
1387 -- If the mode is ZAP and a change has been found
1388 -- retrieve the earliest occurrence of PRIMARY_FLAG = 'Y'. A new
1389 -- Primary Assignment will be required from this date.
1390 ---------------------------------------
1391 IF p_mode = 'ZAP' AND p_change_flag = 'Y' THEN
1392 hr_utility.set_location('hr_assignment.check_future_primary',3);
1393 --
1394 select min(effective_start_date)
1395 into p_primary_date_from_d
1396 from per_assignments_f
1397 where assignment_id = p_assignment_id
1398 and primary_flag = 'Y';
1399 END IF;
1400 --
1401 IF p_primary_date_from_d IS NOT NULL THEN
1402 p_primary_date_from := p_primary_date_from_d;
1403 END IF;
1404 --
1405 EXCEPTION
1406 when others then
1407 p_change_flag := l_change_flag ;
1408 p_new_primary_flag := l_new_primary_flag ;
1409 p_primary_date_from := l_primary_date_from ;
1410 RAISE ;
1411
1412 END check_future_primary;
1413 --
1414 --
1415 ------------------- check_ass_for_primary -------------------------
1416 /*
1417 NAME
1418 check_ass_for_primary
1419 DESCRIPTION
1420 Checks to ensure that the record is continuous until the end
1421 of the Period Of Service / Placement and that if it has been terminated
1422 then termination was as a result of the termination of the employee
1423 i.e. the termination date is the same as the ACTUAL TERMINATION DATE.
1424 PARAMETERS
1425 p_period_of_service_id - The current Period of Service ID
1426 p_assignment_id - The current assignment ID
1427 p_sdate - The validation start date of the updated record
1428 */
1429 PROCEDURE check_ass_for_primary
1430 (
1431 p_period_of_service_id IN INTEGER
1432 ,p_assignment_id IN INTEGER
1433 ,p_sdate IN DATE
1434 ) IS
1435 p_atd DATE;
1436 p_fpd DATE;
1437 p_ass_end_date DATE;
1438 p_first_term_date DATE;
1439 p_start_date DATE;
1440 l_pdp_date_start DATE;
1441 l_person_id NUMBER;
1442 l_assignment_type per_all_assignments_f.assignment_type%TYPE;
1443
1444 CURSOR csr_get_assignment_info IS
1445 SELECT paaf.person_id
1446 ,paaf.assignment_type
1447 ,paaf.period_of_placement_date_start
1448 FROM per_all_assignments_f paaf
1449 WHERE paaf.assignment_id = p_assignment_id
1450 AND paaf.assignment_type IN ('E', 'C');
1451
1452 CURSOR csr_get_term_dates IS
1453 SELECT NVL(actual_termination_date, to_date('30/12/4712','DD/MM/YYYY'))
1454 ,NVL(final_process_date,to_date('31/12/4712','DD/MM/YYYY'))
1455 FROM per_periods_of_service
1456 WHERE period_of_service_id = p_period_of_service_id
1457 UNION
1458 SELECT NVL(pdp.actual_termination_date,to_date('30/12/4712','DD/MM/YYYY'))
1459 ,NVL(pdp.final_process_date,to_date('31/12/4712','DD/MM/YYYY'))
1460 FROM per_periods_of_placement pdp
1461 WHERE pdp.person_id = l_person_id
1462 AND pdp.date_start = l_pdp_date_start;
1463
1464 --
1465 BEGIN
1466 --
1467 --
1468 -- Fetch the assignment type and placement date start.
1469 --
1470 OPEN csr_get_assignment_info;
1471 FETCH csr_get_assignment_info INTO l_person_id
1472 ,l_assignment_type
1473 ,l_pdp_date_start;
1474 CLOSE csr_get_assignment_info;
1475
1476 p_start_date := p_sdate;
1477
1478 -------------------------------------
1479 -- Get the Actual Termination Date and Final Process Date
1480 -------------------------------------
1481 hr_utility.set_location('hr_assignment.check_ass_for_primary',1);
1482 --
1483 -- #306211. If ATD was null, then NVL to EOT - 1, instead of EOT. This is
1484 -- because trying to add 1 to EOT (as happens in a number of places below)
1485 -- raises an ORA-1841 error. So use Dec 30 instead of Dec 31.
1486 -- The ATD and FPD for contingent workers will be the same.
1487 --
1488 OPEN csr_get_term_dates;
1489 FETCH csr_get_term_dates INTO p_atd, p_fpd;
1490 CLOSE csr_get_term_dates;
1491
1492 -------------------------------------
1493 -- Get the Effective End Date of the Assignment
1494 -------------------------------------
1495 hr_utility.set_location('hr_assignment.check_ass_for_primary',2);
1496 --
1497 select max(effective_end_date)
1498 into p_ass_end_date
1499 from per_assignments_f
1500 where assignment_id = p_assignment_id;
1501 --
1502 -- If the end date of the assignment is not on or after the
1503 -- period of service final process date then ERROR
1504 --
1505 IF p_ass_end_date < p_fpd THEN
1506 hr_utility.set_message(801,'HR_6380_EMP_ASS_END_OFF_FPD');
1507 hr_utility.raise_error;
1508 END IF;
1509 --
1510 -------------------------------------
1511 -- If the Start Date of the record is on or before the day after ATD
1512 -- then the first TERM_ASSIGN must be on the day after the ATD i.e.
1513 -- the TERMINATION was brought about by the Employee Termination.
1514 -------------------------------------
1515 IF p_atd IS NULL THEN NULL;
1516 ELSIF p_atd + 1 <= p_start_date THEN NULL;
1517 ELSE
1518 -------------------------------------
1519 -- Get the Start Date of the First terminated status.
1520 -------------------------------------
1521 hr_utility.set_location('hr_assignment.check_ass_for_primary',3);
1522 --
1523 if l_assignment_type <> 'C' then
1524
1525 select min(a.effective_start_date)
1526 into p_first_term_date
1527 from per_assignments_f a
1528 where a.assignment_id = p_assignment_id
1529 and exists ( select null
1530 from per_assignment_status_types s
1531 where s.assignment_status_type_id
1532 = a.assignment_status_type_id
1533 and s.per_system_status = 'TERM_ASSIGN');
1534 --
1535 hr_utility.set_location('hr_assignment.check_ass_for_primary',4);
1536 IF p_first_term_date = p_atd + 1
1537 OR
1538 p_first_term_date IS NULL THEN NULL;
1539 ELSE
1540 hr_utility.set_message(801,'HR_6381_EMP_ASS_TERM_OFF_ATD');
1541 hr_utility.raise_error;
1542 END IF;
1543 END IF;
1544 END IF;
1545 --
1546 END check_ass_for_primary;
1547 --
1548 --------------------------------------------------------------------
1549 ------------------- update_primary_cwk -----------------------------
1550 --------------------------------------------------------------------
1551 --
1552 PROCEDURE update_primary_cwk
1553 (p_assignment_id IN INTEGER
1554 ,p_person_id IN NUMBER
1555 ,p_pop_date_start IN DATE
1556 ,p_new_primary_ass_id IN INTEGER
1557 ,p_sdate IN DATE
1558 ,p_new_primary_flag IN VARCHAR2
1559 ,p_mode IN VARCHAR2
1560 ,p_last_updated_by IN INTEGER
1561 ,p_last_update_login IN INTEGER ) IS
1562 --
1563 l_start_date DATE;
1564 l_proc VARCHAR2(72) := g_package||'update_primary_cwk';
1565 --
1566 CURSOR get_future_primary_assignments IS
1567 SELECT assignment_id
1568 FROM per_assignments_f
1569 WHERE assignment_id NOT IN (p_assignment_id,p_new_primary_ass_id)
1570 AND person_id = p_person_id
1571 AND period_of_placement_date_start = p_pop_date_start
1572 AND effective_end_date >= l_start_date;
1573 --
1574 BEGIN
1575 --
1576 hr_utility.set_location('Entering : '||l_proc,10);
1577 --
1578 l_start_date := p_sdate;
1579 --
1580 -- Update the future changes for the current
1581 -- assignment with the new Primary Flag
1582 --
1583 IF p_mode <> 'ZAP' THEN
1584 --
1585 hr_utility.set_location(l_proc,20);
1586 --
1587 ----------------------------------------------------------------
1588 -- 3584122: The value of 'N' is not updating the future
1589 -- changes of the secondar_assignment to primary if a
1590 -- secondary assignment is updated to primary. As the calling
1591 -- procedure would set the new_primary_flag correctly so setting
1592 -- back to p_new_primary_flag.
1593 ----------------------------------------------------------------
1594 do_primary_update
1595 (p_assignment_id
1596 ,p_sdate
1597 ,p_new_primary_flag -- Bug 3584122 'N'-- Bug 2468916 p_new_primary_flag
1598 ,'Y'
1599 ,p_last_updated_by
1600 ,p_last_update_login);
1601 --
1602 END IF;
1603 --
1604 hr_utility.set_location(l_proc,30);
1605 --
1606 -- If the New Primary Asg is not the Current Asg then the Primary
1607 -- Flag has to be set to 'Y' on all the changes on or after the
1608 -- Start Date
1609 --
1610 IF p_assignment_id <> p_new_primary_ass_id THEN
1611 --
1612 hr_utility.set_location(l_proc,40);
1613 --
1614 do_primary_update
1615 (p_new_primary_ass_id
1616 ,p_sdate
1617 ,'Y'
1618 ,'N'
1619 ,p_last_updated_by
1620 ,p_last_update_login);
1621 --
1622 END IF;
1623 --
1624 hr_utility.set_location(l_proc,50);
1625 --
1626 -- Now for each assignment other than P_ASSIGNMENT_ID and
1627 -- P_NEW_PRIMARY_ASS_ID within the period_of_service
1628 -- future changes must have their Primary Flag set to 'N'. It is only
1629 -- necessary to update the ones that are currently 'Y'.
1630 --
1631 FOR ass_rec IN get_future_primary_assignments LOOP
1632 --
1633 do_primary_update
1634 (ass_rec.assignment_id
1635 ,p_sdate
1636 ,'N'
1637 ,'N'
1638 ,p_last_updated_by
1639 ,p_last_update_login);
1640 --
1641 END LOOP;
1642 --
1643 hr_utility.set_location(' Leaving : '||l_proc,999);
1644 --
1645 END update_primary_cwk;
1646 --
1647 --
1648 ------------------- update_primary -----------------------------
1649 /*
1650 NAME
1651 update_primary
1652 DESCRIPTION
1653 For the Current Assignment, if the operation is not ZAP then updates
1654 all the future rows to the NEW_PRIMARY_FLAG value.
1655 For other assignments,
1656 if the other assignment is the new primary then ensure that there
1657 is a record starting on the correct date with Primary Flag = 'Y'
1658 and update all other future changes to the same Primary value.
1659 For any other assignments
1660 if the assignment is primary on the date in question then
1661 ensure that that there is a row on this date with primary
1662 flag = 'N' and that all future changes are set to 'N'
1663 otherwise
1664 ensure that all future primary flags are set to 'N'.
1665 NB. This uses several calls to DO_PRIMARY_UPDATE which handles the
1666 date effective insert for an individual assignment row if one
1667 is required.
1668 PARAMETERS
1669 p_assignment_id - The current assignment
1670 p_period_of_service_id - The current Period of Service
1671 p_new_primary_ass_id - The Assignment ID that will be primary after
1672 the operation
1673 p_sdate - The date from which changes are to be made
1674 p_new_primary_flag - The current assignment primary flag after the
1675 operation
1676 p_mode - The DT_DELETE_MODE or DT_UPDATE_MODE
1677 */
1678 PROCEDURE update_primary
1679 (
1680 p_assignment_id IN INTEGER
1681 ,p_period_of_service_id IN INTEGER
1682 ,p_new_primary_ass_id IN INTEGER
1683 ,p_sdate IN DATE
1684 ,p_new_primary_flag IN VARCHAR2
1685 ,p_mode IN VARCHAR2
1686 ,p_last_updated_by IN INTEGER
1687 ,p_last_update_login IN INTEGER
1688 ) IS
1689 p_start_date DATE;
1690 --
1691 CURSOR get_future_primary_assignments IS
1692 select assignment_id
1693 from per_assignments_f
1694 where assignment_id not in (P_ASSIGNMENT_ID,P_NEW_PRIMARY_ASS_ID)
1695 and period_of_service_id = P_PERIOD_OF_SERVICE_ID
1696 and effective_end_date >= P_START_DATE;
1697 --
1698 BEGIN
1699 --
1700 p_start_date := p_sdate;
1701 --
1702 -------------------------------------
1703 -- Update the future changes for the current assignment with the
1704 -- new Primary Flag
1705 -- 2468916: this first update ensures the current assignment
1706 -- gets converted to a secondary assignment.
1707 -- 3584122: The value of 'N' is not updating the future
1708 -- changes of the secondar_assignment to primary if a
1709 -- secondary assignment is updated to primary. As the calling
1710 -- procedure would set the new_primary_flag correctly so setting
1711 -- back to p_new_primary_flag.
1712 -------------------------------------
1713 --
1714 IF p_mode <> 'ZAP' THEN
1715 hr_utility.set_location('hr_assignment.update_primary',1);
1716 do_primary_update(p_assignment_id
1717 ,p_sdate
1718 ,p_new_primary_flag -- Bug 3584122 'N'-- Bug 2468916 p_new_primary_flag
1719 ,'Y'
1720 ,p_last_updated_by
1721 ,p_last_update_login
1722 );
1723 END IF;
1724 --
1725 -------------------------------------
1726 -- If the New Primary Asg is not the Current Asg then the Primary
1727 -- Flag has to be set to 'Y' on all the changes on or after the
1728 -- Start Date
1729 -------------------------------------
1730 IF p_assignment_id <> p_new_primary_ass_id THEN
1731 hr_utility.set_location('hr_assignment.update_primary',2);
1732 do_primary_update(p_new_primary_ass_id
1733 ,p_sdate
1734 ,'Y'
1735 ,'N'
1736 ,p_last_updated_by
1737 ,p_last_update_login
1738 );
1739 END IF;
1740 --
1741 -------------------------------------
1742 -- Now for each assignment other than P_ASSIGNMENT_ID and
1743 -- P_NEW_PRIMARY_ASS_ID within the period_of_service
1744 -- future changes must have their Primary Flag set to 'N'. It is only
1745 -- necessary to update the ones that are currently 'Y'.
1746 -------------------------------------
1747 hr_utility.set_location('hr_assignment.update_primary',3);
1748 --
1749 FOR ass_rec IN get_future_primary_assignments LOOP
1750 do_primary_update(ass_rec.assignment_id
1751 ,p_sdate
1752 ,'N'
1753 ,'N'
1754 ,p_last_updated_by
1755 ,p_last_update_login
1756 );
1757 END LOOP;
1758 --
1759 --
1760 END update_primary;
1761 --
1762 --
1763 ------------------- do_primary_update -----------------------------
1764 /*
1765 NAME
1766 do_primary_update
1767 DESCRIPTION
1768 Performs updates on the Assignment to set the Primary Flag to the value
1769 passed in to the procedure.
1770 If a Primary Flag is to be reset on the Date passed in and a row does
1771 not start on this date then a date effective insert is performed.
1772 PARAMETERS
1773 p_assignment_id - The assignment to be updated
1774 p_sdate - The date from which to update
1775 p_primary_flag - The primary flag value
1776 p_current_ass - Whether the assignment is the current one (Y/N)
1777 */
1778 PROCEDURE do_primary_update
1779 (
1780 p_assignment_id IN INTEGER
1781 ,p_sdate IN DATE
1782 ,p_primary_flag IN VARCHAR2
1783 ,p_current_ass IN VARCHAR2
1784 ,p_last_updated_by IN INTEGER
1785 ,p_last_update_login IN INTEGER
1786 ) IS
1787 --
1788 x VARCHAR2(30);
1789 p_start_date DATE;
1790 --
1791 CURSOR select_ass_for_update IS
1792 select *
1793 from per_assignments_f
1794 where assignment_id = P_ASSIGNMENT_ID
1795 and ((p_current_ass <> 'Y'
1796 and (P_START_DATE
1797 between effective_start_date
1798 and effective_end_date
1799 or P_START_DATE < effective_start_date))
1800 or (p_current_ass = 'Y'
1801 and P_START_DATE < effective_start_date))
1802 for update;
1803 --
1804 BEGIN
1805 hr_utility.set_location('hr_assignment.do_primary_update',1);
1806 p_start_date := p_sdate;
1807 --
1808 -------------------------------------
1809 -- If the Assignment is Current i.e. P_CURRENT_ASS = 'Y' then the form
1810 -- has already updated the primary flag. So we only need to update
1811 -- future changes.
1812 --
1813 -- Otherwise attempt to update a row with effective start date on
1814 -- P_START_DATE.
1815 --
1816 -- If one row is updated then there is no need to perform a date
1817 -- effective insert.
1818 --
1819 -- If no rows are updated then perform date effective insert
1820 -- i. If there is a row that spans the P_START_DATE then duplicate the
1821 -- row with effective end date = P_START_DATE - 1
1822 -- ii. Update the row to have effective start date = P_START_DATE and
1823 -- PRIMARY_FLAG = P_PRIMARY_FLAG
1824 --
1825 -- Now Update the Primary Flag to P_PRIMARY_FLAG for all future Changes
1826 -------------------------------------
1827 ---------------------------------------------------------------------
1828 -- Added 10-MAR-93
1829 -- Select for update is only applicable when we are updating 3rd party
1830 -- assignment rows (i.e. not the one that is updated on the form)
1831 -- i.e. p_current_ass <> 'Y' and all the rows that either span the
1832 -- p_start_date or are after it
1833 -- or
1834 -- p_current_ass = 'Y' and all the rows that are after the
1835 -- p_start_date
1836 --
1837 -- IMPORTANT - do not lock the row updated by the form!
1838 --
1839 -- See CURSOR select_ass_for_update
1840 ----------------------------------------------------------------------
1841 --
1842 hr_utility.set_location('hr_assignment.do_primary_update',2);
1843 --
1844 FOR ass_rec IN select_ass_for_update LOOP
1845 NULL;
1846 END LOOP;
1847 --
1848 IF p_current_ass = 'Y' THEN
1849 NULL;
1850 ELSE
1851 hr_utility.set_location('hr_assignment.do_primary_update',3);
1852 --
1853 update per_assignments_f
1854 set primary_flag = P_PRIMARY_FLAG
1855 , last_updated_by = P_LAST_UPDATED_BY
1856 , last_update_login = P_LAST_UPDATE_LOGIN
1857 , last_update_date = sysdate
1858 where assignment_id = P_ASSIGNMENT_ID
1859 and effective_start_date = P_START_DATE;
1860 --
1861 -- hr_utility.set_location('hr_assignment.do_primary_update',4); -- Fix For Bug # 8710298. Commented the Call.
1862 IF SQL%ROWCOUNT = 1 THEN
1863 NULL;
1864 ELSIF SQL%ROWCOUNT > 1 THEN
1865 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
1866 hr_utility.set_message_token('PROCEDURE','DO_PRIMARY_UPDATE');
1867 hr_utility.set_message_token('STEP','1');
1868 hr_utility.raise_error;
1869 ELSE
1870 --
1871 hr_utility.set_location('hr_assignment.do_primary_update',5);
1872 --
1873 insert into per_assignments_f
1874 (
1875 ASSIGNMENT_ID
1876 ,EFFECTIVE_START_DATE
1877 ,EFFECTIVE_END_DATE
1878 ,BUSINESS_GROUP_ID
1879 ,GRADE_ID
1880 ,POSITION_ID
1881 ,JOB_ID
1882 ,ASSIGNMENT_STATUS_TYPE_ID
1883 ,PAYROLL_ID
1884 ,LOCATION_ID
1885 ,PERSON_ID
1886 ,ORGANIZATION_ID
1887 ,PEOPLE_GROUP_ID
1888 ,SOFT_CODING_KEYFLEX_ID
1889 ,VACANCY_ID
1890 ,ASSIGNMENT_SEQUENCE
1891 ,ASSIGNMENT_TYPE
1892 ,MANAGER_FLAG
1893 ,PRIMARY_FLAG
1894 ,APPLICATION_ID
1895 ,ASSIGNMENT_NUMBER
1896 ,CHANGE_REASON
1897 ,COMMENT_ID
1898 ,DATE_PROBATION_END
1899 ,DEFAULT_CODE_COMB_ID
1900 ,FREQUENCY
1901 ,INTERNAL_ADDRESS_LINE
1902 ,NORMAL_HOURS
1903 ,PERIOD_OF_SERVICE_ID
1904 ,PROBATION_PERIOD
1905 ,PROBATION_UNIT
1906 ,RECRUITER_ID
1907 ,SET_OF_BOOKS_ID
1908 ,SPECIAL_CEILING_STEP_ID
1909 ,SUPERVISOR_ID
1910 ,TIME_NORMAL_FINISH
1911 ,TIME_NORMAL_START
1912 ,PERSON_REFERRED_BY_ID
1913 ,RECRUITMENT_ACTIVITY_ID
1914 ,SOURCE_ORGANIZATION_ID
1915 ,SOURCE_TYPE
1916 ,PAY_BASIS_ID
1917 ,EMPLOYMENT_CATEGORY
1918 ,PERF_REVIEW_PERIOD
1919 ,PERF_REVIEW_PERIOD_FREQUENCY
1920 ,SAL_REVIEW_PERIOD
1921 ,SAL_REVIEW_PERIOD_FREQUENCY
1922 ,CONTRACT_ID
1923 ,CAGR_ID_FLEX_NUM
1924 ,CAGR_GRADE_DEF_ID
1925 ,ESTABLISHMENT_ID
1926 ,COLLECTIVE_AGREEMENT_ID
1927 ,NOTICE_PERIOD
1928 ,NOTICE_PERIOD_UOM
1929 ,WORK_AT_HOME
1930 ,EMPLOYEE_CATEGORY
1931 ,JOB_POST_SOURCE_NAME
1932 ,REQUEST_ID
1933 ,PROGRAM_APPLICATION_ID
1934 ,PROGRAM_ID
1935 ,PROGRAM_UPDATE_DATE
1936 ,ASS_ATTRIBUTE_CATEGORY
1937 ,ASS_ATTRIBUTE1
1938 ,ASS_ATTRIBUTE2
1939 ,ASS_ATTRIBUTE3
1940 ,ASS_ATTRIBUTE4
1941 ,ASS_ATTRIBUTE5
1942 ,ASS_ATTRIBUTE6
1943 ,ASS_ATTRIBUTE7
1944 ,ASS_ATTRIBUTE8
1945 ,ASS_ATTRIBUTE9
1946 ,ASS_ATTRIBUTE10
1947 ,ASS_ATTRIBUTE11
1948 ,ASS_ATTRIBUTE12
1949 ,ASS_ATTRIBUTE13
1950 ,ASS_ATTRIBUTE14
1951 ,ASS_ATTRIBUTE15
1952 ,ASS_ATTRIBUTE16
1953 ,ASS_ATTRIBUTE17
1954 ,ASS_ATTRIBUTE18
1955 ,ASS_ATTRIBUTE19
1956 ,ASS_ATTRIBUTE20
1957 ,ASS_ATTRIBUTE21
1958 ,ASS_ATTRIBUTE22
1959 ,ASS_ATTRIBUTE23
1960 ,ASS_ATTRIBUTE24
1961 ,ASS_ATTRIBUTE25
1962 ,ASS_ATTRIBUTE26
1963 ,ASS_ATTRIBUTE27
1964 ,ASS_ATTRIBUTE28
1965 ,ASS_ATTRIBUTE29
1966 ,ASS_ATTRIBUTE30
1967 ,LAST_UPDATE_DATE
1968 ,LAST_UPDATED_BY
1969 ,LAST_UPDATE_LOGIN
1970 ,CREATED_BY
1971 ,CREATION_DATE
1972 ,BARGAINING_UNIT_CODE
1973 ,LABOUR_UNION_MEMBER_FLAG
1974 ,HOURLY_SALARIED_CODE
1975 ,TITLE
1976 ,PERIOD_OF_PLACEMENT_DATE_START
1977 ,VENDOR_ID
1978 ,VENDOR_EMPLOYEE_NUMBER
1979 ,VENDOR_ASSIGNMENT_NUMBER
1980 ,ASSIGNMENT_CATEGORY
1981 ,PROJECT_TITLE
1982 -- Bug 12820636
1983 ,GRADE_LADDER_PGM_ID
1984 ,SUPERVISOR_ASSIGNMENT_ID
1985 )
1986 select
1987 ASSIGNMENT_ID
1988 ,EFFECTIVE_START_DATE
1989 ,p_start_date - 1
1990 ,BUSINESS_GROUP_ID
1991 ,GRADE_ID
1992 ,POSITION_ID
1993 ,JOB_ID
1994 ,ASSIGNMENT_STATUS_TYPE_ID
1995 ,PAYROLL_ID
1996 ,LOCATION_ID
1997 ,PERSON_ID
1998 ,ORGANIZATION_ID
1999 ,PEOPLE_GROUP_ID
2000 ,SOFT_CODING_KEYFLEX_ID
2001 ,VACANCY_ID
2002 ,ASSIGNMENT_SEQUENCE
2003 ,ASSIGNMENT_TYPE
2004 ,MANAGER_FLAG
2005 ,PRIMARY_FLAG
2006 ,APPLICATION_ID
2007 ,ASSIGNMENT_NUMBER
2008 ,CHANGE_REASON
2009 ,COMMENT_ID
2010 ,DATE_PROBATION_END
2011 ,DEFAULT_CODE_COMB_ID
2012 ,FREQUENCY
2013 ,INTERNAL_ADDRESS_LINE
2014 ,NORMAL_HOURS
2015 ,PERIOD_OF_SERVICE_ID
2016 ,PROBATION_PERIOD
2017 ,PROBATION_UNIT
2018 ,RECRUITER_ID
2019 ,SET_OF_BOOKS_ID
2020 ,SPECIAL_CEILING_STEP_ID
2021 ,SUPERVISOR_ID
2022 ,TIME_NORMAL_FINISH
2023 ,TIME_NORMAL_START
2024 ,PERSON_REFERRED_BY_ID
2025 ,RECRUITMENT_ACTIVITY_ID
2026 ,SOURCE_ORGANIZATION_ID
2027 ,SOURCE_TYPE
2028 ,PAY_BASIS_ID
2029 ,EMPLOYMENT_CATEGORY
2030 ,PERF_REVIEW_PERIOD
2031 ,PERF_REVIEW_PERIOD_FREQUENCY
2032 ,SAL_REVIEW_PERIOD
2033 ,SAL_REVIEW_PERIOD_FREQUENCY
2034 ,CONTRACT_ID
2035 ,CAGR_ID_FLEX_NUM
2036 ,CAGR_GRADE_DEF_ID
2037 ,ESTABLISHMENT_ID
2038 ,COLLECTIVE_AGREEMENT_ID
2039 ,NOTICE_PERIOD
2040 ,NOTICE_PERIOD_UOM
2041 ,WORK_AT_HOME
2042 ,EMPLOYEE_CATEGORY
2043 ,JOB_POST_SOURCE_NAME
2044 ,REQUEST_ID
2045 ,PROGRAM_APPLICATION_ID
2046 ,PROGRAM_ID
2047 ,PROGRAM_UPDATE_DATE
2048 ,ASS_ATTRIBUTE_CATEGORY
2049 ,ASS_ATTRIBUTE1
2050 ,ASS_ATTRIBUTE2
2051 ,ASS_ATTRIBUTE3
2052 ,ASS_ATTRIBUTE4
2053 ,ASS_ATTRIBUTE5
2054 ,ASS_ATTRIBUTE6
2055 ,ASS_ATTRIBUTE7
2056 ,ASS_ATTRIBUTE8
2057 ,ASS_ATTRIBUTE9
2058 ,ASS_ATTRIBUTE10
2059 ,ASS_ATTRIBUTE11
2060 ,ASS_ATTRIBUTE12
2061 ,ASS_ATTRIBUTE13
2062 ,ASS_ATTRIBUTE14
2063 ,ASS_ATTRIBUTE15
2064 ,ASS_ATTRIBUTE16
2065 ,ASS_ATTRIBUTE17
2066 ,ASS_ATTRIBUTE18
2067 ,ASS_ATTRIBUTE19
2068 ,ASS_ATTRIBUTE20
2069 ,ASS_ATTRIBUTE21
2070 ,ASS_ATTRIBUTE22
2071 ,ASS_ATTRIBUTE23
2072 ,ASS_ATTRIBUTE24
2073 ,ASS_ATTRIBUTE25
2074 ,ASS_ATTRIBUTE26
2075 ,ASS_ATTRIBUTE27
2076 ,ASS_ATTRIBUTE28
2077 ,ASS_ATTRIBUTE29
2078 ,ASS_ATTRIBUTE30
2079 ,LAST_UPDATE_DATE
2080 ,LAST_UPDATED_BY
2081 ,LAST_UPDATE_LOGIN
2082 ,CREATED_BY
2083 ,CREATION_DATE
2084 ,BARGAINING_UNIT_CODE
2085 ,LABOUR_UNION_MEMBER_FLAG
2086 ,HOURLY_SALARIED_CODE
2087 ,TITLE
2088 ,PERIOD_OF_PLACEMENT_DATE_START
2089 ,VENDOR_ID
2090 ,VENDOR_EMPLOYEE_NUMBER
2091 ,VENDOR_ASSIGNMENT_NUMBER
2092 ,ASSIGNMENT_CATEGORY
2093 ,PROJECT_TITLE
2094 -- Bug 12820636
2095 ,GRADE_LADDER_PGM_ID
2096 ,SUPERVISOR_ASSIGNMENT_ID
2097 from per_assignments_f
2098 where assignment_id = P_ASSIGNMENT_ID
2099 and P_START_DATE
2100 between effective_start_date and effective_end_date
2101 and primary_flag <> P_PRIMARY_FLAG ;
2102 --
2103 IF SQL%ROWCOUNT = 0 THEN
2104 NULL; -- This Assignment Start in the Future
2105 ELSE
2106 hr_utility.set_location('hr_assignment.do_primary_update',6);
2107 --
2108 update per_assignments_f
2109 set effective_start_date = P_START_DATE
2110 , primary_flag = P_PRIMARY_FLAG
2111 , last_updated_by = P_LAST_UPDATED_BY
2112 , last_update_login = P_LAST_UPDATE_LOGIN
2113 , last_update_date = sysdate
2114 where assignment_id = P_ASSIGNMENT_ID
2115 and P_START_DATE
2116 between effective_start_date and effective_end_date
2117 and primary_flag <> P_PRIMARY_FLAG;
2118 --
2119 -- hr_utility.set_location('hr_assignment.do_primary_update',7); -- Fix For Bug # 8710298. Commented the call.
2120 IF SQL%ROWCOUNT <> 1 THEN
2121 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
2122 hr_utility.set_message_token('PROCEDURE','DO_PRIMARY_UPDATE');
2123 hr_utility.set_message_token('STEP','3');
2124 hr_utility.raise_error;
2125 END IF; -- (SQL%ROWCOUNT <> 1)
2126 hr_utility.set_location('hr_assignment.do_primary_update',7); -- Fix For Bug # 8710298 . Moved the hr_utility.set_location call.
2127 END IF; -- (SQL%ROWCOUNT = 0)
2128 END IF; -- (SQL%ROWCOUNT = 1)
2129 END IF; -- (p_current_ass = 'Y')
2130 --
2131 hr_utility.set_location('hr_assignment.do_primary_update',8);
2132 --
2133 update per_assignments_f
2134 set primary_flag = P_PRIMARY_FLAG
2135 , last_updated_by = P_LAST_UPDATED_BY
2136 , last_update_login = P_LAST_UPDATE_LOGIN
2137 , last_update_date = sysdate
2138 where assignment_id = P_ASSIGNMENT_ID
2139 and effective_start_date > P_START_DATE;
2140 --
2141 --
2142 --
2143 END do_primary_update;
2144 --
2145 --
2146 ------------------- get_new_primary_assignment --------------------
2147 /*
2148 NAME
2149 get_new_primary_assignment
2150 DESCRIPTION
2151 Searches for a candidate assignment which will become Primary
2152 on the Date passed into the procedure. The assignment must be continuous
2153 to the end of the period of service and if it is terminated the
2154 first termination must be as aresult of termination of the employee.
2155 If more than one candidate assignment is found then a warning status is
2156 raised (the form detect the warning and pops a QuickPick).
2157 PARAMETERS
2158 p_assignment_id - The current assignment
2159 p_period_of_service_id - The current period of service
2160 p_sdate - The date upon which the assignment will
2161 become primary
2162 p_new_primary_ass_id - The new Primary Assignment ID
2163 */
2164 PROCEDURE get_new_primary_assignment
2165 (
2166 p_assignment_id IN NUMBER
2167 ,p_period_of_service_id IN NUMBER
2168 ,p_sdate IN DATE
2169 ,p_new_primary_ass_id OUT NOCOPY VARCHAR2
2170 ) IS
2171 p_atd DATE;
2172 p_fpd DATE;
2173 p_start_date DATE;
2174 l_person_id NUMBER;
2175 l_assignment_type per_all_assignments_f.assignment_type%TYPE;
2176 l_pdp_date_start DATE;
2177
2178 --
2179 -- Fetch the person ID and assignment type so the
2180 -- period of placement can be obtained for
2181 -- contingent workers.
2182 --
2183 CURSOR csr_get_assignment_info IS
2184 SELECT paaf.person_id
2185 ,paaf.assignment_type
2186 ,paaf.period_of_placement_date_start
2187 FROM per_all_assignments_f paaf
2188 WHERE paaf.assignment_id = p_assignment_id
2189 AND paaf.assignment_type IN ('E', 'C');
2190
2191 --
2192 -- Get the termination dates for the period of placement and
2193 -- period of service.
2194 --
2195 CURSOR csr_get_term_dates IS
2196 SELECT NVL(actual_termination_date, hr_api.g_eot)
2197 ,NVL(final_process_date, hr_api.g_eot)
2198 FROM per_periods_of_service
2199 WHERE period_of_service_id = p_period_of_service_id
2200 UNION
2201 SELECT NVL(pdp.actual_termination_date, hr_api.g_eot)
2202 ,NVL(pdp.final_process_date, hr_api.g_eot)
2203 FROM per_periods_of_placement pdp
2204 WHERE pdp.person_id = l_person_id
2205 AND pdp.date_start = l_pdp_date_start;
2206
2207 --
2208 local_warning EXCEPTION;
2209 --
2210 -------------------------------------
2211 -- Find an assignment for this period of service / placement that is
2212 -- continuous from the start date passed in as a parameter
2213 -- to the end of time or the final processing date or the period of
2214 -- service (whicever is the sooner)
2215 -- and
2216 -- which is not terminated between now and the atd (or eot if atd is null)
2217 -- i.e. as a result of termination.
2218 -- This cursor has been changed to support contingent workers but it
2219 -- should be noted that the 'TERM_ASSIGN' sub-select has not changed
2220 -- as this is wrapped in an AND NOT EXISTS and an equivalent "Terminated"
2221 -- assignment is not possible for contingent workers.
2222 -------------------------------------
2223 CURSOR get_candidate_primary_ass IS
2224 select to_char(a.assignment_id)
2225 from per_assignments_f a,
2226 per_assignment_status_types ast
2227 where assignment_id <> p_assignment_id
2228 and a.effective_start_date <= p_start_date
2229 and a.effective_end_date >= p_start_date
2230 and a.assignment_status_type_id = ast.assignment_status_type_id
2231 and ((a.period_of_service_id = p_period_of_service_id and
2232 a.assignment_type = 'E' and
2233 ast.per_system_status = 'ACTIVE_ASSIGN')
2234 or (a.period_of_placement_date_start = l_pdp_date_start and
2235 a.person_id = l_person_id and
2236 a.assignment_type = 'C' and
2237 ast.per_system_status = 'ACTIVE_CWK'))
2238 and exists
2239 (select null
2240 from per_assignments_f a2
2241 where a2.assignment_id = a.assignment_id
2242 and a2.effective_end_date >= p_fpd)
2243 and not exists
2244 (select null
2245 from per_assignments_f a3
2246 where a3.assignment_id = a.assignment_id
2247 and a3.effective_start_date between p_start_date and p_atd
2248 and exists
2249 (select null
2250 from per_assignment_status_types s
2251 where s.assignment_status_type_id = a3.assignment_status_type_id
2252 and s.per_system_status = 'TERM_ASSIGN'));
2253 --
2254 BEGIN
2255 --
2256 p_start_date := p_sdate;
2257
2258 hr_utility.set_location('hr_assignment.get_new_primary_assignment',1);
2259
2260 --
2261 -- Fetch the desired assignment details.
2262 --
2263 OPEN csr_get_assignment_info;
2264 FETCH csr_get_assignment_info INTO l_person_id
2265 ,l_assignment_type
2266 ,l_pdp_date_start;
2267 CLOSE csr_get_assignment_info;
2268
2269 --
2270 -- Fetch the termination dates.
2271 --
2272 OPEN csr_get_term_dates;
2273 FETCH csr_get_term_dates INTO p_atd
2274 ,p_fpd;
2275 CLOSE csr_get_term_dates;
2276
2277 --
2278 hr_utility.set_location('hr_assignment.get_new_primary_assignment',2);
2279 --
2280 ---------------------------------------------------
2281 -- open the cursor and read the first record if one exists
2282 -- If one doesn't exists then ERROR
2283 -- Try and read another record
2284 -- If one exists then WARNING (prompt user in Form for which one)
2285 ---------------------------------------------------
2286 hr_utility.set_location('hr_assignment.get_new_primary_assignment',3);
2287 OPEN get_candidate_primary_ass;
2288 --
2289 hr_utility.set_location('hr_assignment.get_new_primary_assignment',4);
2290 FETCH get_candidate_primary_ass INTO p_new_primary_ass_id;
2291 --
2292 IF get_candidate_primary_ass%NOTFOUND THEN
2293 hr_utility.set_location('hr_assignment.get_new_primary_assignment',5);
2294 CLOSE get_candidate_primary_ass;
2295 hr_utility.set_message(801,'HR_6384_EMP_ASS_NO_PRIM');
2296 hr_utility.raise_error;
2297 ELSE
2298 hr_utility.set_location('hr_assignment.get_new_primary_assignment',7);
2299 FETCH get_candidate_primary_ass INTO p_new_primary_ass_id;
2300 --
2301 hr_utility.set_location('hr_assignment.get_new_primary_assignment',8);
2302 IF get_candidate_primary_ass%FOUND THEN
2303 raise local_warning;
2304 END IF;
2305 --
2306 hr_utility.set_location('hr_assignment.get_new_primary_assignment',9);
2307 CLOSE get_candidate_primary_ass;
2308 END IF;
2309 --
2310 EXCEPTION
2311 when local_warning then
2312 p_new_primary_ass_id := null ;
2313 hr_utility.set_warning;
2314
2315 when others then
2316 p_new_primary_ass_id := null;
2317 raise;
2318 --
2319 END get_new_primary_assignment;
2320 --
2321 --
2322 ------------------- load_budget_values --------------------
2323 /*
2324 NAME
2325 load_budget_values
2326 DESCRIPTION
2327 Creates Assignment Budget Values form the Default ones for the Business
2328 Group.
2329 PARAMETERS
2330 p_assignment_id - The current assignment
2331 p_business_group_id - The business Group
2332 p_userid
2333 p_login
2334 p_effective_start_date - assignment start date
2335 p_effective_end_date - assignment end date
2336 */
2337 PROCEDURE load_budget_values
2338 (p_assignment_id IN INTEGER
2339 ,p_business_group_id IN INTEGER
2340 ,p_userid IN VARCHAR2
2341 ,p_login IN VARCHAR2
2342 ,p_effective_start_date IN DATE
2343 ,p_effective_end_date IN DATE) IS
2344
2345 --
2346 BEGIN
2347 hr_utility.set_location('hr_assignment.load_budget_values',1);
2348 --
2349 /* 25/05/95 Fixed bug 273820 - performance of following statement
2350 NB the business_group_id no longer needs +0 appended to it, this
2351 is because the view per_default_budget_values is in fact returning
2352 organization_id in place of business_group_id in ordre to make use
2353 of an index
2354 */
2355 -- Change to include effective start and end dates in parameters and logic to check these and
2356 -- insert the assignment dates into the budget values table.
2357 -- Also change of table from per_assignment_budget_values to per_assignment_budget_values_f.
2358 -- This is required as per_assignment_budget_values_f is now a datetracked table.
2359 -- 16-APR-1998 : SASmith.
2360
2361
2362 insert into per_assignment_budget_values_f
2363 (assignment_budget_value_id
2364 ,business_group_id
2365 ,assignment_id
2366 ,last_update_date
2367 ,last_updated_by
2368 ,last_update_login
2369 ,created_by
2370 ,creation_date
2371 ,unit
2372 ,value
2373 ,effective_start_date
2374 ,effective_end_date)
2375 select per_assignment_budget_values_s.nextval
2376 , pabv1.business_group_id
2377 , p_assignment_id
2378 , sysdate
2379 , p_userid
2380 , p_login
2381 , p_userid
2382 , sysdate
2383 , pabv1.unit
2384 , pabv1.value
2385 , p_effective_start_date
2386 , p_effective_end_date
2387 from per_default_budget_values pabv1
2388 where pabv1.business_group_id = p_business_group_id
2389 and not exists (select 'already there'
2390 from per_assignment_budget_values_f pabv2
2391 where pabv2.assignment_id = p_assignment_id
2392 and pabv2.unit = pabv1.unit );
2393 /* BDG 22/12/98 for Bug 679966
2394 and pabv2.value = pabv1.value */
2395 --
2396 --
2397 END load_budget_values;
2398 --
2399 --
2400 ------------------- del_ref_int_check --------------------
2401 /*
2402 NAME
2403 del_ref_int_check
2404 DESCRIPTION
2405 Performs Referential Integrity Checks on the following tables
2406 For 'ZAP'
2407 PER_EVENTS
2408 PER_LETTER_REQUEST_LINES
2409 PAY_COST_ALLOCATIONS_F
2410 PER_ASSIGNMENT_EXTRA_INFO
2411 PAY_PERSONAL_PAYMENT_METHODS_F
2412 HR_ASSIGNMENT_SET_AMENDMENTS
2413 PAY_ASSIGNMENT_ACTIONS
2414 PER_COBRA_COV_ENROLLMENTS
2415 PER_COBRA_COVERAGE_BENEFITS_F
2416 OTA_DELEGATE_BOOKINGS (per_ota_predel_validation.ota_predel_asg_validation)
2417
2418 For 'END' (date effective delete)
2419 PER_EVENTS
2420 PER_LETTER_REQUEST_LINES
2421 PAY_COST_ALLOCATIONS_F
2422 PAY_PERSONAL_PAYMENT_METHODS_F
2423 PAY_ASSIGNMENT_ACTIONS
2424 PER_COBRA_COV_ENROLLMENTS
2425 PER_COBRA_COVERAGE_BENEFITS_F
2426
2427 Determines whether the delete operation is permissible
2428 PARAMETERS
2429 p_assignment_id - The current assignment
2430 p_mode - The mode of operation (ZAP or END)
2431 p_edate - The date the assignment is ENDed
2432 only required for 'END'
2433 */
2434 PROCEDURE del_ref_int_check
2435 (
2436 p_assignment_id IN INTEGER
2437 ,p_mode IN VARCHAR2
2438 ,p_edate IN DATE
2439 ) IS
2440 p_end_date DATE;
2441 p_del_flag VARCHAR2(1);
2442 --
2443 BEGIN
2444 --
2445 --
2446 p_end_date := p_edate;
2447 --
2448 --
2449 IF p_mode = 'ZAP' THEN
2450 hr_utility.set_location('hr_assignment.del_ref_int_check',0);
2451 p_del_flag := 'N';
2452 --
2453 BEGIN
2454 select 'Y'
2455 into p_del_flag
2456 from sys.dual
2457 where exists (
2458 select null
2459 from PER_ASSIGNMENTS_F A
2460 , FND_SESSIONS S
2461 where a.assignment_id = p_assignment_id
2462 and a.assignment_type = 'E'
2463 and effective_date
2464 between a.effective_start_date and a.effective_end_date
2465 and session_id = userenv('SESSIONID')
2466 and exists
2467 (select null
2468 from PER_ASSIGNMENTS_F B
2469 where b.assignment_id = p_assignment_id
2470 and b.assignment_type = 'A'
2471 and b.effective_end_date < a.effective_start_date)
2472 );
2473 EXCEPTION
2474 WHEN NO_DATA_FOUND THEN NULL;
2475 END;
2476 --
2477 IF p_del_flag = 'Y' THEN
2478 hr_utility.set_message(801,'HR_7625_ASS_DEL_APP_ASS');
2479 hr_utility.raise_error;
2480 END IF;
2481 END IF;
2482 --
2483 hr_utility.set_location('hr_assignment.del_ref_int_check',1);
2484 p_del_flag := 'N';
2485 --
2486 BEGIN
2487 select 'Y'
2488 into p_del_flag
2489 from sys.dual
2490 where exists (
2491 select null
2492 from PER_EVENTS
2493 where assignment_id = p_assignment_id
2494 and (p_mode = 'ZAP'
2495 or (p_mode = 'END'
2496 and date_start > p_end_date)));
2497 EXCEPTION
2498 WHEN NO_DATA_FOUND THEN NULL;
2499 END;
2500 --
2501 IF p_del_flag = 'Y' THEN
2502 IF p_mode = 'ZAP' THEN
2503 hr_utility.set_message(801,'HR_7630_ASS_EVE_DEL_ASS');
2504 ELSE
2505 hr_utility.set_message(801,'HR_7633_ASS_EVE_END_ASS');
2506 END IF;
2507 hr_utility.raise_error;
2508 END IF;
2509 --
2510 --
2511 /* Took out nocopy the check on letter requests as they are now Auto Deleted
2512 - 2/6/93
2513 */
2514 /*
2515 hr_utility.set_location('hr_assignment.del_ref_int_check',2);
2516 p_del_flag := 'N';
2517 --
2518 BEGIN
2519 select 'Y'
2520 into p_del_flag
2521 from sys.dual
2522 where exists (
2523 select null
2524 from PER_LETTER_REQUEST_LINES
2525 where assignment_id = p_assignment_id
2526 and (p_mode = 'ZAP'
2527 or (p_mode = 'END'
2528 and date_from > p_end_date)));
2529 EXCEPTION
2530 WHEN NO_DATA_FOUND THEN NULL;
2531 END;
2532 --
2533 IF p_del_flag = 'Y' THEN
2534 IF p_mode = 'ZAP' THEN
2535 hr_utility.set_message(801,'HR_7634_ASS_LET_DEL_ASS');
2536 ELSE
2537 hr_utility.set_message(801,'HR_7637_ASS_EVE_END_ASS');
2538 END IF;
2539 hr_utility.raise_error;
2540 END IF;
2541 */
2542 --
2543 --
2544 hr_utility.set_location('hr_assignment.del_ref_int_check',3);
2545 p_del_flag := 'N';
2546 --
2547 BEGIN
2548 select 'Y'
2549 into p_del_flag
2550 from sys.dual
2551 where exists (
2552 select null
2553 from PAY_COST_ALLOCATIONS_F
2554 where assignment_id = p_assignment_id
2555 and (p_mode = 'ZAP'
2556 or (p_mode = 'END'
2557 and effective_start_date > p_end_date)));
2558 EXCEPTION
2559 WHEN NO_DATA_FOUND THEN NULL;
2560 END;
2561 --
2562 IF p_del_flag = 'Y' THEN
2563 IF p_mode = 'ZAP' THEN
2564 hr_utility.set_message(801,'HR_7638_ASS_COST_DEL_ASS');
2565 ELSE
2566 hr_utility.set_message(801,'HR_7641_ASS_COST_END_ASS');
2567 END IF;
2568 hr_utility.raise_error;
2569 END IF;
2570 --
2571 --
2572 -- 03/18/1998 Bug #642566
2573 -- Removed code to check for existence of per_assignment_extra_info on a delete
2574 -- as per_assignment_extra_info are now deleted along with other
2575 -- assignment related records.
2576 --
2577 --
2578 hr_utility.set_location('hr_assignment.del_ref_int_check',5);
2579 p_del_flag := 'N';
2580 /*
2581 N.B. PER_SECONDARY_ASS_STATUSES rows will now be deleted if they
2582 started after the Assignment End Date - changed 27/5/93.
2583 --
2584 BEGIN
2585 select 'Y'
2586 into p_del_flag
2587 from sys.dual
2588 where exists (
2589 select null
2590 from PER_SECONDARY_ASS_STATUSES
2591 where assignment_id = p_assignment_id
2592 and p_mode = 'ZAP');
2593 EXCEPTION
2594 WHEN NO_DATA_FOUND THEN NULL;
2595 END;
2596 --
2597 IF p_del_flag = 'Y' THEN
2598 IF p_mode = 'ZAP' THEN
2599 hr_utility.set_message(801,'HR_7652_ASS_STAT_DEL_ASS');
2600 ELSE
2601 hr_utility.set_message(801,'HR_7655_ASS_SATA_END_ASS');
2602 END IF;
2603 hr_utility.raise_error;
2604 END IF;
2605 */
2606 --
2607 --
2608 hr_utility.set_location('hr_assignment.del_ref_int_check',6);
2609 p_del_flag := 'N';
2610
2611 /* 2537091: PPMs will be deleted if they started after end date of assignment
2612 changed 04-OCT-2002
2613 --
2614 BEGIN
2615 select 'Y'
2616 into p_del_flag
2617 from sys.dual
2618 where exists (
2619 select null
2620 from PAY_PERSONAL_PAYMENT_METHODS_F
2621 where assignment_id = p_assignment_id
2622 and (p_mode = 'ZAP'
2623 or (p_mode = 'END'
2624 and effective_start_date > p_end_date)));
2625 EXCEPTION
2626 WHEN NO_DATA_FOUND THEN NULL;
2627 END;
2628 --
2629 IF p_del_flag = 'Y' THEN
2630 IF p_mode = 'ZAP' THEN
2631 hr_utility.set_message(801,'HR_7656_ASS_PAY_DEL_ASS');
2632 ELSE
2633 hr_utility.set_message(801,'HR_7659_ASS_PAY_END_ASS');
2634 END IF;
2635 hr_utility.raise_error;
2636 END IF;
2637
2638 */
2639 --
2640 --
2641 hr_utility.set_location('hr_assignment.del_ref_int_check',7);
2642 p_del_flag := 'N';
2643 --
2644 /* BEGIN
2645 select 'Y'
2646 into p_del_flag
2647 from sys.dual
2648 where exists
2649 (select null
2650 from pay_payroll_actions ps
2651 , pay_assignment_actions aa
2652 where aa.assignment_id = P_ASSIGNMENT_ID
2653 and ps.payroll_action_id = aa.payroll_action_id
2654 and ps.action_type not in ('X','BEE') --Added for bug2956160
2655 and (P_MODE = 'ZAP'
2656 or (P_MODE = 'END' and
2657 ps.effective_date > P_END_DATE)));
2658 EXCEPTION
2659 WHEN NO_DATA_FOUND THEN NULL;
2660 END;*/
2661
2662 IF P_MODE = 'ZAP' then -- Added for Bug 4946199
2663 BEGIN
2664 select 'Y'
2665 into p_del_flag
2666 from dual
2667 where exists
2668 (select null
2669 from pay_payroll_actions ps
2670 , pay_assignment_actions aa
2671 where aa.assignment_id = P_ASSIGNMENT_ID
2672 and ps.action_type not in ('X','BEE') --Added for bug2956160
2673 and ps.payroll_action_id = aa.payroll_action_id);
2674 EXCEPTION
2675 WHEN NO_DATA_FOUND THEN NULL;
2676 END;
2677
2678 ELSIF p_MODE = 'END' then
2679 BEGIN
2680 select 'Y'
2681 into p_del_flag
2682 from sys.dual
2683 where exists
2684 (select null
2685 from pay_payroll_actions ps
2686 , pay_assignment_actions aa
2687 where aa.assignment_id = P_ASSIGNMENT_ID
2688 and ps.payroll_action_id = aa.payroll_action_id
2689 and ps.action_type not in ('X','BEE') --Added for bug2956160
2690 and ps.effective_date > P_END_DATE);
2691 EXCEPTION
2692 WHEN NO_DATA_FOUND THEN NULL;
2693 END;
2694 END IF;
2695 --
2696 IF p_del_flag = 'Y' THEN
2697 IF p_mode = 'ZAP' THEN
2698 hr_utility.set_message(801,'HR_7664_ASS_ASS_DEL_ASS');
2699 ELSE
2700 hr_utility.set_message(801,'HR_7667_ASS_ASS_END_ASS');
2701 END IF;
2702 hr_utility.raise_error;
2703 END IF;
2704 --
2705 --
2706 IF p_mode = 'ZAP' THEN
2707 --
2708 hr_utility.set_location('hr_assignment.del_ref_int_check',8);
2709 p_del_flag := 'N';
2710 --
2711 BEGIN
2712 select 'Y'
2713 into p_del_flag
2714 from sys.dual
2715 where exists (
2716 select null
2717 from hr_assignment_set_amendments asa
2718 where asa.assignment_id = p_assignment_id
2719 and asa.include_or_exclude = 'I'
2720 and not exists (
2721 select null
2722 from hr_assignment_set_amendments asa2
2723 where asa2.assignment_set_id = asa.assignment_set_id
2724 and asa2.assignment_id <> asa.assignment_id)
2725 );
2726 EXCEPTION
2727 WHEN NO_DATA_FOUND THEN NULL;
2728 END;
2729 --
2730 IF p_del_flag = 'Y' THEN
2731 hr_utility.set_message(801,'HR_6305_ALL_ASSGT_SET_NO_DEL');
2732 hr_utility.raise_error;
2733 END IF;
2734 END IF;
2735 ------------------------------
2736 -- Cobra Coverage Enrollments
2737 --
2738 hr_utility.set_location('hr_assignment.del_ref_int_check',9);
2739 p_del_flag := 'N';
2740 --
2741 BEGIN
2742 select 'Y'
2743 into p_del_flag
2744 from sys.dual
2745 where exists (
2746 select null
2747 from PER_COBRA_COV_ENROLLMENTS
2748 where assignment_id = p_assignment_id
2749 and (p_mode = 'ZAP'
2750 or (p_mode = 'END'
2751 and coverage_start_date > p_end_date)));
2752 EXCEPTION
2753 WHEN NO_DATA_FOUND THEN NULL;
2754 END;
2755 --
2756 IF p_del_flag = 'Y' THEN
2757 IF p_mode = 'ZAP' THEN
2758 hr_utility.set_message(801,'HR_7672_ASS_COBRA_DEL_ASS');
2759 ELSE
2760 hr_utility.set_message(801,'HR_7675_ASS_COBRA_END_ASS');
2761 END IF;
2762 hr_utility.raise_error;
2763 END IF;
2764 ------------------------------
2765 -- Cobra Coverage Benefits
2766 --
2767 hr_utility.set_location('hr_assignment.del_ref_int_check',9);
2768 p_del_flag := 'N';
2769 --
2770 BEGIN
2771 select 'Y'
2772 into p_del_flag
2773 from dual
2774 where exists
2775 (select null
2776 from per_cobra_cov_enrollments e
2777 , per_cobra_coverage_benefits_f b
2778 where e.assignment_id = P_ASSIGNMENT_ID
2779 and e.cobra_coverage_enrollment_id
2780 = b.cobra_coverage_enrollment_id
2781 and (p_mode = 'ZAP'
2782 or (p_mode = 'END'
2783 and b.effective_end_date > P_END_DATE)));
2784 --
2785 EXCEPTION
2786 WHEN NO_DATA_FOUND THEN NULL;
2787 END;
2788 --
2789 IF p_del_flag = 'Y' THEN
2790 IF p_mode = 'ZAP' THEN
2791 hr_utility.set_message(801,'HR_7668_ASS_COBR_DEL_ASS');
2792 ELSE
2793 hr_utility.set_message(801,'HR_7671_ASS_COBRA_END_ASS');
2794 END IF;
2795 hr_utility.raise_error;
2796 END IF;
2797 --
2798 ------------------------------
2799 -- OTA_DELEGATE_BOOKINGS
2800 --
2801 hr_utility.set_location('hr_assignment.del_ref_int_check',10);
2802 --
2803 p_del_flag := 'N';
2804 --
2805 BEGIN
2806
2807 /* In the select below, added the join to
2808 per_assignments_f to check for the primary assignment. If
2809 it is not a primary assignment then let it get purged.
2810 This has beeen changed for Bug# 787633 */
2811
2812 select 'Y'
2813 into p_del_flag
2814 from sys.dual
2815 where exists (
2816 select null
2817 from PAY_US_EMP_FED_TAX_RULES_F pef,
2818 per_assignments_f paf
2819 where pef.assignment_id = p_assignment_id
2820 and paf.assignment_id = pef.assignment_id
2821 and paf.primary_flag = 'Y'
2822 and (p_mode = 'ZAP'
2823 or (p_mode = 'END'
2824 and pef.effective_start_date > p_end_date)));
2825
2826
2827
2828 EXCEPTION
2829 WHEN NO_DATA_FOUND THEN NULL;
2830 END;
2831 --
2832 IF p_del_flag = 'Y' THEN
2833 IF p_mode = 'ZAP' THEN
2834 hr_utility.set_message(801,'HR_52281_ASS_TAX_DEL_ASS');
2835 ELSE
2836 hr_utility.set_message(801,'HR_52280_ASS_TAX_END_ASS');
2837 END IF;
2838 hr_utility.raise_error;
2839 END IF;
2840
2841 ------------------------------
2842 IF p_mode = 'ZAP' THEN
2843 --
2844 -- OTA_DELEGATE_BOOKINGS
2845
2846 hr_utility.set_location('hr_assignment.del_ref_int_check',11);
2847 per_ota_predel_validation.ota_predel_asg_validation(P_ASSIGNMENT_ID);
2848 END IF;
2849 ---
2850 END del_ref_int_check;
2851 --
2852 ------------------- del_ref_int_delete --------------------
2853 /*
2854 NAME
2855 del_ref_int_delete
2856 DESCRIPTION
2857 Performs Third Party Delete on data that is not checked in
2858 del_ref_in_check. Removes data from the following tables
2859
2860 For 'ZAP'
2861 HR_ASSIGNMENT_SET_AMENDMENTS
2862 PER_ASSIGNMENT_BUDGET_VALUES_F
2863 PER_SPINAL_POINT_PLACEMENTS_F
2864 PER_PAY_PROPOSALS
2865
2866 For 'END' (performs a date effective delete)
2867 PER_SPINAL_POINT_PLACEMENTS_F
2868 PER_ASSIGNMENT_BUDGET_VALUES_F
2869
2870 For 'FUTURE' (including FUTURE_CHANGES, DELETE_NEXT_CHANGE,
2871 UPDATE_OVERRIDE)
2872 PER_SPINAL_POINT_PLACEMENTS_F
2873 PER_ASSIGNMENT_BUDGET_VALUES_F
2874
2875 PARAMETERS
2876 p_assignment_id - The current assignment
2877 p_grade_id - The current grade ('FUTURE' only')
2878 p_mode - The mode of operation (ZAP, END or FUTURE)
2879 p_edate - For END the date the assignment is ENDed
2880 For FUTURE the date the change applies from
2881 For ZAP not required
2882 p_last_updated_by
2883 p_last_update_login
2884 */
2885 -- Change to include table per_assignment_budget_values_f in END and FUTURE logic. Now
2886 -- required as this table is datetracked.
2887 -- 16-APR-1998 : SASmith
2888
2889 PROCEDURE del_ref_int_delete
2890 (
2891 p_assignment_id IN INTEGER
2892 ,p_grade_id IN INTEGER
2893 ,p_mode IN VARCHAR2
2894 ,p_edate IN DATE
2895 ,p_last_updated_by IN INTEGER
2896 ,p_last_update_login IN INTEGER
2897 ,p_calling_proc IN VARCHAR2
2898 ,p_val_st_date IN DATE
2899 ,p_val_end_date IN DATE
2900 ,p_datetrack_mode IN VARCHAR2
2901 ,p_future_spp_warning OUT NOCOPY BOOLEAN
2902 ) IS
2903 p_del_flag VARCHAR2(1);
2904 p_end_date DATE;
2905 --
2906 -- Parameters added for calls to spp api
2907 --
2908 l_placement_id number;
2909 l_object_version_number number;
2910 l_effective_start_date date;
2911 l_effective_end_date date;
2912 l_datetrack_mode varchar2(30);
2913 l_update number;
2914 l_date_temp date;
2915 l_old_parent_spine_id number;
2916 l_parent_spine_id number;
2917 l_temp number;
2918 l_min_step_id number;
2919 l_sequence number;
2920 l_min_start_date date;
2921 l_new_date date;
2922 l_future_spp_warning boolean;
2923 l_ass_end_date date; -- bug 7112709
2924
2925 --
2926 -- Check to see if a grade step has been created for assignment
2927 --
2928 cursor csr_grade_step is
2929 select spp.placement_id
2930 from per_spinal_point_placements_f spp
2931 where spp.assignment_id = p_assignment_id
2932 and p_val_st_date between spp.effective_start_date
2933 and spp.effective_end_date;
2934 --
2935 -- Checks to see if future rows exist - Datetrack mode
2936 --
2937 cursor csr_future_records is
2938 select spp1.placement_id
2939 from per_spinal_point_placements_f spp1
2940 where spp1.assignment_id = p_assignment_id
2941 and spp1.effective_start_date > p_val_st_date;
2942 --
2943 -- Check to see if future records are for current parent spine
2944 -- If so flag a warning!
2945 --
2946 cursor csr_record_check is
2947 select spp2.placement_id
2948 from per_spinal_point_placements_f spp2
2949 where spp2.assignment_id = p_assignment_id
2950 and spp2.effective_start_date > p_val_st_date
2951 and spp2.parent_spine_id = l_old_parent_spine_id
2952 and spp2.effective_end_date <= p_val_end_date;
2953 --
2954 -- Start of fix 3280773
2955 -- Cursor to retrive the spinal point records
2956 --
2957 cursor csr_spp_rec(p_new_date date) is
2958 select spp.effective_end_date,
2959 spp.placement_id,
2960 spp.object_version_number
2961 from per_spinal_point_placements_f spp
2962 where spp.assignment_id = p_assignment_id
2963 and p_new_date between spp.effective_start_date
2964 and spp.effective_end_date;
2965 --
2966 -- Cursor to retrive the SPP effective_end_date
2967 --
2968 cursor csr_spp_end_date(p_placement_id number, l_edate date) is
2969 select spp.effective_end_date
2970 from per_spinal_point_placements_f spp
2971 where spp.placement_id = p_placement_id
2972 and l_edate between spp.effective_start_date
2973 and spp.effective_end_date;
2974 -- End of 3280773
2975 --
2976 BEGIN
2977 --
2978 --
2979 p_end_date := p_edate;
2980 --
2981 hr_utility.set_location('hr_assignment.del_ref_int_delete',1);
2982 p_del_flag := 'N';
2983 --
2984 BEGIN
2985 --
2986 SELECT 'Y'
2987 into p_del_flag
2988 FROM SYS.DUAL
2989 WHERE EXISTS
2990 (SELECT NULL
2991 FROM PER_SPINAL_POINT_PLACEMENTS_F P
2992 WHERE P.ASSIGNMENT_ID = p_assignment_id
2993 AND (p_mode = 'ZAP'
2994 OR (p_mode = 'END'
2995 AND EFFECTIVE_END_DATE > p_end_date)
2996 OR (p_mode = 'FUTURE'
2997 AND P.EFFECTIVE_START_DATE >= p_end_date
2998 AND NOT EXISTS
2999 (SELECT NULL
3000 FROM PER_SPINAL_POINT_STEPS_F S
3001 , PER_GRADE_SPINES_F GS
3002 WHERE GS.GRADE_SPINE_ID = S.GRADE_SPINE_ID
3003 AND S.STEP_ID = P.STEP_ID
3004 AND GS.GRADE_ID = NVL(p_grade_id,-1)))));
3005 --
3006 EXCEPTION
3007 WHEN NO_DATA_FOUND THEN NULL;
3008 END;
3009 hr_utility.set_location('p_del_flag :'||p_del_flag,2);
3010 hr_utility.set_location('p_mode :'||p_mode,2);
3011 hr_utility.set_location('p_edate :'||p_edate,2);
3012 hr_utility.set_location('p_val_st_date: '||p_val_st_date,2);
3013 hr_utility.set_location('p_val_end_date: '||p_val_end_date,2);
3014
3015 --
3016 IF p_del_flag = 'Y' THEN
3017 --
3018 -- Get the min start date for the placement
3019 --
3020 select min(effective_start_date)
3021 into l_min_start_date
3022 from per_spinal_point_placements_f
3023 where assignment_id = p_assignment_id;
3024
3025 if l_min_start_date = p_val_st_date
3026 and p_datetrack_mode = 'DELETE_NEXT_CHANGE' then
3027
3028 hr_assignment_internal.delete_first_spp
3029 (p_effective_date => p_edate,
3030 p_assignment_id => p_assignment_id,
3031 p_validation_start_date => p_val_st_date,
3032 p_validation_end_date => p_val_end_date,
3033 p_future_spp_warning => l_future_spp_warning
3034 );
3035
3036 p_future_spp_warning := l_future_spp_warning;
3037
3038 else
3039
3040 IF p_mode = 'ZAP' THEN
3041 hr_utility.set_location('hr_assignment.delete_ass_ref_int',2);
3042
3043 l_datetrack_mode := 'ZAP';
3044
3045 --
3046 -- Check that there has been a grade step created for this assignment
3047 --
3048 open csr_grade_step;
3049 fetch csr_grade_step into l_update;
3050 if csr_grade_step%found then
3051
3052 --
3053 -- Delete using the api passing the minimum start date
3054 --
3055 hr_sp_placement_api.delete_spp
3056 (p_effective_date => l_min_start_date
3057 ,p_datetrack_mode => l_datetrack_mode
3058 ,p_placement_id => l_placement_id
3059 ,p_object_version_number => l_object_version_number
3060 ,p_effective_start_date => l_effective_start_date
3061 ,p_effective_end_date => l_effective_end_date);
3062
3063 end if;
3064 close csr_grade_step;
3065
3066 --
3067 -- Removed dml and inserted call to api
3068 --
3069 /* DELETE FROM PER_SPINAL_POINT_PLACEMENTS_F P
3070 WHERE P.ASSIGNMENT_ID = p_assignment_id;
3071 */
3072 --
3073 ELSIF p_mode = 'FUTURE' THEN
3074 -- If mode is 'FUTURE' then do Delete Placements that
3075 -- start on or after the vaidation start date, which are related
3076 -- to grade spine records where the grade is different the
3077 --
3078 -- Check that there has been a grade step created for this assignment
3079 --
3080 open csr_grade_step;
3081 fetch csr_grade_step into l_update;
3082 if csr_grade_step%found then
3083 hr_utility.set_location('Grade Step Found',4);
3084 hr_utility.set_location('p_calling_proc :'||p_calling_proc,4);
3085 hr_utility.set_location('p_val_st_date: '||p_val_st_date,4);
3086
3087 --
3088 -- Calling proces = 'POST_UPDATE' then a min step has to be created for the new grade scale
3089 --
3090 if p_calling_proc = 'POST_UPDATE' and
3091 p_datetrack_mode <> hr_api.g_update_override then -- Bug 3335915
3092
3093 --
3094 -- get the placement_id and object_version_number for
3095 -- the current record as of the effective date
3096 --
3097 select spp.placement_id,spp.object_version_number,spp.effective_start_date,spp.parent_spine_id
3098 into l_placement_id,l_object_version_number,l_date_temp,l_old_parent_spine_id
3099 from per_spinal_point_placements_f spp
3100 where spp.assignment_id = p_assignment_id
3101 and p_val_st_date between spp.effective_start_date
3102 and spp.effective_end_date;
3103
3104 hr_utility.set_location('l_placement_id :'||l_placement_id,5);
3105 hr_utility.set_location('l_object_version_number :'||l_object_version_number,5);
3106 hr_utility.set_location('l_date_temp :'||l_date_temp,5);
3107 hr_utility.set_location('l_old_parent_spine_id :'||l_old_parent_spine_id,5);
3108
3109 --
3110 -- get the parent spine_id for the new grade
3111 --
3112 select pgs.parent_spine_id
3113 into l_parent_spine_id
3114 from per_grade_spines_f pgs
3115 where pgs.grade_id = p_grade_id
3116 and P_edate between pgs.effective_start_date
3117 and pgs.effective_end_date;
3118
3119 hr_utility.set_location('l_parent_spine_id :'||l_parent_spine_id,6);
3120
3121 --
3122 -- Get the min seuence for the new grade
3123 --
3124 select min(psp.sequence)
3125 into l_sequence
3126 from per_spinal_points psp,
3127 per_spinal_point_steps_f sps
3128 where psp.parent_spine_id = l_parent_spine_id
3129 and psp.spinal_point_id = sps.spinal_point_id
3130 and P_edate between sps.effective_start_date
3131 and sps.effective_end_date;
3132
3133 hr_utility.set_location('l_sequence :'||l_sequence,7);
3134 --
3135 -- Get the step id for the min sequence
3136 --
3137 select sps.step_id
3138 into l_min_step_id
3139 from per_spinal_point_steps_f sps,
3140 per_spinal_points psp
3141 where sps.spinal_point_id = psp.spinal_point_id
3142 and psp.parent_spine_id = l_parent_spine_id
3143 and psp.sequence = l_sequence;
3144
3145
3146 hr_utility.set_location('l_min_step_id :'||l_min_step_id,8);
3147
3148 open csr_future_records;
3149 fetch csr_future_records into l_temp;
3150
3151 if csr_future_records%found then
3152
3153 hr_utility.set_location('Future record found.',9);
3154
3155 --
3156 -- check if there is a step placement record starting on the same day
3157 --
3158 if l_date_temp = p_edate then
3159 l_datetrack_mode := 'CORRECTION';
3160 p_future_spp_warning := TRUE;
3161 else
3162 l_datetrack_mode := 'UPDATE_OVERRIDE';
3163 p_future_spp_warning := TRUE;
3164 end if;
3165
3166 else
3167
3168 hr_utility.set_location('Future record not found,',10);
3169
3170 if l_date_temp = p_edate then
3171 l_datetrack_mode := 'CORRECTION';
3172 p_future_spp_warning := FALSE;
3173 else
3174 l_datetrack_mode := 'UPDATE';
3175 p_future_spp_warning := FALSE;
3176 end if;
3177 end if;
3178 close csr_future_records;
3179
3180 --
3181 -- Update the now current record
3182 --
3183 hr_sp_placement_api.update_spp
3184 (p_effective_date => p_edate
3185 ,p_datetrack_mode => l_datetrack_mode
3186 ,p_placement_id => l_placement_id
3187 ,p_object_version_number => l_object_version_number
3188 ,p_step_id => l_min_step_id
3189 ,p_auto_increment_flag => 'N'
3190 ,p_reason => ''
3191 ,p_increment_number => NULL
3192 ,p_effective_start_date => l_effective_start_date
3193 ,p_effective_end_date => l_effective_end_date);
3194
3195 l_new_date := p_edate;
3196
3197 end if;
3198
3199 hr_utility.set_location('Deleteing the next change.',15);
3200
3201 /*if p_datetrack_mode <> 'UPDATE_OVERRIDE' then
3202 l_new_date := p_val_st_date;
3203 else
3204 l_new_date := p_edate;
3205 end if;
3206 */
3207 l_new_date := p_edate;
3208
3209 hr_utility.set_location('l_new_date: '||l_new_date,15);
3210
3211 --
3212 -- Delete next change until the effective end date of the record
3213 -- that was just inserted matches the validation end date
3214 --
3215 -- Start of 3335915
3216 open csr_spp_rec(l_new_date);
3217 fetch csr_spp_rec into l_effective_end_date
3218 ,l_placement_id
3219 ,l_object_version_number;
3220 if csr_spp_rec%found then
3221 --
3222 l_datetrack_mode := 'DELETE_NEXT_CHANGE';
3223 --
3224 hr_utility.set_location('l_effective_end_date :'||l_effective_end_date, 25);
3225 hr_utility.set_location('p_val_end_date :'||p_val_end_date, 25);
3226 --
3227 loop
3228 --
3229 if l_effective_end_date = p_val_end_date then
3230 --
3231 exit;
3232 --
3233 end if;
3234 --
3235 hr_sp_placement_api.delete_spp(
3236 p_effective_date => p_edate
3237 ,p_datetrack_mode => l_datetrack_mode
3238 ,p_placement_id => l_placement_id
3239 ,p_object_version_number => l_object_version_number
3240 ,p_effective_start_date => l_effective_start_date
3241 ,p_effective_end_date => l_effective_end_date);
3242 --
3243 open csr_spp_end_date(l_placement_id, p_edate);
3244 fetch csr_spp_end_date into l_effective_end_date;
3245 close csr_spp_end_date;
3246 --
3247 end loop;
3248 end if;
3249 --
3250 close csr_spp_rec;
3251 -- End of 3335915
3252
3253 end if;
3254 close csr_grade_step;
3255
3256 hr_utility.set_location('hr_assignment.delete_ass_ref_int',3);
3257 --
3258 -- Removed dml and inserted call to api
3259 --
3260 /*
3261 DELETE FROM PER_SPINAL_POINT_PLACEMENTS_F P
3262 WHERE P.ASSIGNMENT_ID = p_assignment_id
3263 AND P.EFFECTIVE_START_DATE >= p_end_date
3264 AND NOT EXISTS
3265 (SELECT NULL
3266 FROM PER_SPINAL_POINT_STEPS_F S
3267 , PER_GRADE_SPINES_F GS
3268 WHERE GS.GRADE_SPINE_ID = S.GRADE_SPINE_ID
3269 AND S.STEP_ID = P.STEP_ID
3270 AND GS.GRADE_ID = NVL(p_grade_id,-1));
3271 */
3272 --
3273 ELSE
3274 -- If mode is 'END' then do Date Effective Delete
3275 -- from p_end_date.
3276 --
3277 hr_utility.set_location('hr_assignment.delete_ass_ref_int',4);
3278 --
3279 -- Removed dml and using api
3280 --
3281 /*
3282 DELETE FROM PER_SPINAL_POINT_PLACEMENTS_F P
3283 WHERE P.ASSIGNMENT_ID = p_assignment_id
3284 AND P.EFFECTIVE_START_DATE > p_end_date;
3285 */
3286
3287 --
3288 -- Check that there has been a grade step created for this assignment
3289 --
3290 open csr_grade_step;
3291 fetch csr_grade_step into l_update;
3292 if csr_grade_step%found then
3293
3294 select spp.placement_id,spp.object_version_number,spp.effective_start_date
3295 into l_placement_id,l_object_version_number,l_date_temp
3296 from per_spinal_point_placements_f spp
3297 where spp.assignment_id = p_assignment_id
3298 and p_end_date between spp.effective_start_date
3299 and spp.effective_end_date;
3300
3301 -- This code has been re-written to perform the end-dating of
3302 -- spinal point placement records. Bug# 2854295
3303
3304 l_datetrack_mode := 'DELETE';
3305
3306 hr_sp_placement_api.delete_spp
3307 (p_effective_date => P_edate
3308 ,p_datetrack_mode => l_datetrack_mode
3309 ,p_placement_id => l_placement_id
3310 ,p_object_version_number => l_object_version_number
3311 ,p_effective_start_date => l_effective_start_date
3312 ,p_effective_end_date => l_effective_end_date);
3313
3314 end if;
3315 close csr_grade_step;
3316
3317
3318 /*hr_utility.set_location('hr_assignment.delete_ass_ref_int',5);
3319 UPDATE PER_SPINAL_POINT_PLACEMENTS_F
3320 SET EFFECTIVE_END_DATE = p_end_date
3321 , LAST_UPDATED_BY = p_last_updated_by
3322 , LAST_UPDATE_LOGIN = p_last_update_login
3323 , LAST_UPDATE_DATE = sysdate
3324 WHERE ASSIGNMENT_ID = p_assignment_id
3325 AND p_end_date
3326 BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE;
3327 */
3328 END IF;
3329
3330 end if;
3331 --
3332 END IF;
3333
3334 p_del_flag := 'N';
3335 --
3336 -- Check the assignment budget values
3337 --
3338 BEGIN
3339 --
3340 SELECT 'Y'
3341 into p_del_flag
3342 FROM SYS.DUAL
3343 WHERE EXISTS
3344 (SELECT NULL
3345 FROM PER_ASSIGNMENT_BUDGET_VALUES_F ABV
3346 WHERE ABV.ASSIGNMENT_ID = p_assignment_id
3347 AND (p_mode = 'ZAP'
3348 OR (p_mode = 'END'
3349 AND ABV.EFFECTIVE_END_DATE > p_end_date)
3350 OR (p_mode = 'FUTURE'
3351 --AND ABV.EFFECTIVE_START_DATE >= p_end_date))); -- this condition will never satisfy, as when end dating,
3352 -- we do not create a record for with start date = end_date+1
3353 AND ABV.EFFECTIVE_END_DATE >= p_end_date))); -- bug 7112709
3354
3355 --
3356 EXCEPTION
3357 WHEN NO_DATA_FOUND THEN NULL;
3358 END;
3359 --
3360 IF p_del_flag = 'Y' THEN
3361 --
3362 IF p_mode = 'ZAP' THEN
3363 hr_utility.set_location('hr_assignment.del_ref_int_delet',30);
3364 DELETE FROM PER_ASSIGNMENT_BUDGET_VALUES_F ABV
3365 WHERE ABV.ASSIGNMENT_ID = p_assignment_id;
3366
3367 --
3368 ELSE
3369 IF p_mode = 'END' THEN
3370 -- If mode is 'END' then do Date Effective Delete
3371 -- from p_end_date.
3372 --
3373 hr_utility.set_location('hr_assignment.del_ref_int_delete',40);
3374
3375 DELETE FROM PER_ASSIGNMENT_BUDGET_VALUES_F ABV
3376 WHERE ABV.ASSIGNMENT_ID = p_assignment_id
3377 AND ABV.EFFECTIVE_START_DATE > p_end_date;
3378 --
3379 hr_utility.set_location('hr_assignment.del_ref_int_delete',45);
3380 UPDATE PER_ASSIGNMENT_BUDGET_VALUES_F
3381 SET EFFECTIVE_END_DATE = p_end_date
3382 , LAST_UPDATED_BY = p_last_updated_by
3383 , LAST_UPDATE_LOGIN = p_last_update_login
3384 , LAST_UPDATE_DATE = sysdate
3385 WHERE ASSIGNMENT_ID = p_assignment_id
3386 AND p_end_date
3387 BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE;
3388 END IF;
3389 --
3390 -- addition for Bug 7112709 starts
3391 IF p_mode = 'FUTURE' THEN
3392 -- If mode is 'FUTURE' then do Date Effective Delete for all future records
3393 -- from p_end_date. Further, open the current end dated record. Set the last date
3394 -- of assignment_budget_value record same as the last date of current assignmet.
3395 --
3396 hr_utility.set_location('hr_assignment.del_ref_int_delete',46);
3397 hr_utility.set_location('p_end_date '||p_end_date, 47);
3398
3399 DELETE FROM PER_ASSIGNMENT_BUDGET_VALUES_F ABV
3400 WHERE ABV.ASSIGNMENT_ID = p_assignment_id
3401 AND ABV.EFFECTIVE_START_DATE > p_end_date
3402 AND ABV.EFFECTIVE_END_DATE <> hr_api.g_eot;--Added as a part of fix for bug#11078262
3403 --
3404 hr_utility.set_location(' No of rows deleted '||sql%rowcount,48);
3405 hr_utility.set_location('hr_assignment.del_ref_int_delete',49);
3406
3407 select effective_end_date into l_ass_end_date
3408 from per_all_assignments_f
3409 where assignment_id = p_assignment_id
3410 and p_end_date between EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE;
3411
3412 hr_utility.set_location('l_ass_end_date '||l_ass_end_date,50);
3413
3414
3415 UPDATE PER_ASSIGNMENT_BUDGET_VALUES_F
3416 SET EFFECTIVE_END_DATE = l_ass_end_date
3417 , LAST_UPDATED_BY = p_last_updated_by
3418 , LAST_UPDATE_LOGIN = p_last_update_login
3419 , LAST_UPDATE_DATE = sysdate
3420 WHERE ASSIGNMENT_ID = p_assignment_id
3421 AND p_end_date
3422 BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE
3423 and EFFECTIVE_END_DATE <> hr_api.g_eot; -- Added the condition as a part of bug#9669561.
3424
3425 -- Bug 13809858
3426 -- hr_utility.set_location('No of rows updated '||sql%rowcount,51);
3427
3428 END IF;
3429 -- Addition for Bug 7112709 ends
3430
3431 END IF;
3432 --
3433 END IF;
3434
3435 --
3436 --
3437
3438 IF p_mode = 'ZAP' THEN
3439 hr_utility.set_location('hr_assignment.del_ref_int_delete',5);
3440 p_del_flag := 'N';
3441 --
3442 BEGIN
3443 --
3444 -- Just do a lookup without any complex where clause because the complex
3445 -- where clause has been done in PRE-DELETE triggers.
3446 SELECT 'Y'
3447 into p_del_flag
3448 FROM SYS.DUAL
3449 WHERE EXISTS
3450 (SELECT NULL
3451 FROM HR_ASSIGNMENT_SET_AMENDMENTS
3452 WHERE ASSIGNMENT_ID = p_assignment_id);
3453 --
3454 EXCEPTION
3455 WHEN NO_DATA_FOUND THEN NULL;
3456 END;
3457 --
3458 IF p_del_flag = 'Y' THEN
3459 --
3460 hr_utility.set_location('hr_assignment.delete_ass_ref_int',6);
3461 DELETE FROM HR_ASSIGNMENT_SET_AMENDMENTS
3462 WHERE ASSIGNMENT_ID = p_assignment_id;
3463 END IF;
3464 --
3465 --
3466 --
3467 hr_utility.set_location('hr_assignment.del_ref_int_delete',9);
3468 p_del_flag := 'N';
3469 --
3470 BEGIN
3471 --
3472 SELECT 'Y'
3473 into p_del_flag
3474 FROM SYS.DUAL
3475 WHERE EXISTS
3476 (SELECT NULL
3477 FROM PER_SECONDARY_ASS_STATUSES
3478 WHERE ASSIGNMENT_ID = p_assignment_id);
3479 --
3480 EXCEPTION
3481 WHEN NO_DATA_FOUND THEN NULL;
3482 END;
3483 --
3484 IF p_del_flag = 'Y' THEN
3485 --
3486 hr_utility.set_location('hr_assignment.delete_ass_ref_int',10);
3487 DELETE FROM PER_SECONDARY_ASS_STATUSES
3488 WHERE ASSIGNMENT_ID = p_assignment_id;
3489 END IF;
3490 --
3491 --
3492 hr_utility.set_location('hr_assignment.del_ref_int_delete',11);
3493 p_del_flag := 'N';
3494 --
3495 BEGIN
3496 --
3497 SELECT 'Y'
3498 into p_del_flag
3499 FROM SYS.DUAL
3500 WHERE EXISTS
3501 (SELECT NULL
3502 FROM PER_PAY_PROPOSALS
3503 WHERE ASSIGNMENT_ID = p_assignment_id);
3504 --
3505 EXCEPTION
3506 WHEN NO_DATA_FOUND THEN NULL;
3507 END;
3508 --
3509 IF p_del_flag = 'Y' THEN
3510 --
3511 hr_utility.set_location('hr_assignment.delete_ass_ref_int',12);
3512 DELETE FROM PER_PAY_PROPOSALS
3513 WHERE ASSIGNMENT_ID = p_assignment_id;
3514 END IF;
3515 --
3516 /* This is being changed for Bug# 785427 */
3517
3518 hr_utility.set_location('hr_assignment.del_ref_int_delete',11);
3519 p_del_flag := 'N';
3520
3521 BEGIN
3522 select 'Y'
3523 into p_del_flag
3524 from sys.dual
3525 where exists (
3526 select null
3527 from PAY_US_EMP_FED_TAX_RULES_F pef
3528 where pef.assignment_id = p_assignment_id);
3529
3530 EXCEPTION
3531 WHEN NO_DATA_FOUND THEN NULL;
3532 END;
3533 --
3534 IF p_del_flag = 'Y' THEN
3535 --
3536 hr_utility.set_location('hr_assignment.delete_ass_ref_int',12);
3537 /* If the federal tax record exists then the state, county
3538 and city tax records also exist (due to the defaulting of
3539 tax records). So, delete from all 4 table. In addition, delete
3540 from the table pay_us_asg_reporting as well */
3541
3542 DELETE FROM PAY_US_ASG_REPORTING
3543 WHERE ASSIGNMENT_ID = p_assignment_id;
3544
3545 DELETE FROM PAY_US_EMP_CITY_TAX_RULES_F
3546 WHERE ASSIGNMENT_ID = p_assignment_id;
3547
3548 DELETE FROM PAY_US_EMP_COUNTY_TAX_RULES_F
3549 WHERE ASSIGNMENT_ID = p_assignment_id;
3550
3551 DELETE FROM PAY_US_EMP_STATE_TAX_RULES_F
3552 WHERE ASSIGNMENT_ID = p_assignment_id;
3553
3554 DELETE FROM PAY_US_EMP_FED_TAX_RULES_F
3555 WHERE ASSIGNMENT_ID = p_assignment_id;
3556
3557 END IF;
3558 --
3559
3560 -- 03/18/1998 Bug #642566
3561 -- Remove per_assignment_extra_info records
3562 hr_utility.set_location('hr_assignment.del_ref_int_delete',14);
3563 p_del_flag := 'N';
3564 --
3565 BEGIN
3566 --
3567 SELECT 'Y'
3568 into p_del_flag
3569 FROM SYS.DUAL
3570 WHERE EXISTS
3571 (SELECT NULL
3572 FROM PER_ASSIGNMENT_EXTRA_INFO
3573 WHERE ASSIGNMENT_ID = p_assignment_id);
3574 --
3575 EXCEPTION
3576 WHEN NO_DATA_FOUND THEN NULL;
3577 END;
3578 --
3579 IF p_del_flag = 'Y' THEN
3580 --
3581 hr_utility.set_location('hr_assignment.delete_ass_ref_int',16);
3582 DELETE FROM PER_ASSIGNMENT_EXTRA_INFO
3583 WHERE ASSIGNMENT_ID = p_assignment_id;
3584 END IF;
3585 -- 03/18/1998 Change Ends
3586 --
3587 ELSIF p_mode = 'END' then
3588
3589 DELETE FROM PAY_US_EMP_CITY_TAX_RULES_F
3590 WHERE ASSIGNMENT_ID = p_assignment_id
3591 AND EFFECTIVE_START_DATE > p_end_date;
3592
3593 UPDATE PAY_US_EMP_CITY_TAX_RULES_F
3594 SET EFFECTIVE_END_DATE = p_end_date
3595 , LAST_UPDATED_BY = p_last_updated_by
3596 , LAST_UPDATE_LOGIN = p_last_update_login
3597 , LAST_UPDATE_DATE = sysdate
3598 WHERE ASSIGNMENT_ID = p_assignment_id
3599 AND p_end_date BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE;
3600
3601 DELETE FROM PAY_US_EMP_COUNTY_TAX_RULES_F
3602 WHERE ASSIGNMENT_ID = p_assignment_id
3603 AND EFFECTIVE_START_DATE > p_end_date;
3604
3605 UPDATE PAY_US_EMP_COUNTY_TAX_RULES_F
3606 SET EFFECTIVE_END_DATE = p_end_date
3607 , LAST_UPDATED_BY = p_last_updated_by
3608 , LAST_UPDATE_LOGIN = p_last_update_login
3609 , LAST_UPDATE_DATE = sysdate
3610 WHERE ASSIGNMENT_ID = p_assignment_id
3611 AND p_end_date BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE;
3612
3613 DELETE FROM PAY_US_EMP_STATE_TAX_RULES_F
3614 WHERE ASSIGNMENT_ID = p_assignment_id
3615 AND EFFECTIVE_START_DATE > p_end_date;
3616
3617 UPDATE PAY_US_EMP_STATE_TAX_RULES_F
3618 SET EFFECTIVE_END_DATE = p_end_date
3619 , LAST_UPDATED_BY = p_last_updated_by
3620 , LAST_UPDATE_LOGIN = p_last_update_login
3621 , LAST_UPDATE_DATE = sysdate
3622 WHERE ASSIGNMENT_ID = p_assignment_id
3623 AND p_end_date BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE;
3624
3625 DELETE FROM PAY_US_EMP_FED_TAX_RULES_F
3626 WHERE ASSIGNMENT_ID = p_assignment_id
3627 AND EFFECTIVE_START_DATE > p_end_date;
3628
3629 UPDATE PAY_US_EMP_FED_TAX_RULES_F
3630 SET EFFECTIVE_END_DATE = p_end_date
3631 , LAST_UPDATED_BY = p_last_updated_by
3632 , LAST_UPDATE_LOGIN = p_last_update_login
3633 , LAST_UPDATE_DATE = sysdate
3634 WHERE ASSIGNMENT_ID = p_assignment_id
3635 AND p_end_date BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE;
3636
3637 END IF; -- ZAP /END section
3638 --
3639 --
3640 END del_ref_int_delete;
3641 --
3642 --
3643 ------------------- del_ref_int_delete --------------------
3644 --
3645 /*
3646 NAME
3647 tidy_up_ref_int
3648 DESCRIPTION
3649 This procedure performs two operations.
3650 The first occurs when it is called with a parameter of 'END' - the
3651 procedure then moves the end date of any child rows for the assignment
3652 so that it is set to be the end date of the assignment.
3653
3654 The second occurs when it is called with a parameter of 'FUTURE'.
3655 This is the case when a FUTURE_CHANGE of DELETE_NEXT_CHANGE is going
3656 to open the assignment out nocopy beyond its current End Date. The procedure
3657 resets the End Dates of any child rows to be that on the Assignment. In
3658 the case of Costing records dates are only changed if there are not
3659 future records.
3660
3661 The following tables are affected.
3662
3663 PAY_COST_ALLOCATIONS_F
3664 PER_SECONDARY_ASS_STATUSES
3665 PAY_PERSONAL_PAYMENT_METHODS_F
3666 PER_ASSIGNMENT_BUDGET_VALUES_F
3667
3668 PARAMETERS
3669 p_assignment_id - Assignment ID
3670 p_mode - 'END' or 'FUTURE'
3671 p_new_end_date - The new end date of the parent Assignment
3672 p_old_end_date - The Assignment End Date before the operation
3673 p_last_updated_by
3674 p_last_update_login
3675 p_cost_warning - Pass back warning if future costing records
3676 exist. Can only set to TRUE if mode is
3677 FUTURE.
3678 */
3679 --
3680 PROCEDURE tidy_up_ref_int
3681 (p_assignment_id IN INTEGER
3682 ,p_mode IN VARCHAR2
3683 ,p_new_end_date IN DATE
3684 ,p_old_end_date DATE
3685 ,p_last_updated_by INTEGER
3686 ,p_last_update_login INTEGER
3687 ,p_cost_warning OUT NOCOPY BOOLEAN) IS
3688 --
3689 p_del_flag VARCHAR2(1) := 'N';
3690 l_exists NUMBER := 0;
3691 l_proc VARCHAR(72) := 'hr_assignment.tidy_up_ref_int';
3692 l_effective_start_Date DATE;
3693 l_effective_end_date DATE;
3694 --
3695 -- Retrieve all current Assignment Rate records for the assignment.
3696 --
3697 CURSOR csr_current_asg_rates IS
3698 SELECT pgr.grade_rule_id,
3699 pgr.object_version_number
3700 FROM pay_grade_rules_f pgr
3701 WHERE pgr.grade_or_spinal_point_id = p_assignment_id
3702 AND pgr.rate_type = 'A'
3703 AND p_new_end_date BETWEEN pgr.effective_start_date
3704 AND pgr.effective_end_date
3705 AND p_mode='END';
3706 --
3707 -- Retrieve all the future-only Assignment Rate records for the assignment.
3708 --
3709 CURSOR csr_future_asg_rates IS
3710 SELECT pgr.grade_rule_id,
3711 pgr.object_version_number,
3712 pgr.effective_start_date
3713 FROM pay_grade_rules_f pgr
3714 WHERE pgr.grade_or_spinal_point_id = p_assignment_id
3715 AND pgr.rate_type = 'A'
3716 AND p_new_end_date < pgr.effective_start_date
3717 AND p_mode='END';
3718 --
3719 BEGIN
3720 --
3721 hr_utility.set_location('hr_assignment.tidy_up_ref_int',1);
3722 --
3723 p_cost_warning := FALSE;
3724 BEGIN
3725 select 'Y'
3726 into p_del_flag
3727 from sys.dual
3728 where exists (
3729 select null
3730 from per_secondary_ass_statuses
3731 where assignment_id = p_assignment_id
3732 and ((p_mode = 'END'
3733 and p_new_end_date
3734 between START_DATE and nvl(END_DATE,
3735 to_date('31/12/4712','DD/MM/YYYY')))
3736 or
3737 (p_mode = 'FUTURE'
3738 and p_old_end_date = nvl(END_DATE,
3739 to_date('31/12/4712','DD/MM/YYYY')))));
3740 EXCEPTION
3741 WHEN NO_DATA_FOUND THEN NULL;
3742 END;
3743 --
3744 IF p_del_flag = 'Y' THEN
3745 --
3746 hr_utility.set_location('hr_assignment.tidy_up_ref_int',2);
3747 --
3748 update per_secondary_ass_statuses
3749 set END_DATE = decode(p_new_end_date,to_date('31/12/4712','DD/MM/YYYY'),
3750 null,p_new_end_date)
3751 , last_updated_by = P_LAST_UPDATED_BY
3752 , last_update_login = P_LAST_UPDATE_LOGIN
3753 , last_update_date = sysdate
3754 where assignment_id = p_assignment_id
3755 and ((p_mode = 'END'
3756 and p_new_end_date
3757 between START_DATE and nvl(END_DATE,
3758 to_date('31/12/4712','DD/MM/YYYY')))
3759 or
3760 (p_mode = 'FUTURE'
3761 and p_old_end_date = nvl(END_DATE,
3762 to_date('31/12/4712','DD/MM/YYYY'))));
3763 END IF;
3764 --
3765 p_del_flag := 'N';
3766 --
3767 hr_utility.set_location('hr_assignment.tidy_up_ref_int',3);
3768 --
3769 BEGIN
3770 select 'Y'
3771 into p_del_flag
3772 from sys.dual
3773 where exists (
3774 select null
3775 from per_secondary_ass_statuses
3776 where assignment_id = p_assignment_id
3777 and p_mode = 'END'
3778 and p_new_end_date < START_DATE);
3779 EXCEPTION
3780 WHEN NO_DATA_FOUND THEN NULL;
3781 END;
3782 --
3783 IF p_del_flag = 'Y' THEN
3784 --
3785 hr_utility.set_location('hr_assignment.tidy_up_ref_int',4);
3786 --
3787 delete from per_secondary_ass_statuses
3788 where assignment_id = p_assignment_id
3789 and p_mode = 'END'
3790 and p_new_end_date < START_DATE;
3791 END IF;
3792 --
3793 p_del_flag := 'N';
3794 --
3795 hr_utility.set_location('hr_assignment.tidy_up_ref_int',5);
3796 --
3797 BEGIN
3798 select 'Y'
3799 into p_del_flag
3800 from sys.dual
3801 where exists (
3802 select null
3803 from pay_cost_allocations_f
3804 where assignment_id = p_assignment_id
3805 and ((p_mode = 'END'
3806 and p_new_end_date
3807 between effective_start_date and effective_end_date)
3808 or
3809 (p_mode = 'FUTURE'
3810 and p_old_end_date = effective_end_date)));
3811 EXCEPTION
3812 WHEN NO_DATA_FOUND THEN NULL;
3813 END;
3814 --
3815 IF p_del_flag = 'Y' THEN
3816 --
3817 hr_utility.set_location('hr_assignment.tidy_up_ref_int',6);
3818
3819 if p_mode = 'END' then
3820 hr_utility.set_location('hr_assignment.tidy_up_ref_int',7);
3821 update pay_cost_allocations_f
3822 set effective_end_date = p_new_end_date
3823 , last_updated_by = P_LAST_UPDATED_BY
3824 , last_update_login = P_LAST_UPDATE_LOGIN
3825 , last_update_date = sysdate
3826 where assignment_id = p_assignment_id
3827 and ((p_mode = 'END'
3828 and p_new_end_date
3829 between effective_start_date and effective_end_date));
3830 elsif p_mode='FUTURE' then
3831 hr_utility.set_location('hr_assignment.tidy_up_ref_int',8);
3832
3833 /*
3834 ** When dealing with delete FUTURE_CHANGE, only open out
3835 ** the costing record if no future costing record exists.
3836 ** If they do leaving the costing alone and display
3837 ** message to the user informing them of the situation.
3838 */
3839 select count(*)
3840 into l_exists
3841 from pay_cost_allocations_f
3842 where assignment_id = p_assignment_id
3843 and effective_start_date > p_old_end_date;
3844
3845 if l_exists = 0 then
3846 hr_utility.set_location('hr_assignment.tidy_up_ref_int',9);
3847 update pay_cost_allocations_f
3848 set effective_end_date = p_new_end_date
3849 , last_updated_by = P_LAST_UPDATED_BY
3850 , last_update_login = P_LAST_UPDATE_LOGIN
3851 , last_update_date = sysdate
3852 where assignment_id = p_assignment_id
3853 and (p_mode = 'FUTURE'
3854 and p_old_end_date = effective_end_date);
3855 else
3856 hr_utility.set_location('hr_assignment.tidy_up_ref_int',10);
3857 p_cost_warning := TRUE;
3858 end if;
3859
3860 end if;
3861 --
3862 END IF;
3863
3864
3865 -- New logic added to deal with per assignment_budget_values_f. Now required as this
3866 -- table is datetracked. The following code works when a mode of end is required (i.e. the
3867 -- user has requested a date effective delete which will terminate the row at a given point in time).
3868 --
3869 -- The first thing is to remove any rows which have a start date greater than the requested NEW
3870 -- end date. This will take care of all future rows.
3871 -- What then needs to happen is to change the end date of the existing row to make sure the current child
3872 -- row stays in line with the parent.
3873 -- SASmith : 16-APR-1998
3874
3875 hr_utility.set_location(p_new_end_date,11);
3876 hr_utility.set_location(p_mode,12);
3877 hr_utility.set_location(p_old_end_date,13);
3878
3879 hr_utility.set_location('hr_assignment.tidy_up_ref_int',15);
3880
3881 p_del_flag := 'N';
3882
3883 --
3884 --
3885 BEGIN
3886 select 'Y'
3887 into p_del_flag
3888 from sys.dual
3889 where exists (
3890 select null
3891 from per_assignment_budget_values_f abv
3892 where abv.assignment_id = p_assignment_id
3893 and p_mode = 'END'
3894 and p_new_end_date < abv.effective_start_date);
3895 EXCEPTION
3896 WHEN NO_DATA_FOUND THEN NULL;
3897 END;
3898 --
3899 IF p_del_flag = 'Y' THEN
3900 --
3901 hr_utility.set_location('hr_assignment.tidy_up_ref_int',20);
3902 --
3903 delete from per_assignment_budget_values_f abv
3904 where abv.assignment_id = p_assignment_id
3905 and p_mode = 'END'
3906 and p_new_end_date < abv.effective_start_date;
3907 END IF;
3908
3909 p_del_flag := 'N';
3910 --
3911 hr_utility.set_location('hr_assignment.tidy_up_ref_int',25);
3912
3913
3914 BEGIN
3915 select 'Y'
3916 into p_del_flag
3917 from sys.dual
3918 where exists (
3919 select null
3920 from per_assignment_budget_values_f abv
3921 where abv.assignment_id = p_assignment_id
3922 and p_mode = 'END'
3923 and p_new_end_date between abv.effective_start_date and abv.effective_end_date);
3924
3925 EXCEPTION
3926 WHEN NO_DATA_FOUND THEN NULL;
3927 END;
3928
3929 IF p_del_flag = 'Y' THEN
3930 --
3931 hr_utility.set_location('hr_assignment.tidy_up_ref_int',30);
3932 --
3933 update per_assignment_budget_values_f abv
3934 set abv.effective_end_date = p_new_end_date
3935 , abv.last_updated_by = P_LAST_UPDATED_BY
3936 , abv.last_update_login = P_LAST_UPDATE_LOGIN
3937 , abv.last_update_date = sysdate
3938 where abv.assignment_id = p_assignment_id
3939 and p_mode = 'END'
3940 and p_new_end_date between abv.effective_start_date and abv.effective_end_date;
3941
3942 END IF;
3943
3944 --
3945 hr_utility.set_location('hr_assignment.tidy_up_ref_int',35);
3946 --
3947 -- # 2437795
3948 -- Reversing TAX records
3949 --
3950 IF p_mode = 'FUTURE' THEN
3951 --
3952 hr_utility.set_location('hr_assignment.tidy_up_ref_int',42);
3953 hr_utility.trace(' **** old end date = '||to_char(p_old_end_date,'dd-mon-yyyy'));
3954 declare
3955 cursor csr_asg is
3956 select max(effective_end_date)
3957 from pay_us_emp_fed_tax_rules_f
3958 where assignment_id = p_assignment_id;
3959 l_end_date date;
3960 begin
3961 open csr_asg;
3962 fetch csr_asg into l_end_date;
3963 close csr_asg;
3964 hr_utility.trace(' **** l_end_date = '||to_char(l_end_date,'dd-mon-yyyy'));
3965 if l_end_date is not null then
3966 pay_us_update_tax_rec_pkg.reverse_term_emp_tax_records(p_assignment_id, l_end_date);
3967 end if;
3968 end;
3969 --
3970 hr_utility.set_location('hr_assignment.tidy_up_ref_int',45);
3971 END IF;
3972 -- end #2437795
3973 --
3974 --adhunter added for bug 2537091 04-OCT-02
3975 --need to handle pay_personal_payment_methods in the same way as the others above.
3976 --
3977 hr_utility.set_location('hr_assignment.tidy_up_ref_int',50);
3978 DECLARE
3979 l_effective_start_date date;
3980 l_effective_end_date date;
3981 --
3982 --retrieve all the current PPMs
3983 --
3984 cursor csr_curr_ppm is
3985 select ppm.personal_payment_method_id,ppm.object_version_number
3986 from pay_personal_payment_methods_f ppm
3987 where ppm.assignment_id = p_assignment_id
3988 and p_new_end_date between ppm.effective_start_date and ppm.effective_end_date
3989 and p_mode='END';
3990 --
3991 --retrieve all the future-only PPMs
3992 --
3993 cursor csr_fut_ppm is
3994 select ppm.personal_payment_method_id,ppm.object_version_number,ppm.effective_start_date
3995 from pay_personal_payment_methods_f ppm
3996 where ppm.assignment_id = p_assignment_id
3997 and p_new_end_date < ppm.effective_start_date
3998 and p_mode='END';
3999 --
4000 BEGIN
4001 for l_curr_rec in csr_curr_ppm loop
4002 hr_utility.set_location('ppm_id '||l_curr_rec.personal_payment_method_id,55);
4003 --
4004 --end date current DT row and delete future rows for this PPM
4005 --this means that future-only PPMs are left.
4006 --
4007 hr_personal_pay_method_api.delete_personal_pay_method
4008 (p_effective_date => p_new_end_date
4009 ,p_datetrack_delete_mode => 'DELETE'
4010 ,p_personal_payment_method_id => l_curr_rec.personal_payment_method_id
4011 ,p_object_version_number => l_curr_rec.object_version_number
4012 ,p_effective_start_date => l_effective_start_date
4013 ,p_effective_end_date => l_effective_end_date
4014 );
4015 end loop;
4016 for l_fut_rec in csr_fut_ppm loop
4017 hr_utility.set_location('ppm_id '||l_fut_rec.personal_payment_method_id,60);
4018 --
4019 --delete future-only PPMs, last loop removed future DT rows of current PPMs
4020 --
4021 hr_personal_pay_method_api.delete_personal_pay_method
4022 (p_effective_date => l_fut_rec.effective_start_date
4023 ,p_datetrack_delete_mode => 'ZAP'
4024 ,p_personal_payment_method_id => l_fut_rec.personal_payment_method_id
4025 ,p_object_version_number => l_fut_rec.object_version_number
4026 ,p_effective_start_date => l_effective_start_date
4027 ,p_effective_end_date => l_effective_end_date
4028 );
4029 end loop;
4030 END;
4031 --
4032 hr_utility.set_location(l_proc,70);
4033 --
4034 -- End Date any current Assignment Rates
4035 --
4036 FOR crec_current_asg_rates IN csr_current_asg_rates LOOP
4037 --
4038 hr_utility.set_location(l_proc||'/'||crec_current_asg_rates.grade_rule_id,80);
4039 --
4040 hr_rate_values_api.delete_rate_value
4041 (p_validate => FALSE
4042 ,p_grade_rule_id => crec_current_asg_rates.grade_rule_id
4043 ,p_datetrack_mode => hr_api.g_delete
4044 ,p_effective_date => p_new_end_date
4045 ,p_object_version_number => crec_current_asg_rates.object_version_number
4046 ,p_effective_start_date => l_effective_start_date
4047 ,p_effective_end_date => l_effective_end_date);
4048 --
4049 END LOOP;
4050 --
4051 hr_utility.set_location(l_proc,90);
4052 --
4053 -- Delete any future dated assignment rates if we are Ending the Assignment.
4054 --
4055 FOR crec_future_asg_rates IN csr_future_asg_rates LOOP
4056 --
4057 hr_utility.set_location(l_proc||'/'||crec_future_asg_rates.grade_rule_id,100);
4058 --
4059 hr_rate_values_api.delete_rate_value
4060 (p_validate => FALSE
4061 ,p_grade_rule_id => crec_future_asg_rates.grade_rule_id
4062 ,p_datetrack_mode => hr_api.g_zap
4063 ,p_effective_date => crec_future_asg_rates.effective_start_date
4064 ,p_object_version_number => crec_future_asg_rates.object_version_number
4065 ,p_effective_start_date => l_effective_start_date
4066 ,p_effective_end_date => l_effective_end_date);
4067 --
4068 END LOOP;
4069 --
4070 hr_utility.set_location('Leaving : '||l_proc,999);
4071 --
4072 EXCEPTION
4073 when others then
4074 p_cost_warning := null ;
4075 RAISE ;
4076 --
4077 END tidy_up_ref_int;
4078 --
4079 --
4080 ------------------- call_terminate_entries --------------------
4081 /*
4082 NAME
4083 call_terminate_entries
4084 DESCRIPTION
4085 This procedure determines the Actual Termination Date, Last Standard
4086 Processing Date and Final Process Date in order to terminate element
4087 entries and ALUs when an individual assignment is terminated or ended.
4088
4089 There are several cases :-
4090
4091 i. Status is END and there are no prior TERM_ASSIGNs
4092 => ATD = Session date
4093 LSD = Session date
4094 FPD = Session date
4095
4096 ii. Status is END and there is a prior TERM_ASSIGN
4097 => ATD = NULL
4098 LSD = NULL
4099 FPD = Session Date
4100
4101 iii. Status is TERM_ASSIGN and there are no prior TERM_ASSIGNs
4102 => ATD = Validation Start Date - 1
4103 LSD = (IF Assignment has Payroll then END_DATE of current
4104 processing period
4105 ELSE
4106 Validation Start Date - 1)
4107 FPD = NULL
4108
4109 iv. Status is TERM_ASSIGN and there is a prior TERM_ASSIGN
4110 => No processing required
4111
4112 PARAMETERS
4113 p_assignment_id - Assignment ID
4114 p_status - 'END' or 'TERM_ASSIGN'
4115 p_start_date - Validation Start Date for TERM_ASSIGN or
4116 Session Date for 'END'
4117 */
4118 --
4119 PROCEDURE call_terminate_entries
4120 (P_ASSIGNMENT_ID IN NUMBER
4121 ,P_STATUS IN VARCHAR2
4122 ,P_START_DATE IN DATE
4123 ) IS
4124 --
4125 p_actual_term_date DATE;
4126 p_last_standard_date DATE;
4127 p_final_process_date DATE;
4128 --
4129 -- VT 10/08/96 bug #306710 new local variable
4130 l_entries_changed VARCHAR2(1) := 'N';
4131 --
4132 FUNCTION previous_term_exists
4133 ( p_assignment_id NUMBER
4134 , p_start_date DATE
4135 ) RETURN BOOLEAN IS
4136 --
4137 term_exists VARCHAR2(1) := 'N';
4138 ------------------------
4139 BEGIN
4140 -- This function returns TRUE if a TERM_ASSIGN status exists earlier than the
4141 -- date we are considering.
4142 --
4143 hr_utility.set_location('peassign.call_terminate_entries',1);
4144 --
4145 BEGIN
4146 select 'Y'
4147 into term_exists
4148 from sys.dual
4149 where exists
4150 (select null
4151 from per_assignments_f a
4152 , per_assignment_status_types s
4153 where a.assignment_id = p_assignment_id
4154 and a.effective_start_date < p_start_date
4155 and a.assignment_status_type_id = s.assignment_status_type_id
4156 and s.per_system_status = 'TERM_ASSIGN');
4157 EXCEPTION
4158 WHEN NO_DATA_FOUND THEN NULL;
4159 END;
4160 --
4161 RETURN (term_exists = 'Y');
4162 --
4163 END previous_term_exists;
4164 -------------------------
4165 BEGIN
4166 --
4167 hr_utility.set_location('peassign.call_terminate_entries',2);
4168 IF P_STATUS = 'END' THEN
4169 --
4170 IF previous_term_exists(p_assignment_id
4171 ,p_start_date) THEN
4172 p_actual_term_date := NULL;
4173 p_last_standard_date := NULL;
4174 p_final_process_date := p_start_date;
4175 ELSE
4176 p_actual_term_date := p_start_date;
4177 p_last_standard_date := p_start_date;
4178 p_final_process_date := p_start_date;
4179 END IF; -- IF previous_term_exists(....
4180 --
4181 hr_utility.set_location('peassign.call_terminate_entries',3);
4182 -- VT 10/08/96 bug #306710 added parameter
4183 hrempter.terminate_entries_and_alus(p_assignment_id
4184 ,p_actual_term_date
4185 ,p_last_standard_date
4186 ,p_final_process_date
4187 ,null
4188 ,l_entries_changed);
4189 --
4190 ELSE -- (IF p_status = 'TERM_ASSIGN')
4191 --
4192 hr_utility.set_location('peassign.call_terminate_entries',4);
4193 IF previous_term_exists(p_assignment_id
4194 ,p_start_date) THEN NULL;
4195 ELSE
4196 p_actual_term_date := p_start_date -1;
4197 p_last_standard_date := p_start_date -1;
4198 p_final_process_date := NULL;
4199 --
4200 hr_utility.set_location('peassign.call_terminate_entries',5);
4201 BEGIN
4202 select tp.end_date
4203 into p_last_standard_date
4204 from per_assignments_f a
4205 , per_time_periods tp
4206 where a.assignment_id = p_assignment_id
4207 and a.effective_end_date = p_start_date - 1
4208 and a.payroll_id = tp.payroll_id
4209 and p_start_date - 1 between tp.start_date and tp.end_date;
4210 EXCEPTION
4211 WHEN NO_DATA_FOUND THEN NULL;
4212 END;
4213 --
4214 hr_utility.set_location('peassign.call_terminate_entries',6);
4215 -- VT 10/08/96 bug #306710 added parameter
4216 hrempter.terminate_entries_and_alus(p_assignment_id
4217 ,p_actual_term_date
4218 ,p_last_standard_date
4219 ,p_final_process_date
4220 ,null
4221 ,l_entries_changed);
4222 --
4223 END IF; -- IF previous_term_exists( ...
4224 --
4225 END IF; -- IF P_STATUS = 'END'
4226 --
4227 END call_terminate_entries;
4228 --
4229 ------------ test_for_cancel_reterm ------------------------------------
4230 /*
4231 This procedure works out nocopy whether a Cancel or retermination is required
4232 follwoing an operation that affects the "leading TERM_ASSIGN" status
4233 */
4234 procedure test_for_cancel_reterm
4235 (p_assignment_id in number
4236 ,p_validation_start_date in date
4237 ,p_validation_end_date in date
4238 ,p_mode in varchar2
4239 ,p_current_status_type in varchar2
4240 ,p_old_status_type in varchar2
4241 ,p_cancel_atd in out nocopy date
4242 ,p_cancel_lspd in out nocopy date
4243 ,p_reterm_atd in out nocopy date
4244 ,p_reterm_lspd in out nocopy date
4245 ) is
4246 --
4247 l_leading_date DATE;
4248 l_new_leading_date DATE;
4249 --
4250 l_cancel_atd date := p_cancel_atd ;
4251 l_cancel_lspd date := p_cancel_lspd ;
4252 l_reterm_atd date := p_reterm_atd ;
4253 l_reterm_lspd date := p_reterm_lspd;
4254 --
4255 function leading_term_assign(l_ignore_val_start_date varchar2)
4256 return boolean is
4257 begin
4258 select min(a.effective_start_date)
4259 into l_leading_date
4260 from per_assignments_f a
4261 , per_assignment_status_types s
4262 where a.assignment_id = p_assignment_id
4263 and (l_ignore_val_start_date = 'N' or
4264 (l_ignore_val_start_date = 'Y' and
4265 effective_start_date <> p_validation_start_date ))
4266 and a.assignment_status_type_id = s.assignment_status_type_id
4267 and s.per_system_status = 'TERM_ASSIGN';
4268 --
4269 return(l_leading_date is not null);
4270 end leading_term_assign;
4271 --
4272 function get_lspd(l_default_lspd date) return date is
4273 l_new_lspd date;
4274 begin
4275 l_new_lspd := l_default_lspd;
4276 begin
4277 select tp.end_date
4278 into l_new_lspd
4279 from per_assignments_f a
4280 , per_time_periods tp
4281 where a.assignment_id = p_assignment_id
4282 and a.effective_end_date = l_default_lspd
4283 and a.payroll_id = tp.payroll_id
4284 and l_default_lspd between tp.start_date and tp.end_date;
4285 exception
4286 WHEN NO_DATA_FOUND THEN NULL;
4287 end;
4288 --
4289 return(l_new_lspd);
4290 end;
4291 --
4292 procedure cancel_required is
4293 /*----------------------------------------------------------------
4294 This procedure sets the ATD and LSPD of the leading TERM_ASSIGN
4295 so that POST-COMMIT cancellation can use them.
4296
4297 N.B. Strictly the FPD should also be set in the case when the operation
4298 is removing the assignment END date. However this is complicated
4299 by the fact that under certain circumstances the END date may be
4300 automatically moved to be at the ATD (see
4301 hr_assignment.check_term).
4302
4303 This is not too problematic because a call to
4304 maintain_entries_asg will end any entries that are left open
4305 incorrectly.
4306 -----------------------------------------------------------------*/
4307 begin
4308 p_cancel_atd := l_leading_date - 1;
4309 p_cancel_lspd := get_lspd(l_leading_date - 1);
4310 end cancel_required;
4311 --
4312 begin
4313 --
4314 /*----------------------------------------------------------------
4315 Firstly do the check to see if CANCEL is required
4316 -----------------------------------------------------------------*/
4317
4318 /*----------------------------------------------------------------
4319 if the mode is CORRECTION,UPDATE,UPDATE-OVERRIDE or UPDATE_INSERT
4320 AND
4321 if the current assignment status type is TERM_ASSIGN and the old
4322 assignment status type is not TERM_ASSIGN i.e. this operation is actually
4323 altering the assignment status type
4324 AND
4325 there is a leading TERM_ASSIGN out nocopy of all the TERM_ASSIGNS other
4326 than the one currently being created
4327
4328 then if this leading TERM_ASSIGN is after the validation start date
4329 of the current modification then cancellation of entries
4330 will be required
4331
4332 ELSE
4333
4334 if the mode is UPDATE-OVERRIDE
4335 AND
4336 the assignment status is not being changed by this operation
4337 AND
4338 there is a leading TERM_ASSIGN other out nocopy of all the TERM_ASSIGNS
4339
4340 then if this leading TERM_ASSIGN is after the validation start
4341 date of the current operation then cancellation of entries
4342 will be required
4343
4344 ELSE
4345
4346 if the mode is DELETE-NEXT-CHANGE or FUTURE-CHANGES-DELETE
4347 AND
4348 there is a leading TERM_ASSIGN other out nocopy of all the TERM_ASSIGNS
4349
4350 then if this leading TERM_ASSIGN is on or after the validation start
4351 date of the current operation then cancellation of entries
4352 will be required
4353 -----------------------------------------------------------------*/
4354 --
4355 hr_utility.set_location('hr_assignment.test_for_cancel_reterm',1);
4356 if p_mode in ('CORRECTION'
4357 ,'UPDATE'
4358 ,'UPDATE_OVERRIDE'
4359 ,'UPDATE_CHANGE_INSERT' )
4360 and p_current_status_type = 'TERM_ASSIGN'
4361 and p_current_status_type <> p_old_status_type
4362 and leading_term_assign('Y') then
4363 --
4364 if l_leading_date > p_validation_start_date then
4365 cancel_required;
4366 end if;
4367 --
4368 elsif
4369 --
4370 p_mode = 'UPDATE_OVERRIDE'
4371 and p_current_status_type <> 'TERM_ASSIGN'
4372 and leading_term_assign('N') then
4373 --
4374 if l_leading_date > p_validation_start_date then
4375 cancel_required;
4376 end if;
4377 --
4378 elsif
4379 --
4380 p_mode in ('DELETE_NEXT_CHANGE'
4381 ,'FUTURE_CHANGE')
4382 and leading_term_assign('N') then
4383 --
4384 if l_leading_date between p_validation_start_date
4385 and p_validation_end_date then
4386 cancel_required;
4387 --
4388 /*-----------------------------------------------------------------
4389 Now do the check to see if RE-TERMINATION is required
4390 -----------------------------------------------------------------*/
4391 --
4392 /*-----------------------------------------------------------------
4393 If the operation is a DELETE_NEXT_CHANGE that will remove the
4394 leading TERM_ASSIGN and leave a subsequent TERM_ASSIGN as the
4395 new leading one then the re-termination process is required
4396 as though the new leaading TERM_ASSIGN were being created for
4397 the first time
4398
4399 N.B. This is only necessary if the leading TERM_ASSIGN has been
4400 removed (i.e. if all the conditions for CANCEL_REQUIRED to
4401 be called are met)
4402 -----------------------------------------------------------------*/
4403 --
4404 if p_mode = 'DELETE_NEXT_CHANGE' then
4405 begin
4406 select min(a.effective_start_date)
4407 into l_new_leading_date
4408 from per_assignments_f a
4409 , per_assignment_status_types s
4410 where a.assignment_id = p_assignment_id
4411 and effective_start_date > p_validation_end_date
4412 and a.assignment_status_type_id = s.assignment_status_type_id
4413 and s.per_system_status = 'TERM_ASSIGN';
4414 --
4415 p_reterm_atd := l_new_leading_date - 1;
4416 p_reterm_lspd := get_lspd(l_new_leading_date - 1);
4417 --
4418 exception
4419 when no_data_found then null;
4420 end;
4421 end if;
4422 --
4423 end if;
4424 --
4425 end if;
4426 --
4427 EXCEPTION
4428 when others then
4429 p_cancel_atd := l_cancel_atd ;
4430 p_cancel_lspd := l_cancel_lspd ;
4431 p_reterm_atd := l_reterm_atd ;
4432 p_reterm_lspd := l_reterm_lspd ;
4433 RAISE;
4434
4435 end test_for_cancel_reterm;
4436 --
4437 --
4438 -----------------------------------------------------------------------
4439 -- check_for_cobra
4440 --
4441 -- This procedure checks to see if there are COBRA Enrollments
4442 -- that have a Qualifying Date on Termination Date + 1 (i.e. Enrollment
4443 -- is as a result of the termination)
4444 --
4445 -- If this Termination will be removed as a result of the operation
4446 -- then issue a warning stating that COBRA Coverage may no longer be
4447 -- applicable
4448 --
4449 PROCEDURE check_for_cobra
4450 (p_assignment_id IN INTEGER
4451 ,p_sdate IN DATE
4452 ,p_edate IN DATE
4453 ) IS
4454 --
4455 l_cobra_term_exists VARCHAR2(1);
4456 local_warning exception;
4457 --
4458 BEGIN
4459 hr_utility.set_location('hr_assignment.check_for_cobra',1);
4460 BEGIN
4461 select 'Y'
4462 into l_cobra_term_exists
4463 from sys.dual
4464 where exists
4465 (select null
4466 from per_cobra_cov_enrollments e
4467 where e.assignment_id = p_assignment_id
4468 and exists
4469 (select null
4470 from per_assignments_f a
4471 where a.assignment_id = p_assignment_id
4472 and a.effective_start_date between p_sdate and p_edate
4473 and exists ( select null
4474 from per_assignment_status_types s
4475 where s.assignment_status_type_id
4476 = a.assignment_status_type_id
4477 and s.per_system_status = 'TERM_ASSIGN')
4478 and a.effective_start_date + 1 = e.qualifying_date));
4479 EXCEPTION
4480 WHEN NO_DATA_FOUND THEN NULL;
4481 END;
4482 --
4483 hr_utility.set_location('hr_assignment.check_for_cobra',2);
4484 if l_cobra_term_exists = 'Y' then
4485 raise local_warning;
4486 end if;
4487 --
4488 EXCEPTION
4489 when local_warning then
4490 hr_utility.set_warning;
4491 END check_for_cobra;
4492 --
4493 -----------------------------------------------------------------------
4494 -- validate_pos
4495 --
4496 -- This procedure is called from hr_chg_date.call_session_date to ensure
4497 -- that a new session date that is being set in PERWSEMA does not lie
4498 -- outside the bounds of a Period of Service or Period of Placement.
4499 --
4500 PROCEDURE validate_pos
4501 (p_person_id IN VARCHAR2
4502 ,p_new_date IN VARCHAR2)
4503 IS
4504
4505 l_proc VARCHAR2(80) := g_package||'validate_pos';
4506 l_dummy_dt DATE;
4507 l_pos_id NUMBER;
4508
4509 CURSOR get_pos IS
4510 SELECT p.date_start date_start, p.period_of_service_id
4511 FROM per_periods_of_service p
4512 WHERE p.person_id = to_number(p_person_id)
4513 AND fnd_date.canonical_to_date(p_new_date) BETWEEN
4514 p.date_start AND NVL(p.final_process_date, hr_api.g_eot)
4515 UNION
4516 SELECT pdp.date_start date_start, pdp.period_of_placement_id
4517 FROM per_periods_of_placement pdp
4518 WHERE pdp.person_id = to_number(p_person_id)
4519 AND fnd_date.canonical_to_date(p_new_date) BETWEEN
4520 pdp.date_start AND NVL(pdp.final_process_date, hr_api.g_eot)
4521 ORDER BY date_start DESC;
4522
4523 BEGIN
4524
4525 hr_utility.set_location('Entering: '||l_proc, 10);
4526
4527 OPEN get_pos;
4528 FETCH get_pos INTO l_dummy_dt, l_pos_id;
4529 CLOSE get_pos;
4530
4531 hr_utility.trace('l_pos_id: '||to_char(l_pos_id));
4532
4533 IF l_pos_id IS NULL THEN
4534 hr_utility.set_message(801,'HR_6346_EMP_ASS_NO_POS');
4535 hr_utility.raise_error;
4536 END IF;
4537
4538 hr_utility.set_location('Leaving: '||l_proc, 40);
4539
4540 END validate_pos;
4541 --
4542 --
4543 function per_dflt_asg_cost_alloc_ff(
4544 p_assignment_id number,
4545 p_business_group_id number,
4546 p_position_id number,
4547 p_effective_date date) return varchar2 is
4548 l_session_date date;
4549 l_formula_id number;
4550 l_inputs ff_exec.inputs_t;
4551 l_outputs ff_exec.outputs_t;
4552 i number;
4553 l_cost_allocation_id number;
4554 l_cost_allocation_keyflex_id number;
4555 l_proportion number;
4556 l_combination_name varchar2(2000);
4557 l_effective_start_date date;
4558 l_effective_end_date date;
4559 l_object_version_number number;
4560 l_use_formula varchar2(30) := 'N';
4561 l_formula_name varchar2(100):= 'PER_DFLT_ASG_COST_ALLOCATION';
4562 --
4563 type t_asg_cost_rec is record (
4564 cost_allocation_keyflex_id number, proportion number
4565 );
4566 --
4567 type t_asg_cost_table is table of t_asg_cost_rec index by binary_integer;
4568 --
4569 l_rec t_asg_cost_table;
4570 --
4571 cursor c_formula_id(p_formula_name varchar2, p_business_group_id number,
4572 p_effective_date date) is
4573 select ff.formula_id
4574 from ff_formulas_f ff where upper(ff.formula_name) = p_formula_name
4575 and business_group_id = p_business_group_id
4576 and p_effective_date between effective_start_date and effective_end_date;
4577 --
4578 cursor c_session_date is
4579 select effective_date
4580 from fnd_sessions
4581 where session_id = userenv('sessionid');
4582 --
4583 --
4584 cursor c_cost_allocation_keyflex(p_cost_allocation_keyflex_id number) is
4585 select *
4586 from pay_cost_allocation_keyflex
4587 where cost_allocation_keyflex_id = p_cost_allocation_keyflex_id
4588 and to_char(id_flex_num) in (select cost_allocation_structure
4589 from per_business_groups
4590 where business_group_id = p_business_group_id);
4591 --
4592 begin
4593 --
4594 hr_utility.set_location('Entering hr_assignment.per_dflt_asg_cost_alloc_ff',25);
4595 --
4596 open c_formula_id(l_formula_name, p_business_group_id, p_effective_date);
4597 fetch c_formula_id into l_formula_id;
4598 --
4599 hr_utility.set_location('hr_assignment.per_dflt_asg_cost_alloc_ff',26);
4600 --
4601 if c_formula_id%notfound then
4602 hr_utility.set_location('hr_assignment.per_dflt_asg_cost_alloc_ff',27);
4603 close c_formula_id;
4604 return 'N';
4605 end if;
4606 hr_utility.set_location('hr_assignment.per_dflt_asg_cost_alloc_ff',28);
4607 close c_formula_id;
4608 hr_utility.set_location('hr_assignment.per_dflt_asg_cost_alloc_ff-formula_id :'
4609 || l_formula_id,29);
4610 -- Insert fnd_sessions row
4611 open c_session_date;
4612 fetch c_session_date into l_session_date;
4613 hr_utility.set_location('hr_assignment.per_dflt_asg_cost_alloc_ff' || l_session_date,30);
4614 if c_session_date%notfound then
4615 hr_utility.set_location('hr_assignment.per_dflt_asg_cost_alloc_ff',31);
4616 insert into fnd_sessions (SESSION_ID, EFFECTIVE_DATE) values(userenv('sessionid'), trunc(p_effective_date));
4617 hr_utility.set_location('hr_assignment.per_dflt_asg_cost_alloc_ff',32);
4618 end if;
4619 --
4620 hr_utility.set_location('hr_assignment.per_dflt_asg_cost_alloc_ff',33);
4621 -- Initialize the formula
4622 ff_exec.init_formula(l_formula_id,p_effective_date, l_inputs, l_outputs);
4623 --
4624 hr_utility.set_location('hr_assignment.per_dflt_asg_cost_alloc_ff',34);
4625 --
4626 --set the inputs
4627 --
4628 for i in nvl(l_inputs.first,0) .. nvl(l_inputs.last,-1) loop
4629 if l_inputs(i).name = 'ASSIGNMENT_ID' then
4630 l_inputs(i).value := p_assignment_id;
4631 elsif l_inputs(i).name = 'BUSSINESS_GROUP_ID' then
4632 l_inputs(i).value := p_business_group_id;
4633 elsif l_inputs(i).name = 'POSITION_ID' then
4634 l_inputs(i).value := p_position_id;
4635 elsif l_inputs(i).name = 'EFFECTIVE_DATE' then
4636 l_inputs(i).value := trunc(p_effective_date);
4637 end if;
4638 end loop;
4639 --
4640 hr_utility.set_location('hr_assignment.per_dflt_asg_cost_alloc_ff',35);
4641 --
4642 ff_exec.run_formula(l_inputs, l_outputs);
4643 --
4644 hr_utility.set_location('hr_assignment.per_dflt_asg_cost_alloc_ff',36);
4645 --
4646 for i in nvl(l_outputs.first,0) .. nvl(l_outputs.last,-1) loop
4647 if (l_outputs(i).name = 'USE_FORMULA') then
4648 hr_utility.set_location('hr_assignment.per_dflt_asg_cost_alloc_ff-'|| l_outputs(i).value,361);
4649 l_use_formula := nvl(l_outputs(i).value,'N');
4650 if (l_use_formula <> 'Y')then
4651 return 'N';
4652 end if;
4653 elsif (substr(l_outputs(i).name,1,26) = 'COST_ALLOCATION_KEYFLEX_ID') then
4654 hr_utility.set_location('hr_assignment.per_dflt_asg_cost_alloc_ff-'|| l_outputs(i).value,362);
4655 l_rec(to_number(substr(l_outputs(i).name,27))).cost_allocation_keyflex_id := l_outputs(i).value;
4656 elsif (substr(l_outputs(i).name,1,10) = 'PROPORTION') then
4657 hr_utility.set_location('hr_assignment.per_dflt_asg_cost_alloc_ff-'|| l_outputs(i).value,363);
4658 l_rec(to_number(substr(l_outputs(i).name,11))).PROPORTION := l_outputs(i).value;
4659 end if;
4660 end loop;
4661 --
4662 hr_utility.set_location('hr_assignment.per_dflt_asg_cost_alloc_ff',37);
4663 --
4664 if l_use_formula = 'Y' then
4665 for i in nvl(l_rec.first,0) .. nvl(l_rec.last,-1) loop
4666 hr_utility.set_location('hr_assignment.per_dflt_asg_cost_alloc_ff l_rec(i).cost_all_kf_id'
4667 || l_rec(i).cost_allocation_keyflex_id,381);
4668 hr_utility.set_location('hr_assignment.per_dflt_asg_cost_alloc_ff l_rec(i).proportion'
4669 || l_rec(i).proportion,382);
4670 if (nvl(l_rec(i).cost_allocation_keyflex_id, -1) <> -1) then
4671 --
4672 --
4673 hr_utility.set_location('hr_assignment.per_dflt_asg_cost_alloc_ff - l_rec(i).proportion:'||l_rec(i).proportion,313);
4674 hr_utility.set_location('hr_assignment.per_dflt_asg_cost_alloc_ff - l_rec(i).cost_kf_id:'||l_rec(i).cost_allocation_keyflex_id,314);
4675 --
4676 l_proportion := trunc(l_rec(i).proportion,4);
4677 --
4678 hr_utility.set_location('hr_assignment.per_dflt_asg_cost_alloc_ff - l_proportion1 :'||l_proportion,315);
4679 if l_proportion > 1 then
4680 l_proportion := 1;
4681 end if;
4682 hr_utility.set_location('hr_assignment.per_dflt_asg_cost_alloc_ff - l_proportion2 :'||l_proportion,316);
4683 --
4684 if l_proportion > 0 then
4685 for r3 in c_cost_allocation_keyflex(l_rec(i).cost_allocation_keyflex_id) loop
4686 hr_utility.set_location('hr_assignment.per_dflt_asg_cost_alloc_ff',39);
4687 --
4688 pay_cost_allocation_api.create_cost_allocation(
4689 p_validate =>false ,
4690 p_effective_date =>p_effective_date,
4691 p_assignment_id =>p_assignment_id,
4692 p_cost_allocation_id =>l_cost_allocation_id,
4693 p_business_group_id =>p_business_group_id,
4694 p_combination_name =>l_combination_name,
4695 p_cost_allocation_keyflex_id =>l_cost_allocation_keyflex_id,
4696 p_proportion =>l_proportion,
4697 p_effective_start_date =>l_effective_start_date,
4698 p_effective_end_date =>l_effective_end_date,
4699 p_object_version_number =>l_object_version_number,
4700 p_segment1 =>r3.segment1,
4701 p_segment2 =>r3.segment2,
4702 p_segment3 =>r3.segment3,
4703 p_segment4 =>r3.segment4,
4704 p_segment5 =>r3.segment5,
4705 p_segment6 =>r3.segment6,
4706 p_segment7 =>r3.segment7,
4707 p_segment8 =>r3.segment8,
4708 p_segment9 =>r3.segment9,
4709 p_segment10 =>r3.segment10,
4710 p_segment11 =>r3.segment11,
4711 p_segment12 =>r3.segment12,
4712 p_segment13 =>r3.segment13,
4713 p_segment14 =>r3.segment14,
4714 p_segment15 =>r3.segment15,
4715 p_segment16 =>r3.segment16,
4716 p_segment17 =>r3.segment17,
4717 p_segment18 =>r3.segment18,
4718 p_segment19 =>r3.segment19,
4719 p_segment20 =>r3.segment20,
4720 p_segment21 =>r3.segment21,
4721 p_segment22 =>r3.segment22,
4722 p_segment23 =>r3.segment23,
4723 p_segment24 =>r3.segment24,
4724 p_segment25 =>r3.segment25,
4725 p_segment26 =>r3.segment26,
4726 p_segment27 =>r3.segment27,
4727 p_segment28 =>r3.segment28,
4728 p_segment29 =>r3.segment29,
4729 p_segment30 =>r3.segment30,
4730 p_concat_segments =>r3.concatenated_segments
4731 );
4732 end loop;
4733 hr_utility.set_location('hr_assignment.per_dflt_asg_cost_alloc_ff',391);
4734 --
4735 end if;
4736 --
4737 hr_utility.set_location('hr_assignment.per_dflt_asg_cost_alloc_ff',40);
4738 --
4739 end if;
4740 --
4741 hr_utility.set_location('hr_assignment.per_dflt_asg_cost_alloc_ff',41);
4742 --
4743 end loop;
4744 --
4745 hr_utility.set_location('hr_assignment.per_dflt_asg_cost_alloc_ff',42);
4746 --
4747 return 'Y';
4748 --
4749 end if;
4750 --
4751 hr_utility.set_location('Leaving hr_assignment.per_dflt_asg_cost_alloc_ff',43);
4752 --
4753 return 'N';
4754 --
4755 end;
4756 --
4757 PROCEDURE load_assignment_allocation
4758 (p_assignment_id IN INTEGER
4759 ,p_business_group_id IN INTEGER
4760 ,p_effective_date IN DATE
4761 ,p_position_id in number) IS
4762 --
4763 l_row_id varchar2(100);
4764 l_cost_allocation_id number(20);
4765 l_cost_allocation_keyflex_id number;
4766 l_money varchar2(10) := 'MONEY';
4767 l_unit1 varchar2(30);
4768 l_unit2 varchar2(30);
4769 l_unit3 varchar2(30);
4770 l_period_value number;
4771 l_period1_value number;
4772 l_period2_value number;
4773 l_period3_value number;
4774 l_combination_name varchar2(2000);
4775 l_effective_start_date date;
4776 l_effective_end_date date;
4777 l_object_version_number number;
4778 l_dummy varchar2(30);
4779 l_proportion number;
4780 --
4781 cursor c_asg_cost_allocations(p_assignment_id number) is
4782 select 'x'
4783 from pay_cost_allocations_f
4784 where assignment_id = p_assignment_id;
4785 --
4786 cursor c_cost_allocation_keyflex(p_cost_allocation_keyflex_id number) is
4787 select *
4788 from pay_cost_allocation_keyflex
4789 where cost_allocation_keyflex_id = p_cost_allocation_keyflex_id
4790 and to_char(id_flex_num) in (select cost_allocation_structure
4791 from per_business_groups
4792 where business_group_id = p_business_group_id);
4793 --
4794 --
4795 cursor c0 is
4796 select *
4797 from (
4798 select
4799 hr_psf_shd.system_availability_status(budget_unit1_id) unit1,
4800 hr_psf_shd.system_availability_status(budget_unit2_id) unit2,
4801 hr_psf_shd.system_availability_status(budget_unit3_id) unit3
4802 from pqh_budgets
4803 where p_effective_date between budget_start_date and budget_end_date
4804 and position_control_flag = 'Y'
4805 and budgeted_entity_cd = 'POSITION'
4806 and business_group_id = p_business_group_id
4807 )
4808 where unit1 = 'MONEY' or unit2 = 'MONEY' or unit3 = 'MONEY';
4809 --
4810 cursor c1(unit1 varchar2, unit2 varchar2, unit3 varchar2) is
4811 select src.cost_allocation_keyflex_id,
4812 sum((decode(unit1,l_money,nvl(bset.budget_unit1_value,0),0)
4813 + decode(unit2,l_money,nvl(bset.budget_unit2_value,0),0)
4814 + decode(unit3,l_money,nvl(bset.budget_unit3_value,0),0))
4815 * (ele.distribution_percentage * src.distribution_percentage/10000)) proportion
4816 from pqh_budget_fund_srcs src, pqh_budget_elements ele, pqh_budget_sets bset,
4817 pqh_budget_periods per, pqh_budget_details det, per_time_periods stp, per_time_periods etp
4818 where
4819 det.position_id = p_position_id and
4820 det.budget_detail_id= per.budget_detail_id
4821 and per.budget_period_id = bset.budget_period_id
4822 and per.start_time_period_id = stp.time_period_id
4823 and per.end_time_period_id = etp.time_period_id
4824 and p_effective_date
4825 between stp.start_date and etp.end_date
4826 and bset.budget_set_id = ele.budget_set_id
4827 and ele.budget_element_id = src.budget_element_id
4828 and src.cost_allocation_keyflex_id is not null
4829 and det.budget_version_id in
4830 (select budget_version_id
4831 from pqh_budget_versions ver, pqh_budgets bgt
4832 where ver.budget_id = bgt.budget_id
4833 and bgt.position_control_flag = 'Y'
4834 and bgt.budgeted_entity_cd = 'POSITION'
4835 and bgt.business_group_id = p_business_group_id
4836 and p_effective_date
4837 between ver.date_from and ver.date_to
4838 and p_effective_date
4839 between bgt.budget_start_date and bgt.budget_end_date
4840 and (hr_psf_shd.system_availability_status(budget_unit1_id) = 'MONEY'
4841 or hr_psf_shd.system_availability_status(budget_unit2_id) = 'MONEY'
4842 or hr_psf_shd.system_availability_status(budget_unit3_id) = 'MONEY')
4843 )
4844 group by src.cost_allocation_keyflex_id;
4845 --
4846 cursor c2(unit1 varchar2, unit2 varchar2, unit3 varchar2) is
4847 select
4848 decode(unit1,l_money,nvl(per.budget_unit1_value,0),0)+
4849 decode(unit2,l_money,nvl(per.budget_unit2_value,0),0)+
4850 decode(unit3,l_money,nvl(per.budget_unit3_value,0),0) period_value
4851 from pqh_budget_periods per, pqh_budget_details det,
4852 per_time_periods stp, per_time_periods etp
4853 where
4854 det.position_id = p_position_id and
4855 det.budget_detail_id= per.budget_detail_id
4856 and per.start_time_period_id = stp.time_period_id
4857 and per.end_time_period_id = etp.time_period_id
4858 and p_effective_date
4859 between stp.start_date and etp.end_date
4860 and det.budget_version_id in
4861 (select budget_version_id
4862 from pqh_budget_versions ver, pqh_budgets bgt
4863 where ver.budget_id = bgt.budget_id
4864 and bgt.position_control_flag = 'Y'
4865 and bgt.budgeted_entity_cd = 'POSITION'
4866 and bgt.business_group_id = p_business_group_id
4867 and p_effective_date
4868 between ver.date_from and ver.date_to
4869 and p_effective_date
4870 between bgt.budget_start_date and bgt.budget_end_date
4871 and (hr_psf_shd.system_availability_status(budget_unit1_id) = 'MONEY'
4872 or hr_psf_shd.system_availability_status(budget_unit2_id) = 'MONEY'
4873 or hr_psf_shd.system_availability_status(budget_unit3_id) = 'MONEY')
4874 );
4875 --
4876 BEGIN
4877 hr_utility.set_location('hr_assignment.load_assignment_allocation',1);
4878 --
4879 if nvl(fnd_profile.value('HR_DEFAULT_ASG_COST_ALLOC'),'N') <> 'Y' then
4880 return;
4881 end if;
4882 --
4883 hr_utility.set_location('hr_assignment.load_assignment_allocation',2);
4884 --
4885 if p_position_id is null then
4886 return;
4887 end if;
4888 --
4889 hr_utility.set_location('hr_assignment.load_assignment_allocation',3);
4890 hr_utility.set_location('hr_assignment.load_assignment_allocation - effec date :'|| p_effective_date,3);
4891 hr_utility.set_location('hr_assignment.load_assignment_allocation - p_position_id :'|| p_position_id,3);
4892 hr_utility.set_location('hr_assignment.load_assignment_allocation - assignment_id :'|| p_assignment_id,3);
4893 hr_utility.set_location('hr_assignment.load_assignment_allocation - business_group_id :'|| p_business_group_id,3);
4894 --
4895 open c_asg_cost_allocations(p_assignment_id);
4896 fetch c_asg_cost_allocations into l_dummy;
4897 if c_asg_cost_allocations%found then
4898 hr_utility.set_location('hr_assignment.load_assignment_allocation',4);
4899 return;
4900 end if;
4901 --
4902 hr_utility.set_location('hr_assignment.load_assignment_allocation',5);
4903 --
4904 if (per_dflt_asg_cost_alloc_ff(p_assignment_id, p_business_group_id, p_position_id, p_effective_date) = 'Y') then
4905 hr_utility.set_location('hr_assignment.load_assignment_allocation',6);
4906 return;
4907 end if;
4908 --
4909 hr_utility.set_location('hr_assignment.load_assignment_allocation',7);
4910 --
4911 open c0;
4912 fetch c0 into l_unit1, l_unit2, l_unit3;
4913 close c0;
4914 --
4915 hr_utility.set_location('hr_assignment.load_assignment_allocation - ' || l_unit1,8);
4916 hr_utility.set_location('hr_assignment.load_assignment_allocation - ' || l_unit2,9);
4917 hr_utility.set_location('hr_assignment.load_assignment_allocation - ' || l_unit3,10);
4918 --
4919 open c2(l_unit1, l_unit2, l_unit3);
4920 fetch c2 into l_period_value;
4921 close c2;
4922 --
4923 hr_utility.set_location('hr_assignment.load_assignment_allocation - '|| l_period_value,11);
4924 --
4925 if nvl(l_period_value,0) <> 0 then
4926 --
4927 hr_utility.set_location('hr_assignment.load_assignment_allocation ',12);
4928 --
4929 for r1 in c1(l_unit1, l_unit2, l_unit3) loop
4930 --
4931 hr_utility.set_location('hr_assignment.load_assignment_allocation - '||r1.proportion,13);
4932 hr_utility.set_location('hr_assignment.load_assignment_allocation - '||r1.cost_allocation_keyflex_id,14);
4933 --
4934 l_proportion := trunc(r1.proportion/l_period_value,4);
4935 --
4936 if l_proportion > 1 then
4937 l_proportion := 1;
4938 end if;
4939 --
4940 if l_proportion >0 then
4941 for r3 in c_cost_allocation_keyflex(r1.cost_allocation_keyflex_id) loop
4942 pay_cost_allocation_api.create_cost_allocation(
4943 p_validate =>false ,
4944 p_effective_date =>p_effective_date,
4945 p_assignment_id =>p_assignment_id,
4946 p_cost_allocation_id =>l_cost_allocation_id,
4947 p_business_group_id =>p_business_group_id,
4948 p_combination_name =>l_combination_name,
4949 p_cost_allocation_keyflex_id =>l_cost_allocation_keyflex_id,
4950 p_proportion =>l_proportion,
4951 p_effective_start_date =>l_effective_start_date,
4952 p_effective_end_date =>l_effective_end_date,
4953 p_object_version_number =>l_object_version_number,
4954 p_segment1 =>r3.segment1,
4955 p_segment2 =>r3.segment2,
4956 p_segment3 =>r3.segment3,
4957 p_segment4 =>r3.segment4,
4958 p_segment5 =>r3.segment5,
4959 p_segment6 =>r3.segment6,
4960 p_segment7 =>r3.segment7,
4961 p_segment8 =>r3.segment8,
4962 p_segment9 =>r3.segment9,
4963 p_segment10 =>r3.segment10,
4964 p_segment11 =>r3.segment11,
4965 p_segment12 =>r3.segment12,
4966 p_segment13 =>r3.segment13,
4967 p_segment14 =>r3.segment14,
4968 p_segment15 =>r3.segment15,
4969 p_segment16 =>r3.segment16,
4970 p_segment17 =>r3.segment17,
4971 p_segment18 =>r3.segment18,
4972 p_segment19 =>r3.segment19,
4973 p_segment20 =>r3.segment20,
4974 p_segment21 =>r3.segment21,
4975 p_segment22 =>r3.segment22,
4976 p_segment23 =>r3.segment23,
4977 p_segment24 =>r3.segment24,
4978 p_segment25 =>r3.segment25,
4979 p_segment26 =>r3.segment26,
4980 p_segment27 =>r3.segment27,
4981 p_segment28 =>r3.segment28,
4982 p_segment29 =>r3.segment29,
4983 p_segment30 =>r3.segment30,
4984 p_concat_segments =>r3.concatenated_segments
4985 );
4986 end loop;
4987 --
4988 end if;
4989 --
4990 hr_utility.set_location('hr_assignment.load_assignment_allocation ',15);
4991 --
4992 end loop;
4993 --
4994 hr_utility.set_location('hr_assignment.load_assignment_allocation ',16);
4995 --
4996 end if;
4997 --
4998 hr_utility.set_location('hr_assignment.load_assignment_allocation ',17);
4999 --
5000 END load_assignment_allocation;
5001 -----------------------------------------------------------------------
5002 -- update_assgn_context_value
5003 --
5004 -- populates the per_all_assignments_f.ass_attribute_category value
5005 --
5006 PROCEDURE update_assgn_context_value(
5007 p_business_group_id IN number
5008 ,p_person_id IN number
5009 ,p_assignment_id IN number
5010 ,p_effective_start_date IN date)
5011 IS
5012 l_context_val varchar2(150);
5013 l_output_context_val varchar2(240);
5014
5015 l_ass_attribute_category varchar2(150);
5016
5017 l_sql varchar2(2000);
5018
5019 l_effective_start_date date;
5020 l_proc_name varchar2(100):='update_assgn_context_val :';
5021
5022 cursor csr_dff_context is
5023 select nvl(DEFAULT_CONTEXT_FIELD_NAME,'-100')
5024 from FND_DESCRIPTIVE_FLEXS_VL
5025 where DESCRIPTIVE_FLEXFIELD_NAME='PER_ASSIGNMENTS'
5026 -- fix for the bug 8252045 starts here
5027 AND CONTEXT_USER_OVERRIDE_FLAG='N'
5028 AND CONTEXT_SYNCHRONIZATION_FLAG='N';
5029 -- fix for the bug 8252045 ends here
5030
5031 cursor csr_ass_data is
5032 select ass_attribute_category
5033 from per_all_assignments_f
5034 where business_group_id = p_business_group_id
5035 and person_id = p_person_id
5036 and assignment_id = p_assignment_id
5037 and effective_start_date = p_effective_start_date
5038 and primary_flag = 'Y';
5039
5040 begin
5041
5042 hr_utility.set_location('Entering: '||l_proc_name,10);
5043
5044 hr_utility.set_location('Business_group_id: '||p_business_group_id,11);
5045 hr_utility.set_location('Person_id: '||p_person_id,12);
5046 hr_utility.set_location('Assignment_id: '||p_assignment_id,13);
5047 hr_utility.set_location('Effective_start_date: '||p_effective_start_date,14);
5048
5049 open csr_dff_context ;
5050 fetch csr_dff_context into l_context_val;
5051
5052 if csr_dff_context%found then
5053
5054 if l_context_val <> '-100' then
5055
5056 hr_utility.set_location('DFF setting exists',20);
5057
5058 if instr(upper(l_context_val),'$PROFILES$') <> 0 then
5059 l_context_val:=replace(upper(l_context_val),'$PROFILES$.PER_');
5060 l_context_val:=replace(upper(l_context_val),'$PROFILES$.');
5061 end if;
5062
5063 open csr_ass_data;
5064 fetch csr_ass_data into l_ass_attribute_category;
5065
5066 if csr_ass_data%found then
5067
5068 hr_utility.set_location('l_ass_att: '||l_ass_attribute_category,15);
5069
5070 if l_ass_attribute_category is null
5071 or trim(l_ass_attribute_category) = '' then
5072
5073 begin
5074
5075 hr_utility.set_location(l_proc_name||' got value for reference field',30);
5076
5077 l_effective_start_date:= p_effective_start_date;
5078
5079 hr_utility.set_location(l_proc_name||' selecting records ',30);
5080
5081 l_sql := '
5082 declare
5083 g_rec per_assignments_v%rowtype;
5084 begin
5085 HR_ASSIGNMENT.get_assgn_dff_value('||p_business_group_id||','||p_person_id||','||p_assignment_id||','||':1,g_rec);
5086 select g_rec.'||l_context_val||' into :2 from dual;
5087 end;';
5088
5089 EXECUTE IMMEDIATE l_sql using
5090 in out l_effective_start_date,
5091 in out l_output_context_val;
5092
5093 hr_utility.set_location(l_proc_name||'l_output_context_val: '||l_output_context_val,35);
5094
5095
5096 if l_output_context_val is not null then
5097
5098 update per_all_assignments_f
5099 set ass_attribute_category = l_output_context_val
5100 where business_group_id = p_business_group_id
5101 and person_id = p_person_id
5102 and assignment_id = p_assignment_id
5103 and effective_start_date = p_effective_start_date;
5104
5105 hr_utility.set_location('dynamic sql updated '||sql%rowcount||' records',50);
5106
5107 end if;
5108
5109 close csr_ass_data;
5110
5111 exception
5112 when others then
5113 close csr_ass_data;
5114 hr_utility.set_location(sqlerrm,55);
5115 end;
5116 end if;
5117
5118 end if;
5119
5120 end if;
5121 close csr_dff_context;
5122
5123 end if;
5124
5125 hr_utility.set_location('Leaving: '||l_proc_name,60);
5126
5127 END update_assgn_context_value;
5128
5129 -- get_assgn_dff_value
5130 --
5131 -- returns the per_assignments_v row according to the passed arguments
5132 --
5133 PROCEDURE get_assgn_dff_value(
5134 p_business_group_id IN number
5135 ,p_person_id IN number
5136 ,p_assignment_id IN number
5137 ,p_effective_start_date IN date
5138 , p_asg_rec in out NOCOPY g_asg_type)
5139 Is
5140
5141 cursor csr_asg IS
5142 SELECT PA.ASSIGNMENT_ID ,
5143 '11111111111' as row_id ,
5144 PA.EFFECTIVE_START_DATE ,
5145 DECODE(PA.EFFECTIVE_END_DATE , TO_DATE('4712/12/31', 'YYYY/MM/DD'), to_date(NULL), PA.EFFECTIVE_END_DATE) D_EFFECTIVE_END_DATE ,
5146 PA.EFFECTIVE_END_DATE ,
5147 PA.BUSINESS_GROUP_ID + 0 BUSINESS_GROUP_ID ,
5148 PA.GRADE_ID ,
5149 GDT.NAME GRADE_NAME ,
5150 PA.POSITION_ID ,
5151 HR_GENERAL.DECODE_POSITION_LATEST_NAME(PA.POSITION_ID) POSITION_NAME ,
5152 PA.JOB_ID ,
5153 JBT.NAME JOB_NAME ,
5154 PA.ASSIGNMENT_STATUS_TYPE_ID ,
5155 NVL(AMDTL.USER_STATUS, STTL.USER_STATUS) USER_STATUS ,
5156 NVL(AMD.PER_SYSTEM_STATUS, ST.PER_SYSTEM_STATUS) PER_SYSTEM_STATUS ,
5157 PA.PAYROLL_ID ,
5158 PAY.PAYROLL_NAME ,
5159 PA.LOCATION_ID ,
5160 LOCTL.LOCATION_CODE ,
5161 PA.SUPERVISOR_ID ,
5162 SUP.FULL_NAME SUPERVISOR_NAME ,
5163 NVL(SUP.EMPLOYEE_NUMBER, SUP.NPW_NUMBER) SUPERVISOR_EMPLOYEE_NUMBER ,
5164 PA.SPECIAL_CEILING_STEP_ID ,
5165 PSP.SPINAL_POINT ,
5166 PSPS.SEQUENCE SPINAL_POINT_STEP_SEQUENCE ,
5167 PA.PERSON_ID ,
5168 PA.ORGANIZATION_ID ,
5169 OTL.NAME ORGANIZATION_NAME ,
5170 PA.PEOPLE_GROUP_ID ,
5171 PA.ASSIGNMENT_SEQUENCE ,
5172 PA.PRIMARY_FLAG ,
5173 PA.ASSIGNMENT_NUMBER ,
5174 PA.CHANGE_REASON ,
5175 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 ,
5176 PA.COMMENT_ID ,
5177 COM.COMMENT_TEXT ,
5178 PA.DATE_PROBATION_END ,
5179 PA.DATE_PROBATION_END D_DATE_PROBATION_END ,
5180 PA.FREQUENCY ,
5181 HR_GENERAL.DECODE_LOOKUP('FREQUENCY', PA.FREQUENCY) FREQUENCY_MEANING ,
5182 PA.INTERNAL_ADDRESS_LINE ,
5183 PA.MANAGER_FLAG ,
5184 PA.NORMAL_HOURS ,
5185 PA.PROBATION_PERIOD ,
5186 PA.PROBATION_UNIT ,
5187 HR_GENERAL.DECODE_LOOKUP('QUALIFYING_UNITS', PA.PROBATION_UNIT) PROBATION_UNIT_MEANING ,
5188 PA.TIME_NORMAL_FINISH ,
5189 PA.TIME_NORMAL_START ,
5190 PA.BARGAINING_UNIT_CODE ,
5191 HR_GENERAL.DECODE_LOOKUP('BARGAINING_UNIT_CODE', PA.BARGAINING_UNIT_CODE) BARGAINING_UNIT_CODE_MEANING ,
5192 PA.LABOUR_UNION_MEMBER_FLAG ,
5193 PA.HOURLY_SALARIED_CODE ,
5194 HR_GENERAL.DECODE_LOOKUP('HOURLY_SALARIED_CODE', PA.HOURLY_SALARIED_CODE) ,
5195 PA.LAST_UPDATE_DATE ,
5196 PA.LAST_UPDATED_BY ,
5197 PA.LAST_UPDATE_LOGIN ,
5198 PA.CREATED_BY ,
5199 PA.CREATION_DATE ,
5200 PA.SAL_REVIEW_PERIOD ,
5201 HR_GENERAL.DECODE_LOOKUP('FREQUENCY', PA.SAL_REVIEW_PERIOD_FREQUENCY) SAL_REV_PERIOD_FREQ_MEANING ,
5202 PA.SAL_REVIEW_PERIOD_FREQUENCY ,
5203 PA.PERF_REVIEW_PERIOD ,
5204 HR_GENERAL.DECODE_LOOKUP('FREQUENCY', PA.PERF_REVIEW_PERIOD_FREQUENCY) PERF_REV_PERIOD_FREQ_MEANING ,
5205 PA.PERF_REVIEW_PERIOD_FREQUENCY ,
5206 PA.PAY_BASIS_ID ,
5207 PB.NAME SALARY_BASIS ,
5208 PB.PAY_BASIS PAY_BASIS ,
5209 PA.RECRUITER_ID ,
5210 PA.PERSON_REFERRED_BY_ID ,
5211 PA.RECRUITMENT_ACTIVITY_ID ,
5212 PA.SOURCE_ORGANIZATION_ID ,
5213 PA.SOFT_CODING_KEYFLEX_ID ,
5214 PA.VACANCY_ID ,
5215 PA.ASSIGNMENT_TYPE ,
5216 PA.APPLICATION_ID ,
5217 PA.DEFAULT_CODE_COMB_ID ,
5218 PA.PERIOD_OF_SERVICE_ID ,
5219 PA.SET_OF_BOOKS_ID ,
5220 GL.NAME D_SET_OF_BOOKS ,
5221 GL.CHART_OF_ACCOUNTS_ID GL_KEYFLEX_STRUCTURE ,
5222 PA.SOURCE_TYPE ,
5223 PA.REQUEST_ID ,
5224 PA.PROGRAM_APPLICATION_ID ,
5225 PA.PROGRAM_ID ,
5226 PA.PROGRAM_UPDATE_DATE ,
5227 PA.ASS_ATTRIBUTE_CATEGORY ,
5228 PA.ASS_ATTRIBUTE1 ,
5229 PA.ASS_ATTRIBUTE2 ,
5230 PA.ASS_ATTRIBUTE3 ,
5231 PA.ASS_ATTRIBUTE4 ,
5232 PA.ASS_ATTRIBUTE5 ,
5233 PA.ASS_ATTRIBUTE6 ,
5234 PA.ASS_ATTRIBUTE7 ,
5235 PA.ASS_ATTRIBUTE8 ,
5236 PA.ASS_ATTRIBUTE9 ,
5237 PA.ASS_ATTRIBUTE10 ,
5238 PA.ASS_ATTRIBUTE11 ,
5239 PA.ASS_ATTRIBUTE12 ,
5240 PA.ASS_ATTRIBUTE13 ,
5241 PA.ASS_ATTRIBUTE14 ,
5242 PA.ASS_ATTRIBUTE15 ,
5243 PA.ASS_ATTRIBUTE16 ,
5244 PA.ASS_ATTRIBUTE17 ,
5245 PA.ASS_ATTRIBUTE18 ,
5246 PA.ASS_ATTRIBUTE19 ,
5247 PA.ASS_ATTRIBUTE20 ,
5248 PA.ASS_ATTRIBUTE21 ,
5249 PA.ASS_ATTRIBUTE22 ,
5250 PA.ASS_ATTRIBUTE23 ,
5251 PA.ASS_ATTRIBUTE24 ,
5252 PA.ASS_ATTRIBUTE25 ,
5253 PA.ASS_ATTRIBUTE26 ,
5254 PA.ASS_ATTRIBUTE27 ,
5255 PA.ASS_ATTRIBUTE28 ,
5256 PA.ASS_ATTRIBUTE29 ,
5257 PA.ASS_ATTRIBUTE30 ,
5258 PA.EMPLOYMENT_CATEGORY ,
5259 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 ,
5260 PA.ESTABLISHMENT_ID ,
5261 PA.COLLECTIVE_AGREEMENT_ID ,
5262 PA.CONTRACT_ID ,
5263 PA.CAGR_GRADE_DEF_ID ,
5264 PA.CAGR_ID_FLEX_NUM ,
5265 CA.NAME AGREEMENT_NAME ,
5266 O1.NAME ESTABLISHMENT_NAME ,
5267 CO.REFERENCE REFERENCE ,
5268 PA.NOTICE_PERIOD ,
5269 PA.NOTICE_PERIOD_UOM ,
5270 HR_GENERAL.DECODE_LOOKUP('QUALIFYING_UNITS', PA.NOTICE_PERIOD_UOM) NOTICE_PERIOD_UOM_MEANING ,
5271 PA.EMPLOYEE_CATEGORY ,
5272 HR_GENERAL.DECODE_LOOKUP('EMPLOYEE_CATG', PA. EMPLOYEE_CATEGORY) EMPLOYEE_CATEGORY_MEANING ,
5273 PA.WORK_AT_HOME ,
5274 PA.JOB_POST_SOURCE_NAME ,
5275 PA.TITLE ,
5276 PA.PROJECT_TITLE ,
5277 PA.PERIOD_OF_PLACEMENT_DATE_START ,
5278 PA.VENDOR_ID ,
5279 POV.VENDOR_NAME ,
5280 PA.VENDOR_SITE_ID ,
5281 POVS.VENDOR_SITE_CODE ,
5282 PA.PO_HEADER_ID ,
5283 POH.SEGMENT1 PO_NUMBER ,
5284 PA.PO_LINE_ID ,
5285 POL.LINE_NUM PO_LINE_NUMBER ,
5286 PA.PROJECTED_ASSIGNMENT_END ,
5287 PA.VENDOR_EMPLOYEE_NUMBER ,
5288 PA.VENDOR_ASSIGNMENT_NUMBER ,
5289 PA.ASSIGNMENT_CATEGORY ,
5290 PA.GRADE_LADDER_PGM_ID ,
5291 PA.SUPERVISOR_ASSIGNMENT_ID ,
5292 PGM.NAME GRADE_LADDER_NAME ,
5293 PA2.ASSIGNMENT_NUMBER SUPERVISOR_ASSIGNMENT_NUMBER
5294 FROM PER_ALL_ASSIGNMENTS_F PA ,
5295 PER_ALL_ASSIGNMENTS_F PA2 ,
5296 PER_GRADES PG ,
5297 PER_JOBS J ,
5298 PER_GRADES_TL GDT ,
5299 PER_JOBS_TL JBT ,
5300 PER_ASSIGNMENT_STATUS_TYPES ST ,
5301 PER_ASSIGNMENT_STATUS_TYPES_TL STTL ,
5302 PER_ASS_STATUS_TYPE_AMENDS AMD ,
5303 PER_ASS_STATUS_TYPE_AMENDS_TL AMDTL ,
5304 PAY_ALL_PAYROLLS_F PAY ,
5305 HR_LOCATIONS_ALL_TL LOCTL ,
5306 HR_LOCATIONS_NO_JOIN LOC ,
5307 PER_ALL_PEOPLE_F SUP ,
5308 PER_SPINAL_POINT_STEPS_F PSPS ,
5309 PER_SPINAL_POINTS PSP ,
5310 HR_ALL_ORGANIZATION_UNITS O ,
5311 HR_ALL_ORGANIZATION_UNITS_TL OTL ,
5312 HR_COMMENTS COM ,
5313 GL_SETS_OF_BOOKS GL ,
5314 PER_PAY_BASES PB ,
5315 FND_SESSIONS FND ,
5316 PER_COLLECTIVE_AGREEMENTS CA ,
5317 PER_CONTRACTS_F CO ,
5318 HR_ALL_ORGANIZATION_UNITS O1 ,
5319 BEN_PGM_F PGM ,
5320 PO_VENDORS POV ,
5321 PO_VENDOR_SITES_ALL POVS ,
5322 PO_HEADERS_ALL POH ,
5323 PO_LINES_ALL POL
5324 WHERE PA.ASSIGNMENT_TYPE IN ( 'E','C')
5325 AND PA.ORGANIZATION_ID = O.ORGANIZATION_ID
5326 AND PA.GRADE_ID = PG.GRADE_ID (+)
5327 AND PA.GRADE_ID =GDT.GRADE_ID (+)
5328 AND GDT.LANGUAGE(+) = userenv('LANG')
5329 AND PA.JOB_ID = JBT.JOB_ID (+)
5330 AND JBT.LANGUAGE(+) = userenv('LANG')
5331 AND PA.JOB_ID = J.JOB_ID (+)
5332 AND PA.ASSIGNMENT_STATUS_TYPE_ID = ST.ASSIGNMENT_STATUS_TYPE_ID
5333 AND PA.ASSIGNMENT_STATUS_TYPE_ID = AMD.ASSIGNMENT_STATUS_TYPE_ID (+)
5334 AND PA.BUSINESS_GROUP_ID + 0 = AMD.BUSINESS_GROUP_ID (+) + 0
5335 AND PA.PAYROLL_ID = PAY.PAYROLL_ID (+)
5336 AND PA.LOCATION_ID = LOC.LOCATION_ID (+)
5337 AND PA.SUPERVISOR_ID = SUP.PERSON_ID (+)
5338 AND PA.SPECIAL_CEILING_STEP_ID = PSPS.STEP_ID (+)
5339 AND PSPS.SPINAL_POINT_ID = PSP.SPINAL_POINT_ID (+)
5340 AND PA.SET_OF_BOOKS_ID = GL.SET_OF_BOOKS_ID (+)
5341 AND PA.COMMENT_ID = COM.COMMENT_ID (+)
5342 AND PA.PAY_BASIS_ID = PB.PAY_BASIS_ID (+)
5343 AND (
5344 ( PA2.EFFECTIVE_START_DATE IS NULL
5345 AND PA2.EFFECTIVE_END_DATE IS NULL
5346 )
5347 OR (PA2.EFFECTIVE_START_DATE IS NOT NULL
5348 AND PA2.EFFECTIVE_END_DATE IS NOT NULL
5349 AND PA2.EFFECTIVE_END_DATE =
5350 (SELECT MAX(PA3.EFFECTIVE_END_DATE)
5351 FROM PER_ALL_ASSIGNMENTS_F PA3
5352 WHERE PA3.ASSIGNMENT_ID = PA.SUPERVISOR_ASSIGNMENT_ID
5353 )
5354 )
5355 )
5356 AND O.organization_id = OTL.organization_id
5357 AND OTL.language = userenv('LANG')
5358 AND ST.assignment_status_type_id = STTL.assignment_status_type_id
5359 AND STTL.language = userenv('LANG')
5360 AND AMD.ass_status_type_amend_id = AMDTL.ass_status_type_amend_id (+)
5361 AND DECODE(amdtl.ass_status_type_amend_id,NULL,'1',AMDTL.language) = DECODE(amdtl.ass_status_type_amend_id,NULL,'1',userenv('LANG'))
5362 AND LOC.location_id = LOCTL.location_id (+)
5363 AND DECODE(LOCTL.location_id,NULL,'1',loctl.language) = DECODE(LOCTL.location_id,NULL,'1',userenv('LANG'))
5364 AND PA.ESTABLISHMENT_ID = O1.ORGANIZATION_ID (+)
5365 AND CA.COLLECTIVE_AGREEMENT_ID (+) = PA.COLLECTIVE_AGREEMENT_ID
5366 AND CO.CONTRACT_ID (+) = PA.CONTRACT_ID
5367 AND PA.GRADE_LADDER_PGM_ID = PGM.PGM_ID (+)
5368 AND PA.VENDOR_ID = POV.VENDOR_ID (+)
5369 AND PA.VENDOR_SITE_ID = POVS.VENDOR_SITE_ID (+)
5370 AND PA.PO_HEADER_ID = POH.PO_HEADER_ID (+)
5371 AND PA.PO_LINE_ID = POL.PO_LINE_ID (+)
5372 AND PA2.ASSIGNMENT_ID(+) = PA.SUPERVISOR_ASSIGNMENT_ID
5373 AND PA.BUSINESS_GROUP_ID = P_BUSINESS_GROUP_ID
5374 AND PA.PERSON_ID = P_PERSON_ID
5375 AND PA.ASSIGNMENT_ID = P_ASSIGNMENT_ID
5376 AND PA.EFFECTIVE_START_DATE = P_EFFECTIVE_START_DATE;
5377
5378
5379 l_proc_name varchar2(20):= 'get_assgn_dff_value';
5380 begin
5381
5382 hr_utility.set_location('Entering: '||l_proc_name,10);
5383
5384 OPEN csr_asg;
5385 FETCH csr_asg INTO p_asg_rec;
5386 if csr_asg%found then
5387 close csr_asg;
5388 end if;
5389 hr_utility.set_location('Leaving: '||l_proc_name,10);
5390 END get_assgn_dff_value;
5391
5392 --end for bug 6598795
5393 ---------------------------------------------------------------------------------------
5394 end hr_assignment;
[Home] [Help]