1 PACKAGE BODY HRI_OPL_SUP_STATUS_HST AS
2 /* $Header: hrioshst.pkb 120.7 2006/12/13 10:34:17 jtitmas noship $ */
3 --
4 -- Information to be held for each link in a chain
5 --
6 g_chunk_size PLS_INTEGER;
7 --
8 -- Stores current time
9 --
10 g_current_time DATE;
11 --
12 -- End of time
13 --
14 g_end_of_time DATE := hr_general.end_of_time;
15 --
16 -- Full or incremental refresh
17 --
18 g_run_mode VARCHAR2(30);
19 --
20 -- ---------------------------------------------------------------------------
21 -- GLOBAL CONSTANTS
22 -- ---------------------------------------------------------------------------
23 --
24 c_OUTPUT_LINE_LENGTH CONSTANT NUMBER := 255;
25 --
26 -- ---------------------------------------------------------------------------
27 -- PRIVATE GLOBALS
28 -- ---------------------------------------------------------------------------
29 --
30 -- Bug 4105868: global to store msg_sub_group
31 --
32 g_msg_sub_group VARCHAR(400) := '';
33 --
34 -- ---------------------------------------------------------------------------
35 -- Procedure msg logs a message, either using fnd_file, or
36 -- hr_utility.trace
37 -- ---------------------------------------------------------------------------
38 --
39 PROCEDURE output(p_text IN VARCHAR2) IS
40 --
41 BEGIN
42 --
43 -- Bug 4105868: Collection Diagnostics
44 --
45 HRI_BPL_CONC_LOG.output(p_text);
46 --
47 END output;
48 --
49 -- ---------------------------------------------------------------------------
50 -- Procedure dbg decides whether to log the passed in message
51 -- depending on whether debug mode is set.
52 -- ---------------------------------------------------------------------------
53 --
54 PROCEDURE dbg(p_text IN VARCHAR2) IS
55 --
56 BEGIN
57 --
58 -- Bug 4105868: Collection Diagnostics
59 --
60 HRI_BPL_CONC_LOG.dbg(p_text);
61 --
62 END dbg;
63 --
64 -- ---------------------------------------------------------------------------
65 -- Runs given sql statement dynamically
66 -- ---------------------------------------------------------------------------
67 --
68 PROCEDURE run_sql_stmt_noerr( p_sql_stmt VARCHAR2 ) IS
69 --
70 BEGIN
71 --
72 dbg('Inside run_sql_stmt_noerr');
73 --
74 EXECUTE IMMEDIATE p_sql_stmt;
75 --
76 dbg('Exiting run_sql_stmt_noerr');
77 --
78 EXCEPTION
79 --
80 WHEN OTHERS THEN
81 --
82 dbg('Error running sql:');
83 --
84 dbg(SUBSTR(p_sql_stmt,1,230));
85 --
86 -- Bug 4105868: Collection Diagnostics
87 --
88 hri_bpl_conc_log.log_process_info
89 (p_msg_type => 'WARNING'
90 ,p_package_name => 'HRI_OPL_SUP_STATUS_HST'
91 ,p_msg_group => 'SUP_STS_HST'
92 ,p_msg_sub_group => 'RUN_SQL_STMT_NOERR'
93 ,p_sql_err_code => SQLCODE
94 ,p_note => SUBSTR(p_sql_stmt, 1, 3900)
95 );
96 --
97 END run_sql_stmt_noerr;
98 --
99 -- ---------------------------------------------------------------------------
100 -- Disables/drops indexes and triggers before process begins
101 -- ---------------------------------------------------------------------------
102 --
103 PROCEDURE disable_objects(p_schema IN VARCHAR2,
104 p_object_name IN VARCHAR2) IS
105 --
106 BEGIN
107 --
108 dbg('Inside disable_objects for ' || p_object_name);
109 --
110 run_sql_stmt_noerr('ALTER TRIGGER ' || p_object_name || '_WHO DISABLE');
111 --
112 -- Disable all the indexes on the table
113 --
114 hri_utl_ddl.log_and_drop_indexes
115 (p_application_short_name => 'HRI'
116 ,p_table_name => p_object_name
117 ,p_table_owner => p_schema
118 );
119 --
120 dbg('Exiting disable_objects');
121 --
122 END disable_objects;
123 --
124 -- ---------------------------------------------------------------------------
125 -- Disables/drops indexes and triggers before process begins
126 -- ---------------------------------------------------------------------------
127 --
128 PROCEDURE enable_objects(p_schema IN VARCHAR2,
129 p_object_name IN VARCHAR2) IS
130 --
131 BEGIN
132 --
133 dbg('Inside enable_objects for ' || p_object_name);
134 --
135 run_sql_stmt_noerr('ALTER TRIGGER ' || p_object_name || '_WHO ENABLE');
136 --
137 -- Recreate indexes
138 --
139 hri_utl_ddl.recreate_indexes
140 (p_application_short_name => 'HRI'
141 ,p_table_name => p_object_name
142 ,p_table_owner => p_schema
143 );
144 --
145 dbg('Inside enable_objects');
146 --
147 END enable_objects;
148 --
149 -- ---------------------------------------------------------------------------
150 -- Collect the supervisor status history data
151 -- ---------------------------------------------------------------------------
152 --
153 PROCEDURE collect_data( p_collect_from IN DATE,
154 p_collect_to IN DATE) IS
155 --
156 l_end_of_time DATE;
157 l_user_id NUMBER;
158 l_current_time DATE;
159 --
160 BEGIN
161 --
162 dbg('Inside collect_data');
163 --
164 -- Initialize variables
165 --
166 l_end_of_time := hr_general.end_of_time;
167 l_user_id := fnd_global.user_id;
168 l_current_time := SYSDATE;
169 --
170 -- This sql statement creates the supervisor status history records, which tells
171 -- whether a person is a supervisor on a particular date or not
172 -- The query works on the basis that a person's supervisory status
173 -- can only change on dates when the his subordinate's assignment records
174 -- are update for following reason
175 --
176 -- 1. A subordinate starts reporting
177 -- 2. A subordinate is transfered out (from next day)
178 -- 3. A subordinates primary assignment changes
179 -- 4. A suboridnate is terminated (from next day)
180 --
181 -- Using the dates on which the above events occur to a person's subordinate
182 -- we can determine if a person is a subordinate or not on that date (there
183 -- won't be a per_system_status record for subordinate on that date)
184 --
185 INSERT /*+ APPEND */
186 INTO HRI_CL_WKR_SUP_STATUS_CT
187 (person_id
188 ,effective_start_date
189 ,effective_end_date
190 ,supervisor_flag
191 ,last_update_date
192 ,last_update_login
193 ,last_updated_by
194 ,created_by
195 ,creation_date)
196 SELECT chgs.person_id,
197 chgs.effective_date effective_start_date,
198 least(nvl((LEAD(chgs.effective_date, 1)
199 OVER (PARTITION BY chgs.person_id
200 ORDER BY chgs.effective_date)) - 1,
201 chgs.termination_date),
202 chgs.termination_date) effective_end_date,
203 decode(chgs.leaf_indicator,1,'N','Y'),
204 l_current_time,
205 l_user_id,
206 l_user_id,
207 l_user_id,
208 l_current_time
209 FROM --
210 -- Calculate supervisory status (leaf node) status for every person on any particular date
211 -- Use an analytic function to get previous leaf node status
212 --
213 (SELECT /*+ USE_HASH(asg ast leaf_date) */
214 leaf_date.event_supervisor_id person_id,
215 leaf_date.effective_date effective_date,
216 NVL(pos.actual_termination_date, l_end_of_time) termination_date,
217 --
218 -- If there is no asg status reporting to a person then he is not a
219 -- supervisor
220 --
221 DECODE(MIN(ast.per_system_status), null, 1, 0) leaf_indicator,
222 --
223 -- The leaf_indicator_prev column returns a person's supervisory status
224 -- on a previous effective date. However when a person has been re-hired
225 -- and if the records are not contiguous. Then two records should be
226 -- created even if his supervisory status is unchanged.
227 -- We don't want his records for duration he was not there
228 -- with the organization
229 --
230 CASE WHEN leaf_date.effective_date - 1 =
231 NVL(LAG(pos.actual_termination_date,1) OVER (PARTITION BY leaf_date.event_supervisor_id
232 ORDER BY leaf_date.effective_date), l_end_of_time)
233 THEN
234 --
235 -- 4099447 When the person is rehired the next day, then two records
236 -- two different records should be created. Return the leaf_indicator_prev
237 -- as null
238 --
239 NULL
240 WHEN leaf_date.effective_date - 1 BETWEEN
241 LAG(leaf_date.effective_date ,1)
242 OVER (PARTITION BY leaf_date.event_supervisor_id
243 ORDER BY leaf_date.effective_date)
244 AND LAG(NVL(pos.actual_termination_date, l_end_of_time),1)
245 OVER (PARTITION BY leaf_date.event_supervisor_id
246 ORDER BY leaf_date.effective_date)
247 THEN
248 --
249 -- records are contiguous. Return the prev_leaf_indicator status
250 --
251 LAG(DECODE(MIN(ast.per_system_status), null, 1, 0),1)
252 OVER (PARTITION BY leaf_date.event_supervisor_id
253 ORDER BY leaf_date.effective_date)
254 ELSE
255 --
256 -- previous records and current record is not contiguous
257 -- return null. So that the present record does not get
258 -- filtered out.
259 --
260 null
261 END leaf_indicator_prev,
262 NVL(pos.actual_termination_date, l_end_of_time)
263 FROM (--
264 -- Using a inline view as oracle doesn't like outer joins with in clause..
265 --
266 SELECT supervisor_id,
267 effective_start_date,
268 effective_end_date,
269 assignment_status_type_id
270 FROM per_all_assignments_f
271 WHERE assignment_type in ('E','C')
272 AND primary_flag = 'Y'
273 ) asg,
274 per_assignment_status_types ast,
275 (select person_id,
276 date_start,
277 actual_termination_date
278 from per_periods_of_service
279 UNION ALL
280 select person_id,
281 date_start,
282 actual_termination_date
283 from per_periods_of_placement
284 )pos,
285 (--
286 -- This gets all supervisors whose subordinates have had events that
287 -- can affects his supervisory status
288 --
289 SELECT CASE WHEN WORKER_TERM_IND = 1 THEN
290 --
291 -- For the termination records get the person's
292 -- previous supervisor_id, as the supervisor_id
293 -- is set to -1
294 --
295 evt.supervisor_prv_id
296 ELSE
297 evt.supervisor_id
298 END event_supervisor_id,
299 evt.effective_change_date effective_date
300 FROM hri_mb_asgn_events_ct evt
301 WHERE (
302 (--
303 -- get only those asg records which have had a
304 -- change in supervisor
305 --
306 supervisor_change_ind = 1
307 --
308 -- or change in the primary assignment
309 --
310 OR primary_flag <> primary_flag_prv
311 --
312 -- or if the event record is a retrospective
313 -- record
314 --
315 OR asg_rtrspctv_strt_event_ind = 1
316 )
317 AND evt.supervisor_id <> -1
318 )
319 AND PRIMARY_FLAG = 'Y'
320 UNION
321 --
322 -- The Previous query will only get the assignment events records
323 -- for Transfer In. But Transfer Out's also affect a person's
324 -- supervisory status. Get all the transfer out events
325 --
326 SELECT evt.supervisor_prv_id event_supervisor_id,
327 evt.effective_change_date effective_date
328 FROM hri_mb_asgn_events_ct evt
329 WHERE (
330 (
331 (supervisor_change_ind = 1
332 OR worker_term_ind = 1
333 )
334 AND primary_flag = 'Y'
335 )
336 OR
337 (evt.primary_flag_prv='Y'
338 AND evt.primary_flag='N'
339 )
340 )
341 AND evt.supervisor_prv_id <> -1
342 UNION
343 --
344 -- Gets all active person's
345 --
346 SELECT pos.person_id,
347 GREATEST(p_collect_from,pos.date_start)
348 FROM per_periods_of_service pos
349 WHERE (p_collect_from BETWEEN pos.date_start AND NVL(pos.actual_termination_date, hr_general.end_of_time)
350 OR p_collect_from <= pos.date_start)
351 --
352 -- Gets all active contingent workers
353 --
354 UNION
355 SELECT pop.person_id,
356 GREATEST(p_collect_from,pop.date_start)
357 FROM per_periods_of_placement pop
358 WHERE (p_collect_from BETWEEN pop.date_start AND NVL(pop.actual_termination_date, hr_general.end_of_time)
359 OR p_collect_from <= pop.date_start)
360 )leaf_date
361 WHERE leaf_date.event_supervisor_id = asg.supervisor_id (+)
362 AND leaf_date.event_supervisor_id = pos.person_id
363 AND leaf_date.effective_date BETWEEN pos.date_start
364 AND NVL(pos.actual_termination_date, l_end_of_time)
365 AND ast.assignment_status_type_id (+) = asg.assignment_status_type_id
366 AND ast.per_system_status (+) <> 'TERM_ASSIGN'
367 AND leaf_date.effective_date BETWEEN asg.effective_start_date (+) AND asg.effective_end_date (+)
368 GROUP BY leaf_date.event_supervisor_id, leaf_date.effective_date, pos.actual_termination_date
369 )chgs
370 WHERE (chgs.leaf_indicator <> NVL(chgs.leaf_indicator_prev, -1));
371 --
372 dbg('Exiting collect_data');
373 --
374 -- Bug 4105868: Collection Diagnostics
375 --
376 -- EXCEPTION
377 --
378 -- WHEN OTHERS THEN
379 --
380 -- g_msg_sub_group := NVL(g_msg_sub_group, 'COLLECT_DATA');
381 --
382 -- RAISE;
383 --
384 --
385 END collect_data;
386 --
387 -- ---------------------------------------------------------------------------
388 -- Collect the supervisor status history data for incremental refresh
389 -- ---------------------------------------------------------------------------
390 --
391 PROCEDURE collect_incremental_data IS
392 --
393 l_end_of_time DATE;
394 l_start_date DATE;
395 l_user_id NUMBER;
396 l_current_time DATE;
397 --
398 BEGIN
399 --
400 dbg('Inside collect_incremental_data');
401 --
402 -- Initialize variables
403 --
404 l_end_of_time := hr_general.end_of_time;
405 l_start_date := hri_bpl_parameter.get_bis_global_start_date;
406 l_user_id := fnd_global.user_id;
407 l_current_time := SYSDATE;
408 --
409 INSERT /*+ APPEND */
410 INTO hri_cl_wkr_sup_status_ct
411 (person_id
412 ,effective_start_date
413 ,effective_end_date
414 ,supervisor_flag
415 ,last_update_date
416 ,last_update_login
417 ,last_updated_by
418 ,created_by
419 ,creation_date
420 )
421 SELECT chgs.person_id,
422 chgs.effective_date effective_start_date,
423 least(nvl((LEAD(chgs.effective_date, 1)
424 OVER (PARTITION BY chgs.person_id
425 ORDER BY chgs.effective_date)) - 1,
426 chgs.termination_date),
427 chgs.termination_date) effective_end_date,
428 decode(chgs.leaf_indicator,1,'N','Y'),
429 l_current_time,
430 l_user_id,
431 l_user_id,
432 l_user_id,
433 l_current_time
434 FROM --
435 -- Calculate supervisory status (leaf node) status for every person on any particular date
436 -- Use an analytic function to get previous leaf node status
437 --
438 (SELECT /*+ USE_HASH(asg ast leaf_date) */
439 leaf_date.event_supervisor_id person_id,
440 leaf_date.effective_date effective_date,
441 NVL(pos.actual_termination_date, l_end_of_time) termination_date,
442 --
443 -- If there is no asg status reporting to a person then he is not a
444 -- supervisor
445 --
446 DECODE(MIN(ast.per_system_status), null, 1, 0) leaf_indicator,
447 --
448 -- The leaf_indicator_prev column returns a person's supervisory status
449 -- on a previous effective date. However when a person has been re-hired
450 -- and if the records are not contiguous. Then two records should be
451 -- created even if his supervisory status is unchanged.
452 -- We don't want his records for duration he was not there
453 -- with the organization
454 --
455 CASE WHEN leaf_date.effective_date - 1 =
456 NVL(LAG(pos.actual_termination_date,1) OVER (PARTITION BY leaf_date.event_supervisor_id
457 ORDER BY leaf_date.effective_date), l_end_of_time)
458 THEN
459 --
460 -- 4099447 When the person is rehired the next day, then two records
461 -- two different records should be created. Return the leaf_indicator_prev
462 -- as null
463 --
464 NULL
465 WHEN leaf_date.effective_date - 1 BETWEEN
466 LAG(leaf_date.effective_date ,1)
467 OVER (PARTITION BY leaf_date.event_supervisor_id
468 ORDER BY leaf_date.effective_date)
469 AND LAG(NVL(pos.actual_termination_date, l_end_of_time),1)
470 OVER (PARTITION BY leaf_date.event_supervisor_id
471 ORDER BY leaf_date.effective_date)
472 THEN
473 --
474 -- records are contiguous. Return the prev_leaf_indicator status
475 --
476 LAG(DECODE(MIN(ast.per_system_status), null, 1, 0),1)
477 OVER (PARTITION BY leaf_date.event_supervisor_id
478 ORDER BY leaf_date.effective_date)
479 ELSE
480 --
481 -- previous records and current record is not contiguous
482 -- return null. So that the present record does not get
483 -- filtered out.
484 --
485 null
486 END leaf_indicator_prev,
487 NVL(pos.actual_termination_date, l_end_of_time)
488 FROM (--
489 -- Using a inline view as oracle doesn't like outer joins with in clause..
490 --
491 SELECT supervisor_id,
492 effective_start_date,
493 effective_end_date,
494 assignment_status_type_id
495 FROM per_all_assignments_f
496 WHERE assignment_type in ('E','C')
497 AND primary_flag = 'Y'
498 ) asg,
499 per_assignment_status_types ast,
500 --
501 -- CWK Change
502 --
503 (select pos.person_id,
504 pos.date_start,
505 pos.actual_termination_date
506 from per_periods_of_service pos,
507 hri_eq_sprvsr_hstry_chgs eq
508 where eq.person_id=pos.person_id
509 UNION ALL
510 select pop.person_id,
511 pop.date_start,
512 pop.actual_termination_date
513 from per_periods_of_placement pop,
514 hri_eq_sprvsr_hstry_chgs eq
515 WHERE eq.person_id=pop.person_id
516 )pos,
517 (--
518 -- This gets all supervisors whose subordinates have had events that
519 -- can affects his supervisory status
520 --
521 SELECT CASE WHEN WORKER_TERM_IND = 1 THEN
522 --
523 -- For the termination records get the person's
524 -- previous supervisor_id, as the supervisor_id
525 -- is set to -1
526 --
527 evt.supervisor_prv_id
528 ELSE
529 evt.supervisor_id
530 END event_supervisor_id,
531 evt.effective_change_date effective_date
532 FROM hri_mb_asgn_events_ct evt,
533 hri_eq_sprvsr_hstry_chgs eq
534 WHERE (
535 (--
536 -- get only those asg records which have had a
537 -- change in supervisor
538 --
539 supervisor_change_ind = 1
540 --
541 -- or change in the primary assignment
542 --
543 OR primary_flag <> primary_flag_prv
544 --
545 -- or if the event record is a retrospective
546 -- record
547 --
548 OR asg_rtrspctv_strt_event_ind = 1
549 )
550 AND evt.supervisor_id <> -1
551 )
552 AND PRIMARY_FLAG = 'Y'
553 AND eq.person_id=evt.supervisor_id
554 UNION
555 --
556 -- The Previous query will only get the assignment events records
557 -- for Transfer In. But Transfer Out's also affect a person's
558 -- supervisory status. Get all the transfer out events
559 --
560 SELECT evt.supervisor_prv_id event_supervisor_id,
561 evt.effective_change_date effective_date
562 FROM hri_mb_asgn_events_ct evt,
563 hri_eq_sprvsr_hstry_chgs eq
564 WHERE (
565 (
566 (supervisor_change_ind = 1
567 OR worker_term_ind = 1
568 )
569 AND primary_flag = 'Y'
570 )
571 OR
572 (evt.primary_flag_prv='Y'
573 AND evt.primary_flag='N'
574 )
575 )
576 AND evt.supervisor_prv_id <> -1
577 AND eq.person_id = evt.supervisor_prv_id
578 UNION
579 --
580 -- Gets all active person's
581 --
582 SELECT pos.person_id,
583 GREATEST(hri_bpl_parameter.get_bis_global_start_date
584 ,pos.date_start)
585 FROM per_periods_of_service pos,
586 hri_eq_sprvsr_hstry_chgs eq
587 WHERE eq.person_id=pos.person_id
588 UNION
589 --
590 -- Gets all active placements
591 --
592 SELECT pop.person_id,
593 GREATEST(hri_bpl_parameter.get_bis_global_start_date
594 ,pop.date_start)
595 FROM per_periods_of_placement pop,
596 hri_eq_sprvsr_hstry_chgs eq
597 WHERE eq.person_id=pop.person_id
598 ) leaf_date
599 WHERE leaf_date.event_supervisor_id = asg.supervisor_id (+)
600 AND leaf_date.event_supervisor_id = pos.person_id
601 AND leaf_date.effective_date BETWEEN pos.date_start
602 AND NVL(pos.actual_termination_date, l_end_of_time)
603 AND ast.assignment_status_type_id (+) = asg.assignment_status_type_id
604 AND ast.per_system_status (+) <> 'TERM_ASSIGN'
605 AND leaf_date.effective_date BETWEEN asg.effective_start_date (+) AND asg.effective_end_date (+)
606 GROUP BY leaf_date.event_supervisor_id, leaf_date.effective_date, pos.actual_termination_date
607 )chgs
608 WHERE (chgs.leaf_indicator <> NVL(chgs.leaf_indicator_prev, -1));
609 --
610 dbg('Exiting collect_incremental_data');
611 --
612 -- Bug 4105868: Collection Diagnostics
613 --
614 EXCEPTION
615 --
616 WHEN OTHERS THEN
617 --
618 g_msg_sub_group := NVL(g_msg_sub_group, 'COLLECT_INCREMENTAL_DATA');
619 --
620 RAISE;
621 --
622 --
623 end collect_incremental_data;
624 --
625 -- ---------------------------------------------------------------------------
626 -- Collect the supervisor status history data
627 -- ---------------------------------------------------------------------------
628 --
629 PROCEDURE collect_asg_data( p_collect_from IN DATE,
630 p_collect_to IN DATE) IS
631 --
632 l_end_of_time DATE;
633 l_user_id NUMBER;
634 l_current_time DATE;
635 --
636 BEGIN
637 --
638 dbg('Inside collect_asg_data');
639 --
640 -- Initialize variables
641 --
642 l_end_of_time := hr_general.end_of_time;
643 l_user_id := fnd_global.user_id;
644 l_current_time := SYSDATE;
645 --
646 -- This sql statement creates the supervisor status history records, which tells
647 -- whether a person is a supervisor on a particular date or not
648 -- The query works on the basis that a person's supervisory status
649 -- can only change on dates when the his subordinate's assignment records
650 -- are update for following reason
651 --
652 -- 1. A subordinate starts reporting
653 -- 2. A subordinate is transfered out (from next day)
654 -- 3. A subordinates primary assignment changes
655 -- 4. A suboridnate is terminated (from next day)
656 --
657 -- Using the dates on which the above events occur to a person's subordinate
658 -- we can determine if a person is a subordinate or not on that date (there
659 -- won't be a per_system_status record for subordinate on that date)
660 --
661 INSERT /*+ APPEND */
662 INTO HRI_CL_WKR_SUP_STATUS_ASG_CT
663 (person_id
664 ,effective_start_date
665 ,effective_end_date
666 ,supervisor_flag
667 ,last_update_date
668 ,last_update_login
669 ,last_updated_by
670 ,created_by
671 ,creation_date)
672 SELECT chgs.person_id,
673 chgs.effective_date effective_start_date,
674 least(nvl((LEAD(chgs.effective_date, 1)
675 OVER (PARTITION BY chgs.person_id
676 ORDER BY chgs.effective_date)) - 1,
677 chgs.termination_date),
678 chgs.termination_date) effective_end_date,
679 decode(chgs.leaf_indicator,1,'N','Y'),
680 l_current_time,
681 l_user_id,
682 l_user_id,
683 l_user_id,
684 l_current_time
685 FROM --
686 -- Calculate supervisory status (leaf node) status for every person on any particular date
687 -- Use an analytic function to get previous leaf node status
688 --
689 (SELECT /*+ USE_HASH(asg ast leaf_date) */
690 leaf_date.event_supervisor_id person_id,
691 leaf_date.effective_date effective_date,
692 NVL(pos.actual_termination_date, l_end_of_time) termination_date,
693 --
694 -- If there is no asg status reporting to a person then he is not a
695 -- supervisor
696 --
697 DECODE(MIN(ast.per_system_status), null, 1, 0) leaf_indicator,
698 --
699 -- The leaf_indicator_prev column returns a person's supervisory status
700 -- on a previous effective date. However when a person has been re-hired
701 -- and if the records are not contiguous. Then two records should be
702 -- created even if his supervisory status is unchanged.
703 -- We don't want his records for duration he was not there
704 -- with the organization
705 --
706 CASE WHEN leaf_date.effective_date - 1 =
707 NVL(LAG(pos.actual_termination_date,1) OVER (PARTITION BY leaf_date.event_supervisor_id
708 ORDER BY leaf_date.effective_date), l_end_of_time)
709 THEN
710 --
711 -- 4099447 When the person is rehired the next day, then two records
712 -- two different records should be created. Return the leaf_indicator_prev
713 -- as null
714 --
715 NULL
716 WHEN leaf_date.effective_date - 1 BETWEEN
717 LAG(leaf_date.effective_date ,1)
718 OVER (PARTITION BY leaf_date.event_supervisor_id
719 ORDER BY leaf_date.effective_date)
720 AND LAG(NVL(pos.actual_termination_date, l_end_of_time),1)
721 OVER (PARTITION BY leaf_date.event_supervisor_id
722 ORDER BY leaf_date.effective_date)
723 THEN
724 --
725 -- records are contiguous. Return the prev_leaf_indicator status
726 --
727 LAG(DECODE(MIN(ast.per_system_status), null, 1, 0),1)
728 OVER (PARTITION BY leaf_date.event_supervisor_id
729 ORDER BY leaf_date.effective_date)
730 ELSE
731 --
732 -- previous records and current record is not contiguous
733 -- return null. So that the present record does not get
734 -- filtered out.
735 --
736 null
737 END leaf_indicator_prev,
738 NVL(pos.actual_termination_date, l_end_of_time)
739 FROM (--
740 -- Using a inline view as oracle doesn't like outer joins with in clause..
741 --
742 SELECT supervisor_id,
743 effective_start_date,
744 effective_end_date,
745 assignment_status_type_id
746 FROM per_all_assignments_f
747 WHERE assignment_type in ('E','C')
748 ) asg,
749 per_assignment_status_types ast,
750 (select person_id,
751 date_start,
752 actual_termination_date
753 from per_periods_of_service
754 UNION ALL
755 select person_id,
756 date_start,
757 actual_termination_date
758 from per_periods_of_placement
759 )pos,
760 (--
761 -- This gets all supervisors whose subordinates have had events that
762 -- can affects his supervisory status
763 --
764 SELECT evt.supervisor_id event_supervisor_id,
765 evt.effective_change_date effective_date
766 FROM hri_mb_asgn_events_ct evt
767 WHERE (worker_hire_ind = 1
768 OR post_hire_asgn_start_ind = 1
769 OR supervisor_change_ind = 1
770 OR asg_rtrspctv_strt_event_ind = 1)
771 AND evt.supervisor_id <> -1
772 UNION
773 --
774 -- The Previous query will only get the assignment events records
775 -- for Transfer In. But Transfer Out's also affect a person's
776 -- supervisory status. Get all the transfer out events
777 --
778 SELECT evt.supervisor_prv_id event_supervisor_id,
779 evt.effective_change_date effective_date
780 FROM hri_mb_asgn_events_ct evt
781 WHERE (supervisor_change_ind = 1
782 OR worker_term_ind = 1
783 OR pre_sprtn_asgn_end_ind = 1)
784 AND evt.supervisor_prv_id <> -1
785 UNION
786 --
787 -- Gets all active employees
788 --
789 SELECT pos.person_id, GREATEST(p_collect_from,pos.date_start)
790 FROM per_periods_of_service pos
791 WHERE (p_collect_from BETWEEN pos.date_start
792 AND NVL(pos.actual_termination_date, hr_general.end_of_time)
793 OR p_collect_from <= pos.date_start)
794 --
795 -- Gets all active contingent workers
796 --
797 UNION
798 SELECT pop.person_id, GREATEST(p_collect_from,pop.date_start)
799 FROM per_periods_of_placement pop
800 WHERE (p_collect_from BETWEEN pop.date_start
801 AND NVL(pop.actual_termination_date, hr_general.end_of_time)
802 OR p_collect_from <= pop.date_start)
803 )leaf_date
804 WHERE leaf_date.event_supervisor_id = asg.supervisor_id (+)
805 AND leaf_date.event_supervisor_id = pos.person_id
806 AND leaf_date.effective_date BETWEEN pos.date_start
807 AND NVL(pos.actual_termination_date, l_end_of_time)
808 AND ast.assignment_status_type_id (+) = asg.assignment_status_type_id
809 AND ast.per_system_status (+) <> 'TERM_ASSIGN'
810 AND leaf_date.effective_date BETWEEN asg.effective_start_date (+)
811 AND asg.effective_end_date (+)
812 GROUP BY
813 leaf_date.event_supervisor_id
814 ,leaf_date.effective_date
815 ,pos.actual_termination_date
816 ) chgs
817 WHERE (chgs.leaf_indicator <> NVL(chgs.leaf_indicator_prev, -1));
818 --
819 dbg('Exiting collect_asg_data');
820 --
821 -- Bug 4105868: Collection Diagnostics
822 --
823 EXCEPTION
824 --
825 WHEN OTHERS THEN
826 --
827 g_msg_sub_group := NVL(g_msg_sub_group, 'COLLECT_DATA');
828 --
829 RAISE;
830 --
831 --
832 END collect_asg_data;
833 --
834 -- ---------------------------------------------------------------------------
835 -- Collect the supervisor status history data for incremental refresh
836 -- ---------------------------------------------------------------------------
837 --
838 PROCEDURE collect_asg_incremental_data IS
839 --
840 l_end_of_time DATE;
841 l_start_date DATE;
842 l_user_id NUMBER;
843 l_current_time DATE;
844 --
845 BEGIN
846 --
847 dbg('Inside collect_asg_incremental_data');
848 --
849 -- Initialize variables
850 --
851 l_end_of_time := hr_general.end_of_time;
852 l_start_date := hri_bpl_parameter.get_bis_global_start_date;
853 l_user_id := fnd_global.user_id;
854 l_current_time := SYSDATE;
855 --
856 INSERT /*+ APPEND */
857 INTO hri_cl_wkr_sup_status_asg_ct
858 (person_id
859 ,effective_start_date
860 ,effective_end_date
861 ,supervisor_flag
862 ,last_update_date
863 ,last_update_login
864 ,last_updated_by
865 ,created_by
866 ,creation_date
867 )
868 SELECT chgs.person_id,
869 chgs.effective_date effective_start_date,
870 least(nvl((LEAD(chgs.effective_date, 1)
871 OVER (PARTITION BY chgs.person_id
872 ORDER BY chgs.effective_date)) - 1,
873 chgs.termination_date),
874 chgs.termination_date) effective_end_date,
875 decode(chgs.leaf_indicator,1,'N','Y'),
876 l_current_time,
877 l_user_id,
878 l_user_id,
879 l_user_id,
880 l_current_time
881 FROM --
882 -- Calculate supervisory status (leaf node) status for every person on any particular date
883 -- Use an analytic function to get previous leaf node status
884 --
885 (SELECT /*+ USE_HASH(asg ast leaf_date) */
886 leaf_date.event_supervisor_id person_id,
887 leaf_date.effective_date effective_date,
888 NVL(pos.actual_termination_date, l_end_of_time) termination_date,
889 --
890 -- If there is no asg status reporting to a person then he is not a
891 -- supervisor
892 --
893 DECODE(MIN(ast.per_system_status), null, 1, 0) leaf_indicator,
894 --
895 -- The leaf_indicator_prev column returns a person's supervisory status
896 -- on a previous effective date. However when a person has been re-hired
897 -- and if the records are not contiguous. Then two records should be
898 -- created even if his supervisory status is unchanged.
899 -- We don't want his records for duration he was not there
900 -- with the organization
901 --
902 CASE WHEN leaf_date.effective_date - 1 =
903 NVL(LAG(pos.actual_termination_date,1) OVER (PARTITION BY leaf_date.event_supervisor_id
904 ORDER BY leaf_date.effective_date), l_end_of_time)
905 THEN
906 --
907 -- 4099447 When the person is rehired the next day, then two records
908 -- two different records should be created. Return the leaf_indicator_prev
909 -- as null
910 --
911 NULL
912 WHEN leaf_date.effective_date - 1 BETWEEN
913 LAG(leaf_date.effective_date ,1)
914 OVER (PARTITION BY leaf_date.event_supervisor_id
915 ORDER BY leaf_date.effective_date)
916 AND LAG(NVL(pos.actual_termination_date, l_end_of_time),1)
917 OVER (PARTITION BY leaf_date.event_supervisor_id
918 ORDER BY leaf_date.effective_date)
919 THEN
920 --
921 -- records are contiguous. Return the prev_leaf_indicator status
922 --
923 LAG(DECODE(MIN(ast.per_system_status), null, 1, 0),1)
924 OVER (PARTITION BY leaf_date.event_supervisor_id
925 ORDER BY leaf_date.effective_date)
926 ELSE
927 --
928 -- previous records and current record is not contiguous
929 -- return null. So that the present record does not get
930 -- filtered out.
931 --
932 null
933 END leaf_indicator_prev,
934 NVL(pos.actual_termination_date, l_end_of_time)
935 FROM (--
936 -- Using a inline view as oracle doesn't like outer joins with in clause..
937 --
938 SELECT supervisor_id,
939 effective_start_date,
940 effective_end_date,
941 assignment_status_type_id
942 FROM per_all_assignments_f
943 WHERE assignment_type in ('E','C')
944 ) asg,
945 per_assignment_status_types ast,
946 --
947 -- CWK Change
948 --
949 (select pos.person_id,
950 pos.date_start,
951 pos.actual_termination_date
952 from per_periods_of_service pos,
953 hri_eq_sprvsr_hstry_chgs eq
954 where eq.person_id=pos.person_id
955 UNION ALL
956 select pop.person_id,
957 pop.date_start,
958 pop.actual_termination_date
959 from per_periods_of_placement pop,
960 hri_eq_sprvsr_hstry_chgs eq
961 WHERE eq.person_id=pop.person_id
962 )pos,
963 (--
964 -- This gets all supervisors whose subordinates have had events that
965 -- can affects his supervisory status
966 --
967 SELECT evt.supervisor_id event_supervisor_id,
968 evt.effective_change_date effective_date
969 FROM hri_mb_asgn_events_ct evt,
970 hri_eq_sprvsr_hstry_chgs eq
971 WHERE (worker_hire_ind = 1
972 OR post_hire_asgn_start_ind = 1
973 OR supervisor_change_ind = 1
974 OR asg_rtrspctv_strt_event_ind = 1)
975 AND evt.supervisor_id <> -1
976 AND eq.person_id=evt.supervisor_id
977 UNION
978 --
979 -- The Previous query will only get the assignment events records
980 -- for Transfer In. But Transfer Out's also affect a person's
981 -- supervisory status. Get all the transfer out events
982 --
983 SELECT evt.supervisor_prv_id event_supervisor_id,
984 evt.effective_change_date effective_date
985 FROM hri_mb_asgn_events_ct evt,
986 hri_eq_sprvsr_hstry_chgs eq
987 WHERE (supervisor_change_ind = 1
988 OR worker_term_ind = 1
989 OR pre_sprtn_asgn_end_ind = 1)
990 AND evt.supervisor_prv_id <> -1
991 AND eq.person_id = evt.supervisor_prv_id
992 UNION
993 --
994 -- Gets all active person's
995 --
996 SELECT pos.person_id,
997 GREATEST(hri_bpl_parameter.get_bis_global_start_date
998 ,pos.date_start)
999 FROM per_periods_of_service pos,
1000 hri_eq_sprvsr_hstry_chgs eq
1001 WHERE eq.person_id=pos.person_id
1002 UNION
1003 --
1004 -- Gets all active placements
1005 --
1006 SELECT pop.person_id,
1007 GREATEST(hri_bpl_parameter.get_bis_global_start_date
1008 ,pop.date_start)
1009 FROM per_periods_of_placement pop,
1010 hri_eq_sprvsr_hstry_chgs eq
1011 WHERE eq.person_id=pop.person_id
1012 ) leaf_date
1013 WHERE leaf_date.event_supervisor_id = asg.supervisor_id (+)
1014 AND leaf_date.event_supervisor_id = pos.person_id
1015 AND leaf_date.effective_date BETWEEN pos.date_start
1016 AND NVL(pos.actual_termination_date, l_end_of_time)
1017 AND ast.assignment_status_type_id (+) = asg.assignment_status_type_id
1018 AND ast.per_system_status (+) <> 'TERM_ASSIGN'
1019 AND leaf_date.effective_date BETWEEN asg.effective_start_date (+)
1020 AND asg.effective_end_date (+)
1021 GROUP BY
1022 leaf_date.event_supervisor_id
1023 ,leaf_date.effective_date
1024 ,pos.actual_termination_date
1025 ) chgs
1026 WHERE (chgs.leaf_indicator <> NVL(chgs.leaf_indicator_prev, -1));
1027 --
1028 dbg('Exiting collect_asg_incremental_data');
1029 --
1030 -- Bug 4105868: Collection Diagnostics
1031 --
1032 EXCEPTION
1033 --
1034 WHEN OTHERS THEN
1035 --
1036 g_msg_sub_group := NVL(g_msg_sub_group, 'COLLECT_INCREMENTAL_DATA');
1037 --
1038 RAISE;
1039 --
1040 --
1041 end collect_asg_incremental_data;
1042 --
1043 -- ---------------------------------------------------------------------------
1044 -- This procedure populates the person_id column in hri_eq_sprvsr_hstry_chgs
1045 -- by using the value of assignment_id
1046 -- ---------------------------------------------------------------------------
1047 --
1048 PROCEDURE update_event_queue IS
1049 --
1050 BEGIN
1051 --
1052 dbg('Inside update_event_queue');
1053 --
1054 UPDATE hri_eq_sprvsr_hstry_chgs eq
1055 SET person_id = (SELECT person_id
1056 FROM per_all_assignments_f asg
1057 WHERE eq.assignment_id=asg.assignment_id
1058 AND rownum=1
1059 );
1060 --
1061 dbg('Exiting update_event_queue');
1062 --
1063 --
1064 EXCEPTION
1065 --
1066 WHEN OTHERS THEN
1067 --
1068 dbg('An error occured while updating events queue records.');
1069 output(sqlerrm);
1070 --
1071 -- Bug 4105868: Collection Diagnostics
1072 --
1073 g_msg_sub_group := NVL(g_msg_sub_group, 'UPDATE_EVENT_QUEUE');
1074 --
1075 RAISE;
1076 --
1077 --
1078 END update_event_queue;
1079 --
1080 -- ---------------------------------------------------------------------------
1081 -- When an assignment event occurs the following need to be processed
1082 -- as they can potentially have a changed status
1083 -- A - any previously connected supervisor of the assignment from
1084 -- the event date forwards
1085 -- (e.g. assignment event is last subordinate transferring out)
1086 -- B - any connected supervisor of the assignment from the event
1087 -- date forwards
1088 -- (e.g. assignment event is a new hire for a first time supervisor)
1089 -- C - the assignment person, if the event is a start, end or purge
1090 -- (to create, end date or purge the person's status)
1091 -- ---------------------------------------------------------------------------
1092 --
1093 PROCEDURE find_changed_supervisors IS
1094 --
1095 BEGIN
1096 --
1097 dbg('Finding changed supervisors');
1098 --
1099 dbg('Calling update_event_queue');
1100 --
1101 update_event_queue;
1102 --
1103 dbg('Case A');
1104 --
1105 -- Insert previous supervisors
1106 -- NOTE - THIS MUST BE CALLED BEFORE hri_mb_asgn_events_ct IS REFRESHED
1107 --
1108 INSERT /*+ APPEND */ INTO hri_eq_sprvsr_hstry_chgs
1109 (person_id
1110 ,erlst_evnt_effective_date
1111 ,source_code)
1112 SELECT DISTINCT
1113 evt.supervisor_id
1114 ,eq.erlst_evnt_effective_date
1115 ,'DERIVED'
1116 FROM
1117 hri_eq_sprvsr_hstry_chgs eq
1118 ,hri_mb_asgn_events_ct evt
1119 WHERE eq.assignment_id = evt.assignment_id
1120 AND evt.effective_change_end_date >= eq.erlst_evnt_effective_date
1121 AND eq.source_code IS NULL
1122 AND NOT EXISTS
1123 (SELECT null
1124 FROM hri_eq_sprvsr_hstry_chgs eq2
1125 WHERE eq2.person_id = evt.supervisor_id);
1126 --
1127 dbg(sql%rowcount||' old supervisors found and added to the change list.');
1128 --
1129 dbg(' ');
1130 dbg('Case B');
1131 --
1132 -- Insert current supervisors
1133 --
1134 INSERT /*+ APPEND */ INTO hri_eq_sprvsr_hstry_chgs
1135 (person_id
1136 ,erlst_evnt_effective_date
1137 ,source_code)
1138 SELECT DISTINCT
1139 asg.supervisor_id
1140 ,eq.erlst_evnt_effective_date
1141 ,'DERIVED'
1142 FROM
1143 hri_eq_sprvsr_hstry_chgs eq
1144 ,per_all_assignments_f asg
1145 WHERE eq.assignment_id = asg.assignment_id
1146 AND asg.effective_start_date >= eq.erlst_evnt_effective_date
1147 AND eq.source_code IS NULL
1148 AND NOT EXISTS
1149 (SELECT null
1150 FROM hri_eq_sprvsr_hstry_chgs eq2
1151 WHERE eq2.person_id = asg.supervisor_id);
1152 --
1153 dbg(sql%rowcount||' new supervisors found and added to the change list.');
1154 --
1155 dbg(' ');
1156 dbg('Case C');
1157 --
1158 -- Delete original records where there is no hire or termination
1159 -- after the event date
1160 --
1161 DELETE FROM hri_eq_sprvsr_hstry_chgs eq
1162 WHERE eq.source_code IS NULL
1163 AND eq.person_id IN
1164 (SELECT
1165 pps.person_id
1166 FROM
1167 hri_eq_sprvsr_hstry_chgs eq2
1168 ,per_periods_of_service pps
1169 WHERE eq2.person_id = pps.person_id
1170 AND pps.date_start <> eq2.erlst_evnt_effective_date
1171 AND pps.actual_termination_date IS NULL);
1172 --
1173 dbg(sql%rowcount||' redundant employee assignment changes removed');
1174 --
1175 DELETE FROM hri_eq_sprvsr_hstry_chgs eq
1176 WHERE eq.source_code IS NULL
1177 AND eq.person_id IN
1178 (SELECT
1179 ppp.person_id
1180 FROM
1181 hri_eq_sprvsr_hstry_chgs eq2
1182 ,per_periods_of_placement ppp
1183 WHERE eq2.person_id = ppp.person_id
1184 AND ppp.date_start <> eq2.erlst_evnt_effective_date
1185 AND ppp.actual_termination_date IS NULL);
1186 --
1187 dbg(sql%rowcount||' redundant contingent worker assignment changes removed');
1188 --
1189 COMMIT;
1190 --
1191 dbg(' ');
1192 dbg('Exiting find_changed_supervisors');
1193 --
1194 RETURN;
1195 --
1196 EXCEPTION
1197 --
1198 WHEN OTHERS THEN
1199 --
1200 dbg('An error occured while adding records to the change list.');
1201 output(sqlerrm);
1202 --
1203 -- Bug 4105868: Collection Diagnostics
1204 --
1205 g_msg_sub_group := NVL(g_msg_sub_group, 'FIND_CHANGED_SUPERVISORS');
1206 --
1207 RAISE;
1208 --
1209 END find_changed_supervisors;
1210 --
1211 -- ---------------------------------------------------------------------------
1212 -- This procedure deletes the supervisor status history for people whose
1213 -- supervisory status will changes due to events that have happened
1214 -- to be subordinates
1215 -- ---------------------------------------------------------------------------
1216 --
1217 PROCEDURE delete_old_supervisor_status IS
1218 --
1219 BEGIN
1220 --
1221 dbg('Inside delete_old_supervisor_status');
1222 --
1223 --
1224 DELETE HRI_CL_WKR_SUP_STATUS_CT
1225 WHERE person_id in (SELECT person_id
1226 FROM hri_eq_sprvsr_hstry_chgs
1227 );
1228 --
1229 --
1230 dbg('Exiting delete_old_supervisor_status');
1231 --
1232 --
1233 EXCEPTION
1234 --
1235 WHEN OTHERS THEN
1236 --
1237 dbg('An error occured while deleteing old supervisor status records.');
1238 output(sqlerrm);
1239 --
1240 -- Bug 4105868: Collection Diagnostics
1241 --
1242 g_msg_sub_group := NVL(g_msg_sub_group, 'DELETE_OLD_SUPERVISOR_STATUS');
1243 --
1244 RAISE;
1245 --
1246 --
1247 END delete_old_supervisor_status;
1248 --
1249 -- ---------------------------------------------------------------------------
1250 -- This procedure deletes the supervisor status history for people whose
1251 -- supervisory status will changes due to events that have happened
1252 -- to be subordinates (secondary asg version)
1253 -- ---------------------------------------------------------------------------
1254 --
1255 PROCEDURE delete_asg_supervisor_status IS
1256 --
1257 BEGIN
1258 --
1259 dbg('Inside delete_asg_supervisor_status');
1260 --
1261 --
1262 DELETE HRI_CL_WKR_SUP_STATUS_ASG_CT
1263 WHERE person_id in (SELECT person_id
1264 FROM hri_eq_sprvsr_hstry_chgs
1265 );
1266 --
1267 --
1268 dbg('Exiting delete_asg_supervisor_status');
1269 --
1270 --
1271 EXCEPTION
1272 --
1273 WHEN OTHERS THEN
1274 --
1275 dbg('An error occured while deleteing asg supervisor status records.');
1276 output(sqlerrm);
1277 --
1278 -- Bug 4105868: Collection Diagnostics
1279 --
1280 g_msg_sub_group := NVL(g_msg_sub_group, 'DELETE_OLD_SUPERVISOR_STATUS');
1281 --
1282 RAISE;
1283 --
1284 --
1285 END delete_asg_supervisor_status;
1286 --
1287 -- ---------------------------------------------------------------------------
1288 -- Main entry point to reload the supervisor status history
1289 -- ---------------------------------------------------------------------------
1290 --
1291 PROCEDURE full_refresh(p_start_date IN DATE
1292 ,p_end_date IN DATE
1293 )
1294 IS
1295 --
1296 l_sql_stmt VARCHAR2(2000);
1297 l_dummy1 VARCHAR2(2000);
1298 l_dummy2 VARCHAR2(2000);
1299 l_schema VARCHAR2(400);
1300 l_effective_start_date DATE;
1301 --
1302 BEGIN
1303 --
1304 dbg('Inside full_refresh');
1305 --
1306 -- Initialize variables
1307 --
1308 l_effective_start_date := p_start_date;
1309 --
1310 -- Time at start
1311 dbg('PL/SQL Start: ' || to_char(sysdate,'HH24:MI:SS'));
1312 --
1313 -- Get HRI schema name - get_app_info populates l_schema
1314 --
1315 IF fnd_installation.get_app_info('HRI',l_dummy1, l_dummy2, l_schema) THEN
1316 --
1317 -- Set start time
1318 --
1319 g_current_time := SYSDATE;
1320 --
1321 --
1322 --Insert new supervisor status history records for DBI
1323 --
1324 IF fnd_profile.value('HRI_IMPL_DBI') = 'Y' THEN
1325 --
1326 -- Disable/drop objects (indexes and triggers)
1327 --
1328 disable_objects(p_schema => l_schema,
1329 p_object_name => 'HRI_CL_WKR_SUP_STATUS_CT');
1330 --
1331 dbg('Disabled/dropped objects: ' || to_char(sysdate,'HH24:MI:SS'));
1332 --
1333 -- Empty out supervisor hierarchy history table
1334 --
1335 l_sql_stmt := 'TRUNCATE TABLE ' || l_schema || '.HRI_CL_WKR_SUP_STATUS_CT';
1336 EXECUTE IMMEDIATE(l_sql_stmt);
1337 --
1338 -- Write timing information to log
1339 --
1340 dbg('Truncated Supervisor Status History table: ' ||to_char(sysdate,'HH24:MI:SS'));
1341 --
1342 collect_data (p_collect_from => TRUNC(l_effective_start_date) ,
1343 p_collect_to => TRUNC(p_end_date));
1344 dbg('Re-populated Supervisor History table: ' || to_char(sysdate,'HH24:MI:SS'));
1345 --
1346 -- Re-enable/recreate objects
1347 --
1348 enable_objects(p_schema => l_schema,
1349 p_object_name => 'HRI_CL_WKR_SUP_STATUS_CT');
1350 dbg('Re-enabled/recreated objects: ' || to_char(sysdate,'HH24:MI:SS'));
1351 --
1352 END IF;
1353 --
1354 --Insert new supervisor status history records for OBIEE
1355 --
1356 IF fnd_profile.value('HRI_IMPL_OBIEE') = 'Y' THEN
1357 --
1358 -- Disable/drop objects (indexes and triggers)
1359 --
1360 run_sql_stmt_noerr('ALTER TRIGGER HRI_CL_WKR_SUP_STATUS_ASG_WHO DISABLE');
1361 --
1362 hri_utl_ddl.log_and_drop_indexes
1363 (p_application_short_name => 'HRI'
1364 ,p_table_name => 'HRI_CL_WKR_SUP_STATUS_ASG_CT'
1365 ,p_table_owner => l_schema);
1366 --
1367 dbg('Disabled/dropped objects: ' || to_char(sysdate,'HH24:MI:SS'));
1368 --
1369 -- Empty out supervisor hierarchy history table
1370 --
1371 l_sql_stmt := 'TRUNCATE TABLE ' || l_schema || '.HRI_CL_WKR_SUP_STATUS_ASG_CT';
1372 EXECUTE IMMEDIATE(l_sql_stmt);
1373 --
1374 -- Write timing information to log
1375 --
1376 dbg('Truncated Supervisor Status History table: ' ||to_char(sysdate,'HH24:MI:SS'));
1377 --
1378 collect_asg_data (p_collect_from => TRUNC(l_effective_start_date) ,
1379 p_collect_to => TRUNC(p_end_date));
1380 dbg('Re-populated Supervisor History table (secondary): ' || to_char(sysdate,'HH24:MI:SS'));
1381 --
1382 -- Re-enable/recreate objects
1383 --
1384 run_sql_stmt_noerr('ALTER TRIGGER HRI_CL_WKR_SUP_STATUS_ASG_WHO ENABLE');
1385 --
1386 hri_utl_ddl.recreate_indexes
1387 (p_application_short_name => 'HRI'
1388 ,p_table_name => 'HRI_CL_WKR_SUP_STATUS_ASG_CT'
1389 ,p_table_owner => l_schema);
1390 --
1391 dbg('Re-enabled/recreated objects: ' || to_char(sysdate,'HH24:MI:SS'));
1392 --
1393 -- Gather index stats
1394 --
1395 fnd_stats.gather_table_stats(l_schema, 'HRI_CL_WKR_SUP_STATUS_ASG_CT');
1396 --
1397 END IF;
1398 --
1399 -- Purge the events queue
1400 --
1401 HRI_OPL_EVENT_CAPTURE.purge_queue('HRI_EQ_SPRVSR_HSTRY_CHGS');
1402 --
1403 -- Write timing information to log
1404 dbg('Gathered stats: ' || to_char(sysdate,'HH24:MI:SS'));
1405 --
1406 ELSE
1407 --
1408 dbg('HRI not installed');
1409 --
1410 END IF;
1411 --
1412 dbg('Exiting full_refresh');
1413 --
1414 -- Bug 4105868: Collection Diagnostics
1415 --
1416 -- EXCEPTION
1417 --
1418 -- WHEN OTHERS THEN
1419 --
1420 -- g_msg_sub_group := NVL(g_msg_sub_group, 'FULL_REFRESH');
1421 --
1422 -- RAISE;
1423 --
1424 END full_refresh;
1425 --
1426 -- ---------------------------------------------------------------------------
1427 -- Incremental Update Process Entry Point
1428 -- p_start_date - is the earliest update date of assignment record
1429 -- p_end_date - is the latest update date of assignment record
1430 -- ---------------------------------------------------------------------------
1431 --
1432 PROCEDURE incremental_update( p_start_date IN DATE,
1433 p_end_date IN DATE) IS
1434 --
1435 l_effective_start_date DATE;
1436 l_effective_end_date DATE;
1437 l_dummy1 VARCHAR2(2000);
1438 l_dummy2 VARCHAR2(2000);
1439 l_schema VARCHAR2(400);
1440 --
1441 BEGIN
1442 --
1443 dbg('Inside incremental_update');
1444 --
1445 -- Initialize variables
1446 --
1447 l_effective_start_date := p_start_date;
1448 l_effective_end_date := p_end_date;
1449 --
1450 IF fnd_installation.get_app_info('HRI',l_dummy1, l_dummy2, l_schema) THEN
1451 --
1452 dbg('Starting incremental Update ...');
1453 --
1454 g_run_mode := 'INCREMENTAL';
1455 --
1456 -- Time at start
1457 --
1458 dbg('PL/SQL Start: ' || to_char(sysdate,'HH24:MI:SS'));
1459 --
1460 -- Set start time
1461 --
1462 g_current_time := SYSDATE;
1463 --
1464 IF fnd_profile.value('HRI_IMPL_DBI') = 'Y' THEN
1465 --
1466 -- Delete the status records of people whose status could have changed
1467 --
1468 dbg('Calling delete_old_supervisor_status...');
1469 delete_old_supervisor_status;
1470 --
1471 -- Insert Changed Records
1472 --
1473 dbg('Calling collect_incremental_data...');
1474 --
1475 collect_incremental_data;
1476 --
1477 END IF;
1478 --
1479 IF fnd_profile.value('HRI_IMPL_OBIEE') = 'Y' THEN
1480 --
1481 -- Delete the status records of people whose status could have changed
1482 --
1483 dbg('Calling delete_asg_supervisor_status...');
1484 delete_asg_supervisor_status;
1485 --
1486 -- Insert Changed Records
1487 --
1488 dbg('Calling collect_incremental_data...');
1489 --
1490 collect_asg_incremental_data;
1491 --
1492 END IF;
1493 --
1494 -- Purge the events queue
1495 --
1496 HRI_OPL_EVENT_CAPTURE.purge_queue('HRI_EQ_SPRVSR_HSTRY_CHGS');
1497 --
1498 -- Write timing information to log
1499 --
1500 dbg('Incremental supervisor status history collection completed successfully at ' ||
1501 to_char(sysdate,'HH24:MI:SS')||'.');
1502 --
1503 ELSE
1504 dbg('HRI not installed');
1505 END IF;
1506 --
1507 dbg('Exiting incremental_update');
1508 --
1509 -- Bug 4105868: Collection Diagnostics
1510 --
1511 EXCEPTION
1512 --
1513 WHEN OTHERS THEN
1514 --
1515 g_msg_sub_group := NVL(g_msg_sub_group, 'INCREMENTAL_UPDATE');
1516 --
1517 RAISE;
1518 --
1519 END incremental_update;
1520 --
1521 -- ---------------------------------------------------------------------------
1522 -- This procedure will be called by the Concurrent Manager for running
1523 -- the full refresh collection program
1524 -- ---------------------------------------------------------------------------
1525 --
1526 PROCEDURE full_refresh( errbuf OUT NOCOPY VARCHAR2,
1527 retcode OUT NOCOPY VARCHAR2,
1528 p_start_date IN VARCHAR2,
1529 p_end_date IN VARCHAR2,
1530 p_debugging IN VARCHAR2 DEFAULT 'N')
1531 IS
1532 --
1533 l_start_date DATE;
1534 l_end_date DATE;
1535 l_is_hr_installed VARCHAR2(10);
1536 l_frc_shrd_hr_prfl_val VARCHAR2(30); -- Variable to store value for
1537 -- Profile HRI:DBI Force Shared HR Processes
1538 --
1539 BEGIN
1540 --
1541 hri_bpl_conc_log.record_process_start('HRI_CL_WKR_SUP_STATUS_CT');
1542 --
1543 l_is_hr_installed := hr_general.chk_product_installed(800);
1544 l_frc_shrd_hr_prfl_val := nvl(fnd_profile.value('HRI_DBI_FORCE_SHARED_HR'),'N');
1545 --
1546 IF l_is_hr_installed = 'FALSE'
1547 OR l_frc_shrd_hr_prfl_val = 'Y' THEN
1548 --
1549 l_start_date := TRUNC(SYSDATE);
1550 l_end_date := hr_general.end_of_time;
1551 --
1552 IF l_is_hr_installed = 'FALSE' THEN
1553 --
1554 dbg('Foundation HR detected. Defaulting '||
1555 'collection to run from SYSDATE to end of time.');
1556 --
1557 ELSE
1558 --
1559 dbg('Profile HRI:DBI Force Foundation HR Processes has been set. '||
1560 'Forcing the collection to run from SYSDATE to end of time.');
1561 --
1562 END IF;
1563 --
1564 --
1565 -- If Full HR is installed
1566 --
1567 ELSE
1568 --
1569 -- Set dates
1570 --
1571 l_start_date := TRUNC(fnd_date.canonical_to_date(p_start_date));
1572 l_end_date := TRUNC(fnd_date.canonical_to_date(p_end_date));
1573 --
1574 END IF;
1575 --
1576 dbg('start date = '||to_char(l_start_date,'DD-MON-RRRR HH24:MI:SS'));
1577 dbg('end date = '||to_char(l_end_date,'DD-MON-RRRR HH24:MI:SS'));
1578 full_refresh( p_start_date => l_start_date,
1579 p_end_date => l_end_date);
1580 --
1581 -- Bug 4105868: Collection Diagnostic Call
1582 --
1583 hri_bpl_conc_log.log_process_end
1584 (p_status => TRUE
1585 ,p_period_from => TRUNC(l_start_date)
1586 ,p_period_to => TRUNC(l_end_date)
1587 ,p_attribute1 => p_debugging);
1588 --
1589 COMMIT;
1590 --
1591 EXCEPTION
1592 --
1593 WHEN OTHERS THEN
1594 --
1595 ROLLBACK;
1596 errbuf := SQLERRM;
1597 retcode := SQLCODE;
1598 --
1599 output(SQLERRM);
1600 --
1601 dbg('Supervisor Status History collection failed at ' ||
1602 to_char(sysdate,'HH24:MI:SS')||'.');
1603 --
1604 -- Bug 4105868: Collection Diagnostic Call
1605 --
1606 g_msg_sub_group := NVL(g_msg_sub_group, 'FULL_REFRESH');
1607 --
1608 hri_bpl_conc_log.log_process_info
1609 (p_msg_type => 'ERROR'
1610 ,p_note => SQLERRM
1611 ,p_package_name => 'HRI_OPL_SUP_STATUS_HST'
1612 ,p_msg_sub_group => g_msg_sub_group
1613 ,p_sql_err_code => SQLCODE
1614 ,p_msg_group => 'SUP_STS_HST');
1615 --
1616 hri_bpl_conc_log.log_process_end
1617 (p_status => FALSE
1618 ,p_period_from => TRUNC(l_start_date)
1619 ,p_period_to => TRUNC(l_end_date)
1620 ,p_attribute1 => p_debugging);
1621 --
1622 RAISE;
1623 --
1624 --
1625 END full_refresh;
1626 --
1627 -- ---------------------------------------------------------------------------
1628 -- This procedure will be called by the Concurrent Manager to run the
1629 -- incrmental collection process
1630 -- ---------------------------------------------------------------------------
1631 --
1632 PROCEDURE incremental_update( errbuf OUT NOCOPY VARCHAR2,
1633 retcode OUT NOCOPY VARCHAR2,
1634 p_debugging IN VARCHAR2 DEFAULT 'N') IS
1635 --
1636 l_start_date DATE ;
1637 l_end_date DATE ;
1638 l_bis_start_date DATE;
1639 l_bis_end_date DATE;
1640 l_period_from DATE;
1641 l_period_to DATE;
1642 l_is_hr_installed VARCHAR2(10);
1643 l_frc_shrd_hr_prfl_val VARCHAR2(30); -- Variable to store value for
1644 -- Profile HRI:DBI Force Shared HR Processes
1645 --
1646 BEGIN
1647 --
1648 hri_bpl_conc_log.record_process_start('HRI_CL_WKR_SUP_STATUS_CT');
1649 --
1650 -- If Full HR has not been installed or if profile HRI:DBI Force Shared HR
1651 -- Processes has been set, then the force the process to run in
1652 -- full refresh mode and from SYSDATE
1653 --
1654 l_is_hr_installed := hr_general.chk_product_installed(800);
1655 l_frc_shrd_hr_prfl_val := nvl(fnd_profile.value('HRI_DBI_FORCE_SHARED_HR'),'N');
1656 --
1657 IF l_is_hr_installed = 'FALSE'
1658 OR l_frc_shrd_hr_prfl_val = 'Y'
1659 THEN
1660 --
1661 --
1662 -- Insert the appropriate message in the log file
1663 --
1664 IF l_is_hr_installed = 'FALSE' THEN
1665 --
1666 dbg('HR not installed on this instance, defaulting the full refresh of '||
1667 'the process to run with following parameters');
1668 --
1669 ELSIF l_frc_shrd_hr_prfl_val = 'Y' THEN
1670 --
1671 dbg('Profile HRI:DBI Force Shared HR Processes has been set. '||
1672 'Forcing the full refresh of the process to run with following parameters');
1673 --
1674 END IF;
1675 --
1676 l_start_date := trunc(SYSDATE);
1677 l_end_date := hr_general.end_of_time;
1678 --
1679 dbg('Collect From Date : '||l_start_date);
1680 dbg('Collect To Date : '||l_end_date);
1681 --
1682 full_refresh( p_start_date => l_start_date,
1683 p_end_date => l_end_date);
1684 --
1685 --
1686 ELSE
1687 --
1688 --
1689 -- get the last run dates
1690 --
1691 bis_collection_utilities.get_last_refresh_dates('HRI_CL_WKR_SUP_STATUS_CT'
1692 ,l_bis_start_date
1693 ,l_bis_end_date
1694 ,l_period_from
1695 ,l_period_to);
1696 --
1697 l_start_date := TRUNC(l_period_to) + 1;
1698 l_end_date := TRUNC(SYSDATE);
1699 --
1700 dbg('start date = '||to_char(l_start_date,'DD-MON-RRRR HH24:MI:SS'));
1701 dbg('end date = '||to_char(l_end_date,'DD-MON-RRRR HH24:MI:SS'));
1702 --
1703 incremental_update( p_start_date => l_start_date,
1704 p_end_date => l_end_date);
1705 --
1706 --
1707 END IF;
1708 --
1709 -- Bug 4105868: Collection Diagnostic Call
1710 --
1711 hri_bpl_conc_log.log_process_end
1712 (p_status => TRUE
1713 ,p_period_from => TRUNC(l_start_date)
1714 ,p_period_to => TRUNC(l_end_date)
1715 ,p_attribute1 => p_debugging);
1716 --
1717 COMMIT;
1718 --
1719 EXCEPTION
1720 --
1721 WHEN OTHERS THEN
1722 --
1723 ROLLBACK;
1724 --
1725 errbuf := SQLERRM;
1726 retcode := SQLCODE;
1727 --
1728 output(SQLERRM);
1729 --
1730 dbg('Incremental Supervisor Status History collection failed at ' ||
1731 to_char(sysdate,'HH24:MI:SS')||'.');
1732 --
1733 -- Bug 4105868: Collection Diagnostic Call
1734 --
1735 g_msg_sub_group := NVL(g_msg_sub_group, 'INCREMENTAL_UPDATE');
1736 --
1737 hri_bpl_conc_log.log_process_info
1738 (p_msg_type => 'ERROR'
1739 ,p_note => SQLERRM
1740 ,p_package_name => 'HRI_OPL_SUP_STATUS_HST'
1741 ,p_msg_sub_group => g_msg_sub_group
1742 ,p_sql_err_code => SQLCODE
1743 ,p_msg_group => 'SUP_STS_HST');
1744 --
1745 hri_bpl_conc_log.log_process_end
1746 (p_status => FALSE
1747 ,p_period_from => TRUNC(l_start_date)
1748 ,p_period_to => TRUNC(l_end_date)
1749 ,p_attribute1 => p_debugging);
1750 --
1751 RAISE;
1752 --
1753 --
1754 END;
1755 --
1756 -- ---------------------------------------------------------------------------
1757 -- This procedure will be called by the Concurrent Manager for running
1758 -- the full or incremental refresh
1759 -- ---------------------------------------------------------------------------
1760 --
1761 PROCEDURE run_request (errbuf OUT NOCOPY VARCHAR2,
1762 retcode OUT NOCOPY VARCHAR2,
1763 p_start_date IN VARCHAR2,
1764 p_end_date IN VARCHAR2,
1765 p_full_refresh IN VARCHAR2,
1766 p_debugging IN VARCHAR2 DEFAULT 'N') IS
1767 --
1768 l_start_date DATE;
1769 l_end_date DATE;
1770 l_is_hr_installed VARCHAR2(10);
1771 l_full_refresh VARCHAR2(10);
1772 l_frc_shrd_hr_prfl_val VARCHAR2(30);
1773 l_message fnd_new_messages.message_text%TYPE;
1774 --
1775 BEGIN
1776 --
1777 dbg('Inside run_request');
1778 --
1779 hri_bpl_conc_log.record_process_start('HRI_CL_WKR_SUP_STATUS_CT');
1780 --
1781 -- Determine if the process needs to be run in Foundation HR mode
1782 --
1783 l_is_hr_installed := hr_general.chk_product_installed(800);
1784 l_frc_shrd_hr_prfl_val := nvl(fnd_profile.value('HRI_DBI_FORCE_SHARED_HR'),'N');
1785 --
1786 l_end_date := fnd_date.canonical_to_date(p_end_date);
1787 --
1788 IF l_is_hr_installed = 'FALSE'
1789 OR l_frc_shrd_hr_prfl_val = 'Y'
1790 THEN
1791 --
1792 -- Run the process in Foundation HR Mode, default the start date to sysdate and run
1793 -- full refresh
1794 --
1795 l_full_refresh := 'Y';
1796 l_start_date := trunc(SYSDATE);
1797 --
1798 -- Insert the appropriate message in the log file
1799 --
1800 IF l_is_hr_installed = 'FALSE' THEN
1801 --
1802 -- Bug 4105868: Collection Diagnostics
1803 --
1804 fnd_message.set_name('HRI', 'HRI_407287_FNDTN_HR_INSTLD');
1805 --
1806 fnd_message.set_token('START_DATE', l_start_date);
1807 fnd_message.set_token('END_DATE', l_end_date);
1808 fnd_message.set_token('FULL_REFRESH', l_full_refresh);
1809 --
1810 l_message := nvl(fnd_message.get, SQLERRM);
1811 --
1812 hri_bpl_conc_log.log_process_info
1813 (p_msg_type => 'WARNING'
1814 ,p_note => l_message
1815 ,p_package_name => 'HRI_OPL_SUP_STATUS_HST'
1816 ,p_msg_sub_group => 'RUN_REQUEST'
1817 ,p_sql_err_code => SQLCODE
1818 ,p_msg_group => 'SUP_STS_HST'
1819 );
1820 --
1821 output(l_message);
1822 --
1823 -- output('HR not installed on this instance, defaulting the process '||
1824 -- 'to run with following parameters');
1825 --
1826 ELSIF l_frc_shrd_hr_prfl_val = 'Y' THEN
1827 --
1828 -- Bug 4105868: Collection Diagnostics
1829 --
1830 fnd_message.set_name('HRI', 'HRI_407159_PRF_SHRD_IMPCT');
1831 --
1832 fnd_message.set_token('PROFILE_NAME', 'HRI:DBI Force Foundation HR Processes');
1833 --
1834 l_message := fnd_message.get;
1835 --
1836 hri_bpl_conc_log.log_process_info
1837 (p_msg_type => 'WARNING'
1838 ,p_note => l_message
1839 ,p_package_name => 'HRI_OPL_SUP_STATUS_HST'
1840 ,p_msg_sub_group => 'RUN_REQUEST'
1841 ,p_sql_err_code => SQLCODE
1842 ,p_msg_group => 'SUP_STS_HST');
1843 --
1844 output(l_message);
1845 --
1846 -- output('Profile HRI:DBI Force Foundation HR Processes has been set. '||
1847 -- 'Forcing the full refresh of the process to run with following parameters');
1848 --
1849 END IF;
1850 --
1851 ELSE
1852 --
1853 IF (p_full_refresh IS NULL) THEN
1854 l_full_refresh := hri_oltp_conc_param.get_parameter_value
1855 (p_parameter_name => 'FULL_REFRESH',
1856 p_process_table_name => 'HRI_CL_WKR_SUP_STATUS_CT');
1857 IF (l_full_refresh = 'Y') THEN
1858 l_start_date := hri_oltp_conc_param.get_date_parameter_value
1859 (p_parameter_name => 'FULL_REFRESH_FROM_DATE',
1860 p_process_table_name => 'HRI_CL_WKR_SUP_STATUS_CT');
1861 ELSE
1862 l_start_date := fnd_date.canonical_to_date(p_start_date);
1863 END IF;
1864 ELSE
1865 l_full_refresh := p_full_refresh;
1866 l_start_date := fnd_date.canonical_to_date(p_start_date);
1867 END IF;
1868 --
1869 END IF;
1870 --
1871 hri_bpl_conc_log.dbg('Full refresh: ' || l_full_refresh);
1872 hri_bpl_conc_log.dbg('Collect from: ' || l_start_date);
1873 --
1874 IF (l_full_refresh = 'Y') THEN
1875 --
1876 dbg('Calling full refresh of supervisor status history');
1877 --
1878 hri_opl_sup_status_hst.full_refresh(p_start_date => l_start_date
1879 ,p_end_date => l_end_date);
1880 --
1881 ELSE
1882 --
1883 dbg('Calling incremental update of supervisor status history');
1884 --
1885 hri_opl_sup_status_hst.incremental_update(p_start_date => l_start_date
1886 ,p_end_date => l_end_date);
1887 --
1888 END IF;
1889 --
1890 -- Bug 4105868: Collection Diagnostic Call
1891 --
1892 hri_bpl_conc_log.log_process_end(
1893 p_status => TRUE,
1894 p_period_from => TRUNC(l_start_date),
1895 p_period_to => TRUNC(l_end_date),
1896 p_attribute1 => p_full_refresh);
1897 --
1898 COMMIT;
1899 --
1900 dbg('Exiting run_request');
1901 --
1902 EXCEPTION
1903 --
1904 WHEN OTHERS THEN
1905 --
1906 ROLLBACK;
1907 --
1908 errbuf := SQLERRM;
1909 retcode := SQLCODE;
1910 --
1911 output(SQLERRM);
1912 --
1913 -- Bug 4105868: Collection Diagnostic Call
1914 --
1915 g_msg_sub_group := NVL(g_msg_sub_group, 'RUN_REQUEST');
1916 --
1917 hri_bpl_conc_log.log_process_info
1918 (p_msg_type => 'ERROR'
1919 ,p_note => SQLERRM
1920 ,p_package_name => 'HRI_OPL_SUP_STATUS_HST'
1921 ,p_msg_sub_group => g_msg_sub_group
1922 ,p_sql_err_code => SQLCODE
1923 ,p_msg_group => 'SUP_STS_HST');
1924 --
1925 hri_bpl_conc_log.log_process_end
1926 (p_status => FALSE
1927 ,p_period_from => TRUNC(l_start_date)
1928 ,p_period_to => TRUNC(l_end_date)
1929 ,p_attribute1 => p_full_refresh);
1930 --
1931 RAISE;
1932 --
1933 --
1934 END run_request;
1935 --
1936 END HRI_OPL_SUP_STATUS_HST;
[Home] [Help]