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