[Home] [Help]
PACKAGE BODY: APPS.PAY_US_GEOCODE_REPORT_PKG
Source
1 PACKAGE BODY pay_us_geocode_report_pkg AS
2 /* $Header: pyusgeoa.pkb 120.2.12010000.1 2008/07/27 23:51:29 appldev ship $ */
3 --
4 /*
5 ******************************************************************
6 * *
7 * Copyright (C) 1996 Oracle Corporation. *
8 * All rights reserved. *
9 * *
10 * This material has been provided pursuant to an agreement *
11 * containing restrictions on its use. The material is also *
12 * protected by copyright law. No part of this material may *
13 * be copied or distributed, transmitted or transcribed, in *
14 * any form or by any means, electronic, mechanical, magnetic, *
15 * manual, or otherwise, or disclosed to third parties without *
16 * the express written permission of Oracle Corporation, *
17 * 500 Oracle Parkway, Redwood City, CA, 94065. *
18 * *
19 ******************************************************************
20
21 Name : pay_us_geocode_report_pkg
22
23 Description : Package for the geocode upgrade reporting
24 - HTML
25
26 Change List
27 -----------
28 Date Name Vers Bug No Description
29 ---- ---- ------ ------- -----------
30 12-SEP-2005 tclewis 115.0 Created.
31
32 */
33
34 /************************************************************
35 ** Local Package Variables
36 ************************************************************/
37 gv_title VARCHAR2(100);
38
39 gc_csv_delimiter VARCHAR2(1) := ',';
40 gc_csv_data_delimiter VARCHAR2(1) := '"';
41
42 gv_html_start_data VARCHAR2(5) := '<td>' ;
43 gv_html_end_data VARCHAR2(5) := '</td>' ;
44
45 gv_package_name VARCHAR2(50) := 'pay_us_geocode_report_pkg';
46
47
48 /******************************************************************
49 ** Function Returns the formated input string based on the
50 ** Output format. If the format is CSV then the values are returned
51 ** seperated by comma (,). If the format is HTML then the returned
52 ** string as the HTML tags. The parameter p_bold only works for
53 ** the HTML format.
54 ******************************************************************/
55 FUNCTION formated_data_string
56 (p_input_string in varchar2
57 ,p_output_file_type in varchar2
58 ,p_bold in varchar2 default 'N'
59 )
60 RETURN VARCHAR2
61 IS
62
63 lv_format varchar2(1000);
64
65 BEGIN
66 hr_utility.set_location(gv_package_name || '.formated_data_string', 10);
67 if p_output_file_type = 'CSV' then
68 hr_utility.set_location(gv_package_name || '.formated_data_string', 20);
69 lv_format := gc_csv_data_delimiter || p_input_string ||
70 gc_csv_data_delimiter || gc_csv_delimiter;
71 elsif p_output_file_type = 'HTML' then
72 if p_input_string is null then
73 hr_utility.set_location(gv_package_name || '.formated_data_string', 30);
74 lv_format := gv_html_start_data || '&'||'nbsp;' || gv_html_end_data;
75 else
76 if p_bold = 'Y' then
77 hr_utility.set_location(gv_package_name || '.formated_data_string', 40);
78 lv_format := gv_html_start_data || '<b> ' || p_input_string
79 || '</b>' || gv_html_end_data;
80 else
81 hr_utility.set_location(gv_package_name || '.formated_data_string', 50);
82 lv_format := gv_html_start_data || p_input_string || gv_html_end_data;
83 end if;
84 end if;
85 end if;
86
87 hr_utility.set_location(gv_package_name || '.formated_data_string', 60);
88 return lv_format;
89
90 END formated_data_string;
91
92
93 /************************************************************
94 ** Function returns the string with the HTML Header tags
95 ************************************************************/
96 FUNCTION formated_header_string
97 (p_input_string in varchar2
98 ,p_output_file_type in varchar2
99 )
100 RETURN VARCHAR2
101 IS
102
103 lv_format varchar2(1000);
104
105 BEGIN
106 hr_utility.set_location(gv_package_name || '.formated_header_string', 10);
107 if p_output_file_type = 'CSV' then
108 hr_utility.set_location(gv_package_name || '.formated_header_string', 20);
109 lv_format := p_input_string;
110 elsif p_output_file_type = 'HTML' then
111 hr_utility.set_location(gv_package_name || '.formated_header_string', 30);
112 -- lv_format := '<HTML><HEAD> <CENTER> <H1> <B>' || p_input_string ||
113 -- '</B></H1></CENTER></HEAD>';
114 lv_format := '<HTML> <P> ' || p_input_string ||
115 '</P>';
116 end if;
117
118 hr_utility.set_location(gv_package_name || '.formated_header_string', 40);
119 return lv_format;
120
121 END formated_header_string;
122
123
124 /*****************************************************************
125 ** This procudure returns the Mandatory Static Labels.
126 *****************************************************************/
127 PROCEDURE formated_static_header(
128 p_output_file_type in varchar2
129 ,p_static_label1 out nocopy varchar2
130 )
131 IS
132
133 lv_format1 varchar2(32000);
134
135 BEGIN
136
137 hr_utility.set_location(gv_package_name || '.formated_static_header', 10);
138
139 lv_format1 :=
140 formated_data_string (p_input_string => 'Full Name'
141 ,p_bold => 'Y'
142 ,p_output_file_type => p_output_file_type) ||
143
144 formated_data_string (p_input_string => 'Assignment Id'
145 ,p_bold => 'Y'
146 ,p_output_file_type => p_output_file_type) ||
147 formated_data_string (p_input_string => 'Assignment Number'
148 ,p_bold => 'Y'
149 ,p_output_file_type => p_output_file_type) ||
150
151 formated_data_string (p_input_string =>'Error Description'
152 ,p_bold => 'Y'
153 ,p_output_file_type => p_output_file_type)
154 ;
155
156 hr_utility.set_location(gv_package_name || '.formated_static_header', 20);
157
158 p_static_label1 := lv_format1;
159 hr_utility.trace('Static Label1 = ' || lv_format1);
160 hr_utility.set_location(gv_package_name || '.formated_static_header', 40);
161
162 END;
163
164
165 /*****************************************************************
166 ** This procudure returns the Mandatory Static Labels.
167 *****************************************************************/
168 PROCEDURE formated_static_data (
169 p_full_name in varchar2
170 ,p_assignment_id in number
171 ,p_assignment_number in varchar2
172 ,p_juri_code_1 in varchar2
173 ,p_city_name_1 in varchar2
174 ,p_juri_code_2 in varchar2
175 ,p_city_name_2 in varchar2
176 ,p_table_updated in varchar2
177 ,p_error_description in varchar2
178 ,p_output_file_type in varchar2
179 ,p_static_data1 out nocopy varchar2
180 )
181 IS
182
183 lv_format1 VARCHAR2(32000);
184 lv_format2 VARCHAR2(32000);
185
186
187 BEGIN
188
189 hr_utility.set_location(gv_package_name || '.formated_static_data', 10);
190
191 if p_full_name is not NULL THEN
192
193 lv_format1 := lv_format1 ||
194 formated_data_string (p_input_string => p_full_name
195 ,p_output_file_type => p_output_file_type) ;
196 end if;
197
198 if p_assignment_id is not NULL then
199 lv_format1 := lv_format1 ||
200 formated_data_string (p_input_string => p_assignment_id
201 ,p_output_file_type => p_output_file_type);
202 end if;
203
204 if p_assignment_number is not NULL then
205 lv_format1 := lv_format1 ||
206 formated_data_string (p_input_string => p_assignment_number
207 ,p_output_file_type => p_output_file_type);
208 end if;
209
210 if p_error_description is not NULL then
211 lv_format1 := lv_format1 ||
212 formated_data_string (p_input_string => p_error_description
213 ,p_output_file_type => p_output_file_type);
214 end if;
215
216 if p_juri_code_1 is not NULL then
217 lv_format1 := lv_format1 ||
218 formated_data_string (p_input_string => p_juri_code_1
219 ,p_output_file_type => p_output_file_type);
220 end if;
221
222 if p_city_name_1 is not NULL then
223 lv_format1 := lv_format1 ||
224 formated_data_string (p_input_string => p_city_name_1
225 ,p_output_file_type => p_output_file_type);
226 end if;
227
228 if p_juri_code_2 is not NULL then
229 lv_format1 := lv_format1 ||
230 formated_data_string (p_input_string => p_juri_code_2
231 ,p_output_file_type => p_output_file_type);
232 end if;
233
234 if p_city_name_2 is not NULL then
235 lv_format1 := lv_format1 ||
236 formated_data_string (p_input_string => p_city_name_2
237 ,p_output_file_type => p_output_file_type);
238 end if;
239
240 if p_table_updated is not NULL then
241 lv_format1 := lv_format1 ||
242 formated_data_string (p_input_string => p_table_updated
243 ,p_output_file_type => p_output_file_type);
244 end if;
245
246
247 hr_utility.set_location(gv_package_name || '.formated_static_data', 30);
248
249
250 p_static_data1 := lv_format1;
251 hr_utility.trace('Static Data1 = ' || lv_format1);
252 hr_utility.set_location(gv_package_name || '.formated_static_data', 40);
253
254 END formated_static_data;
255 /*****************************************************************
256 ** This procudure returns the Mandatory Static Labels.
257 *****************************************************************/
258 PROCEDURE formated_static_data2 (
259 p_full_name in varchar2
260 ,p_assignment_id in number
261 ,p_assignment_number in varchar2
262 ,p_city_name in varchar2
263 ,p_county_name in varchar2
264 ,p_state_abbrev in varchar2
265 ,p_old_juri_code in varchar2
266 ,p_new_juri_code in varchar2
267 ,p_output_file_type in varchar2
268 ,p_static_data1 out nocopy varchar2
269 )
270 IS
271
272 lv_format1 VARCHAR2(32000);
273 lv_format2 VARCHAR2(32000);
274
275
276 BEGIN
277
278 hr_utility.set_location(gv_package_name || '.formated_static_data2', 10);
279 lv_format1 :=
280 formated_data_string (p_input_string => p_full_name
281 ,p_output_file_type => p_output_file_type) ||
282 formated_data_string (p_input_string => p_assignment_id
283 ,p_output_file_type => p_output_file_type) ||
284 formated_data_string (p_input_string => p_assignment_number
285 ,p_output_file_type => p_output_file_type) ;
286
287
288 if p_city_name is not NULL then
289 lv_format1 := lv_format1 ||
290 formated_data_string (p_input_string => p_city_name
291 ,p_output_file_type => p_output_file_type);
292 end if;
293
294 if p_county_name is not NULL then
295 lv_format1 := lv_format1 ||
296 formated_data_string (p_input_string => p_county_name
297 ,p_output_file_type => p_output_file_type);
298 end if;
299
300 if p_state_abbrev is not NULL then
301 lv_format1 := lv_format1 ||
302 formated_data_string (p_input_string => p_state_abbrev
303 ,p_output_file_type => p_output_file_type);
304 end if;
305
306 if p_old_juri_code is not NULL then
307 lv_format1 := lv_format1 ||
308 formated_data_string (p_input_string => p_old_juri_code
309 ,p_output_file_type => p_output_file_type);
310 end if;
311
312 if p_new_juri_code is not NULL then
313 lv_format1 := lv_format1 ||
314 formated_data_string (p_input_string => p_new_juri_code
315 ,p_output_file_type => p_output_file_type);
316 end if;
317
318
319 hr_utility.set_location(gv_package_name || '.formated_static_data2', 30);
320
321
322 p_static_data1 := lv_format1;
323 hr_utility.trace('Static Data1 = ' || lv_format1);
324 hr_utility.set_location(gv_package_name || '.formated_static_data2', 40);
325
326 END formated_static_data2;
327
328 PROCEDURE report_1
329 ( p_process_mode in varchar2
330 ,p_geocode_patch_name in varchar2
331 ,p_output_file_type in varchar2
332 )
333 IS
334
335
336 /************************************************************
337 ** Cursor to get all the employee and assignment data.
338 ************************************************************/
339 cursor c_cursor ( cp_process_mode in varchar
340 ,cp_geocode_patch_name in varchar
341 ) is
342 select distinct substr(ppf.full_name,1,40) ,
343 pef.assignment_id ,
344 substr(pef.assignment_number,1,17) ,
345 substr(pgu.description,1,65)
346 from per_people_f ppf,
347 per_assignments_f pef,
348 pay_us_geo_update pgu,
349 pay_patch_status pps
350 where pef.assignment_id = pgu.assignment_id
351 and ppf.person_id = pef.person_id
352 and pgu.id = pps.id
353 and pps.patch_name = p_geocode_patch_name
354 and pgu.status = 'P'
355 and pgu.process_mode = p_process_mode;
356
357 ln_full_name varchar2(40);
358 ln_assignment_id number;
359 ln_assignment_number varchar2(17);
360 ln_error_description varchar2(65);
361
362 lv_header_label VARCHAR2(32000);
363
364 lv_data_row VARCHAR2(32000);
365 lv_data_row1 VARCHAR2(32000);
366
367
368 lv_format1 varchar2(32000);
369
370
371 BEGIN
372
373 hr_utility.set_location(gv_package_name || '.report_1', 10);
374
375 /****************************************************************
379 ****************************************************************/
376 ** Concatnating the second Header Label which includes the User
377 ** Defined data set so that it is printed at the end of the
378 ** report.
380
381 fnd_file.put_line(fnd_file.output, formated_header_string(
382 'THIS IS A LIST OF ASSIGNMENT INFORMATION FOR THE GEOCODE UPDATE QUARTERLY PATCH'
383 ,p_output_file_type
384 ));
385
386 fnd_file.put_line(fnd_file.output, formated_header_string(
387 'Please correct all the following situations(if needed) before running your next payroll'
388 ,p_output_file_type
389 ));
390
391 fnd_file.put_line(fnd_file.output, formated_header_string(
392 'I. Errored Employees'
393 ,p_output_file_type
394 ));
395
396 fnd_file.put_line(fnd_file.output, formated_header_string(
397 'ACTION REQUIRED'
398 ,p_output_file_type
399 ));
400
401 fnd_file.put_line(fnd_file.output, formated_header_string(
402 'WARNING!! Employees that have ERRORED during the upgrade process'
403 ,p_output_file_type
404 ));
405
406 fnd_file.put_line(fnd_file.output, formated_header_string(
407 'Please address these errors immediately as the patch will not complete succesfully'
408 ,p_output_file_type
409 ));
410
411 fnd_file.put_line(fnd_file.output, formated_header_string(
412 ' until all the assignments are processed without error.'
413 ,p_output_file_type
414 ));
415
416
417 hr_utility.set_location(gv_package_name || '.report_1', 12);
418 /****************************************************************
419 ** Print the Header Information. If the format is HTML then open
420 ** the body and table before printing the header info, otherwise
421 ** just print the header information.
422 ****************************************************************/
423 if p_output_file_type ='HTML' then
424 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<body>');
425 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<table border=1>');
426 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<tr>');
427 end if;
428 hr_utility.set_location(gv_package_name || '.report_1', 15);
429
430 lv_format1 :=
431 formated_data_string (p_input_string => 'Full Name'
432 ,p_bold => 'Y'
433 ,p_output_file_type => p_output_file_type) ||
434
435 formated_data_string (p_input_string => 'Assignment Id'
436 ,p_bold => 'Y'
437 ,p_output_file_type => p_output_file_type) ||
438 formated_data_string (p_input_string => 'Assignment Number'
439 ,p_bold => 'Y'
440 ,p_output_file_type => p_output_file_type) ||
441
442 formated_data_string (p_input_string =>'Error Description'
443 ,p_bold => 'Y'
444 ,p_output_file_type => p_output_file_type)
445 ;
446
447 hr_utility.set_location(gv_package_name || '.report_1', 20);
448
449
453 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '</tr>');
450 fnd_file.put_line(fnd_file.output, lv_format1);
451
452 if p_output_file_type ='HTML' then
454 end if;
455
456 hr_utility.set_location(gv_package_name || '.report_1', 30);
457 /*****************************************************
458 ** Start of the Data Section of the Report
459 *****************************************************/
460
461 -- HR_UTILITY.TRACE_ON(NULL,'TCL');
462 open c_cursor( p_process_mode
463 ,p_geocode_patch_name
464 );
465
466 loop
467 hr_utility.set_location(gv_package_name || '.report_1', 40);
468
469 fetch c_cursor into ln_full_name
470 ,ln_assignment_id
471 ,ln_assignment_number
472 ,ln_error_description;
473
474 if c_cursor%notfound then
475 hr_utility.set_location(gv_package_name || '.report_1', 50);
476 exit;
477 end if;
478
479
480 /************************************************************
481 ** If Assignment Set is used, pick up only those employee
482 ** assignments which are part of the Assignment Set - STATIC
483 ** or DYNAMIC.
484 ************************************************************/
485 hr_utility.set_location(gv_package_name || '.report_1', 60);
486 hr_utility.trace('Assignment ID = ' || ln_assignment_id);
487
488 formated_static_data( ln_full_name
489 ,ln_assignment_id
490 ,ln_assignment_number
491 ,ln_error_description
492 ,null
493 ,null
494 ,null
495 ,null
496 ,null
497 ,p_output_file_type
498 ,lv_data_row1);
499
500 lv_data_row := lv_data_row1;
501 hr_utility.set_location(gv_package_name || '.report_1', 70);
502
503 -- if p_output_file_type ='HTML' then
504 lv_data_row := '<tr>' || lv_data_row || '</tr>' ;
505 -- end if;
506
507 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, lv_data_row);
508
509
510 /*****************************************************************
511 ** initialize Data varaibles
512 *****************************************************************/
513 lv_data_row := null;
514 end loop;
515 close c_cursor;
516
517 /*****************************************************
518 ** Close of the Data Section of the Report
519 *****************************************************/
520
521 if p_output_file_type ='HTML' then
522 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '</table></body></html>');
523 end if;
524
525 END report_1;
526
527 PROCEDURE report_2
528 (p_process_mode in varchar2
529 ,p_geocode_patch_name in varchar2
530 ,p_output_file_type in varchar2
531 )
532 IS
533
534
535 /************************************************************
536 ** Cursor to get all the employee and assignment data.
537 ************************************************************/
538 cursor c_cursor ( cp_process_mode in varchar
539 ,cp_geocode_patch_name in varchar
540 ) is
541
542 select distinct substr(ppf.full_name,1,40) ,
543 pef.assignment_id ,
544 substr(pef.assignment_number,1,17)
545 from per_people_f ppf,
546 per_assignments_f pef,
547 pay_us_geo_update pgu,
548 pay_patch_status pps
549 where pgu.process_type = 'PERCENTAGE_OVER_100'
550 and pef.assignment_id = pgu.assignment_id
551 and ppf.person_id = pef.person_id
552 and pgu.process_mode = p_process_mode
553 and pgu.id = pps.id
554 and pps.patch_name = p_geocode_patch_name;
555
556 ln_full_name varchar2(40);
557 ln_assignment_id number;
558 ln_assignment_number varchar2(17);
559
560 lv_header_label VARCHAR2(32000);
561
562 lv_data_row VARCHAR2(32000);
563 lv_data_row1 VARCHAR2(32000);
564
565 lv_format1 varchar2(32000);
566
567
568 BEGIN
569
570 hr_utility.set_location(gv_package_name || '.report_2', 10);
571
572 /****************************************************************
573 ** Concatnating the second Header Label which includes the User
574 ** Defined data set so that it is printed at the end of the
575 ** report.
576 ****************************************************************/
577
578 fnd_file.put_line(fnd_file.output, formated_header_string(
579 'II. Incorrect percent in time.'
580 ,p_output_file_type
581 ));
582
583 fnd_file.put_line(fnd_file.output, formated_header_string(
587
584 'ACTION REQUIRED'
585 ,p_output_file_type
586 ));
588 fnd_file.put_line(fnd_file.output, formated_header_string(
589 'Employees whose sum of percent in time is greater then 100% at the local level.'
590 ,p_output_file_type
591 ));
592
593 fnd_file.put_line(fnd_file.output, formated_header_string(
594 'Due to jurisdiction code upgrade, some cities may have exceeded 100% in time.'
595 ,p_output_file_type
596 ));
597
598 fnd_file.put_line(fnd_file.output, formated_header_string(
599 'Please correct this at the W-4 Percentage screen by setting the percent in time for the city'
600 ,p_output_file_type
601 ));
602
603 fnd_file.put_line(fnd_file.output, formated_header_string(
604 '(or total of all the cities) to not exceed 100% time in total. '
605 ,p_output_file_type
606 ));
607
608 hr_utility.set_location(gv_package_name || '.report_2', 15);
609 /****************************************************************
610 ** Print the Header Information. If the format is HTML then open
611 ** the body and table before printing the header info, otherwise
612 ** just print the header information.
613 ****************************************************************/
614 if p_output_file_type ='HTML' then
615 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<body>');
616 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<table border=1>');
617 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<tr>');
618 end if;
619 hr_utility.set_location(gv_package_name || '.report_2', 20);
620
621 lv_format1 :=
622 formated_data_string (p_input_string => 'Full Name'
623 ,p_bold => 'Y'
624 ,p_output_file_type => p_output_file_type) ||
625
626 formated_data_string (p_input_string => 'Assignment Id'
627 ,p_bold => 'Y'
628 ,p_output_file_type => p_output_file_type) ||
629 formated_data_string (p_input_string => 'Assignment Number'
630 ,p_bold => 'Y'
631 ,p_output_file_type => p_output_file_type) ;
632
633 hr_utility.set_location(gv_package_name || '.report_2', 30);
634
635
636 fnd_file.put_line(fnd_file.output, lv_format1);
637
638 if p_output_file_type ='HTML' then
639 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '</tr>');
640 end if;
641
642 hr_utility.set_location(gv_package_name || '.report_2', 40);
643 /*****************************************************
644 ** Start of the Data Section of the Report
645 *****************************************************/
646
647 -- HR_UTILITY.TRACE_ON(NULL,'TCL');
648 open c_cursor( p_process_mode
649 ,p_geocode_patch_name
650 );
651
652 loop
653 hr_utility.set_location(gv_package_name || '.report_2', 50);
654
655 fetch c_cursor into ln_full_name
656 ,ln_assignment_id
657 ,ln_assignment_number;
658
659 if c_cursor%notfound then
660 hr_utility.set_location(gv_package_name || '.report_2', 60);
661 exit;
662 end if;
663
664
665 /************************************************************
666 ** If Assignment Set is used, pick up only those employee
667 ** assignments which are part of the Assignment Set - STATIC
668 ** or DYNAMIC.
669 ************************************************************/
670 hr_utility.set_location(gv_package_name || '.report_2', 70);
671
672 hr_utility.set_location(gv_package_name || '.report_2', 80);
673 formated_static_data( ln_full_name
674 ,ln_assignment_id
675 ,ln_assignment_number
676 ,null
677 ,null
678 ,null
679 ,null
680 ,null
681 ,null
682 ,p_output_file_type
683 ,lv_data_row1);
684
685 lv_data_row := lv_data_row1;
686 hr_utility.set_location(gv_package_name || '.report_2', 90);
687
688 -- if p_output_file_type ='HTML' then
689 lv_data_row := '<tr>' || lv_data_row || '</tr>' ;
690 -- end if;
691
692 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, lv_data_row);
693
694
695 /*****************************************************************
696 ** initialize Data varaibles
697 *****************************************************************/
701
698 lv_data_row := null;
699 end loop;
700 close c_cursor;
702 /*****************************************************
703 ** Close of the Data Section of the Report
704 *****************************************************/
705
706 if p_output_file_type ='HTML' then
707 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '</table></body></html>');
708 end if;
709
710 END report_2;
711
712 PROCEDURE report_3
713 (p_process_mode in varchar2
714 ,p_geocode_patch_name in varchar2
715 ,p_output_file_type in varchar2
716 )
717 IS
718
719
720 /************************************************************
721 ** Cursor to get all the employee and assignment data.
722 ************************************************************/
723 cursor c_cursor ( cp_process_mode in varchar
724 ,cp_geocode_patch_name in varchar
725 ) is
726
727 select distinct substr(ppf.full_name,1,40) ,
728 pef.assignment_id ,
729 substr(pef.assignment_number,1,17)
730 from per_people_f ppf,
731 per_assignments_f pef,
732 pay_us_geo_update pgu,
733 pay_patch_status pps
734 where pgu.process_type = 'MISSING_COUNTY_RECORDS'
735 and pef.assignment_id = pgu.assignment_id
736 and ppf.person_id = pef.person_id
737 and pgu.process_mode = cp_process_mode
738 and pgu.id = pps.id
739 and pps.patch_name = cp_geocode_patch_name;
740
741 ln_full_name varchar2(40);
742 ln_assignment_id number;
743 ln_assignment_number varchar2(17);
744
745 lv_header_label VARCHAR2(32000);
746
747 lv_data_row VARCHAR2(32000);
748 lv_data_row1 VARCHAR2(32000);
749
750 lv_format1 varchar2(32000);
751
752
753 BEGIN
754
755 hr_utility.set_location(gv_package_name || '.report_3', 10);
756
757 /****************************************************************
758 ** Concatnating the second Header Label which includes the User
759 ** Defined data set so that it is printed at the end of the
760 ** report.
761 ****************************************************************/
762
763 fnd_file.put_line(fnd_file.output, formated_header_string(
764 'III. Missing county tax records.'
765 ,p_output_file_type
766 ));
767
768 fnd_file.put_line(fnd_file.output, formated_header_string(
769 'ACTION REQUIRED'
770 ,p_output_file_type
771 ));
772
773 fnd_file.put_line(fnd_file.output, formated_header_string(
774 'Employees who have missing county tax records.'
775 ,p_output_file_type
776 ));
777
778 fnd_file.put_line(fnd_file.output, formated_header_string(
779 'Please correct this by creating a new county tax record from the W-4 form.'
780 ,p_output_file_type
781 ));
782
783 hr_utility.set_location(gv_package_name || '.report_3', 15);
784 /****************************************************************
785 ** Print the Header Information. If the format is HTML then open
786 ** the body and table before printing the header info, otherwise
787 ** just print the header information.
788 ****************************************************************/
789 if p_output_file_type ='HTML' then
790 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<body>');
791 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<table border=1>');
792 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<tr>');
793 end if;
794 hr_utility.set_location(gv_package_name || '.report_3', 20);
795
796 lv_format1 :=
797 formated_data_string (p_input_string => 'Full Name'
798 ,p_bold => 'Y'
799 ,p_output_file_type => p_output_file_type) ||
800
801 formated_data_string (p_input_string => 'Assignment Id'
802 ,p_bold => 'Y'
803 ,p_output_file_type => p_output_file_type) ||
804 formated_data_string (p_input_string => 'Assignment Number'
805 ,p_bold => 'Y'
806 ,p_output_file_type => p_output_file_type)
807 ;
808
809 hr_utility.set_location(gv_package_name || '.report_3', 30);
810
811
812 fnd_file.put_line(fnd_file.output, lv_format1);
813
814 if p_output_file_type ='HTML' then
815 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '</tr>');
816 end if;
817
818 hr_utility.set_location(gv_package_name || '.report_3', 40);
819 /*****************************************************
823 -- HR_UTILITY.TRACE_ON(NULL,'TCL');
820 ** Start of the Data Section of the Report
821 *****************************************************/
822
824 open c_cursor( p_process_mode
825 ,p_geocode_patch_name
826 );
827
828 loop
829 hr_utility.set_location(gv_package_name || '.report_3', 50);
830
831 fetch c_cursor into ln_full_name
832 ,ln_assignment_id
833 ,ln_assignment_number;
834
835 if c_cursor%notfound then
836 hr_utility.set_location(gv_package_name || '.report_3', 60);
837 exit;
838 end if;
839
840
841 /************************************************************
842 ** If Assignment Set is used, pick up only those employee
843 ** assignments which are part of the Assignment Set - STATIC
844 ** or DYNAMIC.
845 ************************************************************/
846 hr_utility.set_location(gv_package_name || '.report_3', 70);
847 hr_utility.trace('Assignment ID = ' || ln_assignment_id);
848
849
850 hr_utility.set_location(gv_package_name || '.report_3', 80);
851 formated_static_data( ln_full_name
852 ,ln_assignment_id
853 ,ln_assignment_number
854 ,null
855 ,null
856 ,null
857 ,null
858 ,null
859 ,null
860 ,p_output_file_type
861 ,lv_data_row1);
862
863 lv_data_row := lv_data_row1;
864 hr_utility.set_location(gv_package_name || '.report_3', 90);
865
866 -- if p_output_file_type ='HTML' then
867 lv_data_row := '<tr>' || lv_data_row || '</tr>' ;
868 -- end if;
869
870 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, lv_data_row);
871
872
873 /*****************************************************************
874 ** initialize Data varaibles
875 *****************************************************************/
876 lv_data_row := null;
877 end loop;
878 close c_cursor;
879
880 /*****************************************************
881 ** Close of the Data Section of the Report
882 *****************************************************/
883
884 if p_output_file_type ='HTML' then
885 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '</table></body></html>');
886 end if;
887
888 END report_3;
889
890 PROCEDURE report_4
891 (p_process_mode in varchar2
892 ,p_geocode_patch_name in varchar2
893 ,p_output_file_type in varchar2
894 )
895 IS
896
897
898 /************************************************************
899 ** Cursor to get all the employee and assignment data.
900 ************************************************************/
901 cursor c_cursor ( cp_process_mode in varchar
902 ,cp_geocode_patch_name in varchar
903 ) is
904
905 select /*+ index(pmod PAY_US_MODIFIED_GEOCODES_PK)*/ -- Bug 3350007
906 distinct substr(ppf.full_name,1,40) ,
907 pef.assignment_id ,
908 substr(pef.assignment_number,1,17),
909 pgu.new_juri_code ,
910 substr(pusc.city_name,1,20),
911 pgu.old_juri_code,
912 substr(pmod.city_name,1,20),
913 substr(pgu.table_name ,1,20)
914 from pay_us_modified_geocodes pmod,
915 pay_us_city_names pusc ,
916 pay_patch_status pps,
917 per_people_f ppf,
918 per_assignments_f pef,
919 pay_us_geo_update pgu
920 where pgu.process_type = 'PU'
921 and pef.assignment_id = pgu.assignment_id
922 and ppf.person_id = pef.person_id
923 and pgu.table_name is not null
924 and substr(new_juri_code,1,2) = pmod.state_code
925 and substr(new_juri_code,4,3) = pmod.county_code
926 and substr(new_juri_code,8,4) = pmod.new_city_code
927 and substr(old_juri_code,8,4) = pmod.old_city_code
928 and pmod.process_type = 'PU'
929 and pusc.city_code = substr(new_juri_code,8,4)
930 and pusc.county_code = substr(new_juri_code,4,3)
931 and pusc.state_code = substr(new_juri_code,1,2)
932 and pusc.primary_flag = 'Y'
933 and pgu.process_mode = cp_process_mode
934 and pgu.id = pps.id
935 and pps.patch_name = cp_geocode_patch_name;
936
937 ln_full_name varchar2(40);
938 ln_assignment_id number;
939 ln_assignment_number varchar2(17);
940 ln_new_juri_code varchar2(11);
941 ln_new_pri_city varchar2(20);
942 ln_old_juri_code varchar2(11);
943 ln_old_pri_city varchar2(20);
944 ln_table_name varchar2(20);
945
946 lv_header_label VARCHAR2(32000);
947
948 lv_data_row VARCHAR2(32000);
949 lv_data_row1 VARCHAR2(32000);
950
951 lv_format1 varchar2(32000);
955
952
953
954 BEGIN
956 hr_utility.set_location(gv_package_name || '.report_4', 10);
957
958 /****************************************************************
959 ** Concatnating the second Header Label which includes the User
960 ** Defined data set so that it is printed at the end of the
961 ** report.
962 ****************************************************************/
963
964 fnd_file.put_line(fnd_file.output, formated_header_string(
965 'IV. Primary city becoming Secondary with jurisdiction code change'
966 ,p_output_file_type
967 ));
968
969 fnd_file.put_line(fnd_file.output, formated_header_string(
970 'NO ACTION IS REQUIRED. This is for information ONLY.'
971 ,p_output_file_type
972 ));
973
974 fnd_file.put_line(fnd_file.output, formated_header_string(
975 'Employees who have records updated in the following tables because'
976 ,p_output_file_type
977 ));
978
979 fnd_file.put_line(fnd_file.output, formated_header_string(
980 'a primary city has changed to a secondary city with a jurisdiction'
981 ,p_output_file_type
982 ));
983
984 fnd_file.put_line(fnd_file.output, formated_header_string(
985 'code change.'
986 ,p_output_file_type
987 ));
988
989 fnd_file.put_line(fnd_file.output, formated_header_string(
990 'The city displayed here is the NEW PRIMARY CITY for the assignment.'
991 ,p_output_file_type
992 ));
993
994 hr_utility.set_location(gv_package_name || '.report_4', 15);
995 /****************************************************************
996 ** Print the Header Information. If the format is HTML then open
997 ** the body and table before printing the header info, otherwise
998 ** just print the header information.
999 ****************************************************************/
1000 if p_output_file_type ='HTML' then
1001 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<body>');
1002 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<table border=1>');
1003 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<tr>');
1004 end if;
1005 hr_utility.set_location(gv_package_name || '.report_4', 20);
1006
1007 lv_format1 :=
1008 formated_data_string (p_input_string => 'Full Name'
1009 ,p_bold => 'Y'
1010 ,p_output_file_type => p_output_file_type) ||
1011
1012 formated_data_string (p_input_string => 'Assignment Id'
1013 ,p_bold => 'Y'
1014 ,p_output_file_type => p_output_file_type) ||
1015
1016 formated_data_string (p_input_string => 'Assignment Number'
1017 ,p_bold => 'Y'
1018 ,p_output_file_type => p_output_file_type) ||
1019
1020 formated_data_string (p_input_string => 'New JD'
1021 ,p_bold => 'Y'
1022 ,p_output_file_type => p_output_file_type) ||
1023
1024 formated_data_string (p_input_string => 'New Primary City'
1025 ,p_bold => 'Y'
1026 ,p_output_file_type => p_output_file_type) ||
1027
1028 formated_data_string (p_input_string => 'Old JD'
1029 ,p_bold => 'Y'
1030 ,p_output_file_type => p_output_file_type) ||
1031
1032 formated_data_string (p_input_string => 'Old Primary City'
1033 ,p_bold => 'Y'
1034 ,p_output_file_type => p_output_file_type) ||
1035
1036 formated_data_string (p_input_string => 'Table Updated'
1037 ,p_bold => 'Y'
1038 ,p_output_file_type => p_output_file_type)
1039 ;
1040
1041
1042 hr_utility.set_location(gv_package_name || '.report_4', 30);
1043
1044
1045 fnd_file.put_line(fnd_file.output, lv_format1);
1046
1047 if p_output_file_type ='HTML' then
1048 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '</tr>');
1049 end if;
1050
1051 hr_utility.set_location(gv_package_name || '.report_4', 40);
1052 /*****************************************************
1053 ** Start of the Data Section of the Report
1054 *****************************************************/
1055
1056 -- HR_UTILITY.TRACE_ON(NULL,'TCL');
1057 open c_cursor( p_process_mode
1058 ,p_geocode_patch_name
1059 );
1060
1061 loop
1062 hr_utility.set_location(gv_package_name || '.report_4', 50);
1063
1064 fetch c_cursor into ln_full_name
1068 ,ln_new_pri_city
1065 ,ln_assignment_id
1066 ,ln_assignment_number
1067 ,ln_new_juri_code
1069 ,ln_old_juri_code
1070 ,ln_old_pri_city
1071 ,ln_table_name;
1072
1073 if c_cursor%notfound then
1074 hr_utility.set_location(gv_package_name || '.report_4', 60);
1075 exit;
1076 end if;
1077
1078
1079 /************************************************************
1080 ** If Assignment Set is used, pick up only those employee
1081 ** assignments which are part of the Assignment Set - STATIC
1082 ** or DYNAMIC.
1083 ************************************************************/
1084 hr_utility.set_location(gv_package_name || '.report_4', 70);
1085 hr_utility.trace('Assignment ID = ' || ln_assignment_id);
1086
1087
1088 hr_utility.set_location(gv_package_name || '.report_4', 80);
1089 formated_static_data( ln_full_name
1090 ,ln_assignment_id
1091 ,ln_assignment_number
1092 ,null
1093 ,ln_new_juri_code
1094 ,ln_new_pri_city
1095 ,ln_old_juri_code
1096 ,ln_old_pri_city
1097 ,ln_table_name
1098 ,p_output_file_type
1099 ,lv_data_row1);
1100
1101 lv_data_row := lv_data_row1;
1102 hr_utility.set_location(gv_package_name || '.report_4', 90);
1103
1104 -- if p_output_file_type ='HTML' then
1105 lv_data_row := '<tr>' || lv_data_row || '</tr>' ;
1106 -- end if;
1107
1108 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, lv_data_row);
1109
1110
1111 /*****************************************************************
1112 ** initialize Data varaibles
1113 *****************************************************************/
1114 lv_data_row := null;
1115 end loop;
1116 close c_cursor;
1117
1118 /*****************************************************
1119 ** Close of the Data Section of the Report
1120 *****************************************************/
1121
1122 if p_output_file_type ='HTML' then
1123 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '</table></body></html>');
1124 end if;
1125
1126 END report_4;
1127
1128
1129 PROCEDURE report_5
1130 (p_process_mode in varchar2
1131 ,p_geocode_patch_name in varchar2
1132 ,p_output_file_type in varchar2
1133 )
1134 IS
1135
1136
1137 /************************************************************
1138 ** Cursor to get all the employee and assignment data.
1139 ************************************************************/
1140 cursor c_cursor ( cp_process_mode in varchar
1141 ,cp_geocode_patch_name in varchar
1142 ) is
1143
1144 select distinct substr(ppf.full_name,1,40) ,
1145 pef.assignment_id "Assignment Id" ,
1146 substr(pef.assignment_number,1,17),
1147 substr(pusc.city_name,1,20),
1148 old_juri_code "Old JD",
1149 new_juri_code "New JD",
1150 substr(table_name,1,20)
1151 from per_people_f ppf,
1152 per_assignments_f pef,
1153 pay_us_geo_update pgu,
1154 pay_us_city_names pusc,
1155 pay_patch_status pps
1156 where pgu.process_type = 'UP'
1157 and pgu.table_name is not null
1158 and pef.assignment_id = pgu.assignment_id
1159 and ppf.person_id = pef.person_id
1160 and pusc.city_code = substr(new_juri_code,8,4)
1161 and pusc.county_code = substr(new_juri_code,4,3)
1162 and pusc.state_code = substr(new_juri_code,1,2)
1163 and pusc.primary_flag = 'Y'
1164 and pgu.process_mode = p_process_mode
1165 and pgu.id = pps.id
1166 and pps.patch_name = p_geocode_patch_name;
1167
1168 ln_full_name varchar2(40);
1169 ln_assignment_id number;
1170 ln_assignment_number varchar2(17);
1171 ln_new_juri_code varchar2(11);
1172 ln_new_pri_city varchar2(20);
1173 ln_old_juri_code varchar2(11);
1174 ln_old_pri_city varchar2(20);
1175 ln_table_name varchar2(20);
1176
1177 lv_header_label VARCHAR2(32000);
1178
1179 lv_data_row VARCHAR2(32000);
1180 lv_data_row1 VARCHAR2(32000);
1181
1182 lv_format1 varchar2(32000);
1183
1184
1185 BEGIN
1186
1187 hr_utility.set_location(gv_package_name || '.report_5', 10);
1188
1189 /****************************************************************
1190 ** Concatnating the second Header Label which includes the User
1191 ** Defined data set so that it is printed at the end of the
1192 ** report.
1193 ****************************************************************/
1194
1195 fnd_file.put_line(fnd_file.output, formated_header_string(
1199
1196 'V. Primary city jurisdiction code change.'
1197 ,p_output_file_type
1198 ));
1200 fnd_file.put_line(fnd_file.output, formated_header_string(
1201 'NO ACTION IS REQUIRED. This is for information ONLY.'
1202 ,p_output_file_type
1203 ));
1204
1205 fnd_file.put_line(fnd_file.output, formated_header_string(
1206 'Employees whose records have been updated in the following tables'
1207 ,p_output_file_type
1208 ));
1209
1210 fnd_file.put_line(fnd_file.output, formated_header_string(
1211 'because a primary citys jurisdiction code has changed.'
1212 ,p_output_file_type
1213 ));
1214
1215 hr_utility.set_location(gv_package_name || '.report_5', 15);
1216 /****************************************************************
1217 ** Print the Header Information. If the format is HTML then open
1218 ** the body and table before printing the header info, otherwise
1219 ** just print the header information.
1220 ****************************************************************/
1221 if p_output_file_type ='HTML' then
1222 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<body>');
1223 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<table border=1>');
1224 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<tr>');
1225 end if;
1226 hr_utility.set_location(gv_package_name || '.report_5', 20);
1227
1228 lv_format1 :=
1229 formated_data_string (p_input_string => 'Full Name'
1230 ,p_bold => 'Y'
1231 ,p_output_file_type => p_output_file_type) ||
1232
1233 formated_data_string (p_input_string => 'Assignment Id'
1234 ,p_bold => 'Y'
1235 ,p_output_file_type => p_output_file_type) ||
1236
1237 formated_data_string (p_input_string => 'Assignment Number'
1238 ,p_bold => 'Y'
1239 ,p_output_file_type => p_output_file_type) ||
1240
1241 formated_data_string (p_input_string => 'New JD'
1242 ,p_bold => 'Y'
1243 ,p_output_file_type => p_output_file_type) ||
1244
1245 formated_data_string (p_input_string => 'New Primary City'
1246 ,p_bold => 'Y'
1247 ,p_output_file_type => p_output_file_type) ||
1248
1249 formated_data_string (p_input_string => 'Old JD'
1250 ,p_bold => 'Y'
1251 ,p_output_file_type => p_output_file_type) ||
1252
1253 formated_data_string (p_input_string => 'Table Updated'
1254 ,p_bold => 'Y'
1255 ,p_output_file_type => p_output_file_type)
1256 ;
1257
1258
1259 hr_utility.set_location(gv_package_name || '.report_5', 30);
1260
1261
1262 fnd_file.put_line(fnd_file.output, lv_format1);
1263
1264 if p_output_file_type ='HTML' then
1265 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '</tr>');
1266 end if;
1267
1268 hr_utility.set_location(gv_package_name || '.report_5', 40);
1269 /*****************************************************
1270 ** Start of the Data Section of the Report
1271 *****************************************************/
1272
1273 -- HR_UTILITY.TRACE_ON(NULL,'TCL');
1274 open c_cursor( p_process_mode
1275 ,p_geocode_patch_name
1276 );
1277
1278 loop
1279 hr_utility.set_location(gv_package_name || '.report_5', 50);
1280
1281 fetch c_cursor into ln_full_name
1282 ,ln_assignment_id
1283 ,ln_assignment_number
1284 ,ln_new_pri_city
1285 ,ln_new_juri_code
1286 ,ln_old_juri_code
1287 ,ln_table_name;
1288
1289 if c_cursor%notfound then
1290 hr_utility.set_location(gv_package_name || '.report_5', 60);
1291 exit;
1292 end if;
1293
1294
1295 /************************************************************
1296 ** If Assignment Set is used, pick up only those employee
1297 ** assignments which are part of the Assignment Set - STATIC
1298 ** or DYNAMIC.
1299 ************************************************************/
1300 hr_utility.set_location(gv_package_name || '.report_5', 70);
1301 hr_utility.trace('Assignment ID = ' || ln_assignment_id);
1302
1303
1304 hr_utility.set_location(gv_package_name || '.report_5', 80);
1305 formated_static_data( ln_full_name
1306 ,ln_assignment_id
1307 ,ln_assignment_number
1308 ,null
1309 ,ln_new_juri_code
1313 ,ln_table_name
1310 ,ln_new_pri_city
1311 ,ln_old_juri_code
1312 ,null
1314 ,p_output_file_type
1315 ,lv_data_row1);
1316
1317 lv_data_row := lv_data_row1;
1318 hr_utility.set_location(gv_package_name || '.report_5', 90);
1319
1320 -- if p_output_file_type ='HTML' then
1321 lv_data_row := '<tr>' || lv_data_row || '</tr>' ;
1322 -- end if;
1323
1324 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, lv_data_row);
1325
1326
1327 /*****************************************************************
1328 ** initialize Data varaibles
1329 *****************************************************************/
1330 lv_data_row := null;
1331 end loop;
1332 close c_cursor;
1333
1334 /*****************************************************
1335 ** Close of the Data Section of the Report
1336 *****************************************************/
1337
1338 if p_output_file_type ='HTML' then
1339 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '</table></body></html>');
1340 end if;
1341
1342 END report_5;
1343
1344
1345 PROCEDURE report_6
1346 (p_process_mode in varchar2
1347 ,p_geocode_patch_name in varchar2
1348 ,p_output_file_type in varchar2
1349 )
1350 IS
1351
1352
1353 /************************************************************
1354 ** Cursor to get all the employee and assignment data.
1355 ************************************************************/
1356 cursor c_cursor ( cp_process_mode in varchar
1357 ,cp_geocode_patch_name in varchar
1358 ) is
1359
1360 select distinct substr(ppf.full_name,1,40) ,
1361 pef.assignment_id "Assignment Id" ,
1362 substr(pef.assignment_number,1,17),
1363 substr(pusc.city_name,1,20),
1364 substr(puscn.county_name,1,20),
1365 substr(pust.state_abbrev,1,2),
1366 old_juri_code ,
1367 new_juri_code
1368 from per_people_f ppf,
1369 per_assignments_f pef,
1370 pay_us_geo_update pgu,
1371 pay_us_city_names pusc,
1372 pay_patch_status pps ,
1373 pay_us_states pust,
1374 pay_us_counties puscn
1375 where pgu.process_type = 'US'
1376 and pgu.status = 'A'
1377 and pgu.table_name is null
1378 and pef.assignment_id = pgu.assignment_id
1379 and ppf.person_id = pef.person_id
1380 and pusc.city_code = substr(new_juri_code,8,4)
1381 and pusc.county_code = substr(new_juri_code,4,3)
1382 and pusc.state_code = substr(new_juri_code,1,2)
1383 and puscn.county_code = pusc.county_code
1384 and puscn.state_code = pusc.state_code
1385 and pust.state_code = pusc.state_code
1386 and pusc.primary_flag = 'Y'
1387 and pgu.process_mode = cp_process_mode
1388 and pgu.id = pps.id
1389 and pps.patch_name = cp_geocode_patch_name;
1390
1391 ln_full_name varchar2(40);
1392 ln_assignment_id number;
1393 ln_assignment_number varchar2(17);
1394 ln_city_name varchar2(20);
1395 ln_county_name varchar2(20);
1396 ln_state_abbrev varchar2(20);
1397 ln_old_juri_code varchar2(11);
1398 ln_new_juri_code varchar2(11);
1399
1400 lv_header_label VARCHAR2(32000);
1401
1402 lv_data_row VARCHAR2(32000);
1403 lv_data_row1 VARCHAR2(32000);
1404
1405 lv_format1 varchar2(32000);
1406
1407
1408 BEGIN
1409
1410 hr_utility.set_location(gv_package_name || '.report_6', 10);
1411
1412 /****************************************************************
1413 ** Concatnating the second Header Label which includes the User
1414 ** Defined data set so that it is printed at the end of the
1415 ** report.
1416 ****************************************************************/
1417
1418 fnd_file.put_line(fnd_file.output, formated_header_string(
1419 'VI. Secondary jurisdiction code change.'
1420 ,p_output_file_type
1421 ));
1422
1423 fnd_file.put_line(fnd_file.output, formated_header_string(
1424 'ACTION REQUIRED'
1425 ,p_output_file_type
1426 ));
1427
1428 fnd_file.put_line(fnd_file.output, formated_header_string(
1429 'WARNING! Employees whose secondary city jurisdiction code has changed. Thus this means'
1430 ,p_output_file_type
1431 ));
1432
1433 fnd_file.put_line(fnd_file.output, formated_header_string(
1434 'the employees will have different or a new Primary City.'
1435 ,p_output_file_type
1436 ));
1437
1438 fnd_file.put_line(fnd_file.output, formated_header_string(
1442
1439 'New city tax records and Vertex Element Entries have been created for'
1440 ,p_output_file_type
1441 ));
1443 fnd_file.put_line(fnd_file.output, formated_header_string(
1444 'these assignments with the new jurisdiction code.'
1445 ,p_output_file_type
1446 ));
1447
1448 fnd_file.put_line(fnd_file.output, formated_header_string(
1449 'Please check the records of these employees and determine which CITY is to be the Primary City.'
1450 ,p_output_file_type
1451 ));
1452
1453 fnd_file.put_line(fnd_file.output, formated_header_string(
1454 'Then, ensure that the percentages reflect the new primary city.'
1455 ,p_output_file_type
1456 ));
1457
1458 fnd_file.put_line(fnd_file.output, formated_header_string(
1459 'also ensure that the subject to balances are correct. Some manual balance adjustments'
1460 ,p_output_file_type
1461 ));
1462
1463 fnd_file.put_line(fnd_file.output, formated_header_string(
1464 'may be required to reflect the new taxing jurisdictions of highlighted cities.'
1465 ,p_output_file_type
1466 ));
1467
1468 fnd_file.put_line(fnd_file.output, formated_header_string(
1469 'The resident city and work city listed reflects the location as of the day this patch is run.'
1470 ,p_output_file_type
1471 ));
1472
1473 fnd_file.put_line(fnd_file.output, formated_header_string(
1474 'NOTE : This only applies to those cities that have local level taxes. All other'
1475 ,p_output_file_type
1476 ));
1477
1478 fnd_file.put_line(fnd_file.output, formated_header_string(
1479 'cities may be ignored and are listed for information only.'
1480 ,p_output_file_type
1481 ));
1482
1483 hr_utility.set_location(gv_package_name || '.report_6', 15);
1484 /****************************************************************
1485 ** Print the Header Information. If the format is HTML then open
1486 ** the body and table before printing the header info, otherwise
1487 ** just print the header information.
1488 ****************************************************************/
1489 if p_output_file_type ='HTML' then
1490 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<body>');
1491 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<table border=1>');
1492 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<tr>');
1493 end if;
1494 hr_utility.set_location(gv_package_name || '.report_6', 20);
1495
1496 lv_format1 :=
1497 formated_data_string (p_input_string => 'Full Name'
1498 ,p_bold => 'Y'
1499 ,p_output_file_type => p_output_file_type) ||
1500
1501 formated_data_string (p_input_string => 'Assignment Id'
1502 ,p_bold => 'Y'
1503 ,p_output_file_type => p_output_file_type) ||
1504
1505 formated_data_string (p_input_string => 'Assignment Number'
1506 ,p_bold => 'Y'
1507 ,p_output_file_type => p_output_file_type) ||
1508
1509 formated_data_string (p_input_string => 'Primary City'
1510 ,p_bold => 'Y'
1511 ,p_output_file_type => p_output_file_type) ||
1512
1513 formated_data_string (p_input_string => 'County Name'
1514 ,p_bold => 'Y'
1515 ,p_output_file_type => p_output_file_type) ||
1516
1517 formated_data_string (p_input_string => 'State'
1518 ,p_bold => 'Y'
1519 ,p_output_file_type => p_output_file_type) ||
1520
1521 formated_data_string (p_input_string => 'Old JD'
1522 ,p_bold => 'Y'
1523 ,p_output_file_type => p_output_file_type) ||
1524
1525 formated_data_string (p_input_string => 'New JD'
1526 ,p_bold => 'Y'
1527 ,p_output_file_type => p_output_file_type)
1528 ;
1529
1530
1531 hr_utility.set_location(gv_package_name || '.report_6', 30);
1532
1533
1534 fnd_file.put_line(fnd_file.output, lv_format1);
1535
1536 if p_output_file_type ='HTML' then
1537 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '</tr>');
1538 end if;
1539
1540 hr_utility.set_location(gv_package_name || '.report_6', 40);
1544
1541 /*****************************************************
1542 ** Start of the Data Section of the Report
1543 *****************************************************/
1545 -- HR_UTILITY.TRACE_ON(NULL,'TCL');
1546 open c_cursor( p_process_mode
1547 ,p_geocode_patch_name
1548 );
1549
1550 loop
1551 hr_utility.set_location(gv_package_name || '.report_6', 50);
1552
1553 fetch c_cursor into ln_full_name
1554 ,ln_assignment_id
1555 ,ln_assignment_number
1556 ,ln_city_name
1557 ,ln_county_name
1558 ,ln_state_abbrev
1559 ,ln_old_juri_code
1560 ,ln_new_juri_code;
1561
1562 if c_cursor%notfound then
1563 hr_utility.set_location(gv_package_name || '.report_6', 60);
1564 exit;
1565 end if;
1566
1567
1568 /************************************************************
1569 ** If Assignment Set is used, pick up only those employee
1570 ** assignments which are part of the Assignment Set - STATIC
1571 ** or DYNAMIC.
1572 ************************************************************/
1573 hr_utility.set_location(gv_package_name || '.report_6', 70);
1574 hr_utility.trace('Assignment ID = ' || ln_assignment_id);
1575
1576
1577 hr_utility.set_location(gv_package_name || '.report_6', 80);
1578 formated_static_data2(ln_full_name
1579 ,ln_assignment_id
1580 ,ln_assignment_number
1581 ,ln_city_name
1582 ,ln_county_name
1583 ,ln_state_abbrev
1584 ,ln_old_juri_code
1585 ,ln_new_juri_code
1586 ,p_output_file_type
1587 ,lv_data_row1);
1588
1589 lv_data_row := lv_data_row1;
1590 hr_utility.set_location(gv_package_name || '.report_6', 90);
1591
1592 -- if p_output_file_type ='HTML' then
1593 lv_data_row := '<tr>' || lv_data_row || '</tr>' ;
1594 -- end if;
1595
1596 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, lv_data_row);
1597
1598
1599 /*****************************************************************
1600 ** initialize Data varaibles
1601 *****************************************************************/
1602 lv_data_row := null;
1603 end loop;
1604 close c_cursor;
1605
1606 /*****************************************************
1607 ** Close of the Data Section of the Report
1608 *****************************************************/
1609
1610 if p_output_file_type ='HTML' then
1611 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '</table></body></html>');
1612 end if;
1613
1614 END report_6;
1615
1616
1617 PROCEDURE report_7
1618 (p_process_mode in varchar2
1619 ,p_geocode_patch_name in varchar2
1620 ,p_output_file_type in varchar2
1621 )
1622 IS
1623
1624
1625 /************************************************************
1626 ** Cursor to get all the employee and assignment data.
1627 ************************************************************/
1628 cursor c_cursor ( cp_process_mode in varchar
1629 ,cp_geocode_patch_name in varchar
1630 ) is
1631
1632 SELECT /*+ ORDERED
1633 INDEX (PAY_US_MODIFIED_GEOCODES PAY_US_MODIFIED_GEOCODES_N1)
1634 INDEX (PAY_US_CITY_NAMES PAY_US_CITY_NAMES_FK1)
1635 INDEX (PAY_US_EMP_CITY_TAX_RULES_F PAY_US_EMP_CITY_TAX_RULES_N3) */
1636 SUBSTR(ppf.full_name,1,40),
1637 SUBSTR(pmod.city_name,1,20),
1638 SUBSTR(pucn.city_name,1,20)
1639 FROM pay_us_modified_geocodes pmod,
1640 pay_us_city_names pucn,
1641 pay_us_emp_city_tax_rules_f ectr,
1642 per_assignments_f paf,
1643 per_people_f ppf
1644 WHERE pmod.process_type = 'P'
1645 AND pmod.state_code = pucn.state_code
1646 AND pmod.county_code = pucn.county_code
1647 AND pmod.new_city_code = pucn.city_code
1648 AND pucn.primary_flag = 'Y'
1649 AND pmod.state_code = ectr.state_code
1650 AND pmod.county_code = ectr.county_code
1651 AND pmod.old_city_code = ectr.city_code
1652 AND ectr.assignment_id = paf.assignment_id
1653 AND paf.person_id = ppf.person_id
1654 AND pmod.patch_name = cp_geocode_patch_name
1655 union all
1656 SELECT /*+ ORDERED
1657 INDEX (PAY_US_MODIFIED_GEOCODES PAY_US_MODIFIED_GEOCODES_N1)
1658 INDEX (PAY_US_CITY_NAMES PAY_US_CITY_NAMES_FK1)
1659 INDEX (PAY_US_EMP_CITY_TAX_RULES_F PAY_US_EMP_CITY_TAX_RULES_N3) */
1660 SUBSTR(ppf.full_name,1,40),
1661 SUBSTR(pucn.city_name,1,20),
1662 SUBSTR(pmod.city_name,1,20)
1663 FROM pay_us_modified_geocodes pmod,
1664 pay_us_city_names pucn,
1668 hr_s_us_city_names pmod2
1665 pay_us_emp_city_tax_rules_f ectr,
1666 per_assignments_f paf,
1667 per_people_f ppf,
1669 WHERE pmod.process_type = 'S'
1670 AND pmod.state_code = pucn.state_code
1671 AND pmod.county_code = pucn.county_code
1672 AND pmod.new_city_code = pucn.city_code
1673 AND pucn.city_name = pmod2.city_name
1674 and pmod2.state_code = ectr.state_code
1675 and pmod2.county_code = ectr.county_code
1676 and pmod2.city_code = ectr.city_code
1677 and pmod2.primary_flag = 'Y'
1678 and pmod2.city_name = pmod.city_name
1679 AND pmod.state_code = ectr.state_code
1680 AND pmod.county_code = ectr.county_code
1681 AND pmod.old_city_code = ectr.city_code
1682 AND ectr.assignment_id = paf.assignment_id
1683 AND paf.person_id = ppf.person_id
1684 AND pmod.patch_name = cp_geocode_patch_name;
1685
1686
1687 ln_full_name varchar2(40);
1688 ln_old_city_name varchar2(20);
1689 ln_new_city_name varchar2(20);
1690
1691 lv_header_label VARCHAR2(32000);
1692
1693 lv_data_row VARCHAR2(32000);
1694 lv_data_row1 VARCHAR2(32000);
1695
1696 lv_format1 varchar2(32000);
1697
1698
1699 BEGIN
1700
1701 hr_utility.set_location(gv_package_name || '.report_7', 10);
1702
1703 /****************************************************************
1704 ** Concatnating the second Header Label which includes the User
1705 ** Defined data set so that it is printed at the end of the
1706 ** report.
1707 ****************************************************************/
1708
1709 fnd_file.put_line(fnd_file.output, formated_header_string(
1710 'VII. Employees who have new Primary City.'
1711 ,p_output_file_type
1712 ));
1713
1714 fnd_file.put_line(fnd_file.output, formated_header_string(
1715 'NO ACTION IS REQUIRED. This is for information ONLY.'
1716 ,p_output_file_type
1717 ));
1718
1719 fnd_file.put_line(fnd_file.output, formated_header_string(
1720 'The jurisdction code has not changed thus the tax rates will stay the same.'
1721 ,p_output_file_type
1722 ));
1723
1724 fnd_file.put_line(fnd_file.output, formated_header_string(
1725 'the employees will have different or a new Primary City.'
1726 ,p_output_file_type
1727 ));
1728
1729
1730 hr_utility.set_location(gv_package_name || '.report_7', 15);
1731 /****************************************************************
1732 ** Print the Header Information. If the format is HTML then open
1733 ** the body and table before printing the header info, otherwise
1734 ** just print the header information.
1735 ****************************************************************/
1736 if p_output_file_type ='HTML' then
1737 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<body>');
1738 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<table border=1>');
1739 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<tr>');
1740 end if;
1741 hr_utility.set_location(gv_package_name || '.report_7', 20);
1742
1743 lv_format1 :=
1744 formated_data_string (p_input_string => 'Full Name'
1745 ,p_bold => 'Y'
1746 ,p_output_file_type => p_output_file_type) ||
1747
1748 formated_data_string (p_input_string => 'Old Primary City'
1749 ,p_bold => 'Y'
1750 ,p_output_file_type => p_output_file_type) ||
1751
1752 formated_data_string (p_input_string => 'New Primary City'
1753 ,p_bold => 'Y'
1754 ,p_output_file_type => p_output_file_type)
1755 ;
1756
1757 hr_utility.set_location(gv_package_name || '.report_7', 30);
1758
1759
1760 fnd_file.put_line(fnd_file.output, lv_format1);
1761
1762 if p_output_file_type ='HTML' then
1763 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '</tr>');
1764 end if;
1765
1766 hr_utility.set_location(gv_package_name || '.report_7', 40);
1767 /*****************************************************
1768 ** Start of the Data Section of the Report
1769 *****************************************************/
1770
1771 -- HR_UTILITY.TRACE_ON(NULL,'TCL');
1772 open c_cursor( p_process_mode
1773 ,p_geocode_patch_name
1774 );
1775
1776 loop
1777 hr_utility.set_location(gv_package_name || '.report_7', 50);
1778
1779 fetch c_cursor into ln_full_name
1780 ,ln_old_city_name
1781 ,ln_new_city_name;
1782
1783 if c_cursor%notfound then
1787
1784 hr_utility.set_location(gv_package_name || '.report_7', 60);
1785 exit;
1786 end if;
1788
1789 /************************************************************
1790 ** If Assignment Set is used, pick up only those employee
1791 ** assignments which are part of the Assignment Set - STATIC
1792 ** or DYNAMIC.
1793 ************************************************************/
1794 hr_utility.set_location(gv_package_name || '.report_7', 70);
1795
1796 hr_utility.set_location(gv_package_name || '.report_7', 80);
1797 formated_static_data(ln_full_name
1798 ,null
1799 ,null
1800 ,null
1801 ,ln_old_city_name -- Intentional see report format
1802 ,null
1803 ,ln_new_city_name -- Intentional see report format
1804 ,null
1805 ,null
1806 ,p_output_file_type
1807 ,lv_data_row1);
1808
1809 lv_data_row := lv_data_row1;
1810 hr_utility.set_location(gv_package_name || '.report_7', 90);
1811
1812 -- if p_output_file_type ='HTML' then
1813 lv_data_row := '<tr>' || lv_data_row || '</tr>' ;
1814 -- end if;
1815
1816 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, lv_data_row);
1817
1818
1819 /*****************************************************************
1820 ** initialize Data varaibles
1821 *****************************************************************/
1822 lv_data_row := null;
1823 end loop;
1824 close c_cursor;
1825
1826 /*****************************************************
1827 ** Close of the Data Section of the Report
1828 *****************************************************/
1829
1830 if p_output_file_type ='HTML' then
1831 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '</table></body></html>');
1832 end if;
1833
1834 END report_7;
1835
1836 PROCEDURE report_8
1837 (p_process_mode in varchar2
1838 ,p_geocode_patch_name in varchar2
1839 ,p_output_file_type in varchar2
1840 )
1841 IS
1842
1843
1844 /************************************************************
1845 ** Cursor to get all the employee and assignment data.
1846 ************************************************************/
1847 cursor c_cursor ( cp_process_mode in varchar
1848 ,cp_geocode_patch_name in varchar
1849 ) is
1850
1851 select state_code||'-'||county_code||'-'||old_city_code ,
1852 state_code||'-'||county_code||'-'||new_city_code ,
1853 substr(city_name,1,20)
1854 from pay_us_modified_geocodes
1855 where old_city_code like 'U%';
1856
1857
1858 ln_old_juri_code varchar2(11);
1859 ln_new_juri_code varchar2(11);
1860 ln_city_name varchar2(20);
1861
1862 lv_header_label VARCHAR2(32000);
1863
1864 lv_data_row VARCHAR2(32000);
1865 lv_data_row1 VARCHAR2(32000);
1866
1867 lv_format1 varchar2(32000);
1868
1869
1870 BEGIN
1871
1872 hr_utility.set_location(gv_package_name || '.report_8', 10);
1873
1874 /****************************************************************
1875 ** Concatnating the second Header Label which includes the User
1876 ** Defined data set so that it is printed at the end of the
1877 ** report.
1878 ****************************************************************/
1879
1880 fnd_file.put_line(fnd_file.output, formated_header_string(
1881 'VIII. User Defined cities that are now supported by Vertex'
1882 ,p_output_file_type
1883 ));
1884
1885 fnd_file.put_line(fnd_file.output, formated_header_string(
1886 'NO ACTION IS REQUIRED. This is for information ONLY.'
1887 ,p_output_file_type
1888 ));
1889
1890 fnd_file.put_line(fnd_file.output, formated_header_string(
1891 'The following lists user defined cities that are now supported by Vertex.'
1892 ,p_output_file_type
1893 ));
1894
1895
1896 hr_utility.set_location(gv_package_name || '.report_8', 15);
1897 /****************************************************************
1898 ** Print the Header Information. If the format is HTML then open
1899 ** the body and table before printing the header info, otherwise
1900 ** just print the header information.
1901 ****************************************************************/
1902 if p_output_file_type ='HTML' then
1903 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<body>');
1904 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<table border=1>');
1905 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<tr>');
1906 end if;
1907 hr_utility.set_location(gv_package_name || '.report_8', 20);
1908
1912 ,p_output_file_type => p_output_file_type) ||
1909 lv_format1 :=
1910 formated_data_string (p_input_string => 'Old User Defined JD'
1911 ,p_bold => 'Y'
1913
1914 formated_data_string (p_input_string => 'Supported New JD'
1915 ,p_bold => 'Y'
1916 ,p_output_file_type => p_output_file_type) ||
1917
1918 formated_data_string (p_input_string => 'City Name'
1919 ,p_bold => 'Y'
1920 ,p_output_file_type => p_output_file_type)
1921 ;
1922
1923 hr_utility.set_location(gv_package_name || '.report_8', 30);
1924
1925
1926 fnd_file.put_line(fnd_file.output, lv_format1);
1927
1928 if p_output_file_type ='HTML' then
1929 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '</tr>');
1930 end if;
1931
1932 hr_utility.set_location(gv_package_name || '.report_8', 40);
1933 /*****************************************************
1934 ** Start of the Data Section of the Report
1935 *****************************************************/
1936
1937 -- HR_UTILITY.TRACE_ON(NULL,'TCL');
1938 open c_cursor( p_process_mode
1939 ,p_geocode_patch_name
1940 );
1941
1942 loop
1943 hr_utility.set_location(gv_package_name || '.report_8', 50);
1944
1945 fetch c_cursor into ln_old_juri_code
1946 ,ln_new_juri_code
1947 ,ln_city_name;
1948
1949 if c_cursor%notfound then
1950 hr_utility.set_location(gv_package_name || '.report_8', 60);
1951 exit;
1952 end if;
1953
1954
1955 /************************************************************
1956 ** If Assignment Set is used, pick up only those employee
1957 ** assignments which are part of the Assignment Set - STATIC
1958 ** or DYNAMIC.
1959 ************************************************************/
1960 hr_utility.set_location(gv_package_name || '.report_8', 70);
1961
1962 hr_utility.set_location(gv_package_name || '.report_8', 80);
1963 formated_static_data( null
1964 ,null
1965 ,null
1966 ,ln_old_juri_code -- Intentional see report format
1967 ,null
1968 ,ln_new_juri_code -- Intentional see report format
1969 ,ln_city_name
1970 ,null
1971 ,null
1972 ,p_output_file_type
1973 ,lv_data_row1);
1974
1975 lv_data_row := lv_data_row1;
1976 hr_utility.set_location(gv_package_name || '.report_8', 90);
1977
1978 -- if p_output_file_type ='HTML' then
1979 lv_data_row := '<tr>' || lv_data_row || '</tr>' ;
1980 -- end if;
1981
1982 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, lv_data_row);
1983
1984
1985 /*****************************************************************
1986 ** initialize Data varaibles
1987 *****************************************************************/
1988 lv_data_row := null;
1989 end loop;
1990 close c_cursor;
1991
1992 /*****************************************************
1993 ** Close of the Data Section of the Report
1994 *****************************************************/
1995
1996 if p_output_file_type ='HTML' then
1997 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '</table></body></html>');
1998 end if;
1999
2000 END report_8;
2001
2002
2003 PROCEDURE report_9
2004 (p_process_mode in varchar2
2005 ,p_geocode_patch_name in varchar2
2006 ,p_output_file_type in varchar2
2007 )
2008 IS
2009
2010
2011 /************************************************************
2012 ** Cursor to get all the employee and assignment data.
2013 ************************************************************/
2014 cursor c_cursor ( cp_process_mode in varchar
2015 ,cp_geocode_patch_name in varchar
2016 ) is
2017
2018 select /*+ ORDERED
2019 INDEX (PAY_US_GEO_UPDATE PAY_US_GEO_UPDATE_N2)
2020 INDEX (PAY_PATCH_STATUS PAY_PATCH_STATUS_N1) */
2021 distinct substr(ppf.full_name,1,40) ,
2022 pef.assignment_id ,
2023 substr(pef.assignment_number,1,17),
2024 substr(pusc.city_name,1,20),
2025 pgu.new_juri_code
2026 from pay_patch_status pps,
2027 pay_us_geo_update pgu,
2028 pay_us_city_names pusc,
2029 per_assignments_f pef,
2030 per_people_f ppf
2031 where pgu.process_type = 'NEW_CITY_RECORDS'
2032 and pef.assignment_id = pgu.assignment_id
2033 and ppf.person_id = pef.person_id
2034 and pusc.city_code = substr(new_juri_code,8,4)
2035 and pusc.county_code = substr(new_juri_code,4,3)
2036 and pusc.state_code = substr(new_juri_code,1,2)
2040 and pps.patch_name = cp_geocode_patch_name;
2037 and pgu.process_mode = cp_process_mode
2038 and pusc.primary_flag = 'Y'
2039 and pgu.id = pps.id
2041
2042
2043
2044 ln_full_name varchar2(40);
2045 ln_assignment_id number;
2046 ln_assignment_number varchar2(17);
2047 ln_city_name varchar2(20);
2048 ln_jd_code varchar2(11);
2049
2050 lv_header_label VARCHAR2(32000);
2051
2052 lv_data_row VARCHAR2(32000);
2053 lv_data_row1 VARCHAR2(32000);
2054
2055 lv_format1 varchar2(32000);
2056
2057
2058 BEGIN
2059
2060 hr_utility.set_location(gv_package_name || '.report_9', 10);
2061
2062 /****************************************************************
2063 ** Concatnating the second Header Label which includes the User
2064 ** Defined data set so that it is printed at the end of the
2065 ** report.
2066 ****************************************************************/
2067
2068 fnd_file.put_line(fnd_file.output, formated_header_string(
2069 'IX. Summary of employees for whom new city tax records have been created'
2070 ,p_output_file_type
2071 ));
2072
2073 fnd_file.put_line(fnd_file.output, formated_header_string(
2074 'NO ACTION IS REQUIRED. This is for information ONLY.'
2075 ,p_output_file_type
2076 ));
2077
2078 fnd_file.put_line(fnd_file.output, formated_header_string(
2079 'Please ensure that for these assignments, the tax records are as expected and the percent'
2080 ,p_output_file_type
2081 ));
2082
2083 fnd_file.put_line(fnd_file.output, formated_header_string(
2084 'of time spent in each city is correct. These names may be duplicated from above.'
2085 ,p_output_file_type
2086 ));
2087
2088
2089 hr_utility.set_location(gv_package_name || '.report_9', 15);
2090 /****************************************************************
2091 ** Print the Header Information. If the format is HTML then open
2092 ** the body and table before printing the header info, otherwise
2093 ** just print the header information.
2094 ****************************************************************/
2095 if p_output_file_type ='HTML' then
2096 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<body>');
2097 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<table border=1>');
2098 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<tr>');
2099 end if;
2100 hr_utility.set_location(gv_package_name || '.report_9', 20);
2101
2102 lv_format1 :=
2103 formated_data_string (p_input_string => 'Full Name'
2104 ,p_bold => 'Y'
2105 ,p_output_file_type => p_output_file_type) ||
2106
2107 formated_data_string (p_input_string => 'Assignment Id'
2108 ,p_bold => 'Y'
2109 ,p_output_file_type => p_output_file_type) ||
2110
2111 formated_data_string (p_input_string => 'Assignment Number'
2112 ,p_bold => 'Y'
2113 ,p_output_file_type => p_output_file_type) ||
2114
2115 formated_data_string (p_input_string => 'Primary City'
2116 ,p_bold => 'Y'
2117 ,p_output_file_type => p_output_file_type) ||
2118
2119 formated_data_string (p_input_string => 'Jurisdiction Code'
2120 ,p_bold => 'Y'
2121 ,p_output_file_type => p_output_file_type) ;
2122
2123 hr_utility.set_location(gv_package_name || '.report_9', 30);
2124
2125
2126 fnd_file.put_line(fnd_file.output, lv_format1);
2127
2128 if p_output_file_type ='HTML' then
2129 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '</tr>');
2130 end if;
2131
2132 hr_utility.set_location(gv_package_name || '.report_9', 40);
2133 /*****************************************************
2134 ** Start of the Data Section of the Report
2135 *****************************************************/
2136
2137 -- HR_UTILITY.TRACE_ON(NULL,'TCL');
2138 open c_cursor( p_process_mode
2139 ,p_geocode_patch_name
2140 );
2141
2142 loop
2143 hr_utility.set_location(gv_package_name || '.report_9', 50);
2144
2145 fetch c_cursor into ln_full_name
2146 ,ln_assignment_id
2147 ,ln_assignment_number
2148 ,ln_city_name
2149 ,ln_jd_code;
2150
2151 if c_cursor%notfound then
2152 hr_utility.set_location(gv_package_name || '.report_9', 60);
2153 exit;
2154 end if;
2155
2156
2160 ** or DYNAMIC.
2157 /************************************************************
2158 ** If Assignment Set is used, pick up only those employee
2159 ** assignments which are part of the Assignment Set - STATIC
2161 ************************************************************/
2162 hr_utility.set_location(gv_package_name || '.report_9', 70);
2163 hr_utility.trace('Assignment ID = ' || ln_assignment_id);
2164
2165
2166 hr_utility.set_location(gv_package_name || '.report_9', 80);
2167 formated_static_data( ln_full_name
2168 ,ln_assignment_id
2169 ,ln_assignment_number
2170 ,null
2171 ,ln_city_name
2172 ,ln_jd_code
2173 ,null
2174 ,null
2175 ,null
2176 ,p_output_file_type
2177 ,lv_data_row1);
2178
2179 lv_data_row := lv_data_row1;
2180 hr_utility.set_location(gv_package_name || '.report_9', 90);
2181
2182 -- if p_output_file_type ='HTML' then
2183 lv_data_row := '<tr>' || lv_data_row || '</tr>' ;
2184 -- end if;
2185
2186 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, lv_data_row);
2187
2188
2189 /*****************************************************************
2190 ** initialize Data varaibles
2191 *****************************************************************/
2192 lv_data_row := null;
2193 end loop;
2194 close c_cursor;
2195
2196 /*****************************************************
2197 ** Close of the Data Section of the Report
2198 *****************************************************/
2199
2200 if p_output_file_type ='HTML' then
2201 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '</table></body></html>');
2202 end if;
2203
2204 END report_9;
2205
2206
2207 PROCEDURE report_10
2208 (p_process_mode in varchar2
2209 ,p_geocode_patch_name in varchar2
2210 ,p_output_file_type in varchar2
2211 )
2212 IS
2213
2214
2215 /************************************************************
2216 ** Cursor to get all the employee and assignment data.
2217 ************************************************************/
2218 cursor c_cursor ( cp_process_mode in varchar
2219 ,cp_geocode_patch_name in varchar
2220 ) is
2221
2222 select /*+ ORDERED
2223 INDEX (PAY_US_GEO_UPDATE PAY_US_GEO_UPDATE_N2 )
2224 INDEX (PAY_PATCH_STATUS PAY_PATCH_STATUS_N1) */
2225 distinct substr(ppf.full_name,1,40) ,
2226 pef.assignment_id ,
2227 substr(pef.assignment_number,1,17),
2228 substr(pusc.city_name,1,20),
2229 pgu.new_juri_code
2230 from pay_patch_status pps,
2231 pay_us_geo_update pgu,
2232 pay_us_city_names pusc,
2233 per_assignments_f pef,
2234 per_people_f ppf
2235 where pgu.process_type = 'NEW_VERTEX_RECORDS'
2236 and pef.assignment_id = pgu.assignment_id
2237 and ppf.person_id = pef.person_id
2238 and pusc.city_code = substr(new_juri_code,8,4)
2239 and pusc.county_code = substr(new_juri_code,4,3)
2240 and pusc.state_code = substr(new_juri_code,1,2)
2241 and pusc.primary_flag = 'Y'
2242 and pgu.process_mode = cp_process_mode
2243 and pgu.id = pps.id
2244 and pps.patch_name = cp_geocode_patch_name;
2245
2246
2247
2248
2249 ln_full_name varchar2(40);
2250 ln_assignment_id number;
2251 ln_assignment_number varchar2(17);
2252 ln_city_name varchar2(20);
2253 ln_jd_code varchar2(11);
2254
2255 lv_header_label VARCHAR2(32000);
2256
2257 lv_data_row VARCHAR2(32000);
2258 lv_data_row1 VARCHAR2(32000);
2259
2260 lv_format1 varchar2(32000);
2261
2262
2263 BEGIN
2264
2265 hr_utility.set_location(gv_package_name || '.report_10', 10);
2266
2267 /****************************************************************
2268 ** Concatnating the second Header Label which includes the User
2269 ** Defined data set so that it is printed at the end of the
2270 ** report.
2271 ****************************************************************/
2272
2273 fnd_file.put_line(fnd_file.output, formated_header_string(
2274 'X. Summary of employees for whom new Vertex Element Entry records have been created'
2275 ,p_output_file_type
2276 ));
2277
2278 fnd_file.put_line(fnd_file.output, formated_header_string(
2279 'NO ACTION IS REQUIRED. This is for information ONLY.'
2280 ,p_output_file_type
2281 ));
2282
2283 fnd_file.put_line(fnd_file.output, formated_header_string(
2287
2284 'Please ensure that for these assignments, the tax records are as expected and the percent'
2285 ,p_output_file_type
2286 ));
2288 fnd_file.put_line(fnd_file.output, formated_header_string(
2289 'of time spent in each city is correct and the sum of percent of time spent in all states equals 100.'
2290 ,p_output_file_type
2291 ));
2292
2293 fnd_file.put_line(fnd_file.output, formated_header_string(
2294 'These names may be duplicated from above. They are listed just for a reference.'
2295 ,p_output_file_type
2296 ));
2297
2298
2299 hr_utility.set_location(gv_package_name || '.report_10', 15);
2300 /****************************************************************
2301 ** Print the Header Information. If the format is HTML then open
2302 ** the body and table before printing the header info, otherwise
2303 ** just print the header information.
2304 ****************************************************************/
2305 if p_output_file_type ='HTML' then
2306 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<body>');
2307 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<table border=1>');
2308 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<tr>');
2309 end if;
2310 hr_utility.set_location(gv_package_name || '.report_10', 20);
2311
2312 lv_format1 :=
2313 formated_data_string (p_input_string => 'Full Name'
2314 ,p_bold => 'Y'
2315 ,p_output_file_type => p_output_file_type) ||
2316
2317 formated_data_string (p_input_string => 'Assignment Id'
2318 ,p_bold => 'Y'
2319 ,p_output_file_type => p_output_file_type) ||
2320
2321 formated_data_string (p_input_string => 'Assignment Number'
2322 ,p_bold => 'Y'
2323 ,p_output_file_type => p_output_file_type) ||
2324
2325 formated_data_string (p_input_string => 'Primary City'
2326 ,p_bold => 'Y'
2327 ,p_output_file_type => p_output_file_type) ||
2328
2329 formated_data_string (p_input_string => 'Jurisdiction Code'
2330 ,p_bold => 'Y'
2331 ,p_output_file_type => p_output_file_type) ;
2332
2333 hr_utility.set_location(gv_package_name || '.report_10', 30);
2334
2335
2336 fnd_file.put_line(fnd_file.output, lv_format1);
2337
2338 if p_output_file_type ='HTML' then
2339 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '</tr>');
2340 end if;
2341
2342 hr_utility.set_location(gv_package_name || '.report_10', 40);
2343 /*****************************************************
2344 ** Start of the Data Section of the Report
2345 *****************************************************/
2346
2347 -- HR_UTILITY.TRACE_ON(NULL,'TCL');
2348 open c_cursor( p_process_mode
2349 ,p_geocode_patch_name
2350 );
2351
2352 loop
2353 hr_utility.set_location(gv_package_name || '.report_10', 50);
2354
2355 fetch c_cursor into ln_full_name
2356 ,ln_assignment_id
2357 ,ln_assignment_number
2358 ,ln_city_name
2359 ,ln_jd_code;
2360
2361 if c_cursor%notfound then
2362 hr_utility.set_location(gv_package_name || '.report_10', 60);
2363 exit;
2364 end if;
2365
2366
2367 /************************************************************
2368 ** If Assignment Set is used, pick up only those employee
2369 ** assignments which are part of the Assignment Set - STATIC
2370 ** or DYNAMIC.
2371 ************************************************************/
2372 hr_utility.set_location(gv_package_name || '.report_10', 70);
2373 hr_utility.trace('Assignment ID = ' || ln_assignment_id);
2374
2375
2376 hr_utility.set_location(gv_package_name || '.report_10', 80);
2377 formated_static_data( ln_full_name
2378 ,ln_assignment_id
2379 ,ln_assignment_number
2380 ,null
2381 ,ln_city_name
2382 ,ln_jd_code
2383 ,null
2384 ,null
2385 ,null
2386 ,p_output_file_type
2387 ,lv_data_row1);
2388
2389 lv_data_row := lv_data_row1;
2390 hr_utility.set_location(gv_package_name || '.report_10', 90);
2391
2392 -- if p_output_file_type ='HTML' then
2393 lv_data_row := '<tr>' || lv_data_row || '</tr>' ;
2394 -- end if;
2395
2396 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, lv_data_row);
2397
2398
2399 /*****************************************************************
2400 ** initialize Data varaibles
2404 close c_cursor;
2401 *****************************************************************/
2402 lv_data_row := null;
2403 end loop;
2405
2406 /*****************************************************
2407 ** Close of the Data Section of the Report
2408 *****************************************************/
2409
2410 if p_output_file_type ='HTML' then
2411 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '</table></body></html>');
2412 end if;
2413
2414 END report_10;
2415
2416 PROCEDURE report_11
2417 (p_process_mode in varchar2
2418 ,p_geocode_patch_name in varchar2
2419 ,p_output_file_type in varchar2
2420 )
2421 IS
2422
2423
2424 /************************************************************
2425 ** Cursor to get all the employee and assignment data.
2426 ************************************************************/
2427 cursor c_cursor ( cp_process_mode in varchar
2428 ,cp_geocode_patch_name in varchar
2429 ) is
2430
2431 select /*+ ORDERED
2432 INDEX (PAY_US_GEO_UPDATE PAY_US_GEO_UPDATE_N2 )
2433 INDEX (PAY_PATCH_STATUS PAY_PATCH_STATUS_N1) */
2434 distinct old_juri_code ,
2435 new_juri_code
2436 from pay_patch_status pps,
2437 pay_us_geo_update pgu
2438 where pgu.process_type = 'TAX_RULES_CHANGE'
2439 and pgu.process_mode = cp_process_mode
2440 and pgu.id = pps.id
2441 and pps.patch_name = cp_geocode_patch_name;
2442
2443 ln_old_jd_code varchar2(11);
2444 ln_new_jd_code varchar2(11);
2445
2446 lv_header_label VARCHAR2(32000);
2447
2448 lv_data_row VARCHAR2(32000);
2449 lv_data_row1 VARCHAR2(32000);
2450
2451 lv_format1 varchar2(32000);
2452
2453
2454 BEGIN
2455
2456 hr_utility.set_location(gv_package_name || '.report_11', 10);
2457
2458 /****************************************************************
2459 ** Concatnating the second Header Label which includes the User
2460 ** Defined data set so that it is printed at the end of the
2461 ** report.
2462 ****************************************************************/
2463
2464 fnd_file.put_line(fnd_file.output, formated_header_string(
2465 'XI. New taxability rules.'
2466 ,p_output_file_type
2467 ));
2468
2469 fnd_file.put_line(fnd_file.output, formated_header_string(
2470 'The taxability rules have been changed for the following jurisdiction codes to reflect the new jurisdiction code.'
2471 ,p_output_file_type
2472 ));
2473
2474 hr_utility.set_location(gv_package_name || '.report_11', 15);
2475 /****************************************************************
2476 ** Print the Header Information. If the format is HTML then open
2477 ** the body and table before printing the header info, otherwise
2478 ** just print the header information.
2479 ****************************************************************/
2480 if p_output_file_type ='HTML' then
2481 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<body>');
2482 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<table border=1>');
2483 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<tr>');
2484 end if;
2485 hr_utility.set_location(gv_package_name || '.report_11', 20);
2486
2487 lv_format1 :=
2488 formated_data_string (p_input_string => 'Old JD'
2489 ,p_bold => 'Y'
2490 ,p_output_file_type => p_output_file_type) ||
2491
2492 formated_data_string (p_input_string => 'New JD'
2493 ,p_bold => 'Y'
2494 ,p_output_file_type => p_output_file_type)
2495 ;
2496
2497 hr_utility.set_location(gv_package_name || '.report_11', 30);
2498
2499
2500 fnd_file.put_line(fnd_file.output, lv_format1);
2501
2502 if p_output_file_type ='HTML' then
2503 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '</tr>');
2504 end if;
2505
2506 hr_utility.set_location(gv_package_name || '.report_11', 40);
2507 /*****************************************************
2508 ** Start of the Data Section of the Report
2509 *****************************************************/
2510
2511 -- HR_UTILITY.TRACE_ON(NULL,'TCL');
2512 open c_cursor( p_process_mode
2513 ,p_geocode_patch_name
2514 );
2515
2516 loop
2517 hr_utility.set_location(gv_package_name || '.report_11', 50);
2518
2519 fetch c_cursor into ln_old_jd_code
2520 ,ln_new_jd_code;
2521
2522 if c_cursor%notfound then
2523 hr_utility.set_location(gv_package_name || '.report_11', 60);
2524 exit;
2525 end if;
2526
2527
2528 /************************************************************
2529 ** If Assignment Set is used, pick up only those employee
2533 hr_utility.set_location(gv_package_name || '.report_11', 70);
2530 ** assignments which are part of the Assignment Set - STATIC
2531 ** or DYNAMIC.
2532 ************************************************************/
2534
2535 hr_utility.set_location(gv_package_name || '.report_11', 80);
2536 formated_static_data( null
2537 ,null
2538 ,null
2539 ,ln_old_jd_code
2540 ,null
2541 ,ln_new_jd_code
2542 ,null
2543 ,null
2544 ,null
2545 ,p_output_file_type
2546 ,lv_data_row1);
2547
2548 lv_data_row := lv_data_row1;
2549 hr_utility.set_location(gv_package_name || '.report_11', 90);
2550
2551 -- if p_output_file_type ='HTML' then
2552 lv_data_row := '<tr>' || lv_data_row || '</tr>' ;
2553 -- end if;
2554
2555 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, lv_data_row);
2556
2557
2558 /*****************************************************************
2559 ** initialize Data varaibles
2560 *****************************************************************/
2561 lv_data_row := null;
2562 end loop;
2563 close c_cursor;
2564
2565 /*****************************************************
2566 ** Close of the Data Section of the Report
2567 *****************************************************/
2568
2569 if p_output_file_type ='HTML' then
2570 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '</table></body></html>');
2571 end if;
2572
2573 END report_11;
2574
2575
2576 PROCEDURE report_12
2577 (p_process_mode in varchar2
2578 ,p_geocode_patch_name in varchar2
2579 ,p_output_file_type in varchar2
2580 )
2581 IS
2582
2583
2584 /************************************************************
2585 ** Cursor to get all the employee and assignment data.
2586 ************************************************************/
2587 cursor c_cursor ( cp_process_mode in varchar
2588 ,cp_geocode_patch_name in varchar
2589 ) is
2590
2591 SELECT /*+ ORDERED
2592 INDEX (PAY_US_MODIFIED_GEOCODES PAY_US_MODIFIED_GEOCODES_N1) */
2593 distinct
2594 substr(ppf.full_name,1,40),
2595 paf.assignment_id ,
2596 substr(paf.assignment_number,1,17),
2597 substr(pmod.city_name,1,20),
2598 ectr.jurisdiction_code,
2599 substr(puc1.county_name,1,20),
2600 pmod.state_code||'-'||pmod.new_county_code||'-'||pmod.new_city_code "New JD",
2601 substr(puc2.county_name,1,20)
2602 FROM pay_us_modified_geocodes pmod,
2603 pay_us_emp_city_tax_rules_f ectr,
2604 per_assignments_f paf,
2605 per_people_f ppf,
2606 pay_us_counties puc1,
2607 pay_us_counties puc2
2608 WHERE ppf.person_id = paf.person_id
2609 AND pmod.state_code = ectr.state_code
2610 AND pmod.state_code = puc1.state_code
2611 AND pmod.state_code = puc2.state_code
2612 AND pmod.county_code = puc1.county_code
2613 AND pmod.new_county_code = puc2.county_code
2614 AND pmod.county_code = ectr.county_code
2615 AND pmod.new_county_code is not null
2616 AND pmod.old_city_code = ectr.city_code
2617 AND ectr.assignment_id = paf.assignment_id
2618 AND pmod.patch_name = cp_geocode_patch_name
2619 and pmod.process_type in ('P', 'PC', 'PU', 'S', 'SU', 'UP', 'US');
2620
2621
2622 ln_full_name varchar2(40);
2623 ln_assignment_id number;
2624 ln_assignment_number varchar2(17);
2625 ln_city_name varchar2(20);
2626 ln_old_juri_code varchar2(11);
2627 ln_old_county varchar2(20);
2628 ln_new_juri_code varchar2(11);
2629 ln_new_county varchar2(20);
2630
2631
2632 lv_header_label VARCHAR2(32000);
2633
2634 lv_data_row VARCHAR2(32000);
2635 lv_data_row1 VARCHAR2(32000);
2636
2637 lv_format1 varchar2(32000);
2638
2639
2640 BEGIN
2641
2642 hr_utility.set_location(gv_package_name || '.report_12', 10);
2643
2644 /****************************************************************
2645 ** Concatnating the second Header Label which includes the User
2646 ** Defined data set so that it is printed at the end of the
2647 ** report.
2648 ****************************************************************/
2649
2650 fnd_file.put_line(fnd_file.output, formated_header_string(
2651 'XII. County Code Change. '
2652 ,p_output_file_type
2653 ));
2654
2658 ));
2655 fnd_file.put_line(fnd_file.output, formated_header_string(
2656 'ACTION REQUIRED.'
2657 ,p_output_file_type
2659
2660 fnd_file.put_line(fnd_file.output, formated_header_string(
2661 'The following assignments are located in jurisdictions which are '
2662 ,p_output_file_type
2663 ));
2664
2665 fnd_file.put_line(fnd_file.output, formated_header_string(
2666 'changing their county codes. You must update the jurisdiction information'
2667 ,p_output_file_type
2668 ));
2669
2670 fnd_file.put_line(fnd_file.output, formated_header_string(
2671 'for these assignments as Vertex will discontinue support for the'
2672 ,p_output_file_type
2673 ));
2674
2675 fnd_file.put_line(fnd_file.output, formated_header_string(
2676 'old jurisdictions in upcoming data files and taxes will stop being withheld'
2677 ,p_output_file_type
2678 ));
2679
2680
2681 hr_utility.set_location(gv_package_name || '.report_12', 15);
2682 /****************************************************************
2683 ** Print the Header Information. If the format is HTML then open
2684 ** the body and table before printing the header info, otherwise
2685 ** just print the header information.
2686 ****************************************************************/
2687 if p_output_file_type ='HTML' then
2688 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<body>');
2689 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<table border=1>');
2690 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<tr>');
2691 end if;
2692 hr_utility.set_location(gv_package_name || '.report_12', 20);
2693
2694 lv_format1 :=
2695 formated_data_string (p_input_string => 'Full Name'
2696 ,p_bold => 'Y'
2697 ,p_output_file_type => p_output_file_type) ||
2698
2699 formated_data_string (p_input_string => 'Assignment Id'
2700 ,p_bold => 'Y'
2701 ,p_output_file_type => p_output_file_type) ||
2702
2703 formated_data_string (p_input_string => 'Assignment Number'
2704 ,p_bold => 'Y'
2705 ,p_output_file_type => p_output_file_type) ||
2706
2707 formated_data_string (p_input_string => 'Old JD'
2708 ,p_bold => 'Y'
2709 ,p_output_file_type => p_output_file_type) ||
2710
2711 formated_data_string (p_input_string => 'Old County'
2712 ,p_bold => 'Y'
2713 ,p_output_file_type => p_output_file_type) ||
2714
2715 formated_data_string (p_input_string => 'New JD'
2716 ,p_bold => 'Y'
2717 ,p_output_file_type => p_output_file_type) ||
2718
2719 formated_data_string (p_input_string => 'New County'
2720 ,p_bold => 'Y'
2721 ,p_output_file_type => p_output_file_type) ||
2722
2723 formated_data_string (p_input_string => 'City Name'
2724 ,p_bold => 'Y'
2725 ,p_output_file_type => p_output_file_type) ;
2726
2727 hr_utility.set_location(gv_package_name || '.report_12', 30);
2728
2729
2730 fnd_file.put_line(fnd_file.output, lv_format1);
2731
2732 if p_output_file_type ='HTML' then
2733 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '</tr>');
2734 end if;
2735
2736 hr_utility.set_location(gv_package_name || '.report_12', 40);
2737 /*****************************************************
2738 ** Start of the Data Section of the Report
2739 *****************************************************/
2740
2741 -- HR_UTILITY.TRACE_ON(NULL,'TCL');
2742 open c_cursor( p_process_mode
2743 ,p_geocode_patch_name
2744 );
2745
2746 loop
2747 hr_utility.set_location(gv_package_name || '.report_12', 50);
2748
2749 fetch c_cursor into ln_full_name
2750 ,ln_assignment_id
2751 ,ln_assignment_number
2752 ,ln_city_name
2753 ,ln_old_juri_code
2754 ,ln_old_county
2755 ,ln_new_juri_code
2756 ,ln_new_county;
2757
2758 if c_cursor%notfound then
2759 hr_utility.set_location(gv_package_name || '.report_12', 60);
2760 exit;
2761 end if;
2762
2763
2764 /************************************************************
2765 ** If Assignment Set is used, pick up only those employee
2766 ** assignments which are part of the Assignment Set - STATIC
2770 hr_utility.trace('Assignment ID = ' || ln_assignment_id);
2767 ** or DYNAMIC.
2768 ************************************************************/
2769 hr_utility.set_location(gv_package_name || '.report_12', 70);
2771
2772
2773 hr_utility.set_location(gv_package_name || '.report_12', 80);
2774 formated_static_data( ln_full_name
2775 ,ln_assignment_id
2776 ,ln_assignment_number
2777 ,null
2778 ,ln_old_juri_code
2779 ,ln_old_county
2780 ,ln_new_juri_code
2781 ,ln_new_county
2782 ,ln_city_name
2783 ,p_output_file_type
2784 ,lv_data_row1);
2785
2786 lv_data_row := lv_data_row1;
2787 hr_utility.set_location(gv_package_name || '.report_12', 90);
2788
2789 -- if p_output_file_type ='HTML' then
2790 lv_data_row := '<tr>' || lv_data_row || '</tr>' ;
2791 -- end if;
2792
2793 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, lv_data_row);
2794
2795
2796 /*****************************************************************
2797 ** initialize Data varaibles
2798 *****************************************************************/
2799 lv_data_row := null;
2800 end loop;
2801 close c_cursor;
2802
2803 /*****************************************************
2804 ** Close of the Data Section of the Report
2805 *****************************************************/
2806
2807 if p_output_file_type ='HTML' then
2808 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '</table></body></html>');
2809 end if;
2810
2811 END report_12;
2812
2813 PROCEDURE report_13
2814 (p_process_mode in varchar2
2815 ,p_geocode_patch_name in varchar2
2816 ,p_output_file_type in varchar2
2817 )
2818 IS
2819
2820 ln_row_count number;
2821
2822
2823 lv_header_label VARCHAR2(32000);
2824
2825 lv_data_row VARCHAR2(32000);
2826 lv_data_row1 VARCHAR2(32000);
2827
2828 lv_format1 varchar2(32000);
2829
2830
2831 BEGIN
2832
2833 hr_utility.set_location(gv_package_name || '.report_13', 10);
2834
2835 /****************************************************************
2836 ** Concatnating the second Header Label which includes the User
2837 ** Defined data set so that it is printed at the end of the
2838 ** report.
2839 ****************************************************************/
2840
2841 fnd_file.put_line(fnd_file.output, formated_header_string(
2842 'XIII. Table Row Counts. '
2843 ,p_output_file_type
2844 ));
2845
2846 fnd_file.put_line(fnd_file.output, formated_header_string(
2847 'NO ACTION IS REQUIRED. This is for information ONLY.'
2848 ,p_output_file_type
2849 ));
2850
2851 fnd_file.put_line(fnd_file.output, formated_header_string(
2852 'The following tables were updated and now have the following row counts:'
2853 ,p_output_file_type
2854 ));
2855
2856
2857 hr_utility.set_location(gv_package_name || '.report_13', 15);
2858 /****************************************************************
2859 ** Print the Header Information. If the format is HTML then open
2860 ** the body and table before printing the header info, otherwise
2861 ** just print the header information.
2862 ****************************************************************/
2863 if p_output_file_type ='HTML' then
2864 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<body>');
2865 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<table border=1>');
2866 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<tr>');
2867 end if;
2868 hr_utility.set_location(gv_package_name || '.report_13', 20);
2869
2870 lv_format1 :=
2871 formated_data_string (p_input_string => 'Table Name'
2872 ,p_bold => 'Y'
2873 ,p_output_file_type => p_output_file_type) ||
2874
2875 formated_data_string (p_input_string => 'Row Count'
2876 ,p_bold => 'Y'
2877 ,p_output_file_type => p_output_file_type)
2878 ;
2879
2880 hr_utility.set_location(gv_package_name || '.report_13', 30);
2881
2882
2883 fnd_file.put_line(fnd_file.output, lv_format1);
2884
2885 if p_output_file_type ='HTML' then
2886 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '</tr>');
2887 end if;
2888
2889 hr_utility.set_location(gv_package_name || '.report_13', 40);
2890 /*****************************************************
2891 ** Start of the Data Section of the Report
2892 *****************************************************/
2893
2897 ** If Assignment Set is used, pick up only those employee
2894 hr_utility.set_location(gv_package_name || '.report_13', 50);
2895
2896 /************************************************************
2898 ** assignments which are part of the Assignment Set - STATIC
2899 ** or DYNAMIC.
2900 ************************************************************/
2901
2902 hr_utility.set_location(gv_package_name || '.report_13', 60);
2903
2904 -- count for pay_us_states --
2905
2906 ln_row_count := 0;
2907
2908 select count(*)
2909 into ln_row_count
2910 from pay_us_states;
2911
2912 formated_static_data( 'PAY_US_STATES'
2913 ,ln_row_count
2914 ,null
2915 ,null
2916 ,null
2917 ,null
2918 ,null
2919 ,null
2920 ,null
2921 ,p_output_file_type
2922 ,lv_data_row1);
2923
2924
2925 lv_data_row := lv_data_row1;
2926 hr_utility.set_location(gv_package_name || '.report_13', 65);
2927
2928 lv_data_row := '<tr>' || lv_data_row || '</tr>' ;
2929
2930 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, lv_data_row);
2931
2932 -- count for pay_us_counties --
2933
2934 ln_row_count := 0;
2935
2936 select count(*)
2937 into ln_row_count
2938 from pay_us_counties;
2939
2940 formated_static_data( 'PAY_US_COUNTIES'
2941 ,ln_row_count
2942 ,null
2943 ,null
2944 ,null
2945 ,null
2946 ,null
2947 ,null
2948 ,null
2949 ,p_output_file_type
2950 ,lv_data_row1);
2951
2952
2953 lv_data_row := lv_data_row1;
2954 hr_utility.set_location(gv_package_name || '.report_13', 70);
2955
2956 lv_data_row := '<tr>' || lv_data_row || '</tr>' ;
2957
2958 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, lv_data_row);
2959
2960 -- count for pay_us_city_geocodes --
2961
2962 ln_row_count := 0;
2963
2964 select count(*)
2965 into ln_row_count
2966 from pay_us_city_geocodes;
2967
2968 formated_static_data( 'PAY_US_CITY_GEOCODES'
2969 ,ln_row_count
2970 ,null
2971 ,null
2972 ,null
2973 ,null
2974 ,null
2975 ,null
2976 ,null
2977 ,p_output_file_type
2978 ,lv_data_row1);
2979
2980 lv_data_row := lv_data_row1;
2981 hr_utility.set_location(gv_package_name || '.report_13', 75);
2982
2983 lv_data_row := '<tr>' || lv_data_row || '</tr>' ;
2984
2985 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, lv_data_row);
2986
2987 -- count for pay_us_city_names --
2988
2989 ln_row_count := 0;
2990
2991 select count(*)
2992 into ln_row_count
2993 from pay_us_city_names;
2994
2995 formated_static_data( 'PAY_US_CITY_NAMES'
2996 ,ln_row_count
2997 ,null
2998 ,null
2999 ,null
3000 ,null
3001 ,null
3002 ,null
3003 ,null
3004 ,p_output_file_type
3005 ,lv_data_row1);
3006
3007 lv_data_row := lv_data_row1;
3008 hr_utility.set_location(gv_package_name || '.report_13', 80);
3009
3010 lv_data_row := '<tr>' || lv_data_row || '</tr>' ;
3011
3012 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, lv_data_row);
3013
3014 -- count for pay_us_zip_codes --
3015
3016 ln_row_count := 0;
3017
3018 select count(*)
3019 into ln_row_count
3020 from pay_us_zip_codes;
3021
3022 formated_static_data( 'PAY_US_ZIP_CODES'
3023 ,ln_row_count
3024 ,null
3025 ,null
3026 ,null
3027 ,null
3028 ,null
3029 ,null
3030 ,null
3031 ,p_output_file_type
3032 ,lv_data_row1);
3033
3034 lv_data_row := lv_data_row1;
3035 hr_utility.set_location(gv_package_name || '.report_13', 85);
3036
3037 lv_data_row := '<tr>' || lv_data_row || '</tr>' ;
3038
3039 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, lv_data_row);
3040
3041 -- count for pay_us_modified_geocodes --
3042
3043 ln_row_count := 0;
3044
3045 select count(*)
3046 into ln_row_count
3050 ,ln_row_count
3047 from pay_us_modified_geocodes;
3048
3049 formated_static_data( 'PAY_US_MODIFIED_GEOCODES'
3051 ,null
3052 ,null
3053 ,null
3054 ,null
3055 ,null
3056 ,null
3057 ,null
3058 ,p_output_file_type
3059 ,lv_data_row1);
3060
3061 lv_data_row := lv_data_row1;
3062 hr_utility.set_location(gv_package_name || '.report_13', 90);
3063
3064 lv_data_row := '<tr>' || lv_data_row || '</tr>' ;
3065
3066 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, lv_data_row);
3067 /*****************************************************************
3068 ** initialize Data varaibles
3069 *****************************************************************/
3070 lv_data_row := null;
3071
3072
3073 /*****************************************************
3074 ** Close of the Data Section of the Report
3075 *****************************************************/
3076
3077 if p_output_file_type ='HTML' then
3078 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '</table></body></html>');
3079 end if;
3080
3081 END report_13;
3082
3083 PROCEDURE report_14
3084 (p_process_mode in varchar2
3085 ,p_geocode_patch_name in varchar2
3086 ,p_output_file_type in varchar2
3087 )
3088 IS
3089
3090
3091 /************************************************************
3092 ** Cursor to get all the employee and assignment data.
3093 ************************************************************/
3094 cursor c_cursor ( cp_process_mode in varchar
3095 ,cp_geocode_patch_name in varchar
3096 ) is
3097
3098 select hla.location_code "Work Location",
3099 hla.description "Location Description",
3100 hla.address_line_1 "Address",
3101 hla.town_or_city "City Name",
3102 hla.region_1 "County",
3103 hla.region_2 "State",
3104 hla.postal_code "Zipcode"
3105 from hr_locations_all hla
3106 where hla.location_id in
3107 (select distinct location_id
3108 from per_assignments_f paf,
3109 pay_us_emp_city_tax_rules_f pctr
3110 where ( ( pctr.STATE_CODE = '26'
3111 and pctr.county_code = '510'
3112 and pctr.city_code = '1270')
3113 or ( pctr.state_code = '21'
3114 and pctr.county_code = '510'
3115 and pctr.city_code = '0040')
3116 )
3117 and pctr.assignment_id = paf.assignment_id )
3118 and postal_code in
3119 ( '63142',
3120 '63148',
3121 '63149',
3122 '63152',
3123 '63153',
3124 '63154',
3125 '63159',
3126 '63161',
3127 '63162',
3128 '63165',
3129 '63168',
3130 '63170',
3131 '63172',
3132 '63173',
3133 '63174',
3134 '63175',
3135 '63176',
3136 '63181',
3137 '63183',
3138 '63184',
3139 '63185',
3140 '63186',
3141 '63187',
3142 '63189',
3143 '63191',
3144 '63192',
3145 '63193',
3146 '63194',
3147 '21232',
3148 '21238',
3149 '21242',
3150 '21243',
3151 '21245',
3152 '21246',
3153 '21247',
3154 '21248',
3155 '21249',
3156 '21253',
3157 '21254',
3158 '21255',
3159 '21256',
3160 '21257',
3161 '21258',
3162 '21259',
3163 '21260',
3164 '21261',
3165 '21262',
3166 '21266',
3167 '21267',
3168 '21269',
3169 '21271',
3170 '21272',
3171 '21276',
3172 '21277',
3173 '21291',
3174 '21292',
3175 '21293',
3176 '21294',
3177 '21295',
3178 '21296',
3179 '21299' );
3180
3181
3182
3183 ln_work_location varchar2(20);
3184 ln_loc_description varchar2(20);
3185 ln_address varchar2(30);
3186 ln_city_name varchar2(20);
3187 ln_county varchar2(20);
3188 ln_state varchar2(5);
3189 ln_zip_code varchar2(10);
3190
3191 lv_header_label VARCHAR2(32000);
3192
3196 lv_format1 varchar2(32000);
3193 lv_data_row VARCHAR2(32000);
3194 lv_data_row1 VARCHAR2(32000);
3195
3197
3198
3199 BEGIN
3200
3201 hr_utility.set_location(gv_package_name || '.report_14', 10);
3202
3203 /****************************************************************
3204 ** Concatnating the second Header Label which includes the User
3205 ** Defined data set so that it is printed at the end of the
3206 ** report.
3207 ****************************************************************/
3208
3209 fnd_file.put_line(fnd_file.output, formated_header_string(
3210 'XIV. Work Location ZIP Code Support.'
3211 ,p_output_file_type
3212 ));
3213
3214 fnd_file.put_line(fnd_file.output, formated_header_string(
3215 'ACTION REQUIRED.'
3216 ,p_output_file_type
3217 ));
3218
3219 fnd_file.put_line(fnd_file.output, formated_header_string(
3220 'The following work locations are using ZIP Codes no longer '
3221 ,p_output_file_type
3222 ));
3223
3224 fnd_file.put_line(fnd_file.output, formated_header_string(
3225 'supported by Vertex. Please review these locations and adjust'
3226 ,p_output_file_type
3227 ));
3228
3229 fnd_file.put_line(fnd_file.output, formated_header_string(
3230 'Their ZIP Codes to supported values.'
3231 ,p_output_file_type
3232 ));
3233
3234
3235 hr_utility.set_location(gv_package_name || '.report_14', 15);
3236 /****************************************************************
3237 ** Print the Header Information. If the format is HTML then open
3238 ** the body and table before printing the header info, otherwise
3239 ** just print the header information.
3240 ****************************************************************/
3241 if p_output_file_type ='HTML' then
3242 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<body>');
3243 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<table border=1>');
3244 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<tr>');
3245 end if;
3246 hr_utility.set_location(gv_package_name || '.report_14', 20);
3247
3248 lv_format1 :=
3249 formated_data_string (p_input_string => 'Work Location'
3250 ,p_bold => 'Y'
3251 ,p_output_file_type => p_output_file_type) ||
3252
3253 formated_data_string (p_input_string => 'Location Description'
3254 ,p_bold => 'Y'
3255 ,p_output_file_type => p_output_file_type) ||
3256
3257 formated_data_string (p_input_string => 'Address'
3258 ,p_bold => 'Y'
3259 ,p_output_file_type => p_output_file_type) ||
3260
3261 formated_data_string (p_input_string => 'City Name'
3262 ,p_bold => 'Y'
3263 ,p_output_file_type => p_output_file_type) ||
3264
3265 formated_data_string (p_input_string => 'County'
3266 ,p_bold => 'Y'
3267 ,p_output_file_type => p_output_file_type) ||
3268
3269 formated_data_string (p_input_string => 'State'
3270 ,p_bold => 'Y'
3271 ,p_output_file_type => p_output_file_type) ||
3272
3273 formated_data_string (p_input_string => 'Zipcode'
3274 ,p_bold => 'Y'
3275 ,p_output_file_type => p_output_file_type)
3276 ;
3277
3278 hr_utility.set_location(gv_package_name || '.report_14', 30);
3279
3280
3281 fnd_file.put_line(fnd_file.output, lv_format1);
3282
3283 if p_output_file_type ='HTML' then
3284 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '</tr>');
3285 end if;
3286
3287 hr_utility.set_location(gv_package_name || '.report_14', 40);
3288 /*****************************************************
3289 ** Start of the Data Section of the Report
3290 *****************************************************/
3291
3292 -- HR_UTILITY.TRACE_ON(NULL,'TCL');
3293 open c_cursor( p_process_mode
3294 ,p_geocode_patch_name
3295 );
3296
3297 loop
3298 hr_utility.set_location(gv_package_name || '.report_14', 50);
3299
3300 fetch c_cursor into ln_work_location
3301 ,ln_loc_description
3302 ,ln_address
3303 ,ln_city_name
3304 ,ln_county
3305 ,ln_state
3306 ,ln_zip_code;
3307
3308 if c_cursor%notfound then
3312
3309 hr_utility.set_location(gv_package_name || '.report_14', 60);
3310 exit;
3311 end if;
3313
3314 /************************************************************
3315 ** If Assignment Set is used, pick up only those employee
3316 ** assignments which are part of the Assignment Set - STATIC
3317 ** or DYNAMIC.
3318 ************************************************************/
3319 hr_utility.set_location(gv_package_name || '.report_14', 70);
3320
3321 hr_utility.set_location(gv_package_name || '.report_14', 80);
3322 formated_static_data( ln_work_location
3323 ,null
3324 ,ln_loc_description
3325 ,ln_city_name
3326 ,ln_county
3327 ,ln_state
3328 ,ln_zip_code
3329 ,null
3330 ,ln_address
3331 ,p_output_file_type
3332 ,lv_data_row1);
3333
3334 lv_data_row := lv_data_row1;
3335 hr_utility.set_location(gv_package_name || '.report_14', 90);
3336
3337 -- if p_output_file_type ='HTML' then
3338 lv_data_row := '<tr>' || lv_data_row || '</tr>' ;
3339 -- end if;
3340
3341 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, lv_data_row);
3342
3343
3344 /*****************************************************************
3345 ** initialize Data varaibles
3346 *****************************************************************/
3347 lv_data_row := null;
3348 end loop;
3349 close c_cursor;
3350
3351 /*****************************************************
3352 ** Close of the Data Section of the Report
3353 *****************************************************/
3354
3355 if p_output_file_type ='HTML' then
3356 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '</table></body></html>');
3357 end if;
3358
3359 END report_14;
3360
3361 PROCEDURE report_15
3362 (p_process_mode in varchar2
3363 ,p_geocode_patch_name in varchar2
3364 ,p_output_file_type in varchar2
3365 )
3366 IS
3367
3368
3369 /************************************************************
3370 ** Cursor to get all the employee and assignment data.
3371 ************************************************************/
3372 cursor c_cursor ( cp_process_mode in varchar
3373 ,cp_geocode_patch_name in varchar
3374 ) is
3375
3376 SELECT distinct substr(ppf.full_name,1,40),
3377 substr(addr.address_line1 ,1,30),
3378 substr(addr.town_or_city,1,20),
3379 substr(addr.region_1 ,1,20),
3380 substr(addr.region_2 ,1,5),
3381 substr(addr.postal_code ,1,10)
3382 from per_addresses addr,
3383 per_all_people_f ppf
3384 where addr.person_id = ppf.person_id
3385 and ppf.person_id in
3386 (select distinct person_id
3387 from per_assignments_f paf,
3388 pay_us_emp_city_tax_rules_f pctr
3389 where ( ( pctr.STATE_CODE = '26'
3390 and pctr.county_code = '510'
3391 and pctr.city_code = '1270')
3392 or ( pctr.state_code = '21'
3393 and pctr.county_code = '510'
3394 and pctr.city_code = '0040')
3395 )
3396 and pctr.assignment_id = paf.assignment_id )
3397 and addr.postal_code in
3398 ( '63142',
3399 '63148',
3400 '63149',
3401 '63152',
3402 '63153',
3403 '63154',
3404 '63159',
3405 '63161',
3406 '63162',
3407 '63165',
3408 '63168',
3409 '63170',
3410 '63172',
3411 '63173',
3412 '63174',
3413 '63175',
3414 '63176',
3415 '63181',
3416 '63183',
3417 '63184',
3418 '63185',
3419 '63186',
3420 '63187',
3421 '63189',
3422 '63191',
3423 '63192',
3424 '63193',
3425 '63194',
3426 '21232',
3427 '21238',
3428 '21242',
3429 '21243',
3430 '21245',
3431 '21246',
3432 '21247',
3433 '21248',
3434 '21249',
3435 '21253',
3436 '21254',
3437 '21255',
3438 '21256',
3439 '21257',
3440 '21258',
3441 '21259',
3442 '21260',
3443 '21261',
3444 '21262',
3445 '21266',
3446 '21267',
3447 '21269',
3451 '21277',
3448 '21271',
3449 '21272',
3450 '21276',
3452 '21291',
3453 '21292',
3454 '21293',
3455 '21294',
3456 '21295',
3457 '21296',
3458 '21299' );
3459
3460
3461
3462 ln_full_name varchar2(40);
3463 ln_address varchar2(30);
3464 ln_city_name varchar2(20);
3465 ln_county varchar2(20);
3466 ln_state varchar2(5);
3467 ln_zip_code varchar2(10);
3468
3469 lv_header_label VARCHAR2(32000);
3470
3471 lv_data_row VARCHAR2(32000);
3472 lv_data_row1 VARCHAR2(32000);
3473
3474 lv_format1 varchar2(32000);
3475
3476
3477 BEGIN
3478
3479 hr_utility.set_location(gv_package_name || '.report_15', 10);
3480
3481 /****************************************************************
3482 ** Concatnating the second Header Label which includes the User
3483 ** Defined data set so that it is printed at the end of the
3484 ** report.
3485 ****************************************************************/
3486
3487 fnd_file.put_line(fnd_file.output, formated_header_string(
3488 'XV. Home Address ZIP Code Support.'
3489 ,p_output_file_type
3490 ));
3491
3492 fnd_file.put_line(fnd_file.output, formated_header_string(
3493 'ACTION REQUIRED.'
3494 ,p_output_file_type
3495 ));
3496
3497 fnd_file.put_line(fnd_file.output, formated_header_string(
3498 'The following home addresses are using ZIP Codes no longer '
3499 ,p_output_file_type
3500 ));
3501
3502 fnd_file.put_line(fnd_file.output, formated_header_string(
3503 'supported by Vertex. Please review these addresses and adjust'
3504 ,p_output_file_type
3505 ));
3506
3507 fnd_file.put_line(fnd_file.output, formated_header_string(
3508 'their ZIP Codes to supported values.'
3509 ,p_output_file_type
3510 ));
3511
3512
3513 hr_utility.set_location(gv_package_name || '.report_15', 15);
3514 /****************************************************************
3515 ** Print the Header Information. If the format is HTML then open
3516 ** the body and table before printing the header info, otherwise
3517 ** just print the header information.
3518 ****************************************************************/
3519 if p_output_file_type ='HTML' then
3520 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<body>');
3521 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<table border=1>');
3522 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<tr>');
3523 end if;
3524 hr_utility.set_location(gv_package_name || '.report_15', 20);
3525
3526 lv_format1 :=
3527 formated_data_string (p_input_string => 'Full Name'
3528 ,p_bold => 'Y'
3529 ,p_output_file_type => p_output_file_type) ||
3530
3531 formated_data_string (p_input_string => 'Address'
3532 ,p_bold => 'Y'
3533 ,p_output_file_type => p_output_file_type) ||
3534
3535 formated_data_string (p_input_string => 'City Name'
3536 ,p_bold => 'Y'
3537 ,p_output_file_type => p_output_file_type) ||
3538
3539 formated_data_string (p_input_string => 'County'
3540 ,p_bold => 'Y'
3541 ,p_output_file_type => p_output_file_type) ||
3542
3543 formated_data_string (p_input_string => 'State'
3544 ,p_bold => 'Y'
3545 ,p_output_file_type => p_output_file_type) ||
3546
3547 formated_data_string (p_input_string => 'Zipcode'
3548 ,p_bold => 'Y'
3549 ,p_output_file_type => p_output_file_type)
3550 ;
3551
3552 hr_utility.set_location(gv_package_name || '.report_15', 30);
3553
3554
3555 fnd_file.put_line(fnd_file.output, lv_format1);
3556
3557 if p_output_file_type ='HTML' then
3558 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '</tr>');
3559 end if;
3560
3561 hr_utility.set_location(gv_package_name || '.report_15', 40);
3562 /*****************************************************
3563 ** Start of the Data Section of the Report
3564 *****************************************************/
3565
3566 -- HR_UTILITY.TRACE_ON(NULL,'TCL');
3567 open c_cursor( p_process_mode
3568 ,p_geocode_patch_name
3572 hr_utility.set_location(gv_package_name || '.report_15', 50);
3569 );
3570
3571 loop
3573
3574 fetch c_cursor into ln_full_name
3575 ,ln_address
3576 ,ln_city_name
3577 ,ln_county
3578 ,ln_state
3579 ,ln_zip_code;
3580
3581 if c_cursor%notfound then
3582 hr_utility.set_location(gv_package_name || '.report_15', 60);
3583 exit;
3584 end if;
3585
3586
3587 /************************************************************
3588 ** If Assignment Set is used, pick up only those employee
3589 ** assignments which are part of the Assignment Set - STATIC
3590 ** or DYNAMIC.
3591 ************************************************************/
3592 hr_utility.set_location(gv_package_name || '.report_15', 70);
3593
3594 hr_utility.set_location(gv_package_name || '.report_15', 80);
3595 formated_static_data( ln_full_name
3596 ,null
3597 ,null
3598 ,ln_city_name
3599 ,ln_county
3600 ,ln_state
3601 ,ln_zip_code
3602 ,null
3603 ,ln_address
3604 ,p_output_file_type
3605 ,lv_data_row1);
3606
3607 lv_data_row := lv_data_row1;
3608 hr_utility.set_location(gv_package_name || '.report_15', 90);
3609
3610 -- if p_output_file_type ='HTML' then
3611 lv_data_row := '<tr>' || lv_data_row || '</tr>' ;
3612 -- end if;
3613
3614 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, lv_data_row);
3615
3616
3617 /*****************************************************************
3618 ** initialize Data varaibles
3619 *****************************************************************/
3620 lv_data_row := null;
3621 end loop;
3622 close c_cursor;
3623
3624 /*****************************************************
3625 ** Close of the Data Section of the Report
3626 *****************************************************/
3627
3628 if p_output_file_type ='HTML' then
3629 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '</table></body></html>');
3630 end if;
3631
3632 END report_15;
3633
3634
3635 /*****************************************************************
3636 ** This is the main procedure which is called from the Concurrent
3637 ** Request. All the paramaters are passed based on which it will
3638 ** either print a CSV format or an HTML format file.
3639 *****************************************************************/
3640 PROCEDURE extract_data
3641 (errbuf out nocopy varchar2
3642 ,retcode out nocopy number
3643 ,p_process_mode in varchar2
3644 ,p_geocode_patch_name in varchar2
3645
3646 )
3647 IS
3648
3649 lv_output_file_type varchar2(4);
3650
3651 BEGIN
3652
3653 lv_output_file_type := 'HTML';
3654
3655 hr_utility.set_location(gv_package_name || '.extract_data', 10);
3656
3657 report_1 ( p_process_mode => p_process_mode
3658 ,p_geocode_patch_name => p_geocode_patch_name
3659 ,p_output_file_type => lv_output_file_type);
3660
3661 report_2 ( p_process_mode => p_process_mode
3662 ,p_geocode_patch_name => p_geocode_patch_name
3663 ,p_output_file_type => lv_output_file_type);
3664
3665 report_3 ( p_process_mode => p_process_mode
3666 ,p_geocode_patch_name => p_geocode_patch_name
3667 ,p_output_file_type => lv_output_file_type);
3668
3669 report_4 ( p_process_mode => p_process_mode
3670 ,p_geocode_patch_name => p_geocode_patch_name
3671 ,p_output_file_type => lv_output_file_type);
3672
3673 report_5 ( p_process_mode => p_process_mode
3674 ,p_geocode_patch_name => p_geocode_patch_name
3675 ,p_output_file_type => lv_output_file_type);
3676
3677 report_6 ( p_process_mode => p_process_mode
3678 ,p_geocode_patch_name => p_geocode_patch_name
3679 ,p_output_file_type => lv_output_file_type);
3680
3681 report_7 ( p_process_mode => p_process_mode
3682 ,p_geocode_patch_name => p_geocode_patch_name
3683 ,p_output_file_type => lv_output_file_type);
3684
3685 report_8 ( p_process_mode => p_process_mode
3686 ,p_geocode_patch_name => p_geocode_patch_name
3687 ,p_output_file_type => lv_output_file_type);
3688
3689 report_9 ( p_process_mode => p_process_mode
3690 ,p_geocode_patch_name => p_geocode_patch_name
3691 ,p_output_file_type => lv_output_file_type);
3692
3693 report_10 ( p_process_mode => p_process_mode
3694 ,p_geocode_patch_name => p_geocode_patch_name
3695 ,p_output_file_type => lv_output_file_type);
3696
3697 report_11 ( p_process_mode => p_process_mode
3698 ,p_geocode_patch_name => p_geocode_patch_name
3699 ,p_output_file_type => lv_output_file_type);
3700
3704
3701 report_12 ( p_process_mode => p_process_mode
3702 ,p_geocode_patch_name => p_geocode_patch_name
3703 ,p_output_file_type => lv_output_file_type);
3705 report_13 ( p_process_mode => p_process_mode
3706 ,p_geocode_patch_name => p_geocode_patch_name
3707 ,p_output_file_type => lv_output_file_type);
3708
3709 report_14 ( p_process_mode => p_process_mode
3710 ,p_geocode_patch_name => p_geocode_patch_name
3711 ,p_output_file_type => lv_output_file_type);
3712
3713 report_15 ( p_process_mode => p_process_mode
3714 ,p_geocode_patch_name => p_geocode_patch_name
3715 ,p_output_file_type => lv_output_file_type);
3716
3717 hr_utility.trace('Concurrent Request ID = ' || FND_GLOBAL.CONC_REQUEST_ID);
3718
3719 END extract_data;
3720
3721 --begin
3722 --hr_utility.trace_on(null, 'ELE');
3723 end pay_us_geocode_report_pkg;