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