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