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