1 package body per_utility_functions as
2 /* $Header: peutlfnc.pkb 120.7.12020000.4 2013/01/16 18:07:52 srannama ship $ */
3 --
4 g_package varchar2(33) := ' per_utility_functions.'; -- Global package name
5 --
6
7 TYPE global_id_table is TABLE OF number INDEX BY BINARY_INTEGER;
8
9 g_element_entries global_id_table;
10 --
11 -- 3267975 >>
12 g_legcode_cache HR_ORGANIZATION_INFORMATION.org_information9%TYPE;
13 g_legcode_cached boolean := FALSE;
14 --
15 g_reset_pto_accruals varchar2(10) := 'FALSE';
16 g_reset_pto_cache boolean := false;
17 --
18 TYPE number_tbl is TABLE OF NUMBER INDEX BY BINARY_INTEGER;
19 TYPE prmValue_type_tbl is TABLE OF PAY_ACTION_PARAMETER_VALUES.parameter_value%TYPE
20 INDEX BY BINARY_INTEGER;
21 TYPE prmName_type_tbl is TABLE OF PAY_ACTION_PARAMETER_VALUES.parameter_name%TYPE
22 INDEX BY BINARY_INTEGER;
23 --
24 TYPE action_prm_cache_r is RECORD
25 (
26 parameter_name prmName_type_tbl,
27 parameter_value prmValue_type_tbl,
28 sz number
29 );
30
31 g_actionPrm_cache action_prm_cache_r;
32 g_actionPrm_cached boolean := FALSE;
33 --
34 --
35 TYPE varchar_80_tbl is TABLE OF VARCHAR2(80) INDEX BY BINARY_INTEGER;
36 --
37 TYPE event_group_cache_r is RECORD
38 (
39 event_group_id number_tbl,
40 event_group_name varchar_80_tbl,
41 business_group_id number_tbl,
42 legislation_code varchar_80_tbl,
43 sz number
44 );
45
46 g_events_cache event_group_cache_r;
47 g_events_cached boolean := FALSE;
48 --
49 --
50 /* =====================================================================
51 Name : Get_Payroll_Period
52 Purpose : To determine the payroll period spanning a given date and
53 to set global variables containg the start and end dates and the
54 period number
55 Returns : 0 if successful, 1 otherwise
56 ---------------------------------------------------------------------*/
57 function Get_Payroll_Period
58 (P_Payroll_ID IN Number
59 ,P_Date_In_Period IN Date) return number IS
60 --
61 l_proc varchar2(72) := g_package||'Get_Payroll_Period';
62 --
63 cursor csr_get_payroll_period is
64 select start_date
65 , end_date
66 , period_num
67 from per_time_periods
68 where payroll_id = P_Payroll_ID
69 and P_Date_In_Period between start_date and end_date;
70 --
71 l_start_date date;
72 l_end_date date;
73 l_period_number number;
74 --
75 l_error number;
76 --
77 begin
78 hr_utility.set_location(l_proc, 5);
79 --
80 open csr_get_payroll_period;
81 fetch csr_get_payroll_period into l_start_date,l_end_date,l_period_number;
82 if csr_get_payroll_period%notfound then
83 close csr_get_payroll_period;
84 hr_utility.set_location('Payroll Period not found '||l_proc, 10);
85 l_error := per_formula_functions.raise_error(800, 'HR_52798_PTO_PAYROLL_INVALID');
86 return 1;
87 end if;
88 close csr_get_payroll_period;
89 --
90 l_error := per_formula_functions.set_date
91 ('PAYROLL_PERIOD_START_DATE',l_start_date);
92 l_error := per_formula_functions.set_date
93 ('PAYROLL_PERIOD_END_DATE',l_end_date);
94 l_error := per_formula_functions.set_number
95 ('PAYROLL_PERIOD_NUMBER',l_period_number);
96 --
97 hr_utility.set_location(l_proc, 15);
98 return 0;
99 end Get_Payroll_Period;
100 --
101 /* =====================================================================
102 Name : Get_Accrual_Band
103 Purpose : To determine the accrual band that spans the specified number of
104 years and to set global variables containing the ANNUAL_RATE,
105 UPPER_LIMIT and CEILING values.
106 Returns : 0 if successful, 1 otherwise
107 ---------------------------------------------------------------------*/
108 function Get_Accrual_Band
109 (P_Plan_ID IN Number
110 ,P_Number_Of_Years IN Number) return number IS
111 --
112 l_proc varchar2(72) := g_package||'Get_Accrual_Band';
113 --
114 --
115 cursor csr_get_accrual_band is
116 select annual_rate
117 , upper_limit
118 , nvl(ceiling, 99999999)
119 , nvl(max_carry_over, 99999999)
120 from pay_accrual_bands
121 where P_Number_Of_Years >= lower_limit
122 and P_Number_Of_Years < upper_limit
123 and accrual_plan_id = P_Plan_ID;
124 --
125 l_annual_rate number;
126 l_upper_limit number;
127 l_ceiling number;
128 l_max_carry_over number;
129 --
130 l_error number;
131 --
132 begin
133 hr_utility.set_location(l_proc, 5);
134 l_annual_rate := 0;
135 l_upper_limit := 0;
136 l_ceiling := 0;
137 l_max_carry_over := 0;
138 --
139 open csr_get_accrual_band;
140 fetch csr_get_accrual_band into l_annual_rate,
141 l_upper_limit,
142 l_ceiling,
143 l_max_carry_over;
144 if csr_get_accrual_band%notfound then
145 hr_utility.set_location(l_proc, 10);
146 return 1;
147 end if;
148 close csr_get_accrual_band;
149 --
150 l_error := per_formula_functions.set_number('ANNUAL_RATE',l_annual_rate);
151 l_error := per_formula_functions.set_number('UPPER_LIMIT',l_upper_limit);
152 l_error := per_formula_functions.set_number('CEILING' ,l_ceiling);
153 l_error := per_formula_functions.set_number('MAX_CARRY_OVER',l_max_carry_over);
154 --
155 hr_utility.set_location(l_proc, 15);
156 return 0;
157 exception
158 when others then
159 hr_utility.set_location(l_proc, 20);
160 return 1;
161 end Get_Accrual_Band;
162 --
163 /* =====================================================================
164 Name : Get_Period_Dates
165 Purpose : To determine the start and end dates of a period of time that
166 spans a given date, which is of a given duration (e.g. Month) and
167 which is a mulitple of that duration from a given Start date
168 (e.g. 6 months on from 01/01/90)
169 The globals PERIOD_START_DATE and PERIOD_END_DATE are populated
170 Returns : 0 if successful, 1 otherwise
171 ---------------------------------------------------------------------*/
172 function Get_Period_Dates
173 (P_Date_In_Period IN Date
174 ,P_Period_Unit IN Varchar2
175 ,P_Base_Start_Date IN Date
176 ,P_Unit_Multiplier IN Number) RETURN Number IS
177 --
178 l_proc varchar2(72) := g_package||'Get_Period_Dates';
179 --
180 l_start_date date;
181 l_end_date date;
182 l_error number;
183 l_months number;
184 --
185 begin
186
187 hr_utility.set_location(l_proc, 5);
188
189 if P_Date_In_Period >= P_Base_Start_Date then
190 --
191 l_start_date := P_Base_Start_Date;
192 l_months := P_Unit_Multiplier;
193
194 while true loop
195 --
196 if p_period_unit = 'M' then
197 l_end_date := add_months(P_Base_Start_Date, l_months) - 1;
198 l_start_date := add_months(P_Base_Start_Date, (l_months - P_Unit_Multiplier));
199 l_months := l_months + P_Unit_Multiplier;
200 elsif p_period_unit = 'W' then
201 l_end_date := l_start_date + (7*p_unit_multiplier) - 1;
202 elsif p_period_unit = 'D' then
203 l_end_date := l_start_date + p_unit_multiplier - 1;
204 end if;
205 --
206 if P_Date_In_Period between l_start_date and l_end_date then
207 l_error := per_formula_functions.set_date
208 ('PERIOD_START_DATE',l_start_date);
209 l_error := per_formula_functions.set_date
210 ('PERIOD_END_DATE',l_end_date);
211 exit;
212 else
213 l_start_date := l_end_date + 1;
214 end if;
215 --
216 end loop;
217 --
218 else
219 --
220 l_end_date := P_Base_Start_Date - 1;
221 l_months := P_Unit_Multiplier * -1;
222
223 while true loop
224 --
225 if p_period_unit = 'M' then
226 l_end_date := add_months(P_Base_Start_Date, (l_months + P_Unit_Multiplier)) - 1;
227 l_start_date := add_months(P_Base_Start_Date, l_months);
228 l_months := l_months - P_Unit_Multiplier;
229 elsif p_period_unit = 'W' then
230 l_start_date := l_end_date - (7*p_unit_multiplier) + 1;
231 elsif p_period_unit = 'D' then
232 l_start_date := l_end_date - p_unit_multiplier + 1;
233 end if;
234 --
235 if P_Date_In_Period between l_start_date and l_end_date then
236 l_error := per_formula_functions.set_date
237 ('PERIOD_START_DATE',l_start_date);
238 l_error := per_formula_functions.set_date
239 ('PERIOD_END_DATE',l_end_date);
240 exit;
241 else
242 l_end_date := l_start_date - 1;
243 end if;
244 --
245 end loop;
246 --
247 end if;
248
249 hr_utility.set_location(l_proc, 10);
250 return 0;
251 exception
252 when others then
253 hr_utility.set_location(l_proc, 15);
254 return 1;
255 end Get_Period_Dates;
256
257 -- Fix for the bug 13935707
258
259 function Get_Period_Dates_hd
260 (P_Date_In_Period IN Date
261 ,P_Period_Unit IN Varchar2
262 ,P_Base_Start_Date IN Date
263 ,P_Unit_Multiplier IN Number) RETURN Number IS
264 --
265 l_proc varchar2(72) := g_package||'Get_Period_Dates';
266 --
267 l_start_date date;
268 l_end_date date;
269 l_error number;
270 l_months number;
271 l_hire_date date;
272 --
273 begin
274
275 hr_utility.set_location(l_proc, 5);
276
277 if P_Date_In_Period >= P_Base_Start_Date then
278 --
279 l_start_date := P_Base_Start_Date;
280 l_months := P_Unit_Multiplier;
281
282 while true loop
283 --
284 if p_period_unit = 'M' then
285 l_end_date := add_months(P_Base_Start_Date, l_months) - 1;
286 l_start_date := add_months(P_Base_Start_Date, (l_months - P_Unit_Multiplier));
287 l_months := l_months + P_Unit_Multiplier;
288 elsif p_period_unit = 'W' then
289 l_end_date := l_start_date + (7*p_unit_multiplier) - 1;
290 elsif p_period_unit = 'D' then
291 l_end_date := l_start_date + p_unit_multiplier - 1;
292 end if;
293 --
294 if P_Date_In_Period between l_start_date and l_end_date then
295
296 if to_char(l_start_date,'ddmm') = '2902' then
297 l_hire_date := per_formula_functions.get_date('HIRE_DATE_ANNIVERSARY');
298 if to_char(l_hire_date,'ddmm') <> '2902' then
299 l_start_date := l_start_date -1;
300 end if;
301
302 end if;
303 l_error := per_formula_functions.set_date
304 ('PERIOD_START_DATE',l_start_date);
305 l_error := per_formula_functions.set_date
306 ('PERIOD_END_DATE',l_end_date);
307 exit;
308 else
309 l_start_date := l_end_date + 1;
310 end if;
311 --
312 end loop;
313 --
314 else
315 --
316 l_end_date := P_Base_Start_Date - 1;
317 l_months := P_Unit_Multiplier * -1;
318
319 while true loop
320 --
321 if p_period_unit = 'M' then
322 l_end_date := add_months(P_Base_Start_Date, (l_months + P_Unit_Multiplier)) - 1;
323 l_start_date := add_months(P_Base_Start_Date, l_months);
324 l_months := l_months - P_Unit_Multiplier;
325 elsif p_period_unit = 'W' then
326 l_start_date := l_end_date - (7*p_unit_multiplier) + 1;
327 elsif p_period_unit = 'D' then
328 l_start_date := l_end_date - p_unit_multiplier + 1;
329 end if;
330 --
331 if P_Date_In_Period between l_start_date and l_end_date then
332 l_error := per_formula_functions.set_date
333 ('PERIOD_START_DATE',l_start_date);
334 l_error := per_formula_functions.set_date
335 ('PERIOD_END_DATE',l_end_date);
336 exit;
337 else
338 l_end_date := l_start_date - 1;
339 end if;
340 --
341 end loop;
342 --
343 end if;
344
345 hr_utility.set_location(l_proc, 10);
346 return 0;
347 exception
348 when others then
349 hr_utility.set_location(l_proc, 15);
350 return 1;
351 end Get_Period_Dates_hd;
352 --
353 /* =====================================================================
354 Name : Get_Assignment_Status
355 Purpose : To determine assignment status spanning a given date
356 The globals ASSIGNMENT_EFFECTIVE_SD, ASSIGNMENT_EFFECTIVE_ED and
357 ASSIGNMENT_SYSTEM_STATUS are populated
358 Returns : 0 if successful, 1 otherwise
359 ---------------------------------------------------------------------*/
360 function Get_Assignment_Status
361 (P_Assignment_ID IN Number
362 ,P_Effective_Date IN Date) return number IS
363 --
364 l_proc varchar2(72) := g_package||'Get_Assignment_Status';
365 --
366 l_effective_start_date date;
367 l_effective_end_date date;
368 l_per_system_status varchar2(30);
369 l_error number;
370 --
371 cursor csr_assignment_status IS
372 select a.effective_start_date
373 , a.effective_end_date
374 , b.per_system_status
375 from per_all_assignments_f a
376 , per_assignment_status_types b
377 where a.assignment_id = P_Assignment_ID
378 and a.assignment_status_type_id = b.assignment_status_type_id
379 and P_Effective_Date
380 between a.effective_start_date and a.effective_end_date;
381 --
382 begin
383 hr_utility.set_location(l_proc, 5);
384 open csr_assignment_status;
385 fetch csr_assignment_status into l_effective_start_date,
386 l_effective_end_date,
387 l_per_system_status;
388 if csr_assignment_status%notfound then
389 close csr_assignment_status;
390 --
391 hr_utility.set_location(l_proc, 10);
392 return 1;
393 end if;
394 close csr_assignment_status;
395 --
396 l_error := per_formula_functions.set_date
397 ('ASSIGNMENT_EFFECTIVE_SD',l_effective_start_date);
398 l_error := per_formula_functions.set_date
399 ('ASSIGNMENT_EFFECTIVE_ED',l_effective_end_date);
400 l_error := per_formula_functions.set_text
401 ('ASSIGNMENT_SYSTEM_STATUS',l_per_system_status);
402 --
403 hr_utility.set_location(l_proc, 10);
404 return 0;
405 end Get_Assignment_Status;
406 --
407 /* =====================================================================
408 Name : Calculate_Payroll_Periods
409 Purpose : Calculates number of periods in one year for the payroll
410 indicated by payroll_id
411 Returns : 0 if successful, 1 otherwise
412 ---------------------------------------------------------------------*/
413 function Calculate_Payroll_Periods
414 (P_Payroll_ID IN Number,
415 P_Calculation_Date IN Date) return number IS
416 --
417 l_proc varchar2(72) := g_package||'Calculate_Payroll_Periods';
418 l_periods number;
419 l_start_date date;
420 l_error number;
421 l_max_ed_cur_year date; -- bug 4956943
422 --
423 -- Bug 1574928
424 -- As bi-weekly, weekly and lunar months can have a variable number of periods
425 -- in a year, these must be calculated based on per_time_periods.
426 -- They are set to zero and counted later on.
427
428 cursor c_count_periods is
429 select count(*)
430 from per_time_periods ptp
431 where ptp.payroll_id = P_Payroll_ID
432 and ptp.end_date between
433 to_date('01/01/'||to_char(P_Calculation_Date, 'YYYY'), 'DD/MM/YYYY')
434 and to_date('31/12/' || to_char(P_Calculation_Date, 'YYYY'), 'DD/MM/YYYY');
435
436 cursor c_first_date is
437 select start_date
438 from per_time_periods
439 where payroll_id = p_payroll_id
440 and end_date = (select min(end_date)
441 from per_time_periods
442 where payroll_id = p_payroll_id
443 and to_char(end_date, 'YYYY') = to_char(p_calculation_date, 'YYYY'));
444
445 begin
446 --
447 hr_utility.set_location(l_proc, 5);
448
449 open c_count_periods;
450 fetch c_count_periods into l_periods;
451 close c_count_periods;
452
453 l_error := per_formula_functions.set_number
454 ('PAYROLL_YEAR_NUMBER_OF_PERIODS',l_periods);
455
456 hr_utility.set_location(l_proc, 10);
457 -- START bug 4956943
458 select max(end_date)
459 into l_max_ed_cur_year
460 from per_time_periods
461 where payroll_id = p_payroll_id
462 and to_char(end_date, 'YYYY') = to_char(p_calculation_date, 'YYYY');
463 hr_utility.trace(l_proc || ' ' || l_max_ed_cur_year);
464
465 if l_max_ed_cur_year < p_calculation_date then
466 hr_utility.set_location(l_proc, 100);
467 select min(start_date)
468 into l_start_date
469 from per_time_periods
470 where payroll_id = p_payroll_id
471 and end_date >= p_calculation_date;
472 else
473 -- END bug 4956943
474 open c_first_date;
475 fetch c_first_date into l_start_date;
476 close c_first_date;
477 end if;
478
479 l_error := per_formula_functions.set_date
480 ('PAYROLL_YEAR_FIRST_VALID_DATE', l_start_date);
481
482 hr_utility.set_location(l_proc, 15);
483
484 return 0;
485 --
486 end Calculate_Payroll_Periods;
487 --
488 /* =====================================================================
489 Name : Get_Start_Date
490 Purpose : Calculates the adjusted start date for accruals, by checking
491 for element entries attached to an accrual plan which have not
492 yet been processed in a payroll run.
493 Returns : Effective start date of payroll period.
494 ---------------------------------------------------------------------*/
495 function Get_Start_Date
496 (P_Assignment_ID IN Number,
497 P_Accrual_Plan_ID IN Number,
498 P_Assignment_Action_Id IN Number,
499 P_Accrual_Start_Date IN Date,
500 P_Turn_Of_Year_Date IN Date) return Date is
501
502 l_proc varchar2(72) := g_package||'Get_Start_Date';
503 l_date date;
504 l_balance_exists number;
505 l_payroll_id number;
506 l_result number;
507 /*
508 Changes done for Bug 3183291
509 ----------------------------
510 1. Moved pay_element_entries_f pee1 to second last
511 2. Driving the join to pay_net_calculation_rules through the
512 input_value_id (PAY_NET_CALCULATION_RULES_N3) instead of
513 ncr.accrual_plan_id (PAY_NET_CALCULATION_RULES_FK1) which is more selective
514 3. By disabling the Primary Key join to the table the query can be driven off the
515 element entry route which is more selective when driving through subsequent tables.
516 4. Added this predicate to help in the filtering
517 5. Very poor filter so disabled to drive of the source_id which is more selective
518 6. Same as above
519 7. Same as above
520 */
521 /* modified the following cursor to improve performance
522 cursor c_get_date is
523 select nvl(min(pee1.effective_start_date), P_Accrual_Start_Date)
524 from pay_element_links_f pel1,
525 pay_input_values_f piv,
526 pay_net_calculation_rules ncr,
527 pay_accrual_plans pap,
528 pay_element_links_f pel2,
529 pay_element_entries_f pee1, -- Change (1)
530 pay_element_entries_f pee2
531 where pee1.element_link_id = pel1.element_link_id
532 and pel1.element_type_id = piv.element_type_id
533 and piv.input_value_id = ncr.input_value_id
534 and ncr.accrual_plan_id + 0 = pap.accrual_plan_id -- Change (2)
535 and pap.accrual_plan_element_type_id = pel2.element_type_id
536 and pel2.element_link_id = pee2.element_link_id
537 and pee1.assignment_id = p_assignment_id
538 and pee2.assignment_id = p_assignment_id
539 and pap.accrual_plan_id + 0 = p_accrual_plan_id -- Change (3)
540 and pee1.effective_start_date <= p_accrual_start_date - 1 -- Change (4)
541 and pee1.effective_end_date between p_turn_of_year_date
542 and p_accrual_start_date - 1
543 and not exists (select 1
544 from pay_run_results prr
545 where prr.source_id = pee1.element_entry_id
546 and prr.element_type_id + 0 = pel1.element_type_id -- Change (5)
547 and prr.status in ('P', 'PA')
548 )
549 and not exists (select 1
550 from pay_run_results prr,
551 pay_run_result_values rrv
552 where prr.run_result_id = rrv.run_result_id
553 and prr.source_id = pee2.element_entry_id
554 and prr.element_type_id + 0 = pap.tagging_element_type_id -- Change (6)
555 and rrv.result_value = pee1.element_entry_id
556 );
557 */
558 -- fix for the bug 5645232
559 cursor c_get_date is
560 select /*+ index(pee1 PAY_ELEMENT_ENTRIES_F_N53) use_nl(ncr)*/
561 nvl(min(pee1.effective_start_date), P_Accrual_Start_Date)
562 from
563 pay_input_values_f piv,
564 pay_net_calculation_rules ncr,
565 pay_accrual_plans pap,
566 pay_element_entries_f pee1,
567 pay_element_entries_f pee2
568 where
569 pee1.element_type_id = piv.element_type_id
570 and piv.input_value_id = ncr.input_value_id
571 and ncr.accrual_plan_id +0 = pap.accrual_plan_id
572 and pap.accrual_plan_element_type_id = pee1.element_type_id
573 and pee1.element_type_id = pee2.element_type_id
574 and pee1.assignment_id = p_assignment_id
575 and pee2.assignment_id = p_assignment_id
576 and pap.accrual_plan_id = p_accrual_plan_id
577 and pee1.effective_start_date <= p_accrual_start_date
578 and pee1.effective_end_date between p_turn_of_year_date
579 and p_accrual_start_date
580 and not exists (select 1
581 from pay_run_results prr
582 where prr.source_id = pee1.element_entry_id
583 and prr.element_type_id + 0 = pee1.element_type_id -- fix new
584 and prr.status in ('P', 'PA')
585 )
586 and not exists (select 1
587 from pay_run_results prr,
588 pay_run_result_values rrv
589 where prr.run_result_id = rrv.run_result_id
590 and prr.source_id = pee2.element_entry_id
591 and prr.element_type_id + 0 = pap.tagging_element_type_id
592 and rrv.result_value = pee1.element_entry_id
593 );
594 -- end of bug 5645232
595 cursor c_check_balance_exists is
596 select 1
597 from pay_element_entries_f pee,
598 pay_element_links_f pel,
599 pay_accrual_plans pap
600 where pap.accrual_plan_id = p_accrual_plan_id
601 and pee.assignment_id = p_assignment_id
602 and pap.accrual_plan_element_type_id = pel.element_type_id
603 and pel.element_link_id = pee.element_link_id
604 and exists (select 1
605 from pay_run_results prr
606 where prr.source_id = pee.element_entry_id
607 and prr.element_type_id + 0 = pel.element_type_id -- Change (7)
608 and prr.status in ('P', 'PA')
609 );
610
611 begin
612 --
613 hr_utility.set_location(l_proc, 5);
614
615 /*
616 * First check that a latest balance actually exists. There will
617 * be no balance if no payrolls have yet been run with the new
618 * pto system. The balance DBI is set to 0 in this case, so we cannot
619 * detect the event with in a FF. In this circumstance, we just want to
620 * accrue for the entire plan term, and we set the return date accordingly.
621 * If there is a latest balance in existence, continue as normal.
622 */
623
624 open c_check_balance_exists;
625 fetch c_check_balance_exists into l_balance_exists;
626
627 if c_check_balance_exists%notfound then
628 --
629 close c_check_balance_exists;
630 return P_Turn_Of_Year_Date;
631 --
632 end if;
633
634 close c_check_balance_exists;
635
636 /*
637 * Now check for retrospective element entries
638 *
639 */
640
641 open c_get_date;
642 fetch c_get_date into l_date;
643 close c_get_date;
644
645 hr_utility.set_location(l_proc, 10);
646
647 return l_date;
648 --
649 end Get_Start_Date;
650 --
651 --
652
653 /* =====================================================================
654 Name : Get_Element_Entry
655 Purpose : Assigns value of element entry id context to a
656 global variable.
657 Returns : 1
658 ---------------------------------------------------------------------*/
659 function Get_Element_Entry
660 (P_Element_Entry_Id IN Number,
661 P_Assignment_ID IN Number,
662 P_Assignment_Action_Id IN Number) return Number is
663
664 /*
665 Changes done for Bug 3183291
666 ----------------------------
667 1. Moved pay_element_entries_f pee1 to second last
668 2. Driving the join to pay_net_calculation_rules through the
669 input_value_id (PAY_NET_CALCULATION_RULES_N3) instead of
670 ncr.accrual_plan_id (PAY_NET_CALCULATION_RULES_FK1) which is more selective
671 3. Very poor filter so disabled to drive of the source_id which is more selective
672 4. Same as above
673 5. Bulk collect is used to store the values into PL/SQL tables
674 */
675 /*
676 modified the cursor to improve performance
677 cursor c_get_element (p_entry_id number,
678 p_effective_date date) is
679 select distinct pee1.element_entry_id
680 from pay_element_links_f pel1,
681 pay_input_values_f piv,
682 pay_net_calculation_rules ncr,
683 pay_accrual_plans pap,
684 pay_element_links_f pel2,
685 pay_element_entries_f pee1, -- Change (1)
686 pay_element_entries_f pee2
687 where pee1.element_link_id = pel1.element_link_id
688 and pel1.element_type_id = piv.element_type_id
689 and piv.input_value_id = ncr.input_value_id
690 and ncr.accrual_plan_id + 0 = pap.accrual_plan_id -- Change (2)
691 and pap.accrual_plan_element_type_id = pel2.element_type_id
692 and pel2.element_link_id = pee2.element_link_id
693 and pee1.assignment_id = p_assignment_id +
694 decode (pel1.element_link_id, 0, 0, 0)
695 and pee2.assignment_id = p_assignment_id
696 and pee1.effective_end_date < p_effective_date
697 and pee2.element_entry_id = p_entry_id
698 and not exists (select 1
699 from pay_run_results prr
700 where prr.source_id = pee1.element_entry_id
701 and prr.element_type_id + 0 = pel1.element_type_id -- Change (3)
702 and prr.status in ('P', 'PA')
703 )
704 and not exists (select 1
705 from pay_run_results prr,
706 pay_run_result_values rrv
707 where prr.run_result_id = rrv.run_result_id
708 and prr.source_id = pee2.element_entry_id
709 and prr.element_type_id + 0 = pap.tagging_element_type_id -- Change (4)
710 and rrv.result_value = pee1.element_entry_id
711 );
712 */
713 -- fix for the bug 5645232
714 cursor c_get_element (p_entry_id number,
715 p_effective_date date) is
716 select /*+ index(pee1 PAY_ELEMENT_ENTRIES_F_N53)*/
717 distinct pee1.element_entry_id
718 from
719 pay_input_values_f piv,
720 pay_net_calculation_rules ncr,
721 pay_accrual_plans pap,
722 pay_element_entries_f pee1,
723 pay_element_entries_f pee2
724 where
725 pee1.element_type_id = piv.element_type_id
726 and piv.input_value_id = ncr.input_value_id
727 and ncr.accrual_plan_id +0 = pap.accrual_plan_id
728 and pap.accrual_plan_element_type_id = pee2.element_type_id
729 and pee1.element_type_id = pee2.element_type_id
730 and pee1.assignment_id = p_assignment_id
731 and pee2.assignment_id = p_assignment_id
732 and pee1.effective_end_date < p_effective_date
733 and pee2.element_entry_id = p_entry_id
734 and not exists (select 1
735 from pay_run_results prr
736 where prr.source_id = pee1.element_entry_id
737 and prr.element_type_id + 0 = pee1.element_type_id
738 and prr.status in ('P', 'PA')
739 )
740 and not exists (select 1
741 from pay_run_results prr,
742 pay_run_result_values rrv
743 where prr.run_result_id = rrv.run_result_id
744 and prr.source_id = pee2.element_entry_id
745 and prr.element_type_id + 0 = pap.tagging_element_type_id
746 and rrv.result_value = pee1.element_entry_id
747 );
748
749 -- end of bug 5645232
750 --
751 cursor c_get_date is
752 select ptp.start_date
753 from per_time_periods ptp,
754 pay_payroll_actions ppa,
755 pay_assignment_actions paa
756 where paa.payroll_action_id = ppa.payroll_action_id
757 and ppa.time_period_id = ptp.time_period_id
758 and paa.assignment_action_id = p_assignment_action_id;
759
760 -- Bug 3183291 -- Change (5)
761 --l_retro_entry_id number;
762 l_count number;
763 l_effective_date date;
764 l_limit natural := 100; -- Limiting the bulk collect, if not limited then bulk collect
765 -- returns entire rows for the condition, it may affect memory
766 l_prev_collect number := 0; -- Cumulative record count till previous fetch
767 l_curr_collect number := 0; -- Cumulative record count including the current fetch
768 l_diff_collect number := 0; -- To check that, whether the last fetch retrived any new
769 -- records, if not then to exit from the loop
770 g_element_entries1 global_id_table;
771 --
772 begin
773 --
774
775 open c_get_date;
776 fetch c_get_date into l_effective_date;
777 close c_get_date;
778
779 open c_get_element(p_element_entry_id, l_effective_date);
780 --
781 loop
782 --
783 -- Change (5)
784 fetch c_get_element bulk collect into g_element_entries1 limit l_limit;
785 --
786 l_prev_collect := l_curr_collect;
787 l_curr_collect := c_get_element%rowcount;
788 l_diff_collect := l_curr_collect - l_prev_collect;
789 --
790 if l_diff_collect > 0 then
791 --
792 for i in g_element_entries1.first..g_element_entries1.last loop
793 --
794 -- Setting the index
795 l_count := g_element_entries.count + 1;
796 -- Keeping the cumulated records into actual PL/SQL table
797 g_element_entries(l_count) := g_element_entries1(i);
798 --
799 end loop;
800 --
801 end if;
802 --
803 -- Exiting, if the present fetch is NOT returning any new rows
804 exit when (l_diff_collect = 0);
805 --
806 end loop;
807 --
808 close c_get_element;
809 --
810 return 1;
811 --
812 end Get_Element_Entry;
813 --
814 --
815 /* =====================================================================
816 Name : Get_Retro_Element
817 Purpose : Retrieves retrospective elements in order for them to be
818 tagged as processed.
819 Overloaded version of function for use where element_entry_id
820 context is unavailable.
821 Returns : Element Entry ID
822 ---------------------------------------------------------------------*/
823 function Get_Retro_Element return Number is
824
825 l_retro_entry_id number;
826 l_count number;
827
828 begin
829 --
830 l_count := g_element_entries.count;
831
832 if l_count > 0 then
833 --
834 l_retro_entry_id := g_element_entries(l_count);
835 g_element_entries.delete(l_count);
836 --
837 end if;
838
839 if l_retro_entry_id is null then
840 return -1;
841 else
842 return l_retro_entry_id;
843 end if;
844 --
845 end Get_Retro_Element;
846 --
847 /* =====================================================================
848 Name : Get_Net_Accrual
849 Purpose : Wrapper function for per_accrual_calc_functions.get_net_accrual.
850 Only returns accrued time figure.
851 Returns : 0 if successful, 1 otherwise
852 ---------------------------------------------------------------------*/
853 function Get_Net_Accrual
854 (P_Assignment_ID IN Number
855 ,P_Payroll_ID IN Number
856 ,P_Business_Group_ID IN Number
857 ,P_Assignment_Action_ID IN Number default null
858 ,P_Calculation_Date IN Date
859 ,P_Plan_ID IN Number
860 ,P_Accrual_Start_Date IN Date default null
861 ,P_Accrual_Latest_Balance IN Number default null) return number is
862
863 l_proc varchar2(72) := g_package||'Get_Net_Accrual';
864 l_start_date date;
865 l_end_date date;
866 l_accrual_start_date date;
867 l_accrual_end_date date;
868 l_accrual number;
869 l_net_entitlement number;
870
871 begin
872 --
873 hr_utility.set_location(l_proc, 5);
874
875 if P_Accrual_Start_Date = hr_api.g_eot then
876 -- The accrual start date database item returned null but Fast
877 -- Formula defaulted it to the end of time. Re-set it back to null.
878 l_accrual_start_date := null;
879 else
880 l_accrual_start_date := P_Accrual_Start_Date;
881 end if;
882
883 per_accrual_calc_functions.get_net_accrual(
884 P_Assignment_ID => P_Assignment_ID
885 ,P_Plan_ID => P_Plan_ID
886 ,P_Payroll_ID => P_Payroll_ID
887 ,P_Business_Group_ID => P_Business_Group_ID
888 ,P_Assignment_Action_ID => P_Assignment_Action_ID
889 ,P_Calculation_Date => P_Calculation_Date
890 ,P_Accrual_Start_Date => l_accrual_start_date
891 ,P_Accrual_Latest_Balance => P_Accrual_Latest_Balance
892 ,P_Calling_Point => 'SQL'
893 ,P_Start_Date => l_start_date
894 ,P_End_Date => l_end_date
895 ,P_Accrual_End_Date => l_accrual_end_date
896 ,P_Accrual => l_accrual
897 ,P_Net_Entitlement => l_net_entitlement);
898
899 hr_utility.set_location(l_proc, 10);
900
901 return round(nvl(l_net_entitlement, 0), 5);
902 --
903 end Get_Net_Accrual;
904 --
905 --
906 /* =====================================================================
907 Name : Calculate_Hours_Worked
908 Purpose : Calculates the total number of hours worked in a given date
909 range. Moved here to create global version as previously
910 only localised versions existed (Bug 2720878).
911 Returns : Number of hours
912 ---------------------------------------------------------------------*/
913 FUNCTION calculate_hours_worked(
914 p_std_hrs in NUMBER,
915 p_range_start in DATE,
916 p_range_end in DATE,
917 p_std_freq in VARCHAR2) RETURN NUMBER IS
918 --
919 c_wkdays_per_week NUMBER(5,2) := 5;
920 c_wkdays_per_month NUMBER(5,2) := 20;
921 c_wkdays_per_year NUMBER(5,2) := 250;
922
923 /* 353434, 368242 : Fixed number width for total hours */
924 v_total_hours NUMBER(15,7) := 0;
925 v_wrkday_hours NUMBER(15,7) := 0; -- std hrs/wk divided by 5 workdays/wk
926 v_curr_date DATE := NULL;
927 v_curr_day VARCHAR2(3) := NULL; -- 3 char abbrev for day of wk.
928 v_day_no NUMBER;
929 --
930 BEGIN -- calculate_hours_worked
931 --
932 -- Check for valid range
933 hr_utility.set_location('calculate_hours_worked', 5);
934 IF p_range_start > p_range_end THEN
935 hr_utility.set_location('calculate_hours_worked', 7);
936 RETURN v_total_hours;
937 -- hr_utility.set_message(801,'PAY_xxxx_INVALID_DATE_RANGE');
938 -- hr_utility.raise_error;
939 END IF;
940 --
941 --
942 IF UPPER(p_std_freq) = 'W' THEN
943 v_wrkday_hours := p_std_hrs / c_wkdays_per_week;
944 ELSIF UPPER(p_std_freq) = 'M' THEN
945 v_wrkday_hours := p_std_hrs / c_wkdays_per_month;
946 ELSIF UPPER(p_std_freq) = 'Y' THEN
947 v_wrkday_hours := p_std_hrs / c_wkdays_per_year;
948 ELSE
949 v_wrkday_hours := p_std_hrs;
950 END IF;
951 --
952 v_curr_date := p_range_start;
953
954 hr_utility.set_location('calculate_hours_worked', 10);
955
956 hr_utility.trace('p_range_start is'|| to_char(p_range_start));
957 hr_utility.trace('p_range_end is'|| to_char(p_range_end));
958 LOOP
959
960 v_day_no := TO_CHAR(v_curr_date, 'D');
961
962 hr_utility.set_location('calculate_hours_worked', 15);
963
964 IF v_day_no > 1 and v_day_no < 7 then
965
966 v_total_hours := v_total_hours + v_wrkday_hours;
967 hr_utility.set_location('calculate_hours_worked v_total_hours = ', v_total_hours);
968 END IF;
969 v_curr_date := v_curr_date + 1;
970 EXIT WHEN v_curr_date > p_range_end;
971 END LOOP;
972 --
973 hr_utility.set_location('v_total_hours is', to_number(v_total_hours));
974 RETURN v_total_hours;
975 --
976 END calculate_hours_worked;
977 --
978 function Get_Payroll_ID
979 (P_Asg_ID IN Number
980 ,P_Payroll_Id IN Number
981 ,P_Date_In_Period IN Date) return number IS
982 --
983 l_proc varchar2(72) := g_package||'Get_Payroll_ID';
984 --
985 cursor csr_get_payroll is
986 select payroll_id
987 from per_all_assignments_f
988 where assignment_id = P_asg_ID
989 and P_Date_In_Period between effective_start_date
990 and effective_end_date;
991 --
992 l_payroll_id number;
993 --
994 l_error number;
995 --
996 begin
997 hr_utility.set_location(l_proc, 5);
998 --
999 open csr_get_payroll;
1000 fetch csr_get_payroll into l_payroll_id;
1001 if csr_get_payroll%notfound then
1002 close csr_get_payroll;
1003 hr_utility.set_location('Payroll not found '||l_proc, 10);
1004 -- Since no payroll found for the assignment, using the context value
1005 l_payroll_id := P_Payroll_Id;
1006 elsif l_payroll_id is null then
1007 close csr_get_payroll;
1008 hr_utility.set_location('Payroll is null '||l_proc, 12);
1009 -- Since no payroll found for the assignment, using the context value
1010 l_payroll_id := P_Payroll_Id;
1011 end if;
1012 if csr_get_payroll%isopen then
1013 close csr_get_payroll;
1014 end if;
1015 --
1016 l_error := per_formula_functions.set_number
1017 ('LATEST_PAYROLL_ID',l_payroll_id);
1018 --
1019 hr_utility.set_location(l_proc, 15);
1020 return 0;
1021 --
1022 end Get_Payroll_ID;
1023 --
1024 --
1025 function Get_Payroll_Details
1026 (P_payroll_ID IN Number
1027 ,P_Date_In_Period IN Date) return number IS
1028 --
1029 l_proc varchar2(72) := g_package||'Get_Payroll_Details';
1030 --
1031 cursor csr_get_payroll_period is
1032 select start_date
1033 , end_date
1034 , period_num
1035 from per_time_periods
1036 where payroll_id = P_Payroll_ID
1037 and P_Date_In_Period between start_date and end_date;
1038 --
1039 l_start_date date;
1040 l_end_date date;
1041 l_period_number number;
1042 --
1043 l_error number;
1044 --
1045 begin
1046 hr_utility.set_location(l_proc, 5);
1047 --
1048 open csr_get_payroll_period;
1049 fetch csr_get_payroll_period into l_start_date,l_end_date,l_period_number;
1050 if csr_get_payroll_period%notfound then
1051 close csr_get_payroll_period;
1052 hr_utility.set_location('Payroll Period not found '||l_proc, 10);
1053 l_error := per_formula_functions.raise_error(800, 'HR_52798_PTO_PAYROLL_INVALID');
1054 return 1;
1055 end if;
1056 close csr_get_payroll_period;
1057 --
1058 l_error := per_formula_functions.set_date
1059 ('PAYROLL_PERIOD_START_DATE',l_start_date);
1060 l_error := per_formula_functions.set_date
1061 ('PAYROLL_PERIOD_END_DATE',l_end_date);
1062 l_error := per_formula_functions.set_number
1063 ('PAYROLL_PERIOD_NUMBER',l_period_number);
1064 --
1065 hr_utility.set_location(l_proc, 15);
1066 return 0;
1067 end Get_Payroll_Details;
1068 --
1069 --
1070 --
1071 -- 3267975 >>
1072 /* =====================================================================
1073 Name : cache_action_prms
1074 =====================================================================
1075 Purpose : Populates the PL/SQL table with the given parameter_name. If
1076 the table is already cached, the parameter is added.
1077 Returns : Nothing.
1078 ---------------------------------------------------------------------*/
1079 procedure cache_action_prms (p_prm_name in varchar2) is
1080
1081 cursor csr_get_parameter is
1082 select parameter_name
1083 ,parameter_value
1084 from pay_action_parameters
1085 where parameter_name = p_prm_name;
1086 --
1087 l_proc varchar2(80) := g_package||'cache_action_prms';
1088 --
1089 begin
1090 --
1091 hr_utility.set_location('Entering: '||l_proc,5);
1092
1093 if NOT g_actionPrm_cached then
1094 g_actionPrm_cache.sz := 0;
1095 end if;
1096 --
1097 for actionPrm_rec in csr_get_parameter loop
1098 --
1099 g_actionPrm_cache.sz := g_actionPrm_cache.sz + 1;
1100 g_actionPrm_cache.parameter_name(g_actionPrm_cache.sz) := actionPrm_rec.parameter_name;
1101 g_actionPrm_cache.parameter_value(g_actionPrm_cache.sz) := actionPrm_rec.parameter_value;
1102 --
1103 end loop;
1104 --
1105 g_actionPrm_cached := TRUE;
1106 --
1107 hr_utility.set_location('Leaving: '||l_proc,88);
1108 --
1109 end cache_action_prms;
1110 /* =====================================================================
1111 Name : Get Cache ActionPrm
1112 /* =====================================================================
1113 Purpose : Gets the event_id from a cached pl/sql table to prevent
1114 same reads of pay action parameters view.
1115 Returns : parameter value if found, null otherwise.
1116 ---------------------------------------------------------------------*/
1117 function get_cache_ActionPrm(p_prm_name in varchar2)
1118 return varchar2 is
1119
1120 --
1121 l_proc varchar2(80) := g_package||'get_cache_ActionPrm';
1122 --
1123 actionPrm_rec number;
1124 l_prm_value PAY_ACTION_PARAMETER_VALUES.parameter_value%TYPE;
1125
1126 begin
1127 --
1128 hr_utility.set_location('Entering: '||l_proc,5);
1129
1130 for actionPrm_rec in 1..g_actionPrm_cache.sz loop
1131
1132 if (g_actionPrm_cache.parameter_name(actionPrm_rec) = p_prm_name)
1133 then
1134 l_prm_value := g_actionPrm_cache.parameter_value(actionPrm_rec);
1135 end if;
1136
1137 end loop;
1138 --
1139 hr_utility.set_location('Leaving: '||l_proc,88);
1140 --
1141 return l_prm_value;
1142 --
1143 end get_cache_ActionPrm;
1144 --
1145 /* ========================================================================
1146 Name : Get Action Parameter
1147 ========================================================================
1148 Purpose : Gets the Action Parameter from a cached pl/sql table to prevent
1149 same table scans on pay_action_parameters.
1150 Returns : parameter value.
1151 -----------------------------------------------------------------------*/
1152 function get_action_parameter(p_prm_name in varchar2)
1153 return varchar2 is
1154
1155 l_prm_value PAY_ACTION_PARAMETER_VALUES.parameter_value%TYPE;
1156
1157 begin
1158 --
1159 if NOT g_actionPrm_cached then
1160 cache_action_prms (p_prm_name => p_prm_name);
1161 end if;
1162
1163 l_prm_value := get_cache_ActionPrm (p_prm_name);
1164
1165 return l_prm_value;
1166 --
1167 end get_action_parameter;
1168 -- <<
1169 -- 3267975 >>
1170 /* =====================================================================
1171 Name : Reset_PTO_Accruals
1172 =====================================================================
1173 Purpose : Determines whether the PTO accruals for an assignment
1174 should be recalculated from the beginning.
1175 This is based on RESET_PTO_ACCRUALS action parameter
1176 Returns : 'FALSE' or 'TRUE'
1177 ---------------------------------------------------------------------*/
1178 function Reset_PTO_Accruals return varchar2 is
1179
1180 --
1181 l_proc varchar2(72) := g_package||'Reset_PTO_Accruals';
1182 --
1183 l_result PAY_ACTION_PARAMETER_VALUES.parameter_value%TYPE := 'N';
1184 --
1185
1186 begin
1187 hr_utility.set_location('Entering: '||l_proc, 5);
1188
1189 if NOT g_reset_pto_cache then
1190 l_result := Get_Action_Parameter('RESET_PTO_ACCRUALS');
1191 if nvl(l_result,'N') = 'Y' then
1192 g_reset_pto_accruals := 'TRUE';
1193 else
1194 g_reset_pto_accruals := 'FALSE';
1195 end if;
1196 g_reset_pto_cache := true;
1197 end if;
1198 hr_utility.set_location('Leaving: '||l_proc||' => RESET = '||g_reset_pto_accruals, 88);
1199 return g_reset_pto_accruals;
1200
1201 end Reset_PTO_Accruals;
1202 -- <<
1203 --
1204 --
1205 -- 3267975 >>
1206 --
1207 /* =====================================================================
1208 Name : Get_Legislation
1209 =====================================================================
1210 Purpose : Retrieves the legislation code associated with
1211 business group.
1212 Returns : Legislation code.
1213 ---------------------------------------------------------------------*/
1214 function get_legislation (p_business_group_id number)
1215 return varchar2 is
1216
1217 cursor csr_getLegCode is
1218 select legislation_code
1219 from per_business_groups
1220 where organization_id = p_business_group_id;
1221 --
1222 l_proc varchar2(80) := g_package||'get_legislation';
1223 --
1224 l_legcode HR_ORGANIZATION_INFORMATION.org_information9%TYPE;
1225 --
1226 begin
1227 if NOT g_legcode_cached then
1228 open csr_getLegCode;
1229 fetch csr_getLegCode into l_legcode;
1230 close csr_getLegCode;
1231 --
1232 g_legcode_cache := l_legcode;
1233 g_legcode_cached := TRUE;
1234 --
1235 end if;
1236 return g_legcode_cache;
1237 end get_legislation;
1238 --
1239 /* =====================================================================
1240 Name : Cache Events
1241 =====================================================================
1242 Purpose : Populates the PL/SQL table with the given event_name. If
1243 the table is already cached, the event is added.
1244 Returns : Nothing.
1245 ---------------------------------------------------------------------*/
1246 procedure cache_events (p_event_name in varchar2) is
1247
1248 cursor csr_get_events is
1249 select e.event_group_id,
1250 e.event_group_name,
1251 e.business_group_id,
1252 e.legislation_code
1253 from pay_event_groups e
1254 where e.event_group_name = p_event_name;
1255 --
1256 l_proc varchar2(80) := g_package||'cache_events';
1257 --
1258 begin
1259 --
1260
1261 if NOT g_events_cached then
1262 g_events_cache.sz := 0;
1263 end if;
1264 --
1265 for events_rec in csr_get_events loop
1266 --
1267 g_events_cache.sz := g_events_cache.sz + 1;
1268 g_events_cache.event_group_id(g_events_cache.sz) := events_rec.event_group_id;
1269 g_events_cache.event_group_name(g_events_cache.sz) := events_rec.event_group_name;
1270 g_events_cache.business_group_id(g_events_cache.sz) := events_rec.business_group_id;
1271 g_events_cache.legislation_code(g_events_cache.sz) := events_rec.legislation_code;
1272 --
1273 end loop;
1274 --
1275 g_events_cached := TRUE;
1276 --
1277 end cache_events;
1278 /* =====================================================================
1279 Name : Get Cache Event
1280 /* =====================================================================
1281 Purpose : Gets the event_id from a cached pl/sql table to prevent
1282 same reads of PEM tables for each person in the
1283 payroll run.
1284 Returns : event_id if found, otherwise 0.
1285 ---------------------------------------------------------------------*/
1286 function get_cache_event(p_event_name in varchar2,
1287 p_business_group_id in number,
1288 p_legislation_code in varchar2)
1289 return number is
1290
1291 event_rec number;
1292 l_event_id number := 0;
1293
1294 begin
1295 --
1296
1297 for event_rec in 1..g_events_cache.sz loop
1298
1299 if (g_events_cache.event_group_name(event_rec) = p_event_name)
1300 and (nvl(g_events_cache.business_group_id(event_rec)
1301 , nvl(p_business_group_id,-1)) = nvl(p_business_group_id,-1))
1302 and (nvl(g_events_cache.legislation_code(event_rec)
1303 , nvl(p_legislation_code,'X')) = nvl(p_legislation_code,'X'))
1304 then
1305 l_event_id := g_events_cache.event_group_id(event_rec);
1306 end if;
1307
1308 end loop;
1309
1310 return l_event_id;
1311 -- This will be zero if the event is not in the cached events
1312 --
1313 end get_cache_event;
1314 --
1315 /* ========================================================================
1316 Name : Get Event
1317 ========================================================================
1318 Purpose : Gets the event_group_id from a cached pl/sql table to prevent
1319 same table scans on pay_event_groups for each person in the
1320 payroll run.
1321 Returns : event_id if found, otherwise null.
1322 -----------------------------------------------------------------------*/
1323 function get_event(p_event_name in varchar2,
1324 p_business_group_id in number,
1325 p_legislation_code in varchar2)
1326 return number is
1327
1328 l_event_id number;
1329
1330 begin
1331 --
1332 if NOT g_events_cached then
1333 cache_events (p_event_name => p_event_name);
1334 end if;
1335
1336 l_event_id := get_cache_event (
1337 p_event_name => p_event_name,
1338 p_business_group_id => p_business_group_id,
1339 p_legislation_code => p_legislation_code
1340 );
1341
1342 return l_event_id;
1343 -- This will be zero if event does not exist
1344 --
1345 end get_event;
1346 --
1347 FUNCTION GET_PER_TERMINATION_DATE
1348 (P_Assignment_id IN Number) return NUMBER IS
1349 --
1350 l_proc varchar2(72) := g_package||'get_payroll_dtrange';
1351 --
1352 cursor csr_get_per_term_date is
1353 select max(actual_termination_date)
1354 FROM per_all_assignments_f asg,
1355 per_periods_of_service pps
1356 where asg.assignment_id = P_Assignment_id
1357 and asg.period_of_service_id = pps.period_of_service_id;
1358 --changes for bug 5749588 starts here
1359 cursor csr_get_Asg_term_date is
1360 select max(EFFECTIVE_END_DATE)
1361 FROM per_all_assignments_f asg
1362 where asg.assignment_id = P_Assignment_id
1363 and asg.assignment_type<>'B';
1364 --changes for bug 5749588 ends here
1365
1366 --
1367 l_Per_date date;
1368 --
1369 l_error number;
1370 --
1371 begin
1372 hr_utility.set_location(l_proc, 5);
1373 --
1374 l_Per_date := NULL;
1375 open csr_get_per_term_date;
1376 fetch csr_get_per_term_date into l_Per_date;
1377 close csr_get_per_term_date;
1378 --
1379 if l_Per_date is not null THEN
1380 l_error := per_formula_functions.set_date
1381 ('PER_TERMINATION_DATE',l_Per_date);
1382 else
1383 --changes for bug 5749588 starts here
1384 open csr_get_Asg_term_date;
1385 fetch csr_get_Asg_term_date into l_Per_date;
1386 close csr_get_Asg_term_date;
1387 --changes for bug 5749588 ends here
1388 -- l_Per_date := hr_api.g_eot;
1389 l_error := per_formula_functions.set_date
1390 ('PER_TERMINATION_DATE',l_Per_date);
1391 hr_utility.set_location(l_proc, 10);
1392 -- return 1;
1393 end if;
1394 --
1395 hr_utility.set_location(l_proc, 15);
1396 return 0;
1397 end GET_PER_TERMINATION_DATE;
1398 --
1399 --
1400 FUNCTION GET_PAYROLL_DTRANGE
1401 (P_Payroll_ID IN Number) return number IS
1402 --
1403 l_proc varchar2(72) := g_package||'get_payroll_dtrange';
1404 --
1405 cursor csr_get_payroll_range is
1406 select min(start_date),max(end_date)
1407 from per_time_periods
1408 where payroll_id = P_Payroll_ID;
1409 --
1410 l_start_date date;
1411 l_end_date date;
1412 l_period_number number;
1413 --
1414 l_error number;
1415 --
1416 begin
1417 hr_utility.set_location(l_proc, 5);
1418 --
1419 open csr_get_payroll_range;
1420 fetch csr_get_payroll_range into l_start_date,l_end_date;
1421 close csr_get_payroll_range;
1422 --
1423 if l_start_date is not null and l_end_date is not null then
1424 l_error := per_formula_functions.set_date
1425 ('PAYROLL_MAX_START_DATE',l_start_date);
1426 l_error := per_formula_functions.set_date
1427 ('PAYROLL_MAX_END_DATE',l_end_date);
1428 else
1429 hr_utility.set_location(l_proc, 10);
1430 return 1;
1431 end if;
1432 --
1433 hr_utility.set_location(l_proc, 15);
1434 return 0;
1435 end GET_PAYROLL_DTRANGE;
1436
1437 --
1438 /* =====================================================================
1439 Name : Get_Earliest_AsgChange_Date
1440 =====================================================================
1441 Purpose : Determines the earliest assignment status change recorded
1442 by the Payroll Events Model.
1443 Returns : Date
1444 ---------------------------------------------------------------------*/
1445 FUNCTION Get_Earliest_AsgChange_Date(p_business_group_id NUMBER
1446 ,p_assignment_id NUMBER
1447 ,p_event_group VARCHAR2
1448 ,p_start_date DATE
1449 ,p_end_date DATE
1450 ,p_recalc_date DATE)
1451 RETURN DATE IS
1452 --
1453 l_proc varchar2(72) := g_package||'Get_Earliest_AsgChange_Date';
1454 --
1455
1456 l_recalc_date DATE;
1457 l_detailed_output pay_interpreter_pkg.t_detailed_output_table_type;
1458 l_proration_dates pay_interpreter_pkg.t_proration_dates_table_type;
1459 l_proration_change_type pay_interpreter_pkg.t_proration_type_table_type;
1460 l_proration_type pay_interpreter_pkg.t_proration_type_table_type;
1461
1462 l_event_group_id pay_event_groups.event_group_id%TYPE;
1463 l_legislation_code HR_ORGANIZATION_INFORMATION.org_information9%TYPE;
1464
1465 BEGIN
1466 hr_utility.set_location('Entering: '||l_proc, 5);
1467
1468 l_recalc_date := p_recalc_date;
1469 l_legislation_code := get_legislation(p_business_group_id);
1470
1471 l_event_group_id := get_event(p_event_group, p_business_group_id, l_legislation_code);
1472
1473 IF l_event_group_id <> 0 THEN
1474
1475 pay_interpreter_pkg.entry_affected
1476 (p_assignment_id => p_assignment_id
1477 ,p_mode => 'DATE_PROCESSED'
1478 ,p_event_group_id => l_event_group_id
1479 ,p_process_mode => 'ENTRY_CREATION_DATE'
1480 ,p_start_date => p_start_date
1481 ,p_end_date => p_end_date
1482 ,t_detailed_output => l_detailed_output
1483 ,t_proration_dates => l_proration_dates
1484 ,t_proration_change_type => l_proration_change_type
1485 ,t_proration_type => l_proration_type);
1486
1487 IF l_detailed_output.COUNT <> 0 THEN
1488
1489 FOR i IN l_detailed_output.FIRST..l_detailed_output.LAST LOOP
1490
1491 IF l_detailed_output(i).effective_date < l_recalc_date THEN
1492
1493 l_recalc_date := l_detailed_output(i).effective_date;
1494
1495 END IF;
1496
1497 END LOOP;
1498 ELSE
1499 hr_utility.set_location(l_proc, 99);
1500 END IF;
1501 END IF;
1502
1503 hr_utility.set_location('Leaving: '||l_proc, 88);
1504
1505 RETURN l_recalc_date;
1506
1507 END Get_Earliest_AsgChange_Date;
1508 --
1509 --
1510 --
1511 end per_utility_functions;
[Home] [Help]