1 PACKAGE hr_assignment AUTHID CURRENT_USER AS
2 /* $Header: peassign.pkh 120.4.12010000.1 2008/07/28 04:12:11 appldev 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 (HEADER)
24
25 Description : This package declares 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
32
33 Change List
34 -----------
35
36 Version Date Author ER/CR No. Description of Change
37 -------+---------+----------+---------+--------------------------
38 70.0 19-NOV-92 SZWILLIA Date Created
39 70.6 30-DEC-92 SZWILLIA Changed Integer refs to NUMBER.
40 70.7 16-FEB-93 JHOBBS Added maintain_alu_asg procedure for
41 maintaining alus for the assignment.
42 70.8 03-MAR-93 JHOBBS Removed maintain_alu_asg. It is now in
43 hrentmnt.
44 70.9 10-MAR-93 JRHODES Made all Date fields type DATE
45 70.10 11-MAR-93 NKHAN Added 'exit' to the end
46 70.11 25-MAR-93 JRHODES New Procedure 'tidy_up_ref_int'
47 70.12 07-JUN-93 JRHODES New Procedure 'call_terminate_entries'
48 80.1 15-OCT-93 JRHODES Added check for cobra
49 80.2 09-DEC-93 JRhodes New Procedure 'test_for_cancel_reterm'
50 70.22 04-JUL-94 JRhodes Added Validate_Pos
51 110.1 01-APR-98 SASmith Change to the load_budget_values parameters to include
52 effective start and end dates. Required as per_assignments
53 _budget_values_f is now datetracked.
54
55 115.3 26-Nov-2001 HSAJJA Added procedure load_assignment_allocation
56 115.4 27-Nov-2001 HSAJJA Added dbdrv command
57 115.5 15-Feb-2001 M Bocutt 1681015 Added p_cost_warning to tidy_up_ref_int
58 to pass back warning condition about
59 costing maintenance.
60 115.6 26-FEB-2002 M Gettins Added update_primary_cwk as part of
61 of the contingent labour project. 08-MAR-2002 adhunter Added overloaded gen_new_ass_number
62 115.7 04-OCT-2002 adhunter Added whenever oserror exit failure rollback
63 115.11 05-nov-2007 sidsaxen Created update_assgn_context_value and
64 get_assgn_dff_value new procedures.
65 ================================================================= */
66 --
67 --
68 ------------------- gen_probation_end -----------------------------
69 /*
70 NAME
71 gen_probation_end
72 DESCRIPTION
73
74 PARAMETERS
75 p_assignment_id - assignment_id or NULL if in insert mode
76 p_probation_period - probation period, NULL if validating DATE_END
77 p_probation_unit - probation unit, NULL if validating DATE_END
78 p_start_date - Validation start date of the assignment
79 p_date_probation_end - User entered date or NULL when default required
80 */
81 PROCEDURE gen_probation_end
82 ( p_assignment_id IN INTEGER
83 , p_probation_period IN NUMBER
84 , p_probation_unit IN VARCHAR2
85 , p_start_date IN DATE
86 , p_date_probation_end IN OUT NOCOPY DATE
87 );
88 --
89 --
90 ------------------- gen_new_ass_sequence --------------------------
91 /*
92 NAME
93 gen_new_ass_sequence
94 DESCRIPTION
95 Generates a new assignment sequence for Applicant and Employee
96 Assignments.
97 */
98 PROCEDURE gen_new_ass_sequence
99 ( p_person_id in number
100 , p_assignment_type in varchar2
101 , p_assignment_sequence in out nocopy number
102 );
103 --
104 --
105 ------------------- gen_new_ass_number ----------------------------
106 /*
107 NAME
108 gen_new_ass_number
109 DESCRIPTION
110 If an Assignment Number is passed to the procedure it validates
111 that it is a unique number for the Person.
112
113 If no Assignment Number is passed to the procedure then it determines
114 the value of the newxt assignment number. If the assignment sequence
115 is 1 then it is just the value of the employee number otherwise it is
116 the employee number || assignment sequence. If the generated assignment
117 number is not unique then the assignment sequence is incremented until
118 a valid assignment number is generated.
119 */
120 PROCEDURE gen_new_ass_number
121 ( p_assignment_id IN number
122 , p_business_group_id IN number
123 , p_employee_number IN VARCHAR2
124 , p_assignment_sequence IN number
125 , p_assignment_number IN OUT NOCOPY VARCHAR2
126 );
127 --
128 ------------------- gen_new_ass_number ---OVERLOADED---------------
129 /*
130 NAME
131 gen_new_ass_number
132 DESCRIPTION
133 If an Assignment Number is passed to the procedure it validates
134 that it is a unique number for the Person.
135
136 If no Assignment Number is passed to the procedure then it determines
137 the value of the newxt assignment number. If the assignment sequence
138 is 1 then it is just the value of the worker number otherwise it is
139 the worker number || assignment sequence. If the generated assignment
140 number is not unique then the assignment sequence is incremented until
141 a valid assignment number is generated.
142 */
143 PROCEDURE gen_new_ass_number
144 ( p_assignment_id IN number
145 , p_business_group_id IN number
146 , p_worker_number IN VARCHAR2
147 , p_assignment_type IN VARCHAR2
148 , p_assignment_sequence IN number
149 , p_assignment_number IN OUT NOCOPY VARCHAR2
150 );
151 --
152 --
153 ------------------- check_hours -----------------------------------
154 /*
155 NAME
156 check_hours
157 DESCRIPTION
158 Validation to ensure that the normal working hours do not exceed
159 the maximum availble for the Frequency.
160 PARAMETERS
161 p_frequency - Standard Conditions PER field
162 - only D,W,M,Y are valid values
163 p_normal_hours - Standard Conditions WORKING HOURS field
164 */
165 PROCEDURE check_hours
166 ( p_frequency IN VARCHAR2
167 , p_normal_hours IN NUMBER
168 );
169 --
170 --
171 ------------------- check_term -----------------------------
172 /*
173 NAME
174 check_term
175 DESCRIPTION
176 If an Update Override, Delete Next Change or Future Change Delete
177 will remove TERM_ASSIGNed assignments or END DATES after
178 assignment status changes of TERM_ASSIGN then the end date may need
179 to be fixed to either the Actual Termination Date or the Final
180 Process Date or the Employees Period of Service. This procedure
181 determines the requirement and returns an new End Date if one is
182 required.
183 PARAMETERS
184 p_period_of_service_id - Employee's Current Period of Service ID
185 p_assignment_id - Assignment ID
186 p_sdate - Start Date of current Assignment row
187 p_edate - End Date of current Assignment row
188 p_current_status - The PER_SYSTEM_STATUS of the current row
189 p_mode - FUTURE_CHANGES, DELETE_NEXT_CHANGE,
190 UPDATE_OVERRIDE
191 p_newdate - The New ASsignment End Date
192 */
193 PROCEDURE check_term
194 (
195 p_period_of_service_id IN INTEGER
196 ,p_assignment_id IN INTEGER
197 ,p_sdate IN DATE
198 ,p_edate IN DATE
199 ,p_current_status IN VARCHAR2
200 ,p_mode IN VARCHAR2
201 ,p_newdate OUT NOCOPY DATE
202 );
203 --
204 --
205 ------------------- warn_del_term ----------------------------
206 /*
207 NAME
208 warn_del_term
209 DESCRIPTION
210 If the operation will remove an assignment with TERM_ASSIGN status
211 then a warning will be issued from the form. This procedure
212 determines whether such an operation will take place.
213 PARAMETERS
214 p_assignment_id - Assignment ID
215 p_effective_start_date - Start Date of current Assignment row
216 p_effective_end_date - End Date of current Assignment row
217 p_mode - FUTURE_CHANGES, DELETE_NEXT_CHANGE,
218 UPDATE_OVERRIDE
219 */
220 procedure warn_del_term
221 (
222 p_assignment_id IN INTEGER
223 ,p_mode IN VARCHAR2
224 ,p_effective_start_date IN DATE
225 ,p_effective_end_date IN DATE
226 );
227 --
228 --
229 ------------------- delete_ass_ref_int ----------------------------
230 /*
231 NAME
232 delete_ass_ref_int
233 DESCRIPTION
234 Determines whether there are any dependent records for the Assignment.
235 If any are found then delete them.
236 The following tables are examined
237 PER_SPINAL_POINT_PLACEMENTS
238 PER_SECONDARY_ASG_STATUSES
239 PER_ASSIGNMENT_BUDGET_VALUES
240
241 PARAMETERS
242 p_business_group_id - Business Group ID
243 p_assignment_id - Assignment ID
244 */
245 PROCEDURE delete_ass_ref_int
246 (
247 p_business_group_id IN INTEGER
248 ,p_assignment_id IN INTEGER
249 );
250 --
251 --
252 ------------------- get_act_term_date -----------------------------
253 /*
254 NAME
255 get_act_term_date
256 DESCRIPTION
257 Returns the Actual Termination Date of the Employee Period of Service.
258
259 PARAMETERS
260 p_period_of_service_id
261 p_actual_termination_date
262 */
263 PROCEDURE get_act_term_date
264 (
265 p_period_of_service_id IN INTEGER
266 ,p_actual_termination_date OUT NOCOPY DATE
267 );
268 --
269 --
270 ------------------- check_future_primary --------------------------
271 /*
272 NAME
273 check_future_primary
274 DESCRIPTION
275 Checks to see whether the operation will remove a row
276 that has a primary flag value differnet to the current one.
277 If such a row is found then the P_CHANGE_FLAG is set to 'Y' and
278 the date from which changes to other assignment primary flag
279 changes must be catered for is determined and passed back in
280 P_PRIMARY_DATE_FROM.
281 PARAMETERS
282 p_assignment_id - The current assignment to be checked
283 p_sdate - The start date of the current row
284 NB this depends on the Mode
285 UPDATE_OVERRIDE ==> Validation Start Date
286 Otherwise ==> Effective Start Date
287 p_edate - Effective End Date of the current row
288 p_mode - The DT_UPDATE_MODE or DT_DELETE_MODE
289 p_primary_flag - The Primary Flag Value for the current assignment
290 p_change_flag - An indicator to detect whether primary changes are
291 required.
292 p_new_primary_flag - The value that the current assignment will have
293 after the operation
294 p_primary_date_from- The date from which changes to other assignments
295 must be catered for
296 */
297 PROCEDURE check_future_primary
298 (
299 p_assignment_id IN INTEGER
300 ,p_sdate IN DATE
301 ,p_edate IN DATE
302 ,p_mode IN VARCHAR2
303 ,p_primary_flag IN VARCHAR2
304 ,p_change_flag IN OUT NOCOPY VARCHAR2
305 ,p_new_primary_flag IN OUT NOCOPY VARCHAR2
306 ,p_primary_date_from OUT NOCOPY DATE
307 );
308 --
309 --
310 ------------------- check_ass_for_primary -------------------------
311 /*
312 NAME
313 check_ass_for_primary
314 DESCRIPTION
315 Checks to ensure that the record is continuous until the end
316 of the Period Of Service and that if it has been terminated
317 then termination was as a result of the termination of the employee
318 i.e. the termination date is the same as the ACTUAL TERMINATION DATE.
319 PARAMETERS
320 p_period_of_service_id - The current Period of Service ID
321 p_assignment_id - The current assignment ID
322 p_sdate - The validation start date of the updated record
323 */
324 PROCEDURE check_ass_for_primary
325 (
326 p_period_of_service_id IN INTEGER
327 ,p_assignment_id IN INTEGER
328 ,p_sdate IN DATE
329 );
330 --
331 /*
332 NAME
333 update_primary_cwk
334 DESCRIPTION
335 For the Current Assignment, if the operation is not ZAP then updates
336 all the future rows to the NEW_PRIMARY_FLAG value.
337 For other assignments,if the other assignment is the new primary
338 then ensure that there is a record starting on the correct date
339 with Primary Flag = 'Y' and update all other future changes to
340 the same Primary value. For any other assignments
341 if the assignment is primary on the date in question then
342 ensure that that there is a row on this date with primary
343 flag = 'N' and that all future changes are set to 'N'
344 otherwise ensure that all future primary flags are set to 'N'.
345 NB. This uses several calls to DO_PRIMARY_UPDATE which handles the
346 date effective insert for an individual assignment row if one
347 is required.
348 PARAMETERS
349 p_assignment_id - The current assignment
350 p_pop_date_start - The current Period of Placement
351 p_new_primary_ass_id - The Assignment ID that will be primary after
352 the operation
353 p_sdate - The date from which changes are to be made
354 p_new_primary_flag - The current assignment primary flag after the
355 operation
356 p_mode - The DT_DELETE_MODE or DT_UPDATE_MODE
357 */
358 --
359 PROCEDURE update_primary_cwk
360 (p_assignment_id IN INTEGER
361 ,p_person_id IN NUMBER
362 ,p_pop_date_start IN DATE
363 ,p_new_primary_ass_id IN INTEGER
364 ,p_sdate IN DATE
365 ,p_new_primary_flag IN VARCHAR2
366 ,p_mode IN VARCHAR2
367 ,p_last_updated_by IN INTEGER
368 ,p_last_update_login IN INTEGER );
369 --
370 ------------------- update_primary -----------------------------
371 /*
372 NAME
373 update_primary
374 DESCRIPTION
375 For the Current Assignment, if the operation is not ZAP then updates
376 all the future rows to the NEW_PRIMARY_FLAG value.
377 For other assignments,
378 if the other assignment is the new primary then ensure that there
379 is a record starting on the correct date with Primary Flag = 'Y'
380 and update all other future changes to the same Primary value.
381 For any other assignments
382 if the assignment is primary on the date in question then
383 ensure that that there is a row on this date with primary
384 flag = 'N' and that all future changes are set to 'N'
385 otherwise
386 ensure that all future primary flags are set to 'N'.
387 NB. This uses several calls to DO_PRIMARY_UPDATE which handles the
388 date effective insert for an individual assignment row if one
389 is required.
390 PARAMETERS
391 p_assignment_id - The current assignment
392 p_period_of_service_id - The current Period of Service
393 p_new_primary_ass_id - The Assignment ID that will be primary after
394 the operation
395 p_sdate - The date from which changes are to be made
396 p_new_primary_flag - The current assignment primary flag after the
397 operation
398 p_mode - The DT_DELETE_MODE or DT_UPDATE_MODE
399 p_last_updated_by - For Audit
400 p_last_update_login - For Audit
401 */
402 PROCEDURE update_primary
403 (
404 p_assignment_id IN INTEGER
405 ,p_period_of_service_id IN INTEGER
406 ,p_new_primary_ass_id IN INTEGER
407 ,p_sdate IN DATE
408 ,p_new_primary_flag IN VARCHAR2
409 ,p_mode IN VARCHAR2
410 ,p_last_updated_by IN INTEGER
411 ,p_last_update_login IN INTEGER
412 );
413 --
414 --
415 ------------------- do_primary_update -----------------------------
416 /*
417 NAME
418 do_primary_update
419 DESCRIPTION
420 Performs updates on the Assignment to set the Primary Flag to the value
421 passed in to the procedure.
422 If a Primary Flag is to be reset on the Date passed in and a row does
423 not start on this date then a date effective insert is performed.
424 PARAMETERS
425 p_assignment_id - The assignment to be updated
426 p_sdate - The date from which to update
427 p_primary_flag - The primary flag value
428 p_current_ass - Whether the assignment is the current one (Y/N)
429 p_last_updated_by
430 p_last_update_login
431 */
432 PROCEDURE do_primary_update
433 (
434 p_assignment_id IN INTEGER
435 ,p_sdate IN DATE
436 ,p_primary_flag IN VARCHAR2
437 ,p_current_ass IN VARCHAR2
438 ,p_last_updated_by IN INTEGER
439 ,p_last_update_login IN INTEGER
440 );
441 --
442 --
443 ------------------- get_new_primary_assignment --------------------
444 /*
445 NAME
446 get_new_primary_assignment
447 DESCRIPTION
448 Searches for a candidate assignment which will become Primary
449 on the Date passed into the procedure. The assignment must be continuous
450 to the end of the period of service and if it is terminated the
451 first termination must be as aresult of termination of the employee.
452 If more than one candidate assignment is found then a warning status is
453 raised (the form detect the warning and pops a QuickPick).
454 PARAMETERS
455 p_assignment_id - The current assignment
456 p_period_of_service_id - The current period of service
457 p_sdate - The date upon which the assignment will
458 become primary
459 p_new_primary_ass_id - The new Primary Assignment ID
460 */
461 PROCEDURE get_new_primary_assignment
462 (
463 p_assignment_id IN NUMBER
464 ,p_period_of_service_id IN NUMBER
465 ,p_sdate IN DATE
466 ,p_new_primary_ass_id OUT NOCOPY VARCHAR2
467 );
468 --
469 --
470 ------------------- load_budget_values --------------------
471 /*
472 NAME
473 load_budget_values
474 DESCRIPTION
475 Creates Assignment Budget Values form the Default ones for the Business
476 Group.
477 PARAMETERS
478 p_assignment_id - The current assignment
479 p_business_group_id - The business Group
480 p_userid
481 p_login
482 p_effective_start_date - start date of the assignment.
483 p_effective_end_date - end date of the assignment.
484 */
485 -- Change to parameter structure to include effective start and end dates.
486 -- This is required as per_assignment_budget_values now date tracked.
487 -- SASmith : 01-APR-1998.
488 --
489 PROCEDURE load_budget_values
490 (p_assignment_id IN INTEGER
491 ,p_business_group_id IN INTEGER
492 ,p_userid IN VARCHAR2
493 ,p_login IN VARCHAR2
494 ,p_effective_start_date IN DATE
495 ,p_effective_end_date IN DATE
496 );
497 --
498 --
499 ------------------- del_ref_int_check --------------------
500 /*
501 NAME
502 del_ref_int_check
503 DESCRIPTION
504 Performs Referential Integrity Checks on the following tables
505 For 'ZAP'
506 PER_EVENTS
507 PER_LETTER_REQUEST_LINES
508 PAY_COST_ALLOCATIONS_F
509 PER_ASSIGNMENT_EXTRA_INFO
510 PER_SECONDARY_ASG_STATUSES
511 PAY_PERSONAL_PAYMENT_METHODS_F
512 HR_ASSIGNMENT_SET_AMENDMENTS
513 PAY_ASSIGNMENT_ACTIONS
514
515 For 'END' (date effective delete)
516 PER_EVENTS
517 PER_LETTER_REQUEST_LINES
518 PAY_COST_ALLOCATIONS_F
519 PER_SECONDARY_ASG_STATUSES
520 PAY_PERSONAL_PAYMENT_METHODS_F
521 PAY_ASSIGNMENT_ACTIONS
522
523 Determines whether the delete operation is permissible
524 PARAMETERS
525 p_assignment_id - The current assignment
526 p_mode - The mode of operation (ZAP or END)
527 p_edate - The date the assignment is ENDed
528 only required for 'END'
529 */
530 PROCEDURE del_ref_int_check
531 (
532 p_assignment_id IN INTEGER
533 ,p_mode IN VARCHAR2
534 ,p_edate IN DATE
535 );
536 --
537 --
538 ------------------- del_ref_int_delete --------------------
539 /*
540 NAME
541 del_ref_int_delete
542 DESCRIPTION
543 Performs Third Party Delete on data that is not checked in
544 del_ref_in_check. Removes data from the following tables
545
546 For 'ZAP'
547 HR_ASSIGNMENT_SET_AMENDMENTS
548 PER_ASSIGNMENT_BUDGET_VALUES
549 PER_SPINAL_POINT_PLACEMENTS_F
550
551 For 'END' (performs a date effective delete)
552 PER_SPINAL_POINT_PLACEMENTS_F
553
554 For 'FUTURE' (including FUTURE_CHANGES, DELETE_NEXT_CHANGE,
555 UPDATE_OVERRIDE)
556 PER_SPINAL_POINT_PLACEMENTS_F
557
558 PARAMETERS
559 p_assignment_id - The current assignment
560 p_grade_id - The current grade ('FUTURE' only')
561 p_mode - The mode of operation (ZAP, END or FUTURE)
562 p_edate - For END the date the assignment is ENDed
563 For FUTURE the date the change applies from
564 For ZAP not required
565 p_last_updated_by
566 p_last_update_login
567 */
568 PROCEDURE del_ref_int_delete
569 (
570 p_assignment_id IN INTEGER
571 ,p_grade_id IN INTEGER
572 ,p_mode IN VARCHAR2
573 ,p_edate IN DATE
574 ,p_last_updated_by IN INTEGER
575 ,p_last_update_login IN INTEGER
576 ,p_calling_proc IN VARCHAR2 DEFAULT NULL
577 ,p_val_st_date IN DATE DEFAULT NULL
578 ,p_val_end_date IN DATE DEFAULT NULL
579 ,p_datetrack_mode IN VARCHAR2 DEFAULT NULL
580 ,p_future_spp_warning OUT NOCOPY BOOLEAN
581 );
582 --
583 --
584 ------------------- tidy_up_ref_int --------------------
585 /*
586 NAME
587 tidy_up_ref_int
588 DESCRIPTION
589 This procedure performs two operations.
590 The first occurs when it is called with a parameter of 'END' - the
591 procedure then moves the end date of any child rows for the assignment
592 so that it is set to be the end date of the assignment.
593
594 The second occurs when it is called with a parameter of 'FUTURE'.
595 This is the case when a FUTURE_CHANGE of DELETE_NEXT_CHANGE is going
596 to open the assignment out nocopy beyond its current End Date. The procedure
597 resets the End Dates of any child rows to be that on the Assignment. In
598 the case of Costing records dates are only changed if there are not
599 future records.
600
601 The following tables are affected.
602
603 PAY_COST_ALLOCATIONS_F
604 PER_SECONDARY_ASS_STATUSES
605 PAY_PERSONAL_PAYMENT_METHODS_F
606
607 PARAMETERS
608 p_assignment_id - Assignment ID
609 p_mode - 'END' or 'FUTURE'
610 p_new_end_date - The new end date of the parent Assignment
611 p_old_end_date - The Assignment End Date before the operation
612 p_last_updated_by
613 p_last_update_login
614 p_cost_warning - Pass back warning if future costing records
615 exist. Can only set to TRUE if mode is
616 FUTURE.
617 */
618 PROCEDURE tidy_up_ref_int
619 (
620 p_assignment_id IN INTEGER
621 ,p_mode IN VARCHAR2
622 ,p_new_end_date IN DATE
623 ,p_old_end_date DATE
624 ,p_last_updated_by INTEGER
625 ,p_last_update_login INTEGER
626 ,p_cost_warning OUT NOCOPY BOOLEAN
627 );
628 --
629 --
630 ------------------- call_terminate_entries --------------------
631 /*
632 NAME
633 call_terminate_entries
634 DESCRIPTION
635 This procedure determines the Actual Termination Date, Last Standard
636 Processing Date and Final Process Date in order to terminate element
637 entries and ALUs when an individual assignment is terminated or ended.
638
639 There are several cases :-
640
641 i. Status is END and there are no prior TERM_ASSIGNs
642 => ATD = Session date
643 LSD = Session date
644 FPD = Session date
645
646 ii. Status is END and there is a prior TERM_ASSIGN
647 => ATD = NULL
648 LSD = NULL
649 FPD = Session Date
650
651 iii. Status is TERM_ASSIGN and there are no prior TERM_ASSIGNs
652 => ATD = Validation Start Date - 1
653 LSD = (IF Assignment has Payroll then END_DATE of current
654 processing period
655 ELSE
656 Validation Start Date - 1)
657 FPD = NULL
658
659 iv. Status is TERM_ASSIGN and there is a prior TERM_ASSIGN
660 => No processing required
661
662 PARAMETERS
663 p_assignment_id - Assignment ID
664 p_status - 'END' or 'TERM_ASSIGN'
665 p_start_date - Validation Start Date for TERM_ASSIGN or
666 Session Date for 'END'
667 */
668 PROCEDURE call_terminate_entries
669 (
670 p_assignment_id IN NUMBER
671 ,p_status IN VARCHAR2
672 ,p_start_date IN DATE
673 );
674 --
675 ------------ test_for_cancel_reterm ------------------------------------
676 /*
677 This procedure works out nocopy whether a Cancel or retermination is required
678 follwoing an operation that affects the "leading TERM_ASSIGN" status
679 */
680 procedure test_for_cancel_reterm
681 (p_assignment_id in number
682 ,p_validation_start_date in date
683 ,p_validation_end_date in date
684 ,p_mode in varchar2
685 ,p_current_status_type in varchar2
686 ,p_old_status_type in varchar2
687 ,p_cancel_atd in out nocopy date
688 ,p_cancel_lspd in out nocopy date
689 ,p_reterm_atd in out nocopy date
690 ,p_reterm_lspd in out nocopy date
691 );
692 --
693 -----------------------------------------------------------------------
694 -- check_for_cobra
695 --
696 -- This procedure checks to see if there are COBRA Enrollments
697 -- that have a Qualifying Date on Termination Date + 1 (i.e. Enrollment
698 -- is as a result of the termination)
699 --
700 -- If this Termination will be removed as a result of the operation
701 -- then issue a warning stating that COBRA Coverage may no longer be
702 -- applicable
703 --
704 PROCEDURE check_for_cobra
705 (p_assignment_id IN INTEGER
706 ,p_sdate IN DATE
707 ,p_edate IN DATE
708 );
709 --
710 --
711 -----------------------------------------------------------------------
712 -- validate_pos
713 --
714 -- This procedure is called from hr_chg_date.call_session_date to ensure
715 -- that a new session date that is being set in PERWSEMA does not lie
716 -- outside the bounds of a Period of Service.
717 --
718 PROCEDURE validate_pos
719 (p_person_id IN VARCHAR2
720 ,p_new_date IN VARCHAR2
721 );
722 --
723 --
724 PROCEDURE load_assignment_allocation
725 (p_assignment_id IN INTEGER
726 ,p_business_group_id IN INTEGER
727 ,p_effective_date IN DATE
728 ,p_position_id in number);
729 --
730
731 --for bug 6598795
732 --mirror type of PER_ASSIGNMENTS_V
733
734 TYPE g_asg_type IS RECORD
735 (
736 ASSIGNMENT_ID PER_ALL_ASSIGNMENTS_F.ASSIGNMENT_ID%TYPE,
737 ROW_ID VARCHAR2(16),
738 EFFECTIVE_START_DATE PER_ALL_ASSIGNMENTS_F.EFFECTIVE_START_DATE%TYPE,
739 D_EFFECTIVE_END_DATE PER_ALL_ASSIGNMENTS_F.EFFECTIVE_END_DATE%TYPE,
740 EFFECTIVE_END_DATE PER_ALL_ASSIGNMENTS_F.EFFECTIVE_END_DATE%TYPE,
741 BUSINESS_GROUP_ID PER_ALL_ASSIGNMENTS_F.BUSINESS_GROUP_ID%TYPE,
742 GRADE_ID PER_ALL_ASSIGNMENTS_F.GRADE_ID%TYPE,
743 GRADE_NAME PER_GRADES_TL.NAME%TYPE,
744 POSITION_ID PER_ALL_ASSIGNMENTS_F.POSITION_ID%TYPE,
745 POSITION_NAME VARCHAR2(240),
746 JOB_ID PER_ALL_ASSIGNMENTS_F.JOB_ID%TYPE,
747 JOB_NAME PER_JOBS_TL.NAME%TYPE,
748 ASSIGNMENT_STATUS_TYPE_ID PER_ALL_ASSIGNMENTS_F.ASSIGNMENT_STATUS_TYPE_ID%TYPE,
749 USER_STATUS PER_ASS_STATUS_TYPE_AMENDS_TL.USER_STATUS%TYPE,
750 PER_SYSTEM_STATUS PER_ASS_STATUS_TYPE_AMENDS.PER_SYSTEM_STATUS%TYPE,
751 PAYROLL_ID PER_ALL_ASSIGNMENTS_F.PAYROLL_ID%TYPE,
752 PAYROLL_NAME PAY_ALL_PAYROLLS_F.PAYROLL_NAME%TYPE,
753 LOCATION_ID PER_ALL_ASSIGNMENTS_F.LOCATION_ID%TYPE,
754 LOCATION_CODE HR_LOCATIONS_ALL_TL.LOCATION_CODE%TYPE,
755 SUPERVISOR_ID PER_ALL_ASSIGNMENTS_F.SUPERVISOR_ID%TYPE,
756 SUPERVISOR_NAME PER_ALL_PEOPLE_F.FULL_NAME%TYPE,
757 SUPERVISOR_EMPLOYEE_NUMBER PER_ALL_PEOPLE_F.EMPLOYEE_NUMBER%TYPE,
758 SPECIAL_CEILING_STEP_ID PER_ALL_ASSIGNMENTS_F.SPECIAL_CEILING_STEP_ID%TYPE,
759 SPINAL_POINT PER_SPINAL_POINTS.SPINAL_POINT%TYPE,
760 SPINAL_POINT_STEP_SEQUENCE PER_SPINAL_POINT_STEPS_F.SEQUENCE%TYPE,
761 PERSON_ID PER_ALL_ASSIGNMENTS_F.PERSON_ID%TYPE,
762 ORGANIZATION_ID PER_ALL_ASSIGNMENTS_F.ORGANIZATION_ID%TYPE,
763 ORGANIZATION_NAME HR_ALL_ORGANIZATION_UNITS_TL.NAME%TYPE,
764 PEOPLE_GROUP_ID PER_ALL_ASSIGNMENTS_F.PEOPLE_GROUP_ID%TYPE,
765 ASSIGNMENT_SEQUENCE PER_ALL_ASSIGNMENTS_F.ASSIGNMENT_SEQUENCE%TYPE,
766 PRIMARY_FLAG PER_ALL_ASSIGNMENTS_F.PRIMARY_FLAG%TYPE,
767 ASSIGNMENT_NUMBER PER_ALL_ASSIGNMENTS_F.ASSIGNMENT_NUMBER%TYPE,
768 CHANGE_REASON PER_ALL_ASSIGNMENTS_F.CHANGE_REASON%TYPE,
769 CHANGE_REASON_MEANING VARCHAR2(240),
770 COMMENT_ID PER_ALL_ASSIGNMENTS_F.COMMENT_ID%TYPE,
771 COMMENT_TEXT HR_COMMENTS.COMMENT_TEXT%TYPE,
772 DATE_PROBATION_END PER_ALL_ASSIGNMENTS_F.DATE_PROBATION_END%TYPE,
773 D_DATE_PROBATION_END PER_ALL_ASSIGNMENTS_F.DATE_PROBATION_END%TYPE,
774 FREQUENCY PER_ALL_ASSIGNMENTS_F.FREQUENCY%TYPE,
775 FREQUENCY_MEANING VARCHAR2(240),
776 INTERNAL_ADDRESS_LINE PER_ALL_ASSIGNMENTS_F.INTERNAL_ADDRESS_LINE%TYPE,
777 MANAGER_FLAG PER_ALL_ASSIGNMENTS_F.MANAGER_FLAG%TYPE,
778 NORMAL_HOURS PER_ALL_ASSIGNMENTS_F.NORMAL_HOURS%TYPE,
779 PROBATION_PERIOD PER_ALL_ASSIGNMENTS_F.PROBATION_PERIOD%TYPE,
780 PROBATION_UNIT PER_ALL_ASSIGNMENTS_F.PROBATION_UNIT%TYPE,
781 PROBATION_UNIT_MEANING VARCHAR2(240),
782 TIME_NORMAL_FINISH PER_ALL_ASSIGNMENTS_F.TIME_NORMAL_FINISH%TYPE,
783 TIME_NORMAL_START PER_ALL_ASSIGNMENTS_F.TIME_NORMAL_START%TYPE,
784 BARGAINING_UNIT_CODE PER_ALL_ASSIGNMENTS_F.BARGAINING_UNIT_CODE%TYPE,
785 BARGAINING_UNIT_CODE_MEANING VARCHAR2(240),
786 LABOUR_UNION_MEMBER_FLAG PER_ALL_ASSIGNMENTS_F.LABOUR_UNION_MEMBER_FLAG%TYPE,
787 HOURLY_SALARIED_CODE PER_ALL_ASSIGNMENTS_F.HOURLY_SALARIED_CODE%TYPE,
788 HOURLY_SALARIED_CODE_MEANING VARCHAR2(240),
789 LAST_UPDATE_DATE PER_ALL_ASSIGNMENTS_F.LAST_UPDATE_DATE%TYPE,
790 LAST_UPDATED_BY PER_ALL_ASSIGNMENTS_F.LAST_UPDATED_BY%TYPE,
791 LAST_UPDATE_LOGIN PER_ALL_ASSIGNMENTS_F.LAST_UPDATE_LOGIN%TYPE,
792 CREATED_BY PER_ALL_ASSIGNMENTS_F.CREATED_BY%TYPE,
793 CREATION_DATE PER_ALL_ASSIGNMENTS_F.CREATION_DATE%TYPE,
794 SAL_REVIEW_PERIOD PER_ALL_ASSIGNMENTS_F.SAL_REVIEW_PERIOD%TYPE,
795 SAL_REV_PERIOD_FREQ_MEANING VARCHAR2(240),
796 SAL_REVIEW_PERIOD_FREQUENCY PER_ALL_ASSIGNMENTS_F.SAL_REVIEW_PERIOD_FREQUENCY%TYPE,
797 PERF_REVIEW_PERIOD PER_ALL_ASSIGNMENTS_F.PERF_REVIEW_PERIOD%TYPE,
798 PERF_REV_PERIOD_FREQ_MEANING VARCHAR2(240),
799 PERF_REVIEW_PERIOD_FREQUENCY PER_ALL_ASSIGNMENTS_F.PERF_REVIEW_PERIOD_FREQUENCY%TYPE,
800 PAY_BASIS_ID PER_ALL_ASSIGNMENTS_F.PAY_BASIS_ID%TYPE,
801 SALARY_BASIS PER_PAY_BASES.NAME%TYPE,
802 PAY_BASIS PER_PAY_BASES.PAY_BASIS%TYPE,
803 RECRUITER_ID PER_ALL_ASSIGNMENTS_F.RECRUITER_ID%TYPE,
804 PERSON_REFERRED_BY_ID PER_ALL_ASSIGNMENTS_F.PERSON_REFERRED_BY_ID%TYPE,
805 RECRUITMENT_ACTIVITY_ID PER_ALL_ASSIGNMENTS_F.RECRUITMENT_ACTIVITY_ID%TYPE,
806 SOURCE_ORGANIZATION_ID PER_ALL_ASSIGNMENTS_F.SOURCE_ORGANIZATION_ID%TYPE,
807 SOFT_CODING_KEYFLEX_ID PER_ALL_ASSIGNMENTS_F.SOFT_CODING_KEYFLEX_ID%TYPE,
808 VACANCY_ID PER_ALL_ASSIGNMENTS_F.VACANCY_ID%TYPE,
809 ASSIGNMENT_TYPE PER_ALL_ASSIGNMENTS_F.ASSIGNMENT_TYPE%TYPE,
810 APPLICATION_ID PER_ALL_ASSIGNMENTS_F.APPLICATION_ID%TYPE,
811 DEFAULT_CODE_COMB_ID PER_ALL_ASSIGNMENTS_F.DEFAULT_CODE_COMB_ID%TYPE,
812 PERIOD_OF_SERVICE_ID PER_ALL_ASSIGNMENTS_F.PERIOD_OF_SERVICE_ID%TYPE,
813 SET_OF_BOOKS_ID PER_ALL_ASSIGNMENTS_F.SET_OF_BOOKS_ID%TYPE,
814 D_SET_OF_BOOKS GL_SETS_OF_BOOKS.NAME%TYPE,
815 GL_KEYFLEX_STRUCTURE GL_SETS_OF_BOOKS.CHART_OF_ACCOUNTS_ID%TYPE,
816 SOURCE_TYPE PER_ALL_ASSIGNMENTS_F.SOURCE_TYPE%TYPE,
817 REQUEST_ID PER_ALL_ASSIGNMENTS_F.REQUEST_ID%TYPE,
818 PROGRAM_APPLICATION_ID PER_ALL_ASSIGNMENTS_F.PROGRAM_APPLICATION_ID%TYPE,
819 PROGRAM_ID PER_ALL_ASSIGNMENTS_F.PROGRAM_ID%TYPE,
820 PROGRAM_UPDATE_DATE PER_ALL_ASSIGNMENTS_F.PROGRAM_UPDATE_DATE%TYPE,
821 ASS_ATTRIBUTE_CATEGORY PER_ALL_ASSIGNMENTS_F.ASS_ATTRIBUTE_CATEGORY%TYPE,
822 ASS_ATTRIBUTE1 PER_ALL_ASSIGNMENTS_F.ASS_ATTRIBUTE1%TYPE,
823 ASS_ATTRIBUTE2 PER_ALL_ASSIGNMENTS_F.ASS_ATTRIBUTE2%TYPE,
824 ASS_ATTRIBUTE3 PER_ALL_ASSIGNMENTS_F.ASS_ATTRIBUTE3%TYPE,
825 ASS_ATTRIBUTE4 PER_ALL_ASSIGNMENTS_F.ASS_ATTRIBUTE4%TYPE,
826 ASS_ATTRIBUTE5 PER_ALL_ASSIGNMENTS_F.ASS_ATTRIBUTE5%TYPE,
827 ASS_ATTRIBUTE6 PER_ALL_ASSIGNMENTS_F.ASS_ATTRIBUTE6%TYPE,
828 ASS_ATTRIBUTE7 PER_ALL_ASSIGNMENTS_F.ASS_ATTRIBUTE7%TYPE,
829 ASS_ATTRIBUTE8 PER_ALL_ASSIGNMENTS_F.ASS_ATTRIBUTE8%TYPE,
830 ASS_ATTRIBUTE9 PER_ALL_ASSIGNMENTS_F.ASS_ATTRIBUTE9%TYPE,
831 ASS_ATTRIBUTE10 PER_ALL_ASSIGNMENTS_F.ASS_ATTRIBUTE10%TYPE,
832 ASS_ATTRIBUTE11 PER_ALL_ASSIGNMENTS_F.ASS_ATTRIBUTE11%TYPE,
833 ASS_ATTRIBUTE12 PER_ALL_ASSIGNMENTS_F.ASS_ATTRIBUTE12%TYPE,
834 ASS_ATTRIBUTE13 PER_ALL_ASSIGNMENTS_F.ASS_ATTRIBUTE13%TYPE,
835 ASS_ATTRIBUTE14 PER_ALL_ASSIGNMENTS_F.ASS_ATTRIBUTE14%TYPE,
836 ASS_ATTRIBUTE15 PER_ALL_ASSIGNMENTS_F.ASS_ATTRIBUTE15%TYPE,
837 ASS_ATTRIBUTE16 PER_ALL_ASSIGNMENTS_F.ASS_ATTRIBUTE16%TYPE,
838 ASS_ATTRIBUTE17 PER_ALL_ASSIGNMENTS_F.ASS_ATTRIBUTE17%TYPE,
839 ASS_ATTRIBUTE18 PER_ALL_ASSIGNMENTS_F.ASS_ATTRIBUTE18%TYPE,
840 ASS_ATTRIBUTE19 PER_ALL_ASSIGNMENTS_F.ASS_ATTRIBUTE19%TYPE,
841 ASS_ATTRIBUTE20 PER_ALL_ASSIGNMENTS_F.ASS_ATTRIBUTE20%TYPE,
842 ASS_ATTRIBUTE21 PER_ALL_ASSIGNMENTS_F.ASS_ATTRIBUTE21%TYPE,
843 ASS_ATTRIBUTE22 PER_ALL_ASSIGNMENTS_F.ASS_ATTRIBUTE22%TYPE,
844 ASS_ATTRIBUTE23 PER_ALL_ASSIGNMENTS_F.ASS_ATTRIBUTE23%TYPE,
845 ASS_ATTRIBUTE24 PER_ALL_ASSIGNMENTS_F.ASS_ATTRIBUTE24%TYPE,
846 ASS_ATTRIBUTE25 PER_ALL_ASSIGNMENTS_F.ASS_ATTRIBUTE25%TYPE,
847 ASS_ATTRIBUTE26 PER_ALL_ASSIGNMENTS_F.ASS_ATTRIBUTE26%TYPE,
848 ASS_ATTRIBUTE27 PER_ALL_ASSIGNMENTS_F.ASS_ATTRIBUTE27%TYPE,
849 ASS_ATTRIBUTE28 PER_ALL_ASSIGNMENTS_F.ASS_ATTRIBUTE28%TYPE,
850 ASS_ATTRIBUTE29 PER_ALL_ASSIGNMENTS_F.ASS_ATTRIBUTE29%TYPE,
851 ASS_ATTRIBUTE30 PER_ALL_ASSIGNMENTS_F.ASS_ATTRIBUTE30%TYPE,
852 EMPLOYMENT_CATEGORY PER_ALL_ASSIGNMENTS_F.EMPLOYMENT_CATEGORY%TYPE,
853 EMPLOYMENT_CATEGORY_MEANING VARCHAR2(240),
854 ESTABLISHMENT_ID PER_ALL_ASSIGNMENTS_F.ESTABLISHMENT_ID%TYPE,
855 COLLECTIVE_AGREEMENT_ID PER_ALL_ASSIGNMENTS_F.COLLECTIVE_AGREEMENT_ID%TYPE,
856 CONTRACT_ID PER_ALL_ASSIGNMENTS_F.CONTRACT_ID%TYPE,
857 CAGR_GRADE_DEF_ID PER_ALL_ASSIGNMENTS_F.CAGR_GRADE_DEF_ID%TYPE,
858 CAGR_ID_FLEX_NUM PER_ALL_ASSIGNMENTS_F.CAGR_ID_FLEX_NUM%TYPE,
859 AGREEMENT_NAME PER_COLLECTIVE_AGREEMENTS.NAME%TYPE,
860 ESTABLISHMENT_NAME HR_ALL_ORGANIZATION_UNITS.NAME%TYPE,
861 REFERENCE PER_CONTRACTS_F.REFERENCE%TYPE,
862 NOTICE_PERIOD PER_ALL_ASSIGNMENTS_F.NOTICE_PERIOD%TYPE,
863 NOTICE_PERIOD_UOM PER_ALL_ASSIGNMENTS_F.NOTICE_PERIOD_UOM%TYPE,
864 NOTICE_PERIOD_UOM_MEANING VARCHAR2(240),
865 EMPLOYEE_CATEGORY PER_ALL_ASSIGNMENTS_F.EMPLOYEE_CATEGORY%TYPE,
866 EMPLOYEE_CATEGORY_MEANING VARCHAR2(240),
867 WORK_AT_HOME PER_ALL_ASSIGNMENTS_F.WORK_AT_HOME%TYPE,
868 JOB_POST_SOURCE_NAME PER_ALL_ASSIGNMENTS_F.JOB_POST_SOURCE_NAME%TYPE,
869 TITLE PER_ALL_ASSIGNMENTS_F.TITLE%TYPE,
870 PROJECT_TITLE PER_ALL_ASSIGNMENTS_F.PROJECT_TITLE%TYPE,
871 PERIOD_OF_PLACEMENT_DATE_START PER_ALL_ASSIGNMENTS_F.PERIOD_OF_PLACEMENT_DATE_START%TYPE,
872 VENDOR_ID PER_ALL_ASSIGNMENTS_F.VENDOR_ID%TYPE,
873 VENDOR_NAME PO_VENDORS.VENDOR_NAME%TYPE,
874 VENDOR_SITE_ID PER_ALL_ASSIGNMENTS_F.VENDOR_SITE_ID%TYPE,
875 VENDOR_SITE_CODE PO_VENDOR_SITES_ALL.VENDOR_SITE_CODE%TYPE,
876 PO_HEADER_ID PER_ALL_ASSIGNMENTS_F.PO_HEADER_ID%TYPE,
877 PO_NUMBER PO_HEADERS_ALL.SEGMENT1%TYPE,
878 PO_LINE_ID PER_ALL_ASSIGNMENTS_F.PO_LINE_ID%TYPE,
879 PO_LINE_NUMBER PO_LINES_ALL.LINE_NUM%TYPE,
880 PROJECTED_ASSIGNMENT_END PER_ALL_ASSIGNMENTS_F.PROJECTED_ASSIGNMENT_END%TYPE,
881 VENDOR_EMPLOYEE_NUMBER PER_ALL_ASSIGNMENTS_F.VENDOR_EMPLOYEE_NUMBER%TYPE,
882 VENDOR_ASSIGNMENT_NUMBER PER_ALL_ASSIGNMENTS_F.VENDOR_ASSIGNMENT_NUMBER%TYPE,
883 ASSIGNMENT_CATEGORY PER_ALL_ASSIGNMENTS_F.ASSIGNMENT_CATEGORY%TYPE,
884 GRADE_LADDER_PGM_ID PER_ALL_ASSIGNMENTS_F.GRADE_LADDER_PGM_ID%TYPE,
885 SUPERVISOR_ASSIGNMENT_ID PER_ALL_ASSIGNMENTS_F.SUPERVISOR_ASSIGNMENT_ID%TYPE,
886 GRADE_LADDER_NAME BEN_PGM_F.NAME%TYPE,
887 SUPERVISOR_ASSIGNMENT_NUMBER PER_ALL_ASSIGNMENTS_F.ASSIGNMENT_NUMBER%TYPE
888 );
889
890 -- update_assgn_context_value
891 --
892 -- populates the per_all_assignments_f.ass_attribute_category value
893 --
894 PROCEDURE update_assgn_context_value(
895 p_business_group_id IN number
896 ,p_person_id IN number
897 ,p_assignment_id IN number
898 ,p_effective_start_date IN date);
899
900 -- get_assgn_dff_value
901 --
902 -- returns the per_assignments_v row according to the passed arguments
903 --
904 PROCEDURE get_assgn_dff_value(
905 p_business_group_id IN number
906 ,p_person_id IN number
907 ,p_assignment_id IN number
908 ,p_effective_start_date IN DATE
909 , p_asg_rec in out NOCOPY g_asg_type);
910 --end for bug 6598795
911 --
912 end hr_assignment;
[Home] [Help]