1 PACKAGE BODY hri_opl_suph_hst_inc AS
2 /* $Header: hrioshhi.pkb 115.15 2003/05/27 14:45:06 jtitmas noship $ */
3
4 /******************************************************************************/
5 /* */
6 /* OUTLINE / DEFINITIONS */
7 /* */
8 /* CHAINS */
9 /* ====== */
10 /* A chain is defined for an employee as a list starting with the employee */
11 /* which contains their supervisor, and successive higher level supervisors */
12 /* finishing with the highest level (overall) supervisor. */
13 /* */
14 /* Each chain is valid for the length of time it describes the supervisor */
15 /* hierarchy between the employee it is defined for and the overall */
16 /* supervisor in the hierarchy. */
17 /* */
18 /* The supervisor hierarchy table implements each link in the chain as a */
19 /* row with the employee the chain is defined for as the subordinate. The */
20 /* absolute levels refer to absolute positions within the overall hierarchy */
21 /* whereas the relative level refers to the difference in the absolute levels */
22 /* for the row. */
23 /* */
24 /* When an employee changes supervisor, their chain must change since their */
25 /* immediate supervisor is different. However, the chains of all that */
26 /* employee's subordinates must also change because a chain consists of */
27 /* each higher level supervisor up to and including the overall supervisor. */
28 /* */
29 /* LEAF NODES */
30 /* ========== */
31 /* If a person is supervised but is not themselves a supervisor they are */
32 /* termed a "leaf node". The supervisor hierarchy history table also tracks */
33 /* whether the chain owner is a leaf node or not. Terminated people do not */
34 /* have a leaf node status. */
35 /* */
36 /* IMPLEMENTATION LOGIC */
37 /* ==================== */
38 /* The supervisor hierarchy history table is populated by carrying out the */
39 /* following steps: */
40 /* */
41 /* 1) Empty out existing table */
42 /* */
43 /* 2) Loop through a view containing supervisor changes. Supervisor changes */
44 /* are: */
45 /* - New hires with a supervisor */
46 /* - Switching supervisor (before separation) */
47 /* - Supervisors (including "leaf nodes") who terminate */
48 /* - Subordinates of supervisors which are finally processed */
49 /* without the subordinate being updated */
50 /* - Plus the initialization which is done by selecting all the */
51 /* top supervisors at the start of the collection */
52 /* */
53 /* Processing the changes sequentially in reverse date order: */
54 /* */
55 /* i) Calculate new chain for the person who has changed supervisor */
56 /* ii) Get the end date for the chain (held in global, or if not then the */
57 /* chain owner's termination date, or if not then end of time) */
58 /* iii) Insert new chain into hierarchy table */
59 /* iv) Store the same information in a global data structure */
60 /* v) If the supervisor change is not the first record of the person then */
61 /* propagate the change down to all that person's subordinates making */
62 /* use of the data structure to avoid recalculating the same */
63 /* information twice */
64 /* */
65 /* 3) Global structures are used to: */
66 /* */
67 /* i) Bulk fetch the main loop */
68 /* ii) Bulk insert the chains into the hierarchy table */
69 /* iii) Store information about the current chain being processed */
70 /* iv) Keep a note of which chains have been processed on a particular */
71 /* date to avoid re-processing the same information */
72 /* v) Keep a note of the date each chain starts, so that the next time */
73 /* a chain is processed (on an earlier date) the end date is known */
74 /* vi) Store the terminated assignment status types so that it is quick to */
75 /* find out which are invalid at insert time */
76 /* vii) Keep track of whether a supervisor is a leaf node */
77 /* */
78 /* 4) Errors encountered which are specifically handled arise from data */
79 /* inconsistencies: */
80 /* */
81 /* i) Loops in supervisor chain - error is output to log file with the */
82 /* date and assignment in looped chain */
83 /* ii) Overlapping assignment records - these mean a unique constraint */
84 /* error is encountered when inserting. This is recovered and the */
85 /* offending row found. An error is recorded in the log and processing */
86 /* continues. */
87 /* */
88 /******************************************************************************/
89
90 /* Information to be held for each link in a chain */
91 TYPE g_link_record_type IS RECORD
92 (business_group_id per_all_assignments_f.business_group_id%TYPE
93 ,person_id per_all_assignments_f.person_id%TYPE
94 ,assignment_id per_all_assignments_f.assignment_id%TYPE
95 ,asg_status_id per_all_assignments_f.assignment_status_type_id%TYPE
96 ,invalid_flag VARCHAR2(30)
97 ,primary_asg_flag per_all_assignments_f.primary_flag%TYPE
98 ,leaf_node VARCHAR2(30)
99 ,end_date per_all_assignments_f.effective_end_date%TYPE);
100
101 /* Table type to hold information about the current chain */
102 TYPE g_chain_type IS TABLE OF g_link_record_type INDEX BY BINARY_INTEGER;
103
104 /* Simple table types */
105 TYPE g_date_tab_type IS TABLE OF DATE INDEX BY BINARY_INTEGER;
106 TYPE g_number_tab_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
107 TYPE g_varchar2_tab_type IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;
108
109 /* PLSQL table of terminated assignment status types */
110 g_term_asg_statuses g_varchar2_tab_type;
111
112 /* PLSQL table of end dates */
113 g_final_date_tab g_date_tab_type;
114
115 /* PLSQL tables for main bulk fetch */
116 g_fetch_asg_id g_number_tab_type;
117 g_fetch_strt_dt g_date_tab_type;
118 g_fetch_end_dt g_date_tab_type;
119 g_fetch_bgr_id g_number_tab_type;
120 g_fetch_psn_id g_number_tab_type;
121 g_fetch_sup_id g_number_tab_type;
122 g_fetch_prev_sup_id g_number_tab_type;
123 g_fetch_ast_id g_number_tab_type;
124 g_fetch_pos_id g_number_tab_type;
125 g_fetch_chng_dt g_date_tab_type;
126 g_fetch_evt_code g_varchar2_tab_type;
127 g_fetch_term_dt g_date_tab_type;
128 g_fetch_fprc_dt g_date_tab_type;
129
130 /* PLSQL table of top level supervisor checks */
131 g_top_level_check g_date_tab_type;
132
133 /* PLSQL table of dates assignments have already been processed */
134 /* Indexed by assignment id */
135 g_assgnmnts_prcssd g_date_tab_type;
136
137 g_collect_from_date DATE; -- Collection date range start
138 g_collect_to_date DATE; -- Collection date range end
139
140 /* Information about current chain within the hierarchy */
141 g_crrnt_chain g_chain_type; -- Current chain
142 g_crrnt_chain_start_date DATE; -- Current chain start date
143 g_crrnt_chain_end_date DATE; -- Current chain end date
144 g_crrnt_chain_owner_lvl PLS_INTEGER; -- Chain owner's level within chain
145 g_crrnt_chain_orphan_flag VARCHAR2(1); -- Whether current chain is orphaned
146 g_crrnt_chain_top_pos_id NUMBER; -- Current top node period of service
147
148 /* Set to true to output to a concurrent log file */
149 g_conc_request_flag BOOLEAN := FALSE;
150
151 /* Number of rows bulk processed at a time */
152 g_chunk_size PLS_INTEGER := 2000;
153 g_chain_chunk_size PLS_INTEGER := 500;
154 g_chain_transactions PLS_INTEGER := 0;
155
156 /* Stores end of time value */
157 g_end_of_time DATE := hr_general.end_of_time;
158
159 /* Stores current time value */
160 g_current_time DATE;
161 g_current_date DATE := TRUNC(SYSDATE);
162
163 /******************************************************************************/
164 /* Inserts row into concurrent program log when the g_conc_request_flag has */
165 /* been set to TRUE, otherwise does nothing */
166 /******************************************************************************/
167 PROCEDURE output(p_text VARCHAR2)
168 IS
169
170 BEGIN
171
172 /* Write to the concurrent request log if called from a concurrent request */
173 IF (g_conc_request_flag = TRUE) THEN
174
175 /* Put text to log file */
176 fnd_file.put_line(FND_FILE.log, p_text);
177
178 END IF;
179
180 END output;
181
182 /******************************************************************************/
183 /* Load g_term_asg_statuses with the assignment status types which are */
184 /* terminated */
185 /******************************************************************************/
186 PROCEDURE init_term_per_system_status IS
187
188 CURSOR term_asg_statuses_csr IS
189 SELECT ast.assignment_status_type_id
190 FROM per_assignment_status_types ast
191 WHERE ast.per_system_status = 'TERM_ASSIGN';
192
193 BEGIN
194
195 FOR term_asg_status in term_asg_statuses_csr LOOP
196
197 -- load g_term_asg_statuses element at index position of value returned
198 g_term_asg_statuses(term_asg_status.assignment_status_type_id) := 'Y';
199
200 END LOOP;
201
202 END init_term_per_system_status;
203
204 /******************************************************************************/
205 /* Return whether the assignment status type id is terminated */
206 /******************************************************************************/
207 FUNCTION get_inv_flag_status(p_assignment_status_type_id
208 IN per_assignment_status_types.assignment_status_type_id%TYPE)
209 RETURN VARCHAR2 IS
210
211 BEGIN
212
213 -- returns Y if the element exists otherwise a NO_DATA_FOUND exception
214 -- will be raised
215 RETURN(g_term_asg_statuses(p_assignment_status_type_id));
216
217 EXCEPTION
218 WHEN NO_DATA_FOUND THEN
219
220 -- element doesn't exist, return N
221 RETURN('N');
222
223 END get_inv_flag_status;
224
225 /******************************************************************************/
226 /* Checks whether a person is a leaf node on a given date */
227 /******************************************************************************/
228 FUNCTION is_a_leaf_node( p_person_id IN NUMBER
229 , p_on_date IN DATE )
230 RETURN VARCHAR2 IS
231
232 /* A supervisor is a leaf node if they have no non-terminated direct */
233 /* subordinates. Hint seems obvious but on GSIAPDEV it was required */
234 CURSOR is_a_leaf_csr IS
235 SELECT /*+ index(ast PER_ASSIGNMENT_STATUS_TYPE_PK) use_nl(asg ast) */
236 'N'
237 FROM per_all_assignments_f asg,
238 per_assignment_status_types ast
239 WHERE asg.supervisor_id = p_person_id
240 AND asg.assignment_type = 'E'
241 AND asg.primary_flag = 'Y'
242 AND ast.assignment_status_type_id = asg.assignment_status_type_id
243 AND ast.per_system_status <> 'TERM_ASSIGN'
244 AND p_on_date BETWEEN asg.effective_start_date
245 AND asg.effective_end_date;
246
247 l_is_a_leaf_flag VARCHAR2(1);
248
249 BEGIN
250
251 /* Check if the supervisor has any non terminated subordinates */
252 OPEN is_a_leaf_csr;
253 FETCH is_a_leaf_csr INTO l_is_a_leaf_flag;
254 CLOSE is_a_leaf_csr;
255
256 RETURN NVL(l_is_a_leaf_flag, 'Y');
257
258 END is_a_leaf_node;
259
260 /******************************************************************************/
261 /* Inserts row into global temporary table */
262 /******************************************************************************/
263 PROCEDURE insert_row( p_sup_business_group_id IN NUMBER
264 , p_sup_person_id IN NUMBER
265 , p_sup_assignment_id IN NUMBER
266 , p_sup_asg_status_id IN NUMBER
267 , p_sup_level IN NUMBER
268 , p_sup_inv_flag IN VARCHAR2
269 , p_sub_business_group_id IN NUMBER
270 , p_sub_person_id IN NUMBER
271 , p_sub_assignment_id IN NUMBER
272 , p_sub_asg_status_id IN NUMBER
273 , p_sub_primary_asg_flag IN VARCHAR2
274 , p_sub_level IN NUMBER
275 , p_sub_relative_level IN NUMBER
276 , p_sub_inv_flag IN VARCHAR2
277 , p_effective_start_date IN DATE
278 , p_effective_end_date IN DATE
279 , p_orphan_flag IN VARCHAR2
280 , p_sub_leaf_flag IN VARCHAR2 ) IS
281
282 BEGIN
283
284 BEGIN
285
286 INSERT INTO hri_cs_suph
287 (sup_business_group_id
288 ,sup_person_id
289 ,sup_assignment_id
290 ,sup_assignment_status_type_id
291 ,sup_level
292 ,sup_invalid_flag_code
293 ,sub_business_group_id
294 ,sub_person_id
295 ,sub_assignment_id
296 ,sub_assignment_status_type_id
297 ,sub_primary_asg_flag_code
298 ,sub_level
299 ,sub_relative_level
300 ,sub_invalid_flag_code
301 ,orphan_flag_code
302 ,sub_leaf_flag_code
303 ,effective_start_date
304 ,effective_end_date)
305 VALUES
306 (p_sup_business_group_id
307 ,p_sup_person_id
308 ,p_sup_assignment_id
309 ,p_sup_asg_status_id
310 ,p_sup_level
311 ,p_sup_inv_flag
315 ,p_sub_asg_status_id
312 ,p_sub_business_group_id
313 ,p_sub_person_id
314 ,p_sub_assignment_id
316 ,p_sub_primary_asg_flag
317 ,p_sub_level
318 ,p_sub_relative_level
319 ,p_sub_inv_flag
320 ,p_orphan_flag
321 ,p_sub_leaf_flag
322 ,p_effective_start_date
323 ,p_effective_end_date);
324
325 EXCEPTION WHEN OTHERS THEN
326 output('Error inserting chain for:');
327 output('--: ' || to_char(p_sub_person_id) || ' between ' || to_char(p_effective_start_date) ||
328 ' and ' || to_char(p_effective_end_date));
329 END;
330
331 END insert_row;
332
333 /******************************************************************************/
334 /* Inserts chain from specified level */
335 /******************************************************************************/
336 PROCEDURE insert_chain( p_level IN NUMBER,
337 p_end_date IN DATE) IS
338
339 BEGIN
340
341 g_chain_transactions := g_chain_transactions + 1;
342
343 FOR i IN 1..p_level LOOP
344
345 insert_row
346 (p_sup_business_group_id => g_crrnt_chain(i).business_group_id
347 ,p_sup_person_id => g_crrnt_chain(i).person_id
348 ,p_sup_assignment_id => g_crrnt_chain(i).assignment_id
349 ,p_sup_asg_status_id => g_crrnt_chain(i).asg_status_id
350 ,p_sup_level => i
351 ,p_sup_inv_flag => g_crrnt_chain(i).invalid_flag
352 ,p_sub_business_group_id => g_crrnt_chain(p_level).business_group_id
353 ,p_sub_person_id => g_crrnt_chain(p_level).person_id
354 ,p_sub_assignment_id => g_crrnt_chain(p_level).assignment_id
355 ,p_sub_asg_status_id => g_crrnt_chain(p_level).asg_status_id
356 ,p_sub_primary_asg_flag => 'Y'
357 ,p_sub_level => p_level
358 ,p_sub_relative_level => p_level - i
359 ,p_sub_inv_flag => g_crrnt_chain(p_level).invalid_flag
360 ,p_effective_start_date => g_crrnt_chain_start_date
361 ,p_effective_end_date => p_end_date
362 ,p_orphan_flag => g_crrnt_chain_orphan_flag
363 ,p_sub_leaf_flag => g_crrnt_chain(p_level).leaf_node);
364
365 END LOOP;
366
367 END insert_chain;
368
369 /******************************************************************************/
370 /* End dates chain for person */
371 /******************************************************************************/
372 PROCEDURE end_date_chain( p_person_id IN NUMBER,
373 p_end_date IN DATE) IS
374
375 BEGIN
376
377 g_chain_transactions := g_chain_transactions + 1;
378
379 UPDATE hri_cs_suph
380 SET effective_end_date = p_end_date
381 WHERE sub_person_id = p_person_id
382 AND p_end_date BETWEEN effective_start_date AND effective_end_date;
383
384 END end_date_chain;
385
386 /******************************************************************************/
387 /* Processes incremental changes to the table for the current chain */
388 /* */
389 /* This procedure takes the current chain and change date and picks out the */
390 /* existing date tracked chain in the table in which the change date falls. */
391 /* */
392 /* */
393 /* */
394 /******************************************************************************/
395 PROCEDURE process_chain( p_level IN NUMBER,
396 p_end_date IN DATE) IS
397
398 /* Bug 2670477 - join by person id */
399 CURSOR existing_chain_csr IS
400 SELECT
401 effective_start_date
402 ,effective_end_date
403 FROM hri_cs_suph
404 WHERE sub_person_id = g_crrnt_chain(p_level).person_id
405 AND g_crrnt_chain_start_date
406 BETWEEN effective_start_date AND effective_end_date;
407
408 /* Bug 2670477 - join by person id */
409 CURSOR next_chain_start_csr IS
410 SELECT
411 MIN(effective_start_date) next_chain_start_date
412 FROM hri_cs_suph
413 WHERE sub_person_id = g_crrnt_chain(p_level).person_id
414 AND effective_start_date > g_crrnt_chain_start_date;
415
416 l_existing_chain_start DATE;
417 l_existing_chain_end DATE;
418 l_next_chain_start DATE;
419 l_chain_end_date DATE;
420
421 BEGIN
422
423 /* Get information about existing chain */
424 OPEN existing_chain_csr;
425 FETCH existing_chain_csr INTO l_existing_chain_start, l_existing_chain_end;
426 CLOSE existing_chain_csr;
427
428 /* If a chain exists, take the earlier of the current and existing end dates */
429 IF (l_existing_chain_end IS NULL) THEN
430
431 /* If there is no existing chain, check for a future dated one */
432 OPEN next_chain_start_csr;
433 FETCH next_chain_start_csr INTO l_next_chain_start;
434 CLOSE next_chain_start_csr;
435
436 /* If there's a future dated chain, take the earlier of the current end */
440 ELSE
437 /* and one less than the future dated chain start */
438 IF (l_next_chain_start IS NULL) THEN
439 l_chain_end_date := p_end_date;
441 l_chain_end_date := LEAST(l_next_chain_start - 1, p_end_date);
442 END IF;
443
444 ELSE
445
446 l_chain_end_date := LEAST(l_existing_chain_end, p_end_date);
447
448 END IF;
449
450 /* End date existing chain if it is earlier then the current */
451 IF (l_existing_chain_start < g_crrnt_chain_start_date) THEN
452 g_chain_transactions := g_chain_transactions + 1;
453 /* End date existing chain */
454 /* Bug 2670477 - join by person id */
455 UPDATE hri_cs_suph
456 SET effective_end_date = g_crrnt_chain_start_date - 1
457 WHERE sub_person_id =
458 g_crrnt_chain(p_level).person_id
459 AND effective_start_date = l_existing_chain_start;
460
461 /* Delete existing chain if it is the same date as the current */
462 ELSIF (l_existing_chain_start = g_crrnt_chain_start_date) THEN
463 g_chain_transactions := g_chain_transactions + 1;
464 /* Delete existing chain */
465 /* Bug 2670477 - join by person id */
466 DELETE FROM hri_cs_suph
467 WHERE sub_person_id = g_crrnt_chain(p_level).person_id
468 AND effective_start_date = l_existing_chain_start;
469
470 END IF;
471
472 /* Insert new chain */
473 insert_chain(p_level => p_level
474 ,p_end_date => l_chain_end_date);
475
476 /* Remove any obsolete chain updates */
477 BEGIN
478 IF (g_final_date_tab(g_crrnt_chain(p_level).person_id) IS NOT NULL) THEN
479 g_chain_transactions := g_chain_transactions + 1;
480 /* Bug 2670477 - join by person id */
481 DELETE FROM hri_cs_suph
482 WHERE sub_person_id = g_crrnt_chain(p_level).person_id
483 AND effective_start_date > g_final_date_tab(g_crrnt_chain(p_level).person_id);
484 END IF;
485 EXCEPTION WHEN OTHERS THEN
486 null;
487 END;
488
489 END process_chain;
490
491 /******************************************************************************/
492 /* Returns the end date to use for an assignment */
493 /******************************************************************************/
494 FUNCTION get_end_date( p_index IN NUMBER,
495 p_person_id IN NUMBER,
496 p_period_of_service_id IN NUMBER,
497 p_change_date IN DATE)
498 RETURN DATE IS
499
500 CURSOR pos_end_date_csr IS
501 SELECT actual_termination_date, final_process_date
502 FROM per_periods_of_service
503 WHERE period_of_service_id = p_period_of_service_id;
504
505 l_return_date DATE;
506 l_final_process DATE;
507 l_actual_termination DATE;
508
509 BEGIN
510
511 /* If no end date recorded for assignment, get the termination date */
512 IF (p_person_id = g_fetch_psn_id(p_index)) THEN
513 l_actual_termination := g_fetch_term_dt(p_index);
514 l_final_process := g_fetch_fprc_dt(p_index);
515 ELSE
516 OPEN pos_end_date_csr;
517 FETCH pos_end_date_csr INTO l_actual_termination, l_final_process;
518 CLOSE pos_end_date_csr;
519 END IF;
520
521 /* If the change date is after the termination date and the event is */
522 /* not the leaf node termination return the final process date */
523 IF (p_change_date > l_actual_termination) THEN
524 l_return_date := NVL(l_final_process, g_end_of_time);
525 /* Otherwise return the actual termination date */
526 ELSE
527 l_return_date := NVL(l_actual_termination, g_end_of_time);
528 END IF;
529
530 /* If the termination date is in the future return end of time */
531 IF (l_return_date > g_current_date) THEN
532 l_return_date := g_end_of_time;
533 END IF;
534
535 /* Store final process date used */
536 g_final_date_tab(p_person_id) := l_final_process;
537
538 RETURN l_return_date;
539
540 END get_end_date;
541
542 /******************************************************************************/
543 /* Tests whether the top level supervisor is new */
544 /******************************************************************************/
545 PROCEDURE test_top_supervisor( p_index IN NUMBER,
546 p_change_date IN DATE,
547 p_event_code IN VARCHAR2) IS
548
549 /***********************************************************************/
550 /* Cursor to find whether top level supervisor is new */
551 /***********************************************************************/
552 CURSOR top_manager_new_csr IS
553 SELECT 'N'
554 FROM per_all_assignments_f asg
555 WHERE asg.supervisor_id = g_fetch_sup_id(p_index)
556 AND asg.assignment_type = 'E'
557 AND asg.primary_flag = 'Y'
558 AND p_change_date - 1
559 BETWEEN asg.effective_start_date AND asg.effective_end_date;
560
561 l_top_manager_new_flag VARCHAR2(1);
562 l_end_date DATE;
563
564 BEGIN
565
566 /* Catch errors accessing the global table in a PL/SQL block */
567 BEGIN
568 /* Raise an error if there is no record of a check (automatic) or */
572 END IF;
569 /* if the last check was later than the current change date (explicit) */
570 IF (g_top_level_check(g_fetch_sup_id(p_index)) > p_change_date) THEN
571 RAISE NO_DATA_FOUND;
573 EXCEPTION WHEN OTHERS THEN
574 /* Check whether the top manager was previously a supervisor */
575 OPEN top_manager_new_csr;
576 FETCH top_manager_new_csr INTO l_top_manager_new_flag;
577 CLOSE top_manager_new_csr;
578
579 /* Log the check has been done */
580 g_top_level_check(g_fetch_sup_id(p_index)) := p_change_date;
581
582 /* If the top level manager is new */
583 IF (l_top_manager_new_flag IS NULL) THEN
584 /* Get the end date to insert for the new top manager record */
585 l_end_date := get_end_date
586 (p_index => p_index
587 ,p_person_id => g_crrnt_chain(1).person_id
588 ,p_period_of_service_id => g_crrnt_chain_top_pos_id
589 ,p_change_date => p_change_date);
590
591 /* Top manager is not leaf node because level 2 supervisor is */
592 /* non-terminated */
593 IF (g_crrnt_chain(1).invalid_flag = 'N') THEN
594 g_crrnt_chain(1).leaf_node := 'N';
595 END IF;
596
597 /* Insert chain link in historical hierarchy table for new top */
598 /* level manager */
599 process_chain(p_level => 1,
600 p_end_date => l_end_date);
601
602 END IF;
603
604 END;
605
606 END test_top_supervisor;
607
608 /******************************************************************************/
609 /* Inserts and stores the chain of the current supervisor change person */
610 /******************************************************************************/
611 FUNCTION insert_supv_change( p_index IN NUMBER,
612 p_change_date IN DATE,
613 p_event_code IN VARCHAR2)
614 RETURN PLS_INTEGER IS
615
616 /***********************************************************************/
617 /* Cursor picking all managers above person who has changed supervisor */
618 /* LEVEL (wrt this cursor) will be 1 for this person */
619 /* Rows are returned with the topmost supervisor first */
620 /***********************************************************************/
621 CURSOR new_manager_chain_csr IS
622 SELECT
623 hier.business_group_id
624 ,hier.person_id
625 ,hier.assignment_id
626 ,hier.assignment_status_type_id asg_status_id
627 ,hier.supervisor_id
628 ,hier.period_of_service_id
629 ,hier.primary_flag
630 ,LEVEL relative_level
631 FROM (SELECT
632 asg.business_group_id
633 ,asg.person_id
634 ,asg.assignment_id
635 ,asg.assignment_status_type_id
636 ,asg.supervisor_id
637 ,asg.period_of_service_id
638 ,asg.primary_flag
639 FROM
640 per_all_assignments_f asg
641 WHERE asg.assignment_type = 'E'
642 AND asg.primary_flag = 'Y'
643 AND p_change_date
644 BETWEEN asg.effective_start_date AND asg.effective_end_date) hier
645 START WITH hier.assignment_id = g_fetch_asg_id(p_index)
646 CONNECT BY hier.person_id = PRIOR hier.supervisor_id
647 ORDER BY relative_level desc;
648
649 l_person_level PLS_INTEGER;
650 l_sup_lvl PLS_INTEGER;
651
652 l_fetch_bgr_id g_number_tab_type;
653 l_fetch_psn_id g_number_tab_type;
654 l_fetch_asg_id g_number_tab_type;
655 l_fetch_ast_id g_number_tab_type;
656 l_fetch_sup_id g_number_tab_type;
657 l_fetch_pos_id g_number_tab_type;
658 l_fetch_prm_flg g_varchar2_tab_type;
659 l_fetch_level g_number_tab_type;
660
661 l_rows_fetched PLS_INTEGER;
662
663 BEGIN
664
665 /* Get the end date */
666 g_crrnt_chain_end_date := get_end_date
667 (p_index => p_index
668 ,p_person_id => g_fetch_psn_id(p_index)
669 ,p_period_of_service_id => g_fetch_pos_id(p_index)
670 ,p_change_date => p_change_date);
671
672 /* Bulk fetch from cursor without limit as there are never going */
673 /* to be many levels going up */
674 OPEN new_manager_chain_csr;
675 FETCH new_manager_chain_csr
676 BULK COLLECT INTO
677 l_fetch_bgr_id,
678 l_fetch_psn_id,
679 l_fetch_asg_id,
680 l_fetch_ast_id,
681 l_fetch_sup_id,
682 l_fetch_pos_id,
683 l_fetch_prm_flg,
684 l_fetch_level;
685 l_rows_fetched := new_manager_chain_csr%ROWCOUNT;
686 CLOSE new_manager_chain_csr;
687
688 /* Loop through the links in the chain */
689 FOR i IN 1..l_rows_fetched LOOP
690
691 /* If this is the first row, grab the level as it will be the */
692 /* absolute level for the person within the overall hierarchy */
693 /* Also note the top level supervisor period of service id */
694 IF (l_person_level IS NULL) THEN
695 l_person_level := l_fetch_level(i);
696 g_crrnt_chain_top_pos_id := l_fetch_pos_id(i);
697 /* If the top level manager has a supervisor fk */
698 /* then they are an orphan */
699 IF (l_fetch_sup_id(i) IS NOT NULL) THEN
703 END IF;
700 g_crrnt_chain_orphan_flag := 'Y';
701 ELSE
702 g_crrnt_chain_orphan_flag := 'N';
704 END IF;
705
706 /* Calculate the absolute level for the supervisor within the */
707 /* overall hierarchy */
708 l_sup_lvl := l_person_level - l_fetch_level(i) + 1;
709
710 /* Store information in the global data structure */
711 g_crrnt_chain(l_sup_lvl).business_group_id := l_fetch_bgr_id(i);
712 g_crrnt_chain(l_sup_lvl).person_id := l_fetch_psn_id(i);
713 g_crrnt_chain(l_sup_lvl).assignment_id := l_fetch_asg_id(i);
714 g_crrnt_chain(l_sup_lvl).asg_status_id := l_fetch_ast_id(i);
715 g_crrnt_chain(l_sup_lvl).primary_asg_flag := l_fetch_prm_flg(i);
716 g_crrnt_chain(l_sup_lvl).invalid_flag := get_inv_flag_status(l_fetch_ast_id(i));
717 g_crrnt_chain(l_sup_lvl).leaf_node := null;
718
719 END LOOP;
720
721 /* Bug 2521182 (115.12) */
722 /* If the cursor returned no data, then there is probably a data problem */
723 IF (l_person_level IS NULL) THEN
724 output('Possible data corruption for person id: ' ||
725 to_char(g_fetch_psn_id(p_index)) ||
726 ' on ' || to_char(p_change_date,'YYYY/MM/DD'));
727 RETURN -1;
728 END IF;
729
730 /* Store information about the current chain */
731 g_crrnt_chain_owner_lvl := l_person_level;
732 g_crrnt_chain_start_date := p_change_date;
733
734 /* Bug 2748797 - Check for top level supervisor changes */
735 /* If the insert is for a non-terminated level 2 supervisor then */
736 /* potentially the top level supervisor could be new. */
737 IF (l_person_level = 2 AND p_event_code <> 'TERM') THEN
738 test_top_supervisor(p_index => p_index,
739 p_change_date => p_change_date,
740 p_event_code => p_event_code);
741 END IF;
742
743 /* If the change owner is non-terminated then default them to a leaf node */
744 IF (g_crrnt_chain(g_crrnt_chain_owner_lvl).invalid_flag = 'N') THEN
745 g_crrnt_chain(g_crrnt_chain_owner_lvl).leaf_node := 'Y';
746
747 /* If the change owner has a non-terminated supervisor then the supervisor */
748 /* is not a leaf node */
749 IF (g_crrnt_chain_owner_lvl > 1) THEN
750 IF (g_crrnt_chain(g_crrnt_chain_owner_lvl - 1).invalid_flag = 'N') THEN
751 /* Update immediate supervisor to non-leaf node */
752 null;
753 END IF;
754 END IF;
755 END IF;
756
757 /* Return without error */
758 RETURN 0;
759
760 EXCEPTION
761 WHEN OTHERS THEN
762
763 IF new_manager_chain_csr%ISOPEN THEN
764 CLOSE new_manager_chain_csr;
765 END IF;
766
767 /* ORA 01436 - loop in tree walk */
768 IF (SQLCODE = -1436) THEN
769 output('Loop found in supervisor chain for person id: ' ||
770 to_char(g_fetch_psn_id(p_index)) ||
771 ' on ' || to_char(p_change_date,'YYYY/MM/DD'));
772 RETURN -1;
773 ELSE
774 /* Some other error */
775 RAISE;
776 END IF;
777
778 END insert_supv_change;
779
780 /******************************************************************************/
781 /* Calls the chain processing procedure for each of the subordinate chains to */
782 /* process */
783 /******************************************************************************/
784 PROCEDURE update_sub_chains( p_min_lvl IN NUMBER,
785 p_max_lvl IN NUMBER,
786 p_index IN NUMBER) IS
787
788 BEGIN
789
790 FOR v_sub_lvl IN p_min_lvl..p_max_lvl LOOP
791
792 process_chain(p_level => v_sub_lvl,
793 p_end_date => g_crrnt_chain_end_date);
794
795 END LOOP;
796
797 END update_sub_chains;
798
799 /******************************************************************************/
800 /* Updates all subordinates of the current supervisor change person */
801 /* The cursor tree walk returns rows on a depth first basis. The global chain */
802 /* is kept updated with the latest information returned. For example, suppose */
803 /* the supervisor labelled X below changed supervisor. The subordinates of X */
804 /* would be returned in the order they are numbered. This means that when 2 */
805 /* is processed it is guaranteed that the global chain will contain the */
806 /* correct information for X and above, and then for 1 and 2. */
807 /* */
808 /* X */
809 /* / \ */
810 /* 1 4 */
811 /* / \ */
812 /* 2 3 */
813 /* */
814 /* A breadth first tree walk would return the subordinates of X in the order */
815 /* 1 -> 4 -> 2 -> 3. This would mean that when 2 is processed the global */
816 /* chain would contain information for X and above, and then for 4 and 2. */
817 /* This would be wrong!!! */
818 /******************************************************************************/
819 PROCEDURE update_subordinates( p_index IN NUMBER,
823 /* Cursor picks out all subordates of the person who has changed supervisor */
820 p_change_date IN DATE,
821 p_event_code IN VARCHAR2) IS
822
824 /* so that the chains of the subordinates can all be updated with the change */
825 /* This cursor MUST return rows in the default order */
826 CURSOR subordinates_csr IS
827 SELECT
828 hier.business_group_id
829 ,hier.person_id
830 ,hier.assignment_id
831 ,hier.assignment_status_type_id asg_status_id
832 ,hier.supervisor_id
833 ,hier.period_of_service_id
834 ,hier.primary_flag
835 ,LEVEL-1+g_crrnt_chain_owner_lvl actual_level
836 FROM (SELECT
837 asg.business_group_id
838 ,asg.person_id
839 ,asg.assignment_id
840 ,asg.assignment_status_type_id
841 ,asg.period_of_service_id
842 ,asg.supervisor_id
843 ,asg.primary_flag
844 FROM
845 per_all_assignments_f asg
846 WHERE asg.assignment_type = 'E'
847 AND asg.primary_flag = 'Y'
848 AND p_change_date
849 BETWEEN asg.effective_start_date AND asg.effective_end_date) hier
850 WHERE hier.person_id <> g_fetch_psn_id(p_index)
851 START WITH hier.person_id = g_fetch_psn_id(p_index)
852 CONNECT BY hier.supervisor_id = PRIOR hier.person_id;
853 /******************************/
854 /* DO NOT ADD ORDER BY CLAUSE */
855 /******************************/
856
857 l_end_date DATE;
858 l_last_sub_lvl PLS_INTEGER := 0;
859
860 l_fetch_bgr_id g_number_tab_type;
861 l_fetch_psn_id g_number_tab_type;
862 l_fetch_asg_id g_number_tab_type;
863 l_fetch_ast_id g_number_tab_type;
864 l_fetch_sup_id g_number_tab_type;
865 l_fetch_pos_id g_number_tab_type;
866 l_fetch_prm_flg g_varchar2_tab_type;
867 l_fetch_level g_number_tab_type;
868
869 l_rows_fetched PLS_INTEGER := g_chunk_size;
870 l_exit_sub_loop BOOLEAN := FALSE;
871
872 BEGIN
873
874 OPEN subordinates_csr;
875
876 <<subordinates_loop>>
877 LOOP
878
879 FETCH subordinates_csr
880 BULK COLLECT INTO
881 l_fetch_bgr_id,
882 l_fetch_psn_id,
883 l_fetch_asg_id,
884 l_fetch_ast_id,
885 l_fetch_sup_id,
886 l_fetch_pos_id,
887 l_fetch_prm_flg,
888 l_fetch_level
889 LIMIT g_chunk_size;
890 -- check to see if the last row has been fetched
891 IF subordinates_csr%NOTFOUND THEN
892 -- last row fetched, set exit loop flag
893 l_exit_sub_loop := TRUE;
894 -- do we have any rows to process?
895 l_rows_fetched := MOD(subordinates_csr%ROWCOUNT,g_chunk_size);
896 -- note: if l_rows_fetched > 0 then more rows are required to be
897 -- processed and the l_rows_fetched will contain the exact number of
898 -- rows left to process
899 IF l_rows_fetched = 0 THEN
900 -- no more rows to process so exit loop
901 EXIT subordinates_loop;
902 END IF;
903 END IF;
904
905 FOR i IN 1..l_rows_fetched LOOP
906
907 BEGIN
908
909 /* If there is no data in the global, NO_DATA_FOUND will be raised. */
910 /* If the data in the global is an earlier date the same exception is */
911 /* raised. Otherwise the processing has already been done. */
912 IF (p_change_date = g_assgnmnts_prcssd(l_fetch_asg_id(i))) THEN
913 null;
914 ELSE
915 RAISE NO_DATA_FOUND;
916 END IF;
917
918 /* If the subordinate is for the current chain, active and their supervisor */
919 /* is active, mark the supervisor as a non-leaf */
920 IF (g_crrnt_chain(l_fetch_level(i) - 1).person_id = l_fetch_sup_id(i) AND
921 get_inv_flag_status(l_fetch_ast_id(i)) = 'N' AND
922 g_crrnt_chain(l_fetch_level(i) - 1).invalid_flag = 'N') THEN
923 g_crrnt_chain(l_fetch_level(i) - 1).leaf_node := 'N';
924 END IF;
925
926 EXCEPTION
927 WHEN NO_DATA_FOUND THEN
928
929 /* Store information about assignment to be processed */
930 g_assgnmnts_prcssd(l_fetch_asg_id(i)) := p_change_date;
931
932 /* If the end of a chain is reached then insert it */
933 IF (l_fetch_level(i) <= l_last_sub_lvl) THEN
934
935 /* Insert the changed subordinate chains */
936 update_sub_chains(p_min_lvl => l_fetch_level(i),
937 p_max_lvl => l_last_sub_lvl,
938 p_index => p_index);
939
940 END IF; -- End of chain reached
941
942 /* Get the end date */
943 l_end_date := get_end_date
944 (p_index => p_index
945 ,p_person_id => l_fetch_psn_id(i)
946 ,p_period_of_service_id => l_fetch_pos_id(i)
947 ,p_change_date => p_change_date);
948
949 g_crrnt_chain(l_fetch_level(i)).business_group_id := l_fetch_bgr_id(i);
950 g_crrnt_chain(l_fetch_level(i)).person_id := l_fetch_psn_id(i);
951 g_crrnt_chain(l_fetch_level(i)).assignment_id := l_fetch_asg_id(i);
952 g_crrnt_chain(l_fetch_level(i)).asg_status_id := l_fetch_ast_id(i);
953 g_crrnt_chain(l_fetch_level(i)).primary_asg_flag := l_fetch_prm_flg(i);
954 g_crrnt_chain(l_fetch_level(i)).end_date := l_end_date;
955 g_crrnt_chain(l_fetch_level(i)).invalid_flag :=
956 get_inv_flag_status(l_fetch_ast_id(i));
957
958 /* If the subordinate is valid then mark them a leaf node by default */
959 IF (g_crrnt_chain(l_fetch_level(i)).invalid_flag = 'N') THEN
960 g_crrnt_chain(l_fetch_level(i)).leaf_node := 'Y';
961
962 /* In addition, if their supervisor is valid then mark their */
963 /* supervisor as not a leaf node */
964 IF (g_crrnt_chain(l_fetch_level(i) - 1).invalid_flag = 'N') THEN
968 g_crrnt_chain(l_fetch_level(i)).leaf_node := null;
965 g_crrnt_chain(l_fetch_level(i) - 1).leaf_node := 'N';
966 END IF;
967 ELSE
969 END IF;
970
971 l_last_sub_lvl := l_fetch_level(i);
972
973 END;
974
975 /* Commit every so often */
976 IF (g_chain_transactions > (g_chain_chunk_size)) THEN
977 commit;
978 g_chain_transactions := 0;
979 END IF;
980
981 END LOOP;
982
983 -- exit loop if required
984 IF l_exit_sub_loop THEN
985 EXIT subordinates_loop;
986 END IF;
987
988 END LOOP;
989
990 CLOSE subordinates_csr;
991
992 IF (l_last_sub_lvl > 0) THEN
993
994 /* Insert the changed subordinate chains */
995 update_sub_chains(p_min_lvl => g_crrnt_chain_owner_lvl+1,
996 p_max_lvl => l_last_sub_lvl,
997 p_index => p_index);
998
999 END IF; -- End of chain reached
1000
1001 EXCEPTION
1002 WHEN OTHERS THEN
1003
1004 IF subordinates_csr%ISOPEN THEN
1005 CLOSE subordinates_csr;
1006 END IF;
1007
1008 /* ORA 01436 - loop in tree walk */
1009 IF (SQLCODE = -1436) THEN
1010 output('Loop found in supervisor chain for person id: ' ||
1011 to_char(g_crrnt_chain(g_crrnt_chain_owner_lvl).person_id) ||
1012 ' on ' || to_char(p_change_date,'DD-MON-YYYY'));
1013 ELSE
1014 /* Some other error */
1015 RAISE;
1016 END IF;
1017
1018 END update_subordinates;
1019
1020 /******************************************************************************/
1021 /* Updates stored leaf node information */
1022 /******************************************************************************/
1023 PROCEDURE update_leaf_node_change( p_person_id IN NUMBER,
1024 p_change_date IN DATE,
1025 p_from_leaf_flag IN VARCHAR2,
1029 CURSOR chain_csr IS
1026 p_to_leaf_flag IN VARCHAR2) IS
1027
1028 /* Selects single link in chain for a non-terminated supervisor on a date */
1030 SELECT *
1031 FROM hri_cs_suph
1032 WHERE sub_person_id = p_person_id
1033 AND sup_person_id = p_person_id
1034 AND sub_invalid_flag_code = 'N'
1035 AND sub_leaf_flag_code = p_from_leaf_flag
1036 AND p_change_date BETWEEN effective_start_date AND effective_end_date;
1037
1038 BEGIN
1039
1040 FOR chain_rec IN chain_csr LOOP
1041
1042 /* If the start dates match then update the existing chain */
1043 IF (chain_rec.effective_start_date = p_change_date) THEN
1044
1045 g_chain_transactions := g_chain_transactions + 1;
1046
1047 /* Update all links in chain at once */
1048 UPDATE hri_cs_suph
1049 SET sub_leaf_flag_code = p_to_leaf_flag
1050 WHERE sub_person_id = p_person_id
1051 AND effective_start_date = p_change_date
1052 AND sub_invalid_flag_code = 'N';
1053
1054 /* Otherwise end date existing chain and insert new one */
1055 ELSE
1056
1057 g_chain_transactions := g_chain_transactions + 2;
1058
1059 /* Insert new chain */
1060 INSERT INTO hri_cs_suph
1061 (sup_business_group_id
1062 ,sup_person_id
1063 ,sup_assignment_id
1064 ,sup_assignment_status_type_id
1065 ,sup_level
1066 ,sup_invalid_flag_code
1067 ,sub_business_group_id
1068 ,sub_person_id
1069 ,sub_assignment_id
1070 ,sub_assignment_status_type_id
1071 ,sub_primary_asg_flag_code
1072 ,sub_level
1073 ,sub_relative_level
1074 ,sub_invalid_flag_code
1075 ,orphan_flag_code
1076 ,sub_leaf_flag_code
1077 ,effective_start_date
1078 ,effective_end_date)
1079 SELECT
1080 sup_business_group_id
1081 ,sup_person_id
1082 ,sup_assignment_id
1083 ,sup_assignment_status_type_id
1084 ,sup_level
1085 ,sup_invalid_flag_code
1086 ,sub_business_group_id
1087 ,sub_person_id
1088 ,sub_assignment_id
1092 ,sub_relative_level
1089 ,sub_assignment_status_type_id
1090 ,sub_primary_asg_flag_code
1091 ,sub_level
1093 ,sub_invalid_flag_code
1105 SET effective_end_date = p_change_date - 1
1094 ,orphan_flag_code
1095 ,p_to_leaf_flag
1096 ,p_change_date
1097 ,chain_rec.effective_end_date
1098 FROM hri_cs_suph
1099 WHERE sub_person_id = p_person_id
1100 AND effective_start_date = chain_rec.effective_start_date
1101 AND sub_invalid_flag_code = 'N';
1102
1103 /* End date existing chain */
1104 UPDATE hri_cs_suph
1113
1106 WHERE sub_person_id = p_person_id
1107 AND effective_start_date = chain_rec.effective_start_date
1108 AND sub_invalid_flag_code = 'N';
1109
1110 END IF;
1111
1112 END LOOP;
1114 END update_leaf_node_change;
1115
1116
1117 /******************************************************************************/
1118 /* Loops through supervisor changes */
1119 /******************************************************************************/
1120 PROCEDURE collect_data( p_collect_from IN DATE,
1121 p_collect_to IN DATE) IS
1122
1123 /* Pick out all primary assignment supervisor changes */
1124 CURSOR supervisor_changes_csr IS
1125 SELECT
1126 asg.assignment_id assignment_id
1127 ,asg.effective_start_date asg_start
1128 ,asg.effective_end_date asg_end
1129 ,asg.business_group_id business_group_id
1130 ,asg.person_id person_id
1131 ,NVL(asg.supervisor_id , -1) supervisor_id
1132 ,DECODE(prev_asg.assignment_id,
1133 to_number(null), to_number(null),
1134 NVL(prev_asg.supervisor_id, -1)) prev_supervisor_id
1135 ,asg.assignment_status_type_id assignment_status_type_id
1136 ,pos.period_of_service_id period_of_service_id
1137 ,asg.effective_start_date change_date
1138 ,DECODE(asg.effective_start_date,
1139 pos.date_start, 'HIRE',
1140 'CHNG') event_code
1141 ,pos.actual_termination_date termination_date
1142 ,pos.final_process_date final_process_date
1143 FROM
1144 per_all_assignments_f asg
1145 ,per_periods_of_service pos
1146 ,per_all_assignments_f prev_asg
1147 WHERE asg.primary_flag = 'Y'
1151 AND asg.period_of_service_id = pos.period_of_service_id (+)
1148 AND prev_asg.primary_flag (+) = 'Y'
1149 AND asg.assignment_type = 'E'
1150 AND prev_asg.assignment_type (+) = 'E'
1152 AND prev_asg.person_id (+) = asg.person_id
1153 AND prev_asg.effective_end_date (+) = asg.effective_start_date - 1
1154 /* All non-terminated assignment supervisor changes within date range */
1155 AND ((asg.effective_start_date BETWEEN p_collect_from AND p_collect_to
1156 AND NVL(asg.supervisor_id, -1) <> NVL(prev_asg.supervisor_id, -1)
1157 AND NVL(prev_asg.assignment_id, -1) <> -1
1158 AND asg.effective_start_date <= NVL(pos.actual_termination_date, g_current_date))
1159 /* All initial hire assignments with a supervisor */
1160 OR (asg.effective_start_date = pos.date_start
1161 AND pos.date_start BETWEEN p_collect_from AND p_collect_to
1162 AND asg.supervisor_id IS NOT NULL))
1163 UNION ALL
1164 /* All terminations and final processes */
1165 SELECT /*+ leading(pos) use_hash(pos asg) */
1166 asg.assignment_id assignment_id
1167 ,asg.effective_start_date asg_start
1168 ,asg.effective_end_date asg_end
1169 ,asg.business_group_id business_group_id
1170 ,asg.person_id person_id
1171 ,to_number(null) supervisor_id
1172 ,NVL(asg.supervisor_id , -1) prev_supervisor_id
1173 ,asg.assignment_status_type_id assignment_status_type_id
1174 ,pos.period_of_service_id period_of_service_id
1175 ,pos.actual_termination_date + 1 change_date
1176 ,'TERM' event_code
1177 ,pos.actual_termination_date termination_date
1178 ,pos.final_process_date final_process_date
1179 FROM
1180 per_all_assignments_f asg
1181 ,per_periods_of_service pos
1182 WHERE asg.effective_end_date = pos.actual_termination_date
1183 AND (pos.actual_termination_date BETWEEN p_collect_from AND p_collect_to
1184 OR pos.final_process_date BETWEEN p_collect_from AND p_collect_to)
1185 AND asg.period_of_service_id = pos.period_of_service_id
1186 UNION ALL
1187 /* All subordinates of supervisors who have separated (final process) */
1188 /* whose assignments have not been updated with a new supervisor and so */
1189 /* are invalid */
1190 SELECT /*+ leading(pps) use_hash(pps sub_asg sub_pps) */
1191 sub_asg.assignment_id assignment_id
1192 ,sub_asg.effective_start_date asg_start
1193 ,sub_asg.effective_end_date asg_end
1194 ,sub_asg.business_group_id business_group_id
1195 ,sub_asg.person_id person_id
1196 ,to_number(null) supervisor_id
1197 ,sub_asg.supervisor_id prev_supervisor_id
1198 ,sub_asg.assignment_status_type_id assignment_status_type_id
1199 ,sub_pps.period_of_service_id period_of_service_id
1200 ,pps.final_process_date + 1 change_date
1201 /* Event code ORPH for subordinates orphaned by their supervisor's separation */
1202 ,'ORPH' event_code
1203 ,sub_pps.actual_termination_date actual_termination_date
1204 ,sub_pps.final_process_date final_process_date
1205 FROM
1206 per_all_assignments_f sub_asg
1207 ,per_periods_of_service pps
1208 ,per_periods_of_service sub_pps
1209 WHERE pps.final_process_date BETWEEN p_collect_from AND p_collect_to
1210 AND sub_asg.supervisor_id = pps.person_id
1211 AND sub_asg.period_of_service_id = sub_pps.period_of_service_id
1212 AND sub_asg.assignment_type = 'E'
1213 AND sub_asg.primary_flag = 'Y'
1214 AND pps.final_process_date + 1
1218 l_return_code PLS_INTEGER;
1215 BETWEEN sub_asg.effective_start_date AND sub_asg.effective_end_date
1216 ORDER BY change_date;
1217
1219 l_exit_main_loop BOOLEAN := FALSE;
1220 l_rows_fetched PLS_INTEGER := g_chunk_size;
1221 l_leaf_end_date DATE;
1222 l_leaf_flag VARCHAR2(1);
1223
1224 BEGIN
1225 -- set the global collection date range
1226 g_collect_from_date := p_collect_from;
1227 g_collect_to_date := p_collect_to;
1228 -- load TERM assignment statuses
1229 init_term_per_system_status;
1230 -- open main cursor
1231 OPEN supervisor_changes_csr;
1232 -- enter main loop
1233 <<main_loop>>
1234 LOOP
1235 -- bulk fetch rows limit the fetch to value of g_chunk_size
1236 FETCH supervisor_changes_csr
1237 BULK COLLECT INTO
1238 g_fetch_asg_id,
1239 g_fetch_strt_dt,
1240 g_fetch_end_dt,
1241 g_fetch_bgr_id,
1242 g_fetch_psn_id,
1243 g_fetch_sup_id,
1244 g_fetch_prev_sup_id,
1245 g_fetch_ast_id,
1246 g_fetch_pos_id,
1247 g_fetch_chng_dt,
1248 g_fetch_evt_code,
1249 g_fetch_term_dt,
1250 g_fetch_fprc_dt
1251 LIMIT g_chunk_size;
1252 -- check to see if the last row has been fetched
1253 IF supervisor_changes_csr%NOTFOUND THEN
1254 -- last row fetched, set exit loop flag
1255 l_exit_main_loop := TRUE;
1256 -- do we have any rows to process?
1257 l_rows_fetched := MOD(supervisor_changes_csr%ROWCOUNT,g_chunk_size);
1258 -- note: if l_rows_fetched > 0 then more rows are required to be
1259 -- processed and the l_rows_fetched will contain the exact number of
1260 -- rows left to process
1261 IF l_rows_fetched = 0 THEN
1262 -- no more rows to process so exit loop
1263 EXIT main_loop;
1264 END IF;
1265 END IF;
1266
1267 -- Loop through supervisor changes
1268 FOR i IN 1..l_rows_fetched LOOP
1269
1270 /******************************************************************************/
1271 /* Orphans */
1272 /***********/
1273 IF (g_fetch_evt_code(i) = 'ORPH') THEN
1274 BEGIN
1275 /* Process orphan if the assignment hasn't already been */
1276 /* processed on the orphaning date */
1277 IF (g_assgnmnts_prcssd(g_fetch_asg_id(i)) = g_fetch_chng_dt(i)) THEN
1278 null;
1279 ELSE
1280 RAISE NO_DATA_FOUND;
1281 END IF;
1282 EXCEPTION WHEN NO_DATA_FOUND THEN
1283 /* Store record of processing */
1284 g_assgnmnts_prcssd(g_fetch_asg_id(i)) := g_fetch_chng_dt(i);
1285 /* Calculate new chain for orphan */
1286 l_return_code := insert_supv_change(p_index => i
1287 ,p_change_date => g_fetch_chng_dt(i)
1288 ,p_event_code => 'ORPH');
1289 /* If no error encountered then update chains for all their subordinates */
1290 IF (l_return_code = 0) THEN
1291 /* Process subordinates for assignment */
1292 update_subordinates(p_index => i
1293 ,p_change_date => g_fetch_chng_dt(i)
1294 ,p_event_code => 'ORPH');
1295 /* Insert chain for assignment */
1296 process_chain(p_level => g_crrnt_chain_owner_lvl,
1297 p_end_date => g_crrnt_chain_end_date);
1298 END IF;
1299 END;
1300
1301 /******************************************************************************/
1302 /* Hires */
1303 /*********/
1304 ELSIF (g_fetch_evt_code(i) = 'HIRE') THEN
1305 BEGIN
1306 /* Process hire if the assignment hasn't already been */
1307 /* processed on the hire date */
1308 IF (g_assgnmnts_prcssd(g_fetch_asg_id(i)) = g_fetch_chng_dt(i)) THEN
1309 null;
1310 ELSE
1311 RAISE NO_DATA_FOUND;
1312 END IF;
1313 EXCEPTION WHEN NO_DATA_FOUND THEN
1314 /* Store record of processing hire */
1315 g_assgnmnts_prcssd(g_fetch_asg_id(i)) := g_fetch_chng_dt(i);
1316 /* Process chain for new hire */
1320 /* Skip processing if an error is encountered */
1317 l_return_code := insert_supv_change(p_index => i
1318 ,p_change_date => g_fetch_chng_dt(i)
1319 ,p_event_code => 'HIRE');
1321 IF (l_return_code = 0) THEN
1322 /* Test whether the new hire is a leaf node */
1323 g_crrnt_chain(g_crrnt_chain_owner_lvl).leaf_node
1324 := is_a_leaf_node(p_person_id => g_fetch_psn_id(i),
1325 p_on_date => g_fetch_chng_dt(i));
1326 /* Update new hire's manager to be a non-leaf node */
1327 /* if they weren't before */
1328 update_leaf_node_change(p_person_id => g_fetch_sup_id(i),
1329 p_change_date => g_fetch_chng_dt(i),
1330 p_from_leaf_flag => 'Y',
1331 p_to_leaf_flag => 'N');
1332 /* Process subordinates for assignment */
1333 update_subordinates(p_index => i
1334 ,p_change_date => g_fetch_chng_dt(i)
1335 ,p_event_code => 'HIRE');
1336 /* Insert chain for new hire */
1337 process_chain(p_level => g_crrnt_chain_owner_lvl,
1338 p_end_date => g_crrnt_chain_end_date);
1339 END IF;
1340 END;
1341
1342 /******************************************************************************/
1343 /* Non-terminated Supervisor change */
1344 /************************************/
1345 ELSIF (g_fetch_evt_code(i) = 'CHNG') THEN
1346 BEGIN
1347 /* Process the change if the assignment hasn't already */
1348 /* been processed on the change date */
1349 IF (g_assgnmnts_prcssd(g_fetch_asg_id(i)) = g_fetch_strt_dt(i)) THEN
1350 /* Is the previous manager of the person still supervising? */
1351 l_leaf_flag := is_a_leaf_node
1352 (p_person_id => g_fetch_prev_sup_id(i),
1353 p_on_date => g_fetch_strt_dt(i));
1354 /* If not then update chain */
1355 IF (l_leaf_flag = 'Y') THEN
1356 update_leaf_node_change(p_person_id => g_fetch_prev_sup_id(i),
1357 p_change_date => g_fetch_strt_dt(i),
1358 p_from_leaf_flag => 'N',
1359 p_to_leaf_flag => 'Y');
1360 END IF;
1361 ELSE
1362 RAISE NO_DATA_FOUND;
1363 END IF;
1364 EXCEPTION WHEN NO_DATA_FOUND THEN
1365 /* Record processing being done for assignment */
1366 g_assgnmnts_prcssd(g_fetch_asg_id(i)) := g_fetch_strt_dt(i);
1367 /* Process chain for supervisor change and all subordinates */
1368 l_return_code := insert_supv_change(p_index => i
1369 ,p_change_date => g_fetch_strt_dt(i)
1370 ,p_event_code => 'CHNG');
1371 /* If no error encountered then update chains for all their subordinates */
1372 IF (l_return_code = 0) THEN
1373 /* Process subordinates for assignment */
1374 update_subordinates(p_index => i
1375 ,p_change_date => g_fetch_strt_dt(i)
1376 ,p_event_code => 'CHNG');
1377 /* Insert chain for assignment */
1378 process_chain(p_level => g_crrnt_chain_owner_lvl,
1379 p_end_date => g_crrnt_chain_end_date);
1380 /* Update new manager to be a non-leaf node */
1381 /* if they weren't before */
1382 update_leaf_node_change(p_person_id => g_fetch_sup_id(i),
1383 p_change_date => g_fetch_strt_dt(i),
1384 p_from_leaf_flag => 'Y',
1385 p_to_leaf_flag => 'N');
1386 END IF;
1387 /* Is the previous manager of the person still supervising? */
1388 IF (g_fetch_prev_sup_id(i) > 0) THEN
1389 l_leaf_flag := is_a_leaf_node
1390 (p_person_id => g_fetch_prev_sup_id(i),
1391 p_on_date => g_fetch_strt_dt(i));
1392 /* If not then update chain */
1393 IF (l_leaf_flag = 'Y') THEN
1394 update_leaf_node_change(p_person_id => g_fetch_prev_sup_id(i),
1395 p_change_date => g_fetch_strt_dt(i),
1396 p_from_leaf_flag => 'N',
1397 p_to_leaf_flag => 'Y');
1398 END IF;
1399 END IF;
1400 END;
1401
1402 ELSE -- event code 'TERM'
1403 /******************************************************************************/
1404 /* Terminations */
1405 /****************/
1406 IF (g_fetch_term_dt(i) >= p_collect_from AND
1407 g_fetch_term_dt(i) <= p_collect_to) THEN
1408 BEGIN
1409 /* If the termination has been processed before */
1410 /* then don't process the termination */
1411 IF (g_assgnmnts_prcssd(g_fetch_asg_id(i)) = g_fetch_chng_dt(i)) THEN
1412 null;
1413 ELSE
1414 RAISE NO_DATA_FOUND;
1415 END IF;
1416 EXCEPTION WHEN NO_DATA_FOUND THEN
1417 /* Store record of processing */
1418 g_assgnmnts_prcssd(g_fetch_asg_id(i)) := g_fetch_chng_dt(i);
1419 /* If the termination occurs before the final process extra work required */
1420 IF (g_fetch_term_dt(i) <> g_fetch_fprc_dt(i) OR
1421 g_fetch_fprc_dt(i) IS NULL) THEN
1422 /* Is the terminated supervisor still supervising? */
1423 l_leaf_flag := is_a_leaf_node
1424 (p_person_id => g_fetch_psn_id(i),
1425 p_on_date => g_fetch_term_dt(i) + 1);
1426 /* If the terminated supervisor is still supervising, update their chain */
1427 /* and those of all their subordinates */
1428 IF (l_leaf_flag = 'N') THEN
1429 /* Calculate new chain for terminated supervisor */
1430 l_return_code := insert_supv_change(p_index => i
1431 ,p_change_date => g_fetch_term_dt(i) + 1
1432 ,p_event_code => 'TERM');
1433 /* If no error encountered then update chains for all their subordinates */
1434 IF (l_return_code = 0) THEN
1435 /* Process subordinates for assignment */
1436 update_subordinates(p_index => i
1437 ,p_change_date => g_fetch_term_dt(i) + 1
1438 ,p_event_code => 'TERM');
1439 /* Insert chain for assignment */
1440 process_chain(p_level => g_crrnt_chain_owner_lvl,
1441 p_end_date => g_crrnt_chain_end_date);
1442 END IF;
1443 /* otherwise end date the terminated supervisor chain */
1444 ELSE
1445 end_date_chain(p_person_id => g_fetch_psn_id(i),
1446 p_end_date => g_fetch_term_dt(i));
1447 END IF;
1448 END IF; -- Termination before final process
1449 /* Is the manager of the terminated supervisor still supervising? */
1450 l_leaf_flag := is_a_leaf_node
1451 (p_person_id => g_fetch_prev_sup_id(i),
1452 p_on_date => g_fetch_term_dt(i) + 1);
1453 /* If not then update chain */
1454 IF (l_leaf_flag = 'Y') THEN
1455 update_leaf_node_change(p_person_id => g_fetch_prev_sup_id(i),
1456 p_change_date => g_fetch_term_dt(i) + 1,
1457 p_from_leaf_flag => 'N',
1458 p_to_leaf_flag => 'Y');
1459 END IF;
1460 END;
1461 END IF;
1462
1463 /******************************************************************************/
1464 /* Final process */
1465 /*****************/
1466 IF (g_fetch_fprc_dt(i) >= p_collect_from AND
1467 g_fetch_fprc_dt(i) <= p_collect_to AND
1471 p_end_date => g_fetch_fprc_dt(i));
1468 g_fetch_term_dt(i) = g_fetch_end_dt(i)) THEN
1469 /* End date chain */
1470 end_date_chain(p_person_id => g_fetch_psn_id(i),
1472 END IF;
1473
1477
1474 /******************************************************************************/
1475
1476 END IF; -- Event codes
1478 END LOOP;
1479 -- exit loop if required
1480 IF l_exit_main_loop THEN
1481 EXIT main_loop;
1482 END IF;
1483 /* Commit every so often */
1484 IF (g_chain_transactions > (g_chain_chunk_size)) THEN
1485 commit;
1486 g_chain_transactions := 0;
1487 END IF;
1488 END LOOP;
1489
1490 CLOSE supervisor_changes_csr;
1491
1492 EXCEPTION
1493 WHEN OTHERS THEN
1494 -- unexpected error has occurred so close down
1495 -- main bulk cursor if it is open
1496 IF supervisor_changes_csr%ISOPEN THEN
1497 CLOSE supervisor_changes_csr;
1498 END IF;
1499 -- re-raise error
1500 RAISE;
1501 END collect_data;
1502
1503 /******************************************************************************/
1504 /* Main entry point to reload the historical supervisor hierarchy table */
1505 /******************************************************************************/
1506 PROCEDURE load_managers( p_start_date IN DATE,
1507 p_end_date IN DATE ) IS
1508
1509 l_sql_stmt VARCHAR2(2000);
1510 l_dummy1 VARCHAR2(2000);
1511 l_dummy2 VARCHAR2(2000);
1512 l_schema VARCHAR2(400);
1513
1514 BEGIN
1515
1516 /* Time at start */
1517 output('PL/SQL Start: ' || to_char(sysdate,'HH24:MI:SS'));
1518
1519 /* Insert new supervisor hierarchy history records */
1520 collect_data
1521 (p_collect_from => TRUNC(p_start_date)
1522 ,p_collect_to => TRUNC(p_end_date));
1523
1524 COMMIT;
1525
1526 /* Write timing information to log */
1527 output('Updated Supervisor History table: ' ||
1528 to_char(sysdate,'HH24:MI:SS'));
1529
1530 END load_managers;
1531
1532 /******************************************************************************/
1533 /* Entry point to be called from the concurrent manager */
1534 /******************************************************************************/
1535 PROCEDURE load_managers( errbuf OUT NOCOPY VARCHAR2,
1536 retcode OUT NOCOPY VARCHAR2,
1537 p_start_date IN DATE,
1538 p_end_date IN DATE )
1539
1540 IS
1541
1542 BEGIN
1543
1544 /* Enable output to concurrent request log */
1545 g_conc_request_flag := TRUE;
1546
1547 /* Call main function */
1548 load_managers
1549 (p_start_date => p_start_date
1550 ,p_end_date => p_end_date);
1551
1552 EXCEPTION
1553 WHEN OTHERS THEN
1554 errbuf := SQLERRM;
1555 retcode := SQLCODE;
1556
1557 END load_managers;
1558
1559 END hri_opl_suph_hst_inc;
[Home] [Help]