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