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