1 PACKAGE BODY HRI_STRUCT_SUMMARY AS
2 /* $Header: hribstrc.pkb 115.21 2004/06/17 04:49:35 knarula noship $ */
3
4 --------------------------------------------------------------------------------
5
6 --Package Global Exceptions
7 e_g_no_top_node_found exception;
8 e_g_many_top_nodes_found exception;
9 --Package Global Variables
10 g_debug_flag BOOLEAN := TRUE;
11 -- EDW Default Last Update Date
12 g_default_last_update DATE := to_date('01-01-2000','DD-MM-YYYY');
13 --------------------------------------------------------------------------------
14 -- Package Utilies
15 --
16 --------------------------------------------------------------------------------
17 PROCEDURE output(text1 VARCHAR2
18 ,text2 VARCHAR2 DEFAULT NULL)
19 IS
20
21 l_text VARCHAR2(1000);
22
23 BEGIN
24 /*
25 INSERT INTO HRI.HRI_DEBUG
26 (Text1
27 ,Text2
28 ,time_date)
29 values
30 (Text1
31 ,Text2
32 ,sysdate);
33 COMMIT;
34 */
35 /**/
36 /*
37 CREATE TABLE HRI.HRI_DEBUG
38 (Text1 VARCHAR2(200)
39 ,Text2 VARCHAR2(200)
40 ,time_date DATE)
41 /**/
42
43 -- write to the concurrent request log file.
44 IF g_debug_flag = TRUE THEN
45 IF text2 IS NOT NULL THEN
46 l_text := text1 || ' : ' || text2;
47 ELSE
48 l_text := text1;
49 END IF;
50
51 fnd_file.put_line(FND_FILE.log, l_text);
52 END IF;
53
54 END;
55
56
57 --------------------------------------------------------------------------------
58 --------------------------------------------------------------------------------
59 -- GENERIC HIERARCHY FUNCTIONS
60 --
61 -- TABLE: HRI_GEN_HRCHY_SUMMARY
62 --------------------------------------------------------------------------------
63 --------------------------------------------------------------------------------
64
65 --------------------------------------------------------------------------------
66 -- Function Name: Get_Top_Entity_id
67 --
68 -- Parameters: p_gen_hierarhcy_version_id IN
69 --
70 -- Return: Entity (Number) - if only 1 exists
71 --
72 -- Exceptions: e_g_no_top_node_found - if < 1 orgs found
73 -- e_g_many_top_nodes_found - if > 1 orgs found
74 --
75 -- Description: Returns entity id of top of org structure version if it exists
76 -- otherwise exceptions
77 --
78 ---------------------------------------------------------------------------------
79 FUNCTION Get_Top_Entity_Id
80 (p_gen_hierarhcy_version_id IN NUMBER)
81 RETURN NUMBER
82 IS
83 -- Cursor returns the top entity of a particular entity structure
84 -- version
85 CURSOR csr_top_entity
86 (cp_gen_hierarchy_version_id NUMBER)
87 IS
88
89 SELECT ghn.entity_id top_entity_id
90 FROM per_gen_hierarchy_nodes ghn
91 WHERE ghn.hierarchy_version_id = cp_gen_hierarchy_version_id
92 AND ghn.parent_hierarchy_node_id is null;
93
94 --Return value
95 l_top_entity_id NUMBER;
96 --Local counter
97 l_counter NUMBER;
98
99 BEGIN
100 l_counter := 0;
101 l_top_entity_id := -1;
102
103 FOR l_top_entity IN csr_top_entity
104 (p_gen_hierarhcy_version_id)
105 LOOP
106 l_counter := l_counter + 1;
107 --output( '___Loop No: '|| to_char(l_counter));
108 --output( '___Top Org: '|| to_char(l_top_entity.top_entity_id));
109 l_top_entity_id := l_top_entity.top_entity_id;
110
111 END LOOP;
112
113 IF l_counter < 1 THEN
114 RAISE e_g_no_top_node_found;
115 ELSIF l_counter > 1 THEN
116 RAISE e_g_many_top_nodes_found;
117 END IF;
118
119 RETURN l_top_entity_id;
120
121 END Get_Top_Entity_id;
122
123
124 --------------------------------------------------------------------------------
125 -- Procedure Name: Load_Org_Hierarchies
126 --
127 -- Exceptions: None
128 --------------------------------------------------------------------------------
129 PROCEDURE Load_Org_Hierarchies
130 (p_business_group_id IN NUMBER DEFAULT NULL
131 ,p_primary_hrchy_only IN VARCHAR2 DEFAULT 'Y'
132 ,p_date IN DATE DEFAULT SYSDATE) IS
133
134 BEGIN
135
136 /* Call new organization hierarchy loading function */
137 hri_opl_orgh.load(1500);
138
139 END Load_Org_Hierarchies;
140
141 --------------------------------------------------------------------------------
142 -- Procedure Name: Load_All_Org_Hierarchies
143 --
144 -- Exceptions: None
145 --
146 -- Description: Overloaded version of Load_Org_Hierarchies
147 -- Overloaded to be called directly for debugging
148 --
149 --------------------------------------------------------------------------------
150 PROCEDURE Load_All_Org_Hierarchies
151 IS
152 BEGIN
153
154 /* Call new organization hierarchy loading function */
155 hri_opl_orgh.load(1500);
156
157 END Load_All_Org_Hierarchies;
158
159 --------------------------------------------------------------------------------
160 -- Procedure Name: Load_All_Org_Hierarchies
161 --
162 -- Exceptions: None
163 --
164 -- Description: Overloaded version of Load_Org_Hierarchies
165 -- The purpose of this version is to be called from
166 -- the Concurrent Manager.
167 --
168 --------------------------------------------------------------------------------
169
170 PROCEDURE Load_All_Org_Hierarchies
171 ( errbuf OUT NOCOPY Varchar2
172 , retcode OUT NOCOPY Number )
173 IS
174 BEGIN
175 errbuf := null;
176 retcode := null;
177
178 g_debug_flag := TRUE;
179
180 Load_All_Org_Hierarchies;
181
182 COMMIT;
183
184 EXCEPTION
185 WHEN OTHERS THEN
186 errbuf := sqlerrm;
187 retcode := sqlcode;
188
189 END Load_All_Org_Hierarchies;
190
191 PROCEDURE Load_Gen_Hierarchies
192 (p_business_group_id IN NUMBER DEFAULT NULL)
193
194 IS
195 ---------------------
196 -- Cursor Definitions
197 ---------------------
198
199
200 CURSOR csr_gen_struct_vers
201 (cp_business_group_id NUMBER)
202 IS
203 SELECT ghr.business_group_id
204 , ghr.type
205 , ghr.hierarchy_id
206 , ghv.hierarchy_version_id
207 , ghv.date_from
208 , ghv.date_to
209 , ghv.version_number
210 , ghr.name
211 FROM per_gen_hierarchy ghr
212 , per_gen_hierarchy_versions ghv
213 WHERE ghr.hierarchy_id = ghv.hierarchy_id
214 AND ghr.business_group_id = ghv.business_group_id
215 AND ghr.type = 'FEDREP'; -- bug 2492438
216 -- bug 2834215 performance improvement removed order by
217
218 -- cursor walks down the tree to give all parent entities in a tree
219 -- and the level number where the top entity is 1.
220 CURSOR csr_gen_parents
221 (cp_gen_hierarchy_version_id NUMBER
222 ,cp_top_entity_id per_gen_hierarchy_nodes.entity_id%type ) -- 2492438
223 IS
224 -- cursor walks down the tree to give all parent entities in a tree
225 -- and the level number where the top entity is 1.
226 SELECT ghn.business_group_id business_group_id
227 , ghn.hierarchy_node_id hierarchy_node_id
228 , ghn.entity_id entity_id
229 , LEVEL entity_level
230 , ghn.node_type node_type
231 FROM per_gen_hierarchy_nodes ghn
232 WHERE ghn.hierarchy_version_id = cp_gen_hierarchy_version_id
233 START WITH ghn.entity_id = cp_top_entity_id
234 CONNECT BY ghn.parent_hierarchy_node_id = PRIOR ghn.hierarchy_node_id
235 -- Note: this looks excessive but making sure both
236 -- the prior record and the current record
237 -- are both using the specified org struct version
238 AND PRIOR ghn.hierarchy_version_id = cp_gen_hierarchy_version_id
239 AND ghn.hierarchy_version_id = cp_gen_hierarchy_version_id
240 ;
241
242 -- cursor walks down the tree to give all children in the tree
243 -- and the number of levels away from the top entity
244 CURSOR csr_gen_children
245 (cp_gen_hierarchy_version_id NUMBER
246 ,cp_top_entity_id per_gen_hierarchy_nodes.entity_id%type ) -- 2492438
247 IS
248 SELECT ghn.business_group_id business_group_id
249 , ghn.hierarchy_node_id hierarchy_node_id
250 , ghn.entity_id entity_id
251 , LEVEL -1 entity_level
252 , ghn.node_type node_type
253 FROM per_gen_hierarchy_nodes ghn
254 WHERE ghn.hierarchy_version_id = cp_gen_hierarchy_version_id
255 START WITH ghn.entity_id = cp_top_entity_id
256 CONNECT BY ghn.parent_hierarchy_node_id = PRIOR ghn.hierarchy_node_id
257 -- Note: this looks excessive but making sure both
258 -- the prior record and the current record
259 -- are both using the specified org struct version
260 AND PRIOR ghn.hierarchy_version_id = cp_gen_hierarchy_version_id
261 AND ghn.hierarchy_version_id = cp_gen_hierarchy_version_id
262 ;
263
264 ---------------------
265 -- Local Variables
266 ---------------------
267
268 TYPE t_gen_struct_ver_rec IS RECORD
269 (business_group_id per_gen_hierarchy.business_group_id%TYPE
270 ,hierarchy_id per_gen_hierarchy.hierarchy_id%TYPE
271 ,hierarchy_version_id per_gen_hierarchy_versions.hierarchy_version_id%TYPE
272 ,date_from per_gen_hierarchy_versions.date_from%TYPE
273 ,date_to per_gen_hierarchy_versions.date_to%TYPE
274 );
275
276 l_gen_struct_ver_rec t_gen_struct_ver_rec;
277 l_top_entity NUMBER;
278
279 -- Local vars to allow information to be passed
280 -- outside of the loops which restrict the scope of the records
281 -- Used only to give information at exceptions.
282 l_this_child_entity NUMBER;
283 l_this_parent_entity NUMBER;
284
285 l_sql_stmt varchar2(2000);
286 l_dummy1 VARCHAR2(2000);
287 l_dummy2 VARCHAR2(2000);
288 l_schema VARCHAR2(400);
289
290 BEGIN
291
292 output('Truncate Table HRI_GEN_HRCHY_SUMMARY');
293
294 --Clear the table
295 IF fnd_installation.get_app_info('HRI',l_dummy1, l_dummy2, l_schema) THEN
296 l_sql_stmt := 'TRUNCATE TABLE ' || l_schema || '.HRI_GEN_HRCHY_SUMMARY';
297 EXECUTE IMMEDIATE(l_sql_stmt);
298 END IF;
299
300
301 -- 1. Loop through all Generic Hierarchy Versions
302 FOR l_gen_struct_ver_rec in csr_gen_struct_vers
303 (p_business_group_id)
304 LOOP
305
306 output('Processing hierarchy');
307 output('--------------------');
308 output('Hierarchy Name',l_gen_struct_ver_rec.name);
309 output('Hierarchy Type',l_gen_struct_ver_rec.type);
310 output('Hierarchy Version',l_gen_struct_ver_rec.version_number);
311 output('Start Date',l_gen_struct_ver_rec.date_from);
312 output('End Date',l_gen_struct_ver_rec.date_to);
313
314
315 BEGIN -- Handle Top Entity Query and Parent Entity tree walk exceptions
316 -- Get Top Entity for Generic Structure version
317 l_top_entity := Get_Top_Entity_id (l_gen_struct_ver_rec.hierarchy_version_id);
318
319 -- 2. Loop through all parent entities starting with the top entity
320 -- in the gen struct version
321 FOR l_gen_parent_rec IN csr_gen_parents
322 (l_gen_struct_ver_rec.hierarchy_version_id
323 ,l_top_entity )
324 LOOP
325
326 BEGIN -- Handle Gen Children tree walk exceptions and Insert exceptions
327 -- 3. Loop through and insert a row for each Parnet Child Entity
328 -- combination
329 l_this_parent_entity := l_gen_parent_rec.entity_id;
330
331 FOR l_entity_child_rec IN csr_gen_children
332 (l_gen_struct_ver_rec.hierarchy_version_id
333 ,l_gen_parent_rec.entity_id)
334 LOOP
335 l_this_child_entity := l_gen_parent_rec.entity_id;
336
337 -- bug 2885942, removed if statement to allow parent to be inserted
338 -- below itself in the collection table.
339
340 --Insert into table
341 INSERT INTO
342 HRI_GEN_HRCHY_SUMMARY
343 (hierarchy_id
344 ,hierarchy_version_id
345 ,business_group_id
346 ,hierarchy_node_id
347 ,entity_id
348 ,entity_level
349 ,node_type
350 ,sub_entity_bg_id
351 ,sub_hierarchy_node_id
352 ,sub_entity_id
353 ,sub_entity_level
354 ,sub_node_type
355 /* -- This done by trigger HRI_GEN_HRCHY_SUMMARY_WHO
356 ,created_by
357 ,creation_date
358 ,last_updated_by
359 ,last_update_login
360 ,last_update_date/**/
361 )
362 VALUES
363 (l_gen_struct_ver_rec.hierarchy_id
364 ,l_gen_struct_ver_rec.hierarchy_version_id
365 ,l_gen_parent_rec.business_group_id
366 ,l_gen_parent_rec.hierarchy_node_id
367 ,l_gen_parent_rec.entity_id
368 ,l_gen_parent_rec.entity_level
369 ,l_gen_parent_rec.node_type
370 ,l_entity_child_rec.business_group_id
371 ,l_entity_child_rec.hierarchy_node_id
372 ,l_entity_child_rec.entity_id
373 ,l_gen_parent_rec.entity_level + l_entity_child_rec.entity_level
374 ,l_entity_child_rec.node_type
375 /* -- This done by trigger HRI_GEN_HRCHY_SUMMARY_WHO
376 ,-1 --created_by
377 ,sysdate
378 ,-1 --last_updated_by
379 ,-1 --last_update_login
380 ,sysdate --last_update_date/**/
381 );
382
383 END LOOP; -- 3. Child entity
384
385 EXCEPTION
386 --Exception to handle loops in child cursor
387 WHEN OTHERS THEN
388 output( 'Child or Insert Exception ');
389 output( sqlcode , sqlerrm );
390 output( ' Generic Hierarchy Version ID: ',to_char(l_gen_struct_ver_rec.hierarchy_version_id));
391 output( ' Parent Organization ID: ',l_gen_parent_rec.entity_id); -- 2261412 removed to_char()
392 output( ' Parent Organization ID: ',to_char(l_this_child_entity));
393 NULL;
394 END;
395 /**/
396 END LOOP; -- 2. Parent Entity
397
398 EXCEPTION
399 -- Handles either
400 -- top entity issues
401 -- or loops in parent entity elements data
402 -- Either way need to be able to carry on from an excpetion
403 -- and complete as many generic structures as possible
404 WHEN e_g_no_top_node_found THEN
405 output( '****************************************************************');
406 output( 'No Top Entity was found for hierarchy',l_gen_struct_ver_rec.name);
407 output( 'Hierarchy version not collected',l_gen_struct_ver_rec.version_number);
408 output( 'Check the hierarchy version has a top node and children. ');
409 output( '****************************************************************');
410 WHEN OTHERS THEN
411 output( sqlcode , sqlerrm );
412 output( 'Top or Parent Generic Exception for');
413 output( 'Generic Hierarchy Version ID: ',to_char(l_gen_struct_ver_rec.hierarchy_version_id));
414 output( 'Please check this structure for integrity');
415 END;
416 /**/
417
418
419 output('--------------------');
420
421
422 END LOOP; --1. Loop through all Generic Hierarchy Versions
423
424 EXCEPTION
425 -- Handles excetions at the Generic Struct Version cursor level
426 WHEN OTHERS THEN
427 output( 'Load_Gen_Hierarchies: Gen Struct Version Exception ');
428 output( sqlcode , sqlerrm );
429
430 END Load_Gen_Hierarchies;
431 --------------------------------------------------------------------------------
432 -- Procedure Name: Load_All_Gen_Hierarchies
433 --
434 -- Exceptions: None
435 --
436 -- Description: Overloaded version of Load_Gen_Hierarchies
437 -- Overloaded to be called directly for debugging
438 --
439 --------------------------------------------------------------------------------
440
441 PROCEDURE Load_All_Gen_Hierarchies
442 IS
443 BEGIN
444
445 g_debug_flag := FALSE;
446
447 Load_Gen_Hierarchies
448 (p_business_group_id => NULL -- Do all business groups
449 );
450
451 COMMIT;
452
453 END Load_All_Gen_Hierarchies;
454
455 --------------------------------------------------------------------------------
456 -- Procedure Name: Load_All_Org_Hierarchies
457 --
458 -- Exceptions: None
459 --
460 -- Description: Overloaded version of Load_Gen_Hierarchies
461 -- The purpose of this version is to be called from
462 -- the Concurrent Manager.
463 --
464 --------------------------------------------------------------------------------
465
466 PROCEDURE Load_All_Gen_Hierarchies
467 ( errbuf OUT NOCOPY Varchar2
468 , retcode OUT NOCOPY Number )
469 IS
470 BEGIN
471 errbuf := null;
472 retcode := null;
473
474 g_debug_flag := TRUE;
475
476 Load_Gen_Hierarchies
477 (p_business_group_id => NULL -- Do all business groups
478 );
479
480 COMMIT;
481
482 EXCEPTION
483 WHEN OTHERS THEN
484 errbuf := sqlerrm;
485 retcode := sqlcode;
486
487 END Load_All_Gen_Hierarchies;
488
489
490 --------------------------------------------------------------------------------
491 --------------------------------------------------------------------------------
492 -- SUPERVISOR HIERARCHY FUNCTIONS
493 --
494 -- TABLE: HRI_SUPV_HRCHY_SUMMARY
495 --------------------------------------------------------------------------------
496 --------------------------------------------------------------------------------
497
498 --------------------------------------------------------------------------------
499 -- Procedure Name: Load_Sup_Hierarchies
500 --
501 -- Exceptions: <> - <Description>
502 --
503 -- Description: Populates HRI_SUPV_STRUCT_SUMMARY table with flattened
504 -- supervisor (as of current) structure to speed up rollups and
505 -- Include subordinate queries.
506 -- Currently only primary asg hierarchy is supported so 3rd param
507 -- is not used but is retained to allow future scope and to stop
508 -- need for re-shipping and re-compiling of header and dependents
509 --
510 --------------------------------------------------------------------------------
511
512 /* Bugfix - version 115.3 JTitmas - added conditions assignment_type = 'E' */
513 /* throughout the following cursors to filter out benefits assignments */
514
515 PROCEDURE Load_Sup_Hierarchies
516 ( p_business_group_id IN NUMBER DEFAULT NULL
517 , p_include_supervisor IN BOOLEAN DEFAULT FALSE
518 , p_primary_ass_only IN VARCHAR2 DEFAULT 'Y' --Currently primary Hierarchy only
519 , p_date IN DATE DEFAULT SYSDATE)
520 IS
521
522 --
523 -- Type and Variable holding the EDW latest change date of an assignment and all */
524 -- the assignments above it for each person in the supervisor hiearchy */
525 --
526 TYPE l_sup_updates_tabtype IS TABLE OF DATE INDEX BY BINARY_INTEGER;
527 l_sup_updates_tab l_sup_updates_tabtype;
528 --
529 -- Bug 3658473: Variables for getting application information
530 --
531 l_sql_stmt varchar2(2000);
532 l_dummy1 VARCHAR2(2000);
533 l_dummy2 VARCHAR2(2000);
534 l_schema VARCHAR2(400);
535 --
536 -- select all the people who are supervisors
537 -- are not supervised themselves (all the tops of individual supervisor hierarchies)
538 --
539 CURSOR cur_people
540 (cp_business_group_id NUMBER
541 ,cp_date DATE
542 ,cp_primary_ass_only VARCHAR2)
543 IS
544 SELECT asg.person_id
545 , asg.business_group_id
546 , 0 supervisor_hier_level
547 , NVL(asg.last_update_date, g_default_last_update)
548 last_ptntl_change_date
549 FROM per_all_assignments_f asg
550 WHERE DECODE(cp_primary_ass_only, 'Y', asg.primary_flag, 1)
551 = DECODE(cp_primary_ass_only, 'Y', 'Y', 1) -- primary assignments only
552 AND asg.supervisor_id IS NULL -- not supervised themselves.
553 AND asg.assignment_type = 'E'
554 AND cp_date BETWEEN asg.effective_start_date AND asg.effective_end_date
555 AND asg.person_id IN
556 (SELECT DISTINCT asg.supervisor_id
557 FROM per_all_assignments_f asg
558 WHERE DECODE(cp_primary_ass_only, 'Y', asg.primary_flag, 1)
559 = DECODE(cp_primary_ass_only, 'Y', 'Y', 1) -- primary assignments only
560 AND asg.supervisor_id IS NOT NULL
561 AND asg.assignment_type = 'E'
562 AND cp_date BETWEEN
563 asg.effective_start_date AND asg.effective_end_date
564 ) --
565 AND asg.business_group_id
566 = NVL(cp_business_group_id, asg.business_group_id);
567
568 -- Select the assignments that are below the p_supervisor_id
569 -- in the current supervisor tree starting at p_supervisor_id
570 --
571 -- For the purposes of determining the last update date of a position
572 -- this cursor must return rows on a strictly top down basis. This is
573 -- currently forced by starting at the top and having no order by
574 CURSOR cur_supervisors
575 (cp_supervisor_id NUMBER
576 ,cp_date DATE
577 ,cp_primary_ass_only VARCHAR2)
578 IS
579 SELECT hier.business_group_id
580 , hier.person_id
581 , hier.assignment_id
582 , hier.primary_flag
583 , LEVEL-1 supervisor_level
584 , hier.assignment_id supv_asg_id
585 , NVL(hier.last_update_date, g_default_last_update)
586 last_ptntl_change_date
587 FROM per_all_assignments_f hier
588 WHERE cp_date BETWEEN
589 hier.effective_start_date AND hier.effective_end_date
590 AND hier.assignment_type = 'E'
591 AND DECODE(cp_primary_ass_only, 'Y', hier.primary_flag, 1)
592 = DECODE(cp_primary_ass_only,'Y', 'Y', 1) -- primary assignments only
593 START WITH hier.person_id = cp_supervisor_id
594 CONNECT BY hier.supervisor_id = PRIOR hier.person_id
595 AND hier.assignment_type = 'E'
596 AND cp_date BETWEEN
597 PRIOR hier.effective_start_date
598 AND PRIOR hier.effective_end_date
599 AND DECODE(cp_primary_ass_only, 'Y', hier.primary_flag, 1)
600 = DECODE(cp_primary_ass_only,'Y', 'Y', 1); -- primary assignments only
601
602 -- select the assignments that are below the p_supervisor_id
603 -- in the current supervisor tree starting at p_supervisor_id
604 CURSOR cur_reports
605 (cp_supervisor_id NUMBER
606 ,cp_date DATE
607 ,cp_primary_ass_only VARCHAR2)
608 IS
609 SELECT hier.business_group_id
610 , hier.person_id
611 , hier.assignment_id
612 , hier.primary_flag
613 , LEVEL-1 subordinate_level
614 , NVL(hier.last_update_date, g_default_last_update)
615 last_ptntl_change_date
616 FROM per_all_assignments_f hier
617 WHERE cp_date BETWEEN
618 hier.effective_start_date AND hier.effective_end_date
619 AND hier.assignment_type = 'E'
620 AND DECODE(cp_primary_ass_only, 'Y', hier.primary_flag, 1)
621 = DECODE(cp_primary_ass_only,'Y', 'Y', 1) -- primary assignments only
622 START WITH hier.person_id = cp_supervisor_id
623 CONNECT BY hier.supervisor_id = PRIOR hier.person_id
624 AND hier.assignment_type = 'E'
625 AND cp_date BETWEEN
626 PRIOR hier.effective_start_date AND PRIOR hier.effective_end_date
627 AND DECODE(cp_primary_ass_only, 'Y', hier.primary_flag, 1)
628 = DECODE(cp_primary_ass_only,'Y', 'Y', 1); -- primary assignments only
629 BEGIN
630 --
631 -- empty the hri_supv_struct_summary table from any prvious run.
632 --
633 -- Bug 3658473: Changed delete to truncate
634 --
635 IF fnd_installation.get_app_info('HRI',l_dummy1, l_dummy2, l_schema) THEN
636 --
637 l_sql_stmt := 'TRUNCATE TABLE ' || l_schema || '.HRI_SUPV_HRCHY_SUMMARY';
638 EXECUTE IMMEDIATE(l_sql_stmt);
639 --
640 END IF;
641
642 -- for supervisor hierarchy
643 FOR cur_people_rec IN cur_people(p_business_group_id, p_date, p_primary_ass_only)
644 LOOP
645
646 -- Initialise the table of last update dates
647 l_sup_updates_tab(cur_people_rec.person_id) := cur_people_rec.last_ptntl_change_date;
648
649 -- Traps recursive loops in supervisor hierarchy
650 BEGIN
651
652 -- for each supervisor in the hierarchy
653 FOR cur_supervisors_rec IN cur_supervisors(cur_people_rec.person_id, p_date, p_primary_ass_only )
654 LOOP
655
656 -- for each assignment below cur_supervisors_rec.person_id in the current hierarchy
657 FOR cur_reports_rec IN cur_reports(cur_supervisors_rec.person_id, p_date, p_primary_ass_only)
658 LOOP
659
660 -- Update table of last update dates
661 IF (l_sup_updates_tab(cur_supervisors_rec.person_id) >
662 cur_reports_rec.last_ptntl_change_date) THEN
663 l_sup_updates_tab(cur_reports_rec.person_id) :=
664 l_sup_updates_tab(cur_supervisors_rec.person_id);
665 ELSE
666 l_sup_updates_tab(cur_reports_rec.person_id) :=
667 cur_reports_rec.last_ptntl_change_date;
668 END IF;
669
670 -- include the supervisor in their own rollup?
671 IF (p_include_supervisor = TRUE)
672 OR ((p_include_supervisor = FALSE)
673 AND (cur_reports_rec.person_id <> cur_supervisors_rec.person_id))
674 THEN
675
676 -- insert into hri_supv_struct_summary table
677 BEGIN
678 INSERT INTO hri_supv_hrchy_summary(
679 supv_business_group_id
680 , supv_person_id
681 , supv_assignment_id
682 , supv_level
683 , supv_last_ptntl_change
684 , sub_business_group_id
685 , sub_person_id
686 , sub_assignment_id
687 , sub_primary_asg_flag
688 , sub_level
689 , sub_last_ptntl_change
690 /* -- This done by trigger HRI_SUPV_HRCHY_SUMMARY_WHO
691 , creation_date
692 , created_by
693 , last_update_date
694 , last_updated_by
695 , last_update_login
696 /**/
697 )
698 VALUES(
699 cur_supervisors_rec.business_group_id
700 , cur_supervisors_rec.person_id
701 , cur_supervisors_rec.supv_asg_id
702 , cur_supervisors_rec.supervisor_level
703 , l_sup_updates_tab(cur_supervisors_rec.person_id)
704 , cur_reports_rec.business_group_id
705 , cur_reports_rec.person_id
706 , cur_reports_rec.assignment_id
707 , cur_reports_rec.primary_flag
708 , cur_reports_rec.subordinate_level + cur_supervisors_rec.supervisor_level
709 , l_sup_updates_tab(cur_reports_rec.person_id)
710 /* -- This done by trigger HRI_SUPV_HRCHY_SUMMARY_WHO
711 , SYSDATE
712 , -1
713 , SYSDATE
714 , -1
715 , -1
716 /**/
717 );
718
719 EXCEPTION
720 WHEN OTHERS THEN
721 output(sqlcode, sqlerrm);
722 END;
723
724 END IF;
725
726 END LOOP; -- for each assignment below cur_supervisors_rec.person_id in the current hierarchy
727
728 END LOOP; -- for each assignment below cur_supervisors_rec.person_id in the current hierarchy
729
730 EXCEPTION
731 WHEN OTHERS THEN
732 output(sqlcode, sqlerrm);
733 END;
734
735 END LOOP; -- for each supervisor hierarchy
736
737 EXCEPTION
738 WHEN OTHERS THEN
739 output(sqlcode, sqlerrm);
740
741 END Load_Sup_Hierarchies;
742
743 --------------------------------------------------------------------------------
744 -- Procedure Name: Load_All_Supv_Hierarchies
745 --
746 -- Exceptions: None
747 --
748 -- Description: Calls Load_Supv_Hierarchies directly
749 -- Overloaded to be called directly for debugging
750 --
751 --------------------------------------------------------------------------------
752 PROCEDURE Load_All_Sup_Hierarchies
753 IS
754 BEGIN
755
756 g_debug_flag := FALSE;
757
758 -- will load the supervisor hierarchies for all business groups
759 -- excluding the supervisor from their own supervisor hierarchy
760 -- for primary assignments only
761 Load_Sup_Hierarchies(p_business_group_id => NULL
762 ,p_include_supervisor => FALSE
763 ,p_primary_ass_only => 'Y'
764 ,p_date => SYSDATE);
765
766
767 COMMIT;
768
769 END Load_All_Sup_Hierarchies;
770 --------------------------------------------------------------------------------
771 -- Procedure Name: Load_All_Supv_Hierarchies
772 --
773 -- Exceptions: None
774 --
775 -- Description: Calls Load_Supv_Hierarchies directly
776 -- The purpose of this version is to be called from
777 -- the Concurrent Manager.
778 --
779 --------------------------------------------------------------------------------
780 PROCEDURE Load_All_Sup_Hierarchies
781 ( errbuf OUT NOCOPY Varchar2
782 , retcode OUT NOCOPY Number )
783 IS
784 BEGIN
785 errbuf := null;
786 retcode := null;
787
788 g_debug_flag := TRUE;
789
790 -- will load the supervisor hierarchies for all business groups
791 -- excluding the supervisor from their own supervisor hierarchy
792 -- for primary assignments only
793 Load_Sup_Hierarchies( p_business_group_id => NULL
794 , p_include_supervisor => FALSE
795 , p_primary_ass_only => 'Y'
796 , p_date => SYSDATE);
797
798
799 COMMIT;
800
801 EXCEPTION
802 WHEN OTHERS THEN
803 errbuf := sqlerrm;
804 retcode := sqlcode;
805
806 END Load_All_Sup_Hierarchies;
807
808 END HRI_STRUCT_SUMMARY; -- Package Body HRI_STRUCT_SUMMARY
[Home] [Help]