[Home] [Help]
PACKAGE BODY: APPS.HRI_OPL_BEN_DIM_REFRESH
Source
1 package body hri_opl_ben_dim_refresh as
2 /* $Header: hripbdrf.pkb 120.0 2005/09/21 01:27:42 anmajumd noship $ */
3
4 --
5 -- Holds the range for which the collection is to be run.
6 --
7 g_full_refresh VARCHAR2(10);
8 g_business_group_id NUMBER(15);
9 --
10 -- The HRI schema
11 --
12 g_schema VARCHAR2(400);
13 --
14 -- Set to true to output to a concurrent log file
15 --
16 g_conc_request_flag BOOLEAN := FALSE;
17 --
18 --
19 -- CONSTANTS
20 -- =========
21 --
22 -- @@ Code specific to this view/table below
23 -- @@ in the call to hri_bpl_conc_log.get_last_collect_to_date
24 -- @@ change param1/2 to be the concurrent program short name,
25 -- @@ and the target table name respectively.
26 --
27 g_cncrnt_prgrm_shrtnm VARCHAR2(30) DEFAULT 'HRI_CS_COMP_OBJ_ENRT_PERD';
28 --
29 -- @@ Code specific to this view/table below ENDS
30 --
31 -- constants that hold the value that indicates to full refresh or not.
32 --
33 g_is_full_refresh VARCHAR2(5) DEFAULT 'Y';
34 g_not_full_refresh VARCHAR2(5) DEFAULT 'N';
35 --
36 -- -----------------------------------------------------------------------------
37 -- Inserts row into concurrent program log if debugging is enabled
38 -- -----------------------------------------------------------------------------
39 PROCEDURE dbg(p_text VARCHAR2) IS
40 BEGIN
41 --
42 HRI_BPL_CONC_LOG.dbg(p_text);
43 -- DBMS_OUTPUT.PUT_LINE(p_text);
44 --
45 END dbg;
46 --
47 -- ----------------------------------------------------------------------------
48 -- |-------------------------------< OUTPUT >----------------------------------|
49 -- ----------------------------------------------------------------------------
50 -- Inserts row into concurrent program log when the g_conc_request_flag has
51 -- been set to TRUE, otherwise does nothing
52 --
53 PROCEDURE output(p_text VARCHAR2)
54 IS
55 --
56 BEGIN
57 --
58 -- Write to the concurrent request log if called from a concurrent request
59 --
60 IF (g_conc_request_flag = TRUE) THEN
61 --
62 -- Put text to log file
63 --
64 fnd_file.put_line(FND_FILE.log, p_text);
65 --
66 ELSE
67 --
68 hr_utility.set_location(p_text, 999);
69 --dbms_output.put_line(p_text);
70 --
71 END IF;
72 --
73 END output;
74 --
75 -- ----------------------------------------------------------------------------
76 -- |--------------------------< RUN_SQL_STMT_NOERR >--------------------------|
77 -- ----------------------------------------------------------------------------
78 --
79 -- Runs given sql statement dynamically without raising an error
80 --
81 PROCEDURE run_sql_stmt_noerr( p_sql_stmt VARCHAR2 )
82 IS
83 --
84 BEGIN
85 --
86 EXECUTE IMMEDIATE p_sql_stmt;
87 --
88 EXCEPTION WHEN OTHERS THEN
89 --
90 output('Error running sql:');
91 output(SUBSTR(p_sql_stmt,1,230));
92 --
93 END run_sql_stmt_noerr;
94 --
95 --
96 -- ----------------------------------------------------------------------------
97 -- |--------------------------< INCREMENTAL_UPDATE >--------------------------|
98 -- ----------------------------------------------------------------------------
99 --
100 -- Loops through table and collects into table structure.
101 --
102 PROCEDURE Incremental_Update IS
103 --
104 l_sql_stmt VARCHAR2(2000);
105 l_current_time DATE := SYSDATE;
106 l_user_id NUMBER := fnd_global.user_id;
107 l_effective_date DATE := TRUNC(SYSDATE);
108 l_business_group_id NUMBER(15) := g_business_group_id;
109 l_login_id NUMBER := fnd_global.login_id;
110 --
111 CURSOR c_pgm IS
112 SELECT pgm.pgm_id pgm_id
113 FROM ben_pgm_f pgm
114 WHERE l_effective_date BETWEEN pgm.effective_start_date
115 AND pgm.effective_end_date
116 AND pgm.pgm_typ_cd IN ('CORE', 'FLEX', 'FPC', 'OTHER')
117 AND EXISTS (
118 SELECT 'x'
119 FROM ben_popl_enrt_typ_cycl_f pet, ben_enrt_perd enp
120 WHERE pet.pgm_id = pgm.pgm_id
121 AND pet.enrt_typ_cycl_cd = 'O'
122 AND l_effective_date BETWEEN pet.effective_start_date
123 AND pet.effective_end_date
124 AND pet.popl_enrt_typ_cycl_id = enp.popl_enrt_typ_cycl_id)
125 AND NOT EXISTS (SELECT 'x'
126 FROM HRI_CS_CO_PGM_CT pgmd
127 WHERE pgmd.pgm_id = pgm.pgm_id);
128 --
129 CURSOR c_ptip IS
130 SELECT ctp.ptip_id ptip_id, ctp.pl_typ_id pl_typ_id, ctp.pgm_id pgm_id,
131 ctp.ordr_num ptip_ordr_num
132 FROM ben_ptip_f ctp, ben_pl_typ_f ptp
133 WHERE ctp.pl_typ_id = ptp.pl_typ_id
134 AND l_effective_date BETWEEN ctp.effective_start_date
135 AND ctp.effective_end_date
136 AND l_effective_date BETWEEN ptp.effective_start_date
137 AND ptp.effective_end_date
138 AND ptp.opt_typ_cd <> 'SPDGACCT'
139 AND EXISTS (SELECT 'x'
140 FROM hri_cs_co_pgm_ct pgmd
141 WHERE pgmd.pgm_id = ctp.pgm_id)
142 AND NOT EXISTS (SELECT 'x'
143 FROM hri_cs_co_pgmh_ptip_ct ctpd
144 WHERE ctpd.ptip_id = ctp.ptip_id);
145
146 --
147 CURSOR c_plip IS
148 SELECT cpp.plip_id plip_id, cpp.pl_id pl_id, cpp.pgm_id pgm_id,
149 ctpd.ptip_id ptip_id, ctpd.pl_typ_id pl_typ_id, cpp.ordr_num plip_ordr_num
150 FROM ben_plip_f cpp, ben_pl_f pln, hri_cs_co_pgmh_ptip_ct ctpd
151 WHERE cpp.pl_id = pln.pl_id
152 AND pln.pl_typ_id = ctpd.pl_typ_id
153 AND cpp.pgm_id = ctpd.pgm_id
154 AND pln.invk_flx_cr_pl_flag = 'N'
155 AND pln.imptd_incm_calc_cd IS NULL
156 AND l_effective_date BETWEEN cpp.effective_start_date
157 AND cpp.effective_end_date
158 AND l_effective_date BETWEEN pln.effective_start_date
159 AND pln.effective_end_date
160 AND NOT EXISTS (SELECT 'x'
161 FROM HRI_CS_CO_PGMH_PLIP_CT cppd
162 WHERE cppd.plip_id = cpp.plip_id);
163 --
164 CURSOR c_plip_ernt_pl IS
165 SELECT cpp.plip_id plip_id, cpp.pl_id pl_id, cpp.pgm_id pgm_id,
166 ctpd.ptip_id ptip_id, ctpd.pl_typ_id pl_typ_id
167 FROM ben_plip_f cpp, ben_pl_f pln, hri_cs_co_pgmh_ptip_ct ctpd
168 WHERE cpp.pl_id = pln.pl_id
169 AND pln.pl_typ_id = ctpd.pl_typ_id
170 AND cpp.pgm_id = ctpd.pgm_id
171 AND ( pln.enrt_pl_opt_flag = 'Y'
172 OR NOT EXISTS (
173 SELECT 'x'
174 FROM ben_oipl_f cop
175 WHERE cop.pl_id = pln.pl_id
176 AND l_effective_date BETWEEN cop.effective_start_date
177 AND cop.effective_end_date)
178 )
179 AND l_effective_date BETWEEN cpp.effective_start_date
180 AND cpp.effective_end_date
181 AND l_effective_date BETWEEN pln.effective_start_date
182 AND pln.effective_end_date
183 AND pln.invk_flx_cr_pl_flag = 'N'
184 AND pln.imptd_incm_calc_cd IS NULL
185 AND NOT EXISTS (SELECT 'x'
186 FROM HRI_CS_COMPOBJ_CT oppd
187 WHERE oppd.plip_id = cpp.plip_id
188 AND oppd.oiplip_id = -1);
189 --
190 CURSOR c_oiplip IS
191 SELECT opp.oiplip_id oiplip_id, opp.oipl_id oipl_id, cop.opt_id opt_id,
192 opp.plip_id plip_id, cppd.pl_typ_id pl_typ_id,
193 cppd.ptip_id ptip_id, cppd.pl_id pl_id, cppd.pgm_id pgm_id, cop.ordr_num oipl_ordr_num
194 FROM ben_oiplip_f opp, ben_oipl_f cop, hri_cs_co_pgmh_plip_ct cppd
195 WHERE opp.plip_id = cppd.plip_id
196 AND cop.oipl_id = opp.oipl_id
197 AND l_effective_date BETWEEN opp.effective_start_date
198 AND opp.effective_end_date
199 AND l_effective_date BETWEEN cop.effective_start_date
200 AND cop.effective_end_date
201 AND NOT EXISTS (SELECT 'x'
202 FROM HRI_CS_COMPOBJ_CT oppd
203 WHERE oppd.oiplip_id = opp.oiplip_id);
204
205 --
206
207 CURSOR c_popl_rptg_grp
208 IS
209 SELECT rgr.popl_rptg_grp_id, rgr.rptg_grp_id, rgr.pgm_id
210 FROM ben_popl_rptg_grp_f rgr, hri_cs_co_pgm_ct pgmd
211 WHERE rgr.pgm_id IS NOT NULL
212 AND rgr.pgm_id = pgmd.pgm_id
213 AND l_effective_date BETWEEN rgr.effective_start_date
214 AND rgr.effective_end_date
215 AND NOT EXISTS (SELECT 'x'
216 FROM HRI_CS_CO_RPGH_PIRG_CT rgrd
217 WHERE rgrd.RPTGTYP_ID = rgr.rptg_grp_id
218 AND rgrd.pgm_id = rgr.pgm_id);
219 --
220 CURSOR c_pgm_enrt_perd
221 IS
222 SELECT enp.enrt_perd_id, pgmd.pgm_id, enp.strt_dt, enp.end_dt, enp.ASND_LF_EVT_DT ASND_LF_EVT_DT
223 FROM hri_cs_co_pgm_ct pgmd,
224 ben_popl_enrt_typ_cycl_f pet,
225 ben_enrt_perd enp
226 WHERE pgmd.pgm_id = pet.pgm_id
227 AND pet.popl_enrt_typ_cycl_id = enp.popl_enrt_typ_cycl_id
228 AND l_effective_date BETWEEN pet.effective_start_date
229 AND pet.effective_end_date
230 AND NOT EXISTS (SELECT 'x'
231 FROM HRI_CS_TIME_BENRL_PRD_CT enpd
232 WHERE enpd.enrt_perd_id = enp.enrt_perd_id);
233 --
234 CURSOR c_pl_enrt_perd
235 IS
236 SELECT enp.enrt_perd_id, enpd.pgm_id, enp.strt_dt, enp.end_dt,
237 enp.asnd_lf_evt_dt ASND_LF_EVT_DT
238 FROM hri_cs_time_benrl_prd_ct enpd,
239 ben_plip_f cpp,
240 ben_popl_enrt_typ_cycl_f pet,
241 ben_enrt_perd enp
242 WHERE enpd.pgm_id = cpp.pgm_id
243 AND pet.pl_id = cpp.pl_id
244 AND pet.popl_enrt_typ_cycl_id = enp.popl_enrt_typ_cycl_id
245 AND enpd.ASND_LF_EVT_DT = enp.asnd_lf_evt_dt
246 AND l_effective_date BETWEEN cpp.effective_start_date
247 AND cpp.effective_end_date
248 AND l_effective_date BETWEEN pet.effective_start_date
249 AND pet.effective_end_date
250 AND NOT EXISTS (SELECT 'x'
251 FROM HRI_CS_TIME_BENRL_PRD_CT enpd
252 WHERE enpd.enrt_perd_id = enp.enrt_perd_id);
253 --
254 BEGIN
255 --
256 output('Inside Incremental Update');
257 --
258 -- (1) Populate dimension table HRI_CS_CO_PGM_CT
259 --
260 FOR l_pgm_row in c_pgm LOOP
261 --
262 INSERT INTO HRI_CS_CO_PGM_CT
263 ( PGM_id
264 , LAST_UPDATE_DATE
265 , LAST_UPDATED_BY
266 , LAST_UPDATE_LOGIN
267 , CREATED_BY
268 , CREATION_DATE
269 )
270 VALUES
271 ( l_pgm_row.pgm_id
272 , l_current_time
273 , l_user_id
274 , l_login_id
275 , l_user_id
276 , l_current_time
277 );
278 --
279 END LOOP;
280 --
281 output('Populated Program Dimension: ' || to_char(sysdate,'HH24:MI:SS'));
282 --
283 -- (2) Populate dimension table HRI_CS_CO_PGMH_PTIP_CT
284 --
285 FOR l_ptip_row in c_ptip LOOP
286 --
287 INSERT INTO HRI_CS_CO_PGMH_PTIP_CT
288 ( ptip_id
289 , pl_typ_id
290 , PGM_ID
291 , ptip_ordr_num
292 , LAST_UPDATE_DATE
293 , LAST_UPDATED_BY
294 , LAST_UPDATE_LOGIN
295 , CREATED_BY
296 , CREATION_DATE
297 )
298 VALUES
299 ( l_ptip_row.ptip_id
300 , l_ptip_row.pl_typ_id
301 , l_ptip_row.PGM_ID
302 , l_ptip_row.ptip_ordr_num
303 , l_current_time
304 , l_user_id
305 , l_login_id
306 , l_user_id
307 , l_current_time
308 );
309 --
310 END LOOP;
311 --
312 output('Populated Plan Type In Program Dimension: ' || to_char(sysdate,'HH24:MI:SS'));
313 --
314 --
315 -- (3) Populate dimension table HRI_CS_CO_PGMH_PLIP_CT
316 --
317 FOR l_plip_row in c_plip LOOP
318 --
319 INSERT INTO HRI_CS_CO_PGMH_PLIP_CT
320 ( plip_id
321 , pl_id
322 , PGM_ID
323 , ptip_id
324 , pl_typ_id
325 , plip_ordr_num
326 , LAST_UPDATE_DATE
327 , LAST_UPDATED_BY
328 , LAST_UPDATE_LOGIN
329 , CREATED_BY
330 , CREATION_DATE
331 )
332 VALUES
333 ( l_plip_row.plip_id
334 , l_plip_row.pl_id
335 , l_plip_row.PGM_ID
336 , l_plip_row.ptip_id
337 , l_plip_row.pl_typ_id
338 , l_plip_row.plip_ordr_num
339 , l_current_time
340 , l_user_id
341 , l_login_id
342 , l_user_id
343 , l_current_time
344 );
345 --
346 END LOOP;
347 --
348 output('Populated Plan In Program Dimension: ' || to_char(sysdate,'HH24:MI:SS'));
349 --
350 -- (4) Populate dimension table HRI_CS_COMPOBJ_CT
351 --
352 FOR l_oiplip_row in c_oiplip LOOP
353 --
354 INSERT INTO HRI_CS_COMPOBJ_CT
355 ( compobj_sk_pk
356 , oiplip_id
357 , oipl_id
358 , opt_id
359 , pl_id
360 , plip_id
361 , PGM_ID
362 , ptip_id
363 , pl_typ_id
364 , oipl_ordr_num
365 , LAST_UPDATE_DATE
366 , LAST_UPDATED_BY
367 , LAST_UPDATE_LOGIN
368 , CREATED_BY
369 , CREATION_DATE
370 )
371 VALUES
372 ( HRI_CS_COMPOBJ_CT_s.nextval
373 , l_oiplip_row.oiplip_id
374 , l_oiplip_row.oipl_id
375 , l_oiplip_row.opt_id
376 , l_oiplip_row.pl_id
377 , l_oiplip_row.plip_id
378 , l_oiplip_row.PGM_ID
379 , l_oiplip_row.ptip_id
380 , l_oiplip_row.pl_typ_id
381 , l_oiplip_row.oipl_ordr_num
382 , l_current_time
383 , l_user_id
384 , l_login_id
385 , l_user_id
386 , l_current_time
387 );
388 --
389 END LOOP;
390 --
391 -- (5) Populate dimension table HRI_CS_COMPOBJ_CT for Enrollments at Plan Level
392 --
393 FOR l_plip_ernt_pl_row in c_plip_ernt_pl LOOP
394 --
395 INSERT INTO HRI_CS_COMPOBJ_CT
396 ( compobj_sk_pk
397 , oiplip_id
398 , oipl_id
399 , opt_id
400 , pl_id
401 , plip_id
402 , PGM_ID
403 , ptip_id
404 , pl_typ_id
405 , oipl_ordr_num
406 , LAST_UPDATE_DATE
407 , LAST_UPDATED_BY
408 , LAST_UPDATE_LOGIN
409 , CREATED_BY
410 , CREATION_DATE
411 )
412 VALUES
413 ( HRI_CS_COMPOBJ_CT_s.nextval
414 , -1
415 , -1
416 , NULL
417 , l_plip_ernt_pl_row.pl_id
418 , l_plip_ernt_pl_row.plip_id
419 , l_plip_ernt_pl_row.PGM_ID
420 , l_plip_ernt_pl_row.ptip_id
421 , l_plip_ernt_pl_row.pl_typ_id
422 , NULL
423 , l_current_time
424 , l_user_id
425 , l_login_id
426 , l_user_id
427 , l_current_time
428 );
429 --
430 END LOOP;
431 --
432 output('Populated Compensation Object Dimension: ' || to_char(sysdate,'HH24:MI:SS'));
433 --
434 -- (6) Populate dimension table HRI_CS_CO_RPGH_PIRG_CT for Reporting Group Information
435 --
436 FOR l_popl_rptg_grp_row in c_popl_rptg_grp LOOP
437 --
438 INSERT INTO HRI_CS_CO_RPGH_PIRG_CT
439 ( PIRG_SK_PK
440 , RPTGTYP_ID
441 , pgm_id
442 , LAST_UPDATE_DATE
443 , LAST_UPDATED_BY
444 , LAST_UPDATE_LOGIN
445 , CREATED_BY
446 , CREATION_DATE
447 )
448 VALUES
449 ( HRI_CS_CO_RPGH_PIRG_CT_S.nextval
450 , l_popl_rptg_grp_row.rptg_grp_id
451 , l_popl_rptg_grp_row.pgm_id
452 , l_current_time
453 , l_user_id
454 , l_login_id
455 , l_user_id
456 , l_current_time
457 );
458 --
459 END LOOP;
460 --
461 output('Populated Reporting Group Dimension: ' || to_char(sysdate,'HH24:MI:SS'));
462 --
463 -- (7) Populate dimension table HRI_CS_TIME_BENRL_PRD_CT for Program Enrollment Periods
464 --
465 FOR l_enrt_perd_row in c_pgm_enrt_perd LOOP
466 --
467 INSERT INTO HRI_CS_TIME_BENRL_PRD_CT
468 ( enrt_perd_id
469 , pgm_id
470 , enrt_strt_dt
471 , enrt_thru_dt
472 , ASND_LF_EVT_DT
473 , LAST_UPDATE_DATE
474 , LAST_UPDATED_BY
475 , LAST_UPDATE_LOGIN
476 , CREATED_BY
477 , CREATION_DATE
478 )
479 VALUES
480 ( l_enrt_perd_row.enrt_perd_id
481 , l_enrt_perd_row.pgm_id
482 , l_enrt_perd_row.strt_dt
483 , l_enrt_perd_row.end_dt
484 , l_enrt_perd_row.ASND_LF_EVT_DT
485 , l_current_time
486 , l_user_id
487 , l_login_id
488 , l_user_id
489 , l_current_time
490 );
491 --
492 END LOOP;
493 --
494 -- (8) Populate dimension table HRI_CS_TIME_BENRL_PRD_CT for Plan Enrollment Periods
495 --
496 FOR l_pl_enrt_perd_row in c_pl_enrt_perd LOOP
497 --
498 INSERT INTO HRI_CS_TIME_BENRL_PRD_CT
499 ( enrt_perd_id
500 , pgm_id
501 , enrt_strt_dt
502 , enrt_thru_dt
503 , ASND_LF_EVT_DT
504 , LAST_UPDATE_DATE
505 , LAST_UPDATED_BY
506 , LAST_UPDATE_LOGIN
507 , CREATED_BY
508 , CREATION_DATE
509 )
510 VALUES
511 ( l_pl_enrt_perd_row.enrt_perd_id
512 , l_pl_enrt_perd_row.pgm_id
513 , l_pl_enrt_perd_row.strt_dt
514 , l_pl_enrt_perd_row.end_dt
515 , l_pl_enrt_perd_row.ASND_LF_EVT_DT
516 , l_current_time
517 , l_user_id
518 , l_login_id
519 , l_user_id
520 , l_current_time
521 );
522 --
523 END LOOP;
524 --
525 output('Populated Enrollment Period Dimension: ' || to_char(sysdate,'HH24:MI:SS'));
526 --
527 -- Delete rows that no longer exist in the source tables
528 --
529 -- Program Dimension
530 --
531 DELETE FROM HRI_CS_CO_PGM_CT pgmd
532 WHERE NOT EXISTS (SELECT 'x'
533 FROM ben_pgm_f pgm
534 WHERE pgm.pgm_id = pgmd.pgm_id);
535 --
536 -- Plan Type In Program Dimension
537 --
538 DELETE FROM HRI_CS_CO_PGMH_PTIP_CT ctpd
539 WHERE NOT EXISTS (SELECT 'x'
540 FROM ben_ptip_f ctp
541 WHERE ctp.ptip_id = ctpd.ptip_id);
542 --
543 -- Plan In Program Dimension
544 --
545 DELETE FROM HRI_CS_CO_PGMH_PLIP_CT cppd
546 WHERE NOT EXISTS (SELECT 'x'
547 FROM ben_plip_f cpp
548 WHERE cpp.plip_id = cppd.plip_id);
549 --
550 -- Option In Plan In Program Dimension
551 --
552 DELETE FROM HRI_CS_COMPOBJ_CT oppd
553 WHERE oppd.oiplip_id <> -1
554 AND NOT EXISTS (SELECT 'x'
555 FROM ben_oiplip_f opp
556 WHERE opp.oiplip_id = oppd.oiplip_id);
557 --
558 -- Plan In Program Dimension (Enrol In Plan And Option - Checked)
559 --
560 DELETE FROM hri_cs_compobj_ct oppd
561 WHERE oppd.oiplip_id = -1
562 AND oppd.plip_id IS NOT NULL
563 AND ( NOT EXISTS (
564 SELECT 'x'
565 FROM ben_pl_f pln
566 WHERE pln.pl_id = oppd.pl_id
567 AND l_effective_date BETWEEN pln.effective_start_date
568 AND pln.effective_end_date)
569 OR EXISTS (
570 SELECT 'x'
571 FROM ben_oipl_f cop, ben_pl_f pln
572 WHERE cop.pl_id = oppd.pl_id
573 AND cop.pl_id = pln.pl_id
574 AND l_effective_date BETWEEN pln.effective_start_date
575 AND pln.effective_end_date
576 AND pln.enrt_pl_opt_flag = 'N'
577 AND l_effective_date BETWEEN cop.effective_start_date
578 AND cop.effective_end_date)
579 );
580 --
581 -- Enrollment Period Dimension
582 --
583 DELETE FROM HRI_CS_TIME_BENRL_PRD_CT enpd
584 WHERE NOT EXISTS (SELECT 'x'
585 FROM ben_enrt_perd enp
586 WHERE enp.enrt_perd_id = enpd.enrt_perd_id);
587 --
588 -- Reporting Group Dimension
589 --
590 DELETE FROM HRI_CS_CO_RPGH_PIRG_CT rgrd
591 WHERE NOT EXISTS (SELECT 'x'
592 FROM ben_popl_rptg_grp_F rgr
593 WHERE rgr.rptg_grp_id = rgrd.RPTGTYP_ID
594 AND rgr.pgm_id = rgrd.pgm_id);
595 --
596 output('Removed Deleted Data: ' || to_char(sysdate,'HH24:MI:SS'));
597 --
598 COMMIT;
599 --
600 EXCEPTION
601 --
602 WHEN OTHERS THEN
603 --
604 RAISE;
605 --
606 --
607 END Incremental_Update;
608 --
609 -- ----------------------------------------------------------------------------
610 -- |-----------------------------< FULL_REFRESH >-----------------------------|
611 -- ----------------------------------------------------------------------------
612 --
613 -- Loops through table and collects into table structure.
614 --
615 PROCEDURE Full_Refresh IS
616 --
617 l_sql_stmt VARCHAR2(2000);
618 l_current_time DATE := SYSDATE;
619 l_effective_date DATE := TRUNC(SYSDATE);
620 l_business_group_id NUMBER(15) := g_business_group_id;
621 l_user_id NUMBER := fnd_global.user_id;
622 l_login_id NUMBER := fnd_global.login_id;
623 --
624 CURSOR c_pgm IS
625 SELECT pgm.pgm_id PGM_ID
626 FROM ben_pgm_f pgm
627 WHERE l_effective_date BETWEEN pgm.effective_start_date
628 AND pgm.effective_end_date
629 AND pgm.pgm_typ_cd in ('CORE', 'FLEX', 'FPC', 'OTHER')
630 AND EXISTS (
631 SELECT 'x'
632 FROM ben_popl_enrt_typ_cycl_f pet, ben_enrt_perd enp
633 WHERE pet.pgm_id = pgm.pgm_id
634 AND pet.enrt_typ_cycl_cd = 'O'
635 AND l_effective_date BETWEEN pet.effective_start_date
636 AND pet.effective_end_date
637 AND pet.popl_enrt_typ_cycl_id = enp.popl_enrt_typ_cycl_id);
638 --
639 CURSOR c_ptip
640 IS
641 SELECT ctp.ptip_id ptip_id, ctp.pl_typ_id pl_typ_id, ctp.pgm_id pgm_id,
642 ctp.ordr_num ptip_ordr_num
643 FROM ben_ptip_f ctp, ben_pl_typ_f ptp
644 WHERE ctp.pl_typ_id = ptp.pl_typ_id
645 AND l_effective_date BETWEEN ctp.effective_start_date
646 AND ctp.effective_end_date
647 AND l_effective_date BETWEEN ptp.effective_start_date
648 AND ptp.effective_end_date
649 AND ptp.opt_typ_cd <> 'SPDGACCT'
650 AND EXISTS (SELECT 'x'
651 FROM hri_cs_co_pgm_ct pgmd
652 WHERE pgmd.pgm_id = ctp.pgm_id);
653 --
654 CURSOR c_plip
655 IS
656 SELECT cpp.plip_id plip_id, cpp.pl_id pl_id, cpp.pgm_id pgm_id,
657 ctpd.ptip_id ptip_id, ctpd.pl_typ_id pl_typ_id, cpp.ordr_num plip_ordr_num
658 FROM ben_plip_f cpp, ben_pl_f pln, hri_cs_co_pgmh_ptip_ct ctpd
659 WHERE cpp.pl_id = pln.pl_id
660 AND pln.pl_typ_id = ctpd.pl_typ_id
661 AND cpp.pgm_id = ctpd.pgm_id
662 AND pln.invk_flx_cr_pl_flag = 'N'
663 AND pln.imptd_incm_calc_cd IS NULL
664 AND l_effective_date BETWEEN cpp.effective_start_date
665 AND cpp.effective_end_date
666 AND l_effective_date BETWEEN pln.effective_start_date
667 AND pln.effective_end_date;
668
669 --
670 CURSOR c_plip_ernt_pl
671 IS
672 SELECT cpp.plip_id plip_id, cpp.pl_id pl_id, cpp.pgm_id pgm_id,
673 ctpd.ptip_id ptip_id, ctpd.pl_typ_id pl_typ_id
674 FROM ben_plip_f cpp, ben_pl_f pln, hri_cs_co_pgmh_ptip_ct ctpd
675 WHERE cpp.pl_id = pln.pl_id
676 AND pln.pl_typ_id = ctpd.pl_typ_id
677 AND cpp.pgm_id = ctpd.pgm_id
678 AND ( pln.enrt_pl_opt_flag = 'Y'
679 OR NOT EXISTS (
680 SELECT 'x'
681 FROM ben_oipl_f cop
682 WHERE cop.pl_id = pln.pl_id
683 AND l_effective_date BETWEEN cop.effective_start_date
684 AND cop.effective_end_date)
685 )
686 AND l_effective_date BETWEEN cpp.effective_start_date
687 AND cpp.effective_end_date
688 AND l_effective_date BETWEEN pln.effective_start_date
689 AND pln.effective_end_date
690 AND pln.invk_flx_cr_pl_flag = 'N'
691 AND pln.imptd_incm_calc_cd IS NULL;
692 --
693 CURSOR c_oiplip
694 IS
695 SELECT opp.oiplip_id oiplip_id, opp.oipl_id oipl_id, cop.opt_id opt_id,
696 opp.plip_id plip_id, cppd.pl_typ_id pl_typ_id,
697 cppd.ptip_id ptip_id, cppd.pl_id pl_id, cppd.pgm_id pgm_id, cop.ordr_num oipl_ordr_num
698 FROM ben_oiplip_f opp, ben_oipl_f cop, hri_cs_co_pgmh_plip_ct cppd
699 WHERE opp.plip_id = cppd.plip_id
700 AND cop.oipl_id = opp.oipl_id
701 AND l_effective_date BETWEEN opp.effective_start_date
702 AND opp.effective_end_date
703 AND l_effective_date BETWEEN cop.effective_start_date
704 AND cop.effective_end_date;
705
706 --
707 CURSOR c_popl_rptg_grp
708 IS
709 SELECT rgr.popl_rptg_grp_id, rgr.rptg_grp_id, rgr.pgm_id
710 FROM ben_popl_rptg_grp_f rgr, hri_cs_co_pgm_ct pgmd
711 WHERE rgr.pgm_id IS NOT NULL
712 AND rgr.pgm_id = pgmd.pgm_id
713 AND l_effective_date BETWEEN rgr.effective_start_date
714 AND rgr.effective_end_date;
715 --
716 CURSOR c_pgm_enrt_perd
717 IS
718 SELECT enp.enrt_perd_id, pgmd.pgm_id, enp.strt_dt, enp.end_dt, enp.ASND_LF_EVT_DT ASND_LF_EVT_DT
719 FROM hri_cs_co_pgm_ct pgmd,
720 ben_popl_enrt_typ_cycl_f pet,
721 ben_enrt_perd enp
722 WHERE pgmd.pgm_id = pet.pgm_id
723 AND pet.popl_enrt_typ_cycl_id = enp.popl_enrt_typ_cycl_id
724 AND l_effective_date BETWEEN pet.effective_start_date
725 AND pet.effective_end_date;
726 --
727 CURSOR c_pl_enrt_perd
728 IS
729 SELECT enp.enrt_perd_id, enpd.pgm_id, enp.strt_dt, enp.end_dt,
730 enp.asnd_lf_evt_dt ASND_LF_EVT_DT
731 FROM hri_cs_time_benrl_prd_ct enpd,
732 ben_plip_f cpp,
733 ben_popl_enrt_typ_cycl_f pet,
734 ben_enrt_perd enp
735 WHERE enpd.pgm_id = cpp.pgm_id
736 AND pet.pl_id = cpp.pl_id
737 AND pet.popl_enrt_typ_cycl_id = enp.popl_enrt_typ_cycl_id
738 AND enpd.ASND_LF_EVT_DT = enp.asnd_lf_evt_dt
739 AND l_effective_date BETWEEN cpp.effective_start_date
740 AND cpp.effective_end_date
741 AND l_effective_date BETWEEN pet.effective_start_date
742 AND pet.effective_end_date;
743 --
744 BEGIN
745 --
746 output('Start Of Full Refresh');
747 --
748 -- Disable the WHO Triggers
749 --
750 run_sql_stmt_noerr('ALTER TRIGGER HRI_CS_CO_PGM_CT_WHO DISABLE');
751 run_sql_stmt_noerr('ALTER TRIGGER HRI_CS_CO_PGMH_PTIP_CT_WHO DISABLE');
752 run_sql_stmt_noerr('ALTER TRIGGER HRI_CS_CO_PGMH_PLIP_CT_WHO DISABLE');
753 run_sql_stmt_noerr('ALTER TRIGGER HRI_CS_CO_RPGH_PIRG_CT_WHO DISABLE');
754 run_sql_stmt_noerr('ALTER TRIGGER HRI_CS_COMPOBJ_CT_WHO DISABLE');
755 run_sql_stmt_noerr('ALTER TRIGGER HRI_CS_TIME_BENRL_PRD_CT_WHO DISABLE');
756 --
757 -- Truncate the target table prior to full refresh.
758 --
759 EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || g_schema || '.HRI_CS_CO_PGM_CT';
760 EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || g_schema || '.HRI_CS_CO_PGMH_PTIP_CT';
761 EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || g_schema || '.HRI_CS_CO_PGMH_PLIP_CT';
762 EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || g_schema || '.HRI_CS_COMPOBJ_CT';
763 EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || g_schema || '.HRI_CS_CO_RPGH_PIRG_CT';
764 EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || g_schema || '.HRI_CS_TIME_BENRL_PRD_CT';
765 --
766 -- Write timing information to log
767 --
768 output('Truncated the tables: ' || to_char(sysdate,'HH24:MI:SS'));
769 --
770 --
771 -- (1) Populate dimension table HRI_CS_CO_PGM_CT
772 --
773 FOR l_pgm_row in c_pgm LOOP
774 --
775 INSERT INTO HRI_CS_CO_PGM_CT
776 ( PGM_id
777 , LAST_UPDATE_DATE
778 , LAST_UPDATED_BY
779 , LAST_UPDATE_LOGIN
780 , CREATED_BY
781 , CREATION_DATE
782 )
783 VALUES
784 ( l_pgm_row.pgm_id
785 , l_current_time
786 , l_user_id
787 , l_login_id
788 , l_user_id
789 , l_current_time
790 );
791 --
792 END LOOP;
793 --
794 output('Populated Program Dimension: ' || to_char(sysdate,'HH24:MI:SS'));
795 --
796 -- (2) Populate dimension table HRI_CS_CO_PGMH_PTIP_CT
797 --
798 FOR l_ptip_row in c_ptip LOOP
799 --
800 INSERT INTO HRI_CS_CO_PGMH_PTIP_CT
801 ( ptip_id
802 , pl_typ_id
803 , PGM_ID
804 , ptip_ordr_num
805 , LAST_UPDATE_DATE
806 , LAST_UPDATED_BY
807 , LAST_UPDATE_LOGIN
808 , CREATED_BY
809 , CREATION_DATE
810 )
811 VALUES
812 ( l_ptip_row.ptip_id
813 , l_ptip_row.pl_typ_id
814 , l_ptip_row.PGM_ID
815 , l_ptip_row.ptip_ordr_num
816 , l_current_time
817 , l_user_id
818 , l_login_id
819 , l_user_id
820 , l_current_time
821 );
822 --
823 END LOOP;
824 --
825 output('Populated Plan Type In Program Dimension: ' || to_char(sysdate,'HH24:MI:SS'));
826 --
827 -- (3) Populate dimension table HRI_CS_CO_PGMH_PLIP_CT
828 --
829 FOR l_plip_row in c_plip LOOP
830 --
831 INSERT INTO HRI_CS_CO_PGMH_PLIP_CT
832 ( plip_id
833 , pl_id
834 , PGM_ID
835 , ptip_id
836 , pl_typ_id
837 , plip_ordr_num
838 , LAST_UPDATE_DATE
839 , LAST_UPDATED_BY
840 , LAST_UPDATE_LOGIN
841 , CREATED_BY
842 , CREATION_DATE
843 )
844 VALUES
845 ( l_plip_row.plip_id
846 , l_plip_row.pl_id
847 , l_plip_row.PGM_ID
848 , l_plip_row.ptip_id
849 , l_plip_row.pl_typ_id
850 , l_plip_row.plip_ordr_num
851 , l_current_time
852 , l_user_id
853 , l_login_id
854 , l_user_id
855 , l_current_time
856 );
857 --
858 END LOOP;
859 --
860 output('Populated Plan In Program Dimension: ' || to_char(sysdate,'HH24:MI:SS'));
861 --
862 -- (4) Populate dimension table HRI_CS_COMPOBJ_CT
863 --
864 FOR l_oiplip_row in c_oiplip LOOP
865 --
866 INSERT INTO HRI_CS_COMPOBJ_CT
867 ( compobj_sk_pk
868 , oiplip_id
869 , oipl_id
870 , opt_id
871 , pl_id
872 , plip_id
873 , PGM_ID
874 , ptip_id
875 , pl_typ_id
876 , oipl_ordr_num
877 , LAST_UPDATE_DATE
878 , LAST_UPDATED_BY
879 , LAST_UPDATE_LOGIN
880 , CREATED_BY
881 , CREATION_DATE
882 )
883 VALUES
884 ( HRI_CS_COMPOBJ_CT_s.nextval
885 , l_oiplip_row.oiplip_id
886 , l_oiplip_row.oipl_id
887 , l_oiplip_row.opt_id
888 , l_oiplip_row.pl_id
889 , l_oiplip_row.plip_id
890 , l_oiplip_row.PGM_ID
891 , l_oiplip_row.ptip_id
892 , l_oiplip_row.pl_typ_id
893 , l_oiplip_row.oipl_ordr_num
894 , l_current_time
895 , l_user_id
896 , l_login_id
897 , l_user_id
898 , l_current_time
899 );
900 --
901 END LOOP;
902 --
903 -- (5) Populate dimension table HRI_CS_COMPOBJ_CT for Enrollments at Plan Level
904 --
905 FOR l_plip_ernt_pl_row in c_plip_ernt_pl LOOP
906 --
907 INSERT INTO HRI_CS_COMPOBJ_CT
908 ( compobj_sk_pk
909 , oiplip_id
910 , oipl_id
911 , opt_id
912 , pl_id
913 , plip_id
914 , PGM_ID
915 , ptip_id
916 , pl_typ_id
917 , LAST_UPDATE_DATE
918 , LAST_UPDATED_BY
919 , LAST_UPDATE_LOGIN
920 , CREATED_BY
921 , CREATION_DATE
922 )
923 VALUES
924 ( HRI_CS_COMPOBJ_CT_s.nextval
925 , -1
926 , -1
927 , NULL
928 , l_plip_ernt_pl_row.pl_id
929 , l_plip_ernt_pl_row.plip_id
930 , l_plip_ernt_pl_row.PGM_ID
931 , l_plip_ernt_pl_row.ptip_id
932 , l_plip_ernt_pl_row.pl_typ_id
933 , l_current_time
934 , l_user_id
935 , l_login_id
936 , l_user_id
937 , l_current_time
938 );
939 --
940 END LOOP;
941 --
942 output('Populated Compensation Object Dimension: ' || to_char(sysdate,'HH24:MI:SS'));
943 --
944 -- (6) Populate dimension table HRI_CS_CO_RPGH_PIRG_CT for Reporting Group Information
945 --
946 FOR l_popl_rptg_grp_row in c_popl_rptg_grp LOOP
947 --
948 INSERT INTO HRI_CS_CO_RPGH_PIRG_CT
949 ( PIRG_SK_PK
950 , RPTGTYP_ID
951 , pgm_id
952 , LAST_UPDATE_DATE
953 , LAST_UPDATED_BY
954 , LAST_UPDATE_LOGIN
955 , CREATED_BY
956 , CREATION_DATE
957 )
958 VALUES
959 ( HRI_CS_CO_RPGH_PIRG_CT_S.nextval
960 , l_popl_rptg_grp_row.rptg_grp_id
961 , l_popl_rptg_grp_row.pgm_id
962 , l_current_time
963 , l_user_id
964 , l_login_id
965 , l_user_id
966 , l_current_time
967 );
968 --
969 END LOOP;
970 --
971 output('Populated Reporting Group Dimension: ' || to_char(sysdate,'HH24:MI:SS'));
972 --
973 -- (7) Populate dimension table HRI_CS_TIME_BENRL_PRD_CT for Program Enrollment Periods
974 --
975 FOR l_enrt_perd_row in c_pgm_enrt_perd LOOP
976 --
977 INSERT INTO HRI_CS_TIME_BENRL_PRD_CT
978 ( enrt_perd_id
979 , pgm_id
980 , enrt_strt_dt
981 , enrt_thru_dt
982 , ASND_LF_EVT_DT
983 , LAST_UPDATE_DATE
984 , LAST_UPDATED_BY
985 , LAST_UPDATE_LOGIN
986 , CREATED_BY
987 , CREATION_DATE
988 )
989 VALUES
990 ( l_enrt_perd_row.enrt_perd_id
991 , l_enrt_perd_row.pgm_id
992 , l_enrt_perd_row.strt_dt
993 , l_enrt_perd_row.end_dt
994 , l_enrt_perd_row.ASND_LF_EVT_DT
995 , l_current_time
996 , l_user_id
997 , l_login_id
998 , l_user_id
999 , l_current_time
1000 );
1001 --
1002 END LOOP;
1003 --
1004 -- (8) Populate dimension table HRI_CS_TIME_BENRL_PRD_CT for Plan Enrollment Periods
1005 --
1006 FOR l_pl_enrt_perd_row in c_pl_enrt_perd LOOP
1007 --
1008 INSERT INTO HRI_CS_TIME_BENRL_PRD_CT
1009 ( enrt_perd_id
1010 , pgm_id
1011 , enrt_strt_dt
1012 , enrt_thru_dt
1013 , ASND_LF_EVT_DT
1014 , LAST_UPDATE_DATE
1015 , LAST_UPDATED_BY
1016 , LAST_UPDATE_LOGIN
1017 , CREATED_BY
1018 , CREATION_DATE
1019 )
1020 VALUES
1021 ( l_pl_enrt_perd_row.enrt_perd_id
1022 , l_pl_enrt_perd_row.pgm_id
1023 , l_pl_enrt_perd_row.strt_dt
1024 , l_pl_enrt_perd_row.end_dt
1025 , l_pl_enrt_perd_row.ASND_LF_EVT_DT
1026 , l_current_time
1027 , l_user_id
1028 , l_login_id
1029 , l_user_id
1030 , l_current_time
1031 );
1032 --
1033 END LOOP;
1034 --
1035 output('Populated Enrollment Period Dimension: ' || to_char(sysdate,'HH24:MI:SS'));
1036 --
1037 COMMIT;
1038 --
1039 -- Enable the WHO triggers
1040 --
1041 run_sql_stmt_noerr('ALTER TRIGGER HRI_CS_CO_PGM_CT_WHO ENABLE');
1042 run_sql_stmt_noerr('ALTER TRIGGER HRI_CS_CO_PGMH_PTIP_CT_WHO ENABLE');
1043 run_sql_stmt_noerr('ALTER TRIGGER HRI_CS_CO_PGMH_PLIP_CT_WHO ENABLE');
1044 run_sql_stmt_noerr('ALTER TRIGGER HRI_CS_CO_RPGH_PIRG_CT_WHO ENABLE');
1045 run_sql_stmt_noerr('ALTER TRIGGER HRI_CS_COMPOBJ_CT_WHO ENABLE');
1046 run_sql_stmt_noerr('ALTER TRIGGER HRI_CS_TIME_BENRL_PRD_CT_WHO ENABLE');
1047 --
1048 EXCEPTION
1049 --
1050 WHEN OTHERS THEN
1051 --
1052 RAISE;
1053 --
1054 --
1055 END Full_Refresh;
1056 --
1057 -- ----------------------------------------------------------------------------
1058 -- |--------------------------------< COLLECT >-------------------------------|
1059 -- ----------------------------------------------------------------------------
1060 --
1061 -- Checks what mode you are running in, and if g_full_refresh =
1062 -- g_is_full_refresh calls
1063 -- Full_Refresh procedure, otherwise Incremental_Update is called.
1064 --
1065 PROCEDURE Collect IS
1066 --
1067 BEGIN
1068 --
1069 -- If in full refresh mode change the dates so that the collection history
1070 -- is correctly maintained.
1071 --
1072 IF g_full_refresh = g_is_full_refresh THEN
1073 --
1074 Full_Refresh;
1075 --
1076 ELSE
1077 --
1078 --
1079 -- If the passed in date range is NULL default it.
1080 --
1081 Incremental_Update;
1082 --
1083 END IF;
1084 --
1085 EXCEPTION
1086 --
1087 WHEN OTHERS THEN
1088 --
1089 OUTPUT('Exception: ' || SQLERRM);
1090 RAISE;
1091 --
1092 --
1093 END Collect;
1094 --
1095 -- ----------------------------------------------------------------------------
1096 -- |----------------------------------< LOAD >--------------------------------|
1097 -- ----------------------------------------------------------------------------
1098 --
1099 -- Main entry point to load the table.
1100 --
1101 PROCEDURE Load(p_full_refresh IN VARCHAR2) IS
1102 --
1103 -- Variables required for table truncation.
1104 --
1105 l_dummy1 VARCHAR2(2000);
1106 l_dummy2 VARCHAR2(2000);
1107 --
1108 BEGIN
1109 --
1110 output('Start: ' || to_char(sysdate,'HH24:MI:SS'));
1111 --
1112 -- Set globals
1113 --
1114 -- g_business_group_id := p_business_group_id;
1115 --
1116 IF p_full_refresh IS NULL
1117 THEN
1118 --
1119 g_full_refresh := g_not_full_refresh;
1120 --
1121 ELSE
1122 --
1123 g_full_refresh := p_full_refresh;
1124 --
1125 END IF;
1126 --
1127 -- Find the schema we are running in.
1128 --
1129 IF NOT fnd_installation.get_app_info('HRI',l_dummy1, l_dummy2, g_schema)
1130 THEN
1131 --
1132 -- Could not find the schema raising exception.
1133 --
1134 output('Could not find schema to run in.');
1135 --
1136 -- log('Could not find schema.');
1137 RAISE NO_DATA_FOUND;
1138 --
1139 END IF;
1140 --
1141 -- Update information about collection
1142 --
1143 -- log('Record process start.');
1144 hri_bpl_conc_log.record_process_start(g_cncrnt_prgrm_shrtnm);
1145 --
1146 -- Time at start
1147 --
1148 -- log('collect.');
1149 --
1150 -- Get HRI schema name - get_app_info populates l_schema
1151 --
1152 -- Insert new records
1153 --
1154 collect;
1155 -- log('collectED.');
1156 --
1157 -- Write timing information to log
1158 --
1159 output('Finished changes to the table: ' || to_char(sysdate,'HH24:MI:SS'));
1160 --
1161 -- Gather index stats
1162 --
1163 fnd_stats.gather_table_stats(g_schema, 'HRI_CS_CO_PGM_CT');
1164 fnd_stats.gather_table_stats(g_schema, 'HRI_CS_CO_PGMH_PTIP_CT');
1165 fnd_stats.gather_table_stats(g_schema, 'HRI_CS_CO_PGMH_PLIP_CT');
1166 fnd_stats.gather_table_stats(g_schema, 'HRI_CS_CO_RPGH_PIRG_CT');
1167 fnd_stats.gather_table_stats(g_schema, 'HRI_CS_TIME_BENRL_PRD_CT');
1168 fnd_stats.gather_table_stats(g_schema, 'HRI_CS_CO_PGM_CT');
1169 --
1170 -- Write timing information to log
1171 --
1172 -- output('Gathered stats: ' || to_char(sysdate,'HH24:MI:SS'));
1173 --
1174 -- Call to HRI_BPL_CONC_LOG.LOG_PROCESS_END inserts record into BIS_REFRESH_LOG
1175 -- corresponding to concurrent request with start and end times
1176 --
1177 hri_bpl_conc_log.log_process_end(
1178 p_status => TRUE,
1179 p_period_from => TRUNC(to_date('01-01-0001', 'DD-MM-YYYY')),
1180 p_period_to => TRUNC(sysdate),
1181 p_attribute1 => p_full_refresh);
1182 --
1183 EXCEPTION
1184 --
1185 WHEN OTHERS
1186 THEN
1187 --
1188 ROLLBACK;
1189 RAISE;
1190 --
1191 --
1192 END Load;
1193 --
1194 --
1195 -- ----------------------------------------------------------------------------
1196 -- |---------------------------------< LOAD >---------------------------------|
1197 -- ----------------------------------------------------------------------------
1198 --
1199 -- Entry point to be called from the concurrent manager
1200 --
1201 PROCEDURE Load(errbuf OUT NOCOPY VARCHAR2,
1202 retcode OUT NOCOPY VARCHAR2,
1203 p_full_refresh IN VARCHAR2)
1204 IS
1205 --
1206 BEGIN
1207 --
1208 -- Enable output to concurrent request log
1209 --
1210 g_conc_request_flag := TRUE;
1211 --
1212 load( p_full_refresh => p_full_refresh);
1213 --
1214 EXCEPTION
1215 --
1216 WHEN OTHERS THEN
1217 --
1218 errbuf := SQLERRM;
1219 retcode := SQLCODE;
1220 --
1221 --
1222 END load;
1223 --
1224 end hri_opl_ben_dim_refresh ;