[Home] [Help]
PACKAGE BODY: APPS.PAY_MULTIASG
Source
1 PACKAGE BODY pay_multiasg AS
2 /* $Header: pycaearn.pkb 120.5 2007/01/07 17:33:48 ssouresr noship $ */
3 /*
4 +======================================================================+
5 | Copyright (c) 1994 Oracle Corporation |
6 | Redwood Shores, California, USA |
7 | All rights reserved. |
8 +======================================================================+
9
10 Name : pay_multiasg
11 Filename : pycaearn.pkb
12 Change List
13 -----------
14 Date Name Vers Bug No Description
15 ---- ---- ---- ------ -----------
16 09-MAR-2002 ssouresr 2250370 Created
17 01-NOV-2005 mmukherj 4715972 Added some more parameters
18 to call convert_period_type
19 and calculate_period_earnings
20 since those functions have
21 been changed to support core
22 work pattern functionality.
23 14-DEC-2006 ssouresr modified custom element section
24 to remove prorating functions
25 */
26 --
27 -- **********************************************************************
28 -- PRORATION_REGULAR
29 -- Description: This function performs proration for the startup elements Regular Salary AND
30 -- Regular Wages. Proration occurs in the following scenarios: 1. Change of assignment status to
31 -- a status which is unpaid - ie. unpaid leave, termination; 2. Change of regular rate of pay
32 -- ie. could be a change in annual salary or hourly rate.
33 -- This function also calculates AND returns the actual hours worked in the period, vacation pay, sick
34 -- pay, vacation hours, AND sick hours. These calculations are done for all assignments of a person.
35
36
37 FUNCTION Multi_Asg_Proration_Regular (
38 p_bus_grp_id IN NUMBER,
39 p_asst_id IN NUMBER,
40 p_payroll_id IN NUMBER,
41 p_ele_entry_id IN NUMBER,
42 p_tax_unit_id IN NUMBER,
43 p_date_earned IN DATE,
44 p_period_start IN DATE,
45 p_period_end IN DATE,
46 p_run_type IN VARCHAR2)
47 RETURN NUMBER IS
48 --
49 v_assignment_id NUMBER(10,0);
50 v_salary_element VARCHAR2(80);
51 v_freq_code VARCHAR2(30);
52 v_asg_hours NUMBER(22,3);
53 v_salary_basis_code VARCHAR2(30);
54 v_work_schedule VARCHAR2(60);
55 v_periodic_salary NUMBER(27,7);
56 v_hours NUMBER(27,7);
57 v_rate NUMBER(27,7);
58 v_rate_code VARCHAR2(60);
59 v_asg_salary NUMBER(27,7);
60
61 regular_earnings NUMBER(27,7);
62 actual_hours_worked NUMBER(27,7);
63 hourly_rate NUMBER(27,7);
64 chk_hourly_rate NUMBER(27,7);
65 total_earnings NUMBER(27,7) :=0;
66
67 l_input_value_name VARCHAR2(200);
68 l_element_type VARCHAR2(200);
69 l_regular_aggregate VARCHAR2(2);
70 l_value NUMBER;
71 l_dummy_value NUMBER;
72
73 CURSOR regular_aggregate IS
74 SELECT nvl(prl_information5, 'N')
75 FROM pay_all_payrolls_f
76 WHERE payroll_id = p_payroll_id
77 AND p_date_earned BETWEEN effective_start_date
78 AND effective_end_date;
79
80 CURSOR other_assignments IS
81 SELECT DISTINCT ASG1.assignment_id
82 FROM per_assignments_f ASG1,
83 per_assignments_f ASG,
84 hr_soft_coding_keyflex SK
85 WHERE ASG.assignment_id = p_asst_id
86 AND ASG.payroll_id = p_payroll_id
87 AND ASG.person_id = ASG1.person_id
88 AND ASG.payroll_id = ASG1.payroll_id
89 AND ASG1.soft_coding_keyflex_id = SK.soft_coding_keyflex_id
90 AND SK.segment1 = to_char(p_tax_unit_id)
91 AND p_date_earned BETWEEN ASG.effective_start_date
92 AND ASG.effective_end_date
93 AND p_date_earned BETWEEN ASG1.effective_start_date
94 AND ASG1.effective_end_date;
95
96 CURSOR periodic_salary (p_assign_id NUMBER) IS
97 SELECT MIN (fffunc.cn(decode(decode(INPUTV.uom,'M','N','N','N','I','N',null),'N',
98 decode(INPUTV.hot_default_flag,'Y',nvl(EEV.screen_entry_value,
99 nvl(LIV.default_value,INPUTV.default_value)),'N',EEV.screen_entry_value),null)))
100 FROM
101 pay_element_entry_values_f EEV,
102 pay_element_entries_f EE,
103 pay_link_input_values_f LIV,
104 pay_input_values_f INPUTV,
105 pay_element_types_f E
106
107 WHERE p_date_earned BETWEEN INPUTV.effective_start_date
108 AND INPUTV.effective_end_date
109 AND INPUTV.element_type_id + 0 = E.element_type_id
110 AND LIV.input_value_id = INPUTV.input_value_id
111 AND p_date_earned BETWEEN LIV.effective_start_date
112 AND LIV.effective_end_date
113 AND EEV.input_value_id + 0 = INPUTV.input_value_id
114 AND EEV.element_entry_id = EE.element_entry_id
115 AND EEV.effective_start_date = EE.effective_start_date
116 AND EEV.effective_end_date = EE.effective_end_date
117 AND EE.element_link_id = LIV.element_link_id
118 AND EE.assignment_id = p_assign_id
119 AND p_date_earned BETWEEN EE.effective_start_date
120 AND EE.effective_end_date
121 AND nvl(EE.ENTRY_TYPE, 'E') = 'E'
122 AND E.element_name = 'Regular Salary'
123 AND E.legislation_code = 'CA'
124 AND p_date_earned BETWEEN E.effective_start_date
125 AND E.effective_end_date
126 AND INPUTV.name = 'Periodic Salary'
127 AND INPUTV.legislation_code = 'CA';
128
129
130 /*CURSOR pay_earned_start_date IS
131 SELECT PTP.start_date
132 FROM
133 per_time_periods ptp,
134 pay_payroll_actions ppa
135 WHERE ppa.date_earned BETWEEN ptp.START_DATE
136 AND ptp.END_DATE
137 AND ppa.payroll_action_id = p_payroll_action_id
138 AND ptp.payroll_id = ppa.payroll_id;
139
140
141 CURSOR pay_earned_end_date IS
142 SELECT PTP.end_date
143 FROM
144 per_time_periods ptp,
145 pay_payroll_actions ppa
146 WHERE ppa.date_earned BETWEEN ptp.START_DATE
147 AND ptp.END_DATE
148 AND ppa.payroll_action_id = p_payroll_action_id
149 AND ptp.payroll_id = ppa.payroll_id;
150 */
151
152 CURSOR work_schedule (p_assign_id NUMBER) IS
153 SELECT target.SEGMENT4
154 FROM
155 hr_soft_coding_keyflex target,
156 per_assignments_f ASSIGN
157 WHERE p_date_earned BETWEEN ASSIGN.effective_start_date
158 AND ASSIGN.effective_end_date
159 AND ASSIGN.assignment_id = p_assign_id
160 AND target.soft_coding_keyflex_id = ASSIGN.soft_coding_keyflex_id
161 AND target.enabled_flag = 'Y'
162 AND target.id_flex_num = 15;
163
164
165 CURSOR hours (p_assign_id NUMBER) IS
166 SELECT sum(decode(INPUTV.hot_default_flag,
167 'Y',nvl(EEV.screen_entry_value,nvl(LIV.default_value,INPUTV.default_value)),
168 'N',EEV.screen_entry_value))
169 FROM
170 pay_element_entry_values_f EEV,
171 pay_element_entries_f EE,
172 pay_link_input_values_f LIV,
173 pay_input_values_f INPUTV,
174 pay_element_types_f E
175
176 WHERE p_date_earned BETWEEN INPUTV.effective_start_date
177 AND INPUTV.effective_end_date
178 AND INPUTV.element_type_id + 0 = E.element_type_id
179 AND LIV.input_value_id = INPUTV.input_value_id
180 AND p_date_earned BETWEEN LIV.effective_start_date
181 AND LIV.effective_end_date
182 AND EEV.input_value_id + 0 = INPUTV.input_value_id
183 AND EEV.element_entry_id = EE.element_entry_id
184 AND EEV.effective_start_date = EE.effective_start_date
185 AND EEV.effective_end_date = EE.effective_end_date
186 AND EE.element_link_id = LIV.element_link_id
187 AND EE.assignment_id = p_assign_id
188 AND p_date_earned BETWEEN EE.effective_start_date
189 AND EE.effective_end_date
190 AND nvl(EE.ENTRY_TYPE, 'E') = 'E'
191 AND E.element_name = 'Time Entry Wages'
192 AND E.legislation_code = 'CA'
193 AND p_date_earned BETWEEN E.effective_start_date
194 AND E.effective_end_date
195 AND INPUTV.name = 'Hours'
196 AND INPUTV.legislation_code = 'CA';
197
198
199 CURSOR rate (p_assign_id NUMBER) IS
200 SELECT min (fffunc.cn(decode(
201 decode(INPUTV.uom,'M','N','N','N','I','N',null),'N',decode(INPUTV.hot_default_flag,'Y',nvl(EEV.screen_entry_value,nvl(LIV.default_value,INPUTV.default_value)),'N',EEV.screen_entry_value),null)))
202 FROM
203 pay_element_entry_values_f EEV,
204 pay_element_entries_f EE,
205 pay_link_input_values_f LIV,
206 pay_input_values_f INPUTV,
207 pay_element_types_f E
208
209 WHERE p_date_earned BETWEEN INPUTV.effective_start_date
210 AND INPUTV.effective_end_date
211 AND INPUTV.element_type_id + 0 = E.element_type_id
212 AND LIV.input_value_id = INPUTV.input_value_id
213 AND p_date_earned BETWEEN LIV.effective_start_date
214 AND LIV.effective_end_date
215 AND EEV.input_value_id + 0 = INPUTV.input_value_id
216 AND EEV.element_entry_id = EE.element_entry_id
217 AND EEV.effective_start_date = EE.effective_start_date
218 AND EEV.effective_end_date = EE.effective_end_date
219 AND EE.element_link_id = LIV.element_link_id
220 AND EE.assignment_id = p_assign_id
221 AND p_date_earned BETWEEN EE.effective_start_date
222 AND EE.effective_end_date
223 AND nvl(EE.ENTRY_TYPE, 'E') = 'E'
224 AND E.element_name = 'Regular Wages'
225 AND E.legislation_code = 'CA'
226 AND p_date_earned BETWEEN E.effective_start_date
227 AND E.effective_end_date
228 AND INPUTV.name = 'Rate'
229 AND INPUTV.legislation_code = 'CA';
230
231
232 CURSOR rate_code (p_assign_id NUMBER) IS
233 SELECT min (decode(INPUTV.hot_default_flag,'Y',nvl(EEV.screen_entry_value,
234 nvl(LIV.default_value,INPUTV.default_value)),'N',EEV.screen_entry_value))
235 FROM
236 pay_element_entry_values_f EEV,
237 pay_element_entries_f EE,
238 pay_link_input_values_f LIV,
239 pay_input_values_f INPUTV,
240 pay_element_types_f E
241
242 WHERE p_date_earned BETWEEN INPUTV.effective_start_date
243 AND INPUTV.effective_end_date
244 AND INPUTV.element_type_id + 0 = E.element_type_id
245 AND LIV.input_value_id = INPUTV.input_value_id
246 AND p_date_earned BETWEEN LIV.effective_start_date
247 AND LIV.effective_end_date
248 AND EEV.input_value_id + 0 = INPUTV.input_value_id
249 AND EEV.element_entry_id = EE.element_entry_id
250 AND EEV.effective_start_date = EE.effective_start_date
251 AND EEV.effective_end_date = EE.effective_end_date
252 AND EE.element_link_id = LIV.element_link_id
253 AND EE.assignment_id = p_assign_id
254 AND p_date_earned BETWEEN EE.effective_start_date
255 AND EE.effective_end_date
256 AND nvl(EE.ENTRY_TYPE, 'E') = 'E'
257 AND E.element_name = 'Regular Wages'
258 AND E.legislation_code = 'CA'
259 AND p_date_earned BETWEEN E.effective_start_date
260 AND E.effective_end_date
261 AND INPUTV.name = 'Rate Code'
262 AND INPUTV.legislation_code = 'CA';
263
264
265 CURSOR asg_hours (p_assign_id NUMBER) IS
266 SELECT ASSIGN.normal_hours
267 FROM
268 per_all_assignments_f ASSIGN,
269 hr_lookups HR3
270 WHERE p_date_earned BETWEEN ASSIGN.effective_start_date
271 AND ASSIGN.effective_end_date
272 AND ASSIGN.assignment_id = p_assign_id
273 AND HR3.application_id (+)= 800
274 AND HR3.lookup_code (+)= ASSIGN.frequency
275 AND HR3.lookup_type (+)= 'FREQUENCY';
276
277 CURSOR salary_basis_code (p_assign_id NUMBER) IS
278 SELECT BASES.pay_basis
279 FROM
280 per_assignments_f ASSIGN
281 , per_pay_bases BASES
282 , pay_input_values_f INPUTV
283 , pay_element_types_f ETYPE
284 , pay_rates RATE
285 , hr_lookups HR1
286 , hr_lookups HR2
287 WHERE p_date_earned BETWEEN ASSIGN.effective_start_date
288 AND ASSIGN.effective_end_date
289 AND ASSIGN.assignment_id = p_assign_id
290 AND BASES.pay_basis_id (+)= ASSIGN.pay_basis_id
294 AND ETYPE.element_type_id (+)= INPUTV.element_type_id
291 AND INPUTV.input_value_id (+)= BASES.input_value_id
292 AND p_date_earned BETWEEN nvl (INPUTV.effective_start_date, p_date_earned)
293 AND nvl (INPUTV.effective_end_date, p_date_earned)
295 AND p_date_earned BETWEEN nvl (ETYPE.effective_start_date, p_date_earned)
296 AND nvl (ETYPE.effective_end_date, p_date_earned)
297 AND RATE.rate_id (+)= BASES.rate_id
298 AND HR1.lookup_code (+)= BASES.pay_basis
299 AND HR1.lookup_type (+)= 'PAY_BASIS'
300 AND HR1.application_id (+)= 800
301 AND HR2.lookup_code (+)= BASES.rate_basis
302 AND HR2.application_id (+)= 800
303 AND HR2.lookup_type (+)= 'PAY_BASIS';
304
305 CURSOR freq_code (p_assign_id NUMBER) IS
306 SELECT HR3.lookup_code
307 FROM
308 per_all_assignments_f ASSIGN,
309 hr_lookups HR3
310 WHERE p_date_earned BETWEEN ASSIGN.effective_start_date
311 AND ASSIGN.effective_end_date
312 AND ASSIGN.assignment_id = p_assign_id
313 AND HR3.application_id (+)= 800
314 AND HR3.lookup_code (+)= ASSIGN.frequency
315 AND HR3.lookup_type (+)= 'FREQUENCY';
316
317 CURSOR salary_element (p_assign_id NUMBER) IS
318 SELECT ETYPE.element_name
319 FROM
320 per_assignments_f ASSIGN
321 , per_pay_bases BASES
322 , pay_input_values_f INPUTV
323 , pay_element_types_f ETYPE
324 , pay_rates RATE
325 , hr_lookups HR1
326 , hr_lookups HR2
327 WHERE p_date_earned BETWEEN ASSIGN.effective_start_date
328 AND ASSIGN.effective_end_date
329 AND ASSIGN.assignment_id = p_assign_id
330 AND BASES.pay_basis_id (+)= ASSIGN.pay_basis_id
331 AND INPUTV.input_value_id (+)= BASES.input_value_id
332 AND p_date_earned BETWEEN nvl (INPUTV.effective_start_date, p_date_earned)
333 AND nvl (INPUTV.effective_end_date, p_date_earned)
334 AND ETYPE.element_type_id (+)= INPUTV.element_type_id
335 AND p_date_earned BETWEEN nvl (ETYPE.effective_start_date, p_date_earned)
336 AND nvl (ETYPE.effective_end_date, p_date_earned)
337 AND RATE.rate_id (+)= BASES.rate_id
338 AND HR1.lookup_code (+)= BASES.pay_basis
339 AND HR1.lookup_type (+)= 'PAY_BASIS'
340 AND HR1.application_id (+)= 800
341 AND HR2.lookup_code (+)= BASES.rate_basis
342 AND HR2.application_id (+)= 800
343 AND HR2.lookup_type (+)= 'PAY_BASIS';
344
345 CURSOR asg_salary (p_assign_id NUMBER) IS
346 SELECT fnd_number.canonical_to_number (EEV.screen_entry_value)
347 FROM
348 per_assignments_f ASSIGN
349 , per_pay_bases BASES
350 , pay_element_entries_f EE
351 , pay_element_entry_values_f EEV
352 WHERE p_date_earned BETWEEN ASSIGN.effective_start_date
353 AND ASSIGN.effective_end_date
354 AND ASSIGN.assignment_id = p_assign_id
355 AND BASES.pay_basis_id +0 = ASSIGN.pay_basis_id
356 AND EEV.input_value_id = BASES.input_value_id
357 AND p_date_earned BETWEEN EEV.effective_start_date
358 AND EEV.effective_end_date
359 AND EE.assignment_id = ASSIGN.assignment_id
360 AND EE.entry_type = 'E'
361 AND p_date_earned BETWEEN EE.effective_start_date
362 AND EE.effective_end_date
363 AND EEV.element_entry_id = EE.element_entry_id;
364
365 --
366 BEGIN
367
368 OPEN regular_aggregate;
369 FETCH regular_aggregate INTO l_regular_aggregate;
370 CLOSE regular_aggregate;
371
372 OPEN other_assignments;
373
374 hr_utility.trace('Entered Loop');
375
376 LOOP
377
378 FETCH other_assignments
379 INTO v_assignment_id;
380 EXIT WHEN other_assignments%NOTFOUND;
381
382 hr_utility.trace('Fetched from other assignments');
383
384 IF (p_run_type = 'L' OR
385 l_regular_aggregate = 'N') THEN
386 v_assignment_id := p_asst_id;
387 END IF;
388
389 OPEN salary_element (v_assignment_id);
390 OPEN freq_code (v_assignment_id);
391 OPEN asg_hours (v_assignment_id);
392 OPEN salary_basis_code (v_assignment_id);
393 OPEN periodic_salary (v_assignment_id);
394 OPEN work_schedule (v_assignment_id);
395 OPEN rate (v_assignment_id);
396 OPEN rate_code (v_assignment_id);
397 OPEN hours (v_assignment_id);
398 OPEN asg_salary (v_assignment_id);
399
400
401 FETCH salary_element
402 INTO v_salary_element;
403
404 hr_utility.trace('Fetched from salary element');
405
406 IF (salary_element%NOTFOUND) THEN
407
408 regular_earnings := 0;
409
410 ELSIF v_salary_element = 'Regular Salary' THEN
411
415
412 hr_utility.trace('Element is Regular Salary');
413 FETCH freq_code
414 INTO v_freq_code;
416 FETCH asg_hours
417 INTO v_asg_hours;
418
419 hr_utility.trace('Fetched from feq_code and asg_hours');
420
421 FETCH salary_basis_code
422 INTO v_salary_basis_code;
423
424 FETCH periodic_salary
425 INTO v_periodic_salary;
426
427 FETCH work_schedule
428 INTO v_work_schedule;
429
430 hr_utility.trace('Going to call Convertr_Period_Type');
431
432 hourly_rate := hr_ca_ff_udfs.Convert_Period_Type(p_bus_grp_id,
433 p_payroll_id,
434 NULL,
435 p_asst_id,
436 p_ele_entry_id,
437 p_date_earned,
438 v_work_schedule,
439 v_asg_hours,
440 v_periodic_salary,
441 v_salary_basis_code,
442 'HOURLY',
443 p_period_start,
444 p_period_end,
445 v_freq_code);
446
447 hr_utility.trace('Returned from call to Convert_Period_Type');
448
449 actual_hours_worked := 0;
450
451 -- IF ASG_SALARY_BASIS_CODE WAS DEFAULTED THEN
452 -- mesg = ''Pay Basis MUST be entered for Regular Salary calculation.''
453
454 regular_earnings := hr_ca_ff_udfs.Calculate_Period_Earnings(p_bus_grp_id,
455 p_asst_id,
456 null,
457 p_payroll_id,
458 p_ele_entry_id,
459 p_tax_unit_id,
460 p_date_earned,
461 v_salary_basis_code,
462 'MONTHLY SALARY',
463 hourly_rate,
464 p_period_start,
465 p_period_end,
466 v_work_schedule,
467 v_asg_hours,
468 actual_hours_worked,
469 'Y',
470 v_freq_code);
471
472 hr_utility.trace('Calulate_Period_Earnings returns: '|| to_char(regular_earnings));
473 ELSIF v_salary_element = 'Regular Wages' THEN
474
475 FETCH rate
476 INTO v_rate;
477
478 hr_utility.trace('Salary element is Regular Wages');
479 IF (rate%FOUND) THEN
480
481 hourly_rate := v_rate;
482 actual_hours_worked := 0;
483
484 FETCH freq_code
485 INTO v_freq_code;
486
487 hr_utility.trace('Fetched from freq_code');
488 FETCH asg_hours
489 INTO v_asg_hours;
490
491 FETCH salary_basis_code
492 INTO v_salary_basis_code;
493
494 FETCH work_schedule
495 INTO v_work_schedule;
496
497 hr_utility.trace('Going to call Calculate_Period_Earnings');
498
499 regular_earnings := hr_ca_ff_udfs.Calculate_Period_Earnings(p_bus_grp_id,
500 p_asst_id,
501 null,
502 p_payroll_id,
503 p_ele_entry_id,
504 p_tax_unit_id,
505 p_date_earned,
506 v_salary_basis_code,
507 'RATE',
508 hourly_rate,
509 p_period_start,
510 p_period_end,
511 v_work_schedule,
512 v_asg_hours,
513 actual_hours_worked,
514 'Y',
515 v_freq_code);
516
517 hr_utility.trace('Calulate_Period_Earnings returns: '|| to_char(regular_earnings));
518 ELSE
519 FETCH rate_code
520 INTO v_rate_code;
521
525 INTO v_freq_code;
522 IF (rate_code%FOUND) THEN
523
524 FETCH freq_code
526
527 FETCH asg_hours
528 INTO v_asg_hours;
529
530 FETCH work_schedule
531 INTO v_work_schedule;
532
533 hourly_rate := to_number (hruserdt.get_table_value (p_bus_grp_id,
534 'WAGE RATES',
535 'Wage Rate',
536 v_rate_code));
537 actual_hours_worked := 0;
538
539 regular_earnings := hr_ca_ff_udfs.Calculate_Period_Earnings(p_bus_grp_id,
540 p_asst_id,
541 null,
542 p_payroll_id,
543 p_ele_entry_id,
544 p_tax_unit_id,
545 p_date_earned,
546 'HOURLY',
547 'RATE CODE',
548 hourly_rate,
549 p_period_start,
550 p_period_end,
551 v_work_schedule,
552 v_asg_hours,
553 actual_hours_worked,
554 'Y',
555 v_freq_code);
556
557 ELSE
558 regular_earnings := 0;
559 END IF;
560 END IF;
561
562 ELSIF v_salary_element = 'Time Entry Wages' THEN
563
564 FETCH rate_code
565 INTO v_rate_code;
566
567 IF (rate_code%FOUND) THEN
568
569 chk_hourly_rate := to_number (hruserdt.get_table_value (p_bus_grp_id,
570 'WAGE RATES',
571 'Wage Rate',
572 v_rate_code));
573 IF (chk_hourly_rate <> 0) THEN
574
575 FETCH hours
576 INTO v_hours;
577
578 regular_earnings := fffunc.round_up ( (chk_hourly_rate * to_number(v_hours)),2);
579
580 -- ELSE
581 -- mesg := 'Rate Code not found in WAGE RATES table';
582 END IF;
583
584 ELSE
585 FETCH salary_basis_code
586 INTO v_salary_basis_code;
587
588 FETCH asg_salary
589 INTO v_asg_salary;
590
591 IF (v_salary_basis_code = 'Hourly Salary') THEN
592
593 chk_hourly_rate := v_asg_salary;
594
595 ELSE
596 FETCH freq_code
597 INTO v_freq_code;
598
599 FETCH asg_hours
600 INTO v_asg_hours;
601
602 FETCH work_schedule
603 INTO v_work_schedule;
604
605 chk_hourly_rate := hr_ca_ff_udfs.Convert_Period_Type(p_bus_grp_id,
606 p_payroll_id,
607 NULL,
608 p_asst_id,
609 p_ele_entry_id,
610 v_work_schedule,
611 v_asg_hours,
612 v_asg_salary,
613 v_salary_basis_code,
614 'HOURLY',
615 p_period_start,
616 p_period_end,
617 v_freq_code);
618 END IF;
619
620 FETCH hours
621 INTO v_hours;
622
623 regular_earnings := fffunc.round_up ((chk_hourly_rate * to_number(v_hours)),2);
624
625 END IF;
626
627
628 ELSE /* Customer Salary Basis Element 5097793 */
629 -- 1] Get the type of earnings element.
630
631 FETCH salary_basis_code INTO v_salary_basis_code;
632
633 l_dummy_value := hr_ca_ff_udfs.get_earnings_and_type
634 ( p_bus_grp_id => p_bus_grp_id,
635 p_asst_id => v_assignment_id,
636 p_assignment_action_id => 0,
637 p_payroll_id => p_payroll_id,
638 p_ele_entry_id => p_ele_entry_id,
639 p_tax_unit_id => p_tax_unit_id,
640 p_date_earned => p_date_earned,
641 p_pay_basis => v_salary_basis_code,
642 p_period_start => p_period_start,
643 p_period_end => p_period_end,
644 p_element_type => l_element_type,
645 p_value => l_value,
646 p_input_value_name => l_input_value_name);
647
648 IF l_input_value_name = 'Amount' THEN
649
650 hr_utility.trace('Element is Regular Salary');
651 FETCH freq_code INTO v_freq_code;
652
653 FETCH asg_hours INTO v_asg_hours;
654
655 hr_utility.trace('Fetched from feq_code and asg_hours');
656
657 FETCH periodic_salary INTO v_periodic_salary;
658
659 FETCH work_schedule INTO v_work_schedule;
660
661 hr_utility.trace('Going to call Convertr_Period_Type');
662
663 /*Commenting out this section as custom elements in Canada
664 currently do not perform any prorating, therefore the previous
665 earnings should also not be prorated */
666 /*
667 hourly_rate := hr_ca_ff_udfs.Convert_Period_Type(p_bus_grp_id,
668 p_payroll_id,
669 NULL,
670 p_asst_id,
671 p_ele_entry_id,
672 p_date_earned,
673 v_work_schedule,
674 v_asg_hours,
675 l_value,
676 v_salary_basis_code,
677 'HOURLY',
678 p_period_start,
679 p_period_end,
680 v_freq_code);
681
682 hr_utility.trace('Returned from call to Convert_Period_Type');
683
684 actual_hours_worked := 0;
685
686 regular_earnings := hr_ca_ff_udfs.Calculate_Period_Earnings
687 (p_bus_grp_id,
688 p_asst_id,
689 null,
690 p_payroll_id,
691 p_ele_entry_id,
692 p_tax_unit_id,
693 p_date_earned,
694 v_salary_basis_code,
695 l_input_value_name,
696 hourly_rate,
697 p_period_start,
698 p_period_end,
699 v_work_schedule,
700 v_asg_hours,
701 actual_hours_worked,
702 'Y',
703 v_freq_code);
704 */
705 regular_earnings := l_value;
706
707 -- END IF; /* l_input_value_name = 'Amount' */
708
709 -- IF l_element_type = 'REGULAR_WAGES' THEN
710
711 ELSIF l_input_value_name = 'Rate' THEN
712
713 hourly_rate := l_value;
714 actual_hours_worked := 0;
715
716 FETCH freq_code INTO v_freq_code;
717
718 hr_utility.trace('Fetched from freq_code');
719 FETCH asg_hours INTO v_asg_hours;
720
721 FETCH salary_basis_code INTO v_salary_basis_code;
722
723 FETCH work_schedule INTO v_work_schedule;
724
725 hr_utility.trace('Going to call Calculate_Period_Earnings');
726
727 regular_earnings := hr_ca_ff_udfs.Calculate_Period_Earnings
728 (p_bus_grp_id,
729 p_asst_id,
730 null,
731 p_payroll_id,
732 p_ele_entry_id,
733 p_tax_unit_id,
734 p_date_earned,
735 v_salary_basis_code,
736 l_input_value_name,
737 hourly_rate,
738 p_period_start,
739 p_period_end,
740 v_work_schedule,
741 v_asg_hours,
742 actual_hours_worked,
743 'Y',
744 v_freq_code);
745
746 hr_utility.trace('Calulate_Period_Earnings returns: '|| to_char(regular_earnings));
747
748 ELSIF l_input_value_name = 'Rate Code' THEN
749
750 FETCH freq_code INTO v_freq_code;
751
752 FETCH asg_hours INTO v_asg_hours;
753
754 FETCH work_schedule INTO v_work_schedule;
755
756 hourly_rate := to_number (hruserdt.get_table_value
757 (p_bus_grp_id,
758 'WAGE RATES',
759 'Wage Rate',
760 v_rate_code));
761 actual_hours_worked := 0;
762
763 regular_earnings := hr_ca_ff_udfs.Calculate_Period_Earnings
764 (p_bus_grp_id,
765 p_asst_id,
766 null,
767 p_payroll_id,
768 p_ele_entry_id,
769 p_tax_unit_id,
770 p_date_earned,
771 'HOURLY',
772 l_input_value_name,
773 hourly_rate,
774 p_period_start,
775 p_period_end,
776 v_work_schedule,
777 v_asg_hours,
778 actual_hours_worked,
779 'Y',
780 v_freq_code);
781
782 ELSIF l_input_value_name = 'DUMMY' THEN
783 regular_earnings := 0;
784 END IF; /** l_input_value_name = 'Rate'*/
785
786 END IF;
787
788 /* Must add earnings for each assignment */
789
790 hr_utility.trace('Adding earnings : '|| to_char(regular_earnings) || ' to total earnings');
791 total_earnings := total_earnings + regular_earnings;
792
793 CLOSE salary_element;
794 CLOSE freq_code;
795 CLOSE asg_hours;
796 CLOSE salary_basis_code;
797 CLOSE periodic_salary;
798 CLOSE work_schedule;
799 CLOSE rate;
800 CLOSE rate_code;
801 CLOSE hours;
802 CLOSE asg_salary;
803
804 IF (p_run_type = 'L' OR
805 l_regular_aggregate = 'N') THEN
806 EXIT;
807 END IF;
808
809 END LOOP;
810
811 CLOSE other_assignments;
812
813 hr_utility.trace('Total earnings : '|| to_char(total_earnings));
814
815 IF total_earnings IS NULL THEN
816 total_earnings := 0;
817 END IF;
818
819 RETURN total_earnings;
820
821 END Multi_Asg_Proration_Regular;
822
823 END pay_multiasg;