1 package body pay_nz_leave_liability as
2 -- $Header: pynzllal.pkb 115.6 2002/12/03 05:20:43 srrajago ship $
3
4 -- Copyright (C) 1999 Oracle Corporation
5 -- All Rights Reserved
6 --
7 -- Script to create NZ HRMS leave liability package.
8 --
9 -- Change List
10 -- ===========
11 --
12 -- Date Author Reference Description
13 -- -----------+--------+---------+------------------------------------------
14 -- 28 Feb 2000 JTurner Renamed objects to use country identifier
15 -- of "AU" instead of "NZ"
16 -- 29 NOV 1999 JTURNER N/A Created
17 -- 29 sep 2000 rayyadev bug no 1420851
18 -- 30 JUL 2001 rbsinha 1422001 Added function retrieve_variable
19 -- 28 AUG 2002 vgsriniv 2514562 Added code to initialise context parameter
20 -- to a default value.Also added dbdrv command
21 -- 03 DEC 2002 srrajago 2689221 Included 'nocopy' option for the 'out'
22 -- parameters of all the procedures.
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) := 'pay_nz_leave_liability.range_code' ;
35
36 begin
37 -- hr_utility.trace_on(null,'ll_archive') ;
38 hr_utility.trace('In: ' || l_procedure_name) ;
39
40 -- set up a SQL statement that defines the set of people to process
41 -- required by the PAR process
42
43 l_sql := 'select distinct p.person_id ' ||
44 'from per_people_f p ' ||
45 ',pay_payroll_actions pa ' ||
46 ',per_assignments_f a ' ||
47 ',pay_element_entries_f ee ' ||
48 ',pay_element_links_f el ' ||
49 ',pay_accrual_plans ap ' ||
50 'where pa.payroll_action_id = :payroll_action_id ' ||
51 'and p.business_group_id = pa.business_group_id ' ||
52 'and a.person_id = p.person_id ' ||
53 'and a.payroll_id is not null ' ||
54 'and ee.assignment_id = a.assignment_id ' ||
55 'and el.element_link_id = ee.element_link_id ' ||
56 'and ap.accrual_plan_element_type_id = el.element_type_id ' ||
57 'and ap.accrual_category = ''NZAL'' ' ||
58 'and pa.effective_date between p.effective_start_date ' ||
59 'and p.effective_end_date ' ||
60 'and pa.effective_date between a.effective_start_date ' ||
61 'and a.effective_end_date ' ||
62 'and pa.effective_date between ee.effective_start_date ' ||
63 'and ee.effective_end_date ' ||
64 'and pa.effective_date between el.effective_start_date ' ||
65 'and el.effective_end_date ' ||
66 'order by ' ||
67 'p.person_id ' ;
68
69 p_sql := l_sql ;
70
71 hr_utility.trace('Out: ' || l_procedure_name) ;
72
73 end range_code ;
74
75 -----------------------------------------------------------------------------
76 -- assignment_action_code procedure
77 --
78 -- public procedure required by the Payroll Archive Reporter process
79 -----------------------------------------------------------------------------
80
81 procedure assignment_action_code
82 (p_payroll_action_id in number
83 ,p_start_person_id in number
84 ,p_end_person_id in number
85 ,p_chunk in number) is
86
87 l_assignment_action_id number ;
88 l_procedure_name varchar2(61) := 'pay_nz_leave_liability.assignment_action_code' ;
89
90 -- The PAR process can multi-thread this procedure so make
91 -- sure that the cursor only deals with assignments in this
92 -- thread. The p_start_person_id and p_end_person_id parameters
93 -- to the procedure identify the set of people in this thread.
94
95 -- The cursor identifies assignments that are enrolled in
96 -- accrual plans that have an accrual category of 'NZAL'.
97
98 cursor c_assignments (p_payroll_action_id number
99 ,p_start_person_id number
100 ,p_end_person_id number) is
101 select distinct a.assignment_id
102 from pay_payroll_actions pa
103 , per_assignments_f a
104 , pay_element_entries_f ee
105 , pay_element_links_f el
106 , pay_accrual_plans ap
107 where pa.payroll_action_id = p_payroll_action_id
108 and a.business_group_id = pa.business_group_id
109 and a.person_id between p_start_person_id
110 and p_end_person_id
111 and a.payroll_id is not null
112 and ee.assignment_id = a.assignment_id
113 and el.element_link_id = ee.element_link_id
114 and ap.accrual_plan_element_type_id = el.element_type_id
115 and ap.accrual_category = 'NZAL'
116 and pa.effective_date between a.effective_start_date
117 and a.effective_end_date
118 and pa.effective_date between ee.effective_start_date
119 and ee.effective_end_date
120 and pa.effective_date between el.effective_start_date
121 and el.effective_end_date ;
122
123 cursor c_assignment_action_id is
124 select pay_assignment_actions_s.nextval
125 from dual ;
126
127 begin
128
129 hr_utility.trace('In: ' || l_procedure_name) ;
130
131 -- loop through the qualifying assignments
132
133 for r_assignment in c_assignments(p_payroll_action_id
134 ,p_start_person_id
135 ,p_end_person_id)
136 loop
137
138 -- get the next assignment action ID
139
140 open c_assignment_action_id ;
141 fetch c_assignment_action_id
142 into l_assignment_action_id ;
143 close c_assignment_action_id ;
144
145 -- create the assignment action ID
146
147 hr_nonrun_asact.insact(l_assignment_action_id
148 ,r_assignment.assignment_id
149 ,p_payroll_action_id
150 ,p_chunk
151 ,null) ;
152
153 end loop ; -- c_assignments
154
155 hr_utility.trace('Out: ' || l_procedure_name) ;
156
157 end assignment_action_code ;
158
159 -----------------------------------------------------------------------------
160 -- initialization_code procedure
161 --
162 -- public procedure required by the Payroll Archive Reporter process
163 -----------------------------------------------------------------------------
164
165 procedure initialization_code
166 (p_payroll_action_id in number) is
167
168 l_procedure_name varchar2(61) := 'pay_nz_leave_liability.initialization_code' ;
169
170 begin
171
172 hr_utility.trace('In: ' || l_procedure_name) ;
173
174 -- do nothing: no global contexts need to be set
175
176 hr_utility.trace('Out: ' || l_procedure_name) ;
177
178 end initialization_code ;
179
180 -----------------------------------------------------------------------------
181 -- archive_code procedure
182 --
183 -- public procedure required by the Payroll Archive Reporter process
184 -----------------------------------------------------------------------------
185
186 procedure archive_code
187 (p_assignment_action_id in number
188 ,p_effective_date in date) is
189
190 l_procedure_name varchar2(61) := 'pay_nz_leave_liability.archive_code' ;
191 l_process_id pay_au_processes.process_id%type ;
192 l_gcc_parameters_store pay_au_generic_code_caller.t_variable_store_tab ;
193 l_business_group_id pay_payroll_actions.business_group_id%type ;
194 l_assignment_id pay_assignment_actions.assignment_id%type ;
195 l_payroll_action_id pay_payroll_actions.payroll_action_id%type ;
196 l_counter integer := 1 ;
197
198 cursor c_process(p_short_name varchar2) is
199 select p.process_id
200 from pay_au_processes p
201 where p.short_name = p_short_name
202 and p.legislation_code = 'NZ' ;
203
204 cursor c_action(p_assignment_action_id number) is
205 select pa.business_group_id
206 , pa.payroll_action_id
207 , aa.assignment_id
208 from pay_assignment_actions aa
209 , pay_payroll_actions pa
210 where aa.assignment_action_id = p_assignment_action_id
211 and pa.payroll_action_id = aa.payroll_action_id ;
212
213 cursor c_accrual_plans(p_assignment_action_id number) is
214 select ap.accrual_plan_id
215 , a.payroll_id
216 from pay_assignment_actions aa
217 , pay_payroll_actions pa
218 , per_assignments_f a
219 , pay_element_entries_f ee
220 , pay_element_links_f el
221 , pay_accrual_plans ap
222 where aa.assignment_action_id = p_assignment_action_id
223 and pa.payroll_action_id = aa.payroll_action_id
224 and a.assignment_id = aa.assignment_id
225 and a.payroll_id is not null
226 and ee.assignment_id = a.assignment_id
227 and el.element_link_id = ee.element_link_id
228 and ap.accrual_plan_element_type_id = el.element_type_id
229 and ap.accrual_category = 'NZAL'
230 and pa.effective_date between a.effective_start_date
231 and a.effective_end_date
232 and pa.effective_date between ee.effective_start_date
233 and ee.effective_end_date
234 and pa.effective_date between el.effective_start_date
235 and el.effective_end_date ;
236
237 begin
238
239 hr_utility.trace('In: ' || l_procedure_name) ;
240
241 -- find out the generic code caller process ID for
242 -- NZ leave liability
243
244 open c_process('PYNZLLAL') ;
245 fetch c_process
246 into l_process_id ;
247 close c_process ;
248
249 -- the generic code caller requires parameters to be passed
250 -- in using a PL/SQL table - set up the PL/SQL table with
251 -- parameters
252
253 open c_action(p_assignment_action_id) ;
254 fetch c_action
255 into l_business_group_id
256 , l_payroll_action_id
257 , l_assignment_id ;
258 close c_action ;
259
260 l_gcc_parameters_store(1).name := 'ASSIGNMENT_ID' ;
261 l_gcc_parameters_store(1).data_type := 'NUMBER' ;
262 l_gcc_parameters_store(1).value := to_char(l_assignment_id) ;
263
264 l_gcc_parameters_store(2).name := 'PAYROLL_ACTION_ID' ;
265 l_gcc_parameters_store(2).data_type := 'NUMBER' ;
266 l_gcc_parameters_store(2).value := to_char(l_payroll_action_id) ;
267
268 l_gcc_parameters_store(3).name := 'ORIGINAL_ENTRY_ID' ;
269 l_gcc_parameters_store(3).data_type := 'NUMBER' ;
270 l_gcc_parameters_store(3).value := '-1' ;
271
272 -- find the annual leave plans the assignment is enrolled in
273 -- (there will usually only be one).
274
275 for r_accrual_plan in c_accrual_plans(p_assignment_action_id)
276 loop
277
278 -- add the accrual plan ID to the PL/SQL table
279
280 l_gcc_parameters_store(4).name := 'ACCRUAL_PLAN_ID' ;
281 l_gcc_parameters_store(4).data_type := 'NUMBER' ;
282 l_gcc_parameters_store(4).value := r_accrual_plan.accrual_plan_id ;
283
284 -- add the payroll ID to the PL/SQL table
285
286 l_gcc_parameters_store(5).name := 'PAYROLL_ID' ;
287 l_gcc_parameters_store(5).data_type := 'NUMBER' ;
288 l_gcc_parameters_store(5).value := r_accrual_plan.payroll_id ;
289
290 -- set the hourly rate for testing purposes
291
292 l_gcc_parameters_store(6).name := 'ORDINARY_PAY_HOURLY_RATE' ;
293 l_gcc_parameters_store(6).data_type := 'NUMBER' ;
294 l_gcc_parameters_store(6).value := '10' ;
295
296 /* Bug 2514562 Initialised context parameter element entry id to a
297 default value -1 */
298 l_gcc_parameters_store(7).name := 'ELEMENT_ENTRY_ID' ;
299 l_gcc_parameters_store(7).data_type := 'NUMBER' ;
300 l_gcc_parameters_store(7).value := '-1' ;
301
302
303 -- call the generic code caller. It will execute the modules
304 -- associated with the process and write the results as
305 -- archive database items
306
307 pay_au_generic_code_caller.execute_process
308 (p_business_group_id => l_business_group_id
309 ,p_effective_date => p_effective_date
310 ,p_process_id => l_process_id
311 ,p_assignment_action_id => p_assignment_action_id
312 ,p_input_store => l_gcc_parameters_store) ;
313
314 end loop ; -- c_accrual_plans
315
316 hr_utility.trace('Out: ' || l_procedure_name) ;
317
318 end archive_code ;
319
320 -----------------------------------------------------------------------------
321 -- hourly_rate procedure
322 --
323 -- procedure function that gets called as part of the NZ Leave Liability
324 -- process. Note that this procedure can only be called from the
325 -- generic code caller as it relies on data structures that have been
326 -- set up by the generic code caller.
327 -----------------------------------------------------------------------------
328
329 procedure hourly_rate is
330
331 l_hourly_rate_formula_const constant ff_formulas_f.formula_name%type := 'HOURLY_RATE_FORMULA' ;
332
333 l_procedure_name varchar2(61) := 'pay_nz_leave_liability.hourly_rate' ;
334 l_hourly_rate number := null ;
335 l_business_group_id pay_au_modules.business_group_id%type ;
336 l_legislation_code pay_au_modules.legislation_code%type ;
337 l_formula_name ff_formulas_f.formula_name%type ;
338
339 e_bad_module exception ;
340 e_bad_hourly_rate exception ;
341
342 procedure execute_formula(p_formula_name varchar2) is
343
344 l_procedure_name varchar2(61) := 'execute_formula' ;
345 l_module_id pay_au_modules.module_id%type ;
346
347 cursor c_module(p_formula_name varchar2
348 ,p_business_group_id number
349 ,p_legislation_code varchar2) is
350 select m.module_id
351 from pay_au_modules m
352 where m.formula_name = p_formula_name
353 and m.enabled_flag = 'Y'
354 and ((m.business_group_id is null
355 and m.legislation_code is null)
356 or (m.business_group_id = p_business_group_id)
357 or (m.legislation_code = p_legislation_code)) ;
358
359 begin
360
361 hr_utility.trace(' In: ' || l_procedure_name) ;
362
363 -- get the module ID for the formula
364
365 open c_module(p_formula_name
366 ,l_business_group_id
367 ,l_legislation_code) ;
368 fetch c_module
369 into l_module_id ;
370 if c_module%notfound
371 then
372 close c_module ;
373 raise e_bad_module ;
374 end if ;
375 close c_module ;
376
377 -- execute the formula using the generic code caller execute
378 -- formula procedure
379
380 pay_au_generic_code_caller.execute_formula
381 (l_module_id
382 ,p_formula_name) ;
383
384 hr_utility.trace(' Out: ' || l_procedure_name) ;
385
386 end execute_formula ;
387
388 begin
389
390 hr_utility.trace('In: ' || l_procedure_name) ;
391
392 -- get the BUSINESS_GROUP_ID from the PL/SQL table of
393 -- variables maintained by the generic code caller
394
395 pay_au_generic_code_caller.retrieve_variable
396 ('BUSINESS_GROUP_ID'
397 ,'NUMBER'
398 ,l_business_group_id) ;
399
400 -- get the LEGISLATION_CODE from the PL/SQL table of
401 -- variables maintained by the generic code caller
402
403 pay_au_generic_code_caller.retrieve_variable
404 ('LEGISLATION_CODE'
405 ,'TEXT'
406 ,l_legislation_code) ;
407
408 -- Execute the hourly rate formula.
409 -- (the hourly rate formula will be written by the implementation
410 -- team. We will ship the module definition in pay_au_modules
411 -- and define the specification of the formula).
412
413 execute_formula(l_hourly_rate_formula_const) ;
414
415 -- get the HOURLY_RATE_FORMULA from the PL/SQL table of
416 -- variables maintained by the generic code caller. This
417 -- variable should have been set up as an output from the
418 -- hourly rate formula.
419
420 pay_au_generic_code_caller.retrieve_variable
421 ('HOURLY_RATE_FORMULA_NAME'
422 ,'TEXT'
423 ,l_formula_name) ;
424
425 execute_formula(l_formula_name) ;
426
427 -- the formula should have set up the ORDINARY_PAY_HOURLY_RATE
428 -- variable in the generic code caller's PL/SQL table. Get
429 -- rate to make sure that it has been set up.
430
431 pay_au_generic_code_caller.retrieve_variable
432 ('ORDINARY_PAY_HOURLY_RATE'
433 ,'NUMBER'
434 ,l_hourly_rate) ;
435
436 if l_hourly_rate is null
437 then
438 raise e_bad_hourly_rate ;
439 end if ;
440
441 hr_utility.trace('Out: ' || l_procedure_name) ;
442
443 exception
444 when e_bad_module
445 then
446 hr_utility.set_message(801, 'HR_NZ_INVALID_MODULE') ;
447 hr_utility.raise_error ;
448 when e_bad_hourly_rate
449 then
450 hr_utility.set_message(801, 'HR_NZ_BAD_HOURLY_RATE') ;
451 hr_utility.raise_error ;
452
453 end hourly_rate ;
454
455
456 procedure leave_net_accrual
457 (p_assignment_id IN NUMBER
458 ,p_payroll_id IN NUMBER
459 ,p_business_group_id IN NUMBER
460 ,p_plan_id IN NUMBER
461 ,p_calculation_date IN DATE
462 ,p_net_accrual OUT NOCOPY NUMBER
463 ,p_net_entitlement OUT NOCOPY NUMBER
464 ,p_calc_start_date OUT NOCOPY DATE
465 ,p_last_accrual OUT NOCOPY DATE
466 ,p_next_period_end OUT NOCOPY DATE) is
467 l_procedure_name varchar2(61) := 'pay_nz_leave_liability.leave_net_accrual' ;
468 l_get_accrual_value number ;
469 begin
470
471 hr_utility.trace('In: ' || l_procedure_name) ;
472 l_get_accrual_value :=
473 hr_nz_holidays.get_accrual_entitlement
474 (p_assignment_id
475 ,p_payroll_id
476 ,p_business_group_id
477 ,p_plan_id
478 ,p_calculation_date
479 ,p_net_accrual
480 ,p_net_entitlement
481 ,p_calc_start_date
482 ,p_last_accrual
483 ,p_next_period_end ) ;
484
485 hr_utility.trace('out: ' || l_procedure_name) ;
486 exception
487 when others then
488 hr_utility.trace('exception: ' || l_procedure_name) ;
489 end leave_net_accrual;
490
491
492 function retrieve_variable(P_NAME IN VARCHAR2,
493 P_DATA_TYPE IN VARCHAR2) return varchar2 is
494
495 l_value varchar2(200);
496
497 begin
498
499 pay_au_generic_code_caller.retrieve_variable(P_NAME,P_DATA_TYPE,l_value);
500
501 return l_value ;
502
503 end retrieve_variable;
504
505
506 end pay_nz_leave_liability ;
[Home] [Help]