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