[Home] [Help]
PACKAGE BODY: APPS.PAY_CORE_FF_UDFS
Source
1 PACKAGE BODY PAY_CORE_FF_UDFS as
2 /* $Header: paycoreffudfs.pkb 120.22.12020000.2 2012/07/04 19:18:51 amnaraya ship $ */
3 /*
4 +======================================================================+
5 | Copyright (c) 1994 Oracle Corporation |
6 | Redwood Shores, California, USA |
7 | All rights reserved. |
8 +======================================================================+
9
10 Name : pay_core_ff_udfs
11 Filename : paycoreffudfs.sql
12 Change List
13 --------------- -----------
14 01-May-2005 sodhingr 115.0 Defines user defined function
15 used by international payroll
16 14-JUN-2005 sodhingr 115.1 Added the function get_hourly_rate,
17 that returns the hourly rate based on
18 Salary Basis. also added the function
19 calculate_actual_hours_worked that
20 calculates the hours worked based on
21 ATG/workschedule/Std hrs
22 07-OCT-2005 sodhingr 115.2 Changed the function calculate_Actual_hours_worked
23 to use the CORE HR API to calculate work_schedule
24 and changed the procedure convert_period_type
25 to use the lookup_code instead of meaning to avoid
26 translation issue.
27 07-FEB-2005 sodhingr 115.3 added convert_period_type and calculate_period_earnings
28 that can be used by the localization team and take care
29 of proration if core proration is not enabled.
30
31 30-MAR-2005 sodhingr 115.4 change variable v_hrs_per_range
32 to v_hours_in_range in convert_period_type
33 Also, changed to exclude the exception
34 This is for bug 5127891, 5102813
35 20-JUN-2006 sodhingr 115.5 5161241 changed get_hourly_rate to get
36 the salary as of the termination
37 date if the salary is null.
38
39 21-AUG-2007 sodhingr 115.6 6163428 Changed the procedure, Conver_Period_Type to uncomment
40 the code to check the payroll calculation rule defined at the payroll level.
41 Also, moved the variable name l_normal_hours to global variable as this
42 will be used by the uncommented code to get the standard hours.
43 08-Jan-2008 sudedas 115.7 6718164 Added new Function term_skip_rule_rwage
44 21-Jan-2008 sudedas 115.8 Corrected Logic for Term Rule LSPD
45 Used Cursor csr_lspprocd_min_dtearned.
46 22-May-2008 sudedas 115.9 6163428 Changed Convert_Period_Type to Create an
47 Exception for Regular Salary that Always uses
48 Annualized Method irrespective of Payroll
49 Calculation Method. Created Function
50 get_wrk_sch_std_hrs to Calculate Standard
51 Hours per Week From Work Schedule.
52 25-Aug-2008 sudedas 115.10 5895804 Added Functions hours_between, calc_reduced_reg
53 3556204 calc_vacation_pay, calc_sick_pay
54 ER 3855241
55 17-Oct-2008 sudedas 115.12 7458563 Added the provision to enter Extra Element Information
56 12-Dec-2008 sudedas 115.13 7602381 Corrected logic introduced for Bug 7458563
57 15-Apr-2009 sudedas 115.14 8414024 Modified dynamic function call and logic for
58 calculate_actual_hours_worked and hours_between.
59 02-May-2009 sudedas 115.16 8475124 Changed functions calc_sick_pay abd calc_vacation_pay
60 22-Jun-2009 sudedas 115.17 Added function get_asg_status_typ.
61 06-Jul-2009 sudedas 115.18 8637053 Added context element_type_id to function
62 get_num_period_curr_year.
63 15-MAR-2010 tclewis 115.19 9386700 Removed code checking for Active
64 Assignment in
65 convert_period_type
66 as if the payroll type is
67 process we should process the
68 payroll.
69 17-MAR-2010 tclewis 115.20 9386700 Continue of this bug if the
70 proration upgrade has been
71 performed We will not check
72 assignment status. the function
73 get_asg_status_typ will always
74 return 'Y'. I have searched the
75 code tree and believe this code
76 is only used in fast formulas.
77 30-AUG-2010 115.21 10063757 MOdified get_asg_status_typ to reinstante
78 code prior to the last 2 changes. We
79 have added a new action_parameter to
80 'PROC_REG_SAL_INACT' to offer customer the
81 ability to determine if they want to process
82 the regular salary / wages element when the
83 Assignment Status is NOT ACTIVE_ASSIGNMENT.
84 14-Jul-2011 115.22 9955071 Modified the 'PROC_REG_SAL_INACT' processing logic to
85 work correctly when 'PROC_REG_SAL_INACT' = 'N'
86 29-May-2012 nkjaladi 115.23 14067207 Modified the cursor definition of
87 get_RegSal_ele_entry_id in procedure
88 Convert_Period_Type such that correct
89 Regular Salary Element Entry id is
90 pciked up.
91
92 Contexxt
93 =========
94
95 BUSINESS_GROUP_ID
96 ASSIGNMENT_ID
97 PAYROLL_ID
98 ELEMENT_ENTRY_ID
99 DATE_EARNED
100 ASSIGNMENT_ACTION_ID
101
102 parameters
103 ===========
104 p_period_start_date
105 p_period_end_date
106 p_schedule_category
107 p_include_exceptions
108 p_busy_tentative_as
109 p_legislation_code
110 p_schedule_source
111 p_schedule
112 p_return_status
113 p_return_message
114 */
115
116
117 -- **********************************************************************
118
119 --
120 -- ----------------------------------------------------------------------------
121 -- | Private Global Definitions |
122 -- ----------------------------------------------------------------------------
123 --
124 g_package varchar2(33) := ' pay_core_ff_udfs.'; -- Global package name
125 g_legislation_code VARCHAR2(10);
126 l_normal_hours NUMBER := 0;
127
128
129 -- Intrduced for Bug# 6163428
130 -- Introducing NEW Function for Calculating Standard Hours From Work Schedule
131 -- Assuming Work Schedule Always defines Number of Standard Hours
132 -- in Each Week Day i.e. User Table Structure has Rows Days of the Week
133
134 FUNCTION get_wrk_sch_std_hrs(p_assignment_action_id IN number
135 ,p_assignment_id IN number
136 ,p_bg_id IN NUMBER
137 ,p_element_entry_id IN number
138 ,p_date_earned IN DATE
139 ,p_period_start IN DATE
140 ,p_period_end IN DATE)
141 RETURN NUMBER IS
142
143 CURSOR get_id_flex_num IS
144 SELECT rule_mode
145 FROM pay_legislation_rules
146 WHERE legislation_code = 'US'
147 and rule_type = 'S';
148
149 Cursor get_ws_name (p_id_flex_num number,
150 p_date_earned date,
151 p_assignment_id number) IS
152 SELECT target.SEGMENT4
153 FROM /* route for SCL keyflex - assignment level */
154 hr_soft_coding_keyflex target,
155 per_all_assignments_f ASSIGN
156 WHERE p_date_earned BETWEEN ASSIGN.effective_start_date
157 AND ASSIGN.effective_end_date
158 AND ASSIGN.assignment_id = p_assignment_id
159 AND target.soft_coding_keyflex_id = ASSIGN.soft_coding_keyflex_id
160 AND target.enabled_flag = 'Y'
161 AND target.id_flex_num = p_id_flex_num;
162
163 CURSOR get_user_table(p_business_grp_id NUMBER) IS
164 select put.user_table_id
165 ,put.user_table_name
166 from hr_organization_information hoi
167 ,pay_user_tables put
168 where hoi.organization_id = p_bg_id
169 and hoi.org_information_context ='Work Schedule'
170 and hoi.org_information1 = put.user_table_id ;
171
172 CURSOR get_user_cols(p_user_table_id NUMBER
173 ,p_user_col_id NUMBER
174 ,p_bg_id NUMBER) IS
175 select PUC.user_column_id
176 ,PUC.user_column_name
177 from pay_user_tables PUT,
178 pay_user_columns PUC
179 where PUC.USER_COLUMN_ID = p_user_col_id
180 and NVL(PUC.business_group_id, p_bg_id) = p_bg_id
181 and NVL(PUC.legislation_code,'US') = 'US'
182 and PUC.user_table_id = PUT.user_table_id
183 and PUT.user_table_id = p_user_table_id;
184
185 CURSOR get_user_rows(p_user_table_id NUMBER
186 ,p_date_earned DATE) IS
187 select user_row_id
188 from pay_user_rows_f
189 where user_table_id = p_user_table_id
190 and p_date_earned between effective_start_date and effective_end_date ;
191
192 CURSOR get_user_col_values(p_user_row_id NUMBER
193 ,p_user_col_id NUMBER
194 ,p_bg_id NUMBER
195 ,p_date_earned DATE) IS
196 select value
197 from pay_user_column_instances_f
198 where user_row_id = p_user_row_id
199 and user_column_id = p_user_col_id
200 and NVL(business_group_id, p_bg_id) = p_bg_id
201 and NVL(legislation_code, 'US') = 'US'
202 and p_date_earned between effective_start_date and effective_end_date;
203
204 -- Local Variables
205 ln_id_flex_num NUMBER;
206 ln_user_col_id NUMBER;
207 ln_user_table_id NUMBER;
208 lv_user_table_name VARCHAR2(100);
209 lv_user_col_name VARCHAR2(100);
210 ln_std_hrs_wrksch NUMBER;
211 ln_user_row_id NUMBER;
212 ln_value VARCHAR2(100);
213
214 BEGIN
215 hr_utility.trace('Entering into get_wrk_sch_std_hrs');
216 hr_utility.trace('Parameters Passed..');
217 hr_utility.trace('p_assignment_action_id := ' || p_assignment_action_id);
218 hr_utility.trace('p_assignment_id := ' || p_assignment_id);
219 hr_utility.trace('p_bg_id := ' || p_bg_id);
220 hr_utility.trace('p_element_entry_id := ' || p_element_entry_id);
221 hr_utility.trace('p_date_earned := ' || TO_CHAR(p_date_earned));
222 hr_utility.trace('p_period_start := ' || TO_CHAR(p_period_start));
223 hr_utility.trace('p_period_end := ' || TO_CHAR(p_period_end));
224
225 OPEN get_id_flex_num;
226 FETCH get_id_flex_num INTO ln_id_flex_num;
227 CLOSE get_id_flex_num;
228
229 hr_utility.trace('ln_id_flex_num := ' || ln_id_flex_num);
230
231 OPEN get_ws_name(p_id_flex_num => ln_id_flex_num
232 ,p_date_earned => p_date_earned
233 ,p_assignment_id => p_assignment_id);
234 FETCH get_ws_name INTO ln_user_col_id;
235 CLOSE get_ws_name;
236
237 hr_utility.trace('ln_user_col_id := ' || ln_user_col_id);
238
239 OPEN get_user_table(p_business_grp_id => p_bg_id);
240 FETCH get_user_table INTO ln_user_table_id
241 ,lv_user_table_name;
242 CLOSE get_user_table;
243
244 hr_utility.trace('ln_user_table_id := ' || ln_user_table_id);
245 hr_utility.trace('lv_user_table_name := ' || lv_user_table_name);
246
247 OPEN get_user_cols(p_user_table_id => ln_user_table_id
248 ,p_user_col_id => ln_user_col_id
249 ,p_bg_id => p_bg_id);
250 FETCH get_user_cols INTO ln_user_col_id
251 ,lv_user_col_name;
252 CLOSE get_user_cols;
253
254 hr_utility.trace('ln_user_col_id := ' || ln_user_col_id);
255 hr_utility.trace('lv_user_col_name := ' || lv_user_col_name);
256
257 ln_std_hrs_wrksch := 0;
258 hr_utility.trace('ln_std_hrs_wrksch := ' || ln_std_hrs_wrksch);
259 OPEN get_user_rows(p_user_table_id => ln_user_table_id
260 ,p_date_earned => p_date_earned);
261 LOOP
262 FETCH get_user_rows INTO ln_user_row_id;
263
264 hr_utility.trace('ln_user_row_id := ' || ln_user_row_id);
265
266 EXIT WHEN get_user_rows%NOTFOUND;
267 OPEN get_user_col_values(p_user_row_id => ln_user_row_id
268 ,p_user_col_id => ln_user_col_id
269 ,p_bg_id => p_bg_id
270 ,p_date_earned => p_date_earned);
271 FETCH get_user_col_values INTO ln_value;
272 ln_std_hrs_wrksch := ln_std_hrs_wrksch + fnd_number.canonical_to_number(ln_value);
273 CLOSE get_user_col_values;
274
275 END LOOP;
276 CLOSE get_user_rows;
277 hr_utility.trace('Returning ln_std_hrs_wrksch := ' || ln_std_hrs_wrksch);
278 RETURN ln_std_hrs_wrksch;
279
280 END get_wrk_sch_std_hrs;
281
282 -- End of NEW Function for Calculating Standard Hours From Work Schedule
283
284 --- Functions
285 FUNCTION get_legislation_code(p_business_group_id NUMBER)
286 RETURN VARCHAR2 IS
287 CURSOR c_get_legislation_code(p_business_group_id VARCHAR2) IS
288 select legislation_code
289 from per_business_groups_perf
290 where business_group_id = p_business_group_id;
291
292 BEGIN
293 OPEN c_get_legislation_code(p_business_group_id);
294 FETCH c_get_legislation_code INTO g_legislation_code;
295 CLOSE c_get_legislation_code;
296 return g_legislation_code;
297 END;
298
299 FUNCTION Convert_Period_Type(
300 p_bg in NUMBER -- context
301 ,p_assignment_id in NUMBER -- context
302 ,p_payroll_id in NUMBER -- context
303 ,p_element_entry_id in NUMBER -- context
304 ,p_date_earned in DATE -- context
305 ,p_assignment_action_id in NUMBER -- context
306 ,p_period_start_date IN DATE
307 ,p_period_end_date IN DATE
308 /*,p_schedule_category IN varchar2 --Optional
309 ,p_include_exceptions IN varchar2 --Optional
310 ,p_busy_tentative_as IN varchar2 --Optional
311 ,p_schedule_source IN varchar2
312 ,p_schedule IN varchar2*/
313 ,p_figure in NUMBER
314 ,p_from_freq in VARCHAR2
315 ,p_to_freq in VARCHAR2
316 ,p_asst_std_freq in VARCHAR2
317 ,p_rate_calc_override in VARCHAR2)
318 RETURN NUMBER IS
319
320 -- local vars
321 v_calc_type VARCHAR2(50);
322 v_from_stnd_factor NUMBER(30,7);
323 v_stnd_start_date DATE;
324
325 v_converted_figure NUMBER(27,7);
326 v_from_annualizing_factor NUMBER(30,7);
327 v_to_annualizing_factor NUMBER(30,7);
328 v_return_status NUMBER;
329 v_return_message VARCHAR2(500);
330 v_from_freq VARCHAR2(200);
331 v_to_freq VARCHAR2(200);
332 v_rate_calc_override VARCHAR2(200);
333 v_schedule_source varchar2(100);
334 v_schedule varchar2(200);
335 ln_regsal_ele_entry_id number;
336 lv_frequency varchar2(100);
337 lv_ele_xtra_info PAY_ELEMENT_TYPE_EXTRA_INFO.eei_information10%TYPE;
338 ln_bg_id pay_element_types_f.business_group_id%TYPE;
339 lv_leg_code pay_element_types_f.legislation_code%TYPE;
340 lv_class_name pay_element_classifications.classification_name%TYPE;
341 lb_regular_salary boolean;
342
343
344 CURSOR get_RegSal_ele_entry_id(cp_leg_code IN VARCHAR2
345 ,cp_assignment_id IN NUMBER
346 ,cp_effective_date IN DATE) IS
347 SELECT peef.element_entry_id
348 FROM pay_element_entries_f peef
349 ,pay_element_types_f pet
350 where peef.element_type_id = pet.element_type_id
351 and peef.assignment_id = cp_assignment_id
352 and pet.element_name = 'Regular Salary'
353 and pet.legislation_code = cp_leg_code
354 and pet.business_group_id IS NULL
355 -- Added for #14067207 Start
356 and peef.creator_type IN ('H','P','SP'
357 ,'F','M','S'
358 ,'A','D','DF'
359 ,'R','EE','RR'
360 ,'AD','AE','PR'
361 ,'NR','FL')
362 and peef.entry_type IN ('E','S','D')
363 and cp_effective_date between peef.effective_start_date
364 and peef.effective_end_date;
365 -- Added for #14067207 end
366
367 -- Added for Bug# 7458563
368
369 CURSOR get_earning_xtra_ele_info(cp_element_entry_id IN NUMBER
370 ,cp_assignment_id IN NUMBER
371 ,cp_bg_id IN NUMBER) IS
372 SELECT petei.eei_information10
373 FROM pay_element_entries_f peef
377 and peef.assignment_id = cp_assignment_id
374 ,pay_element_types_f pet
375 ,pay_element_type_extra_info petei
376 where peef.element_entry_id = cp_element_entry_id
378 and peef.element_type_id = pet.element_type_id
379 and pet.business_group_id = cp_bg_id
380 and pet.legislation_code IS NULL
381 and pet.element_type_id = petei.element_type_id
382 and petei.information_type = 'US_EARNINGS'
383 and petei.eei_information_category = 'US_EARNINGS';
384
385 -- Added for Bug# 7602381
386
387 CURSOR get_earnings_dtls(cp_element_entry_id IN NUMBER
388 ,cp_assignment_id IN NUMBER
389 ,cp_leg_code IN VARCHAR2) IS
390 SELECT pet.business_group_id
391 ,pet.legislation_code
392 ,pec.classification_name
393 FROM pay_element_entries_f peef
394 ,pay_element_types_f pet
395 ,pay_element_classifications pec
396 WHERE peef.element_entry_id = cp_element_entry_id
397 and peef.assignment_id = cp_assignment_id
398 and peef.element_type_id = pet.element_type_id
399 and peef.effective_start_date >= pet.effective_start_date
400 and peef.effective_end_date <= pet.effective_end_date
401 and pet.classification_id = pec.classification_id
402 and pec.legislation_code = cp_leg_code
403 and pec.business_group_id IS NULL;
404
405 CURSOR get_asg_hours_freq(cp_date_earned date,
406 cp_assignment_id number) IS
407 SELECT hr_general.decode_lookup('FREQUENCY', ASSIGN.frequency)
408 ,ASSIGN.normal_hours
409 FROM per_all_assignments_f ASSIGN
410 where cp_date_earned
411 BETWEEN ASSIGN.effective_start_date
412 AND ASSIGN.effective_end_date
413 and ASSIGN.assignment_id = cp_assignment_id
414 and UPPER(ASSIGN.frequency) = 'W';
415
416
417
418 -- local fun
419
420 FUNCTION Get_Annualizing_Factor
421 ( p_bg IN number -- context
422 ,p_assignment_id IN number -- context
423 ,p_payroll_id IN number -- context
424 ,p_element_entry_id IN number -- context
425 ,p_date_earned IN date -- context
426 ,p_assignment_action_id IN number -- context
427 ,p_period_start_date IN DATE
428 ,p_period_end_date IN DATE
429 ,p_freq IN varchar2)
430
431
432 RETURN NUMBER IS
433
434 CURSOR c_get_lookupcode_freq IS
435 SELECT lookup_code
436 FROM hr_lookups lkp
437 WHERE lkp.application_id = 800
438 AND lkp.lookup_type = 'PAY_BASIS'
439 AND lkp.lookup_code = p_freq;
440
441 CURSOR c_get_lookupmeaning_freq IS
442 SELECT lookup_code
443 FROM hr_lookups lkp
444 WHERE lkp.application_id = 800
445 AND lkp.lookup_type = 'PAY_BASIS'
446 AND lkp.meaning = p_freq;
447
448 -- local constants
449
450 c_weeks_per_year NUMBER(3);
451 c_days_per_year NUMBER(3);
452 c_months_per_year NUMBER(3);
453
454 -- local vars
455
456 v_annualizing_factor NUMBER(30,7);
457 v_periods_per_fiscal_yr NUMBER(5);
458 v_hrs_per_wk NUMBER(15,7);
459 v_hrs_per_range NUMBER(15,7);
460 v_days_per_range NUMBER(15,7);
461 v_use_pay_basis NUMBER(1);
462 v_pay_basis VARCHAR2(80);
463 v_range_start DATE;
464 v_range_end DATE;
465 v_work_sched_name VARCHAR2(80);
466 v_ws_id NUMBER(9);
467 v_period_hours BOOLEAN;
468
469 lv_period_type varchar2(150);
470
471 BEGIN -- Get_Annualizing_Factor
472
473 /* Init */
474
475 c_weeks_per_year := 52;
476 c_days_per_year := 200;
477 c_months_per_year := 12;
478 v_use_pay_basis := 0;
479
480 --
481 -- Check for use of salary admin (ie. pay basis) as frequency.
482 -- Selecting "count" because we want to continue processing even if
483 -- the from_freq is not a pay basis.
484 --
485
486 hr_utility.trace(' Entered Get_Annualizing_Factor ');
487
488 BEGIN -- Is Freq pay basis?
489
490 --
491 -- Decode pay basis and set v_annualizing_factor accordingly.
492 -- PAY_BASIS "Meaning" is passed from FF !
493 --
494
495 hr_utility.trace(' Getting lookup code for lookup_type = PAY_BASIS');
496 hr_utility.trace(' p_freq = '||p_freq);
497
498
499 IF p_freq IS NULL THEN
500 v_use_pay_basis := 0;
501 ELSE
502 v_use_pay_basis := 1;
503
504 OPEN c_get_lookupcode_freq;
505 FETCH c_get_lookupcode_freq INTO v_pay_basis;
506 CLOSE c_get_lookupcode_freq;
507
508 IF v_pay_basis IS NULL THEN
509 OPEN c_get_lookupmeaning_freq;
510 FETCH c_get_lookupmeaning_freq INTO v_pay_basis;
511 CLOSE c_get_lookupmeaning_freq;
512 END IF;
513
514 --v_pay_basis := p_freq;
515
516 hr_utility.trace(' Lookup_code ie v_pay_basis ='||v_pay_basis);
520 hr_utility.trace(' Entered for MONTHLY v_pay_basis');
517
518 IF v_pay_basis = 'MONTHLY' THEN
519
521
522 v_annualizing_factor := 12;
523
524 hr_utility.trace(' v_annualizing_factor = 12 ');
525
526 ELSIF v_pay_basis = 'HOURLY' THEN
527
528 hr_utility.trace(' Entered for HOURLY v_pay_basis');
529
530 IF p_period_start_date IS NOT NULL THEN
531
532 hr_utility.trace(' p_period_start_date IS NOT NULL ' ||
533 ' v_period_hours=T');
534
535 v_range_start := p_period_start_date;
536 v_range_end := p_period_end_date;
537 v_period_hours := TRUE;
538
539 ELSE
540
541 hr_utility.trace(' p_period_start_date IS NULL');
542
543 v_range_start := sysdate;
544 v_range_end := sysdate + 6;
545 v_period_hours := FALSE;
546
547 END IF;
548
549 /* Use new function to calculate hours */
550 v_hrs_per_range := calculate_actual_hours_worked
551 ( p_assignment_action_id
552 ,p_assignment_id
553 ,p_bg
554 ,p_element_entry_id
555 ,p_date_earned
556 ,p_period_start_date
557 ,p_period_end_date
558 ,NULL
559 ,'N'
560 ,'BUSY'
561 ,''--p_legislation_code
562 ,v_schedule_source
563 ,v_schedule
564 ,v_return_status
565 ,v_return_message);
566
567 IF v_period_hours THEN
568
569 hr_utility.trace(' v_period_hours is TRUE');
570
571 SELECT TPT.number_per_fiscal_year
572 INTO v_periods_per_fiscal_yr
573 FROM pay_payrolls_f PPF,
574 per_time_period_types TPT,
575 fnd_sessions fs
576 WHERE PPF.payroll_id = p_payroll_id
577 AND fs.session_id = USERENV('SESSIONID')
578 AND fs.effective_date between PPF.effective_start_date
579 and PPF.effective_end_date
580 AND TPT.period_type = PPF.period_type;
581
582 v_annualizing_factor :=
583 v_hrs_per_range * v_periods_per_fiscal_yr;
584
585 ELSE
586
587 v_annualizing_factor := v_hrs_per_range * c_weeks_per_year;
588
589 END IF;
590
591 ELSIF v_pay_basis = 'PERIOD' THEN
592
593 hr_utility.trace(' v_pay_basis = PERIOD');
594
595 SELECT TPT.number_per_fiscal_year
596 INTO v_annualizing_factor
597 FROM pay_payrolls_f PRL,
598 per_time_period_types TPT,
599 fnd_sessions fs
600 WHERE TPT.period_type = PRL.period_type
601 and fs.session_id = USERENV('SESSIONID')
602 and fs.effective_date BETWEEN PRL.effective_start_date
603 AND PRL.effective_end_date
604 AND PRL.payroll_id = p_payroll_id
605 AND PRL.business_group_id + 0 = p_bg;
606
607
608 ELSIF v_pay_basis = 'ANNUAL' THEN
609
610
611 hr_utility.trace(' v_pay_basis = ANNUAL');
612
613 v_annualizing_factor := 1;
614
615 ELSE
616
617 -- Did not recognize "pay basis", return -999 as annualizing factor.
618 -- Remember this for debugging when zeroes come out as results!!!
619
620 hr_utility.trace(' Did not recognize pay basis');
621
622 v_annualizing_factor := 0;
623
624 RETURN v_annualizing_factor;
625
626 END IF;
627 END IF;
628
629 EXCEPTION
630
631 WHEN NO_DATA_FOUND THEN
632
633 hr_utility.trace(' When no data found' );
634 v_use_pay_basis := 0;
635
636 END; /* SELECT LOOKUP CODE */
637
638
639 IF v_use_pay_basis = 0 THEN
640
641 hr_utility.trace(' Not using pay basis as frequency');
642
643 -- Not using pay basis as frequency...
644
645 IF (p_freq IS NULL) OR
646 (UPPER(p_freq) = 'PERIOD') OR
647 (UPPER(p_freq) = 'NOT ENTERED')
648 THEN
649
650 -- Get "annuallizing factor" from period type of the payroll.
651
652 hr_utility.trace('Get annuallizing factor from period '||
653 'type of the payroll');
654
655 SELECT TPT.number_per_fiscal_year
656 INTO v_annualizing_factor
657 FROM pay_payrolls_f PRL,
658 per_time_period_types TPT,
659 fnd_sessions fs
660 WHERE TPT.period_type = PRL.period_type
661 AND fs.session_id = USERENV('SESSIONID')
662 AND fs.effective_date BETWEEN PRL.effective_start_date
663 AND PRL.effective_end_date
664 AND PRL.payroll_id = p_payroll_id
665 AND PRL.business_group_id + 0 = p_bg;
666
667 hr_utility.trace('v_annualizing_factor ='||
668 to_number(v_annualizing_factor));
669
670
671 ELSIF UPPER(p_freq) = 'HOURLY' THEN -- Hourly employee...
672
673 hr_utility.trace(' Hourly Employee');
674
675 IF p_period_start_date IS NOT NULL THEN
676 v_range_start := p_period_start_date;
677 v_range_end := p_period_end_date;
678 v_period_hours := TRUE;
679 ELSE
680 v_range_start := sysdate;
681 v_range_end := sysdate + 6;
682 v_period_hours := FALSE;
683 END IF;
684
685 /* Use new function to calculate hours */
686 v_hrs_per_range := calculate_actual_hours_worked
687 ( p_assignment_action_id
688 ,p_assignment_id
689 ,p_bg
690 ,p_element_entry_id
691 ,p_date_earned
692 ,p_period_start_date
693 ,p_period_end_date
694 ,NULL
695 ,'N'
696 ,'BUSY'
697 ,''--p_legislation_code
698 ,v_schedule_source
699 ,v_schedule
700 ,v_return_status
701 ,v_return_message);
702
703 hr_utility.trace('v_hrs_per_range ='||v_hrs_per_range);
704 IF v_period_hours THEN
705
706 hr_utility.trace('v_period_hours = TRUE');
707
708 SELECT TPT.number_per_fiscal_year
709 INTO v_periods_per_fiscal_yr
710 FROM pay_payrolls_f ppf,
711 per_time_period_types tpt,
712 fnd_sessions fs
713 WHERE ppf.payroll_id = p_payroll_id
714 AND fs.session_id = USERENV('SESSIONID')
715 AND fs.effective_date BETWEEN ppf.effective_start_date
716 AND ppf.effective_end_date
717 AND tpt.period_type = ppf.period_type;
718
719 v_annualizing_factor :=
720 v_hrs_per_range * v_periods_per_fiscal_yr;
721
722 hr_utility.trace('v_hrs_per_range ='||
723 to_number(v_hrs_per_range));
724 hr_utility.trace('v_periods_per_fiscal_yr ='||
725 to_number(v_periods_per_fiscal_yr));
726 hr_utility.trace('v_annualizing_factor ='||
727 to_number(v_annualizing_factor));
728
729 ELSE
730
731 hr_utility.trace('v_period_hours = FALSE');
732
733 v_annualizing_factor := v_hrs_per_range * c_weeks_per_year;
734
735 hr_utility.trace('v_hrs_per_range ='||
736 to_number(v_hrs_per_range));
737 hr_utility.trace('c_weeks_per_year ='||
738 to_number(c_weeks_per_year));
739 hr_utility.trace('v_annualizing_factor ='||
740 to_number(v_annualizing_factor));
741
742 END IF;
743
744 ELSE
745
746 -- Not hourly, an actual time period type!
747
751
748 hr_utility.trace('Not hourly - an actual time period type');
749
750 BEGIN
752 hr_utility.trace(' selecting from per_time_period_types');
753
754 SELECT PT.number_per_fiscal_year
755 INTO v_annualizing_factor
756 FROM per_time_period_types PT
757 WHERE UPPER(PT.period_type) = UPPER(p_freq);
758
759 hr_utility.trace('v_annualizing_factor ='||
760 to_number(v_annualizing_factor));
761
762 EXCEPTION WHEN no_data_found THEN
763
764 -- Added as part of SALLY CLEANUP.
765 -- Could have been passed in an ASG_FREQ dbi which
766 -- might have the values of
767 -- 'Day' or 'Month' which do not map to a time period type.
768 -- So we'll do these by hand.
769
770 IF UPPER(p_freq) = 'DAY' THEN
771 hr_utility.trace(' p_freq = DAY');
772 v_annualizing_factor := c_days_per_year;
773 ELSIF UPPER(p_freq) = 'MONTH' THEN
774 v_annualizing_factor := c_months_per_year;
775 hr_utility.trace(' p_freq = MONTH');
776 END IF;
777
778 END;
779
780 END IF;
781
782 END IF; -- (v_use_pay_basis = 0)
783
784
785 hr_utility.trace(' Getting out of Get_Annualizing_Factor for '||
786 v_pay_basis);
787 RETURN v_annualizing_factor;
788
789 END Get_Annualizing_Factor;
790
791 BEGIN -- Convert Figure
792 --begin_convert_period_type
793
794 --hr_utility.trace_on(null,'pay_core_ff_udfs');
795
796 IF p_from_freq IS NULL THEN
797 v_from_freq := 'NOT ENTERED';
798 END IF;
799
800 IF p_to_freq IS NULL THEN
801 v_to_freq := 'NOT ENTERED';
802 END IF;
803
804 /* IF p_rate_calc_override IS NULL THEN
805 v_rate_calc_override := 'NOT ENTERED';
806 END IF;
807 */
808 hr_utility.trace('COREUDFS Entered Convert_Period_Type');
809
810 hr_utility.trace('assignment_action_id=' || p_assignment_action_id);
811 hr_utility.trace('assignment_id=' || p_assignment_id);
812 hr_utility.trace('business_group_id=' || p_bg);
813 hr_utility.trace('element_entry_id=' || p_element_entry_id);
814 hr_utility.trace( 'p-date_earned '||p_date_earned);
815 hr_utility.trace(' p_payroll_id: '||p_payroll_id);
816 hr_utility.trace(' p_figure: '||p_figure);
817 hr_utility.trace('p_period_start_date=' || p_period_start_date);
818 hr_utility.trace('p_period_end_date=' || p_period_end_date);
819 /*hr_utility.trace('p_schedule_category=' || p_schedule_category);
820 hr_utility.trace('p_schedule_source=' || p_schedule_source);
821 hr_utility.trace('p_include_exceptions=' || p_include_exceptions);
822 hr_utility.trace('p_busy_tentative_as=' || p_busy_tentative_as);
823 hr_utility.trace('p_schedule=' || p_schedule);*/
824
825 hr_utility.trace(' p_from_freq : '||p_from_freq);
826 hr_utility.trace(' p_to_freq: '||p_to_freq);
827 hr_utility.trace(' p_asst_std_freq: '||p_asst_std_freq);
828
829
830 IF g_legislation_code IS NULL THEN
831 hr_utility.trace('g_legislation_code is null ');
832 g_legislation_code := get_legislation_code(p_bg);
833 END IF;
834
835 hr_utility.trace(' p_asst_std_freq: '||p_asst_std_freq);
836
837
838 --
839 -- If From_Freq and To_Freq are the same, then we're done.
840 --
841
842 IF NVL(p_from_freq, 'NOT ENTERED') = NVL(p_to_freq, 'NOT ENTERED') THEN
843
844 RETURN p_figure;
845
846 END IF;
847 hr_utility.trace('Calling Get_Annualizing_Factor for FROM case');
848 v_from_annualizing_factor := Get_Annualizing_Factor
849 ( p_bg
850 ,p_assignment_id
851 ,p_payroll_id
852 ,p_element_entry_id
853 ,p_date_earned
854 ,p_assignment_action_id
855 ,p_period_start_date
856 ,p_period_end_date
857 ,p_from_freq);
858
859
860
861
862 hr_utility.trace('Calling Get_Annualizing_Factor for TO case');
863
864 v_to_annualizing_factor := Get_Annualizing_Factor(
865 p_bg -- context
866 ,p_assignment_id -- context
867 ,p_payroll_id -- context
868 ,p_element_entry_id -- context
869 ,p_date_earned -- context
870 ,p_assignment_action_id -- context
871 ,p_period_start_date
872 ,p_period_END_date
873 ,p_to_freq);
874 --,p_asst_std_freq);
875
876 --
877 -- Annualize "Figure" and convert to To_Freq.
878 --
879 hr_utility.trace('v_from_annualizing_factor ='||to_char(v_from_annualizing_factor));
880 hr_utility.trace('v_to_annualizing_factor ='||to_char(v_to_annualizing_factor));
881
882 IF v_to_annualizing_factor = 0 OR
886 hr_utility.trace(' v_to_ann =0 or -999 or v_from = -999');
883 v_to_annualizing_factor = -999 OR
884 v_from_annualizing_factor = -999 THEN
885
887
888 v_converted_figure := 0;
889 RETURN v_converted_figure;
890
891 ELSE
892
893 hr_utility.trace(' v_to_ann NOT 0 or -999 or v_from = -999');
894
895 hr_utility.trace('p_figure Monthly Salary = '||p_figure);
896 hr_utility.trace('v_from_annualizing_factor = '||v_from_annualizing_factor);
897 hr_utility.trace('v_to_annualizing_factor = '||v_to_annualizing_factor);
898
899 v_converted_figure := (p_figure * v_from_annualizing_factor) / v_to_annualizing_factor;
900 hr_utility.trace('conv figure is monthly_sal * ann_from div by ann to');
901
902 hr_utility.trace('CORE UDFS v_converted_figure := '||v_converted_figure);
903
904 END IF;
905
906 -- Done
907
908 /***********************************************************
909 The is wrapper is added to check the caluclation rule given
910 at the payroll level. Depending upon the Rule we will the
911 Get_Annualizing_Factor fun calls. If the rule is
912 standard it goes to Standard Caluclation type. If the rule
913 is Annual then it goes to ANNU rule
914 **************************************************************/
915 IF p_period_start_date IS NULL THEN
916 v_stnd_start_date := sysdate;
917 ELSE
918 v_stnd_start_date := p_period_start_date ;
919 END IF;
920
921 begin
922 select nvl(ppf.prl_information2,'NOT ENTERED')
923 into v_calc_type
924 from pay_payrolls_f ppf
925 where payroll_id = p_payroll_id
926 and v_stnd_start_date between ppf.effective_start_date
927 and ppf.effective_end_Date;
928 exception
929 when others then
930 v_calc_type := null;
931 end;
932
933 -- Start Changes for Bug# 6163428
934
935 open get_RegSal_ele_entry_id(g_legislation_code
936 ,p_assignment_id
937 ,p_date_earned); --#14067207
938 fetch get_regsal_ele_entry_id into ln_regsal_ele_entry_id;
939 close get_regsal_ele_entry_id;
940
941 IF ln_regsal_ele_entry_id = p_element_entry_id THEN
942 lb_regular_salary := TRUE;
943 ELSE
944 lb_regular_salary := FALSE;
945 END IF;
946
947 -- Changes for Bug# 7602381
948
949 OPEN get_earnings_dtls(p_element_entry_id
950 ,p_assignment_id
951 ,g_legislation_code);
952 FETCH get_earnings_dtls INTO ln_bg_id
953 ,lv_leg_code
954 ,lv_class_name;
955 CLOSE get_earnings_dtls;
956
957 hr_utility.trace('ln_bg_id := ' || ln_bg_id);
958 hr_utility.trace('lv_leg_code := ' || lv_leg_code);
959 hr_utility.trace('lv_class_name := ' || lv_class_name);
960
961 -- Changes for Bug# 7458563
962
963 open get_earning_xtra_ele_info(p_element_entry_id
964 ,p_assignment_id
965 ,p_bg);
966 fetch get_earning_xtra_ele_info into lv_ele_xtra_info;
967 close get_earning_xtra_ele_info;
968
969 hr_utility.trace('g_legislation_code := ' || g_legislation_code);
970 hr_utility.trace('ln_regsal_ele_entry_id := ' || ln_regsal_ele_entry_id);
971 hr_utility.trace('p_element_entry_id := ' || p_element_entry_id);
972 hr_utility.trace('lv_ele_xtra_info := ' || lv_ele_xtra_info);
973
974 -- This will Affect for Both US And Canada
975 -- depending on g_legislation_code
976
977 -- Changing IF condition for Bug# 7602381
978 -- ( User-created element + SHOULD NOT behave
979 -- like seeded 'Regular Salary' )
980 -- OR ( Oracle seeded elememnt + NOT 'Regular Salary' )
981
982 IF ((ln_bg_id IS NOT NULL
983 and lv_leg_code IS NULL
984 and lv_class_name like '%Earnings%'
985 and NVL(lv_ele_xtra_info, 'N') = 'N')
986 OR
987 (ln_bg_id IS NULL
988 and lv_leg_code IS NOT NULL
989 and lv_class_name like '%Earnings%'
990 and NOT(lb_regular_salary))) THEN
991 IF
992 (v_calc_type = 'STND' and p_to_freq <> 'NOT ENTERED'
993 and p_rate_calc_override = 'FIXED') OR
994 (v_calc_type = 'NOT ENTERED' and p_to_freq <> 'NOT ENTERED'
995 and p_rate_calc_override = 'FIXED') OR
996 (v_calc_type = 'STND' and p_to_freq <> 'NOT ENTERED'
997 and p_rate_calc_override = 'NOT ENTERED') OR
998 (v_calc_type = 'ANNU' and p_to_freq <> 'NOT ENTERED'
999 and p_rate_calc_override = 'FIXED')
1000 THEN
1001
1002 v_from_stnd_factor := Get_Annualizing_Factor
1003 ( p_bg
1004 ,p_assignment_id
1005 ,p_payroll_id
1006 ,p_element_entry_id
1007 ,p_date_earned
1008 ,p_assignment_action_id
1009 ,p_period_start_date
1010 ,p_period_end_date
1011 ,p_from_freq);
1012
1013 -- Calling Function to get Standard Hours Worked
1014 -- As per the Work Schedule Entered.
1015 -- Assuming Work Schedule Always defines Number of Standard Hours in Each Day
1016
1017 l_normal_hours := get_wrk_sch_std_hrs(
1018 p_assignment_action_id => p_assignment_action_id
1019 ,p_assignment_id => p_assignment_id
1020 ,p_bg_id => p_bg
1021 ,p_element_entry_id => p_element_entry_id
1025
1022 ,p_date_earned => p_date_earned
1023 ,p_period_start => p_period_start_date
1024 ,p_period_end => p_period_start_date);
1026 hr_utility.trace('From Work Schedule l_normal_hours := '||l_normal_hours);
1027
1028 -- In case Work Schedule is NOT defined
1029 -- If Assignment Frequency is Week
1030 -- Standard Condition will give Number of Hours per Week
1031 -- (I am NOT Considering any Frequency other than Week)
1032
1033 IF NVL(l_normal_hours, 0) = 0 THEN
1034 open get_asg_hours_freq(p_date_earned
1035 ,p_assignment_id);
1036 fetch get_asg_hours_freq into lv_frequency
1037 ,l_normal_hours;
1038 close get_asg_hours_freq;
1039 END IF;
1040
1041 hr_utility.trace('From Standard Condition l_normal_hours := '||l_normal_hours);
1042
1043 hr_utility.trace('p_figure := '||p_figure);
1044 hr_utility.trace('v_from_stnd_factor := '||v_from_stnd_factor);
1045
1046 -- Following Condition will Arrive If NO Work Schedule Defined
1047 -- Or, Work Schedule User Table does NOT have
1048 -- "Days of the Week / Standard Hours Worked" Structure
1049 -- Or, Standard Condition Specifies Frequency Other than Week
1050 -- like Day / Month / Year etc.
1051
1052 -- Defaulting it to 40 hours / Week to Avoid
1053 -- Divide by Zero Condition but Calculation Might be Wrong
1054
1055 IF NVL(l_normal_hours, 0) = 0 THEN
1056 l_normal_hours := 40;
1057 END IF;
1058
1059 v_converted_figure :=(p_figure * v_from_stnd_factor/(52 * l_normal_hours ));
1060
1061 hr_utility.trace('v_converted_figure := '||v_converted_figure);
1062 END IF;
1063 END IF;
1064
1065 -- End Changes for Bug# 6163428
1066
1067 RETURN v_converted_figure;
1068
1069 END Convert_Period_Type;
1070 --
1071 -- **********************************************************************
1072 --
1073
1074 FUNCTION Calculate_Period_Earnings (
1075 p_bus_grp_id in NUMBER,
1076 p_asst_id in NUMBER,
1077 p_payroll_id in NUMBER,
1078 p_ele_entry_id in NUMBER,
1079 p_tax_unit_id in NUMBER,
1080 p_date_earned in DATE,
1081 p_assignment_action_id in NUMBER,
1082 p_pay_basis in VARCHAR2,
1083 p_inpval_name in VARCHAR2,
1084 p_ass_hrly_figure in NUMBER,
1085 p_period_start in DATE,
1086 p_period_end in DATE,
1087 --p_work_schedule in VARCHAR2,
1088 --p_asst_std_hrs in NUMBER,
1089 p_actual_hours_worked in out nocopy NUMBER,
1090 p_vac_hours_worked in out nocopy NUMBER,
1091 p_vac_pay in out nocopy NUMBER,
1092 p_sick_hours_worked in out nocopy NUMBER,
1093 p_sick_pay in out nocopy NUMBER,
1094 p_prorate in VARCHAR2,
1095 p_asst_std_freq in VARCHAR2)
1096 RETURN NUMBER IS
1097
1098 l_asg_info_changes NUMBER(1);
1099 l_eev_info_changes NUMBER(1);
1100 v_earnings_entry NUMBER(27,7);
1101 v_inpval_id NUMBER(9);
1102 v_pay_basis VARCHAR2(80);
1103 v_pay_periods_per_year NUMBER(3);
1104 v_period_earn NUMBER(27,7) ; -- Pay Period earnings.
1105 v_hourly_earn NUMBER(27,7); -- Hourly Rate (earnings).
1106 v_prorated_earnings NUMBER(27,7) ; -- Calc'd thru proration loops.
1107 v_curr_day VARCHAR2(3); -- Currday while summing hrs for range of dates.
1108 v_hrs_per_wk NUMBER(15,7);
1109 v_hrs_per_range NUMBER(15,7);
1110 v_asst_std_hrs NUMBER(15,7);
1111 v_asst_std_freq VARCHAR2(30);
1112 v_asg_status VARCHAR2(30);
1113 l_proc_reg_sal varchar2(3);
1114 v_hours_in_range NUMBER(15,7);
1115 v_curr_hrly_rate NUMBER(27,7) ;
1116 v_range_start DATE; -- range start of ASST rec
1117 v_range_end DATE; -- range end of ASST rec
1118 v_entry_start DATE; -- start date of ELE ENTRY rec
1119 v_entry_end DATE; -- end date of ELE ENTRY rec
1120 v_entrange_start DATE; -- max of entry or asst range start
1121 v_entrange_end DATE; -- min of entry or asst range end
1122 v_work_schedule VARCHAR2(60); -- Work Schedule ID (stored as varchar2
1123 -- in HR_SOFT_CODING_KEYFLEX; convert
1124 -- fnd_number.canonical_to_number when calling wshours fn.
1125 v_work_sched_name VARCHAR2(80);
1126 v_ws_id NUMBER(9);
1127
1128 b_entries_done BOOLEAN; -- flags no more entry changes in paypd
1129 b_asst_changed BOOLEAN; -- flags if asst changes at least once.
1130 b_on_work_schedule BOOLEAN; -- use wrk scheds or std hours
1131 l_mid_period_asg_change BOOLEAN ;
1132
1133 v_return_status NUMBER;
1134 v_return_message VARCHAR2(500);
1135 v_schedule_source varchar2(100);
1136 v_schedule varchar2(200);
1137 v_total_hours NUMBER(15,7) ;
1138
1139 /*
1140 -- ************************************************************************
1141 --
1142 -- The following cursor "get_asst_chgs" looks for *changes* to or from
1143 -- 'ACTIVE' per_assignment
1144 -- records within the supplied range of dates, *WITHIN THE SAME TAX UNIT*
1145 -- (ie. the tax unit as of the end of the period specified).
1146 -- If no "changes" are found, then assignment information is consistent
1147 -- over entire period specified.
1148 -- Before calling this cursor, will need to select tax_unit_name
1149 -- according to p_tax_unit_id.
1150 --
1151 -- ************************************************************************
1152 */
1153
1154 --
1155 -- This cursor finds ALL ASG records that are WITHIN Period Start and End Dates
1156 -- including Period End Date - NOT BETWEEN since the ASG record existing across
1160
1157 -- Period Start date has already been retrieved in SELECT (ASG1).
1158 -- Work Schedule segment is segment4 on assignment DDF
1159 --
1161 CURSOR get_asst_chgs IS
1162 SELECT ASG.effective_start_date,
1163 ASG.effective_end_date,
1164 NVL(ASG.normal_hours, 0),
1165 NVL(HRL.meaning, 'NOT ENTERED'),
1166 NVL(SCL.segment4, 'NOT ENTERED')
1167 FROM per_assignments_f ASG,
1168 per_assignment_status_types AST,
1169 hr_soft_coding_keyflex SCL,
1170 hr_lookups HRL
1171 WHERE ASG.assignment_id = p_asst_id
1172 AND ASG.business_group_id + 0 = p_bus_grp_id
1173 AND ASG.effective_start_date > p_period_start
1174 AND ASG.effective_end_date <= p_period_end
1175 AND AST.assignment_status_type_id = ASG.assignment_status_type_id
1176 AND AST.per_system_status = 'ACTIVE_ASSIGN'
1177 AND SCL.soft_coding_keyflex_id = ASG.soft_coding_keyflex_id
1178 AND SCL.segment1 = TO_CHAR(p_tax_unit_id)
1179 AND SCL.enabled_flag = 'Y'
1180 AND HRL.lookup_code(+) = ASG.frequency
1181 AND HRL.lookup_type(+) = 'FREQUENCY';
1182
1183 FUNCTION Prorate_Earnings (
1184 p_bg_id IN NUMBER,
1185 p_asg_hrly_rate IN NUMBER,
1186 -- p_wsched IN VARCHAR2 DEFAULT 'NOT ENTERED',
1187 -- p_asg_std_hours IN NUMBER,
1188 -- p_asg_std_freq IN VARCHAR2,
1189 p_range_start_date IN DATE,
1190 p_range_end_date IN DATE,
1191 p_act_hrs_worked IN OUT nocopy NUMBER) RETURN NUMBER IS
1192
1193 v_prorated_earn NUMBER(27,7) ; -- RETURN var
1194 v_hours_in_range NUMBER(15,7);
1195 v_ws_id NUMBER(9);
1196 v_ws_name VARCHAR2(80);
1197
1198 BEGIN
1199
1200 /* Init */
1201
1202 --p_wsched := 'NOT ENTERED';
1203 v_prorated_earn := 0;
1204
1205 hr_utility.trace('UDFS Entered Prorate Earnings');
1206 hr_utility.trace('p_bg_id ='||to_char(p_bg_id));
1207 hr_utility.trace('p_asg_hrly_rate ='||to_char(p_asg_hrly_rate));
1208 -- hr_utility.trace('p_wsched ='||p_wsched);
1209 -- hr_utility.trace('p_asg_std_hours ='||to_char(p_asg_std_hours));
1210 -- hr_utility.trace('p_asg_std_freq ='||p_asg_std_freq);
1211 hr_utility.trace('UDFS p_range_start_date ='||to_char(p_range_start_date));
1212 hr_utility.trace('UDFS p_range_end_date ='||to_char(p_range_end_date));
1213 hr_utility.trace('p_act_hrs_worked ='||to_char(p_act_hrs_worked));
1214
1215 -- Prorate using hourly rate passed in as param:
1216
1217 /*
1218 IF UPPER(p_wsched) = 'NOT ENTERED' THEN
1219
1220 hr_utility.set_location('Prorate_Earnings', 7);
1221 hr_utility.trace('p_wsched NOT ENTERED');
1222 hr_utility.trace('Calling Standard Hours Worked');
1223
1224 v_hours_in_range := Standard_Hours_Worked( p_asg_std_hours,
1225 p_range_start_date,
1226 p_range_end_date,
1227 p_asg_std_freq);
1228
1229 -- Keep running total of ACTUAL hours worked.
1230 hr_utility.set_location('Prorate_Earnings', 11);
1231
1232 hr_utility.trace('Keep running total of ACTUAL hours worked');
1233
1234 hr_utility.trace('actual_hours_worked before call= '||
1235 to_char(p_act_hrs_worked));
1236 hr_utility.trace('v_hours_in_range in current call= '||
1237 to_char(v_hours_in_range));
1238
1239 p_act_hrs_worked := p_act_hrs_worked + v_hours_in_range;
1240
1241 hr_utility.trace('UDFS actual_hours_worked after call = '||
1242 to_char(p_act_hrs_worked));
1243
1244 ELSE
1245
1246 hr_utility.set_location('Prorate_Earnings', 17);
1247 hr_utility.trace('Entered WORK SCHEDULE');
1248
1249 hr_utility.trace('Getting WORK SCHEDULE Name');
1250
1251 -- Get work schedule name:
1252
1253 v_ws_id := fnd_number.canonical_to_number(p_wsched);
1254
1255 hr_utility.trace('v_ws_id ='||to_char(v_ws_id));
1256
1257 SELECT user_column_name
1258 INTO v_ws_name
1259 FROM pay_user_columns
1260 WHERE user_column_id = v_ws_id
1261 AND NVL(business_group_id, p_bg_id) = p_bg_id
1262 AND NVL(legislation_code,'US') = 'US';
1263
1264 hr_utility.trace('v_ws_name ='||v_ws_name );
1265 hr_utility.trace('Calling Work_Schedule_Total_Hours');
1266
1267 v_hours_in_range := Work_Schedule_Total_Hours(
1268 p_bg_id,
1269 v_ws_name,
1270 p_range_start_date,
1271 p_range_end_date);
1272
1273 p_act_hrs_worked := p_act_hrs_worked + v_hours_in_range;
1274 hr_utility.trace('v_hours_in_range = '||to_char(v_hours_in_range));
1275
1276 END IF; -- Hours in date range via work schedule or std hours.
1277 */
1278
1279
1280 hr_utility.trace('calling PAY_CORE_FF_UDFS.calculate_actual_hours_worked');
1281 v_hours_in_range := pay_core_ff_udfs.calculate_actual_hours_worked (
1282 null
1283 ,p_asst_id
1284 ,p_bus_grp_id
1285 ,p_ele_entry_id
1286 ,p_date_earned
1287 ,p_range_start_date
1288 ,p_range_end_date
1289 ,NULL
1290 ,'Y'
1291 ,'BUSY'
1292 ,''--p_legislation_code
1293 ,v_schedule_source
1294 ,v_schedule
1295 ,v_return_status
1296 ,v_return_message);
1297
1298 p_act_hrs_worked := p_act_hrs_worked + v_hours_in_range;
1302 hr_utility.trace('v_prorated_earnings = p_asg_hrly_rate * v_hours_in_range');
1299 hr_utility.trace('v_hours_in_range = '||to_char(v_hours_in_range));
1300
1301
1303
1304
1305 hr_utility.trace('v_prorated_earnings = p_asg_hrly_rate * v_hours_in_range');
1306
1307 v_prorated_earn := v_prorated_earn + (p_asg_hrly_rate * v_hours_in_range);
1308
1309 hr_utility.trace('UDFS final v_prorated_earnings = '||to_char(v_prorated_earn));
1310 hr_utility.set_location('Prorate_Earnings', 97);
1311 p_act_hrs_worked := ROUND(p_act_hrs_worked, 3);
1312 hr_utility.trace('p_act_hrs_worked ='||to_char(p_act_hrs_worked));
1313 hr_utility.trace('UDFS Leaving Prorated Earnings');
1314
1315 RETURN v_prorated_earn;
1316
1317 END Prorate_Earnings;
1318
1319 FUNCTION Prorate_EEV ( p_bus_group_id IN NUMBER,
1320 p_pay_id IN NUMBER,
1321 --p_work_sched IN VARCHAR2 DEFAULT 'NOT ENTERED',
1322 --p_asg_std_hrs IN NUMBER,
1323 --p_asg_std_freq IN VARCHAR2,
1324 p_pay_basis IN VARCHAR2,
1325 p_hrly_rate IN OUT nocopy NUMBER,
1326 p_range_start_date IN DATE,
1327 p_range_end_date IN DATE,
1328 p_actual_hrs_worked IN OUT nocopy NUMBER,
1329 p_element_entry_id IN NUMBER,
1330 p_inpval_id IN NUMBER) RETURN NUMBER IS
1331 --
1332 -- local vars
1333 --
1334 v_eev_prorated_earnings NUMBER(27,7) ; -- Calc'd thru proration loops.
1335 v_earnings_entry VARCHAR2(60);
1336 v_entry_start DATE;
1337 v_entry_end DATE;
1338 v_hours_in_range NUMBER(15,7);
1339 v_curr_hrly_rate NUMBER(27,7);
1340 v_ws_id NUMBER(9);
1341 v_ws_name VARCHAR2(80);
1342 --
1343 -- Select for ALL records that are WITHIN Range Start and End Dates
1344 -- including Range End Date - NOT BETWEEN since the EEV record existing across
1345 -- Range Start date has already been retrieved and dealt with in SELECT (EEV1).
1346 -- A new EEV record results in a change of the current hourly rate being used
1347 -- in proration calculation.
1348 --
1349 CURSOR get_entry_chgs ( p_range_start date,
1350 p_range_end date) IS
1351 SELECT EEV.screen_entry_value,
1352 EEV.effective_start_date,
1353 EEV.effective_end_date
1354 FROM pay_element_entry_values_f EEV
1355 WHERE EEV.element_entry_id = p_element_entry_id
1356 AND EEV.input_value_id = p_inpval_id
1357 AND EEV.effective_start_date > p_range_start
1358 AND EEV.effective_end_date <= p_range_end
1359 ORDER BY EEV.effective_start_date;
1360 --
1361 BEGIN
1362
1363
1364 /* Init */
1365 --p_work_sched := 'NOT ENTERED';
1366 v_eev_prorated_earnings := 0;
1367
1368
1369 hr_utility.trace('UDFS Entering PRORATE_EEV');
1370 hr_utility.trace('p_bus_group_id ='||to_char(p_bus_group_id));
1371 hr_utility.trace('p_pay_id ='||to_char(p_pay_id));
1372 -- hr_utility.trace('p_work_sched ='||p_work_sched);
1373 --hr_utility.trace('p_asg_std_hrs ='||to_char(p_asg_std_hrs));
1374 -- hr_utility.trace('p_asg_std_freq ='||p_asg_std_freq);
1375 hr_utility.trace('p_pay_basis ='||p_pay_basis);
1376 hr_utility.trace('p_hrly_rate ='||to_char(p_hrly_rate));
1377 hr_utility.trace('UDFS p_range_start_date ='||to_char(p_range_start_date));
1378 hr_utility.trace('UDFS p_range_end_date ='||to_char(p_range_end_date));
1379 hr_utility.trace('p_actual_hrs_worked ='||to_char(p_actual_hrs_worked));
1380 hr_utility.trace('p_element_entry_id ='||to_char(p_element_entry_id));
1381 hr_utility.trace('p_inpval_id ='||to_char(p_inpval_id));
1382 --
1383 -- Find all EEV changes, calculate new hourly rate, prorate:
1384 -- SELECT (EEV1):
1385 -- Select for SINGLE record that includes Period Start Date but does not
1386 -- span entire period.
1387 -- We know this select will return a row, otherwise there would be no
1388 -- EEV changes to detect.
1389 --
1390 hr_utility.set_location('Prorate_EEV', 103);
1391 SELECT EEV.screen_entry_value,
1392 GREATEST(EEV.effective_start_date, p_range_start_date),
1393 EEV.effective_end_date
1394 INTO v_earnings_entry,
1395 v_entry_start,
1396 v_entry_end
1397 FROM pay_element_entry_values_f EEV
1398 WHERE EEV.element_entry_id = p_element_entry_id
1399 AND EEV.input_value_id = p_inpval_id
1400 AND EEV.effective_start_date <= p_range_start_date
1401 AND EEV.effective_end_date >= p_range_start_date
1402 AND EEV.effective_end_date < p_range_end_date;
1403
1404
1405 hr_utility.trace('screen_entry_value ='||v_earnings_entry);
1406 hr_utility.trace('v_entry_start ='||to_char(v_entry_start));
1407 hr_utility.trace('v_entry_end ='||to_char(v_entry_end));
1408 hr_utility.trace('Calling Convert_Period_Type ');
1409 hr_utility.set_location('Prorate_EEV', 105);
1410
1411 v_curr_hrly_rate := Convert_Period_Type(p_bus_grp_id
1412 ,p_asst_id
1413 ,p_payroll_id
1414 ,p_ele_entry_id
1415 ,p_date_earned
1416 ,p_assignment_action_id
1417 ,p_period_start -- period start date
1418 ,p_period_end -- period end date
1419 ,v_earnings_entry -- p_figure, salary amount
1420 ,p_pay_basis -- p_from freq, salary basis
1421 ,'HOURLY'); -- p_to_freq
1422
1423
1424 /*get_hourly_rate(
1425 p_bus_grp_id
1426 ,p_asst_id
1427 ,p_payroll_id
1428 ,p_ele_entry_id
1429 ,p_date_earned
1430 ,p_assignment_action_id );
1431 */
1432 /*Convert_Period_Type( p_bus_group_id,
1433 p_pay_id,
1434 p_work_sched,
1435 p_asg_std_hrs,
1436 v_earnings_entry,
1437 p_pay_basis,
1441 p_asg_std_freq); */
1438 'HOURLY',
1439 p_period_start,
1440 p_period_end,
1442 hr_utility.trace('v_curr_hrly_rate ='||to_char(v_curr_hrly_rate));
1443 hr_utility.set_location('Prorate_EEV', 107);
1444
1445 v_eev_prorated_earnings := v_eev_prorated_earnings +
1446 Prorate_Earnings (
1447 p_bg_id => p_bus_group_id,
1448 p_asg_hrly_rate => v_curr_hrly_rate,
1449 p_range_start_date => v_entry_start,
1450 p_range_end_date => v_entry_end,
1451 p_act_hrs_worked => p_actual_hrs_worked);
1452
1453 hr_utility.trace('v_eev_prorated_earnings ='||
1454 to_char(v_eev_prorated_earnings));
1455 -- SELECT (EEV2):
1456 hr_utility.trace('Opening get_entry_chgs cursor EEV2');
1457
1458 OPEN get_entry_chgs (p_range_start_date, p_range_end_date);
1459 LOOP
1460 --
1461 FETCH get_entry_chgs
1462 INTO v_earnings_entry,
1463 v_entry_start,
1464 v_entry_end;
1465 EXIT WHEN get_entry_chgs%NOTFOUND;
1466 --
1467 hr_utility.trace('v_earnings_entry ='||v_earnings_entry);
1468 hr_utility.trace('v_entry_start ='||to_char(v_entry_start));
1469 hr_utility.trace('v_entry_end ='||to_char(v_entry_end));
1470 hr_utility.set_location('Prorate_EEV', 115);
1471 --
1472 -- For each range of dates found, add to running prorated earnings total.
1473 --
1474 hr_utility.trace('Calling Convert_Period_Type ');
1475
1476 v_curr_hrly_rate := Convert_Period_Type(p_bus_grp_id
1477 ,p_asst_id
1478 ,p_payroll_id
1479 ,p_ele_entry_id
1480 ,p_date_earned
1481 ,p_assignment_action_id
1482 ,p_period_start -- period start date
1483 ,p_period_end -- period end date
1484 ,v_earnings_entry -- p_figure, salary amount
1485 ,p_pay_basis -- p_from freq, salary basis
1486 ,'HOURLY'); -- p_to_freq
1487
1488 /*Convert_Period_Type( p_bus_group_id,
1489 p_pay_id,
1490 p_work_sched,
1491 p_asg_std_hrs,
1492 v_earnings_entry,
1493 p_pay_basis,
1494 'HOURLY',
1495 p_period_start,
1496 p_period_end,
1497 p_asg_std_freq); */
1498
1499
1500 hr_utility.trace('v_curr_hrly_rate ='||to_char(v_curr_hrly_rate));
1501 hr_utility.set_location('Prorate_EEV', 119);
1502 v_eev_prorated_earnings := v_eev_prorated_earnings +
1503 Prorate_Earnings (
1504 p_bg_id => p_bus_group_id,
1505 p_asg_hrly_rate => v_curr_hrly_rate,
1506 p_range_start_date => v_entry_start,
1507 p_range_end_date => v_entry_end,
1508 p_act_hrs_worked => p_actual_hrs_worked);
1509
1510 hr_utility.trace('v_eev_prorated_earnings ='||to_char(v_eev_prorated_earnings));
1511
1512 END LOOP;
1513 --
1514 CLOSE get_entry_chgs;
1515 --
1516 -- SELECT (EEV3)
1517 -- Select for SINGLE record that exists across Period End Date:
1518 -- NOTE: Will only return a row if select (2) does not return a row where
1519 -- Effective End Date = Period End Date !
1520
1521 hr_utility.trace('Select EEV3');
1522 hr_utility.set_location('Prorate_EEV', 141);
1523 SELECT EEV.screen_entry_value,
1524 EEV.effective_start_date,
1525 LEAST(EEV.effective_end_date, p_range_end_date)
1526 INTO v_earnings_entry,
1527 v_entry_start,
1528 v_entry_end
1529 FROM pay_element_entry_values_f EEV
1530 WHERE EEV.element_entry_id = p_element_entry_id
1531 AND EEV.input_value_id = p_inpval_id
1532 AND EEV.effective_start_date > p_range_start_date
1533 AND EEV.effective_start_date <= p_range_end_date
1534 AND EEV.effective_end_date > p_range_end_date;
1535 hr_utility.set_location('Prorate_EEV', 147);
1536 hr_utility.trace('screen_entry_value ='||v_earnings_entry);
1537 hr_utility.trace('v_entry_start ='||to_char(v_entry_start));
1538 hr_utility.trace('v_entry_end ='||to_char(v_entry_end));
1539
1540 hr_utility.trace('Calling Convert_Period_Type ');
1541
1542 v_curr_hrly_rate := Convert_Period_Type(p_bus_grp_id
1543 ,p_asst_id
1544 ,p_payroll_id
1545 ,p_ele_entry_id
1546 ,p_date_earned
1547 ,p_assignment_action_id
1548 ,p_period_start -- period start date
1549 ,p_period_end -- period end date
1550 ,v_earnings_entry -- p_figure, salary amount
1551 ,p_pay_basis -- p_from freq, salary basis
1552 ,'HOURLY'); -- p_to_freq
1553 /*Convert_Period_Type( p_bus_group_id,
1554 p_pay_id,
1555 p_work_sched,
1556 p_asg_std_hrs,
1557 v_earnings_entry,
1558 p_pay_basis,
1559 'HOURLY',
1560 p_period_start,
1561 p_period_end,
1562 p_asg_std_freq);
1563 */
1567 v_eev_prorated_earnings := v_eev_prorated_earnings +
1564 hr_utility.set_location('Prorate_EEV', 151);
1565 hr_utility.trace('After Call v_curr_hrly_rate ='||to_char(v_curr_hrly_rate));
1566
1568 Prorate_Earnings (
1569 p_bg_id => p_bus_group_id,
1570 p_asg_hrly_rate => v_curr_hrly_rate,
1571 p_range_start_date => v_entry_start,
1572 p_range_end_date => v_entry_end,
1573 p_act_hrs_worked => p_actual_hrs_worked);
1574
1575 -- We're Done!
1576 hr_utility.trace('v_eev_prorated_earnings ='||
1577 to_char(v_eev_prorated_earnings));
1578 hr_utility.set_location('Prorate_EEV', 167);
1579 p_actual_hrs_worked := ROUND(p_actual_hrs_worked, 3);
1580 p_hrly_rate := v_curr_hrly_rate;
1581
1582 hr_utility.trace('p_actual_hrs_worked ='||to_char(p_actual_hrs_worked));
1583 hr_utility.trace('p_hrly_rate ='||to_char(p_hrly_rate));
1584
1585 hr_utility.trace('UDFS Leaving Prorated EEV');
1586
1587 RETURN v_eev_prorated_earnings;
1588
1589 EXCEPTION WHEN NO_DATA_FOUND THEN
1590 hr_utility.set_location('Prorate_EEV', 177);
1591 hr_utility.trace('Into exception of Prorate_EEV');
1592
1593 p_actual_hrs_worked := ROUND(p_actual_hrs_worked, 3);
1594 p_hrly_rate := v_curr_hrly_rate;
1595
1596 hr_utility.trace('p_actual_hrs_worked ='||to_char(p_actual_hrs_worked));
1597 hr_utility.trace('p_hrly_rate ='||to_char(p_hrly_rate));
1598
1599 RETURN v_eev_prorated_earnings;
1600
1601 END Prorate_EEV;
1602
1603 FUNCTION vacation_pay ( p_vac_hours IN OUT nocopy NUMBER,
1604 p_asg_id IN NUMBER,
1605 p_eff_date IN DATE,
1606 p_curr_rate IN NUMBER) RETURN NUMBER IS
1607
1608 l_vac_pay NUMBER(27,7) ;
1609 l_vac_hours NUMBER(10,7);
1610
1611 CURSOR get_vac_hours ( v_asg_id NUMBER,
1612 v_eff_date DATE) IS
1613 select fnd_number.canonical_to_number(pev.screen_entry_value)
1614 from per_absence_attendance_types abt,
1615 pay_element_entries_f pee,
1616 pay_element_entry_values_f pev
1617 where pev.input_value_id = abt.input_value_id
1618 and abt.absence_category = 'V'
1619 and v_eff_date between pev.effective_start_date
1620 and pev.effective_end_date
1621 and pee.element_entry_id = pev.element_entry_id
1622 and pee.assignment_id = v_asg_id
1623 and v_eff_date between pee.effective_start_date
1624 and pee.effective_end_date;
1625
1626 -- The "vacation_pay" fn looks for hours entered against absence types
1627 -- in the current period. The number of hours are summed and multiplied by
1628 -- the current rate of Regular Pay..
1629 -- Return immediately when no vacation time has been taken.
1630 -- Need to loop thru all "Vacation Plans" and check for entries in the current
1631 -- period for this assignment.
1632
1633 BEGIN
1634
1635 /* Init */
1636 l_vac_pay := 0;
1637
1638 hr_utility.set_location('get_vac_pay', 11);
1639 hr_utility.trace('Entered Vacation Pay');
1640
1641 OPEN get_vac_hours (p_asg_id, p_eff_date);
1642 LOOP
1643
1644 hr_utility.set_location('get_vac_pay', 13);
1645 hr_utility.trace('Opened get_vac_hours');
1646
1647 FETCH get_vac_hours
1648 INTO l_vac_hours;
1649 EXIT WHEN get_vac_hours%NOTFOUND;
1650
1651 p_vac_hours := p_vac_hours + l_vac_hours;
1652
1653 END LOOP;
1654 CLOSE get_vac_hours;
1655
1656 hr_utility.set_location('get_vac_pay', 15);
1657
1658 IF p_vac_hours <> 0 THEN
1659
1660 l_vac_pay := p_vac_hours * p_curr_rate;
1661
1662 END IF;
1663
1664 hr_utility.trace('Leaving Vacation Pay');
1665 RETURN l_vac_pay;
1666
1667 END vacation_pay;
1668
1669 FUNCTION sick_pay ( p_sick_hours IN OUT nocopy NUMBER,
1670 p_asg_id IN NUMBER,
1671 p_eff_date IN DATE,
1672 p_curr_rate IN NUMBER) RETURN NUMBER IS
1673
1674 l_sick_pay NUMBER(27,7) ;
1675 l_sick_hours NUMBER(10,7);
1676
1677 CURSOR get_sick_hours ( v_asg_id NUMBER,
1678 v_eff_date DATE) IS
1679 select fnd_number.canonical_to_number(pev.screen_entry_value)
1680 from per_absence_attendance_types abt,
1681 pay_element_entries_f pee,
1682 pay_element_entry_values_f pev
1683 where pev.input_value_id = abt.input_value_id
1684 and abt.absence_category = 'S'
1685 and v_eff_date between pev.effective_start_date
1686 and pev.effective_end_date
1687 and pee.element_entry_id = pev.element_entry_id
1688 and pee.assignment_id = v_asg_id
1689 and v_eff_date between pee.effective_start_date
1690 and pee.effective_end_date;
1691
1692 -- The "sick_pay" looks for hours entered against Sick absence types in
1693 -- the current period. The number of hours are summed and multiplied by the
1694 -- current rate of Regular Pay.
1695 -- Return immediately when no sick time has been taken.
1696
1697 BEGIN
1698
1699 /* Init */
1700 l_sick_pay :=0;
1701
1702 hr_utility.set_location('get_sick_pay', 11);
1703 hr_utility.trace('Entered Sick Pay');
1704
1705 OPEN get_sick_hours (p_asg_id, p_eff_date);
1706 LOOP
1707
1708 hr_utility.trace('get_sick_pay');
1709 hr_utility.set_location('get_sick_pay', 13);
1710
1711 FETCH get_sick_hours
1712 INTO l_sick_hours;
1713 EXIT WHEN get_sick_hours%NOTFOUND;
1714
1715 p_sick_hours := p_sick_hours + l_sick_hours;
1716
1717 END LOOP;
1718 CLOSE get_sick_hours;
1719
1723 IF p_sick_hours <> 0 THEN
1720 hr_utility.set_location('get_sick_pay', 15);
1721 hr_utility.trace('get_sick_pay');
1722
1724
1725 l_sick_pay := p_sick_hours * p_curr_rate;
1726
1727 END IF;
1728
1729 hr_utility.trace('Leaving get_sick_pay');
1730 RETURN l_sick_pay;
1731
1732 END sick_pay;
1733
1734 BEGIN -- Calculate_Period_Earnings
1735 --BEGINCALC
1736
1737 /* Init */
1738 v_period_earn := 0;
1739 v_prorated_earnings := 0;
1740 v_curr_hrly_rate := 0;
1741 l_mid_period_asg_change := FALSE;
1742
1743 -- hr_utility.trace_on(null,'coreff');
1744
1745 hr_utility.trace('UDFS Entered Calculate_Period_Earnings');
1746 hr_utility.trace('p_asst_id ='||to_char(p_asst_id));
1747 hr_utility.trace('p_payroll_id ='||to_char(p_payroll_id));
1748 hr_utility.trace('p_ele_entry_id ='||to_char(p_ele_entry_id));
1749 hr_utility.trace('p_tax_unit_id ='||to_char(p_tax_unit_id));
1750 hr_utility.trace('p_date_earned ='||to_char(p_date_earned));
1751 hr_utility.trace('p_pay_basis ='||p_pay_basis);
1752 hr_utility.trace('p_inpval_name ='||p_inpval_name);
1753 hr_utility.trace('p_ass_hrly_figure ='||to_char(p_ass_hrly_figure));
1754 hr_utility.trace('UDFS p_period_start ='||to_char(p_period_start));
1755 hr_utility.trace('UDFS p_period_end ='||to_char(p_period_end));
1756 --hr_utility.trace('p_work_schedule ='||p_work_schedule);
1757 --hr_utility.trace('p_asst_std_hrs ='||to_char(p_asst_std_hrs));
1758 hr_utility.trace('p_actual_hours_worked ='||to_char(p_actual_hours_worked));
1759 hr_utility.trace('p_vac_hours_worked ='||to_char(p_vac_hours_worked));
1760 hr_utility.trace('p_vac_pay ='||to_char(p_vac_pay));
1761 hr_utility.trace('p_sick_hours_worked ='||to_char(p_sick_hours_worked));
1762 hr_utility.trace('p_sick_pay ='||to_char(p_sick_pay));
1763 hr_utility.trace('UDFS p_prorate ='||p_prorate);
1764 hr_utility.trace('p_asst_std_freq ='||p_asst_std_freq);
1765
1766 hr_utility.trace('Find earnings element input value id');
1767
1768 p_actual_hours_worked := 0;
1769
1770 -- Step (1): Find earnings element input value.
1771 -- Get input value and pay basis according to salary admin (if exists).
1772 -- If not using salary admin, then get "Rate", "Rate Code", or "Monthly Salary"
1773 -- input value id as appropriate (according to ele name).
1774 IF g_legislation_code IS NULL THEN
1775 g_legislation_code := get_legislation_code(p_bus_grp_id);
1776 END IF;
1777 IF p_pay_basis IS NOT NULL THEN
1778
1779 BEGIN
1780
1781 hr_utility.trace(' p_pay_basis IS NOT NULL');
1782 hr_utility.set_location('calculate_period_earnings', 10);
1783
1784 SELECT PYB.input_value_id,
1785 FCL.meaning
1786 INTO v_inpval_id,
1787 v_pay_basis
1788 FROM per_assignments_f ASG,
1789 per_pay_bases PYB,
1790 hr_lookups FCL
1791 WHERE FCL.lookup_code = PYB.pay_basis
1792 AND FCL.lookup_type = 'PAY_BASIS'
1793 AND FCL.application_id = 800
1794 AND PYB.pay_basis_id = ASG.pay_basis_id
1795 AND ASG.assignment_id = p_asst_id
1796 AND p_date_earned BETWEEN ASG.effective_start_date
1797 AND ASG.effective_end_date;
1798
1799 EXCEPTION WHEN NO_DATA_FOUND THEN
1800 hr_utility.set_location('calculate_period_earnings', 11);
1801 hr_utility.trace(' In EXCEPTION p_pay_basis IS NOT NULL');
1802
1803 v_period_earn := 0;
1804 p_actual_hours_worked := ROUND(p_actual_hours_worked, 3);
1805
1806 hr_utility.trace('p_actual_hours_worked ='||to_char(p_actual_hours_worked));
1807
1808 RETURN v_period_earn;
1809
1810
1811 END;
1812
1813 hr_utility.trace('p_inpval_name = '||p_inpval_name);
1814
1815 ELSIF UPPER(p_inpval_name) = 'RATE' THEN
1816
1817 hr_utility.trace(' p_pay_basis IS NULL');
1818 hr_utility.trace('In p_inpval_name = RATE');
1819 /* Changed the element_name and name to init case and added
1820 the date join for pay_element_types_f */
1821
1822 begin
1823 SELECT IPV.input_value_id
1824 INTO v_inpval_id
1825 FROM pay_input_values_f IPV,
1826 pay_element_types_f ELT
1827 WHERE ELT.element_name = 'Regular Wages'
1828 and p_period_start BETWEEN ELT.effective_start_date
1829 AND ELT.effective_end_date
1830 and ELT.element_type_id = IPV.element_type_id
1831 and p_period_start BETWEEN IPV.effective_start_date
1832 AND IPV.effective_end_date
1833 and IPV.name = 'Rate'
1834 and ELT.legislation_code = g_legislation_code;
1835 --
1836 v_pay_basis := 'HOURLY';
1837 --
1838 EXCEPTION WHEN NO_DATA_FOUND THEN
1839
1840 hr_utility.trace('Exception of RATE ');
1841
1842 v_period_earn := 0;
1843 p_actual_hours_worked := ROUND(p_actual_hours_worked, 3);
1844
1845 hr_utility.trace('p_actual_hours_worked ='||to_char(p_actual_hours_worked));
1846
1847 RETURN v_period_earn;
1848 end;
1849 --
1850 ELSIF UPPER(p_inpval_name) = 'RATE CODE' THEN
1851 /* Changed the element_name and name to init case and added
1852 the date join for pay_element_types_f */
1853
1854 begin
1855 hr_utility.trace('In RATE CODE');
1856
1857 SELECT IPV.input_value_id
1858 INTO v_inpval_id
1859 FROM pay_input_values_f IPV,
1860 pay_element_types_f ELT
1861 WHERE ELT.element_name = 'Regular Wages'
1862 and p_period_start BETWEEN ELT.effective_start_date
1863 AND ELT.effective_end_date
1864 and ELT.element_type_id = IPV.element_type_id
1868 and ELT.legislation_code = g_legislation_code;
1865 and p_period_start BETWEEN IPV.effective_start_date
1866 AND IPV.effective_end_date
1867 and IPV.name = 'Rate Code'
1869 --
1870 v_pay_basis := 'HOURLY';
1871 --
1872 EXCEPTION WHEN NO_DATA_FOUND THEN
1873 hr_utility.trace('Exception of Rate Code');
1874
1875 v_period_earn := 0;
1876 p_actual_hours_worked := ROUND(p_actual_hours_worked, 3);
1877
1878 hr_utility.trace('p_actual_hours_worked ='||to_char(p_actual_hours_worked));
1879
1880 RETURN v_period_earn;
1881
1882 end;
1883 --
1884 ELSIF UPPER(p_inpval_name) = 'MONTHLY SALARY' THEN
1885
1886 /* Changed the element_name and name to init case and added
1887 the date join for pay_element_types_f */
1888
1889 begin
1890 hr_utility.trace('in MONTHLY SALARY');
1891
1892 SELECT IPV.input_value_id
1893 INTO v_inpval_id
1894 FROM pay_input_values_f IPV,
1895 pay_element_types_f ELT
1896 WHERE ELT.element_name = 'Regular Salary'
1897 and p_period_start BETWEEN ELT.effective_start_date
1898 AND ELT.effective_end_date
1899 and ELT.element_type_id = IPV.element_type_id
1900 and p_period_start BETWEEN IPV.effective_start_date
1901 AND IPV.effective_end_date
1902 and IPV.name = 'Monthly Salary'
1903 and ELT.legislation_code = g_legislation_code;
1904
1905 v_pay_basis := 'MONTHLY';
1906
1907 EXCEPTION WHEN NO_DATA_FOUND THEN
1908 hr_utility.set_location('calculate_period_earnings', 18);
1909 v_period_earn := 0;
1910 p_actual_hours_worked := ROUND(p_actual_hours_worked, 3);
1911 hr_utility.trace('p_actual_hours_worked ='||to_char(p_actual_hours_worked));
1912 RETURN v_period_earn;
1913 END;
1914
1915 END IF;
1916
1917 hr_utility.trace('Now know the pay basis for this assignment');
1918 hr_utility.trace('v_inpval_id ='||to_char(v_inpval_id));
1919 hr_utility.trace('v_pay_basis ='||v_pay_basis);
1920 /*
1921 -- Now know the pay basis for this assignment (v_pay_basis).
1922 -- Want to convert entered earnings to pay period earnings.
1923 -- For pay basis of Annual, Monthly, Bi-Weekly, Semi-Monthly,
1924 -- or Period (ie. anything
1925 -- other than Hourly):
1926 -- Annualize entered earnings according to pay basis;
1927 -- then divide by number of payroll periods per fiscal
1928 -- yr for pay period earnings.
1929 -- 02 Dec 1993:
1930 -- Actually, passing in an "Hourly" figure from formula alleviates
1931 -- having to convert in here --> we have Convert_Period_Type fn
1932 -- available to formulae, so a Monthly Salary can be converted before
1933 -- calling this fn. Then we just find the hours scheduled for current period as
1934 -- per the Hourly pay basis algorithm below.
1935 --
1936 -- For Hourly pay basis:
1937 -- Get hours scheduled for the current period either from:
1938 -- 1. ASG work schedule
1939 -- 2. ORG default work schedule
1940 -- 3. ASG standard hours and frequency
1941 -- Multiply the hours scheduled for period by normal Hourly Rate (ie. from
1942 -- pre-defined earnings, REGULAR_WAGES_RATE) pay period earnings.
1943 --
1944 -- In either case, need to find the payroll period type, let's do it upfront:
1945 -- Assignment.payroll_id --> Payroll.period_type
1946 -- --> Per_time_period_types.number_per_fiscal_year.
1947 -- Actually, the number per fiscal year could be found in more than one way:
1948 -- Could also go to per_time_period_rules, but would mean decoding the
1949 -- payroll period type to an appropriate proc_period_type code.
1950 --
1951 */
1952
1953 -- Find # of payroll period types per fiscal year:
1954
1955 begin
1956
1957 hr_utility.trace('Find # of payroll period types per fiscal year');
1958 hr_utility.set_location('calculate_period_earnings', 40);
1959
1960 SELECT TPT.number_per_fiscal_year
1961 INTO v_pay_periods_per_year
1962 FROM pay_payrolls_f PRL,
1963 per_time_period_types TPT
1964 WHERE TPT.period_type = PRL.period_type
1965 AND p_period_end between PRL.effective_start_date
1966 and PRL.effective_end_date
1967 AND PRL.payroll_id = p_payroll_id
1968 AND PRL.business_group_id + 0 = p_bus_grp_id;
1969
1970 hr_utility.trace('v_pay_periods_per_year ='||to_char(v_pay_periods_per_year));
1971
1972 exception when NO_DATA_FOUND then
1973
1974 hr_utility.set_location('calculate_period_earnings', 41);
1975 hr_utility.trace('Exception Find # of payroll period');
1976 v_period_earn := 0;
1977 p_actual_hours_worked := ROUND(p_actual_hours_worked, 3);
1978 hr_utility.trace('p_actual_hours_worked ='||to_char(p_actual_hours_worked));
1979
1980 RETURN v_period_earn;
1981
1982 end;
1983
1984 /*
1985 -- Pay basis is hourly,
1986 -- Get hours scheduled for the current period either from:
1987 -- 1. ASG work schedule
1988 -- 2. ORG default work schedule
1989 -- 3. ASG standard hours and frequency
1990 -- Do we pass in Work Schedule from asst scl db item? Yes
1991 -- 10-JAN-1996 hparicha : We no longer assume "standard hours" represent
1992 -- a weekly figure. We also no longer use a week as
1993 -- the basis for annualization,
1994 -- even when using work schedule - ie. need to find ACTUAL
1995 -- scheduled hours, not
1996 -- actual hours for a week, converted to a period figure.
1997 */
1998 --
1999 hr_utility.set_location('calculate_period_earnings', 45);
2000 hr_utility.trace('Get hours scheduled for the current period');
2001
2005 --
2002 /*IF p_work_schedule <> 'NOT ENTERED' THEN
2003 --
2004 -- Find hours worked between period start and end dates.
2006 hr_utility.trace('Asg has Work Schedule');
2007 hr_utility.trace('p_work_schedule ='||p_work_schedule);
2008
2009 v_ws_id := fnd_number.canonical_to_number(p_work_schedule);
2010 hr_utility.trace('v_ws_id ='||to_char(v_ws_id));
2011 --
2012 SELECT user_column_name
2013 INTO v_work_sched_name
2014 FROM pay_user_columns
2015 WHERE user_column_id = v_ws_id
2016 AND NVL(business_group_id, p_bus_grp_id) = p_bus_grp_id
2017 AND NVL(legislation_code,'US') = 'US';
2018
2019 hr_utility.trace('v_work_sched_name ='||v_work_sched_name);
2020 hr_utility.trace('Calling Work_Schedule_Total_Hours');
2021
2022 v_hrs_per_range := Work_Schedule_Total_Hours( p_bus_grp_id,
2023 v_work_sched_name,
2024 p_period_start,
2025 p_period_end);
2026 hr_utility.trace('v_hrs_per_range ='||to_char(v_hrs_per_range));
2027 ELSE
2028
2029 hr_utility.trace('Asg has No Work Schedule');
2030 hr_utility.trace('Calling Standard_Hours_Worked');
2031
2032 v_hrs_per_range := Standard_Hours_Worked( p_asst_std_hrs,
2033 p_period_start,
2034 p_period_end,
2035 p_asst_std_freq);
2036 hr_utility.trace('v_hrs_per_range ='||to_char(v_hrs_per_range));
2037
2038 END IF;
2039 */
2040
2041 v_hrs_per_range := pay_core_ff_udfs.calculate_actual_hours_worked (
2042 null
2043 ,p_asst_id
2044 ,p_bus_grp_id
2045 ,p_ele_entry_id
2046 ,p_date_earned
2047 ,p_period_start
2048 ,p_period_end
2049 ,NULL
2050 ,'Y'
2051 ,'BUSY'
2052 ,''--p_legislation_code
2053 ,v_schedule_source
2054 ,v_schedule
2055 ,v_return_status
2056 ,v_return_message);
2057 hr_utility.trace('v_hrs_per_range ='||to_char(v_hrs_per_range));
2058
2059 hr_utility.trace('Compute earnings and actual hours');
2060 hr_utility.trace('calling convert_period_type from calculate_period_earnings');
2061 hr_utility.set_location('calculate_period_earnings', 46);
2062
2063 v_period_earn := Convert_Period_Type(p_bus_grp_id
2064 ,p_asst_id
2065 ,p_payroll_id
2066 ,p_ele_entry_id
2067 ,p_date_earned
2068 ,p_assignment_action_id
2069 ,p_period_start -- period start date
2070 ,p_period_end -- period end date
2071 ,p_ass_hrly_figure -- p_figure, salary amount
2072 ,'HOURLY' -- p_from freq, salary basis
2073 ,NULL); -- p_to_freq
2074
2075 /*Convert_Period_Type( p_bus_grp_id,
2076 p_payroll_id,
2077 p_work_schedule,
2078 p_asst_std_hrs,
2079 p_ass_hrly_figure,
2080 'HOURLY',
2081 NULL,
2082 p_period_start,
2083 p_period_end,
2084 p_asst_std_freq); */
2085
2086 hr_utility.trace('v_period_earn ='||to_char(v_period_earn));
2087 hr_utility.set_location('calculate_period_earnings', 47);
2088
2089 p_actual_hours_worked := v_hrs_per_range;
2090
2091 hr_utility.trace('p_actual_hours_worked ='||to_char(p_actual_hours_worked));
2092
2093 IF p_prorate = 'N' THEN
2094
2095 hr_utility.trace('No proration');
2096 hr_utility.trace('Calling p_vac_pay');
2097 hr_utility.set_location('calculate_period_earnings', 49);
2098
2099 p_vac_pay := vacation_pay( p_vac_hours => p_vac_hours_worked,
2100 p_asg_id => p_asst_id,
2101 p_eff_date => p_period_end,
2102 p_curr_rate => p_ass_hrly_figure);
2103
2104 hr_utility.trace('p_vac_pay ='||to_char(p_vac_pay));
2105
2106 hr_utility.trace('Calling sick Pay');
2107 p_sick_pay := sick_pay( p_sick_hours => p_sick_hours_worked,
2108 p_asg_id => p_asst_id,
2109 p_eff_date => p_period_end,
2110 p_curr_rate => p_ass_hrly_figure);
2111
2112
2113 hr_utility.trace('p_sick_pay ='||to_char(p_sick_pay));
2114
2115 p_actual_hours_worked := ROUND(p_actual_hours_worked, 3);
2116
2117 hr_utility.trace('p_actual_hours_worked ='||to_char(p_actual_hours_worked));
2118 hr_utility.trace('UDFS v_period_earn ='||to_char(v_period_earn));
2119
2120 RETURN v_period_earn;
2121
2122 END IF; /* IF p_prorate = 'N' */
2123
2124
2125 hr_utility.trace('UDFS check for ASGMPE changes');
2126 hr_utility.set_location('calculate_period_earnings', 51);
2127 /* ************************************************************** */
2128
2129 BEGIN /* Check ASGMPE */
2130
2131 select 1 INTO l_asg_info_changes
2132 from dual
2133 where exists (
2134 SELECT 1
2135 FROM per_assignments_f ASG,
2136 per_assignment_status_types AST,
2137 hr_soft_coding_keyflex SCL
2138 WHERE ASG.assignment_id = p_asst_id
2139 AND ASG.effective_start_date <= p_period_start
2140 AND ASG.effective_end_date >= p_period_start
2141 AND ASG.effective_end_date < p_period_end
2145 AND SCL.segment1 = TO_CHAR(p_tax_unit_id)
2142 AND AST.assignment_status_type_id = ASG.assignment_status_type_id
2143 AND AST.per_system_status = 'ACTIVE_ASSIGN'
2144 AND SCL.soft_coding_keyflex_id = ASG.soft_coding_keyflex_id
2146 AND SCL.enabled_flag = 'Y' );
2147
2148 hr_utility.trace('ASGMPE Changes found');
2149 hr_utility.trace('Need to prorate b/c of ASGMPE');
2150 hr_utility.trace('Set l_mid_period_asg_change to TRUE I');
2151
2152 l_mid_period_asg_change := TRUE;
2153
2154 hr_utility.set_location('calculate_period_earnings', 56);
2155 hr_utility.trace('Look for EEVMPE changes');
2156
2157 BEGIN /* EEVMPE check - maybe pick*/
2158
2159 select 1 INTO l_eev_info_changes
2160 from dual
2161 where exists (
2162 SELECT 1
2163 FROM pay_element_entry_values_f EEV
2164 WHERE EEV.element_entry_id = p_ele_entry_id
2165 AND EEV.input_value_id+0 = v_inpval_id
2166 AND ( ( EEV.effective_start_date <= p_period_start
2167 AND EEV.effective_end_date >= p_period_start
2168 AND EEV.effective_end_date < p_period_end)
2169 OR ( EEV.effective_start_date between p_period_start and p_period_end)
2170 ) );
2171
2172
2173
2174 hr_utility.trace('EEVMPE changes found after ASGMPE');
2175
2176 EXCEPTION
2177
2178 WHEN NO_DATA_FOUND THEN
2179 l_eev_info_changes := 0;
2180
2181 hr_utility.trace('From EXCEPTION ASGMPE changes found No EEVMPE changes');
2182
2183 END; /* EEV1 check*/
2184
2185 EXCEPTION
2186
2187 WHEN NO_DATA_FOUND THEN
2188
2189 l_asg_info_changes := 0;
2190 hr_utility.trace('From EXCEPTION No ASGMPE changes, nor EEVMPE changes');
2191
2192 END; /* ASGMPE check*/
2193
2194 /* ************************************************ */
2195
2196 IF l_asg_info_changes = 0 THEN /* Check ASGMPS */
2197
2198 hr_utility.trace(' Into l_asg_info_changes = 0');
2199 hr_utility.trace('UDFS looking for ASGMPS changes');
2200 hr_utility.set_location('calculate_period_earnings', 56);
2201
2202 BEGIN /* ASGMPS changes */
2203
2204 select 1 INTO l_asg_info_changes
2205 from dual
2206 where exists (
2207 SELECT 1
2208 FROM per_assignments_f ASG,
2209 per_assignment_status_types AST,
2210 hr_soft_coding_keyflex SCL
2211 WHERE ASG.assignment_id = p_asst_id
2212 AND ASG.effective_start_date > p_period_start
2213 AND ASG.effective_start_date <= p_period_end
2214 AND AST.assignment_status_type_id = ASG.assignment_status_type_id
2215 AND AST.per_system_status = 'ACTIVE_ASSIGN'
2216 AND SCL.soft_coding_keyflex_id = ASG.soft_coding_keyflex_id
2217 AND SCL.segment1 = TO_CHAR(p_tax_unit_id)
2218 AND SCL.enabled_flag = 'Y');
2219
2220 l_mid_period_asg_change := TRUE;
2221
2222 hr_utility.trace('Need to prorate for ASGMPS changes');
2223 hr_utility.set_location('calculate_period_earnings', 57);
2224
2225 BEGIN /* EEVMPE changes ASGMPS */
2226
2227 select 1 INTO l_eev_info_changes
2228 from dual
2229 where exists (
2230 SELECT 1
2231 FROM pay_element_entry_values_f EEV
2232 WHERE EEV.element_entry_id = p_ele_entry_id
2233 AND EEV.input_value_id+0 = v_inpval_id
2234 AND ( ( EEV.effective_start_date <= p_period_start
2235 AND EEV.effective_end_date >= p_period_start
2236 AND EEV.effective_end_date < p_period_end)
2237 --OR ( EEV.effective_start_date between p_period_start and p_period_end)
2238 ) );
2239
2240
2241 hr_utility.trace('Need to prorate EEVMPS changes after ASGMPS ');
2242
2243 EXCEPTION
2244
2245 WHEN NO_DATA_FOUND THEN
2246
2247 l_eev_info_changes := 0;
2248
2249 hr_utility.trace('From EXCEPTIION No EEVMPE changes');
2250
2251 END; /* EEVMPE changes */
2252
2253 EXCEPTION
2254
2255 WHEN NO_DATA_FOUND THEN
2256
2257 l_asg_info_changes := 0;
2258
2259 hr_utility.trace('From EXCEPTION no changes due to ASGMPS or EEVMPE');
2260
2261 END; /* ASGMPS changes */
2262
2263 END IF; /* Check ASGMPS */
2264
2265 /* *************************************************** */
2266
2267 IF l_asg_info_changes = 0 THEN /* ASGMPE=0 and ASGMPS=0 */
2268
2269 BEGIN /* Check for EEVMPE changes */
2270
2271 hr_utility.set_location('calculate_period_earnings', 58);
2272 hr_utility.trace('Check for EEVMPE changes nevertheless');
2273
2274 select 1 INTO l_eev_info_changes
2275 from dual
2276 where exists (
2277 SELECT 1
2278 FROM pay_element_entry_values_f EEV
2279 WHERE EEV.element_entry_id = p_ele_entry_id
2280 AND EEV.input_value_id+0 = v_inpval_id
2281 AND EEV.effective_start_date <= p_period_start
2282 AND EEV.effective_end_date >= p_period_start
2283 AND EEV.effective_end_date < p_period_end);
2284
2285 hr_utility.trace('Proration due to EEVMPE changes');
2286
2287
2288 EXCEPTION
2289
2290 WHEN NO_DATA_FOUND THEN
2291
2292 hr_utility.trace('ASG AND EEV changes DO NOT EXIST EXCEPT ');
2293
2294
2295 /* Bug 10063757 Check to see if we want to confirm the Assignment Status is
2296 Active Assignmnet prior to continuing. We check the pay_action_parameter
2297 'PROC_REG_SAL_INACT'. IF the action parameter is not there or NOT EQUAL to
2298 'Y' we will drop into the code the check if the Assignment_Status =
2299 ACTIVE_ASSIGNMENT prior to continuing processing
2300 */
2301 l_proc_reg_sal := 'N';
2302
2303 BEGIN
2304 SELECT parameter_value
2305 INTO l_proc_reg_sal
2306 FROM pay_action_parameters
2307 WHERE parameter_name = 'PROC_REG_SAL_INACT';
2308
2309
2310 IF ( upper(l_proc_reg_sal) = 'Y'
2311 OR upper(l_proc_reg_sal) = 'YES' ) THEN
2312 l_proc_reg_sal := 'Y';
2313 ELSE
2314 l_proc_reg_sal := 'N';
2315 END IF;
2316 EXCEPTION
2317 WHEN OTHERS THEN
2318 l_proc_reg_sal := 'N';
2319 END;
2320
2321
2322
2323 hr_utility.trace(' Check assignment status of current asg record');
2324
2325 IF l_proc_reg_sal <> 'Y' THEN
2326
2327
2328 SELECT AST.per_system_status
2329 INTO v_asg_status
2330 FROM per_assignments_f ASG,
2331 per_assignment_status_types AST,
2332 hr_soft_coding_keyflex SCL
2333 WHERE ASG.assignment_id = p_asst_id
2334 AND p_period_start BETWEEN ASG.effective_start_date
2335 AND ASG.effective_end_date
2336 AND AST.assignment_status_type_id = ASG.assignment_status_type_id
2337 AND SCL.soft_coding_keyflex_id = ASG.soft_coding_keyflex_id
2338 AND SCL.segment1 = TO_CHAR(p_tax_unit_id)
2339 AND SCL.enabled_flag = 'Y';
2340
2341 IF v_asg_status <> 'ACTIVE_ASSIGN' THEN
2342
2343 hr_utility.trace(' Asg not active');
2344 v_period_earn := 0;
2345 p_actual_hours_worked := 0;
2346
2347 END IF;
2348
2349 END IF;
2350
2351 hr_utility.trace('Chk for vac pay since no ASG EEV changes to prorate' );
2352
2353 p_vac_pay := vacation_pay(p_vac_hours => p_vac_hours_worked,
2354 p_asg_id => p_asst_id,
2355 p_eff_date => p_period_end,
2356 p_curr_rate => p_ass_hrly_figure);
2357
2358 hr_utility.trace('p_vac_pay ='||p_vac_pay);
2359 p_sick_pay := sick_pay( p_sick_hours => p_sick_hours_worked,
2360 p_asg_id => p_asst_id,
2361 p_eff_date => p_period_end,
2362 p_curr_rate => p_ass_hrly_figure);
2363
2364
2365 hr_utility.trace('p_sick_pay ='||p_sick_pay);
2366
2367 p_actual_hours_worked := ROUND(p_actual_hours_worked, 3);
2368 RETURN v_period_earn;
2369
2370 END; /* Check for EEVMPE changes */
2371
2372 END IF; /* ASGMPE=0 ASGMPS =0 */
2373
2374 /* **************************************************************
2375 If code reaches here, then we're prorating for one reason or the other.
2376 ***************************************************************** */
2377
2378
2379 IF (l_asg_info_changes > 0) AND (l_eev_info_changes = 0) THEN /*ASG =1 EEV =0*/
2380
2381
2382 /* ************** ONLY ASG CHANGES START **** */
2383
2384 p_actual_hours_worked := 0;
2385 hr_utility.set_location('calculate_period_earnings', 70);
2386 hr_utility.trace('UDFS ONLY ASG CHANGES START');
2387
2388 BEGIN /* Get Asg Details ASGMPE */
2389
2390 hr_utility.trace('Get Asg details - ASGMPE');
2391 hr_utility.set_location('calculate_period_earnings', 71);
2392
2393 SELECT GREATEST(ASG.effective_start_date, p_period_start),
2394 ASG.effective_end_date,
2395 NVL(ASG.NORMAL_HOURS, 0),
2396 NVL(HRL.meaning, 'NOT ENTERED'),
2397 NVL(SCL.segment4, 'NOT ENTERED')
2398 INTO v_range_start,
2399 v_range_end,
2400 v_asst_std_hrs,
2401 v_asst_std_freq,
2402 v_work_schedule
2403 FROM per_assignments_f ASG,
2404 per_assignment_status_types AST,
2405 hr_soft_coding_keyflex SCL,
2406 hr_lookups HRL
2407 WHERE ASG.assignment_id = p_asst_id
2408 AND ASG.business_group_id + 0 = p_bus_grp_id
2409 AND ASG.effective_start_date <= p_period_start
2410 AND ASG.effective_end_date >= p_period_start
2411 AND ASG.effective_end_date < p_period_end
2412 AND AST.assignment_status_type_id = ASG.assignment_status_type_id
2413 AND AST.per_system_status = 'ACTIVE_ASSIGN'
2414 AND SCL.soft_coding_keyflex_id = ASG.soft_coding_keyflex_id
2418 AND HRL.lookup_type(+) = 'FREQUENCY';
2415 AND SCL.segment1 = TO_CHAR(p_tax_unit_id)
2416 AND SCL.enabled_flag = 'Y'
2417 AND HRL.lookup_code(+) = ASG.frequency
2419
2420
2421 hr_utility.trace('If ASGMPE Details succ. then Calling Prorate_Earnings');
2422 hr_utility.set_location('calculate_period_earnings', 72);
2423 v_prorated_earnings := v_prorated_earnings +
2424 Prorate_Earnings (
2425 p_bg_id => p_bus_grp_id,
2426 p_asg_hrly_rate => p_ass_hrly_figure,
2427 p_range_start_date => v_range_start,
2428 p_range_end_date => v_range_end,
2429 p_act_hrs_worked => p_actual_hours_worked);
2430
2431 hr_utility.trace('After Calling Prorate_Earnings');
2432
2433 EXCEPTION WHEN NO_DATA_FOUND THEN
2434
2435 NULL;
2436
2437 END; /* Get Asg Details */
2438
2439
2440 hr_utility.trace('ONLY ASG , select MULTIASG');
2441 hr_utility.set_location('calculate_period_earnings', 77);
2442
2443 OPEN get_asst_chgs; -- SELECT (ASG2 MULTIASG)
2444 LOOP
2445
2446 FETCH get_asst_chgs
2447 INTO v_range_start,
2448 v_range_end,
2449 v_asst_std_hrs,
2450 v_asst_std_freq,
2451 v_work_schedule;
2452 EXIT WHEN get_asst_chgs%NOTFOUND;
2453 hr_utility.set_location('calculate_period_earnings', 79);
2454
2455
2456 hr_utility.trace('ONLY ASG Calling Prorate_Earning as MULTIASG successful');
2457
2458 v_prorated_earnings := v_prorated_earnings +
2459 Prorate_Earnings (
2460 p_bg_id => p_bus_grp_id,
2461 p_asg_hrly_rate => p_ass_hrly_figure,
2462 p_range_start_date => v_range_start,
2463 p_range_end_date => v_range_end,
2464 p_act_hrs_worked => p_actual_hours_worked);
2465
2466
2467 hr_utility.trace('After calling Prorate_Earnings from MULTIASG');
2468
2469 END LOOP;
2470
2471 CLOSE get_asst_chgs;
2472
2473 BEGIN /* END_SPAN_RECORD */
2474
2475 hr_utility.set_location('calculate_period_earnings', 89);
2476 hr_utility.trace('ONLY ASG , select END_SPAN_RECORD');
2477
2478 SELECT ASG.effective_start_date,
2479 LEAST(ASG.effective_end_date, p_period_end),
2480 NVL(ASG.normal_hours, 0),
2481 NVL(HRL.meaning, 'NOT ENTERED'),
2482 NVL(SCL.segment4, 'NOT ENTERED')
2483 INTO v_range_start,
2484 v_range_end,
2485 v_asst_std_hrs,
2486 v_asst_std_freq,
2487 v_work_schedule
2488 FROM hr_soft_coding_keyflex SCL,
2489 per_assignment_status_types AST,
2490 per_assignments_f ASG,
2491 hr_lookups HRL
2492 WHERE ASG.assignment_id = p_asst_id
2493 AND ASG.business_group_id + 0 = p_bus_grp_id
2494 AND ASG.effective_start_date > p_period_start
2495 AND ASG.effective_start_date <= p_period_end
2496 AND ASG.effective_end_date > p_period_end
2497 AND AST.assignment_status_type_id = ASG.assignment_status_type_id
2498 AND AST.per_system_status = 'ACTIVE_ASSIGN'
2499 AND SCL.soft_coding_keyflex_id = ASG.soft_coding_keyflex_id
2500 AND SCL.segment1 = TO_CHAR(p_tax_unit_id)
2501 AND SCL.enabled_flag = 'Y'
2502 AND HRL.lookup_code(+) = ASG.frequency
2503 AND HRL.lookup_type(+) = 'FREQUENCY';
2504
2505 hr_utility.trace('Calling Prorate_Earnings for ONLY ASG END_SPAN_RECORD');
2506 hr_utility.set_location('calculate_period_earnings', 91);
2507 v_prorated_earnings := v_prorated_earnings +
2508 Prorate_Earnings (
2509 p_bg_id => p_bus_grp_id,
2510 p_asg_hrly_rate => p_ass_hrly_figure,
2511 p_range_start_date => v_range_start,
2512 p_range_end_date => v_range_end,
2513 p_act_hrs_worked => p_actual_hours_worked);
2514
2515
2516 hr_utility.trace('Calling Vacation Pay as END_SPAN succ');
2517 hr_utility.set_location('calculate_period_earnings', 101);
2518
2519 p_vac_pay := vacation_pay( p_vac_hours => p_vac_hours_worked,
2520 p_asg_id => p_asst_id,
2521 p_eff_date => p_period_end,
2522 p_curr_rate => p_ass_hrly_figure);
2523
2524 hr_utility.trace('Calling Sick Pay as ASG3 succ');
2525
2526 p_sick_pay := sick_pay( p_sick_hours => p_sick_hours_worked,
2527 p_asg_id => p_asst_id,
2528 p_eff_date => p_period_end,
2529 p_curr_rate => p_ass_hrly_figure);
2530
2531
2532 p_actual_hours_worked := ROUND(p_actual_hours_worked, 3);
2533 RETURN v_prorated_earnings;
2534
2535 EXCEPTION WHEN NO_DATA_FOUND THEN
2536 hr_utility.set_location('calculate_period_earnings', 102);
2537 hr_utility.trace('Exception of ASG_MID_START_LAST_SPAN_END_DT');
2538
2539 p_vac_pay := vacation_pay( p_vac_hours => p_vac_hours_worked,
2540 p_asg_id => p_asst_id,
2541 p_eff_date => p_period_end,
2542 p_curr_rate => p_ass_hrly_figure);
2543
2544 hr_utility.trace('Calling Sick Pay as ASG3 not succ');
2545 p_sick_pay := sick_pay( p_sick_hours => p_sick_hours_worked,
2546 p_asg_id => p_asst_id,
2547 p_eff_date => p_period_end,
2548 p_curr_rate => p_ass_hrly_figure);
2549
2550
2551 p_actual_hours_worked := ROUND(p_actual_hours_worked, 3);
2552 RETURN v_prorated_earnings;
2553
2554 END; /* ASG_MID_START_LAST_SPAN_END_DT */
2555
2556 /* ************** ONLY ASG CHANGES END **** */
2557
2558
2559 ELSIF (l_asg_info_changes = 0) AND (l_eev_info_changes > 0) THEN
2560
2561 /* ******************* ONLY EEV CHANGES START ****** */
2562
2563 hr_utility.trace(' Only EEV changes exist');
2564 hr_utility.set_location('calculate_period_earnings', 103);
2565 p_actual_hours_worked := 0;
2566
2567
2568 hr_utility.trace('Calling Prorate_EEV');
2569
2570 v_prorated_earnings := v_prorated_earnings +
2571 Prorate_EEV (
2572 p_bus_group_id => p_bus_grp_id,
2573 p_pay_id => p_payroll_id,
2574 p_pay_basis => p_pay_basis,
2575 p_hrly_rate => v_curr_hrly_rate,
2576 p_range_start_date => p_period_start,
2577 p_range_end_date => p_period_end,
2578 p_actual_hrs_worked => p_actual_hours_worked,
2579 p_element_entry_id => p_ele_entry_id,
2580 p_inpval_id => v_inpval_id);
2581
2582 hr_utility.trace('After Calling Prorate_EEV');
2583 hr_utility.set_location('calculate_period_earnings', 127);
2584
2585 hr_utility.trace('Calling vacation_pay');
2586
2587 p_vac_pay := vacation_pay( p_vac_hours => p_vac_hours_worked,
2588 p_asg_id => p_asst_id,
2589 p_eff_date => p_period_end,
2590 p_curr_rate => p_ass_hrly_figure);
2591
2592 hr_utility.trace('Calling sick_pay');
2593
2594 p_sick_pay := sick_pay( p_sick_hours => p_sick_hours_worked,
2595 p_asg_id => p_asst_id,
2596 p_eff_date => p_period_end,
2597 p_curr_rate => p_ass_hrly_figure);
2598
2599
2600 p_actual_hours_worked := ROUND(p_actual_hours_worked, 3);
2601 RETURN v_prorated_earnings;
2602
2603 /* ******************* ONLY EEV CHANGES END ****** */
2604
2605 ELSE /*BOTH ASG AND EEV CHANGES =0*/
2606
2607 /* ******************* BOTH ASG AND EEV CHANGES START ************ */
2608
2609
2610 hr_utility.trace('UDFS BOTH ASG and EEV chages exist');
2611
2612
2613 p_actual_hours_worked := 0;
2614
2615
2616 BEGIN /* Latest Screen Entry Value */
2617
2618 hr_utility.trace('BOTH ASG Get latest screen entry value for EEVMPE');
2619 hr_utility.set_location('calculate_period_earnings', 128);
2620
2621 SELECT fnd_number.canonical_to_number(EEV.screen_entry_value)
2622 INTO v_earnings_entry
2623 FROM pay_element_entry_values_f EEV
2624 WHERE EEV.element_entry_id = p_ele_entry_id
2625 AND EEV.input_value_id = v_inpval_id
2626 AND p_period_start between EEV.effective_start_date
2627 AND EEV.effective_end_date;
2628 /*4750302
2629 AND EEV.effective_start_date <= p_period_start
2630 AND EEV.effective_end_date > p_period_start;
2631 */
2632 --AND EEV.effective_end_date < p_period_end
2633
2634 hr_utility.trace('BOTH ASG Get ASGMPE ');
2635
2636 SELECT GREATEST(ASG.effective_start_date, p_period_start),
2637 ASG.effective_end_date,
2638 NVL(ASG.NORMAL_HOURS, 0),
2639 NVL(HRL.meaning, 'NOT ENTERED'),
2640 NVL(SCL.segment4, 'NOT ENTERED')
2641 INTO v_range_start,
2642 v_range_end,
2643 v_asst_std_hrs,
2644 v_asst_std_freq,
2645 v_work_schedule
2646 FROM per_assignments_f ASG,
2647 per_assignment_status_types AST,
2648 hr_soft_coding_keyflex SCL,
2649 hr_lookups HRL
2650 WHERE ASG.assignment_id = p_asst_id
2651 AND ASG.business_group_id + 0 = p_bus_grp_id
2652 AND ASG.effective_start_date <= p_period_start
2653 AND ASG.effective_end_date >= p_period_start
2654 AND ASG.effective_end_date < p_period_end
2655 AND AST.assignment_status_type_id = ASG.assignment_status_type_id
2656 AND AST.per_system_status = 'ACTIVE_ASSIGN'
2657 AND SCL.soft_coding_keyflex_id = ASG.soft_coding_keyflex_id
2658 AND SCL.segment1 = TO_CHAR(p_tax_unit_id)
2659 AND SCL.enabled_flag = 'Y'
2660 AND HRL.lookup_code(+) = ASG.frequency
2661 AND HRL.lookup_type(+) = 'FREQUENCY';
2662
2663 hr_utility.trace('Calling Convert_Period_Type from ASGMPE');
2664 hr_utility.set_location('v_earnings_entry='||v_earnings_entry, 129);
2665
2666 v_curr_hrly_rate := Convert_Period_Type(p_bus_grp_id
2667 ,p_asst_id
2668 ,p_payroll_id
2669 ,p_ele_entry_id
2670 ,p_date_earned
2671 ,p_assignment_action_id
2672 ,p_period_start -- period start date
2673 ,p_period_end -- period end date
2674 ,v_earnings_entry -- p_figure, salary amount
2675 ,v_pay_basis -- p_from freq, salary basis
2676 ,'HOURLY'); -- p_to_freq
2677 /*Convert_Period_Type( p_bus_grp_id,
2678 p_payroll_id,
2679 v_work_schedule,
2680 v_asst_std_hrs,
2681 v_earnings_entry,
2682 v_pay_basis,
2683 'HOURLY',
2684 p_period_start,
2685 p_period_end,
2686 v_asst_std_freq);*/
2687
2688 hr_utility.trace('Select app. EEVMPE again after range is determined');
2689 hr_utility.set_location('calculate_period_earnings', 130);
2690
2691 SELECT COUNT(EEV.element_entry_value_id)
2692 INTO l_eev_info_changes
2693 FROM pay_element_entry_values_f EEV
2694 WHERE EEV.element_entry_id = p_ele_entry_id
2695 AND EEV.input_value_id = v_inpval_id
2696 AND EEV.effective_start_date <= v_range_start
2697 AND EEV.effective_end_date >= v_range_start
2698 AND EEV.effective_end_date < v_range_end;
2699
2700 IF l_eev_info_changes = 0 THEN
2701
2702
2703 hr_utility.trace('NO EEVMPE changes');
2704 hr_utility.set_location('calculate_period_earnings', 132);
2705
2706 SELECT fnd_number.canonical_to_number(EEV.screen_entry_value)
2707 INTO v_earnings_entry
2708 FROM pay_element_entry_values_f EEV
2709 WHERE EEV.element_entry_id = p_ele_entry_id
2710 AND EEV.input_value_id = v_inpval_id
2711 AND v_range_end BETWEEN EEV.effective_start_date
2712 AND EEV.effective_end_date;
2713
2714 hr_utility.trace('Calling Convert_Period_Type');
2715 hr_utility.set_location('calculate_period_earnings', 134);
2716
2717 v_curr_hrly_rate := Convert_Period_Type(p_bus_grp_id
2718 ,p_asst_id
2719 ,p_payroll_id
2720 ,p_ele_entry_id
2721 ,p_date_earned
2722 ,p_assignment_action_id
2723 ,p_period_start -- period start date
2724 ,p_period_end -- period end date
2725 ,v_earnings_entry -- p_figure, salary amount
2726 ,v_pay_basis -- p_from freq, salary basis
2727 ,'HOURLY'); -- p_to_freq
2728 /*Convert_Period_Type( p_bus_grp_id,
2729 p_payroll_id,
2730 v_work_schedule,
2731 v_asst_std_hrs,
2732 v_earnings_entry,
2733 v_pay_basis,
2734 'HOURLY',
2735 p_period_start,
2736 p_period_end,
2737 v_asst_std_freq);*/
2738
2739 hr_utility.trace('Calling Prorate_Earnings');
2740 hr_utility.set_location('calculate_period_earnings', 135);
2741
2742 v_prorated_earnings := v_prorated_earnings +
2743 Prorate_Earnings (
2744 p_bg_id => p_bus_grp_id,
2745 p_asg_hrly_rate => v_curr_hrly_rate,
2746 p_range_start_date => v_range_start,
2747 p_range_end_date => v_range_end,
2748 p_act_hrs_worked => p_actual_hours_worked);
2749
2750 hr_utility.set_location('calculate_period_earnings', 137);
2751
2752 ELSE
2753 -- Do proration for this ASG range by EEV !
2754
2755 hr_utility.trace('EEVMPE True');
2756 hr_utility.trace('Do proration for this ASG range by EEV');
2757 hr_utility.set_location('calculate_period_earnings', 139);
2758
2759 hr_utility.trace('Calling Prorate_EEV');
2760
2761 v_prorated_earnings := v_prorated_earnings +
2762 Prorate_EEV (
2763 p_bus_group_id => p_bus_grp_id,
2764 p_pay_id => p_payroll_id,
2765 p_pay_basis => v_pay_basis,
2766 p_hrly_rate => v_curr_hrly_rate,
2767 p_range_start_date => v_range_start,
2768 p_range_end_date => v_range_end,
2769 p_actual_hrs_worked => p_actual_hours_worked,
2770 p_element_entry_id => p_ele_entry_id,
2771 p_inpval_id => v_inpval_id);
2772 hr_utility.set_location('calculate_period_earnings', 140);
2773
2774 END IF; -- EEV info changes
2775
2776 EXCEPTION WHEN NO_DATA_FOUND THEN
2777 NULL;
2778
2779 END; /* Latest Screen Entry Value */
2780
2781 hr_utility.trace(' BOTH ASG - SELECT ASG_MULTI_WITHIN');
2782 hr_utility.set_location('calculate_period_earnings', 141);
2783
2784 OPEN get_asst_chgs; -- SELECT ( ASG_MULTI_WITHIN)
2785 LOOP
2786
2787 FETCH get_asst_chgs
2788 INTO v_range_start,
2789 v_range_end,
2790 v_asst_std_hrs,
2791 v_asst_std_freq,
2792 v_work_schedule;
2793 EXIT WHEN get_asst_chgs%NOTFOUND;
2794
2795 --EEV_BEFORE_RANGE_END
2796 hr_utility.trace('BOTH ASG MULTI select app. EEVMPE again after range det.');
2797 hr_utility.set_location('calculate_period_earnings', 145);
2798
2799 SELECT COUNT(EEV.element_entry_value_id)
2800 INTO l_eev_info_changes
2801 FROM pay_element_entry_values_f EEV
2802 WHERE EEV.element_entry_id = p_ele_entry_id
2803 AND EEV.input_value_id = v_inpval_id
2804 AND EEV.effective_start_date <= v_range_start
2805 AND EEV.effective_end_date >= v_range_start
2806 AND EEV.effective_end_date < v_range_end;
2807
2808 IF l_eev_info_changes = 0 THEN /* IF l_eev_info_changes = 0 */
2809
2810 -- EEV_FOR_CURR_RANGE_END
2811
2812 hr_utility.trace('BOTH ASG - EEV false');
2813 SELECT fnd_number.canonical_to_number(EEV.screen_entry_value)
2814 INTO v_earnings_entry
2815 FROM pay_element_entry_values_f EEV
2816 WHERE EEV.element_entry_id = p_ele_entry_id
2817 AND EEV.input_value_id = v_inpval_id
2818 AND v_range_end BETWEEN EEV.effective_start_date
2819 AND EEV.effective_end_date;
2820 hr_utility.set_location('calculate_period_earnings', 150);
2821 v_curr_hrly_rate := Convert_Period_Type(p_bus_grp_id
2822 ,p_asst_id
2823 ,p_payroll_id
2824 ,p_ele_entry_id
2825 ,p_date_earned
2826 ,p_assignment_action_id
2827 ,p_period_start -- period start date
2828 ,p_period_end -- period end date
2829 ,v_earnings_entry -- p_figure, salary amount
2830 ,v_pay_basis -- p_from freq, salary basis
2831 ,'HOURLY'); -- p_to_freq
2832 /*Convert_Period_Type( p_bus_grp_id,
2833 p_payroll_id,
2834 v_work_schedule,
2835 v_asst_std_hrs,
2836 v_earnings_entry,
2837 v_pay_basis,
2838 'HOURLY',
2839 p_period_start,
2840 p_period_end,
2841 v_asst_std_freq);*/
2842
2843 v_prorated_earnings := v_prorated_earnings +
2844 Prorate_Earnings (
2845 p_bg_id => p_bus_grp_id,
2846 p_asg_hrly_rate => v_curr_hrly_rate,
2847 p_range_start_date => v_range_start,
2848 p_range_end_date => v_range_end,
2849 p_act_hrs_worked => p_actual_hours_worked);
2850
2851 hr_utility.set_location('calculate_period_earnings', 155);
2852 ELSE
2853 hr_utility.trace('BOTH ASG - EEV true');
2854 v_prorated_earnings := v_prorated_earnings +
2855 Prorate_EEV (
2856 p_bus_group_id => p_bus_grp_id,
2857 p_pay_id => p_payroll_id,
2858 p_pay_basis => v_pay_basis,
2859 p_hrly_rate => v_curr_hrly_rate,
2860 p_range_start_date => v_range_start,
2861 p_range_end_date => v_range_end,
2862 p_actual_hrs_worked => p_actual_hours_worked,
2863 p_element_entry_id => p_ele_entry_id,
2864 p_inpval_id => v_inpval_id);
2865
2866 END IF; /* IF l_eev_info_changes = 0 */
2867
2868 END LOOP;
2869
2870 CLOSE get_asst_chgs;
2871
2872
2873 BEGIN /* SPAN_RECORD */
2874
2875 hr_utility.trace('BOTH ASG SELECT END_SPAN_RECORD');
2876 hr_utility.set_location('calculate_period_earnings', 160);
2877
2878 SELECT ASG.effective_start_date,
2879 LEAST(ASG.effective_end_date, p_period_end),
2880 NVL(ASG.normal_hours, 0),
2881 NVL(HRL.meaning, 'NOT ENTERED'),
2882 NVL(SCL.segment4, 'NOT ENTERED')
2883 INTO v_range_start,
2884 v_range_end,
2885 v_asst_std_hrs,
2886 v_asst_std_freq,
2887 v_work_schedule
2888 FROM hr_soft_coding_keyflex SCL,
2889 per_assignment_status_types AST,
2890 per_assignments_f ASG,
2891 hr_lookups HRL
2892 WHERE ASG.assignment_id = p_asst_id
2893 AND ASG.business_group_id + 0 = p_bus_grp_id
2894 AND ASG.effective_start_date > p_period_start
2895 AND ASG.effective_start_date <= p_period_end
2896 AND ASG.effective_end_date > p_period_end
2897 AND AST.assignment_status_type_id = ASG.assignment_status_type_id
2898 AND AST.per_system_status = 'ACTIVE_ASSIGN'
2899 AND SCL.soft_coding_keyflex_id = ASG.soft_coding_keyflex_id
2900 AND SCL.segment1 = TO_CHAR(p_tax_unit_id)
2901 AND SCL.enabled_flag = 'Y'
2902 AND HRL.lookup_code(+) = ASG.frequency
2903 AND HRL.lookup_type(+) = 'FREQUENCY';
2904
2905
2906
2907 hr_utility.trace('SELECT EEVMPE');
2908
2909 SELECT COUNT(EEV.element_entry_value_id)
2910 INTO l_eev_info_changes
2911 FROM pay_element_entry_values_f EEV
2912 WHERE EEV.element_entry_id = p_ele_entry_id
2913 AND EEV.input_value_id = v_inpval_id
2914 AND EEV.effective_start_date <= v_range_start
2915 AND EEV.effective_end_date >= v_range_start
2916 AND EEV.effective_end_date < v_range_end;
2917
2918 IF l_eev_info_changes = 0 THEN
2919
2920 hr_utility.trace('BOTH ASG SPAN - SELECT EEV_FOR_CURR_RANGE_END');
2921 hr_utility.set_location('calculate_period_earnings', 165);
2922
2923 SELECT fnd_number.canonical_to_number(EEV.screen_entry_value)
2924 INTO v_earnings_entry
2925 FROM pay_element_entry_values_f EEV
2926 WHERE EEV.element_entry_id = p_ele_entry_id
2927 AND EEV.input_value_id = v_inpval_id
2928 AND v_range_end BETWEEN EEV.effective_start_date
2929 AND EEV.effective_end_date;
2930
2931 v_curr_hrly_rate := Convert_Period_Type(p_bus_grp_id
2932 ,p_asst_id
2933 ,p_payroll_id
2934 ,p_ele_entry_id
2935 ,p_date_earned
2936 ,p_assignment_action_id
2937 ,p_period_start -- period start date
2938 ,p_period_end -- period end date
2939 ,v_earnings_entry -- p_figure, salary amount
2940 ,v_pay_basis -- p_from freq, salary basis
2941 ,'HOURLY'); -- p_to_freq
2942 /*Convert_Period_Type( p_bus_grp_id,
2943 p_payroll_id,
2944 p_work_schedule,
2945 p_asst_std_hrs,
2946 v_earnings_entry,
2947 v_pay_basis,
2948 'HOURLY',
2949 p_period_start,
2950 p_period_end,
2951 v_asst_std_freq);*/
2952
2953 v_prorated_earnings := v_prorated_earnings +
2954 Prorate_Earnings (
2955 p_bg_id => p_bus_grp_id,
2956 p_asg_hrly_rate => v_curr_hrly_rate,
2957 p_range_start_date => v_range_start,
2958 p_range_end_date => v_range_end,
2959 p_act_hrs_worked => p_actual_hours_worked);
2960
2961 hr_utility.set_location('calculate_period_earnings', 170);
2962 ELSE /* EEV succ */
2963
2964 hr_utility.trace('BOTH ASG END_SPAN - EEV true');
2965 v_prorated_earnings := v_prorated_earnings +
2966 Prorate_EEV (
2967 p_bus_group_id => p_bus_grp_id,
2968 p_pay_id => p_payroll_id,
2969 p_pay_basis => v_pay_basis,
2970 p_hrly_rate => v_curr_hrly_rate,
2971 p_range_start_date => v_range_start,
2972 p_range_end_date => v_range_end,
2973 p_actual_hrs_worked => p_actual_hours_worked,
2974 p_element_entry_id => p_ele_entry_id,
2975 p_inpval_id => v_inpval_id);
2976 hr_utility.set_location('calculate_period_earnings', 175);
2977 END IF;
2978
2979
2980 p_vac_pay := vacation_pay( p_vac_hours => p_vac_hours_worked,
2981 p_asg_id => p_asst_id,
2982 p_eff_date => p_period_end,
2983 p_curr_rate => p_ass_hrly_figure);
2984 hr_utility.set_location('calculate_period_earnings', 180);
2985
2986 p_sick_pay := sick_pay( p_sick_hours => p_sick_hours_worked,
2987 p_asg_id => p_asst_id,
2988 p_eff_date => p_period_end,
2989 p_curr_rate => p_ass_hrly_figure);
2990 hr_utility.set_location('calculate_period_earnings', 185);
2991
2992 p_actual_hours_worked := ROUND(p_actual_hours_worked, 3);
2993 RETURN v_prorated_earnings;
2994
2995 EXCEPTION WHEN NO_DATA_FOUND THEN
2996
2997 p_vac_pay := vacation_pay( p_vac_hours => p_vac_hours_worked,
2998 p_asg_id => p_asst_id,
2999 p_eff_date => p_period_end,
3000 p_curr_rate => p_ass_hrly_figure);
3001
3002 p_sick_pay := sick_pay( p_sick_hours => p_sick_hours_worked,
3003 p_asg_id => p_asst_id,
3004 p_eff_date => p_period_end,
3005 p_curr_rate => p_ass_hrly_figure);
3006
3007 p_actual_hours_worked := ROUND(p_actual_hours_worked, 3);
3008 RETURN v_prorated_earnings;
3009
3010 END;
3011
3012
3013 /* ******************* BOTH ASG AND EEV CHANGES ENDS ************ */
3014
3015 END IF; /*END IF OF BOTH ASG AND EEV CHANGES */
3016
3017 EXCEPTION
3018 WHEN NO_DATA_FOUND THEN
3019
3020 p_vac_pay := vacation_pay( p_vac_hours => p_vac_hours_worked,
3021 p_asg_id => p_asst_id,
3022 p_eff_date => p_period_end,
3023 p_curr_rate => p_ass_hrly_figure);
3024
3025 p_sick_pay := sick_pay( p_sick_hours => p_sick_hours_worked,
3026 p_asg_id => p_asst_id,
3030
3027 p_eff_date => p_period_end,
3028 p_curr_rate => p_ass_hrly_figure);
3029
3031 p_actual_hours_worked := ROUND(p_actual_hours_worked, 3);
3032
3033 RETURN v_prorated_earnings;
3034
3035 END Calculate_Period_Earnings;
3036
3037 -- **********************************************************************
3038
3039 -- **********************************************************************
3040 -- converts the amount from one salary basis to another e.g. montly to hourly
3041
3042 -- Calculates hourly rate
3043 FUNCTION get_hourly_rate(
3044 p_bg IN NUMBER -- context
3045 ,p_assignment_id IN NUMBER -- context
3046 ,p_payroll_id IN NUMBER -- context
3047 ,p_element_entry_id IN NUMBER -- context
3048 ,p_date_earned IN DATE -- context
3049 ,p_assignment_action_id IN NUMBER )-- context
3050 RETURN NUMBER IS
3051
3052 CURSOR get_period_dates (l_date_earned date,
3053 l_payroll_id number) IS
3054 select start_date, end_date
3055 from per_time_periods pt
3056 where payroll_id = l_payroll_id
3057 and l_date_earned between start_date and end_date;
3058
3059 CURSOR get_salary_basis(l_date_earned date,
3060 l_assignment_id number) IS
3061 /* using lookup_code to avoid the translation issue*/
3062
3063 select /*hr_general.decode_lookup('PAY_BASIS',BASES.pay_basis)*/
3064 BASES.pay_basis
3065 , INPUTV.input_value_id
3066 from
3067 per_all_assignments_f ASSIGN
3068 , per_pay_bases BASES
3069 , pay_input_values_f INPUTV
3070 , pay_element_types_f ETYPE
3071 , pay_rates RATE
3072 where l_date_earned BETWEEN ASSIGN.effective_start_date
3073 AND ASSIGN.effective_end_date
3074 and ASSIGN.assignment_id = l_assignment_id
3075 and BASES.pay_basis_id (+)= ASSIGN.pay_basis_id
3076 and INPUTV.input_value_id (+)= BASES.input_value_id
3077 and l_date_earned between nvl (INPUTV.effective_start_date, l_date_earned)
3078 and nvl (INPUTV.effective_end_date, l_date_earned)
3079 and ETYPE.element_type_id (+)= INPUTV.element_type_id
3080 and RATE.rate_id (+)= BASES.rate_id
3081 and l_date_earned between nvl (ETYPE.effective_start_date, l_date_earned)
3082 and nvl (ETYPE.effective_end_date, l_date_earned) ;
3083
3084 CURSOR get_salary (l_date_earned date,
3085 l_assignment_id number,
3086 l_input_value_id number) IS
3087 select fnd_number.canonical_to_number (EEV.screen_entry_value)
3088 from pay_element_entries_f EE
3089 , pay_element_entry_values_f EEV
3090 where EEV.input_value_id = l_input_value_id
3091 and l_date_earned BETWEEN EEV.effective_start_date
3092 AND EEV.effective_end_date
3093 and EE.assignment_id = l_assignment_id
3094 and EE.entry_type = 'E'
3095 and l_date_earned BETWEEN EE.effective_start_date
3096 AND EE.effective_end_date
3097 and EEV.element_entry_id = EE.element_entry_id;
3098
3099
3100 CURSOR get_termination_date(l_date_earned date,
3101 l_assignment_id number) IS
3102 select actual_termination_date
3103 from per_assignments_f paf,
3104 per_periods_of_service pps
3105 where paf.assignment_id = l_assignment_id
3106 and l_date_earned between paf.effective_start_date and
3107 paf.effective_end_date
3108 and paf.PERIOD_OF_SERVICE_ID = pps.period_of_service_id;
3109
3110 l_period_start_date date;
3111 l_period_end_date date;
3112 l_salary_basis VARCHAR2(200);
3113 l_input_value_id NUMBER;
3114 l_asg_salary NUMBER;
3115 l_hourly_rate NUMBER;
3116 l_date_used date;
3117 l_termination_date date;
3118
3119
3120 BEGIN
3121
3122 hr_utility.trace(' Entered get_hourly_rate ');
3123 --hr_utility.trace_on(null,'wrkschd');
3124
3125
3126 hr_utility.trace('assignment_action_id=' || p_assignment_action_id);
3127 hr_utility.trace('assignment_id=' || p_assignment_id);
3128 hr_utility.trace('business_group_id=' || p_bg);
3129 hr_utility.trace('element_entry_id=' || p_element_entry_id);
3130 hr_utility.trace('p_date_earned '||p_date_earned);
3131 hr_utility.trace('p_payroll_id: '||p_payroll_id);
3132
3133 l_hourly_rate := 0;
3134
3135 OPEN get_period_dates(p_date_earned,p_payroll_id);
3136 FETCH get_period_dates INTO l_period_start_date,l_period_end_date;
3137 CLOSE get_period_dates;
3138
3139
3140 hr_utility.trace('l_period_start_date =' || l_period_start_date);
3141 hr_utility.trace('l_period_end_date =' || l_period_end_date);
3142
3143 OPEN get_salary_basis(p_date_earned, p_assignment_id);
3144 FETCH get_salary_basis INTO l_salary_basis, l_input_value_id;
3145 CLOSE get_salary_basis;
3146
3147 hr_utility.trace('l_salary_basis =' || l_salary_basis);
3148 hr_utility.trace('l_input_value_id =' || l_input_value_id);
3149
3150 OPEN get_salary(p_date_earned, p_assignment_id,l_input_value_id);
3151 FETCH get_salary INTO l_asg_salary;
3152 CLOSE get_salary;
3153
3154 IF l_asg_salary IS NULL THEN
3155
3159
3156 OPEN get_termination_date(p_date_earned, p_assignment_id);
3157 FETCH get_termination_date INTO l_termination_date;
3158 CLOSE get_termination_date;
3160 hr_utility.trace('l_termination_date =' || l_termination_date);
3161
3162 OPEN get_salary(l_termination_date,
3163 p_assignment_id,l_input_value_id);
3164 FETCH get_salary INTO l_asg_salary;
3165 CLOSE get_salary;
3166 l_date_used := nvl(l_termination_date,p_date_earned);
3167 END IF;
3168
3169 hr_utility.trace('l_asg_salary =' || l_asg_salary);
3170
3171 l_hourly_rate := Convert_Period_Type(p_bg
3172 ,p_assignment_id
3173 ,p_payroll_id
3174 ,p_element_entry_id
3175 ,p_date_earned
3176 ,p_assignment_action_id
3177 ,l_period_start_date -- period start date
3178 ,l_period_end_date -- period end date
3179 ,l_asg_salary -- p_figure, salary amount
3180 ,l_salary_basis -- p_from freq, salary basis
3181 ,'HOURLY'); -- p_to_freq
3182
3183 return l_hourly_rate;
3184
3185 END get_hourly_rate;
3186
3187
3188 FUNCTION standard_hours_worked(
3189 p_std_hrs in NUMBER,
3190 p_range_start in DATE,
3191 p_range_end in DATE,
3192 p_std_freq in VARCHAR2) RETURN NUMBER IS
3193
3194 c_wkdays_per_week NUMBER(5,2) ;
3195 c_wkdays_per_month NUMBER(5,2) ;
3196 c_wkdays_per_year NUMBER(5,2) ;
3197
3198 /* 353434, 368242 : Fixed number width for total hours */
3199 v_total_hours NUMBER(15,7) ;
3200 v_wrkday_hours NUMBER(15,7) ; -- std hrs/wk divided by 5 workdays/wk
3201 v_curr_date DATE;
3202 v_curr_day VARCHAR2(3); -- 3 char abbrev for day of wk.
3203 v_day_no NUMBER;
3204
3205 BEGIN -- standard_hours_worked
3206
3207 /* Init */
3208 c_wkdays_per_week := 5;
3209 c_wkdays_per_month := 20;
3210 c_wkdays_per_year := 250;
3211 v_total_hours := 0;
3212 v_wrkday_hours :=0;
3213 v_curr_date := NULL;
3214 v_curr_day :=NULL;
3215
3216 -- Check for valid range
3217 hr_utility.trace('Entered standard_hours_worked');
3218
3219 IF p_range_start > p_range_end THEN
3220 hr_utility.trace('p_range_start greater than p_range_end');
3221 RETURN v_total_hours;
3222 -- hr_utility.set_message(801,'PAY_xxxx_INVALID_DATE_RANGE');
3223 -- hr_utility.raise_error;
3224 END IF;
3225 --
3226
3227 IF UPPER(p_std_freq) = 'WEEK' THEN
3228 hr_utility.trace('p_std_freq = WEEK ');
3229
3230 v_wrkday_hours := p_std_hrs / c_wkdays_per_week;
3231
3232 hr_utility.trace('p_std_hrs ='||to_number(p_std_hrs));
3233 hr_utility.trace('c_wkdays_per_week ='||to_number(c_wkdays_per_week));
3234 hr_utility.trace('v_wrkday_hours ='||to_number(v_wrkday_hours));
3235
3236 ELSIF UPPER(p_std_freq) = 'MONTH' THEN
3237
3238 hr_utility.trace('p_std_freq = MONTH ');
3239
3240 v_wrkday_hours := p_std_hrs / c_wkdays_per_month;
3241
3242
3243 hr_utility.trace('p_std_hrs ='||to_number(p_std_hrs));
3244 hr_utility.trace('c_wkdays_per_month ='||to_number(c_wkdays_per_month));
3245 hr_utility.trace('v_wrkday_hours ='||to_number(v_wrkday_hours));
3246
3247 ELSIF UPPER(p_std_freq) = 'YEAR' THEN
3248
3249 hr_utility.trace('p_std_freq = YEAR ');
3250 v_wrkday_hours := p_std_hrs / c_wkdays_per_year;
3251
3252 hr_utility.trace('p_std_hrs ='||to_number(p_std_hrs));
3253 hr_utility.trace('c_wkdays_per_year ='||to_number(c_wkdays_per_year));
3254 hr_utility.trace('v_wrkday_hours ='||to_number(v_wrkday_hours));
3255
3256 ELSE
3257 hr_utility.trace('p_std_freq in ELSE ');
3258 v_wrkday_hours := p_std_hrs;
3259 END IF;
3260
3261 v_curr_date := p_range_start;
3262
3263 hr_utility.trace('v_curr_date is range start'||to_char(v_curr_date));
3264
3265
3266 LOOP
3267
3268 v_day_no := TO_CHAR(v_curr_date, 'D');
3269
3270
3271 IF v_day_no > 1 and v_day_no < 7 then
3272
3273
3274 v_total_hours := nvl(v_total_hours,0) + v_wrkday_hours;
3275
3276 hr_utility.trace(' v_day_no = '||to_char(v_day_no));
3277 hr_utility.trace(' v_total_hours = '||to_char(v_total_hours));
3278 END IF;
3279
3280 v_curr_date := v_curr_date + 1;
3281 EXIT WHEN v_curr_date > p_range_end;
3282 END LOOP;
3283 hr_utility.trace(' Final v_total_hours = '||to_char(v_total_hours));
3284 hr_utility.trace(' Leaving standard_hours_worked' );
3285 --
3286 RETURN v_total_hours;
3287 --
3288 END standard_hours_worked;
3289
3290 -- +-------------------------------------------------------------------------+
3291 -- |-----------------< good_time_format >-------------------------|
3292 -- +-------------------------------------------------------------------------+
3293 -- Description:
3294 -- Tests CHAR values for valid time.
3295 --
3296 -- Pre-conditions:
3297 -- None.
3298 --
3299 -- In Arguments:
3300 -- p_time VARCHAR2
3301 --
3302 -- Out Arguments:
3303 -- BOOLEAN
3304 --
3305 -- Post Success:
3306 -- Returns TRUE or FALSE depending on valid time or not.
3307 --
3308 -- Post Failure:
3309 -- Returns FALSE for invalid time.
3310 --
3311 -- Access Status:
3312 -- Internal Development Use Only.
3313 --
3314 -- {End Of Comments}
3315 -- ----------------------------------------------------------------------------
3316 --
3317 FUNCTION good_time_format ( p_time IN VARCHAR2 ) RETURN BOOLEAN IS
3318 --
3322 --
3319 BEGIN
3320 --
3321 IF p_time IS NOT NULL THEN
3323 IF NOT (SUBSTR(p_time,1,2) BETWEEN '00' AND '23' AND
3324 SUBSTR(p_time,4,2) BETWEEN '00' AND '59' AND
3325 SUBSTR(p_time,3,1) = ':' AND
3326 LENGTH(p_time) = 5) THEN
3327 RETURN FALSE;
3328 ELSE
3329 RETURN TRUE;
3330 END IF;
3331 --
3332 ELSE
3333 RETURN FALSE;
3334 END IF;
3335 --
3336 EXCEPTION
3337 --
3338 WHEN OTHERS THEN
3339 RETURN FALSE;
3340 --
3341 END good_time_format;
3342 --
3343
3344 --
3345 -- +-------------------------------------------------------------------------+
3346 -- |-----------------< calc_sch_based_dur >-------------------------|
3347 -- +-------------------------------------------------------------------------+
3348 -- Description:
3349 -- Calculate the duration in hours/days based on the work schedule.
3350 --
3351 -- Pre-conditions:
3352 -- None.
3353 --
3354 -- In Arguments:
3355 -- p_days_or_hours VARCHAR2
3356 -- p_date_start DATE
3357 -- p_date_end DATE
3358 -- p_time_start VARCHAR2
3359 -- p_time_end VARCHAR2
3360 -- p_assignment_id NUMBER
3361 --
3362 -- Out Arguments:
3363 -- p_duration NUMBER
3364 --
3365 -- Post Success:
3366 -- Value returned for duration.
3367 --
3368 -- Post Failure:
3369 -- If a failure occurs, an application error is raised and
3370 -- processing terminates.
3371 --
3372 -- Access Status:
3373 -- Internal Development Use Only.
3374 --
3375 -- {End Of Comments}
3376 -- ----------------------------------------------------------------------------
3377 --
3378 PROCEDURE calc_sch_based_dur ( p_days_or_hours IN VARCHAR2,
3379 p_date_start IN DATE,
3380 p_date_end IN DATE,
3381 p_time_start IN VARCHAR2,
3382 p_time_end IN VARCHAR2,
3383 p_assignment_id IN NUMBER,
3384 p_duration IN OUT NOCOPY NUMBER
3385 ) IS
3386 --
3387 p_start_duration NUMBER;
3388 p_end_duration NUMBER;
3389 l_idx NUMBER;
3390 l_ref_date DATE;
3391 l_first_band BOOLEAN;
3392 l_day_start_time VARCHAR2(5);
3393 l_day_end_time VARCHAR2(5);
3394 l_start_time VARCHAR2(5);
3395 l_end_time VARCHAR2(5);
3396 --
3397 l_start_date DATE;
3398 l_end_date DATE;
3399 l_schedule cac_avlblty_time_varray;
3400 l_schedule_source VARCHAR2(10);
3401 l_return_status VARCHAR2(1);
3402 l_return_message VARCHAR2(2000);
3403 --
3404 l_time_start VARCHAR2(5);
3405 l_time_end VARCHAR2(5);
3406 --
3407 e_bad_time_format EXCEPTION;
3408 --
3409 BEGIN
3410 hr_utility.set_location('Entering '||g_package||'.calc_sch_based_dur',10);
3411 p_duration := 0;
3412 l_time_start := p_time_start;
3413 l_time_end := p_time_end;
3414 --
3415 IF l_time_start IS NULL THEN
3416 l_time_start := '00:00';
3417 ELSE
3418 IF NOT good_time_format(l_time_start) THEN
3419 RAISE e_bad_time_format;
3420 END IF;
3421 END IF;
3422 IF l_time_end IS NULL THEN
3423 l_time_end := '00:00';
3424 ELSE
3425 IF NOT good_time_format(l_time_end) THEN
3426 RAISE e_bad_time_format;
3427 END IF;
3428 END IF;
3429 IF p_days_or_hours = 'D' THEN
3430 l_time_end := '23:59';
3431 END IF;
3432 l_start_date := TO_DATE(TO_CHAR(p_date_start,'DD-MM-YYYY')||' '||l_time_start,'DD-MM-YYYY HH24:MI');
3433 l_end_date := TO_DATE(TO_CHAR(p_date_end,'DD-MM-YYYY')||' '||l_time_end,'DD-MM-YYYY HH24:MI');
3434
3435 hr_utility.trace('p_assignment_id ' ||p_assignment_id);
3436 hr_utility.trace('l_start_date ' ||l_start_date);
3437 hr_utility.trace('l_end_date ' ||l_end_date);
3438 hr_utility.trace('p_time_start ' ||p_time_start);
3439 hr_utility.trace('p_time_end ' ||p_time_end);
3440 hr_utility.trace('p_days_or_hours ' ||p_days_or_hours);
3441
3442 --
3443 -- Fetch the work schedule
3444 --
3445 hr_wrk_sch_pkg.get_per_asg_schedule
3446 ( p_person_assignment_id => p_assignment_id
3447 , p_period_start_date => l_start_date
3448 , p_period_end_date => l_end_date
3449 , p_schedule_category => NULL
3450 , p_include_exceptions => 'N'-- for bug 5102813 'Y'
3451 , p_busy_tentative_as => 'FREE'
3452 , x_schedule_source => l_schedule_source
3453 , x_schedule => l_schedule
3454 , x_return_status => l_return_status
3455 , x_return_message => l_return_message
3456 );
3457 --
3458
3459 hr_utility.trace('l_return_status ' ||l_return_status);
3460 IF l_return_status = '0' THEN
3461 --
3462 -- Calculate duration
3463 --
3464 l_idx := l_schedule.first;
3465 hr_utility.trace('l_idx ' || l_idx);
3466 hr_utility.trace('Schedule Counts ' ||l_schedule.count);
3467 --
3468 IF p_days_or_hours = 'D' THEN
3469 --
3470 l_first_band := TRUE;
3471 l_ref_date := NULL;
3472 WHILE l_idx IS NOT NULL
3473 LOOP
3474 IF l_schedule(l_idx).FREE_BUSY_TYPE IS NOT NULL THEN
3475 IF l_schedule(l_idx).FREE_BUSY_TYPE = 'FREE' THEN
3479 p_duration := p_duration + (TRUNC(l_schedule(l_idx).END_DATE_TIME) - TRUNC(l_schedule(l_idx).START_DATE_TIME) + 1);
3476 IF l_first_band THEN
3477 l_first_band := FALSE;
3478 l_ref_date := TRUNC(l_schedule(l_idx).START_DATE_TIME);
3480 ELSE -- not first time
3481 IF TRUNC(l_schedule(l_idx).START_DATE_TIME) = l_ref_date THEN
3482 p_duration := p_duration + (TRUNC(l_schedule(l_idx).END_DATE_TIME) - TRUNC(l_schedule(l_idx).START_DATE_TIME));
3483 ELSE
3484 l_ref_date := TRUNC(l_schedule(l_idx).END_DATE_TIME);
3485 p_duration := p_duration + (TRUNC(l_schedule(l_idx).END_DATE_TIME) - TRUNC(l_schedule(l_idx).START_DATE_TIME) + 1);
3486 END IF;
3487 END IF;
3488 END IF;
3489 END IF;
3490 l_idx := l_schedule(l_idx).NEXT_OBJECT_INDEX;
3491 END LOOP;
3492 --
3493 ELSE -- p_days_or_hours is 'H'
3494 --
3495 l_day_start_time := '00:00';
3496 l_day_end_time := '23:59';
3497 WHILE l_idx IS NOT NULL
3498 LOOP
3499 hr_utility.trace('l_schedule(l_idx).FREE_BUSY_TYPE ' || l_schedule(l_idx).FREE_BUSY_TYPE );
3500 hr_utility.trace('l_schedule(l_idx).END_DATE_TIME ' || l_schedule(l_idx).END_DATE_TIME );
3501 hr_utility.trace('l_schedule(l_idx).START_DATE_TIME ' || l_schedule(l_idx).START_DATE_TIME );
3502
3503 IF l_schedule(l_idx).FREE_BUSY_TYPE IS NOT NULL THEN
3504 hr_utility.trace('l_schedule(l_idx).FREE_BUSY_TYPE is not null ' || l_schedule(l_idx).FREE_BUSY_TYPE );
3505 IF l_schedule(l_idx).FREE_BUSY_TYPE = 'FREE' THEN
3506 hr_utility.trace('l_schedule(l_idx).FREE_BUSY_TYPE is FREE ' || l_schedule(l_idx).FREE_BUSY_TYPE );
3507 hr_utility.trace('l_schedule(l_idx).END_DATE_TIME ' || l_schedule(l_idx).END_DATE_TIME );
3508 hr_utility.trace('l_schedule(l_idx).START_DATE_TIME ' || l_schedule(l_idx).START_DATE_TIME );
3509 IF l_schedule(l_idx).END_DATE_TIME < l_schedule(l_idx).START_DATE_TIME THEN
3510 -- Skip this invalid slot which ends before it starts
3511 NULL;
3512 ELSE
3513 IF TRUNC(l_schedule(l_idx).END_DATE_TIME) > TRUNC(l_schedule(l_idx).START_DATE_TIME) THEN
3514 -- Start and End on different days
3515 --
3516 -- Get first day hours
3517 l_start_time := TO_CHAR(l_schedule(l_idx).START_DATE_TIME,'HH24:MI');
3518 hr_utility.trace('l_start_time ' || l_start_time);
3519
3520 SELECT p_duration + (((SUBSTR(l_day_end_time,1,2)*60 + SUBSTR(l_day_end_time,4,2)) -
3521 (SUBSTR(l_start_time,1,2)*60 + SUBSTR(l_start_time,4,2)))/60)
3522 INTO p_duration
3523 FROM DUAL;
3524 -- hr_utility.trace('p_start_duration ' || p_start_duration);
3525 hr_utility.trace('Start p_duration ' || p_duration);
3526
3527 --
3528 -- Get last day hours
3529 l_end_time := TO_CHAR(l_schedule(l_idx).END_DATE_TIME,'HH24:MI');
3530 hr_utility.trace('l_end_time ' || l_end_time);
3531 SELECT p_duration + (((SUBSTR(l_end_time,1,2)*60 + SUBSTR(l_end_time,4,2)) -
3532 (SUBSTR(l_day_start_time,1,2)*60 + SUBSTR(l_day_start_time,4,2)) + 1)/60)
3533 INTO p_duration
3534 FROM DUAL;
3535 --hr_utility.trace('p_end_duration ' || p_end_duration);
3536 hr_utility.trace('End p_duration ' || p_duration);
3537 --
3538 -- Get between full day hours
3539 SELECT p_duration + ((TRUNC(l_schedule(l_idx).END_DATE_TIME) - TRUNC(l_schedule(l_idx).START_DATE_TIME) - 1) * 24)
3540 INTO p_duration
3541 FROM DUAL;
3542 ELSE
3543 -- Start and End on same day
3544 l_start_time := TO_CHAR(l_schedule(l_idx).START_DATE_TIME,'HH24:MI');
3545 l_end_time := TO_CHAR(l_schedule(l_idx).END_DATE_TIME,'HH24:MI');
3546
3547 hr_utility.trace('l_start_time ' || l_start_time);
3548 hr_utility.trace('l_end_time ' || l_end_time);
3549
3550 SELECT p_duration + (((SUBSTR(l_end_time,1,2)*60 + SUBSTR(l_end_time,4,2)) -
3551 (SUBSTR(l_start_time,1,2)*60 + SUBSTR(l_start_time,4,2)))/60)
3552 INTO p_duration
3553 FROM DUAL;
3554 hr_utility.trace('duration l_idx '||l_idx||' ' ||p_duration);
3555
3556 END IF;
3557 END IF;
3558 END IF;
3559 END IF;
3560 l_idx := l_schedule(l_idx).NEXT_OBJECT_INDEX;
3561 END LOOP;
3562 hr_utility.trace('duration ' ||p_duration);
3563
3564 p_duration := ROUND(p_duration,2);
3565 --
3566 END IF;
3567 END IF;
3568 --
3569 hr_utility.set_location('Leaving '||g_package||'.calc_sch_based_dur',20);
3570 EXCEPTION
3571 --
3572 WHEN e_bad_time_format THEN
3573 hr_utility.set_location('Leaving '||g_package||'.calc_sch_based_dur',30);
3574 hr_utility.set_location(SQLERRM,35);
3575 RAISE;
3576 --
3577 WHEN OTHERS THEN
3578 hr_utility.set_location('Leaving '||g_package||'.calc_sch_based_dur',40);
3579 hr_utility.set_location(SQLERRM,45);
3580 RAISE;
3581 --
3582 END calc_sch_based_dur;
3583
3584
3585 FUNCTION calculate_actual_hours_worked
3586 (assignment_action_id IN number --Context
3590 ,date_earned IN date --Context
3587 ,assignment_id IN number --Context
3588 ,business_group_id IN number --Context
3589 ,element_entry_id IN number --Context
3591 ,p_period_start_date IN date
3592 ,p_period_end_date IN date
3593 ,p_schedule_category IN varchar2 --Optional
3594 ,p_include_exceptions IN varchar2 --Optional
3595 ,p_busy_tentative_as IN varchar2 --Optional
3596 ,p_legislation_code IN varchar2 -- Optional
3597 ,p_schedule_source IN OUT nocopy varchar2 --OPtional
3598 ,p_schedule IN OUT nocopy varchar2-- Optional
3599 ,p_return_status OUT nocopy number -- Optional
3600 ,p_return_message OUT nocopy varchar2 -- Optional
3601 ,p_days_or_hours IN VARCHAR2 default 'H')
3602 RETURN NUMBER IS
3603 l_work_schedule_found BOOLEAN;
3604 l_total_hours NUMBER;
3605 l_asg_frequency VARCHAR2(20);
3606 l_duration NUMBER;
3607 -- l_legislation_code VARCHAR2(10);
3608 lv_wk_sch_found VARCHAR2(20);
3609
3610 CURSOR get_asg_hours_freq(p_date_earned date,
3611 p_assignment_id number)IS
3612 SELECT hr_general.decode_lookup('FREQUENCY', ASSIGN.frequency)
3613 ,ASSIGN.normal_hours
3614 FROM per_all_assignments_f ASSIGN
3615 where date_earned
3616 BETWEEN ASSIGN.effective_start_date
3617 AND ASSIGN.effective_end_date
3618 and ASSIGN.assignment_id = p_assignment_id;
3619
3620
3621 BEGIN
3622 l_work_schedule_found := FALSE;
3623 l_total_hours := 0;
3624 -- hr_utility.trace_on(NULL, 'PAY_CALC_HOURS_WORKED');
3625 hr_utility.trace( 'Assignment Id '||assignment_id);
3626 hr_utility.trace( 'date_earned '||date_earned);
3627 hr_utility.trace( 'p_days_or_hours '||p_days_or_hours);
3628 hr_utility.trace( 'p_period_start_date '||p_period_start_date);
3629 hr_utility.trace( 'p_period_end_date '||p_period_end_date);
3630 hr_utility.trace( 'p_legislation_code '||p_legislation_code);
3631
3632 IF (p_legislation_code) IS NULL or (p_legislation_code ='') or
3633 (g_legislation_code IS NULL) THEN
3634 g_legislation_code := get_legislation_code(business_group_id);
3635 ELSE
3636 g_legislation_code := nvl(g_legislation_code,p_legislation_code);
3637 END IF;
3638
3639 hr_utility.trace( 'Legislation code : g_legislation_code '||g_legislation_code);
3640
3641 /* Calculate hours worked based on ATG work schedule information using
3642 API : HR_WRK_SCH_PKG.GET_PER_ASG_SCHEDULE ()
3643 This part will be coded later once this API is available from HR
3644 IF p_include_exceptions IS NULL THEN
3645 use p_include_exceptions = 'Y';
3646
3647 */
3648 hr_utility.trace( 'getting work schedule from ATG ');
3649
3650 calc_sch_based_dur ( p_days_or_hours,
3651 p_period_start_date,
3652 p_period_end_date+1,
3653 null,
3654 null,
3655 assignment_id,
3656 l_duration
3657 );
3658
3659
3660 IF (l_duration > 0) THEN
3661 l_work_schedule_found := true;
3662 hr_utility.trace( 'Got work schedule from ATG,duration : '||l_duration);
3663
3664 return l_duration;
3665 END IF;
3666
3667 IF NOT l_work_schedule_found THEN
3668 BEGIN
3669 hr_utility.trace( 'getting work schedule from SCL ');
3670 lv_wk_sch_found := 'FALSE';
3671 EXECUTE IMMEDIATE 'BEGIN :1 := PAY_'||g_legislation_code||
3672 '_RULES.Work_Schedule_Total_Hours(:2,:3,:4,:5,:6,:7,:8,:9); END;'
3673 USING OUT l_total_hours,
3674 IN assignment_action_id,IN assignment_id,IN business_group_id,IN element_entry_id
3675 ,IN date_earned,IN p_period_start_date,IN p_period_end_date,IN OUT lv_wk_sch_found;
3676 /*
3677 IF l_total_hours > 0 THEN
3678 hr_utility.trace( 'work schedule found from SCL ');
3679 l_work_schedule_found := TRUE;
3680 return l_total_hours;
3681 END IF;
3682 */
3683 -- Changing above logic for Bug# 8414024
3684 -- "0" total hours returned by the function does not necessarily
3685 -- mean Work Schedule is NOT found. In case of FLSA / Proration,
3686 -- total hours returned by work schedule may be zero for the FLSA
3687 -- or pro ration period.
3688
3689 IF lv_wk_sch_found = 'TRUE' THEN
3690 hr_utility.trace( 'work schedule found from SCL ');
3691 l_work_schedule_found := TRUE;
3692 return l_total_hours;
3693 END IF;
3694
3695 EXCEPTION
3696 WHEN OTHERS THEN
3697 NULL;
3698 END;
3699 END IF;
3700
3701
3702
3703 /* Calculate hours worked based on standard conditions if the actual hours
3704 worked are not available from either ATG work schedule or work schedule
3705 at assignment/org level */
3706
3707 IF NOT l_work_schedule_found THEN
3708 hr_utility.trace('Calculating hours based on Standard conditions ');
3709 hr_utility.trace( 'Assignment Id '||assignment_id);
3710 hr_utility.trace( 'date_earned '||date_earned);
3711 OPEN get_asg_hours_freq(date_earned,assignment_id);
3712 FETCH get_asg_hours_freq
3713 INTO l_asg_frequency, l_normal_hours;
3714 CLOSE get_asg_hours_freq;
3715
3716 hr_utility.trace( 'l_asg_frequency '||l_asg_frequency);
3717 hr_utility.trace( 'l_normal_hours '||l_normal_hours);
3718
3719 IF l_asg_frequency IS NOT NULL and l_normal_hours IS NOT NULL THEN
3720 l_total_hours := standard_hours_worked(l_normal_hours
3721 ,p_period_start_date
3722 ,p_period_end_date
3723 ,l_asg_frequency);
3724 return l_total_hours;
3725 END IF;
3726
3727 END IF;
3728 return 0;
3729 -- hr_utility.trace_off;
3730 END calculate_actual_hours_worked;
3731
3732 -- Added For Skip Rule for "Regular Wages" Element, "REGULAR_PAY"
3733
3734 FUNCTION term_skip_rule_rwage(ctx_payroll_id NUMBER
3735 ,ctx_assignment_id NUMBER
3736 ,ctx_date_earned DATE
3737 ,p_user_entered_time VARCHAR2
3738 ,p_final_pay_processed VARCHAR2
3739 ,p_lspd_pay_processed VARCHAR2
3740 ,p_payroll_termination_type VARCHAR2
3741 ,p_bg_termination_type VARCHAR2
3742 ,p_already_processed VARCHAR2)
3743 RETURN VARCHAR2 is
3744
3745 -- Get Current Pay Period Start and End Date
3746
3747 CURSOR csr_pay_period(p_date_earned date
3748 ,p_payroll_id number) is
3749 select ptp.start_date
3750 ,ptp.end_date
3751 from per_time_periods ptp
3752 where ptp.payroll_id = p_payroll_id
3753 and p_date_earned between ptp.start_date and ptp.end_date;
3754
3755 -- Get ATD, LSPD, FPD for the Terminated EE
3756
3757 CURSOR csr_term_dates(p_date_earned date
3758 ,p_assignment_id number) is
3759 select pds.actual_termination_date
3760 ,pds.last_standard_process_date
3761 ,pds.final_process_date
3762 from per_periods_of_service PDS,
3763 per_assignments_f ASS
3764 WHERE PDS.actual_termination_date <= p_date_earned
3765 AND PDS.period_of_service_id = ASS.period_of_service_id
3766 AND p_date_earned BETWEEN ASS.effective_start_date
3767 AND ASS.effective_end_date
3768 AND ASS.primary_flag = 'Y'
3769 AND ASS.assignment_id = p_assignment_id;
3770
3771 -- Get the Min Date Earned after ATD
3772
3773 CURSOR csr_fpprocd_min_dtearned(p_atd DATE
3774 ,p_assignment_id NUMBER) IS
3775 SELECT min(ppa_run.date_earned)
3776 FROM pay_payroll_actions ppa_run,
3777 pay_assignment_actions paa_run
3778 WHERE ppa_run.date_earned >= p_atd
3779 AND ppa_run.action_status = 'C'
3780 AND ppa_run.action_type in ('Q','R','B','I')
3781 AND ((nvl(paa_run.run_type_id, ppa_run.run_type_id) is null and
3782 paa_run.source_action_id is null) or
3783 (nvl(paa_run.run_type_id, ppa_run.run_type_id) is not null and
3784 paa_run.source_action_id is not null))
3785 AND ppa_run.payroll_action_id = paa_run.payroll_action_id
3786 AND paa_run.action_status = 'C'
3787 AND paa_run.assignment_id = p_assignment_id
3788 AND NOT EXISTS (
3789 SELECT 1
3790 FROM pay_payroll_actions ppa_rev,
3791 pay_assignment_actions paa_rev,
3792 pay_action_interlocks pai
3793 WHERE pai.locked_Action_id = paa_run.assignment_action_id
3794 AND pai.locking_action_id = paa_rev.assignment_action_id
3795 AND ppa_rev.payroll_action_id = paa_rev.payroll_action_id
3796 AND ppa_rev.action_type = 'V');
3797
3798 -- Get the Min Date Earned after LSPD
3799
3800 CURSOR csr_lspprocd_min_dtearned(p_lspd DATE
3801 ,p_assignment_id NUMBER) IS
3802 SELECT min(ppa_run.date_earned)
3803 FROM pay_payroll_actions ppa_run,
3804 pay_assignment_actions paa_run
3805 WHERE ppa_run.date_earned >= p_lspd
3806 AND ppa_run.action_status = 'C'
3807 AND ppa_run.action_type in ('Q','R','B','I')
3808 AND ((nvl(paa_run.run_type_id, ppa_run.run_type_id) is null and
3809 paa_run.source_action_id is null) or
3810 (nvl(paa_run.run_type_id, ppa_run.run_type_id) is not null and
3811 paa_run.source_action_id is not null))
3812 AND ppa_run.payroll_action_id = paa_run.payroll_action_id
3813 AND paa_run.action_status = 'C'
3814 AND paa_run.assignment_id = p_assignment_id
3815 AND NOT EXISTS (
3816 SELECT 1
3817 FROM pay_payroll_actions ppa_rev,
3818 pay_assignment_actions paa_rev,
3819 pay_action_interlocks pai
3820 WHERE pai.locked_Action_id = paa_run.assignment_action_id
3821 AND pai.locking_action_id = paa_rev.assignment_action_id
3822 AND ppa_rev.payroll_action_id = paa_rev.payroll_action_id
3823 AND ppa_rev.action_type = 'V');
3824
3825 lv_term_typ varchar2(1);
3826 ld_pay_start_date date;
3827 ld_pay_end_date date;
3828 ld_atd date;
3829 ld_lspd date;
3830 ld_fpd date;
3831 ld_fp_dt_earned date;
3832 ld_lsp_dt_earned date;
3833
3834 begin
3835
3836 hr_utility.trace('ctx_date_earned := '|| to_char(ctx_date_earned));
3837 hr_utility.trace('ctx_payroll_id := '|| ctx_payroll_id);
3838 hr_utility.trace('ctx_assignment_id := '|| ctx_assignment_id);
3839 hr_utility.trace('p_user_entered_time := '|| p_user_entered_time);
3840 hr_utility.trace('p_final_pay_processed := '|| p_final_pay_processed);
3841 hr_utility.trace('p_lspd_pay_processed := '|| p_lspd_pay_processed);
3842 hr_utility.trace('p_payroll_termination_type := '|| p_payroll_termination_type);
3843 hr_utility.trace('p_bg_termination_type := '|| p_bg_termination_type);
3844 hr_utility.trace('p_already_processed := '|| p_already_processed);
3845
3846 OPEN csr_pay_period(ctx_date_earned
3847 ,ctx_payroll_id) ;
3848 FETCH csr_pay_period INTO ld_pay_start_date
3849 ,ld_pay_end_date;
3850 CLOSE csr_pay_period;
3851
3852 OPEN csr_term_dates(ctx_date_earned
3853 ,ctx_assignment_id) ;
3854 FETCH csr_term_dates INTO ld_atd
3855 ,ld_lspd
3856 ,ld_fpd;
3857 CLOSE csr_term_dates;
3858
3859 IF p_payroll_termination_type = 'A' THEN
3860 lv_term_typ := 'A';
3861 ELSIF p_payroll_termination_type = 'L' THEN
3862 lv_term_typ := 'L';
3863 ELSE
3864 IF p_bg_termination_type = 'A' THEN
3865 lv_term_typ := 'A';
3866 ELSIF p_bg_termination_type = 'L' THEN
3867 lv_term_typ := 'L';
3868 ELSE
3869 lv_term_typ := 'L';
3870 END IF;
3871 END IF;
3872
3873 hr_utility.trace('ld_pay_start_date := '|| to_char(ld_pay_start_date));
3874 hr_utility.trace('ld_pay_end_date := '|| to_char(ld_pay_end_date));
3875 hr_utility.trace('ld_atd := '|| to_char(ld_atd));
3876 hr_utility.trace('ld_lspd := '|| to_char(ld_lspd));
3877 hr_utility.trace('ld_fpd := '|| to_char(ld_fpd));
3878 hr_utility.trace('lv_term_typ := '|| lv_term_typ);
3879
3880 IF lv_term_typ = 'A' THEN -- Termination Rule 'First Pay After Term Date'
3881 IF ld_atd <= ctx_date_earned THEN
3882 IF p_final_pay_processed = 'Y' THEN
3883 OPEN csr_fpprocd_min_dtearned(ld_atd
3884 ,ctx_assignment_id);
3885 FETCH csr_fpprocd_min_dtearned INTO ld_fp_dt_earned;
3886 CLOSE csr_fpprocd_min_dtearned;
3887
3888 hr_utility.trace('ld_fp_dt_earned := '|| TO_CHAR(ld_fp_dt_earned));
3889
3890 IF ctx_date_earned > ld_fp_dt_earned THEN
3891 return 'Y';
3892 ELSE
3893 IF p_already_processed <> 'Y' THEN
3894 IF p_user_entered_time = 'Y' THEN
3895 return 'Y';
3896 ELSE
3897 return 'N';
3898 END IF;
3899 ELSIF p_already_processed = 'Y' THEN
3900 return 'Y';
3901 END IF;
3902 END IF; -- Current PayPeriod Date Earned > Date Earned of Final Pay Processed
3903 ELSE
3904 IF p_already_processed <> 'Y' THEN
3905 IF p_user_entered_time = 'Y' THEN
3906 return 'Y';
3907 ELSE
3908 return 'N';
3909 END IF;
3910 ELSIF p_already_processed = 'Y' THEN
3911 return 'Y';
3912 END IF;
3913 END IF; -- Final Pay Processed = 'Y'
3914 ELSE
3915 IF p_already_processed <> 'Y' THEN
3916 IF p_user_entered_time = 'Y' THEN
3917 return 'Y';
3918 ELSE
3919 return 'N';
3920 END IF;
3921 ELSIF p_already_processed = 'Y' THEN
3922 return 'Y';
3923 END IF;
3924 END IF; -- ATD <= Current Pay Period Date Earned
3925
3926 ELSIF lv_term_typ = 'L' THEN -- Term Rule 'Last Standard Process Date'
3927 IF ((ld_atd <= ctx_date_earned
3928 AND ld_lspd <= ctx_date_earned) OR
3929 (ld_atd <= ctx_date_earned
3930 AND ld_lspd > ctx_date_earned)) THEN
3931
3932 IF p_lspd_pay_processed = 'Y' THEN
3933
3934 OPEN csr_lspprocd_min_dtearned(ld_lspd
3935 ,ctx_assignment_id);
3936 FETCH csr_lspprocd_min_dtearned INTO ld_lsp_dt_earned;
3937 CLOSE csr_lspprocd_min_dtearned;
3938
3939 hr_utility.trace('ld_fp_dt_earned := '|| TO_CHAR(ld_lsp_dt_earned));
3940
3941 IF ctx_date_earned > ld_lsp_dt_earned THEN
3942 return 'Y';
3943 ELSE
3944 IF p_already_processed <> 'Y' THEN
3945 IF p_user_entered_time = 'Y' THEN
3946 return 'Y';
3947 ELSE
3948 return 'N';
3952 END IF;
3949 END IF;
3950 ELSIF p_already_processed = 'Y' THEN
3951 return 'Y';
3953 END IF; -- -- Current PayPeriod Date Earned > Date Earned of LSPD Pay Processed
3954 ELSE
3955 IF p_already_processed <> 'Y' THEN
3956 IF p_user_entered_time = 'Y' THEN
3957 return 'Y';
3958 ELSE
3959 return 'N';
3960 END IF;
3961 ELSIF p_already_processed = 'Y' THEN
3962 return 'Y';
3963 END IF;
3964 END IF; -- LSPD Pay Processed = 'Y'
3965 ELSE
3966 IF p_already_processed <> 'Y' THEN
3967 IF p_user_entered_time = 'Y' THEN
3968 return 'Y';
3969 ELSE
3970 return 'N';
3971 END IF;
3972 ELSIF p_already_processed = 'Y' THEN
3973 return 'Y';
3974 END IF;
3975 END IF; -- ATD <= Current Pay Period Date Earned AND LSPD <= OR > Current Pay Period Date Earned
3976 ELSE
3977 return 'N';
3978 END IF; -- Term Rule Neither 'A' nor 'L'
3979
3980 END term_skip_rule_rwage;
3981
3982 --
3983 -- Introduced for Enabling Core Proration Functionality into
3984 -- "Regular Salary", "Regular Wages" elements
3985 -- Called by Formula Function HOURS_BETWEEN
3986 --
3987 Function hours_between( business_group_id IN number --Context
3988 ,assignment_id IN number --Context
3989 ,assignment_action_id IN number --Context
3990 ,date_earned IN date --Context
3991 ,element_entry_id IN number --Context
3992
3993 ,p_period_start_date IN date
3994 ,p_period_end_date IN date
3995 ,p_schedule_category IN varchar2 default 'WORK'-- 'WORK'/'PAGER'
3996 ,p_include_exceptions IN varchar2 default ''
3997 ,p_busy_tentative_as IN varchar2 default 'FREE'-- 'BUSY'/FREE/NULL
3998 ,p_legislation_code IN varchar2 default ''
3999 ,p_schedule_source IN OUT nocopy varchar2 -- 'PER_ASG' for asg
4000 ,p_schedule IN OUT nocopy varchar2 -- schedule
4001 ,p_return_status OUT nocopy number
4002 ,p_return_message OUT nocopy varchar2
4003 ,p_days_or_hours IN VARCHAR2 default 'H' -- 'D' for days, 'H' for hours
4004 ) RETURN NUMBER is
4005 l_work_schedule_found BOOLEAN;
4006 l_total_hours NUMBER;
4007 l_asg_frequency VARCHAR2(20);
4008 l_duration NUMBER;
4009 l_normal_hours NUMBER;
4010 -- l_legislation_code VARCHAR2(10);
4011 lv_wk_sch_found VARCHAR2(20);
4012
4013 CURSOR get_asg_hours_freq(p_date_earned date,
4014 p_assignment_id number)IS
4015 SELECT hr_general.decode_lookup('FREQUENCY', ASSIGN.frequency)
4016 ,ASSIGN.normal_hours
4017 FROM per_all_assignments_f ASSIGN
4018 where date_earned
4019 BETWEEN ASSIGN.effective_start_date
4020 AND ASSIGN.effective_end_date
4021 and ASSIGN.assignment_id = p_assignment_id;
4022
4023
4024 BEGIN
4025 hr_utility.trace('Entering Into hours_between.');
4026 l_work_schedule_found := FALSE;
4027 l_total_hours := 0;
4028
4029 hr_utility.trace( 'Assignment Id '||assignment_id);
4030 hr_utility.trace( 'date_earned '||date_earned);
4031 hr_utility.trace( 'p_days_or_hours '||p_days_or_hours);
4032 hr_utility.trace( 'p_period_start_date '||p_period_start_date);
4033 hr_utility.trace( 'p_period_end_date '||p_period_end_date);
4034 hr_utility.trace( 'p_legislation_code '||p_legislation_code);
4035
4036 IF (p_legislation_code) IS NULL or (p_legislation_code ='') or
4037 (g_legislation_code IS NULL) THEN
4038 g_legislation_code := get_legislation_code(business_group_id);
4039 ELSE
4040 g_legislation_code := nvl(g_legislation_code,p_legislation_code);
4041 END IF;
4042
4043 hr_utility.trace( 'Legislation code : g_legislation_code '||g_legislation_code);
4044
4045 /* Calculate hours worked based on ATG work schedule information using
4046 API : HR_WRK_SCH_PKG.GET_PER_ASG_SCHEDULE ()
4047 This part will be coded later once this API is available from HR
4048 IF p_include_exceptions IS NULL THEN
4049 use p_include_exceptions = 'Y';
4050
4051 */
4052
4053 hr_utility.trace( 'getting work schedule from ATG ');
4054
4055 calc_sch_based_dur ( p_days_or_hours,
4056 p_period_start_date,
4057 p_period_end_date+1,
4058 null,
4059 null,
4060 assignment_id,
4061 l_duration
4062 );
4063
4064
4065 IF (l_duration > 0) THEN
4066 l_work_schedule_found := true;
4067 hr_utility.trace( 'Got work schedule from ATG,duration : '||l_duration);
4068 pay_core_ff_udfs.g_normal_hours := l_duration;
4069 return l_duration;
4070 END IF;
4071
4072 IF NOT l_work_schedule_found THEN
4073 BEGIN
4074 hr_utility.trace( 'getting work schedule from SCL ');
4075 lv_wk_sch_found := 'FALSE';
4076 EXECUTE IMMEDIATE 'BEGIN :1 := PAY_'||g_legislation_code||
4077 '_RULES.Work_Schedule_Total_Hours(:2,:3,:4,:5,:6,:7,:8,:9); END;'
4078 USING OUT l_total_hours,
4079 IN assignment_action_id,IN assignment_id,IN business_group_id,IN element_entry_id
4080 ,IN date_earned,IN p_period_start_date,IN p_period_end_date,IN OUT lv_wk_sch_found;
4081
4082 IF lv_wk_sch_found = 'TRUE' THEN
4083 hr_utility.trace( 'work schedule found from SCL ');
4084 l_work_schedule_found := TRUE;
4085 pay_core_ff_udfs.g_normal_hours := l_total_hours;
4086 return l_total_hours;
4087 END IF;
4088
4089 EXCEPTION
4090 WHEN OTHERS THEN
4091 null;
4092 END;
4093 END IF;
4094
4095
4096 /* Calculate hours worked based on standard conditions if the actual hours
4097 worked are not available from either ATG work schedule or work schedule
4098 at assignment/org level */
4099
4100 IF NOT l_work_schedule_found THEN
4101 hr_utility.trace('Calculating hours based on Standard conditions ');
4102 hr_utility.trace( 'Assignment Id '||assignment_id);
4103 hr_utility.trace( 'date_earned '||date_earned);
4104 OPEN get_asg_hours_freq(date_earned,assignment_id);
4105 FETCH get_asg_hours_freq
4106 INTO l_asg_frequency, l_normal_hours;
4107 CLOSE get_asg_hours_freq;
4108
4109 hr_utility.trace( 'l_asg_frequency '||l_asg_frequency);
4110 hr_utility.trace( 'l_normal_hours '||l_normal_hours);
4111
4112 IF l_asg_frequency IS NOT NULL and pay_core_ff_udfs.g_normal_hours IS NOT NULL THEN
4113 l_total_hours := standard_hours_worked(l_normal_hours
4114 ,p_period_start_date
4115 ,p_period_end_date
4116 ,l_asg_frequency);
4117 return l_total_hours;
4118 END IF;
4119
4120 END IF;
4121 return 0;
4122 -- hr_utility.trace_off;
4123 END hours_between;
4124
4125 --
4126 -- Introduced for Enabling Core Proration Functionality into
4127 -- "Regular Salary", "Regular Wages" elements
4128 -- Called by Formula Function CALC_SICK_PAY
4129 --
4130 FUNCTION calc_sick_pay (ctx_asg_id IN NUMBER
4131 ,p_period_end_dt IN DATE
4132 ,p_prorate_start_dt IN DATE
4133 ,p_prorate_end_dt IN DATE
4134 ,p_curr_rate IN NUMBER
4135 ,p_sick_hours IN OUT NOCOPY NUMBER)
4136 RETURN NUMBER IS
4137
4138 l_sick_pay NUMBER;
4139 l_sick_hours NUMBER;
4140 p_sick_pay NUMBER;
4141
4142 /*
4143 CURSOR get_sick_hours ( v_asg_id NUMBER,
4144 v_eff_date DATE) IS
4145 select fnd_number.canonical_to_number(pev.screen_entry_value)
4146 from per_absence_attendance_types abt,
4147 pay_element_entries_f pee,
4148 pay_element_entry_values_f pev
4149 where pev.input_value_id = abt.input_value_id
4150 and abt.absence_category = 'S'
4151 and v_eff_date between pev.effective_start_date
4152 and pev.effective_end_date
4153 and pee.element_entry_id = pev.element_entry_id
4154 and pee.assignment_id = v_asg_id
4155 and v_eff_date between pee.effective_start_date
4156 and pee.effective_end_date;
4157 */
4158
4159 CURSOR get_sick_hours ( v_asg_id NUMBER,
4160 v_st_date DATE,
4161 v_eff_date DATE) IS
4162 select distinct pee.element_entry_id
4163 ,abs.absence_attendance_id
4164 ,abs.date_start
4165 ,abs.date_end
4166 ,fnd_number.canonical_to_number(peev.screen_entry_value)
4167 from per_absence_attendance_types abt
4168 ,per_absence_attendances abs
4169 ,pay_element_entries_f pee
4170 ,pay_element_entry_values_f peev
4171 where abt.input_value_id = peev.input_value_id
4172 and abt.absence_category = 'S'
4173 and abt.absence_attendance_type_id = abs.absence_attendance_type_id
4174 and peev.element_entry_id = pee.element_entry_id
4175 and pee.creator_id = abs.absence_attendance_id
4176 and pee.creator_type = 'A'
4177 and abs.date_start between v_st_date and v_eff_date
4178 and pee.assignment_id = v_asg_id
4179 and v_eff_date between peev.effective_start_date and peev.effective_end_date
4180 and v_eff_date between pee.effective_start_date and pee.effective_end_date;
4181
4182 ln_ele_entry_id pay_element_entries_f.element_entry_id%TYPE;
4183 ln_abs_att_id per_absence_attendances.absence_attendance_id%TYPE;
4184 ld_start_date per_absence_attendances.date_start%TYPE;
4185 ld_end_date per_absence_attendances.date_end%TYPE;
4186
4187 -- The "sick_pay" looks for hours entered against Sick absence types in
4188 -- the current period. The number of hours are summed and multiplied by the
4189 -- current rate of Regular Pay.
4190 -- Return immediately when no sick time has been taken.
4191
4192 BEGIN
4193
4194 hr_utility.trace('Entered calc_sick_pay');
4195 hr_utility.trace('Passed ctx_asg_id := ' || ctx_asg_id);
4196 hr_utility.trace('Passed p_period_end_dt := ' || TO_CHAR(p_period_end_dt));
4197 hr_utility.trace('Passed p_prorate_start_dt := ' || TO_CHAR(p_prorate_start_dt));
4198 hr_utility.trace('Passed p_prorate_end_dt := ' || TO_CHAR(p_prorate_end_dt));
4199 hr_utility.trace('Passed p_curr_rate := ' || p_curr_rate);
4200 hr_utility.trace('Passed p_sick_hours := ' || p_sick_hours);
4201
4202 /* Init */
4203 l_sick_pay :=0;
4204 l_sick_hours := 0;
4205 p_sick_hours := 0;
4206 p_sick_pay := 0;
4207
4208 /*IF p_period_end_dt BETWEEN p_prorate_start_dt AND p_prorate_end_dt THEN
4209 */
4210
4211 OPEN get_sick_hours (ctx_asg_id, /*p_period_end_dt*/ p_prorate_start_dt, p_prorate_end_dt);
4212 LOOP
4213
4214 hr_utility.trace('calc_sick_pay');
4215 hr_utility.set_location('calc_sick_pay', 13);
4216
4217 FETCH get_sick_hours
4218 INTO ln_ele_entry_id
4219 ,ln_abs_att_id
4220 ,ld_start_date
4221 ,ld_end_date
4222 ,l_sick_hours;
4223 EXIT WHEN get_sick_hours%NOTFOUND;
4224
4225 hr_utility.trace('ln_ele_entry_id := ' || ln_ele_entry_id);
4226 hr_utility.trace('ln_abs_att_id := ' || ln_abs_att_id);
4227 hr_utility.trace('ld_start_date := ' || to_char(ld_start_date));
4228 hr_utility.trace('ld_end_date := ' || to_char(ld_end_date));
4229 hr_utility.trace('l_sick_hours := ' || l_sick_hours);
4230
4231 p_sick_hours := p_sick_hours + l_sick_hours;
4232 END LOOP;
4233 CLOSE get_sick_hours;
4234
4235 IF p_sick_hours <> 0 THEN
4236 l_sick_pay := p_sick_hours * p_curr_rate;
4237 p_sick_pay := l_sick_pay;
4238 END IF;
4239
4240 /*
4241 ELSE
4242 l_sick_hours := 0;
4243 l_sick_pay := 0;
4244
4245 p_sick_hours := l_sick_hours;
4246 p_sick_pay := l_sick_pay;
4247
4248 END IF;
4249 */
4250
4251 hr_utility.trace('Returned p_sick_hours := ' || p_sick_hours);
4252 hr_utility.trace('Returned p_sick_pay := ' || p_sick_pay);
4253
4254 RETURN p_sick_pay;
4255
4256 END calc_sick_pay;
4257
4258 --
4259 -- Introduced for Enabling Core Proration Functionality into
4260 -- "Regular Salary", "Regular Wages" elements
4261 -- Called by Formula Function CALC_VAC_PAY
4262 --
4263 FUNCTION calc_vacation_pay (ctx_asg_id IN NUMBER
4264 ,p_period_end_dt IN DATE
4265 ,p_prorate_start_dt IN DATE
4266 ,p_prorate_end_dt IN DATE
4267 ,p_curr_rate IN NUMBER
4268 ,p_vac_hours IN OUT NOCOPY NUMBER)
4269 RETURN NUMBER IS
4270
4271 l_vac_pay NUMBER;
4272 l_vac_hours NUMBER;
4273 p_vac_pay NUMBER;
4274
4275 /*
4276 CURSOR get_vac_hours ( v_asg_id NUMBER,
4277 v_eff_date DATE) IS
4278 select fnd_number.canonical_to_number(pev.screen_entry_value)
4279 from per_absence_attendance_types abt,
4280 pay_element_entries_f pee,
4281 pay_element_entry_values_f pev
4282 where pev.input_value_id = abt.input_value_id
4283 and abt.absence_category = 'V'
4284 and v_eff_date between pev.effective_start_date
4285 and pev.effective_end_date
4286 and pee.element_entry_id = pev.element_entry_id
4287 and pee.assignment_id = v_asg_id
4288 and v_eff_date between pee.effective_start_date
4289 and pee.effective_end_date;
4290 */
4291
4292 CURSOR get_vac_hours ( v_asg_id NUMBER,
4293 v_st_date DATE,
4294 v_eff_date DATE) IS
4295 select distinct pee.element_entry_id
4296 ,abs.absence_attendance_id
4297 ,abs.date_start
4298 ,abs.date_end
4299 ,fnd_number.canonical_to_number(peev.screen_entry_value)
4300 from per_absence_attendance_types abt
4301 ,per_absence_attendances abs
4302 ,pay_element_entries_f pee
4303 ,pay_element_entry_values_f peev
4304 where abt.input_value_id = peev.input_value_id
4305 and abt.absence_category = 'V'
4306 and abt.absence_attendance_type_id = abs.absence_attendance_type_id
4307 and peev.element_entry_id = pee.element_entry_id
4308 and pee.creator_id = abs.absence_attendance_id
4309 and pee.creator_type = 'A'
4310 and abs.date_start between v_st_date and v_eff_date
4311 and pee.assignment_id = v_asg_id
4312 and v_eff_date between peev.effective_start_date and peev.effective_end_date
4313 and v_eff_date between pee.effective_start_date and pee.effective_end_date;
4314
4315 ln_ele_entry_id pay_element_entries_f.element_entry_id%TYPE;
4316 ln_abs_att_id per_absence_attendances.absence_attendance_id%TYPE;
4317 ld_start_date per_absence_attendances.date_start%TYPE;
4318 ld_end_date per_absence_attendances.date_end%TYPE;
4319
4320 -- The "vacation_pay" fn looks for hours entered against absence types
4321 -- in the current period. The number of hours are summed and multiplied by
4322 -- the current rate of Regular Pay..
4323 -- Return immediately when no vacation time has been taken.
4324 -- Need to loop thru all "Vacation Plans" and check for entries in the current
4325 -- period for this assignment.
4326
4327 BEGIN
4328 hr_utility.trace('Entered calc_vacation_pay');
4329 hr_utility.trace('Passed ctx_asg_id := ' || ctx_asg_id);
4330 hr_utility.trace('Passed p_period_end_dt := ' || TO_CHAR(p_period_end_dt));
4331 hr_utility.trace('Passed p_prorate_start_dt := ' || TO_CHAR(p_prorate_start_dt));
4332 hr_utility.trace('Passed p_prorate_end_dt := ' || TO_CHAR(p_prorate_end_dt));
4333 hr_utility.trace('Passed p_curr_rate := ' || p_curr_rate);
4334 hr_utility.trace('Passed p_vac_hours := ' || p_vac_hours);
4335
4336 /* Init */
4337 l_vac_pay := 0;
4338 l_vac_hours := 0;
4339 p_vac_hours := 0;
4340 p_vac_pay := 0;
4341
4342 /*IF p_period_end_dt BETWEEN p_prorate_start_dt AND p_prorate_end_dt THEN
4343 */
4344 OPEN get_vac_hours (ctx_asg_id, /*p_period_end_dt*/ p_prorate_start_dt, p_prorate_end_dt);
4345 LOOP
4346
4347 hr_utility.set_location('calc_vacation_pay', 13);
4348 hr_utility.trace('Opened get_vac_hours');
4349
4350 FETCH get_vac_hours
4351 INTO ln_ele_entry_id
4352 ,ln_abs_att_id
4353 ,ld_start_date
4354 ,ld_end_date
4355 ,l_vac_hours;
4356 EXIT WHEN get_vac_hours%NOTFOUND;
4357
4358 hr_utility.trace('ln_ele_entry_id := ' || ln_ele_entry_id);
4359 hr_utility.trace('ln_abs_att_id := ' || ln_abs_att_id);
4360 hr_utility.trace('ld_start_date := ' || to_char(ld_start_date));
4361 hr_utility.trace('ld_end_date := ' || to_char(ld_end_date));
4362 hr_utility.trace('l_vac_hours := ' || l_vac_hours);
4363
4364 p_vac_hours := p_vac_hours + l_vac_hours;
4365
4366 END LOOP;
4367 CLOSE get_vac_hours;
4368 IF p_vac_hours <> 0 THEN
4369 l_vac_pay := p_vac_hours * p_curr_rate;
4370 p_vac_pay := l_vac_pay;
4371 END IF;
4372
4373 /*
4374 ELSE
4375 l_vac_hours := 0;
4376 l_vac_pay := 0;
4377
4378 p_vac_hours := l_vac_hours;
4379 p_vac_pay := l_vac_pay;
4380 END IF;
4381 */
4382
4383 hr_utility.trace('Returned p_vac_hours := ' || p_vac_hours);
4384 hr_utility.trace('Returned p_vac_pay := ' || p_vac_pay);
4385
4386 RETURN p_vac_pay;
4387
4388 END calc_vacation_pay;
4389
4390 --
4391 -- Introduced for Enabling Core Proration Functionality into
4392 -- "Regular Salary", "Regular Wages" elements
4393 -- Called by Formula Function REDUCED_REGULAR_CALC
4394 --
4395 FUNCTION calc_reduced_reg(ctx_assignment_id IN NUMBER
4396 ,ctx_assignment_action_id IN NUMBER
4397 ,p_period_end_dt IN DATE
4398 ,p_prorate_start_dt IN DATE
4399 ,p_prorate_end_dt IN DATE
4400 ,p_red_reg_earn IN OUT NOCOPY NUMBER
4401 ,p_red_reg_hrs IN OUT NOCOPY NUMBER
4402 )
4403 RETURN VARCHAR2 IS
4404 ln_red_regular_earn NUMBER;
4405 ln_red_regular_hrs NUMBER;
4406 BEGIN
4407 hr_utility.trace('Entered into pay_core_ff_udfs.calc_reduced_reg');
4408 hr_utility.trace('ctx_assignment_id := ' || ctx_assignment_id);
4409 hr_utility.trace('ctx_assignment_action_id := ' || ctx_assignment_action_id);
4410 hr_utility.trace('p_period_end_dt := ' || TO_CHAR(p_period_end_dt));
4411 hr_utility.trace('p_prorate_start_dt := ' || p_prorate_start_dt);
4412 hr_utility.trace('p_prorate_end_dt := ' || p_prorate_end_dt);
4413 hr_utility.trace('p_red_reg_earn := ' || p_red_reg_earn);
4414 hr_utility.trace('p_red_reg_hrs := ' || p_red_reg_hrs);
4415
4416 IF p_period_end_dt BETWEEN p_prorate_start_dt AND p_prorate_end_dt THEN
4417 ln_red_regular_earn := (-1) * p_red_reg_earn;
4418 ln_red_regular_hrs := (-1) * p_red_reg_hrs;
4419
4420 p_red_reg_earn := ln_red_regular_earn;
4421 p_red_reg_hrs := ln_red_regular_hrs;
4422
4423 ELSE
4424 p_red_reg_earn := 0;
4425 p_red_reg_hrs := 0;
4426 END IF;
4427
4428 hr_utility.trace('Returned p_red_reg_earn := ' || p_red_reg_earn);
4429 hr_utility.trace('Returned p_red_reg_hrs := ' || p_red_reg_hrs);
4430
4431 RETURN 'TRUE';
4432
4433 END calc_reduced_reg;
4434
4435 FUNCTION get_upgrade_flag(ctx_ele_typ_id IN NUMBER)
4436 RETURN VARCHAR2 IS
4437 BEGIN
4438
4439 RETURN pay_us_rsrw_upgrev.get_upgrade_flag(p_ctx_ele_typ_id => ctx_ele_typ_id);
4440
4441 END get_upgrade_flag;
4442
4443 FUNCTION get_num_period_curr_year(ctx_bg_id in NUMBER
4444 ,ctx_payroll_id in NUMBER
4445 ,ctx_ele_type_id in NUMBER
4446 ,period_end_date in DATE)
4447 RETURN NUMBER IS
4448 BEGIN
4449 RETURN pay_us_rsrw_upgrev.get_payprd_per_fiscal_yr(p_ctx_bg_id => ctx_bg_id
4450 ,p_ctx_payroll_id => ctx_payroll_id
4451 ,p_eletyp_ctx_id => ctx_ele_type_id
4452 ,p_period_end_date => period_end_date);
4453
4454 END get_num_period_curr_year;
4455
4456 FUNCTION get_asg_status_typ(ctx_asg_id IN NUMBER
4457 ,prorate_end_dt IN DATE)
4458 RETURN VARCHAR2 IS
4459
4460 l_proc_reg_sal varchar2(3);
4461
4462 CURSOR get_proc_reg_sal IS
4463 SELECT parameter_value
4464 FROM pay_action_parameters
4465 WHERE parameter_name = 'PROC_REG_SAL_INACT';
4466
4467 BEGIN
4468
4469 /* Bug 10063757 Check to see if we want to confirm the Assignment Status is
4470 Active Assignmnet prior to continuing. We check the pay_action_parameter
4471 'PROC_REG_SAL_INACT'. IF the action parameter is not there or NOT EQUAL to
4472 'Y' we will drop into the code the check if the Assignment_Status =
4473 ACTIVE_ASSIGNMENT prior to continuing processing
4474 */
4475
4476 l_proc_reg_sal := 'N';
4477
4478 OPEN get_proc_reg_sal;
4479 FETCH get_proc_reg_sal INTO l_proc_reg_sal;
4480
4481 IF get_proc_reg_sal%NOTFOUND THEN
4482 l_proc_reg_sal := 'N';
4483 ELSE
4484 IF ( upper(l_proc_reg_sal) = 'Y'
4485 OR upper(l_proc_reg_sal) = 'YES' ) THEN --skchalla
4486 l_proc_reg_sal := 'Y';
4487 ELSE
4488 l_proc_reg_sal := 'N';
4489 END IF;
4490 END IF;
4491
4492 CLOSE get_proc_reg_sal;
4493
4494
4495
4496
4497 IF l_proc_reg_sal <> 'Y' THEN
4498 IF pay_us_rsrw_upgrev.get_assignment_status(p_ctx_asg_id => ctx_asg_id
4499 ,p_prorate_end_dt => prorate_end_dt) = 'ACTIVE_ASSIGN' THEN
4500
4501 hr_utility.trace('Assignment status is ACTIVE_ASSIGN.');
4502 RETURN 'Y';
4503 ELSE
4504 hr_utility.trace('Assignment status is NOT ACTIVE_ASSIGN.');
4505 RETURN 'N';
4506 END IF;
4507 ELSE
4508 RETURN 'Y';
4509 END IF;
4510
4511 END get_asg_status_typ;
4512
4513 END pay_core_ff_udfs ;