1 PACKAGE BODY HRI_OPL_EVENT_CAPTURE AS
2 /* $Header: hrioetcp.pkb 120.9 2006/12/06 11:02:14 jtitmas noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 --
6 -- Overview
7 -- ========
8 -- This purpose of this package is to interogate the PEM architecture in a
9 -- single pass, to glean details of all the events that have occurred that are
10 -- relevant to various HRMSi DBI base collections.
11 --
12 -- A single record per assignment, is then stored in the appropriate event
13 -- queue table for a given collection, for each collection that is interested
14 -- in the event.
15 --
16 -- Example
17 -- ~~~~~~~
18 -- For example a supervisor change maybe of interest to the supervisor hierachy
19 -- collection process, the assignments events fact collection, and the
20 -- supervisor status history collection. In this case a row will be maintained
21 -- in each of the queue tables for these object's assignment change.
22 --
23 -- Maintaining the Event Queues
24 -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~
25 -- Providing that there is no other change recorded for the assignment in the
26 -- queue already the earliest change for that assignment is simply
27 -- inserted into the qeueue. If there is a row in the queue that has an
28 -- earlier change date, then we will do nothing, otherwise if the change
29 -- pre-dates the record in the queue table, then we will update the record for
30 -- that assignment in the queue to set the change date to the earlier date.
31 --
32 -- Further Information
33 -- ===================
34 -- For details of the process flows in this package it is recomended that you
35 -- look at the detailed design using the following URL:
36 --
37 -- http://files.oraclecorp.com/content/AllPublic/Workspaces/
38 -- HRMS%20Intelligence%20(HRMSi)%20-%20Documents-Public/
39 -- Design%20Specifications/hri_lld_base_incremental_event_capture.doc
40 --
41 -- Process flow
42 -- ============
43 --
44 -- -----------------------------------------------------------------------------
45 -- Process flow
46 -- ============
47 --
48 -- BEFORE MULTI-THREADING
49 -- ----------------------
50 -- PRE_PROCESS
51 -- - Shared HR mode
52 -- - Call the bis process to insert a entry in the bis refresh log
53 -- for the process
54 -- - Full Refresh mode / Process Running for the first time
55 -- - Call the bis process to insert a entry in the bis refresh log
56 -- for the process
57 -- - When the event queue profiles are not set
58 -- - Call the bis process to insert a entry in the bis refresh log
59 -- for the process
60 -- - Incremental Mode
61 -- - Return the SQL based on which the range will be generated
62 --
63 -- PROCESS_RANGE
64 -- - Evaluate the events for every assignment in the range and
65 -- insert/update the records in the event queue
66 --
67 -- POST_PROCESS
68 -- - Call the bis process to insert a entry in the bis refresh log
69 -- for the process
70 --
71 -- -----------------------------------------------------------------------------
72 --
73 -- Start of global variable setup
74 -- ----------------------------------------------------------------------------
75 --
76 --
77 -- Global end of time date initialization from the package hr_general
78 --
79 g_end_of_time DATE := hr_general.end_of_time;
80 --
81 -- Global DBI collection start date initialization
82 --
83 g_dbi_collection_start_date DATE := hri_bpl_parameter.get_bis_global_start_date;
84 --
85 -- Global flag which determines whether debugging is turned on
86 --
87 g_debug_flag VARCHAR2(5) := NVL(fnd_profile.value('HRI_ENBL_DTL_LOG'),'N');
88 --
89 -- Global flag which determines whether archiving is turned on
90 --
91 --
92 g_enable_archive_flag VARCHAR2(5);
93 --
94 -- Global Variable to store the profile value for HRI:Populate Assignment Events Queue
95 --
96 g_col_asg_events_eq VARCHAR2(5);
97 --
98 -- Global Variable to store the profile value for HRI:Populate Supervisor Hierarchy Events Queue
99 --
100 g_col_sup_hrchy_eq VARCHAR2(5);
101 --
102 -- Global Variable to store the profile value for HRI:Populate Supervisor Status History Events Queue
103 --
104 g_col_sup_hstry_eq VARCHAR2(5);
105 --
106 -- Global Variable to store the profile value for 'HRI:Absence Dimension Queue' Events Queue
107 --
108 g_col_absence_events_eq VARCHAR2(5);
109 --
110 -- 3716747 Global Variable to store the date track id of the period of service table
111 --
112 g_prd_of_srvc_table_id NUMBER;
113 g_appraisal_table_id NUMBER;
114 g_perf_review_table_id NUMBER;
115 g_asg_table_id NUMBER;
116 g_person_type_table_id NUMBER;
117 g_absence_attendance_table_id NUMBER;
118 --
119 -- Gloabl variable to store the NLS Date format
120 --
121 g_date_format VARCHAR2(30);
122 --
123 -- Gloabl variable to store the Minimum date in Supervisor Hierarchy or
124 -- the collection from date for the last full refresh
125 --
126 g_min_suph_date DATE;
127 --
128 -- Global HRI Multithreading Array
129 --
130 g_mthd_action_array HRI_ADM_MTHD_ACTIONS%rowtype;
131 g_full_refresh VARCHAR2(30);
132 --
133 -- End of global variable setup
134 --
135 -- -----------------------------------------------------------------------------
136 --
137 --
138 -- Start of global constant setup
139 --
140 c_object_name VARCHAR2(30) := 'HRI_OPL_EVENT_CAPTURE';
141 c_ee_id NUMBER := -1;
142 --
143 -- End of global setting
144 --
145 PROCEDURE process_range(p_start_object_id IN NUMBER
146 ,p_end_object_id IN NUMBER ) ;
147 --
148 PROCEDURE Update_Asgn_Evnt_Fct_Evnt_Q
149 (p_assignment_id IN NUMBER
150 ,p_change_date IN DATE -- The effective change date
151 ,p_start_date IN DATE -- The date the events were captured from
152 );
153 --
154 -- ----------------------------------------------------------------------------
155 -- Inserts row into concurrent program log
156 -- ----------------------------------------------------------------------------
157 --
158 PROCEDURE msg(p_text VARCHAR2) IS
159 --
160 BEGIN
161 --
162 HRI_BPL_CONC_LOG.output(p_text);
163 --
164 END msg;
165 --
166 -- ----------------------------------------------------------------------------
167 -- Inserts row into concurrent program log if debugging is enabled
168 -- ----------------------------------------------------------------------------
169 --
170 PROCEDURE dbg(p_text VARCHAR2) IS
171 --
172 BEGIN
173 --
174 HRI_BPL_CONC_LOG.dbg(p_text);
175 --
176 END dbg;
177 --
178 -- 3716747 Get the date track table id for PER_PERIODS_OF_SERVICE
179 --
180 FUNCTION get_dated_table_id(p_table_name varchar2)
181 RETURN NUMBER
182 IS
183 --
184 CURSOR c_date_table_id (p_table_name varchar2) IS
185 SELECT dated_table_id
186 FROM pay_dated_tables
187 WHERE table_name = p_table_name;
188 --
189 l_table_id NUMBER;
190 --
191 BEGIN
192 --
193 OPEN c_date_table_id (p_table_name);
194 FETCH c_date_table_id into l_table_id;
195 CLOSE c_date_table_id;
196 --
197 RETURN l_table_id;
198 --
199 END get_dated_table_id;
200 --
201 -- 3716747 This function will be called if the change is to column
202 -- PER_PERIODS_OF_SERVICES.DATE_START. This means that the latest hire
203 -- data has been changed. The output should be least of the old and
204 -- new date. In case there is a formating error the effective_date of
205 -- the transaction will be returned
206 --
207 FUNCTION get_least_date(p_change_values IN VARCHAR2,
208 p_effective_date IN DATE)
209 RETURN DATE IS
210 --
211 CURSOR c_date_format_mast IS
212 SELECT VALUE
213 FROM V$PARAMETER
214 WHERE NAME = 'nls_date_format';
215 --
216 l_old_value DATE;
217 l_new_value DATE;
218 l_length_to_grab NUMBER;
219 l_effective_date DATE;
220 --
221 BEGIN
222 --
223 IF g_date_format is null THEN
224 --
225 OPEN c_date_format_mast;
226 FETCH c_date_format_mast into g_date_format;
227 CLOSE c_date_format_mast;
228 --
229 END IF;
230 --
231 -- The change_value column contains data in this format
232 -- 01-jan-03 -> 01-jan-03
233 -- compare the two dates and return the least
234 --
235 l_length_to_grab := (length(p_change_values) - 4)/2;
236 --
237 l_old_value := to_date(substr(p_change_values,0,l_length_to_grab),g_date_format);
238 --
239 l_new_value := to_date(substr(p_change_values,l_length_to_grab+4),g_date_format);
240 --
241 l_effective_date := least(l_old_value, l_new_value);
242 --
243 RETURN l_effective_date;
244 --
245 EXCEPTION
246 --
247 -- In case an error is raised in formating the date, do not raise an error
248 -- return the effective date on PEM record
249 --
250 WHEN others THEN
251 --
252 dbg('Exception Raised in determine change in latest hire date');
253 dbg(sqlerrm);
254 l_effective_date := p_effective_date;
255 --
256 RETURN l_effective_date;
257 --
258 END get_least_date;
259 --
260 --
261 -- Populate snapshot fact EQs with any new snapshot dates
262 --
263 PROCEDURE check_for_new_snapshot_dates IS
264
265 l_implement_obiee VARCHAR2(30);
266 l_implement_obiee_orgh VARCHAR2(30);
267 l_implement_obiee_mgrh VARCHAR2(30);
268
269 BEGIN
270
271 -- Set OBIEE parameters
272 IF (fnd_profile.value('HRI_IMPL_OBIEE') = 'Y') THEN
273 l_implement_obiee := 'Y';
274 IF (fnd_profile.value('HRI_COL_SUP_HRCHY_EQ') = 'Y') THEN
275 l_implement_obiee_mgrh := 'Y';
276 ELSE
277 l_implement_obiee_mgrh := 'N';
278 END IF;
279 IF (fnd_profile.value('HRI_COL_ORG_HRCHY_EQ') = 'Y') THEN
280 l_implement_obiee_orgh := 'Y';
281 ELSE
282 l_implement_obiee_orgh := 'N';
283 END IF;
284 ELSE
285 l_implement_obiee := 'N';
286 l_implement_obiee_mgrh := 'N';
287 l_implement_obiee_orgh := 'N';
288 END IF;
289
290 -- If OBIEE is implemented, check OBIEE facts
291 IF l_implement_obiee = 'Y' THEN
292
293 -- If a new month is reached since the last collection run
294 -- then update event queues
295 IF (trunc(g_capture_from_date,'MONTH') < trunc(sysdate, 'MONTH')) THEN
296
297 dbg('Found new month for OBIEE summaries');
298
299 EXECUTE IMMEDIATE 'alter session enable parallel dml';
300
301 commit;
302
303 -- Insert new snapshots into workforce events fact EQ
304 -- add assignments active between last refresh date and current month end
305 INSERT /*+ APPEND PARALLEL */ INTO hri_eq_wrkfc_mnth
306 (assignment_id
307 ,erlst_evnt_effective_date)
308 SELECT DISTINCT
309 asg_assgnmnt_fk
310 ,add_months(trunc(g_capture_from_date,'MONTH'), 1)
311 FROM
312 hri_mb_wrkfc_evt_ct
313 WHERE g_capture_from_date <= time_day_evt_end_fk
314 AND add_months(trunc(sysdate,'MONTH'), 1) > time_day_evt_fk
315 AND term_or_end_ind = 0;
316
317 commit;
318
319 -- Check manager hierarchy implemented
320 IF l_implement_obiee_mgrh = 'Y' THEN
321
322 -- Insert new snapshots into workforce manager summary EQ
323 -- Add managers active between last refresh date and current month end
324 INSERT /*+ APPEND PARALLEL */ INTO hri_eq_wrkfc_evt_mgrh
325 (sup_person_id
326 ,erlst_evnt_effective_date
327 ,source_code)
328 SELECT DISTINCT
329 mgrs_person_fk
330 ,add_months(trunc(g_capture_from_date,'MONTH'), 1)
331 ,'NEW_SNAP_DATE'
332 FROM
333 hri_cs_mngrsc_ct
334 WHERE g_capture_from_date <= mgrs_date_end
335 AND add_months(trunc(sysdate,'MONTH'), 1) > mgrs_date_start;
336
337 commit;
338
339 END IF;
340
341 -- Check organization hierarchy implemented
342 IF l_implement_obiee_orgh = 'Y' THEN
343
344 -- Insert new snapshots into workforce organization summary
345 -- Add one record per hierarchy node
346 INSERT /*+ APPEND PARALLEL */ INTO hri_eq_wrkfc_evt_orgh
347 (organization_id
348 ,erlst_evnt_effective_date)
349 SELECT
350 orgh_sup_organztn_fk
351 ,add_months(trunc(g_capture_from_date,'MONTH'), 1)
352 FROM
353 hri_cs_orgh_ct
354 WHERE orgh_relative_level = 0;
355
356 commit;
357
358 END IF;
359
360 END IF;
361
362 END IF;
363
364 END check_for_new_snapshot_dates;
365 --
366 --
367 --
368 FUNCTION get_min_suph_date
369 RETURN DATE
370 IS
371 --
372 l_min_suph_date DATE;
373 --
374 CURSOR c_last_full_suph_col_run IS
375 SELECT period_from
376 FROM bis_refresh_log
377 WHERE object_name = 'HRI_CS_SUPH'
378 AND status='SUCCESS'
379 AND attribute1 = 'Y'
380 AND last_update_date =( SELECT max(last_update_date)
381 FROM bis_refresh_log
382 WHERE object_name= 'HRI_CS_SUPH'
383 AND status='SUCCESS'
384 AND attribute1 = 'Y')
385 ORDER BY last_update_date DESC;
386 --
387 CURSOR c_min_suph_date IS
388 SELECT min(effective_start_date)
389 FROM hri_cs_suph;
390 --
391 BEGIN
392 --
393 -- 3906029 The event date to be populated in the supervisor hierarchy
394 -- events queue should not be lesser than the last full refresh date
395 -- or the minimum date in the hierarchy
396 --
397 OPEN c_last_full_suph_col_run;
398 FETCH c_last_full_suph_col_run INTO l_min_suph_date;
399 CLOSE c_last_full_suph_col_run ;
400 --
401 dbg('last collection date='|| l_min_suph_date );
402 --
403 -- In case the collection start date for last full refresh could not
404 -- be found from bis_refresh table, get the data from supervisor hierarchy
405 -- table.
406 --
407 IF g_min_suph_date is null THEN
408 --
409 OPEN c_min_suph_date;
410 FETCH c_min_suph_date INTO l_min_suph_date;
411 CLOSE c_min_suph_date;
412 --
413 END IF;
414 --
415 RETURN NVL(l_min_suph_date,g_dbi_collection_start_date);
416 --
417 END get_min_suph_date;
418 --
419 -- ----------------------------------------------------------------------------
420 -- 3829100 When an event occurs due to changes to PER_PERSON_TYPE_USAGES
421 -- the events can is useful to DBI only if the change is made to a record with
422 -- EMP on CWK system person type, otherwise the event is of no use for DBI
423 -- ----------------------------------------------------------------------------
424 --
425 FUNCTION valid_for_dbi_ptu_rec(p_person_type_usage_id NUMBER
426 ,p_effective_date DATE)
427 RETURN BOOLEAN
428 IS
429 --
430 CURSOR c_ptu IS
431 SELECT 1
432 FROM per_person_type_usages_f ptu,
433 per_person_types ppt
434 WHERE 1=1
435 AND ptu.person_type_usage_id = p_person_type_usage_id
436 AND p_effective_date BETWEEN ptu.effective_start_date and ptu.effective_end_date
437 AND ptu.person_type_id = ppt.person_type_id
438 AND ppt.system_person_type in ('EMP','CWK');
439 --
440 l_dummy NUMBER;
441 --
442 BEGIN
443 --
444 -- Open the cursor to determine if the change has been made to a EMP or CWK
445 --
446 OPEN c_ptu;
447 FETCH c_ptu INTO l_dummy;
448 CLOSE c_ptu;
449 --
450 IF l_dummy = 1 THEN
451 --
452 -- Change made to EMP or CWK person type so return true
453 --
454 dbg('emp asg change so return true');
455 RETURN TRUE;
456 --
457 ELSE
458 --
459 -- Change not made to EMP or CWK person type so return false
460 --
461 dbg('not a emp asg change so return false');
462 RETURN FALSE;
463 --
464 END IF;
465 --
466 END valid_for_dbi_ptu_rec;
467 --
468 -- ----------------------------------------------------------------------------
469 -- 4469175 The function is invoked when the projected end date is changed or when there
470 -- is a change in primary asg for a person. The function re-evaluates the extension start
471 -- date and returns the value. Additionally the function also creates event records for
472 -- other assignment records for the person to ensure that the extension date is
473 -- correctly set
474 -- ----------------------------------------------------------------------------
475 --
476 FUNCTION get_extnsn_strt_dt(
477 p_assignment_id IN NUMBER,
478 p_effective_date IN DATE)
479 RETURN DATE IS
480 --
481 l_person_id NUMBER;
482 l_extnsn_strt_dt DATE;
483 --
484 -- Cursor to get the earlierst extn date from asg events.
485 -- Note: extension starts one day after the projected assignment end specified
486 -- in the assignment record
487 --
488 -- Bug 4533293 - return event date (using NVL) if no projected end date exists in
489 -- per_all_assignments_f but does exist in hri_mb_asgn_events_ct
490 --
491 CURSOR c_extsn_strt_dt IS
492 SELECT asgn.person_id, NVL(min(asgn.projected_assignment_end) + 1, p_effective_date)
493 FROM per_all_assignments_f asgn
494 WHERE primary_flag = 'Y'
495 --
496 -- For people with multiple placement (rehire), the extsn date specified during
497 -- a particular term should only be considered for evaluating the extsn date
498 --
499 AND (person_id,period_of_placement_date_start) =
500 ( SELECT asgn.person_id , asgn.period_of_placement_date_start
501 FROM per_all_assignments_f asgn
502 WHERE asgn.assignment_id = p_assignment_id
503 AND rownum = 1)
504 GROUP BY asgn.person_id
505 --
506 -- The cursor should only return a record is the extension date in asg event
507 -- is not equal to the min date in the asg table
508 -- Bug 4533293 - changed subquery to use NVLs so that cursor will return a
509 -- record in the null case i.e. no previous extension existed
510 -- or previous extension removed
511 --
512 HAVING NVL(min(asgn.projected_assignment_end), g_end_of_time) <>
513 (SELECT NVL(MIN(asg.pow_extn_strt_dt) - 1, g_end_of_time)
514 FROM hri_mb_asgn_events_ct asg
515 WHERE asg.assignment_id = p_assignment_id);
516 --
517 CURSOR c_othr_prmry_asg IS
518 SELECT assignment_id,
519 least(min(asgn.effective_change_date),l_extnsn_strt_dt) change_date
520 FROM hri_mb_asgn_events_ct asgn
521 WHERE asgn.person_id = l_person_id
522 AND pow_extn_strt_dt is not null
523 AND asgn.pow_extn_strt_dt <> l_extnsn_strt_dt
524 AND assignment_id <> p_assignment_id
525 GROUP BY asgn.assignment_id;
526 --
527 BEGIN
528 --
529 dbg('Inside get_least_projected_end_date');
530 --
531 OPEN c_extsn_strt_dt;
532 FETCH c_extsn_strt_dt INTO l_person_id,l_extnsn_strt_dt;
533 CLOSE c_extsn_strt_dt;
534 --
535 -- The event should be generated for all assignment for the person as it is
536 -- likely that asg event records for the person have some incorrect data
537 --
538 IF l_extnsn_strt_dt is not null THEN
539 --
540 FOR l_asg IN c_othr_prmry_asg LOOP
541 --
542 dbg('creating event for asg = '|| l_asg.assignment_id||
543 ' as the extnsn date is changed to'||l_extnsn_strt_dt);
544 --
545 Update_Asgn_Evnt_Fct_Evnt_Q
546 (p_assignment_id => l_asg.assignment_id
547 ,p_change_date => l_asg.change_date
548 ,p_start_date => l_asg.change_date
549 );
550 --
551 END LOOP;
552 --
553 END IF;
554 --
555 dbg('the extension start date is '||l_extnsn_strt_dt);
556 --
557 RETURN l_extnsn_strt_dt;
558 --
559 EXCEPTION
560 --
561 -- In case an error is raised in formating the date, do not raise an error
562 -- return the effective date on PEM record
563 --
564 WHEN others THEN
565 --
566 dbg('Exception Raised in get_least_projected_end_date');
567 dbg(sqlerrm);
568 --
569 RETURN p_effective_date;
570 --
571 END get_extnsn_strt_dt;
572 --
573 -- 3906029, In case of termination events for the supervisor hierarchy
574 -- the date retunred should not be PRE_PERIODS_OF_SERVICE.DATE_START. It should
575 -- be termination_date + 1. This will prevent the hierarchy from recollecting
576 -- the hierarchy from start date for the person. However, in case of rehire
577 -- the effective date should be PRE_PERIODS_OF_SERVICE.DATE_START
578 --
579 --
580 FUNCTION get_changed_termination_date(p_change_values IN VARCHAR2,
581 p_effective_date IN DATE)
582 RETURN DATE IS
583 --
584 CURSOR c_date_format_mast IS
585 SELECT VALUE
586 FROM V$PARAMETER
587 WHERE NAME = 'nls_date_format';
588 --
589 l_old_value DATE;
590 l_new_value DATE;
591 l_length_to_grab NUMBER;
592 l_effective_date DATE;
593 --
594 BEGIN
595 --
596 IF g_date_format is null THEN
597 --
598 OPEN c_date_format_mast;
599 FETCH c_date_format_mast into g_date_format;
600 CLOSE c_date_format_mast;
601 --
602 END IF;
603 --
604 -- The following changes can be done to actual termination and final
605 -- process date columns
606 -- A date can be assigned which means that the person is terminated
607 -- The date can be changed
608 -- In case of rehire the date will be removed.
609 -- Based of these the change_value column can contains data in the
610 -- following formats
611 -- 01-jan-03 -> 01-jan-04 Change is termination date
612 -- <null> -> 11-OCT-04 Termination
613 -- 31-OCT-04 -> <null> Rehire
614 -- Find out the exact format and pass out the values
615 --
616 IF instr(p_change_values,'<null>') = 0 THEN
617 --
618 -- The format is 01-jan-03 -> 01-jan-04
619 -- So the person's termination date has changed
620 --
621 l_length_to_grab := (length(p_change_values) - 4)/2;
622 --
623 l_old_value := to_date(substr(p_change_values,0,l_length_to_grab),g_date_format);
624 --
625 l_new_value := to_date(substr(p_change_values,l_length_to_grab+4),g_date_format);
626 --
627 l_effective_date := least(l_old_value, l_new_value);
628 --
629 ELSIF instr(p_change_values,'<null>') = 1 THEN
630 --
631 -- The format is <null> -> 11-OCT-04
632 -- So the person has been terminated
633 -- A person termination one day after the end date of the record, so add 1
634 l_effective_date := to_date(substr(p_change_values,11),g_date_format) + 1;
635 --
636 ELSE
637 --
638 -- The format is 31-OCT-04 -> <null>
639 -- So the person has been rehired, create the event as of the
640 -- effective date
641 --
642 l_effective_date := p_effective_date;
643 --
644 END IF;
645 --
646 dbg('the termination event date is '||l_effective_date);
647 RETURN l_effective_date;
648 --
649 EXCEPTION
650 --
651 -- In case an error is raised in formating the date, do not raise an error
652 -- return the effective date on PEM record
653 --
654 WHEN others THEN
655 --
656 dbg('Exception Raised in determine termination date, returning effective date');
657 dbg(sqlerrm);
658 l_effective_date := p_effective_date;
659 --
660 RETURN l_effective_date;
661 --
662 END get_changed_termination_date;
663 --
664 -- ----------------------------------------------------------------------------
665 -- The function evaluates the dates for all one off cases, and adjust the
666 -- effective date so that the event is created on a correct date
667 -- ----------------------------------------------------------------------------
668 --
669 FUNCTION eval_one_off_cases
670 (p_sub_evt_grp_tbl pay_interpreter_pkg.t_detailed_output_tab_rec
671 ,p_assignment_id NUMBER
672 ,p_comment_text VARCHAR2
673 ,p_effective_date DATE)
674 RETURN DATE IS
675 --
676 l_effective_date DATE := p_effective_date;
677 l_extns_date DATE;
678 --
679 BEGIN
680 --
681 -- 3716747
682 -- If a person's latest hire date is changed to a future date,
683 -- e.g. from 01-jan-2004 to 15-Jan-2004, the effective date of the transaction
684 -- return by PEM is 15-Jan-2004. It's likely that the person's data in
685 -- base collection table start from the old hire date (01-jan-2004). If the
686 -- event created due to change in latest hire date is created on 15-jan-2004
687 -- the data in base table will not be correct and this will result is errors
688 -- on DBI pages. Therefore, if the event has been caused due to change in person's
689 -- hire date PRE_PERIODS_OF_SERVICE.DATE_START then event date should be set the least
690 -- of the old-new hire dates i.e. 01-jan-2004 in this case. This will ensure that old
691 -- data for the assignment is delete by the collection processes.
692 -- 3952026, extended the case to change in effective date of other non datetrack tables
693 -- per_appraisals and per_periods_of_service
694 -- 3170971, extending the case to per_all_assignments_f, In case of cancel hire of an
695 -- applicant, the previously end dated applicant asg record is end dated to end of
696 -- time from current date and the asg record is deleted. However PEM will detect only
697 -- the update of effective_end_date column
698 --
699 IF (g_prd_of_srvc_table_id = p_sub_evt_grp_tbl.dated_table_id
700 AND p_sub_evt_grp_tbl.column_name = 'DATE_START')
701 OR ( g_appraisal_table_id = p_sub_evt_grp_tbl.dated_table_id
702 AND p_sub_evt_grp_tbl.column_name = 'APPRAISAL_DATE' )
703 OR ( g_perf_review_table_id = p_sub_evt_grp_tbl.dated_table_id
704 AND p_sub_evt_grp_tbl.column_name = 'REVIEW_DATE' )
705 OR ( g_asg_table_id = p_sub_evt_grp_tbl.dated_table_id
706 AND p_sub_evt_grp_tbl.column_name = 'EFFECTIVE_END_DATE' )
707 THEN
708 --
709 dbg('change detected in column '||p_sub_evt_grp_tbl.column_name ||' of table_id '||g_prd_of_srvc_table_id);
710 dbg('p_sub_evt_grp_tbl.change_values = '||p_sub_evt_grp_tbl.change_values);
711 --
712 l_effective_date := get_least_date(
713 p_change_values => p_sub_evt_grp_tbl.change_values,
714 p_effective_date => p_sub_evt_grp_tbl.effective_date);
715 --
716 --
717 -- Extension Start Date
718 -- 4469175 When the projected end date of the record is changed the event should
719 -- be created on the minimum of all projected end dates + 1 stored in the
720 -- primary asg records of the person. If the new date is not less than the
721 -- date in the system, a new event should be created for all other asg for the
722 -- person
723 --
724 ELSIF ( g_asg_table_id = p_sub_evt_grp_tbl.dated_table_id AND
725 p_comment_text <> 'Supervisor' AND
726 p_sub_evt_grp_tbl.column_name = 'PROJECTED_ASSIGNMENT_END' )
727 THEN
728 --
729 dbg('In period of extension change calculation');
730 --
731 l_effective_date := get_extnsn_strt_dt(
732 p_assignment_id => p_assignment_id,
733 p_effective_date => p_sub_evt_grp_tbl.effective_date);
734 --
735 --
736 -- 4469175
737 -- Change of primary assignment and extension calculation
738 -- In case the primary assignment for a record is changed the event date should
739 -- set as least of
740 -- A. Effective change date
741 -- B. The extension date for the person if it has been reset due to the change in
742 -- primary assignment
743 --
744 ELSIF ( g_asg_table_id = p_sub_evt_grp_tbl.dated_table_id AND
745 p_comment_text <> 'Supervisor' AND
746 p_sub_evt_grp_tbl.column_name = 'PRIMARY_FLAG' )
747 THEN
748 --
749 dbg('In change of primary assignment calculation');
750 --
751 l_extns_date := get_extnsn_strt_dt(
752 p_assignment_id => p_assignment_id,
753 p_effective_date => p_sub_evt_grp_tbl.effective_date);
754 --
755 l_effective_date := least(l_extns_date,p_sub_evt_grp_tbl.effective_date);
756 --
757 ELSIF g_person_type_table_id = p_sub_evt_grp_tbl.dated_table_id THEN
758 --
759 -- 3829100 If event is due to change to PER_PERSON_TYPE_USAGE_F, the event
760 -- affect DBI only when the change is made to PTU record with EMP, CWK
761 -- person type. The queues should be populated for such events
762 --
763 dbg('inside ptu check');
764 IF valid_for_dbi_ptu_rec(p_sub_evt_grp_tbl.surrogate_key,
765 p_sub_evt_grp_tbl.effective_date)
766 THEN
767 --
768 -- in case the the change is to EFFECTIVE_END_DATE column then
769 -- the effective_date should be set to least of the prev and current dates
770 --
771 IF p_sub_evt_grp_tbl.column_name = 'EFFECTIVE_END_DATE' THEN
772 --
773 l_effective_date := get_least_date(
774 p_change_values => p_sub_evt_grp_tbl.change_values,
775 p_effective_date => p_sub_evt_grp_tbl.effective_date);
776 --
777 END IF;
778 --
779 ELSE
780 --
781 l_effective_date := null;
782 --
783 END IF;
784 --
785 ELSIF p_comment_text = 'Supervisor'
786 AND g_prd_of_srvc_table_id = p_sub_evt_grp_tbl.dated_table_id
787 AND p_sub_evt_grp_tbl.column_name in ('ACTUAL_TERMINATION_DATE','FINAL_PROCESS_DATE' )
788 THEN
789 --
790 -- 3906029, In case of termination events for the supervisor hierarchy
791 -- the min date should not be PRE_PERIODS_OF_SERVICE.DATE_START. It should
792 -- be termination_date + 1. This will prevent the hierarchy from recollecting
793 -- the hierarchy from start date for the person. However, in case of rehire
794 -- the effective date should be PRE_PERIODS_OF_SERVICE.DATE_START
795 --
796 l_effective_date := get_changed_termination_date(
797 p_change_values => p_sub_evt_grp_tbl.change_values,
798 p_effective_date => p_sub_evt_grp_tbl.effective_date);
799 dbg('termination date = '||l_effective_date);
800 --
801 END IF;
802 --
803 dbg('effective date = '||l_effective_date);
804 --
805 RETURN l_effective_date;
806 --
807 END eval_one_off_cases;
808 --
809 -- ----------------------------------------------------------------------------
810 -- empty_evnts_cptr_refresh_log
811 -- Empty events Capture Refresh Log.
812 -- ============================================================================
813 -- This procedure truncates the BIS log information, for the events capture
814 -- process. This is useful for testing purposes, so that we can re-run tests
815 -- over given date ranges.
816 --
817 -- The logic in this procedure DOES NOT form part of the normal incremental
818 -- events capture process.
819 --
820 PROCEDURE empty_evnts_cptr_refresh_log
821 IS
822 --
823 BEGIN
824 --
825 hri_bpl_conc_log.delete_process_log(c_object_name);
826 --
827 END empty_evnts_cptr_refresh_log;
828 --
829 -- ----------------------------------------------------------------------------
830 -- truncate_table
831 -- The following procedure truncates the passed in table.
832 -- ============================================================================
833 --
834 PROCEDURE truncate_table(p_table_name VARCHAR2)
835 IS
836 --
837 l_sql_stmt VARCHAR2(200);
838 l_dummy1 VARCHAR2(1);
839 l_dummy2 VARCHAR2(1);
840 l_schema VARCHAR2(50);
841 --
842 BEGIN
843 --
844 IF NOT fnd_installation.get_app_info('HRI',l_dummy1, l_dummy2, l_schema)
845 THEN
846 --
847 RAISE schema_name_not_set;
848 --
849 END IF;
850 --
851 l_sql_stmt := 'TRUNCATE TABLE ' || l_schema || '.' || p_table_name;
852 --
853 dbg(l_sql_stmt);
854 --
855 EXECUTE IMMEDIATE(l_sql_stmt);
856 --
857 COMMIT;
858 --
859 dbg('Truncated table: '||p_table_name);
860 --
861 EXCEPTION
862 --
863 WHEN OTHERS
864 THEN
865 --
866 dbg('An error occurred while truncating table '||p_table_name);
867 --
868 -- Bug 4105868: Collection Diagnostic Call
869 --
870 hri_bpl_conc_log.log_process_info
871 (p_package_name => 'HRI_OPL_EVENT_CAPTURE'
872 ,p_msg_type => 'ERROR'
873 ,p_note => SQLERRM
874 ,p_msg_group => 'EVT_CPTR'
875 ,p_msg_sub_group => 'TRUNCATE_TABLE'
876 ,p_sql_err_code => SQLCODE);
877 --
878 RAISE;
879 --
880 --
881 END truncate_table;
882 --
883 -- ----------------------------------------------------------------------------
884 -- purge_queue
885 -- The following procedure purges the passed in queue table.
886 -- ============================================================================
887 --
888 PROCEDURE purge_queue(p_queue_table_name VARCHAR2)
889 IS
890 BEGIN
891 --
892 truncate_table(p_queue_table_name);
893 --
894 END purge_queue;
895 --
896 -- ----------------------------------------------------------------------------
897 -- Full Refresh
898 -- ============================================================================
899 -- This procedure does very little, other than to set the date that the events
900 -- capture process, needs to run from, subsequent to the full refresh.
901 -- The process also purges the event queues,as the data will no longer be
902 -- required after a full refresh.
903 --
904 PROCEDURE full_refresh (p_refresh_to_date IN DATE DEFAULT NULL)
905 IS
906 --
907 -- Date to set fo full refresh end date.
908 --
909 l_refresh_to_date DATE;
910 --
911 BEGIN
912 --
913 -- Record that the full refresh process has started.
914 --
915 hri_bpl_conc_log.record_process_start(c_object_name);
916 --
917 -- Set the date that the full refresh is refreshed to. Normally this will be
918 -- trunc SYSDATE, but for testing purposes, we may sometimes wish to set an
919 -- earlier date.
920 --
921 IF p_refresh_to_date IS NULL
922 THEN
923 --
924 dbg('Setting refresh date to TRUNC(SYSDATE).');
925 --
926 l_refresh_to_date := SYSDATE;
927 --
928 --
929 -- This logic path will only ever be used for testing. It will allow
930 -- full refresh to be run up to a specified date.
931 --
932 ELSE
933 --
934 dbg('Setting refresh date to p_refresh_to_date.');
935 --
936 l_refresh_to_date := p_refresh_to_date;
937 --
938 -- Purge the dbi collection log, so that we do not have any more recent
939 -- processes in the log.
940 --
941 empty_evnts_cptr_refresh_log;
942 --
943 END IF;
944 --
945 dbg('l_refresh_to_date: '||TO_CHAR(l_refresh_to_date));
946 --
947 -- Empty the Event Queues
948 --
949 -- Truncate archive table
950 truncate_table('HRI_ARCHIVE_EVENTS');
951 -- Purge supervisor hierarchy queue
952 purge_queue('HRI_EQ_SPRVSR_HRCHY_CHGS');
953 -- Purge supervisor history queue
954 purge_queue('HRI_EQ_SPRVSR_HSTRY_CHGS');
955 -- Purge assignment events queue
956 purge_queue('HRI_EQ_ASGN_EVNTS');
957 -- Purge absence events queue
958 purge_queue('HRI_EQ_UTL_ABSNC_DIM');
959 --
960 -- Record the full refresh process has ended. This is the main purpose of
961 -- full refresh, as these dates will then be used to drive the dates that
962 -- the events capture process is then run for.
963 --
964 --
965 commit;
966 --
967 hri_bpl_conc_log.log_process_end
968 (p_status => TRUE
969 ,p_period_from => g_dbi_collection_start_date
970 ,p_period_to => l_refresh_to_date);
971 --
972 commit;
973 --
974 END full_refresh;
975 --
976 -- ----------------------------------------------------------------------------
977 -- get_business_group_id
978 -- Gets the Business group ID for a given assignment_id
979 -- ============================================================================
980 --
981 FUNCTION get_business_group_id(p_assignment_id IN VARCHAR2)
982 RETURN NUMBER IS
983 --
984 CURSOR c_business_group(p_assignment_id IN VARCHAR2) IS
985 SELECT business_group_id
986 FROM per_all_assignments_f
987 WHERE assignment_id = p_assignment_id;
988 --
989 l_business_group_id NUMBER;
990 --
991 BEGIN
992 --
993 -- Get the event group id
994 --
995 OPEN c_business_group(p_assignment_id);
996 FETCH c_business_group INTO l_business_group_id;
997 IF c_business_group%NOTFOUND
998 THEN
999 --
1000 -- 3710454 There are certain cases when the business_group_id will not
1001 -- be available eg. when the assignment is deleted. In such a case
1002 -- exception should not be raised but the interpreter should be called without
1003 -- with out the business_group parameter
1004 --
1005 dbg('Business Group for assignment "'||p_assignment_id||'" not found.');
1006 --
1007 END IF;
1008 --
1009 CLOSE c_business_group;
1010 --
1011 RETURN l_business_group_id;
1012 --
1013 END get_business_group_id;
1014 --
1015 -- ----------------------------------------------------------------------------
1016 -- get_event_group_id
1017 -- Gets the event group ID based on its name.
1018 -- ============================================================================
1019 --
1020 FUNCTION get_event_group_id(p_event_group_name IN VARCHAR2)
1021 RETURN NUMBER IS
1022 --
1023 CURSOR get_evt(p_grp IN VARCHAR2) IS
1024 SELECT event_group_id
1025 FROM pay_event_groups
1026 WHERE event_group_name = p_grp;
1027 --
1028 l_event_group_id NUMBER;
1029 --
1030 BEGIN
1031 --
1032 dbg('Getting Event Group Id for '||p_event_group_name||' event group.');
1033 --
1034 -- Get the event group id
1035 --
1036 OPEN get_evt(p_event_group_name);
1037 FETCH get_evt INTO l_event_group_id;
1038 IF get_evt%NOTFOUND
1039 THEN
1040 --
1041 -- Trace some debug info and raise the error
1042 --
1043 dbg('Event group "'||p_event_group_name||'" not found.');
1044 --
1045 CLOSE get_evt;
1046 RAISE event_group_not_found;
1047 --
1048 END IF;
1049 --
1050 CLOSE get_evt;
1051 --
1052 dbg('Getting Event Group Id is '||l_event_group_id||'.');
1053 --
1054 RETURN l_event_group_id;
1055 --
1056 END get_event_group_id;
1057 --
1058 -- ----------------------------------------------------------------------------
1059 -- 5.1.1 (interpret_all_asgnmnt_changes)
1060 -- Get All Assignment Changes For The Master Event Group
1061 -- ============================================================================
1062 -- This procedure calls PEM interpreter package to populate a PLSQL table of
1063 -- all of the events that have occurred for assignments, where the event exists
1064 -- in the master event group. The master event group is an event group that
1065 -- contains all of the events contained in all of the sub event groups.
1066 -- The sub event groups contain only those events that relate to a given
1067 -- collection e.g. the supervisor hierarchy collection.
1068 --
1069 -- The concept of master event groups and sub event groups does not actually
1070 -- exist in PEM, so we need to make sure when we seed the sub event groups or
1071 -- change them, that the master event groups are also maintained.
1072 --
1073 PROCEDURE interpret_all_asgnmnt_changes
1074 (
1075 p_assignment_id IN NUMBER
1076 ,p_start_date IN DATE
1077 ,p_master_events_table
1078 IN OUT nocopy pay_interpreter_pkg.t_detailed_output_table_type
1079 )
1080 IS
1081 --
1082 -- The following 3 PLSQL tables are required as return placeholders for
1083 -- the procedure pay_interpreter_pkg.entry_affected. The results from
1084 -- these tables is CURRENTLY IGNORED.
1085 --
1086 l_proration_dates pay_interpreter_pkg.t_proration_dates_table_type;
1087 l_proration_changes pay_interpreter_pkg.t_proration_type_table_type;
1088 l_pro_type_tab pay_interpreter_pkg.t_proration_type_table_type;
1089 --
1090 -- The business group id of the assignent
1091 --
1092 l_business_group_id NUMBER;
1093 --
1094 BEGIN
1095 --
1096 dbg('Executing interpret_all_asgnmnt_changes ....');
1097 --
1098 -- Get the business group id for the assignment (p_assignment_id)
1099 -- this is not strictly necessary, but improves the performance
1100 -- of pay_interpreter_pkg.entry_affected
1101 --
1102 l_business_group_id := get_business_group_id(p_assignment_id);
1103 --
1104 -- Call the Payroll Events Model (PEM) interpreter to identify all
1105 -- of the events that have occurred for the assignment since p_start_date,
1106 -- in the master event group.
1107 --
1108 dbg('c_ee_id: '||c_ee_id);
1109 dbg('p_assignment_id: '||p_assignment_id);
1110 dbg('g_master_event_group_id: '||g_master_event_group_id);
1111 dbg('p_start_date: '||p_start_date);
1112 dbg('g_end_of_time: '||g_end_of_time);
1113 dbg('l_business_group_id: '||l_business_group_id);
1114 --
1115 IF l_business_group_id is not null THEN
1116 --
1117 pay_interpreter_pkg.entry_affected(
1118 p_element_entry_id => c_ee_id
1119 ,p_assignment_action_id => NULL
1120 ,p_assignment_id => p_assignment_id
1121 ,p_mode => NULL -- pickup events of all types including
1122 -- 'REPORTS' and 'DATE_PROCESSED'
1123 ,p_process => NULL
1124 ,p_event_group_id => g_master_event_group_id
1125 ,p_process_mode => 'ENTRY_CREATION_DATE'-- means I am
1126 -- interested in
1127 -- events created
1128 ,p_start_date => p_start_date -- between here
1129 ,p_end_date => g_end_of_time -- and here
1130 ,p_unique_sort => 'N' -- tells the interpreter not to do a
1131 -- unique sort, and this improves
1132 -- performance.
1133 ,p_business_group_id => l_business_group_id
1134 ,t_detailed_output => p_master_events_table --OUTPUT OF RESULTS
1135 ,t_proration_dates => l_proration_dates --IGNORED
1136 ,t_proration_change_type => l_proration_changes --IGNORED
1137 ,t_proration_type => l_pro_type_tab); --IGNORED
1138 --
1139 ELSE
1140 --
1141 -- 3710454 As business_group_id is not found for the assignment, call the
1142 -- interpreter version which does not take the business group parameters.
1143 -- Note: This version is inefficient, but we don't want to miss any events
1144 -- which has happened to an assignment
1145 --
1146 pay_interpreter_pkg.entry_affected(
1147 p_element_entry_id => c_ee_id
1148 ,p_assignment_action_id => NULL
1149 ,p_assignment_id => p_assignment_id
1150 ,p_mode => NULL
1151 ,p_process => NULL
1152 ,p_event_group_id => g_master_event_group_id
1153 ,p_process_mode => 'ENTRY_CREATION_DATE'
1154 ,p_start_date => p_start_date -- Events from
1155 ,p_end_date => g_end_of_time -- till here
1156 ,t_detailed_output => p_master_events_table -- OUTPUT OF RESULTS
1157 ,t_proration_dates => l_proration_dates -- IGNORED
1158 ,t_proration_change_type => l_proration_changes -- IGNORED
1159 ,t_proration_type => l_pro_type_tab); -- IGNORED
1160 --
1161 END IF;
1162 --
1163 dbg('Rows Returned: '||TO_CHAR(p_master_events_table.COUNT));
1164 --
1165 -- Loop through rows returned by the interpreter for dubug
1166 -- purposes only.
1167 --
1168 -- ONLY if debugging is on output log information
1169 --
1170 IF g_debug_flag = 'Y' THEN
1171 --
1172 FOR i in 1..p_master_events_table.COUNT
1173 LOOP
1174 --
1175 dbg(' dated_table_id: '||TO_CHAR(p_master_events_table(i).dated_table_id)
1176 ||', datetracked_event: '||p_master_events_table(i).datetracked_event
1177 ||', update_type: '||p_master_events_table(i).update_type
1178 ||', surrogate_key: '||TO_CHAR(p_master_events_table(i).surrogate_key)
1179 ||', column_name: '||TO_CHAR(p_master_events_table(i).column_name)
1180 ||', Effective_date: '||p_master_events_table(i).effective_date
1181 ||', old_value: '||p_master_events_table(i).old_value
1182 ||', new_value: '||p_master_events_table(i).new_value
1183 ||', change_values: '||p_master_events_table(i).change_values
1184 ||', proration_type: '||p_master_events_table(i).proration_type
1185 ||', change_mode: '||p_master_events_table(i).change_mode
1186 ||', element_entry_id: '||p_master_events_table(i).element_entry_id
1187 ||', next_ee: '||p_master_events_table(i).next_ee
1188 );
1189 --
1190 END LOOP;
1191 --
1192 END IF; -- end of debug condition
1193 --
1194 END interpret_all_asgnmnt_changes;
1195 --
1196 -- ----------------------------------------------------------------------------
1197 -- Update_archive_record
1198 -- Log details for the identified event
1199 -- ============================================================================
1200 -- Updates hri_archive_events with details of the action taken for
1201 -- the earliest event found for an assignent event.
1202 --
1203 PROCEDURE Update_archive_record
1204 (p_assignment_id IN NUMBER
1205 ,p_change_date IN DATE -- The effective change date
1206 ,p_event_queue_table IN VARCHAR2 -- The table name of the event queue we
1207 -- have identified the event for.
1208 ,p_action_taken IN VARCHAR2 -- The action taken with the event we
1209 -- have identified.
1210 ,p_capture_from_date IN DATE -- The date that the event queue was
1211 -- was using as a start date when the
1212 -- event was found.
1213 )
1214 IS
1215 --
1216 BEGIN
1217 --
1218 INSERT INTO hri_archive_events
1219 (
1220 assignment_id
1221 ,event_queue_table
1222 ,action_taken
1223 ,erlst_evnt_effective_date
1224 ,capture_from_date
1225 )
1226 VALUES
1227 (
1228 p_assignment_id
1229 ,p_event_queue_table
1230 ,p_action_taken
1231 ,p_change_date
1232 ,p_capture_from_date
1233 );
1234 --
1235 END Update_archive_record;
1236 --
1237 -- ----------------------------------------------------------------------------
1238 -- 5.1.2.2 (Update_Sprvsr_Hstry_Evnt_Q)
1239 -- Update the Supervisor Hierarchy Event Queue
1240 -- ============================================================================
1241 -- This procedure will for the given assignment_id and change date, update
1242 -- the event queue by either:
1243 --
1244 -- + Insert a new record in the event queue (if no record for that assignment
1245 -- exists for the assignment).
1246 -- + Update the existing record in the event queue for the assignment, if it
1247 -- exists, and has a later date than the new event you have found.
1248 -- + Do nothing to the event queue as there is already an early change record
1249 -- for the assignment.
1250 --
1251 -- The procedure will also insert arecord of the event in hri_archive_events
1252 -- for audit purposes to record the event capture and what was done to the
1253 -- event queue as a result of the event capture.
1254 --
1255 PROCEDURE Update_Sprvsr_Hstry_Evnt_Q
1256 (p_assignment_id IN NUMBER
1257 ,p_change_date IN DATE -- The effective change date
1258 ,p_start_date IN DATE -- The date the events were captured from
1259 )
1260 IS
1261 --
1262 -- Select the erlst_evnt_processed_date from the event queue
1263 -- for the assignment_id if it exists, so that we can decide
1264 -- whether we need to:
1265 --
1266 -- + Insert if there is no record for the assignment in the queue.
1267 -- + Update the queue if p_change_date is earlier than
1268 -- erlst_evnt_processed_date.
1269 -- + Do nothing.
1270 --
1271 CURSOR c_get_queued_event(cp_assignment_id IN NUMBER) IS
1272 SELECT erlst_evnt_effective_date
1273 FROM hri_eq_sprvsr_hstry_chgs
1274 WHERE assignment_id = cp_assignment_id;
1275 --
1276 l_erlst_evnt_effective_date DATE; -- Stores the earliest event data for
1277 -- the assignment currently stored
1278 -- in the event queue.
1279 --
1280 l_action_taken VARCHAR2(30); -- used to store what we do with the passed
1281 -- in event. This is used when inserting
1282 -- into hri_archive_events to show what action
1283 -- we took with the captured event.
1284 --
1285 BEGIN
1286 --
1287 dbg('Updating the Supervisor History Events Queue for '||p_assignment_id||'.');
1288 --
1289 -- Exit if HRI:Populate Supervisor Status History Events Queue is not enabled
1290 --
1291 IF g_col_sup_hstry_eq = 'N' THEN
1292 --
1293 dbg('Profile HRI:Populate Supervisor Status History Events Queue not enabled, '||
1294 'skip populating supervisor status history events queue');
1295 return;
1296 --
1297 END IF;
1298 --
1299 -- Get the earliest change date currently stored in the event queue
1300 -- (l_erlst_evnt_processed_date), where it exists.
1301 --
1302 OPEN c_get_queued_event(p_assignment_id);
1303 FETCH c_get_queued_event INTO l_erlst_evnt_effective_date;
1304 --
1305 -- If no record exists in the queue for the assignment, then we need to
1306 -- INSERT into the event queue.
1307 --
1308 IF c_get_queued_event%NOTFOUND OR c_get_queued_event%NOTFOUND IS NULL
1309 THEN
1310 --
1311 dbg('No record for assignment '||p_assignment_id||' exists, so INSERT.');
1312 --
1313 INSERT INTO hri_eq_sprvsr_hstry_chgs
1314 (
1315 assignment_id
1316 ,erlst_evnt_effective_date
1317 )
1318 VALUES
1319 (
1320 p_assignment_id
1321 ,p_change_date
1322 );
1323 --
1324 l_action_taken := 'INSERTED';
1325 --
1326 --
1327 -- If there is already a record in hri_eq_asgn_evnts for the assignment_id
1328 -- but it is for an event that occurred later or at the same time as the
1329 -- new event we have found (p_change_date), then update the queue with the
1330 -- earlier date.
1331 --
1332 ELSIF l_erlst_evnt_effective_date > p_change_date
1333 THEN
1334 --
1335 dbg('Record is earlier than one in queue currently for '||p_assignment_id||', so UPDATE.');
1336 --
1337 UPDATE hri_eq_sprvsr_hstry_chgs
1338 SET erlst_evnt_effective_date = p_change_date
1339 WHERE assignment_id = p_assignment_id;
1340 --
1341 l_action_taken := 'UPDATED';
1342 --
1343 ELSE
1344 --
1345 dbg('Record is later, or the same date as the one in queue currently for '||p_assignment_id||', so do NOTHING.');
1346 --
1347 l_action_taken := 'NONE';
1348 --
1349 END IF;
1350 --
1351 -- Update the hri_archive_events table with details of what we have
1352 -- done for the identified event.
1353 --
1354 IF g_enable_archive_flag = 'Y' THEN
1355 --
1356 Update_archive_record
1357 (
1358 p_assignment_id => p_assignment_id
1359 ,p_change_date => p_change_date
1360 ,p_event_queue_table => 'HRI_EQ_SPRVSR_HSTRY_CHGS'
1361 ,p_action_taken => l_action_taken
1362 ,p_capture_from_date => p_start_date
1363 );
1364 --
1365 END IF;
1366 --
1367 END Update_Sprvsr_Hstry_Evnt_Q;
1368 --
1369 --
1370 -- ----------------------------------------------------------------------------
1371 -- 5.1.2.1 (Update_Sprvsr_Hrchy_Evnt_Q)
1372 -- Update the Supervisor Hierarchy Event Queue
1373 -- ============================================================================
1374 -- This procedure will for the given assignment_id and change date, update
1375 -- the event queue by either:
1376 --
1377 -- + Insert a new record in the event queue (if no record for that assignment
1378 -- exists for the assignment).
1379 -- + Update the existing record in the event queue for the assignment, if it
1380 -- exists, and has a later date than the new event you have found.
1381 -- + Do nothing to the event queue as there is already an early change record
1382 -- for the assignment.
1383 --
1384 -- The procedure will also insert arecord of the event in hri_archive_events
1385 -- for audit purposes to record the event capture and what was done to the
1386 -- event queue as a result of the event capture.
1387 --
1388 PROCEDURE Update_Sprvsr_Hrchy_Evnt_Q
1389 (p_assignment_id IN NUMBER
1390 ,p_change_date IN DATE -- The effective change date
1391 ,p_start_date IN DATE -- The date the events were captured from
1392 )
1393 IS
1394 --
1395 -- Select the erlst_evnt_processed_date from the event queue
1396 -- for the assignment_id if it exists, so that we can decide
1397 -- whether we need to:
1398 --
1399 -- + Insert if there is no record for the assignment in the queue.
1400 -- + Update the queue if p_change_date is earlier than
1401 -- erlst_evnt_processed_date.
1402 -- + Do nothing.
1403 --
1404 CURSOR c_get_queued_event(cp_assignment_id IN NUMBER) IS
1405 SELECT erlst_evnt_effective_date
1406 FROM hri_eq_sprvsr_hrchy_chgs
1407 WHERE assignment_id = cp_assignment_id;
1408 --
1409 l_erlst_evnt_effective_date DATE; -- Stores the earliest event data for
1410 -- the assignment currently stored
1411 -- in the event queue.
1412 --
1413 l_action_taken VARCHAR2(30); -- used to store what we do with the passed
1414 -- in event. This is used when inserting
1415 -- into hri_archive_events to show what action
1416 -- we took with the captured event.
1417 --
1418 BEGIN
1419 --
1420 dbg('Updating the Supervisor Hierarchy Events Queue for '||p_assignment_id||'.');
1421 --
1422 -- Exit if HRI:Populate Supervisor Hierarchy Events Queue is not enabled
1423 --
1424 IF g_col_sup_hrchy_eq = 'N' THEN
1425 --
1426 dbg('Profile HRI:Populate Supervisor Hierarchy Events Queue not enabled, '||
1427 'skip populating supervisor hierarchy events queue');
1428 return;
1429 --
1430 END IF;
1431 --
1432 -- Get the earliest change date currently stored in the event queue
1433 -- (l_erlst_evnt_processed_date), where it exists.
1434 --
1435 OPEN c_get_queued_event(p_assignment_id);
1436 FETCH c_get_queued_event INTO l_erlst_evnt_effective_date;
1437 --
1438 -- If no record exists in the queue for the assignment, then we need to
1439 -- INSERT into the event queue.
1440 --
1441 IF c_get_queued_event%NOTFOUND OR c_get_queued_event%NOTFOUND IS NULL
1442 THEN
1443 --
1444 dbg('No record for assignment '||p_assignment_id||' exists, so INSERT.');
1445 --
1446 INSERT INTO hri_eq_sprvsr_hrchy_chgs
1447 (
1448 assignment_id
1449 ,erlst_evnt_effective_date
1450 )
1451 VALUES
1452 (
1453 p_assignment_id
1454 ,p_change_date
1455 );
1456 --
1457 l_action_taken := 'INSERTED';
1458 --
1459 --
1460 -- If there is already a record in hri_eq_asgn_evnts for the assignment_id
1461 -- but it is for an event that occurred later or at the same time as the
1462 -- new event we have found (p_change_date), then update the queue with the
1463 -- earlier date.
1464 --
1465 ELSIF l_erlst_evnt_effective_date > p_change_date
1466 THEN
1467 --
1468 dbg('Record is earlier than one in queue currently for '
1469 ||p_assignment_id||', so UPDATE.');
1470 --
1471 UPDATE hri_eq_sprvsr_hrchy_chgs
1472 SET erlst_evnt_effective_date = p_change_date
1473 WHERE assignment_id = p_assignment_id;
1474 --
1475 l_action_taken := 'UPDATED';
1476 --
1477 ELSE
1478 --
1479 dbg('Record is later, or the same date as the one in queue currently for '
1480 ||p_assignment_id||', so do NOTHING.');
1481 --
1482 l_action_taken := 'NONE';
1483 --
1484 END IF;
1485 --
1486 -- Update the hri_archive_events table with details of what we have
1487 -- done for the identified event.
1488 --
1489 IF g_enable_archive_flag = 'Y' THEN
1490 --
1491 Update_archive_record
1492 (
1493 p_assignment_id => p_assignment_id
1494 ,p_change_date => p_change_date
1495 ,p_event_queue_table => 'HRI_EQ_SPRVSR_HRCHY_CHGS'
1496 ,p_action_taken => l_action_taken
1497 ,p_capture_from_date => p_start_date
1498 );
1499 --
1500 END IF;
1501 --
1502 END Update_Sprvsr_Hrchy_Evnt_Q;
1503 --
1504 -- ----------------------------------------------------------------------------
1505 -- Find_sub_event_group_events
1506 -- Find the earliest event in the master event group PLSQL table for a given
1507 -- sub event group id.
1508 -- ============================================================================
1509 --
1510 -- This procedure calls pay_interpreter_pkg.get_subset_given_new_evg to
1511 -- process the alreadu identified events in the master event group, to see
1512 -- if any of those events are in the sub event group.
1513 --
1514 -- If any events are found in the sub event group, then the earliest event date
1515 -- found is returned via p_event_date to the calling process.
1516 --
1517 -- The package pay_interpreter_pkg.get_subset_given_new_evg returns a PLSQL
1518 -- table similar to the one passed in, but only containing those rows that
1519 -- are relevant to these event queues. The earliest of the records found in
1520 -- the returned PLSQL table, is then used to update the event queues.
1521 --
1522 --
1523 PROCEDURE Find_sub_event_group_events
1524 (
1525 p_assignment_id IN NUMBER -- The assignment id that we are currently
1526 -- processing.
1527 ,p_start_date IN DATE -- Used for updating the event archive only.
1528 -- Does not effect process flow in this
1529 -- procedure.
1530 ,p_sub_event_grp_id IN NUMBER -- The event group id of the sub event group
1531 -- that we are trying to find events for.
1532 ,p_comment_text VARCHAR2 -- Text used by debug comments to indicate
1533 -- which queue's sub eveng group is being
1534 -- processed.
1535 ,p_master_events_table -- The Master Event Group PLSQL table.
1536 IN OUT nocopy pay_interpreter_pkg.t_detailed_output_table_type
1537 ,p_event_date OUT nocopy DATE -- Event date of the earliest sub event found
1538 -- in the passed in event group and
1539 -- master events table
1540 ,p_sub_evt_grp_tbl OUT nocopy pay_interpreter_pkg.t_detailed_output_table_type
1541 -- This array is only relevant as an
1542 -- output value when p_event_capture_mode
1543 -- is 'S'.
1544 --
1545 ,p_event_capture_mode IN VARCHAR2 -- Indicates whether the process should
1546 -- E) return the earliest date for a
1547 -- relevant change.
1548 -- OR
1549 -- R) Return simply the fact that a
1550 -- relevant change has been found
1551 -- OR
1552 -- S) Return the sub event group array
1553 -- for further processing.
1554 )
1555 IS
1556 --
1557 -- We only care about the earliest change for an assignment. l_min_date is
1558 -- used to store the earliest effective_date date found in the PLSQL table
1559 -- l_sub_evt_grp_tbl returned from the call to
1560 -- pay_interpreter_pkg.get_subset_given_new_evg
1561 --
1562 --
1563 l_min_date DATE;
1564 l_effective_date DATE;
1565 l_extns_date DATE;
1566 --
1567 BEGIN
1568 --
1569 dbg('Executing Find_sub_event_group_events for '||p_comment_text
1570 ||' in mode '|| p_event_capture_mode ||'....');
1571 --
1572 -- If the master event group PLSQL table is empty then there is no
1573 -- point continuing, so exit procedure.
1574 --
1575 IF p_master_events_table.COUNT = 0
1576 THEN
1577 --
1578 dbg('0 records in master table for '||p_comment_text||
1579 ', exiting Find_sub_event_group_events');
1580 --
1581 p_event_date := NULL;
1582 --
1583 RETURN;
1584 --
1585 END IF;
1586 --
1587 -- Set the minimum change date for the assignment to end of time.
1588 --
1589 l_min_date := g_end_of_time;
1590 --
1591 -- Find all the events in the master event group for the assignment that
1592 -- relate to the sub event group for changes relevant to the assignment
1593 -- events fact.
1594 --
1595 dbg('Calling pay_interpreter_pkg.get_subset_given_new_evg ...');
1596 --
1597 pay_interpreter_pkg.get_subset_given_new_evg
1598 (p_filter_event_group_id => p_sub_event_grp_id
1599 ,p_complete_detail_tab => p_master_events_table
1600 ,p_subset_detail_tab => p_sub_evt_grp_tbl
1601 );
1602 --
1603 dbg('Sub Event Rows Returned: '||p_sub_evt_grp_tbl.COUNT);
1604 --
1605 -- The followin IF statement is used for debugging only.
1606 --
1607 IF (p_sub_evt_grp_tbl.COUNT <> p_master_events_table.COUNT)
1608 AND
1609 (g_debug_flag = 'Y')
1610 THEN
1611 --
1612 dbg(
1613 'Row number discrepency found for assignment: '
1614 ||TO_CHAR(p_assignment_id)||','
1615 ||TO_CHAR(p_master_events_table.COUNT)||','
1616 ||TO_CHAR(p_sub_evt_grp_tbl.COUNT)
1617 );
1618 --
1619 END IF;
1620 --
1621 -- Only need to check find the earliest change if rows have been found
1622 -- that relate to the sub event group.
1623 --
1624 IF p_sub_evt_grp_tbl.COUNT > 0
1625 THEN
1626 --
1627 dbg('Found some records in the '||p_comment_text||' sub event group');
1628 --
1629 ELSE
1630 --
1631 dbg('No relevant records found that affect '||p_comment_text||'.');
1632 --
1633 -- No relevant events found, so return NULL date, this will be used by the
1634 -- calling process, to decide not to do anything further.
1635 --
1636 p_event_date := NULL;
1637 --
1638 RETURN;
1639 --
1640 END IF; -- If l_sub_evt_grp_tbl.COUNT > 0
1641 --
1642 -- If we are in mode 'R' simply return the fact that an
1643 -- event has been found within the specified period.
1644 --
1645 IF p_event_capture_mode = 'R'
1646 THEN
1647 --
1648 -- If any events have occurred for the sub event group within
1649 -- the period this mode will simply return 'end of time'
1650 --
1651 dbg('An event has been found within the period ...');
1652 dbg('As called in mode ''R'' return p_event_date as ''end of time''.');
1653 --
1654 p_event_date := g_end_of_time;
1655 --
1656 RETURN;
1657 --
1658 END IF;
1659 --
1660 -- Default mode 'S' will return the earliest relevent sub event group
1661 -- event date for the assignment within the period, and p_sub_evt_grp_tbl
1662 -- which can be ignored.
1663 --
1664 IF p_event_capture_mode = 'S'
1665 THEN
1666 --
1667 -- If any events have occurred for the sub event group within
1668 -- the period this mode will simply return 'end of time' and the
1669 -- array p_sub_evt_grp_tbl.
1670 --
1671 dbg('An event has been found within the period ...');
1672 dbg('As called in mode ''S'' return p_event_date as ''end of time'''||
1673 ' and return the sub event group array ''p_sub_evt_grp_tbl'''||
1674 ' for further processing.');
1675 --
1676 p_event_date := g_end_of_time;
1677 --
1678 RETURN;
1679 --
1680 END IF; -- Default get earliest date of change mode 'S'
1681 --
1682 -- Default mode 'E' will return the earliest relevent sub event group
1683 -- event date for the assignment within the period.
1684 --
1685 IF p_event_capture_mode = 'E'
1686 THEN
1687 --
1688 -- Default get earliest date of change mode 'E'
1689 --
1690 dbg('Find_sub_event_group_events has been called in mode ''E''.');
1691 --
1692 FOR i in 1..p_sub_evt_grp_tbl.COUNT
1693 LOOP
1694 --
1695 -- Call the function which evaluates the event date for special cases
1696 -- such as Termination date evaluation, extension date calculations etc.
1697 -- It returns the adjusted event date or the effective change date
1698 --
1699 l_effective_date := eval_one_off_cases
1700 (p_sub_evt_grp_tbl => p_sub_evt_grp_tbl(i)
1701 ,p_assignment_id => p_assignment_id
1702 ,p_comment_text => p_comment_text
1703 ,p_effective_date => p_sub_evt_grp_tbl(i).effective_date);
1704 --
1705 -- If the change date of the current record is less than l_min_date
1706 -- change l_min_date to equal that date. At the end of the loop
1707 -- l_min_date will be set to the earliest change date for the assignment
1708 -- since p_start_date.
1709 --
1710 IF l_effective_date < l_min_date
1711 THEN
1712 --
1713 l_min_date := l_effective_date;
1714 --
1715 dbg('Earliest date found so far: '||TO_CHAR(l_min_date));
1716 --
1717 END IF;
1718 --
1719 -- Following IF statement is for debug purposes only.
1720 --
1721 IF g_debug_flag = 'Y'
1722 THEN
1723 --
1724 dbg('Datetracked_event: '||p_sub_evt_grp_tbl(i).datetracked_event
1725 ||', Change_mode: '||p_sub_evt_grp_tbl(i).change_mode
1726 ||', Effective_date: '||p_sub_evt_grp_tbl(i).effective_date
1727 ||', dated_table_id: '||TO_CHAR(p_sub_evt_grp_tbl(i).dated_table_id)
1728 ||', surrogate_key: '||TO_CHAR(p_sub_evt_grp_tbl(i).surrogate_key)
1729 ||', column_name: '||TO_CHAR(p_sub_evt_grp_tbl(i).column_name)
1730 ||', old_value: '||p_sub_evt_grp_tbl(i).old_value
1731 ||', new_value: '||p_sub_evt_grp_tbl(i).new_value
1732 ||', change_values: '||p_sub_evt_grp_tbl(i).change_values
1733 );
1734 --
1735 END IF; -- g_debug_flag = 'Y'
1736 --
1737 END LOOP;
1738 --
1739 -- Set p_event_date to the earliest event date found. This will be used
1740 -- by the calling procedure to update the relevant event queue.
1741 --
1742 -- 3906029, For supervisor hierarchy events the min date returned by the
1743 -- wrapper, cannot be used directly. In case the event occurs before the
1744 -- minimum date for which the hierarchy is populated, the collection program
1745 -- will collect data which can corrupt the hierarchy with duplicate records
1746 -- for subordinates. So the min date should returned should not be
1747 -- less than the Refresh From Date for the last full refresh run of supervisor
1748 -- hierarchy
1749 --
1750 IF p_comment_text = 'Supervisor' THEN
1751 --
1752 dbg('As this is a supervisor event, we need to make sure event date '||
1753 'returned (p_event_date) is no less than minimum date for which '||
1754 'the supervisor hierarchy has been populated');
1755 --
1756 p_event_date := greatest(l_min_date,g_min_suph_date);
1757 --
1758 ELSIF l_min_date = g_end_of_time THEN
1759 --
1760 -- In case the min event date is eot then there is no need to create an event
1761 -- the l_min_date is initialized to EOT, and there is no point in capturing an event
1762 -- which is happening so far in time.
1763 --
1764 dbg('l_min_date IS EOT so set p_event_date to NULL');
1765 --
1766 p_event_date := null;
1767 --
1768 ELSE
1769 --
1770 dbg('Just return the earliest date found');
1771 --
1772 p_event_date := l_min_date;
1773 --
1774 END IF;
1775 --
1776 dbg('Returning p_event_date as '||TO_CHAR(p_event_date)||'.');
1777 --
1778 RETURN;
1779 --
1780 END IF; -- Default get earliest date of change mode 'E'
1781 --
1782 END Find_sub_event_group_events;
1783 --
1784 -- Overloaded version of procedure to be called for event groups where we
1785 -- are interested in the earliest change for an assignment, as opposed
1786 -- to just being interested in the whether an event has occurred at all.
1787 --
1788 PROCEDURE Find_sub_event_group_events
1789 (
1790 p_assignment_id IN NUMBER -- The assignment id that we are currently
1791 -- processing.
1792 ,p_start_date IN DATE -- Used for updating the event archive only.
1793 -- Does not effect process flow in this
1794 -- procedure.
1795 ,p_sub_event_grp_id IN NUMBER -- The event group id of the sub event group
1796 -- that we are trying to find events for.
1797 ,p_comment_text VARCHAR2 -- Text used by debug comments to indicate
1798 -- which queue's sub eveng group is being
1799 -- processed.
1800 ,p_master_events_table -- The Master Event Group PLSQL table.
1801 IN OUT nocopy pay_interpreter_pkg.t_detailed_output_table_type
1802 ,p_event_date OUT nocopy DATE -- Event date of the earliest sub event found
1803 -- in the passed in event group and
1804 -- master events table
1805 )
1806 IS
1807 --
1808 -- The following PLSQL table will hold the events found from the master
1809 -- event group that relate to sub event group for the passed in sub event
1810 -- group id (p_sub_event_grp_id). It's return value is ignored
1811 --
1812 l_sub_evt_grp_tbl pay_interpreter_pkg.t_detailed_output_table_type;
1813 --
1814 BEGIN
1815 --
1816 Find_sub_event_group_events
1817 (
1818 p_assignment_id => p_assignment_id
1819 ,p_start_date => p_start_date
1820 ,p_sub_event_grp_id => p_sub_event_grp_id
1821 ,p_comment_text => p_comment_text
1822 ,p_master_events_table => p_master_events_table
1823 ,p_event_date => p_event_date
1824 ,p_sub_evt_grp_tbl => l_sub_evt_grp_tbl -- result ignored when called from here
1825 ,p_event_capture_mode => 'E' -- Indicates we want to know earliest event
1826 );
1827 --
1828 END;
1829 --
1830 -- ----------------------------------------------------------------------------
1831 -- 5.1.2 (Process_supervisor_events)
1832 -- Get Earliest Supervisor Event For Assignment
1833 -- ============================================================================
1834 -- This procedure calls the procedure Find_sub_event_group_events, passing
1835 -- in the master event group PLSQL table (containing all the interpreted
1836 -- events that have occurred since p_start_date for the assignment), to
1837 -- identify all of the events that have occurred that are relevent to
1838 -- the supervisor hierarchy, and supervisor status history event queues.
1839 --
1840 PROCEDURE Process_supervisor_events
1841 (p_assignment_id IN NUMBER
1842 ,p_start_date IN DATE
1843 ,p_master_events_table
1844 IN OUT nocopy pay_interpreter_pkg.t_detailed_output_table_type)
1845 IS
1846 --
1847 -- l_min_date is used to store the earliest effective_date date found
1848 -- by the procedure Find_sub_event_group_events for the sub event group
1849 --
1850 l_min_date DATE;
1851 --
1852 BEGIN
1853 --
1854 dbg('Executing Process_supervisor_events ....');
1855 --
1856 -- 3658545 exit if the supervisor related queues are not to be populated
1857 --
1858 IF g_col_sup_hrchy_eq = 'N' AND
1859 g_col_sup_hstry_eq = 'N'
1860 THEN
1861 --
1862 dbg('Not populating supervisor related queues');
1863 RETURN;
1864 --
1865 END IF;
1866 --
1867 Find_sub_event_group_events
1868 (
1869 p_assignment_id => p_assignment_id
1870 ,p_start_date => p_start_date
1871 ,p_sub_event_grp_id => g_sprvsr_change_event_grp_id
1872 ,p_comment_text => 'Supervisor'
1873 ,p_master_events_table => p_master_events_table
1874 ,p_event_date => l_min_date
1875 );
1876 --
1877 -- If l_min_date is NOT NULL this means that Find_sub_event_group_events
1878 -- has found an event for the sub event group, and assignment id on that
1879 -- date. We therefore need to see if it is necessary to update the relevant
1880 -- event queues with this information.
1881 --
1882 IF l_min_date IS NOT NULL
1883 THEN
1884 --
1885 Update_Sprvsr_Hrchy_Evnt_Q
1886 (
1887 p_assignment_id => p_assignment_id
1888 ,p_change_date => l_min_date
1889 ,p_start_date => p_start_date
1890 );
1891 --
1892 Update_Sprvsr_Hstry_Evnt_Q
1893 (
1894 p_assignment_id => p_assignment_id
1895 ,p_change_date => l_min_date
1896 ,p_start_date => p_start_date
1897 );
1898 --
1899 END IF;
1900 --
1901 END Process_supervisor_events;
1902 --
1903 -- ----------------------------------------------------------------------------
1904 -- 5.1.3.1 (Update_Asgn_Evnt_Fct_Evnt_Q)
1905 -- Update the Assignment Event Fact Event Queue
1906 -- ============================================================================
1907 -- This procedure will for the given assignment_id and change date, update
1908 -- the event queue by either:
1909 --
1910 -- + Insert a new record in the event queue (if no record for that assignment
1911 -- exists for the assignment).
1912 -- + Update the existing record in the event queue for the assignment, if it
1913 -- exists, and has a later date than the new event you have found.
1914 -- + Do nothing to the event queue as there is already an early change record
1915 -- for the assignment.
1916 --
1917 -- The procedure will also insert arecord of the event in hri_archive_events
1918 -- for audit purposes to record the event capture and what was done to the
1919 -- event queue as a result of the event capture.
1920 --
1921 PROCEDURE Update_Asgn_Evnt_Fct_Evnt_Q
1922 (p_assignment_id IN NUMBER
1923 ,p_change_date IN DATE -- The effective change date
1924 ,p_start_date IN DATE -- The date the events were captured from
1925 )
1926 IS
1927 --
1928 -- Select the erlst_evnt_processed_date from the event queue
1929 -- for the assignment_id if it exists, so that we can decide
1930 -- whether we need to:
1931 --
1932 -- + Insert if there is no record for the assignment in the queue.
1933 -- + Update the queue if p_change_date is earlier than
1934 -- erlst_evnt_processed_date.
1935 -- + Do nothing.
1936 --
1937 CURSOR c_get_queued_event(cp_assignment_id IN NUMBER) IS
1938 SELECT erlst_evnt_effective_date
1939 FROM hri_eq_asgn_evnts
1940 WHERE assignment_id = cp_assignment_id;
1941 --
1942 l_erlst_evnt_effective_date DATE; -- Stores the earliest event data for
1943 -- the assignment currently stored
1944 -- in the wvwnt queue.
1945 --
1946 l_action_taken VARCHAR2(30); -- used to store what we do with the passed
1947 -- in event. This is used when inserting
1948 -- into hri_archive_events to show what action
1949 -- we took with the captured event.
1950 --
1951 BEGIN
1952 --
1953 dbg('Updating the Assignment Events Queue for '||p_assignment_id||'.');
1954 --
1955 -- Get the earliest change date currently stored in the event queue
1956 -- (l_erlst_evnt_processed_date), where it exists.
1957 --
1958 OPEN c_get_queued_event(p_assignment_id);
1959 FETCH c_get_queued_event INTO l_erlst_evnt_effective_date;
1960 --
1961 -- If no record exists in the queue for the assignment, then we need to
1962 -- INSERT into the event queue.
1963 --
1964 IF c_get_queued_event%NOTFOUND OR c_get_queued_event%NOTFOUND IS NULL
1965 THEN
1966 --
1967 dbg('No record for assignment '||p_assignment_id||' exists, so INSERT.');
1968 --
1969 INSERT INTO hri_eq_asgn_evnts
1970 (
1971 assignment_id
1972 ,erlst_evnt_effective_date
1973 )
1974 VALUES
1975 (
1976 p_assignment_id
1977 ,p_change_date
1978 );
1979 --
1980 l_action_taken := 'INSERTED';
1981 --
1982 --
1983 -- If there is already a record in hri_eq_asgn_evnts for the assignment_id
1984 -- but it is for an event that occurred later or at the same time as the
1985 -- new event we have found (p_change_date), then update the queue with the
1986 -- earlier date.
1987 --
1988 ELSIF l_erlst_evnt_effective_date > p_change_date
1989 THEN
1990 --
1991 dbg('Record is earlier than one in queue currently for '||p_assignment_id||', so UPDATE.');
1992 --
1993 UPDATE hri_eq_asgn_evnts
1994 SET erlst_evnt_effective_date = p_change_date
1995 WHERE assignment_id = p_assignment_id;
1996 --
1997 l_action_taken := 'UPDATED';
1998 --
1999 ELSE
2000 --
2001 dbg('Record is later, or the same date as the one in queue currently for '||p_assignment_id||', so do NOTHING.');
2002 --
2003 l_action_taken := 'NONE';
2004 --
2005 END IF;
2006 --
2007 -- Update the hri_archive_events table with details of what we have
2008 -- done for the identified event.
2009 --
2010 IF g_enable_archive_flag = 'Y' THEN
2011 --
2012 Update_archive_record
2013 (
2014 p_assignment_id => p_assignment_id
2015 ,p_change_date => p_change_date
2016 ,p_event_queue_table => 'HRI_EQ_ASGN_EVNTS'
2017 ,p_action_taken => l_action_taken
2018 ,p_capture_from_date => p_start_date
2019 );
2020 --
2021 END IF;
2022 --
2023 END Update_Asgn_Evnt_Fct_Evnt_Q;
2024
2025
2026 --
2027 -- ----------------------------------------------------------------------------
2028 -- 5.1.3.??? (Update_Absence_Dim_Evnt_Q)
2029 -- Update the Assignment Event Fact Event Queue
2030 -- ============================================================================
2031 -- This procedure will for the given assignment_id and change date, update
2032 -- the event queue by either:
2033 --
2034 -- + Insert a new record in the event queue (if no record for that assignment
2035 -- exists for the assignment).
2036 -- + Do nothing to the event queue as there is already a change record
2037 -- for the assignment.
2038 --
2039 -- The procedure will also insert arecord of the event in hri_archive_events
2040 -- for audit purposes to record the event capture and what was done to the
2041 -- event queue as a result of the event capture.
2042 --
2043 PROCEDURE Update_Absence_Dim_Evnt_Q
2044 (p_assignment_id IN NUMBER
2045 ,p_start_date IN DATE -- The date the events were captured from
2046 ,p_sub_evt_grp_tbl IN pay_interpreter_pkg.t_detailed_output_table_type
2047 -- The events found in the sub event group
2048 -- that need to be processed to identify
2049 -- which absence_attendance_ids have had
2050 -- events.
2051 )
2052 IS
2053 --
2054 -- Identify if an event is already queued for the absence_attendance_id
2055 --
2056 CURSOR c_get_queued_event(cp_absence_attendance_id IN NUMBER) IS
2057 SELECT 'X' h_dummy
2058 FROM hri_eq_utl_absnc_dim
2059 WHERE absence_attendance_id = cp_absence_attendance_id;
2060 --
2061 -- Stores the previous absence attendance id being processed.
2062 --
2063 l_prv_absence_attendance_id NUMBER DEFAULT -1;
2064 --
2065 -- Stores a dummy value returned from cursor c_get_queued_event
2066 --
2067 l_dummy VARCHAR2(1);
2068 --
2069 -- l_action_taken used to store the type of event that has occurred
2070 -- to be stored in the archive table.
2071 --
2072 l_action_taken VARCHAR2(10) DEFAULT NULL;
2073 --
2074 BEGIN
2075 --
2076 dbg('In procedure Update_Absence_Dim_Evnt_Q ...');
2077 --
2078 dbg('Updating the Absence Dimension Queue for '||p_assignment_id||'.');
2079 --
2080 -- Loop through sub event group and identify the unique
2081 -- absence_attendance_ids that have had an event.
2082 --
2083 FOR i in 1..p_sub_evt_grp_tbl.COUNT
2084 LOOP
2085 --
2086 -- Holds the surrogate key value for the event that has occurred
2087 -- for absence eevents this will be the absence_attendance_id
2088 --
2089 IF l_prv_absence_attendance_id <>
2090 p_sub_evt_grp_tbl(i).surrogate_key
2091 THEN
2092 --
2093 dbg('Found event for absence_attendance_id '||
2094 p_sub_evt_grp_tbl(i).surrogate_key);
2095 --
2096 -- Check if there is already an event for this absence_attendance_id
2097 -- in the queue.
2098 --
2099 OPEN c_get_queued_event(p_sub_evt_grp_tbl(i).surrogate_key);
2100 FETCH c_get_queued_event INTO l_dummy;
2101
2102 --
2103 -- If no record in the event queue is found for the absence_attendance_id
2104 -- then insert it into the queue
2105 --
2106 IF c_get_queued_event%NOTFOUND OR c_get_queued_event%NOTFOUND IS NULL
2107 THEN
2108 --
2109 dbg('No record for assignment '||p_assignment_id||' exists, so INSERT.');
2110 --
2111 INSERT INTO hri_eq_utl_absnc_dim
2112 (
2113 absence_attendance_id
2114 )
2115 VALUES
2116 (
2117 p_sub_evt_grp_tbl(i).surrogate_key
2118 );
2119 --
2120 l_action_taken := 'INSERTED';
2121 --
2122 --
2123 -- IF a record already exists for the absence
2124 --
2125 ELSE
2126 --
2127 dbg('Record already exists.');
2128 -- --
2129 l_action_taken := 'NONE';
2130 --
2131 END IF;
2132 --
2133 -- Close Curosr c_get_queued_event
2134 --
2135 CLOSE c_get_queued_event;
2136 --
2137 -- Update the hri_archive_events table with details of what we have
2138 -- done for the identified event.
2139 --
2140 IF g_enable_archive_flag = 'Y' THEN
2141 --
2142 -- Instead of assignment_id pass in absence_attendance_id
2143 --
2144 Update_archive_record
2145 (
2146 p_assignment_id => p_sub_evt_grp_tbl(i).surrogate_key
2147 ,p_change_date => NULL
2148 ,p_event_queue_table => 'HRI_EQ_UTL_ABSNC_DIM '
2149 ,p_action_taken => l_action_taken
2150 ,p_capture_from_date => p_start_date
2151 );
2152 --
2153 END IF;
2154 --
2155 -- Set the value of the value for l_prv_absence_attendance_id
2156 -- to the current absence_attendance_id so that we can see
2157 -- if it has changed in the next loop.
2158 --
2159 l_prv_absence_attendance_id := p_sub_evt_grp_tbl(i).surrogate_key;
2160 --
2161 -- Following IF statement is for debug purposes only.
2162 --
2163 IF g_debug_flag = 'Y'
2164 THEN
2165 --
2166 dbg('Datetracked_event: '||p_sub_evt_grp_tbl(i).datetracked_event
2167 ||', Change_mode: '||p_sub_evt_grp_tbl(i).change_mode
2168 ||', Effective_date: '||p_sub_evt_grp_tbl(i).effective_date
2169 ||', dated_table_id: '||TO_CHAR(p_sub_evt_grp_tbl(i).dated_table_id)
2170 ||', surrogate_key: '||TO_CHAR(p_sub_evt_grp_tbl(i).surrogate_key)
2171 ||', column_name: '||TO_CHAR(p_sub_evt_grp_tbl(i).column_name)
2172 ||', old_value: '||p_sub_evt_grp_tbl(i).old_value
2173 ||', new_value: '||p_sub_evt_grp_tbl(i).new_value
2174 ||', change_values: '||p_sub_evt_grp_tbl(i).change_values
2175 );
2176 --
2177 END IF; -- g_debug_flag = 'Y'
2178 --
2179 END IF; -- If the absence_attendance_id has changed.
2180 --
2181 END LOOP;
2182 --
2183 END Update_Absence_Dim_Evnt_Q;
2184 --
2185 -- ----------------------------------------------------------------------------
2186 -- 5.1.3 (Process_assgnmnt_evnt_changes)
2187 -- Get Earliest Assignment Events Fact Event For Assignment
2188 -- ============================================================================
2189 -- This procedure calls the procedure Find_sub_event_group_events, passing
2190 -- in the master event group PLSQL table (containing all the interpreted
2191 -- events that have occurred since p_start_date for the assignment), to
2192 -- identify all of the events that have occurred that are relevent to
2193 -- the assignment events fact event queue.
2194 --
2195 PROCEDURE Process_assgnmnt_evnt_changes
2196 (p_assignment_id IN NUMBER
2197 ,p_start_date IN DATE
2198 ,p_master_events_table
2199 IN OUT nocopy pay_interpreter_pkg.t_detailed_output_table_type)
2200 IS
2201 --
2202 -- l_min_date is used to store the earliest effective_date date found
2203 -- by the procedure Find_sub_event_group_events for the sub event group
2204 --
2205 l_min_date DATE;
2206 --
2207 BEGIN
2208 --
2209 dbg('Executing Process_assgnmnt_evnt_changes ....');
2210 --
2211 -- 3658545 Populate assignment events queue only if g_col_asg_events_eq = 'Y'
2212 --
2213 IF g_col_asg_events_eq = 'N' THEN
2214 --
2215 dbg('Profile HRI:Populate Assignment Events Queue not enabled, skip populating '||
2216 'assignment events queue');
2217 return;
2218 --
2219 END IF;
2220 --
2221 Find_sub_event_group_events
2222 (
2223 p_assignment_id => p_assignment_id
2224 ,p_start_date => p_start_date
2225 ,p_sub_event_grp_id => g_assgnmnt_evnt_event_grp_id
2226 ,p_comment_text => 'Assignment Events'
2227 ,p_master_events_table => p_master_events_table
2228 ,p_event_date => l_min_date
2229 );
2230 --
2231 -- If l_min_date is NOT NULL this means that Find_sub_event_group_events
2232 -- has found an event for the sub event group, and assignment id on that
2233 -- date. We therefore need to see if it is necessary to update the relevant
2234 -- event queues with this information.
2235 --
2236 IF l_min_date IS NOT NULL
2237 THEN
2238 --
2239 -- Insert a record in the event queue for the earliest change for the
2240 -- assignment for the sub event group.
2241 --
2242 Update_Asgn_Evnt_Fct_Evnt_Q
2243 (
2244 p_assignment_id => p_assignment_id
2245 ,p_change_date => l_min_date
2246 ,p_start_date => p_start_date
2247 );
2248 --
2249 END IF;
2250 --
2251 END Process_assgnmnt_evnt_changes;
2252 --
2253 -- ----------------------------------------------------------------------------
2254 -- 5.1.4 (Process_absence_evnt_changes)
2255 -- Get Absence Events For Assignment
2256 -- ============================================================================
2257 -- This procedure calls the procedure Find_sub_event_group_events, passing
2258 -- in the master event group PLSQL table (containing all the interpreted
2259 -- events that have occurred since p_start_date for the assignment), to
2260 -- identify any events that have occurred that are relevent to
2261 -- the absence dimension event queue.
2262 --
2263 PROCEDURE Process_absence_dim_changes
2264 (p_assignment_id IN NUMBER
2265 ,p_start_date IN DATE
2266 ,p_master_events_table
2267 IN OUT nocopy pay_interpreter_pkg.t_detailed_output_table_type)
2268 IS
2269 --
2270 -- l_min_date is used to store the earliest effective_date date found
2271 -- by the procedure Find_sub_event_group_events for the sub event group
2272 --
2273 l_min_date DATE;
2274 --
2275 -- The following PLSQL table will hold the sub events returned by
2276 -- Find_sub_event_group_events.
2277 --
2278 l_sub_evt_grp_tbl pay_interpreter_pkg.t_detailed_output_table_type;
2279 --
2280 BEGIN
2281 --
2282 dbg('Executing Process_absence_evnt_changes ....');
2283 --
2284 -- 3658545 Populate assignment events queue only if g_col_asg_events_eq = 'Y'
2285 --
2286 IF g_col_absence_events_eq = 'N' THEN
2287 --
2288 dbg('Profile HRI:Absence Dimension Queue not enabled, skip populating '||
2289 'absence events queue');
2290 return;
2291 --
2292 END IF;
2293 --
2294 Find_sub_event_group_events
2295 (
2296 p_assignment_id => p_assignment_id
2297 ,p_start_date => p_start_date
2298 ,p_sub_event_grp_id => g_absence_dim_event_grp_id
2299 ,p_comment_text => 'Absences'
2300 ,p_master_events_table => p_master_events_table
2301 ,p_event_date => l_min_date
2302 ,p_sub_evt_grp_tbl => l_sub_evt_grp_tbl
2303 ,p_event_capture_mode => 'S' -- Tells process just tell if there has been
2304 -- an event within the period. D not care
2305 -- when this was, as absences are not date
2306 -- tracked.
2307 );
2308 --
2309 -- If l_min_date is NOT NULL this means that Find_sub_event_group_events
2310 -- has found an event for the sub event group, and assignment id on that
2311 -- date. We therefore need to see if it is necessary to update the relevant
2312 -- event queues with this information.
2313 --
2314 IF l_min_date IS NOT NULL
2315 THEN
2316 --
2317 dbg('Absence Events have been found ....');
2318 --
2319 -- Insert a record in the event queue for the earliest change for the
2320 -- assignment for the sub event group.
2321 --
2322 dbg('Calling Update_Absence_Dim_Evnt_Q ....');
2323 --
2324 Update_Absence_Dim_Evnt_Q
2325 (
2326 p_assignment_id => p_assignment_id
2327 ,p_start_date => p_start_date
2328 ,p_sub_evt_grp_tbl => l_sub_evt_grp_tbl
2329 );
2330 --
2331 ELSE
2332 --
2333 dbg('Absence Events have NOT been found ....');
2334 --
2335 END IF;
2336 --
2337 END Process_absence_dim_changes;
2338 --
2339 -- ----------------------------------------------------------------------------
2340 -- Capture_Events
2341 -- Main Entry point for capturing events for a given assignment
2342 -- ============================================================================
2343 -- This procedure is the main contoling processing of a given
2344 -- assignment. It is called by process_range, the entry point for handling a
2345 -- single assignment called by the child multithreading process.
2346 --
2347 PROCEDURE capture_events
2348 (p_assignment_id NUMBER
2349 ,p_start_date DATE)
2350 IS
2351 --
2352 l_master_events_table pay_interpreter_pkg.t_detailed_output_table_type;
2353 --
2354 BEGIN
2355 --
2356 interpret_all_asgnmnt_changes(p_assignment_id
2357 ,p_start_date
2358 ,l_master_events_table);
2359 --
2360 Process_supervisor_events(p_assignment_id
2361 ,p_start_date
2362 ,l_master_events_table);
2363 --
2364 Process_assgnmnt_evnt_changes(p_assignment_id
2365 ,p_start_date
2366 ,l_master_events_table);
2367 --
2368 Process_absence_dim_changes(p_assignment_id
2369 ,p_start_date
2370 ,l_master_events_table);
2371 --
2372 END;
2373 --
2374 -- ----------------------------------------------------------------------------
2375 -- run_for_bg
2376 -- Test procedure to run for all assignments consecutively
2377 -- ============================================================================
2378 --
2379 PROCEDURE run_for_bg(
2380 p_business_group_id IN NUMBER
2381 ,p_collect_from IN DATE
2382 )
2383 IS
2384 --
2385 CURSOR asg_csr IS
2386 SELECT DISTINCT assignment_id
2387 FROM per_all_assignments_f
2388 WHERE assignment_type = 'E'
2389 AND business_group_id = NVL(p_business_group_id, business_group_id)
2390 AND (effective_start_date >= p_collect_from
2391 OR effective_end_date >= p_collect_from);
2392 --
2393 BEGIN
2394 --
2395 FOR asg_rec IN asg_csr LOOP
2396 --
2397 capture_events(
2398 p_assignment_id => asg_rec.assignment_id
2399 ,p_start_date => SYSDATE) ;
2400 --
2401 END LOOP;
2402 --
2403 COMMIT;
2404 --
2405 END run_for_bg;
2406 --
2407 -- ----------------------------------------------------------------------------
2408 -- run_for_asg
2409 -- Debugging procedure to run for a single assignment
2410 -- ============================================================================
2411 --
2412 PROCEDURE run_for_asg(
2413 p_assignment_id IN NUMBER
2414 ,p_capture_from_date IN DATE
2415 )
2416 IS
2417 --
2418 BEGIN
2419 --
2420 g_master_event_group_id := get_event_group_id('HRI_ASG_MASTER_GROUP');
2421 g_sprvsr_change_event_grp_id := get_event_group_id('HRI_SUPERVISOR_EVENTS');
2422 g_assgnmnt_evnt_event_grp_id := get_event_group_id('HRI_ASG_EVNTS_FCT');
2423 g_absence_dim_event_grp_id := get_event_group_id('HRI_ABSENCE_EVENTS');
2424 --
2425 g_prd_of_srvc_table_id := get_dated_table_id('PER_PERIODS_OF_SERVICE');
2426 g_appraisal_table_id := get_dated_table_id('PER_APPRAISALS');
2427 g_perf_review_table_id := get_dated_table_id('PER_PERFORMANCE_REVIEWS');
2428 g_asg_table_id := get_dated_table_id('PER_ALL_ASSIGNMENTS_F');
2429 g_person_type_table_id := get_dated_table_id('PER_PERSON_TYPE_USAGES_F');
2430 --
2431 g_absence_attendance_table_id := get_dated_table_id('PER_ABSENCE_ATTENDANCES');
2432 --
2433 capture_events
2434 (
2435 p_assignment_id => p_assignment_id
2436 ,p_start_date => p_capture_from_date
2437 );
2438 --
2439 -- Commit is okay here as we are not executing the PYUGEN portion of this
2440 -- package.
2441 --
2442 COMMIT;
2443 --
2444 END run_for_asg;
2445 --
2446 -- Decides whether to full refresh
2447 --
2448 FUNCTION get_full_refresh_value RETURN VARCHAR2
2449 IS
2450 --
2451 -- Indicators showing whether particular tables should be fully or
2452 -- incrementally refreshed.
2453 --
2454 l_suph_full_refresh VARCHAR2(30);
2455 l_asgn_full_refresh VARCHAR2(30);
2456 l_spst_full_refresh VARCHAR2(30);
2457 l_absc_full_refresh VARCHAR2(30);
2458 --
2459 BEGIN
2460 --
2461 -- Get full refresh value for each of dependent tables
2462 --
2463 dbg('Getting full refresh value for each of dependent tables ...');
2464 --
2465 l_suph_full_refresh := hri_oltp_conc_param.get_parameter_value
2466 (p_parameter_name => 'FULL_REFRESH',
2467 p_process_table_name => 'HRI_CS_SUPH');
2468 l_asgn_full_refresh := hri_oltp_conc_param.get_parameter_value
2469 (p_parameter_name => 'FULL_REFRESH',
2470 p_process_table_name => 'HRI_MB_ASGN_EVENTS_CT');
2471 l_spst_full_refresh := hri_oltp_conc_param.get_parameter_value
2472 (p_parameter_name => 'FULL_REFRESH',
2473 p_process_table_name => 'HRI_CL_WKR_SUP_STATUS_CT');
2474 l_absc_full_refresh := hri_oltp_conc_param.get_parameter_value
2475 (p_parameter_name => 'FULL_REFRESH',
2476 p_process_table_name => 'HRI_CS_ABSENCE_CT');
2477 --
2478 -- Only do the following if you are in debug mode for information purposes
2479 --
2480 IF g_debug_flag = 'Y'
2481 THEN
2482 --
2483 msg('l_suph_full_refresh: '||l_suph_full_refresh);
2484 msg('l_asgn_full_refresh: '||l_asgn_full_refresh);
2485 msg('l_spst_full_refresh: '||l_spst_full_refresh);
2486 msg('l_absc_full_refresh: '||l_absc_full_refresh);
2487 --
2488 END IF;
2489 --
2490 -- If any of these processes is incremental then
2491 -- event capture must be incremental
2492 --
2493 IF (l_suph_full_refresh = 'N' OR
2494 l_asgn_full_refresh = 'N' OR
2495 l_spst_full_refresh = 'N' OR
2496 l_absc_full_refresh = 'N')
2497 THEN
2498 --
2499 dbg('Return ''N'' to indicate incremental refresh should take place.');
2500 --
2501 RETURN 'N';
2502 --
2503 ELSE
2504 --
2505 dbg('Return ''Y'' to indicate full refresh should take place.');
2506 --
2507 RETURN 'Y';
2508 --
2509 END IF;
2510 --
2511 END get_full_refresh_value;
2512 --
2513 --
2514 -- -----------------------------------------------------------------------------
2515 --
2516 -- -----------------------------------------------------------------------------
2517 -- -----------------------------------------------------------------------------
2518 -- -----------------------------------------------------------------------------
2519 -- Multithreading Calls --
2520 -- -----------------------------------------------------------------------------
2521 -- The Multithreading Utility Provides the Framework for processing collection
2522 -- using multiple threads. The sequence of operation performed by the utility are
2523 -- a) Invoke the PRE_PROCESS procedure to initialize the global variables and
2524 -- return a SQL based on which the processing ranges will be created.
2525 -- In case of Foundation HR environment or when the process is being run in
2526 -- full refresh mode the process will not return any SQL. Therefore the
2527 -- mulithtreading utility will not invoke the PROCESS_RANGE and POST_PROCESS
2528 -- process.
2529 -- b) Invoke the PROCESS_RANGE procedure to process the assignments in the range
2530 -- This part is done by multiple threads
2531 -- c) Invoke the POST_PROCESS procedure to perform the post processing tasks
2532 -- -----------------------------------------------------------------------------
2533 -- -----------------------------------------------------------------------------
2534 --
2535 --
2536 -- ----------------------------------------------------------------------------
2537 -- SET_PARAMETERS
2538 -- sets up parameters required for the events capture processes
2539 -- Sets up global list of parameters, this is the way that parameters need
2540 -- to be set up for collecting incremental events by HRI multithreading utility
2541 -- ----------------------------------------------------------------------------
2542 --
2543 PROCEDURE set_parameters( p_mthd_action_id IN NUMBER,
2544 p_called_from IN VARCHAR2 default null)
2545 IS
2546 --
2547 l_bis_start_date DATE;
2548 l_bis_end_date DATE; -- Dummy variable return value ignored
2549 l_period_from DATE; -- Dummy variable return value ignored
2550 l_period_to DATE; -- Dummy variable return value ignored
2551 l_message fnd_new_messages.message_text%TYPE;
2552 --
2553 BEGIN
2554 --
2555 dbg('Setting parameters ...');
2556 --
2557 -- If parameters haven't already been set, then set them
2558 --
2559 IF p_called_from = 'PRE_PROCESS'
2560 THEN
2561 --
2562 dbg('Parameters haven''t been set yet, so set them ...');
2563 --
2564 -- Populate the multithread action arrays
2565 --
2566 g_mthd_action_array := hri_opl_multi_thread.get_mthd_action_array(p_mthd_action_id);
2567 --
2568 -- Decide whether to full refresh
2569 --
2570 g_full_refresh := get_full_refresh_value;
2571 dbg('Full refresh: ' || g_full_refresh);
2572 --
2573 -- Get the profile value which determines whether Archiving has been turned on
2574 --
2575 g_enable_archive_flag := NVL(fnd_profile.value('HRI_SET_EVENTS_ARCHIVE'),'N');
2576 --
2577 -- 3658545 The Events Collection process should populate the events queues
2578 -- based on the following profile
2579 --
2580 -- a) HRI:Populate Assignment Events Queue
2581 -- b) HRI:Populate Supervisor Hierarchy Events Queue
2582 -- c) HRI:Populate Supervisor Status History Events Queue
2583 --
2584 -- The process will only populate the queues for which the profile has been enabled.
2585 -- However, as the Supervisor Status History collection is dependent on assignment events
2586 -- fact table, even if HRI:Populate Assignment Events Queue profile has not been enabled
2587 -- the Assignment Events Queue will be populated.
2588 --
2589 --
2590 -- Get the profile value to determine which events queue is to be populated
2591 --
2592 g_col_asg_events_eq := NVL(fnd_profile.value('HRI_COL_ASG_EVENTS_EQ'),'Y');
2593 g_col_sup_hrchy_eq := NVL(fnd_profile.value('HRI_COL_SUP_HRCHY_EQ'),'Y');
2594 g_col_sup_hstry_eq := NVL(fnd_profile.value('HRI_COL_SUP_STATUS_EQ'),'Y');
2595 g_col_absence_events_eq := NVL(fnd_profile.value('HRI_COL_ABSNCE_DIM_EQ'),'Y');
2596 --
2597 IF g_col_asg_events_eq = 'N' AND
2598 g_col_sup_hstry_eq = 'N'
2599 THEN
2600 --
2601 -- msg('Profile HRI:Populate Assignment Events Queue is not enabled.');
2602 -- msg(' Assignment Events queue will not be populated');
2603 --
2604 -- Bug 4105868: Collection Diagnostics
2605 --
2606 fnd_message.set_name('HRI', 'HRI_407162_PRF_ASGEQ_IMPCT');
2607 fnd_message.set_token('PROFILE_NAME', 'HRI:Populate Assignment Events Queue');
2608 --
2609 l_message := fnd_message.get;
2610 --
2611 hri_bpl_conc_log.log_process_info
2612 (p_package_name => 'HRI_OPL_EVENT_CAPTURE'
2613 ,p_msg_type => 'WARNING'
2614 ,p_note => l_message
2615 ,p_msg_group => 'EVT_CPTR'
2616 ,p_msg_sub_group => 'SET_PARAMETERS'
2617 ,p_sql_err_code => SQLCODE);
2618 --
2619 msg(l_message);
2620 --
2621 END IF;
2622 --
2623 IF g_col_sup_hrchy_eq = 'N' THEN
2624 --
2625 -- msg('Profile HRI:Populate Supervisor Hierarchy Events Queue is not enabled.');
2626 -- msg(' Supervisor Hierarch Events queue will not be populated');
2627 --
2628 -- Bug 4105868: Collection Diagnostics
2629 --
2630 fnd_message.set_name('HRI', 'HRI_407163_PRF_SUPH_EQ_IMPCT');
2631 fnd_message.set_token('PROFILE_NAME', 'HRI:Populate Supervisor Hierarchy Events Queue');
2632 --
2633 l_message := fnd_message.get;
2634 --
2635 hri_bpl_conc_log.log_process_info
2636 (p_package_name => 'HRI_OPL_EVENT_CAPTURE'
2637 ,p_msg_type => 'WARNING'
2638 ,p_note => l_message
2639 ,p_msg_group => 'EVT_CPTR'
2640 ,p_msg_sub_group => 'SET_PARAMETERS'
2641 ,p_sql_err_code => SQLCODE);
2642 --
2643 msg(l_message);
2644 --
2645 END IF;
2646 --
2647 dbg('Start collection diagnostics ...');
2648 --
2649 IF g_col_sup_hstry_eq = 'N' THEN
2650 --
2651 -- msg('Profile HRI:Populate Supervisor Status History Events Queue is not enabled.');
2652 -- msg(' Supervisor Status History Events queue will not be populated');
2653 --
2654 -- Bug 4105868: Collection Diagnostics
2655 --
2656 fnd_message.set_name('HRI', 'HRI_407164_PRF_SUPST_EQ_IMPCT');
2657 fnd_message.set_token('PROFILE_NAME', 'HRI:Populate Supervisor Status History Events Queue');
2658 --
2659 l_message := fnd_message.get;
2660 --
2661 hri_bpl_conc_log.log_process_info
2662 (p_package_name => 'HRI_OPL_EVENT_CAPTURE'
2663 ,p_msg_type => 'WARNING'
2664 ,p_note => l_message
2665 ,p_msg_group => 'EVT_CPTR'
2666 ,p_msg_sub_group => 'SET_PARAMETERS'
2667 ,p_sql_err_code => SQLCODE);
2668 --
2669 msg(l_message);
2670 --
2671 ELSIF g_col_sup_hstry_eq = 'Y' AND
2672 g_col_asg_events_eq = 'N'
2673 THEN
2674 --
2675 -- msg('Profile HRI:Populate Supervisor Status History Events Queue is enabled.');
2676 -- msg(' Assignment Events queue will also be populated');
2677 --
2678 -- Bug 4105868: Collection Diagnostics
2679 --
2680 fnd_message.set_name('HRI', 'HRI_407293_PRF_SUPST_ENBLD');
2681 --
2682 l_message := fnd_message.get;
2683 --
2684 hri_bpl_conc_log.log_process_info
2685 (p_package_name => 'HRI_OPL_EVENT_CAPTURE'
2686 ,p_msg_type => 'WARNING'
2687 ,p_note => l_message
2688 ,p_msg_group => 'EVT_CPTR'
2689 ,p_msg_sub_group => 'SET_PARAMETERS'
2690 ,p_sql_err_code => SQLCODE);
2691 --
2692 msg(l_message);
2693 --
2694 END IF;
2695 --
2696 IF g_col_absence_events_eq = 'N' THEN
2697 --
2698 -- msg('Profile HRI:Populate Absence Dimension Queue is not enabled.');
2699 -- msg(' Absence Events queue will not be populated');
2700 --
2701 fnd_message.set_name('HRI', 'HRI_407200_PRF_ABS_EQ_IMPCT');
2702 fnd_message.set_token('PROFILE_NAME', 'HRI:Populate Absence Dimension Queue');
2703 --
2704 l_message := fnd_message.get;
2705 --
2706 hri_bpl_conc_log.log_process_info
2707 (p_package_name => 'HRI_OPL_EVENT_CAPTURE'
2708 ,p_msg_type => 'WARNING'
2709 ,p_note => l_message
2710 ,p_msg_group => 'EVT_CPTR'
2711 ,p_msg_sub_group => 'SET_PARAMETERS'
2712 ,p_sql_err_code => SQLCODE);
2713 --
2714 msg(l_message);
2715 --
2716 END IF;
2717 --
2718 dbg('Finished collection diagnostics ...');
2719 --
2720 IF g_col_sup_hstry_eq = 'Y' THEN
2721 --
2722 g_col_asg_events_eq := 'Y';
2723 --
2724 END IF;
2725 --
2726 -- If only Supervisor Hierachy events needs to be populated then set the master group
2727 -- as HRI_SUPERVISOR_EVENTS
2728 --
2729 dbg('Start setting up event groups ...');
2730 --
2731 IF g_col_sup_hrchy_eq = 'Y' AND
2732 g_col_asg_events_eq = 'N' AND
2733 g_col_sup_hstry_eq = 'N' AND
2734 g_col_absence_events_eq = 'N'
2735 THEN
2736 --
2737 dbg('Only supervisor events being collected ...');
2738 --
2739 g_master_event_group_id := get_event_group_id('HRI_SUPERVISOR_EVENTS');
2740 --
2741 g_sprvsr_change_event_grp_id := get_event_group_id('HRI_SUPERVISOR_EVENTS');
2742 --
2743 ELSIF g_col_asg_events_eq = 'Y' AND
2744 g_col_sup_hrchy_eq = 'N' AND
2745 g_col_sup_hstry_eq = 'N' AND
2746 g_col_absence_events_eq = 'N'
2747 THEN
2748 --
2749 dbg('Only assignment events being collected ...');
2750 --
2751 g_master_event_group_id := get_event_group_id('HRI_ASG_EVNTS_FCT');
2752 --
2753 g_assgnmnt_evnt_event_grp_id := get_event_group_id('HRI_ASG_EVNTS_FCT');
2754 --
2755 ELSIF g_col_absence_events_eq = 'Y' AND
2756 g_col_asg_events_eq = 'N' AND
2757 g_col_sup_hrchy_eq = 'N' AND
2758 g_col_sup_hstry_eq = 'N'
2759 THEN
2760 --
2761 dbg('Only absence events being collected ...');
2762 --
2763 g_master_event_group_id := get_event_group_id('HRI_ABSENCE_EVENTS');
2764 --
2765 g_absence_dim_event_grp_id := get_event_group_id('HRI_ABSENCE_EVENTS');
2766 --
2767 ELSE
2768 --
2769 dbg('Do normal event group setup ...');
2770 --
2771 g_master_event_group_id := get_event_group_id('HRI_ASG_MASTER_GROUP');
2772 --
2773 g_sprvsr_change_event_grp_id := get_event_group_id('HRI_SUPERVISOR_EVENTS');
2774 --
2775 g_assgnmnt_evnt_event_grp_id := get_event_group_id('HRI_ASG_EVNTS_FCT');
2776 --
2777 g_absence_dim_event_grp_id := get_event_group_id('HRI_ABSENCE_EVENTS');
2778 --
2779 dbg('Finished doing normal event group setup ...');
2780 --
2781 END IF;
2782 --
2783 dbg('Finished setting up event groups ...');
2784 --
2785 -- Get the dates of the last refresh of this program
2786 --
2787 dbg('Get the dates of the last refresh of this program ...');
2788 --
2789 bis_collection_utilities.get_last_refresh_dates(
2790 c_object_name,
2791 l_bis_start_date,
2792 l_bis_end_date,
2793 l_period_from,
2794 l_period_to);
2795 --
2796 -- If the Events capture process has never been run before then the
2797 -- dates returned by bis_collection_utilities will be NULL, so we
2798 -- need to switch to full refresh mode.
2799 --
2800 dbg('Capture From Date: '||to_char(l_bis_start_date,'MM/DD/YYYY HH24:MI:SS')||'.');
2801 --
2802 IF l_bis_start_date IS NULL
2803 OR
2804 l_period_to IS NULL
2805 THEN
2806 --
2807 -- Set indicator to show that full refresh has not been run,
2808 -- and so we need fail cleanly. This will be cause the HRI
2809 -- Multithreading process to end cleanly.
2810 --
2811 dbg('Setting indocators to show ull refresh has not been run.');
2812 --
2813 g_full_refresh_not_run := TRUE;
2814 g_full_refresh := 'Y';
2815 --
2816 ELSE
2817 --
2818 -- The start of this refresh should be the time at which the last one
2819 -- started running so that any changes made during the last run are
2820 -- picked up by this one. The end should be now
2821 --
2822 -- 3696594 changed the capture from date to be start_date of the
2823 -- last process
2824 --
2825 dbg('Setting g_capture_from_date to start date of last run.');
2826 --
2827 g_capture_from_date := l_bis_start_date;
2828 --
2829 END IF;
2830 --
2831 -- Get the dated table id of the non data tracked tables, this is used for
2832 -- determining the change to effective dates of non datetracked tables
2833 --
2834 dbg(' Get the dated table id of the non data tracked tables ...');
2835 --
2836 g_prd_of_srvc_table_id := get_dated_table_id('PER_PERIODS_OF_SERVICE');
2837 g_appraisal_table_id := get_dated_table_id('PER_APPRAISALS');
2838 g_perf_review_table_id := get_dated_table_id('PER_PERFORMANCE_REVIEWS');
2839 g_asg_table_id := get_dated_table_id('PER_ALL_ASSIGNMENTS_F');
2840 g_person_type_table_id := get_dated_table_id('PER_PERSON_TYPE_USAGES_F');
2841 --
2842 -- 3906029 The event date to be populated in the supervisor hierarchy
2843 -- events queue should not be lesser than the last full refresh date
2844 -- or the minimum date in the hierarchy
2845 --
2846 g_min_suph_date := get_min_suph_date;
2847 --
2848 dbg('Store the parameter values for use by slave processes...');
2849 --
2850 UPDATE hri_adm_mthd_actions
2851 SET full_refresh_flag = g_full_refresh,
2852 collect_from_date = g_capture_from_date,
2853 attribute1 = g_master_event_group_id,
2854 attribute2 = g_assgnmnt_evnt_event_grp_id,
2855 attribute3 = g_sprvsr_change_event_grp_id,
2856 attribute4 = g_col_asg_events_eq,
2857 attribute5 = g_col_sup_hrchy_eq,
2858 attribute6 = g_col_sup_hstry_eq,
2859 attribute7 = g_enable_archive_flag,
2860 attribute8 = g_min_suph_date,
2861 attribute9 = g_prd_of_srvc_table_id,
2862 attribute10 = g_appraisal_table_id,
2863 attribute11 = g_perf_review_table_id,
2864 attribute12 = g_asg_table_id,
2865 attribute13 = g_person_type_table_id -- Dated table id of PER_PERSON_TYPE_USAGES_F
2866 WHERE mthd_action_id = p_mthd_action_id;
2867 --
2868 dbg('Completed storing the parameter values for use by the slave processes ...');
2869 --
2870 COMMIT;
2871 --
2872 -- Parameters have already been set so don't need to be set again.
2873 --
2874 ELSIF (g_capture_from_date IS NULL) THEN
2875 --
2876 dbg('Set parameters has been called from a slave process. Retrieve '||
2877 'the parameter values.');
2878 --
2879 -- Populate the multithread action arrays
2880 --
2881 g_mthd_action_array := hri_opl_multi_thread.get_mthd_action_array(p_mthd_action_id);
2882 g_full_refresh := 'N';
2883 --
2884 -- Populate the global variables
2885 --
2886 g_capture_from_date := g_mthd_action_array.collect_from_date;
2887 g_master_event_group_id := g_mthd_action_array.attribute1;
2888 g_assgnmnt_evnt_event_grp_id := g_mthd_action_array.attribute2;
2889 g_sprvsr_change_event_grp_id := g_mthd_action_array.attribute3;
2890 g_col_asg_events_eq := g_mthd_action_array.attribute4;
2891 g_col_sup_hrchy_eq := g_mthd_action_array.attribute5;
2892 g_col_sup_hstry_eq := g_mthd_action_array.attribute6;
2893 g_enable_archive_flag := g_mthd_action_array.attribute7;
2894 g_min_suph_date := g_mthd_action_array.attribute8;
2895 --
2896 -- ID of table on which HRI PEM triggers can be created
2897 --
2898 g_prd_of_srvc_table_id := g_mthd_action_array.attribute9;
2899 g_appraisal_table_id := g_mthd_action_array.attribute10;
2900 g_perf_review_table_id := g_mthd_action_array.attribute11;
2901 g_asg_table_id := g_mthd_action_array.attribute12;
2902 g_person_type_table_id := g_mthd_action_array.attribute13;
2903 --
2904 dbg('Finished retrieving parameter values.');
2905 --
2906 END IF;
2907 --
2908 END set_parameters;
2909 --
2910 -- ----------------------------------------------------------------------------
2911 -- PRE_PROCESS
2912 -- This procedure includes all the logic required for performing the pre_process
2913 -- task of HRI multithreading utility.
2914 -- ----------------------------------------------------------------------------
2915 --
2916 PROCEDURE PRE_PROCESS(
2917 --
2918 p_mthd_action_id IN NUMBER,
2919 p_sqlstr OUT NOCOPY VARCHAR2) IS
2920 --
2921 l_dummy1 VARCHAR2(2000);
2922 l_dummy2 VARCHAR2(2000);
2923 l_schema VARCHAR2(400);
2924 --
2925 BEGIN
2926 --
2927 -- Set up the parameters
2928 --
2929 set_parameters( p_mthd_action_id => p_mthd_action_id,
2930 p_called_from => 'PRE_PROCESS');
2931 --
2932 -- In case the process is running in a Foundation HR environment run
2933 -- the post_process to update the bis_refresh_log table and return without
2934 -- returning any SQL. The mulithreading utility will then not invoke the
2935 -- PROCESS_RANGES and POST_PROCESS calls
2936 --
2937 IF g_mthd_action_array.foundation_hr_flag = 'Y' THEN
2938 --
2939 -- This process is not supported in Shared HR mode, update the
2940 -- bis refresh log table and return. The multithreading utility
2941 -- does not do any processing if no SQL is returned
2942 --
2943 dbg('Foundation HR environment found');
2944 post_process (p_mthd_action_id => p_mthd_action_id);
2945 --
2946 ELSIF g_full_refresh = 'Y'
2947 OR g_full_refresh_not_run
2948 THEN
2949 --
2950 dbg('calling full refresh');
2951 --
2952 -- In case the process is running in full refresh mode, directly call the
2953 -- full_refresh procedure and return without returning any SQL. The mulithreading
2954 -- utility will then not invoke the PROCESS_RANGES and POST_PROCESS calls
2955 --
2956 full_refresh (p_refresh_to_date => NULL);
2957 --
2958 ELSIF (g_col_asg_events_eq = 'N' AND
2959 g_col_sup_hrchy_eq = 'N' AND
2960 g_col_sup_hstry_eq = 'N' AND
2961 g_col_absence_events_eq = 'N')
2962 THEN
2963 --
2964 -- The events queue profile have been set, so events are not to be collected.
2965 -- The multithreading utility does not do any processing if no SQL is returned
2966 --
2967 dbg('All the events queues profiles have been set to ''N''');
2968 post_process(p_mthd_action_id => p_mthd_action_id);
2969 --
2970 ELSE
2971 --
2972 -- The SELECT statement built up underneath populates the out parameter sqlstr
2973 -- which is used by the utility to generate the range of assignments to be
2974 -- processed.
2975 --
2976 -- This is the normal execution path when the process is run correctly
2977 -- after a full refresh.
2978 --
2979 -- Generate a SQL statement that SELECTs all of the DISTINCT assignments
2980 -- that have had events in the pay_process_events table, since the last
2981 -- incremental refresh.
2982 --
2983 -- 3703498 Added restriction so that only assignments with changes that are
2984 -- being tracked in the event group are processed
2985 --
2986 p_sqlstr :=
2987 'SELECT /*+ parallel(ppe , default, default) */ DISTINCT
2988 ppe.assignment_id object_id
2989 FROM pay_process_events ppe
2990 WHERE ppe.creation_date
2991 BETWEEN to_date('''
2992 || to_char(g_capture_from_date, 'DD-MON-YYYY HH24:MI:SS')
2993 || ''',''DD-MON-YYYY HH24:MI:SS'')
2994 AND to_date('''
2995 || to_char(g_end_of_time, 'DD-MON-YYYY HH24:MI:SS')
2996 || ''',''DD-MON-YYYY HH24:MI:SS'')
2997 AND EXISTS (SELECT distinct event_update_id
2998 FROM pay_datetracked_events pde,
2999 pay_event_updates peu
3000 WHERE pde.event_group_id = '||g_master_event_group_id||'
3001 AND pde.dated_table_id = peu.dated_table_id
3002 AND ppe.event_update_id = peu.event_update_id )
3003 ORDER BY ppe.assignment_id';
3004 --
3005 END IF;
3006 --
3007 dbg(p_sqlstr);
3008 --
3009 -- 4357755
3010 -- The central refresh procedure should be initialized so that entries
3011 -- about the process are correctly inserted into the bis refresh log table
3012 -- This information is used to determine the start time of the next process.
3013 --
3014 hri_bpl_conc_log.record_process_start(c_object_name);
3015 --
3016 dbg('Exiting pre_process');
3017 --
3018 END PRE_PROCESS;
3019 --
3020 -- ----------------------------------------------------------------------------
3021 -- PROCESS_RANGE
3022 -- This procedure is dynamically the HRI multithreading utility child threads
3023 -- for processing the assignment ranges. The procedure manages the mulithreading
3024 -- ranges and invokes the overloaded process_range procedure to process the
3025 -- ranges.
3026 -- ----------------------------------------------------------------------------
3027 --
3028 PROCEDURE process_range(
3029 errbuf OUT NOCOPY VARCHAR2
3030 ,retcode OUT NOCOPY NUMBER
3031 ,p_mthd_action_id IN NUMBER
3032 ,p_mthd_range_id IN NUMBER
3033 ,p_start_object_id IN NUMBER
3034 ,p_end_object_id IN NUMBER)
3035 IS
3036 --
3037 l_error_step NUMBER;
3038 l_mthd_range_id NUMBER;
3039 l_start_object_id NUMBER;
3040 l_end_object_id NUMBER;
3041 --
3042 BEGIN
3043 --
3044 dbg('Inside process_range');
3045 --
3046 set_parameters(p_mthd_action_id);
3047 --
3048 dbg('processing range='||p_mthd_range_id);
3049 --
3050 process_range(p_start_object_id => p_start_object_id
3051 ,p_end_object_id => p_end_object_id);
3052 --
3053 errbuf := 'SUCCESS';
3054 retcode := 0;
3055 --
3056 dbg('Exiting process_range');
3057 --
3058 EXCEPTION
3059 when others then
3060 dbg('Error at step '||l_error_step );
3061 msg(sqlerrm);
3062 --
3063 errbuf := SQLERRM;
3064 retcode := SQLCODE;
3065 --
3066 raise;
3067 --
3068 END process_range;
3069 --
3070 -- ----------------------------------------------------------------------------
3071 -- PROCESS_RANGE
3072 -- This is an overloaded procedure which is invoked by PROCESS_RANGE above.
3073 -- For each of the assignment in the range, this procedure invokes the
3074 -- capture_events process to populate the events queues
3075 -- ----------------------------------------------------------------------------
3076 --
3077 PROCEDURE process_range(p_start_object_id IN NUMBER
3078 ,p_end_object_id IN NUMBER )
3079 IS
3080 --
3081 -- Cursor to get the assignment_id for assignment action for full refresh
3082 --
3083 CURSOR c_asg_to_process IS
3084 SELECT DISTINCT ppe.assignment_id
3085 FROM pay_process_events ppe
3086 WHERE assignment_id between p_start_object_id and p_end_object_id
3087 AND ppe.creation_date BETWEEN g_capture_from_date and g_end_of_time
3088 AND EXISTS (SELECT distinct event_update_id
3089 FROM pay_datetracked_events pde,
3090 pay_event_updates peu
3091 WHERE pde.event_group_id = g_master_event_group_id
3092 AND pde.dated_table_id = peu.dated_table_id
3093 AND ppe.event_update_id = peu.event_update_id );
3094 --
3095 -- Holds assignment from the cursor
3096 --
3097 l_assignment_id NUMBER;
3098 l_change_date DATE;
3099 l_error_step NUMBER;
3100 --
3101 BEGIN
3102 --
3103 --
3104 --
3105 FOR l_asg_to_process in c_asg_to_process LOOP
3106 --
3107 -- For each of the
3108 --
3109 capture_events
3110 (p_assignment_id => l_asg_to_process.assignment_id,
3111 p_start_date => g_capture_from_date);
3112 --
3113 END LOOP;
3114 --
3115 END process_range;
3116 --
3117 -- ----------------------------------------------------------------------------
3118 -- POST_PROCESS
3119 -- This procedure is dynamically invoked by the HRI Multithreading utility.
3120 -- It finishes the processing by updating the BIS_REFRESH_LOG table
3121 -- ----------------------------------------------------------------------------
3122 --
3123 PROCEDURE post_process (p_mthd_action_id NUMBER) IS
3124 --
3125 l_dummy1 VARCHAR2(2000);
3126 l_dummy2 VARCHAR2(2000);
3127 l_schema VARCHAR2(400);
3128 --
3129 --
3130 BEGIN
3131 --
3132 dbg('Inside post_process');
3133 --
3134 set_parameters(p_mthd_action_id);
3135 --
3136 -- 4765258
3137 -- Update the supervisor history events queue with the supervisors
3138 -- to process
3139 --
3140 -- Also check snapshot facts for new snapshot dates
3141 -- and populate EQs accordingly
3142 --
3143 IF (g_full_refresh = 'N') THEN
3144 hri_opl_sup_status_hst.find_changed_supervisors;
3145 check_for_new_snapshot_dates;
3146 END IF;
3147 --
3148 -- 4357755
3149 -- Insert the details of the run in the bis refresh log table. This info is
3150 -- read used to set the start date of next run
3151 --
3152 hri_bpl_conc_log.log_process_end
3153 (
3154 p_status => TRUE
3155 ,p_period_from => g_capture_from_date
3156 ,p_period_to => SYSDATE
3157 );
3158 --
3159 dbg('Exiting post_process ....');
3160 --
3161 commit;
3162 --
3163 END post_process;
3164 --
3165 END HRI_OPL_EVENT_CAPTURE;
[Home] [Help]