1 package ben_cwb_utils AUTHID CURRENT_USER as
2 /* $Header: bencwbutils.pkh 120.9.12020000.4 2013/01/29 14:50:33 kmsuresh ship $ */
3 FUNCTION get_task_access (
4 p_hidden_cd IN VARCHAR2,
5 p_task_access_cd IN VARCHAR2,
6 p_plan_access_cd IN VARCHAR2,
7 p_wksht_grp_cd IN VARCHAR2,
8 p_population_cd IN VARCHAR2,
9 p_status_cd IN VARCHAR2,
10 p_dist_bdgt_iss_dt IN DATE,
11 p_ss_update_start_dt IN DATE,
12 p_ss_update_end_dt IN DATE,
13 p_effective_dt IN DATE
14 )
15 RETURN VARCHAR2;
16 --
17 FUNCTION is_task_enabled
18 (p_access_cd in varchar2,
19 p_population_cd in varchar2,
20 p_status_cd in varchar2,
21 p_dist_bdgt_iss_dt in date,
22 p_wksht_grp_cd in varchar2)
23 return varchar2;
24
25 FUNCTION get_manager_name(p_emp_per_in_ler_id in number,
26 p_level in number) return varchar2;
27
28 FUNCTION get_profile(p_profile_name in varchar2)
29 return varchar2;
30
31 PROCEDURE get_site_profile (
32 p_profile_1 in varchar2 default null,
33 p_value_1 out nocopy varchar2);
34
35 PROCEDURE get_resp_profile (
36 p_resp_id in number default null,
37 p_profile_1 in varchar2 default null,
38 p_value_1 out nocopy varchar2);
39
40
41 PROCEDURE get_user_profile (
42 p_user_id in number default null,
43 p_profile_1 in varchar2 default null,
44 p_profile_2 in varchar2 default null,
45 p_profile_3 in varchar2 default null,
46 p_profile_4 in varchar2 default null,
47 p_profile_5 in varchar2 default null,
48 p_profile_6 in varchar2 default null,
49 p_profile_7 in varchar2 default null,
50 p_profile_8 in varchar2 default null,
51 p_profile_9 in varchar2 default null,
52 p_profile_10 in varchar2 default null,
53 p_profile_11 in varchar2 default null,
54 p_profile_12 in varchar2 default null,
55 p_profile_13 in varchar2 default null,
56 p_profile_14 in varchar2 default null,
57 p_value_1 out nocopy varchar2,
58 p_value_2 out nocopy varchar2,
59 p_value_3 out nocopy varchar2,
60 p_value_4 out nocopy varchar2,
61 p_value_5 out nocopy varchar2,
62 p_value_6 out nocopy varchar2,
63 p_value_7 out nocopy varchar2,
64 p_value_8 out nocopy varchar2,
65 p_value_9 out nocopy varchar2,
66 p_value_10 out nocopy varchar2,
67 p_value_11 out nocopy varchar2,
68 p_value_12 out nocopy varchar2,
69 p_value_13 out nocopy varchar2,
70 p_value_14 out nocopy varchar2);
71
72 PROCEDURE get_user_email_profile (
73 p_user_id in number default null,
74 p_profile_1 in varchar2 default null,
75 p_profile_2 in varchar2 default null,
76 p_profile_3 in varchar2 default null,
77 p_profile_4 in varchar2 default null,
78 p_value_1 out nocopy varchar2,
79 p_value_2 out nocopy varchar2,
80 p_value_3 out nocopy varchar2,
81 p_value_4 out nocopy varchar2);
82
83 FUNCTION get_bdgt_pct_of_elig_sal_decs return number;
84 FUNCTION get_alloc_pct_of_elig_sal_decs return number;
85
86 FUNCTION get_eligibility(p_plan_status in varchar2,
87 p_opt1_status in varchar2,
88 p_opt2_status in varchar2,
89 p_opt3_status in varchar2,
90 p_opt4_status in varchar2
91 )
92 return varchar2;
93
94 FUNCTION is_person_switchable(p_person_id in number,
95 p_effective_date in date)
96 return varchar2;
97
98 function add_number_with_null_check(p_orig_val in number,
99 p_new_val in number) return number;
100
101 /*----------------------------------------------------------------------
102 Function below is added for Ben CWB Email Burst Enhancement
103 ---------------------------------------------------------------------*/
104
105 PROCEDURE create_perf_benefit_actions
106 (
107 p_validate in boolean default false
108 ,p_benefit_action_id out nocopy number
109 ,p_process_date in date default SYSDATE
110 ,p_uneai_effective_date in date default null
111 ,p_mode_cd in varchar2 default 'W'
112 ,p_derivable_factors_flag in varchar2 default 'N'
113 ,p_close_uneai_flag in varchar2 default 'N'
114 ,p_validate_flag in varchar2 default 'N'
115 ,p_person_id in number default null
116 ,p_person_type_id in number default null
117 ,p_pgm_id in number default null
118 ,p_business_group_id in number default null
119 ,p_pl_id in number default null
120 ,p_popl_enrt_typ_cycl_id in number default null
121 ,p_no_programs_flag in varchar2 default 'N'
122 ,p_no_plans_flag in varchar2 default 'N'
123 ,p_comp_selection_rl in number default null
124 ,p_person_selection_rl in number default null
125 ,p_ler_id in number default null
126 ,p_organization_id in number default null
127 ,p_benfts_grp_id in number default null
128 ,p_location_id in number default null
129 ,p_pstl_zip_rng_id in number default null
130 ,p_rptg_grp_id in number default null
131 ,p_pl_typ_id in number default null
132 ,p_opt_id in number default null
133 ,p_eligy_prfl_id in number default null
134 ,p_vrbl_rt_prfl_id in number default null
135 ,p_legal_entity_id in number default null
136 ,p_payroll_id in number default null
137 ,p_debug_messages_flag in varchar2 default 'N'
138 ,p_cm_trgr_typ_cd in varchar2 default null
139 ,p_cm_typ_id in number default null
140 ,p_age_fctr_id in number default null
141 ,p_min_age in number default null
142 ,p_max_age in number default null
143 ,p_los_fctr_id in number default null
144 ,p_min_los in number default null
145 ,p_max_los in number default null
146 ,p_cmbn_age_los_fctr_id in number default null
147 ,p_min_cmbn in number default null
148 ,p_max_cmbn in number default null
149 ,p_date_from in date default null
150 ,p_elig_enrol_cd in varchar2 default null
151 ,p_actn_typ_id in number default null
152 ,p_use_fctr_to_sel_flag in varchar2 default 'N'
153 ,p_los_det_to_use_cd in varchar2 default null
154 ,p_audit_log_flag in varchar2 default 'N'
155 ,p_lmt_prpnip_by_org_flag in varchar2 default 'N'
156 ,p_lf_evt_ocrd_dt in date default null
157 ,p_ptnl_ler_for_per_stat_cd in varchar2 default null
158 ,p_bft_attribute_category in varchar2 default null
159 ,p_bft_attribute1 in varchar2 default null
160 ,p_bft_attribute3 in varchar2 default null
161 ,p_bft_attribute4 in varchar2 default null
162 ,p_bft_attribute5 in varchar2 default null
163 ,p_bft_attribute6 in varchar2 default null
164 ,p_bft_attribute7 in varchar2 default null
165 ,p_bft_attribute8 in varchar2 default null
166 ,p_bft_attribute9 in varchar2 default null
167 ,p_bft_attribute10 in varchar2 default null
168 ,p_bft_attribute11 in varchar2 default null
169 ,p_bft_attribute12 in varchar2 default null
170 ,p_bft_attribute13 in varchar2 default null
171 ,p_bft_attribute14 in varchar2 default null
172 ,p_bft_attribute15 in varchar2 default null
173 ,p_bft_attribute16 in varchar2 default null
174 ,p_bft_attribute17 in varchar2 default null
175 ,p_bft_attribute18 in varchar2 default null
176 ,p_bft_attribute19 in varchar2 default null
177 ,p_bft_attribute20 in varchar2 default null
178 ,p_bft_attribute21 in varchar2 default null
179 ,p_bft_attribute22 in varchar2 default null
180 ,p_bft_attribute23 in varchar2 default null
181 ,p_bft_attribute24 in varchar2 default null
182 ,p_bft_attribute25 in varchar2 default null
183 ,p_bft_attribute26 in varchar2 default null
184 ,p_bft_attribute27 in varchar2 default null
185 ,p_bft_attribute28 in varchar2 default null
186 ,p_bft_attribute29 in varchar2 default null
187 ,p_bft_attribute30 in varchar2 default null
188 ,p_request_id in number default null
189 ,p_program_application_id in number default null
190 ,p_program_id in number default null
191 ,p_program_update_date in date default null
192 ,p_object_version_number out nocopy number
193 ,p_effective_date in date default null
194 ,p_enrt_perd_id in number default null
195 ,p_inelg_action_cd in varchar2 default null
196 ,p_org_hierarchy_id in number default null
197 ,p_org_starting_node_id in number default null
198 ,p_grade_ladder_id in number default null
199 ,p_asg_events_to_all_sel_dt in varchar2 default null
200 ,p_rate_id in number default null
201 ,p_per_sel_dt_cd in varchar2 default null
202 ,p_per_sel_freq_cd in varchar2 default null
203 ,p_per_sel_dt_from in date default null
204 ,p_per_sel_dt_to in date default null
205 ,p_year_from in number default null
206 ,p_year_to in number default null
207 ,p_cagr_id in number default null
208 ,p_qual_type in number default null
209 ,p_qual_status in varchar2 default null
210 ,p_concat_segs in varchar2 default null
211 ,p_grant_price_val in number default null
212 );
213
214 PROCEDURE insert_person_actions (
215 p_per_actn_id_array IN HR_MISC_SS_NUMBER_TABLE
216 , p_per_id IN HR_MISC_SS_NUMBER_TABLE
217 , p_group_per_in_ler_id IN HR_MISC_SS_NUMBER_TABLE
218 , p_benefit_action_id IN NUMBER
219 ,p_is_placeholder IN HR_MISC_SS_NUMBER_TABLE
220 );
221
222 /* ---------------------------------------------------------------------
223 Procedures/Functions Below are defined for Document Management
224 Enhancements to support Printable Documents (PDF)
225 BEGIN
226 --------------------------------------------------------------------- */
227
228 g_person_rates_rec ben_cwb_person_rates%RowType;
229 g_prior_person_rates_rec ben_cwb_person_rates%RowType;
230 g_opt1_person_rates_rec ben_cwb_person_rates%RowType;
231 g_opt2_person_rates_rec ben_cwb_person_rates%RowType;
232 g_opt3_person_rates_rec ben_cwb_person_rates%RowType;
233 g_opt4_person_rates_rec ben_cwb_person_rates%RowType;
234
235 -- Added to support worksheet manager name
236 g_ws_mgr_full_name ben_cwb_person_info.full_name%TYPE;
237 g_ws_mgr_brief_name ben_cwb_person_info.brief_name%TYPE;
238 g_ws_mgr_custom_name ben_cwb_person_info.custom_name%TYPE;
239 --
240
241 CURSOR g_cursor_asgn_txn (c_assignment_id number, c_asg_updt_eff_date varchar2) is
242 select transaction_id assignment_id,
243 attribute1 asg_updt_eff_Date,
244 attribute5 job_id,
245 attribute6 position_id,
246 attribute7 grade_id,
247 attribute8 people_group_id,
248 attribute11 asgn_flex1, attribute12 asgn_flex2, attribute13 asgn_flex3, attribute14 asgn_flex4,
249 attribute15 asgn_flex5, attribute16 asgn_flex6, attribute17 asgn_flex7, attribute18 asgn_flex8,
250 attribute19 asgn_flex9, attribute20 asgn_flex10,attribute21 asgn_flex11,attribute22 asgn_flex12,
254 attribute35 asgn_flex25,attribute36 asgn_flex26,attribute37 asgn_flex27,attribute38 asgn_flex28,
251 attribute23 asgn_flex13,attribute24 asgn_flex14,attribute25 asgn_flex15,attribute26 asgn_flex16,
252 attribute27 asgn_flex17,attribute28 asgn_flex18,attribute29 asgn_flex19,attribute30 asgn_flex20,
253 attribute31 asgn_flex21,attribute32 asgn_flex22,attribute33 asgn_flex23,attribute34 asgn_flex24,
255 attribute39 asgn_flex29,attribute40 asgn_flex30
256 From ben_transaction
257 where transaction_id = c_assignment_id
258 and transaction_type = 'CWBASG'||c_asg_updt_eff_date;
259 --
260 g_asgn_txn_rec g_cursor_asgn_txn%rowType;
261 --
262 Function get_option1_name(
263 p_group_plan_id in number,
264 p_lf_evnt_ocrd_dt in Date,
265 p_oipl_id in number,
266 p_group_per_in_ler_id in number,
267 p_pl_id in number,
268 p_ws_sub_acty_typ_cd in varchar2) return varchar2;
269
270
271 Function get_option1_rate_ws_amt(
272 p_group_plan_id in number,
273 p_lf_evnt_ocrd_dt in Date,
274 p_oipl_id in number,
275 p_group_per_in_ler_id in number,
276 p_pl_id in number,
277 p_ws_sub_acty_typ_cd in varchar2) return number;
278
279 Function get_option1_unit(
280 p_group_plan_id in number,
281 p_lf_evnt_ocrd_dt in Date,
282 p_oipl_id in number,
283 p_group_per_in_ler_id in number,
284 p_pl_id in number,
285 p_ws_sub_acty_typ_cd in varchar2) return varchar2;
286
287
288 Function get_option1_elg_sal(
289 p_group_plan_id in number,
290 p_lf_evnt_ocrd_dt in Date,
291 p_oipl_id in number,
292 p_group_per_in_ler_id in number,
293 p_pl_id in number,
294 p_ws_sub_acty_typ_cd in varchar2) return number;
295
296 Function get_option1_elg_per_sal(
297 p_group_plan_id in number,
298 p_lf_evnt_ocrd_dt in Date,
299 p_oipl_id in number,
300 p_group_per_in_ler_id in number,
301 p_pl_id in number,
302 p_ws_sub_acty_typ_cd in varchar2) return number;
303
304 Function get_option1_rate_reco_amt(
305 p_group_plan_id in number,
306 p_lf_evnt_ocrd_dt in Date,
307 p_oipl_id in number,
308 p_group_per_in_ler_id in number,
309 p_pl_id in number,
310 p_ws_sub_acty_typ_cd in varchar2) return number;
311
312 Function get_option1_rate_oth_sal(
313 p_group_plan_id in number,
314 p_lf_evnt_ocrd_dt in Date,
315 p_oipl_id in number,
316 p_group_per_in_ler_id in number,
317 p_pl_id in number,
318 p_ws_sub_acty_typ_cd in varchar2) return number;
319
320 Function get_option1_rate_sta_sal(
321 p_group_plan_id in number,
322 p_lf_evnt_ocrd_dt in Date,
323 p_oipl_id in number,
324 p_group_per_in_ler_id in number,
325 p_pl_id in number,
326 p_ws_sub_acty_typ_cd in varchar2) return number;
327
328 Function get_option1_rate_tot_comp(
329 p_group_plan_id in number,
330 p_lf_evnt_ocrd_dt in Date,
331 p_oipl_id in number,
332 p_group_per_in_ler_id in number,
333 p_pl_id in number,
334 p_ws_sub_acty_typ_cd in varchar2) return number;
335
336
337 Function get_option1_rate_misc1(
338 p_group_plan_id in number,
339 p_lf_evnt_ocrd_dt in Date,
340 p_oipl_id in number,
341 p_group_per_in_ler_id in number,
342 p_pl_id in number,
343 p_ws_sub_acty_typ_cd in varchar2) return number;
344
345 Function get_option1_rate_misc2(
346 p_group_plan_id in number,
347 p_lf_evnt_ocrd_dt in Date,
348 p_oipl_id in number,
349 p_group_per_in_ler_id in number,
350 p_pl_id in number,
351 p_ws_sub_acty_typ_cd in varchar2) return number;
352
353 Function get_option1_rate_misc3(
354 p_group_plan_id in number,
355 p_lf_evnt_ocrd_dt in Date,
356 p_oipl_id in number,
357 p_group_per_in_ler_id in number,
358 p_pl_id in number,
359 p_ws_sub_acty_typ_cd in varchar2) return number;
360
361
362 Function get_option2_name(
363 p_group_plan_id in number,
364 p_lf_evnt_ocrd_dt in Date,
365 p_oipl_id in number,
366 p_group_per_in_ler_id in number,
367 p_pl_id in number,
368 p_ws_sub_acty_typ_cd in varchar2) return varchar2;
369
370 Function get_option2_rate_ws_amt(
371 p_group_plan_id in number,
372 p_lf_evnt_ocrd_dt in Date,
373 p_oipl_id in number,
374 p_group_per_in_ler_id in number,
375 p_pl_id in number,
376 p_ws_sub_acty_typ_cd in varchar2) return number;
377
378 Function get_option2_unit(
379 p_group_plan_id in number,
380 p_lf_evnt_ocrd_dt in Date,
381 p_oipl_id in number,
382 p_group_per_in_ler_id in number,
383 p_pl_id in number,
384 p_ws_sub_acty_typ_cd in varchar2) return varchar2;
385
386
387 Function get_option2_elg_sal(
388 p_group_plan_id in number,
389 p_lf_evnt_ocrd_dt in Date,
390 p_oipl_id in number,
391 p_group_per_in_ler_id in number,
392 p_pl_id in number,
393 p_ws_sub_acty_typ_cd in varchar2) return number;
394
395 Function get_option2_elg_per_sal(
396 p_group_plan_id in number,
397 p_lf_evnt_ocrd_dt in Date,
398 p_oipl_id in number,
399 p_group_per_in_ler_id in number,
403 Function get_option2_rate_reco_amt(
400 p_pl_id in number,
401 p_ws_sub_acty_typ_cd in varchar2) return number;
402
404 p_group_plan_id in number,
405 p_lf_evnt_ocrd_dt in Date,
406 p_oipl_id in number,
407 p_group_per_in_ler_id in number,
408 p_pl_id in number,
409 p_ws_sub_acty_typ_cd in varchar2) return number;
410
411 Function get_option2_rate_oth_sal(
412 p_group_plan_id in number,
413 p_lf_evnt_ocrd_dt in Date,
414 p_oipl_id in number,
415 p_group_per_in_ler_id in number,
416 p_pl_id in number,
417 p_ws_sub_acty_typ_cd in varchar2) return number;
418
419 Function get_option2_rate_sta_sal(
420 p_group_plan_id in number,
421 p_lf_evnt_ocrd_dt in Date,
422 p_oipl_id in number,
423 p_group_per_in_ler_id in number,
424 p_pl_id in number,
425 p_ws_sub_acty_typ_cd in varchar2) return number;
426
427 Function get_option2_rate_tot_comp(
428 p_group_plan_id in number,
429 p_lf_evnt_ocrd_dt in Date,
430 p_oipl_id in number,
431 p_group_per_in_ler_id in number,
432 p_pl_id in number,
433 p_ws_sub_acty_typ_cd in varchar2) return number;
434
435
436 Function get_option2_rate_misc1(
437 p_group_plan_id in number,
438 p_lf_evnt_ocrd_dt in Date,
439 p_oipl_id in number,
440 p_group_per_in_ler_id in number,
441 p_pl_id in number,
442 p_ws_sub_acty_typ_cd in varchar2) return number;
443
444 Function get_option2_rate_misc2(
445 p_group_plan_id in number,
446 p_lf_evnt_ocrd_dt in Date,
447 p_oipl_id in number,
448 p_group_per_in_ler_id in number,
449 p_pl_id in number,
450 p_ws_sub_acty_typ_cd in varchar2) return number;
451
452 Function get_option2_rate_misc3(
453 p_group_plan_id in number,
454 p_lf_evnt_ocrd_dt in Date,
455 p_oipl_id in number,
456 p_group_per_in_ler_id in number,
457 p_pl_id in number,
458 p_ws_sub_acty_typ_cd in varchar2) return number;
459
460
461 Function get_option3_name(
462 p_group_plan_id in number,
463 p_lf_evnt_ocrd_dt in Date,
464 p_oipl_id in number,
465 p_group_per_in_ler_id in number,
466 p_pl_id in number,
467 p_ws_sub_acty_typ_cd in varchar2) return varchar2;
468
469
470
471 Function get_option3_rate_ws_amt(
472 p_group_plan_id in number,
473 p_lf_evnt_ocrd_dt in Date,
474 p_oipl_id in number,
475 p_group_per_in_ler_id in number,
476 p_pl_id in number,
477 p_ws_sub_acty_typ_cd in varchar2) return number;
478
479 Function get_option3_unit(
480 p_group_plan_id in number,
481 p_lf_evnt_ocrd_dt in Date,
482 p_oipl_id in number,
483 p_group_per_in_ler_id in number,
484 p_pl_id in number,
485 p_ws_sub_acty_typ_cd in varchar2) return varchar2;
486
487
488 Function get_option3_elg_sal(
489 p_group_plan_id in number,
490 p_lf_evnt_ocrd_dt in Date,
491 p_oipl_id in number,
492 p_group_per_in_ler_id in number,
493 p_pl_id in number,
494 p_ws_sub_acty_typ_cd in varchar2) return number;
495
496 Function get_option3_elg_per_sal(
497 p_group_plan_id in number,
498 p_lf_evnt_ocrd_dt in Date,
499 p_oipl_id in number,
500 p_group_per_in_ler_id in number,
501 p_pl_id in number,
502 p_ws_sub_acty_typ_cd in varchar2) return number;
503
504 Function get_option3_rate_reco_amt(
505 p_group_plan_id in number,
506 p_lf_evnt_ocrd_dt in Date,
507 p_oipl_id in number,
508 p_group_per_in_ler_id in number,
509 p_pl_id in number,
510 p_ws_sub_acty_typ_cd in varchar2) return number;
511
512 Function get_option3_rate_oth_sal(
513 p_group_plan_id in number,
514 p_lf_evnt_ocrd_dt in Date,
515 p_oipl_id in number,
516 p_group_per_in_ler_id in number,
517 p_pl_id in number,
518 p_ws_sub_acty_typ_cd in varchar2) return number;
519
520 Function get_option3_rate_sta_sal(
521 p_group_plan_id in number,
522 p_lf_evnt_ocrd_dt in Date,
523 p_oipl_id in number,
524 p_group_per_in_ler_id in number,
525 p_pl_id in number,
526 p_ws_sub_acty_typ_cd in varchar2) return number;
527
528 Function get_option3_rate_tot_comp(
529 p_group_plan_id in number,
530 p_lf_evnt_ocrd_dt in Date,
531 p_oipl_id in number,
532 p_group_per_in_ler_id in number,
533 p_pl_id in number,
534 p_ws_sub_acty_typ_cd in varchar2) return number;
535
536
537 Function get_option3_rate_misc1(
538 p_group_plan_id in number,
539 p_lf_evnt_ocrd_dt in Date,
540 p_oipl_id in number,
541 p_group_per_in_ler_id in number,
542 p_pl_id in number,
543 p_ws_sub_acty_typ_cd in varchar2) return number;
544
545 Function get_option3_rate_misc2(
546 p_group_plan_id in number,
547 p_lf_evnt_ocrd_dt in Date,
548 p_oipl_id in number,
549 p_group_per_in_ler_id in number,
550 p_pl_id in number,
551 p_ws_sub_acty_typ_cd in varchar2) return number;
552
553 Function get_option3_rate_misc3(
554 p_group_plan_id in number,
558 p_pl_id in number,
555 p_lf_evnt_ocrd_dt in Date,
556 p_oipl_id in number,
557 p_group_per_in_ler_id in number,
559 p_ws_sub_acty_typ_cd in varchar2) return number;
560
561 Function get_option4_name(
562 p_group_plan_id in number,
563 p_lf_evnt_ocrd_dt in Date,
564 p_oipl_id in number,
565 p_group_per_in_ler_id in number,
566 p_pl_id in number,
567 p_ws_sub_acty_typ_cd in varchar2) return varchar2;
568
569
570
571 Function get_option4_rate_ws_amt(
572 p_group_plan_id in number,
573 p_lf_evnt_ocrd_dt in Date,
574 p_oipl_id in number,
575 p_group_per_in_ler_id in number,
576 p_pl_id in number,
577 p_ws_sub_acty_typ_cd in varchar2) return number;
578
579 Function get_option4_unit(
580 p_group_plan_id in number,
581 p_lf_evnt_ocrd_dt in Date,
582 p_oipl_id in number,
583 p_group_per_in_ler_id in number,
584 p_pl_id in number,
585 p_ws_sub_acty_typ_cd in varchar2) return varchar2;
586
587
588 Function get_option4_elg_sal(
589 p_group_plan_id in number,
590 p_lf_evnt_ocrd_dt in Date,
591 p_oipl_id in number,
592 p_group_per_in_ler_id in number,
593 p_pl_id in number,
594 p_ws_sub_acty_typ_cd in varchar2) return number;
595
596 Function get_option4_elg_per_sal(
597 p_group_plan_id in number,
598 p_lf_evnt_ocrd_dt in Date,
599 p_oipl_id in number,
600 p_group_per_in_ler_id in number,
601 p_pl_id in number,
602 p_ws_sub_acty_typ_cd in varchar2) return number;
603
604 Function get_option4_rate_reco_amt(
605 p_group_plan_id in number,
606 p_lf_evnt_ocrd_dt in Date,
607 p_oipl_id in number,
608 p_group_per_in_ler_id in number,
609 p_pl_id in number,
610 p_ws_sub_acty_typ_cd in varchar2) return number;
611
612 Function get_option4_rate_oth_sal(
613 p_group_plan_id in number,
614 p_lf_evnt_ocrd_dt in Date,
615 p_oipl_id in number,
616 p_group_per_in_ler_id in number,
617 p_pl_id in number,
618 p_ws_sub_acty_typ_cd in varchar2) return number;
619
620 Function get_option4_rate_sta_sal(
621 p_group_plan_id in number,
622 p_lf_evnt_ocrd_dt in Date,
623 p_oipl_id in number,
624 p_group_per_in_ler_id in number,
625 p_pl_id in number,
626 p_ws_sub_acty_typ_cd in varchar2) return number;
627
628 Function get_option4_rate_tot_comp(
629 p_group_plan_id in number,
630 p_lf_evnt_ocrd_dt in Date,
631 p_oipl_id in number,
632 p_group_per_in_ler_id in number,
633 p_pl_id in number,
634 p_ws_sub_acty_typ_cd in varchar2) return number;
635
636
637 Function get_option4_rate_misc1(
638 p_group_plan_id in number,
639 p_lf_evnt_ocrd_dt in Date,
640 p_oipl_id in number,
641 p_group_per_in_ler_id in number,
642 p_pl_id in number,
643 p_ws_sub_acty_typ_cd in varchar2) return number;
644
645 Function get_option4_rate_misc2(
646 p_group_plan_id in number,
647 p_lf_evnt_ocrd_dt in Date,
648 p_oipl_id in number,
649 p_group_per_in_ler_id in number,
650 p_pl_id in number,
651 p_ws_sub_acty_typ_cd in varchar2) return number;
652
653 Function get_option4_rate_misc3(
654 p_group_plan_id in number,
655 p_lf_evnt_ocrd_dt in Date,
656 p_oipl_id in number,
657 p_group_per_in_ler_id in number,
658 p_pl_id in number,
659 p_ws_sub_acty_typ_cd in varchar2) return number;
660
661 FUNCTION get_plan_rate_misc3 (
662 p_group_plan_id in number,
663 p_lf_evnt_ocrd_dt in Date,
664 p_oipl_id in number,
665 p_group_per_in_ler_id in number,
666 p_pl_id in number,
667 p_ws_sub_acty_typ_cd in varchar2 ) return number ;
668
669 FUNCTION get_plan_rate_misc2 (
670 p_group_plan_id in number,
671 p_lf_evnt_ocrd_dt in Date,
672 p_oipl_id in number,
673 p_group_per_in_ler_id in number,
674 p_pl_id in number,
675 p_ws_sub_acty_typ_cd in varchar2 ) return number;
676
677 FUNCTION get_plan_rate_misc1 (
678 p_group_plan_id in number,
679 p_lf_evnt_ocrd_dt in Date,
680 p_oipl_id in number,
681 p_group_per_in_ler_id in number,
682 p_pl_id in number,
683 p_ws_sub_acty_typ_cd in varchar2 ) return number;
684 FUNCTION get_plan_rate_total_comp (
685 p_group_plan_id in number,
686 p_lf_evnt_ocrd_dt in Date,
687 p_oipl_id in number,
688 p_group_per_in_ler_id in number,
689 p_pl_id in number,
690 p_ws_sub_acty_typ_cd in varchar2 ) return number ;
691
692 FUNCTION get_plan_rate_stat_sal (
693 p_group_plan_id in number,
694 p_lf_evnt_ocrd_dt in Date,
695 p_oipl_id in number,
696 p_group_per_in_ler_id in number,
697 p_pl_id in number,
698 p_ws_sub_acty_typ_cd in varchar2 ) return number;
699
700 FUNCTION get_plan_rate_other_sal (
701 p_group_plan_id in number,
702 p_lf_evnt_ocrd_dt in Date,
703 p_oipl_id in number,
704 p_group_per_in_ler_id in number,
705 p_pl_id in number,
706 p_ws_sub_acty_typ_cd in varchar2 ) return number;
707
708
712 p_oipl_id in number,
709 FUNCTION get_plan_rate_rec_amt (
710 p_group_plan_id in number,
711 p_lf_evnt_ocrd_dt in Date,
713 p_group_per_in_ler_id in number,
714 p_pl_id in number,
715 p_ws_sub_acty_typ_cd in varchar2 ) return number;
716 FUNCTION get_plan_percent_elig_sal (
717 p_group_plan_id in number,
718 p_lf_evnt_ocrd_dt in Date,
719 p_oipl_id in number,
720 p_group_per_in_ler_id in number,
721 p_pl_id in number,
722 p_ws_sub_acty_typ_cd in varchar2 ) return number;
723
724 FUNCTION get_plan_rate_elig_sal (
725 p_group_plan_id in number,
726 p_lf_evnt_ocrd_dt in Date,
727 p_oipl_id in number,
728 p_group_per_in_ler_id in number,
729 p_pl_id in number,
730 p_ws_sub_acty_typ_cd in varchar2 ) return number;
731 FUNCTION get_plan_rate_ws_amt (
732 p_group_plan_id in number,
733 p_lf_evnt_ocrd_dt in Date,
734 p_oipl_id in number,
735 p_group_per_in_ler_id in number,
736 p_pl_id in number,
737 p_ws_sub_acty_typ_cd in varchar2 ) return number;
738 FUNCTION get_pay_rate_basis (
739 p_group_plan_id in number,
740 p_lf_evnt_ocrd_dt in Date,
741 p_oipl_id in number,
742 p_group_per_in_ler_id in number,
743 p_pl_id in number,
744 p_ws_sub_acty_typ_cd in varchar2) return Varchar2 ;
745
746 FUNCTION get_pay_rate_change_percent (
747 p_group_plan_id in number,
748 p_lf_evt_ocrd_dt in Date,
749 p_oipl_id in number,
750 p_group_per_in_ler_id in number,
751 p_pl_id in number,
752 p_ws_sub_acty_typ_cd in varchar2) return number;
753
754 FUNCTION get_pay_rate_change_amount (
755 p_group_plan_id in number,
756 p_lf_evt_ocrd_dt in Date,
757 p_oipl_id in number,
758 p_group_per_in_ler_id in number,
759 p_pl_id in number,
760 p_ws_sub_acty_typ_cd in varchar2) return number;
761
762 FUNCTION get_pay_rate (
763 p_group_plan_id in number,
764 p_lf_evnt_ocrd_dt in Date,
765 p_oipl_id in number,
766 p_group_per_in_ler_id in number,
767 p_pl_id in number,
768 p_ws_sub_acty_typ_cd in varchar2,
769 p_new_or_prior in varchar2) return number;
770
771 FUNCTION get_pay_rate_change_date (
772 p_group_plan_id in number,
773 p_lf_evnt_ocrd_dt in Date,
774 p_oipl_id in number,
775 p_group_per_in_ler_id in number,
776 p_pl_id in number,
777 p_ws_sub_acty_typ_cd in varchar2,
778 p_new_or_prior in varchar2) return varchar2;
779
780 function get_new_perf_rating (
781 p_assignment_id in number,
782 p_perf_revw_strt_dt in date,
783 p_emp_interview_typ_cd in varchar2 ) return varchar2 ;
784
785
786 FUNCTION get_new_asgn_flex(
787 p_assignment_id in number,
788 p_asg_updt_eff_date in date,
789 p_asg_flex_num in number
790 ) return varchar2 ;
791
792 FUNCTION get_new_people_group(
793 p_assignment_id in number,
794 p_asg_updt_eff_date in date) return varchar2;
795
796 FUNCTION get_new_grade (
797 p_assignment_id in number,
798 p_asg_updt_eff_date in date) return varchar2;
799
800 FUNCTION get_new_position (
801 p_assignment_id in number,
802 p_asg_updt_eff_date in date) return varchar2;
803
804 FUNCTION get_new_job (
805 p_assignment_id in number,
806 p_asg_updt_eff_date in date) return varchar2;
807 --
808 FUNCTION get_group_short_name (
809 p_plan_id in number ,
810 p_lf_evt_ocrd_dt in date ) return varchar2;
811
812
813
814 FUNCTION get_ws_mgr_full_name(p_group_per_in_ler_id in number) return varchar2;
815 FUNCTION get_ws_mgr_brief_name(p_group_per_in_ler_id in number) return varchar2;
816 FUNCTION get_ws_mgr_custom_name(p_group_per_in_ler_id in number) return varchar2;
817
818 Function get_option1_currency(
819 p_group_plan_id in number,
820 p_lf_evnt_ocrd_dt in Date,
821 p_oipl_id in number,
822 p_group_per_in_ler_id in number,
823 p_pl_id in number,
824 p_ws_sub_acty_typ_cd in varchar2) return varchar2;
825
826 Function get_option2_currency(
827 p_group_plan_id in number,
828 p_lf_evnt_ocrd_dt in Date,
829 p_oipl_id in number,
830 p_group_per_in_ler_id in number,
831 p_pl_id in number,
832 p_ws_sub_acty_typ_cd in varchar2) return varchar2;
833
834 Function get_option3_currency(
835 p_group_plan_id in number,
836 p_lf_evnt_ocrd_dt in Date,
837 p_oipl_id in number,
838 p_group_per_in_ler_id in number,
839 p_pl_id in number,
840 p_ws_sub_acty_typ_cd in varchar2) return varchar2;
841
842 Function get_option4_currency(
843 p_group_plan_id in number,
844 p_lf_evnt_ocrd_dt in Date,
845 p_oipl_id in number,
846 p_group_per_in_ler_id in number,
847 p_pl_id in number,
848 p_ws_sub_acty_typ_cd in varchar2) return varchar2;
849
850 FUNCTION get_plan_rate_start_dt (
851 p_group_plan_id in number,
852 p_lf_evnt_ocrd_dt in Date,
853 p_oipl_id in number,
854 p_group_per_in_ler_id in number,
855 p_pl_id in number,
856 p_ws_sub_acty_typ_cd in varchar2 ) return varchar2;
857
858 Function get_option1_rate_start_dt(
859 p_group_plan_id in number,
863 p_pl_id in number,
860 p_lf_evnt_ocrd_dt in Date,
861 p_oipl_id in number,
862 p_group_per_in_ler_id in number,
864 p_ws_sub_acty_typ_cd in varchar2) return varchar2;
865
866 Function get_option2_rate_start_dt(
867 p_group_plan_id in number,
868 p_lf_evnt_ocrd_dt in Date,
869 p_oipl_id in number,
870 p_group_per_in_ler_id in number,
871 p_pl_id in number,
872 p_ws_sub_acty_typ_cd in varchar2) return varchar2;
873
874 Function get_option3_rate_start_dt(
875 p_group_plan_id in number,
876 p_lf_evnt_ocrd_dt in Date,
877 p_oipl_id in number,
878 p_group_per_in_ler_id in number,
879 p_pl_id in number,
880 p_ws_sub_acty_typ_cd in varchar2) return varchar2;
881
882 Function get_option4_rate_start_dt(
883 p_group_plan_id in number,
884 p_lf_evnt_ocrd_dt in Date,
885 p_oipl_id in number,
886 p_group_per_in_ler_id in number,
887 p_pl_id in number,
888 p_ws_sub_acty_typ_cd in varchar2) return varchar2;
889
890 Function get_custom_segment_message(
891 p_custom_seg_text in varchar2 ) return varchar2;
892
893 --
894 /* ---------------------------------------------------------------------
895 END -- Changes for Printable document
896 --------------------------------------------------------------------- */
897 END ben_cwb_utils;
[Home] [Help]