[Home] [Help]
PACKAGE BODY: APPS.HRI_OPL_UTL_ABSNC_FACT
Source
1 PACKAGE BODY hri_opl_utl_absnc_fact AS
2 /* $Header: hriouabf.pkb 120.5 2005/11/16 01:03:14 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_tab_abs_sk_fk g_number_tab_type;
11 g_tab_effective_date g_date_tab_type;
12 g_tab_drtn_days g_number_tab_type;
13 g_tab_drtn_hours g_number_tab_type;
14 g_tab_start_ind g_number_tab_type;
15 g_tab_end_ind g_number_tab_type;
16 g_tab_ntfctn_blnc g_number_tab_type;
17 g_tab_abs_prsn_id g_number_tab_type;
18 g_tab_index PLS_INTEGER;
19
20 -- End of time
21 g_end_of_time DATE := hr_general.end_of_time;
22
23 -- Global HRI Multithreading Array
24 g_mthd_action_array HRI_ADM_MTHD_ACTIONS%rowtype;
25
26 -- Rounding constant
27 g_rounding NUMBER := 15;
28
29 -- Global parameters
30 g_refresh_start_date DATE;
31 g_full_refresh VARCHAR2(30);
32 g_dbi_collection_start_date DATE;
33 g_sysdate DATE;
34 g_user NUMBER;
35
36 -- ----------------------------------------------------------------------------
37 -- Runs given sql statement dynamically
38 -- ----------------------------------------------------------------------------
39 PROCEDURE run_sql_stmt_noerr(p_sql_stmt VARCHAR2) IS
40
41 BEGIN
42
43 EXECUTE IMMEDIATE p_sql_stmt;
44
45 EXCEPTION WHEN OTHERS THEN
46
47 null;
48
49 END run_sql_stmt_noerr;
50
51 -- ----------------------------------------------------------------------------
52 -- Sets global parameters from multi-threading process parameters
53 -- ----------------------------------------------------------------------------
54 PROCEDURE set_parameters(p_mthd_action_id IN NUMBER,
55 p_mthd_stage_code IN VARCHAR2) IS
56
57 BEGIN
58
59 -- If parameters haven't already been set, then set them
60 IF (g_refresh_start_date IS NULL OR
61 p_mthd_stage_code = 'PRE_PROCESS') THEN
62
63 g_dbi_collection_start_date :=
64 hri_oltp_conc_param.get_date_parameter_value
65 (p_parameter_name => 'FULL_REFRESH_FROM_DATE',
66 p_process_table_name => 'HRI_MB_UTL_ABSNC_CT');
67
68 -- If called for the first time set the defaulted parameters
69 IF (p_mthd_stage_code = 'PRE_PROCESS') THEN
70
71 g_full_refresh :=
72 hri_oltp_conc_param.get_parameter_value
73 (p_parameter_name => 'FULL_REFRESH',
74 p_process_table_name => 'HRI_MB_UTL_ABSNC_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 END set_parameters;
98
99 -- ----------------------------------------------------------------------------
100 -- Inserts a record into the global pl/sql table
101 -- ----------------------------------------------------------------------------
102 PROCEDURE insert_fact_record(p_abs_sk_fk IN NUMBER,
103 p_effective_date IN DATE,
104 p_drtn_days IN NUMBER,
105 p_drtn_hours IN NUMBER,
106 p_start_ind IN NUMBER,
107 p_end_ind IN NUMBER,
108 p_ntfctn_blnc IN NUMBER,
109 p_abs_prsn_id IN NUMBER) IS
110
111 BEGIN
112
113 -- Increment index and store new row
114 g_tab_index := g_tab_index + 1;
115 g_tab_abs_sk_fk(g_tab_index) := p_abs_sk_fk;
116 g_tab_effective_date(g_tab_index) := p_effective_date;
117 g_tab_drtn_days(g_tab_index) := p_drtn_days;
118 g_tab_drtn_hours(g_tab_index) := p_drtn_hours;
119 g_tab_start_ind(g_tab_index) := p_start_ind;
120 g_tab_end_ind(g_tab_index) := p_end_ind;
121 g_tab_ntfctn_blnc(g_tab_index) := p_ntfctn_blnc;
122 g_tab_abs_prsn_id(g_tab_index) := p_abs_prsn_id;
123
124 END insert_fact_record;
125
126 -- ----------------------------------------------------------------------------
127 -- Bulk inserts from pl/sql table to database table
128 -- ----------------------------------------------------------------------------
129 PROCEDURE bulk_insert_rows IS
130
131 BEGIN
132
133 -- Set constants
134 g_sysdate := sysdate;
135 g_user := fnd_global.user_id;
136
137 -- Bulk insert rows if any exist
138 IF g_tab_index > 0 THEN
139
140 FORALL i IN 1..g_tab_index
141 INSERT INTO hri_mb_utl_absnc_ct
142 (absence_sk_fk
143 ,effective_date
144 ,abs_drtn_days
145 ,abs_drtn_hrs
146 ,abs_blnc_ind
147 ,abs_start_ind
148 ,abs_end_ind
149 ,abs_ntfctn_days_blnc
150 ,abs_person_id
151 ,last_update_date
152 ,last_updated_by
153 ,last_update_login
154 ,created_by
155 ,creation_date)
156 VALUES
157 (g_tab_abs_sk_fk(i),
158 g_tab_effective_date(i),
159 g_tab_drtn_days(i),
160 g_tab_drtn_hours(i),
161 1,
162 g_tab_start_ind(i),
163 g_tab_end_ind(i),
164 g_tab_ntfctn_blnc(i),
165 g_tab_abs_prsn_id(i),
166 g_sysdate,
167 g_user,
168 g_user,
169 g_user,
170 g_sysdate);
171
172 -- Commit
173 COMMIT;
174
175 END IF;
176
177 -- Reset index
178 g_tab_index := 0;
179
180 END bulk_insert_rows;
181
182 -- -----------------------------------------------------------------------------
183 -- Processes either:
184 -- FULL REFRESH - All absences for a person within collection range
185 -- INCR REFRESH - All absences within given range of absence ids
186 --
187 -- The corresponding input parameters should be set, either
188 -- p_person_id - for full refresh
189 -- OR
190 -- p_start/end_abs_id - for incr refresh
191 -- NOT BOTH
192 --
193 -- Pushing down of day into absence occurs in PL/SQL to reduce buffer reads
194 -- -----------------------------------------------------------------------------
195 PROCEDURE process_set(p_person_id IN NUMBER,
196 p_start_abs_id IN NUMBER,
197 p_end_abs_id IN NUMBER) IS
198
199 -- Absence details per person
200 CURSOR full_absence_csr IS
201 SELECT
202 dim.absence_sk_pk absence_sk_fk
203 ,GREATEST(dim.abs_start_date, g_dbi_collection_start_date)
204 start_date
205 ,LEAST(NVL(dim.abs_end_date, TRUNC(SYSDATE)),
206 TRUNC(SYSDATE)) end_date
207 ,dim.abs_start_date start_date_actual
208 ,dim.abs_end_date end_date_actual
209 ,ROUND(CASE WHEN dim.abs_end_date = g_end_of_time
210 THEN dim.abs_drtn_days / (trunc(sysdate) - dim.abs_start_date + 1)
211 ELSE dim.abs_drtn_days / (dim.abs_end_date - dim.abs_start_date + 1)
212 END, g_rounding) abs_drtn_days
213 ,ROUND(CASE WHEN dim.abs_end_date = g_end_of_time
214 THEN dim.abs_drtn_hrs / (trunc(sysdate) - dim.abs_start_date + 1)
215 ELSE dim.abs_drtn_hrs / (dim.abs_end_date - dim.abs_start_date + 1)
216 END, g_rounding) abs_drtn_hrs
217 ,CASE WHEN (dim.abs_start_date < dim.abs_notification_date)
218 THEN 0
219 ELSE dim.abs_start_date - dim.abs_notification_date
220 END abs_ntfctn_days_blnc
221 ,dim.abs_person_id abs_person_id
222 FROM
223 hri_cs_absence_ct dim
224 WHERE dim.abs_person_id = p_person_id
225 AND dim.abs_start_date <= trunc(sysdate);
226
227 -- Absence details per absence range
228 CURSOR incr_absence_csr IS
229 SELECT
230 dim.absence_sk_pk absence_sk_fk
231 ,GREATEST(dim.abs_start_date, g_dbi_collection_start_date)
232 start_date
233 ,LEAST(NVL(dim.abs_end_date, TRUNC(SYSDATE)),
234 TRUNC(SYSDATE)) end_date
235 ,dim.abs_start_date start_date_actual
236 ,dim.abs_end_date end_date_actual
237 ,ROUND(CASE WHEN dim.abs_end_date = g_end_of_time
238 THEN dim.abs_drtn_days / (trunc(sysdate) - dim.abs_start_date + 1)
239 ELSE dim.abs_drtn_days / (dim.abs_end_date - dim.abs_start_date + 1)
240 END, g_rounding) abs_drtn_days
241 ,ROUND(CASE WHEN dim.abs_end_date = g_end_of_time
242 THEN dim.abs_drtn_hrs / (trunc(sysdate) - dim.abs_start_date + 1)
243 ELSE dim.abs_drtn_hrs / (dim.abs_end_date - dim.abs_start_date + 1)
244 END, g_rounding) abs_drtn_hrs
245 ,CASE WHEN (dim.abs_start_date < dim.abs_notification_date)
246 THEN 0
247 ELSE dim.abs_start_date - dim.abs_notification_date
248 END abs_ntfctn_days_blnc
249 ,dim.abs_person_id abs_person_id
250 FROM
251 hri_cs_absence_ct dim
252 ,hri_eq_utl_absnc_fact eq
253 WHERE dim.absence_sk_pk = eq.absence_sk_fk
254 AND eq.absence_sk_fk BETWEEN p_start_abs_id
255 AND p_end_abs_id
256 AND dim.abs_start_date <= trunc(sysdate);
257
258 -- Number of days spanned by absences
259 l_abs_length NUMBER;
260
261 -- Indicators
262 l_abs_start_ind PLS_INTEGER;
263 l_abs_end_ind PLS_INTEGER;
264
265 -- PL/SQL table for cursor fetch
266 l_abs_sk_fk g_number_tab_type;
267 l_abs_start_date g_date_tab_type;
268 l_abs_end_date g_date_tab_type;
269 l_abs_start_date_act g_date_tab_type;
270 l_abs_end_date_act g_date_tab_type;
271 l_abs_drtn_days g_number_tab_type;
272 l_abs_drtn_hrs g_number_tab_type;
273 l_abs_ntfctn_blnc g_number_tab_type;
274 l_abs_person_id g_number_tab_type;
275
276 BEGIN
277
278 -- Split out full and incremental refresh
279 IF (p_person_id IS NOT NULL) THEN
280
281 -- Fetch records from full refresh cursor
282 OPEN full_absence_csr;
283 FETCH full_absence_csr BULK COLLECT INTO
284 l_abs_sk_fk,
285 l_abs_start_date,
286 l_abs_end_date,
287 l_abs_start_date_act,
288 l_abs_end_date_act,
289 l_abs_drtn_days,
290 l_abs_drtn_hrs,
291 l_abs_ntfctn_blnc,
292 l_abs_person_id;
293 CLOSE full_absence_csr;
294
295 ELSE
296
297 -- Fetch records from incremental refresh cursor
298 OPEN incr_absence_csr;
299 FETCH incr_absence_csr BULK COLLECT INTO
300 l_abs_sk_fk,
301 l_abs_start_date,
302 l_abs_end_date,
303 l_abs_start_date_act,
304 l_abs_end_date_act,
305 l_abs_drtn_days,
306 l_abs_drtn_hrs,
307 l_abs_ntfctn_blnc,
308 l_abs_person_id;
309 CLOSE incr_absence_csr;
310
311 END IF;
312
313 -- Check if rows were returned by cursor
314 IF (l_abs_sk_fk.exists(1)) THEN
315
316 -- Loop through absences in range
317 FOR i IN l_abs_sk_fk.FIRST..l_abs_sk_fk.LAST LOOP
318
319 -- How many days spanned by absence
320 l_abs_length := l_abs_end_date(i) - l_abs_start_date(i) + 1;
321
322 -- Loop through days in each absence
323 FOR j IN 1..l_abs_length LOOP
324
325 -- Set Indicators
326 -- --------------
327 -- Set/reset indicators to 0
328 l_abs_start_ind := 0;
329 l_abs_end_ind := 0;
330
331 -- Check for start indicator
332 IF (j = 1) THEN
333 -- Start record on first day
334 IF (l_abs_start_date_act(i) = l_abs_start_date(i)) THEN
335 l_abs_start_ind := 1;
336 ELSE
337 l_abs_start_ind := 0;
338 END IF;
339 END IF;
340
341 -- Check for end indicator
342 IF (j = l_abs_length) THEN
343 -- End record on last day
344 IF (l_abs_end_date_act(i) = l_abs_end_date(i)) THEN
345 l_abs_end_ind := 1;
346 ELSE
347 l_abs_end_ind := 0;
348 END IF;
349 END IF;
350
351 -- Add fact row to PL/SQL table
352 insert_fact_record
353 (p_abs_sk_fk => l_abs_sk_fk(i),
354 p_effective_date => l_abs_start_date(i) + j - 1,
355 p_drtn_days => l_abs_drtn_days(i),
356 p_drtn_hours => l_abs_drtn_hrs(i),
357 p_start_ind => l_abs_start_ind,
358 p_end_ind => l_abs_end_ind,
359 p_ntfctn_blnc => l_abs_ntfctn_blnc(i),
360 p_abs_prsn_id => l_abs_person_id(i));
361
362 END LOOP;
363
364 END LOOP;
365
366 END IF;
367
368 -- Insert rows if a limit is reached
369 IF (g_tab_index > 2000) THEN
370 bulk_insert_rows;
371 END IF;
372
373 END process_set;
374
375 -- ----------------------------------------------------------------------------
376 -- PROCESS_RANGE for incremental refresh
377 -- ----------------------------------------------------------------------------
378 PROCEDURE process_range_incr(p_start_abs_id IN NUMBER,
379 p_end_abs_id IN NUMBER) IS
380
381 BEGIN
382
383 -- Delete old records
384 DELETE FROM hri_mb_utl_absnc_ct fact
385 WHERE fact.absence_sk_fk IN
386 (SELECT eq.absence_sk_fk
387 FROM hri_eq_utl_absnc_fact eq
388 WHERE eq.absence_sk_fk BETWEEN p_start_abs_id AND p_end_abs_id);
389
390 -- Reset PL/SQL tables
391 g_tab_index := 0;
392
393 -- Process set of absences in range
394 process_set
395 (p_person_id => to_number(null),
396 p_start_abs_id => p_start_abs_id,
397 p_end_abs_id => p_end_abs_id);
398
399 -- Insert rows
400 bulk_insert_rows;
401
402 END process_range_incr;
403
404 -- ----------------------------------------------------------------------------
405 -- PROCESS_RANGE for full refresh
406 -- ----------------------------------------------------------------------------
407 PROCEDURE process_range_full(p_start_psn_id IN NUMBER,
408 p_end_psn_id IN NUMBER) IS
409
410 -- Person in range
411 CURSOR person_csr IS
412 SELECT DISTINCT
413 abs_person_id
414 FROM hri_cs_absence_ct
415 WHERE abs_person_id BETWEEN p_start_psn_id AND p_end_psn_id;
416
417 BEGIN
418
419 -- Reset PL/SQL tables
420 g_tab_index := 0;
421
422 -- Loop through people in range
423 FOR person_rec IN person_csr LOOP
424
425 -- Process set of absences for each person
426 process_set
427 (p_person_id => person_rec.abs_person_id,
428 p_start_abs_id => to_number(null),
429 p_end_abs_id => to_number(null));
430
431 END LOOP;
432
433 -- Insert rows
434 bulk_insert_rows;
435
436 END process_range_full;
437
438 -- ----------------------------------------------------------------------------
439 -- PROCESS_RANGE
440 -- This procedure includes the logic required for processing the assignments
441 -- which have been included in the range. It is dynamically invoked by the
442 -- multithreading child process. It manages the multithreading ranges.
443 -- ----------------------------------------------------------------------------
444 PROCEDURE process_range(errbuf OUT NOCOPY VARCHAR2
445 ,retcode OUT NOCOPY NUMBER
446 ,p_mthd_action_id IN NUMBER
447 ,p_mthd_range_id IN NUMBER
448 ,p_start_object_id IN NUMBER
449 ,p_end_object_id IN NUMBER) IS
450
451 BEGIN
452
453 -- Set the parameters
454 set_parameters
455 (p_mthd_action_id => p_mthd_action_id,
456 p_mthd_stage_code => 'PROCESS_RANGE');
457
458 -- Process range in corresponding refresh mode
459 IF g_full_refresh = 'Y' THEN
460 process_range_full
461 (p_start_psn_id => p_start_object_id,
462 p_end_psn_id => p_end_object_id);
463 ELSE
464 process_range_incr
465 (p_start_abs_id => p_start_object_id,
466 p_end_abs_id => p_end_object_id);
467 END IF;
468
469 END process_range;
470
471 -- ----------------------------------------------------------------------------
472 -- Pre process entry point
473 -- ----------------------------------------------------------------------------
474 PROCEDURE pre_process(p_mthd_action_id IN NUMBER,
475 p_sqlstr OUT NOCOPY VARCHAR2) IS
476
477 l_sql_stmt VARCHAR2(2000);
478 l_dummy1 VARCHAR2(2000);
479 l_dummy2 VARCHAR2(2000);
480 l_schema VARCHAR2(400);
481
482 BEGIN
483
484 -- Set parameter globals
485 set_parameters
486 (p_mthd_action_id => p_mthd_action_id,
487 p_mthd_stage_code => 'PRE_PROCESS');
488
489 -- Get HRI schema name - get_app_info populates l_schema
490 IF fnd_installation.get_app_info('HRI',l_dummy1, l_dummy2, l_schema) THEN
491 null;
492 END IF;
493
494 -- Disable WHO trigger
495 run_sql_stmt_noerr('ALTER TRIGGER HRI_MB_UTL_ABSNC_CT_WHO DISABLE');
496
497 -- ********************
498 -- Full Refresh Section
499 -- ********************
500 IF (g_full_refresh = 'Y' OR
501 g_mthd_action_array.foundation_hr_flag = 'Y') THEN
502
503 -- Empty out absence fact table
504 l_sql_stmt := 'TRUNCATE TABLE ' || l_schema || '.HRI_MB_UTL_ABSNC_CT';
505 EXECUTE IMMEDIATE(l_sql_stmt);
506
507 -- In shared HR mode do not return a SQL statement so that the
508 -- process_range and post_process will not be executed
509 IF (g_mthd_action_array.foundation_hr_flag = 'Y') THEN
510
511 -- Call post processing API
512 post_process
513 (p_mthd_action_id => p_mthd_action_id);
514
515 ELSE
516
517 -- Drop indexes
518 hri_utl_ddl.log_and_drop_indexes
519 (p_application_short_name => 'HRI',
520 p_table_name => 'HRI_MB_UTL_ABSNC_CT',
521 p_table_owner => l_schema);
522
523 -- Set the SQL statement for the entire range
524 p_sqlstr :=
525 'SELECT /*+ PARALLEL(abs, DEFAULT, DEFAULT) */ DISTINCT
526 abs.abs_person_id object_id
527 FROM hri_cs_absence_ct abs
528 ORDER BY abs.abs_person_id';
529
530 END IF;
531
532 ELSE
533
534 -- Set the SQL statement for the incremental range
535 p_sqlstr :=
536 'SELECT /*+ PARALLEL(eq, DEFAULT, DEFAULT) */
537 absence_sk_fk object_id
538 FROM hri_eq_utl_absnc_fact eq
539 ORDER BY absence_sk_fk';
540
541 END IF;
542
543 END pre_process;
544
545 -- ----------------------------------------------------------------------------
546 -- Post process entry point
547 -- ----------------------------------------------------------------------------
548 PROCEDURE post_process(p_mthd_action_id NUMBER) IS
549
550 l_sql_stmt VARCHAR2(2000);
551 l_dummy1 VARCHAR2(2000);
552 l_dummy2 VARCHAR2(2000);
553 l_schema VARCHAR2(400);
554
555 BEGIN
556
557 -- Check parameters are set
558 set_parameters
559 (p_mthd_action_id => p_mthd_action_id,
560 p_mthd_stage_code => 'POST_PROCESS');
561
562 IF (p_mthd_action_id > -1) THEN
563
564 -- Log process end
565 hri_bpl_conc_log.record_process_start('HRI_MB_UTL_ABSNC_CT');
566 hri_bpl_conc_log.log_process_end(
567 p_status => TRUE
568 ,p_period_from => TRUNC(g_refresh_start_date)
569 ,p_period_to => TRUNC(SYSDATE)
570 ,p_attribute1 => g_full_refresh);
571
572 END IF;
573
574 -- Enable WHO trigger
575 run_sql_stmt_noerr('ALTER TRIGGER HRI_MB_UTL_ABSNC_CT_WHO ENABLE');
576
577 -- Get HRI schema name - get_app_info populates l_schema
578 IF fnd_installation.get_app_info('HRI',l_dummy1, l_dummy2, l_schema) THEN
579 null;
580 END IF;
581
582 -- Recreate indexes
583 IF (g_full_refresh = 'Y') THEN
584 hri_utl_ddl.recreate_indexes
585 (p_application_short_name => 'HRI',
586 p_table_name => 'HRI_MB_UTL_ABSNC_CT',
587 p_table_owner => l_schema);
588 END IF;
589
590 -- Empty out absence dimension event queue
591 l_sql_stmt := 'TRUNCATE TABLE ' || l_schema || '.HRI_EQ_UTL_ABSNC_FACT';
592 EXECUTE IMMEDIATE(l_sql_stmt);
593
594 END post_process;
595
596 -- Populates table in a single thread
597 PROCEDURE single_thread_process(p_full_refresh_flag IN VARCHAR2) IS
598
599 l_end_abs_id NUMBER;
600 l_end_psn_id NUMBER;
601 l_dummy VARCHAR2(32000);
602 l_from_date DATE := hri_bpl_parameter.get_bis_global_start_date;
603
604 BEGIN
605
606 -- get max assignment id
607 SELECT max(person_id) INTO l_end_psn_id
608 FROM per_all_people_f;
609 SELECT max(absence_attendance_id) INTO l_end_abs_id
610 FROM per_absence_attendances;
611
612 -- Set globals
613 g_full_refresh := p_full_refresh_flag;
614 g_refresh_start_date := l_from_date;
615 g_dbi_collection_start_date := l_from_date;
616 g_end_of_time := hr_general.end_of_time;
617 l_dummy := 'HRI';
618
619 -- Truncate table
620 EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || l_dummy || '.hri_mb_utl_absnc_ct';
621
622 -- Process range
623 IF (p_full_refresh_flag = 'Y') THEN
624 process_range_full(0, l_end_psn_id);
625 ELSE
626 process_range_incr(0, l_end_abs_id);
627 END IF;
628
629 -- Truncate table
630 EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || l_dummy || '.hri_eq_utl_absnc_fact';
631
632 END single_thread_process;
633
634 END hri_opl_utl_absnc_fact;