1 PACKAGE BODY pyusdar_pkg AS
2 /* $Header: pyusdar.pkb 115.40 2004/06/08 13:21:41 rmonge ship $ */
3 /*
4 ******************************************************************
5 * *
6 * Copyright (C) 1993 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 : pyusdar.pkb
21
22 Description : Package used for Deposit Advice Report.
23
24 Change List
25 -----------
26 Date Name Vers Description
27 ---------- ---------- ------ -----------------------------------
28 08-JUN-04 rmonge 115.40 Changed the name back to pyusdar_pkg for
29 compatibility reasons in case customers have code
30 that reference the old name (pyusdar_pkg).
31 All new changes need to be done to the package
32 pay_us_deposit_advice_pkg delivered in the file
33 payuslivearchive.pkh/pkb
34 DO NOT ADD CHANGES TO THIS FILE.
35 20-APR-04 schauhan 115.39 Added date effective join condition to c_paid_actions
36 Bug 3009643
37 20-APR-04 schauhan 115.38 Changed the query for the ref cursor c_paid_actions
38 in the procedure action_creation and added table
39 pay_org_payment_methods_f to the join condition.
40 Bug 3009643.
41 25-FEB-04 schauhan 115.37 Changed the cursor c_paid_actions and
42 made it a reference cursor.Also used a
43 variable l_db_version to get database version
44 and used this variable in the procedures
45 range_cursor,action_creation,sort_action
46 Bug3331028
47
48 24-FEB-04 schauhan 115.36 Added Comments for the Bug 3331028.
49 24-FEB-04 schauhan 115.35 1.Modified the procedures range_cursor,
50 and sort_action to use rule hint only
51 database version<10.0.Bug 3331028
52 2.Added cursor c_paid_actions_no_rule to
53 be used instead of c_paid_actions for DB>=10.0
54 Bug 3331028
55 16-JAN-04 rmonge 115.34 Changed the package name to
56 pay_us_deposit_advice_pkg
57 21-JAN_2004 rsethupa 115.33 Bug 3343621 : 11.5.10 Performance changes
58 22-OCT-2003 rmonge 115.32 Added the following line to range_cursor
59 and pa2.action_type in ('R', 'Q')
60 Fix for bug 3180193.
61 06-Aug-2003 vpandya 115.31 Added exists clause in archive_action_crea..
62 procedure.
63 08-JUL-2003 ahanda 115.30 Fixes bug 3023174.
64 Changed date track join in action creation.
65 17-FEB-2003 tclewis 115.29 Added NOCOPY Directive.
66 10-feb-2002 tclewis 115.28 Modified the archive_action_creation code
67 removing the restriction on assignment_sets
68 where the payroll_run must also be run by
69 assignment set. Now all eligible assignments
70 in assignment_set will be processed in run.
71 21-oct-2002 tclewis 115.27 removed the "for Update... " in the
72 action_creation code and archive action
73 creation code. Modified the "for update"
74 clause in the sort code to
75 paa.assignment_id from paf.assignment_id.
76 17-JUL-2002 ahanda 115.26 Added nvl for multi_assignments_flag as
77 the value will be run for existing payrolls.
78 19-mar-2002 irgonzal 115.23 Bug 2264358: Modified archive_action_creation
79 procedure. Added condition that checks for
80 multi-assignment flag.
81 22-mar-2002 irgonzal 115.24 Added condition to action creation cursor
82 to ensure it checks if deposit advice needs
83 to be generated.Added pay_us_employee_payslip
84 _web.get_doc_eit function.
85 23-apr-2002 tclewis 115.25 Modified the arcive_action_creation action_cursor
86 joined the OR condition to XFR --> PRE to pay_payrolls_f
87 17-JAN-2002 TCLEWIS 115.19 ADDED PROCEDURE AND CODE for procedure
88 ARCHIVE_ACTION_CREATION for the new
89 additional deposit advice report that
90 runs off the external process archive
91 data.
92 12-DEC-2001 asasthan 115.18 Aded dbdrv
93 30-NOV-2001 asasthan 115.17 Changed c_actions_zero_pay
94 Added Join of payroll_id and
95 consolidation set id to fix
96 BUG 2122721
97 03-AUG-2001 ahanda 115.16 Changed Sort cursor to take care of
98 terminated assignments.
99 Bug 1918164.
100 24-JUL-2001 asasthan 115.15 Till 115.14 both regular salary
101 and an element set up as separate check
102 were printing only one deposit advice.
103 Modified action creation cursor to
104 achieve this functonality.
105 This version of package will
106 be in sync with report version
107 115.28 onwards.
108 02-JAN-2001 ahanda 115.14 Uncommented whenever sqlerror
109 02-JAN-2001 ahanda 115.13 Added RULE Hint.
110 31-OCT-2000 tclewis 115.11 Modifed the c_actions_zero_pay
111 cursor changing the following code.
112 and ppa_mag_pmts.payroll_id =
113 NVL(pyusdar_pkg.get_parameter('PAYROLL_ID',
114 ppa_dar.legislative_parameters),
115 ppa_mag_pmts.payroll_id)
116 To:
117 and (ppa_mag_pmts.payroll_id =
118 pyusdar_pkg.get_parameter('PAYROLL_ID',
119 ppa_dar.legislative_parameters)
120 or pyusdar_pkg.get_parameter('PAYROLL_ID',
121 ppa_dar.legislative_parameters)
122 is null)
123
124 31-AUG-2000 tclewis 115.8 Added a second cursor to the Action_creation
125 procedure to pick up assignments with
126 zero net pay.
127 15-OCT-1999 mreid 115.7 Changed not equal usage for compliance.
128 25-JUL-1999 nbristow 40.6 Changed c_actions cursor to retrive
129 assignments to be processed when a
130 payroll is not specified.
131 24-JUN-1999 mcpham 115.5 Modified c_actions cursor, added
132 c_get_locked_action cursor and some
133 codes in prodedure action_creation.
134 18-jun-1999 achauhan 115.4 replaced dbms-output with hr_utility.trace
135 18-MAR-1999 kkawol 110.1 Added get_parameter.
136 05-JAN-1999 kkawol 110.0 Created
137 */
138 ----------------------------------- range_cursor ----------------------------------
139 --
140 procedure range_cursor (pactid in number, sqlstr out NOCOPY varchar2) is
141 l_payroll_id number;
142 leg_param pay_payroll_actions.legislative_parameters%type;
143
144 --Bug 3331028
145 l_db_version varchar2(20);
146 --
147
148 begin
149 select legislative_parameters
150 into leg_param
151 from pay_payroll_actions ppa
152 where ppa.payroll_action_id = pactid;
153 --
154 l_payroll_id := pyusdar_pkg.get_parameter('PAYROLL_ID', leg_param);
155 --
156 --Database Version --Bug 3331028
157
158 if (nvl(hr_general2.get_oracle_db_version, 0) < 10.0) then
159 l_db_version := '/*+ RULE */';
160 else
161 l_db_version := '/* NO RULE*/';
162 end if;
163 --
164 if l_payroll_id is not null then
165 --Bug 3331028-- Rule hint is used only for database version < 10.0
166 sqlstr := 'select '||l_db_version||' distinct pos.person_id
167 from pay_assignment_actions act,
168 per_assignments_f asg,
169 per_periods_of_service pos,
170 pay_payroll_actions pa2,
171 pay_payroll_actions pa1
172 where pa1.payroll_action_id = :payroll_action_id
173 and pa2.action_type in (''R'',''Q'')
174 and pa2.consolidation_set_id =
175 pyusdar_pkg.get_parameter(''CONSOLIDATION_SET_ID'',
176 pa1.legislative_parameters)
177 and pa2.payroll_id =
178 pyusdar_pkg.get_parameter(''PAYROLL_ID'',
179 pa1.legislative_parameters)
180 and pa2.effective_date between
181 pa1.start_date and pa1.effective_date
182 and act.payroll_action_id = pa2.payroll_action_id
183 and asg.assignment_id = act.assignment_id
184 and pa2.effective_date between
185 asg.effective_start_date and asg.effective_end_date
186 and pos.period_of_service_id = asg.period_of_service_id
187 order by pos.person_id';
188
189 --
190 else
191 --
192 --Bug 3331028-- Rule hint is used only for database version < 10.0
193 sqlstr := 'select '||l_db_version||' distinct pos.person_id
194 from pay_assignment_actions act,
195 per_assignments_f asg,
196 per_periods_of_service pos,
197 pay_payroll_actions pa2,
198 pay_payroll_actions pa1
199 where pa1.payroll_action_id = :payroll_action_id
200 and pa2.consolidation_set_id =
201 pyusdar_pkg.get_parameter(''CONSOLIDATION_SET_ID'',
202 pa1.legislative_parameters)
203 and pa2.effective_date between
204 pa1.start_date and pa1.effective_date
205 and act.payroll_action_id = pa2.payroll_action_id
206 and asg.assignment_id = act.assignment_id
207 and pa2.effective_date between
208 asg.effective_start_date and asg.effective_end_date
209 and pos.period_of_service_id = asg.period_of_service_id
210 order by pos.person_id';
211 --
212 end if;
213 end range_cursor;
214
215
216 -------------------------- action_creation ---------------------------------
217 PROCEDURE action_creation(pactid in number,
218 stperson in number,
219 endperson in number,
220 chunk in number) is
221 --Bug 3331028
222 l_db_version varchar2(20);
223 l_paid_actions varchar2(4000);
224
225 TYPE PaidActions is REF CURSOR;
226 c_paid_actions PaidActions;
227 --
228
229 CURSOR c_actions_zero_pay
230 (
231 pactid number,
232 stperson number,
233 endperson number
234 ) is
235 select act.assignment_action_id,
236 act.assignment_id,
237 act.tax_unit_id
238 from pay_assignment_actions act,
239 per_all_assignments_f paf1,
240 per_all_assignments_f paf2,
241 per_periods_of_service pos,
242 pay_payroll_actions ppa_dar,
243 pay_payroll_actions ppa_mag_pmts,
244 pay_payrolls_f pay --Bug 3343621
245 where ( ppa_dar.payroll_action_id = pactid
246 and ppa_mag_pmts.consolidation_set_id +0
247 = pyusdar_pkg.get_parameter('CONSOLIDATION_SET_ID',
248 ppa_dar.legislative_parameters)
249 and ppa_mag_pmts.effective_date between ppa_dar.start_date
250 and ppa_dar.effective_date
251 and act.payroll_action_id = ppa_mag_pmts.payroll_action_id
252 and act.action_status = 'C'
253 and ppa_mag_pmts.action_type in ('P', 'U')
254 and ppa_mag_pmts.payroll_id = pay.payroll_id --Bug 3343621
255 and ppa_mag_pmts.effective_date between
256 pay.effective_start_date and pay.effective_end_date --Bug 3343621
257 and pay.payroll_id >= 0 --Bug 3343621
258 and paf1.assignment_id = act.assignment_id
259 and ppa_mag_pmts.effective_date between
260 paf1.effective_start_date and paf1.effective_end_date
261 and paf2.assignment_id = act.assignment_id
262 and ppa_mag_pmts.effective_date between
263 paf2.effective_start_date and paf2.effective_end_date
264 and paf2.payroll_id + 0 = paf1.payroll_id + 0
265 and pos.period_of_service_id = paf1.period_of_service_id
266 and pos.person_id between stperson and endperson
267 and (paf1.payroll_id =
268 pyusdar_pkg.get_parameter('PAYROLL_ID',
269 ppa_dar.legislative_parameters)
270 or pyusdar_pkg.get_parameter('PAYROLL_ID',
271 ppa_dar.legislative_parameters)
272 is null)
273 -- No run results.
274 AND NOT EXISTS (SELECT ' '
275 FROM pay_pre_payments ppp,
276 pay_org_payment_methods_f popm
277 WHERE ppp.assignment_action_id = act.assignment_action_id
278 and ppp.org_payment_method_id = popm.org_payment_method_id
279 and popm.defined_balance_id IS NOT NULL)
280 -- and is not a reversal.
281 AND NOT EXISTS
282 (
283 Select ''
284 from pay_action_interlocks int2,
285 pay_action_interlocks int4,
286 pay_assignment_actions paa4,
287 pay_payroll_actions ppa_run, --- RUN
288 pay_payroll_actions pact4, --- Reversal
289 pay_assignment_actions paa_run --- RUN
290 where
291 int2.locking_action_id = act.assignment_action_id -- prepayment action
292 and int2.locked_action_id = paa_run.assignment_action_id
293 and paa_run.payroll_action_id = ppa_run.payroll_action_id
294 and ppa_run.action_type in ('R', 'Q')
295 and paa_run.assignment_action_id = int4.locked_action_id
296 and int4.locking_action_id = paa4.assignment_action_id
297 and pact4.payroll_action_id = paa4.payroll_action_id
298 and pact4.action_type = 'V'
299 )
300 )
301 order by pos.person_id, act.assignment_id DESC;
302 -- for update of paf1.assignment_id, pos.period_of_service_id;
303
304 /*****************************************************************
305 ** This cursor solves problem when there are multiple pre-payments
306 ** and multiple assignment actions , in this case we only want 1
307 ** assignment action for each pre-payment (bug 890222)
308 *****************************************************************/
309 cursor c_pre_payments (cp_nacha_action_id in number) is
310 select locked_action_id
311 from pay_action_interlocks pai
312 where pai.locking_action_id = cp_nacha_action_id;
313
314 cursor c_payroll_run (cp_pre_pymt_action_id in number) is
315 select assignment_action_id
316 from pay_action_interlocks pai,
317 pay_assignment_actions paa
318 where pai.locking_action_id = cp_pre_pymt_action_id
319 and paa.assignment_Action_id = pai.locked_action_id
320 and paa.run_type_id is null
321 order by action_sequence desc;
322
323 /*****************************************************************
324 ** This cursor will get all the source actions for which the
325 ** assignment should get a deposit advice.
326 ** assignment action for each pre-payment (bug 890222) i.e.
327 ** Seperate Depsoit Advice for Seperate Check and Regular Run
328 *****************************************************************/
329 cursor c_payments (cp_pre_pymt_action_id in number,
330 cp_effective_date in date) is
331 select distinct ppp.source_action_id
332 from pay_pre_payments ppp,
333 pay_personal_payment_methods_f pppm
334 where ppp.assignment_action_id = cp_pre_pymt_action_id
335 and pppm.personal_payment_method_id = ppp.personal_payment_method_id
336 and pppm.external_account_id is not null
337 and cp_effective_date between pppm.effective_start_date
338 and pppm.effective_end_date
339 and nvl(ppp.value,0) <> 0
340 order by ppp.source_action_id;
341
342 ln_nacha_action_id NUMBER;
343 ln_deposit_action_id NUMBER;
344
345 ln_assignment_id NUMBER;
346 ln_tax_unit_id NUMBER;
347 ld_effective_date DATE;
348
349 ln_pre_pymt_action_id NUMBER;
350 ln_prev_pre_pymt_action_id NUMBER := null;
351
352 ln_source_action_id NUMBER;
353 ln_prev_source_action_id NUMBER := null;
354
355 ln_master_action_id NUMBER;
356
357 BEGIN
358
359 --hr_utility.trace_on(null, 'DAR');
360 hr_utility.set_location('procdar',1);
361
362 -- Database Version --Bug 3331028
363 if (nvl(hr_general2.get_oracle_db_version, 0) < 10.0) then
364 l_db_version := '/*+ RULE */';
365 else
366 l_db_version := '/* NO RULE*/';
367 end if;
368 --
369
370 -- Query string for the reference cursor c_paid_actions --Bug 3331028
371 l_paid_actions := 'select '||l_db_version||' act.assignment_action_id,
372 act.assignment_id,
373 act.tax_unit_id,
374 ppa_mag.effective_date
375 from pay_assignment_actions act,
376 per_all_assignments_f paf1,
377 per_all_assignments_f paf2,
378 per_periods_of_service pos,
379 pay_payroll_actions ppa_dar,
380 pay_payroll_actions ppa_mag,
381 pay_org_payment_methods_f popm --Bug 3009643
382 where ppa_dar.payroll_action_id = :pactid
383 and ppa_mag.consolidation_set_id +0 =
384 pyusdar_pkg.get_parameter(''CONSOLIDATION_SET_ID'',
385 ppa_dar.legislative_parameters)
386 and ppa_mag.effective_date between
387 ppa_dar.start_date and ppa_dar.effective_date
388 and act.payroll_action_id = ppa_mag.payroll_action_id
389 and act.action_status = ''C''
390 and ppa_mag.action_type = ''M''
391 and ppa_mag.org_payment_method_id = popm.org_payment_method_id -- Bug 3009643
392 and popm.defined_balance_id is not null -- Bug 3009643
393 and ppa_mag.effective_date between
394 popm.effective_start_date and popm.effective_end_date --Bug 3009643
395 and paf1.assignment_id = act.assignment_id
396 and ppa_mag.effective_date between
397 paf1.effective_start_date and paf1.effective_end_date
398 and paf2.assignment_id = act.assignment_id
399 and ppa_dar.effective_date between
400 paf2.effective_start_date and paf2.effective_end_date
401 and paf2.payroll_id + 0 = paf1.payroll_id + 0
402 and pos.period_of_service_id = paf1.period_of_service_id
403 and pos.person_id between :stperson and :endperson
404 and (paf1.payroll_id =
405 pyusdar_pkg.get_parameter(''PAYROLL_ID'',
406 ppa_dar.legislative_parameters)
407 or pyusdar_pkg.get_parameter(''PAYROLL_ID'',
408 ppa_dar.legislative_parameters)
409 is null)
410 and not exists
411 (
412 Select ''''
413 from pay_action_interlocks int2,
414 pay_action_interlocks int3,
415 pay_assignment_actions paa4,
416 pay_payroll_actions ppa_run, --- RUN
417 pay_payroll_actions pact4, --- Reversal
418 pay_assignment_actions paa_run, --- RUN
419 pay_assignment_actions paa_pp --- PREPAY
420 where
421 int3.locked_action_id = act.assignment_action_id
422 and int3.locking_action_id = paa_pp.assignment_action_id
423 and int2.locked_action_id = paa_pp.assignment_action_id
424 and int2.locking_action_id = paa_run.assignment_action_id
425 and paa_run.payroll_action_id = ppa_run.payroll_action_id
426 and ppa_run.action_type in (''R'', ''Q'')
427 and paa_run.assignment_action_id = int3.locked_action_id
428 and int3.locking_action_id = paa4.assignment_action_id
429 and pact4.payroll_action_id = paa4.payroll_action_id
430 and pact4.action_type = ''V''
431 )
432 order by pos.person_id, act.assignment_id DESC';
433 -- for update of paf1.assignment_id, pos.period_of_service_id;
434 --
435
436 -- Reference cursor opened for the query string l_paid_actions --Bug 3331028
437 open c_paid_actions for l_paid_actions using pactid, stperson, endperson;
438 --
439 loop
440 hr_utility.set_location('procdar',2);
441 fetch c_paid_actions into ln_nacha_action_id, ln_assignment_id,
442 ln_tax_unit_id, ld_effective_date;
443 exit WHEN c_paid_actions%NOTFOUND;
444 hr_utility.trace(' c_paid_actions.ln_nacha_action_id is'
445 ||to_char(ln_nacha_action_id));
446 open c_pre_payments (ln_nacha_action_id);
447 fetch c_pre_payments into ln_pre_pymt_action_id;
448 hr_utility.trace(' c_pre_payments.ln_pre_pymt_action_id is'
449 ||to_char(ln_pre_pymt_action_id));
450 close c_pre_payments;
451
452 /**************************************************************************
453 ** we need to insert atleast one action for each of the rows that we
454 ** return from the cursor (i.e. one for each assignment/pre-payment action).
455 **************************************************************************/
456 hr_utility.trace(' ln_prev_pre_pymt_action_id is'
457 ||to_char(ln_prev_pre_pymt_action_id));
458 if (ln_prev_pre_pymt_action_id is null or
459 ln_prev_pre_pymt_action_id <> ln_pre_pymt_action_id) then
460 open c_payments (ln_pre_pymt_action_id, ld_effective_date);
461 loop
462 hr_utility.set_location('procdar',99);
463 fetch c_payments into ln_source_action_id;
464 hr_utility.trace(' ln_source_action_id is'
465 ||to_char(ln_source_action_id));
466
467 hr_utility.set_location('procdar',98);
468 if c_payments%notfound then
469 exit;
470 end if;
471 hr_utility.set_location('procdar',97);
472
473 /**************************************************************
474 ** we need to insert one action for each of the rows that we
475 ** return from the cursor (i.e. one for each assignment/pre-payment source).
476 **************************************************************/
477 hr_utility.trace(' ln_prev_source_action_id is'
478 ||to_char(ln_prev_source_action_id));
479 if (ln_prev_source_action_id is null or
480 ln_source_action_id <> ln_prev_source_action_id or
481 ln_source_action_id is null) then
482
483 hr_utility.set_location('procdar',3);
484 select pay_assignment_actions_s.nextval
485 into ln_deposit_action_id
486 from dual;
487
488 -- insert the action record.
489 hr_nonrun_asact.insact(ln_deposit_action_id,
490 ln_assignment_id,
491 pactid, chunk, ln_tax_unit_id);
492 hr_utility.trace('Inserted into paa');
493 -- insert an interlock to this action.
494 hr_nonrun_asact.insint(ln_deposit_action_id, ln_nacha_action_id);
495
496 hr_utility.trace('Inserted into interlock');
497 if ln_source_action_id is not null then
498 hr_utility.trace('serial number updated if loop ');
499 hr_utility.trace('serial number is '||to_char(ln_source_action_id));
500 update pay_assignment_Actions
501 set serial_number = 'P'||ln_source_action_id
502 --set serial_number = ln_source_action_id
503 where assignment_action_id = ln_deposit_action_id;
504 else
505 hr_utility.trace('serial number else ');
506 open c_payroll_run (ln_pre_pymt_action_id);
507 fetch c_payroll_run into ln_master_action_id;
508 close c_payroll_run;
509 hr_utility.trace(' ln_master_action_id is'
510 ||to_char(ln_master_action_id));
511
512 update pay_assignment_Actions
513 set serial_number = 'M'||ln_master_action_id
514 --set serial_number = ln_master_action_id
515 where assignment_action_id = ln_deposit_action_id;
516 end if;
517
518 -- skip till next source action id
519 ln_prev_source_action_id := ln_source_action_id;
520 end if;
521 end loop;
522 close c_payments;
523 ln_prev_pre_pymt_action_id := ln_pre_pymt_action_id;
524
525 end if;
526 end loop;
527 close c_paid_actions;
528
529 hr_utility.set_location('procdar',4);
530 ln_prev_pre_pymt_action_id := null;
531 open c_actions_zero_pay(pactid,stperson,endperson);
532
533 loop
534 hr_utility.set_location('procdar',5);
535 fetch c_actions_zero_pay INTO ln_nacha_action_id, --gives P,U
536 ln_assignment_id,
537 ln_tax_unit_id;
538 exit WHEN c_actions_zero_pay%NOTFOUND;
539 hr_utility.trace(' NZ PrePayment Id is' ||to_char(ln_nacha_action_id));
540
541 open c_pre_payments (ln_nacha_action_id); --gives me R,Q
542 fetch c_pre_payments into ln_pre_pymt_action_id;
543 close c_pre_payments;
544
545 hr_utility.trace(' NZ Run ActionId is' ||to_char(ln_pre_pymt_action_id));
546 hr_utility.trace(' NZ ln_prev_pre_pymt_action_id is' ||to_char(ln_prev_pre_pymt_action_id));
547
548 if (ln_prev_pre_pymt_action_id is null or
549 ln_prev_pre_pymt_action_id <> ln_pre_pymt_action_id) then
550
551 hr_utility.set_location('procdar',6);
552 select pay_assignment_actions_s.nextval
553 into ln_deposit_action_id
554 from dual;
555
556 -- insert the action record.
557 hr_nonrun_asact.insact(ln_deposit_action_id,
558 ln_assignment_id,
559 pactid, chunk, ln_tax_unit_id);
560
561 -- insert an interlock to this action.
562 hr_nonrun_asact.insint(ln_deposit_action_id, ln_nacha_action_id);
563 hr_utility.trace(' NZ Inserted into paa');
564
565 open c_payroll_run (ln_nacha_action_id);
566 fetch c_payroll_run into ln_master_action_id;
567 hr_utility.trace(' NZ ln_master_action_id is'
568 ||to_char(ln_master_action_id));
569 close c_payroll_run;
570
571 update pay_assignment_Actions
572 set serial_number = 'M'||ln_master_action_id
573 where assignment_action_id = ln_deposit_action_id;
574
575 -- skip till next pre payment action id
576 ln_prev_pre_pymt_action_id := ln_pre_pymt_action_id;
577
578 end if;
579
580 end loop;
581 close c_actions_zero_pay;
582
583 END action_creation;
584
585
586 -------------------------- action_creation ---------------------------------
587 PROCEDURE archive_action_creation(pactid in number,
588 stperson in number,
589 endperson in number,
590 chunk in number) is
591 CURSOR c_paid_actions
592 (
593 pactid number,
594 stperson number,
595 endperson number
596 ) is
597 select
598 paa_xfr.assignment_action_id,
599 paa_xfr.assignment_id,
600 paa_xfr.tax_unit_id
601 from per_assignments_f paf,
602 pay_payroll_actions ppa_dar,
603 pay_payroll_actions ppa_run,
604 pay_assignment_actions paa_run,
605 pay_action_interlocks pai_pre,
606 pay_action_interlocks pai_run,
607 pay_assignment_actions paa_xfr,
608 pay_payroll_actions ppa_xfr
609 , pay_payrolls_f pay -- #2264358
610 where ppa_dar.payroll_action_id = pactid
611 and ppa_xfr.report_type = 'XFR_INTERFACE'
612 and ppa_dar.effective_date between ppa_xfr.start_date
613 and ppa_xfr.effective_date
614 and pyusdar_pkg.get_parameter('CONSOLIDATION_SET_ID',
615 ppa_dar.legislative_parameters) =
616 pyusdar_pkg.get_parameter('CONSOLIDATION_SET_ID',
617 ppa_xfr.legislative_parameters)
618 and paa_xfr.payroll_action_id = ppa_xfr.payroll_action_id
619 and pai_pre.locking_action_id = paa_xfr.assignment_action_id
620 /* PRE => RUN */
621 and ( paa_xfr.source_action_id is null
622 and pai_run.locking_action_id = pai_pre.locked_action_id
623 and paa_run.assignment_action_id = pai_run.locked_action_id
624 and paa_run.source_action_id is null
625 and ppa_run.payroll_action_id = paa_run.payroll_action_id
626 and ppa_run.action_type in ('R', 'Q')
627 -- *****************************************************************
628 -- #2264358
629 and ppa_run.payroll_id = pay.payroll_id
630 and ppa_dar.effective_date between pay.effective_start_date
631 and pay.effective_end_date
632 and pay.payroll_id >= 0 --Bug 3343621
633 and ((pay.multi_assignments_flag = 'Y' and
634 paa_run.assignment_action_id =
635 (select min(paa.assignment_action_id)
636 from pay_assignment_actions paa
637 where paa.assignment_action_id in (
638 select locked_action_id
639 from pay_action_interlocks
640 where locking_action_id = pai_run.locking_action_id)
641 and paa.source_action_id is null
642 )
643 )
644 OR
645 (nvl(pay.multi_assignments_flag, 'N') = 'N')
646 )
647 -- ***************************************************************
648 OR
649 paa_xfr.source_action_id is not null
650 and substr(paa_xfr.serial_number,3,length(paa_xfr.serial_number)) =
651 paa_run.assignment_action_id
652 and pai_run.locking_action_id = pai_pre.locked_action_id
653 and paa_run.assignment_action_id = pai_run.locked_action_id
654 and ppa_run.payroll_action_id = paa_run.payroll_action_id
655 and ppa_run.action_type in ('R', 'Q')
656 and ppa_run.payroll_id = pay.payroll_id
657 and ppa_run.effective_date between pay.effective_start_date
658 and pay.effective_end_date
659 and pay.payroll_id >= 0 --Bug 3343621
660 )
661 /* XFR => PRE */
662 and exists ( select 'Y'
663 from pay_action_interlocks pai_mag,
664 pay_assignment_actions paa_mag,
665 pay_payroll_actions ppa_mag
666 where pai_mag.locked_action_id = pai_pre.locked_action_id
667 and pai_mag.locking_Action_id = paa_mag.assignment_action_id
668 and paa_mag.payroll_action_id = ppa_mag.payroll_action_id
669 and ppa_mag.action_type = 'M'
670 and ppa_mag.effective_date between ppa_dar.start_date
671 and ppa_dar.effective_date
672 and ppa_mag.consolidation_set_id +0 =
673 pyusdar_pkg.get_parameter('CONSOLIDATION_SET_ID',
674 ppa_dar.legislative_parameters)
675 )
676 and paa_xfr.assignment_id = paf.assignment_id
677 and ppa_dar.effective_date between
678 paf.effective_start_date and paf.effective_end_date
679 and (
680 paf.payroll_id = pyusdar_pkg.get_parameter('PAYROLL_ID',
681 ppa_dar.legislative_parameters)
682 or pyusdar_pkg.get_parameter('PAYROLL_ID',
683 ppa_dar.legislative_parameters) is null
684 )
685
686 and paf.person_id between stperson and endperson
687 and not exists
688 (
689 Select ''
690 from pay_action_interlocks int2,
691 pay_action_interlocks int3,
692 pay_assignment_actions paa4,
693 pay_payroll_actions ppa_run, --- RUN
694 pay_payroll_actions pact4, --- Reversal
695 pay_assignment_actions paa_run --- RUN
696 where int2.locked_action_id = pai_pre.locked_action_id
697 and int2.locking_action_id = paa_run.assignment_action_id
698 and paa_run.payroll_action_id = ppa_run.payroll_action_id
699 and ppa_run.action_type in ('R', 'Q')
700 and paa_run.assignment_action_id = int3.locked_action_id
701 and int3.locking_action_id = paa4.assignment_action_id
702 and pact4.payroll_action_id = paa4.payroll_action_id
703 and pact4.action_type = 'V'
704 )
705 /* ONLINE or PRINT ? */
706 and pay_us_employee_payslip_web.get_doc_eit('PAYSLIP','PRINT'
707 ,'ASSIGNMENT',paf.assignment_id,ppa_dar.effective_date) = 'Y'
708 and exists ( select 1
709 from pay_action_information pai
710 where pai.action_context_id = paa_xfr.assignment_action_id
711 and rownum < 2 ) --Bug 3343621
712 order by paf.person_id, paf.assignment_id DESC;
713 -- for update of paf.assignment_id;
714
715 CURSOR c_actions_zero_pay
716 (
717 pactid number,
718 stperson number,
719 endperson number
720 ) is
721 select
722 paa_xfr.assignment_action_id,
723 paa_xfr.assignment_id,
724 paa_xfr.tax_unit_id
725 from per_assignments_f paf,
726 pay_payroll_actions ppa_dar,
727 pay_payroll_actions ppa_run,
728 pay_payroll_actions ppa_pre,
729 pay_action_interlocks pai_pre,
730 pay_action_interlocks pai_run,
731 pay_assignment_actions paa_xfr,
732 pay_assignment_actions paa_pre,
733 pay_assignment_actions paa_run,
734 pay_payroll_actions ppa_xfr
735 , pay_payrolls_f pay -- #2264358
736 where ppa_dar.payroll_action_id = pactid
737 and pyusdar_pkg.get_parameter('CONSOLIDATION_SET_ID',
738 ppa_dar.legislative_parameters) =
739 pyusdar_pkg.get_parameter('CONSOLIDATION_SET_ID',
740 ppa_xfr.legislative_parameters)
741 and ppa_xfr.report_type = 'XFR_INTERFACE'
742 and ppa_dar.effective_date between ppa_xfr.start_date
743 and ppa_xfr.effective_date
744 and paa_xfr.payroll_action_id = ppa_xfr.payroll_action_id
745 and pai_pre.locking_action_id = paa_xfr.assignment_action_id
746 and paa_pre.assignment_action_id = pai_pre.locked_action_id
747 and ppa_pre.payroll_action_id = paa_pre.payroll_action_id
748 and ppa_pre.action_type in ('P', 'U')
749 and ppa_pre.consolidation_set_id +0 =
750 pyusdar_pkg.get_parameter('CONSOLIDATION_SET_ID',
751 ppa_dar.legislative_parameters)
752 /* PRE => RUN */
753 and pai_run.locking_action_id = pai_pre.locked_action_id
754 and paa_run.assignment_action_id = pai_run.locked_action_id
755 and paa_run.source_action_id is null
756 and ppa_run.payroll_action_id = paa_run.payroll_action_id
757 and ppa_run.action_type in ('R', 'Q')
758 -- **********************************************************************
759 -- #2264358
760 and ppa_run.payroll_id = pay.payroll_id
761 and ppa_dar.effective_date between pay.effective_start_date
762 and pay.effective_end_date
763 and pay.payroll_id >= 0 --Bug 3343621
764 and ((pay.multi_assignments_flag = 'Y' and
765 paa_run.assignment_action_id =
766 (select min(paa.assignment_action_id)
767 from pay_assignment_actions paa
768 where paa.assignment_action_id in (select locked_action_id
769 from pay_action_interlocks
770 where locking_action_id = pai_run.locking_action_id)
771 and paa.source_action_id is null
772 )
773 )
774 OR
775 (nvl(pay.multi_assignments_flag, 'N') = 'N')
776 )
777 -- **********************************************************************
778 and not exists (select ' '
779 from pay_pre_payments ppp
780 where ppp.assignment_action_id = pai_pre.locked_action_id
781 )
782
783 /* XFR => PRE */
784 and paa_xfr.assignment_id = paf.assignment_id
785 and ppa_dar.effective_date between
786 paf.effective_start_date and paf.effective_end_date
787 and (
788 paf.payroll_id = pyusdar_pkg.get_parameter('PAYROLL_ID',
789 ppa_dar.legislative_parameters)
790 or pyusdar_pkg.get_parameter('PAYROLL_ID',
791 ppa_dar.legislative_parameters) is null
792 )
793
794 and paf.person_id between stperson and endperson
795 and not exists
796 (
797 Select ''
798 from pay_action_interlocks int2,
799 pay_action_interlocks int3,
800 pay_assignment_actions paa4,
801 pay_payroll_actions ppa_run, --- RUN
802 pay_payroll_actions pact4, --- Reversal
803 pay_assignment_actions paa_run --- RUN
804 where int2.locking_action_id = pai_pre.locked_action_id
805 and int2.locked_action_id = paa_run.assignment_action_id
806 and paa_run.payroll_action_id = ppa_run.payroll_action_id
807 and ppa_run.action_type in ('R', 'Q')
808 and paa_run.assignment_action_id = int3.locked_action_id
809 and int3.locking_action_id = paa4.assignment_action_id
810 and pact4.payroll_action_id = paa4.payroll_action_id
811 and pact4.action_type = 'V'
812 )
813 /* ONLINE or PRINT ? */
814 and pay_us_employee_payslip_web.get_doc_eit('PAYSLIP','PRINT'
815 ,'ASSIGNMENT',paf.assignment_id,ppa_dar.effective_date) = 'Y'
816 and exists ( select 1
817 from pay_action_information pai
818 where pai.action_context_id = paa_xfr.assignment_action_id
819 and pai.action_context_type not in ('AAP')
820 and pai.action_information_category not in ('EMPLOYEE NET PAY DISTRIBUTION')
821 )
822 order by paf.person_id, paa_xfr.assignment_id DESC;
823 -- for update of paf.assignment_id;
824
825
826 ln_nacha_action_id NUMBER;
827 ln_deposit_action_id NUMBER;
828
829 ln_assignment_id NUMBER;
830 ln_tax_unit_id NUMBER;
831 ld_effective_date DATE;
832
833 ln_pre_pymt_action_id NUMBER;
834 ln_prev_pre_pymt_action_id NUMBER := null;
835
836 ln_source_action_id NUMBER;
837 ln_prev_source_action_id NUMBER := null;
838
839 ln_master_action_id NUMBER;
840
841 ass_set_id NUMBER;
842 ass_flag VARCHAR2(1);
843
844 l_legislative_parameters varchar2(2000);
845
846
847 BEGIN
848 select legislative_parameters
849 into l_legislative_parameters
850 from pay_payroll_actions
851 where payroll_action_id = pactid;
852
853 ass_set_id := pyusdar_pkg.get_parameter('ASG_SET_ID',
854 l_legislative_parameters);
855
856 --hr_utility.trace_on(null, 'DAR');
857 hr_utility.set_location('procdar archive',1);
858 open c_paid_actions(pactid, stperson, endperson);
859 loop
860 hr_utility.set_location('procdar archive',2);
861
862 ass_flag := 'N';
863
864 fetch c_paid_actions into ln_nacha_action_id, ln_assignment_id,
865 ln_tax_unit_id;
866 exit WHEN c_paid_actions%NOTFOUND;
867
868 ass_flag := hr_assignment_set.assignment_in_set(ass_set_id,ln_assignment_id);
869
870 IF ass_flag = 'Y' THEN
871 hr_utility.trace(' c_paid_actions.ln_nacha_action_id is'
872 ||to_char(ln_nacha_action_id));
873
874 hr_utility.set_location('procdar archive',3);
875 select pay_assignment_actions_s.nextval
876 into ln_deposit_action_id
877 from dual;
878
879 -- insert the action record.
880 hr_nonrun_asact.insact(ln_deposit_action_id,
881 ln_assignment_id,
882 pactid, chunk, ln_tax_unit_id);
883 hr_utility.trace('Inserted into paa');
884 -- insert an interlock to this action.
885 hr_nonrun_asact.insint(ln_deposit_action_id, ln_nacha_action_id);
886
887 update pay_assignment_Actions
888 set serial_number = ln_nacha_action_id
889 where assignment_action_id = ln_deposit_action_id;
890
891 END IF;
892
893 end loop;
894 close c_paid_actions;
895
896 --
897 hr_utility.set_location('procdar archive',4);
898 ln_prev_pre_pymt_action_id := null;
899 open c_actions_zero_pay(pactid,stperson,endperson);
900
901 loop
902 hr_utility.set_location('procdar archive',5);
903
904 ass_flag := 'N';
905
906 fetch c_actions_zero_pay INTO ln_nacha_action_id, --gives P,U
907 ln_assignment_id,
908 ln_tax_unit_id;
909 exit WHEN c_actions_zero_pay%NOTFOUND;
910 hr_utility.trace(' NZ PrePayment Id is' ||to_char(ln_nacha_action_id));
911
912 hr_utility.set_location('procdar archive',3);
913
914 ass_flag := hr_assignment_set.assignment_in_set(ass_set_id,ln_assignment_id);
915
916 IF ass_flag = 'Y' THEN
917
918 select pay_assignment_actions_s.nextval
919 into ln_deposit_action_id
920 from dual;
921
922 -- insert the action record.
923 hr_nonrun_asact.insact(ln_deposit_action_id,
924 ln_assignment_id,
925 pactid, chunk, ln_tax_unit_id);
926
927 -- insert an interlock to this action.
928 hr_nonrun_asact.insint(ln_deposit_action_id, ln_nacha_action_id);
929 hr_utility.trace(' NZ Inserted into paa');
930
931 update pay_assignment_Actions
932 set serial_number = ln_nacha_action_id
933 where assignment_action_id = ln_deposit_action_id;
934 END IF;
935
936 end loop;
937 close c_actions_zero_pay;
938
939
940 END archive_action_creation;
941
942
943 ---------------------------------- sort_action ----------------------------------
944 procedure sort_action
945 (
946 procname in varchar2, /* name of the select statement to use */
947 sqlstr in out NOCOPY varchar2, /* string holding the sql statement */
948 len out NOCOPY number /* length of the sql string */
949 ) is
950
951 --Bug 3331028
952 l_db_version varchar2(20);
953 --
954
955 begin
956 -- go through each of the sql sub strings and see if
957 -- they are needed.
958 -- Databse Version --Bug 3331028
959 if (nvl(hr_general2.get_oracle_db_version, 0) < 10.0) then
960 l_db_version := '/*+ RULE */';
961 else
962 l_db_version := '/* NO RULE*/';
963 end if;
964 --
965 --Bug 3331028-- Rule hint is used only for database version < 10.0
966 sqlstr := 'select '||l_db_version||' paa.rowid
967 from hr_all_organization_units hou,
968 per_all_people_f ppf,
969 per_all_assignments_f paf,
970 pay_assignment_actions paa,
971 pay_payroll_actions ppa
972 where ppa.payroll_action_id = :pactid
973 and paa.payroll_action_id = ppa.payroll_action_id
974 and paa.assignment_id = paf.assignment_id
975 and paf.effective_start_date =
976 (select max(paf1.effective_start_date)
977 from per_all_assignments_f paf1
978 where paf1.assignment_id = paf.assignment_id
979 and paf1.effective_start_date <= ppa.effective_date
980 and paf1.effective_end_date >= ppa.start_date
981 )
982 and paf.person_id = ppf.person_id
983 and ppa.effective_date between ppf.effective_start_date
984 and ppf.effective_end_date
985 and hou.organization_id = nvl(paf.organization_id, paf.business_group_id)
986 order by hou.name,ppf.last_name,ppf.first_name
987 for update of paa.assignment_id';
988 len := length(sqlstr); -- return the length of the string.
989 end sort_action;
990 --
991 ------------------------------ get_parameter -------------------------------
992 function get_parameter(name in varchar2,
993 parameter_list varchar2) return varchar2
994 is
995 start_ptr number;
996 end_ptr number;
997 token_val pay_payroll_actions.legislative_parameters%type;
998 par_value pay_payroll_actions.legislative_parameters%type;
999 begin
1000 --
1001 token_val := name||'=';
1002 --
1003 start_ptr := instr(parameter_list, token_val) + length(token_val);
1004 end_ptr := instr(parameter_list, ' ', start_ptr);
1005 --
1006 /* if there is no spaces use then length of the string */
1007 if end_ptr = 0 then
1008 end_ptr := length(parameter_list)+1;
1009 end if;
1010 --
1011 /* Did we find the token */
1012 if instr(parameter_list, token_val) = 0 then
1013 par_value := NULL;
1014 else
1015 par_value := substr(parameter_list, start_ptr, end_ptr - start_ptr);
1016 end if;
1017 --
1018 return par_value;
1019 --
1020 end get_parameter;
1021 --
1022 end pyusdar_pkg;
[Home] [Help]