[Home] [Help]
PACKAGE BODY: APPS.HRI_OLTP_PMV_OPEN_ENRT_STAT
Source
1 PACKAGE BODY HRI_OLTP_PMV_OPEN_ENRT_STAT AS
2 /* $Header: hrirpoes.pkb 120.0 2005/09/21 01:28:52 anmajumd noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- |----------------------< SET_BIND_PARAMETERS >-----------------------------|
6 -- ----------------------------------------------------------------------------
7 --
8 -- This procedure will populate X_CUSTOM_OUTPUT parameter with all the BIND
9 -- variables required for the execution of queries returned by GET_???_SQL
10 -- procedures.
11 --
12 procedure SET_BIND_PARAMETERS(
13 p_page_parameter_tbl IN bis_pmv_page_parameter_tbl,
14 x_custom_output IN OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL,
15 p_selection OUT NOCOPY NUMBER)
16 IS
17 --
18 l_custom_rec BIS_QUERY_ATTRIBUTES;
19 L_VALUE varchar2(80);
20 --
21 l_pgm_rptgtyp_id number;
22 l_enrt_perd_found boolean := false;
23 l_effective_date date;
24 l_asnd_lf_evt_dt date;
25 --
26 cursor c_pgm_enrt_perd ( cv_pgm_id NUMBER,
27 cv_effective_date DATE )
28 IS
29 SELECT asnd_lf_evt_dt
30 FROM (SELECT MAX (asnd_lf_evt_dt) asnd_lf_evt_dt
31 FROM hri_cs_time_benrl_prd_ct enp
32 WHERE pgm_id = cv_pgm_id
33 AND enrt_strt_dt <= cv_effective_date
34 );
35 --
36 cursor c_rptgrp_enrt_perd ( cv_rptgrp_id NUMBER,
37 cv_effective_date DATE )
38 IS
39 SELECT asnd_lf_evt_dt
40 FROM (SELECT MAX (asnd_lf_evt_dt) asnd_lf_evt_dt
41 FROM hri_cs_time_benrl_prd_ct enp
42 WHERE enrt_strt_dt <= cv_effective_date
43 AND pgm_id IN (SELECT pgm_id
44 FROM hri_cs_co_rpgh_pirg_ct
45 WHERE rptgtyp_id = cv_rptgrp_id
46 )
47 );
48 --
49 BEGIN
50 --
51 l_custom_rec := BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
52 --
53 IF (p_page_parameter_tbl.COUNT > 0)
54 THEN
55 --
56 FOR i IN p_page_parameter_tbl.FIRST .. p_page_parameter_tbl.LAST
57 LOOP
58 --
59 p_selection := 1; /* Default the dimension selection to PROGRAM */
60 --
61 /*
62 IF p_page_parameter_tbl (i).parameter_name = 'SELECT_A+SELECT_B'
63 THEN
64 --
65 -- Dimension Selection
66 -- 1 = Program
67 -- 2 = Reporting Group
68 --
69 l_value := ( p_page_parameter_tbl (i).parameter_id );
70 l_value := rtrim(ltrim(l_value, ''''), '''');
71 --
72 p_selection := l_value;
73 --
74 */
75 IF p_page_parameter_tbl (i).parameter_name = 'PGM_A+PGM_B'
76 THEN
77 --
78 l_value := ( p_page_parameter_tbl (i).parameter_id );
79 l_value := rtrim(ltrim(l_value, ''''), '''');
80 --
81 -- For Dimension : Program => PGM_A+PGM_B = PGM_ID
82 --
83 --
84 -- For Dimension : Reporting Group => PGM_A+PGM_B = RPTGTYP_ID
85 --
86 x_custom_output.EXTEND;
87 x_custom_output(x_custom_output.LAST) := bis_query_attributes
88 ( ':PGM_RPTGTYP_ID'
89 , l_value
90 , bis_pmv_parameters_pub.bind_type
91 , bis_pmv_parameters_pub.numeric_bind);
92 --
93 l_pgm_rptgtyp_id := l_value;
94 --
95 ELSIF p_page_parameter_tbl (i).parameter_name = 'PL_TYP_A+PL_TYP_B'
96 THEN
97 --
98 l_value := ( p_page_parameter_tbl (i).parameter_id );
99 l_value := rtrim(ltrim(l_value, ''''), '''');
100 --
101 -- For Dimension : Reporting Group => PL_TYP_A+PL_TYP_B = PL_TYP_ID
102 --
103 --
104 -- For Dimension : Program => PL_TYP_A+PL_TYP_B = PTIP_ID
105 --
106 x_custom_output.EXTEND;
107 x_custom_output(x_custom_output.LAST) := bis_query_attributes
108 ( ':PTIP_PLTYP_ID'
109 , l_value
110 , bis_pmv_parameters_pub.bind_type
111 , bis_pmv_parameters_pub.numeric_bind);
112 --
113 ELSIF p_page_parameter_tbl (i).parameter_name = 'PLN_A+PLN_B'
114 THEN
115 --
116 l_value := ( p_page_parameter_tbl (i).parameter_id );
117 l_value := rtrim(ltrim(l_value, ''''), '''');
118 --
119 -- For Dimension : Reporting Group => PLN_A+PLN_B = PL_ID
120 --
121 --
122 -- For Dimension : Program => PLN_A+PLN_B = PLIP_ID
123 --
124 x_custom_output.EXTEND;
125 x_custom_output(x_custom_output.LAST) := bis_query_attributes
126 ( ':PLIP_PL_ID'
127 , l_value
128 , bis_pmv_parameters_pub.bind_type
129 , bis_pmv_parameters_pub.numeric_bind);
130 --
131 /*
132 --
133 -- Uncomment this code when Enrollment Period Dimension is Re-instated
134 --
135 ELSIF p_page_parameter_tbl (i).parameter_name = 'ENRT_PERD_A+ENRT_PERD_B'
136 THEN
137 --
138 l_value := ( p_page_parameter_tbl (i).parameter_id );
139 l_value := rtrim(ltrim(l_value, ''''), '''');
140 --
141 -- Assigned Life Event Date
142 --
143 x_custom_output.EXTEND;
144 x_custom_output(x_custom_output.LAST) := bis_query_attributes
145 ( ':ASND_LF_EVT_DT'
146 , to_char(l_asnd_lf_evt_dt, 'DD/MM/YYYY')
147 , bis_pmv_parameters_pub.bind_type
148 , bis_pmv_parameters_pub.DATE_BIND);
149 --
150 if nvl(l_value, '') <> ''
151 then
152 --
153 l_enrt_perd_found := TRUE;
154 --
155 end if;
156 --
157 */
158 ELSIF p_page_parameter_tbl (i).parameter_name = 'AS_OF_DATE'
159 THEN
160 --
161 l_value := ( p_page_parameter_tbl (i).parameter_id );
162 l_value := rtrim(ltrim(l_value, ''''), '''');
163 --
164 -- Effective Date
165 --
166 x_custom_output.EXTEND;
167 x_custom_output(x_custom_output.LAST) := bis_query_attributes
168 ( ':BEN_AS_OF_DATE'
169 , l_value
170 , bis_pmv_parameters_pub.bind_type
171 , bis_pmv_parameters_pub.DATE_BIND);
172 --
173 l_effective_date := to_date(l_value, 'DD/MM/YYYY');
174 --
175 ELSIF p_page_parameter_tbl (i).parameter_name = 'ACTN_TYP_A+ACTN_TYP_B'
176 THEN
177 --
178 l_value := ( p_page_parameter_tbl (i).parameter_id );
179 l_value := rtrim(ltrim(l_value, ''''), '''');
180 --
181 -- Action Type Code
182 --
183 x_custom_output.EXTEND;
184 x_custom_output(x_custom_output.LAST) := bis_query_attributes
185 ( ':ACTN_TYP_CD'
186 , l_value
187 , bis_pmv_parameters_pub.bind_type
188 , bis_pmv_parameters_pub.VARCHAR2_BIND);
189 --
190 END IF;
191 --
192 END LOOP;
193 --
194 -- Display data for the latest enrollment period relative to the effective date
195 -- In future when Enrollment Period dimension is re-instated, query data based on
196 -- enrollment period selected
197 --
198 if l_effective_date is not null AND
199 p_selection is not null
200 then
201 --
202 if p_selection = 1
203 then
204 --
205 -- Program
206 --
207 open c_pgm_enrt_perd ( cv_pgm_id => l_pgm_rptgtyp_id ,
208 cv_effective_date => l_effective_date );
209 --
210 fetch c_pgm_enrt_perd into l_asnd_lf_evt_dt;
211 --
212 close c_pgm_enrt_perd ;
213 --
214 /*
215 elsif p_selection = 2
216 then
217 --
218 -- Reporting Group
219 --
220 open c_rptgrp_enrt_perd ( cv_rptgrp_id => l_pgm_rptgtyp_id,
221 cv_effective_date => l_effective_date );
222 --
223 fetch c_rptgrp_enrt_perd into l_asnd_lf_evt_dt;
224 --
225 close c_rptgrp_enrt_perd ;
226 --
227 */
228 end if;
229 --
230 end if;
231 --
232 x_custom_output.EXTEND;
233 x_custom_output(x_custom_output.LAST) := bis_query_attributes
234 ( ':ASND_LF_EVT_DT'
235 , to_char(l_asnd_lf_evt_dt, 'DD/MM/YYYY')
236 , bis_pmv_parameters_pub.bind_type
237 , bis_pmv_parameters_pub.DATE_BIND);
238 --
239 END IF;
240 --
241 --
242 END SET_BIND_PARAMETERS;
243 --
244 -- ----------------------------------------------------------------------------
245 -- |-------------------< PRINT_TABLE_PARAMETERS >-----------------------------|
246 -- ----------------------------------------------------------------------------
247 --
248 -- The procedure will print all parameters passed by P_PAGE_PARAMETER_TBL to user pipe DBI
249 -- This procedure is added for debugging purpose. Whenever it is required to check the
250 -- parameters passed by parameter portlet, call this procedure from CHECK_???_SQL procedures
251 -- Remember to uncomment call to this procedure, since it enables a trace
252 --
253 PROCEDURE PRINT_TABLE_PARAMETERS (
254 p_page_parameter_tbl IN bis_pmv_page_parameter_tbl )
255 IS
256 --
257 --
258 BEGIN
259 --
260 hr_utility.trace_on(null, 'DBIBEN');
261 hr_utility.set_location('--------------------------------------', 9999);
262 --
263 IF (p_page_parameter_tbl.COUNT > 0)
264 THEN
265 --
266 FOR i IN p_page_parameter_tbl.FIRST .. p_page_parameter_tbl.LAST
267 LOOP
268 --
269 hr_utility.set_location('----', 9999);
270 hr_utility.set_location('ACE parameter_name = ' || p_page_parameter_tbl (i).parameter_name, 9999);
271 hr_utility.set_location('ACE parameter_value = ' || p_page_parameter_tbl (i).parameter_value, 9999);
272 hr_utility.set_location('ACE parameter_id = ' || p_page_parameter_tbl (i).parameter_id, 9999);
273 --
274 END LOOP;
275 --
276 END IF;
277 --
278 hr_utility.trace_off;
279 --
280 END PRINT_TABLE_PARAMETERS;
281 --
282 -- ----------------------------------------------------------------------------
283 -- |--------------------< GET_PARAMETER_VALUE >-------------------------------|
284 -- ----------------------------------------------------------------------------
285 --
286 -- The function will return the paramter value for the passed parameter name
287 --
288 FUNCTION GET_PARAMETER_VALUE (
289 p_page_parameter_tbl IN bis_pmv_page_parameter_tbl,
290 p_parameter_name IN VARCHAR2
291 )
292 RETURN VARCHAR2
293 IS
294 --
295 l_value varchar2(3000);
296 --
297 BEGIN
298 --
299 IF (p_page_parameter_tbl.COUNT > 0)
300 THEN
301 --
302 FOR i IN p_page_parameter_tbl.FIRST .. p_page_parameter_tbl.LAST
303 LOOP
304 --
305 IF p_page_parameter_tbl (i).parameter_name = p_parameter_name
306 THEN
307 --
308 l_value := p_page_parameter_tbl (i).parameter_value;
309 --
310 END IF;
311 --
312 END LOOP;
313 --
314 END IF;
315 --
316 RETURN l_value;
317 --
318 END GET_PARAMETER_VALUE;
319 --
320 -- ----------------------------------------------------------------------------
321 -- |-----------------------< GET_PARAMETER_ID >-------------------------------|
322 -- ----------------------------------------------------------------------------
323 --
324 -- The function will return the paramter ID for the passed parameter name
325 --
326 FUNCTION GET_PARAMETER_ID (
327 p_page_parameter_tbl IN bis_pmv_page_parameter_tbl,
328 p_parameter_name IN VARCHAR2
329 )
330 RETURN varchar2
331 IS
332 --
333 l_id varchar2(3000);
334 --
335 BEGIN
336 --
337 IF (p_page_parameter_tbl.COUNT > 0)
338 THEN
339 --
340 FOR i IN p_page_parameter_tbl.FIRST .. p_page_parameter_tbl.LAST
341 LOOP
342 --
343 IF p_page_parameter_tbl (i).parameter_name = p_parameter_name
344 THEN
345 --
346 l_id := p_page_parameter_tbl (i).parameter_id;
347 l_id := rtrim(ltrim(l_id, ''''), '''');
348 --
349 END IF;
350 --
351 END LOOP;
352 --
353 END IF;
354 --
355 RETURN l_id;
356 --
357 END GET_PARAMETER_ID;
358 --
359 -- ----------------------------------------------------------------------------
360 -- |-----------------------< GET_ELIGENRL_PLIP_SQL >---------------------------|
361 -- ----------------------------------------------------------------------------
362 --
363 -- Participation By Plan
364 -- AK_REGION = HRI_P_ELIGENRL_PRTT_PLIP
365 --
366 PROCEDURE GET_ELIGENRL_PLIP_SQL (
367 p_page_parameter_tbl IN bis_pmv_page_parameter_tbl,
368 x_custom_sql OUT NOCOPY VARCHAR2,
369 x_custom_output OUT NOCOPY bis_query_attributes_tbl
370 )
371 IS
372 --
373 l_selection number;
374 l_custom_rec BIS_QUERY_ATTRIBUTES;
375 --
376 BEGIN
377 --
378 x_custom_sql := ' SELECT NULL HRI_P_CHAR1_GA /* Participation By Plan */,
379 NULL HRI_P_MEASURE1,
380 NULL HRI_P_MEASURE2,
381 NULL HRI_P_MEASURE3_MP,
382 NULL HRI_P_MEASURE4,
383 NULL HRI_P_DRILL_URL1
384 FROM DUAL';
385 --
386 l_custom_rec := BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
387 x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
388 --
389 set_bind_parameters( p_page_parameter_tbl => p_page_parameter_tbl,
390 x_custom_output => x_custom_output,
391 p_selection => l_selection) ;
392 --
393 IF l_selection = 1
394 THEN
395 --
396 -- Use Views corresponding to Program Dimension
397 --
398 x_custom_sql :=
399 ' SELECT cppv.value HRI_P_CHAR1_GA /* Participation By Plan (Program) */,
400 cppmv.elig_count HRI_P_MEASURE1,
401 cppmv.enrt_count HRI_P_MEASURE2,
402 cppmv.enrt_per HRI_P_MEASURE3_MP,
403 cppmv.plip_id HRI_P_MEASURE4,
404 (SELECT ''pFunctionName=HRI_P_ELIGENRL_PRTT_OIPL'' ||
405 ''&'' || ''PLN_A+PLN_B=HRI_P_MEASURE4'' ||
406 ''&'' || ''pParamIds=Y''
407 FROM dual
408 WHERE EXISTS
409 ( SELECT 1
410 FROM BEN_OIPL_F
411 WHERE pl_id = cppv.pl_id
412 )
413 ) HRI_P_DRILL_URL1
414 FROM HRI_MDP_BEN_ELIGENRL_CPP_MV cppmv,
415 HRI_CL_CO_PGMH_PLIP_V cppv
416 WHERE cppmv.asnd_lf_evt_dt = :ASND_LF_EVT_DT
417 AND cppv.id = cppmv.plip_id
418 AND cppmv.asnd_lf_evt_dt BETWEEN cppv.start_date AND cppv.end_date
419 AND cppv.pgm_id = :PGM_RPTGTYP_ID
420 AND cppv.ptip_id = :PTIP_PLTYP_ID
421 AND :BEN_AS_OF_DATE BETWEEN cppmv.effective_start_Date
422 AND cppmv.effective_end_Date
423 &ORDER_BY_CLAUSE';
424 --
425 /*
426 ELSIF l_selection = 2
427 THEN
428 --
429 -- Use Views corresponding to Reporting Group Dimension
430 --
431 x_custom_sql :=
432 ' SELECT rplnv.value HRI_P_CHAR1_GA, -- Participation By Plan (Reporting Group)
433 cppmv.elig_count HRI_P_MEASURE1,
434 cppmv.enrt_count HRI_P_MEASURE2,
435 cppmv.enrt_per HRI_P_MEASURE3_MP,
436 cppmv.pl_id HRI_P_MEASURE4,
437 (SELECT ''pFunctionName=HRI_P_ELIGENRL_PRTT_OIPL'' ||
438 ''&'' || ''PLN_A+PLN_B=HRI_P_MEASURE4'' ||
439 ''&'' || ''pParamIds=Y''
440 FROM dual
441 WHERE EXISTS
442 ( SELECT 1
443 FROM BEN_OIPL_F
444 WHERE pl_id = cppmv.pl_id
445 )
446 ) HRI_P_DRILL_URL1
447 FROM HRI_MDP_BEN_ELIGENRL_RPLN_MV cppmv,
448 HRI_CL_CO_RPTG_PL_V rplnv
449 WHERE cppmv.asnd_lf_evt_dt = :ASND_LF_EVT_DT
450 AND rplnv.id = cppmv.pl_id
451 AND rplnv.rptgtyp_id = cppmv.rptgtyp_id
452 AND cppmv.asnd_lf_evt_dt BETWEEN rplnv.start_date AND rplnv.end_date
453 AND cppmv.rptgtyp_id = :PGM_RPTGTYP_ID
454 AND rplnv.pl_typ_id = :PTIP_PLTYP_ID
455 AND :BEN_AS_OF_DATE BETWEEN cppmv.effective_start_Date
456 AND cppmv.effective_end_Date
457 &ORDER_BY_CLAUSE';
458 */
459 --
460 END IF;
461 --
465 -- |-----------------------< GET_ELIGENRL_OIPL_SQL >---------------------------|
462 END GET_ELIGENRL_PLIP_SQL;
463 --
464 -- ----------------------------------------------------------------------------
466 -- ----------------------------------------------------------------------------
467 --
468 -- Participation By Option In Plan
469 -- AK_REGION = HRI_P_ELIGENRL_PRTT_OIPL
470 --
471 PROCEDURE GET_ELIGENRL_OIPL_SQL (
472 p_page_parameter_tbl IN bis_pmv_page_parameter_tbl,
473 x_custom_sql OUT NOCOPY VARCHAR2,
474 x_custom_output OUT NOCOPY bis_query_attributes_tbl
475 )
476 IS
477 --
478 l_selection number;
479 l_custom_rec BIS_QUERY_ATTRIBUTES;
480 --
481 BEGIN
482 --
483 x_custom_sql := ' SELECT NULL HRI_P_CHAR1_GA /* Participation By Option In Plan */,
484 NULL HRI_P_MEASURE1,
485 NULL HRI_P_MEASURE2,
486 NULL HRI_P_MEASURE3_MP
487 FROM DUAL';
488 --
489 l_custom_rec := BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
490 x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
491 --
492 set_bind_parameters( p_page_parameter_tbl => p_page_parameter_tbl,
493 x_custom_output => x_custom_output,
494 p_selection => l_selection) ;
495 --
496 IF l_selection = 1
497 THEN
498 --
499 -- Use Views corresponding to Program Dimension
500 --
501 x_custom_sql :=
502 ' SELECT copv.value HRI_P_CHAR1_GA /* Participation By Option In Plan (Program) */,
503 copmv.elig_count HRI_P_MEASURE1,
504 copmv.enrt_count HRI_P_MEASURE2,
505 copmv.enrt_per HRI_P_MEASURE3_MP
506 FROM HRI_MDP_BEN_ELIGENRL_COP_MV copmv,
507 HRI_CL_CO_PGMH_OIPLIP_V copv
508 WHERE copmv.asnd_lf_evt_dt = :ASND_LF_EVT_DT
509 AND copv.id = copmv.compobj_sk_pk
510 AND copmv.asnd_lf_evt_dt BETWEEN copv.start_date AND copv.end_date
511 AND copv.pgm_id = :PGM_RPTGTYP_ID
512 AND copv.ptip_id = :PTIP_PLTYP_ID
513 AND copv.plip_id = :PLIP_PL_ID
514 AND copv.oiplip_id <> -1 /* Bug 4543445 To remove records with - plan without options */
515 AND :BEN_AS_OF_DATE BETWEEN copmv.effective_start_Date
516 AND copmv.effective_end_Date
517 &ORDER_BY_CLAUSE';
518 --
519 /*
520 ELSIF l_selection = 2
521 THEN
522 --
523 -- Use Views corresponding to Reporting Group Dimension
524 --
525 x_custom_sql :=
526 ' SELECT optv.name HRI_P_CHAR1_GA, -- Participation By Option In Plan (Reporting Group)
527 roptmv.elig_count HRI_P_MEASURE1,
528 roptmv.enrt_count HRI_P_MEASURE2,
529 roptmv.enrt_per HRI_P_MEASURE3_MP
530 FROM HRI_MDP_BEN_ELIGENRL_ROPT_MV roptmv,
531 BEN_OIPL_F copv,
532 BEN_OPT_F optv
533 WHERE roptmv.asnd_lf_evt_dt = :ASND_LF_EVT_DT
534 AND copv.oipl_id = roptmv.oipl_id
535 AND copv.opt_id = optv.opt_id
536 AND roptmv.asnd_lf_evt_dt BETWEEN copv.effective_start_date AND copv.effective_end_date
537 AND roptmv.asnd_lf_evt_dt BETWEEN optv.effective_start_date AND optv.effective_end_date
538 AND roptmv.rptgtyp_id = :PGM_RPTGTYP_ID
539 AND copv.pl_id = :PLIP_PL_ID
540 AND :BEN_AS_OF_DATE BETWEEN roptmv.effective_start_Date
541 AND roptmv.effective_end_Date
542 &ORDER_BY_CLAUSE';
543 */
544 --
545 END IF;
546 --
547 END GET_ELIGENRL_OIPL_SQL;
548 --
549 -- ----------------------------------------------------------------------------
550 -- |--------------------------< GET_ENRLACTN_SQL >-----------------------------|
551 -- ----------------------------------------------------------------------------
552 --
553 -- Open Action Items
554 -- AK_REGION : HRI_P_ENRLACTN_OPN_ITEMS
555 --
556 PROCEDURE GET_ENRLACTN_SQL (
557 p_page_parameter_tbl IN bis_pmv_page_parameter_tbl,
558 x_custom_sql OUT NOCOPY VARCHAR2,
559 x_custom_output OUT NOCOPY bis_query_attributes_tbl
560 )
561 IS
562 --
563 l_selection number;
564 l_custom_rec BIS_QUERY_ATTRIBUTES;
565 --
566 BEGIN
567 --
568 x_custom_sql := ' SELECT NULL HRI_P_CHAR1_GA /* Open Action Items */,
569 NULL HRI_P_MEASURE1,
570 NULL HRI_P_MEASURE2,
571 NULL HRI_P_CHAR2_GA
572 FROM DUAL';
573 --
574 l_custom_rec := BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
575 x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
576 --
577 set_bind_parameters( p_page_parameter_tbl => p_page_parameter_tbl,
578 x_custom_output => x_custom_output,
579 p_selection => l_selection) ;
580 --
581 IF l_selection = 1
582 THEN
583 --
584 -- Use Views corresponding to Program Dimension
585 --
586 x_custom_sql :=
587 ' SELECT actd.value HRI_P_CHAR1_GA /* Open Action Items (Program) */,
588 sspnd_count HRI_P_MEASURE1,
589 actn_item_ind HRI_P_MEASURE2,
590 actd.id HRI_P_CHAR2_GA
594 AND pgm_id = :PGM_RPTGTYP_ID
591 FROM HRI_MDP_BEN_ENRLACTN_PGM_MV peac,
592 HRI_CL_BACTN_TYP_V actd
593 WHERE asnd_lf_evt_dt = :ASND_LF_EVT_DT
595 AND peac.actn_typ_cd = actd.ID
596 AND :BEN_AS_OF_DATE BETWEEN effective_start_Date
597 AND effective_end_Date
598 AND actn_item_ind > 0
599 &ORDER_BY_CLAUSE';
600 --
601 /*
602 ELSIF l_selection = 2
603 THEN
604 --
605 -- Use Views corresponding to Reporting Group Dimension
606 --
607 x_custom_sql :=
608 ' SELECT actd.value HRI_P_CHAR1_GA, -- Open Action Items (Reporting Group)
609 sspnd_count HRI_P_MEASURE1,
610 actn_item_ind HRI_P_MEASURE2,
611 actd.id HRI_P_CHAR2_GA
612 FROM HRI_MDP_BEN_ENRLACTN_RPG_MV peac,
613 HRI_CL_BACTN_TYP_V actd
614 WHERE asnd_lf_evt_dt = :ASND_LF_EVT_DT
615 AND rptgtyp_id = :PGM_RPTGTYP_ID
616 AND peac.actn_typ_cd = actd.ID
617 AND :BEN_AS_OF_DATE BETWEEN effective_start_Date
618 AND effective_end_Date
619 &ORDER_BY_CLAUSE';
620 */
621 --
622 END IF;
623 --
624 END GET_ENRLACTN_SQL;
625 --
626 -- ----------------------------------------------------------------------------
627 -- |------------------------< GET_ENRLACTN_DET_SQL >--------------------------|
628 -- ----------------------------------------------------------------------------
629 --
630 -- Open Action Item Details
631 -- AK_REGION : HRI_P_ENRLACTN_OPN_ITEM_DTL
632 --
633 PROCEDURE GET_ENRLACTN_DET_SQL (
634 p_page_parameter_tbl IN bis_pmv_page_parameter_tbl,
635 x_custom_sql OUT NOCOPY VARCHAR2,
636 x_custom_output OUT NOCOPY bis_query_attributes_tbl
637 )
638 IS
639 --
640 l_selection NUMBER;
641 l_custom_rec BIS_QUERY_ATTRIBUTES;
642 l_lnk_profile_chk NUMBER;
643 --
644 l_parameter_rec hri_oltp_pmv_util_param.HRI_PMV_PARAM_REC_TYPE;
645 l_bind_tab hri_oltp_pmv_util_param.HRI_PMV_BIND_TAB_TYPE;
646 l_lnk_emp_name VARCHAR2(255);
647 --
648 BEGIN
649 --
650 x_custom_sql := ' SELECT NULL HRI_P_CHAR1_GA /* Open Action Item Details */,
651 NULL HRI_P_CHAR2_GA,
652 NULL HRI_P_CHAR3_GA,
653 NULL HRI_P_CHAR4_GA,
654 NULL HRI_P_CHAR5_GA,
655 NULL HRI_P_CHAR6_GA,
656 NULL HRI_P_CHAR7_GA,
657 NULL HRI_P_CHAR8_GA,
658 NULL HRI_P_DATE1_GA,
659 NULL HRI_P_CHAR9_GA,
660 NULL HRI_P_DRILL_URL1
661 FROM DUAL';
662 --
663 l_custom_rec := BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
664 x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
665 --
666 set_bind_parameters( p_page_parameter_tbl => p_page_parameter_tbl,
667 x_custom_output => x_custom_output,
668 p_selection => l_selection) ;
669 --
670 -- Populate L_PARAMETER_REC and L_BIND_TAB for subsequent use in CHK_EMP_DIR_LNK
671 --
672 hri_oltp_pmv_util_param.get_parameters_from_table ( p_page_parameter_tbl => p_page_parameter_tbl,
673 p_parameter_rec => l_parameter_rec,
674 p_bind_tab => l_bind_tab);
675 --
676 -- This function call checks Profile Option HRI:DBI Link To Transaction System
677 -- Link to HR Employee Directory
678 --
679 l_lnk_profile_chk := hri_oltp_pmv_util_pkg.chk_emp_dir_lnk( p_parameter_rec => l_parameter_rec,
680 p_bind_tab => l_bind_tab);
681 --
682 IF (l_lnk_profile_chk = 1 AND l_parameter_rec.time_curr_end_date = TRUNC(SYSDATE) )
683 THEN
684 --
685 l_lnk_emp_name := 'pFunctionName=HR_EMPDIR_EMPDTL_PROXY_SS&pId=HRI_P_CHAR9_GA&OAPB=FII_HR_BRAND_TEXT';
686 --
687 ELSE
688 --
689 l_lnk_emp_name := '';
690 --
691 END IF ;
692 --
693 IF l_selection = 1
694 THEN
695 --
696 -- Use Views corresponding to Program Dimension
697 --
698 x_custom_sql :=
699 ' SELECT per.full_name HRI_P_CHAR1_GA /* Open Action Item Details (Program) */,
700 per.employee_number HRI_P_CHAR2_GA,
701 per.email_address HRI_P_CHAR3_GA,
702 copv.pl_value HRI_P_CHAR4_GA,
703 copv.value HRI_P_CHAR5_GA,
704 HR_GENERAL.DECODE_LOOKUP (''YES_NO'',
705 DECODE (peac.sspnd_ind ,1,''Y'',''N'')) HRI_P_CHAR6_GA,
706 icopv.pl_value HRI_P_CHAR7_GA,
707 icopv.value HRI_P_CHAR8_GA,
708 peac.due_dt HRI_P_DATE1_GA,
709 per.person_id HRI_P_CHAR9_GA,
710 ''' || l_lnk_emp_name || ''' HRI_P_DRILL_URL1
711 FROM HRI_MB_BEN_ENRLACTN_CT peac,
712 HRI_CL_CO_PGMH_OIPLIP_V copv,
713 HRI_CL_CO_PGMH_OIPLIP_V icopv,
714 PER_ALL_PEOPLE_F per
715 WHERE peac.asnd_lf_evt_dt = :ASND_LF_EVT_DT
716 AND peac.actn_typ_cd = :ACTN_TYP_CD
717 AND :BEN_AS_OF_DATE BETWEEN peac.effective_start_Date
718 AND peac.effective_end_Date
719 AND peac.compobj_sk_pk = copv.id
720 AND NVL(peac.interim_compobj_sk_pk, -1) = icopv.id(+)
721 AND per.person_id = peac.person_id
722 AND NVL(TRUNC(SYSDATE),peac.asnd_lf_evt_dt) BETWEEN per.effective_start_date
723 AND per.effective_end_date
724 AND copv.pgm_id = :PGM_RPTGTYP_ID
725 AND peac.actn_item_ind > 0
726 &ORDER_BY_CLAUSE';
727 --
728 /*
729 ELSIF l_selection = 2
730 THEN
731 --
732 -- Use Views corresponding to Reporting Group Dimension
733 --
734 x_custom_sql :=
735 ' SELECT per.full_name HRI_P_CHAR1_GA, -- Open Action Item Details (Reporting Group)
736 per.employee_number HRI_P_CHAR2_GA,
737 per.email_address HRI_P_CHAR3_GA,
738 cppv.value HRI_P_CHAR4_GA,
739 decode(copv.opt_id,
740 null, null,
741 copv.value) HRI_P_CHAR5_GA,
742 hl.meaning HRI_P_CHAR6_GA,
743 icppv.value HRI_P_CHAR7_GA,
744 decode(icopv.opt_id,
745 null, null,
746 icopv.value) HRI_P_CHAR8_GA,
747 peac.due_dt HRI_P_DATE1_GA,
748 per.person_id HRI_P_CHAR9_GA,
749 ''' || l_lnk_emp_name || ''' HRI_P_DRILL_URL1
750 FROM HRI_MB_BEN_ENRLACTN_CT peac,
751 HRI_CL_CO_OIPLIP_V copv,
752 HRI_CL_CO_PLIP_V cppv,
753 HRI_CL_CO_OIPLIP_V icopv,
754 HRI_CL_CO_PLIP_V icppv,
755 PER_ALL_PEOPLE_F per,
756 HR_LOOKUPS hl
757 WHERE peac.asnd_lf_evt_dt = :ASND_LF_EVT_DT
758 AND peac.actn_typ_cd = :ACTN_TYP_CD
759 AND :BEN_AS_OF_DATE BETWEEN peac.effective_start_Date
760 AND peac.effective_end_Date
761 AND copv.plip_id = cppv.id
762 AND peac.interim_compobj_sk_pk = icopv.id(+)
763 AND icopv.plip_id = icppv.id(+)
764 AND per.person_id = peac.person_id
765 AND peac.asnd_lf_evt_dt BETWEEN per.effective_start_date AND per.effective_end_date
766 and peac.compobj_sk_pk = copv.id
767 AND hl.lookup_type = ''YES_NO''
768 AND DECODE(peac.sspnd_ind,1,''Y'',''N'') = hl.lookup_code
769 AND copv.pgm_id IN
770 ( SELECT pgm_id
771 FROM hri_cl_co_rptgrp_v
772 WHERE ID = :PGM_RPTGTYP_ID )
773 &ORDER_BY_CLAUSE';
774 --
775 */
776 END IF;
777 --
778 END GET_ENRLACTN_DET_SQL;
779 --
780 -- ----------------------------------------------------------------------------
781 -- |-------------------------< GET_ELCTN_EVNT_SQL >---------------------------|
782 -- ----------------------------------------------------------------------------
783 --
784 -- Event Status
785 -- AK_REGION : HRI_P_ELCTN_EVNT_STATUS
786 --
787 PROCEDURE GET_ELCTN_EVNT_SQL (
788 p_page_parameter_tbl IN bis_pmv_page_parameter_tbl,
789 x_custom_sql OUT NOCOPY VARCHAR2,
790 x_custom_output OUT NOCOPY bis_query_attributes_tbl
791 )
792 IS
793 --
794 l_selection number;
795 l_custom_rec BIS_QUERY_ATTRIBUTES;
796 --
797 BEGIN
798 --
799 x_custom_sql := ' SELECT NULL HRI_P_CHAR1_GA /* Event Status */,
800 NULL HRI_P_MEASURE1
801 FROM DUAL';
802 --
803 l_custom_rec := BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
804 x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
805 --
806 set_bind_parameters( p_page_parameter_tbl => p_page_parameter_tbl,
807 x_custom_output => x_custom_output,
808 p_selection => l_selection) ;
809 --
810 IF l_selection = 1
811 THEN
812 --
813 -- Use Views corresponding to Program Dimension
814 --
815 x_custom_sql :=
816 ' SELECT /* Event Status (Program) */
817 HR_GENERAL.DECODE_LOOKUP (
818 DECODE(pelc.ler_status_cd,
819 ''MNL'',''BEN_PTNL_LER_FOR_PER_STAT''
820 ,''BEN_PER_IN_LER_STAT'')
821 , pelc.ler_status_cd) HRI_P_CHAR1_GA,
822 per_count HRI_P_MEASURE1
823 FROM HRI_MDP_BEN_LESTAT_PGM_MV pelc
824 WHERE asnd_lf_evt_dt = :ASND_LF_EVT_DT
825 AND pgm_id = :PGM_RPTGTYP_ID
826 &ORDER_BY_CLAUSE';
827 --
828 /*
829 ELSIF l_selection = 2
830 THEN
831 --
832 -- Use Views corresponding to Reporting Group Dimension
833 --
834 x_custom_sql :=
835 ' SELECT hl.meaning HRI_P_CHAR1_GA, -- Event Status (Reporting Group)
836 per_count HRI_P_MEASURE1
837 FROM HRI_MDP_BEN_LESTAT_RPTG_MV pelc,
838 HR_LOOKUPS hl
839 WHERE asnd_lf_evt_dt = :ASND_LF_EVT_DT
840 AND rptgtyp_id = :PGM_RPTGTYP_ID
841 AND pelc.ler_status_cd = hl.lookup_code
842 AND hl.lookup_type = ''BEN_PER_IN_LER_STAT''
843 &ORDER_BY_CLAUSE';
847 --
844 --
845 */
846 END IF;
848 END GET_ELCTN_EVNT_SQL;
849 --
850 -- ----------------------------------------------------------------------------
851 -- |------------------------< GET_ENRT_KPI_GRAPH_SQL >-------------------------|
852 -- ----------------------------------------------------------------------------
853 --
854 -- Open Enrollment KPIs
855 -- AK_REGION : HRI_P_ELCTN_ENRT_GRAPH
856 --
857 PROCEDURE GET_ENRT_KPI_GRAPH_SQL (
858 p_page_parameter_tbl IN bis_pmv_page_parameter_tbl,
859 x_custom_sql OUT NOCOPY VARCHAR2,
860 x_custom_output OUT NOCOPY bis_query_attributes_tbl
861 )
862 IS
863 --
864 l_selection number;
865 l_custom_rec BIS_QUERY_ATTRIBUTES;
866 --
867 BEGIN
868 --
869 x_custom_sql := ' SELECT NULL HRI_P_CHAR1_GA /* Open Enrollment KPIs */,
870 NULL HRI_P_MEASURE1
871 FROM DUAL';
872 --
873 l_custom_rec := BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
874 x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
875 --
876 set_bind_parameters( p_page_parameter_tbl => p_page_parameter_tbl,
877 x_custom_output => x_custom_output,
878 p_selection => l_selection) ;
879 --
880 IF l_selection = 1
881 THEN
882 --
883 -- Use Views corresponding to Program Dimension
884 --
885 x_custom_sql :=
886 ' SELECT hl.meaning HRI_P_CHAR1_GA /* Open Enrollment KPIs (Program) */,
887 pelc.cnt_all HRI_P_MEASURE1
888 FROM HRI_MDP_BEN_ELCTN_PGMV_MV pelc,
889 HR_LOOKUPS hl
890 WHERE asnd_lf_evt_dt = :ASND_LF_EVT_DT
891 AND pgm_id = :PGM_RPTGTYP_ID
892 AND hl.lookup_type = ''HRI_BEN_ENRT_STATUS''
893 AND hl.lookup_code <> ''ELIG''
894 AND hl.lookup_code = rec_type
895 &ORDER_BY_CLAUSE';
896 --
897 /*
898 ELSIF l_selection = 2
899 THEN
900 --
901 -- Use Views corresponding to Reporting Group Dimension
902 --
903 x_custom_sql :=
904 ' SELECT hl.meaning HRI_P_CHAR1_GA, -- Open Enrollment KPIs (Reporting Group)
905 pelc.cnt_all HRI_P_MEASURE1
906 FROM HRI_MDP_BEN_ELCTN_RPTGV_MV pelc,
907 HR_LOOKUPS hl
908 WHERE asnd_lf_evt_dt = :ASND_LF_EVT_DT
909 AND rptgtyp_id = :PGM_RPTGTYP_ID
910 AND hl.lookup_type = ''HRI_BEN_ENRT_STATUS''
911 AND hl.lookup_code <> ''ELIG''
912 AND hl.lookup_code = rec_type
913 &ORDER_BY_CLAUSE';
914 --
915 */
916 END IF;
917 --
918 END GET_ENRT_KPI_GRAPH_SQL;
919 --
920 -- ----------------------------------------------------------------------------
921 -- |---------------------------< GET_ENRT_KPI_SQL >---------------------------|
922 -- ----------------------------------------------------------------------------
923 --
924 -- Open Enrollment Status
925 -- AK_REGION : HRI_K_ELCTN_ENRT
926 --
927 PROCEDURE GET_ENRT_KPI_SQL (
928 p_page_parameter_tbl IN bis_pmv_page_parameter_tbl,
929 x_custom_sql OUT NOCOPY VARCHAR2,
930 x_custom_output OUT NOCOPY bis_query_attributes_tbl
931 )
932 IS
933 --
934 l_selection number;
935 l_custom_rec BIS_QUERY_ATTRIBUTES;
936 --
937 BEGIN
938 --
939 x_custom_sql := ' SELECT NULL HRI_P_MEASURE1 /* Open Enrollment Status */,
940 NULL HRI_P_MEASURE2,
941 NULL HRI_P_MEASURE3,
942 NULL HRI_P_MEASURE4
943 FROM DUAL';
944 --
945 l_custom_rec := BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
946 x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
947 --
948 set_bind_parameters( p_page_parameter_tbl => p_page_parameter_tbl,
949 x_custom_output => x_custom_output,
950 p_selection => l_selection) ;
951 --
952 IF l_selection = 1
953 THEN
954 --
955 -- Use Views corresponding to Program Dimension
956 --
957 x_custom_sql :=
958 ' SELECT elig_count HRI_P_MEASURE1 /* Open Enrollment Status (Program) */,
959 enrt_count HRI_P_MEASURE2,
960 not_enrt_count HRI_P_MEASURE3,
961 dflt_count HRI_P_MEASURE4
962 FROM HRI_MDP_BEN_ELCTN_PGM_MV
963 WHERE asnd_lf_evt_dt = :ASND_LF_EVT_DT
964 AND pgm_id = :PGM_RPTGTYP_ID ';
965 --
966 /*
967 ELSIF l_selection = 2
968 THEN
969 --
970 -- Use Views corresponding to Reporting Group Dimension
971 --
972 x_custom_sql :=
973 ' SELECT elig_count HRI_P_MEASURE1, -- Open Enrollment Status (Reporting Group)
974 enrt_count HRI_P_MEASURE2,
975 not_enrt_count HRI_P_MEASURE3,
976 dflt_count HRI_P_MEASURE4
977 FROM HRI_MDP_BEN_ELCTN_RPTG_MV
978 WHERE asnd_lf_evt_dt = :ASND_LF_EVT_DT
979 AND rptgtyp_id = :PGM_RPTGTYP_ID ';
980 --
981 */
982 END IF;
983 --
984 END GET_ENRT_KPI_SQL;
985 --
986 -- ----------------------------------------------------------------------------
987 -- |-------------------------< GET_ELIGENRL_PTIP_SQL >-------------------------|
988 -- ----------------------------------------------------------------------------
989 --
990 -- Participation By Plan Type
991 -- AK_REGION : HRI_P_ELIGENRL_PRTT_PTIP
992 --
993 PROCEDURE GET_ELIGENRL_PTIP_SQL (
994 p_page_parameter_tbl IN bis_pmv_page_parameter_tbl,
995 x_custom_sql OUT NOCOPY VARCHAR2,
996 x_custom_output OUT NOCOPY bis_query_attributes_tbl
997 )
998 IS
999 --
1000 l_selection number;
1001 l_custom_rec BIS_QUERY_ATTRIBUTES;
1002 --
1003 BEGIN
1004 --
1005 x_custom_sql := ' SELECT NULL HRI_P_CHAR1_GA /* Participation By Plan Type */,
1006 NULL HRI_P_MEASURE1,
1007 NULL HRI_P_MEASURE2,
1008 NULL HRI_P_MEASURE3_MP,
1009 NULL HRI_P_MEASURE4,
1010 NULL HRI_P_MEASURE5,
1011 NULL HRI_P_MEASURE6,
1012 NULL HRI_P_MEASURE7_MP,
1013 NULL HRI_P_MEASURE8_MP,
1014 NULL HRI_P_MEASURE9_MP,
1015 NULL HRI_P_MEASURE10
1016 FROM DUAL';
1017 --
1018 l_custom_rec := BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
1019 x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
1020 --
1021 set_bind_parameters( p_page_parameter_tbl => p_page_parameter_tbl,
1022 x_custom_output => x_custom_output,
1023 p_selection => l_selection) ;
1024 --
1025 IF l_selection = 1
1026 THEN
1027 --
1028 -- Use Views corresponding to Program Dimension
1029 --
1030 x_custom_sql :=
1031 ' SELECT ctpv.value HRI_P_CHAR1_GA /* Participation By Plan Type (Program) */,
1032 ctpmv.elig_count HRI_P_MEASURE1,
1033 ctpmv.enrt_count HRI_P_MEASURE2,
1034 ctpmv.enrt_per HRI_P_MEASURE3_MP,
1035 ctpmv.waive_expl_count HRI_P_MEASURE4,
1036 ctpmv.waive_dflt_count HRI_P_MEASURE5,
1037 ctpmv.waive_total_count HRI_P_MEASURE6,
1038 ctpmv.waive_expl_per HRI_P_MEASURE7_MP,
1039 ctpmv.waive_dflt_per HRI_P_MEASURE8_MP,
1040 ctpmv.waive_total_per HRI_P_MEASURE9_MP,
1041 ctpmv.ptip_id HRI_P_MEASURE10
1042 FROM HRI_MDP_BEN_ELIGENRL_CTP_MV ctpmv,
1043 HRI_CL_CO_PGMH_PTIP_V ctpv
1044 WHERE ctpmv.asnd_lf_evt_dt = :ASND_LF_EVT_DT
1045 AND ctpv.id = ctpmv.ptip_id
1046 AND ctpmv.asnd_lf_evt_dt BETWEEN ctpv.start_date AND ctpv.end_date
1047 AND ctpv.pgm_id = :PGM_RPTGTYP_ID
1048 AND :BEN_AS_OF_DATE BETWEEN ctpmv.effective_start_date
1049 AND ctpmv.effective_end_date
1050 &ORDER_BY_CLAUSE';
1051 --
1052 /*
1053 ELSIF l_selection = 2
1054 THEN
1055 --
1056 -- Use Views corresponding to Reporting Group Dimension
1057 --
1058 x_custom_sql :=
1059 ' SELECT ptpv.value HRI_P_CHAR1_GA, -- Participation By Plan Type (Reporting Group)
1060 ptpmv.elig_count HRI_P_MEASURE1,
1061 ptpmv.enrt_count HRI_P_MEASURE2,
1062 ptpmv.enrt_per HRI_P_MEASURE3_MP,
1063 ptpmv.waive_expl_count HRI_P_MEASURE4,
1064 ptpmv.waive_dflt_count HRI_P_MEASURE5,
1065 ptpmv.waive_total_count HRI_P_MEASURE6,
1066 ptpmv.waive_expl_per HRI_P_MEASURE7_MP,
1067 ptpmv.waive_dflt_per HRI_P_MEASURE8_MP,
1068 ptpmv.waive_total_per HRI_P_MEASURE9_MP,
1069 ptpmv.pl_typ_id HRI_P_MEASURE10
1070 FROM HRI_MDP_BEN_ELIGENRL_RPTP_MV ptpmv,
1071 HRI_CL_CO_RPTG_PLTYP_V ptpv
1072 WHERE ptpmv.asnd_lf_evt_dt = :ASND_LF_EVT_DT
1073 AND ptpv.id = ptpmv.pl_typ_id
1074 AND ptpv.rptgtyp_id = ptpmv.rptgtyp_id
1075 AND ptpmv.asnd_lf_evt_dt BETWEEN ptpv.start_date AND ptpv.end_date
1076 AND ptpmv.rptgtyp_id = :PGM_RPTGTYP_ID
1077 AND :BEN_AS_OF_DATE BETWEEN ptpmv.effective_start_Date
1078 AND ptpmv.effective_end_Date
1079 &ORDER_BY_CLAUSE';
1080 */
1081 --
1082 END IF;
1083 --
1084 END GET_ELIGENRL_PTIP_SQL;
1085 --
1086 END HRI_OLTP_PMV_OPEN_ENRT_STAT;