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