1 PACKAGE BODY hr_ca_ff_udfs AS
2 /* $Header: pycaudfs.pkb 120.3.12000000.4 2007/05/15 09:48:36 amigarg noship $ */
3 /*
4 +======================================================================+
5 | Copyright (c) 1994 Oracle Corporation |
6 | Redwood Shores, California, USA |
7 | All rights reserved. |
8 +======================================================================+
9
10 Name : hr_ca_ff_udfs
11 Filename : pycaudfs.pkb
12 Change List
13 -----------
14 Date Name Vers Bug No Description
15 ---- ---- ---- ------ -----------
16 05-MAY-1999 mmukherj Created
17 This file has been copied
18 from pyusudfs.pkb
19 14-FEB-2000 SSattineni Done Flexible Dates and Multi
20 radix conversion compatible to
21 11.5 version
22 21-JUN-2000 MMukherj Changed the Meanings to Codes
23 for Bug No: 1081235
24 v_pay_basis_code,
25 v_asst_std_freq_code
26 17-SEP-2001 SSouresr Changed the insert into the
27 table fnd_sessions to use the
28 function set_effective_date
29 instead.
30 10-Jan-2002 vpandya Converted p_tax_unit_id to
31 character while comparing it
32 to segment1 in
33 hr_soft_coding_keyflex table
34 to overcome 'invali number' &
35 also added dbdrv lines for gscc
36 30-OCT-2002 pganguly 2647412 Changed Convert_Period_Type.
37 Previously it used to get the
38 day of the week via 'DT' and
39 used to pass this value to get
40 the value from
41 hruserdt.get_table_value. But
42 this was not working in the
43 Psedo translated env as
44 user_tables/row/columns are
45 not translated. Now it is
46 getting the day number and then
47 based upon the number it deter
48 mines the day. Also changed
49 standard_hours_worked to do the
50 same.
51 18-NOV-2002 tclewis 2666118 Changed convert_period_type
52 where we are querring number per
53 fiscal year. to change
54 where sysdate between ...
55 to nvl(p_period_start_date,
56 sysdate).
57 19-NOV-2002 tclewis changed nvl(p_period_start_date
58 ,sysdate) to use session date.
59 02-JAN-2003 mmukherj 115.9 2710358 get_flat_amounts,get_rates and
60 get_percentage cursors have
61 been changed to improve the
62 performance.
63 22-Jan-2003 vpandya 115.10 For Multi GRE functionality,
64 tax unit id will be stored in
65 segment1, segment11 and
66 segment12 of
67 hr_soft_coding_keyflex
68 depending on the gre type.
69 Changed all conditions wherever
70 segment1 is used for tax unit
71 id.
72 22-Jan-2003 vpandya 115.11 Added nocopy with out parameter.
73 09-JAN-2003 pganguly 115.12 Changed the select statements
74 in OT_Base_Rate which was
75 flagged in the Perf Repository
76 with cost more than 150. This
77 fixes bug# 3358735.
78 12-JUN-2004 trugless 115.13 3650170 Changed format of
79 v_from_annualizing_factor and
80 v_to_annualizing_factor from
81 number(10) to number(30,7) in
82 the Convert_Period_Type
83 function.
84 21-FEB-2005 pganguly 115.14 4118082 Added OR Condition in the select
85 which sets 1 to
86 l_eev_info_changes in the
87 calculate_period_earnings
88 procedure.
89 28-OCT-2005 mmukherj 115.15 Added extra parameters in
90 calculate_period_earnings
91 and convert_period_type
92 These parameters are coming
93 from contexts and will be used
94 to use the new core work schedule.
95 31-OCT-2005 mmukherj 115.16 Added calls to the core function
96 to calculate the actual hours
97 worked. This will make sure that
98 while calculating the hours it
99 looks into the core work pattern
100 information.
101 12-APR-2006 meshah 115.17 5155854 changed the select for the
102 condition UPPER(p_freq) <> 'HOURLY'
103 also changed the exception
104 21-NOV-2006 saikrish 115.18 5097793 Added get_earnings_and_type
105 14-DEC-2006 ssouresr 115.19 Corrected main cursor in
106 get_earnings_and_type to remove dups
107 */
108 --
109 -- **********************************************************************
110 -- CALCULATE_PERIOD_EARNINGS
111 -- Description: This function performs proration for the startup elements
112 -- Regular Salary and Regular Wages. Proration occurs in the following
113 -- scenarios:
114 -- 1. Change of assignment status to a status which is unpaid
115 -- ie. unpaid leave, termination;
116 -- 2. Change of regular rate of pay
117 -- ie. could be a change in annual salary or hourly rate.
118 --
119 -- This function also calculates and returns the actual hours worked in the
120 -- period, vacation pay, sick pay, vacation hours, and sick hours.
121
122 FUNCTION Calculate_Period_Earnings (
123 p_bus_grp_id in NUMBER,
124 p_asst_id in NUMBER,
125 p_assignment_action_id in NUMBER,
126 p_payroll_id in NUMBER,
127 p_ele_entry_id in NUMBER,
128 p_tax_unit_id in NUMBER,
129 p_date_earned in DATE,
130 p_pay_basis in VARCHAR2,
131 p_inpval_name in VARCHAR2,
132 p_ass_hrly_figure in NUMBER,
133 p_period_start in DATE,
134 p_period_end in DATE,
135 p_work_schedule in VARCHAR2,
136 p_asst_std_hrs in NUMBER,
137 p_actual_hours_worked in out nocopy NUMBER,
138 p_prorate in VARCHAR2,
139 p_asst_std_freq in VARCHAR2)
140 RETURN NUMBER IS
141 --
142 -- local vars
143 --
144 l_asg_info_changes NUMBER(1);
145 l_eev_info_changes NUMBER(1);
146 v_earnings_entry NUMBER(27,7);
147 v_inpval_id NUMBER(9);
148 v_pay_basis VARCHAR2(80);
149 v_pay_basis_code VARCHAR2(80);
150 v_pay_periods_per_year NUMBER(3);
151 v_period_earn NUMBER(27,7) := 0; -- Pay Period earnings.
152 v_hourly_earn NUMBER(27,7); -- Hourly Rate (earnings).
153 v_prorated_earnings NUMBER(27,7) := 0; -- Calc'd thru proration loops.
154 v_curr_day VARCHAR2(3); -- Currday while summing hrs for range of dates.
155 v_hrs_per_wk NUMBER(15,7);
156 v_hrs_per_range NUMBER(15,7);
157 v_asst_std_hrs NUMBER(15,7);
158 v_asst_std_freq VARCHAR2(30);
159 v_asst_std_freq_code VARCHAR2(30);
160 v_asg_status VARCHAR2(30);
161 v_hours_in_range NUMBER(15,7);
162 v_curr_hrly_rate NUMBER(27,7) := 0;
163 v_range_start DATE; -- range start of ASST rec
164 v_range_end DATE; -- range end of ASST rec
165 v_entry_start DATE; -- start date of ELE ENTRY rec
166 v_entry_end DATE; -- end date of ELE ENTRY rec
167 v_entrange_start DATE; -- max of entry or asst range start
168 v_entrange_end DATE; -- min of entry or asst range end
169 v_work_schedule VARCHAR2(60); -- Work Schedule ID (stored as varchar2
170 -- in HR_SOFT_CODING_KEYFLEX; convert
171 -- to_number when calling wshours fn.
172 v_work_sched_name VARCHAR2(80);
173 v_ws_id NUMBER(9);
174
175 b_entries_done BOOLEAN; -- flags no more entry changes in paypd
176 b_asst_changed BOOLEAN; -- flags if asst changes at least once.
177 b_on_work_schedule BOOLEAN; -- use wrk scheds or std hours
178 l_mid_period_asg_change BOOLEAN := FALSE;
179
180 lv_gre_type varchar2(80) := NULL;
181 v_return_status NUMBER;
182 v_return_message VARCHAR2(500);
183 v_schedule_source varchar2(100);
184 v_schedule varchar2(200);
185 v_total_hours NUMBER(15,7) ;
186
187 /*
188 -- ************************************************************************
189 --
190 -- The following cursor "get_asst_chgs" looks for *changes* to or from
191 -- 'ACTIVE' per_assignment
192 -- records within the supplied range of dates, *WITHIN THE SAME TAX UNIT*
193 -- (ie. the tax unit as of the end of the period specified).
194 -- If no "changes" are found, then assignment information is consistent
195 -- over entire period specified.
196 -- Before calling this cursor, will need to select tax_unit_name
197 -- according to p_tax_unit_id.
198 --
199 -- ************************************************************************
200 */
201
202 --
203 -- This cursor finds ALL ASG records that are WITHIN Period Start and End Dates
204 -- including Period End Date - NOT BETWEEN since the ASG record existing across
205 -- Period Start date has already been retrieved in SELECT (ASG1).
206 -- Work Schedule segment is segment4 on assignment DDF
207 --
208
209 CURSOR get_asst_chgs IS
210 SELECT ASG.effective_start_date,
211 ASG.effective_end_date,
212 NVL(ASG.normal_hours, 0),
213 NVL(HRL.meaning, 'NOT ENTERED'),
214 NVL(HRL.lookup_code, 'NOT ENTERED'),
215 NVL(SCL.segment4, 'NOT ENTERED')
216 FROM per_assignments_f ASG,
217 per_assignment_status_types AST,
218 hr_soft_coding_keyflex SCL,
219 hr_lookups HRL
220 WHERE ASG.assignment_id = p_asst_id
221 AND ASG.business_group_id + 0 = p_bus_grp_id
222 AND ASG.effective_start_date > p_period_start
223 AND ASG.effective_end_date <= p_period_end
224 AND AST.assignment_status_type_id = ASG.assignment_status_type_id
225 AND AST.per_system_status = 'ACTIVE_ASSIGN'
226 AND SCL.soft_coding_keyflex_id = ASG.soft_coding_keyflex_id
227 AND decode(lv_gre_type, 'T4A/RL1', segment11, 'T4A/RL2', SCL.segment12,
228 SCL.segment1) = to_char(p_tax_unit_id)
229 AND SCL.enabled_flag = 'Y'
230 AND HRL.lookup_code(+) = ASG.frequency
231 AND HRL.lookup_type(+) = 'FREQUENCY';
232 --
233 -- 25 May 1994:
234 -- Changing ASG status check to be for Payroll Status of 'Process'
235 -- AND AST.per_system_status = 'ACTIVE_ASSIGN'
236 -- AND AST.pay_system_status = 'P'
237 -- 12 Jul 1994: Now changing back!
238 -- Here's the deal:
239 -- 1) PAY_SYSTEM_STATUS needs to be 'P' in order for the payroll run to
240 -- pick up the asg for processing.
241 -- 2) The Proration function will look at 'ACTIVE_ASSIGN' *PER_SYSTEM_STATUS*
242 -- asgs for purposes of "paying" someone - ie. just b/c the "Pay Status"
243 -- is process for the asg status DOES NOT mean the person is to be paid!
244 -- ISSUE: How else can we determine whether or not to pay someone via
245 -- assignment status types? Ron doesn't like the fact that a user
246 -- status name of "Leave with Pay" has to have a PER_SYSTEM_STATUS of
247 -- 'ACTIVE_ASSIGN'...and a PAY_SYSTEM_STATUS of 'Process'.
248 -- So it currently comes down to this - all assignment statuses must have a payroll system
249 -- status of 'P' in order to be processed by Oracle Payroll. If the status is to be "with pay", then
250 -- the personnel system status MUST BE 'ACTIVE_ASSIGN'; an asg status "without pay" will
251 -- have a per system status of "SUSP_ASSIGN" or "TERM_ASSIGN".
252 --
253
254 FUNCTION Prorate_Earnings (
255 p_bg_id IN NUMBER,
256 p_assignment_id IN NUMBER,
257 p_assignment_action_id IN NUMBER,
258 p_element_entry_id IN NUMBER,
259 p_date_earned IN DATE,
260 p_asg_hrly_rate IN NUMBER,
261 p_wsched IN VARCHAR2 DEFAULT 'NOT ENTERED' ,
262 p_asg_std_hours IN NUMBER,
263 p_asg_std_freq IN VARCHAR2,
264 p_range_start_date IN DATE,
265 p_range_end_date IN DATE,
266 p_act_hrs_worked IN OUT NOCOPY NUMBER) RETURN NUMBER IS
267
268 v_prorated_earn NUMBER(27,7) := 0; -- RETURN var
269 v_hours_in_range NUMBER(15,7);
270 v_ws_id NUMBER(9);
271 v_ws_name VARCHAR2(80);
272
273 v_return_status NUMBER;
274 v_return_message VARCHAR2(500);
275 v_schedule_source varchar2(100);
276 v_schedule varchar2(200);
277 v_total_hours NUMBER(15,7) ;
278
279 BEGIN
280
281 hr_utility.set_location('Pro_Earn: actual hours worked IN = ', p_act_hrs_worked);
282 --
283 -- Prorate using hourly rate passed in as param:
284 --
285 /*
286 IF UPPER(p_wsched) = 'NOT ENTERED' THEN
287
288 hr_utility.set_location('Prorate_Earnings', 7);
289 v_hours_in_range := Standard_Hours_Worked( p_asg_std_hours,
290 p_range_start_date,
291 p_range_end_date,
292 p_asg_std_freq);
293
294 -- Keep running total of ACTUAL hours worked.
295
296 hr_utility.set_location('Prorate_Earnings', 11);
297 p_act_hrs_worked := p_act_hrs_worked + v_hours_in_range;
298 hr_utility.set_location('actual_hours_worked = ', p_act_hrs_worked);
299
300 ELSE
301
302 hr_utility.set_location('Prorate_Earnings', 17);
303
304 -- Get work schedule name:
305 -- v_ws_id := to_number(p_wsched);
306 v_ws_id := fnd_number.canonical_to_number(p_wsched);
307
308 SELECT user_column_name
309 INTO v_ws_name
310 FROM pay_user_columns
311 WHERE user_column_id = v_ws_id
312 AND NVL(legislation_code,'CA') = 'CA';
313
314 hr_utility.set_location('p_range_start_date='||to_char(p_range_start_date), 19);
315 hr_utility.set_location('p_range_end_date='||to_char(p_range_end_date), 19);
316
317 hr_utility.set_location('calling core udfs', 44);
318 */
319 v_hours_in_range := PAY_CORE_FF_UDFS.calculate_actual_hours_worked (
320 p_assignment_action_id
321 ,p_assignment_id
322 ,p_bg_id
323 ,p_element_entry_id
324 ,p_date_earned
325 ,p_range_start_date
326 ,p_range_end_date
327 ,NULL
328 ,'Y'
329 ,'BUSY'
330 ,'CA'--p_legislation_code
331 ,v_schedule_source
332 ,v_schedule
333 ,v_return_status
334 ,v_return_message);
335
336 p_act_hrs_worked := p_act_hrs_worked + v_hours_in_range;
337 hr_utility.set_location('actual_hours_worked = ', p_act_hrs_worked);
338
339 -- Hours in date range via work schedule or std hours.
340 /* END IF;*/
341
342 v_prorated_earn := v_prorated_earn + (p_asg_hrly_rate * v_hours_in_range);
343 hr_utility.set_location('v_prorated_earnings = ', v_prorated_earn);
344 hr_utility.set_location('Prorate_Earnings', 97);
345 p_act_hrs_worked := ROUND(p_act_hrs_worked, 3);
346 hr_utility.set_location('Pro_Earn: actual hours worked OUT = ', p_act_hrs_worked);
347 RETURN v_prorated_earn;
348
349 END Prorate_Earnings;
350
351 FUNCTION Prorate_EEV ( p_bus_group_id IN NUMBER,
352 p_assignment_id IN NUMBER,
353 p_assignment_action_id IN NUMBER,
354 p_date_earned IN DATE,
355 p_pay_id IN NUMBER,
356 p_work_sched IN VARCHAR2 DEFAULT 'NOT ENTERED',
357 p_asg_std_hrs IN NUMBER,
358 p_asg_std_freq IN VARCHAR2,
359 p_pay_basis IN VARCHAR2,
360 p_hrly_rate IN OUT NOCOPY NUMBER,
361 p_range_start_date IN DATE,
362 p_range_end_date IN DATE,
363 p_actual_hrs_worked IN OUT NOCOPY NUMBER,
364 p_element_entry_id IN NUMBER,
365 p_inpval_id IN NUMBER) RETURN NUMBER IS
366 --
367 -- local vars
368 --
369 v_eev_prorated_earnings NUMBER(27,7) := 0; -- Calc'd thru proration loops.
370 v_earnings_entry VARCHAR2(60);
371 v_entry_start DATE;
372 v_entry_end DATE;
373 v_hours_in_range NUMBER(15,7);
374 v_curr_hrly_rate NUMBER(27,7);
375 v_ws_id NUMBER(9);
376 v_ws_name VARCHAR2(80);
377 --
378 -- Select for ALL records that are WITHIN Range Start and End Dates
379 -- including Range End Date - NOT BETWEEN since the EEV record existing across
380 -- Range Start date has already been retrieved and dealt with in SELECT (EEV1).
381 -- A new EEV record results in a change of the current hourly rate being used
382 -- in proration calculation.
383 --
384 CURSOR get_entry_chgs ( p_range_start date,
385 p_range_end date) IS
386 SELECT EEV.screen_entry_value,
387 EEV.effective_start_date,
388 EEV.effective_end_date
389 FROM pay_element_entry_values_f EEV
390 WHERE EEV.element_entry_id = p_element_entry_id
391 AND EEV.input_value_id = p_inpval_id
392 AND EEV.effective_start_date > p_range_start
393 AND EEV.effective_end_date <= p_range_end
394 ORDER BY EEV.effective_start_date;
395 --
396 BEGIN
397 --
398 --
399 -- Find all EEV changes, calculate new hourly rate, prorate:
400 -- SELECT (EEV1):
401 -- Select for SINGLE record that includes Period Start Date but does not
402 -- span entire period.
403 -- We know this select will return a row, otherwise there would be no
404 -- EEV changes to detect.
405 --
406 hr_utility.set_location('Prorate_EEV', 103);
407 SELECT EEV.screen_entry_value,
408 GREATEST(EEV.effective_start_date, p_range_start_date),
409 EEV.effective_end_date
410 INTO v_earnings_entry,
411 v_entry_start,
412 v_entry_end
413 FROM pay_element_entry_values_f EEV
414 WHERE EEV.element_entry_id = p_element_entry_id
415 AND EEV.input_value_id = p_inpval_id
416 AND EEV.effective_start_date <= p_range_start_date
417 AND EEV.effective_end_date >= p_range_start_date
418 AND EEV.effective_end_date < p_range_end_date;
419 --
420 hr_utility.set_location('Prorate_EEV', 105);
421 hr_utility.set_location('p_asg_std_hrs=', p_asg_std_hrs);
422 hr_utility.set_location('p_pay_basis='||p_pay_basis, 105);
423 hr_utility.set_location('v_earnings_entry='||v_earnings_entry, 105);
424
425 hr_utility.set_location('calling core udfs', 45);
426 v_curr_hrly_rate := PAY_CORE_FF_UDFS.get_hourly_rate(
427 p_bus_group_id
428 ,p_assignment_id
429 ,p_pay_id
430 ,p_element_entry_id
431 ,p_date_earned
432 ,p_assignment_action_id );
433 --
434 hr_utility.set_location('v_curr_hrly_rate = ', v_curr_hrly_rate);
435 --
436 hr_utility.set_location('Prorate_EEV', 107);
437 hr_utility.set_location('v_entry_start='||to_char(v_entry_start), 107);
438 hr_utility.set_location('v_entry_end='||to_char(v_entry_end), 107);
439
440 v_eev_prorated_earnings := v_eev_prorated_earnings +
441 Prorate_Earnings (
442 p_bg_id => p_bus_group_id,
443 p_assignment_id => p_assignment_id,
444 p_assignment_action_id => p_assignment_action_id,
445 p_element_entry_id => p_element_entry_id,
446 p_date_earned => p_date_earned,
447 p_asg_hrly_rate => v_curr_hrly_rate,
448 p_wsched => p_work_sched,
449 p_asg_std_hours => p_asg_std_hrs,
450 p_asg_std_freq => p_asg_std_freq,
451 p_range_start_date => v_entry_start,
452 p_range_end_date => v_entry_end,
453 p_act_hrs_worked => p_actual_hrs_worked);
454 --
455 hr_utility.set_location('Prorate_EEV.v_eev_prorated_earnings = ', v_eev_prorated_earnings);
456 --
457 -- SELECT (EEV2):
458 OPEN get_entry_chgs (p_range_start_date, p_range_end_date);
459 LOOP
460 --
461 FETCH get_entry_chgs
462 INTO v_earnings_entry,
463 v_entry_start,
464 v_entry_end;
465 EXIT WHEN get_entry_chgs%NOTFOUND;
466 --
467 hr_utility.set_location('Prorate_EEV', 115);
468 --
469 -- For each range of dates found, add to running prorated earnings total.
470 --
471 hr_utility.set_location('Prorate_EEV', 117);
472
473 hr_utility.set_location('calling core udfs', 46);
474 v_curr_hrly_rate := PAY_CORE_FF_UDFS.get_hourly_rate(
475 p_bus_group_id
476 ,p_assignment_id
477 ,p_pay_id
478 ,p_element_entry_id
479 ,p_date_earned
480 ,p_assignment_action_id );
481
482 hr_utility.set_location('v_curr_hrly_rate = ', v_curr_hrly_rate);
483 hr_utility.set_location('Prorate_EEV', 119);
484 --
485 v_eev_prorated_earnings := v_eev_prorated_earnings +
486 Prorate_Earnings (
487 p_bg_id => p_bus_group_id,
488 p_assignment_id => p_assignment_id,
489 p_assignment_action_id => p_assignment_action_id,
490 p_element_entry_id => p_element_entry_id,
491 p_date_earned => p_date_earned,
492 p_asg_hrly_rate => v_curr_hrly_rate,
493 p_wsched => p_work_sched,
494 p_asg_std_hours => p_asg_std_hrs,
495 p_asg_std_freq => p_asg_std_freq,
496 p_range_start_date => v_entry_start,
497 p_range_end_date => v_entry_end,
498 p_act_hrs_worked => p_actual_hrs_worked);
499 --
500 hr_utility.set_location('Prorate_EEV.v_eev_prorated_earnings = ', v_eev_prorated_earnings);
501 --
502 END LOOP;
503 --
504 CLOSE get_entry_chgs;
505 --
506 -- SELECT (EEV3)
507 -- Select for SINGLE record that exists across Period End Date:
508 -- NOTE: Will only return a row if select (2) does not return a row where
509 -- Effective End Date = Period End Date !
510 --
511 hr_utility.set_location('Prorate_EEV', 141);
512 SELECT EEV.screen_entry_value,
513 EEV.effective_start_date,
514 LEAST(EEV.effective_end_date, p_range_end_date)
515 INTO v_earnings_entry,
516 v_entry_start,
517 v_entry_end
518 FROM pay_element_entry_values_f EEV
519 WHERE EEV.element_entry_id = p_element_entry_id
520 AND EEV.input_value_id = p_inpval_id
521 AND EEV.effective_start_date > p_range_start_date
522 AND EEV.effective_start_date <= p_range_end_date
523 AND EEV.effective_end_date > p_range_end_date;
524 --
525 hr_utility.set_location('Prorate_EEV', 147);
526 hr_utility.set_location('calling core udfs', 47);
527 v_curr_hrly_rate := PAY_CORE_FF_UDFS.get_hourly_rate(
528 p_bus_group_id
529 ,p_assignment_id
530 ,p_pay_id
531 ,p_element_entry_id
532 ,p_date_earned
533 ,p_assignment_action_id );
534
535 hr_utility.set_location('v_curr_hrly_rate = ', v_curr_hrly_rate);
536 hr_utility.set_location('Prorate_EEV', 151);
537
538 v_eev_prorated_earnings := v_eev_prorated_earnings +
539 Prorate_Earnings (
540 p_bg_id => p_bus_group_id,
541 p_assignment_id => p_assignment_id,
542 p_assignment_action_id => p_assignment_action_id,
543 p_element_entry_id => p_element_entry_id,
544 p_date_earned => p_date_earned,
545 p_asg_hrly_rate => v_curr_hrly_rate,
546 p_wsched => p_work_sched,
547 p_asg_std_hours => p_asg_std_hrs,
548 p_asg_std_freq => p_asg_std_freq,
549 p_range_start_date => v_entry_start,
550 p_range_end_date => v_entry_end,
551 p_act_hrs_worked => p_actual_hrs_worked);
552
553 hr_utility.set_location('Prorate_EEV.v_eev_prorated_earnings = ', v_eev_prorated_earnings);
554 -- We're Done!
555 hr_utility.set_location('Prorate_EEV', 167);
556 p_actual_hrs_worked := ROUND(p_actual_hrs_worked, 3);
557 p_hrly_rate := v_curr_hrly_rate;
558 RETURN v_eev_prorated_earnings;
559
560 EXCEPTION WHEN NO_DATA_FOUND THEN
561
562 hr_utility.set_location('Prorate_EEV', 177);
563 p_actual_hrs_worked := ROUND(p_actual_hrs_worked, 3);
564 p_hrly_rate := v_curr_hrly_rate;
565 RETURN v_eev_prorated_earnings;
566
567 END Prorate_EEV;
568
569 FUNCTION vacation_pay ( p_vac_hours IN OUT NOCOPY NUMBER,
570 p_asg_id IN NUMBER,
571 p_eff_date IN DATE,
572 p_curr_rate IN NUMBER) RETURN NUMBER IS
573
574 l_vac_pay NUMBER(27,7) DEFAULT 0;
575 l_vac_hours NUMBER(10,7);
576
577 CURSOR get_vac_hours ( v_asg_id NUMBER,
578 v_eff_date DATE) IS
579 select pev.screen_entry_value
580 from per_absence_attendance_types abt,
581 pay_element_entries_f pee,
582 pay_element_entry_values_f pev
583 where pev.input_value_id = abt.input_value_id
584 and abt.absence_category = 'V'
585 and v_eff_date between pev.effective_start_date
586 and pev.effective_end_date
587 and pee.element_entry_id = pev.element_entry_id
588 and pee.assignment_id = v_asg_id
589 and v_eff_date between pee.effective_start_date
590 and pee.effective_end_date;
591
592 -- The "vacation_pay" function looks for hours entered against absence types
593 -- in the current period. The number of hours are summed and multiplied by
594 -- the current rate of Regular Pay..
595 -- Return immediately when no vacation time has been taken.
596 -- Need to loop thru all "Vacation Plans" and check for entries in the current
597 -- period for this assignment.
598
599 BEGIN
600 hr_utility.set_location('get_vac_pay', 11);
601 OPEN get_vac_hours (p_asg_id, p_eff_date);
602 LOOP
603
604 hr_utility.set_location('get_vac_pay', 13);
605 FETCH get_vac_hours
606 INTO l_vac_hours;
607 EXIT WHEN get_vac_hours%NOTFOUND;
608
609 p_vac_hours := p_vac_hours + l_vac_hours;
610
611 END LOOP;
612 CLOSE get_vac_hours;
613 hr_utility.set_location('get_vac_pay', 15);
614
615 IF p_vac_hours <> 0 THEN
616
617 l_vac_pay := p_vac_hours * p_curr_rate;
618
619 END IF;
620
621 RETURN l_vac_pay;
622
623 END vacation_pay;
624
625 FUNCTION sick_pay ( p_sick_hours IN OUT NOCOPY NUMBER,
626 p_asg_id IN NUMBER,
627 p_eff_date IN DATE,
628 p_curr_rate IN NUMBER) RETURN NUMBER IS
629
630 l_sick_pay NUMBER(27,7) DEFAULT 0;
631 l_sick_hours NUMBER(10,7);
632
633 CURSOR get_sick_hours ( v_asg_id NUMBER,
634 v_eff_date DATE) IS
635 select pev.screen_entry_value
636 from per_absence_attendance_types abt,
637 pay_element_entries_f pee,
638 pay_element_entry_values_f pev
639 where pev.input_value_id = abt.input_value_id
640 and abt.absence_category = 'S'
641 and v_eff_date between pev.effective_start_date
642 and pev.effective_end_date
643 and pee.element_entry_id = pev.element_entry_id
644 and pee.assignment_id = v_asg_id
645 and v_eff_date between pee.effective_start_date
646 and pee.effective_end_date;
647
648 -- The "sick_pay" function looks for hours entered against Sick absence types in
649 -- the current period. The number of hours are summed and multiplied by the
650 -- current rate of Regular Pay.
651 -- Return immediately when no sick time has been taken.
652
653 BEGIN
654
655 hr_utility.set_location('get_sick_pay', 11);
656 OPEN get_sick_hours (p_asg_id, p_eff_date);
657 LOOP
658
659 hr_utility.set_location('get_sick_pay', 13);
660 FETCH get_sick_hours
661 INTO l_sick_hours;
662 EXIT WHEN get_sick_hours%NOTFOUND;
663
664 p_sick_hours := p_sick_hours + l_sick_hours;
665
666 END LOOP;
667 CLOSE get_sick_hours;
668 hr_utility.set_location('get_sick_pay', 15);
669
670 IF p_sick_hours <> 0 THEN
671
672 l_sick_pay := p_sick_hours * p_curr_rate;
673
674 END IF;
675
676 RETURN l_sick_pay;
677
678 END sick_pay;
679
680 BEGIN -- Calculate_Period_Earnings
681
682 /* Getting GRE Type of tax unit for Multi GRE functionality
683 Based on the gre type, the segment will be used in where clause.
684
685 T4/RL1 - Segment1
686 T4A/RL1 - Segment11
687 T4A/RL2 - Segment12
688 */
689
690 begin
691 select org_information5
692 into lv_gre_type
693 from hr_organization_information hoi
694 where hoi.org_information_context = 'Canada Employer Identification'
695 and hoi.organization_id = p_tax_unit_id;
696
697 exception
698 when others then
699 null;
700
701 end;
702
703 -- init out param
704 p_actual_hours_worked := 0;
705
706 -- Step (1): Find earnings element input value.
707 -- Get input value and pay basis according to salary admin (if exists).
708 -- If not using salary admin, then get "Rate", "Rate Code", or "Monthly Salary"
709 -- input value id as appropriate (according to ele name).
710
711 IF p_pay_basis IS NOT NULL THEN
712 begin
713 hr_utility.set_location('calculate_period_earnings', 10);
714 SELECT PYB.input_value_id,
715 FCL.meaning,
716 FCL.lookup_code
717 INTO v_inpval_id,
718 v_pay_basis,
719 v_pay_basis_code
720 FROM per_assignments_f ASG,
721 per_pay_bases PYB,
722 hr_lookups FCL
723 WHERE FCL.lookup_code = PYB.pay_basis
724 AND FCL.lookup_type = 'PAY_BASIS'
725 AND FCL.application_id = 800
726 AND PYB.pay_basis_id = ASG.pay_basis_id
727 AND ASG.assignment_id = p_asst_id
728 AND p_date_earned
729 BETWEEN ASG.effective_start_date
730 AND ASG.effective_end_date;
731
732 EXCEPTION WHEN NO_DATA_FOUND THEN
733
734 hr_utility.set_location('calculate_period_earnings', 11);
735 v_period_earn := 0;
736 p_actual_hours_worked := ROUND(p_actual_hours_worked, 3);
737 RETURN v_period_earn;
738
739 -- hr_utility.set_message(801, 'PAY_xxxx_NO_ASST_IN_PERIOD');
740 -- hr_utility.raise_error;
741
742 END;
743
744 ELSIF UPPER(p_inpval_name) = 'RATE' THEN
745 begin
746 hr_utility.set_location('calculate_period_earnings', 13);
747 SELECT IPV.input_value_id
748 INTO v_inpval_id
749 FROM pay_input_values_f IPV,
750 pay_element_types_f ELT
751 WHERE UPPER(ELT.element_name) = 'REGULAR WAGES'
752 AND ELT.element_type_id = IPV.element_type_id
753 AND p_period_start BETWEEN IPV.effective_start_date
754 AND IPV.effective_end_date
755 AND UPPER(IPV.name) = 'RATE';
756 --
757 v_pay_basis := 'HOURLY';
758 --
759 EXCEPTION WHEN NO_DATA_FOUND THEN
760 hr_utility.set_location('calculate_period_earnings', 14);
761 v_period_earn := 0;
762 p_actual_hours_worked := ROUND(p_actual_hours_worked, 3);
763 RETURN v_period_earn;
764 -- hr_utility.set_message(801, 'PAY_xxx_REGWAGE_RATE_NOTFOUND');
765 -- hr_utility.raise_error;
766 end;
767 --
768 ELSIF UPPER(p_inpval_name) = 'RATE CODE' THEN
769 begin
770 hr_utility.set_location('calculate_period_earnings', 15);
771 SELECT IPV.input_value_id
772 INTO v_inpval_id
773 FROM pay_input_values_f IPV,
774 pay_element_types_f ELT
775 WHERE UPPER(ELT.element_name) = 'REGULAR WAGES'
776 AND ELT.element_type_id = IPV.element_type_id
777 AND p_period_start BETWEEN IPV.effective_start_date
778 AND IPV.effective_end_date
779 AND UPPER(IPV.name) = 'RATE CODE';
780 --
781 v_pay_basis := 'HOURLY';
782 --
783 EXCEPTION WHEN NO_DATA_FOUND THEN
784 hr_utility.set_location('calculate_period_earnings', 16);
785 v_period_earn := 0;
786 p_actual_hours_worked := ROUND(p_actual_hours_worked, 3);
787 RETURN v_period_earn;
788 -- hr_utility.set_message(801, 'PAY_xxx_REGWAGE_RATECODE_NOTFOUND');
789 -- hr_utility.raise_error;
790 end;
791 --
792 ELSIF UPPER(p_inpval_name) = 'MONTHLY SALARY' THEN
793 begin
794 hr_utility.set_location('calculate_period_earnings', 17);
795 SELECT IPV.input_value_id
796 INTO v_inpval_id
797 FROM pay_input_values_f IPV,
798 pay_element_types_f ELT
799 WHERE UPPER(ELT.element_name) = 'REGULAR SALARY'
800 AND ELT.element_type_id = IPV.element_type_id
801 AND p_period_start BETWEEN IPV.effective_start_date
802 AND IPV.effective_end_date
803 AND UPPER(IPV.name) = 'MONTHLY SALARY';
804
805 v_pay_basis := 'MONTHLY';
806
807 EXCEPTION WHEN NO_DATA_FOUND THEN
808 hr_utility.set_location('calculate_period_earnings', 18);
809 v_period_earn := 0;
810 p_actual_hours_worked := ROUND(p_actual_hours_worked, 3);
811 RETURN v_period_earn;
812 -- hr_utility.set_message(801, 'PAY_xxx_REGSAL_NOTFOUND');
813 -- hr_utility.raise_error;
814 END;
815
816 END IF;
817
818 /*
819 -- Now know the pay basis for this assignment (v_pay_basis).
820 -- Want to convert entered earnings to pay period earnings.
821 -- For pay basis of Annual, Monthly, Bi-Weekly, Semi-Monthly, or Period (ie. anything
822 -- other than Hourly):
823 -- Annualize entered earnings according to pay basis;
824 -- then divide by number of payroll periods per fiscal yr for pay period earnings.
825 -- 02 Dec 1993:
826 -- Actually, passing in an "Hourly" figure from formula alleviates
827 -- having to convert in here --> we have Convert_Period_Type function
828 -- available to formulae, so a Monthly Salary can be converted before
829 -- calling this fn. Then we just find the hours scheduled for current period as
830 -- per the Hourly pay basis algorithm below.
831 --
832 -- For Hourly pay basis:
833 -- Get hours scheduled for the current period either from:
834 -- 1. ASG work schedule
835 -- 2. ORG default work schedule
836 -- 3. ASG standard hours and frequency
837 -- Multiply the hours scheduled for period by normal Hourly Rate (ie. from
838 -- pre-defined earnings, REGULAR_WAGES_RATE) pay period earnings.
839 --
840 -- In either case, need to find the payroll period type, let's do it upfront:
841 -- Assignment.payroll_id --> Payroll.period_type
842 -- --> Per_time_period_types.number_per_fiscal_year.
843 -- Actually, the number per fiscal year could be found in more than one way:
844 -- Could also go to per_time_period_rules, but would mean decoding the
845 -- payroll period type to an appropriate proc_period_type code.
846 --
847 */
848
849 -- Find # of payroll period types per fiscal year:
850
851 begin
852
853 hr_utility.set_location('calculate_period_earnings', 40);
854 SELECT TPT.number_per_fiscal_year
855 INTO v_pay_periods_per_year
856 FROM pay_payrolls_f PRL,
857 per_time_period_types TPT
858 WHERE TPT.period_type = PRL.period_type
859 AND p_period_end between PRL.effective_start_date
860 and PRL.effective_end_date
861 AND PRL.payroll_id = p_payroll_id
862 AND PRL.business_group_id + 0 = p_bus_grp_id;
863
864 exception when NO_DATA_FOUND then
865
866 hr_utility.set_location('calculate_period_earnings', 41);
867 v_period_earn := 0;
868 p_actual_hours_worked := ROUND(p_actual_hours_worked, 3);
869 RETURN v_period_earn;
870 -- hr_utility.set_message(801, 'PAY_xxxx_NUM_PER_FY_NOT_FOUND');
871 -- hr_utility.raise_error;
872
873 end;
874
875 /*
876 -- Pay basis is hourly,
877 -- Get hours scheduled for the current period either from:
878 -- 1. ASG work schedule
879 -- 2. ORG default work schedule
880 -- 3. ASG standard hours and frequency
881 -- Do we pass in Work Schedule from asst scl db item? Yes
882 -- 10-JAN-1996 hparicha : We no longer assume "standard hours" represent
883 -- a weekly figure. We also no longer use a week as the basis for annualization,
884 -- even when using work schedule - ie. need to find ACTUAL scheduled hours, not
885 -- actual hours for a week, converted to a period figure.
886 */
887 --
888 hr_utility.set_location('calculate_period_earnings', 45);
889 /* IF p_work_schedule <> 'NOT ENTERED' THEN
890 --
891 -- Find hours worked between period start and end dates.
892 --
893
894 -- v_ws_id := to_number(p_work_schedule);
895 v_ws_id := fnd_number.canonical_to_number(p_work_schedule);
896
897 --
898 SELECT user_column_name
899 INTO v_work_sched_name
900 FROM pay_user_columns
901 WHERE user_column_id = v_ws_id
902 AND NVL(business_group_id, p_bus_grp_id) = p_bus_grp_id
903 AND NVL(legislation_code,'CA') = 'CA';
904 --
905 */
906 hr_utility.set_location('calling core udfs', 45);
907 v_hrs_per_range := PAY_CORE_FF_UDFS.calculate_actual_hours_worked (
908 p_assignment_action_id
909 ,p_asst_id
910 ,p_bus_grp_id
911 ,p_ele_entry_id
912 ,p_date_earned
913 ,p_period_start
914 ,p_period_end
915 ,NULL
916 ,'Y'
917 ,'BUSY'
918 ,'CA'--p_legislation_code
919 ,v_schedule_source
920 ,v_schedule
921 ,v_return_status
922 ,v_return_message);
923 /*
924 ELSE
925
926 v_hrs_per_range := Standard_Hours_Worked( p_asst_std_hrs,
927 p_period_start,
928 p_period_end,
929 p_asst_std_freq);
930
931 END IF;
932 */
933
934 -- Compute earnings and actual hours PER PAY PERIOD.
935 -- Convert HOURLY earnings to PERIOD earnings.
936 -- Passing "NULL" freq to Convert_Period_Type will convert
937 -- to/from the payroll time period type.
938
939 hr_utility.set_location('calculate_period_earnings', 46);
940 v_period_earn := Convert_Period_Type( p_bus_grp_id,
941 p_payroll_id,
942 p_assignment_action_id,
943 p_asst_id ,
944 p_ele_entry_id,
945 p_date_earned ,
946 p_work_schedule,
947 p_asst_std_hrs,
948 p_ass_hrly_figure,
949 'HOURLY',
950 NULL,
951 p_period_start,
952 p_period_end,
953 p_asst_std_freq);
954
955 hr_utility.set_location('calculate_period_earnings', 47);
956
957 p_actual_hours_worked := v_hrs_per_range;
958 hr_utility.set_location('Calc_PE actual_hours_worked = ', p_actual_hours_worked);
959
960 -- Check that Pro-rate = 'Y' before continuing.
961
962 IF p_prorate = 'N' THEN
963
964 -- Done!!! No pro-ration...
965 hr_utility.set_location('calculate_period_earnings', 49);
966 p_actual_hours_worked := ROUND(p_actual_hours_worked, 3);
967
968 RETURN v_period_earn;
969
970 END IF;
971
972 -- Before going into proration, check for ASG and EEV changes within
973 -- period dates -> if none are found then return v_period_earn and we're done!
974 -- This is the 80-90% case so we want to get out as quickly as possible.
975
976 -- (ASG1) Select for SINGLE record that includes Period Start Date but does not
977 -- span entire period. If no row returned, then ASG record spans period
978 -- and there is no need to run selects (ASG2) or (ASG3).
979
980 hr_utility.set_location('calculate_period_earnings', 51);
981
982 /* ************************************************************** */
983
984 begin
985
986 select 1 INTO l_asg_info_changes
987 from dual
988 where exists (
989 SELECT 1
990 FROM per_assignments_f ASG,
991 per_assignment_status_types AST,
992 hr_soft_coding_keyflex SCL
993 WHERE ASG.assignment_id = p_asst_id
994 AND ASG.effective_start_date <= p_period_start
995 AND ASG.effective_end_date >= p_period_start
996 AND ASG.effective_end_date < p_period_end
997 AND AST.assignment_status_type_id = ASG.assignment_status_type_id
998 AND AST.per_system_status = 'ACTIVE_ASSIGN'
999 AND SCL.soft_coding_keyflex_id = ASG.soft_coding_keyflex_id
1000 AND decode(lv_gre_type, 'T4A/RL1', segment11, 'T4A/RL2', segment12,
1001 SCL.segment1) = to_char(p_tax_unit_id)
1002 AND SCL.enabled_flag = 'Y');
1003
1004 -- l_asg_info_changes := 1;
1005
1006 -- 25 May 1994:
1007 -- Changing ASG status check to be for Payroll Status of 'Process'
1008 -- AND AST.per_system_status = 'ACTIVE_ASSIGN'
1009 -- AND AST.pay_system_status = 'P'
1010
1011 -- Need to prorate b/c of ASG changes, but let's look for EEV changes
1012 -- for future reference:
1013
1014 l_mid_period_asg_change := TRUE;
1015
1016 hr_utility.set_location('calculate_period_earnings', 56);
1017
1018 begin
1019
1020 select 1 INTO l_eev_info_changes
1021 from dual
1022 where exists (
1023 SELECT 1
1024 FROM pay_element_entry_values_f EEV
1025 WHERE EEV.element_entry_id = p_ele_entry_id
1026 AND EEV.input_value_id+0 = v_inpval_id
1027 AND (
1028 (EEV.effective_start_date <= p_period_start
1029 AND EEV.effective_end_date >= p_period_start
1030 AND EEV.effective_end_date < p_period_end)
1031 OR (EEV.effective_start_date between p_period_start and p_period_end)
1032 )
1033 );
1034
1035 -- Prorate: l_asg_info_changes EXIST,
1036 -- l_eev_info_changes EXIST
1037
1038 exception
1039
1040 WHEN NO_DATA_FOUND THEN
1041 l_eev_info_changes := 0;
1042
1043 -- Prorate: l_asg_info_changes EXIST,
1044 -- l_eev_info_changes DO NOT EXIST
1045
1046 end;
1047
1048 exception
1049
1050 WHEN NO_DATA_FOUND THEN
1051
1052 l_asg_info_changes := 0;
1053
1054 end;
1055
1056 IF l_asg_info_changes = 0 THEN
1057
1058 -- (ASG1.1)
1059 -- Actually need to select for ASG record that STARTS within period
1060 -- ie. becomes active on a date later than start date!
1061
1062 hr_utility.set_location('calculate_period_earnings', 52);
1063
1064 begin
1065
1066 select 1 INTO l_asg_info_changes
1067 from dual
1068 where exists (
1069 SELECT 1
1070 FROM per_assignments_f ASG,
1071 per_assignment_status_types AST,
1072 hr_soft_coding_keyflex SCL
1073 WHERE ASG.assignment_id = p_asst_id
1074 AND ASG.effective_start_date > p_period_start
1075 AND ASG.effective_start_date <= p_period_end
1076 AND AST.assignment_status_type_id = ASG.assignment_status_type_id
1077 AND AST.per_system_status = 'ACTIVE_ASSIGN'
1078 AND SCL.soft_coding_keyflex_id = ASG.soft_coding_keyflex_id
1079 AND decode(lv_gre_type, 'T4A/RL1', SCL.segment11, 'T4A/RL2', SCL.segment12,
1080 SCL.segment1) = to_char(p_tax_unit_id)
1081 AND SCL.enabled_flag = 'Y');
1082
1083 l_mid_period_asg_change := TRUE;
1084
1085 -- Need to prorate b/c of mid period ASG changes, but let's look for
1086 -- EEV changes for future reference:
1087
1088 hr_utility.set_location('calculate_period_earnings', 55);
1089
1090 begin
1091
1092 select 1 INTO l_eev_info_changes
1093 from dual
1094 where exists (
1095 SELECT 1
1096 FROM pay_element_entry_values_f EEV
1097 WHERE EEV.element_entry_id = p_ele_entry_id
1098 AND EEV.input_value_id+0 = v_inpval_id
1099 AND EEV.effective_start_date <= p_period_start
1100 AND EEV.effective_end_date >= p_period_start
1101 AND EEV.effective_end_date < p_period_end);
1102
1103 -- Prorate: l_asg_info_changes EXIST,
1104 -- l_eev_info_changes EXIST
1105
1106 exception
1107
1108 WHEN NO_DATA_FOUND THEN
1109 l_eev_info_changes := 0;
1110
1111 -- Prorate: l_asg_info_changes EXIST,
1112 -- l_eev_info_changes DO NOT EXIST
1113
1114 end;
1115
1116 exception
1117
1118 WHEN NO_DATA_FOUND THEN
1119 l_asg_info_changes := 0;
1120
1121 end;
1122
1123 END IF;
1124
1125 IF l_asg_info_changes = 0 THEN -- Still
1126
1127 -- Check for EEV changes, if also none -> return v_period_earn and done:
1128 -- (EEV1) Select for SINGLE record that includes Period Start Date but
1129 -- does not span entire period. If no row returned, then EEV record
1130 -- spans period and there is no need to run selects (EEV2) or (EEV3)
1131 -- and we can stop now!
1132
1133 begin
1134
1135 hr_utility.set_location('calculate_period_earnings', 53);
1136
1137 select 1 INTO l_eev_info_changes
1138 from dual
1139 where exists (
1140 SELECT 1
1141 FROM pay_element_entry_values_f EEV
1142 WHERE EEV.element_entry_id = p_ele_entry_id
1143 AND EEV.input_value_id+0 = v_inpval_id
1144 AND EEV.effective_start_date <= p_period_start
1145 AND EEV.effective_end_date >= p_period_start
1146 AND EEV.effective_end_date < p_period_end);
1147
1148 -- Proration will occur for l_asg_info_changes DO NOT EXIST and
1149 -- l_eev_info_changes EXIST
1150
1151 exception
1152
1153 WHEN NO_DATA_FOUND THEN
1154 -- This is mainline fallthru point.
1155 -- l_asg_info_changes AND l_eev_info_changes DO NOT EXIST.
1156 -- Done!!! No pro-ration required b/c no ASG or EEV changes exist
1157 -- this period!
1158 -- Either there are no changes to an Active Assignment OR
1159 -- the assignment was not active at all this period.
1160 -- Check assignment status of current asg record.
1161
1162 SELECT AST.per_system_status
1163 INTO v_asg_status
1164 FROM per_assignments_f ASG,
1165 per_assignment_status_types AST,
1166 hr_soft_coding_keyflex SCL
1167 WHERE ASG.assignment_id = p_asst_id
1168 AND p_period_start BETWEEN ASG.effective_start_date
1169 AND ASG.effective_end_date
1170 AND AST.assignment_status_type_id = ASG.assignment_status_type_id
1171 AND SCL.soft_coding_keyflex_id = ASG.soft_coding_keyflex_id
1172 AND decode(lv_gre_type, 'T4A/RL1', segment11, 'T4A/RL2', SCL.segment12,
1173 SCL.segment1) = to_char(p_tax_unit_id)
1174 AND SCL.enabled_flag = 'Y';
1175
1176 IF v_asg_status <> 'ACTIVE_ASSIGN' THEN
1177
1178 v_period_earn := 0;
1179 p_actual_hours_worked := 0;
1180
1181 END IF;
1182
1183 p_actual_hours_worked := ROUND(p_actual_hours_worked, 3);
1184 RETURN v_period_earn;
1185
1186 end;
1187
1188 END IF;
1189
1190 /* ************************************************************** */
1191
1192 -- If code reaches here, then we're prorating for one reason or the other.
1193 -- We can check l_asg_info_changes and l_eev_info_changes to see what needs
1194 -- to be done.
1195 -- We'll use these flags in the interest of efficiency:
1196 -- l_asg_info_changes = 0 ==> No ASG changes
1197 -- l_eev_info_changes = 0 ==> No EEV changes
1198 --
1199 -- *********************************************************************
1200 -- Step (2): is Work_Schedule_Total_Hours(ws, from, to) code or fn call.
1201 -- *********************************************************************
1202 --
1203 -- *********************************************************************
1204 -- Step (3): Find pay period salary as hourly rate.
1205 -- 02 Dec 1993: We're passing in an hourly figure already!!!
1206 -- *********************************************************************
1207 /*
1208 -- Step (4): Perform any necessary pro-ration.
1209 -- In declaration we have:
1210 -- Need to check if cursor sql w/max and min works; otherwise do check after
1211 -- fetch. This cursor *FAILS* to retreive a rec if NO CHANGES occur in period.
1212 -- This is the 90% case where we want to just fall thru asap. Same will apply
1213 -- for element entry change cursor.
1214 -- Remember, we need asst recs in the same tax unit as that passed in.
1215 -- So the start date of the work info record w/the same tax unit must be less
1216 -- than or equal to the start date of the asst rec. That's all we have to
1217 -- check since a change to tax unit will cause date-effective update to asst.
1218 -- ^^^ Need to verify that tax-unit changes are date-effective and that they
1219 -- **are reflected in per_assignments_f**. Also need to know what other
1220 -- changes result in date-effective update to work_info tab and whether
1221 -- or not all changes date-effectively affect assignment. They SHOULD be
1222 -- reflected in asst since this would be consistent w/SCL keyflex behaviour.
1223 -- A: Yes they are. (13 May 1994 HParicha)
1224 -- Remember, need to check work schedule before each call to
1225 -- Work_Schedule_Total_Hours.
1226 -- OK so to pro-rate earnings, determine exactly what needs to be done up
1227 -- front in order of expected frequency:
1228 -- 1. Assignment Info Changes Only
1229 -- 2. Element Entry Info Changes Only
1230 -- 3. Both ASG and EEV info changes
1231
1232 */
1233 IF (l_asg_info_changes > 0) AND (l_eev_info_changes = 0) THEN
1234
1235 -- Use hourly rate passed in (p_ass_hrly_figure) and use this to prorate
1236 -- thru all ASG changes. No need to check for change in hourly rate.
1237
1238 -- SELECT (ASG1):
1239 -- Select for SINGLE record that includes Period Start Date but does not
1240 -- span entire period.
1241 -- We know this select will return a row, otherwise there would be no
1242 -- ASG changes to detect.
1243 -- Actually it might NOT return a row - the mid period change may have
1244 -- been a mid period hire or return from leave of absence, in which case
1245 -- you would not have an assignment record to 'Process' that spans the
1246 -- Start Date of the period!
1247
1248 p_actual_hours_worked := 0;
1249 hr_utility.set_location('calculate_period_earnings', 70);
1250 -- IF NOT l_mid_period_asg_change THEN
1251
1252 begin
1253
1254 hr_utility.set_location('calculate_period_earnings', 71);
1255 SELECT GREATEST(ASG.effective_start_date, p_period_start),
1256 ASG.effective_end_date,
1257 NVL(ASG.NORMAL_HOURS, 0),
1258 NVL(HRL.meaning, 'NOT ENTERED'),
1259 NVL(HRL.lookup_code, 'NOT ENTERED'),
1260 NVL(SCL.segment4, 'NOT ENTERED')
1261 INTO v_range_start,
1262 v_range_end,
1263 v_asst_std_hrs,
1264 v_asst_std_freq,
1265 v_asst_std_freq_code,
1266 v_work_schedule
1267 FROM per_assignments_f ASG,
1268 per_assignment_status_types AST,
1269 hr_soft_coding_keyflex SCL,
1270 hr_lookups HRL
1271 WHERE ASG.assignment_id = p_asst_id
1272 AND ASG.business_group_id + 0 = p_bus_grp_id
1273 AND ASG.effective_start_date <= p_period_start
1274 AND ASG.effective_end_date >= p_period_start
1275 AND ASG.effective_end_date < p_period_end
1276 AND AST.assignment_status_type_id = ASG.assignment_status_type_id
1277 AND AST.per_system_status = 'ACTIVE_ASSIGN'
1278 AND SCL.soft_coding_keyflex_id = ASG.soft_coding_keyflex_id
1279 AND decode(lv_gre_type, 'T4A/RL1', segment11, 'T4A/RL2', SCL.segment12,
1280 SCL.segment1) = to_char(p_tax_unit_id)
1281 AND SCL.enabled_flag = 'Y'
1282 AND HRL.lookup_code(+) = ASG.frequency
1283 AND HRL.lookup_type(+) = 'FREQUENCY';
1284
1285 -- 25 May 1994:
1286 -- Changing ASG status check to be for Payroll Status of 'Process'
1287 -- AND AST.per_system_status = 'ACTIVE_ASSIGN'
1288 -- AND AST.pay_system_status = 'P'
1289
1290 -- Prorate using hourly rate passed in as param:
1291 -- Should this become a function from here...
1292
1293 hr_utility.set_location('calculate_period_earnings', 72);
1294 v_prorated_earnings := v_prorated_earnings +
1295 Prorate_Earnings (
1296 p_bg_id => p_bus_grp_id,
1297 p_assignment_id => p_asst_id,
1298 p_assignment_action_id => p_assignment_action_id,
1299 p_element_entry_id => p_ele_entry_id,
1300 p_date_earned => p_date_earned,
1301 p_asg_hrly_rate => p_ass_hrly_figure,
1302 p_wsched => v_work_schedule,
1303 p_asg_std_hours => v_asst_std_hrs,
1304 p_asg_std_freq => v_asst_std_freq_code,
1305 p_range_start_date => v_range_start,
1306 p_range_end_date => v_range_end,
1307 p_act_hrs_worked => p_actual_hours_worked);
1308
1309 hr_utility.set_location('Calculate_Period_Earnings.v_prorated_earnings = ', v_prorated_earnings);
1310
1311 -- Just in case mid period change does not span Start of Period!
1312
1313 EXCEPTION WHEN NO_DATA_FOUND THEN
1314 NULL;
1315
1316 end;
1317
1318 -- END IF;
1319
1320 -- SELECT (ASG2)
1321 -- Select for ALL records that are WITHIN Period Start and End Dates
1322 -- including Period End Date.
1323 -- Not BETWEEN Period Start/End since we already found a record including
1324 -- Start Date in select (1) above.
1325
1326 hr_utility.set_location('calculate_period_earnings', 77);
1327 OPEN get_asst_chgs; -- SELECT (ASG2)
1328 LOOP
1329
1330 FETCH get_asst_chgs
1331 INTO v_range_start,
1332 v_range_end,
1333 v_asst_std_hrs,
1334 v_asst_std_freq,
1335 v_asst_std_freq_code,
1336 v_work_schedule;
1337 EXIT WHEN get_asst_chgs%NOTFOUND;
1338
1339 hr_utility.set_location('calculate_period_earnings', 79);
1340
1341 -- For each range of dates found, add to running prorated earnings total.
1342
1343 hr_utility.set_location('calculate_period_earnings', 81);
1344 v_prorated_earnings := v_prorated_earnings +
1345 Prorate_Earnings (
1346 p_bg_id => p_bus_grp_id,
1347 p_assignment_id => p_asst_id,
1348 p_assignment_action_id => p_assignment_action_id,
1349 p_element_entry_id => p_ele_entry_id,
1350 p_date_earned => p_date_earned,
1351 p_asg_hrly_rate => p_ass_hrly_figure,
1352 p_wsched => v_work_schedule,
1353 p_asg_std_hours => v_asst_std_hrs,
1354 p_asg_std_freq => v_asst_std_freq_code,
1355 p_range_start_date => v_range_start,
1356 p_range_end_date => v_range_end,
1357 p_act_hrs_worked => p_actual_hours_worked);
1358
1359 hr_utility.set_location('Calculate_Period_Earnings.v_prorated_earnings = ', v_prorated_earnings);
1360
1361 END LOOP;
1362
1363 CLOSE get_asst_chgs;
1364
1365 -- SELECT (ASG3)
1366 -- Select for SINGLE record that exists across Period End Date:
1367 -- NOTE: Will only return a row if select (2) does not return a row where
1368 -- Effective End Date = Period End Date !
1369
1370 begin
1371
1372 hr_utility.set_location('calculate_period_earnings', 89);
1373 SELECT ASG.effective_start_date,
1374 LEAST(ASG.effective_end_date, p_period_end),
1375 NVL(ASG.normal_hours, 0),
1376 NVL(HRL.meaning, 'NOT ENTERED'),
1377 NVL(HRL.lookup_code, 'NOT ENTERED'),
1378 NVL(SCL.segment4, 'NOT ENTERED')
1379 INTO v_range_start,
1380 v_range_end,
1381 v_asst_std_hrs,
1382 v_asst_std_freq,
1383 v_asst_std_freq_code,
1384 v_work_schedule
1385 FROM hr_soft_coding_keyflex SCL,
1386 per_assignment_status_types AST,
1387 per_assignments_f ASG,
1388 hr_lookups HRL
1389 WHERE ASG.assignment_id = p_asst_id
1390 AND ASG.business_group_id + 0 = p_bus_grp_id
1391 AND ASG.effective_start_date > p_period_start
1392 AND ASG.effective_start_date <= p_period_end
1393 AND ASG.effective_end_date > p_period_end
1394 AND AST.assignment_status_type_id = ASG.assignment_status_type_id
1395 AND AST.per_system_status = 'ACTIVE_ASSIGN'
1396 AND SCL.soft_coding_keyflex_id = ASG.soft_coding_keyflex_id
1397 AND decode(lv_gre_type, 'T4A/RL1', segment11, 'T4A/RL2', SCL.segment12,
1398 SCL.segment1) = to_char(p_tax_unit_id)
1399 AND SCL.enabled_flag = 'Y'
1400 AND HRL.lookup_code(+) = ASG.frequency
1401 AND HRL.lookup_type(+) = 'FREQUENCY';
1402
1403 -- 25 May 1994:
1404 -- Changing ASG status check to be for Payroll Status of 'Process'
1405 -- AND AST.per_system_status = 'ACTIVE_ASSIGN'
1406 -- AND AST.pay_system_status = 'P'
1407
1408 hr_utility.set_location('calculate_period_earnings', 91);
1409 v_prorated_earnings := v_prorated_earnings +
1410 Prorate_Earnings (
1411 p_bg_id => p_bus_grp_id,
1412 p_assignment_id => p_asst_id,
1413 p_assignment_action_id => p_assignment_action_id,
1414 p_element_entry_id => p_ele_entry_id,
1415 p_date_earned => p_date_earned,
1416 p_asg_hrly_rate => p_ass_hrly_figure,
1417 p_wsched => v_work_schedule,
1418 p_asg_std_hours => v_asst_std_hrs,
1419 p_asg_std_freq => v_asst_std_freq_code,
1420 p_range_start_date => v_range_start,
1421 p_range_end_date => v_range_end,
1422 p_act_hrs_worked => p_actual_hours_worked);
1423
1424 hr_utility.set_location('Calculate_Period_Earnings.v_prorated_earnings = ', v_prorated_earnings);
1425
1426 -- We're done!
1427
1428 hr_utility.set_location('calculate_period_earnings', 101);
1429
1430 p_actual_hours_worked := ROUND(p_actual_hours_worked, 3);
1431 RETURN v_prorated_earnings;
1432
1433 EXCEPTION WHEN NO_DATA_FOUND THEN
1434 -- (ASG3) returned no rows, but we're done anyway!
1435 hr_utility.set_location('calculate_period_earnings', 102);
1436
1437 p_actual_hours_worked := ROUND(p_actual_hours_worked, 3);
1438 RETURN v_prorated_earnings;
1439
1440 END;
1441
1442 ELSIF (l_asg_info_changes = 0) AND (l_eev_info_changes > 0) THEN
1443
1444 hr_utility.set_location('calculate_period_earnings', 103);
1445 p_actual_hours_worked := 0;
1446 v_prorated_earnings := v_prorated_earnings +
1447 Prorate_EEV (
1448 p_bus_group_id => p_bus_grp_id,
1449 p_assignment_id => p_asst_id,
1450 p_assignment_action_id => p_assignment_action_id,
1451 p_date_earned => p_date_earned,
1452 p_pay_id => p_payroll_id,
1453 p_work_sched => p_work_schedule,
1454 p_asg_std_hrs => p_asst_std_hrs,
1455 p_asg_std_freq => p_asst_std_freq,
1456 p_pay_basis => p_pay_basis,
1457 p_hrly_rate => v_curr_hrly_rate,
1458 p_range_start_date => p_period_start,
1459 p_range_end_date => p_period_end,
1460 p_actual_hrs_worked => p_actual_hours_worked,
1461 p_element_entry_id => p_ele_entry_id,
1462 p_inpval_id => v_inpval_id);
1463
1464 -- We're Done!
1465 hr_utility.set_location('calculate_period_earnings', 127);
1466
1467 p_actual_hours_worked := ROUND(p_actual_hours_worked, 3);
1468 RETURN v_prorated_earnings;
1469
1470 ELSE
1471
1472 -- We assume both l_asg_info_changes and l_eev_info_changes > 0 since
1473 -- we wouldn't have reached this section of code if they both = 0 !
1474 -- So cycle thru ASG changes; for each range found for ASG change,
1475 -- check for EEV changes IN THAT RANGE! If none are found, then
1476 -- prorate with current (latest) hourly rate figure. If EEV changes
1477 -- are found, then call EEV proration and continue when complete.
1478
1479 -- SELECT (ASG1.2):
1480 -- Select for SINGLE record that includes Period Start Date but does not
1481 -- span entire period.
1482 -- We know this select will return a row, otherwise there would be no
1483 -- ASG changes to detect.
1484
1485 p_actual_hours_worked := 0;
1486
1487 -- Since we KNOW both asg and eev changes occur, then we want current
1488 -- (latest) hourly rate to be figure AS OF BEGINNING OF PERIOD for starters.
1489 -- NOT! v_curr_hrly_rate := p_ass_hrly_figure;
1490
1491 begin
1492
1493 hr_utility.set_location('calculate_period_earnings', 128);
1494 SELECT EEV.screen_entry_value
1495 INTO v_earnings_entry
1496 FROM pay_element_entry_values_f EEV
1497 WHERE EEV.element_entry_id = p_ele_entry_id
1498 AND EEV.input_value_id = v_inpval_id
1499 --bug 5617540 starts
1500 AND p_period_start between EEV.effective_start_date
1501 AND EEV.effective_end_date;
1502 /* --
1503 AND EEV.effective_start_date <= p_period_start
1504 AND EEV.effective_end_date >= p_period_start
1505 AND EEV.effective_end_date < p_period_end;*/
1506 --bug 5617540 ends
1507
1508 SELECT GREATEST(ASG.effective_start_date, p_period_start),
1509 ASG.effective_end_date,
1510 NVL(ASG.NORMAL_HOURS, 0),
1511 NVL(HRL.meaning, 'NOT ENTERED'),
1512 NVL(HRL.lookup_code, 'NOT ENTERED'),
1513 NVL(SCL.segment4, 'NOT ENTERED')
1514 INTO v_range_start,
1515 v_range_end,
1516 v_asst_std_hrs,
1517 v_asst_std_freq,
1518 v_asst_std_freq_code,
1519 v_work_schedule
1520 FROM per_assignments_f ASG,
1521 per_assignment_status_types AST,
1522 hr_soft_coding_keyflex SCL,
1523 hr_lookups HRL
1524 WHERE ASG.assignment_id = p_asst_id
1525 AND ASG.business_group_id + 0 = p_bus_grp_id
1526 AND ASG.effective_start_date <= p_period_start
1527 AND ASG.effective_end_date >= p_period_start
1528 AND ASG.effective_end_date < p_period_end
1529 AND AST.assignment_status_type_id = ASG.assignment_status_type_id
1530 AND AST.per_system_status = 'ACTIVE_ASSIGN'
1531 AND SCL.soft_coding_keyflex_id = ASG.soft_coding_keyflex_id
1532 AND decode(lv_gre_type, 'T4A/RL1', segment11, 'T4A/RL2', SCL.segment12,
1533 SCL.segment1) = to_char(p_tax_unit_id)
1534 AND SCL.enabled_flag = 'Y'
1535 AND HRL.lookup_code(+) = ASG.frequency
1536 AND HRL.lookup_type(+) = 'FREQUENCY';
1537
1538 hr_utility.set_location('v_earnings_entry='||v_earnings_entry, 129);
1539 hr_utility.set_location('v_asst_std_hrs=', v_asst_std_hrs);
1540 hr_utility.set_location('calling core udfs', 48);
1541 v_curr_hrly_rate := PAY_CORE_FF_UDFS.get_hourly_rate(
1542 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
1549 hr_utility.set_location('v_curr_hrly_rate = ', v_curr_hrly_rate);
1550 hr_utility.set_location('calculate_period_earnings', 130);
1551 -- IF NOT l_mid_period_asg_change THEN
1552
1553 -- Do not have to do this first bit if Mid period hire/active asg occurs:
1554
1555 -- 25 May 1994:
1556 -- Changing ASG status check to be for Payroll Status of 'Process'
1557 -- AND AST.per_system_status = 'ACTIVE_ASSIGN'
1558 -- AND AST.pay_system_status = 'P'
1559
1560 -- (EEV1) Select for SINGLE record that includes Period Start Date but
1561 -- does not span entire period.
1562
1563 hr_utility.set_location('calculate_period_earnings', 132);
1564 SELECT COUNT(EEV.element_entry_value_id)
1565 INTO l_eev_info_changes
1566 FROM pay_element_entry_values_f EEV
1567 WHERE EEV.element_entry_id = p_ele_entry_id
1568 AND EEV.input_value_id = v_inpval_id
1569 AND EEV.effective_start_date <= v_range_start
1570 AND EEV.effective_end_date >= v_range_start
1571 AND EEV.effective_end_date < v_range_end;
1572
1573 IF l_eev_info_changes = 0 THEN
1574 -- Prorate using latest hourly rate:
1575 hr_utility.set_location('calculate_period_earnings', 128);
1576 SELECT EEV.screen_entry_value
1577 INTO v_earnings_entry
1578 FROM pay_element_entry_values_f EEV
1579 WHERE EEV.element_entry_id = p_ele_entry_id
1580 AND EEV.input_value_id = v_inpval_id
1581 AND v_range_end BETWEEN EEV.effective_start_date
1582 AND EEV.effective_end_date;
1583
1584 hr_utility.set_location('v_earnings_entry='||v_earnings_entry, 129);
1585 hr_utility.set_location('v_asst_std_hrs=', v_asst_std_hrs);
1586 hr_utility.set_location('calling core udfs', 49);
1587 v_curr_hrly_rate := PAY_CORE_FF_UDFS.get_hourly_rate(
1588 p_bus_grp_id
1589 ,p_asst_id
1590 ,p_payroll_id
1591 ,p_ele_entry_id
1592 ,p_date_earned
1593 ,p_assignment_action_id );
1594
1595 hr_utility.set_location('v_curr_hrly_rate = ', v_curr_hrly_rate);
1596 -- Should this become a function from here...
1597 hr_utility.set_location('calculate_period_earnings', 133);
1598 v_prorated_earnings := v_prorated_earnings +
1599 Prorate_Earnings (
1600 p_bg_id => p_bus_grp_id,
1601 p_assignment_id => p_asst_id,
1602 p_assignment_action_id => p_assignment_action_id,
1603 p_element_entry_id => p_ele_entry_id,
1604 p_date_earned => p_date_earned,
1605 p_asg_hrly_rate => v_curr_hrly_rate,
1606 p_wsched => v_work_schedule,
1607 p_asg_std_hours => v_asst_std_hrs,
1608 p_asg_std_freq => v_asst_std_freq_code,
1609 p_range_start_date => v_range_start,
1610 p_range_end_date => v_range_end,
1611 p_act_hrs_worked => p_actual_hours_worked);
1612
1613 hr_utility.set_location('Calculate_Period_Earnings.v_prorated_earnings = ', v_prorated_earnings);
1614
1615 ELSE
1616 -- Do proration for this ASG range by EEV !
1617
1618 hr_utility.set_location('calculate_period_earnings', 134);
1619 v_prorated_earnings := v_prorated_earnings +
1620 Prorate_EEV (
1621 p_bus_group_id => p_bus_grp_id,
1622 p_assignment_id => p_asst_id,
1623 p_assignment_action_id => p_assignment_action_id,
1624 p_date_earned => p_date_earned,
1625 p_pay_id => p_payroll_id,
1626 p_work_sched => v_work_schedule,
1627 p_asg_std_hrs => v_asst_std_hrs,
1628 p_asg_std_freq => v_asst_std_freq_code,
1629 p_pay_basis => v_pay_basis_code,
1630 p_hrly_rate => v_curr_hrly_rate,
1631 p_range_start_date => v_range_start,
1632 p_range_end_date => v_range_end,
1633 p_actual_hrs_worked => p_actual_hours_worked,
1634 p_element_entry_id => p_ele_entry_id,
1635 p_inpval_id => v_inpval_id);
1636
1637 END IF; -- EEV info changes
1638
1639 EXCEPTION WHEN NO_DATA_FOUND THEN
1640 NULL;
1641
1642 end;
1643
1644 -- END IF; -- Mid Period Active ASG.
1645
1646 -- SELECT (ASG2.2)
1647 -- Select for ALL records that are WITHIN Period Start and End Dates
1648 -- including Period End Date.
1649 -- Not BETWEEN Period Start/End since we already found a record including
1650 -- Start Date in select (1) above.
1651
1652 hr_utility.set_location('calculate_period_earnings', 135);
1653 OPEN get_asst_chgs; -- SELECT (ASG2)
1654 LOOP
1655
1656 FETCH get_asst_chgs
1657 INTO v_range_start,
1658 v_range_end,
1659 v_asst_std_hrs,
1660 v_asst_std_freq,
1661 v_asst_std_freq_code,
1662 v_work_schedule;
1663 EXIT WHEN get_asst_chgs%NOTFOUND;
1664
1665 hr_utility.set_location('calculate_period_earnings', 79);
1666
1667 -- For each range of dates found, add to running prorated earnings total.
1668
1669 -- Check for EEV changes, if also none -> return v_period_earn and done:
1670 -- (EEV1) Select for SINGLE record that includes Period Start Date but
1671 -- does not span entire period. If no row returned, then EEV record
1672 -- spans period and there is no need to run selects (EEV2) or (EEV3)
1673 -- and we can stop now!
1674
1675 hr_utility.set_location('calculate_period_earnings', 133);
1676 SELECT COUNT(EEV.element_entry_value_id)
1677 INTO l_eev_info_changes
1678 FROM pay_element_entry_values_f EEV
1679 WHERE EEV.element_entry_id = p_ele_entry_id
1680 AND EEV.input_value_id = v_inpval_id
1681 AND EEV.effective_start_date <= v_range_start
1682 AND EEV.effective_end_date >= v_range_start
1683 AND EEV.effective_end_date < v_range_end;
1684
1685 IF l_eev_info_changes = 0 THEN
1686 -- Prorate using latest hourly rate:
1687 hr_utility.set_location('calculate_period_earnings', 128);
1688 SELECT EEV.screen_entry_value
1689 INTO v_earnings_entry
1690 FROM pay_element_entry_values_f EEV
1691 WHERE EEV.element_entry_id = p_ele_entry_id
1692 AND EEV.input_value_id = v_inpval_id
1693 AND v_range_end BETWEEN EEV.effective_start_date
1694 AND EEV.effective_end_date;
1695 --
1696 hr_utility.set_location('v_earnings_entry='||v_earnings_entry, 129);
1697 hr_utility.set_location('v_asst_std_hrs=', v_asst_std_hrs);
1698
1699 hr_utility.set_location('calling core udfs', 50);
1700 v_curr_hrly_rate := PAY_CORE_FF_UDFS.get_hourly_rate(
1701 p_bus_grp_id
1702 ,p_asst_id
1703 ,p_payroll_id
1704 ,p_ele_entry_id
1705 ,p_date_earned
1706 ,p_assignment_action_id );
1707 --
1708 hr_utility.set_location('v_curr_hrly_rate = ', v_curr_hrly_rate);
1709 hr_utility.set_location('calculate_period_earnings', 91);
1710 v_prorated_earnings := v_prorated_earnings +
1711 Prorate_Earnings (
1712 p_bg_id => p_bus_grp_id,
1713 p_assignment_id => p_asst_id,
1714 p_assignment_action_id => p_assignment_action_id,
1715 p_element_entry_id => p_ele_entry_id,
1716 p_date_earned => p_date_earned,
1717 p_asg_hrly_rate => v_curr_hrly_rate,
1718 p_wsched => v_work_schedule,
1719 p_asg_std_hours => v_asst_std_hrs,
1720 p_asg_std_freq => v_asst_std_freq_code,
1721 p_range_start_date => v_range_start,
1722 p_range_end_date => v_range_end,
1723 p_act_hrs_worked => p_actual_hours_worked);
1724 --
1725 hr_utility.set_location('Calculate_Period_Earnings.v_prorated_earnings = ', v_prorated_earnings);
1726 --
1727 ELSE
1728 -- Do proration for this ASG range by EEV !
1729 --
1730 v_prorated_earnings := v_prorated_earnings +
1731 Prorate_EEV (
1732 p_bus_group_id => p_bus_grp_id,
1733 p_assignment_id => p_asst_id,
1734 p_assignment_action_id => p_assignment_action_id,
1735 p_date_earned => p_date_earned,
1736 p_pay_id => p_payroll_id,
1737 p_work_sched => v_work_schedule,
1738 p_asg_std_hrs => v_asst_std_hrs,
1739 p_asg_std_freq => v_asst_std_freq_code,
1740 p_pay_basis => v_pay_basis_code,
1741 p_hrly_rate => v_curr_hrly_rate,
1742 p_range_start_date => v_range_start,
1743 p_range_end_date => v_range_end,
1744 p_actual_hrs_worked => p_actual_hours_worked,
1745 p_element_entry_id => p_ele_entry_id,
1746 p_inpval_id => v_inpval_id);
1747
1748 END IF;
1749
1750 END LOOP;
1751
1752 CLOSE get_asst_chgs;
1753
1754 -- SELECT (ASG3.2)
1755 -- Select for SINGLE record that exists across Period End Date:
1756 -- NOTE: Will only return a row if select (2) does not return a row where
1757 -- Effective End Date = Period End Date !
1758
1759 begin
1760
1761 hr_utility.set_location('calculate_period_earnings', 129);
1762 SELECT ASG.effective_start_date,
1763 LEAST(ASG.effective_end_date, p_period_end),
1764 NVL(ASG.normal_hours, 0),
1765 NVL(HRL.meaning, 'NOT ENTERED'),
1766 NVL(HRL.lookup_code, 'NOT ENTERED'),
1767 NVL(SCL.segment4, 'NOT ENTERED')
1768 INTO v_range_start,
1769 v_range_end,
1770 v_asst_std_hrs,
1771 v_asst_std_freq,
1772 v_asst_std_freq_code,
1773 v_work_schedule
1774 FROM hr_soft_coding_keyflex SCL,
1775 per_assignment_status_types AST,
1776 per_assignments_f ASG,
1777 hr_lookups HRL
1778 WHERE ASG.assignment_id = p_asst_id
1779 AND ASG.business_group_id + 0 = p_bus_grp_id
1780 AND ASG.effective_start_date > p_period_start
1781 AND ASG.effective_start_date <= p_period_end
1782 AND ASG.effective_end_date > p_period_end
1783 AND AST.assignment_status_type_id = ASG.assignment_status_type_id
1784 AND AST.per_system_status = 'ACTIVE_ASSIGN'
1785 AND SCL.soft_coding_keyflex_id = ASG.soft_coding_keyflex_id
1786 AND decode(lv_gre_type, 'T4A/RL1', segment11, 'T4A/RL2', SCL.segment12,
1787 SCL.segment1) = to_char(p_tax_unit_id)
1788 AND SCL.enabled_flag = 'Y'
1789 AND HRL.lookup_code(+) = ASG.frequency
1790 AND HRL.lookup_type(+) = 'FREQUENCY';
1791
1792 -- 25 May 1994:
1793 -- Changing ASG status check to be for Payroll Status of 'Process'
1794 -- AND AST.per_system_status = 'ACTIVE_ASSIGN'
1795 -- AND AST.pay_system_status = 'P'
1796
1797 -- Check for EEV changes, if also none -> return v_period_earn and done:
1798 -- (EEV1) Select for SINGLE record that includes Period Start Date but
1799 -- does not span entire period. If no row returned, then EEV record
1800 -- spans period and there is no need to run selects (EEV2) or (EEV3)
1801 -- and we can stop now! Remember, if eev spans period then get
1802 -- eev.screen_entry_value that spans period (ie latest hrly rate).
1803
1804 hr_utility.set_location('calculate_period_earnings', 133);
1805 SELECT COUNT(EEV.element_entry_value_id)
1806 INTO l_eev_info_changes
1807 FROM pay_element_entry_values_f EEV
1808 WHERE EEV.element_entry_id = p_ele_entry_id
1809 AND EEV.input_value_id = v_inpval_id
1810 AND EEV.effective_start_date <= v_range_start
1811 AND EEV.effective_end_date >= v_range_start
1812 AND EEV.effective_end_date < v_range_end;
1813
1814 IF l_eev_info_changes = 0 THEN
1815 -- Prorate using latest hourly rate (ie. rate as of end of period):
1816 hr_utility.set_location('calculate_period_earnings', 128);
1817 SELECT EEV.screen_entry_value
1818 INTO v_earnings_entry
1819 FROM pay_element_entry_values_f EEV
1820 WHERE EEV.element_entry_id = p_ele_entry_id
1821 AND EEV.input_value_id = v_inpval_id
1822 AND v_range_end BETWEEN EEV.effective_start_date
1823 AND EEV.effective_end_date;
1824
1825 hr_utility.set_location('v_earnings_entry='||v_earnings_entry, 129);
1826 hr_utility.set_location('calling core udfs', 51);
1827 v_curr_hrly_rate := PAY_CORE_FF_UDFS.get_hourly_rate(
1828 p_bus_grp_id
1829 ,p_asst_id
1830 ,p_payroll_id
1831 ,p_ele_entry_id
1832 ,p_date_earned
1833 ,p_assignment_action_id );
1834
1835 hr_utility.set_location('v_curr_hrly_rate = ', v_curr_hrly_rate);
1836 hr_utility.set_location('calculate_period_earnings', 130);
1837
1838 hr_utility.set_location('calculate_period_earnings', 137);
1839 v_prorated_earnings := v_prorated_earnings +
1840 Prorate_Earnings (
1841 p_bg_id => p_bus_grp_id,
1842 p_assignment_id => p_asst_id,
1843 p_assignment_action_id => p_assignment_action_id,
1844 p_element_entry_id => p_ele_entry_id,
1845 p_date_earned => p_date_earned,
1846 p_asg_hrly_rate => v_curr_hrly_rate,
1847 p_wsched => v_work_schedule,
1848 p_asg_std_hours => v_asst_std_hrs,
1849 p_asg_std_freq => v_asst_std_freq_code,
1850 p_range_start_date => v_range_start,
1851 p_range_end_date => v_range_end,
1852 p_act_hrs_worked => p_actual_hours_worked);
1853
1854 hr_utility.set_location('Calculate_Period_Earnings.v_prorated_earnings = ', v_prorated_earnings);
1855
1856 ELSE
1857 -- Do proration for this ASG range by EEV !
1858
1859 hr_utility.set_location('calculate_period_earnings', 139);
1860 v_prorated_earnings := v_prorated_earnings +
1861 Prorate_EEV (
1862 p_bus_group_id => p_bus_grp_id,
1863 p_assignment_id => p_asst_id,
1864 p_assignment_action_id => p_assignment_action_id,
1865 p_date_earned => p_date_earned,
1866 p_pay_id => p_payroll_id,
1867 p_work_sched => v_work_schedule,
1868 p_asg_std_hrs => v_asst_std_hrs,
1869 p_asg_std_freq => v_asst_std_freq_code,
1870 p_pay_basis => v_pay_basis_code,
1871 p_hrly_rate => v_curr_hrly_rate,
1872 p_range_start_date => v_range_start,
1873 p_range_end_date => v_range_end,
1874 p_actual_hrs_worked => p_actual_hours_worked,
1875 p_element_entry_id => p_ele_entry_id,
1876 p_inpval_id => v_inpval_id);
1877
1878 END IF;
1879
1880 -- We're done!
1881
1882 hr_utility.set_location('calculate_period_earnings', 141);
1883
1884 p_actual_hours_worked := ROUND(p_actual_hours_worked, 3);
1885 RETURN v_prorated_earnings;
1886
1887 EXCEPTION WHEN NO_DATA_FOUND THEN
1888 -- (ASG3.2) returned no rows, but we're done anyway!
1889 hr_utility.set_location('calculate_period_earnings', 142);
1890
1891 p_actual_hours_worked := ROUND(p_actual_hours_worked, 3);
1892 RETURN v_prorated_earnings;
1893
1894 END;
1895
1896 END IF;
1897
1898 EXCEPTION
1899 WHEN NO_DATA_FOUND THEN
1900 hr_utility.set_location('calculate_period_earnings', 190);
1901
1902 p_actual_hours_worked := ROUND(p_actual_hours_worked, 3);
1903
1904 RETURN v_prorated_earnings;
1905
1906 END Calculate_Period_Earnings;
1907
1908 -- **********************************************************************
1909
1910 FUNCTION standard_hours_worked(
1911 p_std_hrs in NUMBER,
1912 p_range_start in DATE,
1913 p_range_end in DATE,
1914 p_std_freq in VARCHAR2) RETURN NUMBER IS
1915
1916 c_wkdays_per_week NUMBER(5,2) := 5;
1917 c_wkdays_per_month NUMBER(5,2) := 20;
1918 c_wkdays_per_year NUMBER(5,2) := 250;
1919
1920 /* 353434, 368242 : Fixed number width for total hours */
1921 v_total_hours NUMBER(15,7) := 0;
1922 v_wrkday_hours NUMBER(15,7) := 0; -- std hrs/wk divided by 5 workdays/wk
1923 v_curr_date DATE := NULL;
1924 v_curr_day VARCHAR2(3) := NULL; -- 3 char abbrev for day of wk.
1925 v_day_no NUMBER;
1926
1927 BEGIN -- standard_hours_worked
1928
1929 -- Check for valid range
1930 hr_utility.set_location('standard_hours_worked', 5);
1931 IF p_range_start > p_range_end THEN
1932 hr_utility.set_location('standard_hours_worked', 7);
1933 RETURN v_total_hours;
1934 -- hr_utility.set_message(801,'PAY_xxxx_INVALID_DATE_RANGE');
1935 -- hr_utility.raise_error;
1936 END IF;
1937 --
1938
1939 IF UPPER(p_std_freq) = 'W' THEN
1940 v_wrkday_hours := p_std_hrs / c_wkdays_per_week;
1941 ELSIF UPPER(p_std_freq) = 'M' THEN
1942 v_wrkday_hours := p_std_hrs / c_wkdays_per_month;
1943 ELSIF UPPER(p_std_freq) = 'Y' THEN
1944 v_wrkday_hours := p_std_hrs / c_wkdays_per_year;
1945 ELSE
1946 v_wrkday_hours := p_std_hrs;
1947 END IF;
1948
1949 v_curr_date := p_range_start;
1950
1951 hr_utility.set_location('standard_hours_worked', 10);
1952
1953 hr_utility.trace('p_range_start is'|| to_char(p_range_start));
1954 hr_utility.trace('p_range_end is'|| to_char(p_range_end));
1955 LOOP
1956
1957 v_day_no := TO_CHAR(v_curr_date, 'D');
1958
1959 hr_utility.set_location('standard_hours_worked', 15);
1960
1961 IF v_day_no > 1 and v_day_no < 7 then
1962
1963 v_total_hours := v_total_hours + v_wrkday_hours;
1964 hr_utility.set_location('standard_hours_worked v_total_hours = ', v_total_hours);
1965 END IF;
1966 v_curr_date := v_curr_date + 1;
1967 EXIT WHEN v_curr_date > p_range_end;
1968 END LOOP;
1969 --
1970 hr_utility.set_location('v_total_hours is', to_number(v_total_hours));
1971 RETURN v_total_hours;
1972 --
1973 END standard_hours_worked;
1974 --
1975 -- **********************************************************************
1976 --
1977 FUNCTION Convert_Period_Type(
1978 p_bus_grp_id in NUMBER,
1979 p_payroll_id in NUMBER,
1980 p_assignment_action_id in NUMBER,
1981 p_assignment_id in NUMBER,
1982 p_element_entry_id in NUMBER,
1983 p_date_earned in DATE,
1984 p_asst_work_schedule in VARCHAR2,
1985 p_asst_std_hours in NUMBER ,
1986 p_figure in NUMBER,
1987 p_from_freq in VARCHAR2,
1988 p_to_freq in VARCHAR2,
1989 p_period_start_date in DATE ,
1990 p_period_end_date in DATE ,
1991 p_asst_std_freq in VARCHAR2 )
1992 RETURN NUMBER IS
1993
1994 -- local vars
1995
1996 v_converted_figure NUMBER(27,7);
1997 v_from_annualizing_factor NUMBER(30,7); --Fix for bug 3650170
1998 v_to_annualizing_factor NUMBER(30,7); --Fix for bug 3650170
1999 --v_from_annualizing_factor NUMBER(10);
2000 --v_to_annualizing_factor NUMBER(10);
2001
2002 -- local function
2003
2004 FUNCTION Get_Annualizing_Factor(p_bg in NUMBER,
2005 p_payroll in NUMBER,
2006 p_freq in VARCHAR2,
2007 p_asg_work_sched in VARCHAR2,
2008 p_asg_std_hrs in NUMBER,
2009 p_asg_std_freq in VARCHAR2)
2010 RETURN NUMBER IS
2011
2012 -- local constants
2013
2014 c_weeks_per_year NUMBER(3) := 52;
2015 c_days_per_year NUMBER(3) := 200;
2016 c_months_per_year NUMBER(3) := 12;
2017
2018 -- local vars
2019 /* 353434, 368242 : Fixed number width for total hours variables */
2020 v_annualizing_factor NUMBER(30,7);
2021 v_periods_per_fiscal_yr NUMBER(5);
2022 v_hrs_per_wk NUMBER(15,7);
2023 v_hrs_per_range NUMBER(15,7);
2024 v_use_pay_basis NUMBER(1) := 0;
2025 v_pay_basis VARCHAR2(80);
2026 v_range_start DATE;
2027 v_range_end DATE;
2028 v_work_sched_name VARCHAR2(80);
2029 v_ws_id NUMBER(9);
2030 v_period_hours BOOLEAN;
2031
2032 v_return_status NUMBER;
2033 v_return_message VARCHAR2(500);
2034 v_schedule_source varchar2(100);
2035 v_schedule varchar2(200);
2036 v_total_hours NUMBER(15,7) ;
2037 BEGIN -- Get_Annualizing_Factor
2038
2039 --
2040 -- Check for use of salary admin (ie. pay basis) as frequency.
2041 -- Selecting "count" because we want to continue processing even if
2042 -- the from_freq is not a pay basis.
2043 --
2044
2045 --hr_utility.trace_on('Y', 'ORACLE');
2046 hr_utility.set_location('Get_Annualizing_Factor', 1);
2047
2048 begin -- Is Freq pay basis?
2049
2050 --
2051 -- Decode pay basis and set v_annualizing_factor accordingly.
2052 -- PAY_BASIS "Meaning" is passed from FF !
2053 --
2054
2055 hr_utility.set_location('Get_Annualizing_Factor', 13);
2056
2057 SELECT lookup_code
2058 INTO v_pay_basis
2059 FROM hr_lookups lkp
2060 WHERE lkp.application_id = 800
2061 AND lkp.lookup_type = 'PAY_BASIS'
2062 AND lkp.lookup_code = p_freq;
2063
2064 v_pay_basis := p_freq;
2065
2066 hr_utility.trace('v_pay_basis is'|| v_pay_basis);
2067 hr_utility.set_location('Get_Annualizing_Factor', 15);
2068 v_use_pay_basis := 1;
2069
2070 IF v_pay_basis = 'MONTHLY' THEN
2071
2072 hr_utility.set_location('Get_Annualizing_Factor', 17);
2073 v_annualizing_factor := 12;
2074
2075 ELSIF v_pay_basis = 'HOURLY' THEN
2076
2077 hr_utility.set_location('Get_Annualizing_Factor', 19);
2078
2079 IF p_period_start_date IS NOT NULL THEN
2080 v_range_start := p_period_start_date;
2081 v_range_end := p_period_end_date;
2082 v_period_hours := TRUE;
2083 ELSE
2084 v_range_start := sysdate;
2085 v_range_end := sysdate + 6;
2086 v_period_hours := FALSE;
2087 END IF;
2088
2089 /* IF UPPER(p_asg_work_sched) <> 'NOT ENTERED' THEN
2090
2091 -- Hourly employee using work schedule.
2092 -- Get work schedule name
2093
2094 -- v_ws_id := to_number(p_asg_work_sched);
2095 v_ws_id := fnd_number.canonical_to_number(p_asg_work_sched);
2096
2097 SELECT user_column_name
2098 INTO v_work_sched_name
2099 FROM pay_user_columns
2100 WHERE user_column_id = v_ws_id
2101 AND NVL(business_group_id, p_bg) = p_bg
2102 AND NVL(legislation_code,'CA') = 'CA';
2103
2104 hr_utility.set_location('Get_Annualizing_Factor', 21);
2105 */
2106
2107 hr_utility.set_location('calling core udfs', 52);
2108 v_hrs_per_range := PAY_CORE_FF_UDFS.calculate_actual_hours_worked (
2109 p_assignment_action_id
2110 ,p_assignment_id
2111 ,p_bg
2112 ,p_element_entry_id
2113 ,p_date_earned
2114 ,p_period_start_date
2115 ,p_period_end_date
2116 ,NULL
2117 ,'Y'
2118 ,'BUSY'
2119 ,'CA'--p_legislation_code
2120 ,v_schedule_source
2121 ,v_schedule
2122 ,v_return_status
2123 ,v_return_message);
2124
2125 /* ELSE-- Hourly emp using Standard Hours on asg.
2126
2127 hr_utility.set_location('Get_Annualizing_Factor', 23);
2128
2129
2130 v_hrs_per_range := Standard_Hours_Worked( p_asg_std_hrs,
2131 v_range_start,
2132 v_range_end,
2133 p_asg_std_freq);
2134
2135 END IF;
2136
2137 */
2138 IF v_period_hours THEN
2139
2140 select TPT.number_per_fiscal_year
2141 into v_periods_per_fiscal_yr
2142 from pay_payrolls_f PPF,
2143 per_time_period_types TPT,
2144 fnd_sessions fs
2145 where PPF.payroll_id = p_payroll
2146 and fs.session_id = USERENV('SESSIONID')
2147 and fs.effective_date between PPF.effective_start_date and PPF.effective_end_date
2148 and TPT.period_type = PPF.period_type;
2149
2150 v_annualizing_factor := v_hrs_per_range * v_periods_per_fiscal_yr;
2151
2152 ELSE
2153
2154 hr_utility.set_location('Get_Annualizing_Factor', 230000);
2155 v_annualizing_factor := v_hrs_per_range * c_weeks_per_year;
2156
2157 END IF;
2158
2159 ELSIF v_pay_basis = 'PERIOD' THEN
2160
2161 hr_utility.set_location('Get_Annualizing_Factor', 25);
2162
2163 SELECT TPT.number_per_fiscal_year
2164 INTO v_annualizing_factor
2165 FROM pay_payrolls_f PRL,
2166 per_time_period_types TPT,
2167 fnd_sessions fs
2168 WHERE TPT.period_type = PRL.period_type
2169 and fs.session_id = USERENV('SESSIONID')
2170 and fs.effective_date BETWEEN PRL.effective_start_date
2171 AND PRL.effective_end_date
2172 AND PRL.payroll_id = p_payroll
2173 AND PRL.business_group_id + 0 = p_bg;
2174
2175 hr_utility.set_location('Get_Annualizing_Factor', 27);
2176
2177 ELSIF v_pay_basis = 'ANNUAL' THEN
2178
2179 hr_utility.set_location('Get_Annualizing_Factor', 97);
2180
2181 v_annualizing_factor := 1;
2182
2183 ELSE
2184
2185 -- Did not recognize "pay basis", return -999 as annualizing factor.
2186 -- Remember this for debugging when zeroes come out as results!!!
2187
2188 hr_utility.set_location('Get_Annualizing_Factor', 99);
2189
2190 v_annualizing_factor := 0;
2191 RETURN v_annualizing_factor;
2192
2193 END IF;
2194
2195 exception
2196
2197 WHEN NO_DATA_FOUND THEN
2198
2199 hr_utility.set_location('Get_Annualizing_Factor', 101);
2200 v_use_pay_basis := 0;
2201
2202 end;
2203
2204 IF v_use_pay_basis = 0 THEN
2205
2206 -- Not using pay basis as frequency...
2207
2208 IF (p_freq IS NULL) OR
2209 (UPPER(p_freq) = 'PERIOD') OR
2210 (UPPER(p_freq) = 'NOT ENTERED') THEN
2211
2212 -- Get "annuallizing factor" from period type of the payroll.
2213
2214 hr_utility.set_location('Get_Annualizing_Factor', 20);
2215
2216 SELECT TPT.number_per_fiscal_year
2217 INTO v_annualizing_factor
2218 FROM pay_payrolls_f PRL,
2219 per_time_period_types TPT,
2220 fnd_sessions fs
2221 WHERE TPT.period_type = PRL.period_type
2222 and fs.session_id = USERENV('SESSIONID')
2223 and fs.effective_date BETWEEN PRL.effective_start_date
2224 AND PRL.effective_end_date
2225 AND PRL.payroll_id = p_payroll
2226 AND PRL.business_group_id + 0 = p_bg;
2227
2228 hr_utility.set_location('Get_Annualizing_Factor', 22);
2229
2230 ELSIF UPPER(p_freq) <> 'HOURLY' THEN
2231
2232 -- Not hourly, an actual time period type!
2233
2234 begin
2235
2236 hr_utility.set_location('Get_Annualizing_Factor',24);
2237
2238 SELECT PT.number_per_fiscal_year
2239 INTO v_annualizing_factor
2240 FROM per_time_period_types PT
2241 WHERE UPPER(PT.period_type) = UPPER(p_freq);
2242
2243 /* changed for bug 5155854
2244 decode(UPPER(p_freq),'W','WEEK','M','MONTH','D','DAY','Y','YEAR','H','HOUR');
2245 */
2246
2247 hr_utility.set_location('Get_Annualizing_Factor',26);
2248
2249 exception when NO_DATA_FOUND then
2250
2251 -- Added as part of SALLY CLEANUP.
2252 -- Could have been passed in an ASG_FREQ dbi which might have the values of
2253 -- 'Day' or 'Month' which do not map to a time period type. So we'll do these by hand.
2254
2255 hr_utility.set_location('Get_Annualizing_Factor',27);
2256 IF UPPER(p_freq) = 'DAY' THEN /* changed D to DAY and M to Month for bug 5155854 */
2257 v_annualizing_factor := c_days_per_year;
2258 ELSIF UPPER(p_freq) = 'MONTH' THEN
2259 v_annualizing_factor := c_months_per_year;
2260 END IF;
2261
2262 end;
2263
2264 ELSE -- Hourly employee...
2265
2266 hr_utility.set_location('Get_Annualizing_Factor', 28);
2267
2268 IF p_period_start_date IS NOT NULL THEN
2269 v_range_start := p_period_start_date;
2270 v_range_end := p_period_end_date;
2271 v_period_hours := TRUE;
2272 ELSE
2273 v_range_start := sysdate;
2274 v_range_end := sysdate + 6;
2275 v_period_hours := FALSE;
2276 END IF;
2277
2278 /* IF UPPER(p_asg_work_sched) <> 'NOT ENTERED' THEN
2279
2280 -- Hourly emp using work schedule.
2281 -- Get work schedule name:
2282
2283 -- v_ws_id := to_number(p_asg_work_sched);
2284 v_ws_id := fnd_number.canonical_to_number(p_asg_work_sched);
2285
2286 SELECT user_column_name
2287 INTO v_work_sched_name
2288 FROM pay_user_columns
2289 WHERE user_column_id = v_ws_id
2290 AND NVL(business_group_id, p_bg) = p_bg
2291 AND NVL(legislation_code,'CA') = 'CA';
2292
2293 hr_utility.set_location('Get_Annualizing_Factor',30);
2294
2295 hr_utility.set_location('calling core udfs', 53);
2296 */
2297 v_hrs_per_range := PAY_CORE_FF_UDFS.calculate_actual_hours_worked (
2298 p_assignment_action_id
2299 ,p_assignment_id
2300 ,p_bg
2301 ,p_element_entry_id
2302 ,p_date_earned
2303 ,p_period_start_date
2304 ,p_period_end_date
2305 ,NULL
2306 ,'Y'
2307 ,'BUSY'
2308 ,'CA'--p_legislation_code
2309 ,v_schedule_source
2310 ,v_schedule
2311 ,v_return_status
2312 ,v_return_message);
2313
2314 /* ELSE-- Hourly emp using Standard Hours on asg.
2315
2316 hr_utility.set_location('Get_Annualizing_Factor', 23);
2317
2318
2319 v_hrs_per_range := Standard_Hours_Worked( p_asg_std_hrs,
2320 v_range_start,
2321 v_range_end,
2322 p_asg_std_freq);
2323
2324 END IF; */
2325
2326
2327 IF v_period_hours THEN
2328
2329 select TPT.number_per_fiscal_year
2330 into v_periods_per_fiscal_yr
2331 from pay_payrolls_f PPF,
2332 per_time_period_types TPT,
2333 fnd_sessions fs
2334 where PPF.payroll_id = p_payroll
2335 and fs.session_id = USERENV('SESSIONID')
2336 and fs.effective_date between PPF.effective_start_date and PPF.effective_end_date
2337 and TPT.period_type = PPF.period_type;
2338
2339 v_annualizing_factor := v_hrs_per_range * v_periods_per_fiscal_yr;
2340
2341 ELSE
2342
2343 v_annualizing_factor := v_hrs_per_range * c_weeks_per_year;
2344
2345 END IF;
2346
2347 END IF;
2348
2349 END IF; -- (v_use_pay_basis = 0)
2350
2351 hr_utility.set_location('v_annualizing_factor is', to_number(v_annualizing_factor));
2352 RETURN v_annualizing_factor;
2353
2354 END Get_Annualizing_Factor;
2355
2356
2357 BEGIN -- Convert Figure
2358
2359 --hr_utility.trace_on('Y', 'ORACLE');
2360 hr_utility.set_location('Convert_Period_Type', 10);
2361
2362 --
2363 -- If From_Freq and To_Freq are the same, then we're done.
2364 --
2365
2366 IF NVL(p_from_freq, 'NOT ENTERED') = NVL(p_to_freq, 'NOT ENTERED') THEN
2367
2368 RETURN p_figure;
2369
2370 END IF;
2371
2372 hr_utility.set_location('Mita a trace1 ',30000);
2373
2374 hr_utility.trace('v_from_freq is'|| p_from_freq);
2375 hr_utility.trace('v_to_freq is'|| p_to_freq);
2376
2377 v_from_annualizing_factor := Get_Annualizing_Factor(
2378 p_bg => p_bus_grp_id,
2379 p_payroll => p_payroll_id,
2380 p_freq => p_from_freq,
2381 p_asg_work_sched => p_asst_work_schedule,
2382 p_asg_std_hrs => p_asst_std_hours,
2383 p_asg_std_freq => p_asst_std_freq);
2384
2385 v_to_annualizing_factor := Get_Annualizing_Factor(
2386 p_bg => p_bus_grp_id,
2387 p_payroll => p_payroll_id,
2388 p_freq => p_to_freq,
2389 p_asg_work_sched => p_asst_work_schedule,
2390 p_asg_std_hrs => p_asst_std_hours,
2391 p_asg_std_freq => p_asst_std_freq);
2392
2393 --
2394 -- Annualize "Figure" and convert to To_Freq.
2395 --
2396
2397 hr_utility.set_location('v_from_annualizing_factor is', to_number(v_from_annualizing_factor));
2398 hr_utility.set_location('v_to_annualizing_factor is', to_number(v_to_annualizing_factor));
2399 hr_utility.set_location('Convert_Period_Type', 170);
2400
2401 IF v_to_annualizing_factor = 0 OR
2402 v_to_annualizing_factor = -999 OR
2403 v_from_annualizing_factor = -999 THEN
2404
2405 hr_utility.set_location('Convert_Period_Type', 175);
2406
2407 v_converted_figure := 0;
2408 RETURN v_converted_figure;
2409
2410 ELSE
2411
2412 hr_utility.set_location('Convert_Period_Type', 180);
2413 /* hard coded values are for testing */
2414
2415 -- v_from_annualizing_factor := 12;
2416 -- v_to_annualizing_factor := 1040;
2417
2418 v_converted_figure := (p_figure * v_from_annualizing_factor) / v_to_annualizing_factor;
2419
2420 END IF;
2421
2422 -- Done
2423
2424 RETURN v_converted_figure;
2425
2426 END Convert_Period_Type;
2427
2428 --
2429 -- **********************************************************************
2430 --
2431 FUNCTION work_schedule_total_hours(
2432 p_bg_id in NUMBER,
2433 p_ws_name in VARCHAR2,
2434 p_range_start in DATE ,
2435 p_range_end in DATE )
2436 RETURN NUMBER IS
2437
2438 -- local constants
2439
2440 c_ws_tab_name VARCHAR2(80) := 'COMPANY WORK SCHEDULES';
2441
2442 -- local variables
2443
2444 /* 353434, 368242 : Fixed number width for total hours */
2445 v_total_hours NUMBER(15,7) := 0;
2446 v_range_start DATE;
2447 v_range_end DATE;
2448 v_curr_date DATE;
2449 v_curr_day VARCHAR2(3); -- 3 char abbrev for day of wk.
2450 v_ws_name VARCHAR2(80); -- Work Schedule Name.
2451 v_gtv_hours VARCHAR2(80); -- get_table_value returns varchar2
2452 -- Remember to TO_NUMBER result.
2453 v_fnd_sess_row VARCHAR2(1);
2454 l_exists VARCHAR2(1);
2455 v_day_no NUMBER;
2456
2457 BEGIN -- work_schedule_total_hours
2458
2459 -- Set range to a single week if no dates are entered:
2460 -- IF (p_range_start IS NULL) AND (p_range_end IS NULL) THEN
2461 --
2462 hr_utility.set_location('work_schedule_total_hours setting dates', 3);
2463 v_range_start := NVL(p_range_start, sysdate);
2464 v_range_end := NVL(p_range_end, sysdate + 6);
2465 --
2466 -- END IF;
2467 -- Check for valid range
2468 hr_utility.set_location('work_schedule_total_hours', 5);
2469 IF v_range_start > v_range_end THEN
2470 --
2471 hr_utility.set_location('work_schedule_total_hours', 7);
2472 RETURN v_total_hours;
2473 -- hr_utility.set_message(801,'PAY_xxxx_INVALID_DATE_RANGE');
2474 -- hr_utility.raise_error;
2475 --
2476 END IF;
2477 --
2478 -- Get_Table_Value requires row in FND_SESSIONS. We must insert this
2479 -- record if one doe not already exist.
2480 --
2481 SELECT DECODE(COUNT(session_id), 0, 'N', 'Y')
2482 INTO v_fnd_sess_row
2483 FROM fnd_sessions
2484 WHERE session_id = userenv('sessionid');
2485 --
2486 IF v_fnd_sess_row = 'N' THEN
2487 --
2488 dt_fndate.set_effective_date (p_effective_date => sysdate);
2489 -- INSERT INTO fnd_sessions
2490 -- SELECT userenv('sessionid'),
2491 -- sysdate
2492 -- FROM sys.dual;
2493 --
2494 END IF;
2495 --
2496 hr_utility.set_location('work_schedule_total_hours', 10);
2497 -- Track range dates:
2498 hr_utility.set_location('range start = '||to_char(v_range_start), 5);
2499 hr_utility.set_location('range end = '||to_char(v_range_end), 6);
2500 --
2501 -- Check if the work schedule is an id or a name. If the work
2502 -- schedule does not exist, then return 0.
2503 --
2504 BEGIN
2505 select 'Y'
2506 into l_exists
2507 from pay_user_tables PUT,
2508 pay_user_columns PUC
2509 where PUC.USER_COLUMN_NAME = p_ws_name
2510 and PUC.user_table_id = PUT.user_table_id
2511 and PUT.user_table_name = c_ws_tab_name
2512 and NVL(PUC.business_group_id, p_bg_id) = p_bg_id
2513 and NVL(PUC.legislation_code,'CA') = 'CA';
2514
2515 EXCEPTION WHEN NO_DATA_FOUND THEN NULL;
2516 END;
2517
2518 if l_exists = 'Y' then
2519 v_ws_name := p_ws_name;
2520 else
2521 BEGIN
2522 select PUC.USER_COLUMN_NAME
2523 into v_ws_name
2524 from pay_user_tables PUT,
2525 pay_user_columns PUC
2526 where PUC.USER_COLUMN_ID = p_ws_name
2527 and PUT.user_table_name = c_ws_tab_name
2528 and PUC.user_table_id = PUT.user_table_id
2529 and NVL(PUC.business_group_id, p_bg_id) = p_bg_id
2530 and NVL(PUC.legislation_code,'CA') = 'CA';
2531
2532 EXCEPTION WHEN NO_DATA_FOUND THEN
2533 RETURN v_total_hours;
2534 END;
2535 end if;
2536 --
2537 v_curr_date := v_range_start;
2538 --
2539 hr_utility.set_location('work_schedule_total_hours curr_date = '||to_char(v_curr_date), 20);
2540 --
2541 LOOP
2542 v_day_no := TO_CHAR(v_curr_date, 'D');
2543
2544 hr_utility.set_location('curr_day_no = '||to_char(v_day_no), 20);
2545
2546 SELECT decode(v_day_no,1,'SUN',2,'MON',3,'TUE',
2547 4,'WED',5,'THU',6,'FRI',7,'SAT')
2548 INTO v_curr_day
2549 FROM DUAL;
2550
2551 --
2552 hr_utility.set_location('curr_day = '||v_curr_day, 20);
2553
2554 --
2555 hr_utility.set_location('work_schedule_total_hours.gettabval', 25);
2556 v_total_hours := v_total_hours +
2557 fnd_number.canonical_to_number(hruserdt.get_table_value(p_bg_id,
2558 c_ws_tab_name,
2559 v_ws_name,
2560 v_curr_day));
2561 v_curr_date := v_curr_date + 1;
2562 --
2563 hr_utility.set_location('curr_date = '||to_char(v_curr_date), 20);
2564 --
2565 EXIT WHEN v_curr_date > v_range_end;
2566 --
2567 END LOOP;
2568 --
2569 RETURN v_total_hours;
2570 --
2571 END work_schedule_total_hours;
2572 --
2573 -- **********************************************************************
2574 --
2575 FUNCTION chained_element_exists(p_bg_id in NUMBER,
2576 p_asst_id in NUMBER,
2577 p_payroll_id in NUMBER,
2578 p_date_earned in DATE,
2579 p_ele_name IN VARCHAR2) RETURN VARCHAR2 IS
2580 -- local vars
2581 v_ele_exists VARCHAR2(1);
2582 --
2583 BEGIN
2584 --
2585 -- Get formula context values: bg, payroll, asst ids; date earned.
2586 --
2587 -- ...
2588 --
2589 SELECT DECODE(COUNT(0), 0, 'N', 'Y')
2590 INTO v_ele_exists
2591 FROM PAY_ELEMENT_ENTRIES_F ELE,
2592 PAY_ELEMENT_LINKS_F ELI,
2593 PAY_ELEMENT_TYPES_F ELT
2594 WHERE p_date_earned
2595 BETWEEN ELE.effective_start_date
2596 AND ELE.effective_end_date
2597 AND ELE.assignment_id = p_asst_id
2598 AND ELE.element_link_id = ELI.element_link_id
2599 AND ELI.business_group_id + 0 = p_bg_id
2600 AND ELI.element_type_id = ELT.element_type_id
2601 AND NVL(ELT.business_group_id, p_bg_id) = p_bg_id
2602 AND UPPER(ELT.element_name) = UPPER(p_ele_name);
2603
2604 RETURN v_ele_exists;
2605
2606 END chained_element_exists;
2607
2608 --
2609 -- **********************************************************************
2610 --
2611
2612 FUNCTION us_jurisdiction_val (p_jurisdiction_code in VARCHAR2)
2613 RETURN VARCHAR2 IS
2614
2615 v_valid_jurisdiction VARCHAR2(1) := 'E'; -- RETURN var.
2616
2617 BEGIN
2618
2619 hr_utility.set_location('Jurisdiction_Validation', 01);
2620
2621 IF substr(p_jurisdiction_code, 8,4) = '0000' THEN
2622
2623 IF substr(p_jurisdiction_code, 4,3) = '000' THEN
2624
2625 -- Only entered a state geo, check against PAY_US_STATES.
2626
2627 SELECT 'S'
2628 INTO v_valid_jurisdiction
2629 FROM PAY_US_STATES
2630 WHERE STATE_CODE = substr(p_jurisdiction_code, 1,2);
2631
2632 ELSE
2633
2634 -- State/County entered
2635
2636 SELECT 'S'
2637 INTO v_valid_jurisdiction
2638 FROM PAY_US_COUNTIES
2639 WHERE STATE_CODE = substr(p_jurisdiction_code, 1,2)
2640 AND COUNTY_CODE = substr(p_jurisdiction_code, 4,3);
2641
2642 END IF;
2643
2644 ELSE
2645
2646 -- State/County/City entered
2647
2648 SELECT 'S'
2649 INTO v_valid_jurisdiction
2650 FROM PAY_US_CITY_NAMES
2651 WHERE STATE_CODE = substr(p_jurisdiction_code, 1,2)
2652 AND COUNTY_CODE = substr(p_jurisdiction_code, 4,3)
2653 AND CITY_CODE = substr(p_jurisdiction_code, 8,4)
2654 AND PRIMARY_FLAG ='Y';
2655
2656 END IF;
2657
2658 RETURN v_valid_jurisdiction;
2659
2660 EXCEPTION
2661 WHEN NO_DATA_FOUND THEN
2662
2663 hr_utility.set_location('Jurisdiction_Validation', 03);
2664 v_valid_jurisdiction := 'E';
2665 RETURN v_valid_jurisdiction;
2666
2667 END us_jurisdiction_val;
2668
2669
2670 --
2671 -- **********************************************************************
2672 --
2673 FUNCTION get_process_run_flag ( p_date_earned IN DATE,
2674 p_ele_type_id IN NUMBER) RETURN VARCHAR2 IS
2675 --
2676 v_proc_run_type VARCHAR2(3) := 'REG';
2677 --
2678 BEGIN
2679 --
2680 --
2681 -- GET <ELE_NAME>_PROCESSING_RUN_TYPE. IF = 'ALL' then SKIP='N'.
2682 -- This DDF info is held in ELEMENT_INFORMATION3.
2683 --
2684 hr_utility.set_location('get_process_run_flag', 10);
2685 --
2686 begin
2687 SELECT element_information3
2688 INTO v_proc_run_type
2689 FROM pay_element_types_f
2690 WHERE p_date_earned
2691 BETWEEN effective_start_date
2692 AND effective_end_date
2693 AND element_type_id = p_ele_type_id;
2694 --
2695 hr_utility.set_location('get_process_run_flag', 20);
2696 RETURN v_proc_run_type;
2697 --
2698 exception when NO_DATA_FOUND then
2699 hr_utility.set_location('get_process_run_flag', 30);
2700 RETURN v_proc_run_type;
2701 end;
2702 --
2703 END get_process_run_flag;
2704 --
2705 -- **********************************************************************
2706 --
2707 FUNCTION check_dedn_freq ( p_payroll_id IN NUMBER,
2708 p_bg_id IN NUMBER,
2709 p_pay_action_id IN NUMBER,
2710 p_date_earned IN DATE,
2711 p_ele_type_id IN NUMBER) RETURN VARCHAR2 IS
2712
2713 v_skip_element VARCHAR2(1) := 'N';
2714 v_number_per_fy NUMBER(3);
2715 v_run_number NUMBER(3);
2716 v_proc_run_type VARCHAR2(3);
2717 v_freq_rule_exists NUMBER(3);
2718 v_period_end_date DATE;
2719
2720 BEGIN
2721
2722 -- Check that <ELE_NAME>_PROCESSING_RUN_TYPE = 'ALL', meaning SKIP='N'.
2723 -- This DDF info is held in ELEMENT_INFORMATION3.
2724
2725 hr_utility.set_location('check_dedn_freq', 10);
2726
2727 begin
2728 SELECT element_information3
2729 INTO v_proc_run_type
2730 FROM pay_element_types_f
2731 WHERE p_date_earned
2732 BETWEEN effective_start_date
2733 AND effective_end_date
2734 AND element_type_id = p_ele_type_id;
2735
2736 IF v_proc_run_type = 'ALL' THEN
2737 RETURN v_skip_element;
2738 END IF;
2739
2740 exception when NO_DATA_FOUND then
2741 RETURN v_skip_element;
2742 end;
2743 --
2744 -- See if freq rule even comes into play here:
2745 --
2746 hr_utility.set_location('check_dedn_freq', 45);
2747 SELECT COUNT(0)
2748 INTO v_freq_rule_exists
2749 FROM pay_ele_payroll_freq_rules EPF
2750 WHERE element_type_id = p_ele_type_id
2751 AND payroll_id = p_payroll_id
2752 AND business_group_id + 0 = p_bg_id;
2753
2754 IF v_freq_rule_exists = 0 THEN
2755 RETURN v_skip_element;
2756 END IF;
2757 --
2758 -- If we're here, then maybe freq rule will affect processing...
2759 -- Get payroll period type.number per fiscal year.
2760 --
2761 SELECT end_date
2762 INTO v_period_end_date
2763 FROM per_time_periods
2764 WHERE p_date_earned BETWEEN start_date AND end_date
2765 AND payroll_id = p_payroll_id;
2766
2767 SELECT TPT.number_per_fiscal_year
2768 INTO v_number_per_fy
2769 FROM per_time_period_types TPT,
2770 pay_payrolls_f PRL
2771 WHERE TPT.period_type = PRL.period_type
2772 AND PRL.business_group_id + 0 = p_bg_id
2773 AND PRL.payroll_id = p_payroll_id;
2774 --
2775 -- Get period number in Month or Year according to number per fiscal year.
2776 -- ...into v_run_number...
2777 -- What we NEED is the actual PERIOD # w/in Month or Year.
2778 --
2779 IF v_number_per_fy < 12 THEN
2780 hr_utility.set_location('check_dedn_freq', 20);
2781
2782 SELECT COUNT(0)
2783 INTO v_run_number
2784 FROM per_time_periods PTP
2785 WHERE PTP.end_date
2786 BETWEEN TRUNC(p_date_earned,'YEAR')
2787 AND v_period_end_date
2788 AND PTP.payroll_id = p_payroll_id;
2789
2790 ELSIF v_number_per_fy > 12 THEN
2791 hr_utility.set_location('check_dedn_freq', 30);
2792
2793 SELECT COUNT(0)
2794 INTO v_run_number
2795 FROM per_time_periods PTP
2796 WHERE PTP.end_date
2797 BETWEEN TRUNC(p_date_earned,'MONTH')
2798 AND v_period_end_date
2799 AND PTP.payroll_id = p_payroll_id;
2800
2801 ELSIF v_number_per_fy = 12 THEN
2802 hr_utility.set_location('check_dedn_freq', 40);
2803 v_skip_element := 'N';
2804 RETURN v_skip_element;
2805 END IF;
2806
2807 --
2808 -- Check frequency rule:
2809 -- If none exists, then process!
2810 --
2811
2812 hr_utility.set_location('check_dedn_freq', 50);
2813 SELECT 'N'
2814 INTO v_skip_element
2815 FROM pay_ele_payroll_freq_rules EPF,
2816 pay_freq_rule_periods FRP
2817 WHERE FRP.period_no_in_reset_period = v_run_number
2818 AND FRP.ele_payroll_freq_rule_id = EPF.ele_payroll_freq_rule_id
2819 AND EPF.business_group_id + 0 = p_bg_id
2820 AND EPF.payroll_id = p_payroll_id
2821 AND EPF.element_type_id = p_ele_type_id;
2822
2823 RETURN v_skip_element;
2824
2825 EXCEPTION
2826 WHEN NO_DATA_FOUND THEN
2827 hr_utility.set_location('check_dedn_freq', 60);
2828 v_skip_element := 'Y';
2829 RETURN v_skip_element;
2830
2831 END check_dedn_freq;
2832
2833 --
2834 -- **********************************************************************
2835 --
2836 FUNCTION Separate_Check_Skip (
2837 p_bg_id in NUMBER,
2838 p_element_type_id in NUMBER,
2839 p_assact_id in NUMBER,
2840 p_payroll_id in NUMBER,
2841 p_date_earned in DATE) RETURN VARCHAR2 IS
2842
2843 -- This function is called from skip rules attached to Deductions.
2844 -- Purpose is to check if an earnings requires special "Deduction Processing"
2845 -- ie. take only Pretax and/or Tax deductions.
2846 -- Algorithm:
2847 -- 1. Check for run results where "Deduction Processing" inpval is something
2848 -- other than 'A' for All.
2849 -- 2. If there is, then check classification of current deduction against
2850 -- the deduction processing requirement - ie. skip any deductions that
2851 -- are not pre-tax or tax deductions; further, if dedn proc is 'Tax Only'
2852 -- then skip pre-tax dedns as well - easy!
2853
2854 -- local constants
2855
2856 -- local vars
2857 v_dedn_proc VARCHAR2(3);
2858 v_dedn_proc_value VARCHAR2(80);
2859 v_ele_class_name VARCHAR2(80);
2860 v_skip_element VARCHAR2(1) := 'N';
2861
2862 --
2863 BEGIN -- Separate_Check_Skip
2864 --
2865
2866 hr_utility.set_location('Separate_Check_Skip', 7);
2867
2868 SELECT RRV.result_value
2869 INTO v_dedn_proc
2870 FROM pay_run_result_values RRV,
2871 pay_run_results PRR,
2872 pay_input_values_f IPV
2873 WHERE PRR.assignment_action_id = p_assact_id
2874 AND RRV.result_value <> 'A'
2875 AND RRV.run_result_id = PRR.run_result_id
2876 AND IPV.input_value_id = RRV.input_value_id
2877 AND p_date_earned
2878 BETWEEN IPV.effective_start_date
2879 AND IPV.effective_end_date
2880 AND UPPER(IPV.name) = 'DEDUCTION PROCESSING'
2881 AND IPV.business_group_id + 0 = p_bg_id;
2882
2883 --
2884 -- We now assume there is a value in Deduction Processing input value of
2885 -- either 'T' ("Tax Only") or 'PTT' ("Pre-Tax and Tax Only).
2886 --
2887
2888 v_skip_element := 'Y';
2889
2890 hr_utility.set_location('Separate_Check_Skip', 9);
2891
2892 begin
2893
2894 SELECT ECL.classification_name
2895 INTO v_ele_class_name
2896 FROM pay_element_types_f ELT,
2897 pay_element_classifications ECL
2898 WHERE ECL.classification_id = ELT.classification_id
2899 AND ELT.business_group_id + 0 = p_bg_id
2900 AND p_date_earned
2901 BETWEEN ELT.effective_start_date
2902 AND ELT.effective_end_date
2903 AND ELT.element_type_id = p_element_type_id;
2904
2905 IF UPPER(v_ele_class_name) = 'TAX DEDUCTIONS' THEN
2906
2907 -- Change v_skip_element back to 'N' if this a tax deduction.
2908 -- ie. we know DEDN PROC inpval is not null, meaning it's either TAX ONLY
2909 -- or PRETAX AND TAX ONLY.
2910
2911 hr_utility.set_location('Separate_Check_Skip', 10);
2912 v_skip_element := 'N';
2913 RETURN v_skip_element;
2914
2915 ELSIF UPPER(v_ele_class_name) = 'PRE-TAX DEDUCTIONS' AND
2916 v_dedn_proc = 'PTT' THEN
2917
2918 -- Change v_skip_element back to 'N' if dedn proc = 'PTT'
2919
2920 hr_utility.set_location('Separate_Check_Skip', 11);
2921 v_skip_element := 'N';
2922 RETURN v_skip_element;
2923
2924 END IF;
2925
2926 exception WHEN NO_DATA_FOUND THEN
2927 hr_utility.set_location('Separate_Check_Skip - Error EleClass NOTFOUND', 12);
2928 v_skip_element := 'Y';
2929 -- hr_utility.set_message(801, 'PAY_ELE_CLASS_NOTFOUND');
2930 -- hr_utility.raise_error;
2931 end;
2932
2933 RETURN v_skip_element;
2934
2935 EXCEPTION
2936 WHEN NO_DATA_FOUND THEN
2937 hr_utility.set_location('Separate_Check_Skip', 21);
2938 RETURN v_skip_element;
2939 -- Special Dedn Proc not required. SKIP_FLAG = 'N'.
2940 --
2941 END Separate_Check_Skip;
2942 --
2943 -- **********************************************************************
2944 --
2945 /* ( ) OTHER_NON_SEPARATE_CHECK
2946 Desc: Returns 'Y' if other ELEMENT ENTRIES exist where
2947 Separate_Check = 'N' or null ;
2948 OR
2949 Earnings element entries exist with no "Separate Check" input
2950 value at all.
2951 */
2952 FUNCTION Other_Non_Separate_Check (
2953 p_date_earned IN DATE,
2954 p_ass_id IN NUMBER) RETURN VARCHAR2 IS
2955
2956 -- local vars
2957 sepcheck_flag VARCHAR2(1) := 'N';
2958 --
2959
2960 BEGIN
2961
2962 hr_utility.set_location('Other_Non_Separate_Check', 10);
2963
2964 SELECT DECODE(COUNT(IPV.input_value_id), 0, 'N', 'Y')
2965 INTO sepcheck_flag
2966 FROM pay_element_entry_values_f EEV,
2967 pay_element_entries_f ELE,
2968 pay_input_values_f IPV
2969 WHERE ELE.assignment_id = p_ass_id
2970 AND p_date_earned
2971 BETWEEN ELE.effective_start_date AND ELE.effective_end_date
2972 AND ELE.element_entry_id = EEV.element_entry_id
2973 AND p_date_earned
2974 BETWEEN EEV.effective_start_date AND EEV.effective_end_date
2975 AND nvl(EEV.screen_entry_value,'N') = 'N'
2976 AND EEV.input_value_id = IPV.input_value_id
2977 AND UPPER(IPV.name) = 'SEPARATE CHECK';
2978 --
2979 IF sepcheck_flag = 'Y' THEN
2980 hr_utility.set_location('Other_Non_Separate_Check', 15);
2981 RETURN sepcheck_flag;
2982 END IF;
2983 --
2984 hr_utility.set_location('Other_Non_Separate_Check', 20);
2985
2986 SELECT DECODE(COUNT(ELE.element_entry_id), 0, 'N', 'Y')
2987 INTO sepcheck_flag
2988 FROM pay_element_entries_f ELE,
2989 pay_element_links_f ELL,
2990 pay_element_types_f ELT,
2991 pay_element_classifications ECL
2992 WHERE ELE.assignment_id = p_ass_id
2993 AND p_date_earned BETWEEN
2994 ELE.effective_start_date and ELE.effective_end_date
2995 AND ELE.element_link_id = ELL.element_link_id
2996 AND p_date_earned BETWEEN
2997 ELL.effective_start_date and ELL.effective_end_date
2998 AND ELL.element_type_id = ELT.element_type_id
2999 AND p_date_earned BETWEEN
3000 ELT.effective_start_date and ELT.effective_end_date
3001 AND ECL.classification_id = ELT.classification_id
3002 AND UPPER(ECL.classification_name) IN ( 'EARNINGS',
3003 'SUPPLEMENTAL EARNINGS',
3004 'IMPUTED EARNINGS',
3005 'NON-PAYROLL PAYMENTS')
3006 AND NOT EXISTS
3007 (SELECT 'X'
3008 FROM pay_input_values_f IPV
3009 WHERE IPV.element_type_id = ELT.element_type_id
3010 AND p_date_earned BETWEEN
3011 IPV.effective_start_date and IPV.effective_end_date
3012 AND UPPER(IPV.name) = 'SEPARATE CHECK');
3013 --
3014
3015 RETURN sepcheck_flag;
3016
3017 --
3018 EXCEPTION
3019 WHEN NO_DATA_FOUND THEN
3020 hr_utility.set_location('Other_Non_Separate_Check', 30);
3021 RETURN sepcheck_flag;
3022 --
3023 END Other_Non_Separate_Check;
3024 --
3025 /*
3026 *****************************************************
3027 FUNCTION NAME:
3028 *****************************************************
3029 OT_Base_Rate
3030 Inputs: p_ass_id, -- Context from formula, pass TO Convert_Figure
3031 p_date_earned -- Context
3032 p_work_sched -- Pass to Convert_Figure
3033 p_std_hours -- Pass to Convert_Figure
3034
3035 Outputs: v_ot_base_rate -- Hourly Rate for use by OT
3036
3037 12 Dec 1993 hparicha Created.
3038
3039 *****************************************************
3040 DESCRIPTION:
3041 *****************************************************
3042 1) Add hourly Rate from Regular Wages, Time Entry Wages,
3043 or equivalent hourly rate from Regular Salary to v_ot_base_rate;
3044 2) Get elements and formula names where "Include in OT Base" = 'Y'
3045 3) Get hourly rate from either input values or run result values
3046 - based on calculation method (ie. formula name).
3047 --
3048 Calc Rule Include in OT Base
3049 ============== ====================================
3050 Flat Amount Amount input value converted to hourly rate.
3051 Hours * Rate Rate input value.
3052 Hours * Rate * Multiple Rate input value.
3053 Percentage of Reg Sal Percentage input value * Monthly Salary, converted to
3054 hourly rate.
3055 Gross Up Gross Amount run result value converted to hourly rate.
3056 *****************************************************
3057 FUNCTION TEXT:
3058 *****************************************************
3059 */
3060 FUNCTION OT_Base_Rate ( p_bg_id in NUMBER,
3061 p_pay_id in NUMBER,
3062 p_ass_id in NUMBER,
3063 p_ass_action_id in NUMBER,
3064 p_date_earned in DATE,
3065 p_work_sched in VARCHAR2 ,
3066 p_std_hours in NUMBER ,
3067 p_ass_salary in NUMBER,
3068 p_ass_sal_basis in VARCHAR2,
3069 p_std_freq in VARCHAR2 )
3070 RETURN NUMBER IS
3071 --
3072 -- local constants
3073 --
3074 c_ot_scale VARCHAR2(80) := 'Hourly';
3075 c_rate_table_name VARCHAR2(80) := 'WAGE RATES';
3076 c_rate_table_column VARCHAR2(80) := 'Wage Rate';
3077 --
3078 -- local vars
3079 --
3080 v_entry_id NUMBER(9);
3081 v_ot_base_rate NUMBER(27,7) := 0;
3082 v_tew_rate NUMBER(27,7) := 0;
3083 v_regwage_rate NUMBER(27,7) := 0;
3084 v_regsal_rate NUMBER(27,7) := 0;
3085 v_regsal_mosal NUMBER(27,7) := 0;
3086 v_tew_rcode VARCHAR2(80);
3087 v_regwage_rcode VARCHAR2(80);
3088 v_use_regwage NUMBER(2);
3089 v_use_regsal NUMBER(2);
3090 v_ele_type_id NUMBER(9);
3091 v_ele_class_id NUMBER(9);
3092 v_include_in_ot VARCHAR2(1);
3093 v_equiv_hrly_rate VARCHAR2(80) := 'No OT';
3094 v_chk_sal VARCHAR2(1) := 'N';
3095 v_eletype_id NUMBER(9);
3096 v_ele_name VARCHAR2(80);
3097 v_ff_name VARCHAR2(80);
3098 v_flat_amount NUMBER(27,7) := 0;
3099 v_flat_total NUMBER(27,7) := 0;
3100 v_flat_count NUMBER(3) := 0;
3101 v_percentage NUMBER(27,7) := 0;
3102 v_pct_sal NUMBER(27,7) := 0;
3103 v_pct_total NUMBER(27,7) := 0;
3104 v_pct_count NUMBER(3) := 0;
3105 v_rate NUMBER(27,7) := 0;
3106 v_rate_total NUMBER(27,7) := 0;
3107 v_rate_count NUMBER(3) := 0;
3108 v_rate_rcode VARCHAR2(80);
3109 v_rate_multiple NUMBER(27,7) := 0;
3110 v_rate_mult_count NUMBER(3) := 0;
3111 v_gross_results NUMBER(3) := 0;
3112 v_gross_amount NUMBER(27,7) := 0;
3113 v_gross_total NUMBER(27,7) := 0;
3114 v_gross_count NUMBER(3) := 0;
3115 v_tew_count NUMBER(3) := 0;
3116 v_tew_total_rate NUMBER(27,7) := 0;
3117 v_pay_basis_rate NUMBER(27,7) := 0;
3118 v_work_sched_name VARCHAR2(80);
3119 v_ws_id NUMBER(9);
3120 v_range_start DATE;
3121 v_range_end DATE;
3122
3123 --
3124 --
3125 --
3126 CURSOR cur_element_type_id(p_element_name varchar2) IS
3127 SELECT element_type_id
3128 FROM pay_element_types_f
3129 WHERE element_name = p_element_name
3130 AND legislation_code = 'CA';
3131
3132 l_reg_sal_ele_id pay_element_types_f.element_type_id%TYPE;
3133 l_reg_wages_ele_id pay_element_types_f.element_type_id%TYPE;
3134 l_time_entry_ele_id pay_element_types_f.element_type_id%TYPE;
3135
3136 --
3137 -- local cursors
3138 --
3139 CURSOR get_tew_rate IS
3140 SELECT NVL(fnd_number.canonical_to_number(EEV.screen_entry_value), 0),
3141 EEV.element_entry_id
3142 FROM pay_element_entry_values_f EEV,
3143 pay_element_entries_f ELE,
3144 pay_input_values_f IPV
3145 WHERE ELE.assignment_id = p_ass_id
3146 AND ELE.element_entry_id = EEV.element_entry_id
3147 AND p_date_earned
3148 BETWEEN EEV.effective_start_date
3149 AND EEV.effective_end_date
3150 AND EEV.input_value_id = IPV.input_value_id
3151 AND IPV.element_type_id = l_time_entry_ele_id
3152 AND UPPER(IPV.name) = 'RATE';
3153 --
3154 --
3155 CURSOR get_include_in_ot IS
3156 SELECT ELT.element_type_id,
3157 ELT.element_name,
3158 FRA.formula_name
3159 FROM pay_element_entries_f ELE,
3160 pay_element_links_f ELI,
3161 pay_element_types_f ELT,
3162 pay_status_processing_rules_f SPR,
3163 ff_formulas_f FRA
3164 WHERE FRA.formula_id = SPR.formula_id
3165 AND p_date_earned
3166 BETWEEN SPR.effective_start_date
3167 AND SPR.effective_end_date
3168 AND SPR.assignment_status_type_id IS NULL
3169 AND SPR.element_type_id = ELT.element_type_id
3170 AND p_date_earned
3171 BETWEEN ELE.effective_start_date
3172 AND ELE.effective_end_date
3173 AND ELE.assignment_id = p_ass_id
3174 AND ELE.element_link_id = ELI.element_link_id
3175 AND p_date_earned
3176 BETWEEN ELI.effective_start_date
3177 AND ELI.effective_end_date
3178 AND ELI.element_type_id = ELT.element_type_id
3179 AND p_date_earned
3180 BETWEEN ELT.effective_start_date
3181 AND ELT.effective_end_date
3182 AND ELT.element_information8 = 'Y'
3183 AND ELT.element_information_category IN ( 'CA_EARNINGS',
3184 'CA_SUPPLEMENTAL EARNINGS');
3185 --
3186 -- These cursors get ALL entries of a particular element type during
3187 -- the period:
3188 /* Cursors get_flat_amounts, get_rates and get_percentage have been changed
3189 to improve performance.
3190 */
3191 CURSOR get_flat_amounts IS
3192 SELECT fnd_number.canonical_to_number(EEV.screen_entry_value)
3193 FROM pay_element_links_f pel,
3194 pay_element_entries_f ele,
3195 pay_element_entry_values_f eev,
3196 pay_input_values_f ipv
3197 WHERE pel.element_type_id = v_eletype_id
3198 AND p_date_earned BETWEEN pel.effective_start_date
3199 AND pel.effective_end_date
3200 AND ele.element_link_id = pel.element_link_id
3201 AND ele.assignment_id = p_ass_id
3202 AND ele.element_entry_id = eev.element_entry_id
3203 AND p_date_earned BETWEEN eev.effective_start_date
3204 AND eev.effective_end_date
3205 AND EEV.input_value_id = ipv.input_value_id
3206 AND IPV.element_type_id = pel.element_type_id --v_eletype_id
3207 AND IPV.name = 'Amount';
3208 --
3209 CURSOR get_rates IS
3210 SELECT fnd_number.canonical_to_number(EEV.screen_entry_value),
3211 EEV.element_entry_id
3212 FROM pay_element_links_f pel,
3213 pay_element_entries_f ele,
3214 pay_element_entry_values_f eev,
3215 pay_input_values_f ipv
3216 WHERE pel.element_type_id = v_eletype_id
3217 AND p_date_earned BETWEEN pel.effective_start_date
3218 AND pel.effective_end_date
3219 AND ele.element_link_id = pel.element_link_id
3220 AND ELE.assignment_id = p_ass_id
3221 AND ELE.element_entry_id = EEV.element_entry_id
3222 AND p_date_earned BETWEEN EEV.effective_start_date
3223 AND EEV.effective_end_date
3224 AND EEV.input_value_id = IPV.input_value_id
3225 AND IPV.element_type_id = pel.element_type_id --v_eletype_id
3226 AND IPV.name = 'Rate';
3227 --
3228 CURSOR get_percentages IS
3229 SELECT fnd_number.canonical_to_number(EEV.screen_entry_value)
3230 FROM pay_element_links_f pel,
3231 pay_element_entries_f ele,
3232 pay_element_entry_values_f eev,
3233 pay_input_values_f ipv
3234 WHERE pel.element_type_id = v_eletype_id
3235 AND p_date_earned BETWEEN pel.effective_start_date
3236 AND pel.effective_end_date
3237 AND ele.element_link_id = pel.element_link_id
3238 AND ele.assignment_id = p_ass_id
3239 AND ele.element_entry_id = EEV.element_entry_id
3240 AND p_date_earned BETWEEN EEV.effective_start_date
3241 AND EEV.effective_end_date
3242 AND eev.input_value_id = IPV.input_value_id
3243 AND ipv.element_type_id = pel.element_type_id --v_eletype_id
3244 AND ipv.name = 'Percentage';
3245 --
3246 CURSOR get_grosses IS
3247 SELECT RRV.result_value
3248 FROM pay_run_result_values RRV,
3249 pay_run_results RRS,
3250 pay_input_values_f IPV,
3251 pay_element_types_f ELT
3252 WHERE RRV.input_value_id = IPV.input_value_id
3253 AND RRV.run_result_id = RRS.run_result_id
3254 AND RRS.element_type_id = ELT.element_type_id
3255 AND RRS.assignment_action_id = p_ass_action_id
3256 AND p_date_earned
3257 BETWEEN IPV.effective_start_date
3258 AND IPV.effective_end_date
3259 AND IPV.name = 'Pay Value'
3260 AND IPV.element_type_id = ELT.element_type_id
3261 AND p_date_earned
3262 BETWEEN ELT.effective_start_date
3263 AND ELT.effective_end_date
3264 AND ELT.element_name = 'Vertex ' || v_ele_name || ' Gross';
3265 --
3266 -- Check with Roy on "<ELE_NAME> Gross" element being created for grossups.
3267 --
3268 --
3269 --
3270 --
3271 BEGIN -- OT_Base_Rate
3272 --
3273 -- Get "Regular" rate from either Time Entry Wages, Regular Wages,
3274 -- or Regular Salary. For Time Entry rate, we need to take an average
3275 -- of all Rates entered via Time Entry Wages.
3276 -- Remember to check for a rate via Rate Code!
3277 --
3278 -- Go ahead and set pay_basis_rate now - will most likely be used somewhere.
3279 --
3280 hr_utility.set_location('OT_Base_Rate', 5);
3281
3282 select start_date,
3283 end_date
3284 into v_range_start,
3285 v_range_end
3286 from per_time_periods
3287 where payroll_id = p_pay_id
3288 and p_date_earned between start_date and end_date;
3289
3290 OPEN cur_element_type_id('Regular Salary');
3291 FETCH cur_element_type_id
3292 INTO l_reg_sal_ele_id;
3293 CLOSE cur_element_type_id;
3294
3295 OPEN cur_element_type_id('Regular Wages');
3296 FETCH cur_element_type_id
3297 INTO l_reg_wages_ele_id;
3298 CLOSE cur_element_type_id;
3299
3300 OPEN cur_element_type_id('Times Entry Wages');
3301 FETCH cur_element_type_id
3302 INTO l_time_entry_ele_id;
3303 CLOSE cur_element_type_id;
3304
3305 v_pay_basis_rate := fnd_number.canonical_to_number(hr_ca_ff_udfs.convert_period_type(
3306 p_bus_grp_id => p_bg_id,
3307 p_payroll_id => p_pay_id,
3308 p_assignment_action_id => p_ass_action_id,
3309 p_assignment_id => p_ass_id ,
3310 p_element_entry_id => v_entry_id,
3311 p_date_earned => p_date_earned,
3312 p_asst_work_schedule => p_work_sched,
3313 p_asst_std_hours => p_std_hours,
3314 p_figure => p_ass_salary,
3315 p_from_freq => p_ass_sal_basis,
3316 p_to_freq => 'HOURLY',
3317 p_period_start_date => v_range_start,
3318 p_period_end_date => v_range_end,
3319 p_asst_std_freq => p_std_freq));
3320 --
3321 OPEN get_tew_rate;
3322 --
3323 LOOP
3324 hr_utility.set_location('OT_Base_Rate', 10);
3325 FETCH get_tew_rate
3326 INTO v_tew_rate, v_entry_id;
3327 EXIT WHEN get_tew_rate%NOTFOUND;
3328 --
3329 v_tew_count := v_tew_count + 1;
3330 IF v_tew_rate <> 0 THEN
3331 v_tew_total_rate := v_tew_total_rate + v_tew_rate;
3332 ELSE -- no Rate entered, check Rate Code
3333 hr_utility.set_location('OT_Base_Rate', 15);
3334 SELECT NVL(EEV.screen_entry_value, 'NOT ENTERED')
3335 INTO v_tew_rcode
3336 FROM pay_element_entry_values_f EEV,
3337 pay_element_entries_f ELE,
3338 pay_element_types_f ELT,
3339 pay_input_values_f IPV
3340 WHERE ELE.assignment_id = p_ass_id
3341 AND ELE.element_entry_id = EEV.element_entry_id
3342 AND p_date_earned
3343 BETWEEN EEV.effective_start_date
3344 AND EEV.effective_end_date
3345 AND EEV.element_entry_id = v_entry_id
3346 AND EEV.input_value_id = IPV.input_value_id
3347 AND UPPER(ELT.element_name) = 'TIME ENTRY WAGES'
3348 AND ELT.element_type_id = IPV.element_type_id
3349 AND UPPER(IPV.name) = 'RATE CODE';
3350 --
3351 IF v_tew_rcode = 'NOT ENTERED' THEN
3352 -- Use pay basis salary converted to hourly rate.
3353 v_tew_total_rate := v_tew_total_rate + v_pay_basis_rate;
3354 ELSE
3355 -- Find rate from rate table.
3356 hr_utility.set_location('OT_Base_Rate', 17);
3357 v_tew_total_rate := v_tew_total_rate +
3358 fnd_number.canonical_to_number(hruserdt.get_table_value(
3359 p_bg_id,
3360 c_rate_table_name,
3361 c_rate_table_column,
3362 v_tew_rcode));
3363 END IF;
3364 --
3365 END IF;
3366 --
3367 END LOOP;
3368 --
3369 CLOSE get_tew_rate;
3370 --
3371 IF v_tew_count = 0 THEN -- ie. only use "Regular" rates if TEW not entered.
3372 hr_utility.set_location('OT_Base_Rate', 20);
3373 SELECT COUNT(IPV.input_value_id)
3374 INTO v_use_regwage
3375 FROM pay_element_entry_values_f EEV,
3376 pay_element_entries_f ELE,
3377 pay_input_values_f IPV
3378 WHERE ELE.assignment_id = p_ass_id
3379 AND ELE.element_entry_id = EEV.element_entry_id
3380 AND p_date_earned
3381 BETWEEN EEV.effective_start_date
3382 AND EEV.effective_end_date
3383 AND EEV.input_value_id = IPV.input_value_id
3384 AND IPV.element_type_id = l_reg_wages_ele_id
3385 AND UPPER(IPV.name) = 'RATE';
3386 --
3387 IF v_use_regwage <> 0 THEN
3388 hr_utility.set_location('OT_Base_Rate', 30);
3389 SELECT NVL(fnd_number.canonical_to_number(EEV.screen_entry_value), 0),
3390 EEV.element_entry_id
3391 INTO v_regwage_rate,
3392 v_entry_id
3393 FROM pay_element_entry_values_f EEV,
3394 pay_element_entries_f ELE,
3395 pay_input_values_f IPV
3396 WHERE ELE.assignment_id = p_ass_id
3397 AND ELE.element_entry_id = EEV.element_entry_id
3398 AND p_date_earned
3399 BETWEEN EEV.effective_start_date
3400 AND EEV.effective_end_date
3401 AND EEV.input_value_id = IPV.input_value_id
3402 AND IPV.element_type_id = l_reg_wages_ele_id
3403 AND UPPER(IPV.name) = 'RATE';
3404 --
3405 IF v_regwage_rate = 0 THEN
3406 hr_utility.set_location('OT_Base_Rate', 40);
3407 SELECT NVL(EEV.screen_entry_value, 'NOT ENTERED')
3408 INTO v_regwage_rcode
3409 FROM pay_element_entry_values_f EEV,
3410 pay_element_entries_f ELE,
3411 pay_element_types_f ELT,
3412 pay_input_values_f IPV
3413 WHERE ELE.assignment_id = p_ass_id
3414 AND ELE.element_entry_id = EEV.element_entry_id
3415 AND p_date_earned
3416 BETWEEN EEV.effective_start_date
3417 AND EEV.effective_end_date
3418 AND EEV.element_entry_id = v_entry_id
3419 AND EEV.input_value_id = IPV.input_value_id
3420 AND UPPER(ELT.element_name) = 'REGULAR WAGES'
3421 AND ELT.element_type_id = IPV.element_type_id
3422 AND UPPER(IPV.name) = 'RATE CODE';
3423 --
3424 hr_utility.set_location('OT_Base_Rate', 41);
3425 v_regwage_rate := fnd_number.canonical_to_number(hruserdt.get_table_value(
3426 p_bus_group_id => p_bg_id,
3427 p_table_name => c_rate_table_name,
3428 p_col_name => c_rate_table_column,
3429 p_row_value => v_regwage_rcode));
3430 END IF;
3431 v_ot_base_rate := v_ot_base_rate + v_regwage_rate;
3432 --
3433 ELSE
3434 hr_utility.set_location('OT_Base_Rate', 50);
3435 SELECT COUNT(IPV.input_value_id)
3436 INTO v_use_regsal
3437 FROM pay_element_entry_values_f EEV,
3438 pay_element_entries_f ELE,
3439 pay_input_values_f IPV
3440 WHERE ELE.assignment_id = p_ass_id
3441 AND ELE.element_entry_id = EEV.element_entry_id
3442 AND p_date_earned
3443 BETWEEN EEV.effective_start_date
3444 AND EEV.effective_end_date
3445 AND EEV.input_value_id = IPV.input_value_id
3446 AND IPV.element_type_id = l_reg_sal_ele_id
3447 AND UPPER(IPV.name) = 'MONTHLY SALARY';
3448 --
3449 IF v_use_regsal <> 0 THEN
3450 hr_utility.set_location('OT_Base_Rate', 51);
3451 SELECT NVL(fnd_number.canonical_to_number(EEV.screen_entry_value), 0)
3452 INTO v_regsal_mosal
3453 FROM pay_element_entry_values_f EEV,
3454 pay_element_entries_f ELE,
3455 pay_input_values_f IPV
3456 WHERE ELE.assignment_id = p_ass_id
3457 AND ELE.element_entry_id = EEV.element_entry_id
3458 AND p_date_earned
3459 BETWEEN EEV.effective_start_date
3460 AND EEV.effective_end_date
3461 AND EEV.input_value_id = IPV.input_value_id
3462 AND IPV.element_type_id = l_reg_sal_ele_id
3463 AND UPPER(IPV.name) = 'MONTHLY SALARY';
3464 --
3465 hr_utility.set_location('OT_Base_Rate', 60);
3466
3467 v_regsal_rate := hr_ca_ff_udfs.Convert_Period_Type(
3468 p_bus_grp_id => p_bg_id,
3469 p_payroll_id => p_pay_id,
3470 p_assignment_action_id => p_ass_action_id,
3471 p_assignment_id => p_ass_id ,
3472 p_element_entry_id => v_entry_id,
3473 p_date_earned => p_date_earned,
3474 p_asst_work_schedule => p_work_sched,
3475 p_asst_std_hours => p_std_hours,
3476 p_figure => v_regsal_mosal,
3477 p_from_freq => p_ass_sal_basis,
3478 p_to_freq => 'HOURLY',
3479 p_period_start_date => v_range_start,
3480 p_period_end_date => v_range_end,
3481 p_asst_std_freq => p_std_freq);
3482 --
3483 END IF;
3484 --
3485 END IF; -- "Regular" rate done.
3486 --
3487 ELSE
3488 -- TEW entered, so take average:
3489 v_ot_base_rate := v_ot_base_rate + (v_tew_total_rate / v_tew_count);
3490 --
3491 END IF; -- TEW entered.
3492 --
3493 -- Now add all other "Include in OT Base" = 'Y' values.
3494 --
3495 OPEN get_include_in_ot;
3496 LOOP
3497 --
3498 hr_utility.set_location('OT_Base_Rate', 70);
3499 FETCH get_include_in_ot
3500 INTO v_eletype_id,
3501 v_ele_name,
3502 v_ff_name;
3503 EXIT WHEN get_include_in_ot%NOTFOUND;
3504 --
3505 IF SUBSTR(v_ff_name,1,11) = 'FLAT_AMOUNT' THEN
3506 -- Find "Amount" entered, convert to hourly figure.
3507 hr_utility.set_location('OT_Base_Rate', 80);
3508 OPEN get_flat_amounts;
3509 LOOP
3510 FETCH get_flat_amounts
3511 INTO v_flat_amount;
3512 EXIT WHEN get_flat_amounts%NOTFOUND;
3513 v_flat_count := v_flat_count + 1;
3514 hr_utility.set_location('OT_Base_Rate', 90);
3515
3516 v_flat_total := v_flat_total + hr_ca_ff_udfs.Convert_Period_Type(
3517 p_bus_grp_id => p_bg_id,
3518 p_payroll_id => p_pay_id,
3519 p_assignment_action_id => p_ass_action_id,
3520 p_assignment_id => p_ass_id ,
3521 p_element_entry_id => v_entry_id,
3522 p_date_earned => p_date_earned,
3523 p_asst_work_schedule => p_work_sched,
3524 p_asst_std_hours => p_std_hours,
3525 p_figure => v_flat_amount,
3526 p_from_freq => 'PERIOD',
3527 p_to_freq => 'HOURLY',
3528 p_period_start_date => v_range_start,
3529 p_period_end_date => v_range_end,
3530 p_asst_std_freq => p_std_freq);
3531 --
3532 END LOOP;
3533 CLOSE get_flat_amounts;
3534 --
3535 hr_utility.set_location('OT_Base_Rate', 100);
3536 v_ot_base_rate := v_ot_base_rate + (v_flat_total / v_flat_count);
3537 --
3538 ELSIF SUBSTR(v_ff_name,1,10) = 'PERCENTAGE' THEN
3539 hr_utility.set_location('OT_Base_Rate', 110);
3540 OPEN get_percentages;
3541 LOOP
3542 FETCH get_percentages
3543 INTO v_percentage;
3544 EXIT WHEN get_percentages%NOTFOUND;
3545 v_pct_count := v_pct_count + 1;
3546 --
3547 IF v_regsal_rate <> 0 THEN
3548 hr_utility.set_location('OT_Base_Rate', 105);
3549 v_pct_total := v_percentage * v_regsal_rate;
3550 END IF;
3551 --
3552 END LOOP;
3553 --
3554 CLOSE get_percentages;
3555 --
3556 hr_utility.set_location('OT_Base_Rate', 110);
3557 v_ot_base_rate := v_ot_base_rate + (v_pct_total / v_pct_count);
3558 --
3559 ELSIF SUBSTR(v_ff_name,1,12) = 'HOURS_X_RATE' THEN
3560 --
3561 -- Remember to look for "Rate Code" if necessary and "Multiple" always.
3562 --
3563 hr_utility.set_location('OT_Base_Rate', 115);
3564 OPEN get_rates;
3565 LOOP
3566 FETCH get_rates
3567 INTO v_rate, v_entry_id;
3568 EXIT WHEN get_rates%NOTFOUND;
3569 hr_utility.set_location('OT_Base_Rate', 120);
3570 v_rate_count := v_rate_count + 1;
3571 IF v_rate = 0 THEN
3572 hr_utility.set_location('OT_Base_Rate', 125);
3573 SELECT NVL(EEV.screen_entry_value, 'NOT ENTERED')
3574 INTO v_rate_rcode
3575 FROM pay_element_entry_values_f EEV,
3576 pay_element_entries_f ELE,
3577 pay_element_types_f ELT,
3578 pay_input_values_f IPV
3579 WHERE ELE.assignment_id = p_ass_id
3580 AND ELE.element_entry_id = EEV.element_entry_id
3581 AND p_date_earned
3582 BETWEEN EEV.effective_start_date
3583 AND EEV.effective_end_date
3584 AND EEV.element_entry_id = v_entry_id
3585 AND EEV.input_value_id = IPV.input_value_id
3586 AND UPPER(ELT.element_name) = UPPER(v_ele_name)
3587 AND ELT.element_type_id = IPV.element_type_id
3588 AND UPPER(IPV.name) = 'RATE CODE';
3589 --
3590 IF v_rate_rcode <> 'NOT ENTERED' THEN
3591 hr_utility.set_location('OT_Base_Rate', 130);
3592 v_rate := fnd_number.canonical_to_number(hruserdt.get_table_value(
3593 p_bg_id,
3594 c_rate_table_name,
3595 c_rate_table_column,
3596 v_rate_rcode));
3597 END IF;
3598 --
3599 END IF;
3600 -- Now get "Multiple" on this entry, if any.
3601 IF v_rate <> 0 THEN
3602 hr_utility.set_location('OT_Base_Rate', 135);
3603 SELECT COUNT(0)
3604 INTO v_rate_mult_count
3605 FROM pay_element_entry_values_f EEV,
3606 pay_element_entries_f ELE,
3607 pay_element_types_f ELT,
3608 pay_input_values_f IPV
3609 WHERE ELE.assignment_id = p_ass_id
3610 AND ELE.element_entry_id = EEV.element_entry_id
3611 AND p_date_earned
3612 BETWEEN EEV.effective_start_date
3613 AND EEV.effective_end_date
3614 AND EEV.element_entry_id = v_entry_id
3615 AND EEV.input_value_id = IPV.input_value_id
3616 AND UPPER(ELT.element_name) = UPPER(v_ele_name)
3617 AND ELT.element_type_id = IPV.element_type_id
3618 AND UPPER(IPV.name) = 'MULTIPLE';
3619 --
3620 IF v_rate_mult_count <> 0 THEN
3621 hr_utility.set_location('OT_Base_Rate', 140);
3622 SELECT NVL(EEV.screen_entry_value, 0)
3623 INTO v_rate_multiple
3624 FROM pay_element_entry_values_f EEV,
3625 pay_element_entries_f ELE,
3626 pay_element_types_f ELT,
3627 pay_input_values_f IPV
3628 WHERE ELE.assignment_id = p_ass_id
3629 AND ELE.element_entry_id = EEV.element_entry_id
3630 AND p_date_earned
3631 BETWEEN EEV.effective_start_date
3632 AND EEV.effective_end_date
3633 AND EEV.element_entry_id = v_entry_id
3634 AND EEV.input_value_id = IPV.input_value_id
3635 AND UPPER(ELT.element_name) = UPPER(v_ele_name)
3636 AND ELT.element_type_id = IPV.element_type_id
3637 AND UPPER(IPV.name) = 'MULTIPLE';
3638 --
3639 IF v_rate_multiple <> 0 THEN
3640 v_rate := v_rate * v_rate_multiple;
3641 END IF;
3642 --
3643 END IF;
3644 --
3645 END IF;
3646 --
3647 v_rate_total := v_rate_total + v_rate;
3648 --
3649 END LOOP;
3650 CLOSE get_rates;
3651 --
3652 v_ot_base_rate := v_ot_base_rate + (v_rate_total / v_rate_count);
3653 --
3654 ELSIF SUBSTR(v_ff_name,1,8) = 'GROSS_UP' THEN
3655 hr_utility.set_location('OT_Base_Rate', 150);
3656 OPEN get_grosses;
3657 LOOP
3658 FETCH get_grosses
3659 INTO v_gross_results;
3660 EXIT WHEN get_grosses%NOTFOUND;
3661 v_gross_count := v_gross_count + 1;
3662 IF v_gross_results <> 0 THEN
3663 -- Convert gross result to hourly figure.
3664 hr_utility.set_location('OT_Base_Rate', 160);
3665
3666 v_gross_total := v_gross_total + hr_ca_ff_udfs.Convert_Period_Type(
3667 p_bus_grp_id => p_bg_id,
3668 p_payroll_id => p_pay_id,
3669 p_assignment_action_id => p_ass_action_id,
3670 p_assignment_id => p_ass_id ,
3671 p_element_entry_id => v_entry_id,
3672 p_date_earned => p_date_earned,
3673 p_asst_work_schedule => p_work_sched,
3674 p_asst_std_hours => p_std_hours,
3675 p_figure => v_gross_amount,
3676 p_from_freq => NULL,
3677 p_to_freq => 'HOURLY',
3678 p_period_start_date => v_range_start,
3679 p_period_end_date => v_range_end,
3680 p_asst_std_freq => p_std_freq);
3681 --
3682 END IF;
3683 --
3684 END LOOP;
3685 CLOSE get_grosses;
3686 --
3687 v_ot_base_rate := v_ot_base_rate + (v_gross_total / v_gross_count);
3688 --
3689 END IF; -- Calc Method
3690 --
3691 END LOOP;
3692 --
3693 CLOSE get_include_in_ot;
3694 --
3695 RETURN v_ot_base_rate;
3696 --
3697 EXCEPTION
3698 WHEN NO_DATA_FOUND THEN
3699 hr_utility.set_location('OT_Base_Rate', 170);
3700 RETURN v_ot_base_rate;
3701 -- hr_utility.set_message(801, 'PAY_SCL_SEG_NOTFOUND');
3702 -- hr_utility.raise_error;
3703 --
3704 END OT_Base_Rate;
3705 --
3706 /*
3707 *****************************************************
3708 FUNCTION NAME:
3709 *****************************************************
3710 Dedn_Freq_Factor
3711 Inputs: p_payroll_id -- Context
3712 p_element_type_id -- Context
3713 p_date_earned -- Context
3714 p_ele_period_type -- DBI param
3715
3716 Outputs: v_dedn_freq_factor
3717
3718 28 JAN 1994 hparicha Created.
3719
3720 *****************************************************
3721 DESCRIPTION:
3722
3723 This function computes the "Deduction Frequency Factor" for deductions
3724 that have frequency rules and/or processing period type.
3725
3726 Algorithm:
3727
3728 IF period type IS NULL and frequency rules DO NOT exist THEN
3729
3730 dedn_freq_factor = 1 -- The deduction is assumed to be for the pay period.
3731
3732 ELSIF period type IS NULL and frequency rules EXIST THEN
3733
3734 dedn_freq_factor = 1 / (# pay periods in reset period)
3735
3736 ELSIF period type IS NOT NULL and frequency rules DO NOT exist THEN
3737
3738 dedn_freq_factor = (# per FY, eletype period type) /
3739 (# per FY, payroll period type)
3740
3741 ELSIF period type IS NOT NULL and frequency rules EXIST THEN
3742
3743 dedn_freq_factor = (# per FY, eletype period type) /
3744 (# per FY, reset period type) /
3745 (# pay periods in reset period)
3746
3747 END IF
3748 -- NOTE: "Reset Period" is either Month or Year.
3749 "# pay periods in reset period" means the number of deduction
3750 frequency rules for the ele/payroll that exist AND have a
3751 PERIOD_NO_IN_RESET_PERIOD less than the number of pay periods
3752 that will actually process.
3753 For example: if 2 bi-week pay periods will process in a month and the
3754 frequency rules say to process in the 1st and 3rd bi-wks of the month,
3755 then we need to process the deduction "in full" on the 1st run of the
3756 month. PERIOD_NO_IN_RESET_PERIOD = 1 and 3, so only one of these
3757 records has a column value less than the # pay periods that will
3758 actually process this month.
3759 --
3760 IF payroll period type is one of:
3761 Bi-Week,
3762 Calendar Month,
3763 Lunar Month,
3764 Semi-Month,
3765 Week
3766 THEN "reset period" = Month, number per fiscal year = 12.
3767 --
3768 IF payroll period type is one of:
3769 Bi-Month,
3770 Quarter,
3771 Semi-Year,
3772 Year
3773 THEN "reset period" = Year, number per fiscal year = 1.
3774
3775 *****************************************************
3776 FUNCTION TEXT:
3777 *****************************************************
3778 */
3779 FUNCTION Dedn_Freq_Factor (
3780 p_payroll_id in NUMBER,
3781 p_element_type_id in NUMBER,
3782 p_date_earned in DATE,
3783 p_ele_period_type in VARCHAR2)
3784 RETURN NUMBER IS
3785 --
3786 -- local constants
3787 --
3788 c_months_per_fy NUMBER(2) := 12;
3789 c_years_per_fy NUMBER(1) := 1;
3790 --
3791 -- local vars
3792 --
3793 v_date_earned DATE;
3794 v_dedn_freq_factor NUMBER(11,5);
3795 v_ele_period_num_per_fy NUMBER(3);
3796 v_pay_period_num_per_fy NUMBER(3);
3797 v_reset_periods_per_fy NUMBER(3);
3798 v_pay_periods_in_reset NUMBER(3);
3799 v_pay_periods_in_month NUMBER(3);
3800 v_pay_periods_in_year NUMBER(3);
3801 v_freq_rule_count NUMBER(3);
3802 v_freq_rules_exist VARCHAR2(1);
3803 v_pay_period_type VARCHAR2(30);
3804 --
3805 --
3806 BEGIN -- Dedn_Freq_Factor
3807 --
3808 -- v_date_earned := p_date_earned;
3809 --
3810 hr_utility.set_location('Dedn_Freq_Factor', 10);
3811 SELECT DECODE(COUNT(FRP.freq_rule_period_id), 0, 'N', 'Y')
3812 INTO v_freq_rules_exist
3813 FROM pay_freq_rule_periods FRP,
3814 pay_ele_payroll_freq_rules EPF
3815 WHERE FRP.ele_payroll_freq_rule_id = EPF.ele_payroll_freq_rule_id
3816 AND EPF.element_type_id = p_element_type_id
3817 AND EPF.payroll_id = p_payroll_id
3818 AND EPF.start_date <= p_date_earned;
3819 --
3820 IF p_ele_period_type = 'NOT ENTERED' THEN
3821 -- AND v_freq_rules_exist = 'N' (I say if ele period type is null, then
3822 -- dedn freq factor is 1 whether freq rules exist or not! Right, the
3823 -- freq rule will tell us WHEN to process the given Deduction amount.
3824 -- If there is no period type on the Dedn, then we take the FULL AMOUNT
3825 -- every time it is processed - according to freq rule.)
3826 --
3827 v_dedn_freq_factor := 1;
3828 --
3829 --
3830 ELSIF p_ele_period_type <> 'NOT ENTERED' AND v_freq_rules_exist = 'N' THEN
3831 --
3832 hr_utility.set_location('Dedn_Freq_Factor', 40);
3833 SELECT number_per_fiscal_year
3834 INTO v_ele_period_num_per_fy
3835 FROM per_time_period_types TPT
3836 WHERE UPPER(period_type) = UPPER(p_ele_period_type);
3837 --
3838 hr_utility.set_location('Dedn_Freq_Factor', 45);
3839 SELECT TPT.number_per_fiscal_year
3840 INTO v_pay_period_num_per_fy
3841 FROM per_time_period_types TPT,
3842 pay_payrolls_f PPF
3843 WHERE TPT.period_type = PPF.period_type
3844 AND p_date_earned
3845 BETWEEN PPF.effective_start_date
3846 AND PPF.effective_end_date
3847 AND PPF.payroll_id = p_payroll_id;
3848 --
3849 v_dedn_freq_factor := v_ele_period_num_per_fy / v_pay_period_num_per_fy;
3850 --
3851 ELSIF p_ele_period_type <> 'NOT ENTERED' AND v_freq_rules_exist = 'Y' THEN
3852 --
3853 hr_utility.set_location('Dedn_Freq_Factor', 50);
3854 SELECT number_per_fiscal_year
3855 INTO v_ele_period_num_per_fy
3856 FROM per_time_period_types TPT
3857 WHERE UPPER(period_type) = UPPER(p_ele_period_type);
3858 --
3859 hr_utility.set_location('Dedn_Freq_Factor', 55);
3860 SELECT TPT.number_per_fiscal_year
3861 INTO v_pay_period_num_per_fy
3862 FROM pay_payrolls_f PPF,
3863 per_time_period_types TPT
3864 WHERE TPT.period_type = PPF.period_type
3865 AND PPF.payroll_id = p_payroll_id
3866 AND p_date_earned
3867 BETWEEN PPF.effective_start_date
3868 AND PPF.effective_end_date;
3869 --
3870 IF v_pay_period_num_per_fy >= 12 THEN
3871 hr_utility.set_location('Dedn_Freq_Factor', 60);
3872 SELECT COUNT(0)
3873 INTO v_pay_periods_in_month
3874 FROM per_time_periods PTP
3875 WHERE PTP.end_date
3876 BETWEEN TRUNC(p_date_earned, 'MONTH')
3877 AND LAST_DAY(p_date_earned)
3878 AND PTP.payroll_id = p_payroll_id;
3879 --
3880 -- Frequency rules exist, so this select should never return 0.
3881 -- Just in case, we'll decode for 0 and set v_pay_periods_in_reset to 1.
3882 -- ie. so v_dedn_freq_factor will also equal 1.
3883 --
3884 hr_utility.set_location('Dedn_Freq_Factor', 65);
3885 SELECT COUNT(0)
3886 INTO v_pay_periods_in_reset
3887 FROM pay_ele_payroll_freq_rules EPF,
3888 pay_freq_rule_periods FRP
3889 WHERE FRP.period_no_in_reset_period <= v_pay_periods_in_month
3890 AND FRP.ele_payroll_freq_rule_id = EPF.ele_payroll_freq_rule_id
3891 AND EPF.payroll_id = p_payroll_id
3892 AND EPF.element_type_id = p_element_type_id;
3893 hr_utility.set_location('v_pay_periods_in_reset = ', v_pay_periods_in_reset);
3894 --
3895 IF v_ele_period_num_per_fy = v_pay_period_num_per_fy THEN
3896 v_dedn_freq_factor := 1;
3897 ELSIF v_pay_periods_in_reset = 0 THEN
3898 v_dedn_freq_factor := 0;
3899 -- Freq rules exist, but will not be processed enough this reset period.
3900 -- Ie. freq rule says process in 3rd bi-wk when only 2 will process in
3901 -- the current month, so NOTHING is taken for deduction (factor = 0).
3902 ELSE
3903 v_dedn_freq_factor := v_ele_period_num_per_fy / c_months_per_fy / v_pay_periods_in_reset;
3904 END IF;
3905 --
3906 ELSE
3907 hr_utility.set_location('Dedn_Freq_Factor', 70);
3908 SELECT COUNT(0)
3909 INTO v_pay_periods_in_year
3910 FROM per_time_periods PTP
3911 WHERE PTP.end_date
3912 BETWEEN TRUNC(p_date_earned,'YEAR')
3913 AND LAST_DAY(ADD_MONTHS(TRUNC(p_date_earned,'YEAR'), 11))
3914 AND PTP.payroll_id = p_payroll_id;
3915 --
3916 -- Frequency rules exist, so this select should never return 0.
3917 -- Just in case, we'll decode for 0 and set v_pay_periods_in_reset to 1.
3918 -- ie. so v_dedn_freq_factor will also equal 1.
3919 --
3920 hr_utility.set_location('Dedn_Freq_Factor', 75);
3921 SELECT DECODE(COUNT(0), 0, 1, COUNT(0))
3922 INTO v_pay_periods_in_reset
3923 FROM pay_ele_payroll_freq_rules EPF,
3924 pay_freq_rule_periods FRP
3925 WHERE FRP.period_no_in_reset_period <= v_pay_periods_in_year
3926 AND FRP.ele_payroll_freq_rule_id = EPF.ele_payroll_freq_rule_id
3927 AND EPF.payroll_id = p_payroll_id
3928 AND EPF.element_type_id = p_element_type_id;
3929 --
3930 hr_utility.set_location('v_pay_periods_in_reset = ', v_pay_periods_in_reset);
3931 IF v_ele_period_num_per_fy = v_pay_period_num_per_fy THEN
3932 v_dedn_freq_factor := 1;
3933 ELSE
3934 v_dedn_freq_factor := v_ele_period_num_per_fy / c_months_per_fy / v_pay_periods_in_reset;
3935 END IF;
3936 --
3937 END IF;
3938 --
3939 END IF;
3940 --
3941 hr_utility.set_location('Dedn_Freq_Factor', 80);
3942 RETURN v_dedn_freq_factor;
3943 --
3944 END Dedn_Freq_Factor;
3945 --
3946 FUNCTION Arrearage ( p_eletype_id IN NUMBER,
3947 p_date_earned IN DATE,
3948 p_partial_flag IN VARCHAR2,
3949 p_net_asg_run IN NUMBER,
3950 p_arrears_itd IN NUMBER,
3951 p_guaranteed_net IN NUMBER,
3952 p_dedn_amt IN NUMBER,
3953 p_to_arrears IN OUT NOCOPY NUMBER,
3954 p_not_taken IN OUT NOCOPY NUMBER)
3955 RETURN NUMBER IS
3956 --
3957 -- Call from fast formulae as:
3958 -- dedn_amt = arrearage ( <ELE_NAME>_PARTIAL_EE_CONTRIBUTIONS,
3959 -- NET_ASG_RUN,
3960 -- <ELE_NAME>_ARREARS_ASG_GRE_ITD,
3961 -- Guaranteed_Net,
3962 -- dedn_amt,
3963 -- to_arrears,
3964 -- not_taken)
3965 --
3966 -- Test cases need to be run where:
3967 -- 1. p_net_asg_run > p_guaranteed_net
3968 -- 2. p_net_asg_run < p_guaranteed_net
3969 -- 3. p_net_asg_run = 0
3970
3971 l_total_dedn NUMBER(27,7); -- local var
3972 l_dedn_amt NUMBER(27,7); -- local var
3973 v_dedn_multiple NUMBER(9);
3974 v_arrears_flag VARCHAR2(1);
3975 v_shadow_ele_name VARCHAR2(80);
3976 v_shadow_ele_id NUMBER(9);
3977 v_bg_id NUMBER(9);
3978
3979 --
3980 BEGIN
3981 --
3982 p_to_arrears := 0;
3983 p_not_taken := 0;
3984
3985 hr_utility.set_location('hr_ca_ff_udfs.arrearage', 1);
3986
3987 -- Determine if Arrears = 'Y' for this dedn
3988 -- Can do this by checking for "Clear Arrears" input value on base ele.
3989 -- This input value is only created when Arrears is marked Yes on Deductions
3990 -- screen.
3991
3992 begin
3993
3994 hr_utility.set_location('Shadow elename = '||v_shadow_ele_name, 38 );
3995
3996 select 'Y'
3997 into v_arrears_flag
3998 from pay_input_values_f ipv
3999 where ipv.name = 'Clear Arrears'
4000 and p_date_earned BETWEEN ipv.effective_start_date
4001 AND ipv.effective_end_date
4002 and ipv.element_type_id = p_eletype_id;
4003
4004 exception
4005
4006 WHEN NO_DATA_FOUND THEN
4007 hr_utility.set_location('Arrearage is NOT ON for this ele.', 99);
4008 v_arrears_flag := 'N';
4009
4010 WHEN TOO_MANY_ROWS THEN
4011 hr_utility.set_location('Too many rows returned for Clear Arrears inpval.', 99);
4012 v_arrears_flag := 'N';
4013
4014 end;
4015
4016 IF v_arrears_flag = 'N' THEN
4017
4018 IF p_net_asg_run - p_dedn_amt >= p_guaranteed_net THEN
4019
4020 p_to_arrears := 0;
4021 p_not_taken := 0;
4022 l_dedn_amt := p_dedn_amt;
4023 -- hr_utility.set_location('pycaudfs.arrearage.to_arrears = ', p_to_arrears);
4024 hr_utility.set_location('pycaudfs.arrearage.dedn_amt = ', p_dedn_amt);
4025 -- hr_utility.set_location('pycaudfs.arrearage.not_taken = ', p_not_taken);
4026
4027 ELSIF p_net_asg_run <= p_guaranteed_net THEN
4028 -- Don't take anything, no arrears contr either.
4029 p_to_arrears := 0;
4030 p_not_taken := p_dedn_amt;
4031 l_dedn_amt := 0;
4032 -- hr_utility.set_location('pycaudfs.arrearage.to_arrears = ', p_to_arrears);
4033 hr_utility.set_location('pycaudfs.arrearage.dedn_amt = ', l_dedn_amt);
4034 -- hr_utility.set_location('pycaudfs.arrearage.not_taken = ', p_not_taken);
4035
4036 ELSIF p_net_asg_run - p_dedn_amt < p_guaranteed_net THEN
4037
4038 IF p_partial_flag = 'Y' THEN
4039 --
4040 p_to_arrears := 0;
4041 p_not_taken := p_dedn_amt - (p_net_asg_run - p_guaranteed_net);
4042 l_dedn_amt := p_net_asg_run - p_guaranteed_net;
4043 -- hr_utility.set_location('pycaudfs.arrearage.to_arrears = ', p_to_arrears);
4044 -- hr_utility.set_location('pycaudfs.arrearage.not_taken = ', p_not_taken);
4045 hr_utility.set_location('pycaudfs.arrearage.dedn_amt = ', l_dedn_amt);
4046
4047 ELSE
4048
4049 p_to_arrears := 0;
4050 p_not_taken := p_dedn_amt;
4051 l_dedn_amt := 0;
4052 -- hr_utility.set_location('pycaudfs.arrearage.to_arrears = ', p_to_arrears);
4053 -- hr_utility.set_location('pycaudfs.arrearage.not_taken = ', p_not_taken);
4054 hr_utility.set_location('pycaudfs.arrearage.dedn_amt = ', l_dedn_amt);
4055
4056 END IF;
4057
4058 END IF;
4059
4060 ELSE -- Arrearage is on, try and clear any balance currently in arrears.
4061
4062 IF p_net_asg_run <= p_guaranteed_net THEN
4063
4064 -- Don't take anything, put it all in arrears.
4065 p_to_arrears := p_dedn_amt;
4066 p_not_taken := p_dedn_amt;
4067 l_dedn_amt := 0;
4068 -- hr_utility.set_location('pycaudfs.arrearage.to_arrears = ', p_to_arrears);
4069 hr_utility.set_location('pycaudfs.arrearage.dedn_amt = ', l_dedn_amt);
4070 -- hr_utility.set_location('pycaudfs.arrearage.not_taken = ', p_not_taken);
4071
4072 ELSE
4073
4074 l_total_dedn := p_dedn_amt + p_arrears_itd;
4075
4076 -- Attempt to clear any arrears bal:
4077
4078 IF p_net_asg_run - p_guaranteed_net >= l_total_dedn THEN
4079
4080 -- there's enough net to take it all, clear arrears:
4081 p_to_arrears := -1 * p_arrears_itd;
4082 l_dedn_amt := l_total_dedn;
4083 p_not_taken := 0;
4084 -- hr_utility.set_location('pycaudfs.arrearage.to_arrears = ', p_to_arrears);
4085 hr_utility.set_location('pycaudfs.arrearage.dedn_amt = ', l_dedn_amt);
4086 -- hr_utility.set_location('pycaudfs.arrearage.not_taken = ', p_not_taken);
4087
4088 /* Deleted a load of code above to fix 504970. If partial_flag = Y, then
4089 try and take as much of the total deduction amount (current dedn +
4090 arrears) and leave the rest in arrears. */
4091
4092 ELSIF p_partial_flag = 'Y' THEN
4093
4094 -- Going into arrears, not enough Net to take curr p_dedn_amt
4095 --
4096 p_to_arrears := (l_total_dedn - (p_net_asg_run - p_guaranteed_net)) +
4097 (-1 * p_arrears_itd);
4098 IF (p_net_asg_run - p_guaranteed_net) >= p_dedn_amt THEN
4099 p_not_taken := 0;
4100 ELSE
4101 p_not_taken := p_dedn_amt - (p_net_asg_run - p_guaranteed_net);
4102 END IF;
4103 l_dedn_amt := p_net_asg_run - p_guaranteed_net;
4104 -- hr_utility.set_location('pycaudfs.arrearage.to_arrears = ', p_to_arrears);
4105 hr_utility.set_location('pycaudfs.arrearage.dedn_amt = ', l_dedn_amt);
4106 -- hr_utility.set_location('pycaudfs.arrearage.not_taken = ', p_not_taken);
4107
4108 ELSE -- p_partial_flag = 'N'
4109 IF (p_net_asg_run - p_guaranteed_net) >= p_dedn_amt THEN
4110 -- Take the whole deduction amount.
4111 l_dedn_amt := p_dedn_amt;
4112 p_to_arrears := 0;
4113 p_not_taken := 0;
4114 ELSE
4115 -- Don't take anything, partial dedn = 'N'
4116 p_to_arrears := p_dedn_amt;
4117 p_not_taken := p_dedn_amt;
4118 l_dedn_amt := 0;
4119 END IF;
4120 -- hr_utility.set_location('pycaudfs.arrearage.to_arrears = ', p_to_arrears);
4121 hr_utility.set_location('pycaudfs.arrearage.dedn_amt = ', l_dedn_amt);
4122 -- hr_utility.set_location('pycaudfs.arrearage.not_taken = ', p_not_taken);
4123
4124 END IF;
4125
4126 END IF;
4127
4128 END IF;
4129 --
4130 -- p_to_arrears and p_not_taken are set and sent out as well.
4131 --
4132 RETURN l_dedn_amt;
4133 --
4134 END Arrearage;
4135 --
4136 -- G1668: Addr_Val optimization. We tune for the majority case of city/zip
4137 -- uniquely identifying a geocode and handle the exception cases
4138 -- as appropriate. The exceptions will be raise VERY RARELY.
4139 -- Part of the optimization assumes that the code which calls this fn
4140 -- verify that city/zip params are populated before making the call
4141 -- which essentially means city/zip are required params for optimal
4142 -- performance. In the event city/zip are not supplied, this function
4143 -- still works.
4144 --
4145 -- Optimization issues:
4146 -- 1. In order to get the BEST performance possible, we need to add a "mode"
4147 -- parameter in order to this function so that majority cases can be checked
4148 -- in the optimal order. The high volume users of this fn are MIX batch
4149 -- val and the payroll run (VERTEX formulae). Since we KNOW that MIX will
4150 -- only provide State and City params, we can quickly check this and return
4151 -- without going thru any of the gyrations needed for more general cases.
4152 -- The validation required for the VERTEX formulae will be the "general"
4153 -- case, tuned to succeed in the shortest possible time.
4154 -- Resolution: Make all params mandatory, make the calling modules take care
4155 -- of this requirement.
4156 --
4157 FUNCTION addr_val ( p_state_abbrev IN VARCHAR2 ,
4158 p_county_name IN VARCHAR2 ,
4159 p_city_name IN VARCHAR2 ,
4160 p_zip_code IN VARCHAR2 )
4161 RETURN VARCHAR2 IS
4162 --
4163 l_geocode VARCHAR2(11); -- Output var in "12-345-6789" format.
4164 --
4165 BEGIN -- Call main addr_val
4166
4167 l_geocode := addr_val(p_state_abbrev,
4168 p_county_name,
4169 p_city_name,
4170 p_zip_code,
4171 'N');
4172
4173 RETURN l_geocode;
4174 --
4175 EXCEPTION
4176 WHEN OTHERS THEN
4177 hr_utility.set_location('hr_ca_ff_udfs.addr_val', 20);
4178 l_geocode := '00-000-0000';
4179 RETURN l_geocode;
4180 --
4181 END addr_val; -- addr_val
4182
4183 FUNCTION addr_val ( p_state_abbrev IN VARCHAR2 ,
4184 p_county_name IN VARCHAR2 ,
4185 p_city_name IN VARCHAR2 ,
4186 p_zip_code IN VARCHAR2 ,
4187 p_skip_rule IN VARCHAR2 )
4188 RETURN VARCHAR2 IS
4189 --
4190 l_geocode VARCHAR2(11); -- Output var in "12-345-6789" format.
4191 l_state_code VARCHAR2(2);
4192 l_state_name VARCHAR2(25);
4193 l_county_code VARCHAR2(3);
4194 l_county_name VARCHAR2(20);
4195 l_city_code VARCHAR2(4);
4196 l_city_name VARCHAR2(25);
4197 l_zip_code VARCHAR2(5);
4198 --
4199 BEGIN -- Main addr_val
4200 l_zip_code := substr(p_zip_code, 1, 5);
4201 --
4202 begin -- (1)
4203 --
4204 begin -- (2)
4205 -- We're going 3-deep here in order to handle multiple raising
4206 -- of the same exception...will this work?
4207 -- 90% case, geo determined by city/zip combo:
4208
4209 IF (p_skip_rule = 'Y') THEN
4210 RAISE TOO_MANY_ROWS;
4211 END IF;
4212
4213 hr_utility.set_location('hr_ca_ff_udfs.addr_val', 1);
4214
4215 SELECT a.state_code||'-'||a.county_code||'-'||a.city_code
4216 INTO l_geocode
4217 FROM pay_us_zip_codes z,
4218 pay_us_city_names a
4219 WHERE a.city_name = INITCAP(p_city_name)
4220 AND
4221 z.state_code = a.state_code AND
4222 z.county_code = a.county_code AND
4223 z.city_code = a.city_code AND
4224 l_zip_code BETWEEN z.zip_start AND z.zip_end;
4225 --
4226 EXCEPTION -- (2)
4227 --
4228 WHEN NO_DATA_FOUND THEN -- Invalid city/zip combo
4229 hr_utility.set_location('hr_ca_ff_udfs.addr_val', 3);
4230 l_geocode := '00-000-0000';
4231 RETURN l_geocode;
4232 --
4233 WHEN TOO_MANY_ROWS THEN -- city/zip does not uniquely defn geo
4234 -- same county name can exists in many states
4235 SELECT state_code
4236 INTO l_state_code
4237 FROM pay_us_states
4238 WHERE state_abbrev = p_state_abbrev;
4239
4240 hr_utility.set_location('hr_ca_ff_udfs.addr_val', 5);
4241 SELECT a.state_code||'-'||a.county_code||'-'||a.city_code
4242 INTO l_geocode
4243 FROM pay_us_zip_codes z,
4244 pay_us_city_names a,
4245 pay_us_counties b
4246 WHERE a.city_name = INITCAP(p_city_name)
4247 AND a.county_code = b.county_code
4248 AND b.county_name = INITCAP(p_county_name)
4249 AND b.state_code = l_state_code
4250 AND z.state_code = a.state_code AND
4251 z.county_code = a.county_code AND
4252 z.city_code = a.city_code AND
4253 l_zip_code BETWEEN z.zip_start AND z.zip_end;
4254 --
4255 end; -- (2)
4256 --
4257 EXCEPTION -- (1)
4258 --
4259 -- Fallout from (2) ie. county/city/zip combo invalid or does not
4260 -- uniquely define geocode.
4261 WHEN NO_DATA_FOUND THEN
4262 hr_utility.set_location('hr_ca_ff_udfs.addr_val', 7);
4263 l_geocode := '00-000-0000';
4264 RETURN l_geocode;
4265 --
4266 WHEN TOO_MANY_ROWS THEN
4267 hr_utility.set_location('hr_ca_ff_udfs.addr_val', 9);
4268 SELECT a.state_code||'-'||a.county_code||'-'||a.city_code
4269 INTO l_geocode
4270 FROM pay_us_zip_codes z,
4271 pay_us_city_names a,
4272 pay_us_counties b,
4273 pay_us_states c
4274 WHERE c.state_code = a.state_code AND
4275 c.state_abbrev = UPPER(p_state_abbrev)
4276 AND
4277 b.county_name = INITCAP(p_county_name)AND
4278 b.state_code = c.state_code
4279 AND
4280 a.city_name = INITCAP(p_city_name) AND
4281 a.state_code = c.state_code AND
4282 a.county_code = b.county_code
4283 AND
4284 z.state_code = c.state_code AND
4285 z.county_code = b.county_code AND
4286 z.city_code = a.city_code AND
4287 l_zip_code BETWEEN z.zip_start AND z.zip_end;
4288 --
4289 end; -- (1)
4290 --
4291 -- We're in Main
4292 --
4293 hr_utility.set_location('hr_ca_ff_udfs.addr_val', 11);
4294 --
4295 if (substr(l_geocode,8,1) = 'U') THEN
4296 l_geocode := substr(l_geocode,1,7)||'0000';
4297 END IF;
4298 --
4299 RETURN l_geocode;
4300 --
4301 EXCEPTION -- Main addr_val
4302 -- Fallout from (1) state/county/city/zip does not uniquely define a geo.
4303 -- Return failure geocode.
4304 WHEN NO_DATA_FOUND THEN
4305 hr_utility.set_location('hr_ca_ff_udfs.addr_val', 13);
4306 l_geocode := '00-000-0000';
4307 RETURN l_geocode;
4308 --
4309 WHEN TOO_MANY_ROWS THEN
4310 hr_utility.set_location('hr_ca_ff_udfs.addr_val', 15);
4311 l_geocode := '00-000-0000';
4312 RETURN l_geocode;
4313 --
4314 END addr_val; -- Main addr_val
4315 --
4316
4317 /*
4318 Created for Bug 5097793.
4319 1] Fetch the regular earnings in the current period based on the salary basis code.
4320
4321 */
4322 FUNCTION get_earnings_and_type( p_bus_grp_id IN NUMBER,
4323 p_asst_id IN NUMBER,
4324 p_assignment_action_id IN NUMBER,
4325 p_payroll_id IN NUMBER,
4326 p_ele_entry_id IN NUMBER,
4327 p_tax_unit_id IN NUMBER,
4328 p_date_earned IN DATE,
4329 p_pay_basis IN VARCHAR2 DEFAULT NULL,
4330 p_period_start IN DATE,
4331 p_period_end IN DATE,
4332 p_element_type IN OUT NOCOPY VARCHAR2,
4333 p_value IN OUT NOCOPY NUMBER,
4334 p_input_value_name IN OUT NOCOPY VARCHAR2)
4335 RETURN NUMBER AS
4336
4337 CURSOR csr_reg_earnings(p_assignment_id NUMBER,
4338 p_bus_grp_id NUMBER,
4339 p_date_earned DATE) IS
4340 SELECT peev.screen_entry_value
4341 ,piv.name
4342 FROM pay_element_entry_values_f peev
4343 ,pay_element_entries_f pee
4344 ,pay_element_links_f pel
4345 ,pay_element_types_f pet
4346 ,pay_input_values_f piv
4347 ,per_pay_bases ppb
4348 ,per_all_assignments_f paa
4349 WHERE paa.assignment_id = p_assignment_id
4350 AND paa.business_group_id = p_bus_grp_id
4351 AND p_date_earned BETWEEN paa.effective_start_date AND paa.effective_end_date
4352 AND paa.pay_basis_id = ppb.pay_basis_id
4353 AND ppb.input_value_id = piv.input_value_id
4354 AND p_date_earned BETWEEN piv.effective_start_date AND piv.effective_end_date
4355 AND piv.element_type_id = pet.element_type_id
4356 AND p_date_earned BETWEEN pet.effective_start_date AND pet.effective_end_date
4357 AND pet.element_type_id = pel.element_type_id
4358 AND p_date_earned BETWEEN pel.effective_start_date AND pel.effective_end_date
4359 AND pel.element_link_id = pee.element_link_id
4360 AND pee.assignment_id = p_assignment_id
4361 AND p_date_earned BETWEEN pee.effective_start_date AND pee.effective_end_date
4362 AND pee.element_entry_id = peev.element_entry_id
4363 AND p_date_earned BETWEEN peev.effective_start_date AND peev.effective_end_date
4364 AND pee.element_type_id = pet.element_type_id
4365 AND peev.input_value_id = ppb.input_value_id;
4366
4367 l_value NUMBER;
4368 l_input_value_name VARCHAR2(200);
4369
4370 BEGIN
4371 hr_utility.set_location('in get_earnings_and_type',10);
4372 hr_utility.set_location('p_bus_grp_id -> '|| p_bus_grp_id ,10);
4373 hr_utility.set_location(' p_asst_id -> '|| p_asst_id ,10);
4374 hr_utility.set_location(' p_assignment_action_id -> '|| p_assignment_action_id,10);
4375 hr_utility.set_location(' p_date_earned -> '|| p_date_earned ,10);
4376 hr_utility.set_location(' p_period_start -> '|| to_char(p_period_start) ,10);
4377 hr_utility.set_location(' p_period_end -> '|| to_char(p_period_end),10);
4378 hr_utility.set_location(' p_pay_basis -> '|| p_pay_basis,10);
4379
4380 l_value := 0;
4381
4382 OPEN csr_reg_earnings(p_asst_id, p_bus_grp_id, p_date_earned);
4383 FETCH csr_reg_earnings INTO l_value,l_input_value_name;
4384 IF csr_reg_earnings%NOTFOUND THEN
4385 p_value := 0;
4386 p_element_type := 'DUMMY';
4387 p_input_value_name := 'DUMMY';
4388 ELSE
4389 p_value := l_value;
4390 p_element_type := 'REGULAR_SALARY';
4391 p_input_value_name := l_input_value_name;
4392 END IF;
4393 CLOSE csr_reg_earnings;
4394
4395 hr_utility.set_location('returning p_value '|| p_value,10);
4396 hr_utility.set_location('returning p_element_type '|| p_element_type,10);
4397 hr_utility.set_location('returning p_input_value_name '|| p_input_value_name,10);
4398
4399 RETURN 0;
4400
4401
4402 END get_earnings_and_type;
4403
4404
4405 END hr_ca_ff_udfs;
[Home] [Help]