1 package body pycadar_pkg as
2 /* $Header: pycadar.pkb 120.2.12020000.6 2013/03/04 06:14:57 sbachu ship $ */
3 /*
4 --
5 rem +======================================================================+
6 rem | Copyright (c) 1993 Oracle Corporation |
7 rem | Redwood Shores, California, USA |
8 rem | All rights reserved. |
9 rem +======================================================================+
10 Name :pycadar
11 --
12 Change List
13 -----------
14 Date Name Vers Description
15 ----------- ---------- ----- -----------------------------------
16 03-JUN-1999 mmukherj 110.0 Created
17 07-AUG-2000 mmukherj 115.1 Taken out serveroutput on and dbms_output
18 23-MAR-2001 vpandya 115.2 Added get_labels function with three
19 input parameters.
20 09-MAY-2002 vpandya 115.4 Both Regular Salary and an element set up as
21 separate check like Bonus were printing only
22 one deposit advice.
23 Modified action creation cursor to
24 achieve this functonality.
25 12-JUN-2002 vpandya 115.5 For Multiple Assignment Payment functionality
26 Added procedure archive_action_creation that
27 will create assignment action when Canadian
28 Deposit Advice is run for Archiver.
29 17-JUL-2002 vpandya 115.6 Changed archive_action_creation and replaced
30 pay.multi_assignments_flag with
31 nvl(pay.multi_assignments_flag,'N') in SQL.
32 20-SEP-2002 pganguly 115.7 Changed the cursor c_actions, added
33 pay_payrolls_f in the from list. Added a NVL
34 in the where clause so if the payroll is not
35 passed then join to the ppa_mag.payroll_id.
36 Also joined the consolidation id passed with
37 consolidation_id of the pay_payrolls_f.
38 21-SEP-2002 pganguly 115.8 Added whenever oserror ...
39 04-NOV-2002 pganguly 115.9 Fixed Bug# 2579614. Added code in the range
40 cursor/assignment_action_creation so that
41 if assignment set is passed then it prints
42 'Deposit Advice' for those assignments only.
43 27-Jan-2003 vpandya 115.10 Fixed Bug# 2763252. Modified action_creation
44 and added condtion to check payroll id of
45 pay_payrolls_f if payroll id is null for
46 Direct Deposit(when Direct Deposit is run by
47 consolidation set only), so Deposit advice
48 should work for consolidation set only or
49 consolidation set along with payroll.
50 27-Jan-2003 vpandya 115.11 Added nocopy with out parameter as per gscc.
51 25-Feb-2003 vpandya 115.12 Changed archive_action_creation, checking
52 fetch only those assignment action for that
53 archiver has been run.
54 24-Mar-2003 vpandya 115.13 Bug 2862554: Changed archive_action_creation,
55 added distinct and person id in cursor.
56 03-Apr-2003 vpandya 115.14 Bug 2882568: Changed archive_action_creation,
57 commented line paa_run.source_action_id is
58 null to create assignment action for all GREs
59 13-May-2003 vpandya 115.15 Bug 2942093: Changed action_creation for live
60 Deposit Advice. Added distinct in select
61 clause and assignment_action_id in order by
62 clause in c_action cursor to get uniq deposit
63 advice.
64 22-Jul-2003 vpandya 115.16 Bug 3046204: Changed action_creation for live
65 Deposit Advice to print zero net pay deposit
66 advice.
67 24-Mar-2004 ssattini 115.17 Bug 3331023: 11510 changes done in
68 range_cursor and action_creation procedures
69 by removing rule hint and tuning them. Still
70 changes need to be done for archive_action_
71 creation procedure.
72 27-Jul-2004 ssattini 115.20 Bug 3438254: 11510 Performance changes done.
73 Changed the cursors and logic in
74 archive_action_creation procedure, also
75 added get_payroll_action procedure and
76 check_if_assignment_paid function. Tuned
77 c_actions_asg_set cursor in action_creation
78 procedure. Used the get_payroll_action
79 in range_cursor, action_creation procedures.
80 Added assignment_set validation logic for
81 c_actions_zero_pay records in
82 action_creation procedure.
83 15-Mar-2005 ssouresr 115.21 The condition that the consolidation set
84 should be linked to a payroll has been
85 removed from the range cursor and the
86 action creation functions
87 27-Apr-2005 sackumar 115.22 Bug 3800169. Modification in the logic of
88 action_creation_procedure. Merge the Zero pay
89 cursor in the c_action and c_actions_asg_set
90 cursor and introduce a flag_variable for zero pay
91 in the cursor fetch loop.
92 16-JUN-2005 mmukherj Removed the changes mentioned in 115.22.
93 in 115.22 the changes has been done by
94 merging the two cursors c_actions_zero_pay
95 and c_actions_asg_set. So what was happening
96 is that the cursoe c_actions_asg_set was
97 being called only if the assignment_set has
98 been passed , so the zero pay actions was not
99 checked if the Deposit Advice was not run
100 with assignment set. That was not the intention
101 of this fix.
102 22-Sep-2011 Pkoduri 115.24 bug#12377778 - Added the order by clause to the range cursor,
103 for the scenario where we run deposit advice with
104 Assignment Set.
105 This is to ensure that all the employees in that range
106 are picked up - because later we process only the emp
107 between the start and end assignments returned by range cursor.
108 05-MAR-2012 sgotlasw 115.25 " CANADIAN DEPOSIT ADVICE (XML) ENHANCEMENT "
109 Added following methods and set of new variables.
110 qualifying_proc - This method is invoked from
111 action creation code of generic package,
112 PAY_GENERIC_UPGRADE.
113 This qualifying procedure name is mentioned
114 PAY_REPORT_GROUPS table for 'PAYSLIP_REPORT_CA'
115 check_if_qualified_for_CA
116 08-MAR-2012 sgotlasw 115.26 Commented out condition in csr_asg, csr_inc_asg.
117 12-APR-2012 sgotlasw 115.27 Modified cursor in archive action creation code to pick
118 all the employees when 'Canadian Deposit Advice' is run
119 for particular date range.
120 11-MAY-2012 sgotlasw 115.28 Modified 'c_direct_deposit_run' cursor to check if the
121 payment is voided.
122 01-MAR-2013 sgotlasw 115.29 Created 'c_non_void_pymts' cursor to check if any of
123 non void payments exists for given archive action id
124 before creating assignment_actions.
125
126 --
127 --
128
129 */
130
131 ---------------------------------- get_payroll_action -------------------
132 /**********************************************************************
133 ** PROCEDURE : get_payroll_action
134 ** Description: Bug 3438254
135 ** This procedure returns the details for payroll action for
136 ** deposit advice. This is called in the range cursor,
137 ** action_creation and archive_action_creation procedures.
138 **********************************************************************/
139 PROCEDURE get_payroll_action(p_payroll_action_id in number
140 ,p_deposit_start_date out nocopy date
141 ,p_deposit_end_date out nocopy date
142 ,p_assignment_set_id out nocopy number
143 ,p_payroll_id out nocopy number
144 ,p_consolidation_set_id out nocopy number
145 )
146 IS
147 cursor c_get_payroll_action
148 (cp_payroll_action_id in number) is
149 select legislative_parameters,
150 start_date,
151 effective_date
152 from pay_payroll_actions
153 where payroll_action_id = cp_payroll_action_id;
154
155 lv_legislative_parameters VARCHAR2(2000);
156
157 ln_assignment_set_id NUMBER;
158 ln_payroll_id NUMBER;
159 ln_consolidation_set_id NUMBER;
160 ld_deposit_start_date DATE;
161 ld_deposit_end_date DATE;
162
163 BEGIN
164 open c_get_payroll_action(p_payroll_action_id);
165 fetch c_get_payroll_action into lv_legislative_parameters,
166 ld_deposit_start_date,
167 ld_deposit_end_date;
168 close c_get_payroll_action;
169
170 ln_assignment_set_id := pycadar_pkg.get_parameter(
171 'ASG_SET_ID',
172 lv_legislative_parameters);
173 ln_payroll_id := pycadar_pkg.get_parameter(
174 'PAYROLL_ID',
175 lv_legislative_parameters);
176 ln_consolidation_set_id := pycadar_pkg.get_parameter(
177 'CONSOLIDATION_SET_ID',
178 lv_legislative_parameters);
179
180 p_deposit_start_date := ld_deposit_start_date;
181 p_deposit_end_date := ld_deposit_end_date;
182 p_payroll_id := ln_payroll_id;
183 p_assignment_set_id := ln_assignment_set_id;
184 p_consolidation_set_id := ln_consolidation_set_id;
185
186 END get_payroll_action;
187
188 ----------------------------------- range_cursor ------------------------------
189 --
190 procedure range_cursor (pactid in number, sqlstr out nocopy varchar2) is
191 l_payroll_id number;
192 -- leg_param pay_payroll_actions.legislative_parameters%type;
193 l_asg_set_id hr_assignment_sets.assignment_set_id%TYPE;
194
195 --Bug 3331023
196 l_db_version varchar2(20);
197 -- Bug#4338254
198 ld_deposit_start_date DATE;
199 ld_deposit_end_date DATE;
200 ln_consolidation_set_id NUMBER;
201 --
202 begin
203
204 get_payroll_action(p_payroll_action_id => pactid
205 ,p_deposit_start_date => ld_deposit_start_date
206 ,p_deposit_end_date => ld_deposit_end_date
207 ,p_assignment_set_id => l_asg_set_id
208 ,p_payroll_id => l_payroll_id
209 ,p_consolidation_set_id => ln_consolidation_set_id);
210
211 /* Removed old code to use get_payroll_action bug#3438254 */
212
213 hr_utility.trace('l_payroll_id = ' || to_char(l_payroll_id));
214 hr_utility.trace('l_asg_set_id = ' || to_char(l_asg_set_id));
215
216 --Database Version --Bug 3331023
217
218 if (nvl(hr_general2.get_oracle_db_version, 0) < 10.0) then
219 l_db_version := '/*+ RULE */';
220 else
221 l_db_version := '/* NO RULE*/';
222 end if;
223
224 --
225 if l_asg_set_id is NOT NULL then
226
227 sqlstr := 'select
228 distinct paf.person_id
229 from
230 hr_assignment_set_amendments hasa,
231 per_all_assignments_f paf,
232 pay_payroll_actions ppa
233 where
234 ppa.payroll_action_id = :PACTID and
235 hasa.assignment_set_id = ' || to_char(l_asg_set_id) ||
236 ' and hasa.assignment_id = paf.assignment_id and
237 ppa.effective_date between
238 paf.effective_start_date and
239 paf.effective_end_date
240 order by paf.person_id '; --12377778
241
242 else
243
244 if l_payroll_id is not null then
245 --Bug 3331023-- Rule hint is used only for database version < 10.0
246 sqlstr := 'select '||l_db_version||' distinct pos.person_id
247 from pay_assignment_actions act,
248 per_all_assignments_f asg,
249 per_periods_of_service pos,
250 pay_payroll_actions pa2,
251 pay_payroll_actions pa1,
252 pay_all_payrolls_f ppf
253 where pa1.payroll_action_id = :payroll_action_id
254 and ppf.payroll_id = pycadar_pkg.get_parameter(''PAYROLL_ID'',
255 pa1.legislative_parameters)
256 and pa2.consolidation_set_id =
257 pycadar_pkg.get_parameter(''CONSOLIDATION_SET_ID'',
258 pa1.legislative_parameters)
259 and pa2.payroll_id = ppf.payroll_id
260 and pa2.effective_date between
261 pa1.start_date and pa1.effective_date
262 and pa2.effective_date between
263 ppf.effective_start_date and ppf.effective_end_date
264 and pa2.payroll_action_id= act.payroll_action_id
265 and asg.assignment_id = act.assignment_id
266 and pa2.effective_date between
267 asg.effective_start_date and asg.effective_end_date
268 and pos.period_of_service_id = asg.period_of_service_id
269 order by pos.person_id';
270
271 --
272 else
273 --Bug 3331023-- Rule hint is used only for database version < 10.0
274 sqlstr := 'select '||l_db_version||' distinct pos.person_id
275 from pay_assignment_actions act,
276 per_all_assignments_f asg,
277 per_periods_of_service pos,
278 pay_payroll_actions pa2,
279 pay_payroll_actions pa1,
280 pay_all_payrolls_f ppf
281 where pa1.payroll_action_id = :payroll_action_id
282 and pa2.consolidation_set_id =
283 pycadar_pkg.get_parameter(''CONSOLIDATION_SET_ID'',
284 pa1.legislative_parameters)
285 and pa2.payroll_id = ppf.payroll_id
286 and pa2.effective_date between
287 pa1.start_date and pa1.effective_date
288 and pa2.effective_date between
289 ppf.effective_start_date and ppf.effective_end_date
290 and act.payroll_action_id = pa2.payroll_action_id
291 and asg.assignment_id = act.assignment_id
292 and pa2.effective_date between
293 asg.effective_start_date and asg.effective_end_date
294 and pos.period_of_service_id = asg.period_of_service_id
295 order by pos.person_id';
296
297 --
298 end if; -- l_payroll_id validation
299
300 end if; -- End if Assignment Set ID
301
302 end range_cursor;
303
304
305 ---------------------------------- check_if_assignment_paid -------------------
306 /**********************************************************************
307 ** FUNCTION : check_if_assignment_paid
308 ** Parameters :
309 ** Description: Bug#3438254
310 ** Function call is added for eliminating the cursor
311 ** c_actions_zero_pay. This is called in the archive
312 ** action creation procedure.
313 **********************************************************************/
314 FUNCTION check_if_assignment_paid(p_prepayment_action_id in number,
315 p_deposit_start_date in date,
316 p_deposit_end_date in date,
317 p_consolidation_set_id in number)
318 RETURN VARCHAR2
319 IS
320 cursor c_direct_deposit_run
321 (cp_prepayment_action_id in number,
322 cp_deposit_start_date in date,
323 cp_deposit_end_date in date,
324 cp_consolidation_set_id in number
325 ) is
326 select 1
327 from dual
328 where exists
329 (select 1
330 from pay_action_interlocks pai_mag,
331 pay_assignment_actions paa_mag,
332 pay_payroll_actions ppa_mag
333 where pai_mag.locked_action_id = cp_prepayment_action_id
334 and pai_mag.locking_Action_id = paa_mag.assignment_action_id
335 and paa_mag.payroll_action_id = ppa_mag.payroll_action_id
336 and ppa_mag.action_type = 'M'
337 and ppa_mag.effective_date between cp_deposit_start_date
338 and cp_deposit_end_date
339 and ppa_mag.consolidation_set_id +0 = cp_consolidation_set_id
340 /* Bug 14061942 : Added to check if payment is voided. */
341 and not exists
342 (
343 select 1
344 from pay_action_interlocks pai,
345 pay_assignment_actions paa,
346 pay_payroll_actions ppa
347 where pai.locked_action_id = paa_mag.assignment_action_id
348 and pai.locking_Action_id = paa.assignment_action_id
349 and paa.payroll_action_id = ppa.payroll_action_id
350 and ppa.action_type = 'D'
351 )
352 );
353
354 cursor c_no_prepayments (cp_prepayment_action_id in number) is
355 select 1
356 from dual
357 where not exists
358 (select 1
359 from pay_pre_payments ppp
360 where ppp.assignment_action_id = cp_prepayment_action_id
361 );
362
363 lc_dd_flag VARCHAR2(1);
364 lc_no_prepayment_flag VARCHAR2(1);
365
366 lc_return_flag VARCHAR2(1);
367
368 BEGIN
369 hr_utility.trace(' p_prepayment_action_id '|| to_char(p_prepayment_action_id));
370 hr_utility.trace(' p_deposit_start_date ' || to_char(p_deposit_start_date)); hr_utility.trace(' p_deposit_end_date ' || to_char(p_deposit_end_date));
371 hr_utility.trace(' p_consolidation_set_id '|| to_char(p_consolidation_set_id));
372
373 lc_return_flag := 'N';
374 open c_direct_deposit_run(p_prepayment_action_id,
375 p_deposit_start_date,
376 p_deposit_end_date,
377 p_consolidation_set_id);
378 fetch c_direct_deposit_run into lc_dd_flag;
379 if c_direct_deposit_run%found then
380 lc_return_flag := 'Y';
381 hr_utility.trace('c_direct_deposit_run%found lc_return_flag: '|| lc_return_flag);
382
383 else
384 open c_no_prepayments(p_prepayment_action_id);
385 fetch c_no_prepayments into lc_no_prepayment_flag;
386 if c_no_prepayments%found then
387 lc_return_flag := 'Y';
388 hr_utility.trace('c_no_prepayments%found lc_return_flag: '|| lc_return_flag);
389 end if;
390 close c_no_prepayments;
391 end if;
392 close c_direct_deposit_run;
393
394 return (lc_return_flag);
395
396 END check_if_assignment_paid;
397 ---------------------------------- action_creation -----------------------------
398 --
399 procedure action_creation(pactid in number,
400 stperson in number,
401 endperson in number,
402 chunk in number) is
403
404 --Bug 3331023
405 l_db_version varchar2(20);
406 l_actions varchar2(4000);
407
408 TYPE PaidActions is REF CURSOR;
409 c_actions PaidActions;
410 --
411 --
412 -- Bug#3331023 removed rule hint for c_actions_zero_pay cursor and
413 -- added pay_all_payrolls_f table in the main query to use correct indexes
414
415 CURSOR c_actions_zero_pay
416 (
417 pactid number,
418 stperson number,
419 endperson number
420 ) is
421 select act.assignment_action_id,
422 act.assignment_id,
423 act.tax_unit_id
424 from pay_assignment_actions act,
425 per_all_assignments_f paf1,
426 per_periods_of_service pos,
427 pay_payroll_actions ppa_dar,
428 pay_payroll_actions ppa_mag_pmts,
429 pay_all_payrolls_f ppf
430 where ( ppa_dar.payroll_action_id = pactid
431 and ppa_mag_pmts.consolidation_set_id =
432 pycadar_pkg.get_parameter('CONSOLIDATION_SET_ID',
433 ppa_dar.legislative_parameters)
434 and ppa_mag_pmts.payroll_id = ppf.payroll_id
435 and ppa_mag_pmts.effective_date between ppa_dar.start_date
436 and ppa_dar.effective_date
437 and ppa_mag_pmts.effective_date between ppf.effective_start_date
438 and ppf.effective_end_date
439 and act.payroll_action_id = ppa_mag_pmts.payroll_action_id
440 and act.action_status = 'C'
441 and ppa_mag_pmts.action_type in ('P', 'U')
442 and paf1.assignment_id = act.assignment_id
443 and ppa_mag_pmts.effective_date between
444 paf1.effective_start_date and paf1.effective_end_date
445 and pos.period_of_service_id = paf1.period_of_service_id
446 and pos.person_id between stperson and endperson
447 and (paf1.payroll_id =
448 pycadar_pkg.get_parameter('PAYROLL_ID',
449 ppa_dar.legislative_parameters)
450 or pycadar_pkg.get_parameter('PAYROLL_ID',
451 ppa_dar.legislative_parameters)
452 is null)
453 -- No run results.
454 AND NOT EXISTS (SELECT ' '
455 FROM pay_pre_payments ppp,
456 pay_org_payment_methods_f popm
457 WHERE ppp.assignment_action_id = act.assignment_action_id
458 and ppp.org_payment_method_id = popm.org_payment_method_id
459 and popm.defined_balance_id IS NOT NULL)
460 -- and is not a reversal.
461 AND NOT EXISTS
462 ( Select ' '
463 from pay_action_interlocks int2,
464 pay_action_interlocks int4,
465 pay_assignment_actions paa4,
466 pay_payroll_actions ppa_run, --- RUN
467 pay_payroll_actions pact4, --- Reversal
468 pay_assignment_actions paa_run --- RUN
469 where int2.locking_action_id = act.assignment_action_id -- prepayment action
470 and int2.locked_action_id = paa_run.assignment_action_id
471 and paa_run.payroll_action_id = ppa_run.payroll_action_id
472 and ppa_run.action_type in ('R', 'Q')
473 and paa_run.assignment_action_id = int4.locked_action_id
474 and int4.locking_action_id = paa4.assignment_action_id
475 and pact4.payroll_action_id = paa4.payroll_action_id
476 and pact4.action_type = 'V'
477 )
478 )
479 order by pos.person_id, act.assignment_id DESC;
480
481
482 --
483 -- if assignment_set is passed then the cursor ignores
484 -- consolidation set and payroll passed a takes the payroll
485 -- considation set of the assignment set.
486 --
487 CURSOR c_actions_asg_set
488 (
489 pactid number,
490 stperson number,
491 endperson number,
492 p_assignment_set_id number
493 ) is
494 select act.assignment_action_id,
495 act.assignment_id,
496 act.tax_unit_id
497 from pay_assignment_actions act,
498 per_all_assignments_f paf1,
499 per_all_assignments_f paf2,
500 per_periods_of_service pos,
501 pay_payroll_actions ppa_dar,
502 pay_payroll_actions ppa_mag,
503 pay_all_payrolls_f ppf,
504 hr_assignment_sets has,
505 hr_assignment_set_amendments hasa
506 where ppa_dar.payroll_action_id = pactid
507 and has.assignment_set_id = p_assignment_set_id
508 and ppa_mag.effective_date between
509 ppa_dar.start_date and ppa_dar.effective_date
510 and ppa_mag.consolidation_set_id =
511 pycadar_pkg.get_parameter('CONSOLIDATION_SET_ID',ppa_dar.legislative_parameters)
512
513 and (( has.payroll_id is null
514 and nvl(ppa_mag.payroll_id,ppf.payroll_id) =
515 nvl(pycadar_pkg.get_parameter('PAYROLL_ID',ppa_dar.legislative_parameters),
516 nvl(ppa_mag.payroll_id,ppf.payroll_id))
517 ) or
518
519 nvl(ppa_mag.payroll_id,has.payroll_id) = has.payroll_id
520 )
521 and ppa_mag.effective_date between
522 ppf.effective_start_date and ppf.effective_end_date
523 and act.payroll_action_id = ppa_mag.payroll_action_id
524 and act.action_status = 'C'
525 and ppa_mag.action_type = 'M'
526 and hasa.assignment_set_id = has.assignment_set_id
527 and hasa.assignment_id = act.assignment_id
528 and hasa.include_or_exclude = 'I'
529 and paf1.assignment_id = act.assignment_id
530 and ppa_mag.effective_date between
531 paf1.effective_start_date and paf1.effective_end_date
532 and paf2.assignment_id = act.assignment_id
533 and ppa_dar.effective_date between
534 paf2.effective_start_date and paf2.effective_end_date
535 and paf2.payroll_id + 0 = paf1.payroll_id + 0
536 and pos.period_of_service_id = paf1.period_of_service_id
537 and pos.person_id between stperson and endperson
538 and (paf1.payroll_id = ppa_dar.payroll_id or ppa_dar.payroll_id is null)
539 and not exists
540 ( select ''
541 from pay_action_interlocks int2,
542 pay_action_interlocks int3,
543 pay_assignment_actions paa4,
544 pay_payroll_actions ppa_run, --- RUN
545 pay_payroll_actions pact4, --- Reversal
546 pay_assignment_actions paa_run, --- RUN
547 pay_assignment_actions paa_pp --- PREPAY
548 where int3.locked_action_id = act.assignment_action_id
549 and int3.locking_action_id = paa_pp.assignment_action_id
550 and int2.locked_action_id = paa_pp.assignment_action_id
551 and int2.locking_action_id = paa_run.assignment_action_id
552 and paa_run.payroll_action_id = ppa_run.payroll_action_id
553 and ppa_run.action_type in ('R', 'Q')
554 and paa_run.assignment_action_id = int3.locked_action_id
555 and int3.locking_action_id = paa4.assignment_action_id
556 and pact4.payroll_action_id = paa4.payroll_action_id
557 and pact4.action_type = 'V'
558 )
559 order by act.assignment_id;
560 --
561 /*****************************************************************
562 ** This cursor solves problem when there are multiple pre-payments
563 ** and multiple assignment actions , in this case we only want 1
564 ** assignment action for each pre-payment.
565 *****************************************************************/
566 cursor c_pre_payments (cp_dd_action_id in number) is
567 select locked_action_id
568 from pay_action_interlocks pai
569 where pai.locking_action_id = cp_dd_action_id; --Direct Deposit dd
570
571 /*****************************************************************
572 ** This cursor will get all the source actions for which the
573 ** assignment should get a deposit advice.
574 ** assignment action for each pre-payment (bug 890222) i.e.
575 ** Seperate Depsoit Advice for Seperate Check and Regular Run
576 *****************************************************************/
577 cursor c_payments (cp_pre_pymt_action_id in number) is
578 select distinct ppp.source_action_id
579 from pay_pre_payments ppp
580 where ppp.assignment_action_id = cp_pre_pymt_action_id
581 order by ppp.source_action_id;
582
583 cursor c_payroll_run (cp_pre_pymt_action_id in number) is
584 select assignment_action_id
585 from pay_action_interlocks pai,
586 pay_assignment_actions paa
587 where pai.locking_action_id = cp_pre_pymt_action_id
588 and paa.assignment_Action_id = pai.locked_action_id
589 and paa.run_type_id is null
590 order by action_sequence desc;
591
592 lockingactid number;
593 lockedactid number;
594 assignid number;
595 greid number;
596 num number;
597 --
598 ln_pre_pymt_action_id NUMBER;
599 ln_prev_pre_pymt_action_id NUMBER;
600
601 ln_source_action_id NUMBER;
602 ln_prev_source_action_id NUMBER;
603
604 ln_master_action_id NUMBER;
605
606 ln_prev_asg_act_id NUMBER;
607 --
608 ln_direct_dep_act_id NUMBER;
609 ln_deposit_action_id NUMBER;
610 ln_assignment_id NUMBER;
611 ln_tax_unit_id NUMBER;
612 --
613 l_asg_set_id hr_assignment_sets.assignment_set_id%TYPE;
614 --
615 /* Removed cur_leg_param cursor to use get_payroll_action bug#3438254 */
616
617 -- Bug#4338254
618 ld_deposit_start_date DATE;
619 ld_deposit_end_date DATE;
620 ln_payroll_id NUMBER;
621 ln_consolidation_set_id NUMBER;
622 lv_ass_set_on VARCHAR2(10);
623 --
624 -- algorithm is quite similar to the other process cases,
625 -- but we have to take into account assignments and
626 -- personal payment methods.
627 begin
628 -- hr_utility.trace_on('Y','CAASGSET');
629 hr_utility.set_location('pycadar.action_creation',1);
630 -- Initialising local variables here to avoid GSCC warnings
631 ln_prev_pre_pymt_action_id := null;
632 ln_prev_source_action_id := null;
633 ln_prev_asg_act_id := -999999;
634
635 -- checking Database Version Bug 3331023
636 if (nvl(hr_general2.get_oracle_db_version, 0) < 10.0) then
637 l_db_version := '/*+ RULE */';
638 else
639 l_db_version := '/* NO RULE*/';
640 end if;
641 --
642
643 -- Bug 3331023 Query string for the reference cursor c_actions used rule hint
644 -- if db_version is < 10 Bug 3331023
645 l_actions := 'select '||l_db_version||' distinct act.assignment_action_id,
646 act.assignment_id,
647 act.tax_unit_id
648 from pay_assignment_actions act,
649 per_all_assignments_f paf1,
650 per_all_assignments_f paf2,
651 per_periods_of_service pos,
652 pay_payroll_actions ppa_dar,
653 pay_payroll_actions ppa_mag,
654 pay_all_payrolls_f ppf
655 where ppa_dar.payroll_action_id = :pactid
656 and nvl(ppa_mag.payroll_id,ppf.payroll_id) =
657 NVL(pycadar_pkg.get_parameter(''PAYROLL_ID'',
658 ppa_dar.legislative_parameters),
659 nvl(ppa_mag.payroll_id,ppf.payroll_id))
660 and nvl(ppa_mag.payroll_id,ppf.payroll_id) = ppf.payroll_id
661 and ppa_mag.effective_date between
662 ppf.effective_start_date and ppf.effective_end_date
663 and nvl(ppf.multi_assignments_flag,''N'') = ''N''
664 and ppa_mag.consolidation_set_id + 0 =
665 pycadar_pkg.get_parameter(''CONSOLIDATION_SET_ID'',
666 ppa_dar.legislative_parameters)
667 and ppa_mag.effective_date between
668 ppa_dar.start_date and ppa_dar.effective_date
669 and act.payroll_action_id = ppa_mag.payroll_action_id
670 and act.action_status = ''C''
671 and ppa_mag.action_type = ''M''
672 and paf1.assignment_id = act.assignment_id
673 and ppa_mag.effective_date between
674 paf1.effective_start_date and paf1.effective_end_date
675 and paf2.assignment_id = act.assignment_id
676 and ppa_dar.effective_date between
677 paf2.effective_start_date and paf2.effective_end_date
678 and paf2.payroll_id + 0 = paf1.payroll_id + 0
679 and pos.period_of_service_id = paf1.period_of_service_id
680 and pos.person_id between :stperson and :endperson
681 and (( paf1.payroll_id = pycadar_pkg.get_parameter(''PAYROLL_ID'',
682 ppa_dar.legislative_parameters) )
683 or
684 ( pycadar_pkg.get_parameter(''PAYROLL_ID'',
685 ppa_dar.legislative_parameters) is null )
686 )
687 and not exists
688 (
689 Select ''''
690 from pay_action_interlocks int2,
691 pay_action_interlocks int3,
692 pay_assignment_actions paa4,
693 pay_payroll_actions ppa_run, --- RUN
694 pay_payroll_actions pact4, --- Reversal
695 pay_assignment_actions paa_run, --- RUN
696 pay_assignment_actions paa_pp --- PREPAY
697 where int3.locked_action_id = act.assignment_action_id
698 and int3.locking_action_id = paa_pp.assignment_action_id
699 and int2.locked_action_id = paa_pp.assignment_action_id
700 and int2.locking_action_id = paa_run.assignment_action_id
701 and paa_run.payroll_action_id = ppa_run.payroll_action_id
702 and ppa_run.action_type in (''R'', ''Q'')
703 and paa_run.assignment_action_id = int3.locked_action_id
704 and int3.locking_action_id = paa4.assignment_action_id
705 and pact4.payroll_action_id = paa4.payroll_action_id
706 and pact4.action_type = ''V''
707 )
708 order by act.assignment_id, act.assignment_action_id';
709
710 get_payroll_action(p_payroll_action_id => pactid
711 ,p_deposit_start_date => ld_deposit_start_date
712 ,p_deposit_end_date => ld_deposit_end_date
713 ,p_assignment_set_id => l_asg_set_id
714 ,p_payroll_id => ln_payroll_id
715 ,p_consolidation_set_id => ln_consolidation_set_id);
716
717 /* removed old code to use get_payroll_action bug#3438254 */
718
719 hr_utility.set_location('pycadar.action_creation l_asg_set_id = '
720 ,l_asg_set_id);
721
722 IF l_asg_set_id IS NOT NULL THEN
723 open c_actions_asg_set(pactid,stperson,endperson,l_asg_set_id);
724 ELSE
725
726 -- Reference cursor opened for the query string l_paid_actions Bug 3331023
727 open c_actions for l_actions using pactid, stperson, endperson;
728 END IF;
729
730 num := 0;
731 loop
732 hr_utility.set_location('pycadar.action_creation',2);
733 IF l_asg_set_id IS NOT NULL THEN
734 fetch c_actions_asg_set into lockedactid,assignid,greid;
735 if c_actions_asg_set%found then num := num + 1; end if;
736 exit when c_actions_asg_set%notfound;
737 ELSE
738 fetch c_actions into lockedactid,assignid,greid;
739 if c_actions%found then
740 num := num + 1;
741 end if;
742 exit when c_actions%notfound;
743 END IF;
744 --
745 IF lockedactid <> ln_prev_asg_act_id THEN
746
747 hr_utility.trace(' c_actions.lockedactid is '||to_char(lockedactid));
748 open c_pre_payments (lockedactid);
749 fetch c_pre_payments into ln_pre_pymt_action_id;
750 hr_utility.trace(' c_pre_payments.ln_pre_pymt_action_id is'
751 ||to_char(ln_pre_pymt_action_id));
752 close c_pre_payments;
753
754 -- we need to insert one action for each of the
755 -- rows that we return from the cursor (i.e. one
756 -- for each assignment/pre-payment).
757
758 hr_utility.trace(' ln_prev_pre_pymt_action_id is'
759 ||to_char(ln_prev_pre_pymt_action_id));
760 if (ln_prev_pre_pymt_action_id is null or
761 ln_prev_pre_pymt_action_id <> ln_pre_pymt_action_id) then
762 open c_payments (ln_pre_pymt_action_id);
763 loop
764 hr_utility.set_location('procdar',99);
765 fetch c_payments into ln_source_action_id;
766 hr_utility.trace(' ln_source_action_id is'
767 ||to_char(ln_source_action_id));
768
769 hr_utility.set_location('procdar',98);
770 if c_payments%notfound then
771 exit;
772 end if;
773 hr_utility.set_location('procdar',97);
774 /**************************************************************
775 ** we need to insert one action for each of the rows that we
776 ** return from the cursor (i.e. one for each
777 ** assignment/pre-payment source).
778 **************************************************************/
779 hr_utility.trace(' ln_prev_source_action_id is'
780 ||to_char(ln_prev_source_action_id));
781 if (ln_prev_source_action_id is null or
782 ln_source_action_id <> ln_prev_source_action_id or
783 ln_source_action_id is null) then
784
785 hr_utility.set_location('procdar',3);
786 select pay_assignment_actions_s.nextval
787 into lockingactid
788 from dual;
789
790 -- insert the action record.
791 hr_nonrun_asact.insact(lockingactid,assignid,
792 pactid,chunk,greid);
793 hr_utility.trace('Inserted into paa');
794 hr_utility.trace(' assignment_id is ' ||to_char(assignid));
795 -- insert an interlock to this action.
796 hr_nonrun_asact.insint(lockingactid,lockedactid);
797 hr_utility.trace('Inserted into interlock');
798
799 if ln_source_action_id is not null then
800
801 hr_utility.trace('serial number updated if loop ');
802 hr_utility.trace('serial number is '||ln_source_action_id);
803 update pay_assignment_Actions
804 set serial_number = 'P'||ln_source_action_id
805 --set serial_number = ln_source_action_id
806 where assignment_action_id = lockingactid;
807 else
808 hr_utility.trace('serial number else ');
809 open c_payroll_run (ln_pre_pymt_action_id);
810 fetch c_payroll_run into ln_master_action_id;
811 close c_payroll_run;
812 hr_utility.trace(' ln_master_action_id is'
813 ||to_char(ln_master_action_id));
814
815 update pay_assignment_Actions
816 set serial_number = 'M'||ln_master_action_id
817 --set serial_number = ln_master_action_id
818 where assignment_action_id = lockingactid;
819 end if;
820
821 -- skip till next source action id
822 ln_prev_source_action_id := ln_source_action_id;
823 end if;
824 end loop;
825 close c_payments;
826
827 ln_prev_pre_pymt_action_id := ln_pre_pymt_action_id;
828
829 end if;
830
831 ln_prev_asg_act_id := lockedactid;
832
833 END IF;
834 end loop;
835 if l_asg_set_id is not null then
836 close c_actions_asg_set;
837 else
838 close c_actions;
839 end if;
840
841 /* removed the commented code bug#3438254 */
842
843 hr_utility.set_location('procdar',4);
844 ln_prev_pre_pymt_action_id := null;
845 open c_actions_zero_pay(pactid,stperson,endperson);
846
847 loop
848 hr_utility.set_location('procdar',5);
849 lv_ass_set_on := 'N';
850 hr_utility.trace('Start of c_actions_zero_pay ');
851 fetch c_actions_zero_pay INTO ln_direct_dep_act_id, --gives P,U
852 ln_assignment_id,
853 ln_tax_unit_id;
854 exit WHEN c_actions_zero_pay%NOTFOUND;
855 hr_utility.trace(' NZ PrePayment Id is' ||ln_direct_dep_act_id);
856
857 /* Added this code for Assignment set validation bug#3438254,
858 Otherwise it was displaying all the assignments that are
859 not in the given Assignment Set.
860 */
861 lv_ass_set_on := hr_assignment_set.assignment_in_set(
862 l_asg_set_id,
863 ln_assignment_id);
864 hr_utility.trace('lv_ass_set_on : '||lv_ass_set_on);
865
866 If lv_ass_set_on = 'Y' then
867
868 open c_pre_payments (ln_direct_dep_act_id); --gives me R,Q
869 fetch c_pre_payments into ln_pre_pymt_action_id;
870 close c_pre_payments;
871
872 hr_utility.trace(' NZ Run ActionId is' ||to_char(ln_pre_pymt_action_id));
873 hr_utility.trace(' NZ ln_prev_pre_pymt_action_id is' ||to_char(ln_prev_pre_pymt_action_id));
874
875 if (ln_prev_pre_pymt_action_id is null or
876 ln_prev_pre_pymt_action_id <> ln_pre_pymt_action_id) then
877
878 hr_utility.set_location('procdar',6);
879 select pay_assignment_actions_s.nextval
880 into ln_deposit_action_id
881 from dual;
882
883 -- insert the action record.
884 hr_nonrun_asact.insact(ln_deposit_action_id,
885 ln_assignment_id,
886 pactid, chunk, ln_tax_unit_id);
887
888 -- insert an interlock to this action.
889 hr_nonrun_asact.insint(ln_deposit_action_id,ln_direct_dep_act_id);
890 hr_utility.trace(' NZ Inserted into paa');
891 hr_utility.trace(' Asg id: '||to_char(ln_assignment_id));
892
893 /* removed the commented code bug#3438254 */
894
895 update pay_assignment_Actions
896 set serial_number = 'Z'||ln_direct_dep_act_id
897 where assignment_action_id = ln_deposit_action_id;
898
899 -- skip till next pre payment action id
900 ln_prev_pre_pymt_action_id := ln_pre_pymt_action_id;
901
902 end if;
903
904 End if; -- lv_ass_set_on = 'Y'
905
906 end loop;
907 close c_actions_zero_pay;
908 -- hr_utility.trace_off;
909
910 commit;
911 end action_creation;
912 --------------------- archive_action_creation ----------------------------
913 PROCEDURE archive_action_creation(pactid in number,
914 stperson in number,
915 endperson in number,
916 chunk in number) is
917
918 -- Bug#3438254 -- Cursor definition changed to improve performance.
919 cursor c_paid_actions
920 (cp_start_person in number,
921 cp_end_person in number,
922 cp_payroll_id in number,
923 cp_consolidation_set_id in number,
924 cp_deposit_start_date in date,
925 cp_deposit_end_date in date) is
926 select paa_pyarch.assignment_action_id,
927 paa_pyarch.assignment_id,
928 paa_pyarch.tax_unit_id
929 from pay_payroll_actions ppa_pyarch,
930 pay_assignment_actions paa_pyarch,
931 per_assignments_f paf,
932 pay_action_interlocks pai_pre
933 where ppa_pyarch.report_type = 'PY_ARCHIVER'
934 and ppa_pyarch.report_category = 'RT'
935 and ppa_pyarch.report_qualifier = 'PYCAPYAR'
936 /* and cp_deposit_end_date between ppa_pyarch.start_date
937 and ppa_pyarch.effective_date */
938 /* Bug 13934170 : Modified above date range condition as follows */
939 and ppa_pyarch.effective_date between cp_deposit_start_date
940 and cp_deposit_end_date
941 and cp_deposit_end_date between paf.effective_start_date
942 and paf.effective_end_date
943 and pycadar_pkg.get_parameter('CONSOLIDATION_SET_ID',
944 ppa_pyarch.legislative_parameters)
945 = cp_consolidation_set_id
946 and paa_pyarch.payroll_action_id = ppa_pyarch.payroll_action_id
947 -- the statement below will make sure only Pre Payment Archive
948 -- Actions are picked up
949 and substr(paa_pyarch.serial_number,1,1) not in ('V', 'B')
950 and paa_pyarch.assignment_id = paf.assignment_id
951 and ppa_pyarch.effective_date between paf.effective_start_date
952 and paf.effective_end_date
953 and pai_pre.locking_Action_id = paa_pyarch.assignment_action_id
954 and (cp_payroll_id is null
955 or
956 pycadar_pkg.get_parameter('PAYROLL_ID',
957 ppa_pyarch.legislative_parameters)
958 = cp_payroll_id
959 )
960 and paf.person_id between cp_start_person and cp_end_person
961 and pay_us_employee_payslip_web.get_doc_eit(
962 'PAYSLIP','PRINT',
963 'ASSIGNMENT',paf.assignment_id,
964 cp_deposit_end_date
965 ) = 'Y'
966 and pycadar_pkg.check_if_assignment_paid(
967 pai_pre.locked_action_id,
968 cp_deposit_start_date,
969 cp_deposit_end_date,
970 cp_consolidation_set_id) = 'Y'
971 and not exists
972 (Select 1
973 from pay_action_interlocks pai_run, --Pre > Run
974 pay_action_interlocks pai_rev, --Run > Rev
975 pay_assignment_actions paa_rev, --Rev
976 pay_payroll_actions ppa_rev --Rev
977 where pai_run.locking_action_id = pai_pre.locked_action_id
978 and pai_rev.locked_action_id = pai_run.locked_action_id
979 and paa_rev.assignment_action_id = pai_run.locking_action_id
980 and ppa_rev.payroll_action_id = paa_rev.payroll_action_id
981 and ppa_rev.action_type in ('V')
982 )
983 and exists (select 1
984 from pay_action_information pai
985 where pai.action_context_id = paa_pyarch.assignment_action_id)
986 order by paf.person_id, paf.assignment_id desc;
987
988 /*Added the following cursor to check if any non void payments exist
989 for given archiver action id for bug 16387461*/
990 cursor c_non_void_pymts (cp_action_id in number) is
991 select 1
992 from dual
993 where exists
994 (select 1
995 from PAY_EMP_NET_DIST_ACTION_INFO_V net_pay,
996 pay_assignment_actions paa_mag,
997 pay_payroll_actions ppa_mag
998 where net_pay.action_context_id = cp_action_id
999 and net_pay.pre_payment_id = paa_mag.pre_payment_id
1000 and ppa_mag.payroll_action_id = paa_mag.payroll_action_id
1001 and ppa_mag.action_type = 'M'
1002 and paa_mag.action_status = 'C'
1003 and net_pay.pre_payment_id is not null
1004 and not exists
1005 (
1006 select 1
1007 from pay_action_interlocks pai,
1008 pay_assignment_actions paa,
1009 pay_payroll_actions ppa
1010 where pai.locked_action_id = paa_mag.assignment_action_id
1011 and pai.locking_Action_id = paa.assignment_action_id
1012 and paa.payroll_action_id = ppa.payroll_action_id
1013 and ppa.action_type = 'D'
1014 )
1015 );
1016
1017
1018 lc_nv_flag VARCHAR2(1); /* Bug 16387461 sbachu*/
1019 ln_dd_action_id NUMBER;
1020 ln_deposit_action_id NUMBER;
1021
1022 ln_person_id NUMBER;
1023 ln_assignment_id NUMBER;
1024 ln_tax_unit_id NUMBER;
1025 ld_effective_date DATE;
1026
1027
1028 ln_asg_set_id NUMBER;
1029 lv_ass_set_on VARCHAR2(10);
1030
1031 -- Bug#4338254
1032 ld_deposit_start_date DATE;
1033 ld_deposit_end_date DATE;
1034 ln_payroll_id NUMBER;
1035 ln_consolidation_set_id NUMBER;
1036
1037 BEGIN
1038
1039 get_payroll_action(p_payroll_action_id => pactid
1040 ,p_deposit_start_date => ld_deposit_start_date
1041 ,p_deposit_end_date => ld_deposit_end_date
1042 ,p_assignment_set_id => ln_asg_set_id
1043 ,p_payroll_id => ln_payroll_id
1044 ,p_consolidation_set_id => ln_consolidation_set_id);
1045
1046 -- hr_utility.trace_on(null, 'ARCH_DEPADV');
1047 hr_utility.set_location('pycadar archive_action_creation',1);
1048 open c_paid_actions(stperson, endperson,
1049 ln_payroll_id,
1050 ln_consolidation_set_id,
1051 ld_deposit_start_date,
1052 ld_deposit_end_date);
1053 loop
1054 hr_utility.set_location('pycadar archive_action_creation',2);
1055
1056 lv_ass_set_on := 'N';
1057
1058 fetch c_paid_actions into ln_dd_action_id,
1059 ln_assignment_id,
1060 ln_tax_unit_id;
1061 exit WHEN c_paid_actions%NOTFOUND;
1062
1063 open c_non_void_pymts(ln_dd_action_id); /* Bug 16387461 sbachu*/
1064 fetch c_non_void_pymts into lc_nv_flag;
1065 if c_non_void_pymts%found then
1066
1067 lv_ass_set_on := hr_assignment_set.assignment_in_set(
1068 ln_asg_set_id,
1069 ln_assignment_id);
1070 hr_utility.trace('lv_ass_set_on : '||lv_ass_set_on);
1071
1072 IF lv_ass_set_on = 'Y' THEN
1073 hr_utility.trace('c_paid_actions.ln_dd_action_id is' ||to_char(ln_dd_action_id));
1074 hr_utility.trace(' ln_assignment_id is' ||to_char(ln_assignment_id));
1075 hr_utility.trace(' ln_tax_unit_id is' ||to_char(ln_tax_unit_id));
1076
1077 hr_utility.set_location('pycadar archive_action_creation',3);
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 hr_utility.trace('Inserted into paa, New Asg_act_id:'||to_char(ln_deposit_action_id));
1087 -- insert an interlock to this action.
1088 hr_nonrun_asact.insint(ln_deposit_action_id, ln_dd_action_id);
1089
1090 update pay_assignment_Actions
1091 set serial_number = ln_dd_action_id
1092 where assignment_action_id = ln_deposit_action_id;
1093
1094 END IF;
1095 end if;
1096 close c_non_void_pymts;
1097 end loop;
1098 close c_paid_actions;
1099
1100 hr_utility.set_location('pycadar archive_action_creation',4);
1101
1102 END archive_action_creation;
1103
1104 ---------------------------------- sort_action -------------------------------
1105 procedure sort_action
1106 (
1107 procname in varchar2, /* name of the select statement to use */
1108 sqlstr in out nocopy varchar2, /* string holding the sql statement */
1109 len out nocopy number /* length of the sql string */
1110 ) is
1111 begin
1112 -- go through each of the sql sub strings and see if
1113 -- they are needed.
1114 sqlstr := 'select paa.rowid
1115 from hr_organization_units hou,
1116 per_people_f ppf,
1117 per_assignments_f paf,
1118 pay_assignment_actions paa,
1119 pay_payroll_actions ppa
1120 where ppa.payroll_action_id = :pactid
1121 and paa.payroll_action_id = ppa.payroll_action_id
1122 and paa.assignment_id = paf.assignment_id
1123 and ppa.effective_date between
1124 paf.effective_start_date and paf.effective_end_date
1125 and paf.person_id = ppf.person_id
1126 and ppa.effective_date between
1127 ppf.effective_start_date and ppf.effective_end_date
1128 and paf.organization_id = hou.organization_id
1129 order by hou.name,ppf.last_name,ppf.first_name
1130 for update of paf.assignment_id';
1131 len := length(sqlstr); -- return the length of the string.
1132 end sort_action;
1133 --
1134 ------------------------------ get_parameter -------------------------------
1135 function get_parameter(name in varchar2,
1136 parameter_list varchar2) return varchar2
1137 is
1138 start_ptr number;
1139 end_ptr number;
1140 token_val pay_payroll_actions.legislative_parameters%type;
1141 par_value pay_payroll_actions.legislative_parameters%type;
1142 begin
1143 --
1144 token_val := name||'=';
1145 --
1146 start_ptr := instr(parameter_list, token_val) + length(token_val);
1147 end_ptr := instr(parameter_list, ' ', start_ptr);
1148 --
1149 /* if there is no spaces use then length of the string */
1150 if end_ptr = 0 then
1151 end_ptr := length(parameter_list)+1;
1152 end if;
1153 --
1154 /* Did we find the token */
1155 if instr(parameter_list, token_val) = 0 then
1156 par_value := NULL;
1157 else
1158 par_value := substr(parameter_list, start_ptr, end_ptr - start_ptr);
1159 end if;
1160 --
1161 return par_value;
1162 --
1163 end get_parameter;
1164 --
1165
1166 function get_labels(p_lookup_type in VARCHAR2,
1167 p_lookup_code in VARCHAR2)
1168 return VARCHAR2 is
1169 cursor csr_label_meaning is
1170 select meaning
1171 from hr_lookups
1172 where lookup_type = p_lookup_type
1173 and lookup_code = p_lookup_code;
1174
1175 l_label_meaning varchar2(80);
1176 begin
1177 open csr_label_meaning;
1178
1179 fetch csr_label_meaning into l_label_meaning;
1180 if csr_label_meaning%NOTFOUND then
1181 l_label_meaning := NULL;
1182 end if;
1183 close csr_label_meaning;
1184
1185 return l_label_meaning;
1186 end get_labels;
1187
1188 --
1189 function get_labels(p_lookup_type in VARCHAR2,
1190 p_lookup_code in VARCHAR2,
1191 p_person_language in varchar2)
1192 return VARCHAR2 is
1193 cursor csr_label_meaning is
1194 select 1 ord, meaning
1195 from fnd_lookup_values
1196 where lookup_type = p_lookup_type
1197 and lookup_code = p_lookup_code
1198 and ( ( p_person_language is null and language = 'US' ) or
1199 ( p_person_language is not null and language = p_person_language ) )
1200 union all
1201 select 2 ord, meaning
1202 from fnd_lookup_values
1203 where lookup_type = p_lookup_type
1204 and lookup_code = p_lookup_code
1205 and ( language = 'US' and p_person_language is not null
1206 and language <> p_person_language )
1207 order by 1;
1208
1209 l_order number;
1210 l_label_meaning varchar2(80);
1211 begin
1212 open csr_label_meaning;
1213
1214 fetch csr_label_meaning into l_order, l_label_meaning;
1215 if csr_label_meaning%NOTFOUND then
1216 l_label_meaning := NULL;
1217 end if;
1218 close csr_label_meaning;
1219
1220 return l_label_meaning;
1221 end get_labels;
1222
1223 /* Bug 13773865: CANADIAN DEPOSIT ADVICE (XML) ENHANCEMENT
1224 Added following 2 sub program units.
1225 qualifying_proc
1226 check_if_qualified_for_CA
1227 */
1228
1229 PROCEDURE qualifying_proc(p_assignment_id IN NUMBER
1230 ,p_qualifier OUT NOCOPY VARCHAR2 ) IS
1231 --
1232 l_actid NUMBER;
1233 l_rep_group pay_report_groups.report_group_name%TYPE;
1234 l_rep_category pay_report_categories.category_name%TYPE;
1235 l_effective_date DATE;
1236 l_business_group_id NUMBER;
1237 l_assignment_set_id NUMBER;
1238 l_assignment_id NUMBER;
1239 l_inc_exc VARCHAR2(1);
1240 l_asg_inc_exc VARCHAR2(1);
1241 --
1242 l_payroll_id NUMBER;
1243 l_consolidation_set_id NUMBER;
1244 l_start_date VARCHAR2(20);
1245 l_end_date VARCHAR2(20);
1246 l_legislation_code VARCHAR2(10);
1247 l_start_dt DATE;
1248 l_end_dt DATE;
1249 l_qualifier VARCHAR2(1);
1250 --
1251 ln_curr_payroll_act_id NUMBER;
1252 k NUMBER;
1253 ln_assignment_id NUMBER;
1254 ln_action_ctx_id NUMBER;
1255 --
1256 sql_cur NUMBER;
1257 l_rows NUMBER;
1258 statem VARCHAR2(256);
1259 --
1260 CURSOR csr_asg(c_payroll_id NUMBER
1261 ,c_consolidation_set_id NUMBER
1262 ,c_start_date DATE
1263 ,c_end_date DATE
1264 ,c_pa_token VARCHAR2
1265 ,c_cs_token VARCHAR2
1266 ,c_legislation_code VARCHAR2) IS
1267 SELECT /* 'Y' */
1268 distinct paa.assignment_id
1269 ,pai.action_context_id
1270 FROM pay_assignment_actions paa
1271 ,pay_payroll_actions ppa
1272 ,hr_lookups hrl
1273 ,pay_action_information pai
1274 ,per_time_periods ptp
1275 WHERE /* paa.assignment_id = c_assignment_id */
1276 ppa.effective_Date BETWEEN c_start_date
1277 AND c_end_date
1278 AND ppa.report_type = hrl.meaning
1279 AND hrl.lookup_type = 'PAYSLIP_REPORT_TYPES'
1280 AND hrl.lookup_code = c_legislation_code
1281 AND NVL(c_payroll_id,NVL(pay_payslip_report.get_parameter(ppa.legislative_parameters,c_pa_token),-1))
1282 = NVL(pay_payslip_report.get_parameter(ppa.legislative_parameters,c_pa_token),-1)
1283 AND c_consolidation_set_id = pay_payslip_report.get_parameter(ppa.legislative_parameters,c_cs_token)
1284
1285 --
1286 --
1287 AND ppa.payroll_action_id = paa.payroll_action_id
1288 -- AND paa.source_action_id IS NULL --RLN P1 8941027
1289 --
1290 --
1291 AND pai.assignment_id = paa.assignment_id
1292 AND pai.action_context_type = 'AAP'
1293 AND pai.action_information_category = 'EMPLOYEE DETAILS'
1294 AND pai.action_context_id = paa.assignment_action_id
1295 AND ptp.time_period_id = pai.ACTION_INFORMATION16;
1296
1297 /* AND check_if_qualified_for_CA(pai.action_context_id
1298 ,paa.assignment_id
1299 ,c_start_date
1300 ,c_end_date
1301 ,c_consolidation_set_id) = 'Y'; */
1302
1303 --
1304 CURSOR csr_inc_asg(c_payroll_id NUMBER
1305 ,c_consolidation_set_id NUMBER
1306 ,c_start_date DATE
1307 ,c_end_date DATE
1308 ,c_pa_token VARCHAR2
1309 ,c_cs_token VARCHAR2
1310 ,c_legislation_code VARCHAR2
1311 ,c_assignment_set_id NUMBER ) IS
1312 SELECT /* 'Y' */
1313 distinct paa.assignment_id
1314 ,pai.action_context_id
1315 FROM pay_assignment_actions paa
1316 ,pay_payroll_actions ppa
1317 ,hr_lookups hrl
1318 ,hr_assignment_set_amendments hasa
1319 ,pay_action_information pai
1320 ,per_time_periods ptp
1321 WHERE ppa.effective_Date BETWEEN c_start_date
1322 AND c_end_date
1323 AND ppa.report_type = hrl.meaning
1324 AND hrl.lookup_type = 'PAYSLIP_REPORT_TYPES'
1325 AND hrl.lookup_code = c_legislation_code
1326 AND NVL(c_payroll_id,NVL(pay_payslip_report.get_parameter(ppa.legislative_parameters,c_pa_token),-1))
1327 = NVL(pay_payslip_report.get_parameter(ppa.legislative_parameters,c_pa_token),-1)
1328 AND c_consolidation_set_id = pay_payslip_report.get_parameter(ppa.legislative_parameters,c_cs_token)
1329 AND ppa.payroll_action_id = paa.payroll_action_id
1330 --AND paa.source_action_id IS NULL --RLN P1 894102
1331 AND paa.assignment_id = hasa.assignment_id
1332 AND hasa.assignment_set_id = c_assignment_set_id
1333 AND hasa.include_or_exclude = 'I'
1334 AND pai.assignment_id = paa.assignment_id
1335 AND pai.action_context_type = 'AAP'
1336 AND pai.action_information_category = 'EMPLOYEE DETAILS'
1337 AND pai.action_context_id = paa.assignment_action_id
1338 AND ptp.time_period_id = pai.ACTION_INFORMATION16;
1339
1340 /* AND check_if_qualified_for_CA(pai.action_context_id
1341 ,paa.assignment_id
1342 ,c_start_date
1343 ,c_end_date
1344 ,c_consolidation_set_id) = 'Y'; */
1345
1346 --
1347 -- The Assignment Set Logic is handled only for either Include or Exclude
1348 -- and not for both. This doesn't handle the assignment_set_criteria.
1349 --
1350 CURSOR csr_inc_exc(c_assignment_set_id NUMBER
1351 ,c_assignment_id NUMBER) IS
1352 SELECT include_or_exclude
1353 FROM hr_assignment_set_amendments
1354 WHERE assignment_set_id = c_assignment_set_id
1355 AND assignment_id = nvl(c_assignment_id,assignment_id);
1356 --
1357 --
1358 --
1359
1360 BEGIN
1361 hr_utility.trace('###### IN Qualifying Proc');
1362 --
1363 l_actid := pay_proc_environment_pkg.get_pactid;
1364 --
1365 ln_curr_payroll_act_id := l_actid;
1366 hr_utility.trace('In QualProc l_actid := ' || l_actid);
1367 hr_utility.trace('p_assignment_id := ' || p_assignment_id);
1368
1369 IF pycadar_pkg.g_payroll_act_id <> ln_curr_payroll_act_id THEN
1370 pycadar_pkg.g_payroll_act_id := ln_curr_payroll_act_id;
1371
1372 pay_payslip_report.get_all_parameters(l_actid
1373 ,l_payroll_id
1374 ,l_consolidation_set_id
1375 ,l_start_date
1376 ,l_end_date
1377 ,l_rep_group
1378 ,l_rep_category
1379 ,l_assignment_set_id
1380 ,l_assignment_id
1381 ,l_effective_date
1382 ,l_business_group_id
1383 ,l_legislation_code);
1384
1385 -- hr_utility.trace('l_payroll_id :='||l_payroll_id);
1386 -- hr_utility.trace('l_consolidation_set_id :='||l_consolidation_set_id);
1387 -- hr_utility.trace('l_start_date :='||l_start_date);
1388 -- hr_utility.trace('l_end_date :='||l_end_date);
1389 -- hr_utility.trace('l_rep_group :='||l_rep_group);
1390 -- hr_utility.trace('l_rep_category :='||l_rep_category);
1391 -- hr_utility.trace('l_assignment_set_id :='||l_assignment_set_id);
1392 -- hr_utility.trace('l_assignment_id :='||l_assignment_id);
1393 -- hr_utility.trace('l_effective_date :='||l_effective_date);
1394 -- hr_utility.trace('l_business_group_id :='||l_business_group_id);
1395 -- hr_utility.trace('l_legislation_code :='||l_legislation_code);
1396
1397 --
1398 l_start_dt := TO_DATE(l_start_date,'YYYY/MM/DD');
1399 l_end_dt := TO_DATE(l_end_date,'YYYY/MM/DD');
1400 --
1401 -- Fetching legislative prameters for the very first time
1402 -- And caching them into global variables.
1403
1404 pycadar_pkg.g_payroll_id := l_payroll_id;
1405 pycadar_pkg.g_consolidation_set_id := l_consolidation_set_id;
1406 pycadar_pkg.g_start_dt := l_start_dt;
1407 pycadar_pkg.g_end_dt := l_end_dt;
1408 pycadar_pkg.g_rep_group := l_rep_group;
1409 pycadar_pkg.g_rep_category := l_rep_category;
1410 pycadar_pkg.g_assignment_set_id := l_assignment_set_id;
1411 pycadar_pkg.g_assignment_id := l_assignment_id;
1412 pycadar_pkg.g_effective_date := l_effective_date;
1413 pycadar_pkg.g_business_group_id := l_business_group_id;
1414 pycadar_pkg.g_legislation_code := l_legislation_code;
1415
1416 --
1417 DECLARE
1418 BEGIN
1419 statem := 'BEGIN pay_'||l_legislation_code||'_rules.get_token_names(:p_pa_token, :p_cs_token); END;';
1420 --hr_utility.trace(statem);
1421 sql_cur := dbms_sql.open_cursor;
1422 dbms_sql.parse(sql_cur
1423 ,statem
1424 ,dbms_sql.v7);
1425 dbms_sql.bind_variable(sql_cur, 'p_pa_token', pay_payslip_report.g_pa_token, 50);
1426 dbms_sql.bind_variable(sql_cur, 'p_cs_token', pay_payslip_report.g_cs_token, 50);
1427 l_rows := dbms_sql.execute(sql_cur);
1428 dbms_sql.variable_value(sql_cur, 'p_pa_token', pay_payslip_report.g_pa_token);
1429 dbms_sql.variable_value(sql_cur, 'p_cs_token', pay_payslip_report.g_cs_token);
1430 dbms_sql.close_cursor(sql_cur);
1431 Exception
1432 WHEN OTHERS THEN
1433 pay_payslip_report.g_pa_token := NVL(pay_payslip_report.g_pa_token,'PAYROLL_ID');
1434 pay_payslip_report.g_cs_token := NVL(pay_payslip_report.g_cs_token,'CONSOLIDATION_SET_ID');
1435 --
1436 IF dbms_sql.IS_OPEN(sql_cur) THEN
1437 dbms_sql.close_cursor(sql_cur);
1438 END IF;
1439 END;
1440 --
1441 --
1442 --hr_utility.trace('pay_payslip_report.g_pa_token :='||pay_payslip_report.g_pa_token);
1443 --hr_utility.trace('pay_payslip_report.g_cs_token :='||pay_payslip_report.g_cs_token);
1444
1445 IF pycadar_pkg.g_assignment_set_id IS NULL THEN
1446 OPEN csr_asg(pycadar_pkg.g_payroll_id
1447 ,pycadar_pkg.g_consolidation_set_id
1448 ,pycadar_pkg.g_start_dt
1449 ,pycadar_pkg.g_end_dt
1450 ,pay_payslip_report.g_pa_token
1451 ,pay_payslip_report.g_cs_token
1452 ,pycadar_pkg.g_legislation_code);
1453 LOOP
1454
1455 ln_assignment_id := -1;
1456 ln_action_ctx_id := -1;
1457
1458 FETCH csr_asg INTO ln_assignment_id, ln_action_ctx_id;
1459
1460
1461 IF csr_asg%NOTFOUND THEN
1462 hr_utility.trace('csr_asg didnot return any row');
1463 EXIT;
1464 ELSE
1465 IF check_if_qualified_for_CA(ln_action_ctx_id
1466 ,ln_assignment_id
1467 ,pycadar_pkg.g_start_dt
1468 ,pycadar_pkg.g_end_dt
1469 ,pycadar_pkg.g_consolidation_set_id) = 'Y' THEN
1470
1471 g_tmp_tbl(ln_assignment_id) := ln_assignment_id;
1472 hr_utility.trace('g_tmp_tbl(' || ln_assignment_id || ') := ' || ln_assignment_id);
1473
1474 END IF;
1475 END IF;
1476
1477 END LOOP;
1478 CLOSE csr_asg;
1479 --
1480 ELSE
1481 OPEN csr_inc_asg(pycadar_pkg.g_payroll_id
1482 ,pycadar_pkg.g_consolidation_set_id
1483 ,pycadar_pkg.g_start_dt
1484 ,pycadar_pkg.g_end_dt
1485 ,pay_payslip_report.g_pa_token
1486 ,pay_payslip_report.g_cs_token
1487 ,pycadar_pkg.g_legislation_code
1488 ,pycadar_pkg.g_assignment_set_id);
1489 LOOP
1490
1491 ln_assignment_id := -1;
1492 ln_action_ctx_id := -1;
1493
1494 FETCH csr_inc_asg INTO ln_assignment_id, ln_action_ctx_id;
1495
1496 IF csr_inc_asg%NOTFOUND THEN
1497 EXIT;
1498 ELSE
1499 IF check_if_qualified_for_CA(ln_action_ctx_id
1500 ,ln_assignment_id
1501 ,pycadar_pkg.g_start_dt
1502 ,pycadar_pkg.g_end_dt
1503 ,pycadar_pkg.g_consolidation_set_id) = 'Y' THEN
1504
1505 g_tmp_tbl(ln_assignment_id) := ln_assignment_id;
1506 hr_utility.trace('g_tmp_tbl(' || ln_assignment_id || ') := ' || ln_assignment_id);
1507 END IF;
1508 END IF;
1509
1510 END LOOP;
1511 CLOSE csr_inc_asg;
1512
1513 END IF;
1514 END IF;
1515
1516 l_qualifier := 'N';
1517 k := 1;
1518
1519 hr_utility.trace('g_tmp_tbl.COUNT := ' || g_tmp_tbl.COUNT);
1520
1521 IF g_tmp_tbl.EXISTS(p_assignment_id) THEN
1522 l_qualifier := 'Y';
1523 END IF;
1524
1525 hr_utility.trace('B4 Return l_qualifier := ' || l_qualifier);
1526
1527
1528 IF l_qualifier = 'Y' THEN
1529 p_qualifier := 'Y' ;
1530 END IF;
1531
1532 END qualifying_proc;
1533
1534
1535 FUNCTION check_if_qualified_for_CA(p_archive_action_id IN NUMBER
1536 ,p_assignment_id IN NUMBER
1537 ,p_deposit_start_date IN DATE
1538 ,p_deposit_end_date IN DATE
1539 ,p_consolidation_set_id IN NUMBER)
1540 RETURN VARCHAR2
1541 IS
1542 /****************************************************************
1543 ** If archiver is locking the pre-payment assignment_action_id,
1544 ** we get it from interlocks.
1545 ****************************************************************/
1546 cursor c_prepay_arch_action(cp_assignment_action_id in number) is
1547 select paa.assignment_action_id
1548 from pay_action_interlocks paci,
1549 pay_assignment_actions paa,
1550 pay_payroll_actions ppa
1551 where paci.locking_action_id = cp_assignment_action_id
1552 and paa.assignment_action_id = paci.locked_action_id
1553 and ppa.payroll_action_id = paa.payroll_action_id
1554 and ppa.action_type in ('P', 'U');
1555
1556 /****************************************************************
1557 ** If archiver is locking the run assignment_action_id, we get
1558 ** the corresponding run assignment_action_id and then get
1559 ** the pre-payment assignemnt_action_id.
1560 ** This cursor is only required when there are child action which
1561 ** means there is a seperate check.
1562 * ***************************************************************/
1563 cursor c_prepay_run_arch_action(cp_assignment_action_id in number) is
1564 select paa_pre.assignment_action_id
1565 from pay_action_interlocks pai_run,
1566 pay_action_interlocks pai_pre,
1567 pay_assignment_actions paa_pre,
1568 pay_payroll_actions ppa_pre
1569 where pai_run.locking_action_id = cp_assignment_action_id
1570 and pai_pre.locked_action_id = pai_run.locked_action_id
1571 and paa_pre.assignment_Action_id = pai_pre.locking_action_id
1572 and ppa_pre.payroll_action_id = paa_pre.payroll_action_id
1573 and ppa_pre.action_type in ('P', 'U');
1574
1575 ln_prepay_action_id NUMBER;
1576 lv_rev_run_exists VARCHAR2(1);
1577 BEGIN
1578 --
1579 --
1580 hr_utility.trace('Entering check_if_qualified_for_CA');
1581
1582 OPEN c_prepay_arch_action(p_archive_action_id);
1583 FETCH c_prepay_arch_action INTO ln_prepay_action_id;
1584 IF c_prepay_arch_action%notfound THEN
1585 OPEN c_prepay_run_arch_action(p_archive_action_id);
1586 FETCH c_prepay_run_arch_action INTO ln_prepay_action_id;
1587 IF c_prepay_run_arch_action%notfound THEN
1588 RETURN('N');
1589 END IF;
1590 CLOSE c_prepay_run_arch_action;
1591 END IF;
1592 CLOSE c_prepay_arch_action;
1593 --
1594 --
1595 hr_utility.trace('ln_prepay_action_id :='||ln_prepay_action_id);
1596
1597 IF pay_us_employee_payslip_web.get_doc_eit(
1598 'PAYSLIP'
1599 ,'PRINT'
1600 ,'ASSIGNMENT'
1601 ,p_assignment_id
1602 ,p_deposit_end_date) = 'Y'
1603 AND pycadar_pkg.check_if_assignment_paid(
1604 ln_prepay_action_id
1605 ,p_deposit_start_date
1606 ,p_deposit_end_date
1607 ,p_consolidation_set_id) = 'Y' --Bug 3512116
1608 THEN
1609
1610 lv_rev_run_exists := NULL;
1611
1612 BEGIN
1613 SELECT '1'
1614 INTO lv_rev_run_exists
1615 FROM dual
1616 where exists
1617 (Select /*+ ORDERED */ 1
1618 from pay_action_interlocks pai_run, --Pre > Run
1619 pay_action_interlocks pai_rev, --Run > Rev
1620 pay_assignment_actions paa_rev, --Rev
1621 pay_payroll_actions ppa_rev --Rev
1622 where pai_run.locking_action_id = ln_prepay_action_id
1623 and pai_rev.locked_action_id = pai_run.locked_action_id
1624 and paa_rev.assignment_action_id = pai_run.locking_action_id
1625 and ppa_rev.payroll_action_id = paa_rev.payroll_action_id
1626 and ppa_rev.action_type in ('V')
1627 );
1628 EXCEPTION
1629 WHEN OTHERS THEN
1630 lv_rev_run_exists := NULL;
1631 END;
1632
1633 IF lv_rev_run_exists = '1' then
1634 RETURN 'N';
1635 ELSE
1636 RETURN 'Y';
1637 END IF;
1638 ELSE
1639 RETURN 'N';
1640 END IF;
1641 END check_if_qualified_for_CA;
1642
1643 end pycadar_pkg;
[Home] [Help]