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