[Home] [Help]
PACKAGE BODY: APPS.HRI_OPL_BEN_ENRL_ACTN
Source
1 PACKAGE BODY hri_opl_ben_enrl_actn AS
2 /* $Header: hripbeea.pkb 120.1 2005/11/17 03:14:52 bmanyam noship $ */
3 /*++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
4 Name : HRI_OPL_BEN_ENRL_ACTN
5 Purpose : Collect Benefits Enrollments Action Fact.
6
7 Description:
8 We try to use HRI_OPL_MULTI_THREAD utility.
9 Process Flow
10 ===========
11 1. BEFORE MULTI-THREADING
12 --------------------------
13 1.0 PRE_PROCESS
14 Full Refresh
15 1.0.1 Truncate the Events Table.
16 1.0.2 Generate the Person Ranges SQL. (From BEN_PER_IN_LER)
17
18 Incremental Refresh
19 1.0.1 Generate the Person Ranges SQL. (From HRI_MB_BEN_ENRLACTN_CT).
20
21 2. MULTI-THREADING
22 -------------------
23 2.0 PROCESS_RANGE
24 2.0.1 Gets a range of objects (per_in_lers) to process
25 2.0.2 Calls process_range or process_incr_range for each range.
26 2.0.2.1 PROCESS_RANGE (FULL REFRESH)
27 2.0.2.2 PROCESS_INCR_RANGE (INCREMENTAL REFRESH)
28
29 3. AFTER MULTI-THREADING
30 -------------------------
31 3.0 POST_PROCESS
32 3.0.1 Logs process end (success/failure)
33 3.0.2 Purges event queue
34 3.0.3 Full Refresh
35 3.0.4 Incremental Refres
36 3.0.5 Recreates indexes that were dropped in PRE_PROCESS
37 3.0.6 Gathers stats
38 -- ------------------------------------------------------------------------------
39 +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++*/
40
41 --
42 -- Global variables representing parameters
43 --
44 g_refresh_start_date DATE;
45 g_refresh_end_date DATE;
46 g_full_refresh VARCHAR2 (5);
47 g_collect_oe_only VARCHAR2 (5);
48 g_concurrent_flag VARCHAR2 (5);
49 g_debug_flag VARCHAR2 (5);
50 g_global_start_date DATE;
51
52 --
53 -- Set to true to output to a concurrent log file
54 --
55 g_conc_request_flag BOOLEAN := FALSE;
56 --
57 --
58 -- Global end of time date initialization from the package hr_general
59 --
60 g_end_of_time DATE;
61 --
62 -- Global DBI collection start date initialization
63 --
64 g_dbi_collection_start_date DATE;
65 --
66 -- Global Variable for checking if performance rating is to be collected
67 --
68 g_collect_perf_rating VARCHAR2 (30);
69 g_collect_prsn_typ VARCHAR2 (30);
70 --
71 -- Global HRI Multithreading Array
72 --
73 g_mthd_action_array HRI_ADM_MTHD_ACTIONS%rowtype;
74 --
75 -- Global warning indicator
76 --
77 g_raise_warning VARCHAR2 (1);
78 --
79 g_enrlactn_evnt_table VARCHAR2 (50) := 'HRI_MB_BEN_ENRLACTN_CT';
80 g_package VARCHAR2 (50) := 'HRI_OPL_BEN_ENRL_ACTN';
81 -- -----------------------------------------------------------------------------
82 -- Inserts row into concurrent program log if debugging is enabled
83 -- -----------------------------------------------------------------------------
84 PROCEDURE dbg(p_text VARCHAR2) IS
85 BEGIN
86 --
87 HRI_BPL_CONC_LOG.dbg(p_text);
88 --
89 END dbg;
90 --
91 -- -----------------------------------------------------------------------------
92 -- Inserts row into concurrent program log
93 -- -----------------------------------------------------------------------------
94 PROCEDURE output (p_text VARCHAR2)
95 IS
96 --
97 BEGIN
98 --
99 -- Write to the concurrent request log if called from a concurrent request
100 --
101 IF (g_conc_request_flag = TRUE)
102 THEN
103 --
104 -- Put text to log file
105 --
106 fnd_file.put_line (fnd_file.LOG, p_text);
107 --
108 ELSE
109 --
110 hr_utility.set_location (p_text, 999);
111 --
112 END IF;
113 --
114 END output;
115
116 -- ----------------------------------------------------------------------------
117 -- |--------------------------< RUN_SQL_STMT_NOERR >--------------------------|
118 -- ----------------------------------------------------------------------------
119 --
120 -- Runs given sql statement dynamically without raising an error
121 --
122 PROCEDURE run_sql_stmt_noerr( p_sql_stmt VARCHAR2 )
123 IS
124 --
125 BEGIN
126 --
127 EXECUTE IMMEDIATE p_sql_stmt;
128 --
129 EXCEPTION WHEN OTHERS THEN
130 --
131 dbg('Error running sql:');
132 dbg(SUBSTR(p_sql_stmt,1,230));
133 --
134 END run_sql_stmt_noerr;
135 --
136 --
137 -- ----------------------------------------------------------------------------
138 -- SET_PARAMETERS
139 -- sets up parameters required for the process.
140 -- ----------------------------------------------------------------------------
141 --
142 PROCEDURE set_parameters (
143 p_mthd_action_id IN NUMBER,
144 p_mthd_range_id IN NUMBER DEFAULT NULL
145 )
146 IS
147 --
148 l_procedure VARCHAR2(100) := g_package || '.set_parameters';
149 --
150 BEGIN
151 --
152 -- If parameters haven't already been set, then set them
153 --
154 -- dbg('Entering ' || l_procedure);
155 --
156 g_collect_oe_only := HRI_BPL_BEN_UTIL.get_curr_oe_coll_mode;
157 g_global_start_date := HRI_BPL_PARAMETER.get_bis_global_start_date;
158 --
159 IF p_mthd_action_id IS NULL
160 THEN
161 --
162 -- Called from test harness
163 --
164 g_refresh_start_date := bis_common_parameters.get_global_start_date;
165 g_refresh_end_date := hr_general.end_of_time;
166 g_full_refresh := 'Y';
167 g_concurrent_flag := 'Y';
168 g_debug_flag := 'Y';
169 --
170 ELSIF (g_refresh_start_date IS NULL)
171 THEN
172 --
173 g_mthd_action_array := hri_opl_multi_thread.get_mthd_action_array(p_mthd_action_id);
174 g_refresh_start_date := g_mthd_action_array.collect_from_date;
175 g_refresh_end_date := hr_general.end_of_time;
176 g_full_refresh := g_mthd_action_array.full_refresh_flag;
177 g_concurrent_flag := 'Y';
178 g_debug_flag := g_mthd_action_array.debug_flag;
179 --
180 END IF;
181 --
182 -- dbg('Leaving ' || l_procedure);
183 --
184 END set_parameters;
185 --
186 --
187 -- ----------------------------------------------------------------------------
188 -- PRE_PROCESS
189 -- This procedure includes the logic required for performing the pre_process
190 -- task of HRI multithreading utility.
191 -- ----------------------------------------------------------------------------
192 --
193
194 PROCEDURE pre_process (
195 p_mthd_action_id IN NUMBER,
196 p_sqlstr OUT NOCOPY VARCHAR2
197 )
198 IS
199 l_dummy1 VARCHAR2 (4000);
200 l_dummy2 VARCHAR2 (4000);
201 l_schema VARCHAR2 (10);
202 l_procedure VARCHAR2(100) := g_package || '.pre_process';
203 BEGIN
204 --
205 dbg('Entering ' || l_procedure);
206 --
207 -- Set Initialization Parameters.
208 set_parameters (p_mthd_action_id);
209 OUTPUT('Full Refresh Flag : ' || g_full_refresh);
210 OUTPUT('Open Enr Only Flag : ' || g_collect_oe_only);
211
212
213
214 --
215 IF (fnd_installation.get_app_info ('HRI', l_dummy1, l_dummy2, l_schema)) THEN
216 --
217 -- output ('Schema Found: ' || l_schema);
218 -- l_schema := 'BEN';
219
220 -- ---------------------------------------------------------------------------
221 -- Full Refresh Section
222 -- ---------------------------------------------------------------------------
223 IF (g_full_refresh = 'Y') THEN
224 --
225 --
226 -- If 'Collect For Current Open Enrollment Only',
227 -- Follow the same flow as Full_Refresh,
228 -- And avoid truncate tables.
229 IF (g_collect_oe_only = 'Y') THEN
230 -- Return the sql query to fetch PERSON_ID ranges.
231 -- Select all PERSONs for whom an Open Enrollment is either in Started or Processed State
232 -- for the Latest Open LE in each BG.
233 --
234 p_sqlstr := ' ';
235 p_sqlstr := p_sqlstr || 'SELECT DISTINCT pil.person_id object_id';
236 p_sqlstr := p_sqlstr || ' FROM ben_per_in_ler pil,';
237 p_sqlstr := p_sqlstr || ' (SELECT MAX(pil.lf_evt_ocrd_dt) lf_evt_ocrd_dt';
238 p_sqlstr := p_sqlstr || ' , pil.business_group_id';
239 p_sqlstr := p_sqlstr || ' , pil.ler_id';
240 p_sqlstr := p_sqlstr || ' FROM ben_per_in_ler pil, ';
241 p_sqlstr := p_sqlstr || ' ben_ler_f ler ';
242 p_sqlstr := p_sqlstr || ' WHERE pil.ler_id = ler.ler_id ';
243 p_sqlstr := p_sqlstr || ' AND ler.typ_cd = ''SCHEDDO''';
244 p_sqlstr := p_sqlstr || ' AND pil.per_in_ler_stat_cd = ''STRTD''';
245 p_sqlstr := p_sqlstr || ' AND pil.lf_evt_ocrd_dt >= ';
246 p_sqlstr := p_sqlstr || ' TO_DATE('''||TO_CHAR(g_global_start_date,'MM/DD/YYYY') ||''',''MM/DD/YYYY'') ';
247 p_sqlstr := p_sqlstr || ' AND pil.lf_evt_ocrd_dt BETWEEN ler.effective_start_date ';
248 p_sqlstr := p_sqlstr || ' AND ler.effective_end_date ';
249 p_sqlstr := p_sqlstr || ' GROUP BY pil.business_group_id, pil.ler_id ) pil1';
250 p_sqlstr := p_sqlstr || ' WHERE pil.ler_id = pil1.ler_id';
251 p_sqlstr := p_sqlstr || ' AND pil.business_group_id = pil1.business_group_id';
252 p_sqlstr := p_sqlstr || ' AND pil.lf_evt_ocrd_dt = pil1.lf_evt_ocrd_dt ';
253 p_sqlstr := p_sqlstr || ' ORDER BY pil.person_id ';
254 --
255 OUTPUT(' Collect For Current Open ');
256 --
257 ELSE
258 --
259 OUTPUT(' Collect For All Open');
260 OUTPUT(' Disabling Indexes...');
261 -- Disable WHO triggers on Events table
262 --
263 run_sql_stmt_noerr('ALTER TRIGGER ' || g_enrlactn_evnt_table || '_WHO DISABLE');
264 --
265 -- Disable Logs and Indexes
266 --
267 hri_utl_ddl.log_and_drop_indexes(
268 p_application_short_name => 'HRI',
269 p_table_name => g_enrlactn_evnt_table,
270 p_table_owner => l_schema);
271
272 --
273 -- Truncate the table
274 --
275 OUTPUT(' Truncating Tables...');
276 --
277 EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || l_schema || '.' || g_enrlactn_evnt_table;
278 --
279 -- Return the sql query to fetch PERSON_ID ranges.
280 -- Select all PERSONs for whom an Open Enrollment is either in Started or Processed State
281 -- Use Profiles to limit entries base on life event occured date.
282 p_sqlstr := ' ';
283 p_sqlstr := p_sqlstr || 'SELECT DISTINCT pil.person_id object_id ';
284 p_sqlstr := p_sqlstr || ' FROM ben_per_in_ler pil, ';
285 p_sqlstr := p_sqlstr || ' ben_ler_f ler ';
286 p_sqlstr := p_sqlstr || ' WHERE pil.ler_id = ler.ler_id ';
287 p_sqlstr := p_sqlstr || ' AND ler.typ_cd = ''SCHEDDO'' ';
288 p_sqlstr := p_sqlstr || ' AND pil.per_in_ler_stat_cd IN (''STRTD'',''PROCD'') ';
289 p_sqlstr := p_sqlstr || ' AND pil.lf_evt_ocrd_dt >= ';
290 p_sqlstr := p_sqlstr || ' TO_DATE('''||TO_CHAR(g_global_start_date,'MM/DD/YYYY') || ''',''MM/DD/YYYY'') ';
291 p_sqlstr := p_sqlstr || ' AND EXISTS (SELECT 1 ';
292 p_sqlstr := p_sqlstr || ' FROM ben_prtt_enrt_actn_f pea ';
293 p_sqlstr := p_sqlstr || ' WHERE pea.per_in_ler_id = pil.per_in_ler_id ) ';
294 p_sqlstr := p_sqlstr || ' ORDER BY pil.person_id ';
295 --
296 END IF;
297 ELSE
298 -- ---------------------------------------------------------------------------
299 -- Start of Incremental Refresh Section
300 -- ---------------------------------------------------------------------------
301 --
302 -- Return the sql query to fetch PERSON_ID ranges
303 --
304 p_sqlstr := ' ';
305 p_sqlstr := p_sqlstr || ' SELECT DISTINCT peaq.person_id object_id ';
306 p_sqlstr := p_sqlstr || ' FROM hri_eq_ben_enrlactn_evts peaq ';
307 p_sqlstr := p_sqlstr || ' ORDER BY peaq.person_id ';
308 --
309 --
310 END IF;
311 END IF;
312 --
313 dbg('Leaving ' || l_procedure);
314 --
315 EXCEPTION
316 --
317 WHEN OTHERS
318 THEN
319 output ('Exception in pre_process: ' || substr(SQLERRM,1,200));
320 --
321 END pre_process;
322
323 --
324 -- ----------------------------------------------------------------------------
325 -- COLLECT_ENRLACTN_EVT (INCREMENTAL REFRESH Overloaded)
326 -- This procedure includes the logic for collecting Eligibility
327 -- and Enrollment Events Fact.
328 -- ----------------------------------------------------------------------------
329 --
330 PROCEDURE collect_enrlactn_evt (p_person_id NUMBER, p_per_in_ler_id NUMBER)
331 IS
332 --
333 -- Cursor to select all the action items that were completed
334 -- after last refresh
335 --
336 CURSOR c_eac_end_date
337 IS
338 SELECT per_in_ler_id, prtt_enrt_rslt_id, person_id, actn_typ_id,
339 event_date, lf_evt_ocrd_dt, due_dt, actn_typ_cd, rqd_flag,
340 cmpltd_dt, prtt_enrt_actn_id
341 FROM hri_eq_ben_enrlactn_evts peaq
342 WHERE per_in_ler_id = p_per_in_ler_id
343 AND event_cd = 'COMPLETED'
344 AND cmpltd_dt IS NOT NULL;
345 --
346 -- Cursor to select all the action items that were created since
347 -- last refresh
348 --
349 CURSOR c_eac_optional_actn
350 IS
351 SELECT per_in_ler_id, prtt_enrt_rslt_id, person_id, actn_typ_id,
352 event_date, lf_evt_ocrd_dt, due_dt, actn_typ_cd, rqd_flag,
353 cmpltd_dt, prtt_enrt_actn_id
354 FROM hri_eq_ben_enrlactn_evts peaq
355 WHERE per_in_ler_id = p_per_in_ler_id AND event_cd = 'INSERTED';
356 --
357 -- Cursor to select all action items that were zapped since last refresh
358 -- 1. Delete the enrollment (bepearhi.pkb logs the event in queue)
359 -- 2. Void the life event (bepilrhi.pkb logs the event in queue)
360 --
361 cursor c_peac is
362 SELECT peaq.lf_evt_ocrd_dt, peaq.person_id,
363 peaq.per_in_ler_id, peaq.PRTT_ENRT_ACTN_ID
364 FROM hri_eq_ben_enrlactn_evts peaq
365 WHERE peaq.per_in_ler_id = p_per_in_ler_id
366 AND peaq.person_id = p_person_id
367 AND peaq.event_cd IN ('ZAP');
368 --
369 -- Variables for HRI_EQ_BEN_ENRLACTN_EVTS
370 --
371 l_per_in_ler_id_tab g_per_in_ler_id_tab_type;
372 l_prtt_enrt_rslt_id_tab g_prtt_enrt_rslt_id_tab_type;
373 l_person_id_tab g_person_id_tab_type;
374 l_actn_typ_id_tab g_actn_typ_id_tab_type;
375 l_event_date_tab g_event_date_tab_type;
376 l_lf_evt_ocrd_dt_tab g_lf_evt_ocrd_dt_tab_type;
377 l_due_dt_tab g_due_dt_tab_type;
378 l_actn_typ_cd_tab g_actn_typ_cd_tab_type;
379 l_rqd_flag_tab g_rqd_flag_tab_type;
380 l_cmpltd_dt_tab g_cmpltd_dt_tab_type;
381 l_prtt_enrt_actn_id_tab g_prtt_enrt_actn_id_tab_type;
382 l_procedure VARCHAR2(100) := g_package || '.collect_enrlactn_evt [IR]';
383 --
384 -- End of Variables for HRI_EQ_BEN_ENRLACTN_EVTS
385 --
386 dml_errors EXCEPTION;
387 PRAGMA EXCEPTION_INIT (dml_errors, -24381);
388 --
389 --
390 BEGIN
391 --
392 dbg('Entering ' || l_procedure);
393 --
394 -- Step 1. Create action items which are newly created
395 --
396 OPEN c_eac_optional_actn;
397 --
398 FETCH c_eac_optional_actn BULK COLLECT INTO l_per_in_ler_id_tab,
399 l_prtt_enrt_rslt_id_tab,
400 l_person_id_tab,
401 l_actn_typ_id_tab,
402 l_event_date_tab,
403 l_lf_evt_ocrd_dt_tab,
404 l_due_dt_tab,
405 l_actn_typ_cd_tab,
406 l_rqd_flag_tab,
407 l_cmpltd_dt_tab,
408 l_prtt_enrt_actn_id_tab;
409 --
410 CLOSE c_eac_optional_actn;
411 --
412 IF l_per_in_ler_id_tab.COUNT > 0
413 THEN
414 --
415 FORALL i IN l_per_in_ler_id_tab.FIRST .. l_per_in_ler_id_tab.LAST SAVE EXCEPTIONS
416 --
417 INSERT INTO hri_mb_ben_enrlactn_ct
418 ( sspnd_ind,
419 actn_item_ind,
420 interim_ind,
421 change_date,
422 effective_start_date,
423 effective_end_date,
424 person_id,
425 asnd_lf_evt_dt,
426 actn_typ_cd,
427 compobj_sk_pk,
428 enrt_perd_id,
429 actn_typ_id,
430 prtt_enrt_rslt_id,
431 prtt_enrt_actn_id,
432 per_in_ler_id,
433 interim_enrt_rslt_id,
434 interim_compobj_sk_pk, due_dt
435 )
436 ( SELECT (CASE WHEN (l_rqd_flag_tab (i) = 'Y'
437 AND l_cmpltd_dt_tab (i) IS NULL)
438 THEN 1
439 ELSE 0 END) sspnd_ind,
440 (CASE WHEN (l_cmpltd_dt_tab (i) IS NULL) -- 4541338
441 THEN 1
442 ELSE 0 END ) actn_item_ind,
443 DECODE (pen.rplcs_sspndd_rslt_id, NULL, 0, 1),
444 l_event_date_tab (i),
445 --l_event_date_tab (i),
446 pen.effective_start_Date,
447 hr_api.g_eot,
448 l_person_id_tab (i),
449 l_lf_evt_ocrd_dt_tab (i),
450 l_actn_typ_cd_tab (i),
451 copd.compobj_sk_pk compobj_sk_pk,
452 enpd.enrt_perd_id,
453 l_actn_typ_id_tab (i),
454 pen.prtt_enrt_rslt_id,
455 l_prtt_enrt_actn_id_tab (i),
456 l_per_in_ler_id_tab (i),
457 pen.rplcs_sspndd_rslt_id,
458 copd_int.compobj_sk_pk interim_compobj_sk_pk,
459 l_due_dt_tab (i)
460 FROM ben_prtt_enrt_rslt_f pen,
461 hri_cs_time_benrl_prd_ct enpd,
462 ben_prtt_enrt_rslt_f pen_int,
463 hri_cs_compobj_ct copd_int,
464 hri_cs_compobj_ct copd,
465 ben_opt_f opt,
466 ben_pl_f pln
467 WHERE pen.per_in_ler_id = l_per_in_ler_id_tab (i)
468 AND pen.prtt_enrt_rslt_id = l_prtt_enrt_rslt_id_tab (i)
469 AND enpd.pgm_id = pen.pgm_id
470 AND l_lf_evt_ocrd_dt_tab (i) = enpd.asnd_lf_evt_dt
471 AND ( copd.oipl_id = pen.oipl_id
472 OR (pen.oipl_id IS NULL AND copd.oipl_id = -1)
473 )
474 AND copd.pl_id = pen.pl_id
475 AND copd.pgm_id = pen.pgm_id
476 AND opt.opt_id(+) = copd.opt_id
477 AND pln.pl_id = copd.pl_id
478 AND l_lf_evt_ocrd_dt_tab (i) BETWEEN opt.effective_start_date(+)
479 AND opt.effective_end_date(+)
480 AND l_lf_evt_ocrd_dt_tab (i) BETWEEN pln.effective_start_date
481 AND pln.effective_end_date
482 AND pen.rplcs_sspndd_rslt_id = pen_int.prtt_enrt_rslt_id(+)
483 /* AND copd_int.oipl_id(+) = NVL (pen_int.oipl_id, -1) */
484 AND ( copd_int.oipl_id = pen_int.oipl_id
485 OR (pen_int.oipl_id IS NULL AND NVL(copd_int.oipl_id,-1) = -1)
486 )
487 AND copd_int.pgm_id(+) = pen_int.pgm_id
488 AND copd_int.pl_id(+) = pen_int.pl_id
489 AND pen.prtt_enrt_rslt_stat_cd IS NULL
490 /* Bug 4562628 */
491 and ( pen_int.effective_start_date is null
492 or (pen.effective_Start_date between pen_int.effective_Start_date
493 and pen_int.effective_end_date)
494 )
495 /* Bug 4562628 */
496 );
497 --
498 --
499 END IF;
500 --
501 dbg('Created New Action Items');
502 --
503 -- Step 2. End date exising records to one day prior.
504 --
505 OPEN c_eac_end_date;
506 --
507 FETCH c_eac_end_date BULK COLLECT INTO l_per_in_ler_id_tab,
508 l_prtt_enrt_rslt_id_tab,
509 l_person_id_tab,
510 l_actn_typ_id_tab,
511 l_event_date_tab,
512 l_lf_evt_ocrd_dt_tab,
513 l_due_dt_tab,
514 l_actn_typ_cd_tab,
515 l_rqd_flag_tab,
516 l_cmpltd_dt_tab,
517 l_prtt_enrt_actn_id_tab;
518 --
519 CLOSE c_eac_end_date;
520
521 IF l_per_in_ler_id_tab.COUNT > 0
522 THEN
523 --
524 FORALL i IN l_per_in_ler_id_tab.FIRST .. l_per_in_ler_id_tab.LAST SAVE EXCEPTIONS
525 UPDATE hri_mb_ben_enrlactn_ct peac
526 SET peac.effective_end_date = l_event_date_tab (i) - 1
527 WHERE peac.per_in_ler_id = l_per_in_ler_id_tab (i)
528 AND peac.prtt_enrt_rslt_id = l_prtt_enrt_rslt_id_tab (i)
529 AND peac.prtt_enrt_actn_id = l_prtt_enrt_actn_id_tab (i)
530 AND peac.actn_typ_id = l_actn_typ_id_tab (i)
531 AND peac.asnd_lf_evt_dt = l_lf_evt_ocrd_dt_tab (i);
532 --
533 END IF;
534 --
535 dbg ('End-dated Existing Records To A Day Prior');
536 --
537 -- Step 3. Delete Records for which Results were ZAPPED.
538 --
539 -- Case (i) The ZAP event logged by bepilrhi.pkb >> hrieqele.pkb
540 -- The record in event queue will not have PRTT_ENRT_ACTN_ID
541 -- and PRTT_ENRT_RSLT_ID populated.
542 -- But will have only PER_IN_LER_ID populated. In such case
543 -- delete all records from fact for the given PER_IN_LER_ID
544 -- Case (ii) The ZAP event logged by benelinf.pkb >> bepearhi.pkb >> hrieqeea.pkb
545 -- The record in event queue will have both PRTT_ENRT_RSLT_ID
546 -- and PRTT_ENRT_ACTN_ID. Hence delete records in the fact table
547 -- for the given PRTT_ENRT_ACTN_ID
548 --
549 OPEN c_peac;
550 --
551 FETCH c_peac BULK COLLECT INTO l_lf_evt_ocrd_dt_tab,
552 l_person_id_tab,
553 l_per_in_ler_id_tab,
554 l_prtt_enrt_actn_id_tab;
555 --
556 CLOSE c_peac;
557 --
558 IF l_per_in_ler_id_tab.COUNT > 0
559 THEN
560 --
561 FORALL i IN l_per_in_ler_id_tab.FIRST .. l_per_in_ler_id_tab.LAST SAVE EXCEPTIONS
562 DELETE FROM hri_mb_ben_enrlactn_ct peac
563 WHERE peac.asnd_lf_evt_dt = l_lf_evt_ocrd_dt_tab (i)
564 and peac.person_id = l_person_id_tab(i)
565 and peac.per_in_ler_id = l_per_in_ler_id_tab (i)
566 /* See Case(i) and Case(ii) above for clause below */
567 and peac.PRTT_ENRT_ACTN_ID = nvl(l_prtt_enrt_actn_id_tab (i),peac.PRTT_ENRT_ACTN_ID ) ;
568 --
569 end if;
570 --
571 dbg ('Deleted Zapped Records');
572 --
573 --
574 -- output('Incremental Update Completed');
575 --
576 dbg('Leaving ' || l_procedure);
577 --
578 --
579 EXCEPTION
580 --
581 WHEN dml_errors
582 THEN
583 output ('Exception in collect_enrlactn_evt: ' || SUBSTR (SQLERRM, 1, 190));
584 --
585 END collect_enrlactn_evt;
586
587 --
588 -- ----------------------------------------------------------------------------
589 -- COLLECT_ENRLACTN_EVT (FULL REFRESH Overloaded)
590 -- This procedure includes the logic for collecting Eligibility
591 -- and Enrollment Events Fact.
592 -- ----------------------------------------------------------------------------
593 --
594 PROCEDURE collect_enrlactn_evt (p_pil_rec g_pil_rec_type)
595 IS
596 --
597 dml_errors EXCEPTION;
598 PRAGMA EXCEPTION_INIT (dml_errors, -24381);
599 l_procedure VARCHAR2(100) := g_package || '.collect_enrlactn_evt [FR]';
600 --
601 BEGIN
602 --
603 dbg('Entering ' || l_procedure);
604 --
605 IF (g_collect_oe_only = 'Y') THEN
606 -- If Only Current Open Enrollment is being Collected,
607 -- Delete any prior run's stray records, if present.
608 DELETE from hri_mb_ben_enrlactn_ct
609 WHERE person_id = p_pil_rec.person_id
610 AND asnd_lf_evt_dt = p_pil_rec.lf_evt_ocrd_dt
611 AND per_in_ler_id = p_pil_rec.per_in_ler_id;
612 --
613 END IF;
614 -- output('Start Of Full Refresh');
615 -- Populate Benefits Enrollment Actions Fact
616 -- Insert Actions (From Enrollment Action Items BEN_PRTT_ENRT_ACT_F)
617 --
618 INSERT INTO hri_mb_ben_enrlactn_ct
619 (
620 sspnd_ind,
621 actn_item_ind,
622 interim_ind,
623 change_date,
624 effective_start_date,
625 effective_end_date,
626 person_id,
627 asnd_lf_evt_dt,
628 actn_typ_cd,
629 compobj_sk_pk,
630 enrt_perd_id,
631 actn_typ_id,
632 prtt_enrt_rslt_id,
633 prtt_enrt_actn_id,
634 per_in_ler_id,
635 interim_enrt_rslt_id,
636 interim_compobj_sk_pk,
637 due_dt
638 )
639 (
640 SELECT (CASE WHEN (pea.rqd_flag = 'Y'
641 AND pea.cmpltd_dt IS NULL)
642 THEN 1
643 ELSE 0 END) sspnd_ind,
644 (CASE WHEN (pea.cmpltd_dt IS NULL) -- 4541338
645 THEN 1
646 ELSE 0 END ) actn_item_ind,
647 DECODE (pen.rplcs_sspndd_rslt_id, NULL, 0, 1),
648 pea.effective_start_date,
649 pea.effective_start_date,
650 pea.effective_end_date,
651 p_pil_rec.person_id,
652 p_pil_rec.lf_evt_ocrd_dt,
653 act.type_cd,
654 copd.compobj_sk_pk compobj_sk_pk,
655 enpd.enrt_perd_id,
656 pea.actn_typ_id,
657 pen.prtt_enrt_rslt_id,
658 pea.prtt_enrt_actn_id,
659 p_pil_rec.per_in_ler_id,
660 pen.rplcs_sspndd_rslt_id,
661 copd_int.compobj_sk_pk interim_compobj_sk_pk, pea.due_dt
662 FROM ben_prtt_enrt_rslt_f pen,
663 ben_prtt_enrt_actn_f pea,
664 hri_cs_time_benrl_prd_ct enpd,
665 ben_actn_typ act,
666 ben_prtt_enrt_rslt_f pen_int,
667 hri_cs_compobj_ct copd_int,
668 hri_cs_compobj_ct copd,
669 ben_opt_f opt,
670 ben_pl_f pln
671 WHERE pen.per_in_ler_id = p_pil_rec.per_in_ler_id
672 AND pen.prtt_enrt_rslt_id = pea.prtt_enrt_rslt_id
673 AND pea.actn_typ_id = act.actn_typ_id
674 AND enpd.pgm_id = pen.pgm_id
675 AND p_pil_rec.lf_evt_ocrd_dt = enpd.asnd_lf_evt_dt
676 AND ( copd.oipl_id = pen.oipl_id
677 OR (pen.oipl_id IS NULL AND copd.oipl_id = -1)
678 )
679 AND copd.pl_id = pen.pl_id
680 AND copd.pgm_id = pen.pgm_id
681 AND opt.opt_id(+) = copd.opt_id
682 AND pln.pl_id = copd.pl_id
683 AND p_pil_rec.lf_evt_ocrd_dt BETWEEN opt.effective_start_date(+)
684 AND opt.effective_end_date(+)
685 AND p_pil_rec.lf_evt_ocrd_dt BETWEEN pln.effective_start_date
686 AND pln.effective_end_date
687 AND pen.rplcs_sspndd_rslt_id = pen_int.prtt_enrt_rslt_id(+)
688 /* AND copd_int.oipl_id(+) = NVL(pen_int.oipl_id, -1) */
689 AND ( copd_int.oipl_id = pen_int.oipl_id
690 OR (pen_int.oipl_id IS NULL AND NVL(copd_int.oipl_id,-1) = -1)
691 )
692 AND copd_int.pgm_id(+) = pen_int.pgm_id
693 AND copd_int.pl_id(+) = pen_int.pl_id
694 -- AND pen.effective_end_date = hr_api.g_eot /* Bug 4562628 */
695 AND pen.enrt_cvg_thru_dt = hr_api.g_eot
696 AND pen.prtt_enrt_rslt_stat_cd IS NULL
697 /* Bug 4562628 */
698 and pea.effective_Start_date between pen.effective_Start_date and pen.effective_end_date
699 and ( pen_int.effective_start_date is null
700 or (pen.effective_Start_date between pen_int.effective_Start_date
701 and pen_int.effective_end_date)
702 )
703 /* Bug 4562628 */
704 );
705 --
706 -- output ('Full Refresh Completed');
707 --
708 dbg('Leaving ' || l_procedure);
709 --
710 EXCEPTION
711 WHEN dml_errors
712 THEN
713 --
714 output ('Exception in collect_enrlactn_evt : ' || SUBSTR (SQLERRM, 1, 200));
715 --
716 END collect_enrlactn_evt;
717
718 --
719 -- ----------------------------------------------------------------------------
720 -- PROCESS_FULL_RANGE
721 -- Is called in FULL REFRESH mode.
722 -- ----------------------------------------------------------------------------
723 --
724 PROCEDURE process_full_range (
725 p_start_object_id IN NUMBER,
726 p_end_object_id IN NUMBER
727 )
728 IS
729 --
730 CURSOR c_pil
731 IS
732 SELECT pil.per_in_ler_id,
733 pil.person_id,
734 pil.lf_evt_ocrd_dt,
735 pil.per_in_ler_stat_cd,
736 pil.business_group_id
737 FROM ben_per_in_ler pil, ben_ler_f ler
738 WHERE pil.ler_id = ler.ler_id
739 AND ler.typ_cd = 'SCHEDDO'
740 AND pil.per_in_ler_stat_cd IN ('STRTD', 'PROCD')
741 AND pil.lf_evt_ocrd_dt >= g_global_start_date
742 AND pil.person_id BETWEEN p_start_object_id
743 AND p_end_object_id
744 ORDER BY pil.person_id;
745 --
746 CURSOR c_pil_oe IS
747 SELECT pil.per_in_ler_id,
748 pil.person_id,
749 pil.lf_evt_ocrd_dt,
750 pil.per_in_ler_stat_cd,
751 pil.business_group_id
752 FROM ben_per_in_ler pil,
753 (SELECT MAX(pil.lf_evt_ocrd_dt) lf_evt_ocrd_dt
754 , pil.business_group_id
755 , pil.ler_id
756 FROM ben_per_in_ler pil,
757 ben_ler_f ler
758 WHERE pil.ler_id = ler.ler_id
759 AND ler.typ_cd = 'SCHEDDO'
760 AND pil.per_in_ler_stat_cd = 'STRTD'
761 AND pil.lf_evt_ocrd_dt BETWEEN ler.effective_start_date
762 AND ler.effective_end_date
763 GROUP BY pil.business_group_id, pil.ler_id ) pil1
764 WHERE pil.ler_id = pil1.ler_id
765 AND pil.business_group_id = pil1.business_group_id
766 AND pil.lf_evt_ocrd_dt = pil1.lf_evt_ocrd_dt
767 AND pil.lf_evt_ocrd_dt >= g_global_start_date
768 AND pil.person_id BETWEEN p_start_object_id AND p_end_object_id
769 ORDER BY pil.person_id;
770 --
771 l_pil_tbl g_pil_tab_type;
772 --
773 BEGIN
774 --
775 IF (g_collect_oe_only = 'Y') THEN
776 --
777 OPEN c_pil_oe;
778 FETCH c_pil_oe BULK COLLECT INTO l_pil_tbl;
779 CLOSE c_pil_oe;
780 --
781 ELSE
782 --
783 OPEN c_pil;
784 FETCH c_pil BULK COLLECT INTO l_pil_tbl;
785 CLOSE c_pil;
786 --
787 END IF;
788
789 --
790 -- For each PIL collect the Election and Enrollment Information
791 --
792 IF (l_pil_tbl.COUNT > 0)
793 THEN
794 FOR i IN l_pil_tbl.FIRST .. l_pil_tbl.LAST
795 LOOP
796 --
797 -- 1. Load the Enrollments Action Fact
798 --
799 collect_enrlactn_evt (p_pil_rec => l_pil_tbl (i));
800 --
801 END LOOP;
802 END IF;
803 --
804 EXCEPTION
805 WHEN OTHERS
806 THEN
807 output ('Exception in process_range : ' || SUBSTR (SQLERRM, 1, 200));
808 END process_full_range;
809
810 --
811 -- ----------------------------------------------------------------------------
812 -- PROCESS_INCR_RANGE
813 -- This procedure is called in INCREMENTAL REFRESH mode.
814 -- ----------------------------------------------------------------------------
815 --
816 PROCEDURE process_incr_range (
817 p_start_object_id IN NUMBER,
818 p_end_object_id IN NUMBER
819 )
820 IS
821 --
822 CURSOR c_per_in_evt
823 IS
824 SELECT DISTINCT penq.person_id, penq.per_in_ler_id
825 FROM hri_eq_ben_enrlactn_evts penq
826 WHERE penq.person_id BETWEEN p_start_object_id
827 AND p_end_object_id
828 ORDER BY penq.person_id;
829 --
830 l_person_tbl g_number_tab_type;
831 l_pil_tbl g_number_tab_type;
832 --
833 BEGIN
834 --
835 -- 1. Load the Election Events Fact
836 --
837 OPEN c_per_in_evt;
838 FETCH c_per_in_evt BULK COLLECT INTO l_person_tbl, l_pil_tbl;
839 CLOSE c_per_in_evt;
840 --
841 FOR i IN l_person_tbl.FIRST .. l_person_tbl.LAST
842 LOOP
843 --
844 collect_enrlactn_evt (p_person_id => l_person_tbl (i),
845 p_per_in_ler_id => l_pil_tbl (i)
846 );
847 --
848 END LOOP;
849 --
850 l_person_tbl.DELETE;
851 l_pil_tbl.DELETE;
852 --
853 EXCEPTION
854 WHEN OTHERS
855 THEN
856 output ('Exception in process_incr_range : ' || SUBSTR (SQLERRM, 1, 200));
857 END process_incr_range;
858
859 -- ----------------------------------------------------------------------------
860 -- PROCESS_RANGE
861 -- This procedure is dynamically called from HRI Multithreading utility.
862 -- Calls Collection procedures for Election Event and Elibility Enrollment Event Facts
863 -- for All PER_IN_LER_IDs obtained from the thread range.
864 -- ----------------------------------------------------------------------------
865 PROCEDURE process_range (
866 errbuf OUT NOCOPY VARCHAR2,
867 retcode OUT NOCOPY NUMBER,
868 p_mthd_action_id IN NUMBER,
869 p_mthd_range_id IN NUMBER,
870 p_start_object_id IN NUMBER,
871 p_end_object_id IN NUMBER
872 )
873 IS
874 --
875 l_procedure VARCHAR2(100) := g_package || 'process_range';
876 --
877 BEGIN
878 --
879 -- Enable output to concurrent request log
880 --
881 dbg('Entering ' || l_procedure);
882 --
883 g_conc_request_flag := TRUE;
884 --
885 -- 1. Set parameters for this thread.
886 --
887 set_parameters (p_mthd_action_id => p_mthd_action_id,
888 p_mthd_range_id => p_mthd_range_id);
889 --
890 IF g_full_refresh = 'Y'
891 THEN
892 --
893 process_full_range (p_start_object_id => p_start_object_id,
894 p_end_object_id => p_end_object_id);
895 --
896 ELSE
897 --
898 process_incr_range (p_start_object_id => p_start_object_id,
899 p_end_object_id => p_end_object_id);
900 --
901 END IF;
902 --
903 errbuf := 'SUCCESS';
904 retcode := 0;
905 --
906 dbg('Leaving ' || l_procedure);
907 --
908 EXCEPTION
909 WHEN OTHERS
910 THEN
911 --
912 dbg('Error ' || l_procedure);
913 output ('Error encountered while processing range = ' || p_mthd_range_id );
914 output (SQLERRM);
915 errbuf := SQLERRM;
916 retcode := SQLCODE;
917 --
918 RAISE;
919 --
920 END process_range;
921
922 --
923 -- ----------------------------------------------------------------------------
924 -- POST_PROCESS
925 -- This procedure is dynamically invoked by the HRI Multithreading utility.
926 -- It performs all the clean up action for after collection.
927 -- Enable the MV logs
928 -- Purge the Election and Eligibility Events' incremental events queue
929 -- Update BIS Refresh Log
930 -- ----------------------------------------------------------------------------
931 --
932 PROCEDURE post_process (p_mthd_action_id NUMBER)
933 IS
934 --
935 l_dummy1 VARCHAR2 (2000);
936 l_dummy2 VARCHAR2 (2000);
937 l_schema VARCHAR2 (400);
938 --
939 BEGIN
940 --
941 dbg ('Inside post_process');
942 --
943 set_parameters (p_mthd_action_id);
944 OUTPUT('Full Refresh Flag : ' || g_full_refresh);
945 OUTPUT('Open Enr Only Flag : ' || g_collect_oe_only);
946 --
947 IF (fnd_installation.get_app_info ('HRI',l_dummy1,l_dummy2,l_schema)) THEN
948 --
949 IF (g_full_refresh = 'Y') THEN
950 --
951 -- output ('Full Refresh selected - Creating indexes');
952 --
953 HRI_UTL_DDL.recreate_indexes(
954 p_application_short_name => 'HRI',
955 p_table_name => g_enrlactn_evnt_table,
956 p_table_owner => l_schema);
957 --
958 run_sql_stmt_noerr('ALTER TRIGGER ' || g_enrlactn_evnt_table || '_WHO ENABLE');
959 --
960 ELSE
961 --
962 -- Incremental Changes
963 -- Enable the WHO trigger on the events fact table
964 --
965 IF (HRI_BPL_BEN_UTIL.get_archive_events = 'Y') THEN
966 -- If Event Queue is to be be archived add code here
967 dbg ('Archive the events queue');
968 END IF;
969 --
970 END IF;
971 --
972 -- Purge the Events Queue. The events queue needs to be purged
973 -- even after the after full refresh. Recollecting incremental changes
974 -- will be useless if a full refresh has been run.
975 EXECUTE IMMEDIATE 'truncate table ' || l_schema || '.HRI_EQ_BEN_ENRLACTN_EVTS';
976 --
977 END IF;
978 --
979 dbg ('Exiting post_process');
980 --
981 EXCEPTION
982 --
983 WHEN OTHERS THEN
984 --
985 RAISE;
986 --
987 END post_process;
988 --
989 END hri_opl_ben_enrl_actn;