[Home] [Help]
PACKAGE: APPS.BEN_DERIVE_PART_AND_RATE_CACHE
Source
1 package ben_derive_part_and_rate_cache as
2 /* $Header: bendrpac.pkh 120.0 2005/05/28 04:12:26 appldev noship $ */
3 --------------------------------------------------------------------------------
4 /*
5 +==============================================================================+
6 | Copyright (c) 1997 Oracle Corporation |
7 | Redwood Shores, California, USA |
8 | All rights reserved. |
9 +==============================================================================+
10
11 Name
12 Derive Participation and Rate Caching Routine
13 Purpose
14 This package is used to return or retrieve information that is
15 needed for rates and or factors.
16 History
17 Date Who Version What?
18 ---- --- ------- -----
19 23 Nov 98 G Perry 115.0 Created.
20 20 Dec 98 G Perry 115.1 Support for hours worked.
21 17 Feb 99 G Perry 115.2 Changed cache strucutre for
22 hours worked so we can store
23 the once_r_cntug_cd.
24 04 May 99 G Perry 115.3 Added cache support for
25 PLIP and PTIP.
26 06 May 99 G Perry 115.4 Backport for Fidelity
27 06 May 99 G Perry 115.5 Leapfrog from 115.3
28 04 Aug 99 T Guy 115.6 added age_to_use_cd to
29 g_cache_age_rec_obj
30 23 Aug 99 G Perry 115.7 Added nocopy compiler directive.
31 10 Jan 00 pbodla 115.8 Added los_calc_rl to
32 g_cache_los_rec_obj
33 24 Jan 00 lmcdonal 115.9 Add hrs_wkd_calc_rl to hwf and
34 comp_calc_rl to clf. Bugs
35 1118118, 1118113.
36 07 Mar 00 gperry 115.10 Fixed for WWBUG 1195803.
37 31 Mar 00 gperry 115.11 Added oiplip support.
38 26 Jun 00 gperry 115.12 Added age_calc_rl support.
39 08 Oct 02 kmahendr 115.13 Added parameters to get_los_elig
40 08 Oct 02 kmahendr 115.14 Added dbdrv command
41 18 Oct 02 kmahendr 115.15 Added parameters to get_age_elig, get_comp_elig
42 get_pct_elig, get_hours_elig, get_comb_elig
43 22 Oct 02 ikasire 115.16 Bug 2502763 added parameters to clf routine
44 */
45 --------------------------------------------------------------------------------
46 --
47 -- Cache all derivable factor stuff for any particular plan or program or
48 -- oipl.
49 --
50 type g_cache_los_rec_obj is record
51 (id number,
52 exist varchar2(1),
53 los_det_cd ben_los_fctr.los_det_cd%type,
54 los_dt_to_use_cd ben_los_fctr.los_dt_to_use_cd%type,
55 use_overid_svc_dt_flag ben_los_fctr.use_overid_svc_dt_flag%type,
56 los_uom ben_los_fctr.los_uom%type,
57 los_det_rl ben_los_fctr.los_det_rl%type,
58 los_dt_to_use_rl ben_los_fctr.los_dt_to_use_rl%type,
59 los_calc_rl ben_los_fctr.los_calc_rl%type,
60 rndg_cd ben_los_fctr.rndg_cd%type,
61 rndg_rl ben_los_fctr.rndg_rl%type,
62 mn_los_num ben_los_fctr.mn_los_num%type,
63 mx_los_num ben_los_fctr.mx_los_num%type);
64 --
65 type g_cache_los_rec_table is table of g_cache_los_rec_obj index
66 by binary_integer;
67 --
68 g_cache_pl_los_el_rec g_cache_los_rec_table;
69 g_cache_oipl_los_el_rec g_cache_los_rec_table;
70 g_cache_pgm_los_el_rec g_cache_los_rec_table;
71 g_cache_plip_los_el_rec g_cache_los_rec_table;
72 g_cache_ptip_los_el_rec g_cache_los_rec_table;
73 g_cache_pl_los_rt_rec g_cache_los_rec_table;
74 g_cache_oipl_los_rt_rec g_cache_los_rec_table;
75 g_cache_pgm_los_rt_rec g_cache_los_rec_table;
76 g_cache_plip_los_rt_rec g_cache_los_rec_table;
77 g_cache_ptip_los_rt_rec g_cache_los_rec_table;
78 g_cache_oiplip_los_rt_rec g_cache_los_rec_table;
79 g_cache_stated_los_rec g_cache_los_rec_table;
80 --
81 type g_cache_age_rec_obj is record
82 (id number,
83 exist varchar2(1),
84 age_det_cd ben_age_fctr.age_det_cd%type,
85 age_to_use_cd ben_age_fctr.age_to_use_cd%type,
86 age_uom ben_age_fctr.age_uom%type,
87 age_det_rl ben_age_fctr.age_det_rl%type,
88 rndg_cd ben_age_fctr.rndg_cd%type,
89 rndg_rl ben_age_fctr.rndg_rl%type,
90 age_calc_rl ben_age_fctr.age_calc_rl%type,
91 mn_age_num ben_age_fctr.mn_age_num%type,
92 mx_age_num ben_age_fctr.mx_age_num%type);
93 --
94 type g_cache_age_rec_table is table of g_cache_age_rec_obj index
95 by binary_integer;
96 --
97 g_cache_pl_age_el_rec g_cache_age_rec_table;
98 g_cache_oipl_age_el_rec g_cache_age_rec_table;
99 g_cache_pgm_age_el_rec g_cache_age_rec_table;
100 g_cache_plip_age_el_rec g_cache_age_rec_table;
101 g_cache_ptip_age_el_rec g_cache_age_rec_table;
102 g_cache_pl_age_rt_rec g_cache_age_rec_table;
103 g_cache_oipl_age_rt_rec g_cache_age_rec_table;
104 g_cache_pgm_age_rt_rec g_cache_age_rec_table;
105 g_cache_plip_age_rt_rec g_cache_age_rec_table;
106 g_cache_ptip_age_rt_rec g_cache_age_rec_table;
107 g_cache_oiplip_age_rt_rec g_cache_age_rec_table;
108 g_cache_stated_age_rec g_cache_age_rec_table;
109 --
110 type g_cache_clf_rec_obj is record
111 (id number,
112 exist varchar2(1),
113 comp_lvl_uom ben_comp_lvl_fctr.comp_lvl_uom%type,
114 comp_src_cd ben_comp_lvl_fctr.comp_src_cd%type,
115 comp_lvl_det_cd ben_comp_lvl_fctr.comp_lvl_det_cd%type,
116 comp_lvl_det_rl ben_comp_lvl_fctr.comp_lvl_det_rl%type,
117 rndg_cd ben_comp_lvl_fctr.rndg_cd%type,
118 rndg_rl ben_comp_lvl_fctr.rndg_rl%type,
119 mn_comp_val ben_comp_lvl_fctr.mn_comp_val%type,
120 mx_comp_val ben_comp_lvl_fctr.mx_comp_val%type,
121 bnfts_bal_id ben_comp_lvl_fctr.bnfts_bal_id%type,
122 defined_balance_id ben_comp_lvl_fctr.defined_balance_id%type,
123 sttd_sal_prdcty_cd ben_comp_lvl_fctr.sttd_sal_prdcty_cd%type,
124 comp_lvl_fctr_id ben_comp_lvl_fctr.comp_lvl_fctr_id%type,
125 comp_calc_rl ben_comp_lvl_fctr.comp_calc_rl%type);
126 --
127 type g_cache_clf_rec_table is table of g_cache_clf_rec_obj index
128 by binary_integer;
129 --
130 g_cache_pl_clf_el_rec g_cache_clf_rec_table;
131 g_cache_oipl_clf_el_rec g_cache_clf_rec_table;
132 g_cache_pgm_clf_el_rec g_cache_clf_rec_table;
133 g_cache_plip_clf_el_rec g_cache_clf_rec_table;
134 g_cache_ptip_clf_el_rec g_cache_clf_rec_table;
135 g_cache_pl_clf_rt_rec g_cache_clf_rec_table;
136 g_cache_oipl_clf_rt_rec g_cache_clf_rec_table;
137 g_cache_pgm_clf_rt_rec g_cache_clf_rec_table;
138 g_cache_plip_clf_rt_rec g_cache_clf_rec_table;
139 g_cache_ptip_clf_rt_rec g_cache_clf_rec_table;
140 g_cache_oiplip_clf_rt_rec g_cache_clf_rec_table;
141 --
142 type g_cache_cla_rec_obj is record
143 (id number,
144 exist varchar2(1),
145 los_fctr_id ben_cmbn_age_los_fctr.los_fctr_id%type,
146 age_fctr_id ben_cmbn_age_los_fctr.age_fctr_id%type,
147 cmbnd_min_val ben_cmbn_age_los_fctr.cmbnd_min_val%type,
148 cmbnd_max_val ben_cmbn_age_los_fctr.cmbnd_max_val%type);
149 --
150 type g_cache_cla_rec_table is table of g_cache_cla_rec_obj index
151 by binary_integer;
152 --
153 g_cache_pl_cla_el_rec g_cache_cla_rec_table;
154 g_cache_oipl_cla_el_rec g_cache_cla_rec_table;
155 g_cache_pgm_cla_el_rec g_cache_cla_rec_table;
156 g_cache_plip_cla_el_rec g_cache_cla_rec_table;
157 g_cache_ptip_cla_el_rec g_cache_cla_rec_table;
158 g_cache_pl_cla_rt_rec g_cache_cla_rec_table;
159 g_cache_oipl_cla_rt_rec g_cache_cla_rec_table;
160 g_cache_pgm_cla_rt_rec g_cache_cla_rec_table;
161 g_cache_plip_cla_rt_rec g_cache_cla_rec_table;
162 g_cache_ptip_cla_rt_rec g_cache_cla_rec_table;
163 g_cache_oiplip_cla_rt_rec g_cache_cla_rec_table;
164 --
165 type g_cache_pff_rec_obj is record
166 (id number,
167 exist varchar2(1),
168 use_prmry_asnt_only_flag ben_pct_fl_tm_fctr.use_prmry_asnt_only_flag%type,
169 use_sum_of_all_asnts_flag ben_pct_fl_tm_fctr.use_sum_of_all_asnts_flag%type,
170 rndg_cd ben_pct_fl_tm_fctr.rndg_cd%type,
171 rndg_rl ben_pct_fl_tm_fctr.rndg_rl%type,
172 mn_pct_val ben_pct_fl_tm_fctr.mn_pct_val%type,
173 mx_pct_val ben_pct_fl_tm_fctr.mx_pct_val%type);
174 --
175 type g_cache_pff_rec_table is table of g_cache_pff_rec_obj index
176 by binary_integer;
177 --
178 g_cache_pl_pff_el_rec g_cache_pff_rec_table;
179 g_cache_oipl_pff_el_rec g_cache_pff_rec_table;
180 g_cache_pgm_pff_el_rec g_cache_pff_rec_table;
181 g_cache_plip_pff_el_rec g_cache_pff_rec_table;
182 g_cache_ptip_pff_el_rec g_cache_pff_rec_table;
183 g_cache_pl_pff_rt_rec g_cache_pff_rec_table;
184 g_cache_oipl_pff_rt_rec g_cache_pff_rec_table;
185 g_cache_pgm_pff_rt_rec g_cache_pff_rec_table;
186 g_cache_plip_pff_rt_rec g_cache_pff_rec_table;
187 g_cache_ptip_pff_rt_rec g_cache_pff_rec_table;
188 g_cache_oiplip_pff_rt_rec g_cache_pff_rec_table;
189 --
190 type g_cache_hwf_rec_obj is record
191 (id number,
192 exist varchar2(1),
193 hrs_src_cd ben_hrs_wkd_in_perd_fctr.hrs_src_cd%type,
194 hrs_wkd_det_cd ben_hrs_wkd_in_perd_fctr.hrs_wkd_det_cd%type,
195 hrs_wkd_det_rl ben_hrs_wkd_in_perd_fctr.hrs_wkd_det_rl%type,
196 rndg_cd ben_hrs_wkd_in_perd_fctr.rndg_cd%type,
197 rndg_rl ben_hrs_wkd_in_perd_fctr.rndg_rl%type,
198 defined_balance_id ben_hrs_wkd_in_perd_fctr.defined_balance_id%type,
199 bnfts_bal_id ben_hrs_wkd_in_perd_fctr.bnfts_bal_id%type,
200 mn_hrs_num ben_hrs_wkd_in_perd_fctr.mn_hrs_num%type,
201 mx_hrs_num ben_hrs_wkd_in_perd_fctr.mx_hrs_num%type,
202 once_r_cntug_cd ben_hrs_wkd_in_perd_fctr.once_r_cntug_cd%type,
203 hrs_wkd_calc_rl ben_hrs_wkd_in_perd_fctr.hrs_wkd_calc_rl%type);
204 --
205 type g_cache_hwf_rec_table is table of g_cache_hwf_rec_obj index
206 by binary_integer;
207 --
208 g_cache_pl_hwf_el_rec g_cache_hwf_rec_table;
209 g_cache_oipl_hwf_el_rec g_cache_hwf_rec_table;
210 g_cache_pgm_hwf_el_rec g_cache_hwf_rec_table;
211 g_cache_plip_hwf_el_rec g_cache_hwf_rec_table;
212 g_cache_ptip_hwf_el_rec g_cache_hwf_rec_table;
213 g_cache_pl_hwf_rt_rec g_cache_hwf_rec_table;
214 g_cache_oipl_hwf_rt_rec g_cache_hwf_rec_table;
215 g_cache_pgm_hwf_rt_rec g_cache_hwf_rec_table;
216 g_cache_plip_hwf_rt_rec g_cache_hwf_rec_table;
217 g_cache_ptip_hwf_rt_rec g_cache_hwf_rec_table;
218 g_cache_oiplip_hwf_rt_rec g_cache_hwf_rec_table;
219 --
220 procedure get_los_elig
221 (p_pgm_id in number,
222 p_pl_id in number,
223 p_oipl_id in number,
224 p_plip_id in number,
225 p_ptip_id in number,
226 p_old_val in number default null,
227 p_new_val in number default null,
228 p_business_group_id in number,
229 p_effective_date in date,
230 p_rec out nocopy g_cache_los_rec_obj);
231 --
232 procedure get_los_rate
233 (p_pgm_id in number,
234 p_pl_id in number,
235 p_oipl_id in number,
236 p_plip_id in number,
237 p_ptip_id in number,
238 p_oiplip_id in number,
239 p_old_val in number default null,
240 p_new_val in number default null,
241 p_business_group_id in number,
242 p_effective_date in date,
243 p_rec out nocopy g_cache_los_rec_obj);
244 --
245 procedure get_los_stated
246 (p_los_fctr_id in number,
247 p_business_group_id in number,
248 p_rec out nocopy g_cache_los_rec_obj);
249 --
250 procedure get_age_elig
251 (p_pgm_id in number,
252 p_pl_id in number,
253 p_oipl_id in number,
254 p_plip_id in number,
255 p_ptip_id in number,
256 p_old_val in number default null,
257 p_new_val in number default null,
258 p_business_group_id in number,
259 p_effective_date in date,
260 p_rec out nocopy g_cache_age_rec_obj);
261 --
262 procedure get_age_rate
263 (p_pgm_id in number,
264 p_pl_id in number,
265 p_oipl_id in number,
266 p_plip_id in number,
267 p_ptip_id in number,
268 p_oiplip_id in number,
269 p_old_val in number default null,
270 p_new_val in number default null,
271 p_business_group_id in number,
272 p_effective_date in date,
273 p_rec out nocopy g_cache_age_rec_obj);
274 --
275 procedure get_age_stated
276 (p_age_fctr_id in number,
277 p_business_group_id in number,
278 p_rec out nocopy g_cache_age_rec_obj);
279 --
280 procedure get_comp_elig
281 (p_pgm_id in number,
282 p_pl_id in number,
283 p_oipl_id in number,
284 p_plip_id in number,
285 p_ptip_id in number,
286 p_old_val in number default null,
287 p_new_val in number default null,
288 p_business_group_id in number,
289 p_effective_date in date,
290 p_rec out nocopy g_cache_clf_rec_obj);
291 --
292 procedure get_comp_rate
293 (p_pgm_id in number,
294 p_pl_id in number,
295 p_oipl_id in number,
296 p_plip_id in number,
297 p_ptip_id in number,
298 p_oiplip_id in number,
299 p_old_val in number default null,
300 p_new_val in number default null,
301 p_business_group_id in number,
302 p_effective_date in date,
303 p_rec out nocopy g_cache_clf_rec_obj);
304 --
305 procedure get_comb_elig
306 (p_pgm_id in number,
307 p_pl_id in number,
308 p_oipl_id in number,
309 p_plip_id in number,
310 p_ptip_id in number,
311 p_old_val in number default null,
312 p_new_val in number default null,
313 p_business_group_id in number,
314 p_effective_date in date,
315 p_rec out nocopy g_cache_cla_rec_obj);
316 --
317 procedure get_comb_rate
318 (p_pgm_id in number,
319 p_pl_id in number,
320 p_oipl_id in number,
321 p_plip_id in number,
322 p_ptip_id in number,
323 p_oiplip_id in number,
324 p_old_val in number default null,
325 p_new_val in number default null,
326 p_business_group_id in number,
327 p_effective_date in date,
328 p_rec out nocopy g_cache_cla_rec_obj);
329 --
330 procedure get_pct_elig
331 (p_pgm_id in number,
332 p_pl_id in number,
333 p_oipl_id in number,
334 p_plip_id in number,
335 p_ptip_id in number,
336 p_old_val in number default null,
337 p_new_val in number default null,
338 p_business_group_id in number,
339 p_effective_date in date,
340 p_rec out nocopy g_cache_pff_rec_obj);
341 --
342 procedure get_pct_rate
343 (p_pgm_id in number,
344 p_pl_id in number,
345 p_oipl_id in number,
346 p_plip_id in number,
347 p_ptip_id in number,
348 p_oiplip_id in number,
349 p_old_val in number default null,
353 p_rec out nocopy g_cache_pff_rec_obj);
350 p_new_val in number default null,
351 p_business_group_id in number,
352 p_effective_date in date,
354 --
355 procedure get_hours_elig
356 (p_pgm_id in number,
357 p_pl_id in number,
358 p_oipl_id in number,
359 p_plip_id in number,
360 p_ptip_id in number,
361 p_old_val in number default null,
362 p_new_val in number default null,
363 p_business_group_id in number,
364 p_effective_date in date,
365 p_rec out nocopy g_cache_hwf_rec_obj);
366 --
367 procedure get_hours_rate
368 (p_pgm_id in number,
369 p_pl_id in number,
370 p_oipl_id in number,
371 p_plip_id in number,
372 p_ptip_id in number,
373 p_oiplip_id in number,
374 p_old_val in number default null,
375 p_new_val in number default null,
376 p_business_group_id in number,
377 p_effective_date in date,
378 p_rec out nocopy g_cache_hwf_rec_obj);
379 --
380 procedure clear_down_cache;
381 --
382 end ben_derive_part_and_rate_cache;