[Home] [Help]
PACKAGE BODY: APPS.HRI_OPL_UTL_ABSNC_DIM
Source
1 PACKAGE BODY hri_opl_utl_absnc_dim AS
2 /* $Header: hriouabd.pkb 120.7 2005/12/13 05:49:59 jtitmas noship $ */
3
4 -- Simple table types
5 TYPE g_date_tab_type IS TABLE OF DATE INDEX BY BINARY_INTEGER;
6 TYPE g_number_tab_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
7 TYPE g_varchar2_tab_type IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;
8
9 -- PL/SQL table representing database table
10 g_abs_sk_pk g_number_tab_type;
11 g_abs_attendance_id g_number_tab_type;
12 g_abs_start_date g_date_tab_type;
13 g_abs_end_date g_date_tab_type;
14 g_abs_notification_date g_date_tab_type;
15 g_abs_person_id g_number_tab_type;
16 g_abs_category_code g_varchar2_tab_type;
17 g_abs_reason_code g_varchar2_tab_type;
18 g_abs_status_code g_varchar2_tab_type;
19 g_abs_attendance_type_id g_number_tab_type;
20 g_abs_attendance_reason_id g_number_tab_type;
21 g_abs_drtn_days g_number_tab_type;
22 g_abs_drtn_hrs g_number_tab_type;
23 g_abs_index PLS_INTEGER;
24
25 -- End of time
26 g_end_of_time DATE;
27
28 -- Global HRI Multithreading Array
29 g_mthd_action_array HRI_ADM_MTHD_ACTIONS%rowtype;
30
31 -- Global parameters
32 g_refresh_start_date DATE;
33 g_full_refresh VARCHAR2(30);
34 g_dbi_collection_start_date DATE;
35 g_sysdate DATE;
36 g_user NUMBER;
37
38 -- ----------------------------------------------------------------------------
39 -- Runs given sql statement dynamically
40 -- ----------------------------------------------------------------------------
41 PROCEDURE run_sql_stmt_noerr(p_sql_stmt VARCHAR2) IS
42
43 BEGIN
44
45 EXECUTE IMMEDIATE p_sql_stmt;
46
47 EXCEPTION WHEN OTHERS THEN
48
49 null;
50
51 END run_sql_stmt_noerr;
52
53 -- ----------------------------------------------------------------------------
54 -- Sets global parameters from multi-threading process parameters
55 -- ----------------------------------------------------------------------------
56 PROCEDURE set_parameters(p_mthd_action_id IN NUMBER,
57 p_mthd_stage_code IN VARCHAR2) IS
58
59 BEGIN
60
61 -- If parameters haven't already been set, then set them
62 IF (g_refresh_start_date IS NULL OR
63 p_mthd_stage_code = 'PRE_PROCESS') THEN
64
65 g_dbi_collection_start_date := hri_oltp_conc_param.get_date_parameter_value
66 (p_parameter_name => 'FULL_REFRESH_FROM_DATE',
67 p_process_table_name => 'HRI_CS_ABSENCE_CT');
68
69 -- If called for the first time set the defaulted parameters
70 IF (p_mthd_stage_code = 'PRE_PROCESS') THEN
71
72 g_full_refresh := hri_oltp_conc_param.get_parameter_value
73 (p_parameter_name => 'FULL_REFRESH',
74 p_process_table_name => 'HRI_CS_ABSENCE_CT');
75
76 -- Log defaulted parameters so the slave processes pick up
77 hri_opl_multi_thread.update_parameters
78 (p_mthd_action_id => p_mthd_action_id,
79 p_full_refresh => g_full_refresh,
80 p_global_start_date => g_dbi_collection_start_date);
81
82 END IF;
83
84 g_mthd_action_array := hri_opl_multi_thread.get_mthd_action_array
85 (p_mthd_action_id);
86 g_refresh_start_date := g_mthd_action_array.collect_from_date;
87 g_full_refresh := g_mthd_action_array.full_refresh_flag;
88 g_sysdate := sysdate;
89 g_user := fnd_global.user_id;
90 g_end_of_time := hr_general.end_of_time;
91
92 hri_bpl_conc_log.dbg('Full refresh: ' || g_full_refresh);
93 hri_bpl_conc_log.dbg('Collect from: ' ||
94 to_char(g_dbi_collection_start_date));
95 END IF;
96
97
98 END set_parameters;
99
100 -- ----------------------------------------------------------------------------
101 -- Adds future absences that are now occurring/occurred
102 -- and occurring absences to the event queue
103 -- ----------------------------------------------------------------------------
104 PROCEDURE update_eq_with_status_changes IS
105
106 BEGIN
107
108 INSERT INTO hri_eq_utl_absnc_dim
109 (absence_attendance_id)
110 SELECT
111 dim.absence_attendance_id
112 FROM
113 hri_cs_absence_ct dim
114 WHERE dim.absence_status_code IN ('FUTURE','OCCURRING')
115 AND dim.abs_start_date <= trunc(sysdate)
116 AND NOT EXISTS
117 (SELECT null
118 FROM hri_eq_utl_absnc_dim eq
119 WHERE eq.absence_attendance_id = dim.absence_attendance_id);
120
121 commit;
122
123 END update_eq_with_status_changes;
124
125 -- ----------------------------------------------------------------------------
126 -- Populates parent event queues with the absence change events
127 -- ----------------------------------------------------------------------------
128 PROCEDURE populate_parent_eqs(p_event_type IN VARCHAR2,
129 p_start_abs_id IN NUMBER,
130 p_end_abs_id IN NUMBER) IS
131
132 BEGIN
133
134 -- Process according to event type
135 IF (p_event_type = 'PURGES') THEN
136
137 -- Update fact event queue with surrogate key of purged absences
138 -- Bug 4648262
139 INSERT INTO hri_eq_utl_absnc_fact
140 (absence_sk_fk)
141 SELECT
142 dim.absence_sk_pk
143 FROM
144 hri_cs_absence_ct dim
145 ,hri_eq_utl_absnc_dim eq
146 WHERE dim.absence_attendance_id = eq.absence_attendance_id
147 AND eq.absence_attendance_id BETWEEN p_start_abs_id AND p_end_abs_id
148 AND NOT EXISTS
149 (SELECT null
150 FROM per_absence_attendances tab
151 WHERE tab.absence_attendance_id = eq.absence_attendance_id)
152 AND NOT EXISTS
153 (SELECT null
154 FROM hri_eq_utl_absnc_fact eq2
155 WHERE eq2.absence_sk_fk = dim.absence_sk_pk);
156
157 -- Update summary event queue with surrogate key of purged absences
158 -- Bug 4648262
159 INSERT INTO hri_eq_sup_absnc
160 (source_id
161 ,source_type
162 ,erlst_evnt_effective_date)
163 SELECT
164 dim.absence_sk_pk
165 ,'ABSENCE'
166 ,to_date(null)
167 FROM
168 hri_cs_absence_ct dim
169 ,hri_eq_utl_absnc_dim eq
170 WHERE dim.absence_attendance_id = eq.absence_attendance_id
171 AND eq.absence_attendance_id BETWEEN p_start_abs_id AND p_end_abs_id
172 AND NOT EXISTS
173 (SELECT null
174 FROM per_absence_attendances tab
175 WHERE tab.absence_attendance_id = eq.absence_attendance_id)
176 AND NOT EXISTS
177 (SELECT null
178 FROM hri_eq_sup_absnc eq2
179 WHERE eq2.source_id = dim.absence_sk_pk
180 AND eq2.source_type = 'ABSENCE');
181
182 ELSIF (p_event_type = 'UPDATES') THEN
183
184 -- Update fact event queue with surrogate key
185 INSERT INTO hri_eq_utl_absnc_fact
186 (absence_sk_fk)
187 SELECT
188 dim.absence_sk_pk
189 FROM
190 hri_cs_absence_ct dim
191 ,hri_eq_utl_absnc_dim eq
192 WHERE dim.absence_attendance_id = eq.absence_attendance_id
193 AND eq.absence_attendance_id BETWEEN p_start_abs_id AND p_end_abs_id
194 AND NOT EXISTS
195 (SELECT null
196 FROM hri_eq_utl_absnc_fact eq2
197 WHERE eq2.absence_sk_fk = dim.absence_sk_pk);
198
199 -- Update summary event queue with surrogate key
200 INSERT INTO hri_eq_sup_absnc
201 (source_id
202 ,source_type
203 ,erlst_evnt_effective_date)
204 SELECT
205 dim.absence_sk_pk
206 ,'ABSENCE'
207 ,to_date(null)
208 FROM
209 hri_cs_absence_ct dim
210 ,hri_eq_utl_absnc_dim eq
211 WHERE dim.absence_attendance_id = eq.absence_attendance_id
212 AND eq.absence_attendance_id BETWEEN p_start_abs_id AND p_end_abs_id
213 AND NOT EXISTS
214 (SELECT null
215 FROM hri_eq_sup_absnc eq2
216 WHERE eq2.source_id = dim.absence_sk_pk
217 AND eq2.source_type = 'ABSENCE');
218
219 END IF;
220
221 END populate_parent_eqs;
222
223 -- ----------------------------------------------------------------------------
224 -- Repopulates the supervisor events helper table incrementally
225 -- ----------------------------------------------------------------------------
226 PROCEDURE process_range_incr(p_start_abs_id IN NUMBER,
227 p_end_abs_id IN NUMBER) IS
228
229 BEGIN
230
231 -- Update parent event queues with the surrogate key of any purges
232 populate_parent_eqs
233 (p_event_type => 'PURGES',
234 p_start_abs_id => p_start_abs_id,
235 p_end_abs_id => p_end_abs_id);
236
237 -- Delete old records
238 DELETE FROM hri_cs_absence_ct dim
239 WHERE dim.absence_attendance_id IN
240 (SELECT eq.absence_attendance_id
241 FROM hri_eq_utl_absnc_dim eq
242 WHERE eq.absence_attendance_id BETWEEN p_start_abs_id AND p_end_abs_id);
243
244 -- Insert new/changed records
245 INSERT INTO hri_cs_absence_ct
246 (absence_sk_pk
247 ,absence_attendance_id
248 ,abs_start_date
249 ,abs_end_date
250 ,abs_notification_date
251 ,abs_person_id
252 ,absence_category_code
253 ,absence_reason_code
254 ,absence_status_code
255 ,absence_attendance_type_id
256 ,abs_attendance_reason_id
257 ,abs_drtn_days
258 ,abs_drtn_hrs
259 ,last_update_date
260 ,last_updated_by
261 ,last_update_login
262 ,created_by
263 ,creation_date)
264 SELECT
265 paa.absence_attendance_id absence_sk_pk
266 ,paa.absence_attendance_id absence_attendance_id
267 ,paa.date_start abs_start_date
268 ,NVL(paa.date_end, g_end_of_time) abs_end_date
269 ,paa.date_notification abs_notification_date
270 ,paa.person_id abs_person_id
271 ,NVL(pat.absence_category, 'NA_EDW') absence_category_code
272 ,NVL(par.name, 'NA_EDW') absence_reason_code
273 ,CASE WHEN paa.date_start > TRUNC(SYSDATE)
274 THEN 'FUTURE'
275 WHEN (TRUNC(SYSDATE) BETWEEN paa.date_start
276 AND NVL(paa.date_end, g_end_of_time))
277 THEN 'OCCURRING'
278 ELSE 'OCCURRED'
279 END absence_status_code
280 ,paa.absence_attendance_type_id
281 ,NVL(paa.abs_attendance_reason_id, -1) abs_attendance_reason_id
282 ,SUM(hri_bpl_utilization.calculate_absence_duration
283 (paa.absence_attendance_id
284 ,'DAYS'
285 ,paa.absence_hours
286 ,paa.absence_days
287 ,asg.assignment_id
288 ,asg.business_group_id
289 ,asg.primary_flag
290 ,paa.date_start
291 ,NVL(paa.date_end, trunc(sysdate))
292 ,paa.time_start
293 ,paa.time_end)) abs_drtn_days
294 ,SUM(hri_bpl_utilization.calculate_absence_duration
295 (paa.absence_attendance_id
296 ,'HOURS'
297 ,paa.absence_hours
298 ,paa.absence_days
299 ,asg.assignment_id
300 ,asg.business_group_id
301 ,asg.primary_flag
302 ,paa.date_start
303 ,NVL(paa.date_end, trunc(sysdate))
304 ,paa.time_start
305 ,paa.time_end)) abs_drtn_hrs
306 ,g_sysdate
307 ,g_user
308 ,g_user
309 ,g_user
310 ,g_sysdate
311 FROM
312 per_absence_attendances paa
313 ,per_absence_attendance_types pat
314 ,per_abs_attendance_reasons par
315 ,per_all_assignments_f asg
316 ,hri_eq_utl_absnc_dim eq
317 WHERE eq.absence_attendance_id BETWEEN p_start_abs_id
318 AND p_end_abs_id
319 AND paa.absence_attendance_id = eq.absence_attendance_id
320 AND paa.absence_attendance_type_id = pat.absence_attendance_type_id
321 AND paa.abs_attendance_reason_id = par.abs_attendance_reason_id (+)
322 AND paa.person_id = asg.person_id
323 AND asg.assignment_type IN ('E','C')
324 AND paa.date_start BETWEEN asg.effective_start_date
325 AND asg.effective_end_date
326 AND paa.date_start IS NOT NULL
327 AND NVL(paa.date_end, g_end_of_time) >= g_dbi_collection_start_date
328 GROUP BY
329 paa.absence_attendance_id
330 ,paa.date_start
331 ,paa.date_end
332 ,paa.date_notification
333 ,paa.person_id
334 ,pat.absence_category
335 ,par.name
336 ,paa.absence_attendance_type_id
337 ,paa.abs_attendance_reason_id
338 ,paa.absence_days
339 ,paa.absence_hours;
340
341 -- Update parent event queues with the surrogate key of the updates
342 populate_parent_eqs
343 (p_event_type => 'UPDATES',
344 p_start_abs_id => p_start_abs_id,
345 p_end_abs_id => p_end_abs_id);
346
347 -- Commit the processing for the range
348 commit;
349
350 END process_range_incr;
351
352 -- ----------------------------------------------------------------------------
353 -- Bulk inserts from PL/SQL table to database
354 -- ----------------------------------------------------------------------------
355 PROCEDURE bulk_insert_rows IS
356
357 BEGIN
358
359 g_user := fnd_global.user_id;
360 g_sysdate := sysdate;
361
362 -- Bulk insert rows if any exist
363 IF (g_abs_index > 0) THEN
364
365 FORALL i IN 1..g_abs_index
366 INSERT INTO hri_cs_absence_ct
367 (absence_sk_pk
368 ,absence_attendance_id
369 ,abs_start_date
370 ,abs_end_date
371 ,abs_notification_date
372 ,abs_person_id
373 ,absence_category_code
374 ,absence_reason_code
375 ,absence_status_code
376 ,absence_attendance_type_id
377 ,abs_attendance_reason_id
378 ,abs_drtn_days
379 ,abs_drtn_hrs
380 ,last_update_date
381 ,last_updated_by
382 ,last_update_login
383 ,created_by
384 ,creation_date)
385 VALUES
386 (g_abs_sk_pk(i),
387 g_abs_attendance_id(i),
388 g_abs_start_date(i),
389 g_abs_end_date(i),
390 g_abs_notification_date(i),
391 g_abs_person_id(i),
392 g_abs_category_code(i),
393 g_abs_reason_code(i),
394 g_abs_status_code(i),
395 g_abs_attendance_type_id(i),
396 g_abs_attendance_reason_id(i),
397 g_abs_drtn_days(i),
398 g_abs_drtn_hrs(i),
399 g_sysdate,
400 g_user,
401 g_user,
402 g_user,
403 g_sysdate);
404
405 -- Commit
406 commit;
407
408 END IF;
409
410 -- Reset index
411 g_abs_index := 0;
412
413 END bulk_insert_rows;
414
415 -- ----------------------------------------------------------------------------
416 -- Inserts row into PL/SQL table
417 -- ----------------------------------------------------------------------------
418 PROCEDURE insert_row(
419 p_abs_sk_pk IN NUMBER,
420 p_abs_attendance_id IN NUMBER,
421 p_abs_start_date IN DATE,
422 p_abs_end_date IN DATE,
423 p_abs_notification_date IN DATE,
424 p_abs_person_id IN NUMBER,
425 p_abs_category_code IN VARCHAR2,
426 p_abs_reason_code IN VARCHAR2,
427 p_abs_status_code IN VARCHAR2,
428 p_abs_attendance_type_id IN NUMBER,
429 p_abs_attendance_reason_id IN NUMBER,
430 p_abs_drtn_days IN NUMBER,
431 p_abs_drtn_hrs IN NUMBER) IS
432
433 BEGIN
434
435 g_abs_index := g_abs_index + 1;
436 g_abs_sk_pk(g_abs_index) := p_abs_sk_pk;
437 g_abs_attendance_id(g_abs_index) := p_abs_attendance_id;
438 g_abs_start_date(g_abs_index) := p_abs_start_date;
439 g_abs_end_date(g_abs_index) := p_abs_end_date;
440 g_abs_notification_date(g_abs_index) := p_abs_notification_date;
441 g_abs_person_id(g_abs_index) := p_abs_person_id;
442 g_abs_category_code(g_abs_index) := p_abs_category_code;
443 g_abs_reason_code(g_abs_index) := p_abs_reason_code;
444 g_abs_status_code(g_abs_index) := p_abs_status_code;
445 g_abs_attendance_type_id(g_abs_index) := p_abs_attendance_type_id;
446 g_abs_attendance_reason_id(g_abs_index) := p_abs_attendance_reason_id;
447 g_abs_drtn_days(g_abs_index) := p_abs_drtn_days;
448 g_abs_drtn_hrs(g_abs_index) := p_abs_drtn_hrs;
449
450 END insert_row;
451
452 -- ----------------------------------------------------------------------------
453 -- Processes a single person
454 -- ----------------------------------------------------------------------------
455 PROCEDURE process_person(p_person_id IN NUMBER) IS
456
457 CURSOR absence_csr IS
458 SELECT
459 paa.absence_attendance_id absence_sk_pk
460 ,paa.absence_attendance_id
461 ,paa.date_start abs_start_date
462 ,NVL(paa.date_end, g_end_of_time) abs_end_date
463 ,paa.date_notification abs_notification_date
464 ,paa.person_id abs_person_id
465 ,NVL(pat.absence_category, 'NA_EDW') absence_category_code
466 ,NVL(par.name, 'NA_EDW') absence_reason_code
467 ,CASE WHEN paa.date_start > TRUNC(SYSDATE)
468 THEN 'FUTURE'
469 WHEN (TRUNC(SYSDATE) BETWEEN paa.date_start
470 AND NVL(paa.date_end, g_end_of_time))
471 THEN 'OCCURRING'
472 ELSE 'OCCURRED'
473 END absence_status_code
474 ,paa.absence_attendance_type_id
475 ,NVL(paa.abs_attendance_reason_id, -1) abs_attendance_reason_id
476 ,SUM(hri_bpl_utilization.calculate_absence_duration
477 (paa.absence_attendance_id
478 ,'DAYS'
479 ,paa.absence_hours
480 ,paa.absence_days
481 ,asg.assignment_id
482 ,asg.business_group_id
483 ,asg.primary_flag
484 ,paa.date_start
485 ,NVL(paa.date_end, trunc(sysdate))
486 ,paa.time_start
487 ,paa.time_end)) abs_drtn_days
488 ,SUM(hri_bpl_utilization.calculate_absence_duration
489 (paa.absence_attendance_id
490 ,'HOURS'
491 ,paa.absence_hours
492 ,paa.absence_days
493 ,asg.assignment_id
494 ,asg.business_group_id
495 ,asg.primary_flag
496 ,paa.date_start
497 ,NVL(paa.date_end, trunc(sysdate))
498 ,paa.time_start
499 ,paa.time_end)) abs_drtn_hrs
500 FROM
501 per_absence_attendances paa
502 ,per_absence_attendance_types pat
503 ,per_abs_attendance_reasons par
504 ,per_all_assignments_f asg
505 WHERE paa.person_id = p_person_id
506 AND paa.absence_attendance_type_id = pat.absence_attendance_type_id
507 AND paa.abs_attendance_reason_id = par.abs_attendance_reason_id (+)
508 AND paa.person_id = asg.person_id
509 AND asg.assignment_type IN ('E','C')
510 AND paa.date_start BETWEEN asg.effective_start_date
511 AND asg.effective_end_date
512 AND paa.date_start IS NOT NULL
513 AND NVL(paa.date_end, g_end_of_time) >= g_dbi_collection_start_date
514 GROUP BY
515 paa.absence_attendance_id
516 ,paa.date_start
517 ,paa.date_end
518 ,paa.date_notification
519 ,paa.person_id
520 ,pat.absence_category
521 ,par.name
522 ,paa.absence_attendance_type_id
523 ,paa.abs_attendance_reason_id
524 ,paa.absence_days
525 ,paa.absence_hours;
526
527 -- PL/SQL table for cursor fetch
528 l_abs_sk_pk g_number_tab_type;
529 l_abs_attendance_id g_number_tab_type;
530 l_abs_start_date g_date_tab_type;
531 l_abs_end_date g_date_tab_type;
532 l_abs_notification_date g_date_tab_type;
533 l_abs_person_id g_number_tab_type;
534 l_abs_category_code g_varchar2_tab_type;
535 l_abs_reason_code g_varchar2_tab_type;
536 l_abs_status_code g_varchar2_tab_type;
537 l_abs_attendance_type_id g_number_tab_type;
538 l_abs_attendance_reason_id g_number_tab_type;
539 l_abs_drtn_days g_number_tab_type;
540 l_abs_drtn_hrs g_number_tab_type;
541
542 BEGIN
543
544 -- Bulk fetch from cursor
545 OPEN absence_csr;
546 FETCH absence_csr BULK COLLECT INTO
547 l_abs_sk_pk,
548 l_abs_attendance_id,
549 l_abs_start_date,
550 l_abs_end_date,
551 l_abs_notification_date,
552 l_abs_person_id,
553 l_abs_category_code,
554 l_abs_reason_code,
555 l_abs_status_code,
556 l_abs_attendance_type_id,
557 l_abs_attendance_reason_id,
558 l_abs_drtn_days,
559 l_abs_drtn_hrs;
560 CLOSE absence_csr;
561
562 -- If rows are returned then store them in PL/SQL table
563 IF (l_abs_sk_pk.EXISTS(1)) THEN
564
565 -- Loop through and insert rows to PL/SQL table
566 FOR i IN l_abs_sk_pk.FIRST..l_abs_sk_pk.LAST LOOP
567 insert_row
568 (p_abs_sk_pk => l_abs_sk_pk(i),
569 p_abs_attendance_id => l_abs_attendance_id(i),
570 p_abs_start_date => l_abs_start_date(i),
571 p_abs_end_date => l_abs_end_date(i),
572 p_abs_notification_date => l_abs_notification_date(i),
573 p_abs_person_id => l_abs_person_id(i),
574 p_abs_category_code => l_abs_category_code(i),
575 p_abs_reason_code => l_abs_reason_code(i),
576 p_abs_status_code => l_abs_status_code(i),
577 p_abs_attendance_type_id => l_abs_attendance_type_id(i),
578 p_abs_attendance_reason_id => l_abs_attendance_reason_id(i),
579 p_abs_drtn_days => l_abs_drtn_days(i),
580 p_abs_drtn_hrs => l_abs_drtn_hrs(i));
581 END LOOP;
582
583 END IF;
584
585 -- Insert rows if limit is reached
586 IF (g_abs_index > 2000) THEN
587 bulk_insert_rows;
588 END IF;
589
590 END process_person;
591
592 -- ----------------------------------------------------------------------------
593 -- Full refresh of range
594 -- ----------------------------------------------------------------------------
595 PROCEDURE process_range_full(p_start_psn_id IN NUMBER,
596 p_end_psn_id IN NUMBER) IS
597
598 -- Person in range
599 CURSOR person_csr IS
600 SELECT DISTINCT
601 paa.person_id
602 FROM per_absence_attendances paa
603 WHERE paa.person_id BETWEEN p_start_psn_id AND p_end_psn_id
604 AND paa.date_start IS NOT NULL
605 AND NVL(paa.date_end, sysdate) >= g_dbi_collection_start_date;
606
607 BEGIN
608
609 -- Reset global index
610 g_abs_index := 0;
611
612 -- Loop through people in range
613 FOR person_rec IN person_csr LOOP
614
615 -- Process people one at a time
616 process_person(person_rec.person_id);
617
618 END LOOP;
619
620 -- Insert any remaining rows for range
621 bulk_insert_rows;
622
623 END process_range_full;
624
625 -- ----------------------------------------------------------------------------
626 -- PROCESS_RANGE
627 -- This procedure includes the logic required for processing the assignments
628 -- which have been included in the range. It is dynamically invoked by the
629 -- multithreading child process. It manages the multithreading ranges.
630 -- ----------------------------------------------------------------------------
631 PROCEDURE process_range(errbuf OUT NOCOPY VARCHAR2
632 ,retcode OUT NOCOPY NUMBER
633 ,p_mthd_action_id IN NUMBER
634 ,p_mthd_range_id IN NUMBER
635 ,p_start_object_id IN NUMBER
636 ,p_end_object_id IN NUMBER) IS
637
638 BEGIN
639
640 -- Set the parameters
641 set_parameters
642 (p_mthd_action_id => p_mthd_action_id,
643 p_mthd_stage_code => 'PROCESS_RANGE');
644
645 -- Process range in corresponding refresh mode
646 IF g_full_refresh = 'Y' THEN
647 process_range_full
648 (p_start_psn_id => p_start_object_id,
649 p_end_psn_id => p_end_object_id);
650 ELSE
651 process_range_incr
652 (p_start_abs_id => p_start_object_id,
653 p_end_abs_id => p_end_object_id);
654 END IF;
655
656 END process_range;
657
658 -- ----------------------------------------------------------------------------
659 -- Pre process entry point
660 -- ----------------------------------------------------------------------------
661 PROCEDURE pre_process(p_mthd_action_id IN NUMBER,
662 p_sqlstr OUT NOCOPY VARCHAR2) IS
663
664 l_sql_stmt VARCHAR2(2000);
665 l_dummy1 VARCHAR2(2000);
666 l_dummy2 VARCHAR2(2000);
667 l_schema VARCHAR2(400);
668
669 BEGIN
670
671 -- Set parameter globals
672 set_parameters
673 (p_mthd_action_id => p_mthd_action_id,
674 p_mthd_stage_code => 'PRE_PROCESS');
675
676 -- Get HRI schema name - get_app_info populates l_schema
677 IF fnd_installation.get_app_info('HRI',l_dummy1, l_dummy2, l_schema) THEN
678 null;
679 END IF;
680
681 -- Disable WHO trigger
682 run_sql_stmt_noerr('ALTER TRIGGER HRI_CS_ABSENCE_CT_WHO DISABLE');
683
684 -- ********************
685 -- Full Refresh Section
686 -- ********************
687 IF (g_full_refresh = 'Y' OR
688 g_mthd_action_array.foundation_hr_flag = 'Y') THEN
689
690 -- Empty out absence dimension table
691 l_sql_stmt := 'TRUNCATE TABLE ' || l_schema || '.HRI_CS_ABSENCE_CT';
692 EXECUTE IMMEDIATE(l_sql_stmt);
693
694 -- In shared HR mode do not return a SQL statement so that
695 -- process_range and post_process will not be executed
696 IF (g_mthd_action_array.foundation_hr_flag = 'Y') THEN
697
698 -- Call post processing API
699 post_process
700 (p_mthd_action_id => p_mthd_action_id);
701
702 ELSE
703
704 -- Drop all the indexes on the table
705 hri_utl_ddl.log_and_drop_indexes
706 (p_application_short_name => 'HRI',
707 p_table_name => 'HRI_CS_ABSENCE_CT',
708 p_table_owner => l_schema);
709
710
711 -- Set the SQL statement for the entire range
712 p_sqlstr :=
713 'SELECT /*+ PARALLEL(paa, DEFAULT, DEFAULT) */ DISTINCT
714 paa.person_id object_id
715 FROM per_absence_attendances paa
716 WHERE paa.date_start IS NOT NULL
717 AND NVL(paa.date_end, sysdate) >= to_date(''' ||
718 to_char(g_dbi_collection_start_date, 'DD-MM-YYYY') ||
719 ''',''DD-MM-YYYY'')
720 ORDER BY paa.person_id';
721
722 END IF;
723
724 ELSE
725
726 -- Inserts future and occurring absences into the event queue
727 update_eq_with_status_changes;
728
729 -- Set the SQL statement for the incremental range
730 p_sqlstr :=
731 'SELECT /*+ PARALLEL(eq, DEFAULT, DEFAULT) */
732 absence_attendance_id object_id
733 FROM hri_eq_utl_absnc_dim eq
734 ORDER BY absence_attendance_id';
735
736 END IF;
737
738 END pre_process;
739
740 -- ----------------------------------------------------------------------------
741 -- Post process entry point
742 -- ----------------------------------------------------------------------------
743 PROCEDURE post_process(p_mthd_action_id NUMBER) IS
744
745 l_sql_stmt VARCHAR2(2000);
746 l_dummy1 VARCHAR2(2000);
747 l_dummy2 VARCHAR2(2000);
748 l_schema VARCHAR2(400);
749
750 BEGIN
751
752 -- Check parameters are set
753 set_parameters
754 (p_mthd_action_id => p_mthd_action_id,
755 p_mthd_stage_code => 'POST_PROCESS');
756
757 IF (p_mthd_action_id > -1) THEN
758
759 -- Log process end
760 hri_bpl_conc_log.record_process_start('HRI_CS_ABSENCE_CT');
761 hri_bpl_conc_log.log_process_end(
762 p_status => TRUE
763 ,p_period_from => TRUNC(g_refresh_start_date)
764 ,p_period_to => TRUNC(SYSDATE)
765 ,p_attribute1 => g_full_refresh);
766
767 END IF;
768
769 -- Enable WHO trigger
770 run_sql_stmt_noerr('ALTER TRIGGER HRI_CS_ABSENCE_CT_WHO ENABLE');
771
772 -- Get HRI schema name - get_app_info populates l_schema
773 IF fnd_installation.get_app_info('HRI',l_dummy1, l_dummy2, l_schema) THEN
774 null;
775 END IF;
776
777 -- Recreate indexes
778 IF (g_full_refresh = 'Y') THEN
779 hri_utl_ddl.recreate_indexes
780 (p_application_short_name => 'HRI',
781 p_table_name => 'HRI_CS_ABSENCE_CT',
782 p_table_owner => l_schema);
783 END IF;
784
785 -- Empty out absence dimension event queue
786 l_sql_stmt := 'TRUNCATE TABLE ' || l_schema || '.HRI_EQ_UTL_ABSNC_DIM';
787 EXECUTE IMMEDIATE(l_sql_stmt);
788
789 END post_process;
790
791 -- Populates table in a single thread
792 PROCEDURE single_thread_process(p_full_refresh_flag IN VARCHAR2) IS
793
794 l_end_abs_id NUMBER;
795 l_end_psn_id NUMBER;
796 l_dummy VARCHAR2(32000);
797 l_from_date DATE := hri_bpl_parameter.get_bis_global_start_date;
798
799 BEGIN
800
801 -- get max ids
802 SELECT max(person_id) INTO l_end_psn_id
803 FROM per_all_people_f;
804 SELECT max(absence_attendance_id) INTO l_end_abs_id
805 FROM per_absence_attendances;
806
807 -- Set globals
808 g_full_refresh := p_full_refresh_flag;
809 g_refresh_start_date := l_from_date;
810 g_dbi_collection_start_date := l_from_date;
811 g_end_of_time := hr_general.end_of_time;
812 l_dummy := 'HRI';
813
814 -- truncate table
815 EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || l_dummy || '.hri_cs_absence_ct';
816
817 -- Process range
818 IF (p_full_refresh_flag = 'Y') THEN
819 process_range_full(0, l_end_psn_id);
820 ELSE
821 process_range_incr(0, l_end_abs_id);
822 END IF;
823
824 -- truncate eq
825 EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || l_dummy || '.hri_eq_utl_absnc_dim';
826
827 END single_thread_process;
828
829 END hri_opl_utl_absnc_dim;