1 package body pay_ca_rl1_reg as
2 /* $Header: pycarrrg.pkb 120.5.12020000.4 2013/04/30 10:00:23 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 RL1 Register Multi-Threaded Report
8 --
9 Change List
10 -----------
11 Date Name Vers Description
12 ----------- ---------- ----- -----------------------------------
13 06-JAN-2000 mmukherj 110.0 Created
14 25-JAN-2000 MMukherjee 110.1 Changed the legislative_parameter name
15 from QUEBEC_BUSINESS_NO to QC_ID_NO
16 04-FEB-2000 MMukherjee 110.2 Modified sort code
17 04-FEB-2000 MMukherjee 115.1 Modified sort code to include
18 terminated employees
19
20 21-SEP-2000 VPANDYA 115.2 Modified cursor all_asg commented
21 primaryflag condition
22 02-NOV-2000 VPANDYA 115.3 Modified cursor c_first_tax_unit_id
23 and c_all_asg declaring with parameters.
24 Modified condition from = NULL to is NULL
25 for l_rl1reg_pre_org_id
26 08-DEC-2000 VPANDYA 115.4 Added to_char in subquery of c_all_asg
27 cursor.
28 27-DEC-2001 VPANDYA 115.5 Added function get_rl1_message
29 and dbdrv lines.
30 28-DEC-2001 VPANDYA 115.6 Modified sort_action cursor replace
31 to_date('31-DEC'..with add_months
32 28-DEC-2001 VPANDYA 115.7 Modified function get_rl1_message
33 removed 'DD-MON-YYYY' from
34 to_date( p_emp_dob,..)
35 08-Jan-2002 VPANDYA 115.8 Modified function get_rl1_message
36 in which added one more input
37 parameter p_hire_dt and returning
38 message with hire date.
39 09-MAY-2002 SSattineni 115.9 Fixed the bug#2135545 by
40 modifying action_creation
41 procedure.
42 16-AUG-2002 mmukherj 115.10 Added get_user_entity_id procedure.
43 added these two lines in action_creation
44 procedure.
45 l_uid_caeoy_tax_year := get_user_entity_id('CAEOY_TAXATION_YEAR');
46 l_uid_caeoy_rl1_quebec_bn := get_user_entity_id('CAEOY_RL1_QUEBEC_BN');
47 and used these two variables in c_all_pres cursor. This makes this cursor
48 more performant. The query inside this cursor had been recognised as a
49 query with high cost in 11.5.8.
50
51 16-AUG-2002 vpandya 115.11 Changed c_all_pres for perfoemance 11.5.8,
52 getting Quebec Busi.number from
53 pay_payroll_actions table instead of
54 archiver table.
55 Changed c_all_asg_in_asgset for 11.5.8 perf.
56 added table hr_all_organization_units table
57 to avoid cartesian join.
58 Changed function get_rl1_message, added
59 input parameter p_termination_dt to print it
60 in the message if it is not null.Bug2192914
61 06-NOV-2002 vpandya 115.12 Added function get_primary_address,
62 Changed action_creation procedure to create
63 one assignment action for a person if the
64 person has more than one assignments(multi).
65 06-NOV-2002 vpandya 115.13 Added country in get_primary_address
66 07-NOV-2002 vpandya 115.14 Print country code only in the address
67 instead of Country name(Ref. by LT).
68 08-Nov-2002 vpandya 115.15 Added address_line_6 which returns
69 Country Name where as line 5 returns
70 Country Code.
71 22-Oct-2002 vpandya 115.16 Bug 2681250: changed cursor csr_address
72 of get_primary_address. If country is CA
73 take data from region_1 to get province
74 code and if it is US take data from
75 region_2 to get state code.
76 02-DEC-2002 vpandya 115.17 Added nocopy with out parameter
77 as per GSCC.
78 04-DEC-2002 vpandya 115.18 Changed get_parimary_address function,
79 returns region_1 for province if country is
80 null
81 04-SEP-2003 vpandya 115.19 Changed cursors c_all_asg and
82 c_all_asg_in_asgset to check tax unit id of
83 RL1 with segment1(T4/RL1) and segment11
84 (T4A/RL1) -- Multi GRE Changes.
85 Bug 2633035: stamping organization id of
86 PRE in to tax unit id of asg act.
87 Changed sort_action cursor to use sort
88 options.
89 18-Sep-2003 vpandya 115.20 Fix gscc date conversion error by replacing
90 to_date with fnd_date.canonical_to_date in
91 function get_rl1_message.
92 25-Sep-2003 vpandya 115.21 Change sort action cursor and also changed
93 in c_all_asg c_all_asg_set cursor in
94 action creation. Bug 2633035.
95 04-Nov-2003 ssouresr 115.22 Using pre_organization_id instead of Quebec
96 Business Number. Also updating the serial
97 number on pay_assignment_actions to province
98 archived assignment action and payroll action id
99 11-Feb-2004 ssouresr 115.23 Sort_action query was modified to eliminate dups
100 16-Feb-2004 ssouresr 115.24 Taken out join to hr_locations from assignment
101 cursors and from sort_action
102 03-Mar-2004 ssouresr 115.26 Data is archived against the primary assignment_id
103 however non primary assignments were being
104 compared to the archived assignment_id in the
105 cursor c_archived_person_info. This mismatch
106 resulted in archived employees not being reported.
107 02-Apr-2004 ssattini 115.27 11510 Changes to fix bug#3356512.
108 Modified cursor c_all_asg_in_asgset and
109 c_all_asg in action_creation procedure.
110 17-Apr-2004 ssouresr 115.28 Created new cursor c_single_asg to allow a
111 single assignment to be displayed Bug #3274365
112 03-Sep-2004 ssattini 115.29 Added get_label function to fix
113 bug#3810959.
114 22-NOV-2004 ssouresr 115.30 Replaced tables with views for security group
115 25-FEB-2005 ssmukher 115.31 Added TRUNC function to the date parameter
116 p_effective_date in the csr_address cursor
117 of the function get_primary_address to fix
118 #Bug 4205724
119 15-JUN-2005 ssouresr 115.32 Replaced views with tables in sort_action
120 as this was causing Assertion failure
121 13-JUL-2005 saurgupt 115.33 Modified function get_primary_address. Cursor csr_address
122 is modified to add country_code in address_line4 to
123 fix #Bug 4131616.
124 10-FEB-2006 ssouresr 115.34 Removed references to hr_soft_coding_keyflex
125
126 24-FEB-2009 sneelapa 115.35 Bug 7572889, Modified the CURSORS
127 c_all_asg, c_single_asg and c_all_asg_in_asgset
128 to fetch the data from "pay_payroll_actions"
129 and "pay_assignment_actions" tables.
130 05-Sep-2011 rgottipa 115.36 Bug 10399514, introduced new cursores
131 c_all_asg_range, c_single_asg_range and
132 c_all_asg_in_asgset_range. These will be
133 called if RANGE_PERSON_ID is enabled.
134 31-Dec-2012 rgottipa 115.37 Bug 15886428, Done changes to support print
135 terminate employees and Self Service
136 'paper' option.
137 25-Jan-2013 rgottipa 115.38 Bug 16208287, 'paper' option should not
138 affect the register report outputs.
139 30-Apr-2013 rgottipa 115.39 Bug 16730209, using dummy for loop to exit
140 if no data found in CURSOR c_get_asg_id_term.
141 */
142 /*
143 ----------------------------------- range_cursor -----------------------------
144 */
145
146 function get_user_entity_id(p_user_name varchar2) return number is
147
148 begin
149
150 declare
151
152 cursor cur_user_entity_id is
153 select user_entity_id
154 from ff_database_items
155 where user_name = p_user_name;
156
157 l_user_entity_id ff_database_items.user_entity_id%TYPE;
158
159 begin
160
161 open cur_user_entity_id;
162
163 fetch cur_user_entity_id
164 into l_user_entity_id;
165
166 close cur_user_entity_id;
167
168 return l_user_entity_id;
169
170 end;
171 end;
172
173 procedure range_cursor (pactid in number, sqlstr out nocopy varchar2) is
174 l_payroll_id number;
175 leg_param pay_payroll_actions.legislative_parameters%type;
176
177 begin
178 --hr_utility.trace_on('Y','RL1');
179 hr_utility.trace('begining of range_cursor 1 ');
180 select legislative_parameters
181 into leg_param
182 from pay_payroll_actions ppa
183 where ppa.payroll_action_id = pactid;
184
185
186 /* pay reg code */
187
188 sqlstr := 'select distinct to_number(fai3.value)
189 from ff_archive_items fai1,
190 ff_archive_items fai2,
191 ff_database_items fdi1,
192 ff_database_items fdi2,
193 ff_archive_items fai3,
194 ff_database_items fdi3,
195 pay_payroll_actions ppa,
196 pay_assignment_actions paa
197 where ppa.payroll_action_id = :payroll_action_id
198 and fai1.user_entity_id = fdi1.user_entity_id
199 and fdi1.user_name = ''CAEOY_TAXATION_YEAR''
200 and fai1.value =
201 nvl(pay_ca_rl1_reg.get_parameter(''TAX_YEAR'',
202 ppa.legislative_parameters),fai1.value)
203 and fai2.user_entity_id = fdi2.user_entity_id
204 and fdi2.user_name = ''CAEOY_RL1_PRE_ORG_ID''
205 and fai2.value =
206 nvl(pay_ca_rl1_reg.get_parameter(''PRE_ORGANIZATION_ID'',
207 ppa.legislative_parameters),
208 fai2.value)
209 and fai1.context1 = fai2.context1
210 and paa.payroll_action_id= fai2.context1
211 and paa.assignment_action_id=fai3.context1
212 and fai3.user_entity_id = fdi3.user_entity_id
213 and fdi3.user_name = ''CAEOY_PERSON_ID''
214 and fai3.value = nvl(pay_ca_rl1_reg.get_parameter(''PER_ID'',
215 ppa.legislative_parameters),fai3.value)
216 order by to_number(fai3.value)';
217
218 hr_utility.trace('End of range_cursor 2 ');
219 end range_cursor;
220 /*
221 -------------------------------- action_creation ----------------------------------
222 */
223
224 procedure action_creation(pactid in number,
225 stperson in number,
226 endperson in number,
227 chunk in number) is
228
229 lockingactid number;
230 lockedactid number;
231 l_asg_id number;
232 l_primary_asg number;
233 l_asg_set_id number;
234 l_tax_unit_id number;
235 l_year varchar2(4);
236 l_bus_group_id number;
237 l_year_start date;
238 l_year_end date;
239 l_pre_organization_id varchar2(17);
240 l_rl1reg_pre_org_id varchar2(17);
241 l_effective_date date;
242 l_report_type varchar2(80);
243 l_legislative_parameters pay_payroll_actions.legislative_parameters%TYPE;
244 l_person_id number;
245 l_prev_person_id number;
246 l_prev_pre_organization_id varchar2(17);
247
248 l_pre_org_id number; -- Organization Id of PRE (Prov Reporting Est)
249 lv_serial_number varchar2(30);
250 ln_arch_asgact_id number;
251 ln_arch_pact_id number;
252 lv_per_id varchar2(30);
253
254 -- Variables declared for bug 10399514
255 l_person_on boolean ;
256 l_report_cat pay_payroll_actions.report_category%type;
257 l_state pay_payroll_actions.report_qualifier%type;
258 l_report_format pay_report_format_mappings_f.report_format%type;
259 -- Variables declared for bug 10399514
260
261 l_print_term varchar2(1);
262 l_effective_end_date date;
263
264 /* For performance: getting all pre organization ids from
265 legislative parameter of pay_payroll_actions for RL1 archiver
266 for the given year within same business group.
267 */
268 cursor c_all_pres is
269 select pycadar_pkg.get_parameter('PRE_ORGANIZATION_ID',
270 ppa.legislative_parameters )
271 from pay_payroll_actions ppa
272 where ppa.report_type = 'RL1'
273 and ppa.report_qualifier = 'CAEOYRL1'
274 and ppa.report_category = 'CAEOYRL1'
275 and ppa.effective_date = l_year_end
276 and ppa.start_date = l_year_start
277 and ppa.business_group_id = l_bus_group_id
278 and ppa.action_status = 'C';
279
280 cursor c_archived_person_info (cp_person_id in number,
281 cp_assignment_id in number,
282 cp_pre_org_id in varchar2) is
283 select paa.assignment_action_id,
284 ppa.payroll_action_id
285 from pay_payroll_actions ppa,
286 pay_assignment_actions paa
287 where ppa.report_type = 'RL1'
288 and ppa.report_qualifier = 'CAEOYRL1'
289 and ppa.report_category = 'CAEOYRL1'
290 and ppa.effective_date = l_year_end
291 and ppa.start_date = l_year_start
292 and ppa.business_group_id = l_bus_group_id
293 and ppa.action_status = 'C'
294 and ppa.payroll_action_id = paa.payroll_action_id
295 and paa.serial_number = to_char(cp_person_id)
296 and pycadar_pkg.get_parameter('PRE_ORGANIZATION_ID',
297 ppa.legislative_parameters) = cp_pre_org_id
298 and paa.assignment_id = cp_assignment_id;
299
300 cursor c_first_tax_unit_id (p_pre_org_id varchar2) is
301 select distinct organization_id
302 from hr_organization_information hoi
303 where hoi.org_information_context = 'Canada Employer Identification'
304 and hoi.org_information2 = p_pre_org_id;
305
306 /* 11510 Change to fix bug#3356512, modified cursor c_all_asg */
307 cursor c_all_asg(l_business_group_id number
308 , l_year_start date
309 , l_year_end date ) is
310 select distinct to_number(paa.serial_number),
311 paa.assignment_id assignment_id
312 from pay_payroll_actions ppa,
313 pay_assignment_actions paa
314 where ppa.report_type = 'RL1'
315 and ppa.report_qualifier = 'CAEOYRL1'
316 and ppa.report_category = 'CAEOYRL1'
317 and ppa.effective_date = l_year_end
318 and ppa.start_date = l_year_start
319 and ppa.business_group_id = l_business_group_id
320 and ppa.action_status = 'C'
321 and ppa.payroll_action_id = paa.payroll_action_id
322 and to_number(paa.serial_number) between stperson and endperson
323 order by 1, 2;
324
325 -- Added for Bug# 10399514
326 -- Used when RANGE_PERSON_ID functionality is available
327 cursor c_all_asg_range(l_business_group_id number
328 , l_year_start date
329 , l_year_end date ) is
330 select distinct to_number(paa.serial_number),
331 paa.assignment_id assignment_id
332 from pay_payroll_actions ppa,
333 pay_assignment_actions paa,
334 pay_population_ranges ppr
335 where ppa.report_type = 'RL1'
336 and ppa.report_qualifier = 'CAEOYRL1'
337 and ppa.report_category = 'CAEOYRL1'
338 and ppa.effective_date = l_year_end
339 and ppa.start_date = l_year_start
340 and ppa.business_group_id = l_business_group_id
341 and ppa.action_status = 'C'
342 and ppa.payroll_action_id = paa.payroll_action_id
343 --and to_number(paa.serial_number) between stperson and endperson
344 and ppr.payroll_action_id = pactid
345 and ppr.chunk_number = chunk
346 and ppr.person_id = to_number(paa.serial_number)
347 order by 1, 2;
348
349 cursor c_single_asg (l_business_group_id number
350 ,l_year_start date
351 ,l_year_end date
352 ,l_per_id varchar2) is
353 select distinct to_number(paa.serial_number),
354 paa.assignment_id assignment_id
355 from pay_payroll_actions ppa,
356 pay_assignment_actions paa
357 where ppa.report_type = 'RL1'
358 and ppa.report_qualifier = 'CAEOYRL1'
359 and ppa.report_category = 'CAEOYRL1'
360 and ppa.effective_date = l_year_end
361 and ppa.start_date = l_year_start
362 and ppa.business_group_id = l_business_group_id
363 and ppa.action_status = 'C'
364 and ppa.payroll_action_id = paa.payroll_action_id
365 and to_number(paa.serial_number) between stperson and endperson
366 and paa.serial_number = l_per_id;
367
368 -- Added for Bug# 10399514
369 -- Used when RANGE_PERSON_ID functionality is available
370 cursor c_single_asg_range(l_business_group_id number
371 ,l_year_start date
372 ,l_year_end date
373 ,l_per_id varchar2) is
374 select distinct to_number(paa.serial_number),
375 paa.assignment_id assignment_id
376 from pay_payroll_actions ppa,
377 pay_assignment_actions paa,
378 pay_population_ranges ppr
379 where ppa.report_type = 'RL1'
380 and ppa.report_qualifier = 'CAEOYRL1'
381 and ppa.report_category = 'CAEOYRL1'
382 and ppa.effective_date = l_year_end
383 and ppa.start_date = l_year_start
384 and ppa.business_group_id = l_business_group_id
385 and ppa.action_status = 'C'
386 and ppa.payroll_action_id = paa.payroll_action_id
387 --and to_number(paa.serial_number) between stperson and endperson
388 and ppr.payroll_action_id = pactid
389 and ppr.chunk_number = chunk
390 and ppr.person_id = l_per_id
391 and paa.serial_number = l_per_id;
392
393 /* Added this new cursor to fix bug#2135545 and this
394 will be used only if Assignment Set is passed for RL1 reports */
395 /* 11510 change modified c_all_asg_in_asgset cursor to fix bug#3356512*/
396 cursor c_all_asg_in_asgset(l_business_group_id number
397 ,l_year_start date
398 ,l_year_end date ) is
399 select distinct to_number(paa.serial_number),
400 paa.assignment_id assignment_id
401 from pay_payroll_actions ppa,
402 pay_assignment_actions paa
403 where ppa.report_type = 'RL1'
404 and ppa.report_qualifier = 'CAEOYRL1'
405 and ppa.report_category = 'CAEOYRL1'
406 and ppa.effective_date = l_year_end
407 and ppa.start_date = l_year_start
408 and ppa.business_group_id = l_business_group_id
409 and ppa.action_status = 'C'
410 and ppa.payroll_action_id = paa.payroll_action_id
411 and to_number(paa.serial_number) between stperson and endperson
412 and exists (select 1
413 from hr_assignment_set_amendments hasa,
414 per_assignments_f paf
415 where hasa.assignment_set_id = l_asg_set_id
416 and upper(hasa.include_or_exclude) = 'I'
417 and hasa.assignment_id = paf.assignment_id
418 and paf.person_id = to_number(paa.serial_number))
419 order by 1,2;
420
421
422 -- Added for Bug# 10399514
423 -- Used when RANGE_PERSON_ID functionality is available
424 cursor c_all_asg_in_asgset_range(l_business_group_id number
425 ,l_year_start date
426 ,l_year_end date ) is
427 select distinct to_number(paa.serial_number),
428 paa.assignment_id assignment_id
429 from pay_payroll_actions ppa,
430 pay_assignment_actions paa,
431 pay_population_ranges ppr
432 where ppa.report_type = 'RL1'
433 and ppa.report_qualifier = 'CAEOYRL1'
434 and ppa.report_category = 'CAEOYRL1'
435 and ppa.effective_date = l_year_end
436 and ppa.start_date = l_year_start
437 and ppa.business_group_id = l_business_group_id
438 and ppa.action_status = 'C'
439 and ppa.payroll_action_id = paa.payroll_action_id
440 --and to_number(paa.serial_number) between stperson and endperson
441 and ppr.payroll_action_id = pactid
442 and ppr.chunk_number = chunk
443 and ppr.person_id = to_number(paa.serial_number)
444 and exists (select 1
445 from hr_assignment_set_amendments hasa,
446 per_assignments_f paf
447 where hasa.assignment_set_id = l_asg_set_id
448 and upper(hasa.include_or_exclude) = 'I'
449 and hasa.assignment_id = paf.assignment_id
450 and paf.person_id = to_number(paa.serial_number))
451 order by 1,2;
452
453 cursor c_get_asg_id (p_person_id number) is
454 select paf.assignment_id,
455 paf.effective_end_date
456 from per_assignments_f paf
457 where person_id = p_person_id
458 and primary_flag = 'Y'
459 and assignment_type = 'E'
460 and paf.effective_start_date <= l_year_end
461 and paf.effective_end_date >= l_year_start
462 order by assignment_id desc,paf.effective_end_date desc;
463
464 --Changes for bug 15886428 starts
465 cursor c_get_asg_id_term (p_person_id number) is
466 select paf.assignment_id,
467 paf.effective_end_date
468 from per_assignments_f paf,
469 per_periods_of_service pds
470 where paf.person_id = p_person_id
471 and paf.primary_flag = 'Y'
472 and paf.assignment_type = 'E'
473 and paf.effective_start_date <= l_year_end
474 and paf.effective_end_date >= l_year_start
475 and pds.actual_termination_date is not null
476 and pds.period_of_service_id = paf.period_of_service_id
477 order by assignment_id desc,paf.effective_end_date desc;
478 --Changes for bug 15886428 ends
479
480 begin
481 hr_utility.set_location('procpyr',1);
482 hr_utility.trace('begining of action creation 1'||to_char(pactid));
483
484 /* get report type and effective date */
485
486 select effective_date,
487 report_type,
488 -- Added for bug 10399514
489 report_qualifier,
490 report_category,
491 -- Added for bug 10399514
492 business_group_id,
493 legislative_parameters
494 into l_effective_date,
495 l_report_type,
496 -- Added for bug 10399514
497 l_state,
498 l_report_cat,
499 -- Added for bug 10399514
500 l_bus_group_id,
501 l_legislative_parameters
502 from pay_payroll_actions
503 where payroll_action_id = pactid;
504
505 hr_utility.trace('begining of action creation 2 '||
506 to_char(l_bus_group_id));
507
508 hr_utility.trace('legislative parameters is '||l_legislative_parameters);
509
510 l_year := pay_ca_rl1_reg.get_parameter('TAX_YEAR',l_legislative_parameters);
511 l_year_start := trunc(to_date(l_year,'YYYY'), 'Y');
512 l_year_end := add_months(trunc(to_date(l_year,'YYYY'), 'Y'),12) - 1;
513 l_asg_set_id := pay_ca_rl1_reg.get_parameter('ASG_SET_ID',l_legislative_parameters);
514 lv_per_id := pay_ca_rl1_reg.get_parameter('PER_ID',l_legislative_parameters);
515 l_print_term := pay_ca_rl1_reg.get_parameter('PRINT_TERM',l_legislative_parameters);
516
517 hr_utility.trace('begin action creation '||l_year||to_char(l_year_start)||to_char(l_year_end));
518
519 l_rl1reg_pre_org_id := pay_ca_rl1_reg.get_parameter('PRE_ORGANIZATION_ID',
520 l_legislative_parameters);
521
522 hr_utility.trace('begining of action creation 4 *'||
523 l_rl1reg_pre_org_id||'*');
524
525 if l_rl1reg_pre_org_id is NULL then
526 open c_all_pres;
527 hr_utility.trace('else condition after open c_all_pres '||
528 'c_all_asg cursor 6 ');
529 else
530 l_pre_organization_id := l_rl1reg_pre_org_id;
531 hr_utility.trace('begining of if condition 5 '||l_pre_organization_id);
532 end if;
533
534 -- Code modification for bug 10399514 starts here
535 /* Initializing variable */
536 l_person_on := FALSE ;
537
538 Begin
539 select report_format
540 into l_report_format
541 from pay_report_format_mappings_f
542 where report_type = l_report_type
543 and report_qualifier = l_state
544 and report_category = l_report_cat ;
545 Exception
546 When Others Then
547 l_report_format := Null ;
548 End ;
549
550 l_person_on := pay_ac_utility.range_person_on( p_report_type => l_report_type,
551 p_report_format => l_report_format,
552 p_report_qualifier => l_state,
553 p_report_category => l_report_cat) ;
554
555 if l_rl1reg_pre_org_id is NULL then
556 loop
557 fetch c_all_pres into l_pre_organization_id;
558 hr_utility.trace('Begining of else loop for c_all_pres 7 '||
559 l_pre_organization_id);
560 exit when c_all_pres%notfound;
561 open c_first_tax_unit_id(l_pre_organization_id);
562 fetch c_first_tax_unit_id into l_tax_unit_id;
563
564 if c_first_tax_unit_id%FOUND then
565 close c_first_tax_unit_id;
566
567 l_pre_org_id := to_number(l_pre_organization_id);
568
569 /* Added this validation to fix bug#2135545 */
570 if l_asg_set_id is not null then
571 if l_person_on then
572 hr_utility.trace('opening c_all_asg_in_asgset_range CURSOR');
573 open c_all_asg_in_asgset_range(l_bus_group_id,
574 l_year_start , l_year_end);
575 else
576 hr_utility.trace('opening c_all_asg_in_asgset CURSOR');
577 open c_all_asg_in_asgset(l_bus_group_id,
578 l_year_start , l_year_end);
579 end if;
580 elsif lv_per_id is not null then
581 if l_person_on then
582 hr_utility.trace('opening c_single_asg_range CURSOR');
583 open c_single_asg_range(l_bus_group_id, l_year_start,
584 l_year_end, lv_per_id);
585 else
586 hr_utility.trace('opening c_single_asg CURSOR');
587 open c_single_asg(l_bus_group_id, l_year_start,
588 l_year_end, lv_per_id);
589 end if;
590 else
591 if l_person_on then
592 hr_utility.trace('opening c_all_asg_range CURSOR');
593 open c_all_asg_range(l_bus_group_id,
594 l_year_start, l_year_end);
595 else
596 hr_utility.trace('opening c_all_asg CURSOR');
597 open c_all_asg(l_bus_group_id,
598 l_year_start, l_year_end);
599 end if;
600 end if;
601
602 loop
603 /* Added this validation to fix bug#2135545 */
604 if l_asg_set_id is not null then
605 if l_person_on then
606 fetch c_all_asg_in_asgset_range into l_person_id, l_asg_id;
607 exit when c_all_asg_in_asgset_range%notfound;
608 else
609 fetch c_all_asg_in_asgset into l_person_id, l_asg_id;
610 exit when c_all_asg_in_asgset%notfound;
611 end if;
612 elsif lv_per_id is not null then
613 if l_person_on then
614 fetch c_single_asg_range into l_person_id, l_asg_id;
615 exit when c_single_asg_range%notfound;
616 else
617 fetch c_single_asg into l_person_id, l_asg_id;
618 exit when c_single_asg%notfound;
619 end if;
620 else
621 if l_person_on then
622 fetch c_all_asg_range into l_person_id, l_asg_id;
623 exit when c_all_asg_range%notfound;
624 else
625 fetch c_all_asg into l_person_id, l_asg_id;
626 exit when c_all_asg%notfound;
627 end if;
628 end if;
629
630 hr_utility.trace('Begining of loop for c_all_asg 8 '||
631 to_char(l_asg_id));
632
633 if ( l_person_id = l_prev_person_id and
634 l_pre_organization_id = l_prev_pre_organization_id) then
635
636 hr_utility.trace('Not creating assignment action');
637
638 else
639 /* Get the primary assignment as the primary
640 assignment is the assignment_id that is
641 always archived. Must check against this
642 assignment when checking for archived person */
643 /* using this dummy loop to exit if no data found in
644 CURSOR c_get_asg_id_term */
645 for i in 1..1 loop
646 if nvl(l_print_term,'N') = 'Y' then
647 open c_get_asg_id_term(l_person_id);
648 fetch c_get_asg_id_term into l_primary_asg,l_effective_end_date;
649 if c_get_asg_id_term%NOTFOUND then
650 close c_get_asg_id_term;
651 exit;
652 else
653 close c_get_asg_id_term;
654 end if;
655 else
656 open c_get_asg_id(l_person_id);
657 fetch c_get_asg_id into l_primary_asg,l_effective_end_date;
658 if c_get_asg_id%NOTFOUND then
659 close c_get_asg_id;
660 hr_utility.raise_error;
661 else
662 close c_get_asg_id;
663 end if;
664 end if;
665
666 if (l_report_type = 'RL1PAPERPDF' and nvl(pay_us_employee_payslip_web.get_doc_eit('RL1PDF',
667 'PRINT',
668 'ASSIGNMENT',
669 l_primary_asg,
670 l_effective_end_date),'Y') = 'Y') or l_report_type <> 'RL1PAPERPDF' then
671 open c_archived_person_info (l_person_id,
672 l_primary_asg,
673 l_pre_organization_id);
674 fetch c_archived_person_info
675 into ln_arch_asgact_id,
676 ln_arch_pact_id;
677 if c_archived_person_info%notfound then
678 hr_utility.trace('No Archived Person Found');
679 else
680 select pay_assignment_actions_s.nextval
681 into lockingactid
682 from dual;
683
684 hr_nonrun_asact.insact(lockingactid,
685 -- l_asg_id, -- commented by sneelapa, for bug 7572889
686 l_primary_asg,
687 pactid,
688 chunk,
689 l_pre_org_id);
690
691 hr_utility.trace('in if loop after calling '||
692 'hr_nonrun_asact.insact pkg 9 '||to_char(l_asg_id));
693
694 /* Added this to implement RL1 Register and RL1 Amendment Register
695 using the same report file */
696 lv_serial_number := 'QC' ||lpad(to_char(ln_arch_asgact_id),14,0)||
697 lpad(to_char(ln_arch_pact_id),14,0);
698
699 hr_utility.trace('lv_serial_number :' ||lv_serial_number);
700
701 update pay_assignment_actions paa
702 set paa.serial_number = lv_serial_number
703 where paa.assignment_action_id = lockingactid;
704
705 l_prev_person_id := l_person_id;
706 l_prev_pre_organization_id := l_pre_organization_id;
707
708 end if;
709 close c_archived_person_info;
710 end if; --pay_us_employee_payslip_web.get_doc_eit
711 end loop;
712 end if;
713
714 end loop;
715
716 /* Added this validation to fix bug#2135545 */
717 if l_asg_set_id is not null then
718 if l_person_on then
719 close c_all_asg_in_asgset_range;
720 else
721 close c_all_asg_in_asgset;
722 end if;
723 elsif lv_per_id is not null then
724 if l_person_on then
725 close c_single_asg_range;
726 else
727 close c_single_asg;
728 end if;
729 else
730 if l_person_on then
731 close c_all_asg_range;
732 else
733 close c_all_asg;
734 end if;
735 end if;
736
737 else
738
739 hr_utility.trace('No GRE for this PRE Organization id');
740 hr_utility.raise_error;
741
742 end if;
743 end loop;
744 close c_all_pres;
745 else
746 open c_first_tax_unit_id(l_pre_organization_id);
747 fetch c_first_tax_unit_id into l_tax_unit_id;
748
749 if c_first_tax_unit_id%FOUND then
750 close c_first_tax_unit_id;
751
752 l_pre_org_id := to_number(l_pre_organization_id);
753
754 /* Added this validation to fix bug#2135545 */
755 if l_asg_set_id is not null then
756 if l_person_on then
757 hr_utility.trace('opening c_all_asg_in_asgset_range CURSOR');
758 open c_all_asg_in_asgset_range(l_bus_group_id,
759 l_year_start , l_year_end);
760 else
761 hr_utility.trace('opening c_all_asg_in_asgset CURSOR');
762 open c_all_asg_in_asgset(l_bus_group_id,
763 l_year_start , l_year_end);
764 end if;
765 elsif lv_per_id is not null then
766 if l_person_on then
767 hr_utility.trace('opening c_single_asg_range CURSOR');
768 open c_single_asg_range(l_bus_group_id, l_year_start,
769 l_year_end, lv_per_id);
770 else
771 hr_utility.trace('opening c_single_asg CURSOR');
772 open c_single_asg(l_bus_group_id, l_year_start,
773 l_year_end, lv_per_id);
774 end if;
775 else
776 if l_person_on then
777 hr_utility.trace('opening c_all_asg_range CURSOR');
778 open c_all_asg_range(l_bus_group_id,
779 l_year_start, l_year_end);
780 else
781 hr_utility.trace('opening c_all_asg CURSOR');
782 open c_all_asg(l_bus_group_id,
783 l_year_start, l_year_end);
784 end if;
785 end if;
786
787 loop
788
789 /* Added this validation to fix bug#2135545 */
790 if l_asg_set_id is not null then
791 if l_person_on then
792 fetch c_all_asg_in_asgset_range into l_person_id, l_asg_id;
793 exit when c_all_asg_in_asgset_range%notfound;
794 else
795 fetch c_all_asg_in_asgset into l_person_id, l_asg_id;
796 exit when c_all_asg_in_asgset%notfound;
797 end if;
798 elsif lv_per_id is not null then
799 if l_person_on then
800 fetch c_single_asg_range into l_person_id, l_asg_id;
801 exit when c_single_asg_range%notfound;
802 else
803 fetch c_single_asg into l_person_id, l_asg_id;
804 exit when c_single_asg%notfound;
805 end if;
806 else
807 if l_person_on then
808 fetch c_all_asg_range into l_person_id, l_asg_id;
809 exit when c_all_asg_range%notfound;
810 else
811 fetch c_all_asg into l_person_id, l_asg_id;
812 exit when c_all_asg%notfound;
813 end if;
814 end if;
815
816 hr_utility.trace('Begining of if part loop for c_all_asg 10 '||
817 to_char(l_asg_id));
818
819
820 if ( l_person_id = l_prev_person_id and
821 l_pre_organization_id = l_prev_pre_organization_id) then
822
823 hr_utility.trace('Not creating assignment action');
824
825 else
826
827 /* Get the primary assignment as the primary
828 assignment is the assignment_id that is
829 always archived. Must check against this
830 assignment when checking for archived person */
831 /* using this dummy loop to exit if no data found in
832 CURSOR c_get_asg_id_term */
833 for i in 1..1 loop
834 if nvl(l_print_term,'N') = 'Y' then
835 open c_get_asg_id_term(l_person_id);
836 fetch c_get_asg_id_term into l_primary_asg,l_effective_end_date;
837 if c_get_asg_id_term%NOTFOUND then
838 close c_get_asg_id_term;
839 exit;
840 else
841 close c_get_asg_id_term;
842 end if;
843 else
844 open c_get_asg_id(l_person_id);
845 fetch c_get_asg_id into l_primary_asg,l_effective_end_date;
846 if c_get_asg_id%NOTFOUND then
847 close c_get_asg_id;
848 hr_utility.raise_error;
849 else
850 close c_get_asg_id;
851 end if;
852 end if;
853
854 if (l_report_type = 'RL1PAPERPDF' and nvl(pay_us_employee_payslip_web.get_doc_eit('RL1PDF',
855 'PRINT',
856 'ASSIGNMENT',
857 l_primary_asg,
858 l_effective_end_date),'Y') = 'Y') or l_report_type <> 'RL1PAPERPDF' then
859
860 open c_archived_person_info (l_person_id,
861 l_primary_asg,
862 l_pre_organization_id);
863 fetch c_archived_person_info
864 into ln_arch_asgact_id,
865 ln_arch_pact_id;
866 if c_archived_person_info%notfound then
867 hr_utility.trace('No Archived Person Found');
868 else
869 select pay_assignment_actions_s.nextval
870 into lockingactid
871 from dual;
872
873 hr_nonrun_asact.insact(lockingactid,
874 -- l_asg_id, -- commented by sneelapa, for bug 7572889
875 l_primary_asg,
876 pactid,
877 chunk,
878 l_pre_org_id);
879 hr_utility.trace('in if loop after calling '||
880 'hr_nonrun_asact.insact pkg 11 '||to_char(lockingactid));
881
882 /* Added this to implement RL1 Register and RL1 Amendment Register
883 using the same report file */
884 lv_serial_number := 'QC' ||lpad(to_char(ln_arch_asgact_id),14,0)||
885 lpad(to_char(ln_arch_pact_id),14,0);
886
887 hr_utility.trace('lv_serial_number :' ||lv_serial_number);
888
889 update pay_assignment_actions paa
890 set paa.serial_number = lv_serial_number
891 where paa.assignment_action_id = lockingactid;
892
893 l_prev_person_id := l_person_id;
894 l_prev_pre_organization_id := l_pre_organization_id;
895
896 end if;
897 close c_archived_person_info;
898 end if; --pay_us_employee_payslip_web.get_doc_eit
899 end loop;
900 end if;
901
902 end loop;
903
904 /* Added this validation to fix bug#2135545 */
905 if l_asg_set_id is not null then
906 if l_person_on then
907 close c_all_asg_in_asgset_range;
908 else
909 close c_all_asg_in_asgset;
910 end if;
911 elsif lv_per_id is not null then
912 if l_person_on then
913 close c_single_asg_range;
914 else
915 close c_single_asg;
916 end if;
917 else
918 if l_person_on then
919 close c_all_asg_range;
920 else
921 close c_all_asg;
922 end if;
923 end if;
924
925 hr_utility.trace('End of cursor c_all_asg 12');
926 end if;
927 end if;
928 hr_utility.trace('End of If Condition for Loop 13');
929 end action_creation;
930 /*
931 ---------------------------------- sort_action ----------------------------------
932 */
933 procedure sort_action
934 (
935 payactid in varchar2, /* payroll action id */
936 sqlstr in out nocopy varchar2, /* string holding the sql statement */
937 len out nocopy number /* length of the sql string */
938 ) is
939 begin
940 hr_utility.trace('Start of Sort_Action 1');
941 -- assignment_type, primary_flag condition are added by sneelapa, for bug 7572889
942 sqlstr := 'select paa1.rowid /* we need the row id of the assignment actions that are created by PYUGEN */
943 from hr_all_organization_units hou1,
944 hr_all_organization_units hou,
945 hr_locations_all loc,
946 per_all_people_f ppf,
947 per_all_assignments_f paf,
948 pay_assignment_actions paa1,
949 pay_payroll_actions ppa1
950 where ppa1.payroll_action_id = :pactid
951 and paa1.payroll_action_id = ppa1.payroll_action_id
952 and paa1.assignment_id = paf.assignment_id
953 and paf.assignment_type = ''E''
954 and paf.primary_flag = ''Y''
955 and paf.business_group_id = ppa1.business_group_id
956 and paf.effective_start_date <= ppa1.effective_date
957 and hou.organization_id = paa1.tax_unit_id
958 and loc.location_id = paf.location_id
959 and hou1.organization_id = paf.organization_id
960 and ppf.person_id = paf.person_id
961 and ppa1.effective_date between
962 ppf.effective_start_date and ppf.effective_end_date
963 and paf.effective_end_date =
964 (select max(paaf2.effective_end_date)
965 from per_all_assignments_f paaf2
966 where paaf2.assignment_id = paf.assignment_id
967 and paaf2.effective_start_date <= ppa1.effective_date)
968 order by
969 decode(pay_ca_rl1_reg.get_parameter(''P_S1'',ppa1.legislative_parameters),
970 ''RL1_PRE'',hou.name,
971 ''RL1_ORG'',hou1.name,
972 ''RL1_LOC'',loc.location_code,null)
973 ,decode(pay_ca_rl1_reg.get_parameter(''P_S2'',ppa1.legislative_parameters),
974 ''RL1_PRE'',hou.name,
975 ''RL1_ORG'',hou1.name,
976 ''RL1_LOC'',loc.location_code,null)
977 ,decode(pay_ca_rl1_reg.get_parameter(''P_S3'',ppa1.legislative_parameters),
978 ''RL1_PRE'',hou.name,
979 ''RL1_ORG'',hou1.name,
980 ''RL1_LOC'',loc.location_code,null)
981 ,ppf.last_name,ppf.first_name';
982
983 -- Remove below lines from above query and rewrite it using add_months
984 -- paaf.effective_end_date,-1,to_date(''31-DEC-''||
985 -- to_char(paaf.effective_end_date,''YY'')) )
986
987 len := length(sqlstr); -- return the length of the string.
988 hr_utility.trace('End of Sort_Action 2');
989 end sort_action;
990 /*
991 ------------------------------ get_parameter -------------------------------
992 */
993 function get_parameter(name in varchar2,
994 parameter_list varchar2) return varchar2
995 is
996 start_ptr number;
997 end_ptr number;
998 token_val pay_payroll_actions.legislative_parameters%type;
999 par_value pay_payroll_actions.legislative_parameters%type;
1000 begin
1001
1002 token_val := name||'=';
1003
1004 start_ptr := instr(parameter_list, token_val) + length(token_val);
1005 end_ptr := instr(parameter_list, ' ',start_ptr);
1006
1007 /* if there is no spaces use then length of the string */
1008 if end_ptr = 0 then
1009 end_ptr := length(parameter_list)+1;
1010 end if;
1011
1012 /* Did we find the token */
1013 if instr(parameter_list, token_val) = 0 then
1014 par_value := NULL;
1015 else
1016 par_value := substr(parameter_list, start_ptr, end_ptr - start_ptr);
1017 end if;
1018
1019 return par_value;
1020
1021 end get_parameter;
1022
1023 /* Get RL1 Messages */
1024 /* Modified this function on 08-Jan-2002 version 115.8
1025 Bug - 2159362
1026 The "From" date should indicate either the day the employee was hired (hire
1027 date) if hired within the Calendar Year, or January 1st of the Calendar Year
1028 if hired prior to the Calendar Year. Presently the "From" date is using the
1029 date on which the employee turned 18.
1030 */
1031 function get_rl1_message(p_tax_year in varchar2,
1032 p_emp_dob in varchar2,
1033 p_hire_dt in varchar2,
1034 p_termination_dt in varchar2) return varchar2
1035 is
1036 lv_message varchar2(250) := null;
1037 lv_eighteen varchar2(250);
1038 lv_year varchar2(250);
1039 lv_st_dt varchar2(250);
1040 begin
1041 if to_number(p_tax_year) -
1042 to_number(to_char(fnd_date.canonical_to_date(p_emp_dob),'YYYY')) = 18 then
1043 lv_year := to_char(add_months(trunc(to_date(p_tax_year,'YYYY'),'Y'),
1044 12)-1,'DD-MON-YYYY');
1045 lv_eighteen := to_char(add_months(fnd_date.canonical_to_date(p_emp_dob),
1046 216), 'DD-MON-YYYY');
1047 if fnd_date.canonical_to_date(p_hire_dt) <=
1048 trunc(to_date(p_tax_year,'YYYY'),'Y') then
1049 lv_st_dt := to_char(trunc(to_date(p_tax_year,'YYYY'),'Y'),
1050 'DD-MON-YYYY');
1051 else
1052 lv_st_dt := p_hire_dt;
1053 end if;
1054 if p_termination_dt is not null and
1055 fnd_date.canonical_to_date(nvl(p_termination_dt,lv_year)) <
1056 fnd_date.canonical_to_date(lv_year)
1057 then
1058 lv_year := p_termination_dt;
1059 end if;
1060 hr_utility.set_message(801,'PAY_74040_EOY_EXCP_TURNS_18');
1061 -- hr_utility.set_message_token('ST_DATE',lv_eighteen);
1062 hr_utility.set_message_token('ST_DATE',lv_st_dt);
1063 hr_utility.set_message_token('END_DATE',lv_year);
1064 lv_message := hr_utility.get_message;
1065 end if;
1066 return(lv_message);
1067 end get_rl1_message;
1068
1069 --
1070 /* To get primary address of an employee */
1071 /* Address line 1 to 3 are normal address lines */
1072 /* Address Line 4 = City + Province Code + Postal Code */
1073 /* Address Line 5 = Country Code */
1074 /* Address Line 6 = Country Name */
1075 /* Address Line 7 = Town or City */
1076 /* Address Line 8 = Province Code */
1077 /* Address Line 9 = Postal Code */
1078
1079 function get_primary_address(p_person_id in Number,
1080 p_effective_date in date
1081 ) return PrimaryAddress IS
1082
1083 cursor csr_address( p_person_id in number,
1084 p_effective_date in date) is
1085 select addr.address_line1
1086 ,addr.address_line2
1087 ,addr.address_line3
1088 ,rtrim(substr(addr.town_or_city,1,23)) ||' '||
1089 decode(addr.country, 'CA', addr.region_1, 'US', addr.region_2,
1090 addr.region_1 )
1091 ||' '|| addr.country -- Bug 4134616
1092 ||' '|| addr.postal_code address_line4
1093 ,addr.country address_line5 -- Country Code
1094 ,country.territory_short_name address_line6 -- Country Name
1095 ,addr.town_or_city Town_or_City
1096 ,decode(addr.country, 'CA', addr.region_1,
1097 'US', addr.region_2, addr.region_1 ) Province
1098 ,addr.postal_code Postal_Code
1099 from per_addresses addr
1100 ,fnd_territories_vl country
1101 where addr.person_id = p_person_id
1102 and addr.primary_flag = 'Y'
1103 /* Added the trunc function by ssmukher for Bug 4205724 */
1104 and trunc(p_effective_date) between
1105 addr.date_from and nvl(addr.date_to, trunc(p_effective_date))
1106 and country.territory_code = addr.country;
1107
1108 addr PrimaryAddress;
1109
1110 begin
1111
1112 open csr_address(p_person_id,p_effective_date);
1113 fetch csr_address into addr;
1114 close csr_address;
1115
1116 return addr;
1117
1118 end get_primary_address;
1119
1120
1121 function get_label(p_lookup_type in VARCHAR2,
1122 p_lookup_code in VARCHAR2)
1123 return VARCHAR2 is
1124 cursor csr_label_meaning is
1125 select meaning
1126 from hr_lookups
1127 where lookup_type = p_lookup_type
1128 and lookup_code = p_lookup_code;
1129
1130 l_label_meaning varchar2(80);
1131 begin
1132 open csr_label_meaning;
1133
1134 fetch csr_label_meaning into l_label_meaning;
1135 if csr_label_meaning%NOTFOUND then
1136 l_label_meaning := NULL;
1137 end if;
1138 close csr_label_meaning;
1139
1140 return l_label_meaning;
1141 end get_label;
1142
1143
1144 function get_label(p_lookup_type in VARCHAR2,
1145 p_lookup_code in VARCHAR2,
1146 p_person_language in varchar2)
1147 return VARCHAR2 is
1148 cursor csr_label_meaning is
1149 select 1 ord, meaning
1150 from fnd_lookup_values
1151 where lookup_type = p_lookup_type
1152 and lookup_code = p_lookup_code
1153 and ( ( p_person_language is null and language = 'US' ) or
1154 ( p_person_language is not null and language = p_person_language ) )
1155 union all
1156 select 2 ord, meaning
1157 from fnd_lookup_values
1158 where lookup_type = p_lookup_type
1159 and lookup_code = p_lookup_code
1160 and ( language = 'US' and p_person_language is not null
1161 and language <> p_person_language )
1162 order by 1;
1163
1164 l_order number;
1165 l_label_meaning varchar2(80);
1166
1167 begin
1168 open csr_label_meaning;
1169
1170 fetch csr_label_meaning into l_order, l_label_meaning;
1171 if csr_label_meaning%NOTFOUND then
1172 l_label_meaning := NULL;
1173 end if;
1174 close csr_label_meaning;
1175
1176 return l_label_meaning;
1177 end get_label;
1178
1179 end pay_ca_rl1_reg;
[Home] [Help]