1 Package body ben_ext_evaluate_inclusion as
2 /* $Header: benxincl.pkb 120.9.12010000.2 2008/08/05 14:58:12 ubhat ship $ */
3 --
4 --
5 -- ----------------------------------------------------------------------------
6 -- | Private Global Definitions |
7 -- ----------------------------------------------------------------------------
8 --
9 g_debug boolean := hr_utility.debug_enabled;
10 --
11 -----------------------------------------------------------------------------
12 -------------------------< initailize_all_cache_area >-----------------------
13 -----------------------------------------------------------------------------
14 --
15 -- The following procedure initialize all the cache area.
16 --
17 Procedure Initialize_All_Cache_Area is
18 --
19 l_proc varchar2(72);
20 --
21 Begin
22 --
23 if g_debug then
24 l_proc := g_package||'initialize_all_cache_area';
25 hr_utility.set_location('Entering:'||l_proc, 5);
26 end if;
27 --
28 g_person_id_incl_rqd := 'N';
29 g_postal_code_incl_rqd := 'N';
30 g_org_id_incl_rqd := 'N';
31 g_loc_id_incl_rqd := 'N';
32 g_gre_incl_rqd := 'N';
33 g_state_incl_rqd := 'N';
34 g_bnft_grp_incl_rqd := 'N';
35 g_ee_status_incl_rqd := 'N';
36 g_payroll_id_incl_rqd := 'N';
37 g_payroll_rl_incl_rqd := 'N';
38 g_payroll_last_date_excld_flag := 'N' ;
39 g_enrt_plan_incl_rqd := 'N';
40 g_enrt_opt_incl_rqd := 'N';
41 g_enrt_rg_plan_incl_rqd := 'N';
42 g_enrt_sspndd_incl_rqd := 'N';
43 g_enrt_cvg_strt_dt_incl_rqd := 'N';
44 g_enrt_cvg_drng_perd_incl_rqd := 'N';
45 g_enrt_stat_incl_rqd := 'N';
46 g_enrt_mthd_incl_rqd := 'N';
47 g_enrt_pgm_incl_rqd := 'N';
48 g_enrt_pl_typ_incl_rqd := 'N';
49 g_enrt_last_upd_dt_incl_rqd := 'N';
50 g_enrt_ler_name_incl_rqd := 'N';
51 g_enrt_ler_stat_incl_rqd := 'N';
52 g_enrt_ler_ocrd_dt_incl_rqd := 'N';
53 g_enrt_ler_ntfn_dt_incl_rqd := 'N';
54 g_enrt_rltn_incl_rqd := 'N'; --currently known as misc
55 g_enrt_dpnt_rltn_incl_rqd := 'N'; --dependent enrolled on the day
56 g_elct_plan_incl_rqd := 'N';
57 g_elct_opt_incl_rqd := 'N';
58 g_elct_rg_plan_incl_rqd := 'N';
59 g_elct_enrt_strt_dt_incl_rqd := 'N';
60 g_elct_yrprd_incl_rqd := 'N';
61 g_elct_pgm_incl_rqd := 'N';
62 g_elct_pl_typ_incl_rqd := 'N';
63 g_elct_last_upd_dt_incl_rqd := 'N';
64 g_elct_ler_name_incl_rqd := 'N';
65 g_elct_ler_stat_incl_rqd := 'N';
66 g_elct_ler_ocrd_dt_incl_rqd := 'N';
67 g_elct_ler_ntfn_dt_incl_rqd := 'N';
68 g_elct_rltn_incl_rqd := 'N';
69 -- g_per_plan_incl_rqd := 'N'; --tvh remove
70 -- g_part_type_incl_rqd := 'N';
71 g_ele_input_incl_rqd := 'N';
72 g_person_rule_incl_rqd := 'N';
73 g_per_ler_incl_rqd := 'N';
74 g_person_type_incl_rqd := 'N';
75 g_chg_evt_incl_rqd := 'N';
76 g_chg_pay_evt_incl_rqd := 'N';
77 g_chg_eff_dt_incl_rqd := 'N';
78 g_chg_actl_dt_incl_rqd := 'N';
79 g_chg_login_incl_rqd := 'N';
80 g_chg_login_incl_rqd := 'N';
81 g_cm_typ_incl_rqd := 'N';
82 g_cm_typ_incl_rqd := 'N';
83 g_cm_last_upd_dt_incl_rqd := 'N';
84 g_cm_pr_last_upd_dt_incl_rqd := 'N';
85 g_cm_sent_dt_incl_rqd := 'N';
86 g_cm_to_be_sent_dt_incl_rqd := 'N';
87 g_cmbn_incl_rqd := 'N';
88 g_actn_name_incl_rqd := 'N';
89 g_actn_item_rltn_incl_rqd := 'N';
90 g_prem_last_updt_dt_rqd := 'N' ;
91 g_prem_month_year_rqd := 'N' ;
92 g_asg_to_use_rqd := 'N' ;
93 g_subhead_rule_rqd := 'N' ;
94 g_subhead_pos_rqd := 'N' ;
95 g_subhead_job_rqd := 'N' ;
96 g_subhead_loc_rqd := 'N' ;
97 g_subhead_pay_rqd := 'N' ;
98 g_subhead_org_rqd := 'N' ;
99 g_subhead_bg_rqd := 'N' ;
100 g_subhead_grd_rqd := 'N' ;
101
102 -- cwb
103 g_cwb_pl_prd_rqd := 'N' ;
104 --
105 g_person_id_excld_flag := 'N';
106 g_postal_code_excld_flag := 'N';
107 g_org_id_excld_flag := 'N';
108 g_loc_id_excld_flag := 'N';
109 g_gre_excld_flag := 'N';
110 g_state_excld_flag := 'N';
111 g_bnft_grp_excld_flag := 'N';
112 g_ee_status_excld_flag := 'N';
113 g_payroll_id_excld_flag := 'N';
114 g_payroll_rl_excld_flag := 'N';
115 g_payroll_last_Date_excld_flag := 'N' ;
116 g_enrt_plan_excld_flag := 'N';
117 g_enrt_rg_plan_excld_flag := 'N';
118 g_enrt_sspndd_excld_flag := 'N';
119 g_enrt_cvg_strt_dt_excld_flag := 'N';
120 g_enrt_cvg_drng_prd_excld_flag := 'N';
121 g_enrt_stat_excld_flag := 'N';
122 g_enrt_mthd_excld_flag := 'N';
123 g_enrt_pgm_excld_flag := 'N';
124 g_enrt_pl_typ_excld_flag := 'N';
125 g_enrt_last_upd_dt_excld_flag := 'N';
126 g_enrt_ler_name_excld_flag := 'N';
127 g_enrt_ler_stat_excld_flag := 'N';
128 g_enrt_ler_ocrd_dt_excld_flag := 'N';
129 g_enrt_ler_ntfn_dt_excld_flag := 'N';
130 g_enrt_rltn_excld_flag := 'N'; --currently known as misc
131 g_enrt_dpnt_rltn_excld_flag := 'N';
132 g_elct_plan_excld_flag := 'N';
133 g_elct_rg_plan_excld_flag := 'N';
134 g_elct_enrt_strt_dt_excld_flag := 'N';
135 g_elct_yrprd_excld_flag := 'N';
136 g_elct_pgm_excld_flag := 'N';
137 g_elct_pl_typ_excld_flag := 'N';
138 g_elct_last_upd_dt_excld_flag := 'N';
139 g_elct_ler_name_excld_flag := 'N';
140 g_elct_ler_stat_excld_flag := 'N';
141 g_elct_ler_ocrd_dt_excld_flag := 'N';
142 g_elct_ler_ntfn_dt_excld_flag := 'N';
143 g_elct_rltn_excld_flag := 'N';
144 -- g_per_plan_excld_flag := 'N'; -- tvh remove
145 -- g_part_type_excld_flag := 'N';
146 g_ele_input_excld_flag := 'N';
147 g_person_rule_excld_flag := 'N';
148 g_per_ler_excld_flag := 'N';
149 g_person_type_excld_flag := 'N';
150 g_chg_evt_excld_flag := 'N';
151 g_chg_pay_evt_excld_flag := 'N';
152 g_chg_eff_dt_excld_flag := 'N';
153 g_chg_actl_dt_excld_flag := 'N';
154 g_chg_login_excld_flag := 'N';
155 g_chg_login_excld_flag := 'N';
156 g_cm_typ_excld_flag := 'N';
157 g_cm_typ_excld_flag := 'N';
158 g_cm_last_upd_dt_excld_flag := 'N';
159 g_cm_pr_last_upd_dt_excld_flag := 'N';
160 g_cm_sent_dt_excld_flag := 'N';
161 g_cm_to_be_sent_dt_excld_flag := 'N';
162 g_actn_name_excld_flag := 'N';
163 g_actn_item_rltn_excld_flag := 'N';
164 g_prem_last_updt_dt_excld_flag := 'N' ;
165 g_prem_month_year_excld_flag := 'N' ;
166 g_subhead_rule_excld_flag := 'N' ;
167 g_subhead_pos_excld_flag := 'N' ;
168 g_subhead_job_excld_flag := 'N' ;
169 g_subhead_loc_excld_flag := 'N' ;
170 g_subhead_pay_excld_flag := 'N' ;
171 g_subhead_org_excld_flag := 'N' ;
172 g_subhead_bg_excld_flag := 'N' ;
173 g_subhead_grd_excld_flag := 'N' ;
174 -- cwb
175 g_cwb_pl_prd_excld_flag := 'N' ;
176
177 -- Timecard Inclusion Globals
178
179 g_tc_status_excld_flag := 'N';
180 g_tc_deleted_excld_flag := 'N';
181 g_project_id_excld_flag := 'N';
182 g_task_id_excld_flag := 'N';
183 g_exp_typ_id_excld_flag := 'N';
184 g_po_num_excld_flag := 'N';
185
186 g_tc_status_incl_rqd := 'N';
187 g_tc_deleted_incl_rqd := 'N';
188 g_project_id_incl_rqd := 'N';
189 g_task_id_incl_rqd := 'N';
190 g_exp_typ_id_incl_rqd := 'N';
191 g_po_num_incl_rqd := 'N';
192
193 --
194 -- Initialize all the lists - May, 99
195 --
196 for i in 1..g_person_id_list.count loop
197 if g_person_id_list.exists(i) then
198 g_person_id_list(i) := null;
199 end if;
200 end loop;
201 for i in 1..g_postal_code_list1.count loop
202 if g_postal_code_list1.exists(i) then
203 g_postal_code_list1(i) := null;
204 end if;
205 end loop;
206 for i in 1..g_postal_code_list2.count loop
207 if g_postal_code_list2.exists(i) then
208 g_postal_code_list2(i) := null;
209 end if;
210 end loop;
211 for i in 1..g_org_id_list.count loop
212 if g_org_id_list.exists(i) then
213 g_org_id_list(i) := null;
214 end if;
215 end loop;
216 for i in 1..g_loc_id_list.count loop
217 if g_loc_id_list.exists(i) then
218 g_loc_id_list(i) := null;
219 end if;
220 end loop;
221 for i in 1..g_gre_list.count loop
222 if g_gre_list.exists(i) then
223 g_gre_list(i) := null;
224 end if;
225 end loop;
226 for i in 1..g_state_list.count loop
227 if g_state_list.exists(i) then
228 g_state_list(i) := null;
229 end if;
230 end loop;
231 for i in 1..g_bnft_grp_list.count loop
232 if g_bnft_grp_list.exists(i) then
233 g_bnft_grp_list(i) := null;
234 end if;
235 end loop;
236 for i in 1..g_ee_status_list.count loop
237 if g_ee_status_list.exists(i) then
238 g_ee_status_list(i) := null;
239 end if;
240 end loop;
241 for i in 1..g_payroll_id_list.count loop
242 if g_payroll_id_list.exists(i) then
243 g_payroll_id_list(i) := null;
244 end if;
245 end loop;
246
247 for i in 1..g_payroll_rl_list.count loop
248 if g_payroll_rl_list.exists(i) then
249 g_payroll_rl_list(i) := null;
250 end if;
251 end loop;
252
253
254 for i in 1..g_enrt_plan_list.count loop
255 if g_enrt_plan_list.exists(i) then
256 g_enrt_plan_list(i) := null;
257 end if;
258 end loop;
259 for i in 1..g_enrt_rg_plan_list.count loop
260 if g_enrt_rg_plan_list.exists(i) then
261 g_enrt_rg_plan_list(i) := null;
262 end if;
263 end loop;
264 for i in 1..g_enrt_sspndd_list.count loop
265 if g_enrt_sspndd_list.exists(i) then
266 g_enrt_sspndd_list(i) := null;
267 end if;
268 end loop;
269 for i in 1..g_enrt_cvg_strt_dt_list1.count loop
270 if g_enrt_cvg_strt_dt_list1.exists(i) then
271 g_enrt_cvg_strt_dt_list1(i) := null;
272 end if;
273 end loop;
274 for i in 1..g_enrt_cvg_strt_dt_list2.count loop
275 if g_enrt_cvg_strt_dt_list2.exists(i) then
276 g_enrt_cvg_strt_dt_list2(i) := null;
277 end if;
278 end loop;
279 for i in 1..g_enrt_cvg_drng_perd_list1.count loop
280 if g_enrt_cvg_drng_perd_list1.exists(i) then
281 g_enrt_cvg_drng_perd_list1(i) := null;
282 end if;
283 end loop;
284 for i in 1..g_enrt_cvg_drng_perd_list2.count loop
285 if g_enrt_cvg_drng_perd_list2.exists(i) then
286 g_enrt_cvg_drng_perd_list2(i) := null;
287 end if;
288 end loop;
289 for i in 1..g_enrt_stat_list.count loop
290 if g_enrt_stat_list.exists(i) then
291 g_enrt_stat_list(i) := null;
292 end if;
293 end loop;
294 for i in 1..g_enrt_mthd_list.count loop
295 if g_enrt_mthd_list.exists(i) then
296 g_enrt_mthd_list(i) := null;
297 end if;
298 end loop;
299 for i in 1..g_enrt_pgm_list.count loop
300 if g_enrt_pgm_list.exists(i) then
301 g_enrt_pgm_list(i) := null;
302 end if;
303 end loop;
304 for i in 1..g_enrt_pl_typ_list.count loop
305 if g_enrt_pl_typ_list.exists(i) then
306 g_enrt_pl_typ_list(i) := null;
307 end if;
308 end loop;
309 for i in 1..g_enrt_last_upd_dt_list1.count loop
310 if g_enrt_last_upd_dt_list1.exists(i) then
311 g_enrt_last_upd_dt_list1(i) := null;
312 end if;
313 end loop;
314 for i in 1..g_enrt_last_upd_dt_list2.count loop
315 if g_enrt_last_upd_dt_list2.exists(i) then
316 g_enrt_last_upd_dt_list2(i) := null;
317 end if;
318 end loop;
319 for i in 1..g_enrt_ler_name_list.count loop
320 if g_enrt_ler_name_list.exists(i) then
321 g_enrt_ler_name_list(i) := null;
322 end if;
323 end loop;
324 for i in 1..g_enrt_ler_stat_list.count loop
325 if g_enrt_ler_stat_list.exists(i) then
326 g_enrt_ler_stat_list(i) := null;
327 end if;
328 end loop;
329 for i in 1..g_enrt_ler_ocrd_dt_list1.count loop
330 if g_enrt_ler_ocrd_dt_list1.exists(i) then
331 g_enrt_ler_ocrd_dt_list1(i) := null;
332 end if;
333 end loop;
334 for i in 1..g_enrt_ler_ocrd_dt_list2.count loop
335 if g_enrt_ler_ocrd_dt_list2.exists(i) then
336 g_enrt_ler_ocrd_dt_list2(i) := null;
337 end if;
338 end loop;
339 for i in 1..g_enrt_ler_ntfn_dt_list1.count loop
340 if g_enrt_ler_ntfn_dt_list1.exists(i) then
341 g_enrt_ler_ntfn_dt_list1(i) := null;
342 end if;
343 end loop;
344 for i in 1..g_enrt_ler_ntfn_dt_list2.count loop
345 if g_enrt_ler_ntfn_dt_list2.exists(i) then
346 g_enrt_ler_ntfn_dt_list2(i) := null;
347 end if;
348 end loop;
349 for i in 1..g_enrt_rltn_list.count loop
350 if g_enrt_rltn_list.exists(i) then
351 g_enrt_rltn_list(i) := null;
352 end if;
353 end loop;
354
355 for i in 1..g_enrt_dpnt_rltn_list.count loop
356 if g_enrt_dpnt_rltn_list.exists(i) then
357 g_enrt_dpnt_rltn_list(i) := null;
358 end if;
359 end loop;
360
361 for i in 1..g_elct_plan_list.count loop
362 if g_elct_plan_list.exists(i) then
363 g_elct_plan_list(i) := null;
364 end if;
365 end loop;
366 for i in 1..g_elct_rg_plan_list.count loop
367 if g_elct_rg_plan_list.exists(i) then
368 g_elct_rg_plan_list(i) := null;
369 end if;
370 end loop;
371 for i in 1..g_elct_enrt_strt_dt_list1.count loop
372 if g_elct_enrt_strt_dt_list1.exists(i) then
373 g_elct_enrt_strt_dt_list1(i) := null;
374 end if;
375 end loop;
376 for i in 1..g_elct_enrt_strt_dt_list2.count loop
377 if g_elct_enrt_strt_dt_list2.exists(i) then
378 g_elct_enrt_strt_dt_list2(i) := null;
379 end if;
380 end loop;
381 for i in 1..g_elct_yrprd_list.count loop
382 if g_elct_yrprd_list.exists(i) then
383 g_elct_yrprd_list(i) := null;
384 end if;
385 end loop;
386 for i in 1..g_elct_pgm_list.count loop
387 if g_elct_pgm_list.exists(i) then
388 g_elct_pgm_list(i) := null;
389 end if;
390 end loop;
391 for i in 1..g_elct_pl_typ_list.count loop
392 if g_elct_pl_typ_list.exists(i) then
393 g_elct_pl_typ_list(i) := null;
394 end if;
395 end loop;
396 for i in 1..g_elct_last_upd_dt_list1.count loop
397 if g_elct_last_upd_dt_list1.exists(i) then
398 g_elct_last_upd_dt_list1(i) := null;
399 end if;
400 end loop;
401 for i in 1..g_elct_last_upd_dt_list2.count loop
402 if g_elct_last_upd_dt_list2.exists(i) then
403 g_elct_last_upd_dt_list2(i) := null;
404 end if;
405 end loop;
406 for i in 1..g_elct_ler_name_list.count loop
407 if g_elct_ler_name_list.exists(i) then
408 g_elct_ler_name_list(i) := null;
409 end if;
410 end loop;
411 for i in 1..g_elct_ler_stat_list.count loop
412 if g_elct_ler_stat_list.exists(i) then
413 g_elct_ler_stat_list(i) := null;
414 end if;
415 end loop;
416 for i in 1..g_elct_ler_ocrd_dt_list1.count loop
417 if g_elct_ler_ocrd_dt_list1.exists(i) then
418 g_elct_ler_ocrd_dt_list1(i) := null;
419 end if;
420 end loop;
421 for i in 1..g_elct_ler_ocrd_dt_list2.count loop
422 if g_elct_ler_ocrd_dt_list2.exists(i) then
423 g_elct_ler_ocrd_dt_list2(i) := null;
424 end if;
425 end loop;
426 for i in 1..g_elct_ler_ntfn_dt_list1.count loop
427 if g_elct_ler_ntfn_dt_list1.exists(i) then
428 g_elct_ler_ntfn_dt_list1(i) := null;
429 end if;
430 end loop;
431 for i in 1..g_elct_ler_ntfn_dt_list2.count loop
432 if g_elct_ler_ntfn_dt_list2.exists(i) then
433 g_elct_ler_ntfn_dt_list2(i) := null;
434 end if;
435 end loop;
436 for i in 1..g_elct_rltn_list.count loop
437 if g_elct_rltn_list.exists(i) then
438 g_elct_rltn_list(i) := null;
439 end if;
440 end loop;
441 /* for i in 1..g_per_plan_list.count loop -- tvh remove
442 if g_per_plan_list.exists(i) then
443 g_per_plan_list(i) := null;
444 end if;
445 end loop; */
446 /* for i in 1..g_part_type_list.count loop
447 if g_part_type_list.exists(i) then
448 g_part_type_list(i) := null;
449 end if;
450 end loop; */
451 for i in 1..g_ele_input_list.count loop
452 if g_ele_input_list.exists(i) then
453 g_ele_input_list(i) := null;
454 g_ele_type_list(i) := null;
455 end if;
456 end loop;
457 for i in 1..g_person_rule_list.count loop
458 if g_person_rule_list.exists(i) then
459 g_person_rule_list(i) := null;
460 end if;
461 end loop;
462 for i in 1..g_per_ler_list.count loop
463 if g_per_ler_list.exists(i) then
464 g_per_ler_list(i) := null;
465 end if;
466 end loop;
467 for i in 1..g_person_type_list.count loop
468 if g_person_type_list.exists(i) then
469 g_person_type_list(i) := null;
470 end if;
471 end loop;
472 for i in 1..g_chg_evt_list.count loop
473 if g_chg_evt_list.exists(i) then
474 g_chg_evt_list(i) := null;
475 end if;
476 end loop;
477 for i in 1..g_chg_pay_evt_list.count loop
478 if g_chg_pay_evt_list.exists(i) then
479 g_chg_pay_evt_list(i) := null;
480 end if;
481 end loop;
482
483 for i in 1..g_chg_eff_dt_list1.count loop
484 if g_chg_eff_dt_list1.exists(i) then
485 g_chg_eff_dt_list1(i) := null;
486 end if;
487 end loop;
488 for i in 1..g_chg_eff_dt_list2.count loop
489 if g_chg_eff_dt_list2.exists(i) then
490 g_chg_eff_dt_list2(i) := null;
491 end if;
492 end loop;
493 for i in 1..g_chg_actl_dt_list1.count loop
494 if g_chg_actl_dt_list1.exists(i) then
495 g_chg_actl_dt_list1(i) := null;
496 end if;
497 end loop;
498 for i in 1..g_chg_actl_dt_list2.count loop
499 if g_chg_actl_dt_list2.exists(i) then
500 g_chg_actl_dt_list2(i) := null;
501 end if;
502 end loop;
503 for i in 1..g_chg_login_list.count loop
504 if g_chg_login_list.exists(i) then
505 g_chg_login_list(i) := null;
506 end if;
507 end loop;
508 for i in 1..g_cm_typ_list.count loop
509 if g_cm_typ_list.exists(i) then
510 g_cm_typ_list(i) := null;
511 end if;
512 end loop;
513 for i in 1..g_cm_last_upd_dt_list1.count loop
514 if g_cm_last_upd_dt_list1.exists(i) then
515 g_cm_last_upd_dt_list1(i) := null;
516 end if;
517 end loop;
518 for i in 1..g_cm_last_upd_dt_list2.count loop
519 if g_cm_last_upd_dt_list2.exists(i) then
520 g_cm_last_upd_dt_list2(i) := null;
521 end if;
522 end loop;
523 for i in 1..g_cm_pr_last_upd_dt_list1.count loop
524 if g_cm_pr_last_upd_dt_list1.exists(i) then
525 g_cm_pr_last_upd_dt_list1(i) := null;
526 end if;
527 end loop;
528 for i in 1..g_cm_pr_last_upd_dt_list2.count loop
529 if g_cm_pr_last_upd_dt_list2.exists(i) then
530 g_cm_pr_last_upd_dt_list2(i) := null;
531 end if;
532 end loop;
533 for i in 1..g_cm_sent_dt_list1.count loop
534 if g_cm_sent_dt_list1.exists(i) then
535 g_cm_sent_dt_list1(i) := null;
536 end if;
537 end loop;
538 for i in 1..g_cm_sent_dt_list2.count loop
539 if g_cm_sent_dt_list2.exists(i) then
540 g_cm_sent_dt_list2(i) := null;
541 end if;
542 end loop;
543 for i in 1..g_cm_to_be_sent_dt_list1.count loop
544 if g_cm_to_be_sent_dt_list1.exists(i) then
545 g_cm_to_be_sent_dt_list1(i) := null;
546 end if;
547 end loop;
548 for i in 1..g_cm_to_be_sent_dt_list2.count loop
549 if g_cm_to_be_sent_dt_list2.exists(i) then
550 g_cm_to_be_sent_dt_list2(i) := null;
551 end if;
552 end loop;
553 for i in 1..g_crit_typ_list.count loop
554 if g_crit_typ_list.exists(i) then
555 g_crit_typ_list(i) := null;
556 end if;
557 end loop;
558 for i in 1..g_crit_val_list.count loop
559 if g_crit_val_list.exists(i) then
560 g_crit_val_list(i) := null;
561 end if;
562 end loop;
563 for i in 1..g_oper_list.count loop
564 if g_oper_list.exists(i) then
565 g_oper_list(i) := null;
566 end if;
567 end loop;
568 for i in 1..g_val1_list.count loop
569 if g_val1_list.exists(i) then
570 g_val1_list(i) := null;
571 end if;
572 end loop;
573 for i in 1..g_val2_list.count loop
574 if g_val2_list.exists(i) then
575 g_val2_list(i) := null;
576 end if;
577 end loop;
578 for i in 1..g_val2_list.count loop
579 if g_val2_list.exists(i) then
580 g_val2_list(i) := null;
581 end if;
582 end loop;
583 for i in 1..g_actn_name_list.count loop
584 if g_actn_name_list.exists(i) then
585 g_actn_name_list(i) := null;
586 end if;
587 end loop;
588 for i in 1..g_actn_item_rltn_list.count loop
589 if g_actn_item_rltn_list.exists(i) then
590 g_actn_item_rltn_list(i) := null;
591 end if;
592 end loop;
593 for i in 1..g_prem_month_year_dt_list1.count loop
594 if g_prem_month_year_dt_list1.exists(i) then
595 g_prem_month_year_dt_list1(i) := null;
596 end if;
597 end loop;
598 for i in 1..g_prem_month_year_dt_list2.count loop
599 if g_prem_month_year_dt_list2.exists(i) then
600 g_prem_month_year_dt_list2(i) := null;
601 end if;
602 end loop;
603 for i in 1..g_payroll_last_dt_list1.count loop
604 if g_payroll_last_dt_list1.exists(i) then
605 g_payroll_last_dt_list1(i) := null;
606 end if;
607 end loop;
608 for i in 1..g_payroll_last_dt_list2.count loop
609 if g_payroll_last_dt_list2.exists(i) then
610 g_payroll_last_dt_list2(i) := null;
611 end if;
612 end loop;
613
614 for i in 1..g_tc_status_list.count loop
615 if g_tc_status_list.exists(i) then
616 g_tc_status_list(i) := null;
617 end if;
618 end loop;
619 for i in 1..g_tc_deleted_list.count loop
620 if g_tc_deleted_list.exists(i) then
621 g_tc_deleted_list(i) := null;
622 end if;
623 end loop;
624 for i in 1..g_project_id_list.count loop
625 if g_project_id_list.exists(i) then
626 g_project_id_list(i) := null;
627 end if;
628 end loop;
629 for i in 1..g_task_id_list.count loop
630 if g_task_id_list.exists(i) then
631 g_task_id_list(i) := null;
632 end if;
633 end loop;
634 for i in 1..g_exp_typ_id_list.count loop
635 if g_exp_typ_id_list.exists(i) then
636 g_exp_typ_id_list(i) := null;
637 end if;
638 end loop;
639 for i in 1..g_po_num_list.count loop
640 if g_po_num_list.exists(i) then
641 g_po_num_list(i) := null;
642 end if;
643 end loop;
644
645 --- cwb
646 for i in 1..g_cwb_pl_list.count loop
647 if g_cwb_pl_list.exists(i) then
648 g_cwb_pl_list(i) := null;
649 end if;
650 end loop;
651
652 for i in 1..g_cwb_prd_list.count loop
653 if g_cwb_prd_list.exists(i) then
654 g_cwb_prd_list(i) := null;
655 end if;
656 end loop;
657
658
659 --- subhead
660 for i in 1..g_subhead_rule_list.count loop
661 if g_subhead_rule_list.exists(i) then
662 g_subhead_rule_list(i) := null;
663 end if;
664 end loop;
665
666 for i in 1..g_subhead_pos_list.count loop
667 if g_subhead_pos_list.exists(i) then
668 g_subhead_pos_list(i) := null;
669 end if;
670 end loop;
671
672
673 for i in 1..g_subhead_job_list.count loop
674 if g_subhead_job_list.exists(i) then
675 g_subhead_job_list(i) := null;
676 end if;
677 end loop;
678
679
680 for i in 1..g_subhead_loc_list.count loop
681 if g_subhead_loc_list.exists(i) then
682 g_subhead_loc_list(i) := null;
683 end if;
684 end loop;
685
686
687 for i in 1..g_subhead_pay_list.count loop
688 if g_subhead_pay_list.exists(i) then
689 g_subhead_pay_list(i) := null;
690 end if;
691 end loop;
692
693 for i in 1..g_subhead_org_list.count loop
694 if g_subhead_org_list.exists(i) then
695 g_subhead_org_list(i) := null;
696 end if;
697 end loop;
698
699 for i in 1..g_subhead_bg_list.count loop
700 if g_subhead_bg_list.exists(i) then
701 g_subhead_bg_list(i) := null;
702 end if;
703 end loop;
704
705 for i in 1..g_subhead_grd_list.count loop
706 if g_subhead_grd_list.exists(i) then
707 g_subhead_grd_list(i) := null;
708 end if;
709 end loop;
710
711 -- end of initialization of global lists
712 --
713 if g_debug then
714 hr_utility.set_location(' Exiting:'||l_proc, 15);
715 end if;
716 --
717 end initialize_all_cache_area;
718 -----------------------------------------------------------------------------
719 -------------------------< result >------------------------------------------
720 -----------------------------------------------------------------------------
721 -- This procedure is used to compare the criteria for combination
722 -- criteria type
723 --
724 Procedure result
725 (p_chg_evt_cd in varchar2,
726 p_chg_evt_source in varchar2 default null ,
727 p_eff_dt in date,
728 p_actl_dt in date,
729 p_crit_typ_cd in varchar2,
730 p_oper_cd in varchar2,
731 p_val1 in varchar2,
732 p_val2 in varchar2,
733 p_result out nocopy varchar2)
734 is
735 --
736 l_eff_dt_1 date;
737 l_eff_dt_2 date;
738 l_actl_dt_1 date;
739 l_actl_dt_2 date;
740 l_success varchar2(1);
741 l_proc varchar2(72);
742 --
743 Begin
744 --
745 if g_debug then
746 l_proc := g_package||'result';
747 hr_utility.set_location('Entering:'||l_proc, 5);
748 end if;
749 --
750 l_success := 'N';
751 --
752 -- Operator is 'between'
753 if p_oper_cd = 'BE' then
754 if p_crit_typ_cd = 'CAD' then
755 l_actl_dt_1 := trunc(ben_ext_util.calc_ext_date
756 (p_val1,
757 ben_ext_person.g_effective_date,
758 ben_ext_thread.g_ext_dfn_id)
759 );
760 l_actl_dt_2 := trunc(ben_ext_util.calc_ext_date
761 (p_val2,
762 ben_ext_person.g_effective_date,
763 ben_ext_thread.g_ext_dfn_id)
764 );
765 if trunc(p_actl_dt) >= l_actl_dt_1 and trunc(p_actl_dt) <= l_actl_dt_2 then
766 l_success := 'Y';
767 else
768 l_success := 'N';
769 end if;
770 elsif p_crit_typ_cd = 'CED' then
771 l_eff_dt_1 := trunc(ben_ext_util.calc_ext_date
772 (p_val1,
773 ben_ext_person.g_effective_date,
774 ben_ext_thread.g_ext_dfn_id)
775 );
776 l_eff_dt_2 := trunc(ben_ext_util.calc_ext_date
777 (p_val2,
778 ben_ext_person.g_effective_date,
779 ben_ext_thread.g_ext_dfn_id)
780 );
781 if trunc(p_eff_dt) >= l_eff_dt_1 and trunc(p_eff_dt) <= l_eff_dt_2 then
782 l_success := 'Y';
783 else
784 l_success := 'N';
785 end if;
786 end if;
787 -- Operator 'between' ends
788 -- Operator '='
789 elsif p_oper_cd = 'EQ' then
790 if p_crit_typ_cd in ( 'CCE' , 'CPE') then
791 if p_chg_evt_cd = p_val1 then
792 l_success := 'Y';
793 else
794 l_success := 'N';
795 end if;
796 elsif p_crit_typ_cd = 'CAD' then
797 l_actl_dt_1 := trunc(ben_ext_util.calc_ext_date
798 (p_val1,
799 ben_ext_person.g_effective_date,
800 ben_ext_thread.g_ext_dfn_id)
801 );
802 if trunc(p_actl_dt) = l_actl_dt_1 then
803 l_success := 'Y';
804 else
805 l_success := 'N';
806 end if;
807 elsif p_crit_typ_cd = 'CED' then
808 l_eff_dt_1 := trunc(ben_ext_util.calc_ext_date
809 (p_val1,
810 ben_ext_person.g_effective_date,
811 ben_ext_thread.g_ext_dfn_id)
812 );
813 if trunc(p_eff_dt) = l_eff_dt_1 then
814 l_success := 'Y';
815 else
816 l_success := 'N';
817 end if;
818 end if;
819 -- Operator '=' ends
820 -- Operator '<>'
821 elsif p_oper_cd = 'NE' then
822 if p_crit_typ_cd in ( 'CCE' , 'CPE') then
823 if p_chg_evt_cd <> p_val1 then
824 l_success := 'Y';
825 else
826 l_success := 'N';
827 end if;
828 end if;
829 end if;
830 --
831 p_result := l_success;
832 --
833 --
834 if g_debug then
835 hr_utility.set_location('Exiting:'||l_proc, 15);
836 end if;
837 --
838 End;
839 --
840 -----------------------------------------------------------------------------
841 -------------------------< get_incl_crit_values >----------------------------
842 -----------------------------------------------------------------------------
843 --
844 Procedure get_incl_crit_values (p_crit_typ_id in number,
845 p_incl_rqd in out nocopy varchar2,
846 p_list out nocopy num_list)
847 is
848 --
849 CURSOR get_values IS
850 SELECT val_1
851 FROM ben_ext_crit_val
852 WHERE ext_crit_typ_id = p_crit_typ_id;
853 --
854 l_val_1 ben_ext_crit_val.val_1%type;
855 l_proc varchar2(72);
856 --- more then one criteria validated for pgm,pl,opt,pl_typ id so all the crieria will be calling the
857 -- same function to store the value
858 -- now the dups are not validated , can be done in future
859 l_index binary_integer := nvl(p_list.count(),0);
860 --
861 Begin
862 --
863 if g_debug then
864 l_proc := g_package||'get_incl_crit_values';
865 hr_utility.set_location('Entering:'||l_proc||'num', 5);
866 end if;
867 --
868 open get_values;
869 loop
870 fetch get_values into l_val_1;
871 exit when get_values%notfound;
872 l_index := l_index + 1;
873 p_list(l_index) := to_number(l_val_1);
874 end loop;
875 if p_list.count > 0 then
876 p_incl_rqd := 'Y';
877 end if;
878 close get_values;
879 --
880 if g_debug then
881 hr_utility.set_location(' Exiting:'||l_proc || p_incl_rqd , 15);
882 end if;
883 --
884 End get_incl_crit_values;
885 --
886 -----------------------------------------------------------------------------
887 -------------------------< get_incl_crit_values >----------------------------
888 -----------------------------------------------------------------------------
889 --
890 Procedure get_incl_crit_values (p_crit_typ_id in number,
891 p_incl_rqd in out nocopy varchar2,
892 p_list out nocopy char_list)
893 is
894 --
895 CURSOR get_values IS
896 SELECT val_1
897 FROM ben_ext_crit_val
898 WHERE ext_crit_typ_id = p_crit_typ_id;
899 --
900 l_val_1 ben_ext_crit_val.val_1%type;
901 l_proc varchar2(72);
902 --- more then one criteria validated for pgm,pl,opt,pl_typ id so all the crieria will be calling the
903 -- same function to store the value
904 -- now the dups are not validated , can be done in future
905 l_index binary_integer := nvl(p_list.count(),0);
906
907 --
908 Begin
909 --
910 if g_debug then
911 l_proc := g_package||'get_incl_crit_values';
912 hr_utility.set_location('Entering:'||l_proc || 'char', 5);
913 end if;
914 --
915 open get_values;
916 loop
917 fetch get_values into l_val_1;
918 exit when get_values%notfound;
919 l_index := l_index + 1;
920 p_list(l_index) := l_val_1;
921 end loop;
922 if p_list.count > 0 then
923 p_incl_rqd := 'Y';
924 end if;
925 close get_values;
926 --
927 if g_debug then
928 hr_utility.set_location(' Exiting:'||l_proc, 15);
929 end if;
930 --
931 End get_incl_crit_values;
932 --
933 -----------------------------------------------------------------------------
934 -------------------------< get_incl_crit_values >----------------------------
935 -----------------------------------------------------------------------------
936 --
937 Procedure get_incl_crit_values (p_crit_typ_id in number,
938 p_incl_rqd in out nocopy varchar2,
939 p_list1 out nocopy num_list,
940 p_list2 out nocopy num_list)
941 is
942 --
943 CURSOR get_values IS
944 SELECT val_1, val_2
945 FROM ben_ext_crit_val
946 WHERE ext_crit_typ_id = p_crit_typ_id;
947 --
948 l_val_1 ben_ext_crit_val.val_1%type;
949 l_val_2 ben_ext_crit_val.val_2%type;
950 l_proc varchar2(72);
951 l_index binary_integer := 0;
952 --
953 Begin
954 --
955 if g_debug then
956 l_proc := g_package||'get_incl_crit_values';
957 hr_utility.set_location('Entering:'||l_proc, 5);
958 end if;
959 --
960 open get_values;
961 loop
962 fetch get_values into l_val_1, l_val_2;
963 exit when get_values%notfound;
964 l_index := l_index + 1;
965 p_list1(l_index) := to_number(l_val_1);
966 p_list2(l_index) := to_number(l_val_2);
967 end loop;
968 if p_list1.count > 0 then
969 p_incl_rqd := 'Y';
970 end if;
971 close get_values;
972 --
973 if g_debug then
974 hr_utility.set_location(' Exiting:'||l_proc, 15);
975 end if;
976 --
977 End get_incl_crit_values;
978 --
979 -----------------------------------------------------------------------------
980 -------------------------< get_incl_crit_values >----------------------------
981 -----------------------------------------------------------------------------
982 --
983 Procedure get_incl_crit_values (p_crit_typ_id in number,
984 p_incl_rqd in out nocopy varchar2,
985 p_list1 out nocopy char_list,
986 p_list2 out nocopy char_list)
987 is
988 --
989 CURSOR get_values IS
990 SELECT val_1, val_2
991 FROM ben_ext_crit_val
992 WHERE ext_crit_typ_id = p_crit_typ_id;
993 --
994 l_val_1 ben_ext_crit_val.val_1%type;
995 l_val_2 ben_ext_crit_val.val_2%type;
996 l_proc varchar2(72);
997 l_index binary_integer := 0;
998 --
999 --
1000 Begin
1001 --
1002 if g_debug then
1003 l_proc := g_package||'get_incl_crit_values';
1004 hr_utility.set_location('Entering:'||l_proc, 5);
1005 end if;
1006 --
1007 open get_values;
1008 loop
1009 fetch get_values into l_val_1, l_val_2;
1010 exit when get_values%notfound;
1011 l_index := l_index + 1;
1012 p_list1(l_index) := l_val_1;
1013 p_list2(l_index) := l_val_2;
1014 end loop;
1015 if p_list1.count > 0 then
1016 p_incl_rqd := 'Y';
1017 end if;
1018 close get_values;
1019 --
1020 if g_debug then
1021 hr_utility.set_location(' Exiting:'||l_proc, 15);
1022 end if;
1023 --
1024 End get_incl_crit_values;
1025 --
1026 -----------------------------------------------------------------------------
1027 -------------------------< get_incl_crit_values >----------------------------
1028 -----------------------------------------------------------------------------
1029 --
1030 Procedure get_incl_crit_values (p_crit_typ_id in number,
1031 p_incl_rqd in out nocopy varchar2,
1032 p_crit_typ_list out nocopy char_list,
1033 p_crit_val_list out nocopy num_list,
1034 p_oper_list out nocopy char_list,
1035 p_val1_list out nocopy char_list,
1036 p_val2_list out nocopy char_list)
1037 is
1038 --
1039 CURSOR get_values IS
1040 SELECT ext_crit_val_id
1041 FROM ben_ext_crit_val
1042 WHERE ext_crit_typ_id = p_crit_typ_id
1043 ORDER BY ext_crit_val_id;
1044 --
1045 CURSOR get_cmbn(p_crit_val_id ben_ext_crit_val.ext_crit_val_id%type) IS
1046 SELECT ext_crit_val_id,
1047 crit_typ_cd,
1048 oper_cd,
1049 val_1,
1050 val_2
1051 FROM ben_ext_crit_cmbn
1052 WHERE ext_crit_val_id = p_crit_val_id;
1053
1054 l_get_cmbn get_cmbn%rowtype;
1055 --
1056 l_crit_val_id ben_ext_crit_val.ext_crit_val_id%type;
1057 l_proc varchar2(72);
1058 l_index_val binary_integer := 0;
1059 l_index_cmbn binary_integer := 0;
1060 --
1061 --
1062 Begin
1063 --
1064 if g_debug then
1065 l_proc := g_package||'get_incl_crit_values';
1066 hr_utility.set_location('Entering:'||l_proc, 5);
1067 end if;
1068 --
1069 open get_values;
1070 loop
1071 fetch get_values into l_crit_val_id;
1072 exit when get_values%notfound;
1073 l_index_val := l_index_val + 1;
1074 open get_cmbn(l_crit_val_id);
1075 loop
1076 fetch get_cmbn into l_get_cmbn;
1077 exit when get_cmbn%notfound;
1078 l_index_cmbn := l_index_cmbn + 1;
1079 p_crit_val_list(l_index_cmbn) := l_get_cmbn.ext_crit_val_id;
1080 p_crit_typ_list(l_index_cmbn) := l_get_cmbn.crit_typ_cd;
1081 p_oper_list(l_index_cmbn) := l_get_cmbn.oper_cd;
1082 p_val1_list(l_index_cmbn) := l_get_cmbn.val_1;
1083 p_val2_list(l_index_cmbn) := l_get_cmbn.val_2;
1084 end loop;
1085 close get_cmbn;
1086 end loop;
1087 if p_crit_val_list.count > 0 then
1088 p_incl_rqd := 'Y';
1089 end if;
1090 close get_values;
1091 --
1092 if g_debug then
1093 hr_utility.set_location(' Exiting:'||l_proc, 15);
1094 end if;
1095 --
1096 End get_incl_crit_values;
1097 --
1098 -----------------------------------------------------------------------------
1099 -------------------------< get_incl_crit_values_rg >----------------------------
1100 -----------------------------------------------------------------------------
1101 --
1102 Procedure get_incl_crit_values_rg (p_crit_typ_id in number,
1103 p_incl_rqd in out nocopy varchar2,
1104 p_list out nocopy num_list)
1105 is
1106 --
1107 --
1108 CURSOR get_values is
1109 SELECT pl_id
1110 FROM ben_popl_rptg_grp_f rg,
1111 ben_ext_crit_val xcv
1112 WHERE to_char(rg.rptg_grp_id) = xcv.val_1
1113 and xcv.ext_crit_typ_id = p_crit_typ_id
1114 and rg.pl_id is not null
1115 and ben_ext_person.g_benefits_ext_dt between rg.effective_start_date
1116 and rg.effective_end_date;
1117 --
1118 l_val_1 ben_popl_rptg_grp_f.pl_id%type;
1119 l_proc varchar2(72);
1120 l_index binary_integer := 0;
1121 --
1122 Begin
1123 --
1124 if g_debug then
1125 l_proc := g_package||'get_incl_crit_values_rg';
1126 hr_utility.set_location('Entering:'||l_proc, 5);
1127 end if;
1128 --
1129 open get_values;
1130 loop
1131 fetch get_values into l_val_1;
1132 exit when get_values%notfound;
1133 l_index := l_index + 1;
1134 p_list(l_index) := to_number(l_val_1);
1135 end loop;
1136 if p_list.count > 0 then
1137 p_incl_rqd := 'Y';
1138 end if;
1139 close get_values;
1140 --
1141 if g_debug then
1142 hr_utility.set_location(' Exiting:'||l_proc, 15);
1143 end if;
1144 --
1145 End get_incl_crit_values_rg;
1146 --
1147 -----------------------------------------------------------------------------
1148 ---------------------< determine_incl_crit_to_check >------------------------
1149 -----------------------------------------------------------------------------
1150 --
1151 -- The following procedure sets the cache area.
1152 --
1153 Procedure determine_incl_crit_to_check
1154 (p_ext_crit_prfl_id in ben_ext_crit_prfl.ext_crit_prfl_id%type) is
1155 --
1156 l_proc varchar2(72);
1157 --
1158 CURSOR get_incldd_incl_crit IS
1159 SELECT ext_crit_typ_id, crit_typ_cd, excld_flag
1160 FROM ben_ext_crit_typ
1161 WHERE ext_crit_prfl_id = p_ext_crit_prfl_id
1162 ORDER BY excld_flag ;
1163 --
1164 l_crit_typ_id ben_ext_crit_typ.ext_crit_typ_id%type;
1165 l_crit_typ_cd ben_ext_crit_typ.crit_typ_cd%type;
1166 --
1167 l_excld_flag varchar2(1);
1168 --
1169 Begin
1170 g_debug := hr_utility.debug_enabled;
1171 if g_debug then
1172 l_proc := g_package||'determine_incl_crit_to_check';
1173 hr_utility.set_location('Entering:'||l_proc, 5);
1174 end if;
1175
1176
1177
1178 --
1179 Initialize_All_Cache_Area;
1180 --
1181 open get_incldd_incl_crit;
1182 loop
1183 fetch get_incldd_incl_crit into l_crit_typ_id, l_crit_typ_cd, l_excld_flag;
1184 exit when get_incldd_incl_crit%notfound;
1185
1186 if g_debug then
1187 hr_utility.set_location('crit_typ_cd:'|| l_crit_typ_cd, 5);
1188 end if;
1189
1190 if l_crit_typ_cd = 'PID' then -- specific person
1191 g_person_id_excld_flag := l_excld_flag;
1192 Get_Incl_Crit_Values(l_crit_typ_id,
1193 g_person_id_incl_rqd,
1194 g_person_id_list);
1195 elsif l_crit_typ_cd = 'PPC' then -- postal code
1196 g_postal_code_excld_flag := l_excld_flag;
1197 Get_Incl_Crit_Values(l_crit_typ_id,
1198 g_postal_code_incl_rqd,
1199 g_postal_code_list1,
1200 g_postal_code_list2);
1201 elsif l_crit_typ_cd = 'POR' then -- assignment organization
1202 g_org_id_excld_flag := l_excld_flag;
1203 Get_Incl_Crit_Values(l_crit_typ_id,
1204 g_org_id_incl_rqd,
1205 g_org_id_list);
1206 elsif l_crit_typ_cd = 'PLO' then -- assignment location
1207 g_loc_id_excld_flag := l_excld_flag;
1208 Get_Incl_Crit_Values(l_crit_typ_id,
1209 g_loc_id_incl_rqd,
1210 g_loc_id_list);
1211 elsif l_crit_typ_cd = 'PLE' then -- assignmnet legal entity
1212 g_gre_excld_flag := l_excld_flag;
1213 Get_Incl_Crit_Values(l_crit_typ_id,
1214 g_gre_incl_rqd,
1215 g_gre_list);
1216 elsif l_crit_typ_cd = 'PST' then -- state
1217 g_state_excld_flag := l_excld_flag;
1218 Get_Incl_Crit_Values(l_crit_typ_id,
1219 g_state_incl_rqd,
1220 g_state_list);
1221 elsif l_crit_typ_cd = 'PBG' then -- benefits group
1222 g_bnft_grp_excld_flag := l_excld_flag;
1223 Get_Incl_Crit_Values(l_crit_typ_id,
1224 g_bnft_grp_incl_rqd,
1225 g_bnft_grp_list);
1226 elsif l_crit_typ_cd = 'PAS' then -- assignment status
1227 g_ee_status_excld_flag := l_excld_flag;
1228 Get_Incl_Crit_Values(l_crit_typ_id,
1229 g_ee_status_incl_rqd,
1230 g_ee_status_list);
1231 elsif l_crit_typ_cd = 'RRL' then -- assignmnet payroll
1232 g_payroll_id_excld_flag := l_excld_flag;
1233 Get_Incl_Crit_Values(l_crit_typ_id,
1234 g_payroll_id_incl_rqd,
1235 g_payroll_id_list);
1236
1237 elsif l_crit_typ_cd = 'RFFRL' then -- payroll rule
1238 g_payroll_rl_excld_flag := l_excld_flag;
1239 Get_Incl_Crit_Values(l_crit_typ_id,
1240 g_payroll_rl_incl_rqd,
1241 g_payroll_rl_list);
1242
1243 elsif l_crit_typ_cd in ( 'BNFOPN','BNFOPC') then -- enrollment result - option
1244 g_enrt_opt_excld_flag := l_excld_flag;
1245 Get_Incl_Crit_Values(l_crit_typ_id,
1246 g_enrt_opt_incl_rqd,
1247 g_enrt_opt_list);
1248
1249 --2732104 whether short name or pln name or short code the pgm id is validated
1250 elsif l_crit_typ_cd in ( 'BPL','BNFPLN','BNFPLC') then -- enrollment result - plan
1251 g_enrt_plan_excld_flag := l_excld_flag;
1252 Get_Incl_Crit_Values(l_crit_typ_id,
1253 g_enrt_plan_incl_rqd,
1254 g_enrt_plan_list);
1255 elsif l_crit_typ_cd = 'BRG' then -- enrollment result - reporting group
1256 g_enrt_rg_plan_excld_flag := l_excld_flag;
1257 Get_Incl_Crit_Values_rg(l_crit_typ_id,
1258 g_enrt_rg_plan_incl_rqd,
1259 g_enrt_rg_plan_list);
1260 elsif l_crit_typ_cd = 'BSE' then -- enrollment result - suspended flag
1261 g_enrt_sspndd_excld_flag := l_excld_flag;
1262 Get_Incl_Crit_Values(l_crit_typ_id,
1263 g_enrt_sspndd_incl_rqd,
1264 g_enrt_sspndd_list);
1265 elsif l_crit_typ_cd = 'BERCSD' then -- enrollment result - coverage start date
1266 g_enrt_cvg_strt_dt_excld_flag := l_excld_flag;
1267 Get_Incl_Crit_Values(l_crit_typ_id,
1268 g_enrt_cvg_strt_dt_incl_rqd,
1269 g_enrt_cvg_strt_dt_list1,
1270 g_enrt_cvg_strt_dt_list2);
1271 elsif l_crit_typ_cd = 'BERCDP' then -- enrollment result - coverage during period
1272 g_enrt_cvg_drng_prd_excld_flag := l_excld_flag;
1273 Get_Incl_Crit_Values(l_crit_typ_id,
1274 g_enrt_cvg_drng_perd_incl_rqd,
1275 g_enrt_cvg_drng_perd_list1,
1276 g_enrt_cvg_drng_perd_list2);
1277 elsif l_crit_typ_cd = 'BERSTA' then -- enrollment result - status
1278 g_enrt_stat_excld_flag := l_excld_flag;
1279 Get_Incl_Crit_Values(l_crit_typ_id,
1280 g_enrt_stat_incl_rqd,
1281 g_enrt_stat_list);
1282 elsif l_crit_typ_cd = 'BERENM' then -- enrollment result - enrollment method
1283 g_enrt_mthd_excld_flag := l_excld_flag;
1284 Get_Incl_Crit_Values(l_crit_typ_id,
1285 g_enrt_mthd_incl_rqd,
1286 g_enrt_mthd_list);
1287 --2732104 whether short name or pgm name or short code the pgm id is validated
1288 elsif l_crit_typ_cd in ( 'BERPGN','BNFPGC','BNFPGN') then -- enrollment result - program name
1289 g_enrt_pgm_excld_flag := l_excld_flag;
1290 Get_Incl_Crit_Values(l_crit_typ_id,
1291 g_enrt_pgm_incl_rqd,
1292 g_enrt_pgm_list);
1293 --2732104 whether short name or plan type name or short code the pgm id is validated
1294 elsif l_crit_typ_cd in ('BERPTN','BNFPTN','BNFPTC') then -- enrollment result - plan type name
1295 g_enrt_pl_typ_excld_flag := l_excld_flag;
1296 Get_Incl_Crit_Values(l_crit_typ_id,
1297 g_enrt_pl_typ_incl_rqd,
1298 g_enrt_pl_typ_list);
1299 elsif l_crit_typ_cd = 'BERLUD' then -- enrollment result - last update date
1300 g_enrt_last_upd_dt_excld_flag := l_excld_flag;
1301 Get_Incl_Crit_Values(l_crit_typ_id,
1302 g_enrt_last_upd_dt_incl_rqd,
1303 g_enrt_last_upd_dt_list1,
1304 g_enrt_last_upd_dt_list2);
1305 elsif l_crit_typ_cd = 'BERLEN' then -- enrollment result - life event name
1306 g_enrt_ler_name_excld_flag := l_excld_flag;
1307 Get_Incl_Crit_Values(l_crit_typ_id,
1308 g_enrt_ler_name_incl_rqd,
1309 g_enrt_ler_name_list);
1310 elsif l_crit_typ_cd = 'BERLES' then -- enrollment result - life event status
1311 g_enrt_ler_stat_excld_flag := l_excld_flag;
1312 Get_Incl_Crit_Values(l_crit_typ_id,
1313 g_enrt_ler_stat_incl_rqd,
1314 g_enrt_ler_stat_list);
1315 elsif l_crit_typ_cd = 'BERLOD' then -- enrollment result - life event occurred date
1316 g_enrt_ler_ocrd_dt_excld_flag := l_excld_flag;
1317 Get_Incl_Crit_Values(l_crit_typ_id,
1318 g_enrt_ler_ocrd_dt_incl_rqd,
1319 g_enrt_ler_ocrd_dt_list1,
1320 g_enrt_ler_ocrd_dt_list2);
1321 elsif l_crit_typ_cd = 'BERLND' then -- enrollment result - life event notif date
1322 g_enrt_ler_ntfn_dt_excld_flag := l_excld_flag;
1323 Get_Incl_Crit_Values(l_crit_typ_id,
1324 g_enrt_ler_ntfn_dt_incl_rqd,
1325 g_enrt_ler_ntfn_dt_list1,
1326 g_enrt_ler_ntfn_dt_list2);
1327 elsif l_crit_typ_cd = 'BERMIS' then -- enrollment result - relation
1328 g_enrt_rltn_excld_flag := l_excld_flag;
1329 Get_Incl_Crit_Values(l_crit_typ_id,
1330 g_enrt_rltn_incl_rqd,
1331 g_enrt_rltn_list);
1332 elsif l_crit_typ_cd = 'BEDPLN' then -- dependent - relation
1333 g_enrt_dpnt_rltn_excld_flag := l_excld_flag;
1334 Get_Incl_Crit_Values(l_crit_typ_id,
1335 g_enrt_dpnt_rltn_incl_rqd,
1336 g_enrt_dpnt_rltn_list);
1337
1338 elsif l_crit_typ_cd = 'BACN' then -- action item - name
1339 g_actn_name_excld_flag := l_excld_flag;
1340 Get_Incl_Crit_Values(l_crit_typ_id,
1341 g_actn_name_incl_rqd,
1342 g_actn_name_list);
1343 elsif l_crit_typ_cd = 'BACMIS' then -- action item - relation
1344 g_actn_item_rltn_excld_flag := l_excld_flag;
1345 Get_Incl_Crit_Values(l_crit_typ_id,
1346 g_actn_item_rltn_incl_rqd,
1347 g_actn_item_rltn_list);
1348 elsif l_crit_typ_cd = 'BECESD' then -- electable choice - enrollment period start date
1349 g_elct_enrt_strt_dt_excld_flag := l_excld_flag;
1350 Get_Incl_Crit_Values(l_crit_typ_id,
1351 g_elct_enrt_strt_dt_incl_rqd,
1352 g_elct_enrt_strt_dt_list1,
1353 g_elct_enrt_strt_dt_list2);
1354 elsif l_crit_typ_cd = 'BECLEN' then -- electable choice - life event name
1355 g_elct_ler_name_excld_flag := l_excld_flag;
1356 Get_Incl_Crit_Values(l_crit_typ_id,
1357 g_elct_ler_name_incl_rqd,
1358 g_elct_ler_name_list);
1359 elsif l_crit_typ_cd = 'BECLES' then -- electable choice - life event status
1360 g_elct_ler_stat_excld_flag := l_excld_flag;
1361 Get_Incl_Crit_Values(l_crit_typ_id,
1362 g_elct_ler_stat_incl_rqd,
1363 g_elct_ler_stat_list);
1364 elsif l_crit_typ_cd = 'BECLUD' then -- electable choice - last update date
1365 g_elct_last_upd_dt_excld_flag := l_excld_flag;
1366 Get_Incl_Crit_Values(l_crit_typ_id,
1367 g_elct_last_upd_dt_incl_rqd,
1368 g_elct_last_upd_dt_list1,
1369 g_elct_last_upd_dt_list2);
1370 elsif l_crit_typ_cd = 'BECLND' then -- electable choice - life event notification date
1371 g_elct_ler_ntfn_dt_excld_flag := l_excld_flag;
1372 Get_Incl_Crit_Values(l_crit_typ_id,
1373 g_elct_ler_ntfn_dt_incl_rqd,
1374 g_elct_ler_ntfn_dt_list1,
1375 g_elct_ler_ntfn_dt_list2);
1376 elsif l_crit_typ_cd = 'BECLED' then -- electable choice - life event occurred date
1377 g_elct_ler_ocrd_dt_excld_flag := l_excld_flag;
1378 Get_Incl_Crit_Values(l_crit_typ_id,
1379 g_elct_ler_ocrd_dt_incl_rqd,
1380 g_elct_ler_ocrd_dt_list1,
1381 g_elct_ler_ocrd_dt_list2);
1382
1383
1384 elsif l_crit_typ_cd in ( 'BEFOPN' , 'BEFOPC') then -- electable choice option z
1385
1386 g_elct_opt_excld_flag := l_excld_flag;
1387 Get_Incl_Crit_Values(l_crit_typ_id,
1388 g_elct_opt_incl_rqd,
1389 g_elct_opt_list);
1390
1391 --2732104 whether short name or pl name or short code the pgm id is validated
1392 elsif l_crit_typ_cd in ( 'BECPLN','BEFPLN','BEFPLC') then -- electable choice - plan name
1393 g_elct_plan_excld_flag := l_excld_flag;
1394 Get_Incl_Crit_Values(l_crit_typ_id,
1395 g_elct_plan_incl_rqd,
1396 g_elct_plan_list);
1397 elsif l_crit_typ_cd = 'BECRPG' then -- electable choice - reporting group
1398 g_elct_rg_plan_excld_flag := l_excld_flag;
1399 Get_Incl_Crit_Values_rg(l_crit_typ_id,
1400 g_elct_rg_plan_incl_rqd,
1401 g_elct_rg_plan_list);
1402 --2732104 whether short name or pgm name or short code the pgm id is validated
1403 elsif l_crit_typ_cd in ( 'BECPGN','BEFPGN','BEFPGC') then -- electable choice - program name
1404 g_elct_pgm_excld_flag := l_excld_flag;
1405 Get_Incl_Crit_Values(l_crit_typ_id,
1406 g_elct_pgm_incl_rqd,
1407 g_elct_pgm_list);
1408 --2732104 whether short name or plan type name or short code the pl_type id is validated
1409 elsif l_crit_typ_cd in ('BECPTN','BEFPTN','BEFPTC') then -- electable choice - plan type name
1410 g_elct_pl_typ_excld_flag := l_excld_flag;
1411 Get_Incl_Crit_Values(l_crit_typ_id,
1412 g_elct_pl_typ_incl_rqd,
1413 g_elct_pl_typ_list);
1414 elsif l_crit_typ_cd = 'BECYRP' then -- electable choice - year period
1415 g_elct_yrprd_excld_flag := l_excld_flag;
1416 Get_Incl_Crit_Values(l_crit_typ_id,
1417 g_elct_yrprd_incl_rqd,
1418 g_elct_yrprd_list);
1419 elsif l_crit_typ_cd = 'BECMIS' then -- electable choice - misc
1420 g_elct_rltn_excld_flag := l_excld_flag;
1421 Get_Incl_Crit_Values(l_crit_typ_id,
1422 g_elct_rltn_incl_rqd,
1423 g_elct_rltn_list);
1424
1425 /* Get_Incl_Crit_Values(l_crit_typ_id,
1426 g_per_plan_incl_rqd,
1427 g_per_plan_list); */
1428 /* elsif l_crit_typ_cd = 'BPT' then -- tvh remove this altogether.
1429 g_part_type_excld_flag := l_excld_flag;
1430 Get_Incl_Crit_Values(l_crit_typ_id,
1431 g_part_type_incl_rqd,
1432 g_part_type_list); */
1433
1434 elsif l_crit_typ_cd = 'REE' then -- element input
1435 g_ele_input_excld_flag := l_excld_flag;
1436 Get_Incl_Crit_Values(l_crit_typ_id,
1437 g_ele_input_incl_rqd,
1438 g_ele_input_list,
1439 g_ele_type_list);
1440 elsif l_crit_typ_cd = 'PRL' then -- person rule
1441 g_person_rule_excld_flag := l_excld_flag;
1442 Get_Incl_Crit_Values(l_crit_typ_id,
1443 g_person_rule_incl_rqd,
1444 g_person_rule_list);
1445 elsif l_crit_typ_cd = 'PLV' then -- person life event ?
1446 g_per_ler_excld_flag := l_excld_flag;
1447 Get_Incl_Crit_Values(l_crit_typ_id,
1448 g_per_ler_incl_rqd,
1449 g_per_ler_list);
1450 elsif l_crit_typ_cd = 'PPT' then -- person type
1451 g_person_type_excld_flag := l_excld_flag;
1452 Get_Incl_Crit_Values(l_crit_typ_id,
1453 g_person_type_incl_rqd,
1454 g_person_type_list);
1455 elsif l_crit_typ_cd = 'CCE' then -- change event
1456 g_chg_evt_excld_flag := l_excld_flag;
1457 Get_Incl_Crit_Values(l_crit_typ_id,
1458 g_chg_evt_incl_rqd,
1459 g_chg_evt_list);
1460 elsif l_crit_typ_cd = 'CPE' then -- change event
1461 g_chg_pay_evt_excld_flag := l_excld_flag;
1462 Get_Incl_Crit_Values(l_crit_typ_id,
1463 g_chg_pay_evt_incl_rqd,
1464 g_chg_pay_evt_list);
1465
1466 elsif l_crit_typ_cd = 'CED' then -- change effective date
1467 g_chg_eff_dt_excld_flag := l_excld_flag;
1468 Get_Incl_Crit_Values(l_crit_typ_id,
1469 g_chg_eff_dt_incl_rqd,
1470 g_chg_eff_dt_list1,
1471 g_chg_eff_dt_list2);
1472 elsif l_crit_typ_cd = 'CAD' then -- change actual date
1473 g_chg_actl_dt_excld_flag := l_excld_flag;
1474 Get_Incl_Crit_Values(l_crit_typ_id,
1475 g_chg_actl_dt_incl_rqd,
1476 g_chg_actl_dt_list1,
1477 g_chg_actl_dt_list2);
1478 elsif l_crit_typ_cd = 'CBU' then -- changed by user
1479 g_chg_login_excld_flag := l_excld_flag;
1480 Get_Incl_Crit_Values(l_crit_typ_id,
1481 g_chg_login_incl_rqd,
1482 g_chg_login_list);
1483 elsif l_crit_typ_cd = 'MTP' then -- communication type
1484 g_cm_typ_excld_flag := l_excld_flag;
1485 Get_Incl_Crit_Values(l_crit_typ_id,
1486 g_cm_typ_incl_rqd,
1487 g_cm_typ_list);
1488 elsif l_crit_typ_cd = 'MPCLUD' then -- per communication last update date
1489 g_cm_last_upd_dt_excld_flag := l_excld_flag;
1490 Get_Incl_Crit_Values(l_crit_typ_id,
1491 g_cm_last_upd_dt_incl_rqd,
1492 g_cm_last_upd_dt_list1,
1493 g_cm_last_upd_dt_list2);
1494 elsif l_crit_typ_cd = 'MPCPLUD' then -- per communication provided last update date
1495 g_cm_pr_last_upd_dt_excld_flag := l_excld_flag;
1496 Get_Incl_Crit_Values(l_crit_typ_id,
1497 g_cm_pr_last_upd_dt_incl_rqd,
1498 g_cm_pr_last_upd_dt_list1,
1499 g_cm_pr_last_upd_dt_list2);
1500 elsif l_crit_typ_cd = 'MSDT' then -- per communication provided sent date
1501 g_cm_sent_dt_excld_flag := l_excld_flag;
1502 Get_Incl_Crit_Values(l_crit_typ_id,
1503 g_cm_sent_dt_incl_rqd,
1504 g_cm_sent_dt_list1,
1505 g_cm_sent_dt_list2);
1506 elsif l_crit_typ_cd = 'MTBSDT' then -- per communication provided to be sent date
1507 g_cm_to_be_sent_dt_excld_flag := l_excld_flag;
1508 Get_Incl_Crit_Values(l_crit_typ_id,
1509 g_cm_to_be_sent_dt_incl_rqd,
1510 g_cm_to_be_sent_dt_list1,
1511 g_cm_to_be_sent_dt_list2);
1512 elsif l_crit_typ_cd = 'ADV' then -- combination criteria
1513
1514 Get_Incl_Crit_Values(l_crit_typ_id,
1515 g_cmbn_incl_rqd,
1516 g_crit_typ_list,
1517 g_crit_val_list,
1518 g_oper_list,
1519 g_val1_list,
1520 g_val2_list);
1521 elsif l_crit_typ_cd = 'EPLDT' then -- premium last update date
1522 if g_debug then
1523 hr_utility.set_location('crit_type ' ||'EPLDT',16);
1524 end if;
1525 g_prem_last_updt_dt_excld_flag := l_excld_flag;
1526 Get_Incl_Crit_Values(l_crit_typ_id,
1527 g_prem_last_updt_dt_rqd ,
1528 g_prem_last_upd_dt_list1,
1529 g_prem_last_upd_dt_list2);
1530
1531 elsif l_crit_typ_cd = 'EPMNYR' then -- premium month and year
1532 g_prem_month_year_excld_flag := l_excld_flag;
1533 if g_debug then
1534 hr_utility.set_location('crit_type ' ||'EPMNYR',16);
1535 end if;
1536 Get_Incl_Crit_Values(l_crit_typ_id,
1537 g_prem_month_year_rqd,
1538 g_prem_month_year_dt_list1,
1539 g_prem_month_year_dt_list2) ;
1540
1541 elsif l_crit_typ_cd = 'RPPEDT' then -- pay period end date
1542 g_payroll_last_Date_excld_flag := l_excld_flag;
1543 if g_debug then
1544 hr_utility.set_location('crit_type ' ||'EPMNYR',16);
1545 end if;
1546 Get_Incl_Crit_Values(l_crit_typ_id,
1547 g_payroll_last_date_incl_rqd,
1548 g_payroll_last_dt_list1,
1549 g_payroll_last_dt_list2) ;
1550
1551 ---intilise the gloabl variable
1552 if g_payroll_last_dt_list1.count > 0 and g_payroll_last_dt_list2.count > 0 then
1553 ben_ext_person.g_pay_last_start_date := ben_ext_util.calc_ext_date
1554 (p_ext_date_cd => g_payroll_last_dt_list1(1),
1555 p_abs_date => ben_extract.g_effective_date,
1556 p_ext_dfn_id => ben_extract.g_ext_dfn_id);
1557
1558 ben_ext_person.g_pay_last_end_date := ben_ext_util.calc_ext_date
1559 (p_ext_date_cd => g_payroll_last_dt_list2(1),
1560 p_abs_date => ben_extract.g_effective_date,
1561 p_ext_dfn_id => ben_extract.g_ext_dfn_id);
1562
1563 end if ;
1564
1565 elsif l_crit_typ_cd = 'PASU' then --person assignment to use
1566 Get_Incl_Crit_Values(l_crit_typ_id,
1567 g_asg_to_use_rqd,
1568 g_asg_to_use_list);
1569
1570
1571 elsif l_crit_typ_cd = 'WPLPR' then -- CWB
1572 g_cwb_pl_prd_excld_flag := l_excld_flag;
1573 Get_Incl_Crit_Values(l_crit_typ_id,
1574 g_cwb_pl_prd_rqd,
1575 g_cwb_prd_list,
1576 g_cwb_pl_list ) ;
1577
1578 elsif l_crit_typ_cd = 'HRL' then -- subheader rule
1579 g_subhead_rule_excld_flag := l_excld_flag;
1580 Get_Incl_Crit_Values(l_crit_typ_id,
1581 g_subhead_rule_rqd,
1582 g_subhead_rule_list);
1583
1584 elsif l_crit_typ_cd = 'HJOB' then -- subheader job
1585 g_subhead_job_excld_flag := l_excld_flag;
1586 Get_Incl_Crit_Values(l_crit_typ_id,
1587 g_subhead_job_rqd,
1588 g_subhead_job_list);
1589
1590 elsif l_crit_typ_cd = 'HPOS' then -- subheader POS
1591 g_subhead_pos_excld_flag := l_excld_flag;
1592 Get_Incl_Crit_Values(l_crit_typ_id,
1593 g_subhead_pos_rqd,
1594 g_subhead_pos_list);
1595
1596 elsif l_crit_typ_cd = 'HLOC' then -- subheader LOC
1597 g_subhead_loc_excld_flag := l_excld_flag;
1598 Get_Incl_Crit_Values(l_crit_typ_id,
1599 g_subhead_loc_rqd,
1600 g_subhead_loc_list);
1601
1602 elsif l_crit_typ_cd = 'HPY' then -- subheader PAY
1603 g_subhead_pay_excld_flag := l_excld_flag;
1604 Get_Incl_Crit_Values(l_crit_typ_id,
1605 g_subhead_pay_rqd,
1606 g_subhead_pay_list);
1607
1608 elsif l_crit_typ_cd = 'HORG' then -- subheader ORG
1609 g_subhead_org_excld_flag := l_excld_flag;
1610 Get_Incl_Crit_Values(l_crit_typ_id,
1611 g_subhead_org_rqd,
1612 g_subhead_org_list);
1613
1614
1615 elsif l_crit_typ_cd = 'HBG' then -- subheader business group
1616 g_subhead_bg_excld_flag := l_excld_flag;
1617 Get_Incl_Crit_Values(l_crit_typ_id,
1618 g_subhead_bg_rqd,
1619 g_subhead_bg_list);
1620
1621
1622 elsif l_crit_typ_cd = 'HGRD' then -- subheader grade group
1623 g_subhead_grd_excld_flag := l_excld_flag;
1624 Get_Incl_Crit_Values(l_crit_typ_id,
1625 g_subhead_grd_rqd,
1626 g_subhead_grd_list);
1627
1628 -- OTL inclusion criteria
1629
1630
1631 elsif l_crit_typ_cd = 'OTL_TC_STATUS' then
1632 Get_Incl_Crit_Values(l_crit_typ_id,
1633 g_tc_status_incl_rqd,
1634 g_tc_status_list);
1635
1636 elsif l_crit_typ_cd = 'OTL_TC_DELETED' then
1637 Get_Incl_Crit_Values(l_crit_typ_id,
1638 g_tc_deleted_incl_rqd,
1639 g_tc_deleted_list);
1640
1641 elsif l_crit_typ_cd = 'OTL_PROJECT_ID' then
1642 Get_Incl_Crit_Values(l_crit_typ_id,
1643 g_project_id_incl_rqd,
1644 g_project_id_list);
1645
1646 elsif l_crit_typ_cd = 'OTL_TASK_ID' then
1647 Get_Incl_Crit_Values(l_crit_typ_id,
1648 g_task_id_incl_rqd,
1649 g_task_id_list);
1650
1651 elsif l_crit_typ_cd = 'OTL_EXP_TYP_ID' then
1652 Get_Incl_Crit_Values(l_crit_typ_id,
1653 g_exp_typ_id_incl_rqd,
1654 g_exp_typ_id_list);
1655
1656 elsif l_crit_typ_cd = 'OTL_ELEMENT_TYPE_ID' then
1657 Get_Incl_Crit_Values(l_crit_typ_id,
1658 g_element_type_id_incl_rqd,
1659 g_element_type_list);
1660
1661 elsif l_crit_typ_cd = 'OTL_PO_NUM' then
1662 Get_Incl_Crit_Values(l_crit_typ_id,
1663 g_po_num_incl_rqd,
1664 g_po_num_list);
1665
1666 end if;
1667 --
1668 end loop;
1669 --
1670 close get_incldd_incl_crit;
1671 --
1672 if g_debug then
1673 hr_utility.set_location(' Exiting:'||l_proc, 15);
1674 end if;
1675 --
1676 end determine_incl_crit_to_check;
1677 --
1678
1679
1680
1681
1682
1683 -----------------------------------------------------------------------------
1684 ------------------------< chk_person_id_incl >-------------------------------
1685 -----------------------------------------------------------------------------
1686 --
1687 Procedure chk_person_id_incl
1688 (p_person_id in per_all_people_f.person_id%type,
1689 p_excld_flag in varchar2)
1690 is
1691 --
1692 --
1693 l_proc varchar2(72);
1694 --
1695 Begin
1696 --
1697 if g_debug then
1698 l_proc := g_package||'chk_person_id_incl';
1699 hr_utility.set_location(' Entering:'||l_proc, 5);
1700 end if;
1701 --
1702 for i in 1..g_person_id_list.count
1703 loop
1704 if p_person_id = g_person_id_list(i) then
1705 if p_excld_flag = 'N' then
1706 raise g_include;
1707 else
1708 raise g_not_include;
1709 end if;
1710 end if;
1711 end loop;
1712 --
1713 if p_excld_flag = 'Y' then
1714 raise g_include;
1715 end if;
1716 --
1717 if g_debug then
1718 hr_utility.set_location(' Exiting:'||l_proc, 15);
1719 end if;
1720 --
1721 raise g_not_include;
1722 --
1723 Exception
1724 --
1725 when g_include then
1726 null;
1727 --
1728 when g_not_include then
1729 raise g_not_include; --- will be handled in the calling program
1730 --
1731 End Chk_Person_Id_Incl;
1732 --
1733 -----------------------------------------------------------------------------
1734 ------------------------< chk_postal_code_incl >-----------------------------
1735 -----------------------------------------------------------------------------
1736 --
1737 Procedure chk_postal_code_incl
1738 (p_person_id in per_all_people_f.person_id%type,
1739 p_postal_code in per_addresses.postal_code%type,
1740 p_effective_date in date,
1741 p_excld_flag in varchar2)
1742 is
1743 --
1744 cursor get_postal_code is
1745 SELECT postal_code
1746 FROM per_addresses addr
1747 WHERE addr.person_id = p_person_id
1748 AND primary_flag = 'Y'
1749 AND p_effective_date between date_from
1750 and nvl(date_to, p_effective_date);
1751 --
1752 l_postal_code per_addresses.postal_code%type;
1753 --
1754 l_proc varchar2(72);
1755 --
1756 Begin
1757 --
1758 if g_debug then
1759 l_proc := g_package||'chk_postal_code_incl';
1760 hr_utility.set_location(' Entering:'||l_proc, 5);
1761 end if;
1762 --
1763 if p_postal_code is null then
1764 open get_postal_code;
1765 fetch get_postal_code into l_postal_code;
1766 close get_postal_code;
1767 else
1768 l_postal_code := p_postal_code;
1769 end if;
1770 for i in 1..g_postal_code_list1.count
1771 loop
1772 if l_postal_code >=
1773 g_postal_code_list1(i)
1774 and l_postal_code <=
1775 nvl(g_postal_code_list2(i), g_postal_code_list1(i)) then -- 3108106 added nvl
1776 if p_excld_flag = 'N' then
1777 raise g_include;
1778 else
1779 raise g_not_include;
1780 end if;
1781 end if;
1782 end loop;
1783 --
1784 if p_excld_flag = 'Y' then
1785 raise g_include;
1786 end if;
1787 --
1788 if g_debug then
1789 hr_utility.set_location(' Exiting:'||l_proc, 15);
1790 end if;
1791 --
1792 --
1793 raise g_not_include;
1794 --
1795 Exception
1796 --
1797 when g_include then
1798 null;
1799 --
1800 when g_not_include then
1801 raise g_not_include; --- will be handled in the calling program
1802 --
1803 End Chk_Postal_Code_Incl;
1804 --
1805 -----------------------------------------------------------------------------
1806 -----------------------------< chk_org_id_incl >-----------------------------
1807 -----------------------------------------------------------------------------
1808 --
1809 Procedure chk_org_id_incl
1810 (p_person_id in per_all_people_f.person_id%type,
1811 p_org_id in per_all_assignments_f.organization_id%type,
1812 p_effective_date in date,
1813 p_excld_flag in varchar2)
1814 is
1815 --
1816 cursor get_org_id is
1817 SELECT organization_id
1818 FROM per_all_assignments_f asn
1819 WHERE asn.person_id = p_person_id
1820 and asn.assignment_id = ben_ext_person.g_assignment_id --1969853
1821 AND asn.primary_flag = 'Y'
1822 AND p_effective_date between effective_start_date
1823 and effective_end_date;
1824 --
1825 l_org_id per_all_assignments_f.organization_id%type;
1826 --
1827 l_proc varchar2(72);
1828 --
1829 Begin
1830 --
1831 if g_debug then
1832 l_proc := g_package||'chk_org_id_incl';
1833 hr_utility.set_location(' Entering:'||l_proc, 5);
1834 end if;
1835 --
1836 if p_org_id is null then
1837 open get_org_id;
1838 fetch get_org_id into l_org_id;
1839 close get_org_id;
1840 else
1841 l_org_id := p_org_id;
1842 end if;
1843 for i in 1..g_org_id_list.count
1844 loop
1845 if l_org_id = g_org_id_list(i) then
1846 if p_excld_flag = 'N' then
1847 raise g_include;
1848 else
1849 raise g_not_include;
1850 end if;
1851 end if;
1852 end loop;
1853 --
1854 if p_excld_flag = 'Y' then
1855 raise g_include;
1856 end if;
1857 --
1858 if g_debug then
1859 hr_utility.set_location(' Exiting:'||l_proc, 15);
1860 end if;
1861 --
1862 --
1863 raise g_not_include;
1864 --
1865 Exception
1866 --
1867 when g_include then
1868 null;
1869 --
1870 when g_not_include then
1871 raise g_not_include; --- will be handled in the calling program
1872 --
1873 End Chk_Org_Id_Incl;
1874 --
1875 -----------------------------------------------------------------------------
1876 -----------------------------< chk_loc_id_incl >-----------------------------
1877 -----------------------------------------------------------------------------
1878 --
1879 Procedure chk_loc_id_incl
1880 (p_person_id in per_all_people_f.person_id%type,
1881 p_loc_id in per_all_assignments_f.location_id%type,
1882 p_effective_date in date,
1883 p_excld_flag in varchar2)
1884 is
1885 --
1886 cursor get_loc_id is
1887 SELECT location_id
1888 FROM per_all_assignments_f asn
1889 WHERE asn.person_id = p_person_id
1890 and asn.assignment_id = ben_ext_person.g_assignment_id --1969853
1891 AND asn.primary_flag = 'Y'
1892 AND p_effective_date between effective_start_date
1893 and effective_end_date;
1894 --
1895 l_loc_id per_all_assignments_f.location_id%type;
1896 --
1897 l_proc varchar2(72);
1898 --
1899 Begin
1900 --
1901 if g_debug then
1902 l_proc := g_package||'chk_loc_id_incl';
1903 hr_utility.set_location(' Entering:'||l_proc, 5);
1904 end if;
1905 --
1906 if p_loc_id is null then
1907 open get_loc_id;
1908 fetch get_loc_id into l_loc_id;
1909 close get_loc_id;
1910 else
1911 l_loc_id := p_loc_id;
1912 end if;
1913 for i in 1..g_loc_id_list.count
1914 loop
1915 if l_loc_id = g_loc_id_list(i) then
1916 if p_excld_flag = 'N' then
1917 raise g_include;
1918 else
1919 raise g_not_include;
1920 end if;
1921 end if;
1922 end loop;
1923 --
1924 if p_excld_flag = 'Y' then
1925 raise g_include;
1926 end if;
1927 --
1928 if g_debug then
1929 hr_utility.set_location(' Exiting:'||l_proc, 15);
1930 end if;
1931 --
1932 raise g_not_include;
1933 --
1934 Exception
1935 --
1936 when g_include then
1937 null;
1938 --
1939 when g_not_include then
1940 raise g_not_include; --- will be handled in the calling program
1941 --
1942 End Chk_Loc_Id_Incl;
1943 --
1944 -----------------------------------------------------------------------------
1945 --------------------------------< chk_gre_incl >-----------------------------
1946 -----------------------------------------------------------------------------
1947 --
1948 Procedure chk_gre_incl
1949 (p_person_id in per_all_people_f.person_id%type,
1950 p_gre in hr_soft_coding_keyflex.segment1%type,
1951 p_effective_date in date,
1952 p_excld_flag in varchar2)
1953 is
1954 --
1955 cursor get_gre is
1956 SELECT flex.segment1
1957 FROM per_all_assignments_f asn, hr_soft_coding_keyflex flex
1958 WHERE asn.soft_coding_keyflex_id = flex.soft_coding_keyflex_id
1959 and asn.assignment_id = ben_ext_person.g_assignment_id --1969853
1960 AND asn.person_id = p_person_id
1961 AND asn.primary_flag = 'Y'
1962 AND p_effective_date between asn.effective_start_date
1963 and asn.effective_end_date
1964 AND p_effective_date between nvl(flex.start_date_active, p_effective_date)
1965 and nvl(flex.end_date_active, p_effective_date);
1966 --
1967 l_gre hr_soft_coding_keyflex.segment1%type;
1968 --
1969 l_proc varchar2(72);
1970 --
1971 Begin
1972 --
1973 if g_debug then
1974 l_proc := g_package||'chk_gre_incl';
1975 hr_utility.set_location(' Entering:'||l_proc, 5);
1976 end if;
1977 --
1978 if p_gre is null then
1979 open get_gre;
1980 fetch get_gre into l_gre;
1981 close get_gre;
1982 else
1983 l_gre := p_gre;
1984 end if;
1985 for i in 1..g_gre_list.count
1986 loop
1987 if l_gre = g_gre_list(i) then
1988 if p_excld_flag = 'N' then
1989 raise g_include;
1990 else
1991 raise g_not_include;
1992 end if;
1993 end if;
1994 end loop;
1995 --
1996 if p_excld_flag = 'Y' then
1997 raise g_include;
1998 end if;
1999 --
2000 if g_debug then
2001 hr_utility.set_location(' Exiting:'||l_proc, 15);
2002 end if;
2003 --
2004 raise g_not_include;
2005 --
2006 Exception
2007 --
2008 when g_include then
2009 null;
2010 --
2011 when g_not_include then
2012 raise g_not_include; --- will be handled in the calling program
2013 --
2014 End Chk_Gre_Incl;
2015 --
2016 -----------------------------------------------------------------------------
2017 ------------------------------< chk_state_incl >-----------------------------
2018 -----------------------------------------------------------------------------
2019 --
2020 Procedure chk_state_incl
2021 (p_person_id in per_all_people_f.person_id%type,
2022 p_state in per_addresses.region_2%type,
2023 p_effective_date in date,
2024 p_excld_flag in varchar2)
2025 is
2026 --
2027 cursor get_state is
2028 SELECT addr.region_2
2029 FROM per_all_people_f per, per_addresses addr
2030 WHERE per.person_id = addr.person_id
2031 AND per.person_id = p_person_id
2032 AND addr.primary_flag = 'Y'
2033 AND p_effective_date between per.effective_start_date
2034 and per.effective_end_date
2035 AND p_effective_date between addr.date_from
2036 and nvl(addr.date_to, p_effective_date);
2037 --
2038 l_state per_addresses.region_2%type;
2039 --
2040 l_proc varchar2(72);
2041 --
2042 Begin
2043 --
2044 if g_debug then
2045 l_proc := g_package||'chk_state_incl';
2046 hr_utility.set_location(' Entering:'||l_proc, 5);
2047 end if;
2048 --
2049 if p_state is null then
2050 open get_state;
2051 fetch get_state into l_state;
2052 close get_state;
2053 else
2054 l_state := p_state;
2055 end if;
2056 for i in 1..g_state_list.count
2057 loop
2058 if l_state = g_state_list(i) then
2059 if p_excld_flag = 'N' then
2060 raise g_include;
2061 else
2062 raise g_not_include;
2063 end if;
2064 end if;
2065 end loop;
2066 --
2067 if p_excld_flag = 'Y' then
2068 raise g_include;
2069 end if;
2070 --
2071 if g_debug then
2072 hr_utility.set_location(' Exiting:'||l_proc, 15);
2073 end if;
2074 --
2075 raise g_not_include;
2076 --
2077 Exception
2078 --
2079 when g_include then
2080 null;
2081 --
2082 when g_not_include then
2083 raise g_not_include; --- will be handled in the calling program
2084 --
2085 End Chk_State_Incl;
2086 --
2087 -----------------------------------------------------------------------------
2088 ------------------------------< chk_bnft_grp_incl >--------------------------
2089 -----------------------------------------------------------------------------
2090 --
2091 Procedure chk_bnft_grp_incl
2092 (p_person_id in per_all_people_f.person_id%type,
2093 p_bnft_grp in per_all_people_f.benefit_group_id%type,
2094 p_effective_date in date,
2095 p_excld_flag in varchar2)
2096 is
2097 --
2098 cursor get_bnft_grp is
2099 SELECT benefit_group_id
2100 FROM per_all_people_f per
2101 WHERE per.person_id = p_person_id
2102 AND p_effective_date between per.effective_start_date
2103 and per.effective_end_date;
2104 --
2105 l_bnft_grp per_all_people_f.benefit_group_id%type;
2106 --
2107 l_proc varchar2(72);
2108 --
2109 Begin
2110 --
2111 if g_debug then
2112 l_proc := g_package||'chk_bnft_grp_incl';
2113 hr_utility.set_location(' Entering:'||l_proc, 5);
2114 end if;
2115 --
2116 if p_bnft_grp is null then
2117 open get_bnft_grp;
2118 fetch get_bnft_grp into l_bnft_grp;
2119 close get_bnft_grp;
2120 else
2121 l_bnft_grp := p_bnft_grp;
2122 end if;
2123 for i in 1..g_bnft_grp_list.count
2124 loop
2125 if l_bnft_grp = g_bnft_grp_list(i) then
2126 if p_excld_flag = 'N' then
2127 raise g_include;
2128 else
2129 raise g_not_include;
2130 end if;
2131 end if;
2132 end loop;
2133 --
2134 if p_excld_flag = 'Y' then
2135 raise g_include;
2136 end if;
2137 --
2138 if g_debug then
2139 hr_utility.set_location(' Exiting:'||l_proc, 15);
2140 end if;
2141 --
2142 raise g_not_include;
2143 --
2144 Exception
2145 --
2146 when g_include then
2147 null;
2148 --
2149 when g_not_include then
2150 raise g_not_include; --- will be handled in the calling program
2151 --
2152 End Chk_Bnft_Grp_Incl;
2153 --
2154 -----------------------------------------------------------------------------
2155 ------------------------------< chk_ee_status_incl >-------------------------
2156 -----------------------------------------------------------------------------
2157 --
2158 Procedure chk_ee_status_incl
2159 (p_person_id in per_all_people_f.person_id%type,
2160 p_ee_status in per_all_assignments_f.assignment_status_type_id%type,
2161 p_effective_date in date,
2162 p_excld_flag in varchar2)
2163 is
2164 --
2165 cursor get_ee_status is
2166 SELECT assignment_status_type_id
2167 FROM per_all_assignments_f asn
2168 WHERE asn.person_id = p_person_id
2169 and asn.assignment_id = ben_ext_person.g_assignment_id --1969853
2170 AND asn.primary_flag = 'Y'
2171 AND p_effective_date between effective_start_date and effective_end_date;
2172 --
2173 l_ee_status per_all_assignments_f.assignment_status_type_id%type;
2174 --
2175 l_proc varchar2(72);
2176 --
2177 Begin
2178 --
2179 if g_debug then
2180 l_proc := g_package||'chk_ee_status_incl';
2181 hr_utility.set_location(' Entering:'||l_proc, 5);
2182 end if;
2183 --
2184 if p_ee_status is null then
2185 open get_ee_status;
2186 fetch get_ee_status into l_ee_status;
2187 close get_ee_status;
2188 else
2189 l_ee_status := p_ee_status;
2190 end if;
2191 for i in 1..g_ee_status_list.count
2192 loop
2193 if l_ee_status = g_ee_status_list(i) then
2194 if p_excld_flag = 'N' then
2195 raise g_include;
2196 else
2197 raise g_not_include;
2198 end if;
2199 end if;
2200 end loop;
2201 --
2202 if p_excld_flag = 'Y' then
2203 raise g_include;
2204 end if;
2205 --
2206 if g_debug then
2207 hr_utility.set_location(' Exiting:'||l_proc, 15);
2208 end if;
2209 --
2210 raise g_not_include;
2211 --
2212 Exception
2213 --
2214 when g_include then
2215 null;
2216 --
2217 when g_not_include then
2218 raise g_not_include; --- will be handled in the calling program
2219 --
2220 End Chk_Ee_Status_Incl;
2221 --
2222 -----------------------------------------------------------------------------
2223 ------------------------------< chk_payroll_id_incl >-------------------------
2224 -----------------------------------------------------------------------------
2225 --
2226 Procedure chk_payroll_id_incl
2227 (p_person_id in per_all_people_f.person_id%type,
2228 p_payroll_id in per_all_assignments_f.payroll_id%type,
2229 p_effective_date in date,
2230 p_excld_flag in varchar2)
2231 is
2232 --
2233 cursor c_pay is
2234 SELECT payroll_id
2235 FROM per_all_assignments_f asn
2236 WHERE asn.person_id = p_person_id
2237 and asn.assignment_id = ben_ext_person.g_assignment_id --1969853
2238 AND asn.primary_flag = 'Y'
2239 AND p_effective_date between effective_start_date and effective_end_date;
2240 --
2241 l_payroll_id per_all_assignments_f.payroll_id%type;
2242 --
2243 l_proc varchar2(72);
2244 --
2245 Begin
2246 --
2247 if g_debug then
2248 l_proc := g_package||'chk_payroll_id_incl';
2249 hr_utility.set_location(' Entering:'||l_proc, 5);
2250 end if;
2251 --
2252 if p_payroll_id is null then
2253 open c_pay;
2254 fetch c_pay into l_payroll_id;
2255 close c_pay;
2256 else
2257 l_payroll_id := p_payroll_id;
2258 end if;
2259 for i in 1..g_payroll_id_list.count
2260 loop
2261 if l_payroll_id = g_payroll_id_list(i) then
2262 if p_excld_flag = 'N' then
2263 raise g_include;
2264 else
2265 raise g_not_include;
2266 end if;
2267 end if;
2268 end loop;
2269 --
2270 if p_excld_flag = 'Y' then
2271 raise g_include;
2272 end if;
2273 --
2274 if g_debug then
2275 hr_utility.set_location(' Exiting:'||l_proc, 15);
2276 end if;
2277 --
2278 raise g_not_include;
2279 --
2280 Exception
2281 --
2282 when g_include then
2283 null;
2284 --
2285 when g_not_include then
2286 raise g_not_include; --- will be handled in the calling program
2287 --
2288 End Chk_payroll_id_Incl;
2289 --
2290 -----------------------------------------------------------------------------
2291 ------------------------------< chk_person_rule_incl >-----------------------
2292 -----------------------------------------------------------------------------
2293 --
2294 Procedure chk_person_rule_incl
2295 (p_person_id in per_all_people_f.person_id%type,
2296 p_effective_date in date,
2297 p_excld_flag in varchar2)
2298 is
2299 --
2300 cursor c_asg is
2301 SELECT asg.assignment_id, asg.business_group_id
2302 FROM per_all_assignments_f asg
2303 WHERE asg.person_id = p_person_id
2304 and asg.assignment_id = ben_ext_person.g_assignment_id --1969853
2305 AND (asg.primary_flag = 'Y' or asg.assignment_type = 'A' ) -- if the asg type is A dont validate the primary flag
2306 AND p_effective_date between asg.effective_start_date
2307 and asg.effective_end_date
2308 order by decode(asg.primary_flag , 'Y', 1, 2) ;
2309 --
2310 l_asg c_asg%rowtype;
2311 l_outputs ff_exec.outputs_t;
2312 --
2313 l_proc varchar2(72);
2314 --
2315 Begin
2316 --
2317 if g_debug then
2318 l_proc := g_package||'chk_person_rule_incl';
2319 hr_utility.set_location(' Entering:'||l_proc, 5);
2320 end if;
2321 --
2322 open c_asg;
2323 fetch c_asg into l_asg;
2324 close c_asg;
2325 --
2326 if l_asg.assignment_id is not null then
2327 for i in 1..g_person_rule_list.count
2328 loop
2329 l_outputs := benutils.formula
2330 (p_formula_id => g_person_rule_list(i),
2331 p_effective_date => p_effective_date,
2332 p_assignment_id => l_asg.assignment_id,
2333 p_business_group_id => l_asg.business_group_id
2334 --RChase pass extract definition id as input value
2335 ,p_param1 => 'EXT_DFN_ID'
2336 ,p_param1_value => to_char(nvl(ben_ext_thread.g_ext_dfn_id, -1))
2337 ,p_param2 => 'EXT_RSLT_ID'
2338 ,p_param2_value => to_char(nvl(ben_ext_thread.g_ext_rslt_id, -1))
2339 ,p_param3 => 'EXT_PROCESS_BUSINESS_GROUP'
2340 ,p_param3_value => to_char(ben_extract.g_proc_business_group_id)
2341 );
2342 if l_outputs(l_outputs.first).value = 'Y' then
2343 if p_excld_flag = 'N' then
2344 raise g_include;
2345 else
2346 raise g_not_include;
2347 end if;
2348 end if;
2349 end loop;
2350 end if;
2351 --
2352 if p_excld_flag = 'Y' then
2353 raise g_include;
2354 end if;
2355 --
2356 if g_debug then
2357 hr_utility.set_location(' Exiting:'||l_proc, 15);
2358 end if;
2359 --
2360 raise g_not_include;
2361 --
2362 Exception
2363 --
2364 when g_include then
2365 null;
2366 --
2367 when g_not_include then
2368 raise g_not_include; --- will be handled in the calling program
2369 --
2370 End Chk_Person_Rule_Incl;
2371 --
2372 -----------------------------------------------------------------------------
2373 ------------------------------< chk_per_ler_incl >---------------------------
2374 -----------------------------------------------------------------------------
2375 --
2376 Procedure chk_per_ler_incl
2377 (p_person_id in per_all_people_f.person_id%type,
2378 p_effective_date in date,
2379 p_excld_flag in varchar2)
2380 is
2381 --
2382 cursor get_per_ler is
2383 SELECT ler_id
2384 FROM ben_per_in_ler pil
2385 WHERE pil.person_id = p_person_id
2386 AND pil.per_in_ler_stat_cd = 'STRTD';
2387 --
2388 l_ler_id ben_per_in_ler.ler_id%type;
2389 --
2390 l_proc varchar2(72);
2391 --
2392 Begin
2393 --
2394 if g_debug then
2395 l_proc := g_package||'chk_per_ler_incl';
2396 hr_utility.set_location(' Entering:'||l_proc, 5);
2397 end if;
2398 --
2399 open get_per_ler;
2400 loop
2401 fetch get_per_ler into l_ler_id;
2402 exit when get_per_ler%notfound;
2403 for i in 1..g_per_ler_list.count
2404 loop
2405 if l_ler_id = g_per_ler_list(i) then
2406 if p_excld_flag = 'N' then
2407 raise g_include;
2408 else
2409 raise g_not_include;
2410 end if;
2411 end if;
2412 end loop;
2413 end loop;
2414 close get_per_ler;
2415 --
2416 if p_excld_flag = 'Y' then
2417 raise g_include;
2418 end if;
2419 --
2420 if g_debug then
2421 hr_utility.set_location(' Exiting:'||l_proc, 15);
2422 end if;
2423 --
2424 raise g_not_include;
2425 --
2426 Exception
2427 --
2428 when g_include then
2429 null;
2430 --
2431 when g_not_include then
2432 raise g_not_include; --- will be handled in the calling program
2433 --
2434 End Chk_Per_Ler_Incl;
2435 --
2436 -----------------------------------------------------------------------------
2437 -------------------------< chk_person_type_incl >-----------------------
2438 -----------------------------------------------------------------------------
2439 --
2440 Procedure chk_person_type_incl
2441 (p_person_id in per_all_people_f.person_id%type,
2442 p_effective_date in date,
2443 p_excld_flag in varchar2)
2444 is
2445 --
2446 cursor get_person_type is
2447 SELECT person_type_id
2448 FROM per_person_type_usages_f ptu
2449 WHERE ptu.person_id = p_person_id
2450 AND p_effective_date between ptu.effective_start_date and
2451 ptu.effective_end_date;
2452 --
2453 l_person_type_id per_person_type_usages_f.person_type_id%type;
2454 --
2455 l_proc varchar2(72);
2456 --
2457 Begin
2458 --
2459 if g_debug then
2460 l_proc := g_package||'chk_person_type_incl';
2461 hr_utility.set_location(' Entering:'||l_proc, 5);
2462 end if;
2463 --
2464 open get_person_type;
2465 loop
2466 fetch get_person_type into l_person_type_id;
2467 exit when get_person_type%notfound;
2468 for i in 1..g_person_type_list.count
2469 loop
2470 if l_person_type_id = g_person_type_list(i) then
2471 if p_excld_flag = 'N' then
2472 raise g_include;
2473 else
2474 raise g_not_include;
2475 end if;
2476 end if;
2477 end loop;
2478 end loop;
2479 close get_person_type;
2480 --
2481 if g_debug then
2482 hr_utility.set_location(' Exiting:'||l_proc, 15);
2483 end if;
2484 --
2485 if p_excld_flag = 'Y' then
2486 raise g_include;
2487 end if;
2488 --
2489 raise g_not_include;
2490 --
2491 Exception
2492 --
2493 when g_include then
2494 null;
2495 --
2496 when g_not_include then
2497 raise g_not_include; --- will be handled in the calling program
2498 --
2499 End Chk_Person_Type_Incl;
2500 --
2501 -----------------------------------------------------------------------------
2502 ---------------------------------< chk_cmbn_incl >---------------------------
2503 -----------------------------------------------------------------------------
2504 --
2505 --The following procedure checks combination criteria inclusion
2506 --
2507 Procedure chk_cmbn_incl
2508 (p_person_id per_all_people_f.person_id%type,
2509 p_chg_evt_cd varchar2,
2510 p_chg_evt_source varchar2,
2511 p_effective_date date,
2512 p_actl_date date)
2513 is
2514 --
2515 l_success varchar2(1);
2516 l_current_success varchar2(1);
2517 l_current_crit_val number := 0;
2518 --
2519 l_proc varchar2(72);
2520 --
2521 Begin
2522 --
2523 if g_debug then
2524 l_proc := g_package||'chk_cmbn_incl';
2525 hr_utility.set_location(' Entering:'||l_proc, 5);
2526 end if;
2527 --
2528 l_success := 'N';
2529 l_current_success := 'N';
2530
2531 for i in 1..g_crit_val_list.count
2532 loop
2533 if g_crit_val_list(i) <> l_current_crit_val then
2534 l_current_crit_val := g_crit_val_list(i);
2535 if l_current_success = 'Y' then
2536 raise g_include;
2537 else
2538 result(p_chg_evt_cd,
2539 p_chg_evt_source,
2540 p_effective_date,
2541 p_actl_date,
2542 g_crit_typ_list(i),
2543 g_oper_list(i),
2544 g_val1_list(i),
2545 g_val2_list(i),
2546 l_success);
2547 if l_success = 'Y' then
2548 l_current_success := 'Y';
2549 else
2550 l_current_success := 'N';
2551 end if;
2552 end if;
2553 else
2554 result(p_chg_evt_cd,
2555 p_chg_evt_source,
2556 p_effective_date,
2557 p_actl_date,
2558 g_crit_typ_list(i),
2559 g_oper_list(i),
2560 g_val1_list(i),
2561 g_val2_list(i),
2562 l_success);
2563 if l_current_success = 'Y' and l_success = 'Y' then
2564 l_current_success := 'Y';
2565 else
2566 l_current_success := 'N';
2567 end if;
2568
2569 end if;
2570 end loop;
2571 --
2572 if g_debug then
2573 hr_utility.set_location(' Exiting:'||l_proc, 15);
2574 end if;
2575 --
2576 if l_current_success = 'Y' then
2577 raise g_include;
2578 else
2579 raise g_not_include;
2580 end if;
2581 --
2582 Exception
2583 --
2584 when g_include then
2585 null;
2586 --
2587 when g_not_include then
2588 raise g_not_include; --- will be handled in the calling program
2589 --
2590 End chk_cmbn_incl;
2591
2592
2593 -----------------------------------------------------------------------------
2594 ---------------------------< chk_enrt_opt_incl >----------------------------
2595 -----------------------------------------------------------------------------
2596 -- The following procedure evaluates plan id.
2597 --
2598 Procedure chk_enrt_opt_incl
2599 (p_opt_id in ben_opt_f.opt_id%type,
2600 p_excld_flag in varchar2)
2601 is
2602 --
2603 l_proc varchar2(72);
2604 --
2605 Begin
2606 --
2607 if g_debug then
2608 l_proc := g_package||'chk_enrt_opt_incl';
2609 hr_utility.set_location(' Entering:'||l_proc, 5);
2610 end if;
2611 --
2612 -- if p_opt_id is null then error, it must be passed in!
2613 --
2614 for i in 1..g_enrt_opt_list.count
2615 loop
2616 if p_opt_id = g_enrt_opt_list(i) then
2617 if p_excld_flag = 'N' then
2618 raise g_include;
2619 else
2620 raise g_not_include;
2621 end if;
2622 end if;
2623 end loop;
2624 --
2625 if p_excld_flag = 'Y' then
2626 raise g_include;
2627 end if;
2628 --
2629 if g_debug then
2630 hr_utility.set_location(' Exiting:'||l_proc, 15);
2631 end if;
2632 --
2633 raise g_not_include;
2634 --
2635 Exception
2636 --
2637 when g_include then
2638 null;
2639 --
2640 when g_not_include then
2641 raise g_not_include; --- will be handled in the calling program
2642 --
2643 End chk_enrt_opt_incl;
2644
2645 --
2646 -----------------------------------------------------------------------------
2647 ---------------------------< chk_enrt_plan_incl >----------------------------
2648 -----------------------------------------------------------------------------
2649 -- The following procedure evaluates plan id.
2650 --
2651 Procedure chk_enrt_plan_incl
2652 (p_pl_id in ben_prtt_enrt_rslt_f.pl_id%type,
2653 p_excld_flag in varchar2)
2654 is
2655 --
2656 l_proc varchar2(72);
2657 --
2658 Begin
2659 --
2660 if g_debug then
2661 l_proc := g_package||'chk_enrt_plan_incl';
2662 hr_utility.set_location(' Entering:'||l_proc, 5);
2663 end if;
2664 --
2665 -- if p_pl_id is null then error, it must be passed in!
2666 --
2667 for i in 1..g_enrt_plan_list.count
2668 loop
2669 if p_pl_id = g_enrt_plan_list(i) then
2670 if p_excld_flag = 'N' then
2671 raise g_include;
2672 else
2673 raise g_not_include;
2674 end if;
2675 end if;
2676 end loop;
2677 --
2678 if p_excld_flag = 'Y' then
2679 raise g_include;
2680 end if;
2681 --
2682 if g_debug then
2683 hr_utility.set_location(' Exiting:'||l_proc, 15);
2684 end if;
2685 --
2686 raise g_not_include;
2687 --
2688 Exception
2689 --
2690 when g_include then
2691 null;
2692 --
2693 when g_not_include then
2694 raise g_not_include; --- will be handled in the calling program
2695 --
2696 End chk_enrt_plan_incl;
2697 --
2698 -----------------------------------------------------------------------------
2699 ---------------------------< chk_enrt_rg_plan_incl >----------------------------
2700 -----------------------------------------------------------------------------
2701 -- The following procedure evaluates plans of a particular reporting group id.
2702 --
2703 Procedure chk_enrt_rg_plan_incl
2704 (p_pl_id in ben_prtt_enrt_rslt_f.pl_id%type,
2705 p_excld_flag in varchar2)
2706 is
2707 --
2708 l_proc varchar2(72);
2709 --
2710 Begin
2711 --
2712 if g_debug then
2713 l_proc := g_package||'chk_enrt_rg_plan_incl';
2714 hr_utility.set_location(' Entering:'||l_proc, 5);
2715 end if;
2716 --
2717 -- if p_pl_id is null then error, it must be passed in!
2718 --
2719 for i in 1..g_enrt_rg_plan_list.count
2720 loop
2721 if p_pl_id = g_enrt_rg_plan_list(i) then
2722 if p_excld_flag = 'N' then
2723 raise g_include;
2724 else
2725 raise g_not_include;
2726 end if;
2727 end if;
2728 end loop;
2729 --
2730 if p_excld_flag = 'Y' then
2731 raise g_include;
2732 end if;
2733 --
2734 if g_debug then
2735 hr_utility.set_location(' Exiting:'||l_proc, 15);
2736 end if;
2737 --
2738 raise g_not_include;
2739 --
2740 Exception
2741 --
2742 when g_include then
2743 null;
2744 --
2745 when g_not_include then
2746 raise g_not_include; --- will be handled in the calling program
2747 --
2748 End chk_enrt_rg_plan_incl;
2749 --
2750 -----------------------------------------------------------------------------
2751 ---------------------------< chk_enrt_sspndd_incl >---------------------------
2752 -----------------------------------------------------------------------------
2753 -- The following procedure evaluates plans of a particular reporting group id.
2754 --
2755 Procedure chk_enrt_sspndd_incl
2756 (p_sspndd_flag in ben_prtt_enrt_rslt_f.sspndd_flag%type,
2757 p_excld_flag in varchar2)
2758 is
2759 --
2760 l_proc varchar2(72);
2761 --
2762 Begin
2763 --
2764 if g_debug then
2765 l_proc := g_package||'chk_enrt_sspndd_incl';
2766 hr_utility.set_location(' Entering:'||l_proc, 5);
2767 end if;
2768 --
2769 if p_sspndd_flag is null then -- sspndd_flag will always be null for eligiblity.
2770 raise g_include;
2771 end if;
2772 --
2773 for i in 1..g_enrt_sspndd_list.count
2774 loop
2775 if p_sspndd_flag = g_enrt_sspndd_list(i) then
2776 if p_excld_flag = 'N' then
2777 raise g_include;
2778 else
2779 raise g_not_include;
2780 end if;
2781 end if;
2782 end loop;
2783 --
2784 if p_excld_flag = 'Y' then
2785 raise g_include;
2786 end if;
2787 --
2788 if g_debug then
2789 hr_utility.set_location(' Exiting:'||l_proc, 15);
2790 end if;
2791 --
2792 raise g_not_include;
2793 --
2794 Exception
2795 --
2796 when g_include then
2797 null;
2798 --
2799 when g_not_include then
2800 raise g_not_include; --- will be handled in the calling program
2801 --
2802 End chk_enrt_sspndd_incl;
2803 --
2804 -----------------------------------------------------------------------------
2805 ---------------------------< chk_enrt_cvg_strt_dt_incl >----------------------
2806 -----------------------------------------------------------------------------
2807 -- The following procedure evaluates enrt_cvg_strt_dt id.
2808 --
2809 Procedure chk_enrt_cvg_strt_dt_incl
2810 (p_enrt_cvg_strt_dt in ben_prtt_enrt_rslt_f.enrt_cvg_strt_dt%type,
2811 p_effective_date in date,
2812 p_excld_flag in varchar2,
2813 p_pl_id in number default null )
2814 is
2815 --
2816 l_proc varchar2(72);
2817 l_low_date date;
2818 l_high_date date;
2819 --
2820 Begin
2821 --
2822 if g_debug then
2823 l_proc := g_package||'chk_enrt_cvg_strt_dt_incl';
2824 hr_utility.set_location(' Entering:'||l_proc, 5);
2825 end if;
2826 --
2827 for i in 1..g_enrt_cvg_strt_dt_list1.count
2828 loop
2829 l_low_date := ben_ext_util.calc_ext_date
2830 (p_ext_date_cd => g_enrt_cvg_strt_dt_list1(i),
2831 p_abs_date => p_effective_date,
2832 p_ext_dfn_id => ben_extract.g_ext_dfn_id,
2833 p_pl_id => p_pl_id);
2834 l_high_date := ben_ext_util.calc_ext_date
2835 (p_ext_date_cd => g_enrt_cvg_strt_dt_list2(i),
2836 p_abs_date => p_effective_date,
2837 p_ext_dfn_id => ben_extract.g_ext_dfn_id,
2838 p_pl_id => p_pl_id
2839 );
2840 if p_enrt_cvg_strt_dt between l_low_date and nvl(l_high_date,l_low_date) then
2841 if p_excld_flag = 'N' then
2842 raise g_include;
2843 else
2844 raise g_not_include;
2845 end if;
2846 end if;
2847 end loop;
2848 --
2849 if p_excld_flag = 'Y' then
2850 raise g_include;
2851 end if;
2852 --
2853 if g_debug then
2854 hr_utility.set_location(' Exiting:'||l_proc, 15);
2855 end if;
2856 --
2857 raise g_not_include;
2858 --
2859 Exception
2860 --
2861 when g_include then
2862 null;
2863 --
2864 when g_not_include then
2865 raise g_not_include; --- will be handled in the calling program
2866 --
2867 End chk_enrt_cvg_strt_dt_incl;
2868 --
2869 -----------------------------------------------------------------------------
2870 ---------------------------< chk_enrt_cvg_drng_perd_incl >-------------------
2871 -----------------------------------------------------------------------------
2872 -- The following procedure evaluates if a person was covered any time during
2873 -- a period.
2874 --
2875 Procedure chk_enrt_cvg_drng_perd_incl
2876 (p_enrt_cvg_strt_dt in ben_prtt_enrt_rslt_f.enrt_cvg_strt_dt%type,
2877 p_enrt_cvg_thru_dt in ben_prtt_enrt_rslt_f.enrt_cvg_thru_dt%type,
2878 p_effective_date in date,
2879 p_excld_flag in varchar2,
2880 p_pl_id in number default null )
2881 is
2882 --
2883 l_proc varchar2(72);
2884 l_low_date date;
2885 l_high_date date;
2886 --
2887 Begin
2888 --
2889 if g_debug then
2890 l_proc := g_package||'chk_enrt_cvg_drng_perd_incl';
2891 hr_utility.set_location(' Entering:'||l_proc, 5);
2892 end if;
2893 --
2894 for i in 1..g_enrt_cvg_drng_perd_list1.count
2895 loop
2896 l_low_date := ben_ext_util.calc_ext_date
2897 (p_ext_date_cd => g_enrt_cvg_drng_perd_list1(i),
2898 p_abs_date => p_effective_date,
2899 p_ext_dfn_id => ben_extract.g_ext_dfn_id,
2900 p_pl_id => p_pl_id );
2901 l_high_date := ben_ext_util.calc_ext_date
2902 (p_ext_date_cd => g_enrt_cvg_drng_perd_list2(i),
2903 p_abs_date => p_effective_date,
2904 p_ext_dfn_id => ben_extract.g_ext_dfn_id,
2905 p_pl_id => p_pl_id );
2906 l_high_date := nvl(l_high_date,l_low_date);
2907 -- out of range if both dates are less than low date or greater than high date.
2908 if (p_enrt_cvg_strt_dt < l_low_date and p_enrt_cvg_thru_dt < l_low_date) or
2909 (p_enrt_cvg_strt_dt > l_high_date and p_enrt_cvg_thru_dt > l_high_date) or
2910 ( p_enrt_cvg_strt_dt > p_enrt_cvg_thru_dt ) then
2911 null;
2912 else
2913 if p_excld_flag = 'N' then
2914 raise g_include;
2915 else
2916 raise g_not_include;
2917 end if;
2918 end if;
2919 end loop;
2920 --
2921 if p_excld_flag = 'Y' then
2922 raise g_include;
2923 end if;
2924 --
2925 if g_debug then
2926 hr_utility.set_location(' Exiting:'||l_proc, 15);
2927 end if;
2928 --
2929 raise g_not_include;
2930 --
2931 Exception
2932 --
2933 when g_include then
2934 null;
2935 --
2936 when g_not_include then
2937 raise g_not_include; --- will be handled in the calling program
2938 --
2939 End chk_enrt_cvg_drng_perd_incl;
2940 --
2941 -----------------------------------------------------------------------------
2942 ---------------------------< chk_enrt_stat_incl >---------------------------
2943 -----------------------------------------------------------------------------
2944 -- The following procedure evaluates enrollment status code.
2945 --
2946 Procedure chk_enrt_stat_incl
2947 (p_prtt_enrt_rslt_stat_cd in ben_prtt_enrt_rslt_f.prtt_enrt_rslt_stat_cd%type,
2948 p_excld_flag in varchar2)
2949 is
2950 --
2951 l_proc varchar2(72);
2952 --
2953 Begin
2954 --
2955 if g_debug then
2956 l_proc := g_package||'chk_enrt_stat_incl';
2957 hr_utility.set_location(' Entering:'||l_proc, 5);
2958 end if;
2959 --
2960 if p_prtt_enrt_rslt_stat_cd is null then
2961 raise g_include;
2962 end if;
2963 --
2964 for i in 1..g_enrt_stat_list.count
2965 loop
2966 if p_prtt_enrt_rslt_stat_cd = g_enrt_stat_list(i) then
2967 if p_excld_flag = 'N' then
2968 raise g_include;
2969 else
2970 raise g_not_include;
2971 end if;
2972 end if;
2973 end loop;
2974 --
2975 if p_excld_flag = 'Y' then
2976 raise g_include;
2977 end if;
2978 --
2979 if g_debug then
2980 hr_utility.set_location(' Exiting:'||l_proc, 15);
2981 end if;
2982 --
2983 raise g_not_include;
2984 --
2985 Exception
2986 --
2987 when g_include then
2988 null;
2989 --
2990 when g_not_include then
2991 raise g_not_include; --- will be handled in the calling program
2992 --
2993 End chk_enrt_stat_incl;
2994 --
2995 -----------------------------------------------------------------------------
2996 ---------------------------< chk_enrt_mthd_incl >---------------------------
2997 -----------------------------------------------------------------------------
2998 -- The following procedure evaluates enrollment method
2999 --
3000 Procedure chk_enrt_mthd_incl
3001 (p_enrt_mthd_cd in ben_prtt_enrt_rslt_f.enrt_mthd_cd%type,
3002 p_excld_flag in varchar2)
3003 is
3004 --
3005 l_proc varchar2(72);
3006 --
3007 Begin
3008 --
3009 if g_debug then
3010 l_proc := g_package||'chk_enrt_mthd_incl';
3011 hr_utility.set_location(' Entering:'||l_proc, 5);
3012 end if;
3013 --
3014 if p_enrt_mthd_cd is null then
3015 raise g_include;
3016 end if;
3017 --
3018 for i in 1..g_enrt_mthd_list.count
3019 loop
3020 if p_enrt_mthd_cd = g_enrt_mthd_list(i) then
3021 if p_excld_flag = 'N' then
3022 raise g_include;
3023 else
3024 raise g_not_include;
3025 end if;
3026 end if;
3027 end loop;
3028 --
3029 if p_excld_flag = 'Y' then
3030 raise g_include;
3031 end if;
3032 --
3033 if g_debug then
3034 hr_utility.set_location(' Exiting:'||l_proc, 15);
3035 end if;
3036 --
3037 raise g_not_include;
3038 --
3039 Exception
3040 --
3041 when g_include then
3042 null;
3043 --
3044 when g_not_include then
3045 raise g_not_include; --- will be handled in the calling program
3046 --
3047 End chk_enrt_mthd_incl;
3048 --
3049 -----------------------------------------------------------------------------
3050 ---------------------------< chk_enrt_pgm_incl >----------------------------
3051 -----------------------------------------------------------------------------
3052 -- The following procedure evaluates pgm id.
3053 --
3054 Procedure chk_enrt_pgm_incl
3055 (p_pgm_id in ben_prtt_enrt_rslt_f.pgm_id%type,
3056 p_excld_flag in varchar2)
3057 is
3058 --
3059 l_proc varchar2(72);
3060 --
3061 Begin
3062 --
3063 if g_debug then
3064 l_proc := g_package||'chk_enrt_pgm_incl';
3065 hr_utility.set_location(' Entering:'||l_proc, 5);
3066 end if;
3067 --
3068 for i in 1..g_enrt_pgm_list.count
3069 loop
3070 if p_pgm_id = g_enrt_pgm_list(i) then
3071 if p_excld_flag = 'N' then
3072 raise g_include;
3073 else
3074 raise g_not_include;
3075 end if;
3076 end if;
3077 end loop;
3078 --
3079 if p_excld_flag = 'Y' then
3080 raise g_include;
3081 end if;
3082 --
3083 if g_debug then
3084 hr_utility.set_location(' Exiting:'||l_proc, 15);
3085 end if;
3086 --
3087 raise g_not_include;
3088 --
3089 Exception
3090 --
3091 when g_include then
3092 null;
3093 --
3094 when g_not_include then
3095 raise g_not_include; --- will be handled in the calling program
3096 --
3097 End chk_enrt_pgm_incl;
3098 --
3099 -----------------------------------------------------------------------------
3100 ---------------------------< chk_enrt_pl_typ_incl >----------------------------
3101 -----------------------------------------------------------------------------
3102 -- The following procedure evaluates pl_typ id.
3103 --
3104 Procedure chk_enrt_pl_typ_incl
3105 (p_pl_typ_id in ben_prtt_enrt_rslt_f.pl_typ_id%type,
3106 p_excld_flag in varchar2)
3107 is
3108 --
3109 l_proc varchar2(72);
3110 --
3111 Begin
3112 --
3113 if g_debug then
3114 l_proc := g_package||'chk_enrt_pl_typ_incl';
3115 hr_utility.set_location(' Entering:'||l_proc, 5);
3116 end if;
3117 --
3118 for i in 1..g_enrt_pl_typ_list.count
3119 loop
3120 if p_pl_typ_id = g_enrt_pl_typ_list(i) then
3121 if p_excld_flag = 'N' then
3122 raise g_include;
3123 else
3124 raise g_not_include;
3125 end if;
3126 end if;
3127 end loop;
3128 --
3129 if p_excld_flag = 'Y' then
3130 raise g_include;
3131 end if;
3132 --
3133 if g_debug then
3134 hr_utility.set_location(' Exiting:'||l_proc, 15);
3135 end if;
3136 --
3137 raise g_not_include;
3138 --
3139 Exception
3140 --
3141 when g_include then
3142 null;
3143 --
3144 when g_not_include then
3145 raise g_not_include; --- will be handled in the calling program
3146 --
3147 End chk_enrt_pl_typ_incl;
3148 --
3149 -----------------------------------------------------------------------------
3150 ---------------------------< chk_enrt_last_upd_dt_incl >----------------------
3151 -----------------------------------------------------------------------------
3152 -- The following procedure evaluates enrt_last_upd_dt id.
3153 --
3154 Procedure chk_enrt_last_upd_dt_incl
3155 (p_last_update_date in ben_prtt_enrt_rslt_f.last_update_date%type,
3156 p_effective_date in date,
3157 p_excld_flag in varchar2 ,
3158 p_pl_id in number default null )
3159 is
3160 --
3161 l_proc varchar2(72);
3162 l_low_date date;
3163 l_high_date date;
3164 --
3165 Begin
3166 --
3167 if g_debug then
3168 l_proc := g_package||'chk_enrt_last_upd_dt_incl';
3169 hr_utility.set_location(' Entering:'||l_proc, 5);
3170 end if;
3171 --
3172 for i in 1..g_enrt_last_upd_dt_list1.count
3173 loop
3174 l_low_date := ben_ext_util.calc_ext_date
3175 (p_ext_date_cd => g_enrt_last_upd_dt_list1(i),
3176 p_abs_date => p_effective_date,
3177 p_ext_dfn_id => ben_extract.g_ext_dfn_id ,
3178 p_pl_id => p_pl_id );
3179
3180 l_high_date := ben_ext_util.calc_ext_date
3181 (p_ext_date_cd => g_enrt_last_upd_dt_list2(i),
3182 p_abs_date => p_effective_date,
3183 p_ext_dfn_id => ben_extract.g_ext_dfn_id ,
3184 p_pl_id => p_pl_id );
3185
3186 if p_last_update_date between l_low_date and nvl(l_high_date,l_low_date) then
3187 if p_excld_flag = 'N' then
3188 raise g_include;
3189 else
3190 raise g_not_include;
3191 end if;
3192 end if;
3193 end loop;
3194 --
3195 if p_excld_flag = 'Y' then
3196 raise g_include;
3197 end if;
3198 --
3199 if g_debug then
3200 hr_utility.set_location(' Exiting:'||l_proc, 15);
3201 end if;
3202 --
3203 raise g_not_include;
3204 --
3205 Exception
3206 --
3207 when g_include then
3208 null;
3209 --
3210 when g_not_include then
3211 raise g_not_include; --- will be handled in the calling program
3212 --
3213 End chk_enrt_last_upd_dt_incl;
3214 --
3215 -----------------------------------------------------------------------------
3216 ---------------------------< chk_enrt_ler_name_incl >----------------------------
3217 -----------------------------------------------------------------------------
3218 -- The following procedure evaluates ler_name id.
3219 --
3220 Procedure chk_enrt_ler_name_incl
3221 (p_ler_id in ben_per_in_ler.ler_id%type,
3222 p_excld_flag in varchar2)
3223 is
3224 --
3225 l_proc varchar2(72);
3226 --
3227 Begin
3228 --
3229 if g_debug then
3230 l_proc := g_package||'chk_enrt_ler_name_incl';
3231 hr_utility.set_location(' Entering:'||l_proc, 5);
3232 end if;
3233 --
3234 for i in 1..g_enrt_ler_name_list.count
3235 loop
3236 if p_ler_id = g_enrt_ler_name_list(i) then
3237 if p_excld_flag = 'N' then
3238 raise g_include;
3239 else
3240 raise g_not_include;
3241 end if;
3242 end if;
3243 end loop;
3244 --
3245 if p_excld_flag = 'Y' then
3246 raise g_include;
3247 end if;
3248 --
3249 if g_debug then
3250 hr_utility.set_location(' Exiting:'||l_proc, 15);
3251 end if;
3252 --
3253 raise g_not_include;
3254 --
3255 Exception
3256 --
3257 when g_include then
3258 null;
3259 --
3260 when g_not_include then
3261 raise g_not_include; --- will be handled in the calling program
3262 --
3263 End chk_enrt_ler_name_incl;
3264 --
3265 -----------------------------------------------------------------------------
3266 ---------------------------< chk_enrt_ler_stat_incl >----------------------------
3267 -----------------------------------------------------------------------------
3268 -- The following procedure evaluates per in ler status cd
3269 --
3270 Procedure chk_enrt_ler_stat_incl
3271 (p_per_in_ler_stat_cd in ben_per_in_ler.per_in_ler_stat_cd%type,
3272 p_excld_flag in varchar2)
3273 is
3274 --
3275 l_proc varchar2(72);
3276 --
3277 Begin
3278 --
3279 if g_debug then
3280 l_proc := g_package||'chk_enrt_ler_stat_incl';
3281 hr_utility.set_location(' Entering:'||l_proc, 5);
3282 end if;
3283 --
3284 for i in 1..g_enrt_ler_stat_list.count
3285 loop
3286 if p_per_in_ler_stat_cd = g_enrt_ler_stat_list(i) then
3287 if p_excld_flag = 'N' then
3288 raise g_include;
3289 else
3290 raise g_not_include;
3291 end if;
3292 end if;
3293 end loop;
3294 --
3295 if p_excld_flag = 'Y' then
3296 raise g_include;
3297 end if;
3298 --
3299 if g_debug then
3300 hr_utility.set_location(' Exiting:'||l_proc, 15);
3301 end if;
3302 --
3303 raise g_not_include;
3304 --
3305 Exception
3306 --
3307 when g_include then
3308 null;
3309 --
3310 when g_not_include then
3311 raise g_not_include; --- will be handled in the calling program
3312 --
3313 End chk_enrt_ler_stat_incl;
3314 --
3315 -----------------------------------------------------------------------------
3316 ---------------------------< chk_enrt_ler_ocrd_dt_incl >----------------------
3317 -----------------------------------------------------------------------------
3318 -- The following procedure evaluates enrt_ler_ocrd_dt id.
3319 --
3320 Procedure chk_enrt_ler_ocrd_dt_incl
3321 (p_lf_evt_ocrd_dt in ben_per_in_ler.lf_evt_ocrd_dt%type,
3322 p_effective_date in date,
3323 p_excld_flag in varchar2,
3324 p_pl_id in number default null )
3325 is
3326 --
3327 l_proc varchar2(72);
3328 l_low_date date;
3329 l_high_date date;
3330 --
3331 Begin
3332 --
3333 if g_debug then
3334 l_proc := g_package||'chk_enrt_ler_ocrd_dt_incl';
3335 hr_utility.set_location(' Entering:'||l_proc, 5);
3336 end if;
3337 --
3338 for i in 1..g_enrt_ler_ocrd_dt_list1.count
3339 loop
3340 l_low_date := ben_ext_util.calc_ext_date
3341 (p_ext_date_cd => g_enrt_ler_ocrd_dt_list1(i),
3342 p_abs_date => p_effective_date,
3343 p_ext_dfn_id => ben_extract.g_ext_dfn_id ,
3344 p_pl_id => p_pl_id );
3345 l_high_date := ben_ext_util.calc_ext_date
3346 (p_ext_date_cd => g_enrt_ler_ocrd_dt_list2(i),
3347 p_abs_date => p_effective_date,
3348 p_ext_dfn_id => ben_extract.g_ext_dfn_id,
3349 p_pl_id => p_pl_id );
3350 if p_lf_evt_ocrd_dt between l_low_date and nvl(l_high_date,l_low_date) then
3351 if p_excld_flag = 'N' then
3352 raise g_include;
3353 else
3354 raise g_not_include;
3355 end if;
3356 end if;
3357 end loop;
3358 --
3359 if p_excld_flag = 'Y' then
3360 raise g_include;
3361 end if;
3362 --
3363 if g_debug then
3364 hr_utility.set_location(' Exiting:'||l_proc, 15);
3365 end if;
3366 --
3367 raise g_not_include;
3368 --
3369 Exception
3370 --
3371 when g_include then
3372 null;
3373 --
3374 when g_not_include then
3375 raise g_not_include; --- will be handled in the calling program
3376 --
3377 End chk_enrt_ler_ocrd_dt_incl;
3378 --
3379 -----------------------------------------------------------------------------
3380 ---------------------------< chk_enrt_ler_ntfn_dt_incl >----------------------
3381 -----------------------------------------------------------------------------
3382 -- The following procedure evaluates enrt_ler_ntfn_dt id.
3383 --
3384 Procedure chk_enrt_ler_ntfn_dt_incl
3385 (p_ntfn_dt in ben_per_in_ler.ntfn_dt%type,
3386 p_effective_date in date,
3387 p_excld_flag in varchar2,
3388 p_pl_id in number default null )
3389 is
3390 --
3391 l_proc varchar2(72);
3392 l_low_date date;
3393 l_high_date date;
3394 --
3395 Begin
3396 --
3397 if g_debug then
3398 l_proc := g_package||'chk_enrt_ler_ntfn_dt_incl';
3399 hr_utility.set_location(' Entering:'||l_proc, 5);
3400 end if;
3401 --
3402 for i in 1..g_enrt_ler_ntfn_dt_list1.count
3403 loop
3404 l_low_date := ben_ext_util.calc_ext_date
3405 (p_ext_date_cd => g_enrt_ler_ntfn_dt_list1(i),
3406 p_abs_date => p_effective_date,
3407 p_ext_dfn_id => ben_extract.g_ext_dfn_id);
3408 l_high_date := ben_ext_util.calc_ext_date
3409 (p_ext_date_cd => g_enrt_ler_ntfn_dt_list2(i),
3410 p_abs_date => p_effective_date,
3411 p_ext_dfn_id => ben_extract.g_ext_dfn_id ,
3412 p_pl_id => p_pl_id );
3413 if p_ntfn_dt between l_low_date and nvl(l_high_date,l_low_date) then
3414 if p_excld_flag = 'N' then
3415 raise g_include;
3416 else
3417 raise g_not_include;
3418 end if;
3419 end if;
3420 end loop;
3421 --
3422 if p_excld_flag = 'Y' then
3423 raise g_include;
3424 end if;
3425 --
3426 if g_debug then
3427 hr_utility.set_location(' Exiting:'||l_proc, 15);
3428 end if;
3429 --
3430 raise g_not_include;
3431 --
3432 Exception
3433 --
3434 when g_include then
3435 null;
3436 --
3437 when g_not_include then
3438 raise g_not_include; --- will be handled in the calling program
3439 --
3440 End chk_enrt_ler_ntfn_dt_incl;
3441 --
3442 --
3443 -----------------------------------------------------------------------------
3444 ---------------------------< chk_enrt_dpnt_rltn_incl >----------------------------
3445 -----------------------------------------------------------------------------
3446 -- The following procedure evaluates the relationships between enrtollment
3447 -- dependent
3448 --
3449 Procedure chk_enrt_dpnt_rltn_incl
3450 (p_per_in_ler_id in ben_prtt_enrt_rslt_f.per_in_ler_id%type,
3451 p_prtt_enrt_rslt_id in ben_prtt_enrt_rslt_f.prtt_enrt_rslt_id%type,
3452 p_dpnt_person_id in number,
3453 p_effective_date in date,
3454 p_excld_flag in varchar2) is
3455
3456 l_proc varchar2(72);
3457 l_dummy varchar2(1);
3458
3459
3460 begin
3461 if g_debug then
3462 l_proc := g_package||'chk_enrt_dpnt_rltn_incl';
3463 hr_utility.set_location(' Entering:'||l_proc, 5);
3464 end if;
3465
3466 --link the entrolled dependent with change even dependent
3467 if p_dpnt_person_id = nvl(ben_ext_person.g_chg_prmtr_06,'-1') then
3468 if p_excld_flag = 'N' then
3469 raise g_include;
3470 else
3471 raise g_not_include;
3472 end if;
3473 end if;
3474
3475 if g_debug then
3476 hr_utility.set_location(' Exiting:'||l_proc, 15);
3477 end if;
3478 if p_excld_flag = 'Y' then
3479 raise g_include;
3480 end if;
3481 --
3482 raise g_not_include;
3483 --
3484 Exception
3485 --
3486 when g_include then
3487 null;
3488 --
3489 when g_not_include then
3490 raise g_not_include; --- will be handled in the calling program
3491 --
3492 End chk_enrt_dpnt_rltn_incl;
3493
3494
3495
3496
3497
3498 -----------------------------------------------------------------------------
3499 ---------------------------< chk_enrt_rltn_incl >----------------------------
3500 -----------------------------------------------------------------------------
3501 -- The following procedure evaluates the relationships between enrtollment
3502 -- results and other tables.
3503 --
3504 Procedure chk_enrt_rltn_incl
3505 (p_per_in_ler_id in ben_prtt_enrt_rslt_f.per_in_ler_id%type,
3506 p_prtt_enrt_rslt_id in ben_prtt_enrt_rslt_f.prtt_enrt_rslt_id%type,
3507 p_pl_id in ben_prtt_enrt_rslt_f.pl_id%type,
3508 p_pl_typ_id in ben_prtt_enrt_rslt_f.pl_typ_id%type,
3509 p_pgm_id in ben_prtt_enrt_rslt_f.pgm_id%type,
3510 p_effective_date in date,
3511 p_excld_flag in varchar2)
3512 is
3513 --
3514 cursor c_cm_usg (p_per_cm_id number,
3515 p_pl_id number,
3516 p_pl_typ_id number,
3517 p_pgm_id number) is
3518 select null
3519 from ben_per_cm_usg_f pcu,
3520 ben_cm_typ_usg_f ctu
3521 where pcu.per_cm_id = p_per_cm_id
3522 and pcu.cm_typ_usg_id = ctu.cm_typ_usg_id
3523 and nvl(ctu.pl_id,p_pl_id) = p_pl_id
3524 and nvl(ctu.pl_typ_id,p_pl_typ_id) = p_pl_typ_id
3525 and nvl(ctu.pgm_id,p_pgm_id) = p_pgm_id
3526 and p_effective_date between pcu.effective_start_date and pcu.effective_end_date
3527 and p_effective_date between ctu.effective_start_date and ctu.effective_end_date;
3528 --
3529 l_proc varchar2(72);
3530 l_dummy varchar2(1);
3531 --
3532 Begin
3533 --
3534 if g_debug then
3535 l_proc := g_package||'chk_enrt_rltn_incl';
3536 hr_utility.set_location(' Entering:'||l_proc, 5);
3537 end if;
3538 --
3539 for i in 1..g_enrt_rltn_list.count
3540 loop
3541 if g_enrt_rltn_list(i) = 'EERCEP' then
3542 if ben_ext_person.g_chg_enrt_rslt_id is null then
3543 raise g_include;
3544 end if;
3545 if p_prtt_enrt_rslt_id = ben_ext_person.g_chg_enrt_rslt_id then
3546 if p_excld_flag = 'N' then
3547 raise g_include;
3548 else
3549 raise g_not_include;
3550 end if;
3551 end if;
3552 elsif g_enrt_rltn_list(i) = 'EERCMP' then
3553 if ben_ext_person.g_cm_per_in_ler_id is null then
3554 raise g_include;
3555 end if;
3556 if p_per_in_ler_id = ben_ext_person.g_cm_per_in_ler_id then
3557 if p_excld_flag = 'N' then
3558 raise g_include;
3559 else
3560 raise g_not_include;
3561 end if;
3562 end if;
3563 -- Enrt Rslt Comp Object must match that the communication usage.
3564 elsif g_enrt_rltn_list(i) = 'EERCMU' then
3565 open c_cm_usg (ben_ext_person.g_per_cm_id, p_pl_id, p_pl_typ_id, p_pgm_id);
3566 fetch c_cm_usg into l_dummy;
3567 if c_cm_usg%found then
3568 if p_excld_flag = 'N' then
3569 close c_cm_usg;
3570 raise g_include;
3571 else
3572 close c_cm_usg;
3573 raise g_not_include;
3574 end if;
3575 end if;
3576 end if;
3577 end loop;
3578 --
3579 if p_excld_flag = 'Y' then
3580 raise g_include;
3581 end if;
3582 --
3583 if g_debug then
3584 hr_utility.set_location(' Exiting:'||l_proc, 15);
3585 end if;
3586 --
3587 raise g_not_include;
3588 --
3589 Exception
3590 --
3591 when g_include then
3592 null;
3593 --
3594 when g_not_include then
3595 raise g_not_include; --- will be handled in the calling program
3596 --
3597 End chk_enrt_rltn_incl;
3598 --
3599
3600 -----------------------------------------------------------------------------
3601 ---------------------------< chk_elct_opt_incl >----------------------------
3602 -----------------------------------------------------------------------------
3603 -- The following procedure evaluates plan id.
3604 --
3605 Procedure chk_elct_opt_incl
3606 (p_opt_id in ben_opt_f.opt_id%type,
3607 p_excld_flag in varchar2)
3608 is
3609 --
3610 l_proc varchar2(72);
3611 --
3612 Begin
3613 --
3614 if g_debug then
3615 l_proc := g_package||'chk_elct_opt_incl';
3616 hr_utility.set_location(' Entering:'||l_proc, 5);
3617 end if;
3618 --
3619 -- if p_pl_id is null then error, it must be passed in!
3620 --
3621 for i in 1..g_elct_opt_list.count
3622 loop
3623 if g_debug then
3624 hr_utility.set_location(' option id '|| p_opt_id || ':'||g_elct_opt_list(i), 5);
3625 end if;
3626
3627 if p_opt_id = g_elct_opt_list(i) then
3628 if p_excld_flag = 'N' then
3629 hr_utility.set_location(' rise incl ', 5);
3630 raise g_include;
3631 else
3632 hr_utility.set_location(' rise excl ', 5);
3633 raise g_not_include;
3634 end if;
3635 end if;
3636 end loop;
3637 --
3638 if p_excld_flag = 'Y' then
3639 raise g_include;
3640 end if;
3641 --
3642 if g_debug then
3643 hr_utility.set_location(' Exiting:'||l_proc, 15);
3644 end if;
3645 --
3646 raise g_not_include;
3647 --
3648 Exception
3649 --
3650 when g_include then
3651 if g_debug then
3652 hr_utility.set_location(' option id include ', 5);
3653 end if;
3654
3655 null;
3656 --
3657 when g_not_include then
3658 raise g_not_include; --- will be handled in the calling program
3659 --
3660 End chk_elct_opt_incl;
3661 --
3662
3663
3664
3665 --
3666 -----------------------------------------------------------------------------
3667 ---------------------------< chk_elct_plan_incl >----------------------------
3668 -----------------------------------------------------------------------------
3669 -- The following procedure evaluates plan id.
3670 --
3671 Procedure chk_elct_plan_incl
3672 (p_pl_id in ben_elig_per_elctbl_chc.pl_id%type,
3673 p_excld_flag in varchar2)
3674 is
3675 --
3676 l_proc varchar2(72);
3677 --
3678 Begin
3679 --
3680 if g_debug then
3681 l_proc := g_package||'chk_elct_plan_incl';
3682 hr_utility.set_location(' Entering:'||l_proc, 5);
3683 end if;
3684 --
3685 -- if p_pl_id is null then error, it must be passed in!
3686 --
3687 for i in 1..g_elct_plan_list.count
3688 loop
3689 if p_pl_id = g_elct_plan_list(i) then
3690 if p_excld_flag = 'N' then
3691 raise g_include;
3692 else
3693 raise g_not_include;
3694 end if;
3695 end if;
3696 end loop;
3697 --
3698 if p_excld_flag = 'Y' then
3699 raise g_include;
3700 end if;
3701 --
3702 if g_debug then
3703 hr_utility.set_location(' Exiting:'||l_proc, 15);
3704 end if;
3705 --
3706 raise g_not_include;
3707 --
3708 Exception
3709 --
3710 when g_include then
3711 null;
3712 --
3713 when g_not_include then
3714 raise g_not_include; --- will be handled in the calling program
3715 --
3716 End chk_elct_plan_incl;
3717 --
3718 --
3719 -----------------------------------------------------------------------------
3720 ---------------------------< chk_elct_rg_plan_incl >----------------------------
3721 -----------------------------------------------------------------------------
3722 -- The following procedure evaluates plans of a particular reporting group id.
3723 --
3724 Procedure chk_elct_rg_plan_incl
3725 (p_pl_id in ben_elig_per_elctbl_chc.pl_id%type,
3726 p_excld_flag in varchar2)
3727 is
3728 --
3729 l_proc varchar2(72);
3730 --
3731 Begin
3732 --
3733 if g_debug then
3734 l_proc := g_package||'chk_elct_rg_plan_incl';
3735 hr_utility.set_location(' Entering:'||l_proc, 5);
3736 end if;
3737 --
3738 -- if p_pl_id is null then error, it must be passed in!
3739 --
3740 for i in 1..g_elct_rg_plan_list.count
3741 loop
3742 if p_pl_id = g_elct_rg_plan_list(i) then
3743 if p_excld_flag = 'N' then
3744 raise g_include;
3745 else
3746 raise g_not_include;
3747 end if;
3748 end if;
3749 end loop;
3750 --
3751 if p_excld_flag = 'Y' then
3752 raise g_include;
3753 end if;
3754 --
3755 if g_debug then
3756 hr_utility.set_location(' Exiting:'||l_proc, 15);
3757 end if;
3758 --
3759 raise g_not_include;
3760 --
3761 Exception
3762 --
3763 when g_include then
3764 null;
3765 --
3766 when g_not_include then
3767 raise g_not_include; --- will be handled in the calling program
3768 --
3769 End chk_elct_rg_plan_incl;
3770 --
3771 -----------------------------------------------------------------------------
3772 ---------------------------< chk_elct_enrt_strt_dt_incl >----------------------
3773 -----------------------------------------------------------------------------
3774 -- The following procedure evaluates elct_enrt_strt_dt id.
3775 --
3776 Procedure chk_elct_enrt_strt_dt_incl
3777 (p_elct_enrt_strt_dt in ben_elig_per_elctbl_chc.enrt_cvg_strt_dt%type,
3778 p_effective_date in date,
3779 p_excld_flag in varchar2 ,
3780 p_pl_id in number default null )
3781 is
3782 --
3783 l_proc varchar2(72);
3784 l_low_date date;
3785 l_high_date date;
3786 --
3787 Begin
3788 --
3789 if g_debug then
3790 l_proc := g_package||'chk_elct_enrt_strt_dt_incl';
3791 hr_utility.set_location(' Entering:'||l_proc, 5);
3792 end if;
3793 --
3794 for i in 1..g_elct_enrt_strt_dt_list1.count
3795 loop
3796 l_low_date := ben_ext_util.calc_ext_date
3797 (p_ext_date_cd => g_elct_enrt_strt_dt_list1(i),
3798 p_abs_date => p_effective_date,
3799 p_ext_dfn_id => ben_extract.g_ext_dfn_id,
3800 p_pl_id => p_pl_id );
3801 l_high_date := ben_ext_util.calc_ext_date
3802 (p_ext_date_cd => g_elct_enrt_strt_dt_list2(i),
3803 p_abs_date => p_effective_date,
3804 p_ext_dfn_id => ben_extract.g_ext_dfn_id ,
3805 p_pl_id => p_pl_id );
3806 if p_elct_enrt_strt_dt between l_low_date and nvl(l_high_date,l_low_date) then
3807 if p_excld_flag = 'N' then
3808 raise g_include;
3809 else
3810 raise g_not_include;
3811 end if;
3812 end if;
3813 end loop;
3814 --
3815 if p_excld_flag = 'Y' then
3816 raise g_include;
3817 end if;
3818 --
3819 if g_debug then
3820 hr_utility.set_location(' Exiting:'||l_proc, 15);
3821 end if;
3822 --
3823 raise g_not_include;
3824 --
3825 Exception
3826 --
3827 when g_include then
3828 null;
3829 --
3830 when g_not_include then
3831 raise g_not_include; --- will be handled in the calling program
3832 --
3833 End chk_elct_enrt_strt_dt_incl;
3834 --
3835 -----------------------------------------------------------------------------
3836 ---------------------------< chk_elct_pgm_incl >----------------------------
3837 -----------------------------------------------------------------------------
3838 -- The following procedure evaluates pgm id.
3839 --
3840 Procedure chk_elct_pgm_incl
3841 (p_pgm_id in ben_elig_per_elctbl_chc.pgm_id%type,
3842 p_excld_flag in varchar2)
3843 is
3844 --
3845 l_proc varchar2(72);
3846 --
3847 Begin
3848 --
3849 if g_debug then
3850 l_proc := g_package||'chk_elct_pgm_incl';
3851 hr_utility.set_location(' Entering:'||l_proc, 5);
3852 end if;
3853 --
3854 for i in 1..g_elct_pgm_list.count
3855 loop
3856 if p_pgm_id = g_elct_pgm_list(i) then
3857 if p_excld_flag = 'N' then
3858 raise g_include;
3859 else
3860 raise g_not_include;
3861 end if;
3862 end if;
3863 end loop;
3864 --
3865 if p_excld_flag = 'Y' then
3866 raise g_include;
3867 end if;
3868 --
3869 if g_debug then
3870 hr_utility.set_location(' Exiting:'||l_proc, 15);
3871 end if;
3872 --
3873 raise g_not_include;
3874 --
3875 Exception
3876 --
3877 when g_include then
3878 null;
3879 --
3880 when g_not_include then
3881 raise g_not_include; --- will be handled in the calling program
3882 --
3883 End chk_elct_pgm_incl;
3884 --
3885 --
3886 -----------------------------------------------------------------------------
3887 ---------------------------< chk_elct_pl_typ_incl >----------------------------
3888 -----------------------------------------------------------------------------
3889 -- The following procedure evaluates pl_typ id.
3890 --
3891 Procedure chk_elct_pl_typ_incl
3892 (p_pl_typ_id in ben_elig_per_elctbl_chc.pl_typ_id%type,
3893 p_excld_flag in varchar2)
3894 is
3895 --
3896 l_proc varchar2(72);
3897 --
3898 Begin
3899 --
3900 if g_debug then
3901 l_proc := g_package||'chk_elct_pl_typ_incl';
3902 hr_utility.set_location(' Entering:'||l_proc, 5);
3903 end if;
3904 --
3905 for i in 1..g_elct_pl_typ_list.count
3906 loop
3907 if p_pl_typ_id = g_elct_pl_typ_list(i) then
3908 if p_excld_flag = 'N' then
3909 raise g_include;
3910 else
3911 raise g_not_include;
3912 end if;
3913 end if;
3914 end loop;
3915 --
3916 if p_excld_flag = 'Y' then
3917 raise g_include;
3918 end if;
3919 --
3920 if g_debug then
3921 hr_utility.set_location(' Exiting:'||l_proc, 15);
3922 end if;
3923 --
3924 raise g_not_include;
3925 --
3926 Exception
3927 --
3928 when g_include then
3929 null;
3930 --
3931 when g_not_include then
3932 raise g_not_include; --- will be handled in the calling program
3933 --
3934 End chk_elct_pl_typ_incl;
3935 --
3936 --
3937 -----------------------------------------------------------------------------
3938 ---------------------------< chk_elct_last_upd_dt_incl >----------------------
3939 -----------------------------------------------------------------------------
3940 -- The following procedure evaluates elct_last_upd_dt id.
3941 --
3942 Procedure chk_elct_last_upd_dt_incl
3943 (p_last_update_date in ben_elig_per_elctbl_chc.last_update_date%type,
3944 p_effective_date in date,
3945 p_excld_flag in varchar2,
3946 p_pl_id in number default null )
3947 is
3948 --
3949 l_proc varchar2(72);
3950 l_low_date date;
3951 l_high_date date;
3952 --
3953 Begin
3954 --
3955 if g_debug then
3956 l_proc := g_package||'chk_elct_last_upd_dt_incl';
3957 hr_utility.set_location(' Entering:'||l_proc, 5);
3958 end if;
3959 --
3960 for i in 1..g_elct_last_upd_dt_list1.count
3961 loop
3962 l_low_date := ben_ext_util.calc_ext_date
3963 (p_ext_date_cd => g_elct_last_upd_dt_list1(i),
3964 p_abs_date => p_effective_date,
3965 p_ext_dfn_id => ben_extract.g_ext_dfn_id,
3966 p_pl_id => p_pl_id );
3967 l_high_date := ben_ext_util.calc_ext_date
3968 (p_ext_date_cd => g_elct_last_upd_dt_list2(i),
3969 p_abs_date => p_effective_date,
3970 p_ext_dfn_id => ben_extract.g_ext_dfn_id ,
3971 p_pl_id => p_pl_id );
3972 if p_last_update_date between l_low_date and nvl(l_high_date,l_low_date) then
3973 if p_excld_flag = 'N' then
3974 raise g_include;
3975 else
3976 raise g_not_include;
3977 end if;
3978 end if;
3979 end loop;
3980 --
3981 if p_excld_flag = 'Y' then
3982 raise g_include;
3983 end if;
3984 --
3985 if g_debug then
3986 hr_utility.set_location(' Exiting:'||l_proc, 15);
3987 end if;
3988 --
3989 raise g_not_include;
3990 --
3991 Exception
3992 --
3993 when g_include then
3994 null;
3995 --
3996 when g_not_include then
3997 raise g_not_include; --- will be handled in the calling program
3998 --
3999 End chk_elct_last_upd_dt_incl;
4000 --
4001 --
4002 -----------------------------------------------------------------------------
4003 ---------------------------< chk_elct_ler_name_incl >----------------------------
4004 -----------------------------------------------------------------------------
4005 -- The following procedure evaluates ler_name id.
4006 --
4007 Procedure chk_elct_ler_name_incl
4008 (p_ler_id in ben_per_in_ler.ler_id%type,
4009 p_excld_flag in varchar2)
4010 is
4011 --
4012 l_proc varchar2(72);
4013 --
4014 Begin
4015 --
4016 if g_debug then
4017 l_proc := g_package||'chk_elct_ler_name_incl';
4018 hr_utility.set_location(' Entering:'||l_proc, 5);
4019 end if;
4020 --
4021 for i in 1..g_elct_ler_name_list.count
4022 loop
4023 if p_ler_id = g_elct_ler_name_list(i) then
4024 if p_excld_flag = 'N' then
4025 raise g_include;
4026 else
4027 raise g_not_include;
4028 end if;
4029 end if;
4030 end loop;
4031 --
4032 if p_excld_flag = 'Y' then
4033 raise g_include;
4034 end if;
4035 --
4036 if g_debug then
4037 hr_utility.set_location(' Exiting:'||l_proc, 15);
4038 end if;
4039 --
4040 raise g_not_include;
4041 --
4042 Exception
4043 --
4044 when g_include then
4045 null;
4046 --
4047 when g_not_include then
4048 raise g_not_include; --- will be handled in the calling program
4049 --
4050 End chk_elct_ler_name_incl;
4051
4052 --
4053 -----------------------------------------------------------------------------
4054 ---------------------------< chk_elct_ler_stat_incl >----------------------------
4055 -----------------------------------------------------------------------------
4056 -- The following procedure evaluates per in ler status cd
4057 --
4058 Procedure chk_elct_ler_stat_incl
4059 (p_per_in_ler_stat_cd in ben_per_in_ler.per_in_ler_stat_cd%type,
4060 p_excld_flag in varchar2)
4061 is
4062 --
4063 l_proc varchar2(72);
4064 --
4065 Begin
4066 --
4067 if g_debug then
4068 l_proc := g_package||'chk_elct_ler_stat_incl';
4069 hr_utility.set_location(' Entering:'||l_proc, 5);
4070 end if;
4071 --
4072 for i in 1..g_elct_ler_stat_list.count
4073 loop
4074 if p_per_in_ler_stat_cd = g_elct_ler_stat_list(i) then
4075 if p_excld_flag = 'N' then
4076 raise g_include;
4077 else
4078 raise g_not_include;
4079 end if;
4080 end if;
4081 end loop;
4082 --
4083 if p_excld_flag = 'Y' then
4084 raise g_include;
4085 end if;
4086 --
4087 if g_debug then
4088 hr_utility.set_location(' Exiting:'||l_proc, 15);
4089 end if;
4090 --
4091 raise g_not_include;
4092 --
4093 Exception
4094 --
4095 when g_include then
4096 null;
4097 --
4098 when g_not_include then
4099 raise g_not_include; --- will be handled in the calling program
4100 --
4101 End chk_elct_ler_stat_incl;
4102 --
4103 --
4104 -----------------------------------------------------------------------------
4105 ---------------------------< chk_elct_yrprd_incl >----------------------------
4106 -----------------------------------------------------------------------------
4107 -- The following procedure evaluates year period
4108 --
4109 Procedure chk_elct_yrprd_incl
4110 (p_yrprd_id in ben_elig_per_elctbl_chc.yr_perd_id%type,
4111 p_excld_flag in varchar2)
4112 is
4113 --
4114 l_proc varchar2(72);
4115 --
4116 Begin
4117 --
4118 if g_debug then
4119 l_proc := g_package||'chk_elct_yrprd_incl';
4120 hr_utility.set_location(' Entering:'||l_proc, 5);
4121 end if;
4122 --
4123 for i in 1..g_elct_yrprd_list.count
4124 loop
4125 if p_yrprd_id = g_elct_yrprd_list(i) then
4126 if p_excld_flag = 'N' then
4127 raise g_include;
4128 else
4129 raise g_not_include;
4130 end if;
4131 end if;
4132 end loop;
4133 --
4134 if p_excld_flag = 'Y' then
4135 raise g_include;
4136 end if;
4137 --
4138 if g_debug then
4139 hr_utility.set_location(' Exiting:'||l_proc, 15);
4140 end if;
4141 --
4142 raise g_not_include;
4143 --
4144 Exception
4145 --
4146 when g_include then
4147 null;
4148 --
4149 when g_not_include then
4150 raise g_not_include; --- will be handled in the calling program
4151 --
4152 End chk_elct_yrprd_incl;
4153 --
4154 -----------------------------------------------------------------------------
4155 ---------------------------< chk_elct_ler_ocrd_dt_incl >----------------------
4156 -----------------------------------------------------------------------------
4157 -- The following procedure evaluates elct_ler_ocrd_dt id.
4158 --
4159 Procedure chk_elct_ler_ocrd_dt_incl
4160 (p_lf_evt_ocrd_dt in ben_per_in_ler.lf_evt_ocrd_dt%type,
4161 p_effective_date in date,
4162 p_excld_flag in varchar2,
4163 p_pl_id in number default null )
4164 is
4165 --
4166 l_proc varchar2(72);
4167 l_low_date date;
4168 l_high_date date;
4169 --
4170 Begin
4171 --
4172 if g_debug then
4173 l_proc := g_package||'chk_elct_ler_ocrd_dt_incl';
4174 hr_utility.set_location(' Entering:'||l_proc, 5);
4175 end if;
4176 --
4177 for i in 1..g_elct_ler_ocrd_dt_list1.count
4178 loop
4179 l_low_date := ben_ext_util.calc_ext_date
4180 (p_ext_date_cd => g_elct_ler_ocrd_dt_list1(i),
4181 p_abs_date => p_effective_date,
4182 p_ext_dfn_id => ben_extract.g_ext_dfn_id,
4183 p_pl_id => p_pl_id );
4184 l_high_date := ben_ext_util.calc_ext_date
4185 (p_ext_date_cd => g_elct_ler_ocrd_dt_list2(i),
4186 p_abs_date => p_effective_date,
4187 p_ext_dfn_id => ben_extract.g_ext_dfn_id,
4188 p_pl_id => p_pl_id );
4189 if p_lf_evt_ocrd_dt between l_low_date and nvl(l_high_date,l_low_date) then
4190 if p_excld_flag = 'N' then
4191 raise g_include;
4192 else
4193 raise g_not_include;
4194 end if;
4195 end if;
4196 end loop;
4197 --
4198 if p_excld_flag = 'Y' then
4199 raise g_include;
4200 end if;
4201 --
4202 if g_debug then
4203 hr_utility.set_location(' Exiting:'||l_proc, 15);
4204 end if;
4205 --
4206 raise g_not_include;
4207 --
4208 Exception
4209 --
4210 when g_include then
4211 null;
4212 --
4213 when g_not_include then
4214 raise g_not_include; --- will be handled in the calling program
4215 --
4216 End chk_elct_ler_ocrd_dt_incl;
4217 --
4218 --
4219 -----------------------------------------------------------------------------
4220 ---------------------------< chk_elct_ler_ntfn_dt_incl >----------------------
4221 -----------------------------------------------------------------------------
4222 -- The following procedure evaluates elct_ler_ntfn_dt id.
4223 --
4224 Procedure chk_elct_ler_ntfn_dt_incl
4225 (p_ntfn_dt in ben_per_in_ler.ntfn_dt%type,
4226 p_effective_date in date,
4227 p_excld_flag in varchar2 ,
4228 p_pl_id in number default null )
4229 is
4230 --
4231 l_proc varchar2(72);
4232 l_low_date date;
4233 l_high_date date;
4234 --
4235 Begin
4236 --
4237 if g_debug then
4238 l_proc := g_package||'chk_elct_ler_ntfn_dt_incl';
4239 hr_utility.set_location(' Entering:'||l_proc, 5);
4240 end if;
4241 --
4242 for i in 1..g_elct_ler_ntfn_dt_list1.count
4243 loop
4244 l_low_date := ben_ext_util.calc_ext_date
4245 (p_ext_date_cd => g_elct_ler_ntfn_dt_list1(i),
4246 p_abs_date => p_effective_date,
4247 p_ext_dfn_id => ben_extract.g_ext_dfn_id ,
4248 p_pl_id => p_pl_id );
4249 l_high_date := ben_ext_util.calc_ext_date
4250 (p_ext_date_cd => g_elct_ler_ntfn_dt_list2(i),
4251 p_abs_date => p_effective_date,
4252 p_ext_dfn_id => ben_extract.g_ext_dfn_id ,
4253 p_pl_id => p_pl_id );
4254 if p_ntfn_dt between l_low_date and nvl(l_high_date,l_low_date) then
4255 if p_excld_flag = 'N' then
4256 raise g_include;
4257 else
4258 raise g_not_include;
4259 end if;
4260 end if;
4261 end loop;
4262 --
4263 if p_excld_flag = 'Y' then
4264 raise g_include;
4265 end if;
4266 --
4267 if g_debug then
4268 hr_utility.set_location(' Exiting:'||l_proc, 15);
4269 end if;
4270 --
4271 raise g_not_include;
4272 --
4273 Exception
4274 --
4275 when g_include then
4276 null;
4277 --
4278 when g_not_include then
4279 raise g_not_include; --- will be handled in the calling program
4280 --
4281 End chk_elct_ler_ntfn_dt_incl;
4282 --
4283 --
4284 -----------------------------------------------------------------------------
4285 ---------------------------< chk_elct_rltn_incl >----------------------------
4286 -----------------------------------------------------------------------------
4287 -- The following procedure evaluates the relationships between enrtollment
4288 -- results and other tables.
4289 --
4290 Procedure chk_elct_rltn_incl
4291 (p_per_in_ler_id in ben_prtt_enrt_rslt_f.per_in_ler_id%type,
4292 p_prtt_enrt_rslt_id in ben_prtt_enrt_rslt_f.prtt_enrt_rslt_id%type,
4293 p_excld_flag in varchar2)
4294 is
4295 --
4296 l_proc varchar2(72);
4297 --
4298 Begin
4299 --
4300 if g_debug then
4301 l_proc := g_package||'chk_elct_rltn_incl';
4302 hr_utility.set_location(' Entering:'||l_proc, 5);
4303 end if;
4304 --
4305 for i in 1..g_elct_rltn_list.count
4306 loop
4307 if g_elct_rltn_list(i) = 'EECCMP' then
4308 if ben_ext_person.g_cm_per_in_ler_id is null then
4309 raise g_include;
4310 end if;
4311 if p_per_in_ler_id = ben_ext_person.g_cm_per_in_ler_id then
4312 if p_excld_flag = 'N' then
4313 raise g_include;
4314 else
4315 raise g_not_include;
4316 end if;
4317 end if;
4318 end if;
4319 end loop;
4320 --
4321 if p_excld_flag = 'Y' then
4322 raise g_include;
4323 end if;
4324 --
4325 if g_debug then
4326 hr_utility.set_location(' Exiting:'||l_proc, 15);
4327 end if;
4328 --
4329 raise g_not_include;
4330 --
4331 Exception
4332 --
4333 when g_include then
4334 null;
4335 --
4336 when g_not_include then
4337 raise g_not_include; --- will be handled in the calling program
4338 --
4339 End chk_elct_rltn_incl;
4340 --
4341 -----------------------------------------------------------------------------
4342 ---------------------------< chk_chg_evt_cd_incl >----------------------------
4343 -----------------------------------------------------------------------------
4344 -- The following procedure evaluates change event code
4345 --
4346 Procedure chk_chg_evt_cd_incl
4347 (p_chg_evt_cd in ben_ext_chg_evt_log.chg_evt_cd%type,
4348 p_chg_evt_source in ben_ext_chg_evt_log.chg_evt_cd%type,
4349 p_excld_flag in varchar2)
4350 is
4351 --
4352 l_proc varchar2(72);
4353 --
4354 Begin
4355 --
4356 if g_debug then
4357 l_proc := g_package||'chk_chg_evt_cd_incl';
4358 hr_utility.set_location(' Entering:'||l_proc, 5);
4359 end if;
4360 --
4361 if p_chg_evt_source is null or p_chg_evt_source = 'BEN' then
4362 for i in 1..g_chg_evt_list.count
4363 loop
4364 if p_chg_evt_cd = g_chg_evt_list(i) then
4365 if p_excld_flag = 'N' then
4366 raise g_include;
4367 else
4368 raise g_not_include;
4369 end if;
4370 end if;
4371 end loop;
4372 end if ;
4373
4374 if p_chg_evt_source = 'PAY' then
4375 for i in 1..g_chg_pay_evt_list.count
4376 loop
4377 if p_chg_evt_cd = g_chg_pay_evt_list(i) then
4378 if p_excld_flag = 'N' then
4379 raise g_include;
4380 else
4381 raise g_not_include;
4382 end if;
4383 end if;
4384 end loop;
4385
4386 end if ;
4387 --
4388 if p_excld_flag = 'Y' then
4389 raise g_include;
4390 end if;
4391 --
4392 if g_debug then
4393 hr_utility.set_location(' Exiting:'||l_proc, 15);
4394 end if;
4395 --
4396 raise g_not_include;
4397 --
4398 Exception
4399 --
4400 when g_include then
4401 null;
4402 --
4403 when g_not_include then
4404 raise g_not_include; --- will be handled in the calling program
4405 --
4406 End chk_chg_evt_cd_incl;
4407 --
4408 -----------------------------------------------------------------------------
4409 ---------------------------< chk_chg_eff_dt_incl >----------------------
4410 -----------------------------------------------------------------------------
4411 -- The following procedure evaluates chg_eff_dt.
4412 --
4413 Procedure chk_chg_eff_dt_incl
4414 (p_chg_eff_dt in ben_ext_chg_evt_log.chg_eff_dt%type,
4415 p_effective_date in date,
4416 p_excld_flag in varchar2)
4417 is
4418 --
4419 l_proc varchar2(72);
4420 l_low_date date;
4421 l_high_date date;
4422 --
4423 Begin
4424 --
4425 if g_debug then
4426 l_proc := g_package||'chk_chg_eff_dt_incl';
4427 hr_utility.set_location(' Entering:'||l_proc, 5);
4428 end if;
4429 --
4430 for i in 1..g_chg_eff_dt_list1.count
4431 loop
4432 --- if the concurrent manager param is passed in then use that
4433 if ben_ext_thread.g_effective_start_date is not null then
4434 l_low_date := ben_ext_thread.g_effective_start_date ;
4435 l_high_date := ben_ext_thread.g_effective_end_date ;
4436 hr_utility.set_location( ' param chg eff date ' || l_low_date || ' /' || l_high_date, 99 );
4437 else
4438 l_low_date := ben_ext_util.calc_ext_date
4439 (p_ext_date_cd => g_chg_eff_dt_list1(i),
4440 p_abs_date => p_effective_date,
4441 p_ext_dfn_id => ben_extract.g_ext_dfn_id);
4442 l_high_date := ben_ext_util.calc_ext_date
4443 (p_ext_date_cd => g_chg_eff_dt_list2(i),
4444 p_abs_date => p_effective_date,
4445 p_ext_dfn_id => ben_extract.g_ext_dfn_id);
4446 end if ;
4447 ---
4448 if p_chg_eff_dt between l_low_date and nvl(l_high_date,l_low_date) then
4449 if p_excld_flag = 'N' then
4450 raise g_include;
4451 else
4452 raise g_not_include;
4453 end if;
4454 end if;
4455 end loop;
4456 --
4457 if p_excld_flag = 'Y' then
4458 raise g_include;
4459 end if;
4460 --
4461 if g_debug then
4462 hr_utility.set_location(' Exiting:'||l_proc, 15);
4463 end if;
4464 --
4465 raise g_not_include;
4466 --
4467 Exception
4468 --
4469 when g_include then
4470 null;
4471 --
4472 when g_not_include then
4473 raise g_not_include; --- will be handled in the calling program
4474 --
4475 End chk_chg_eff_dt_incl;
4476 --
4477 --
4478 -----------------------------------------------------------------------------
4479 ---------------------------< chk_chg_actl_dt_incl >----------------------
4480 -----------------------------------------------------------------------------
4481 -- The following procedure evaluates chg_actl_dt.
4482 --
4483 Procedure chk_chg_actl_dt_incl
4484 (p_chg_actl_dt in ben_ext_chg_evt_log.chg_actl_dt%type,
4485 p_effective_date in date,
4486 p_excld_flag in varchar2)
4487 is
4488 --
4489 l_proc varchar2(72);
4490 l_low_date date;
4491 l_high_date date;
4492 --
4493 Begin
4494 --
4495 if g_debug then
4496 l_proc := g_package||'chk_chg_actl_dt_incl';
4497 hr_utility.set_location(' Entering:'||l_proc, 5);
4498 end if;
4499 --
4500 for i in 1..g_chg_actl_dt_list1.count
4501 loop
4502 --- when the date passed from param use the param date
4503 if ben_ext_thread.g_actual_start_date is not null then
4504 l_low_date := ben_ext_thread.g_actual_start_date ;
4505 l_high_date := ben_ext_thread.g_actual_end_date ;
4506 hr_utility.set_location( ' param chg act date ' || l_low_date || ' /' || l_high_date, 99 );
4507 else
4508 l_low_date := ben_ext_util.calc_ext_date
4509 (p_ext_date_cd => g_chg_actl_dt_list1(i),
4510 p_abs_date => p_effective_date,
4511 p_ext_dfn_id => ben_extract.g_ext_dfn_id);
4512 l_high_date := ben_ext_util.calc_ext_date
4513 (p_ext_date_cd => g_chg_actl_dt_list2(i),
4514 p_abs_date => p_effective_date,
4515 p_ext_dfn_id => ben_extract.g_ext_dfn_id);
4516 end if ;
4517 if p_chg_actl_dt between l_low_date and nvl(l_high_date,l_low_date) then
4518 if p_excld_flag = 'N' then
4519 raise g_include;
4520 else
4521 raise g_not_include;
4522 end if;
4523 end if;
4524 end loop;
4525 --
4526 if p_excld_flag = 'Y' then
4527 raise g_include;
4528 end if;
4529 --
4530 if g_debug then
4531 hr_utility.set_location(' Exiting:'||l_proc, 15);
4532 end if;
4533 --
4534 raise g_not_include;
4535 --
4536 Exception
4537 --
4538 when g_include then
4539 null;
4540 --
4541 when g_not_include then
4542 raise g_not_include; --- will be handled in the calling program
4543 --
4544 End chk_chg_actl_dt_incl;
4545 --
4546 -----------------------------------------------------------------------------
4547 ---------------------------< chk_chg_login_incl >----------------------------
4548 -----------------------------------------------------------------------------
4549 -- The following procedure evaluates change last_updated_login
4550 --
4551 Procedure chk_chg_login_incl
4552 (p_last_update_login in ben_ext_chg_evt_log.last_update_login%type,
4553 p_excld_flag in varchar2)
4554 is
4555 --
4556 l_proc varchar2(72);
4557 --
4558 Begin
4559 --
4560 if g_debug then
4561 l_proc := g_package||'chk_chg_login_incl';
4562 hr_utility.set_location(' Entering:'||l_proc, 5);
4563 end if;
4564 --
4565 for i in 1..g_chg_login_list.count
4566 loop
4567 if p_last_update_login = g_chg_login_list(i) then
4568 if p_excld_flag = 'N' then
4569 raise g_include;
4570 else
4571 raise g_not_include;
4572 end if;
4573 end if;
4574 end loop;
4575 --
4576 if p_excld_flag = 'Y' then
4577 raise g_include;
4578 end if;
4579 --
4580 if g_debug then
4581 hr_utility.set_location(' Exiting:'||l_proc, 15);
4582 end if;
4583 --
4584 raise g_not_include;
4585 --
4586 Exception
4587 --
4588 when g_include then
4589 null;
4590 --
4591 when g_not_include then
4592 raise g_not_include; --- will be handled in the calling program
4593 --
4594 End chk_chg_login_incl;
4595 --
4596 -----------------------------------------------------------------------------
4597 ---------------------------< chk_cm_typ_incl >----------------------------
4598 -----------------------------------------------------------------------------
4599 -- The following procedure evaluates communication type
4600 --
4601 Procedure chk_cm_typ_incl
4602 (p_cm_typ_id in ben_per_cm_f.cm_typ_id%type,
4603 p_excld_flag in varchar2)
4604 is
4605 --
4606 l_proc varchar2(72);
4607 --
4608 Begin
4609 --
4610 if g_debug then
4611 l_proc := g_package||'chk_cm_typ_incl';
4612 hr_utility.set_location(' Entering:'||l_proc, 5);
4613 end if;
4614 --
4615 for i in 1..g_cm_typ_list.count
4616 loop
4617 if p_cm_typ_id = g_cm_typ_list(i) then
4618 if p_excld_flag = 'N' then
4619 raise g_include;
4620 else
4621 raise g_not_include;
4622 end if;
4623 end if;
4624 end loop;
4625 --
4626 if p_excld_flag = 'Y' then
4627 raise g_include;
4628 end if;
4629 --
4630 if g_debug then
4631 hr_utility.set_location(' Exiting:'||l_proc, 15);
4632 end if;
4633 --
4634 raise g_not_include;
4635 --
4636 Exception
4637 --
4638 when g_include then
4639 null;
4640 --
4641 when g_not_include then
4642 raise g_not_include; --- will be handled in the calling program
4643 --
4644 End chk_cm_typ_incl;
4645 --
4646 -----------------------------------------------------------------------------
4647 ---------------------------< chk_cm_last_upd_dt_incl >----------------------
4648 -----------------------------------------------------------------------------
4649 -- The following procedure evaluates cm_last_upd_dt.
4650 --
4651 Procedure chk_cm_last_upd_dt_incl
4652 (p_last_update_date in ben_per_cm_f.last_update_date%type,
4653 p_effective_date in date,
4654 p_excld_flag in varchar2)
4655 is
4656 --
4657 l_proc varchar2(72);
4658 l_low_date date;
4659 l_high_date date;
4660 --
4661 Begin
4662 --
4663 if g_debug then
4664 l_proc := g_package||'chk_cm_last_upd_dt_incl';
4665 hr_utility.set_location(' Entering:'||l_proc, 5);
4666 end if;
4667 --
4668 for i in 1..g_cm_last_upd_dt_list1.count
4669 loop
4670 l_low_date := ben_ext_util.calc_ext_date
4671 (p_ext_date_cd => g_cm_last_upd_dt_list1(i),
4672 p_abs_date => p_effective_date,
4673 p_ext_dfn_id => ben_extract.g_ext_dfn_id);
4674 l_high_date := ben_ext_util.calc_ext_date
4675 (p_ext_date_cd => g_cm_last_upd_dt_list2(i),
4676 p_abs_date => p_effective_date,
4677 p_ext_dfn_id => ben_extract.g_ext_dfn_id);
4678 if p_last_update_date between l_low_date and nvl(l_high_date,l_low_date) then
4679 if p_excld_flag = 'N' then
4680 raise g_include;
4681 else
4682 raise g_not_include;
4683 end if;
4684 end if;
4685 end loop;
4686 --
4687 if p_excld_flag = 'Y' then
4688 raise g_include;
4689 end if;
4690 --
4691 if g_debug then
4692 hr_utility.set_location(' Exiting:'||l_proc, 15);
4693 end if;
4694 --
4695 raise g_not_include;
4696 --
4697 Exception
4698 --
4699 when g_include then
4700 null;
4701 --
4702 when g_not_include then
4703 raise g_not_include; --- will be handled in the calling program
4704 --
4705 End chk_cm_last_upd_dt_incl;
4706 --
4707 -----------------------------------------------------------------------------
4708 ---------------------------< chk_cm_pr_last_upd_dt_incl >----------------------
4709 -----------------------------------------------------------------------------
4710 -- The following procedure evaluates cm_pr_last_upd_dt.
4711 --
4712 Procedure chk_cm_pr_last_upd_dt_incl
4713 (p_pvdd_last_update_date in ben_per_cm_prvdd_f.last_update_date%type,
4714 p_effective_date in date,
4715 p_excld_flag in varchar2)
4716 is
4717 --
4718 l_proc varchar2(72);
4719 l_low_date date;
4720 l_high_date date;
4721 --
4722 Begin
4723 --
4724 if g_debug then
4725 l_proc := g_package||'chk_cm_pr_last_upd_dt_incl';
4726 hr_utility.set_location(' Entering:'||l_proc, 5);
4727 end if;
4728 --
4729 for i in 1..g_cm_pr_last_upd_dt_list1.count
4730 loop
4731 l_low_date := ben_ext_util.calc_ext_date
4732 (p_ext_date_cd => g_cm_pr_last_upd_dt_list1(i),
4733 p_abs_date => p_effective_date,
4734 p_ext_dfn_id => ben_extract.g_ext_dfn_id);
4735 l_high_date := ben_ext_util.calc_ext_date
4736 (p_ext_date_cd => g_cm_pr_last_upd_dt_list2(i),
4737 p_abs_date => p_effective_date,
4738 p_ext_dfn_id => ben_extract.g_ext_dfn_id);
4739 if p_pvdd_last_update_date between l_low_date and nvl(l_high_date,l_low_date) then
4740 if p_excld_flag = 'N' then
4741 raise g_include;
4742 else
4743 raise g_not_include;
4744 end if;
4745 end if;
4746 end loop;
4747 --
4748 if p_excld_flag = 'Y' then
4749 raise g_include;
4750 end if;
4751 --
4752 if g_debug then
4753 hr_utility.set_location(' Exiting:'||l_proc, 15);
4754 end if;
4755 --
4756 raise g_not_include;
4757 --
4758 Exception
4759 --
4760 when g_include then
4761 null;
4762 --
4763 when g_not_include then
4764 raise g_not_include; --- will be handled in the calling program
4765 --
4766 End chk_cm_pr_last_upd_dt_incl;
4767
4768 -----------------------------------------------------------------------------
4769 ---------------------------< chk_payroll_end_date_incl >----------------------
4770 -----------------------------------------------------------------------------
4771 -- The following procedure evaluates chk_payroll_end_date_incl;.
4772 -- payrill payperiod end date
4773 --
4774 Procedure chk_payroll_end_date_incl
4775 (p_pay_end_date in pay_payroll_actions.effective_date%type,
4776 p_effective_date in date,
4777 p_excld_flag in varchar2)
4778 is
4779 --
4780 l_proc varchar2(72);
4781 l_low_date date;
4782 l_high_date date;
4783 --
4784 Begin
4785 --
4786 if g_debug then
4787 l_proc := g_package||'chk_payroll_end_date_incl';
4788 hr_utility.set_location(' Entering:'||l_proc, 5);
4789 end if;
4790 --
4791 for i in 1..g_payroll_last_dt_list1.count
4792 loop
4793
4794 l_low_date := ben_ext_util.calc_ext_date
4795 (p_ext_date_cd => g_payroll_last_dt_list1(i),
4796 p_abs_date => p_effective_date,
4797 p_ext_dfn_id => ben_extract.g_ext_dfn_id);
4798
4799 l_high_date := ben_ext_util.calc_ext_date
4800 (p_ext_date_cd => g_payroll_last_dt_list2(i),
4801 p_abs_date => p_effective_date,
4802 p_ext_dfn_id => ben_extract.g_ext_dfn_id);
4803 if g_debug then
4804 hr_utility.set_location(' hight date '|| l_high_date,51);
4805 hr_utility.set_location(' low date ' || l_low_date,51);
4806 end if;
4807
4808 if p_pay_end_date between l_low_date and nvl(l_high_date,l_low_date) then
4809 if p_excld_flag = 'N' then
4810 if g_debug then
4811 hr_utility.set_location('Y Exiting:'||l_proc, 51);
4812 end if;
4813 raise g_include;
4814 else
4815 if g_debug then
4816 hr_utility.set_location('N Exiting:'||l_proc, 51);
4817 end if;
4818 raise g_not_include;
4819 end if;
4820 end if;
4821 end loop;
4822 ---
4823 if p_excld_flag = 'Y' then
4824 if g_debug then
4825 hr_utility.set_location('Y Exiting:'||l_proc, 15);
4826 end if;
4827 raise g_include;
4828 end if;
4829 --
4830 if g_debug then
4831 hr_utility.set_location(' Y Exiting:'||l_proc, 15);
4832 end if;
4833 --
4834 raise g_not_include;
4835 --
4836 Exception
4837 --
4838 when g_include then
4839 null;
4840 --
4841 when g_not_include then
4842 raise g_not_include; --- will be handled in the calling program
4843 --
4844 End chk_payroll_end_date_incl;
4845
4846
4847
4848
4849 -----------------------------------------------------------------------------
4850 ---------------------------< chk_prem_last_upd_dt_incl >----------------------
4851 -----------------------------------------------------------------------------
4852 -- The following procedure evaluates cm_pr_last_upd_dt.
4853 -- premuim last updated date
4854 --
4855 Procedure chk_prem_last_upd_dt_incl
4856 (p_last_update_date in ben_prtt_prem_by_mo_f.last_update_date%type,
4857 p_effective_date in date,
4858 p_excld_flag in varchar2)
4859 is
4860 --
4861 l_proc varchar2(72);
4862 l_low_date date;
4863 l_high_date date;
4864 --
4865 Begin
4866 --
4867 if g_debug then
4868 l_proc := g_package||'chk_prem_last_upd_dt_incl';
4869 hr_utility.set_location(' Entering:'||l_proc, 5);
4870 end if;
4871 --
4872 for i in 1..g_prem_last_upd_dt_list1.count
4873 loop
4874 l_low_date := ben_ext_util.calc_ext_date
4875 (p_ext_date_cd => g_prem_last_upd_dt_list1(i),
4876 p_abs_date => p_effective_date,
4877 p_ext_dfn_id => ben_extract.g_ext_dfn_id);
4878 l_high_date := ben_ext_util.calc_ext_date
4879 (p_ext_date_cd => g_prem_last_upd_dt_list2(i),
4880 p_abs_date => p_effective_date,
4881 p_ext_dfn_id => ben_extract.g_ext_dfn_id);
4882 if g_debug then
4883 hr_utility.set_location(' hight date '|| l_high_date,16);
4884 hr_utility.set_location(' low date ' || l_low_date,16);
4885 hr_utility.set_location(' update dte '|| p_last_update_date,16);
4886 end if;
4887 if p_last_update_date between l_low_date and nvl(l_high_date,l_low_date) then
4888 if p_excld_flag = 'N' then
4889 if g_debug then
4890 hr_utility.set_location('Y Exiting:'||l_proc, 15);
4891 end if;
4892 raise g_include;
4893 else
4894 if g_debug then
4895 hr_utility.set_location('N Exiting:'||l_proc, 15);
4896 end if;
4897 raise g_not_include;
4898 end if;
4899 end if;
4900 end loop;
4901 ---
4902 if p_excld_flag = 'Y' then
4903 if g_debug then
4904 hr_utility.set_location('Y Exiting:'||l_proc, 15);
4905 end if;
4906 raise g_include;
4907 end if;
4908 --
4909 if g_debug then
4910 hr_utility.set_location(' Y Exiting:'||l_proc, 15);
4911 end if;
4912 --
4913 raise g_not_include;
4914 --
4915 Exception
4916 --
4917 when g_include then
4918 null;
4919 --
4920 when g_not_include then
4921 raise g_not_include; --- will be handled in the calling program
4922 --
4923 End chk_prem_last_upd_dt_incl;
4924
4925
4926 -----------------------------------------------------------------------------
4927 ---------------------------< chk_prem_month_year_no_incl >----------------------
4928 -----------------------------------------------------------------------------
4929 -- The following procedure evaluates chk_prem_month_year_no_incl.
4930 -- premuim month No and year no
4931 --
4932 Procedure chk_prem_month_year_no_incl
4933 (p_mo_num in ben_prtt_prem_by_mo_f.mo_num%type default null,
4934 p_yr_num in ben_prtt_prem_by_mo_f.yr_num%type default null,
4935 p_effective_date in date,
4936 p_excld_flag in varchar2)
4937 is
4938 --
4939 l_proc varchar2(72);
4940 l_low_date date;
4941 l_high_date date;
4942 l_low_mo_no number ;
4943 l_high_mo_no number ;
4944 l_low_yr_no number ;
4945 l_high_yr_no number ;
4946 --
4947 Begin
4948 --
4949 if g_debug then
4950 l_proc := g_package||'chk_prem_month_year_no_incl';
4951 hr_utility.set_location(' Entering:'||l_proc, 5);
4952 end if;
4953 --
4954 for i in 1..g_prem_month_year_dt_list1.count
4955 loop
4956 l_low_date := ben_ext_util.calc_ext_date
4957 (p_ext_date_cd => g_prem_month_year_dt_list1(i),
4958 p_abs_date => p_effective_date,
4959 p_ext_dfn_id => ben_extract.g_ext_dfn_id);
4960 l_high_date := ben_ext_util.calc_ext_date
4961 (p_ext_date_cd => g_prem_month_year_dt_list2(i),
4962 p_abs_date => p_effective_date,
4963 p_ext_dfn_id => ben_extract.g_ext_dfn_id);
4964 ---converting to month no and year no
4965 l_low_mo_no := to_number(to_char(l_low_date,'MM')) ;
4966 l_low_yr_no := to_number(to_char(l_low_date,'RRRR')) ;
4967
4968 l_high_mo_no := to_number(to_char(l_high_date,'MM')) ;
4969 l_high_yr_no := to_number(to_char(l_high_date,'RRRR')) ;
4970
4971 if g_debug then
4972 hr_utility.set_location(' hight month ' || l_low_mo_no ,161);
4973 hr_utility.set_location(' low month ' || l_high_mo_no,161);
4974 hr_utility.set_location(' hight year ' || l_low_yr_no ,161);
4975 hr_utility.set_location(' low year ' || l_high_yr_no,161);
4976 hr_utility.set_location(' month ' || p_mo_num,161);
4977 hr_utility.set_location(' year ' || p_yr_num,161);
4978 end if;
4979 ---validate month year no
4980 if p_mo_num between l_low_mo_no and nvl(l_high_mo_no,l_low_mo_no) and
4981 p_yr_num between l_low_yr_no and nvl(l_high_yr_no,l_low_yr_no) then
4982 if p_excld_flag = 'N' then
4983 raise g_include;
4984 else
4985 raise g_not_include;
4986 end if;
4987 end if ;
4988
4989 end loop;
4990 ---
4991 if p_excld_flag = 'Y' then
4992 raise g_include;
4993 end if;
4994 --
4995 if g_debug then
4996 hr_utility.set_location(' Exiting:'||l_proc, 15);
4997 end if;
4998 --
4999 raise g_not_include;
5000 --
5001 Exception
5002 --
5003 when g_include then
5004 null;
5005 --
5006 when g_not_include then
5007 raise g_not_include; --- will be handled in the calling program
5008 --
5009 End chk_prem_month_year_no_incl;
5010
5011
5012 --
5013 -----------------------------------------------------------------------------
5014 ---------------------------< chk_cm_sent_dt_incl >----------------------
5015 -----------------------------------------------------------------------------
5016 -- The following procedure evaluates cm_sent_dt.
5017 --
5018 Procedure chk_cm_sent_dt_incl
5019 (p_sent_dt in ben_per_cm_prvdd_f.sent_dt%type,
5020 p_effective_date in date,
5021 p_excld_flag in varchar2)
5022 is
5023 --
5024 l_proc varchar2(72);
5025 l_low_date date;
5026 l_high_date date;
5027 --
5028 Begin
5029 --
5030 if g_debug then
5031 l_proc := g_package||'chk_cm_sent_dt_incl';
5032 hr_utility.set_location(' Entering:'||l_proc, 5);
5033 end if;
5034 --
5035 for i in 1..g_cm_sent_dt_list1.count
5036 loop
5037 l_low_date := ben_ext_util.calc_ext_date
5038 (p_ext_date_cd => g_cm_sent_dt_list1(i),
5039 p_abs_date => p_effective_date,
5040 p_ext_dfn_id => ben_extract.g_ext_dfn_id);
5041 l_high_date := ben_ext_util.calc_ext_date
5042 (p_ext_date_cd => g_cm_sent_dt_list2(i),
5043 p_abs_date => p_effective_date,
5044 p_ext_dfn_id => ben_extract.g_ext_dfn_id);
5045 if p_sent_dt between l_low_date and nvl(l_high_date,l_low_date) then
5046 if p_excld_flag = 'N' then
5047 raise g_include;
5048 else
5049 raise g_not_include;
5050 end if;
5051 end if;
5052 end loop;
5053 --
5054 if p_excld_flag = 'Y' then
5055 raise g_include;
5056 end if;
5057 --
5058 if g_debug then
5059 hr_utility.set_location(' Exiting:'||l_proc, 15);
5060 end if;
5061 --
5062 raise g_not_include;
5063 --
5064 Exception
5065 --
5066 when g_include then
5067 null;
5068 --
5069 when g_not_include then
5070 raise g_not_include; --- will be handled in the calling program
5071 --
5072 End chk_cm_sent_dt_incl;
5073 --
5074 -----------------------------------------------------------------------------
5075 ---------------------------< chk_cm_to_be_sent_dt_incl >----------------------
5076 -----------------------------------------------------------------------------
5077 -- The following procedure evaluates cm_to_be_sent_dt.
5078 --
5079 Procedure chk_cm_to_be_sent_dt_incl
5080 (p_to_be_sent_dt in ben_per_cm_prvdd_f.to_be_sent_dt%type,
5081 p_effective_date in date,
5082 p_excld_flag in varchar2)
5083 is
5084 --
5085 l_proc varchar2(72);
5086 l_low_date date;
5087 l_high_date date;
5088 --
5089 Begin
5090 --
5091 if g_debug then
5092 l_proc := g_package||'chk_cm_to_be_sent_dt_incl';
5093 hr_utility.set_location(' Entering:'||l_proc, 5);
5094 end if;
5095 --
5096 for i in 1..g_cm_to_be_sent_dt_list1.count
5097 loop
5098 l_low_date := ben_ext_util.calc_ext_date
5099 (p_ext_date_cd => g_cm_to_be_sent_dt_list1(i),
5100 p_abs_date => p_effective_date,
5101 p_ext_dfn_id => ben_extract.g_ext_dfn_id);
5102 l_high_date := ben_ext_util.calc_ext_date
5103 (p_ext_date_cd => g_cm_to_be_sent_dt_list2(i),
5104 p_abs_date => p_effective_date,
5105 p_ext_dfn_id => ben_extract.g_ext_dfn_id);
5106 if p_to_be_sent_dt between l_low_date and nvl(l_high_date,l_low_date) then
5107 if p_excld_flag = 'N' then
5108 raise g_include;
5109 else
5110 raise g_not_include;
5111 end if;
5112 end if;
5113 end loop;
5114 --
5115 if p_excld_flag = 'Y' then
5116 raise g_include;
5117 end if;
5118 --
5119 if g_debug then
5120 hr_utility.set_location(' Exiting:'||l_proc, 15);
5121 end if;
5122 --
5123 raise g_not_include;
5124 --
5125 Exception
5126 --
5127 when g_include then
5128 null;
5129 --
5130 when g_not_include then
5131 raise g_not_include; --- will be handled in the calling program
5132 --
5133 End chk_cm_to_be_sent_dt_incl;
5134 --
5135 --
5136 -----------------------------------------------------------------------------
5137 ---------------------------< chk_actn_name_incl >----------------------------
5138 -----------------------------------------------------------------------------
5139 -- The following procedure evaluates communication type
5140 --
5141 Procedure chk_actn_name_incl
5142 (p_actn_typ_id in ben_prtt_enrt_actn_f.actn_typ_id%type,
5143 p_excld_flag in varchar2)
5144 is
5145 --
5146 l_proc varchar2(72);
5147 --
5148 Begin
5149 --
5150 if g_debug then
5151 l_proc := g_package||'chk_actn_name_incl';
5152 hr_utility.set_location(' Entering:'||l_proc, 5);
5153 end if;
5154 --
5155 for i in 1..g_actn_name_list.count
5156 loop
5157 if p_actn_typ_id = g_actn_name_list(i) then
5158 if p_excld_flag = 'N' then
5159 raise g_include;
5160 else
5161 raise g_not_include;
5162 end if;
5163 end if;
5164 end loop;
5165 --
5166 if p_excld_flag = 'Y' then
5167 raise g_include;
5168 end if;
5169 --
5170 if g_debug then
5171 hr_utility.set_location(' Exiting:'||l_proc, 15);
5172 end if;
5173 --
5174 raise g_not_include;
5175 --
5176 Exception
5177 --
5178 when g_include then
5179 null;
5180 --
5181 when g_not_include then
5182 raise g_not_include; --- will be handled in the calling program
5183 --
5184 End chk_actn_name_incl;
5185 --
5186 -----------------------------------------------------------------------------
5187 -----------------------< chk_actn_item_rltn_incl >----------------------------
5188 -----------------------------------------------------------------------------
5189 -- The following procedure evaluates the relationships between communications
5190 -- and prtt action items.
5191 --
5192 Procedure chk_actn_item_rltn_incl
5193 (p_prtt_enrt_actn_id in ben_prtt_enrt_actn_f.prtt_enrt_actn_id%type,
5194 p_excld_flag in varchar2)
5195 is
5196 --
5197 l_proc varchar2(72);
5198 --
5199 Begin
5200 --
5201 if g_debug then
5202 l_proc := g_package||'chk_actn_item_rltn_incl';
5203 hr_utility.set_location(' Entering:'||l_proc, 5);
5204 end if;
5205 --
5206 for i in 1..g_actn_item_rltn_list.count
5207 loop
5208 if g_actn_item_rltn_list(i) = 'EAICMP' then
5209 if ben_ext_person.g_cm_prtt_enrt_actn_id is null then
5210 raise g_include;
5211 end if;
5212 if p_prtt_enrt_actn_id = ben_ext_person.g_cm_prtt_enrt_actn_id then
5213 if p_excld_flag = 'N' then
5214 raise g_include;
5215 else
5216 raise g_not_include;
5217 end if;
5218 end if;
5219 end if;
5220 end loop;
5221 --
5222 if p_excld_flag = 'Y' then
5223 raise g_include;
5224 end if;
5225 --
5226 if g_debug then
5227 hr_utility.set_location(' Exiting:'||l_proc, 15);
5228 end if;
5229 --
5230 raise g_not_include;
5231 --
5232 Exception
5233 --
5234 when g_include then
5235 null;
5236 --
5237 when g_not_include then
5238 raise g_not_include; --- will be handled in the calling program
5239 --
5240 End chk_actn_item_rltn_incl;
5241 --
5242
5243 -- Timecard (OTL) Inclusion Function
5244
5245
5246
5247 -----------------------------------------------------------------------------
5248 ---------------------------< chk_tc_status_incl >----------------------------
5249 -----------------------------------------------------------------------------
5250 -- The following procedure evaluates plan id.
5251 --
5252 Procedure chk_tc_status_incl
5253 (p_tc_status in VARCHAR2
5254 ,p_excld_flag in varchar2)
5255 is
5256 --
5257 l_proc varchar2(72);
5258 --
5259 Begin
5260 --
5261 if g_debug then
5262 l_proc := g_package||'chk_tc_status_incl';
5263 hr_utility.set_location(' Entering:'||l_proc, 5);
5264 end if;
5265 --
5266 -- if p_tc_status_id is null then error, it must be passed in!
5267 --
5268 for i in 1..g_tc_status_list.count
5269 loop
5270 if p_tc_status = g_tc_status_list(i) then
5271 if p_excld_flag = 'N' then
5272 raise g_include;
5273 else
5274 raise g_not_include;
5275 end if;
5276 end if;
5277 end loop;
5278 --
5279 if p_excld_flag = 'Y' then
5280 raise g_include;
5281 end if;
5282 --
5283 if g_debug then
5284 hr_utility.set_location(' Exiting:'||l_proc, 15);
5285 end if;
5286 --
5287 raise g_not_include;
5288 --
5289 Exception
5290 --
5291 when g_include then
5292 null;
5293 --
5294 when g_not_include then
5295 raise g_not_include; --- will be handled in the calling program
5296 --
5297 End chk_tc_status_incl;
5298 --
5299
5300
5301 -----------------------------------------------------------------------------
5302 ---------------------------< chk_tc_deleted_incl >----------------------------
5303 -----------------------------------------------------------------------------
5304 -- The following procedure evaluates plan id.
5305 --
5306 Procedure chk_tc_deleted_incl
5307 (p_tc_deleted in VARCHAR2
5308 ,p_excld_flag in varchar2)
5309 is
5310 --
5311 l_proc varchar2(72);
5312 --
5313 Begin
5314 --
5315 if g_debug then
5316 l_proc := g_package||'chk_tc_deleted_incl';
5317 hr_utility.set_location(' Entering:'||l_proc, 5);
5318 end if;
5319 --
5320 -- if p_tc_deleted is null then error, it must be passed in!
5321 --
5322 for i in 1..g_tc_deleted_list.count
5323 loop
5324 if p_tc_deleted = g_tc_deleted_list(i) then
5325 if p_excld_flag = 'N' then
5326 raise g_include;
5327 else
5328 raise g_not_include;
5329 end if;
5330 end if;
5331 end loop;
5332 --
5333 if p_excld_flag = 'Y' then
5334 raise g_include;
5335 end if;
5336 --
5337 if g_debug then
5338 hr_utility.set_location(' Exiting:'||l_proc, 15);
5339 end if;
5340 --
5341 raise g_not_include;
5342 --
5343 Exception
5344 --
5345 when g_include then
5346 null;
5347 --
5348 when g_not_include then
5349 raise g_not_include; --- will be handled in the calling program
5350 --
5351 End chk_tc_deleted_incl;
5352 --
5353
5354
5355 -----------------------------------------------------------------------------
5356 ---------------------------< chk_project_id_incl >----------------------------
5357 -----------------------------------------------------------------------------
5358 -- The following procedure evaluates plan id.
5359 --
5360 Procedure chk_project_id_incl
5361 (p_project_id in VARCHAR2
5362 ,p_excld_flag in varchar2)
5363 is
5364 --
5365 l_proc varchar2(72);
5366 --
5367 Begin
5368 --
5369 if g_debug then
5370 l_proc := g_package||'chk_project_id_incl';
5371 hr_utility.set_location(' Entering:'||l_proc, 5);
5372 end if;
5373 --
5374 -- if p_project_id is null then error, it must be passed in!
5375 --
5376 for i in 1..g_project_id_list.count
5377 loop
5378 if p_project_id = g_project_id_list(i) then
5379 if p_excld_flag = 'N' then
5380 raise g_include;
5381 else
5382 raise g_not_include;
5383 end if;
5384 end if;
5385 end loop;
5386 --
5387 if p_excld_flag = 'Y' then
5388 raise g_include;
5389 end if;
5390 --
5391 if g_debug then
5392 hr_utility.set_location(' Exiting:'||l_proc, 15);
5393 end if;
5394 --
5395 raise g_not_include;
5396 --
5397 Exception
5398 --
5399 when g_include then
5400 null;
5401 --
5402 when g_not_include then
5403 raise g_not_include; --- will be handled in the calling program
5404 --
5405 End chk_project_id_incl;
5406 --
5407
5408
5409 -----------------------------------------------------------------------------
5410 ---------------------------< chk_task_id_incl >----------------------------
5411 -----------------------------------------------------------------------------
5412 -- The following procedure evaluates plan id.
5413 --
5414 Procedure chk_task_id_incl
5415 (p_task_id in VARCHAR2
5416 ,p_excld_flag in varchar2)
5417 is
5418 --
5419 l_proc varchar2(72);
5420 --
5421 Begin
5422 --
5423 if g_debug then
5424 l_proc := g_package||'chk_task_id_incl';
5425 hr_utility.set_location(' Entering:'||l_proc, 5);
5426 end if;
5427 --
5428 -- if p_task_id is null then error, it must be passed in!
5429 --
5430 for i in 1..g_task_id_list.count
5431 loop
5432 if p_task_id = g_task_id_list(i) then
5433 if p_excld_flag = 'N' then
5434 raise g_include;
5435 else
5436 raise g_not_include;
5437 end if;
5438 end if;
5439 end loop;
5440 --
5441 if p_excld_flag = 'Y' then
5442 raise g_include;
5443 end if;
5444 --
5445 if g_debug then
5446 hr_utility.set_location(' Exiting:'||l_proc, 15);
5447 end if;
5448 --
5449 raise g_not_include;
5450 --
5451 Exception
5452 --
5453 when g_include then
5454 null;
5455 --
5456 when g_not_include then
5457 raise g_not_include; --- will be handled in the calling program
5458 --
5459 End chk_task_id_incl;
5460 --
5461
5462
5463 -----------------------------------------------------------------------------
5464 ---------------------------< chk_exp_typ_id_incl >----------------------------
5465 -----------------------------------------------------------------------------
5466 -- The following procedure evaluates plan id.
5467 --
5468 Procedure chk_exp_typ_id_incl
5469 (p_exp_typ_id in VARCHAR2
5470 ,p_excld_flag in varchar2)
5471 is
5472 --
5473 l_proc varchar2(72);
5474 --
5475 Begin
5476 --
5477 if g_debug then
5478 l_proc := g_package||'chk_exp_typ_id_incl';
5479 hr_utility.set_location(' Entering:'||l_proc, 5);
5480 end if;
5481 --
5482 -- if p_exp_typ_id is null then error, it must be passed in!
5483 --
5484 for i in 1..g_exp_typ_id_list.count
5485 loop
5486 if p_exp_typ_id = g_exp_typ_id_list(i) then
5487 if p_excld_flag = 'N' then
5488 raise g_include;
5489 else
5490 raise g_not_include;
5491 end if;
5492 end if;
5493 end loop;
5494 --
5495 if p_excld_flag = 'Y' then
5496 raise g_include;
5497 end if;
5498 --
5499 if g_debug then
5500 hr_utility.set_location(' Exiting:'||l_proc, 15);
5501 end if;
5502 --
5503 raise g_not_include;
5504 --
5505 Exception
5506 --
5507 when g_include then
5508 null;
5509 --
5510 when g_not_include then
5511 raise g_not_include; --- will be handled in the calling program
5512 --
5513 End chk_exp_typ_id_incl;
5514 --
5515
5516
5517 -----------------------------------------------------------------------------
5518 ---------------------------< chk_element_type_id_incl >----------------------------
5519 -----------------------------------------------------------------------------
5520 -- The following procedure evaluates plan id.
5521 --
5522 Procedure chk_element_type_id_incl
5523 (p_element_type_id in VARCHAR2
5524 ,p_excld_flag in varchar2)
5525 is
5526 --
5527 l_proc varchar2(72);
5528 --
5529 Begin
5530 --
5531 if g_debug then
5532 l_proc := g_package||'chk_element_type_id_incl';
5533 hr_utility.set_location(' Entering:'||l_proc, 5);
5534 end if;
5535 --
5536 -- if p_element_type_id is null then error, it must be passed in!
5537 --
5538 for i in 1..g_element_type_list.count
5539 loop
5540 if p_element_type_id = g_element_type_list(i) then
5541 if p_excld_flag = 'N' then
5542 raise g_include;
5543 else
5544 raise g_not_include;
5545 end if;
5546 end if;
5547 end loop;
5548 --
5549 if p_excld_flag = 'Y' then
5550 raise g_include;
5551 end if;
5552 --
5553 if g_debug then
5554 hr_utility.set_location(' Exiting:'||l_proc, 15);
5555 end if;
5556 --
5557 raise g_not_include;
5558 --
5559 Exception
5560 --
5561 when g_include then
5562 null;
5563 --
5564 when g_not_include then
5565 raise g_not_include; --- will be handled in the calling program
5566 --
5567 End chk_element_type_id_incl;
5568 --
5569
5570
5571 -----------------------------------------------------------------------------
5572 ---------------------------< chk_po_num_incl >----------------------------
5573 -----------------------------------------------------------------------------
5574 -- The following procedure evaluates plan id.
5575 --
5576 Procedure chk_po_num_incl
5577 (p_po_num in VARCHAR2
5578 ,p_excld_flag in varchar2)
5579 is
5580 --
5581 l_proc varchar2(72);
5582 --
5583 Begin
5584 --
5585 if g_debug then
5586 l_proc := g_package||'chk_po_num_incl';
5587 hr_utility.set_location(' Entering:'||l_proc, 5);
5588 end if;
5589 --
5590 -- if p_po_num is null then error, it must be passed in!
5591 --
5592 for i in 1..g_po_num_list.count
5593 loop
5594 if p_po_num = g_po_num_list(i) then
5595 if p_excld_flag = 'N' then
5596 raise g_include;
5597 else
5598 raise g_not_include;
5599 end if;
5600 end if;
5601 end loop;
5602 --
5603 if p_excld_flag = 'Y' then
5604 raise g_include;
5605 end if;
5606 --
5607 if g_debug then
5608 hr_utility.set_location(' Exiting:'||l_proc, 15);
5609 end if;
5610 --
5611 raise g_not_include;
5612 --
5613 Exception
5614 --
5615 when g_include then
5616 null;
5617 --
5618 when g_not_include then
5619 raise g_not_include; --- will be handled in the calling program
5620 --
5621 End chk_po_num_incl;
5622 -- cwb
5623
5624 Procedure chk_cwb_pl_prd_incl
5625 (p_pl_id in number ,
5626 p_LF_EVT_OCRD_DT in date ,
5627 p_effective_date in date,
5628 p_excld_flag in varchar2)
5629 is
5630 --
5631 cursor c1 (l_val number) is
5632 SELECT enp.ASND_LF_EVT_DT
5633 FROM ben_enrt_perd enp
5634 WHERE enp.enrt_perd_id = l_val
5635 ;
5636 --
5637 l_ASND_LF_EVT_DT date ;
5638 --
5639 l_proc varchar2(72);
5640 --
5641 Begin
5642 --
5643 if g_debug then
5644 l_proc := g_package||'chk_cwb_pl_prd_incl';
5645 hr_utility.set_location(' Entering:'||l_proc, 5);
5646 end if;
5647 --
5648 for i in 1..g_cwb_pl_list.count
5649 loop
5650 if p_pl_id = g_cwb_pl_list(i) then
5651 open c1 (to_number(g_cwb_prd_list(i))) ;
5652 fetch c1 into l_ASND_LF_EVT_DT ;
5653 close c1 ;
5654 if l_ASND_LF_EVT_DT = p_LF_EVT_OCRD_DT then
5655 if p_excld_flag = 'N' then
5656 raise g_include;
5657 else
5658 raise g_not_include;
5659 end if;
5660 end if;
5661 end if ;
5662 end loop;
5663 --
5664 if p_excld_flag = 'Y' then
5665 raise g_include;
5666 end if;
5667 --
5668 if g_debug then
5669 hr_utility.set_location(' Exiting:'||l_proc, 15);
5670 end if;
5671 --
5672 --
5673 raise g_not_include;
5674 --
5675 Exception
5676 --
5677 when g_include then
5678 null;
5679 --
5680 when g_not_include then
5681 raise g_not_include; --- will be handled in the calling program
5682 --
5683 End chk_cwb_pl_prd_incl;
5684
5685
5686
5687
5688
5689 ---- subheader
5690
5691
5692 Procedure chk_subhead_pos_incl
5693 (p_position_id in number
5694 ,p_excld_flag in varchar2)
5695 is
5696 --
5697 l_proc varchar2(72);
5698 Begin
5699 --
5700 if g_debug then
5701 l_proc := g_package||'chk_subhead_pos_incl';
5702 hr_utility.set_location(' Entering:'||l_proc, 5);
5703 end if;
5704 -- if p_position_id is null then error, it must be passed in!
5705 --
5706 for i in 1..g_subhead_pos_list.count
5707 loop
5708 if p_position_id = g_subhead_pos_list(i) then
5709 if p_excld_flag = 'N' then
5710 raise g_include;
5711 else
5712 raise g_not_include;
5713 end if;
5714 end if;
5715 end loop;
5716 if p_excld_flag = 'Y' then
5717 raise g_include;
5718 end if;
5719 if g_debug then
5720 hr_utility.set_location(' Exiting:'||l_proc, 15);
5721 end if;
5722 raise g_not_include;
5723 --
5724 Exception
5725 when g_include then
5726 null;
5727 when g_not_include then
5728 raise g_not_include; --- will be handled in the calling program
5729 End chk_subhead_pos_incl;
5730 --
5731
5732 Procedure chk_subhead_job_incl
5733 (p_job_id in number
5734 ,p_excld_flag in varchar2)
5735 is
5736 --
5737 l_proc varchar2(72);
5738 Begin
5739 --
5740 if g_debug then
5741 l_proc := g_package||'chk_subhead_job_incl';
5742 hr_utility.set_location(' Entering:'||l_proc, 5);
5743 end if;
5744 for i in 1..g_subhead_job_list.count
5745 loop
5746 if p_job_id = g_subhead_job_list(i) then
5747 if p_excld_flag = 'N' then
5748 raise g_include;
5749 else
5750 raise g_not_include;
5751 end if;
5752 end if;
5753 end loop;
5754 if p_excld_flag = 'Y' then
5755 raise g_include;
5756 end if;
5757 if g_debug then
5758 hr_utility.set_location(' Exiting:'||l_proc, 15);
5759 end if;
5760 raise g_not_include;
5761 --
5762 Exception
5763 when g_include then
5764 null;
5765 when g_not_include then
5766 raise g_not_include; --- will be handled in the calling program
5767 End chk_subhead_job_incl;
5768
5769
5770
5771 Procedure chk_subhead_loc_incl
5772 (p_location_id in number
5773 ,p_excld_flag in varchar2)
5774 is
5775 --
5776 l_proc varchar2(72);
5777 Begin
5778 --
5779 if g_debug then
5780 l_proc := g_package||'chk_subhead_loc_incl';
5781 hr_utility.set_location(' Entering:'||l_proc, 5);
5782 end if;
5783 for i in 1..g_subhead_loc_list.count
5784 loop
5785 if p_location_id = g_subhead_loc_list(i) then
5786 if p_excld_flag = 'N' then
5787 raise g_include;
5788 else
5789 raise g_not_include;
5790 end if;
5791 end if;
5792 end loop;
5793 if p_excld_flag = 'Y' then
5794 raise g_include;
5795 end if;
5796 if g_debug then
5797 hr_utility.set_location(' Exiting:'||l_proc, 15);
5798 end if;
5799 raise g_not_include;
5800 --
5801 Exception
5802 when g_include then
5803 null;
5804 when g_not_include then
5805 raise g_not_include; --- will be handled in the calling program
5806 End chk_subhead_loc_incl;
5807
5808
5809 Procedure chk_subhead_pay_incl
5810 (p_payroll_id in number
5811 ,p_excld_flag in varchar2)
5812 is
5813 --
5814 l_proc varchar2(72);
5815 Begin
5816 --
5817 if g_debug then
5818 l_proc := g_package||'chk_subhead_pay_incl';
5819 hr_utility.set_location(' Entering:'||l_proc, 5);
5820 end if;
5821 for i in 1..g_subhead_pay_list.count
5822 loop
5823 if p_payroll_id = g_subhead_pay_list(i) then
5824 if p_excld_flag = 'N' then
5825 raise g_include;
5826 else
5827 raise g_not_include;
5828 end if;
5829 end if;
5830 end loop;
5831 if p_excld_flag = 'Y' then
5832 raise g_include;
5833 end if;
5834 if g_debug then
5835 hr_utility.set_location(' Exiting:'||l_proc, 15);
5836 end if;
5837 raise g_not_include;
5838 --
5839 Exception
5840 when g_include then
5841 null;
5842 when g_not_include then
5843 raise g_not_include; --- will be handled in the calling program
5844 End chk_subhead_pay_incl;
5845
5846
5847 Procedure chk_subhead_org_incl
5848 (p_organization_id in number
5849 ,p_excld_flag in varchar2)
5850 is
5851 --
5852 l_proc varchar2(72);
5853 Begin
5854 --
5855 if g_debug then
5856 l_proc := g_package||'chk_subhead_org_incl';
5857 hr_utility.set_location(' Entering:'||l_proc, 5);
5858 end if;
5859 for i in 1..g_subhead_org_list.count
5860 loop
5861 if p_organization_id = g_subhead_org_list(i) then
5862 if p_excld_flag = 'N' then
5863 raise g_include;
5864 else
5865 raise g_not_include;
5866 end if;
5867 end if;
5868 end loop;
5869 if p_excld_flag = 'Y' then
5870 raise g_include;
5871 end if;
5872 if g_debug then
5873 hr_utility.set_location(' Exiting:'||l_proc, 15);
5874 end if;
5875 raise g_not_include;
5876 --
5877 Exception
5878 when g_include then
5879 null;
5880 when g_not_include then
5881 raise g_not_include; --- will be handled in the calling program
5882 End chk_subhead_org_incl;
5883
5884
5885 Procedure chk_subhead_bg_incl
5886 (p_business_group_id in number
5887 ,p_excld_flag in varchar2)
5888 is
5889 --
5890 l_proc varchar2(72);
5891 Begin
5892 --
5893 if g_debug then
5894 l_proc := g_package||'chk_subhead_bg_incl';
5895 hr_utility.set_location(' Entering:'||l_proc, 5);
5896 end if;
5897 for i in 1..g_subhead_bg_list.count
5898 loop
5899 if p_business_group_id = g_subhead_bg_list(i) then
5900 if p_excld_flag = 'N' then
5901 raise g_include;
5902 else
5903 raise g_not_include;
5904 end if;
5905 end if;
5906 end loop;
5907 if p_excld_flag = 'Y' then
5908 raise g_include;
5909 end if;
5910 if g_debug then
5911 hr_utility.set_location(' Exiting:'||l_proc, 15);
5912 end if;
5913 raise g_not_include;
5914 --
5915 Exception
5916 when g_include then
5917 null;
5918 when g_not_include then
5919 raise g_not_include; --- will be handled in the calling program
5920 End chk_subhead_bg_incl;
5921
5922
5923 Procedure chk_subhead_grd_incl
5924 (p_grade_id in number
5925 ,p_excld_flag in varchar2)
5926 is
5927 --
5928 l_proc varchar2(72);
5929 Begin
5930 --
5931 if g_debug then
5932 l_proc := g_package||'chk_subhead_grd_incl';
5933 hr_utility.set_location(' Entering:'||l_proc, 5);
5934 end if;
5935 --
5936 for i in 1..g_subhead_grd_list.count
5937 loop
5938 if p_grade_id = g_subhead_grd_list(i) then
5939 if p_excld_flag = 'N' then
5940 raise g_include;
5941 else
5942 raise g_not_include;
5943 end if;
5944 end if;
5945 end loop;
5946 if p_excld_flag = 'Y' then
5947 raise g_include;
5948 end if;
5949 if g_debug then
5950 hr_utility.set_location(' Exiting:'||l_proc, 15);
5951 end if;
5952 raise g_not_include;
5953 --
5954 Exception
5955 when g_include then
5956 null;
5957 when g_not_include then
5958 raise g_not_include; --- will be handled in the calling program
5959 End chk_subhead_grd_incl;
5960
5961
5962 --------------------------------- chck_subhead_rule_incl ----------------------
5963
5964 Procedure chk_subhead_rule_incl
5965 (p_business_group_id in number ,
5966 p_effective_date in date,
5967 p_excld_flag in varchar2,
5968 p_param_1 in varchar2 ,
5969 p_param_val_1 in varchar2 ,
5970 p_param_2 in varchar2 ,
5971 p_param_val_2 in varchar2
5972 )
5973 is
5974 --
5975 l_outputs ff_exec.outputs_t;
5976 --
5977 l_proc varchar2(72);
5978 --
5979 Begin
5980 --
5981 if g_debug then
5982 l_proc := g_package||'chk_subhead_rule_incl';
5983 hr_utility.set_location(' Entering:'||l_proc, 5);
5984 end if;
5985 --
5986 hr_utility.set_location( p_param_2 || ' ' || p_param_val_2 , 5);
5987 for i in 1..g_subhead_rule_list.count
5988 loop
5989
5990 hr_utility.set_location( 'formula id ' || g_subhead_rule_list(i) , 5);
5991 l_outputs := benutils.formula
5992 (p_formula_id => g_subhead_rule_list(i)
5993 ,p_effective_date => p_effective_date
5994 ,p_business_group_id => p_business_group_id
5995 ,p_param1 => 'EXT_DFN_ID'
5996 ,p_param1_value => to_char(nvl(ben_ext_thread.g_ext_dfn_id, -1))
5997 ,p_param2 => 'EXT_RSLT_ID'
5998 ,p_param2_value => to_char(nvl(ben_ext_thread.g_ext_rslt_id, -1))
5999 ,p_param3 => p_param_1
6000 ,p_param3_value => p_param_val_1
6001 ,p_param4 => p_param_2
6002 ,p_param4_value => p_param_val_2
6003 ,p_param5 => 'EXT_PROCESS_BUSINESS_GROUP'
6004 ,p_param5_value => to_char(ben_extract.g_proc_business_group_id)
6005 );
6006 hr_utility.set_location(' out of rl :'||l_outputs(l_outputs.first).value , 5);
6007 if l_outputs(l_outputs.first).value = 'Y' then
6008 if p_excld_flag = 'N' then
6009 raise g_include;
6010 else
6011 raise g_not_include;
6012 end if;
6013 end if;
6014 end loop;
6015 --
6016 if p_excld_flag = 'Y' then
6017 raise g_include;
6018 end if;
6019 --
6020 if g_debug then
6021 hr_utility.set_location(' Exiting:'||l_proc, 15);
6022 end if;
6023 --
6024 raise g_not_include;
6025 --
6026 Exception
6027 --
6028 when g_include then
6029 null;
6030 --
6031 when g_not_include then
6032 raise g_not_include; --- will be handled in the calling program
6033 --
6034 End chk_subhead_rule_incl;
6035 --
6036
6037
6038 --------------------------------- chck_payroll_rule_incl ----------------------
6039
6040 Procedure chk_payroll_rule_incl
6041 (p_business_group_id in number ,
6042 p_person_id in number ,
6043 p_effective_date in date,
6044 p_excld_flag in varchar2,
6045 p_input_value_id in number ,
6046 p_processing_type in varchar2 ,
6047 p_element_type_id in number ,
6048 p_source_id in number ,
6049 p_source_type in varchar2 ,
6050 p_element_entry_id in number
6051 )
6052 is
6053 --
6054 cursor c_asg is
6055 SELECT asg.assignment_id, asg.business_group_id
6056 FROM per_all_assignments_f asg
6057 WHERE asg.person_id = p_person_id
6058 and asg.assignment_id = ben_ext_person.g_assignment_id --1969853
6059 AND asg.primary_flag = 'Y'
6060 AND p_effective_date between asg.effective_start_date
6061 and asg.effective_end_date;
6062 --
6063 l_asg c_asg%rowtype;
6064
6065 l_outputs ff_exec.outputs_t;
6066 --
6067 l_proc varchar2(72);
6068 --
6069 Begin
6070 --
6071 if g_debug then
6072 l_proc := g_package||'chk_payroll_rule_incl';
6073 hr_utility.set_location(' Entering:'||l_proc, 5);
6074 end if;
6075
6076 --
6077 open c_asg;
6078 fetch c_asg into l_asg;
6079 close c_asg;
6080 --
6081
6082 --
6083 for i in 1..g_payroll_rl_list.count
6084 loop
6085
6086
6087 hr_utility.set_location( 'formula id ' || g_payroll_rl_list(i) , 5);
6088 hr_utility.set_location( 'asg id ' || l_asg.assignment_id , 5);
6089 hr_utility.set_location( 'bg id ' || p_business_group_id , 5);
6090
6091 l_outputs := benutils.formula
6092 (p_formula_id => g_payroll_rl_list(i)
6093 ,p_effective_date => p_effective_date
6094 ,p_assignment_id => l_asg.assignment_id
6095 ,p_business_group_id => p_business_group_id
6096 ,p_param1 => 'EXT_DFN_ID'
6097 ,p_param1_value => to_char(nvl(ben_ext_thread.g_ext_dfn_id, -1))
6098 ,p_param2 => 'EXT_RSLT_ID'
6099 ,p_param2_value => to_char(nvl(ben_ext_thread.g_ext_rslt_id, -1))
6100 ,p_param3 => 'EXT_PAY_INPUT_VALUE'
6101 ,p_param3_value => to_char(p_input_value_id)
6102 ,p_param4 => 'EXT_PAY_PROCESSING_TYPE'
6103 ,p_param4_value => p_processing_type
6104 ,p_param5 => 'EXT_PAY_ELEMENT_TYPE'
6105 ,p_param5_value => to_char(p_element_type_id)
6106 ,p_param6 => 'EXT_PAY_ENTRY_SOURCE'
6107 ,p_param6_value => to_char(p_source_id)
6108 ,p_param7 => 'EXT_PAY_ENTRY_SOURCE_TYPE'
6109 ,p_param7_value => p_source_type
6110 ,p_param8 => 'EXT_PAY_ELEMENT_ENTRY'
6111 ,p_param8_value => to_char(p_element_entry_id )
6112 ,p_param9 => 'EXT_PROCESS_BUSINESS_GROUP'
6113 ,p_param9_value => to_char(ben_extract.g_proc_business_group_id)
6114 );
6115 hr_utility.set_location(' out of rl :'||l_outputs(l_outputs.first).value , 5);
6116 if l_outputs(l_outputs.first).value = 'Y' then
6117 if p_excld_flag = 'N' then
6118 raise g_include;
6119 else
6120 raise g_not_include;
6121 end if;
6122 end if;
6123 end loop;
6124 --
6125 if p_excld_flag = 'Y' then
6126 raise g_include;
6127 end if;
6128 --
6129 if g_debug then
6130 hr_utility.set_location(' Exiting:'||l_proc, 15);
6131 end if;
6132 --
6133 --
6134 raise g_not_include;
6135 --
6136 Exception
6137 --
6138 when g_include then
6139 null;
6140 --
6141 when g_not_include then
6142 raise g_not_include; --- will be handled in the calling program
6143 --
6144 End chk_payroll_rule_incl;
6145 --
6146
6147
6148 --
6149 -----------------------------------------------------------------------------
6150 ---------------------------< evaluate_person_incl >--------------------------
6151 -----------------------------------------------------------------------------
6152 --
6153 -- The following procedure evaluates person inclusion criteria
6154 --
6155 Procedure evaluate_person_incl
6156 (p_person_id in per_all_people_f.person_id%type,
6157 p_postal_code in per_addresses.postal_code%type default null,
6158 p_org_id in per_all_assignments_f.organization_id%type default null,
6159 p_loc_id in per_all_assignments_f.location_id%type default null,
6160 p_gre in hr_soft_coding_keyflex.segment1%type default null,
6161 p_state in per_addresses.region_2%type default null,
6162 p_bnft_grp in per_all_people_f.benefit_group_id%type default null,
6163 p_ee_status in per_all_assignments_f.assignment_status_type_id%type default null,
6164 p_payroll_id in per_all_assignments_f.payroll_id%type default null,
6165 p_chg_evt_cd in varchar2 default null,
6166 p_chg_evt_source in varchar2 default null,
6167 p_effective_date in date,
6168 --RCHASE
6169 p_eff_date in date default null,
6170 --End RCHASE
6171 p_actl_date in date,
6172 p_include out nocopy varchar2)
6173 is
6174 --
6175 l_proc varchar2(72);
6176 --
6177 Begin
6178 --
6179 g_debug := hr_utility.debug_enabled;
6180 if g_debug then
6181 l_proc := g_package||'evaluate_person_incl';
6182 hr_utility.set_location(' Entering:'||l_proc, 5);
6183 end if;
6184 --
6185 p_include := 'Y';
6186 --
6187 if g_person_id_incl_rqd = 'Y' then
6188 Chk_Person_Id_Incl
6189 (p_person_id,
6190 g_person_id_excld_flag);
6191 end if;
6192 if g_postal_code_incl_rqd = 'Y' then
6193 Chk_Postal_Code_Incl
6194 (p_person_id,
6195 p_postal_code,
6196 p_effective_date,
6197 g_postal_code_excld_flag);
6198 end if;
6199 if g_org_id_incl_rqd = 'Y' then
6200 Chk_Org_Id_Incl
6201 (p_person_id,
6202 p_org_id,
6203 p_effective_date,
6204 g_org_id_excld_flag);
6205 end if;
6206 if g_loc_id_incl_rqd = 'Y' then
6207 Chk_Loc_Id_Incl
6208 (p_person_id,
6209 p_loc_id,
6210 p_effective_date,
6211 g_loc_id_excld_flag);
6212 end if;
6213 if g_gre_incl_rqd = 'Y' then
6214 Chk_Gre_Incl
6215 (p_person_id,
6216 p_gre,
6217 p_effective_date,
6218 g_gre_excld_flag);
6219 end if;
6220 if g_state_incl_rqd = 'Y' then
6221 Chk_State_Incl
6222 (p_person_id,
6223 p_state,
6224 p_effective_date,
6225 g_state_excld_flag);
6226 end if;
6227 if g_bnft_grp_incl_rqd = 'Y' then
6228 Chk_bnft_grp_Incl
6229 (p_person_id,
6230 p_bnft_grp,
6231 p_effective_date,
6232 g_bnft_grp_excld_flag);
6233 end if;
6234 if g_ee_status_incl_rqd = 'Y' then
6235 Chk_Ee_Status_Incl
6236 (p_person_id,
6237 p_ee_status,
6238 p_effective_date,
6239 g_ee_status_excld_flag);
6240 end if;
6241 if g_payroll_id_incl_rqd = 'Y' then
6242 Chk_payroll_id_Incl
6243 (p_person_id,
6244 p_payroll_id,
6245 p_effective_date,
6246 g_payroll_id_excld_flag);
6247 end if;
6248 if g_person_rule_incl_rqd = 'Y' then
6249 Chk_Person_Rule_Incl
6250 (p_person_id,
6251 p_effective_date,
6252 g_person_rule_excld_flag);
6253 end if;
6254 if g_per_ler_incl_rqd = 'Y' then
6255 Chk_Per_Ler_Incl
6256 (p_person_id,
6257 p_effective_date,
6258 g_per_ler_excld_flag);
6259 end if;
6260 if g_person_type_incl_rqd = 'Y' then
6261 Chk_Person_Type_Incl
6262 (p_person_id,
6263 p_effective_date,
6264 g_person_type_excld_flag);
6265 end if;
6266 --
6267 /* if g_per_plan_incl_rqd = 'Y'
6268 and p_chg_evt_cd is null then
6269 Evaluate_Per_Plan_Incl
6270 (p_person_id => p_person_id,
6271 p_effective_date => p_effective_date,
6272 p_excld_flag => g_per_plan_excld_flag);
6273 end if; */
6274 --
6275 /* if g_rptg_grp_incl_rqd = 'Y'
6276 and p_chg_evt_cd is null then
6277 Evaluate_Per_Rptg_Grp_Incl
6278 (p_person_id => p_person_id,
6279 p_effective_date => p_effective_date,
6280 p_excld_flag => g_rptg_grp_excld_flag);
6281 end if; */
6282 --
6283 /* if g_ele_input_incl_rqd = 'Y' then
6284 Evaluate_Per_Elm_Entry_Incl
6285 (p_person_id => p_person_id,
6286 p_effective_date => p_effective_date,
6287 p_excld_flag => g_ele_input_excld_flag);
6288 end if; */
6289 --
6290 if g_cmbn_incl_rqd = 'Y' /*and p_chg_evt_cd is not null */ then
6291 Chk_Cmbn_Incl
6292 (p_person_id => p_person_id,
6293 p_chg_evt_cd => p_chg_evt_cd,
6294 p_chg_evt_source => p_chg_evt_source,
6295 --RCHASE
6296 p_effective_date => nvl(p_eff_date,p_effective_date), -- p_effective_date
6297 --End RCHASE
6298 p_actl_date => p_actl_date);
6299 end if;
6300 --
6301 if g_debug then
6302 hr_utility.set_location(' Exiting:'||l_proc, 15);
6303 end if;
6304 --
6305 Exception
6306 --
6307 when g_not_include then
6308 p_include := 'N';
6309 --
6310 End Evaluate_Person_Incl;
6311 --
6312 -----------------------------------------------------------------------------
6313 ---------------------------< evaluate_benefit_incl >--------------------------
6314 -----------------------------------------------------------------------------
6315 --
6316 -- The following procedure evaluates person inclusion criteria
6317 --
6318 Procedure evaluate_benefit_incl
6319 (p_pl_id in ben_prtt_enrt_rslt_f.pl_id%type default null,
6320 p_sspndd_flag in ben_prtt_enrt_rslt_f.sspndd_flag%type default null,
6321 p_enrt_cvg_strt_dt in ben_prtt_enrt_rslt_f.enrt_cvg_strt_dt%type default null,
6322 p_enrt_cvg_thru_dt in ben_prtt_enrt_rslt_f.enrt_cvg_thru_dt%type default null,
6323 p_prtt_enrt_rslt_stat_cd in ben_prtt_enrt_rslt_f.prtt_enrt_rslt_stat_cd%type default null,
6324 p_enrt_mthd_cd in ben_prtt_enrt_rslt_f.enrt_mthd_cd%type default null,
6325 p_pgm_id in ben_prtt_enrt_rslt_f.pgm_id%type default null,
6326 p_pl_typ_id in ben_prtt_enrt_rslt_f.pl_typ_id%type default null,
6327 p_opt_id in ben_opt_f.opt_id%type default null,
6328 p_last_update_date in ben_prtt_enrt_rslt_f.last_update_date%type default null,
6329 p_ler_id in ben_per_in_ler.ler_id%type default null,
6330 p_ntfn_dt in ben_per_in_ler.ntfn_dt%type default null,
6331 p_lf_evt_ocrd_dt in ben_per_in_ler.lf_evt_ocrd_dt%type default null,
6332 p_per_in_ler_stat_cd in ben_per_in_ler.per_in_ler_stat_cd%type default null,
6333 p_per_in_ler_id in ben_per_in_ler.per_in_ler_id%type default null,
6334 p_prtt_enrt_rslt_id in ben_prtt_enrt_rslt_f.prtt_enrt_rslt_id%type default null,
6335 p_effective_date in date default null,
6336 p_dpnt_id in number default null,
6337 p_include out nocopy varchar2)
6338 is
6339 --
6340 l_proc varchar2(72);
6341 --
6342 Begin
6343 --
6344 g_debug := hr_utility.debug_enabled;
6345 if g_debug then
6346 l_proc := g_package||'evaluate_benefit_incl';
6347 hr_utility.set_location(' Entering:'||l_proc, 5);
6348 end if;
6349 --
6350 p_include := 'Y';
6351 --
6352 if g_enrt_plan_incl_rqd = 'Y' then
6353 chk_enrt_plan_incl
6354 (p_pl_id => p_pl_id,
6355 p_excld_flag => g_enrt_plan_excld_flag);
6356 end if;
6357 if g_enrt_opt_incl_rqd = 'Y' then
6358 chk_enrt_opt_incl
6359 (p_opt_id => p_opt_id,
6360 p_excld_flag => g_enrt_opt_excld_flag);
6361 end if;
6362
6363 if g_enrt_rg_plan_incl_rqd = 'Y' then
6364 chk_enrt_rg_plan_incl
6365 (p_pl_id => p_pl_id,
6366 p_excld_flag => g_enrt_rg_plan_excld_flag);
6367 end if;
6368 if g_enrt_sspndd_incl_rqd = 'Y' then
6369 chk_enrt_sspndd_incl
6370 (p_sspndd_flag => p_sspndd_flag,
6371 p_excld_flag => g_enrt_sspndd_excld_flag);
6372 end if;
6373 if g_enrt_cvg_strt_dt_incl_rqd = 'Y' then
6374 chk_enrt_cvg_strt_dt_incl
6375 (p_enrt_cvg_strt_dt => p_enrt_cvg_strt_dt,
6376 p_effective_date => p_effective_date,
6377 p_excld_flag => g_enrt_cvg_strt_dt_excld_flag,
6378 p_pl_id => p_pl_id );
6379 end if;
6380 if g_enrt_cvg_drng_perd_incl_rqd = 'Y' then
6381 chk_enrt_cvg_drng_perd_incl
6382 (p_enrt_cvg_strt_dt => p_enrt_cvg_strt_dt,
6383 p_enrt_cvg_thru_dt => p_enrt_cvg_thru_dt,
6384 p_effective_date => p_effective_date,
6385 p_excld_flag => g_enrt_cvg_drng_prd_excld_flag,
6386 p_pl_id => p_pl_id );
6387 end if;
6388 if g_enrt_stat_incl_rqd = 'Y' then
6389 chk_enrt_stat_incl
6390 (p_prtt_enrt_rslt_stat_cd => p_prtt_enrt_rslt_stat_cd,
6391 p_excld_flag => g_enrt_stat_excld_flag);
6392 end if;
6393 if g_enrt_mthd_incl_rqd = 'Y' then
6394 chk_enrt_mthd_incl
6395 (p_enrt_mthd_cd => p_enrt_mthd_cd,
6396 p_excld_flag => g_enrt_mthd_excld_flag);
6397 end if;
6398 if g_enrt_pgm_incl_rqd = 'Y' then
6399 chk_enrt_pgm_incl
6400 (p_pgm_id => p_pgm_id,
6401 p_excld_flag => g_enrt_pgm_excld_flag);
6402 end if;
6403 if g_enrt_pl_typ_incl_rqd = 'Y' then
6404 chk_enrt_pl_typ_incl
6405 (p_pl_typ_id => p_pl_typ_id,
6406 p_excld_flag => g_enrt_pl_typ_excld_flag);
6407 end if;
6408 if g_enrt_last_upd_dt_incl_rqd = 'Y' then
6409 chk_enrt_last_upd_dt_incl
6410 (p_last_update_date => p_last_update_date,
6411 p_effective_date => p_effective_date,
6412 p_excld_flag => g_enrt_last_upd_dt_excld_flag ,
6413 p_pl_id => p_pl_id );
6414 end if;
6415 if g_enrt_ler_name_incl_rqd = 'Y' then
6416 chk_enrt_ler_name_incl
6417 (p_ler_id => p_ler_id,
6418 p_excld_flag => g_enrt_ler_name_excld_flag);
6419 end if;
6420 if g_enrt_ler_stat_incl_rqd = 'Y' then
6421 chk_enrt_ler_stat_incl
6422 (p_per_in_ler_stat_cd => p_per_in_ler_stat_cd,
6423 p_excld_flag => g_enrt_ler_stat_excld_flag);
6424 end if;
6425 if g_enrt_ler_ocrd_dt_incl_rqd = 'Y' then
6426 chk_enrt_ler_ocrd_dt_incl
6427 (p_lf_evt_ocrd_dt => p_lf_evt_ocrd_dt,
6428 p_effective_date => p_effective_date,
6429 p_excld_flag => g_enrt_ler_ocrd_dt_excld_flag ,
6430 p_pl_id => p_pl_id );
6431 end if;
6432 if g_enrt_ler_ntfn_dt_incl_rqd = 'Y' then
6433 chk_enrt_ler_ntfn_dt_incl
6434 (p_ntfn_dt => p_ntfn_dt,
6435 p_effective_date => p_effective_date,
6436 p_excld_flag => g_enrt_ler_ntfn_dt_excld_flag,
6437 p_pl_id => p_pl_id );
6438 end if;
6439 if g_enrt_rltn_incl_rqd = 'Y' then
6440 chk_enrt_rltn_incl
6441 (p_per_in_ler_id => p_per_in_ler_id,
6442 p_prtt_enrt_rslt_id => p_prtt_enrt_rslt_id,
6443 p_pl_id => p_pl_id,
6444 p_pl_typ_id => p_pl_typ_id,
6445 p_pgm_id => p_pgm_id,
6446 p_effective_date => p_effective_date,
6447 p_excld_flag => g_enrt_rltn_excld_flag);
6448 end if;
6449 --
6450 if g_enrt_dpnt_rltn_incl_rqd = 'Y'
6451 and p_dpnt_id is not null then
6452 chk_enrt_dpnt_rltn_incl
6453 (p_per_in_ler_id => p_per_in_ler_id,
6454 p_prtt_enrt_rslt_id => p_prtt_enrt_rslt_id,
6455 p_dpnt_person_id => p_dpnt_id,
6456 p_effective_date => p_effective_date,
6457 p_excld_flag => g_enrt_dpnt_rltn_excld_flag);
6458 end if;
6459
6460 if g_debug then
6461 hr_utility.set_location(' Exiting:'||l_proc, 15);
6462 end if;
6463 --
6464 Exception
6465 --
6466 when g_not_include then
6467 p_include := 'N';
6468 --
6469 End Evaluate_benefit_Incl;
6470 --
6471 -----------------------------------------------------------------------------
6472 ---------------------------< evaluate_eligibility_incl >--------------------------
6473 -----------------------------------------------------------------------------
6474 --
6475 -- The following procedure evaluates person inclusion criteria
6476 --
6477 Procedure evaluate_eligibility_incl
6478 (p_elct_pl_id in ben_elig_per_elctbl_chc.pl_id%type default null,
6479 p_elct_enrt_strt_dt in ben_elig_per_elctbl_chc.enrt_cvg_strt_dt%type default null,
6480 p_elct_yrprd_id in ben_elig_per_elctbl_chc.yr_perd_id%type default null,
6481 p_elct_pgm_id in ben_elig_per_elctbl_chc.pgm_id%type default null,
6482 p_elct_pl_typ_id in ben_elig_per_elctbl_chc.pl_typ_id%type default null,
6483 p_elct_opt_id in ben_opt_f.opt_id%type default null,
6484 p_elct_last_upd_dt in ben_elig_per_elctbl_chc.last_update_date%type default null,
6485 p_elct_per_in_ler_id in ben_per_in_ler.per_in_ler_id%type default null,
6486 p_elct_ler_id in ben_per_in_ler.ler_id%type default null,
6487 p_elct_per_in_ler_stat_cd in ben_per_in_ler.per_in_ler_stat_cd%type default null,
6488 p_elct_lf_evt_ocrd_dt in ben_per_in_ler.lf_evt_ocrd_dt%type default null,
6489 p_elct_ntfn_dt in ben_per_in_ler.ntfn_dt%type default null,
6490 p_prtt_enrt_rslt_id in ben_elig_per_elctbl_chc.prtt_enrt_rslt_id%type default null,
6491 p_effective_date in date default null,
6492 p_include out nocopy varchar2)
6493
6494 is
6495 --
6496 l_proc varchar2(72);
6497 --
6498 Begin
6499 --
6500 g_debug := hr_utility.debug_enabled;
6501 if g_debug then
6502 l_proc := g_package||'evaluate_eligibility_incl';
6503 hr_utility.set_location(' Entering:'||l_proc, 5);
6504 end if;
6505 --
6506 p_include := 'Y';
6507 --
6508 if g_elct_plan_incl_rqd = 'Y' then
6509 chk_elct_plan_incl
6510 (p_pl_id => p_elct_pl_id,
6511 p_excld_flag => g_elct_plan_excld_flag);
6512 end if;
6513
6514 --
6515 if g_debug then
6516 hr_utility.set_location(' opt incl :'||g_elct_opt_excld_flag || p_elct_opt_id, 5);
6517 end if;
6518
6519 if g_elct_opt_incl_rqd = 'Y' then
6520 chk_elct_opt_incl
6521 (p_opt_id => p_elct_opt_id,
6522 p_excld_flag => g_elct_opt_excld_flag);
6523 end if;
6524 if g_debug then
6525 hr_utility.set_location(' opt incl :'||g_elct_opt_incl_rqd, 6);
6526 end if;
6527
6528 if g_elct_rg_plan_incl_rqd = 'Y' then
6529 chk_elct_rg_plan_incl
6530 (p_pl_id => p_elct_pl_id,
6531 p_excld_flag => g_elct_rg_plan_excld_flag);
6532 end if;
6533 if g_elct_enrt_strt_dt_incl_rqd = 'Y' then
6534 chk_elct_enrt_strt_dt_incl
6535 (p_elct_enrt_strt_dt => p_elct_enrt_strt_dt,
6536 p_effective_date => p_effective_date,
6537 p_excld_flag => g_elct_enrt_strt_dt_excld_flag ,
6538 p_pl_id => p_elct_pl_id );
6539 end if;
6540 if g_elct_pgm_incl_rqd = 'Y' then
6541 chk_elct_pgm_incl
6542 (p_pgm_id => p_elct_pgm_id,
6543 p_excld_flag => g_elct_pgm_excld_flag);
6544 end if;
6545 if g_elct_pl_typ_incl_rqd = 'Y' then
6546 chk_elct_pl_typ_incl
6547 (p_pl_typ_id => p_elct_pl_typ_id,
6548 p_excld_flag => g_elct_pl_typ_excld_flag);
6549 end if;
6550 if g_elct_last_upd_dt_incl_rqd = 'Y' then
6551 chk_elct_last_upd_dt_incl
6552 (p_last_update_date => p_elct_last_upd_dt,
6553 p_effective_date => p_effective_date,
6554 p_excld_flag => g_elct_last_upd_dt_excld_flag,
6555 p_pl_id => p_elct_pl_id );
6556 end if;
6557 if g_elct_ler_name_incl_rqd = 'Y' then
6558 chk_elct_ler_name_incl
6559 (p_ler_id => p_elct_ler_id,
6560 p_excld_flag => g_elct_ler_name_excld_flag);
6561 end if;
6562 if g_elct_ler_stat_incl_rqd = 'Y' then
6563 chk_elct_ler_stat_incl
6564 (p_per_in_ler_stat_cd => p_elct_per_in_ler_stat_cd,
6565 p_excld_flag => g_elct_ler_stat_excld_flag);
6566 end if;
6567 if g_elct_yrprd_incl_rqd = 'Y' then
6568 chk_elct_yrprd_incl
6569 (p_yrprd_id => p_elct_yrprd_id,
6570 p_excld_flag => g_elct_yrprd_excld_flag);
6571 end if;
6572 if g_elct_ler_ocrd_dt_incl_rqd = 'Y' then
6573 chk_elct_ler_ocrd_dt_incl
6574 (p_lf_evt_ocrd_dt => p_elct_lf_evt_ocrd_dt,
6575 p_effective_date => p_effective_date,
6576 p_excld_flag => g_elct_ler_ocrd_dt_excld_flag,
6577 p_pl_id => p_elct_pl_id );
6578 end if;
6579 if g_elct_ler_ntfn_dt_incl_rqd = 'Y' then
6580 chk_elct_ler_ntfn_dt_incl
6581 (p_ntfn_dt => p_elct_ntfn_dt,
6582 p_effective_date => p_effective_date,
6583 p_excld_flag => g_elct_ler_ntfn_dt_excld_flag,
6584 p_pl_id => p_elct_pl_id );
6585 end if;
6586 if g_elct_rltn_incl_rqd = 'Y' then
6587 chk_elct_rltn_incl
6588 (p_per_in_ler_id => p_elct_per_in_ler_id,
6589 p_prtt_enrt_rslt_id => p_prtt_enrt_rslt_id,
6590 p_excld_flag => g_elct_rltn_excld_flag);
6591 end if;
6592 --
6593 if g_debug then
6594 hr_utility.set_location(' Exiting:'||l_proc, 15);
6595 end if;
6596 --
6597 Exception
6598 --
6599 when g_not_include then
6600 p_include := 'N';
6601 --
6602 End evaluate_eligibility_incl;
6603 --
6604 -----------------------------------------------------------------------------
6605 ------------------------< evaluate_change_log_incl >--------------------------
6606 -----------------------------------------------------------------------------
6607 --
6608 -- The following procedure evaluates the extract change log inclusion criteria
6609 --
6610 Procedure evaluate_change_log_incl
6611 (p_chg_evt_cd in ben_ext_chg_evt_log.chg_evt_cd%type,
6612 p_chg_evt_source in ben_ext_chg_evt_log.chg_evt_cd%type,
6613 p_chg_eff_dt in ben_ext_chg_evt_log.chg_eff_dt%type,
6614 p_chg_actl_dt in ben_ext_chg_evt_log.chg_actl_dt%type,
6615 p_last_update_login in ben_ext_chg_evt_log.last_update_login%type,
6616 p_effective_date in date default null,
6617 p_include out nocopy varchar2)
6618 is
6619 --
6620 l_proc varchar2(72);
6621 --
6622 Begin
6623 --
6624 g_debug := hr_utility.debug_enabled;
6625 if g_debug then
6626 l_proc := g_package||'evaluate_change_log_incl';
6627 hr_utility.set_location(' Entering:'||l_proc, 5);
6628 end if;
6629 --
6630 p_include := 'Y';
6631 --
6632 if g_chg_evt_incl_rqd = 'Y' then
6633 chk_chg_evt_cd_incl
6634 (p_chg_evt_cd => p_chg_evt_cd,
6635 p_chg_evt_source => p_chg_evt_source,
6636 p_excld_flag => g_chg_evt_excld_flag);
6637 end if;
6638 if g_chg_eff_dt_incl_rqd = 'Y' then
6639 chk_chg_eff_dt_incl
6640 (p_chg_eff_dt => p_chg_eff_dt,
6641 p_effective_date => p_effective_date,
6642 p_excld_flag => g_chg_eff_dt_excld_flag);
6643 end if;
6644 -- for payroll dont validate the actual date without parameter date
6645 if g_chg_actl_dt_incl_rqd = 'Y' and ( p_chg_evt_source = 'BEN' or p_chg_actl_dt is not null ) then
6646 chk_chg_actl_dt_incl
6647 (p_chg_actl_dt => p_chg_actl_dt,
6648 p_effective_date => p_effective_date,
6649 p_excld_flag => g_chg_actl_dt_excld_flag);
6650 end if;
6651 -- for payroll dont validate the actual date without last_update_login
6652 if g_chg_login_incl_rqd = 'Y' and ( p_chg_evt_source = 'BEN' or p_last_update_login is not null ) then
6653 chk_chg_login_incl
6654 (p_last_update_login => p_last_update_login,
6655 p_excld_flag => g_chg_login_excld_flag);
6656 end if;
6657 --
6658 if g_debug then
6659 hr_utility.set_location(' Exiting:'||l_proc, 15);
6660 end if;
6661 --
6662 Exception
6663 --
6664 when g_not_include then
6665 p_include := 'N';
6666 --
6667 End evaluate_change_log_incl;
6668 --
6669 -----------------------------------------------------------------------------
6670 ------------------------< evaluate_comm_incl >--------------------------
6671 -----------------------------------------------------------------------------
6672 --
6673 -- The following procedure evaluates the extract change log inclusion criteria
6674 --
6675 Procedure evaluate_comm_incl
6676 (p_cm_typ_id in ben_per_cm_f.cm_typ_id%type,
6677 p_last_update_date in ben_per_cm_f.last_update_date%type,
6678 p_pvdd_last_update_date in ben_per_cm_prvdd_f.last_update_date%type,
6679 p_sent_dt in ben_per_cm_prvdd_f.sent_dt%type,
6680 p_to_be_sent_dt in ben_per_cm_prvdd_f.to_be_sent_dt%type,
6681 p_effective_date in date default null,
6682 p_include out nocopy varchar2)
6683 is
6684 --
6685 l_proc varchar2(72);
6686 --
6687 Begin
6688 --
6689 g_debug := hr_utility.debug_enabled;
6690 if g_debug then
6691 l_proc := g_package||'evaluate_comm_incl';
6692 hr_utility.set_location(' Entering:'||l_proc, 5);
6693 end if;
6694 --
6695 p_include := 'Y';
6696 --
6697 if g_cm_typ_incl_rqd = 'Y' then
6698 chk_cm_typ_incl
6699 (p_cm_typ_id => p_cm_typ_id,
6700 p_excld_flag => g_cm_typ_excld_flag);
6701 end if;
6702 if g_cm_last_upd_dt_incl_rqd = 'Y' then
6703 chk_cm_last_upd_dt_incl
6704 (p_last_update_date => p_last_update_date,
6705 p_effective_date => p_effective_date,
6706 p_excld_flag => g_cm_last_upd_dt_excld_flag);
6707 end if;
6708 if g_cm_pr_last_upd_dt_incl_rqd = 'Y' then
6709 chk_cm_pr_last_upd_dt_incl
6710 (p_pvdd_last_update_date => p_pvdd_last_update_date,
6711 p_effective_date => p_effective_date,
6712 p_excld_flag => g_cm_pr_last_upd_dt_excld_flag);
6713 end if;
6714 if g_cm_sent_dt_incl_rqd = 'Y' then
6715 chk_cm_sent_dt_incl
6716 (p_sent_dt => p_sent_dt,
6717 p_effective_date => p_effective_date,
6718 p_excld_flag => g_cm_sent_dt_excld_flag);
6719 end if;
6720 if g_cm_to_be_sent_dt_incl_rqd = 'Y' then
6721 chk_cm_to_be_sent_dt_incl
6722 (p_to_be_sent_dt => p_to_be_sent_dt,
6723 p_effective_date => p_effective_date,
6724 p_excld_flag => g_cm_to_be_sent_dt_excld_flag);
6725 end if;
6726 --
6727 if g_debug then
6728 hr_utility.set_location(' Exiting:'||l_proc, 15);
6729 end if;
6730 --
6731 Exception
6732 --
6733 when g_not_include then
6734 p_include := 'N';
6735 --
6736 End evaluate_comm_incl;
6737 --
6738
6739
6740
6741
6742 -----------------------------------------------------------------------------
6743 ------------------------< evaluate_prem_incl >--------------------------
6744 -----------------------------------------------------------------------------
6745 --
6746 -- The following procedure evaluates the extract premium inclusion criteria
6747 --
6748 Procedure evaluate_prem_incl
6749 (p_last_update_date in ben_prtt_prem_by_mo_f.last_update_date%type,
6750 p_mo_num in ben_prtt_prem_by_mo_f.mo_num%type default null ,
6751 p_yr_num in ben_prtt_prem_by_mo_f.yr_num%type default null,
6752 p_effective_date in date default null,
6753 p_include out nocopy varchar2)
6754 is
6755 --
6756 l_proc varchar2(72);
6757 --
6758 Begin
6759 --
6760 g_debug := hr_utility.debug_enabled;
6761 if g_debug then
6762 l_proc := g_package||'evaluate_prem_incl';
6763 hr_utility.set_location(' Entering:'||l_proc, 5);
6764 end if;
6765 --
6766 p_include := 'Y';
6767 --
6768 if g_prem_last_updt_dt_rqd = 'Y' then
6769 chk_prem_last_upd_dt_incl
6770 (p_last_update_date => p_last_update_date,
6771 p_effective_date => p_effective_date,
6772 p_excld_flag => g_prem_last_updt_dt_excld_flag);
6773
6774 end if;
6775 if g_prem_month_year_rqd = 'Y' then
6776 chk_prem_month_year_no_incl
6777 (p_mo_num => p_mo_num,
6778 p_yr_num => p_yr_num,
6779 p_effective_date => p_effective_date,
6780 p_excld_flag => g_prem_month_year_excld_flag) ;
6781 end if;
6782 --
6783 if g_debug then
6784 hr_utility.set_location(' Exiting:'||l_proc, 15);
6785 end if;
6786 --
6787 Exception
6788 --
6789 when g_not_include then
6790 p_include := 'N';
6791 --
6792 End evaluate_prem_incl;
6793
6794
6795
6796
6797 -----------------------------------------------------------------------------
6798 ------------------------< evaluate_elm_entry_incl >--------------------------
6799 -----------------------------------------------------------------------------
6800 --
6801 -- The following procedure evaluates element entry inclusion criteria
6802 --
6803 Procedure Evaluate_Elm_Entry_Incl
6804 (p_processing_type in pay_element_types_f.processing_type%type, --future use
6805 p_input_value_id in pay_input_values_f.input_value_id%type,
6806 p_business_group_id in number ,
6807 p_pay_period_date in date default null ,
6808 p_effective_date in date default null,
6809 p_person_id in number default null,
6810 p_source_id in number default null,
6811 p_source_Type in varchar2 default null,
6812 p_element_type_id in number default null,
6813 p_element_entry_id in number default null,
6814 p_include out nocopy varchar2
6815 )
6816 is
6817 --
6818 l_proc varchar2(72);
6819 --
6820 Begin
6821 --
6822 g_debug := hr_utility.debug_enabled;
6823 if g_debug then
6824 l_proc := g_package||'evaluate_elm_entry_incl';
6825 hr_utility.set_location(' Entering:'||l_proc, 5);
6826 end if;
6827 --
6828 p_include := 'Y';
6829
6830 if g_payroll_last_date_incl_rqd = 'Y' then
6831 -- when the evaliation is called from payroll/element
6832 -- the pay period date is not sent as parameter
6833 -- validated only for run result
6834 if p_pay_period_date is not null then
6835 if g_debug then
6836 hr_utility.set_location(' calling chk_payroll_end_date_incl '||p_pay_period_date, 51);
6837 hr_utility.set_location(' effective date '||p_effective_date, 51);
6838 end if;
6839 chk_payroll_end_date_incl
6840 (p_pay_end_date => p_pay_period_date,
6841 p_effective_date => p_effective_date,
6842 p_excld_flag => g_payroll_last_Date_excld_flag );
6843 end if ;
6844
6845 end if;
6846
6847 if g_payroll_rl_incl_rqd = 'Y' then
6848
6849 chk_payroll_rule_incl
6850 (p_business_group_id => p_business_group_id ,
6851 p_person_id => p_person_id ,
6852 p_effective_date => p_effective_date,
6853 p_excld_flag => g_payroll_rl_excld_flag,
6854 p_input_value_id => p_input_value_id ,
6855 p_processing_type => p_processing_type ,
6856 p_element_type_id => p_element_type_id ,
6857 p_source_id => p_source_id ,
6858 p_source_type => p_source_type ,
6859 p_element_entry_id => p_element_entry_id
6860 ) ;
6861
6862 end if ;
6863 --
6864 if g_ele_input_incl_rqd = 'Y' then
6865 --
6866 for i in 1..g_ele_input_list.count
6867 loop
6868 if p_input_value_id = g_ele_input_list(i) then
6869 if g_ele_input_excld_flag = 'Y' then
6870 raise g_not_include;
6871 else
6872 raise g_include;
6873 end if ;
6874 end if;
6875 end loop;
6876 --
6877 if g_debug then
6878 hr_utility.set_location(' Exiting:'||l_proc, 15);
6879 end if;
6880 --
6881 if g_ele_input_excld_flag = 'Y' then
6882 raise g_include;
6883 else
6884 raise g_not_include;
6885 end if ;
6886 --
6887 end if;
6888
6889 if g_debug then
6890 hr_utility.set_location(' Exiting:'||l_proc, 15);
6891 end if;
6892 --
6893
6894 --
6895 Exception
6896 --
6897 when g_include then
6898 p_include := 'Y';
6899 --
6900 when g_not_include then
6901 p_include := 'N';
6902 --
6903 End Evaluate_Elm_Entry_Incl;
6904 --
6905 -----------------------------------------------------------------------------
6906 ------------------------< evaluate_action_item_incl >--------------------------
6907 -----------------------------------------------------------------------------
6908 --
6909 -- The following procedure evaluates action item inclusion criteria
6910 --
6911 Procedure evaluate_action_item_incl
6912 (p_actn_typ_id in ben_prtt_enrt_actn_f.actn_typ_id%type,
6913 p_prtt_enrt_actn_id in ben_prtt_enrt_actn_f.prtt_enrt_actn_id%type,
6914 p_include out nocopy varchar2)
6915 is
6916 --
6917 l_proc varchar2(72);
6918 --
6919 Begin
6920 --
6921 g_debug := hr_utility.debug_enabled;
6922 if g_debug then
6923 l_proc := g_package||'evaluate_action_item_incl';
6924 hr_utility.set_location(' Entering:'||l_proc, 5);
6925 end if;
6926 --
6927 p_include := 'Y';
6928 --
6929 if g_actn_name_incl_rqd = 'Y' then
6930 chk_actn_name_incl
6931 (p_actn_typ_id => p_actn_typ_id,
6932 p_excld_flag => g_actn_name_excld_flag);
6933 end if;
6934 if g_actn_item_rltn_incl_rqd = 'Y' then
6935 chk_actn_item_rltn_incl
6936 (p_prtt_enrt_actn_id => p_prtt_enrt_actn_id,
6937 p_excld_flag => g_actn_item_rltn_excld_flag);
6938 end if;
6939 --
6940 if g_debug then
6941 hr_utility.set_location(' Exiting:'||l_proc, 15);
6942 end if;
6943 --
6944 Exception
6945 --
6946 when g_not_include then
6947 p_include := 'N';
6948 --
6949 End Evaluate_action_item_Incl;
6950 --
6951
6952 -----------------------------------------------------------------------------
6953 ------------------------- cwb ---------------------------------------
6954
6955 Procedure Evaluate_cwb_incl
6956 (p_group_pl_id in number ,
6957 p_lf_evt_ocrd_dt in date ,
6958 p_include out nocopy varchar2 ,
6959 p_effective_date in date ) is
6960
6961 l_proc varchar2(72);
6962
6963 begin
6964
6965
6966 g_debug := hr_utility.debug_enabled;
6967 if g_debug then
6968 l_proc := g_package||'Evaluate_cwb_incl';
6969 hr_utility.set_location(' Entering:'||l_proc, 5);
6970 end if;
6971
6972 p_include := 'Y';
6973 if g_cwb_pl_prd_rqd = 'Y' then
6974 chk_cwb_pl_prd_incl
6975 (p_pl_id => p_group_pl_id ,
6976 p_LF_EVT_OCRD_DT => p_lf_evt_ocrd_dt ,
6977 p_effective_date => p_effective_date,
6978 p_excld_flag => g_cwb_pl_prd_excld_flag
6979 ) ;
6980 end if ;
6981
6982 --
6983 if g_debug then
6984 hr_utility.set_location(' Exiting:'||l_proc, 15);
6985 end if;
6986 --
6987 Exception
6988 --
6989 when g_not_include then
6990 p_include := 'N';
6991 --
6992
6993 end Evaluate_cwb_incl ;
6994
6995
6996
6997
6998 -----------------------------------------------------------------------------
6999 ------------------------- Subheader ---------------------------------------
7000
7001 Procedure Evaluate_subhead_incl
7002 (p_organization_id in number default null ,
7003 p_position_id in number default null ,
7004 p_job_id in number default null ,
7005 p_payroll_id in number default null ,
7006 p_location_id in number default null ,
7007 p_grade_id in number default null ,
7008 p_business_group_id in number ,
7009 p_include out nocopy varchar2 ,
7010 p_effective_date in date ,
7011 p_eff_date in date default null ,
7012 p_actl_date in date default null ) is
7013
7014
7015 l_proc varchar2(72);
7016 l_rl_param1 varchar2(100) ;
7017 l_rl_param1_value varchar2(100) ;
7018 l_rl_param2 varchar2(100) ;
7019 l_rl_param2_value varchar2(100) ;
7020 begin
7021
7022 g_debug := hr_utility.debug_enabled;
7023 if g_debug then
7024 l_proc := g_package||'Evaluate_subhead_incl';
7025 hr_utility.set_location(' Entering:'||l_proc, 5);
7026 end if;
7027
7028 p_include := 'Y';
7029
7030 if g_subhead_rule_rqd = 'Y' then
7031 -- call formula
7032 if p_organization_id is not null then
7033 l_rl_param1 := 'EXT_SUBHDR_ORG_ID' ;
7034 l_rl_param1_value := p_organization_id ;
7035 end if ;
7036
7037 if p_position_id is not null then
7038 l_rl_param2 := 'EXT_SUBHDR_POS_ID' ;
7039 l_rl_param2_value := p_position_id ;
7040 elsif p_job_id is not null then
7041 l_rl_param2 := 'EXT_SUBHDR_JOB_ID' ;
7042 l_rl_param2_value := p_job_id ;
7043 elsif p_payroll_id is not null then
7044 l_rl_param2 := 'EXT_SUBHDR_PAY_ID' ;
7045 l_rl_param2_value := p_payroll_id ;
7046 elsif p_location_id is not null then
7047 l_rl_param2 := 'EXT_SUBHDR_LOC_ID' ;
7048 l_rl_param2_value := p_location_id ;
7049 elsif p_grade_id is not null then
7050 l_rl_param2 := 'EXT_SUBHDR_GRADE_ID' ;
7051 l_rl_param2_value := p_grade_id ;
7052
7053 end if ;
7054
7055 --- call formula with the above two values
7056 chk_subhead_rule_incl
7057 (p_business_group_id => p_business_group_id ,
7058 p_effective_date => p_effective_date,
7059 p_excld_flag => g_subhead_rule_excld_flag,
7060 p_param_1 => l_rl_param1 ,
7061 p_param_val_1 => l_rl_param1_value ,
7062 p_param_2 => l_rl_param2 ,
7063 p_param_val_2 => l_rl_param2_value
7064 ) ;
7065 ---
7066
7067 end if ;
7068
7069 --- validate business group -- for future
7070 if g_subhead_bg_rqd = 'Y' and p_business_group_id is not null then
7071 chk_subhead_bg_incl
7072 (p_business_group_id => p_business_group_id
7073 ,p_excld_flag => g_subhead_bg_excld_flag ) ;
7074 end if ;
7075
7076
7077 --- validate position
7078 if g_subhead_pos_rqd = 'Y' and p_position_id is not null then
7079 chk_subhead_pos_incl
7080 (p_position_id => p_position_id
7081 ,p_excld_flag => g_subhead_pos_excld_flag ) ;
7082 end if ;
7083
7084
7085 --- validate position
7086 if g_subhead_job_rqd = 'Y' and p_job_id is not null then
7087 chk_subhead_job_incl
7088 (p_job_id => p_job_id
7089 ,p_excld_flag => g_subhead_job_excld_flag ) ;
7090 end if ;
7091
7092 --- validate location
7093 if g_subhead_loc_rqd = 'Y' and p_location_id is not null then
7094 chk_subhead_loc_incl
7095 (p_location_id => p_location_id
7096 ,p_excld_flag => g_subhead_loc_excld_flag ) ;
7097 end if ;
7098
7099
7100 --- validate payroll
7101 if g_subhead_pay_rqd = 'Y' and p_payroll_id is not null then
7102 chk_subhead_pay_incl
7103 (p_payroll_id => p_payroll_id
7104 ,p_excld_flag => g_subhead_pay_excld_flag ) ;
7105 end if ;
7106
7107
7108 --- validate organizarion
7109 if g_subhead_org_rqd = 'Y' and p_organization_id is not null then
7110 chk_subhead_org_incl
7111 (p_organization_id => p_organization_id
7112 ,p_excld_flag => g_subhead_org_excld_flag ) ;
7113 end if ;
7114
7115
7116 --- validate Grqade
7117 if g_subhead_grd_rqd = 'Y' and p_grade_id is not null then
7118 chk_subhead_grd_incl
7119 (p_grade_id => p_grade_id
7120 ,p_excld_flag => g_subhead_grd_excld_flag ) ;
7121 end if ;
7122
7123
7124
7125 if g_debug then
7126 hr_utility.set_location(' Exiting:'||l_proc, 15);
7127 end if;
7128
7129 Exception
7130 --
7131 when g_not_include then
7132 p_include := 'N';
7133 if g_debug then
7134 hr_utility.set_location(' Exiting:'||l_proc, 5);
7135 end if ;
7136 --
7137
7138 end ;
7139
7140
7141
7142 -----------------------------------------------------------------------------
7143 ---------------------------< evaluate_timecard_incl >--------------------------
7144 -----------------------------------------------------------------------------
7145 --
7146 -- The following procedure evaluates timecard inclusion criteria
7147 --
7148 Procedure evaluate_timecard_incl
7149 (p_otl_lvl IN VARCHAR2
7150 ,p_tc_status IN VARCHAR2 DEFAULT NULL
7151 ,p_tc_deleted IN VARCHAR2 DEFAULT NULL
7152 ,p_project_id IN VARCHAR2 DEFAULT NULL
7153 ,p_task_id IN VARCHAR2 DEFAULT NULL
7154 ,p_exp_typ_id IN VARCHAR2 DEFAULT NULL
7155 ,p_element_type_id IN VARCHAR2 DEFAULT NULL
7156 ,p_po_num IN VARCHAR2 DEFAULT NULL
7157 ,p_include out nocopy varchar2)
7158 is
7159 --
7160 l_proc varchar2(72);
7161 --
7162 Begin
7163 --
7164 g_debug := hr_utility.debug_enabled;
7165 if g_debug then
7166 l_proc := g_package||'evaluate_timecard_incl';
7167 hr_utility.set_location(' Entering:'||l_proc, 5);
7168 end if;
7169
7170 p_include := 'Y';
7171
7172 IF ( p_otl_lvl = 'SUMMARY' )
7173 THEN
7174
7175 IF g_tc_status_incl_rqd = 'Y'
7176 THEN
7177 chk_tc_status_incl
7178 ( p_tc_status => p_tc_status
7179 , p_excld_flag => g_tc_status_excld_flag );
7180 END IF;
7181
7182 IF ( g_tc_deleted_incl_rqd = 'Y' )
7183 THEN
7184 chk_tc_deleted_incl
7185 ( p_tc_deleted => p_tc_deleted
7186 , p_excld_flag => g_tc_deleted_excld_flag );
7187
7188 END IF;
7189
7190 ELSE -- ( p_otl_lvl = 'DETAIL' )
7191
7192 IF ( g_project_id_incl_rqd = 'Y' )
7193 THEN
7194 chk_project_id_incl
7195 ( p_project_id => p_project_id
7196 , p_excld_flag => g_project_id_excld_flag );
7197
7198 END IF;
7199
7200 IF ( g_task_id_incl_rqd = 'Y' )
7201 THEN
7202 chk_task_id_incl
7203 ( p_task_id => p_task_id
7204 , p_excld_flag => g_task_id_excld_flag );
7205
7206 END IF;
7207
7208 IF ( g_exp_typ_id_incl_rqd = 'Y' )
7209 THEN
7210 chk_exp_typ_id_incl
7211 ( p_exp_typ_id => p_exp_typ_id
7212 , p_excld_flag => g_exp_typ_id_excld_flag );
7213
7214 END IF;
7215
7216 IF ( g_element_type_id_incl_rqd = 'Y' )
7217 THEN
7218 chk_element_type_id_incl
7219 ( p_element_type_id => p_element_type_id
7220 , p_excld_flag => g_element_type_id_excld_flag );
7221
7222 END IF;
7223
7224 IF ( g_po_num_incl_rqd = 'Y' )
7225 THEN
7226 chk_po_num_incl
7227 ( p_po_num => p_po_num
7228 , p_excld_flag => g_po_num_excld_flag );
7229
7230 END IF;
7231
7232
7233 END IF; -- p_otl_lvl
7234
7235
7236 if g_debug then
7237 hr_utility.set_location(' Exiting:'||l_proc, 15);
7238 end if;
7239 --
7240 Exception
7241 --
7242 when g_not_include then
7243 p_include := 'N';
7244 --
7245 End Evaluate_timecard_Incl;
7246 --
7247
7248
7249 end ben_ext_evaluate_inclusion;
[Home] [Help]