1 package ben_rt_prfl_cache AUTHID CURRENT_USER as
2 /* $Header: bertprch.pkh 115.16 2003/02/10 19:31:35 hnarayan ship $*/
3 --
4 /*
5 +==============================================================================+
6 | Copyright (c) 1997 Oracle Corporation |
7 | Redwood Shores, California, USA |
8 | All rights reserved. |
9 +==============================================================================+
10 --
11 History
12 Version Date Who What?
13 --------- --------- ---------- --------------------------------------------
14 115.0 24-Jun-99 bbulusu Created.
15 115.1 01-Jul-99 lmcdonal added ttl_prtt and ttl_cvg
16 115.2 16-Aug-99 stee Added absence_attendance_type_id and
17 abs_attendance_reason_id to loa_rsn cache.
18 115.3 27-Sep-99 GPERRY Added missing fields to many derived factors.
19 115.4 04-Oct-99 GPERRY Backport of 115.1 with 115.3 fixes.
20 115.5 04-Oct-99 GPERRY Leapfrog of 115.3.
21 115.6 06-Oct-99 STEE Add new criteria, period of enrollment
22 and disabled code.
23 115.7 12 Nov 99 tguy added los_fctr_id and age/comp id for
24 factors criteria.
25 115.8 11-May-00 dcollins Performance enhancements, implemented
26 exception capturing instead of exists clauses
27 added "in out NOCOPY" to all set procs and
28 removed extra record assignment statements
29 115.9 19-dec-00 Tmathers Fixed check_sql errors.
30 115.10 20-Mar-02 vsethi added dbdrv lines
31 115.11 29-Apr-02 pabodla Bug 1631182 : support user created
32 person type.
33 115.12 05-Jun-02 vsethi Added code to handle the new rates profile
34 115.13 12-Jun-02 vsethi Added code to handle the quartile and
35 performance rating
36 115.14 27-Jun-02 vsethi Bug 2436338 Modified the data type of title
37 field of g_qual_titl_inst_rec record type to varchar2.
38 115.16 10-feb-03 hnarayan Added NOCOPY Changes
39 -----------------------------------------------------------------------------
40 */
41 --
42 g_inst_count number;
43 --
44 -- -----------------------------------------------------------------------------
45 -- Each of the global record structures below represents a cursor that was part
46 -- of benrtprf.pkb. The id column at the beginning of every structure is
47 -- required for the caching mechanism to work.
48 --
49 -- The "_lookup" structures act as MASTER cache structures.
50 -- The "_instance" structures act as the DETAIL cache structures.
51 -- The "_out" in each case is a global area that the get_cached_data procedure
52 -- uses to to store the DATA BEING RETURNED.
53 -- ----------------------------------------------------------------------------
54 --
55 -- PEOPLE GROUP
56 --
57 type g_pg_inst_rec is record
58 (id number
59 ,person_type_id number
60 ,vrbl_rt_prfl_id number(15)
61 ,people_group_id number(15)
62 ,excld_flag varchar2(30)
63 ,segment1 varchar2(60)
64 ,segment2 varchar2(60)
65 ,segment3 varchar2(60)
66 ,segment4 varchar2(60)
67 ,segment5 varchar2(60)
68 ,segment6 varchar2(60)
69 ,segment7 varchar2(60)
70 ,segment8 varchar2(60)
71 ,segment9 varchar2(60)
72 ,segment10 varchar2(60)
73 ,segment11 varchar2(60)
74 ,segment12 varchar2(60)
75 ,segment13 varchar2(60)
76 ,segment14 varchar2(60)
77 ,segment15 varchar2(60)
78 ,segment16 varchar2(60)
79 ,segment17 varchar2(60)
80 ,segment18 varchar2(60)
81 ,segment19 varchar2(60)
82 ,segment20 varchar2(60)
83 ,segment21 varchar2(60)
84 ,segment22 varchar2(60)
85 ,segment23 varchar2(60)
86 ,segment24 varchar2(60)
87 ,segment25 varchar2(60)
88 ,segment26 varchar2(60)
89 ,segment27 varchar2(60)
90 ,segment28 varchar2(60)
91 ,segment29 varchar2(60)
92 ,segment30 varchar2(60) );
93 --
94 type g_pg_inst_tbl is table of ben_rt_prfl_cache.g_pg_inst_rec
95 index by binary_integer;
96 --
97 g_pg_lookup ben_cache.g_cache_lookup_table;
98 g_pg_instance g_pg_inst_tbl;
99 g_pg_out ben_rt_prfl_cache.g_pg_inst_tbl;
100 --
101 procedure get_rt_prfl_cache
102 (p_vrbl_rt_prfl_id in number
103 ,p_effective_date in date
104 ,p_lf_evt_ocrd_dt in date
105 ,p_business_group_id in number
106 ,p_inst_set in out NOCOPY ben_rt_prfl_cache.g_pg_inst_tbl
107 ,p_inst_count out nocopy number);
108 --
109 -- RULE
110 --
111 type g_rl_inst_rec is record
112 (id number
113 ,vrbl_rt_prfl_id number(15)
114 ,formula_id number(15)
115 );
116 --
117 type g_rl_inst_tbl is table of ben_rt_prfl_cache.g_rl_inst_rec
118 index by binary_integer;
119 --
120 g_rl_lookup ben_cache.g_cache_lookup_table;
121 g_rl_instance g_rl_inst_tbl;
122 g_rl_out ben_rt_prfl_cache.g_rl_inst_tbl;
123 --
124 procedure get_rt_prfl_cache
125 (p_vrbl_rt_prfl_id in number
126 ,p_effective_date in date
127 ,p_lf_evt_ocrd_dt in date
128 ,p_business_group_id in number
129 ,p_inst_set in out NOCOPY ben_rt_prfl_cache.g_rl_inst_tbl
130 ,p_inst_count out nocopy number);
131 --
132 -- TOBACCO
133 --
134 type g_tbco_inst_rec is record
135 (id number
136 ,vrbl_rt_prfl_id number(15)
137 ,uses_tbco_flag varchar2(30)
138 ,excld_flag varchar2(30)
139 );
140 --
141 type g_tbco_inst_tbl is table of ben_rt_prfl_cache.g_tbco_inst_rec
142 index by binary_integer;
143 --
144 g_tbco_lookup ben_cache.g_cache_lookup_table;
145 g_tbco_instance g_tbco_inst_tbl;
146 g_tbco_out g_tbco_inst_tbl;
147 --
148 procedure get_rt_prfl_cache
149 (p_vrbl_rt_prfl_id in number
150 ,p_effective_date in date
151 ,p_lf_evt_ocrd_dt in date
152 ,p_business_group_id in number
153 ,p_inst_set in out NOCOPY ben_rt_prfl_cache.g_tbco_inst_tbl
154 ,p_inst_count out nocopy number);
155 --
156 -- GENDER
157 --
158 type g_gndr_inst_rec is record
159 (id number
160 ,vrbl_rt_prfl_id number(15)
161 ,gndr_cd varchar2(30)
162 ,excld_flag varchar2(30)
163 );
164 --
165 type g_gndr_inst_tbl is table of ben_rt_prfl_cache.g_gndr_inst_rec
166 index by binary_integer;
167 --
168 g_gndr_lookup ben_cache.g_cache_lookup_table;
169 g_gndr_instance g_gndr_inst_tbl;
170 g_gndr_out g_gndr_inst_tbl;
171 --
172 procedure get_rt_prfl_cache
173 (p_vrbl_rt_prfl_id in number
174 ,p_effective_date in date
175 ,p_lf_evt_ocrd_dt in date
176 ,p_business_group_id in number
177 ,p_inst_set in out NOCOPY ben_rt_prfl_cache.g_gndr_inst_tbl
178 ,p_inst_count out nocopy number);
179 --
180 -- DISABLED CODE
181 --
182 type g_dsbld_inst_rec is record
183 (id number
184 ,vrbl_rt_prfl_id number(15)
185 ,dsbld_cd varchar2(30)
186 ,excld_flag varchar2(30)
187 );
188 --
189 type g_dsbld_inst_tbl is table of ben_rt_prfl_cache.g_dsbld_inst_rec
190 index by binary_integer;
191 --
192 g_dsbld_lookup ben_cache.g_cache_lookup_table;
193 g_dsbld_instance g_dsbld_inst_tbl;
194 g_dsbld_out g_dsbld_inst_tbl;
195 --
196 procedure get_rt_prfl_cache
197 (p_vrbl_rt_prfl_id in number
198 ,p_effective_date in date
199 ,p_lf_evt_ocrd_dt in date
200 ,p_business_group_id in number
201 ,p_inst_set in out NOCOPY ben_rt_prfl_cache.g_dsbld_inst_tbl
202 ,p_inst_count out nocopy number);
203 --
204 -- BARGAINING UNIT
205 --
206 type g_brgng_inst_rec is record
207 (id number
208 ,vrbl_rt_prfl_id number(15)
209 ,brgng_unit_cd varchar2(30)
210 ,excld_flag varchar2(30)
211 );
212 --
213 type g_brgng_inst_tbl is table of ben_rt_prfl_cache.g_brgng_inst_rec
214 index by binary_integer;
215 --
216 g_brgng_lookup ben_cache.g_cache_lookup_table;
217 g_brgng_instance g_brgng_inst_tbl;
218 g_brgng_out g_brgng_inst_tbl;
219 --
220 procedure get_rt_prfl_cache
221 (p_vrbl_rt_prfl_id in number
222 ,p_effective_date in date
223 ,p_lf_evt_ocrd_dt in date
224 ,p_business_group_id in number
225 ,p_inst_set in out NOCOPY ben_rt_prfl_cache.g_brgng_inst_tbl
226 ,p_inst_count out nocopy number);
227 --
228 -- BENEFITS GROUP
229 --
230 type g_bnfgrp_inst_rec is record
231 (id number
232 ,vrbl_rt_prfl_id number(15)
233 ,benfts_grp_id number(15)
234 ,excld_flag varchar2(30)
235 );
236 --
237 type g_bnfgrp_inst_tbl is table of ben_rt_prfl_cache.g_bnfgrp_inst_rec
238 index by binary_integer;
239 --
240 g_bnfgrp_lookup ben_cache.g_cache_lookup_table;
241 g_bnfgrp_instance g_bnfgrp_inst_tbl;
242 g_bnfgrp_out g_bnfgrp_inst_tbl;
243 --
244 procedure get_rt_prfl_cache
245 (p_vrbl_rt_prfl_id in number
246 ,p_effective_date in date
247 ,p_lf_evt_ocrd_dt in date
248 ,p_business_group_id in number
249 ,p_inst_set in out NOCOPY ben_rt_prfl_cache.g_bnfgrp_inst_tbl
250 ,p_inst_count out nocopy number);
251 --
252 -- EMPLOYMENT STATUS
253 --
254 type g_eestat_inst_rec is record
255 (id number
256 ,vrbl_rt_prfl_id number(15)
257 ,assignment_status_type_id number(15)
258 ,excld_flag varchar2(30)
259 );
260 --
261 type g_eestat_inst_tbl is table of ben_rt_prfl_cache.g_eestat_inst_rec
262 index by binary_integer;
263 --
264 g_eestat_lookup ben_cache.g_cache_lookup_table;
265 g_eestat_instance g_eestat_inst_tbl;
266 g_eestat_out g_eestat_inst_tbl;
267 --
268 procedure get_rt_prfl_cache
269 (p_vrbl_rt_prfl_id in number
270 ,p_effective_date in date
271 ,p_lf_evt_ocrd_dt in date
272 ,p_business_group_id in number
273 ,p_inst_set in out NOCOPY ben_rt_prfl_cache.g_eestat_inst_tbl
274 ,p_inst_count out nocopy number);
275 --
276 --
277 -- FULL TIME PART TIME
278 --
279 type g_ftpt_inst_rec is record
280 (id number
281 ,vrbl_rt_prfl_id number(15)
282 ,fl_tm_pt_tm_cd varchar2(30)
283 ,excld_flag varchar2(30)
284 );
285 --
286 type g_ftpt_inst_tbl is table of ben_rt_prfl_cache.g_ftpt_inst_rec
287 index by binary_integer;
288 --
289 g_ftpt_lookup ben_cache.g_cache_lookup_table;
290 g_ftpt_instance g_ftpt_inst_tbl;
291 g_ftpt_out g_ftpt_inst_tbl;
292 --
293 procedure get_rt_prfl_cache
294 (p_vrbl_rt_prfl_id in number
295 ,p_effective_date in date
296 ,p_lf_evt_ocrd_dt in date
297 ,p_business_group_id in number
298 ,p_inst_set in out NOCOPY ben_rt_prfl_cache.g_ftpt_inst_tbl
299 ,p_inst_count out nocopy number);
300 --
301 -- GRADE
302 --
303 type g_grd_inst_rec is record
304 (id number
305 ,vrbl_rt_prfl_id number(15)
306 ,grade_id number(15)
307 ,excld_flag varchar2(30)
308 );
309 --
310 type g_grd_inst_tbl is table of ben_rt_prfl_cache.g_grd_inst_rec
311 index by binary_integer;
312 --
313 g_grd_lookup ben_cache.g_cache_lookup_table;
314 g_grd_instance g_grd_inst_tbl;
315 g_grd_out g_grd_inst_tbl;
316 --
317 procedure get_rt_prfl_cache
318 (p_vrbl_rt_prfl_id in number
319 ,p_effective_date in date
320 ,p_lf_evt_ocrd_dt in date
321 ,p_business_group_id in number
322 ,p_inst_set in out NOCOPY ben_rt_prfl_cache.g_grd_inst_tbl
323 ,p_inst_count out nocopy number);
324 --
325 -- PERCENT FULL TIME
326 --
327 type g_pctft_inst_rec is record
328 (id number
329 ,vrbl_rt_prfl_id number(15)
330 ,mn_pct_val number
331 ,mx_pct_val number
332 ,no_mn_pct_val_flag varchar2(30)
333 ,no_mx_pct_val_flag varchar2(30)
334 ,excld_flag varchar2(30)
335 );
336 --
337 type g_pctft_inst_tbl is table of ben_rt_prfl_cache.g_pctft_inst_rec
338 index by binary_integer;
339 --
340 g_pctft_lookup ben_cache.g_cache_lookup_table;
341 g_pctft_instance g_pctft_inst_tbl;
342 g_pctft_out g_pctft_inst_tbl;
343 --
344 procedure get_rt_prfl_cache
345 (p_vrbl_rt_prfl_id in number
346 ,p_effective_date in date
347 ,p_lf_evt_ocrd_dt in date
348 ,p_business_group_id in number
349 ,p_inst_set in out NOCOPY ben_rt_prfl_cache.g_pctft_inst_tbl
350 ,p_inst_count out nocopy number);
351 --
352 -- HOURS WORKED
353 --
354 type g_hrswkd_inst_rec is record
355 (id number
356 ,vrbl_rt_prfl_id number(15)
357 ,mn_hrs_num ben_hrs_wkd_in_perd_fctr.mn_hrs_num%type
358 ,mx_hrs_num ben_hrs_wkd_in_perd_fctr.mx_hrs_num%type
359 ,no_mn_hrs_wkd_flag varchar2(30)
360 ,no_mx_hrs_wkd_flag varchar2(30)
361 ,excld_flag varchar2(30)
362 );
363 --
364 type g_hrswkd_inst_tbl is table of ben_rt_prfl_cache.g_hrswkd_inst_rec
365 index by binary_integer;
366 --
367 g_hrswkd_lookup ben_cache.g_cache_lookup_table;
368 g_hrswkd_instance g_hrswkd_inst_tbl;
369 g_hrswkd_out g_hrswkd_inst_tbl;
370 --
371 procedure get_rt_prfl_cache
372 (p_vrbl_rt_prfl_id in number
373 ,p_effective_date in date
374 ,p_lf_evt_ocrd_dt in date
375 ,p_business_group_id in number
376 ,p_inst_set in out NOCOPY ben_rt_prfl_cache.g_hrswkd_inst_tbl
377 ,p_inst_count out nocopy number);
378 --
379 -- Period of Enrollment
380 --
381 type g_poe_inst_rec is record
382 (id number
383 ,vrbl_rt_prfl_id number(15)
384 ,mn_poe_num ben_poe_rt_f.mn_poe_num%type
385 ,mx_poe_num ben_poe_rt_f.mx_poe_num%type
386 ,no_mn_poe_flag varchar2(30)
387 ,no_mx_poe_flag varchar2(30)
388 ,rndg_cd ben_poe_rt_f.rndg_cd%type
389 ,rndg_rl ben_poe_rt_f.rndg_rl%type
390 ,poe_nnmntry_uom ben_poe_rt_f.poe_nnmntry_uom%type
391 ,cbr_dsblty_apls_flag varchar2(30)
392 );
393 --
394 type g_poe_inst_tbl is table of ben_rt_prfl_cache.g_poe_inst_rec
395 index by binary_integer;
396 --
397 g_poe_lookup ben_cache.g_cache_lookup_table;
398 g_poe_instance g_poe_inst_tbl;
399 g_poe_out g_poe_inst_tbl;
400 --
401 procedure get_rt_prfl_cache
402 (p_vrbl_rt_prfl_id in number
403 ,p_effective_date in date
404 ,p_lf_evt_ocrd_dt in date
405 ,p_business_group_id in number
406 ,p_inst_set in out NOCOPY ben_rt_prfl_cache.g_poe_inst_tbl
407 ,p_inst_count out nocopy number);
408 --
409 -- LABOR UNION MEMBERSHIP
410 --
411 type g_lbrmmbr_inst_rec is record
412 (id number
413 ,vrbl_rt_prfl_id number(15)
414 ,lbr_mmbr_flag varchar2(30)
415 ,excld_flag varchar2(30)
416 );
417 --
418 type g_lbrmmbr_inst_tbl is table of ben_rt_prfl_cache.g_lbrmmbr_inst_rec
419 index by binary_integer;
420 --
421 g_lbrmmbr_lookup ben_cache.g_cache_lookup_table;
422 g_lbrmmbr_instance g_lbrmmbr_inst_tbl;
423 g_lbrmmbr_out g_lbrmmbr_inst_tbl;
424 --
425 procedure get_rt_prfl_cache
426 (p_vrbl_rt_prfl_id in number
427 ,p_effective_date in date
428 ,p_lf_evt_ocrd_dt in date
429 ,p_business_group_id in number
430 ,p_inst_set in out NOCOPY ben_rt_prfl_cache.g_lbrmmbr_inst_tbl
431 ,p_inst_count out nocopy number);
432 --
433 -- LEGAL ENTITY
434 --
435 type g_lglenty_inst_rec is record
436 (id number
437 ,vrbl_rt_prfl_id number(15)
438 ,organization_id number(15)
439 ,excld_flag varchar2(30)
440 );
441 --
442 type g_lglenty_inst_tbl is table of ben_rt_prfl_cache.g_lglenty_inst_rec
443 index by binary_integer;
444 --
445 g_lglenty_lookup ben_cache.g_cache_lookup_table;
446 g_lglenty_instance g_lglenty_inst_tbl;
447 g_lglenty_out g_lglenty_inst_tbl;
448 --
449 procedure get_rt_prfl_cache
450 (p_vrbl_rt_prfl_id in number
451 ,p_effective_date in date
452 ,p_lf_evt_ocrd_dt in date
453 ,p_business_group_id in number
454 ,p_inst_set in out NOCOPY ben_rt_prfl_cache.g_lglenty_inst_tbl
455 ,p_inst_count out nocopy number);
456 --
457 -- LEAVE OF ABSENCE
458 --
459 type g_loa_inst_rec is record
460 (id number
461 ,vrbl_rt_prfl_id number(15)
462 ,absence_attendance_type_id number(15)
463 ,abs_attendance_reason_id number(15)
464 ,excld_flag varchar2(30)
465 );
466 --
467 type g_loa_inst_tbl is table of ben_rt_prfl_cache.g_loa_inst_rec
468 index by binary_integer;
469 --
470 g_loa_lookup ben_cache.g_cache_lookup_table;
471 g_loa_instance g_loa_inst_tbl;
472 g_loa_out g_loa_inst_tbl;
473 --
474 procedure get_rt_prfl_cache
475 (p_vrbl_rt_prfl_id in number
476 ,p_effective_date in date
477 ,p_lf_evt_ocrd_dt in date
478 ,p_business_group_id in number
479 ,p_inst_set in out NOCOPY ben_rt_prfl_cache.g_loa_inst_tbl
480 ,p_inst_count out nocopy number);
481 --
482 -- ORGANIZATION_UNIT
483 --
484 type g_org_inst_rec is record
485 (id number
486 ,vrbl_rt_prfl_id number(15)
487 ,organization_id number(15)
488 ,excld_flag varchar2(30)
489 );
490 --
491 type g_org_inst_tbl is table of ben_rt_prfl_cache.g_org_inst_rec
492 index by binary_integer;
493 --
494 g_org_lookup ben_cache.g_cache_lookup_table;
495 g_org_instance g_org_inst_tbl;
496 g_org_out g_org_inst_tbl;
497 --
498 procedure get_rt_prfl_cache
499 (p_vrbl_rt_prfl_id in number
500 ,p_effective_date in date
501 ,p_lf_evt_ocrd_dt in date
502 ,p_business_group_id in number
503 ,p_inst_set in out NOCOPY ben_rt_prfl_cache.g_org_inst_tbl
504 ,p_inst_count out nocopy number);
505 --
506 -- PERSON TYPE
507 --
508 type g_pertyp_inst_rec is record
509 (id number
510 ,vrbl_rt_prfl_id number(15)
511 --,per_typ_cd varchar2(30)
512 ,person_type_id number(15)
513 ,excld_flag varchar2(30)
514 );
515 --
516 type g_pertyp_inst_tbl is table of ben_rt_prfl_cache.g_pertyp_inst_rec
517 index by binary_integer;
518 --
519 g_pertyp_lookup ben_cache.g_cache_lookup_table;
520 g_pertyp_instance g_pertyp_inst_tbl;
521 g_pertyp_out g_pertyp_inst_tbl;
522 --
523 procedure get_rt_prfl_cache
524 (p_vrbl_rt_prfl_id in number
525 ,p_effective_date in date
526 ,p_lf_evt_ocrd_dt in date
527 ,p_business_group_id in number
528 ,p_inst_set in out NOCOPY ben_rt_prfl_cache.g_pertyp_inst_tbl
529 ,p_inst_count out nocopy number);
530 --
531 -- ZIP RANGE
532 --
533 type g_ziprng_inst_rec is record
534 (id number
535 ,vrbl_rt_prfl_id number(15)
536 ,from_value varchar2(30)
537 ,to_value varchar2(30)
538 ,excld_flag varchar2(30)
539 );
540 --
541 type g_ziprng_inst_tbl is table of ben_rt_prfl_cache.g_ziprng_inst_rec
542 index by binary_integer;
543 --
544 g_ziprng_lookup ben_cache.g_cache_lookup_table;
545 g_ziprng_instance g_ziprng_inst_tbl;
546 g_ziprng_out g_ziprng_inst_tbl;
547 --
548 procedure get_rt_prfl_cache
549 (p_vrbl_rt_prfl_id in number
550 ,p_effective_date in date
551 ,p_lf_evt_ocrd_dt in date
552 ,p_business_group_id in number
553 ,p_inst_set in out NOCOPY ben_rt_prfl_cache.g_ziprng_inst_tbl
554 ,p_inst_count out nocopy number);
555 --
556 -- PAYROLL
557 --
558 type g_pyrl_inst_rec is record
559 (id number
560 ,vrbl_rt_prfl_id number(15)
561 ,payroll_id number(15)
562 ,excld_flag varchar2(30)
563 );
564 --
565 type g_pyrl_inst_tbl is table of ben_rt_prfl_cache.g_pyrl_inst_rec
566 index by binary_integer;
567 --
568 g_pyrl_lookup ben_cache.g_cache_lookup_table;
569 g_pyrl_instance g_pyrl_inst_tbl;
570 g_pyrl_out g_pyrl_inst_tbl;
571 --
572 procedure get_rt_prfl_cache
573 (p_vrbl_rt_prfl_id in number
574 ,p_effective_date in date
575 ,p_lf_evt_ocrd_dt in date
576 ,p_business_group_id in number
577 ,p_inst_set in out NOCOPY ben_rt_prfl_cache.g_pyrl_inst_tbl
578 ,p_inst_count out nocopy number);
579 --
580 -- PAY BASIS
581 --
582 type g_py_bss_inst_rec is record
583 (id number
584 ,vrbl_rt_prfl_id number(15)
585 ,pay_basis_id number(15)
586 ,excld_flag varchar2(30)
587 );
588 --
589 type g_py_bss_inst_tbl is table of ben_rt_prfl_cache.g_py_bss_inst_rec
590 index by binary_integer;
591 --
592 g_py_bss_lookup ben_cache.g_cache_lookup_table;
593 g_py_bss_instance g_py_bss_inst_tbl;
594 g_py_bss_out g_py_bss_inst_tbl;
595 --
596 procedure get_rt_prfl_cache
597 (p_vrbl_rt_prfl_id in number
598 ,p_effective_date in date
599 ,p_lf_evt_ocrd_dt in date
600 ,p_business_group_id in number
601 ,p_inst_set in out NOCOPY ben_rt_prfl_cache.g_py_bss_inst_tbl
602 ,p_inst_count out nocopy number);
603 --
604 -- SCHEDULED HOURS
605 --
606 type g_scdhrs_inst_rec is record
607 (id number
608 ,vrbl_rt_prfl_id number(15)
609 ,hrs_num number(22,3)
610 ,freq_cd varchar2(30)
611 ,max_hrs_num number(22,3)
612 ,schedd_hrs_rl number(15)
613 ,determination_cd varchar2(30)
614 ,determination_rl number(15)
615 ,rounding_cd varchar2(30)
616 ,rounding_rl number(15)
617 ,excld_flag varchar2(30)
618 );
619 --
620 type g_scdhrs_inst_tbl is table of ben_rt_prfl_cache.g_scdhrs_inst_rec
621 index by binary_integer;
622 --
623 g_scdhrs_lookup ben_cache.g_cache_lookup_table;
624 g_scdhrs_instance g_scdhrs_inst_tbl;
625 g_scdhrs_out g_scdhrs_inst_tbl;
626 --
627 procedure get_rt_prfl_cache
628 (p_vrbl_rt_prfl_id in number
629 ,p_effective_date in date
630 ,p_lf_evt_ocrd_dt in date
631 ,p_business_group_id in number
632 ,p_inst_set in out NOCOPY ben_rt_prfl_cache.g_scdhrs_inst_tbl
633 ,p_inst_count out nocopy number);
634 --
635 -- WORK LOCATION
636 --
637 type g_wkloc_inst_rec is record
638 (id number
639 ,vrbl_rt_prfl_id number(15)
640 ,location_id number(15)
641 ,excld_flag varchar2(30)
642 );
643 --
644 type g_wkloc_inst_tbl is table of ben_rt_prfl_cache.g_wkloc_inst_rec
645 index by binary_integer;
646 --
647 g_wkloc_lookup ben_cache.g_cache_lookup_table;
648 g_wkloc_instance g_wkloc_inst_tbl;
649 g_wkloc_out g_wkloc_inst_tbl;
650 --
651 procedure get_rt_prfl_cache
652 (p_vrbl_rt_prfl_id in number
653 ,p_effective_date in date
654 ,p_lf_evt_ocrd_dt in date
655 ,p_business_group_id in number
656 ,p_inst_set in out NOCOPY ben_rt_prfl_cache.g_wkloc_inst_tbl
657 ,p_inst_count out nocopy number);
658 --
659 -- SERVICE AREA
660 --
661 type g_svcarea_inst_rec is record
662 (id number
663 ,vrbl_rt_prfl_id number(15)
664 ,from_value varchar2(30)
665 ,to_value varchar2(30)
666 ,excld_flag varchar2(30)
667 );
668 --
669 type g_svcarea_inst_tbl is table of ben_rt_prfl_cache.g_svcarea_inst_rec
670 index by binary_integer;
671 --
672 g_svcarea_lookup ben_cache.g_cache_lookup_table;
673 g_svcarea_instance g_svcarea_inst_tbl;
674 g_svcarea_out g_svcarea_inst_tbl;
675 --
676 procedure get_rt_prfl_cache
677 (p_vrbl_rt_prfl_id in number
678 ,p_effective_date in date
679 ,p_lf_evt_ocrd_dt in date
680 ,p_business_group_id in number
681 ,p_inst_set in out NOCOPY ben_rt_prfl_cache.g_svcarea_inst_tbl
682 ,p_inst_count out nocopy number);
683 --
684 -- HOURLY OR SALARIED
685 --
686 type g_hrlyslrd_inst_rec is record
687 (id number
688 ,vrbl_rt_prfl_id number(15)
689 ,hrly_slrd_cd varchar2(30)
690 ,excld_flag varchar2(30)
691 );
692 --
693 type g_hrlyslrd_inst_tbl is table of ben_rt_prfl_cache.g_hrlyslrd_inst_rec
694 index by binary_integer;
695 --
696 g_hrlyslrd_lookup ben_cache.g_cache_lookup_table;
697 g_hrlyslrd_instance g_hrlyslrd_inst_tbl;
698 g_hrlyslrd_out g_hrlyslrd_inst_tbl;
699 --
700 procedure get_rt_prfl_cache
701 (p_vrbl_rt_prfl_id in number
702 ,p_effective_date in date
703 ,p_lf_evt_ocrd_dt in date
704 ,p_business_group_id in number
705 ,p_inst_set in out NOCOPY ben_rt_prfl_cache.g_hrlyslrd_inst_tbl
706 ,p_inst_count out nocopy number);
707 --
708 -- AGE
709 --
710 type g_age_inst_rec is record
711 (id number
712 ,vrbl_rt_prfl_id number(15)
713 ,mn_age_num number
714 ,mx_age_num number
715 ,no_mn_age_flag varchar2(30)
716 ,no_mx_age_flag varchar2(30)
717 ,age_fctr_id number(15)
718 ,excld_flag varchar2(30)
719 );
720 --
721 type g_age_inst_tbl is table of ben_rt_prfl_cache.g_age_inst_rec
722 index by binary_integer;
723 --
724 g_age_lookup ben_cache.g_cache_lookup_table;
725 g_age_instance g_age_inst_tbl;
726 g_age_out g_age_inst_tbl;
727 --
728 procedure get_rt_prfl_cache
729 (p_vrbl_rt_prfl_id in number
730 ,p_effective_date in date
731 ,p_lf_evt_ocrd_dt in date
732 ,p_business_group_id in number
733 ,p_inst_set in out NOCOPY ben_rt_prfl_cache.g_age_inst_tbl
734 ,p_inst_count out nocopy number);
735 --
736 -- COMP LVL CODE
737 --
738 type g_complvl_inst_rec is record
739 (id number
740 ,vrbl_rt_prfl_id number(15)
741 ,mn_comp_val number
742 ,mx_comp_val number
743 ,no_mn_comp_flag varchar2(30)
744 ,no_mx_comp_flag varchar2(30)
745 ,comp_lvl_fctr_id number(15)
746 ,excld_flag varchar2(30)
747 );
748 --
749 type g_complvl_inst_tbl is table of ben_rt_prfl_cache.g_complvl_inst_rec
750 index by binary_integer;
751 --
752 g_complvl_lookup ben_cache.g_cache_lookup_table;
753 g_complvl_instance g_complvl_inst_tbl;
754 g_complvl_out g_complvl_inst_tbl;
755 --
756 procedure get_rt_prfl_cache
757 (p_vrbl_rt_prfl_id in number
758 ,p_effective_date in date
759 ,p_lf_evt_ocrd_dt in date
760 ,p_business_group_id in number
761 ,p_inst_set in out NOCOPY ben_rt_prfl_cache.g_complvl_inst_tbl
762 ,p_inst_count out nocopy number);
763 --
764 -- LOS
765 --
766 type g_los_inst_rec is record
767 (id number
768 ,vrbl_rt_prfl_id number(15)
769 ,mn_los_num number
770 ,mx_los_num number
771 ,no_mn_los_num_apls_flag varchar2(30)
772 ,no_mx_los_num_apls_flag varchar2(30)
773 ,excld_flag varchar2(30)
774 ,los_fctr_id number
775 );
776 --
777 type g_los_inst_tbl is table of ben_rt_prfl_cache.g_los_inst_rec
778 index by binary_integer;
779 --
780 g_los_lookup ben_cache.g_cache_lookup_table;
781 g_los_instance g_los_inst_tbl;
782 g_los_out g_los_inst_tbl;
783 --
784 procedure get_rt_prfl_cache
785 (p_vrbl_rt_prfl_id in number
786 ,p_effective_date in date
787 ,p_lf_evt_ocrd_dt in date
788 ,p_business_group_id in number
789 ,p_inst_set in out NOCOPY ben_rt_prfl_cache.g_los_inst_tbl
790 ,p_inst_count out nocopy number);
791 --
792 -- COMBINATION AGE LOS
793 --
794 type g_age_los_inst_rec is record
795 (id number
796 ,vrbl_rt_prfl_id number(15)
797 ,cmbnd_min_val number
798 ,cmbnd_max_val number
799 ,excld_flag varchar2(30)
800 ,cmbn_age_los_fctr_id number
801 );
802 --
803 type g_age_los_inst_tbl is table of ben_rt_prfl_cache.g_age_los_inst_rec
804 index by binary_integer;
805 --
806 g_age_los_lookup ben_cache.g_cache_lookup_table;
807 g_age_los_instance g_age_los_inst_tbl;
808 g_age_los_out g_age_los_inst_tbl;
809 --
810 procedure get_rt_prfl_cache
811 (p_vrbl_rt_prfl_id in number
812 ,p_effective_date in date
813 ,p_lf_evt_ocrd_dt in date
814 ,p_business_group_id in number
815 ,p_inst_set in out NOCOPY ben_rt_prfl_cache.g_age_los_inst_tbl
816 ,p_inst_count out nocopy number);
817
818 ------------------------------------------------------------------------
819 -- TOTAL PARTICIPANTS
820 ------------------------------------------------------------------------
821 type g_ttl_prtt_inst_rec is record
822 (id number
823 ,vrbl_rt_prfl_id number(15)
824 ,mn_prtt_num number
825 ,mx_prtt_num number
826 );
827 --
828 type g_ttl_prtt_inst_tbl is table of
829 ben_rt_prfl_cache.g_ttl_prtt_inst_rec index by binary_integer;
830 --
831 g_ttl_prtt_lookup ben_cache.g_cache_lookup_table;
832 g_ttl_prtt_instance g_ttl_prtt_inst_tbl;
833 g_ttl_prtt_out g_ttl_prtt_inst_tbl;
834 --
835 procedure get_rt_prfl_cache
836 (p_vrbl_rt_prfl_id in number
837 ,p_effective_date in date
838 ,p_lf_evt_ocrd_dt in date
839 ,p_business_group_id in number
840 ,p_inst_set in out NOCOPY ben_rt_prfl_cache.g_ttl_prtt_inst_tbl
841 ,p_inst_count out nocopy number);
842 ------------------------------------------------------------------------
843 -- TOTAL COVERAGE
844 ------------------------------------------------------------------------
845 type g_ttl_cvg_inst_rec is record
846 (id number
847 ,vrbl_rt_prfl_id number(15)
848 ,mn_cvg_vol_amt number
849 ,mx_cvg_vol_amt number
850 );
851 --
852 type g_ttl_cvg_inst_tbl is table of
853 ben_rt_prfl_cache.g_ttl_cvg_inst_rec index by binary_integer;
854 --
855 g_ttl_cvg_lookup ben_cache.g_cache_lookup_table;
856 g_ttl_cvg_instance g_ttl_cvg_inst_tbl;
857 g_ttl_cvg_out g_ttl_cvg_inst_tbl;
858 --
859 procedure get_rt_prfl_cache
860 (p_vrbl_rt_prfl_id in number
861 ,p_effective_date in date
862 ,p_lf_evt_ocrd_dt in date
863 ,p_business_group_id in number
864 ,p_inst_set in out NOCOPY ben_rt_prfl_cache.g_ttl_cvg_inst_tbl
865 ,p_inst_count out nocopy number);
866
867
868 ------------------------------------------------------------------------
869 -- JOB
870 ------------------------------------------------------------------------
871 type g_job_inst_rec is record
872 (id number
873 ,vrbl_rt_prfl_id number(15)
874 ,job_id number(15)
875 ,excld_flag varchar2(30)
876 );
877 --
878 type g_job_inst_tbl is table of ben_rt_prfl_cache.g_job_inst_rec
879 index by binary_integer;
880 --
881 g_job_lookup ben_cache.g_cache_lookup_table;
882 g_job_instance g_job_inst_tbl;
883 g_job_out g_job_inst_tbl;
884 --
885 procedure get_rt_prfl_cache
886 (p_vrbl_rt_prfl_id in number
887 ,p_effective_date in date
888 ,p_lf_evt_ocrd_dt in date
889 ,p_business_group_id in number
890 ,p_inst_set in out NOCOPY ben_rt_prfl_cache.g_job_inst_tbl
891 ,p_inst_count out nocopy number);
892
893
894 ------------------------------------------------------------------------
895 -- OMR - Opted for Medicare
896 ------------------------------------------------------------------------
897 type g_optd_mdcr_inst_rec is record
898 (id number
899 ,vrbl_rt_prfl_id number(15)
900 ,optd_mdcr_flag varchar2(30)
901 );
902 --
903 type g_optd_mdcr_inst_tbl is table of ben_rt_prfl_cache.g_optd_mdcr_inst_rec
904 index by binary_integer;
905 --
906 g_optd_mdcr_lookup ben_cache.g_cache_lookup_table;
907 g_optd_mdcr_instance g_optd_mdcr_inst_tbl;
908 g_optd_mdcr_out g_optd_mdcr_inst_tbl;
909 --
910 procedure get_rt_prfl_cache
911 (p_vrbl_rt_prfl_id in number
912 ,p_effective_date in date
913 ,p_lf_evt_ocrd_dt in date
914 ,p_business_group_id in number
915 ,p_inst_set in out NOCOPY ben_rt_prfl_cache.g_optd_mdcr_inst_tbl
916 ,p_inst_count out nocopy number);
917
918 ------------------------------------------------------------------------
919 -- LRR - Leaving Reason
920 ------------------------------------------------------------------------
921 type g_lvg_rsn_inst_rec is record
922 (id number
923 ,vrbl_rt_prfl_id number(15)
924 ,lvg_rsn_cd varchar(30)
925 ,excld_flag varchar2(30)
926 );
927 --
928 type g_lvg_rsn_inst_tbl is table of ben_rt_prfl_cache.g_lvg_rsn_inst_rec
929 index by binary_integer;
930 --
931 g_lvg_rsn_lookup ben_cache.g_cache_lookup_table;
932 g_lvg_rsn_instance g_lvg_rsn_inst_tbl;
933 g_lvg_rsn_out g_lvg_rsn_inst_tbl;
934 --
935 procedure get_rt_prfl_cache
936 (p_vrbl_rt_prfl_id in number
937 ,p_effective_date in date
938 ,p_lf_evt_ocrd_dt in date
939 ,p_business_group_id in number
940 ,p_inst_set in out NOCOPY ben_rt_prfl_cache.g_lvg_rsn_inst_tbl
941 ,p_inst_count out nocopy number);
942
943
944 ------------------------------------------------------------------------
945 -- CQR Cobra Qualified Beneficiary
946 ------------------------------------------------------------------------
947 type g_cbr_qual_bnf_inst_rec is record
948 (id number
949 ,vrbl_rt_prfl_id number(15)
950 ,ptip_id number(15)
951 ,pgm_id number(15)
952 ,quald_bnf_flag varchar(30)
953 );
954 --
955 type g_cbr_qual_bnf_inst_tbl is table of ben_rt_prfl_cache.g_cbr_qual_bnf_inst_rec
956 index by binary_integer;
957 --
958 g_cbr_qual_bnf_lookup ben_cache.g_cache_lookup_table;
959 g_cbr_qual_bnf_instance g_cbr_qual_bnf_inst_tbl;
960 g_cbr_qual_bnf_out g_cbr_qual_bnf_inst_tbl;
961 --
962 procedure get_rt_prfl_cache
963 (p_vrbl_rt_prfl_id in number
964 ,p_effective_date in date
965 ,p_lf_evt_ocrd_dt in date
966 ,p_business_group_id in number
967 ,p_inst_set in out NOCOPY ben_rt_prfl_cache.g_cbr_qual_bnf_inst_tbl
968 ,p_inst_count out nocopy number);
969
970
971 ------------------------------------------------------------------------
972 -- CPP Continuing Participation Profile
973 ------------------------------------------------------------------------
974 type g_cntng_prtn_prfl_inst_rec is record
975 (id number
976 ,vrbl_rt_prfl_id number(15)
977 ,pymt_must_be_rcvd_uom varchar2(30)
978 ,pymt_must_be_rcvd_num number(15)
979 ,pymt_must_be_rcvd_rl number(15)
980 );
981 --
982 type g_cntng_prtn_prfl_inst_tbl is table of ben_rt_prfl_cache.g_cntng_prtn_prfl_inst_rec
983 index by binary_integer;
984 --
985 g_cntng_prtn_prfl_lookup ben_cache.g_cache_lookup_table;
986 g_cntng_prtn_prfl_instance g_cntng_prtn_prfl_inst_tbl;
987 g_cntng_prtn_prfl_out g_cntng_prtn_prfl_inst_tbl;
988 --
989 procedure get_rt_prfl_cache
990 (p_vrbl_rt_prfl_id in number
991 ,p_effective_date in date
992 ,p_lf_evt_ocrd_dt in date
993 ,p_business_group_id in number
994 ,p_inst_set in out NOCOPY ben_rt_prfl_cache.g_cntng_prtn_prfl_inst_tbl
995 ,p_inst_count out nocopy number);
996
997
998
999 ------------------------------------------------------------------------
1000 -- PSR Position
1001 ------------------------------------------------------------------------
1002 type g_pstn_inst_rec is record
1003 (id number
1004 ,vrbl_rt_prfl_id number(15)
1005 ,position_id number(15)
1006 ,excld_flag varchar2(30)
1007 );
1008 --
1009 type g_pstn_inst_tbl is table of ben_rt_prfl_cache.g_pstn_inst_rec
1010 index by binary_integer;
1011 --
1012 g_pstn_lookup ben_cache.g_cache_lookup_table;
1013 g_pstn_instance g_pstn_inst_tbl;
1014 g_pstn_out g_pstn_inst_tbl;
1015 --
1016 procedure get_rt_prfl_cache
1017 (p_vrbl_rt_prfl_id in number
1018 ,p_effective_date in date
1019 ,p_lf_evt_ocrd_dt in date
1020 ,p_business_group_id in number
1021 ,p_inst_set in out NOCOPY ben_rt_prfl_cache.g_pstn_inst_tbl
1022 ,p_inst_count out nocopy number);
1023
1024 ------------------------------------------------------------------------
1025 -- CTY Competency
1026 ------------------------------------------------------------------------
1027 type g_comptncy_inst_rec is record
1028 (id number
1029 ,vrbl_rt_prfl_id number(15)
1030 ,competence_id number(15)
1031 ,rating_level_id number(15)
1032 ,excld_flag varchar2(30)
1033 );
1034 --
1035 type g_comptncy_inst_tbl is table of ben_rt_prfl_cache.g_comptncy_inst_rec
1036 index by binary_integer;
1037 --
1038 g_comptncy_lookup ben_cache.g_cache_lookup_table;
1039 g_comptncy_instance g_comptncy_inst_tbl;
1040 g_comptncy_out g_comptncy_inst_tbl;
1041 --
1042 procedure get_rt_prfl_cache
1043 (p_vrbl_rt_prfl_id in number
1044 ,p_effective_date in date
1045 ,p_lf_evt_ocrd_dt in date
1046 ,p_business_group_id in number
1047 ,p_inst_set in out NOCOPY ben_rt_prfl_cache.g_comptncy_inst_tbl
1048 ,p_inst_count out nocopy number);
1049
1050
1051 ------------------------------------------------------------------------
1052 -- QTR Qualification Title
1053 ------------------------------------------------------------------------
1054 type g_qual_titl_inst_rec is record
1055 (id number
1056 ,vrbl_rt_prfl_id number(15)
1057 ,qualification_type_id number(15)
1058 ,title varchar2(120)
1059 ,excld_flag varchar2(30)
1060 );
1061 --
1062 type g_qual_titl_inst_tbl is table of ben_rt_prfl_cache.g_qual_titl_inst_rec
1063 index by binary_integer;
1064 --
1065 g_qual_titl_lookup ben_cache.g_cache_lookup_table;
1066 g_qual_titl_instance g_qual_titl_inst_tbl;
1067 g_qual_titl_out g_qual_titl_inst_tbl;
1068 --
1069 procedure get_rt_prfl_cache
1070 (p_vrbl_rt_prfl_id in number
1071 ,p_effective_date in date
1072 ,p_lf_evt_ocrd_dt in date
1073 ,p_business_group_id in number
1074 ,p_inst_set in out NOCOPY ben_rt_prfl_cache.g_qual_titl_inst_tbl
1075 ,p_inst_count out nocopy number);
1076
1077 ------------------------------------------------------------------------
1078 -- DCR Covered by Other Plan
1079 ------------------------------------------------------------------------
1080 type g_dpnt_cvrd_othr_pl_inst_rec is record
1081 (id number
1082 ,vrbl_rt_prfl_id number(15)
1083 ,pl_id number(15)
1084 ,cvg_det_dt_cd varchar2(30)
1085 ,excld_flag varchar2(30)
1086 );
1087 --
1088 type g_dpnt_cvrd_othr_pl_inst_tbl is table of ben_rt_prfl_cache.g_dpnt_cvrd_othr_pl_inst_rec
1089 index by binary_integer;
1090 --
1091 g_dpnt_cvrd_othr_pl_lookup ben_cache.g_cache_lookup_table;
1092 g_dpnt_cvrd_othr_pl_instance g_dpnt_cvrd_othr_pl_inst_tbl;
1093 g_dpnt_cvrd_othr_pl_out g_dpnt_cvrd_othr_pl_inst_tbl;
1094 --
1095 procedure get_rt_prfl_cache
1096 (p_vrbl_rt_prfl_id in number
1097 ,p_effective_date in date
1098 ,p_lf_evt_ocrd_dt in date
1099 ,p_business_group_id in number
1100 ,p_inst_set in out NOCOPY ben_rt_prfl_cache.g_dpnt_cvrd_othr_pl_inst_tbl
1101 ,p_inst_count out nocopy number);
1102
1103
1104 ------------------------------------------------------------------------
1105 -- DCP Covered by Other Plan in Program
1106 ------------------------------------------------------------------------
1107 type g_dpnt_cvrd_othr_plip_inst_rec is record
1108 (id number
1109 ,vrbl_rt_prfl_id number(15)
1110 ,plip_id number(15)
1111 ,enrl_det_dt_cd varchar2(30)
1112 ,excld_flag varchar2(30)
1113 );
1114 --
1115 type g_dpnt_cvrd_othr_plip_inst_tbl is table of ben_rt_prfl_cache.g_dpnt_cvrd_othr_plip_inst_rec
1116 index by binary_integer;
1117 --
1118 g_dpnt_cvrd_othr_plip_lookup ben_cache.g_cache_lookup_table;
1119 g_dpnt_cvrd_othr_plip_instance g_dpnt_cvrd_othr_plip_inst_tbl;
1120 g_dpnt_cvrd_othr_plip_out g_dpnt_cvrd_othr_plip_inst_tbl;
1121 --
1122 procedure get_rt_prfl_cache
1123 (p_vrbl_rt_prfl_id in number
1124 ,p_effective_date in date
1125 ,p_lf_evt_ocrd_dt in date
1126 ,p_business_group_id in number
1127 ,p_inst_set in out NOCOPY ben_rt_prfl_cache.g_dpnt_cvrd_othr_plip_inst_tbl
1128 ,p_inst_count out nocopy number);
1129
1130
1131 ------------------------------------------------------------------------
1132 -- DCO Covered by Other Plan Type in Program
1133 ------------------------------------------------------------------------
1134 type g_dpnt_cvrd_othr_ptip_inst_rec is record
1135 (id number
1136 ,vrbl_rt_prfl_id number(15)
1137 ,ptip_id number(15)
1138 ,enrl_det_dt_cd varchar2(30)
1139 ,excld_flag varchar2(30)
1140 ,only_pls_subj_cobra_flag varchar2(30)
1141 );
1142 --
1143 type g_dpnt_cvrd_othr_ptip_inst_tbl is table of ben_rt_prfl_cache.g_dpnt_cvrd_othr_ptip_inst_rec
1144 index by binary_integer;
1145 --
1146 g_dpnt_cvrd_othr_ptip_lookup ben_cache.g_cache_lookup_table;
1147 g_dpnt_cvrd_othr_ptip_instance g_dpnt_cvrd_othr_ptip_inst_tbl;
1148 g_dpnt_cvrd_othr_ptip_out g_dpnt_cvrd_othr_ptip_inst_tbl;
1149 --
1150 procedure get_rt_prfl_cache
1151 (p_vrbl_rt_prfl_id in number
1152 ,p_effective_date in date
1153 ,p_lf_evt_ocrd_dt in date
1154 ,p_business_group_id in number
1155 ,p_inst_set in out NOCOPY ben_rt_prfl_cache.g_dpnt_cvrd_othr_ptip_inst_tbl
1156 ,p_inst_count out nocopy number);
1157
1158
1159
1160 ------------------------------------------------------------------------
1161 -- DOP Covered by Other Program
1162 ------------------------------------------------------------------------
1163 type g_dpnt_cvrd_othr_pgm_inst_rec is record
1164 (id number
1165 ,vrbl_rt_prfl_id number(15)
1166 ,pgm_id number(15)
1167 ,enrl_det_dt_cd varchar2(30)
1168 ,excld_flag varchar2(30)
1169 ,only_pls_subj_cobra_flag varchar2(30)
1170 );
1171 --
1172 type g_dpnt_cvrd_othr_pgm_inst_tbl is table of ben_rt_prfl_cache.g_dpnt_cvrd_othr_pgm_inst_rec
1173 index by binary_integer;
1174 --
1175 g_dpnt_cvrd_othr_pgm_lookup ben_cache.g_cache_lookup_table;
1176 g_dpnt_cvrd_othr_pgm_instance g_dpnt_cvrd_othr_pgm_inst_tbl;
1177 g_dpnt_cvrd_othr_pgm_out g_dpnt_cvrd_othr_pgm_inst_tbl;
1178 --
1179 procedure get_rt_prfl_cache
1180 (p_vrbl_rt_prfl_id in number
1181 ,p_effective_date in date
1182 ,p_lf_evt_ocrd_dt in date
1183 ,p_business_group_id in number
1184 ,p_inst_set in out NOCOPY ben_rt_prfl_cache.g_dpnt_cvrd_othr_pgm_inst_tbl
1185 ,p_inst_count out nocopy number);
1186
1187
1188 ------------------------------------------------------------------------
1189 -- PAP Eligible for Another Plan
1190 ------------------------------------------------------------------------
1191 type g_prtt_anthr_pl_inst_rec is record
1192 (id number
1193 ,vrbl_rt_prfl_id number(15)
1194 ,pl_id number(15)
1195 ,excld_flag varchar2(30)
1196 );
1197 --
1198 type g_prtt_anthr_pl_inst_tbl is table of ben_rt_prfl_cache.g_prtt_anthr_pl_inst_rec
1199 index by binary_integer;
1200 --
1201 g_prtt_anthr_pl_lookup ben_cache.g_cache_lookup_table;
1202 g_prtt_anthr_pl_instance g_prtt_anthr_pl_inst_tbl;
1203 g_prtt_anthr_pl_out g_prtt_anthr_pl_inst_tbl;
1204 --
1205 procedure get_rt_prfl_cache
1206 (p_vrbl_rt_prfl_id in number
1207 ,p_effective_date in date
1208 ,p_lf_evt_ocrd_dt in date
1209 ,p_business_group_id in number
1210 ,p_inst_set in out NOCOPY ben_rt_prfl_cache.g_prtt_anthr_pl_inst_tbl
1211 ,p_inst_count out nocopy number);
1212
1213
1214 ------------------------------------------------------------------------
1215 -- OPR Eligible for Another Plan Type in Program
1216 ------------------------------------------------------------------------
1217 type g_othr_ptip_inst_rec is record
1218 (id number
1219 ,vrbl_rt_prfl_id number(15)
1220 ,ptip_id number(15)
1221 ,excld_flag varchar2(30)
1222 ,only_pls_subj_cobra_flag varchar2(30)
1223 );
1224 --
1225 type g_othr_ptip_inst_tbl is table of ben_rt_prfl_cache.g_othr_ptip_inst_rec
1226 index by binary_integer;
1227 --
1228 g_othr_ptip_lookup ben_cache.g_cache_lookup_table;
1229 g_othr_ptip_instance g_othr_ptip_inst_tbl;
1230 g_othr_ptip_out g_othr_ptip_inst_tbl;
1231 --
1232 procedure get_rt_prfl_cache
1233 (p_vrbl_rt_prfl_id in number
1234 ,p_effective_date in date
1235 ,p_lf_evt_ocrd_dt in date
1236 ,p_business_group_id in number
1237 ,p_inst_set in out NOCOPY ben_rt_prfl_cache.g_othr_ptip_inst_tbl
1238 ,p_inst_count out nocopy number);
1239
1240
1241 ------------------------------------------------------------------------
1242 -- ENL Enrolled Another Plan
1243 ------------------------------------------------------------------------
1244 type g_enrld_anthr_pl_inst_rec is record
1245 (id number
1246 ,vrbl_rt_prfl_id number(15)
1247 ,pl_id number(15)
1248 ,enrl_det_dt_cd varchar2(30)
1249 ,excld_flag varchar2(30)
1250 );
1251 --
1252 type g_enrld_anthr_pl_inst_tbl is table of ben_rt_prfl_cache.g_enrld_anthr_pl_inst_rec
1253 index by binary_integer;
1254 --
1255 g_enrld_anthr_pl_lookup ben_cache.g_cache_lookup_table;
1256 g_enrld_anthr_pl_instance g_enrld_anthr_pl_inst_tbl;
1257 g_enrld_anthr_pl_out g_enrld_anthr_pl_inst_tbl;
1258 --
1259 procedure get_rt_prfl_cache
1260 (p_vrbl_rt_prfl_id in number
1261 ,p_effective_date in date
1262 ,p_lf_evt_ocrd_dt in date
1263 ,p_business_group_id in number
1264 ,p_inst_set in out NOCOPY ben_rt_prfl_cache.g_enrld_anthr_pl_inst_tbl
1265 ,p_inst_count out nocopy number);
1266
1267
1268
1269 ------------------------------------------------------------------------
1270 -- EAO Enrolled Another Option in Plan
1271 ------------------------------------------------------------------------
1272 type g_enrld_anthr_oipl_inst_rec is record
1273 (id number
1274 ,vrbl_rt_prfl_id number(15)
1275 ,oipl_id number(15)
1276 ,enrl_det_dt_cd varchar2(30)
1277 ,excld_flag varchar2(30)
1278 );
1279 --
1280 type g_enrld_anthr_oipl_inst_tbl is table of ben_rt_prfl_cache.g_enrld_anthr_oipl_inst_rec
1281 index by binary_integer;
1282 --
1283 g_enrld_anthr_oipl_lookup ben_cache.g_cache_lookup_table;
1284 g_enrld_anthr_oipl_instance g_enrld_anthr_oipl_inst_tbl;
1285 g_enrld_anthr_oipl_out g_enrld_anthr_oipl_inst_tbl;
1286 --
1287 procedure get_rt_prfl_cache
1288 (p_vrbl_rt_prfl_id in number
1289 ,p_effective_date in date
1290 ,p_lf_evt_ocrd_dt in date
1291 ,p_business_group_id in number
1292 ,p_inst_set in out NOCOPY ben_rt_prfl_cache.g_enrld_anthr_oipl_inst_tbl
1293 ,p_inst_count out nocopy number);
1294
1295
1296 ------------------------------------------------------------------------
1297 -- EAR Enrolled Another Plan in Program
1298 ------------------------------------------------------------------------
1299 type g_enrld_anthr_plip_inst_rec is record
1300 (id number
1301 ,vrbl_rt_prfl_id number(15)
1302 ,plip_id number(15)
1303 ,enrl_det_dt_cd varchar2(30)
1304 ,excld_flag varchar2(30)
1305 );
1306 --
1307 type g_enrld_anthr_plip_inst_tbl is table of ben_rt_prfl_cache.g_enrld_anthr_plip_inst_rec
1308 index by binary_integer;
1309 --
1310 g_enrld_anthr_plip_lookup ben_cache.g_cache_lookup_table;
1311 g_enrld_anthr_plip_instance g_enrld_anthr_plip_inst_tbl;
1312 g_enrld_anthr_plip_out g_enrld_anthr_plip_inst_tbl;
1313 --
1314 procedure get_rt_prfl_cache
1315 (p_vrbl_rt_prfl_id in number
1316 ,p_effective_date in date
1317 ,p_lf_evt_ocrd_dt in date
1318 ,p_business_group_id in number
1319 ,p_inst_set in out NOCOPY ben_rt_prfl_cache.g_enrld_anthr_plip_inst_tbl
1320 ,p_inst_count out nocopy number);
1321
1322 ------------------------------------------------------------------------
1323 -- ENT Enrolled Another Plan Type in Program
1324 ------------------------------------------------------------------------
1325 type g_enrld_anthr_ptip_inst_rec is record
1326 (id number
1327 ,vrbl_rt_prfl_id number(15)
1328 ,ptip_id number(15)
1329 ,enrl_det_dt_cd varchar2(30)
1330 ,excld_flag varchar2(30)
1331 ,only_pls_subj_cobra_flag varchar2(30)
1332 );
1333 --
1334 type g_enrld_anthr_ptip_inst_tbl is table of ben_rt_prfl_cache.g_enrld_anthr_ptip_inst_rec
1335 index by binary_integer;
1336 --
1337 g_enrld_anthr_ptip_lookup ben_cache.g_cache_lookup_table;
1338 g_enrld_anthr_ptip_instance g_enrld_anthr_ptip_inst_tbl;
1339 g_enrld_anthr_ptip_out g_enrld_anthr_ptip_inst_tbl;
1340 --
1341 procedure get_rt_prfl_cache
1342 (p_vrbl_rt_prfl_id in number
1343 ,p_effective_date in date
1344 ,p_lf_evt_ocrd_dt in date
1345 ,p_business_group_id in number
1346 ,p_inst_set in out NOCOPY ben_rt_prfl_cache.g_enrld_anthr_ptip_inst_tbl
1347 ,p_inst_count out nocopy number);
1348
1349
1350 ------------------------------------------------------------------------
1351 -- EAG Enrolled Another Program
1352 ------------------------------------------------------------------------
1353 type g_enrld_anthr_pgm_inst_rec is record
1354 (id number
1355 ,vrbl_rt_prfl_id number(15)
1356 ,pgm_id number(15)
1357 ,enrl_det_dt_cd varchar2(30)
1358 ,excld_flag varchar2(30)
1359 );
1360 --
1361 type g_enrld_anthr_pgm_inst_tbl is table of ben_rt_prfl_cache.g_enrld_anthr_pgm_inst_rec
1362 index by binary_integer;
1363 --
1364 g_enrld_anthr_pgm_lookup ben_cache.g_cache_lookup_table;
1365 g_enrld_anthr_pgm_instance g_enrld_anthr_pgm_inst_tbl;
1366 g_enrld_anthr_pgm_out g_enrld_anthr_pgm_inst_tbl;
1367 --
1368 procedure get_rt_prfl_cache
1369 (p_vrbl_rt_prfl_id in number
1370 ,p_effective_date in date
1371 ,p_lf_evt_ocrd_dt in date
1372 ,p_business_group_id in number
1373 ,p_inst_set in out NOCOPY ben_rt_prfl_cache.g_enrld_anthr_pgm_inst_tbl
1374 ,p_inst_count out nocopy number);
1375
1376 ------------------------------------------------------------------------
1377 -- DOT Dependent eligible for another plan type in program
1378 ------------------------------------------------------------------------
1379 type g_dpnt_othr_ptip_inst_rec is record
1380 (id number
1381 ,vrbl_rt_prfl_id number(15)
1382 ,ptip_id number(15)
1383 ,excld_flag varchar2(30)
1384 );
1385 --
1386 type g_dpnt_othr_ptip_inst_tbl is table of ben_rt_prfl_cache.g_dpnt_othr_ptip_inst_rec
1387 index by binary_integer;
1388 --
1389 g_dpnt_othr_ptip_lookup ben_cache.g_cache_lookup_table;
1390 g_dpnt_othr_ptip_instance g_dpnt_othr_ptip_inst_tbl;
1391 g_dpnt_othr_ptip_out g_dpnt_othr_ptip_inst_tbl;
1392 --
1393 procedure get_rt_prfl_cache
1394 (p_vrbl_rt_prfl_id in number
1395 ,p_effective_date in date
1396 ,p_lf_evt_ocrd_dt in date
1397 ,p_business_group_id in number
1398 ,p_inst_set in out NOCOPY ben_rt_prfl_cache.g_dpnt_othr_ptip_inst_tbl
1399 ,p_inst_count out nocopy number);
1400
1401 ------------------------------------------------------------------------
1402 -- NOC No Other Coverage
1403 ------------------------------------------------------------------------
1404 type g_no_othr_cvg_inst_rec is record
1405 (id number
1406 ,vrbl_rt_prfl_id number(15)
1407 ,coord_ben_no_cvg_flag varchar2(30)
1408 );
1409 --
1410 type g_no_othr_cvg_inst_tbl is table of ben_rt_prfl_cache.g_no_othr_cvg_inst_rec
1411 index by binary_integer;
1412 --
1413 g_no_othr_cvg_lookup ben_cache.g_cache_lookup_table;
1414 g_no_othr_cvg_instance g_no_othr_cvg_inst_tbl;
1415 g_no_othr_cvg_out g_no_othr_cvg_inst_tbl;
1416 --
1417 procedure get_rt_prfl_cache
1418 (p_vrbl_rt_prfl_id in number
1419 ,p_effective_date in date
1420 ,p_lf_evt_ocrd_dt in date
1421 ,p_business_group_id in number
1422 ,p_inst_set in out NOCOPY ben_rt_prfl_cache.g_no_othr_cvg_inst_tbl
1423 ,p_inst_count out nocopy number);
1424
1425
1426 ------------------------------------------------------------------------
1427 -- Quartile in Grade
1428 ------------------------------------------------------------------------
1429 type g_qua_in_gr_inst_rec is record
1430 (id number
1431 ,vrbl_rt_prfl_id number(15)
1432 ,quar_in_grade_cd varchar2(30)
1433 ,excld_flag varchar2(30)
1434 );
1435 --
1436 type g_qua_in_gr_inst_tbl is table of ben_rt_prfl_cache.g_qua_in_gr_inst_rec
1437 index by binary_integer;
1438 --
1439 g_qua_in_gr_lookup ben_cache.g_cache_lookup_table;
1440 g_qua_in_gr_instance g_qua_in_gr_inst_tbl;
1441 g_qua_in_gr_out g_qua_in_gr_inst_tbl;
1442 --
1443 procedure get_rt_prfl_cache
1444 (p_vrbl_rt_prfl_id in number
1445 ,p_effective_date in date
1446 ,p_lf_evt_ocrd_dt in date
1447 ,p_business_group_id in number
1448 ,p_inst_set in out NOCOPY ben_rt_prfl_cache.g_qua_in_gr_inst_tbl
1449 ,p_inst_count out nocopy number);
1450
1451
1452 ------------------------------------------------------------------------
1453 -- Performance Rating
1454 ------------------------------------------------------------------------
1455 type g_perf_rtng_inst_rec is record
1456 (id number
1457 ,vrbl_rt_prfl_id number(15)
1458 ,perf_rtng_cd varchar2(30)
1459 ,event_type varchar2(30)
1460 ,excld_flag varchar2(30)
1461 );
1462 --
1463 type g_perf_rtng_inst_tbl is table of ben_rt_prfl_cache.g_perf_rtng_inst_rec
1464 index by binary_integer;
1465 --
1466 g_perf_rtng_lookup ben_cache.g_cache_lookup_table;
1467 g_perf_rtng_instance g_perf_rtng_inst_tbl;
1468 g_perf_rtng_out g_perf_rtng_inst_tbl;
1469 --
1470 procedure get_rt_prfl_cache
1471 (p_vrbl_rt_prfl_id in number
1472 ,p_effective_date in date
1473 ,p_lf_evt_ocrd_dt in date
1474 ,p_business_group_id in number
1475 ,p_inst_set in out NOCOPY ben_rt_prfl_cache.g_perf_rtng_inst_tbl
1476 ,p_inst_count out nocopy number);
1477
1478 ------------------------------------------------------------------------
1479 -- DELETE CACHED DATA
1480 ------------------------------------------------------------------------
1481 procedure clear_down_cache;
1482 --
1483 END ben_rt_prfl_cache;
[Home] [Help]