[Home] [Help]
PACKAGE: APPS.BEN_EXT_EVALUATE_INCLUSION
Source
1 Package ben_ext_evaluate_inclusion AUTHID CURRENT_USER as
2 /* $Header: benxincl.pkh 120.3 2005/12/23 13:47:05 tjesumic noship $ */
3 --
4 -- Cache area for each inclusion criteria for whether to check
5 -- the criteria for this extract.
6 --
7 g_person_id_incl_rqd varchar2(1);
8 g_postal_code_incl_rqd varchar2(1);
9 g_org_id_incl_rqd varchar2(1);
10 g_loc_id_incl_rqd varchar2(1);
11 g_gre_incl_rqd varchar2(1);
12 g_state_incl_rqd varchar2(1);
13 g_bnft_grp_incl_rqd varchar2(1);
14 g_ee_status_incl_rqd varchar2(1);
15 g_payroll_id_incl_rqd varchar2(1);
16 g_payroll_rl_incl_rqd varchar2(1);
17 g_payroll_last_date_incl_rqd varchar2(1);
18 g_enrt_plan_incl_rqd varchar2(1);
19 g_enrt_rg_plan_incl_rqd varchar2(1);
20 g_enrt_sspndd_incl_rqd varchar2(1);
21 g_enrt_cvg_strt_dt_incl_rqd varchar2(1);
22 g_enrt_cvg_drng_perd_incl_rqd varchar2(1);
23 g_enrt_stat_incl_rqd varchar2(1);
24 g_enrt_mthd_incl_rqd varchar2(1);
25 g_enrt_pgm_incl_rqd varchar2(1);
26 g_enrt_opt_incl_rqd varchar2(1);
27 g_enrt_pl_typ_incl_rqd varchar2(1);
28 g_enrt_last_upd_dt_incl_rqd varchar2(1);
29 g_enrt_ler_name_incl_rqd varchar2(1);
30 g_enrt_ler_stat_incl_rqd varchar2(1);
31 g_enrt_ler_ocrd_dt_incl_rqd varchar2(1);
32 g_enrt_ler_ntfn_dt_incl_rqd varchar2(1);
33 g_enrt_rltn_incl_rqd varchar2(1);
34 g_enrt_dpnt_rltn_incl_rqd varchar2(1);
35 g_elct_plan_incl_rqd varchar2(1);
36 g_elct_rg_plan_incl_rqd varchar2(1);
37 g_elct_enrt_strt_dt_incl_rqd varchar2(1);
38 g_elct_yrprd_incl_rqd varchar2(1);
39 g_elct_pgm_incl_rqd varchar2(1);
40 g_elct_opt_incl_rqd varchar2(1);
41 g_elct_pl_typ_incl_rqd varchar2(1);
42 g_elct_last_upd_dt_incl_rqd varchar2(1);
43 g_elct_ler_name_incl_rqd varchar2(1);
44 g_elct_ler_stat_incl_rqd varchar2(1);
45 g_elct_ler_ocrd_dt_incl_rqd varchar2(1);
46 g_elct_ler_ntfn_dt_incl_rqd varchar2(1);
47 g_elct_rltn_incl_rqd varchar2(1);
48 g_ele_input_incl_rqd varchar2(1);
49 g_person_rule_incl_rqd varchar2(1);
50 g_per_ler_incl_rqd varchar2(1);
51 g_person_type_incl_rqd varchar2(1);
52 g_chg_evt_incl_rqd varchar2(1);
53 g_chg_pay_evt_incl_rqd varchar2(1);
54 g_chg_eff_dt_incl_rqd varchar2(1);
55 g_chg_actl_dt_incl_rqd varchar2(1);
56 g_chg_login_incl_rqd varchar2(1);
57 g_cm_typ_incl_rqd varchar2(1);
58 g_cm_last_upd_dt_incl_rqd varchar2(1);
59 g_cm_pr_last_upd_dt_incl_rqd varchar2(1);
60 g_cm_sent_dt_incl_rqd varchar2(1);
61 g_cm_to_be_sent_dt_incl_rqd varchar2(1);
62 g_cmbn_incl_rqd varchar2(1);
63 g_actn_name_incl_rqd varchar2(1);
64 g_actn_item_rltn_incl_rqd varchar2(1);
65 g_prem_last_updt_dt_rqd varchar2(1);
66 g_prem_month_year_rqd varchar2(1);
67 g_asg_to_use_rqd varchar2(1);
68 g_subhead_rule_rqd varchar2(1);
69 g_subhead_pos_rqd varchar2(1);
70 g_subhead_job_rqd varchar2(1);
71 g_subhead_loc_rqd varchar2(1);
72 g_subhead_pay_rqd varchar2(1);
73 g_subhead_org_rqd varchar2(1);
74 g_subhead_bg_rqd varchar2(1);
75 g_subhead_grd_rqd varchar2(1);
76
77 -- CWB
78
79 g_cwb_pl_prd_rqd varchar2(1);
80
81 --
82 -- Flags for checking whether the criterion is inclusion or exclusion one
83 --
84 g_person_id_excld_flag varchar2(1);
85 g_postal_code_excld_flag varchar2(1);
86 g_org_id_excld_flag varchar2(1);
87 g_loc_id_excld_flag varchar2(1);
88 g_gre_excld_flag varchar2(1);
89 g_state_excld_flag varchar2(1);
90 g_bnft_grp_excld_flag varchar2(1);
91 g_ee_status_excld_flag varchar2(1);
92 g_payroll_id_excld_flag varchar2(1);
93 g_payroll_rl_excld_flag varchar2(1);
94 g_payroll_last_Date_excld_flag varchar2(1);
95 g_enrt_plan_excld_flag varchar2(1);
96 g_enrt_rg_plan_excld_flag varchar2(1);
97 g_enrt_sspndd_excld_flag varchar2(1);
98 g_enrt_cvg_strt_dt_excld_flag varchar2(1);
99 g_enrt_cvg_drng_prd_excld_flag varchar2(1);
100 g_enrt_stat_excld_flag varchar2(1);
101 g_enrt_mthd_excld_flag varchar2(1);
102 g_enrt_pgm_excld_flag varchar2(1);
103 g_enrt_opt_excld_flag varchar2(1);
104 g_enrt_pl_typ_excld_flag varchar2(1);
105 g_enrt_last_upd_dt_excld_flag varchar2(1);
106 g_enrt_ler_name_excld_flag varchar2(1);
107 g_enrt_ler_stat_excld_flag varchar2(1);
108 g_enrt_ler_ocrd_dt_excld_flag varchar2(1);
109 g_enrt_ler_ntfn_dt_excld_flag varchar2(1);
110 g_enrt_rltn_excld_flag varchar2(1);
111 g_enrt_dpnt_rltn_excld_flag varchar2(1);
112 g_elct_plan_excld_flag varchar2(1);
113 g_elct_rg_plan_excld_flag varchar2(1);
114 g_elct_enrt_strt_dt_excld_flag varchar2(1);
115 g_elct_yrprd_excld_flag varchar2(1);
116 g_elct_pgm_excld_flag varchar2(1);
117 g_elct_opt_excld_flag varchar2(1);
118 g_elct_pl_typ_excld_flag varchar2(1);
119 g_elct_last_upd_dt_excld_flag varchar2(1);
120 g_elct_ler_name_excld_flag varchar2(1);
121 g_elct_ler_stat_excld_flag varchar2(1);
122 g_elct_ler_ocrd_dt_excld_flag varchar2(1);
123 g_elct_ler_ntfn_dt_excld_flag varchar2(1);
124 g_elct_rltn_excld_flag varchar2(1);
125 g_ele_input_excld_flag varchar2(1);
126 g_person_rule_excld_flag varchar2(1);
127 g_per_ler_excld_flag varchar2(1);
128 g_person_type_excld_flag varchar2(1);
129 g_chg_evt_excld_flag varchar2(1);
130 g_chg_pay_evt_excld_flag varchar2(1);
131 g_chg_eff_dt_excld_flag varchar2(1);
132 g_chg_actl_dt_excld_flag varchar2(1);
133 g_chg_login_excld_flag varchar2(1);
134 g_cm_typ_excld_flag varchar2(1);
135 g_cm_last_upd_dt_excld_flag varchar2(1);
136 g_cm_pr_last_upd_dt_excld_flag varchar2(1);
137 g_cm_sent_dt_excld_flag varchar2(1);
138 g_cm_to_be_sent_dt_excld_flag varchar2(1);
139 g_actn_name_excld_flag varchar2(1);
140 g_actn_item_rltn_excld_flag varchar2(1);
141 g_prem_last_updt_dt_excld_flag varchar2(1) ;
142 g_prem_month_year_excld_flag varchar2(1) ;
143 g_subhead_rule_excld_flag varchar2(1);
144 g_subhead_pos_excld_flag varchar2(1);
145 g_subhead_job_excld_flag varchar2(1);
146 g_subhead_loc_excld_flag varchar2(1);
147 g_subhead_pay_excld_flag varchar2(1);
148 g_subhead_org_excld_flag varchar2(1);
149 g_subhead_bg_excld_flag varchar2(1);
150 g_subhead_grd_excld_flag varchar2(1);
151 -- cwb
152 g_cwb_pl_prd_excld_flag varchar2(1);
153 -- Timecard Globals
154
155 g_tc_status_excld_flag VARCHAR2(1);
156 g_tc_deleted_excld_flag VARCHAR2(1);
157 g_project_id_excld_flag VARCHAR2(1);
158 g_task_id_excld_flag VARCHAR2(1);
159 g_exp_typ_id_excld_flag VARCHAR2(1);
160 g_po_num_excld_flag VARCHAR2(1);
161 g_element_type_id_excld_flag VARCHAR2(1);
162
163 g_tc_status_incl_rqd VARCHAR2(1);
164 g_tc_deleted_incl_rqd VARCHAR2(1);
165 g_project_id_incl_rqd VARCHAR2(1);
166 g_task_id_incl_rqd VARCHAR2(1);
167 g_exp_typ_id_incl_rqd VARCHAR2(1);
168 g_po_num_incl_rqd VARCHAR2(1);
169 g_element_type_id_incl_rqd VARCHAR2(1);
170
171 --
172 -- Cache area for each inclusion criteria for storing the criteria
173 -- values. For range type inclusion criteria, e.g. postal code, there
174 -- will be two cache area for storing value1 and value2.
175 --
176 Type num_list is Table of number
177 Index by binary_integer;
178 --
179 Type char_list is Table of ben_ext_rslt_Dtl.val_01%type
180 Index by binary_integer;
181 --
182 g_person_id_list num_list;
183 --
184 g_postal_code_list1 char_list;
185 g_postal_code_list2 char_list;
186 --
187 g_org_id_list num_list;
188 --
189 g_loc_id_list num_list;
190 --
191 g_gre_list char_list;
192 --
193 g_state_list char_list;
194 --
195 g_bnft_grp_list num_list;
196 --
197 g_ee_status_list num_list;
198 --
199 g_payroll_id_list num_list;
200 --
201 g_payroll_rl_list num_list;
202 --
203 g_enrt_plan_list num_list;
204 --
205 g_enrt_rg_plan_list num_list;
206 --
207 g_enrt_sspndd_list char_list;
208 --
209 g_enrt_cvg_strt_dt_list1 char_list;
210 g_enrt_cvg_strt_dt_list2 char_list;
211 --
212 g_enrt_cvg_drng_perd_list1 char_list;
213 g_enrt_cvg_drng_perd_list2 char_list;
214 --
215 g_enrt_stat_list char_list;
216 --
217 g_enrt_mthd_list char_list;
218 --
219 g_enrt_pgm_list num_list;
220 --
221 g_enrt_pl_typ_list num_list;
222 --
223 g_enrt_opt_list num_list;
224 --
225 g_enrt_last_upd_dt_list1 char_list;
226 g_enrt_last_upd_dt_list2 char_list;
227 --
228 g_enrt_ler_name_list num_list;
229 --
230 g_enrt_ler_stat_list char_list;
231 --
232 g_enrt_ler_ocrd_dt_list1 char_list;
233 g_enrt_ler_ocrd_dt_list2 char_list;
234 --
235 g_enrt_ler_ntfn_dt_list1 char_list;
236 g_enrt_ler_ntfn_dt_list2 char_list;
237 --
238 g_enrt_rltn_list char_list;
239 --
240 g_actn_item_rltn_list char_list;
241 --
242 g_enrt_dpnt_rltn_list char_list;
243 --
244 g_actn_name_list num_list;
245 --
246 g_elct_plan_list num_list;
247 --
248 g_elct_rg_plan_list num_list;
249 --
250 g_elct_enrt_strt_dt_list1 char_list;
251 g_elct_enrt_strt_dt_list2 char_list;
252 --
253 g_elct_yrprd_list num_list;
254 --
255 g_elct_pgm_list num_list;
256 --
257 g_elct_pl_typ_list num_list;
258 --
259 g_elct_opt_list num_list;
260 --
261 g_elct_last_upd_dt_list1 char_list;
262 g_elct_last_upd_dt_list2 char_list;
263 --
264 g_elct_ler_name_list num_list;
265 --
266 g_elct_ler_stat_list char_list;
267 --
268 g_elct_ler_ocrd_dt_list1 char_list;
269 g_elct_ler_ocrd_dt_list2 char_list;
270 --
271 g_elct_ler_ntfn_dt_list1 char_list;
272 g_elct_ler_ntfn_dt_list2 char_list;
273 --
274 g_elct_rltn_list char_list;
275 --
276 g_ele_input_list num_list;
277 g_ele_type_list num_list;
278 --
279 g_person_rule_list num_list;
280 --
281 g_per_ler_list num_list;
282 --
283 g_person_type_list num_list;
284 --
285 g_chg_evt_list char_list;
286 --
287 g_chg_pay_evt_list char_list;
288 --
289 g_chg_eff_dt_list1 char_list;
290 g_chg_eff_dt_list2 char_list;
291 --
292 g_chg_actl_dt_list1 char_list;
293 g_chg_actl_dt_list2 char_list;
294 --
295 g_chg_login_list num_list;
296 --
297 g_cm_typ_list num_list;
298 --
299 g_cm_last_upd_dt_list1 char_list;
300 g_cm_last_upd_dt_list2 char_list;
301 --
302 g_cm_pr_last_upd_dt_list1 char_list;
303 g_cm_pr_last_upd_dt_list2 char_list;
304 --
305 g_cm_sent_dt_list1 char_list;
306 g_cm_sent_dt_list2 char_list;
307 --
308 g_cm_to_be_sent_dt_list1 char_list;
309 g_cm_to_be_sent_dt_list2 char_list;
310 --
311 g_prem_last_upd_dt_list1 char_list;
312 g_prem_last_upd_dt_list2 char_list;
313 --
314 g_prem_month_year_dt_list1 char_list;
315 g_prem_month_year_dt_list2 char_list;
316 --
317 g_payroll_last_dt_list1 char_list;
318 g_payroll_last_dt_list2 char_list;
319 --
320 g_asg_to_use_list char_list;
321 ---
322 g_subhead_rule_list num_list;
323 g_subhead_pos_list num_list;
324 g_subhead_job_list num_list;
325 g_subhead_loc_list num_list;
326 g_subhead_pay_list num_list;
327 g_subhead_org_list num_list;
328 g_subhead_bg_list num_list;
329 g_subhead_grd_list num_list;
330 ---
331 g_cwb_pl_list num_list;
332 g_cwb_prd_list num_list;
333
334 g_crit_typ_list char_list;
335 g_crit_val_list num_list;
336 g_oper_list char_list;
337 g_val1_list char_list;
338 g_val2_list char_list;
339
340 g_tc_status_list char_list;
341 g_tc_deleted_list char_list;
342 g_project_id_list char_list;
343 g_task_id_list char_list;
344 g_exp_typ_id_list char_list;
345 g_po_num_list char_list;
346 g_element_type_list char_list;
347
348 --
349 g_package varchar2(33) := ' ben_ext_evaluate_inclusion.';
350 --
351 g_include exception;
352 --
353 g_not_include exception;
354 --
355 -- The following procedure sets the cache area.
356 --
357 Procedure Determine_Incl_Crit_To_Check
358 (p_ext_crit_prfl_id in ben_ext_crit_prfl.ext_crit_prfl_id%type);
359 --
360 -- The following procedure evaluates person inclusion criteria
361 --
362 Procedure Evaluate_Person_Incl
363 (p_person_id in per_all_people_f.person_id%type,
364 p_postal_code in per_addresses.postal_code%type default null,
365 p_org_id in per_all_assignments_f.organization_id%type default null,
366 p_loc_id in per_all_assignments_f.location_id%type default null,
367 p_gre in hr_soft_coding_keyflex.segment1%type default null,
368 p_state in per_addresses.region_2%type default null,
369 p_bnft_grp in per_all_people_f.benefit_group_id%type default null,
370 p_ee_status in per_all_assignments_f.assignment_status_type_id%type default null,
371 p_payroll_id in per_all_assignments_f.payroll_id%type default null,
372 p_chg_evt_cd in varchar2 default null,
373 p_chg_evt_source in varchar2 default null,
374 p_effective_date in date,
375 --RCHASE
376 p_eff_date in date default null,
377 --End RCHASE
378 p_actl_date in date,
379 p_include out nocopy varchar2);
380 --
381 -- The following procedure evaluates plan inclusion criteria
382 --
383 Procedure Evaluate_Benefit_Incl
384 (p_pl_id in ben_prtt_enrt_rslt_f.pl_id%type default null,
385 p_sspndd_flag in ben_prtt_enrt_rslt_f.sspndd_flag%type default null,
386 p_enrt_cvg_strt_dt in ben_prtt_enrt_rslt_f.enrt_cvg_strt_dt%type default null,
387 p_enrt_cvg_thru_dt in ben_prtt_enrt_rslt_f.enrt_cvg_thru_dt%type default null,
388 p_prtt_enrt_rslt_stat_cd in ben_prtt_enrt_rslt_f.prtt_enrt_rslt_stat_cd%type default null,
392 p_opt_id in ben_opt_f.opt_id%type default null,
389 p_enrt_mthd_cd in ben_prtt_enrt_rslt_f.enrt_mthd_cd%type default null,
390 p_pgm_id in ben_prtt_enrt_rslt_f.pgm_id%type default null,
391 p_pl_typ_id in ben_prtt_enrt_rslt_f.pl_typ_id%type default null,
393 p_last_update_date in ben_prtt_enrt_rslt_f.last_update_date%type default null,
394 p_ler_id in ben_per_in_ler.ler_id%type default null,
395 p_ntfn_dt in ben_per_in_ler.ntfn_dt%type default null,
396 p_lf_evt_ocrd_dt in ben_per_in_ler.lf_evt_ocrd_dt%type default null,
397 p_per_in_ler_stat_cd in ben_per_in_ler.per_in_ler_stat_cd%type default null,
398 p_per_in_ler_id in ben_per_in_ler.per_in_ler_id%type default null,
399 p_prtt_enrt_rslt_id in ben_prtt_enrt_rslt_f.prtt_enrt_rslt_id%type default null,
400 p_effective_date in date default null,
401 p_dpnt_id in number default null ,
402 p_include out nocopy varchar2);
403 --
404 Procedure evaluate_eligibility_incl
405 (p_elct_pl_id in ben_elig_per_elctbl_chc.pl_id%type default null,
406 p_elct_enrt_strt_dt in ben_elig_per_elctbl_chc.enrt_cvg_strt_dt%type default null,
407 p_elct_yrprd_id in ben_elig_per_elctbl_chc.yr_perd_id%type default null,
408 p_elct_pgm_id in ben_elig_per_elctbl_chc.pgm_id%type default null,
409 p_elct_pl_typ_id in ben_elig_per_elctbl_chc.pl_typ_id%type default null,
410 p_elct_opt_id in ben_opt_f.opt_id%type default null,
411 p_elct_last_upd_dt in ben_elig_per_elctbl_chc.last_update_date%type default null,
412 p_elct_per_in_ler_id in ben_per_in_ler.per_in_ler_id%type default null,
413 p_elct_ler_id in ben_per_in_ler.ler_id%type default null,
414 p_elct_per_in_ler_stat_cd in ben_per_in_ler.per_in_ler_stat_cd%type default null,
415 p_elct_lf_evt_ocrd_dt in ben_per_in_ler.lf_evt_ocrd_dt%type default null,
416 p_elct_ntfn_dt in ben_per_in_ler.ntfn_dt%type default null,
417 p_prtt_enrt_rslt_id in ben_elig_per_elctbl_chc.prtt_enrt_rslt_id%type default null,
418 p_effective_date in date default null,
419 p_include out nocopy varchar2);
420 --
421 Procedure evaluate_change_log_incl
422 (p_chg_evt_cd in ben_ext_chg_evt_log.chg_evt_cd%type,
423 p_chg_evt_source in ben_ext_chg_evt_log.chg_evt_cd%type,
424 p_chg_eff_dt in ben_ext_chg_evt_log.chg_eff_dt%type,
425 p_chg_actl_dt in ben_ext_chg_evt_log.chg_actl_dt%type,
426 p_last_update_login in ben_ext_chg_evt_log.last_update_login%type,
427 p_effective_date in date default null,
428 p_include out nocopy varchar2);
429
430 --- procedure to evaluate extract premium
431 Procedure evaluate_prem_incl
432 (p_last_update_date in ben_prtt_prem_by_mo_f.last_update_date%type,
433 p_mo_num in ben_prtt_prem_by_mo_f.mo_num%type default null ,
434 p_yr_num in ben_prtt_prem_by_mo_f.yr_num%type default null,
435 p_effective_date in date default null,
436 p_include out nocopy varchar2) ;
437
438
439
440 --
441 Procedure evaluate_comm_incl
442 (p_cm_typ_id in ben_per_cm_f.cm_typ_id%type,
443 p_last_update_date in ben_per_cm_f.last_update_date%type,
444 p_pvdd_last_update_date in ben_per_cm_prvdd_f.last_update_date%type,
445 p_sent_dt in ben_per_cm_prvdd_f.sent_dt%type,
446 p_to_be_sent_dt in ben_per_cm_prvdd_f.to_be_sent_dt%type,
447 p_effective_date in date default null,
448 p_include out nocopy varchar2);
449 --
450
451 Procedure Evaluate_Elm_Entry_Incl
452 (p_processing_type in pay_element_types_f.processing_type%type, --future use
453 p_input_value_id in pay_input_values_f.input_value_id%type,
454 p_business_group_id in number ,
455 p_pay_period_date in date default null ,
456 p_effective_date in date default null,
457 p_person_id in number default null,
458 p_source_id in number default null,
459 p_source_Type in varchar2 default null,
460 p_element_type_id in number default null,
461 p_element_entry_id in number default null,
462 p_include out nocopy varchar2
463 ) ;
464
465 --
466 Procedure Evaluate_Action_Item_Incl
467 (p_actn_typ_id in ben_prtt_enrt_actn_f.actn_typ_id%type,
468 p_prtt_enrt_actn_id in ben_prtt_enrt_actn_f.prtt_enrt_actn_id%type,
469 p_include out nocopy varchar2);
470
471
472 Procedure Evaluate_subhead_incl
473 (p_organization_id in number default null ,
474 p_position_id in number default null ,
475 p_job_id in number default null ,
476 p_payroll_id in number default null ,
477 p_location_id in number default null ,
478 p_grade_id in number default null ,
479 p_business_group_id in number ,
480 p_include out nocopy varchar2,
481 p_effective_date in date ,
482 p_eff_date in date default null ,
483 p_actl_date in date default null
484 );
485
486
487 -- cwb
488
489 Procedure Evaluate_cwb_incl
490 (p_group_pl_id in number ,
491 p_lf_evt_ocrd_dt in date ,
492 p_include out nocopy varchar2 ,
493 p_effective_date in date ) ;
494
495
496 -----------------------------------------------------------------------------
497 ---------------------------< evaluate_timecard_incl >--------------------------
498 -----------------------------------------------------------------------------
499 --
500 -- The following procedure evaluates timecard inclusion criteria
501 --
502 Procedure evaluate_timecard_incl
503 (p_otl_lvl IN VARCHAR2
504 ,p_tc_status IN VARCHAR2 DEFAULT NULL
505 ,p_tc_deleted IN VARCHAR2 DEFAULT NULL
506 ,p_project_id IN VARCHAR2 DEFAULT NULL
507 ,p_task_id IN VARCHAR2 DEFAULT NULL
508 ,p_exp_typ_id IN VARCHAR2 DEFAULT NULL
509 ,p_element_type_id IN VARCHAR2 DEFAULT NULL
510 ,p_po_num IN VARCHAR2 DEFAULT NULL
511 ,p_include out nocopy varchar2);
512
513 end ; -- ben_ext_evaluate_inclusion