[Home] [Help]
PACKAGE BODY: APPS.PAY_CA_EOY_T4A_CANCEL_PKG
Source
1 PACKAGE BODY pay_ca_eoy_t4a_cancel_pkg AS
2 /* $Header: pycat4aclcr.pkb 120.4.12020000.2 2012/12/31 11:59:28 rgottipa ship $ */
3 /*
4 ******************************************************************
5 * *
6 * Copyright (C) 2010 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_ca_eoy_t4a_cancel_pkg
21
22 Description : This Package is used by T4 Cancel PDF Report.
23
24 Change List
25 -----------
26 Date Name Vers Bug No Description
27 ----------- ---------- ------ ------- --------------------------
28 03-Jan-2011 nkjaladi 115.0 10359119 Created.
29 06-Jan-2011 nkjaladi 115.1 10359119 Modified procedure action_creation
30 to avoid error message showing
31 if atleast one record is fetched
32 for a person.
33 07-Jan-2011 nkjaladi 115.2 10359119 Modified procedure action_creation
34 to exit the loop if archiver is
35 rolledback or negative flag is
36 encountered
37 07-Jan-2011 nkjaladi 115.3 10359119 Modified cursor c_selected_asg_set
38 in procedure action_creation
39 to remove the check for person_id
40 29-Aug-2011 sneelapa 115.4 10399514 Introduced new CURSOR
41 c_selected_asg_set_range it will
42 be called in place of
43 c_selected_asg_set CURSOR,
44 if RANGE_PERSON_ID is enabled.
45 31-Dec-2012 rgottipa 115.5 15886428 Done changes to support print
46 terminate employees and Self Service
47 'paper' option.
48 *****************************************************************************/
49
50 gv_package VARCHAR2(100) := 'pay_ca_eoy_t4a_cancel_pkg';
51 gv_procedure_name VARCHAR2(100);
52
53 /*****************************************************************************
54 Name : get_payroll_action_info
55 Purpose : This returns the Payroll Action level
56 information for T4 Cancel PDF.
57 Arguments : p_payroll_action_id - Payroll_Action_id of archiver
58 p_start_date - Start date of T4 Amendment PAPER
59 p_end_date - End date of T4 Amendment PAPER
60 p_business_group_id - Business Group ID
61 *****************************************************************************/
62 PROCEDURE get_payroll_action_info(p_payroll_action_id in number
63 ,p_end_date out nocopy date
64 ,p_start_date out nocopy date
65 ,p_business_group_id out nocopy number
66 ,p_tax_unit_id out nocopy number
67 ,p_person_id out nocopy number
68 ,p_asg_set out nocopy number
69 ,p_print out nocopy varchar2
70 ,p_report_type out nocopy varchar2
71 )
72 IS
73 cursor c_payroll_Action_info (cp_payroll_action_id in number) is
74 select to_number(pay_ca_eoy_t4a_cancel_pkg.get_parameter(
75 'GRE_ID',ppa.legislative_parameters)),
76 to_number(pay_ca_eoy_t4a_cancel_pkg.get_parameter(
77 'PER_ID',ppa.legislative_parameters)),
78 to_number(pay_ca_eoy_t4a_cancel_pkg.get_parameter(
79 'ASG_SET_ID',ppa.legislative_parameters)),
80 pay_ca_eoy_t4a_cancel_pkg.get_parameter(
81 'MODE',ppa.legislative_parameters),
82 effective_date,
83 start_date,
84 business_group_id,
85 report_type
86 from pay_payroll_actions ppa
87 where ppa.payroll_action_id = cp_payroll_action_id;
88
89 ld_end_date DATE;
90 ld_start_date DATE;
91 ln_business_group_id NUMBER;
92 ln_tax_unit_id NUMBER := 0;
93 ln_person_id NUMBER := 0;
94 ln_asg_set NUMBER := 0;
95 lv_print varchar2(10);
96 lv_report_type varchar2(50);
97
98 BEGIN
99 hr_utility.trace('Entered get_payroll_action_info');
100 open c_payroll_action_info(p_payroll_action_id);
101 fetch c_payroll_action_info into ln_tax_unit_id,
102 ln_person_id,
103 ln_asg_set,
104 lv_print,
105 ld_end_date,
106 ld_start_date,
107 ln_business_group_id,
108 lv_report_type;
109 close c_payroll_action_info;
110
111 hr_utility.trace('ld_end_date = ' || to_char(ld_end_date));
112 hr_utility.trace('ld_start_date = ' || to_char(ld_start_date));
113 hr_utility.trace('ln_tax_unit_id = '|| to_char(ln_tax_unit_id));
114 hr_utility.trace('ln_person_id = ' || to_char(ln_person_id));
115 hr_utility.trace('ln_asg_set = ' || to_char(ln_asg_set));
116
117 p_end_date := ld_end_date;
118 p_start_date := ld_start_date;
119 p_business_group_id := ln_business_group_id;
120 p_tax_unit_id := ln_tax_unit_id;
121 p_person_id := ln_person_id;
122 p_asg_set := ln_asg_set;
123 p_print := lv_print;
124 p_report_type := lv_report_type;
125
126 hr_utility.trace('Leaving get_payroll_action_info');
127
128 EXCEPTION
129 when others then
130 hr_utility.trace('Error in ' || gv_procedure_name ||
131 to_char(sqlcode) || '-' || sqlerrm);
132 raise hr_utility.hr_error;
133
134 END get_payroll_action_info;
135
136
137 -- Added for Bug# 10399514
138 -- Used when RANGE_PERSON_ID functionality is available
139 /*****************************************************************************
140 Name : get_payroll_action_info
141 Purpose : This returns the Payroll Action level
142 information for T4 Cancel PDF.
143 Arguments : p_payroll_action_id - Payroll_Action_id of archiver
144 p_start_date - Start date of T4 Amendment PAPER
145 p_end_date - End date of T4 Amendment PAPER
146 p_business_group_id - Business Group ID
147 *****************************************************************************/
148 PROCEDURE get_payroll_action_info(p_payroll_action_id in number
149 ,p_end_date out nocopy date
150 ,p_start_date out nocopy date
151 ,p_business_group_id out nocopy number
152 ,p_tax_unit_id out nocopy number
153 ,p_person_id out nocopy number
154 ,p_asg_set out nocopy number
155 ,p_print out nocopy varchar2
156 ,p_report_type out nocopy varchar2
157 ,p_state out nocopy varchar2
158 ,p_report_cat out nocopy varchar2
159 ,p_print_term out nocopy varchar2
160 )
161 IS
162 cursor c_payroll_Action_info (cp_payroll_action_id in number) is
163 select to_number(pay_ca_eoy_t4a_cancel_pkg.get_parameter(
164 'GRE_ID',ppa.legislative_parameters)),
165 to_number(pay_ca_eoy_t4a_cancel_pkg.get_parameter(
166 'PER_ID',ppa.legislative_parameters)),
167 to_number(pay_ca_eoy_t4a_cancel_pkg.get_parameter(
168 'ASG_SET_ID',ppa.legislative_parameters)),
169 pay_ca_eoy_t4a_cancel_pkg.get_parameter(
170 'MODE',ppa.legislative_parameters),
171 effective_date,
172 start_date,
173 business_group_id,
174 report_type,
175 report_qualifier,
176 report_category,
177 pay_ca_eoy_t4a_cancel_pkg.get_parameter(
178 'PRINT_TERM',ppa.legislative_parameters)
179 from pay_payroll_actions ppa
180 where ppa.payroll_action_id = cp_payroll_action_id;
181
182 ld_end_date DATE;
183 ld_start_date DATE;
184 ln_business_group_id NUMBER;
185 ln_tax_unit_id NUMBER := 0;
186 ln_person_id NUMBER := 0;
187 ln_asg_set NUMBER := 0;
188 lv_print varchar2(10);
189 lv_report_type varchar2(50);
190 lv_report_qualifier pay_payroll_actions.report_qualifier%type;
191 lv_report_cat pay_payroll_actions.report_category%type;
192 l_print_term varchar2(1) := 'N';
193
194 BEGIN
195 hr_utility.trace('Entered get_payroll_action_info');
196 open c_payroll_action_info(p_payroll_action_id);
197 fetch c_payroll_action_info into ln_tax_unit_id,
198 ln_person_id,
199 ln_asg_set,
200 lv_print,
201 ld_end_date,
202 ld_start_date,
203 ln_business_group_id,
204 lv_report_type,
205 lv_report_qualifier,
206 lv_report_cat,
207 l_print_term;
208 close c_payroll_action_info;
209
210 hr_utility.trace('ld_end_date = ' || to_char(ld_end_date));
211 hr_utility.trace('ld_start_date = ' || to_char(ld_start_date));
212 hr_utility.trace('ln_tax_unit_id = ' || to_char(ln_tax_unit_id));
213 hr_utility.trace('ln_person_id = ' || to_char(ln_person_id));
214 hr_utility.trace('ln_asg_set = ' || to_char(ln_asg_set));
215 hr_utility.trace('lv_report_type = ' || lv_report_type);
216 hr_utility.trace('lv_report_qualifier = '|| lv_report_qualifier);
217 hr_utility.trace('lv_report_cat = ' || lv_report_cat);
218 hr_utility.trace('l_print_term = ' || l_print_term);
219
220 p_end_date := ld_end_date;
221 p_start_date := ld_start_date;
222 p_business_group_id := ln_business_group_id;
223 p_tax_unit_id := ln_tax_unit_id;
224 p_person_id := ln_person_id;
225 p_asg_set := ln_asg_set;
226 p_print := lv_print;
227 p_report_type := lv_report_type;
228 p_state := lv_report_qualifier;
229 p_report_cat := lv_report_cat;
230 p_print_term := l_print_term;
231
232 hr_utility.trace('Leaving get_payroll_action_info');
233
234 EXCEPTION
235 when others then
236 hr_utility.trace('Error in ' || gv_procedure_name ||
237 to_char(sqlcode) || '-' || sqlerrm);
238 raise hr_utility.hr_error;
239
240 END get_payroll_action_info;
241
242
243 /******************************************************************
244 Name : range_cursor
245 Purpose : This returns the select statement that is
246 used to created the range rows for the
247 T4A Cancel PDF.
248 Arguments :
249 Notes : Calls procedure - get_payroll_action_info
250 ******************************************************************/
251 PROCEDURE range_cursor(
252 p_payroll_action_id in number
253 ,p_sqlstr out nocopy varchar2)
254 IS
255
256 ld_end_date DATE;
257 ld_start_date DATE;
258 ln_business_group_id NUMBER;
259 ln_tax_unit_id NUMBER;
260 ln_person_id NUMBER := 0;
261 ln_asg_set NUMBER := 0;
262 lv_sql_string VARCHAR2(32000);
263 ln_eoy_pactid number;
264 lv_print varchar2(10):=null;
265 lv_error_mesg varchar2(100);
266 ln_agent_tax_unit_id pay_assignment_actions.tax_unit_id%type;
267 ln_year number;
268 lv_report_type varchar2(50);
269
270 BEGIN
271 hr_utility.trace('Entered range_cursor');
272 hr_utility.trace('p_payroll_action_id = ' ||
273 to_char(p_payroll_action_id));
274
275 get_payroll_action_info(p_payroll_action_id => p_payroll_action_id
276 ,p_start_date => ld_start_date
277 ,p_end_date => ld_end_date
278 ,p_business_group_id => ln_business_group_id
279 ,p_tax_unit_id => ln_tax_unit_id
280 ,p_person_id => ln_person_id
281 ,p_asg_set => ln_asg_set
282 ,p_print => lv_print
283 ,p_report_type => lv_report_type);
284
285 ln_year := to_number(to_char(ld_end_date,'YYYY'));
286
287 hr_utility.trace('Checking for Preprocess Agent GRE setup');
288
289 if ln_person_id is not null then
290
291 lv_sql_string :=
292 'select distinct asg.person_id person_id
293 from per_assignments_f asg
294 where person_id = ' || ln_person_id ||
295 ' and :payroll_action_id > 0';
296
297 hr_utility.trace('Range for person_id not null');
298
299 elsif ln_asg_set is not null then
300
301 lv_sql_string :=
302 'select distinct paf.person_id
303 from hr_assignment_set_amendments asgset,
304 per_assignments_f paf
305 where assignment_set_id = ' || ln_asg_set || '
306 and asgset.assignment_id = paf.assignment_id
307 and asgset.include_or_exclude = ''I''
308 and :payroll_action_id > 0
309 order by paf.person_id';
310
311 hr_utility.trace('Range for asg_set not null');
312
313 elsif ln_tax_unit_id is not NULL then
314
315 lv_sql_string :=
316 'select distinct paf.person_id
317 from pay_payroll_actions ppa_rep,
318 pay_assignment_actions paa_rep,
319 per_assignments_f paf,
320 pay_payroll_actions ppa
321 where paa_rep.assignment_id = paf.assignment_id
322 and ppa.payroll_action_id = :payroll_action_id
323 and ppa_rep.business_group_id = ppa.business_group_id
324 and ppa_rep.effective_date = ppa.effective_date
325 and ppa_rep.report_type in (''PAYCAT4APDF'',''PAYCAT4AAMPDF'')
326 and paa_rep.tax_unit_id = '|| ln_tax_unit_id ||'
327 and paa_rep.action_status = ''C''
328 and ppa_rep.payroll_action_id = paa_rep.payroll_action_id
329 order by paf.person_id ';
330
331 else
332
333 lv_sql_string :=
334 'select distinct paf.person_id
335 from pay_payroll_actions ppa_rep,
336 pay_assignment_actions paa_rep,
337 per_assignments_f paf,
338 pay_payroll_actions ppa
339 where paa_rep.assignment_id = paf.assignment_id
340 and ppa.payroll_action_id = :payroll_action_id
341 and ppa_rep.business_group_id = ppa.business_group_id
342 and ppa_rep.effective_date = ppa.effective_date
343 and ppa_rep.report_type in (''PAYCAT4APDF'',''PAYCAT4AAMPDF'')
344 and paa_rep.action_status = ''C''
345 and ppa_rep.payroll_action_id = paa_rep.payroll_action_id
346 order by paf.person_id ';
347
348 end if;
349
350 p_sqlstr := lv_sql_string;
351 hr_utility.trace('p_sqlstr = ' ||p_sqlstr);
352
353 hr_utility.trace('Leaving range_cursor');
354
355 END range_cursor;
356
357
358 /************************************************************
359 Name : action_creation
360 Purpose : This creates the assignment actions for
361 a specific chunk of people to be archived
362 by the T4A Cancel PDF Report process.
363 Arguments :
364 Notes : Calls procedure - get_payroll_action_info
365 ************************************************************/
366
367 PROCEDURE action_creation(
368 p_payroll_action_id in number
369 ,p_start_person_id in number
370 ,p_end_person_id in number
371 ,p_chunk in number)
372
373 IS
374
375 ln_assignment_id NUMBER := 0;
376 ln_tax_unit_id NUMBER := 0;
377 ld_effective_date DATE;
378 ln_asg_action_id NUMBER := 0;
379 ln_primary_assignment_id NUMBER := 0;
380 ln_yepp_aaid NUMBER := 0;
381 ln_payroll_action_id NUMBER := 0;
382 ln_t4acancel_asg_action NUMBER := 0;
383 lv_year VARCHAR2(4);
384
385 ld_end_date DATE;
386 ld_start_date DATE;
387 ln_business_group_id NUMBER;
388 ln_person_id NUMBER := 0 ;
389 ln_set_person_id NUMBER := 0 ;
390 ln_asg_set NUMBER := 0 ;
391 lv_print varchar2(10);
392
393 lv_report_type pay_payroll_actions.report_type%TYPE ;
394
395 -- Variables declared for bug 10399514
396 l_person_on boolean ;
397 l_report_cat pay_payroll_actions.report_category%type;
398 l_state pay_payroll_actions.report_qualifier%type;
399 l_report_format pay_report_format_mappings_f.report_format%type;
400 -- Variables declared for bug 10399514
401
402 ln_rep_act_to_lock pay_assignment_actions.assignment_action_id%TYPE;
403 ln_arch_act_to_lock pay_assignment_actions.assignment_action_id%TYPE;
404 ln_second_last_arch_action pay_assignment_actions.assignment_action_id%TYPE;
405 ln_prev_yepp_lock_action pay_assignment_actions.assignment_action_id%TYPE;
406
407
408 lv_serial_number VARCHAR2(30);
409 lv_employee_number per_people_f.employee_number%type;
410 lv_message varchar2(240):= null;
411 lv_full_name per_people_f.full_name%type;
412 lv_record_name varchar2(100);
413 lv_prev_report_type pay_payroll_actions.report_type%TYPE;
414 ln_prev_lock_action pay_assignment_actions.assignment_action_id%TYPE;
415 ln_prev_w2c_action_id pay_assignment_actions.assignment_action_id%TYPE;
416 ln_serial_number pay_assignment_actions.serial_number%TYPE;
417 ln_gre_id NUMBER;
418 ln_gre_parameter NUMBER;
419 lv_ppr_report_type varchar2(50);
420
421 l_print_term varchar2(1);
422
423 CURSOR c_selected_asg_set(cp_start_person in number
424 ,cp_end_person in number
425 ,cp_asg_set in number
426 ,cp_effective_date in date) is
427 select distinct paf.person_id
428 from hr_assignment_set_amendments asgset,
429 per_assignments_f paf,
430 pay_payroll_actions ppa,
431 pay_assignment_actions paa
432 where asgset.assignment_set_id = cp_asg_set
433 and asgset.include_or_exclude = 'I'
434 and paf.assignment_id = asgset.assignment_id
435 and paf.person_id between cp_start_person
436 and cp_end_person
437 and ppa.business_group_id = ln_business_group_id
438 and ppa.report_type in ('PAYCAT4APDF','PAYCAT4AAMPDF')
439 and ppa.payroll_action_id = paa.payroll_action_id
440 and paa.tax_unit_id = nvl(ln_tax_unit_id, paa.tax_unit_id)
441 and paa.action_status = 'C'
442 and ppa.effective_date = cp_effective_date;
443
444 -- Added for Bug# 10399514
445 -- Used when RANGE_PERSON_ID functionality is available
446 CURSOR c_selected_asg_set_range(cp_start_person in number
447 ,cp_end_person in number
448 ,cp_asg_set in number
449 ,cp_effective_date in date) is
450 select distinct paf.person_id
451 from hr_assignment_set_amendments asgset,
452 per_assignments_f paf,
453 pay_payroll_actions ppa,
454 pay_assignment_actions paa,
455 pay_population_ranges ppr
456 where asgset.assignment_set_id = cp_asg_set
457 and asgset.include_or_exclude = 'I'
458 and paf.assignment_id = asgset.assignment_id
459 -- and paf.person_id between cp_start_person
460 -- and cp_end_person
461 and ppr.payroll_action_id = p_payroll_action_id
462 and ppr.chunk_number = p_chunk
463 and ppr.person_id = paf.person_id
464 and ppa.business_group_id = ln_business_group_id
465 and ppa.report_type in ('PAYCAT4APDF','PAYCAT4AAMPDF')
466 and ppa.payroll_action_id = paa.payroll_action_id
467 and paa.tax_unit_id = nvl(ln_tax_unit_id, paa.tax_unit_id)
468 and paa.action_status = 'C'
469 and ppa.effective_date = cp_effective_date;
470
471
472 /* Cursor c_all_gres to select T4 Cancel GRE based on Business Group
473 and effective date */
474 CURSOR c_all_gres(cp_bg_id number,
475 cp_eff_date date) IS
476 select hou.organization_id
477 from hr_organization_information hoi,
478 hr_all_organization_units hou
479 where hou.business_group_id = cp_bg_id
480 AND hou.organization_id = hoi.organization_id
481 AND hou.date_from <= cp_eff_date
482 AND nvl(hou.date_to,fnd_date.canonical_to_date('4712/12/31 00:00:00'))
483 >= cp_eff_date
484 AND hoi.org_information_context = 'Canada Employer Identification'
485 AND hoi.org_information5 like 'T4A%'
486 AND exists ( SELECT 1
487 FROM pay_payroll_actions ppa ,
488 pay_assignment_actions paa
489 WHERE ppa.report_type in ('PAYCAT4APDF','PAYCAT4AAMPDF')
490 AND ppa.report_qualifier = 'DEFAULT'
491 AND ppa.business_group_id = cp_bg_id
492 AND ppa.effective_date = cp_eff_date
493 AND paa.payroll_action_id = ppa.payroll_action_id
494 AND paa.tax_unit_id = hou.organization_id);
495
496 cursor c_all_asg(cp_bg_id number,
497 cp_gre number,
498 cp_eff_date date,
499 cp_start_person number,
500 cp_end_person number) is
501 select distinct paf.person_id
502 from pay_payroll_actions ppa_rep,
503 pay_assignment_actions paa_rep,
504 per_assignments_f paf
505 where ppa_rep.business_group_id = cp_bg_id
506 and ppa_rep.report_type in ('PAYCAT4APDF','PAYCAT4AAMPDF')
507 and ppa_rep.report_qualifier = 'DEFAULT'
508 and ppa_rep.effective_date = cp_eff_date
509 and ppa_rep.action_status = 'C'
510 and paa_rep.payroll_action_id = ppa_rep.payroll_action_id
511 and paa_rep.action_status = 'C'
512 and paa_rep.assignment_id = paf.assignment_id
513 and paa_rep.tax_unit_id = nvl(cp_gre,paa_rep.tax_unit_id)
514 and to_number(paf.person_id) between
515 cp_start_person and cp_end_person;
516
517 -- Added for Bug# 10399514
518 -- Used when RANGE_PERSON_ID functionality is available
519 cursor c_all_asg_range(cp_bg_id number,
520 cp_gre number,
521 cp_eff_date date,
522 cp_start_person number,
523 cp_end_person number) is
524 select distinct paf.person_id
525 from pay_payroll_actions ppa_rep,
526 pay_assignment_actions paa_rep,
527 per_assignments_f paf,
528 pay_population_ranges ppr
529 where ppa_rep.business_group_id = cp_bg_id
530 and ppa_rep.report_type in ('PAYCAT4APDF','PAYCAT4AAMPDF')
531 and ppa_rep.report_qualifier = 'DEFAULT'
532 and ppa_rep.effective_date = cp_eff_date
533 and ppa_rep.action_status = 'C'
534 and paa_rep.payroll_action_id = ppa_rep.payroll_action_id
535 and paa_rep.action_status = 'C'
536 and paa_rep.assignment_id = paf.assignment_id
537 and paa_rep.tax_unit_id = nvl(cp_gre,paa_rep.tax_unit_id)
538 -- and to_number(paf.person_id) between
539 -- cp_start_person and cp_end_person;
540 and ppr.payroll_action_id = p_payroll_action_id
541 and ppr.chunk_number = p_chunk
542 and ppr.person_id = paf.person_id;
543
544
545 PROCEDURE action_creation (p_person_id in NUMBER)
546 IS
547
548 CURSOR get_latest_t4a_cancel_dtls (cp_person_id in number
549 ,cp_tax_unit_id in number
550 ,cp_effective_date in date ) is
551 select ppa.report_type,
552 paa.assignment_id,
553 paa.assignment_action_id,
554 paa.tax_unit_id,
555 paa.serial_number,
556 paf.effective_end_date
557 from pay_payroll_actions ppa,
558 pay_assignment_actions paa,
559 per_assignments_f paf
560 where paf.person_id = to_char(cp_person_id)
561 and paf.assignment_id = paa.assignment_id
562 and paa.tax_unit_id = nvl(cp_tax_unit_id,paa.tax_unit_id)
563 and ppa.business_group_id = ln_business_group_id
564 and paa.action_status = 'C'
565 and ppa.payroll_action_id = paa.payroll_action_id
566 and ppa.effective_date = cp_effective_date
567 and ppa.report_type in ('PAYCAT4APDF','PAYCAT4AAMPDF')
568 and decode(ppa.report_type,'PAYCAT4APDF','LATEST','PAYCAT4AAMPDF',
569 pay_ca_eoy_t4a_cancel_pkg.get_parameter(
570 'MODE',ppa.legislative_parameters)) = 'LATEST'
571 and paf.effective_end_date = (select max(paf.effective_end_date)
572 from per_assignments_f paf
573 where paf.assignment_id = paa.assignment_id
574 and paf.effective_start_date <= cp_effective_date
575 and paf.effective_end_date >= trunc(cp_effective_date,'Y'))
576 and not exists (select 1
577 from pay_action_interlocks pai,
578 pay_assignment_actions paa1,
579 pay_payroll_actions ppa1
580 where pai.locked_action_id = paa.assignment_action_id
581 and pai.locking_action_id = paa1.assignment_action_id
582 and ppa1.effective_date = ppa.effective_date
583 and paa1.tax_unit_id = paa.tax_unit_id
584 and paa1.payroll_action_id = ppa1.payroll_action_id
585 and ppa1.report_type = 'PAYCAT4ACLPDF')
586 and not exists (select 1
587 from pay_assignment_actions paa2,
588 pay_payroll_actions ppa2
589 where paa2.payroll_action_id = ppa2.payroll_action_id
590 and ppa2.report_type in ('PAYCAT4APDF','PAYCAT4AAMPDF')
591 and ppa2.effective_date = ppa.effective_date
592 and paa2.tax_unit_id = paa.tax_unit_id
593 and paa2.assignment_id = paa.assignment_id
594 and substr(paa2.serial_number,1,14)||lpad(paa2.assignment_action_id,14,0)
595 > substr(paa.serial_number,1,14)||lpad(paa.assignment_action_id,14,0)
596 and decode(ppa2.report_type,'PAYCAT4APDF','LATEST','PAYCAT4AAMPDF',
597 pay_ca_eoy_t4a_cancel_pkg.get_parameter('MODE',ppa2.legislative_parameters)) = 'LATEST'
598 )
599 group by paa.assignment_action_id,ppa.report_type,paa.assignment_id,paa.tax_unit_id,paa.serial_number,paf.effective_end_date
600 order by paa.assignment_action_id desc;
601
602 CURSOR get_reprint_t4a_cancel_dtls (cp_person_id in number
603 ,cp_tax_unit_id in number
604 ,cp_effective_date in date ) is
605 select ppa.report_type,
606 paa.assignment_id,
607 paa.assignment_action_id,
608 paa.tax_unit_id,
609 paa.serial_number,
610 paf.effective_end_date
611 from pay_payroll_actions ppa,
612 pay_assignment_actions paa,
613 per_assignments_f paf
614 where paf.person_id = to_char(cp_person_id)
615 and paf.assignment_id = paa.assignment_id
616 and paa.tax_unit_id = nvl(cp_tax_unit_id,paa.tax_unit_id)
617 and ppa.business_group_id = ln_business_group_id
618 and paa.action_status = 'C'
619 and ppa.payroll_action_id = paa.payroll_action_id
620 and ppa.effective_date = cp_effective_date
621 and ppa.report_type in ('PAYCAT4APDF','PAYCAT4AAMPDF')
622 and paf.effective_end_date = (select max(paf.effective_end_date)
623 from per_assignments_f paf
624 where paf.assignment_id = paa.assignment_id
625 and paf.effective_start_date <= cp_effective_date
626 and paf.effective_end_date >= trunc(cp_effective_date,'Y'))
627 and exists
628 ( SELECT pail.locked_action_id
629 FROM pay_action_interlocks pail,
630 pay_payroll_actions ppa1,
631 pay_assignment_actions paa1
632 WHERE ppa1.report_type = 'PAYCAT4ACLPDF'
633 AND ppa1.payroll_action_id = paa1.payroll_action_id
634 AND ppa1.effective_date = cp_effective_date
635 AND paa1.assignment_action_id = pail.locking_action_id
636 AND paa1.tax_unit_id = paa.tax_unit_id
637 AND paa1.assignment_id=paf.assignment_id
638 AND pail.locked_action_id = paa.assignment_action_id)
639 AND not exists (select 1
640 from pay_assignment_actions paa2,
641 pay_payroll_actions ppa2
642 where paa2.payroll_action_id = ppa2.payroll_action_id
643 and ppa2.report_type in ('PAYCAT4APDF','PAYCAT4AAMPDF')
644 and ppa2.effective_date = ppa.effective_date
645 and paa2.tax_unit_id = paa.tax_unit_id
646 and paa2.assignment_id = paa.assignment_id
647 and substr(paa2.serial_number,1,14)||lpad(paa2.assignment_action_id,14,0)
648 > substr(paa.serial_number,1,14)||lpad(paa.assignment_action_id,14,0)
649 and decode(ppa2.report_type,'PAYCAT4APDF','LATEST','PAYCAT4AAMPDF',
650 pay_ca_eoy_t4a_cancel_pkg.get_parameter('MODE',ppa2.legislative_parameters)) = 'LATEST'
651 )
652 group by paa.assignment_action_id,ppa.report_type,paa.assignment_id,paa.tax_unit_id,paa.serial_number,paf.effective_end_date
653 order by paa.assignment_action_id desc;
654
655 --Changes for bug 15886428 starts
656 CURSOR get_latest_t4a_cancel_term (cp_person_id in number
657 ,cp_tax_unit_id in number
658 ,cp_effective_date in date ) is
659 select ppa.report_type,
660 paa.assignment_id,
661 paa.assignment_action_id,
662 paa.tax_unit_id,
663 paa.serial_number,
664 paf.effective_end_date
665 from pay_payroll_actions ppa,
666 pay_assignment_actions paa,
667 per_assignments_f paf,
668 per_periods_of_service pds
669 where paf.person_id = to_char(cp_person_id)
670 and paf.assignment_id = paa.assignment_id
671 and paa.tax_unit_id = nvl(cp_tax_unit_id,paa.tax_unit_id)
672 and ppa.business_group_id = ln_business_group_id
673 and paa.action_status = 'C'
674 and ppa.payroll_action_id = paa.payroll_action_id
675 and ppa.effective_date = cp_effective_date
676 and ppa.report_type in ('PAYCAT4APDF','PAYCAT4AAMPDF')
677 and decode(ppa.report_type,'PAYCAT4APDF','LATEST','PAYCAT4AAMPDF',
678 pay_ca_eoy_t4a_cancel_pkg.get_parameter(
679 'MODE',ppa.legislative_parameters)) = 'LATEST'
680 and paf.effective_end_date = (select max(paf.effective_end_date)
681 from per_assignments_f paf
682 where paf.assignment_id = paa.assignment_id
683 and paf.effective_start_date <= cp_effective_date
684 and paf.effective_end_date >= trunc(cp_effective_date,'Y'))
685 and not exists (select 1
686 from pay_action_interlocks pai,
687 pay_assignment_actions paa1,
688 pay_payroll_actions ppa1
689 where pai.locked_action_id = paa.assignment_action_id
690 and pai.locking_action_id = paa1.assignment_action_id
691 and ppa1.effective_date = ppa.effective_date
692 and paa1.tax_unit_id = paa.tax_unit_id
693 and paa1.payroll_action_id = ppa1.payroll_action_id
694 and ppa1.report_type = 'PAYCAT4ACLPDF')
695 and not exists (select 1
696 from pay_assignment_actions paa2,
697 pay_payroll_actions ppa2
698 where paa2.payroll_action_id = ppa2.payroll_action_id
699 and ppa2.report_type in ('PAYCAT4APDF','PAYCAT4AAMPDF')
700 and ppa2.effective_date = ppa.effective_date
701 and paa2.tax_unit_id = paa.tax_unit_id
702 and paa2.assignment_id = paa.assignment_id
703 and substr(paa2.serial_number,1,14)||lpad(paa2.assignment_action_id,14,0)
704 > substr(paa.serial_number,1,14)||lpad(paa.assignment_action_id,14,0)
705 and decode(ppa2.report_type,'PAYCAT4APDF','LATEST','PAYCAT4AAMPDF',
706 pay_ca_eoy_t4a_cancel_pkg.get_parameter('MODE',ppa2.legislative_parameters)) = 'LATEST'
707 )
708 and pds.actual_termination_date is not null
709 and pds.period_of_service_id = paf.period_of_service_id
710 group by paa.assignment_action_id,ppa.report_type,paa.assignment_id,paa.tax_unit_id,paa.serial_number,paf.effective_end_date
711 order by paa.assignment_action_id desc;
712
713 CURSOR get_reprint_t4a_cancel_term (cp_person_id in number
714 ,cp_tax_unit_id in number
715 ,cp_effective_date in date ) is
716 select ppa.report_type,
717 paa.assignment_id,
718 paa.assignment_action_id,
719 paa.tax_unit_id,
720 paa.serial_number,
721 paf.effective_end_date
722 from pay_payroll_actions ppa,
723 pay_assignment_actions paa,
724 per_assignments_f paf,
725 per_periods_of_service pds
726 where paf.person_id = to_char(cp_person_id)
727 and paf.assignment_id = paa.assignment_id
728 and paa.tax_unit_id = nvl(cp_tax_unit_id,paa.tax_unit_id)
729 and ppa.business_group_id = ln_business_group_id
730 and paa.action_status = 'C'
731 and ppa.payroll_action_id = paa.payroll_action_id
732 and ppa.effective_date = cp_effective_date
733 and ppa.report_type in ('PAYCAT4APDF','PAYCAT4AAMPDF')
734 and paf.effective_end_date = (select max(paf.effective_end_date)
735 from per_assignments_f paf
736 where paf.assignment_id = paa.assignment_id
737 and paf.effective_start_date <= cp_effective_date
738 and paf.effective_end_date >= trunc(cp_effective_date,'Y'))
739 and exists
740 ( SELECT pail.locked_action_id
741 FROM pay_action_interlocks pail,
742 pay_payroll_actions ppa1,
743 pay_assignment_actions paa1
744 WHERE ppa1.report_type = 'PAYCAT4ACLPDF'
745 AND ppa1.payroll_action_id = paa1.payroll_action_id
746 AND ppa1.effective_date = cp_effective_date
747 AND paa1.assignment_action_id = pail.locking_action_id
748 AND paa1.tax_unit_id = paa.tax_unit_id
749 AND paa1.assignment_id=paf.assignment_id
750 AND pail.locked_action_id = paa.assignment_action_id)
751 AND not exists (select 1
752 from pay_assignment_actions paa2,
753 pay_payroll_actions ppa2
754 where paa2.payroll_action_id = ppa2.payroll_action_id
755 and ppa2.report_type in ('PAYCAT4APDF','PAYCAT4AAMPDF')
756 and ppa2.effective_date = ppa.effective_date
757 and paa2.tax_unit_id = paa.tax_unit_id
758 and paa2.assignment_id = paa.assignment_id
759 and substr(paa2.serial_number,1,14)||lpad(paa2.assignment_action_id,14,0)
760 > substr(paa.serial_number,1,14)||lpad(paa.assignment_action_id,14,0)
761 and decode(ppa2.report_type,'PAYCAT4APDF','LATEST','PAYCAT4AAMPDF',
762 pay_ca_eoy_t4a_cancel_pkg.get_parameter('MODE',ppa2.legislative_parameters)) = 'LATEST'
763 )
764 and pds.actual_termination_date is not null
765 and pds.period_of_service_id = paf.period_of_service_id
766 group by paa.assignment_action_id,ppa.report_type,paa.assignment_id,paa.tax_unit_id,paa.serial_number,paf.effective_end_date
767 order by paa.assignment_action_id desc;
768 --Changes for bug 15886428 starts
769
770 CURSOR get_warning_dtls_for_ee(cp_person_id in number) is
771 select substr(full_name,1,48), employee_number
772 from per_people_f
773 where person_id = cp_person_id
774 order by effective_end_date desc;
775
776 CURSOR check_arch_action_exists (p_assignment_action_id pay_assignment_actions.assignment_action_id%TYPE) is
777 select 'Y'
778 from pay_assignment_actions
779 where assignment_action_id = p_assignment_action_id;
780
781 lv_gross_earn_value varchar2(30);
782 lv_jurisdiction varchar2(10);
783 lv_fed_amend_flag varchar2(5);
784 ln_gross_earn_ue_id number;
785 ln_yepp_pact_id number;
786 ln_gre_id_null number;
787 lv_flag_count number :=0;
788 lv_serial_number pay_assignment_actions.serial_number%TYPE;
789
790 lv_negative_bal_flag varchar2(10);
791 lv_neg_bal_mesg varchar2(100);
792 lv_person_type varchar2(20);
793 lv_message_level varchar2(20);
794 lv_message varchar2(240);
795
796 lv_sin varchar2(20);
797 lv_employee_full_name varchar2(300);
798 lv_employee_last_name varchar2(200);
799 lv_employee_name varchar2(200);
800 l_arch_asg_action_check varchar2(1);
801 ln_iteration number :=0;
802 l_effective_end_date date; --Added for bug 15886428
803
804 BEGIN
805
806 hr_utility.trace('Inside action_creation');
807 ln_iteration := 0;
808 if lv_print = 'LATEST' then
809 if nvl(l_print_term,'N') = 'Y' then
810 open get_latest_t4a_cancel_term(p_person_id
811 ,ln_tax_unit_id
812 ,ld_end_date);
813 else
814 open get_latest_t4a_cancel_dtls(p_person_id
815 ,ln_tax_unit_id
816 ,ld_end_date);
817 end if;
818
819 loop
820
821 if nvl(l_print_term,'N') = 'Y' then
822 fetch get_latest_t4a_cancel_term into lv_report_type
823 ,ln_primary_assignment_id
824 ,ln_rep_act_to_lock
825 ,ln_gre_id_null
826 ,lv_serial_number
827 ,l_effective_end_date;
828 else
829 fetch get_latest_t4a_cancel_dtls into lv_report_type
830 ,ln_primary_assignment_id
831 ,ln_rep_act_to_lock
832 ,ln_gre_id_null
833 ,lv_serial_number
834 ,l_effective_end_date;
835 end if;
836
837 if (nvl(l_print_term,'N') = 'Y' and get_latest_t4a_cancel_term%notfound)
838 or (nvl(l_print_term,'N') = 'N' and get_latest_t4a_cancel_dtls%notfound) then
839 hr_utility.trace('get_latest_t4a_cancel_dtls not found ln_iteration:'||ln_iteration);
840 if ln_iteration = 0 then
841 open get_warning_dtls_for_ee(p_person_id);
842 fetch get_warning_dtls_for_ee into lv_full_name
843 ,lv_employee_number;
844 close get_warning_dtls_for_ee;
845
846 hr_utility.trace('get_latest_t4a_cancel_dtls not found');
847 hr_utility.trace('p_person_id :'||to_char(p_person_id));
848
849 lv_record_name := 'T4A Cancel PDF Report';
850 lv_message := 'Either T4A PDF or T4A Amendment PDF were not run for the employee';
851
852 pay_core_utils.push_message(801,'PAY_INVALID_EE_FORMAT','I');
853 pay_core_utils.push_token('record_name',lv_record_name);
854 pay_core_utils.push_token('name_or_number',lv_full_name);
855 pay_core_utils.push_token('description',lv_message);
856
857 end if;
858
859 exit;
860
861 end if;
862
863 if (nvl(l_print_term,'N') = 'Y' and get_latest_t4a_cancel_term%found)
864 or (nvl(l_print_term,'N') = 'N' and get_latest_t4a_cancel_dtls%found) then
865
866 ln_iteration := ln_iteration + 1;
867 ln_arch_act_to_lock := substr(lv_serial_number,1,14);
868 hr_utility.trace('ln_arch_act_to_lock:'||ln_arch_act_to_lock);
869
870 open check_arch_action_exists(ln_arch_act_to_lock);
871 fetch check_arch_action_exists into l_arch_asg_action_check;
872 if check_arch_action_exists%notfound then
873
874 open get_warning_dtls_for_ee(p_person_id);
875 fetch get_warning_dtls_for_ee into lv_full_name
876 ,lv_employee_number;
877 close get_warning_dtls_for_ee;
878
879 hr_utility.trace('check_arch_action_exists not found');
880 hr_utility.trace('p_person_id :'||to_char(p_person_id));
881 hr_utility.trace('ln_arch_act_to_lock :'||to_char(ln_arch_act_to_lock));
882 hr_utility.trace('lv_report_type :'||lv_report_type);
883 hr_utility.trace('ln_rep_act_to_lock :'||ln_rep_act_to_lock);
884 lv_record_name := 'T4A Cancel PDF Report';
885 lv_message := 'Archiver run with assignment action id '||ln_arch_act_to_lock||' is rolled back';
886
887 pay_core_utils.push_message(801,'PAY_INVALID_EE_FORMAT','I');
888 pay_core_utils.push_token('record_name',lv_record_name);
889 pay_core_utils.push_token('name_or_number',lv_full_name);
890 pay_core_utils.push_token('description',lv_message);
891
892 close check_arch_action_exists;
893
894 exit;
895
896 end if;
897 close check_arch_action_exists;
898
899 lv_negative_bal_flag := substr(lv_serial_number,29,1);
900 hr_utility.trace('lv_negative_bal_flag:'||lv_negative_bal_flag);
901
902 if lv_negative_bal_flag ='Y' then
903
904 open get_warning_dtls_for_ee(p_person_id);
905 fetch get_warning_dtls_for_ee into lv_full_name
906 ,lv_employee_number;
907 close get_warning_dtls_for_ee;
908
909 hr_utility.trace('Negative_bal_flag is Y');
910 hr_utility.trace('p_person_id :'||to_char(p_person_id));
911
912 lv_record_name := 'T4A Cancel PDF Report';
913 lv_message := 'This employee has negative balance';
914
915 pay_core_utils.push_message(801,'PAY_INVALID_EE_FORMAT','I');
916 pay_core_utils.push_token('record_name',lv_record_name);
917 pay_core_utils.push_token('name_or_number',lv_full_name);
918 pay_core_utils.push_token('description',lv_message);
919
920 exit;
921
922 end if;
923
924 if ln_gre_parameter is NULL then
925 ln_tax_unit_id := ln_gre_id_null;
926 end if;
927
928 /* Create an assignment action for this person */
929 hr_utility.trace('get_latest_t4A_cancel_dtls found ');
930 hr_utility.trace('Report Type: '||lv_report_type);
931
932 if nvl(pay_us_employee_payslip_web.get_doc_eit('T4APDF',
933 'PRINT',
934 'ASSIGNMENT',
935 ln_primary_assignment_id,
936 l_effective_end_date),'Y') = 'Y' then
937 select pay_assignment_actions_s.nextval
938 into ln_t4acancel_asg_action
939 from dual;
940
941 hr_utility.trace('New T4A Cancel Action = ' ||
942 to_char(ln_t4acancel_asg_action));
943
944 /* Insert into pay_assignment_actions. */
945 hr_nonrun_asact.insact(ln_t4acancel_asg_action
946 ,ln_primary_assignment_id
947 ,p_payroll_action_id
948 ,p_chunk
949 ,ln_tax_unit_id);
950
951 if ln_gre_parameter is NULL then
952 ln_tax_unit_id := '';
953 end if;
954
955 /*****************************************************************
956 ** Update the serial number column with Archiver assignment_action
957 ** and Archiver Payroll_action_id so that we need not refer back
958 ** in the reports.
959 ******************************************************************/
960
961 ln_serial_number := lpad(to_char(ln_arch_act_to_lock),14,0)||
962 lpad(to_char(ln_rep_act_to_lock),14,0)||
963 lv_negative_bal_flag;
964
965 update pay_assignment_actions aa
966 set aa.serial_number = ln_serial_number
967 where aa.assignment_action_id = ln_t4acancel_asg_action;
968
969 hr_utility.trace('Archiver Asg Act :'||substr(ln_serial_number,1,14));
970 hr_utility.trace('Archiver PactID :'||substr(ln_serial_number,15,14));
971
972
973 --Added to lock the PDF Report Run
974 hr_nonrun_asact.insint(ln_t4acancel_asg_action,ln_rep_act_to_lock);
975 hr_utility.trace('Locking the PDF Assignment Action');
976 hr_utility.trace('Locking Action'||ln_t4acancel_asg_action);
977 hr_utility.trace('Locked Action = ' || to_char(ln_rep_act_to_lock));
978
979 --Added to lock the Archiver
980 hr_nonrun_asact.insint(ln_t4acancel_asg_action,ln_arch_act_to_lock);
981 hr_utility.trace('Locking the Archiver Assignment Action');
982 hr_utility.trace('Locking Action'||ln_t4acancel_asg_action);
983 hr_utility.trace('Locked Action = ' || to_char(ln_arch_act_to_lock));
984 end if;
985
986 end if; /* get_latest_t4a_cancel_dtls found*/
987
988 end loop; /* get_latest_t4a_cancel_dtls loop */
989
990 if nvl(l_print_term,'N') = 'Y' then
991 close get_latest_t4a_cancel_term;
992 else
993 close get_latest_t4a_cancel_dtls;
994 end if;
995
996 end if; --LATEST
997
998 if lv_print = 'REPRINT' then
999
1000 if nvl(l_print_term,'N') = 'Y' then
1001 open get_reprint_t4a_cancel_term (p_person_id
1002 ,ln_tax_unit_id
1003 ,ld_end_date);
1004 else
1005 open get_reprint_t4a_cancel_dtls (p_person_id
1006 ,ln_tax_unit_id
1007 ,ld_end_date);
1008 end if;
1009
1010 loop
1011
1012 if nvl(l_print_term,'N') = 'Y' then
1013 fetch get_reprint_t4a_cancel_term into lv_report_type
1014 ,ln_primary_assignment_id
1015 ,ln_rep_act_to_lock
1016 ,ln_gre_id_null
1017 ,lv_serial_number
1018 ,l_effective_end_date;
1019 else
1020 fetch get_reprint_t4a_cancel_dtls into lv_report_type
1021 ,ln_primary_assignment_id
1022 ,ln_rep_act_to_lock
1023 ,ln_gre_id_null
1024 ,lv_serial_number
1025 ,l_effective_end_date;
1026 end if;
1027
1028
1029 if (nvl(l_print_term,'N') = 'Y' and get_reprint_t4a_cancel_term%notfound)
1030 or (nvl(l_print_term,'N') = 'N' and get_reprint_t4a_cancel_dtls%notfound) then
1031
1032 hr_utility.trace('get_latest_t4a_cancel_dtls not found ln_iteration:'||ln_iteration);
1033
1034 if ln_iteration = 0 then
1035
1036 open get_warning_dtls_for_ee(p_person_id);
1037 fetch get_warning_dtls_for_ee into lv_full_name
1038 ,lv_employee_number;
1039 close get_warning_dtls_for_ee;
1040
1041 hr_utility.trace('get_reprint_t4a_cancel_dtls not found');
1042 hr_utility.trace('p_person_id :'||to_char(p_person_id));
1043
1044 lv_record_name := 'T4A Cancel PDF Report';
1045 lv_message := 'As T4A Cancel PDF was not run in Unprint Mode, Reprint mode will not be applicable';
1046
1047 pay_core_utils.push_message(801,'PAY_INVALID_EE_FORMAT','I');
1048 pay_core_utils.push_token('record_name',lv_record_name);
1049 pay_core_utils.push_token('name_or_number',lv_full_name);
1050 pay_core_utils.push_token('description',lv_message);
1051
1052 end if;
1053
1054 exit;
1055
1056 end if;
1057
1058 if (nvl(l_print_term,'N') = 'Y' and get_reprint_t4a_cancel_term%found)
1059 or (nvl(l_print_term,'N') = 'N' and get_reprint_t4a_cancel_dtls%found) then
1060
1061 ln_iteration := ln_iteration + 1;
1062
1063 ln_arch_act_to_lock := substr(lv_serial_number,1,14);
1064 hr_utility.trace('ln_arch_act_to_lock:'||ln_arch_act_to_lock);
1065
1066 open check_arch_action_exists(ln_arch_act_to_lock);
1067 fetch check_arch_action_exists into l_arch_asg_action_check;
1068 if check_arch_action_exists%notfound then
1069
1070 open get_warning_dtls_for_ee(p_person_id);
1071 fetch get_warning_dtls_for_ee into lv_full_name
1072 ,lv_employee_number;
1073 close get_warning_dtls_for_ee;
1074
1075 hr_utility.trace('check_arch_action_exists not found');
1076 hr_utility.trace('p_person_id :'||to_char(p_person_id));
1077 hr_utility.trace('ln_arch_act_to_lock :'||to_char(ln_arch_act_to_lock));
1078 hr_utility.trace('lv_report_type :'||lv_report_type);
1079 hr_utility.trace('ln_rep_act_to_lock :'||ln_rep_act_to_lock);
1080 lv_record_name := 'T4A Cancel PDF Report';
1081 lv_message := 'Archiver run with assignment action id '||ln_arch_act_to_lock||' is rolled back';
1082
1083 pay_core_utils.push_message(801,'PAY_INVALID_EE_FORMAT','I');
1084 pay_core_utils.push_token('record_name',lv_record_name);
1085 pay_core_utils.push_token('name_or_number',lv_full_name);
1086 pay_core_utils.push_token('description',lv_message);
1087
1088 end if;
1089 close check_arch_action_exists;
1090
1091 lv_negative_bal_flag := substr(lv_serial_number,29,1);
1092 hr_utility.trace('lv_negative_bal_flag:'||lv_negative_bal_flag);
1093
1094 if lv_negative_bal_flag ='Y' then
1095
1096 open get_warning_dtls_for_ee(p_person_id);
1097 fetch get_warning_dtls_for_ee into lv_full_name
1098 ,lv_employee_number;
1099 close get_warning_dtls_for_ee;
1100
1101 hr_utility.trace('negative_bal_flag is Y');
1102 hr_utility.trace('p_person_id :'||to_char(p_person_id));
1103
1104 lv_record_name := 'T4A Cancel PDF Report';
1105 lv_message := 'This employee has negative balance';
1106
1107 pay_core_utils.push_message(801,'PAY_INVALID_EE_FORMAT','I');
1108 pay_core_utils.push_token('record_name',lv_record_name);
1109 pay_core_utils.push_token('name_or_number',lv_full_name);
1110 pay_core_utils.push_token('description',lv_message);
1111
1112 end if;
1113
1114 if ln_gre_parameter is NULL then
1115 ln_tax_unit_id := ln_gre_id_null;
1116 end if;
1117
1118
1119 /* Create an assignment action for this person */
1120 hr_utility.trace('get_reprint_t4a_cancel_dtls found ');
1121 hr_utility.trace('Report Type: '||lv_report_type);
1122
1123 if nvl(pay_us_employee_payslip_web.get_doc_eit('T4APDF',
1124 'PRINT',
1125 'ASSIGNMENT',
1126 ln_primary_assignment_id,
1127 l_effective_end_date),'Y') = 'Y' then
1128
1129 select pay_assignment_actions_s.nextval
1130 into ln_t4acancel_asg_action
1131 from dual;
1132
1133 hr_utility.trace('New T4A Cancel Action = ' ||
1134 to_char(ln_t4acancel_asg_action));
1135
1136 /* Insert into pay_assignment_actions. */
1137 hr_nonrun_asact.insact(ln_t4acancel_asg_action
1138 ,ln_primary_assignment_id
1139 ,p_payroll_action_id
1140 ,p_chunk
1141 ,ln_tax_unit_id);
1142
1143 if ln_gre_parameter is NULL then
1144 ln_tax_unit_id := '';
1145 end if;
1146
1147 /***********************************************************
1148 ** Update the serial number column with
1149 ** Archiver assignment_action and Archiver Payroll_action_id
1150 ** so that we need not refer back in the reports.
1151 ***********************************************************/
1152
1153 ln_serial_number := lpad(to_char(ln_arch_act_to_lock),14,0)||
1154 lpad(to_char(ln_rep_act_to_lock),14,0)||
1155 lv_negative_bal_flag;
1156
1157 update pay_assignment_actions aa
1158 set aa.serial_number = ln_serial_number
1159 where aa.assignment_action_id = ln_t4acancel_asg_action;
1160
1161 hr_utility.trace('Archiver Asg Act :'||substr(ln_serial_number,1,14));
1162 hr_utility.trace('Archiver PactID :'||substr(ln_serial_number,15,14));
1163 end if; -- 'T4APDF'
1164
1165 end if; /* get_reprint_t4amendpdf_dtls found*/
1166
1167 end loop; /* get_reprint_t4amendpdf_dtls loop */
1168
1169 if nvl(l_print_term,'N') = 'Y' then
1170 close get_reprint_t4a_cancel_term;
1171 else
1172 close get_reprint_t4a_cancel_dtls;
1173 end if;
1174
1175 end if; --REPRINT
1176
1177 hr_utility.trace('Leaving action_creation');
1178
1179 END action_creation;
1180
1181 BEGIN
1182 hr_utility.trace('Entered action_creation ');
1183 hr_utility.trace('p_payroll_action_id = '|| to_char(p_payroll_action_id));
1184 hr_utility.trace('p_start_person_id ='|| to_char(p_start_person_id));
1185 hr_utility.trace('p_end_person_id = '|| to_char(p_end_person_id));
1186 hr_utility.trace('p_chunk = ' || to_char(p_chunk));
1187
1188 get_payroll_action_info(p_payroll_action_id => p_payroll_action_id
1189 ,p_start_date => ld_start_date
1190 ,p_end_date => ld_end_date
1191 ,p_business_group_id => ln_business_group_id
1192 ,p_tax_unit_id => ln_gre_parameter
1193 ,p_person_id => ln_person_id
1194 ,p_asg_set => ln_asg_set
1195 ,p_print => lv_print
1196 ,p_report_type => lv_ppr_report_type
1197 -- Added for bug 10399514
1198 ,p_state => l_state
1199 ,p_report_cat => l_report_cat
1200 ,p_print_term => l_print_term);
1201 -- Added for bug 10399514
1202
1203 hr_utility.trace('lv_ppr_report_type: '||lv_ppr_report_type);
1204
1205 ln_tax_unit_id := ln_gre_parameter;
1206
1207 -- Code modification for bug 10399514 start here
1208 l_person_on := FALSE ;
1209
1210 Begin
1211 select report_format
1212 into l_report_format
1213 from pay_report_format_mappings_f
1214 where report_type = lv_ppr_report_type
1215 and report_qualifier = l_state
1216 and report_category = l_report_cat ;
1217 Exception
1218 When Others Then
1219 l_report_format := Null ;
1220 End ;
1221
1222 l_person_on := pay_ac_utility.range_person_on( p_report_type => lv_ppr_report_type,
1223 p_report_format => l_report_format,
1224 p_report_qualifier => l_state,
1225 p_report_category => l_report_cat) ;
1226
1227 -- Code modification for bug 10399514 ends here
1228
1229 /* PERSON ID IS NOT NULL */
1230 if ln_person_id is not null then
1231 action_creation(p_start_person_id);
1232
1233 elsif ln_asg_set is not null then
1234
1235 hr_utility.trace('Entered Asg Set logic');
1236 hr_utility.trace('Asg Set ='||to_char(ln_asg_set));
1237 hr_utility.trace('p_start_person_id ='||to_char(p_start_person_id));
1238 hr_utility.trace('End Person ='||to_char(p_end_person_id));
1239
1240 -- Code modification for bug 10399514 start here
1241 if l_person_on then
1242 hr_utility.trace('opening c_selected_asg_set_range CURSOR');
1243 OPEN c_selected_asg_set_range (p_start_person_id
1244 ,p_end_person_id
1245 ,ln_asg_set
1246 ,ld_end_date);
1247 else
1248 hr_utility.trace('opening c_selected_asg_set CURSOR');
1249 OPEN c_selected_asg_set (p_start_person_id
1250 ,p_end_person_id
1251 ,ln_asg_set
1252 ,ld_end_date);
1253 end if ;
1254
1255 -- Code modification for bug 10399514 ends here
1256
1257 loop
1258
1259
1260 -- Code modification for bug 10399514 starts here
1261 if l_person_on then
1262 hr_utility.trace('fetching from c_selected_asg_set_range CURSOR');
1263 fetch c_selected_asg_set_range
1264 into ln_set_person_id;
1265 exit when c_selected_asg_set_range%NOTFOUND;
1266 else
1267 hr_utility.trace('fetching from c_selected_asg_set CURSOR');
1268 fetch c_selected_asg_set
1269 into ln_set_person_id;
1270 exit when c_selected_asg_set%NOTFOUND;
1271 end if ;
1272 -- Code modification for bug 10399514 ends here
1273
1274 hr_utility.trace('Assignment Set Person ='||to_char(ln_set_person_id));
1275 action_creation(ln_set_person_id);
1276
1277 end loop;
1278
1279 -- Code modification for bug 10399514 starts here
1280 if l_person_on then
1281 hr_utility.trace('closing c_selected_asg_set_range CURSOR');
1282 close c_selected_asg_set_range;
1283 else
1284 hr_utility.trace('closing c_selected_asg_set CURSOR');
1285 close c_selected_asg_set;
1286 end if ;
1287 -- Code modification for bug 10399514 ends here
1288
1289 elsif ln_tax_unit_id is not null then
1290
1291 hr_utility.trace('Entered GRE not null logic');
1292 hr_utility.trace('GRE ='||to_char(ln_tax_unit_id));
1293 hr_utility.trace('p_start_person_id ='||to_char(p_start_person_id));
1294 hr_utility.trace('End Person ='||to_char(p_end_person_id));
1295
1296 -- Code modification for bug 10399514 start here
1297 if l_person_on then
1298 hr_utility.trace('opening c_all_asg_range CURSOR');
1299 OPEN c_all_asg_range (ln_business_group_id
1300 ,ln_tax_unit_id
1301 ,ld_end_date
1302 ,p_start_person_id
1303 ,p_end_person_id);
1304 else
1305 hr_utility.trace('opening c_all_asg CURSOR');
1306 OPEN c_all_asg (ln_business_group_id
1307 ,ln_tax_unit_id
1308 ,ld_end_date
1309 ,p_start_person_id
1310 ,p_end_person_id);
1311 end if ;
1312
1313 -- Code modification for bug 10399514 ends here
1314 loop
1315
1316 -- Code modification for bug 10399514 starts here
1317 if l_person_on then
1318 hr_utility.trace('fetching from c_all_asg_range CURSOR');
1319 fetch c_all_asg_range
1320 into ln_set_person_id;
1321 exit when c_all_asg_range%NOTFOUND;
1322 else
1323 hr_utility.trace('fetching from c_all_asg CURSOR');
1324 fetch c_all_asg
1325 into ln_set_person_id;
1326 exit when c_all_asg%NOTFOUND;
1327 end if ;
1328 -- Code modification for bug 10399514 ends here
1329
1330 action_creation(ln_set_person_id);
1331
1332 end loop;
1333 -- Code modification for bug 10399514 starts here
1334 if l_person_on then
1335 hr_utility.trace('closing c_all_asg_range CURSOR');
1336 close c_all_asg_range;
1337 else
1338 hr_utility.trace('closing c_all_asg CURSOR');
1339 close c_all_asg;
1340 end if ;
1341 -- Code modification for bug 10399514 ends here
1342 else
1343
1344 hr_utility.trace('Entered All GRE logic');
1345
1346 open c_all_gres(ln_business_group_id,
1347 ld_end_date);
1348
1349 loop -- c_all_gres
1350
1351 fetch c_all_gres into ln_gre_id;
1352 if c_all_gres%NOTFOUND then
1353 hr_utility.trace('c_all_gres NOT FOUND');
1354 exit;
1355 end if;
1356
1357 hr_utility.trace('GRE ='||to_char(ln_gre_id));
1358 hr_utility.trace('p_start_person_id ='||to_char(p_start_person_id));
1359 hr_utility.trace('End Person ='||to_char(p_end_person_id));
1360
1361 -- Code modification for bug 10399514 start here
1362 if l_person_on then
1363 hr_utility.trace('opening c_all_asg_range CURSOR');
1364 OPEN c_all_asg_range (ln_business_group_id
1365 ,ln_gre_id
1366 ,ld_end_date
1367 ,p_start_person_id
1368 ,p_end_person_id);
1369 else
1370 hr_utility.trace('opening c_all_asg CURSOR');
1371 OPEN c_all_asg (ln_business_group_id
1372 ,ln_gre_id
1373 ,ld_end_date
1374 ,p_start_person_id
1375 ,p_end_person_id);
1376 end if ;
1377
1378 -- Code modification for bug 10399514 ends here
1379
1380 loop -- c_all_asg
1381
1382 -- Code modification for bug 10399514 starts here
1383 if l_person_on then
1384 hr_utility.trace('fetching from c_all_asg_range CURSOR');
1385 fetch c_all_asg_range
1386 into ln_set_person_id;
1387 exit when c_all_asg_range%NOTFOUND;
1388 else
1389 hr_utility.trace('fetching from c_all_asg CURSOR');
1390 fetch c_all_asg
1391 into ln_set_person_id;
1392 exit when c_all_asg%NOTFOUND;
1393 end if ;
1394 -- Code modification for bug 10399514 ends here
1395
1396 ln_tax_unit_id := ln_gre_id;
1397 action_creation(ln_set_person_id);
1398
1399 end loop; -- c_all_asg
1400 -- Code modification for bug 10399514 starts here
1401 if l_person_on then
1402 hr_utility.trace('closing c_all_asg_range CURSOR');
1403 close c_all_asg_range;
1404 else
1405 hr_utility.trace('closing c_all_asg CURSOR');
1406 close c_all_asg;
1407 end if ;
1408 -- Code modification for bug 10399514 ends here
1409
1410 end loop; -- c_all_gres
1411 close c_all_gres;
1412
1413 end if; /* ln_person_id */
1414
1415 END action_creation;
1416
1417
1418 ---------------------------------- sort_action ------------------------------
1419
1420 PROCEDURE sort_action
1421 (
1422 payactid in varchar2,
1423 sqlstr in out nocopy varchar2,
1424 len out nocopy number
1425 ) is
1426
1427 l_dt date;
1428 l_year number ;
1429 l_gre_id pay_assignment_actions.tax_unit_id%type;
1430 l_per_id per_assignments_f.person_id%type;
1431 l_sort1 varchar2(60);
1432 l_sort2 varchar2(60);
1433 l_sort3 varchar2(60);
1434 l_year_start date;
1435 l_year_end date;
1436 l_print varchar2(20);
1437 l_asg_set_id hr_assignment_set_amendments.assignment_set_id%TYPE;
1438 l_bg_id pay_payroll_actions.business_group_id%type ;
1439
1440 begin
1441
1442 begin
1443 select pay_ca_eoy_t4a_cancel_pkg.get_parameter('GRE_ID',ppa.legislative_parameters),
1444 pay_ca_eoy_t4a_cancel_pkg.get_parameter('PER_ID',ppa.legislative_parameters),
1445 pay_ca_eoy_t4a_cancel_pkg.get_parameter('ASG_SET_ID',ppa.legislative_parameters),
1446 pay_ca_eoy_t4a_cancel_pkg.get_parameter('P_S1',ppa.legislative_parameters),
1447 pay_ca_eoy_t4a_cancel_pkg.get_parameter('P_S2',ppa.legislative_parameters),
1448 pay_ca_eoy_t4a_cancel_pkg.get_parameter('P_S3',ppa.legislative_parameters),
1449 pay_ca_eoy_t4a_cancel_pkg.get_parameter('EFFECTIVE_DATE',ppa.legislative_parameters),
1450 pay_ca_eoy_t4a_cancel_pkg.get_parameter('MODE',ppa.legislative_parameters),
1451 ppa.effective_date,
1452 ppa.start_date,
1453 ppa.business_group_id
1454 into l_gre_id,
1455 l_per_id,
1456 l_asg_set_id,
1457 l_sort1,
1458 l_sort2,
1459 l_sort3,
1460 l_dt, --session_date
1461 l_print,
1462 l_year_end,
1463 l_year_start,
1464 l_bg_id
1465 from pay_payroll_actions ppa
1466 where ppa.payroll_action_id = payactid;
1467
1468 exception
1469 when no_data_found then
1470 hr_utility.trace('Error in Sort Procedure - getting legislative param');
1471 raise;
1472 end;
1473
1474 if l_year_end > l_dt then
1475 l_dt := l_year_end;
1476 end if;
1477
1478 hr_utility.trace('Beginning of the sort_action cursor');
1479
1480 sqlstr := 'select paa1.rowid
1481 from hr_all_organization_units hou,
1482 hr_all_organization_units hou1,
1483 hr_locations_all loc,
1484 per_all_people_f ppf,
1485 per_all_assignments_f paf,
1486 pay_assignment_actions paa1,
1487 pay_payroll_actions ppa1
1488 where ppa1.payroll_action_id = :p_payroll_action_id
1489 and paa1.payroll_action_id = ppa1.payroll_action_id
1490 and paa1.assignment_id = paf.assignment_id
1491 and paf.effective_start_date =
1492 (select max(paf2.effective_start_date)
1493 from per_all_assignments_f paf2
1494 where paf2.assignment_id= paf.assignment_id
1495 and paf2.effective_start_date
1496 <= ppa1.effective_date)
1497 and paf.effective_end_date >= ppa1.start_date
1498 and paf.assignment_type = ''E''
1499 and hou1.organization_id = paa1.tax_unit_id
1500 and hou.organization_id = paf.organization_id
1501 and loc.location_id = paf.location_id
1502 and ppf.person_id = paf.person_id
1503 and ppf.effective_start_date =
1504 (select max(ppf2.effective_start_date)
1505 from per_all_people_f ppf2
1506 where ppf2.person_id= paf.person_id
1507 and ppf2.effective_start_date
1508 <= ppa1.effective_date)
1509 and ppf.effective_end_date >= ppa1.start_date
1510 order by
1511 decode(pay_ca_t4_reg.get_parameter
1512 (''P_S1'',ppa1.legislative_parameters),
1513 ''GRE'',hou1.name,
1514 ''ORGANIZATION'',hou.name,
1515 ''LOCATION'',loc.location_code,null),
1516 decode(pay_ca_t4_reg.get_parameter(''P_S2'',ppa1.legislative_parameters),
1517 ''GRE'',hou1.name,
1518 ''ORGANIZATION'',hou.name,
1519 ''LOCATION'',loc.location_code,null),
1520 decode(pay_ca_t4_reg.get_parameter(''P_S3'',ppa1.legislative_parameters),
1521 ''GRE'',hou1.name,
1522 ''ORGANIZATION'',hou.name,
1523 ''LOCATION'',loc.location_code,null),
1524 ppf.last_name,first_name';
1525
1526
1527 len := length(sqlstr); -- return the length of the string.
1528 hr_utility.trace('End of the sort_Action cursor');
1529
1530 end sort_action;
1531
1532
1533 FUNCTION get_parameter(name in varchar2,
1534 parameter_list varchar2) return varchar2
1535 IS
1536 start_ptr number;
1537 end_ptr number;
1538 token_val pay_payroll_actions.legislative_parameters%type;
1539 par_value pay_payroll_actions.legislative_parameters%type;
1540
1541 BEGIN
1542
1543 token_val := name||'=';
1544
1545 start_ptr := instr(parameter_list, token_val) + length(token_val);
1546 end_ptr := instr(parameter_list, ' ',start_ptr);
1547
1548
1549 /* if there is no spaces use then length of the string */
1550 if end_ptr = 0 then
1551 end_ptr := length(parameter_list)+1;
1552 end if;
1553
1554 /* Did we find the token */
1555 if instr(parameter_list, token_val) = 0 then
1556 par_value := NULL;
1557 else
1558 par_value := substr(parameter_list, start_ptr, end_ptr - start_ptr);
1559 end if;
1560
1561 return par_value;
1562
1563 END get_parameter;
1564 Begin
1565
1566 /* hr_utility.trace_on(null,'T4A_CANCEL_PDF'); */
1567 null;
1568
1569 end pay_ca_eoy_t4a_cancel_pkg;