1 package body pay_au_leave_liability as
2 -- $Header: pyaullal.pkb 120.0 2005/05/29 03:06:58 appldev noship $
3
4 -- Copyright (C) 1999 Oracle Corporation
5 -- All Rights Reserved
6 --
7 -- Script to create AU HRMS leave liability package.
8 --
9 -- Change List
10 -- ===========
11 --
12 -- Date Author Reference Description
13 -- -----------+--------+---------+------------------------------------------
14 -- 01 Sep 2004 JLin N/A Fixed GSCC warning
15 -- 12 Aug 2004 JLin 3781867 Modified accraul_category getting
16 -- from the pay_au_processes table
17 -- 04 Dec 2002 Ragovind 2689226 Added NOCOPY for the functions leave_net_accrual, leave_net_entitlement, range_code and added dbdrv
18 -- 25 JUL 2000 rayyadev N/A modified the query of cursor c_Accrual_plans
19 -- in the archive_code procedure
20 -- 14 JUL 2000 rayyadev N/A Created
21 -- 06 oct 2000 rayyadev N/A Added the Leave_Net_Accrual Procedure.
22 -- 11 SEP 2001 vgsriniv 1938342 Added the c_formula Cursor.
23 -----------------------------------------------------------------------------
24 -- range_code procedure
25 --
26 -- public procedure required by the Payroll Archive Reporter (PAR) process
27 -----------------------------------------------------------------------------
28
29 procedure range_code
30 (p_payroll_action_id in number
31 ,p_sql out NOCOPY varchar2) is
32
33 l_sql varchar2(4000) ;
34 l_procedure_name varchar2(61);
35
36 begin
37 l_procedure_name := 'pay_au_leave_liability.range_code' ;
38
39 hr_utility.trace('In: ' || l_procedure_name) ;
40
41 -- set up a SQL statement that defines the set of people to process
42 -- required by the PAR process
43
44 l_sql := 'select distinct p.person_id ' ||
45 'from per_people_f p ' ||
46 ',pay_payroll_actions pa ' ||
47 ',per_assignments_f a ' ||
48 ',pay_element_entries_f ee ' ||
49 ',pay_element_links_f el ' ||
50 ',pay_accrual_plans ap ' ||
51 'where pa.payroll_action_id = :payroll_action_id ' ||
52 'and p.business_group_id = pa.business_group_id ' ||
53 'and a.person_id = p.person_id ' ||
54 'and a.payroll_id is not null ' ||
55 'and ee.assignment_id = a.assignment_id ' ||
56 'and el.element_link_id = ee.element_link_id ' ||
57 'and ap.accrual_plan_element_type_id = el.element_type_id ' ||
58 'and ap.accrual_category In (select accrual_category ' ||
59 'from pay_au_processes ' ||
60 'where accrual_category is not null ' ||
61 'and legislation_code = ''AU'') ' ||
62 'and pa.effective_date between p.effective_start_date ' ||
63 'and p.effective_end_date ' ||
64 'and pa.effective_date between a.effective_start_date ' ||
65 'and a.effective_end_date ' ||
66 'and pa.effective_date between ee.effective_start_date ' ||
67 'and ee.effective_end_date ' ||
68 'and pa.effective_date between el.effective_start_date ' ||
69 'and el.effective_end_date ' ||
70 'order by ' ||
71 'p.person_id ' ;
72
73 p_sql := l_sql ;
74
75 hr_utility.trace('Out: ' || l_procedure_name) ;
76
77 end range_code ;
78
79 -----------------------------------------------------------------------------
80 -- assignment_action_code procedure
81 --
82 -- public procedure required by the Payroll Archive Reporter process
83 -----------------------------------------------------------------------------
84
85 procedure assignment_action_code
86 (p_payroll_action_id in number
87 ,p_start_person_id in number
88 ,p_end_person_id in number
89 ,p_chunk in number) is
90
91 l_assignment_action_id number ;
92 l_procedure_name varchar2(61) ;
93
94 -- The PAR process can multi-thread this procedure so make
95 -- sure that the cursor only deals with assignments in this
96 -- thread. The p_start_person_id and p_end_person_id parameters
97 -- to the procedure identify the set of people in this thread.
98
99 -- The cursor identifies assignments that are enrolled in
100 -- accrual plans that have an accrual category of 'AUAL','AULSL'.
101
102 cursor c_assignments (p_payroll_action_id number
103 ,p_start_person_id number
104 ,p_end_person_id number) is
105 select distinct a.assignment_id
106 from pay_payroll_actions pa
107 , per_assignments_f a
108 , pay_element_entries_f ee
109 , pay_element_links_f el
110 , pay_accrual_plans ap
111 where pa.payroll_action_id = p_payroll_action_id
112 and a.business_group_id = pa.business_group_id
113 and a.person_id between p_start_person_id
114 and p_end_person_id
115 and a.payroll_id is not null
116 and ee.assignment_id = a.assignment_id
117 and el.element_link_id = ee.element_link_id
118 and ap.accrual_plan_element_type_id = el.element_type_id
119 and ap.accrual_category IN (select accrual_category
120 from pay_au_processes
121 where legislation_code = 'AU'
122 and accrual_category is not null)
123 and pa.effective_date between a.effective_start_date
124 and a.effective_end_date
125 and pa.effective_date between ee.effective_start_date
126 and ee.effective_end_date
127 and pa.effective_date between el.effective_start_date
128 and el.effective_end_date ;
129
130 cursor c_assignment_action_id is
131 select pay_assignment_actions_s.nextval
132 from dual ;
133
134 begin
135
136 l_procedure_name := 'pay_au_leave_liability.assignment_action_code' ;
137
138 hr_utility.trace('In: ' || l_procedure_name) ;
139
140 -- loop through the qualifying assignments
141
142 for r_assignment in c_assignments(p_payroll_action_id
143 ,p_start_person_id
144 ,p_end_person_id)
145 loop
146
147 -- get the next assignment action ID
148
149 open c_assignment_action_id ;
150 fetch c_assignment_action_id
151 into l_assignment_action_id ;
152 close c_assignment_action_id ;
153
154 -- create the assignment action ID
155
156 hr_nonrun_asact.insact(l_assignment_action_id
157 ,r_assignment.assignment_id
158 ,p_payroll_action_id
159 ,p_chunk
160 ,null) ;
161
162 end loop ; -- c_assignments
163
164 hr_utility.trace('Out: ' || l_procedure_name) ;
165
166 end assignment_action_code ;
167
168 -----------------------------------------------------------------------------
169 -- initialization_code procedure
170 --
171 -- public procedure required by the Payroll Archive Reporter process
172 -----------------------------------------------------------------------------
173
174 procedure initialization_code
175 (p_payroll_action_id in number) is
176
177 l_procedure_name varchar2(61) ;
178
179 begin
180
181 l_procedure_name := 'pay_au_leave_liability.initialization_code' ;
182
183 hr_utility.trace('In: ' || l_procedure_name) ;
184
185 -- do nothing: no global contexts need to be set
186
187 hr_utility.trace('Out: ' || l_procedure_name) ;
188
189 end initialization_code ;
190
191 -----------------------------------------------------------------------------
192 -- archive_code procedure
193 --
194 -- public procedure required by the Payroll Archive Reporter process
195 -----------------------------------------------------------------------------
196
197 procedure archive_code
198 (p_assignment_action_id in number
199 ,p_effective_date in date) is
200
201 l_procedure_name varchar2(61) ;
202 l_process_id pay_au_processes.process_id%type ;
203 l_gcc_parameters_store pay_au_generic_code_caller.t_variable_store_tab ;
204 l_business_group_id pay_payroll_actions.business_group_id%type ;
205 l_assignment_id pay_assignment_actions.assignment_id%type ;
206 l_payroll_action_id pay_payroll_actions.payroll_action_id%type ;
207 l_counter integer := 1 ;
208 l_accrual_category varchar2(15); --temporary variable to check the accrual category.
209
210 cursor c_process(p_short_name varchar2) is
211 select p.process_id
212 from pay_au_processes p
213 where p.short_name = p_short_name
214 and p.legislation_code = 'AU' ;
215
216 cursor c_action(p_assignment_action_id number) is
217 select pa.business_group_id
218 , pa.payroll_action_id
219 , aa.assignment_id
220 from pay_assignment_actions aa
221 , pay_payroll_actions pa
222 where aa.assignment_action_id = p_assignment_action_id
223 and pa.payroll_action_id = aa.payroll_action_id ;
224
225 cursor c_accrual_plans(p_assignment_action_id number) is
226 select ap.accrual_plan_id
227 , ap.accrual_category
228 , a.payroll_id
229 from pay_assignment_actions aa
230 , pay_payroll_actions pa
231 , per_assignments_f a
232 , pay_element_entries_f ee
233 , pay_element_links_f el
234 , pay_accrual_plans ap
235 where aa.assignment_action_id = p_assignment_action_id
236 and pa.payroll_action_id = aa.payroll_action_id
237 and a.assignment_id = aa.assignment_id
238 and a.payroll_id is not null
239 and ee.assignment_id = a.assignment_id
240 and el.element_link_id = ee.element_link_id
241 and ap.accrual_plan_element_type_id = el.element_type_id
242 and ap.accrual_category in (select accrual_category
243 from pay_au_processes
244 where legislation_code = 'AU'
245 and accrual_category is not null)
246 and pa.effective_date between a.effective_start_date
247 and a.effective_end_date
248 and pa.effective_date between ee.effective_start_date
249 and ee.effective_end_date
250 and pa.effective_date between el.effective_start_date
251 and el.effective_end_date ;
252
253 cursor c_process_category(p_accrual_category varchar2) is
254 select p.process_id
255 from pay_au_processes p
256 where p.accrual_category = p_accrual_category
257 and p.legislation_code = 'AU'
258 and p.accrual_category is not null;
259
260 begin
261
262 l_procedure_name := 'pay_au_leave_liability.archive_code' ;
263
264 hr_utility.trace('In: ' || l_procedure_name) ;
265
266 -- find out the generic code caller process ID for
267 -- AU leave liability
268 hr_utility.trace('P_assignment_action_id' || P_assignment_action_id) ;
269
270 hr_utility.trace('Accrual category ' || l_Accrual_category) ;
271
272
273
274 -- the generic code caller requires parameters to be passed
275 -- in using a PL/SQL table - set up the PL/SQL table with
276 -- parameters
277
278 open c_action(p_assignment_action_id) ;
279 fetch c_action
280 into l_business_group_id
281 , l_payroll_action_id
282 , l_assignment_id ;
283 close c_action ;
284
285 l_gcc_parameters_store(1).name := 'ASSIGNMENT_ID' ;
286 l_gcc_parameters_store(1).data_type := 'NUMBER' ;
287 l_gcc_parameters_store(1).value := to_char(l_assignment_id) ;
288
289 l_gcc_parameters_store(2).name := 'PAYROLL_ACTION_ID' ;
290 l_gcc_parameters_store(2).data_type := 'NUMBER' ;
291 l_gcc_parameters_store(2).value := to_char(l_payroll_action_id) ;
292
293 l_gcc_parameters_store(3).name := 'ORIGINAL_ENTRY_ID' ;
294 l_gcc_parameters_store(3).data_type := 'NUMBER' ;
295 l_gcc_parameters_store(3).value := '-1' ;
296
297 -- find the annual leave plans the assignment is enrolled in
298 -- (there will usually only be one).
299
300 for r_accrual_plan in c_accrual_plans(p_assignment_action_id)
301 loop
302 open c_process_category(r_accrual_plan.accrual_category);
303 fetch c_process_category into l_process_id;
304 close c_process_category ;
305
306 -- add the accrual plan ID to the PL/SQL table
307
308 l_gcc_parameters_store(4).name := 'ACCRUAL_PLAN_ID' ;
309 l_gcc_parameters_store(4).data_type := 'NUMBER' ;
310 l_gcc_parameters_store(4).value := r_accrual_plan.accrual_plan_id ;
311
312 -- add the payroll ID to the PL/SQL table
313
314 l_gcc_parameters_store(5).name := 'PAYROLL_ID' ;
315 l_gcc_parameters_store(5).data_type := 'NUMBER' ;
316 l_gcc_parameters_store(5).value := r_accrual_plan.payroll_id ;
317
318 -- set the hourly rate for testing purposes
319
320 l_gcc_parameters_store(6).name := 'ORDINARY_PAY_HOURLY_RATE' ;
321 l_gcc_parameters_store(6).data_type := 'NUMBER' ;
322 l_gcc_parameters_store(6).value := '10' ;
323
324 -- call the generic code caller. It will execute the modules
325 -- associated with the process and write the results as
326 -- archive database items
327
328 pay_au_generic_code_caller.execute_process
329 (p_business_group_id => l_business_group_id
330 ,p_effective_date => p_effective_date
331 ,p_process_id => l_process_id
332 ,p_assignment_action_id => p_assignment_action_id
333 ,p_input_store => l_gcc_parameters_store) ;
334
335 end loop ; -- c_accrual_plans
336
337 hr_utility.trace('Out: ' || l_procedure_name) ;
338
339 end archive_code ;
340
341 -----------------------------------------------------------------------------
342 -- hourly_rate procedure
343 --
344 -- procedure function that gets called as part of the AU Leave Liability
345 -- process. Note that this procedure can only be called from the
346 -- generic code caller as it relies on data structures that have been
347 -- set up by the generic code caller.
348 -----------------------------------------------------------------------------
349
350 procedure hourly_rate is
351
352 l_hourly_rate_formula_const constant ff_formulas_f.formula_name%type := 'AU_HOURLY_RATE_FORMULA' ;
353
354 l_procedure_name varchar2(61) ;
355 l_hourly_rate number := null ;
359
356 l_business_group_id pay_au_modules.business_group_id%type ;
357 l_legislation_code pay_au_modules.legislation_code%type ;
358 l_formula_name ff_formulas_f.formula_name%type ;
360 e_bad_module exception ;
361 e_bad_hourly_rate exception ;
362
363 l_formula_id number;
364 cursor c_formula (p_formula_name varchar2
365 ,p_business_group_id number
366 ,p_legislation_code varchar2) is
367 select f.formula_id
368 from ff_formulas_f f
369 where f.formula_name = p_formula_name
370 and ((f.business_group_id is null
371 and f.legislation_code is null)
372 or (f.business_group_id = p_business_group_id)
373 or (f.legislation_code = p_legislation_code)) ;
374 procedure execute_formula(p_formula_name varchar2) is
375
376 l_procedure_name varchar2(61) ;
377 l_module_id pay_au_modules.module_id%type ;
378
379 cursor c_module(p_formula_name varchar2
380 ,p_business_group_id number
381 ,p_legislation_code varchar2) is
382 select m.module_id
383 from pay_au_modules m
384 where m.formula_name = p_formula_name
385 and m.enabled_flag = 'Y'
386 and ((m.business_group_id is null
387 and m.legislation_code is null)
388 or (m.business_group_id = p_business_group_id)
389 or (m.legislation_code = p_legislation_code)) ;
390
391 begin
392
393 l_procedure_name := 'execute_formula' ;
394
395 hr_utility.trace(' In: ' || l_procedure_name) ;
396
397 -- get the module ID for the formula
398
399 open c_module(p_formula_name
400 ,l_business_group_id
401 ,l_legislation_code) ;
402 fetch c_module
403 into l_module_id ;
404 if c_module%notfound
405 then
406 close c_module ;
407 raise e_bad_module ;
408 end if ;
409 close c_module ;
410
411 -- execute the formula using the generic code caller execute
412 -- formula procedure
413
414 pay_au_generic_code_caller.execute_formula
415 (l_module_id
416 ,p_formula_name) ;
417
418 hr_utility.trace(' Out: ' || l_procedure_name) ;
419 exception
420 when e_bad_module
421 then
422 hr_utility.set_message(801, 'HR_AU_INVALID_MODULE') ;
423 hr_utility.set_message_token('MODULE_NAME',p_formula_name);
424 hr_utility.raise_error ;
425 end execute_formula ;
426
427 begin
428
429 l_procedure_name := 'pay_au_leave_liability.hourly_rate' ;
430
431 hr_utility.trace('In: ' || l_procedure_name) ;
432
433 -- get the BUSINESS_GROUP_ID from the PL/SQL table of
434 -- variables maintained by the generic code caller
435
436 pay_au_generic_code_caller.retrieve_variable
437 ('BUSINESS_GROUP_ID'
438 ,'NUMBER'
439 ,l_business_group_id) ;
440
441 -- get the LEGISLATION_CODE from the PL/SQL table of
442 -- variables maintained by the generic code caller
443
444 pay_au_generic_code_caller.retrieve_variable
445 ('LEGISLATION_CODE'
446 ,'TEXT'
447 ,l_legislation_code) ;
448
449 -- Execute the hourly rate formula.
450 -- (the hourly rate formula will be written by the implementation
451 -- team. We will ship the module definition in pay_au_modules
452 -- and define the specification of the formula).
453
454
455
456 open
457 c_formula(l_hourly_rate_formula_const,l_business_group_id,l_legislation_code);
458
459 fetch c_formula into l_formula_id ;
460 if c_formula%notfound then
461 close c_formula;
462 raise e_bad_hourly_rate;
463 else
464 close c_formula;
465 end if;
466
467
468
469
470 execute_formula(l_hourly_rate_formula_const) ;
471
472 -- get the HOURLY_RATE_FORMULA from the PL/SQL table of
473 -- variables maintained by the generic code caller. This
474 -- variable should have been set up as an output from the
475 -- hourly rate formula.
476
477 pay_au_generic_code_caller.retrieve_variable
478 ('HOURLY_RATE_FORMULA_NAME'
479 ,'TEXT'
480 ,l_formula_name) ;
481
482 execute_formula(l_formula_name) ;
483
484 -- the formula should have set up the ORDINARY_PAY_HOURLY_RATE
485 -- variable in the generic code caller's PL/SQL table. Get
486 -- rate to make sure that it has been set up.
487
488 pay_au_generic_code_caller.retrieve_variable
489 ('ORDINARY_PAY_HOURLY_RATE'
490 ,'NUMBER'
491 ,l_hourly_rate) ;
492
493 if l_hourly_rate is null
494 then
495 raise e_bad_hourly_rate ;
496 end if ;
497
498 hr_utility.trace('Out: ' || l_procedure_name) ;
499
500 exception
501 when e_bad_hourly_rate
502 then
503 hr_utility.set_message(801, 'HR_AU_BAD_HOURLY_RATE') ;
504 hr_utility.raise_error ;
505
506 end hourly_rate ;
507
508 procedure leave_net_entitlement
509 (p_assignment_id in number
510 ,p_payroll_id in number
514 ,p_start_date out NOCOPY date
511 ,p_business_group_id in number
512 ,p_plan_id in number
513 ,p_calculation_date in date
515 ,p_end_date out NOCOPY date
516 ,p_net_entitlement out NOCOPY number) is
517 l_get_accrual_value number;
518 l_net_accrual number;
519 l_next_period_end date;
520 l_procedure_name varchar2(61) ;
521
522 begin
523
524 l_procedure_name := 'pay_au_leave_liability.annual_leave_net_entitlement' ;
525
526 hr_utility.trace('In: ' || l_procedure_name) ;
527 l_get_accrual_value := hr_au_holidays.get_accrual_entitlement(p_assignment_id
528 ,p_payroll_id
529 ,p_business_group_id
530 ,p_plan_id
531 ,p_calculation_date
532 ,l_net_accrual
533 ,p_net_entitlement
534 ,p_start_date
535 ,p_end_date
536 ,l_next_period_end
537 );
538 hr_utility.trace('out: ' || l_procedure_name) ;
539 -- exception
540 -- when others then
541 end leave_net_entitlement;
542
543 procedure leave_net_accrual
544 (p_assignment_id IN NUMBER
545 ,p_payroll_id IN NUMBER
546 ,p_business_group_id IN NUMBER
547 ,p_plan_id IN NUMBER
548 ,p_calculation_date IN DATE
549 ,p_net_accrual OUT NOCOPY NUMBER
550 ,p_net_entitlement OUT NOCOPY NUMBER
551 ,p_calc_start_date OUT NOCOPY DATE
552 ,p_last_accrual OUT NOCOPY DATE
553 ,p_next_period_end OUT NOCOPY DATE) is
554 l_procedure_name varchar2(61) ;
555 l_get_accrual_value number ;
556 begin
557
558 l_procedure_name := 'pay_au_leave_liability.leave_net_accrual' ;
559
560 hr_utility.trace('In: ' || l_procedure_name) ;
561 l_get_accrual_value :=
562 hr_au_holidays.get_accrual_entitlement
563 (p_assignment_id
564 ,p_payroll_id
565 ,p_business_group_id
566 ,p_plan_id
567 ,p_calculation_date
568 ,p_net_accrual
569 ,p_net_entitlement
570 ,p_calc_start_date
571 ,p_last_accrual
572 ,p_next_period_end ) ;
573
574 hr_utility.trace('out: ' || l_procedure_name) ;
575 exception
576 when others then
577 hr_utility.trace('exception: ' || l_procedure_name) ;
578 end leave_net_accrual;
579
580 /*---------------------------------------------------------------------
581 Name : get_weekdays_in_period
582 Purpose : To get the number of weekdays in a date range
583 Returns : Number of Weekdays if successful, NULL otherwise
584 ---------------------------------------------------------------------*/
585
586 FUNCTION get_weekdays_in_period
587 (p_start_date IN DATE
588 ,p_end_date IN DATE)
589 RETURN NUMBER IS
590 l_proc VARCHAR2(72);
591
592 l_day_count NUMBER := 0;
593 l_day DATE;
594 BEGIN
595 l_proc := 'get_weekdays_in_period';
596
597 hr_utility.trace('In: '||l_proc);
598 hr_utility.trace(' p_start_date: ' || to_char(p_start_date,'dd Mon yyyy')) ;
599 hr_utility.trace(' p_end_date: ' || to_char(p_end_date,'dd Mon yyyy')) ;
600
601
602 IF (p_start_date > p_end_date)
603 THEN
604 hr_utility.trace('Crash Out: '||l_proc);
605 hr_utility.set_message(801,'HR_AU_INVALID_DATE_RANGE');
606 hr_utility.raise_error;
607 END IF;
608
609 hr_utility.set_location(l_proc,5);
610 l_day := p_start_date;
611 WHILE (l_day <= p_end_date)
612 LOOP
613 IF (TO_CHAR(l_day,'DY') IN ('MON','TUE','WED','THU','FRI'))
614 THEN
615 l_day_count := l_day_count + 1;
616 END IF;
617 l_day := l_day + 1;
618 END LOOP;
619 hr_utility.trace(' return: ' || to_char(l_day_count)) ;
620 hr_utility.trace('Out: '||l_proc);
621 RETURN l_day_count;
622
623 END get_weekdays_in_period;
624
625
626
627
628
629 end pay_au_leave_liability ;
[Home] [Help]