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