[Home] [Help]
PACKAGE BODY: APPS.BEN_FASTFORMULA_CHECK
Source
1 PACKAGE BODY Ben_FastFormula_Check AS
2 /* $Header: benffchk.pkb 120.0 2005/05/28 09:00:32 appldev noship $ */
3 /*============================================================================+
4 | Copyright (c) 1997 Oracle Corporation |
5 | Redwood Shores, California, USA |
6 | All rights reserved. |
7 | << BEN_FASTFORMULA_CHECK (B) >> |
8 +=============================================================================+
9 * Name:
10 * Fast_Formula_Check
11 * Purpose:
12 * This package is used to check the existence of given formula_type_id
13 * and formula_id in ben tables.
14 * History:
15 * Date Who Version What?
16 * ----------- ------------ ------- ------------------------------------
17 * 01-SEP-2004 swjain 115.0 Created.
18 * 01-SEP-2004 swjain 115.1 p_effective_date and p_business_group_id
19 * defaulted to null
20 * 02-SEP-2004 swjain 115.2 p_legislation_cd parameter added for
21 * future use
22 * 27-SEP-2004 swjain 115.3 case statements changed to if-elsif for
23 * 8i compatibility and few more formula_type_ids
24 * added
25 * 27-SEP-2004 swjain 115.4 Added check for formula_type_id -535 and -393
26 * ===========================================================================
27 */
28
29 --
30 -- Global variables declaration.
31 --
32 g_package varchar2(40) := 'ben_fastformula_check';
33
34 ---
35 --- Types Declaration
36 ---
37 TYPE ff_rec IS RECORD (table_name VARCHAR2(100), column_name VARCHAR2(100));
38 TYPE ff_table IS TABLE OF ff_rec INDEX BY binary_integer;
39 table_list ff_table;
40
41 --
42 -- ============================================================================
43 -- <<Procedure: populate_table_list>>
44 -- Description:
45 -- Called by chk_formula_exists_in_ben function to populate the table list
46 -- depending on the formula_type_id.
47 -- ============================================================================
48 --
49 PROCEDURE populate_table_list(p_formula_type_id IN NUMBER) IS
50 L_proc varchar2(80) := g_package||'.populate_table_list';
51 BEGIN
52 hr_utility.set_location ('Entering '|| l_proc,5);
53
54 -- to ensure the table is empty before use
55 table_list.DELETE;
56
57 IF p_formula_type_id = -550 THEN
58 /* Formula Type */
59 --
60 hr_utility.set_location(' TO check table_nameBEN_PGM_F column_name SCORES_CALC_RL',10);
61 table_list(1).table_name := 'BEN_PGM_F';
62 table_list(1).column_name := 'SCORES_CALC_RL';
63 --
64 ELSIF p_formula_type_id = -549 THEN
65 /* Formula Type Rate Periodization */
66 --
67 hr_utility.set_location(' TO check table_nameBEN_ACTY_BASE_RT_F column_name RATE_PERIODIZATION_RL',10);
68 table_list(1).table_name := 'BEN_ACTY_BASE_RT_F';
69 table_list(1).column_name := 'RATE_PERIODIZATION_RL';
70 --
71 --
72 hr_utility.set_location(' TO check table_nameBEN_ENRT_PERD column_name HRCHY_RL',10);
73 table_list(2).table_name := 'BEN_ENRT_PERD';
74 table_list(2).column_name := 'HRCHY_RL';
75 --
76 ELSIF p_formula_type_id = -548 THEN
77 /* Formula Type Scheduled Hours */
78 --
79 hr_utility.set_location(' TO check table_nameBEN_SCHEDD_HRS_RT_F column_name SCHEDD_HRS_RL',10);
80 table_list(1).table_name := 'BEN_SCHEDD_HRS_RT_F';
81 table_list(1).column_name := 'SCHEDD_HRS_RL';
82 --
83 ELSIF p_formula_type_id = -535 THEN
84 /* Formula Type Extract Post Process */
85 --
86 hr_utility.set_location(' TO check table_nameBEN_EXT_DFN column_name EXT_POST_PRCS_RL',10);
87 table_list(1).table_name := 'BEN_EXT_DFN';
88 table_list(1).column_name := 'EXT_POST_PRCS_RL';
89 --
90 ELSIF p_formula_type_id = -534 THEN
91 /* Formula Type Prorate Annual Election Value */
92 --
93 hr_utility.set_location(' TO check table_nameBEN_ACTY_BASE_RT_F column_name PRORT_MN_ANN_ELCN_VAL_RL',10);
94 table_list(1).table_name := 'BEN_ACTY_BASE_RT_F';
95 table_list(1).column_name := 'PRORT_MN_ANN_ELCN_VAL_RL';
96 --
97 --
98 hr_utility.set_location(' TO check table_nameBEN_ACTY_BASE_RT_F column_name PRORT_MX_ANN_ELCN_VAL_RL',10);
99 table_list(2).table_name := 'BEN_ACTY_BASE_RT_F';
100 table_list(2).column_name := 'PRORT_MX_ANN_ELCN_VAL_RL';
101 --
102 ELSIF p_formula_type_id = -533 THEN
103 /* Formula Type Default Excess Treatment */
104 --
105 hr_utility.set_location(' TO check table_nameBEN_BNFT_PRVDR_POOL_F column_name DFLT_EXCS_TRTMT_RL',10);
106 table_list(1).table_name := 'BEN_BNFT_PRVDR_POOL_F';
107 table_list(1).column_name := 'DFLT_EXCS_TRTMT_RL';
108 --
109 ELSIF p_formula_type_id = -532 THEN
110 /* Formula Type Rollover Value */
111 --
112 hr_utility.set_location(' TO check table_nameBEN_BNFT_POOL_RLOVR_RQMT_F column_name RLOVR_VAL_RL',10);
113 table_list(1).table_name := 'BEN_BNFT_POOL_RLOVR_RQMT_F';
114 table_list(1).column_name := 'RLOVR_VAL_RL';
115 --
116 ELSIF p_formula_type_id = -529 THEN
117 /* Formula Type Variable Rate Add On Calculation */
118 --
119 hr_utility.set_location(' TO check table_nameBEN_ACTL_PREM_F column_name VRBL_RT_ADD_ON_CALC_RL',10);
120 table_list(1).table_name := 'BEN_ACTL_PREM_F';
121 table_list(1).column_name := 'VRBL_RT_ADD_ON_CALC_RL';
122 --
123 ELSIF p_formula_type_id = -528 THEN
124 /* Formula Type Partial Month Proration Rule */
125 --
126 hr_utility.set_location(' TO check table_nameBEN_PRTL_MO_RT_PRTN_VAL_F column_name PRTL_MO_PRORTN_RL',10);
127 table_list(1).table_name := 'BEN_PRTL_MO_RT_PRTN_VAL_F';
128 table_list(1).column_name := 'PRTL_MO_PRORTN_RL';
129 --
130 ELSIF p_formula_type_id = -527 THEN
131 /* Formula Type Maximum Period of Enrollment Determination Date */
132 --
133 hr_utility.set_location(' TO check table_nameBEN_ELIG_TO_PRTE_RSN_F column_name MX_POE_DET_DT_RL',10);
134 table_list(1).table_name := 'BEN_ELIG_TO_PRTE_RSN_F';
135 table_list(1).column_name := 'MX_POE_DET_DT_RL';
136 --
137 --
138 hr_utility.set_location(' TO check table_nameBEN_PRTN_ELIG_F column_name MX_POE_DET_DT_RL',10);
139 table_list(2).table_name := 'BEN_PRTN_ELIG_F';
140 table_list(2).column_name := 'MX_POE_DET_DT_RL';
141 --
142 ELSIF p_formula_type_id = -526 THEN
143 /* Formula Type Maximum Period of Enrollment */
144 --
145 hr_utility.set_location(' TO check table_nameBEN_ELIG_TO_PRTE_RSN_F column_name MX_POE_RL',10);
146 table_list(1).table_name := 'BEN_ELIG_TO_PRTE_RSN_F';
147 table_list(1).column_name := 'MX_POE_RL';
148 --
149 --
150 hr_utility.set_location(' TO check table_nameBEN_PRTN_ELIG_F column_name MX_POE_RL',10);
151 table_list(2).table_name := 'BEN_PRTN_ELIG_F';
152 table_list(2).column_name := 'MX_POE_RL';
153 --
154 ELSIF p_formula_type_id = -518 THEN
155 /* Formula Type Waiting Period Value and UOM */
156 --
157 hr_utility.set_location(' TO check table_nameBEN_ELIG_TO_PRTE_RSN_F column_name WAIT_PERD_RL',10);
158 table_list(1).table_name := 'BEN_ELIG_TO_PRTE_RSN_F';
159 table_list(1).column_name := 'WAIT_PERD_RL';
160 --
161 --
162 hr_utility.set_location(' TO check table_nameBEN_PL_F column_name MX_WTG_PERD_RL',10);
163 table_list(2).table_name := 'BEN_PL_F';
164 table_list(2).column_name := 'MX_WTG_PERD_RL';
165 --
166 --
167 hr_utility.set_location(' TO check table_nameBEN_PRTN_ELIG_F column_name WAIT_PERD_RL',10);
168 table_list(3).table_name := 'BEN_PRTN_ELIG_F';
169 table_list(3).column_name := 'WAIT_PERD_RL';
170 --
171 ELSIF p_formula_type_id = -517 THEN
172 /* Formula Type Compensation Calculation */
173 --
174 hr_utility.set_location(' TO check table_nameBEN_COMP_LVL_FCTR column_name COMP_CALC_RL',10);
175 table_list(1).table_name := 'BEN_COMP_LVL_FCTR';
176 table_list(1).column_name := 'COMP_CALC_RL';
177 --
178 -- -517 for salary_calc_mthd_rl is not confirmed.
179 --
180 hr_utility.set_location(' TO check table_nameBEN_PGM_F column_name SALARY_CALC_MTHD_RL',10);
181 table_list(2).table_name := 'BEN_PGM_F';
182 table_list(2).column_name := 'SALARY_CALC_MTHD_RL';
183 --
184 ELSIF p_formula_type_id = -516 THEN
185 /* Formula Type Hours Worked Calculation */
186 --
187 hr_utility.set_location(' TO check table_nameBEN_HRS_WKD_IN_PERD_FCTR column_name HRS_WKD_CALC_RL',10);
188 table_list(1).table_name := 'BEN_HRS_WKD_IN_PERD_FCTR';
189 table_list(1).column_name := 'HRS_WKD_CALC_RL';
190 --
191 ELSIF p_formula_type_id = -515 THEN
192 /* Formula Type Premium Upper Limit */
193 --
194 hr_utility.set_location(' TO check table_nameBEN_ACTL_PREM_F column_name UPR_LMT_CALC_RL',10);
195 table_list(1).table_name := 'BEN_ACTL_PREM_F';
196 table_list(1).column_name := 'UPR_LMT_CALC_RL';
197 --
198 ELSIF p_formula_type_id = -514 THEN
199 /* Formula Type Coverage Upper Limit */
200 --
201 hr_utility.set_location(' TO check table_nameBEN_CVG_AMT_CALC_MTHD_F column_name UPR_LMT_CALC_RL',10);
202 table_list(1).table_name := 'BEN_CVG_AMT_CALC_MTHD_F';
203 table_list(1).column_name := 'UPR_LMT_CALC_RL';
204 --
205 ELSIF p_formula_type_id = -513 THEN
206 /* Formula Type Required Period of Enrollment */
207 --
208 hr_utility.set_location(' TO check table_nameBEN_OIPL_F column_name RQD_PERD_ENRT_NENRT_RL',10);
209 table_list(1).table_name := 'BEN_OIPL_F';
210 table_list(1).column_name := 'RQD_PERD_ENRT_NENRT_RL';
211 --
212 --
213 hr_utility.set_location(' TO check table_nameBEN_PL_F column_name RQD_PERD_ENRT_NENRT_RL',10);
214 table_list(2).table_name := 'BEN_PL_F';
215 table_list(2).column_name := 'RQD_PERD_ENRT_NENRT_RL';
216 --
217 --
218 hr_utility.set_location(' TO check table_nameBEN_OPT_F column_name RQD_PERD_ENRT_NENRT_RL',10);
219 table_list(3).table_name := 'BEN_OPT_F';
220 table_list(3).column_name := 'RQD_PERD_ENRT_NENRT_RL';
221 --
222 --
223 hr_utility.set_location(' TO check table_nameBEN_PTIP_F column_name RQD_PERD_ENRT_NENRT_RL',10);
224 table_list(4).table_name := 'BEN_PTIP_F';
225 table_list(4).column_name := 'RQD_PERD_ENRT_NENRT_RL';
226 --
227 ELSIF p_formula_type_id = -512 THEN
228 /* Formula Type Premium Lower Limit */
229 --
230 hr_utility.set_location(' TO check table_nameBEN_ACTL_PREM_F column_name LWR_LMT_CALC_RL',10);
231 table_list(1).table_name := 'BEN_ACTL_PREM_F';
232 table_list(1).column_name := 'LWR_LMT_CALC_RL';
233 --
234 ELSIF p_formula_type_id = -511 THEN
235 /* Formula Type Coverage Lower Limit */
236 --
237 hr_utility.set_location(' TO check table_nameBEN_CVG_AMT_CALC_MTHD_F column_name LWR_LMT_CALC_RL',10);
238 table_list(1).table_name := 'BEN_CVG_AMT_CALC_MTHD_F';
239 table_list(1).column_name := 'LWR_LMT_CALC_RL';
240 --
241 ELSIF p_formula_type_id = -510 THEN
242 /* Formula Type Length of Service Calculation */
243 --
244 hr_utility.set_location(' TO check table_nameBEN_LOS_FCTR column_name LOS_CALC_RL',10);
245 table_list(1).table_name := 'BEN_LOS_FCTR';
246 table_list(1).column_name := 'LOS_CALC_RL';
247 --
248 ELSIF p_formula_type_id = -508 THEN
249 /* Formula Type Formula Id */
250 --
251 hr_utility.set_location(' TO check table_nameBEN_POP_UP_MESSAGES column_name FORMULA_ID',10);
252 table_list(1).table_name := 'BEN_POP_UP_MESSAGES';
253 table_list(1).column_name := 'FORMULA_ID';
254 --
255 ELSIF p_formula_type_id = -507 THEN
256 /* Formula Type Premium Value Calculation */
257 --
258 hr_utility.set_location(' TO check table_nameBEN_ACTL_PREM_F column_name VAL_CALC_RL',10);
259 table_list(1).table_name := 'BEN_ACTL_PREM_F';
260 table_list(1).column_name := 'VAL_CALC_RL';
261 --
262 --
263 hr_utility.set_location(' TO check table_nameBEN_ACTL_PREM_VRBL_RT_RL_F column_name FORMULA_ID',10);
264 table_list(2).table_name := 'BEN_ACTL_PREM_VRBL_RT_RL_F';
265 table_list(2).column_name := 'FORMULA_ID';
266 --
267 --
268 hr_utility.set_location(' TO check table_nameBEN_BNFT_VRBL_RT_RL_F column_name FORMULA_ID',10);
269 table_list(3).table_name := 'BEN_BNFT_VRBL_RT_RL_F';
270 table_list(3).column_name := 'FORMULA_ID';
271 --
272 --
273 hr_utility.set_location(' TO check table_nameBEN_VRBL_RT_RL_F column_name FORMULA_ID',10);
274 table_list(4).table_name := 'BEN_VRBL_RT_RL_F';
275 table_list(4).column_name := 'FORMULA_ID';
276 --
277 ELSIF p_formula_type_id = -506 THEN
278 /* Formula Type Evaluation rule */
279 --
280 hr_utility.set_location(' TO check table_nameBEN_CLPSE_LF_EVT_F column_name EVAL_RL',10);
281 table_list(1).table_name := 'BEN_CLPSE_LF_EVT_F';
282 table_list(1).column_name := 'EVAL_RL';
283 --
284 ELSIF p_formula_type_id = -505 THEN
285 /* Formula Type Life event reason determination rule */
286 --
287 hr_utility.set_location(' TO check table_nameBEN_CLPSE_LF_EVT_F column_name EVAL_LER_DET_RL',10);
288 table_list(1).table_name := 'BEN_CLPSE_LF_EVT_F';
289 table_list(1).column_name := 'EVAL_LER_DET_RL';
290 --
291 ELSIF p_formula_type_id = -504 THEN
292 /* Formula Type Enrollment Period Start Date */
293 --
294 hr_utility.set_location(' TO check table_nameBEN_LEE_RSN_F column_name ENRT_PERD_STRT_DT_RL',10);
295 table_list(1).table_name := 'BEN_LEE_RSN_F';
296 table_list(1).column_name := 'ENRT_PERD_STRT_DT_RL';
297 --
298 ELSIF p_formula_type_id = -503 THEN
299 /* Formula Type Enrollment Period End Date */
300 --
301 hr_utility.set_location(' TO check table_nameBEN_LEE_RSN_F column_name ENRT_PERD_END_DT_RL',10);
302 table_list(1).table_name := 'BEN_LEE_RSN_F';
303 table_list(1).column_name := 'ENRT_PERD_END_DT_RL';
304 --
305 ELSIF p_formula_type_id = -502 THEN
306 /* Formula Type Waive Certification Required */
307 --
308 hr_utility.set_location(' TO check table_nameBEN_WV_PRTN_RSN_CTFN_PL_F column_name CTFN_RQD_ELSIF_RL',10);
309 table_list(1).table_name := 'BEN_WV_PRTN_RSN_CTFN_PL_F';
310 table_list(1).column_name := 'CTFN_RQD_ELSIF_RL';
311 --
312 --
313 hr_utility.set_location(' TO check table_nameBEN_WV_PRTN_RSN_CTFN_PTIP_F column_name CTFN_RQD_WHEN_RL',10);
314 table_list(2).table_name := 'BEN_WV_PRTN_RSN_CTFN_PTIP_F';
315 table_list(2).column_name := 'CTFN_RQD_WHEN_RL';
316 --
317 ELSIF p_formula_type_id = -500 THEN
318 /* Formula Type Age Calculation */
319 --
320 hr_utility.set_location(' TO check table_nameBEN_AGE_FCTR column_name AGE_CALC_RL',10);
321 table_list(1).table_name := 'BEN_AGE_FCTR';
322 table_list(1).column_name := 'AGE_CALC_RL';
323 --
324 ELSIF p_formula_type_id = -454 THEN
325 /* Formula Type Default to Assign Pending Action */
326 --
327 hr_utility.set_location(' TO check table_nameBEN_LER_BNFT_RSTRN_F column_name DFLT_TO_ASN_PNDG_CTFN_RL',10);
328 table_list(1).table_name := 'BEN_LER_BNFT_RSTRN_F';
329 table_list(1).column_name := 'DFLT_TO_ASN_PNDG_CTFN_RL';
330 --
331 --
332 hr_utility.set_location(' TO check table_nameBEN_PLIP_F column_name DFLT_TO_ASN_PNDG_CTFN_RL',10);
333 table_list(2).table_name := 'BEN_PLIP_F';
334 table_list(2).column_name := 'DFLT_TO_ASN_PNDG_CTFN_RL';
335 --
336 --
337 hr_utility.set_location(' TO check table_nameBEN_PL_F column_name DFLT_TO_ASN_PNDG_CTFN_RL',10);
338 table_list(3).table_name := 'BEN_PL_F';
339 table_list(3).column_name := 'DFLT_TO_ASN_PNDG_CTFN_RL';
340 --
341 ELSIF p_formula_type_id = -453 THEN
342 /* Formula Type Life Event Reason Timeliness */
343 --
344 hr_utility.set_location(' TO check table_nameBEN_LER_F column_name TMLNS_PERD_RL',10);
345 table_list(1).table_name := 'BEN_LER_F';
346 table_list(1).column_name := 'TMLNS_PERD_RL';
347 --
348 ELSIF p_formula_type_id = -449 THEN
349 /* Formula Type */
350 --
351 hr_utility.set_location(' TO check table_nameBEN_PGM_F column_name DFLT_STEP_RL',10);
352 table_list(1).table_name := 'BEN_PGM_F';
353 table_list(1).column_name := 'DFLT_STEP_RL';
354 --
355 ELSIF p_formula_type_id = -413 THEN
356 /* Formula Type Data element rule*/
357 --
358 hr_utility.set_location(' TO check table_nameBEN_EXT_DATA_ELMT column_name DATA_ELMT_RL',10);
359 table_list(1).table_name := 'BEN_EXT_DATA_ELMT';
360 table_list(1).column_name := 'DATA_ELMT_RL';
361 --
362 ELSIF p_formula_type_id = -393 THEN
363 /* Formula Type Enrollment Opportunity */
364 --
365 hr_utility.set_location(' TO check table_nameBEN_LER_CHG_OIPL_ENRT_F column_name ENRT_RL',10);
366 table_list(1).table_name := 'BEN_LER_CHG_OIPL_ENRT_F';
367 table_list(1).column_name := 'ENRT_RL';
368 --
369 --
370 hr_utility.set_location(' TO check table_nameBEN_LER_CHG_PGM_ENRT_F column_name ENRT_RL',10);
371 table_list(2).table_name := 'BEN_LER_CHG_PGM_ENRT_F';
372 table_list(2).column_name := 'ENRT_RL';
373 --
374 --
375 hr_utility.set_location(' TO check table_nameBEN_LER_CHG_PLIP_ENRT_F column_name ENRT_RL',10);
376 table_list(3).table_name := 'BEN_LER_CHG_PLIP_ENRT_F';
377 table_list(3).column_name := 'ENRT_RL';
378 --
379 --
380 hr_utility.set_location(' TO check table_nameBEN_LER_CHG_PTIP_ENRT_F column_name ENRT_RL',10);
381 table_list(4).table_name := 'BEN_LER_CHG_PTIP_ENRT_F';
382 table_list(4).column_name := 'ENRT_RL';
383 --
384 --
385 hr_utility.set_location(' TO check table_nameBEN_PTIP_F column_name ENRT_RL',10);
386 table_list(5).table_name := 'BEN_PTIP_F';
387 table_list(5).column_name := 'ENRT_RL';
388 --
389 --
390 hr_utility.set_location(' TO check table_nameBEN_PL_F column_name ENRT_RL',10);
391 table_list(6).table_name := 'BEN_PL_F';
392 table_list(6).column_name := 'ENRT_RL';
393 --
394 --
395 hr_utility.set_location(' TO check table_nameBEN_PLIP_F column_name ENRT_RL',10);
396 table_list(7).table_name := 'BEN_PLIP_F';
397 table_list(7).column_name := 'ENRT_RL';
398 --
399 --
400 hr_utility.set_location(' TO check table_nameBEN_PGM_F column_name ENRT_RL',10);
401 table_list(8).table_name := 'BEN_PGM_F';
402 table_list(8).column_name := 'ENRT_RL';
403 --
404 --
405 hr_utility.set_location(' TO check table_nameBEN_OIPL_F column_name ENRT_RL',10);
406 table_list(9).table_name := 'BEN_OIPL_F';
407 table_list(9).column_name := 'ENRT_RL';
408 --
409 --
410 hr_utility.set_location(' TO check table_nameBEN_LER_CHG_PL_NIP_ENRT_F column_name ENRT_RL',10);
411 table_list(10).table_name := 'BEN_LER_CHG_PL_NIP_ENRT_F';
412 table_list(10).column_name := 'ENRT_RL';
413 --
414 --
415 hr_utility.set_location(' TO check table_nameBEN_LER_CHG_PL_NIP_RL_F column_name FORMULA_ID',10);
416 table_list(11).table_name := 'BEN_LER_CHG_PL_NIP_RL_F';
417 table_list(11).column_name := 'FORMULA_ID';
418 --
419 --
420 hr_utility.set_location(' TO check table_nameBEN_LER_CHG_PLIP_ENRT_RL_F column_name FORMULA_ID',10);
421 table_list(12).table_name := 'BEN_LER_CHG_PLIP_ENRT_RL_F';
422 table_list(12).column_name := 'FORMULA_ID';
423 --
424 --
425 hr_utility.set_location(' TO check table_nameBEN_LER_CHG_OIPL_ENRT_RL_F column_name FORMULA_ID',10);
426 table_list(13).table_name := 'BEN_LER_CHG_OIPL_ENRT_RL_F';
427 table_list(13).column_name := 'FORMULA_ID';
428 --
429 --
430 hr_utility.set_location(' TO check table_nameBEN_LEE_RSN_RL_F column_name FORMULA_ID',10);
431 table_list(14).table_name := 'BEN_LEE_RSN_RL_F';
432 table_list(14).column_name := 'FORMULA_ID';
433 --
434 --
435 hr_utility.set_location(' TO check table_nameBEN_SCHEDD_ENRT_RL_F column_name FORMULA_ID',10);
436 table_list(15).table_name := 'BEN_SCHEDD_ENRT_RL_F';
437 table_list(15).column_name := 'FORMULA_ID';
438 --
439 ELSIF p_formula_type_id = -392 THEN
440 /* Formula Type Rate Lower Limit */
441 --
442 hr_utility.set_location(' TO check table_nameBEN_ACTY_BASE_RT_F column_name LWR_LMT_CALC_RL',10);
443 table_list(1).table_name := 'BEN_ACTY_BASE_RT_F';
444 table_list(1).column_name := 'LWR_LMT_CALC_RL';
445 --
446 --
447 hr_utility.set_location(' TO check table_nameBEN_VRBL_RT_PRFL_F column_name ULTMT_LWR_LMT_CALC_RL',10);
448 table_list(2).table_name := 'BEN_VRBL_RT_PRFL_F';
449 table_list(2).column_name := 'ULTMT_LWR_LMT_CALC_RL';
450 --
451 --
452 hr_utility.set_location(' TO check table_nameBEN_VRBL_RT_PRFL_F column_name LWR_LMT_CALC_RL',10);
453 table_list(3).table_name := 'BEN_VRBL_RT_PRFL_F';
454 table_list(3).column_name := 'LWR_LMT_CALC_RL';
455 --
456 ELSIF p_formula_type_id = -372 THEN
457 /* Formula Type Action Type Due Date */
458 --
459 hr_utility.set_location(' TO check table_nameBEN_POPL_ACTN_TYP_F column_name ACTN_TYP_DUE_DT_RL',10);
460 table_list(1).table_name := 'BEN_POPL_ACTN_TYP_F';
461 table_list(1).column_name := 'ACTN_TYP_DUE_DT_RL';
462 --
463 ELSIF p_formula_type_id = -352 THEN
464 /* Formula Type Communication Usage */
465 --
466 hr_utility.set_location(' TO check table_nameBEN_CM_TYP_USG_F column_name CM_USG_RL',10);
467 table_list(1).table_name := 'BEN_CM_TYP_USG_F';
468 table_list(1).column_name := 'CM_USG_RL';
469 --
470 ELSIF p_formula_type_id = -332 THEN
471 /* Formula Type Communication Type */
472 --
473 hr_utility.set_location(' TO check table_nameBEN_CM_TYP_F column_name CM_TYP_RL',10);
474 table_list(1).table_name := 'BEN_CM_TYP_F';
475 table_list(1).column_name := 'CM_TYP_RL';
476 --
477 ELSIF p_formula_type_id = -313 THEN
478 /* Formula Type Inspection Required */
479 --
480 hr_utility.set_location(' TO check table_nameBEN_CM_TYP_F column_name INSPN_RQD_RL',10);
481 table_list(1).table_name := 'BEN_CM_TYP_F';
482 table_list(1).column_name := 'INSPN_RQD_RL';
483 --
484 ELSIF p_formula_type_id = -312 THEN
485 /* Formula Type Communication Appropriate */
486 --
487 hr_utility.set_location(' TO check table_nameBEN_CM_TYP_TRGR_F column_name CM_TYP_TRGR_RL',10);
488 table_list(1).table_name := 'BEN_CM_TYP_TRGR_F';
489 table_list(1).column_name := 'CM_TYP_TRGR_RL';
490 --
491 ELSIF p_formula_type_id = -294 THEN
492 /* Formula Type Participant Eligible to Rollover */
493 --
494 hr_utility.set_location(' TO check table_nameBEN_BNFT_POOL_RLOVR_RQMT_F column_name PRTT_ELIG_RLOVR_RL',10);
495 table_list(1).table_name := 'BEN_BNFT_POOL_RLOVR_RQMT_F';
496 table_list(1).column_name := 'PRTT_ELIG_RLOVR_RL';
497 --
498 ELSIF p_formula_type_id = -293 THEN
499 /* Formula Type Rate Upper Limit */
500 --
501 hr_utility.set_location(' TO check table_nameBEN_ACTY_BASE_RT_F column_name UPR_LMT_CALC_RL',10);
502 table_list(1).table_name := 'BEN_ACTY_BASE_RT_F';
503 table_list(1).column_name := 'UPR_LMT_CALC_RL';
504 --
505 --
506 hr_utility.set_location(' TO check table_nameBEN_VRBL_RT_PRFL_F column_name UPR_LMT_CALC_RL',10);
507 table_list(2).table_name := 'BEN_VRBL_RT_PRFL_F';
508 table_list(2).column_name := 'UPR_LMT_CALC_RL';
509 --
510 --
511 hr_utility.set_location(' TO check table_nameBEN_VRBL_RT_PRFL_F column_name ULTMT_UPR_LMT_CALC_RL',10);
512 table_list(3).table_name := 'BEN_VRBL_RT_PRFL_F';
513 table_list(3).column_name := 'ULTMT_UPR_LMT_CALC_RL';
514 --
515 ELSIF p_formula_type_id = -215 THEN
516 /* Formula Type Postelection Edit */
517 --
518 hr_utility.set_location(' TO check table_nameBEN_OIPL_F column_name POSTELCN_EDIT_RL',10);
519 table_list(1).table_name := 'BEN_OIPL_F';
520 table_list(1).column_name := 'POSTELCN_EDIT_RL';
521 --
522 --
523 hr_utility.set_location(' TO check table_nameBEN_PL_F column_name POSTELCN_EDIT_RL',10);
524 table_list(2).table_name := 'BEN_PL_F';
525 table_list(2).column_name := 'POSTELCN_EDIT_RL';
526 --
527 --
528 hr_utility.set_location(' TO check table_nameBEN_PLIP_F column_name POSTELCN_EDIT_RL',10);
529 table_list(3).table_name := 'BEN_PLIP_F';
530 table_list(3).column_name := 'POSTELCN_EDIT_RL';
531 --
532 --
533 hr_utility.set_location(' TO check table_nameBEN_PTIP_F column_name POSTELCN_EDIT_RL',10);
534 table_list(4).table_name := 'BEN_PTIP_F';
535 table_list(4).column_name := 'POSTELCN_EDIT_RL';
536 --
537 ELSIF p_formula_type_id = -214 THEN
538 /* Formula Type Person Selection rule*/
539 --
540 hr_utility.set_location(' TO check table_nameBEN_BENEFIT_ACTIONS column_name PERSON_SELECTION_RL',10);
541 table_list(1).table_name := 'BEN_BENEFIT_ACTIONS';
542 table_list(1).column_name := 'PERSON_SELECTION_RL';
543 --
544 ELSIF p_formula_type_id = -213 THEN
545 /* Formula Type */
546 --
547 hr_utility.set_location(' TO check table_nameBEN_BENEFIT_ACTIONS column_name COMP_SELECTION_RL',10);
548 table_list(1).table_name := 'BEN_BENEFIT_ACTIONS';
549 table_list(1).column_name := 'COMP_SELECTION_RL';
550 --
551 ELSIF p_formula_type_id = -174 THEN
552 /* Formula Type Compensation Determination Date */
553 --
554 hr_utility.set_location(' TO check table_nameBEN_COMP_LVL_FCTR column_name COMP_LVL_DET_RL',10);
555 table_list(1).table_name := 'BEN_COMP_LVL_FCTR';
556 table_list(1).column_name := 'COMP_LVL_DET_RL';
557 --
558 ELSIF p_formula_type_id = -171 THEN
559 /* Formula Type Rate Value Calculation */
560 --
561 hr_utility.set_location(' TO check table_nameBEN_ACTY_BASE_RT_F column_name INPUT_VA_CALC_RL',10);
562 table_list(1).table_name := 'BEN_ACTY_BASE_RT_F';
563 table_list(1).column_name := 'INPUT_VA_CALC_RL';
564 --
565 --
566 hr_utility.set_location(' TO check table_nameBEN_VRBL_RT_PRFL_F column_name VAL_CALC_RL',10);
567 table_list(2).table_name := 'BEN_VRBL_RT_PRFL_F';
568 table_list(2).column_name := 'VAL_CALC_RL';
569 --
570 --
571 hr_utility.set_location(' TO check table_nameBEN_ACTY_BASE_RT_F column_name VAL_CALC_RL',10);
572 table_list(3).table_name := 'BEN_ACTY_BASE_RT_F';
573 table_list(3).column_name := 'VAL_CALC_RL';
574 --
575 --
576 hr_utility.set_location(' TO check table_nameBEN_BNFT_VRBL_RT_RL_F column_name FORMULA_ID',10);
577 table_list(4).table_name := 'BEN_BNFT_VRBL_RT_RL_F';
578 table_list(4).column_name := 'FORMULA_ID';
579 --
580 --
581 hr_utility.set_location(' TO check table_nameBEN_VRBL_RT_RL_F column_name FORMULA_ID',10);
582 table_list(5).table_name := 'BEN_VRBL_RT_RL_F';
583 table_list(5).column_name := 'FORMULA_ID';
584 --
585 ELSIF p_formula_type_id = -170 THEN
586 /* Formula Type Length of Service Determination Date */
587 --
588 hr_utility.set_location(' TO check table_nameBEN_LOS_FCTR column_name LOS_DET_RL',10);
589 table_list(1).table_name := 'BEN_LOS_FCTR';
590 table_list(1).column_name := 'LOS_DET_RL';
591 --
592 --
593 hr_utility.set_location(' TO check table_nameBEN_VSTG_SCHED_F column_name LOS_DET_RL',10);
594 table_list(2).table_name := 'BEN_VSTG_SCHED_F';
595 table_list(2).column_name := 'LOS_DET_RL';
596 --
597 --
598 hr_utility.set_location(' TO check table_nameBEN_SCHEDD_HRS_RT_F column_name DETERMINATION_RL',10);
599 table_list(3).table_name := 'BEN_SCHEDD_HRS_RT_F';
600 table_list(3).column_name := 'DETERMINATION_RL';
601 --
602 ELSIF p_formula_type_id = -169 THEN
603 /* Formula Type Rounding */
604 --
605 hr_utility.set_location(' TO check table_nameBEN_ACTL_PREM_F column_name RNDG_RL',10);
606 table_list(1).table_name := 'BEN_ACTL_PREM_F';
607 table_list(1).column_name := 'RNDG_RL';
608 --
609 --
610 hr_utility.set_location(' TO check table_nameBEN_ACTY_BASE_RT_F column_name RNDG_RL',10);
611 table_list(2).table_name := 'BEN_ACTY_BASE_RT_F';
612 table_list(2).column_name := 'RNDG_RL';
613 --
614 --
615 hr_utility.set_location(' TO check table_nameBEN_AGE_FCTR column_name RNDG_RL',10);
616 table_list(3).table_name := 'BEN_AGE_FCTR';
617 table_list(3).column_name := 'RNDG_RL';
618 --
619 --
620 hr_utility.set_location(' TO check table_nameBEN_BNFT_POOL_RLOVR_RQMT_F column_name PCT_RNDG_RL',10);
621 table_list(4).table_name := 'BEN_BNFT_POOL_RLOVR_RQMT_F';
622 table_list(4).column_name := 'PCT_RNDG_RL';
623 --
624 --
625 hr_utility.set_location(' TO check table_nameBEN_BNFT_PRVDR_POOL_F column_name PCT_RNDG_RL',10);
626 table_list(5).table_name := 'BEN_BNFT_PRVDR_POOL_F';
627 table_list(5).column_name := 'PCT_RNDG_RL';
628 --
629 --
630 hr_utility.set_location(' TO check table_nameBEN_BNFT_PRVDR_POOL_F column_name VAL_RNDG_RL',10);
631 table_list(6).table_name := 'BEN_BNFT_PRVDR_POOL_F';
632 table_list(6).column_name := 'VAL_RNDG_RL';
633 --
634 --
635 hr_utility.set_location(' TO check table_nameBEN_VRBL_RT_PRFL_F column_name RNDG_RL',10);
636 table_list(7).table_name := 'BEN_VRBL_RT_PRFL_F';
637 table_list(7).column_name := 'RNDG_RL';
638 --
639 --
640 hr_utility.set_location(' TO check table_nameBEN_PRTL_MO_RT_PRTN_VAL_F column_name RNDG_RL',10);
641 table_list(8).table_name := 'BEN_PRTL_MO_RT_PRTN_VAL_F';
642 table_list(8).column_name := 'RNDG_RL';
643 --
644 --
645 hr_utility.set_location(' TO check table_nameBEN_PCT_FL_TM_FCTR column_name RNDG_RL',10);
646 table_list(9).table_name := 'BEN_PCT_FL_TM_FCTR';
647 table_list(9).column_name := 'RNDG_RL';
648 --
649 --
650 hr_utility.set_location(' TO check table_nameBEN_LOS_FCTR column_name RNDG_RL',10);
651 table_list(10).table_name := 'BEN_LOS_FCTR';
652 table_list(10).column_name := 'RNDG_RL';
653 --
654 --
655 hr_utility.set_location(' TO check table_nameBEN_HRS_WKD_IN_PERD_FCTR column_name RNDG_RL',10);
656 table_list(11).table_name := 'BEN_HRS_WKD_IN_PERD_FCTR';
657 table_list(11).column_name := 'RNDG_RL';
658 --
659 --
660 hr_utility.set_location(' TO check table_nameBEN_ELIG_SCHEDD_HRS_PRTE_F column_name ROUNDING_RL',10);
661 table_list(12).table_name := 'BEN_ELIG_SCHEDD_HRS_PRTE_F';
662 table_list(12).column_name := 'ROUNDING_RL';
663 --
664 --
665 hr_utility.set_location(' TO check table_nameBEN_CVG_AMT_CALC_MTHD_F column_name RNDG_RL',10);
666 table_list(13).table_name := 'BEN_CVG_AMT_CALC_MTHD_F';
667 table_list(13).column_name := 'RNDG_RL';
668 --
669 --
670 hr_utility.set_location(' TO check table_nameBEN_COMP_LVL_FCTR column_name RNDG_RL',10);
671 table_list(14).table_name := 'BEN_COMP_LVL_FCTR';
672 table_list(14).column_name := 'RNDG_RL';
673 --
674 --
675 hr_utility.set_location(' TO check table_nameBEN_BNFT_POOL_RLOVR_RQMT_F column_name VAL_RNDG_RL',10);
676 table_list(15).table_name := 'BEN_BNFT_POOL_RLOVR_RQMT_F';
677 table_list(15).column_name := 'VAL_RNDG_RL';
678 --
679 --
680 hr_utility.set_location(' TO check table_nameBEN_VSTG_LOS_RQMT column_name RNDG_RL',10);
681 table_list(16).table_name := 'BEN_VSTG_LOS_RQMT';
682 table_list(16).column_name := 'RNDG_RL';
683 --
684 --
685 hr_utility.set_location(' TO check table_nameBEN_VSTG_AGE_RQMT column_name RNDG_RL',10);
686 table_list(17).table_name := 'BEN_VSTG_AGE_RQMT';
687 table_list(17).column_name := 'RNDG_RL';
688 --
689 --
690 hr_utility.set_location(' TO check table_nameBEN_SCHEDD_HRS_RT_F column_name ROUNDING_RL',10);
691 table_list(18).table_name := 'BEN_SCHEDD_HRS_RT_F';
692 table_list(18).column_name := 'ROUNDING_RL';
693 --
694 --
695 hr_utility.set_location(' TO check table_nameBEN_POE_RT_F column_name RNDG_RL',10);
696 table_list(19).table_name := 'BEN_POE_RT_F';
697 table_list(19).column_name := 'RNDG_RL';
698 --
699 ELSIF p_formula_type_id = -168 THEN
700 /* Formula Type Related Person Change Causes Life Event */
701 --
702 hr_utility.set_location(' TO check table_nameBEN_LER_RLTD_PER_CS_LER_F column_name LER_RLTD_PER_CS_CHG_RL',10);
703 table_list(1).table_name := 'BEN_LER_RLTD_PER_CS_LER_F';
704 table_list(1).column_name := 'LER_RLTD_PER_CS_CHG_RL';
705 --
706 --
707 hr_utility.set_location(' TO check table_nameBEN_RLTD_PER_CHG_CS_LER_F column_name RLTD_PER_CHG_CS_LER_RL',10);
708 table_list(2).table_name := 'BEN_RLTD_PER_CHG_CS_LER_F';
709 table_list(2).column_name := 'RLTD_PER_CHG_CS_LER_RL';
710 --
711 ELSIF p_formula_type_id = -167 THEN
712 /* Formula Type */
713 --
714 hr_utility.set_location(' TO check table_nameBEN_PTD_LMT_F column_name PTD_LMT_CALC_RL',10);
715 table_list(1).table_name := 'BEN_PTD_LMT_F';
716 table_list(1).column_name := 'PTD_LMT_CALC_RL';
717 --
718 ELSIF p_formula_type_id = -166 THEN
719 /* Formula Type Partial Year Coverage Restriction */
720 --
721 hr_utility.set_location(' TO check table_nameBEN_PLIP_F column_name PRORT_PRTL_YR_CVG_RSTRN_RL',10);
722 table_list(1).table_name := 'BEN_PLIP_F';
723 table_list(1).column_name := 'PRORT_PRTL_YR_CVG_RSTRN_RL';
724 --
725 --
726 hr_utility.set_location(' TO check table_nameBEN_PL_F column_name PRORT_PRTL_YR_CVG_RSTRN_RL',10);
727 table_list(2).table_name := 'BEN_PL_F';
728 table_list(2).column_name := 'PRORT_PRTL_YR_CVG_RSTRN_RL';
729 --
730 ELSIF p_formula_type_id = -165 THEN
731 /* Formula Type Partial Month Proration Method */
732 --
733 hr_utility.set_location(' TO check table_nameBEN_ACTL_PREM_F column_name PRTL_MO_DET_MTHD_RL',10);
734 table_list(1).table_name := 'BEN_ACTL_PREM_F';
735 table_list(1).column_name := 'PRTL_MO_DET_MTHD_RL';
736 --
737 --
738 hr_utility.set_location(' TO check table_nameBEN_ACTY_BASE_RT_F column_name PRTL_MO_DET_MTHD_RL',10);
739 table_list(2).table_name := 'BEN_ACTY_BASE_RT_F';
740 table_list(2).column_name := 'PRTL_MO_DET_MTHD_RL';
741 --
742 ELSIF p_formula_type_id = -164 THEN
743 /* Formula Type Minimum Coverage Amount Calculation */
744 --
745 hr_utility.set_location(' TO check table_nameBEN_LER_BNFT_RSTRN_F column_name MN_CVG_RL',10);
746 table_list(1).table_name := 'BEN_LER_BNFT_RSTRN_F';
747 table_list(1).column_name := 'MN_CVG_RL';
748 --
749 --
750 hr_utility.set_location(' TO check table_nameBEN_PL_F column_name MN_CVG_RL',10);
751 table_list(2).table_name := 'BEN_PL_F';
752 table_list(2).column_name := 'MN_CVG_RL';
753 --
754 --
755 hr_utility.set_location(' TO check table_nameBEN_PLIP_F column_name MN_CVG_RL',10);
756 table_list(3).table_name := 'BEN_PLIP_F';
757 table_list(3).column_name := 'MN_CVG_RL';
758 --
759 ELSIF p_formula_type_id = -162 THEN
760 /* Formula Type Maximum Waiting Period Date to Use */
761 --
762 hr_utility.set_location(' TO check table_nameBEN_ELIG_TO_PRTE_RSN_F column_name WAIT_PERD_DT_TO_USE_RL',10);
763 table_list(1).table_name := 'BEN_ELIG_TO_PRTE_RSN_F';
764 table_list(1).column_name := 'WAIT_PERD_DT_TO_USE_RL';
765 --
766 --
767 hr_utility.set_location(' TO check table_nameBEN_PRTN_ELIG_F column_name WAIT_PERD_DT_TO_USE_RL',10);
768 table_list(2).table_name := 'BEN_PRTN_ELIG_F';
769 table_list(2).column_name := 'WAIT_PERD_DT_TO_USE_RL';
770 --
771 --
772 hr_utility.set_location(' TO check table_nameBEN_PL_F column_name MX_WTG_DT_TO_USE_RL',10);
773 table_list(3).table_name := 'BEN_PL_F';
774 table_list(3).column_name := 'MX_WTG_DT_TO_USE_RL';
775 --
776 ELSIF p_formula_type_id = -161 THEN
777 /* Formula Type Maximum Coverage Amount Calculation */
778 --
779 hr_utility.set_location(' TO check table_nameBEN_LER_BNFT_RSTRN_F column_name MX_CVG_RL',10);
780 table_list(1).table_name := 'BEN_LER_BNFT_RSTRN_F';
781 table_list(1).column_name := 'MX_CVG_RL';
782 --
783 --
784 hr_utility.set_location(' TO check table_nameBEN_PLIP_F column_name MX_CVG_RL',10);
785 table_list(2).table_name := 'BEN_PLIP_F';
786 table_list(2).column_name := 'MX_CVG_RL';
787 --
788 --
789 hr_utility.set_location(' TO check table_nameBEN_PL_F column_name MX_CVG_RL',10);
790 table_list(3).table_name := 'BEN_PL_F';
791 table_list(3).column_name := 'MX_CVG_RL';
792 --
793 ELSIF p_formula_type_id = -160 THEN
794 /* Formula Type Matching rate Calculation */
795 --
796 hr_utility.set_location(' TO check table_nameBEN_VRBL_MTCHG_RT_F column_name MTCHG_RT_CALC_RL',10);
797 table_list(1).table_name := 'BEN_VRBL_MTCHG_RT_F';
798 table_list(1).column_name := 'MTCHG_RT_CALC_RL';
799 --
800 --
801 hr_utility.set_location(' TO check table_nameBEN_MTCHG_RT_F column_name MTCHG_RT_CALC_RL',10);
802 table_list(2).table_name := 'BEN_MTCHG_RT_F';
803 table_list(2).column_name := 'MTCHG_RT_CALC_RL';
804 --
805 ELSIF p_formula_type_id = -159 THEN
806 /* Formula Type Mandatory Determination */
807 --
808 hr_utility.set_location(' TO check table_nameBEN_OIPL_F column_name MNDTRY_RL',10);
809 table_list(1).table_name := 'BEN_OIPL_F';
810 table_list(1).column_name := 'MNDTRY_RL';
811 --
812 ELSIF p_formula_type_id = -157 THEN
813 /* Formula Type Evaluate Life Event */
814 --
815 hr_utility.set_location(' TO check table_nameBEN_LER_F column_name LER_EVAL_RL',10);
816 table_list(1).table_name := 'BEN_LER_F';
817 table_list(1).column_name := 'LER_EVAL_RL';
818 --
819 ELSIF p_formula_type_id = -156 THEN
820 /* Formula Type Length of Service Date to Use */
821 --
822 hr_utility.set_location(' TO check table_nameBEN_LOS_FCTR column_name LOS_DT_TO_USE_RL',10);
823 table_list(1).table_name := 'BEN_LOS_FCTR';
824 table_list(1).column_name := 'LOS_DT_TO_USE_RL';
825 --
826 ELSIF p_formula_type_id = -155 THEN
827 /* Formula Type Hours Worked Determination Date */
828 --
829 hr_utility.set_location(' TO check table_nameBEN_ELIG_SCHEDD_HRS_PRTE_F column_name DETERMINATION_RL',10);
830 table_list(1).table_name := 'BEN_ELIG_SCHEDD_HRS_PRTE_F';
831 table_list(1).column_name := 'DETERMINATION_RL';
832 --
833 --
834 hr_utility.set_location(' TO check table_nameBEN_HRS_WKD_IN_PERD_FCTR column_name HRS_WKD_DET_RL',10);
835 table_list(2).table_name := 'BEN_HRS_WKD_IN_PERD_FCTR';
836 table_list(2).column_name := 'HRS_WKD_DET_RL';
837 --
838 --
839 hr_utility.set_location(' TO check table_nameBEN_ELIG_SCHEDD_HRS_PRTE_F column_name SCHEDD_HRS_RL',10);
840 table_list(3).table_name := 'BEN_ELIG_SCHEDD_HRS_PRTE_F';
841 table_list(3).column_name := 'SCHEDD_HRS_RL';
842 --
843 ELSIF p_formula_type_id = -154 THEN
844 /* Formula Type Five percent ownership rule*/
845 --
846 hr_utility.set_location(' TO check table_nameBEN_PL_REGN_F column_name FIVE_PCT_OWNR_RL',10);
847 table_list(1).table_name := 'BEN_PL_REGN_F';
848 table_list(1).column_name := 'FIVE_PCT_OWNR_RL';
849 --
850 ELSIF p_formula_type_id = -153 THEN
851 /* Formula Type Formula Id */
852 --
853 hr_utility.set_location(' TO check table_nameBEN_VRBL_RT_PRFL_RL_F column_name FORMULA_ID',10);
854 table_list(1).table_name := 'BEN_VRBL_RT_PRFL_RL_F';
855 table_list(1).column_name := 'FORMULA_ID';
856 --
857 --
858 hr_utility.set_location(' TO check table_nameBEN_PRTN_ELIGY_RL_F column_name FORMULA_ID',10);
859 table_list(2).table_name := 'BEN_PRTN_ELIGY_RL_F';
860 table_list(2).column_name := 'FORMULA_ID';
861 --
862 --
863 hr_utility.set_location(' TO check table_nameBEN_ELIGY_PRFL_RL_F column_name FORMULA_ID',10);
864 table_list(3).table_name := 'BEN_ELIGY_PRFL_RL_F';
865 table_list(3).column_name := 'FORMULA_ID';
866 --
867 ELSIF p_formula_type_id = -151 THEN
868 /* Formula Type */
869 --
870 hr_utility.set_location(' TO check table_nameBEN_ACTY_RT_DED_SCHED_F column_name DED_SCHED_RL',10);
871 table_list(1).table_name := 'BEN_ACTY_RT_DED_SCHED_F';
872 table_list(1).column_name := 'DED_SCHED_RL';
873 --
874 ELSIF p_formula_type_id = -146 THEN
875 /* Formula Type Automatic Enrollment Method */
876 --
877 hr_utility.set_location(' TO check table_nameBEN_LER_CHG_OIPL_ENRT_F column_name AUTO_ENRT_MTHD_RL',10);
878 table_list(1).table_name := 'BEN_LER_CHG_OIPL_ENRT_F';
879 table_list(1).column_name := 'AUTO_ENRT_MTHD_RL';
880 --
881 --
882 hr_utility.set_location(' TO check table_nameBEN_LER_CHG_PL_NIP_ENRT_F column_name AUTO_ENRT_MTHD_RL',10);
883 table_list(2).table_name := 'BEN_LER_CHG_PL_NIP_ENRT_F';
884 table_list(2).column_name := 'AUTO_ENRT_MTHD_RL';
885 --
886 --
887 hr_utility.set_location(' TO check table_nameBEN_PTIP_F column_name AUTO_ENRT_MTHD_RL',10);
888 table_list(3).table_name := 'BEN_PTIP_F';
889 table_list(3).column_name := 'AUTO_ENRT_MTHD_RL';
890 --
891 --
892 hr_utility.set_location(' TO check table_nameBEN_PL_F column_name AUTO_ENRT_MTHD_RL',10);
893 table_list(4).table_name := 'BEN_PL_F';
894 table_list(4).column_name := 'AUTO_ENRT_MTHD_RL';
895 --
896 --
897 hr_utility.set_location(' TO check table_nameBEN_PLIP_F column_name AUTO_ENRT_MTHD_RL',10);
898 table_list(5).table_name := 'BEN_PLIP_F';
899 table_list(5).column_name := 'AUTO_ENRT_MTHD_RL';
900 --
901 --
902 hr_utility.set_location(' TO check table_nameBEN_PGM_F column_name AUTO_ENRT_MTHD_RL',10);
903 table_list(6).table_name := 'BEN_PGM_F';
904 table_list(6).column_name := 'AUTO_ENRT_MTHD_RL';
905 --
906 --
907 hr_utility.set_location(' TO check table_nameBEN_OIPL_F column_name AUTO_ENRT_MTHD_RL',10);
908 table_list(7).table_name := 'BEN_OIPL_F';
909 table_list(7).column_name := 'AUTO_ENRT_MTHD_RL';
910 --
911 --
912 hr_utility.set_location(' TO check table_nameBEN_LER_CHG_PGM_ENRT_F column_name AUTO_ENRT_MTHD_RL',10);
913 table_list(8).table_name := 'BEN_LER_CHG_PGM_ENRT_F';
914 table_list(8).column_name := 'AUTO_ENRT_MTHD_RL';
915 --
916 --
917 hr_utility.set_location(' TO check table_nameBEN_LER_CHG_PLIP_ENRT_F column_name AUTO_ENRT_MTHD_RL',10);
918 table_list(9).table_name := 'BEN_LER_CHG_PLIP_ENRT_F';
919 table_list(9).column_name := 'AUTO_ENRT_MTHD_RL';
920 --
921 ELSIF p_formula_type_id = -145 THEN
922 /* Formula Type Age Determination Date */
923 --
924 hr_utility.set_location(' TO check table_nameBEN_AGE_FCTR column_name AGE_DET_RL',10);
925 table_list(1).table_name := 'BEN_AGE_FCTR';
926 table_list(1).column_name := 'AGE_DET_RL';
927 --
928 ELSIF p_formula_type_id = -143 THEN
929 /* Formula Type Enrollment Certification Required */
930 --
931 hr_utility.set_location(' TO check table_nameBEN_BNFT_RSTRN_CTFN_F column_name CTFN_RQD_ELSIF_RL',10);
932 table_list(1).table_name := 'BEN_BNFT_RSTRN_CTFN_F';
933 table_list(1).column_name := 'CTFN_RQD_ELSIF_RL';
934 --
935 --
936 hr_utility.set_location(' TO check table_nameBEN_ENRT_CTFN_F column_name CTFN_RQD_ELSIF_RL',10);
937 table_list(2).table_name := 'BEN_ENRT_CTFN_F';
938 table_list(2).column_name := 'CTFN_RQD_ELSIF_RL';
939 --
940 --
941 hr_utility.set_location(' TO check table_nameBEN_LER_RQRS_ENRT_CTFN_F column_name CTFN_RQD_ELSIF_RL',10);
942 table_list(3).table_name := 'BEN_LER_RQRS_ENRT_CTFN_F';
943 table_list(3).column_name := 'CTFN_RQD_ELSIF_RL';
944 --
945 --
946 hr_utility.set_location(' TO check table_nameBEN_LER_ENRT_CTFN_F column_name CTFN_RQD_ELSIF_RL',10);
947 table_list(4).table_name := 'BEN_LER_ENRT_CTFN_F';
948 table_list(4).column_name := 'CTFN_RQD_ELSIF_RL';
949 --
950 --
951 hr_utility.set_location(' TO check table_nameBEN_CNTNU_PRTN_CTFN_TYP_F column_name CTFN_RQD_WHEN_RL',10);
952 table_list(5).table_name := 'BEN_CNTNU_PRTN_CTFN_TYP_F';
953 table_list(5).column_name := 'CTFN_RQD_WHEN_RL';
954 --
955 --
956 hr_utility.set_location(' TO check table_nameBEN_ACTY_BASE_RT_CTFN_F column_name CTFN_RQD_WHEN_RL',10);
957 table_list(6).table_name := 'BEN_ACTY_BASE_RT_CTFN_F';
958 table_list(6).column_name := 'CTFN_RQD_WHEN_RL';
959 --
960 ELSIF p_formula_type_id = -142 THEN
961 /* Formula Type Payment Must Be Received */
962 --
963 hr_utility.set_location(' TO check table_nameBEN_CNTNG_PRTN_ELIG_PRFL_F column_name PYMT_MUST_BE_RCVD_RL',10);
964 table_list(1).table_name := 'BEN_CNTNG_PRTN_ELIG_PRFL_F';
965 table_list(1).column_name := 'PYMT_MUST_BE_RCVD_RL';
966 --
967 --
968 hr_utility.set_location(' TO check table_nameBEN_CNTNG_PRTN_PRFL_RT_F column_name PYMT_MUST_BE_RCVD_RL',10);
969 table_list(2).table_name := 'BEN_CNTNG_PRTN_PRFL_RT_F';
970 table_list(2).column_name := 'PYMT_MUST_BE_RCVD_RL';
971 --
972 ELSIF p_formula_type_id = -83 THEN
973 /* Formula Type Participation Eligibility End Date */
974 --
975 hr_utility.set_location(' TO check table_nameBEN_ELIG_TO_PRTE_RSN_F column_name PRTN_EFF_END_DT_RL',10);
976 table_list(1).table_name := 'BEN_ELIG_TO_PRTE_RSN_F';
977 table_list(1).column_name := 'PRTN_EFF_END_DT_RL';
978 --
979 --
980 hr_utility.set_location(' TO check table_nameBEN_PRTN_ELIG_F column_name PRTN_EFF_END_DT_RL',10);
981 table_list(2).table_name := 'BEN_PRTN_ELIG_F';
982 table_list(2).column_name := 'PRTN_EFF_END_DT_RL';
983 --
984 ELSIF p_formula_type_id = -82 THEN
985 /* Formula Type Participation Eligibility Start Date */
986 --
987 hr_utility.set_location(' TO check table_nameBEN_ELIG_TO_PRTE_RSN_F column_name PRTN_EFF_STRT_DT_RL',10);
988 table_list(1).table_name := 'BEN_ELIG_TO_PRTE_RSN_F';
989 table_list(1).column_name := 'PRTN_EFF_STRT_DT_RL';
990 --
991 --
992 hr_utility.set_location(' TO check table_nameBEN_PRTN_ELIG_F column_name PRTN_EFF_STRT_DT_RL',10);
993 table_list(2).table_name := 'BEN_PRTN_ELIG_F';
994 table_list(2).column_name := 'PRTN_EFF_STRT_DT_RL';
995 --
996 ELSIF p_formula_type_id = -67 THEN
997 /* Formula Type Rate End Date */
998 --
999 hr_utility.set_location(' TO check table_nameBEN_ENRT_PERD column_name RT_END_DT_RL',10);
1000 table_list(1).table_name := 'BEN_ENRT_PERD';
1001 table_list(1).column_name := 'RT_END_DT_RL';
1002 --
1003 --
1004 hr_utility.set_location(' TO check table_nameBEN_LEE_RSN_F column_name RT_END_DT_RL',10);
1005 table_list(2).table_name := 'BEN_LEE_RSN_F';
1006 table_list(2).column_name := 'RT_END_DT_RL';
1007 --
1008 --
1009 hr_utility.set_location(' TO check table_nameBEN_PTIP_F column_name RT_END_DT_RL',10);
1010 table_list(3).table_name := 'BEN_PTIP_F';
1011 table_list(3).column_name := 'RT_END_DT_RL';
1012 --
1013 --
1014 hr_utility.set_location(' TO check table_nameBEN_PLIP_F column_name RT_END_DT_RL',10);
1015 table_list(4).table_name := 'BEN_PLIP_F';
1016 table_list(4).column_name := 'RT_END_DT_RL';
1017 --
1018 --
1019 hr_utility.set_location(' TO check table_nameBEN_PL_F column_name RT_END_DT_RL',10);
1020 table_list(5).table_name := 'BEN_PL_F';
1021 table_list(5).column_name := 'RT_END_DT_RL';
1022 --
1023 --
1024 hr_utility.set_location(' TO check table_nameBEN_PGM_F column_name RT_END_DT_RL',10);
1025 table_list(6).table_name := 'BEN_PGM_F';
1026 table_list(6).column_name := 'RT_END_DT_RL';
1027 --
1028 --
1029 hr_utility.set_location(' TO check table_nameBEN_ENRT_PERD_FOR_PL_F column_name RT_END_DT_RL',10);
1030 table_list(7).table_name := 'BEN_ENRT_PERD_FOR_PL_F';
1031 table_list(7).column_name := 'RT_END_DT_RL';
1032 --
1033 ELSIF p_formula_type_id = -66 THEN
1034 /* Formula Type Rate Start Date */
1035 --
1036 hr_utility.set_location(' TO check table_nameBEN_ENRT_PERD column_name RT_STRT_DT_RL',10);
1037 table_list(1).table_name := 'BEN_ENRT_PERD';
1038 table_list(1).column_name := 'RT_STRT_DT_RL';
1039 --
1040 --
1041 hr_utility.set_location(' TO check table_nameBEN_ENRT_RT column_name RT_STRT_DT_RL',10);
1042 table_list(2).table_name := 'BEN_ENRT_RT';
1043 table_list(2).column_name := 'RT_STRT_DT_RL';
1044 --
1045 --
1046 hr_utility.set_location(' TO check table_nameBEN_ENRT_PERD_FOR_PL_F column_name RT_STRT_DT_RL',10);
1047 table_list(3).table_name := 'BEN_ENRT_PERD_FOR_PL_F';
1048 table_list(3).column_name := 'RT_STRT_DT_RL';
1049 --
1050 --
1051 hr_utility.set_location(' TO check table_nameBEN_PTIP_F column_name RT_STRT_DT_RL',10);
1052 table_list(4).table_name := 'BEN_PTIP_F';
1053 table_list(4).column_name := 'RT_STRT_DT_RL';
1054 --
1055 --
1056 hr_utility.set_location(' TO check table_nameBEN_PL_F column_name RT_STRT_DT_RL',10);
1057 table_list(5).table_name := 'BEN_PL_F';
1058 table_list(5).column_name := 'RT_STRT_DT_RL';
1059 --
1060 --
1061 hr_utility.set_location(' TO check table_nameBEN_PLIP_F column_name RT_STRT_DT_RL',10);
1062 table_list(6).table_name := 'BEN_PLIP_F';
1063 table_list(6).column_name := 'RT_STRT_DT_RL';
1064 --
1065 --
1066 hr_utility.set_location(' TO check table_nameBEN_PGM_F column_name RT_STRT_DT_RL',10);
1067 table_list(7).table_name := 'BEN_PGM_F';
1068 table_list(7).column_name := 'RT_STRT_DT_RL';
1069 --
1070 --
1071 hr_utility.set_location(' TO check table_nameBEN_LEE_RSN_F column_name RT_STRT_DT_RL',10);
1072 table_list(8).table_name := 'BEN_LEE_RSN_F';
1073 table_list(8).column_name := 'RT_STRT_DT_RL';
1074 --
1075 --
1076 hr_utility.set_location(' TO check table_nameBEN_ENRT_RT_RBV column_name RT_STRT_DT_RL',10);
1077 table_list(9).table_name := 'BEN_ENRT_RT_RBV';
1078 table_list(9).column_name := 'RT_STRT_DT_RL';
1079 --
1080 ELSIF p_formula_type_id = -52 THEN
1081 /* Formula Type Payment schedule */
1082 --
1083 hr_utility.set_location(' TO check table_nameBEN_ACTY_RT_PYMT_SCHED_F column_name PYMT_SCHED_RL',10);
1084 table_list(1).table_name := 'BEN_ACTY_RT_PYMT_SCHED_F';
1085 table_list(1).column_name := 'PYMT_SCHED_RL';
1086 --
1087 ELSIF p_formula_type_id = -49 THEN
1088 /* Formula Type Coverage Amount Calculation */
1089 --
1090 hr_utility.set_location(' TO check table_nameBEN_CVG_AMT_CALC_MTHD_F column_name VAL_CALC_RL',10);
1091 table_list(1).table_name := 'BEN_CVG_AMT_CALC_MTHD_F';
1092 table_list(1).column_name := 'VAL_CALC_RL';
1093 --
1094 --
1095 hr_utility.set_location(' TO check table_nameBEN_BNFT_VRBL_RT_RL_F column_name FORMULA_ID',10);
1096 table_list(2).table_name := 'BEN_BNFT_VRBL_RT_RL_F';
1097 table_list(2).column_name := 'FORMULA_ID';
1098 --
1099 --
1100 hr_utility.set_location(' TO check table_nameBEN_VRBL_RT_RL_F column_name FORMULA_ID',10);
1101 table_list(3).table_name := 'BEN_VRBL_RT_RL_F';
1102 table_list(3).column_name := 'FORMULA_ID';
1103 --
1104 ELSIF p_formula_type_id = -48 THEN
1105 /* Formula Type Partial Month Effective Date Determination */
1106 --
1107 hr_utility.set_location(' TO check table_nameBEN_ACTY_BASE_RT_F column_name PRTL_MO_EFF_DT_DET_RL',10);
1108 table_list(1).table_name := 'BEN_ACTY_BASE_RT_F';
1109 table_list(1).column_name := 'PRTL_MO_EFF_DT_DET_RL';
1110 --
1111 ELSIF p_formula_type_id = -46 THEN
1112 /* Formula Type Person Change Causes Life Event */
1113 --
1114 hr_utility.set_location(' TO check table_nameBEN_LER_PER_INFO_CS_LER_F column_name LER_PER_INFO_CS_LER_RL',10);
1115 table_list(1).table_name := 'BEN_LER_PER_INFO_CS_LER_F';
1116 table_list(1).column_name := 'LER_PER_INFO_CS_LER_RL';
1117 --
1118 --
1119 hr_utility.set_location(' TO check table_nameBEN_PER_INFO_CHG_CS_LER_F column_name PER_INFO_CHG_CS_LER_RL',10);
1120 table_list(2).table_name := 'BEN_PER_INFO_CHG_CS_LER_F';
1121 table_list(2).column_name := 'PER_INFO_CHG_CS_LER_RL';
1122 --
1123 ELSIF p_formula_type_id = -45 THEN
1124 /* Formula Type To Be Sent Date */
1125 --
1126 hr_utility.set_location(' TO check table_nameBEN_CM_TYP_F column_name TO_BE_SENT_DT_RL',10);
1127 table_list(1).table_name := 'BEN_CM_TYP_F';
1128 table_list(1).column_name := 'TO_BE_SENT_DT_RL';
1129 --
1130 ELSIF p_formula_type_id = -43 THEN
1131 /* Formula Type Certification Allow Reimbursement */
1132 --
1133 hr_utility.set_location(' TO check table_nameBEN_PL_GD_R_SVC_CTFN_F column_name CTFN_RQD_ELSIF_RL',10);
1134 table_list(1).table_name := 'BEN_PL_GD_R_SVC_CTFN_F';
1135 table_list(1).column_name := 'CTFN_RQD_ELSIF_RL';
1136 --
1137 ELSIF p_formula_type_id = -42 THEN
1138 /* Formula Type */
1139 --
1140 hr_utility.set_location(' TO check table_nameBEN_PL_REGN_F column_name CNTR_NNDSCRN_RL',10);
1141 table_list(1).table_name := 'BEN_PL_REGN_F';
1142 table_list(1).column_name := 'CNTR_NNDSCRN_RL';
1143 --
1144 ELSIF p_formula_type_id = -41 THEN
1145 /* Formula Type */
1146 --
1147 hr_utility.set_location(' TO check table_nameBEN_PL_REGN_F column_name CVG_NNDSCRN_RL',10);
1148 table_list(1).table_name := 'BEN_PL_REGN_F';
1149 table_list(1).column_name := 'CVG_NNDSCRN_RL';
1150 --
1151 ELSIF p_formula_type_id = -39 THEN
1152 /* Formula Type */
1153 --
1154 hr_utility.set_location(' TO check table_nameBEN_PL_REGN_F column_name KEY_EE_DET_RL',10);
1155 table_list(1).table_name := 'BEN_PL_REGN_F';
1156 table_list(1).column_name := 'KEY_EE_DET_RL';
1157 --
1158 ELSIF p_formula_type_id = -36 THEN
1159 /* Formula Type Change Dependent Coverage */
1160 --
1161 hr_utility.set_location(' TO check table_nameBEN_LER_CHG_DPNT_CVG_F column_name LER_CHG_DPNT_CVG_RL',10);
1162 table_list(1).table_name := 'BEN_LER_CHG_DPNT_CVG_F';
1163 table_list(1).column_name := 'LER_CHG_DPNT_CVG_RL';
1164 --
1165 ELSIF p_formula_type_id = -35 THEN
1166 /* Formula Type Dependent Eligibility */
1167 --
1168 hr_utility.set_location(' TO check table_nameBEN_APLD_DPNT_CVG_ELIG_PRFL_F column_name APLD_DPNT_CVG_ELIG_RL',10);
1169 table_list(1).table_name := 'BEN_APLD_DPNT_CVG_ELIG_PRFL_F';
1170 table_list(1).column_name := 'APLD_DPNT_CVG_ELIG_RL';
1171 --
1172 --
1173 hr_utility.set_location(' TO check table_nameBEN_DPNT_CVG_ELIGY_PRFL_F column_name DPNT_CVG_ELIG_DET_RL',10);
1174 table_list(2).table_name := 'BEN_DPNT_CVG_ELIGY_PRFL_F';
1175 table_list(2).column_name := 'DPNT_CVG_ELIG_DET_RL';
1176 --
1177 ELSIF p_formula_type_id = -32 THEN
1178 /* Formula Type Default Enrollment */
1179 --
1180 hr_utility.set_location(' TO check table_nameBEN_LER_CHG_OIPL_ENRT_F column_name DFLT_ENRT_RL',10);
1181 table_list(1).table_name := 'BEN_LER_CHG_OIPL_ENRT_F';
1182 table_list(1).column_name := 'DFLT_ENRT_RL';
1183 --
1184 --
1185 hr_utility.set_location(' TO check table_nameBEN_LER_CHG_PGM_ENRT_F column_name DFLT_ENRT_RL',10);
1186 table_list(2).table_name := 'BEN_LER_CHG_PGM_ENRT_F';
1187 table_list(2).column_name := 'DFLT_ENRT_RL';
1188 --
1189 --
1190 hr_utility.set_location(' TO check table_nameBEN_LER_CHG_PLIP_ENRT_F column_name DFLT_ENRT_RL',10);
1191 table_list(3).table_name := 'BEN_LER_CHG_PLIP_ENRT_F';
1192 table_list(3).column_name := 'DFLT_ENRT_RL';
1193 --
1194 --
1195 hr_utility.set_location(' TO check table_nameBEN_LER_CHG_PL_NIP_ENRT_F column_name DFLT_ENRT_RL',10);
1196 table_list(4).table_name := 'BEN_LER_CHG_PL_NIP_ENRT_F';
1197 table_list(4).column_name := 'DFLT_ENRT_RL';
1198 --
1199 --
1200 hr_utility.set_location(' TO check table_nameBEN_PLIP_F column_name DFLT_ENRT_DET_RL',10);
1201 table_list(5).table_name := 'BEN_PLIP_F';
1202 table_list(5).column_name := 'DFLT_ENRT_DET_RL';
1203 --
1204 --
1205 hr_utility.set_location(' TO check table_nameBEN_PTIP_F column_name DFLT_ENRT_DET_RL',10);
1206 table_list(6).table_name := 'BEN_PTIP_F';
1207 table_list(6).column_name := 'DFLT_ENRT_DET_RL';
1208 --
1209 --
1210 hr_utility.set_location(' TO check table_nameBEN_PL_F column_name NIP_DFLT_ENRT_DET_RL',10);
1211 table_list(7).table_name := 'BEN_PL_F';
1212 table_list(7).column_name := 'NIP_DFLT_ENRT_DET_RL';
1213 --
1214 --
1215 hr_utility.set_location(' TO check table_nameBEN_OIPL_F column_name DFLT_ENRT_DET_RL',10);
1216 table_list(8).table_name := 'BEN_OIPL_F';
1217 table_list(8).column_name := 'DFLT_ENRT_DET_RL';
1218 --
1219 --
1220 hr_utility.set_location(' TO check table_nameBEN_LER_CHG_PTIP_ENRT_F column_name DFLT_ENRT_RL',10);
1221 table_list(9).table_name := 'BEN_LER_CHG_PTIP_ENRT_F';
1222 table_list(9).column_name := 'DFLT_ENRT_RL';
1223 --
1224 ELSIF p_formula_type_id = -31 THEN
1225 /* Formula Type Highly computed determination*/
1226 --
1227 hr_utility.set_location(' TO check table_nameBEN_PL_REGN_F column_name HGHLY_COMPD_DET_RL',10);
1228 table_list(1).table_name := 'BEN_PL_REGN_F';
1229 table_list(1).column_name := 'HGHLY_COMPD_DET_RL';
1230 --
1231 ELSIF p_formula_type_id = -30 THEN
1232 /* Formula Type Enrollment End */
1233 --
1234 hr_utility.set_location(' TO check table_nameBEN_ELIG_AGE_CVG_F column_name CVG_THRU_RL',10);
1235 table_list(1).table_name := 'BEN_ELIG_AGE_CVG_F';
1236 table_list(1).column_name := 'CVG_THRU_RL';
1237 --
1238 --
1239 hr_utility.set_location(' TO check table_nameBEN_ELIG_MLTRY_STAT_CVG_F column_name CVG_THRU_RL',10);
1240 table_list(2).table_name := 'BEN_ELIG_MLTRY_STAT_CVG_F';
1241 table_list(2).column_name := 'CVG_THRU_RL';
1242 --
1243 --
1244 hr_utility.set_location(' TO check table_nameBEN_ELIG_MRTL_STAT_CVG_F column_name CVG_THRU_RL',10);
1245 table_list(3).table_name := 'BEN_ELIG_MRTL_STAT_CVG_F';
1246 table_list(3).column_name := 'CVG_THRU_RL';
1247 --
1248 --
1249 hr_utility.set_location(' TO check table_nameBEN_PTIP_F column_name ENRT_CVG_END_DT_RL',10);
1250 table_list(4).table_name := 'BEN_PTIP_F';
1251 table_list(4).column_name := 'ENRT_CVG_END_DT_RL';
1252 --
1253 --
1254 hr_utility.set_location(' TO check table_nameBEN_PL_F column_name ENRT_CVG_END_DT_RL',10);
1255 table_list(5).table_name := 'BEN_PL_F';
1256 table_list(5).column_name := 'ENRT_CVG_END_DT_RL';
1257 --
1258 --
1259 hr_utility.set_location(' TO check table_nameBEN_PLIP_F column_name ENRT_CVG_END_DT_RL',10);
1260 table_list(6).table_name := 'BEN_PLIP_F';
1261 table_list(6).column_name := 'ENRT_CVG_END_DT_RL';
1262 --
1263 --
1264 hr_utility.set_location(' TO check table_nameBEN_PGM_F column_name ENRT_CVG_END_DT_RL',10);
1265 table_list(7).table_name := 'BEN_PGM_F';
1266 table_list(7).column_name := 'ENRT_CVG_END_DT_RL';
1267 --
1268 --
1269 hr_utility.set_location(' TO check table_nameBEN_LER_CHG_DPNT_CVG_F column_name CVG_EFF_END_RL',10);
1270 table_list(8).table_name := 'BEN_LER_CHG_DPNT_CVG_F';
1271 table_list(8).column_name := 'CVG_EFF_END_RL';
1272 --
1273 --
1274 hr_utility.set_location(' TO check table_nameBEN_LEE_RSN_F column_name ENRT_CVG_END_DT_RL',10);
1275 table_list(9).table_name := 'BEN_LEE_RSN_F';
1276 table_list(9).column_name := 'ENRT_CVG_END_DT_RL';
1277 --
1278 --
1279 hr_utility.set_location(' TO check table_nameBEN_ENRT_PERD_FOR_PL_F column_name ENRT_CVG_END_DT_RL',10);
1280 table_list(10).table_name := 'BEN_ENRT_PERD_FOR_PL_F';
1281 table_list(10).column_name := 'ENRT_CVG_END_DT_RL';
1282 --
1283 --
1284 hr_utility.set_location(' TO check table_nameBEN_ENRT_PERD column_name ENRT_CVG_END_DT_RL',10);
1285 table_list(11).table_name := 'BEN_ENRT_PERD';
1286 table_list(11).column_name := 'ENRT_CVG_END_DT_RL';
1287 --
1288 --
1289 hr_utility.set_location(' TO check table_nameBEN_ELIG_STDNT_STAT_CVG_F column_name CVG_THRU_RL',10);
1290 table_list(12).table_name := 'BEN_ELIG_STDNT_STAT_CVG_F';
1291 table_list(12).column_name := 'CVG_THRU_RL';
1292 --
1293 --
1294 hr_utility.set_location(' TO check table_nameBEN_ELIG_DSBLD_STAT_CVG_F column_name CVG_THRU_RL',10);
1295 table_list(13).table_name := 'BEN_ELIG_DSBLD_STAT_CVG_F';
1296 table_list(13).column_name := 'CVG_THRU_RL';
1297 --
1298 ELSIF p_formula_type_id = -29 THEN
1299 /* Formula Type Enrollment Coverage Start Date */
1300 --
1301 hr_utility.set_location(' TO check table_nameBEN_ELIG_AGE_CVG_F column_name CVG_STRT_RL',10);
1302 table_list(1).table_name := 'BEN_ELIG_AGE_CVG_F';
1303 table_list(1).column_name := 'CVG_STRT_RL';
1304 --
1305 --
1306 hr_utility.set_location(' TO check table_nameBEN_ELIG_DSBLD_STAT_CVG_F column_name CVG_STRT_RL',10);
1307 table_list(2).table_name := 'BEN_ELIG_DSBLD_STAT_CVG_F';
1308 table_list(2).column_name := 'CVG_STRT_RL';
1309 --
1310 --
1311 hr_utility.set_location(' TO check table_nameBEN_ELIG_MLTRY_STAT_CVG_F column_name CVG_STRT_RL',10);
1312 table_list(3).table_name := 'BEN_ELIG_MLTRY_STAT_CVG_F';
1313 table_list(3).column_name := 'CVG_STRT_RL';
1314 --
1315 --
1316 hr_utility.set_location(' TO check table_nameBEN_PTIP_F column_name ENRT_CVG_STRT_DT_RL',10);
1317 table_list(4).table_name := 'BEN_PTIP_F';
1318 table_list(4).column_name := 'ENRT_CVG_STRT_DT_RL';
1319 --
1320 --
1321 hr_utility.set_location(' TO check table_nameBEN_PL_F column_name ENRT_CVG_STRT_DT_RL',10);
1322 table_list(5).table_name := 'BEN_PL_F';
1323 table_list(5).column_name := 'ENRT_CVG_STRT_DT_RL';
1324 --
1325 --
1326 hr_utility.set_location(' TO check table_nameBEN_PLIP_F column_name ENRT_CVG_STRT_DT_RL',10);
1327 table_list(6).table_name := 'BEN_PLIP_F';
1328 table_list(6).column_name := 'ENRT_CVG_STRT_DT_RL';
1329 --
1330 --
1331 hr_utility.set_location(' TO check table_nameBEN_PGM_F column_name ENRT_CVG_STRT_DT_RL',10);
1332 table_list(7).table_name := 'BEN_PGM_F';
1333 table_list(7).column_name := 'ENRT_CVG_STRT_DT_RL';
1334 --
1335 --
1336 hr_utility.set_location(' TO check table_nameBEN_LER_CHG_DPNT_CVG_F column_name CVG_EFF_STRT_RL',10);
1337 table_list(8).table_name := 'BEN_LER_CHG_DPNT_CVG_F';
1338 table_list(8).column_name := 'CVG_EFF_STRT_RL';
1339 --
1340 --
1341 hr_utility.set_location(' TO check table_nameBEN_LEE_RSN_F column_name ENRT_CVG_STRT_DT_RL',10);
1342 table_list(9).table_name := 'BEN_LEE_RSN_F';
1343 table_list(9).column_name := 'ENRT_CVG_STRT_DT_RL';
1344 --
1345 --
1346 hr_utility.set_location(' TO check table_nameBEN_ENRT_PERD_FOR_PL_F column_name ENRT_CVG_STRT_DT_RL',10);
1347 table_list(10).table_name := 'BEN_ENRT_PERD_FOR_PL_F';
1348 table_list(10).column_name := 'ENRT_CVG_STRT_DT_RL';
1349 --
1350 --
1351 hr_utility.set_location(' TO check table_nameBEN_ENRT_PERD column_name ENRT_CVG_STRT_DT_RL',10);
1352 table_list(11).table_name := 'BEN_ENRT_PERD';
1353 table_list(11).column_name := 'ENRT_CVG_STRT_DT_RL';
1354 --
1355 --
1356 hr_utility.set_location(' TO check table_nameBEN_ELIG_STDNT_STAT_CVG_F column_name CVG_STRT_RL',10);
1357 table_list(12).table_name := 'BEN_ELIG_STDNT_STAT_CVG_F';
1358 table_list(12).column_name := 'CVG_STRT_RL';
1359 --
1360 --
1361 hr_utility.set_location(' TO check table_nameBEN_ELIG_PER_ELCTBL_CHC column_name ENRT_CVG_STRT_DT_RL',10);
1362 table_list(13).table_name := 'BEN_ELIG_PER_ELCTBL_CHC';
1363 table_list(13).column_name := 'ENRT_CVG_STRT_DT_RL';
1364 --
1365 --
1366 hr_utility.set_location(' TO check table_nameBEN_ELIG_MRTL_STAT_CVG_F column_name CVG_STRT_RL',10);
1367 table_list(14).table_name := 'BEN_ELIG_MRTL_STAT_CVG_F';
1368 table_list(14).column_name := 'CVG_STRT_RL';
1369 --
1370 --
1371 hr_utility.set_location(' TO check table_nameBEN_ELIG_PER_ELCTBL_CHC_RBV column_name ENRT_CVG_STRT_DT_RL',10);
1372 table_list(15).table_name := 'BEN_ELIG_PER_ELCTBL_CHC_RBV';
1373 table_list(15).column_name := 'ENRT_CVG_STRT_DT_RL';
1374 --
1375 ELSIF p_formula_type_id = -28 THEN
1376 /* Formula Type Dependent Coverage End Date */
1377 --
1378 hr_utility.set_location(' TO check table_nameBEN_DPNT_CVG_RQD_RLSHP_F column_name CVG_THRU_DT_RL',10);
1379 table_list(1).table_name := 'BEN_DPNT_CVG_RQD_RLSHP_F';
1380 table_list(1).column_name := 'CVG_THRU_DT_RL';
1381 --
1382 --
1383 hr_utility.set_location(' TO check table_nameBEN_PTIP_F column_name DPNT_CVG_END_DT_RL',10);
1384 table_list(2).table_name := 'BEN_PTIP_F';
1385 table_list(2).column_name := 'DPNT_CVG_END_DT_RL';
1386 --
1387 --
1388 hr_utility.set_location(' TO check table_nameBEN_PL_F column_name DPNT_CVG_END_DT_RL',10);
1389 table_list(3).table_name := 'BEN_PL_F';
1390 table_list(3).column_name := 'DPNT_CVG_END_DT_RL';
1391 --
1392 --
1393 hr_utility.set_location(' TO check table_nameBEN_PGM_F column_name DPNT_CVG_END_DT_RL',10);
1394 table_list(4).table_name := 'BEN_PGM_F';
1395 table_list(4).column_name := 'DPNT_CVG_END_DT_RL';
1396 --
1397 ELSIF p_formula_type_id = -27 THEN
1398 /* Formula Type Dependent Coverage Start Date */
1399 --
1400 hr_utility.set_location(' TO check table_nameBEN_DPNT_CVG_RQD_RLSHP_F column_name CVG_STRT_DT_RL',10);
1401 table_list(1).table_name := 'BEN_DPNT_CVG_RQD_RLSHP_F';
1402 table_list(1).column_name := 'CVG_STRT_DT_RL';
1403 --
1404 --
1405 hr_utility.set_location(' TO check table_nameBEN_ELIG_PER_ELCTBL_CHC column_name DPNT_CVG_STRT_DT_RL',10);
1406 table_list(2).table_name := 'BEN_ELIG_PER_ELCTBL_CHC';
1407 table_list(2).column_name := 'DPNT_CVG_STRT_DT_RL';
1408 --
1409 --
1410 hr_utility.set_location(' TO check table_nameBEN_PTIP_F column_name DPNT_CVG_STRT_DT_RL',10);
1411 table_list(3).table_name := 'BEN_PTIP_F';
1412 table_list(3).column_name := 'DPNT_CVG_STRT_DT_RL';
1413 --
1414 --
1415 hr_utility.set_location(' TO check table_nameBEN_PL_F column_name DPNT_CVG_STRT_DT_RL',10);
1416 table_list(4).table_name := 'BEN_PL_F';
1417 table_list(4).column_name := 'DPNT_CVG_STRT_DT_RL';
1418 --
1419 --
1420 hr_utility.set_location(' TO check table_nameBEN_PGM_F column_name DPNT_CVG_STRT_DT_RL',10);
1421 table_list(5).table_name := 'BEN_PGM_F';
1422 table_list(5).column_name := 'DPNT_CVG_STRT_DT_RL';
1423 --
1424 --
1425 hr_utility.set_location(' TO check table_nameBEN_ELIG_PER_ELCTBL_CHC_RBV column_name DPNT_CVG_STRT_DT_RL',10);
1426 table_list(6).table_name := 'BEN_ELIG_PER_ELCTBL_CHC_RBV';
1427 table_list(6).column_name := 'DPNT_CVG_STRT_DT_RL';
1428 --
1429 ELSIF p_formula_type_id = -26 THEN
1430 /* Formula Type Dependent Certification Required */
1431 --
1432 hr_utility.set_location(' TO check table_nameBEN_LER_CHG_DPNT_CVG_CTFN_F column_name CTFN_RQD_ELSIF_RL',10);
1433 table_list(1).table_name := 'BEN_LER_CHG_DPNT_CVG_CTFN_F';
1434 table_list(1).column_name := 'CTFN_RQD_ELSIF_RL';
1435 --
1436 --
1437 hr_utility.set_location(' TO check table_nameBEN_PL_DPNT_CVG_CTFN_F column_name CTFN_RQD_ELSIF_RL',10);
1438 table_list(2).table_name := 'BEN_PL_DPNT_CVG_CTFN_F';
1439 table_list(2).column_name := 'CTFN_RQD_ELSIF_RL';
1440 --
1441 --
1442 hr_utility.set_location(' TO check table_nameBEN_PGM_DPNT_CVG_CTFN_F column_name CTFN_RQD_ELSIF_RL',10);
1443 table_list(3).table_name := 'BEN_PGM_DPNT_CVG_CTFN_F';
1444 table_list(3).column_name := 'CTFN_RQD_ELSIF_RL';
1445 --
1446 --
1447 hr_utility.set_location(' TO check table_nameBEN_PTIP_DPNT_CVG_CTFN_F column_name CTFN_RQD_WHEN_RL',10);
1448 table_list(4).table_name := 'BEN_PTIP_DPNT_CVG_CTFN_F';
1449 table_list(4).column_name := 'CTFN_RQD_WHEN_RL';
1450 --
1451 ELSIF p_formula_type_id = -25 THEN
1452 /* Formula Type Beneficiary Certification Required */
1453 --
1454 hr_utility.set_location(' TO check table_nameBEN_LER_BNFT_RSTRN_CTFN_F column_name CTFN_RQD_ELSIF_RL',10);
1455 table_list(1).table_name := 'BEN_LER_BNFT_RSTRN_CTFN_F';
1456 table_list(1).column_name := 'CTFN_RQD_ELSIF_RL';
1457 --
1458 --
1459 hr_utility.set_location(' TO check table_nameBEN_PL_BNF_CTFN_F column_name CTFN_RQD_ELSIF_RL',10);
1460 table_list(2).table_name := 'BEN_PL_BNF_CTFN_F';
1461 table_list(2).column_name := 'CTFN_RQD_ELSIF_RL';
1462 --
1463 ELSIF p_formula_type_id = -21 THEN
1464 /* Formula Type Family Member Determination */
1465 --
1466 hr_utility.set_location(' TO check table_nameBEN_ELIG_TO_PRTE_RSN_F column_name VRFY_FMLY_MMBR_RL',10);
1467 table_list(1).table_name := 'BEN_ELIG_TO_PRTE_RSN_F';
1468 table_list(1).column_name := 'VRFY_FMLY_MMBR_RL';
1469 --
1470 --
1471 hr_utility.set_location(' TO check table_nameBEN_PGM_F column_name VRFY_FMLY_MMBR_RL',10);
1472 table_list(2).table_name := 'BEN_PGM_F';
1473 table_list(2).column_name := 'VRFY_FMLY_MMBR_RL';
1474 --
1475 --
1476 hr_utility.set_location(' TO check table_nameBEN_PL_F column_name VRFY_FMLY_MMBR_RL',10);
1477 table_list(3).table_name := 'BEN_PL_F';
1478 table_list(3).column_name := 'VRFY_FMLY_MMBR_RL';
1479 --
1480 --
1481 hr_utility.set_location(' TO check table_nameBEN_PTIP_F column_name VRFY_FMLY_MMBR_RL',10);
1482 table_list(4).table_name := 'BEN_PTIP_F';
1483 table_list(4).column_name := 'VRFY_FMLY_MMBR_RL';
1484 --
1485 --
1486 hr_utility.set_location(' TO check table_nameBEN_PLIP_F column_name VRFY_FMLY_MMBR_RL',10);
1487 table_list(5).table_name := 'BEN_PLIP_F';
1488 table_list(5).column_name := 'VRFY_FMLY_MMBR_RL';
1489 --
1490 --
1491 hr_utility.set_location(' TO check table_nameBEN_OIPL_F column_name VRFY_FMLY_MMBR_RL',10);
1492 table_list(6).table_name := 'BEN_OIPL_F';
1493 table_list(6).column_name := 'VRFY_FMLY_MMBR_RL';
1494 /*
1495 --
1496 -- Formula_type_id for the following table_name and column_name combination hasn't been found yet.
1497 --
1498
1499 ELSIF p_formula_type_id = THEN
1500 --
1501 hr_utility.set_location(' TO check table_nameBEN_ELIG_TTL_CVG_VOL_PRTE_F column_name CVG_VOL_DET_RL',10);
1502 table_list(1).table_name := 'BEN_ELIG_TTL_CVG_VOL_PRTE_F';
1503 table_list(1).column_name := 'CVG_VOL_DET_RL';
1504 --
1505 ELSIF p_formula_type_id = THEN
1506 --
1507 hr_utility.set_location(' TO check table_nameBEN_ELIG_TTL_PRTT_PRTE_F column_name PRTT_DET_RL',10);
1508 table_list(1).table_name := 'BEN_ELIG_TTL_PRTT_PRTE_F';
1509 table_list(1).column_name := 'PRTT_DET_RL';
1510 --
1511 ELSIF p_formula_type_id = THEN
1512 --
1513 hr_utility.set_location(' TO check table_nameBEN_PL_F column_name FRFS_DISTR_MTHD_RL',10);
1514 table_list(1).table_name := 'BEN_PL_F';
1515 table_list(1).column_name := 'FRFS_DISTR_MTHD_RL';
1516 --
1517 ELSIF p_formula_type_id = THEN
1518 --
1519 hr_utility.set_location(' TO check table_nameBEN_PL_GD_R_SVC_CTFN_F column_name LACK_CTFN_DENY_RMBMT_RL',10);
1520 table_list(1).table_name := 'BEN_PL_GD_R_SVC_CTFN_F';
1521 table_list(1).column_name := 'LACK_CTFN_DENY_RMBMT_RL';
1522 --
1523 ELSIF p_formula_type_id = THEN
1524 --
1525 hr_utility.set_location(' TO check table_nameBEN_VSTG_SCHED_F column_name FORMULA_ID',10);
1526 table_list(1).table_name := 'BEN_VSTG_SCHED_F';
1527 table_list(1).column_name := 'FORMULA_ID';
1528 --
1529 ELSIF p_formula_type_id = THEN
1530 --
1531 hr_utility.set_location(' TO check table_nameBEN_VSTG_SCHED_F column_name BIS_RL',10);
1532 table_list(1).table_name := 'BEN_VSTG_SCHED_F';
1533 table_list(1).column_name := 'BIS_RL';
1534 --
1535 ELSIF p_formula_type_id = THEN
1536 --
1537 hr_utility.set_location(' TO check table_nameBEN_VSTG_FOR_ACTY_RT_F column_name LOS_OVRID_RL',10);
1538 table_list(1).table_name := 'BEN_VSTG_FOR_ACTY_RT_F';
1539 table_list(1).column_name := 'LOS_OVRID_RL';
1540 --
1541 ELSIF p_formula_type_id = THEN
1542 --
1543 hr_utility.set_location(' TO check table_nameBEN_VSTG_FOR_ACTY_RT_F column_name BIS_OVRID_RL',10);
1544 table_list(1).table_name := 'BEN_VSTG_FOR_ACTY_RT_F';
1545 table_list(1).column_name := 'BIS_OVRID_RL';
1546 --
1547 ELSIF p_formula_type_id = THEN
1548 --
1549 hr_utility.set_location(' TO check table_nameBEN_VSTG_FOR_ACTY_RT_F column_name AGE_DET_OVRID_RL',10);
1550 table_list(1).table_name := 'BEN_VSTG_FOR_ACTY_RT_F';
1551 table_list(1).column_name := 'AGE_DET_OVRID_RL';
1552 --
1553 ELSIF p_formula_type_id = THEN
1554 --
1555 hr_utility.set_location(' TO check table_nameBEN_PREM_CSTG_BY_SGMT_F column_name SGMT_CSTG_MTHD_RL',10);
1556 table_list(1).table_name := 'BEN_PREM_CSTG_BY_SGMT_F';
1557 table_list(1).column_name := 'SGMT_CSTG_MTHD_RL';
1558 --
1559 ELSIF p_formula_type_id = THEN
1560 --
1561 hr_utility.set_location(' TO check table_nameBEN_ELIG_TTL_PRTT_PRTE_F column_name PRTT_DET_RL',10);
1562 table_list(1).table_name := 'BEN_ELIG_TTL_PRTT_PRTE_F';
1563 table_list(1).column_name := 'PRTT_DET_RL';
1564 --
1565 ELSIF p_formula_type_id = THEN
1566 --
1567 hr_utility.set_location(' TO check table_nameBEN_ELIG_TTL_CVG_VOL_PRTE_F column_name CVG_VOL_DET_RL',10);
1568 table_list(1).table_name := 'BEN_ELIG_TTL_CVG_VOL_PRTE_F';
1569 table_list(1).column_name := 'CVG_VOL_DET_RL';
1570 --
1571 ELSIF p_formula_type_id = THEN
1572 --
1573 hr_utility.set_location(' TO check table_nameBEN_TTL_PRTT_RT_F column_name PRTT_DET_RL',10);
1574 table_list(1).table_name := 'BEN_TTL_PRTT_RT_F';
1575 table_list(1).column_name := 'PRTT_DET_RL';
1576 --
1577 ELSIF p_formula_type_id = THEN
1578 --
1579 hr_utility.set_location(' TO check table_nameBEN_TTL_CVG_VOL_RT_F column_name CVG_VOL_DET_RL',10);
1580 table_list(1).table_name := 'BEN_TTL_CVG_VOL_RT_F';
1581 table_list(1).column_name := 'CVG_VOL_DET_RL';
1582 --
1583 ELSIF p_formula_type_id = THEN
1584 --
1585 hr_utility.set_location(' TO check table_nameBEN_POP_UP_MESSAGES column_name NO_FORMULA_FLAG',10);
1586 table_list(1).table_name := 'BEN_POP_UP_MESSAGES';
1587 table_list(1).column_name := 'NO_FORMULA_FLAG';
1588 --
1589 ELSIF p_formula_type_id = THEN
1590 --
1591 hr_utility.set_location(' TO check table_nameBEN_PL_GD_R_SVC_CTFN_F column_name LACK_CTFN_DENY_RMBMT_RL',10);
1592 table_list(1).table_name := 'BEN_PL_GD_R_SVC_CTFN_F';
1593 table_list(1).column_name := 'LACK_CTFN_DENY_RMBMT_RL';
1594 -- */
1595 END IF;
1596
1597 hr_utility.set_location ('Leaving ' || l_proc,10);
1598
1599 END populate_table_list;
1600
1601 --
1602 -- ============================================================================
1603 -- <<Function: chk_formula_exists_in_ben>>
1604 -- ============================================================================
1605 --
1606 FUNCTION chk_formula_exists_in_ben(p_formula_id IN NUMBER,
1607 p_formula_type_id IN NUMBER,
1608 p_effective_date IN DATE Default NULL,
1609 p_business_group_id IN NUMBER Default NULL,
1610 p_legislation_cd IN VARCHAR2 Default NULL
1611 )
1612 RETURN BOOLEAN IS
1613 --
1614 L_proc varchar2(80);
1615 g_debug BOOLEAN;
1616 --
1617 BEGIN
1618 g_debug := hr_utility.debug_enabled;
1619 IF g_debug THEN
1620 l_proc := g_package||'.chk_formula_exists_in_ben';
1621 hr_utility.set_location ('Entering '|| l_proc,5);
1622 END IF;
1623
1624 populate_table_list(p_formula_type_id);
1625
1626 IF table_list.COUNT <> 0 THEN
1627 FOR i IN table_list.FIRST..table_list.LAST LOOP
1628 IF ben_batch_utils.rows_exist(table_list(i).table_name,
1629 table_list(i).column_name,
1630 p_formula_id) THEN
1631 -- one row exists so return true
1632 IF g_debug THEN
1633 hr_utility.set_location('Leaving:'||l_proc, 10);
1634 END IF;
1635 RETURN (TRUE);
1636 END IF;
1637 END LOOP;
1638 END IF;
1639 -- return false as no rows exist
1640 IF g_debug THEN
1641 hr_utility.set_location ('Leaving ' || l_proc,10);
1642 END IF;
1643 RETURN (FALSE);
1644 END;
1645 --
1646 END Ben_FastFormula_Check;