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