[Home] [Help]
PACKAGE: APPS.BEN_ELP_CACHE
Source
1 PACKAGE ben_elp_cache AUTHID CURRENT_USER AS
2 /* $Header: benelpch.pkh 120.0 2005/05/28 08:57:43 appldev noship $ */
3 type g_cobcep_cache_rec is record
4 (id number
5 ,pgm_id ben_prtn_elig_f.pgm_id%type
6 ,ptip_id ben_prtn_elig_f.ptip_id%type
7 ,plip_id ben_prtn_elig_f.plip_id%type
8 ,pl_id ben_prtn_elig_f.pl_id%type
9 ,oipl_id ben_prtn_elig_f.oipl_id%type
10 ,prtn_elig_id ben_prtn_elig_f.prtn_elig_id%type
11 ,mndtry_flag ben_prtn_elig_prfl_f.mndtry_flag%type
12 ,eligy_prfl_id ben_eligy_prfl_f.eligy_prfl_id%type
13 ,asmt_to_use_cd ben_eligy_prfl_f.asmt_to_use_cd%type
14 ,elig_enrld_plip_flag varchar2(30)
15 ,elig_cbr_quald_bnf_flag varchar2(30)
16 ,elig_enrld_ptip_flag varchar2(30)
17 ,elig_dpnt_cvrd_plip_flag varchar2(30)
18 ,elig_dpnt_cvrd_ptip_flag varchar2(30)
19 ,elig_dpnt_cvrd_pgm_flag varchar2(30)
20 ,elig_job_flag varchar2(30)
21 ,elig_hrly_slrd_flag varchar2(30)
22 ,elig_pstl_cd_flag varchar2(30)
23 ,elig_lbr_mmbr_flag varchar2(30)
24 ,elig_lgl_enty_flag varchar2(30)
25 ,elig_benfts_grp_flag varchar2(30)
26 ,elig_wk_loc_flag varchar2(30)
27 ,elig_brgng_unit_flag varchar2(30)
28 ,elig_age_flag varchar2(30)
29 ,elig_los_flag varchar2(30)
30 ,elig_per_typ_flag varchar2(30)
31 ,elig_fl_tm_pt_tm_flag varchar2(30)
32 ,elig_ee_stat_flag varchar2(30)
33 ,elig_grd_flag varchar2(30)
34 ,elig_pct_fl_tm_flag varchar2(30)
35 ,elig_asnt_set_flag varchar2(30)
36 ,elig_hrs_wkd_flag varchar2(30)
37 ,elig_comp_lvl_flag varchar2(30)
38 ,elig_org_unit_flag varchar2(30)
39 ,elig_loa_rsn_flag varchar2(30)
40 ,elig_pyrl_flag varchar2(30)
41 ,elig_schedd_hrs_flag varchar2(30)
42 ,elig_py_bss_flag varchar2(30)
43 ,eligy_prfl_rl_flag varchar2(30)
44 ,elig_cmbn_age_los_flag varchar2(30)
45 ,cntng_prtn_elig_prfl_flag varchar2(30)
46 ,elig_prtt_pl_flag varchar2(30)
47 ,elig_ppl_grp_flag varchar2(30)
48 ,elig_svc_area_flag varchar2(30)
49 ,elig_ptip_prte_flag varchar2(30)
50 ,elig_no_othr_cvg_flag varchar2(30)
51 ,elig_enrld_pl_flag varchar2(30)
52 ,elig_enrld_oipl_flag varchar2(30)
53 ,elig_enrld_pgm_flag varchar2(30)
54 ,elig_dpnt_cvrd_pl_flag varchar2(30)
55 ,elig_lvg_rsn_flag varchar2(30)
56 ,elig_optd_mdcr_flag varchar2(30)
57 ,elig_tbco_use_flag varchar2(30)
58 ,elig_dpnt_othr_ptip_flag varchar2(30)
59 ,elig_gndr_flag varchar2(30)
60 ,elig_mrtl_sts_flag varchar2(30)
61 ,elig_dsblty_ctg_flag varchar2(30)
62 ,elig_dsblty_rsn_flag varchar2(30)
63 ,elig_dsblty_dgr_flag varchar2(30)
64 ,elig_suppl_role_flag varchar2(30)
65 ,elig_qual_titl_flag varchar2(30)
66 ,elig_pstn_flag varchar2(30)
67 ,elig_prbtn_perd_flag varchar2(30)
68 ,elig_sp_clng_prg_pt_flag varchar2(30)
69 ,bnft_cagr_prtn_cd varchar2(30)
70 ---
71 ,elig_dsbld_flag varchar2(30)
72 ,elig_ttl_cvg_vol_flag varchar2(30)
73 ,elig_ttl_prtt_flag varchar2(30)
74 ,elig_comptncy_flag varchar2(30)
75 ,elig_hlth_cvg_flag varchar2(30)
76 ,elig_anthr_pl_flag varchar2(30)
77 ,elig_qua_in_gr_flag varchar2(30)
78 ,elig_perf_rtng_flag varchar2(30)
79 );
80 --
81 -- Comp object
82 --
83 type g_cobcep_cache is table of g_cobcep_cache_rec
84 index by binary_integer;
85 --
86 procedure cobcep_getdets
87 (p_business_group_id in number
88 ,p_effective_date in date
89 ,p_pgm_id in number default hr_api.g_number
90 ,p_pl_id in number default hr_api.g_number
91 ,p_oipl_id in number default hr_api.g_number
92 ,p_plip_id in number default hr_api.g_number
93 ,p_ptip_id in number default hr_api.g_number
94 --
95 ,p_inst_set out nocopy ben_elp_cache.g_cobcep_cache
96 ,p_inst_count out nocopy number
97 );
98 --
99 type g_cobcep_odcache is varray(1000000) of g_cobcep_cache_rec;
100 --
101 procedure cobcep_odgetdets
102 (p_effective_date in date
103 ,p_pgm_id in number default hr_api.g_number
104 ,p_pl_id in number default hr_api.g_number
105 ,p_oipl_id in number default hr_api.g_number
106 ,p_plip_id in number default hr_api.g_number
107 ,p_ptip_id in number default hr_api.g_number
108 --
109 ,p_inst_set in out nocopy ben_elp_cache.g_cobcep_odcache
110 );
111 --
112 type g_elpelc_cache_rec is record
113 (eligy_prfl_id number
114 ,pk_id number
115 ,short_code varchar2(30)
116 ,code varchar2(100)
117 ,id number
118 ,id1 number
119 ,from_value varchar2(100)
120 ,to_value varchar2(100)
121 ,mx_num number
122 ,mn_num number
123 ,no_mx_num_apls_flag varchar2(100)
124 ,no_mn_num_apls_flag varchar2(100)
125 ,cmbnd_min_val number
126 ,cmbnd_max_val number
127 ,excld_flag varchar2(100)
128 ,criteria_score number
129 ,criteria_weight number
130 );
131 --
132 type g_elpelc_cache is table of g_elpelc_cache_rec
133 index by binary_integer;
134 --
135 g_elpept_lookup ben_cache.g_cache_lookup_table;
136 g_elpept_inst ben_elp_cache.g_elpelc_cache;
137 g_elpees_lookup ben_cache.g_cache_lookup_table;
138 g_elpees_inst ben_elp_cache.g_elpelc_cache;
139 g_elpesa_lookup ben_cache.g_cache_lookup_table;
140 g_elpesa_inst ben_elp_cache.g_elpelc_cache;
141 g_elpehs_lookup ben_cache.g_cache_lookup_table;
142 g_elpehs_inst ben_elp_cache.g_elpelc_cache;
143 g_elpels_lookup ben_cache.g_cache_lookup_table;
144 g_elpels_inst ben_elp_cache.g_elpelc_cache;
145 g_elpecp_lookup ben_cache.g_cache_lookup_table;
146 g_elpecp_inst ben_elp_cache.g_elpelc_cache;
147 --
148 procedure elpelc_getdets
149 (p_business_group_id in number
150 ,p_effective_date in date
151 ,p_eligy_prfl_id in number default hr_api.g_number
152 ,p_cache_code in varchar2 default hr_api.g_varchar2
153 --
154 ,p_inst_set out nocopy ben_elp_cache.g_elpelc_cache
155 ,p_inst_count out nocopy number
156 )
157 ;
158
159 --
160 -- eligibility profile person type by eligibility profile
161 --
162 type g_cache_elpesa_object_rec is record
163 (eligy_prfl_id ben_eligy_prfl_f.eligy_prfl_id%type,
164 pk_id number,
165 short_code varchar2(30),
166 criteria_score ben_elig_svc_area_prte_f.criteria_score%type,
167 criteria_weight ben_elig_svc_area_prte_f.criteria_weight%type,
168 from_value ben_pstl_zip_rng_f.from_value%type,
169 to_value ben_pstl_zip_rng_f.to_value%type,
170 excld_flag ben_elig_svc_area_prte_f.excld_flag%type);
171 --
172 type g_cache_elpesa_instor is table of g_cache_elpesa_object_rec
173 index by binary_integer;
174 --
175 -- eligibility profile person type by eligibility profile
176 --
177 type g_cache_elpept_object_rec is record
178 (eligy_prfl_id ben_eligy_prfl_f.eligy_prfl_id%type,
179 pk_id number,
180 short_code varchar2(30),
181 criteria_score ben_elig_per_typ_prte_f.criteria_score%type,
182 criteria_weight ben_elig_per_typ_prte_f.criteria_weight%type,
183 -- per_typ_cd ben_elig_per_typ_prte_f.per_typ_cd%type,
184 -- Not supporting per_typ_cd, instead use person_typ_id
185 person_type_id ben_elig_per_typ_prte_f.person_type_id%type,
186 excld_flag ben_elig_per_typ_prte_f.excld_flag%type);
187 --
188 type g_cache_elpept_instor is table of g_cache_elpept_object_rec
189 index by binary_integer;
190 --
191 -- eligibility profile people group by eligibility profile
192 --
193 type g_cache_elpepg_object_rec is record
194 (eligy_prfl_id ben_eligy_prfl_f.eligy_prfl_id%type,
195 pk_id number,
196 short_code varchar2(30),
197 people_group_id ben_elig_ppl_grp_prte_f.people_group_id%type,
198 excld_flag ben_elig_ppl_grp_prte_f.excld_flag%type,
199 criteria_score ben_elig_ppl_grp_prte_f.criteria_score%type,
200 criteria_weight ben_elig_ppl_grp_prte_f.criteria_weight%type,
201 segment1 varchar2(60),
202 segment2 varchar2(60),
203 segment3 varchar2(60),
204 segment4 varchar2(60),
205 segment5 varchar2(60),
206 segment6 varchar2(60),
207 segment7 varchar2(60),
208 segment8 varchar2(60),
209 segment9 varchar2(60),
210 segment10 varchar2(60),
211 segment11 varchar2(60),
212 segment12 varchar2(60),
213 segment13 varchar2(60),
214 segment14 varchar2(60),
215 segment15 varchar2(60),
216 segment16 varchar2(60),
217 segment17 varchar2(60),
218 segment18 varchar2(60),
219 segment19 varchar2(60),
220 segment20 varchar2(60),
221 segment21 varchar2(60),
222 segment22 varchar2(60),
223 segment23 varchar2(60),
224 segment24 varchar2(60),
225 segment25 varchar2(60),
226 segment26 varchar2(60),
227 segment27 varchar2(60),
228 segment28 varchar2(60),
229 segment29 varchar2(60),
230 segment30 varchar2(60) );
231 --
232 type g_cache_elpepg_instor is table of g_cache_elpepg_object_rec
233 index by binary_integer;
234 --
235 -- eligibility profile assignment status type by eligibility profile
236 --
237 type g_cache_elpees_object_rec is record
238 (eligy_prfl_id ben_eligy_prfl_f.eligy_prfl_id%type,
239 pk_id number,
240 short_code varchar2(30),
241 assignment_status_type_id ben_elig_ee_stat_prte_f.
242 assignment_status_type_id%type,
243 excld_flag ben_elig_ee_stat_prte_f.excld_flag%type,
244 criteria_score ben_elig_ee_stat_prte_f.criteria_score%type,
245 criteria_weight ben_elig_ee_stat_prte_f.criteria_weight%type
246 );
247 --
248 type g_cache_elpees_instor is table of g_cache_elpees_object_rec
249 index by binary_integer;
250 --
251 -- eligibility profile length of service by eligibility profile
252 --
253 type g_cache_elpels_object_rec is record
254 (eligy_prfl_id ben_eligy_prfl_f.eligy_prfl_id%type,
255 pk_id number,
256 short_code varchar2(30),
257 excld_flag ben_elig_los_prte_f.excld_flag%type,
258 criteria_score ben_elig_los_prte_f.criteria_score%type,
259 criteria_weight ben_elig_los_prte_f.criteria_weight%type,
260 mx_los_num ben_los_fctr.mx_los_num%type,
261 mn_los_num ben_los_fctr.mn_los_num%type,
262 no_mx_los_num_apls_flag ben_los_fctr.no_mx_los_num_apls_flag%type,
263 no_mn_los_num_apls_flag ben_los_fctr.no_mn_los_num_apls_flag%type,
264 los_fctr_id ben_los_fctr.los_fctr_id%type);
265 --
266 type g_cache_elpels_instor is table of g_cache_elpels_object_rec
267 index by binary_integer;
268 --
269 -- eligibility profile age/los combination by eligibility profile
270 --
271 type g_cache_elpecp_object_rec is record
272 (eligy_prfl_id ben_eligy_prfl_f.eligy_prfl_id%type,
273 pk_id number,
274 short_code varchar2(30),
275 excld_flag ben_elig_cmbn_age_los_prte_f.excld_flag%type,
276 criteria_score ben_elig_cmbn_age_los_prte_f.criteria_score%type,
277 criteria_weight ben_elig_cmbn_age_los_prte_f.criteria_weight%type,
278 cmbnd_min_val ben_cmbn_age_los_fctr.cmbnd_min_val%type,
279 cmbnd_max_val ben_cmbn_age_los_fctr.cmbnd_max_val%type,
280 los_fctr_id ben_cmbn_age_los_fctr.los_fctr_id%type,
281 age_fctr_id ben_cmbn_age_los_fctr.age_fctr_id%type,
282 cmbn_age_los_fctr_id ben_cmbn_age_los_fctr.cmbn_age_los_fctr_id%type);
283 --
284 type g_cache_elpecp_instor is table of g_cache_elpecp_object_rec
285 index by binary_integer;
286 --
287 -- eligibility profile location by eligibility profile
288 --
289 type g_cache_elpewl_object_rec is record
290 (eligy_prfl_id ben_eligy_prfl_f.eligy_prfl_id%type,
291 pk_id number,
292 short_code varchar2(30),
293 location_id ben_elig_wk_loc_prte_f.location_id%type,
294 excld_flag ben_elig_wk_loc_prte_f.excld_flag%type,
295 criteria_score ben_elig_wk_loc_prte_f.criteria_score%type,
296 criteria_weight ben_elig_wk_loc_prte_f.criteria_weight%type);
297 --
298 type g_cache_elpewl_instor is table of g_cache_elpewl_object_rec
299 index by binary_integer;
300 --
301 -- eligibility profile organization by eligibility profile
302 --
303 type g_cache_elpeou_object_rec is record
304 (eligy_prfl_id ben_eligy_prfl_f.eligy_prfl_id%type,
305 pk_id number,
306 short_code varchar2(30),
307 organization_id ben_elig_org_unit_prte_f.organization_id%type,
308 excld_flag ben_elig_org_unit_prte_f.excld_flag%type,
309 criteria_score ben_elig_org_unit_prte_f.criteria_score%type,
310 criteria_weight ben_elig_org_unit_prte_f.criteria_weight%type);
311 --
312 type g_cache_elpeou_instor is table of g_cache_elpeou_object_rec
313 index by binary_integer;
314 --
315 -- eligibility profile pay frequency by eligibility profile
316 --
317 type g_cache_elpehs_object_rec is record
318 (eligy_prfl_id ben_eligy_prfl_f.eligy_prfl_id%type,
319 pk_id number,
320 short_code varchar2(30),
321 hrly_slrd_cd ben_elig_hrly_slrd_prte_f.hrly_slrd_cd%type,
322 excld_flag ben_elig_hrly_slrd_prte_f.excld_flag%type,
323 criteria_score ben_elig_hrly_slrd_prte_f.criteria_score%type,
324 criteria_weight ben_elig_hrly_slrd_prte_f.criteria_weight%type);
325 --
326 type g_cache_elpehs_instor is table of g_cache_elpehs_object_rec
327 index by binary_integer;
328 --
329 -- eligibility profile full/part time by eligibility profile
330 --
331 type g_cache_elpefp_object_rec is record
332 (eligy_prfl_id ben_eligy_prfl_f.eligy_prfl_id%type,
333 pk_id number,
334 short_code varchar2(30),
335 fl_tm_pt_tm_cd ben_elig_fl_tm_pt_tm_prte_f.fl_tm_pt_tm_cd%type,
336 excld_flag ben_elig_fl_tm_pt_tm_prte_f.excld_flag%type,
337 criteria_score ben_elig_fl_tm_pt_tm_prte_f.criteria_score%type,
338 criteria_weight ben_elig_fl_tm_pt_tm_prte_f.criteria_weight%type);
339 --
340 type g_cache_elpefp_instor is table of g_cache_elpefp_object_rec
341 index by binary_integer;
342 --
343 -- eligibility profile rules by eligibility profile
344 --
345 type g_cache_elperl_object_rec is record
346 (eligy_prfl_id ben_eligy_prfl_f.eligy_prfl_id%type,
347 pk_id number,
348 short_code varchar2(30),
349 formula_id ben_eligy_prfl_rl_f.formula_id%type,
350 criteria_score ben_eligy_prfl_rl_f.criteria_score%type,
351 criteria_weight ben_eligy_prfl_rl_f.criteria_weight%type);
352 --
353 type g_cache_elperl_instor is table of g_cache_elperl_object_rec
354 index by binary_integer;
355 --
356 -- eligibility profile scheduled hours by eligibility profile
357 --
358 type g_cache_elpesh_object_rec is record
359 (eligy_prfl_id ben_eligy_prfl_f.eligy_prfl_id%type,
360 pk_id number,
361 short_code varchar2(30),
362 hrs_num ben_elig_schedd_hrs_prte_f.hrs_num%type,
363 determination_cd ben_elig_schedd_hrs_prte_f.determination_cd%type,
364 determination_rl ben_elig_schedd_hrs_prte_f.determination_rl%type,
365 rounding_cd ben_elig_schedd_hrs_prte_f.rounding_cd%type,
366 rounding_rl ben_elig_schedd_hrs_prte_f.rounding_rl%type,
367 max_hrs_num ben_elig_schedd_hrs_prte_f.max_hrs_num%type,
368 schedd_hrs_rl ben_elig_schedd_hrs_prte_f.schedd_hrs_rl%type,
369 freq_cd ben_elig_schedd_hrs_prte_f.freq_cd%type,
370 excld_flag ben_elig_schedd_hrs_prte_f.excld_flag%type,
371 criteria_score ben_elig_schedd_hrs_prte_f.criteria_score%type,
372 criteria_weight ben_elig_schedd_hrs_prte_f.criteria_weight%type);
373 --
374 type g_cache_elpesh_instor is table of g_cache_elpesh_object_rec
375 index by binary_integer;
376 --
377 -- eligibility profile compensation level by eligibility profile
378 --
379 type g_cache_elpecl_object_rec is record
380 (eligy_prfl_id ben_eligy_prfl_f.eligy_prfl_id%type,
381 pk_id number,
382 short_code varchar2(30),
383 excld_flag ben_elig_comp_lvl_prte_f.excld_flag%type,
384 criteria_score ben_elig_comp_lvl_prte_f.criteria_score%type,
385 criteria_weight ben_elig_comp_lvl_prte_f.criteria_weight%type,
386 mn_comp_val ben_comp_lvl_fctr.mn_comp_val%type,
387 mx_comp_val ben_comp_lvl_fctr.mx_comp_val%type,
388 no_mn_comp_flag ben_comp_lvl_fctr.no_mn_comp_flag%type,
389 no_mx_comp_flag ben_comp_lvl_fctr.no_mx_comp_flag%type,
390 comp_src_cd ben_comp_lvl_fctr.comp_src_cd%type,
391 comp_lvl_fctr_id ben_comp_lvl_fctr.comp_lvl_fctr_id%type
392 );
393 --
394 type g_cache_elpecl_instor is table of g_cache_elpecl_object_rec
395 index by binary_integer;
396 --
397 -- eligibility profile hours worked by eligibility profile
398 --
399 type g_cache_elpehw_object_rec is record
400 (eligy_prfl_id ben_eligy_prfl_f.eligy_prfl_id%type,
401 pk_id number,
402 short_code varchar2(30),
403 hrs_wkd_in_perd_fctr_id ben_elig_hrs_wkd_prte_f.hrs_wkd_in_perd_fctr_id%type,
404 excld_flag ben_elig_hrs_wkd_prte_f.excld_flag%type,
405 criteria_score ben_elig_hrs_wkd_prte_f.criteria_score%type,
406 criteria_weight ben_elig_hrs_wkd_prte_f.criteria_weight%type,
407 mn_hrs_num ben_hrs_wkd_in_perd_fctr.mn_hrs_num%type,
408 mx_hrs_num ben_hrs_wkd_in_perd_fctr.mx_hrs_num%type,
409 no_mn_hrs_wkd_flag ben_hrs_wkd_in_perd_fctr.no_mn_hrs_wkd_flag%type,
410 no_mx_hrs_wkd_flag ben_hrs_wkd_in_perd_fctr.no_mx_hrs_wkd_flag%type,
411 hrs_src_cd ben_hrs_wkd_in_perd_fctr.hrs_src_cd%type);
412 --
413 type g_cache_elpehw_instor is table of g_cache_elpehw_object_rec
414 index by binary_integer;
415 --
416 -- eligibility profile hours worked by eligibility profile
417 --
418 type g_cache_elpean_object_rec is record
419 (eligy_prfl_id ben_eligy_prfl_f.eligy_prfl_id%type,
420 pk_id number,
421 short_code varchar2(30),
422 excld_flag ben_elig_hrs_wkd_prte_f.excld_flag%type,
423 criteria_score ben_elig_hrs_wkd_prte_f.criteria_score%type,
424 criteria_weight ben_elig_hrs_wkd_prte_f.criteria_weight%type,
425 formula_id hr_assignment_sets.formula_id%type);
426 --
427 type g_cache_elpean_instor is table of g_cache_elpean_object_rec
428 index by binary_integer;
429 --
430 -- eligibility profile full time by eligibility profile
431 --
432 type g_cache_elpepf_object_rec is record
433 (eligy_prfl_id ben_eligy_prfl_f.eligy_prfl_id%type,
434 pk_id number,
435 short_code varchar2(30),
436 pct_fl_tm_fctr_id ben_elig_pct_fl_tm_prte_f.pct_fl_tm_fctr_id%type,
437 excld_flag ben_elig_pct_fl_tm_prte_f.excld_flag%type,
438 criteria_score ben_elig_pct_fl_tm_prte_f.criteria_score%type,
439 criteria_weight ben_elig_pct_fl_tm_prte_f.criteria_weight%type,
440 mx_pct_val ben_pct_fl_tm_fctr.mx_pct_val%type,
441 mn_pct_val ben_pct_fl_tm_fctr.mn_pct_val%type,
442 no_mn_pct_val_flag ben_pct_fl_tm_fctr.no_mn_pct_val_flag%type,
443 no_mx_pct_val_flag ben_pct_fl_tm_fctr.no_mx_pct_val_flag%type);
444 --
445 type g_cache_elpepf_instor is table of g_cache_elpepf_object_rec
446 index by binary_integer;
447 --
448 -- eligibility profile grade by eligibility profile
449 --
450 type g_cache_elpegr_object_rec is record
451 (eligy_prfl_id ben_eligy_prfl_f.eligy_prfl_id%type,
452 pk_id number,
453 short_code varchar2(30),
454 grade_id ben_elig_grd_prte_f.grade_id%type,
455 excld_flag ben_elig_grd_prte_f.excld_flag%type,
456 criteria_score ben_elig_grd_prte_f.criteria_score%type,
457 criteria_weight ben_elig_grd_prte_f.criteria_weight%type);
458 --
459 type g_cache_elpegr_instor is table of g_cache_elpegr_object_rec
460 index by binary_integer;
461 --
462 -- eligibility profile based on person's sex
463 --
464 type g_cache_elpegn_object_rec is record
465 (eligy_prfl_id ben_eligy_prfl_f.eligy_prfl_id%type,
466 pk_id number,
467 short_code varchar2(30),
468 sex ben_elig_gndr_prte_f.sex%type,
469 excld_flag ben_elig_gndr_prte_f.excld_flag%type,
470 criteria_score ben_elig_gndr_prte_f.criteria_score%type,
471 criteria_weight ben_elig_gndr_prte_f.criteria_weight%type);
472 --
473 type g_cache_elpegn_instor is table of g_cache_elpegn_object_rec
474 index by binary_integer;
475 --
476 -- eligibility profile job by eligibility profile
477 --
478 type g_cache_elpejp_object_rec is record
479 (eligy_prfl_id ben_eligy_prfl_f.eligy_prfl_id%type,
480 pk_id number,
481 short_code varchar2(30),
482 job_id ben_elig_job_prte_f.job_id%type,
483 excld_flag ben_elig_job_prte_f.excld_flag%type,
484 criteria_score ben_elig_job_prte_f.criteria_score%type,
485 criteria_weight ben_elig_job_prte_f.criteria_weight%type);
486 --
487 type g_cache_elpejp_instor is table of g_cache_elpejp_object_rec
488 index by binary_integer;
489 --
490 -- eligibility profile pay basis by eligibility profile
491 --
492 type g_cache_elpepb_object_rec is record
493 (eligy_prfl_id ben_eligy_prfl_f.eligy_prfl_id%type,
494 pk_id number,
495 short_code varchar2(30),
496 pay_basis_id ben_elig_py_bss_prte_f.pay_basis_id%type,
497 excld_flag ben_elig_py_bss_prte_f.excld_flag%type,
498 criteria_score ben_elig_py_bss_prte_f.criteria_score%type,
499 criteria_weight ben_elig_py_bss_prte_f.criteria_weight%type);
500 --
501 type g_cache_elpepb_instor is table of g_cache_elpepb_object_rec
502 index by binary_integer;
503 --
504 -- eligibility profile payroll by eligibility profile
505 --
506 type g_cache_elpepy_object_rec is record
507 (eligy_prfl_id ben_eligy_prfl_f.eligy_prfl_id%type,
508 pk_id number,
509 short_code varchar2(30),
510 payroll_id ben_elig_pyrl_prte_f.payroll_id%type,
511 excld_flag ben_elig_pyrl_prte_f.excld_flag%type,
512 criteria_score ben_elig_pyrl_prte_f.criteria_score%type,
513 criteria_weight ben_elig_pyrl_prte_f.criteria_weight%type);
514 --
515 type g_cache_elpepy_instor is table of g_cache_elpepy_object_rec
516 index by binary_integer;
517 --
518 -- eligibility profile bargaining unit by eligibility profile
519 --
520 type g_cache_elpebu_object_rec is record
521 (eligy_prfl_id ben_eligy_prfl_f.eligy_prfl_id%type,
522 pk_id number,
523 short_code varchar2(30),
524 brgng_unit_cd ben_elig_brgng_unit_prte_f.brgng_unit_cd%type,
525 excld_flag ben_elig_brgng_unit_prte_f.excld_flag%type,
526 criteria_score ben_elig_brgng_unit_prte_f.criteria_score%type,
527 criteria_weight ben_elig_brgng_unit_prte_f.criteria_weight%type);
528 --
529 type g_cache_elpebu_instor is table of g_cache_elpebu_object_rec
530 index by binary_integer;
531 --
532 -- eligibility profile labour union membership by eligibility profile
533 --
534 type g_cache_elpelu_object_rec is record
535 (eligy_prfl_id ben_eligy_prfl_f.eligy_prfl_id%type,
536 pk_id number,
537 short_code varchar2(30),
538 lbr_mmbr_flag ben_elig_lbr_mmbr_prte_f.lbr_mmbr_flag%type,
539 excld_flag ben_elig_lbr_mmbr_prte_f.excld_flag%type,
540 criteria_score ben_elig_los_prte_f.criteria_score%type,
541 criteria_weight ben_elig_los_prte_f.criteria_weight%type);
542 --
543 type g_cache_elpelu_instor is table of g_cache_elpelu_object_rec
544 index by binary_integer;
545 --
546 -- eligibility profile leave of absence reason by eligibility profile
547 --
548 type g_cache_elpelr_object_rec is record
549 (eligy_prfl_id ben_eligy_prfl_f.eligy_prfl_id%type,
550 pk_id number,
551 short_code varchar2(30),
552 absence_attendance_type_id ben_elig_loa_rsn_prte_f.absence_attendance_type_id%type,
553 abs_attendance_reason_id ben_elig_loa_rsn_prte_f.abs_attendance_reason_id%type,
554 excld_flag ben_elig_loa_rsn_prte_f.excld_flag%type,
555 criteria_score ben_elig_loa_rsn_prte_f.criteria_score%type,
556 criteria_weight ben_elig_loa_rsn_prte_f.criteria_weight%type);
557 --
558 type g_cache_elpelr_instor is table of g_cache_elpelr_object_rec
559 index by binary_integer;
560 --
561 -- eligibility profile age details by eligibility profile
562 --
563 type g_cache_elpeap_object_rec is record
564 (eligy_prfl_id ben_eligy_prfl_f.eligy_prfl_id%type,
565 pk_id number,
566 short_code varchar2(30),
567 age_fctr_id ben_age_fctr.age_fctr_id%type,
568 excld_flag ben_elig_age_prte_f.excld_flag%type,
569 criteria_score ben_elig_age_prte_f.criteria_score%type,
570 criteria_weight ben_elig_age_prte_f.criteria_weight%type,
571 mx_age_num ben_age_fctr.mx_age_num%type,
572 mn_age_num ben_age_fctr.mn_age_num%type,
573 no_mn_age_flag ben_age_fctr.no_mn_age_flag%type,
574 no_mx_age_flag ben_age_fctr.no_mx_age_flag%type);
575 --
576 type g_cache_elpeap_instor is table of g_cache_elpeap_object_rec
577 index by binary_integer;
578 --
579 -- eligibility profile zip code range by eligibility profile
580 --
581 type g_cache_elpepz_object_rec is record
582 (eligy_prfl_id ben_eligy_prfl_f.eligy_prfl_id%type,
583 pk_id number,
584 short_code varchar2(30),
585 excld_flag ben_elig_pstl_cd_r_rng_prte_f.excld_flag%type,
586 criteria_score ben_elig_pstl_cd_r_rng_prte_f.criteria_score%type,
587 criteria_weight ben_elig_pstl_cd_r_rng_prte_f.criteria_weight%type,
588 from_value ben_pstl_zip_rng_f.from_value%type,
589 to_value ben_pstl_zip_rng_f.to_value%type);
590 --
591 type g_cache_elpepz_instor is table of g_cache_elpepz_object_rec
592 index by binary_integer;
593 --
594 -- eligibility profile benefits group by eligibility profile
595 --
596 type g_cache_elpebn_object_rec is record
597 (eligy_prfl_id ben_eligy_prfl_f.eligy_prfl_id%type,
598 pk_id number,
599 short_code varchar2(30),
600 benfts_grp_id ben_elig_benfts_grp_prte_f.benfts_grp_id%type,
601 excld_flag ben_elig_benfts_grp_prte_f.excld_flag%type,
602 criteria_score ben_elig_benfts_grp_prte_f.criteria_score%type,
603 criteria_weight ben_elig_benfts_grp_prte_f.criteria_weight%type);
604 --
605 type g_cache_elpebn_instor is table of g_cache_elpebn_object_rec
606 index by binary_integer;
607 --
608 -- eligibility profile legal entity by eligibility profile
609 --
610 type g_cache_elpeln_object_rec is record
611 (eligy_prfl_id ben_eligy_prfl_f.eligy_prfl_id%type,
612 pk_id number,
613 short_code varchar2(30),
614 excld_flag ben_elig_los_prte_f.excld_flag%type,
615 criteria_score ben_elig_los_prte_f.criteria_score%type,
616 criteria_weight ben_elig_los_prte_f.criteria_weight%type,
617 name hr_all_organization_units.name%type);
618 --
619 type g_cache_elpeln_instor is table of g_cache_elpeln_object_rec
620 index by binary_integer;
621 --
622 -- eligibility profile other plan by eligibility profile
623 --
624 type g_cache_elpepp_object_rec is record
625 (eligy_prfl_id ben_eligy_prfl_f.eligy_prfl_id%type,
626 pl_id ben_elig_prtt_anthr_pl_prte_f.pl_id%type,
627 excld_flag ben_elig_prtt_anthr_pl_prte_f.excld_flag%type);
628 --
629 type g_cache_elpepp_instor is table of g_cache_elpepp_object_rec
630 index by binary_integer;
631 --
632 -- eligibility profile people group by eligibility profile
633 --
634 type g_cache_elpeoy_object_rec is record
635 (eligy_prfl_id ben_eligy_prfl_f.eligy_prfl_id%type,
636 ptip_id ben_elig_othr_ptip_prte_f.ptip_id%type,
637 only_pls_subj_cobra_flag ben_elig_othr_ptip_prte_f.
638 only_pls_subj_cobra_flag%type,
639 excld_flag ben_elig_othr_ptip_prte_f.excld_flag%type);
640 --
641 type g_cache_elpeoy_instor is table of g_cache_elpeoy_object_rec
642 index by binary_integer;
643 --
644 -- eligibility profile plan type in program participate by eligibility profile
645 --
646 type g_cache_elpetd_object_rec is record
647 (eligy_prfl_id ben_eligy_prfl_f.eligy_prfl_id%type,
648 ptip_id ben_elig_dpnt_othr_ptip_f.ptip_id%type,
649 excld_flag ben_elig_dpnt_othr_ptip_f.excld_flag%type);
650 --
651 type g_cache_elpetd_instor is table of g_cache_elpetd_object_rec
652 index by binary_integer;
653 --
654 -- eligibility profile(dpnt) plan type in program participate by eligibility profile
655 --
656 type g_cache_elpeno_object_rec is record
657 (eligy_prfl_id ben_eligy_prfl_f.eligy_prfl_id%type,
658 coord_ben_no_cvg_flag ben_elig_no_othr_cvg_prte_f.
659 coord_ben_no_cvg_flag%type);
660 --
661 type g_cache_elpeno_instor is table of g_cache_elpeno_object_rec
662 index by binary_integer;
663 --
664 -- eligibility profile no other coverage particpation by eligibility profile
665 --
666 type g_cache_elpeep_object_rec is record
667 (eligy_prfl_id ben_eligy_prfl_f.eligy_prfl_id%type,
668 excld_flag ben_elig_enrld_anthr_pl_f.excld_flag%type,
669 enrl_det_dt_cd ben_elig_enrld_anthr_pl_f.enrl_det_dt_cd%type,
670 pl_id ben_elig_enrld_anthr_pl_f.pl_id%type);
671 --
672 type g_cache_elpeep_instor is table of g_cache_elpeep_object_rec
673 index by binary_integer;
674 --
675 -- eligibility profile enrolled another plan by eligibility profile
676 --
677 type g_cache_elpeei_object_rec is record
678 (eligy_prfl_id ben_eligy_prfl_f.eligy_prfl_id%type,
679 excld_flag ben_elig_enrld_anthr_oipl_f.excld_flag%type,
680 enrl_det_dt_cd ben_elig_enrld_anthr_oipl_f.enrl_det_dt_cd%type,
681 oipl_id ben_elig_enrld_anthr_oipl_f.oipl_id%type);
682 --
683 type g_cache_elpeei_instor is table of g_cache_elpeei_object_rec
684 index by binary_integer;
685 --
686 -- eligibility profile enrolled another option in plan by eligibility profile
687 --
688 type g_cache_elpeeg_object_rec is record
689 (eligy_prfl_id ben_eligy_prfl_f.eligy_prfl_id%type,
690 excld_flag ben_elig_enrld_anthr_pgm_f.excld_flag%type,
691 enrl_det_dt_cd ben_elig_enrld_anthr_pgm_f.enrl_det_dt_cd%type,
692 pgm_id ben_elig_enrld_anthr_pgm_f.pgm_id%type);
693 --
694 type g_cache_elpeeg_instor is table of g_cache_elpeeg_object_rec
695 index by binary_integer;
696 --
697 -- eligibility profile enrolled another program by eligibility profile
698 --
699 type g_cache_elpedp_object_rec is record
700 (eligy_prfl_id ben_eligy_prfl_f.eligy_prfl_id%type,
701 excld_flag ben_elig_dpnt_cvrd_othr_pl_f.excld_flag%type,
702 cvg_det_dt_cd ben_elig_dpnt_cvrd_othr_pl_f.cvg_det_dt_cd%type,
703 pl_id ben_elig_dpnt_cvrd_othr_pl_f.pl_id%type);
704 --
705 type g_cache_elpedp_instor is table of g_cache_elpedp_object_rec
706 index by binary_integer;
707 --
708 -- eligibility profile dependent covered another plan by eligibility profile
709 --
710 type g_cache_elpelv_object_rec is record
711 (eligy_prfl_id ben_eligy_prfl_f.eligy_prfl_id%type,
712 pk_id number,
713 short_code varchar2(30),
714 excld_flag ben_elig_lvg_rsn_prte_f.excld_flag%type,
715 criteria_score ben_elig_lvg_rsn_prte_f.criteria_score%type,
716 criteria_weight ben_elig_lvg_rsn_prte_f.criteria_weight%type,
717 lvg_rsn_cd ben_elig_lvg_rsn_prte_f.lvg_rsn_cd%type);
718 --
719 type g_cache_elpelv_instor is table of g_cache_elpelv_object_rec
720 index by binary_integer;
721 --
722 -- eligibility profile leaving reason by eligibility profile
723 --
724 type g_cache_elpeom_object_rec is record
725 (eligy_prfl_id ben_eligy_prfl_f.eligy_prfl_id%type,
726 excld_flag ben_elig_optd_mdcr_prte_f.exlcd_flag%type,
727 optd_mdcr_flag ben_elig_optd_mdcr_prte_f.optd_mdcr_flag%type);
728 --
729 type g_cache_elpeom_instor is table of g_cache_elpeom_object_rec
730 index by binary_integer;
731 --
732 -- eligibility profile enrolled in another plan in program by
733 -- eligibility profile
734 --
735 type g_cache_elpeai_object_rec is record
736 (eligy_prfl_id ben_eligy_prfl_f.eligy_prfl_id%type,
737 excld_flag ben_elig_enrld_anthr_plip_f.excld_flag%type,
738 enrl_det_dt_cd ben_elig_enrld_anthr_plip_f.enrl_det_dt_cd%type,
739 plip_id ben_elig_enrld_anthr_plip_f.plip_id%type);
740 --
741 type g_cache_elpeai_instor is table of g_cache_elpeai_object_rec
742 index by binary_integer;
743 --
744 -- eligibility profile dependent covered in another plan in program by
745 -- eligibility profile
746 --
747 type g_cache_elpedi_object_rec is record
748 (eligy_prfl_id ben_eligy_prfl_f.eligy_prfl_id%type,
749 excld_flag ben_elig_dpnt_cvrd_plip_f.excld_flag%type,
750 enrl_det_dt_cd ben_elig_dpnt_cvrd_plip_f.enrl_det_dt_cd%type,
751 plip_id ben_elig_dpnt_cvrd_plip_f.plip_id%type);
752 --
753 type g_cache_elpedi_instor is table of g_cache_elpedi_object_rec
754 index by binary_integer;
755 --
756 -- eligibility profile enrolled in another plan type in program by
757 -- eligibility profile
758 --
759 type g_cache_elpeet_object_rec is record
760 (eligy_prfl_id ben_eligy_prfl_f.eligy_prfl_id%type,
761 excld_flag ben_elig_enrld_anthr_ptip_f.excld_flag%type,
762 enrl_det_dt_cd ben_elig_enrld_anthr_ptip_f.enrl_det_dt_cd%type,
763 only_pls_subj_cobra_flag ben_elig_enrld_anthr_ptip_f.
764 only_pls_subj_cobra_flag%type,
765 ptip_id ben_elig_enrld_anthr_ptip_f.ptip_id%type);
766 --
767 type g_cache_elpeet_instor is table of g_cache_elpeet_object_rec
768 index by binary_integer;
769 --
770 -- eligibility profile enrolled in another plan type in program by
771 -- eligibility profile
772 --
773 type g_cache_elpedt_object_rec is record
774 (eligy_prfl_id ben_eligy_prfl_f.eligy_prfl_id%type,
775 excld_flag ben_elig_dpnt_cvrd_othr_ptip_f.excld_flag%type,
776 enrl_det_dt_cd ben_elig_dpnt_cvrd_othr_ptip_f.enrl_det_dt_cd%type,
777 only_pls_subj_cobra_flag ben_elig_dpnt_cvrd_othr_ptip_f.
778 only_pls_subj_cobra_flag%type,
779 ptip_id ben_elig_dpnt_cvrd_othr_ptip_f.ptip_id%type);
780 --
781 type g_cache_elpedt_instor is table of g_cache_elpedt_object_rec
782 index by binary_integer;
783 --
784 -- eligibility profile covered in another program by eligibility profile
785 --
786 type g_cache_elpedg_object_rec is record
787 (eligy_prfl_id ben_eligy_prfl_f.eligy_prfl_id%type,
788 excld_flag ben_elig_dpnt_cvrd_othr_pgm_f.excld_flag%type,
789 enrl_det_dt_cd ben_elig_dpnt_cvrd_othr_pgm_f.enrl_det_dt_cd%type,
790 pgm_id ben_elig_dpnt_cvrd_othr_pgm_f.pgm_id%type);
791 --
792 type g_cache_elpedg_instor is table of g_cache_elpedg_object_rec
793 index by binary_integer;
794 --
795 -- eligibility profile cobra qualified beneficiary by eligibility profile
796 --
797 type g_cache_elpecq_object_rec is record
798 (eligy_prfl_id ben_eligy_prfl_f.eligy_prfl_id%type,
799 pk_id number,
800 short_code varchar2(30),
801 quald_bnf_flag ben_elig_cbr_quald_bnf_f.quald_bnf_flag%type,
802 -- lamc added these 2 lines:
803 pgm_id ben_elig_cbr_quald_bnf_f.pgm_id%type,
804 ptip_id ben_elig_cbr_quald_bnf_f.ptip_id%type,
805 criteria_score ben_elig_cbr_quald_bnf_f.criteria_score%type,
806 criteria_weight ben_elig_cbr_quald_bnf_f.criteria_weight%type);
807 --
808 type g_cache_elpecq_instor is table of g_cache_elpecq_object_rec
809 index by binary_integer;
810 --
811 procedure elpepg_writecache
812 (p_effective_date in date,
813 p_refresh_cache in boolean default FALSE);
814 --
815 procedure elpepg_getcacdets
816 (p_effective_date in date,
817 p_business_group_id in number,
818 p_eligy_prfl_id in number,
819 p_refresh_cache in boolean default FALSE,
820 p_inst_set out nocopy ben_elp_cache.g_cache_elpepg_instor,
821 p_inst_count out nocopy number);
822 --
823 -- eligibility profile person type by eligibility profile
824 --
825 procedure elpept_writecache
826 (p_effective_date in date,
827 p_refresh_cache in boolean default FALSE);
828 --
829 procedure elpept_getcacdets
830 (p_effective_date in date,
831 p_business_group_id in number,
832 p_eligy_prfl_id in number,
833 p_refresh_cache in boolean default FALSE,
834 p_inst_set out nocopy ben_elp_cache.g_cache_elpept_instor,
835 p_inst_count out nocopy number);
836 --
837 -- eligibility profile assignment set by eligibility profile
838 --
839 procedure elpean_writecache
840 (p_effective_date in date,
841 p_refresh_cache in boolean default FALSE);
842 --
843 procedure elpean_getcacdets
844 (p_effective_date in date,
845 p_business_group_id in number,
846 p_eligy_prfl_id in number,
847 p_refresh_cache in boolean default FALSE,
848 p_inst_set out nocopy ben_elp_cache.g_cache_elpean_instor,
849 p_inst_count out nocopy number);
850 --
851 -- eligibility profile rule by eligibility profile
852 --
853 procedure elperl_writecache
854 (p_effective_date in date,
855 p_refresh_cache in boolean default FALSE);
856 --
857 procedure elperl_getcacdets
858 (p_effective_date in date,
859 p_business_group_id in number,
860 p_eligy_prfl_id in number,
861 p_refresh_cache in boolean default FALSE,
862 p_inst_set out nocopy ben_elp_cache.g_cache_elperl_instor,
863 p_inst_count out nocopy number);
864 --
865 -- eligibility profile assignment status type by eligibility profile
866 --
867 procedure elpees_writecache
868 (p_effective_date in date,
869 p_refresh_cache in boolean default FALSE);
870 --
871 procedure elpees_getcacdets
872 (p_effective_date in date,
873 p_business_group_id in number,
874 p_eligy_prfl_id in number,
875 p_refresh_cache in boolean default FALSE,
876 p_inst_set out nocopy ben_elp_cache.g_cache_elpees_instor,
877 p_inst_count out nocopy number);
878 --
879 -- eligibility profile length of service by eligibility profile
880 --
881 procedure elpels_writecache
882 (p_effective_date in date,
883 p_refresh_cache in boolean default FALSE);
884 --
885 procedure elpels_getcacdets
886 (p_effective_date in date,
887 p_business_group_id in number,
888 p_eligy_prfl_id in number,
889 p_refresh_cache in boolean default FALSE,
890 p_inst_set out nocopy ben_elp_cache.g_cache_elpels_instor,
891 p_inst_count out nocopy number);
892 --
893 -- eligibility profile age/los combination by eligibility profile
894 --
895 procedure elpecp_writecache
896 (p_effective_date in date,
897 p_refresh_cache in boolean default FALSE);
898 --
899 procedure elpecp_getcacdets
900 (p_effective_date in date,
901 p_business_group_id in number,
902 p_eligy_prfl_id in number,
903 p_refresh_cache in boolean default FALSE,
904 p_inst_set out nocopy ben_elp_cache.g_cache_elpecp_instor,
905 p_inst_count out nocopy number);
906 --
907 -- eligibility profile location by eligibility profile
908 --
909 procedure elpewl_writecache
910 (p_effective_date in date,
911 p_refresh_cache in boolean default FALSE);
912 --
913 procedure elpewl_getcacdets
914 (p_effective_date in date,
915 p_business_group_id in number,
916 p_eligy_prfl_id in number,
917 p_refresh_cache in boolean default FALSE,
918 p_inst_set out nocopy ben_elp_cache.g_cache_elpewl_instor,
919 p_inst_count out nocopy number);
920 --
921 -- eligibility profile organization by eligibility profile
922 --
923 procedure elpeou_writecache
924 (p_effective_date in date,
925 p_refresh_cache in boolean default FALSE);
926 --
927 procedure elpeou_getcacdets
928 (p_effective_date in date,
929 p_business_group_id in number,
930 p_eligy_prfl_id in number,
931 p_refresh_cache in boolean default FALSE,
932 p_inst_set out nocopy ben_elp_cache.g_cache_elpeou_instor,
933 p_inst_count out nocopy number);
934 --
935 -- eligibility profile pay frequency by eligibility profile
936 --
937 procedure elpehs_writecache
938 (p_effective_date in date,
939 p_refresh_cache in boolean default FALSE);
940 --
941 procedure elpehs_getcacdets
942 (p_effective_date in date,
943 p_business_group_id in number,
944 p_eligy_prfl_id in number,
945 p_refresh_cache in boolean default FALSE,
946 p_inst_set out nocopy ben_elp_cache.g_cache_elpehs_instor,
947 p_inst_count out nocopy number);
948 --
949 -- eligibility profile full/part time by eligibility profile
950 --
951 procedure elpefp_writecache
952 (p_effective_date in date,
953 p_refresh_cache in boolean default FALSE);
954 --
955 procedure elpefp_getcacdets
956 (p_effective_date in date,
957 p_business_group_id in number,
958 p_eligy_prfl_id in number,
959 p_refresh_cache in boolean default FALSE,
960 p_inst_set out nocopy ben_elp_cache.g_cache_elpefp_instor,
961 p_inst_count out nocopy number);
962 --
963 -- eligibility profile scheduled hours by eligibility profile
964 --
965 procedure elpesh_writecache
966 (p_effective_date in date,
967 p_refresh_cache in boolean default FALSE);
968 --
969 procedure elpesh_getcacdets
970 (p_effective_date in date,
971 p_business_group_id in number,
972 p_eligy_prfl_id in number,
973 p_refresh_cache in boolean default FALSE,
974 p_inst_set out nocopy ben_elp_cache.g_cache_elpesh_instor,
975 p_inst_count out nocopy number);
976 --
977 -- eligibility profile compensation level by eligibility profile
978 --
979 procedure elpecl_writecache
980 (p_effective_date in date,
981 p_refresh_cache in boolean default FALSE);
982 --
983 procedure elpecl_getcacdets
984 (p_effective_date in date,
985 p_business_group_id in number,
986 p_eligy_prfl_id in number,
987 p_comp_src_cd in varchar2 default null,
988 p_refresh_cache in boolean default FALSE,
989 p_inst_set out nocopy ben_elp_cache.g_cache_elpecl_instor,
990 p_inst_count out nocopy number);
991 --
992 -- eligibility profile hours worked by eligibility profile
993 --
994 procedure elpehw_writecache
995 (p_effective_date in date,
996 p_refresh_cache in boolean default FALSE);
997 --
998 procedure elpehw_getcacdets
999 (p_effective_date in date,
1000 p_business_group_id in number,
1001 p_eligy_prfl_id in number,
1002 p_hrs_src_cd in varchar2 default null,
1003 p_refresh_cache in boolean default FALSE,
1004 p_inst_set out nocopy ben_elp_cache.g_cache_elpehw_instor,
1005 p_inst_count out nocopy number);
1006 --
1007 -- eligibility profile full time by eligibility profile
1008 --
1009 procedure elpepf_writecache
1010 (p_effective_date in date,
1011 p_refresh_cache in boolean default FALSE);
1012 --
1013 procedure elpepf_getcacdets
1014 (p_effective_date in date,
1015 p_business_group_id in number,
1016 p_eligy_prfl_id in number,
1017 p_refresh_cache in boolean default FALSE,
1018 p_inst_set out nocopy ben_elp_cache.g_cache_elpepf_instor,
1019 p_inst_count out nocopy number);
1020 --
1021 -- eligibility profile grade by eligibility profile *
1022 --
1023 procedure elpegr_writecache
1024 (p_effective_date in date,
1025 p_refresh_cache in boolean default FALSE);
1026 --
1027 procedure elpegr_getcacdets
1028 (p_effective_date in date,
1029 p_business_group_id in number,
1030 p_eligy_prfl_id in number,
1031 p_refresh_cache in boolean default FALSE,
1032 p_inst_set out nocopy ben_elp_cache.g_cache_elpegr_instor,
1033 p_inst_count out nocopy number);
1034 --
1035 -- eligibility profile sex by eligibility profile *
1036 --
1037 procedure elpegn_writecache
1038 (p_effective_date in date,
1039 p_refresh_cache in boolean default FALSE);
1040 --
1041 procedure elpegn_getcacdets
1042 (p_effective_date in date,
1043 p_business_group_id in number,
1044 p_eligy_prfl_id in number,
1045 p_refresh_cache in boolean default FALSE,
1046 p_inst_set out nocopy ben_elp_cache.g_cache_elpegn_instor,
1047 p_inst_count out nocopy number);
1048 --
1049 -- eligibility profile job by eligibility profile *
1050 --
1051 procedure elpejp_writecache
1052 (p_effective_date in date,
1053 p_refresh_cache in boolean default FALSE);
1054 --
1055 procedure elpejp_getcacdets
1056 (p_effective_date in date,
1057 p_business_group_id in number,
1058 p_eligy_prfl_id in number,
1059 p_refresh_cache in boolean default FALSE,
1060 p_inst_set out nocopy ben_elp_cache.g_cache_elpejp_instor,
1061 p_inst_count out nocopy number);
1062 --
1063 -- eligibility profile pay basis by eligibility profile
1064 --
1065 procedure elpepb_writecache
1066 (p_effective_date in date,
1067 p_refresh_cache in boolean default FALSE);
1068 --
1069 procedure elpepb_getcacdets
1070 (p_effective_date in date,
1071 p_business_group_id in number,
1072 p_eligy_prfl_id in number,
1073 p_refresh_cache in boolean default FALSE,
1074 p_inst_set out nocopy ben_elp_cache.g_cache_elpepb_instor,
1075 p_inst_count out nocopy number);
1076 --
1077 -- eligibility profile payroll by eligibility profile
1078 --
1079 procedure elpepy_writecache
1080 (p_effective_date in date,
1081 p_refresh_cache in boolean default FALSE);
1082 --
1083 procedure elpepy_getcacdets
1084 (p_effective_date in date,
1085 p_business_group_id in number,
1086 p_eligy_prfl_id in number,
1087 p_refresh_cache in boolean default FALSE,
1088 p_inst_set out nocopy ben_elp_cache.g_cache_elpepy_instor,
1089 p_inst_count out nocopy number);
1090 --
1091 -- eligibility profile bargaining unit by eligibility profile
1092 --
1093 procedure elpebu_writecache
1094 (p_effective_date in date,
1095 p_refresh_cache in boolean default FALSE);
1096 --
1097 procedure elpebu_getcacdets
1098 (p_effective_date in date,
1099 p_business_group_id in number,
1100 p_eligy_prfl_id in number,
1101 p_refresh_cache in boolean default FALSE,
1102 p_inst_set out nocopy ben_elp_cache.g_cache_elpebu_instor,
1103 p_inst_count out nocopy number);
1104 --
1105 -- eligibility profile labour union membership by eligibility profile
1106 --
1107 procedure elpelu_writecache
1108 (p_effective_date in date,
1109 p_refresh_cache in boolean default FALSE);
1110 --
1111 procedure elpelu_getcacdets
1112 (p_effective_date in date,
1113 p_business_group_id in number,
1114 p_eligy_prfl_id in number,
1115 p_refresh_cache in boolean default FALSE,
1116 p_inst_set out nocopy ben_elp_cache.g_cache_elpelu_instor,
1117 p_inst_count out nocopy number);
1118 --
1119 -- eligibility profile leave of absence reason by eligibility profile
1120 --
1121 procedure elpelr_writecache
1122 (p_effective_date in date,
1123 p_refresh_cache in boolean default FALSE);
1124 --
1125 procedure elpelr_getcacdets
1126 (p_effective_date in date,
1127 p_business_group_id in number,
1128 p_eligy_prfl_id in number,
1129 p_refresh_cache in boolean default FALSE,
1130 p_inst_set out nocopy ben_elp_cache.g_cache_elpelr_instor,
1131 p_inst_count out nocopy number);
1132 --
1133 -- eligibility profile age details by eligibility profile
1134 --
1135 procedure elpeap_writecache
1136 (p_effective_date in date,
1137 p_refresh_cache in boolean default FALSE);
1138 --
1139 procedure elpeap_getcacdets
1140 (p_effective_date in date,
1141 p_business_group_id in number,
1142 p_eligy_prfl_id in number,
1143 p_refresh_cache in boolean default FALSE,
1144 p_inst_set out nocopy ben_elp_cache.g_cache_elpeap_instor,
1145 p_inst_count out nocopy number);
1146 --
1147 -- eligibility profile zip code range by eligibility profile
1148 --
1149 procedure elpepz_writecache
1150 (p_effective_date in date,
1151 p_refresh_cache in boolean default FALSE);
1152 --
1153 procedure elpepz_getcacdets
1154 (p_effective_date in date,
1155 p_business_group_id in number,
1156 p_eligy_prfl_id in number,
1157 p_refresh_cache in boolean default FALSE,
1158 p_inst_set out nocopy ben_elp_cache.g_cache_elpepz_instor,
1159 p_inst_count out nocopy number);
1160 --
1161 -- eligibility profile benefits group by eligibility profile
1162 --
1163 procedure elpebn_writecache
1164 (p_effective_date in date,
1165 p_refresh_cache in boolean default FALSE);
1166 --
1167 procedure elpebn_getcacdets
1168 (p_effective_date in date,
1169 p_business_group_id in number,
1170 p_eligy_prfl_id in number,
1171 p_refresh_cache in boolean default FALSE,
1172 p_inst_set out nocopy ben_elp_cache.g_cache_elpebn_instor,
1173 p_inst_count out nocopy number);
1174 --
1175 -- eligibility profile legal entity by eligibility profile
1176 --
1177 procedure elpeln_writecache
1178 (p_effective_date in date,
1179 p_refresh_cache in boolean default FALSE);
1180 --
1181 procedure elpeln_getcacdets
1182 (p_effective_date in date,
1183 p_business_group_id in number,
1184 p_eligy_prfl_id in number,
1185 p_refresh_cache in boolean default FALSE,
1186 p_inst_set out nocopy ben_elp_cache.g_cache_elpeln_instor,
1187 p_inst_count out nocopy number);
1188 --
1189 -- eligibility profile other plan by eligibility profile
1190 --
1191 procedure elpepp_writecache
1192 (p_effective_date in date,
1193 p_refresh_cache in boolean default FALSE);
1194 --
1195 procedure elpepp_getcacdets
1196 (p_effective_date in date,
1197 p_business_group_id in number,
1198 p_eligy_prfl_id in number,
1199 p_refresh_cache in boolean default FALSE,
1200 p_inst_set out nocopy ben_elp_cache.g_cache_elpepp_instor,
1201 p_inst_count out nocopy number);
1202 --
1203 -- eligibility profile service area by eligibility profile
1204 --
1205 procedure elpesa_writecache
1206 (p_effective_date in date,
1207 p_refresh_cache in boolean default FALSE);
1208 --
1209 procedure elpesa_getcacdets
1210 (p_effective_date in date,
1211 p_business_group_id in number,
1212 p_eligy_prfl_id in number,
1213 p_refresh_cache in boolean default FALSE,
1214 p_inst_set out nocopy ben_elp_cache.g_cache_elpesa_instor,
1215 p_inst_count out nocopy number);
1216 --
1217 -- eligibility profile other PTIP participate by eligibility profile
1218 --
1219 procedure elpeoy_writecache
1220 (p_effective_date in date,
1221 p_refresh_cache in boolean default FALSE);
1222 --
1223 procedure elpeoy_getcacdets
1224 (p_effective_date in date,
1225 p_business_group_id in number,
1226 p_eligy_prfl_id in number,
1227 p_refresh_cache in boolean default FALSE,
1228 p_inst_set out nocopy ben_elp_cache.g_cache_elpeoy_instor,
1229 p_inst_count out nocopy number);
1230 --
1231 procedure elpetd_writecache
1232 (p_effective_date in date,
1233 p_refresh_cache in boolean default FALSE);
1234 --
1235 procedure elpetd_getcacdets
1236 (p_effective_date in date,
1237 p_business_group_id in number,
1238 p_eligy_prfl_id in number,
1239 p_refresh_cache in boolean default FALSE,
1240 p_inst_set out nocopy ben_elp_cache.g_cache_elpetd_instor,
1241 p_inst_count out nocopy number);
1242 --
1243 -- eligibility profile no other coverage participate by eligibility profile
1244 --
1245 procedure elpeno_writecache
1246 (p_effective_date in date,
1247 p_refresh_cache in boolean default FALSE);
1248 --
1249 procedure elpeno_getcacdets
1250 (p_effective_date in date,
1251 p_business_group_id in number,
1252 p_eligy_prfl_id in number,
1253 p_refresh_cache in boolean default FALSE,
1254 p_inst_set out nocopy ben_elp_cache.g_cache_elpeno_instor,
1255 p_inst_count out nocopy number);
1256 --
1257 -- eligibility profile eligibility enrolled another plan by eligibility profile
1258 --
1259 procedure elpeep_writecache
1260 (p_effective_date in date,
1261 p_refresh_cache in boolean default FALSE);
1262 --
1263 procedure elpeep_getcacdets
1264 (p_effective_date in date,
1265 p_business_group_id in number,
1266 p_eligy_prfl_id in number,
1267 p_refresh_cache in boolean default FALSE,
1268 p_inst_set out nocopy ben_elp_cache.g_cache_elpeep_instor,
1269 p_inst_count out nocopy number);
1270 --
1271 -- eligibility profile eligibility enrolled another oipl by eligibility profile
1272 --
1273 procedure elpeei_writecache
1274 (p_effective_date in date,
1275 p_refresh_cache in boolean default FALSE);
1276 --
1277 procedure elpeei_getcacdets
1278 (p_effective_date in date,
1279 p_business_group_id in number,
1280 p_eligy_prfl_id in number,
1281 p_refresh_cache in boolean default FALSE,
1282 p_inst_set out nocopy ben_elp_cache.g_cache_elpeei_instor,
1283 p_inst_count out nocopy number);
1284 --
1285 -- eligibility profile eligibility enrolled another pgm by eligibility profile
1286 --
1287 procedure elpeeg_writecache
1288 (p_effective_date in date,
1289 p_refresh_cache in boolean default FALSE);
1290 --
1291 procedure elpeeg_getcacdets
1292 (p_effective_date in date,
1293 p_business_group_id in number,
1294 p_eligy_prfl_id in number,
1295 p_refresh_cache in boolean default FALSE,
1296 p_inst_set out nocopy ben_elp_cache.g_cache_elpeeg_instor,
1297 p_inst_count out nocopy number);
1298 --
1299 procedure elpedp_writecache
1300 (p_effective_date in date,
1301 p_refresh_cache in boolean default FALSE);
1302 --
1303 procedure elpedp_getcacdets
1304 (p_effective_date in date,
1305 p_business_group_id in number,
1306 p_eligy_prfl_id in number,
1307 p_refresh_cache in boolean default FALSE,
1308 p_inst_set out nocopy ben_elp_cache.g_cache_elpedp_instor,
1309 p_inst_count out nocopy number);
1310 --
1311 -- eligibility profile eligibility leaving reason part by eligibility profile
1312 --
1313 procedure elpelv_writecache
1314 (p_effective_date in date,
1315 p_refresh_cache in boolean default FALSE);
1316 --
1317 procedure elpelv_getcacdets
1318 (p_effective_date in date,
1319 p_business_group_id in number,
1320 p_eligy_prfl_id in number,
1321 p_refresh_cache in boolean default FALSE,
1322 p_inst_set out nocopy ben_elp_cache.g_cache_elpelv_instor,
1323 p_inst_count out nocopy number);
1324 --
1325 -- eligibility profile eligibility opted medicare part by eligibility profile
1326 --
1327 procedure elpeom_writecache
1328 (p_effective_date in date,
1329 p_refresh_cache in boolean default FALSE);
1330 --
1331 procedure elpeom_getcacdets
1332 (p_effective_date in date,
1333 p_business_group_id in number,
1334 p_eligy_prfl_id in number,
1335 p_refresh_cache in boolean default FALSE,
1336 p_inst_set out nocopy ben_elp_cache.g_cache_elpeom_instor,
1337 p_inst_count out nocopy number);
1338 --
1339 -- eligibility profile enrolled in another plip by eligibility profile
1340 --
1341 procedure elpeai_writecache
1342 (p_effective_date in date,
1343 p_refresh_cache in boolean default FALSE);
1344 --
1345 procedure elpeai_getcacdets
1346 (p_effective_date in date,
1347 p_business_group_id in number,
1348 p_eligy_prfl_id in number,
1349 p_refresh_cache in boolean default FALSE,
1350 p_inst_set out nocopy ben_elp_cache.g_cache_elpeai_instor,
1351 p_inst_count out nocopy number);
1352 --
1353 -- eligibility profile covered in another plip by eligibility profile
1354 --
1355 procedure elpedi_writecache
1356 (p_effective_date in date,
1357 p_refresh_cache in boolean default FALSE);
1358 --
1359 procedure elpedi_getcacdets
1360 (p_effective_date in date,
1361 p_business_group_id in number,
1362 p_eligy_prfl_id in number,
1363 p_refresh_cache in boolean default FALSE,
1364 p_inst_set out nocopy ben_elp_cache.g_cache_elpedi_instor,
1365 p_inst_count out nocopy number);
1366 --
1367 -- eligibility profile enrolled in another ptip by eligibility profile
1368 --
1369 procedure elpeet_writecache
1370 (p_effective_date in date,
1371 p_refresh_cache in boolean default FALSE);
1372 --
1373 procedure elpeet_getcacdets
1374 (p_effective_date in date,
1375 p_business_group_id in number,
1376 p_eligy_prfl_id in number,
1377 p_refresh_cache in boolean default FALSE,
1378 p_inst_set out nocopy ben_elp_cache.g_cache_elpeet_instor,
1379 p_inst_count out nocopy number);
1380 --
1381 -- eligibility profile covered in another ptip by eligibility profile
1382 --
1383 procedure elpedt_writecache
1384 (p_effective_date in date,
1385 p_refresh_cache in boolean default FALSE);
1386 --
1387 procedure elpedt_getcacdets
1388 (p_effective_date in date,
1389 p_business_group_id in number,
1390 p_eligy_prfl_id in number,
1391 p_refresh_cache in boolean default FALSE,
1392 p_inst_set out nocopy ben_elp_cache.g_cache_elpedt_instor,
1393 p_inst_count out nocopy number);
1394 --
1395 -- eligibility profile covered in another program by eligibility profile
1396 --
1397 procedure elpedg_writecache
1398 (p_effective_date in date,
1399 p_refresh_cache in boolean default FALSE);
1400 --
1401 procedure elpedg_getcacdets
1402 (p_effective_date in date,
1403 p_business_group_id in number,
1404 p_eligy_prfl_id in number,
1405 p_refresh_cache in boolean default FALSE,
1406 p_inst_set out nocopy ben_elp_cache.g_cache_elpedg_instor,
1407 p_inst_count out nocopy number);
1408 --
1409 -- eligibility profile covered in another program by eligibility profile
1410 --
1411 procedure elpecq_writecache
1412 (p_effective_date in date,
1413 p_refresh_cache in boolean default FALSE);
1414 --
1415 procedure elpecq_getcacdets
1416 (p_effective_date in date,
1417 p_business_group_id in number,
1418 p_eligy_prfl_id in number,
1419 p_refresh_cache in boolean default FALSE,
1420 p_inst_set out nocopy ben_elp_cache.g_cache_elpecq_instor,
1421 p_inst_count out nocopy number);
1422 --
1423 -- GENERIC ARRAY to cater to all profiles
1424 --
1425 type g_elp_cache_rec is record
1426 (eligy_prfl_id number
1427 ,pk_id number
1428 ,short_code varchar2(30)
1429 ,criteria_score number
1430 ,criteria_weight number
1431 ,v230_val varchar2(30)
1432 ,v230_val1 varchar2(30)
1433 ,num_val number
1434 ,num_val1 number
1435 ,excld_flag varchar2(100)
1436 );
1437 --
1438 type g_elp_cache is varray(1000000) of g_elp_cache_rec;
1439
1440 -- ---------------------------------------------------------------------
1441 -- eligibility profile - disability
1442 -- ---------------------------------------------------------------------
1443 --
1444 procedure elpeds_getdets
1445 (p_effective_date in date
1446 ,p_eligy_prfl_id in number
1447 --
1448 ,p_inst_set in out nocopy g_elp_cache
1449 );
1450
1451 -- ---------------------------------------------------------------------
1452 -- eligibility profile - tobacco use
1453 -- ---------------------------------------------------------------------
1454 procedure elpetu_getdets
1455 (p_effective_date in date
1456 ,p_eligy_prfl_id in number
1457 --
1458 ,p_inst_set in out nocopy g_elp_cache
1459 );
1460
1461 -- ---------------------------------------------------------------------
1462 -- eligibility profile - total coverage volume
1463 -- ---------------------------------------------------------------------
1464 --
1465 procedure elpetc_getdets
1466 (p_effective_date in date
1467 ,p_eligy_prfl_id in number
1468 --
1469 ,p_inst_set in out nocopy g_elp_cache
1470 );
1471
1472 -- ---------------------------------------------------------------------
1473 -- eligibility profile - total participants
1474 -- ---------------------------------------------------------------------
1475 --
1476 procedure elpetp_getdets
1477 (p_effective_date in date
1478 ,p_eligy_prfl_id in number
1479 --
1480 ,p_inst_set in out nocopy g_elp_cache
1481 );
1482
1483 -- ---------------------------------------------------------------------
1484 -- eligibility profile - Participation in another plan
1485 -- ---------------------------------------------------------------------
1486 --
1487 procedure elpeop_getdets
1488 (p_effective_date in date
1489 ,p_eligy_prfl_id in number
1490 --
1491 ,p_inst_set in out nocopy g_elp_cache
1492 );
1493
1494 -- ---------------------------------------------------------------------
1495 -- eligibility profile - Health Coverage Selected
1496 -- ---------------------------------------------------------------------
1497 --
1498 procedure elpehc_getdets
1499 (p_effective_date in date
1500 ,p_eligy_prfl_id in number
1501 --
1502 ,p_inst_set in out nocopy g_elp_cache
1503 );
1504
1505 -- ---------------------------------------------------------------------
1506 -- eligibility profile - Competency
1507 -- ---------------------------------------------------------------------
1508 --
1509 procedure elpecy_getdets
1510 (p_effective_date in date
1511 ,p_eligy_prfl_id in number
1512 --
1513 ,p_inst_set in out nocopy g_elp_cache
1514 );
1515 -- ---------------------------------------------------------------------
1516 -- eligibility profile - Quartile in Grade
1517 -- ---------------------------------------------------------------------
1518 --
1519 procedure elpeqg_getdets
1520 (p_effective_date in date
1521 ,p_eligy_prfl_id in number
1522 --
1523 ,p_inst_set in out nocopy g_elp_cache
1524 );
1525 -- ---------------------------------------------------------------------
1526 -- eligibility profile - Performance Rating
1527 -- ---------------------------------------------------------------------
1528 --
1529 procedure elpepr_getdets
1530 (p_effective_date in date
1531 ,p_eligy_prfl_id in number
1532 --
1533 ,p_inst_set in out nocopy g_elp_cache
1534 );
1535
1536 --
1537 procedure clear_down_cache;
1538 --
1539 END ben_elp_cache;