1 package body pay_ca_rl1_reg as
2 /* $Header: pycarrrg.pkb 120.3 2006/02/11 00:09:29 ssouresr noship $ */
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 /*
127 ----------------------------------- range_cursor -----------------------------
128 */
129
130 function get_user_entity_id(p_user_name varchar2) return number is
131
132 begin
133
134 declare
135
136 cursor cur_user_entity_id is
137 select user_entity_id
138 from ff_database_items
139 where user_name = p_user_name;
140
141 l_user_entity_id ff_database_items.user_entity_id%TYPE;
142
143 begin
144
145 open cur_user_entity_id;
146
147 fetch cur_user_entity_id
148 into l_user_entity_id;
149
150 close cur_user_entity_id;
151
152 return l_user_entity_id;
153
154 end;
155 end;
156
157 procedure range_cursor (pactid in number, sqlstr out nocopy varchar2) is
158 l_payroll_id number;
159 leg_param pay_payroll_actions.legislative_parameters%type;
160
161 begin
162 --hr_utility.trace_on('Y','RL1');
163 hr_utility.trace('begining of range_cursor 1 ');
164 select legislative_parameters
165 into leg_param
166 from pay_payroll_actions ppa
167 where ppa.payroll_action_id = pactid;
168
169
170 /* pay reg code */
171
172 sqlstr := 'select distinct to_number(fai3.value)
173 from ff_archive_items fai1,
174 ff_archive_items fai2,
175 ff_database_items fdi1,
176 ff_database_items fdi2,
177 ff_archive_items fai3,
178 ff_database_items fdi3,
179 pay_payroll_actions ppa,
180 pay_assignment_actions paa
181 where ppa.payroll_action_id = :payroll_action_id
182 and fai1.user_entity_id = fdi1.user_entity_id
183 and fdi1.user_name = ''CAEOY_TAXATION_YEAR''
184 and fai1.value =
185 nvl(pay_ca_rl1_reg.get_parameter(''TAX_YEAR'',
186 ppa.legislative_parameters),fai1.value)
187 and fai2.user_entity_id = fdi2.user_entity_id
188 and fdi2.user_name = ''CAEOY_RL1_PRE_ORG_ID''
189 and fai2.value =
190 nvl(pay_ca_rl1_reg.get_parameter(''PRE_ORGANIZATION_ID'',
191 ppa.legislative_parameters),
192 fai2.value)
193 and fai1.context1 = fai2.context1
194 and paa.payroll_action_id= fai2.context1
195 and paa.assignment_action_id=fai3.context1
196 and fai3.user_entity_id = fdi3.user_entity_id
197 and fdi3.user_name = ''CAEOY_PERSON_ID''
198 and fai3.value = nvl(pay_ca_rl1_reg.get_parameter(''PER_ID'',
199 ppa.legislative_parameters),fai3.value)
200 order by to_number(fai3.value)';
201
202 hr_utility.trace('End of range_cursor 2 ');
203 end range_cursor;
204 /*
205 -------------------------------- action_creation ----------------------------------
206 */
207
208 procedure action_creation(pactid in number,
209 stperson in number,
210 endperson in number,
211 chunk in number) is
212
213 lockingactid number;
214 lockedactid number;
215 l_asg_id number;
216 l_primary_asg number;
217 l_asg_set_id number;
218 l_tax_unit_id number;
219 l_year varchar2(4);
220 l_bus_group_id number;
221 l_year_start date;
222 l_year_end date;
223 l_pre_organization_id varchar2(17);
224 l_rl1reg_pre_org_id varchar2(17);
225 l_effective_date date;
226 l_report_type varchar2(80);
230 l_prev_pre_organization_id varchar2(17);
227 l_legislative_parameters varchar2(240);
228 l_person_id number;
229 l_prev_person_id number;
231
232 l_pre_org_id number; -- Organization Id of PRE (Prov Reporting Est)
233 lv_serial_number varchar2(30);
234 ln_arch_asgact_id number;
235 ln_arch_pact_id number;
236 lv_per_id varchar2(30);
237
238 /* For performance: getting all pre organization ids from
239 legislative parameter of pay_payroll_actions for RL1 archiver
240 for the given year within same business group.
241 */
242 cursor c_all_pres is
243 select pycadar_pkg.get_parameter('PRE_ORGANIZATION_ID',
244 ppa.legislative_parameters )
245 from pay_payroll_actions ppa
246 where ppa.report_type = 'RL1'
247 and ppa.report_qualifier = 'CAEOYRL1'
248 and ppa.report_category = 'CAEOYRL1'
249 and ppa.effective_date = l_year_end
250 and ppa.start_date = l_year_start
251 and ppa.business_group_id = l_bus_group_id
252 and ppa.action_status = 'C';
253
254 cursor c_archived_person_info (cp_person_id in number,
255 cp_assignment_id in number,
256 cp_pre_org_id in varchar2) is
257 select paa.assignment_action_id,
258 ppa.payroll_action_id
259 from pay_payroll_actions ppa,
260 pay_assignment_actions paa
261 where ppa.report_type = 'RL1'
262 and ppa.report_qualifier = 'CAEOYRL1'
263 and ppa.report_category = 'CAEOYRL1'
264 and ppa.effective_date = l_year_end
265 and ppa.start_date = l_year_start
266 and ppa.business_group_id = l_bus_group_id
267 and ppa.action_status = 'C'
268 and ppa.payroll_action_id = paa.payroll_action_id
269 and paa.serial_number = to_char(cp_person_id)
270 and pycadar_pkg.get_parameter('PRE_ORGANIZATION_ID',
271 ppa.legislative_parameters) = cp_pre_org_id
272 and paa.assignment_id = cp_assignment_id;
273
274 cursor c_first_tax_unit_id (p_pre_org_id varchar2) is
275 select distinct organization_id
276 from hr_organization_information hoi
277 where hoi.org_information_context = 'Canada Employer Identification'
278 and hoi.org_information2 = p_pre_org_id;
279
280 /* 11510 Change to fix bug#3356512, modified cursor c_all_asg */
281 cursor c_all_asg(l_business_group_id number
282 , l_year_start date
283 , l_year_end date ) is
284 select distinct paf.person_id,
285 paf.assignment_id assignment_id
286 from per_people_f ppf,
287 per_assignments_f paf
288 where ppf.person_id between stperson and endperson
289 and ppf.effective_start_date <= l_year_end
290 and ppf.effective_end_date >= l_year_start
291 and paf.person_id = ppf.person_id
292 and paf.effective_start_date <= l_year_end
293 and paf.effective_end_date >= l_year_start
294 and paf.assignment_type = 'E'
295 and paf.business_group_id + 0 = l_business_group_id
296 order by 1,2;
297
298 cursor c_single_asg (l_business_group_id number
299 ,l_year_start date
300 ,l_year_end date
301 ,l_per_id varchar2) is
302 select distinct paf.person_id,
303 paf.assignment_id assignment_id
304 from per_people_f ppf,
305 per_assignments_f paf
306 where ppf.person_id between stperson and endperson
307 and ppf.effective_start_date <= l_year_end
308 and ppf.effective_end_date >= l_year_start
309 and paf.person_id = ppf.person_id
310 and ppf.person_id = to_number(l_per_id)
311 and paf.effective_start_date <= l_year_end
312 and paf.effective_end_date >= l_year_start
313 and paf.assignment_type = 'E'
314 and paf.business_group_id + 0 = l_business_group_id
315 order by 1,2;
316
317 /* Added this new cursor to fix bug#2135545 and this
318 will be used only if Assignment Set is passed for RL1 reports */
319 /* 11510 change modified c_all_asg_in_asgset cursor to fix bug#3356512*/
320 cursor c_all_asg_in_asgset(l_business_group_id number
321 ,l_year_start date
322 ,l_year_end date ) is
323 select distinct paf.person_id,
324 paf.assignment_id assignment_id
325 from per_people_f ppf,
326 per_assignments_f paf
327 where ppf.person_id between stperson and endperson
328 and ppf.effective_start_date <= l_year_end
329 and ppf.effective_end_date >= l_year_start
330 and paf.person_id = ppf.person_id
331 and paf.effective_start_date <= l_year_end
332 and paf.effective_end_date >= l_year_start
333 and paf.assignment_type = 'E'
334 and paf.business_group_id +0 = l_business_group_id
335 and exists ( select 1 /* Selected Assignment Set */
336 from hr_assignment_set_amendments hasa
337 where hasa.assignment_set_id = l_asg_set_id
338 and hasa.assignment_id = paf.assignment_id
339 and upper(hasa.include_or_exclude) = 'I')
340 order by 1,2;
341
342 cursor c_get_asg_id (p_person_id number) is
343 select assignment_id
344 from per_assignments_f paf
348 and paf.effective_start_date <= l_year_end
345 where person_id = p_person_id
346 and primary_flag = 'Y'
347 and assignment_type = 'E'
349 and paf.effective_end_date >= l_year_start
350 order by assignment_id desc;
351
352 begin
353 hr_utility.set_location('procpyr',1);
354 hr_utility.trace('begining of action creation 1'||to_char(pactid));
355
356 /* get report type and effective date */
357
358 select effective_date,
359 report_type,
360 business_group_id,
361 legislative_parameters
362 into l_effective_date,
363 l_report_type,
364 l_bus_group_id,
365 l_legislative_parameters
366 from pay_payroll_actions
367 where payroll_action_id = pactid;
368
369 hr_utility.trace('begining of action creation 2 '||
370 to_char(l_bus_group_id));
371
372 hr_utility.trace('legislative parameters is '||l_legislative_parameters);
373
374 l_year := pay_ca_rl1_reg.get_parameter('TAX_YEAR',l_legislative_parameters);
375 l_year_start := trunc(to_date(l_year,'YYYY'), 'Y');
376 l_year_end := add_months(trunc(to_date(l_year,'YYYY'), 'Y'),12) - 1;
377 l_asg_set_id := pay_ca_rl1_reg.get_parameter('ASG_SET_ID',l_legislative_parameters);
378 lv_per_id := pay_ca_rl1_reg.get_parameter('PER_ID',l_legislative_parameters);
379
380 hr_utility.trace('begin action creation '||l_year||to_char(l_year_start)||to_char(l_year_end));
381
382 l_rl1reg_pre_org_id := pay_ca_rl1_reg.get_parameter('PRE_ORGANIZATION_ID',
383 l_legislative_parameters);
384
385 hr_utility.trace('begining of action creation 4 *'||
386 l_rl1reg_pre_org_id||'*');
387
388 if l_rl1reg_pre_org_id is NULL then
389 open c_all_pres;
390 hr_utility.trace('else condition after open c_all_pres '||
391 'c_all_asg cursor 6 ');
392 else
393 l_pre_organization_id := l_rl1reg_pre_org_id;
394 hr_utility.trace('begining of if condition 5 '||l_pre_organization_id);
395 end if;
396
397 if l_rl1reg_pre_org_id is NULL then
398 loop
399 fetch c_all_pres into l_pre_organization_id;
400 hr_utility.trace('Begining of else loop for c_all_pres 7 '||
401 l_pre_organization_id);
402 exit when c_all_pres%notfound;
403 open c_first_tax_unit_id(l_pre_organization_id);
404 fetch c_first_tax_unit_id into l_tax_unit_id;
405
406 if c_first_tax_unit_id%FOUND then
407 close c_first_tax_unit_id;
408
409 l_pre_org_id := to_number(l_pre_organization_id);
410
411 /* Added this validation to fix bug#2135545 */
412
413 if l_asg_set_id is not null then
414 open c_all_asg_in_asgset(l_bus_group_id,
415 l_year_start , l_year_end);
416 elsif lv_per_id is not null then
417 open c_single_asg(l_bus_group_id, l_year_start,
418 l_year_end, lv_per_id);
419 else
420 open c_all_asg(l_bus_group_id,
421 l_year_start, l_year_end);
422 end if;
423
424 loop
425 /* Added this validation to fix bug#2135545 */
426
427 if l_asg_set_id is not null then
428 fetch c_all_asg_in_asgset into l_person_id, l_asg_id;
429 exit when c_all_asg_in_asgset%notfound;
430 elsif lv_per_id is not null then
431 fetch c_single_asg into l_person_id, l_asg_id;
432 exit when c_single_asg%notfound;
433 else
434 fetch c_all_asg into l_person_id, l_asg_id;
435 exit when c_all_asg%notfound;
436 end if;
437
438 hr_utility.trace('Begining of loop for c_all_asg 8 '||
439 to_char(l_asg_id));
440
441 if ( l_person_id = l_prev_person_id and
442 l_pre_organization_id = l_prev_pre_organization_id) then
443
444 hr_utility.trace('Not creating assignment action');
445
446 else
447 /* Get the primary assignment as the primary
448 assignment is the assignment_id that is
449 always archived. Must check against this
450 assignment when checking for archived person */
451
452 open c_get_asg_id(l_person_id);
453 fetch c_get_asg_id into l_primary_asg;
454
455 if c_get_asg_id%NOTFOUND then
456 close c_get_asg_id;
457 hr_utility.raise_error;
458 else
459 close c_get_asg_id;
460 end if;
461
462 open c_archived_person_info (l_person_id,
463 l_primary_asg,
464 l_pre_organization_id);
465 fetch c_archived_person_info
469 hr_utility.trace('No Archived Person Found');
466 into ln_arch_asgact_id,
467 ln_arch_pact_id;
468 if c_archived_person_info%notfound then
470 else
471 select pay_assignment_actions_s.nextval
472 into lockingactid
473 from dual;
474
475 hr_nonrun_asact.insact(lockingactid,
476 l_asg_id,
477 pactid,
478 chunk,
479 l_pre_org_id);
480
481 hr_utility.trace('in if loop after calling '||
482 'hr_nonrun_asact.insact pkg 9 '||to_char(l_asg_id));
483
484 /* Added this to implement RL1 Register and RL1 Amendment Register
485 using the same report file */
486 lv_serial_number := 'QC' ||lpad(to_char(ln_arch_asgact_id),14,0)||
487 lpad(to_char(ln_arch_pact_id),14,0);
488
489 hr_utility.trace('lv_serial_number :' ||lv_serial_number);
490
491 update pay_assignment_actions paa
492 set paa.serial_number = lv_serial_number
493 where paa.assignment_action_id = lockingactid;
494
495 l_prev_person_id := l_person_id;
496 l_prev_pre_organization_id := l_pre_organization_id;
497
498 end if;
499 close c_archived_person_info;
500
501 end if;
502
503 end loop;
504
505 /* Added this validation to fix bug#2135545 */
506 if l_asg_set_id is not null then
507 close c_all_asg_in_asgset;
508 elsif lv_per_id is not null then
509 close c_single_asg;
510 else
511 close c_all_asg;
512 end if;
513
514 else
515
516 hr_utility.trace('No GRE for this PRE Organization id');
517 hr_utility.raise_error;
518
519 end if;
520 end loop;
521 close c_all_pres;
522 else
523 open c_first_tax_unit_id(l_pre_organization_id);
524 fetch c_first_tax_unit_id into l_tax_unit_id;
525
526 if c_first_tax_unit_id%FOUND then
527 close c_first_tax_unit_id;
528
529 l_pre_org_id := to_number(l_pre_organization_id);
530
531 /* Added this validation to fix bug#2135545 */
532 if l_asg_set_id is not null then
533 open c_all_asg_in_asgset(l_bus_group_id,
534 l_year_start, l_year_end);
535 elsif lv_per_id is not null then
536 open c_single_asg(l_bus_group_id, l_year_start,
537 l_year_end, lv_per_id);
538 else
539 open c_all_asg(l_bus_group_id,
540 l_year_start, l_year_end);
541 end if;
542
543 loop
544
545 /* Added this validation to fix bug#2135545 */
546 if l_asg_set_id is not null then
547 fetch c_all_asg_in_asgset into l_person_id, l_asg_id;
548 exit when c_all_asg_in_asgset%notfound;
549 elsif lv_per_id is not null then
550 fetch c_single_asg into l_person_id, l_asg_id;
551 exit when c_single_asg%notfound;
552 else
553 fetch c_all_asg into l_person_id, l_asg_id;
554 exit when c_all_asg%notfound;
555 end if;
556
557 hr_utility.trace('Begining of if part loop for c_all_asg 10 '||
558 to_char(l_asg_id));
559
560
561 if ( l_person_id = l_prev_person_id and
562 l_pre_organization_id = l_prev_pre_organization_id) then
563
564 hr_utility.trace('Not creating assignment action');
565
566 else
567
568 /* Get the primary assignment as the primary
569 assignment is the assignment_id that is
570 always archived. Must check against this
571 assignment when checking for archived person */
572
573 open c_get_asg_id(l_person_id);
574 fetch c_get_asg_id into l_primary_asg;
575
576 if c_get_asg_id%NOTFOUND then
577 close c_get_asg_id;
578 hr_utility.raise_error;
579 else
580 close c_get_asg_id;
581 end if;
582
583 open c_archived_person_info (l_person_id,
584 l_primary_asg,
585 l_pre_organization_id);
586 fetch c_archived_person_info
587 into ln_arch_asgact_id,
591 else
588 ln_arch_pact_id;
589 if c_archived_person_info%notfound then
590 hr_utility.trace('No Archived Person Found');
592 select pay_assignment_actions_s.nextval
593 into lockingactid
594 from dual;
595
596 hr_nonrun_asact.insact(lockingactid,
597 l_asg_id,
598 pactid,
599 chunk,
600 l_pre_org_id);
601 hr_utility.trace('in if loop after calling '||
602 'hr_nonrun_asact.insact pkg 11 '||to_char(lockingactid));
603
604 /* Added this to implement RL1 Register and RL1 Amendment Register
605 using the same report file */
606 lv_serial_number := 'QC' ||lpad(to_char(ln_arch_asgact_id),14,0)||
607 lpad(to_char(ln_arch_pact_id),14,0);
608
609 hr_utility.trace('lv_serial_number :' ||lv_serial_number);
610
611 update pay_assignment_actions paa
612 set paa.serial_number = lv_serial_number
613 where paa.assignment_action_id = lockingactid;
614
615 l_prev_person_id := l_person_id;
616 l_prev_pre_organization_id := l_pre_organization_id;
617
618 end if;
619 close c_archived_person_info;
620
621 end if;
622
623 end loop;
624
625 /* Added this validation to fix bug#2135545 */
626 if l_asg_set_id is not null then
627 close c_all_asg_in_asgset;
628 elsif lv_per_id is not null then
629 close c_single_asg;
630 else
631 close c_all_asg;
632 end if;
633
634 hr_utility.trace('End of cursor c_all_asg 12');
635 end if;
636 end if;
637 hr_utility.trace('End of If Condition for Loop 13');
638 end action_creation;
639 /*
640 ---------------------------------- sort_action ----------------------------------
641 */
642 procedure sort_action
643 (
644 payactid in varchar2, /* payroll action id */
645 sqlstr in out nocopy varchar2, /* string holding the sql statement */
646 len out nocopy number /* length of the sql string */
647 ) is
648 begin
649 hr_utility.trace('Start of Sort_Action 1');
650 sqlstr := 'select paa1.rowid /* we need the row id of the assignment actions that are created by PYUGEN */
651 from hr_all_organization_units hou1,
652 hr_all_organization_units hou,
653 hr_locations_all loc,
654 per_all_people_f ppf,
655 per_all_assignments_f paf,
656 pay_assignment_actions paa1,
657 pay_payroll_actions ppa1
658 where ppa1.payroll_action_id = :pactid
659 and paa1.payroll_action_id = ppa1.payroll_action_id
660 and paa1.assignment_id = paf.assignment_id
661 and paf.business_group_id = ppa1.business_group_id
662 and paf.effective_start_date <= ppa1.effective_date
663 and hou.organization_id = paa1.tax_unit_id
664 and loc.location_id = paf.location_id
665 and hou1.organization_id = paf.organization_id
666 and ppf.person_id = paf.person_id
667 and ppa1.effective_date between
668 ppf.effective_start_date and ppf.effective_end_date
669 and paf.effective_end_date =
670 (select max(paaf2.effective_end_date)
671 from per_all_assignments_f paaf2
672 where paaf2.assignment_id = paf.assignment_id
673 and paaf2.effective_start_date <= ppa1.effective_date)
674 order by
675 decode(pay_ca_rl1_reg.get_parameter(''P_S1'',ppa1.legislative_parameters),
676 ''RL1_PRE'',hou.name,
677 ''RL1_ORG'',hou1.name,
678 ''RL1_LOC'',loc.location_code,null)
679 ,decode(pay_ca_rl1_reg.get_parameter(''P_S2'',ppa1.legislative_parameters),
680 ''RL1_PRE'',hou.name,
681 ''RL1_ORG'',hou1.name,
682 ''RL1_LOC'',loc.location_code,null)
683 ,decode(pay_ca_rl1_reg.get_parameter(''P_S3'',ppa1.legislative_parameters),
684 ''RL1_PRE'',hou.name,
685 ''RL1_ORG'',hou1.name,
686 ''RL1_LOC'',loc.location_code,null)
687 ,ppf.last_name,ppf.first_name';
688
689 -- Remove below lines from above query and rewrite it using add_months
690 -- paaf.effective_end_date,-1,to_date(''31-DEC-''||
691 -- to_char(paaf.effective_end_date,''YY'')) )
692
693 len := length(sqlstr); -- return the length of the string.
694 hr_utility.trace('End of Sort_Action 2');
695 end sort_action;
696 /*
697 ------------------------------ get_parameter -------------------------------
698 */
699 function get_parameter(name in varchar2,
700 parameter_list varchar2) return varchar2
704 token_val pay_payroll_actions.legislative_parameters%type;
701 is
702 start_ptr number;
703 end_ptr number;
705 par_value pay_payroll_actions.legislative_parameters%type;
706 begin
707
708 token_val := name||'=';
709
710 start_ptr := instr(parameter_list, token_val) + length(token_val);
711 end_ptr := instr(parameter_list, ' ',start_ptr);
712
713 /* if there is no spaces use then length of the string */
714 if end_ptr = 0 then
715 end_ptr := length(parameter_list)+1;
716 end if;
717
718 /* Did we find the token */
719 if instr(parameter_list, token_val) = 0 then
720 par_value := NULL;
721 else
722 par_value := substr(parameter_list, start_ptr, end_ptr - start_ptr);
723 end if;
724
725 return par_value;
726
727 end get_parameter;
728
729 /* Get RL1 Messages */
730 /* Modified this function on 08-Jan-2002 version 115.8
731 Bug - 2159362
732 The "From" date should indicate either the day the employee was hired (hire
733 date) if hired within the Calendar Year, or January 1st of the Calendar Year
734 if hired prior to the Calendar Year. Presently the "From" date is using the
735 date on which the employee turned 18.
736 */
737 function get_rl1_message(p_tax_year in varchar2,
738 p_emp_dob in varchar2,
739 p_hire_dt in varchar2,
740 p_termination_dt in varchar2) return varchar2
741 is
742 lv_message varchar2(250) := null;
743 lv_eighteen varchar2(250);
744 lv_year varchar2(250);
745 lv_st_dt varchar2(250);
746 begin
747 if to_number(p_tax_year) -
748 to_number(to_char(fnd_date.canonical_to_date(p_emp_dob),'YYYY')) = 18 then
749 lv_year := to_char(add_months(trunc(to_date(p_tax_year,'YYYY'),'Y'),
750 12)-1,'DD-MON-YYYY');
751 lv_eighteen := to_char(add_months(fnd_date.canonical_to_date(p_emp_dob),
752 216), 'DD-MON-YYYY');
753 if fnd_date.canonical_to_date(p_hire_dt) <=
754 trunc(to_date(p_tax_year,'YYYY'),'Y') then
755 lv_st_dt := to_char(trunc(to_date(p_tax_year,'YYYY'),'Y'),
756 'DD-MON-YYYY');
757 else
758 lv_st_dt := p_hire_dt;
759 end if;
760 if p_termination_dt is not null and
761 fnd_date.canonical_to_date(nvl(p_termination_dt,lv_year)) <
762 fnd_date.canonical_to_date(lv_year)
763 then
764 lv_year := p_termination_dt;
765 end if;
766 hr_utility.set_message(801,'PAY_74040_EOY_EXCP_TURNS_18');
767 -- hr_utility.set_message_token('ST_DATE',lv_eighteen);
768 hr_utility.set_message_token('ST_DATE',lv_st_dt);
769 hr_utility.set_message_token('END_DATE',lv_year);
770 lv_message := hr_utility.get_message;
771 end if;
772 return(lv_message);
773 end get_rl1_message;
774
775 --
776 /* To get primary address of an employee */
777 /* Address line 1 to 3 are normal address lines */
778 /* Address Line 4 = City + Province Code + Postal Code */
779 /* Address Line 5 = Country Code */
780 /* Address Line 6 = Country Name */
781 /* Address Line 7 = Town or City */
782 /* Address Line 8 = Province Code */
783 /* Address Line 9 = Postal Code */
784
785 function get_primary_address(p_person_id in Number,
786 p_effective_date in date
787 ) return PrimaryAddress IS
788
789 cursor csr_address( p_person_id in number,
790 p_effective_date in date) is
791 select addr.address_line1
792 ,addr.address_line2
793 ,addr.address_line3
794 ,rtrim(substr(addr.town_or_city,1,23)) ||' '||
795 decode(addr.country, 'CA', addr.region_1, 'US', addr.region_2,
796 addr.region_1 )
797 ||' '|| addr.country -- Bug 4134616
798 ||' '|| addr.postal_code address_line4
799 ,addr.country address_line5 -- Country Code
800 ,country.territory_short_name address_line6 -- Country Name
801 ,addr.town_or_city Town_or_City
802 ,decode(addr.country, 'CA', addr.region_1,
803 'US', addr.region_2, addr.region_1 ) Province
804 ,addr.postal_code Postal_Code
805 from per_addresses addr
806 ,fnd_territories_vl country
807 where addr.person_id = p_person_id
808 and addr.primary_flag = 'Y'
809 /* Added the trunc function by ssmukher for Bug 4205724 */
810 and trunc(p_effective_date) between
811 addr.date_from and nvl(addr.date_to, trunc(p_effective_date))
812 and country.territory_code = addr.country;
813
814 addr PrimaryAddress;
815
816 begin
817
818 open csr_address(p_person_id,p_effective_date);
819 fetch csr_address into addr;
820 close csr_address;
821
822 return addr;
823
824 end get_primary_address;
825
826
827 function get_label(p_lookup_type in VARCHAR2,
828 p_lookup_code in VARCHAR2)
829 return VARCHAR2 is
830 cursor csr_label_meaning is
831 select meaning
832 from hr_lookups
833 where lookup_type = p_lookup_type
834 and lookup_code = p_lookup_code;
835
836 l_label_meaning varchar2(80);
837 begin
838 open csr_label_meaning;
839
840 fetch csr_label_meaning into l_label_meaning;
841 if csr_label_meaning%NOTFOUND then
842 l_label_meaning := NULL;
843 end if;
844 close csr_label_meaning;
845
846 return l_label_meaning;
847 end get_label;
848
849
850 function get_label(p_lookup_type in VARCHAR2,
851 p_lookup_code in VARCHAR2,
852 p_person_language in varchar2)
853 return VARCHAR2 is
854 cursor csr_label_meaning is
855 select 1 ord, meaning
856 from fnd_lookup_values
857 where lookup_type = p_lookup_type
858 and lookup_code = p_lookup_code
859 and ( ( p_person_language is null and language = 'US' ) or
860 ( p_person_language is not null and language = p_person_language ) )
861 union all
862 select 2 ord, meaning
863 from fnd_lookup_values
864 where lookup_type = p_lookup_type
865 and lookup_code = p_lookup_code
866 and ( language = 'US' and p_person_language is not null
867 and language <> p_person_language )
868 order by 1;
869
870 l_order number;
871 l_label_meaning varchar2(80);
872
873 begin
874 open csr_label_meaning;
875
876 fetch csr_label_meaning into l_order, l_label_meaning;
877 if csr_label_meaning%NOTFOUND then
878 l_label_meaning := NULL;
879 end if;
880 close csr_label_meaning;
881
882 return l_label_meaning;
883 end get_label;
884
885 end pay_ca_rl1_reg;
[Home] [Help]