1 PACKAGE BODY pay_payact_pkg as
2 /* $Header: pypayact.pkb 120.1.12010000.2 2008/08/06 08:10:40 ubhat ship $ */
3 /*
4 ******************************************************************
5 * *
6 * Copyright (C) 1996 Oracle Corporation. *
7 * All rights reserved. *
8 * *
9 * This material has been provided pursuant to an agreement *
10 * containing restrictions on its use. The material is also *
11 * protected by copyright law. No part of this material may *
12 * be copied or distributed, transmitted or transcribed, in *
13 * any form or by any means, electronic, mechanical, magnetic, *
14 * manual, or otherwise, or disclosed to third parties without *
15 * the express written permission of Oracle Corporation, *
16 * 500 Oracle Parkway, Redwood City, CA, 94065. *
17 * *
18 ******************************************************************
19
20 Name : pay_payact_pkg
21
22 Description : This package defines the cursors needed to run
23 Payroll Activity Report for Multi-Threaded
24
25 Note : For all the different action type there is a sqlstr in the
26 range cursor and action creation cursor. This is required
27 because in the Activity report before the report runs we
28 insert the number of records per thread in the table
29 pay_us_rpt_totals which is used to get Rpt Seq Id. If the
30 report is run for a specific action type this Id will not
31 show correct value.
32
33 Change List
34 -----------
35 Date Name Vers Description
36 ----------- ---------- ----- ----------------------------
37 05-APR-1999 meshah 40.0/110.0 created
38 04-AUG-1999 rmonge 40.0/110.1 Made package body adchkdrv
39 compliant.
40 26-SEP-2000 sravuri 115.2 Added Assignment Set
41 functionality to the package.
42 13-APR-2001 ahanda 115.3 Changed HR_LOCATIONS to
43 HR_LOCATIONS_ALL.
44 26-apr-2001 tclewis 115.4 modified the cursor(s) in the
45 range_cursor and action creation
46 to use secure views. Modified
47 the sql query in the sort_code
48 routine to use base tables.
49 21-oct-2002 tclewis 115.5 commented out the "for update..."
50 in the action_creation cursor.
51 changed the locking on the sort_cursor
52 from paf.assignment_id to paa.
53 16-SEP-2003 sdahiya 115.7 modified the sort_action procedure
54 (Bug# 3037633).Added nocopy changes
55 16-OCT-2003 sdahiya 115.8 Modified sort_action procedure so that
56 it sorts data first on employee name
57 and later on date paid (Bug 3037633).
58 09-FEB-2004 ssmukher 115.9 11.5.10 Performance Fix (Bug 3372732)
59 in action_creation
60 23-AUG-2005 jgoswami 115.10 R12 Performance Fix (Bug 4347329)
61 in range_creation
62 16-Jun-2008 pannapur 115.11 Modified the cursor definitions of all
63 process types to generate proper sequence id
64 (6854964)
65
66 */
67
68 -------------------------------- range_cursor ----------------------------------
69 PROCEDURE range_cursor (pactid in number,
70 sqlstr out nocopy varchar2) is
71
72 --
73 leg_param pay_payroll_actions.legislative_parameters%type;
74
75 l_business_group_id number;
76 l_consolidation_set_id number;
77 l_payroll_id number;
78 l_organization_id number;
79 l_location_id number;
80 l_person_id number;
81 l_leg_start_date date;
82 l_leg_end_date date;
83
84 pay_process varchar2(40);
85 l_payroll_text varchar2(70);
86 l_consolidation_set_text varchar2(50);
87 --
88
89 begin
90 select legislative_parameters
91 into leg_param
92 from pay_payroll_actions ppa
93 where ppa.payroll_action_id = pactid;
94
95 -- pay_process := pay_payact_pkg.get_parameter('P_P_TY',leg_param);
96
97 select ppa.legislative_parameters,
98 pay_payrg_pkg.get_parameter('P_P_TY', ppa.legislative_parameters),
99 pay_payrg_pkg.get_parameter('C_ST_ID', ppa.legislative_parameters),
100 pay_payrg_pkg.get_parameter('PY_ID', ppa.legislative_parameters),
101 pay_payrg_pkg.get_parameter('O_ID', ppa.legislative_parameters),
102 pay_payrg_pkg.get_parameter('L_ID', ppa.legislative_parameters),
103 pay_payrg_pkg.get_parameter('P_ID', ppa.legislative_parameters),
104 ppa.start_date,
105 ppa.effective_date,
106 ppa.business_group_id
107 into leg_param,
108 pay_process,
109 l_consolidation_set_id,
110 l_payroll_id,
111 l_organization_id,
112 l_location_id,
113 l_person_id,
114 l_leg_start_date,
115 l_leg_end_date,
116 l_business_group_id
117 from pay_payroll_actions ppa
118 where ppa.payroll_action_id = pactid;
119
120 IF l_consolidation_set_id is not null THEN
121
122 l_consolidation_set_text := 'and pa1.consolidation_set_id = ' || to_char(l_consolidation_set_id) ;
123
124 ELSE
125
126 l_consolidation_set_text := NULL;
127
128 END IF;
129
130 IF l_payroll_id is not null THEN
131
132 l_payroll_text := 'and pa1.payroll_id = ' || to_char(l_payroll_id) ;
133
134 ELSE
135
136 l_payroll_text := null;
137
138 END IF;
139
140 /* pay act */
141
142 -- if pay process type (P_P_TY) is balance adjustement
143 -- if pay_process = 'BA' then
144 -- action_type = 'B'
145 -- if pay process type (P_P_TY) is balance initialization
146 -- if pay_process = 'BI' then
147 -- action_type = 'I'
148 -- if pay process type (P_P_TY) is balance adjustement and balance initilization
149 -- if pay_process = 'BAI' then
150 -- action_type in ('B','I')
151 -- if P_P_TY is RUN
152 -- if pay_process = 'PR' then
153 -- action_type = 'R'
154 -- if P_P_TY is Quick Pay
155 -- if pay_process = 'QP' then
156 -- action_type = 'Q'
157 -- if P_P_TY is RUN and Quick Pay
158 -- if pay_process = 'PRQP' then
159 -- action_type in ('R','Q')
160 -- if P_P_TY is Reversal
161 -- if pay_process = 'REV' then
162 -- action_type = 'V'
163 -- if pay_process = 'ALL' then
164 -- if P_P_TY is ALL then
165 -- action_type in ('B','D','I','R','Q','V')
166
167 -- Modified to a single sql statement with dynamic selection criteria
168 -- for payroll and consolidation set
169
170 sqlstr :=
171 'select distinct asg.person_id
172 from pay_payroll_actions ppa,
173 pay_payroll_actions pa1,
174 pay_assignment_actions act,
175 per_assignments_f asg
176 where ppa.payroll_action_id = :payroll_action_id
177 '||l_consolidation_set_text||'
178 '||l_payroll_text||'
179 and pa1.effective_date between ppa.start_date
180 and ppa.effective_date
181 and pa1.effective_date between asg.effective_start_date
182 and asg.effective_end_date
183 and pa1.action_type in (''B'',''D'',''I'',''R'',''Q'',''V'')
184 and pa1.payroll_action_id = act.payroll_action_id
185 and asg.assignment_id = act.assignment_id
186 and act.action_status = ''C''
187 and asg.organization_id = nvl('''||l_organization_id||''',
188 asg.organization_id)
189 and asg.location_id = nvl('''||l_location_id||''',
190 asg.location_id)
191 and asg.person_id = nvl('''||l_person_id||''',
192 asg.person_id)
193 and asg.business_group_id +0 = ppa.business_group_id
194 order by asg.person_id';
195
196 end range_cursor;
197
198 ---------------------------------- action_creation ----------------------------------
199 PROCEDURE action_creation(pactid in number,
200 stperson in number,
201 endperson in number,
202 chunk in number) is
203
204 -- Bug 3372732 : cursor created to fetch legislative parameter values for
205 -- Payroll Activity generated Payroll Action
206 -- All the values will be passed to cursors below
207
208 CURSOR c_inputs(pactid number) is -- Bug 3372732
209 select pay_payrg_pkg.get_parameter('PY_ID',ppa.legislative_parameters) payroll_id,
210 pay_payrg_pkg.get_parameter('C_ST_ID',ppa.legislative_parameters) consolidation_set_id,
211 pay_payrg_pkg.get_parameter('T_U_ID',ppa.legislative_parameters) tax_unit_id,
212 pay_payrg_pkg.get_parameter('L_ID',ppa.legislative_parameters) location_id,
213 pay_payrg_pkg.get_parameter('O_ID',ppa.legislative_parameters) organization_id,
214 pay_payrg_pkg.get_parameter('P_ID',ppa.legislative_parameters) person_id,
215 pay_payrg_pkg.get_parameter('B_G_ID',ppa.legislative_parameters) business_group_id,
216 ppa.start_date start_date,
217 ppa.effective_date effective_date
218 from pay_payroll_actions ppa
219 where ppa.payroll_action_id = pactid;
220
221
222 -- Bug 3372732 : All cursors defined below are changed
223 -- to include pay_payrolls_f and inputs from
224 -- cursor c_inputs .
225
226 CURSOR c_bal_adj
227 (
228 c_stperson number,
229 c_endperson number,
230 c_payroll_id number,
231 c_consolidation_set_id number,
232 c_tax_unit_id number,
233 c_location_id number,
234 c_organization_id number,
235 c_person_id number,
236 c_business_group_id number,
237 c_start_date date,
238 c_effective_date date
239 ) is --Bug 3372732
240 select act.assignment_action_id,
241 act.assignment_id,
242 act.tax_unit_id
243 from pay_assignment_actions act,
244 per_assignments_f paf,
245 pay_payroll_actions ppa, /* pre-payments and reversals
246 payroll action id */
247 pay_payrolls_f ppf -- Bug 3372732
248 where ppa.payroll_id = nvl(c_payroll_id,ppa.payroll_id)
249 and ppa.consolidation_set_id + 0 = nvl(c_consolidation_set_id,
250 ppa.consolidation_set_id)
251 and ppa.effective_date between c_start_date
252 and c_effective_date
253 and act.tax_unit_id = nvl(c_tax_unit_id ,act.tax_unit_id)
254 and paf.organization_id = nvl(c_organization_id,paf.organization_id)
255 and paf.location_id = nvl(c_location_id,paf.location_id)
256 and paf.person_id = nvl(c_person_id,paf.person_id)
257 and paf.business_group_id + 0 = c_business_group_id
258 and ppa.action_type = 'B'
259 and act.action_status = 'C'
260 and act.payroll_action_id = ppa.payroll_action_id
261 and paf.assignment_id = act.assignment_id
262 and ppa.effective_date between paf.effective_start_date
263 and paf.effective_end_date
264 and paf.person_id between stperson and endperson
265 and ppa.payroll_id = ppf.payroll_id -- Bug 3372732
266 and ppa.effective_date between ppf.effective_start_date
267 and ppf.effective_end_date
268 and ppf.payroll_id >= 0
269 --added for bug 6854964
270 AND ((nvl(act.run_type_id, ppa.run_type_id) is null and
271 act.source_action_id is null)
272 or (nvl(act.run_type_id, ppa.run_type_id) is not null and
273 act.source_action_id is not null )
274 or (ppa.action_type = 'V' and ppa.run_type_id is null and
275 act.run_type_id is not null and
276 act.source_action_id is null))
277 --end of addition
278 ORDER BY act.assignment_action_id;
279
280
281
282 CURSOR c_bal_ini
283 (
284 c_stperson number,
285 c_endperson number,
286 c_payroll_id number,
287 c_consolidation_set_id number,
288 c_tax_unit_id number,
289 c_location_id number,
290 c_organization_id number,
291 c_person_id number,
292 c_business_group_id number,
293 c_start_date date,
294 c_effective_date date
295 ) is -- Bug 3372737
296 select act.assignment_action_id,
297 act.assignment_id,
298 act.tax_unit_id
299 from pay_assignment_actions act,
300 per_assignments_f paf,
301 pay_payroll_actions ppa, /* pre-payments and reversals payroll action id */
302 pay_payrolls_f ppf -- Bug 3372732
303 where ppa.payroll_id = nvl(c_payroll_id, ppa.payroll_id)
304 and ppa.consolidation_set_id +0 =
305 nvl(c_consolidation_set_id, ppa.consolidation_set_id)
306 and ppa.effective_date between c_start_date and c_effective_date
307 and act.tax_unit_id = nvl(c_tax_unit_id,act.tax_unit_id)
308 and paf.organization_id = nvl(c_organization_id,
309 paf.organization_id)
310 and paf.location_id = nvl(c_location_id, paf.location_id)
311 and paf.person_id = nvl(c_person_id, paf.person_id)
312 and paf.business_group_id +0 = c_business_group_id
313 and ppa.action_type = 'I'
314 and act.action_status = 'C'
315 and act.payroll_action_id = ppa.payroll_action_id
316 and paf.assignment_id = act.assignment_id
317 and ppa.effective_date between paf.effective_start_date
318 and paf.effective_end_date
319 and paf.person_id between stperson and endperson
320 and ppa.payroll_id = ppf.payroll_id -- Bug 3372732
321 and ppa.effective_date between ppf.effective_start_date
322 and ppf.effective_end_date
323 and ppf.payroll_id >= 0
324 --added for bug 6854964
325 AND ((nvl(act.run_type_id, ppa.run_type_id) is null and
326 act.source_action_id is null)
327 or (nvl(act.run_type_id, ppa.run_type_id) is not null and
328 act.source_action_id is not null )
329 or (ppa.action_type = 'V' and ppa.run_type_id is null and
330 act.run_type_id is not null and
331 act.source_action_id is null))
332 --end of addition
333 ORDER BY act.assignment_action_id;
334 -- for update of paf.assignment_id;
335
336 CURSOR c_bal_adj_ini
337 (
338 c_stperson number,
339 c_endperson number,
340 c_payroll_id number,
341 c_consolidation_set_id number,
342 c_tax_unit_id number,
343 c_location_id number,
344 c_organization_id number,
345 c_person_id number,
346 c_business_group_id number,
347 c_start_date date,
348 c_effective_date date
349
350 ) is
351 select act.assignment_action_id,
352 act.assignment_id,
353 act.tax_unit_id
357 pay_payrolls_f ppf -- Bug 3372732
354 from pay_assignment_actions act,
355 per_assignments_f paf,
356 pay_payroll_actions ppa, /* pre-payments and reversals payroll action id */
358 where ppa.payroll_id =
359 nvl(c_payroll_id, ppa.payroll_id)
360 and ppa.consolidation_set_id +0 =
361 nvl(c_consolidation_set_id, ppa.consolidation_set_id)
362 and ppa.effective_date between c_start_date and c_effective_date
363 and act.tax_unit_id = nvl(c_tax_unit_id,act.tax_unit_id)
364 and paf.organization_id = nvl(c_organization_id, paf.organization_id)
365 and paf.location_id = nvl(c_location_id, paf.location_id)
366 and paf.person_id = nvl(c_person_id, paf.person_id)
367 and paf.business_group_id +0 = c_business_group_id
368 and ppa.action_type in ('B','I')
369 and act.action_status = 'C'
370 and act.payroll_action_id = ppa.payroll_action_id
371 and paf.assignment_id = act.assignment_id
372 and ppa.effective_date between paf.effective_start_date
373 and paf.effective_end_date
374 and paf.person_id between stperson and endperson
375 and ppa.payroll_id = ppf.payroll_id -- Bug 3372732
376 and ppa.effective_date between ppf.effective_start_date
377 and ppf.effective_end_date
378 and ppf.payroll_id >= 0
379 --added for bug 6854964
380 AND ((nvl(act.run_type_id, ppa.run_type_id) is null and
381 act.source_action_id is null)
382 or (nvl(act.run_type_id, ppa.run_type_id) is not null and
383 act.source_action_id is not null )
384 or (ppa.action_type = 'V' and ppa.run_type_id is null and
385 act.run_type_id is not null and
386 act.source_action_id is null))
387 --end of addition
388 ORDER BY act.assignment_action_id;
389 -- for update of paf.assignment_id;
390
391 CURSOR c_run
392 (
393 c_stperson number,
394 c_endperson number,
395 c_payroll_id number,
396 c_consolidation_set_id number,
397 c_tax_unit_id number,
398 c_location_id number,
399 c_organization_id number,
400 c_person_id number,
401 c_business_group_id number,
402 c_start_date date,
403 c_effective_date date
404
405 ) is
406 select act.assignment_action_id,
407 act.assignment_id,
408 act.tax_unit_id
409 from pay_assignment_actions act,
410 per_assignments_f paf,
411 pay_payroll_actions ppa, /* pre-payments and reversals */
412 /* payroll action id */
413 pay_payrolls_f ppf -- Bug 3372732
414 where ppa.payroll_id = nvl(c_payroll_id, ppa.payroll_id)
415 and ppa.consolidation_set_id +0 = nvl(c_consolidation_set_id,
416 ppa.consolidation_set_id)
417 and ppa.effective_date between c_start_date and c_effective_date
418 and act.tax_unit_id = nvl(c_tax_unit_id,act.tax_unit_id)
419 and paf.organization_id= nvl(c_organization_id, paf.organization_id)
420 and paf.location_id = nvl(c_location_id, paf.location_id)
421 and paf.person_id = nvl(c_person_id, paf.person_id)
422 and paf.business_group_id +0 = c_business_group_id
423 and ppa.action_type = 'R'
424 and act.action_status = 'C'
425 and act.payroll_action_id = ppa.payroll_action_id
426 and paf.assignment_id = act.assignment_id
427 and ppa.effective_date between paf.effective_start_date
428 and paf.effective_end_date
429 and paf.person_id between stperson and endperson
430 and ppa.payroll_id = ppf.payroll_id -- Bug3372732
431 and ppa.effective_date between ppf.effective_start_date
432 and ppf.effective_end_date
433 and ppf.payroll_id >= 0
434 --added for bug 6854964
435 AND ((nvl(act.run_type_id, ppa.run_type_id) is null and
436 act.source_action_id is null)
437 or (nvl(act.run_type_id, ppa.run_type_id) is not null and
438 act.source_action_id is not null )
439 or (ppa.action_type = 'V' and ppa.run_type_id is null and
440 act.run_type_id is not null and
441 act.source_action_id is null))
442 --end of addition
443 ORDER BY act.assignment_action_id;
444 -- for update of paf.assignment_id;
445
446 CURSOR c_qp
447 (
448 c_stperson number,
449 c_endperson number,
450 c_payroll_id number,
451 c_consolidation_set_id number,
452 c_tax_unit_id number,
453 c_location_id number,
454 c_organization_id number,
455 c_person_id number,
456 c_business_group_id number,
457 c_start_date date,
458 c_effective_date date
459
460 ) is
461 select act.assignment_action_id,
462 act.assignment_id,
463 act.tax_unit_id
464 from pay_assignment_actions act,
465 per_assignments_f paf,
466 pay_payroll_actions ppa, /* pre-payments and */
467 /* reversals payroll action id */
468 pay_payrolls_f ppf -- Bug 3372732
472 and ppa.effective_date between c_start_date and c_effective_date
469 where ppa.payroll_id = nvl(c_payroll_id, ppa.payroll_id)
470 and ppa.consolidation_set_id +0 = nvl(c_consolidation_set_id,
471 ppa.consolidation_set_id)
473 and act.tax_unit_id = nvl(c_tax_unit_id,act.tax_unit_id)
474 and paf.organization_id = nvl(c_organization_id,
475 paf.organization_id)
476 and paf.location_id = nvl(c_location_id, paf.location_id)
477 and paf.person_id = nvl(c_person_id, paf.person_id)
478 and paf.business_group_id +0 = c_business_group_id
479 and ppa.action_type = 'Q'
480 and act.action_status = 'C'
481 and act.payroll_action_id = ppa.payroll_action_id
482 and paf.assignment_id = act.assignment_id
483 and ppa.effective_date between paf.effective_start_date
484 and paf.effective_end_date
485 and paf.person_id between stperson and endperson
486 and ppf.payroll_id = ppa.payroll_id -- Bug 3372732
487 and ppa.effective_date between ppf.effective_start_date
488 and ppf.effective_end_date
489 and ppf.payroll_id >= 0
490 --added for bug 6854964
491 AND ((nvl(act.run_type_id, ppa.run_type_id) is null and
492 act.source_action_id is null)
493 or (nvl(act.run_type_id, ppa.run_type_id) is not null and
494 act.source_action_id is not null )
495 or (ppa.action_type = 'V' and ppa.run_type_id is null and
496 act.run_type_id is not null and
497 act.source_action_id is null))
498 --end of addition
499 ORDER BY act.assignment_action_id;
500 -- for update of paf.assignment_id;
501
502 CURSOR c_run_qp
503 (
504 c_stperson number,
505 c_endperson number,
506 c_payroll_id number,
507 c_consolidation_set_id number,
508 c_tax_unit_id number,
509 c_location_id number,
510 c_organization_id number,
511 c_person_id number,
512 c_business_group_id number,
513 c_start_date date,
514 c_effective_date date
515 ) is -- Bug 3372732
516 select act.assignment_action_id,
517 act.assignment_id,
518 act.tax_unit_id
519 from pay_assignment_actions act,
520 per_assignments_f paf,
521 pay_payroll_actions ppa, /* pre-payments and */
522 /* reversals payroll action id */
523 pay_payrolls_f ppf
524 where ppa.payroll_id = nvl(c_payroll_id, ppa.payroll_id)
525 and ppa.consolidation_set_id +0 = nvl(c_consolidation_set_id,
526 ppa.consolidation_set_id)
527 and ppa.effective_date between c_start_date and c_effective_date
528 and act.tax_unit_id = nvl(c_tax_unit_id,act.tax_unit_id)
529 and paf.organization_id = nvl(c_organization_id ,paf.organization_id)
530 and paf.location_id = nvl(c_location_id, paf.location_id)
531 and paf.person_id = nvl(c_person_id, paf.person_id)
532 and paf.business_group_id +0 = c_business_group_id
533 and ppa.action_type in ('R','Q')
534 and act.action_status = 'C'
535 and act.payroll_action_id = ppa.payroll_action_id
536 and paf.assignment_id = act.assignment_id
537 and ppa.effective_date between paf.effective_start_date
538 and paf.effective_end_date
539 and paf.person_id between stperson and endperson
540 and ppa.payroll_id = ppf.payroll_id -- Bug 3372732
541 and ppa.effective_date between ppf.effective_start_date
542 and ppf.effective_end_date
543 and ppf.payroll_id >= 0
544 --added for bug 6854964
545 AND ((nvl(act.run_type_id, ppa.run_type_id) is null and
546 act.source_action_id is null)
547 or (nvl(act.run_type_id, ppa.run_type_id) is not null and
548 act.source_action_id is not null )
549 or (ppa.action_type = 'V' and ppa.run_type_id is null and
550 act.run_type_id is not null and
551 act.source_action_id is null))
552 --end of addition
553 ORDER BY act.assignment_action_id;
554 -- for update of paf.assignment_id;
555
556 CURSOR c_rev
557 (
558 c_stperson number,
559 c_endperson number,
560 c_payroll_id number,
561 c_consolidation_set_id number,
562 c_tax_unit_id number,
563 c_location_id number,
564 c_organization_id number,
565 c_person_id number,
566 c_business_group_id number,
567 c_start_date date,
568 c_effective_date date
569 ) is --Bug 3372732
570 select act.assignment_action_id,
571 act.assignment_id,
572 act.tax_unit_id
573 from pay_assignment_actions act,
574 per_assignments_f paf,
575 pay_payroll_actions ppa, /* pre-payments and */
576 /* reversals payroll action id */
577 pay_payrolls_f ppf -- Bug 3372732
578 where ppa.payroll_id =
579 nvl(c_payroll_id, ppa.payroll_id)
580 and ppa.consolidation_set_id +0 = nvl(c_consolidation_set_id,
581 ppa.consolidation_set_id)
585 and paf.location_id = nvl(c_location_id, paf.location_id)
582 and ppa.effective_date between c_start_date and c_effective_date
583 and act.tax_unit_id = nvl(c_tax_unit_id,act.tax_unit_id)
584 and paf.organization_id = nvl(c_organization_id, paf.organization_id)
586 and paf.person_id = nvl(c_person_id, paf.person_id)
587 and paf.business_group_id +0 = c_business_group_id
588 and ppa.action_type = 'V'
589 and act.action_status = 'C'
590 and act.payroll_action_id = ppa.payroll_action_id
591 and paf.assignment_id = act.assignment_id
592 and ppa.effective_date between paf.effective_start_date
593 and paf.effective_end_date
594 and paf.person_id between stperson and endperson
595 and ppa.payroll_id = ppf.payroll_id -- Bug 3372732
596 and ppa.effective_date between ppf.effective_start_date
597 and ppf.effective_end_date
598 and ppf.payroll_id >= 0
599 --added for bug 6854964
600 AND ((nvl(act.run_type_id, ppa.run_type_id) is null and
601 act.source_action_id is null)
602 or (nvl(act.run_type_id, ppa.run_type_id) is not null and
603 act.source_action_id is not null )
604 or (ppa.action_type = 'V' and ppa.run_type_id is null and
605 act.run_type_id is not null and
606 act.source_action_id is null))
607 --end of addition
608 ORDER BY act.assignment_action_id;
609 -- for update of paf.assignment_id;
610
611 CURSOR c_all
612 (
613 c_stperson number,
614 c_endperson number,
615 c_payroll_id number,
616 c_consolidation_set_id number,
617 c_tax_unit_id number,
618 c_location_id number,
619 c_organization_id number,
620 c_person_id number,
621 c_business_group_id number,
622 c_start_date date,
623 c_effective_date date
624 ) is -- Bug 3372732
625 select act.assignment_action_id,
626 act.assignment_id,
627 act.tax_unit_id
628 from pay_assignment_actions act,
629 per_assignments_f paf,
630 pay_payroll_actions ppa, /* pre-payments and */
631 /* reversals payroll action id */
632 pay_payrolls_f ppf
633 where ppa.payroll_id = nvl(c_payroll_id, ppa.payroll_id)
634 and ppa.consolidation_set_id +0 = nvl(c_consolidation_set_id ,ppa.consolidation_set_id)
635 and ppa.effective_date between c_start_date and c_effective_date
636 and act.tax_unit_id = nvl(c_tax_unit_id,act.tax_unit_id)
637 and paf.organization_id = nvl(c_organization_id,
638 paf.organization_id)
639 and paf.location_id = nvl(c_location_id, paf.location_id)
640 and paf.person_id = nvl(c_person_id, paf.person_id)
641 and paf.business_group_id +0 = c_business_group_id
642 and ppa.action_type in ('B','D','I','R','Q','V')
643 and act.action_status = 'C'
644 and act.payroll_action_id = ppa.payroll_action_id
645 and paf.assignment_id = act.assignment_id
646 and ppa.effective_date between paf.effective_start_date
647 and paf.effective_end_date
648 and paf.person_id between stperson and endperson
649 and ppa.payroll_id = ppf.payroll_id -- Bug 3372732
650 and ppa.effective_date between ppf.effective_start_date
651 and ppf.effective_end_date
652 and ppf.payroll_id >= 0
653 --added for bug 6854964
654 AND ((nvl(act.run_type_id, ppa.run_type_id) is null and
655 act.source_action_id is null)
656 or (nvl(act.run_type_id, ppa.run_type_id) is not null and
657 act.source_action_id is not null )
658 or (ppa.action_type = 'V' and ppa.run_type_id is null and
659 act.run_type_id is not null and
660 act.source_action_id is null))
661 --end of addition
662 ORDER BY act.assignment_action_id;
663 -- for update of paf.assignment_id;
664
665 --
666 lockingactid number;
667 lockedactid number;
668 assignid number;
669 greid number;
670 num number;
671 process_type varchar2(20);
672
673 -- Bug 3372732
674 leg_param pay_payroll_actions.legislative_parameters%type;
675 ass_set_id number;
676 ass_flag varchar2(2);
677
678 --
679 l_payroll_id pay_payroll_actions.payroll_id%TYPE;
680 l_location_id per_all_assignments_f.location_id%TYPE;
681 l_consolidation_set_id pay_payroll_actions.consolidation_set_id%TYPE;
682 l_tax_unit_id pay_assignment_actions.tax_unit_id%TYPE;
683 l_person_id per_all_assignments_f.person_id%TYPE;
684 l_business_group_id per_all_assignments_f.business_group_id%TYPE;
685 l_organization_id per_all_assignments_f.organization_id%TYPE;
686 l_start_date pay_payroll_actions.effective_date%TYPE;
687 l_effective_date pay_payroll_actions.effective_date%TYPE;
688
689 -- algorithm is quite similar to the other process cases,
690 -- but we have to take into account assignments and
691 -- personal payment methods.
692 begin
693
694 hr_utility.set_location('procpyr',1);
695
696 select legislative_parameters into leg_param
697 from pay_payroll_actions
701 open c_inputs(pactid);
698 where payroll_action_id = pactid;
699
700 -- Bug 3372732:Fetching the Input parameters that are passed to other cursors
702
703 fetch c_inputs into l_payroll_id,
704 l_consolidation_set_id,
705 l_tax_unit_id,
706 l_location_id,
707 l_organization_id,
708 l_person_id,
709 l_business_group_id ,
710 l_start_date,
711 l_effective_date;
712 close c_inputs;
713 process_type := pay_payact_pkg.get_parameter('P_P_TY',leg_param) ;
714
715 -- BALANCE AJUSTMENT
716 if process_type = 'BA' then
717
718 -- Bug 3372732 : Passing values from the c_input Cursor
719 open c_bal_adj( stperson,
720 endperson,
721 l_payroll_id,
722 l_consolidation_set_id,
723 l_tax_unit_id,
724 l_location_id,
725 l_organization_id,
726 l_person_id,
727 l_business_group_id,
728 l_start_date,
729 l_effective_date);
730 loop
731 hr_utility.set_location('procpyr',2);
732
733 fetch c_bal_adj into lockedactid,assignid,greid;
734 if c_bal_adj%found then
735 num := num + 1;
736 end if;
737 exit when c_bal_adj%notfound;
738
739 -- we should include the assignment_set_id to the new version for
740 -- dynamic assignment_set_id
741 -- Assignment set funtionality starts here and ends before the
742 -- endloop of this cur
743
744 ass_set_id := pay_payact_pkg.get_parameter('PASID',leg_param);
745
746 -- Generating the assignment actions only for assignment where
747 -- Assignment_flag = Y
748 if ass_set_id is not null then
749
750 ass_flag := hr_assignment_set.assignment_in_set(ass_set_id,assignid);
751
752 If ass_flag = 'Y' then
753
754 hr_utility.set_location('procpyr',3);
755 select pay_assignment_actions_s.nextval
756 into lockingactid
757 from dual;
758
759 -- insert the action record.
760 hr_nonrun_asact.insact(lockingactid,assignid,pactid,chunk,greid);
761
762 -- insert an interlock to this action.
763 hr_nonrun_asact.insint(lockingactid,lockedactid);
764
765 End if;
766 else
767
768 hr_utility.set_location('procpyr',30);
769 select pay_assignment_actions_s.nextval
770 into lockingactid
771 from dual;
772
773 -- insert the action record.
774 hr_nonrun_asact.insact(lockingactid,assignid,pactid,chunk,greid);
775
776 -- insert an interlock to this action.
777 hr_nonrun_asact.insint(lockingactid,lockedactid);
778 end if;
779
780 end loop;
781
782 close c_bal_adj;
783
784 end if; /* 'BA' */
785
786 -- BALANCE INITIALIZATION
787 if process_type = 'BI' then
788
789 open c_bal_ini( stperson,
790 endperson,
791 l_payroll_id,
792 l_consolidation_set_id,
793 l_tax_unit_id,
794 l_location_id,
795 l_organization_id,
796 l_person_id,
797 l_business_group_id,
798 l_start_date,
799 l_effective_date);
800 loop
801 hr_utility.set_location('procpyr',2);
802
803 fetch c_bal_ini into lockedactid,assignid,greid;
804 if c_bal_ini%found then
805 num := num + 1;
806 end if;
807 exit when c_bal_ini%notfound;
808
809 -- we should include the assignment_set_id to the new version for
810 -- dynamic assignment_set_id
811 -- Assignment set funtionality starts here and ends before the
812 -- endloop of this cur
813
814
815 ass_set_id := pay_payact_pkg.get_parameter('PASID',leg_param);
816
817 -- Generating the assignment actions only for assignment where
818 -- Assignment_flag = Y
819 if ass_set_id is not null then
820
821 ass_flag := hr_assignment_set.assignment_in_set(ass_set_id,assignid);
822
823 If ass_flag = 'Y' then
824
825 hr_utility.set_location('procpyr',3);
826 select pay_assignment_actions_s.nextval
827 into lockingactid
828 from dual;
829
830 -- insert the action record.
831 hr_nonrun_asact.insact(lockingactid,assignid,pactid,chunk,greid);
832
833 -- insert an interlock to this action.
834 hr_nonrun_asact.insint(lockingactid,lockedactid);
835
836 end if;
837 else
838 hr_utility.set_location('procpyr',3);
839 select pay_assignment_actions_s.nextval
840 into lockingactid
841 from dual;
842
843 -- insert the action record.
844 hr_nonrun_asact.insact(lockingactid,assignid,pactid,chunk,greid);
845
849
846 -- insert an interlock to this action.
847 hr_nonrun_asact.insint(lockingactid,lockedactid);
848 end if;
850 end loop;
851 close c_bal_ini;
852
853 end if; /* 'BI' */
854
855 -- BALANCE ADJUST. AND INITIALIZATION
856 if process_type = 'BAI' then
857
858 open c_bal_adj_ini( stperson,
859 endperson,
860 l_payroll_id,
861 l_consolidation_set_id,
862 l_tax_unit_id,
863 l_location_id,
864 l_organization_id,
865 l_person_id,
866 l_business_group_id,
867 l_start_date,
868 l_effective_date);
869 loop
870 hr_utility.set_location('procpyr',2);
871
872 fetch c_bal_adj_ini into lockedactid,assignid,greid;
873 if c_bal_adj_ini%found then
874 num := num + 1;
875 end if;
876 exit when c_bal_adj_ini%notfound;
877
878 -- we should include the assignment_set_id to the new version for
879 -- dynamic assignment_set_id
880 -- Assignment set funtionality starts here and ends before the
881 -- endloop of this cur
882
883
884 ass_set_id := pay_payact_pkg.get_parameter('PASID',leg_param);
885
886 -- Generating the assignment actions only for assignment where
887 -- Assignment_flag = Y
888 if ass_set_id is not null then
889
890 ass_flag := hr_assignment_set.assignment_in_set(ass_set_id,assignid);
891
892 If ass_flag = 'Y' then
893
894 hr_utility.set_location('procpyr',3);
895 select pay_assignment_actions_s.nextval
896 into lockingactid
897 from dual;
898
899 -- insert the action record.
900 hr_nonrun_asact.insact(lockingactid,assignid,pactid,chunk,greid);
901
902 -- insert an interlock to this action.
903 hr_nonrun_asact.insint(lockingactid,lockedactid);
904
905 end if;
906 else
907 hr_utility.set_location('procpyr',3);
908 select pay_assignment_actions_s.nextval
909 into lockingactid
910 from dual;
911
912 -- insert the action record.
913 hr_nonrun_asact.insact(lockingactid,assignid,pactid,chunk,greid);
914
915 -- insert an interlock to this action.
916 hr_nonrun_asact.insint(lockingactid,lockedactid);
917 end if;
918
919 end loop;
920 close c_bal_adj_ini;
921
922 end if; /* 'BA','BI','BAI' */
923
924 -- PAYROLL RUNS
925 if process_type = 'PR' then
926
927 open c_run( stperson,
928 endperson,
929 l_payroll_id,
930 l_consolidation_set_id,
931 l_tax_unit_id,
932 l_location_id,
933 l_organization_id,
934 l_person_id,
935 l_business_group_id,
936 l_start_date,
937 l_effective_date);
938
939 loop
940 hr_utility.set_location('procpyr',2);
941
942 fetch c_run into lockedactid,assignid,greid;
943 if c_run%found then
944 num := num + 1;
945 end if;
946 exit when c_run%notfound;
947
948 -- we should include the assignment_set_id to the new version for
949 -- dynamic assignment_set_id
950 -- Assignment set funtionality starts here and ends before the
951 -- endloop of this cur
952
953
954 ass_set_id := pay_payact_pkg.get_parameter('PASID',leg_param);
955
956 -- Generating the assignment actions only for assignment where
957 -- Assignment_flag = Y
958 if ass_set_id is not null then
959 ass_flag := hr_assignment_set.assignment_in_set(ass_set_id,assignid);
960
961 If ass_flag = 'Y' then
962
963 hr_utility.set_location('procpyr',3);
964 select pay_assignment_actions_s.nextval
965 into lockingactid
966 from dual;
967
968 -- insert the action record.
969 hr_nonrun_asact.insact(lockingactid,assignid,pactid,chunk,greid);
970
971 -- insert an interlock to this action.
972 hr_nonrun_asact.insint(lockingactid,lockedactid);
973
974 end if;
975 else
976 hr_utility.set_location('procpyr',3);
977 select pay_assignment_actions_s.nextval
978 into lockingactid
979 from dual;
980
981 -- insert the action record.
982 hr_nonrun_asact.insact(lockingactid,assignid,pactid,chunk,greid);
983
984 -- insert an interlock to this action.
985 hr_nonrun_asact.insint(lockingactid,lockedactid);
986
987 end if;
988 end loop;
989 close c_run;
990
991 end if; /* 'RUN' */
992
993
994 -- QUICK PAYS
995 if process_type = 'QP' then
996
1000 l_consolidation_set_id,
997 open c_qp( stperson,
998 endperson,
999 l_payroll_id,
1001 l_tax_unit_id,
1002 l_location_id,
1003 l_organization_id,
1004 l_person_id,
1005 l_business_group_id,
1006 l_start_date,
1007 l_effective_date);
1008
1009 loop
1010 hr_utility.set_location('procpyr',2);
1011
1012 fetch c_qp into lockedactid,assignid,greid;
1013 if c_qp%found then
1014 num := num + 1;
1015 end if;
1016 exit when c_qp%notfound;
1017
1018 -- we should include the assignment_set_id to the new version for
1019 -- dynamic assignment_set_id
1020 -- Assignment set funtionality starts here and ends before the
1021 -- endloop of this cur
1022
1023
1024 ass_set_id := pay_payact_pkg.get_parameter('PASID',leg_param);
1025
1026 -- Generating the assignment actions only for assignment where
1027 -- Assignment_flag = Y
1028 if ass_set_id is not null then
1029
1030 ass_flag := hr_assignment_set.assignment_in_set(ass_set_id,assignid);
1031
1032 If ass_flag = 'Y' then
1033
1034 hr_utility.set_location('procpyr',3);
1035 select pay_assignment_actions_s.nextval
1036 into lockingactid
1037 from dual;
1038
1039 -- insert the action record.
1040 hr_nonrun_asact.insact(lockingactid,assignid,pactid,chunk,greid);
1041
1042 -- insert an interlock to this action.
1043 hr_nonrun_asact.insint(lockingactid,lockedactid);
1044
1045 end if;
1046 else
1047
1048 hr_utility.set_location('procpyr',3);
1049 select pay_assignment_actions_s.nextval
1050 into lockingactid
1051 from dual;
1052
1053 -- insert the action record.
1054 hr_nonrun_asact.insact(lockingactid,assignid,pactid,chunk,greid);
1055
1056 -- insert an interlock to this action.
1057 hr_nonrun_asact.insint(lockingactid,lockedactid);
1058
1059 end if;
1060 end loop;
1061
1062 close c_qp;
1063
1064 end if; /* 'QUICK PAY' */
1065
1066 -- PAYROLL RUNS AND QUICK PAYS
1067 if process_type = 'PRQP' then
1068
1069 open c_run_qp( stperson,
1070 endperson,
1071 l_payroll_id,
1072 l_consolidation_set_id,
1073 l_tax_unit_id,
1074 l_location_id,
1075 l_organization_id,
1076 l_person_id,
1077 l_business_group_id,
1078 l_start_date,
1079 l_effective_date);
1080
1081 loop
1082 hr_utility.set_location('procpyr',2);
1083
1084 fetch c_run_qp into lockedactid,assignid,greid;
1085 if c_run_qp%found then
1086 num := num + 1;
1087 end if;
1088 exit when c_run_qp%notfound;
1089
1090 -- we should include the assignment_set_id to the new version for
1091 -- dynamic assignment_set_id
1092 -- Assignment set funtionality starts here and ends before the
1093 -- endloop of this cur
1094
1095
1096 ass_set_id := pay_payact_pkg.get_parameter('PASID',leg_param);
1097
1098 -- Generating the assignment actions only for assignment where
1099 -- Assignment_flag = Y
1100 if ass_set_id is not null then
1101
1102 ass_flag := hr_assignment_set.assignment_in_set(ass_set_id,assignid);
1103
1104 If ass_flag = 'Y' then
1105
1106 hr_utility.set_location('procpyr',3);
1107 select pay_assignment_actions_s.nextval
1108 into lockingactid
1109 from dual;
1110
1111 -- insert the action record.
1112 hr_nonrun_asact.insact(lockingactid,assignid,pactid,chunk,greid);
1113
1114 -- insert an interlock to this action.
1115 hr_nonrun_asact.insint(lockingactid,lockedactid);
1116
1117 end if;
1118 else
1119
1120 hr_utility.set_location('procpyr',3);
1121 select pay_assignment_actions_s.nextval
1122 into lockingactid
1123 from dual;
1124 --
1125 -- insert the action record.
1126 hr_nonrun_asact.insact(lockingactid,assignid,pactid,chunk,greid);
1127 --
1128 -- insert an interlock to this action.
1129 hr_nonrun_asact.insint(lockingactid,lockedactid);
1130 end if;
1131
1132 end loop;
1133 close c_run_qp;
1134
1135 end if; /* 'RUN','QP','RUN and QP' */
1136
1137
1138 if process_type = 'REV' then
1139
1140 open c_rev( stperson,
1141 endperson,
1142 l_payroll_id,
1143 l_consolidation_set_id,
1144 l_tax_unit_id,
1145 l_location_id,
1146 l_organization_id,
1147 l_person_id,
1151
1148 l_business_group_id,
1149 l_start_date,
1150 l_effective_date);
1152 loop
1153 hr_utility.set_location('procpyr',2);
1154
1155 fetch c_rev into lockedactid,assignid,greid;
1156 if c_rev%found then
1157 num := num + 1;
1158 end if;
1159 exit when c_rev%notfound;
1160
1161
1162 -- we should include the assignment_set_id to the new version for
1163 -- dynamic assignment_set_id
1164 -- Assignment set funtionality starts here and ends before the
1165 -- endloop of this cur
1166
1167
1168 ass_set_id := pay_payact_pkg.get_parameter('PASID',leg_param);
1169
1170 -- Generating the assignment actions only for assignment where
1171 -- Assignment_flag = Y
1172 if ass_set_id is not null then
1173 ass_flag := hr_assignment_set.assignment_in_set(ass_set_id,assignid);
1174
1175 If ass_flag = 'Y' then
1176
1177 hr_utility.set_location('procpyr',3);
1178 select pay_assignment_actions_s.nextval
1179 into lockingactid
1180 from dual;
1181
1182 -- insert the action record.
1183 hr_nonrun_asact.insact(lockingactid,assignid,pactid,chunk,greid);
1184
1185 -- insert an interlock to this action.
1186 hr_nonrun_asact.insint(lockingactid,lockedactid);
1187 end if;
1188 else
1189 hr_utility.set_location('procpyr',3);
1190 select pay_assignment_actions_s.nextval
1191 into lockingactid
1192 from dual;
1193
1194 -- insert the action record.
1195 hr_nonrun_asact.insact(lockingactid,assignid,pactid,chunk,greid);
1196
1197 -- insert an interlock to this action.
1198 hr_nonrun_asact.insint(lockingactid,lockedactid);
1199 end if;
1200 end loop;
1201 close c_rev;
1202
1203 end if; /* 'REV' */
1204
1205
1206 if process_type = 'ALL' then
1207
1208 open c_all( stperson,
1209 endperson,
1210 l_payroll_id,
1211 l_consolidation_set_id,
1212 l_tax_unit_id,
1213 l_location_id,
1214 l_organization_id,
1215 l_person_id,
1216 l_business_group_id,
1217 l_start_date,
1218 l_effective_date);
1219 num := 0;
1220 loop
1221 hr_utility.set_location('procpyr',2);
1222
1223 fetch c_all into lockedactid,assignid,greid;
1224
1225 if c_all%found then
1226 num := num + 1;
1227 end if;
1228 exit when c_all%notfound;
1229
1230 -- we should include the assignment_set_id to the new version for
1231 -- dynamic assignment_set_id
1232 -- Assignment set funtionality starts here and ends before the
1233 -- endloop of this cur
1234
1235
1236 ass_set_id := pay_payact_pkg.get_parameter('PASID',leg_param);
1237
1238 -- Generating the assignment actions only for assignment where
1239 -- Assignment_flag = Y
1240 if ass_set_id is not null then
1241
1242 ass_flag := hr_assignment_set.assignment_in_set(ass_set_id,assignid);
1243
1244 If ass_flag = 'Y' then
1245 -- we need to insert one action for each of the
1246 -- rows that we return from the cursor (i.e. one
1247 -- for each assignment/pre-payment/reversal).
1248 hr_utility.set_location('procpyr',3);
1249 select pay_assignment_actions_s.nextval
1250 into lockingactid
1251 from dual;
1252
1253 -- insert the action record.
1254 hr_nonrun_asact.insact(lockingactid,assignid,pactid,chunk,greid);
1255
1256 -- insert an interlock to this action.
1257 hr_nonrun_asact.insint(lockingactid,lockedactid);
1258
1259 end if;
1260 else
1261
1262 -- we need to insert one action for each of the
1263 -- rows that we return from the cursor (i.e. one
1264 -- for each assignment/pre-payment/reversal).
1265 hr_utility.set_location('procpyr',3);
1266 select pay_assignment_actions_s.nextval
1267 into lockingactid
1268 from dual;
1269
1270 -- insert the action record.
1271 hr_nonrun_asact.insact(lockingactid,assignid,pactid,chunk,greid);
1272
1273 -- insert an interlock to this action.
1274 hr_nonrun_asact.insint(lockingactid,lockedactid);
1275 end if;
1276
1277 end loop;
1278 close c_all;
1279 end if; /* 'ALL' */
1280
1281 end action_creation;
1282
1283 ---------------------------------- sort_action ----------------------------------
1284 PROCEDURE sort_action(
1285 payactid in varchar2, /* payroll action id */
1286 sqlstr in out nocopy varchar2, /* string holding the sql statement */
1287 len out nocopy number /* length of the sql string */
1288 ) is
1289 -- Cursor to get legislative parameters for Payroll Activity
1290 -- Bug 3037633
1291 cursor cur_leg_params(pactid varchar2) is
1292 select legislative_parameters
1293 from pay_payroll_actions
1294 where payroll_action_id = pactid;
1295
1296 leg_params pay_payroll_actions.legislative_parameters%type;
1297 begin
1298 sqlstr := 'select paa.rowid
1299 /* we need the row id of the assignment actions
1300 that are created by PYUGEN */
1301 from hr_all_organization_units hou, /* Assignment Org */
1302 hr_all_organization_units hou1,/* Tax Unit */
1303 hr_locations_all loc,
1304 per_all_people_f ppf,
1305 per_all_assignments_f paf,
1306 pay_assignment_actions paa, /* PYUGEN assignment action */
1307 pay_payroll_actions ppa, /* PYUGEN payroll action id */
1308 pay_assignment_actions paa1, /*For Sorting */
1309 pay_action_interlocks pai,
1310 pay_payroll_actions ppa1 /*For Sorting */
1311 where ppa.payroll_action_id = :payactid
1312 and paa.payroll_action_id = ppa.payroll_action_id
1313 and paf.assignment_id = paa.assignment_id
1314 and paf.effective_start_date =
1315 (select max(paf1.effective_start_date)
1316 from per_all_assignments_f paf1
1317 where paf1.assignment_id = paf.assignment_id
1318 and paf1.effective_start_date <= ppa.effective_date
1319 and paf1.effective_end_date >= ppa.start_date
1320 )
1321 and hou1.organization_id = paa.tax_unit_id
1322 and hou.organization_id = paf.organization_id
1323 and loc.location_id = paf.location_id
1324 and ppf.person_id = paf.person_id
1325 and ppa.effective_date between ppf.effective_start_date
1326 and ppf.effective_end_date
1327 AND ppa1.effective_date BETWEEN ppa.start_date and ppa.effective_Date
1328 AND ppa1.action_status = ''C''
1329 AND ppa1.payroll_action_id = paa1.payroll_action_id
1330 and paa1.action_status = ''C''
1331 AND paa1.assignment_id = paa.assignment_id
1332 and pai.locking_action_id = paa.assignment_action_id
1333 and pai.locked_action_id = paa1.assignment_action_id
1334 and ppa.business_group_id = ppa1.business_group_id';
1335
1336 open cur_leg_params(payactid);
1337 fetch cur_leg_params into leg_params;
1338
1339 /* Bug 3037633 : The SQL query string is prepared on the basis of the legislative parameters
1340 string obtained from the cursor 'cur_leg_params'. This string is passed
1341 to the get_parameter function to obtain the actual value. */
1342
1343 IF pay_payrg_pkg.get_parameter('P_P_TY',leg_params)= 'BA' then
1344 sqlstr := sqlstr || ' and ppa1.action_type = ''B'' ';
1345 ELSIF pay_payrg_pkg.get_parameter('P_P_TY',leg_params)= 'BI' then
1346 sqlstr := sqlstr || ' and ppa1.action_type = ''I'' ';
1347 ELSIF pay_payrg_pkg.get_parameter('P_P_TY',leg_params)= 'BAI' then
1348 sqlstr := sqlstr || ' and (ppa1.action_type = ''B'' or ppa1.action_type = ''I'')';
1349 ELSIF pay_payrg_pkg.get_parameter('P_P_TY',leg_params)= 'PR' then
1350 sqlstr := sqlstr || ' and ppa1.action_type = ''R''';
1351 ELSIF pay_payrg_pkg.get_parameter('P_P_TY',leg_params)= 'QP' then
1352 sqlstr := sqlstr || ' and ppa1.action_type = ''Q''';
1353 ELSIF pay_payrg_pkg.get_parameter('P_P_TY',leg_params)= 'PRQP' then
1354 sqlstr := sqlstr || ' and (ppa1.action_type = ''R'' or ppa1.action_type = ''Q'')';
1355 ELSIF pay_payrg_pkg.get_parameter('P_P_TY',leg_params)= 'REV' then
1356 sqlstr := sqlstr || ' and ppa1.action_type = ''V''';
1357 ELSIF pay_payrg_pkg.get_parameter('P_P_TY',leg_params)= 'ALL' then
1358 sqlstr := sqlstr || ' and (ppa1.action_type = ''B'' or ppa1.action_type = ''D'' or ppa1.action_type = ''I'' or ppa1.action_type = ''Q'' or ppa1.action_type = ''R'' or ppa1.action_type = ''V'')';
1359 END IF;
1360 close cur_leg_params;
1361
1362 /* Bug 3037633 : Order By clause changed to include the effective_date after sort options
1363 choosen by the user. */
1364
1365 sqlstr := sqlstr||'order by
1366 decode(pay_payrg_pkg.get_parameter(''P_S1'',
1367 ppa.legislative_parameters),
1368 ''GRE'',hou1.name,
1369 ''Organization'',hou.name,
1370 ''Location'',loc.location_code,null),
1371 decode(pay_payrg_pkg.get_parameter(''P_S2'',
1372 ppa.legislative_parameters),
1373 ''GRE'',hou1.name,
1374 ''Organization'',hou.name,
1375 ''Location'',loc.location_code,null),
1376 decode(pay_payrg_pkg.get_parameter(''P_S3'',
1377 ppa.legislative_parameters),
1378 ''GRE'',hou1.name,
1379 ''Organization'',hou.name,
1380 ''Location'',loc.location_code,null),
1381 hou.name, ppf.full_name, ppa1.effective_date
1382 for update of paa.assignment_id';
1383
1384 len := length(sqlstr); -- return the length of the string.
1385 end sort_action;
1386
1387 ------------------------------ get_parameter -------------------------------
1388 FUNCTION get_parameter(name in varchar2,
1389 parameter_list varchar2) return varchar2
1390 is
1391 start_ptr number;
1392 end_ptr number;
1393 token_val pay_payroll_actions.legislative_parameters%type;
1394 par_value pay_payroll_actions.legislative_parameters%type;
1395 begin
1396 --
1397 token_val := name||'=';
1398 --
1399 start_ptr := instr(parameter_list, token_val) + length(token_val);
1400 end_ptr := instr(parameter_list, ' ',start_ptr);
1401 --
1402 /* if there is no spaces use then length of the string */
1403 if end_ptr = 0 then
1404 end_ptr := length(parameter_list)+1;
1405 end if;
1406 --
1407 /* Did we find the token */
1408 if instr(parameter_list, token_val) = 0 then
1409 par_value := NULL;
1410 else
1411 par_value := substr(parameter_list, start_ptr, end_ptr - start_ptr);
1412 end if;
1413 --
1414 return par_value;
1415 --
1416 end get_parameter;
1417
1418 end pay_payact_pkg;
[Home] [Help]