1 PACKAGE BODY pay_us_deposit_advice_pkg AS
2 /* $Header: payuslivearchive.pkb 120.14.12010000.7 2009/02/17 17:27:27 rnestor 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 : pay_us_deposit_advice_pkg.pkb
21
22 Description : Package used for Deposit Advice Report.
23
24 Change List
25 -----------
26 Date Name Vers Bug Description
27 ---------- ---------- ------ -------- -----------------------------------
28 08-JUN-2004 RMONGE 115.0 Created.
29 This package is a copy of the
30 pyusdar.pkb.
31
32 Please refer to the old package body
33 for a history of changes.
34
35 25-JUN-2004 RMONGE 115.1 Replace existing version of this package with the
36 version 115.33 from pyusdar.pkb as required.
37
38 02-JUL-2004 schauhan 115.2 3512116 1.Changed the query for cursor c_paid_actions in
39 procedure archive_action_creation for better
40 performance.
41 2.Removed cursor c_actions_zero_pay in procedure
42 archive_action_creation and added its logic to
43 function check_if_assignment_paid.
44 3.Changed query for range_cursor for better
45 performance.
46 4.Removed function get_parameter and used function
47 pay_us_payroll_utils.get_parameter instead.
48 5.Added function get_payroll_action which returns
49 payroll_action data.
50 04-AUG-2004 schauhan 115.3 3512116 Added assignment_set_id in the return list of
51 procedure get_payroll_action.
52 18-OCT-2004 schauhan 115.4 3928576 Made changes to cursor c_actions_zero_pay to check
53 only if assignment_action_id for master action is
54 is there pay_pre_payments.
55 14-MAR-2005 sackumar 115.5 4222032 Change in the Range Cursor removing redundant
56 use of bind Variable (:payroll_action_id)
57 29-MAR-2005 sackumar 115.6 4222032 Removing GSCC Errors
58 29-APR-2005 sackumar 115.7 3812668 Merge the concept of Zero pay cursor into c_actions
59 cursor and introduce a new cursor c_actions_assign_set
60 and also introduce a Zero_pay_flag to process the
61 Zero pay concept.
62 31-may-2005 djoshi 115.10 Performance Fix for action creation based on
63 city of Chicago tar. 4190348.996
64 06-JUN-2005 rsethupa 115.11 4406538 Changed cursor c_paid_actions in
65 archive_action_creation procedure to use the
66 exact names TRANSFER_CONSOLIDATION_SET_ID and
67 TRANSFER_PAYROLL_ID while calling
68 pay_us_payroll_utils.get_parameter()
69 29-Sep-2005 sackumar 115.12 4631914 Modified the payroll_id join condition in c_paid_actions
70 query in action_creation procedure
71 06-Oct-2005 sackumar 115.13 4636646 Modified the c_paid_actions cursor query in
72 action_creation procedure for Zero Pay assignment.
73 26-Nov-2005 sackumar 115.14 4742901 Modified the c_paid_actions cursor query in
74 action_creation procedure to resolve the
75 performance issues.
76 16-Dec-2005 tclewis 115.15 added code to check for the existance of data in the
77 pay_action_information table. thie is to fix a problem
78 with the zero (gross, net) pay assignment being picked up
79 by in the archive_action_creation procedure.
80 23-Jan-2006 sackumar 115.18 4945604 Modified the C_action_asg_set Cursor in action_creation procedure
81 24-May-2007 sudedas 115.21 5635335 Procedure archive_deinit has
82 been added to be used by Archive
83 Deposit Advice (PDF) Process.
84 27-Jun-2007 sudedas 115.22 Added Qualifying Procedure and Function
85 check_if_qualified_for_US. This is for
86 Archive Deposit Advice producing XML
87 using Global Payslip Printing Solution
88 03-OCT-2008 rnestor 115.25 Modified the c_no_prepayments cursor to exclude Third Party Payments
89 15-Jan-2009 sudedas 115.26 7583387 Added function get_DAxml_payroll_action
90 ,DAxml_range_cursor and changed
91 qualifying_proc.
92 21-Jan-2009 sudedas 115.27 7583387 Changed Function DAxml_range_cursor
93 to Procedure.
94 115.28 7583387 Added NOCOPY hint for OUT variable.
95 17-feb-2009 115.29 8254078 <> NULL replaced with is not NULL
96 in the c_no_prepayments cursor
97 **********************************************************************/
98
99 /**********************************************************************
100 ** PROCEDURE : get_payroll_action
101 ** Description: Bug 3512116
102 ** This procedure returns the details for payroll action for
103 ** deposit advice. This is called in the range cursor.
104 **********************************************************************/
105 PROCEDURE get_payroll_action(p_payroll_action_id in number
106 ,p_deposit_start_date out nocopy date
107 ,p_deposit_end_date out nocopy date
108 ,p_assignment_set_id out nocopy number
109 ,p_payroll_id out nocopy number
110 ,p_consolidation_set_id out nocopy number
111 )
112 IS
113
114 cursor c_get_payroll_action
115 (cp_payroll_action_id in number) is
116 select legislative_parameters,
117 start_date,
118 effective_date
119 from pay_payroll_actions
120 where payroll_action_id = cp_payroll_action_id;
121
122 lv_legislative_parameters VARCHAR2(2000);
123
124 ln_assignment_set_id NUMBER;
125 ln_payroll_id NUMBER;
126 ln_consolidation_set_id NUMBER;
127 ld_deposit_start_date DATE;
128 ld_deposit_end_date DATE;
129
130 BEGIN
131 open c_get_payroll_action(p_payroll_action_id);
132 fetch c_get_payroll_action into lv_legislative_parameters,
133 ld_deposit_start_date,
134 ld_deposit_end_date;
135 close c_get_payroll_action;
136
137 ln_assignment_set_id := pay_us_payroll_utils.get_parameter(
138 'ASG_SET_ID',
139 lv_legislative_parameters);
140 ln_payroll_id := pay_us_payroll_utils.get_parameter(
141 'PAYROLL_ID',
142 lv_legislative_parameters);
143 ln_consolidation_set_id := pay_us_payroll_utils.get_parameter(
144 'CONSOLIDATION_SET_ID',
145 lv_legislative_parameters);
146
147 p_deposit_start_date := ld_deposit_start_date;
148 p_deposit_end_date := ld_deposit_end_date;
149 p_payroll_id := ln_payroll_id;
150 p_assignment_set_id := ln_assignment_set_id;
151 p_consolidation_set_id := ln_consolidation_set_id;
152
153 END get_payroll_action;
154
155 /**********************************************************************
156 ** PROCEDURE : get_DAxml_payroll_action
157 ** This procedure returns the details for payroll action for
158 ** Deposit Advice(XML). This is called in the range cursor.
159 **********************************************************************/
160 PROCEDURE get_DAxml_payroll_action(p_payroll_action_id in number
161 ,p_deposit_start_date out nocopy date
162 ,p_deposit_end_date out nocopy date
163 ,p_assignment_set_id out nocopy number
164 ,p_payroll_id out nocopy number
165 ,p_consolidation_set_id out nocopy number
166 )
167 IS
168
169 cursor c_get_payroll_action
170 (cp_payroll_action_id in number) is
171 select legislative_parameters,
172 start_date,
173 effective_date
174 from pay_payroll_actions
175 where payroll_action_id = cp_payroll_action_id;
176
177 lv_legislative_parameters VARCHAR2(2000);
178
179 ln_assignment_set_id NUMBER;
180 ln_payroll_id NUMBER;
181 ln_consolidation_set_id NUMBER;
182 ld_deposit_start_date DATE;
183 ld_deposit_end_date DATE;
184
185 BEGIN
186 hr_utility.trace('Entering get_DAxml_payroll_action');
187 open c_get_payroll_action(p_payroll_action_id);
188 fetch c_get_payroll_action into lv_legislative_parameters,
189 ld_deposit_start_date,
190 ld_deposit_end_date;
191 hr_utility.trace('lv_legislative_parameters := ' || lv_legislative_parameters);
192 hr_utility.trace('ld_deposit_start_date := ' || TO_CHAR(ld_deposit_start_date));
193 hr_utility.trace('ld_deposit_end_date := ' || TO_CHAR(ld_deposit_end_date));
194
195 close c_get_payroll_action;
196
197 ln_assignment_set_id := pay_us_payroll_utils.get_parameter(
198 'ASSIGNMENT_SET_ID',
199 lv_legislative_parameters);
200 ln_payroll_id := pay_us_payroll_utils.get_parameter(
201 'PAYROLL_ID',
202 lv_legislative_parameters);
203 ln_consolidation_set_id := pay_us_payroll_utils.get_parameter(
204 'CONSOLIDATION_SET_ID',
205 lv_legislative_parameters);
206
207 hr_utility.trace('ln_assignment_set_id := ' || ln_assignment_set_id);
208 hr_utility.trace('ln_payroll_id := ' || ln_payroll_id);
209 hr_utility.trace('ln_consolidation_set_id := ' || ln_consolidation_set_id);
210
211 p_deposit_start_date := ld_deposit_start_date;
212 p_deposit_end_date := ld_deposit_end_date;
213 p_payroll_id := ln_payroll_id;
214 p_assignment_set_id := ln_assignment_set_id;
215 p_consolidation_set_id := ln_consolidation_set_id;
216
217 hr_utility.trace('Leaving get_DAxml_payroll_action');
218
219 END get_DAxml_payroll_action;
220
221 /********************************************************************
222 ** Procedure : range_cursor
223 ** Description: This is used for both Live and Archive Deposit
224 ** advice reports.
225 ********************************************************************/
226 PROCEDURE range_cursor (pactid in number, sqlstr out NOCOPY varchar2)
227 IS
228
229 ln_assignment_set_id NUMBER;
230 ln_payroll_id NUMBER;
231 ln_consolidation_set_id NUMBER;
232 ld_deposit_start_date DATE;
233 ld_deposit_end_date DATE;
234
235 --Bug 3331028
236 l_db_version varchar2(20);
237
238 BEGIN
239 get_payroll_action(p_payroll_action_id => pactid
240 ,p_deposit_start_date => ld_deposit_start_date
241 ,p_deposit_end_date => ld_deposit_end_date
242 ,p_assignment_set_id => ln_assignment_set_id
243 ,p_payroll_id => ln_payroll_id
244 ,p_consolidation_set_id => ln_consolidation_set_id);
245
246 --Database Version --Bug 3331028
247 if (nvl(hr_general2.get_oracle_db_version, 0) < 10.0) then
248 l_db_version := '/*+ RULE */';
249 else
250 l_db_version := '/* NO RULE*/';
251 end if;
252
253 -- Query is modified for better performance Bug3512116--
254
255 if ln_payroll_id is not null then
256 sqlstr := 'select '||l_db_version||' distinct paa.assignment_id
257 from pay_assignment_actions paa,
258 pay_payroll_actions ppa
259 where :payroll_action_id is not null
260 and ppa.action_type in (''R'',''Q'')
261 and ppa.consolidation_set_id = ' || ln_consolidation_set_id
262 || ' and ppa.payroll_id = ' || ln_payroll_id
263 || ' and ppa.effective_date between ''' || ld_deposit_start_date
264 || ''' and ''' || ld_deposit_end_date
265 || ''' and paa.payroll_action_id = ppa.payroll_action_id
266 order by paa.assignment_id';
267
268 else
269 sqlstr := 'select '||l_db_version||' distinct paa.assignment_id
270 from pay_assignment_actions paa,
271 pay_payroll_actions ppa
272 where :payroll_action_id is not null
273 and ppa.action_type in (''R'',''Q'')
274 and ppa.consolidation_set_id = ' || ln_consolidation_set_id
275 || ' and ppa.effective_date between ''' || ld_deposit_start_date
276 || ''' and ''' || ld_deposit_end_date
277 || ''' and paa.payroll_action_id = ppa.payroll_action_id
278 order by paa.assignment_id';
279 end if;
280
281 END RANGE_CURSOR;
282
283 /**********************************************************************
284 ** FUNCTION : check_if_assignment_paid
285 ** Parameters :
286 ** Description: Bug 3512116
287 ** Function call is added for eliminating the cursor
288 ** c_actions_zero_pay. This is called in the archive
289 ** action creation cursor
290 **********************************************************************/
291 FUNCTION check_if_assignment_paid(p_prepayment_action_id in number,
292 p_deposit_start_date in date,
293 p_deposit_end_date in date,
294 p_consolidation_set_id in number)
295 RETURN VARCHAR2
296 IS
297
298 cursor c_nacha_run
299 (cp_prepayment_action_id in number,
300 cp_deposit_start_date in date,
301 cp_deposit_end_date in date,
302 cp_consolidation_set_id in number
303 ) is
304 select 1
305 from dual
306 where exists
307 (select 1
308 from pay_action_interlocks pai_mag,
309 pay_assignment_actions paa_mag,
310 pay_payroll_actions ppa_mag,
311 pay_org_payment_methods_f popm,
312 pay_pre_payments ppp,
313 pay_payment_types ppt
314 where pai_mag.locked_action_id = cp_prepayment_action_id
315 and pai_mag.locking_Action_id = paa_mag.assignment_action_id
316 and paa_mag.payroll_action_id = ppa_mag.payroll_action_id
317 and ppa_mag.action_type = 'M'
318
319 and pai_mag.locked_action_id = ppp.assignment_action_id
320 and ppp.value > 0
321 and ppp.org_payment_method_id = popm.org_payment_method_id
322 and ppa_mag.effective_date between popm.effective_start_date
323 and popm.effective_end_date
324 and popm.DEFINED_BALANCE_ID is not null
325 and popm.payment_type_id = ppt.payment_type_id
326 and ppt.territory_code = 'US'
327 and ppt.payment_type_name = 'NACHA'
328
329 and ppa_mag.effective_date between cp_deposit_start_date
330 and cp_deposit_end_date
331 and ppa_mag.consolidation_set_id +0 = cp_consolidation_set_id
332 and ppa_mag.ORG_PAYMENT_METHOD_ID = popm.org_payment_method_id);
333
334 cursor c_no_prepayments (cp_prepayment_action_id in number) is
335 select 1
336 from dual
337 where not exists
338 (select 1
339 from pay_pre_payments ppp ,
340 pay_org_payment_methods_f popm
341 where ppp.assignment_action_id = cp_prepayment_action_id
342 and popm.ORG_PAYMENT_METHOD_ID = ppp.org_payment_method_id
343 and popm.defined_balance_id is not NULL );
344
345 lc_nacha_flag VARCHAR2(1);
346 lc_no_prepayment_flag VARCHAR2(1);
347
348 lc_return_flag VARCHAR2(1);
349
350 BEGIN
351 hr_utility.trace(' p_prepayment_action_id '|| p_prepayment_action_id);
352 hr_utility.trace(' p_deposit_start_date ' || to_char(p_deposit_start_date));
353 hr_utility.trace(' p_deposit_end_date ' || to_char(p_deposit_end_date));
354 hr_utility.trace(' p_consolidation_set_id '|| p_consolidation_set_id);
355
356 lc_return_flag := 'N';
357 open c_nacha_run(p_prepayment_action_id,
358 p_deposit_start_date,
359 p_deposit_end_date,
360 p_consolidation_set_id);
361 fetch c_nacha_run into lc_nacha_flag;
362 if c_nacha_run%found then
363 lc_return_flag := 'Y';
364 else
365 open c_no_prepayments(p_prepayment_action_id);
366 fetch c_no_prepayments into lc_no_prepayment_flag;
367 if c_no_prepayments%found then
368 lc_return_flag := 'Y';
369 end if;
370 close c_no_prepayments;
371 end if;
372 close c_nacha_run;
373
374 return (lc_return_flag);
375
376 END check_if_assignment_paid;
377 --
378 --
379 FUNCTION check_if_qualified_for_US(p_archive_action_id IN NUMBER
380 ,p_assignment_id IN NUMBER
381 ,p_deposit_start_date IN DATE
382 ,p_deposit_end_date IN DATE
383 ,p_consolidation_set_id IN NUMBER)
384 RETURN VARCHAR2
385 IS
386 /****************************************************************
387 ** If archiver is locking the pre-payment assignment_action_id,
388 ** we get it from interlocks.
389 ****************************************************************/
390 cursor c_prepay_arch_action(cp_assignment_action_id in number) is
391 select paa.assignment_action_id
392 from pay_action_interlocks paci,
393 pay_assignment_actions paa,
394 pay_payroll_actions ppa
395 where paci.locking_action_id = cp_assignment_action_id
396 and paa.assignment_action_id = paci.locked_action_id
397 and ppa.payroll_action_id = paa.payroll_action_id
398 and ppa.action_type in ('P', 'U');
399
400 /****************************************************************
401 ** If archiver is locking the run assignment_action_id, we get
402 ** the corresponding run assignment_action_id and then get
403 ** the pre-payment assignemnt_action_id.
404 ** This cursor is only required when there are child action which
405 ** means there is a seperate check.
406 * ***************************************************************/
407 cursor c_prepay_run_arch_action(cp_assignment_action_id in number) is
408 select paa_pre.assignment_action_id
409 from pay_action_interlocks pai_run,
410 pay_action_interlocks pai_pre,
411 pay_assignment_actions paa_pre,
412 pay_payroll_actions ppa_pre
413 where pai_run.locking_action_id = cp_assignment_action_id
414 and pai_pre.locked_action_id = pai_run.locked_action_id
415 and paa_pre.assignment_Action_id = pai_pre.locking_action_id
416 and ppa_pre.payroll_action_id = paa_pre.payroll_action_id
417 and ppa_pre.action_type in ('P', 'U');
418
419 ln_prepay_action_id NUMBER;
420 lv_rev_run_exists VARCHAR2(1);
421 BEGIN
422 --
423 --
424 hr_utility.trace('Entering check_if_qualified_for_US');
425
426 OPEN c_prepay_arch_action(p_archive_action_id);
427 FETCH c_prepay_arch_action INTO ln_prepay_action_id;
428 IF c_prepay_arch_action%notfound THEN
429 OPEN c_prepay_run_arch_action(p_archive_action_id);
430 FETCH c_prepay_run_arch_action INTO ln_prepay_action_id;
431 IF c_prepay_run_arch_action%notfound THEN
432 RETURN('N');
433 END IF;
434 CLOSE c_prepay_run_arch_action;
435 END IF;
436 CLOSE c_prepay_arch_action;
437 --
438 --
439 hr_utility.trace('ln_prepay_action_id :='||ln_prepay_action_id);
440
441 IF pay_us_employee_payslip_web.get_doc_eit(
442 'PAYSLIP'
443 ,'PRINT'
444 ,'ASSIGNMENT'
445 ,p_assignment_id
446 ,p_deposit_end_date) = 'Y'
447 AND pay_us_deposit_advice_pkg.check_if_assignment_paid(
448 ln_prepay_action_id
449 ,p_deposit_start_date
450 ,p_deposit_end_date
451 ,p_consolidation_set_id) = 'Y' --Bug 3512116
452 THEN
453
454 lv_rev_run_exists := NULL;
455
456 BEGIN
457 SELECT '1'
458 INTO lv_rev_run_exists
459 FROM dual
460 where exists
461 (Select /*+ ORDERED */ 1
462 from pay_action_interlocks pai_run, --Pre > Run
463 pay_action_interlocks pai_rev, --Run > Rev
464 pay_assignment_actions paa_rev, --Rev
465 pay_payroll_actions ppa_rev --Rev
466 where pai_run.locking_action_id = ln_prepay_action_id
467 and pai_rev.locked_action_id = pai_run.locked_action_id
468 and paa_rev.assignment_action_id = pai_run.locking_action_id
469 and ppa_rev.payroll_action_id = paa_rev.payroll_action_id
470 and ppa_rev.action_type in ('V')
471 );
472 EXCEPTION
473 WHEN OTHERS THEN
474 lv_rev_run_exists := NULL;
475 END;
476
477 IF lv_rev_run_exists = '1' then
478 RETURN 'N';
479 ELSE
480 RETURN 'Y';
481 END IF;
482 ELSE
483 RETURN 'N';
484 END IF;
485 END check_if_qualified_for_US;
486
487 /*********************************************************************
488 ** Name : archive_action_creation
489 ** Description: Archive Assignment Action for archive deposit advice report
490 **
491 *********************************************************************/
492 PROCEDURE archive_action_creation(pactid in number,
493 stperson in number,
494 endperson in number,
495 chunk in number)
496 IS
497
498 -- Bug 3512116 -- Cursor changed to improve performance.
499 cursor c_paid_actions
500 (cp_start_person in number,
501 cp_end_person in number,
502 cp_payroll_id in number,
503 cp_consolidation_set_id in number,
504 cp_deposit_start_date in date,
505 cp_deposit_end_date in date) is
506 select /*+ ORDERED */
507 paa_xfr.assignment_action_id,
508 paa_xfr.assignment_id,
509 paa_xfr.tax_unit_id
510 from
511 pay_payroll_actions ppa_xfr,
512 pay_assignment_actions paa_xfr,
513 pay_action_interlocks pai_pre
514 where ppa_xfr.report_type = 'XFR_INTERFACE'
515 and ppa_xfr.report_category = 'RT'
516 and ppa_xfr.report_qualifier = 'FED'
517 and cp_deposit_end_date between ppa_xfr.start_date
518 and ppa_xfr.effective_date
519 and pay_us_payroll_utils.get_parameter('TRANSFER_CONSOLIDATION_SET_ID',
520 ppa_xfr.legislative_parameters)
521 = cp_consolidation_set_id
522 and paa_xfr.payroll_action_id = ppa_xfr.payroll_action_id
523 -- the statement below will make sure only Pre Payment Archive Actions are picked up
524 and substr(paa_xfr.serial_number,1,1) not in ('V', 'B')
525 and pai_pre.locking_Action_id = paa_xfr.assignment_action_id
526 and (cp_payroll_id is null
527 or
528 pay_us_payroll_utils.get_parameter('TRANSFER_PAYROLL_ID',
529 ppa_xfr.legislative_parameters)
530 = cp_payroll_id
531 )
532 and paa_xfr.assignment_id between cp_start_person and cp_end_person
533 and pay_us_employee_payslip_web.get_doc_eit(
534 'PAYSLIP','PRINT',
535 'ASSIGNMENT',paa_xfr.assignment_id,
536 cp_deposit_end_date) = 'Y'
537 and pay_us_deposit_advice_pkg.check_if_assignment_paid(
538 pai_pre.locked_action_id,
539 cp_deposit_start_date,
540 cp_deposit_end_date,
541 cp_consolidation_set_id) = 'Y' --Bug 3512116
542 and not exists
543 (Select /*+ ORDERED */ 1
544 from pay_action_interlocks pai_run, --Pre > Run
545 pay_action_interlocks pai_rev, --Run > Rev
546 pay_assignment_actions paa_rev, --Rev
547 pay_payroll_actions ppa_rev --Rev
548 where pai_run.locking_action_id = pai_pre.locked_action_id
549 and pai_rev.locked_action_id = pai_run.locked_action_id
550 and paa_rev.assignment_action_id = pai_run.locking_action_id
551 and ppa_rev.payroll_action_id = paa_rev.payroll_action_id
552 and ppa_rev.action_type in ('V')
553 )
554 and exists ( select 1
555 from pay_action_information pai
556 where pai.action_context_id = paa_xfr.assignment_action_id
557 and rownum < 2
558 ) order by paa_xfr.assignment_id desc;
559
560 /* cursor c_actions_zero_pay is now removed from here Bug 3512116*/
561
562
563 ln_assignment_id NUMBER;
564 ln_tax_unit_id NUMBER;
565 ln_nacha_action_id NUMBER;
566 ln_deposit_action_id NUMBER;
567
568 lv_legislative_parameters VARCHAR2(2000);
569 ln_assignment_set_id NUMBER;
570 ln_payroll_id NUMBER;
571 ln_consolidation_set_id NUMBER;
572
573 lc_asg_flag VARCHAR2(1);
574
575 -- Bug 3512116
576 ld_deposit_start_date DATE;
577 ld_deposit_end_date DATE;
578
579
580
581 BEGIN
582
583 get_payroll_action(p_payroll_action_id => pactid
584 ,p_deposit_start_date => ld_deposit_start_date
585 ,p_deposit_end_date => ld_deposit_end_date
586 ,p_assignment_set_id => ln_assignment_set_id
587 ,p_payroll_id => ln_payroll_id
588 ,p_consolidation_set_id => ln_consolidation_set_id);
589
590 hr_utility.set_location('procdar archive',1);
591 open c_paid_actions(stperson, endperson,
592 ln_payroll_id,
593 ln_consolidation_set_id,
594 ld_deposit_start_date,
595 ld_deposit_end_date);
596 loop
597 hr_utility.set_location('procdar archive',2);
598
599 lc_asg_flag := 'N';
600
601 fetch c_paid_actions into ln_nacha_action_id,
602 ln_assignment_id,
603 ln_tax_unit_id;
604 exit WHEN c_paid_actions%NOTFOUND;
605
606 lc_asg_flag := hr_assignment_set.assignment_in_set(
607 ln_assignment_set_id,
608 ln_assignment_id);
609
610 IF lc_asg_flag = 'Y' THEN
611 hr_utility.trace(' c_paid_actions.ln_nacha_action_id is'
612 ||to_char(ln_nacha_action_id));
613
614
615
616
617 hr_utility.set_location('procdar archive',3);
618 select pay_assignment_actions_s.nextval
619 into ln_deposit_action_id
620 from dual;
621
622 -- insert the action record.
623 hr_nonrun_asact.insact(ln_deposit_action_id,
624 ln_assignment_id,
625 pactid, chunk, ln_tax_unit_id);
626 hr_utility.trace('Inserted into paa');
627 -- insert an interlock to this action.
628 hr_nonrun_asact.insint(ln_deposit_action_id, ln_nacha_action_id);
629
630 update pay_assignment_Actions
631 set serial_number = ln_nacha_action_id
632 where assignment_action_id = ln_deposit_action_id;
633
634 END IF;
635
636 end loop;
637 close c_paid_actions;
638
639 hr_utility.set_location('procdar archive',4);
640
641 END archive_action_creation;
642
643 /***********************************************************************
644 ** Name : sort_action
645 ** Description: This cursor is used to sort the data in the report based
646 on the parameter entered by the user when submitting the
647 report.
648
649 This procedure is used by both Live and Archive Deposit
650 Advice Reports.
651 **********************************************************************/
652 PROCEDURE sort_action(procname in varchar2,
653 sqlstr in out NOCOPY varchar2,
654 len out NOCOPY number)
655 IS
656
657 --Bug 3331028
658 l_db_version varchar2(20);
659
660 BEGIN
661 -- Databse Version --Bug 3331028
662 if (nvl(hr_general2.get_oracle_db_version, 0) < 10.0) then
663 l_db_version := '/*+ RULE */';
664 else
665 l_db_version := '/* NO RULE*/';
666 end if;
667
668 sqlstr := 'select '||l_db_version||' paa.rowid
669 from hr_all_organization_units hou,
670 per_all_people_f ppf,
671 per_all_assignments_f paf,
672 pay_assignment_actions paa,
673 pay_payroll_actions ppa
674 where ppa.payroll_action_id = :pactid
675 and paa.payroll_action_id = ppa.payroll_action_id
676 and paa.assignment_id = paf.assignment_id
677 and paf.effective_start_date =
678 (select max(paf1.effective_start_date)
679 from per_all_assignments_f paf1
680 where paf1.assignment_id = paf.assignment_id
681 and paf1.effective_start_date <= ppa.effective_date
682 and paf1.effective_end_date >= ppa.start_date
683 )
684 and paf.person_id = ppf.person_id
685 and ppa.effective_date between ppf.effective_start_date
686 and ppf.effective_end_date
687 and hou.organization_id
688 = nvl(paf.organization_id, paf.business_group_id)
689 order by hou.name,ppf.last_name,ppf.first_name
690 for update of paa.assignment_id';
691
692 len := length(sqlstr); -- return the length of the string.
693
694 END sort_action;
695
696 --Bug 3812668
697 -------------------------- action_creation ---------------------------------
698 PROCEDURE action_creation(pactid in number,
699 stperson in number,
700 endperson in number,
701 chunk in number) is
702 --Bug 3331028
703 l_db_version varchar2(20);
704 l_paid_actions varchar2(5000);
705
706 TYPE PaidActions is REF CURSOR;
707 c_paid_actions PaidActions;
708
709 -------------------------------------------Assignment Set concept
710 CURSOR c_actions_asg_set
711 (
712 pactid number,
713 stperson number,
714 endperson number,
715 p_assignment_set_id number,
716 payid number,
717 consetid number
718 ) is
719 select distinct act.assignment_action_id,
720 act.assignment_id,
721 act.tax_unit_id,
722 ppa_mag.effective_date,
723 ppa_mag.action_type
724 from pay_assignment_actions act,
725 pay_payroll_actions ppa_dar,
726 pay_payroll_actions ppa_mag,
727 pay_org_payment_methods_f popm,
728 pay_all_payrolls_f ppf,
729 per_all_assignments_f paf2,
730 hr_assignment_sets has,
731 hr_assignment_set_amendments hasa
732 where ppa_dar.payroll_action_id = pactid
733 and has.assignment_set_id = p_assignment_set_id
734 and ppa_mag.effective_date between
735 ppa_dar.start_date and ppa_dar.effective_date
736 and ppa_mag.consolidation_set_id = consetid
737 and (( has.payroll_id is null
738 and nvl(ppa_mag.payroll_id,ppf.payroll_id) =
739 nvl(payid, nvl(ppa_mag.payroll_id,ppf.payroll_id))
740 ) or
741 nvl(ppa_mag.payroll_id,has.payroll_id) = has.payroll_id
742 )
743 and ppa_mag.effective_date between
744 ppf.effective_start_date and ppf.effective_end_date
745 and ppf.Payroll_id >= 0
746 and act.payroll_action_id = ppa_mag.payroll_action_id
747 and act.action_status = 'C'
748 and ppa_mag.action_type in ('M','P','U')
749 and decode(ppa_mag.action_type,'M',
750 ppa_mag.org_payment_method_id,
751 popm.org_payment_method_id) = popm.org_payment_method_id
752 and popm.defined_balance_id is not null
753 and ppa_mag.effective_date between
754 popm.effective_start_date and popm.effective_end_date
755 and hasa.assignment_set_id = has.assignment_set_id
756 and hasa.assignment_id = act.assignment_id
757 and hasa.include_or_exclude = 'I'
758 and paf2.assignment_id = act.assignment_id
759 and ppa_dar.effective_date between
760 paf2.effective_start_date and paf2.effective_end_date
761 and paf2.payroll_id + 0 = ppf.payroll_id
762 and act.assignment_id between stperson and endperson
763 -- No run results.
764 and NOT EXISTS (SELECT ' '
765 FROM pay_pre_payments ppp,
766 pay_org_payment_methods_f popm
767 WHERE ppp.assignment_action_id = decode(act.source_action_id,NULL
768 ,act.assignment_action_id,
769 act.source_action_id) --Bug 3928576.Check only for master actions.
770 and ppp.org_payment_method_id = popm.org_payment_method_id
771 and popm.defined_balance_id IS NOT NULL)
772 -- and is not a reversal.
773 and not exists
774 ( select ''
775 from pay_action_interlocks int2,
776 pay_action_interlocks int3,
777 pay_assignment_actions paa4,
778 pay_payroll_actions ppa_run, --- RUN
779 pay_payroll_actions pact4, --- Reversal
780 pay_assignment_actions paa_run, --- RUN
781 pay_assignment_actions paa_pp --- PREPAY
782 where int3.locked_action_id = act.assignment_action_id
783 and int3.locking_action_id = paa_pp.assignment_action_id
784 and int2.locked_action_id = paa_pp.assignment_action_id
785 and int2.locking_action_id = paa_run.assignment_action_id
786 and paa_run.payroll_action_id = ppa_run.payroll_action_id
787 and ppa_run.action_type in ('R', 'Q')
788 and paa_run.assignment_action_id = int3.locked_action_id
789 and int3.locking_action_id = paa4.assignment_action_id
790 and pact4.payroll_action_id = paa4.payroll_action_id
791 and pact4.action_type = 'V'
792 )
793 order by act.assignment_id;
794
795 -------------------------------------------
796
797 /*****************************************************************
798 ** This cursor solves problem when there are multiple pre-payments
799 ** and multiple assignment actions , in this case we only want 1
800 ** assignment action for each pre-payment (bug 890222)
801 *****************************************************************/
802 cursor c_pre_payments (cp_nacha_action_id in number) is
803 select locked_action_id
804 from pay_action_interlocks pai
805 where pai.locking_action_id = cp_nacha_action_id;
806
807 cursor c_payroll_run (cp_pre_pymt_action_id in number) is
808 select assignment_action_id
809 from pay_action_interlocks pai,
810 pay_assignment_actions paa
811 where pai.locking_action_id = cp_pre_pymt_action_id
812 and paa.assignment_Action_id = pai.locked_action_id
813 and paa.run_type_id is null
814 order by action_sequence desc;
815
816 /*****************************************************************
817 ** This cursor will get all the source actions for which the
818 ** assignment should get a deposit advice.
819 ** assignment action for each pre-payment (bug 890222) i.e.
820 ** Seperate Depsoit Advice for Seperate Check and Regular Run
821 *****************************************************************/
822 cursor c_payments (cp_pre_pymt_action_id in number,
823 cp_effective_date in date) is
824 select distinct ppp.source_action_id
825 from pay_pre_payments ppp,
826 pay_personal_payment_methods_f pppm
827 where ppp.assignment_action_id = cp_pre_pymt_action_id
828 and pppm.personal_payment_method_id = ppp.personal_payment_method_id
829 and pppm.external_account_id is not null
830 and cp_effective_date between pppm.effective_start_date
831 and pppm.effective_end_date
832 and nvl(ppp.value,0) <> 0
833 order by ppp.source_action_id;
834
835 ln_nacha_action_id NUMBER;
836 ln_deposit_action_id NUMBER;
837
838 ln_assignment_id NUMBER;
839 ln_tax_unit_id NUMBER;
840 ld_effective_date DATE;
841
842 ln_pre_pymt_action_id NUMBER;
843 ln_prev_pre_pymt_action_id NUMBER := null;
844
845 ln_source_action_id NUMBER;
846 ln_prev_source_action_id NUMBER := null;
847
848 ln_master_action_id NUMBER;
849
850 l_asg_set_id hr_assignment_sets.assignment_set_id%TYPE;
851
852 ld_deposit_start_date DATE;
853 ld_deposit_end_date DATE;
854 ln_payroll_id NUMBER;
855 ln_consolidation_set_id NUMBER;
856 lv_ass_set_on VARCHAR2(10);
857 Zero_Pay_Flag Varchar2(1);
858 ln_person_id number;
859 lv_action_type varchar2(1);
860 BEGIN
861 hr_utility.set_location('procdar',1);
862
863 -- Database Version --Bug 3331028
864 if (nvl(hr_general2.get_oracle_db_version, 0) < 10.0) then
865 l_db_version := '/*+ RULE */';
866 else
867 l_db_version := '/* NO RULE*/';
868 end if;
869
870 --
871 get_payroll_action(p_payroll_action_id => pactid
872 ,p_deposit_start_date => ld_deposit_start_date
873 ,p_deposit_end_date => ld_deposit_end_date
874 ,p_assignment_set_id => l_asg_set_id
875 ,p_payroll_id => ln_payroll_id
876 ,p_consolidation_set_id => ln_consolidation_set_id);
877
878 hr_utility.trace('pactid='||pactid);
879 hr_utility.trace('ln_payroll_id='||ln_payroll_id);
880 hr_utility.trace('ln_consolidation_set_id='||ln_consolidation_set_id);
881
882 -- Query string for the reference cursor c_paid_actions
883 l_paid_actions := 'select distinct '||l_db_version||' act.assignment_action_id,
884 act.assignment_id,
885 act.tax_unit_id,
886 ppa_mag.effective_date,
887 ppa_mag.action_type
888 from pay_assignment_actions act,
889 pay_payroll_actions ppa_dar,
890 pay_payroll_actions ppa_mag,
891 per_all_assignments_f paf2,
892 pay_org_payment_methods_f popm, --Bug 3009643
893 pay_payrolls_f pay --Bug 3343621
894 where ppa_dar.payroll_action_id = :pactid
895 and ppa_mag.consolidation_set_id +0 = '|| nvl(ln_consolidation_set_id,0) ||'
896 and ppa_mag.effective_date between
897 ppa_dar.start_date and ppa_dar.effective_date
898 and act.payroll_action_id = ppa_mag.payroll_action_id
899 and act.action_status = ''C''
900 and ppa_mag.action_type in (''M'',''P'',''U'')
901 and decode(ppa_mag.action_type,''M'',
902 ppa_mag.org_payment_method_id,
903 popm.org_payment_method_id) = popm.org_payment_method_id -- Bug 3009643
904 and popm.defined_balance_id is not null -- Bug 3009643
905 and ppa_mag.effective_date between
906 popm.effective_start_date and popm.effective_end_date --Bug 3009643
907 and nvl(ppa_mag.payroll_id,pay.payroll_id) = pay.payroll_id --Bug 3343621
908 and ppa_mag.effective_date between
909 pay.effective_start_date and pay.effective_end_date --Bug 3343621
910 and pay.payroll_id >= 0 --Bug 3343621
911 and paf2.assignment_id = act.assignment_id
912 and ppa_dar.effective_date between
913 paf2.effective_start_date and paf2.effective_end_date
914 and paf2.payroll_id = pay.payroll_id
915 and act.assignment_id between :stperson and :endperson
916 and (' || NVL(ln_payroll_id,-99999) || ' = -99999
917 or
918 pay.payroll_id = ' || NVL(ln_payroll_id,-99999) ||'
919 )
920
921 -- No run results.
922 and NOT EXISTS (SELECT '' ''
923 FROM pay_pre_payments ppp,
924 pay_org_payment_methods_f popm
925 WHERE ppp.assignment_action_id = decode(act.source_action_id,NULL
926 ,act.assignment_action_id,
927 act.source_action_id) --Bug 3928576.Check only for master actions.
928 and ppp.org_payment_method_id = popm.org_payment_method_id
929 and popm.defined_balance_id IS NOT NULL)
930 -- and is not a reversal.
931 and not exists
932 (
933 Select ''''
934 from pay_action_interlocks int2,
935 pay_action_interlocks int3,
936 pay_assignment_actions paa4,
937 pay_payroll_actions ppa_run, --- RUN
938 pay_payroll_actions pact4, --- Reversal
939 pay_assignment_actions paa_run, --- RUN
940 pay_assignment_actions paa_pp --- PREPAY
941 where
942 int3.locked_action_id = act.assignment_action_id
943 and int3.locking_action_id = paa_pp.assignment_action_id
944 and int2.locked_action_id = paa_pp.assignment_action_id
945 and int2.locking_action_id = paa_run.assignment_action_id
946 and paa_run.payroll_action_id = ppa_run.payroll_action_id
947 and ppa_run.action_type in (''R'', ''Q'')
948 and paa_run.assignment_action_id = int3.locked_action_id
949 and int3.locking_action_id = paa4.assignment_action_id
950 and pact4.payroll_action_id = paa4.payroll_action_id
951 and pact4.action_type = ''V''
952 )
953 order by act.assignment_id DESC';
954
955 -- Reference cursor opened for the query string l_paid_actions --Bug 3331028
956 if l_asg_set_id is not null then
957 open c_actions_asg_set(pactid,stperson,endperson,l_asg_set_id,
958 ln_payroll_id,ln_consolidation_set_id);
959 else
960 open c_paid_actions for l_paid_actions using pactid, stperson, endperson;
961 end if;
962 --
963 loop
964 hr_utility.set_location('procdar',2);
965 if l_asg_set_id is not null then
966 fetch c_actions_asg_set into ln_nacha_action_id, ln_assignment_id,
967 ln_tax_unit_id, ld_effective_date,
968 lv_action_type;
969 exit WHEN c_actions_asg_set%NOTFOUND;
970 else
971 fetch c_paid_actions into ln_nacha_action_id, ln_assignment_id,
972 ln_tax_unit_id, ld_effective_date,
973 lv_action_type;
974 exit WHEN c_paid_actions%NOTFOUND;
975 end if;
976
977 if lv_action_type ='M' then
978 Zero_Pay_Flag := 'N';
979 else
980 Zero_Pay_Flag := 'Y';
981 end if;
982
983 hr_utility.trace(' c_paid_actions.ln_nacha_action_id is'
984 ||to_char(ln_nacha_action_id));
985
986 open c_pre_payments (ln_nacha_action_id);
987 fetch c_pre_payments into ln_pre_pymt_action_id;
988 close c_pre_payments;
989 hr_utility.trace(' c_pre_payments.ln_pre_pymt_action_id is'
990 ||to_char(ln_pre_pymt_action_id));
991
992 if Zero_Pay_Flag = 'N' then
993 hr_utility.trace(' Not a Zero Pay Assignment');
994 /**************************************************************************
995 ** we need to insert atleast one action for each of the rows that we
996 ** return from the cursor (i.e. one for each assignment/pre-payment action).
997 **************************************************************************/
998 hr_utility.trace(' ln_prev_pre_pymt_action_id is'
999 ||to_char(ln_prev_pre_pymt_action_id));
1000 if (ln_prev_pre_pymt_action_id is null or
1001 ln_prev_pre_pymt_action_id <> ln_pre_pymt_action_id) then
1002 open c_payments (ln_pre_pymt_action_id, ld_effective_date);
1003 loop
1004 hr_utility.set_location('procdar',99);
1005 fetch c_payments into ln_source_action_id;
1006 hr_utility.trace(' ln_source_action_id is'
1007 ||to_char(ln_source_action_id));
1008
1009 hr_utility.set_location('procdar',98);
1010 if c_payments%notfound then
1011 exit;
1012 end if;
1013 hr_utility.set_location('procdar',97);
1014 /**************************************************************
1015 ** we need to insert one action for each of the rows that we
1016 ** return from the cursor (i.e. one for each assignment/pre-payment source).
1017 **************************************************************/
1018 hr_utility.trace(' ln_prev_source_action_id is'
1019 ||to_char(ln_prev_source_action_id));
1020 if (ln_prev_source_action_id is null or
1021 ln_source_action_id <> ln_prev_source_action_id or
1022 ln_source_action_id is null) then
1023
1024 hr_utility.set_location('procdar',3);
1025 select pay_assignment_actions_s.nextval
1026 into ln_deposit_action_id
1027 from dual;
1028
1029 -- insert the action record.
1030 hr_nonrun_asact.insact(ln_deposit_action_id,
1031 ln_assignment_id,
1032 pactid, chunk, ln_tax_unit_id);
1033 hr_utility.trace('Inserted into paa');
1034 -- insert an interlock to this action.
1035 hr_nonrun_asact.insint(ln_deposit_action_id, ln_nacha_action_id);
1036
1037 hr_utility.trace('Inserted into interlock');
1038 if ln_source_action_id is not null then
1039 hr_utility.trace('serial number updated if loop ');
1040 hr_utility.trace('serial number is '||to_char(ln_source_action_id));
1041 update pay_assignment_Actions
1042 set serial_number = 'P'||ln_source_action_id
1043 where assignment_action_id = ln_deposit_action_id;
1044 else
1045 hr_utility.trace('serial number else ');
1046 open c_payroll_run (ln_pre_pymt_action_id);
1047 fetch c_payroll_run into ln_master_action_id;
1048 close c_payroll_run;
1049 hr_utility.trace(' ln_master_action_id is'
1050 ||to_char(ln_master_action_id));
1051
1052 update pay_assignment_Actions
1053 set serial_number = 'M'||ln_master_action_id
1054 where assignment_action_id = ln_deposit_action_id;
1055 end if;
1056 -- skip till next source action id
1057 ln_prev_source_action_id := ln_source_action_id;
1058 end if;
1059 end loop;
1060 close c_payments;
1061 ln_prev_pre_pymt_action_id := ln_pre_pymt_action_id;
1062 end if;
1063 elsif Zero_Pay_Flag = 'Y' then
1064 hr_utility.trace('Zero Pay Assignment');
1065 if (ln_prev_pre_pymt_action_id is null or
1066 ln_prev_pre_pymt_action_id <> ln_pre_pymt_action_id) then
1067 hr_utility.set_location('procdar',6);
1068 select pay_assignment_actions_s.nextval
1069 into ln_deposit_action_id
1070 from dual;
1071
1072 -- insert the action record.
1073 hr_nonrun_asact.insact(ln_deposit_action_id,
1074 ln_assignment_id,
1075 pactid, chunk, ln_tax_unit_id);
1076
1077 -- insert an interlock to this action.
1078 hr_nonrun_asact.insint(ln_deposit_action_id, ln_nacha_action_id);
1079 hr_utility.trace(' NZ Inserted into paa');
1080
1081
1082 open c_payroll_run (ln_nacha_action_id);
1083 fetch c_payroll_run into ln_master_action_id;
1084 close c_payroll_run;
1085
1086 update pay_assignment_Actions
1087 set serial_number = 'M'||ln_master_action_id
1088 where assignment_action_id = ln_deposit_action_id;
1089
1090 hr_utility.trace(' NZ ln_master_action_id is'
1091 ||to_char(ln_master_action_id));
1092
1093 -- skip till next pre payment action id
1094 ln_prev_pre_pymt_action_id := ln_pre_pymt_action_id;
1095 end if;
1096 end if; -- ZERO PAY
1097 end loop;
1098
1099 if l_asg_set_id is not null then
1100 close c_actions_asg_set;
1101 else
1102 close c_paid_actions;
1103 end if;
1104 END action_creation;
1105
1106 -- Following Procedure has been added for de-initialization
1107 -- To be used by (Archive) Deposit Advice process generating
1108 -- PDF Output.
1109
1110 procedure archive_deinit(pactid in number) is
1111 begin
1112
1113 --pay_core_xdo_utils.archive_deinit(pactid) ;
1114 pay_archive.remove_report_actions(pactid) ;
1115
1116 end archive_deinit ;
1117 --
1118 --------------------------------------------------------------------------------
1119 -- QUALIFYING_PROC
1120 --------------------------------------------------------------------------------
1121 PROCEDURE qualifying_proc(p_assignment_id IN NUMBER
1122 ,p_qualifier OUT NOCOPY VARCHAR2 ) IS
1123 --
1124 l_actid NUMBER;
1125 l_rep_group pay_report_groups.report_group_name%TYPE;
1126 l_rep_category pay_report_categories.category_name%TYPE;
1127 l_effective_date DATE;
1128 l_business_group_id NUMBER;
1129 l_assignment_set_id NUMBER;
1130 l_assignment_id NUMBER;
1131 l_inc_exc VARCHAR2(1);
1132 l_asg_inc_exc VARCHAR2(1);
1133 --
1134 l_payroll_id NUMBER;
1135 l_consolidation_set_id NUMBER;
1136 l_start_date VARCHAR2(20);
1137 l_end_date VARCHAR2(20);
1138 l_legislation_code VARCHAR2(10);
1139 l_start_dt DATE;
1140 l_end_dt DATE;
1141 l_qualifier VARCHAR2(1);
1142 --
1143 ln_curr_payroll_act_id NUMBER;
1144 k NUMBER;
1145 ln_assignment_id NUMBER;
1146 ln_action_ctx_id NUMBER;
1147 --
1148 sql_cur NUMBER;
1149 l_rows NUMBER;
1150 statem VARCHAR2(256);
1151 --
1152 CURSOR csr_asg(c_payroll_id NUMBER
1153 ,c_consolidation_set_id NUMBER
1154 ,c_start_date DATE
1155 ,c_end_date DATE
1156 ,c_pa_token VARCHAR2
1157 ,c_cs_token VARCHAR2
1158 ,c_legislation_code VARCHAR2) IS
1159 SELECT /* 'Y' */
1160 distinct paa.assignment_id
1161 ,pai.action_context_id
1162 FROM pay_assignment_actions paa
1163 ,pay_payroll_actions ppa
1164 ,hr_lookups hrl
1165 ,pay_action_information pai
1166 ,per_time_periods ptp
1167 WHERE /* paa.assignment_id = c_assignment_id */
1168 ppa.effective_Date BETWEEN c_start_date
1169 AND c_end_date
1170 AND ppa.report_type = hrl.meaning
1171 AND hrl.lookup_type = 'PAYSLIP_REPORT_TYPES'
1172 AND hrl.lookup_code = c_legislation_code
1173 AND NVL(c_payroll_id,NVL(pay_payslip_report.get_parameter(ppa.legislative_parameters,c_pa_token),-1))
1174 = NVL(pay_payslip_report.get_parameter(ppa.legislative_parameters,c_pa_token),-1)
1175 AND c_consolidation_set_id = pay_payslip_report.get_parameter(ppa.legislative_parameters,c_cs_token)
1176
1177 --
1178 --
1179 AND ppa.payroll_action_id = paa.payroll_action_id
1180 AND paa.source_action_id IS NULL
1181 --
1182 --
1183 AND pai.assignment_id = paa.assignment_id
1184 AND pai.action_context_type = 'AAP'
1185 AND pai.action_information_category = 'EMPLOYEE DETAILS'
1186 AND pai.action_context_id = paa.assignment_action_id
1187 AND ptp.time_period_id = pai.ACTION_INFORMATION16;
1188 /*
1189 AND check_if_qualified_for_US(pai.action_context_id
1190 ,paa.assignment_id
1191 ,c_start_date
1192 ,c_end_date
1193 ,c_consolidation_set_id) = 'Y';
1194 */
1195 --
1196 CURSOR csr_inc_asg(c_payroll_id NUMBER
1197 ,c_consolidation_set_id NUMBER
1198 ,c_start_date DATE
1199 ,c_end_date DATE
1200 ,c_pa_token VARCHAR2
1201 ,c_cs_token VARCHAR2
1202 ,c_legislation_code VARCHAR2
1203 ,c_assignment_set_id NUMBER ) IS
1204 SELECT /* 'Y' */
1205 distinct paa.assignment_id
1206 ,pai.action_context_id
1207 FROM pay_assignment_actions paa
1208 ,pay_payroll_actions ppa
1209 ,hr_lookups hrl
1210 ,hr_assignment_set_amendments hasa
1211 ,pay_action_information pai
1212 ,per_time_periods ptp
1213 WHERE ppa.effective_Date BETWEEN c_start_date
1214 AND c_end_date
1215 AND ppa.report_type = hrl.meaning
1216 AND hrl.lookup_type = 'PAYSLIP_REPORT_TYPES'
1217 AND hrl.lookup_code = c_legislation_code
1218 AND NVL(c_payroll_id,NVL(pay_payslip_report.get_parameter(ppa.legislative_parameters,c_pa_token),-1))
1219 = NVL(pay_payslip_report.get_parameter(ppa.legislative_parameters,c_pa_token),-1)
1220 AND c_consolidation_set_id = pay_payslip_report.get_parameter(ppa.legislative_parameters,c_cs_token)
1221 AND ppa.payroll_action_id = paa.payroll_action_id
1222 AND paa.source_action_id IS NULL
1223 AND paa.assignment_id = hasa.assignment_id
1224 AND hasa.assignment_set_id = c_assignment_set_id
1225 AND hasa.include_or_exclude = 'I'
1226 AND pai.assignment_id = paa.assignment_id
1227 AND pai.action_context_type = 'AAP'
1228 AND pai.action_information_category = 'EMPLOYEE DETAILS'
1229 AND pai.action_context_id = paa.assignment_action_id
1230 AND ptp.time_period_id = pai.ACTION_INFORMATION16;
1231 /*
1232 AND check_if_qualified_for_US(pai.action_context_id
1233 ,paa.assignment_id
1234 ,c_start_date
1235 ,c_end_date
1236 ,c_consolidation_set_id) = 'Y';
1237 */
1238 --
1239 -- The Assignment Set Logic is handled only for either Include or Exclude
1240 -- and not for both. This doesn't handle the assignment_set_criteria.
1241 --
1242 CURSOR csr_inc_exc(c_assignment_set_id NUMBER
1243 ,c_assignment_id NUMBER) IS
1244 SELECT include_or_exclude
1245 FROM hr_assignment_set_amendments
1246 WHERE assignment_set_id = c_assignment_set_id
1247 AND assignment_id = nvl(c_assignment_id,assignment_id);
1248 --
1249 --
1250 --
1251
1252 BEGIN
1253 hr_utility.trace('###### IN Qualifying Proc');
1254 --
1255 l_actid := pay_proc_environment_pkg.get_pactid;
1256 --
1257 ln_curr_payroll_act_id := l_actid;
1258 hr_utility.trace('In QualProc l_actid := ' || l_actid);
1259 hr_utility.trace('p_assignment_id := ' || p_assignment_id);
1260
1261 IF pay_us_deposit_advice_pkg.g_payroll_act_id <> ln_curr_payroll_act_id THEN
1262 pay_us_deposit_advice_pkg.g_payroll_act_id := ln_curr_payroll_act_id;
1263
1264 pay_payslip_report.get_all_parameters(l_actid
1265 ,l_payroll_id
1266 ,l_consolidation_set_id
1267 ,l_start_date
1268 ,l_end_date
1269 ,l_rep_group
1270 ,l_rep_category
1271 ,l_assignment_set_id
1272 ,l_assignment_id
1273 ,l_effective_date
1274 ,l_business_group_id
1275 ,l_legislation_code);
1276
1277 --hr_utility.trace('l_payroll_id :='||l_payroll_id);
1278 --hr_utility.trace('l_consolidation_set_id :='||l_consolidation_set_id);
1279 --hr_utility.trace('l_start_date :='||l_start_date);
1280 --hr_utility.trace('l_end_date :='||l_end_date);
1281 --hr_utility.trace('l_rep_group :='||l_rep_group);
1282 --hr_utility.trace('l_rep_category :='||l_rep_category);
1283 --hr_utility.trace('l_assignment_set_id :='||l_assignment_set_id);
1284 --hr_utility.trace('l_assignment_id :='||l_assignment_id);
1285 --hr_utility.trace('l_effective_date :='||l_effective_date);
1286 --hr_utility.trace('l_business_group_id :='||l_business_group_id);
1287 --hr_utility.trace('l_legislation_code :='||l_legislation_code);
1288
1289 --
1290 l_start_dt := TO_DATE(l_start_date,'YYYY/MM/DD');
1291 l_end_dt := TO_DATE(l_end_date,'YYYY/MM/DD');
1292 --
1293 -- Fetching legislative prameters for the very first time
1294 -- And caching them into global variables.
1295
1296 pay_us_deposit_advice_pkg.g_payroll_id := l_payroll_id;
1297 pay_us_deposit_advice_pkg.g_consolidation_set_id := l_consolidation_set_id;
1298 pay_us_deposit_advice_pkg.g_start_dt := l_start_dt;
1299 pay_us_deposit_advice_pkg.g_end_dt := l_end_dt;
1300 pay_us_deposit_advice_pkg.g_rep_group := l_rep_group;
1301 pay_us_deposit_advice_pkg.g_rep_category := l_rep_category;
1302 pay_us_deposit_advice_pkg.g_assignment_set_id := l_assignment_set_id;
1303 pay_us_deposit_advice_pkg.g_assignment_id := l_assignment_id;
1304 pay_us_deposit_advice_pkg.g_effective_date := l_effective_date;
1305 pay_us_deposit_advice_pkg.g_business_group_id := l_business_group_id;
1306 pay_us_deposit_advice_pkg.g_legislation_code := l_legislation_code;
1307
1308 --
1309 DECLARE
1310 BEGIN
1311 statem := 'BEGIN pay_'||l_legislation_code||'_rules.get_token_names(:p_pa_token, :p_cs_token); END;';
1312 --hr_utility.trace(statem);
1313 sql_cur := dbms_sql.open_cursor;
1314 dbms_sql.parse(sql_cur
1315 ,statem
1316 ,dbms_sql.v7);
1317 dbms_sql.bind_variable(sql_cur, 'p_pa_token', pay_payslip_report.g_pa_token, 50);
1318 dbms_sql.bind_variable(sql_cur, 'p_cs_token', pay_payslip_report.g_cs_token, 50);
1319 l_rows := dbms_sql.execute(sql_cur);
1320 dbms_sql.variable_value(sql_cur, 'p_pa_token', pay_payslip_report.g_pa_token);
1321 dbms_sql.variable_value(sql_cur, 'p_cs_token', pay_payslip_report.g_cs_token);
1322 dbms_sql.close_cursor(sql_cur);
1323 Exception
1324 WHEN OTHERS THEN
1325 pay_payslip_report.g_pa_token := NVL(pay_payslip_report.g_pa_token,'PAYROLL_ID');
1326 pay_payslip_report.g_cs_token := NVL(pay_payslip_report.g_cs_token,'CONSOLIDATION_SET_ID');
1327 --
1328 IF dbms_sql.IS_OPEN(sql_cur) THEN
1329 dbms_sql.close_cursor(sql_cur);
1330 END IF;
1331 END;
1332 --
1333 --
1334 --hr_utility.trace('pay_payslip_report.g_pa_token :='||pay_payslip_report.g_pa_token);
1335 --hr_utility.trace('pay_payslip_report.g_cs_token :='||pay_payslip_report.g_cs_token);
1336
1337 IF pay_us_deposit_advice_pkg.g_assignment_set_id IS NULL THEN
1338 OPEN csr_asg(pay_us_deposit_advice_pkg.g_payroll_id
1339 ,pay_us_deposit_advice_pkg.g_consolidation_set_id
1340 ,pay_us_deposit_advice_pkg.g_start_dt
1341 ,pay_us_deposit_advice_pkg.g_end_dt
1342 ,pay_payslip_report.g_pa_token
1343 ,pay_payslip_report.g_cs_token
1344 ,pay_us_deposit_advice_pkg.g_legislation_code);
1345 LOOP
1346
1347 ln_assignment_id := -1;
1348 ln_action_ctx_id := -1;
1349
1350 FETCH csr_asg INTO ln_assignment_id, ln_action_ctx_id;
1351
1352 IF csr_asg%NOTFOUND THEN
1353 EXIT;
1354 ELSE
1355 IF check_if_qualified_for_US(ln_action_ctx_id
1356 ,ln_assignment_id
1357 ,pay_us_deposit_advice_pkg.g_start_dt
1358 ,pay_us_deposit_advice_pkg.g_end_dt
1359 ,pay_us_deposit_advice_pkg.g_consolidation_set_id) = 'Y' THEN
1360
1361 g_tmp_tbl(ln_assignment_id) := ln_assignment_id;
1362 hr_utility.trace('g_tmp_tbl(' || ln_assignment_id || ') := ' || ln_assignment_id);
1363
1364 END IF;
1365 END IF;
1366
1367 END LOOP;
1368 CLOSE csr_asg;
1369 --
1370 ELSE
1371 OPEN csr_inc_asg(pay_us_deposit_advice_pkg.g_payroll_id
1372 ,pay_us_deposit_advice_pkg.g_consolidation_set_id
1373 ,pay_us_deposit_advice_pkg.g_start_dt
1374 ,pay_us_deposit_advice_pkg.g_end_dt
1375 ,pay_payslip_report.g_pa_token
1376 ,pay_payslip_report.g_cs_token
1377 ,pay_us_deposit_advice_pkg.g_legislation_code
1378 ,pay_us_deposit_advice_pkg.g_assignment_set_id);
1379 LOOP
1380
1381 ln_assignment_id := -1;
1382 ln_action_ctx_id := -1;
1383
1384 FETCH csr_inc_asg INTO ln_assignment_id, ln_action_ctx_id;
1385
1386 IF csr_inc_asg%NOTFOUND THEN
1387 EXIT;
1388 ELSE
1389 IF check_if_qualified_for_US(ln_action_ctx_id
1390 ,ln_assignment_id
1391 ,pay_us_deposit_advice_pkg.g_start_dt
1392 ,pay_us_deposit_advice_pkg.g_end_dt
1393 ,pay_us_deposit_advice_pkg.g_consolidation_set_id) = 'Y' THEN
1394
1395 g_tmp_tbl(ln_assignment_id) := ln_assignment_id;
1396 hr_utility.trace('g_tmp_tbl(' || ln_assignment_id || ') := ' || ln_assignment_id);
1397 END IF;
1398 END IF;
1399
1400 END LOOP;
1401 CLOSE csr_inc_asg;
1402
1403 END IF;
1404 END IF;
1405
1406 l_qualifier := 'N';
1407 k := 1;
1408
1409 hr_utility.trace('g_tmp_tbl.COUNT := ' || g_tmp_tbl.COUNT);
1410
1411 IF g_tmp_tbl.EXISTS(p_assignment_id) THEN
1412 l_qualifier := 'Y';
1413 END IF;
1414
1415 hr_utility.trace('B4 Return l_qualifier := ' || l_qualifier);
1416
1417
1418 IF l_qualifier = 'Y' THEN
1419 p_qualifier := 'Y' ;
1420 END IF;
1421
1422 END qualifying_proc;
1423 --
1424 --
1425 /********************************************************************
1426 ** Procedure : DAxml_range_cursor
1427 ** Description: This is used for DA (XML) program
1428 **
1429 ********************************************************************/
1430 PROCEDURE DAxml_range_cursor(pactid in number
1431 ,psqlstr out NOCOPY varchar2)
1432 IS
1433
1434 ln_assignment_set_id NUMBER;
1435 ln_payroll_id NUMBER;
1436 ln_consolidation_set_id NUMBER;
1437 ld_deposit_start_date DATE;
1438 ld_deposit_end_date DATE;
1439
1440 --Bug 3331028
1441 l_db_version varchar2(20);
1442 --
1443 lv_rep_group pay_report_groups.report_group_name%TYPE;
1444 lv_rep_category pay_report_categories.category_name%TYPE;
1445 l_assignment_id NUMBER;
1446 ld_effective_date DATE;
1447 ln_business_group_id NUMBER;
1448 lv_legislation_code VARCHAR2(10);
1449 lv_sqlstr VARCHAR2(32000);
1450
1451 BEGIN
1452 hr_utility.trace('Entering into Func DAxml_range_cursor');
1453 get_DAxml_payroll_action(p_payroll_action_id => pactid
1454 ,p_deposit_start_date => ld_deposit_start_date
1455 ,p_deposit_end_date => ld_deposit_end_date
1456 ,p_assignment_set_id => ln_assignment_set_id
1457 ,p_payroll_id => ln_payroll_id
1458 ,p_consolidation_set_id => ln_consolidation_set_id);
1459
1460 hr_utility.trace('ln_payroll_id :='||ln_payroll_id);
1461 hr_utility.trace('ln_consolidation_set_id :='||ln_consolidation_set_id);
1462 hr_utility.trace('ld_deposit_start_date :='||ld_deposit_start_date);
1463 hr_utility.trace('ld_deposit_end_date :='||ld_deposit_end_date);
1464 hr_utility.trace('ln_assignment_set_id :='||ln_assignment_set_id);
1465 --
1466 --
1467
1468 --Database Version --Bug 3331028
1469 if (nvl(hr_general2.get_oracle_db_version, 0) < 10.0) then
1470 l_db_version := '/*+ RULE */';
1471 else
1472 l_db_version := '/* NO RULE*/';
1473 end if;
1474
1475 if ln_payroll_id is not null then
1476 if ln_assignment_set_id is not null then
1477
1478 lv_sqlstr := 'select '||l_db_version||' distinct paf.person_id
1479 from pay_assignment_actions paa,
1480 pay_payroll_actions ppa,
1481 per_assignments_f paf,
1482 hr_assignment_set_amendments hasa
1483 where :payroll_action_id is not null
1484 and ppa.action_type in (''R'',''Q'')
1485 and paa.assignment_id = paf.assignment_id
1486 and ppa.consolidation_set_id = ' || ln_consolidation_set_id
1487 || ' and ppa.payroll_id = ' || ln_payroll_id
1488 || ' and ppa.effective_date between ''' || ld_deposit_start_date
1489 || ''' and ''' || ld_deposit_end_date
1490 || ''' and paa.payroll_action_id = ppa.payroll_action_id'
1491 || ' and paa.assignment_id = hasa.assignment_id'
1492 || ' and hasa.assignment_set_id = ' || ln_assignment_set_id
1493 || ' and hasa.include_or_exclude = ''I'''
1494 || ' order by paf.person_id';
1495 else
1496 lv_sqlstr := 'select '||l_db_version||' distinct paf.person_id
1497 from pay_assignment_actions paa,
1498 pay_payroll_actions ppa,
1499 per_assignments_f paf
1500 where :payroll_action_id is not null
1501 and ppa.action_type in (''R'',''Q'')
1502 and paa.assignment_id = paf.assignment_id
1503 and ppa.consolidation_set_id = ' || ln_consolidation_set_id
1504 || ' and ppa.payroll_id = ' || ln_payroll_id
1505 || ' and ppa.effective_date between ''' || ld_deposit_start_date
1506 || ''' and ''' || ld_deposit_end_date
1507 || ''' and paa.payroll_action_id = ppa.payroll_action_id
1508 order by paf.person_id';
1509 end if; -- ln_assignment_set_id NOT NULL
1510
1511 else
1512 if ln_assignment_set_id is not null then
1513 lv_sqlstr := 'select '||l_db_version||' distinct paf.person_id
1514 from pay_assignment_actions paa,
1515 pay_payroll_actions ppa,
1516 per_assignments_f paf,
1517 hr_assignment_set_amendments hasa
1518 where :payroll_action_id is not null
1519 and ppa.action_type in (''R'',''Q'')
1520 and paa.assignment_id = paf.assignment_id
1521 and ppa.consolidation_set_id = ' || ln_consolidation_set_id
1522 || ' and ppa.effective_date between ''' || ld_deposit_start_date
1523 || ''' and ''' || ld_deposit_end_date
1524 || ''' and paa.payroll_action_id = ppa.payroll_action_id'
1525 || ' and paa.assignment_id = hasa.assignment_id'
1526 || ' and hasa.assignment_set_id = ' || ln_assignment_set_id
1527 || ' and hasa.include_or_exclude = ''I'''
1528 || ' order by paf.person_id';
1529
1530 else
1531 lv_sqlstr := 'select '||l_db_version||' distinct paf.person_id
1532 from pay_assignment_actions paa,
1533 pay_payroll_actions ppa,
1534 per_assignments_f paf
1535 where :payroll_action_id is not null
1536 and ppa.action_type in (''R'',''Q'')
1537 and paa.assignment_id = paf.assignment_id
1538 and ppa.consolidation_set_id = ' || ln_consolidation_set_id
1539 || ' and ppa.effective_date between ''' || ld_deposit_start_date
1540 || ''' and ''' || ld_deposit_end_date
1541 || ''' and paa.payroll_action_id = ppa.payroll_action_id
1542 order by paf.person_id';
1543 end if; -- ln_assignment_set_id NOT NULL
1544
1545 end if;
1546
1547 hr_utility.trace('lv_sqlstr := ' || lv_sqlstr);
1548
1549 psqlstr := lv_sqlstr;
1550
1551 end DAxml_range_cursor;
1552 --
1553 --
1554 end pay_us_deposit_advice_pkg;
[Home] [Help]