1 package body pay_ca_rl2_reg as
2 /* $Header: pycarl2.pkb 120.4.12000000.3 2007/08/08 05:26:53 amigarg 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 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
53 ----------------------------------- range_cursor -----------------------------
54 */
55
56 procedure range_cursor (pactid in number, sqlstr out nocopy varchar2) is
57 l_payroll_id number;
58 leg_param pay_payroll_actions.legislative_parameters%type;
59 l_taxyear varchar2(100);
60 l_pre_org_id varchar2(100);
61
62 begin
63 --hr_utility.trace_on('Y','RL2');
64 hr_utility.trace('begining of range_cursor 1 ');
65
66 select legislative_parameters
67 into leg_param
68 from pay_payroll_actions ppa
69 where ppa.payroll_action_id = pactid;
70
71 l_taxyear := '''' || pay_ca_rl2_reg.get_parameter('TAX_YEAR',leg_param) || '''';
72 l_pre_org_id := '''' || pay_ca_rl2_reg.get_parameter('PRE_ORGANIZATION_ID',leg_param) || '''';
73
74 sqlstr := 'select distinct to_number(paa_arch.serial_number)
75 from pay_action_information pai1,
76 pay_action_information pai2,
77 pay_payroll_actions ppa_reg,
78 pay_payroll_actions ppa_arch,
79 pay_assignment_actions paa_arch
80 where ppa_reg.payroll_action_id = :payroll_action_id
81 and pai1.action_context_type = ''PA''
82 and pai1.action_information1 = ''RL2''
83 and pai1.action_information_category = ''CAEOY TRANSMITTER INFO''
84 and pai1.action_information8 = nvl(' ||l_taxyear ||', pai1.action_information8)
85 and pai1.action_information27 = nvl(' ||l_pre_org_id || ',pai1.action_information27)
86 and pai2.action_context_type = ''AAP''
87 and pai2.action_information_category = ''CAEOY RL2 EMPLOYEE INFO''
88 and ppa_arch.payroll_action_id = pai1.action_context_id
89 and ppa_arch.payroll_action_id = paa_arch.payroll_action_id
90 and paa_arch.assignment_action_id = pai2.action_context_id
91 and paa_arch.action_status = ''C''
92 and paa_arch.serial_number = nvl(pay_ca_rl2_reg.get_parameter(''PER_ID'',ppa_reg.legislative_parameters),
93 paa_arch.serial_number)
94 order by to_number(paa_arch.serial_number)';
95
96 hr_utility.trace('End of range_cursor 2 ');
97 end range_cursor;
98 /*
99 -------------------------------- action_creation ----------------------------------
100 */
101
102 procedure action_creation(pactid in number,
103 stperson in number,
104 endperson in number,
105 chunk in number) is
106
107 lockingactid number;
108 l_asg_id number;
109 l_asg_set_id number;
110 l_tax_unit_id number;
111 l_year varchar2(4);
112 l_primary_asg number;
113 l_bus_group_id number;
114 l_person_id number;
115 l_prev_person_id number;
116 l_year_start date;
117 l_year_end date;
118 l_pre_org_id varchar2(30);
119 l_prev_pre_org_id varchar2(30);
120 l_pre_organization_id number;
121 l_rlreg_pre_org_id varchar2(30);
122 l_effective_date date;
123 l_report_type varchar2(80);
124 l_legislative_parameters varchar2(240);
125 lv_serial_number varchar2(30);
126 ln_arch_asgact_id number;
127 ln_arch_pact_id number;
128 lv_per_id varchar2(30);
129
130
131 /* For performance: getting all Prov Reporting Est org ids from
132 legislative parameter of pay_payroll_actions for RL2 archiver
133 for the given year within same business group.
134 */
135 cursor c_all_pres is
136 select pycadar_pkg.get_parameter('PRE_ORGANIZATION_ID',
137 ppa.legislative_parameters )
138 from pay_payroll_actions ppa
139 where ppa.report_type = 'RL2'
140 and ppa.report_qualifier = 'CAEOYRL2'
141 and ppa.report_category = 'ARCHIVE'
142 and ppa.effective_date = l_year_end
143 and ppa.start_date = l_year_start
144 and ppa.business_group_id = l_bus_group_id
145 and ppa.action_status = 'C';
146
147 cursor c_archived_person_info (cp_person_id in number,
148 cp_assignment_id in number,
149 cp_pre_org_id in varchar2) is
150 select paa.assignment_action_id,
151 ppa.payroll_action_id
152 from pay_payroll_actions ppa,
153 pay_assignment_actions paa
154 where ppa.report_type = 'RL2'
155 and ppa.report_qualifier = 'CAEOYRL2'
156 and ppa.report_category = 'ARCHIVE'
157 and ppa.effective_date = l_year_end
158 and ppa.start_date = l_year_start
159 and ppa.business_group_id = l_bus_group_id
160 and ppa.action_status = 'C'
161 and ppa.payroll_action_id = paa.payroll_action_id
162 and paa.serial_number = to_char(cp_person_id)
163 and pycadar_pkg.get_parameter('PRE_ORGANIZATION_ID',
164 ppa.legislative_parameters) = cp_pre_org_id
165 and paa.assignment_id = cp_assignment_id;
166
167 cursor c_get_asg_id (p_person_id number) is
168 select assignment_id
169 from per_assignments_f paf
170 where person_id = p_person_id
171 and primary_flag = 'Y'
172 and assignment_type = 'E'
173 and paf.effective_start_date <= l_year_end
174 and paf.effective_end_date >= l_year_start
175 order by assignment_id desc;
176
177
178 cursor c_first_tax_unit_id (l_pre_org_id varchar2) is
179 select distinct hoi.organization_id
180 from hr_organization_information hoi,
181 hr_all_organization_units hou
182 where hou.business_group_id = l_bus_group_id
183 and hou.organization_id = hoi.organization_id
184 and hoi.org_information_context = 'Canada Employer Identification'
185 and hoi.org_information2 = l_pre_org_id
186 and hoi.org_information5 = 'T4A/RL2';
187
188 cursor c_all_asg (l_year_start date,
189 l_year_end date) is
190 select distinct paa.assignment_id,
191 to_number(paa.serial_number)
192 from pay_payroll_actions ppa,
193 pay_assignment_actions paa
194 where ppa.report_type = 'RL2'
195 and ppa.report_qualifier = 'CAEOYRL2'
196 and ppa.report_category = 'ARCHIVE'
197 and ppa.effective_date = l_year_end
198 and ppa.start_date = l_year_start
199 and ppa.business_group_id = l_bus_group_id
200 and ppa.action_status = 'C'
201 and ppa.payroll_action_id = paa.payroll_action_id
202 and to_number(paa.serial_number) between stperson and endperson
203 order by to_number(paa.serial_number);
204
205 /*
206 select distinct paf.assignment_id assignment_id,
207 paf.person_id person_id
208 from
209 per_assignments_f paf
210 where paf.person_id between stperson and endperson
211 and paf.assignment_type = 'E'
212 and paf.primary_flag = 'Y'
213 and paf.effective_end_date >= l_year_start
214 and paf.business_group_id = l_bus_group_id
215 and paf.effective_start_date =
216 (select max(paf2.effective_start_date)
217 from per_assignments_f paf2
218 where paf2.assignment_id = paf.assignment_id
219 and paf2.effective_start_date <= l_year_end )
220
221 order by paf.person_id;
222 */
223
224 cursor c_single_asg (l_year_start date,
225 l_year_end date,
226 l_per_id varchar2) is
227 select distinct paa.assignment_id,
228 to_number(paa.serial_number)
229 from pay_payroll_actions ppa,
230 pay_assignment_actions paa
231 where ppa.report_type = 'RL2'
232 and ppa.report_qualifier = 'CAEOYRL2'
233 and ppa.report_category = 'ARCHIVE'
234 and ppa.effective_date = l_year_end
235 and ppa.start_date = l_year_start
236 and ppa.business_group_id = l_bus_group_id
237 and ppa.action_status = 'C'
238 and ppa.payroll_action_id = paa.payroll_action_id
239 and to_number(paa.serial_number) between stperson and endperson
240 and paa.serial_number = l_per_id;
241
242 /*
243 select distinct paf.assignment_id assignment_id,
244 paf.person_id person_id
245 from
246 per_assignments_f paf
247 where paf.person_id between stperson and endperson
248 and paf.person_id = to_number(l_per_id)
249 and paf.assignment_type = 'E'
250 and paf.primary_flag = 'Y'
251 and paf.effective_start_date <= l_year_end
252 and paf.effective_end_date >= l_year_start
253 and paf.business_group_id = l_bus_group_id;
254 */
255
256
257 /* Will be used only if Assignment Set is passed for RL2 reports */
258
259 cursor c_all_asg_in_asgset(l_year_start date,
260 l_year_end date) is
261 select distinct paa.assignment_id,
262 to_number(paa.serial_number)
263 from pay_payroll_actions ppa,
264 pay_assignment_actions paa
265 where ppa.report_type = 'RL2'
266 and ppa.report_qualifier = 'CAEOYRL2'
267 and ppa.report_category = 'ARCHIVE'
268 and ppa.effective_date = l_year_end
269 and ppa.start_date = l_year_start
270 and ppa.business_group_id = l_bus_group_id
271 and ppa.action_status = 'C'
272 and ppa.payroll_action_id = paa.payroll_action_id
273 and to_number(paa.serial_number) between stperson and endperson
274 and exists (select 1
275 from hr_assignment_set_amendments hasa,
276 per_assignments_f paf
277 where hasa.assignment_set_id = l_asg_set_id
278 and upper(hasa.include_or_exclude) = 'I'
279 and hasa.assignment_id = paf.assignment_id
280 and paf.person_id = to_number(paa.serial_number));
281
282 /*
283 select distinct paf.assignment_id assignment_id,
284 paf.person_id person_id
285 from per_assignments_f paf
286 where paf.person_id between stperson and endperson
287 and paf.assignment_type = 'E'
288 and paf.primary_flag = 'Y'
289 and paf.effective_start_date <= l_year_end
290 and paf.effective_end_date >= l_year_start
291 and paf.business_group_id = l_bus_group_id
292 and exists (select 1
293 from hr_assignment_set_amendments hasa
294 where hasa.assignment_set_id = l_asg_set_id
295 and hasa.assignment_id = paf.assignment_id
296 and upper(hasa.include_or_exclude) = 'I');
297 */
298
299 begin
300 --hr_utility.trace_on('Y','RL2PAPER');
301 hr_utility.set_location('procpyr',1);
302 hr_utility.trace('begining of action creation 1'||to_char(pactid));
303
304 /* get report type and effective date */
305
306 select effective_date,
307 report_type,
308 business_group_id,
309 legislative_parameters
310 into l_effective_date,
311 l_report_type,
312 l_bus_group_id,
313 l_legislative_parameters
314 from pay_payroll_actions
315 where payroll_action_id = pactid;
316
317 hr_utility.trace('legislative parameters is '||l_legislative_parameters);
318
319 l_year := pay_ca_rl2_reg.get_parameter('TAX_YEAR',l_legislative_parameters);
320 l_year_start := trunc(to_date(l_year,'YYYY'), 'Y');
321 l_year_end := add_months(trunc(to_date(l_year,'YYYY'), 'Y'),12) - 1;
322 l_asg_set_id := pay_ca_rl2_reg.get_parameter('ASG_SET_ID',l_legislative_parameters);
323 lv_per_id := pay_ca_rl2_reg.get_parameter('PER_ID',l_legislative_parameters);
324
325 l_rlreg_pre_org_id := pay_ca_rl2_reg.get_parameter('PRE_ORGANIZATION_ID',l_legislative_parameters);
326
327 if l_rlreg_pre_org_id is NULL then
328 open c_all_pres;
329 hr_utility.trace('else condition after open c_all_pres c_all_asg cursor 6 ');
330 else
331 l_pre_org_id := l_rlreg_pre_org_id;
332 hr_utility.trace('begining of if condition 5 '||l_pre_org_id);
333 end if;
334
338 loop
335
336 if l_rlreg_pre_org_id is NULL then
337
339 fetch c_all_pres into l_pre_org_id;
340 hr_utility.trace('Begining of else loop for c_all_pres 7 '|| l_pre_org_id);
341
342 exit when c_all_pres%notfound;
343
344 open c_first_tax_unit_id(l_pre_org_id);
345 fetch c_first_tax_unit_id into l_tax_unit_id;
346
347 if c_first_tax_unit_id%FOUND then
348 close c_first_tax_unit_id;
349
350 l_pre_organization_id := to_number(l_pre_org_id);
351
352 if l_asg_set_id is not null then
353 open c_all_asg_in_asgset(l_year_start, l_year_end );
354 elsif lv_per_id is not null then
355 open c_single_asg(l_year_start,
356 l_year_end, lv_per_id);
357
358 else
359 open c_all_asg(l_year_start, l_year_end );
360 end if;
361
362 loop
363 if l_asg_set_id is not null then
364 fetch c_all_asg_in_asgset into l_asg_id, l_person_id;
365 exit when c_all_asg_in_asgset%notfound;
366 elsif lv_per_id is not null then
367 fetch c_single_asg into l_asg_id, l_person_id;
368 exit when c_single_asg%notfound;
369 else
370 fetch c_all_asg into l_asg_id, l_person_id;
371 exit when c_all_asg%notfound;
372 end if;
373
374 if (l_person_id = l_prev_person_id and
375 l_pre_org_id = l_prev_pre_org_id) then
376
377 hr_utility.trace('Not creating assignment action');
378
379 else
380
381 /* Get the primary assignment as the primary
382 assignment is the assignment_id that is
383 always archived. Must check against this
384 assignment when checking for archived person */
385
386 open c_get_asg_id(l_person_id);
387 fetch c_get_asg_id into l_primary_asg;
388
389 if c_get_asg_id%NOTFOUND then
390 close c_get_asg_id;
391 hr_utility.raise_error;
392 else
393 close c_get_asg_id;
394 end if;
395
396 open c_archived_person_info (l_person_id,
397 l_primary_asg,
398 l_pre_org_id);
399 fetch c_archived_person_info
400 into ln_arch_asgact_id,
401 ln_arch_pact_id;
402 if c_archived_person_info%notfound then
403 hr_utility.trace('No Archived Person Found');
404 else
405
406 select pay_assignment_actions_s.nextval
407 into lockingactid
408 from dual;
409
410 hr_nonrun_asact.insact(lockingactid,
411 l_primary_asg,
412 pactid,
413 chunk,
414 l_pre_organization_id);
415
416 hr_utility.trace('after hr_nonrun_asact.insact'||to_char(l_asg_id));
417
418 lv_serial_number := 'QC' ||lpad(to_char(ln_arch_asgact_id),14,0)||
419 lpad(to_char(ln_arch_pact_id),14,0);
420
421 hr_utility.trace('lv_serial_number :' ||lv_serial_number);
422
423 update pay_assignment_actions paa
424 set paa.serial_number = lv_serial_number
425 where paa.assignment_action_id = lockingactid;
426
427 l_prev_person_id := l_person_id;
428 l_prev_pre_org_id := l_pre_org_id;
429
430 end if;
431 close c_archived_person_info;
432
433 end if;
434
435 end loop;
436
437 if l_asg_set_id is not null then
438 close c_all_asg_in_asgset;
439 elsif lv_per_id is not null then
440 close c_single_asg;
441 else
442 close c_all_asg;
443 end if;
444
445 else
446 close c_first_tax_unit_id;
447 hr_utility.trace('No GRE for this PRE ');
448
449 end if;
450
451 end loop;
452
453 close c_all_pres;
454 else
455 open c_first_tax_unit_id(l_pre_org_id);
456 fetch c_first_tax_unit_id into l_tax_unit_id;
457
458 if c_first_tax_unit_id%FOUND then
459 close c_first_tax_unit_id;
460
461 l_pre_organization_id := to_number(l_pre_org_id);
462
466 elsif lv_per_id is not null then
463 if l_asg_set_id is not null then
464 open c_all_asg_in_asgset(l_year_start,
465 l_year_end);
467 open c_single_asg(l_year_start,
468 l_year_end, lv_per_id);
469
470 else
471 open c_all_asg(l_year_start, l_year_end);
472 end if;
473
474 loop
475
476 if l_asg_set_id is not null then
477 fetch c_all_asg_in_asgset into l_asg_id, l_person_id;
478 exit when c_all_asg_in_asgset%notfound;
479 elsif lv_per_id is not null then
480 fetch c_single_asg into l_asg_id, l_person_id;
481 exit when c_single_asg%notfound;
482 else
483 fetch c_all_asg into l_asg_id, l_person_id;
484 exit when c_all_asg%notfound;
485 end if;
486
487 if (l_person_id = l_prev_person_id and
488 l_pre_org_id = l_prev_pre_org_id) then
489
490 hr_utility.trace('Not creating assignment action');
491
492 else
493
494 /* Get the primary assignment as the primary
495 assignment is the assignment_id that is
496 always archived. Must check against this
497 assignment when checking for archived person */
498
499 open c_get_asg_id(l_person_id);
500 fetch c_get_asg_id into l_primary_asg;
501
502 if c_get_asg_id%NOTFOUND then
503 close c_get_asg_id;
504 hr_utility.raise_error;
505 else
506 close c_get_asg_id;
507 end if;
508
509 open c_archived_person_info(l_person_id,
510 l_primary_asg,
511 l_pre_org_id);
512 fetch c_archived_person_info
513 into ln_arch_asgact_id,
514 ln_arch_pact_id;
515 if c_archived_person_info%notfound then
516 hr_utility.trace('No Archived Person Found');
517 else
518
519 select pay_assignment_actions_s.nextval
520 into lockingactid
521 from dual;
522
523 hr_nonrun_asact.insact(lockingactid,
524 l_primary_asg,
525 pactid,
526 chunk,
527 l_pre_organization_id);
528
529 hr_utility.trace('after calling hr_nonrun_asact.insact '||to_char(lockingactid));
530
531 lv_serial_number := 'QC' ||lpad(to_char(ln_arch_asgact_id),14,0)||
532 lpad(to_char(ln_arch_pact_id),14,0);
533
534 hr_utility.trace('lv_serial_number :' ||lv_serial_number);
535
536 update pay_assignment_actions paa
537 set paa.serial_number = lv_serial_number
538 where paa.assignment_action_id = lockingactid;
539
540 l_prev_person_id := l_person_id;
541 l_prev_pre_org_id := l_pre_org_id;
542
543 end if;
544 close c_archived_person_info;
545
546 end if;
547
548 end loop;
549
550 if l_asg_set_id is not null then
551 close c_all_asg_in_asgset;
552 elsif lv_per_id is not null then
553 close c_single_asg;
554 else
555 close c_all_asg;
556 end if;
557
558 hr_utility.trace('End of cursor c_all_asg 12');
559 else
560 close c_first_tax_unit_id;
561 hr_utility.trace('No GRE for this PRE ');
562 end if;
563 end if;
564 end action_creation;
565 /*
566 ---------------------------------- sort_action ----------------------------------
567 */
568 procedure sort_action
569 (payactid in varchar2, /* payroll action id */
570 sqlstr in out nocopy varchar2, /* string holding the sql statement */
571 len out nocopy number) /* length of the sql string */
572 is
573 begin
574 hr_utility.trace('Start of Sort_Action 1');
575
576 sqlstr := 'select paa1.rowid /* we need the row id of the assignment actions that are created by PYUGEN */
577 from hr_all_organization_units hou1,
578 hr_all_organization_units hou,
579 hr_locations_all loc,
580 per_all_people_f ppf,
581 per_all_assignments_f paf,
582 pay_assignment_actions paa1,
583 pay_payroll_actions ppa1
584 where ppa1.payroll_action_id = :pactid
585 and paa1.payroll_action_id = ppa1.payroll_action_id
586 and paa1.assignment_id = paf.assignment_id
590 and ppa1.effective_date >= paf.effective_start_date
587 and paf.assignment_type = ''E''
588 and paf.primary_flag = ''Y''
589 and paf.business_group_id = ppa1.business_group_id
591 and hou.organization_id = paa1.tax_unit_id
592 and loc.location_id = paf.location_id
593 and hou1.organization_id = paf.organization_id
594 and ppf.person_id = paf.person_id
595 and ppa1.effective_date between
596 ppf.effective_start_date and ppf.effective_end_date
597 and paf.effective_end_date = (
598 select max(paaf2.effective_end_date)
599 from per_all_assignments_f paaf2
600 where paaf2.assignment_id = paf.assignment_id
601 and paaf2.effective_start_date <= ppa1.effective_date
602 )
603 order by
604 decode(pay_ca_rl2_reg.get_parameter(''P_S1'',ppa1.legislative_parameters), ''RL1_PRE'',hou.name,
605 ''RL1_ORG'',hou1.name,
606 ''RL1_LOC'',loc.location_code,null)
607 ,decode(pay_ca_rl2_reg.get_parameter(''P_S2'',ppa1.legislative_parameters), ''RL1_PRE'',hou.name,
608 ''RL1_ORG'',hou1.name,
609 ''RL1_LOC'',loc.location_code,null)
610 ,decode(pay_ca_rl2_reg.get_parameter(''P_S3'',ppa1.legislative_parameters), ''RL1_PRE'',hou.name,
611 ''RL1_ORG'',hou1.name,
612 ''RL1_LOC'',loc.location_code,null)
613 ,ppf.last_name,ppf.first_name';
614
615 len := length(sqlstr); -- return the length of the string.
616 hr_utility.trace('End of Sort_Action 2');
617 end sort_action;
618 /*
619 ------------------------------ get_parameter -------------------------------
620 */
621 function get_parameter(name in varchar2,
622 parameter_list varchar2) return varchar2
623 is
624 start_ptr number;
625 end_ptr number;
626 token_val pay_payroll_actions.legislative_parameters%type;
627 par_value pay_payroll_actions.legislative_parameters%type;
628 begin
629
630 token_val := name||'=';
631
632 start_ptr := instr(parameter_list, token_val) + length(token_val);
633 end_ptr := instr(parameter_list, ' ',start_ptr);
634
635 /* if there is no spaces use then length of the string */
636 if end_ptr = 0 then
637 end_ptr := length(parameter_list)+1;
638 end if;
639
640 /* Did we find the token */
641 if instr(parameter_list, token_val) = 0 then
642 par_value := NULL;
643 else
644 par_value := substr(parameter_list, start_ptr, end_ptr - start_ptr);
645 end if;
646
647 return par_value;
648
649 end get_parameter;
650
651 function get_label(p_lookup_type in varchar2,
652 p_lookup_code in varchar2) return varchar2 is
653
654 l_meaning hr_lookups.meaning%TYPE;
655
656 CURSOR cur_get_meaning IS
657 SELECT hl.meaning
658 FROM hr_lookups hl
659 WHERE hl.lookup_type = p_lookup_type AND
660 hl.lookup_code = p_lookup_code;
661
662 BEGIN
663
664 OPEN cur_get_meaning;
665 FETCH cur_get_meaning
666 INTO l_meaning;
667 if cur_get_meaning%NOTFOUND then
668 l_meaning := NULL;
669 end if;
670
671 CLOSE cur_get_meaning;
672
673 RETURN l_meaning;
674
675 END get_label; -- get_label
676
677 /* To get primary address of an employee */
678 /* Address line 1 to 3 are normal address lines */
679 /* Address Line 4 = City + Province Code + Postal Code */
680 /* Address Line 5 = Country Code */
681 /* Address Line 6 = Country Name */
682 /* Address Line 7 = Town or City */
683 /* Address Line 8 = Province Code */
684 /* Address Line 9 = Postal Code */
685
686 function get_primary_address(p_person_id in Number,
687 p_effective_date in date
688 ) return PrimaryAddress IS
689
690 cursor csr_address( p_person_id in number,
691 p_effective_date in date) is
692 select addr.address_line1
693 ,addr.address_line2
694 ,addr.address_line3
695 ,rtrim(substr(addr.town_or_city,1,23)) ||' '||
696 decode(addr.country, 'CA', addr.region_1, 'US', addr.region_2,
697 addr.region_1 )
698 ||' '|| addr.country -- Bug 4134616
699 ||' '|| addr.postal_code address_line4
700 ,addr.country address_line5 -- Country Code
701 ,country.territory_short_name address_line6 -- Country Name
702 ,addr.town_or_city Town_or_City
703 ,decode(addr.country, 'CA', addr.region_1,
704 'US', addr.region_2, addr.region_1 ) Province
705 ,addr.postal_code Postal_Code
706 from per_addresses addr
707 ,fnd_territories_vl country
708 where addr.person_id = p_person_id
709 and addr.primary_flag = 'Y'
710 and p_effective_date between
711 addr.date_from and nvl(addr.date_to, p_effective_date)
712 and country.territory_code = addr.country;
713
714 addr PrimaryAddress;
715
716 begin
717
718 open csr_address(p_person_id,p_effective_date);
719 fetch csr_address into addr;
720 close csr_address;
721
722 return addr;
723
724 end get_primary_address;
725
726 end pay_ca_rl2_reg;
[Home] [Help]