[Home] [Help]
PACKAGE BODY: APPS.PQH_RBC_RATE_RETRIEVAL
Source
1 Package Body pqh_rbc_rate_retrieval as
2 /* $Header: pqrbcpkg.pkb 120.10.12010000.1 2008/07/28 13:06:28 appldev ship $ */
3 --
4 g_package varchar2(33) := 'pqh_rbc_rate_retrieval.'; -- Global package name
5 --
6 g_person_id number(15):= null;
7 g_assignment_id number(15):= null;
8 g_element_type_id number(15):= null;
9 g_business_group_id number(15):= null;
10 g_criteria_rate_defn_id number(15):= null;
11 --
12 g_asg_rec per_all_assignments_f%ROWTYPE;
13 g_per_rec per_all_people_f%ROWTYPE;
14 g_empty_tab ff_exec.outputs_t; -- donot populate. Only to be used a default value
15 --
16 --
17 Type tc_criteria is record(
18 short_code ben_eligy_criteria.short_code%type,
19 crit_col1_datatype ben_eligy_criteria.crit_col1_datatype%type,
20 time_entry_access_table_name1 ben_eligy_criteria.time_entry_access_table_name1%type,
21 time_entry_access_col_name1 ben_eligy_criteria.time_entry_access_col_name1%type,
22 crit_col2_datatype ben_eligy_criteria.crit_col2_datatype%type,
23 time_entry_access_table_name2 ben_eligy_criteria.time_entry_access_table_name2%type,
24 time_entry_access_col_name2 ben_eligy_criteria.time_entry_access_col_name2%type);
25 --
26 Type tc_criteria_tbl is table of tc_criteria index by binary_integer;
27 --
28 --
29 --
30 -------------------------< exec_pref_rate_formula >----------------------------------
31 --
32 -- Function to execute the preferential rate formula for a person, and return the rate.
33 -- Preferential rate formula's return 4 outputs in the following order: Minimum rate,
34 -- Mid-Value Rate, Maximum rate and Default Rate.
35 -- Inputs values are Person_id, criteria_rate_defn_id.
36 --
37 function exec_pref_rate_formula
38 (p_formula_id in number,
39 p_effective_date in date,
40 p_param1 in varchar2 ,
41 p_param1_value in varchar2 ,
42 p_param2 in varchar2 ,
43 p_param2_value in varchar2 ,
44 p_param3 in varchar2 default null,
45 p_param3_value in varchar2 default null,
46 p_param4 in varchar2 default null,
47 p_param4_value in varchar2 default null,
48 p_param5 in varchar2 default null,
49 p_param5_value in varchar2 default null,
50 p_param6 in varchar2 default null,
51 p_param6_value in varchar2 default null,
52 p_param7 in varchar2 default null,
53 p_param7_value in varchar2 default null,
54 p_param8 in varchar2 default null,
55 p_param8_value in varchar2 default null,
56 p_param9 in varchar2 default null,
57 p_param9_value in varchar2 default null,
58 p_param10 in varchar2 default null,
59 p_param10_value in varchar2 default null,
60 p_param_tab in ff_exec.outputs_t default g_empty_tab
61 )
62 return ff_exec.outputs_t is
63 --
64 l_proc varchar2(80) := 'exec_pref_rate_formula';
65 l_inputs ff_exec.inputs_t;
66 l_outputs ff_exec.outputs_t;
67 j int;
68 l_param_tab_count number;
69 --
70 l_organization_id per_all_assignments_f.organization_id%type;
71 l_payroll_id per_all_assignments_f.payroll_id%type;
72 l_jurisdiction_code varchar2(150);
73 --
74 cursor csr_asg_details(p_asg_id in number) is
75 Select organization_id, payroll_id
76 From per_all_assignments_f
77 Where assignment_id = p_asg_id
78 and p_effective_date between effective_start_date and effective_end_date;
79 begin
80 --
81 hr_utility.set_location ('Entering '||l_proc,10);
82 --
83 ff_exec.init_formula
84 (p_formula_id => p_formula_id,
85 p_effective_date => p_effective_date,
86 p_inputs => l_inputs,
87 p_outputs => l_outputs);
88 --
89 hr_utility.set_location ('After Init Formula '||l_proc,10);
90 --
91 l_param_tab_count := p_param_tab.count;
92 --
93 -- Account for case where formula has no contexts or inputs
94 --
95 for l_count in nvl(l_inputs.first,0)..nvl(l_inputs.last,-1) loop
96 --
97 hr_utility.set_location ('Current Context'||l_inputs(l_count).name,10);
98 --
99 if l_inputs(l_count).name = 'BUSINESS_GROUP_ID' then
100 --
101 l_inputs(l_count).value := nvl(g_business_group_id, -1);
102 --
103 elsif l_inputs(l_count).name = 'PAYROLL_ID' then
104 --
105 l_inputs(l_count).value := nvl(l_payroll_id,-1);
106 --
107 --
108 elsif l_inputs(l_count).name = 'ASSIGNMENT_ID' then
109 --
110 l_inputs(l_count).value := nvl(g_assignment_id, -1);
111 --
112 --
113 elsif l_inputs(l_count).name = 'ORGANIZATION_ID' then
114 --
115 l_inputs(l_count).value := nvl(l_organization_id, -1);
116 --
117 elsif l_inputs(l_count).name = 'JURISDICTION_CODE' then
118 --
119 l_inputs(l_count).value := nvl(l_jurisdiction_code, 'xx');
120 --
121 --
122 elsif l_inputs(l_count).name = 'DATE_EARNED' then
123 --
124 -- Note that you must pass the date as a string, that is because
125 -- of the canonical date change of 11.5
126 --
127 -- hr_utility.set_location ('Date Earned '||to_char(p_effective_date),10);
128 -- Still the fast formula does't accept the full canonical form.
129 -- l_inputs(l_count).value := fnd_date.date_to_canonical(p_effective_date);
130 l_inputs(l_count).value := to_char(p_effective_date, 'YYYY/MM/DD');
131 --
132 elsif l_param_tab_count >0 then
133
134 for j in 1..l_param_tab_count
135 loop
136 if l_inputs(l_count).name = p_param_tab(j).name then
137 l_inputs(l_count).value := p_param_tab(j).value;
138 end if;
139 end loop;
140 elsif l_inputs(l_count).name = p_param1 then
141 --
142 l_inputs(l_count).value := p_param1_value;
143 --
144 elsif l_inputs(l_count).name = p_param2 then
145 --
146 l_inputs(l_count).value := p_param2_value;
147 --
148 elsif l_inputs(l_count).name = p_param3 then
149 --
150 l_inputs(l_count).value := p_param3_value;
151 --
152 elsif l_inputs(l_count).name = p_param4 then
153 --
154 l_inputs(l_count).value := p_param4_value;
155 --
156 elsif l_inputs(l_count).name = p_param5 then
157 --
158 l_inputs(l_count).value := p_param5_value;
159 --
160 elsif l_inputs(l_count).name = p_param6 then
161 --
162 l_inputs(l_count).value := p_param6_value;
163 --
164 elsif l_inputs(l_count).name = p_param7 then
165 --
166 l_inputs(l_count).value := p_param7_value;
167 --
168 elsif l_inputs(l_count).name = p_param8 then
169 --
170 l_inputs(l_count).value := p_param8_value;
171 --
172 elsif l_inputs(l_count).name = p_param9 then
173 --
174 l_inputs(l_count).value := p_param9_value;
175 --
176 elsif l_inputs(l_count).name = p_param10 then
177 --
178 l_inputs(l_count).value := p_param10_value;
179 end if;
180 --
181 end loop;
182 --
183 -- We have loaded the input record. Now run the formula.
184 --
185 ff_exec.run_formula(p_inputs => l_inputs,
186 p_outputs => l_outputs,
187 p_use_dbi_cache => false); -- bug# 2430017
188 --
189 hr_utility.set_location ('Leaving '||l_proc,10);
190 --
191 return l_outputs;
192 --
193 end exec_pref_rate_formula;
194
195 -------------------------< apply_pref_rate_code >----------------------------------
196 --
197 -- Function to return the preferential rate for a person, for a given rate type.
198 --
199 Procedure apply_pref_rate_code
200 (p_crit_rt_defn_id IN number,
201 p_eligible_rates IN ben_evaluate_rate_matrix.rate_tab,
202 p_effective_date IN date,
203 p_preferential_rate_cd IN varchar2,
204 p_preferential_rate_rl IN varchar2,
205 p_preferential_min_rt OUT nocopy number,
206 p_preferential_mid_rt OUT nocopy number,
207 p_preferential_max_rt OUT nocopy number,
208 p_preferential_rt OUT nocopy number,
209 p_rate_factors OUT nocopy g_rbc_factor_tbl,
210 p_rate_factor_cnt OUT nocopy number)
211 is
212 --
213 l_proc varchar2(72) := g_package||'apply_pref_rate_code';
214 --
215 l_rt_counter number:= 0;
216 l_rl_rt_counter number:= 0;
217 l_rate number := 0;
218 rec_no number;
219 l_cnt number:= 0;
220 l_outputs ff_exec.outputs_t;
221 l_param_tab ff_exec.outputs_t;
222 l_rate_matrix_rate_id pqh_rate_matrix_rates_f.rate_matrix_rate_id%type;
223 --
224 -- Declare local procedure
225 --
226 Procedure populate_param_tab
227 (p_name in varchar2,
228 p_value in varchar2) is
229 --
230 l_next_index number;
231 --
232 begin
233 --
234 l_next_index := nvl(l_param_tab.count,0) + 1;
235 l_param_tab(l_next_index).name := p_name;
236 l_param_tab(l_next_index).value := p_value;
237 --
238 end;
239 --
240 Begin
241 --
242 hr_utility.set_location('Entering:'||l_proc, 5);
243 --
244 l_rt_counter := p_eligible_rates.count;
245 hr_utility.set_location('Total Eligible Rates:'||to_char(l_rt_counter), 6);
246 --
247 p_rate_factor_cnt := 0;
248 --
249 If p_preferential_rate_cd = 'HIGHEST' then
250 l_rate := p_eligible_rates(1).rate_value;
251 p_preferential_rt := p_eligible_rates(1).rate_value;
252 p_preferential_min_rt := p_eligible_rates(1).min_rate_value;
253 p_preferential_mid_rt := p_eligible_rates(1).mid_rate_value;
254 p_preferential_max_rt := p_eligible_rates(1).max_rate_value;
255 l_rate_matrix_rate_id := p_eligible_rates(1).rate_matrix_rate_id;
256 --
257 For rec_no in 1..l_rt_counter loop
258 If p_eligible_rates(rec_no).rate_value > l_rate then
259 l_rate := p_eligible_rates(rec_no).rate_value;
260 p_preferential_rt := p_eligible_rates(rec_no).rate_value;
261 p_preferential_min_rt := p_eligible_rates(rec_no).min_rate_value;
262 p_preferential_mid_rt := p_eligible_rates(rec_no).mid_rate_value;
263 p_preferential_max_rt := p_eligible_rates(rec_no).max_rate_value;
264 l_rate_matrix_rate_id := p_eligible_rates(rec_no).rate_matrix_rate_id;
265 End if;
266 End loop;
267 --
268 -- PAYROLL EVENT CHANGE
269 --
270 p_rate_factors(1).rate_matrix_rate_id := l_rate_matrix_rate_id;
271 p_rate_factors(1).default_rate := l_rate;
272 p_rate_factor_cnt := 1;
273 --
274 hr_utility.set_location('Highest Rate:'||to_char(l_rate), 6);
275 Elsif p_preferential_rate_cd = 'LOWEST' then
276 l_rate := p_eligible_rates(1).rate_value;
277 p_preferential_rt := p_eligible_rates(1).rate_value;
278 p_preferential_min_rt := p_eligible_rates(1).min_rate_value;
279 p_preferential_mid_rt := p_eligible_rates(1).mid_rate_value;
280 p_preferential_max_rt := p_eligible_rates(1).max_rate_value;
281 l_rate_matrix_rate_id := p_eligible_rates(1).rate_matrix_rate_id;
282 --
283 For rec_no in 1..l_rt_counter loop
284 If p_eligible_rates(rec_no).rate_value < l_rate then
285 l_rate := p_eligible_rates(rec_no).rate_value;
286 p_preferential_rt := p_eligible_rates(rec_no).rate_value;
287 p_preferential_min_rt := p_eligible_rates(rec_no).min_rate_value;
288 p_preferential_mid_rt := p_eligible_rates(rec_no).mid_rate_value;
289 p_preferential_max_rt := p_eligible_rates(rec_no).max_rate_value;
290 l_rate_matrix_rate_id := p_eligible_rates(rec_no).rate_matrix_rate_id;
291 End if;
292 End loop;
293 --
294 -- PAYROLL EVENT CHANGE
295 --
296 p_rate_factors(1).rate_matrix_rate_id := l_rate_matrix_rate_id;
297 p_rate_factors(1).default_rate := l_rate;
298 p_rate_factor_cnt := 1;
299 --
300 hr_utility.set_location('Lowest Rate:'||to_char(l_rate), 7);
301 Elsif p_preferential_rate_cd = 'AVERAGE' then
302 For rec_no in 1..l_rt_counter loop
303 p_preferential_rt := p_preferential_rt + p_eligible_rates(rec_no).rate_value;
304 p_preferential_min_rt := p_preferential_min_rt + p_eligible_rates(rec_no).min_rate_value;
305 p_preferential_mid_rt := p_preferential_mid_rt + p_eligible_rates(rec_no).mid_rate_value;
306 p_preferential_max_rt := p_preferential_max_rt + p_eligible_rates(rec_no).max_rate_value;
307 --
308 -- PAYROLL EVENT CHANGE
309 --
310 p_rate_factors(rec_no).rate_matrix_rate_id := p_eligible_rates(rec_no).rate_matrix_rate_id;
311 p_rate_factors(rec_no).default_rate := p_eligible_rates(rec_no).rate_value;
312 End loop;
313 p_preferential_rt := p_preferential_rt / l_rt_counter;
314 p_preferential_min_rt := p_preferential_min_rt / l_rt_counter;
315 p_preferential_mid_rt := p_preferential_mid_rt / l_rt_counter;
316 p_preferential_max_rt := p_preferential_max_rt / l_rt_counter;
317 l_rate := p_preferential_rt;
318 --
319 p_rate_factor_cnt := l_rt_counter;
320 --
321 hr_utility.set_location('Average Rate:'||to_char(l_rate), 8);
322 Elsif p_preferential_rate_cd = 'RULE' then
323 hr_utility.set_location('Calling fast formula', 9);
324 --
325 -- Setting fast formula inputs. We are reading only the first 5 eligible rates.
326 --
327 l_rl_rt_counter := l_rt_counter;
328 If l_rt_counter > 5 then
329 l_rl_rt_counter := 5;
330 End if;
331 --
332 For rec_no in 1..l_rl_rt_counter loop
333 populate_param_tab('RATE_MATRIX_RATE_ID'||rec_no,p_eligible_rates(rec_no).rate_matrix_rate_id);
334 populate_param_tab('MIN_'||rec_no,p_eligible_rates(rec_no).min_rate_value);
335 populate_param_tab('MID_'||rec_no,p_eligible_rates(rec_no).mid_rate_value);
336 populate_param_tab('MAX_'||rec_no,p_eligible_rates(rec_no).max_rate_value);
337 populate_param_tab('DFLT_'||rec_no,p_eligible_rates(rec_no).rate_value);
338
339 End loop;
340 --
341 /**
342 For rec_no in 1..l_param_tab.count loop
343 hr_utility.set_location('Name = '|| l_param_tab(rec_no).name,280);
344 hr_utility.set_location('Value = '|| l_param_tab(rec_no).value,280);
345 end loop;
346 **/
347 --
348 l_outputs := exec_pref_rate_formula
349 (p_formula_id => p_preferential_rate_rl,
350 p_effective_date => p_effective_date,
351 p_param1 => 'PQH_RBC_PERSON_ID',
352 p_param1_value => to_char(nvl(g_person_id, -1)),
353 p_param2 => 'PQH_RBC_CRIT_RATE_DEFN_ID',
354 p_param2_value => to_char(nvl(g_criteria_rate_defn_id, -1) ),
355 p_param_tab => l_param_tab );
356
357 for l_count in nvl(l_outputs.first,0)..nvl(l_outputs.last,-1) loop
358 --
359 hr_utility.set_location ('Current Context'||l_outputs(l_count).name,10);
360 --
361 if l_outputs(l_count).name = 'MIN' then
362 --
363 p_preferential_min_rt := l_outputs(l_count).value;
364 elsif l_outputs(l_count).name = 'MID' then
365 --
366 p_preferential_mid_rt := l_outputs(l_count).value;
367 elsif l_outputs(l_count).name = 'MAX' then
368 --
369 p_preferential_max_rt := l_outputs(l_count).value;
370 elsif l_outputs(l_count).name = 'DFLT' then
371 --
372 p_preferential_rt := l_outputs(l_count).value;
373 --
374 End if;
375 End loop;
376 --
377 Else
378 hr_utility.set_location('Invalid Preferential Rate Code:', 10);
379 p_preferential_rt := 0;
380 p_preferential_min_rt := 0;
381 p_preferential_mid_rt := 0;
382 p_preferential_max_rt := 0;
383 End if;
384 --
385 hr_utility.set_location('Leaving:'||l_proc, 20);
386 --
387 End;
388 -------------------------< calculate_preferential_rate >----------------------------------
389 --
390 -- Function to return the preferential rate for a person, for a given rate type.
391 --
392 Procedure calculate_preferential_rate
393 (p_crit_rt_defn_id IN number,
394 p_eligible_rates IN ben_evaluate_rate_matrix.rate_tab,
395 p_effective_date IN date,
396 p_rate_factors OUT nocopy g_rbc_factor_tbl,
397 p_rate_factor_cnt OUT nocopy number,
398 p_preferential_min_rt OUT nocopy number,
399 p_preferential_mid_rt OUT nocopy number,
400 p_preferential_max_rt OUT nocopy number,
401 p_preferential_rt OUT nocopy number)
402 is
403 --
404 l_rt_counter number:= 0;
405 l_temp_rate_tab ben_evaluate_rate_matrix.rate_tab;
406 l_highest_level pqh_rate_matrix_nodes.level_number%type;
407 rec_no number;
408 l_cnt number:= 0;
409 l_pref_rate_cd pqh_criteria_rate_defn.preferential_rate_cd%type;
410 l_pref_rate_rule pqh_criteria_rate_defn.preferential_rate_rule%type;
411 l_proc varchar2(72) := g_package||'calculate_preferential_rate';
412 --
413 Cursor csr_rate_type is
414 Select preferential_rate_cd, preferential_rate_rule
415 from pqh_criteria_rate_defn
416 Where criteria_rate_defn_id = p_crit_rt_defn_id;
417 --
418 Begin
419 --
420 hr_utility.set_location('Entering:'||l_proc, 5);
421 --
422 l_rt_counter := p_eligible_rates.count;
423 hr_utility.set_location('Total Eligible Rates:'||to_char(l_rt_counter), 6);
424 --
425 If l_rt_counter = 0 then
426 -- No matching rates found.
427 p_preferential_rt := 0;
428 p_preferential_min_rt := 0;
429 p_preferential_mid_rt := 0;
430 p_preferential_max_rt := 0;
431 Elsif l_rt_counter = 1 then
432 -- Just one matching rate found.
433 p_preferential_rt := p_eligible_rates(l_rt_counter).rate_value;
434 p_preferential_min_rt := p_eligible_rates(l_rt_counter).min_rate_value;
435 p_preferential_mid_rt := p_eligible_rates(l_rt_counter).mid_rate_value;
436 p_preferential_max_rt := p_eligible_rates(l_rt_counter).max_rate_value;
437 -- PAYROLL EVENT CHANGE
438 p_rate_factors(l_rt_counter).rate_matrix_rate_id := p_eligible_rates(l_rt_counter).rate_matrix_rate_id;
439 p_rate_factors(l_rt_counter).default_rate := p_eligible_rates(l_rt_counter).rate_value;
440 p_rate_factor_cnt := l_rt_counter;
441 --
442 Else
443 -- Multiple eligible rates found.
444 -- Loop through the eligible rates table and find the rates with highest priority
445 -- 1) Find highest level
446 l_highest_level := p_eligible_rates(1).level_number;
447 --
448 For rec_no in 1..l_rt_counter loop
449 If p_eligible_rates(rec_no).level_number > l_highest_level then
450 l_highest_level := p_eligible_rates(rec_no).level_number;
451 End if;
452 End loop;
453 hr_utility.set_location('Highest level number:'||to_char(l_highest_level), 6);
454 --
455 -- 2) Copy rates at the highest level into temporary table.
456 --
457 l_cnt := 0;
458 For rec_no in 1..l_rt_counter loop
459 If p_eligible_rates(rec_no).level_number = l_highest_level then
460 l_cnt := l_cnt + 1;
461 l_temp_rate_tab(l_cnt).rate_matrix_rate_id := p_eligible_rates(rec_no).rate_matrix_rate_id;
462 l_temp_rate_tab(l_cnt).min_rate_value := p_eligible_rates(rec_no).min_rate_value;
463 l_temp_rate_tab(l_cnt).mid_rate_value := p_eligible_rates(rec_no).mid_rate_value;
464 l_temp_rate_tab(l_cnt).max_rate_value := p_eligible_rates(rec_no).max_rate_value;
465 l_temp_rate_tab(l_cnt).rate_value := p_eligible_rates(rec_no).rate_value;
466 l_temp_rate_tab(l_cnt).level_number := p_eligible_rates(rec_no).level_number;
467 End if;
468 End loop;
469 hr_utility.set_location('Rates at highest level number:'||to_char(l_cnt), 6);
470 --
471 If l_cnt = 1 then
472 -- Only one eligible rate found at the highest level.
473 p_preferential_rt := l_temp_rate_tab(l_cnt).rate_value;
474 p_preferential_mid_rt := l_temp_rate_tab(l_cnt).mid_rate_value;
475 p_preferential_min_rt := l_temp_rate_tab(l_cnt).min_rate_value;
476 p_preferential_max_rt := l_temp_rate_tab(l_cnt).max_rate_value;
477 --
478 -- PAYROLL EVENT CHANGE
479 --
480 p_rate_factors(l_cnt).rate_matrix_rate_id := l_temp_rate_tab(l_cnt).rate_matrix_rate_id;
481 p_rate_factors(l_cnt).default_rate := l_temp_rate_tab(l_cnt).rate_value;
482 p_rate_factor_cnt := l_cnt;
483 --
484 Else
485 -- Multiple eligible rates found at highest level.
486 -- Apply preferential rate code or preferential rate rule.
487 l_pref_rate_cd := 'HIGHEST';
488 Open csr_rate_type;
489 Fetch csr_rate_type into l_pref_rate_cd, l_pref_rate_rule;
490 Close csr_rate_type;
491 --
492 apply_pref_rate_code
493 (p_crit_rt_defn_id => p_crit_rt_defn_id,
494 p_eligible_rates => l_temp_rate_tab,
495 p_effective_date => p_effective_date,
496 p_preferential_rate_cd => l_pref_rate_cd,
497 p_preferential_rate_rl => l_pref_rate_rule,
498 p_preferential_min_rt => p_preferential_min_rt,
499 p_preferential_mid_rt => p_preferential_mid_rt,
500 p_preferential_max_rt => p_preferential_max_rt,
501 p_preferential_rt => p_preferential_rt,
502 p_rate_factors => p_rate_factors,
503 p_rate_factor_cnt => p_rate_factor_cnt);
504 End if;
505 --
506 End if;
507 --
508 hr_utility.set_location('Eligible Rate:'||to_char(p_preferential_rt), 6);
509 hr_utility.set_location('Leaving:'||l_proc, 10);
510 --
511 End;
512 --
513 -------------------------< determine_crit_rate_defn_rt >----------------------------------
514 --
515 -- Function to return the rate for a given rate type. It calls the BEN procedure to get the
516 -- eligible rates for a given criteria set, marks the preferential rate and applies any
517 -- calculation method needed.
518 --
519 Procedure determine_crit_rate_defn_rt
520 (p_crit_rt_defn_id IN number,
521 p_person_id IN number default null,
522 p_assignment_id IN number default null,
523 p_business_group_id IN number,
524 p_effective_date IN date,
525 p_rate_factors OUT nocopy g_rbc_factor_tbl,
526 p_rate_factor_cnt OUT nocopy number,
527 p_min_rate OUT nocopy number,
528 p_mid_rate OUT nocopy number,
529 p_max_rate OUT nocopy number,
530 p_rate OUT nocopy number)
531 is
532 --
533 l_elig_rates ben_evaluate_rate_matrix.rate_tab;
534 l_pref_rate pqh_rate_matrix_rates_f.rate_value%type;
535 l_min_rate pqh_rate_matrix_rates_f.min_rate_value%type;
536 l_mid_rate pqh_rate_matrix_rates_f.mid_rate_value%type;
537 l_max_rate pqh_rate_matrix_rates_f.max_rate_value%type;
538 --
539 l_rate_factors g_rbc_factor_tbl;
540 l_rate_factor_cnt number := 0;
541
542 --
543 l_proc varchar2(72) := g_package||'determine_crit_rate_defn_rt';
544 --
545 Begin
546 --
547 hr_utility.set_location('Entering:'||l_proc, 5);
548 --
549 -- Find the eligible rates for the current rate type.
550 --
551 ben_env_object.init
552 (p_business_group_id => p_business_group_id,
553 p_effective_date => p_effective_date,
554 p_thread_id => null,
555 p_chunk_size => null,
556 p_threads => null,
557 p_max_errors => null,
558 p_benefit_action_id => null);
559 --
560 ben_evaluate_rate_matrix.determine_rate
561 (p_person_id => p_person_id,
562 p_assignment_id => p_assignment_id,
563 p_criteria_rate_defn_id => p_crit_rt_defn_id,
564 p_effective_date => p_effective_date,
565 p_business_group_id => p_business_group_id,
566 p_rate_tab => l_elig_rates);
567
568 --
569 -- Find the preferential rate;
570 --
571 l_pref_rate := 0;
572 --
573 calculate_preferential_rate
574 (p_crit_rt_defn_id => p_crit_rt_defn_id,
575 p_eligible_rates => l_elig_rates,
576 p_effective_date => p_effective_date,
577 p_preferential_rt => l_pref_rate,
578 p_preferential_min_rt => l_min_rate,
579 p_preferential_mid_rt => l_mid_rate,
580 p_preferential_max_rt => l_max_rate,
581 p_rate_factors => p_rate_factors,
582 p_rate_factor_cnt => p_rate_factor_cnt);
583 --
584 p_rate := l_pref_rate;
585 p_min_rate := l_min_rate;
586 p_mid_rate := l_mid_rate;
587 p_max_rate := l_max_rate;
588 --
589 hr_utility.set_location('Leaving:'||l_proc, 10);
590 --
591 End;
592 --
593 --
594 -------------------------< exec_rate_calc_formula >----------------------------------
595 --
596 -- Function to execute the rate calculation formula.The formula's return 4 outputs in the following
597 -- order: Minimum rate, Mid-Value Rate, Maximum rate and Default Rate.
598 -- Inputs values are Person_id, criteria_rate_defn_id.
599 --
600 function exec_rate_calc_formula
601 (p_formula_id in number,
602 p_effective_date in date,
603 p_param1 in varchar2 ,
604 p_param1_value in varchar2 ,
605 p_param2 in varchar2 ,
606 p_param2_value in varchar2 ,
607 p_param3 in varchar2 default null,
608 p_param3_value in varchar2 default null,
609 p_param4 in varchar2 default null,
610 p_param4_value in varchar2 default null,
611 p_param5 in varchar2 default null,
612 p_param5_value in varchar2 default null,
613 p_param6 in varchar2 default null,
614 p_param6_value in varchar2 default null,
615 p_param7 in varchar2 default null,
616 p_param7_value in varchar2 default null,
617 p_param8 in varchar2 default null,
618 p_param8_value in varchar2 default null,
619 p_param9 in varchar2 default null,
620 p_param9_value in varchar2 default null,
621 p_param10 in varchar2 default null,
622 p_param10_value in varchar2 default null,
623 p_param_tab in ff_exec.outputs_t default g_empty_tab
624 )
625 return ff_exec.outputs_t is
626 --
627 l_proc varchar2(80) := 'exec_rate_calc_formula';
628 l_inputs ff_exec.inputs_t;
629 l_outputs ff_exec.outputs_t;
630 j int;
631 l_param_tab_count number;
632 --
633 l_organization_id per_all_assignments_f.organization_id%type;
634 l_payroll_id per_all_assignments_f.payroll_id%type;
635 l_jurisdiction_code varchar2(150);
636 --
637 cursor csr_asg_details(p_asg_id in number) is
638 Select organization_id, payroll_id
639 From per_all_assignments_f
640 Where assignment_id = p_asg_id
641 and p_effective_date between effective_start_date and effective_end_date;
642 begin
643 --
644 hr_utility.set_location ('Entering '||l_proc,10);
645 --
646 ff_exec.init_formula
647 (p_formula_id => p_formula_id,
648 p_effective_date => p_effective_date,
649 p_inputs => l_inputs,
650 p_outputs => l_outputs);
651 --
652 hr_utility.set_location ('After Init Formula '||l_proc,10);
653 --
654 l_param_tab_count := p_param_tab.count;
655 --
656 -- Account for case where formula has no contexts or inputs
657 --
658 for l_count in nvl(l_inputs.first,0)..nvl(l_inputs.last,-1) loop
659 --
660 hr_utility.set_location ('Current Context'||l_inputs(l_count).name,10);
661 --
662 if l_inputs(l_count).name = 'BUSINESS_GROUP_ID' then
663 --
664 l_inputs(l_count).value := nvl(g_business_group_id, -1);
665 --
666 elsif l_inputs(l_count).name = 'PAYROLL_ID' then
667 --
668 l_inputs(l_count).value := nvl(l_payroll_id,-1);
669 --
670 --
671 elsif l_inputs(l_count).name = 'ASSIGNMENT_ID' then
672 --
673 l_inputs(l_count).value := nvl(g_assignment_id, -1);
674 --
675 --
676 elsif l_inputs(l_count).name = 'ORGANIZATION_ID' then
677 --
678 l_inputs(l_count).value := nvl(l_organization_id, -1);
679 --
680 elsif l_inputs(l_count).name = 'JURISDICTION_CODE' then
681 --
682 l_inputs(l_count).value := nvl(l_jurisdiction_code, 'xx');
683 --
684 --
685 elsif l_inputs(l_count).name = 'DATE_EARNED' then
686 --
687 -- Note that you must pass the date as a string, that is because
688 -- of the canonical date change of 11.5
689 --
690 -- hr_utility.set_location ('Date Earned '||to_char(p_effective_date),10);
691 -- Still the fast formula does't accept the full canonical form.
692 -- l_inputs(l_count).value := fnd_date.date_to_canonical(p_effective_date);
693 l_inputs(l_count).value := to_char(p_effective_date, 'YYYY/MM/DD');
694 --
695 elsif l_param_tab_count >0 then
696
697 for j in 1..l_param_tab_count
698 loop
699 if l_inputs(l_count).name = p_param_tab(j).name then
700 l_inputs(l_count).value := p_param_tab(j).value;
701 end if;
702 end loop;
703 elsif l_inputs(l_count).name = p_param1 then
704 --
705 l_inputs(l_count).value := p_param1_value;
706 --
707 elsif l_inputs(l_count).name = p_param2 then
708 --
709 l_inputs(l_count).value := p_param2_value;
710 --
711 elsif l_inputs(l_count).name = p_param3 then
712 --
713 l_inputs(l_count).value := p_param3_value;
714 --
715 elsif l_inputs(l_count).name = p_param4 then
716 --
717 l_inputs(l_count).value := p_param4_value;
718 --
719 elsif l_inputs(l_count).name = p_param5 then
720 --
721 l_inputs(l_count).value := p_param5_value;
722 --
723 elsif l_inputs(l_count).name = p_param6 then
724 --
725 l_inputs(l_count).value := p_param6_value;
726 --
727 elsif l_inputs(l_count).name = p_param7 then
728 --
729 l_inputs(l_count).value := p_param7_value;
730 --
731 elsif l_inputs(l_count).name = p_param8 then
732 --
733 l_inputs(l_count).value := p_param8_value;
734 --
735 elsif l_inputs(l_count).name = p_param9 then
736 --
737 l_inputs(l_count).value := p_param9_value;
738 --
739 elsif l_inputs(l_count).name = p_param10 then
740 --
741 l_inputs(l_count).value := p_param10_value;
742 end if;
743 --
744 end loop;
745 --
746 -- We have loaded the input record. Now run the formula.
747 --
748 ff_exec.run_formula(p_inputs => l_inputs,
749 p_outputs => l_outputs,
750 p_use_dbi_cache => false); -- bug# 2430017
751 --
752 hr_utility.set_location ('Leaving '||l_proc,10);
753 --
754 return l_outputs;
755 --
756 end exec_rate_calc_formula;
757
758
759 -------------------------< calculate_crit_rate_defn_rt >----------------------------------
760 --
761 -- Function to return the rate for a given rate type. It calls the BEN procedure to get the
762 -- eligible rates for a given criteria set, marks the preferential rate and applies any
763 -- calculation method needed.
764 --
765 Procedure calculate_crit_rate_defn_rt
766 (p_crit_rt_defn_id IN number,
767 p_person_id IN number default null,
768 p_assignment_id IN number default null,
769 p_business_group_id IN number,
770 p_effective_date IN date,
771 p_rate_factors OUT nocopy g_rbc_factor_tbl,
772 p_rate_factor_cnt OUT nocopy number,
773 p_min_rate OUT nocopy number,
774 p_mid_rate OUT nocopy number,
775 p_max_rate OUT nocopy number,
776 p_rate OUT nocopy number)
777 is
778 --
779 l_parent_rates ben_evaluate_rate_matrix.rate_tab;
780 --
781 l_ref_period_cd pqh_criteria_rate_defn.reference_period_cd%type;
782 l_rate_calc_cd pqh_criteria_rate_defn.rate_calc_cd%type;
783 l_rate_calc_rule pqh_criteria_rate_defn.rate_calc_rule%type;
784 l_rounding_cd pqh_criteria_rate_defn.rounding_cd%type;
785 l_rounding_rule pqh_criteria_rate_defn.rounding_rule%type;
786 l_uom pqh_criteria_rate_defn.uom%type;
787 l_currency_code pqh_criteria_rate_defn.currency_code%type;
788 l_reference_period_cd pqh_criteria_rate_defn.reference_period_cd%type;
789 --
790 l_dflt_rate pqh_rate_matrix_rates_f.rate_value%type := 0;
791 l_min_rate pqh_rate_matrix_rates_f.min_rate_value%type := 0;
792 l_mid_rate pqh_rate_matrix_rates_f.mid_rate_value%type := 0;
793 l_max_rate pqh_rate_matrix_rates_f.max_rate_value%type := 0;
794 --
795 l_t_dflt_rate pqh_rate_matrix_rates_f.rate_value%type := 0;
796 l_t_min_rate pqh_rate_matrix_rates_f.min_rate_value%type := 0;
797 l_t_mid_rate pqh_rate_matrix_rates_f.mid_rate_value%type := 0;
798 l_t_max_rate pqh_rate_matrix_rates_f.max_rate_value%type := 0;
799 l_t_rate_calc_cd pqh_criteria_rate_defn.rate_calc_cd%type;
800 l_t_rate_calc_rule pqh_criteria_rate_defn.rate_calc_rule%type;
801 l_t_rounding_cd pqh_criteria_rate_defn.rounding_cd%type;
802 l_t_rounding_rule pqh_criteria_rate_defn.rounding_rule%type;
803 l_t_ref_period_cd pqh_criteria_rate_defn.reference_period_cd%type;
804 l_t_uom pqh_criteria_rate_defn.uom%type;
805 l_t_currency_code pqh_criteria_rate_defn.currency_code%type;
806 l_t_reference_period_cd pqh_criteria_rate_defn.reference_period_cd%type;
807 --
808 l_cnt number := 0;
809 l_t_cnt number := 0;
810 --
811 l_freq_conv number := 0;
812 l_curr_conv number := 0;
813 l_rt_freq_ann number := 0;
814 l_ref_freq_ann number := 0;
815 --
816 l_rate_factors g_rbc_factor_tbl;
817 l_rate_factor_cnt number := 0;
818 l_dummy_rt_fact_cnt number := 0;
819 --
820 Cursor csr_parent_rates is
821 Select parent_criteria_rate_defn_id
822 From pqh_criteria_rate_factors
823 Where criteria_rate_defn_id = p_crit_rt_defn_id
824 and business_group_id = p_business_group_id;
825 --
826 Cursor csr_crd_details(p_crit_rt_defn_id in number) is
827 Select rate_calc_cd, rate_calc_rule,rounding_cd,rounding_rule,uom,currency_code,reference_period_cd
828 from pqh_criteria_rate_defn
829 Where criteria_rate_defn_id = p_crit_rt_defn_id
830 and business_group_id = p_business_group_id;
831 --
832 l_proc varchar2(72) := g_package||'calculate_crit_rate_defn_rt';
833 l_outputs ff_exec.outputs_t;
834 --
835 Begin
836 --
837 hr_utility.set_location('Entering:'||l_proc, 5);
838 --
839 -- Get the calculation method for the rate type. For Norway, the calc method is flat amt.
840 --
841 Open csr_crd_details(p_crit_rt_defn_id);
842 Fetch csr_crd_details into l_rate_calc_cd, l_rate_calc_rule, l_rounding_cd, l_rounding_rule, l_uom,l_currency_code,l_ref_period_cd;
843 Close csr_crd_details;
844 --
845 -- Find the current rate value.
846 --
847 -- p_rate_factors := null;
848 p_rate_factor_cnt := 0;
849 -- l_rate_factors := null;
850 l_rate_factor_cnt := 0;
851 l_dummy_rt_fact_cnt:= 0;
852 --
853 If l_rate_calc_cd = 'AMOUNT' OR
854 l_rate_calc_cd = 'PERCENT' OR
855 l_rate_calc_cd = 'ADD_TO' then
856 --
857 determine_crit_rate_defn_rt
858 (p_crit_rt_defn_id => p_crit_rt_defn_id,
859 p_person_id => p_person_id,
860 p_assignment_id => p_assignment_id,
861 p_business_group_id => p_business_group_id,
862 p_effective_date => p_effective_date,
863 p_rate_factors => l_rate_factors,
864 p_rate_factor_cnt => l_rate_factor_cnt,
865 p_rate => l_dflt_rate,
866 p_min_rate => l_min_rate,
867 p_mid_rate => l_mid_rate,
868 p_max_rate => l_max_rate);
869 --
870 For rec_no in 1..l_rate_factor_cnt loop
871 p_rate_factors(rec_no).rate_matrix_rate_id := l_rate_factors(rec_no).rate_matrix_rate_id;
872 p_rate_factors(rec_no).default_rate := l_rate_factors(rec_no).default_rate;
873 End loop;
874 l_dummy_rt_fact_cnt := l_rate_factor_cnt;
875 p_rate_factor_cnt := l_dummy_rt_fact_cnt;
876 --
877 End if;
878 --
879 -- Find Rate Value for parents
880 --
881 If l_rate_calc_cd = 'SUM' OR
882 l_rate_calc_cd = 'PERCENT' OR
883 l_rate_calc_cd = 'ADD_TO' then
884 --
885 -- If current rate type depends on other rate types, find the rate for the parent rate types.
886 --
887 For parent_crd_rec in csr_parent_rates loop
888 --
889 l_cnt := l_cnt + 1;
890 --
891 l_t_dflt_rate := 0;
892 l_t_min_rate := 0;
893 l_t_mid_rate := 0;
894 l_t_max_rate := 0;
895 l_t_ref_period_cd := null;
896 l_t_uom := null;
897 l_t_currency_code := null;
898 l_t_reference_period_cd := null;
899
900 --
901 -- l_rate_factors := null;
902 l_rate_factor_cnt := 0;
903 --
904 determine_crit_rate_defn_rt
905 (p_crit_rt_defn_id => parent_crd_rec.parent_criteria_rate_defn_id,
906 p_person_id => p_person_id,
907 p_assignment_id => p_assignment_id,
908 p_business_group_id => p_business_group_id,
909 p_effective_date => p_effective_date,
910 p_rate_factors => l_rate_factors,
911 p_rate_factor_cnt => l_rate_factor_cnt,
912 p_rate => l_t_dflt_rate,
913 p_min_rate => l_t_min_rate,
914 p_mid_rate => l_t_mid_rate,
915 p_max_rate => l_t_max_rate);
916 --
917 For rec_no in 1..l_rate_factor_cnt loop
918 l_dummy_rt_fact_cnt := l_dummy_rt_fact_cnt + 1;
919 p_rate_factors(l_dummy_rt_fact_cnt).rate_matrix_rate_id := l_rate_factors(rec_no).rate_matrix_rate_id;
920 p_rate_factors(l_dummy_rt_fact_cnt).default_rate := l_rate_factors(rec_no).default_rate;
921 End loop;
922 p_rate_factor_cnt := l_dummy_rt_fact_cnt;
923 --
924 --
925 -- Convert at this point
926 --
927 Open csr_crd_details(parent_crd_rec.parent_criteria_rate_defn_id);
928 Fetch csr_crd_details into l_t_rate_calc_cd, l_t_rate_calc_rule, l_t_rounding_cd, l_t_rounding_rule, l_t_uom,l_t_currency_code,l_t_ref_period_cd;
929 Close csr_crd_details;
930 --
931 If l_uom = 'M' and l_t_uom = 'M' then
932 -- get the conv factor between frequencies
933 if l_t_ref_period_cd <> l_ref_period_cd then
934 l_rt_freq_ann := PQH_RBC_STAGE.get_annual_factor(l_t_ref_period_cd);
935 l_ref_freq_ann := PQH_RBC_STAGE.get_annual_factor(l_ref_period_cd);
936 l_freq_conv := l_rt_freq_ann/l_ref_freq_ann;
937 else
938 l_freq_conv := 1;
939 end if;
940 hr_utility.set_location('freq conv fctr is '||l_freq_conv,46);
941 if l_t_currency_code <> l_currency_code then
942 -- get the conv factor between currencies from gl_daily_rates
943 begin
944 select conversion_rate
945 into l_curr_conv
946 from gl_daily_rates
947 where from_currency = l_t_currency_code
948 and to_currency = l_currency_code
949 and conversion_date = (select max(conversion_date)
950 from gl_daily_rates
951 where from_currency = l_t_currency_code
952 and to_currency = l_currency_code
953 and conversion_date <= p_effective_date);
954 exception
955 when no_data_found then
956 hr_utility.set_location('rates not exist',25);
957 l_curr_conv := 1;
958 when others then
959 hr_utility.set_location('daily rates pull error',25);
960 raise;
961 end;
962 else
963 l_curr_conv := 1;
964 end if;
965 hr_utility.set_location('curr conv factr is'||l_curr_conv,28);
966 l_t_min_rate := (l_freq_conv * nvl(l_t_min_rate,0)*l_curr_conv);
967 l_t_mid_rate := (l_freq_conv * nvl(l_t_mid_rate,0)*l_curr_conv);
968 l_t_max_rate := (l_freq_conv * nvl(l_t_max_rate,0)*l_curr_conv);
969 l_t_dflt_rate := (l_freq_conv * nvl(l_t_dflt_rate,0)*l_curr_conv);
970 --
971 Else
972 hr_utility.set_location('This should not be happening', 5);
973 End if;
974 --
975 l_parent_rates(l_cnt).min_rate_value := l_t_min_rate;
976 l_parent_rates(l_cnt).mid_rate_value := l_t_mid_rate;
977 l_parent_rates(l_cnt).max_rate_value := l_t_max_rate;
978 l_parent_rates(l_cnt).rate_value := l_t_dflt_rate;
979 --
980 End loop;
981 End if;
982 --
983 -- Apply Calculation method and find the final rate for the given rate type.
984 --
985 hr_utility.set_location('No ofParent Rates:'||to_char(l_cnt), 10);
986 If l_rate_calc_cd = 'PERCENT' then
987 If l_cnt > 0 then
988 hr_utility.set_location('Percent', 10);
989 l_dflt_rate := (l_dflt_rate/100) * l_parent_rates(1).rate_value;
990 l_min_rate := (l_min_rate/100) * l_parent_rates(1).min_rate_value;
991 l_mid_rate := (l_mid_rate/100) * l_parent_rates(1).mid_rate_value;
992 l_max_rate := (l_max_rate/100) * l_parent_rates(1).max_rate_value;
993 End if;
994 Elsif l_rate_calc_cd = 'ADD_TO' OR l_rate_calc_cd = 'SUM' then
995 If l_cnt > 0 then
996 hr_utility.set_location('Sum or Add_To', 10);
997 For l_t_cnt in 1..l_cnt loop
998 l_min_rate := l_min_rate + l_parent_rates(l_t_cnt).min_rate_value ;
999 l_mid_rate := l_mid_rate + l_parent_rates(l_t_cnt).mid_rate_value ;
1000 l_max_rate := l_max_rate + l_parent_rates(l_t_cnt).max_rate_value ;
1001 l_dflt_rate := l_dflt_rate + l_parent_rates(l_t_cnt).rate_value ;
1002 End loop;
1003 End if;
1004 Elsif l_rate_calc_cd = 'RULE' then
1005 -- Use Calculation Rule.
1006 hr_utility.set_location('Rule', 10);
1007 l_outputs := exec_rate_calc_formula
1008 (p_formula_id => l_rate_calc_rule,
1009 p_effective_date => p_effective_date,
1010 p_param1 => 'PQH_RBC_PERSON_ID',
1011 p_param1_value => to_char(nvl(p_person_id, -1)),
1012 p_param2 => 'PQH_RBC_CRIT_RATE_DEFN_ID',
1013 p_param2_value => to_char(nvl(p_crit_rt_defn_id, -1) ));
1014
1015 for l_count in nvl(l_outputs.first,0)..nvl(l_outputs.last,-1) loop
1016 --
1017 hr_utility.set_location ('Current Context'||l_outputs(l_count).name,10);
1018 --
1019 if l_outputs(l_count).name = 'MIN' then
1020 --
1021 l_min_rate := l_outputs(l_count).value;
1022 elsif l_outputs(l_count).name = 'MID' then
1023 --
1024 l_mid_rate := l_outputs(l_count).value;
1025 elsif l_outputs(l_count).name = 'MAX' then
1026 --
1027 l_max_rate := l_outputs(l_count).value;
1028 elsif l_outputs(l_count).name = 'DFLT' then
1029 --
1030 l_dflt_rate := l_outputs(l_count).value;
1031 --
1032 End if;
1033 --
1034 End loop;
1035 --
1036 Elsif l_rate_calc_cd = 'AMOUNT' then
1037 hr_utility.set_location('Amount', 10);
1038 null;
1039 Else
1040 hr_utility.set_message(8302,'PQH_RBC_INVALID_RT_CALC_METHOD');
1041 hr_utility.raise_error;
1042 End if;
1043 --
1044 -- Apply Rounding.
1045 --
1046 If l_rounding_cd <> 'NONE' then
1047 --
1048 hr_utility.set_location('Perform Rounding', 10);
1049 --
1050 p_min_rate := benutils.do_rounding
1051 (p_rounding_cd => l_rounding_cd,
1052 p_rounding_rl => l_rounding_rule ,
1053 p_assignment_id => p_assignment_id,
1054 p_value => l_min_rate,
1055 p_effective_date => p_effective_date);
1056 --
1057 p_mid_rate := benutils.do_rounding
1058 (p_rounding_cd => l_rounding_cd,
1059 p_rounding_rl => l_rounding_rule ,
1060 p_assignment_id => p_assignment_id,
1061 p_value => l_mid_rate,
1062 p_effective_date => p_effective_date);
1063 --
1064 p_max_rate := benutils.do_rounding
1065 (p_rounding_cd => l_rounding_cd,
1066 p_rounding_rl => l_rounding_rule ,
1067 p_assignment_id => p_assignment_id,
1068 p_value => l_max_rate,
1069 p_effective_date => p_effective_date);
1070 --
1071 p_rate := benutils.do_rounding
1072 (p_rounding_cd => l_rounding_cd,
1073 p_rounding_rl => l_rounding_rule ,
1074 p_assignment_id => p_assignment_id,
1075 p_value => l_dflt_rate,
1076 p_effective_date => p_effective_date);
1077 --
1078 Else
1079 --
1080 hr_utility.set_location('No Rounding', 10);
1081 --
1082 p_min_rate := l_min_rate;
1083 p_mid_rate := l_mid_rate;
1084 p_max_rate := l_max_rate;
1085 p_rate := l_dflt_rate;
1086 End if;
1087 --
1088 hr_utility.set_location('Leaving:'||l_proc, 10);
1089 --
1090 End;
1091 --
1092 ----------------------------------------------------------------------------------------------------
1093 --
1094 Procedure popl_crit_list_in_ovrrd_tbl
1095 (p_criteria_list IN pqh_popl_criteria_ovrrd.g_crit_ovrrd_val_tbl ,
1096 p_business_group_id IN number,
1097 p_effective_date IN date) is
1098 --
1099 l_proc varchar2(72) := g_package||'popl_crit_list_in_ovrrd_tbl';
1100 l_crit_val_rec pqh_popl_criteria_ovrrd.g_crit_ovrrd_val_rec;
1101 cnt number(15) := 0;
1102 --
1103 Begin
1104 --
1105 hr_utility.set_location('Entering:'||l_proc, 5);
1106 --
1107 -- If the override list is already populated , populate the global override table for BEN to read.
1108 --
1109
1110 pqh_popl_criteria_ovrrd.init_criteria_override_tbl;
1111
1112 For cnt in 1.. p_criteria_list.count loop
1113 l_crit_val_rec.criteria_short_code := p_criteria_list(cnt).criteria_short_code;
1114 l_crit_val_rec.number_value1 := p_criteria_list(cnt).number_value1;
1115 l_crit_val_rec.number_value2 := p_criteria_list(cnt).number_value2;
1116 -- l_crit_val_rec.number_value3 := p_criteria_list(cnt).number_value3;
1117 -- l_crit_val_rec.number_value4 := p_criteria_list(cnt).number_value4;
1118
1119 l_crit_val_rec.char_value1 := p_criteria_list(cnt).char_value1;
1120 l_crit_val_rec.char_value2 := p_criteria_list(cnt).char_value2;
1121 -- l_crit_val_rec.char_value3 := p_criteria_list(cnt).char_value3;
1122 -- l_crit_val_rec.char_value4 := p_criteria_list(cnt).char_value4;
1123
1124 l_crit_val_rec.date_value1 := p_criteria_list(cnt).date_value1;
1125 l_crit_val_rec.date_value2 := p_criteria_list(cnt).date_value2;
1126 -- l_crit_val_rec.date_value3 := p_criteria_list(cnt).date_value3;
1127 -- l_crit_val_rec.date_value4 := p_criteria_list(cnt).date_value4;
1128
1129 pqh_popl_criteria_ovrrd.insert_criteria_override(p_crit_ovrrd_val_rec => l_crit_val_rec);
1130
1131 End loop;
1132 --
1133 --
1134 hr_utility.set_location('Leaving:'||l_proc, 10);
1135 --
1136 End;
1137 ----------------------------------------------------------------------------------------------------
1138 --
1139 Procedure populate_person_override_val
1140 (p_person_id IN number,
1141 p_assignment_id IN number,
1142 p_element_entry_id IN number,
1143 p_business_group_id IN number,
1144 p_effective_date IN date) is
1145 --
1146 -- Cursor to select only those eligibility criteria where time card override is not null.
1147 --
1148 Cursor csr_timecard_ovrrd_exists is
1149 Select *
1150 from ben_eligy_criteria
1151 Where (time_entry_access_table_name1 is not null or time_entry_access_table_name2 is not null)
1152 and business_group_id = p_business_group_id;
1153 --
1154 l_proc varchar2(72) := g_package||'populate_person_override_val';
1155 --
1156 l_sql_stmt varchar2(2000);
1157 l_tc_ovrrd_col varchar2(100);
1158 l_crit_dtls_tbl tc_criteria_tbl;
1159 --
1160 l_temp_cnt number := 0;
1161 l_crit_cnt number := 0;
1162 l_val_cnt number := 0;
1163 l_crit_val_rec pqh_popl_criteria_ovrrd.g_crit_ovrrd_val_rec;
1164 --
1165 l_dummy_value1 varchar2(30);
1166 l_dummy_value2 varchar2(30);
1167 l_cost_allocation_keyflex_id pay_cost_allocation_keyflex.cost_allocation_keyflex_id%type;
1168 --
1169 Cursor csr_input_values is
1170 select a.input_value_id, a.screen_entry_value, b.display_sequence
1171 from pay_element_entry_values_f a, pay_input_values_f b
1172 Where a.element_entry_id = p_element_entry_id
1173 and a.input_value_id = b.input_value_id
1174 order by display_sequence;
1175 --
1176 Cursor csr_kflx (p_cost_allocation_keyflex_id in number) is
1177 Select *
1178 From pay_cost_allocation_keyflex
1179 Where cost_allocation_keyflex_id = p_cost_allocation_keyflex_id;
1180 --
1181 Begin
1182 --
1183 hr_utility.set_location('Entering:'||l_proc, 5);
1184 --
1185 -- Check if there are any criteria in business group that has timecard override info specified.
1186 -- and fetch its details
1187 --
1188 For csr_tc_rec in csr_timecard_ovrrd_exists loop
1189 --
1190 l_crit_cnt := l_crit_cnt + 1;
1191 l_crit_dtls_tbl(l_crit_cnt).short_code := csr_tc_rec.short_code;
1192 l_crit_dtls_tbl(l_crit_cnt).crit_col1_datatype := csr_tc_rec.crit_col1_datatype;
1193 l_crit_dtls_tbl(l_crit_cnt).time_entry_access_table_name1 := csr_tc_rec.time_entry_access_table_name1;
1194 l_crit_dtls_tbl(l_crit_cnt).time_entry_access_col_name1 := csr_tc_rec.time_entry_access_col_name1;
1195 l_crit_dtls_tbl(l_crit_cnt).crit_col2_datatype := csr_tc_rec.crit_col2_datatype;
1196 l_crit_dtls_tbl(l_crit_cnt).time_entry_access_table_name2 := csr_tc_rec.time_entry_access_table_name2;
1197 l_crit_dtls_tbl(l_crit_cnt).time_entry_access_col_name2 := csr_tc_rec.time_entry_access_col_name2;
1198 --
1199 hr_utility.set_location('time_entry_access_col_name1:'||l_crit_dtls_tbl(l_crit_cnt).time_entry_access_col_name1, 20);
1200 hr_utility.set_location('time_entry_access_col_name2:'||l_crit_dtls_tbl(l_crit_cnt).time_entry_access_col_name2, 20);
1201 --
1202 End loop;
1203 --
1204 If l_crit_cnt > 0 then
1205 --
1206 -- Browse through each criteria, find the override value and populate
1207 --
1208 -- 1. Popluate input values
1209 l_val_cnt := 0;
1210 For ip_val_rec in csr_input_values loop
1211 l_val_cnt := l_val_cnt + 1;
1212 l_tc_ovrrd_col := 'VALUE_'||to_char(l_val_cnt);
1213 g_entry_val_tbl(l_val_cnt).column_name := l_tc_ovrrd_col;
1214 g_entry_val_tbl(l_val_cnt).col_value := ip_val_rec.screen_entry_value;
1215 End loop;
1216
1217 If l_val_cnt < 15 then
1218 l_temp_cnt := l_val_cnt + 1;
1219 For l_temp_cnt2 in l_temp_cnt..15 loop
1220 --
1221 l_val_cnt := l_val_cnt + 1;
1222 l_tc_ovrrd_col := 'VALUE_'||to_char(l_val_cnt);
1223 g_entry_val_tbl(l_val_cnt).column_name := l_tc_ovrrd_col;
1224 g_entry_val_tbl(l_val_cnt).col_value := null;
1225 --
1226 End loop;
1227 End if;
1228 --
1229
1230 For l_temp_cnt in 1..20 loop
1231 --
1232 l_val_cnt := l_val_cnt + 1;
1233 l_tc_ovrrd_col := 'ATTRIBUTE'||to_char(l_temp_cnt);
1234 g_entry_val_tbl(l_val_cnt).column_name := l_tc_ovrrd_col;
1235 l_sql_stmt := 'Begin ';
1236 l_sql_stmt := l_sql_stmt || 'pqh_rbc_rate_retrieval.g_entry_val_tbl('||to_char(l_val_cnt)||').col_value := pqh_rbc_rate_retrieval.g_entry_rec.'||l_tc_ovrrd_col||';';
1237 l_sql_stmt := l_sql_stmt || 'End;';
1238 EXECUTE IMMEDIATE l_sql_stmt;
1239 --
1240 hr_utility.set_location(l_sql_stmt,100);
1241 hr_utility.set_location('Value is:'||g_entry_val_tbl(l_val_cnt).col_value,100);
1242 --
1243 End loop;
1244 --
1245 For l_temp_cnt in 1..30 loop
1246 --
1247 l_val_cnt := l_val_cnt + 1;
1248 l_tc_ovrrd_col := 'ENTRY_INFORMATION'||to_char(l_temp_cnt);
1249 g_entry_val_tbl(l_val_cnt).column_name := l_tc_ovrrd_col;
1250 l_sql_stmt := 'Begin ';
1251 l_sql_stmt := l_sql_stmt || 'pqh_rbc_rate_retrieval.g_entry_val_tbl('||to_char(l_val_cnt)||').col_value := pqh_rbc_rate_retrieval.g_entry_rec.'||l_tc_ovrrd_col||';';
1252 l_sql_stmt := l_sql_stmt || 'End;';
1253 EXECUTE IMMEDIATE l_sql_stmt;
1254 --
1255 hr_utility.set_location(l_sql_stmt,90);
1256 hr_utility.set_location('Value is:'||g_entry_val_tbl(l_val_cnt).col_value,91);
1257 --
1258 End loop;
1259 --
1260 -- Add Cost allocation flexfield segments.
1261 --
1262 l_cost_allocation_keyflex_id := pqh_rbc_rate_retrieval.g_entry_rec.cost_allocation_keyflex_id;
1263 --
1264 If l_cost_allocation_keyflex_id is not null then
1265 Open csr_kflx(l_cost_allocation_keyflex_id);
1266 Fetch csr_kflx into g_ckf_rec;
1267 Close csr_kflx;
1268 --
1269 For l_temp_cnt in 1..30 loop
1270 --
1271 l_val_cnt := l_val_cnt + 1;
1272 l_tc_ovrrd_col := 'SEGMENT'||to_char(l_temp_cnt);
1273 g_entry_val_tbl(l_val_cnt).column_name := l_tc_ovrrd_col;
1274 l_sql_stmt := 'Begin ';
1275 l_sql_stmt := l_sql_stmt || 'pqh_rbc_rate_retrieval.g_entry_val_tbl('||to_char(l_val_cnt)||').col_value := pqh_rbc_rate_retrieval.g_ckf_rec.'||l_tc_ovrrd_col||';';
1276 l_sql_stmt := l_sql_stmt || 'End;';
1277 EXECUTE IMMEDIATE l_sql_stmt;
1278 --
1279 hr_utility.set_location(l_sql_stmt,90);
1280 hr_utility.set_location('Value is:'||g_entry_val_tbl(l_val_cnt).col_value,91);
1281 --
1282 End loop;
1283 --
1284 End if;
1285 --
1286 pqh_popl_criteria_ovrrd.init_criteria_override_tbl;
1287
1288 For l_temp_cnt in 1..l_crit_cnt loop
1289 --
1290 -- Assign the criteria short code.
1291 l_crit_val_rec.criteria_short_code := l_crit_dtls_tbl(l_temp_cnt).short_code;
1292 --
1293 l_dummy_value1 := null;
1294 l_dummy_value2 := null;
1295 For l_temp_cnt2 in 1..l_val_cnt loop
1296 -- Find the value;
1297 hr_utility.set_location('Comparing:'||l_crit_dtls_tbl(l_temp_cnt).time_entry_access_col_name1||','||g_entry_val_tbl(l_temp_cnt2).column_name,91);
1298 If l_crit_dtls_tbl(l_temp_cnt).time_entry_access_col_name1 = g_entry_val_tbl(l_temp_cnt2).column_name then
1299 l_dummy_value1 := g_entry_val_tbl(l_temp_cnt2).col_value;
1300 End if;
1301 If l_crit_dtls_tbl(l_temp_cnt).time_entry_access_col_name2 = g_entry_val_tbl(l_temp_cnt2).column_name then
1302 l_dummy_value2 := g_entry_val_tbl(l_temp_cnt2).col_value;
1303 End if;
1304 --
1305 End loop;
1306 hr_utility.set_location('Criteria Short Code is:'|| l_crit_val_rec.criteria_short_code,100);
1307 hr_utility.set_location('Override Value1 is:'||l_dummy_value1,101);
1308 hr_utility.set_location('Override Value2 is:'||l_dummy_value2,102);
1309 --
1310 l_crit_val_rec.number_value1 := null;
1311 l_crit_val_rec.number_value2 := null;
1312 l_crit_val_rec.char_value1 := null;
1313 l_crit_val_rec.char_value2 := null;
1314 l_crit_val_rec.date_value1 := null;
1315 l_crit_val_rec.date_value2 := null;
1316 --
1317 hr_utility.set_location('Initialise override rec ',103);
1318 --
1319 If l_crit_dtls_tbl(l_temp_cnt).crit_col1_datatype = 'N' then
1320 hr_utility.set_location('Copy to number ',103);
1321 l_crit_val_rec.number_value1 := l_dummy_value1;
1322 l_crit_val_rec.number_value2 := l_dummy_value2;
1323 elsif l_crit_dtls_tbl(l_temp_cnt).crit_col1_datatype = 'C' then
1324 hr_utility.set_location('Copy to char ',103);
1325 l_crit_val_rec.char_value1 := l_dummy_value1;
1326 l_crit_val_rec.char_value2 := l_dummy_value2;
1327 elsif l_crit_dtls_tbl(l_temp_cnt).crit_col1_datatype = 'D' then
1328 hr_utility.set_location('Copy to date ',103);
1329 l_crit_val_rec.date_value1 := to_date(l_dummy_value1,'yyyymmdd');
1330 l_crit_val_rec.date_value2 := to_date(l_dummy_value2,'yyyymmdd');
1331 End if;
1332 --
1333 If l_dummy_value1 is not null or l_dummy_value2 is not null then
1334 --
1335 pqh_popl_criteria_ovrrd.insert_criteria_override(p_crit_ovrrd_val_rec => l_crit_val_rec);
1336 --
1337 End if;
1338 --
1339 End loop;
1340 End if; --If l_crit_cnt > 0
1341 hr_utility.set_location('Leaving:'||l_proc, 10);
1342 --
1343 End;
1344 --
1345 -------------------------< determine_rbc_rate >----------------------------------
1346 -- Function to return the rate for a given element type /criteria rate definition.
1347 -- This is the main function to be called by other applications to retrieve the RBC rate
1348 -- for a person. No timecard override data will be used in computing the rate.
1349 --
1350 Procedure determine_rbc_rate
1351 (p_element_entry_id IN number,
1352 p_element_type_id IN number default null,
1353 p_business_group_id IN number,
1354 p_effective_date IN date,
1355 p_rate_factors OUT nocopy g_rbc_factor_tbl,
1356 p_rate_factor_cnt OUT nocopy number,
1357 p_min_rate OUT nocopy number,
1358 p_mid_rate OUT nocopy number,
1359 p_max_rate OUT nocopy number,
1360 p_rate OUT nocopy number) is
1361 --
1362 Cursor csr_entry_details is
1363 Select *
1364 From pay_element_entries_f
1365 Where element_entry_id = p_element_entry_id
1366 and p_effective_date between effective_start_date and effective_end_date;
1367 --
1368 cursor csr_linked_crd (p_element_type_id in number)is
1369 Select criteria_rate_defn_id
1370 from pqh_criteria_rate_elements
1371 Where element_type_id = p_element_type_id;
1372 --
1373 Cursor csr_per_details (p_per_id in number)is
1374 Select *
1375 From per_all_people_f
1376 Where person_id = p_per_id
1377 and p_effective_date between effective_start_date and effective_end_date;
1378 --
1379 Cursor csr_asg_details (p_assignment_id in number) is
1380 select *
1381 from per_all_assignments_f
1382 Where assignment_id = p_assignment_id
1383 and p_effective_date between effective_start_date and effective_end_date;
1384 --
1385 l_crit_rt_defn_id pqh_criteria_rate_defn.criteria_rate_defn_id%type;
1386 l_assignment_id per_all_assignments_f.assignment_id%type;
1387 l_person_id per_all_people_f.person_id%type;
1388 l_proc varchar2(72) := g_package||'determine_rbc_rate';
1389 --
1390 Begin
1391 --
1392 hr_utility.set_location('Entering:'||l_proc, 5);
1393 --
1394 g_person_id := null;
1395 g_assignment_id := null;
1396 g_element_type_id := null;
1397 g_business_group_id := null;
1398 g_criteria_rate_defn_id := null;
1399 --
1400 Open csr_entry_details;
1401 Fetch csr_entry_details into g_entry_rec;
1402 Close csr_entry_details;
1403 --
1404 If p_element_type_id is not null then
1405 g_element_type_id := p_element_type_id;
1406 else
1407 g_element_type_id := g_entry_rec.element_type_id;
1408 End if;
1409 --
1410 Open csr_linked_crd (g_element_type_id);
1411 Fetch csr_linked_crd into l_crit_rt_defn_id;
1412 If csr_linked_crd%notfound then
1413 hr_utility.set_message(8302,'PQH_RBC_ELE_NOT_LNKD_RT_TYP');
1414 hr_utility.raise_error;
1415 Else
1416 g_criteria_rate_defn_id := l_crit_rt_defn_id;
1417 End if;
1418 Close csr_linked_crd;
1419 --
1420 l_assignment_id := g_entry_rec.assignment_id;
1421 --
1422 -- Find the assignment and cache its details
1423 --
1424 Open csr_asg_details(l_assignment_id);
1425 Fetch csr_asg_details into g_asg_rec;
1426 If csr_asg_details%notfound then
1427 hr_utility.set_message(8302,'PQH_RBC_INVALID_ASG_ID');
1428 hr_utility.raise_error;
1429 Else
1430 g_assignment_id := g_asg_rec.assignment_id;
1431 End if;
1432 Close csr_asg_details;
1433 --
1434 -- find person's details and cache the information.
1435 --
1436 l_person_id := g_asg_rec.person_id;
1437 Open csr_per_details(l_person_id);
1438 Fetch csr_per_details into g_per_rec;
1439 If csr_per_details%notfound then
1440 hr_utility.set_message(8302,'PQH_RBC_INVALID_PERSON_ID');
1441 hr_utility.raise_error;
1442 Else
1443 g_person_id := l_person_id;
1444 End if;
1445 Close csr_per_details;
1446 --
1447 g_business_group_id := p_business_group_id;
1448 --
1449 -- Populate timecard override
1450 --
1451 populate_person_override_val
1452 (p_person_id => l_person_id,
1453 p_assignment_id => l_assignment_id,
1454 p_element_entry_id => p_element_entry_id,
1455 p_business_group_id => p_business_group_id,
1456 p_effective_date => p_effective_date);
1457 --
1458 -- Loop through the rate matrices in the business group and call function to get rate for a
1459 -- given rate matrix, person, rate type and effective date.
1460 --
1461 calculate_crit_rate_defn_rt(
1462 p_crit_rt_defn_id => l_crit_rt_defn_id,
1463 p_person_id => l_person_id,
1464 p_assignment_id => l_assignment_id,
1465 p_business_group_id => p_business_group_id,
1466 p_effective_date => p_effective_date,
1467 p_rate_factors => p_rate_factors,
1468 p_rate_factor_cnt => p_rate_factor_cnt,
1469 p_rate => p_rate,
1470 p_min_rate => p_min_rate,
1471 p_mid_rate => p_mid_rate,
1472 p_max_rate => p_max_rate);
1473 --
1474 hr_utility.set_location('Leaving:'||l_proc, 10);
1475 --
1476 End;
1477 --
1478 -------------------------< determine_rbc_rate >----------------------------------
1479 -- Function to return the rate for a given element type /criteria rate definition.
1480 -- This is the main function to be called by other applications to retrieve the RBC rate
1481 -- for a person. No timecard override data will be used in computing the rate.
1482 --
1483 Procedure determine_rbc_rate
1484 (p_element_type_id IN number default null,
1485 p_crit_rt_defn_id IN number default null,
1486 p_person_id IN number default null,
1487 p_assignment_id IN number default null,
1488 p_business_group_id IN number,
1489 p_effective_date IN date,
1490 p_rate_factors OUT nocopy g_rbc_factor_tbl,
1491 p_rate_factor_cnt OUT nocopy number,
1492 p_min_rate OUT nocopy number,
1493 p_mid_rate OUT nocopy number,
1494 p_max_rate OUT nocopy number,
1495 p_rate OUT nocopy number) is
1496 --
1497 --
1498 cursor csr_linked_crd is
1499 Select criteria_rate_defn_id
1500 from pqh_criteria_rate_elements
1501 Where element_type_id = p_element_type_id;
1502 --
1503 Cursor csr_per_details (p_per_id in number)is
1504 Select *
1505 From per_all_people_f
1506 Where person_id = p_per_id
1507 and p_effective_date between effective_start_date and effective_end_date;
1508 --
1509 Cursor csr_primary_asg_details is
1510 select *
1511 from per_all_assignments_f
1512 Where person_id = p_person_id
1513 and primary_flag = 'Y'
1514 and p_effective_date between effective_start_date and effective_end_date;
1515 --
1516 Cursor csr_asg_details is
1517 select *
1518 from per_all_assignments_f
1519 Where assignment_id = p_assignment_id
1520 and p_effective_date between effective_start_date and effective_end_date;
1521 --
1522 l_crit_rt_defn_id pqh_criteria_rate_defn.criteria_rate_defn_id%type;
1523 l_criteria_list pqh_popl_criteria_ovrrd.g_crit_ovrrd_val_tbl;
1524 l_assignment_id per_all_assignments_f.assignment_id%type;
1525 l_person_id per_all_people_f.person_id%type;
1526 l_proc varchar2(72) := g_package||'determine_rbc_rate';
1527 --
1528 Begin
1529 --
1530 hr_utility.set_location('Entering:'||l_proc, 5);
1531 --
1532 g_person_id := null;
1533 g_assignment_id := null;
1534 g_element_type_id := null;
1535 g_business_group_id := null;
1536 g_criteria_rate_defn_id := null;
1537
1538 -- At least element type or rate type must be passed, but not both.
1539 --
1540 If p_element_type_id is NULL and p_crit_rt_defn_id is NULL then
1541 --
1542 hr_utility.set_message(8302,'PQH_RBC_NO_ELE_AND_RT_TYP');
1543 hr_utility.raise_error;
1544 --
1545 Elsif p_element_type_id IS NOT NULL and p_crit_rt_defn_id IS NULL then
1546 --
1547 -- If element type is passed, get the rate type linked to the element.
1548 --
1549 Open csr_linked_crd;
1550 Fetch csr_linked_crd into l_crit_rt_defn_id;
1551 If csr_linked_crd%notfound then
1552 hr_utility.set_message(8302,'PQH_RBC_ELE_NOT_LNKD_RT_TYP');
1553 hr_utility.raise_error;
1554 Else
1555 g_criteria_rate_defn_id := l_crit_rt_defn_id;
1556 End if;
1557 Close csr_linked_crd;
1558 --
1559 g_element_type_id := p_element_type_id;
1560
1561 Elsif p_crit_rt_defn_id IS NOT NULL then
1562 --
1563 -- Rate type is passed directly.
1564 --
1565 l_crit_rt_defn_id := p_crit_rt_defn_id;
1566 --
1567 g_criteria_rate_defn_id := p_crit_rt_defn_id;
1568
1569 End if;
1570 --
1571 -- 1) At least person id or assignment id or criteria list must be passed
1572 -- 2) If person id and assignment id is passed or just assignment id is passed,
1573 -- rate is evaluated for the passed assignment.
1574 -- 3)If person id is passed , but assignment assignment id is not passed, then
1575 -- Evaluate rate for primary assignment.
1576 --
1577 If p_person_id is NULL and p_assignment_id is NULL then
1578 --
1579 hr_utility.set_message(8302,'PQH_RBC_NO_PER_AND_ASG');
1580 hr_utility.raise_error;
1581 --
1582 Elsif p_person_id IS NOT NULL and p_assignment_id IS NULL then
1583 --
1584 -- Find the primary assignment and cache its details
1585 --
1586 Open csr_primary_asg_details;
1587 Fetch csr_primary_asg_details into g_asg_rec;
1588 If csr_primary_asg_details%notfound then
1589 hr_utility.set_message(8302,'PQH_RBC_NO_PRIM_ASG');
1590 hr_utility.raise_error;
1591 Else
1592 g_assignment_id := g_asg_rec.assignment_id;
1593 l_assignment_id := g_asg_rec.assignment_id;
1594 End if;
1595 Close csr_primary_asg_details;
1596 --
1597 -- find person's details and cache the information.
1598 --
1599 Open csr_per_details(p_person_id);
1600 Fetch csr_per_details into g_per_rec;
1601 If csr_per_details%notfound then
1602 hr_utility.set_message(8302,'PQH_RBC_INVALID_PERSON_ID');
1603 hr_utility.raise_error;
1604 Else
1605 g_person_id := p_person_id;
1606 l_person_id := p_person_id;
1607 End if;
1608 Close csr_per_details;
1609
1610 Elsif p_assignment_id IS NOT NULL then
1611 --
1612 -- assignment_id is passed directly. Cache assignment details.
1613 --
1614 Open csr_asg_details;
1615 Fetch csr_asg_details into g_asg_rec;
1616 If csr_asg_details%notfound then
1617 hr_utility.set_message(8302,'PQH_RBC_INVALID_ASG_ID');
1618 hr_utility.raise_error;
1619 Else
1620 g_assignment_id := g_asg_rec.assignment_id;
1621 l_assignment_id := g_asg_rec.assignment_id;
1622 End if;
1623 Close csr_asg_details;
1624 --
1625 -- Find the person and cache person's details
1626 --
1627 Open csr_per_details(g_asg_rec.person_id);
1628 Fetch csr_per_details into g_per_rec;
1629 If csr_per_details%notfound then
1630 hr_utility.set_message(8302,'PQH_RBC_INVALID_PERSON_ID');
1631 hr_utility.raise_error;
1632 Else
1633 g_person_id := g_per_rec.person_id;
1634 l_person_id := g_per_rec.person_id;
1635 End if;
1636 Close csr_per_details;
1637
1638 End if;
1639 --
1640 g_business_group_id := p_business_group_id;
1641 --
1642 -- No need to get timecard override.
1643 -- Loop through the rate matrices in the business group and call function to get rate for a
1644 -- given rate matrix, person, rate type and effective date.
1645 --
1646 calculate_crit_rate_defn_rt(
1647 p_crit_rt_defn_id => l_crit_rt_defn_id,
1648 p_person_id => l_person_id,
1649 p_assignment_id => l_assignment_id,
1650 p_business_group_id => p_business_group_id,
1651 p_effective_date => p_effective_date,
1652 p_rate_factors => p_rate_factors,
1653 p_rate_factor_cnt => p_rate_factor_cnt,
1654 p_rate => p_rate,
1655 p_min_rate => p_min_rate,
1656 p_mid_rate => p_mid_rate,
1657 p_max_rate => p_max_rate);
1658 --
1659 hr_utility.set_location('Leaving:'||l_proc, 10);
1660 --
1661 End;
1662 --
1663 -------------------------< determine_rbc_rate >----------------------------------
1664 --
1665 -- Overloaded function to return the rate for a given element type. This is the main function to
1666 -- be called by other applications to retrieve the RBC rate for a criteria list.
1667 --
1668 Procedure determine_rbc_rate
1669 (p_element_type_id IN number default null,
1670 p_crit_rt_defn_id IN number default null,
1671 p_business_group_id IN number,
1672 p_criteria_list IN pqh_popl_criteria_ovrrd.g_crit_ovrrd_val_tbl,
1673 p_effective_date IN date,
1674 p_rate_factors OUT nocopy g_rbc_factor_tbl,
1675 p_rate_factor_cnt OUT nocopy number,
1676 p_min_rate OUT nocopy number,
1677 p_mid_rate OUT nocopy number,
1678 p_max_rate OUT nocopy number,
1679 p_rate OUT nocopy number) is
1680 --
1681 l_crit_rt_defn_id pqh_criteria_rate_defn.criteria_rate_defn_id%type;
1682 l_criteria_list pqh_popl_criteria_ovrrd.g_crit_ovrrd_val_tbl;
1683 --
1684 cursor csr_linked_crd is
1685 Select criteria_rate_defn_id
1686 from pqh_criteria_rate_elements
1687 Where element_type_id = p_element_type_id;
1688 --
1689 l_proc varchar2(72) := g_package||'determine_rbc_rate';
1690 --
1691 Begin
1692 --
1693 hr_utility.set_location('Entering:'||l_proc, 5);
1694 --
1695 g_person_id := null;
1696 g_assignment_id := null;
1697 g_element_type_id := null;
1698 g_business_group_id := null;
1699 g_criteria_rate_defn_id := null;
1700
1701 -- At least element type or rate type must be passed, but not both.
1702 --
1703 If p_element_type_id is NULL and p_crit_rt_defn_id is NULL then
1704 --
1705 hr_utility.set_message(8302,'PQH_RBC_NO_ELE_AND_RT_TYP');
1706 hr_utility.raise_error;
1707 --
1708 Elsif p_element_type_id IS NOT NULL and p_crit_rt_defn_id IS NULL then
1709 --
1710 -- If element type is passed, get the rate type linked to the element.
1711 --
1712 Open csr_linked_crd;
1713 Fetch csr_linked_crd into l_crit_rt_defn_id;
1714 If csr_linked_crd%notfound then
1715 hr_utility.set_message(8302,'PQH_RBC_ELE_NOT_LNKD_RT_TYP');
1716 hr_utility.raise_error;
1717 Else
1718 g_criteria_rate_defn_id := l_crit_rt_defn_id;
1719 End if;
1720 Close csr_linked_crd;
1721 --
1722 g_element_type_id := p_element_type_id;
1723 Elsif p_crit_rt_defn_id IS NOT NULL then
1724 --
1725 -- Rate type is passed directly.
1726 --
1727 l_crit_rt_defn_id := p_crit_rt_defn_id;
1728 --
1729 g_criteria_rate_defn_id := p_crit_rt_defn_id;
1730 End if;
1731 --
1732 g_business_group_id := p_business_group_id;
1733 --
1734 -- Populate the global criteria value override structure either from the passed criteria list.
1735 --
1736 popl_crit_list_in_ovrrd_tbl
1737 (p_criteria_list => p_criteria_list,
1738 p_business_group_id => p_business_group_id,
1739 p_effective_date => p_effective_date);
1740 --
1741 -- Loop through the rate matrices in the business group and call function to get rate for a
1742 -- given rate matrix, set of criteria, rate type and effective date.
1743 --
1744 calculate_crit_rate_defn_rt
1745 (p_crit_rt_defn_id => p_crit_rt_defn_id,
1746 p_person_id => null,
1747 p_assignment_id => null,
1748 p_business_group_id => p_business_group_id,
1749 p_effective_date => p_effective_date,
1750 p_rate_factors => p_rate_factors,
1751 p_rate_factor_cnt => p_rate_factor_cnt,
1752 p_rate => p_rate,
1753 p_min_rate => p_min_rate,
1754 p_mid_rate => p_mid_rate,
1755 p_max_rate => p_max_rate);
1756 --
1757 --
1758 hr_utility.set_location('Leaving:'||l_proc, 10);
1759 --
1760 End;
1761 --
1762 ----------------------------------------------------------------------------------------------------
1763 -- The following are functions that return a person's default rate only.
1764 --
1765 Function get_persons_rbc_rate
1766 (p_element_type_id IN number default null,
1767 p_crit_rt_defn_id IN number default null,
1768 p_person_id IN number default null,
1769 p_assignment_id IN number default null,
1770 p_business_group_id IN number,
1771 p_effective_date IN date)
1772 return number is
1773 --
1774 l_rate pqh_rate_matrix_rates_f.rate_value%type := 0;
1775 l_min_rate pqh_rate_matrix_rates_f.min_rate_value%type := 0;
1776 l_mid_rate pqh_rate_matrix_rates_f.mid_rate_value%type := 0;
1777 l_max_rate pqh_rate_matrix_rates_f.max_rate_value%type := 0;
1778 --
1779 l_rate_factors g_rbc_factor_tbl;
1780 l_rate_factor_cnt number := 0;
1781 --
1782 l_proc varchar2(72) := g_package||'get_persons_rbc_rate';
1783 --
1784 Begin
1785 --
1786 --
1787 hr_utility.set_location('Entering:'||l_proc, 10);
1788 --
1789 determine_rbc_rate(
1790 p_element_type_id => p_element_type_id,
1791 p_crit_rt_defn_id => p_crit_rt_defn_id,
1792 p_person_id => p_person_id,
1793 p_assignment_id => p_assignment_id,
1794 p_business_group_id => p_business_group_id,
1795 p_effective_date => p_effective_date,
1796 p_rate_factors => l_rate_factors,
1797 p_rate_factor_cnt => l_rate_factor_cnt,
1798 p_rate => l_rate,
1799 p_min_rate => l_min_rate,
1800 p_mid_rate => l_mid_rate,
1801 p_max_rate => l_max_rate);
1802 --
1803 --
1804 hr_utility.set_location('Leaving:'||l_proc, 10);
1805 --
1806 Return l_rate;
1807 --
1808 End get_persons_rbc_rate;
1809 --------------------------------------------------------------------------------------------------
1810 --
1811 Function get_persons_rbc_rate
1812 (p_element_type_id IN number default null,
1813 p_crit_rt_defn_id IN number default null,
1814 p_business_group_id IN number,
1815 p_criteria_list IN pqh_popl_criteria_ovrrd.g_crit_ovrrd_val_tbl,
1816 p_effective_date IN date)
1817 return number is
1818 --
1819 l_rate pqh_rate_matrix_rates_f.rate_value%type := 0;
1820 l_min_rate pqh_rate_matrix_rates_f.min_rate_value%type := 0;
1821 l_mid_rate pqh_rate_matrix_rates_f.mid_rate_value%type := 0;
1822 l_max_rate pqh_rate_matrix_rates_f.max_rate_value%type := 0;
1823 --
1824 l_rate_factors g_rbc_factor_tbl;
1825 l_rate_factor_cnt number := 0;
1826 --
1827 l_proc varchar2(72) := g_package||'get_persons_rbc_rate';
1828 --
1829 Begin
1830 --
1831 --
1832 hr_utility.set_location('Entering:'||l_proc, 10);
1833 --
1834 determine_rbc_rate(
1835 p_element_type_id => p_element_type_id,
1836 p_crit_rt_defn_id => p_crit_rt_defn_id,
1837 p_business_group_id => p_business_group_id,
1838 p_criteria_list => p_criteria_list,
1839 p_effective_date => p_effective_date,
1840 p_rate_factors => l_rate_factors,
1841 p_rate_factor_cnt => l_rate_factor_cnt,
1842 p_rate => l_rate,
1843 p_min_rate => l_min_rate,
1844 p_mid_rate => l_mid_rate,
1845 p_max_rate => l_max_rate);
1846 --
1847 --
1848 hr_utility.set_location('Leaving:'||l_proc, 10);
1849 --
1850 Return l_rate;
1851 --
1852 End get_persons_rbc_rate;
1853 --
1854 --------------------------------------------------------------------------------------------------
1855 --
1856 Function get_ele_entry_rbc_rate
1857 (p_element_entry_id IN number,
1858 p_business_group_id IN number,
1859 p_effective_date IN date)
1860 return number is
1861 --
1862 l_rate pqh_rate_matrix_rates_f.rate_value%type := 0;
1863 l_min_rate pqh_rate_matrix_rates_f.min_rate_value%type := 0;
1864 l_mid_rate pqh_rate_matrix_rates_f.mid_rate_value%type := 0;
1865 l_max_rate pqh_rate_matrix_rates_f.max_rate_value%type := 0;
1866 --
1867 l_rate_factors g_rbc_factor_tbl;
1868 l_rate_factor_cnt number := 0;
1869 --
1870 l_proc varchar2(72) := g_package||'get_ele_entry_rbc_rate';
1871 --
1872 Begin
1873 --
1874 --
1875 hr_utility.set_location('Entering:'||l_proc, 10);
1876 --
1877 determine_rbc_rate(
1878 p_element_entry_id => p_element_entry_id,
1879 p_element_type_id => null,
1880 p_business_group_id => p_business_group_id,
1881 p_effective_date => p_effective_date,
1882 p_rate_factors => l_rate_factors,
1883 p_rate_factor_cnt => l_rate_factor_cnt,
1884 p_rate => l_rate,
1885 p_min_rate => l_min_rate,
1886 p_mid_rate => l_mid_rate,
1887 p_max_rate => l_max_rate);
1888 --
1889 --
1890 hr_utility.set_location('Leaving:'||l_proc, 10);
1891 --
1892 Return l_rate;
1893 --
1894 End get_ele_entry_rbc_rate;
1895 --
1896 Function get_ele_entry_rbc_rate
1897 (p_element_entry_id IN number,
1898 p_business_group_id IN number,
1899 p_effective_date IN date,
1900 p_element_type_id IN number)
1901 return number is
1902 --
1903 l_rate pqh_rate_matrix_rates_f.rate_value%type := 0;
1904 l_min_rate pqh_rate_matrix_rates_f.min_rate_value%type := 0;
1905 l_mid_rate pqh_rate_matrix_rates_f.mid_rate_value%type := 0;
1906 l_max_rate pqh_rate_matrix_rates_f.max_rate_value%type := 0;
1907 --
1908 l_rate_factors g_rbc_factor_tbl;
1909 l_rate_factor_cnt number := 0;
1910 --
1911 l_proc varchar2(72) := g_package||'get_ele_entry_rbc_rate';
1912 --
1913 Begin
1914 --
1915 --
1916 hr_utility.set_location('Entering:'||l_proc, 10);
1917 --
1918 determine_rbc_rate(
1919 p_element_entry_id => p_element_entry_id,
1920 p_element_type_id => p_element_type_id,
1921 p_business_group_id => p_business_group_id,
1922 p_effective_date => p_effective_date,
1923 p_rate_factors => l_rate_factors,
1924 p_rate_factor_cnt => l_rate_factor_cnt,
1925 p_rate => l_rate,
1926 p_min_rate => l_min_rate,
1927 p_mid_rate => l_mid_rate,
1928 p_max_rate => l_max_rate);
1929 --
1930 --
1931 hr_utility.set_location('Leaving:'||l_proc, 10);
1932 --
1933 Return l_rate;
1934 --
1935 End get_ele_entry_rbc_rate;
1936 --
1937 End pqh_rbc_rate_retrieval;