1 PACKAGE BODY hri_opl_suph_hst AS
2 /* $Header: hrioshh.pkb 120.18 2007/01/02 13:58:59 jtitmas noship $ */
3 /******************************************************************************/
4 /* */
5 /* OUTLINE / DEFINITIONS */
6 /* */
7 /* A chain is defined for an employee as a list starting with the employee */
8 /* which contains their supervisor, and successive higher level supervisors */
9 /* finishing with the highest level (overall) supervisor. */
10 /* */
11 /* Each chain is valid for the length of time it describes the supervisor */
12 /* hierarchy between the employee it is defined for and the overall */
13 /* supervisor in the hierarchy. */
14 /* */
15 /* The supervisor hierarchy table implements each link in the chain as a */
16 /* row with the employee the chain is defined for as the subordinate. The */
17 /* absolute levels refer to absolute positions within the overall hierarchy */
18 /* whereas the relative level refers to the difference in the absolute levels */
19 /* for the row. */
20 /* */
21 /* When an employee changes supervisor, their chain must change since their */
22 /* immediate supervisor is different. However, the chains of all that */
23 /* employee's subordinates must also change because a chain consists of */
24 /* each higher level supervisor up to and including the overall supervisor. */
25 /* */
26 /* IMPLEMENTATION LOGIC */
27 /* */
28 /* The supervisor hierarchy history table is populated by carrying out the */
29 /* following steps using the multi-threading wrapper
30 /* */
31 /* Pre-process (single-thread) */
32 /* =========================== */
33 /* 1) Update event queue (incremental only) */
34 /* 2) Empty out existing table (all or queued supervisors) */
35 /* 3) End date chains for queued supervisors (incremental only) */
36 /* 4) Remove indexes (full refresh only) */
37 /* 5) Disable WHO trigger */
38 /* */
39 /* Main collection (multi-thread by person) */
40 /* ======================================== */
41 /* 1) Set first date to sample management chain as later of person hire */
42 /* or refresh from date */
43 /* 2) Loop through sample dates: */
44 /* i) Insert links in chain when there is a change in supervisor, */
45 /* assignment, levels or orphan status. */
46 /* ii) Retain the next sample date as the earliest date any link in */
47 /* the sampled chain has the next supervisor change event */
48 /* 3) Exit the loop when either: */
49 /* i) No data is found - person has been terminated on the previous date */
50 /* ii) Sample date hits end of time - no further changes */
51 /* 4) Ensure PL/SQL table of rows to insert is fully updated and execute */
52 /* the bulk insert */
53 /* */
54 /* Post-process (single-thread) */
55 /* ============================ */
56 /* 1) Recreate indexes (full refresh only) */
57 /* 2) Enable WHO trigger */
58 /* */
59 /* Data Structures */
60 /* =============== */
61 /* A chain cache table stores information about each link in the chain. It */
62 /* is indexed by link level. It should be well maintained (i.e. links are */
63 /* removed when no longer required). The person being processed will always */
64 /* be the last link in the chain. */
65 /* */
66 /* Error handling */
67 /* ============== */
68 /* */
69 /* Orphans (no exception raised) */
70 /* ----------------------------- */
71 /* If the management chain is sampled and it is found that the top manager */
72 /* has a supervisor assigned then the chain is said to be orphaned. */
73 /* */
74 /* If the supervisor of the top manager has been terminated, it is possible */
75 /* they may be re-hired. This should be taken into account when deciding */
76 /* which date to next sample the hierarchy. */
77 /* */
78 /* Loops (exception explicitly trapped) */
79 /* ------------------------------------ */
80 /* When a loop is encountered the person being processed is deemed an orphan */
81 /* (since no management chain can be found for them). */
82 /* */
83 /* They are then treated as an orphan but the chain resampled at regular */
84 /* intervals up to system date in case the data is fixed at a later date. */
85 /* */
86 /* Other errors (not trapped) */
87 /* -------------------------- */
88 /* Other errors are not handled. */
89 /******************************************************************************/
90
91 -- Information to be held for each link in a chain
92 TYPE g_link_record_type IS RECORD
93 (chain_id NUMBER
94 ,person_id per_all_assignments_f.person_id%TYPE
95 ,assignment_id per_all_assignments_f.assignment_id%TYPE
96 ,business_group_id per_all_assignments_f.business_group_id%TYPE
97 ,asg_status_type_id per_all_assignments_f.assignment_status_type_id%TYPE
98 ,start_date DATE
99 ,relative_level PLS_INTEGER
100 ,orphan_flag VARCHAR2(30));
101
102 -- Information relating to transfers
103 TYPE g_trn_rec_type IS RECORD
104 (node_exists_before BOOLEAN
105 ,node_exists_after BOOLEAN
106 ,node_direct_before NUMBER
107 ,node_direct_after NUMBER);
108
109 -- Table type to hold information about the current chain
110 TYPE g_chain_type IS TABLE OF g_link_record_type INDEX BY BINARY_INTEGER;
111
112 -- Table tpye to hold transfer information
113 TYPE g_trn_tab_type IS TABLE OF g_trn_rec_type INDEX BY BINARY_INTEGER;
114
115 -- Simple table types
116 TYPE g_date_tab_type IS TABLE OF DATE INDEX BY BINARY_INTEGER;
117 TYPE g_number_tab_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
118 TYPE g_varchar2_tab_type IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;
119
120 -- PLSQL table of tables representing database table
121 g_suph_sup_psn_id g_number_tab_type;
122 g_suph_sup_asg_id g_number_tab_type;
123 g_suph_sup_ast_id g_number_tab_type;
124 g_suph_sup_level g_number_tab_type;
125 g_suph_sup_bgr_id g_number_tab_type;
126 g_suph_sup_sub1_psn_id g_number_tab_type;
127 g_suph_sup_sub2_psn_id g_number_tab_type;
128 g_suph_sup_sub3_psn_id g_number_tab_type;
129 g_suph_sup_sub4_psn_id g_number_tab_type;
130 g_suph_sub_psn_id g_number_tab_type;
131 g_suph_sub_asg_id g_number_tab_type;
132 g_suph_sub_level g_number_tab_type;
133 g_suph_sub_bgr_id g_number_tab_type;
134 g_suph_sub_rlt_lvl g_number_tab_type;
135 g_suph_sub_chain_id g_number_tab_type;
136 g_suph_start_date g_date_tab_type;
137 g_suph_end_date g_date_tab_type;
138 g_suph_orphan_flg g_varchar2_tab_type;
139 g_suph_row_count PLS_INTEGER;
140
141 -- PLSQL table of tables representing database table
142 g_chn_psn_id g_number_tab_type;
143 g_chn_asg_id g_number_tab_type;
144 g_chn_start_date g_date_tab_type;
145 g_chn_end_date g_date_tab_type;
146 g_chn_chain_id g_number_tab_type;
147 g_chn_psn_lvl g_number_tab_type;
148 g_chn_row_count PLS_INTEGER;
149
150 -- PLSQL table of tables representing database table
151 g_trn_sup_psn_id g_number_tab_type;
152 g_trn_sup_sc_fk g_number_tab_type;
153 g_trn_psn_id g_number_tab_type;
154 g_trn_ref_id g_number_tab_type;
155 g_trn_asg_id g_number_tab_type;
156 g_trn_wty_fk g_varchar2_tab_type;
157 g_trn_date g_date_tab_type;
158 g_trn_in_ind g_number_tab_type;
159 g_trn_out_ind g_number_tab_type;
160 g_trn_dir_ind g_number_tab_type;
161 g_trn_dir_rec g_number_tab_type;
162 g_trn_sec_asg_ind g_number_tab_type;
163 g_trn_hdc_trn g_number_tab_type;
164 g_trn_fte_trn g_number_tab_type;
165 g_trn_row_count PLS_INTEGER;
166
167 -- Global time variables
168 g_current_time DATE;
169 g_end_of_time DATE := hr_general.end_of_time;
170
171 -- Whether OBIEE is implemented
172 g_implement_obiee VARCHAR2(30);
173
174 -- Whether to print debug messages
175 g_debug BOOLEAN := FALSE;
176 g_log_sup_loop BOOLEAN;
177
178 -- Global HRI Multithreading Array
179 g_mthd_action_array HRI_ADM_MTHD_ACTIONS%rowtype;
180
181 -- Global parameters
182 g_refresh_start_date DATE;
183 g_full_refresh VARCHAR2(30);
184 g_load_helper_table VARCHAR2(30);
185
186 -- DBI global start date
187 g_dbi_collection_start_date DATE := TRUNC(TO_DATE(fnd_profile.value
188 ('BIS_GLOBAL_START_DATE'),'MM/DD/YYYY'));
189
190 -- Bug 4105868: Global to store msg_sub_group
191 g_msg_sub_group VARCHAR2(400);
192
193 -- Write to log
194 PROCEDURE output(p_message IN VARCHAR2) IS
195
196 BEGIN
197 HRI_BPL_CONC_LOG.output(p_message);
198 END output;
199
200 -- Write to log if debugging is set
201 PROCEDURE debug(p_message IN VARCHAR2) IS
202
203 BEGIN
204 HRI_BPL_CONC_LOG.dbg(p_message);
205 END debug;
206
207 -- Get the supervisor loop message
208 FUNCTION get_sup_loop_message(p_message IN VARCHAR2,
209 p_effective_date IN DATE,
210 p_person_id IN VARCHAR2)
211 RETURN VARCHAR2 IS
212
213 CURSOR person_name_csr IS
214 SELECT full_name
215 FROM per_people_x
216 WHERE person_id = p_person_id;
217
218 l_person_name VARCHAR2(240);
219
220 BEGIN
221
222 -- Get person name
223 OPEN person_name_csr;
224 FETCH person_name_csr INTO l_person_name;
225 CLOSE person_name_csr;
226
227 -- Set message parameters
228 fnd_message.set_name('HRI', p_message);
229 fnd_message.set_token('DATE',to_char(p_effective_date, 'YYYY/MM/DD'));
230 fnd_message.set_token('PERSON_NAME',l_person_name);
231
232 -- Get the message and return it
233 RETURN fnd_message.get;
234
235 END get_sup_loop_message;
236
237
238 -- ----------------------------------------------------------------------------
239 -- Adds change records to workforce events fact event queue
240 -- ----------------------------------------------------------------------------
241 PROCEDURE populate_wrkfc_evt_eq IS
242
243 BEGIN
244
245 IF g_implement_obiee = 'Y' THEN
246
247 -- Insert assignments with manager who have had chain changes
248 -- to workforce event fact queue
249 INSERT INTO hri_eq_wrkfc_evt
250 (assignment_id
251 ,erlst_evnt_effective_date
252 ,source_code)
253 SELECT /*+ ORDERED */
254 wevt.asg_assgnmnt_fk
255 ,eq.erlst_evnt_effective_date
256 ,'ASG_MGR_' || eq.source_code
257 FROM
258 hri_eq_sprvsr_hrchy_chgs eq
259 ,hri_mb_wrkfc_evt_ct wevt
260 WHERE wevt.time_day_evt_end_fk >= eq.erlst_evnt_effective_date
261 AND wevt.per_person_mgr_fk = eq.person_id;
262
263 INSERT INTO hri_eq_wrkfc_mnth
264 (assignment_id
265 ,erlst_evnt_effective_date
266 ,source_code)
267 SELECT /*+ ORDERED */
268 wevt.asg_assgnmnt_fk
269 ,eq.erlst_evnt_effective_date
270 ,'ASG_MGR_' || eq.source_code
271 FROM
272 hri_eq_sprvsr_hrchy_chgs eq
273 ,hri_mb_wrkfc_evt_ct wevt
274 WHERE wevt.time_day_evt_end_fk >= eq.erlst_evnt_effective_date
275 AND wevt.per_person_mgr_fk = eq.person_id;
276
277 -- commit
278 COMMIT;
279
280 END IF;
281
282 END populate_wrkfc_evt_eq;
283
284
285 -- ----------------------------------------------------------------------------
286 -- Adds change records to workforce events by manager summary event queue
287 -- ----------------------------------------------------------------------------
288 PROCEDURE populate_wrkfc_evt_mgrh_eq IS
289
290 BEGIN
291
292 IF g_implement_obiee = 'Y' THEN
293
294 INSERT INTO hri_eq_wrkfc_evt_mgrh
295 (sup_person_id
296 ,erlst_evnt_effective_date
297 ,source_code)
298 SELECT
299 person_id
300 ,erlst_evnt_effective_date
301 ,source_code
302 FROM hri_eq_sprvsr_hrchy_chgs;
303
304 -- commit
305 COMMIT;
306
307 END IF;
308
309 END populate_wrkfc_evt_mgrh_eq;
310
311
312 -- ----------------------------------------------------------------------------
313 -- POPULATE_ASG_DELTA_EQ (4259598 Incremental Changes)
314 -- This procedure inserts all records from the supervisor event queue into the
315 -- assignment event delta queue, which is used to incrementally refresh
316 -- the assignment delta table
317 --
318 -- Also, if absence is used, the corresponding absences event queue is
319 -- populated
320 -- ----------------------------------------------------------------------------
321 PROCEDURE populate_asg_delta_eq IS
322
323 BEGIN
324
325 -- 4259598 Incremental Changes
326 -- Populate the assignment event delta queue using which the assignment delta
327 -- table can be refrshed incrementally It should be noted that any point in
328 -- time there should only be one record for an assingment and type in the event
329 -- queue which contains the earliest event date for the assignment. Therefore,
330 -- if a record exists for the asg then update the record otherwise, insert a
331 -- new record for the assignment
332 IF (fnd_profile.value('HRI_IMPL_DBI') = 'Y') THEN
333
334 MERGE INTO hri_eq_asg_sup_wrfc delta_eq
335 USING (SELECT assignment_id,
336 erlst_evnt_effective_date,
337 'SUPERVISOR' source_type
338 FROM hri_eq_sprvsr_hrchy_chgs) sup_eq
339 ON ( delta_eq.source_type = 'SUPERVISOR'
340 AND sup_eq.assignment_id = delta_eq.source_id)
341 WHEN MATCHED THEN
342 UPDATE SET delta_eq.erlst_evnt_effective_date =
343 least(delta_eq.erlst_evnt_effective_date,sup_eq.erlst_evnt_effective_date)
344 WHEN NOT MATCHED THEN
345 INSERT (delta_eq.source_type,
346 delta_eq.source_id,
347 delta_eq.erlst_evnt_effective_date
348 )
349 VALUES (sup_eq.source_type,
350 sup_eq.assignment_id,
351 sup_eq.erlst_evnt_effective_date);
352 -- Commit
353 COMMIT;
354
355 END IF;
356
357 -- Check if absence is required
358 IF (fnd_profile.value('HRI_COL_ABSNCE_EVENT_EQ') = 'Y') THEN
359
360 MERGE INTO hri_eq_sup_absnc absnc_eq
361 USING (SELECT person_id,
362 erlst_evnt_effective_date,
363 'SUPERVISOR' source_type
364 FROM hri_eq_sprvsr_hrchy_chgs) sup_eq
365 ON ( absnc_eq.source_type = 'SUPERVISOR'
366 AND sup_eq.person_id = absnc_eq.source_id)
367 WHEN MATCHED THEN
368 UPDATE SET absnc_eq.erlst_evnt_effective_date =
369 LEAST(absnc_eq.erlst_evnt_effective_date,
370 sup_eq.erlst_evnt_effective_date)
371 WHEN NOT MATCHED THEN
372 INSERT (absnc_eq.source_type,
373 absnc_eq.source_id,
374 absnc_eq.erlst_evnt_effective_date
375 )
376 VALUES (sup_eq.source_type,
377 sup_eq.person_id,
378 sup_eq.erlst_evnt_effective_date);
379
380 -- Commit
381 COMMIT;
382
383 END IF;
384
385 END populate_asg_delta_eq;
386
387 -- ----------------------------------------------------------------------------
388 -- Recovers rows to insert when an exception occurs
389 -- ----------------------------------------------------------------------------
390 PROCEDURE recover_insert_rows IS
391
392 -- variables needed for populating the WHO columns
393 l_user_id NUMBER;
394
395 BEGIN
396
397 -- Initialize variables
398 l_user_id := fnd_global.user_id;
399 g_current_time := sysdate;
400
401 -- Loop through rows one at a time
402 FOR i IN 1..g_suph_row_count LOOP
403
404 -- Trap unique constraint errors
405 BEGIN
406
407 -- Perform single row insert
408 INSERT INTO hri_cs_suph
409 (sup_person_id
410 ,sup_assignment_id
411 ,sup_level
412 ,sup_business_group_id
413 ,sup_assignment_status_type_id
414 ,sup_invalid_flag_code
415 ,sup_sub1_mgr_person_fk
416 ,sup_sub2_mgr_person_fk
417 ,sup_sub3_mgr_person_fk
418 ,sup_sub4_mgr_person_fk
419 ,sub_person_id
420 ,sub_assignment_id
421 ,sub_level
422 ,sub_relative_level
423 ,sub_business_group_id
424 ,sub_invalid_flag_code
425 ,sub_primary_asg_flag_code
426 ,orphan_flag_code
427 ,sub_mngrsc_fk
428 ,effective_start_date
429 ,effective_end_date
430 ,last_update_date
431 ,last_update_login
432 ,last_updated_by
433 ,created_by
434 ,creation_date)
435 VALUES
436 (g_suph_sup_psn_id(i)
437 ,g_suph_sup_asg_id(i)
438 ,g_suph_sup_level(i)
439 ,g_suph_sup_bgr_id(i)
440 ,g_suph_sup_ast_id(i)
441 ,'N'
442 ,g_suph_sup_sub1_psn_id(i)
443 ,g_suph_sup_sub2_psn_id(i)
444 ,g_suph_sup_sub3_psn_id(i)
445 ,g_suph_sup_sub4_psn_id(i)
446 ,g_suph_sub_psn_id(i)
447 ,g_suph_sub_asg_id(i)
448 ,g_suph_sub_level(i)
449 ,g_suph_sub_rlt_lvl(i)
450 ,g_suph_sub_bgr_id(i)
451 ,'N'
452 ,'Y'
453 ,g_suph_orphan_flg(i)
454 ,g_suph_sub_chain_id(i)
455 ,g_suph_start_date(i)
456 ,g_suph_end_date(i)
457 ,g_current_time
458 ,l_user_id
459 ,l_user_id
460 ,l_user_id
461 ,g_current_time);
462
463 EXCEPTION
464 WHEN OTHERS THEN
465
466 -- Probable overlap on date tracked assignment rows
467 output('Assignment error: ' || to_char(g_suph_sub_asg_id(i)) ||
468 ' on ' || to_char(g_suph_start_date(i),'DD-MON-YYYY'));
469
470 END;
471
472 END LOOP;
473
474 FOR i IN 1..g_chn_row_count LOOP
475
476 BEGIN
477
478 INSERT INTO hri_cs_mngrsc_ct
479 (mgrs_mngrsc_pk
480 ,mgrs_person_fk
481 ,mgrs_assignment_fk
482 ,mgrs_date_start
483 ,mgrs_date_end
484 ,mgrs_level
485 ,last_update_date
486 ,last_update_login
487 ,last_updated_by
488 ,created_by
489 ,creation_date)
490 VALUES
491 (g_chn_chain_id(i)
492 ,g_chn_psn_id(i)
493 ,g_chn_asg_id(i)
494 ,g_chn_start_date(i)
495 ,g_chn_end_date(i)
496 ,g_chn_psn_lvl(i)
497 ,g_current_time
498 ,l_user_id
499 ,l_user_id
500 ,l_user_id
501 ,g_current_time);
502
503 EXCEPTION WHEN OTHERS THEN
504 -- If this insert errors the above insert will have also failed
505 null;
506 END;
507
508 END LOOP;
509
510 -- Insert manager hierarchy transfers
511 IF (g_trn_row_count > 0) THEN
512
513 FOR i IN 1..g_trn_row_count LOOP
514
515 BEGIN
516
517 INSERT INTO hri_mdp_mgrh_transfers_ct
518 (mgr_sup_person_fk
519 ,per_person_fk
520 ,asg_assgnmnt_fk
521 ,per_person_trn_fk
522 ,time_day_evt_fk
523 ,ptyp_wrktyp_fk
524 ,transfer_in_ind
525 ,transfer_out_ind
526 ,direct_ind
527 ,direct_record_ind
528 ,sec_asg_ind
529 ,last_update_date
530 ,last_update_login
531 ,last_updated_by
532 ,created_by
533 ,creation_date)
534 VALUES
535 (g_trn_sup_psn_id(i)
536 ,g_trn_psn_id(i)
537 ,g_trn_asg_id(i)
538 ,g_trn_ref_id(i)
539 ,g_trn_date(i)
540 ,g_trn_wty_fk(i)
541 ,g_trn_in_ind(i)
542 ,g_trn_out_ind(i)
543 ,g_trn_dir_ind(i)
544 ,g_trn_dir_rec(i)
545 ,g_trn_sec_asg_ind(i)
546 ,g_current_time
547 ,l_user_id
548 ,l_user_id
549 ,l_user_id
550 ,g_current_time);
551
552 EXCEPTION WHEN OTHERS THEN
553 -- If this insert errors the above insert will have also failed
554 null;
555 END;
556
557 END LOOP;
558
559 END IF;
560
561 -- Commit the chunk of rows
562 COMMIT;
563
564 -- Reset the row counters
565 g_suph_row_count := 0;
566 g_chn_row_count := 0;
567 g_trn_row_count := 0;
568
569 END recover_insert_rows;
570
571 -- ----------------------------------------------------------------------------
572 -- Bulk inserts rows from global temporary table to database table
573 -- ----------------------------------------------------------------------------
574 PROCEDURE bulk_insert_rows IS
575
576 l_user_id NUMBER;
577
578 BEGIN
579
580 -- Initialize variables
581 l_user_id := fnd_global.user_id;
582 g_current_time := sysdate;
583
584 IF (g_suph_row_count > 0) THEN
585
586 -- insert chunk of rows
587 FORALL i IN 1..g_suph_row_count
588 INSERT INTO hri_cs_suph
589 (sup_person_id
590 ,sup_assignment_id
591 ,sup_level
592 ,sup_business_group_id
593 ,sup_assignment_status_type_id
594 ,sup_invalid_flag_code
595 ,sup_sub1_mgr_person_fk
596 ,sup_sub2_mgr_person_fk
597 ,sup_sub3_mgr_person_fk
598 ,sup_sub4_mgr_person_fk
599 ,sub_person_id
600 ,sub_assignment_id
601 ,sub_level
602 ,sub_relative_level
603 ,sub_business_group_id
604 ,sub_invalid_flag_code
605 ,sub_primary_asg_flag_code
606 ,orphan_flag_code
607 ,sub_mngrsc_fk
608 ,effective_start_date
609 ,effective_end_date
610 ,last_update_date
611 ,last_update_login
612 ,last_updated_by
613 ,created_by
614 ,creation_date)
615 VALUES
616 (g_suph_sup_psn_id(i)
617 ,g_suph_sup_asg_id(i)
618 ,g_suph_sup_level(i)
619 ,g_suph_sup_bgr_id(i)
620 ,g_suph_sup_ast_id(i)
621 ,'N'
622 ,g_suph_sup_sub1_psn_id(i)
623 ,g_suph_sup_sub2_psn_id(i)
624 ,g_suph_sup_sub3_psn_id(i)
625 ,g_suph_sup_sub4_psn_id(i)
626 ,g_suph_sub_psn_id(i)
627 ,g_suph_sub_asg_id(i)
628 ,g_suph_sub_level(i)
629 ,g_suph_sub_rlt_lvl(i)
630 ,g_suph_sub_bgr_id(i)
631 ,'N'
632 ,'Y'
633 ,g_suph_orphan_flg(i)
634 ,g_suph_sub_chain_id(i)
635 ,g_suph_start_date(i)
636 ,g_suph_end_date(i)
637 ,g_current_time
638 ,l_user_id
639 ,l_user_id
640 ,l_user_id
641 ,g_current_time);
642
643 END IF;
644
645 IF (g_chn_row_count > 0) THEN
646
647 FORALL i IN 1..g_chn_row_count
648 INSERT INTO hri_cs_mngrsc_ct
649 (mgrs_mngrsc_pk
650 ,mgrs_person_fk
651 ,mgrs_assignment_fk
652 ,mgrs_date_start
653 ,mgrs_date_end
654 ,mgrs_level
655 ,last_update_date
656 ,last_update_login
657 ,last_updated_by
658 ,created_by
659 ,creation_date)
660 VALUES
661 (g_chn_chain_id(i)
662 ,g_chn_psn_id(i)
663 ,g_chn_asg_id(i)
664 ,g_chn_start_date(i)
665 ,g_chn_end_date(i)
666 ,g_chn_psn_lvl(i)
667 ,g_current_time
668 ,l_user_id
669 ,l_user_id
670 ,l_user_id
671 ,g_current_time);
672
673 END IF;
674
675 -- Insert manager hierarchy transfers
676 IF (g_trn_row_count > 0) THEN
677
678 FORALL i IN 1..g_trn_row_count
679 INSERT INTO hri_mdp_mgrh_transfers_ct
680 (mgr_sup_person_fk
681 ,per_person_fk
682 ,asg_assgnmnt_fk
683 ,per_person_trn_fk
684 ,time_day_evt_fk
685 ,ptyp_wrktyp_fk
686 ,transfer_in_ind
687 ,transfer_out_ind
688 ,direct_ind
689 ,direct_record_ind
690 ,sec_asg_ind
691 ,last_update_date
692 ,last_update_login
693 ,last_updated_by
694 ,created_by
695 ,creation_date)
696 VALUES
697 (g_trn_sup_psn_id(i)
698 ,g_trn_psn_id(i)
699 ,g_trn_asg_id(i)
700 ,g_trn_ref_id(i)
701 ,g_trn_date(i)
702 ,g_trn_wty_fk(i)
703 ,g_trn_in_ind(i)
704 ,g_trn_out_ind(i)
705 ,g_trn_dir_ind(i)
706 ,g_trn_dir_rec(i)
707 ,g_trn_sec_asg_ind(i)
708 ,g_current_time
709 ,l_user_id
710 ,l_user_id
711 ,l_user_id
712 ,g_current_time);
713
714 END IF;
715
716 -- commit the chunk of rows
717 COMMIT;
718
719 -- Reset the row counters
720 g_suph_row_count := 0;
721 g_chn_row_count := 0;
722 g_trn_row_count := 0;
723
724 EXCEPTION
725 WHEN OTHERS THEN
726
727 recover_insert_rows;
728
729 END bulk_insert_rows;
730
731
732 -- ----------------------------------------------------------------------------
733 -- Inserts row into global temporary table
734 -- ----------------------------------------------------------------------------
735 PROCEDURE insert_row(p_supv_person_id IN NUMBER
736 ,p_supv_assignment_id IN NUMBER
737 ,p_supv_level IN NUMBER
738 ,p_supv_business_group_id IN NUMBER
739 ,p_supv_asg_status_type_id IN NUMBER
740 ,p_supv_sub1_psn_id IN VARCHAR2
741 ,p_supv_sub2_psn_id IN VARCHAR2
742 ,p_supv_sub3_psn_id IN VARCHAR2
743 ,p_supv_sub4_psn_id IN VARCHAR2
744 ,p_sub_person_id IN NUMBER
745 ,p_sub_assignment_id IN NUMBER
746 ,p_sub_level IN NUMBER
747 ,p_sub_relative_level IN NUMBER
748 ,p_sub_business_group_id IN NUMBER
749 ,p_effective_start_date IN DATE
750 ,p_effective_end_date IN DATE
751 ,p_orphan_flag IN VARCHAR2
752 ,p_chain_id IN VARCHAR2) IS
753
754 BEGIN
755
756 -- increment the index
757 g_suph_row_count := g_suph_row_count + 1;
758
759 -- set the table structures
760 g_suph_sup_psn_id(g_suph_row_count) := p_supv_person_id;
761 g_suph_sup_asg_id(g_suph_row_count) := p_supv_assignment_id;
762 g_suph_sup_level(g_suph_row_count) := p_supv_level;
763 g_suph_sup_bgr_id(g_suph_row_count) := p_supv_business_group_id;
764 g_suph_sup_ast_id(g_suph_row_count) := p_supv_asg_status_type_id;
765 g_suph_sup_sub1_psn_id(g_suph_row_count) := p_supv_sub1_psn_id;
766 g_suph_sup_sub2_psn_id(g_suph_row_count) := p_supv_sub2_psn_id;
767 g_suph_sup_sub3_psn_id(g_suph_row_count) := p_supv_sub3_psn_id;
768 g_suph_sup_sub4_psn_id(g_suph_row_count) := p_supv_sub4_psn_id;
769 g_suph_sub_psn_id(g_suph_row_count) := p_sub_person_id;
770 g_suph_sub_asg_id(g_suph_row_count) := p_sub_assignment_id;
771 g_suph_sub_level(g_suph_row_count) := p_sub_level;
772 g_suph_sub_rlt_lvl(g_suph_row_count) := p_sub_relative_level;
773 g_suph_sub_bgr_id(g_suph_row_count) := p_sub_business_group_id;
774 g_suph_start_date(g_suph_row_count) := p_effective_start_date;
775 g_suph_end_date(g_suph_row_count) := p_effective_end_date;
776 g_suph_orphan_flg(g_suph_row_count) := p_orphan_flag;
777 g_suph_sub_chain_id(g_suph_row_count) := p_chain_id;
778
779 END insert_row;
780
781 -- ----------------------------------------------------------------------------
782 -- Inserts row into global temporary table
783 -- ----------------------------------------------------------------------------
784 PROCEDURE insert_chn_row(p_person_id IN NUMBER
785 ,p_assignment_id IN NUMBER
786 ,p_start_date IN DATE
787 ,p_end_date IN DATE
788 ,p_chain_id IN NUMBER
789 ,p_person_level IN NUMBER) IS
790
791 l_user_id NUMBER;
792
793 BEGIN
794
795 -- Initialize variables
796 l_user_id := fnd_global.user_id;
797 g_current_time := sysdate;
798
799 -- Add row
800 g_chn_row_count := g_chn_row_count + 1;
801 g_chn_psn_id(g_chn_row_count) := p_person_id;
802 g_chn_asg_id(g_chn_row_count) := p_assignment_id;
803 g_chn_start_date(g_chn_row_count) := p_start_date;
804 g_chn_end_date(g_chn_row_count) := p_end_date;
805 g_chn_chain_id(g_chn_row_count) := p_chain_id;
806 g_chn_psn_lvl(g_chn_row_count) := p_person_level;
807
808 END insert_chn_row;
809
810 -- ----------------------------------------------------------------------------
811 -- Inserts row into global pl/sql table
812 -- ----------------------------------------------------------------------------
813 PROCEDURE insert_trn_row(p_sup_person_id IN NUMBER
814 ,p_trn_person_id IN NUMBER
815 ,p_trn_assignment_id IN NUMBER
816 ,p_ref_person_id IN NUMBER
817 ,p_transfer_date IN DATE
818 ,p_trn_wrktyp_fk IN VARCHAR2
819 ,p_transfer_in_ind IN NUMBER
820 ,p_transfer_out_ind IN NUMBER
821 ,p_direct_ind IN NUMBER
822 ,p_direct_rec IN NUMBER
823 ,p_sec_asg_ind IN NUMBER) IS
824
825 BEGIN
826
827 -- Add row
828 g_trn_row_count := g_trn_row_count + 1;
829 g_trn_sup_psn_id(g_trn_row_count) := p_sup_person_id;
830 g_trn_psn_id(g_trn_row_count) := p_trn_person_id;
831 g_trn_ref_id(g_trn_row_count) := p_ref_person_id;
832 g_trn_asg_id(g_trn_row_count) := p_trn_assignment_id;
833 g_trn_date(g_trn_row_count) := p_transfer_date;
834 g_trn_wty_fk(g_trn_row_count) := p_trn_wrktyp_fk;
835 g_trn_in_ind(g_trn_row_count) := p_transfer_in_ind;
836 g_trn_out_ind(g_trn_row_count) := p_transfer_out_ind;
837 g_trn_dir_ind(g_trn_row_count) := p_direct_ind;
838 g_trn_dir_rec(g_trn_row_count) := p_direct_rec;
839 g_trn_sec_asg_ind(g_trn_row_count) := p_sec_asg_ind;
840
841 END insert_trn_row;
842
843 -- ----------------------------------------------------------------------------
844 -- This procedure populates the person_id column in hri_eq_sprvsr_hrchy_chgs
845 -- by using the value of assignment_id
846 -- ----------------------------------------------------------------------------
847 PROCEDURE update_event_queue IS
848
849 BEGIN
850
851 -- 3667099 The events queue may contain records for events that have taken place
852 -- to assignment records which do not affect the supervisor hierarchy, for
853 -- example secondary assingments and non employee assingments.
854 -- Delete event queue records that are related to secondary assingments
855 -- ,non employee assignments and assignments that do not have any supervisor
856 -- 4186087 If a person is made a top supervisor or if a new top supervisor is
857 -- added the event should not be deleted, otherwise the person's record may
858 -- not be correct in the hiearchy.
859 -- Removed the condition (AND supervisor_id is not null) from the inner query
860
861 -- Delete records that are not primary employee assignment change events
862 DELETE /*+ PARALLEL(eq, default,default)*/ hri_eq_sprvsr_hrchy_chgs eq
863 WHERE assignment_id NOT IN
864 (SELECT assignment_id
865 FROM per_all_assignments_f asg
866 WHERE eq.assignment_id = asg.assignment_id
867 AND primary_flag = 'Y'
868 AND assignment_type IN ('E','C')
869 AND asg.effective_end_date >= eq.erlst_evnt_effective_date);
870
871 debug(sql%rowcount || ' records deleted from supervior events queue.');
872
873 -- Commit
874 commit;
875
876 -- Set person ids on event queue
877 UPDATE hri_eq_sprvsr_hrchy_chgs eq
878 SET person_id =
879 (SELECT person_id
880 FROM per_all_assignments_f asg
881 WHERE eq.assignment_id = asg.assignment_id
882 AND rownum = 1);
883
884 EXCEPTION
885 WHEN OTHERS THEN
886
887 output('An error occured while updating events queue records.');
888 output(sqlerrm);
889 g_msg_sub_group := NVL(g_msg_sub_group, 'UPDATE_EVENT_QUEUE');
890 RAISE;
891
892 END update_event_queue;
893
894
895 -- ----------------------------------------------------------------------------
896 -- Removes records from the supervisor hierarchy after the earliest event date
897 -- End dates latest remaining record
898 -- Processes all records for the chunk
899 -- ----------------------------------------------------------------------------
900 PROCEDURE delete_and_end_date_suph_recs
901 (p_start_person_id IN NUMBER,
902 p_end_person_id IN NUMBER) IS
903
904 BEGIN
905
906 -- Delete chain updates after the date of refresh
907 DELETE FROM hri_cs_suph sph
908 WHERE sph.rowid IN
909 (SELECT sph2.rowid
910 FROM hri_eq_sprvsr_hrchy_chgs evt,
911 hri_cs_suph sph2
912 WHERE evt.person_id = sph2.sub_person_id
913 AND evt.person_id BETWEEN p_start_person_id
914 AND p_end_person_id
915 AND evt.erlst_evnt_effective_date <= sph2.effective_start_date);
916
917 debug(sql%rowcount || ' supervisor hierarchy records deleted.');
918
919 -- Delete lookup chain updates after the date of refresh
920 DELETE FROM hri_cs_mngrsc_ct chn
921 WHERE chn.rowid IN
922 (SELECT chn2.rowid
923 FROM hri_eq_sprvsr_hrchy_chgs evt,
924 hri_cs_mngrsc_ct chn2
925 WHERE evt.person_id = chn2.mgrs_person_fk
926 AND evt.person_id BETWEEN p_start_person_id
927 AND p_end_person_id
928 AND evt.erlst_evnt_effective_date <= chn2.mgrs_date_start);
929
930 debug(sql%rowcount || ' supervisor chain lookup records deleted.');
931
932 -- Set end dates to the day before the earliest effective change date
933 -- for latest chains of supervisor in event queue
934 UPDATE hri_cs_suph sph
935 SET effective_end_date =
936 (SELECT (evt.erlst_evnt_effective_date - 1)
937 FROM hri_eq_sprvsr_hrchy_chgs evt
938 WHERE evt.person_id = sph.sub_person_id
939 AND evt.erlst_evnt_effective_date BETWEEN sph.effective_start_date
940 AND sph.effective_end_date)
941 ,last_update_date = sysdate
942 WHERE (sph.sub_person_id,
943 sph.sup_person_id,
944 sph.effective_start_date) IN
945 (SELECT
946 sph2.sub_person_id,
947 sph2.sup_person_id,
948 sph2.effective_start_date
949 FROM hri_eq_sprvsr_hrchy_chgs evt,
950 hri_cs_suph sph2
951 WHERE evt.person_id = sph2.sub_person_id
952 AND evt.person_id BETWEEN p_start_person_id
953 AND p_end_person_id
954 AND evt.erlst_evnt_effective_date BETWEEN sph2.effective_start_date
955 AND sph2.effective_end_date);
956
957 debug(sql%rowcount || ' supervisor hierarchy records end dated.');
958
959 -- Set end dates to the day before the earliest effective change date
960 -- for latest lookup chains of supervisor in event queue
961 UPDATE hri_cs_mngrsc_ct chn
962 SET chn.mgrs_date_end =
963 (SELECT (evt.erlst_evnt_effective_date - 1)
964 FROM hri_eq_sprvsr_hrchy_chgs evt
965 WHERE evt.person_id = chn.mgrs_person_fk
966 AND evt.erlst_evnt_effective_date BETWEEN chn.mgrs_date_start
967 AND chn.mgrs_date_end)
968 ,last_update_date = sysdate
969 WHERE chn.mgrs_mngrsc_pk IN
970 (SELECT
971 chn2.mgrs_mngrsc_pk
972 FROM hri_eq_sprvsr_hrchy_chgs evt,
973 hri_cs_mngrsc_ct chn2
974 WHERE evt.person_id = chn2.mgrs_person_fk
975 AND evt.person_id BETWEEN p_start_person_id
976 AND p_end_person_id
977 AND evt.erlst_evnt_effective_date BETWEEN chn.mgrs_date_start
978 AND chn.mgrs_date_end);
979
980 debug(sql%rowcount || ' supervisor lookup records end dated.');
981
982 EXCEPTION
983 WHEN OTHERS THEN
984
985 output('An error occured while deleting and end dating records');
986 output(SQLERRM);
987 g_msg_sub_group := NVL(g_msg_sub_group, 'END_DATE_PRIOR_RECORDS');
988 RAISE;
989
990 END delete_and_end_date_suph_recs;
991
992 -- ----------------------------------------------------------------------------
993 -- Removes later duplicate events for a person in hri_eq_sprvsr_hrchy_chgs
994 -- leaving only the earliest recorded event held in the table
995 -- ----------------------------------------------------------------------------
996 PROCEDURE remove_duplicates IS
997
998 BEGIN
999
1000 -- Delete duplicate events from queue
1001 DELETE FROM hri_eq_sprvsr_hrchy_chgs evt
1002 WHERE EXISTS
1003 (SELECT 'x'
1004 FROM hri_eq_sprvsr_hrchy_chgs evt2
1005 WHERE evt2.person_id = evt.person_id
1006 AND ((evt.erlst_evnt_effective_date = evt2.erlst_evnt_effective_date
1007 AND evt.rowid < evt2.rowid)
1008 OR
1009 evt.erlst_evnt_effective_date > evt2.erlst_evnt_effective_date));
1010
1011 debug(sql%rowcount || ' duplicate records deleted.');
1012
1013 -- Commit
1014 commit;
1015
1016 EXCEPTION
1017 WHEN OTHERS THEN
1018
1019 output('An error occured while removing duplicates from the change list.');
1020 output(SQLERRM);
1021 g_msg_sub_group := NVL(g_msg_sub_group, 'REMOVE_DUPLICATES');
1022 RAISE;
1023
1024 END remove_duplicates;
1025
1026 -- ----------------------------------------------------------------------------
1027 -- For every change for a supervisor there is a knock on effect on his
1028 -- subordinates i.e. the supervisor hierarchy for the subordinates changes.
1029 -- This procedure finds the subordinates for a supervisor that has an event
1030 -- and inserts them into 'hri_eq_sprvsr_hrchy_chgs'.
1031 -- ----------------------------------------------------------------------------
1032 PROCEDURE find_subordinates IS
1033
1034 BEGIN
1035
1036 -- Insert subordinate records into event queue
1037 INSERT /*+ append */ INTO hri_eq_sprvsr_hrchy_chgs
1038 (person_id
1039 ,assignment_id
1040 ,erlst_evnt_effective_date
1041 ,source_code)
1042 SELECT
1043 sph.sub_person_id
1044 ,sph.sub_assignment_id
1045 ,GREATEST(evt.erlst_evnt_effective_date,sph.effective_start_date)
1046 ,'DERIVED'
1047 FROM
1048 hri_eq_sprvsr_hrchy_chgs evt
1049 ,hri_cs_suph sph
1050 WHERE sph.sup_person_id = evt.person_id
1051 AND sph.sub_relative_level > 0
1052 AND sph.effective_end_date >= evt.erlst_evnt_effective_date;
1053
1054 debug(sql%rowcount || ' subordinate records inserted.');
1055
1056 -- Commit
1057 commit;
1058
1059 EXCEPTION
1060 WHEN OTHERS THEN
1061
1062 output('An error occured while adding subordinates to the change list.');
1063 output(SQLERRM);
1064 g_msg_sub_group := NVL(g_msg_sub_group, 'FIND_SUBORDINATES');
1065 RAISE;
1066
1067 END find_subordinates;
1068
1069 -- ----------------------------------------------------------------------------
1070 -- Runs given sql statement dynamically
1071 -- ----------------------------------------------------------------------------
1072 PROCEDURE run_sql_stmt_noerr(p_sql_stmt VARCHAR2) IS
1073
1074 BEGIN
1075
1076 EXECUTE IMMEDIATE p_sql_stmt;
1077
1078 EXCEPTION WHEN OTHERS THEN
1079
1080 output('Error running sql:');
1081 output(SUBSTR(p_sql_stmt,1,230));
1082
1083 END run_sql_stmt_noerr;
1084
1085 -- ----------------------------------------------------------------------------
1086 -- Sets global parameters from multi-threading process parameters
1087 -- ----------------------------------------------------------------------------
1088 PROCEDURE set_parameters(p_mthd_action_id IN NUMBER) IS
1089
1090 BEGIN
1091
1092 -- If parameters haven't already been set, then set them
1093 IF (g_refresh_start_date IS NULL) THEN
1094 g_mthd_action_array := hri_opl_multi_thread.get_mthd_action_array
1095 (p_mthd_action_id);
1096 g_refresh_start_date := g_mthd_action_array.collect_from_date;
1097 g_full_refresh := g_mthd_action_array.full_refresh_flag;
1098 g_load_helper_table := g_mthd_action_array.attribute1;
1099 IF (fnd_profile.value('HRI_IMPL_OBIEE') = 'Y') THEN
1100 g_implement_obiee := 'Y';
1101 ELSE
1102 g_implement_obiee := 'N';
1103 END IF;
1104 END IF;
1105
1106 END set_parameters;
1107
1108 -- ----------------------------------------------------------------------------
1109 -- Returns next value from chain id sequence
1110 -- ----------------------------------------------------------------------------
1111 FUNCTION get_chain_id RETURN NUMBER IS
1112
1113 l_chain_id NUMBER;
1114
1115 BEGIN
1116
1117 SELECT hri_cs_mngrsc_ct_s.nextval
1118 INTO l_chain_id
1119 FROM dual;
1120
1121 RETURN l_chain_id;
1122
1123 END get_chain_id;
1124
1125
1126 -- ----------------------------------------------------------------------------
1127 -- Given a transfer table containing all nodes before and after with details
1128 -- of whether each node existed in the transferees management chain before and
1129 -- after, insert a row for each node having the transfer.
1130 -- ----------------------------------------------------------------------------
1131 PROCEDURE process_transfer
1132 (p_trn_psn_id IN NUMBER,
1133 p_trn_asg_id IN NUMBER,
1134 p_trn_aty_id IN VARCHAR2,
1135 p_trn_date IN DATE,
1136 p_trn_tab IN g_trn_tab_type) IS
1137
1138 -- All secondary assignments reporting to the transferee before
1139 -- and after the transfer
1140 CURSOR sec_directs_csr IS
1141 SELECT
1142 sec_pre.person_id
1143 ,sec_pre.assignment_id
1144 ,CASE WHEN sec_pre.assignment_type = 'E'
1145 THEN 'EMP'
1146 ELSE 'CWK'
1147 END ptyp_wrktyp_fk
1148 FROM
1149 per_all_assignments_f sec_pre
1150 ,per_all_assignments_f sec_post
1151 WHERE sec_pre.supervisor_id = p_trn_psn_id
1152 AND sec_post.assignment_id = sec_pre.assignment_id
1153 AND sec_post.supervisor_id = sec_pre.supervisor_id
1154 AND sec_post.primary_flag = 'N'
1155 AND sec_pre.assignment_type IN ('E','C')
1156 AND p_trn_date - 1 BETWEEN sec_pre.effective_start_date AND sec_pre.effective_end_date
1157 AND p_trn_date BETWEEN sec_post.effective_start_date AND sec_post.effective_end_date;
1158
1159 l_idx NUMBER;
1160 l_direct_ind NUMBER;
1161 l_transfer_in_ind NUMBER;
1162 l_transfer_out_ind NUMBER;
1163 l_wrktyp_fk VARCHAR2(30);
1164
1165 l_sec_psn_tab g_number_tab_type;
1166 l_sec_asg_tab g_number_tab_type;
1167 l_sec_wrktyp_tab g_varchar2_tab_type;
1168
1169 BEGIN
1170
1171 -- Load list of secondary assignments reporting to the transferee before
1172 -- and after the transfer
1173 OPEN sec_directs_csr;
1174 FETCH sec_directs_csr BULK COLLECT INTO
1175 l_sec_psn_tab, l_sec_asg_tab, l_sec_wrktyp_tab;
1176 CLOSE sec_directs_csr;
1177
1178 -- Set worker type for transferee
1179 IF p_trn_aty_id = 'E' THEN
1180 l_wrktyp_fk := 'EMP';
1181 ELSE
1182 l_wrktyp_fk := 'CWK';
1183 END IF;
1184
1185 l_idx := p_trn_tab.FIRST;
1186
1187 WHILE l_idx IS NOT NULL LOOP
1188
1189 -- If node exists before and after transfer it is a transfer within
1190 -- the hierarchy, so do not do anything
1191 IF (p_trn_tab(l_idx).node_exists_before AND
1192 p_trn_tab(l_idx).node_exists_after) THEN
1193 null;
1194
1195 ELSE
1196
1197 -- If node exists before (but not after) then it is a transfer out
1198 IF (p_trn_tab(l_idx).node_exists_before) THEN
1199
1200 l_transfer_in_ind := 0;
1201 l_transfer_out_ind := 1;
1202 l_direct_ind := p_trn_tab(l_idx).node_direct_before;
1203
1204 -- If node exists after (but not before) then it is a transfer in
1205 ELSE
1206
1207 l_transfer_in_ind := 1;
1208 l_transfer_out_ind := 0;
1209 l_direct_ind := p_trn_tab(l_idx).node_direct_after;
1210
1211 END IF;
1212
1213 -- Insert transfer record for transferee
1214 insert_trn_row
1215 (p_sup_person_id => l_idx
1216 ,p_trn_person_id => p_trn_psn_id
1217 ,p_trn_assignment_id => p_trn_asg_id
1218 ,p_ref_person_id => -1
1219 ,p_transfer_date => p_trn_date
1220 ,p_trn_wrktyp_fk => l_wrktyp_fk
1221 ,p_transfer_in_ind => l_transfer_in_ind
1222 ,p_transfer_out_ind => l_transfer_out_ind
1223 ,p_direct_ind => l_direct_ind
1224 ,p_direct_rec => 0
1225 ,p_sec_asg_ind => 0);
1226
1227 -- Insert transfer record for any secondary assignments reporting to the
1228 -- transferee before and after transfer
1229 IF l_sec_psn_tab.EXISTS(1) THEN
1230 FOR i IN 1..l_sec_psn_tab.LAST LOOP
1231 insert_trn_row
1232 (p_sup_person_id => l_idx
1233 ,p_trn_person_id => l_sec_psn_tab(i)
1234 ,p_trn_assignment_id => l_sec_asg_tab(i)
1235 ,p_ref_person_id => p_trn_psn_id
1236 ,p_transfer_date => p_trn_date
1237 ,p_trn_wrktyp_fk => l_sec_wrktyp_tab(i)
1238 ,p_transfer_in_ind => l_transfer_in_ind
1239 ,p_transfer_out_ind => l_transfer_out_ind
1240 ,p_direct_ind => 0
1241 ,p_direct_rec => 0
1242 ,p_sec_asg_ind => 1);
1243 END LOOP;
1244 END IF;
1245
1246 END IF;
1247
1248 -- Filter out direct record transfers within
1249 IF (p_trn_tab(l_idx).node_direct_before = 1 AND
1250 p_trn_tab(l_idx).node_direct_after = 1) THEN
1251
1252 null;
1253
1254 ELSE
1255
1256 -- If node is a direct manager before but not after it is a direct record transfer out
1257 IF (p_trn_tab(l_idx).node_direct_before = 1) THEN
1258
1259 -- Insert transfer record for transferee
1260 insert_trn_row
1261 (p_sup_person_id => l_idx
1262 ,p_trn_person_id => p_trn_psn_id
1263 ,p_trn_assignment_id => p_trn_asg_id
1264 ,p_ref_person_id => -1
1265 ,p_transfer_date => p_trn_date
1266 ,p_trn_wrktyp_fk => l_wrktyp_fk
1267 ,p_transfer_in_ind => 0
1268 ,p_transfer_out_ind => 1
1269 ,p_direct_ind => 1
1270 ,p_direct_rec => 1
1271 ,p_sec_asg_ind => 0);
1272
1273 -- If node is a direct manager after but not before it is a direct record transfer in
1274 ELSIF (p_trn_tab(l_idx).node_direct_after = 1) THEN
1275
1276 -- Insert transfer record for transferee
1277 insert_trn_row
1278 (p_sup_person_id => l_idx
1279 ,p_trn_person_id => p_trn_psn_id
1280 ,p_trn_assignment_id => p_trn_asg_id
1281 ,p_ref_person_id => -1
1282 ,p_transfer_date => p_trn_date
1283 ,p_trn_wrktyp_fk => l_wrktyp_fk
1284 ,p_transfer_in_ind => 1
1285 ,p_transfer_out_ind => 0
1286 ,p_direct_ind => 1
1287 ,p_direct_rec => 1
1288 ,p_sec_asg_ind => 0);
1289
1290 END IF;
1291
1292 END IF;
1293
1294 -- Increment index
1295 l_idx := p_trn_tab.NEXT(l_idx);
1296
1297 END LOOP;
1298
1299 END process_transfer;
1300
1301
1302 -- ----------------------------------------------------------------------------
1303 -- Given a chain cache table containing the previous manager chain and a new
1304 -- manager chain
1305 -- - Insert new chain records
1306 -- - Insert chain id lookups
1307 -- ----------------------------------------------------------------------------
1308 PROCEDURE process_chain
1309 (p_new_psn_tab IN g_number_tab_type,
1310 p_new_asg_tab IN g_number_tab_type,
1311 p_new_bgr_tab IN g_number_tab_type,
1312 p_new_sup_tab IN g_number_tab_type,
1313 p_new_ast_tab IN g_number_tab_type,
1314 p_new_end_tab IN g_date_tab_type,
1315 p_new_aty_tab IN g_varchar2_tab_type,
1316 p_orphan_flag IN VARCHAR2,
1317 p_chain_table IN OUT NOCOPY g_chain_type,
1318 p_loop_date IN DATE,
1319 p_next_loop_date IN DATE) IS
1320
1321 l_sup_level PLS_INTEGER;
1322 l_sub_level PLS_INTEGER;
1323
1324 l_chain_id NUMBER;
1325 l_sup_sub1_psn_id NUMBER;
1326 l_sup_sub2_psn_id NUMBER;
1327 l_sup_sub3_psn_id NUMBER;
1328 l_sup_sub4_psn_id NUMBER;
1329
1330 l_chain_end_date DATE;
1331
1332 l_trn_tab g_trn_tab_type;
1333 l_is_a_trn BOOLEAN;
1334
1335 BEGIN
1336
1337 -- Get chain id
1338 l_chain_id := get_chain_id;
1339
1340 -- Set chain end date
1341 IF (p_next_loop_date = g_end_of_time) THEN
1342 l_chain_end_date := g_end_of_time;
1343 ELSE
1344 l_chain_end_date := p_next_loop_date - 1;
1345 END IF;
1346
1347 -- Set the new level for the person (equal to the number of links in
1348 -- the new chain)
1349 l_sub_level := p_new_psn_tab.LAST;
1350
1351 -- Load manager chain before transfer
1352 IF p_chain_table.EXISTS(1) THEN
1353 l_is_a_trn := TRUE;
1354 FOR i IN 1..p_chain_table.LAST LOOP
1355 l_trn_tab(p_chain_table(i).person_id).node_exists_before := TRUE;
1356 IF i = p_chain_table.LAST - 1 THEN
1357 l_trn_tab(p_chain_table(i).person_id).node_direct_before := 1;
1358 ELSE
1359 l_trn_tab(p_chain_table(i).person_id).node_direct_before := 0;
1360 END IF;
1361 END LOOP;
1362 ELSE
1363 l_is_a_trn := FALSE;
1364 END IF;
1365
1366 -- Loop through new management chain (top supervisor last)
1367 FOR i IN 1..l_sub_level LOOP
1368
1369 -- Set manager chain after transfer
1370 l_trn_tab(p_new_psn_tab(i)).node_exists_after := TRUE;
1371 IF i = 2 THEN
1372 l_trn_tab(p_new_psn_tab(i)).node_direct_after := 1;
1373 ELSE
1374 l_trn_tab(p_new_psn_tab(i)).node_direct_after := 0;
1375 END IF;
1376
1377 -- Set level for link as default order is reverse level order
1378 l_sup_level := l_sub_level - i + 1;
1379
1380 -- Set relative levels
1381 IF (i - 1) >= 1 THEN
1382 l_sup_sub1_psn_id := p_new_psn_tab(i - 1);
1383 ELSE
1384 l_sup_sub1_psn_id := p_new_psn_tab(1);
1385 END IF;
1386 IF (i - 2) >= 1 THEN
1387 l_sup_sub2_psn_id := p_new_psn_tab(i - 2);
1388 ELSE
1389 l_sup_sub2_psn_id := p_new_psn_tab(1);
1390 END IF;
1391 IF (i - 3) >= 1 THEN
1392 l_sup_sub3_psn_id := p_new_psn_tab(i - 3);
1393 ELSE
1394 l_sup_sub3_psn_id := p_new_psn_tab(1);
1395 END IF;
1396 IF (i - 4) >= 1 THEN
1397 l_sup_sub4_psn_id := p_new_psn_tab(i - 4);
1398 ELSE
1399 l_sup_sub4_psn_id := p_new_psn_tab(1);
1400 END IF;
1401
1402 -- Insert row
1403 insert_row
1404 (p_supv_person_id => p_new_psn_tab(i)
1405 ,p_supv_assignment_id => p_new_asg_tab(i)
1406 ,p_supv_level => l_sup_level
1407 ,p_supv_business_group_id => p_new_bgr_tab(i)
1408 ,p_supv_asg_status_type_id => p_new_ast_tab(i)
1409 ,p_supv_sub1_psn_id => l_sup_sub1_psn_id
1410 ,p_supv_sub2_psn_id => l_sup_sub2_psn_id
1411 ,p_supv_sub3_psn_id => l_sup_sub3_psn_id
1412 ,p_supv_sub4_psn_id => l_sup_sub4_psn_id
1413 ,p_sub_person_id => p_new_psn_tab(1)
1414 ,p_sub_assignment_id => p_new_asg_tab(1)
1415 ,p_sub_level => l_sub_level
1416 ,p_sub_relative_level => l_sub_level - l_sup_level
1417 ,p_sub_business_group_id => p_new_bgr_tab(1)
1418 ,p_effective_start_date => p_loop_date
1419 ,p_effective_end_date => l_chain_end_date
1420 ,p_orphan_flag => p_orphan_flag
1421 ,p_chain_id => l_chain_id);
1422
1423 -- Update chain table with new link details
1424 p_chain_table(l_sup_level).person_id := p_new_psn_tab(i);
1425 p_chain_table(l_sup_level).assignment_id := p_new_asg_tab(i);
1426 p_chain_table(l_sup_level).business_group_id := p_new_bgr_tab(i);
1427 p_chain_table(l_sup_level).asg_status_type_id := p_new_ast_tab(i);
1428 p_chain_table(l_sup_level).start_date := p_loop_date;
1429 p_chain_table(l_sup_level).relative_level := l_sub_level - l_sup_level;
1430 p_chain_table(l_sup_level).orphan_flag := p_orphan_flag;
1431
1432 END LOOP;
1433
1434 -- Remove any additional records in the chain table that result from
1435 -- a promotion (person decrease in absolute level)
1436 FOR i IN (l_sub_level + 1)..p_chain_table.LAST LOOP
1437 p_chain_table.DELETE(i);
1438 END LOOP;
1439
1440 -- Insert chain lookup
1441 insert_chn_row
1442 (p_person_id => p_new_psn_tab(1)
1443 ,p_assignment_id => p_new_asg_tab(1)
1444 ,p_start_date => p_loop_date
1445 ,p_end_date => l_chain_end_date
1446 ,p_chain_id => l_chain_id
1447 ,p_person_level => l_sub_level);
1448
1449 -- Process transfer
1450 IF l_is_a_trn THEN
1451 process_transfer
1452 (p_trn_psn_id => p_new_psn_tab(1),
1453 p_trn_asg_id => p_new_asg_tab(1),
1454 p_trn_aty_id => p_new_aty_tab(1),
1455 p_trn_date => p_loop_date,
1456 p_trn_tab => l_trn_tab);
1457 END IF;
1458
1459 END process_chain;
1460
1461
1462 -- ----------------------------------------------------------------------------
1463 -- Tree-walk the manager hierarchy for a person on a given date
1464 -- Trap loops and return as orphans
1465 -- ----------------------------------------------------------------------------
1466 PROCEDURE get_manager_chain
1467 (p_person_id IN NUMBER,
1468 p_effective_date IN DATE,
1469 p_hier_psn_tab OUT NOCOPY g_number_tab_type,
1470 p_hier_asg_tab OUT NOCOPY g_number_tab_type,
1471 p_hier_bgr_tab OUT NOCOPY g_number_tab_type,
1472 p_hier_sup_tab OUT NOCOPY g_number_tab_type,
1473 p_hier_ast_tab OUT NOCOPY g_number_tab_type,
1474 p_hier_end_tab OUT NOCOPY g_date_tab_type,
1475 p_hier_aty_tab OUT NOCOPY g_varchar2_tab_type,
1476 p_next_change_date OUT NOCOPY DATE) IS
1477
1478 -- Return PL/SQL tables
1479 l_psn_tab g_number_tab_type;
1480 l_asg_tab g_number_tab_type;
1481 l_bgr_tab g_number_tab_type;
1482 l_sup_tab g_number_tab_type;
1483 l_ast_tab g_number_tab_type;
1484 l_end_tab g_date_tab_type;
1485 l_aty_tab g_varchar2_tab_type;
1486
1487 -- Loop message
1488 l_loop_msg VARCHAR2(2000);
1489
1490 -- Main tree walk returns rows in default order starting with
1491 -- the person and ending with the top manager
1492 CURSOR manager_chain_csr(v_effective_date DATE) IS
1493 SELECT
1494 hier.person_id
1495 ,hier.assignment_id
1496 ,hier.business_group_id
1497 ,hier.supervisor_person_id
1498 ,hier.assignment_status_type_id
1499 ,hier.effective_end_date
1500 ,hier.assignment_type
1501 FROM
1502 (SELECT
1503 ase.person_id
1504 ,ase.assignment_id
1505 ,ase.business_group_id
1506 ,ase.supervisor_person_id
1507 ,ase.assignment_status_type_id
1508 ,ase.effective_end_date
1509 ,ase.assignment_type
1510 FROM
1511 hri_cs_asgn_suph_events_ct ase
1512 WHERE ase.primary_flag = 'Y'
1513 AND v_effective_date BETWEEN ase.effective_start_date
1514 AND ase.effective_end_date
1515 ) hier
1516 START WITH hier.person_id = p_person_id
1517 CONNECT BY hier.person_id = PRIOR hier.supervisor_person_id;
1518 -- NO ORDER BY LEAVE DEFAULT!
1519
1520 -- If the main tree walk fails then treat the person as an orphan
1521 -- and get their next change date which is the earlier of:
1522 -- - Next change date
1523 -- - 1 month on provided this is less than system date
1524 CURSOR loop_in_chain_csr(v_effective_date DATE) IS
1525 SELECT
1526 ase.person_id
1527 ,ase.assignment_id
1528 ,ase.business_group_id
1529 ,ase.supervisor_person_id
1530 ,ase.assignment_status_type_id
1531 ,CASE WHEN v_effective_date >= ADD_MONTHS(TRUNC(SYSDATE), -1)
1532 THEN ase.effective_end_date
1533 ELSE LEAST(ase.effective_end_date, ADD_MONTHS(v_effective_date, 1))
1534 END
1535 ,ase.assignment_type
1536 FROM
1537 hri_cs_asgn_suph_events_ct ase
1538 WHERE ase.person_id = p_person_id
1539 AND ase.primary_flag = 'Y'
1540 AND v_effective_date BETWEEN ase.effective_start_date
1541 AND ase.effective_end_date;
1542
1543 BEGIN
1544
1545 -- PL/SQL block to trap loop exceptions
1546 BEGIN
1547
1548 -- Get first supervisor chain for person
1549 OPEN manager_chain_csr(p_effective_date);
1550 FETCH manager_chain_csr
1551 BULK COLLECT INTO
1552 l_psn_tab,
1553 l_asg_tab,
1554 l_bgr_tab,
1555 l_sup_tab,
1556 l_ast_tab,
1557 l_end_tab,
1558 l_aty_tab;
1559 CLOSE manager_chain_csr;
1560
1561 -- Loop not encountered, so output next loop message
1562 g_log_sup_loop := TRUE;
1563
1564 EXCEPTION WHEN OTHERS THEN
1565
1566 -- Close cursor
1567 IF manager_chain_csr%ISOPEN THEN
1568 CLOSE manager_chain_csr;
1569 END IF;
1570
1571 -- Log message if first iteration of encountering loop
1572 IF g_log_sup_loop THEN
1573
1574 -- Loop diagnostics
1575 l_loop_msg := get_sup_loop_message
1576 (p_message => 'HRI_407283_SUP_LOOP_MSG'
1577 ,p_effective_date => p_effective_date
1578 ,p_person_id => p_person_id);
1579
1580 -- Write message to concurrent program log
1581 output(l_loop_msg);
1582
1583 -- Write message to log table hri_adm_msg_log
1584 hri_bpl_conc_log.log_process_info
1585 (p_package_name => 'HRI_OPL_SUPH_HST'
1586 ,p_msg_type => 'WARNING'
1587 ,p_effective_date => p_effective_date
1588 ,p_person_id => p_person_id
1589 ,p_note => l_loop_msg
1590 ,p_msg_group => 'SUP_LOOP');
1591
1592 END IF;
1593
1594 -- Do not output further loop messages until the loop is fixed
1595 g_log_sup_loop := FALSE;
1596
1597 -- Loop in chain
1598 -- get default information for the person
1599 OPEN loop_in_chain_csr(p_effective_date);
1600 FETCH loop_in_chain_csr
1601 BULK COLLECT INTO
1602 l_psn_tab,
1603 l_asg_tab,
1604 l_bgr_tab,
1605 l_sup_tab,
1606 l_ast_tab,
1607 l_end_tab,
1608 l_aty_tab;
1609 CLOSE loop_in_chain_csr;
1610
1611 END;
1612
1613 -- Get next change date
1614 IF l_end_tab.EXISTS(1) THEN
1615
1616 -- Initialize to end of time
1617 p_next_change_date := g_end_of_time;
1618
1619 -- Set to day after earliest link end date
1620 FOR i IN 1..l_end_tab.LAST LOOP
1621 IF (l_end_tab(i) < p_next_change_date) THEN
1622 p_next_change_date := l_end_tab(i) + 1;
1623 END IF;
1624 END LOOP;
1625
1626 END IF;
1627
1628 -- Return the tables
1629 p_hier_psn_tab := l_psn_tab;
1630 p_hier_asg_tab := l_asg_tab;
1631 p_hier_bgr_tab := l_bgr_tab;
1632 p_hier_sup_tab := l_sup_tab;
1633 p_hier_ast_tab := l_ast_tab;
1634 p_hier_end_tab := l_end_tab;
1635 p_hier_aty_tab := l_aty_tab;
1636
1637 EXCEPTION WHEN OTHERS THEN
1638
1639 IF loop_in_chain_csr%ISOPEN THEN
1640 CLOSE loop_in_chain_csr;
1641 END IF;
1642
1643 g_msg_sub_group := NVL(g_msg_sub_group, 'GET_MANAGER_CHAIN');
1644
1645 RAISE;
1646
1647 END get_manager_chain;
1648
1649
1650 -- ----------------------------------------------------------------------------
1651 -- Initializes chain cache in incremental mode
1652 -- ----------------------------------------------------------------------------
1653 PROCEDURE initialize_previous_chain
1654 (p_person_id IN NUMBER,
1655 p_effective_date IN DATE,
1656 p_chain_table IN OUT NOCOPY g_chain_type) IS
1657
1658 -- Results of tree walk
1659 l_hier_psn_tab g_number_tab_type;
1660 l_hier_asg_tab g_number_tab_type;
1661 l_hier_bgr_tab g_number_tab_type;
1662 l_hier_sup_tab g_number_tab_type;
1663 l_hier_ast_tab g_number_tab_type;
1664 l_hier_end_tab g_date_tab_type;
1665 l_hier_aty_tab g_varchar2_tab_type;
1666 l_dummy DATE;
1667 l_sup_level NUMBER;
1668
1669 BEGIN
1670
1671 -- Leave chain table as NULL in full refresh mode
1672 -- as there data before global start date are ignored
1673 IF (g_full_refresh = 'N') THEN
1674
1675 -- Get previous supervisor chain for person
1676 get_manager_chain
1677 (p_person_id => p_person_id,
1678 p_effective_date => p_effective_date,
1679 p_hier_psn_tab => l_hier_psn_tab,
1680 p_hier_asg_tab => l_hier_asg_tab,
1681 p_hier_bgr_tab => l_hier_bgr_tab,
1682 p_hier_sup_tab => l_hier_sup_tab,
1683 p_hier_ast_tab => l_hier_ast_tab,
1684 p_hier_end_tab => l_hier_end_tab,
1685 p_hier_aty_tab => l_hier_aty_tab,
1686 p_next_change_date => l_dummy);
1687
1688 -- If previous chain found
1689 IF l_hier_psn_tab.EXISTS(1) THEN
1690
1691 -- Loop through previous chain
1692 FOR i IN 1..l_hier_psn_tab.LAST LOOP
1693
1694 -- Set level for link as default order is reverse level order
1695 l_sup_level := l_hier_psn_tab.LAST - i + 1;
1696
1697 -- Update chain table with new link details
1698 p_chain_table(l_sup_level).person_id := l_hier_psn_tab(i);
1699 p_chain_table(l_sup_level).assignment_id := l_hier_asg_tab(i);
1700 p_chain_table(l_sup_level).business_group_id := l_hier_bgr_tab(i);
1701 p_chain_table(l_sup_level).asg_status_type_id := l_hier_ast_tab(i);
1702 p_chain_table(l_sup_level).start_date := p_effective_date;
1703 p_chain_table(l_sup_level).relative_level := l_hier_psn_tab.LAST - l_sup_level;
1704
1705 -- Set orphan flag
1706 IF (l_hier_sup_tab(l_hier_psn_tab.LAST) = -1) THEN
1707 p_chain_table(l_sup_level).orphan_flag := 'N';
1708 ELSE
1709 p_chain_table(l_sup_level).orphan_flag := 'Y';
1710 END IF;
1711
1712 END LOOP;
1713
1714 END IF;
1715 END IF;
1716
1717 END initialize_previous_chain;
1718
1719
1720 -- ----------------------------------------------------------------------------
1721 -- Samples manager chain for given person between the given dates for the
1722 -- period of service
1723 -- ----------------------------------------------------------------------------
1724 PROCEDURE process_period_of_work(p_person_id IN NUMBER,
1725 p_start_date IN DATE,
1726 p_end_date IN DATE) IS
1727
1728 -- Main loop variable
1729 l_loop_date DATE;
1730 l_next_loop_date DATE;
1731
1732 -- Whether the chain is an orphan
1733 l_orphan_flag VARCHAR2(30);
1734
1735 -- Results of tree walk
1736 l_hier_psn_tab g_number_tab_type;
1737 l_hier_asg_tab g_number_tab_type;
1738 l_hier_bgr_tab g_number_tab_type;
1739 l_hier_sup_tab g_number_tab_type;
1740 l_hier_ast_tab g_number_tab_type;
1741 l_hier_end_tab g_date_tab_type;
1742 l_hier_aty_tab g_varchar2_tab_type;
1743
1744 -- Information about current chain within the hierarchy
1745 l_chain_table g_chain_type;
1746
1747 -- Whether to exit the loop
1748 l_exit_loop VARCHAR2(30);
1749
1750 BEGIN
1751
1752 -- Initialization
1753 l_loop_date := p_start_date;
1754 l_exit_loop := 'N';
1755 g_log_sup_loop := TRUE;
1756 initialize_previous_chain
1757 (p_person_id => p_person_id,
1758 p_effective_date => l_loop_date - 1,
1759 p_chain_table => l_chain_table);
1760
1761 -- Get first supervisor chain for person
1762 get_manager_chain
1763 (p_person_id => p_person_id,
1764 p_effective_date => l_loop_date,
1765 p_hier_psn_tab => l_hier_psn_tab,
1766 p_hier_asg_tab => l_hier_asg_tab,
1767 p_hier_bgr_tab => l_hier_bgr_tab,
1768 p_hier_sup_tab => l_hier_sup_tab,
1769 p_hier_ast_tab => l_hier_ast_tab,
1770 p_hier_end_tab => l_hier_end_tab,
1771 p_hier_aty_tab => l_hier_aty_tab,
1772 p_next_change_date => l_next_loop_date);
1773
1774 -- If no data is found there may be assignment records missing
1775 -- at the start of the period of service. Attempt to re-initialize
1776 -- based on the earliest assignment record
1777 IF (NOT l_hier_psn_tab.EXISTS(1)) THEN
1778
1779 -- Output warning message
1780 output('WARNING: No chain found for person ' || to_char(p_person_id) ||
1781 ' on ' || to_char(l_loop_date, 'DD-MON-YYYY'));
1782
1783 -- Get the earliest assignment record
1784 SELECT MIN(effective_start_date)
1785 INTO l_loop_date
1786 FROM hri_cs_asgn_suph_events_ct
1787 WHERE person_id = p_person_id
1788 AND primary_flag = 'Y';
1789
1790 -- If the earliest assignment record exists and is later than
1791 -- the date already attempted then retry chain initialization
1792 IF (l_loop_date > p_start_date) THEN
1793
1794 -- Get first supervisor chain for person
1795 get_manager_chain
1796 (p_person_id => p_person_id,
1797 p_effective_date => l_loop_date,
1798 p_hier_psn_tab => l_hier_psn_tab,
1799 p_hier_asg_tab => l_hier_asg_tab,
1800 p_hier_bgr_tab => l_hier_bgr_tab,
1801 p_hier_sup_tab => l_hier_sup_tab,
1802 p_hier_ast_tab => l_hier_ast_tab,
1803 p_hier_end_tab => l_hier_end_tab,
1804 p_hier_aty_tab => l_hier_aty_tab,
1805 p_next_change_date => l_next_loop_date);
1806
1807 END IF;
1808
1809 END IF;
1810
1811 -- If still no data is found there is some data issue since
1812 -- this procedure is called with the start date set within
1813 -- an active period of service
1814 IF (NOT l_hier_psn_tab.EXISTS(1)) THEN
1815
1816 -- Output warning message
1817 output('WARNING: No chain found for person ' || to_char(p_person_id) ||
1818 ' on ' || to_char(l_loop_date, 'DD-MON-YYYY'));
1819
1820 ELSE
1821
1822 -- Test for orphan chain
1823 -- Chain is an orphan if the top manager, ordered last, has a
1824 -- not-null supervisor id
1825 IF (l_hier_sup_tab(l_hier_psn_tab.LAST) = -1) THEN
1826 l_orphan_flag := 'N';
1827 ELSE
1828 l_orphan_flag := 'Y';
1829 END IF;
1830
1831 -- Process chain
1832 process_chain
1833 (p_new_psn_tab => l_hier_psn_tab,
1834 p_new_asg_tab => l_hier_asg_tab,
1835 p_new_bgr_tab => l_hier_bgr_tab,
1836 p_new_sup_tab => l_hier_sup_tab,
1837 p_new_ast_tab => l_hier_ast_tab,
1838 p_new_end_tab => l_hier_end_tab,
1839 p_new_aty_tab => l_hier_aty_tab,
1840 p_orphan_flag => l_orphan_flag,
1841 p_chain_table => l_chain_table,
1842 p_loop_date => l_loop_date,
1843 p_next_loop_date => l_next_loop_date);
1844
1845 -- Set new loop date
1846 l_loop_date := l_next_loop_date;
1847
1848 -- Loop through dates to tree walk supervisor hierarchy
1849 -- for the given person and period of work
1850 -- Exit loop when the next date to sample goes beyond the period of
1851 -- work or reaches end of time
1852 WHILE (l_loop_date <= p_end_date AND
1853 l_loop_date < g_end_of_time AND
1854 l_exit_loop = 'N') LOOP
1855
1856 -- Reset local PL/SQL tables with latest manager chain
1857 get_manager_chain
1858 (p_person_id => p_person_id,
1859 p_effective_date => l_loop_date,
1860 p_hier_psn_tab => l_hier_psn_tab,
1861 p_hier_asg_tab => l_hier_asg_tab,
1862 p_hier_bgr_tab => l_hier_bgr_tab,
1863 p_hier_sup_tab => l_hier_sup_tab,
1864 p_hier_ast_tab => l_hier_ast_tab,
1865 p_hier_end_tab => l_hier_end_tab,
1866 p_hier_aty_tab => l_hier_aty_tab,
1867 p_next_change_date => l_next_loop_date);
1868
1869 -- If no data is returned then there is some data problem since
1870 -- the loop date is within an active period of service
1871 -- Print the issue to the log and exit the loop
1872 IF (NOT l_hier_psn_tab.EXISTS(1)) THEN
1873
1874 output('No chain found for person ' || to_char(p_person_id) ||
1875 ' on ' || to_char(l_loop_date, 'DD-MON-YYYY'));
1876
1877 -- Set flag to exit loop
1878 l_exit_loop := 'Y';
1879
1880 ELSE
1881
1882 -- Test for orphan chain
1883 -- Chain is an orphan if the top manager, ordered last, has a
1884 -- not-null supervisor id
1885 IF (l_hier_sup_tab(l_hier_psn_tab.LAST) = -1) THEN
1886 l_orphan_flag := 'N';
1887 ELSE
1888 l_orphan_flag := 'Y';
1889 END IF;
1890
1891 -- Process chain
1892 process_chain
1893 (p_new_psn_tab => l_hier_psn_tab,
1894 p_new_asg_tab => l_hier_asg_tab,
1895 p_new_bgr_tab => l_hier_bgr_tab,
1896 p_new_sup_tab => l_hier_sup_tab,
1897 p_new_ast_tab => l_hier_ast_tab,
1898 p_new_end_tab => l_hier_end_tab,
1899 p_new_aty_tab => l_hier_aty_tab,
1900 p_orphan_flag => l_orphan_flag,
1901 p_chain_table => l_chain_table,
1902 p_loop_date => l_loop_date,
1903 p_next_loop_date => l_next_loop_date);
1904
1905 -- Set new loop date
1906 l_loop_date := l_next_loop_date;
1907
1908 END IF;
1909
1910 END LOOP;
1911
1912 END IF;
1913
1914 END process_period_of_work;
1915
1916 -- ----------------------------------------------------------------------------
1917 -- Calls process_period_of_work with the start and end dates for each active
1918 -- period of service in the collection range
1919 -- ----------------------------------------------------------------------------
1920 PROCEDURE process_person(p_person_id IN NUMBER,
1921 p_refresh_from_date IN DATE) IS
1922
1923 -- Gets all periods of work for a person
1924 CURSOR period_of_work_csr IS
1925 SELECT
1926 GREATEST(pos.date_start,
1927 p_refresh_from_date) start_date
1928 ,LEAST(NVL(pos.actual_termination_date, g_end_of_time),
1929 g_end_of_time) end_date
1930 FROM
1931 per_periods_of_service pos
1932 WHERE pos.person_id = p_person_id
1933 AND (p_refresh_from_date BETWEEN pos.date_start
1934 AND NVL(pos.actual_termination_date, g_end_of_time)
1935 OR pos.date_start > p_refresh_from_date)
1936 UNION ALL
1937 SELECT
1938 GREATEST(pop.date_start,
1939 p_refresh_from_date) start_date
1940 ,LEAST(NVL(pop.actual_termination_date, g_end_of_time),
1941 g_end_of_time) end_date
1942 FROM
1943 per_periods_of_placement pop
1944 WHERE pop.person_id = p_person_id
1945 AND (p_refresh_from_date BETWEEN pop.date_start
1946 AND NVL(pop.actual_termination_date, g_end_of_time)
1947 OR pop.date_start > p_refresh_from_date);
1948
1949 BEGIN
1950
1951 -- Loop through periods of work
1952 FOR pow_rec IN period_of_work_csr LOOP
1953
1954 -- Process the period of work
1955 process_period_of_work
1956 (p_person_id => p_person_id
1957 ,p_start_date => pow_rec.start_date
1958 ,p_end_date => pow_rec.end_date);
1959
1960 END LOOP;
1961
1962 END process_person;
1963
1964 -- ----------------------------------------------------------------------------
1965 -- Main process entry point
1966 -- ----------------------------------------------------------------------------
1967 PROCEDURE process_range(errbuf OUT NOCOPY VARCHAR2,
1968 retcode OUT NOCOPY NUMBER,
1969 p_mthd_action_id IN NUMBER,
1970 p_mthd_range_id IN NUMBER,
1971 p_start_object_id IN NUMBER,
1972 p_end_object_id IN NUMBER) IS
1973
1974 CURSOR person_csr_full IS
1975 SELECT DISTINCT
1976 ase.person_id
1977 FROM
1978 hri_cs_asgn_suph_events_ct ase
1979 WHERE ase.person_id BETWEEN p_start_object_id and p_end_object_id
1980 AND ase.effective_end_date >= g_refresh_start_date;
1981
1982 CURSOR person_csr_incr IS
1983 SELECT DISTINCT
1984 eq.person_id
1985 ,eq.erlst_evnt_effective_date change_date
1986 FROM
1987 hri_eq_sprvsr_hrchy_chgs eq
1988 WHERE eq.person_id BETWEEN p_start_object_id and p_end_object_id;
1989
1990 BEGIN
1991
1992 -- Initialization
1993 g_suph_row_count := 0;
1994 g_chn_row_count := 0;
1995 g_trn_row_count := 0;
1996
1997 -- Set parameter globals
1998 set_parameters(p_mthd_action_id);
1999
2000 -- Full refresh
2001 IF (g_full_refresh = 'Y') THEN
2002
2003 -- Loop through all employees in range
2004 FOR person_rec IN person_csr_full LOOP
2005
2006 -- Process each person from refresh start date
2007 process_person(p_person_id => person_rec.person_id,
2008 p_refresh_from_date => g_refresh_start_date);
2009 END LOOP;
2010
2011 -- Incremental refresh
2012 ELSE
2013
2014 -- Loop through all employees in range
2015 FOR person_rec IN person_csr_incr LOOP
2016
2017 -- Process each person from their earliest change date
2018 process_person(p_person_id => person_rec.person_id,
2019 p_refresh_from_date => person_rec.change_date);
2020 END LOOP;
2021
2022 -- Delete and end date supervisor hierarchy rows
2023 delete_and_end_date_suph_recs
2024 (p_start_person_id => p_start_object_id,
2025 p_end_person_id => p_end_object_id);
2026
2027 -- Remove transfer records for range
2028 hri_opl_wrkfc_trnsfr_events.delete_transfers_mgrh
2029 (p_start_object_id => p_start_object_id,
2030 p_end_object_id => p_end_object_id);
2031
2032 END IF;
2033
2034 -- Insert stored rows
2035 IF g_suph_row_count > 0 THEN
2036 bulk_insert_rows;
2037 END IF;
2038
2039 -- Flush log messages
2040 hri_bpl_conc_log.flush_process_info('HRI_CS_SUPH');
2041
2042 END process_range;
2043
2044 -- ----------------------------------------------------------------------------
2045 -- Pre process entry point
2046 -- ----------------------------------------------------------------------------
2047 PROCEDURE pre_process(p_mthd_action_id IN NUMBER,
2048 p_sqlstr OUT NOCOPY VARCHAR2) IS
2049
2050 l_sql_stmt VARCHAR2(2000);
2051 l_dummy1 VARCHAR2(2000);
2052 l_dummy2 VARCHAR2(2000);
2053 l_schema VARCHAR2(400);
2054
2055 BEGIN
2056
2057 -- Set parameter globals
2058 set_parameters( p_mthd_action_id => p_mthd_action_id );
2059
2060 -- Get HRI schema name - get_app_info populates l_schema
2061 IF fnd_installation.get_app_info('HRI',l_dummy1, l_dummy2, l_schema) THEN
2062 null;
2063 END IF;
2064
2065 -- Disable WHO trigger
2066 run_sql_stmt_noerr('ALTER TRIGGER HRI_CS_SUPH_WHO DISABLE');
2067 run_sql_stmt_noerr('ALTER TRIGGER HRI_CS_MNGRSC_CT_WHO DISABLE');
2068 run_sql_stmt_noerr('ALTER TRIGGER HRI_MDP_MGRH_TRANSFERS_CT_WHO DISABLE');
2069
2070 -- ********************
2071 -- Full Refresh Section
2072 -- ********************
2073 IF (g_full_refresh = 'Y') THEN
2074
2075 -- Drop all the indexes on the table (except the unique index)
2076 hri_utl_ddl.log_and_drop_indexes
2077 (p_application_short_name => 'HRI',
2078 p_table_name => 'HRI_CS_SUPH',
2079 p_table_owner => l_schema,
2080 p_index_excptn_lst => 'HRI_CS_SUPH_U1');
2081 hri_utl_ddl.log_and_drop_indexes
2082 (p_application_short_name => 'HRI',
2083 p_table_name => 'HRI_CS_MNGRSC_CT',
2084 p_table_owner => l_schema,
2085 p_index_excptn_lst => 'HRI_CS_MNGRSC_CT_U1');
2086 hri_utl_ddl.log_and_drop_indexes
2087 (p_application_short_name => 'HRI',
2088 p_table_name => 'HRI_MDP_MGRH_TRANSFERS_CT',
2089 p_table_owner => l_schema,
2090 p_index_excptn_lst => 'HRI_MDP_MGRH_TRANSFERS_CT_U1');
2091
2092 -- Empty out supervisor hierarchy history table
2093 l_sql_stmt := 'TRUNCATE TABLE ' || l_schema || '.HRI_CS_SUPH';
2094 EXECUTE IMMEDIATE(l_sql_stmt);
2095 l_sql_stmt := 'TRUNCATE TABLE ' || l_schema || '.HRI_CS_MNGRSC_CT';
2096 EXECUTE IMMEDIATE(l_sql_stmt);
2097 l_sql_stmt := 'TRUNCATE TABLE ' || l_schema || '.HRI_MDP_MGRH_TRANSFERS_CT';
2098 EXECUTE IMMEDIATE(l_sql_stmt);
2099
2100 -- Insert chain lookup
2101 g_chn_row_count := 0;
2102 insert_chn_row
2103 (p_person_id => -1
2104 ,p_assignment_id => -1
2105 ,p_start_date => hr_general.start_of_time
2106 ,p_end_date => g_end_of_time
2107 ,p_chain_id => -1
2108 ,p_person_level => to_number(null));
2109 bulk_insert_rows;
2110
2111 -- Set the SQL statement for the entire range
2112 p_sqlstr :=
2113 'SELECT DISTINCT person_id object_id
2114 FROM hri_cs_asgn_suph_events_ct
2115 ORDER BY person_id';
2116
2117 -- ***************************
2118 -- Incremental refresh section
2119 -- ***************************
2120 ELSE
2121
2122 -- STEP A - Populate the person_id column in events queue
2123 -- and remove events that are not required
2124 update_event_queue;
2125
2126 -- STEP B - Find Subordinates
2127 find_subordinates;
2128
2129 -- STEP C - Remove Duplicates
2130 remove_duplicates;
2131
2132 -- STEP D - Delete Records After last Change
2133 -- STEP E - End Date Prior Records
2134 -- These steps are done by process_range
2135 -- for each chunk to ensure consistency of
2136 -- table during incremental load
2137
2138 -- 4259598 Incremental Changes
2139 -- Populate the assignment event delta queue in order to incrementally refresh
2140 -- the assignment delta table
2141 populate_asg_delta_eq;
2142 -- Populate workforce events fact event queue
2143 populate_wrkfc_evt_eq;
2144 -- Populate workforce events by manager event queue
2145 populate_wrkfc_evt_mgrh_eq;
2146
2147 -- Set the SQL statement for the entire range
2148 p_sqlstr :=
2149 'SELECT person_id object_id
2150 FROM hri_eq_sprvsr_hrchy_chgs
2151 ORDER BY person_id';
2152
2153 END IF;
2154
2155 END pre_process;
2156
2157 -- ----------------------------------------------------------------------------
2158 -- Post process entry point
2159 -- ----------------------------------------------------------------------------
2160 PROCEDURE post_process(p_mthd_action_id NUMBER) IS
2161
2162 l_dummy1 VARCHAR2(2000);
2163 l_dummy2 VARCHAR2(2000);
2164 l_schema VARCHAR2(400);
2165
2166 BEGIN
2167
2168 -- Check parameters are set
2169 set_parameters(p_mthd_action_id);
2170
2171 -- Get HRI schema
2172 IF fnd_installation.get_app_info('HRI',l_dummy1, l_dummy2, l_schema) THEN
2173 null;
2174 END IF;
2175
2176 -- Enable WHO trigger
2177 run_sql_stmt_noerr('ALTER TRIGGER HRI_CS_SUPH_WHO ENABLE');
2178 run_sql_stmt_noerr('ALTER TRIGGER HRI_CS_MNGRSC_CT_WHO ENABLE');
2179 run_sql_stmt_noerr('ALTER TRIGGER HRI_MDP_MGRH_TRANSFERS_CT_WHO ENABLE');
2180
2181 -- Recreate indexes if they were dropped (full refresh)
2182 IF (g_full_refresh = 'Y') THEN
2183 hri_utl_ddl.recreate_indexes
2184 (p_application_short_name => 'HRI',
2185 p_table_name => 'HRI_CS_SUPH',
2186 p_table_owner => l_schema);
2187 hri_utl_ddl.recreate_indexes
2188 (p_application_short_name => 'HRI',
2189 p_table_name => 'HRI_CS_MNGRSC_CT',
2190 p_table_owner => l_schema);
2191 hri_utl_ddl.recreate_indexes
2192 (p_application_short_name => 'HRI',
2193 p_table_name => 'HRI_MDP_MGRH_TRANSFERS_CT',
2194 p_table_owner => l_schema);
2195 END IF;
2196
2197 -- As the supervisor hierarchy has been rebuilt, purge the events queue
2198 hri_opl_event_capture.purge_queue('HRI_EQ_SPRVSR_HRCHY_CHGS');
2199
2200 IF (p_mthd_action_id > -1) THEN
2201
2202 -- Log process end
2203 hri_bpl_conc_log.record_process_start('HRI_CS_SUPH');
2204 hri_bpl_conc_log.log_process_end(
2205 p_status => TRUE
2206 ,p_period_from => TRUNC(g_refresh_start_date)
2207 ,p_period_to => TRUNC(SYSDATE)
2208 ,p_attribute1 => g_full_refresh);
2209
2210 END IF;
2211
2212 END post_process;
2213
2214 -- --------------------------------------------
2215 -- API to run single thread incremental refresh
2216 -- --------------------------------------------
2217 PROCEDURE incremental_refresh_single IS
2218
2219 l_dummy VARCHAR2(32000);
2220
2221 CURSOR sup_event_queue_csr IS
2222 SELECT
2223 person_id
2224 ,erlst_evnt_effective_date start_date
2225 FROM
2226 hri_eq_sprvsr_hrchy_chgs;
2227
2228 BEGIN
2229
2230 -- Set globals
2231 g_debug := TRUE;
2232 g_full_refresh := 'N';
2233 g_refresh_start_date := trunc(sysdate);
2234
2235 -- Pre process
2236 pre_process(-1, l_dummy);
2237
2238 -- Loop through supervisors in event queue
2239 FOR sup_rec IN sup_event_queue_csr LOOP
2240 process_person(sup_rec.person_id, sup_rec.start_date);
2241 END LOOP;
2242
2243 -- Post process
2244 post_process(-1);
2245
2246 END incremental_refresh_single;
2247
2248 -- --------------------------------------------
2249 -- API to run single thread full refresh
2250 -- --------------------------------------------
2251 PROCEDURE full_refresh_single IS
2252
2253 CURSOR psn_csr IS
2254 SELECT DISTINCT person_id
2255 FROM hri_cs_asgn_suph_events_ct
2256 WHERE primary_flag = 'Y';
2257
2258 l_dummy VARCHAR2(32000);
2259
2260 BEGIN
2261
2262 -- Set globals
2263 g_debug := FALSE;
2264 g_full_refresh := 'Y';
2265 g_refresh_start_date := g_dbi_collection_start_date;
2266
2267 -- Pre process
2268 pre_process(-1, l_dummy);
2269
2270 -- Set number of rows to 0
2271 g_suph_row_count := 0;
2272 g_chn_row_count := 0;
2273 g_trn_row_count := 0;
2274
2275 -- Loop through all employees
2276 FOR psn_rec IN psn_csr LOOP
2277
2278 process_person(psn_rec.person_id, g_dbi_collection_start_date);
2279
2280 -- Insert stored rows
2281 IF g_suph_row_count > 2000 THEN
2282 bulk_insert_rows;
2283 END IF;
2284
2285 END LOOP;
2286
2287 -- Insert stored rows
2288 IF g_suph_row_count > 0 THEN
2289 bulk_insert_rows;
2290 END IF;
2291
2292 -- Post process
2293 post_process(-1);
2294
2295 END full_refresh_single;
2296
2297 -- ---------------------------------------------
2298 -- API to run a full refresh for a single person
2299 -- ---------------------------------------------
2300 PROCEDURE run_for_person(p_person_id IN NUMBER) IS
2301
2302 BEGIN
2303
2304 debug('Start');
2305
2306 DELETE FROM hri_cs_suph
2307 WHERE sub_person_id = p_person_id;
2308
2309 g_suph_row_count := 0;
2310 g_chn_row_count := 0;
2311
2312 process_person(p_person_id, g_dbi_collection_start_date);
2313
2314 -- Insert stored rows
2315 IF g_suph_row_count > 0 THEN
2316 bulk_insert_rows;
2317 END IF;
2318
2319 debug('End');
2320
2321 END run_for_person;
2322
2323 END hri_opl_suph_hst;
[Home] [Help]