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