[Home] [Help]
PACKAGE BODY: APPS.HR_US_FF_UDFS
Source
1 package body hr_us_ff_udfs as
2 /* $Header: pyusudfs.pkb 120.15.12020000.3 2012/11/01 17:27:37 emunisek ship $ */
3 /*
4 +======================================================================+
5 | Copyright (c) 1994 Oracle Corporation |
6 | Redwood Shores, California, USA |
7 | All rights reserved. |
8 +======================================================================+
9
10 Name : hr_us_ff_udfs
11 Filename : pyusudfs.sql
12 Change List
13 -----------
14 Date Name Vers Bug No Description
15 ---- ---- ---- ------ -----------
16 16-NOV-93 hparicha 1.0 Created
17 04-JAN-94 jmychale 40.8 G479 Corrected Other_Non_Separate_
18 Check processing
19 07-JAN-94 jmychale 40.9 G491 Removed actual hrs worked
20 param from Calculate_Period_
21 Earnings.
22 13-JAN-94 hparicha 40.11 G497 Reverted to version 40.8
23 NEED actual_hours_worked for
24 Statement of Earnings report.
25 02-FEB-94 hparicha 40.12 G542 Added Dedn_Freq_Factor.
26 25-FEB-94 hparicha 40.13 G555 Added salary basis comparison
27 to "to_freq" in Convert_Period_
28 Type. Also, make check
29 for Processing Run Type = 'ALL'
30 in Check_Dedn_Freq.
31 18-MAR-94 hparicha 40.14 G605 Return 'E' or 'S' for Jurisd
32 val.
33
34 24-APR-94 hparicha 40.15 G___ Changes to work sched tot hrs;
35 Changes to Calc Per Earnings
36 and Convert Period Type to
37 accept Work Schedule ID!
38 15-JUN-94 hparicha 40. G907 New implementation, fixes
39 to Arrearage.
40 01-AUG-94 hparicha 40.4 G1185 Updated Check_Dedn_Freq to
41 check PERIOD num in month/year
42 instead of Run in month/year.
43 05-AUG-94 hparicha 40.5 G1188 Updated Separate_Check_Skip to
44 verify when a not-null 'Dedn
45 Proc' RRV is found that 'Sep
46 Check' or 'Tax Separately'
47 is also in fact marked as 'Y'.
48 Ie. the scenario could exist
49 where Dedn Proc is 'PTT' but
50 Tax Sep/Sep Check both = 'N' -
51 in which case the deduction
52 should process as normal.
53 26-OCT-94 hparicha 40.6 G1342 Included as part of this STU
54 bug. Added "addr_val" for
55 use by VERTEX formulae and
56 "get_geocode".
57 14-NOV-94 hparicha 40.7 G1668 Optimization of addr_val.
58 15-NOV-94 hparicha 40.8 G1679 More optimization of fns -
59 proration, arrearage, period
60 type conversion.
61 24-NOV-94 rfine 40.9 G1725 Suppressed index on
62 business_group_id
63 01-DEC-94 hparicha 40.10 G1529 We longer require TaxSep earns
64 G1601 to be paid on sepchecks.
65 This is a reversal of G1188.
66 Dedn Proc is now independant
67 of Sep Check and Tax Sep
68 processing.
69 09-DEC-94 hparicha 40.11 G1530 Fixup of us_jurisdiction_val.
70 04-JAN-95 hparicha 40.12 G1565 Vacation/Sick correlation
71 to Regular Pay - changes to
72 Calc Period Earns.
73 Also need separate fn to calc
74 Vac/Sick Pay as well as a fn
75 to check for entry of Vac/Sick
76 Hours against an accrual plan.
77 25-APR-95 hparicha 40.13 Fixed addr_val -aka GET_GEOCODE
78 such that INITCAP is used
79 instead of UPPER when comparing
80 City/County/State names.
81 Latest Vertex data has been
82 converted to initcaps.
83 Actually, just UPPER both sides
84 of compare to remove any doubt.
85 Also needed
86 FND_NUMBER.CANONICAL_TO_NUMBER
87 on zip code for "between"
88 comparisons.
89 - not sure why this is needed,
90 - but the sql would not work
91 - otherwise.
92 26-APR-95 hparicha 40.14 No, idiot, UPPER on both sides
93 trashes the index...back to
94 INITCAP on literal in addr_val.
95 01-MAY-95 gpaytonm 40.15 addr_val select only doesn't
96 look at canada a.state_code
97 != 70. This fixes the invalid
98 number error
99 01-MAY-95 gpaytonm 40.16 Removed prior fix and removed
100 FND_NUMBER.CANONICAL_TO_NUMBER
101 from addr_val select instead
102 09-MAY-95 gpaytonm 40.17 Removed remaining
103 FND_NUMBER.CANONICAL_TO_NUMBER's from addr_Val
104 15-MAY-95 hparicha 40.18 Added UPPER to PERIOD_TYPE
105 comparison in Convert_Period_
106 Type.
107 23-JUN-95 gpaytonm 40.19 Changed references to
108 pay_us_cities to
109 pay_us_city_names and
110 pay_us_zip_codes where
111 appropriate
112 29-JUN-95 spanwar 40.20 Changed addr_val to convert
113 'UNKN' city codes to '0000'
114
115 07-JUL-95 hparicha 40.21 290249 Fixed arrearage function to
116 correctly handle partial dedns
117 when clearing arrears.
118
119 08-JUL-95 hparicha 40.22 264781 Proration function needs mod
120 to algo for semi-monthly and
121 monthly employees.
122
123 14-AUG-95 tgrisco 40.23 Changed addr_val to convert
124 'U' city codes to '0000'.
125
126 22-Nov-95 ssdesai 40.24 substr zipcode to 5 chars
127 in addr_val allowing for
128 zip code extension.
129
130 10-JAN-96 hparicha 40.27 Cleaned up all references to
131 specific period types
132 and pay bases in relation to
133 proration, regular salary,
134 regular wages, calculations of
135 hourly rates. Affected
136 functions: calculate_period_earn
137 convert_period_type,
138 ot_base_rate.
139 Bug 334245 - Add check for
140 primary flag
141 on pay_us_city_names select.
142
143 ??? ??? 40.28 ??? ???
144
145 4th July 1996 hparicha 40.29 360549, 366215, 378753
146 Removed consumed_entry fn.
147 Package too large.
148 Moved into pay_consumed_entry
149 package. Replaced by function
150 of same name in new package.
151 353434, 368242
152 Fixed number width for
153 total hours variables in
154 convert_period_type,
155 work_schedule_total_hours,
156 and standard_hours_worked.
157
158 25-Jul-96 P Jones 40.30 Removed user_errors
159
160 1st Nov 1996 hparicha 40.31 408507 Removing vacation and sick
161 accrual pay and hours
162 calculation from regular
163 salary and wages proration
164 calc - ie.
165 Calculate_Period_Earnings
166
167 14th Nov 1996 hparicha 40.32 408507 Undoing 40.31 - see README
168 for 408507 for explanation.
169
170 8th Dec 1997 kmundair 40.33(110.1) 509120 Overloaded addr_val.
171 24th Feb 1998 tbattoo 40.33 572081 Changed date rng prorate EEV.
172 26th Feb 1998 arashid 40.35 504970 Fix unhandled divide-by-zero
173 exception. The change made
174 is to consolidate processing
175 if arrearage is on, but the
176 whole amount cannot be cleared.
177 3rd Mar 1998 arashid 40.36 504970 Fixed the 504970 fix to take
178 the current deduction if
179 PARTIAL_FLAG = 'N' and (net -
180 guaranteed) >= current
181 deduction.
182 4th Jun 1998 jarthurt 110.2 408507 Alter the Vacation and Sick Pay
183 cursors to pick up absences not
184 tied to an accrual plan as well
185 as those that are.
186 17-AUG-1998 ekim 110.4 716066 Length has been changed
187 from (10,7) to (15,7)
188 for v_hrs_per_wk,
189 v_hrs_per_range
190 v_asst_std_hrs,
191 v_hours_in_range.
192 23-NOV-1998 tbattoo Added frequency rules
193 functionality to core
194
195 10-MAR-1999 ahanda 115.5 803662 Modified function
196 Calculate_Period_Earnings.
197 Modified select statments
198 which checks for mid
199 change in asgn to use Exists,
200 as it
201 will return multiple records
202 if the assgn id
203 has been changed more than
204 once in a Pay Period.
205 21-Apr-1999 scgrant 115.6 Multi-radix changes.
206
207 Description: User-Defined
208 Functions required for US
209 implementations.
210 19-MAY-99 gpaytonm 115.12 Removed hr_utility.trace
211 20-MAY-99 VMehta 115.13 Converted p_tax_unit_id to
212 character while comparing it to segment1 in hr_soft_coding_
213 keyflex table to overcome
214 'invali number' error.
215 Bug 894503.
216 25-MAY-1999 mmukherj 115.14 868824 Changes made to include
217 legislation code in selecting
218 elements, otherwise it might
219 fetch multiple row, because of
220 same element present for canada
221 28-Sep-1999 djoshi added function for
222 pay_us_country
223 28-OCT-1999 mreid 115.17 Changed territory short name
224 retrieve to come from VL view.
225 09-may-2000 vmehta 115.18 863771 Modified function
226 work_schedule_total_hours to
227 check for business_group_id and
228 user_table_id while obtaining
229 user_column_name from
230 pay_user_columns
231 01-JUL-2000 vmehta 115.19 Added a check of the last 3 characters
232 in the city name. This is to fix a
233 problem where Air Force Bases (in the
234 City_name would error during processing
235 of a payroll run. Example "Elgin AFB",
236 this is how the name appears in the
237 pay_us_city_names table. however the
238 addr_val function would perform an
239 initcap to the p_city_name parameter.
240 This caused no data found. Now if the
241 last 3 characters are "AFB" do not
242 perform the Initcap. Bug 1266054
243 21-DEC-2000 ekim 115.22 ******* LEAP FROGGED *******
244 DO NOT USED v115.21.
245 This is leap frogged from 115.19.
246 Bug 1541873. Moved city_name check
247 right after the main begin of addr_val
248 as when skip_flag of 'Y' is passed
249 the city_name check is skipped.
250 23-JAN-2001 ahanda 115.23 Added the Calculation Type functoinality
251 to Package. The Hourly Calculation will
252 be done depending on the calculation
253 rule given on the payroll define screen.
254 The default calculation rule is
255 Annulization, and the other available
256 rule is Standard.
257 Also added ORDERED hint in function
258 addr_val(Bug 1484707).
259 09-MAR-2001 ssarma 115.24 Added a new parameter
260 p_hour_calc_override to
261 convert_period_type function.
262 17-SEP-2001 ptitoren 115.25 Changed insert_session_row procedures
263 to call dt_fndate.set_effective_date
264 to shield our code from future
265 FND_SESSION table changes.
266 07-FEB-2002 tclewis 115.26 Modified the code around dt_fndate
267 above to first check for the
268 existence of a row in the FND_Sessions
269 table before making the call.
270 13-FEB-2002 rsirigir 115.27 Bug 2196352
271 changed datatype/datalengths
272 for three variables
273 from FUNCTION Convert_Period_Type,from
274 v_from_stnd_factor NUMBER(10)
275 v_from_annualizing_factor NUMBER(10)
276 v_to_annualizing_factor NUMBER(10)
277 to
278 v_from_stnd_factor NUMBER(30,7)
279 v_from_annualizing_factor NUMBER(30,7)
280 v_to_annualizing_factor NUMBER(30,7)
281
282 05-APR-2001 rsirigir 115.28 Bug 1877889
283 changed to select the work
284 schedule defined
285 at the business group level instead of
286 hardcoding the default work schedule
287 (COMPANY WORK SCHEDULES ) to the
288 variable c_ws_tab_name
289 13-AUG-2002 ahanda 115.30 Changed get_flat_amounts, get_rates and
290 get_percentages for performance.
291 13-NOV-2002 tclewis 115.31 2666118 Changed work_schedule_total_hours
292 Previously it used to get the
293 day of the week via 'DT' and
294 used to pass this value to get
295 the value from
296 hruserdt.get_table_value. But
297 this was not working in the
298 Psedo translated env as
299 user_tables/row/columns are
300 not translated. Now it is
301 getting the day number and then
302 based upon the number it deter
303 mines the day. Also changed
304 standard_hours_worked to do the
305 same.
306 18-NOV-2002 tclewis 115.31 2666118 Changed convert_period_type
307 where we are querring number per
308 fiscal year. to change
309 where sysdate between ...
310 to nvl(p_period_start_date, sysdate).
311 19-NOV-2002 tclewis 115.32 Fixed GSCC compliance warning with
312 default parameter values.
313 19-nov-2002 tclewis 115.33 changed nvl(p_period_start_date, sysdate)
314 to use fnd_sessions.
315 07-Jan-2003 ekim 115.35 Made performance change in function
316 OT_Base_Rate as :
317 Added date joins and business_group_id
318 and legislation_code join. Also added
319 + ORDERED to avoid merge join.
320 Did no change to the queries which gets
321 the following variables since the cost
322 is already low (cost=9)
323 v_rate_multiple, v_rate_mult_count,
324 v_rate_code
325 Added element_type_id join in a query
326 which gets v_dedn_proc for function
327 separate_check_skip.
328 09-Jan-2003 ekim 115.36 GSCC warning fix for nocopy.
329 09-Jan-2003 meshah 115.37 changed the sql for Rate, Rate Code and
330 Monthly Salary in
331 Calculate_Period_Earnings.
332 25-MAR-2003 tclewis 115.38 Modified the query at the
333 beginning of the
334 Calculate_Period_earnings which
335 looks for
336 l_eev_info_changes (element entry value)
337 change for the salary element.
338 It now also
339 looks for a salary element starting
340 in the
341 middle of the pay period.
342 25-Jul-2003 vmehta 115.39 Modified get_annulization_factor (within
343 convert_period_type) to use fnd_sessions
344 to get the effective date instead of
345 using p_effecive_end_date. (Bug 3067262)
346 07-JAN-2004 trugless 115.40 Commented out the following code for bug
347 3271413 in the SELECT (ASG1.2) section
348 "AND EEV.effective_end_date
349 < p_period_end;"
350 Employees who had salary adjustments
351 done on the first day of a payroll
352 and were terminated
353 during the same payroll period were
354 showing zero
355 salary for the payroll run.
356
357 22-MAR-2004 asasthan 115.41 Fix for Bug 3521706
358 The where clause has been modified.
359 Additional checking between period dates removed and added to another query
360 so that 2594138 is not broken
361 30-MAR-2004 asasthan 115.42 Uncommented changes made for 3271413
362 01-APR-2004 asasthan 115.43 gscc warnings fixed
363 07-MAY-2004 asasthan 115.44 Reverting to 115.40
364 10-MAY-2004 asasthan 115.45 gscc warnings fixed
365 18-JUN-2004 sodhingr 115.46 changed the procedure addr_val to use the
366 ordered hint only for database prior to 9i
367 Fixed bug 3685724.
368 10-AUG-2004 schauhan 115.47 3783309 Changed INITCAP to UPPER in query of function
369 addr_val for county name.
370 11-AUG-2004 schauhan 115.48 3703863 Commented out the primary flag condition in the query
371 in function us_jurisdiction_val as this flag is N for
372 user defined cities.Also added rownum to handle multiple
373 rows if returned by this query.
374 04-MAR-2003 rmonge 115.49 Geocode change for JEDD jurisdictions.
375 06-DEC-2005 sackumar 115.51 4750302 Modified a Select statement in Calculate_Period_Earnings.
376 30-DEC-2005 schauhan 115.52 4868637 Modified the query.
377 21-AUG-2006 rpasumar 115.54 5343679 Modified the query.
378 31-OCT-2006 rpasumar 115.55 5629688 Removed the ORDERED hint.
379 13-NOV-2007 svannian 115.56 6319565 Modified the deduction amt
380 calculation during Negative Net Salary.
381 30-MAR-2011 sjawid 115.62 11787061 Modified function addr_val
382 10-Aug-2011 emunisek 115.63 12588037 Added function catchup_type_details, which
383 will be used to store the catchup_type selected
384 for 403/457 Catchup Elements. The stored value
385 will be used in Roth Formulas.
386 10-Aug-2011 emunisek 115.64 12588037 Corrected GSCC Errors
387 01-Nov-2012 emunisek 115.66 14385437 Added changes to check the value set for Profile
388 Option 'PAY_US_DIRECT_BALANCE_START_YEAR' to use
389 the Direct US Federal Balances approach.
390 */
391
392 --
393 -- **********************************************************************
394 -- CALCULATE_PERIOD_EARNINGS
395 -- Description: This fn performs proration for the startup elements
396 -- Regular Salary and
397 -- Regular Wages.
398 -- Proration occurs in the following scenarios:
399 -- 1. Change of assignment status to
400 -- a status which is unpaid - ie. unpaid leave, termination;
401 -- 2. Change of regular rate of pay - ie. could --
402 -- be a change in annual salary or hourly rate.
403 -- This fn also calculates and returns the actual hours worked in the period,
404 -- vacation pay, sick pay,
405 -- vacation hours, and sick hours.
406
407 FUNCTION Calculate_Period_Earnings (
408 p_bus_grp_id in NUMBER,
409 p_asst_id in NUMBER,
410 p_payroll_id in NUMBER,
411 p_ele_entry_id in NUMBER,
412 p_tax_unit_id in NUMBER,
413 p_date_earned in DATE,
414 p_pay_basis in VARCHAR2,
415 p_inpval_name in VARCHAR2,
416 p_ass_hrly_figure in NUMBER,
417 p_period_start in DATE,
418 p_period_end in DATE,
419 p_work_schedule in VARCHAR2,
420 p_asst_std_hrs in NUMBER,
421 p_actual_hours_worked in out nocopy NUMBER,
422 p_vac_hours_worked in out nocopy NUMBER,
423 p_vac_pay in out nocopy NUMBER,
424 p_sick_hours_worked in out nocopy NUMBER,
425 p_sick_pay in out nocopy NUMBER,
426 p_prorate in VARCHAR2,
427 p_asst_std_freq in VARCHAR2)
428 RETURN NUMBER IS
429
430 l_asg_info_changes NUMBER(1);
431 l_eev_info_changes NUMBER(1);
432 v_earnings_entry NUMBER(27,7);
433 v_inpval_id NUMBER(9);
434 v_pay_basis VARCHAR2(80);
435 v_pay_periods_per_year NUMBER(3);
436 v_period_earn NUMBER(27,7) ; -- Pay Period earnings.
437 v_hourly_earn NUMBER(27,7); -- Hourly Rate (earnings).
438 v_prorated_earnings NUMBER(27,7) ; -- Calc'd thru proration loops.
439 v_curr_day VARCHAR2(3); -- Currday while summing hrs for range of dates.
440 v_hrs_per_wk NUMBER(15,7);
441 v_hrs_per_range NUMBER(15,7);
442 v_asst_std_hrs NUMBER(15,7);
443 v_asst_std_freq VARCHAR2(30);
444 v_asg_status VARCHAR2(30);
445 v_hours_in_range NUMBER(15,7);
446 v_curr_hrly_rate NUMBER(27,7) ;
447 v_range_start DATE; -- range start of ASST rec
448 v_range_end DATE; -- range end of ASST rec
449 v_entry_start DATE; -- start date of ELE ENTRY rec
450 v_entry_end DATE; -- end date of ELE ENTRY rec
451 v_entrange_start DATE; -- max of entry or asst range start
452 v_entrange_end DATE; -- min of entry or asst range end
453 v_work_schedule VARCHAR2(60); -- Work Schedule ID (stored as varchar2
454 -- in HR_SOFT_CODING_KEYFLEX; convert
455 -- fnd_number.canonical_to_number when calling wshours fn.
456 v_work_sched_name VARCHAR2(80);
457 v_ws_id NUMBER(9);
458
459 b_entries_done BOOLEAN; -- flags no more entry changes in paypd
460 b_asst_changed BOOLEAN; -- flags if asst changes at least once.
461 b_on_work_schedule BOOLEAN; -- use wrk scheds or std hours
462 l_mid_period_asg_change BOOLEAN ;
463
464 /*
465 -- ************************************************************************
466 --
467 -- The following cursor "get_asst_chgs" looks for *changes* to or from
468 -- 'ACTIVE' per_assignment
469 -- records within the supplied range of dates, *WITHIN THE SAME TAX UNIT*
470 -- (ie. the tax unit as of the end of the period specified).
471 -- If no "changes" are found, then assignment information is consistent
472 -- over entire period specified.
473 -- Before calling this cursor, will need to select tax_unit_name
474 -- according to p_tax_unit_id.
475 --
476 -- ************************************************************************
477 */
478
479 --
480 -- This cursor finds ALL ASG records that are WITHIN Period Start and End Dates
481 -- including Period End Date - NOT BETWEEN since the ASG record existing across
482 -- Period Start date has already been retrieved in SELECT (ASG1).
483 -- Work Schedule segment is segment4 on assignment DDF
484 --
485
486 CURSOR get_asst_chgs IS
487 SELECT ASG.effective_start_date,
488 ASG.effective_end_date,
489 NVL(ASG.normal_hours, 0),
490 NVL(HRL.meaning, 'NOT ENTERED'),
491 NVL(SCL.segment4, 'NOT ENTERED')
492 FROM per_assignments_f ASG,
493 per_assignment_status_types AST,
494 hr_soft_coding_keyflex SCL,
495 hr_lookups HRL
496 WHERE ASG.assignment_id = p_asst_id
497 AND ASG.business_group_id + 0 = p_bus_grp_id
498 AND ASG.effective_start_date > p_period_start
499 AND ASG.effective_end_date <= p_period_end
500 AND AST.assignment_status_type_id = ASG.assignment_status_type_id
501 AND AST.per_system_status = 'ACTIVE_ASSIGN'
502 AND SCL.soft_coding_keyflex_id = ASG.soft_coding_keyflex_id
503 AND SCL.segment1 = TO_CHAR(p_tax_unit_id)
504 AND SCL.enabled_flag = 'Y'
505 AND HRL.lookup_code(+) = ASG.frequency
506 AND HRL.lookup_type(+) = 'FREQUENCY';
507
508 FUNCTION Prorate_Earnings (
509 p_bg_id IN NUMBER,
510 p_asg_hrly_rate IN NUMBER,
511 p_wsched IN VARCHAR2 DEFAULT 'NOT ENTERED',
512 p_asg_std_hours IN NUMBER,
513 p_asg_std_freq IN VARCHAR2,
514 p_range_start_date IN DATE,
515 p_range_end_date IN DATE,
516 p_act_hrs_worked IN OUT nocopy NUMBER) RETURN NUMBER IS
517
518 v_prorated_earn NUMBER(27,7) ; -- RETURN var
519 v_hours_in_range NUMBER(15,7);
520 v_ws_id NUMBER(9);
521 v_ws_name VARCHAR2(80);
522
523 BEGIN
524
525 /* Init */
526
527 --p_wsched := 'NOT ENTERED';
528 v_prorated_earn := 0;
529
530 hr_utility.trace('UDFS Entered Prorate Earnings');
531 hr_utility.trace('p_bg_id ='||to_char(p_bg_id));
532 hr_utility.trace('p_asg_hrly_rate ='||to_char(p_asg_hrly_rate));
533 hr_utility.trace('p_wsched ='||p_wsched);
534 hr_utility.trace('p_asg_std_hours ='||to_char(p_asg_std_hours));
535 hr_utility.trace('p_asg_std_freq ='||p_asg_std_freq);
536 hr_utility.trace('UDFS p_range_start_date ='||to_char(p_range_start_date));
537 hr_utility.trace('UDFS p_range_end_date ='||to_char(p_range_end_date));
538 hr_utility.trace('p_act_hrs_worked ='||to_char(p_act_hrs_worked));
539
540 -- Prorate using hourly rate passed in as param:
541
542
543 IF UPPER(p_wsched) = 'NOT ENTERED' THEN
544
545 hr_utility.set_location('Prorate_Earnings', 7);
546 hr_utility.trace('p_wsched NOT ENTERED');
547 hr_utility.trace('Calling Standard Hours Worked');
548
549 v_hours_in_range := Standard_Hours_Worked( p_asg_std_hours,
550 p_range_start_date,
551 p_range_end_date,
552 p_asg_std_freq);
553
554 -- Keep running total of ACTUAL hours worked.
555 hr_utility.set_location('Prorate_Earnings', 11);
556
557 hr_utility.trace('Keep running total of ACTUAL hours worked');
558
559 hr_utility.trace('actual_hours_worked before call= '||
560 to_char(p_act_hrs_worked));
561 hr_utility.trace('v_hours_in_range in current call= '||
562 to_char(v_hours_in_range));
563
564 p_act_hrs_worked := p_act_hrs_worked + v_hours_in_range;
565
566 hr_utility.trace('UDFS actual_hours_worked after call = '||
567 to_char(p_act_hrs_worked));
568
569 ELSE
570
571 hr_utility.set_location('Prorate_Earnings', 17);
572 hr_utility.trace('Entered WORK SCHEDULE');
573
574 hr_utility.trace('Getting WORK SCHEDULE Name');
575
576 -- Get work schedule name:
577
578 v_ws_id := fnd_number.canonical_to_number(p_wsched);
579
580 hr_utility.trace('v_ws_id ='||to_char(v_ws_id));
581
582 SELECT user_column_name
583 INTO v_ws_name
584 FROM pay_user_columns
585 WHERE user_column_id = v_ws_id
586 AND NVL(business_group_id, p_bg_id) = p_bg_id
587 AND NVL(legislation_code,'US') = 'US';
588
589 hr_utility.trace('v_ws_name ='||v_ws_name );
590 hr_utility.trace('Calling Work_Schedule_Total_Hours');
591
592 v_hours_in_range := Work_Schedule_Total_Hours(
593 p_bg_id,
594 v_ws_name,
595 p_range_start_date,
596 p_range_end_date);
597
598 p_act_hrs_worked := p_act_hrs_worked + v_hours_in_range;
599 hr_utility.trace('v_hours_in_range = '||to_char(v_hours_in_range));
600
601 END IF; -- Hours in date range via work schedule or std hours.
602
603 hr_utility.trace('v_prorated_earnings = p_asg_hrly_rate * v_hours_in_range');
604
605 v_prorated_earn := v_prorated_earn + (p_asg_hrly_rate * v_hours_in_range);
606
607 hr_utility.trace('UDFS final v_prorated_earnings = '||to_char(v_prorated_earn));
608 hr_utility.set_location('Prorate_Earnings', 97);
609 p_act_hrs_worked := ROUND(p_act_hrs_worked, 3);
610 hr_utility.trace('p_act_hrs_worked ='||to_char(p_act_hrs_worked));
611 hr_utility.trace('UDFS Leaving Prorated Earnings');
612
613 RETURN v_prorated_earn;
614
615 END Prorate_Earnings;
616
617 FUNCTION Prorate_EEV ( p_bus_group_id IN NUMBER,
618 p_pay_id IN NUMBER,
619 p_work_sched IN VARCHAR2 DEFAULT 'NOT ENTERED',
620 p_asg_std_hrs IN NUMBER,
621 p_asg_std_freq IN VARCHAR2,
622 p_pay_basis IN VARCHAR2,
623 p_hrly_rate IN OUT nocopy NUMBER,
624 p_range_start_date IN DATE,
625 p_range_end_date IN DATE,
626 p_actual_hrs_worked IN OUT nocopy NUMBER,
627 p_element_entry_id IN NUMBER,
628 p_inpval_id IN NUMBER) RETURN NUMBER IS
629 --
630 -- local vars
631 --
632 v_eev_prorated_earnings NUMBER(27,7) ; -- Calc'd thru proration loops.
633 v_earnings_entry VARCHAR2(60);
634 v_entry_start DATE;
635 v_entry_end DATE;
636 v_hours_in_range NUMBER(15,7);
637 v_curr_hrly_rate NUMBER(27,7);
638 v_ws_id NUMBER(9);
639 v_ws_name VARCHAR2(80);
640 --
641 -- Select for ALL records that are WITHIN Range Start and End Dates
642 -- including Range End Date - NOT BETWEEN since the EEV record existing across
643 -- Range Start date has already been retrieved and dealt with in SELECT (EEV1).
644 -- A new EEV record results in a change of the current hourly rate being used
645 -- in proration calculation.
646 --
647 CURSOR get_entry_chgs ( p_range_start date,
648 p_range_end date) IS
649 SELECT EEV.screen_entry_value,
650 EEV.effective_start_date,
651 EEV.effective_end_date
652 FROM pay_element_entry_values_f EEV
653 WHERE EEV.element_entry_id = p_element_entry_id
654 AND EEV.input_value_id = p_inpval_id
655 AND EEV.effective_start_date > p_range_start
656 AND EEV.effective_end_date <= p_range_end
657 ORDER BY EEV.effective_start_date;
658 --
659 BEGIN
660
661
662 /* Init */
663 --p_work_sched := 'NOT ENTERED';
664 v_eev_prorated_earnings := 0;
665
666
667 hr_utility.trace('UDFS Entering PRORATE_EEV');
668 hr_utility.trace('p_bus_group_id ='||to_char(p_bus_group_id));
669 hr_utility.trace('p_pay_id ='||to_char(p_pay_id));
670 hr_utility.trace('p_work_sched ='||p_work_sched);
671 hr_utility.trace('p_asg_std_hrs ='||to_char(p_asg_std_hrs));
672 hr_utility.trace('p_asg_std_freq ='||p_asg_std_freq);
673 hr_utility.trace('p_pay_basis ='||p_pay_basis);
674 hr_utility.trace('p_hrly_rate ='||to_char(p_hrly_rate));
675 hr_utility.trace('UDFS p_range_start_date ='||to_char(p_range_start_date));
676 hr_utility.trace('UDFS p_range_end_date ='||to_char(p_range_end_date));
677 hr_utility.trace('p_actual_hrs_worked ='||to_char(p_actual_hrs_worked));
678 hr_utility.trace('p_element_entry_id ='||to_char(p_element_entry_id));
679 hr_utility.trace('p_inpval_id ='||to_char(p_inpval_id));
680 --
681 -- Find all EEV changes, calculate new hourly rate, prorate:
682 -- SELECT (EEV1):
683 -- Select for SINGLE record that includes Period Start Date but does not
684 -- span entire period.
685 -- We know this select will return a row, otherwise there would be no
686 -- EEV changes to detect.
687 --
688 hr_utility.set_location('Prorate_EEV', 103);
689 SELECT EEV.screen_entry_value,
690 GREATEST(EEV.effective_start_date, p_range_start_date),
691 EEV.effective_end_date
692 INTO v_earnings_entry,
693 v_entry_start,
694 v_entry_end
695 FROM pay_element_entry_values_f EEV
696 WHERE EEV.element_entry_id = p_element_entry_id
697 AND EEV.input_value_id = p_inpval_id
698 AND EEV.effective_start_date <= p_range_start_date
699 AND EEV.effective_end_date >= p_range_start_date
700 AND EEV.effective_end_date < p_range_end_date;
701
702
703 hr_utility.trace('screen_entry_value ='||v_earnings_entry);
704 hr_utility.trace('v_entry_start ='||to_char(v_entry_start));
705 hr_utility.trace('v_entry_end ='||to_char(v_entry_end));
706 hr_utility.trace('Calling Convert_Period_Type ');
707 hr_utility.set_location('Prorate_EEV', 105);
708
709 v_curr_hrly_rate := Convert_Period_Type( p_bus_group_id,
710 p_pay_id,
711 p_work_sched,
712 p_asg_std_hrs,
713 v_earnings_entry,
714 p_pay_basis,
715 'HOURLY',
716 p_period_start,
717 p_period_end,
718 p_asg_std_freq);
719 hr_utility.trace('v_curr_hrly_rate ='||to_char(v_curr_hrly_rate));
720 hr_utility.set_location('Prorate_EEV', 107);
721
722 v_eev_prorated_earnings := v_eev_prorated_earnings +
723 Prorate_Earnings (
724 p_bg_id => p_bus_group_id,
725 p_asg_hrly_rate => v_curr_hrly_rate,
726 p_wsched => p_work_sched,
727 p_asg_std_hours => p_asg_std_hrs,
728 p_asg_std_freq => p_asg_std_freq,
729 p_range_start_date => v_entry_start,
730 p_range_end_date => v_entry_end,
731 p_act_hrs_worked => p_actual_hrs_worked);
732
733 hr_utility.trace('v_eev_prorated_earnings ='||
734 to_char(v_eev_prorated_earnings));
735 -- SELECT (EEV2):
736 hr_utility.trace('Opening get_entry_chgs cursor EEV2');
737
738 OPEN get_entry_chgs (p_range_start_date, p_range_end_date);
739 LOOP
740 --
741 FETCH get_entry_chgs
742 INTO v_earnings_entry,
743 v_entry_start,
744 v_entry_end;
745 EXIT WHEN get_entry_chgs%NOTFOUND;
746 --
747 hr_utility.trace('v_earnings_entry ='||v_earnings_entry);
748 hr_utility.trace('v_entry_start ='||to_char(v_entry_start));
749 hr_utility.trace('v_entry_end ='||to_char(v_entry_end));
750 hr_utility.set_location('Prorate_EEV', 115);
751 --
752 -- For each range of dates found, add to running prorated earnings total.
753 --
754 hr_utility.trace('Calling Convert_Period_Type ');
755
756 v_curr_hrly_rate := Convert_Period_Type( p_bus_group_id,
757 p_pay_id,
758 p_work_sched,
759 p_asg_std_hrs,
760 v_earnings_entry,
761 p_pay_basis,
762 'HOURLY',
763 p_period_start,
764 p_period_end,
765 p_asg_std_freq);
766
767
768 hr_utility.trace('v_curr_hrly_rate ='||to_char(v_curr_hrly_rate));
769 hr_utility.set_location('Prorate_EEV', 119);
770 v_eev_prorated_earnings := v_eev_prorated_earnings +
771 Prorate_Earnings (
772 p_bg_id => p_bus_group_id,
773 p_asg_hrly_rate => v_curr_hrly_rate,
774 p_wsched => p_work_sched,
775 p_asg_std_hours => p_asg_std_hrs,
776 p_asg_std_freq => p_asg_std_freq,
777 p_range_start_date => v_entry_start,
778 p_range_end_date => v_entry_end,
779 p_act_hrs_worked => p_actual_hrs_worked);
780
781 hr_utility.trace('v_eev_prorated_earnings ='||to_char(v_eev_prorated_earnings));
782
783 END LOOP;
784 --
785 CLOSE get_entry_chgs;
786 --
787 -- SELECT (EEV3)
788 -- Select for SINGLE record that exists across Period End Date:
789 -- NOTE: Will only return a row if select (2) does not return a row where
790 -- Effective End Date = Period End Date !
791
792 hr_utility.trace('Select EEV3');
793 hr_utility.set_location('Prorate_EEV', 141);
794 SELECT EEV.screen_entry_value,
795 EEV.effective_start_date,
796 LEAST(EEV.effective_end_date, p_range_end_date)
797 INTO v_earnings_entry,
798 v_entry_start,
799 v_entry_end
800 FROM pay_element_entry_values_f EEV
801 WHERE EEV.element_entry_id = p_element_entry_id
802 AND EEV.input_value_id = p_inpval_id
803 AND EEV.effective_start_date > p_range_start_date
804 AND EEV.effective_start_date <= p_range_end_date
805 AND EEV.effective_end_date > p_range_end_date;
806 hr_utility.set_location('Prorate_EEV', 147);
807 hr_utility.trace('screen_entry_value ='||v_earnings_entry);
808 hr_utility.trace('v_entry_start ='||to_char(v_entry_start));
809 hr_utility.trace('v_entry_end ='||to_char(v_entry_end));
810
811 hr_utility.trace('Calling Convert_Period_Type ');
812
813 v_curr_hrly_rate := Convert_Period_Type( p_bus_group_id,
814 p_pay_id,
815 p_work_sched,
816 p_asg_std_hrs,
817 v_earnings_entry,
818 p_pay_basis,
819 'HOURLY',
820 p_period_start,
821 p_period_end,
822 p_asg_std_freq);
823 hr_utility.set_location('Prorate_EEV', 151);
824 hr_utility.trace('After Call v_curr_hrly_rate ='||to_char(v_curr_hrly_rate));
825
826 v_eev_prorated_earnings := v_eev_prorated_earnings +
827 Prorate_Earnings (
828 p_bg_id => p_bus_group_id,
829 p_asg_hrly_rate => v_curr_hrly_rate,
830 p_wsched => p_work_sched,
831 p_asg_std_hours => p_asg_std_hrs,
832 p_asg_std_freq => p_asg_std_freq,
833 p_range_start_date => v_entry_start,
834 p_range_end_date => v_entry_end,
835 p_act_hrs_worked => p_actual_hrs_worked);
836
837 -- We're Done!
838 hr_utility.trace('v_eev_prorated_earnings ='||
839 to_char(v_eev_prorated_earnings));
840 hr_utility.set_location('Prorate_EEV', 167);
841 p_actual_hrs_worked := ROUND(p_actual_hrs_worked, 3);
842 p_hrly_rate := v_curr_hrly_rate;
843
844 hr_utility.trace('p_actual_hrs_worked ='||to_char(p_actual_hrs_worked));
845 hr_utility.trace('p_hrly_rate ='||to_char(p_hrly_rate));
846
847 hr_utility.trace('UDFS Leaving Prorated EEV');
848
849 RETURN v_eev_prorated_earnings;
850
851 EXCEPTION WHEN NO_DATA_FOUND THEN
852 hr_utility.set_location('Prorate_EEV', 177);
853 hr_utility.trace('Into exception of Prorate_EEV');
854
855 p_actual_hrs_worked := ROUND(p_actual_hrs_worked, 3);
856 p_hrly_rate := v_curr_hrly_rate;
857
858 hr_utility.trace('p_actual_hrs_worked ='||to_char(p_actual_hrs_worked));
859 hr_utility.trace('p_hrly_rate ='||to_char(p_hrly_rate));
860
861 RETURN v_eev_prorated_earnings;
862
863 END Prorate_EEV;
864
865 FUNCTION vacation_pay ( p_vac_hours IN OUT nocopy NUMBER,
866 p_asg_id IN NUMBER,
867 p_eff_date IN DATE,
868 p_curr_rate IN NUMBER) RETURN NUMBER IS
869
870 l_vac_pay NUMBER(27,7) ;
871 l_vac_hours NUMBER(10,7);
872
873 CURSOR get_vac_hours ( v_asg_id NUMBER,
874 v_eff_date DATE) IS
875 select fnd_number.canonical_to_number(pev.screen_entry_value)
876 from per_absence_attendance_types abt,
877 pay_element_entries_f pee,
878 pay_element_entry_values_f pev
879 where pev.input_value_id = abt.input_value_id
880 and abt.absence_category = 'V'
881 and v_eff_date between pev.effective_start_date
882 and pev.effective_end_date
883 and pee.element_entry_id = pev.element_entry_id
884 and pee.assignment_id = v_asg_id
885 and v_eff_date between pee.effective_start_date
886 and pee.effective_end_date;
887
888 -- The "vacation_pay" fn looks for hours entered against absence types
889 -- in the current period. The number of hours are summed and multiplied by
890 -- the current rate of Regular Pay..
891 -- Return immediately when no vacation time has been taken.
892 -- Need to loop thru all "Vacation Plans" and check for entries in the current
893 -- period for this assignment.
894
895 BEGIN
896
897 /* Init */
898 l_vac_pay := 0;
899
900 hr_utility.set_location('get_vac_pay', 11);
901 hr_utility.trace('Entered Vacation Pay');
902
903 OPEN get_vac_hours (p_asg_id, p_eff_date);
904 LOOP
905
906 hr_utility.set_location('get_vac_pay', 13);
907 hr_utility.trace('Opened get_vac_hours');
908
909 FETCH get_vac_hours
910 INTO l_vac_hours;
911 EXIT WHEN get_vac_hours%NOTFOUND;
912
913 p_vac_hours := p_vac_hours + l_vac_hours;
914
915 END LOOP;
916 CLOSE get_vac_hours;
917
918 hr_utility.set_location('get_vac_pay', 15);
919
920 IF p_vac_hours <> 0 THEN
921
922 l_vac_pay := p_vac_hours * p_curr_rate;
923
924 END IF;
925
926 hr_utility.trace('Leaving Vacation Pay');
927 RETURN l_vac_pay;
928
929 END vacation_pay;
930
931 FUNCTION sick_pay ( p_sick_hours IN OUT nocopy NUMBER,
932 p_asg_id IN NUMBER,
933 p_eff_date IN DATE,
934 p_curr_rate IN NUMBER) RETURN NUMBER IS
935
936 l_sick_pay NUMBER(27,7) ;
937 l_sick_hours NUMBER(10,7);
938
939 CURSOR get_sick_hours ( v_asg_id NUMBER,
940 v_eff_date DATE) IS
941 select fnd_number.canonical_to_number(pev.screen_entry_value)
942 from per_absence_attendance_types abt,
943 pay_element_entries_f pee,
944 pay_element_entry_values_f pev
945 where pev.input_value_id = abt.input_value_id
946 and abt.absence_category = 'S'
947 and v_eff_date between pev.effective_start_date
948 and pev.effective_end_date
949 and pee.element_entry_id = pev.element_entry_id
950 and pee.assignment_id = v_asg_id
951 and v_eff_date between pee.effective_start_date
952 and pee.effective_end_date;
953
954 -- The "sick_pay" looks for hours entered against Sick absence types in
955 -- the current period. The number of hours are summed and multiplied by the
956 -- current rate of Regular Pay.
957 -- Return immediately when no sick time has been taken.
958
959 BEGIN
960
961 /* Init */
962 l_sick_pay :=0;
963
964 hr_utility.set_location('get_sick_pay', 11);
965 hr_utility.trace('Entered Sick Pay');
966
967 OPEN get_sick_hours (p_asg_id, p_eff_date);
968 LOOP
969
970 hr_utility.trace('get_sick_pay');
971 hr_utility.set_location('get_sick_pay', 13);
972
973 FETCH get_sick_hours
974 INTO l_sick_hours;
975 EXIT WHEN get_sick_hours%NOTFOUND;
976
977 p_sick_hours := p_sick_hours + l_sick_hours;
978
979 END LOOP;
980 CLOSE get_sick_hours;
981
982 hr_utility.set_location('get_sick_pay', 15);
983 hr_utility.trace('get_sick_pay');
984
985 IF p_sick_hours <> 0 THEN
986
987 l_sick_pay := p_sick_hours * p_curr_rate;
988
989 END IF;
990
991 hr_utility.trace('Leaving get_sick_pay');
992 RETURN l_sick_pay;
993
994 END sick_pay;
995
996 BEGIN -- Calculate_Period_Earnings
997 --BEGINCALC
998
999 /* Init */
1000 v_period_earn := 0;
1001 v_prorated_earnings := 0;
1002 v_curr_hrly_rate := 0;
1003 l_mid_period_asg_change := FALSE;
1004
1005 hr_utility.trace('UDFS Entered Calculate_Period_Earnings');
1006 hr_utility.trace('p_asst_id ='||to_char(p_asst_id));
1007 hr_utility.trace('p_payroll_id ='||to_char(p_payroll_id));
1008 hr_utility.trace('p_ele_entry_id ='||to_char(p_ele_entry_id));
1009 hr_utility.trace('p_tax_unit_id ='||to_char(p_tax_unit_id));
1010 hr_utility.trace('p_date_earned ='||to_char(p_date_earned));
1011 hr_utility.trace('p_pay_basis ='||p_pay_basis);
1012 hr_utility.trace('p_inpval_name ='||p_inpval_name);
1013 hr_utility.trace('p_ass_hrly_figure ='||to_char(p_ass_hrly_figure));
1014 hr_utility.trace('UDFS p_period_start ='||to_char(p_period_start));
1015 hr_utility.trace('UDFS p_period_end ='||to_char(p_period_end));
1016 hr_utility.trace('p_work_schedule ='||p_work_schedule);
1017 hr_utility.trace('p_asst_std_hrs ='||to_char(p_asst_std_hrs));
1018 hr_utility.trace('p_actual_hours_worked ='||to_char(p_actual_hours_worked));
1019 hr_utility.trace('p_vac_hours_worked ='||to_char(p_vac_hours_worked));
1020 hr_utility.trace('p_vac_pay ='||to_char(p_vac_pay));
1021 hr_utility.trace('p_sick_hours_worked ='||to_char(p_sick_hours_worked));
1022 hr_utility.trace('p_sick_pay ='||to_char(p_sick_pay));
1023 hr_utility.trace('UDFS p_prorate ='||p_prorate);
1024 hr_utility.trace('p_asst_std_freq ='||p_asst_std_freq);
1025
1026 hr_utility.trace('Find earnings element input value id');
1027
1028 p_actual_hours_worked := 0;
1029
1030 -- Step (1): Find earnings element input value.
1031 -- Get input value and pay basis according to salary admin (if exists).
1032 -- If not using salary admin, then get "Rate", "Rate Code", or "Monthly Salary"
1033 -- input value id as appropriate (according to ele name).
1034
1035 IF p_pay_basis IS NOT NULL THEN
1036
1037 BEGIN
1038
1039 hr_utility.trace(' p_pay_basis IS NOT NULL');
1040 hr_utility.set_location('calculate_period_earnings', 10);
1041
1042 SELECT PYB.input_value_id,
1043 FCL.meaning
1044 INTO v_inpval_id,
1045 v_pay_basis
1046 FROM per_assignments_f ASG,
1047 per_pay_bases PYB,
1048 hr_lookups FCL
1049 WHERE FCL.lookup_code = PYB.pay_basis
1050 AND FCL.lookup_type = 'PAY_BASIS'
1051 AND FCL.application_id = 800
1052 AND PYB.pay_basis_id = ASG.pay_basis_id
1053 AND ASG.assignment_id = p_asst_id
1054 AND p_date_earned BETWEEN ASG.effective_start_date
1055 AND ASG.effective_end_date;
1056
1057 EXCEPTION WHEN NO_DATA_FOUND THEN
1058 hr_utility.set_location('calculate_period_earnings', 11);
1059 hr_utility.trace(' In EXCEPTION p_pay_basis IS NOT NULL');
1060
1061 v_period_earn := 0;
1062 p_actual_hours_worked := ROUND(p_actual_hours_worked, 3);
1063
1064 hr_utility.trace('p_actual_hours_worked ='||to_char(p_actual_hours_worked));
1065
1066 RETURN v_period_earn;
1067
1068
1069 END;
1070
1071 hr_utility.trace('p_inpval_name = '||p_inpval_name);
1072
1073 ELSIF UPPER(p_inpval_name) = 'RATE' THEN
1074
1075 hr_utility.trace(' p_pay_basis IS NULL');
1076 hr_utility.trace('In p_inpval_name = RATE');
1077 /* Changed the element_name and name to init case and added
1078 the date join for pay_element_types_f */
1079
1080 begin
1081 SELECT IPV.input_value_id
1082 INTO v_inpval_id
1083 FROM pay_input_values_f IPV,
1084 pay_element_types_f ELT
1085 WHERE ELT.element_name = 'Regular Wages'
1086 and p_period_start BETWEEN ELT.effective_start_date
1087 AND ELT.effective_end_date
1088 and ELT.element_type_id = IPV.element_type_id
1089 and p_period_start BETWEEN IPV.effective_start_date
1090 AND IPV.effective_end_date
1091 and IPV.name = 'Rate'
1092 and ELT.legislation_code = 'US';
1093 --
1094 v_pay_basis := 'HOURLY';
1095 --
1096 EXCEPTION WHEN NO_DATA_FOUND THEN
1097
1098 hr_utility.trace('Exception of RATE ');
1099
1100 v_period_earn := 0;
1101 p_actual_hours_worked := ROUND(p_actual_hours_worked, 3);
1102
1103 hr_utility.trace('p_actual_hours_worked ='||to_char(p_actual_hours_worked));
1104
1105 RETURN v_period_earn;
1106 end;
1107 --
1108 ELSIF UPPER(p_inpval_name) = 'RATE CODE' THEN
1109 /* Changed the element_name and name to init case and added
1110 the date join for pay_element_types_f */
1111
1112 begin
1113 hr_utility.trace('In RATE CODE');
1114
1115 SELECT IPV.input_value_id
1116 INTO v_inpval_id
1117 FROM pay_input_values_f IPV,
1118 pay_element_types_f ELT
1119 WHERE ELT.element_name = 'Regular Wages'
1120 and p_period_start BETWEEN ELT.effective_start_date
1121 AND ELT.effective_end_date
1122 and ELT.element_type_id = IPV.element_type_id
1123 and p_period_start BETWEEN IPV.effective_start_date
1124 AND IPV.effective_end_date
1125 and IPV.name = 'Rate Code'
1126 and ELT.legislation_code = 'US';
1127 --
1128 v_pay_basis := 'HOURLY';
1129 --
1130 EXCEPTION WHEN NO_DATA_FOUND THEN
1131 hr_utility.trace('Exception of Rate Code');
1132
1133 v_period_earn := 0;
1134 p_actual_hours_worked := ROUND(p_actual_hours_worked, 3);
1135
1136 hr_utility.trace('p_actual_hours_worked ='||to_char(p_actual_hours_worked));
1137
1138 RETURN v_period_earn;
1139
1140 end;
1141 --
1142 ELSIF UPPER(p_inpval_name) = 'MONTHLY SALARY' THEN
1143
1144 /* Changed the element_name and name to init case and added
1145 the date join for pay_element_types_f */
1146
1147 begin
1148 hr_utility.trace('in MONTHLY SALARY');
1149
1150 SELECT IPV.input_value_id
1151 INTO v_inpval_id
1152 FROM pay_input_values_f IPV,
1153 pay_element_types_f ELT
1154 WHERE ELT.element_name = 'Regular Salary'
1155 and p_period_start BETWEEN ELT.effective_start_date
1156 AND ELT.effective_end_date
1157 and ELT.element_type_id = IPV.element_type_id
1158 and p_period_start BETWEEN IPV.effective_start_date
1159 AND IPV.effective_end_date
1160 and IPV.name = 'Monthly Salary'
1161 and ELT.legislation_code = 'US';
1162
1163 v_pay_basis := 'MONTHLY';
1164
1165 EXCEPTION WHEN NO_DATA_FOUND THEN
1166 hr_utility.set_location('calculate_period_earnings', 18);
1167 v_period_earn := 0;
1168 p_actual_hours_worked := ROUND(p_actual_hours_worked, 3);
1169 hr_utility.trace('p_actual_hours_worked ='||to_char(p_actual_hours_worked));
1170 RETURN v_period_earn;
1171 END;
1172
1173 END IF;
1174
1175 hr_utility.trace('Now know the pay basis for this assignment');
1176 hr_utility.trace('v_inpval_id ='||to_char(v_inpval_id));
1177 hr_utility.trace('v_pay_basis ='||v_pay_basis);
1178 /*
1179 -- Now know the pay basis for this assignment (v_pay_basis).
1180 -- Want to convert entered earnings to pay period earnings.
1181 -- For pay basis of Annual, Monthly, Bi-Weekly, Semi-Monthly,
1182 -- or Period (ie. anything
1183 -- other than Hourly):
1184 -- Annualize entered earnings according to pay basis;
1185 -- then divide by number of payroll periods per fiscal
1186 -- yr for pay period earnings.
1187 -- 02 Dec 1993:
1188 -- Actually, passing in an "Hourly" figure from formula alleviates
1189 -- having to convert in here --> we have Convert_Period_Type fn
1190 -- available to formulae, so a Monthly Salary can be converted before
1191 -- calling this fn. Then we just find the hours scheduled for current period as
1192 -- per the Hourly pay basis algorithm below.
1193 --
1194 -- For Hourly pay basis:
1195 -- Get hours scheduled for the current period either from:
1196 -- 1. ASG work schedule
1197 -- 2. ORG default work schedule
1198 -- 3. ASG standard hours and frequency
1199 -- Multiply the hours scheduled for period by normal Hourly Rate (ie. from
1200 -- pre-defined earnings, REGULAR_WAGES_RATE) pay period earnings.
1201 --
1202 -- In either case, need to find the payroll period type, let's do it upfront:
1203 -- Assignment.payroll_id --> Payroll.period_type
1204 -- --> Per_time_period_types.number_per_fiscal_year.
1205 -- Actually, the number per fiscal year could be found in more than one way:
1206 -- Could also go to per_time_period_rules, but would mean decoding the
1207 -- payroll period type to an appropriate proc_period_type code.
1208 --
1209 */
1210
1211 -- Find # of payroll period types per fiscal year:
1212
1213 begin
1214
1215 hr_utility.trace('Find # of payroll period types per fiscal year');
1216 hr_utility.set_location('calculate_period_earnings', 40);
1217
1218 SELECT TPT.number_per_fiscal_year
1219 INTO v_pay_periods_per_year
1220 FROM pay_payrolls_f PRL,
1221 per_time_period_types TPT
1222 WHERE TPT.period_type = PRL.period_type
1223 AND p_period_end between PRL.effective_start_date
1224 and PRL.effective_end_date
1225 AND PRL.payroll_id = p_payroll_id
1226 AND PRL.business_group_id + 0 = p_bus_grp_id;
1227
1228 hr_utility.trace('v_pay_periods_per_year ='||to_char(v_pay_periods_per_year));
1229
1230 exception when NO_DATA_FOUND then
1231
1232 hr_utility.set_location('calculate_period_earnings', 41);
1233 hr_utility.trace('Exception Find # of payroll period');
1234 v_period_earn := 0;
1235 p_actual_hours_worked := ROUND(p_actual_hours_worked, 3);
1236 hr_utility.trace('p_actual_hours_worked ='||to_char(p_actual_hours_worked));
1237
1238 RETURN v_period_earn;
1239
1240 end;
1241
1242 /*
1243 -- Pay basis is hourly,
1244 -- Get hours scheduled for the current period either from:
1245 -- 1. ASG work schedule
1246 -- 2. ORG default work schedule
1247 -- 3. ASG standard hours and frequency
1248 -- Do we pass in Work Schedule from asst scl db item? Yes
1249 -- 10-JAN-1996 hparicha : We no longer assume "standard hours" represent
1250 -- a weekly figure. We also no longer use a week as
1251 -- the basis for annualization,
1252 -- even when using work schedule - ie. need to find ACTUAL
1253 -- scheduled hours, not
1254 -- actual hours for a week, converted to a period figure.
1255 */
1256 --
1257 hr_utility.set_location('calculate_period_earnings', 45);
1258 hr_utility.trace('Get hours scheduled for the current period');
1259
1260 IF p_work_schedule <> 'NOT ENTERED' THEN
1261 --
1262 -- Find hours worked between period start and end dates.
1263 --
1264 hr_utility.trace('Asg has Work Schedule');
1265 hr_utility.trace('p_work_schedule ='||p_work_schedule);
1266
1267 v_ws_id := fnd_number.canonical_to_number(p_work_schedule);
1268 hr_utility.trace('v_ws_id ='||to_char(v_ws_id));
1269 --
1270 SELECT user_column_name
1271 INTO v_work_sched_name
1272 FROM pay_user_columns
1273 WHERE user_column_id = v_ws_id
1274 AND NVL(business_group_id, p_bus_grp_id) = p_bus_grp_id
1275 AND NVL(legislation_code,'US') = 'US';
1276
1277 hr_utility.trace('v_work_sched_name ='||v_work_sched_name);
1278 hr_utility.trace('Calling Work_Schedule_Total_Hours');
1279
1280 v_hrs_per_range := Work_Schedule_Total_Hours( p_bus_grp_id,
1281 v_work_sched_name,
1282 p_period_start,
1283 p_period_end);
1284 hr_utility.trace('v_hrs_per_range ='||to_char(v_hrs_per_range));
1285 ELSE
1286
1287 hr_utility.trace('Asg has No Work Schedule');
1288 hr_utility.trace('Calling Standard_Hours_Worked');
1289
1290 v_hrs_per_range := Standard_Hours_Worked( p_asst_std_hrs,
1291 p_period_start,
1292 p_period_end,
1293 p_asst_std_freq);
1294 hr_utility.trace('v_hrs_per_range ='||to_char(v_hrs_per_range));
1295
1296 END IF;
1297
1298
1299 hr_utility.trace('Compute earnings and actual hours');
1300 hr_utility.trace('calling convert_period_type from calculate_period_earnings');
1301 hr_utility.set_location('calculate_period_earnings', 46);
1302
1303 v_period_earn := Convert_Period_Type( p_bus_grp_id,
1304 p_payroll_id,
1305 p_work_schedule,
1306 p_asst_std_hrs,
1307 p_ass_hrly_figure,
1308 'HOURLY',
1309 NULL,
1310 p_period_start,
1311 p_period_end,
1312 p_asst_std_freq);
1313
1314 hr_utility.trace('v_period_earn ='||to_char(v_period_earn));
1315 hr_utility.set_location('calculate_period_earnings', 47);
1316
1317 p_actual_hours_worked := v_hrs_per_range;
1318
1319 hr_utility.trace('p_actual_hours_worked ='||to_char(p_actual_hours_worked));
1320
1321 IF p_prorate = 'N' THEN
1322
1323 hr_utility.trace('No proration');
1324 hr_utility.trace('Calling p_vac_pay');
1325 hr_utility.set_location('calculate_period_earnings', 49);
1326
1327 p_vac_pay := vacation_pay( p_vac_hours => p_vac_hours_worked,
1328 p_asg_id => p_asst_id,
1329 p_eff_date => p_period_end,
1330 p_curr_rate => p_ass_hrly_figure);
1331
1332 hr_utility.trace('p_vac_pay ='||to_char(p_vac_pay));
1333
1334 hr_utility.trace('Calling sick Pay');
1335 p_sick_pay := sick_pay( p_sick_hours => p_sick_hours_worked,
1336 p_asg_id => p_asst_id,
1337 p_eff_date => p_period_end,
1338 p_curr_rate => p_ass_hrly_figure);
1339
1340
1341 hr_utility.trace('p_sick_pay ='||to_char(p_sick_pay));
1342
1343 p_actual_hours_worked := ROUND(p_actual_hours_worked, 3);
1344
1345 hr_utility.trace('p_actual_hours_worked ='||to_char(p_actual_hours_worked));
1346 hr_utility.trace('UDFS v_period_earn ='||to_char(v_period_earn));
1347
1348 RETURN v_period_earn;
1349
1350 END IF; /* IF p_prorate = 'N' */
1351
1352
1353 hr_utility.trace('UDFS check for ASGMPE changes');
1354 hr_utility.set_location('calculate_period_earnings', 51);
1355 /* ************************************************************** */
1356
1357 BEGIN /* Check ASGMPE */
1358
1359 select 1 INTO l_asg_info_changes
1360 from dual
1361 where exists (
1362 SELECT 1
1363 FROM per_assignments_f ASG,
1364 per_assignment_status_types AST,
1365 hr_soft_coding_keyflex SCL
1366 WHERE ASG.assignment_id = p_asst_id
1367 AND ASG.effective_start_date <= p_period_start
1368 AND ASG.effective_end_date >= p_period_start
1369 AND ASG.effective_end_date < p_period_end
1370 AND AST.assignment_status_type_id = ASG.assignment_status_type_id
1371 AND AST.per_system_status = 'ACTIVE_ASSIGN'
1372 AND SCL.soft_coding_keyflex_id = ASG.soft_coding_keyflex_id
1373 AND SCL.segment1 = TO_CHAR(p_tax_unit_id)
1374 AND SCL.enabled_flag = 'Y' );
1375
1376 hr_utility.trace('ASGMPE Changes found');
1377 hr_utility.trace('Need to prorate b/c of ASGMPE');
1378 hr_utility.trace('Set l_mid_period_asg_change to TRUE I');
1379
1380 l_mid_period_asg_change := TRUE;
1381
1382 hr_utility.set_location('calculate_period_earnings', 56);
1383 hr_utility.trace('Look for EEVMPE changes');
1384
1385 BEGIN /* EEVMPE check - maybe pick*/
1386
1387 select 1 INTO l_eev_info_changes
1388 from dual
1389 where exists (
1390 SELECT 1
1391 FROM pay_element_entry_values_f EEV
1392 WHERE EEV.element_entry_id = p_ele_entry_id
1393 AND EEV.input_value_id+0 = v_inpval_id
1394 AND ( ( EEV.effective_start_date <= p_period_start
1395 AND EEV.effective_end_date >= p_period_start
1396 AND EEV.effective_end_date < p_period_end)
1397 OR ( EEV.effective_start_date between p_period_start and p_period_end)
1398 ) );
1399
1400
1401
1402 hr_utility.trace('EEVMPE changes found after ASGMPE');
1403
1404 EXCEPTION
1405
1406 WHEN NO_DATA_FOUND THEN
1407 l_eev_info_changes := 0;
1408
1409 hr_utility.trace('From EXCEPTION ASGMPE changes found No EEVMPE changes');
1410
1411 END; /* EEV1 check*/
1412
1413 EXCEPTION
1414
1415 WHEN NO_DATA_FOUND THEN
1416
1417 l_asg_info_changes := 0;
1418 hr_utility.trace('From EXCEPTION No ASGMPE changes, nor EEVMPE changes');
1419
1420 END; /* ASGMPE check*/
1421
1422 /* ************************************************ */
1423
1424 IF l_asg_info_changes = 0 THEN /* Check ASGMPS */
1425
1426 hr_utility.trace(' Into l_asg_info_changes = 0');
1427 hr_utility.trace('UDFS looking for ASGMPS changes');
1428 hr_utility.set_location('calculate_period_earnings', 56);
1429
1430 BEGIN /* ASGMPS changes */
1431
1432 select 1 INTO l_asg_info_changes
1433 from dual
1434 where exists (
1435 SELECT 1
1436 FROM per_assignments_f ASG,
1437 per_assignment_status_types AST,
1438 hr_soft_coding_keyflex SCL
1439 WHERE ASG.assignment_id = p_asst_id
1440 AND ASG.effective_start_date > p_period_start
1441 AND ASG.effective_start_date <= p_period_end
1442 AND AST.assignment_status_type_id = ASG.assignment_status_type_id
1443 AND AST.per_system_status = 'ACTIVE_ASSIGN'
1444 AND SCL.soft_coding_keyflex_id = ASG.soft_coding_keyflex_id
1445 AND SCL.segment1 = TO_CHAR(p_tax_unit_id)
1446 AND SCL.enabled_flag = 'Y');
1447
1448 l_mid_period_asg_change := TRUE;
1449
1450 hr_utility.trace('Need to prorate for ASGMPS changes');
1451 hr_utility.set_location('calculate_period_earnings', 57);
1452
1453 BEGIN /* EEVMPE changes ASGMPS */
1454
1455 select 1 INTO l_eev_info_changes
1456 from dual
1457 where exists (
1458 SELECT 1
1459 FROM pay_element_entry_values_f EEV
1460 WHERE EEV.element_entry_id = p_ele_entry_id
1461 AND EEV.input_value_id+0 = v_inpval_id
1462 AND ( ( EEV.effective_start_date <= p_period_start
1463 AND EEV.effective_end_date >= p_period_start
1464 AND EEV.effective_end_date < p_period_end)
1465 --OR ( EEV.effective_start_date between p_period_start and p_period_end)
1466 ) );
1467
1468
1469 hr_utility.trace('Need to prorate EEVMPS changes after ASGMPS ');
1470
1471 EXCEPTION
1472
1473 WHEN NO_DATA_FOUND THEN
1474
1475 l_eev_info_changes := 0;
1476
1477 hr_utility.trace('From EXCEPTIION No EEVMPE changes');
1478
1479 END; /* EEVMPE changes */
1480
1481 EXCEPTION
1482
1483 WHEN NO_DATA_FOUND THEN
1484
1485 l_asg_info_changes := 0;
1486
1487 hr_utility.trace('From EXCEPTION no changes due to ASGMPS or EEVMPE');
1488
1489 END; /* ASGMPS changes */
1490
1491 END IF; /* Check ASGMPS */
1492
1493 /* *************************************************** */
1494
1495 IF l_asg_info_changes = 0 THEN /* ASGMPE=0 and ASGMPS=0 */
1496
1497 BEGIN /* Check for EEVMPE changes */
1498
1499 hr_utility.set_location('calculate_period_earnings', 58);
1500 hr_utility.trace('Check for EEVMPE changes nevertheless');
1501
1502 select 1 INTO l_eev_info_changes
1503 from dual
1504 where exists (
1505 SELECT 1
1506 FROM pay_element_entry_values_f EEV
1507 WHERE EEV.element_entry_id = p_ele_entry_id
1508 AND EEV.input_value_id+0 = v_inpval_id
1509 AND EEV.effective_start_date <= p_period_start
1510 AND EEV.effective_end_date >= p_period_start
1511 AND EEV.effective_end_date < p_period_end);
1512
1513 hr_utility.trace('Proration due to EEVMPE changes');
1514
1515
1516 EXCEPTION
1517
1518 WHEN NO_DATA_FOUND THEN
1519
1520 hr_utility.trace('ASG AND EEV changes DO NOT EXIST EXCEPT ');
1521
1522 -- Either there are no changes to an Active Assignment OR
1523 -- the assignment was not active at all this period.
1524 -- Check assignment status of current asg record.
1525
1526 hr_utility.trace(' Check assignment status of current asg record');
1527
1528 SELECT AST.per_system_status
1529 INTO v_asg_status
1530 FROM per_assignments_f ASG,
1531 per_assignment_status_types AST,
1532 hr_soft_coding_keyflex SCL
1533 WHERE ASG.assignment_id = p_asst_id
1534 AND p_period_start BETWEEN ASG.effective_start_date
1535 AND ASG.effective_end_date
1536 AND AST.assignment_status_type_id = ASG.assignment_status_type_id
1537 AND SCL.soft_coding_keyflex_id = ASG.soft_coding_keyflex_id
1538 AND SCL.segment1 = TO_CHAR(p_tax_unit_id)
1539 AND SCL.enabled_flag = 'Y';
1540
1541 IF v_asg_status <> 'ACTIVE_ASSIGN' THEN
1542
1543 hr_utility.trace(' Asg not active');
1544 v_period_earn := 0;
1545 p_actual_hours_worked := 0;
1546
1547 END IF;
1548
1549 hr_utility.trace('Chk for vac pay since no ASG EEV changes to prorate' );
1550
1551 p_vac_pay := vacation_pay(p_vac_hours => p_vac_hours_worked,
1552 p_asg_id => p_asst_id,
1553 p_eff_date => p_period_end,
1554 p_curr_rate => p_ass_hrly_figure);
1555
1556 hr_utility.trace('p_vac_pay ='||p_vac_pay);
1557 p_sick_pay := sick_pay( p_sick_hours => p_sick_hours_worked,
1558 p_asg_id => p_asst_id,
1559 p_eff_date => p_period_end,
1560 p_curr_rate => p_ass_hrly_figure);
1561
1562
1563 hr_utility.trace('p_sick_pay ='||p_sick_pay);
1564
1565 p_actual_hours_worked := ROUND(p_actual_hours_worked, 3);
1566 RETURN v_period_earn;
1567
1568 END; /* Check for EEVMPE changes */
1569
1570 END IF; /* ASGMPE=0 ASGMPS =0 */
1571
1572 /* **************************************************************
1573 If code reaches here, then we're prorating for one reason or the other.
1574 ***************************************************************** */
1575
1576
1577 IF (l_asg_info_changes > 0) AND (l_eev_info_changes = 0) THEN /*ASG =1 EEV =0*/
1578
1579
1580 /* ************** ONLY ASG CHANGES START **** */
1581
1582 p_actual_hours_worked := 0;
1583 hr_utility.set_location('calculate_period_earnings', 70);
1584 hr_utility.trace('UDFS ONLY ASG CHANGES START');
1585
1586 BEGIN /* Get Asg Details ASGMPE */
1587
1588 hr_utility.trace('Get Asg details - ASGMPE');
1589 hr_utility.set_location('calculate_period_earnings', 71);
1590
1591 SELECT GREATEST(ASG.effective_start_date, p_period_start),
1592 ASG.effective_end_date,
1593 NVL(ASG.NORMAL_HOURS, 0),
1594 NVL(HRL.meaning, 'NOT ENTERED'),
1595 NVL(SCL.segment4, 'NOT ENTERED')
1596 INTO v_range_start,
1597 v_range_end,
1598 v_asst_std_hrs,
1599 v_asst_std_freq,
1600 v_work_schedule
1601 FROM per_assignments_f ASG,
1602 per_assignment_status_types AST,
1603 hr_soft_coding_keyflex SCL,
1604 hr_lookups HRL
1605 WHERE ASG.assignment_id = p_asst_id
1606 AND ASG.business_group_id + 0 = p_bus_grp_id
1607 AND ASG.effective_start_date <= p_period_start
1608 AND ASG.effective_end_date >= p_period_start
1609 AND ASG.effective_end_date < p_period_end
1610 AND AST.assignment_status_type_id = ASG.assignment_status_type_id
1611 AND AST.per_system_status = 'ACTIVE_ASSIGN'
1612 AND SCL.soft_coding_keyflex_id = ASG.soft_coding_keyflex_id
1613 AND SCL.segment1 = TO_CHAR(p_tax_unit_id)
1614 AND SCL.enabled_flag = 'Y'
1615 AND HRL.lookup_code(+) = ASG.frequency
1616 AND HRL.lookup_type(+) = 'FREQUENCY';
1617
1618
1619 hr_utility.trace('If ASGMPE Details succ. then Calling Prorate_Earnings');
1620 hr_utility.set_location('calculate_period_earnings', 72);
1621 v_prorated_earnings := v_prorated_earnings +
1622 Prorate_Earnings (
1623 p_bg_id => p_bus_grp_id,
1624 p_asg_hrly_rate => p_ass_hrly_figure,
1625 p_wsched => v_work_schedule,
1626 p_asg_std_hours => v_asst_std_hrs,
1627 p_asg_std_freq => v_asst_std_freq,
1628 p_range_start_date => v_range_start,
1629 p_range_end_date => v_range_end,
1630 p_act_hrs_worked => p_actual_hours_worked);
1631
1632 hr_utility.trace('After Calling Prorate_Earnings');
1633
1634 EXCEPTION WHEN NO_DATA_FOUND THEN
1635
1636 NULL;
1637
1638 END; /* Get Asg Details */
1639
1640
1641 hr_utility.trace('ONLY ASG , select MULTIASG');
1642 hr_utility.set_location('calculate_period_earnings', 77);
1643
1644 OPEN get_asst_chgs; -- SELECT (ASG2 MULTIASG)
1645 LOOP
1646
1647 FETCH get_asst_chgs
1648 INTO v_range_start,
1649 v_range_end,
1650 v_asst_std_hrs,
1651 v_asst_std_freq,
1652 v_work_schedule;
1653 EXIT WHEN get_asst_chgs%NOTFOUND;
1654 hr_utility.set_location('calculate_period_earnings', 79);
1655
1656
1657 hr_utility.trace('ONLY ASG Calling Prorate_Earning as MULTIASG successful');
1658
1659 v_prorated_earnings := v_prorated_earnings +
1660 Prorate_Earnings (
1661 p_bg_id => p_bus_grp_id,
1662 p_asg_hrly_rate => p_ass_hrly_figure,
1663 p_wsched => v_work_schedule,
1664 p_asg_std_hours => v_asst_std_hrs,
1665 p_asg_std_freq => v_asst_std_freq,
1666 p_range_start_date => v_range_start,
1667 p_range_end_date => v_range_end,
1668 p_act_hrs_worked => p_actual_hours_worked);
1669
1670
1671 hr_utility.trace('After calling Prorate_Earnings from MULTIASG');
1672
1673 END LOOP;
1674
1675 CLOSE get_asst_chgs;
1676
1677 BEGIN /* END_SPAN_RECORD */
1678
1679 hr_utility.set_location('calculate_period_earnings', 89);
1680 hr_utility.trace('ONLY ASG , select END_SPAN_RECORD');
1681
1682 SELECT ASG.effective_start_date,
1683 LEAST(ASG.effective_end_date, p_period_end),
1684 NVL(ASG.normal_hours, 0),
1685 NVL(HRL.meaning, 'NOT ENTERED'),
1686 NVL(SCL.segment4, 'NOT ENTERED')
1687 INTO v_range_start,
1688 v_range_end,
1689 v_asst_std_hrs,
1690 v_asst_std_freq,
1691 v_work_schedule
1692 FROM hr_soft_coding_keyflex SCL,
1693 per_assignment_status_types AST,
1694 per_assignments_f ASG,
1695 hr_lookups HRL
1696 WHERE ASG.assignment_id = p_asst_id
1697 AND ASG.business_group_id + 0 = p_bus_grp_id
1698 AND ASG.effective_start_date > p_period_start
1699 AND ASG.effective_start_date <= p_period_end
1700 AND ASG.effective_end_date > p_period_end
1701 AND AST.assignment_status_type_id = ASG.assignment_status_type_id
1702 AND AST.per_system_status = 'ACTIVE_ASSIGN'
1703 AND SCL.soft_coding_keyflex_id = ASG.soft_coding_keyflex_id
1704 AND SCL.segment1 = TO_CHAR(p_tax_unit_id)
1705 AND SCL.enabled_flag = 'Y'
1706 AND HRL.lookup_code(+) = ASG.frequency
1707 AND HRL.lookup_type(+) = 'FREQUENCY';
1708
1709 hr_utility.trace('Calling Prorate_Earnings for ONLY ASG END_SPAN_RECORD');
1710 hr_utility.set_location('calculate_period_earnings', 91);
1711 v_prorated_earnings := v_prorated_earnings +
1712 Prorate_Earnings (
1713 p_bg_id => p_bus_grp_id,
1714 p_asg_hrly_rate => p_ass_hrly_figure,
1715 p_wsched => v_work_schedule,
1716 p_asg_std_hours => v_asst_std_hrs,
1717 p_asg_std_freq => v_asst_std_freq,
1718 p_range_start_date => v_range_start,
1719 p_range_end_date => v_range_end,
1720 p_act_hrs_worked => p_actual_hours_worked);
1721
1722
1723 hr_utility.trace('Calling Vacation Pay as END_SPAN succ');
1724 hr_utility.set_location('calculate_period_earnings', 101);
1725
1726 p_vac_pay := vacation_pay( p_vac_hours => p_vac_hours_worked,
1727 p_asg_id => p_asst_id,
1728 p_eff_date => p_period_end,
1729 p_curr_rate => p_ass_hrly_figure);
1730
1731 hr_utility.trace('Calling Sick Pay as ASG3 succ');
1732
1733 p_sick_pay := sick_pay( p_sick_hours => p_sick_hours_worked,
1734 p_asg_id => p_asst_id,
1735 p_eff_date => p_period_end,
1736 p_curr_rate => p_ass_hrly_figure);
1737
1738
1739 p_actual_hours_worked := ROUND(p_actual_hours_worked, 3);
1740 RETURN v_prorated_earnings;
1741
1742 EXCEPTION WHEN NO_DATA_FOUND THEN
1743 hr_utility.set_location('calculate_period_earnings', 102);
1744 hr_utility.trace('Exception of ASG_MID_START_LAST_SPAN_END_DT');
1745
1746 p_vac_pay := vacation_pay( p_vac_hours => p_vac_hours_worked,
1747 p_asg_id => p_asst_id,
1748 p_eff_date => p_period_end,
1749 p_curr_rate => p_ass_hrly_figure);
1750
1751 hr_utility.trace('Calling Sick Pay as ASG3 not succ');
1752 p_sick_pay := sick_pay( p_sick_hours => p_sick_hours_worked,
1753 p_asg_id => p_asst_id,
1754 p_eff_date => p_period_end,
1755 p_curr_rate => p_ass_hrly_figure);
1756
1757
1758 p_actual_hours_worked := ROUND(p_actual_hours_worked, 3);
1759 RETURN v_prorated_earnings;
1760
1761 END; /* ASG_MID_START_LAST_SPAN_END_DT */
1762
1763 /* ************** ONLY ASG CHANGES END **** */
1764
1765
1766 ELSIF (l_asg_info_changes = 0) AND (l_eev_info_changes > 0) THEN
1767
1768 /* ******************* ONLY EEV CHANGES START ****** */
1769
1770 hr_utility.trace(' Only EEV changes exist');
1771 hr_utility.set_location('calculate_period_earnings', 103);
1772 p_actual_hours_worked := 0;
1773
1774
1775 hr_utility.trace('Calling Prorate_EEV');
1776
1777 v_prorated_earnings := v_prorated_earnings +
1778 Prorate_EEV (
1779 p_bus_group_id => p_bus_grp_id,
1780 p_pay_id => p_payroll_id,
1781 p_work_sched => p_work_schedule,
1782 p_asg_std_hrs => p_asst_std_hrs,
1783 p_asg_std_freq => p_asst_std_freq,
1784 p_pay_basis => p_pay_basis,
1785 p_hrly_rate => v_curr_hrly_rate,
1786 p_range_start_date => p_period_start,
1787 p_range_end_date => p_period_end,
1788 p_actual_hrs_worked => p_actual_hours_worked,
1789 p_element_entry_id => p_ele_entry_id,
1790 p_inpval_id => v_inpval_id);
1791
1792 hr_utility.trace('After Calling Prorate_EEV');
1793 hr_utility.set_location('calculate_period_earnings', 127);
1794
1795 hr_utility.trace('Calling vacation_pay');
1796
1797 p_vac_pay := vacation_pay( p_vac_hours => p_vac_hours_worked,
1798 p_asg_id => p_asst_id,
1799 p_eff_date => p_period_end,
1800 p_curr_rate => p_ass_hrly_figure);
1801
1802 hr_utility.trace('Calling sick_pay');
1803
1804 p_sick_pay := sick_pay( p_sick_hours => p_sick_hours_worked,
1805 p_asg_id => p_asst_id,
1806 p_eff_date => p_period_end,
1807 p_curr_rate => p_ass_hrly_figure);
1808
1809
1810 p_actual_hours_worked := ROUND(p_actual_hours_worked, 3);
1811 RETURN v_prorated_earnings;
1812
1813 /* ******************* ONLY EEV CHANGES END ****** */
1814
1815 ELSE /*BOTH ASG AND EEV CHANGES =0*/
1816
1817 /* ******************* BOTH ASG AND EEV CHANGES START ************ */
1818
1819
1820 hr_utility.trace('UDFS BOTH ASG and EEV chages exist');
1821
1822
1823 p_actual_hours_worked := 0;
1824
1825
1826 BEGIN /* Latest Screen Entry Value */
1827
1828 hr_utility.trace('BOTH ASG Get latest screen entry value for EEVMPE');
1829 hr_utility.set_location('calculate_period_earnings', 128);
1830
1831 SELECT fnd_number.canonical_to_number(EEV.screen_entry_value)
1832 INTO v_earnings_entry
1833 FROM pay_element_entry_values_f EEV
1834 WHERE EEV.element_entry_id = p_ele_entry_id
1835 AND EEV.input_value_id = v_inpval_id
1836 AND p_period_start between EEV.effective_start_date
1837 AND EEV.effective_end_date;
1838 /*4750302
1839 AND EEV.effective_start_date <= p_period_start
1840 AND EEV.effective_end_date > p_period_start;
1841 */
1842 --AND EEV.effective_end_date < p_period_end
1843
1844 hr_utility.trace('BOTH ASG Get ASGMPE ');
1845
1846 SELECT GREATEST(ASG.effective_start_date, p_period_start),
1847 ASG.effective_end_date,
1848 NVL(ASG.NORMAL_HOURS, 0),
1849 NVL(HRL.meaning, 'NOT ENTERED'),
1850 NVL(SCL.segment4, 'NOT ENTERED')
1851 INTO v_range_start,
1852 v_range_end,
1853 v_asst_std_hrs,
1854 v_asst_std_freq,
1855 v_work_schedule
1856 FROM per_assignments_f ASG,
1857 per_assignment_status_types AST,
1858 hr_soft_coding_keyflex SCL,
1859 hr_lookups HRL
1860 WHERE ASG.assignment_id = p_asst_id
1861 AND ASG.business_group_id + 0 = p_bus_grp_id
1862 AND ASG.effective_start_date <= p_period_start
1863 AND ASG.effective_end_date >= p_period_start
1864 AND ASG.effective_end_date < p_period_end
1865 AND AST.assignment_status_type_id = ASG.assignment_status_type_id
1866 AND AST.per_system_status = 'ACTIVE_ASSIGN'
1867 AND SCL.soft_coding_keyflex_id = ASG.soft_coding_keyflex_id
1868 AND SCL.segment1 = TO_CHAR(p_tax_unit_id)
1869 AND SCL.enabled_flag = 'Y'
1870 AND HRL.lookup_code(+) = ASG.frequency
1871 AND HRL.lookup_type(+) = 'FREQUENCY';
1872
1873 hr_utility.trace('Calling Convert_Period_Type from ASGMPE');
1874 hr_utility.set_location('v_earnings_entry='||v_earnings_entry, 129);
1875
1876 v_curr_hrly_rate := Convert_Period_Type( p_bus_grp_id,
1877 p_payroll_id,
1878 v_work_schedule,
1879 v_asst_std_hrs,
1880 v_earnings_entry,
1881 v_pay_basis,
1882 'HOURLY',
1883 p_period_start,
1884 p_period_end,
1885 v_asst_std_freq);
1886
1887 hr_utility.trace('Select app. EEVMPE again after range is determined');
1888 hr_utility.set_location('calculate_period_earnings', 130);
1889
1890 SELECT COUNT(EEV.element_entry_value_id)
1891 INTO l_eev_info_changes
1892 FROM pay_element_entry_values_f EEV
1893 WHERE EEV.element_entry_id = p_ele_entry_id
1894 AND EEV.input_value_id = v_inpval_id
1895 AND EEV.effective_start_date <= v_range_start
1896 AND EEV.effective_end_date >= v_range_start
1897 AND EEV.effective_end_date < v_range_end;
1898
1899 IF l_eev_info_changes = 0 THEN
1900
1901
1902 hr_utility.trace('NO EEVMPE changes');
1903 hr_utility.set_location('calculate_period_earnings', 132);
1904
1905 SELECT fnd_number.canonical_to_number(EEV.screen_entry_value)
1906 INTO v_earnings_entry
1907 FROM pay_element_entry_values_f EEV
1908 WHERE EEV.element_entry_id = p_ele_entry_id
1909 AND EEV.input_value_id = v_inpval_id
1910 AND v_range_end BETWEEN EEV.effective_start_date
1911 AND EEV.effective_end_date;
1912
1913 hr_utility.trace('Calling Convert_Period_Type');
1914 hr_utility.set_location('calculate_period_earnings', 134);
1915
1916 v_curr_hrly_rate := Convert_Period_Type( p_bus_grp_id,
1917 p_payroll_id,
1918 v_work_schedule,
1919 v_asst_std_hrs,
1920 v_earnings_entry,
1921 v_pay_basis,
1922 'HOURLY',
1923 p_period_start,
1924 p_period_end,
1925 v_asst_std_freq);
1926
1927 hr_utility.trace('Calling Prorate_Earnings');
1928 hr_utility.set_location('calculate_period_earnings', 135);
1929
1930 v_prorated_earnings := v_prorated_earnings +
1931 Prorate_Earnings (
1932 p_bg_id => p_bus_grp_id,
1933 p_asg_hrly_rate => v_curr_hrly_rate,
1934 p_wsched => v_work_schedule,
1935 p_asg_std_hours => v_asst_std_hrs,
1936 p_asg_std_freq => v_asst_std_freq,
1937 p_range_start_date => v_range_start,
1938 p_range_end_date => v_range_end,
1939 p_act_hrs_worked => p_actual_hours_worked);
1940
1941 hr_utility.set_location('calculate_period_earnings', 137);
1942
1943 ELSE
1944 -- Do proration for this ASG range by EEV !
1945
1946 hr_utility.trace('EEVMPE True');
1947 hr_utility.trace('Do proration for this ASG range by EEV');
1948 hr_utility.set_location('calculate_period_earnings', 139);
1949
1950 hr_utility.trace('Calling Prorate_EEV');
1951
1952 v_prorated_earnings := v_prorated_earnings +
1953 Prorate_EEV (
1954 p_bus_group_id => p_bus_grp_id,
1955 p_pay_id => p_payroll_id,
1956 p_work_sched => v_work_schedule,
1957 p_asg_std_hrs => v_asst_std_hrs,
1958 p_asg_std_freq => v_asst_std_freq,
1959 p_pay_basis => v_pay_basis,
1960 p_hrly_rate => v_curr_hrly_rate,
1961 p_range_start_date => v_range_start,
1962 p_range_end_date => v_range_end,
1963 p_actual_hrs_worked => p_actual_hours_worked,
1964 p_element_entry_id => p_ele_entry_id,
1965 p_inpval_id => v_inpval_id);
1966 hr_utility.set_location('calculate_period_earnings', 140);
1967
1968 END IF; -- EEV info changes
1969
1970 EXCEPTION WHEN NO_DATA_FOUND THEN
1971 NULL;
1972
1973 END; /* Latest Screen Entry Value */
1974
1975 hr_utility.trace(' BOTH ASG - SELECT ASG_MULTI_WITHIN');
1976 hr_utility.set_location('calculate_period_earnings', 141);
1977
1978 OPEN get_asst_chgs; -- SELECT ( ASG_MULTI_WITHIN)
1979 LOOP
1980
1981 FETCH get_asst_chgs
1982 INTO v_range_start,
1983 v_range_end,
1984 v_asst_std_hrs,
1985 v_asst_std_freq,
1986 v_work_schedule;
1987 EXIT WHEN get_asst_chgs%NOTFOUND;
1988
1989 --EEV_BEFORE_RANGE_END
1990 hr_utility.trace('BOTH ASG MULTI select app. EEVMPE again after range det.');
1991 hr_utility.set_location('calculate_period_earnings', 145);
1992
1993 SELECT COUNT(EEV.element_entry_value_id)
1994 INTO l_eev_info_changes
1995 FROM pay_element_entry_values_f EEV
1996 WHERE EEV.element_entry_id = p_ele_entry_id
1997 AND EEV.input_value_id = v_inpval_id
1998 AND EEV.effective_start_date <= v_range_start
1999 AND EEV.effective_end_date >= v_range_start
2000 AND EEV.effective_end_date < v_range_end;
2001
2002 IF l_eev_info_changes = 0 THEN /* IF l_eev_info_changes = 0 */
2003
2004 -- EEV_FOR_CURR_RANGE_END
2005
2006 hr_utility.trace('BOTH ASG - EEV false');
2007 SELECT fnd_number.canonical_to_number(EEV.screen_entry_value)
2008 INTO v_earnings_entry
2009 FROM pay_element_entry_values_f EEV
2010 WHERE EEV.element_entry_id = p_ele_entry_id
2011 AND EEV.input_value_id = v_inpval_id
2012 AND v_range_end BETWEEN EEV.effective_start_date
2013 AND EEV.effective_end_date;
2014 hr_utility.set_location('calculate_period_earnings', 150);
2015 v_curr_hrly_rate := Convert_Period_Type( p_bus_grp_id,
2016 p_payroll_id,
2017 v_work_schedule,
2018 v_asst_std_hrs,
2019 v_earnings_entry,
2020 v_pay_basis,
2021 'HOURLY',
2022 p_period_start,
2023 p_period_end,
2024 v_asst_std_freq);
2025
2026 v_prorated_earnings := v_prorated_earnings +
2027 Prorate_Earnings (
2028 p_bg_id => p_bus_grp_id,
2029 p_asg_hrly_rate => v_curr_hrly_rate,
2030 p_wsched => v_work_schedule,
2031 p_asg_std_hours => v_asst_std_hrs,
2032 p_asg_std_freq => v_asst_std_freq,
2033 p_range_start_date => v_range_start,
2034 p_range_end_date => v_range_end,
2035 p_act_hrs_worked => p_actual_hours_worked);
2036
2037 hr_utility.set_location('calculate_period_earnings', 155);
2038 ELSE
2039 hr_utility.trace('BOTH ASG - EEV true');
2040 v_prorated_earnings := v_prorated_earnings +
2041 Prorate_EEV (
2042 p_bus_group_id => p_bus_grp_id,
2043 p_pay_id => p_payroll_id,
2044 p_work_sched => v_work_schedule,
2045 p_asg_std_hrs => v_asst_std_hrs,
2046 p_asg_std_freq => v_asst_std_freq,
2047 p_pay_basis => v_pay_basis,
2048 p_hrly_rate => v_curr_hrly_rate,
2049 p_range_start_date => v_range_start,
2050 p_range_end_date => v_range_end,
2051 p_actual_hrs_worked => p_actual_hours_worked,
2052 p_element_entry_id => p_ele_entry_id,
2053 p_inpval_id => v_inpval_id);
2054
2055 END IF; /* IF l_eev_info_changes = 0 */
2056
2057 END LOOP;
2058
2059 CLOSE get_asst_chgs;
2060
2061
2062 BEGIN /* SPAN_RECORD */
2063
2064 hr_utility.trace('BOTH ASG SELECT END_SPAN_RECORD');
2065 hr_utility.set_location('calculate_period_earnings', 160);
2066
2067 SELECT ASG.effective_start_date,
2068 LEAST(ASG.effective_end_date, p_period_end),
2069 NVL(ASG.normal_hours, 0),
2070 NVL(HRL.meaning, 'NOT ENTERED'),
2071 NVL(SCL.segment4, 'NOT ENTERED')
2072 INTO v_range_start,
2073 v_range_end,
2074 v_asst_std_hrs,
2075 v_asst_std_freq,
2076 v_work_schedule
2077 FROM hr_soft_coding_keyflex SCL,
2078 per_assignment_status_types AST,
2079 per_assignments_f ASG,
2080 hr_lookups HRL
2081 WHERE ASG.assignment_id = p_asst_id
2082 AND ASG.business_group_id + 0 = p_bus_grp_id
2083 AND ASG.effective_start_date > p_period_start
2084 AND ASG.effective_start_date <= p_period_end
2085 AND ASG.effective_end_date > p_period_end
2086 AND AST.assignment_status_type_id = ASG.assignment_status_type_id
2087 AND AST.per_system_status = 'ACTIVE_ASSIGN'
2088 AND SCL.soft_coding_keyflex_id = ASG.soft_coding_keyflex_id
2089 AND SCL.segment1 = TO_CHAR(p_tax_unit_id)
2090 AND SCL.enabled_flag = 'Y'
2091 AND HRL.lookup_code(+) = ASG.frequency
2092 AND HRL.lookup_type(+) = 'FREQUENCY';
2093
2094
2095
2096 hr_utility.trace('SELECT EEVMPE');
2097
2098 SELECT COUNT(EEV.element_entry_value_id)
2099 INTO l_eev_info_changes
2100 FROM pay_element_entry_values_f EEV
2101 WHERE EEV.element_entry_id = p_ele_entry_id
2102 AND EEV.input_value_id = v_inpval_id
2103 AND EEV.effective_start_date <= v_range_start
2104 AND EEV.effective_end_date >= v_range_start
2105 AND EEV.effective_end_date < v_range_end;
2106
2107 IF l_eev_info_changes = 0 THEN
2108
2109 hr_utility.trace('BOTH ASG SPAN - SELECT EEV_FOR_CURR_RANGE_END');
2110 hr_utility.set_location('calculate_period_earnings', 165);
2111
2112 SELECT fnd_number.canonical_to_number(EEV.screen_entry_value)
2113 INTO v_earnings_entry
2114 FROM pay_element_entry_values_f EEV
2115 WHERE EEV.element_entry_id = p_ele_entry_id
2116 AND EEV.input_value_id = v_inpval_id
2117 AND v_range_end BETWEEN EEV.effective_start_date
2118 AND EEV.effective_end_date;
2119
2120 v_curr_hrly_rate := Convert_Period_Type( p_bus_grp_id,
2121 p_payroll_id,
2122 p_work_schedule,
2123 p_asst_std_hrs,
2124 v_earnings_entry,
2125 v_pay_basis,
2126 'HOURLY',
2127 p_period_start,
2128 p_period_end,
2129 v_asst_std_freq);
2130
2131 v_prorated_earnings := v_prorated_earnings +
2132 Prorate_Earnings (
2133 p_bg_id => p_bus_grp_id,
2134 p_asg_hrly_rate => v_curr_hrly_rate,
2135 p_wsched => v_work_schedule,
2136 p_asg_std_hours => v_asst_std_hrs,
2137 p_asg_std_freq => v_asst_std_freq,
2138 p_range_start_date => v_range_start,
2139 p_range_end_date => v_range_end,
2140 p_act_hrs_worked => p_actual_hours_worked);
2141
2142 hr_utility.set_location('calculate_period_earnings', 170);
2143 ELSE /* EEV succ */
2144
2145 hr_utility.trace('BOTH ASG END_SPAN - EEV true');
2146 v_prorated_earnings := v_prorated_earnings +
2147 Prorate_EEV (
2148 p_bus_group_id => p_bus_grp_id,
2149 p_pay_id => p_payroll_id,
2150 p_work_sched => v_work_schedule,
2151 p_asg_std_hrs => v_asst_std_hrs,
2152 p_asg_std_freq => v_asst_std_freq,
2153 p_pay_basis => v_pay_basis,
2154 p_hrly_rate => v_curr_hrly_rate,
2155 p_range_start_date => v_range_start,
2156 p_range_end_date => v_range_end,
2157 p_actual_hrs_worked => p_actual_hours_worked,
2158 p_element_entry_id => p_ele_entry_id,
2159 p_inpval_id => v_inpval_id);
2160 hr_utility.set_location('calculate_period_earnings', 175);
2161 END IF;
2162
2163
2164 p_vac_pay := vacation_pay( p_vac_hours => p_vac_hours_worked,
2165 p_asg_id => p_asst_id,
2166 p_eff_date => p_period_end,
2167 p_curr_rate => p_ass_hrly_figure);
2168 hr_utility.set_location('calculate_period_earnings', 180);
2169
2170 p_sick_pay := sick_pay( p_sick_hours => p_sick_hours_worked,
2171 p_asg_id => p_asst_id,
2172 p_eff_date => p_period_end,
2173 p_curr_rate => p_ass_hrly_figure);
2174 hr_utility.set_location('calculate_period_earnings', 185);
2175
2176 p_actual_hours_worked := ROUND(p_actual_hours_worked, 3);
2177 RETURN v_prorated_earnings;
2178
2179 EXCEPTION WHEN NO_DATA_FOUND THEN
2180
2181 p_vac_pay := vacation_pay( p_vac_hours => p_vac_hours_worked,
2182 p_asg_id => p_asst_id,
2183 p_eff_date => p_period_end,
2184 p_curr_rate => p_ass_hrly_figure);
2185
2186 p_sick_pay := sick_pay( p_sick_hours => p_sick_hours_worked,
2187 p_asg_id => p_asst_id,
2188 p_eff_date => p_period_end,
2189 p_curr_rate => p_ass_hrly_figure);
2190
2191 p_actual_hours_worked := ROUND(p_actual_hours_worked, 3);
2192 RETURN v_prorated_earnings;
2193
2194 END;
2195
2196
2197 /* ******************* BOTH ASG AND EEV CHANGES ENDS ************ */
2198
2199 END IF; /*END IF OF BOTH ASG AND EEV CHANGES */
2200
2201 EXCEPTION
2202 WHEN NO_DATA_FOUND THEN
2203
2204 p_vac_pay := vacation_pay( p_vac_hours => p_vac_hours_worked,
2205 p_asg_id => p_asst_id,
2206 p_eff_date => p_period_end,
2207 p_curr_rate => p_ass_hrly_figure);
2208
2209 p_sick_pay := sick_pay( p_sick_hours => p_sick_hours_worked,
2210 p_asg_id => p_asst_id,
2211 p_eff_date => p_period_end,
2212 p_curr_rate => p_ass_hrly_figure);
2213
2214
2215 p_actual_hours_worked := ROUND(p_actual_hours_worked, 3);
2216
2217 RETURN v_prorated_earnings;
2218
2219 END Calculate_Period_Earnings;
2220
2221 -- **********************************************************************
2222
2223 FUNCTION standard_hours_worked(
2224 p_std_hrs in NUMBER,
2225 p_range_start in DATE,
2226 p_range_end in DATE,
2227 p_std_freq in VARCHAR2) RETURN NUMBER IS
2228
2229 c_wkdays_per_week NUMBER(5,2) ;
2230 c_wkdays_per_month NUMBER(5,2) ;
2231 c_wkdays_per_year NUMBER(5,2) ;
2232
2233 /* 353434, 368242 : Fixed number width for total hours */
2234 v_total_hours NUMBER(15,7) ;
2235 v_wrkday_hours NUMBER(15,7) ; -- std hrs/wk divided by 5 workdays/wk
2236 v_curr_date DATE;
2237 v_curr_day VARCHAR2(3); -- 3 char abbrev for day of wk.
2238 v_day_no NUMBER;
2239
2240 BEGIN -- standard_hours_worked
2241
2242 /* Init */
2243 c_wkdays_per_week := 5;
2244 c_wkdays_per_month := 20;
2245 c_wkdays_per_year := 250;
2246 v_total_hours := 0;
2247 v_wrkday_hours :=0;
2248 v_curr_date := NULL;
2249 v_curr_day :=NULL;
2250
2251 -- Check for valid range
2252 hr_utility.trace('Entered standard_hours_worked');
2253
2254 IF p_range_start > p_range_end THEN
2255 hr_utility.trace('p_range_start greater than p_range_end');
2256 RETURN v_total_hours;
2257 -- hr_utility.set_message(801,'PAY_xxxx_INVALID_DATE_RANGE');
2258 -- hr_utility.raise_error;
2259 END IF;
2260 --
2261
2262 IF UPPER(p_std_freq) = 'WEEK' THEN
2263 hr_utility.trace('p_std_freq = WEEK ');
2264
2265 v_wrkday_hours := p_std_hrs / c_wkdays_per_week;
2266
2267 hr_utility.trace('p_std_hrs ='||to_number(p_std_hrs));
2268 hr_utility.trace('c_wkdays_per_week ='||to_number(c_wkdays_per_week));
2269 hr_utility.trace('v_wrkday_hours ='||to_number(v_wrkday_hours));
2270
2271 ELSIF UPPER(p_std_freq) = 'MONTH' THEN
2272
2273 hr_utility.trace('p_std_freq = MONTH ');
2274
2275 v_wrkday_hours := p_std_hrs / c_wkdays_per_month;
2276
2277
2278 hr_utility.trace('p_std_hrs ='||to_number(p_std_hrs));
2279 hr_utility.trace('c_wkdays_per_month ='||to_number(c_wkdays_per_month));
2280 hr_utility.trace('v_wrkday_hours ='||to_number(v_wrkday_hours));
2281
2282 ELSIF UPPER(p_std_freq) = 'YEAR' THEN
2283
2284 hr_utility.trace('p_std_freq = YEAR ');
2285 v_wrkday_hours := p_std_hrs / c_wkdays_per_year;
2286
2287 hr_utility.trace('p_std_hrs ='||to_number(p_std_hrs));
2288 hr_utility.trace('c_wkdays_per_year ='||to_number(c_wkdays_per_year));
2289 hr_utility.trace('v_wrkday_hours ='||to_number(v_wrkday_hours));
2290
2291 ELSE
2292 hr_utility.trace('p_std_freq in ELSE ');
2293 v_wrkday_hours := p_std_hrs;
2294 END IF;
2295
2296 v_curr_date := p_range_start;
2297
2298 hr_utility.trace('v_curr_date is range start'||to_char(v_curr_date));
2299
2300
2301 LOOP
2302
2303 v_day_no := TO_CHAR(v_curr_date, 'D');
2304
2305
2306 IF v_day_no > 1 and v_day_no < 7 then
2307
2308
2309 v_total_hours := nvl(v_total_hours,0) + v_wrkday_hours;
2310
2311 hr_utility.trace(' v_day_no = '||to_char(v_day_no));
2312 hr_utility.trace(' v_total_hours = '||to_char(v_total_hours));
2313 END IF;
2314
2315 v_curr_date := v_curr_date + 1;
2316 EXIT WHEN v_curr_date > p_range_end;
2317 END LOOP;
2318 hr_utility.trace(' Final v_total_hours = '||to_char(v_total_hours));
2319 hr_utility.trace(' Leaving standard_hours_worked' );
2320 --
2321 RETURN v_total_hours;
2322 --
2323 END standard_hours_worked;
2324 --
2325 -- **********************************************************************
2326 FUNCTION Convert_Period_Type(
2327 p_bus_grp_id in NUMBER,
2328 p_payroll_id in NUMBER,
2329 p_asst_work_schedule in VARCHAR2,
2330 p_asst_std_hours in NUMBER,
2331 p_figure in NUMBER,
2332 p_from_freq in VARCHAR2,
2333 p_to_freq in VARCHAR2,
2334 p_period_start_date in DATE,
2335 p_period_end_date in DATE,
2336 p_asst_std_freq in VARCHAR2,
2337 p_rate_calc_override in VARCHAR2)
2338 RETURN NUMBER IS
2339
2340 -- local vars
2341 v_calc_type VARCHAR2(50);
2342 v_from_stnd_factor NUMBER(30,7);
2343 v_stnd_start_date DATE;
2344
2345 v_converted_figure NUMBER(27,7);
2346 v_from_annualizing_factor NUMBER(30,7);
2347 v_to_annualizing_factor NUMBER(30,7);
2348
2349 -- local fun
2350
2351 FUNCTION Get_Annualizing_Factor(p_bg in NUMBER,
2352 p_payroll in NUMBER,
2353 p_freq in VARCHAR2,
2354 p_asg_work_sched in VARCHAR2,
2355 p_asg_std_hrs in NUMBER,
2356 p_asg_std_freq in VARCHAR2)
2357 RETURN NUMBER IS
2358
2359 -- local constants
2360
2361 c_weeks_per_year NUMBER(3);
2362 c_days_per_year NUMBER(3);
2363 c_months_per_year NUMBER(3);
2364
2365 -- local vars
2366 /* 353434, 368242 : Fixed number width for total hours variables */
2367 v_annualizing_factor NUMBER(30,7);
2368 v_periods_per_fiscal_yr NUMBER(5);
2369 v_hrs_per_wk NUMBER(15,7);
2370 v_hrs_per_range NUMBER(15,7);
2371 v_use_pay_basis NUMBER(1);
2372 v_pay_basis VARCHAR2(80);
2373 v_range_start DATE;
2374 v_range_end DATE;
2375 v_work_sched_name VARCHAR2(80);
2376 v_ws_id NUMBER(9);
2377 v_period_hours BOOLEAN;
2378
2379 BEGIN -- Get_Annualizing_Factor
2380
2381 /* Init */
2382
2383 c_weeks_per_year := 52;
2384 c_days_per_year := 200;
2385 c_months_per_year := 12;
2386 v_use_pay_basis := 0;
2387 --
2388 -- Check for use of salary admin (ie. pay basis) as frequency.
2389 -- Selecting "count" because we want to continue processing even if
2390 -- the from_freq is not a pay basis.
2391 --
2392
2393 hr_utility.trace(' Entered Get_Annualizing_Factor ');
2394
2395 BEGIN -- Is Freq pay basis?
2396
2397 --
2398 -- Decode pay basis and set v_annualizing_factor accordingly.
2399 -- PAY_BASIS "Meaning" is passed from FF !
2400 --
2401
2402 hr_utility.trace(' Getting lookup code for lookup_type = PAY_BASIS');
2403 hr_utility.trace(' p_freq ='||p_freq);
2404
2405 SELECT lookup_code
2406 INTO v_pay_basis
2407 FROM hr_lookups lkp
2408 WHERE lkp.application_id = 800
2409 AND lkp.lookup_type = 'PAY_BASIS'
2410 AND lkp.meaning = p_freq;
2411
2412 hr_utility.trace(' Lookup_code ie v_pay_basis ='||v_pay_basis);
2413 v_use_pay_basis := 1;
2414
2415 IF v_pay_basis = 'MONTHLY' THEN
2416
2417 hr_utility.trace(' Entered for MONTHLY v_pay_basis');
2418
2419 v_annualizing_factor := 12;
2420
2421 hr_utility.trace(' v_annualizing_factor = 12 ');
2422 ELSIF v_pay_basis = 'HOURLY' THEN
2423
2424 hr_utility.trace(' Entered for HOURLY v_pay_basis');
2425
2426 IF p_period_start_date IS NOT NULL THEN
2427
2428 hr_utility.trace(' p_period_start_date IS NOT NULL v_period_hours=T');
2429 v_range_start := p_period_start_date;
2430 v_range_end := p_period_end_date;
2431 v_period_hours := TRUE;
2432 ELSE
2433
2434 hr_utility.trace(' p_period_start_date IS NULL');
2435
2436 v_range_start := sysdate;
2437 v_range_end := sysdate + 6;
2438 v_period_hours := FALSE;
2439 END IF;
2440
2441 IF UPPER(p_asg_work_sched) <> 'NOT ENTERED' THEN
2442
2443 -- Hourly employee using work schedule.
2444 -- Get work schedule name
2445
2446 hr_utility.trace(' Hourly employee using work schedule');
2447 hr_utility.trace(' Get work schedule name');
2448
2449 v_ws_id := fnd_number.canonical_to_number(p_asg_work_sched);
2450
2451 hr_utility.trace(' v_ws_id ='||to_number(v_ws_id));
2452
2453
2454 SELECT user_column_name
2455 INTO v_work_sched_name
2456 FROM pay_user_columns
2457 WHERE user_column_id = v_ws_id
2458 AND NVL(business_group_id, p_bg) = p_bg
2459 AND NVL(legislation_code,'US') = 'US';
2460
2461 hr_utility.trace(' v_work_sched_name ='||v_work_sched_name);
2462 hr_utility.trace(' Calling Work_Schedule_Total_Hours');
2463
2464 v_hrs_per_range := Work_Schedule_Total_Hours( p_bg,
2465 v_work_sched_name,
2466 v_range_start,
2467 v_range_end);
2468
2469 ELSE-- Hourly emp using Standard Hours on asg.
2470
2471 hr_utility.trace(' Hourly emp using Standard Hours on asg');
2472
2473
2474 hr_utility.trace(' calling Standard_Hours_Worked');
2475 v_hrs_per_range := Standard_Hours_Worked( p_asg_std_hrs,
2476 v_range_start,
2477 v_range_end,
2478 p_asg_std_freq);
2479
2480 END IF;
2481
2482 IF v_period_hours THEN
2483
2484 hr_utility.trace(' v_period_hours is TRUE');
2485
2486 select TPT.number_per_fiscal_year
2487 into v_periods_per_fiscal_yr
2488 from pay_payrolls_f PPF,
2489 per_time_period_types TPT,
2490 fnd_sessions fs
2491 where PPF.payroll_id = p_payroll
2492 and fs.session_id = USERENV('SESSIONID')
2493 and fs.effective_date between PPF.effective_start_date and PPF.effective_end_date
2494 and TPT.period_type = PPF.period_type;
2495
2496 v_annualizing_factor := v_hrs_per_range * v_periods_per_fiscal_yr;
2497
2498 ELSE
2499
2500 v_annualizing_factor := v_hrs_per_range * c_weeks_per_year;
2501
2502 END IF;
2503
2504 ELSIF v_pay_basis = 'PERIOD' THEN
2505
2506 hr_utility.trace(' v_pay_basis = PERIOD');
2507
2508 SELECT TPT.number_per_fiscal_year
2509 INTO v_annualizing_factor
2510 FROM pay_payrolls_f PRL,
2511 per_time_period_types TPT,
2512 fnd_sessions fs
2513 WHERE TPT.period_type = PRL.period_type
2514 and fs.session_id = USERENV('SESSIONID')
2515 and fs.effective_date BETWEEN PRL.effective_start_date
2516 AND PRL.effective_end_date
2517 AND PRL.payroll_id = p_payroll
2518 AND PRL.business_group_id + 0 = p_bg;
2519
2520
2521 ELSIF v_pay_basis = 'ANNUAL' THEN
2522
2523
2524 hr_utility.trace(' v_pay_basis = ANNUAL');
2525 v_annualizing_factor := 1;
2526
2527 ELSE
2528
2529 -- Did not recognize "pay basis", return -999 as annualizing factor.
2530 -- Remember this for debugging when zeroes come out as results!!!
2531
2532 hr_utility.trace(' Did not recognize pay basis');
2533
2534 v_annualizing_factor := 0;
2535 RETURN v_annualizing_factor;
2536
2537 END IF;
2538
2539 EXCEPTION
2540
2541 WHEN NO_DATA_FOUND THEN
2542
2543 hr_utility.trace(' When no data found' );
2544 v_use_pay_basis := 0;
2545
2546 END; /* SELECT LOOKUP CODE */
2547
2548 IF v_use_pay_basis = 0 THEN
2549
2550 hr_utility.trace(' Not using pay basis as frequency');
2551
2552 -- Not using pay basis as frequency...
2553
2554 IF (p_freq IS NULL) OR
2555 (UPPER(p_freq) = 'PERIOD') OR
2556 (UPPER(p_freq) = 'NOT ENTERED') THEN
2557
2558 -- Get "annuallizing factor" from period type of the payroll.
2559
2560 hr_utility.trace('Get annuallizing factor from period type of the payroll');
2561
2562 SELECT TPT.number_per_fiscal_year
2563 INTO v_annualizing_factor
2564 FROM pay_payrolls_f PRL,
2565 per_time_period_types TPT,
2566 fnd_sessions fs
2567 WHERE TPT.period_type = PRL.period_type
2568 and fs.session_id = USERENV('SESSIONID')
2569 and fs.effective_date BETWEEN PRL.effective_start_date
2570 AND PRL.effective_end_date
2571 AND PRL.payroll_id = p_payroll
2572 AND PRL.business_group_id + 0 = p_bg;
2573
2574 hr_utility.trace('v_annualizing_factor ='||to_number(v_annualizing_factor));
2575
2576 ELSIF UPPER(p_freq) <> 'HOURLY' THEN
2577
2578 -- Not hourly, an actual time period type!
2579 hr_utility.trace('Not hourly - an actual time period type');
2580
2581 BEGIN
2582
2583 hr_utility.trace(' selecting from per_time_period_types');
2584
2585 SELECT PT.number_per_fiscal_year
2586 INTO v_annualizing_factor
2587 FROM per_time_period_types PT
2588 WHERE UPPER(PT.period_type) = UPPER(p_freq);
2589
2590 hr_utility.trace('v_annualizing_factor ='||to_number(v_annualizing_factor));
2591
2592 EXCEPTION when NO_DATA_FOUND then
2593
2594 -- Added as part of SALLY CLEANUP.
2595 -- Could have been passed in an ASG_FREQ dbi which might have the values of
2596 -- 'Day' or 'Month' which do not map to a time period type. So we'll do these by hand.
2597
2598 IF UPPER(p_freq) = 'DAY' THEN
2599 hr_utility.trace(' p_freq = DAY');
2600 v_annualizing_factor := c_days_per_year;
2601 ELSIF UPPER(p_freq) = 'MONTH' THEN
2602 v_annualizing_factor := c_months_per_year;
2603 hr_utility.trace(' p_freq = MONTH');
2604 END IF;
2605
2606 END;
2607
2608 ELSE -- Hourly employee...
2609 hr_utility.trace(' Hourly Employee');
2610
2611 IF p_period_start_date IS NOT NULL THEN
2612 v_range_start := p_period_start_date;
2613 v_range_end := p_period_end_date;
2614 v_period_hours := TRUE;
2615 ELSE
2616 v_range_start := sysdate;
2617 v_range_end := sysdate + 6;
2618 v_period_hours := FALSE;
2619 END IF;
2620
2621 IF UPPER(p_asg_work_sched) <> 'NOT ENTERED' THEN
2622
2623 -- Hourly emp using work schedule.
2624 -- Get work schedule name:
2625
2626 v_ws_id := fnd_number.canonical_to_number(p_asg_work_sched);
2627
2628 SELECT user_column_name
2629 INTO v_work_sched_name
2630 FROM pay_user_columns
2631 WHERE user_column_id = v_ws_id
2632 AND NVL(business_group_id, p_bg) = p_bg
2633 AND NVL(legislation_code,'US') = 'US';
2634
2635
2636 v_hrs_per_range := Work_Schedule_Total_Hours( p_bg,
2637 v_work_sched_name,
2638 v_range_start,
2639 v_range_end);
2640
2641 ELSE-- Hourly emp using Standard Hours on asg.
2642
2643 hr_utility.trace(' Hourly emp using Standard Hours on asg');
2644
2645 hr_utility.trace('calling Standard_Hours_Worked');
2646
2647 v_hrs_per_range := Standard_Hours_Worked(p_asg_std_hrs,
2648 v_range_start,
2649 v_range_end,
2650 p_asg_std_freq);
2651
2652 hr_utility.trace('returned Standard_Hours_Worked');
2653 END IF;
2654
2655
2656 IF v_period_hours THEN
2657
2658 hr_utility.trace('v_period_hours = TRUE');
2659
2660 select TPT.number_per_fiscal_year
2661 into v_periods_per_fiscal_yr
2662 from pay_payrolls_f PPF,
2663 per_time_period_types TPT,
2664 fnd_sessions fs
2665 where PPF.payroll_id = p_payroll
2666 and fs.session_id = USERENV('SESSIONID')
2667 and fs.effective_date between PPF.effective_start_date and PPF.effective_end_date
2668 and TPT.period_type = PPF.period_type;
2669
2670 v_annualizing_factor := v_hrs_per_range * v_periods_per_fiscal_yr;
2671 hr_utility.trace('v_hrs_per_range ='||to_number(v_hrs_per_range));
2672 hr_utility.trace('v_periods_per_fiscal_yr ='||to_number(v_periods_per_fiscal_yr));
2673 hr_utility.trace('v_annualizing_factor ='||to_number(v_annualizing_factor));
2674
2675 ELSE
2676
2677 hr_utility.trace('v_period_hours = FALSE');
2678
2679 v_annualizing_factor := v_hrs_per_range * c_weeks_per_year;
2680
2681 hr_utility.trace('v_hrs_per_range ='||to_number(v_hrs_per_range));
2682 hr_utility.trace('c_weeks_per_year ='||to_number(c_weeks_per_year));
2683 hr_utility.trace('v_annualizing_factor ='||to_number(v_annualizing_factor));
2684
2685 END IF;
2686
2687 END IF;
2688
2689 END IF; -- (v_use_pay_basis = 0)
2690
2691
2692 hr_utility.trace(' Getting out of Get_Annualizing_Factor for '||v_pay_basis);
2693 RETURN v_annualizing_factor;
2694
2695 END Get_Annualizing_Factor;
2696
2697
2698 BEGIN -- Convert Figure
2699 --begin_convert_period_type
2700
2701 --hr_utility.trace_on(null,'UDFS');
2702
2703 hr_utility.trace('UDFS Entered Convert_Period_Type');
2704
2705 hr_utility.trace(' p_bus_grp_id: '|| p_bus_grp_id);
2706 hr_utility.trace(' p_payroll_id: '||p_payroll_id);
2707 hr_utility.trace(' p_asst_work_schedule: '||p_asst_work_schedule);
2708 hr_utility.trace(' p_asst_std_hours: '||p_asst_std_hours);
2709 hr_utility.trace(' p_figure: '||p_figure);
2710 hr_utility.trace(' p_from_freq : '||p_from_freq);
2711 hr_utility.trace(' p_to_freq: '||p_to_freq);
2712 hr_utility.trace(' p_period_start_date: '||to_char(p_period_start_date));
2713
2714 hr_utility.trace(' p_period_end_date: '||to_char(p_period_end_date));
2715 hr_utility.trace(' p_asst_std_freq: '||p_asst_std_freq);
2716
2717
2718 --
2719 -- If From_Freq and To_Freq are the same, then we're done.
2720 --
2721
2722 IF NVL(p_from_freq, 'NOT ENTERED') = NVL(p_to_freq, 'NOT ENTERED') THEN
2723
2724 RETURN p_figure;
2725
2726 END IF;
2727 hr_utility.trace('Calling Get_Annualizing_Factor for FROM case');
2728 v_from_annualizing_factor := Get_Annualizing_Factor(
2729 p_bg => p_bus_grp_id,
2730 p_payroll => p_payroll_id,
2731 p_freq => p_from_freq,
2732 p_asg_work_sched => p_asst_work_schedule,
2733 p_asg_std_hrs => p_asst_std_hours,
2734 p_asg_std_freq => p_asst_std_freq);
2735
2736 hr_utility.trace('Calling Get_Annualizing_Factor for TO case');
2737
2738 v_to_annualizing_factor := Get_Annualizing_Factor(
2739 p_bg => p_bus_grp_id,
2740 p_payroll => p_payroll_id,
2741 p_freq => p_to_freq,
2742 p_asg_work_sched => p_asst_work_schedule,
2743 p_asg_std_hrs => p_asst_std_hours,
2744 p_asg_std_freq => p_asst_std_freq);
2745
2746 --
2747 -- Annualize "Figure" and convert to To_Freq.
2748 --
2749 hr_utility.trace('v_from_annualizing_factor ='||to_char(v_from_annualizing_factor));
2750 hr_utility.trace('v_to_annualizing_factor ='||to_char(v_to_annualizing_factor));
2751
2752 IF v_to_annualizing_factor = 0 OR
2753 v_to_annualizing_factor = -999 OR
2754 v_from_annualizing_factor = -999 THEN
2755
2756 hr_utility.trace(' v_to_ann =0 or -999 or v_from = -999');
2757
2758 v_converted_figure := 0;
2759 RETURN v_converted_figure;
2760
2761 ELSE
2762
2763 hr_utility.trace(' v_to_ann NOT 0 or -999 or v_from = -999');
2764
2765 hr_utility.trace('p_figure Monthly Salary = '||p_figure);
2766 hr_utility.trace('v_from_annualizing_factor = '||v_from_annualizing_factor);
2767 hr_utility.trace('v_to_annualizing_factor = '||v_to_annualizing_factor);
2768
2769 v_converted_figure := (p_figure * v_from_annualizing_factor) / v_to_annualizing_factor;
2770 hr_utility.trace('conv figure is monthly_sal * ann_from div by ann to');
2771
2772 hr_utility.trace('UDFS v_converted_figure := '||v_converted_figure);
2773
2774 END IF;
2775
2776 -- Done
2777
2778 /***********************************************************
2779 The is wrapper is added to check the caluclation rule given
2780 at the payroll level. Depending upon the Rule we will the
2781 Get_Annualizing_Factor fun calls. If the rule is
2782 standard it goes to Standard Caluclation type. If the rule
2783 is Annual then it goes to ANNU rule
2784 **************************************************************/
2785 IF p_period_start_date IS NULL THEN
2786 v_stnd_start_date := sysdate;
2787 ELSE
2788 v_stnd_start_date := p_period_start_date ;
2789 END IF;
2790
2791 begin
2792 select nvl(ppf.prl_information2,'NOT ENTERED')
2793 into v_calc_type
2794 from pay_payrolls_f ppf
2795 where payroll_id = p_payroll_id
2796 and v_stnd_start_date between ppf.effective_start_date
2797 and ppf.effective_end_Date;
2798 exception
2799 when others then
2800 v_calc_type := null;
2801 end;
2802
2803 IF
2804 (v_calc_type = 'STND' and p_to_freq <> 'NOT ENTERED'
2805 and p_rate_calc_override = 'FIXED') OR
2806 (v_calc_type = 'NOT ENTERED' and p_to_freq <> 'NOT ENTERED'
2807 and p_rate_calc_override = 'FIXED') OR
2808 (v_calc_type = 'STND' and p_to_freq <> 'NOT ENTERED'
2809 and p_rate_calc_override = 'NOT ENTERED') OR
2810 (v_calc_type = 'ANNU' and p_to_freq <> 'NOT ENTERED'
2811 and p_rate_calc_override = 'FIXED')
2812 THEN
2813
2814 v_from_stnd_factor := Get_Annualizing_Factor(
2815 p_bg => p_bus_grp_id,
2816 p_payroll => p_payroll_id,
2817 p_freq => p_from_freq,
2818 p_asg_work_sched => p_asst_work_schedule,
2819 p_asg_std_hrs => p_asst_std_hours,
2820 p_asg_std_freq => p_asst_std_freq);
2821
2822 v_converted_figure :=(p_figure * v_from_stnd_factor/(52 * p_asst_std_hours));
2823
2824 END IF;
2825
2826
2827 RETURN v_converted_figure;
2828
2829 END Convert_Period_Type;
2830
2831 --
2832 -- **********************************************************************
2833 --
2834 FUNCTION work_schedule_total_hours(
2835 p_bg_id in NUMBER,
2836 p_ws_name in VARCHAR2,
2837 p_range_start in DATE,
2838 p_range_end in DATE)
2839 RETURN NUMBER IS
2840
2841 -- local constants
2842
2843 c_ws_tab_name VARCHAR2(80) ;
2844
2845 -- local variables
2846
2847 /* 353434, 368242 : Fixed number width for total hours */
2848 v_total_hours NUMBER(15,7);
2849 v_range_start DATE;
2850 v_range_end DATE;
2851 v_curr_date DATE;
2852 v_curr_day VARCHAR2(3); -- 3 char abbrev for day of wk.
2853 v_ws_name VARCHAR2(80); -- Work Schedule Name.
2854 v_gtv_hours VARCHAR2(80); -- get_table_value returns varchar2
2855 -- Remember to FND_NUMBER.CANONICAL_TO_NUMBER result.
2856 v_fnd_sess_row VARCHAR2(1);
2857 l_exists VARCHAR2(1);
2858 v_day_no NUMBER;
2859
2860 BEGIN -- work_schedule_total_hours
2861
2862 /* Init */
2863 v_total_hours := 0;
2864 c_ws_tab_name := 'COMPANY WORK SCHEDULES';
2865
2866 --Bug 1877889 start
2867 --changed to select the work schedule defined
2868 --at the business group level instead of
2869 --hardcoding the default work schedule
2870 --(COMPANY WORK SCHEDULES ) to the
2871 --variable c_ws_tab_name
2872
2873 begin
2874 select put.user_table_name
2875 into c_ws_tab_name
2876 from hr_organization_information hoi
2877 ,pay_user_tables put
2878 where hoi.organization_id = p_bg_id
2879 and hoi.org_information_context ='Work Schedule'
2880 and hoi.org_information1 = put.user_table_id ;
2881
2882 EXCEPTION WHEN NO_DATA_FOUND THEN
2883 null;
2884 end;
2885
2886 --Bug 1877889 end
2887
2888
2889 -- Set range to a single week if no dates are entered:
2890 -- IF (p_range_start IS NULL) AND (p_range_end IS NULL) THEN
2891 --
2892 v_range_start := NVL(p_range_start, sysdate);
2893 v_range_end := NVL(p_range_end, sysdate + 6);
2894 --
2895 -- END IF;
2896 -- Check for valid range
2897 IF v_range_start > v_range_end THEN
2898 --
2899 RETURN v_total_hours;
2900 -- hr_utility.set_message(801,'PAY_xxxx_INVALID_DATE_RANGE');
2901 -- hr_utility.raise_error;
2902 --
2903 END IF;
2904 --
2905 -- Get_Table_Value requires row in FND_SESSIONS. We must insert this
2906 -- record if one doe not already exist.
2907 --
2908 SELECT DECODE(COUNT(session_id), 0, 'N', 'Y')
2909 INTO v_fnd_sess_row
2910 FROM fnd_sessions
2911 WHERE session_id = userenv('sessionid');
2912 --
2913 IF v_fnd_sess_row = 'N' THEN
2914
2915 dt_fndate.set_effective_date(trunc(sysdate));
2916
2917 END IF;
2918
2919 --
2920 -- Track range dates:
2921 --
2922 -- Check if the work schedule is an id or a name. If the work
2923 -- schedule does not exist, then return 0.
2924 --
2925 BEGIN
2926 select 'Y'
2927 into l_exists
2928 from pay_user_tables PUT,
2929 pay_user_columns PUC
2930 where PUC.USER_COLUMN_NAME = p_ws_name
2931 and NVL(PUC.business_group_id, p_bg_id) = p_bg_id
2932 and NVL(PUC.legislation_code,'US') = 'US'
2933 and PUC.user_table_id = PUT.user_table_id
2934 and PUT.user_table_name = c_ws_tab_name;
2935
2936
2937 EXCEPTION WHEN NO_DATA_FOUND THEN NULL;
2938 END;
2939
2940 if l_exists = 'Y' then
2941 v_ws_name := p_ws_name;
2942 else
2943 BEGIN
2944 select PUC.USER_COLUMN_NAME
2945 into v_ws_name
2946 from pay_user_tables PUT,
2947 pay_user_columns PUC
2948 where PUC.USER_COLUMN_ID = p_ws_name
2949 and NVL(PUC.business_group_id, p_bg_id) = p_bg_id
2950 and NVL(PUC.legislation_code,'US') = 'US'
2951 and PUC.user_table_id = PUT.user_table_id
2952 and PUT.user_table_name = c_ws_tab_name;
2953
2954
2955 EXCEPTION WHEN NO_DATA_FOUND THEN
2956 RETURN v_total_hours;
2957 END;
2958 end if;
2959 --
2960 v_curr_date := v_range_start;
2961 --
2962 --
2963 LOOP
2964
2965 v_day_no := TO_CHAR(v_curr_date, 'D');
2966
2967
2968 SELECT decode(v_day_no,1,'SUN',2,'MON',3,'TUE',
2969 4,'WED',5,'THU',6,'FRI',7,'SAT')
2970 INTO v_curr_day
2971 FROM DUAL;
2972
2973 --
2974 --
2975 v_total_hours := v_total_hours + FND_NUMBER.CANONICAL_TO_NUMBER(hruserdt.get_table_value(p_bg_id,
2976 c_ws_tab_name,
2977 v_ws_name,
2978 v_curr_day));
2979 v_curr_date := v_curr_date + 1;
2980 --
2981 --
2982 EXIT WHEN v_curr_date > v_range_end;
2983 --
2984 END LOOP;
2985 --
2986 RETURN v_total_hours;
2987 --
2988 END work_schedule_total_hours;
2989 --
2990 -- **********************************************************************
2991 --
2992 FUNCTION chained_element_exists(p_bg_id in NUMBER,
2993 p_asst_id in NUMBER,
2994 p_payroll_id in NUMBER,
2995 p_date_earned in DATE,
2996 p_ele_name IN VARCHAR2) RETURN VARCHAR2 IS
2997 -- local vars
2998 v_ele_exists VARCHAR2(1);
2999 --
3000 BEGIN
3001 --
3002 -- Get formula context values: bg, payroll, asst ids; date earned.
3003 --
3004 -- ...
3005 --
3006 hr_utility.trace('UDFS Entered chained_element_exists');
3007
3008 SELECT DECODE(COUNT(0), 0, 'N', 'Y')
3009 INTO v_ele_exists
3010 FROM PAY_ELEMENT_ENTRIES_F ELE,
3011 PAY_ELEMENT_LINKS_F ELI,
3012 PAY_ELEMENT_TYPES_F ELT
3013 WHERE p_date_earned BETWEEN ELE.effective_start_date
3014 AND ELE.effective_end_date
3015 AND ELE.assignment_id = p_asst_id
3016 AND ELE.element_link_id = ELI.element_link_id
3017 AND ELI.business_group_id + 0 = p_bg_id
3018 AND ELI.element_type_id = ELT.element_type_id
3019 AND NVL(ELT.business_group_id, p_bg_id) = p_bg_id
3020 AND UPPER(ELT.element_name) = UPPER(p_ele_name);
3021
3022 hr_utility.trace('UDFS Leaving chained_element_exists');
3023 RETURN v_ele_exists;
3024
3025 END chained_element_exists;
3026
3027 --
3028 -- **********************************************************************
3029 --
3030
3031 FUNCTION us_jurisdiction_val (p_jurisdiction_code in VARCHAR2)
3032 RETURN VARCHAR2 IS
3033
3034 v_valid_jurisdiction VARCHAR2(1) ; -- RETURN var.
3035
3036 BEGIN
3037 /* Init */
3038 v_valid_jurisdiction := 'E';
3039
3040
3041 IF substr(p_jurisdiction_code, 8,4) = '0000' THEN
3042
3043 IF substr(p_jurisdiction_code, 4,3) = '000' THEN
3044
3045 -- Only entered a state geo, check against PAY_US_STATES.
3046
3047 SELECT 'S'
3048 INTO v_valid_jurisdiction
3049 FROM PAY_US_STATES
3050 WHERE STATE_CODE = substr(p_jurisdiction_code, 1,2);
3051
3052 ELSE
3053
3054 -- State/County entered
3055
3056 SELECT 'S'
3057 INTO v_valid_jurisdiction
3058 FROM PAY_US_COUNTIES
3059 WHERE STATE_CODE = substr(p_jurisdiction_code, 1,2)
3060 AND COUNTY_CODE = substr(p_jurisdiction_code, 4,3);
3061
3062 END IF;
3063
3064 ELSE
3065
3066 -- State/County/City entered
3067
3068 SELECT 'S'
3069 INTO v_valid_jurisdiction
3070 FROM PAY_US_CITY_NAMES
3071 WHERE STATE_CODE = substr(p_jurisdiction_code, 1,2)
3072 AND COUNTY_CODE = substr(p_jurisdiction_code, 4,3)
3073 AND CITY_CODE = substr(p_jurisdiction_code, 8,4)
3074 --AND PRIMARY_FLAG ='Y' -- Bug 3703863-- Commented out as this flag is 'N' for user defined cities.
3075 AND ROWNUM < 2;
3076
3077 END IF;
3078
3079 RETURN v_valid_jurisdiction;
3080
3081 EXCEPTION
3082 WHEN NO_DATA_FOUND THEN
3083
3084 v_valid_jurisdiction := 'E';
3085 RETURN v_valid_jurisdiction;
3086
3087 END us_jurisdiction_val;
3088
3089
3090 --
3091 -- **********************************************************************
3092 --
3093 FUNCTION get_process_run_flag ( p_date_earned IN DATE,
3094 p_ele_type_id IN NUMBER) RETURN VARCHAR2 IS
3095 --
3096 v_proc_run_type VARCHAR2(3) ;
3097 --
3098 BEGIN
3099
3100 /* Init */
3101 v_proc_run_type := 'REG';
3102 --
3103 --
3104 -- GET <ELE_NAME>_PROCESSING_RUN_TYPE. IF = 'ALL' then SKIP='N'.
3105 -- This DDF info is held in ELEMENT_INFORMATION3.
3106 --
3107 --
3108 begin
3109 SELECT element_information3
3110 INTO v_proc_run_type
3111 FROM pay_element_types_f
3112 WHERE p_date_earned BETWEEN effective_start_date
3113 AND effective_end_date
3114 AND element_type_id = p_ele_type_id;
3115 --
3116 RETURN v_proc_run_type;
3117 --
3118 exception when NO_DATA_FOUND then
3119 hr_utility.set_location('get_process_run_flag', 30);
3120 RETURN v_proc_run_type;
3121 end;
3122 --
3123 END get_process_run_flag;
3124 --
3125 -- **********************************************************************
3126 --
3127 FUNCTION check_dedn_freq ( p_payroll_id IN NUMBER,
3128 p_bg_id IN NUMBER,
3129 p_pay_action_id IN NUMBER,
3130 p_date_earned IN DATE,
3131 p_ele_type_id IN NUMBER) RETURN VARCHAR2 IS
3132
3133 v_skip_element VARCHAR2(1) ;
3134 v_number_per_fy NUMBER(3);
3135 v_run_number NUMBER(3);
3136 v_proc_run_type VARCHAR2(3);
3137 v_freq_rule_exists NUMBER(3);
3138 v_period_end_date DATE;
3139
3140 BEGIN
3141 /* Init */
3142 v_skip_element :='N';
3143
3144
3145 -- Check that <ELE_NAME>_PROCESSING_RUN_TYPE = 'ALL', meaning SKIP='N'.
3146 -- This DDF info is held in ELEMENT_INFORMATION3.
3147
3148 hr_utility.set_location('check_dedn_freq', 10);
3149
3150 begin
3151 SELECT element_information3
3152 INTO v_proc_run_type
3153 FROM pay_element_types_f
3154 WHERE p_date_earned BETWEEN effective_start_date
3155 AND effective_end_date
3156 AND element_type_id = p_ele_type_id;
3157
3158 IF v_proc_run_type = 'ALL' THEN
3159 RETURN v_skip_element;
3160 END IF;
3161
3162 exception when NO_DATA_FOUND then
3163 RETURN v_skip_element;
3164 end;
3165 --
3166 -- See if freq rule even comes into play here:
3167 --
3168 hr_elements.check_element_freq (p_payroll_id,
3169 p_bg_id,
3170 p_pay_action_id,
3171 p_date_earned,
3172 p_ele_type_id,
3173 v_skip_element);
3174
3175 hr_utility.set_location('check_dedn_freq', 45);
3176
3177 RETURN v_skip_element;
3178
3179 END check_dedn_freq;
3180
3181 --
3182 -- **********************************************************************
3183 --
3184 FUNCTION Separate_Check_Skip (
3185 p_bg_id in NUMBER,
3186 p_element_type_id in NUMBER,
3187 p_assact_id in NUMBER,
3188 p_payroll_id in NUMBER,
3189 p_date_earned in DATE) RETURN VARCHAR2 IS
3190
3191 -- This fun is called from skip rules attached to Deductions.
3192 -- Purpose is to check if an earnings requires special "Deduction Processing"
3193 -- ie. take only Pretax and/or Tax deductions.
3194 -- Algorithm:
3195 -- 1. Check for run results where "Deduction Processing" inpval is something
3196 -- other than 'A' for All.
3197 -- 2. If there is, then check classification of current deduction against
3198 -- the deduction processing requirement - ie. skip any deductions that
3199 -- are not pre-tax or tax deductions; further, if dedn proc is 'Tax Only'
3200 -- then skip pre-tax dedns as well - easy!
3201
3202 -- local constants
3203
3204 -- local vars
3205 v_dedn_proc VARCHAR2(3);
3206 v_dedn_proc_value VARCHAR2(80);
3207 v_ele_class_name VARCHAR2(80);
3208 v_skip_element VARCHAR2(1) ;
3209
3210 --
3211 BEGIN -- Separate_Check_Skip
3212 /* Init */
3213 v_skip_element := 'N';
3214 --
3215
3216 hr_utility.set_location('Separate_Check_Skip', 7);
3217
3218 SELECT RRV.result_value
3219 INTO v_dedn_proc
3220 FROM pay_run_result_values RRV,
3221 pay_run_results PRR,
3222 pay_input_values_f IPV
3223 WHERE PRR.assignment_action_id = p_assact_id
3224 AND RRV.result_value <> 'A'
3225 AND RRV.run_result_id = PRR.run_result_id
3226 AND IPV.input_value_id = RRV.input_value_id
3227 AND p_date_earned BETWEEN IPV.effective_start_date
3228 AND IPV.effective_end_date
3229 and ipv.element_type_id = p_element_type_id
3230 AND UPPER(IPV.name) = 'DEDUCTION PROCESSING'
3231 AND IPV.business_group_id + 0 = p_bg_id
3232 AND prr.element_type_id = ipv.element_type_id;
3233
3234 --
3235 -- We now assume there is a value in Deduction Processing input value of
3236 -- either 'T' ("Tax Only") or 'PTT' ("Pre-Tax and Tax Only).
3237 --
3238
3239 v_skip_element := 'Y';
3240
3241 hr_utility.set_location('Separate_Check_Skip', 9);
3242
3243 begin
3244
3245 SELECT ECL.classification_name
3246 INTO v_ele_class_name
3247 FROM pay_element_types_f ELT,
3248 pay_element_classifications ECL
3249 WHERE ECL.classification_id = ELT.classification_id
3250 AND ELT.business_group_id + 0 = p_bg_id
3251 AND p_date_earned BETWEEN ELT.effective_start_date
3252 AND ELT.effective_end_date
3253 AND ELT.element_type_id = p_element_type_id;
3254
3255 IF UPPER(v_ele_class_name) = 'TAX DEDUCTIONS' THEN
3256
3257 -- Change v_skip_element back to 'N' if this a tax deduction.
3258 -- ie. we know DEDN PROC inpval is not null, meaning it's either TAX ONLY
3259 -- or PRETAX AND TAX ONLY.
3260
3261 hr_utility.set_location('Separate_Check_Skip', 10);
3262 v_skip_element := 'N';
3263 RETURN v_skip_element;
3264
3265 ELSIF UPPER(v_ele_class_name) = 'PRE-TAX DEDUCTIONS' AND
3266 v_dedn_proc = 'PTT' THEN
3267
3268 -- Change v_skip_element back to 'N' if dedn proc = 'PTT'
3269
3270 hr_utility.set_location('Separate_Check_Skip', 11);
3271 v_skip_element := 'N';
3272 RETURN v_skip_element;
3273
3274 END IF;
3275
3276 exception WHEN NO_DATA_FOUND THEN
3277 hr_utility.set_location('Separate_Check_Skip - Error EleClass NOTFOUND', 12);
3278 v_skip_element := 'Y';
3279 -- hr_utility.set_message(801, 'PAY_ELE_CLASS_NOTFOUND');
3280 -- hr_utility.raise_error;
3281 end;
3282
3283 RETURN v_skip_element;
3284
3285 EXCEPTION
3286 WHEN NO_DATA_FOUND THEN
3287 hr_utility.set_location('Separate_Check_Skip', 21);
3288 RETURN v_skip_element;
3289 -- Special Dedn Proc not required. SKIP_FLAG = 'N'.
3290 --
3291 END Separate_Check_Skip;
3292 --
3293 -- **********************************************************************
3294 --
3295 /* ( ) OTHER_NON_SEPARATE_CHECK
3296 Desc: Returns 'Y' if other ELEMENT ENTRIES exist where
3297 Separate_Check = 'N' or null ;
3298 OR
3299 Earnings element entries exist with no "Separate Check" input
3300 value at all.
3301 */
3302 FUNCTION Other_Non_Separate_Check (
3303 p_date_earned IN DATE,
3304 p_ass_id IN NUMBER) RETURN VARCHAR2 IS
3305
3306 -- local vars
3307 sepcheck_flag VARCHAR2(1) ;
3308 --
3309
3310 BEGIN
3311
3312 /* Init */
3313 sepcheck_flag :='N';
3314
3315
3316 hr_utility.set_location('Other_Non_Separate_Check', 10);
3317
3318 SELECT DECODE(COUNT(IPV.input_value_id), 0, 'N', 'Y')
3319 INTO sepcheck_flag
3320 FROM pay_element_entry_values_f EEV,
3321 pay_element_entries_f ELE,
3322 pay_input_values_f IPV
3323 WHERE ELE.assignment_id = p_ass_id
3324 AND p_date_earned BETWEEN ELE.effective_start_date
3325 AND ELE.effective_end_date
3326 AND ELE.element_entry_id = EEV.element_entry_id
3327 AND p_date_earned BETWEEN EEV.effective_start_date
3328 AND EEV.effective_end_date
3329 AND nvl(EEV.screen_entry_value,'N') = 'N'
3330 AND EEV.input_value_id = IPV.input_value_id
3331 AND UPPER(IPV.name) = 'SEPARATE CHECK';
3332 --
3333 IF sepcheck_flag = 'Y' THEN
3334 hr_utility.set_location('Other_Non_Separate_Check', 15);
3335 RETURN sepcheck_flag;
3336 END IF;
3337 --
3338 hr_utility.set_location('Other_Non_Separate_Check', 20);
3339
3340 SELECT DECODE(COUNT(ELE.element_entry_id), 0, 'N', 'Y')
3341 INTO sepcheck_flag
3342 FROM pay_element_entries_f ELE,
3343 pay_element_links_f ELL,
3344 pay_element_types_f ELT,
3345 pay_element_classifications ECL
3346 WHERE ELE.assignment_id = p_ass_id
3347 AND p_date_earned BETWEEN ELE.effective_start_date
3348 and ELE.effective_end_date
3349 AND ELE.element_link_id = ELL.element_link_id
3350 AND p_date_earned BETWEEN ELL.effective_start_date
3351 and ELL.effective_end_date
3352 AND ELL.element_type_id = ELT.element_type_id
3353 AND p_date_earned BETWEEN ELT.effective_start_date
3354 and ELT.effective_end_date
3355 AND ECL.classification_id = ELT.classification_id
3356 AND UPPER(ECL.classification_name) IN ( 'EARNINGS',
3357 'SUPPLEMENTAL EARNINGS',
3358 'IMPUTED EARNINGS',
3359 'NON-PAYROLL PAYMENTS')
3360 AND NOT EXISTS
3361 (SELECT 'X'
3362 FROM pay_input_values_f IPV
3363 WHERE IPV.element_type_id = ELT.element_type_id
3364 AND p_date_earned BETWEEN IPV.effective_start_date
3365 and IPV.effective_end_date
3366 AND UPPER(IPV.name) = 'SEPARATE CHECK');
3367 --
3368
3369 RETURN sepcheck_flag;
3370
3371 --
3372 EXCEPTION
3373 WHEN NO_DATA_FOUND THEN
3374 hr_utility.set_location('Other_Non_Separate_Check', 30);
3375 RETURN sepcheck_flag;
3376 --
3377 END Other_Non_Separate_Check;
3378 --
3379 /*
3380 *****************************************************
3381 FUNCTION NAME:
3382 *****************************************************
3383 OT_Base_Rate
3384 Inputs: p_ass_id, -- Context from formula, pass TO Convert_Figure
3385 p_date_earned -- Context
3386 p_work_sched -- Pass to Convert_Figure
3387 p_std_hours -- Pass to Convert_Figure
3388
3389 Outputs: v_ot_base_rate -- Hourly Rate for use by OT
3390
3391 12 Dec 1993 hparicha Created.
3392
3393 *****************************************************
3394 DESCRIPTION:
3395 *****************************************************
3396 1) Add hourly Rate from Regular Wages, Time Entry Wages,
3397 or equivalent hourly rate from Regular Salary to v_ot_base_rate;
3398 2) Get elements and formula names where "Include in OT Base" = 'Y'
3399 3) Get hourly rate from either input values or run result values
3400 - based on calculation method (ie. formula name).
3401 --
3402 Calc Rule Include in OT Base
3403 ============== ====================================
3404 Flat Amount Amount input value converted to hourly rate.
3405 Hours * Rate Rate input value.
3406 Hours * Rate * Multiple Rate input value.
3407 Percentage of Reg Sal Percentage input value * Monthly Salary, converted to
3408 hourly rate.
3409 Gross Up Gross Amount run result value converted to hourly rate.
3410 *****************************************************
3411 FUNCTION TEXT:
3412 *****************************************************
3413 */
3414 FUNCTION OT_Base_Rate ( p_bg_id in NUMBER,
3415 p_pay_id in NUMBER,
3416 p_ass_id in NUMBER,
3417 p_ass_action_id in NUMBER,
3418 p_date_earned in DATE,
3419 p_work_sched in VARCHAR2,
3420 p_std_hours in NUMBER,
3421 p_ass_salary in NUMBER,
3422 p_ass_sal_basis in VARCHAR2,
3423 p_std_freq in VARCHAR2)
3424 RETURN NUMBER IS
3425 --
3426 -- local constants
3427 --
3428 c_ot_scale VARCHAR2(80) ;
3429 c_rate_table_name VARCHAR2(80) ;
3430 c_rate_table_column VARCHAR2(80) ;
3431 --
3432 -- local vars
3433 --
3434 v_entry_id NUMBER(9);
3435 v_ot_base_rate NUMBER(27,7) ;
3436 v_tew_rate NUMBER(27,7) ;
3437 v_regwage_rate NUMBER(27,7) ;
3438 v_regsal_rate NUMBER(27,7) ;
3439 v_regsal_mosal NUMBER(27,7) ;
3440 v_tew_rcode VARCHAR2(80);
3441 v_regwage_rcode VARCHAR2(80);
3442 v_use_regwage NUMBER(2);
3443 v_use_regsal NUMBER(2);
3444 v_ele_type_id NUMBER(9);
3445 v_ele_class_id NUMBER(9);
3446 v_include_in_ot VARCHAR2(1);
3447 v_equiv_hrly_rate VARCHAR2(80) ;
3448 v_chk_sal VARCHAR2(1) ;
3449 v_eletype_id NUMBER(9);
3450 v_ele_name VARCHAR2(80);
3451 v_ff_name VARCHAR2(80);
3452 v_flat_amount NUMBER(27,7) ;
3453 v_flat_total NUMBER(27,7) ;
3454 v_flat_count NUMBER(3) ;
3455 v_percentage NUMBER(27,7) ;
3456 v_pct_sal NUMBER(27,7) ;
3457 v_pct_total NUMBER(27,7) ;
3458 v_pct_count NUMBER(3) ;
3459 v_rate NUMBER(27,7) ;
3460 v_rate_total NUMBER(27,7) ;
3461 v_rate_count NUMBER(3) ;
3462 v_rate_rcode VARCHAR2(80);
3463 v_rate_multiple NUMBER(27,7) ;
3464 v_rate_mult_count NUMBER(3) ;
3465 v_gross_results NUMBER(3) ;
3466 v_gross_amount NUMBER(27,7) ;
3467 v_gross_total NUMBER(27,7) ;
3468 v_gross_count NUMBER(3) ;
3469 v_tew_count NUMBER(3) ;
3470 v_tew_total_rate NUMBER(27,7) ;
3471 v_pay_basis_rate NUMBER(27,7) ;
3472 v_work_sched_name VARCHAR2(80);
3473 v_ws_id NUMBER(9);
3474 v_range_start DATE;
3475 v_range_end DATE;
3476 --
3477 -- local cursors
3478 --
3479 CURSOR get_tew_rate IS
3480 SELECT /*+ ORDERED */ NVL(fnd_number.canonical_to_number(EEV.screen_entry_value), 0),
3481 EEV.element_entry_id
3482 FROM pay_element_entries_f ELE,
3483 pay_element_entry_values_f EEV,
3484 pay_input_values_f IPV,
3485 pay_element_types_f ELT
3486 WHERE ELE.assignment_id = p_ass_id
3487 AND p_date_earned BETWEEN ELE.effective_start_date
3488 AND ELE.effective_end_date
3489 AND ELE.element_entry_id = EEV.element_entry_id
3490 AND p_date_earned BETWEEN EEV.effective_start_date
3491 AND EEV.effective_end_date
3492 AND EEV.input_value_id = IPV.input_value_id
3493 AND p_date_earned BETWEEN IPV.effective_start_date
3494 AND IPV.effective_end_date
3495 AND IPV.element_type_id = ELT.element_type_id
3496 AND p_date_earned BETWEEN ELT.effective_start_date
3497 AND ElT.effective_end_date
3498 and ipv.business_group_id = elt.business_group_id
3499 and ipv.legislation_code = elt.legislation_code
3500 AND ELT.element_name = 'Time Entry Wages'
3501 AND IPV.name = 'Rate';
3502 --
3503 /* CURSOR get_tew_rcode IS
3504 SELECT NVL(EEV.screen_entry_value, 'NOT ENTERED')
3505 FROM pay_element_entry_values_f EEV,
3506 pay_element_entries_f ELE,
3507 pay_element_types_f ELT,
3508 pay_input_values_f IPV
3509 WHERE ELE.assignment_id = p_ass_id
3510 AND p_date_earned BETWEEN ELE.effective_start_date
3511 AND ELE.effective_end_date
3512 AND ELE.element_entry_id = EEV.element_entry_id
3513 AND p_date_earned BETWEEN EEV.effective_start_date
3514 AND EEV.effective_end_date
3515 AND EEV.input_value_id = IPV.input_value_id
3516 AND p_date_earned BETWEEN IPV.effective_start_date
3517 AND IPV.effective_end_date
3518 AND ELT.element_name = 'Time Entry Wages'
3519 AND p_date_earned BETWEEN ELT.effective_start_date
3520 AND ElT.effective_end_date
3521 AND ELT.element_type_id = IPV.element_type_id
3522 AND ipv.business_group_id = elt.business_group_id
3523 AND ipv.legislation_code = elt.legislation_code
3524 AND IPV.name = 'Rate Code';
3525
3526 --
3527 */
3528 --
3529 CURSOR get_include_in_ot IS
3530 SELECT ELT.element_type_id,
3531 ELT.element_name,
3532 FRA.formula_name
3533 FROM pay_element_entries_f ELE,
3534 pay_element_links_f ELI,
3535 pay_element_types_f ELT,
3536 pay_status_processing_rules_f SPR,
3537 ff_formulas_f FRA
3538 WHERE FRA.formula_id = SPR.formula_id
3539 AND p_date_earned BETWEEN SPR.effective_start_date
3540 AND SPR.effective_end_date
3541 AND SPR.assignment_status_type_id IS NULL
3542 AND SPR.element_type_id = ELT.element_type_id
3543 AND p_date_earned BETWEEN ELE.effective_start_date
3544 AND ELE.effective_end_date
3545 AND ELE.assignment_id = p_ass_id
3546 AND ELE.element_link_id = ELI.element_link_id
3547 AND p_date_earned BETWEEN ELI.effective_start_date
3548 AND ELI.effective_end_date
3549 AND ELI.element_type_id = ELT.element_type_id
3550 AND p_date_earned BETWEEN ELT.effective_start_date
3551 AND ELT.effective_end_date
3552 AND ELT.element_information8 = 'Y'
3553 AND ELT.element_information_category IN ( 'US_EARNINGS',
3554 'US_SUPPLEMENTAL EARNINGS');
3555 --
3556 -- These cursors get ALL entries of a particular element type during
3557 -- the period:
3558 CURSOR get_flat_amounts IS
3559 SELECT fnd_number.canonical_to_number(EEV.screen_entry_value)
3560 FROM pay_element_links_f pel,
3561 pay_element_entries_f ele,
3562 pay_element_entry_values_f eev,
3563 pay_input_values_f ipv
3564 WHERE pel.element_type_id = v_eletype_id
3565 AND p_date_earned BETWEEN pel.effective_start_date
3566 AND pel.effective_end_date
3567 AND ele.element_link_id = pel.element_link_id
3568 AND ele.assignment_id = p_ass_id
3569 AND ele.element_entry_id = eev.element_entry_id
3570 AND p_date_earned BETWEEN eev.effective_start_date
3571 AND eev.effective_end_date
3572 AND EEV.input_value_id = ipv.input_value_id
3573 AND IPV.element_type_id = pel.element_type_id --v_eletype_id
3574 AND IPV.name = 'Amount';
3575 --
3576 CURSOR get_rates IS
3577 SELECT fnd_number.canonical_to_number(EEV.screen_entry_value),
3578 EEV.element_entry_id
3579 FROM pay_element_links_f pel,
3580 pay_element_entries_f ele,
3581 pay_element_entry_values_f eev,
3582 pay_input_values_f ipv
3583 WHERE pel.element_type_id = v_eletype_id
3584 AND p_date_earned BETWEEN pel.effective_start_date
3585 AND pel.effective_end_date
3586 AND ele.element_link_id = pel.element_link_id
3587 AND ELE.assignment_id = p_ass_id
3588 AND ELE.element_entry_id = EEV.element_entry_id
3589 AND p_date_earned BETWEEN EEV.effective_start_date
3590 AND EEV.effective_end_date
3591 AND EEV.input_value_id = IPV.input_value_id
3592 AND IPV.element_type_id = pel.element_type_id --v_eletype_id
3593 AND IPV.name = 'Rate';
3594 --
3595 CURSOR get_percentages IS
3596 SELECT fnd_number.canonical_to_number(EEV.screen_entry_value)
3597 FROM pay_element_links_f pel,
3598 pay_element_entries_f ele,
3599 pay_element_entry_values_f eev,
3600 pay_input_values_f ipv
3601 WHERE pel.element_type_id = v_eletype_id
3602 AND p_date_earned BETWEEN pel.effective_start_date
3603 AND pel.effective_end_date
3604 AND ele.element_link_id = pel.element_link_id
3605 AND ele.assignment_id = p_ass_id
3606 AND ele.element_entry_id = EEV.element_entry_id
3607 AND p_date_earned BETWEEN EEV.effective_start_date
3608 AND EEV.effective_end_date
3609 AND eev.input_value_id = IPV.input_value_id
3610 AND ipv.element_type_id = pel.element_type_id --v_eletype_id
3611 AND ipv.name = 'Percentage';
3612 --
3613 CURSOR get_grosses IS
3614 SELECT fnd_number.canonical_to_number(RRV.result_value)
3615 FROM pay_run_result_values RRV,
3616 pay_run_results RRS,
3617 pay_input_values_f IPV,
3618 pay_element_types_f ELT
3619 WHERE RRV.input_value_id = IPV.input_value_id
3620 AND RRV.run_result_id = RRS.run_result_id
3621 AND RRS.element_type_id = ELT.element_type_id
3622 AND RRS.assignment_action_id = p_ass_action_id
3623 AND p_date_earned BETWEEN IPV.effective_start_date
3624 AND IPV.effective_end_date
3625 AND IPV.name = 'Pay Value'
3626 AND IPV.element_type_id = ELT.element_type_id
3627 AND p_date_earned BETWEEN ELT.effective_start_date
3628 AND ELT.effective_end_date
3629 AND ELT.element_name = 'Vertex ' || v_ele_name || ' Gross';
3630 --
3631 -- Check with Roy on "<ELE_NAME> Gross" element being created for grossups.
3632 --
3633 --
3634 BEGIN -- OT_Base_Rate
3635
3636 /* Init */
3637 c_ot_scale := 'Hourly';
3638 c_rate_table_name := 'WAGE RATES';
3639 c_rate_table_column := 'Wage Rate';
3640 v_ot_base_rate := 0;
3641 v_tew_rate := 0;
3642 v_regwage_rate := 0;
3643 v_regsal_rate := 0;
3644 v_regsal_mosal := 0;
3645 v_equiv_hrly_rate := 'No OT';
3646 v_chk_sal := 'N';
3647 v_flat_amount := 0;
3648 v_flat_total := 0;
3649 v_flat_count := 0;
3650 v_percentage := 0;
3651 v_pct_sal := 0;
3652 v_pct_total := 0;
3653 v_pct_count := 0;
3654 v_rate := 0;
3655 v_rate_total := 0;
3656 v_rate_count := 0;
3657 v_rate_multiple := 0;
3658 v_rate_mult_count := 0;
3659 v_gross_results := 0;
3660 v_gross_amount := 0;
3661 v_gross_total := 0;
3662 v_gross_count := 0;
3663 v_tew_count := 0;
3664 v_tew_total_rate := 0;
3665 v_pay_basis_rate := 0;
3666 --
3667
3668 --
3669 -- Get "Regular" rate from either Time Entry Wages, Regular Wages,
3670 -- or Regular Salary. For Time Entry rate, we need to take an average
3671 -- of all Rates entered via Time Entry Wages.
3672 -- Remember to check for a rate via Rate Code!
3673 --
3674 -- Go ahead and set pay_basis_rate now - will most likely be used somewhere.
3675 --
3676 hr_utility.set_location('OT_Base_Rate', 5);
3677
3678 select start_date,
3679 end_date
3680 into v_range_start,
3681 v_range_end
3682 from per_time_periods
3683 where payroll_id = p_pay_id
3684 and p_date_earned between start_date and end_date;
3685
3686 v_pay_basis_rate := FND_NUMBER.CANONICAL_TO_NUMBER(hr_us_ff_udfs.convert_period_type(
3687 p_bus_grp_id => p_bg_id,
3688 p_payroll_id => p_pay_id,
3689 p_asst_work_schedule => p_work_sched,
3690 p_asst_std_hours => p_std_hours,
3691 p_figure => p_ass_salary,
3692 p_from_freq => p_ass_sal_basis,
3693 p_to_freq => 'HOURLY',
3694 p_period_start_date => v_range_start,
3695 p_period_end_date => v_range_end,
3696 p_asst_std_freq => p_std_freq));
3697 --
3698 OPEN get_tew_rate;
3699 --
3700 LOOP
3701 hr_utility.set_location('OT_Base_Rate', 10);
3702 FETCH get_tew_rate
3703 INTO v_tew_rate, v_entry_id;
3704 EXIT WHEN get_tew_rate%NOTFOUND;
3705 --
3706 v_tew_count := v_tew_count + 1;
3707 IF v_tew_rate <> 0 THEN
3708 v_tew_total_rate := v_tew_total_rate + v_tew_rate;
3709 ELSE -- no Rate entered, check Rate Code
3710 hr_utility.set_location('OT_Base_Rate', 15);
3711 SELECT /*+ ORDERED */ NVL(EEV.screen_entry_value, 'NOT ENTERED')
3712 INTO v_tew_rcode
3713 FROM pay_element_entries_f ELE,
3714 pay_element_entry_values_f EEV,
3715 pay_input_values_f IPV,
3716 pay_element_types_f ELT
3717 WHERE ELE.assignment_id = p_ass_id
3718 AND EEV.element_entry_id = v_entry_id
3719 AND ELE.element_entry_id = EEV.element_entry_id
3720 AND p_date_earned BETWEEN EEV.effective_start_date
3721 AND EEV.effective_end_date
3722 AND EEV.input_value_id = IPV.input_value_id
3723 AND p_date_earned BETWEEN IPV.effective_start_date
3724 AND IPV.effective_end_date
3725 AND ELT.element_name = 'Time Entry Wages'
3726 and p_date_earned BETWEEN elt.effective_start_date
3727 AND elt.effective_end_date
3728 AND ELT.element_type_id = IPV.element_type_id
3729 AND ipv.business_group_id = elt.business_group_id
3730 AND ipv.legislation_code = elt.legislation_code
3731 AND IPV.name = 'Rate Code';
3732
3733 --
3734 IF v_tew_rcode = 'NOT ENTERED' THEN
3735 -- Use pay basis salary converted to hourly rate.
3736 v_tew_total_rate := v_tew_total_rate + v_pay_basis_rate;
3737 ELSE
3738 -- Find rate from rate table.
3739 hr_utility.set_location('OT_Base_Rate', 17);
3740 v_tew_total_rate := v_tew_total_rate +
3741 FND_NUMBER.CANONICAL_TO_NUMBER(hruserdt.get_table_value(
3742 p_bg_id,
3743 c_rate_table_name,
3744 c_rate_table_column,
3745 v_tew_rcode));
3746 END IF;
3747 --
3748 END IF;
3749 --
3750 END LOOP;
3751 --
3752 CLOSE get_tew_rate;
3753 --
3754 IF v_tew_count = 0 THEN -- ie. only use "Regular" rates if TEW not entered.
3755 hr_utility.set_location('OT_Base_Rate', 20);
3756 SELECT /*+ ORDERED */ COUNT(IPV.input_value_id)
3757 INTO v_use_regwage
3758 FROM pay_element_entries_f ELE,
3759 pay_element_entry_values_f EEV,
3760 pay_input_values_f IPV,
3761 pay_element_types_f ELT
3762 WHERE ELE.assignment_id = p_ass_id
3763 AND p_date_earned BETWEEN ELE.effective_start_date
3764 AND ELE.effective_end_date
3765 AND ELE.element_entry_id = EEV.element_entry_id
3766 AND p_date_earned BETWEEN EEV.effective_start_date
3767 AND EEV.effective_end_date
3768 AND EEV.input_value_id = IPV.input_value_id
3769 AND p_date_earned BETWEEN IPV.effective_start_date
3770 AND ipv.effective_end_date
3771 AND ELT.element_name = 'Regular Wages'
3772 AND p_date_earned BETWEEN elt.effective_start_date
3773 AND elt.effective_end_date
3774 AND ELT.element_type_id = IPV.element_type_id
3775 AND IPV.business_group_id = ELT.business_group_id
3776 AND IPV.legislation_code = ELT.legislation_code
3777 AND IPV.name = 'Rate';
3778
3779 --
3780 IF v_use_regwage <> 0 THEN
3781 hr_utility.set_location('OT_Base_Rate', 30);
3782 SELECT /*+ ORDERED */ NVL(fnd_number.canonical_to_number(EEV.screen_entry_value), 0),
3783 EEV.element_entry_id
3784 INTO v_regwage_rate,
3785 v_entry_id
3786 FROM pay_element_entries_f ELE,
3787 pay_element_entry_values_f EEV,
3788 pay_input_values_f IPV,
3789 pay_element_types_f ELT
3790 WHERE ELE.assignment_id = p_ass_id
3791 AND p_date_earned BETWEEN ele.effective_start_date
3792 AND ele.effective_end_date
3793 AND ELE.element_entry_id = EEV.element_entry_id
3794 AND p_date_earned BETWEEN EEV.effective_start_date
3795 AND EEV.effective_end_date
3796 AND EEV.input_value_id = IPV.input_value_id
3797 AND p_date_earned BETWEEN ipv.effective_start_date
3798 AND ipv.effective_end_date
3799 AND ELT.element_name = 'Regular Wages'
3800 AND p_date_earned BETWEEN elt.effective_start_date
3801 AND elt.effective_end_date
3802 AND ELT.element_type_id = IPV.element_type_id
3803 AND IPV.name = 'Rate'
3804 AND IPV.business_group_id = ELT.business_group_id
3805 AND IPV.legislation_code = ELT.legislation_code;
3806
3807 --
3808 IF v_regwage_rate = 0 THEN
3809 hr_utility.set_location('OT_Base_Rate', 40);
3810 SELECT /*+ ORDERED */ NVL(EEV.screen_entry_value, 'NOT ENTERED')
3811 INTO v_regwage_rcode
3812 FROM pay_element_entries_f ELE,
3813 pay_element_entry_values_f EEV,
3814 pay_input_values_f IPV,
3815 pay_element_types_f ELT
3816 WHERE ELE.assignment_id = p_ass_id
3817 AND p_date_earned between ELE.effective_start_date
3818 AND ELE.effective_end_date
3819 AND ELE.element_entry_id = EEV.element_entry_id
3820 AND p_date_earned BETWEEN EEV.effective_start_date
3821 AND EEV.effective_end_date
3822 AND EEV.element_entry_id = v_entry_id
3823 AND EEV.input_value_id = IPV.input_value_id
3824 AND p_date_earned BETWEEN IPV.effective_start_date
3825 AND IPV.effective_end_date
3826 AND ELT.element_name = 'Regular Wages'
3827 ANd p_date_earned BETWEEN ELT.effective_start_date
3828 AND ELT.effective_end_date
3829 AND ELT.element_type_id = IPV.element_type_id
3830 AND IPV.name = 'Rate Code'
3831 AND IPV.business_group_id = ELT.business_group_id
3832 AND IPV.legislation_code = ELT.legislation_code;
3833
3834 --
3835 hr_utility.set_location('OT_Base_Rate', 41);
3836 v_regwage_rate := FND_NUMBER.CANONICAL_TO_NUMBER(hruserdt.get_table_value(
3837 p_bus_group_id => p_bg_id,
3838 p_table_name => c_rate_table_name,
3839 p_col_name => c_rate_table_column,
3840 p_row_value => v_regwage_rcode));
3841 END IF;
3842 v_ot_base_rate := v_ot_base_rate + v_regwage_rate;
3843 --
3844 ELSE
3845 hr_utility.set_location('OT_Base_Rate', 50);
3846 SELECT /*+ ORDERED */ COUNT(IPV.input_value_id)
3847 INTO v_use_regsal
3848 FROM pay_element_entries_f ELE,
3849 pay_element_entry_values_f EEV,
3850 pay_input_values_f IPV,
3851 pay_element_types_f ELT
3852 WHERE ELE.assignment_id = p_ass_id
3853 AND p_date_earned BETWEEN ELE.effective_start_date
3854 AND ELE.effective_end_date
3855 AND ELE.element_entry_id = EEV.element_entry_id
3856 AND p_date_earned BETWEEN EEV.effective_start_date
3857 AND EEV.effective_end_date
3858 AND EEV.input_value_id = IPV.input_value_id
3859 AND p_date_earned BETWEEN IPV.effective_start_date
3860 AND IPV.effective_end_date
3861 AND ELT.element_name = 'Regular Salary'
3862 AND p_date_earned BETWEEN ELT.effective_start_date
3863 AND ELT.effective_end_date
3864 AND ELT.element_type_id = IPV.element_type_id
3865 AND IPV.name = 'Monthly Salary'
3866 AND IPV.business_group_id = ELT.business_group_id
3867 AND IPV.legislation_code = ELT.legislation_code;
3868
3869 --
3870 IF v_use_regsal <> 0 THEN
3871 hr_utility.set_location('OT_Base_Rate', 51);
3872 SELECT /*+ ORDERED */ NVL(fnd_number.canonical_to_number(EEV.screen_entry_value), 0)
3873 INTO v_regsal_mosal
3874 FROM pay_element_entries_f ELE,
3875 pay_element_entry_values_f EEV,
3876 pay_input_values_f IPV,
3877 pay_element_types_f ELT
3878 WHERE ELE.assignment_id = p_ass_id
3879 AND p_date_earned BETWEEN ELE.effective_start_date
3880 AND ELE.effective_end_date
3881 AND ELE.element_entry_id = EEV.element_entry_id
3882 AND p_date_earned BETWEEN EEV.effective_start_date
3883 AND EEV.effective_end_date
3884 AND EEV.input_value_id = IPV.input_value_id
3885 AND p_date_earned BETWEEN IPV.effective_start_date
3886 AND IPV.effective_end_date
3887 AND ELT.element_name = 'Regular Salary'
3888 AND p_date_earned BETWEEN ELT.effective_start_date
3889 AND ELT.effective_end_date
3890 AND ELT.element_type_id = IPV.element_type_id
3891 AND IPV.name = 'Monthly Salary'
3892 AND IPV.business_group_id = ELT.business_group_id
3893 AND IPV.legislation_code = ELT.legislation_code;
3894
3895 --
3896 hr_utility.set_location('OT_Base_Rate', 60);
3897
3898 v_regsal_rate := hr_us_ff_udfs.Convert_Period_Type(
3899 p_bus_grp_id => p_bg_id,
3900 p_payroll_id => p_pay_id,
3901 p_asst_work_schedule => p_work_sched,
3902 p_asst_std_hours => p_std_hours,
3903 p_figure => v_regsal_mosal,
3904 p_from_freq => p_ass_sal_basis,
3905 p_to_freq => 'HOURLY',
3906 p_period_start_date => v_range_start,
3907 p_period_end_date => v_range_end,
3908 p_asst_std_freq => p_std_freq);
3909 --
3910 END IF;
3911 --
3912 END IF; -- "Regular" rate done.
3913 --
3914 ELSE
3915 -- TEW entered, so take average:
3916 v_ot_base_rate := v_ot_base_rate + (v_tew_total_rate / v_tew_count);
3917 --
3918 END IF; -- TEW entered.
3919 --
3920 -- Now add all other "Include in OT Base" = 'Y' values.
3921 --
3922 OPEN get_include_in_ot;
3923 LOOP
3924 --
3925 hr_utility.set_location('OT_Base_Rate', 70);
3926 FETCH get_include_in_ot
3927 INTO v_eletype_id,
3928 v_ele_name,
3929 v_ff_name;
3930 EXIT WHEN get_include_in_ot%NOTFOUND;
3931 --
3932 IF SUBSTR(v_ff_name,1,11) = 'FLAT_AMOUNT' THEN
3933 -- Find "Amount" entered, convert to hourly figure.
3934 hr_utility.set_location('OT_Base_Rate', 80);
3935 OPEN get_flat_amounts;
3936 LOOP
3937 FETCH get_flat_amounts
3938 INTO v_flat_amount;
3939 EXIT WHEN get_flat_amounts%NOTFOUND;
3940 v_flat_count := v_flat_count + 1;
3941 hr_utility.set_location('OT_Base_Rate', 90);
3942
3943 v_flat_total := v_flat_total + hr_us_ff_udfs.Convert_Period_Type(
3944 p_bus_grp_id => p_bg_id,
3945 p_payroll_id => p_pay_id,
3946 p_asst_work_schedule => p_work_sched,
3947 p_asst_std_hours => p_std_hours,
3948 p_figure => v_flat_amount,
3949 p_from_freq => 'PERIOD',
3950 p_to_freq => 'HOURLY',
3951 p_period_start_date => v_range_start,
3952 p_period_end_date => v_range_end,
3953 p_asst_std_freq => p_std_freq);
3954 --
3955 END LOOP;
3956 CLOSE get_flat_amounts;
3957 --
3958 hr_utility.set_location('OT_Base_Rate', 100);
3959 v_ot_base_rate := v_ot_base_rate + (v_flat_total / v_flat_count);
3960 --
3961 ELSIF SUBSTR(v_ff_name,1,10) = 'PERCENTAGE' THEN
3962 hr_utility.set_location('OT_Base_Rate', 110);
3963 OPEN get_percentages;
3964 LOOP
3965 FETCH get_percentages
3966 INTO v_percentage;
3967 EXIT WHEN get_percentages%NOTFOUND;
3968 v_pct_count := v_pct_count + 1;
3969 --
3970 IF v_regsal_rate <> 0 THEN
3971 hr_utility.set_location('OT_Base_Rate', 105);
3972 v_pct_total := v_percentage * v_regsal_rate;
3973 END IF;
3974 --
3975 END LOOP;
3976 --
3977 CLOSE get_percentages;
3978 --
3979 hr_utility.set_location('OT_Base_Rate', 110);
3980 v_ot_base_rate := v_ot_base_rate + (v_pct_total / v_pct_count);
3981 --
3982 ELSIF SUBSTR(v_ff_name,1,12) = 'HOURS_X_RATE' THEN
3983 --
3984 -- Remember to look for "Rate Code" if necessary and "Multiple" always.
3985 --
3986 hr_utility.set_location('OT_Base_Rate', 115);
3987 OPEN get_rates;
3988 LOOP
3989 FETCH get_rates
3990 INTO v_rate, v_entry_id;
3991 EXIT WHEN get_rates%NOTFOUND;
3992 hr_utility.set_location('OT_Base_Rate', 120);
3993 v_rate_count := v_rate_count + 1;
3994 IF v_rate = 0 THEN
3995 hr_utility.set_location('OT_Base_Rate', 125);
3996 SELECT NVL(EEV.screen_entry_value, 'NOT ENTERED')
3997 INTO v_rate_rcode
3998 FROM pay_element_entry_values_f EEV,
3999 pay_element_entries_f ELE,
4000 pay_element_types_f ELT,
4001 pay_input_values_f IPV
4002 WHERE ELE.assignment_id = p_ass_id
4003 AND ELE.element_entry_id = EEV.element_entry_id
4004 AND p_date_earned BETWEEN EEV.effective_start_date
4005 AND EEV.effective_end_date
4006 AND EEV.element_entry_id = v_entry_id
4007 AND EEV.input_value_id = IPV.input_value_id
4008 AND UPPER(ELT.element_name) = UPPER(v_ele_name)
4009 AND ELT.element_type_id = IPV.element_type_id
4010 AND UPPER(IPV.name) = 'RATE CODE';
4011 --
4012 IF v_rate_rcode <> 'NOT ENTERED' THEN
4013 hr_utility.set_location('OT_Base_Rate', 130);
4014 v_rate := FND_NUMBER.CANONICAL_TO_NUMBER(hruserdt.get_table_value(
4015 p_bg_id,
4016 c_rate_table_name,
4017 c_rate_table_column,
4018 v_rate_rcode));
4019 END IF;
4020 --
4021 END IF;
4022 -- Now get "Multiple" on this entry, if any.
4023 IF v_rate <> 0 THEN
4024 hr_utility.set_location('OT_Base_Rate', 135);
4025 SELECT COUNT(0)
4026 INTO v_rate_mult_count
4027 FROM pay_element_entry_values_f EEV,
4028 pay_element_entries_f ELE,
4029 pay_element_types_f ELT,
4030 pay_input_values_f IPV
4031 WHERE ELE.assignment_id = p_ass_id
4032 AND ELE.element_entry_id = EEV.element_entry_id
4033 AND p_date_earned BETWEEN EEV.effective_start_date
4034 AND EEV.effective_end_date
4035 AND EEV.element_entry_id = v_entry_id
4036 AND EEV.input_value_id = IPV.input_value_id
4037 AND UPPER(ELT.element_name) = UPPER(v_ele_name)
4038 AND ELT.element_type_id = IPV.element_type_id
4039 AND UPPER(IPV.name) = 'MULTIPLE';
4040 --
4041 IF v_rate_mult_count <> 0 THEN
4042 hr_utility.set_location('OT_Base_Rate', 140);
4043 SELECT NVL(fnd_number.canonical_to_number(EEV.screen_entry_value), 0)
4044 INTO v_rate_multiple
4045 FROM pay_element_entry_values_f EEV,
4046 pay_element_entries_f ELE,
4047 pay_element_types_f ELT,
4048 pay_input_values_f IPV
4049 WHERE ELE.assignment_id = p_ass_id
4050 AND ELE.element_entry_id = EEV.element_entry_id
4051 AND p_date_earned BETWEEN EEV.effective_start_date
4052 AND EEV.effective_end_date
4053 AND EEV.element_entry_id = v_entry_id
4054 AND EEV.input_value_id = IPV.input_value_id
4055 AND UPPER(ELT.element_name) = UPPER(v_ele_name)
4056 AND ELT.element_type_id = IPV.element_type_id
4057 AND UPPER(IPV.name) = 'MULTIPLE';
4058 --
4059 IF v_rate_multiple <> 0 THEN
4060 v_rate := v_rate * v_rate_multiple;
4061 END IF;
4062 --
4063 END IF;
4064 --
4065 END IF;
4066 --
4067 v_rate_total := v_rate_total + v_rate;
4068 --
4069 END LOOP;
4070 CLOSE get_rates;
4071 --
4072 v_ot_base_rate := v_ot_base_rate + (v_rate_total / v_rate_count);
4073 --
4074 ELSIF SUBSTR(v_ff_name,1,8) = 'GROSS_UP' THEN
4075 hr_utility.set_location('OT_Base_Rate', 150);
4076 OPEN get_grosses;
4077 LOOP
4078 FETCH get_grosses
4079 INTO v_gross_results;
4080 EXIT WHEN get_grosses%NOTFOUND;
4081 v_gross_count := v_gross_count + 1;
4082 IF v_gross_results <> 0 THEN
4083 -- Convert gross result to hourly figure.
4084 hr_utility.set_location('OT_Base_Rate', 160);
4085
4086 v_gross_total := v_gross_total + hr_us_ff_udfs.Convert_Period_Type(
4087 p_bus_grp_id => p_bg_id,
4088 p_payroll_id => p_pay_id,
4089 p_asst_work_schedule => p_work_sched,
4090 p_asst_std_hours => p_std_hours,
4091 p_figure => v_gross_amount,
4092 p_from_freq => NULL,
4093 p_to_freq => 'HOURLY',
4094 p_period_start_date => v_range_start,
4095 p_period_end_date => v_range_end,
4096 p_asst_std_freq => p_std_freq);
4097 --
4098 END IF;
4099 --
4100 END LOOP;
4101 CLOSE get_grosses;
4102 --
4103 v_ot_base_rate := v_ot_base_rate + (v_gross_total / v_gross_count);
4104 --
4105 END IF; -- Calc Method
4106 --
4107 END LOOP;
4108 --
4109 CLOSE get_include_in_ot;
4110 --
4111 RETURN v_ot_base_rate;
4112 --
4113 EXCEPTION
4114 WHEN NO_DATA_FOUND THEN
4115 hr_utility.set_location('OT_Base_Rate', 170);
4116 RETURN v_ot_base_rate;
4117 -- hr_utility.set_message(801, 'PAY_SCL_SEG_NOTFOUND');
4118 -- hr_utility.raise_error;
4119 --
4120 END OT_Base_Rate;
4121 --
4122 /*
4123 *****************************************************
4124 FUNCTION NAME:
4125 *****************************************************
4126 Dedn_Freq_Factor
4127 Inputs: p_payroll_id -- Context
4128 p_element_type_id -- Context
4129 p_date_earned -- Context
4130 p_ele_period_type -- DBI param
4131
4132 Outputs: v_dedn_freq_factor
4133
4134 28 JAN 1994 hparicha Created.
4135
4136 *****************************************************
4137 DESCRIPTION:
4138
4139 This fun computes the "Deduction Frequency Factor" for deductions
4140 that have frequency rules and/or processing period type.
4141
4142 Algorithm:
4143
4144 IF period type IS NULL and frequency rules DO NOT exist THEN
4145
4146 dedn_freq_factor = 1 -- The deduction is assumed to be for the pay period.
4147
4148 ELSIF period type IS NULL and frequency rules EXIST THEN
4149
4150 dedn_freq_factor = 1 / (# pay periods in reset period)
4151
4152 ELSIF period type IS NOT NULL and frequency rules DO NOT exist THEN
4153
4154 dedn_freq_factor = (# per FY, eletype period type) /
4155 (# per FY, payroll period type)
4156
4157 ELSIF period type IS NOT NULL and frequency rules EXIST THEN
4158
4159 dedn_freq_factor = (# per FY, eletype period type) /
4160 (# per FY, reset period type) /
4161 (# pay periods in reset period)
4162
4163 END IF
4164 -- NOTE: "Reset Period" is either Month or Year.
4165 "# pay periods in reset period" means the number of deduction
4166 frequency rules for the ele/payroll that exist AND have a
4167 PERIOD_NO_IN_RESET_PERIOD less than the number of pay periods
4168 that will actually process.
4169 For example: if 2 bi-week pay periods will process in a month and the
4170 frequency rules say to process in the 1st and 3rd bi-wks of the month,
4171 then we need to process the deduction "in full" on the 1st run of the
4172 month. PERIOD_NO_IN_RESET_PERIOD = 1 and 3, so only one of these
4173 records has a column value less than the # pay periods that will
4174 actually process this month.
4175 --
4176 IF payroll period type is one of:
4177 Bi-Week,
4178 Calendar Month,
4179 Lunar Month,
4180 Semi-Month,
4181 Week
4182 THEN "reset period" = Month, number per fiscal year = 12.
4183 --
4184 IF payroll period type is one of:
4185 Bi-Month,
4186 Quarter,
4187 Semi-Year,
4188 Year
4189 THEN "reset period" = Year, number per fiscal year = 1.
4190
4191 *****************************************************
4192 fun TEXT:
4193 *****************************************************
4194 */
4195 FUNCTION Dedn_Freq_Factor (
4196 p_payroll_id in NUMBER,
4197 p_element_type_id in NUMBER,
4198 p_date_earned in DATE,
4199 p_ele_period_type in VARCHAR2)
4200 RETURN NUMBER IS
4201 --
4202 -- local constants
4203 --
4204 c_months_per_fy NUMBER(2) ;
4205 c_years_per_fy NUMBER(1) ;
4206 --
4207 -- local vars
4208 --
4209 v_date_earned DATE;
4210 v_dedn_freq_factor NUMBER(11,5);
4211 v_ele_period_num_per_fy NUMBER(3);
4212 v_pay_period_num_per_fy NUMBER(3);
4213 v_reset_periods_per_fy NUMBER(3);
4214 v_pay_periods_in_reset NUMBER(3);
4215 v_pay_periods_in_month NUMBER(3);
4216 v_pay_periods_in_year NUMBER(3);
4217 v_freq_rule_count NUMBER(3);
4218 v_freq_rules_exist VARCHAR2(1);
4219 v_pay_period_type VARCHAR2(30);
4220 --
4221 --
4222 BEGIN -- Dedn_Freq_Factor
4223 --
4224 -- v_date_earned := p_date_earned;
4225 --
4226 /* Init */
4227 c_months_per_fy := 12;
4228 c_years_per_fy := 1;
4229
4230
4231 hr_utility.set_location('Dedn_Freq_Factor', 10);
4232 SELECT DECODE(COUNT(FRP.freq_rule_period_id), 0, 'N', 'Y')
4233 INTO v_freq_rules_exist
4234 FROM pay_freq_rule_periods FRP,
4235 pay_ele_payroll_freq_rules EPF
4236 WHERE FRP.ele_payroll_freq_rule_id = EPF.ele_payroll_freq_rule_id
4237 AND EPF.element_type_id = p_element_type_id
4238 AND EPF.payroll_id = p_payroll_id
4239 AND EPF.start_date <= p_date_earned;
4240 --
4241 IF p_ele_period_type = 'NOT ENTERED' THEN
4242 -- AND v_freq_rules_exist = 'N' (I say if ele period type is null, then
4243 -- dedn freq factor is 1 whether freq rules exist or not! Right, the
4244 -- freq rule will tell us WHEN to process the given Deduction amount.
4245 -- If there is no period type on the Dedn, then we take the FULL AMOUNT
4246 -- every time it is processed - according to freq rule.)
4247 --
4248 v_dedn_freq_factor := 1;
4249 --
4250 ELSIF p_ele_period_type = 'NOT ENTERED' AND v_freq_rules_exist = 'Y' THEN
4251 --
4252 hr_utility.set_location('Dedn_Freq_Factor', 15);
4253 SELECT TPT.number_per_fiscal_year
4254 INTO v_pay_period_num_per_fy
4255 FROM pay_payrolls_f PPF,
4256 per_time_period_types TPT
4257 WHERE TPT.period_type = PPF.period_type
4258 AND p_date_earned BETWEEN PPF.effective_start_date
4259 AND PPF.effective_end_date
4260 AND PPF.payroll_id = p_payroll_id;
4261 --
4262 IF v_pay_period_num_per_fy >= 12 THEN
4263 IF v_pay_period_num_per_fy = 12 THEN
4264 v_dedn_freq_factor := 1;
4265 ELSE
4266 hr_utility.set_location('Dedn_Freq_Factor', 20);
4267 SELECT COUNT(0)
4268 INTO v_pay_periods_in_month
4269 FROM per_time_periods PTP
4270 WHERE PTP.end_date
4271 BETWEEN TRUNC(p_date_earned, 'MONTH')
4272 AND LAST_DAY(p_date_earned)
4273 AND PTP.payroll_id = p_payroll_id;
4274 hr_utility.set_location('v_pay_periods_in_month', v_pay_periods_in_month);
4275 --
4276 -- Frequency rules exist, so this select should never return 0.
4277 -- Just in case, we'll decode for 0 and set v_pay_periods_in_reset to 1.
4278 -- ie. so v_dedn_freq_factor will also equal 1.
4279 --
4280 hr_utility.set_location('Dedn_Freq_Factor', 25);
4281 SELECT DECODE(COUNT(0), 0, 1, COUNT(0))
4282 INTO v_pay_periods_in_reset
4283 FROM pay_ele_payroll_freq_rules EPF,
4284 pay_freq_rule_periods FRP
4285 WHERE FRP.period_no_in_reset_period <= v_pay_periods_in_month
4286 AND FRP.ele_payroll_freq_rule_id = EPF.ele_payroll_freq_rule_id
4287 AND EPF.payroll_id = p_payroll_id
4288 AND EPF.element_type_id = p_element_type_id;
4289 --
4290 hr_utility.set_location('v_pay_periods_in_reset = ', v_pay_periods_in_reset);
4291 v_dedn_freq_factor := 1 / v_pay_periods_in_reset;
4292 --
4293 END IF;
4294 ELSE
4295 hr_utility.set_location('Dedn_Freq_Factor', 30);
4296 SELECT COUNT(0)
4297 INTO v_pay_periods_in_year
4298 FROM per_time_periods PTP
4299 WHERE PTP.end_date
4300 BETWEEN TRUNC(p_date_earned, 'YEAR')
4301 AND LAST_DAY(ADD_MONTHS(TRUNC(p_date_earned, 'YEAR'), 11))
4302 AND PTP.payroll_id = p_payroll_id;
4303 --
4304 -- Frequency rules exist, so this select should never return 0.
4305 -- Just in case, we'll decode for 0 and set v_pay_periods_in_reset to 1.
4306 -- ie. so v_dedn_freq_factor will also equal 1.
4307 --
4308 hr_utility.set_location('Dedn_Freq_Factor', 35);
4309 SELECT DECODE(COUNT(0), 0, 1, COUNT(0))
4310 INTO v_pay_periods_in_reset
4311 FROM pay_ele_payroll_freq_rules EPF,
4312 pay_freq_rule_periods FRP
4313 WHERE FRP.period_no_in_reset_period <= v_pay_periods_in_year
4314 AND FRP.ele_payroll_freq_rule_id = EPF.ele_payroll_freq_rule_id
4315 AND EPF.payroll_id = p_payroll_id
4316 AND EPF.element_type_id = p_element_type_id;
4317 --
4318 hr_utility.set_location('v_pay_periods_in_reset = ', v_pay_periods_in_reset);
4319 v_dedn_freq_factor := 1 / v_pay_periods_in_reset;
4320 --
4321 END IF;
4322 --
4323 ELSIF p_ele_period_type <> 'NOT ENTERED' AND v_freq_rules_exist = 'N' THEN
4324 --
4325 hr_utility.set_location('Dedn_Freq_Factor', 40);
4326 SELECT number_per_fiscal_year
4327 INTO v_ele_period_num_per_fy
4328 FROM per_time_period_types TPT
4329 WHERE UPPER(period_type) = UPPER(p_ele_period_type);
4330 --
4331 hr_utility.set_location('Dedn_Freq_Factor', 45);
4332 SELECT TPT.number_per_fiscal_year
4333 INTO v_pay_period_num_per_fy
4334 FROM per_time_period_types TPT,
4335 pay_payrolls_f PPF
4336 WHERE TPT.period_type = PPF.period_type
4337 AND p_date_earned BETWEEN PPF.effective_start_date
4338 AND PPF.effective_end_date
4339 AND PPF.payroll_id = p_payroll_id;
4340 --
4341 v_dedn_freq_factor := v_ele_period_num_per_fy / v_pay_period_num_per_fy;
4342 --
4343 ELSIF p_ele_period_type <> 'NOT ENTERED' AND v_freq_rules_exist = 'Y' THEN
4344 --
4345 hr_utility.set_location('Dedn_Freq_Factor', 50);
4346 SELECT number_per_fiscal_year
4347 INTO v_ele_period_num_per_fy
4348 FROM per_time_period_types TPT
4349 WHERE UPPER(period_type) = UPPER(p_ele_period_type);
4350 --
4351 hr_utility.set_location('Dedn_Freq_Factor', 55);
4352 SELECT TPT.number_per_fiscal_year
4353 INTO v_pay_period_num_per_fy
4354 FROM pay_payrolls_f PPF,
4355 per_time_period_types TPT
4356 WHERE TPT.period_type = PPF.period_type
4357 AND PPF.payroll_id = p_payroll_id
4358 AND p_date_earned BETWEEN PPF.effective_start_date
4359 AND PPF.effective_end_date;
4360 --
4361 IF v_pay_period_num_per_fy >= 12 THEN
4362 hr_utility.set_location('Dedn_Freq_Factor', 60);
4363 SELECT COUNT(0)
4364 INTO v_pay_periods_in_month
4365 FROM per_time_periods PTP
4366 WHERE PTP.end_date
4367 BETWEEN TRUNC(p_date_earned, 'MONTH')
4368 AND LAST_DAY(p_date_earned)
4369 AND PTP.payroll_id = p_payroll_id;
4370 --
4371 -- Frequency rules exist, so this select should never return 0.
4372 -- Just in case, we'll decode for 0 and set v_pay_periods_in_reset to 1.
4373 -- ie. so v_dedn_freq_factor will also equal 1.
4374 --
4375 hr_utility.set_location('Dedn_Freq_Factor', 65);
4376 SELECT COUNT(0)
4377 INTO v_pay_periods_in_reset
4378 FROM pay_ele_payroll_freq_rules EPF,
4379 pay_freq_rule_periods FRP
4380 WHERE FRP.period_no_in_reset_period <= v_pay_periods_in_month
4381 AND FRP.ele_payroll_freq_rule_id = EPF.ele_payroll_freq_rule_id
4382 AND EPF.payroll_id = p_payroll_id
4383 AND EPF.element_type_id = p_element_type_id;
4384 hr_utility.set_location('v_pay_periods_in_reset = ', v_pay_periods_in_reset);
4385 --
4386 IF v_ele_period_num_per_fy = v_pay_period_num_per_fy THEN
4387 v_dedn_freq_factor := 1;
4388 ELSIF v_pay_periods_in_reset = 0 THEN
4389 v_dedn_freq_factor := 0;
4390 -- Freq rules exist, but will not be processed enough this reset period.
4391 -- Ie. freq rule says process in 3rd bi-wk when only 2 will process in
4392 -- the current month, so NOTHING is taken for deduction (factor = 0).
4393 ELSE
4394 v_dedn_freq_factor := v_ele_period_num_per_fy / c_months_per_fy / v_pay_periods_in_reset;
4395 END IF;
4396 --
4397 ELSE
4398 hr_utility.set_location('Dedn_Freq_Factor', 70);
4399 SELECT COUNT(0)
4400 INTO v_pay_periods_in_year
4401 FROM per_time_periods PTP
4402 WHERE PTP.end_date
4403 BETWEEN TRUNC(p_date_earned, 'YEAR')
4404 AND LAST_DAY(ADD_MONTHS(TRUNC(p_date_earned, 'YEAR'), 11))
4405 AND PTP.payroll_id = p_payroll_id;
4406 --
4407 -- Frequency rules exist, so this select should never return 0.
4408 -- Just in case, we'll decode for 0 and set v_pay_periods_in_reset to 1.
4409 -- ie. so v_dedn_freq_factor will also equal 1.
4410 --
4411 hr_utility.set_location('Dedn_Freq_Factor', 75);
4412 SELECT DECODE(COUNT(0), 0, 1, COUNT(0))
4413 INTO v_pay_periods_in_reset
4414 FROM pay_ele_payroll_freq_rules EPF,
4415 pay_freq_rule_periods FRP
4416 WHERE FRP.period_no_in_reset_period <= v_pay_periods_in_year
4417 AND FRP.ele_payroll_freq_rule_id = EPF.ele_payroll_freq_rule_id
4418 AND EPF.payroll_id = p_payroll_id
4419 AND EPF.element_type_id = p_element_type_id;
4420 --
4421 hr_utility.set_location('v_pay_periods_in_reset = ', v_pay_periods_in_reset);
4422 IF v_ele_period_num_per_fy = v_pay_period_num_per_fy THEN
4423 v_dedn_freq_factor := 1;
4424 ELSE
4425 v_dedn_freq_factor := v_ele_period_num_per_fy / c_months_per_fy / v_pay_periods_in_reset;
4426 END IF;
4427 --
4428 END IF;
4429 --
4430 END IF;
4431 --
4432 hr_utility.set_location('Dedn_Freq_Factor', 80);
4433 RETURN v_dedn_freq_factor;
4434 --
4435 END Dedn_Freq_Factor;
4436 --
4437 FUNCTION Arrearage ( p_eletype_id IN NUMBER,
4438 p_date_earned IN DATE,
4439 p_assignment_id IN NUMBER,
4440 p_ele_entry_id IN NUMBER,
4441 p_partial_flag IN VARCHAR2,
4442 p_net_asg_run IN NUMBER,
4443 p_arrears_itd IN NUMBER,
4444 p_guaranteed_net IN NUMBER,
4445 p_dedn_amt IN NUMBER,
4446 p_to_arrears IN OUT nocopy NUMBER,
4447 p_not_taken IN OUT nocopy NUMBER)
4448 RETURN NUMBER IS
4449 --
4450 -- Call from fast formulae as:
4451 -- dedn_amt = arrearage ( <ELE_NAME>_PARTIAL_EE_CONTRIBUTIONS,
4452 -- NET_ASG_RUN,
4453 -- <ELE_NAME>_ARREARS_ASG_GRE_ITD,
4454 -- Guaranteed_Net,
4455 -- dedn_amt,
4456 -- to_arrears,
4457 -- not_taken)
4458 --
4459 -- Test cases need to be run where:
4460 -- 1. p_net_asg_run > p_guaranteed_net
4461 -- 2. p_net_asg_run < p_guaranteed_net
4462 -- 3. p_net_asg_run = 0
4463
4464 l_total_dedn NUMBER(27,7); -- local var
4465 l_dedn_amt NUMBER(27,7); -- local var
4466 v_dedn_multiple NUMBER(9);
4467 v_arrears_flag VARCHAR2(1);
4468 v_shadow_ele_name VARCHAR2(80);
4469 v_shadow_ele_id NUMBER(9);
4470 v_bg_id NUMBER(9);
4471 l_arr_eletype_id number ;
4472 l_arrear_contr_value number;
4473 l_rule_mode varchar2(5);
4474 l_retro_arr_eletype_id number ;
4475
4476 --
4477 BEGIN
4478 --
4479 p_to_arrears := 0;
4480 p_not_taken := 0;
4481 l_arrear_contr_value := 0 ;
4482
4483 hr_utility.set_location('hr_us_ff_udfs.arrearage', 1);
4484
4485 -- Determine if Arrears = 'Y' for this dedn
4486 -- Can do this by checking for "Clear Arrears" input value on base ele.
4487 -- This input value is only created when Arrears is marked Yes on Deductions
4488 -- screen.
4489
4490 begin
4491
4492 hr_utility.set_location('Shadow elename = '||v_shadow_ele_name, 38 );
4493
4494 select 'Y'
4495 into v_arrears_flag
4496 from pay_input_values_f ipv
4497 where ipv.name = 'Clear Arrears'
4498 and p_date_earned BETWEEN ipv.effective_start_date
4499 AND ipv.effective_end_date
4500 and ipv.element_type_id = p_eletype_id;
4501
4502 exception
4503
4504 WHEN NO_DATA_FOUND THEN
4505 hr_utility.set_location('Arrearage is NOT ON for this ele.', 99);
4506 v_arrears_flag := 'N';
4507
4508 WHEN TOO_MANY_ROWS THEN
4509 hr_utility.set_location('Too many rows returned for Clear Arrears inpval.', 99);
4510 v_arrears_flag := 'N';
4511
4512 end;
4513
4514 IF v_arrears_flag = 'N' THEN
4515
4516 IF p_net_asg_run - p_dedn_amt >= p_guaranteed_net THEN
4517
4518 p_to_arrears := 0;
4519 p_not_taken := 0;
4520 l_dedn_amt := p_dedn_amt;
4521 -- hr_utility.set_location('pyusudfs.arrearage.to_arrears = ', p_to_arrears);
4522 hr_utility.set_location('pyusudfs.arrearage.dedn_amt = ', p_dedn_amt);
4523 -- hr_utility.set_location('pyusudfs.arrearage.not_taken = ', p_not_taken);
4524
4525 ELSIF p_net_asg_run <= p_guaranteed_net THEN
4526 -- Don't take anything, no arrears contr either.
4527 p_to_arrears := 0;
4528 p_not_taken := p_dedn_amt;
4529 l_dedn_amt := 0;
4530 -- hr_utility.set_location('pyusudfs.arrearage.to_arrears = ', p_to_arrears);
4531 hr_utility.set_location('pyusudfs.arrearage.dedn_amt = ', l_dedn_amt);
4532 -- hr_utility.set_location('pyusudfs.arrearage.not_taken = ', p_not_taken);
4533
4534 ELSIF p_net_asg_run - p_dedn_amt < p_guaranteed_net THEN
4535
4536 IF p_partial_flag = 'Y' THEN
4537 --
4538 p_to_arrears := 0;
4539 p_not_taken := p_dedn_amt - (p_net_asg_run - p_guaranteed_net);
4540 /* 6319565 */
4541 IF p_net_asg_run < 0 THEN
4542 l_dedn_amt := 0 ;
4543 ELSE
4544 l_dedn_amt := p_net_asg_run - p_guaranteed_net;
4545 END IF;
4546 -- hr_utility.set_location('pyusudfs.arrearage.to_arrears = ', p_to_arrears);
4547 -- hr_utility.set_location('pyusudfs.arrearage.not_taken = ', p_not_taken);
4548 hr_utility.set_location('pyusudfs.arrearage.dedn_amt = ', l_dedn_amt);
4549
4550 ELSE
4551
4552 p_to_arrears := 0;
4553 p_not_taken := p_dedn_amt;
4554 l_dedn_amt := 0;
4555 -- hr_utility.set_location('pyusudfs.arrearage.to_arrears = ', p_to_arrears);
4556 -- hr_utility.set_location('pyusudfs.arrearage.not_taken = ', p_not_taken);
4557 hr_utility.set_location('pyusudfs.arrearage.dedn_amt = ', l_dedn_amt);
4558
4559 END IF;
4560
4561 END IF;
4562
4563 ELSE -- Arrearage is on, try and clear any balance currently in arrears.
4564
4565 /* Changes done for bug # 6970340 starts */
4566 begin
4567 select to_number(nvl(element_information19,0)) into l_arr_eletype_id /*Element Type id of Spl features */
4568 from pay_element_types_f
4569 where element_type_id = p_eletype_id ;
4570 exception when others then null ;
4571 end;
4572
4573 begin
4574 select nvl(rule_mode,'N') into l_rule_mode from pay_legislation_rules where
4575 rule_type = 'ADVANCED_RETRO' AND
4576 legislation_code = 'US';
4577 exception when others then null ;
4578 end ;
4579
4580 if l_rule_mode = 'Y' then
4581 begin
4582
4583 /* select pes.retro_element_type_id into l_retro_arr_eletype_id
4584 from pay_retro_component_usages rcu , pay_element_span_usages pes
4585 where rcu.creator_id = l_arr_eletype_id
4586 and rcu.creator_type = 'ET'
4587 and rcu.retro_component_usage_id = pes.retro_component_usage_id
4588 and pes.legislation_code is null ; */
4589
4590 select (sum(to_number(nvl(screen_entry_value,'0')))) into l_arrear_contr_value
4591 from PAY_ELEMENT_ENTRY_VALUES_F eev , PAY_INPUT_VALUES_F ip
4592 where eev.input_value_id = ip.input_value_id
4593 and ip.name like 'Arrears Contr'
4594 and element_entry_id in ( select element_entry_id -- element entry for arrear contr
4595 from pay_element_entries_f
4596 where assignment_id = p_assignment_id
4597 and p_date_earned between effective_start_date and effective_end_date
4598 and element_type_id = l_arr_eletype_id );
4599
4600
4601 exception when others then null ;
4602 end ;
4603 end if ;
4604
4605
4606 l_arrear_contr_value := nvl(l_arrear_contr_value ,0);
4607
4608
4609 /* Changes done for bug # 6970340 ends */
4610
4611 IF p_net_asg_run <= p_guaranteed_net THEN
4612
4613 -- Don't take anything, put it all in arrears.
4614 p_to_arrears := p_dedn_amt + l_arrear_contr_value ;
4615 p_not_taken := p_dedn_amt + l_arrear_contr_value ;
4616 l_dedn_amt := 0;
4617 -- hr_utility.set_location('pyusudfs.arrearage.to_arrears = ', p_to_arrears);
4618 hr_utility.set_location('pyusudfs.arrearage.dedn_amt = ', l_dedn_amt);
4619 -- hr_utility.set_location('pyusudfs.arrearage.not_taken = ', p_not_taken);
4620
4621 ELSE
4622
4623 l_total_dedn := p_dedn_amt + p_arrears_itd + l_arrear_contr_value ;
4624
4625 -- Attempt to clear any arrears bal:
4626
4627 IF p_net_asg_run - p_guaranteed_net >= l_total_dedn THEN
4628
4629 -- there's enough net to take it all, clear arrears:
4630 p_to_arrears := -1 * ( p_arrears_itd + l_arrear_contr_value ) ;
4631 l_dedn_amt := l_total_dedn;
4632 p_not_taken := 0;
4633 -- hr_utility.set_location('pyusudfs.arrearage.to_arrears = ', p_to_arrears);
4634 hr_utility.set_location('pyusudfs.arrearage.dedn_amt = ', l_dedn_amt);
4635 -- hr_utility.set_location('pyusudfs.arrearage.not_taken = ', p_not_taken);
4636
4637 /* Deleted a load of code above to fix 504970. If partial_flag = Y, then
4638 try and take as much of the total deduction amount (current dedn +
4639 arrears) and leave the rest in arrears. */
4640
4641 ELSIF p_partial_flag = 'Y' THEN
4642
4643 -- Going into arrears, not enough Net to take curr p_dedn_amt
4644 --
4645 p_to_arrears := (l_total_dedn - l_arrear_contr_value - (p_net_asg_run - p_guaranteed_net)) +
4646 (-1 * (p_arrears_itd + l_arrear_contr_value )) ;
4647 IF (p_net_asg_run - p_guaranteed_net + l_arrear_contr_value ) >= p_dedn_amt THEN
4648 p_not_taken := 0;
4649 ELSE
4650 p_not_taken := p_dedn_amt - (p_net_asg_run - p_guaranteed_net + l_arrear_contr_value);
4651 END IF;
4652 /* 6319565 */
4653 IF p_net_asg_run < 0 THEN
4654 l_dedn_amt := 0 ;
4655 ELSE
4656 l_dedn_amt := p_net_asg_run - p_guaranteed_net + l_arrear_contr_value ;
4657 END IF;
4658
4659 -- hr_utility.set_location('pyusudfs.arrearage.to_arrears = ', p_to_arrears);
4660 hr_utility.set_location('pyusudfs.arrearage.dedn_amt = ', l_dedn_amt);
4661 -- hr_utility.set_location('pyusudfs.arrearage.not_taken = ', p_not_taken);
4662
4663 ELSE -- p_partial_flag = 'N'
4664 IF (p_net_asg_run - p_guaranteed_net + l_arrear_contr_value ) >= p_dedn_amt THEN
4665 -- Take the whole deduction amount.
4666 l_dedn_amt := p_dedn_amt;
4667 p_to_arrears := l_arrear_contr_value;
4668 p_not_taken := l_arrear_contr_value ;
4669 ELSE
4670 -- Don't take anything, partial dedn = 'N'
4671 p_to_arrears := p_dedn_amt ;
4672 p_not_taken := p_dedn_amt ;
4673 l_dedn_amt := 0;
4674 END IF;
4675 -- hr_utility.set_location('pyusudfs.arrearage.to_arrears = ', p_to_arrears);
4676 hr_utility.set_location('pyusudfs.arrearage.dedn_amt = ', l_dedn_amt);
4677 -- hr_utility.set_location('pyusudfs.arrearage.not_taken = ', p_not_taken);
4678
4679 END IF;
4680
4681 END IF;
4682
4683 END IF;
4684 --
4685 -- p_to_arrears and p_not_taken are set and sent out as well.
4686 --
4687 RETURN l_dedn_amt;
4688 --
4689 END Arrearage;
4690 --
4691 -- G1668: Addr_Val optimization. We tune for the majority case of city/zip
4692 -- uniquely identifying a geocode and handle the exception cases
4693 -- as appropriate. The exceptions will be raise VERY RARELY.
4694 -- Part of the optimization assumes that the code which calls this fn
4695 -- verify that city/zip params are populated before making the call
4696 -- which essentially means city/zip are required params for optimal
4697 -- performance. In the event city/zip are not supplied, this fun
4698 -- still works.
4699 --
4700 -- Optimization issues:
4701 -- 1. In order to get the BEST performance possible, we need to add a "mode"
4702 -- parameter in order to this fun so that majority cases can be checked
4703 -- in the optimal order. The high volume users of this fn are MIX batch
4704 -- val and the payroll run (VERTEX formulae). Since we KNOW that MIX will
4705 -- only provide State and City params, we can quickly check this and return
4706 -- without going thru any of the gyrations needed for more general cases.
4707 -- The validation required for the VERTEX formulae will be the "general"
4708 -- case, tuned to succeed in the shortest possible time.
4709 -- Resolution: Make all params mandatory, make the calling modules take care
4710 -- of this requirement.
4711 --
4712 FUNCTION addr_val ( p_state_abbrev IN VARCHAR2,
4713 p_county_name IN VARCHAR2,
4714 p_city_name IN VARCHAR2,
4715 p_zip_code IN VARCHAR2)
4716 RETURN VARCHAR2 IS
4717 --
4718 l_geocode VARCHAR2(11); -- Output var in "12-345-6789" format.
4719 --
4720 BEGIN -- Call main addr_val
4721
4722 l_geocode := addr_val(p_state_abbrev,
4723 p_county_name,
4724 p_city_name,
4725 p_zip_code,
4726 'N');
4727
4728 RETURN l_geocode;
4729 --
4730 EXCEPTION
4731 WHEN OTHERS THEN
4732 hr_utility.set_location('hr_us_ff_udfs.addr_val', 20);
4733 l_geocode := '00-000-0000';
4734 RETURN l_geocode;
4735 --
4736 END addr_val; -- addr_val
4737
4738 FUNCTION addr_val ( p_state_abbrev IN VARCHAR2,
4739 p_county_name IN VARCHAR2,
4740 p_city_name IN VARCHAR2,
4741 p_zip_code IN VARCHAR2,
4742 p_skip_rule IN VARCHAR2 )
4743 RETURN VARCHAR2 IS
4744 --
4745 l_geocode VARCHAR2(11); -- Output var in "12-345-6789" format.
4746 l_state_code VARCHAR2(2);
4747 l_state_name VARCHAR2(25);
4748 l_county_code VARCHAR2(3);
4749 l_county_name VARCHAR2(20);
4750 l_city_code VARCHAR2(4);
4751 l_city_name VARCHAR2(30); --bug 4652178
4752 --l_city_name VARCHAR2(25);
4753 l_zip_code VARCHAR2(5);
4754 l_query_city_name VARCHAR2(30); --bug 4652178
4755 --l_query_city_name VARCHAR2(25);
4756
4757 --
4758 BEGIN -- Main addr_val
4759 -- rmonge commented out this code.
4760 -- See new code below to handle JEDD also.
4761 /*
4762 l_zip_code := substr(p_zip_code, 1, 5);
4763 -- Checking the name of the city in the P_CITY_NAME is and air force base
4764 -- IE: The last three characters are 'AFB' then we will not INITCAP the
4765 -- cityname.
4766 IF upper(ltrim(rtrim(substr(rtrim(p_city_name),length(rtrim(p_city_name)) - 3,
4767 length(rtrim(p_city_name)) )))) = 'AFB' THEN
4768 l_query_city_name := p_city_name;
4769 ELSE
4770 l_query_city_name := INITCAP(p_city_name);
4771 END IF;
4772 */
4773
4774 l_zip_code := substr(p_zip_code, 1, 5);
4775 -- Checking the name of the city in the P_CITY_NAME is and air force base
4776 -- IE: The last three characters are 'AFB' then we will not INITCAP the
4777 -- cityname.
4778
4779 /* Start - VMKULKAR Bug 5985902
4780 IF upper(ltrim(rtrim(substr(rtrim(p_city_name),length(rtrim(p_city_name)) - 3,
4781 length(rtrim(p_city_name)) )))) = 'AFB' OR
4782 upper(ltrim(rtrim(substr(rtrim(p_city_name),
4783 length(rtrim(p_city_name)) - 4,
4784 length(rtrim(p_city_name)) )))) = 'JEDD' THEN
4785 l_query_city_name := p_city_name;
4786 ELSE
4787 l_query_city_name := INITCAP(p_city_name);
4788 END IF;
4789 End - VMKULKAR Bug 5985902 */
4790 IF instr(p_city_name,' JEDD') > 0
4791 OR instr(p_city_name,' AFB') > 0
4792 OR instr(p_city_name,' JEDZ-I') > 0
4793 OR instr(p_city_name,' JEDZ') > 0 THEN /*bug 10074813 */
4794 l_query_city_name := p_city_name;
4795 ELSE
4796 l_query_city_name := INITCAP(p_city_name);
4797 END IF;
4798
4799 --
4800 begin -- (1)
4801 --
4802 begin -- (2)
4803 -- We're going 3-deep here in order to handle multiple raising
4804 -- of the same exception...will this work?
4805 -- 90% case, geo determined by city/zip combo:
4806
4807 IF (p_skip_rule = 'Y') THEN
4808 RAISE TOO_MANY_ROWS;
4809 END IF;
4810
4811 hr_utility.set_location('hr_us_ff_udfs.addr_val', 1);
4812
4813 IF (nvl(hr_general2.get_oracle_db_version, 0) < 9.0) THEN
4814 SELECT /*+ ORDERED */ a.state_code||'-'||a.county_code||'-'||a.city_code
4815 INTO l_geocode
4816 FROM pay_us_city_names a,
4817 pay_us_zip_codes z
4818 WHERE a.city_name = l_query_city_name
4819 AND z.state_code = a.state_code AND
4820 z.county_code = a.county_code AND
4821 z.city_code = a.city_code AND
4822 l_zip_code BETWEEN z.zip_start AND z.zip_end;
4823 ELSE
4824 -- Bug# 5343679.
4825 -- Removed the ORDERED hint for the Bug# 5629688.
4826 SELECT a.state_code||'-'||a.county_code||'-'||a.city_code
4827 INTO l_geocode
4828 FROM pay_us_city_names a,
4829 pay_us_zip_codes z
4830 WHERE a.city_name = l_query_city_name
4831 AND z.state_code = a.state_code AND
4832 z.county_code = a.county_code AND
4833 z.city_code = a.city_code AND
4834 l_zip_code BETWEEN z.zip_start AND z.zip_end;
4835 END IF;
4836
4837 --
4838 EXCEPTION -- (2)
4839 --
4840 WHEN NO_DATA_FOUND THEN -- Invalid city/zip combo
4841 hr_utility.set_location('hr_us_ff_udfs.addr_val', 3);
4842 l_geocode := '00-000-0000';
4843 RETURN l_geocode;
4844 --
4845
4846 WHEN TOO_MANY_ROWS THEN -- city/zip does not uniquely defn geo
4847 -- same county name can exists in many states
4848 SELECT state_code
4849 INTO l_state_code
4850 FROM pay_us_states
4851 WHERE state_abbrev = p_state_abbrev;
4852
4853 hr_utility.set_location('hr_us_ff_udfs.addr_val', 5);
4854 SELECT a.state_code||'-'||a.county_code||'-'||a.city_code
4855 INTO l_geocode
4856 FROM pay_us_zip_codes z,
4857 pay_us_city_names a,
4858 pay_us_counties b
4859 WHERE a.city_name = l_query_city_name
4860 AND a.county_code = b.county_code
4861 AND UPPER(b.county_name) = UPPER(p_county_name) --Bug3783309-Changed Initcap to Upper.
4862 AND b.state_code = l_state_code
4863 AND z.state_code = a.state_code AND
4864 z.county_code = a.county_code AND
4865 z.city_code = a.city_code AND
4866 l_zip_code BETWEEN z.zip_start||'' AND z.zip_end||'' ; --Bug 4868637
4867 --
4868 end; -- (2)
4869 --
4870 EXCEPTION -- (1)
4871 --
4872 -- Fallout from (2) ie. county/city/zip combo invalid or does not
4873 -- uniquely define geocode.
4874 WHEN NO_DATA_FOUND THEN
4875 hr_utility.set_location('hr_us_ff_udfs.addr_val', 7);
4876 l_geocode := '00-000-0000';
4877 RETURN l_geocode;
4878 --
4879 WHEN TOO_MANY_ROWS THEN
4880 hr_utility.set_location('hr_us_ff_udfs.addr_val', 9);
4881 SELECT a.state_code||'-'||a.county_code||'-'||a.city_code
4882 INTO l_geocode
4883 FROM pay_us_zip_codes z,
4884 pay_us_city_names a,
4885 pay_us_counties b,
4886 pay_us_states c
4887 WHERE c.state_code = a.state_code AND
4888 c.state_abbrev = UPPER(p_state_abbrev)
4889 AND
4890 UPPER(b.county_name) = UPPER(p_county_name)AND --Bug3783309-Changed Initcap to Upper.
4891 b.state_code = c.state_code
4892 AND
4893 a.city_name = l_query_city_name AND
4894 a.state_code = c.state_code AND
4895 a.county_code = b.county_code
4896 AND
4897 z.state_code = c.state_code AND
4898 z.county_code = b.county_code AND
4899 z.city_code = a.city_code AND
4900 l_zip_code BETWEEN z.zip_start AND z.zip_end;
4901 --
4902 end; -- (1)
4903 --
4904 -- We're in Main
4905 --
4906 hr_utility.set_location('hr_us_ff_udfs.addr_val', 11);
4907 --
4908 if (substr(l_geocode,8,1) = 'U') THEN
4909 l_geocode := substr(l_geocode,1,7)||'0000';
4910 END IF;
4911 --
4912 RETURN l_geocode;
4913 --
4914 EXCEPTION -- Main addr_val
4915 -- Fallout from (1) state/county/city/zip does not uniquely define a geo.
4916 -- Return failure geocode.
4917 WHEN NO_DATA_FOUND THEN
4918 hr_utility.set_location('hr_us_ff_udfs.addr_val', 13);
4919 l_geocode := '00-000-0000';
4920 RETURN l_geocode;
4921 --
4922 WHEN TOO_MANY_ROWS THEN
4923 hr_utility.set_location('hr_us_ff_udfs.addr_val', 15);
4924 l_geocode := '00-000-0000';
4925 RETURN l_geocode;
4926 --
4927 END addr_val; -- Main addr_val
4928
4929 --createed for returning the country name for
4930 -- courty code in Fast formula of mag. W2
4931 -- with record type A,B,E
4932 -- it returns the value in upper case
4933
4934 FUNCTION PAY_US_COUNTRY (p_territory_code IN varchar2)
4935 RETURN varchar2 IS
4936 r_territory_short_name fnd_territories_tl.territory_short_name%type;
4937 BEGIN
4938
4939 hr_utility.set_location('Pay us country territory code : ', p_territory_code);
4940 IF p_territory_code IS NOT NULL THEN
4941
4942
4943 SELECT territory_short_name
4944 INTO r_territory_short_name
4945 FROM FND_TERRITORIES_VL
4946 WHERE territory_code = p_territory_code;
4947
4948 RETURN upper(r_territory_short_name);
4949 ELSE
4950
4951 RETURN(NULL);
4952
4953 END IF;
4954
4955 --
4956 -- EXCEPTION handling routine
4957 --
4958 EXCEPTION
4959 WHEN others THEN
4960 RETURN NULL ;
4961 END; -- pay_us_country
4962
4963
4964 FUNCTION catchup_type_details ( p_assignment_action_id IN NUMBER,
4965 p_plan IN NUMBER,
4966 p_base_element_name IN VARCHAR2,
4967 p_catchup_type IN OUT NOCOPY VARCHAR2,
4968 p_mode IN VARCHAR2
4969 )
4970 RETURN VARCHAR2 IS
4971
4972 BEGIN
4973
4974 hr_utility.trace('Entering hr_us_ff_udfs.catchup_type_details in '||p_mode||' Mode');
4975 hr_utility.trace('p_plan : '||p_plan);
4976 hr_utility.trace('p_assignment_action_id : '||p_assignment_action_id);
4977 hr_utility.trace('p_base_element_name : '||p_base_element_name);
4978 hr_utility.trace('p_catchup_type : '||p_catchup_type);
4979
4980 IF p_mode = 'IN' THEN
4981
4982 IF p_plan = 403 THEN
4983
4984 catchup_type_table(p_assignment_action_id).base_element_403 := p_base_element_name;
4985 catchup_type_table(p_assignment_action_id).catchup_type_403 := p_catchup_type;
4986 catchup_type_table(p_assignment_action_id).catchup_403 := 'Y';
4987
4988 hr_utility.trace('catchup_type_details: Stored 403 Catchup Type as '||catchup_type_table(p_assignment_action_id).catchup_type_403);
4989
4990 ELSIF p_plan = 457 THEN
4991
4992 catchup_type_table(p_assignment_action_id).base_element_457 := p_base_element_name;
4993 catchup_type_table(p_assignment_action_id).catchup_type_457 := p_catchup_type;
4994 catchup_type_table(p_assignment_action_id).catchup_457 := 'Y';
4995
4996 hr_utility.trace('catchup_type_details: Stored 457 Catchup Type as '||catchup_type_table(p_assignment_action_id).catchup_type_457);
4997
4998 END IF;
4999
5000 END IF; /* p_mode = 'IN' IF */
5001
5002 IF p_mode = 'OUT' THEN
5003
5004 IF catchup_type_table.EXISTS(p_assignment_action_id) THEN
5005
5006 hr_utility.trace('catchup_type_details: Record found for given assignment_action_id');
5007
5008 IF p_plan = 403
5009 AND catchup_type_table(p_assignment_action_id).catchup_403 = 'Y'
5010 AND catchup_type_table(p_assignment_action_id).base_element_403 = p_base_element_name
5011 THEN
5012
5013 p_catchup_type := catchup_type_table(p_assignment_action_id).catchup_type_403;
5014 hr_utility.trace('catchup_type_details: Found 403 Catchup Type as '||p_catchup_type);
5015
5016 IF nvl(catchup_type_table(p_assignment_action_id).catchup_457,'N') <> 'Y' THEN
5017 /*Delete the record if 457 details are not present */
5018 catchup_type_table.DELETE(p_assignment_action_id);
5019
5020 ELSE
5021
5022 catchup_type_table(p_assignment_action_id).catchup_403 := 'N';
5023
5024 END IF;
5025
5026
5027 ELSIF p_plan = 457
5028 AND catchup_type_table(p_assignment_action_id).catchup_457 = 'Y'
5029 AND catchup_type_table(p_assignment_action_id).base_element_457 = p_base_element_name
5030 THEN
5031
5032 p_catchup_type := catchup_type_table(p_assignment_action_id).catchup_type_457;
5033 hr_utility.trace('catchup_type_details: Found 457 Catchup Type as '||p_catchup_type);
5034
5035 IF nvl(catchup_type_table(p_assignment_action_id).catchup_403,'N') <> 'Y' THEN
5036 /*Delete the record if 403 details are not present */
5037 catchup_type_table.DELETE(p_assignment_action_id);
5038
5039 ELSE
5040
5041 catchup_type_table(p_assignment_action_id).catchup_457 := 'N';
5042
5043 END IF;
5044
5045 ELSE
5046
5047 p_catchup_type := 'NOT ENTERED';
5048
5049 END IF;
5050
5051 ELSE
5052
5053 p_catchup_type := 'NOT ENTERED';
5054
5055 END IF; /*catchup_type_table.EXISTS IF*/
5056
5057 END IF; /* p_mode = 'OUT' IF */
5058
5059 hr_utility.trace('Leaving hr_us_ff_udfs.catchup_type_details successfully');
5060
5061 RETURN 'Y';
5062
5063 EXCEPTION
5064 WHEN others THEN
5065 hr_utility.trace('Exception raised in hr_us_ff_udfs.catchup_type_details');
5066 RETURN NULL ;
5067 END catchup_type_details;
5068
5069 --
5070
5071 /*Function created for Bug#13614766*/
5072
5073 FUNCTION direct_fed_data_archived ( p_reporting_year IN VARCHAR2 )
5074 RETURN VARCHAR2 IS
5075
5076 CURSOR check_archive_data IS
5077 SELECT 'Y'
5078 FROM PAY_PAYROLL_ACTIONS PPA,
5079 PAY_ASSIGNMENT_ACTIONS PAA,
5080 FF_ARCHIVE_ITEMS FAI,
5081 FF_DATABASE_ITEMS FDI
5082 WHERE PPA.REPORT_TYPE = 'YREND'
5083 AND TO_CHAR(PPA.EFFECTIVE_DATE,'YYYY') = p_reporting_year
5084 AND PPA.PAYROLL_ACTION_ID = PAA.PAYROLL_ACTION_ID
5085 AND PAA.ASSIGNMENT_ACTION_ID= FAI.CONTEXT1
5086 AND FAI.USER_ENTITY_ID = FDI.USER_ENTITY_ID
5087 AND FDI.USER_NAME IN
5088 ('A_FIT_SUBJ_WHABLE_PER_GRE_YTD',
5089 'A_FIT_SUBJ_NWHABLE_PER_GRE_YTD',
5090 'A_FIT_PRE_TAX_REDNS_PER_GRE_YTD')
5091 AND ROWNUM = 1;
5092
5093 /* Added for Bug#14385437 */
5094 l_return varchar2(2) := 'N';
5095 l_direct_fed_bal_starting_year varchar2(4) := '0001';
5096 /* End of changes for Bug#14385437 */
5097
5098 BEGIN
5099
5100 /*Added for Bug#14385437 */
5101
5102 hr_utility.trace('Entering hr_us_ff_udfs.direct_fed_data_archived for year '||p_reporting_year);
5103
5104 l_direct_fed_bal_starting_year := NVL(fnd_profile.value('PAY_US_DIRECT_BALANCE_START_YEAR'),'0001');
5105
5106 hr_utility.trace('Starting year for Direct US Federal Balances :'||l_direct_fed_bal_starting_year);
5107
5108 IF l_direct_fed_bal_starting_year > p_reporting_year THEN
5109
5110 RETURN 'N';
5111
5112 END IF;
5113
5114 /* End of changes for Bug#14385437 */
5115
5116 OPEN check_archive_data;
5117 FETCH check_archive_data INTO l_return;
5118 IF check_archive_data%FOUND THEN
5119 l_return := 'Y';
5120 ELSE
5121 l_return := 'N';
5122 END IF;
5123 CLOSE check_archive_data;
5124
5125 hr_utility.trace('Leaving hr_us_ff_udfs.direct_fed_data_archived successfully');
5126
5127 RETURN l_return;
5128
5129 EXCEPTION
5130 WHEN others THEN
5131 hr_utility.trace('Exception raised in hr_us_ff_udfs.direct_fed_data_archived');
5132 RETURN NULL ;
5133 END direct_fed_data_archived;
5134
5135 /*End of changes for Bug#13614766* */
5136
5137 --
5138
5139 END hr_us_ff_udfs;