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