[Home] [Help]
PACKAGE BODY: APPS.HRI_OPL_BEN_ELIG_ENRL
Source
1 PACKAGE BODY HRI_OPL_BEN_ELIG_ENRL AS
2 /* $Header: hripbeec.pkb 120.1 2005/11/14 08:07:22 bmanyam noship $ */
3 --
4 /* ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
5 Name : HRI_OPL_BEN_ELIG_ENRL
6 Purpose : Collect Benefits Election Events Fact and
7 Benefits Eligibility and Enrollments Fact.
8
9 Description:
10 We try to use HRI_OPL_MULTI_THREAD utility.
11 Process Flow
12 ===========
13 1. BEFORE MULTI-THREADING
14 --------------------------
15 1.0 PRE_PROCESS
16 Full Refresh
17 1.0.1 If 'Collect For Current Open Enrollment' profile is set to 'Yes',
18 Fetch PER-IN-LERs corresponding to 'STRTD' LEs.
19 ELSE
20 Truncate the Events Table.
21 Fetch All PER-IN-LERs.
22 1.0.2 Generate the Person Ranges SQL. (From PER_IN_LERs)
23
24 Incremental Refresh
25 1.0.1 Generate the Person Ranges SQL. (From HRI_BEN_EQ_ELIGENRL_EVTS_CT).
26
27 2. MULTI-THREADING
28 -------------------
29 2.0 PROCESS_RANGE
30 2.0.1 Gets a range of objects (per_in_lers) to process
31 2.0.2 Calls process_full_range or process_incr_range for each range.
32 2.0.2.1 PROCESS_FULL_RANGE (Full Refresh)
33 2.0.2.2 PROCESS_INCR_RANGE (Incremental Refresh)
34
35 3. AFTER MULTI-THREADING
36 -------------------------
37 3.0 POST_PROCESS
38 3.0.1 Logs process end (Success/Failure)
39 3.0.2 Purges event queue
40 3.0.3 Full Refresh
41 3.0.4 Incremental Refres
42 3.0.5 Recreates indexes that were dropped in PRE_PROCESS
43 3.0.6 Gathers stats
44 -- +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ */
45 --
46 -- Global variables representing parameters
47 --
48 g_package VARCHAR2(30) := 'HRI_OPL_BEN_ELIG_ENRL.';
49
50 g_refresh_start_date DATE;
51 g_refresh_end_date DATE;
52 g_full_refresh VARCHAR2(5);
53 g_concurrent_flag VARCHAR2(5);
54 g_debug_flag VARCHAR2(5);
55 g_collect_oe_only VARCHAR2(5);
56 g_global_start_date DATE;
57
58 --
59 -- Global end of time date initialization from the package hr_general
60 --
61 g_end_of_time DATE;
62 --
63 -- Global DBI collection start date initialization
64 --
65 g_dbi_collection_start_date DATE;
66 --
67 -- Global Variable for checking if performance rating is to be collected
68 --
69 g_collect_perf_rating VARCHAR2(30);
70 g_collect_prsn_typ VARCHAR2(30);
71 --
72 -- Global HRI Multithreading Array
73 --
74 g_mthd_action_array HRI_ADM_MTHD_ACTIONS%rowtype;
75 --
76 -- Global warning indicator
77 --
78 g_raise_warning VARCHAR2(1);
79 --
80 g_eligenrl_evnt_table VARCHAR2(50) := 'HRI_MB_BEN_ELIGENRL_EVNT_CT';
81 g_elctn_evnt_table VARCHAR2(50) := 'HRI_MB_BEN_ELCTN_EVNT_CT';
82 --
83 g_elctn_evts_eq_table VARCHAR2(50) := 'HRI_EQ_BEN_ELCTN_EVTS';
84 g_eligenrl_evts_eq_table VARCHAR2(50) := 'HRI_EQ_BEN_ELIGENRL_EVTS';
85
86 -- -----------------------------------------------------------------------------
87 -- Inserts row into concurrent program log
88 -- -----------------------------------------------------------------------------
89 PROCEDURE output(p_text VARCHAR2) IS
90 BEGIN
91 --
92 -- DBMS_OUTPUT.PUT_LINE(p_text);
93 HRI_BPL_CONC_LOG.output(p_text);
94 --
95 END output;
96
97 --
98 -- -----------------------------------------------------------------------------
99 -- Inserts row into concurrent program log if debugging is enabled
100 -- -----------------------------------------------------------------------------
101 PROCEDURE dbg(p_text VARCHAR2) IS
102 BEGIN
103 --
104 HRI_BPL_CONC_LOG.dbg(p_text);
105 -- DBMS_OUTPUT.PUT_LINE(p_text);
106 --
107 END dbg;
108 -- ----------------------------------------------------------------------------
109 -- SET_PARAMETERS
110 -- sets up parameters required for the process.
111 -- ----------------------------------------------------------------------------
112 --
113 PROCEDURE set_parameters(p_mthd_action_id IN NUMBER
114 ,p_mthd_range_id IN NUMBER DEFAULT NULL) IS
115 --
116 l_procedure VARCHAR2(100) := g_package || 'set_parameters';
117 --
118 BEGIN
119 --
120 -- If parameters haven't already been set, then set them
121 --
122 -- dbg('Entering ' || l_procedure);
123 --
124 -- Default these values..
125 -- g_full_refresh := 'Y';
126 g_collect_oe_only := HRI_BPL_BEN_UTIL.get_curr_oe_coll_mode;
127 g_global_start_date := HRI_BPL_PARAMETER.get_bis_global_start_date;
128 --
129 IF p_mthd_action_id IS NULL THEN
130 --
131 -- Called from test harness
132 --
133 g_refresh_start_date := bis_common_parameters.get_global_start_date;
134 g_refresh_end_date := hr_general.end_of_time;
135 g_full_refresh := 'Y';
136 g_concurrent_flag := 'Y';
137 g_debug_flag := 'Y';
138 --
139 ELSIF (g_refresh_start_date IS NULL) THEN
140 --
141 g_mthd_action_array := hri_opl_multi_thread.get_mthd_action_array(p_mthd_action_id);
142 g_refresh_start_date := g_mthd_action_array.collect_from_date;
143 g_refresh_end_date := hr_general.end_of_time;
144 g_full_refresh := g_mthd_action_array.full_refresh_flag;
145 g_concurrent_flag := 'Y';
146 g_debug_flag := g_mthd_action_array.debug_flag;
147 --
148 END IF;
149 -- dbg('Leaving ' || l_procedure);
150 --
151 END set_parameters;
152
153 --
154 -- ----------------------------------------------------------------------------
155 -- PRE_PROCESS
156 -- This procedure includes the logic required for performing the pre_process
157 -- task of HRI multithreading utility.
158 -- ----------------------------------------------------------------------------
159 --
160
161 PROCEDURE pre_process
162 (p_mthd_action_id IN NUMBER
163 ,p_sqlstr OUT NOCOPY VARCHAR2) IS
164 l_dummy1 VARCHAR2(4000);
165 l_dummy2 VARCHAR2(4000);
166 l_schema VARCHAR2(10);
167
168 l_procedure VARCHAR2(100) := g_package || 'pre_process';
169
170 l_strtd_events_only VARCHAR2(1000);
171
172 BEGIN
173 dbg('Entering ' || l_procedure);
174 -- Set Initialization Parameters.
175 set_parameters(p_mthd_action_id);
176 OUTPUT('Full Refresh Flag : ' || g_full_refresh);
177 OUTPUT('Open Enr Only Flag : ' || g_collect_oe_only);
178 --
179 -- Disable WHO triggers on Events table
180 IF (fnd_installation.get_app_info('HRI',l_dummy1, l_dummy2, l_schema)) THEN
181 --
182 -- ---------------------------------------------------------------------------
183 -- Full Refresh Section
184 -- ---------------------------------------------------------------------------
185 IF (g_full_refresh = 'Y') THEN
186 --
187 -- If 'Collect For Current Open Enrollment Only',
188 -- Follow the same flow as Full_Refresh,
189 -- Only collect PER_IN_LERs STRTD state.
190 -- And avoid truncate tables.
191 IF (g_collect_oe_only = 'Y') THEN
192 --
193 -- l_strtd_events_only := ' AND pil.per_in_ler_stat_cd = ''STRTD''';
194 -- Change in Logic
195 -- Return the sql query to fetch PERSON_ID ranges.
196 -- Select all PERSONs for whom an Open Enrollment is either in Started or Processed State
197 -- for the Latest Open LE in each BG.
198 --
199 p_sqlstr := ' ';
200 p_sqlstr := p_sqlstr || 'SELECT DISTINCT pil.person_id object_id';
201 p_sqlstr := p_sqlstr || ' FROM ben_per_in_ler pil,';
202 p_sqlstr := p_sqlstr || ' (SELECT MAX(pil.lf_evt_ocrd_dt) lf_evt_ocrd_dt';
203 p_sqlstr := p_sqlstr || ' , pil.business_group_id';
204 p_sqlstr := p_sqlstr || ' , pil.ler_id';
205 p_sqlstr := p_sqlstr || ' FROM ben_per_in_ler pil, ';
206 p_sqlstr := p_sqlstr || ' ben_ler_f ler ';
207 p_sqlstr := p_sqlstr || ' WHERE pil.ler_id = ler.ler_id ';
208 p_sqlstr := p_sqlstr || ' AND ler.typ_cd = ''SCHEDDO''';
209 p_sqlstr := p_sqlstr || ' AND pil.per_in_ler_stat_cd = ''STRTD''';
210 p_sqlstr := p_sqlstr || ' AND pil.lf_evt_ocrd_dt >= ';
211 p_sqlstr := p_sqlstr || ' TO_DATE('''||TO_CHAR(g_global_start_date,'MM/DD/YYYY') ||''',''MM/DD/YYYY'') ';
212 p_sqlstr := p_sqlstr || ' AND pil.lf_evt_ocrd_dt BETWEEN ler.effective_start_date ';
213 p_sqlstr := p_sqlstr || ' AND ler.effective_end_date ';
214 p_sqlstr := p_sqlstr || ' GROUP BY pil.business_group_id, pil.ler_id ) pil1';
215 p_sqlstr := p_sqlstr || ' WHERE pil.ler_id = pil1.ler_id';
216 p_sqlstr := p_sqlstr || ' AND pil.business_group_id = pil1.business_group_id';
217 p_sqlstr := p_sqlstr || ' AND pil.lf_evt_ocrd_dt = pil1.lf_evt_ocrd_dt ';
218 p_sqlstr := p_sqlstr || ' ORDER BY pil.person_id ';
219
220 OUTPUT(' Collect For Current Open ');
221 --
222 ELSE
223 --
224 OUTPUT(' Collect For All Open');
225 OUTPUT(' Disabling Indexes...');
226 -- Disable Logs and Indexes
227 hri_utl_ddl.log_and_drop_indexes(
228 p_application_short_name => 'HRI',
229 p_table_name => g_eligenrl_evnt_table,
230 p_table_owner => l_schema);
231
232 hri_utl_ddl.log_and_drop_indexes(
233 p_application_short_name => 'HRI',
234 p_table_name => g_elctn_evnt_table,
235 p_table_owner => l_schema);
236 --
237 -- l_strtd_events_only := '';
238 -- Return the sql query to fetch PERSON_ID ranges.
239 -- Select all PERSONs for whom an Open Enrollment is either in Started or Processed State
240 -- Use Profiles to limit entries base on life event occured date.
241 p_sqlstr := ' ';
242 p_sqlstr := p_sqlstr || 'SELECT DISTINCT pil.person_id object_id ';
243 p_sqlstr := p_sqlstr || ' FROM ben_per_in_ler pil, ';
244 p_sqlstr := p_sqlstr || ' ben_ler_f ler ';
245 p_sqlstr := p_sqlstr || ' WHERE pil.ler_id = ler.ler_id ';
246 p_sqlstr := p_sqlstr || ' AND ler.typ_cd = ''SCHEDDO'' ';
247 p_sqlstr := p_sqlstr || ' AND pil.lf_evt_ocrd_dt >= ';
248 p_sqlstr := p_sqlstr || ' TO_DATE('''||TO_CHAR(g_global_start_date,'MM/DD/YYYY') || ''',''MM/DD/YYYY'') ';
249 p_sqlstr := p_sqlstr || ' ORDER BY pil.person_id ';
250 --
251 -- Truncate the table
252 OUTPUT(' Truncating Tables...');
253 --
254 EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || l_schema || '.' || g_eligenrl_evnt_table;
255 EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || l_schema || '.' || g_elctn_evnt_table;
256 --
257 END IF;
258 --
259 --
260 -- End of Full Refresh Section
261 -- -------------------------------------------------------------------------
262 --
263 -- -------------------------------------------------------------------------
264 -- Start of Incremental Refresh Section
265 --
266 ELSE
267 --
268 -- Return the sql query to fetch PERSON_ID ranges
269 --
270 p_sqlstr := ' ';
271 p_sqlstr := p_sqlstr || ' SELECT penq.person_id object_id ';
272 p_sqlstr := p_sqlstr || ' FROM hri_eq_ben_eligenrl_evts penq ';
273 p_sqlstr := p_sqlstr || ' UNION '; -- Removes Duplicates
274 -- 4501649 Election Events to be considered for incremental refresh.
275 p_sqlstr := p_sqlstr || ' SELECT pelq.person_id object_id ';
276 p_sqlstr := p_sqlstr || ' FROM hri_eq_ben_elctn_evts pelq ';
277 p_sqlstr := p_sqlstr || ' ORDER BY 1 ';
278
279 --
280 -- End of Incremental Refresh Section
281 -- -------------------------------------------------------------------------
282 --
283 END IF;
284 END IF;
285 dbg('Leaving ' || l_procedure);
286 EXCEPTION
287 WHEN OTHERS THEN
288 dbg('Error ' || l_procedure);
289 OUTPUT('SQLERRM '|| SQLERRM);
290 RAISE;
291 END pre_process;
292 --
293 -- ----------------------------------------------------------------------------
294 -- COLLECT_ELIGIBLE_EVT (INCREMENTAL REFRESH)
295 -- This procedure includes the logic for collecting Only Eligibility
296 -- during Incremental Refresh.
297 -- ----------------------------------------------------------------------------
298 --
299 PROCEDURE collect_eligible_evt (p_pil_rec G_PIL_REC_TYPE) IS
300
301 dml_errors EXCEPTION;
302 PRAGMA exception_init(dml_errors, -24381);
303
304 l_procedure VARCHAR2(100) := g_package || 'collect_eligible_evt';
305
306 BEGIN
307 -- dbg('Entering ' || l_procedure);
308
309 INSERT INTO HRI_MB_BEN_ELIGENRL_EVNT_CT
310 (change_date
311 ,effective_start_date
312 ,effective_end_date
313 ,compobj_sk_pk
314 ,asnd_lf_evt_dt
315 ,person_id
316 ,per_in_ler_id
317 ,enrt_perd_id
318 ,prtt_enrt_rslt_id
319 ,elig_ind
320 ,enrt_ind
321 ,not_enrt_ind
322 ,dflt_ind
323 ,waive_expl_ind
324 ,waive_dflt_ind)
325 (SELECT ee.change_date change_date,
326 ee.change_date effective_start_date,
327 NVL(LEAD(ee.change_date - 1)
328 OVER (PARTITION BY compobj_sk_pk
329 ORDER BY compobj_sk_pk, ee.change_date), hr_api.g_eot) effective_end_date,
330 ee.compobj_sk_pk,
331 p_pil_rec.lf_evt_ocrd_dt asnd_lf_evt_dt ,
332 p_pil_rec.person_id person_id,
333 p_pil_rec.per_in_ler_id per_in_ler_id,
334 ee.enrt_perd_id,
335 ee.prtt_enrt_rslt_id,
336 ee.elig_ind,
337 ee.enrt_ind,
338 ee.not_enrt_ind,
339 ee.dflt_ind,
340 ee.waive_expl_ind,
341 ee.waive_dflt_ind
342 FROM ( -- Retuns all Electable Choices if Enrollments DOES NOT start on the same day.
343 -- First Part of UNION brings all PLIPs and OIPL IS NULL
344 SELECT pel.enrt_perd_strt_dt change_date,
345 copd.compobj_sk_pk compobj_sk_pk,
346 pel.enrt_perd_id,
347 epe.prtt_enrt_rslt_id prtt_enrt_rslt_id,
348 1 elig_ind,
349 (CASE WHEN (epe.crntly_enrd_flag = 'Y')
350 THEN 1
351 ELSE 0 END ) enrt_ind,
352 (CASE WHEN (epe.crntly_enrd_flag = 'Y')
353 THEN 0
354 ELSE 1 END ) not_enrt_ind,
355 -- DFLT_IND -> If Currently Enrolled and Default Comp Object
356 (CASE WHEN (pel.elcns_made_dt IS NULL
357 AND pel.dflt_asnd_dt IS NOT NULL
358 AND epe.crntly_enrd_flag = 'Y'
359 AND epe.dflt_flag = 'Y')
360 THEN 1
361 ELSE 0 END) dflt_ind,
362 -- WAIVE_EXPL_IND -> If Currently Enrolled and Waive Opt/Pln and Not Default Comp.Object
363 (CASE WHEN (pel.elcns_made_dt IS NOT NULL
364 AND epe.crntly_enrd_flag = 'Y'
365 AND pln.invk_dcln_prtn_pl_flag = 'Y')
366 THEN 1
367 ELSE 0 END) waive_expl_ind,
368 -- WAIVE_DFLT_IND -> If Currently Enrolled and Waive Opt/Pln and Default Comp.Object
369 (CASE WHEN (pel.elcns_made_dt IS NULL
370 AND pel.dflt_asnd_dt IS NOT NULL
371 AND epe.dflt_flag = 'Y'
372 AND pln.invk_dcln_prtn_pl_flag = 'Y')
373 THEN 1
374 ELSE 0 END) waive_dflt_ind
375 FROM ben_elig_per_elctbl_chc epe,
376 ben_pil_elctbl_chc_popl pel,
377 hri_cs_compobj_ct copd,
378 ben_pl_f pln
379 WHERE epe.pil_elctbl_chc_popl_id = pel.pil_elctbl_chc_popl_id
380 AND pel.per_in_ler_id = p_pil_rec.per_in_ler_id
381 AND epe.elctbl_flag = 'Y'
382 AND epe.elig_flag = 'Y'
383 AND copd.oipl_id = -1
384 AND epe.oipl_id IS NULL
385 AND copd.plip_id = epe.plip_id
386 AND copd.pgm_id = epe.pgm_id -- As required for Perf.
387 AND copd.pl_id = epe.pl_id -- As required for Perf.
388 AND pln.pl_id = copd.pl_id
389 AND p_pil_rec.lf_evt_ocrd_dt BETWEEN pln.effective_start_date
390 AND pln.effective_end_date
391 AND (epe.prtt_enrt_rslt_id IS NULL
392 OR NOT EXISTS (
393 SELECT null
394 FROM ben_prtt_enrt_rslt_f pen
395 WHERE pen.prtt_enrt_rslt_id = epe.prtt_enrt_rslt_id
396 AND pen.per_in_ler_id = p_pil_rec.per_in_ler_id
397 AND pen.prtt_enrt_rslt_stat_cd IS NULL
398 AND pen.enrt_cvg_thru_dt = hr_api.g_eot
399 AND pen.effective_end_date = hr_api.g_eot
400 AND pen.effective_start_date = pel.enrt_perd_strt_dt))
401 UNION ALL
402 -- Second Part of UNION brings all OIPLs
403 SELECT pel.enrt_perd_strt_dt change_date,
404 copd.compobj_sk_pk compobj_sk_pk,
405 pel.enrt_perd_id,
406 epe.prtt_enrt_rslt_id prtt_enrt_rslt_id,
407 1 elig_ind,
408 (CASE WHEN (epe.crntly_enrd_flag = 'Y')
409 THEN 1
410 ELSE 0 END ) enrt_ind,
411 (CASE WHEN (epe.crntly_enrd_flag = 'Y')
412 THEN 0
413 ELSE 1 END ) not_enrt_ind,
414 -- DFLT_IND -> If Currently Enrolled and Default Comp Object
415 (CASE WHEN (pel.elcns_made_dt IS NULL
416 AND pel.dflt_asnd_dt IS NOT NULL
417 AND epe.crntly_enrd_flag = 'Y'
418 AND epe.dflt_flag = 'Y')
419 THEN 1
420 ELSE 0 END) dflt_ind,
421 -- WAIVE_EXPL_IND -> If Currently Enrolled and Waive Opt/Pln and Not Default Comp.Object
422 (CASE WHEN (pel.elcns_made_dt IS NOT NULL
423 AND epe.crntly_enrd_flag = 'Y'
424 AND opt.invk_wv_opt_flag = 'Y')
425 THEN 1
426 ELSE 0 END) waive_expl_ind,
427 -- WAIVE_DFLT_IND -> If Currently Enrolled and Waive Opt/Pln and Default Comp.Object
428 (CASE WHEN (pel.elcns_made_dt IS NULL
429 AND pel.dflt_asnd_dt IS NOT NULL
430 AND epe.dflt_flag = 'Y'
431 AND opt.invk_wv_opt_flag = 'Y')
432 THEN 1
433 ELSE 0 END) waive_dflt_ind
434 FROM ben_elig_per_elctbl_chc epe,
435 ben_pil_elctbl_chc_popl pel,
436 hri_cs_compobj_ct copd,
437 ben_opt_f opt
438 WHERE epe.pil_elctbl_chc_popl_id = pel.pil_elctbl_chc_popl_id
439 AND pel.per_in_ler_id = p_pil_rec.per_in_ler_id
440 AND epe.elctbl_flag = 'Y'
441 AND epe.elig_flag = 'Y'
442 AND copd.oipl_id = epe.oipl_id
443 AND copd.plip_id = epe.plip_id
444 AND copd.pgm_id = epe.pgm_id -- As required for perf.
445 AND copd.pl_id = epe.pl_id -- As required for perf.
446 AND opt.opt_id = copd.opt_id
447 AND p_pil_rec.lf_evt_ocrd_dt BETWEEN opt.effective_start_date
448 AND opt.effective_end_date
449 AND (epe.prtt_enrt_rslt_id IS NULL
450 OR NOT EXISTS (
451 SELECT null
452 FROM ben_prtt_enrt_rslt_f pen
453 WHERE pen.prtt_enrt_rslt_id = epe.prtt_enrt_rslt_id
454 AND pen.per_in_ler_id = p_pil_rec.per_in_ler_id
455 AND pen.prtt_enrt_rslt_stat_cd IS NULL
456 AND pen.enrt_cvg_thru_dt = hr_api.g_eot
457 AND pen.effective_end_date = hr_api.g_eot
458 AND pen.effective_start_date = pel.enrt_perd_strt_dt))
459 ) ee
460 );
461
462 -- dbg('Leaving ' || l_procedure);
463 EXCEPTION
464 WHEN OTHERS THEN
465 dbg('Error ' || l_procedure);
466 OUTPUT ('ERROR '||SQLERRM);
467 RAISE;
468 END collect_eligible_evt;
469
470 --
471 -- ----------------------------------------------------------------------------
472 -- COLLECT_ELCN_EVT (FULL REFRESH Overloaded)
473 -- This procedure includes the logic for collection Election Events Fact.
474 -- ----------------------------------------------------------------------------
475 --
476 PROCEDURE collect_elcn_evt (p_pil_rec G_PIL_REC_TYPE) IS
477 dml_errors EXCEPTION;
478 PRAGMA exception_init(dml_errors, -24381);
479
480 l_procedure VARCHAR2(100) := g_package || 'collect_elcn_evt';
481
482 BEGIN
483 -- dbg('Entering ' || l_procedure);
484 --
485 IF (g_collect_oe_only = 'Y' AND g_full_refresh = 'Y') THEN
486 -- If Only Current Open Enrollment is being Collected,
487 -- Delete any stray records, if present.
488 DELETE from hri_mb_ben_elctn_evnt_ct
489 WHERE person_id = p_pil_rec.person_id
490 AND asnd_lf_evt_dt = p_pil_rec.lf_evt_ocrd_dt;
491 --
492 END IF;
493 -- Populate Benefit Election Event Fact
494 INSERT INTO hri_mb_ben_elctn_evnt_ct
495 ( elig_ind
496 ,enrt_ind
497 ,not_enrt_ind
498 ,dflt_ind
499 ,ler_status_cd
500 ,voidd_ind
501 ,bckdt_ind
502 ,procd_ind
503 ,strtd_ind
504 ,change_date
505 ,effective_start_date
506 ,effective_end_date
507 ,person_id
508 ,asnd_lf_evt_dt
509 ,enrt_perd_id
510 ,per_in_ler_id
511 ,pgm_id
512 ,pil_elctbl_chc_popl_id )
513 (SELECT DECODE (pel.pil_elctbl_popl_stat_cd,'BCKDT',0,'VOIDD',0,1) elig_ind
514 ,(CASE WHEN (pel.pil_elctbl_popl_stat_cd NOT IN ('BCKDT','VOIDD')
515 AND (pel.elcns_made_dt IS NOT NULL)) -- 4721802: Not counting Automatics
516 THEN 1
517 ELSE 0 END) enrt_ind
518 ,(CASE WHEN (pel.pil_elctbl_popl_stat_cd NOT IN ('BCKDT','VOIDD')
519 AND pel.elcns_made_dt IS NULL
520 -- AND pel.auto_asnd_dt IS NULL -- 4568414
521 AND pel.dflt_asnd_dt IS NULL)
522 THEN 1
523 ELSE 0 END) not_enrt_ind
524 ,(CASE WHEN (pel.pil_elctbl_popl_stat_cd NOT IN ('BCKDT','VOIDD')
525 AND pel.elcns_made_dt IS NULL
526 AND pel.auto_asnd_dt IS NULL -- 4568414
527 AND pel.dflt_asnd_dt IS NOT NULL)
528 THEN 1
529 ELSE 0 END) dflt_ind
530 ,(CASE WHEN (pil.per_in_ler_stat_cd = 'BCKDT'
531 AND ppl.ptnl_ler_for_per_stat_cd = 'MNL') -- 4514159
532 THEN 'MNL'
533 WHEN (pil.per_in_ler_stat_cd = 'BCKDT'
534 AND ppl.ptnl_ler_for_per_stat_cd <> 'MNL') -- 4514159
535 THEN 'BCKDT'
536 ELSE pil.per_in_ler_stat_cd END ) per_in_ler_stat_cd
537 ,DECODE(pil.per_in_ler_stat_cd,'VOIDD',1,0) voidd_ind
538 ,DECODE(pil.per_in_ler_stat_cd,'BCKDT',1,0) bckdt_ind
539 ,DECODE(pil.per_in_ler_stat_cd,'PROCD',1,0) procd_ind
540 ,DECODE(pil.per_in_ler_stat_cd,'STRTD',1,0) strtd_ind
541 ,pil.lf_evt_ocrd_dt change_date
542 ,pil.lf_evt_ocrd_dt effective_start_date
543 ,hr_api.g_eot effective_end_date
544 ,pil.person_id person_id
545 ,pil.lf_evt_ocrd_dt asnd_lf_evt_dt
546 ,pel.enrt_perd_id enrt_perd_id
547 ,pil.per_in_ler_id per_in_ler_id
548 ,pel.pgm_id pgm_id
549 ,pel.pil_elctbl_chc_popl_id pil_elctbl_chc_popl_id
550 FROM ben_pil_elctbl_chc_popl pel,
551 ben_per_in_ler pil,
552 ben_ptnl_ler_for_per ppl
553 WHERE pel.per_in_ler_id = pil.per_in_ler_id
554 AND ppl.ptnl_ler_for_per_id = pil.ptnl_ler_for_per_id
555 AND pil.per_in_ler_id = p_pil_rec.per_in_ler_id
556 );
557 -- dbg('Leaving ' || l_procedure);
558 EXCEPTION
559 WHEN OTHERS THEN
560 dbg('Error ' || l_procedure);
561 OUTPUT ('ERROR '||SQLERRM);
562 RAISE;
563 END collect_elcn_evt;
564
565 --
566 -- ----------------------------------------------------------------------------
567 -- COLLECT_ELCN_EVT (INCREMENTAL REFRESH Overloaded)
568 -- This procedure includes the logic for collection Election Events Fact.
569 -- ----------------------------------------------------------------------------
570 --
571 PROCEDURE collect_elcn_evt (p_person_id NUMBER
572 ,p_per_in_ler_id NUMBER) IS
573 dml_errors EXCEPTION;
574 PRAGMA exception_init(dml_errors, -24381);
575
576 l_procedure VARCHAR2(100) := g_package || 'collect_elcn_evt';
577
578 CURSOR c_pil IS
579 SELECT pil.per_in_ler_id
580 ,pil.person_id
581 ,pil.lf_evt_ocrd_dt
582 ,(CASE WHEN (pil.per_in_ler_stat_cd = 'BCKDT'
583 AND ppl.ptnl_ler_for_per_stat_cd = 'MNL') -- 4514159
584 THEN 'MNL'
585 WHEN (pil.per_in_ler_stat_cd = 'BCKDT'
586 AND ppl.ptnl_ler_for_per_stat_cd <> 'MNL') -- 4514159
587 THEN 'BCKDT'
588 ELSE pil.per_in_ler_stat_cd END ) per_in_ler_stat_cd
589 ,pil.business_group_id
590 FROM ben_per_in_ler pil,
591 ben_ptnl_ler_for_per ppl
592 WHERE pil.per_in_ler_id = p_per_in_ler_id
593 AND ppl.ptnl_ler_for_per_id = pil.ptnl_ler_for_per_id;
594
595 l_pil_rec G_PIL_REC_TYPE;
596
597 CURSOR c_bckdt_void_pil IS
598 SELECT per_in_ler_id,
599 pil.per_in_ler_stat_cd
600 FROM ben_per_in_ler pil,
601 ben_ler_f ler
602 WHERE pil.lf_evt_ocrd_dt = l_pil_rec.lf_evt_ocrd_dt
603 AND pil.ler_id = ler.ler_id
604 AND ler.typ_cd = 'SCHEDDO'
605 AND pil.person_id = p_person_id
606 AND pil.per_in_ler_stat_cd IN ('BCKDT','VOIDD')
607 AND pil.lf_evt_ocrd_dt BETWEEN ler.effective_start_date
608 AND ler.effective_end_date;
609
610 CURSOR c_elcn_evt IS
611 SELECT null
612 FROM HRI_EQ_BEN_ELCTN_EVTS pelq
613 WHERE pelq.person_id = p_person_id
614 AND pelq.lf_evt_ocrd_dt = l_pil_rec.lf_evt_ocrd_dt
615 AND pelq.per_in_ler_id = p_per_in_ler_id
616 AND event_cd = 'INSERT';
617
618 CURSOR c_upd_elcn IS
619 SELECT 1 elig_ind
620 ,(CASE WHEN (pelq.elcns_made_dt IS NOT NULL )
621 --OR pelq.auto_asnd_dt IS NOT NULL) -- 4721802: Not Counting Automatics
622 THEN 1
623 ELSE 0 END) enrt_ind
624 ,(CASE WHEN (pelq.elcns_made_dt IS NULL
625 AND pelq.dflt_asnd_dt IS NULL) -- 4721802
626 --AND pelq.auto_asnd_dt IS NULL)
627 THEN 1
628 ELSE 0 END) not_enrt_ind
629 ,(CASE WHEN (pelq.elcns_made_dt IS NULL
630 AND pelq.auto_asnd_dt IS NULL
631 AND pelq.dflt_asnd_dt IS NOT NULL)
632 THEN 1
633 ELSE 0 END) dflt_ind
634 ,NVL(pelq.pil_elctbl_popl_stat_cd,'STRTD') pil_elctbl_popl_stat_cd
635 ,DECODE(pelq.pil_elctbl_popl_stat_cd,'VOIDD',1,0) voidd_ind
636 ,DECODE(pelq.pil_elctbl_popl_stat_cd,'BCKDT',1,0) bckdt_ind
637 ,DECODE(pelq.pil_elctbl_popl_stat_cd,'PROCD',1,0) procd_ind
638 ,DECODE(pelq.pil_elctbl_popl_stat_cd,'STRTD',1,NULL,1,0) strtd_ind
639 ,pelq.lf_evt_ocrd_dt change_date
640 ,pelq.lf_evt_ocrd_dt effective_start_date
641 ,hr_api.g_eot effective_end_date
642 ,pelq.person_id person_id
643 ,pelq.lf_evt_ocrd_dt asnd_lf_evt_dt
644 ,enpd.enrt_perd_id enrt_perd_id
645 ,pelq.per_in_ler_id per_in_ler_id
646 ,pelq.pgm_id pgm_id
647 ,pelq.pil_elctbl_chc_popl_id pil_elctbl_chc_popl_id
648 FROM hri_eq_ben_elctn_evts pelq,
649 hri_cs_time_benrl_prd_ct enpd
650 WHERE pelq.pil_elctbl_popl_stat_cd IN ('STRTD','PROCD')
651 AND pelq.event_cd = 'UPDATE'
652 AND pelq.per_in_ler_id = p_per_in_ler_id
653 AND pelq.person_id = p_person_id
654 AND pelq.pgm_id = enpd.pgm_id
655 AND enpd.asnd_lf_evt_dt = pelq.lf_evt_ocrd_dt
656 AND pelq.last_update_date =
657 (SELECT MAX(pelq1.last_update_date)
658 FROM hri_eq_ben_elctn_evts pelq1
659 WHERE pelq1.per_in_ler_id = p_per_in_ler_id
660 AND pelq1.person_id = p_person_id
661 AND pelq1.pgm_id = pelq.pgm_id
662 AND pelq1.lf_evt_ocrd_dt = pelq.lf_evt_ocrd_dt
663 AND pelq1.event_cd = 'UPDATE'
664 AND pelq1.pil_elctbl_popl_stat_cd IN ('STRTD','PROCD')
665 );
666 --
667 TYPE g_pel_tab_type IS TABLE OF c_upd_elcn%ROWTYPE
668 INDEX BY BINARY_INTEGER;
669 --
670 l_elcn_evt_tbl g_pel_tab_type;
671 --
672 l_dummy VARCHAR2(2);
673 --
674 BEGIN
675 -- dbg('Entering ' || l_procedure);
676 --
677 OPEN c_pil;
678 FETCH c_pil INTO l_pil_rec;
679 CLOSE c_pil;
680
681 IF (l_pil_rec.per_in_ler_stat_cd IN ('BCKDT','VOIDD','MNL')) THEN
682 -- For Backed/Voided Elections ...
683
684 -- 1. Delete Eligibility/Enrollment events ...
685 DELETE FROM hri_mb_ben_eligenrl_evnt_ct penc
686 WHERE penc.person_id = p_person_id
687 AND penc.asnd_lf_evt_dt = l_pil_rec.lf_evt_ocrd_dt
688 AND penc.per_in_ler_id = p_per_in_ler_id;
689
690 -- 2. Update Election Events to Reset Flags.
691 UPDATE hri_mb_ben_elctn_evnt_ct pelc
692 SET pelc.elig_ind = 0
693 ,pelc.enrt_ind = 0
694 ,pelc.not_enrt_ind = 0
695 ,pelc.dflt_ind = 0
696 ,pelc.ler_status_cd = l_pil_rec.per_in_ler_stat_cd
697 ,pelc.voidd_ind = 0
698 ,pelc.bckdt_ind = 0
699 ,pelc.procd_ind = 0
700 ,pelc.strtd_ind = 0
701 ,pelc.effective_start_date = l_pil_rec.lf_evt_ocrd_dt
702 ,pelc.effective_end_date = hr_api.g_eot
703 ,pelc.pil_elctbl_chc_popl_id = NULL
704 WHERE pelc.person_id = p_person_id
705 AND pelc.asnd_lf_evt_dt = l_pil_rec.lf_evt_ocrd_dt
706 AND pelc.per_in_ler_id = l_pil_rec.per_in_ler_id;
707 --
708 ELSIF (l_pil_rec.per_in_ler_stat_cd IN ('STRTD','PROCD')) THEN
709 -- For Started/Process Elections ...
710
711 FOR i IN c_bckdt_void_pil LOOP
712 -- 1. Delete Any Back-out / Voided Enrollment Events
713 DELETE FROM hri_mb_ben_eligenrl_evnt_ct penc
714 WHERE penc.person_id = p_person_id
715 AND penc.asnd_lf_evt_dt = l_pil_rec.lf_evt_ocrd_dt
716 AND penc.per_in_ler_id = i.per_in_ler_id;
717
718 -- 2. Delete Any Back-out / Voided Eligibility Events
719 DELETE FROM hri_mb_ben_elctn_evnt_ct pelc
720 WHERE pelc.person_id = p_person_id
721 AND pelc.asnd_lf_evt_dt = l_pil_rec.lf_evt_ocrd_dt
722 AND pelc.per_in_ler_id = i.per_in_ler_id;
723 --
724 END LOOP;
725 -- Check if the Election event is 'INSERT'
726 OPEN c_elcn_evt;
727 FETCH c_elcn_evt into l_dummy;
728 IF c_elcn_evt%FOUND THEN
729 -- Check if the Election event is 'INSERT'
730 -- 3. Collect Election Event, using Full Refresh Mode.
731 collect_elcn_evt (p_pil_rec => l_pil_rec);
732 --
733 -- 4. Collect Eligibility for the Event..
734 collect_eligible_evt(p_pil_rec => l_pil_rec);
735
736 ELSE
737 -- If the Election event is 'UPDATE'
738 -- Update Flags for Election Events..
739 OPEN c_upd_elcn;
740 FETCH c_upd_elcn BULK COLLECT INTO l_elcn_evt_tbl;
741 CLOSE c_upd_elcn;
742 --
743 IF (l_elcn_evt_tbl.COUNT > 0) THEN
744 FOR i IN l_elcn_evt_tbl.FIRST..l_elcn_evt_tbl.LAST LOOP
745 --
746 UPDATE HRI_MB_BEN_ELCTN_EVNT_CT pelc
747 SET pelc.elig_ind = l_elcn_evt_tbl(i).elig_ind
748 ,pelc.enrt_ind = l_elcn_evt_tbl(i).enrt_ind
749 ,pelc.not_enrt_ind = l_elcn_evt_tbl(i).not_enrt_ind
750 ,pelc.dflt_ind = l_elcn_evt_tbl(i).dflt_ind
751 ,pelc.ler_status_cd = l_elcn_evt_tbl(i).pil_elctbl_popl_stat_cd
752 ,pelc.voidd_ind = l_elcn_evt_tbl(i).voidd_ind
753 ,pelc.bckdt_ind = l_elcn_evt_tbl(i).bckdt_ind
754 ,pelc.procd_ind = l_elcn_evt_tbl(i).procd_ind
755 ,pelc.strtd_ind = l_elcn_evt_tbl(i).strtd_ind
756 ,pelc.effective_start_date = l_elcn_evt_tbl(i).effective_start_date
757 ,pelc.effective_end_date = l_elcn_evt_tbl(i).effective_end_date
758 ,pelc.enrt_perd_id = l_elcn_evt_tbl(i).enrt_perd_id
759 ,pelc.pil_elctbl_chc_popl_id = l_elcn_evt_tbl(i).pil_elctbl_chc_popl_id
760 WHERE pelc.person_id = l_elcn_evt_tbl(i).person_id
761 AND pelc.asnd_lf_evt_dt = l_elcn_evt_tbl(i).asnd_lf_evt_dt
762 AND pelc.pgm_id = l_elcn_evt_tbl(i).pgm_id;
763 --
764 END LOOP;
765 --
766 END IF;
767 END IF;
768 CLOSE c_elcn_evt;
769
770 -- 5. If Elections Made Date is populated, set the Default Flag in Eligible Events to 0.
771 UPDATE hri_mb_ben_eligenrl_evnt_ct penc
772 SET penc.dflt_ind = 0
773 ,waive_dflt_ind = 0
774 ,waive_expl_ind = DECODE(penc.waive_dflt_ind,1,1,0)
775 WHERE penc.enrt_ind = 1
776 AND penc.person_id = p_person_id
777 AND penc.asnd_lf_evt_dt = l_pil_rec.lf_evt_ocrd_dt
778 AND penc.per_in_ler_id = l_pil_rec.per_in_ler_id
779 AND EXISTS
780 (SELECT NULL
781 FROM HRI_EQ_BEN_ELCTN_EVTS pelq
782 WHERE pelq.event_cd = 'UPDATE'
783 AND pelq.pil_elctbl_popl_stat_cd IN ('STRTD','PROCD')
784 AND pelq.per_in_ler_id = p_per_in_ler_id
785 AND pelq.person_id = p_person_id
786 AND pelq.lf_evt_ocrd_dt = l_pil_rec.lf_evt_ocrd_dt
787 AND pelq.elcns_made_dt IS NOT NULL);
788
789 END IF;
790 -- dbg('Leaving ' || l_procedure);
791 EXCEPTION
792 WHEN OTHERS THEN
793 dbg('Error ' || l_procedure);
794 OUTPUT ('ERROR '||SQLERRM);
795 RAISE;
796 END collect_elcn_evt;
797
798 --
799 -- ----------------------------------------------------------------------------
800 -- COLLECT_ELIGENRL_EVT (INCREMENTAL REFRESH Overloaded)
801 -- This procedure includes the logic for collecting Eligibility
802 -- and Enrollment Events Fact.
803 -- ----------------------------------------------------------------------------
804 --
805 PROCEDURE collect_eligenrl_evt (p_person_id NUMBER
806 ,p_per_in_ler_id NUMBER
807 ,p_lf_evt_ocrd_dt DATE) IS
808
809 CURSOR c_eee_end_date IS
810 SELECT PRTTQ.eee_end_dt, penc.rowid row_id
811 FROM (SELECT NVL(MIN(penq.event_date-1),hr_api.g_eot) eee_end_dt,
812 copd.compobj_sk_pk,
813 penq.lf_evt_ocrd_dt
814 FROM hri_eq_ben_eligenrl_evts penq,
815 hri_cs_compobj_ct copd
816 WHERE penq.per_in_ler_id = p_per_in_ler_id
817 AND penq.pgm_id = copd.pgm_id
818 AND copd.oipl_id = NVL(penq.oipl_id, -1)
819 AND copd.pl_id = penq.pl_id
820 GROUP BY penq.lf_evt_ocrd_dt, copd.compobj_sk_pk ) PRTTQ,
821 hri_mb_ben_eligenrl_evnt_ct penc
822 WHERE penc.asnd_lf_evt_dt = PRTTQ.lf_evt_ocrd_dt
823 AND penc.person_id = p_person_id
824 AND penc.per_in_ler_id = p_per_in_ler_id
825 AND (PRTTQ.eee_end_dt + 1) > penc.effective_start_date
826 AND penc.effective_end_date = hr_api.g_eot
827 AND PRTTQ.compobj_sk_pk = penc.compobj_sk_pk;
828
829 l_eee_end_dt_tbl g_date_tab_type;
830 l_row_id_tbl g_rowid_tab_type;
831
832 dml_errors EXCEPTION;
833 PRAGMA exception_init(dml_errors, -24381);
834
835 l_procedure VARCHAR2(100) := g_package || 'collect_eligenrl_evt';
836
837 BEGIN
838 -- dbg('Entering ' || l_procedure);
839 --1. Update Records for which Results were ZAPPED / DE-ENROLLED
840 -- 4579556 - Changed this from Delete to Update.
841 UPDATE hri_mb_ben_eligenrl_evnt_ct penc
842 SET enrt_ind = 0
843 ,not_enrt_ind = 1
844 ,dflt_ind = 0
845 ,waive_expl_ind = 0
846 ,waive_dflt_ind = 0
847 WHERE (penc.compobj_sk_pk, penc.asnd_lf_evt_dt, penc.person_id)
848 IN (SELECT copd.compobj_sk_pk
849 ,penq.lf_evt_ocrd_dt
850 ,penq.person_id
851 FROM hri_eq_ben_eligenrl_evts penq,
852 hri_cs_compobj_ct copd
853 WHERE penq.per_in_ler_id = p_per_in_ler_id
854 AND penq.event_cd IN ('ZAP','DE-ENRD')
855 AND penq.pgm_id = copd.pgm_id
856 AND copd.oipl_id = NVL(penq.oipl_id, -1)
857 AND copd.pl_id = penq.pl_id);
858
859 --2. End date exising records to one day prior.
860 l_eee_end_dt_tbl.delete;
861 l_row_id_tbl.delete;
862
863 OPEN c_eee_end_date;
864 FETCH c_eee_end_date BULK COLLECT INTO l_eee_end_dt_tbl, l_row_id_tbl;
865 CLOSE c_eee_end_date;
866 --
867 IF (l_eee_end_dt_tbl.COUNT > 0) THEN
868 FORALL i IN l_eee_end_dt_tbl.FIRST..l_eee_end_dt_tbl.LAST SAVE EXCEPTIONS
869 UPDATE hri_mb_ben_eligenrl_evnt_ct penc
870 SET penc.effective_end_date = l_eee_end_dt_tbl(i)
871 WHERE ROWID = l_row_id_tbl(i);
872 END IF;
873 --
874 --3. Merge remaining events.
875 MERGE INTO HRI_MB_BEN_ELIGENRL_EVNT_CT penc
876 USING (
877 SELECT copd.compobj_sk_pk
878 ,enpd.enrt_perd_id
879 ,penq.lf_evt_ocrd_dt
880 ,penq.event_date
881 ,penq.event_date effective_start_date
882 ,NVL(LAG(penq.event_date-1)
883 OVER (PARTITION BY penq.lf_evt_ocrd_dt, copd.compobj_sk_pk
884 ORDER BY penq.lf_evt_ocrd_dt, copd.compobj_sk_pk, penq.event_date, penq.creation_date)
885 , hr_api.g_eot) effective_end_date
886 ,penq.person_id
887 ,penq.prtt_enrt_rslt_id
888 ,penq.per_in_ler_id
889 ,1 elig_ind
890 ,penq.enrt_ind
891 ,(CASE WHEN (penq.event_cd IN ('DE-ENRD','ZAP')) -- Only 'ENRD' events come up.. so this may not be necessary.
892 THEN 1
893 ELSE 0 END ) not_enrt_ind
894 ,penq.dflt_ind
895 ,(CASE WHEN (penq.dflt_ind = 0
896 AND penq.enrt_ind = 1
897 AND (opt.invk_wv_opt_flag = 'Y'
898 OR (opt.opt_id IS NULL AND pln.invk_dcln_prtn_pl_flag = 'Y')) )
899 THEN 1
900 ELSE 0 END ) waive_expl_ind
901 ,(CASE WHEN (penq.dflt_ind = 1
902 AND penq.enrt_ind = 1
903 AND (opt.invk_wv_opt_flag = 'Y'
904 OR (opt.opt_id IS NULL AND pln.invk_dcln_prtn_pl_flag = 'Y')) )
905 THEN 1
906 ELSE 0 END ) waive_dflt_ind
907 FROM HRI_EQ_BEN_ELIGENRL_EVTS penq,
908 hri_cs_time_benrl_prd_ct enpd,
909 hri_cs_compobj_ct copd,
910 ben_opt_f opt,
911 ben_pl_f pln
912 WHERE penq.per_in_ler_id = p_per_in_ler_id
913 AND penq.event_cd IN ('ENRD') --,'DE-ENRD')
914 AND opt.opt_id(+) = NVL(copd.opt_id,-1)
915 AND pln.pl_id = copd.pl_id
916 AND penq.pgm_id = copd.pgm_id
917 AND enpd.pgm_id = penq.pgm_id
918 AND p_lf_evt_ocrd_dt between opt.effective_start_date(+) AND opt.effective_end_date(+)
919 AND p_lf_evt_ocrd_dt between pln.effective_start_date AND pln.effective_end_date
920 AND enpd.asnd_lf_evt_dt = penq.lf_evt_ocrd_dt
921 AND copd.oipl_id = NVL(penq.oipl_id, -1)
922 AND copd.pl_id = penq.pl_id
923 AND NOT EXISTS
924 (SELECT null -- Picks up only the latest Event from queue.
925 FROM HRI_EQ_BEN_ELIGENRL_EVTS penq1
926 WHERE penq1.per_in_ler_id = p_per_in_ler_id
927 AND NVL(penq1.oipl_id,-1) = NVL(penq.oipl_id,-1)
928 AND penq1.pl_id = penq.pl_id
929 AND penq1.pgm_id = penq.pgm_id
930 AND NVL(penq1.last_update_date,TRUNC(SYSDATE)) > NVL(penq.last_update_date,TRUNC(SYSDATE))
931 )
932 ) PRTTQ
933 ON (PRTTQ.compobj_sk_pk = penc.compobj_sk_pk
934 AND PRTTQ.lf_evt_ocrd_dt = penc.asnd_lf_evt_dt
935 AND PRTTQ.event_date = penc.change_date
936 AND PRTTQ.person_id = penc.person_id)
937 WHEN MATCHED THEN
938 UPDATE SET penc.elig_ind = PRTTQ.elig_ind,
939 penc.enrt_ind = PRTTQ.enrt_ind,
940 penc.dflt_ind = PRTTQ.dflt_ind,
941 penc.not_enrt_ind = PRTTQ.not_enrt_ind,
942 penc.waive_expl_ind = PRTTQ.waive_expl_ind,
943 penc.waive_dflt_ind = PRTTQ.waive_dflt_ind
944 WHEN NOT MATCHED THEN
945 INSERT (compobj_sk_pk
946 ,enrt_perd_id
947 ,asnd_lf_evt_dt
948 ,change_date
949 ,effective_start_date
950 ,effective_end_date
951 ,person_id
952 ,prtt_enrt_rslt_id
953 ,per_in_ler_id
954 ,elig_ind
955 ,enrt_ind
956 ,not_enrt_ind
957 ,dflt_ind
958 ,waive_expl_ind
959 ,waive_dflt_ind)
960 VALUES (PRTTQ.compobj_sk_pk
961 ,PRTTQ.enrt_perd_id
962 ,PRTTQ.lf_evt_ocrd_dt
963 ,PRTTQ.event_date
964 ,PRTTQ.effective_start_date
965 ,PRTTQ.effective_end_date
966 ,PRTTQ.person_id
967 ,PRTTQ.prtt_enrt_rslt_id
968 ,PRTTQ.per_in_ler_id
969 ,PRTTQ.elig_ind
970 ,PRTTQ.enrt_ind
971 ,PRTTQ.not_enrt_ind
972 ,PRTTQ.dflt_ind
973 ,PRTTQ.waive_expl_ind
974 ,PRTTQ.waive_dflt_ind);
975
976 -- 4. Delete Events which are VOIDED.
977 DELETE FROM HRI_MB_BEN_ELIGENRL_EVNT_CT penc
978 WHERE penc.person_id = p_person_id
979 AND penc.per_in_ler_id = p_per_in_ler_id
980 AND penc.effective_start_date > penc.effective_end_date;
981 --NULL;
982 -- dbg('Leaving ' || l_procedure);
983 EXCEPTION
984 WHEN OTHERS THEN
985 dbg('Error ' || l_procedure);
986 OUTPUT('SQLERRM '|| SQLERRM);
987 RAISE;
988 END collect_eligenrl_evt;
989 --
990 -- ----------------------------------------------------------------------------
991 -- COLLECT_ELIGENRL_EVT (FULL REFRESH Overloaded)
992 -- This procedure includes the logic for collecting Eligibility
993 -- and Enrollment Events Fact.
994 -- ----------------------------------------------------------------------------
995 --
996 PROCEDURE collect_eligenrl_evt (p_pil_rec G_PIL_REC_TYPE) IS
997
998 dml_errors EXCEPTION;
999 PRAGMA exception_init(dml_errors, -24381);
1000
1001 l_procedure VARCHAR2(100) := g_package || 'collect_eligenrl_evt';
1002
1003 BEGIN
1004 -- dbg('Entering ' || l_procedure);
1005 --
1006 IF (g_collect_oe_only = 'Y') THEN
1007 -- If Only Current Open Enrollment is being Collected,
1008 -- Delete any stray records, if present.
1009 DELETE from hri_mb_ben_eligenrl_evnt_ct
1010 WHERE person_id = p_pil_rec.person_id
1011 AND asnd_lf_evt_dt = p_pil_rec.lf_evt_ocrd_dt
1012 AND per_in_ler_id = p_pil_rec.per_in_ler_id;
1013 --
1014 END IF;
1015 --
1016 -- Populate Benefits Eligbility and Enrollment Fact
1017 INSERT INTO HRI_MB_BEN_ELIGENRL_EVNT_CT
1018 (change_date
1019 ,effective_start_date
1020 ,effective_end_date
1021 ,compobj_sk_pk
1022 ,asnd_lf_evt_dt
1023 ,person_id
1024 ,per_in_ler_id
1025 ,enrt_perd_id
1026 ,prtt_enrt_rslt_id
1027 ,elig_ind
1028 ,enrt_ind
1029 ,not_enrt_ind
1030 ,dflt_ind
1031 ,waive_expl_ind
1032 ,waive_dflt_ind)
1033 (SELECT ee.change_date change_date,
1034 ee.change_date effective_start_date,
1035 NVL(LEAD(ee.change_date - 1)
1036 OVER (PARTITION BY compobj_sk_pk
1037 ORDER BY compobj_sk_pk, ee.change_date), hr_api.g_eot) effective_end_date,
1038 ee.compobj_sk_pk,
1039 p_pil_rec.lf_evt_ocrd_dt asnd_lf_evt_dt ,
1040 p_pil_rec.person_id person_id,
1041 p_pil_rec.per_in_ler_id per_in_ler_id,
1042 ee.enrt_perd_id,
1043 ee.prtt_enrt_rslt_id,
1044 ee.elig_ind,
1045 ee.enrt_ind,
1046 ee.not_enrt_ind,
1047 ee.dflt_ind,
1048 ee.waive_expl_ind,
1049 ee.waive_dflt_ind
1050 FROM (
1051 -- The FIRST 2 UNIONS.. retuns all Electable Choices if Enrollments DOES NOT start on the same day.
1052 -- First UNION gets PLIPs and OIPL IS NULL
1053 SELECT pel.enrt_perd_strt_dt change_date,
1054 copd.compobj_sk_pk compobj_sk_pk,
1055 pel.enrt_perd_id,
1056 epe.prtt_enrt_rslt_id prtt_enrt_rslt_id,
1057 1 elig_ind,
1058 (CASE WHEN (epe.crntly_enrd_flag = 'Y')
1059 THEN 1
1060 ELSE 0 END ) enrt_ind,
1061 (CASE WHEN (epe.crntly_enrd_flag = 'Y')
1062 THEN 0
1063 ELSE 1 END ) not_enrt_ind,
1064 -- DFLT_IND -> If Currently Enrolled and Default Comp Object
1065 (CASE WHEN (pel.elcns_made_dt IS NULL
1066 AND pel.dflt_asnd_dt IS NOT NULL
1067 AND epe.crntly_enrd_flag = 'Y'
1068 AND epe.dflt_flag = 'Y')
1069 THEN 1
1070 ELSE 0 END) dflt_ind,
1071 -- WAIVE_EXPL_IND -> If Currently Enrolled and Waive Opt/Pln and Not Default Comp.Object
1072 (CASE WHEN (pel.elcns_made_dt IS NOT NULL
1073 AND epe.crntly_enrd_flag = 'Y'
1074 AND pln.invk_dcln_prtn_pl_flag = 'Y')
1075 THEN 1
1076 ELSE 0 END) waive_expl_ind,
1077 -- WAIVE_DFLT_IND -> If Currently Enrolled and Waive Opt/Pln and Default Comp.Object
1078 (CASE WHEN (pel.elcns_made_dt IS NULL
1079 AND pel.dflt_asnd_dt IS NOT NULL
1080 AND epe.dflt_flag = 'Y'
1081 AND pln.invk_dcln_prtn_pl_flag = 'Y')
1082 THEN 1
1083 ELSE 0 END) waive_dflt_ind
1084 FROM ben_elig_per_elctbl_chc epe,
1085 ben_pil_elctbl_chc_popl pel,
1086 hri_cs_compobj_ct copd,
1087 ben_pl_f pln
1088 WHERE epe.pil_elctbl_chc_popl_id = pel.pil_elctbl_chc_popl_id
1089 AND pel.per_in_ler_id = p_pil_rec.per_in_ler_id
1090 AND epe.elctbl_flag = 'Y'
1091 AND epe.elig_flag = 'Y'
1092 AND copd.oipl_id = -1
1093 AND epe.oipl_id IS NULL
1094 AND copd.plip_id = epe.plip_id
1095 AND copd.pgm_id = epe.pgm_id -- As required for Perf.
1096 AND copd.pl_id = epe.pl_id -- As required for Perf.
1097 AND pln.pl_id = copd.pl_id
1098 AND p_pil_rec.lf_evt_ocrd_dt BETWEEN pln.effective_start_date
1099 AND pln.effective_end_date
1100 AND (epe.prtt_enrt_rslt_id IS NULL
1101 OR NOT EXISTS (
1102 SELECT null
1103 FROM ben_prtt_enrt_rslt_f pen
1104 WHERE pen.prtt_enrt_rslt_id = epe.prtt_enrt_rslt_id
1105 AND pen.per_in_ler_Id = p_pil_rec.per_in_ler_id
1106 AND pen.prtt_enrt_rslt_stat_cd IS NULL
1107 AND pen.enrt_cvg_thru_dt = hr_api.g_eot
1108 AND pen.effective_end_date = hr_api.g_eot
1109 AND pen.effective_start_date = pel.enrt_perd_strt_dt))
1110 UNION ALL
1111 -- Second UNION gets OIPLs
1112 SELECT pel.enrt_perd_strt_dt change_date,
1113 copd.compobj_sk_pk compobj_sk_pk,
1114 pel.enrt_perd_id,
1115 epe.prtt_enrt_rslt_id prtt_enrt_rslt_id,
1116 1 elig_ind,
1117 (CASE WHEN (epe.crntly_enrd_flag = 'Y')
1118 THEN 1
1119 ELSE 0 END ) enrt_ind,
1120 (CASE WHEN (epe.crntly_enrd_flag = 'Y')
1121 THEN 0
1122 ELSE 1 END ) not_enrt_ind,
1123 -- DFLT_IND -> If Currently Enrolled and Default Comp Object
1124 (CASE WHEN (pel.elcns_made_dt IS NULL
1125 AND pel.dflt_asnd_dt IS NOT NULL
1126 AND epe.crntly_enrd_flag = 'Y'
1127 AND epe.dflt_flag = 'Y')
1128 THEN 1
1129 ELSE 0 END) dflt_ind,
1130 -- WAIVE_EXPL_IND -> If Currently Enrolled and Waive Opt/Pln and Not Default Comp.Object
1131 (CASE WHEN (pel.elcns_made_dt IS NOT NULL
1132 AND epe.crntly_enrd_flag = 'Y'
1133 AND opt.invk_wv_opt_flag = 'Y' )
1134 THEN 1
1135 ELSE 0 END) waive_expl_ind,
1136 -- WAIVE_DFLT_IND -> If Currently Enrolled and Waive Opt/Pln and Default Comp.Object
1137 (CASE WHEN (pel.elcns_made_dt IS NULL
1138 AND pel.dflt_asnd_dt IS NOT NULL
1139 AND epe.dflt_flag = 'Y'
1140 AND opt.invk_wv_opt_flag = 'Y')
1141 THEN 1
1142 ELSE 0 END) waive_dflt_ind
1143 FROM ben_elig_per_elctbl_chc epe,
1144 ben_pil_elctbl_chc_popl pel,
1145 hri_cs_compobj_ct copd,
1146 ben_opt_f opt
1147 WHERE epe.pil_elctbl_chc_popl_id = pel.pil_elctbl_chc_popl_id
1148 /* AND pel.per_in_ler_id = p_pil_rec.per_in_ler_id : 4552984 - Perf Fix */
1149 AND EPE.PER_IN_LER_ID = p_pil_rec.per_in_ler_id
1150 AND epe.elctbl_flag = 'Y'
1151 AND epe.elig_flag = 'Y'
1152 AND copd.oipl_id = epe.oipl_id
1153 AND copd.plip_id = epe.plip_id
1154 AND copd.pgm_id = epe.pgm_id
1155 AND copd.pl_id = epe.pl_id
1156 AND opt.opt_id = copd.opt_id
1157 AND p_pil_rec.lf_evt_ocrd_dt BETWEEN opt.effective_start_date
1158 AND opt.effective_end_date
1159 AND (epe.prtt_enrt_rslt_id IS NULL
1160 OR NOT EXISTS (
1161 SELECT null
1162 FROM ben_prtt_enrt_rslt_f pen
1163 WHERE pen.prtt_enrt_rslt_id = epe.prtt_enrt_rslt_id
1164 AND pen.per_in_ler_Id = p_pil_rec.per_in_ler_id
1165 AND pen.prtt_enrt_rslt_stat_cd IS NULL
1166 AND pen.enrt_cvg_thru_dt = hr_api.g_eot
1167 AND pen.effective_end_date = hr_api.g_eot
1168 AND pen.effective_start_date = pel.enrt_perd_strt_dt))
1169 UNION ALL
1170 -- The 3rd and 4th UNIONs.. returns all Enrollment Results.
1171 -- 3rd Union gets all PLIPs and OIPL IS NULL
1172 SELECT /*+ INDEX(epe BEN_ELIG_PER_ELCTBL_CHC_N5) */
1173 pen.effective_start_date change_date,
1174 copd.compobj_sk_pk compobj_sk_pk,
1175 pel.enrt_perd_id,
1176 pen.prtt_enrt_rslt_id prtt_enrt_rslt_id,
1177 1 elig_ind,
1178 1 enrt_ind,
1179 0 not_enrt_ind,
1180 (CASE WHEN (pel.elcns_made_dt IS NULL
1181 AND pel.dflt_asnd_dt IS NOT NULL
1182 AND epe.dflt_flag = 'Y')
1183 THEN 1
1184 ELSE 0 END) dflt_ind,
1185 (CASE WHEN (pel.elcns_made_dt IS NOT NULL
1186 AND pln.invk_dcln_prtn_pl_flag = 'Y')
1187 THEN 1
1188 ELSE 0 END) waive_expl_ind,
1189 (CASE WHEN (pel.elcns_made_dt IS NULL
1190 AND pel.dflt_asnd_dt IS NOT NULL
1191 AND epe.dflt_flag = 'Y'
1192 AND pln.invk_dcln_prtn_pl_flag = 'Y')
1193 THEN 1
1194 ELSE 0 END) waive_dflt_ind
1195 FROM ben_prtt_enrt_rslt_f pen,
1196 ben_pil_elctbl_chc_popl pel,
1197 hri_cs_compobj_ct copd,
1198 ben_elig_per_elctbl_chc epe,
1199 ben_pl_f pln
1200 WHERE pel.per_in_ler_id = p_pil_rec.per_in_ler_id
1201 AND pen.per_in_ler_id = pel.per_in_ler_id
1202 AND pen.pgm_id = copd.pgm_id
1203 AND pen.pgm_id = pel.pgm_id
1204 AND copd.oipl_id = -1
1205 AND pen.oipl_id IS NULL
1206 AND copd.pl_id = pen.pl_id
1207 AND pen.prtt_enrt_rslt_stat_cd IS NULL
1208 AND pen.enrt_cvg_thru_dt = hr_api.g_eot
1209 AND pen.effective_end_date = hr_api.g_eot
1210 AND epe.prtt_enrt_rslt_id(+) = pen.prtt_enrt_rslt_id
1211 AND epe.per_in_ler_id(+) = pen.per_in_ler_id
1212 AND pln.pl_id = copd.pl_id
1213 AND p_pil_rec.lf_evt_ocrd_dt BETWEEN pln.effective_start_date
1214 AND pln.effective_end_date
1215 UNION ALL
1216 -- 4th Union gets all OIPLs
1217 SELECT /*+ INDEX(epe BEN_ELIG_PER_ELCTBL_CHC_N5) */
1218 pen.effective_start_date change_date,
1219 copd.compobj_sk_pk compobj_sk_pk,
1220 pel.enrt_perd_id,
1221 pen.prtt_enrt_rslt_id prtt_enrt_rslt_id,
1222 1 elig_ind,
1223 1 enrt_ind,
1224 0 not_enrt_ind,
1225 (CASE WHEN (pel.elcns_made_dt IS NULL
1226 AND pel.dflt_asnd_dt IS NOT NULL
1227 AND epe.dflt_flag = 'Y')
1228 THEN 1
1229 ELSE 0 END) dflt_ind,
1230 (CASE WHEN (pel.elcns_made_dt IS NOT NULL
1231 AND opt.invk_wv_opt_flag = 'Y')
1232 THEN 1
1233 ELSE 0 END) waive_expl_ind,
1234 (CASE WHEN (pel.elcns_made_dt IS NULL
1235 AND pel.dflt_asnd_dt IS NOT NULL
1236 AND epe.dflt_flag = 'Y'
1237 AND opt.invk_wv_opt_flag = 'Y' )
1238 THEN 1
1239 ELSE 0 END) waive_dflt_ind
1240 FROM ben_prtt_enrt_rslt_f pen,
1241 ben_pil_elctbl_chc_popl pel,
1242 hri_cs_compobj_ct copd,
1243 ben_elig_per_elctbl_chc epe,
1244 ben_opt_f opt
1245 WHERE /* pel.per_in_ler_id = p_pil_rec.per_in_ler_id : 4552984 - Perf Fix */
1246 EPE.PER_IN_LER_ID = p_pil_rec.per_in_ler_id
1247 AND pen.per_in_ler_id = pel.per_in_ler_id
1248 AND pen.pgm_id = copd.pgm_id
1249 AND pen.pgm_id = pel.pgm_id
1250 AND copd.oipl_id = pen.oipl_id
1251 AND copd.pl_id = pen.pl_id
1252 AND pen.prtt_enrt_rslt_stat_cd IS NULL
1253 AND pen.enrt_cvg_thru_dt = hr_api.g_eot
1254 AND pen.effective_end_date = hr_api.g_eot
1255 AND epe.prtt_enrt_rslt_id(+) = pen.prtt_enrt_rslt_id
1256 AND epe.per_in_ler_id(+) = pen.per_in_ler_id
1257 AND opt.opt_id = copd.opt_id
1258 AND p_pil_rec.lf_evt_ocrd_dt BETWEEN opt.effective_start_date
1259 AND opt.effective_end_date
1260 ) ee
1261 );
1262 -- dbg('Leaving ' || l_procedure);
1263 EXCEPTION
1264 WHEN OTHERS THEN
1265 dbg('Error ' || l_procedure);
1266 OUTPUT('SQLERRM '|| SQLERRM);
1267 RAISE;
1268 END collect_eligenrl_evt;
1269
1270 --
1271 -- ----------------------------------------------------------------------------
1272 -- PROCESS_FULL_RANGE
1273 -- Is called in FULL REFRESH mode.
1274 -- ----------------------------------------------------------------------------
1275 --
1276 PROCEDURE process_full_range(
1277 p_start_object_id IN NUMBER
1278 ,p_end_object_id IN NUMBER) IS
1279
1280 CURSOR c_pil IS
1281 SELECT pil.per_in_ler_id,
1282 pil.person_id,
1283 pil.lf_evt_ocrd_dt,
1284 pil.per_in_ler_stat_cd,
1285 pil.business_group_id
1286 FROM ben_per_in_ler pil,
1287 ben_ler_f ler
1288 WHERE pil.ler_id = ler.ler_id
1289 AND ler.typ_cd = 'SCHEDDO'
1290 AND pil.per_in_ler_stat_cd IN ('STRTD','PROCD')
1291 AND pil.lf_evt_ocrd_dt >= g_global_start_date
1292 AND pil.person_id BETWEEN p_start_object_id AND p_end_object_id
1293 UNION
1294 SELECT pil3.per_in_ler_id,
1295 pil3.person_id,
1296 pil3.lf_evt_ocrd_dt,
1297 pil3.per_in_ler_stat_cd,
1298 pil3.business_group_id
1299 FROM ben_per_in_ler pil3,
1300 (SELECT MAX(pil1.per_in_ler_id) per_in_ler_id
1301 FROM ben_per_in_ler pil1,
1302 ben_ler_f ler1
1303 WHERE pil1.ler_id = ler1.ler_id
1304 AND ler1.typ_cd = 'SCHEDDO'
1305 AND pil1.per_in_ler_stat_cd IN ('BCKDT','VOIDD')
1306 AND pil1.lf_evt_ocrd_dt >= g_global_start_date
1307 AND pil1.person_id BETWEEN p_start_object_id AND p_end_object_id
1308 AND NOT EXISTS (SELECT null
1309 -- DO NOT pick up Backed/Voided events, if a Started/Processed Event exists.
1310 FROM ben_per_in_ler pil2,
1311 ben_ler_f ler2
1312 WHERE pil2.lf_evt_ocrd_dt = pil1.lf_evt_ocrd_dt
1313 AND pil2.person_id = pil1.person_id
1314 AND pil2.per_in_ler_stat_cd IN ('STRTD','PROCD')
1315 AND pil2.ler_id = ler2.ler_id
1316 AND ler2.typ_cd = 'SCHEDDO'
1317 AND pil2.lf_evt_ocrd_dt BETWEEN ler2.effective_start_date
1318 AND ler2.effective_end_date)
1319 GROUP BY pil1.person_id, pil1.lf_evt_ocrd_dt
1320 ) pil4
1321 WHERE pil4.per_in_ler_id = pil3.per_in_ler_id
1322 AND pil3.lf_evt_ocrd_dt >= g_global_start_date
1323 ORDER BY 2, 3;
1324 --
1325 CURSOR c_pil_oe IS
1326 SELECT pil.per_in_ler_id,
1327 pil.person_id,
1328 pil.lf_evt_ocrd_dt,
1329 pil.per_in_ler_stat_cd,
1330 pil.business_group_id
1331 FROM ben_per_in_ler pil,
1332 (SELECT MAX(pil.lf_evt_ocrd_dt) lf_evt_ocrd_dt
1333 , pil.business_group_id
1334 , pil.ler_id
1335 FROM ben_per_in_ler pil,
1336 ben_ler_f ler
1337 WHERE pil.ler_id = ler.ler_id
1338 AND ler.typ_cd = 'SCHEDDO'
1339 AND pil.per_in_ler_stat_cd = 'STRTD'
1340 AND pil.lf_evt_ocrd_dt BETWEEN ler.effective_start_date
1341 AND ler.effective_end_date
1342 GROUP BY pil.business_group_id, pil.ler_id ) pil1
1343 WHERE pil.ler_id = pil1.ler_id
1344 AND pil.business_group_id = pil1.business_group_id
1345 AND pil.lf_evt_ocrd_dt = pil1.lf_evt_ocrd_dt
1346 AND pil.lf_evt_ocrd_dt >= g_global_start_date
1347 AND pil.person_id BETWEEN p_start_object_id AND p_end_object_id
1348 ORDER BY 2, 3;
1349 --
1350 l_pil_tbl g_pil_tab_type;
1351 l_procedure VARCHAR2(100) := g_package || 'pre_process';
1352 --
1353 BEGIN
1354 -- dbg('Entering ' || l_procedure);
1355 -- output('Range ' || p_start_object_id || ' : ' || p_end_object_id);
1356 IF (g_collect_oe_only = 'Y') THEN
1357 --
1358 OPEN c_pil_oe;
1359 FETCH c_pil_oe BULK COLLECT INTO l_pil_tbl;
1360 CLOSE c_pil_oe;
1361 --
1362 ELSE
1363 --
1364 OPEN c_pil;
1365 FETCH c_pil BULK COLLECT INTO l_pil_tbl;
1366 CLOSE c_pil;
1367 --
1368 END IF;
1369 -- For each PIL collect the Election and Enrollment Information
1370 IF (l_pil_tbl.count > 0 ) THEN
1371 FOR i IN l_pil_tbl.FIRST..l_pil_tbl.LAST LOOP
1372 -- 1. Load the Election Events Fact
1373 COLLECT_ELCN_EVT (p_pil_rec => l_pil_tbl(i));
1374
1375 -- 2. Load the Elibility and Enrollment Fact
1376 IF (l_pil_tbl(i).per_in_ler_stat_cd IN ('STRTD','PROCD')) THEN
1377 COLLECT_ELIGENRL_EVT (p_pil_rec => l_pil_tbl(i));
1378 END IF;
1379 --
1380 END LOOP;
1381 END IF;
1382 --
1383 -- dbg('Leaving ' || l_procedure);
1384 EXCEPTION
1385 WHEN OTHERS THEN
1386 dbg('Error ' || l_procedure);
1387 OUTPUT('SQLERRM '|| SQLERRM);
1388 RAISE;
1389 END process_full_range;
1390
1391 --
1392 -- ----------------------------------------------------------------------------
1393 -- PROCESS_INCR_RANGE
1394 -- This procedure is called in INCREMENTAL REFRESH mode.
1395 -- ----------------------------------------------------------------------------
1396 --
1397 PROCEDURE process_incr_range(
1398 p_start_object_id IN NUMBER
1399 ,p_end_object_id IN NUMBER) IS
1400
1401 CURSOR c_per_in_evt IS
1402 SELECT DISTINCT penq.person_id, penq.per_in_ler_id, lf_evt_ocrd_dt
1403 FROM HRI_EQ_BEN_ELIGENRL_EVTS penq
1404 WHERE penq.person_id BETWEEN p_start_object_id AND p_end_object_id
1405 ORDER BY penq.person_id, penq.per_in_ler_id;
1406
1407 CURSOR c_elcn_evt IS
1408 SELECT DISTINCT pelq.person_id, pelq.per_in_ler_id
1409 FROM HRI_EQ_BEN_ELCTN_EVTS pelq
1410 WHERE pelq.person_id BETWEEN p_start_object_id AND p_end_object_id
1411 ORDER BY pelq.person_id, pelq.per_in_ler_id;
1412 --
1413 l_person_tbl g_number_tab_type;
1414 l_pil_tbl g_number_tab_type;
1415 l_lf_evt_ocrd_dt_tbl g_date_tab_type;
1416
1417 l_procedure VARCHAR2(100) := g_package || 'process_incr_range';
1418
1419 BEGIN
1420 -- dbg('Entering ' || l_procedure);
1421 -- dbg('start ' || p_start_object_id || ' end ' || p_end_object_id );
1422 -- 1. Load the Election Events Fact
1423 OPEN c_elcn_evt;
1424 FETCH c_elcn_evt BULK COLLECT INTO l_person_tbl, l_pil_tbl;
1425 CLOSE c_elcn_evt;
1426 --
1427 IF (l_person_tbl.COUNT > 0) THEN
1428 FOR i IN l_person_tbl.FIRST..l_person_tbl.LAST LOOP
1429 --
1430 COLLECT_ELCN_EVT (p_person_id => l_person_tbl(i)
1431 ,p_per_in_ler_id => l_pil_tbl(i));
1432 --
1433 END LOOP;
1434 END IF;
1435 --
1436 l_person_tbl.delete;
1437 l_pil_tbl.delete;
1438
1439 -- 2. Load the Elibility and Enrollment Fact
1440 OPEN c_per_in_evt;
1441 FETCH c_per_in_evt BULK COLLECT INTO l_person_tbl, l_pil_tbl, l_lf_evt_ocrd_dt_tbl;
1442 CLOSE c_per_in_evt;
1443 --
1444 IF (l_person_tbl.COUNT > 0) THEN
1445 FOR i IN l_person_tbl.FIRST..l_person_tbl.LAST LOOP
1446 --
1447 COLLECT_ELIGENRL_EVT (p_person_id => l_person_tbl(i)
1448 ,p_per_in_ler_id => l_pil_tbl(i)
1449 ,p_lf_evt_ocrd_dt => l_lf_evt_ocrd_dt_tbl(i));
1450 --
1451 END LOOP;
1452 END IF;
1453 -- dbg('Leaving ' || l_procedure);
1454 EXCEPTION
1455 WHEN OTHERS THEN
1456 dbg('Error ' || l_procedure);
1457 OUTPUT ('SQLERRM ' || SQLERRM );
1458 RAISE;
1459 END process_incr_range;
1460
1461 -- ----------------------------------------------------------------------------
1462 -- PROCESS_RANGE
1463 -- This procedure is dynamically called from HRI Multithreading utility.
1464 -- Calls Collection procedures for Election Event and Elibility Enrollment Event Facts
1465 -- for All PER_IN_LER_IDs obtained from the thread range.
1466 -- ----------------------------------------------------------------------------
1467 PROCEDURE process_range(
1468 errbuf OUT NOCOPY VARCHAR2
1469 ,retcode OUT NOCOPY NUMBER
1470 ,p_mthd_action_id IN NUMBER
1471 ,p_mthd_range_id IN NUMBER
1472 ,p_start_object_id IN NUMBER
1473 ,p_end_object_id IN NUMBER) IS
1474
1475 l_procedure VARCHAR2(100) := g_package || 'process_range';
1476
1477 BEGIN
1478 -- dbg('Entering ' || l_procedure);
1479 -- 1. Set parameters for this thread.
1480 set_parameters(p_mthd_action_id => p_mthd_action_id
1481 ,p_mthd_range_id => p_mthd_range_id);
1482 --
1483 IF g_full_refresh = 'Y' THEN
1484 --
1485 process_full_range(p_start_object_id => p_start_object_id
1486 ,p_end_object_id => p_end_object_id);
1487 --
1488 ELSE
1489 --
1490 process_incr_range(p_start_object_id => p_start_object_id
1491 ,p_end_object_id => p_end_object_id);
1492 --
1493 END IF;
1494 --
1495 errbuf := 'SUCCESS';
1496 retcode := 0;
1497 -- dbg('Leaving ' || l_procedure);
1498 EXCEPTION
1499 WHEN others THEN
1500 dbg('Error ' || l_procedure);
1501 output('Error encountered while processing range = '|| p_mthd_range_id );
1502 output('SQLERRM ' || SQLERRM);
1503 errbuf := SQLERRM;
1504 retcode := SQLCODE;
1505 ROLLBACK;
1506 --
1507 RAISE;
1508 --
1509 END process_range;
1510 --
1511 -- ----------------------------------------------------------------------------
1512 -- POST_PROCESS
1513 -- This procedure is dynamically invoked by the HRI Multithreading utility.
1514 -- It performs all the clean up action for after collection.
1515 -- Enable the MV logs
1516 -- Purge the Election and Eligibility Events' incremental events queue
1517 -- Update BIS Refresh Log
1518 -- ----------------------------------------------------------------------------
1519 --
1520 PROCEDURE post_process (p_mthd_action_id NUMBER) IS
1521 --
1522 l_dummy1 VARCHAR2(2000);
1523 l_dummy2 VARCHAR2(2000);
1524 l_schema VARCHAR2(400);
1525
1526 l_procedure VARCHAR2(100) := g_package || 'post_process';
1527 --
1528 BEGIN
1529 --
1530 dbg('Entering ' || l_procedure);
1531 --
1532 set_parameters(p_mthd_action_id);
1533 OUTPUT('Full Refresh Flag : ' || g_full_refresh);
1534 OUTPUT('Open Enr Only Flag : ' || g_collect_oe_only);
1535 --
1536 -- Recreate indexes and gather stats for full refresh or shared HR insert
1537 --
1538 IF (fnd_installation.get_app_info('HRI',l_dummy1, l_dummy2, l_schema)) THEN
1539
1540 IF (g_full_refresh = 'Y') THEN
1541 --
1542 OUTPUT('Full Refresh selected - Creating indexes');
1543 --
1544 HRI_UTL_DDL.recreate_indexes(
1545 p_application_short_name => 'HRI',
1546 p_table_name => g_eligenrl_evnt_table,
1547 p_table_owner => l_schema);
1548 --
1549 HRI_UTL_DDL.recreate_indexes(
1550 p_application_short_name => 'HRI',
1551 p_table_name => g_elctn_evnt_table,
1552 p_table_owner => l_schema);
1553 --
1554 ELSE
1555 -- Incremental Changes
1556 -- Purge the Events Queue. The events queue needs to be purged
1557 -- even after the after full refresh. Recollecting incremental changes
1558 -- will be useless if a full refresh has been run.
1559 IF (HRI_BPL_BEN_UTIL.get_archive_events = 'Y') THEN
1560 -- If Event Queue is to be be archived
1561 -- Devl in Phase-2
1562 OUTPUT('Archive Event Queue Table..');
1563 --
1564 END IF;
1565 --
1566 -- Enable the WHO trigger on the events fact table
1567 END IF;
1568 OUTPUT('Truncate Event Queue Tables..');
1569 -- Required in both modes
1570 EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || l_schema || '.' || g_elctn_evts_eq_table;
1571 EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || l_schema || '.' || g_eligenrl_evts_eq_table;
1572 --
1573 OUTPUT('Gathering Stats for Election Events');
1574 --
1575 fnd_stats.gather_table_stats(l_schema, g_elctn_evnt_table);
1576 -- fnd_stats.gather_table_stats(l_schema, g_eligenrl_evnt_table);
1577 --
1578 END IF;
1579 --
1580 dbg('Leaving ' || l_procedure);
1581 --
1582 EXCEPTION
1583 WHEN OTHERS THEN
1584 --
1585 dbg('Error ' || l_procedure);
1586 OUTPUT(' SQLERRM '|| SQLERRM);
1587 rollback;
1588 RAISE;
1589 --
1590 END post_process;
1591 --
1592
1593 END HRI_OPL_BEN_ELIG_ENRL;