[Home] [Help]
PACKAGE BODY: APPS.BEN_CWB_DYN_CALC_PKG
Source
1 PACKAGE BODY BEN_CWB_DYN_CALC_PKG as
2 /* $Header: bencwbdc.pkb 120.13.12010000.3 2009/06/19 08:28:49 sgnanama ship $ */
3 --
4 -- --------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- --------------------------------------------------------------------------
7 --
8 g_package varchar2(33):=' ben_cwb_dyn_calc_pkg.';
9 g_debug boolean := hr_utility.debug_enabled;
10
11 cursor c_calc_details
12 (v_def_key in number
13 ,v_def_type in varchar2) is
14 select def.view_attribute rslt_col
15 ,def.exe_ordr
16 ,def.rndg_cd
17 ,def.calc_type
18 ,cond.ordr_num cond_ordr
19 ,cond.value cond_val
20 ,cond_eq.ordr_num cond_eq_ordr
21 ,cond_eq.view_attribute cond_eq_col
22 ,cond_eq.operator cond_eq_oper
23 ,cond_eq.logical cond_eq_log
24 ,cond_eq.attribute1 cond_eq_attr1
25 ,cond_eq.attribute2 cond_eq_attr2
26 ,eq.ordr_num calc_eq_ordr
27 ,eq.view_attribute calc_eq_col
28 ,eq.operator calc_eq_oper
29 ,eq.attribute1 calc_eq_attr1
30 ,cond_eq.data_type cond_eq_data_type
31 ,cond.view_attribute cond_col
32 ,cond.message_type cond_msg_type
33 ,cond_eq.view_attribute1 cond_eq_col1
34 ,cond_eq.view_attribute2 cond_eq_col2
35 from ben_calc_definitions def
36 ,ben_calc_conditions cond
37 ,ben_calc_equations cond_eq
38 ,ben_calc_equations eq
39 where def.def_key = v_def_key
40 and def.key_type = v_def_type
41 and def.definition_id = cond.definition_id (+)
42 and cond.condition_id = cond_eq.condition_id (+)
43 and def.definition_id = eq.definition_id (+)
44 and eq.condition_id (+) is null
45 order by def.exe_ordr, def.definition_id,
46 cond.ordr_num, cond_eq.ordr_num,
47 eq.ordr_num ;
48
49 cursor c_allocation_row
50 (v_group_per_in_ler_id in number
51 ,v_group_oipl_id1 in number
52 ,v_group_oipl_id2 in number
53 ,v_group_oipl_id3 in number
54 ,v_group_oipl_id4 in number
55 ,v_perf_txn_type in varchar2
56 ,v_asg_txn_type in varchar2) is
57 select inf.*
58 ,pl.elig_sal_val
59 ,pl.stat_sal_val
60 ,pl.ws_val
61 ,pl.ws_mn_val
62 ,pl.ws_mx_val
63 ,pl.ws_incr_val
64 ,pl.tot_comp_val
65 ,pl.oth_comp_val
66 ,pl.rec_val
67 ,pl.rec_mn_val
68 ,pl.rec_mx_val
69 ,pl.misc1_val
70 ,pl.misc2_val
71 ,pl.misc3_val
72 ,pl.currency
73 ,pl.object_version_number ovn_pl
74 ,dsgn.pl_annulization_factor
75 ,dsgn.pl_id
76 ,dsgn.ws_abr_id
77 ,perf.attribute3 new_perf_rating
78 ,asg.attribute3 new_change_reason
79 ,asg.attribute5 new_job_id
80 ,asg.attribute6 new_position_id
81 ,asg.attribute7 new_grade_id
82 ,opt1.elig_sal_val elig_sal_val_opt1
83 ,opt1.stat_sal_val stat_sal_val_opt1
84 ,opt1.ws_val ws_val_opt1
85 ,opt1.ws_mn_val ws_mn_val_opt1
86 ,opt1.ws_mx_val ws_mx_val_opt1
87 ,opt1.ws_incr_val ws_incr_val_opt1
88 ,opt1.tot_comp_val tot_comp_val_opt1
89 ,opt1.oth_comp_val oth_comp_val_opt1
90 ,opt1.rec_val rec_val_opt1
91 ,opt1.rec_mn_val rec_mn_val_opt1
92 ,opt1.rec_mx_val rec_mx_val_opt1
93 ,opt1.misc1_val misc1_val_opt1
94 ,opt1.misc2_val misc2_val_opt1
95 ,opt1.misc3_val misc3_val_opt1
96 ,opt1.currency currency_opt1
97 ,opt1.object_version_number ovn_opt1
98 ,opt1.oipl_id oipl_id_opt1
99 ,opt1.group_oipl_id group_oipl_id_opt1
100 ,opt2.elig_sal_val elig_sal_val_opt2
101 ,opt2.stat_sal_val stat_sal_val_opt2
102 ,opt2.ws_val ws_val_opt2
103 ,opt2.ws_mn_val ws_mn_val_opt2
104 ,opt2.ws_mx_val ws_mx_val_opt2
105 ,opt2.ws_incr_val ws_incr_val_opt2
106 ,opt2.tot_comp_val tot_comp_val_opt2
107 ,opt2.oth_comp_val oth_comp_val_opt2
108 ,opt2.rec_val rec_val_opt2
109 ,opt2.rec_mn_val rec_mn_val_opt2
110 ,opt2.rec_mx_val rec_mx_val_opt2
111 ,opt2.misc1_val misc1_val_opt2
112 ,opt2.misc2_val misc2_val_opt2
113 ,opt2.misc3_val misc3_val_opt2
114 ,opt2.currency currency_opt2
115 ,opt2.object_version_number ovn_opt2
116 ,opt2.oipl_id oipl_id_opt2
117 ,opt2.group_oipl_id group_oipl_id_opt2
118 ,opt3.elig_sal_val elig_sal_val_opt3
119 ,opt3.stat_sal_val stat_sal_val_opt3
120 ,opt3.ws_val ws_val_opt3
121 ,opt3.ws_mn_val ws_mn_val_opt3
122 ,opt3.ws_mx_val ws_mx_val_opt3
123 ,opt3.ws_incr_val ws_incr_val_opt3
124 ,opt3.tot_comp_val tot_comp_val_opt3
125 ,opt3.oth_comp_val oth_comp_val_opt3
126 ,opt3.rec_val rec_val_opt3
127 ,opt3.rec_mn_val rec_mn_val_opt3
128 ,opt3.rec_mx_val rec_mx_val_opt3
129 ,opt3.misc1_val misc1_val_opt3
130 ,opt3.misc2_val misc2_val_opt3
131 ,opt3.misc3_val misc3_val_opt3
132 ,opt3.currency currency_opt3
133 ,opt3.object_version_number ovn_opt3
134 ,opt3.oipl_id oipl_id_opt3
135 ,opt3.group_oipl_id group_oipl_id_opt3
136 ,opt4.elig_sal_val elig_sal_val_opt4
137 ,opt4.stat_sal_val stat_sal_val_opt4
138 ,opt4.ws_val ws_val_opt4
139 ,opt4.ws_mn_val ws_mn_val_opt4
140 ,opt4.ws_mx_val ws_mx_val_opt4
141 ,opt4.ws_incr_val ws_incr_val_opt4
142 ,opt4.tot_comp_val tot_comp_val_opt4
143 ,opt4.oth_comp_val oth_comp_val_opt4
144 ,opt4.rec_val rec_val_opt4
145 ,opt4.rec_mn_val rec_mn_val_opt4
146 ,opt4.rec_mx_val rec_mx_val_opt4
147 ,opt4.misc1_val misc1_val_opt4
148 ,opt4.misc2_val misc2_val_opt4
149 ,opt4.misc3_val misc3_val_opt4
150 ,opt4.currency currency_opt4
151 ,opt4.object_version_number ovn_opt4
152 ,opt4.oipl_id oipl_id_opt4
153 ,opt4.group_oipl_id group_oipl_id_opt4
154 ,null new_salary
155 ,null new_grd_min_val
156 ,null new_grd_max_val
157 ,null new_grd_mid_point
158 ,null new_grd_comparatio
159 ,null new_grd_quartile
160 from ben_cwb_person_info inf
161 ,ben_cwb_person_rates pl
162 ,ben_cwb_pl_dsgn dsgn
163 ,ben_transaction perf
164 ,ben_transaction asg
165 ,ben_cwb_person_rates opt1
166 ,ben_cwb_person_rates opt2
167 ,ben_cwb_person_rates opt3
168 ,ben_cwb_person_rates opt4
169 where inf.group_per_in_ler_id = v_group_per_in_ler_id
170 and inf.group_per_in_ler_id = pl.group_per_in_ler_id
171 and pl.oipl_id = -1
172 and pl.elig_flag = 'Y'
173 and pl.pl_id = dsgn.pl_id
174 and pl.oipl_id = dsgn.oipl_id
175 and pl.lf_evt_ocrd_dt = dsgn.lf_evt_ocrd_dt
176 and pl.group_per_in_ler_id = opt1.group_per_in_ler_id (+)
177 and pl.pl_id = opt1.pl_id (+)
178 and opt1.group_oipl_id (+) = v_group_oipl_id1
179 and opt1.elig_flag (+) = 'Y'
180 and pl.group_per_in_ler_id = opt2.group_per_in_ler_id (+)
181 and pl.pl_id = opt2.pl_id (+)
182 and opt2.group_oipl_id (+) = v_group_oipl_id2
183 and opt2.elig_flag (+) = 'Y'
184 and pl.group_per_in_ler_id = opt3.group_per_in_ler_id (+)
185 and pl.pl_id = opt3.pl_id (+)
186 and opt3.group_oipl_id (+) = v_group_oipl_id3
187 and opt3.elig_flag (+) = 'Y'
188 and pl.group_per_in_ler_id = opt4.group_per_in_ler_id (+)
189 and pl.pl_id = opt4.pl_id (+)
190 and opt4.group_oipl_id (+) = v_group_oipl_id4
191 and opt4.elig_flag (+) = 'Y'
192 and pl.assignment_id = perf.transaction_id (+)
193 and perf.transaction_type (+) = v_perf_txn_type
194 and pl.assignment_id = asg.transaction_id (+)
195 and asg.transaction_type (+) = v_asg_txn_type;
196
197 cursor c_plan_info(v_group_pl_id in number
198 ,v_lf_evt_ocrd_dt in date) is
199 select o1.oipl_id oipl_id1
200 ,o2.oipl_id oipl_id2
201 ,o3.oipl_id oipl_id3
202 ,o4.oipl_id oipl_id4
203 ,ben_cwb_asg_update.g_ws_perf_rec_type||
204 to_char(pl.perf_revw_strt_dt,'yyyy/mm/dd')||
205 pl.emp_interview_typ_cd perf_txn_type
206 ,ben_cwb_asg_update.g_ws_asg_rec_type||
207 to_char(pl.asg_updt_eff_date,'yyyy/mm/dd') asg_txn_type
208 from ben_cwb_pl_dsgn pl
209 ,ben_cwb_pl_dsgn o1
210 ,ben_cwb_pl_dsgn o2
211 ,ben_cwb_pl_dsgn o3
212 ,ben_cwb_pl_dsgn o4
213 where pl.pl_id = v_group_pl_id
214 and pl.lf_evt_ocrd_dt = v_lf_evt_ocrd_dt
215 and pl.oipl_id = -1
216 and pl.pl_id = o1.pl_id (+)
217 and pl.lf_evt_ocrd_dt = o1.lf_evt_ocrd_dt (+)
218 and o1.oipl_ordr_num (+) = 1
219 and o1.pl_id = o2.pl_id (+)
220 and o1.lf_evt_ocrd_dt = o2.lf_evt_ocrd_dt (+)
221 and o2.oipl_ordr_num (+) = 2
222 and o2.pl_id = o3.pl_id (+)
223 and o2.lf_evt_ocrd_dt = o3.lf_evt_ocrd_dt (+)
224 and o3.oipl_ordr_num (+) = 3
225 and o3.pl_id = o4.pl_id (+)
226 and o3.lf_evt_ocrd_dt = o4.lf_evt_ocrd_dt (+)
227 and o4.oipl_ordr_num (+) = 4;
228
229 g_group_pl_id number(15) := null;
230 g_lf_evt_ocrd_dt date := null;
231 g_allocation_row c_allocation_row%rowtype := null;
232 g_plan_info c_plan_info%rowtype;
233 g_upd_person_info boolean := false;
234 g_upd_pl_rate boolean := false;
235 g_upd_opt1_rate boolean := false;
236 g_upd_opt2_rate boolean := false;
237 g_upd_opt3_rate boolean := false;
238 g_upd_opt4_rate boolean := false;
239
240 type t_calc_details is record
241 (rslt_col varchar2(30)
242 ,func varchar2(30)
243 ,oper varchar2(30)
244 ,attribute1 varchar2(60)
245 ,attribute2 varchar2(60)
246 ,attribute1_type varchar2(30)
247 ,attribute2_type varchar2(30)
248 ,attribute1_oper varchar2(30)
249 ,attribute2_oper varchar2(30));
250 type tab_calc_details is table of t_calc_details index by binary_integer;
251 g_calc_details tab_calc_details;
252
253 NULL_NUMBER constant number := -999999999999999;
254 NULL_CHAR constant varchar2(30) := '9--NULL--9';
255 CALC_START constant varchar2(30) := 'START';
256 CALC_END constant varchar2(30) := 'END';
257 CALC_RETURN constant varchar2(30) := 'RETURN';
258 CALC_ADD constant varchar2(30) := 'ADD';
259 CALC_SUBTRACT constant varchar2(30) := 'SUB';
260 CALC_MULTIPLY constant varchar2(30) := 'MUL';
261 CALC_DIVIDE constant varchar2(30) := 'DIV';
262 CALC_EVAL constant varchar2(30) := 'EVAL';
263 CALC_AND constant varchar2(30) := 'AND';
264 CALC_OR constant varchar2(30) := 'OR';
265 CALC_ROUND constant varchar2(30) := 'RND';
266
267 CALC_GET constant varchar2(30) := 'GET';
268 CALC_FIXED constant varchar2(30) := 'FX';
269 CALC_EQUAL constant varchar2(30) := 'EQ';
270 CALC_MATCH constant varchar2(30) := 'MTCH';
271 CALC_STARTS constant varchar2(30) := 'STRS';
272 CALC_ENDS constant varchar2(30) := 'ENDS';
273 CALC_CONTAINS constant varchar2(30) := 'CNTN';
274 CALC_NULL constant varchar2(30) := 'NULL';
275 CALC_NOTNULL constant varchar2(30) := 'NN';
276 CALC_BETWEEN constant varchar2(30) := 'BTWN';
277 CALC_GREATER constant varchar2(30) := 'GR';
278 CALC_LESS constant varchar2(30) := 'LT';
279 CALC_EQGREATER constant varchar2(30) := 'EGR';
280 CALC_EQLESS constant varchar2(30) := 'ELT';
281
282 CALC_NUMBER constant varchar2(30) := 'N';
283 CALC_DATE constant varchar2(30) := 'D';
284 CALC_CHAR constant varchar2(30) := 'T';
285
286 CALC_CALCULATOR constant varchar2(30) := 'CALC';
287 CALC_COND constant varchar2(30) := 'COND';
288 CALC_MSG constant varchar2(30) := 'MSG';
289
290 CALC_ERR constant varchar2(30) := 'ERR';
291 CALC_INFO constant varchar2(30) := 'INFO';
292
293 function check_null_number(p_value in number)
294 return number is
295 begin
296 if p_value = NULL_NUMBER then
297 return null;
298 else
299 return p_value;
300 end if;
301 end check_null_number;
302
303 function check_null_char(p_value in varchar2)
304 return varchar2 is
305 begin
306 if p_value = NULL_CHAR then
307 return null;
308 else
309 return p_value;
310 end if;
311 end check_null_char;
312
313 procedure load_plan_info(p_group_pl_id in number
314 ,p_lf_evt_ocrd_dt in date) is
315 begin
316 if g_group_pl_id = p_group_pl_id and
317 g_lf_evt_ocrd_dt = p_lf_evt_ocrd_dt then
318 return;
319 end if;
320
321 open c_plan_info(p_group_pl_id, p_lf_evt_ocrd_dt);
322 fetch c_plan_info into g_plan_info;
323 close c_plan_info;
324
325 g_lf_evt_ocrd_dt := p_lf_evt_ocrd_dt;
326
327 end load_plan_info;
328
329 procedure load_allocation_row(p_group_per_in_ler_id in number) is
330 begin
331 open c_allocation_row(p_group_per_in_ler_id
332 ,g_plan_info.oipl_id1
333 ,g_plan_info.oipl_id2
334 ,g_plan_info.oipl_id3
335 ,g_plan_info.oipl_id4
336 ,g_plan_info.perf_txn_type
337 ,g_plan_info.asg_txn_type);
338 fetch c_allocation_row into g_allocation_row;
339
340 -- 6024581: the group_per_in_ler_id should not retain the previous value if the cursor retuns null.
341
342 IF c_allocation_row%NOTFOUND then
343 g_allocation_row := null;
344 end if;
345
346 close c_allocation_row;
347
348 g_allocation_row.base_salary := g_allocation_row.base_salary*(g_allocation_row.pay_annulization_factor/g_allocation_row.pl_annulization_factor);
349 g_allocation_row.GRD_MIN_VAL := g_allocation_row.GRD_MIN_VAL*(g_allocation_row.grade_annulization_factor/g_allocation_row.pl_annulization_factor);
350 g_allocation_row.GRD_MAX_VAL := g_allocation_row.GRD_MAX_VAL*(g_allocation_row.grade_annulization_factor/g_allocation_row.pl_annulization_factor);
351 g_allocation_row.GRD_MID_POINT := g_allocation_row.GRD_MID_POINT*(g_allocation_row.grade_annulization_factor/g_allocation_row.pl_annulization_factor);
352
353 end load_allocation_row;
354 --
355 function get_new_grd_min_val return number is
356 l_min_val number;
357 begin
358 if g_allocation_row.new_grd_min_val is null then
359 l_min_val := ben_cwb_person_info_pkg.
360 get_grd_min_val(g_allocation_row.new_grade_id,
361 g_allocation_row.pay_rate_id,
362 g_allocation_row.effective_date);
363 if l_min_val is null then
364 g_allocation_row.new_grd_min_val := NULL_NUMBER;
365 else
366 g_allocation_row.new_grd_min_val := l_min_val;
367 end if;
368 end if;
369 return check_null_number(g_allocation_row.new_grd_min_val);
370 end get_new_grd_min_val;
371 --
372 function get_new_grd_max_val return number is
373 l_max_val number;
374 begin
375 if g_allocation_row.new_grd_max_val is null then
376 l_max_val := ben_cwb_person_info_pkg.
377 get_grd_max_val(g_allocation_row.new_grade_id,
378 g_allocation_row.pay_rate_id,
379 g_allocation_row.effective_date);
380 if l_max_val is null then
381 g_allocation_row.new_grd_max_val := NULL_NUMBER;
382 else
383 g_allocation_row.new_grd_max_val := l_max_val;
384 end if;
385 end if;
386 return check_null_number(g_allocation_row.new_grd_max_val);
387 end get_new_grd_max_val;
388 --
389 function get_new_grd_mid_point return number is
390 l_mid_point number;
391 begin
392 if g_allocation_row.new_grd_mid_point is null then
393 l_mid_point := ben_cwb_person_info_pkg.
394 get_grd_mid_point(g_allocation_row.new_grade_id,
395 g_allocation_row.pay_rate_id,
396 g_allocation_row.effective_date);
397 if l_mid_point is null then
398 g_allocation_row.new_grd_mid_point := NULL_NUMBER;
399 else
400 g_allocation_row.new_grd_mid_point := l_mid_point;
401 end if;
402 end if;
403 return check_null_number(g_allocation_row.new_grd_mid_point);
404 end get_new_grd_mid_point;
405 --
406 function get_new_salary return number is
407 cursor c_local_plan_info is
408 select oipl_id,
409 oipl_ordr_num
410 from ben_cwb_pl_dsgn
411 where pl_id = g_allocation_row.pl_id
412 and lf_evt_ocrd_dt = g_allocation_row.lf_evt_ocrd_dt
413 and group_pl_id = g_allocation_row.group_pl_id
414 and ws_sub_acty_typ_cd = 'ICM7';
415 l_increase_amt number := null;
416 begin
417 if g_allocation_row.new_salary is null then
418 for l_local_plan_info in c_local_plan_info loop
419 if l_local_plan_info.oipl_ordr_num is null then
420 l_increase_amt := ben_cwb_utils.add_number_with_null_check
421 (g_allocation_row.ws_val,l_increase_amt);
422 elsif l_local_plan_info.oipl_ordr_num = 1 then
423 l_increase_amt := ben_cwb_utils.add_number_with_null_check
424 (g_allocation_row.ws_val_opt1,l_increase_amt);
425 elsif l_local_plan_info.oipl_ordr_num = 2 then
426 l_increase_amt := ben_cwb_utils.add_number_with_null_check
427 (g_allocation_row.ws_val_opt2,l_increase_amt);
428 elsif l_local_plan_info.oipl_ordr_num = 3 then
429 l_increase_amt := ben_cwb_utils.add_number_with_null_check
430 (g_allocation_row.ws_val_opt3,l_increase_amt);
431 elsif l_local_plan_info.oipl_ordr_num = 4 then
432 l_increase_amt := ben_cwb_utils.add_number_with_null_check
433 (g_allocation_row.ws_val_opt4,l_increase_amt);
434 end if;
435 end loop;
436 --
437 if l_increase_amt is null then
438 g_allocation_row.new_salary := NULL_NUMBER;
439 else
440 g_allocation_row.new_salary := l_increase_amt +
441 nvl(g_allocation_row.base_salary,0);
442 end if;
443 end if;
444 return check_null_number(g_allocation_row.new_salary);
445 --
446 end get_new_salary;
447 --
448 function get_new_grd_comparatio return number is
449 l_comparatio number;
450 begin
451 if g_allocation_row.new_grd_comparatio is null then
452 if g_allocation_row.new_grade_id is not null then
453 l_comparatio := ben_cwb_person_info_pkg.
454 get_grd_comparatio(nvl(get_new_salary,
455 g_allocation_row.base_salary),
456 get_new_grd_mid_point);
457 else
458 l_comparatio := ben_cwb_person_info_pkg.
459 get_grd_comparatio(get_new_salary,
460 g_allocation_row.grd_mid_point);
461 end if;
462 if l_comparatio is null then
463 g_allocation_row.new_grd_comparatio := NULL_NUMBER;
464 else
465 g_allocation_row.new_grd_comparatio := l_comparatio;
466 end if;
467 end if;
468 return check_null_number(g_allocation_row.new_grd_comparatio);
469 end get_new_grd_comparatio;
470 --
471 function get_new_grd_quartile return varchar2 is
472 l_quartile varchar2(30);
473 begin
474 if g_allocation_row.new_grd_quartile is null then
475 if g_allocation_row.new_grade_id is not null then
476 l_quartile := ben_cwb_person_info_pkg.
477 get_grd_quartile(nvl(get_new_salary,
478 g_allocation_row.base_salary),
479 get_new_grd_min_val,
480 get_new_grd_max_val,
481 get_new_grd_mid_point);
482 else
483 l_quartile := ben_cwb_person_info_pkg.
484 get_grd_quartile(get_new_salary,
485 g_allocation_row.grd_min_val,
486 g_allocation_row.grd_max_val,
487 g_allocation_row.grd_mid_point);
488 end if;
489 if l_quartile is null then
490 g_allocation_row.new_grd_quartile := NULL_CHAR;
491 else
492 g_allocation_row.new_grd_quartile := l_quartile;
493 end if;
494 end if;
495 return check_null_char(g_allocation_row.new_grd_quartile);
496 end get_new_grd_quartile;
497 --
498 function get_attribute(p_attribute_name in varchar2)
499 return varchar2 is
500
501 begin
502 --
503 if instr(p_attribute_name, 'CustomSegment') > 0 then
504 if p_attribute_name = 'CustomSegment1' then
505 return g_allocation_row.custom_segment1;
506 elsif p_attribute_name = 'CustomSegment2' then
507 return g_allocation_row.custom_segment2;
508 elsif p_attribute_name = 'CustomSegment3' then
509 return g_allocation_row.custom_segment3;
510 elsif p_attribute_name = 'CustomSegment4' then
511 return g_allocation_row.custom_segment4;
512 elsif p_attribute_name = 'CustomSegment5' then
513 return g_allocation_row.custom_segment5;
514 elsif p_attribute_name = 'CustomSegment6' then
515 return g_allocation_row.custom_segment6;
516 elsif p_attribute_name = 'CustomSegment7' then
517 return g_allocation_row.custom_segment7;
518 elsif p_attribute_name = 'CustomSegment8' then
519 return g_allocation_row.custom_segment8;
520 elsif p_attribute_name = 'CustomSegment9' then
521 return g_allocation_row.custom_segment9;
522 elsif p_attribute_name = 'CustomSegment10' then
523 return to_char(g_allocation_row.custom_segment10);
524 elsif p_attribute_name = 'CustomSegment11' then
525 return to_char(g_allocation_row.custom_segment11);
526 elsif p_attribute_name = 'CustomSegment12' then
527 return to_char(g_allocation_row.custom_segment12);
528 elsif p_attribute_name = 'CustomSegment13' then
529 return to_char(g_allocation_row.custom_segment13);
530 elsif p_attribute_name = 'CustomSegment14' then
531 return to_char(g_allocation_row.custom_segment14);
532 elsif p_attribute_name = 'CustomSegment15' then
533 return to_char(g_allocation_row.custom_segment15);
534 elsif p_attribute_name = 'CustomSegment16' then
535 return to_char(g_allocation_row.custom_segment16);
536 elsif p_attribute_name = 'CustomSegment17' then
537 return to_char(g_allocation_row.custom_segment17);
538 elsif p_attribute_name = 'CustomSegment18' then
539 return to_char(g_allocation_row.custom_segment18);
540 elsif p_attribute_name = 'CustomSegment19' then
541 return to_char(g_allocation_row.custom_segment19);
542 elsif p_attribute_name = 'CustomSegment20' then
543 return to_char(g_allocation_row.custom_segment20);
544 end if;
545
546 elsif instr(p_attribute_name, 'Opt1') > 0 then
547 if p_attribute_name = 'EligSalValOpt1' then
548 return to_char(g_allocation_row.elig_sal_val_opt1);
549 elsif p_attribute_name = 'PctOfEligSalOpt1' then
550 return to_char(g_allocation_row.ws_val_opt1*100/g_allocation_row.elig_sal_val_opt1);
551 elsif p_attribute_name = 'WsValOpt1' then
552 return to_char(g_allocation_row.ws_val_opt1);
553 elsif p_attribute_name = 'WsMnValOpt1' then
554 return to_char(g_allocation_row.ws_mn_val_opt1);
555 elsif p_attribute_name = 'WsMxValOpt1' then
556 return to_char(g_allocation_row.ws_mx_val_opt1);
557 elsif p_attribute_name = 'WsIncrValOpt1' then
558 return to_char(g_allocation_row.ws_incr_val_opt1);
559 elsif p_attribute_name = 'StatSalValOpt1' then
560 return to_char(g_allocation_row.stat_sal_val_opt1);
561 elsif p_attribute_name = 'TotCompValOpt1' then
562 return to_char(g_allocation_row.tot_comp_val_opt1);
563 elsif p_attribute_name = 'OthCompValOpt1' then
564 return to_char(g_allocation_row.oth_comp_val_opt1);
565 elsif p_attribute_name = 'RecValOpt1' then
566 return to_char(g_allocation_row.rec_val_opt1);
567 elsif p_attribute_name = 'RecMnValOpt1' then
568 return to_char(g_allocation_row.rec_mn_val_opt1);
569 elsif p_attribute_name = 'RecMxValOpt1' then
570 return to_char(g_allocation_row.rec_mx_val_opt1);
571 elsif p_attribute_name = 'Misc1ValOpt1' then
572 return to_char(g_allocation_row.misc1_val_opt1);
573 elsif p_attribute_name = 'Misc2ValOpt1' then
574 return to_char(g_allocation_row.misc2_val_opt1);
575 elsif p_attribute_name = 'Misc3ValOpt1' then
576 return to_char(g_allocation_row.misc3_val_opt1);
577 else
578 return null;
579 end if;
580
581 elsif instr(p_attribute_name, 'Opt2') > 0 then
582 if p_attribute_name = 'EligSalValOpt2' then
583 return to_char(g_allocation_row.elig_sal_val_opt2);
584 elsif p_attribute_name = 'PctOfEligSalOpt2' then
585 return to_char(g_allocation_row.ws_val_opt2*100/g_allocation_row.elig_sal_val_opt2);
586 elsif p_attribute_name = 'WsValOpt2' then
587 return to_char(g_allocation_row.ws_val_opt2);
588 elsif p_attribute_name = 'WsMnValOpt2' then
589 return to_char(g_allocation_row.ws_mn_val_opt2);
590 elsif p_attribute_name = 'WsMxValOpt2' then
591 return to_char(g_allocation_row.ws_mx_val_opt2);
592 elsif p_attribute_name = 'WsIncrValOpt2' then
593 return to_char(g_allocation_row.ws_incr_val_opt2);
594 elsif p_attribute_name = 'StatSalValOpt2' then
595 return to_char(g_allocation_row.stat_sal_val_opt2);
596 elsif p_attribute_name = 'TotCompValOpt2' then
597 return to_char(g_allocation_row.tot_comp_val_opt2);
598 elsif p_attribute_name = 'OthCompValOpt2' then
599 return to_char(g_allocation_row.oth_comp_val_opt2);
600 elsif p_attribute_name = 'RecValOpt2' then
601 return to_char(g_allocation_row.rec_val_opt2);
602 elsif p_attribute_name = 'RecMnValOpt2' then
603 return to_char(g_allocation_row.rec_mn_val_opt2);
604 elsif p_attribute_name = 'RecMxValOpt2' then
605 return to_char(g_allocation_row.rec_mx_val_opt2);
606 elsif p_attribute_name = 'Misc1ValOpt2' then
607 return to_char(g_allocation_row.misc1_val_opt2);
608 elsif p_attribute_name = 'Misc2ValOpt2' then
609 return to_char(g_allocation_row.misc2_val_opt2);
610 elsif p_attribute_name = 'Misc3ValOpt2' then
611 return to_char(g_allocation_row.misc3_val_opt2);
612 else
613 return null;
614 end if;
615
616
617 elsif instr(p_attribute_name, 'Opt3') > 0 then
618 if p_attribute_name = 'EligSalValOpt3' then
619 return to_char(g_allocation_row.elig_sal_val_opt3);
620 elsif p_attribute_name = 'PctOfEligSalOpt3' then
621 return to_char(g_allocation_row.ws_val_opt3*100/g_allocation_row.elig_sal_val_opt3);
622 elsif p_attribute_name = 'WsValOpt3' then
623 return to_char(g_allocation_row.ws_val_opt3);
624 elsif p_attribute_name = 'WsMnValOpt3' then
625 return to_char(g_allocation_row.ws_mn_val_opt3);
626 elsif p_attribute_name = 'WsMxValOpt3' then
627 return to_char(g_allocation_row.ws_mx_val_opt3);
628 elsif p_attribute_name = 'WsIncrValOpt3' then
629 return to_char(g_allocation_row.ws_incr_val_opt3);
630 elsif p_attribute_name = 'StatSalValOpt3' then
631 return to_char(g_allocation_row.stat_sal_val_opt3);
632 elsif p_attribute_name = 'TotCompValOpt3' then
633 return to_char(g_allocation_row.tot_comp_val_opt3);
634 elsif p_attribute_name = 'OthCompValOpt3' then
635 return to_char(g_allocation_row.oth_comp_val_opt3);
636 elsif p_attribute_name = 'RecValOpt3' then
637 return to_char(g_allocation_row.rec_val_opt3);
638 elsif p_attribute_name = 'RecMnValOpt3' then
639 return to_char(g_allocation_row.rec_mn_val_opt3);
640 elsif p_attribute_name = 'RecMxValOpt3' then
641 return to_char(g_allocation_row.rec_mx_val_opt3);
642 elsif p_attribute_name = 'Misc1ValOpt3' then
643 return to_char(g_allocation_row.misc1_val_opt3);
644 elsif p_attribute_name = 'Misc2ValOpt3' then
645 return to_char(g_allocation_row.misc2_val_opt3);
646 elsif p_attribute_name = 'Misc3ValOpt3' then
647 return to_char(g_allocation_row.misc3_val_opt3);
648 else
649 return null;
650 end if;
651
652
653 elsif instr(p_attribute_name, 'Opt4') > 0 then
654 if p_attribute_name = 'EligSalValOpt4' then
655 return to_char(g_allocation_row.elig_sal_val_opt4);
656 elsif p_attribute_name = 'PctOfEligSalOpt4' then
657 return to_char(g_allocation_row.ws_val_opt4*100/g_allocation_row.elig_sal_val_opt4);
658 elsif p_attribute_name = 'WsValOpt4' then
659 return to_char(g_allocation_row.ws_val_opt4);
660 elsif p_attribute_name = 'WsMnValOpt4' then
661 return to_char(g_allocation_row.ws_mn_val_opt4);
662 elsif p_attribute_name = 'WsMxValOpt4' then
663 return to_char(g_allocation_row.ws_mx_val_opt4);
664 elsif p_attribute_name = 'WsIncrValOpt4' then
665 return to_char(g_allocation_row.ws_incr_val_opt4);
666 elsif p_attribute_name = 'StatSalValOpt4' then
667 return to_char(g_allocation_row.stat_sal_val_opt4);
668 elsif p_attribute_name = 'TotCompValOpt4' then
669 return to_char(g_allocation_row.tot_comp_val_opt4);
670 elsif p_attribute_name = 'OthCompValOpt4' then
671 return to_char(g_allocation_row.oth_comp_val_opt4);
672 elsif p_attribute_name = 'RecValOpt4' then
673 return to_char(g_allocation_row.rec_val_opt4);
674 elsif p_attribute_name = 'RecMnValOpt4' then
675 return to_char(g_allocation_row.rec_mn_val_opt4);
676 elsif p_attribute_name = 'RecMxValOpt4' then
677 return to_char(g_allocation_row.rec_mx_val_opt4);
678 elsif p_attribute_name = 'Misc1ValOpt4' then
679 return to_char(g_allocation_row.misc1_val_opt4);
680 elsif p_attribute_name = 'Misc2ValOpt4' then
681 return to_char(g_allocation_row.misc2_val_opt4);
682 elsif p_attribute_name = 'Misc3ValOpt4' then
683 return to_char(g_allocation_row.misc3_val_opt4);
684 else
685 return null;
686 end if;
687
688
689 elsif instr(p_attribute_name, 'AssAttribute') > 0 then
690 if p_attribute_name = 'AssAttribute1' then
691 return g_allocation_row.ass_attribute1;
692 elsif p_attribute_name = 'AssAttribute2' then
693 return g_allocation_row.ass_attribute2;
694 elsif p_attribute_name = 'AssAttribute3' then
695 return g_allocation_row.ass_attribute3;
696 elsif p_attribute_name = 'AssAttribute4' then
697 return g_allocation_row.ass_attribute4;
698 elsif p_attribute_name = 'AssAttribute5' then
699 return g_allocation_row.ass_attribute5;
700 elsif p_attribute_name = 'AssAttribute6' then
701 return g_allocation_row.ass_attribute6;
702 elsif p_attribute_name = 'AssAttribute7' then
703 return g_allocation_row.ass_attribute7;
704 elsif p_attribute_name = 'AssAttribute8' then
705 return g_allocation_row.ass_attribute8;
706 elsif p_attribute_name = 'AssAttribute9' then
707 return g_allocation_row.ass_attribute9;
708 elsif p_attribute_name = 'AssAttribute10' then
709 return g_allocation_row.ass_attribute10;
710 elsif p_attribute_name = 'AssAttribute11' then
711 return g_allocation_row.ass_attribute11;
712 elsif p_attribute_name = 'AssAttribute12' then
713 return g_allocation_row.ass_attribute12;
714 elsif p_attribute_name = 'AssAttribute13' then
715 return g_allocation_row.ass_attribute13;
716 elsif p_attribute_name = 'AssAttribute14' then
717 return g_allocation_row.ass_attribute14;
718 elsif p_attribute_name = 'AssAttribute15' then
719 return g_allocation_row.ass_attribute15;
720 elsif p_attribute_name = 'AssAttribute16' then
721 return g_allocation_row.ass_attribute16;
722 elsif p_attribute_name = 'AssAttribute17' then
723 return g_allocation_row.ass_attribute17;
724 elsif p_attribute_name = 'AssAttribute18' then
725 return g_allocation_row.ass_attribute18;
726 elsif p_attribute_name = 'AssAttribute19' then
727 return g_allocation_row.ass_attribute19;
728 elsif p_attribute_name = 'AssAttribute20' then
729 return g_allocation_row.ass_attribute20;
730 elsif p_attribute_name = 'AssAttribute21' then
731 return g_allocation_row.ass_attribute21;
732 elsif p_attribute_name = 'AssAttribute22' then
733 return g_allocation_row.ass_attribute22;
734 elsif p_attribute_name = 'AssAttribute23' then
735 return g_allocation_row.ass_attribute23;
736 elsif p_attribute_name = 'AssAttribute24' then
737 return g_allocation_row.ass_attribute24;
738 elsif p_attribute_name = 'AssAttribute25' then
739 return g_allocation_row.ass_attribute25;
740 elsif p_attribute_name = 'AssAttribute26' then
741 return g_allocation_row.ass_attribute26;
742 elsif p_attribute_name = 'AssAttribute27' then
743 return g_allocation_row.ass_attribute27;
744 elsif p_attribute_name = 'AssAttribute28' then
745 return g_allocation_row.ass_attribute28;
746 elsif p_attribute_name = 'AssAttribute29' then
747 return g_allocation_row.ass_attribute29;
748 elsif p_attribute_name = 'AssAttribute30' then
749 return g_allocation_row.ass_attribute30;
750 else
751 return null;
752 end if;
753
754 elsif instr(p_attribute_name, 'CpiAttribute') > 0 then
755
756 if p_attribute_name = 'CpiAttribute1' then
757 return g_allocation_row.cpi_attribute1;
758 elsif p_attribute_name = 'CpiAttribute2' then
759 return g_allocation_row.cpi_attribute2;
760 elsif p_attribute_name = 'CpiAttribute3' then
761 return g_allocation_row.cpi_attribute3;
762 elsif p_attribute_name = 'CpiAttribute4' then
763 return g_allocation_row.cpi_attribute4;
764 elsif p_attribute_name = 'CpiAttribute5' then
765 return g_allocation_row.cpi_attribute5;
766 elsif p_attribute_name = 'CpiAttribute6' then
767 return g_allocation_row.cpi_attribute6;
768 elsif p_attribute_name = 'CpiAttribute7' then
769 return g_allocation_row.cpi_attribute7;
770 elsif p_attribute_name = 'CpiAttribute8' then
771 return g_allocation_row.cpi_attribute8;
772 elsif p_attribute_name = 'CpiAttribute9' then
773 return g_allocation_row.cpi_attribute9;
774 elsif p_attribute_name = 'CpiAttribute10' then
775 return g_allocation_row.cpi_attribute10;
776 elsif p_attribute_name = 'CpiAttribute11' then
777 return g_allocation_row.cpi_attribute11;
778 elsif p_attribute_name = 'CpiAttribute12' then
779 return g_allocation_row.cpi_attribute12;
780 elsif p_attribute_name = 'CpiAttribute13' then
781 return g_allocation_row.cpi_attribute13;
782 elsif p_attribute_name = 'CpiAttribute14' then
783 return g_allocation_row.cpi_attribute14;
784 elsif p_attribute_name = 'CpiAttribute15' then
785 return g_allocation_row.cpi_attribute15;
786 elsif p_attribute_name = 'CpiAttribute16' then
787 return g_allocation_row.cpi_attribute16;
788 elsif p_attribute_name = 'CpiAttribute17' then
789 return g_allocation_row.cpi_attribute17;
790 elsif p_attribute_name = 'CpiAttribute18' then
791 return g_allocation_row.cpi_attribute18;
792 elsif p_attribute_name = 'CpiAttribute19' then
793 return g_allocation_row.cpi_attribute19;
794 elsif p_attribute_name = 'CpiAttribute20' then
795 return g_allocation_row.cpi_attribute20;
796 elsif p_attribute_name = 'CpiAttribute21' then
797 return g_allocation_row.cpi_attribute21;
798 elsif p_attribute_name = 'CpiAttribute22' then
799 return g_allocation_row.cpi_attribute22;
800 elsif p_attribute_name = 'CpiAttribute23' then
801 return g_allocation_row.cpi_attribute23;
802 elsif p_attribute_name = 'CpiAttribute24' then
803 return g_allocation_row.cpi_attribute24;
804 elsif p_attribute_name = 'CpiAttribute25' then
805 return g_allocation_row.cpi_attribute25;
806 elsif p_attribute_name = 'CpiAttribute26' then
807 return g_allocation_row.cpi_attribute26;
808 elsif p_attribute_name = 'CpiAttribute27' then
809 return g_allocation_row.cpi_attribute27;
810 elsif p_attribute_name = 'CpiAttribute28' then
811 return g_allocation_row.cpi_attribute28;
812 elsif p_attribute_name = 'CpiAttribute29' then
813 return g_allocation_row.cpi_attribute29;
814 elsif p_attribute_name = 'CpiAttribute30' then
815 return g_allocation_row.cpi_attribute30;
816 else
817 return null;
818 end if;
819
820
821 elsif p_attribute_name = 'EligSalVal' then
822 return to_char(g_allocation_row.elig_sal_val);
823 elsif p_attribute_name = 'PctOfEligSal' then
824 return to_char(g_allocation_row.ws_val*100/g_allocation_row.elig_sal_val);
825 elsif p_attribute_name = 'WsVal' then
826 return to_char(g_allocation_row.ws_val);
827 elsif p_attribute_name = 'WsMnVal' then
828 return to_char(g_allocation_row.ws_mn_val);
829 elsif p_attribute_name = 'WsMxVal' then
830 return to_char(g_allocation_row.ws_mx_val);
831 elsif p_attribute_name = 'WsIncrVal' then
832 return to_char(g_allocation_row.ws_incr_val);
833 elsif p_attribute_name = 'StatSalVal' then
834 return to_char(g_allocation_row.stat_sal_val);
835 elsif p_attribute_name = 'TotCompVal' then
836 return to_char(g_allocation_row.tot_comp_val);
837 elsif p_attribute_name = 'OthCompVal' then
838 return to_char(g_allocation_row.oth_comp_val);
839 elsif p_attribute_name = 'RecVal' then
840 return to_char(g_allocation_row.rec_val);
841 elsif p_attribute_name = 'RecMnVal' then
842 return to_char(g_allocation_row.rec_mn_val);
843 elsif p_attribute_name = 'RecMxVal' then
844 return to_char(g_allocation_row.rec_mx_val);
845 elsif p_attribute_name = 'Misc1Val' then
846 return to_char(g_allocation_row.misc1_val);
847 elsif p_attribute_name = 'Misc2Val' then
848 return to_char(g_allocation_row.misc2_val);
849 elsif p_attribute_name = 'Misc3Val' then
850 return to_char(g_allocation_row.misc3_val);
851 elsif p_attribute_name = 'BaseSalary' then
852 return to_char(g_allocation_row.base_salary);
853 elsif p_attribute_name = 'NewSalary' then
854 return to_char(get_new_salary);
855 elsif p_attribute_name = 'EmailAddress' then
856 return g_allocation_row.email_address;
857 elsif p_attribute_name = 'EmpCategory' then
858 return g_allocation_row.emp_category;
859 elsif p_attribute_name = 'EmpName' then
860 return g_allocation_row.full_name;
861 elsif p_attribute_name = 'EmployeeNumber' then
862 return g_allocation_row.employee_number;
863 elsif p_attribute_name = 'AssignmentStatusTypeId' then
864 return to_char(g_allocation_row.assignment_status_type_id);
865 elsif p_attribute_name = 'BusinessGroupId' then
866 return to_char(g_allocation_row.business_group_id);
867 elsif p_attribute_name = 'GrdComparatio' then
868 return to_char(g_allocation_row.grd_comparatio);
869 elsif p_attribute_name = 'GradeId' then
870 return to_char(g_allocation_row.grade_id);
871 elsif p_attribute_name = 'GrdMinVal' then
872 return to_char(g_allocation_row.grd_min_val);
873 elsif p_attribute_name = 'GrdMaxVal' then
874 return to_char(g_allocation_row.grd_max_val);
875 elsif p_attribute_name = 'GrdMidPoint' then
876 return to_char(g_allocation_row.grd_mid_point);
877 elsif p_attribute_name = 'GrdQuartile' then
878 return g_allocation_row.grd_quartile;
879 elsif p_attribute_name = 'JobId' then
880 return to_char(g_allocation_row.job_id);
881 elsif p_attribute_name = 'LegislationCode' then
882 return g_allocation_row.legislation_code;
883 elsif p_attribute_name = 'LocationId' then
884 return to_char(g_allocation_row.location_id);
885 elsif p_attribute_name = 'MgrName' then
886 return g_allocation_row.supervisor_full_name;
887 elsif p_attribute_name = 'NewChangeReason' then
888 return g_allocation_row.new_change_reason;
889 elsif p_attribute_name = 'NewGrdComparatio' then
890 return to_char(get_new_grd_comparatio);
891 elsif p_attribute_name = 'NewGradeId' then
892 return g_allocation_row.new_grade_id;
893 elsif p_attribute_name = 'NewJobId' then
894 return g_allocation_row.new_job_id;
895 elsif p_attribute_name = 'NewPositionId' then
896 return g_allocation_row.new_position_id;
897 elsif p_attribute_name = 'NewPerfRating' then
898 return g_allocation_row.new_perf_rating;
899 elsif p_attribute_name = 'NewGrdMinVal' then
900 return to_char(get_new_grd_min_val);
901 elsif p_attribute_name = 'NewGrdMidPoint' then
902 return to_char(get_new_grd_mid_point);
903 elsif p_attribute_name = 'NewGrdMaxVal' then
904 return to_char(get_new_grd_max_val);
905 elsif p_attribute_name = 'NewGrdQuartile' then
906 return get_new_grd_quartile;
907 elsif p_attribute_name = 'NormalHours' then
908 return to_char(g_allocation_row.normal_hours);
909 elsif p_attribute_name = 'OrganizationId' then
910 return to_char(g_allocation_row.organization_id);
911 elsif p_attribute_name = 'OriginalStartDate' then
912 return to_char(g_allocation_row.original_start_date, 'yyyy/mm/dd');
913 elsif p_attribute_name = 'PeopleGroupId' then
914 return to_char(g_allocation_row.people_group_id);
915 elsif p_attribute_name = 'PositionId' then
916 return to_char(g_allocation_row.position_id);
917 elsif p_attribute_name = 'PlId' then
918 return to_char(g_allocation_row.pl_id);
919 elsif p_attribute_name = 'PayrollName' then
920 return g_allocation_row.payroll_name;
921 elsif p_attribute_name = 'PerformanceRating' then
922 return g_allocation_row.performance_rating;
923 elsif p_attribute_name = 'PerformanceRatingDate' then
924 return to_char(g_allocation_row.performance_rating_date, 'yyyy/mm/dd');
925 elsif p_attribute_name = 'PerformanceRatingType' then
926 return g_allocation_row.performance_rating_type;
927 elsif p_attribute_name = 'StartDate' then
928 return to_char(g_allocation_row.start_date, 'yyyy/mm/dd');
929 elsif p_attribute_name = 'YearsEmployed' then
930 return to_char(g_allocation_row.years_employed);
931 elsif p_attribute_name = 'YearsInJob' then
932 return to_char(g_allocation_row.years_in_job);
933 elsif p_attribute_name = 'YearsInGrade' then
934 return to_char(g_allocation_row.years_in_grade);
935 elsif p_attribute_name = 'YearsInPosition' then
936 return to_char(g_allocation_row.years_in_position);
937 else
938 return null;
939 end if;
940 --
941 exception
942 when others then
943 return null;
944 end get_attribute;
945
946 procedure set_attribute(p_attribute_name in varchar2
947 ,p_value in varchar2) is
948 begin
949 if instr(p_attribute_name, 'CustomSegment') > 0 then
950 g_upd_person_info := true;
951 if p_attribute_name = 'CustomSegment1' then
952 g_allocation_row.custom_segment1 := p_value;
953 elsif p_attribute_name = 'CustomSegment2' then
954 g_allocation_row.custom_segment2 := p_value;
955 elsif p_attribute_name = 'CustomSegment3' then
956 g_allocation_row.custom_segment3 := p_value;
957 elsif p_attribute_name = 'CustomSegment4' then
958 g_allocation_row.custom_segment4 := p_value;
959 elsif p_attribute_name = 'CustomSegment5' then
960 g_allocation_row.custom_segment5 := p_value;
961 elsif p_attribute_name = 'CustomSegment6' then
962 g_allocation_row.custom_segment6 := p_value;
963 elsif p_attribute_name = 'CustomSegment7' then
964 g_allocation_row.custom_segment7 := p_value;
965 elsif p_attribute_name = 'CustomSegment8' then
966 g_allocation_row.custom_segment8 := p_value;
967 elsif p_attribute_name = 'CustomSegment9' then
968 g_allocation_row.custom_segment9 := p_value;
969 elsif p_attribute_name = 'CustomSegment10' then
970 g_allocation_row.custom_segment10 := p_value;
971 elsif p_attribute_name = 'CustomSegment11' then
972 g_allocation_row.custom_segment11 := to_number(p_value);
973 elsif p_attribute_name = 'CustomSegment12' then
974 g_allocation_row.custom_segment12 := to_number(p_value);
975 elsif p_attribute_name = 'CustomSegment13' then
976 g_allocation_row.custom_segment13 := to_number(p_value);
977 elsif p_attribute_name = 'CustomSegment14' then
978 g_allocation_row.custom_segment14 := to_number(p_value);
979 elsif p_attribute_name = 'CustomSegment15' then
980 g_allocation_row.custom_segment15 := to_number(p_value);
981 elsif p_attribute_name = 'CustomSegment16' then
982 g_allocation_row.custom_segment16 := to_number(p_value);
983 elsif p_attribute_name = 'CustomSegment17' then
984 g_allocation_row.custom_segment17 := to_number(p_value);
985 elsif p_attribute_name = 'CustomSegment18' then
986 g_allocation_row.custom_segment18 := to_number(p_value);
987 elsif p_attribute_name = 'CustomSegment19' then
988 g_allocation_row.custom_segment19 := to_number(p_value);
989 elsif p_attribute_name = 'CustomSegment20' then
990 g_allocation_row.custom_segment20 := to_number(p_value);
991 end if;
992 elsif instr(p_attribute_name, 'CpiAttribute') > 0 then
993 g_upd_person_info := true;
994 if p_attribute_name = 'CpiAttribute1' then
995 g_allocation_row.cpi_attribute1 := p_value;
996 elsif p_attribute_name = 'CpiAttribute2' then
997 g_allocation_row.cpi_attribute2 := p_value;
998 elsif p_attribute_name = 'CpiAttribute3' then
999 g_allocation_row.cpi_attribute3 := p_value;
1000 elsif p_attribute_name = 'CpiAttribute4' then
1001 g_allocation_row.cpi_attribute4 := p_value;
1002 elsif p_attribute_name = 'CpiAttribute5' then
1003 g_allocation_row.cpi_attribute5 := p_value;
1004 elsif p_attribute_name = 'CpiAttribute6' then
1005 g_allocation_row.cpi_attribute6 := p_value;
1006 elsif p_attribute_name = 'CpiAttribute7' then
1007 g_allocation_row.cpi_attribute7 := p_value;
1008 elsif p_attribute_name = 'CpiAttribute8' then
1009 g_allocation_row.cpi_attribute8 := p_value;
1010 elsif p_attribute_name = 'CpiAttribute9' then
1011 g_allocation_row.cpi_attribute9 := p_value;
1012 elsif p_attribute_name = 'CpiAttribute10' then
1013 g_allocation_row.cpi_attribute10 := p_value;
1014 elsif p_attribute_name = 'CpiAttribute11' then
1015 g_allocation_row.cpi_attribute11 := p_value;
1016 elsif p_attribute_name = 'CpiAttribute12' then
1017 g_allocation_row.cpi_attribute12 := p_value;
1018 elsif p_attribute_name = 'CpiAttribute13' then
1019 g_allocation_row.cpi_attribute13 := p_value;
1020 elsif p_attribute_name = 'CpiAttribute14' then
1021 g_allocation_row.cpi_attribute14 := p_value;
1022 elsif p_attribute_name = 'CpiAttribute15' then
1023 g_allocation_row.cpi_attribute15 := p_value;
1024 elsif p_attribute_name = 'CpiAttribute16' then
1025 g_allocation_row.cpi_attribute16 := p_value;
1026 elsif p_attribute_name = 'CpiAttribute17' then
1027 g_allocation_row.cpi_attribute17 := p_value;
1028 elsif p_attribute_name = 'CpiAttribute18' then
1029 g_allocation_row.cpi_attribute18 := p_value;
1030 elsif p_attribute_name = 'CpiAttribute19' then
1031 g_allocation_row.cpi_attribute19 := p_value;
1032 elsif p_attribute_name = 'CpiAttribute20' then
1033 g_allocation_row.cpi_attribute20 := p_value;
1034 elsif p_attribute_name = 'CpiAttribute21' then
1035 g_allocation_row.cpi_attribute21 := p_value;
1036 elsif p_attribute_name = 'CpiAttribute22' then
1037 g_allocation_row.cpi_attribute22 := p_value;
1038 elsif p_attribute_name = 'CpiAttribute23' then
1039 g_allocation_row.cpi_attribute23 := p_value;
1040 elsif p_attribute_name = 'CpiAttribute24' then
1041 g_allocation_row.cpi_attribute24 := p_value;
1042 elsif p_attribute_name = 'CpiAttribute25' then
1043 g_allocation_row.cpi_attribute25 := p_value;
1044 elsif p_attribute_name = 'CpiAttribute26' then
1045 g_allocation_row.cpi_attribute26 := p_value;
1046 elsif p_attribute_name = 'CpiAttribute27' then
1047 g_allocation_row.cpi_attribute27 := p_value;
1048 elsif p_attribute_name = 'CpiAttribute28' then
1049 g_allocation_row.cpi_attribute28 := p_value;
1050 elsif p_attribute_name = 'CpiAttribute29' then
1051 g_allocation_row.cpi_attribute29 := p_value;
1052 elsif p_attribute_name = 'CpiAttribute30' then
1053 g_allocation_row.cpi_attribute30 := p_value;
1054 end if;
1055 elsif ((instr(p_attribute_name, 'Opt1') > 0) and g_allocation_row.oipl_id_opt1 IS NOT NULL) then
1056 g_upd_opt1_rate := true;
1057 if p_attribute_name = 'WsValOpt1' then
1058 g_allocation_row.new_salary := null;
1059 if g_allocation_row.ws_abr_id is not null then
1060 g_upd_pl_rate := true;
1061 g_allocation_row.ws_val:=
1062 ben_cwb_utils.add_number_with_null_check(
1063 g_allocation_row.ws_val,ben_cwb_utils.add_number_with_null_check
1064 (to_number(p_value),-g_allocation_row.ws_val_opt1));
1065 end if;
1066 g_allocation_row.ws_val_opt1:= to_number(p_value);
1067 elsif p_attribute_name = 'StatSalValOpt1' then
1068 g_allocation_row.stat_sal_val_opt1:= to_number(p_value);
1069 elsif p_attribute_name = 'TotCompValOpt1' then
1070 g_allocation_row.tot_comp_val_opt1:= to_number(p_value);
1071 elsif p_attribute_name = 'OthCompValOpt1' then
1072 g_allocation_row.oth_comp_val_opt1:= to_number(p_value);
1073 elsif p_attribute_name = 'RecValOpt1' then
1074 g_allocation_row.rec_val_opt1:= to_number(p_value);
1075 elsif p_attribute_name = 'Misc1ValOpt1' then
1076 g_allocation_row.misc1_val_opt1:= to_number(p_value);
1077 elsif p_attribute_name = 'Misc2ValOpt1' then
1078 g_allocation_row.misc2_val_opt1:= to_number(p_value);
1079 elsif p_attribute_name = 'Misc3ValOpt1' then
1080 g_allocation_row.misc3_val_opt1:= to_number(p_value);
1081 end if;
1082 elsif ((instr(p_attribute_name, 'Opt2') > 0) and g_allocation_row.oipl_id_opt2 IS NOT NULL) then
1083 g_upd_opt2_rate := true;
1084 if p_attribute_name = 'WsValOpt2' then
1085 g_allocation_row.new_salary := null;
1086 if g_allocation_row.ws_abr_id is not null then
1087 g_upd_pl_rate := true;
1088 g_allocation_row.ws_val:=
1089 ben_cwb_utils.add_number_with_null_check(
1090 g_allocation_row.ws_val,ben_cwb_utils.add_number_with_null_check
1091 (to_number(p_value),-g_allocation_row.ws_val_opt2));
1092 end if;
1093 g_allocation_row.ws_val_opt2:= to_number(p_value);
1094 elsif p_attribute_name = 'StatSalValOpt2' then
1095 g_allocation_row.stat_sal_val_opt2:= to_number(p_value);
1096 elsif p_attribute_name = 'TotCompValOpt2' then
1097 g_allocation_row.tot_comp_val_opt2:= to_number(p_value);
1098 elsif p_attribute_name = 'OthCompValOpt2' then
1099 g_allocation_row.oth_comp_val_opt2:= to_number(p_value);
1100 elsif p_attribute_name = 'RecValOpt2' then
1101 g_allocation_row.rec_val_opt2:= to_number(p_value);
1102 elsif p_attribute_name = 'Misc1ValOpt2' then
1103 g_allocation_row.misc1_val_opt2:= to_number(p_value);
1104 elsif p_attribute_name = 'Misc2ValOpt2' then
1105 g_allocation_row.misc2_val_opt2:= to_number(p_value);
1106 elsif p_attribute_name = 'Misc3ValOpt2' then
1107 g_allocation_row.misc3_val_opt2:= to_number(p_value);
1108 end if;
1109 elsif ((instr(p_attribute_name, 'Opt3') > 0) and g_allocation_row.oipl_id_opt3 IS NOT NULL) then
1110 g_upd_opt3_rate := true;
1111 if p_attribute_name = 'WsValOpt3' then
1112 g_allocation_row.new_salary := null;
1113 if g_allocation_row.ws_abr_id is not null then
1114 g_upd_pl_rate := true;
1115 g_allocation_row.ws_val:=
1116 ben_cwb_utils.add_number_with_null_check(
1117 g_allocation_row.ws_val,ben_cwb_utils.add_number_with_null_check
1118 (to_number(p_value),-g_allocation_row.ws_val_opt3));
1119 end if;
1120 g_allocation_row.ws_val_opt3:= to_number(p_value);
1121 elsif p_attribute_name = 'StatSalValOpt3' then
1122 g_allocation_row.stat_sal_val_opt3:= to_number(p_value);
1123 elsif p_attribute_name = 'TotCompValOpt3' then
1124 g_allocation_row.tot_comp_val_opt3:= to_number(p_value);
1125 elsif p_attribute_name = 'OthCompValOpt3' then
1126 g_allocation_row.oth_comp_val_opt3:= to_number(p_value);
1127 elsif p_attribute_name = 'RecValOpt3' then
1128 g_allocation_row.rec_val_opt3:= to_number(p_value);
1129 elsif p_attribute_name = 'Misc1ValOpt3' then
1130 g_allocation_row.misc1_val_opt3:= to_number(p_value);
1131 elsif p_attribute_name = 'Misc2ValOpt3' then
1132 g_allocation_row.misc2_val_opt3:= to_number(p_value);
1133 elsif p_attribute_name = 'Misc3ValOpt3' then
1134 g_allocation_row.misc3_val_opt3:= to_number(p_value);
1135 end if;
1136 elsif ((instr(p_attribute_name, 'Opt4') > 0) and g_allocation_row.oipl_id_opt4 IS NOT NULL) then
1137 g_upd_opt4_rate := true;
1138 if p_attribute_name = 'WsValOpt4' then
1139 g_allocation_row.new_salary := null;
1140 if g_allocation_row.ws_abr_id is not null then
1141 g_upd_pl_rate := true;
1142 g_allocation_row.ws_val:=
1143 ben_cwb_utils.add_number_with_null_check(
1144 g_allocation_row.ws_val,ben_cwb_utils.add_number_with_null_check
1145 (to_number(p_value),-g_allocation_row.ws_val_opt4));
1146 end if;
1147 g_allocation_row.ws_val_opt4:= to_number(p_value);
1148 elsif p_attribute_name = 'StatSalValOpt4' then
1149 g_allocation_row.stat_sal_val_opt4:= to_number(p_value);
1150 elsif p_attribute_name = 'TotCompValOpt4' then
1151 g_allocation_row.tot_comp_val_opt4:= to_number(p_value);
1152 elsif p_attribute_name = 'OthCompValOpt4' then
1153 g_allocation_row.oth_comp_val_opt4:= to_number(p_value);
1154 elsif p_attribute_name = 'RecValOpt4' then
1155 g_allocation_row.rec_val_opt4:= to_number(p_value);
1156 elsif p_attribute_name = 'Misc1ValOpt4' then
1157 g_allocation_row.misc1_val_opt4:= to_number(p_value);
1158 elsif p_attribute_name = 'Misc2ValOpt4' then
1159 g_allocation_row.misc2_val_opt4:= to_number(p_value);
1160 elsif p_attribute_name = 'Misc3ValOpt4' then
1161 g_allocation_row.misc3_val_opt4:= to_number(p_value);
1162 end if;
1163 elsif p_attribute_name = 'WsVal' then
1164 g_allocation_row.new_salary := null;
1165 g_upd_pl_rate := true;
1166 g_allocation_row.ws_val:= to_number(p_value);
1167 elsif p_attribute_name = 'StatSalVal' then
1168 g_upd_pl_rate := true;
1169 g_allocation_row.stat_sal_val:= to_number(p_value);
1170 elsif p_attribute_name = 'TotCompVal' then
1171 g_upd_pl_rate := true;
1172 g_allocation_row.tot_comp_val:= to_number(p_value);
1173 elsif p_attribute_name = 'OthCompVal' then
1174 g_upd_pl_rate := true;
1175 g_allocation_row.oth_comp_val:= to_number(p_value);
1176 elsif p_attribute_name = 'RecVal' then
1177 g_upd_pl_rate := true;
1178 g_allocation_row.rec_val:= to_number(p_value);
1179 elsif p_attribute_name = 'Misc1Val' then
1180 g_upd_pl_rate := true;
1181 g_allocation_row.misc1_val:= to_number(p_value);
1182 elsif p_attribute_name = 'Misc2Val' then
1183 g_upd_pl_rate := true;
1184 g_allocation_row.misc2_val:= to_number(p_value);
1185 elsif p_attribute_name = 'Misc3Val' then
1186 g_upd_pl_rate := true;
1187 g_allocation_row.misc3_val:= to_number(p_value);
1188 end if;
1189 end set_attribute;
1190
1191 function add_number(p_value1 in varchar2,
1192 p_value2 in varchar2)
1193 return varchar2 is
1194 begin
1195 return to_char(nvl(to_number(p_value1),0) + nvl(to_number(p_value2),0));
1196 end add_number;
1197
1198
1199 function sub_number(p_value1 in varchar2,
1200 p_value2 in varchar2)
1201 return varchar2 is
1202 begin
1203 return to_char(nvl(to_number(p_value1),0) - nvl(to_number(p_value2),0));
1204 end sub_number;
1205
1206 function mul_number(p_value1 in varchar2,
1207 p_value2 in varchar2)
1208 return varchar2 is
1209 begin
1210 return to_char(nvl(to_number(p_value1),0) * nvl(to_number(p_value2),0));
1211 end mul_number;
1212
1213 function div_number(p_value1 in varchar2,
1214 p_value2 in varchar2)
1215 return varchar2 is
1216 begin
1217 if (p_value2 is null or to_number(p_value2) = 0) then
1218 return null;
1219 else
1220 return to_char(nvl(to_number(p_value1),0)/nvl(to_number(p_value2),0));
1221 end if;
1222 end div_number;
1223
1224
1225 function round_number(p_value in varchar2,
1226 p_rndg_cd in varchar2)
1227 return varchar2 is
1228 begin
1229 return to_char(benutils.do_rounding(p_rounding_cd => p_rndg_cd,
1230 p_rounding_rl => null,
1231 p_assignment_id => null,
1232 p_value => to_number(p_value),
1233 p_effective_date => null));
1234 end round_number;
1235
1236 function my_nvl2(p_value in varchar2,
1237 p_null_value in varchar2,
1238 p_not_null_value in varchar2)
1239 return varchar2 is
1240 begin
1241 if p_value is null then
1242 return p_null_value;
1243 else
1244 return p_not_null_value;
1245 end if;
1246 end my_nvl2;
1247
1248 function convert_number(p_value in varchar2,
1249 p_data_type in varchar2 default CALC_NUMBER)
1250 return varchar2 is
1251 begin
1252 if p_data_type = CALC_NUMBER then
1253 return to_char(to_number(p_value, '9999999999999999.9999999999'));
1254 else
1255 return p_value;
1256 end if;
1257 end convert_number;
1258
1259
1260 function load_calc_details(p_group_pl_id in number)
1261 return number is
1262
1263 l_rslt_col varchar2(30) := null;
1264 l_old_rslt_col varchar2(30) := null;
1265 l_cond_ordr number := null;
1266 l_old_cond_ordr number := null;
1267 l_calc_type varchar2(30) := null;
1268 l_rndg_cd varchar2(30) := null;
1269 l_cond_return_val varchar2(60) := null;
1270 l_cond_return_attr varchar2(30) := null;
1271 l_msg_type varchar2(30) := null;
1272 l_create_return boolean := false;
1273 l_oper varchar2(30) := null;
1274 l_logic varchar2(30) := null;
1275 l_count integer := 0;
1276 begin
1277
1278 if p_group_pl_id = g_group_pl_id then
1279 return g_calc_details.count;
1280 end if;
1281
1282 g_calc_details.delete;
1283
1284 for l_calc_row in c_calc_details(p_group_pl_id, 'CwbWsPl') loop
1285 l_rslt_col := l_calc_row.rslt_col;
1286 l_cond_ordr := l_calc_row.cond_ordr;
1287
1288 if (l_old_rslt_col is null or l_rslt_col <> l_old_rslt_col) then
1289
1290 if (l_rndg_cd is not null) then
1291 l_count := l_count + 1;
1292 g_calc_details(l_count).rslt_col := l_old_rslt_col;
1293 g_calc_details(l_count).func := CALC_ROUND;
1294 g_calc_details(l_count).attribute1 := l_rndg_cd;
1295 end if;
1296
1297 if (l_create_return) then
1298 l_count := l_count + 1;
1299 g_calc_details(l_count).rslt_col := l_old_rslt_col;
1300 g_calc_details(l_count).func := CALC_RETURN;
1301 g_calc_details(l_count).oper := my_nvl2(l_cond_return_val,
1302 my_nvl2(l_cond_return_attr,null,CALC_GET),
1303 CALC_FIXED);
1304 g_calc_details(l_count).attribute1 := my_nvl2(l_cond_return_val,
1305 my_nvl2(l_cond_return_attr,null,l_cond_return_attr),
1306 l_cond_return_val);
1307 g_calc_details(l_count).attribute2 := l_msg_type;
1308 end if;
1309
1310 l_oper := null;
1311 l_calc_type := l_calc_row.calc_type;
1312 l_logic := null;
1313 l_create_return := false;
1314 l_rndg_cd := null;
1315 l_old_cond_ordr := null;
1316
1317 end if;
1318
1319 if (l_calc_type = CALC_CALCULATOR) then
1320 if (not l_create_return) then
1321
1322 if (l_calc_row.calc_eq_ordr is not null) then
1323 l_create_return := true;
1324 l_rndg_cd := l_calc_row.rndg_cd;
1325 l_cond_return_val := null;
1326 l_cond_return_attr := null;
1327 l_msg_type := null;
1328
1329 l_count := l_count + 1;
1330 g_calc_details(l_count).rslt_col := l_rslt_col;
1331 g_calc_details(l_count).func := CALC_START;
1332 g_calc_details(l_count).oper := my_nvl2(l_calc_row.calc_eq_attr1, CALC_GET, CALC_FIXED);
1333 g_calc_details(l_count).attribute1 := my_nvl2(l_calc_row.calc_eq_attr1, l_calc_row.calc_eq_col, convert_number(l_calc_row.calc_eq_attr1));
1334 g_calc_details(l_count).attribute1_type := CALC_NUMBER;
1335 end if;
1336 else
1337 l_count := l_count + 1;
1338 g_calc_details(l_count).rslt_col := l_rslt_col;
1339 g_calc_details(l_count).func := l_oper;
1340 g_calc_details(l_count).oper := my_nvl2(l_calc_row.calc_eq_attr1, CALC_GET, CALC_FIXED);
1341 g_calc_details(l_count).attribute1 := my_nvl2(l_calc_row.calc_eq_attr1, l_calc_row.calc_eq_col, convert_number(l_calc_row.calc_eq_attr1));
1342 g_calc_details(l_count).attribute1_type := CALC_NUMBER;
1343 end if;
1344
1345 l_oper := l_calc_row.calc_eq_oper;
1346
1347 else
1348 if (l_create_return and l_old_cond_ordr is not null and l_old_cond_ordr <> l_cond_ordr) then
1349 l_count := l_count + 1;
1350 g_calc_details(l_count).rslt_col := l_rslt_col;
1351 g_calc_details(l_count).func := CALC_RETURN;
1352 g_calc_details(l_count).oper := my_nvl2(l_cond_return_val,
1353 my_nvl2(l_cond_return_attr,null,CALC_GET),
1354 CALC_FIXED);
1355 g_calc_details(l_count).attribute1 := my_nvl2(l_cond_return_val,
1356 my_nvl2(l_cond_return_attr,null,l_cond_return_attr),
1357 l_cond_return_val);
1358 g_calc_details(l_count).attribute2 := l_msg_type;
1359 l_create_return := false;
1360 end if;
1361
1362 if (not l_create_return) then
1363 l_cond_return_val := l_calc_row.cond_val;
1364 l_cond_return_attr := l_calc_row.cond_col;
1365 l_msg_type := l_calc_row.cond_msg_type;
1366 l_create_return := true;
1367
1368 l_count := l_count + 1;
1369 g_calc_details(l_count).rslt_col := l_rslt_col;
1370 g_calc_details(l_count).func := CALC_START;
1371 g_calc_details(l_count).oper := CALC_GET;
1372 g_calc_details(l_count).attribute1 := l_calc_row.cond_eq_col;
1373 g_calc_details(l_count).attribute1_type := l_calc_row.cond_eq_data_type;
1374 else
1375 l_count := l_count + 1;
1376 g_calc_details(l_count).rslt_col := l_rslt_col;
1377 g_calc_details(l_count).func := l_logic;
1378 g_calc_details(l_count).oper := CALC_GET;
1379 g_calc_details(l_count).attribute1 := l_calc_row.cond_eq_col;
1380 g_calc_details(l_count).attribute1_type := l_calc_row.cond_eq_data_type;
1381 end if;
1382
1383 if l_calc_row.cond_eq_oper is not null then
1384 l_count := l_count + 1;
1385 g_calc_details(l_count).rslt_col := l_rslt_col;
1386 g_calc_details(l_count).func := CALC_EVAL;
1387 g_calc_details(l_count).oper := l_calc_row.cond_eq_oper;
1388 g_calc_details(l_count).attribute1 := my_nvl2(l_calc_row.cond_eq_attr1,
1389 l_calc_row.cond_eq_col1,
1390 convert_number(l_calc_row.cond_eq_attr1,l_calc_row.cond_eq_data_type));
1391 g_calc_details(l_count).attribute2 := my_nvl2(l_calc_row.cond_eq_attr2,
1392 l_calc_row.cond_eq_col2,
1393 convert_number(l_calc_row.cond_eq_attr2,l_calc_row.cond_eq_data_type));
1394 g_calc_details(l_count).attribute1_type := l_calc_row.cond_eq_data_type;
1395 g_calc_details(l_count).attribute2_type := l_calc_row.cond_eq_data_type;
1396 g_calc_details(l_count).attribute1_oper := my_nvl2(l_calc_row.cond_eq_attr1,CALC_GET,CALC_FIXED);
1397 g_calc_details(l_count).attribute2_oper := my_nvl2(l_calc_row.cond_eq_attr2,CALC_GET,CALC_FIXED);
1398 end if;
1399
1400 l_logic := l_calc_row.cond_eq_log;
1401
1402 end if;
1403
1404 l_old_rslt_col := l_rslt_col;
1405 l_old_cond_ordr := l_cond_ordr;
1406
1407
1408 end loop;
1409
1410 if (l_rndg_cd is not null) then
1411 l_count := l_count + 1;
1412 g_calc_details(l_count).rslt_col := l_old_rslt_col;
1413 g_calc_details(l_count).func := CALC_ROUND;
1414 g_calc_details(l_count).attribute1 := l_rndg_cd;
1415 end if;
1416
1417 if (l_create_return) then
1418 l_count := l_count + 1;
1419 g_calc_details(l_count).rslt_col := l_old_rslt_col;
1420 g_calc_details(l_count).func := CALC_RETURN;
1421 g_calc_details(l_count).oper := my_nvl2(l_cond_return_val,
1422 my_nvl2(l_cond_return_attr,null,CALC_GET),
1423 CALC_FIXED);
1424 g_calc_details(l_count).attribute1 := my_nvl2(l_cond_return_val,
1425 my_nvl2(l_cond_return_attr,null,l_cond_return_attr),
1426 l_cond_return_val);
1427 g_calc_details(l_count).attribute2 := l_msg_type;
1428 end if;
1429
1430 g_group_pl_id := p_group_pl_id;
1431
1432 return l_count;
1433 end load_calc_details;
1434
1435 function get_attribute1(p_calc_detail_row in t_calc_details)
1436 return varchar2 is
1437 begin
1438 if p_calc_detail_row.attribute1_oper = CALC_GET then
1439 return get_attribute(p_calc_detail_row.attribute1);
1440 else
1441 return p_calc_detail_row.attribute1;
1442 end if;
1443 end get_attribute1;
1444
1445 function get_attribute2(p_calc_detail_row in t_calc_details)
1446 return varchar2 is
1447 begin
1448 if p_calc_detail_row.attribute2_oper = CALC_GET then
1449 return get_attribute(p_calc_detail_row.attribute2);
1450 else
1451 return p_calc_detail_row.attribute2;
1452 end if;
1453 end get_attribute2;
1454
1455 function eval_condition(p_value in varchar2,
1456 p_calc_detail_row in t_calc_details)
1457 return boolean is
1458 l_oper varchar2(30) := p_calc_detail_row.oper;
1459 begin
1460 if l_oper = CALC_NULL then
1461 return (p_value is null);
1462 elsif l_oper = CALC_NOTNULL then
1463 return (p_value is not null);
1464 elsif l_oper = CALC_MATCH then
1465 return (p_value = get_attribute1(p_calc_detail_row));
1466 elsif l_oper = CALC_STARTS then
1467 return (instr(p_value,get_attribute1(p_calc_detail_row)) = length(p_value));
1468 elsif l_oper = CALC_ENDS then
1469 return (instr(p_value,get_attribute1(p_calc_detail_row), -1)
1470 = length(p_value)-length(get_attribute1(p_calc_detail_row))+1 );
1471 elsif l_oper = CALC_CONTAINS then
1472 return (instr(p_value,get_attribute1(p_calc_detail_row))>0);
1473 elsif l_oper = CALC_GREATER then
1474 if p_calc_detail_row.attribute1_type = CALC_NUMBER then
1475 return (to_number(p_value) > to_number(get_attribute1(p_calc_detail_row)));
1476 else
1477 return (p_value > get_attribute1(p_calc_detail_row));
1478 end if;
1479 elsif l_oper = CALC_LESS then
1480 if p_calc_detail_row.attribute1_type = CALC_NUMBER then
1481 return (to_number(p_value) < to_number(get_attribute1(p_calc_detail_row)));
1482 else
1483 return (p_value < get_attribute1(p_calc_detail_row));
1484 end if;
1485 elsif l_oper = CALC_EQGREATER then
1486 if p_calc_detail_row.attribute1_type = CALC_NUMBER then
1487 return (to_number(p_value) >= to_number(get_attribute1(p_calc_detail_row)));
1488 else
1489 return (p_value >= get_attribute1(p_calc_detail_row));
1490 end if;
1491 elsif l_oper = CALC_EQLESS then
1492 if p_calc_detail_row.attribute1_type = CALC_NUMBER then
1493 return (to_number(p_value) <= to_number(get_attribute1(p_calc_detail_row)));
1494 else
1495 return (p_value <= get_attribute1(p_calc_detail_row));
1496 end if;
1497 elsif l_oper = CALC_BETWEEN then
1498 if p_calc_detail_row.attribute1_type = CALC_NUMBER then
1499 return (to_number(p_value) between
1500 to_number(get_attribute1(p_calc_detail_row)) and
1501 to_number(get_attribute2(p_calc_detail_row)));
1502 else
1503 return (p_value between
1504 get_attribute1(p_calc_detail_row) and
1505 get_attribute2(p_calc_detail_row));
1506 end if;
1507 end if;
1508
1509 end eval_condition;
1510
1511 function return_value(p_value in varchar2,
1512 p_calc_detail_row in t_calc_details)
1513 return varchar2 is
1514 begin
1515 if p_calc_detail_row.oper = CALC_GET then
1516 return get_attribute(p_calc_detail_row.attribute1);
1517 elsif p_calc_detail_row.attribute1 is not null then
1518 return p_calc_detail_row.attribute1;
1519 else
1520 return p_value;
1521 end if;
1522 end return_value;
1523
1524 function get_attribute(p_calc_detail_row in t_calc_details)
1525 return varchar2 is
1526 begin
1527 if p_calc_detail_row.oper = CALC_GET then
1528 return get_attribute(p_calc_detail_row.attribute1);
1529 elsif p_calc_detail_row.oper = CALC_FIXED then
1530 return p_calc_detail_row.attribute1;
1531 else
1532 return null;
1533 end if;
1534 end get_attribute;
1535
1536 procedure raise_error(p_message in varchar2) is
1537 l_product varchar2(30);
1538 l_message varchar2(60);
1539 l_index number;
1540 begin
1541 --
1542 l_index := instr(p_message, ':');
1543 if l_index > 0 then
1544 l_product := substr(p_message,1,l_index-1);
1545 l_message := substr(p_message,l_index+1,length(p_message)-l_index);
1546 if l_product is not null and l_message is not null then
1547 fnd_message.set_name(l_product, l_message);
1548 fnd_message.raise_error;
1549 end if;
1550 end if;
1551 --
1552 end raise_error;
1553
1554
1555 procedure execute_column(p_group_per_in_ler_id in number
1556 ,p_rslt_col in varchar2
1557 ,p_start_index in integer
1558 ,p_raise_error in boolean) is
1559 l_char_val varchar2(500) := null;
1560 l_bool_val boolean:= true;
1561 l_eval_val boolean:= true;
1562 l_raise_err boolean:= false;
1563
1564 l_next_alwd_func varchar2(30) := null;
1565 l_ret_val varchar2(400) := null;
1566
1567 l_func varchar2(30);
1568 l_proceed boolean;
1569
1570 begin
1571
1572 if p_start_index > 0 and g_calc_details.count > 0 then
1573
1574 l_func := null;
1575
1576 for i in p_start_index..g_calc_details.count loop
1577 if g_calc_details(i).rslt_col is null or
1578 g_calc_details(i).rslt_col <> p_rslt_col then
1579 exit;
1580 end if;
1581
1582 l_func := g_calc_details(i).func;
1583 l_proceed := true;
1584
1585 if (l_next_alwd_func is not null) then
1586 if (l_func <> l_next_alwd_func) then
1587 l_proceed := false;
1588 end if;
1589 end if;
1590
1591 if l_proceed then
1592 if l_func = CALC_START then
1593 l_char_val := get_attribute(g_calc_details(i));
1594 l_bool_val := true;
1595 l_eval_val := true;
1596 l_raise_err:= false;
1597 l_next_alwd_func := null;
1598 elsif (l_func = CALC_RETURN and l_bool_val) then
1599 if g_calc_details(i).attribute2 is null then
1600 l_ret_val := return_value(l_char_val, g_calc_details(i));
1601 exit;
1602 else
1603 l_raise_err := (g_calc_details(i).attribute2 = CALC_ERR);
1604 if l_raise_err then
1605 l_ret_val := g_calc_details(i).attribute1;
1606 else
1607 l_ret_val := g_calc_details(i).attribute2||':'||
1608 g_calc_details(i).attribute1;
1609 end if;
1610 exit;
1611 end if;
1612 elsif (l_func = CALC_EVAL and l_eval_val) then
1613 l_bool_val := eval_condition(l_char_val, g_calc_details(i));
1614 l_eval_val := false;
1615 elsif l_func = CALC_ADD then
1616 l_char_val := add_number(l_char_val, get_attribute(g_calc_details(i)));
1617 elsif l_func = CALC_SUBTRACT then
1618 l_char_val := sub_number(l_char_val, get_attribute(g_calc_details(i)));
1619 elsif l_func = CALC_MULTIPLY then
1620 l_char_val := mul_number(l_char_val, get_attribute(g_calc_details(i)));
1621 elsif l_func = CALC_DIVIDE then
1622 l_char_val := div_number(l_char_val, get_attribute(g_calc_details(i)));
1623 elsif l_func = CALC_ROUND then
1624 l_char_val := round_number(l_char_val, g_calc_details(i).attribute1);
1625 elsif (l_func = CALC_AND) then
1626 if (not l_bool_val) then
1627 l_next_alwd_func := CALC_START;
1628 l_proceed := false;
1629 end if;
1630 if l_proceed then
1631 l_eval_val := true;
1632 l_char_val := get_attribute(g_calc_details(i));
1633 end if;
1634 elsif (l_func = CALC_OR) then
1635 if (l_bool_val) then
1636 l_next_alwd_func := CALC_RETURN;
1637 l_proceed := false;
1638 end if;
1639 if l_proceed then
1640 l_char_val := get_attribute(g_calc_details(i));
1641 l_eval_val := true;
1642 end if;
1643
1644 end if; -- l_func
1645
1646 end if; -- l_proceed
1647
1648
1649 end loop;
1650
1651 if l_func is not null then
1652 --
1653 if l_raise_err then
1654 if p_raise_error then
1655 raise_error(l_ret_val);
1656 end if;
1657 else
1658 set_attribute(p_rslt_col, l_ret_val);
1659 end if;
1660 end if;
1661
1662 end if;
1663 exception
1664 when others then
1665 hr_utility.set_location('execute_column '||p_group_per_in_ler_id||p_rslt_col,99);
1666 raise;
1667
1668 end execute_column;
1669
1670 procedure save_data is
1671 l_save_summary boolean := false;
1672 begin
1673 if g_upd_person_info then
1674 update ben_cwb_person_info
1675 set custom_segment1 = g_allocation_row.custom_segment1
1676 ,custom_segment2 = g_allocation_row.custom_segment2
1677 ,custom_segment3 = g_allocation_row.custom_segment3
1678 ,custom_segment4 = g_allocation_row.custom_segment4
1679 ,custom_segment5 = g_allocation_row.custom_segment5
1680 ,custom_segment6 = g_allocation_row.custom_segment6
1681 ,custom_segment7 = g_allocation_row.custom_segment7
1682 ,custom_segment8 = g_allocation_row.custom_segment8
1683 ,custom_segment9 = g_allocation_row.custom_segment9
1684 ,custom_segment10 = g_allocation_row.custom_segment10
1685 ,custom_segment11 = g_allocation_row.custom_segment11
1686 ,custom_segment12 = g_allocation_row.custom_segment12
1687 ,custom_segment13 = g_allocation_row.custom_segment13
1688 ,custom_segment14 = g_allocation_row.custom_segment14
1689 ,custom_segment15 = g_allocation_row.custom_segment15
1690 ,custom_segment16 = g_allocation_row.custom_segment16
1691 ,custom_segment17 = g_allocation_row.custom_segment17
1692 ,custom_segment18 = g_allocation_row.custom_segment18
1693 ,custom_segment19 = g_allocation_row.custom_segment19
1694 ,custom_segment20 = g_allocation_row.custom_segment20
1695 ,cpi_attribute1 = g_allocation_row.cpi_attribute1
1696 ,cpi_attribute2 = g_allocation_row.cpi_attribute2
1697 ,cpi_attribute3 = g_allocation_row.cpi_attribute3
1698 ,cpi_attribute4 = g_allocation_row.cpi_attribute4
1699 ,cpi_attribute5 = g_allocation_row.cpi_attribute5
1700 ,cpi_attribute6 = g_allocation_row.cpi_attribute6
1701 ,cpi_attribute7 = g_allocation_row.cpi_attribute7
1702 ,cpi_attribute8 = g_allocation_row.cpi_attribute8
1703 ,cpi_attribute9 = g_allocation_row.cpi_attribute9
1704 ,cpi_attribute10 = g_allocation_row.cpi_attribute10
1705 ,cpi_attribute11 = g_allocation_row.cpi_attribute11
1706 ,cpi_attribute12 = g_allocation_row.cpi_attribute12
1707 ,cpi_attribute13 = g_allocation_row.cpi_attribute13
1708 ,cpi_attribute14 = g_allocation_row.cpi_attribute14
1709 ,cpi_attribute15 = g_allocation_row.cpi_attribute15
1710 ,cpi_attribute16 = g_allocation_row.cpi_attribute16
1711 ,cpi_attribute17 = g_allocation_row.cpi_attribute17
1712 ,cpi_attribute18 = g_allocation_row.cpi_attribute18
1713 ,cpi_attribute19 = g_allocation_row.cpi_attribute19
1714 ,cpi_attribute20 = g_allocation_row.cpi_attribute20
1715 ,cpi_attribute21 = g_allocation_row.cpi_attribute21
1716 ,cpi_attribute22 = g_allocation_row.cpi_attribute22
1717 ,cpi_attribute23 = g_allocation_row.cpi_attribute23
1718 ,cpi_attribute24 = g_allocation_row.cpi_attribute24
1719 ,cpi_attribute25 = g_allocation_row.cpi_attribute25
1720 ,cpi_attribute26 = g_allocation_row.cpi_attribute26
1721 ,cpi_attribute27 = g_allocation_row.cpi_attribute27
1722 ,cpi_attribute28 = g_allocation_row.cpi_attribute28
1723 ,cpi_attribute29 = g_allocation_row.cpi_attribute29
1724 ,cpi_attribute30 = g_allocation_row.cpi_attribute30
1725 where group_per_in_ler_id = g_allocation_row.group_per_in_ler_id;
1726 end if;
1727 if g_upd_pl_rate then
1728 ben_cwb_person_rates_api.update_person_rate
1729 (p_validate => false
1730 ,p_group_per_in_ler_id => g_allocation_row.group_per_in_ler_id
1731 ,p_pl_id => g_allocation_row.pl_id
1732 ,p_oipl_id => -1
1733 ,p_group_pl_id => g_allocation_row.group_pl_id
1734 ,p_group_oipl_id => -1
1735 ,p_lf_evt_ocrd_dt => g_allocation_row.lf_evt_ocrd_dt
1736 ,p_ws_val => g_allocation_row.ws_val
1737 ,p_stat_sal_val => g_allocation_row.stat_sal_val
1738 ,p_oth_comp_val => g_allocation_row.oth_comp_val
1739 ,p_tot_comp_val => g_allocation_row.tot_comp_val
1740 ,p_misc1_val => g_allocation_row.misc1_val
1741 ,p_misc2_val => g_allocation_row.misc2_val
1742 ,p_misc3_val => g_allocation_row.misc3_val
1743 ,p_rec_val => g_allocation_row.rec_val
1744 ,p_object_version_number => g_allocation_row.ovn_pl);
1745 l_save_summary := true;
1746 end if;
1747 if g_upd_opt1_rate then
1748 ben_cwb_person_rates_api.update_person_rate
1749 (p_validate => false
1750 ,p_group_per_in_ler_id => g_allocation_row.group_per_in_ler_id
1751 ,p_pl_id => g_allocation_row.pl_id
1752 ,p_oipl_id => g_allocation_row.oipl_id_opt1
1753 ,p_group_pl_id => g_allocation_row.group_pl_id
1754 ,p_group_oipl_id => g_allocation_row.group_oipl_id_opt1
1755 ,p_lf_evt_ocrd_dt => g_allocation_row.lf_evt_ocrd_dt
1756 ,p_ws_val => g_allocation_row.ws_val_opt1
1757 ,p_stat_sal_val => g_allocation_row.stat_sal_val_opt1
1758 ,p_oth_comp_val => g_allocation_row.oth_comp_val_opt1
1759 ,p_tot_comp_val => g_allocation_row.tot_comp_val_opt1
1760 ,p_misc1_val => g_allocation_row.misc1_val_opt1
1761 ,p_misc2_val => g_allocation_row.misc2_val_opt1
1762 ,p_misc3_val => g_allocation_row.misc3_val_opt1
1763 ,p_rec_val => g_allocation_row.rec_val_opt1
1764 ,p_object_version_number => g_allocation_row.ovn_opt1);
1765 l_save_summary := true;
1766 end if;
1767 if g_upd_opt2_rate then
1768 ben_cwb_person_rates_api.update_person_rate
1769 (p_validate => false
1770 ,p_group_per_in_ler_id => g_allocation_row.group_per_in_ler_id
1771 ,p_pl_id => g_allocation_row.pl_id
1772 ,p_oipl_id => g_allocation_row.oipl_id_opt2
1773 ,p_group_pl_id => g_allocation_row.group_pl_id
1774 ,p_group_oipl_id => g_allocation_row.group_oipl_id_opt2
1775 ,p_lf_evt_ocrd_dt => g_allocation_row.lf_evt_ocrd_dt
1776 ,p_ws_val => g_allocation_row.ws_val_opt2
1777 ,p_stat_sal_val => g_allocation_row.stat_sal_val_opt2
1778 ,p_oth_comp_val => g_allocation_row.oth_comp_val_opt2
1779 ,p_tot_comp_val => g_allocation_row.tot_comp_val_opt2
1780 ,p_misc1_val => g_allocation_row.misc1_val_opt2
1781 ,p_misc2_val => g_allocation_row.misc2_val_opt2
1782 ,p_misc3_val => g_allocation_row.misc3_val_opt2
1783 ,p_rec_val => g_allocation_row.rec_val_opt2
1784 ,p_object_version_number => g_allocation_row.ovn_opt2);
1785 l_save_summary := true;
1786 end if;
1787 if g_upd_opt3_rate then
1788 ben_cwb_person_rates_api.update_person_rate
1789 (p_validate => false
1790 ,p_group_per_in_ler_id => g_allocation_row.group_per_in_ler_id
1791 ,p_pl_id => g_allocation_row.pl_id
1792 ,p_oipl_id => g_allocation_row.oipl_id_opt3
1793 ,p_group_pl_id => g_allocation_row.group_pl_id
1794 ,p_group_oipl_id => g_allocation_row.group_oipl_id_opt3
1795 ,p_lf_evt_ocrd_dt => g_allocation_row.lf_evt_ocrd_dt
1796 ,p_ws_val => g_allocation_row.ws_val_opt3
1797 ,p_stat_sal_val => g_allocation_row.stat_sal_val_opt3
1798 ,p_oth_comp_val => g_allocation_row.oth_comp_val_opt3
1799 ,p_tot_comp_val => g_allocation_row.tot_comp_val_opt3
1800 ,p_misc1_val => g_allocation_row.misc1_val_opt3
1801 ,p_misc2_val => g_allocation_row.misc2_val_opt3
1802 ,p_misc3_val => g_allocation_row.misc3_val_opt3
1803 ,p_rec_val => g_allocation_row.rec_val_opt3
1804 ,p_object_version_number => g_allocation_row.ovn_opt3);
1805 l_save_summary := true;
1806 end if;
1807 if g_upd_opt4_rate then
1808 ben_cwb_person_rates_api.update_person_rate
1809 (p_validate => false
1810 ,p_group_per_in_ler_id => g_allocation_row.group_per_in_ler_id
1811 ,p_pl_id => g_allocation_row.pl_id
1812 ,p_oipl_id => g_allocation_row.oipl_id_opt4
1813 ,p_group_pl_id => g_allocation_row.group_pl_id
1814 ,p_group_oipl_id => g_allocation_row.group_oipl_id_opt4
1815 ,p_lf_evt_ocrd_dt => g_allocation_row.lf_evt_ocrd_dt
1816 ,p_ws_val => g_allocation_row.ws_val_opt4
1817 ,p_stat_sal_val => g_allocation_row.stat_sal_val_opt4
1818 ,p_oth_comp_val => g_allocation_row.oth_comp_val_opt4
1819 ,p_tot_comp_val => g_allocation_row.tot_comp_val_opt4
1820 ,p_misc1_val => g_allocation_row.misc1_val_opt4
1821 ,p_misc2_val => g_allocation_row.misc2_val_opt4
1822 ,p_misc3_val => g_allocation_row.misc3_val_opt4
1823 ,p_rec_val => g_allocation_row.rec_val_opt4
1824 ,p_object_version_number => g_allocation_row.ovn_opt4);
1825 l_save_summary := true;
1826 end if;
1827 if l_save_summary then
1828 ben_cwb_summary_pkg.save_pl_sql_tab;
1829 end if;
1830
1831 end save_data;
1832
1833 --
1834 -- Public procedure
1835 --
1836
1837 -- --------------------------------------------------------------------------
1838 -- |--------------------< run_dynamic_calculations >------------------------|
1839 -- --------------------------------------------------------------------------
1840 procedure run_dynamic_calculations(p_group_per_in_ler_id in number
1841 ,p_group_pl_id in number
1842 ,p_lf_evt_ocrd_dt in date
1843 ,p_raise_error in boolean default false) is
1844 l_calc_details_count integer := 0;
1845 l_old_rslt_col varchar2(30) := 'zzzzz';
1846 l_rslt_col varchar2(30);
1847 begin
1848 l_calc_details_count := load_calc_details(p_group_pl_id);
1849 if l_calc_details_count = 0 then
1850 return;
1851 end if;
1852 --
1853 g_upd_person_info := false;
1854 g_upd_pl_rate := false;
1855 g_upd_opt1_rate := false;
1856 g_upd_opt2_rate := false;
1857 g_upd_opt3_rate := false;
1858 g_upd_opt4_rate := false;
1859 --
1860 load_plan_info(p_group_pl_id, p_lf_evt_ocrd_dt);
1861 load_allocation_row(p_group_per_in_ler_id);
1862
1863 -- 6024581: if group_per_in_ler_id is not null, then save the data.
1864
1865 if (g_allocation_row.group_per_in_ler_id is not null) then
1866 for i in 1..g_calc_details.count loop
1867 l_rslt_col := g_calc_details(i).rslt_col;
1868 if (l_rslt_col <> l_old_rslt_col) then
1869 execute_column(p_group_per_in_ler_id, l_rslt_col, i, p_raise_error);
1870 l_old_rslt_col := l_rslt_col;
1871 end if;
1872 end loop;
1873
1874 save_data;
1875 end if;
1876
1877 exception
1878 when others then
1879 hr_utility.set_location('run_dynamic_calculations '||p_group_per_in_ler_id,999);
1880 raise;
1881
1882 end run_dynamic_calculations;
1883
1884 end ben_cwb_dyn_calc_pkg;
1885