[Home] [Help]
PACKAGE BODY: APPS.PAY_US_TRR_ENGINE_PKG
Source
1 package body PAY_US_TRR_ENGINE_PKG as
2 /* $Header: pyusteng.pkb 120.0 2005/05/29 09:59:04 appldev noship $ */
3
4 procedure federal_trr(errbuf OUT nocopy VARCHAR2,
5 retcode OUT nocopy NUMBER,
6 p_business_group number ,
7 p_start_date varchar2,
8 p_end_date varchar2,
9 p_gre number,
10 p_federal varchar2,
11 p_state varchar2,
12 p_dimension varchar2)
13 is
14 --
15 --
16 /* ORIGINAL CODE
17 cursor gre_sizes(c_business_group_id number,
18 c_tax_unit_id number,
19 c_jurisdiction_code varchar2)
20 is
21 select count(*) gre_size, puar.tax_unit_id gre_id, htu.name gre_name
22 from pay_us_asg_reporting puar,
23 hr_tax_units_v htu
24 where puar.tax_unit_id=htu.tax_unit_id
25 and htu.business_group_id=c_business_group_id
26 and substr(puar.jurisdiction_code,1,2)=
27 nvl(c_jurisdiction_code,substr(puar.jurisdiction_code,1,2))
28 and htu.tax_unit_id=nvl(c_tax_unit_id,htu.tax_unit_id)
29 group by puar.tax_unit_id,htu.name
30 order by count(*);
31 */
32
33 cursor gre_sizes_gre is
34 select count(*) gre_size, info.organization_id gre_id, hoi.name gre_name
35 from hr_organization_units hoi,
36 hr_organization_information info
37 where hoi.organization_id = info.organization_id
38 and info.org_information_context = 'CLASS'
39 and info.org_information1 = 'HR_LEGAL'
40 and hoi.business_group_id = p_business_group
41 and info.organization_id = p_gre
42 group by info.organization_id,hoi.name
43 order by count(*);
44
45 cursor gre_sizes is
46 select count(*) gre_size, info.organization_id gre_id, hoi.name gre_name
47 from hr_organization_units hoi,
48 hr_organization_information info
49 where hoi.organization_id = info.organization_id
50 and info.org_information_context = 'CLASS'
51 and info.org_information1 = 'HR_LEGAL'
52 and hoi.business_group_id = p_business_group
53 and trunc(nvl(date_to,fnd_date.canonical_to_date(p_start_date)),'Y') >= trunc(fnd_date.canonical_to_date(p_start_date),'Y')
54 group by info.organization_id,hoi.name
55 order by count(*);
56
57 cursor gre_sizes_gre_state is
58 select count(*) gre_size, info.organization_id gre_id, hoi.name gre_name
59 from hr_organization_units hoi,
60 hr_organization_information info
61 where hoi.organization_id = info.organization_id
62 and info.org_information_context = 'CLASS'
63 and info.org_information1 = 'HR_LEGAL'
64 and hoi.business_group_id = p_business_group
65 and info.organization_id = p_gre
66 and exists (select '1' from pay_us_asg_reporting puar
67 where puar.tax_unit_id = info.organization_id
68 and puar.jurisdiction_code like p_state||'%')
69 group by info.organization_id,hoi.name
70 order by count(*);
71
72 cursor gre_sizes_state is
73 select /*+ INDEX(hoi.hao HR_ORGANIZATION_UNITS_FK1) */
74 count(*) gre_size,info.organization_id gre_id, hoi.name gre_name
75 from hr_organization_units hoi,
76 hr_organization_information info
77 where hoi.organization_id = info.organization_id
78 and info.org_information_context = 'CLASS'
79 and info.org_information1 = 'HR_LEGAL'
80 and hoi.business_group_id = p_business_group
81 and trunc(nvl(date_to,fnd_date.canonical_to_date(p_start_date)),'Y') >= trunc(fnd_date.canonical_to_date(p_start_date),'Y')
82 and exists (select '1' from pay_us_asg_reporting puar
83 where puar.tax_unit_id = info.organization_id
84 and puar.jurisdiction_code like p_state||'%')
85 group by info.organization_id,hoi.name
86 order by count(*);
87
88 gre_list gre_info_list;
89 list_index number:=1;
90 start_index number:=1;
91 end_index number:=1;
92 l_req_id number;
93 copies_buffer varchar2(80) := null;
94 print_buffer varchar2(80) := null;
95 printer_buffer varchar2(80) := null;
96 style_buffer varchar2(80) := null;
97 save_buffer boolean := null;
98 save_result varchar2(1) := null;
99 req_id varchar2(80) := null;
100 x boolean;
101 x1 boolean;
102
103 l_valid_status varchar2(5);
104 l_program varchar2(100);
105 --
106 --
107 begin
108
109 --hr_utility.trace_on(null,'oracle');
110
111 -- initialise variable - 0 is SRS Success, 1 is SRS Warning, 2 is SRS Error
112 retcode := 0;
113 -- get printing info
114 req_id:=fnd_profile.value('CONC_REQUEST_ID');
115 print_buffer:=fnd_profile.value('CONC_PRINT_TOGETHER');
116 if (print_buffer is NULL)
117 then print_buffer:='N';
118 end if;
119
120 select number_of_copies,
121 printer,
122 print_style,
123 save_output_flag
124 into copies_buffer,
125 printer_buffer,
126 style_buffer,
127 save_result
128 from fnd_concurrent_requests
129 where request_id = fnd_number.canonical_to_number(req_id);
130
131
132 if (save_result='Y') then
133 save_buffer:=true;
134 elsif (save_result='N') then
135 save_buffer:=false;
136 else
137 save_buffer:=NULL;
138 end if;
139
140 -- logic to decide which report to fire
141 /*begin
142 select pdb.run_balance_status
143 into l_valid_status
144 from pay_defined_balances pdb,
145 pay_balance_types pbt,
146 pay_balance_dimensions pbd
147 where pdb.legislation_code = 'US'
148 and pdb.save_run_balance = 'Y'
149 and pdb.run_balance_status is not null
150 and pdb.balance_type_id = pbt.balance_type_id
151 and pbd.balance_dimension_id = pdb.balance_dimension_id
152 and pbt.balance_name = 'SIT Withheld'
153 and pbd.database_item_suffix = '_GRE_JD_RUN';
154 */
155 /* New code : In the table pay_balnace_validation the balance_load_date is
156 not Mendatory so we need to take a look at it.p_start_date
157 can have null value .
158 1. What should we do if the status of the column
159 is processing. Should we stop running TRR or should we
160 continue with the process "
161
162 */
163 -- Bug 3400857 : Logic to check valid balances is now moved to PAYUSFTR.rdf
164 /* select pbv.run_balance_status
165 into l_valid_status
166 from pay_defined_balances pdb,
167 pay_balance_types pbt,
168 pay_balance_validation pbv,
169 pay_balance_dimensions pbd
170 where pdb.legislation_code = 'US'
171 and pdb.save_run_balance = 'Y'
172 and pdb.balance_type_id = pbt.balance_type_id
173 and pbd.balance_dimension_id = pdb.balance_dimension_id
174 and pbt.balance_name = 'SIT Withheld'
175 and pbd.database_item_suffix = '_GRE_JD_RUN'
176 and pdb.defined_balance_id = pbv.defined_balance_id
177 and pbv.business_group_id = p_business_group
178 and nvl(pbv.balance_load_date,
179 fnd_date.canonical_to_date(p_start_date))
180 <= fnd_date.canonical_to_date(p_start_date);
181
182
183 if l_valid_status = 'V' then
184 --call the new report
185 l_program := 'PYFEDTRR_RB';
186
187 else
188 -- call the old report
189 l_program := 'PYFEDTRR';
190
191 end if;
192
193 exception when others then
194 -- call the old report
195 l_program := 'PYFEDTRR';
196 end;*/
197 -- end logic
198 l_program := 'PYFEDTRR';
199 -- l_program := 'PYFEDTRR_RB';
200 -- read data into table
201 if (p_gre is null and p_state is null ) then
202
203 for grerec in gre_sizes loop
204 gre_list(list_index).gre_size :=grerec.gre_size;
205 gre_list(list_index).gre_id :=grerec.gre_id;
206 gre_list(list_index).gre_name :=grerec.gre_name;
207
208 list_index:=list_index+1;
209 end loop;
210
211 elsif (p_gre is not null and p_state is null ) then
212
213 for grerec in gre_sizes_gre loop
214 gre_list(list_index).gre_size :=grerec.gre_size;
215 gre_list(list_index).gre_id :=grerec.gre_id;
216 gre_list(list_index).gre_name :=grerec.gre_name;
217
218 list_index:=list_index+1;
219 end loop;
220
221 elsif (p_gre is not null and p_state is not null) then
222
223 for grerec in gre_sizes_gre_state loop
224 gre_list(list_index).gre_size :=grerec.gre_size;
225 gre_list(list_index).gre_id :=grerec.gre_id;
226 gre_list(list_index).gre_name :=grerec.gre_name;
227
228 list_index:=list_index+1;
229 end loop;
230
231 else /* (p_gre is null and p_state is not null) */
232
233 for grerec in gre_sizes_state loop
234 gre_list(list_index).gre_size :=grerec.gre_size;
235 gre_list(list_index).gre_id :=grerec.gre_id;
236 gre_list(list_index).gre_name :=grerec.gre_name;
237
238 list_index:=list_index+1;
239 end loop;
240
241 end if;
242
243 /* ORIGINAL CODE
244 for grerec in gre_sizes(p_business_group,p_gre,p_state) loop
245 gre_list(list_index).gre_size :=grerec.gre_size;
246 gre_list(list_index).gre_id :=grerec.gre_id;
247 gre_list(list_index).gre_name :=grerec.gre_name;
248
249 list_index:=list_index+1;
250 end loop;
251 */
252
253 -- get start of list
254 start_index:=1;
255 -- get end of list
256 end_index:=list_index-1;
257 -- loop round from both ends working inwards
258 while (start_index<end_index) loop
259 -- set print options
260 x:=FND_REQUEST.set_print_options(
261 printer => printer_buffer,
262 style => style_buffer,
263 copies => copies_buffer,
264 save_output => save_buffer,
265 print_together => print_buffer);
266
267 -- Bug 3487186 Added by ssmukher
268 x1 := fnd_Request.USE_CURRENT_NOTIFICATION;
269
270 -- submit requests for report
271 l_req_id:=fnd_request.submit_request(
272 application => 'PAY',
273 program => l_program,
274 argument1 => gre_list(start_index).gre_name,
275 argument2 => p_business_group,
276 argument3 => p_start_date,
277 argument4 => p_end_date,
278 argument5 => gre_list(start_index).gre_id,
279 argument6 => p_federal,
280 argument7 => p_state,
281 argument8 => p_dimension);
282 -- set print options
283 x:=FND_REQUEST.set_print_options(
284 printer => printer_buffer,
285 style => style_buffer,
286 copies => copies_buffer,
287 save_output => save_buffer,
288 print_together => print_buffer);
289 --Bug 3487186 Add by ssmukher
290 x1 := fnd_Request.USE_CURRENT_NOTIFICATION;
291
292 l_req_id:=fnd_request.submit_request(
293 application => 'PAY',
294 program => l_program,
295 argument1 => gre_list(end_index).gre_name,
296 argument2 => p_business_group,
297 argument3 => p_start_date,
298 argument4 => p_end_date,
299 argument5 => gre_list(end_index).gre_id,
300 argument6 => p_federal,
301 argument7 => p_state,
302 argument8 => p_dimension);
303 -- get next values
304 start_index:=start_index+1;
305 end_index:=end_index-1;
306 --
307
308 end loop;
309 -- submit for middle value in list if odd number of gre's
310 if (start_index=end_index) then
311 -- set print options
312 x:=FND_REQUEST.set_print_options(
313 printer => printer_buffer,
314 style => style_buffer,
315 copies => copies_buffer,
316 save_output => save_buffer,
317 print_together => print_buffer);
318
319 -- Bug 3487186 Added by ssmukher
320 x1 := fnd_Request.USE_CURRENT_NOTIFICATION;
321 l_req_id:=fnd_request.submit_request(
322 application => 'PAY',
323 program => l_program,
324 argument1 => gre_list(start_index).gre_name,
325 argument2 => p_business_group,
326 argument3 => p_start_date,
327 argument4 => p_end_date,
328 argument5 => gre_list(start_index).gre_id,
329 argument6 => p_federal,
330 argument7 => p_state,
331 argument8 => p_dimension);
332 end if;
333 EXCEPTION
334 --
335 WHEN hr_utility.hr_error THEN
336 --
337 -- Set up error message and error return code.
338 --
339 --hr_utility.trace('in the exception');
340 errbuf := hr_utility.get_message;
341 retcode := 2;
342 --
343 --
344 WHEN others THEN
345 --
346 -- Set up error message and return code.
347 --
348 errbuf := sqlerrm;
349 retcode := 2;
350 end federal_trr;
351
352 procedure state_trr
353 is
354 begin
355
356 null;
357 end state_trr;
358 --
359 procedure local_trr(errbuf out nocopy varchar2
360 ,retcode out nocopy number
361 ,p_business_group number
362 ,p_start_date varchar2
363 ,p_end_date varchar2
364 ,p_gre number
365 ,p_state varchar2
366 ,p_locality_type varchar2
367 ,p_is_city varchar2
368 ,p_city varchar2
369 ,p_is_county varchar2
370 ,p_county varchar2
371 ,p_is_school varchar2
372 ,p_school varchar2
373 ,p_sort_option_1 varchar2
374 ,p_sort_option_2 varchar2
375 ,p_sort_option_3 varchar2
376 ,p_dimension varchar2)
377 is
378 --
379 /* ORIGINAL CODE
380 cursor gre_sizes(c_business_group_id number,
381 c_tax_unit_id number,
382 c_jurisdiction_code varchar2)
383 is
384 select count(*) gre_size,puar.tax_unit_id gre_id,htu.name gre_name
385 from pay_us_asg_reporting puar,
389 and substr(puar.jurisdiction_code,1,2)=
386 hr_tax_units_v htu
387 where puar.tax_unit_id=htu.tax_unit_id
388 and htu.business_group_id=c_business_group_id
390 nvl(c_jurisdiction_code,substr(puar.jurisdiction_code,1,2))
391 and htu.tax_unit_id=nvl(c_tax_unit_id,htu.tax_unit_id)
392 group by puar.tax_unit_id,htu.name
393 order by count(*);
394 */
395
396 cursor gre_sizes_gre is
397 select count(*) gre_size, info.organization_id gre_id, hoi.name gre_name
398 from hr_organization_units hoi,
399 hr_organization_information info
400 where hoi.organization_id = info.organization_id
401 and info.org_information_context = 'CLASS'
402 and info.org_information1 = 'HR_LEGAL'
403 and hoi.business_group_id = p_business_group
404 and info.organization_id = p_gre
405 group by info.organization_id,hoi.name
406 order by count(*);
407
408 cursor gre_sizes is
409 select count(*) gre_size, info.organization_id gre_id, hoi.name gre_name
410 from hr_organization_units hoi,
411 hr_organization_information info
412 where hoi.organization_id = info.organization_id
413 and info.org_information_context = 'CLASS'
414 and info.org_information1 = 'HR_LEGAL'
415 and hoi.business_group_id = p_business_group
416 and trunc(nvl(date_to,fnd_date.canonical_to_date(p_start_date)),'Y') >= trunc(fnd_date.canonical_to_date(p_start_date),'Y')
417 group by info.organization_id,hoi.name
418 order by count(*);
419
420 cursor gre_sizes_gre_juri(cv_jurisdiction varchar2) is
421 select count(*) gre_size, info.organization_id gre_id, hoi.name gre_name
422 from hr_organization_units hoi,
423 hr_organization_information info
424 where hoi.organization_id = info.organization_id
425 and info.org_information_context = 'CLASS'
426 and info.org_information1 = 'HR_LEGAL'
427 and hoi.business_group_id = p_business_group
428 and info.organization_id = p_gre
429 and exists (select '1' from pay_us_asg_reporting puar
430 where puar.tax_unit_id = info.organization_id
431 and puar.jurisdiction_code like cv_jurisdiction)
432 group by info.organization_id,hoi.name
433 order by count(*);
434
435 cursor gre_sizes_juri(cv_jurisdiction varchar2) is
436 select /*+ INDEX(hoi.hao HR_ORGANIZATION_UNITS_FK1) */
437 count(*) gre_size, info.organization_id gre_id, hoi.name gre_name
438 from hr_organization_units hoi,
439 hr_organization_information info
440 where hoi.organization_id = info.organization_id
441 and info.org_information_context = 'CLASS'
442 and info.org_information1 = 'HR_LEGAL'
443 and hoi.business_group_id = p_business_group
444 and trunc(nvl(date_to,fnd_date.canonical_to_date(p_start_date)),'Y') >= trunc(fnd_date.canonical_to_date(p_start_date),'Y')
445 and exists (select '1' from pay_us_asg_reporting puar
446 where puar.tax_unit_id = info.organization_id
447 and puar.jurisdiction_code like cv_jurisdiction)
448 group by info.organization_id,hoi.name
449 order by count(*);
450
451 gre_list gre_info_list;
452 list_index number:=1;
453 start_index number:=1;
454 end_index number:=1;
455 l_req_id number;
456 copies_buffer varchar2(80) := null;
457 print_buffer varchar2(80) := null;
458 printer_buffer varchar2(80) := null;
459 style_buffer varchar2(80) := null;
460 save_buffer boolean := null;
461 save_result varchar2(1) := null;
462 req_id varchar2(80) := null;
463 x boolean;
464 x1 boolean;
465
466 c_jurisdiction varchar2(15);
467 /* Bug 3376256 added local variable to hold the substring of p_county */
468 lv_County varchar2(15);
469 --
470 --
471 begin
472 -- initialise variable - 0 is SRS Success, 1 is SRS Warning, 2 is SRS Error
473 retcode := 0;
474 -- get printing info
475 req_id:=fnd_profile.value('CONC_REQUEST_ID');
476 print_buffer:=fnd_profile.value('CONC_PRINT_TOGETHER');
477 if (print_buffer is NULL)
478 then print_buffer:='N';
479 end if;
480
481 select number_of_copies,
482 printer,
483 print_style,
484 save_output_flag
485 into copies_buffer,
486 printer_buffer,
487 style_buffer,
488 save_result
489 from fnd_concurrent_requests
490 where request_id = fnd_number.canonical_to_number(req_id);
491
492 if (save_result='Y') then
493 save_buffer:=true;
494 elsif (save_result='N') then
495 save_buffer:=false;
496 else
497 save_buffer:=NULL;
498 end if;
499
500 /* New Logic */
501
502 If p_locality_type is null then
503
504 if p_state is null then
505
506 c_jurisdiction := null;
507 else
508 c_jurisdiction := p_state||'%';
509
510 end if;
511
512 else /* p_locality_type */
513
514 if p_city is null then
515
516 if p_county is null then
517
518 if p_school is null then
519
520 c_jurisdiction := null;
521 else
522 c_jurisdiction := p_school;
523
524 end if; /* school */
525
529
526 else /* county */
527
528 /* Bug 3376256: select the first 6 characters from p_county and assign to c_jurisdiction */
530 Begin
531 Select Substr(p_county, 1, 6)
532 Into lv_County
533 From Dual;
534 Exception
535 When Others Then
536 lv_County := Null;
537 End;
538 c_jurisdiction := lv_County||'%';
539
540 /* End Bug 3376256 */
541 end if; /* county */
542
543 else /* city */
544 c_jurisdiction := p_city;
545
546 end if; /* city */
547
548 end if; /* p_locality_type */
549
550 if (p_gre is null and c_jurisdiction is null ) then
551
552 for grerec in gre_sizes loop
553 gre_list(list_index).gre_size :=grerec.gre_size;
554 gre_list(list_index).gre_id :=grerec.gre_id;
555 gre_list(list_index).gre_name :=grerec.gre_name;
556
557 list_index:=list_index+1;
558 end loop;
559
560 elsif (p_gre is not null and c_jurisdiction is null ) then
561
562 for grerec in gre_sizes_gre loop
563 gre_list(list_index).gre_size :=grerec.gre_size;
564 gre_list(list_index).gre_id :=grerec.gre_id;
565 gre_list(list_index).gre_name :=grerec.gre_name;
566
567 list_index:=list_index+1;
568 end loop;
569
570 elsif (p_gre is not null and c_jurisdiction is not null) then
571
572 for grerec in gre_sizes_gre_juri(c_jurisdiction ) loop
573 gre_list(list_index).gre_size :=grerec.gre_size;
574 gre_list(list_index).gre_id :=grerec.gre_id;
575 gre_list(list_index).gre_name :=grerec.gre_name;
576
577 list_index:=list_index+1;
578 end loop;
579
580 else /* (p_gre is null and c_jurisdiction is not null) */
581
582 for grerec in gre_sizes_juri(c_jurisdiction ) loop
583 gre_list(list_index).gre_size :=grerec.gre_size;
584 gre_list(list_index).gre_id :=grerec.gre_id;
585 gre_list(list_index).gre_name :=grerec.gre_name;
586
587 list_index:=list_index+1;
588 end loop;
589
590 end if;
591
592 -- read data into table
593 /* ORIGINAL CODE
594 for grerec in gre_sizes(p_business_group,p_gre,p_state) loop
595 gre_list(list_index).gre_size:=grerec.gre_size;
596 gre_list(list_index).gre_id :=grerec.gre_id;
597 gre_list(list_index).gre_name :=grerec.gre_name;
598
599 list_index:=list_index+1;
600 end loop;
601
602 */
603 -- get start of list
604 start_index:=1;
605 -- get end of list
606 end_index:=list_index-1;
607 -- loop round from both ends working inwards
608 while (start_index<end_index) loop
609 -- set print options
610 x:=FND_REQUEST.set_print_options(
611 printer => printer_buffer,
612 style => style_buffer,
613 copies => copies_buffer,
614 save_output => save_buffer,
615 print_together => print_buffer);
616
617 -- Bug 3487186 Added by ssmukher
618 x1 := fnd_Request.USE_CURRENT_NOTIFICATION;
619 -- submit requests for report
620 l_req_id:=fnd_request.submit_request(
621 application => 'PAY',
622 program => 'PYLOCTRR',
623 argument1 => gre_list(start_index).gre_name,
624 argument2 => p_business_group,
625 argument3 => p_start_date,
626 argument4 => p_end_date,
627 argument5 => gre_list(start_index).gre_id,
628 argument6 => p_state,
629 argument7 => p_locality_type,
630 argument8 => p_is_city,
631 argument9 => p_city,
632 argument10 => p_is_county,
633 argument11 => p_county,
634 argument12 => p_is_school,
635 argument13 => p_school,
636 argument14 => p_sort_option_1,
637 argument15 => p_sort_option_2,
638 argument16 => p_sort_option_3,
639 argument17 => p_dimension);
640 -- set print options
641 x:=FND_REQUEST.set_print_options(
642 printer => printer_buffer,
643 style => style_buffer,
644 copies => copies_buffer,
645 save_output => save_buffer,
646 print_together => print_buffer);
647
648 -- Bug 3487186 Added by ssmukher
649 x1 := fnd_Request.USE_CURRENT_NOTIFICATION;
650
651 l_req_id:=fnd_request.submit_request(
652 application => 'PAY',
653 program => 'PYLOCTRR',
654 argument1 => gre_list(end_index).gre_name,
655 argument2 => p_business_group,
656 argument3 => p_start_date,
657 argument4 => p_end_date,
658 argument5 => gre_list(end_index).gre_id,
662 argument9 => p_city,
659 argument6 => p_state,
660 argument7 => p_locality_type,
661 argument8 => p_is_city,
663 argument10 => p_is_county,
664 argument11 => p_county,
665 argument12 => p_is_school,
666 argument13 => p_school,
667 argument14 => p_sort_option_1,
668 argument15 => p_sort_option_2,
669 argument16 => p_sort_option_3,
670 argument17 => p_dimension);
671 -- get next values
672 start_index:=start_index+1;
673 end_index:=end_index-1;
674 --
675
676 end loop;
677 -- submit for middle value in list if odd number of gre's
678 if (start_index=end_index) then
679 -- set print options
680 x:=FND_REQUEST.set_print_options(
681 printer => printer_buffer,
682 style => style_buffer,
683 copies => copies_buffer,
684 save_output => save_buffer,
685 print_together => print_buffer);
686
687 -- Bug 3487186 Added by ssmukher
688 x1 := fnd_Request.USE_CURRENT_NOTIFICATION;
689 l_req_id:=fnd_request.submit_request(
690 application => 'PAY',
691 program => 'PYLOCTRR',
692 argument1 => gre_list(start_index).gre_name,
693 argument2 => p_business_group,
694 argument3 => p_start_date,
695 argument4 => p_end_date,
696 argument5 => gre_list(start_index).gre_id,
697 argument6 => p_state,
698 argument7 => p_locality_type,
699 argument8 => p_is_city,
700 argument9 => p_city,
701 argument10 => p_is_county,
702 argument11 => p_county,
703 argument12 => p_is_school,
704 argument13 => p_school,
705 argument14 => p_sort_option_1,
706 argument15 => p_sort_option_2,
707 argument16 => p_sort_option_3,
708 argument17 => p_dimension);
709 end if;
710 EXCEPTION
711 --
712 WHEN hr_utility.hr_error THEN
713 --
714 -- Set up error message and error return code.
715 --
716 errbuf := hr_utility.get_message;
717 retcode := 2;
718
719 WHEN others THEN
720 -- Set up error message and return code.
721 --
722 errbuf := sqlerrm;
723 retcode := 2;
724 end local_trr;
725 end PAY_US_TRR_ENGINE_PKG;