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