[Home] [Help]
PACKAGE BODY: APPS.PAY_CORE_PAYSLIP_UTILS
Source
1 PACKAGE BODY pay_core_payslip_utils AS
2 /* $Header: pycopysl.pkb 115.0 2004/04/02 01:45:38 tbattoo noship $ */
3
4
5 g_package CONSTANT VARCHAR2(30) := 'pay_core_payslip_utils.';
6
7 PROCEDURE get_parameters(p_payroll_action_id IN NUMBER,
8 p_token_name IN VARCHAR2,
9 p_token_value OUT NOCOPY VARCHAR2) IS
10
11 CURSOR csr_parameter_info(p_pact_id NUMBER,
12 p_token CHAR) IS
13 SELECT SUBSTR(legislative_parameters,
14 INSTR(legislative_parameters,p_token)+(LENGTH(p_token)+1),
15 INSTR(legislative_parameters,' ',
16 INSTR(legislative_parameters,p_token))
17 - (INSTR(legislative_parameters,p_token)+LENGTH(p_token))),
18 business_group_id
19 FROM pay_payroll_actions
20 WHERE payroll_action_id = p_pact_id;
21
22 l_business_group_id VARCHAR2(20);
23 l_token_value VARCHAR2(50);
24
25 l_proc VARCHAR2(50) := g_package || 'get_parameters';
26
27 BEGIN
28
29 hr_utility.set_location('Entering ' || l_proc,10);
30
31 hr_utility.set_location('Step ' || l_proc,20);
32 hr_utility.set_location('p_token_name = ' || p_token_name,20);
33
34 OPEN csr_parameter_info(p_payroll_action_id,
35 p_token_name);
36
37 FETCH csr_parameter_info INTO l_token_value,
38 l_business_group_id;
39
40 CLOSE csr_parameter_info;
41
42 IF p_token_name = 'BG_ID'
43
44 THEN
45
46 p_token_value := l_business_group_id;
47
48 ELSE
49
50 p_token_value := l_token_value;
51
52 END IF;
53
54 hr_utility.set_location('l_token_value = ' || l_token_value,20);
55 hr_utility.set_location('Leaving ' || l_proc,30);
56
57 END get_parameters;
58
59 /*
60 Name: range_cursor
61 Desrciption:
62 This code returns the select statement that
63 should be used to generate the ranges.
64 */
65 PROCEDURE range_cursor (pactid IN NUMBER,
66 sqlstr OUT NOCOPY VARCHAR2)
67 -- public procedure which archives the payroll information, then returns a
68 -- varchar2 defining a SQL statement to select all the people that may be
69 -- eligible for payslip reports.
70 -- The archiver uses this cursor to split the people into chunks for parallel
71 -- processing.
72 IS
73 --
74 l_proc CONSTANT VARCHAR2(50):= g_package||'range_cursor';
75 -- vars for constructing the sqlstr
76 BEGIN
77
78 hr_utility.set_location('Entering ' || l_proc,10);
79
80 sqlstr := 'SELECT DISTINCT person_id
81 FROM per_people_f ppf,
82 pay_payroll_actions ppa
83 WHERE ppa.payroll_action_id = :payroll_action_id
84 AND ppa.business_group_id +0 = ppf.business_group_id
85 ORDER BY ppf.person_id';
86
87 hr_utility.set_location('Leaving ' || l_proc,40);
88
89 END range_cursor;
90
91 /*
92 Name: action_creation
93 Desrciption:
94 This code should be used to generate the
95 master assignment actions for the
96 payslip archive.
97 */
98 PROCEDURE action_creation (pactid in number,
99 stperson in number,
100 endperson in number,
101 chunk in number,
102 p_report_type in varchar2,
103 p_report_qualifier in varchar2) is
104 --
105 CURSOR csr_prepaid_assignments(p_pact_id NUMBER,
106 stperson NUMBER,
107 endperson NUMBER,
108 p_payroll_id NUMBER,
109 p_consolidation_id NUMBER,
110 p_report_type VARCHAR2,
111 p_report_qualifier VARCHAR2) IS
112 SELECT act.assignment_id assignment_id,
113 act.assignment_action_id run_action_id,
114 act1.assignment_action_id prepaid_action_id
115 FROM pay_payroll_actions ppa,
116 pay_payroll_actions appa,
117 pay_payroll_actions appa2,
118 pay_assignment_actions act,
119 pay_assignment_actions act1,
120 pay_action_interlocks pai,
121 per_all_assignments_f as1
122 WHERE ppa.payroll_action_id = p_pact_id
123 AND appa2.consolidation_set_id = p_consolidation_id
124 AND appa2.effective_date BETWEEN
125 ppa.start_date AND ppa.effective_date
126 AND as1.person_id BETWEEN
127 stperson AND endperson
128 AND appa.action_type IN ('R','Q') -- Payroll Run or Quickpay Run
129 AND act.payroll_action_id = appa.payroll_action_id
130 AND act.source_action_id IS NULL
131 AND as1.assignment_id = act1.assignment_id
132 AND ppa.effective_date BETWEEN
133 as1.effective_start_date AND as1.effective_end_date
134 AND act.action_status = 'C'
135 AND act.assignment_action_id = pai.locked_action_id
136 AND act1.assignment_action_id = pai.locking_action_id
137 AND act1.action_status = 'C'
138 AND act1.payroll_action_id = appa2.payroll_action_id
139 AND appa2.action_type IN ('P','U') -- Prepayments or Quickpay Prepayments
140 AND (as1.payroll_id = p_payroll_id OR p_payroll_id IS NULL)
141 AND NOT EXISTS (SELECT /*+ ORDERED */ NULL
142 FROM pay_action_interlocks pai1,
143 pay_assignment_actions act2,
144 pay_payroll_actions appa3
145 WHERE pai1.locked_action_id = act.assignment_action_id
146 AND act2.assignment_action_id = pai1.locking_action_id
147 AND act2.payroll_action_id = appa3.payroll_action_id
148 AND appa3.action_type = 'X'
149 AND appa3.report_type = p_report_type
150 AND appa3.report_qualifier = p_report_qualifier)
151 ORDER BY act.assignment_id, act.assignment_action_id
152 FOR UPDATE OF as1.assignment_id;
153
154 l_actid NUMBER;
155 l_canonical_end_date DATE;
156 l_canonical_start_date DATE;
157 l_consolidation_set VARCHAR2(30);
158 l_end_date VARCHAR2(20);
159 l_payroll_id NUMBER;
160 l_prepay_action_id NUMBER;
161 l_start_date VARCHAR2(20);
162
163 l_proc VARCHAR2(50) := g_package||'action_creation';
164
165 BEGIN
166
167 hr_utility.set_location('Entering ' || l_proc,10);
168
169 get_parameters (
170 p_payroll_action_id => pactid
171 , p_token_name => 'PAYROLL'
172 , p_token_value => l_payroll_id);
173
174 get_parameters (
175 p_payroll_action_id => pactid
176 , p_token_name => 'CONSOLIDATION'
177 , p_token_value => l_consolidation_set);
178
179 get_parameters (
180 p_payroll_action_id => pactid
181 , p_token_name => 'START_DATE'
182 , p_token_value => l_start_date);
183
184 get_parameters (
185 p_payroll_action_id => pactid
186 , p_token_name => 'END_DATE'
187 , p_token_value => l_end_date);
188
189 hr_utility.set_location('Step ' || l_proc,20);
190 hr_utility.set_location('l_payroll_id = ' || l_payroll_id,20);
191 hr_utility.set_location('l_start_date = ' || l_start_date,20);
192 hr_utility.set_location('l_end_date = ' || l_end_date,20);
193
194 l_canonical_start_date := TO_DATE(l_start_date,'yyyy/mm/dd');
195 l_canonical_end_date := TO_DATE(l_end_date,'yyyy/mm/dd');
196
197 l_prepay_action_id := 0;
198
199 FOR csr_rec IN csr_prepaid_assignments(pactid,
200 stperson,
201 endperson,
202 l_payroll_id,
203 l_consolidation_set,
204 p_report_type,
205 p_report_qualifier)
206
207 LOOP
208
209 IF l_prepay_action_id <> csr_rec.prepaid_action_id
210
211 THEN
212
213 SELECT pay_assignment_actions_s.NEXTVAL
214 INTO l_actid
215 FROM dual;
216
217 -- CREATE THE ARCHIVE ASSIGNMENT ACTION FOR THE MASTER ASSIGNMENT ACTION
218
219 hr_nonrun_asact.insact(l_actid,csr_rec.assignment_id,pactid,chunk,NULL);
220
221 -- CREATE THE ARCHIVE TO PAYROLL MASTER ASSIGNMENT ACTION INTERLOCK AND
222 -- THE ARCHIVE TO PREPAYMENT ASSIGNMENT ACTION INTERLOCK
223
224 hr_utility.set_location('creating lock1 ' || l_actid || ' to ' || csr_rec.run_action_id,20);
225 hr_utility.set_location('creating lock2 ' || l_actid || ' to ' || csr_rec.prepaid_action_id,20);
226
227 hr_nonrun_asact.insint(l_actid,csr_rec.prepaid_action_id);
228
229 END IF;
230
231 hr_nonrun_asact.insint(l_actid,csr_rec.run_action_id);
232
233 l_prepay_action_id := csr_rec.prepaid_action_id;
234
235 END LOOP;
236
237 hr_utility.set_location('Leaving ' || l_proc,20);
238
239 END action_creation;
240
241 function get_max_nor_act_seq(p_payroll_action_id in number,
242 p_assignment_action_id in number,
243 p_effective_date in date)
244 return number
245 is
246 l_run_type_id pay_payroll_actions.run_type_id%type;
247 l_act_seq pay_assignment_actions.action_sequence%type;
248 begin
249 --
250 select run_type_id
251 into l_run_type_id
252 from pay_payroll_actions
253 where payroll_action_id = p_payroll_action_id;
254 --
255 /* If the run type id is null then run
256 types are not being used
257 */
258 if (l_run_type_id is null) then
259 select action_sequence
260 into l_act_seq
261 from pay_assignment_actions
262 where assignment_action_id = p_assignment_action_id;
263 else
264 SELECT MAX(paa1.action_sequence)
265 into l_act_seq
266 FROM pay_assignment_actions paa1,
267 pay_assignment_actions paa2,
268 pay_run_types_f prt1
269 WHERE prt1.run_type_id = paa1.run_type_id
270 AND prt1.run_method IN ('N','P')
271 AND paa1.payroll_action_id = p_payroll_action_id
272 AND paa1.assignment_id = paa2.assignment_id
273 AND paa1.source_action_id = paa2.assignment_action_id
274 AND paa2.assignment_action_id = p_assignment_action_id
275 AND p_effective_date BETWEEN
276 prt1.effective_start_date AND prt1.effective_end_date;
277 end if;
278 --
279 return l_act_seq;
280 --
281 end get_max_nor_act_seq;
282 /*
283 Name: generate_child_actions
284 Desrciption:
285 This procedure should be the first procedure called
286 from the payslip archive archive_code section.
287
288 The procedure generates the child assignment actions
289 it is these that determine the number of payslips
290 to archive
291 */
292 PROCEDURE generate_child_actions(p_assactid in number,
293 p_effective_date in date) IS
294
295 CURSOR csr_assignment_actions(p_locking_action_id NUMBER) IS
296 SELECT pre.locked_action_id pre_assignment_action_id,
297 pay.locked_action_id master_assignment_action_id,
298 assact.assignment_id assignment_id,
299 assact.payroll_action_id pay_payroll_action_id,
300 paa.effective_date effective_date,
301 ppaa.effective_date pre_effective_date,
302 paa.date_earned date_earned,
303 paa.time_period_id time_period_id
304 FROM pay_action_interlocks pre,
305 pay_action_interlocks pay,
306 pay_payroll_actions paa,
307 pay_payroll_actions ppaa,
308 pay_assignment_actions assact,
309 pay_assignment_actions passact
310 WHERE pre.locked_action_id = pay.locking_action_id
311 AND pre.locking_action_id = p_locking_action_id
312 AND pre.locked_action_id = passact.assignment_action_id
313 AND passact.payroll_action_id = ppaa.payroll_action_id
314 AND ppaa.action_type IN ('P','U')
315 AND pay.locked_action_id = assact.assignment_action_id
316 AND assact.payroll_action_id = paa.payroll_action_id
317 AND assact.source_action_id IS NULL
318 ORDER BY pay.locked_action_id;
319
320 CURSOR csr_child_actions(p_master_assignment_action NUMBER,
321 p_payroll_action_id NUMBER,
322 p_assignment_id NUMBER,
323 p_effective_date DATE ) IS
324 SELECT paa.assignment_action_id child_assignment_action_id,
325 'S' run_type
326 FROM pay_assignment_actions paa,
327 pay_run_types_f prt
328 WHERE paa.source_action_id = p_master_assignment_action
329 AND paa.payroll_action_id = p_payroll_action_id
330 AND paa.assignment_id = p_assignment_id
331 AND paa.run_type_id = prt.run_type_id
332 AND prt.run_method = 'S'
333 AND p_effective_date BETWEEN
334 prt.effective_start_date AND prt.effective_end_date
335 UNION
336 SELECT paa.assignment_action_id child_assignment_action_id,
337 'NP' run_type
338 FROM pay_assignment_actions paa
339 WHERE paa.payroll_action_id = p_payroll_action_id
340 AND paa.assignment_id = p_assignment_id
341 AND paa.action_sequence =
342 pay_core_payslip_utils.get_max_nor_act_seq(p_payroll_action_id,
343 p_master_assignment_action,
344 p_effective_date);
345
346 CURSOR csr_np_children (p_assignment_action_id NUMBER,
347 p_payroll_action_id NUMBER,
348 p_assignment_id NUMBER,
349 p_effective_date DATE) IS
350 SELECT paa.assignment_action_id np_assignment_action_id,
351 prt.run_method
352 FROM pay_assignment_actions paa,
353 pay_run_types_f prt
354 WHERE paa.source_action_id = p_assignment_action_id
355 AND paa.payroll_action_id = p_payroll_action_id
356 AND paa.assignment_id = p_assignment_id
357 AND paa.run_type_id = prt.run_type_id
358 AND prt.run_method IN ('N','P')
359 AND p_effective_date BETWEEN
360 prt.effective_start_date AND prt.effective_end_date
361 UNION
362 SELECT paa.assignment_action_id np_assignment_action_id,
363 'N'
364 FROM pay_assignment_actions paa,
365 pay_payroll_actions ppa
366 WHERE paa.assignment_action_id = p_assignment_action_id
367 AND ppa.payroll_action_id = p_payroll_action_id
368 AND ppa.payroll_action_id = paa.payroll_action_id
369 AND ppa.run_type_id is null
370 AND paa.assignment_id = p_assignment_id;
371
372 l_actid NUMBER;
373 l_action_context_id NUMBER;
374 l_action_info_id NUMBER(15);
375 l_assignment_action_id NUMBER;
376 l_business_group_id NUMBER;
377 l_chunk_number NUMBER;
378 l_date_earned DATE;
379 l_ovn NUMBER;
380 l_person_id NUMBER;
381 l_salary VARCHAR2(10);
382 l_sequence NUMBER;
383
384 l_proc VARCHAR2(50) := g_package || 'archive_code';
385
386 l_standard_asg_act_id pay_assignment_actions.assignment_action_id%type;
387 l_pactid pay_assignment_actions.payroll_action_id%type;
388
389 BEGIN
390
391 hr_utility.set_location('Entering '|| l_proc,10);
392
393 hr_utility.set_location('Step '|| l_proc,20);
394 hr_utility.set_location('p_assactid = ' || p_assactid,20);
395
396 -- retrieve the chunk number for the current assignment action
397
398 SELECT paa.chunk_number,
399 paa.payroll_action_id
400 INTO l_chunk_number,
401 l_pactid
402 FROM pay_assignment_actions paa
403 WHERE paa.assignment_action_id = p_assactid;
404
405 l_standard_asg_act_id := null;
406
407 -- Select all the master run actions.
408 FOR csr_rec IN csr_assignment_actions(p_assactid)
409
410 LOOP
411
412 hr_utility.set_location('csr_rec.master_assignment_action_id = ' ||
413 csr_rec.master_assignment_action_id,20);
414 hr_utility.set_location('csr_rec.pre_assignment_action_id = ' ||
415 csr_rec.pre_assignment_action_id,20);
416 hr_utility.set_location('csr_rec.assignment_id = ' ||
417 csr_rec.assignment_id,20);
418 hr_utility.set_location('csr_rec.date_earned = ' ||
419 to_char( csr_rec.date_earned,'dd-mon-yyyy'),20);
423 csr_rec.time_period_id,20);
420 hr_utility.set_location('csr_rec.pre_effective_date = '
421 ||to_char( csr_rec.pre_effective_date,'dd-mon-yyyy'),20);
422 hr_utility.set_location('csr_rec.time_period_id = ' ||
424
425 -- Select all the child actions
426 FOR csr_child_rec IN csr_child_actions(csr_rec.master_assignment_action_id,
427 csr_rec.pay_payroll_action_id,
428 csr_rec.assignment_id,
429 csr_rec.effective_date)
430
431 LOOP
432
433 -- create additional archive assignment actions and interlocks
434
435 IF csr_child_rec.run_type = 'S'
436
437 THEN
438
439 SELECT pay_assignment_actions_s.NEXTVAL
440 INTO l_actid
441 FROM dual;
442
443 hr_utility.set_location('csr_child_rec.run_type = ' ||
444 csr_child_rec.run_type,30);
445 hr_utility.set_location('csr_rec.master_assignment_action_id = ' ||
446 csr_rec.master_assignment_action_id,30);
447
448 hr_nonrun_asact.insact(
449 lockingactid => l_actid
450 , assignid => csr_rec.assignment_id
451 , pactid => l_pactid
452 , chunk => l_chunk_number
453 , greid => NULL
454 , prepayid => NULL
455 , status => 'C'
456 , source_act => p_assactid);
457
458 hr_utility.set_location('creating lock3 ' ||
459 l_actid || ' to ' ||
460 csr_child_rec.child_assignment_action_id,30);
461
462 hr_nonrun_asact.insint(
463 lockingactid => l_actid
464 , lockedactid => csr_child_rec.child_assignment_action_id);
465
466 l_action_context_id := l_actid;
467
468 END IF;
469
470 IF csr_child_rec.run_type = 'NP'
471
472 THEN
473
474 if (l_standard_asg_act_id is null) then
475 --
476 SELECT pay_assignment_actions_s.NEXTVAL
477 INTO l_actid
478 FROM dual;
479
480 hr_utility.set_location('csr_child_rec.run_type = ' ||
481 csr_child_rec.run_type,30);
482 hr_utility.set_location('csr_rec.master_assignment_action_id = ' ||
483 csr_rec.master_assignment_action_id,30);
484
485 hr_nonrun_asact.insact(
486 lockingactid => l_actid
487 , assignid => csr_rec.assignment_id
488 , pactid => l_pactid
489 , chunk => l_chunk_number
490 , greid => NULL
491 , prepayid => NULL
492 , status => 'C'
493 , source_act => p_assactid);
494 --
495 l_standard_asg_act_id := l_actid;
496 --
497 else
498 --
499 l_actid := l_standard_asg_act_id;
500 --
501 end if;
502 --
503 FOR csr_np_rec IN csr_np_children(csr_rec.master_assignment_action_id,
504 csr_rec.pay_payroll_action_id,
505 csr_rec.assignment_id,
506 csr_rec.effective_date)
507
508 LOOP
509
510 hr_utility.set_location('creating lock4 ' ||
511 l_actid || ' to ' || csr_np_rec.np_assignment_action_id,30);
512
513 hr_nonrun_asact.insint(
514 lockingactid => l_actid
515 , lockedactid => csr_np_rec.np_assignment_action_id);
516
517 END LOOP;
518
519 END IF;
520
521 END LOOP; -- child assignment actions
522
523
524 END LOOP;
525 hr_utility.set_location('Leaving '|| l_proc,80);
526
527 END generate_child_actions;
528
529 END pay_core_payslip_utils;