1 package body PAY_FR_GENERAL as
2 /* $Header: pyfrgenr.pkb 120.0 2005/05/29 05:02:49 appldev noship $ */
3 --
4 g_package varchar2(30) := 'pay_fr_general';
5
6 g_summary_deductions t_summary_deductions;
7 g_summary_idx number := 0;
8
9 g_deduction_rates t_deduction_rates;
10
11 -- Added 115.15. Used to store previous run in period assignment action ID and Action Date.
12 -- These are set by the procedure set_prior_asg_action
13 g_prior_asg_action_id number;
14 g_prior_pay_action_date date;
15
16 -- global with which to cache (grand) parent asg action from within
17 -- initialize_payroll function. Added to support proration w/ run types.
18 g_parent_asg_action_id number;
19
20 -- +********************************************************************+
21 -- | PRIVATE FUNCTIONS |
22 -- +********************************************************************+
23 --
24 --
25 ------------------------------------------------------------------------
26 -- Function GET_TABLE_INDEX
27 -- This function will return a unique index for a given base/band name or
28 -- base code name. The function uses a standard call to
29 -- DBMS_UTILITY.get_hash_value this is similar to the call used in hr_bis.pkb
30 -- to calculate lookup cache value indexes.
31 --
32 -- This will used by the function WRITE_BASE_BANDS to calculate the PL/SQL
33 -- table index when populating it with the base and band values.
34 -- This function will also be called from the BASE_CODE functions to obtain
35 -- a index to store each base code in a unique PL/SQL table index.
36 ------------------------------------------------------------------------
37 function get_table_index(p_input_name varchar2) return number is
38 --
39 l_hash_number number;
40 --
41 --
42 begin
43 --
44 l_hash_number :=
45 DBMS_UTILITY.get_hash_value(
46 p_input_name,
47 1,
48 1048576);
49 -- (2^20)
50 return l_hash_number;
51
52 end get_table_index;
53
54 ------------------------------------------------------------------------
55 -- Procedure SET_PRIOR_ASG_ACTION
56 ------------------------------------------------------------------------
57 procedure set_prior_asg_action(p_date_earned in date
58 ,p_assignment_id in number
59 ,p_business_group_id in number
60 ,p_tax_unit_id in number
61 ,p_orig_entry_id in number
62 )
63 IS
64
65 l_asg_action_id number;
66 l_pay_action_date date;
67 l_proc varchar2(72) := g_package||'.get_prior_asg_action';
68
69 /* This cursor gets any other assignment action where:
70 1) For the current Business Group
71 2) The payroll action was complete or incomplete, and the type was Payroll Run or QuickPay.
72 3) The payroll run date is in this period
73 4) For the current Assignment ID
74 5) For the same establishment (Tax_Unit_ID)
75 6) the assignment action was complete
76 7) Where the run contained 'FR_STATUTORY_DEDUCTIONS' element */
77
78
79 cursor pay_asg_act_csr is
80 select paa.assignment_action_id, ppa.effective_date
81 from pay_payroll_actions ppa
82 , pay_assignment_actions paa
83 , pay_run_results prr
84 where ppa.business_group_id = p_business_group_id
85 and ppa.action_type in ('Q','R')
86 and ppa.action_status in ('C','I')
87 and ppa.effective_date between trunc(p_date_earned,'MONTH')
88 and last_day(p_date_earned)
89 and ppa.payroll_action_id = paa.payroll_action_id
90 and paa.assignment_id = p_assignment_id
91 and paa.action_status = 'C'
92 and paa.tax_unit_id = p_tax_unit_id
93 and paa.assignment_action_id = prr.assignment_action_id
94 and prr.source_id = p_orig_entry_id
95 and prr.source_type = 'E';
96 --
97 begin
98 --
99 hr_utility.set_location('Entered '||l_proc,5);
100 hr_utility.set_location('. Asg_ID:'||p_assignment_id||'. BG_ID:'||p_business_group_id||'. Date:'||p_date_earned,20);
101
102 open pay_asg_act_csr;
103 fetch pay_asg_act_csr into l_asg_action_id, l_pay_action_date;
104
105 if pay_asg_act_csr%notfound then
106 hr_utility.set_location('Could not find Previous payroll run this period',40);
107
108 close pay_asg_act_csr;
109 g_prior_asg_action_id := -1;
110 g_prior_pay_action_date := NULL;
111
112 else
113 hr_utility.set_location('Found previous payroll run this period',60);
114
115 close pay_asg_act_csr;
116 g_prior_asg_action_id := l_asg_action_id;
117 g_prior_pay_action_date := l_pay_action_date;
118
119 end if;
120
121 end set_prior_asg_action;
122
123
124
125 function get_prior_run_result(p_element_name in varchar2
126 ,p_input_value_name in varchar2
127 ,p_date_earned in date
128 ,p_assignment_id in number
129 ,p_business_group_id in number
130 ) return varchar2
131 IS
132
133 l_asg_action_id number;
134 l_pay_action_date date;
135 l_run_result_value varchar2(60);
136 l_proc varchar2(72) := g_package||'.get_prior_run_result';
137
138
139 cursor pay_run_result_csr is
140 select prrv.result_value
141 from pay_run_results prr
142 , pay_element_types_f pet
143 , pay_input_values_f piv
144 , pay_run_result_values prrv
145 where prr.assignment_action_id = g_prior_asg_action_id
146 and prr.element_type_id = pet.element_type_id
147 and pet.ELEMENT_NAME = p_element_name
148 and pet.legislation_code = 'FR'
149 and pet.business_group_id is NULL
150 and g_prior_pay_action_date between pet.effective_start_date and pet.effective_end_date
151 and pet.element_type_id = piv.element_type_id
152 and g_prior_pay_action_date between piv.effective_start_date and piv.effective_end_date
153 and piv.name = p_input_value_name
154 and piv.legislation_code = 'FR'
155 and piv.business_group_id is NULL
156 and prrv.input_value_id = piv.input_value_id
157 and prrv.run_result_id = prr.run_result_id;
158 --
159 begin
160 --
161 hr_utility.set_location('Entered '||l_proc,5);
162 hr_utility.set_location('. Element='||p_element_name||'. IV='||p_input_value_name,20);
163
164 if g_prior_asg_action_id <> -1 THEN
165 /* Assignment Action was found in initalize_payroll function.
166 Run cursor to get record using assignment action stored in global */
167 open pay_run_result_csr;
168 fetch pay_run_result_csr into l_run_result_value;
169
170 if pay_run_result_csr%found then
171 hr_utility.set_location('. Found run result value='||l_run_result_value,40);
172
173 close pay_run_result_csr;
174 return l_run_result_value;
175 else
176 hr_utility.set_location('. Run Result Not found',60);
177 close pay_run_result_csr;
178 l_run_result_value:= NULL;
179 end if;
180 else /* no prior runs were found this period */
181 l_run_result_value := NULL;
182 end if;
183
184 return l_run_result_value;
185
186 end get_prior_run_result;
187
188
189 ------------------------------------------------------------------------
190 -- Function GET_PRIOR_BASE_CODE
191 -- This function will be used to obtain the base code for a given
192 -- contribution type should a payroll action already exist this period.
193 ------------------------------------------------------------------------
194 function get_prior_base_code(p_base_element in varchar2
195 ,p_date_earned in date
196 ,p_assignment_id in number
197 ,p_business_group_id in number) return varchar2
198 IS
199
200 l_base_code_val varchar2(10);
201 l_proc varchar2(72) := g_package||'.get_prior_base_code';
202
203 --
204 begin
205 --
206 hr_utility.set_location('Entered '||l_proc,5);
207 hr_utility.set_location('. Finding base code for element:'||p_base_element,20);
208
209 l_base_code_val := get_prior_run_result(p_element_name => p_base_element
210 ,p_input_value_name => 'Base_Code'
211 ,p_date_earned => p_date_earned
212 ,p_assignment_id => p_assignment_id
213 ,p_business_group_id => p_business_group_id);
214
215 return l_base_code_val;
216
217 end get_prior_base_code;
218
219 ------------------------------------------------------------------------
220 -- Function GET_BASE_NAME
221 -- This function will be used to determine the name of a base
222 -- as an input it takes a group code which is used to determine the correct base.
223 -- renamed to old as the seed data currently supports this method but
224 -- core functionality prives primary and base balance relationships
225 ------------------------------------------------------------------------
226 function get_base_name(p_business_group_id in number
227 ,p_group_code in varchar2)
228 return varchar2
229 is
230 l_base_type pay_user_Column_instances_f.value%type;
231 l_proc varchar2(72) := g_package||'.get_base_name';
232 --
233 begin
234 --
235 hr_utility.set_location('Entered '||l_proc,5);
236
237 l_base_type := hruserdt.get_table_value(p_bus_group_id => p_business_group_id
238 ,p_table_name => 'FR_DEDUCTION_GROUPS'
239 ,p_col_name=> 'Base Balance'
240 ,p_row_value => p_group_code);
241
242 hr_utility.set_location('. Base Type:'||l_base_type,20);
243
244 return l_base_type;
245
246 exception
247 when no_data_found then
248 fnd_message.set_name('PAY','PAY_74938_SD_NO_BASE_DUCT_GRP');
249 fnd_message.set_token('DEDUCTION_GROUP',p_group_code);
250 fnd_message.raise_error;
251 end get_base_name;
252 ------------------------------------------------------------------------
253 -- Function GET_BASE_NAME_CU
254 -- This function will be used to determine the name of a base
255 -- type from a contribution_Usage_ID row.
256 ------------------------------------------------------------------------
257 function get_base_name_CU (
258 p_business_group_id in number
259 ,p_cu_id in number)
260 return varchar2
261 is
262
263 l_base_type pay_user_Column_instances_f.value%type;
264 l_group_code pay_fr_contribution_usages.group_code%TYPE;
265 l_proc varchar2(72) := g_package||'.get_base_name_cu';
266 --
267 begin
268 --
269 hr_utility.set_location('Entered '||l_proc,5);
270 --
271 l_group_code := get_group_code(p_cu_id);
272 --
273 l_base_type := hruserdt.get_table_value(p_bus_group_id => p_business_group_id
274 ,p_table_name => 'FR_DEDUCTION_GROUPS'
275 ,p_col_name=> 'Base Balance'
276 ,p_row_value => l_group_code);
277
278 hr_utility.set_location('. Base Type:'||l_base_type,20);
279 hr_utility.set_location('Leaving '||l_proc,25);
280 return l_base_type;
281 exception
282 when no_data_found then
283 fnd_message.set_name('PAY','PAY_74938_SD_NO_BASE_DUCT_GRP');
284 fnd_message.set_token('DEDUCTION_GROUP',l_group_code);
285 fnd_message.raise_error;
286 end get_base_name_CU;
287 ------------------------------------------------------------------------
288 -- Function GET_GROUP_CODE
289 -- This function will be used to determine the group code of a given
290 -- row in pay_fr_contribution_usages
291 ------------------------------------------------------------------------
292 function get_group_code (p_cu_id in number)
293 return varchar2
294 is
295 l_group_code pay_fr_contribution_usages.group_code%TYPE;
296 l_proc varchar2(72) := g_package||'.get_group_code';
297 --
298 begin
299 --
300 hr_utility.set_location('Entered '||l_proc,5);
301 select group_code
302 into l_group_code
303 from pay_fr_contribution_usages
304 where contribution_usage_id = p_cu_id;
305 --
306 hr_utility.set_location('. Group Code:'||l_group_code,20);
307 hr_utility.set_location('Leaving '||l_proc,25);
308 return l_group_code;
309 exception
310 when no_data_found then null; /* calling fn handles error */
311 end get_group_code;
312 ------------------------------------------------------------------------
313 -- Function GET_BASE_VALUE
314 -- This function will be used to determine the value of a base
315 -- as an input it takes a base_type
316 ------------------------------------------------------------------------
317 function get_base_value(p_base_type in varchar2)
318 return number
319 is
320 l_base_value number;
321 l_proc varchar2(72) := g_package||'.get_base_value';
322 --
323 begin
324 --
325 hr_utility.set_location('Entered '||l_proc,5);
326
327 hr_utility.set_location('Base Type='||p_base_type,10);
328
329 l_base_value := g_band_table(get_table_index(p_base_type));
330
331 hr_utility.set_location('leaving: '||l_proc||' Value='||l_base_value,30);
332
333 return l_base_value;
334 exception
335 when no_data_found then
336 fnd_message.set_name('PAY','PAY_74937_SD_NO_BASE_VALUE');
337 fnd_message.set_token('TYPE',p_base_type);
338 fnd_message.raise_error;
339
340 end get_base_value;
341
342 ------------------------------------------------------------------------
343 -- Private Function GET_RATE_VALUE
344 -- This function will be used to determine the value of a rate
345 -- as an input it takes the rate type to determine the correct rate value.
346 ------------------------------------------------------------------------
347 function get_rate_value(p_business_group_id in number
348 ,p_rate_type in varchar2)
349 return number
350 is
351 l_rate_value number;
352 l_proc varchar2(72) := g_package||'.get_rate_value';
353 --
354 begin
355 --
356 hr_utility.set_location('Entered '||l_proc,5);
357 hr_utility.set_location('. Rate Type='||p_rate_type,10);
358
359 l_rate_value := to_number(hruserdt.get_table_value(p_bus_group_id => p_business_group_id
360 ,p_table_name => 'FR_CONTRIBUTION_RATES'
361 ,p_col_name=> 'Value (EUR)'
362 ,p_row_value => p_rate_type));
363
364 hr_utility.set_location('Leaving: '||l_proc||'. Rate='||l_rate_value,40);
365
366 return l_rate_value;
367 exception
368 when no_data_found then
369 fnd_message.set_name('PAY','PAY_74935_SD_NO_RATE');
370 fnd_message.set_token('RATE_TYPE',p_rate_type);
371 fnd_message.raise_error;
372 end get_rate_value;
373
374
375 -- +********************************************************************+
376 -- | PUBLIC FUNCTIONS |
377 -- +********************************************************************+
378 --
379 ------------------------------------------------------------------------
380 -- Function GET_CONTRIBUTION_USAGE
381 -- This function obtains a row from the pay_fr_contribution_usages table
382 -- It will be called from other cover functions.
383 ------------------------------------------------------------------------
384 function get_contribution_usage
385 (p_process_type in varchar2
386 ,p_element_name in varchar2
387 ,p_usage_type in varchar2
388 ,p_effective_date in date
389 ,p_business_group_id in number default null)
390 return pay_fr_contribution_usages%rowtype
391 is
392
393 l_contribution_row pay_fr_contribution_usages%rowtype;
394
395 cursor contribution_usages_csr is
396 select *
397 from pay_fr_contribution_usages cu
398 where cu.process_type = p_process_type
399 and cu.contribution_usage_type = p_usage_type
400 and p_effective_date between cu.date_from and nvl(cu.date_to,to_date('31-12-4712','DD-MM-YYYY'))
401 and cu.element_name = p_element_name
402 and (cu.business_group_id is NULL or cu.business_group_id = p_business_group_id);
403
404
405 begin
406 hr_utility.set_location('Entered pay_fr_general.get_contribution_usage',10);
407 hr_utility.set_location('Obtaining row, Element='||p_element_name, 11);
408 hr_utility.set_location('. p_process_type='||p_process_type,12);
409 hr_utility.set_location('. p_usage_type='||p_usage_type,13);
410
411 OPEN contribution_usages_csr;
412 FETCH contribution_usages_csr INTO l_contribution_row;
413
414 if contribution_usages_csr%notfound then
415 CLOSE contribution_usages_csr;
416 raise no_data_found;
417 ELSE
418 close contribution_usages_csr;
419 end if;
420
421 hr_utility.set_location('Leaving get_contribution_usage, ID:'||l_contribution_row.contribution_usage_id,100);
422 return l_contribution_row;
423
424 exception
425 when no_data_found then
426 begin
427 hr_utility.set_location('ERROR: Contribution Usage missing for:'||p_element_name,100);
428 hr_utility.set_location('. Process Type:'||p_process_type||', Usage:'||p_usage_type,110);
429 fnd_message.set_name('PAY','PAY_74918_SD_NO_CNU_DATA');
430 fnd_message.set_token('ET',p_element_name);
431 fnd_message.set_token('PT',p_process_type);
432 fnd_message.set_token('UT',p_usage_type);
433 fnd_message.raise_error;
434 end;
435 end get_contribution_usage;
436
437 ------------------------------------------------------------------------
438 -- Private Function GET_RATE_VALUE
439 -- This function will be used to determine the value of a rate
440 -- as an input it takes the same inputs as the get_contribution_usage
441 -- to determine ONLY the correct rate value.
442 ------------------------------------------------------------------------
443 function get_rate_value(p_assignment_id in number
444 ,p_business_group_id in number default null
445 ,p_date_earned in date
446 ,p_tax_unit_id in number
447 ,p_element_name in varchar2
448 ,p_usage_type in varchar2
449 ,p_override_rate in number default null) return number
450 is
451
452 l_group_code varchar2(30);
453 l_rate_value number;
454 l_rate_type varchar2(80);
455 l_contribution_usage_id number;
456 l_contribution_code varchar2(30);
457 l_contribution_value number;
458 l_contribution_type varchar2(10);
459 l_base_name pay_user_Column_instances_f.value%type;
460 l_code_rate_id number;
461 l_rate_category varchar2(1);
462 l_element_name pay_fr_contribution_usages.element_name%type;
463
464 l_contribution_usage_row pay_fr_contribution_usages%rowtype;
465
466 l_proc varchar2(72) := g_package||'.GET_RATE_VALUE';
467
468 begin
469 hr_utility.set_location('Entered pay_fr_general.get_rate_type',10);
470 hr_utility.set_location('Obtaining row, Element='||p_element_name, 11);
471 hr_utility.set_location('. p_process_type='||g_process_type,12);
472 hr_utility.set_location('. p_usage_type='||p_usage_type,13);
473 hr_utility.set_location('. p_date_earnd='||p_date_earned,14);
474 hr_utility.set_location('. p_assignt_id='||p_assignment_id,15);
475 hr_utility.set_location('. p_bus_grp_id='||p_business_group_id,16);
476 l_contribution_usage_row:= get_contribution_usage(
477 p_process_type => g_process_type
478 ,p_element_name => p_element_name
479 ,p_usage_type => p_usage_type
480 ,p_effective_date => p_date_earned
481 ,p_business_group_id => p_business_group_id);
482
483 l_group_code := l_contribution_usage_row.group_code;
484 l_rate_type := l_contribution_usage_row.rate_type;
485 l_contribution_code := l_contribution_usage_row.contribution_code;
486 l_contribution_usage_id := l_contribution_usage_row.contribution_usage_id;
487 l_contribution_type := l_contribution_usage_row.contribution_type;
488 l_code_rate_id := l_contribution_usage_row.code_rate_id;
489 l_rate_category := l_contribution_usage_row.rate_category;
490 l_element_name := l_contribution_usage_row.element_name;
491
492 l_rate_value := get_cached_rate(p_assignment_id,l_contribution_usage_id, p_tax_unit_id,
493 l_contribution_code, l_code_rate_id, l_rate_type);
494 if l_rate_value is null then -- { no cached rate
495 hr_utility.set_location('Entered '||l_proc,40);
496 if p_override_rate is null then
497 l_rate_value := get_rate_value(p_business_group_id,l_rate_type);
498 else
499 l_rate_value := p_override_rate;
500 hr_utility.set_location('Using Override Rate Value:'||p_override_rate,80);
501 end if;
502 maintain_rate_cache(l_contribution_usage_id, p_tax_unit_id, l_contribution_code,
503 l_rate_value, l_code_rate_id, l_rate_type);
504 end if;
505
506 hr_utility.set_location('Leaving get_contribution_usage rate value:'||l_rate_value,100);
507 return l_rate_value;
508
509 end get_rate_value;
510
511
512 ------------------------------------------------------------------------
513 -- Function GET_FORMULA_INFO
514 -- This function is used to obtain a fast formula ID and effective_start_date.
515 --
516 -- This function will return -1 if the formula was not found,
517 -- otherwise the formula_id is returned
518 ------------------------------------------------------------------------
519 function get_formula_info
520 (p_formula_name in varchar2
521 ,p_effective_date in date
522 ,p_business_group_id in number default -1
523 ,p_effective_start_date out nocopy date
524 ) return number
525 is
526 cursor csr_get_formula is
527 select ff.formula_id,
528 ff.effective_start_date
529 from ff_formulas_f ff
530 , ff_formula_types ft
531 where ft.formula_type_name = 'Oracle Payroll'
532 and ft.formula_type_id = ff.formula_type_id
533 and ff.formula_name = p_formula_name
534 and p_effective_date between ff.effective_start_date and ff.effective_end_date
535 and nvl(ff.business_group_id,-1) = p_business_group_id
536 and nvl(ff.legislation_code,'FR') = 'FR';
537
538 l_formula_id number;
539 l_start_date date;
540
541 begin
542 open csr_get_formula;
543 fetch csr_get_formula into l_formula_id, l_start_date;
544 If csr_get_formula%found then
545 p_effective_start_date := l_start_date;
546 else
547 /* If the formula was not found then return -1 to indicate an error */
548 l_formula_id := -1;
549 p_effective_start_date := to_date('01011900','DDMMYYYY');
550 end if;
551
552 close csr_get_formula;
553
554 return l_formula_id;
555
556 end get_formula_info;
557
558
559 --
560 ------------------------------------------------------------------------
561 -- Function SUB_CONTRIB_CODE
562 -- This function will determine the full contribution code by substituting
563 -- into the pattern contribution code the correct base code.
564 ------------------------------------------------------------------------
565 function sub_contrib_code(p_contribution_type in varchar2
566 ,p_contribution_code in varchar2) return varchar2 IS
567 --
568 l_full_code varchar2(7);
569 l_base_code varchar2(4);
570 l_proc varchar2(72) := g_package||'.sub_contrib_code';
571 --
572 begin
573 --
574 hr_utility.set_location('Entered '||l_proc,5);
575
576 /* Some contributions do not have a contribution code, therefore do not substitute */
577 if p_contribution_code is NULL then
578 return null;
579 else
580 /* Substitute the correct base code into the contribution code */
581 If p_contribution_type in ('URSSAF','ASSEDIC','AGIRC','ARRCO') then
582 /* obtain correct base code value */
583 l_base_code := g_base_code_table(get_table_index(p_contribution_type));
584
585 IF p_contribution_type = 'URSSAF' THEN
586 l_full_code := '1'|| l_base_code || substr(p_contribution_code,4,4);
587 ELSIF p_contribution_type = 'ASSEDIC' THEN
588 l_full_code := '2'|| l_base_code || substr(p_contribution_code,3,5);
589 ELSIF p_contribution_type = 'AGIRC' THEN
590 l_full_code := '3'|| l_base_code || substr(p_contribution_code,6,2);
591 ELSE /* Must be ARRCO */
592 l_full_code := '4'|| l_base_code || substr(p_contribution_code,6,2);
593 END IF;
594
595 hr_utility.set_location('Leaving, code found='||l_full_code,50);
596 return l_full_code;
597
598 ELSE
599 /* Contribution Type was not one of the four valid values */
600 fnd_message.set_name('PAY', 'PAY_74909_CNU_BAD_CONT_TYPE');
601 fnd_message.raise_error;
602 END IF;
603 end if;
604 exception
605 when no_data_found then
606 fnd_message.set_name('PAY','PAY_74914_SD_NO_BASE_CODE_VAL');
607 fnd_message.set_token('TYPE',p_contribution_type);
608 fnd_message.raise_error;
609
610 end sub_contrib_code;
611
612 ------------------------------------------------------------------------
613 -- Function GET_PAYROLL_MESSAGE
614 -- This function is used to obtain a message.
615 -- The token parameters must be of the form 'TOKEN_NAME:TOKEN_VALUE' i.e.
616 -- If you want to set the value of a token called ELEMENT to FR_ER_SMID
617 -- the token parameter would be 'ELEMENT:FR_ER_SMID'
618 ------------------------------------------------------------------------
619 function get_payroll_message
620 (p_message_name in varchar2
621 ,p_token1 in varchar2 default null
622 ,p_token2 in varchar2 default null
623 ,p_token3 in varchar2 default null) return varchar2
624 is
625 l_message varchar2(2000);
626 l_token_name varchar2(20);
627 l_token_value varchar2(80);
628 l_colon_position number;
629 l_proc varchar2(72) := g_package||'.get_payroll_name';
630 --
631 begin
632 --
633 hr_utility.set_location('Entered '||l_proc,5);
634 hr_utility.set_location('. Message Name: '||p_message_name,40);
635
636 fnd_message.set_name('PAY', p_message_name);
637
638 if p_token1 is not null then
639 /* Obtain token 1 name and value */
640 l_colon_position := instr(p_token1,':');
641 l_token_name := substr(p_token1,1,l_colon_position-1);
642 l_token_value := substr(p_token1,l_colon_position+1,length(p_token1));
643 fnd_message.set_token(l_token_name, l_token_value);
644 hr_utility.set_location('. Token1: '||l_token_name||'. Value: '||l_token_value,50);
645 end if;
646
647 if p_token2 is not null then
648 /* Obtain token 2 name and value */
649 l_colon_position := instr(p_token2,':');
650 l_token_name := substr(p_token2,1,l_colon_position-1);
651 l_token_value := substr(p_token2,l_colon_position+1,length(p_token2));
652 fnd_message.set_token(l_token_name, l_token_value);
653 hr_utility.set_location('. Token2: '||l_token_name||'. Value: '||l_token_value,60);
654 end if;
655
656 if p_token3 is not null then
657 /* Obtain token 3 name and value */
658 l_colon_position := instr(p_token3,':');
659 l_token_name := substr(p_token3,1,l_colon_position-1);
660 l_token_value := substr(p_token3,l_colon_position+1,length(p_token3));
661 fnd_message.set_token(l_token_name, l_token_value);
662 hr_utility.set_location('. Token3: '||l_token_name||'. Value: '||l_token_value,70);
663 end if;
664
665 l_message := substrb(fnd_message.get,1,250);
666
667 hr_utility.set_location('leaving '||l_proc,100);
668
669 return l_message;
670 end get_payroll_message;
671
672
673
674 ------------------------------------------------------------------------
675 -- Function INITIALIZE_PAYROLL
676 -- This function is used to initialise a number of global variables
677 -- that are used by payroll processing.
678 ------------------------------------------------------------------------
679 function initialize_payroll
680 (p_business_group_id in number
681 ,p_effective_date in date
682 ,p_assignment_id in number
683 ,p_tax_unit_id in number
684 ,p_process_type in varchar2
685 ,p_orig_entry_id in number
686 ,p_asg_action_id in number
687 ,p_payroll_id in number
688 ,P_ASG_HOURS in number
689 ,p_asg_frequency in varchar2 ) return number
690 is
691 l_proc varchar2(72) := g_package||'.initialize_payroll';
692 --
693 l_parent_action_id number;
694 --
695 cursor csr_parent_action is
696 select nvl(nvl(act_parent.source_action_id, act_child.source_action_id),act_child.assignment_action_id)
697 from pay_assignment_actions act_child,
698 pay_assignment_actions act_parent
699 where act_child.assignment_action_id = p_asg_action_id
700 and act_parent.assignment_action_id (+) = act_child.source_action_id;
701 begin
702 --
703 hr_utility.set_location('Entered '||l_proc,5);
704 -- First check if this is a supplementary run. Call consumed entry
705 -- only on change of (grand) parent action.
706 open csr_parent_action;
707 fetch csr_parent_action into l_parent_action_id;
708 close csr_parent_action;
709
710 if g_parent_asg_action_id is null
711 or g_parent_asg_action_id <> l_parent_action_id then
712 g_parent_asg_action_id := l_parent_action_id;
713 if pay_consumed_entry.consumed_entry
714 (p_date_earned => p_effective_date
715 ,p_payroll_id => p_payroll_id
716 ,p_ele_entry_id => p_orig_entry_id) = 'Y'
717 then
718 -- return with error status
719 hr_utility.set_location(' Leaving '||l_proc,10);
720 return 1;
721 end if;
722 -- 115.46 Also clear rate cache on change of (grand) parent action
723 g_deduction_rates.delete;
724 end if;
725
726 /* Set Contribution information global variables */
727
728 /* Delete all existing values from the PL/SQL temp tables */
729 g_band_table.delete;
730 g_base_code_table.delete;
731 g_summary_deductions.delete;
732 g_summary_idx := 0;
733
734 g_process_type := p_process_type;
735
736 g_monthly_hours := convert_hours(p_effective_date => p_effective_date
737 ,p_business_group_id => p_business_group_id
738 ,p_assignment_id => p_assignment_id
739 ,p_hours => p_asg_hours
740 ,p_from_freq_code => p_asg_frequency
741 ,p_to_freq_code => 'M');
742
743 -- Ver 115.16 Added Call to set_prior_asg_action
744 set_prior_asg_action(p_date_earned => p_effective_date
745 ,p_assignment_id => p_assignment_id
746 ,p_business_group_id => p_business_group_id
747 ,p_tax_unit_id => p_tax_unit_id -- Tax_Unit_id is Establishment_ID
748 ,p_orig_entry_id => p_orig_entry_id );
749
750 hr_utility.set_location('leaving pay_fr_general.initialize_payroll',50);
751 return 0;
752 end initialize_payroll;
753
754
755 ------------------------------------------------------------------------
756 -- Function GET_URSSAF_BASE_CODE
757 -- This function will obtain the base code for URSSAF contributions.
758 ------------------------------------------------------------------------
759 function get_urssaf_base_code(P_ASSIGNMENT_ID in number
760 ,P_BUSINESS_GROUP_ID in number
761 ,p_date_earned in date
762 ,P_ESTAB_FORMAT_NUMBER in VARCHAR2
763 ,P_ESTAB_WORK_ACCIDENT_ORDER_NO in VARCHAR2
764 ) return varchar2
765 is
766
767 l_base_code varchar2(2);
768 l_index number;
769 l_existing_row varchar2(4);
770 l_proc varchar2(72) := g_package||'.get_urssaf_base_code';
771 --
772 begin
773 --
774 hr_utility.set_location('Entered '||l_proc,5);
775
776 /* Check to see if run already exists this period and if it does obtain base_code from the input value
777 of the URSSAF_BASES element */
778
779 l_base_code := get_prior_base_code(p_base_element => 'FR_URSSAF_BASES'
780 ,p_date_earned => p_date_earned
781 ,p_assignment_id => p_assignment_id
782 ,p_business_group_id => p_business_group_id);
783
784 IF l_base_code is null then
785 hr_utility.set_location('Estab Format No:'''||p_estab_format_number||''' ',50);
786 hr_utility.set_location('Estab Wrk Accident Ord No:'''||p_estab_work_accident_order_no||''' ',51);
787 l_base_code := P_ESTAB_FORMAT_NUMBER || P_ESTAB_WORK_ACCIDENT_ORDER_NO;
788 ELSE
789 l_base_code := substr(l_base_code,1,1)||P_ESTAB_WORK_ACCIDENT_ORDER_NO;
790 END IF;
791
792 hr_utility.set_location('URSSAF base code:'||l_base_code,20);
793
794
795 /* WRITE VALUE TO PL/SQL TABLE TO BE USED BY CONTRIBUTIONS PROCESS */
796 l_index := get_table_index('URSSAF');
797
798 /* Ensure that the index has not already been used */
799 begin
800 l_existing_row := g_base_code_table(l_index);
801
802 /* If a no_data_found exception did not occur then the index is
803 already in use and hence an error has occured */
804 fnd_message.set_name('PAY','PAY_74915_SD_NON_UNIQUE_INDEX');
805 fnd_message.set_token('NAME','URSSAF');
806 fnd_message.raise_error;
807
808 exception
809 when no_data_found then null; -- success, the index number has not been used
810 end;
811
812 g_base_code_table(l_index) := l_base_code;
813
814 hr_utility.set_location('URSSAF base code written to PL/SQL table',50);
815
816 return l_base_code;
817
818 end get_urssaf_base_code;
819
820
821 ------------------------------------------------------------------------
822 -- Function GET_ASSEDIC_BASE_CODE
823 -- This function will obtain the base code for ASSEDIC contributions.
824 ------------------------------------------------------------------------
825 function get_assedic_base_code(p_assignment_id in number
826 ,P_BUSINESS_GROUP_ID in number
827 ,p_date_earned in date
828 ,P_ESTAB_ASSEDIC_ORDER_NUMBER in varchar2
829 ) return varchar2
830 is
831 --
832 l_base_code varchar2(1);
833 l_existing_row varchar2(4);
834 l_index number;
835 l_proc varchar2(72) := g_package||'.get_assedic_base_code';
836 --
837 begin
838 --
839 hr_utility.set_location('Entered '||l_proc,5);
840
841 /* Check to see if run already exists this period and if it does obtain base_code
842 from the input value of the ASSEDIC_BASES element */
843
844 l_base_code := get_prior_base_code(p_base_element => 'FR_ASSEDIC_BASES'
845 ,p_date_earned => p_date_earned
846 ,p_assignment_id => p_assignment_id
847 ,p_business_group_id => p_business_group_id);
848
849 IF l_base_code is null then
850 l_base_code := P_ESTAB_ASSEDIC_ORDER_NUMBER;
851
852 END IF;
853
854 hr_utility.set_location('ASSEDIC base code:'||l_base_code,20);
855
856
857 /* WRITE VALUE TO PL/SQL TABLE TO BE USED BY CONTRIBUTIONS PROCESS */
858 l_index := get_table_index('ASSEDIC');
859
860 /* Ensure that the index has not already been used */
861 begin
862 l_existing_row := g_base_code_table(l_index);
863
864 /* If a no_data_found exception did not occur then the index is
865 already in use and hence an error has occured */
866 fnd_message.set_name('PAY','PAY_74915_SD_NON_UNIQUE_INDEX');
867 fnd_message.set_token('NAME','ASSEDIC');
868 fnd_message.raise_error;
869
870 exception
871 when no_data_found then null; -- success, the index number has not been used
872 end;
873
874 g_base_code_table(l_index) := l_base_code;
875
876 hr_utility.set_location('ASSEDIC base code written to PL/SQL table',50);
877
878
879 return l_base_code;
880
881 end get_assedic_base_code;
882
883
884 ------------------------------------------------------------------------
885 -- Function GET_PENSION_BASE_CODE
886 -- This function will obtain the base code for PENSION contributions, it
887 -- handles both AGRIC and ARRCO base code because as a parameter
888 -- the provider type must be passed in.
889 ------------------------------------------------------------------------
890 function get_pension_base_code(p_establishment_id in number
891 ,p_assignment_id in number
892 ,P_BUSINESS_GROUP_ID in number
893 ,p_date_earned in date
894 ,p_emp_pension_provider_id in number
895 ,p_provider_type in varchar2
896 ,p_emp_pension_category in varchar2
897 ) return varchar2
898 is
899
900 cursor estab_pen_prvs_csr is
901 select oi1.org_information4 order_number
902 from hr_organization_information oi1
903 where oi1.organization_id = p_establishment_id
904 and oi1.org_information_context = 'FR_ESTAB_PE_PRVS'
905 and oi1.org_information1 = p_emp_pension_provider_id;
906
907 cursor estab_default_pen_prvs_csr is
908 select oi1.org_information4 order_number
909 from hr_organization_information oi1
910 , hr_organization_information oi2
911 where oi1.organization_id = p_establishment_id
912 and oi1.org_information_context = 'FR_ESTAB_PE_PRVS'
913 and oi1.org_information3 = 'Y'
914 and oi1.org_information1 = oi2.organization_id
915 and oi2.org_information2 = p_provider_type
916 and oi2.org_information_context = 'FR_PE_PRV_INFO';
917
918
919 l_order_number varchar2(20);
920 l_provider_id number;
921 l_base_code varchar2(4);
922 l_existing_row varchar2(4);
923 l_index number;
924 --
925 begin
926 hr_utility.set_location('Entered get_pension_base_code, TYPE='||p_provider_type,10);
927
928 /* Check to see if run already exists this period and if it does obtain base_code from the input value
929 of the BASES element */
930
931 l_base_code := get_prior_base_code(p_base_element => 'FR_'||p_provider_type||'_BASES'
932 ,p_date_earned => p_date_earned
933 ,p_assignment_id => p_assignment_id
934 ,p_business_group_id => p_business_group_id);
935
936 IF l_base_code is null then
937 /* No previous run found so obtain the latest information */
938
939 IF p_emp_pension_provider_id <> -1 THEN
940 hr_utility.set_location('Provider set on Pension Element is:'||p_emp_pension_provider_id,15);
941 hr_utility.set_location('Establishment ='||p_establishment_id,15);
942 /* The provider has been set on the Pension Information Element
943 therefore obtain the estab info for that provider */
944
945 open estab_pen_prvs_csr;
946 fetch estab_pen_prvs_csr into l_order_number;
947
948 if estab_pen_prvs_csr%notfound then
949 close estab_pen_prvs_csr;
950 fnd_message.set_name('PAY','PAY_74926_SD_BAD_PEN_PRVS');
951 fnd_message.set_token('TYPE',p_provider_type);
952 fnd_message.raise_error;
953 else
954 close estab_pen_prvs_csr;
955 end if;
956 hr_utility.set_location('Order Number Found:'||l_order_number,20);
957
958 ELSE /* find the default pension provider from the estab eit i.e. Provider not set on Pension element*/
959
960 hr_utility.set_location('About to obtain default pension provider from Estab:'||p_establishment_id,30);
961
962 open estab_default_pen_prvs_csr;
963 fetch estab_default_pen_prvs_csr into l_order_number;
964
965 if estab_default_pen_prvs_csr%notfound then
966 close estab_default_pen_prvs_csr;
967 /* Raise Application error as no default provider could be found */
968 fnd_message.set_name('PAY','PAY_74917_SD_NO_DFLT_PEN_PRVS');
969 fnd_message.set_token('TYPE',p_provider_type);
970 fnd_message.raise_error;
971 else
972 /* Check that only one default was set - i.e. no more records should be found */
973 fetch estab_default_pen_prvs_csr into l_order_number;
974
975 if estab_default_pen_prvs_csr%found then
976 close estab_default_pen_prvs_csr;
977 /* If a row was found then an error has occured */
978 fnd_message.set_name('PAY','PAY_74916_SD_BAD_DFLT_PEN_PRVS');
979 fnd_message.set_token('TYPE',p_provider_type);
980 fnd_message.raise_error;
981 ELSE
982 hr_utility.set_location('Order Number Found:'||l_order_number,39);
983 close estab_default_pen_prvs_csr;
984 end if;
985 end if;
986 END IF; /* end of obtaining default provider */
987
988 l_base_code := l_order_number || p_emp_pension_category;
989
990 END IF; /* End of section that obtain new values if previous run was not found */
991
992 /* WRITE VALUE TO PL/SQL TABLE TO BE USED BY CONTRIBUTIONS PROCESS */
993 l_index := get_table_index(p_provider_type);
994
995 /* Ensure that the index has not already been used */
996 begin
997 l_existing_row := g_base_code_table(l_index);
998
999 /* If a no_data_found exception did not occur then the index is
1000 already in use and hence an error has occured */
1001 fnd_message.set_name('PAY','PAY_74915_SD_NON_UNIQUE_INDEX');
1002 fnd_message.set_token('NAME',p_provider_type);
1003 fnd_message.raise_error;
1004
1005 exception
1006 when no_data_found then null; -- success, the index number has not been used before
1007 end;
1008
1009 g_base_code_table(l_index) := l_base_code;
1010
1011 hr_utility.set_location(p_provider_type||' base code written to PL/SQL table',50);
1012
1013
1014 return l_base_code;
1015
1016 end get_pension_base_code;
1017
1018
1019 ------------------------------------------------------------------------
1020 -- Function GET_CONTRIBUTION_BAND
1021 -- This will be used to retrieve the URSSAF, ASSEDIC, ARRCO and AGIRC
1022 -- band values (excluding GMP_BAND).
1023 ------------------------------------------------------------------------
1024 function get_contribution_band(
1025 p_business_group_id in number
1026 ,p_band_type in varchar2
1027 ,p_ytd_ss_ceiling in number
1028 ,p_ytd_base in number
1029 ,p_ytd_band in number
1030 ) return number
1031 is
1032 l_band_low_value number;
1033 l_band_high_value number;
1034 l_ytd_low_value number;
1035 l_ytd_high_value number;
1036 l_run_band number;
1037 --
1038 begin
1039 hr_utility.set_location('Entered get_contribution_band, band_type= '||p_band_type,0);
1040 hr_utility.set_location('SS_ceiling_YTD: '||p_ytd_ss_ceiling||', ytd_base:'||p_ytd_base||', ytd_band: '||p_ytd_band,1);
1041
1042 l_band_low_value := to_number(hruserdt.get_table_value(p_bus_group_id => p_business_group_id
1043 ,p_table_name => 'FR_CONTRIBUTION_BANDS'
1044 ,p_col_name=> 'LOW_VALUE'
1045 ,p_row_value => p_band_type));
1046 hr_utility.set_location('Low value found= '||l_band_low_value,4);
1047
1048 l_band_high_value := to_number(hruserdt.get_table_value(p_bus_group_id => p_business_group_id
1049 ,p_table_name => 'FR_CONTRIBUTION_BANDS'
1050 ,p_col_name=> 'HIGH_VALUE'
1051 ,p_row_value => p_band_type));
1052 hr_utility.set_location('High value found= '||l_band_high_value,6);
1053
1054 --
1055 l_ytd_low_value := p_ytd_ss_ceiling * l_band_low_value;
1056 l_ytd_high_value := p_ytd_ss_ceiling * l_band_high_value;
1057
1058 --
1059 if p_ytd_base < l_ytd_low_value then
1060 l_run_band := -1 * p_ytd_band;
1061 else if l_ytd_low_value <= p_ytd_base and p_ytd_base <= l_ytd_high_value then
1062 l_run_band := p_ytd_base - p_ytd_band - l_ytd_low_value;
1063 else
1064 l_run_band := l_ytd_high_value - p_ytd_band - l_ytd_low_value;
1065 end if;
1066 end if;
1067
1068 l_run_band := round(l_run_band,2);
1069
1070 hr_utility.set_location('Band: '||p_band_type||' Value calculated= '||l_run_band,8);
1071 --
1072 return l_run_band;
1073 end get_contribution_band;
1074
1075
1076 ------------------------------------------------------------------------
1077 -- Function GET_GMP_BAND
1078 -- This will be used to retrieve the GMP band value
1079 ------------------------------------------------------------------------
1080 function get_gmp_band(p_ytd_gmp_ceiling in number
1081 ,p_ytd_gmp_band in number
1082 ,p_ytd_p3_band in number
1083 ,p_run_p3_band in number) return number
1084
1085 IS
1086 l_new_ytd_p3_band number;
1087 l_ytd_gmp_value number;
1088 l_run_gmp_band number;
1089 begin
1090 hr_utility.set_location('Entered pay_fr_general.get_gmp_band',10);
1091
1092 hr_utility.set_location('YTD GMP ceiling:'||p_ytd_gmp_ceiling,25);
1093 l_new_ytd_p3_band := p_ytd_p3_band + p_run_p3_band;
1094 hr_utility.set_location('YTD P3 band:'||l_new_ytd_p3_band,30);
1095
1096 if p_ytd_gmp_ceiling > l_new_ytd_p3_band then
1097 l_ytd_gmp_value := p_ytd_gmp_ceiling - l_new_ytd_p3_band;
1098 else
1099 l_ytd_gmp_value := 0;
1100 end if;
1101
1102 hr_utility.set_location('YTD GMP band:'||l_ytd_gmp_value,35);
1103
1104 l_run_gmp_band := l_ytd_gmp_value - p_ytd_gmp_band;
1105
1106 l_run_gmp_band := round(l_run_gmp_band,2);
1107
1108 hr_utility.set_location('GMP Band Run Value calculated: '||l_run_gmp_band,50);
1109
1110 return l_run_gmp_band;
1111 end get_gmp_band;
1112
1113
1114 ------------------------------------------------------------------------
1115 -- Function GET_SALARY_TAX_BAND
1116 -- This will be used to retrieve the Salary Tax band values
1117 ------------------------------------------------------------------------
1118 function get_salary_tax_band(p_business_group_id in number,
1119 p_band_type in varchar2,
1120 p_ptd_base in number,
1121 p_ptd_band in number) return number
1122 is
1123 l_low_value number;
1124 l_high_value number;
1125 l_run_band number;
1126
1127 begin
1128 --
1129 hr_utility.set_location('Entered fr_get_salary_tax_band, band_type= '||p_band_type,10);
1130 hr_utility.set_location('Base PTD='||p_ptd_base||', Band PTD='||p_ptd_band,15);
1131
1132 l_low_value := to_number(hruserdt.get_table_value(p_bus_group_id => p_business_group_id
1133 ,p_table_name => 'FR_CONTRIBUTION_BANDS'
1134 ,p_col_name=> 'LOW_VALUE'
1135 ,p_row_value => p_band_type));
1136 l_high_value := to_number(hruserdt.get_table_value(p_bus_group_id => p_business_group_id
1137 ,p_table_name => 'FR_CONTRIBUTION_BANDS'
1138 ,p_col_name=> 'HIGH_VALUE'
1139 ,p_row_value => p_band_type));
1140 hr_utility.set_location('low band='||l_low_value||', high_band='||l_high_value,20);
1141
1142
1143 l_low_value := l_low_value / 12;
1144 l_high_value := l_high_value / 12;
1145 --
1146 hr_utility.set_location('low band='||l_low_value||', high_band='||l_high_value,20);
1147 if p_ptd_base < l_low_value then
1148 l_run_band :=0;
1149 elsif p_ptd_base < l_high_value then
1150 l_run_band := p_ptd_base - p_ptd_band - l_low_value;
1151 else
1152 l_run_band := l_high_value - p_ptd_band - l_low_value;
1153 end if;
1154
1155 l_run_band := round(l_run_band,2);
1156
1157 hr_utility.set_location('Band Value: '||l_run_band,50);
1158
1159 return l_run_band;
1160 --
1161 end get_salary_tax_band;
1162
1163 ------------------------------------------------------------------------
1164 -- Function WRITE_BASE_BAND
1165 -- This will be used to store a base or band value in a PL/SQL table
1166 -- to enable the value to be easily obtained later in the payroll process
1167 ------------------------------------------------------------------------
1168 function WRITE_BASE_BANDS(p_name in varchar2
1169 ,p_value in number) return number
1170 is
1171 l_index number;
1172 l_existing_row number;
1173 begin
1174 l_index := get_table_index(p_name);
1175
1176 /* Ensure that the band index has not already been used */
1177 begin
1178 l_existing_row := g_band_table(l_index);
1179
1180 /* If a no_data_found exception did not occur then the index is
1181 already in use and hence an error has occured */
1182 fnd_message.set_name('PAY','PAY_74915_SD_NON_UNIQUE_INDEX');
1183 fnd_message.set_token('NAME',p_name);
1184 fnd_message.raise_error;
1185
1186 return 1; --Return 1 to indicate an error occured
1187
1188 exception
1189 when no_data_found then null; -- success, the index number has not been used already
1190 end;
1191
1192 g_band_table(l_index) := p_value;
1193
1194 hr_utility.set_location('WRITE_BASE_BAND: '||p_name||', value: '||p_value||', Index: '||l_index,500);
1195
1196 return 0; -- Return 0 to indicate success
1197 end write_base_bands;
1198
1199 ------------------------------------------------------------------------
1200 -- Function WRITE_CALENDAR_DAYS_WORKED
1201 -- This will be used to store the value of calendar days worked,
1202 -- as determined in the formula FR_SS_CEILING. The value is stored in a
1203 -- global so that it can be used in GET_GMP_BAND for pro-ration
1204 ------------------------------------------------------------------------
1205 function WRITE_CALENDAR_DAYS_WORKED(p_calendar_days_worked in number) return number
1206 is
1207
1208 begin
1209 g_calendar_days_worked := p_calendar_days_worked;
1210 return 0; -- Indicates success
1211 end write_calendar_days_worked;
1212
1213 ------------------------------------------------------------------------
1214 -- Function READ_CALENDAR_DAYS_WORKED
1215 -- This will be used to read the value of calendar days worked,
1216 -- this value is stored as a package variable
1217 ------------------------------------------------------------------------
1218 function READ_CALENDAR_DAYS_WORKED return number
1219 is
1220
1221 begin
1222 --
1223 return pay_fr_general.g_calendar_days_worked;
1224 --
1225 end read_calendar_days_worked;
1226
1227 ------------------------------------------------------------------------
1228 -- Function GET_DAYS_OVER_PENSION_LIMIT
1229 -- This function obtains the number of days in the month that are
1230 -- over the annual absence days limit.
1231 ------------------------------------------------------------------------
1232 function get_days_over_pension_limit(p_assignment_id in number
1233 ,p_business_group_id in number
1234 ,p_pay_period_start_date in date
1235 ,p_pay_period_end_date in date
1236 ,p_abs_days_limit in number) return number
1237 is
1238 l_proc varchar2(72) := g_package||'.get_days_over_pension_limit';
1239 l_days_over_limit number;
1240 l_start_of_cal_year date;
1241 l_non_abs_day date;
1242 l_num_rolling_abs_days number;
1243 l_start_cal_year date;
1244 l_start_prev_cal_year date;
1245 l_non_abs_day_prev_yr date;
1246 l_debug_number number;
1247
1248 function get_non_absence_day(p_start_of_year in date
1249 ,p_assignment_id in number
1250 ,p_business_group_id in number) return date
1251 is
1252 l_first_absence_day date;
1253 /* The absence records to consider should only be those that feed
1254 the sickness days absence balance */
1255 cursor first_sickness_abs_csr is
1256 select min(paa.date_start)
1257 from pay_balance_types pbt
1258 , pay_balance_feeds_f pbf
1259 , pay_input_values_f piv
1260 , pay_element_types_f pet
1261 , pay_element_entries_f pee
1262 , pay_element_links_f pel
1263 , per_absence_attendances paa
1264 where pbt.balance_name = 'FR_SICKNESS_ABSENCE_DAYS'
1265 and pbt.business_group_id IS NULL
1266 and pbt.legislation_code = 'FR'
1267 and pbt.balance_type_id = pbf.balance_type_id
1268 and pbf.input_value_id = piv.input_value_id
1269 and pbf.business_group_id = p_business_group_id
1270 and piv.element_type_id = pet.element_type_id
1271 and pet.element_type_id = pel.element_type_id
1272 and pet.business_group_id = p_business_group_id
1273 and pel.element_link_id = pee.element_link_id
1274 and pee.assignment_id = p_assignment_id
1275 and pee.creator_type = 'A'
1276 and pee.creator_id = paa.absence_attendance_id
1277 and paa.date_start > p_start_of_year
1278 and pbf.effective_start_date <= pee.effective_start_date
1279 and pbf.effective_end_date >= pee.effective_end_date;
1280
1281 begin
1282
1283 /* This function will obtain the 1st absence in the year that does not span the year end
1284 boundary. It will then return the day prior to this.
1285 Even though this is not the first non absence day. This date is acceptable because all we are
1286 really interested in is the fact that no absence occurred up to that point in the year */
1287
1288 open first_sickness_abs_csr;
1289 fetch first_sickness_abs_csr into l_first_absence_day;
1290
1291 if first_sickness_abs_csr%notfound then
1292 hr_utility.set_location('Could not find any absence in year starting:'||to_char(p_start_of_year),10);
1293
1294 close first_sickness_abs_csr;
1295 return p_start_of_year;
1296 else
1297 hr_utility.set_location('First Absence found was:'||to_char(l_first_absence_day),20);
1298 l_first_absence_day := l_first_absence_day -1;
1299 hr_utility.set_location('Prior day was:'||to_char(l_first_absence_day),30);
1300
1301 close first_sickness_abs_csr;
1302 return l_first_absence_day;
1303 end if;
1304
1305 end get_non_absence_day;
1306
1307 function get_abs_days(p_from_date in date
1308 ,p_to_date in date
1309 ,p_assignment_id in number
1310 ,p_business_group_id in number) return number
1311 is
1312 l_abs_days number;
1313 /* The absence records to consider should only be those that feed
1314 the sickness days absence balance */
1315 cursor count_sickness_abs_csr is
1316 select /*+ USE_NL(pbt pee pee2) */
1317 sum(paa.absence_days)
1318 from pay_balance_types pbt
1319 , pay_balance_feeds_f pbf
1320 , pay_input_values_f piv
1321 , pay_element_types_f pet
1322 , pay_element_entries_f pee
1323 , pay_element_links_f pel
1324 , per_absence_attendances paa
1325 , pay_element_entries_f pee2
1326 , pay_element_links_f pel2
1327 , pay_element_types_f pet2
1328 where pbt.balance_name = 'FR_SICKNESS_ABSENCE_DAYS'
1329 and pbt.business_group_id IS NULL
1330 and pbt.legislation_code = 'FR'
1331 and pbt.balance_type_id = pbf.balance_type_id
1332 and pbf.input_value_id = piv.input_value_id
1333 and pbf.business_group_id = p_business_group_id
1334 and piv.element_type_id = pet.element_type_id
1335 and pet.element_type_id = pel.element_type_id
1336 and pet.business_group_id = p_business_group_id
1337 and pet.effective_start_date <= p_from_date
1338 and pet.effective_end_date >= p_to_date
1339 and pel.element_link_id = pee.element_link_id
1340 and pee.assignment_id = p_assignment_id
1341 and pee.creator_type = 'A'
1342 and pee.creator_id = paa.absence_attendance_id
1343 and paa.date_start >= p_from_date
1344 and paa.date_end <= p_to_date
1345 and pbf.effective_start_date <= pee.effective_start_date
1346 and pbf.effective_end_date >= pee.effective_end_date
1347 /* Added to ensure that absences where employee is ARRCO are excluded */
1348 and paa.date_start between pee2.effective_start_date and pee2.effective_end_date
1349 and pee2.assignment_id = p_assignment_id
1350 and pee2.entry_type = 'E'
1351 and pee2.creator_type = 'F'
1352 and pee2.element_link_id = pel2.element_link_id
1353 and paa.date_start between pel2.effective_start_date and pel2.effective_end_date
1354 and pel2.element_type_id = pet2.element_type_id
1355 and paa.date_start between pet2.effective_start_date and pet2.effective_end_date
1356 and pet2.element_name = 'FR_PENSION'
1357 and pet2.legislation_code = 'FR'
1358 and 'Y' = hruserdt.get_table_value(p_business_group_id
1359 , 'FR_APEC_AGIRC', 'AGIRC'
1360 , pee2.entry_information1, paa.date_start);
1361
1362 begin
1363 hr_utility.set_location('Entered get_abs_days, ASG_ID:'||p_assignment_id||', BG_ID:'||p_business_group_id,10);
1364 hr_utility.set_location('. From:'||p_from_date||', To:'||p_to_date,20);
1365
1366 open count_sickness_abs_csr;
1367 fetch count_sickness_abs_csr into l_abs_days;
1368
1369 if count_sickness_abs_csr%notfound then
1370 hr_utility.set_location('Could not find any absence in period:'||p_from_date||', To:'||p_to_date,10);
1371
1372 close count_sickness_abs_csr;
1373 l_abs_days := 0;
1374 else
1375 hr_utility.set_location('Found Absence days='||l_abs_days,20);
1376 close count_sickness_abs_csr;
1377 end if;
1378
1379 return l_abs_days;
1380
1381 end get_abs_days;
1382 --
1383 begin
1384 --
1385 hr_utility.set_location('Entered '||l_proc,5);
1386 hr_utility.set_location('Abs Days Limit:'||p_abs_days_limit,10);
1387
1388 /* Get the start of the year and the day where no absences exist up to */
1389 l_start_cal_year := trunc(p_pay_period_start_date,'YEAR');
1390
1391 hr_utility.set_location('Start of Payroll Year: '||l_start_cal_year,10);
1392
1393 l_non_abs_day := get_non_absence_day(p_start_of_year => l_start_cal_year,
1394 p_assignment_id => p_assignment_id,
1395 p_business_group_id => p_business_group_id);
1396 hr_utility.set_location('First non absence day: '||l_non_abs_day,20);
1397
1398 if l_non_abs_day < p_pay_period_start_date then
1399 hr_utility.set_location('Rolling Year Has been rest',30);
1400 /* The rolling year has been reset prior to this period. Therefore get the
1401 absence day count from the sart of the new rolling period to the end
1402 of this month */
1403 l_num_rolling_abs_days := get_abs_days(p_from_date => l_non_abs_day,
1404 p_to_date => p_pay_period_end_date,
1405 p_assignment_id => p_assignment_id,
1406 p_business_group_id => p_business_group_id);
1407 else
1408 hr_utility.set_location('Still in Rolling year',40);
1409 /* Otherwise the rolling year is still continuing from the previous year.
1410 Therefore get the rolling absence count start from the previous years first
1411 absence. Up to whichever occurs first of, the end of the payroll period or
1412 the first non absence day this year. */
1413 l_start_prev_cal_year := add_months(l_start_cal_year,-12);
1414
1415 l_non_abs_day_prev_yr := get_non_absence_day(p_start_of_year => l_start_prev_cal_year
1416 ,p_assignment_id => p_assignment_id
1417 ,p_business_group_id => p_business_group_id);
1418 hr_utility.set_location('Previous year first non absence day: '||l_non_abs_day_prev_yr,50);
1419
1420 l_num_rolling_abs_days := get_abs_days(p_from_date => l_non_abs_day_prev_yr
1421 ,p_to_date => least(l_non_abs_day,p_pay_period_end_date)
1422 ,p_assignment_id => p_assignment_id
1423 ,p_business_group_id => p_business_group_id);
1424 hr_utility.set_location('Rolling year absence days: '||l_num_rolling_abs_days,60);
1425
1426 end if;
1427
1428
1429
1430 if l_num_rolling_abs_days > p_abs_days_limit then
1431
1432 l_days_over_limit := l_num_rolling_abs_days - p_abs_days_limit;
1433
1434 else
1435 l_days_over_limit := 0;
1436 end if;
1437
1438 hr_utility.set_location('Days over Limit: '||l_days_over_limit,200);
1439 return l_days_over_limit;
1440
1441 end get_days_over_pension_limit;
1442
1443
1444 ------------------------------------------------------------------------
1445 -- Function GET_CONTRIBUTION_INFO
1446 -- This will be used to obtain all the information about a given contribution element
1447 -- i.e. Contribution amount, base value, rate value, usage_id an contribution code.
1448 ------------------------------------------------------------------------
1449
1450 function get_contribution_info( p_assignment_id in number
1451 ,p_business_group_id in number
1452 ,p_date_earned in date
1453 ,p_tax_unit_id in number
1454 ,p_element_name IN varchar2
1455 ,p_usage_type IN varchar2
1456 ,p_base OUT NOCOPY number
1457 ,p_rate OUT NOCOPY number
1458 ,p_contribution_code IN OUT NOCOPY varchar2
1459 ,p_contribution_usage_id OUT NOCOPY number
1460 ,p_override_rate in number default null) return number
1461 is
1462 l_group_code varchar2(30);
1463 l_rate_value number;
1464 l_rate_type varchar2(80);
1465 l_contribution_usage_id number;
1466 l_contribution_code varchar2(30);
1467 l_contribution_value number;
1468 l_contribution_type varchar2(10);
1469 l_base_name pay_user_Column_instances_f.value%type;
1470 l_code_rate_id number;
1471 l_rate_category varchar2(1);
1472 l_element_name pay_fr_contribution_usages.element_name%type;
1473
1474 l_contribution_usage_row pay_fr_contribution_usages%rowtype := get_contribution_usage(
1475 p_process_type => g_process_type
1476 ,p_element_name => p_element_name
1477 ,p_usage_type => p_usage_type
1478 ,p_effective_date => p_date_earned
1479 ,p_business_group_id => p_business_group_id);
1480
1481
1482 l_proc varchar2(72) := g_package||'.GET_CONTRIBUTION_INFO';
1483 --
1484 begin
1485 --
1486 hr_utility.set_location('Entered '||l_proc,5);
1487 hr_utility.set_location('. Element: '||p_element_name,10);
1488
1489 l_group_code := l_contribution_usage_row.group_code;
1490 l_rate_type := l_contribution_usage_row.rate_type;
1491 l_contribution_code := l_contribution_usage_row.contribution_code;
1492 l_contribution_usage_id := l_contribution_usage_row.contribution_usage_id;
1493 l_contribution_type := l_contribution_usage_row.contribution_type;
1494 l_code_rate_id := l_contribution_usage_row.code_rate_id;
1495 l_rate_category := l_contribution_usage_row.rate_category;
1496 l_element_name := l_contribution_usage_row.element_name;
1497
1498 hr_utility.set_location('. group code:'||l_group_code,20);
1499 hr_utility.set_location('. rate type:'||l_rate_type,25);
1500
1501 /* Call function to substitute base code into contribution code */
1502 if l_contribution_code is not null then
1503 l_contribution_code := sub_contrib_code(
1504 p_contribution_type => l_contribution_type
1505 ,p_contribution_code => l_contribution_code);
1506 elsif p_contribution_code is not null then
1507 -- use template code passed in, with extra validation
1508 l_contribution_code := substitute_code(p_contribution_code);
1509 end if;
1510 hr_utility.set_location('. Contribution code:'||l_contribution_code,28);
1511
1512 l_base_name := get_base_name(p_business_group_id, l_group_code);
1513
1514 hr_utility.set_location('. Base_name ='||l_base_name,35);
1515
1516 p_base := get_base_value(l_base_name);
1517
1518 -- check to see if this rate has been used for this assignment in previous subruns
1519 -- for this assignment, establishment, process_type if so use that rate rather
1520 -- rederiving the rate for the current contribution.
1521
1522 l_rate_value := get_cached_rate(p_assignment_id,l_contribution_usage_id, p_tax_unit_id,
1523 l_contribution_code, l_code_rate_id, l_rate_type);
1524 if l_rate_value is null then -- { no cached rate
1525 hr_utility.set_location('Entered '||l_proc,40);
1526 if p_override_rate is null then
1527 l_rate_value := get_rate_value(p_business_group_id,l_rate_type);
1528 else
1529 l_rate_value := p_override_rate;
1530 hr_utility.set_location('Using Override Rate Value:'||p_override_rate,80);
1531 end if;
1532 maintain_rate_cache(l_contribution_usage_id, p_tax_unit_id, l_contribution_code,
1533 l_rate_value, l_code_rate_id, l_rate_type);
1534 end if;
1535
1536 /* Round all money values to 2 d.p. */
1537 l_contribution_value := round(p_base * (l_rate_value /100),2);
1538
1539
1540 p_rate := l_rate_value;
1541 p_contribution_usage_id := l_contribution_usage_id;
1542 p_contribution_code := l_contribution_code;
1543
1544 -- output this line to g_summary_deductions plsql table
1545 hr_utility.set_location('calling maintain_summary_deduction. code_rate_id:'||to_char(l_code_rate_id),90);
1546 maintain_summary_deduction(
1547 p_rate => l_rate_value
1548 , p_base_type => ltrim(l_base_name)
1549 , p_base => p_base
1550 , p_contribution_code => l_contribution_code
1551 , p_contribution_usage_id => l_contribution_usage_id
1552 , p_rate_type => ltrim(l_rate_type)
1553 , p_pay_value => l_contribution_value
1554 , p_rate_category => l_rate_category
1555 , p_user_column_instance_id => null
1556 , p_code_rate_id => l_code_rate_id
1557 , p_element_name => l_element_name
1558 );
1559
1560
1561 hr_utility.set_location('Leaving '||l_proc||'. Value='||l_contribution_value,100);
1562 --hr_utility.set_location('Leaving '||l_proc||'. base='||to_char(p_base),101);
1563 --hr_utility.set_location('Leaving '||l_proc||'. rate='||to_char(p_rate),102);
1564 --hr_utility.set_location('Leaving '||l_proc||'. contribution_code='||p_contribution_code,103);
1565 --hr_utility.set_location('Leaving '||l_proc||'. contribution_usage_id='||to_char(p_contribution_usage_id),104);
1566 --hr_utility.set_location('Leaving '||l_proc||'. asg_id='||to_char(p_assignment_id),105);
1567 --hr_utility.set_location('Leaving '||l_proc||'. bg_id='||to_char(p_business_group_id),106);
1568 --hr_utility.set_location('Leaving '||l_proc||'. date_earned='||to_char(p_date_earned),107);
1569 --hr_utility.set_location('Leaving '||l_proc||'. element='||p_element_name,108);
1570 --hr_utility.set_location('Leaving '||l_proc||'. usage_type='||p_usage_type,109);
1571
1572 return l_contribution_value;
1573 end get_contribution_info;
1574
1575 function get_contribution_info(p_assignment_id in number
1576 ,p_business_group_id in number
1577 ,p_date_earned in date
1578 ,p_tax_unit_id in number
1579 ,p_element_name IN varchar2
1580 ,p_usage_type IN varchar2
1581 ,p_base OUT NOCOPY number
1582 ,p_rate OUT NOCOPY number
1583 ,p_contribution_usage_id OUT NOCOPY number
1584 ,p_override_rate in number default null)
1585 return number
1586 is
1587 l_contribution_code pay_fr_contribution_usages.contribution_code%TYPE:=null;
1588 begin
1589 return get_contribution_info(p_assignment_id
1590 ,p_business_group_id
1591 ,p_date_earned
1592 ,p_tax_unit_id
1593 ,p_element_name
1594 ,p_usage_type
1595 ,p_base
1596 ,p_rate
1597 ,l_contribution_code
1598 ,p_contribution_usage_id
1599 ,p_override_rate);
1600 end get_contribution_info;
1601 ------------------------------------------------------------------------
1602 -- Function GET_WORK_ACCIDENT_CONTRIBUTION
1603 -- This will be used to obtain all the information about the given contribution element
1604 -- i.e. Contribution amount, base value, rate value, usage_id an contribution code.
1605 -- V115.16 Added parameters P_ASSIGNMENT_ID and P_RATE_TYPE so that previous run in period
1606 -- rate_type can be determined and returned back to formula.
1607 ------------------------------------------------------------------------
1608 function GET_WORK_ACCIDENT_CONTRIBUTION(P_ASSIGNMENT_ID in number
1609 ,P_BUSINESS_GROUP_ID in number
1610 ,P_DATE_EARNED in date
1611 ,P_TAX_UNIT_ID in number
1612 ,P_ELEMENT_NAME IN varchar2
1613 ,P_USAGE_TYPE IN varchar2
1614 ,P_RISK_CODE in Varchar2
1615 ,P_BASE out nocopy number
1616 ,P_RATE out nocopy number
1617 ,P_RATE_TYPE out nocopy varchar2
1618 ,P_CONTRIBUTION_CODE out nocopy varchar2
1619 ,P_CONTRIBUTION_USAGE_ID out nocopy number
1620 ,P_REDUCTION_PERCENT in number default null) return number
1621 is
1622
1623
1624 l_group_code varchar2(30);
1625 l_contribution_value number;
1626 l_rate_value number;
1627 l_contribution_usage_id number;
1628 l_contribution_code varchar2(30);
1629 l_contribution_type varchar2(10);
1630 l_base_name varchar2(30);
1631 l_risk_code varchar2(30);
1632 l_user_column_instance_id number;
1633 l_user_row_id number;
1634 l_rate_category varchar2(1);
1635 l_element_name pay_fr_contribution_usages.element_name%type;
1636
1637 l_contribution_usage_row pay_fr_contribution_usages%rowtype := get_contribution_usage(
1638 p_process_type => g_process_type
1639 ,p_element_name => p_element_name
1640 ,p_usage_type => p_usage_type
1641 ,p_effective_date => p_date_earned);
1642
1643 l_proc varchar2(72) := g_package||'.GET_WORK_ACCIDENT_CONTRIBUTION';
1644 --
1645 begin
1646 --
1647 hr_utility.set_location('Entered '||l_proc,5);
1648
1649 hr_utility.set_location('. element name='||p_element_name,10);
1650
1651 l_group_code := l_contribution_usage_row.group_code;
1652 l_contribution_code := l_contribution_usage_row.contribution_code;
1653 l_contribution_usage_id := l_contribution_usage_row.contribution_usage_id;
1654 l_contribution_type := l_contribution_usage_row.contribution_type;
1655 l_rate_category := l_contribution_usage_row.rate_category;
1656 l_risk_code := p_risk_code;
1657 l_element_name := l_contribution_usage_row.element_name;
1658
1659 /* Call function to substitute base code into contribution code */
1660 l_contribution_code := sub_contrib_code(p_contribution_type => l_contribution_type
1661 ,p_contribution_code => l_contribution_code);
1662
1663 hr_utility.set_location('Found element info.code:'||l_contribution_code,20);
1664 hr_utility.set_location('. group code:'||l_group_code,30);
1665
1666 -- check to see if this rate has been used for this assignment in previous subruns
1667 -- for this assignment, establishment, process_type if so use that rate rather
1668 -- rederiving the rate for the current contribution.
1669
1670 l_rate_value := get_cached_rate(p_assignment_id,l_contribution_usage_id, p_tax_unit_id,
1671 l_contribution_code, l_user_column_instance_id, l_risk_code);
1672 if l_rate_value is null then -- { no cached rate
1673 hr_utility.set_location('Entered '||l_proc,40);
1674
1675 begin
1676 l_rate_value := get_table_rate(p_bus_group_id => p_business_group_id
1677 ,p_table_name => 'FR_WORK_ACCIDENT_RATES'
1678 ,p_row_value => l_risk_code
1679 ,p_user_column_instance_id => l_user_column_instance_id
1680 ,p_user_row_id => l_user_row_id);
1681 exception
1682 when no_data_found then
1683 fnd_message.set_name('PAY','PAY_74933_SD_NO_WRK_ACC_RATE');
1684 fnd_message.set_token('RISK_CODE',l_risk_code);
1685 fnd_message.raise_error;
1686 end;
1687 maintain_rate_cache(l_contribution_usage_id, p_tax_unit_id, l_contribution_code,
1688 l_rate_value,l_user_column_instance_id, l_risk_code);
1689 end if; -- } no cached rate
1690 hr_utility.set_location('Rate value: '||l_rate_value,40);
1691 hr_utility.set_location('l_contribution_code: '||l_contribution_code,40);
1692
1693 l_base_name := get_base_name(p_business_group_id, l_group_code);
1694
1695 p_base := get_base_value(l_base_name);
1696
1697 /* Reduce the rate value by the reduction amount. Used for Part Time Rebate reduction */
1698 if p_reduction_percent is not null then
1699 l_rate_value := l_rate_value * ((100-p_reduction_percent) /100);
1700 hr_utility.set_location('reduced Rate value: '||l_rate_value,42);
1701 end if;
1702
1703 l_contribution_value := round(p_base * (l_rate_value /100),2);
1704
1705 p_rate := l_rate_value;
1706 p_rate_type := l_risk_code;
1707 p_contribution_usage_id := l_contribution_usage_id;
1708 p_contribution_code := l_contribution_code;
1709
1710 -- output this line to g_summary_deductions plsql table
1711 hr_utility.set_location('calling maintain_summary_deduction. user_col_instance_id:'||to_char(l_user_column_instance_id),90);
1712 maintain_summary_deduction(
1713 p_rate => l_rate_value
1714 , p_base_type => l_base_name
1715 , p_base => p_base
1716 , p_contribution_code => l_contribution_code
1717 , p_contribution_usage_id => l_contribution_usage_id
1718 , p_rate_type => p_rate_type
1719 , p_pay_value => l_contribution_value
1720 , p_rate_category => 'W'
1721 , p_user_column_instance_id => l_user_column_instance_id
1722 , p_code_rate_id => null
1723 , p_element_name => l_element_name
1724 );
1725
1726 hr_utility.set_location('Leaving '||l_proc||'. Value='||l_contribution_value,100);
1727
1728 return l_contribution_value;
1729
1730 end get_work_accident_contribution;
1731
1732
1733 ------------------------------------------------------------------------
1734 -- Function GET_TRANSPORT_TAX_CONTRIBUTION
1735 -- This will be used to obtain all the information about the given contribution element
1736 -- i.e. Contribution amount, base value, rate value, usage_id an contribution code.
1737 ------------------------------------------------------------------------
1738 function GET_TRANSPORT_TAX_CONTRIBUTION(P_ASSIGNMENT_ID in NUMBER
1739 ,P_BUSINESS_GROUP_ID in number
1740 ,p_date_earned in date
1741 ,P_TAX_UNIT_ID in number
1742 ,P_ELEMENT_NAME in varchar2
1743 ,P_USAGE_TYPE IN varchar2
1744 ,P_TRANSPORT_TAX_REGION in varchar2
1745 ,P_REDUCTION in number
1746 ,P_BASE out nocopy number
1747 ,P_RATE out nocopy number
1748 ,P_CONTRIBUTION_CODE out nocopy varchar2
1749 ,P_CONTRIBUTION_USAGE_ID out nocopy number) return number
1750 is
1751
1752 l_group_code varchar2(30);
1753 l_contribution_value number;
1754 l_rate_value number;
1755 l_contribution_usage_id number;
1756 l_contribution_code varchar2(30);
1757 l_contribution_type varchar2(10);
1758 l_base_name varchar2(30);
1759 l_user_column_instance_id number;
1760 l_user_row_id number;
1761 l_rate_category varchar2(1);
1762 l_element_name pay_element_types_f.element_name%type;
1763 l_transport_tax_region varchar2(80);
1764
1765 l_contribution_usage_row pay_fr_contribution_usages%rowtype := get_contribution_usage(
1766 p_process_type => g_process_type
1767 ,p_element_name => p_element_name
1768 ,p_usage_type => p_usage_type
1769 ,p_effective_date => p_date_earned);
1770
1771 l_proc varchar2(72) := g_package||'.GET_TRANSPORT_TAX_CONTRIBUTION';
1772 --
1773 begin
1774 --
1775 hr_utility.set_location('Entered '||l_proc,5);
1776
1777 l_group_code := l_contribution_usage_row.group_code;
1778 l_contribution_code := l_contribution_usage_row.contribution_code;
1779 l_contribution_usage_id := l_contribution_usage_row.contribution_usage_id;
1780 l_contribution_type := l_contribution_usage_row.contribution_type;
1781 l_rate_category := l_contribution_usage_row.rate_category;
1782 l_element_name := l_contribution_usage_row.element_name;
1783 l_transport_tax_region := p_transport_tax_region;
1784
1785 hr_utility.set_location('element info code:'||l_contribution_code,20);
1786 hr_utility.set_location('. group code:'||l_group_code,30);
1787 hr_utility.set_location('Transport Tax Region:'||p_transport_tax_region,40);
1788 hr_utility.set_location('Transport Tax Reduction:'||p_reduction,41);
1789
1790 /* Call function to substitute base code into contribution code */
1791 l_contribution_code := sub_contrib_code(p_contribution_type => l_contribution_type
1792 ,p_contribution_code => l_contribution_code);
1793
1794 -- check to see if this rate has been used for this assignment in previous subruns
1795 -- for this assignment, establishment if so use that rate rather
1796 -- rederiving the rate for the current contribution.
1797
1798 l_rate_value := get_cached_rate(p_assignment_id,l_contribution_usage_id, p_tax_unit_id,
1799 l_contribution_code, l_user_column_instance_id, l_transport_tax_region);
1800 if l_rate_value is null then -- { no cached rate
1801 hr_utility.set_location('Entered '||l_proc,50);
1802
1803
1804 begin
1805 l_rate_value := get_table_rate(p_bus_group_id => p_business_group_id
1806 ,p_table_name => 'FR_TRANSPORT_TAX_RATES'
1807 ,p_row_value => p_transport_tax_region
1808 ,p_user_row_id => l_user_row_id
1809 ,p_user_column_instance_id => l_user_column_instance_id);
1810 exception
1811 when no_data_found then
1812 fnd_message.set_name('PAY','PAY_74934_SD_NO_TRNS_TAX_RATE');
1813 fnd_message.set_token('TRNS_TAX_REGION',p_transport_tax_region);
1814 fnd_message.raise_error;
1815 end;
1816 maintain_rate_cache(l_contribution_usage_id, p_tax_unit_id, l_contribution_code,
1817 l_rate_value,l_user_column_instance_id, l_transport_tax_region);
1818 end if; -- } no cached rate
1819
1820 hr_utility.set_location('Rate value: '||l_rate_value,60);
1821
1822 if p_reduction <> 0 then
1823 l_rate_value := l_rate_value * ((100-p_reduction) /100);
1824 hr_utility.set_location('reduced Rate value: '||l_rate_value,65);
1825 end if;
1826
1827 l_base_name := get_base_name(p_business_group_id, l_group_code);
1828
1829 p_base := get_base_value(l_base_name);
1830
1831 l_contribution_value := round(p_base * (l_rate_value /100),2);
1832
1833 p_rate := l_rate_value;
1834 p_contribution_usage_id := l_contribution_usage_id;
1835 p_contribution_code := l_contribution_code;
1836
1837 hr_utility.set_location('Leaving '||l_proc||'. Value='||l_contribution_value,100);
1838
1839 -- output this line to g_summary_deductions plsql table
1840 hr_utility.set_location('calling maintain_summary_deduction. user_col_instance_id:'||to_char(l_user_column_instance_id),90);
1841 maintain_summary_deduction(
1842 p_rate => l_rate_value
1843 , p_base_type => l_base_name
1844 , p_base => p_base
1845 , p_contribution_code => l_contribution_code
1846 , p_contribution_usage_id => l_contribution_usage_id
1847 , p_rate_type => p_transport_tax_region
1848 , p_pay_value => l_contribution_value
1849 , p_rate_category => 'T'
1850 , p_user_column_instance_id => l_user_column_instance_id
1851 , p_code_rate_id => null
1852 , p_element_name => l_element_name
1853 );
1854
1855 return l_contribution_value;
1856
1857 end get_transport_tax_contribution;
1858
1859 ------------------------------------------------------------------------
1860 -- Function GET_FIXED_VALUE_CONTRIBUTION
1861 -- Some contributions are paid at a fixed value and therefore do not have a rate
1862 -- or base. Only the value and contribution usage id and code are obtained
1863 ------------------------------------------------------------------------
1864 function get_fixed_value_contribution(P_BUSINESS_GROUP_ID in number
1865 ,p_date_earned in date
1866 ,p_element_name IN varchar2
1867 ,p_usage_type IN varchar2
1868 ,p_contribution_code OUT NOCOPY varchar2
1869 ,p_contribution_usage_id OUT NOCOPY number) return number
1870 is
1871 l_rate_value number;
1872 l_rate_type varchar2(80);
1873 l_contribution_usage_id number;
1874 l_contribution_code varchar2(30);
1875 l_contribution_type varchar2(10);
1876
1877 l_contribution_usage_row pay_fr_contribution_usages%rowtype := get_contribution_usage(
1878 p_business_group_id => p_business_group_id
1879 ,p_process_type => g_process_type
1880 ,p_element_name => p_element_name
1881 ,p_usage_type => p_usage_type
1882 ,p_effective_date => p_date_earned);
1883
1884 l_proc varchar2(72) := g_package||'.get_fixed_value_contribution';
1885 --
1886 begin
1887 --
1888 hr_utility.set_location('Entered '||l_proc,5);
1889
1890 l_rate_type := l_contribution_usage_row.rate_type;
1891 l_contribution_code := l_contribution_usage_row.contribution_code;
1892 l_contribution_usage_id := l_contribution_usage_row.contribution_usage_id;
1893 l_contribution_type := l_contribution_usage_row.contribution_type;
1894
1895 /* Call function to substitute base code into contribution code */
1896 l_contribution_code := sub_contrib_code(p_contribution_type => l_contribution_type
1897 ,p_contribution_code => l_contribution_code);
1898
1899 hr_utility.set_location('Found element info.code:'||l_contribution_code,15);
1900 hr_utility.set_location('. rate type:'||l_rate_type,25);
1901
1902 l_rate_value := get_rate_value(p_business_group_id,l_rate_type);
1903
1904 p_contribution_usage_id := l_contribution_usage_id;
1905 p_contribution_code := l_contribution_code;
1906
1907 hr_utility.set_location('Leaving '||l_proc||'. Value='||l_rate_value,100);
1908
1909 return l_rate_value;
1910 end get_fixed_value_contribution;
1911
1912 ------------------------------------------------------------------------
1913 -- Function GET_REDUCED_CONTRIBUTION
1914 -- This function is used to obtain contribution information for those
1915 -- contributions that can be reduced by a given reduction percentage
1916 ------------------------------------------------------------------------
1917 function get_reduced_contribution(P_BUSINESS_GROUP_ID in number
1918 ,p_date_earned in date
1919 ,P_ELEMENT_NAME IN varchar2
1920 ,P_USAGE_TYPE IN varchar2
1921 ,p_reduction in number
1922 ,P_BASE out nocopy number
1923 ,P_RATE out nocopy number
1924 ,P_CONTRIBUTION_CODE in out nocopy varchar2
1925 ,P_CONTRIBUTION_USAGE_ID out nocopy number) return number
1926 is
1927 l_group_code varchar2(30);
1928 l_contribution_value number;
1929 l_rate_value number;
1930 l_rate_type varchar2(80);
1931 l_contribution_code varchar2(30);
1932 l_contribution_usage_id number;
1933 l_contribution_type varchar2(10);
1934 l_base_name varchar2(30);
1935
1936 l_contribution_usage_row pay_fr_contribution_usages%rowtype := get_contribution_usage(
1937 p_process_type => g_process_type
1938 ,p_element_name => p_element_name
1939 ,p_usage_type => p_usage_type
1940 ,p_effective_date => p_date_earned);
1941
1942 l_proc varchar2(72) := g_package||'.get_reduced_contribution';
1943 --
1944 begin
1945 --
1946 hr_utility.set_location('Entered '||l_proc,5);
1947
1948 hr_utility.set_location('. element:'||p_element_name,10);
1949
1950 l_group_code := l_contribution_usage_row.group_code;
1951 l_rate_type := l_contribution_usage_row.rate_type;
1952 l_contribution_code := l_contribution_usage_row.contribution_code;
1953 l_contribution_usage_id := l_contribution_usage_row.contribution_usage_id;
1954 l_contribution_type := l_contribution_usage_row.contribution_type;
1955
1956 /* Call function to substitute base code into contribution code */
1957 if l_contribution_code is not null then
1958 l_contribution_code := sub_contrib_code(
1959 p_contribution_type => l_contribution_type
1960 ,p_contribution_code => l_contribution_code);
1961 elsif p_contribution_code is not null then
1962 -- use template code passed in, with extra validation
1963 l_contribution_code := substitute_code(p_contribution_code);
1964 end if;
1965
1966 hr_utility.set_location('Found element info.code:'||l_contribution_code,20);
1967 hr_utility.set_location('. Rate type:'||l_rate_type,25);
1968 hr_utility.set_location('. Group code:'||l_group_code,30);
1969
1970
1971 l_base_name := get_base_name(p_business_group_id, l_group_code);
1972
1973 p_base := get_base_value(l_base_name);
1974
1975 l_rate_value := get_rate_value(p_business_group_id,l_rate_type);
1976
1977 /* Reduce the rate value by the reduction amount i.e. Post the reduced rate value */
1978 if p_reduction <> 0 then
1979 l_rate_value := l_rate_value * ((100-p_reduction) /100);
1980 hr_utility.set_location('reduced Rate value: '||l_rate_value,45);
1981 end if;
1982
1983 l_contribution_value := round(p_base * (l_rate_value /100),2);
1984
1985 p_rate := l_rate_value;
1986 p_contribution_usage_id := l_contribution_usage_id;
1987 p_contribution_code := l_contribution_code;
1988
1989 hr_utility.set_location('Leaving '||l_proc||'. Value='||l_contribution_value,100);
1990
1991 return l_contribution_value;
1992
1993 end get_reduced_contribution;
1994
1995 function get_reduced_contribution(P_BUSINESS_GROUP_ID in number
1996 ,p_date_earned in date
1997 ,P_ELEMENT_NAME IN varchar2
1998 ,P_USAGE_TYPE IN varchar2
1999 ,p_reduction in number
2000 ,P_BASE out nocopy number
2001 ,P_RATE out nocopy number
2002 ,P_CONTRIBUTION_USAGE_ID out nocopy number)
2003 return number
2004 is
2005 l_contribution_code pay_fr_contribution_usages.contribution_code%TYPE:=null;
2006 begin
2007 return get_reduced_contribution(p_business_group_id
2008 ,p_date_earned
2009 ,p_element_name
2010 ,p_usage_type
2011 ,p_reduction
2012 ,p_base
2013 ,p_rate
2014 ,l_contribution_code
2015 ,p_contribution_usage_id);
2016 end get_reduced_contribution;
2017 ------------------------------------------------------------------------
2018 -- Function CONVERT_HOURS
2019 ------------------------------------------------------------------------
2020 function convert_hours(p_effective_date in date
2021 ,p_business_group_id in number
2022 ,p_assignment_id in number
2023 ,p_hours in number
2024 ,p_from_freq_code in varchar2
2025 ,p_to_freq_code in varchar2) return number
2026 IS
2027 --
2028 l_hours_in_days number;
2029 l_hours_in_weeks number;
2030 l_hours_in_months number;
2031 l_hours_in_years number;
2032 l_hours_in_day number := 7;
2033 l_days_in_week number := 5;
2034 l_months_in_year number := 12;
2035 l_weeks_in_year number := 52;
2036 l_weeks_in_month number := l_weeks_in_year/l_months_in_year;
2037 l_inputs ff_exec.inputs_t;
2038 l_outputs ff_exec.outputs_t;
2039 l_formula_id number;
2040 l_start_date date;
2041 l_hourly_value number;
2042 --
2043 l_proc varchar2(72) := g_package||'.convert_hours';
2044 --
2045 --
2046 begin
2047 --
2048 hr_utility.set_location('Entered '||l_proc,8);
2049 --
2050 /* Check if a user formula exists - if it does then use values from that, otherwise use defaults
2051 set above */
2052 --
2053 /* This function call returns -1 if the formula was not found */
2054 l_formula_id := get_formula_info
2055 (p_formula_name => 'USER_CONVERT_HOURS'
2056 ,p_effective_date => p_effective_date
2057 ,p_business_group_id => p_business_group_id
2058 ,p_effective_start_date => l_start_date);
2059
2060 If l_formula_id <> -1 then
2061 -- Initialise the formula
2062 ff_exec.init_formula (l_formula_id,
2063 l_start_date,
2064 l_inputs,
2065 l_outputs);
2066 --
2067 -- populate input parameters
2068 for i in l_inputs.first..l_inputs.last loop
2069 if l_inputs(i).name = 'HOURS' then
2070 l_inputs(i).value := p_hours;
2071 elsif l_inputs(i).name = 'FROM_FREQ_CODE' then
2072 l_inputs(i).value := p_from_freq_code;
2073 elsif l_inputs(i).name = 'TO_FREQ_CODE' then
2074 l_inputs(i).value := p_to_freq_code;
2075 elsif l_inputs(i).name = 'DATE_EARNED' then
2076 l_inputs(i).value := fnd_date.date_to_canonical(p_effective_date);
2077 elsif l_inputs(i).name = 'ASSIGNMENT_ID' then
2078 l_inputs(i).value := p_assignment_id;
2079 else
2080 hr_utility.set_location('ERROR value = '||l_inputs(i).name ,7);
2081 end if;
2082 end loop;
2083 --
2084 hr_utility.set_location(' Prior to execute the formula',8);
2085 ff_exec.run_formula (l_inputs
2086 ,l_outputs);
2087 --
2088 hr_utility.set_location(' End run formula',9);
2089 --
2090 for l_out_cnt in l_outputs.first..l_outputs.last loop
2091 if l_outputs(l_out_cnt).name = 'P_HOURLY_VALUE' then
2092 l_hourly_value := l_outputs(l_out_cnt).value;
2093 end if;
2094 end loop;
2095 --
2096 return l_hourly_value;
2097 --
2098 Else
2099 --
2100 if p_from_freq_code = 'D' then
2101 l_hours_in_days := p_hours;
2102 l_hours_in_weeks := p_hours*l_days_in_week;
2103 l_hours_in_months := l_hours_in_weeks*l_weeks_in_month;
2104 l_hours_in_years := l_hours_in_months*l_months_in_year;
2105 elsif p_from_freq_code = 'W' then
2106 l_hours_in_weeks := p_hours;
2107 l_hours_in_months := l_hours_in_weeks*l_weeks_in_month;
2108 l_hours_in_years := l_hours_in_months*l_months_in_year;
2109 l_hours_in_days := l_hours_in_weeks/l_days_in_week;
2110 elsif p_from_freq_code = 'M' then
2111 l_hours_in_months := p_hours;
2112 l_hours_in_years := l_hours_in_months*l_months_in_year;
2113 l_hours_in_weeks := l_hours_in_months/l_weeks_in_month;
2114 l_hours_in_days := l_hours_in_weeks/l_days_in_week;
2115 elsif p_from_freq_code = 'Y' then
2116 l_hours_in_years := p_hours;
2117 l_hours_in_months := l_hours_in_years*l_months_in_year;
2118 l_hours_in_weeks := l_hours_in_months/l_weeks_in_month;
2119 l_hours_in_days := l_hours_in_weeks/l_days_in_week;
2120 else
2121 fnd_message.set_name('PAY','PAY_74922_SD_BAD_CONV_HRS_FREQ');
2122 fnd_message.set_token('FREQ',p_from_freq_code);
2123 fnd_message.raise_error;
2124 hr_utility.set_location('Error - p_from_freq_code not recognised',10);
2125 end if;
2126 --
2127 End If;
2128 --
2129 hr_utility.set_location('Leaving '||l_proc,100);
2130 --
2131 if p_to_freq_code = 'D' then
2132 return l_hours_in_day;
2133 elsif p_to_freq_code = 'W' then
2134 return l_hours_in_weeks;
2135 elsif p_to_freq_code = 'M' then
2136 return l_hours_in_months;
2137 elsif p_to_freq_code = 'Y' then
2138 return l_hours_in_years;
2139 end if;
2140 --
2141 end convert_hours;
2142 --
2143 ------------------------------------------------------------------------
2144 -- Function GET_MONTHLY_HOURS
2145 -- This function determines returns global g_monthly_hours
2146 -- which has been set in function initialize_payroll
2147 ------------------------------------------------------------------------
2148 function get_monthly_hours return number is
2149 begin
2150 --
2151 return pay_fr_general.g_monthly_hours;
2152 --
2153 end get_monthly_hours;
2154 --
2155 ------------------------------------------------------------------------
2156 -- Function GET_PAY_RATE
2157 -- This function determines whether a user defined formula passed in as a
2158 -- parameter exists, if it does it executes it to retrieve the
2159 -- hourly rate.
2160 -- Otherwise it determines the hourly rate from the employees salary basis
2161 -- record (using the normal working hours to determine an hourly rate).
2162 ------------------------------------------------------------------------
2163 function get_pay_rate(p_assignment_id in number
2164 ,p_business_group_id in number
2165 ,p_effective_date in date
2166 ,p_formula varchar2 default 'FR_USER_HOURLY_RATE'
2167 ,p_parameter_list varchar2 default null) return number is
2168 l_hourly_rate number;
2169 l_pay_rate number;
2170 l_pay_basis varchar2(30);
2171 l_normal_hours number;
2172 l_frequency varchar2(30);
2173 --
2174 l_list varchar2(2000);
2175 l_param varchar2(2000);
2176 l_param_end number;
2177 j number;
2178 --
2179 TYPE param_rec_type is RECORD
2180 (name varchar2(200)
2181 ,value varchar2(200));
2182 --
2183 TYPE param_tab_type is TABLE of param_rec_type index by BINARY_INTEGER;
2184 --
2185 param_tab param_tab_type;
2186 --
2187
2188 --
2189 l_inputs ff_exec.inputs_t;
2190 l_outputs ff_exec.outputs_t;
2191 l_formula_id number;
2192 l_start_date date;
2193 --
2194 l_proc varchar2(72) := g_package||'.get_pay_rate';
2195 --
2196
2197 cursor c_pay_rate is
2198 select ee.screen_entry_value
2199 , b.pay_basis
2200 -- commented as part of time analysis changes
2201 --, a.normal_hours
2202 --, a.frequency
2203 , decode(pcf.ctr_information12, 'HOUR', fnd_number.canonical_to_number(pcf.ctr_information11), a.normal_hours) normal_hours
2204 , decode(pcf.ctr_information12, 'HOUR', pcf.ctr_information13, a.frequency) frequency
2205 from pay_element_entry_values_f ee
2206 , pay_element_entries_f e
2207 , per_all_assignments_f a
2208 , per_pay_bases b
2209 --
2210 , per_contracts_f pcf
2211 --
2212 where a.assignment_id = p_assignment_id
2213 and a.assignment_id = e.assignment_id
2214 and e.element_entry_id = ee.element_entry_id
2215 and ee.input_value_id = b.input_value_id
2216 and a.pay_basis_id = b.pay_basis_id
2217 and b.pay_basis in ('HOURLY','MONTHLY','ANNUAL')
2218 --
2219 and pcf.contract_id = a.contract_id
2220 --
2221 and p_effective_date between a.effective_start_date and a.effective_end_date
2222 and p_effective_date between e.effective_start_date and e.effective_end_date
2223 and p_effective_date between ee.effective_start_date and ee.effective_end_date
2224 --
2225 and p_effective_date between pcf.effective_start_date and pcf.effective_end_date;
2226 --
2227 begin
2228 hr_utility.set_location(l_proc,10);
2229 --
2230 /* If there is a formula in the business group named FR_USER_HOURLY_RATE then this will be used */
2231 --
2232 /* This function call returns -1 if the formula was not found */
2233 l_formula_id := get_formula_info
2234 (p_formula_name => p_formula
2235 ,p_effective_date => p_effective_date
2236 ,p_business_group_id => p_business_group_id
2237 ,p_effective_start_date => l_start_date);
2238
2239 If l_formula_id <> -1 then
2240 hr_utility.set_location(l_proc,20);
2241 -- IF parameter list is not null Extract parameters from parameter list
2242 -- Parameters are in the format P1=P1_VALUE,P2=P2_VALUE
2243 --
2244 if p_parameter_list is not null then
2245 j := 1;
2246 l_list := p_parameter_list;
2247 while true loop -- loop while list is not null
2248 if length(l_list) > 0 then
2249 l_param_end := instr(l_list,',');
2250 if l_param_end > 0 then -- this is not the last parameter
2251 l_param := substr(l_list,1,l_param_end);
2252 l_list := substr(l_list,l_param_end+1,length(l_list));
2253 else -- last parameter
2254 l_param := l_list||',';
2255 l_list := null;
2256 end if;
2257 l_param := substr(l_param,1,length(l_param)-1);
2258 param_tab(j).name := substr(l_param,1,instr(l_param,'=')-1);
2259 param_tab(j).value :=
2260 substr(l_param,instr(l_param,'=')+1,length(l_param));
2261 hr_utility.trace(p_formula||' '|| param_tab(j).name||' '||param_tab(j).value);
2262 else
2263 exit;
2264 end if;
2265 j := j + 1;
2266 end loop;
2267 end if;
2268 -- Initialise the formula
2269 ff_exec.init_formula (l_formula_id,
2270 l_start_date,
2271 l_inputs,
2272 l_outputs);
2273 --
2274 -- populate input parameters
2275 if (l_inputs.first is not null) and (l_inputs.last is not null) then
2276 for i in l_inputs.first..l_inputs.last loop
2277 if l_inputs(i).name = 'ASSIGNMENT_ID' then
2278 l_inputs(i).value := p_assignment_id;
2279 elsif l_inputs(i).name = 'DATE_EARNED' then
2280 l_inputs(i).value := fnd_date.date_to_canonical(p_effective_date);
2281 elsif l_inputs(i).name = 'BUSINESS_GROUP_ID' then
2282 l_inputs(i).value := p_business_group_id;
2283 end if;
2284 if j > 1 then
2285 for x in 1..j-1 loop
2286 if l_inputs(i).name = param_tab(x).name then
2287 l_inputs(i).value := param_tab(x).value;
2288 exit;
2289 end if;
2290 end loop;
2291 end if;
2292 end loop;
2293 end if;
2294 --
2295 hr_utility.set_location(' Prior to execute the formula',8);
2296 hr_utility.set_location(l_proc,30);
2297 ff_exec.run_formula (l_inputs
2298 ,l_outputs);
2299 --
2300 hr_utility.set_location(l_proc,40);
2301 hr_utility.set_location(' End run formula',9);
2302 --
2303 for l_out_cnt in l_outputs.first..l_outputs.last loop
2304 if l_outputs(l_out_cnt).name = 'HOURLY_PAY_RATE' then
2305 l_hourly_rate := l_outputs(l_out_cnt).value;
2306 end if;
2307 end loop;
2308 --
2309 else
2310 /* User formula not found so try to derive the hourly rate from Salary Admin */
2311 --
2312 hr_utility.set_location(l_proc,50);
2313 --
2314 open c_pay_rate;
2315 fetch c_pay_rate into l_pay_rate,
2316 l_pay_basis,
2317 l_normal_hours,
2318 l_frequency;
2319 if c_pay_rate%found then
2320 close c_pay_rate;
2321 if l_pay_basis = 'HOURLY' then
2322 --
2323 hr_utility.set_location(l_proc,60);
2324 --
2325 l_hourly_rate := l_pay_rate;
2326 --
2327 elsif l_pay_basis = 'MONTHLY' then
2328 --
2329 hr_utility.set_location(l_proc,70);
2330 --
2331 /* Convert the normal working hours into a monthly figure so that
2332 it can be compared with the Monthly salary basis */
2333 --
2334 if l_normal_hours is not null and l_frequency is not null then
2335 l_normal_hours := pay_fr_general.convert_hours(p_effective_date
2336 ,p_business_group_id
2337 ,p_assignment_id
2338 ,l_normal_hours
2339 ,l_frequency
2340 ,'M');
2341 l_hourly_rate := l_pay_rate / l_normal_hours;
2342 end if;
2343 --
2344 elsif l_pay_basis = 'ANNUAL' then
2345 --
2346 hr_utility.set_location(l_proc,80);
2347 --
2348 /* Convert the normal working hours into an annual figure so that
2349 it can be compared with the Annual salary basis */
2350 --
2351 if l_normal_hours is not null and l_frequency is not null then
2352 l_normal_hours := pay_fr_general.convert_hours(p_effective_date
2353 ,p_business_group_id
2354 ,p_assignment_id
2355 ,l_normal_hours
2356 ,l_frequency
2357 ,'Y');
2358 l_hourly_rate := l_pay_rate / l_normal_hours;
2359 end if;
2360 end if;
2361 --
2362 else -- c_pay_rate%notfound
2363 hr_utility.set_location(l_proc,90);
2364 close c_pay_rate;
2365 end if;
2366 end if; -- Formula_ID <> -1
2367 --
2368 /* If the hourly rate is not null then return it otherwise raise an error */
2369 --
2370 if l_hourly_rate is not null then
2371 return l_hourly_rate;
2372 else
2373 hr_utility.set_location(l_proc,100);
2374 fnd_message.set_name('PAY','PAY_HOURLY_RATE_NOT_DERIVED');
2375 fnd_message.raise_error;
2376 end if;
2377 end get_pay_rate;
2378 --
2379 ------------------------------------------------------------------------
2380 -- Function GET_PREV_START_END
2381 -- This function determines the start and end date of the previous
2382 -- period - these dates are assigned to global variable. They should only
2383 -- be set whenever the payroll action id has changed.
2384 ------------------------------------------------------------------------
2385 function get_prev_start_end (p_payroll_action_id in number
2386 ,p_start_date in out nocopy date
2387 ,p_end_date in out nocopy date) return number is
2388 --
2389 cursor csr_get_time_period is
2390 select ptp2.start_date,
2391 ptp2.end_date
2392 from per_time_periods ptp,
2393 pay_payroll_actions ppa,
2394 per_time_periods ptp2
2395 where ppa.date_earned BETWEEN ptp.START_DATE and ptp.END_DATE
2396 and ppa.payroll_action_id = p_payroll_action_id
2397 and ptp.payroll_id = ppa.payroll_id
2398 and ptp2.end_date = ptp.start_date - 1
2399 and ptp2.payroll_id = ppa.payroll_id;
2400 --
2401 l_proc varchar2(72) := g_package||'.get_prev_start_end';
2402 --
2403 begin
2404 --
2405 hr_utility.set_location('Entering '||l_proc,10);
2406 --
2407 if p_payroll_action_id <> nvl(pay_fr_general.g_payroll_action_id,-1) then
2408 --
2409 open csr_get_time_period;
2410 fetch csr_get_time_period into p_start_date,p_end_date;
2411 if csr_get_time_period%found then
2412 pay_fr_general.g_payroll_action_id := p_payroll_action_id;
2413 close csr_get_time_period;
2414 return 0;
2415 else
2416 close csr_get_time_period;
2417 return 1;
2418 end if;
2419 --
2420 else
2421 return 0;
2422 end if;
2423 --
2424 end get_prev_start_end;
2425 --
2426
2427 ------------------------------------------------------------------------
2428 -- Function SUBSTITUTE_CODE
2429 -- A function that can be called from fast formula. It will call
2430 -- sub_contrib_code if a valid contribution code is passed into this function
2431 ------------------------------------------------------------------------
2432 function substitute_code(p_contribution_code in varchar2) return varchar2
2433 IS
2434 --
2435 l_code_prefix varchar2(1);
2436 l_code_error_mesg varchar2(30) := 'VALID';
2437 l_contrib_code varchar2(30);
2438 l_proc varchar2(72) := g_package||'.substitute_code';
2439 --
2440 begin
2441 --
2442 hr_utility.set_location('Entered '||l_proc,5);
2443 --
2444 l_code_prefix := SUBSTR(p_contribution_code,1,1);
2445
2446 if l_code_prefix = '3' then
2447 if substr(p_contribution_code,2,4) = 'XXXX' then
2448 l_contrib_code := sub_contrib_code(p_contribution_type => 'AGIRC'
2449 ,p_contribution_code => p_contribution_code);
2450 else
2451 l_code_error_mesg := 'PAY_74906_CNU_BAD_AGIRC';
2452 end if;
2453 elsif l_code_prefix = '4' then
2454 if substr(p_contribution_code,2,4) = 'XXXX' then
2455 l_contrib_code := sub_contrib_code(p_contribution_type => 'ARRCO'
2456 ,p_contribution_code => p_contribution_code);
2457 else
2458 l_code_error_mesg := 'PAY_74907_CNU_BAD_ARRCO';
2459 end if;
2460 elsif l_code_prefix = '1' then
2461 if substr(p_contribution_code,2,2) = 'XX' then
2462 l_contrib_code := sub_contrib_code(p_contribution_type => 'URSSAF'
2463 ,p_contribution_code => p_contribution_code);
2464 else
2465 l_code_error_mesg := 'PAY_74904_CNU_BAD_URSSAF';
2466 end if;
2467 elsif l_code_prefix = '2' then
2468 if substr(p_contribution_code,2,1) = 'X' then
2469 l_contrib_code := sub_contrib_code(p_contribution_type => 'ASSEDIC'
2470 ,p_contribution_code => p_contribution_code);
2471 else
2472 l_code_error_mesg := 'PAY_74905_CNU_BAD_ASSEDIC';
2473 end if;
2474 else /* code is other type and cannot be substituted or is null */
2475 l_contrib_code := p_contribution_code;
2476 end if;
2477
2478
2479 if l_code_error_mesg = 'VALID' then
2480 return l_contrib_code;
2481 else
2482 hr_utility.set_location('Error:'||l_code_error_mesg,100);
2483 fnd_message.set_name('PAY',l_code_error_mesg);
2484 fnd_message.raise_error;
2485 end if;
2486
2487 end substitute_code;
2488 --
2489 ------------------------------------------------------------------------
2490 -- Function FORMAT_NAME
2491 -- A function that can be called from the Absence Report to format name.
2492 ------------------------------------------------------------------------
2493 Function format_name(p_employee_id IN NUMBER) RETURN VARCHAR2
2494 IS
2495 l_formatted_name varchar2(200);
2496 l_last_name varchar2(100);
2497 l_first_name varchar2(100);
2498 l_maiden_name varchar2(100);
2499 l_sex varchar2(3);
2500 l_marital_status varchar2(3);
2501
2502 -- Cursor to fetch Sex and Marital Status of Employee
2503 CURSOR csr_person_info IS
2504 SELECT first_name,last_name,per_information1,sex,marital_status
2505 FROM per_all_people_f
2506 WHERE person_id = p_employee_id
2507 AND per_information_category ='FR';
2508 --
2509 BEGIN
2510
2511 OPEN csr_person_info;
2512 FETCH csr_person_info INTO l_first_name,l_last_name,l_maiden_name,l_sex,l_marital_status ;
2513 CLOSE csr_person_info;
2514
2515 l_formatted_name := l_last_name||' '||l_first_name;
2516
2517 IF l_sex = 'F' THEN
2518
2519 IF l_marital_status = 'M' THEN -- Married
2520 l_formatted_name := l_maiden_name||' '||hr_general.decode_lookup('NAME_TRANSLATIONS','FR_EPOUSE')||' '||l_last_name||' '||l_first_name;
2521
2522 ELSIF l_marital_status = 'W' THEN -- Widowed
2523 l_formatted_name := l_maiden_name ||' '||hr_general.decode_lookup('NAME_TRANSLATIONS','FR_VEUVE')||' '||l_last_name||' '||l_first_name;
2524
2525 ELSE
2526
2527 IF l_last_name = l_maiden_name THEN
2528 l_formatted_name := l_last_name||' '||l_first_name;
2529 ELSE
2530 l_formatted_name := l_maiden_name||' '||hr_general.decode_lookup('NAME_TRANSLATIONS','FR_NOM_D''USAGE') ||' '||l_last_name||' '||l_first_name;
2531 END IF;
2532
2533 END IF;
2534 END IF;
2535 RETURN l_formatted_name;
2536 END format_name;
2537
2538 -----------------------------------------------------------------------
2539 -- Function FR_ROLLING_BALANCE
2540 -- function to return rolling balance values
2541 ----------------------------------------------------------------------
2542 Function fr_rolling_balance (p_assignment_id in number,
2543 p_balance_name in varchar2,
2544 p_balance_start_date in date,
2545 p_balance_end_date in date) return number
2546 IS
2547 Cursor csr_def_bal_id IS
2548 SELECT pdb.defined_balance_id
2549 FROM pay_balance_types pbt,
2550 pay_balance_dimensions pbd,
2551 pay_defined_balances pdb
2552 WHERE pdb.balance_type_id = pbt.balance_type_id
2553 AND pdb.balance_dimension_id = pbd.balance_dimension_id
2554 AND pbt.balance_name = p_balance_name
2555 AND pbd.database_item_suffix = '_ASG_PTD'
2556 AND pdb.legislation_code = 'FR';
2557 --
2558 l_defined_balance_id number;
2559 l_start number := to_char(p_balance_start_date,'J');
2560 l_end number := to_char(p_balance_end_date,'J');
2561 i number := 0;
2562 l_value number := 0;
2563 l_proc varchar2(72) := g_package||'fr_rolling_balance';
2564
2565 BEGIN
2566 hr_utility.set_location('Entering:'|| l_proc,10);
2567 open csr_def_bal_id;
2568 fetch csr_def_bal_id into l_defined_balance_id;
2569 close csr_def_bal_id;
2570 --
2571 while add_months(p_balance_start_date,i) <= p_balance_end_date loop
2572 BEGIN
2573 l_value := l_value +
2574 pay_balance_pkg.get_value
2575 (l_defined_balance_id
2576 ,p_assignment_id
2577 ,add_months(p_balance_start_date,i+1)-1);
2578
2579 EXCEPTION
2580 WHEN NO_DATA_FOUND THEN --Bug #2651568
2581 l_value := 0;
2582 END;
2583 i := i + 1;
2584 hr_utility.set_location(' BAL VAL='||l_value, 60);
2585 end loop;
2586 hr_utility.set_location(' FINAL BAL VAL='||l_value, 60);
2587 hr_utility.set_location(' Leaving:'||l_proc, 70);
2588 return l_value;
2589
2590 END;
2591 ---------------------------------------------------------------------
2592 FUNCTION GET_SUMMARY_DEDUCTION
2593 (
2594 p_rate OUT NOCOPY NUMBER
2595 , p_base OUT NOCOPY NUMBER
2596 , p_contribution_code OUT NOCOPY VARCHAR2
2597 , p_contribution_usage_id OUT NOCOPY NUMBER
2598 , p_pay_value OUT NOCOPY NUMBER
2599 ) return varchar2 is
2600 -- function called from formula to fetch the next row from t_summary_deductions table
2601 -- to return as results
2602 l_proc varchar2(72) := g_package||'.get_summary_deduction';
2603 begin
2604 hr_utility.set_location('Entering:'|| l_proc, 10);
2605 begin
2606 if g_summary_idx = 0 then p_pay_value := 0;
2607 else p_pay_value := nvl(g_summary_deductions(g_summary_idx).pay_value,0);
2608 end if;
2609 g_summary_idx := g_summary_idx + 1;
2610 p_rate := g_summary_deductions(g_summary_idx).rate;
2611 p_base := g_summary_deductions(g_summary_idx).base;
2612 p_contribution_code := g_summary_deductions(g_summary_idx).contribution_code;
2613 p_contribution_usage_id := g_summary_deductions(g_summary_idx).contribution_usage_id;
2614 exception when no_data_found then
2615 hr_utility.set_location('No data found '|| l_proc, 20);
2616 p_rate := 0 ;
2617 p_base := 0;
2618 p_contribution_code := ' ';
2619 p_contribution_usage_id := 0;
2620 end;
2621
2622 hr_utility.trace('GET_SUMMARY_DEDUCTION.g_summary_idx:'||to_char(g_summary_idx));
2623 hr_utility.trace('GET_SUMMARY_DEDUCTION.g_summary_deductions.last:'||to_char(g_summary_deductions.last));
2624 hr_utility.trace('GET_SUMMARY_DEDUCTION.p_rate:'||to_char(p_rate));
2625 hr_utility.trace('GET_SUMMARY_DEDUCTION.p_base:'||to_char(p_base));
2626 hr_utility.trace('GET_SUMMARY_DEDUCTION.p_contribution_code:'||p_contribution_code);
2627 hr_utility.trace('GET_SUMMARY_DEDUCTION.p_contribution_usage_id:'||to_char(p_contribution_usage_id));
2628 hr_utility.trace('GET_SUMMARY_DEDUCTION.p_pay_value:'||to_char(p_pay_value));
2629 -- if this is the last row in the table return a Y to stop processing, the indirect return is for the
2630 -- next row so that context aren't changed the direct return is for index - 1 so test that
2631 if g_summary_idx = nvl(g_summary_deductions.last + 1,g_summary_idx ) then return 'Y';
2632 else return 'N';
2633 end if;
2634
2635
2636 hr_utility.set_location('Entering:'|| l_proc, 90);
2637
2638 end GET_SUMMARY_DEDUCTION;
2639
2640 PROCEDURE MAINTAIN_SUMMARY_DEDUCTION
2641 (
2642 p_rate IN NUMBER
2643 , p_base_type IN VARCHAR2
2644 , p_base IN NUMBER
2645 , p_contribution_code IN VARCHAR2
2646 , p_contribution_usage_id IN NUMBER
2647 , p_rate_type IN VARCHAR2
2648 , p_pay_value IN NUMBER
2649 , p_rate_category IN VARCHAR2
2650 , p_user_column_instance_id IN NUMBER
2651 , p_code_rate_id IN NUMBER
2652 , p_element_name IN VARCHAR2
2653 ) is
2654 -- check that this row doesn't already exist on the g_summary_deductions table
2655 -- matching on base_type, base and contribution code
2656 -- if it does exist add the pay_value, rate and contribution_usage_id to that row
2657 -- contribution_usage_id to add in the 2 to the power code_rate_id
2658 -- if it doesn't exist then add as a new row.
2659 -- special handling for Work Accident, Travel Tax rate identinfied by pay_fr_contribution_usages.rate_category
2660 -- 'W', 'A' the contribution_usage_id for
2661 -- work accident is set from the user_column_instance_id of udt -
2662 -- currently
2663 l_proc varchar2(72) := g_package||'.maintain_summary_deduction';
2664 l_idx NUMBER := 0;
2665 l_action varchar2(1) := 'I';
2666
2667 begin
2668 -- the contribution_usage_id parameter is pay_fr_contribution_usages.contribution_usage_id
2669 -- fetch the rate_category and rate_code_id from pay_fr_contribution_usages
2670
2671 -- loop through the table to see if the row already exists
2672 -- for the matching row add in the pay_value, rate and the exponent value
2673 -- error condition ? analysis talks about trapping duplicate deduction
2674
2675 -- if it doen't exists then add new row
2676 --
2677 -- the only deductions going into summary table are rate based deductions
2678 -- also T(ransport tax) and W(ork accident) are just inserted on the table - don't need to check
2679 -- whether a row exists already
2680 hr_utility.set_location('Entering:'|| l_proc, 10);
2681
2682 if p_rate <> 0 and p_base <> 0 and p_contribution_code is not null then -- { record summary
2683
2684 if p_rate_category = 'C' then
2685 -- 115.48 This means the contribution code is not held on
2686 -- pay_fr_contribution_usages. Insert into the table with
2687 -- contribution_usage_id set to -1*contribution_usage_id
2688 -- to allow the pay_fr_contribution_usages row to be identified later.
2689 hr_utility.set_location(l_proc, 20);
2690 l_idx := nvl(g_summary_deductions.last,0) + 1;
2691 g_summary_deductions(l_idx).base_type := p_base_type;
2692 g_summary_deductions(l_idx).base := p_base;
2693 g_summary_deductions(l_idx).Contribution_usage_id_type := p_rate_category;
2694 g_summary_deductions(l_idx).contribution_usage_id:=
2695 -p_contribution_usage_id;
2696 g_summary_deductions(l_idx).pay_value := p_pay_value;
2697 g_summary_deductions(l_idx).rate_type := p_rate_type;
2698 g_summary_deductions(l_idx).rate := p_rate;
2699 g_summary_deductions(l_idx).contribution_code := p_contribution_code;
2700 g_summary_deductions(l_idx).retro := null;
2701 elsif p_rate_category not in ('T','W') then -- { code_rate_id deductions
2702 hr_utility.set_location('Entering:'|| l_proc, 30);
2703 if p_code_rate_id is null then
2704 hr_utility.trace('No CODE_RATE_ID for p_contribution_usage_id:'||to_char(p_contribution_usage_id));
2705 fnd_message.set_name('PAY','PAY_75069_NO_CODE_RATE_ID');
2706 fnd_message.set_token('ELEMENT_NAME',p_element_name);
2707 fnd_message.raise_error;
2708 else -- { all columns exists
2709 --FOR 1 to nvl(g_summary_deductions.last,0)
2710 if g_summary_deductions.FIRST is not null then -- { empty table
2711 LOOP
2712 hr_utility.set_location('Entering:'|| l_proc, 40);
2713 l_idx := l_idx + 1;
2714
2715 exit when l_action = 'U' or l_idx > nvl(g_summary_deductions.last,0);
2716
2717 if g_summary_deductions(l_idx).base_type = p_base_type
2718 and g_summary_deductions(l_idx).base = p_base
2719 and g_summary_deductions(l_idx).contribution_code = p_contribution_code
2720 and g_summary_deductions(l_idx).Contribution_usage_id_type =
2721 p_rate_category
2722 then -- { matched row
2723 hr_utility.set_location('Entering:'|| l_proc, 50);
2724 l_action := 'U' ;
2725 g_summary_deductions(l_idx).rate := g_summary_deductions(l_idx).rate + p_rate;
2726 g_summary_deductions(l_idx).pay_value := g_summary_deductions(l_idx).pay_value + p_pay_value;
2727 g_summary_deductions(l_idx).contribution_usage_id := g_summary_deductions(l_idx).contribution_usage_id
2728 + power(2,p_code_rate_id);
2729 end if; -- } matched row
2730
2731 end LOOP;
2732
2733 end if; -- } empty table
2734 -- if there wasn't a row to update then insert
2735 if l_action <> 'U' then -- { insert row
2736 hr_utility.set_location(l_proc, 60);
2737 l_idx := nvl(g_summary_deductions.last,0) + 1;
2738 g_summary_deductions(l_idx).base_type := p_base_type;
2739 g_summary_deductions(l_idx).base := p_base;
2740 g_summary_deductions(l_idx).Contribution_usage_id_type:=p_rate_category;
2741 g_summary_deductions(l_idx).contribution_usage_id := power(2,p_code_rate_id);
2742 g_summary_deductions(l_idx).pay_value := p_pay_value;
2743 g_summary_deductions(l_idx).rate_type := p_rate_type;
2744 g_summary_deductions(l_idx).rate := p_rate;
2745 g_summary_deductions(l_idx).contribution_code := p_contribution_code;
2746 g_summary_deductions(l_idx).retro := null;
2747 end if; -- } insert row
2748 end if; -- } columns exist
2749 else -- }{ end code_rate_id, start user_column_instance_id
2750 -- for T(ransport tax) and W(ork accident) deductions insert into the table with
2751 -- contribution_usage_id set to user_column_instance_id
2752 hr_utility.set_location(l_proc, 70);
2753 l_idx := nvl(g_summary_deductions.last,0) + 1;
2754 hr_utility.trace('l_idx:'||to_char(l_idx));
2755 g_summary_deductions(l_idx).base_type := p_base_type;
2756 g_summary_deductions(l_idx).base := p_base;
2757 g_summary_deductions(l_idx).Contribution_usage_id_type := p_rate_category;
2758 g_summary_deductions(l_idx).contribution_usage_id := p_user_column_instance_id;
2759 g_summary_deductions(l_idx).pay_value := p_pay_value;
2760 g_summary_deductions(l_idx).rate_type := p_rate_type;
2761 g_summary_deductions(l_idx).rate := p_rate;
2762 g_summary_deductions(l_idx).contribution_code := p_contribution_code;
2763 g_summary_deductions(l_idx).retro := null;
2764 end if; -- } code_rate_id deduction
2765 end if; -- } record summary
2766
2767 hr_utility.set_location('Leaving:'|| l_proc, 90);
2768
2769 end maintain_summary_deduction;
2770
2771 PROCEDURE MAINTAIN_RATE_CACHE
2772 (
2773 p_contribution_usage_id IN NUMBER
2774 , p_tax_unit_id IN NUMBER
2775 , p_contribution_code IN VARCHAR2
2776 , p_rate_value IN NUMBER
2777 , p_user_column_instance_id IN NUMBER
2778 , p_risk_code IN VARCHAR2
2779 ) is
2780 -- insert into t_deduction_rates the current rate being used for this assignment
2781 -- for a given contribution usage row. Note that sometimes the variable component
2782 -- of the contribution_code could change in a subsequent run of the same deduction
2783 -- for the same assignment therefore to contribution code is also cached. If the
2784 -- cached rate is used then the cached contribution code is used.
2785 l_proc varchar2(72) := g_package||'.maintain_rate_cache';
2786 l_idx NUMBER := 0;
2787
2788 begin
2789 hr_utility.set_location('Entering:'|| l_proc, 10);
2790
2791 l_idx := nvl(g_deduction_rates.last,0) + 1;
2792
2793 hr_utility.trace('p_contribution_usage_id:'||to_char(p_contribution_usage_id)||' l_idx:'||to_char(l_idx));
2794
2795 g_deduction_rates(l_idx).contribution_usage_id := p_contribution_usage_id;
2796 g_deduction_rates(l_idx).tax_unit_id := p_tax_unit_id;
2797 g_deduction_rates(l_idx).contribution_code := p_contribution_code;
2798 g_deduction_rates(l_idx).rate := p_rate_value ;
2799 g_deduction_rates(l_idx).user_column_instance_id := p_user_column_instance_id ;
2800 g_deduction_rates(l_idx).risk_code := p_rate_value ;
2801
2802 hr_utility.set_location('Leaving:'|| l_proc, 90);
2803
2804 end maintain_rate_cache;
2805
2806 FUNCTION GET_CACHED_RATE
2807 (
2808 p_assignment_id IN NUMBER
2809 , p_contribution_usage_id IN NUMBER
2810 , p_tax_unit_id IN NUMBER
2811 , p_contribution_code IN OUT nocopy VARCHAR2
2812 , p_user_column_instance_id IN OUT nocopy NUMBER
2813 , p_risk_code IN OUT nocopy VARCHAR2
2814 ) return number is
2815 -- loop through g_deduction_rates plsql table to see if the rate has been
2816 -- cached for this contribution_usage_id, tax_unit_id. If it exists return
2817 -- the cached rate with its contribution_code
2818 l_proc varchar2(72) := g_package||'.get_cached_rate';
2819 l_idx NUMBER := 0;
2820 l_rate number;
2821 l_contribution_code VARCHAR2(30);
2822
2823 begin
2824 -- the contribution_usage_id parameter is
2825 -- pay_fr_contribution_usages.contribution_usage_id
2826
2827 hr_utility.set_location('Entering:'|| l_proc, 10);
2828 -- 115.46 Rate cache is now cleared on change of (grand) parent action
2829 -- within initialize_payroll()
2830 l_rate := NULL;
2831
2832 if g_deduction_rates.FIRST is not null then -- { empty table
2833 LOOP
2834 -- hr_utility.set_location(l_proc||' loop:'||to_char(l_idx+1)||' thru deduction_rates table', 40);
2835 l_idx := l_idx + 1;
2836
2837 exit when l_idx > nvl(g_deduction_rates.last,0) or l_rate is not null;
2838
2839 if g_deduction_rates(l_idx).contribution_usage_id = p_contribution_usage_id and
2840 g_deduction_rates(l_idx).tax_unit_id = p_tax_unit_id
2841 then -- { matched row
2842 hr_utility.set_location(l_proc||' rate in cache', 50);
2843 l_rate := g_deduction_rates(l_idx).rate ;
2844 p_contribution_code := g_deduction_rates(l_idx).contribution_code;
2845 p_user_column_instance_id := g_deduction_rates(l_idx).user_column_instance_id;
2846 p_risk_code := g_deduction_rates(l_idx).risk_code;
2847
2848 end if; -- } matched row
2849
2850 end LOOP;
2851
2852 end if; -- } empty table
2853 hr_utility.set_location('leaving:'|| l_proc, 90);
2854 RETURN l_rate;
2855 end get_cached_rate;
2856 --
2857 Function get_table_rate (p_bus_group_id in number,
2858 p_table_name in varchar2,
2859 p_row_value in varchar2,
2860 p_user_row_id out NOCOPY number,
2861 p_user_column_instance_id out NOCOPY number )
2862 return number is
2863 l_effective_date date;
2864 l_table_id pay_user_tables.user_table_id%type;
2865 l_value pay_user_column_instances_f.value%type;
2866 l_user_column_instance_id pay_user_column_instances_f.user_column_instance_id%type;
2867 l_user_row_id pay_user_column_instances_f.user_row_id%type;
2868 l_proc varchar2(72) := g_package||'.get_table_rate';
2869
2870
2871 --
2872 cursor csr_get_rate is
2873
2874 select CINST.value,
2875 CINST.user_column_instance_id,
2876 CINST.user_row_id
2877 from pay_user_column_instances CINST
2878 , pay_user_columns C
2879 , pay_user_rows R
2880 , pay_user_tables TAB
2881 where TAB.user_table_name = p_table_name
2882 and C.user_table_id = TAB.user_table_id
2883 and nvl (C.business_group_id,
2884 p_bus_group_id) = p_bus_group_id
2885 and nvl (C.legislation_code,
2886 'FR') = 'FR'
2887 and C.user_column_name = 'RATE'
2888 and CINST.user_column_id = C.user_column_id
2889 and R.user_table_id = TAB.user_table_id
2890 and nvl (R.business_group_id,
2891 p_bus_group_id) = p_bus_group_id
2892 and nvl (R.legislation_code,
2893 'FR') = 'FR'
2894 and p_row_value = R.row_low_range_or_name
2895 and CINST.user_row_id = R.user_row_id
2896 and nvl (CINST.business_group_id,
2897 p_bus_group_id) = p_bus_group_id
2898 and nvl (CINST.legislation_code,
2899 'FR') = 'FR';
2900 --
2901 begin
2902 open csr_get_rate;
2903 fetch csr_get_rate into l_value, l_user_column_instance_id, l_user_row_id;
2904 close csr_get_rate;
2905
2906 p_user_column_instance_id := l_user_column_instance_id;
2907 p_user_row_id := l_user_row_id;
2908 return to_number(l_value);
2909 end;
2910
2911
2912 FUNCTION COUNT_SUMMARY_DEDUCTIONS
2913 return number is
2914 -- function called from formula to check that t_summary_deductions table
2915 -- is not empty. If not it contains rows then these need to be returned as
2916 -- FR_SUMMARY_DEDUCTION results.
2917 l_proc varchar2(72) := g_package||'.count_summary_deductions';
2918 l_count number;
2919
2920 begin
2921 hr_utility.set_location('Entering:'|| l_proc, 10);
2922 l_count := nvl(g_summary_deductions.count,0);
2923 hr_utility.trace('g_summary_deductions.count: '|| to_char(l_count));
2924 return l_count;
2925
2926 hr_utility.set_location('leaving:'|| l_proc, 90);
2927
2928 end COUNT_SUMMARY_DEDUCTIONS;
2929
2930 ------------------------------------------------------------------------
2931 end PAY_FR_GENERAL;
[Home] [Help]