1 package body pay_ca_rl2_reg as
2 /* $Header: pycarl2.pkb 120.8.12020000.5 2013/04/30 10:02:21 rgottipa ship $ */
3 /*
4 Copyright (c) Oracle Corporation 1991,1992,1993. All rights reserved
5 --
6 Name :This package defines the cursors needed to run
7 RL2 Register Multi-Threaded Report
8 --
9 Change List
10 -----------
11 Date Name Vers Description
12 ----------- ---------- ----- -----------------------------------
13 07-OCT-2002 ssouresr 115.0 Created
14 18-NOV-2002 ssouresr 115.1 Added to_number to serial_number returned
15 by range_cursor
16 22_NOV-2002 ssouresr 115.2 Changed Report Category to ARCHIVE
17 11-NOV-2003 ssouresr 115.4 Made changes to use Prov Reporting Est
18 instead of Quebec Business Number. Also
19 made report Multi-GRE compliant.
20 30-DEC-2003 ssattini 115.5 Changed the Sort_action procedure to fix
21 bug#3163968 and also added get_label
22 function.
23 22-Jan-2004 ssattini 115.6 Added function get_primary_address
24 used in the RL2 Paper Report.
25 27-FEB-2004 ssattini 115.7 Modified the c_all_asg cursor
26 to fix the 11510 performance bug#3356512.
27 06-MAY-2004 ssattini 115.8 Modified the c_all_asg cursor
28 and sort_action sql stmt because the RL2
29 Paper report was erroring out with assertion
30 error, changed sort_action same as RL1
31 Register pkg as mentioned in bug#3493075.
32 The 11510 bug#3601976 was not showing the
33 employee in RL2 Paper because that employee
34 had negative RL2 Box values.
35 30-JUL-2004 ssouresr 115.9 Before creating assignment actions we now
36 check to make sure employee has been previously
37 archived
38 22-NOV-2004 ssouresr 115.10 Replaced tables with views for security group
39 15-JUN-2005 ssouresr 115.11 Replaced views with tables in sort_action
40 as this was causing Assertion failure
41 21-JUN-2005 ssouresr 115.12 Security Profile changes to c_first_tax_unit_id
42 13-jul-2005 saurgupt 115.13 Modified function get_primary_address. Cursor csr_address
43 is modified to add country_code in address_line4 to
44 fix #Bug 4131616.
45 04-FEB-2006 ssouresr 115.14 Added code to run the RL2 Paper
46 Report for a single employee,
47 part of enhancement to add
48 'Selection Criterion'
49 parameters to the RL2 SRS Defn.
50 Removed references to hr_soft_coding_keyflex
51 13-NOV-2006 ssmukher 115.15 Added the orderby clause in c_all_asg cursor.
52 05-SEP-2011 rgottipa 115.17 Bug 10399514, introduced new cursores
53 c_all_asg_range, c_single_asg_range and
54 c_all_asg_in_asgset_range. These will be
55 called if RANGE_PERSON_ID is enabled.
56 14-SEP-2011 sneelapa 115.18 Bug 12965359, Modified order of the variables
57 in fetch clause for CURSORs in ACTION_CREATION
58 procedure.
59 31-Dec-2012 rgottipa 115.19 Bug 15886428, Done changes to support print
60 terminate employees and Self Service
61 'paper' option.
62 11-Jan-2013 rgottipa 115.20 If CURSOR c_get_asg_id_term is returning no
63 rows then exit from block instead of erroring.
64 25-Jan-2013 rgottipa 115.21 Bug 16208287, 'paper' option should not affect
65 the register report.
66 30-Apr-2013 rgottipa 115.22 Bug 16730209, using dummy for loop to exit
67 if no data found in CURSOR c_get_asg_id_term.
68 ----------------------------------- range_cursor -----------------------------
69 */
70
71 procedure range_cursor (pactid in number, sqlstr out nocopy varchar2) is
72 l_payroll_id number;
73 leg_param pay_payroll_actions.legislative_parameters%type;
74 l_taxyear varchar2(100);
75 l_pre_org_id varchar2(100);
76
77 begin
78 --hr_utility.trace_on('Y','RL2');
79 hr_utility.trace('begining of range_cursor 1 ');
80
81 select legislative_parameters
82 into leg_param
83 from pay_payroll_actions ppa
84 where ppa.payroll_action_id = pactid;
85
86 l_taxyear := '''' || pay_ca_rl2_reg.get_parameter('TAX_YEAR',leg_param) || '''';
87 l_pre_org_id := '''' || pay_ca_rl2_reg.get_parameter('PRE_ORGANIZATION_ID',leg_param) || '''';
88
89 sqlstr := 'select distinct to_number(paa_arch.serial_number)
90 from pay_action_information pai1,
91 pay_action_information pai2,
92 pay_payroll_actions ppa_reg,
93 pay_payroll_actions ppa_arch,
94 pay_assignment_actions paa_arch
95 where ppa_reg.payroll_action_id = :payroll_action_id
96 and pai1.action_context_type = ''PA''
97 and pai1.action_information1 = ''RL2''
98 and pai1.action_information_category = ''CAEOY TRANSMITTER INFO''
99 and pai1.action_information8 = nvl(' ||l_taxyear ||', pai1.action_information8)
100 and pai1.action_information27 = nvl(' ||l_pre_org_id || ',pai1.action_information27)
101 and pai2.action_context_type = ''AAP''
102 and pai2.action_information_category = ''CAEOY RL2 EMPLOYEE INFO''
103 and ppa_arch.payroll_action_id = pai1.action_context_id
104 and ppa_arch.payroll_action_id = paa_arch.payroll_action_id
105 and paa_arch.assignment_action_id = pai2.action_context_id
106 and paa_arch.action_status = ''C''
107 and paa_arch.serial_number = nvl(pay_ca_rl2_reg.get_parameter(''PER_ID'',ppa_reg.legislative_parameters),
108 paa_arch.serial_number)
109 order by to_number(paa_arch.serial_number)';
110
111 hr_utility.trace('End of range_cursor 2 ');
112 end range_cursor;
113 /*
114 -------------------------------- action_creation ----------------------------------
115 */
116
117 procedure action_creation(pactid in number,
118 stperson in number,
119 endperson in number,
120 chunk in number) is
121
122 lockingactid number;
123 l_asg_id number;
124 l_asg_set_id number;
125 l_tax_unit_id number;
126 l_year varchar2(4);
127 l_primary_asg number;
128 l_bus_group_id number;
129 l_person_id number;
130 l_prev_person_id number;
131 l_year_start date;
132 l_year_end date;
133 l_pre_org_id varchar2(30);
134 l_prev_pre_org_id varchar2(30);
135 l_pre_organization_id number;
136 l_rlreg_pre_org_id varchar2(30);
137 l_effective_date date;
138 l_report_type varchar2(80);
139 l_legislative_parameters pay_payroll_actions.legislative_parameters%TYPE;
140 lv_serial_number varchar2(30);
141 ln_arch_asgact_id number;
142 ln_arch_pact_id number;
143 lv_per_id varchar2(30);
144
145 -- Variables declared for bug 10399514
146 l_person_on boolean ;
147 l_report_cat pay_payroll_actions.report_category%type;
148 l_state pay_payroll_actions.report_qualifier%type;
149 l_report_format pay_report_format_mappings_f.report_format%type;
150 -- Variables declared for bug 10399514
151
152 l_print_term varchar2(1);
153 l_effective_end_date date;
154
155 /* For performance: getting all Prov Reporting Est org ids from
156 legislative parameter of pay_payroll_actions for RL2 archiver
157 for the given year within same business group.
158 */
159 cursor c_all_pres is
160 select pycadar_pkg.get_parameter('PRE_ORGANIZATION_ID',
161 ppa.legislative_parameters )
162 from pay_payroll_actions ppa
163 where ppa.report_type = 'RL2'
164 and ppa.report_qualifier = 'CAEOYRL2'
165 and ppa.report_category = 'ARCHIVE'
166 and ppa.effective_date = l_year_end
167 and ppa.start_date = l_year_start
168 and ppa.business_group_id = l_bus_group_id
169 and ppa.action_status = 'C';
170
171 cursor c_archived_person_info (cp_person_id in number,
172 cp_assignment_id in number,
173 cp_pre_org_id in varchar2) is
174 select paa.assignment_action_id,
175 ppa.payroll_action_id
176 from pay_payroll_actions ppa,
177 pay_assignment_actions paa
178 where ppa.report_type = 'RL2'
179 and ppa.report_qualifier = 'CAEOYRL2'
180 and ppa.report_category = 'ARCHIVE'
181 and ppa.effective_date = l_year_end
182 and ppa.start_date = l_year_start
183 and ppa.business_group_id = l_bus_group_id
184 and ppa.action_status = 'C'
185 and ppa.payroll_action_id = paa.payroll_action_id
186 and paa.serial_number = to_char(cp_person_id)
187 and pycadar_pkg.get_parameter('PRE_ORGANIZATION_ID',
188 ppa.legislative_parameters) = cp_pre_org_id
189 and paa.assignment_id = cp_assignment_id;
190
191 cursor c_get_asg_id (p_person_id number) is
192 select paf.assignment_id,
193 paf.effective_end_date
194 from per_assignments_f paf
195 where person_id = p_person_id
196 and primary_flag = 'Y'
197 and assignment_type = 'E'
198 and paf.effective_start_date <= l_year_end
199 and paf.effective_end_date >= l_year_start
200 order by assignment_id desc,paf.effective_end_date desc;
201
202 --Changes for bug 15886428 starts
203 cursor c_get_asg_id_term (p_person_id number) is
204 select paf.assignment_id,
205 paf.effective_end_date
206 from per_assignments_f paf,
207 per_periods_of_service pds
208 where paf.person_id = p_person_id
209 and paf.primary_flag = 'Y'
210 and paf.assignment_type = 'E'
211 and paf.effective_start_date <= l_year_end
212 and paf.effective_end_date >= l_year_start
213 and pds.actual_termination_date is not null
214 and pds.period_of_service_id = paf.period_of_service_id
215 order by assignment_id desc,paf.effective_end_date desc;
216 --Changes for bug 15886428 ends
217
218
219 cursor c_first_tax_unit_id (l_pre_org_id varchar2) is
220 select distinct hoi.organization_id
221 from hr_organization_information hoi,
222 hr_all_organization_units hou
223 where hou.business_group_id = l_bus_group_id
224 and hou.organization_id = hoi.organization_id
225 and hoi.org_information_context = 'Canada Employer Identification'
226 and hoi.org_information2 = l_pre_org_id
227 and hoi.org_information5 = 'T4A/RL2';
228
229 cursor c_all_asg (l_year_start date,
230 l_year_end date) is
231 select distinct paa.assignment_id,
232 to_number(paa.serial_number)
233 from pay_payroll_actions ppa,
234 pay_assignment_actions paa
235 where ppa.report_type = 'RL2'
236 and ppa.report_qualifier = 'CAEOYRL2'
237 and ppa.report_category = 'ARCHIVE'
238 and ppa.effective_date = l_year_end
239 and ppa.start_date = l_year_start
240 and ppa.business_group_id = l_bus_group_id
241 and ppa.action_status = 'C'
242 and ppa.payroll_action_id = paa.payroll_action_id
243 and to_number(paa.serial_number) between stperson and endperson
244 order by to_number(paa.serial_number);
245
246 -- Added for Bug# 10399514
247 -- Used when RANGE_PERSON_ID functionality is available
248 cursor c_all_asg_range(l_year_start date
249 , l_year_end date ) is
250 /* Commented for bug 12965359
251 select distinct to_number(paa.serial_number),
252 paa.assignment_id assignment_id
253 */
254 select distinct paa.assignment_id assignment_id,
255 to_number(paa.serial_number)
256 from pay_payroll_actions ppa,
257 pay_assignment_actions paa,
258 pay_population_ranges ppr
259 where ppa.report_type = 'RL2'
260 and ppa.report_qualifier = 'CAEOYRL2'
261 and ppa.report_category = 'ARCHIVE'
262 and ppa.effective_date = l_year_end
263 and ppa.start_date = l_year_start
264 and ppa.business_group_id = l_bus_group_id
265 and ppa.action_status = 'C'
266 and ppa.payroll_action_id = paa.payroll_action_id
267 --and to_number(paa.serial_number) between stperson and endperson
268 and ppr.payroll_action_id = pactid
269 and ppr.chunk_number = chunk
270 and ppr.person_id = to_number(paa.serial_number)
271 order by to_number(paa.serial_number);
272
273
274 cursor c_single_asg (l_year_start date,
275 l_year_end date,
276 l_per_id varchar2) is
277 select distinct paa.assignment_id,
278 to_number(paa.serial_number)
279 from pay_payroll_actions ppa,
280 pay_assignment_actions paa
281 where ppa.report_type = 'RL2'
282 and ppa.report_qualifier = 'CAEOYRL2'
283 and ppa.report_category = 'ARCHIVE'
284 and ppa.effective_date = l_year_end
285 and ppa.start_date = l_year_start
286 and ppa.business_group_id = l_bus_group_id
287 and ppa.action_status = 'C'
288 and ppa.payroll_action_id = paa.payroll_action_id
289 and to_number(paa.serial_number) between stperson and endperson
290 and paa.serial_number = l_per_id;
291
292
293 /* Will be used only if Assignment Set is passed for RL2 reports */
294
295 cursor c_all_asg_in_asgset(l_year_start date,
296 l_year_end date) is
297 select distinct paa.assignment_id,
298 to_number(paa.serial_number)
299 from pay_payroll_actions ppa,
300 pay_assignment_actions paa
301 where ppa.report_type = 'RL2'
302 and ppa.report_qualifier = 'CAEOYRL2'
303 and ppa.report_category = 'ARCHIVE'
304 and ppa.effective_date = l_year_end
305 and ppa.start_date = l_year_start
306 and ppa.business_group_id = l_bus_group_id
307 and ppa.action_status = 'C'
308 and ppa.payroll_action_id = paa.payroll_action_id
309 and to_number(paa.serial_number) between stperson and endperson
310 and exists (select 1
311 from hr_assignment_set_amendments hasa,
312 per_assignments_f paf
313 where hasa.assignment_set_id = l_asg_set_id
314 and upper(hasa.include_or_exclude) = 'I'
315 and hasa.assignment_id = paf.assignment_id
316 and paf.person_id = to_number(paa.serial_number));
317
318 -- Added for Bug# 10399514
319 -- Used when RANGE_PERSON_ID functionality is available
320 cursor c_all_asg_in_asgset_range(l_year_start date
321 ,l_year_end date ) is
322 /* Commented for bug 12965359
323 select distinct to_number(paa.serial_number),
324 paa.assignment_id assignment_id
325 */
326 select distinct paa.assignment_id assignment_id,
327 to_number(paa.serial_number)
328 from pay_payroll_actions ppa,
329 pay_assignment_actions paa,
330 pay_population_ranges ppr
331 where ppa.report_type = 'RL2'
332 and ppa.report_qualifier = 'CAEOYRL2'
333 and ppa.report_category = 'ARCHIVE'
334 and ppa.effective_date = l_year_end
335 and ppa.start_date = l_year_start
336 and ppa.business_group_id = l_bus_group_id
337 and ppa.action_status = 'C'
338 and ppa.payroll_action_id = paa.payroll_action_id
339 --and to_number(paa.serial_number) between stperson and endperson
340 and ppr.payroll_action_id = pactid
341 and ppr.chunk_number = chunk
342 and ppr.person_id = to_number(paa.serial_number)
343 and exists (select 1
344 from hr_assignment_set_amendments hasa,
345 per_assignments_f paf
346 where hasa.assignment_set_id = l_asg_set_id
347 and upper(hasa.include_or_exclude) = 'I'
348 and hasa.assignment_id = paf.assignment_id
349 and paf.person_id = to_number(paa.serial_number))
350 order by 1,2;
351
352 begin
353 --hr_utility.trace_on('Y','RL2PAPER');
354 hr_utility.set_location('procpyr',1);
355 hr_utility.trace('begining of action creation 1'||to_char(pactid));
356
357 /* get report type and effective date */
358
359 select effective_date,
360 report_type,
361 -- Added for bug 10399514
362 report_qualifier,
363 report_category,
364 -- Added for bug 10399514
365 business_group_id,
366 legislative_parameters
367 into l_effective_date,
368 l_report_type,
369 -- Added for bug 10399514
370 l_state,
371 l_report_cat,
372 -- Added for bug 10399514
373 l_bus_group_id,
374 l_legislative_parameters
375 from pay_payroll_actions
376 where payroll_action_id = pactid;
377
378 hr_utility.trace('legislative parameters is '||l_legislative_parameters);
379
380 l_year := pay_ca_rl2_reg.get_parameter('TAX_YEAR',l_legislative_parameters);
381 l_year_start := trunc(to_date(l_year,'YYYY'), 'Y');
382 l_year_end := add_months(trunc(to_date(l_year,'YYYY'), 'Y'),12) - 1;
383 l_asg_set_id := pay_ca_rl2_reg.get_parameter('ASG_SET_ID',l_legislative_parameters);
384 lv_per_id := pay_ca_rl2_reg.get_parameter('PER_ID',l_legislative_parameters);
385 l_print_term := pay_ca_rl2_reg.get_parameter('PRINT_TERM',l_legislative_parameters);
386
387 l_rlreg_pre_org_id := pay_ca_rl2_reg.get_parameter('PRE_ORGANIZATION_ID',l_legislative_parameters);
388
389 if l_rlreg_pre_org_id is NULL then
390 open c_all_pres;
391 hr_utility.trace('else condition after open c_all_pres c_all_asg cursor 6 ');
392 else
393 l_pre_org_id := l_rlreg_pre_org_id;
394 hr_utility.trace('begining of if condition 5 '||l_pre_org_id);
395 end if;
396
397 -- Code modification for bug 10399514 starts here
398 /* Initializing variable */
399 l_person_on := FALSE ;
400
401 Begin
402 select report_format
403 into l_report_format
404 from pay_report_format_mappings_f
405 where report_type = l_report_type
406 and report_qualifier = l_state
407 and report_category = l_report_cat ;
408 Exception
409 When Others Then
410 l_report_format := Null ;
411 End ;
412
413 l_person_on := pay_ac_utility.range_person_on( p_report_type => l_report_type,
414 p_report_format => l_report_format,
415 p_report_qualifier => l_state,
416 p_report_category => l_report_cat) ;
417
418 if l_rlreg_pre_org_id is NULL then
419
420 loop
421 fetch c_all_pres into l_pre_org_id;
422 hr_utility.trace('Begining of else loop for c_all_pres 7 '|| l_pre_org_id);
423
424 exit when c_all_pres%notfound;
425
426 open c_first_tax_unit_id(l_pre_org_id);
427 fetch c_first_tax_unit_id into l_tax_unit_id;
428
429 if c_first_tax_unit_id%FOUND then
430 close c_first_tax_unit_id;
431
432 l_pre_organization_id := to_number(l_pre_org_id);
433
434 if l_asg_set_id is not null then
435 if l_person_on then
436 hr_utility.trace('opening c_all_asg_in_asgset_range CURSOR');
437 open c_all_asg_in_asgset_range(l_year_start , l_year_end);
438 else
439 hr_utility.trace('opening c_all_asg_in_asgset CURSOR');
440 open c_all_asg_in_asgset(l_year_start , l_year_end);
441 end if;
442 elsif lv_per_id is not null then
443 hr_utility.trace('opening c_single_asg CURSOR');
444 open c_single_asg(l_year_start,l_year_end, lv_per_id);
445 else
446 if l_person_on then
447 hr_utility.trace('opening c_all_asg_range CURSOR');
448 open c_all_asg_range(l_year_start, l_year_end);
449 else
450 hr_utility.trace('opening c_all_asg CURSOR');
451 open c_all_asg(l_year_start, l_year_end);
452 end if;
453 end if;
454
455 loop
456 if l_asg_set_id is not null then
457 if l_person_on then
458 -- Commented for bug 12965359
459 -- fetch c_all_asg_in_asgset_range into l_person_id, l_asg_id;
460
461 fetch c_all_asg_in_asgset_range into l_asg_id, l_person_id;
462 exit when c_all_asg_in_asgset_range%notfound;
463 else
464 -- Commented for bug 12965359
465 -- fetch c_all_asg_in_asgset into l_person_id, l_asg_id;
466
467 fetch c_all_asg_in_asgset into l_asg_id, l_person_id;
468 exit when c_all_asg_in_asgset%notfound;
469 end if;
470 elsif lv_per_id is not null then
471 -- Commented for bug 12965359
472 -- fetch c_single_asg into l_person_id, l_asg_id;
473
474 fetch c_single_asg into l_asg_id, l_person_id;
475 exit when c_single_asg%notfound;
476 else
477 if l_person_on then
478 -- Commented for bug 12965359
479 -- fetch c_all_asg_range into l_person_id, l_asg_id;
480
481 fetch c_all_asg_range into l_asg_id, l_person_id;
482 exit when c_all_asg_range%notfound;
483 else
484 -- Commented for bug 12965359
485 -- fetch c_all_asg into l_person_id, l_asg_id;
486
487 fetch c_all_asg into l_asg_id, l_person_id;
488 exit when c_all_asg%notfound;
489 end if;
490 end if;
491
492 if (l_person_id = l_prev_person_id and
493 l_pre_org_id = l_prev_pre_org_id) then
494
495 hr_utility.trace('Not creating assignment action');
496
497 else
498
499 /* Get the primary assignment as the primary
500 assignment is the assignment_id that is
501 always archived. Must check against this
502 assignment when checking for archived person */
503
504 /* using this dummy loop to exit if no data found in
505 CURSOR c_get_asg_id_term */
506 for i in 1..1 loop
507 if nvl(l_print_term,'N') = 'Y' then
508 open c_get_asg_id_term(l_person_id);
509 fetch c_get_asg_id_term into l_primary_asg,l_effective_end_date;
510 if c_get_asg_id_term%NOTFOUND then
511 close c_get_asg_id_term;
512 exit;
513 else
514 close c_get_asg_id_term;
515 end if;
516 else
517 open c_get_asg_id(l_person_id);
518 fetch c_get_asg_id into l_primary_asg,l_effective_end_date;
519 if c_get_asg_id%NOTFOUND then
520 close c_get_asg_id;
521 hr_utility.raise_error;
522 else
523 close c_get_asg_id;
524 end if;
525 end if;
526
527 if (l_report_type = 'RL2PAPERPDF' and nvl(pay_us_employee_payslip_web.get_doc_eit('RL2PDF',
528 'PRINT',
529 'ASSIGNMENT',
530 l_primary_asg,
531 l_effective_end_date),'Y') = 'Y') or l_report_type <> 'RL2PAPERPDF' then
532
533 open c_archived_person_info (l_person_id,
534 l_primary_asg,
535 l_pre_org_id);
536 fetch c_archived_person_info
537 into ln_arch_asgact_id,
538 ln_arch_pact_id;
539 if c_archived_person_info%notfound then
540 hr_utility.trace('No Archived Person Found');
541 else
542
543 select pay_assignment_actions_s.nextval
544 into lockingactid
545 from dual;
546
547 hr_nonrun_asact.insact(lockingactid,
548 l_primary_asg,
549 pactid,
550 chunk,
551 l_pre_organization_id);
552
553 hr_utility.trace('after hr_nonrun_asact.insact'||to_char(l_asg_id));
554
555 lv_serial_number := 'QC' ||lpad(to_char(ln_arch_asgact_id),14,0)||
556 lpad(to_char(ln_arch_pact_id),14,0);
557
558 hr_utility.trace('lv_serial_number :' ||lv_serial_number);
559
560 update pay_assignment_actions paa
561 set paa.serial_number = lv_serial_number
562 where paa.assignment_action_id = lockingactid;
563
564 l_prev_person_id := l_person_id;
565 l_prev_pre_org_id := l_pre_org_id;
566
567 end if;
568 close c_archived_person_info;
569 end if; --pay_us_employee_payslip_web.get_doc_eit
570 end loop;
571 end if;
572
573 end loop;
574
575 if l_asg_set_id is not null then
576 if l_person_on then
577 close c_all_asg_in_asgset_range;
578 else
579 close c_all_asg_in_asgset;
580 end if;
581 elsif lv_per_id is not null then
582 close c_single_asg;
583 else
584 if l_person_on then
585 close c_all_asg_range;
586 else
587 close c_all_asg;
588 end if;
589 end if;
590
591 else
592 close c_first_tax_unit_id;
593 hr_utility.trace('No GRE for this PRE ');
594
595 end if;
596
597 end loop;
598
599 close c_all_pres;
600 else
601 open c_first_tax_unit_id(l_pre_org_id);
602 fetch c_first_tax_unit_id into l_tax_unit_id;
603
604 hr_utility.trace('In else');
605 if c_first_tax_unit_id%FOUND then
606 close c_first_tax_unit_id;
607
608 l_pre_organization_id := to_number(l_pre_org_id);
609
610 if l_asg_set_id is not null then
611 if l_person_on then
612 hr_utility.trace('opening c_all_asg_in_asgset_range CURSOR');
613 open c_all_asg_in_asgset_range(l_year_start , l_year_end);
614 else
615 hr_utility.trace('opening c_all_asg_in_asgset CURSOR');
616 open c_all_asg_in_asgset(l_year_start , l_year_end);
617 end if;
618 elsif lv_per_id is not null then
619 hr_utility.trace('opening c_single_asg CURSOR');
620 open c_single_asg(l_year_start,l_year_end, lv_per_id);
621 else
622 if l_person_on then
623 hr_utility.trace('opening c_all_asg_range CURSOR');
624 open c_all_asg_range(l_year_start, l_year_end);
625 else
626 hr_utility.trace('opening c_all_asg CURSOR');
627 open c_all_asg(l_year_start, l_year_end);
628 end if;
629 end if;
630
631 loop
632
633 if l_asg_set_id is not null then
634 if l_person_on then
635 -- Commented for bug 12965359
636 -- fetch c_all_asg_in_asgset_range into l_person_id, l_asg_id;
637
638 fetch c_all_asg_in_asgset_range into l_asg_id, l_person_id;
639 exit when c_all_asg_in_asgset_range%notfound;
640 else
641 -- Commented for bug 12965359
642 -- fetch c_all_asg_in_asgset into l_person_id, l_asg_id;
643
644 fetch c_all_asg_in_asgset into l_asg_id, l_person_id;
645
646 exit when c_all_asg_in_asgset%notfound;
647 end if;
648 elsif lv_per_id is not null then
649 -- Commented for bug 12965359
650 -- fetch c_single_asg into l_person_id, l_asg_id;
651
652 fetch c_single_asg into l_asg_id, l_person_id;
653 exit when c_single_asg%notfound;
654 else
655 if l_person_on then
656 -- Commented for bug 12965359
657 -- fetch c_all_asg_range into l_person_id, l_asg_id;
658
659 fetch c_all_asg_range into l_asg_id, l_person_id;
660 exit when c_all_asg_range%notfound;
661 else
662 -- Commented for bug 12965359
663 -- fetch c_all_asg into l_person_id, l_asg_id;
664
665 fetch c_all_asg into l_asg_id, l_person_id;
666 exit when c_all_asg%notfound;
667 end if;
668 end if;
669
670 if (l_person_id = l_prev_person_id and
671 l_pre_org_id = l_prev_pre_org_id) then
672
673 hr_utility.trace('Not creating assignment action');
674
675 else
676
677 /* Get the primary assignment as the primary
678 assignment is the assignment_id that is
679 always archived. Must check against this
680 assignment when checking for archived person */
681
682 /* using this dummy loop to exit if no data found in
683 CURSOR c_get_asg_id_term */
684 for i in 1..1 loop
685 if nvl(l_print_term,'N') = 'Y' then
686 open c_get_asg_id_term(l_person_id);
687 fetch c_get_asg_id_term into l_primary_asg,l_effective_end_date;
688 if c_get_asg_id_term%NOTFOUND then
689 close c_get_asg_id_term;
690 exit;
691 else
692 close c_get_asg_id_term;
693 end if;
694 else
695 open c_get_asg_id(l_person_id);
696 fetch c_get_asg_id into l_primary_asg,l_effective_end_date;
697 if c_get_asg_id%NOTFOUND then
698 close c_get_asg_id;
699 hr_utility.raise_error;
700 else
701 close c_get_asg_id;
702 end if;
703 end if;
704
705 if (l_report_type = 'RL2PAPERPDF' and nvl(pay_us_employee_payslip_web.get_doc_eit('RL2PDF',
706 'PRINT',
707 'ASSIGNMENT',
708 l_primary_asg,
709 l_effective_end_date),'Y') = 'Y') or l_report_type <> 'RL2PAPERPDF' then
710
711 open c_archived_person_info(l_person_id,
712 l_primary_asg,
713 l_pre_org_id);
714 fetch c_archived_person_info
715 into ln_arch_asgact_id,
716 ln_arch_pact_id;
717 if c_archived_person_info%notfound then
718 hr_utility.trace('No Archived Person Found');
719 else
720
721 select pay_assignment_actions_s.nextval
722 into lockingactid
723 from dual;
724
725 hr_nonrun_asact.insact(lockingactid,
726 l_primary_asg,
727 pactid,
728 chunk,
729 l_pre_organization_id);
730
731 hr_utility.trace('after calling hr_nonrun_asact.insact '||to_char(lockingactid));
732
733 lv_serial_number := 'QC' ||lpad(to_char(ln_arch_asgact_id),14,0)||
734 lpad(to_char(ln_arch_pact_id),14,0);
735
736 hr_utility.trace('lv_serial_number :' ||lv_serial_number);
737
738 update pay_assignment_actions paa
739 set paa.serial_number = lv_serial_number
740 where paa.assignment_action_id = lockingactid;
741
742 l_prev_person_id := l_person_id;
743 l_prev_pre_org_id := l_pre_org_id;
744
745 end if;
746 close c_archived_person_info;
747 end if; --pay_us_employee_payslip_web.get_doc_eit
748 end loop;
749 end if;
750
751 end loop;
752
753 if l_asg_set_id is not null then
754 if l_person_on then
755 close c_all_asg_in_asgset_range;
756 else
757 close c_all_asg_in_asgset;
758 end if;
759 elsif lv_per_id is not null then
760 close c_single_asg;
761 else
762 if l_person_on then
763 close c_all_asg_range;
764 else
765 close c_all_asg;
766 end if;
767 end if;
768
769 hr_utility.trace('End of cursor c_all_asg 12');
770 else
771 close c_first_tax_unit_id;
772 hr_utility.trace('No GRE for this PRE ');
773 end if;
774 end if;
775 end action_creation;
776 /*
777 ---------------------------------- sort_action ----------------------------------
778 */
779 procedure sort_action
780 (payactid in varchar2, /* payroll action id */
781 sqlstr in out nocopy varchar2, /* string holding the sql statement */
782 len out nocopy number) /* length of the sql string */
783 is
784 begin
785 hr_utility.trace('Start of Sort_Action 1');
786
787 sqlstr := 'select paa1.rowid /* we need the row id of the assignment actions that are created by PYUGEN */
788 from hr_all_organization_units hou1,
789 hr_all_organization_units hou,
790 hr_locations_all loc,
791 per_all_people_f ppf,
792 per_all_assignments_f paf,
793 pay_assignment_actions paa1,
794 pay_payroll_actions ppa1
795 where ppa1.payroll_action_id = :pactid
796 and paa1.payroll_action_id = ppa1.payroll_action_id
797 and paa1.assignment_id = paf.assignment_id
798 and paf.assignment_type = ''E''
799 and paf.primary_flag = ''Y''
800 and paf.business_group_id = ppa1.business_group_id
801 and ppa1.effective_date >= paf.effective_start_date
802 and hou.organization_id = paa1.tax_unit_id
803 and loc.location_id = paf.location_id
804 and hou1.organization_id = paf.organization_id
805 and ppf.person_id = paf.person_id
806 and ppa1.effective_date between
807 ppf.effective_start_date and ppf.effective_end_date
808 and paf.effective_end_date = (
809 select max(paaf2.effective_end_date)
810 from per_all_assignments_f paaf2
811 where paaf2.assignment_id = paf.assignment_id
812 and paaf2.effective_start_date <= ppa1.effective_date
813 )
814 order by
815 decode(pay_ca_rl2_reg.get_parameter(''P_S1'',ppa1.legislative_parameters), ''RL1_PRE'',hou.name,
816 ''RL1_ORG'',hou1.name,
817 ''RL1_LOC'',loc.location_code,null)
818 ,decode(pay_ca_rl2_reg.get_parameter(''P_S2'',ppa1.legislative_parameters), ''RL1_PRE'',hou.name,
819 ''RL1_ORG'',hou1.name,
820 ''RL1_LOC'',loc.location_code,null)
821 ,decode(pay_ca_rl2_reg.get_parameter(''P_S3'',ppa1.legislative_parameters), ''RL1_PRE'',hou.name,
822 ''RL1_ORG'',hou1.name,
823 ''RL1_LOC'',loc.location_code,null)
824 ,ppf.last_name,ppf.first_name';
825
826 len := length(sqlstr); -- return the length of the string.
827 hr_utility.trace('End of Sort_Action 2');
828 end sort_action;
829 /*
830 ------------------------------ get_parameter -------------------------------
831 */
832 function get_parameter(name in varchar2,
833 parameter_list varchar2) return varchar2
834 is
835 start_ptr number;
836 end_ptr number;
837 token_val pay_payroll_actions.legislative_parameters%type;
838 par_value pay_payroll_actions.legislative_parameters%type;
839 begin
840
841 token_val := name||'=';
842
843 start_ptr := instr(parameter_list, token_val) + length(token_val);
844 end_ptr := instr(parameter_list, ' ',start_ptr);
845
846 /* if there is no spaces use then length of the string */
847 if end_ptr = 0 then
848 end_ptr := length(parameter_list)+1;
849 end if;
850
851 /* Did we find the token */
852 if instr(parameter_list, token_val) = 0 then
853 par_value := NULL;
854 else
855 par_value := substr(parameter_list, start_ptr, end_ptr - start_ptr);
856 end if;
857
858 return par_value;
859
860 end get_parameter;
861
862 function get_label(p_lookup_type in varchar2,
863 p_lookup_code in varchar2) return varchar2 is
864
865 l_meaning hr_lookups.meaning%TYPE;
866
867 CURSOR cur_get_meaning IS
868 SELECT hl.meaning
869 FROM hr_lookups hl
870 WHERE hl.lookup_type = p_lookup_type AND
871 hl.lookup_code = p_lookup_code;
872
873 BEGIN
874
875 OPEN cur_get_meaning;
876 FETCH cur_get_meaning
877 INTO l_meaning;
878 if cur_get_meaning%NOTFOUND then
879 l_meaning := NULL;
880 end if;
881
882 CLOSE cur_get_meaning;
883
884 RETURN l_meaning;
885
886 END get_label; -- get_label
887
888 /* To get primary address of an employee */
889 /* Address line 1 to 3 are normal address lines */
890 /* Address Line 4 = City + Province Code + Postal Code */
891 /* Address Line 5 = Country Code */
892 /* Address Line 6 = Country Name */
893 /* Address Line 7 = Town or City */
894 /* Address Line 8 = Province Code */
895 /* Address Line 9 = Postal Code */
896
897 function get_primary_address(p_person_id in Number,
898 p_effective_date in date
899 ) return PrimaryAddress IS
900
901 cursor csr_address( p_person_id in number,
902 p_effective_date in date) is
903 select addr.address_line1
904 ,addr.address_line2
905 ,addr.address_line3
906 ,rtrim(substr(addr.town_or_city,1,23)) ||' '||
907 decode(addr.country, 'CA', addr.region_1, 'US', addr.region_2,
908 addr.region_1 )
909 ||' '|| addr.country -- Bug 4134616
910 ||' '|| addr.postal_code address_line4
911 ,addr.country address_line5 -- Country Code
912 ,country.territory_short_name address_line6 -- Country Name
913 ,addr.town_or_city Town_or_City
914 ,decode(addr.country, 'CA', addr.region_1,
915 'US', addr.region_2, addr.region_1 ) Province
916 ,addr.postal_code Postal_Code
917 from per_addresses addr
918 ,fnd_territories_vl country
919 where addr.person_id = p_person_id
920 and addr.primary_flag = 'Y'
921 and p_effective_date between
922 addr.date_from and nvl(addr.date_to, p_effective_date)
923 and country.territory_code = addr.country;
924
925 addr PrimaryAddress;
926
927 begin
928
929 open csr_address(p_person_id,p_effective_date);
930 fetch csr_address into addr;
931 close csr_address;
932
933 return addr;
934
935 end get_primary_address;
936
937 end pay_ca_rl2_reg;
[Home] [Help]