[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.11.12020000.5 2012/07/19 09:26:59 emunisek 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 05-NOV-2008 tclewis 115.3 7516651 Added distinct to Report 14,
32 7 and 8.
33 27-Aug-2009 jdevasah 115.4 8829668 changed size of local variables
34 defined in report14 and report15
35 28-Aug-2009 jdevasah 115.5,6 8843479 Report7: changed cursor definition
36 22-Oct-2009 jdevasah 115.9 7476966 Report1: Modified c_cursor so that
37 errored employee records are
38 reported even in debug mode
39 12-Jul-2010 emunisek 115.10 7476966 Report1: Modified c_cursor so that
40 employees without GRE information
41 will be caught correctly.
42 15-Jul-2010 emunisek 115.11 9541247 Updated report_12 to indicate the
43 update to Person and Location Address
44 Details for a change in county name
45 16-Jul-2010 emunisek 115.13 AGEO2010 Corrected the Data Formatting in
46 Report_14 to match the data of Location
47 being displayed with Header Record.
48 14-Jul-2011 emunisek 115.14 10060041 Modified function Report_12 to display
49 the County Update Message based on the
50 data found in pay_us_geo_update table
51 unlike the earlier hard-coded message.
52 18-Jul-2011 emunisek 115.15 AGEO2011 Modified the package to take the 30
53 characters of the city name unlike the
54 earlier 20 characters limit as it is
55 causing confusion in case of long city
56 names
57 18-Jul-2011 emunisek 115.16 AGEO2011 Modified the cursor c_cursor of procedure
58 report_7 so that only the Employees who
59 had association with the Primary City Flag
60 changed cities get displayed unlike the
61 check based on city_code earlier.
62 15-Jul-2012 emunisek 115.17 14314081 Added below procedures to support the
63 City Name changes delivered as part of
64 Annual Geocode 2012 Data Update.
65 1.report_16
66 2.city_name_change_report
67 3.formated_static_data3
68 4.formated_static_data4
69 5.formated_static_data5
70 6.formated_static_data6
71 17-Jul-2012 emunisek 115.18 14314081 Added changes to make sure that City Name
72 is checked while generating output to generate
73 correct results
74 18-Jul-2012 emunisek 115.19 AGEO2012 Under report_5, new_juri_code was shown as
75 Old Jurisdiction and old_juri_code as New
76 Jurisdiction. Corrected the issue
77 19-Jul-2012 emunisek 115.20 14314081 Added changes to check Old City Name saved in
78 PAY_US_GEO_UPDATE table to give correct output
79 report
80 */
81
82 /************************************************************
83 ** Local Package Variables
84 ************************************************************/
85 gv_title VARCHAR2(100);
86
87 gc_csv_delimiter VARCHAR2(1) := ',';
88 gc_csv_data_delimiter VARCHAR2(1) := '"';
89
90 gv_html_start_data VARCHAR2(5) := '<td>' ;
91 gv_html_end_data VARCHAR2(5) := '</td>' ;
92
93 gv_package_name VARCHAR2(50) := 'pay_us_geocode_report_pkg';
94
95
96 /******************************************************************
97 ** Function Returns the formated input string based on the
98 ** Output format. If the format is CSV then the values are returned
99 ** seperated by comma (,). If the format is HTML then the returned
100 ** string as the HTML tags. The parameter p_bold only works for
101 ** the HTML format.
102 ******************************************************************/
103 FUNCTION formated_data_string
104 (p_input_string in varchar2
105 ,p_output_file_type in varchar2
106 ,p_bold in varchar2 default 'N'
107 )
108 RETURN VARCHAR2
109 IS
110
111 lv_format varchar2(1000);
112
113 BEGIN
114 hr_utility.set_location(gv_package_name || '.formated_data_string', 10);
115 if p_output_file_type = 'CSV' then
116 hr_utility.set_location(gv_package_name || '.formated_data_string', 20);
117 lv_format := gc_csv_data_delimiter || p_input_string ||
118 gc_csv_data_delimiter || gc_csv_delimiter;
119 elsif p_output_file_type = 'HTML' then
120 if p_input_string is null then
121 hr_utility.set_location(gv_package_name || '.formated_data_string', 30);
122 lv_format := gv_html_start_data || '&'||'nbsp;' || gv_html_end_data;
123 else
124 if p_bold = 'Y' then
125 hr_utility.set_location(gv_package_name || '.formated_data_string', 40);
126 lv_format := gv_html_start_data || '<b> ' || p_input_string
127 || '</b>' || gv_html_end_data;
128 else
129 hr_utility.set_location(gv_package_name || '.formated_data_string', 50);
130 lv_format := gv_html_start_data || p_input_string || gv_html_end_data;
131 end if;
132 end if;
133 end if;
134
135 hr_utility.set_location(gv_package_name || '.formated_data_string', 60);
136 return lv_format;
137
138 END formated_data_string;
139
140
141 /************************************************************
142 ** Function returns the string with the HTML Header tags
143 ************************************************************/
144 FUNCTION formated_header_string
145 (p_input_string in varchar2
146 ,p_output_file_type in varchar2
147 )
148 RETURN VARCHAR2
149 IS
150
151 lv_format varchar2(1000);
152
153 BEGIN
154 hr_utility.set_location(gv_package_name || '.formated_header_string', 10);
155 if p_output_file_type = 'CSV' then
156 hr_utility.set_location(gv_package_name || '.formated_header_string', 20);
157 lv_format := p_input_string;
158 elsif p_output_file_type = 'HTML' then
159 hr_utility.set_location(gv_package_name || '.formated_header_string', 30);
160 -- lv_format := '<HTML><HEAD> <CENTER> <H1> <B>' || p_input_string ||
161 -- '</B></H1></CENTER></HEAD>';
162 lv_format := '<HTML> <P> ' || p_input_string ||
163 '</P>';
164 end if;
165
166 hr_utility.set_location(gv_package_name || '.formated_header_string', 40);
167 return lv_format;
168
169 END formated_header_string;
170
171
172 /*****************************************************************
173 ** This procudure returns the Mandatory Static Labels.
174 *****************************************************************/
175 PROCEDURE formated_static_header(
176 p_output_file_type in varchar2
177 ,p_static_label1 out nocopy varchar2
178 )
179 IS
180
181 lv_format1 varchar2(32000);
182
183 BEGIN
184
185 hr_utility.set_location(gv_package_name || '.formated_static_header', 10);
186
187 lv_format1 :=
188 formated_data_string (p_input_string => 'Full Name'
189 ,p_bold => 'Y'
190 ,p_output_file_type => p_output_file_type) ||
191
192 formated_data_string (p_input_string => 'Assignment Id'
193 ,p_bold => 'Y'
194 ,p_output_file_type => p_output_file_type) ||
195 formated_data_string (p_input_string => 'Assignment Number'
196 ,p_bold => 'Y'
197 ,p_output_file_type => p_output_file_type) ||
198
199 formated_data_string (p_input_string =>'Error Description'
200 ,p_bold => 'Y'
201 ,p_output_file_type => p_output_file_type)
202 ;
203
204 hr_utility.set_location(gv_package_name || '.formated_static_header', 20);
205
206 p_static_label1 := lv_format1;
207 hr_utility.trace('Static Label1 = ' || lv_format1);
208 hr_utility.set_location(gv_package_name || '.formated_static_header', 40);
209
210 END;
211
212
213 /*****************************************************************
214 ** This procudure returns the Mandatory Static Labels.
215 *****************************************************************/
216 PROCEDURE formated_static_data (
217 p_full_name in varchar2
218 ,p_assignment_id in number
219 ,p_assignment_number in varchar2
220 ,p_error_description in varchar2
221 ,p_juri_code_1 in varchar2
222 ,p_city_name_1 in varchar2
223 ,p_juri_code_2 in varchar2
224 ,p_city_name_2 in varchar2
225 ,p_table_updated in varchar2
226 ,p_output_file_type in varchar2
227 ,p_static_data1 out nocopy varchar2
228 )
229 IS
230
231 lv_format1 VARCHAR2(32000);
232 lv_format2 VARCHAR2(32000);
233
234
235 BEGIN
236
237 hr_utility.set_location(gv_package_name || '.formated_static_data', 10);
238
239 if p_full_name is not NULL THEN
240
241 lv_format1 := lv_format1 ||
242 formated_data_string (p_input_string => p_full_name
243 ,p_output_file_type => p_output_file_type) ;
244 end if;
245
246 if p_assignment_id is not NULL then
247 lv_format1 := lv_format1 ||
248 formated_data_string (p_input_string => p_assignment_id
249 ,p_output_file_type => p_output_file_type);
250 end if;
251
252 if p_assignment_number is not NULL then
253 lv_format1 := lv_format1 ||
254 formated_data_string (p_input_string => p_assignment_number
255 ,p_output_file_type => p_output_file_type);
256 end if;
257
258 if p_error_description is not NULL then
259 lv_format1 := lv_format1 ||
260 formated_data_string (p_input_string => p_error_description
261 ,p_output_file_type => p_output_file_type);
262 end if;
263
264 if p_juri_code_1 is not NULL then
265 lv_format1 := lv_format1 ||
266 formated_data_string (p_input_string => p_juri_code_1
267 ,p_output_file_type => p_output_file_type);
268 end if;
269
270 if p_city_name_1 is not NULL then
271 lv_format1 := lv_format1 ||
272 formated_data_string (p_input_string => p_city_name_1
273 ,p_output_file_type => p_output_file_type);
274 end if;
275
276 if p_juri_code_2 is not NULL then
277 lv_format1 := lv_format1 ||
278 formated_data_string (p_input_string => p_juri_code_2
279 ,p_output_file_type => p_output_file_type);
280 end if;
281
282 if p_city_name_2 is not NULL then
283 lv_format1 := lv_format1 ||
284 formated_data_string (p_input_string => p_city_name_2
285 ,p_output_file_type => p_output_file_type);
286 end if;
287
288 if p_table_updated is not NULL then
289 lv_format1 := lv_format1 ||
290 formated_data_string (p_input_string => p_table_updated
291 ,p_output_file_type => p_output_file_type);
292 end if;
293
294
295 hr_utility.set_location(gv_package_name || '.formated_static_data', 30);
296
297
298 p_static_data1 := lv_format1;
299 hr_utility.trace('Static Data1 = ' || lv_format1);
300 hr_utility.set_location(gv_package_name || '.formated_static_data', 40);
301
302 END formated_static_data;
303 /*****************************************************************
304 ** This procudure returns the Mandatory Static Labels.
305 *****************************************************************/
306 PROCEDURE formated_static_data2 (
307 p_full_name in varchar2
308 ,p_assignment_id in number
309 ,p_assignment_number in varchar2
310 ,p_city_name in varchar2
311 ,p_county_name in varchar2
312 ,p_state_abbrev in varchar2
313 ,p_old_juri_code in varchar2
314 ,p_new_juri_code in varchar2
315 ,p_output_file_type in varchar2
316 ,p_static_data1 out nocopy varchar2
317 )
318 IS
319
320 lv_format1 VARCHAR2(32000);
321 lv_format2 VARCHAR2(32000);
322
323
324 BEGIN
325
326 hr_utility.set_location(gv_package_name || '.formated_static_data2', 10);
327 lv_format1 :=
328 formated_data_string (p_input_string => p_full_name
329 ,p_output_file_type => p_output_file_type) ||
330 formated_data_string (p_input_string => p_assignment_id
331 ,p_output_file_type => p_output_file_type) ||
332 formated_data_string (p_input_string => p_assignment_number
333 ,p_output_file_type => p_output_file_type) ;
334
335
336 if p_city_name is not NULL then
337 lv_format1 := lv_format1 ||
338 formated_data_string (p_input_string => p_city_name
339 ,p_output_file_type => p_output_file_type);
340 end if;
341
342 if p_county_name is not NULL then
343 lv_format1 := lv_format1 ||
344 formated_data_string (p_input_string => p_county_name
345 ,p_output_file_type => p_output_file_type);
346 end if;
347
348 if p_state_abbrev is not NULL then
349 lv_format1 := lv_format1 ||
350 formated_data_string (p_input_string => p_state_abbrev
351 ,p_output_file_type => p_output_file_type);
352 end if;
353
354 if p_old_juri_code is not NULL then
355 lv_format1 := lv_format1 ||
356 formated_data_string (p_input_string => p_old_juri_code
357 ,p_output_file_type => p_output_file_type);
358 end if;
359
360 if p_new_juri_code is not NULL then
361 lv_format1 := lv_format1 ||
362 formated_data_string (p_input_string => p_new_juri_code
363 ,p_output_file_type => p_output_file_type);
364 end if;
365
366
367 hr_utility.set_location(gv_package_name || '.formated_static_data2', 30);
368
369
370 p_static_data1 := lv_format1;
371 hr_utility.trace('Static Data1 = ' || lv_format1);
372 hr_utility.set_location(gv_package_name || '.formated_static_data2', 40);
373
374 END formated_static_data2;
375
376 /*Added for Bug#14314081 */
377
378 /*****************************************************************
379 ** This procudure returns the City Change Details
380 *****************************************************************/
381
382 PROCEDURE formated_static_data3 (
383 p_country in varchar2
384 ,p_state_code in varchar2
385 ,p_state_abbrev in varchar2
386 ,p_county_code in varchar2
387 ,p_city_code in varchar2
388 ,p_old_city_name in varchar2
389 ,p_new_city_name in varchar2
390 ,p_output_file_type in varchar2
391 ,p_static_data1 out nocopy varchar2
392 )
393 IS
394
395 lv_format1 VARCHAR2(32000);
396 lv_format2 VARCHAR2(32000);
397
398
399 BEGIN
400
401 hr_utility.set_location(gv_package_name || '.formated_static_data3', 10);
402
403 if p_country is not NULL THEN
404
405 lv_format1 := lv_format1 ||
406 formated_data_string (p_input_string => p_country
407 ,p_output_file_type => p_output_file_type) ;
408 end if;
409
410 if p_state_code is not NULL then
411 lv_format1 := lv_format1 ||
412 formated_data_string (p_input_string => p_state_code
413 ,p_output_file_type => p_output_file_type);
414 end if;
415
416 if p_state_abbrev is not NULL then
417 lv_format1 := lv_format1 ||
418 formated_data_string (p_input_string => p_state_abbrev
419 ,p_output_file_type => p_output_file_type);
420 end if;
421
422 if p_county_code is not NULL then
423 lv_format1 := lv_format1 ||
424 formated_data_string (p_input_string => p_county_code
425 ,p_output_file_type => p_output_file_type);
426 end if;
427
428 if p_city_code is not NULL then
429 lv_format1 := lv_format1 ||
430 formated_data_string (p_input_string => p_city_code
431 ,p_output_file_type => p_output_file_type);
432 end if;
433
434 if p_old_city_name is not NULL then
435 lv_format1 := lv_format1 ||
436 formated_data_string (p_input_string => p_old_city_name
437 ,p_output_file_type => p_output_file_type);
438 end if;
439
440 if p_new_city_name is not NULL then
441 lv_format1 := lv_format1 ||
442 formated_data_string (p_input_string => p_new_city_name
443 ,p_output_file_type => p_output_file_type);
444 end if;
445
446 hr_utility.set_location(gv_package_name || '.formated_static_data3', 30);
447
448
449 p_static_data1 := lv_format1;
450 hr_utility.trace('Static Data1 = ' || lv_format1);
451 hr_utility.set_location(gv_package_name || '.formated_static_data3', 40);
452
453 END formated_static_data3;
454
455 /*************************************************************************
456 ** This procudure returns the Person Address Details for City Name change
457 *************************************************************************/
458
459 PROCEDURE formated_static_data4 (
460 p_full_name in varchar2
461 ,p_person_id in number
462 ,p_employee_number in varchar2
463 ,p_jurisdiction_code in varchar2
464 ,p_old_city_name in varchar2
465 ,p_new_city_name in varchar2
466 ,p_update_description in varchar2
467 ,p_output_file_type in varchar2
468 ,p_static_data1 out nocopy varchar2
469 )
470 IS
471
472 lv_format1 VARCHAR2(32000);
473 lv_format2 VARCHAR2(32000);
474
475
476 BEGIN
477
478 hr_utility.set_location(gv_package_name || '.formated_static_data4', 10);
479
480 if p_full_name is not NULL THEN
481
482 lv_format1 := lv_format1 ||
483 formated_data_string (p_input_string => p_full_name
484 ,p_output_file_type => p_output_file_type) ;
485 end if;
486
487 if p_person_id is not NULL then
488 lv_format1 := lv_format1 ||
489 formated_data_string (p_input_string => p_person_id
490 ,p_output_file_type => p_output_file_type);
491 end if;
492
493 if p_employee_number is not NULL then
494 lv_format1 := lv_format1 ||
495 formated_data_string (p_input_string => p_employee_number
496 ,p_output_file_type => p_output_file_type);
497 end if;
498
499 if p_jurisdiction_code is not NULL then
500 lv_format1 := lv_format1 ||
501 formated_data_string (p_input_string => p_jurisdiction_code
502 ,p_output_file_type => p_output_file_type);
503 end if;
504
505 if p_old_city_name is not NULL then
506 lv_format1 := lv_format1 ||
507 formated_data_string (p_input_string => p_old_city_name
508 ,p_output_file_type => p_output_file_type);
509 end if;
510
511 if p_new_city_name is not NULL then
512 lv_format1 := lv_format1 ||
513 formated_data_string (p_input_string => p_new_city_name
514 ,p_output_file_type => p_output_file_type);
515 end if;
516
517 if p_update_description is not NULL then
518 lv_format1 := lv_format1 ||
519 formated_data_string (p_input_string => p_update_description
520 ,p_output_file_type => p_output_file_type);
521 end if;
522
523 hr_utility.set_location(gv_package_name || '.formated_static_data4', 30);
524
525
526 p_static_data1 := lv_format1;
527 hr_utility.trace('Static Data1 = ' || lv_format1);
528 hr_utility.set_location(gv_package_name || '.formated_static_data4', 40);
529
530 END formated_static_data4;
531
532 /***************************************************************************
533 ** This procudure returns the Location Address Details for City Name change
534 ****************************************************************************/
535
536 PROCEDURE formated_static_data5 (
537 p_location_name in varchar2
538 ,p_location_id in number
539 ,p_jurisdiction_code in varchar2
540 ,p_old_city_name in varchar2
541 ,p_new_city_name in varchar2
542 ,p_update_description in varchar2
543 ,p_output_file_type in varchar2
544 ,p_static_data1 out nocopy varchar2
545 )
546 IS
547
548 lv_format1 VARCHAR2(32000);
549 lv_format2 VARCHAR2(32000);
550
551
552 BEGIN
553
554 hr_utility.set_location(gv_package_name || '.formated_static_data5', 10);
555
556 if p_location_name is not NULL THEN
557
558 lv_format1 := lv_format1 ||
559 formated_data_string (p_input_string => p_location_name
560 ,p_output_file_type => p_output_file_type) ;
561 end if;
562
563 if p_location_id is not NULL then
564 lv_format1 := lv_format1 ||
565 formated_data_string (p_input_string => p_location_id
566 ,p_output_file_type => p_output_file_type);
567 end if;
568
569 if p_jurisdiction_code is not NULL then
570 lv_format1 := lv_format1 ||
571 formated_data_string (p_input_string => p_jurisdiction_code
572 ,p_output_file_type => p_output_file_type);
573 end if;
574
575 if p_old_city_name is not NULL then
576 lv_format1 := lv_format1 ||
577 formated_data_string (p_input_string => p_old_city_name
578 ,p_output_file_type => p_output_file_type);
579 end if;
580
581 if p_new_city_name is not NULL then
582 lv_format1 := lv_format1 ||
583 formated_data_string (p_input_string => p_new_city_name
584 ,p_output_file_type => p_output_file_type);
585 end if;
586
587 if p_update_description is not NULL then
588 lv_format1 := lv_format1 ||
589 formated_data_string (p_input_string => p_update_description
590 ,p_output_file_type => p_output_file_type);
591 end if;
592
593 hr_utility.set_location(gv_package_name || '.formated_static_data5', 30);
594
595
596 p_static_data1 := lv_format1;
597 hr_utility.trace('Static Data1 = ' || lv_format1);
598 hr_utility.set_location(gv_package_name || '.formated_static_data5', 40);
599
600 END formated_static_data5;
601
602 /************************************************************************************
603 ** This procudure returns the Organization Information Details for City Name change
604 *************************************************************************************/
605
606 PROCEDURE formated_static_data6 (
607 p_organization_name in varchar2
608 ,p_organization_id in number
609 ,p_jurisdiction_code in varchar2
610 ,p_old_city_name in varchar2
611 ,p_new_city_name in varchar2
612 ,p_update_description in varchar2
613 ,p_output_file_type in varchar2
614 ,p_static_data1 out nocopy varchar2
615 )
616 IS
617
618 lv_format1 VARCHAR2(32000);
619 lv_format2 VARCHAR2(32000);
620
621
622 BEGIN
623
624 hr_utility.set_location(gv_package_name || '.formated_static_data6', 10);
625
626 if p_organization_name is not NULL THEN
627
628 lv_format1 := lv_format1 ||
629 formated_data_string (p_input_string => p_organization_name
630 ,p_output_file_type => p_output_file_type) ;
631 end if;
632
633 if p_organization_id is not NULL then
634 lv_format1 := lv_format1 ||
635 formated_data_string (p_input_string => p_organization_id
636 ,p_output_file_type => p_output_file_type);
637 end if;
638
639 if p_jurisdiction_code is not NULL then
640 lv_format1 := lv_format1 ||
641 formated_data_string (p_input_string => p_jurisdiction_code
642 ,p_output_file_type => p_output_file_type);
643 end if;
644
645 if p_old_city_name is not NULL then
646 lv_format1 := lv_format1 ||
647 formated_data_string (p_input_string => p_old_city_name
648 ,p_output_file_type => p_output_file_type);
649 end if;
650
651 if p_new_city_name is not NULL then
652 lv_format1 := lv_format1 ||
653 formated_data_string (p_input_string => p_new_city_name
654 ,p_output_file_type => p_output_file_type);
655 end if;
656
657 if p_update_description is not NULL then
658 lv_format1 := lv_format1 ||
659 formated_data_string (p_input_string => p_update_description
660 ,p_output_file_type => p_output_file_type);
661 end if;
662
663 hr_utility.set_location(gv_package_name || '.formated_static_data6', 30);
664
665
666 p_static_data1 := lv_format1;
667 hr_utility.trace('Static Data1 = ' || lv_format1);
668 hr_utility.set_location(gv_package_name || '.formated_static_data6', 40);
669
670 END formated_static_data6;
671
672 /*End of changes for Bug#14314081 */
673
674 PROCEDURE report_1
675 ( p_process_mode in varchar2
676 ,p_geocode_patch_name in varchar2
677 ,p_output_file_type in varchar2
678 )
679 IS
680
681
682 /************************************************************
683 ** Cursor to get all the employee and assignment data.
684 ************************************************************/
685 /*Bug#7476966: In debug mode, the process does not keep status
686 as 'P' for employees having issues with their assignemnt details
687 So we need to check integrity of assignment records explicitly.
688 Added additional or conditions to check inetegrity */
689
690 cursor c_cursor ( cp_process_mode in varchar
691 ,cp_geocode_patch_name in varchar
692 ) is
693 select distinct substr(ppf.full_name,1,40) ,
694 pef.assignment_id ,
695 substr(pef.assignment_number,1,17) ,
696 substr(pgu.description,1,65)
697 from per_people_f ppf,
698 per_assignments_f pef,
699 pay_us_geo_update pgu,
700 pay_patch_status pps,
701 pay_us_emp_city_tax_rules_f pctrf
702 where pef.assignment_id = pgu.assignment_id
703 and pctrf.assignment_id = pef.assignment_id
704 and pctrf.effective_start_Date <= pef.effective_end_date
705 and pctrf.effective_end_date >= pef.effective_start_Date
706 and ppf.person_id = pef.person_id
707 and pgu.id = pps.id
708 and pps.patch_name = p_geocode_patch_name
709 and ( pgu.status = 'P'
710 or pef.pay_basis_id is null
711 or pef.payroll_id is null
712 or pef.location_id is null
713 or pef.soft_coding_keyflex_id is null
714 or exists (select null
715 from hr_soft_coding_keyflex hsck
716 where hsck.soft_coding_keyflex_id =
717 pef.soft_coding_keyflex_id
718 and hsck.segment1 is null ))
719 and pgu.process_mode = p_process_mode;
720
721 ln_full_name varchar2(40);
722 ln_assignment_id number;
723 ln_assignment_number varchar2(17);
724 ln_error_description varchar2(65);
725
726 lv_header_label VARCHAR2(32000);
727
728 lv_data_row VARCHAR2(32000);
729 lv_data_row1 VARCHAR2(32000);
730
731
732 lv_format1 varchar2(32000);
733
734
735 BEGIN
736
737 hr_utility.set_location(gv_package_name || '.report_1', 10);
738
739 /****************************************************************
740 ** Concatnating the second Header Label which includes the User
741 ** Defined data set so that it is printed at the end of the
742 ** report.
743 ****************************************************************/
744
745 fnd_file.put_line(fnd_file.output, formated_header_string(
746 'THIS IS A LIST OF ASSIGNMENT INFORMATION FOR THE GEOCODE UPDATE QUARTERLY PATCH'
747 ,p_output_file_type
748 ));
749
750 fnd_file.put_line(fnd_file.output, formated_header_string(
751 'Please correct all the following situations(if needed) before running your next payroll'
752 ,p_output_file_type
753 ));
754
755 fnd_file.put_line(fnd_file.output, formated_header_string(
756 'I. Errored Employees'
757 ,p_output_file_type
758 ));
759
760 fnd_file.put_line(fnd_file.output, formated_header_string(
761 'ACTION REQUIRED'
762 ,p_output_file_type
763 ));
764
765 fnd_file.put_line(fnd_file.output, formated_header_string(
766 'WARNING!! Employees that have ERRORED during the upgrade process'
767 ,p_output_file_type
768 ));
769
770 fnd_file.put_line(fnd_file.output, formated_header_string(
771 'Please address these errors immediately as the patch will not complete succesfully'
772 ,p_output_file_type
773 ));
774
775 fnd_file.put_line(fnd_file.output, formated_header_string(
776 ' until all the assignments are processed without error.'
777 ,p_output_file_type
778 ));
779
780
781 hr_utility.set_location(gv_package_name || '.report_1', 12);
782 /****************************************************************
783 ** Print the Header Information. If the format is HTML then open
784 ** the body and table before printing the header info, otherwise
785 ** just print the header information.
786 ****************************************************************/
787 if p_output_file_type ='HTML' then
788 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<body>');
789 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<table border=1>');
790 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<tr>');
791 end if;
792 hr_utility.set_location(gv_package_name || '.report_1', 15);
793
794 lv_format1 :=
795 formated_data_string (p_input_string => 'Full Name'
796 ,p_bold => 'Y'
797 ,p_output_file_type => p_output_file_type) ||
798
799 formated_data_string (p_input_string => 'Assignment Id'
800 ,p_bold => 'Y'
801 ,p_output_file_type => p_output_file_type) ||
802 formated_data_string (p_input_string => 'Assignment Number'
803 ,p_bold => 'Y'
804 ,p_output_file_type => p_output_file_type) ||
805
806 formated_data_string (p_input_string =>'Error Description'
807 ,p_bold => 'Y'
808 ,p_output_file_type => p_output_file_type)
809 ;
810
811 hr_utility.set_location(gv_package_name || '.report_1', 20);
812
813
814 fnd_file.put_line(fnd_file.output, lv_format1);
815
816 if p_output_file_type ='HTML' then
817 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '</tr>');
818 end if;
819
820 hr_utility.set_location(gv_package_name || '.report_1', 30);
821 /*****************************************************
822 ** Start of the Data Section of the Report
823 *****************************************************/
824
825 -- HR_UTILITY.TRACE_ON(NULL,'TCL');
826 open c_cursor( p_process_mode
827 ,p_geocode_patch_name
828 );
829
830 loop
831 hr_utility.set_location(gv_package_name || '.report_1', 40);
832
833 fetch c_cursor into ln_full_name
834 ,ln_assignment_id
835 ,ln_assignment_number
836 ,ln_error_description;
837
838 if c_cursor%notfound then
839 hr_utility.set_location(gv_package_name || '.report_1', 50);
840 exit;
841 end if;
842
843
844 /************************************************************
845 ** If Assignment Set is used, pick up only those employee
846 ** assignments which are part of the Assignment Set - STATIC
847 ** or DYNAMIC.
848 ************************************************************/
849 hr_utility.set_location(gv_package_name || '.report_1', 60);
850 hr_utility.trace('Assignment ID = ' || ln_assignment_id);
851
852 formated_static_data( ln_full_name
853 ,ln_assignment_id
854 ,ln_assignment_number
855 ,ln_error_description
856 ,null
857 ,null
858 ,null
859 ,null
860 ,null
861 ,p_output_file_type
862 ,lv_data_row1);
863
864 lv_data_row := lv_data_row1;
865 hr_utility.set_location(gv_package_name || '.report_1', 70);
866
867 -- if p_output_file_type ='HTML' then
868 lv_data_row := '<tr>' || lv_data_row || '</tr>' ;
869 -- end if;
870
871 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, lv_data_row);
872
873
874 /*****************************************************************
875 ** initialize Data varaibles
876 *****************************************************************/
877 lv_data_row := null;
878 end loop;
879 close c_cursor;
880
881 /*****************************************************
882 ** Close of the Data Section of the Report
883 *****************************************************/
884
885 if p_output_file_type ='HTML' then
886 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '</table></body></html>');
887 end if;
888
889 END report_1;
890
891 PROCEDURE report_2
892 (p_process_mode in varchar2
893 ,p_geocode_patch_name in varchar2
894 ,p_output_file_type in varchar2
895 )
896 IS
897
898
899 /************************************************************
900 ** Cursor to get all the employee and assignment data.
901 ************************************************************/
902 cursor c_cursor ( cp_process_mode in varchar
903 ,cp_geocode_patch_name in varchar
904 ) is
905
906 select distinct substr(ppf.full_name,1,40) ,
907 pef.assignment_id ,
908 substr(pef.assignment_number,1,17)
909 from per_people_f ppf,
910 per_assignments_f pef,
911 pay_us_geo_update pgu,
912 pay_patch_status pps
913 where pgu.process_type = 'PERCENTAGE_OVER_100'
914 and pef.assignment_id = pgu.assignment_id
915 and ppf.person_id = pef.person_id
916 and pgu.process_mode = p_process_mode
917 and pgu.id = pps.id
918 and pps.patch_name = p_geocode_patch_name;
919
920 ln_full_name varchar2(40);
921 ln_assignment_id number;
922 ln_assignment_number varchar2(17);
923
924 lv_header_label VARCHAR2(32000);
925
926 lv_data_row VARCHAR2(32000);
927 lv_data_row1 VARCHAR2(32000);
928
929 lv_format1 varchar2(32000);
930
931
932 BEGIN
933
934 hr_utility.set_location(gv_package_name || '.report_2', 10);
935
936 /****************************************************************
937 ** Concatnating the second Header Label which includes the User
938 ** Defined data set so that it is printed at the end of the
939 ** report.
940 ****************************************************************/
941
942 fnd_file.put_line(fnd_file.output, formated_header_string(
943 'II. Incorrect percent in time.'
944 ,p_output_file_type
945 ));
946
947 fnd_file.put_line(fnd_file.output, formated_header_string(
948 'ACTION REQUIRED'
949 ,p_output_file_type
950 ));
951
952 fnd_file.put_line(fnd_file.output, formated_header_string(
953 'Employees whose sum of percent in time is greater then 100% at the local level.'
954 ,p_output_file_type
955 ));
956
957 fnd_file.put_line(fnd_file.output, formated_header_string(
958 'Due to jurisdiction code upgrade, some cities may have exceeded 100% in time.'
959 ,p_output_file_type
960 ));
961
962 fnd_file.put_line(fnd_file.output, formated_header_string(
963 'Please correct this at the W-4 Percentage screen by setting the percent in time for the city'
964 ,p_output_file_type
965 ));
966
967 fnd_file.put_line(fnd_file.output, formated_header_string(
968 '(or total of all the cities) to not exceed 100% time in total. '
969 ,p_output_file_type
970 ));
971
972 hr_utility.set_location(gv_package_name || '.report_2', 15);
973 /****************************************************************
974 ** Print the Header Information. If the format is HTML then open
975 ** the body and table before printing the header info, otherwise
976 ** just print the header information.
977 ****************************************************************/
978 if p_output_file_type ='HTML' then
979 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<body>');
980 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<table border=1>');
981 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<tr>');
982 end if;
983 hr_utility.set_location(gv_package_name || '.report_2', 20);
984
985 lv_format1 :=
986 formated_data_string (p_input_string => 'Full Name'
987 ,p_bold => 'Y'
988 ,p_output_file_type => p_output_file_type) ||
989
990 formated_data_string (p_input_string => 'Assignment Id'
991 ,p_bold => 'Y'
992 ,p_output_file_type => p_output_file_type) ||
993 formated_data_string (p_input_string => 'Assignment Number'
994 ,p_bold => 'Y'
995 ,p_output_file_type => p_output_file_type) ;
996
997 hr_utility.set_location(gv_package_name || '.report_2', 30);
998
999
1000 fnd_file.put_line(fnd_file.output, lv_format1);
1001
1002 if p_output_file_type ='HTML' then
1003 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '</tr>');
1004 end if;
1005
1006 hr_utility.set_location(gv_package_name || '.report_2', 40);
1007 /*****************************************************
1008 ** Start of the Data Section of the Report
1009 *****************************************************/
1010
1011 -- HR_UTILITY.TRACE_ON(NULL,'TCL');
1012 open c_cursor( p_process_mode
1013 ,p_geocode_patch_name
1014 );
1015
1016 loop
1017 hr_utility.set_location(gv_package_name || '.report_2', 50);
1018
1019 fetch c_cursor into ln_full_name
1020 ,ln_assignment_id
1021 ,ln_assignment_number;
1022
1023 if c_cursor%notfound then
1024 hr_utility.set_location(gv_package_name || '.report_2', 60);
1025 exit;
1026 end if;
1027
1028
1029 /************************************************************
1030 ** If Assignment Set is used, pick up only those employee
1031 ** assignments which are part of the Assignment Set - STATIC
1032 ** or DYNAMIC.
1033 ************************************************************/
1034 hr_utility.set_location(gv_package_name || '.report_2', 70);
1035
1036 hr_utility.set_location(gv_package_name || '.report_2', 80);
1037 formated_static_data( ln_full_name
1038 ,ln_assignment_id
1039 ,ln_assignment_number
1040 ,null
1041 ,null
1042 ,null
1043 ,null
1044 ,null
1045 ,null
1046 ,p_output_file_type
1047 ,lv_data_row1);
1048
1049 lv_data_row := lv_data_row1;
1050 hr_utility.set_location(gv_package_name || '.report_2', 90);
1051
1052 -- if p_output_file_type ='HTML' then
1053 lv_data_row := '<tr>' || lv_data_row || '</tr>' ;
1054 -- end if;
1055
1056 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, lv_data_row);
1057
1058
1059 /*****************************************************************
1060 ** initialize Data varaibles
1061 *****************************************************************/
1062 lv_data_row := null;
1063 end loop;
1064 close c_cursor;
1065
1066 /*****************************************************
1067 ** Close of the Data Section of the Report
1068 *****************************************************/
1069
1070 if p_output_file_type ='HTML' then
1071 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '</table></body></html>');
1072 end if;
1073
1074 END report_2;
1075
1076 PROCEDURE report_3
1077 (p_process_mode in varchar2
1078 ,p_geocode_patch_name in varchar2
1079 ,p_output_file_type in varchar2
1080 )
1081 IS
1082
1083
1084 /************************************************************
1085 ** Cursor to get all the employee and assignment data.
1086 ************************************************************/
1087 cursor c_cursor ( cp_process_mode in varchar
1088 ,cp_geocode_patch_name in varchar
1089 ) is
1090
1091 select distinct substr(ppf.full_name,1,40) ,
1092 pef.assignment_id ,
1093 substr(pef.assignment_number,1,17)
1094 from per_people_f ppf,
1095 per_assignments_f pef,
1096 pay_us_geo_update pgu,
1097 pay_patch_status pps
1098 where pgu.process_type = 'MISSING_COUNTY_RECORDS'
1099 and pef.assignment_id = pgu.assignment_id
1100 and ppf.person_id = pef.person_id
1101 and pgu.process_mode = cp_process_mode
1102 and pgu.id = pps.id
1103 and pps.patch_name = cp_geocode_patch_name;
1104
1105 ln_full_name varchar2(40);
1106 ln_assignment_id number;
1107 ln_assignment_number varchar2(17);
1108
1109 lv_header_label VARCHAR2(32000);
1110
1111 lv_data_row VARCHAR2(32000);
1112 lv_data_row1 VARCHAR2(32000);
1113
1114 lv_format1 varchar2(32000);
1115
1116
1117 BEGIN
1118
1119 hr_utility.set_location(gv_package_name || '.report_3', 10);
1120
1121 /****************************************************************
1122 ** Concatnating the second Header Label which includes the User
1123 ** Defined data set so that it is printed at the end of the
1124 ** report.
1125 ****************************************************************/
1126
1127 fnd_file.put_line(fnd_file.output, formated_header_string(
1128 'III. Missing county tax records.'
1129 ,p_output_file_type
1130 ));
1131
1132 fnd_file.put_line(fnd_file.output, formated_header_string(
1133 'ACTION REQUIRED'
1134 ,p_output_file_type
1135 ));
1136
1137 fnd_file.put_line(fnd_file.output, formated_header_string(
1138 'Employees who have missing county tax records.'
1139 ,p_output_file_type
1140 ));
1141
1142 fnd_file.put_line(fnd_file.output, formated_header_string(
1143 'Please correct this by creating a new county tax record from the W-4 form.'
1144 ,p_output_file_type
1145 ));
1146
1147 hr_utility.set_location(gv_package_name || '.report_3', 15);
1148 /****************************************************************
1149 ** Print the Header Information. If the format is HTML then open
1150 ** the body and table before printing the header info, otherwise
1151 ** just print the header information.
1152 ****************************************************************/
1153 if p_output_file_type ='HTML' then
1154 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<body>');
1155 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<table border=1>');
1156 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<tr>');
1157 end if;
1158 hr_utility.set_location(gv_package_name || '.report_3', 20);
1159
1160 lv_format1 :=
1161 formated_data_string (p_input_string => 'Full Name'
1162 ,p_bold => 'Y'
1163 ,p_output_file_type => p_output_file_type) ||
1164
1165 formated_data_string (p_input_string => 'Assignment Id'
1166 ,p_bold => 'Y'
1167 ,p_output_file_type => p_output_file_type) ||
1168 formated_data_string (p_input_string => 'Assignment Number'
1169 ,p_bold => 'Y'
1170 ,p_output_file_type => p_output_file_type)
1171 ;
1172
1173 hr_utility.set_location(gv_package_name || '.report_3', 30);
1174
1175
1176 fnd_file.put_line(fnd_file.output, lv_format1);
1177
1178 if p_output_file_type ='HTML' then
1179 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '</tr>');
1180 end if;
1181
1182 hr_utility.set_location(gv_package_name || '.report_3', 40);
1183 /*****************************************************
1184 ** Start of the Data Section of the Report
1185 *****************************************************/
1186
1187 -- HR_UTILITY.TRACE_ON(NULL,'TCL');
1188 open c_cursor( p_process_mode
1189 ,p_geocode_patch_name
1190 );
1191
1192 loop
1193 hr_utility.set_location(gv_package_name || '.report_3', 50);
1194
1195 fetch c_cursor into ln_full_name
1196 ,ln_assignment_id
1197 ,ln_assignment_number;
1198
1199 if c_cursor%notfound then
1200 hr_utility.set_location(gv_package_name || '.report_3', 60);
1201 exit;
1202 end if;
1203
1204
1205 /************************************************************
1206 ** If Assignment Set is used, pick up only those employee
1207 ** assignments which are part of the Assignment Set - STATIC
1208 ** or DYNAMIC.
1209 ************************************************************/
1210 hr_utility.set_location(gv_package_name || '.report_3', 70);
1211 hr_utility.trace('Assignment ID = ' || ln_assignment_id);
1212
1213
1214 hr_utility.set_location(gv_package_name || '.report_3', 80);
1215 formated_static_data( ln_full_name
1216 ,ln_assignment_id
1217 ,ln_assignment_number
1218 ,null
1219 ,null
1220 ,null
1221 ,null
1222 ,null
1223 ,null
1224 ,p_output_file_type
1225 ,lv_data_row1);
1226
1227 lv_data_row := lv_data_row1;
1228 hr_utility.set_location(gv_package_name || '.report_3', 90);
1229
1230 -- if p_output_file_type ='HTML' then
1231 lv_data_row := '<tr>' || lv_data_row || '</tr>' ;
1232 -- end if;
1233
1234 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, lv_data_row);
1235
1236
1237 /*****************************************************************
1238 ** initialize Data varaibles
1239 *****************************************************************/
1240 lv_data_row := null;
1241 end loop;
1242 close c_cursor;
1243
1244 /*****************************************************
1245 ** Close of the Data Section of the Report
1246 *****************************************************/
1247
1248 if p_output_file_type ='HTML' then
1249 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '</table></body></html>');
1250 end if;
1251
1252 END report_3;
1253
1254 PROCEDURE report_4
1255 (p_process_mode in varchar2
1256 ,p_geocode_patch_name in varchar2
1257 ,p_output_file_type in varchar2
1258 )
1259 IS
1260
1261
1262 /************************************************************
1263 ** Cursor to get all the employee and assignment data.
1264 ************************************************************/
1265 cursor c_cursor ( cp_process_mode in varchar
1266 ,cp_geocode_patch_name in varchar
1267 ) is
1268
1269 select /*+ index(pmod PAY_US_MODIFIED_GEOCODES_PK)*/ -- Bug 3350007
1270 distinct substr(ppf.full_name,1,40) ,
1271 pef.assignment_id ,
1272 substr(pef.assignment_number,1,17),
1273 pgu.new_juri_code ,
1274 substr(pusc.city_name,1,30),
1275 pgu.old_juri_code,
1276 substr(pmod.city_name,1,30),
1277 substr(pgu.table_name ,1,20)
1278 from pay_us_modified_geocodes pmod,
1279 pay_us_city_names pusc ,
1280 pay_patch_status pps,
1281 per_people_f ppf,
1282 per_assignments_f pef,
1283 pay_us_geo_update pgu
1284 where pgu.process_type = 'PU'
1285 and pef.assignment_id = pgu.assignment_id
1286 and ppf.person_id = pef.person_id
1287 and pgu.table_name is not null
1288 and substr(new_juri_code,1,2) = pmod.state_code
1289 and substr(new_juri_code,4,3) = pmod.county_code
1290 and substr(new_juri_code,8,4) = pmod.new_city_code
1291 and substr(old_juri_code,8,4) = pmod.old_city_code
1292 and pmod.process_type = 'PU'
1293 and pusc.city_code = substr(new_juri_code,8,4)
1294 and pusc.county_code = substr(new_juri_code,4,3)
1295 and pusc.state_code = substr(new_juri_code,1,2)
1296 and pusc.primary_flag = 'Y'
1297 and pgu.process_mode = cp_process_mode
1298 and pgu.id = pps.id
1299 and pps.patch_name = cp_geocode_patch_name;
1300
1301 ln_full_name varchar2(40);
1302 ln_assignment_id number;
1303 ln_assignment_number varchar2(17);
1304 ln_new_juri_code varchar2(11);
1305 ln_new_pri_city varchar2(30);
1306 ln_old_juri_code varchar2(11);
1307 ln_old_pri_city varchar2(30);
1308 ln_table_name varchar2(20);
1309
1310 lv_header_label VARCHAR2(32000);
1311
1312 lv_data_row VARCHAR2(32000);
1313 lv_data_row1 VARCHAR2(32000);
1314
1315 lv_format1 varchar2(32000);
1316
1317
1318 BEGIN
1319
1320 hr_utility.set_location(gv_package_name || '.report_4', 10);
1321
1322 /****************************************************************
1323 ** Concatnating the second Header Label which includes the User
1324 ** Defined data set so that it is printed at the end of the
1325 ** report.
1326 ****************************************************************/
1327
1328 fnd_file.put_line(fnd_file.output, formated_header_string(
1329 'IV. Primary city becoming Secondary with jurisdiction code change'
1330 ,p_output_file_type
1331 ));
1332
1333 fnd_file.put_line(fnd_file.output, formated_header_string(
1334 'NO ACTION IS REQUIRED. This is for information ONLY.'
1335 ,p_output_file_type
1336 ));
1337
1338 fnd_file.put_line(fnd_file.output, formated_header_string(
1339 'Employees who have records updated in the following tables because'
1340 ,p_output_file_type
1341 ));
1342
1343 fnd_file.put_line(fnd_file.output, formated_header_string(
1344 'a primary city has changed to a secondary city with a jurisdiction'
1345 ,p_output_file_type
1346 ));
1347
1348 fnd_file.put_line(fnd_file.output, formated_header_string(
1349 'code change.'
1350 ,p_output_file_type
1351 ));
1352
1353 fnd_file.put_line(fnd_file.output, formated_header_string(
1354 'The city displayed here is the NEW PRIMARY CITY for the assignment.'
1355 ,p_output_file_type
1356 ));
1357
1358 hr_utility.set_location(gv_package_name || '.report_4', 15);
1359 /****************************************************************
1360 ** Print the Header Information. If the format is HTML then open
1361 ** the body and table before printing the header info, otherwise
1362 ** just print the header information.
1363 ****************************************************************/
1364 if p_output_file_type ='HTML' then
1365 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<body>');
1366 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<table border=1>');
1367 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<tr>');
1368 end if;
1369 hr_utility.set_location(gv_package_name || '.report_4', 20);
1370
1371 lv_format1 :=
1372 formated_data_string (p_input_string => 'Full Name'
1373 ,p_bold => 'Y'
1374 ,p_output_file_type => p_output_file_type) ||
1375
1376 formated_data_string (p_input_string => 'Assignment Id'
1377 ,p_bold => 'Y'
1378 ,p_output_file_type => p_output_file_type) ||
1379
1380 formated_data_string (p_input_string => 'Assignment Number'
1381 ,p_bold => 'Y'
1382 ,p_output_file_type => p_output_file_type) ||
1383
1384 formated_data_string (p_input_string => 'New JD'
1385 ,p_bold => 'Y'
1386 ,p_output_file_type => p_output_file_type) ||
1387
1388 formated_data_string (p_input_string => 'New Primary City'
1389 ,p_bold => 'Y'
1390 ,p_output_file_type => p_output_file_type) ||
1391
1392 formated_data_string (p_input_string => 'Old JD'
1393 ,p_bold => 'Y'
1394 ,p_output_file_type => p_output_file_type) ||
1395
1396 formated_data_string (p_input_string => 'Old Primary City'
1397 ,p_bold => 'Y'
1398 ,p_output_file_type => p_output_file_type) ||
1399
1400 formated_data_string (p_input_string => 'Table Updated'
1401 ,p_bold => 'Y'
1402 ,p_output_file_type => p_output_file_type)
1403 ;
1404
1405
1406 hr_utility.set_location(gv_package_name || '.report_4', 30);
1407
1408
1409 fnd_file.put_line(fnd_file.output, lv_format1);
1410
1411 if p_output_file_type ='HTML' then
1412 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '</tr>');
1413 end if;
1414
1415 hr_utility.set_location(gv_package_name || '.report_4', 40);
1416 /*****************************************************
1417 ** Start of the Data Section of the Report
1418 *****************************************************/
1419
1420 -- HR_UTILITY.TRACE_ON(NULL,'TCL');
1421 open c_cursor( p_process_mode
1422 ,p_geocode_patch_name
1423 );
1424
1425 loop
1426 hr_utility.set_location(gv_package_name || '.report_4', 50);
1427
1428 fetch c_cursor into ln_full_name
1429 ,ln_assignment_id
1430 ,ln_assignment_number
1431 ,ln_new_juri_code
1432 ,ln_new_pri_city
1433 ,ln_old_juri_code
1434 ,ln_old_pri_city
1435 ,ln_table_name;
1436
1437 if c_cursor%notfound then
1438 hr_utility.set_location(gv_package_name || '.report_4', 60);
1439 exit;
1440 end if;
1441
1442
1443 /************************************************************
1444 ** If Assignment Set is used, pick up only those employee
1445 ** assignments which are part of the Assignment Set - STATIC
1446 ** or DYNAMIC.
1447 ************************************************************/
1448 hr_utility.set_location(gv_package_name || '.report_4', 70);
1449 hr_utility.trace('Assignment ID = ' || ln_assignment_id);
1450
1451
1452 hr_utility.set_location(gv_package_name || '.report_4', 80);
1453 formated_static_data( ln_full_name
1454 ,ln_assignment_id
1455 ,ln_assignment_number
1456 ,null
1457 ,ln_new_juri_code
1458 ,ln_new_pri_city
1459 ,ln_old_juri_code
1460 ,ln_old_pri_city
1461 ,ln_table_name
1462 ,p_output_file_type
1463 ,lv_data_row1);
1464
1465 lv_data_row := lv_data_row1;
1466 hr_utility.set_location(gv_package_name || '.report_4', 90);
1467
1468 -- if p_output_file_type ='HTML' then
1469 lv_data_row := '<tr>' || lv_data_row || '</tr>' ;
1470 -- end if;
1471
1472 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, lv_data_row);
1473
1474
1475 /*****************************************************************
1476 ** initialize Data varaibles
1477 *****************************************************************/
1478 lv_data_row := null;
1479 end loop;
1480 close c_cursor;
1481
1482 /*****************************************************
1483 ** Close of the Data Section of the Report
1484 *****************************************************/
1485
1486 if p_output_file_type ='HTML' then
1487 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '</table></body></html>');
1488 end if;
1489
1490 END report_4;
1491
1492
1493 PROCEDURE report_5
1494 (p_process_mode in varchar2
1495 ,p_geocode_patch_name in varchar2
1496 ,p_output_file_type in varchar2
1497 )
1498 IS
1499
1500
1501 /************************************************************
1502 ** Cursor to get all the employee and assignment data.
1503 ************************************************************/
1504 cursor c_cursor ( cp_process_mode in varchar
1505 ,cp_geocode_patch_name in varchar
1506 ) is
1507
1508 select distinct substr(ppf.full_name,1,40) ,
1509 pef.assignment_id "Assignment Id" ,
1510 substr(pef.assignment_number,1,17),
1511 substr(pusc.city_name,1,30),
1512 old_juri_code "Old JD",
1513 new_juri_code "New JD",
1514 substr(table_name,1,20)
1515 from per_people_f ppf,
1516 per_assignments_f pef,
1517 pay_us_geo_update pgu,
1518 pay_us_city_names pusc,
1519 pay_patch_status pps
1520 where pgu.process_type = 'UP'
1521 and pgu.table_name is not null
1522 and pef.assignment_id = pgu.assignment_id
1523 and ppf.person_id = pef.person_id
1524 and pusc.city_code = substr(new_juri_code,8,4)
1525 and pusc.county_code = substr(new_juri_code,4,3)
1526 and pusc.state_code = substr(new_juri_code,1,2)
1527 and pusc.primary_flag = 'Y'
1528 and pgu.process_mode = p_process_mode
1529 and pgu.id = pps.id
1530 and pps.patch_name = p_geocode_patch_name;
1531
1532 ln_full_name varchar2(40);
1533 ln_assignment_id number;
1534 ln_assignment_number varchar2(17);
1535 ln_new_juri_code varchar2(11);
1536 ln_new_pri_city varchar2(30);
1537 ln_old_juri_code varchar2(11);
1538 ln_old_pri_city varchar2(30);
1539 ln_table_name varchar2(20);
1540
1541 lv_header_label VARCHAR2(32000);
1542
1543 lv_data_row VARCHAR2(32000);
1544 lv_data_row1 VARCHAR2(32000);
1545
1546 lv_format1 varchar2(32000);
1547
1548
1549 BEGIN
1550
1551 hr_utility.set_location(gv_package_name || '.report_5', 10);
1552
1553 /****************************************************************
1554 ** Concatnating the second Header Label which includes the User
1555 ** Defined data set so that it is printed at the end of the
1556 ** report.
1557 ****************************************************************/
1558
1559 fnd_file.put_line(fnd_file.output, formated_header_string(
1560 'V. Primary city jurisdiction code change.'
1561 ,p_output_file_type
1562 ));
1563
1564 fnd_file.put_line(fnd_file.output, formated_header_string(
1565 'NO ACTION IS REQUIRED. This is for information ONLY.'
1566 ,p_output_file_type
1567 ));
1568
1569 fnd_file.put_line(fnd_file.output, formated_header_string(
1570 'Employees whose records have been updated in the following tables'
1571 ,p_output_file_type
1572 ));
1573
1574 fnd_file.put_line(fnd_file.output, formated_header_string(
1575 'because a primary citys jurisdiction code has changed.'
1576 ,p_output_file_type
1577 ));
1578
1579 hr_utility.set_location(gv_package_name || '.report_5', 15);
1580 /****************************************************************
1581 ** Print the Header Information. If the format is HTML then open
1582 ** the body and table before printing the header info, otherwise
1583 ** just print the header information.
1584 ****************************************************************/
1585 if p_output_file_type ='HTML' then
1586 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<body>');
1587 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<table border=1>');
1588 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<tr>');
1589 end if;
1590 hr_utility.set_location(gv_package_name || '.report_5', 20);
1591
1592 lv_format1 :=
1593 formated_data_string (p_input_string => 'Full Name'
1594 ,p_bold => 'Y'
1595 ,p_output_file_type => p_output_file_type) ||
1596
1597 formated_data_string (p_input_string => 'Assignment Id'
1598 ,p_bold => 'Y'
1599 ,p_output_file_type => p_output_file_type) ||
1600
1601 formated_data_string (p_input_string => 'Assignment Number'
1602 ,p_bold => 'Y'
1603 ,p_output_file_type => p_output_file_type) ||
1604
1605 formated_data_string (p_input_string => 'New JD'
1606 ,p_bold => 'Y'
1607 ,p_output_file_type => p_output_file_type) ||
1608
1609 formated_data_string (p_input_string => 'New Primary City'
1610 ,p_bold => 'Y'
1611 ,p_output_file_type => p_output_file_type) ||
1612
1613 formated_data_string (p_input_string => 'Old JD'
1614 ,p_bold => 'Y'
1615 ,p_output_file_type => p_output_file_type) ||
1616
1617 formated_data_string (p_input_string => 'Table Updated'
1618 ,p_bold => 'Y'
1619 ,p_output_file_type => p_output_file_type)
1620 ;
1621
1622
1623 hr_utility.set_location(gv_package_name || '.report_5', 30);
1624
1625
1626 fnd_file.put_line(fnd_file.output, lv_format1);
1627
1628 if p_output_file_type ='HTML' then
1629 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '</tr>');
1630 end if;
1631
1632 hr_utility.set_location(gv_package_name || '.report_5', 40);
1633 /*****************************************************
1634 ** Start of the Data Section of the Report
1635 *****************************************************/
1636
1637 -- HR_UTILITY.TRACE_ON(NULL,'TCL');
1638 open c_cursor( p_process_mode
1639 ,p_geocode_patch_name
1640 );
1641
1642 loop
1643 hr_utility.set_location(gv_package_name || '.report_5', 50);
1644
1645 fetch c_cursor into ln_full_name
1646 ,ln_assignment_id
1647 ,ln_assignment_number
1648 ,ln_new_pri_city
1649 ,ln_old_juri_code
1650 ,ln_new_juri_code
1651 ,ln_table_name;
1652
1653 if c_cursor%notfound then
1654 hr_utility.set_location(gv_package_name || '.report_5', 60);
1655 exit;
1656 end if;
1657
1658
1659 /************************************************************
1660 ** If Assignment Set is used, pick up only those employee
1661 ** assignments which are part of the Assignment Set - STATIC
1662 ** or DYNAMIC.
1663 ************************************************************/
1664 hr_utility.set_location(gv_package_name || '.report_5', 70);
1665 hr_utility.trace('Assignment ID = ' || ln_assignment_id);
1666
1667
1668 hr_utility.set_location(gv_package_name || '.report_5', 80);
1669 formated_static_data( ln_full_name
1670 ,ln_assignment_id
1671 ,ln_assignment_number
1672 ,null
1673 ,ln_new_juri_code
1674 ,ln_new_pri_city
1675 ,ln_old_juri_code
1676 ,null
1677 ,ln_table_name
1678 ,p_output_file_type
1679 ,lv_data_row1);
1680
1681 lv_data_row := lv_data_row1;
1682 hr_utility.set_location(gv_package_name || '.report_5', 90);
1683
1684 -- if p_output_file_type ='HTML' then
1685 lv_data_row := '<tr>' || lv_data_row || '</tr>' ;
1686 -- end if;
1687
1688 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, lv_data_row);
1689
1690
1691 /*****************************************************************
1692 ** initialize Data varaibles
1693 *****************************************************************/
1694 lv_data_row := null;
1695 end loop;
1696 close c_cursor;
1697
1698 /*****************************************************
1699 ** Close of the Data Section of the Report
1700 *****************************************************/
1701
1702 if p_output_file_type ='HTML' then
1703 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '</table></body></html>');
1704 end if;
1705
1706 END report_5;
1707
1708
1709 PROCEDURE report_6
1710 (p_process_mode in varchar2
1711 ,p_geocode_patch_name in varchar2
1712 ,p_output_file_type in varchar2
1713 )
1714 IS
1715
1716
1717 /************************************************************
1718 ** Cursor to get all the employee and assignment data.
1719 ************************************************************/
1720 cursor c_cursor ( cp_process_mode in varchar
1721 ,cp_geocode_patch_name in varchar
1722 ) is
1723
1724 select distinct substr(ppf.full_name,1,40) ,
1725 pef.assignment_id "Assignment Id" ,
1726 substr(pef.assignment_number,1,17),
1727 substr(pusc.city_name,1,30),
1728 substr(puscn.county_name,1,20),
1729 substr(pust.state_abbrev,1,2),
1730 old_juri_code ,
1731 new_juri_code
1732 from per_people_f ppf,
1733 per_assignments_f pef,
1734 pay_us_geo_update pgu,
1735 pay_us_city_names pusc,
1736 pay_patch_status pps ,
1737 pay_us_states pust,
1738 pay_us_counties puscn
1739 where pgu.process_type = 'US'
1740 and pgu.status = 'A'
1741 and pgu.table_name is null
1742 and pef.assignment_id = pgu.assignment_id
1743 and ppf.person_id = pef.person_id
1744 and pusc.city_code = substr(new_juri_code,8,4)
1745 and pusc.county_code = substr(new_juri_code,4,3)
1746 and pusc.state_code = substr(new_juri_code,1,2)
1747 and puscn.county_code = pusc.county_code
1748 and puscn.state_code = pusc.state_code
1749 and pust.state_code = pusc.state_code
1750 and pusc.primary_flag = 'Y'
1751 and pgu.process_mode = cp_process_mode
1752 and pgu.id = pps.id
1753 and pps.patch_name = cp_geocode_patch_name;
1754
1755 ln_full_name varchar2(40);
1756 ln_assignment_id number;
1757 ln_assignment_number varchar2(17);
1758 ln_city_name varchar2(30);
1759 ln_county_name varchar2(20);
1760 ln_state_abbrev varchar2(20);
1761 ln_old_juri_code varchar2(11);
1762 ln_new_juri_code varchar2(11);
1763
1764 lv_header_label VARCHAR2(32000);
1765
1766 lv_data_row VARCHAR2(32000);
1767 lv_data_row1 VARCHAR2(32000);
1768
1769 lv_format1 varchar2(32000);
1770
1771
1772 BEGIN
1773
1774 hr_utility.set_location(gv_package_name || '.report_6', 10);
1775
1776 /****************************************************************
1777 ** Concatnating the second Header Label which includes the User
1778 ** Defined data set so that it is printed at the end of the
1779 ** report.
1780 ****************************************************************/
1781
1782 fnd_file.put_line(fnd_file.output, formated_header_string(
1783 'VI. Secondary jurisdiction code change.'
1784 ,p_output_file_type
1785 ));
1786
1787 fnd_file.put_line(fnd_file.output, formated_header_string(
1788 'ACTION REQUIRED'
1789 ,p_output_file_type
1790 ));
1791
1792 fnd_file.put_line(fnd_file.output, formated_header_string(
1793 'WARNING! Employees whose secondary city jurisdiction code has changed. Thus this means'
1794 ,p_output_file_type
1795 ));
1796
1797 fnd_file.put_line(fnd_file.output, formated_header_string(
1798 'the employees will have different or a new Primary City.'
1799 ,p_output_file_type
1800 ));
1801
1802 fnd_file.put_line(fnd_file.output, formated_header_string(
1803 'New city tax records and Vertex Element Entries have been created for'
1804 ,p_output_file_type
1805 ));
1806
1807 fnd_file.put_line(fnd_file.output, formated_header_string(
1808 'these assignments with the new jurisdiction code.'
1809 ,p_output_file_type
1810 ));
1811
1812 fnd_file.put_line(fnd_file.output, formated_header_string(
1813 'Please check the records of these employees and determine which CITY is to be the Primary City.'
1814 ,p_output_file_type
1815 ));
1816
1817 fnd_file.put_line(fnd_file.output, formated_header_string(
1818 'Then, ensure that the percentages reflect the new primary city.'
1819 ,p_output_file_type
1820 ));
1821
1822 fnd_file.put_line(fnd_file.output, formated_header_string(
1823 'also ensure that the subject to balances are correct. Some manual balance adjustments'
1824 ,p_output_file_type
1825 ));
1826
1827 fnd_file.put_line(fnd_file.output, formated_header_string(
1828 'may be required to reflect the new taxing jurisdictions of highlighted cities.'
1829 ,p_output_file_type
1830 ));
1831
1832 fnd_file.put_line(fnd_file.output, formated_header_string(
1833 'The resident city and work city listed reflects the location as of the day this patch is run.'
1834 ,p_output_file_type
1835 ));
1836
1837 fnd_file.put_line(fnd_file.output, formated_header_string(
1838 'NOTE : This only applies to those cities that have local level taxes. All other'
1839 ,p_output_file_type
1840 ));
1841
1842 fnd_file.put_line(fnd_file.output, formated_header_string(
1843 'cities may be ignored and are listed for information only.'
1844 ,p_output_file_type
1845 ));
1846
1847 hr_utility.set_location(gv_package_name || '.report_6', 15);
1848 /****************************************************************
1849 ** Print the Header Information. If the format is HTML then open
1850 ** the body and table before printing the header info, otherwise
1851 ** just print the header information.
1852 ****************************************************************/
1853 if p_output_file_type ='HTML' then
1854 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<body>');
1855 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<table border=1>');
1856 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<tr>');
1857 end if;
1858 hr_utility.set_location(gv_package_name || '.report_6', 20);
1859
1860 lv_format1 :=
1861 formated_data_string (p_input_string => 'Full Name'
1862 ,p_bold => 'Y'
1863 ,p_output_file_type => p_output_file_type) ||
1864
1865 formated_data_string (p_input_string => 'Assignment Id'
1866 ,p_bold => 'Y'
1867 ,p_output_file_type => p_output_file_type) ||
1868
1869 formated_data_string (p_input_string => 'Assignment Number'
1870 ,p_bold => 'Y'
1871 ,p_output_file_type => p_output_file_type) ||
1872
1873 formated_data_string (p_input_string => 'Primary City'
1874 ,p_bold => 'Y'
1875 ,p_output_file_type => p_output_file_type) ||
1876
1877 formated_data_string (p_input_string => 'County Name'
1878 ,p_bold => 'Y'
1879 ,p_output_file_type => p_output_file_type) ||
1880
1881 formated_data_string (p_input_string => 'State'
1882 ,p_bold => 'Y'
1883 ,p_output_file_type => p_output_file_type) ||
1884
1885 formated_data_string (p_input_string => 'Old JD'
1886 ,p_bold => 'Y'
1887 ,p_output_file_type => p_output_file_type) ||
1888
1889 formated_data_string (p_input_string => 'New JD'
1890 ,p_bold => 'Y'
1891 ,p_output_file_type => p_output_file_type)
1892 ;
1893
1894
1895 hr_utility.set_location(gv_package_name || '.report_6', 30);
1896
1897
1898 fnd_file.put_line(fnd_file.output, lv_format1);
1899
1900 if p_output_file_type ='HTML' then
1901 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '</tr>');
1902 end if;
1903
1904 hr_utility.set_location(gv_package_name || '.report_6', 40);
1905 /*****************************************************
1906 ** Start of the Data Section of the Report
1907 *****************************************************/
1908
1909 -- HR_UTILITY.TRACE_ON(NULL,'TCL');
1910 open c_cursor( p_process_mode
1911 ,p_geocode_patch_name
1912 );
1913
1914 loop
1915 hr_utility.set_location(gv_package_name || '.report_6', 50);
1916
1917 fetch c_cursor into ln_full_name
1918 ,ln_assignment_id
1919 ,ln_assignment_number
1920 ,ln_city_name
1921 ,ln_county_name
1922 ,ln_state_abbrev
1923 ,ln_old_juri_code
1924 ,ln_new_juri_code;
1925
1926 if c_cursor%notfound then
1927 hr_utility.set_location(gv_package_name || '.report_6', 60);
1928 exit;
1929 end if;
1930
1931
1932 /************************************************************
1933 ** If Assignment Set is used, pick up only those employee
1934 ** assignments which are part of the Assignment Set - STATIC
1935 ** or DYNAMIC.
1936 ************************************************************/
1937 hr_utility.set_location(gv_package_name || '.report_6', 70);
1938 hr_utility.trace('Assignment ID = ' || ln_assignment_id);
1939
1940
1941 hr_utility.set_location(gv_package_name || '.report_6', 80);
1942 formated_static_data2(ln_full_name
1943 ,ln_assignment_id
1944 ,ln_assignment_number
1945 ,ln_city_name
1946 ,ln_county_name
1947 ,ln_state_abbrev
1948 ,ln_old_juri_code
1949 ,ln_new_juri_code
1950 ,p_output_file_type
1951 ,lv_data_row1);
1952
1953 lv_data_row := lv_data_row1;
1954 hr_utility.set_location(gv_package_name || '.report_6', 90);
1955
1956 -- if p_output_file_type ='HTML' then
1957 lv_data_row := '<tr>' || lv_data_row || '</tr>' ;
1958 -- end if;
1959
1960 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, lv_data_row);
1961
1962
1963 /*****************************************************************
1964 ** initialize Data varaibles
1965 *****************************************************************/
1966 lv_data_row := null;
1967 end loop;
1968 close c_cursor;
1969
1970 /*****************************************************
1971 ** Close of the Data Section of the Report
1972 *****************************************************/
1973
1974 if p_output_file_type ='HTML' then
1975 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '</table></body></html>');
1976 end if;
1977
1978 END report_6;
1979
1980
1981 PROCEDURE report_7
1982 (p_process_mode in varchar2
1983 ,p_geocode_patch_name in varchar2
1984 ,p_output_file_type in varchar2
1985 )
1986 IS
1987
1988
1989 /************************************************************
1990 ** Cursor to get all the employee and assignment data.
1991 ************************************************************/
1992 cursor c_cursor ( cp_process_mode in varchar
1993 ,cp_geocode_patch_name in varchar
1994 ) is
1995
1996 SELECT /*+ ORDERED
1997 INDEX (PAY_US_MODIFIED_GEOCODES PAY_US_MODIFIED_GEOCODES_N1)
1998 INDEX (PAY_US_CITY_NAMES PAY_US_CITY_NAMES_FK1)
1999 INDEX (PAY_US_EMP_CITY_TAX_RULES_F PAY_US_EMP_CITY_TAX_RULES_N3) */
2000 SUBSTR(ppf.full_name,1,40),
2001 SUBSTR(pmod.city_name,1,30),
2002 SUBSTR(pucn.city_name,1,30)
2003 FROM pay_us_modified_geocodes pmod,
2004 pay_us_city_names pucn,
2005 pay_us_emp_city_tax_rules_f ectr,
2006 per_assignments_f paf,
2007 per_people_f ppf
2008 WHERE pmod.process_type = 'P'
2009 AND pmod.state_code = pucn.state_code
2010 AND pmod.county_code = pucn.county_code
2011 AND pmod.new_city_code = pucn.city_code
2012 AND pucn.primary_flag = 'Y'
2013 AND pmod.state_code = ectr.state_code
2014 AND pmod.county_code = ectr.county_code
2015 AND pmod.old_city_code = ectr.city_code
2016 AND ectr.assignment_id = paf.assignment_id
2017 AND paf.person_id = ppf.person_id
2018 AND pmod.patch_name = cp_geocode_patch_name
2019 AND pay_us_geo_upd_pkg.get_city(paf.person_id, paf.location_id, ectr.state_code,
2020 ectr.county_code,ectr.city_code,pmod.city_name,pmod.patch_name,pmod.process_type) = pmod.city_name
2021 UNION
2022 SELECT /*+ ORDERED
2023 INDEX (PAY_US_MODIFIED_GEOCODES PAY_US_MODIFIED_GEOCODES_N1)
2024 INDEX (PAY_US_EMP_CITY_TAX_RULES_F PAY_US_EMP_CITY_TAX_RULES_N3) */
2025 SUBSTR(ppf.full_name,1,40),
2026 SUBSTR(pmod2.city_name,1,30),
2027 SUBSTR(pmod.city_name,1,30)
2028 FROM pay_us_modified_geocodes pmod,
2029 pay_us_emp_city_tax_rules_f ectr,
2030 per_assignments_f paf,
2031 per_people_f ppf,
2032 pay_us_modified_geocodes pmod2
2033 WHERE pmod.process_type = 'S'
2034 and pmod2.process_type in ('UP','PU','P')
2035 and pmod2.state_code = ectr.state_code
2036 and pmod2.county_code = ectr.county_code
2037 and pmod2.old_city_code = ectr.city_code
2038 AND pmod.state_code = ectr.state_code
2039 AND pmod.county_code = ectr.county_code
2040 AND pmod.new_city_code = ectr.city_code
2041 AND ectr.assignment_id = paf.assignment_id
2042 AND paf.person_id = ppf.person_id
2043 AND pmod.patch_name = cp_geocode_patch_name
2044 AND pmod2.patch_name = cp_geocode_patch_name
2045 AND pay_us_geo_upd_pkg.get_city(paf.person_id, paf.location_id, ectr.state_code,
2046 ectr.county_code,ectr.city_code,pmod.city_name,pmod.patch_name,pmod.process_type) = pmod.city_name;
2047
2048
2049 ln_full_name varchar2(40);
2050 ln_old_city_name varchar2(30);
2051 ln_new_city_name varchar2(30);
2052
2053 lv_header_label VARCHAR2(32000);
2054
2055 lv_data_row VARCHAR2(32000);
2056 lv_data_row1 VARCHAR2(32000);
2057
2058 lv_format1 varchar2(32000);
2059
2060
2061 BEGIN
2062
2063 hr_utility.set_location(gv_package_name || '.report_7', 10);
2064
2065 /****************************************************************
2066 ** Concatnating the second Header Label which includes the User
2067 ** Defined data set so that it is printed at the end of the
2068 ** report.
2069 ****************************************************************/
2070
2071 fnd_file.put_line(fnd_file.output, formated_header_string(
2072 'VII. Employees who have new Primary City.'
2073 ,p_output_file_type
2074 ));
2075
2076 fnd_file.put_line(fnd_file.output, formated_header_string(
2077 'NO ACTION IS REQUIRED. This is for information ONLY.'
2078 ,p_output_file_type
2079 ));
2080
2081 fnd_file.put_line(fnd_file.output, formated_header_string(
2082 'The jurisdction code has not changed thus the tax rates will stay the same.'
2083 ,p_output_file_type
2084 ));
2085
2086 fnd_file.put_line(fnd_file.output, formated_header_string(
2087 'the employees will have different or a new Primary City.'
2088 ,p_output_file_type
2089 ));
2090
2091
2092 hr_utility.set_location(gv_package_name || '.report_7', 15);
2093 /****************************************************************
2094 ** Print the Header Information. If the format is HTML then open
2095 ** the body and table before printing the header info, otherwise
2096 ** just print the header information.
2097 ****************************************************************/
2098 if p_output_file_type ='HTML' then
2099 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<body>');
2100 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<table border=1>');
2101 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<tr>');
2102 end if;
2103 hr_utility.set_location(gv_package_name || '.report_7', 20);
2104
2105 lv_format1 :=
2106 formated_data_string (p_input_string => 'Full Name'
2107 ,p_bold => 'Y'
2108 ,p_output_file_type => p_output_file_type) ||
2109
2110 formated_data_string (p_input_string => 'Old Primary City'
2111 ,p_bold => 'Y'
2112 ,p_output_file_type => p_output_file_type) ||
2113
2114 formated_data_string (p_input_string => 'New Primary City'
2115 ,p_bold => 'Y'
2116 ,p_output_file_type => p_output_file_type)
2117 ;
2118
2119 hr_utility.set_location(gv_package_name || '.report_7', 30);
2120
2121
2122 fnd_file.put_line(fnd_file.output, lv_format1);
2123
2124 if p_output_file_type ='HTML' then
2125 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '</tr>');
2126 end if;
2127
2128 hr_utility.set_location(gv_package_name || '.report_7', 40);
2129 /*****************************************************
2130 ** Start of the Data Section of the Report
2131 *****************************************************/
2132
2133 -- HR_UTILITY.TRACE_ON(NULL,'TCL');
2134 open c_cursor( p_process_mode
2135 ,p_geocode_patch_name
2136 );
2137
2138 loop
2139 hr_utility.set_location(gv_package_name || '.report_7', 50);
2140
2141 fetch c_cursor into ln_full_name
2142 ,ln_old_city_name
2143 ,ln_new_city_name;
2144
2145 if c_cursor%notfound then
2146 hr_utility.set_location(gv_package_name || '.report_7', 60);
2147 exit;
2148 end if;
2149
2150
2151 /************************************************************
2152 ** If Assignment Set is used, pick up only those employee
2153 ** assignments which are part of the Assignment Set - STATIC
2154 ** or DYNAMIC.
2155 ************************************************************/
2156 hr_utility.set_location(gv_package_name || '.report_7', 70);
2157
2158 hr_utility.set_location(gv_package_name || '.report_7', 80);
2159 formated_static_data(ln_full_name
2160 ,null
2161 ,null
2162 ,null
2163 ,ln_old_city_name -- Intentional see report format
2164 ,null
2165 ,ln_new_city_name -- Intentional see report format
2166 ,null
2167 ,null
2168 ,p_output_file_type
2169 ,lv_data_row1);
2170
2171 lv_data_row := lv_data_row1;
2172 hr_utility.set_location(gv_package_name || '.report_7', 90);
2173
2174 -- if p_output_file_type ='HTML' then
2175 lv_data_row := '<tr>' || lv_data_row || '</tr>' ;
2176 -- end if;
2177
2178 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, lv_data_row);
2179
2180
2181 /*****************************************************************
2182 ** initialize Data varaibles
2183 *****************************************************************/
2184 lv_data_row := null;
2185 end loop;
2186 close c_cursor;
2187
2188 /*****************************************************
2189 ** Close of the Data Section of the Report
2190 *****************************************************/
2191
2192 if p_output_file_type ='HTML' then
2193 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '</table></body></html>');
2194 end if;
2195
2196 END report_7;
2197
2198 PROCEDURE report_8
2199 (p_process_mode in varchar2
2200 ,p_geocode_patch_name in varchar2
2201 ,p_output_file_type in varchar2
2202 )
2203 IS
2204
2205
2206 /************************************************************
2207 ** Cursor to get all the employee and assignment data.
2208 ************************************************************/
2209 cursor c_cursor ( cp_process_mode in varchar
2210 ,cp_geocode_patch_name in varchar
2211 ) is
2212
2213 select distinct state_code||'-'||county_code||'-'||old_city_code ,
2214 state_code||'-'||county_code||'-'||new_city_code ,
2215 substr(city_name,1,30)
2216 from pay_us_modified_geocodes
2217 where old_city_code like 'U%';
2218
2219
2220 ln_old_juri_code varchar2(11);
2221 ln_new_juri_code varchar2(11);
2222 ln_city_name varchar2(30);
2223
2224 lv_header_label VARCHAR2(32000);
2225
2226 lv_data_row VARCHAR2(32000);
2227 lv_data_row1 VARCHAR2(32000);
2228
2229 lv_format1 varchar2(32000);
2230
2231
2232 BEGIN
2233
2234 hr_utility.set_location(gv_package_name || '.report_8', 10);
2235
2236 /****************************************************************
2237 ** Concatnating the second Header Label which includes the User
2238 ** Defined data set so that it is printed at the end of the
2239 ** report.
2240 ****************************************************************/
2241
2242 fnd_file.put_line(fnd_file.output, formated_header_string(
2243 'VIII. User Defined cities that are now supported by Vertex'
2244 ,p_output_file_type
2245 ));
2246
2247 fnd_file.put_line(fnd_file.output, formated_header_string(
2248 'NO ACTION IS REQUIRED. This is for information ONLY.'
2249 ,p_output_file_type
2250 ));
2251
2252 fnd_file.put_line(fnd_file.output, formated_header_string(
2253 'The following lists user defined cities that are now supported by Vertex.'
2254 ,p_output_file_type
2255 ));
2256
2257
2258 hr_utility.set_location(gv_package_name || '.report_8', 15);
2259 /****************************************************************
2260 ** Print the Header Information. If the format is HTML then open
2261 ** the body and table before printing the header info, otherwise
2262 ** just print the header information.
2263 ****************************************************************/
2264 if p_output_file_type ='HTML' then
2265 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<body>');
2266 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<table border=1>');
2267 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<tr>');
2268 end if;
2269 hr_utility.set_location(gv_package_name || '.report_8', 20);
2270
2271 lv_format1 :=
2272 formated_data_string (p_input_string => 'Old User Defined JD'
2273 ,p_bold => 'Y'
2274 ,p_output_file_type => p_output_file_type) ||
2275
2276 formated_data_string (p_input_string => 'Supported New JD'
2277 ,p_bold => 'Y'
2278 ,p_output_file_type => p_output_file_type) ||
2279
2280 formated_data_string (p_input_string => 'City Name'
2281 ,p_bold => 'Y'
2282 ,p_output_file_type => p_output_file_type)
2283 ;
2284
2285 hr_utility.set_location(gv_package_name || '.report_8', 30);
2286
2287
2288 fnd_file.put_line(fnd_file.output, lv_format1);
2289
2290 if p_output_file_type ='HTML' then
2291 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '</tr>');
2292 end if;
2293
2294 hr_utility.set_location(gv_package_name || '.report_8', 40);
2295 /*****************************************************
2296 ** Start of the Data Section of the Report
2297 *****************************************************/
2298
2299 -- HR_UTILITY.TRACE_ON(NULL,'TCL');
2300 open c_cursor( p_process_mode
2301 ,p_geocode_patch_name
2302 );
2303
2304 loop
2305 hr_utility.set_location(gv_package_name || '.report_8', 50);
2306
2307 fetch c_cursor into ln_old_juri_code
2308 ,ln_new_juri_code
2309 ,ln_city_name;
2310
2311 if c_cursor%notfound then
2312 hr_utility.set_location(gv_package_name || '.report_8', 60);
2313 exit;
2314 end if;
2315
2316
2317 /************************************************************
2318 ** If Assignment Set is used, pick up only those employee
2319 ** assignments which are part of the Assignment Set - STATIC
2320 ** or DYNAMIC.
2321 ************************************************************/
2322 hr_utility.set_location(gv_package_name || '.report_8', 70);
2323
2324 hr_utility.set_location(gv_package_name || '.report_8', 80);
2325 formated_static_data( null
2326 ,null
2327 ,null
2328 ,ln_old_juri_code -- Intentional see report format
2329 ,null
2330 ,ln_new_juri_code -- Intentional see report format
2331 ,ln_city_name
2332 ,null
2333 ,null
2334 ,p_output_file_type
2335 ,lv_data_row1);
2336
2337 lv_data_row := lv_data_row1;
2338 hr_utility.set_location(gv_package_name || '.report_8', 90);
2339
2340 -- if p_output_file_type ='HTML' then
2341 lv_data_row := '<tr>' || lv_data_row || '</tr>' ;
2342 -- end if;
2343
2344 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, lv_data_row);
2345
2346
2347 /*****************************************************************
2348 ** initialize Data varaibles
2349 *****************************************************************/
2350 lv_data_row := null;
2351 end loop;
2352 close c_cursor;
2353
2354 /*****************************************************
2355 ** Close of the Data Section of the Report
2356 *****************************************************/
2357
2358 if p_output_file_type ='HTML' then
2359 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '</table></body></html>');
2360 end if;
2361
2362 END report_8;
2363
2364
2365 PROCEDURE report_9
2366 (p_process_mode in varchar2
2367 ,p_geocode_patch_name in varchar2
2368 ,p_output_file_type in varchar2
2369 )
2370 IS
2371
2372
2373 /************************************************************
2374 ** Cursor to get all the employee and assignment data.
2375 ************************************************************/
2376 cursor c_cursor ( cp_process_mode in varchar
2377 ,cp_geocode_patch_name in varchar
2378 ) is
2379
2380 select /*+ ORDERED
2381 INDEX (PAY_US_GEO_UPDATE PAY_US_GEO_UPDATE_N2)
2382 INDEX (PAY_PATCH_STATUS PAY_PATCH_STATUS_N1) */
2383 distinct substr(ppf.full_name,1,40) ,
2384 pef.assignment_id ,
2385 substr(pef.assignment_number,1,17),
2386 substr(pusc.city_name,1,30),
2387 pgu.new_juri_code
2388 from pay_patch_status pps,
2389 pay_us_geo_update pgu,
2390 pay_us_city_names pusc,
2391 per_assignments_f pef,
2392 per_people_f ppf
2393 where pgu.process_type = 'NEW_CITY_RECORDS'
2394 and pef.assignment_id = pgu.assignment_id
2395 and ppf.person_id = pef.person_id
2396 and pusc.city_code = substr(new_juri_code,8,4)
2397 and pusc.county_code = substr(new_juri_code,4,3)
2398 and pusc.state_code = substr(new_juri_code,1,2)
2399 and pgu.process_mode = cp_process_mode
2400 and pusc.primary_flag = 'Y'
2401 and pgu.id = pps.id
2402 and pps.patch_name = cp_geocode_patch_name;
2403
2404
2405
2406 ln_full_name varchar2(40);
2407 ln_assignment_id number;
2408 ln_assignment_number varchar2(17);
2409 ln_city_name varchar2(30);
2410 ln_jd_code varchar2(11);
2411
2412 lv_header_label VARCHAR2(32000);
2413
2414 lv_data_row VARCHAR2(32000);
2415 lv_data_row1 VARCHAR2(32000);
2416
2417 lv_format1 varchar2(32000);
2418
2419
2420 BEGIN
2421
2422 hr_utility.set_location(gv_package_name || '.report_9', 10);
2423
2424 /****************************************************************
2425 ** Concatnating the second Header Label which includes the User
2426 ** Defined data set so that it is printed at the end of the
2427 ** report.
2428 ****************************************************************/
2429
2430 fnd_file.put_line(fnd_file.output, formated_header_string(
2431 'IX. Summary of employees for whom new city tax records have been created'
2432 ,p_output_file_type
2433 ));
2434
2435 fnd_file.put_line(fnd_file.output, formated_header_string(
2436 'NO ACTION IS REQUIRED. This is for information ONLY.'
2437 ,p_output_file_type
2438 ));
2439
2440 fnd_file.put_line(fnd_file.output, formated_header_string(
2441 'Please ensure that for these assignments, the tax records are as expected and the percent'
2442 ,p_output_file_type
2443 ));
2444
2445 fnd_file.put_line(fnd_file.output, formated_header_string(
2446 'of time spent in each city is correct. These names may be duplicated from above.'
2447 ,p_output_file_type
2448 ));
2449
2450
2451 hr_utility.set_location(gv_package_name || '.report_9', 15);
2452 /****************************************************************
2453 ** Print the Header Information. If the format is HTML then open
2454 ** the body and table before printing the header info, otherwise
2455 ** just print the header information.
2456 ****************************************************************/
2457 if p_output_file_type ='HTML' then
2458 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<body>');
2459 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<table border=1>');
2460 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<tr>');
2461 end if;
2462 hr_utility.set_location(gv_package_name || '.report_9', 20);
2463
2464 lv_format1 :=
2465 formated_data_string (p_input_string => 'Full Name'
2466 ,p_bold => 'Y'
2467 ,p_output_file_type => p_output_file_type) ||
2468
2469 formated_data_string (p_input_string => 'Assignment Id'
2470 ,p_bold => 'Y'
2471 ,p_output_file_type => p_output_file_type) ||
2472
2473 formated_data_string (p_input_string => 'Assignment Number'
2474 ,p_bold => 'Y'
2475 ,p_output_file_type => p_output_file_type) ||
2476
2477 formated_data_string (p_input_string => 'Primary City'
2478 ,p_bold => 'Y'
2479 ,p_output_file_type => p_output_file_type) ||
2480
2481 formated_data_string (p_input_string => 'Jurisdiction Code'
2482 ,p_bold => 'Y'
2483 ,p_output_file_type => p_output_file_type) ;
2484
2485 hr_utility.set_location(gv_package_name || '.report_9', 30);
2486
2487
2488 fnd_file.put_line(fnd_file.output, lv_format1);
2489
2490 if p_output_file_type ='HTML' then
2491 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '</tr>');
2492 end if;
2493
2494 hr_utility.set_location(gv_package_name || '.report_9', 40);
2495 /*****************************************************
2496 ** Start of the Data Section of the Report
2497 *****************************************************/
2498
2499 -- HR_UTILITY.TRACE_ON(NULL,'TCL');
2500 open c_cursor( p_process_mode
2501 ,p_geocode_patch_name
2502 );
2503
2504 loop
2505 hr_utility.set_location(gv_package_name || '.report_9', 50);
2506
2507 fetch c_cursor into ln_full_name
2508 ,ln_assignment_id
2509 ,ln_assignment_number
2510 ,ln_city_name
2511 ,ln_jd_code;
2512
2513 if c_cursor%notfound then
2514 hr_utility.set_location(gv_package_name || '.report_9', 60);
2515 exit;
2516 end if;
2517
2518
2519 /************************************************************
2520 ** If Assignment Set is used, pick up only those employee
2521 ** assignments which are part of the Assignment Set - STATIC
2522 ** or DYNAMIC.
2523 ************************************************************/
2524 hr_utility.set_location(gv_package_name || '.report_9', 70);
2525 hr_utility.trace('Assignment ID = ' || ln_assignment_id);
2526
2527
2528 hr_utility.set_location(gv_package_name || '.report_9', 80);
2529 formated_static_data( ln_full_name
2530 ,ln_assignment_id
2531 ,ln_assignment_number
2532 ,null
2533 ,ln_city_name
2534 ,ln_jd_code
2535 ,null
2536 ,null
2537 ,null
2538 ,p_output_file_type
2539 ,lv_data_row1);
2540
2541 lv_data_row := lv_data_row1;
2542 hr_utility.set_location(gv_package_name || '.report_9', 90);
2543
2544 -- if p_output_file_type ='HTML' then
2545 lv_data_row := '<tr>' || lv_data_row || '</tr>' ;
2546 -- end if;
2547
2548 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, lv_data_row);
2549
2550
2551 /*****************************************************************
2552 ** initialize Data varaibles
2553 *****************************************************************/
2554 lv_data_row := null;
2555 end loop;
2556 close c_cursor;
2557
2558 /*****************************************************
2559 ** Close of the Data Section of the Report
2560 *****************************************************/
2561
2562 if p_output_file_type ='HTML' then
2563 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '</table></body></html>');
2564 end if;
2565
2566 END report_9;
2567
2568
2569 PROCEDURE report_10
2570 (p_process_mode in varchar2
2571 ,p_geocode_patch_name in varchar2
2572 ,p_output_file_type in varchar2
2573 )
2574 IS
2575
2576
2577 /************************************************************
2578 ** Cursor to get all the employee and assignment data.
2579 ************************************************************/
2580 cursor c_cursor ( cp_process_mode in varchar
2581 ,cp_geocode_patch_name in varchar
2582 ) is
2583
2584 select /*+ ORDERED
2585 INDEX (PAY_US_GEO_UPDATE PAY_US_GEO_UPDATE_N2 )
2586 INDEX (PAY_PATCH_STATUS PAY_PATCH_STATUS_N1) */
2587 distinct substr(ppf.full_name,1,40) ,
2588 pef.assignment_id ,
2589 substr(pef.assignment_number,1,17),
2590 substr(pusc.city_name,1,30),
2591 pgu.new_juri_code
2592 from pay_patch_status pps,
2593 pay_us_geo_update pgu,
2594 pay_us_city_names pusc,
2595 per_assignments_f pef,
2596 per_people_f ppf
2597 where pgu.process_type = 'NEW_VERTEX_RECORDS'
2598 and pef.assignment_id = pgu.assignment_id
2599 and ppf.person_id = pef.person_id
2600 and pusc.city_code = substr(new_juri_code,8,4)
2601 and pusc.county_code = substr(new_juri_code,4,3)
2602 and pusc.state_code = substr(new_juri_code,1,2)
2603 and pusc.primary_flag = 'Y'
2604 and pgu.process_mode = cp_process_mode
2605 and pgu.id = pps.id
2606 and pps.patch_name = cp_geocode_patch_name;
2607
2608
2609
2610
2611 ln_full_name varchar2(40);
2612 ln_assignment_id number;
2613 ln_assignment_number varchar2(17);
2614 ln_city_name varchar2(30);
2615 ln_jd_code varchar2(11);
2616
2617 lv_header_label VARCHAR2(32000);
2618
2619 lv_data_row VARCHAR2(32000);
2620 lv_data_row1 VARCHAR2(32000);
2621
2622 lv_format1 varchar2(32000);
2623
2624
2625 BEGIN
2626
2627 hr_utility.set_location(gv_package_name || '.report_10', 10);
2628
2629 /****************************************************************
2630 ** Concatnating the second Header Label which includes the User
2631 ** Defined data set so that it is printed at the end of the
2632 ** report.
2633 ****************************************************************/
2634
2635 fnd_file.put_line(fnd_file.output, formated_header_string(
2636 'X. Summary of employees for whom new Vertex Element Entry records have been created'
2637 ,p_output_file_type
2638 ));
2639
2640 fnd_file.put_line(fnd_file.output, formated_header_string(
2641 'NO ACTION IS REQUIRED. This is for information ONLY.'
2642 ,p_output_file_type
2643 ));
2644
2645 fnd_file.put_line(fnd_file.output, formated_header_string(
2646 'Please ensure that for these assignments, the tax records are as expected and the percent'
2647 ,p_output_file_type
2648 ));
2649
2650 fnd_file.put_line(fnd_file.output, formated_header_string(
2651 'of time spent in each city is correct and the sum of percent of time spent in all states equals 100.'
2652 ,p_output_file_type
2653 ));
2654
2655 fnd_file.put_line(fnd_file.output, formated_header_string(
2656 'These names may be duplicated from above. They are listed just for a reference.'
2657 ,p_output_file_type
2658 ));
2659
2660
2661 hr_utility.set_location(gv_package_name || '.report_10', 15);
2662 /****************************************************************
2663 ** Print the Header Information. If the format is HTML then open
2664 ** the body and table before printing the header info, otherwise
2665 ** just print the header information.
2666 ****************************************************************/
2667 if p_output_file_type ='HTML' then
2668 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<body>');
2669 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<table border=1>');
2670 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<tr>');
2671 end if;
2672 hr_utility.set_location(gv_package_name || '.report_10', 20);
2673
2674 lv_format1 :=
2675 formated_data_string (p_input_string => 'Full Name'
2676 ,p_bold => 'Y'
2677 ,p_output_file_type => p_output_file_type) ||
2678
2679 formated_data_string (p_input_string => 'Assignment Id'
2680 ,p_bold => 'Y'
2681 ,p_output_file_type => p_output_file_type) ||
2682
2683 formated_data_string (p_input_string => 'Assignment Number'
2684 ,p_bold => 'Y'
2685 ,p_output_file_type => p_output_file_type) ||
2686
2687 formated_data_string (p_input_string => 'Primary City'
2688 ,p_bold => 'Y'
2689 ,p_output_file_type => p_output_file_type) ||
2690
2691 formated_data_string (p_input_string => 'Jurisdiction Code'
2692 ,p_bold => 'Y'
2693 ,p_output_file_type => p_output_file_type) ;
2694
2695 hr_utility.set_location(gv_package_name || '.report_10', 30);
2696
2697
2698 fnd_file.put_line(fnd_file.output, lv_format1);
2699
2700 if p_output_file_type ='HTML' then
2701 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '</tr>');
2702 end if;
2703
2704 hr_utility.set_location(gv_package_name || '.report_10', 40);
2705 /*****************************************************
2706 ** Start of the Data Section of the Report
2707 *****************************************************/
2708
2709 -- HR_UTILITY.TRACE_ON(NULL,'TCL');
2710 open c_cursor( p_process_mode
2711 ,p_geocode_patch_name
2712 );
2713
2714 loop
2715 hr_utility.set_location(gv_package_name || '.report_10', 50);
2716
2717 fetch c_cursor into ln_full_name
2718 ,ln_assignment_id
2719 ,ln_assignment_number
2720 ,ln_city_name
2721 ,ln_jd_code;
2722
2723 if c_cursor%notfound then
2724 hr_utility.set_location(gv_package_name || '.report_10', 60);
2725 exit;
2726 end if;
2727
2728
2729 /************************************************************
2730 ** If Assignment Set is used, pick up only those employee
2731 ** assignments which are part of the Assignment Set - STATIC
2732 ** or DYNAMIC.
2733 ************************************************************/
2734 hr_utility.set_location(gv_package_name || '.report_10', 70);
2735 hr_utility.trace('Assignment ID = ' || ln_assignment_id);
2736
2737
2738 hr_utility.set_location(gv_package_name || '.report_10', 80);
2739 formated_static_data( ln_full_name
2740 ,ln_assignment_id
2741 ,ln_assignment_number
2742 ,null
2743 ,ln_city_name
2744 ,ln_jd_code
2745 ,null
2746 ,null
2747 ,null
2748 ,p_output_file_type
2749 ,lv_data_row1);
2750
2751 lv_data_row := lv_data_row1;
2752 hr_utility.set_location(gv_package_name || '.report_10', 90);
2753
2754 -- if p_output_file_type ='HTML' then
2755 lv_data_row := '<tr>' || lv_data_row || '</tr>' ;
2756 -- end if;
2757
2758 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, lv_data_row);
2759
2760
2761 /*****************************************************************
2762 ** initialize Data varaibles
2763 *****************************************************************/
2764 lv_data_row := null;
2765 end loop;
2766 close c_cursor;
2767
2768 /*****************************************************
2769 ** Close of the Data Section of the Report
2770 *****************************************************/
2771
2772 if p_output_file_type ='HTML' then
2773 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '</table></body></html>');
2774 end if;
2775
2776 END report_10;
2777
2778 PROCEDURE report_11
2779 (p_process_mode in varchar2
2780 ,p_geocode_patch_name in varchar2
2781 ,p_output_file_type in varchar2
2782 )
2783 IS
2784
2785
2786 /************************************************************
2787 ** Cursor to get all the employee and assignment data.
2788 ************************************************************/
2789 cursor c_cursor ( cp_process_mode in varchar
2790 ,cp_geocode_patch_name in varchar
2791 ) is
2792
2793 select /*+ ORDERED
2794 INDEX (PAY_US_GEO_UPDATE PAY_US_GEO_UPDATE_N2 )
2795 INDEX (PAY_PATCH_STATUS PAY_PATCH_STATUS_N1) */
2796 distinct old_juri_code ,
2797 new_juri_code
2798 from pay_patch_status pps,
2799 pay_us_geo_update pgu
2800 where pgu.process_type = 'TAX_RULES_CHANGE'
2801 and pgu.process_mode = cp_process_mode
2802 and pgu.id = pps.id
2803 and pps.patch_name = cp_geocode_patch_name;
2804
2805 ln_old_jd_code varchar2(11);
2806 ln_new_jd_code varchar2(11);
2807
2808 lv_header_label VARCHAR2(32000);
2809
2810 lv_data_row VARCHAR2(32000);
2811 lv_data_row1 VARCHAR2(32000);
2812
2813 lv_format1 varchar2(32000);
2814
2815
2816 BEGIN
2817
2818 hr_utility.set_location(gv_package_name || '.report_11', 10);
2819
2820 /****************************************************************
2821 ** Concatnating the second Header Label which includes the User
2822 ** Defined data set so that it is printed at the end of the
2823 ** report.
2824 ****************************************************************/
2825
2826 fnd_file.put_line(fnd_file.output, formated_header_string(
2827 'XI. New taxability rules.'
2828 ,p_output_file_type
2829 ));
2830
2831 fnd_file.put_line(fnd_file.output, formated_header_string(
2832 'The taxability rules have been changed for the following jurisdiction codes to reflect the new jurisdiction code.'
2833 ,p_output_file_type
2834 ));
2835
2836 hr_utility.set_location(gv_package_name || '.report_11', 15);
2837 /****************************************************************
2838 ** Print the Header Information. If the format is HTML then open
2839 ** the body and table before printing the header info, otherwise
2840 ** just print the header information.
2841 ****************************************************************/
2842 if p_output_file_type ='HTML' then
2843 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<body>');
2844 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<table border=1>');
2845 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<tr>');
2846 end if;
2847 hr_utility.set_location(gv_package_name || '.report_11', 20);
2848
2849 lv_format1 :=
2850 formated_data_string (p_input_string => 'Old JD'
2851 ,p_bold => 'Y'
2852 ,p_output_file_type => p_output_file_type) ||
2853
2854 formated_data_string (p_input_string => 'New JD'
2855 ,p_bold => 'Y'
2856 ,p_output_file_type => p_output_file_type)
2857 ;
2858
2859 hr_utility.set_location(gv_package_name || '.report_11', 30);
2860
2861
2862 fnd_file.put_line(fnd_file.output, lv_format1);
2863
2864 if p_output_file_type ='HTML' then
2865 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '</tr>');
2866 end if;
2867
2868 hr_utility.set_location(gv_package_name || '.report_11', 40);
2869 /*****************************************************
2870 ** Start of the Data Section of the Report
2871 *****************************************************/
2872
2873 -- HR_UTILITY.TRACE_ON(NULL,'TCL');
2874 open c_cursor( p_process_mode
2875 ,p_geocode_patch_name
2876 );
2877
2878 loop
2879 hr_utility.set_location(gv_package_name || '.report_11', 50);
2880
2881 fetch c_cursor into ln_old_jd_code
2882 ,ln_new_jd_code;
2883
2884 if c_cursor%notfound then
2885 hr_utility.set_location(gv_package_name || '.report_11', 60);
2886 exit;
2887 end if;
2888
2889
2890 /************************************************************
2891 ** If Assignment Set is used, pick up only those employee
2892 ** assignments which are part of the Assignment Set - STATIC
2893 ** or DYNAMIC.
2894 ************************************************************/
2895 hr_utility.set_location(gv_package_name || '.report_11', 70);
2896
2897 hr_utility.set_location(gv_package_name || '.report_11', 80);
2898 formated_static_data( null
2899 ,null
2900 ,null
2901 ,ln_old_jd_code
2902 ,null
2903 ,ln_new_jd_code
2904 ,null
2905 ,null
2906 ,null
2907 ,p_output_file_type
2908 ,lv_data_row1);
2909
2910 lv_data_row := lv_data_row1;
2911 hr_utility.set_location(gv_package_name || '.report_11', 90);
2912
2913 -- if p_output_file_type ='HTML' then
2914 lv_data_row := '<tr>' || lv_data_row || '</tr>' ;
2915 -- end if;
2916
2917 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, lv_data_row);
2918
2919
2920 /*****************************************************************
2921 ** initialize Data varaibles
2922 *****************************************************************/
2923 lv_data_row := null;
2924 end loop;
2925 close c_cursor;
2926
2927 /*****************************************************
2928 ** Close of the Data Section of the Report
2929 *****************************************************/
2930
2931 if p_output_file_type ='HTML' then
2932 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '</table></body></html>');
2933 end if;
2934
2935 END report_11;
2936
2937
2938 PROCEDURE report_12
2939 (p_process_mode in varchar2
2940 ,p_geocode_patch_name in varchar2
2941 ,p_output_file_type in varchar2
2942 )
2943 IS
2944
2945
2946 /************************************************************
2947 ** Cursor to get all the employee and assignment data.
2948 ************************************************************/
2949 cursor c_cursor ( cp_process_mode in varchar
2950 ,cp_geocode_patch_name in varchar
2951 ) is
2952
2953 SELECT /*+ ORDERED
2954 INDEX (PAY_US_MODIFIED_GEOCODES PAY_US_MODIFIED_GEOCODES_N1) */
2955 distinct
2956 substr(ppf.full_name,1,40),
2957 paf.assignment_id ,
2958 substr(paf.assignment_number,1,17),
2959 substr(pmod.city_name,1,30),
2960 ectr.jurisdiction_code,
2961 substr(puc1.county_name,1,20),
2962 pmod.state_code||'-'||pmod.new_county_code||'-'||pmod.new_city_code "New JD",
2963 substr(puc2.county_name,1,20)
2964 FROM pay_us_modified_geocodes pmod,
2965 pay_us_emp_city_tax_rules_f ectr,
2966 per_assignments_f paf,
2967 per_people_f ppf,
2968 pay_us_counties puc1,
2969 pay_us_counties puc2
2970 WHERE ppf.person_id = paf.person_id
2971 AND pmod.state_code = ectr.state_code
2972 AND pmod.state_code = puc1.state_code
2973 AND pmod.state_code = puc2.state_code
2974 AND pmod.county_code = puc1.county_code
2975 AND pmod.new_county_code = puc2.county_code
2976 AND pmod.county_code = ectr.county_code
2977 AND pmod.new_county_code is not null
2978 AND pmod.old_city_code = ectr.city_code
2979 AND ectr.assignment_id = paf.assignment_id
2980 AND pmod.patch_name = cp_geocode_patch_name
2981 and pmod.process_type in ('P', 'PC', 'PU', 'S', 'SU', 'UP', 'US');
2982
2983 /*Added process_type to make sure that only County Name changes are fetched
2984 in Below cursor. Table Name is not sufficient because for City Name change
2985 the same tables are getting updated Bug#14314081*/
2986
2987 cursor c_county_name_change (cp_geocode_patch_name in varchar) is
2988
2989 SELECT pugp.new_juri_code,pugp.description
2990 FROM pay_us_geo_update pugp,
2991 pay_patch_status pps
2992 WHERE pugp.table_name in ('PER_ADDRESSES','HR_LOCATIONS_ALL')
2993 AND pugp.process_type = 'CN'
2994 AND pugp.id = pps.id
2995 AND pps.patch_name = cp_geocode_patch_name
2996 ORDER BY pugp.description;
2997
2998 ln_full_name varchar2(40);
2999 ln_assignment_id number;
3000 ln_assignment_number varchar2(17);
3001 ln_city_name varchar2(30);
3002 ln_old_juri_code varchar2(11);
3003 ln_old_county varchar2(20);
3004 ln_new_juri_code varchar2(11);
3005 ln_new_county varchar2(20);
3006
3007
3008 lv_header_label VARCHAR2(32000);
3009
3010 lv_data_row VARCHAR2(32000);
3011 lv_data_row1 VARCHAR2(32000);
3012
3013 lv_format1 varchar2(32000);
3014 l_count number;
3015 l_description varchar2(500);
3016
3017
3018 BEGIN
3019
3020 hr_utility.set_location(gv_package_name || '.report_12', 10);
3021
3022 /****************************************************************
3023 ** Concatnating the second Header Label which includes the User
3024 ** Defined data set so that it is printed at the end of the
3025 ** report.
3026 ****************************************************************/
3027
3028 fnd_file.put_line(fnd_file.output, formated_header_string(
3029 'XII. County Code Change. '
3030 ,p_output_file_type
3031 ));
3032
3033 fnd_file.put_line(fnd_file.output, formated_header_string(
3034 'ACTION REQUIRED.'
3035 ,p_output_file_type
3036 ));
3037
3038 fnd_file.put_line(fnd_file.output, formated_header_string(
3039 'The following assignments are located in jurisdictions which are '
3040 ,p_output_file_type
3041 ));
3042
3043 fnd_file.put_line(fnd_file.output, formated_header_string(
3044 'changing their county codes. You must update the jurisdiction information'
3045 ,p_output_file_type
3046 ));
3047
3048 fnd_file.put_line(fnd_file.output, formated_header_string(
3049 'for these assignments as Vertex will discontinue support for the'
3050 ,p_output_file_type
3051 ));
3052
3053 fnd_file.put_line(fnd_file.output, formated_header_string(
3054 'old jurisdictions in upcoming data files and taxes will stop being withheld'
3055 ,p_output_file_type
3056 ));
3057
3058
3059 hr_utility.set_location(gv_package_name || '.report_12', 15);
3060 /****************************************************************
3061 ** Print the Header Information. If the format is HTML then open
3062 ** the body and table before printing the header info, otherwise
3063 ** just print the header information.
3064 ****************************************************************/
3065 if p_output_file_type ='HTML' then
3066 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<body>');
3067 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<table border=1>');
3068 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<tr>');
3069 end if;
3070 hr_utility.set_location(gv_package_name || '.report_12', 20);
3071
3072 lv_format1 :=
3073 formated_data_string (p_input_string => 'Full Name'
3074 ,p_bold => 'Y'
3075 ,p_output_file_type => p_output_file_type) ||
3076
3077 formated_data_string (p_input_string => 'Assignment Id'
3078 ,p_bold => 'Y'
3079 ,p_output_file_type => p_output_file_type) ||
3080
3081 formated_data_string (p_input_string => 'Assignment Number'
3082 ,p_bold => 'Y'
3083 ,p_output_file_type => p_output_file_type) ||
3084
3085 formated_data_string (p_input_string => 'Old JD'
3086 ,p_bold => 'Y'
3087 ,p_output_file_type => p_output_file_type) ||
3088
3089 formated_data_string (p_input_string => 'Old County'
3090 ,p_bold => 'Y'
3091 ,p_output_file_type => p_output_file_type) ||
3092
3093 formated_data_string (p_input_string => 'New JD'
3094 ,p_bold => 'Y'
3095 ,p_output_file_type => p_output_file_type) ||
3096
3097 formated_data_string (p_input_string => 'New County'
3098 ,p_bold => 'Y'
3099 ,p_output_file_type => p_output_file_type) ||
3100
3101 formated_data_string (p_input_string => 'City Name'
3102 ,p_bold => 'Y'
3103 ,p_output_file_type => p_output_file_type) ;
3104
3105 hr_utility.set_location(gv_package_name || '.report_12', 30);
3106
3107
3108 fnd_file.put_line(fnd_file.output, lv_format1);
3109
3110 if p_output_file_type ='HTML' then
3111 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '</tr>');
3112 end if;
3113
3114 hr_utility.set_location(gv_package_name || '.report_12', 40);
3115 /*****************************************************
3116 ** Start of the Data Section of the Report
3117 *****************************************************/
3118
3119 -- HR_UTILITY.TRACE_ON(NULL,'TCL');
3120 open c_cursor( p_process_mode
3121 ,p_geocode_patch_name
3122 );
3123
3124 loop
3125 hr_utility.set_location(gv_package_name || '.report_12', 50);
3126
3127 fetch c_cursor into ln_full_name
3128 ,ln_assignment_id
3129 ,ln_assignment_number
3130 ,ln_city_name
3131 ,ln_old_juri_code
3132 ,ln_old_county
3133 ,ln_new_juri_code
3134 ,ln_new_county;
3135
3136 if c_cursor%notfound then
3137 hr_utility.set_location(gv_package_name || '.report_12', 60);
3138 exit;
3139 end if;
3140
3141
3142 /************************************************************
3143 ** If Assignment Set is used, pick up only those employee
3144 ** assignments which are part of the Assignment Set - STATIC
3145 ** or DYNAMIC.
3146 ************************************************************/
3147 hr_utility.set_location(gv_package_name || '.report_12', 70);
3148 hr_utility.trace('Assignment ID = ' || ln_assignment_id);
3149
3150
3151 hr_utility.set_location(gv_package_name || '.report_12', 80);
3152 formated_static_data( ln_full_name
3153 ,ln_assignment_id
3154 ,ln_assignment_number
3155 ,null
3156 ,ln_old_juri_code
3157 ,ln_old_county
3158 ,ln_new_juri_code
3159 ,ln_new_county
3160 ,ln_city_name
3161 ,p_output_file_type
3162 ,lv_data_row1);
3163
3164 lv_data_row := lv_data_row1;
3165 hr_utility.set_location(gv_package_name || '.report_12', 90);
3166
3167 -- if p_output_file_type ='HTML' then
3168 lv_data_row := '<tr>' || lv_data_row || '</tr>' ;
3169 -- end if;
3170
3171 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, lv_data_row);
3172
3173
3174 /*****************************************************************
3175 ** initialize Data varaibles
3176 *****************************************************************/
3177 lv_data_row := null;
3178 end loop;
3179 close c_cursor;
3180
3181 /*****************************************************
3182 ** Close of the Data Section of the Report
3183 *****************************************************/
3184
3185 if p_output_file_type ='HTML' then
3186 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '</table></body></html>');
3187 end if;
3188
3189 --Added for Annual GEO 2010 Bug#9541247
3190 --The Details about County Name Change will only come in the Report
3191 --only if there was actual requirement to update the county name
3192
3193 /*****************************************************
3194 ** County Name Change Report
3195 *****************************************************/
3196
3197 OPEN c_county_name_change(p_geocode_patch_name);
3198 FETCH c_county_name_change INTO l_count,l_description;
3199
3200 IF c_county_name_change%FOUND THEN
3201
3202 fnd_file.put_line(fnd_file.output, formated_header_string(
3203 'County Name Change'
3204 ,p_output_file_type
3205 ));
3206
3207 END IF;
3208
3209 WHILE (c_county_name_change%FOUND)
3210 LOOP
3211 fnd_file.put_line(fnd_file.output, formated_header_string(
3212 l_description
3213 ,p_output_file_type
3214 ));
3215 FETCH c_county_name_change INTO l_count,l_description;
3216 END LOOP;
3217
3218 CLOSE c_county_name_change;
3219
3220 --End Bug#9541247
3221
3222 END report_12;
3223
3224 PROCEDURE report_13
3225 (p_process_mode in varchar2
3226 ,p_geocode_patch_name in varchar2
3227 ,p_output_file_type in varchar2
3228 )
3229 IS
3230
3231 ln_row_count number;
3232
3233
3234 lv_header_label VARCHAR2(32000);
3235
3236 lv_data_row VARCHAR2(32000);
3237 lv_data_row1 VARCHAR2(32000);
3238
3239 lv_format1 varchar2(32000);
3240
3241
3242 BEGIN
3243
3244 hr_utility.set_location(gv_package_name || '.report_13', 10);
3245
3246 /****************************************************************
3247 ** Concatnating the second Header Label which includes the User
3248 ** Defined data set so that it is printed at the end of the
3249 ** report.
3250 ****************************************************************/
3251
3252 fnd_file.put_line(fnd_file.output, formated_header_string(
3253 'XIII. Table Row Counts. '
3254 ,p_output_file_type
3255 ));
3256
3257 fnd_file.put_line(fnd_file.output, formated_header_string(
3258 'NO ACTION IS REQUIRED. This is for information ONLY.'
3259 ,p_output_file_type
3260 ));
3261
3262 fnd_file.put_line(fnd_file.output, formated_header_string(
3263 'The following tables were updated and now have the following row counts:'
3264 ,p_output_file_type
3265 ));
3266
3267
3268 hr_utility.set_location(gv_package_name || '.report_13', 15);
3269 /****************************************************************
3270 ** Print the Header Information. If the format is HTML then open
3271 ** the body and table before printing the header info, otherwise
3272 ** just print the header information.
3273 ****************************************************************/
3274 if p_output_file_type ='HTML' then
3275 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<body>');
3276 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<table border=1>');
3277 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<tr>');
3278 end if;
3279 hr_utility.set_location(gv_package_name || '.report_13', 20);
3280
3281 lv_format1 :=
3282 formated_data_string (p_input_string => 'Table Name'
3283 ,p_bold => 'Y'
3284 ,p_output_file_type => p_output_file_type) ||
3285
3286 formated_data_string (p_input_string => 'Row Count'
3287 ,p_bold => 'Y'
3288 ,p_output_file_type => p_output_file_type)
3289 ;
3290
3291 hr_utility.set_location(gv_package_name || '.report_13', 30);
3292
3293
3294 fnd_file.put_line(fnd_file.output, lv_format1);
3295
3296 if p_output_file_type ='HTML' then
3297 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '</tr>');
3298 end if;
3299
3300 hr_utility.set_location(gv_package_name || '.report_13', 40);
3301 /*****************************************************
3302 ** Start of the Data Section of the Report
3303 *****************************************************/
3304
3305 hr_utility.set_location(gv_package_name || '.report_13', 50);
3306
3307 /************************************************************
3308 ** If Assignment Set is used, pick up only those employee
3309 ** assignments which are part of the Assignment Set - STATIC
3310 ** or DYNAMIC.
3311 ************************************************************/
3312
3313 hr_utility.set_location(gv_package_name || '.report_13', 60);
3314
3315 -- count for pay_us_states --
3316
3317 ln_row_count := 0;
3318
3319 select count(*)
3320 into ln_row_count
3321 from pay_us_states;
3322
3323 formated_static_data( 'PAY_US_STATES'
3324 ,ln_row_count
3325 ,null
3326 ,null
3327 ,null
3328 ,null
3329 ,null
3330 ,null
3331 ,null
3332 ,p_output_file_type
3333 ,lv_data_row1);
3334
3335
3336 lv_data_row := lv_data_row1;
3337 hr_utility.set_location(gv_package_name || '.report_13', 65);
3338
3339 lv_data_row := '<tr>' || lv_data_row || '</tr>' ;
3340
3341 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, lv_data_row);
3342
3343 -- count for pay_us_counties --
3344
3345 ln_row_count := 0;
3346
3347 select count(*)
3348 into ln_row_count
3349 from pay_us_counties;
3350
3351 formated_static_data( 'PAY_US_COUNTIES'
3352 ,ln_row_count
3353 ,null
3354 ,null
3355 ,null
3356 ,null
3357 ,null
3358 ,null
3359 ,null
3360 ,p_output_file_type
3361 ,lv_data_row1);
3362
3363
3364 lv_data_row := lv_data_row1;
3365 hr_utility.set_location(gv_package_name || '.report_13', 70);
3366
3367 lv_data_row := '<tr>' || lv_data_row || '</tr>' ;
3368
3369 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, lv_data_row);
3370
3371 -- count for pay_us_city_geocodes --
3372
3373 ln_row_count := 0;
3374
3375 select count(*)
3376 into ln_row_count
3377 from pay_us_city_geocodes;
3378
3379 formated_static_data( 'PAY_US_CITY_GEOCODES'
3380 ,ln_row_count
3381 ,null
3382 ,null
3383 ,null
3384 ,null
3385 ,null
3386 ,null
3387 ,null
3388 ,p_output_file_type
3389 ,lv_data_row1);
3390
3391 lv_data_row := lv_data_row1;
3392 hr_utility.set_location(gv_package_name || '.report_13', 75);
3393
3394 lv_data_row := '<tr>' || lv_data_row || '</tr>' ;
3395
3396 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, lv_data_row);
3397
3398 -- count for pay_us_city_names --
3399
3400 ln_row_count := 0;
3401
3402 select count(*)
3403 into ln_row_count
3404 from pay_us_city_names;
3405
3406 formated_static_data( 'PAY_US_CITY_NAMES'
3407 ,ln_row_count
3408 ,null
3409 ,null
3410 ,null
3411 ,null
3412 ,null
3413 ,null
3414 ,null
3415 ,p_output_file_type
3416 ,lv_data_row1);
3417
3418 lv_data_row := lv_data_row1;
3419 hr_utility.set_location(gv_package_name || '.report_13', 80);
3420
3421 lv_data_row := '<tr>' || lv_data_row || '</tr>' ;
3422
3423 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, lv_data_row);
3424
3425 -- count for pay_us_zip_codes --
3426
3427 ln_row_count := 0;
3428
3429 select count(*)
3430 into ln_row_count
3431 from pay_us_zip_codes;
3432
3433 formated_static_data( 'PAY_US_ZIP_CODES'
3434 ,ln_row_count
3435 ,null
3436 ,null
3437 ,null
3438 ,null
3439 ,null
3440 ,null
3441 ,null
3442 ,p_output_file_type
3443 ,lv_data_row1);
3444
3445 lv_data_row := lv_data_row1;
3446 hr_utility.set_location(gv_package_name || '.report_13', 85);
3447
3448 lv_data_row := '<tr>' || lv_data_row || '</tr>' ;
3449
3450 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, lv_data_row);
3451
3452 -- count for pay_us_modified_geocodes --
3453
3454 ln_row_count := 0;
3455
3456 select count(*)
3457 into ln_row_count
3458 from pay_us_modified_geocodes;
3459
3460 formated_static_data( 'PAY_US_MODIFIED_GEOCODES'
3461 ,ln_row_count
3462 ,null
3463 ,null
3464 ,null
3465 ,null
3466 ,null
3467 ,null
3468 ,null
3469 ,p_output_file_type
3470 ,lv_data_row1);
3471
3472 lv_data_row := lv_data_row1;
3473 hr_utility.set_location(gv_package_name || '.report_13', 90);
3474
3475 lv_data_row := '<tr>' || lv_data_row || '</tr>' ;
3476
3477 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, lv_data_row);
3478 /*****************************************************************
3479 ** initialize Data varaibles
3480 *****************************************************************/
3481 lv_data_row := null;
3482
3483
3484 /*****************************************************
3485 ** Close of the Data Section of the Report
3486 *****************************************************/
3487
3488 if p_output_file_type ='HTML' then
3489 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '</table></body></html>');
3490 end if;
3491
3492 END report_13;
3493
3494 PROCEDURE report_14
3495 (p_process_mode in varchar2
3496 ,p_geocode_patch_name in varchar2
3497 ,p_output_file_type in varchar2
3498 )
3499 IS
3500
3501
3502 /************************************************************
3503 ** Cursor to get all the employee and assignment data.
3504 ************************************************************/
3505 cursor c_cursor ( cp_process_mode in varchar
3506 ,cp_geocode_patch_name in varchar
3507 ) is
3508
3509 select distinct hla.location_code "Work Location",
3510 hla.description "Location Description",
3511 hla.address_line_1 "Address",
3512 hla.town_or_city "City Name",
3513 hla.region_1 "County",
3514 hla.region_2 "State",
3515 hla.postal_code "Zipcode"
3516 from hr_locations_all hla
3517 where hla.location_id in
3518 (select distinct location_id
3519 from per_assignments_f paf,
3520 pay_us_emp_city_tax_rules_f pctr
3521 where ( ( pctr.STATE_CODE = '26'
3522 and pctr.county_code = '510'
3523 and pctr.city_code = '1270')
3524 or ( pctr.state_code = '21'
3525 and pctr.county_code = '510'
3526 and pctr.city_code = '0040')
3527 )
3528 and pctr.assignment_id = paf.assignment_id )
3529 and postal_code in
3530 ( '63142',
3531 '63148',
3532 '63149',
3533 '63152',
3534 '63153',
3535 '63154',
3536 '63159',
3537 '63161',
3538 '63162',
3539 '63165',
3540 '63168',
3541 '63170',
3542 '63172',
3543 '63173',
3544 '63174',
3545 '63175',
3546 '63176',
3547 '63181',
3548 '63183',
3549 '63184',
3550 '63185',
3551 '63186',
3552 '63187',
3553 '63189',
3554 '63191',
3555 '63192',
3556 '63193',
3557 '63194',
3558 '21232',
3559 '21238',
3560 '21242',
3561 '21243',
3562 '21245',
3563 '21246',
3564 '21247',
3565 '21248',
3566 '21249',
3567 '21253',
3568 '21254',
3569 '21255',
3570 '21256',
3571 '21257',
3572 '21258',
3573 '21259',
3574 '21260',
3575 '21261',
3576 '21262',
3577 '21266',
3578 '21267',
3579 '21269',
3580 '21271',
3581 '21272',
3582 '21276',
3583 '21277',
3584 '21291',
3585 '21292',
3586 '21293',
3587 '21294',
3588 '21295',
3589 '21296',
3590 '21299' );
3591
3592
3593
3594 ln_work_location varchar2(60);
3595 ln_loc_description varchar2(240);
3596 ln_address varchar2(240);
3597 ln_city_name varchar2(30);
3598 ln_county varchar2(120);
3599 ln_state varchar2(120);
3600 ln_zip_code varchar2(30);
3601
3602 lv_header_label VARCHAR2(32000);
3603
3604 lv_data_row VARCHAR2(32000);
3605 lv_data_row1 VARCHAR2(32000);
3606
3607 lv_format1 varchar2(32000);
3608
3609
3610 BEGIN
3611
3612 hr_utility.set_location(gv_package_name || '.report_14', 10);
3613
3614 /****************************************************************
3615 ** Concatnating the second Header Label which includes the User
3616 ** Defined data set so that it is printed at the end of the
3617 ** report.
3618 ****************************************************************/
3619
3620 fnd_file.put_line(fnd_file.output, formated_header_string(
3621 'XIV. Work Location ZIP Code Support.'
3622 ,p_output_file_type
3623 ));
3624
3625 fnd_file.put_line(fnd_file.output, formated_header_string(
3626 'ACTION REQUIRED.'
3627 ,p_output_file_type
3628 ));
3629
3630 fnd_file.put_line(fnd_file.output, formated_header_string(
3631 'The following work locations are using ZIP Codes no longer '
3632 ,p_output_file_type
3633 ));
3634
3635 fnd_file.put_line(fnd_file.output, formated_header_string(
3636 'supported by Vertex. Please review these locations and adjust'
3637 ,p_output_file_type
3638 ));
3639
3640 fnd_file.put_line(fnd_file.output, formated_header_string(
3641 'Their ZIP Codes to supported values.'
3642 ,p_output_file_type
3643 ));
3644
3645
3646 hr_utility.set_location(gv_package_name || '.report_14', 15);
3647 /****************************************************************
3648 ** Print the Header Information. If the format is HTML then open
3649 ** the body and table before printing the header info, otherwise
3650 ** just print the header information.
3651 ****************************************************************/
3652 if p_output_file_type ='HTML' then
3653 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<body>');
3654 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<table border=1>');
3655 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<tr>');
3656 end if;
3657 hr_utility.set_location(gv_package_name || '.report_14', 20);
3658
3659 lv_format1 :=
3660 formated_data_string (p_input_string => 'Work Location'
3661 ,p_bold => 'Y'
3662 ,p_output_file_type => p_output_file_type) ||
3663
3664 formated_data_string (p_input_string => 'Location Description'
3665 ,p_bold => 'Y'
3666 ,p_output_file_type => p_output_file_type) ||
3667
3668 formated_data_string (p_input_string => 'Address'
3669 ,p_bold => 'Y'
3670 ,p_output_file_type => p_output_file_type) ||
3671
3672 formated_data_string (p_input_string => 'City Name'
3673 ,p_bold => 'Y'
3674 ,p_output_file_type => p_output_file_type) ||
3675
3676 formated_data_string (p_input_string => 'County'
3677 ,p_bold => 'Y'
3678 ,p_output_file_type => p_output_file_type) ||
3679
3680 formated_data_string (p_input_string => 'State'
3681 ,p_bold => 'Y'
3682 ,p_output_file_type => p_output_file_type) ||
3683
3684 formated_data_string (p_input_string => 'Zipcode'
3685 ,p_bold => 'Y'
3686 ,p_output_file_type => p_output_file_type)
3687 ;
3688
3689 hr_utility.set_location(gv_package_name || '.report_14', 30);
3690
3691
3692 fnd_file.put_line(fnd_file.output, lv_format1);
3693
3694 if p_output_file_type ='HTML' then
3695 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '</tr>');
3696 end if;
3697
3698 hr_utility.set_location(gv_package_name || '.report_14', 40);
3699 /*****************************************************
3700 ** Start of the Data Section of the Report
3701 *****************************************************/
3702
3703 -- HR_UTILITY.TRACE_ON(NULL,'TCL');
3704 open c_cursor( p_process_mode
3705 ,p_geocode_patch_name
3706 );
3707
3708 loop
3709 hr_utility.set_location(gv_package_name || '.report_14', 50);
3710
3711 fetch c_cursor into ln_work_location
3712 ,ln_loc_description
3713 ,ln_address
3714 ,ln_city_name
3715 ,ln_county
3716 ,ln_state
3717 ,ln_zip_code;
3718
3719 if c_cursor%notfound then
3720 hr_utility.set_location(gv_package_name || '.report_14', 60);
3721 exit;
3722 end if;
3723
3724
3725 /************************************************************
3726 ** If Assignment Set is used, pick up only those employee
3727 ** assignments which are part of the Assignment Set - STATIC
3728 ** or DYNAMIC.
3729 ************************************************************/
3730 hr_utility.set_location(gv_package_name || '.report_14', 70);
3731 --Modified for AGEO2010 such that the Data of Location is shown in same order
3732 --as indicated in Header Record.Also updated loc_description such that a space
3733 --will be printed in case it is left blank.
3734 hr_utility.set_location(gv_package_name || '.report_14', 80);
3735 formated_static_data( ln_work_location
3736 ,null
3737 ,nvl(ln_loc_description,' ')
3738 ,ln_address
3739 ,ln_city_name
3740 ,ln_county
3741 ,ln_state
3742 ,ln_zip_code
3743 ,null
3744 -- ,ln_address
3745 ,p_output_file_type
3746 ,lv_data_row1);
3747
3748 lv_data_row := lv_data_row1;
3749 hr_utility.set_location(gv_package_name || '.report_14', 90);
3750
3751 -- if p_output_file_type ='HTML' then
3752 lv_data_row := '<tr>' || lv_data_row || '</tr>' ;
3753 -- end if;
3754
3755 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, lv_data_row);
3756
3757
3758 /*****************************************************************
3759 ** initialize Data varaibles
3760 *****************************************************************/
3761 lv_data_row := null;
3762 end loop;
3763 close c_cursor;
3764
3765 /*****************************************************
3766 ** Close of the Data Section of the Report
3767 *****************************************************/
3768
3769 if p_output_file_type ='HTML' then
3770 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '</table></body></html>');
3771 end if;
3772
3773 END report_14;
3774
3775 PROCEDURE report_15
3776 (p_process_mode in varchar2
3777 ,p_geocode_patch_name in varchar2
3778 ,p_output_file_type in varchar2
3779 )
3780 IS
3781
3782
3783 /************************************************************
3784 ** Cursor to get all the employee and assignment data.
3785 ************************************************************/
3786 cursor c_cursor ( cp_process_mode in varchar
3787 ,cp_geocode_patch_name in varchar
3788 ) is
3789
3790 SELECT distinct substr(ppf.full_name,1,40),
3791 substr(addr.address_line1 ,1,30),
3792 substr(addr.town_or_city,1,30),
3793 substr(addr.region_1 ,1,20),
3794 substr(addr.region_2 ,1,5),
3795 substr(addr.postal_code ,1,10)
3796 from per_addresses addr,
3797 per_all_people_f ppf
3798 where addr.person_id = ppf.person_id
3799 and ppf.person_id in
3800 (select distinct person_id
3801 from per_assignments_f paf,
3802 pay_us_emp_city_tax_rules_f pctr
3803 where ( ( pctr.STATE_CODE = '26'
3804 and pctr.county_code = '510'
3805 and pctr.city_code = '1270')
3806 or ( pctr.state_code = '21'
3807 and pctr.county_code = '510'
3808 and pctr.city_code = '0040')
3809 )
3810 and pctr.assignment_id = paf.assignment_id )
3811 and addr.postal_code in
3812 ( '63142',
3813 '63148',
3814 '63149',
3815 '63152',
3816 '63153',
3817 '63154',
3818 '63159',
3819 '63161',
3820 '63162',
3821 '63165',
3822 '63168',
3823 '63170',
3824 '63172',
3825 '63173',
3826 '63174',
3827 '63175',
3828 '63176',
3829 '63181',
3830 '63183',
3831 '63184',
3832 '63185',
3833 '63186',
3834 '63187',
3835 '63189',
3836 '63191',
3837 '63192',
3838 '63193',
3839 '63194',
3840 '21232',
3841 '21238',
3842 '21242',
3843 '21243',
3844 '21245',
3845 '21246',
3846 '21247',
3847 '21248',
3848 '21249',
3849 '21253',
3850 '21254',
3851 '21255',
3852 '21256',
3853 '21257',
3854 '21258',
3855 '21259',
3856 '21260',
3857 '21261',
3858 '21262',
3859 '21266',
3860 '21267',
3861 '21269',
3862 '21271',
3863 '21272',
3864 '21276',
3865 '21277',
3866 '21291',
3867 '21292',
3868 '21293',
3869 '21294',
3870 '21295',
3871 '21296',
3872 '21299' );
3873
3874
3875
3876 ln_full_name varchar2(240);
3877 ln_address varchar2(240);
3878 ln_city_name varchar2(30);
3879 ln_county varchar2(120);
3880 ln_state varchar2(120);
3881 ln_zip_code varchar2(30);
3882
3883 lv_header_label VARCHAR2(32000);
3884
3885 lv_data_row VARCHAR2(32000);
3886 lv_data_row1 VARCHAR2(32000);
3887
3888 lv_format1 varchar2(32000);
3889
3890
3891 BEGIN
3892
3893 hr_utility.set_location(gv_package_name || '.report_15', 10);
3894
3895 /****************************************************************
3896 ** Concatnating the second Header Label which includes the User
3897 ** Defined data set so that it is printed at the end of the
3898 ** report.
3899 ****************************************************************/
3900
3901 fnd_file.put_line(fnd_file.output, formated_header_string(
3902 'XV. Home Address ZIP Code Support.'
3903 ,p_output_file_type
3904 ));
3905
3906 fnd_file.put_line(fnd_file.output, formated_header_string(
3907 'ACTION REQUIRED.'
3908 ,p_output_file_type
3909 ));
3910
3911 fnd_file.put_line(fnd_file.output, formated_header_string(
3912 'The following home addresses are using ZIP Codes no longer '
3913 ,p_output_file_type
3914 ));
3915
3916 fnd_file.put_line(fnd_file.output, formated_header_string(
3917 'supported by Vertex. Please review these addresses and adjust'
3918 ,p_output_file_type
3919 ));
3920
3921 fnd_file.put_line(fnd_file.output, formated_header_string(
3922 'their ZIP Codes to supported values.'
3923 ,p_output_file_type
3924 ));
3925
3926
3927 hr_utility.set_location(gv_package_name || '.report_15', 15);
3928 /****************************************************************
3929 ** Print the Header Information. If the format is HTML then open
3930 ** the body and table before printing the header info, otherwise
3931 ** just print the header information.
3932 ****************************************************************/
3933 if p_output_file_type ='HTML' then
3934 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<body>');
3935 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<table border=1>');
3936 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<tr>');
3937 end if;
3938 hr_utility.set_location(gv_package_name || '.report_15', 20);
3939
3940 lv_format1 :=
3941 formated_data_string (p_input_string => 'Full Name'
3942 ,p_bold => 'Y'
3943 ,p_output_file_type => p_output_file_type) ||
3944
3945 formated_data_string (p_input_string => 'Address'
3946 ,p_bold => 'Y'
3947 ,p_output_file_type => p_output_file_type) ||
3948
3949 formated_data_string (p_input_string => 'City Name'
3950 ,p_bold => 'Y'
3951 ,p_output_file_type => p_output_file_type) ||
3952
3953 formated_data_string (p_input_string => 'County'
3954 ,p_bold => 'Y'
3955 ,p_output_file_type => p_output_file_type) ||
3956
3957 formated_data_string (p_input_string => 'State'
3958 ,p_bold => 'Y'
3959 ,p_output_file_type => p_output_file_type) ||
3960
3961 formated_data_string (p_input_string => 'Zipcode'
3962 ,p_bold => 'Y'
3963 ,p_output_file_type => p_output_file_type)
3964 ;
3965
3966 hr_utility.set_location(gv_package_name || '.report_15', 30);
3967
3968
3969 fnd_file.put_line(fnd_file.output, lv_format1);
3970
3971 if p_output_file_type ='HTML' then
3972 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '</tr>');
3973 end if;
3974
3975 hr_utility.set_location(gv_package_name || '.report_15', 40);
3976 /*****************************************************
3977 ** Start of the Data Section of the Report
3978 *****************************************************/
3979
3980 -- HR_UTILITY.TRACE_ON(NULL,'TCL');
3981 open c_cursor( p_process_mode
3982 ,p_geocode_patch_name
3983 );
3984
3985 loop
3986 hr_utility.set_location(gv_package_name || '.report_15', 50);
3987
3988 fetch c_cursor into ln_full_name
3989 ,ln_address
3990 ,ln_city_name
3991 ,ln_county
3992 ,ln_state
3993 ,ln_zip_code;
3994
3995 if c_cursor%notfound then
3996 hr_utility.set_location(gv_package_name || '.report_15', 60);
3997 exit;
3998 end if;
3999
4000
4001 /************************************************************
4002 ** If Assignment Set is used, pick up only those employee
4003 ** assignments which are part of the Assignment Set - STATIC
4004 ** or DYNAMIC.
4005 ************************************************************/
4006 hr_utility.set_location(gv_package_name || '.report_15', 70);
4007
4008 hr_utility.set_location(gv_package_name || '.report_15', 80);
4009 formated_static_data( ln_full_name
4010 ,null
4011 ,ln_address
4012 ,ln_city_name
4013 ,ln_county
4014 ,ln_state
4015 ,ln_zip_code
4016 ,null
4017 ,null
4018 ,p_output_file_type
4019 ,lv_data_row1);
4020
4021 lv_data_row := lv_data_row1;
4022 hr_utility.set_location(gv_package_name || '.report_15', 90);
4023
4024 -- if p_output_file_type ='HTML' then
4025 lv_data_row := '<tr>' || lv_data_row || '</tr>' ;
4026 -- end if;
4027
4028 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, lv_data_row);
4029
4030
4031 /*****************************************************************
4032 ** initialize Data varaibles
4033 *****************************************************************/
4034 lv_data_row := null;
4035 end loop;
4036 close c_cursor;
4037
4038 /*****************************************************
4039 ** Close of the Data Section of the Report
4040 *****************************************************/
4041
4042 if p_output_file_type ='HTML' then
4043 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '</table></body></html>');
4044 end if;
4045
4046 END report_15;
4047
4048
4049 /* report_16 is created to generate the Output corresponding to the City Name
4050 Changes and the details of the Address updated */
4051
4052 PROCEDURE report_16
4053 (p_process_mode in varchar2
4054 ,p_geocode_patch_name in varchar2
4055 ,p_output_file_type in varchar2
4056 )
4057 IS
4058
4059 /*Cursor to get all the City Names for the current patch*/
4060 cursor c_city_name_change (cp_geocode_patch_name in varchar) is
4061 SELECT decode(pumg1.state_code,'70','CA','US') country,
4062 pus.state_code,
4063 pus.state_abbrev,
4064 puc.county_code,
4065 pumg1.old_city_code,
4066 pumg1.city_name old_city_name,
4067 pumg2.city_name new_city_name
4068 FROM pay_us_modified_geocodes pumg1,
4069 pay_us_modified_geocodes pumg2,
4070 pay_us_states pus,
4071 pay_us_counties puc
4072 WHERE pus.state_code = puc.state_code
4073 AND pus.state_code = pumg1.state_code
4074 AND pus.state_code = pumg2.state_code
4075 AND puc.county_code = pumg1.county_code
4076 AND puc.county_code = pumg2.county_code
4077 AND pumg1.patch_name = p_geocode_patch_name
4078 AND pumg1.process_type = 'CY'
4079 AND pumg1.state_code = pumg2.state_code
4080 AND pumg1.county_code = pumg2.county_code
4081 AND pumg1.old_city_code = pumg2.old_city_code
4082 AND pumg1.new_city_code = pumg2.new_city_code
4083 AND pumg1.city_name = SUBSTR(pumg2.patch_name,INSTR(pumg2.patch_name,':')+1)
4084 AND REPLACE(pumg1.patch_name,'GEOCODE_ANNUAL_')
4085 = REPLACE(SUBSTR(pumg2.patch_name,1,INSTR(pumg2.patch_name,':')-1),'CITY_NAME_CHANGE_')
4086 ORDER BY country DESC,
4087 pus.state_code,
4088 puc.county_code,
4089 pumg1.old_city_code;
4090
4091 /************************************************************
4092 ** Cursor to get the employee data.
4093 ************************************************************/
4094 cursor c_person_cursor ( cp_process_mode in varchar
4095 ,cp_geocode_patch_name in varchar
4096 ) is
4097
4098 select /*+ ORDERED
4099 INDEX (PAY_US_GEO_UPDATE PAY_US_GEO_UPDATE_N2)
4100 INDEX (PAY_PATCH_STATUS PAY_PATCH_STATUS_N1) */
4101 distinct substr(ppf.full_name,1,40) ,
4102 ppf.person_id ,
4103 ppf.employee_number,
4104 pgu.new_juri_code,
4105 pumg1.city_name old_city_name,
4106 pumg2.city_name new_city_name,
4107 substr(pgu.description,1,instr(pgu.description,':')-1)
4108 from pay_patch_status pps,
4109 pay_us_geo_update pgu,
4110 per_people_f ppf,
4111 per_addresses pa,
4112 pay_us_modified_geocodes pumg1,
4113 pay_us_modified_geocodes pumg2
4114 where pgu.process_type = 'CY'
4115 and pgu.table_name = 'PER_ADDRESSES'
4116 and ppf.person_id = pgu.person_id
4117 and pa.person_id = ppf.person_id
4118 and decode(substr(pgu.description,1,instr(pgu.description,':')-1),'Taxation Address',pa.add_information18,pa.town_or_city) =
4119 decode(cp_process_mode,'DEBUG',pumg1.city_name,pumg2.city_name)
4120 and pgu.process_mode = cp_process_mode
4121 and pgu.id = pps.id
4122 and pgu.new_juri_code = pumg1.state_code||'-'||
4123 pumg1.county_code||'-'||
4124 pumg1.old_city_code
4125 and pps.patch_name = cp_geocode_patch_name
4126 and pumg1.patch_name = cp_geocode_patch_name
4127 and pumg1.process_type = 'CY'
4128 and pumg1.city_name = substr(pgu.description,instr(pgu.description,':')+1)
4129 and pumg1.state_code = pumg2.state_code
4130 and pumg1.county_code = pumg2.county_code
4131 and pumg1.old_city_code = pumg2.old_city_code
4132 and pumg1.new_city_code = pumg2.new_city_code
4133 and pumg1.city_name = SUBSTR(pumg2.patch_name,INSTR(pumg2.patch_name,':')+1)
4134 and REPLACE(pumg1.patch_name,'GEOCODE_ANNUAL_')
4135 = REPLACE(SUBSTR(pumg2.patch_name,1,INSTR(pumg2.patch_name,':')-1),'CITY_NAME_CHANGE_')
4136 order by ppf.person_id;
4137
4138 /************************************************************
4139 ** Cursor to get the Location data.
4140 ************************************************************/
4141 cursor c_location_cursor ( cp_process_mode in varchar
4142 ,cp_geocode_patch_name in varchar
4143 ) is
4144
4145 select /*+ ORDERED
4146 INDEX (PAY_US_GEO_UPDATE PAY_US_GEO_UPDATE_N2)
4147 INDEX (PAY_PATCH_STATUS PAY_PATCH_STATUS_N1) */
4148 distinct substr(hl.location_code,1,40) ,
4149 hl.location_id ,
4150 pgu.new_juri_code,
4151 pumg1.city_name old_city_name,
4152 pumg2.city_name new_city_name,
4153 substr(pgu.description,1,instr(pgu.description,':')-1)
4154 from pay_patch_status pps,
4155 pay_us_geo_update pgu,
4156 hr_locations_all hl,
4157 pay_us_modified_geocodes pumg1,
4158 pay_us_modified_geocodes pumg2
4159 where pgu.process_type = 'CY'
4160 and pgu.table_name = 'HR_LOCATIONS_ALL'
4161 and hl.location_id = pgu.table_value_id
4162 and pgu.process_mode = cp_process_mode
4163 and pgu.id = pps.id
4164 and pgu.new_juri_code = pumg1.state_code||'-'||
4165 pumg1.county_code||'-'||
4166 pumg1.old_city_code
4167 and pps.patch_name = cp_geocode_patch_name
4168 and pumg1.patch_name = cp_geocode_patch_name
4169 and decode(substr(pgu.description,1,instr(pgu.description,':')-1),'Payroll Tax Address',hl.loc_information18,hl.town_or_city) =
4170 decode(cp_process_mode,'DEBUG',pumg1.city_name,pumg2.city_name)
4171 and pumg1.process_type = 'CY'
4172 and pumg1.city_name = substr(pgu.description,instr(pgu.description,':')+1)
4173 and pumg1.state_code = pumg2.state_code
4174 and pumg1.county_code = pumg2.county_code
4175 and pumg1.old_city_code = pumg2.old_city_code
4176 and pumg1.new_city_code = pumg2.new_city_code
4177 and pumg1.city_name = SUBSTR(pumg2.patch_name,INSTR(pumg2.patch_name,':')+1)
4178 and REPLACE(pumg1.patch_name,'GEOCODE_ANNUAL_')
4179 = REPLACE(SUBSTR(pumg2.patch_name,1,INSTR(pumg2.patch_name,':')-1),'CITY_NAME_CHANGE_')
4180 order by hl.location_id;
4181
4182 /************************************************************
4183 ** Cursor to get the Organization data.
4184 ************************************************************/
4185 cursor c_org_cursor ( cp_process_mode in varchar
4186 ,cp_geocode_patch_name in varchar
4187 ) is
4188 select /*+ ORDERED
4189 INDEX (PAY_US_GEO_UPDATE PAY_US_GEO_UPDATE_N2)
4190 INDEX (PAY_PATCH_STATUS PAY_PATCH_STATUS_N1) */
4191 distinct substr(hou.name,1,40) ,
4192 hoi.organization_id ,
4193 pgu.new_juri_code,
4194 pumg1.city_name old_city_name,
4195 pumg2.city_name new_city_name,
4196 NVL(hoit.displayed_org_information_type,fdfv.descriptive_flex_context_name)||
4197 ', '||fdfc.form_left_prompt
4198 from pay_patch_status pps,
4199 pay_us_geo_update pgu,
4200 hr_organization_information hoi,
4201 hr_organization_units hou,
4202 fnd_descr_flex_contexts_vl fdfv,
4203 fnd_descr_flex_col_usage_vl fdfc,
4204 hr_org_information_types hoit,
4205 pay_us_modified_geocodes pumg1,
4206 pay_us_modified_geocodes pumg2
4207 where pgu.process_type = 'CY'
4208 and pgu.table_name = 'HR_ORGANIZATION_INFORMATION'
4209 and hoi.org_information_id = pgu.table_value_id
4210 and hoi.organization_id = hou.organization_id
4211 and hoi.org_information_context = substr(pgu.description,1,instr(pgu.description,':')-1)
4212 and hoi.org_information_context = fdfv.descriptive_flex_context_code
4213 and fdfv.descriptive_flex_context_code = fdfc.descriptive_flex_context_code
4214 and fdfc.descriptive_flex_context_code = hoit.org_information_type(+)
4215 and fdfc.descriptive_flexfield_name = fdfv.descriptive_flexfield_name
4216 and fdfc.descriptive_flexfield_name = 'Org Developer DF'
4217 and fdfc.application_column_name = substr(pgu.description,instr(pgu.description,':')+1)
4218 and pgu.process_mode = cp_process_mode
4219 and pgu.id = pps.id
4220 and pgu.new_juri_code = pumg1.state_code||'-'||
4221 pumg1.county_code||'-'||
4222 pumg1.old_city_code
4223 and pps.patch_name = cp_geocode_patch_name
4224 and pumg1.patch_name = cp_geocode_patch_name
4225 and pumg1.process_type = 'CY'
4226 and pumg1.state_code = pumg2.state_code
4227 and pumg1.county_code = pumg2.county_code
4228 and pumg1.old_city_code = pumg2.old_city_code
4229 and pumg1.new_city_code = pumg2.new_city_code
4230 and pumg1.city_name = SUBSTR(pumg2.patch_name,INSTR(pumg2.patch_name,':')+1)
4231 and REPLACE(pumg1.patch_name,'GEOCODE_ANNUAL_')
4232 = REPLACE(SUBSTR(pumg2.patch_name,1,INSTR(pumg2.patch_name,':')-1),'CITY_NAME_CHANGE_')
4233 order by hoi.organization_id;
4234
4235 ln_city_name_change c_city_name_change%ROWTYPE;
4236
4237 ln_full_name per_people_f.full_name%TYPE;
4238 ln_person_id per_people_f.person_id%TYPE;
4239 ln_employee_number per_people_f.employee_number%TYPE;
4240 ln_jurisdiction_code pay_us_geo_update.new_juri_code%TYPE;
4241 ln_old_city_name pay_us_modified_geocodes.city_name%TYPE;
4242 ln_new_city_name pay_us_modified_geocodes.city_name%TYPE;
4243 ln_update_description pay_us_geo_update.description%TYPE;
4244
4245 ln_location_name hr_locations_all.location_code%TYPE;
4246 ln_location_id hr_locations_all.location_id%TYPE;
4247
4248 ln_organization_name hr_organization_units.name%TYPE;
4249 ln_organization_id hr_organization_units.organization_id%TYPE;
4250
4251 lv_header_label VARCHAR2(32000);
4252
4253 lv_data_row VARCHAR2(32000);
4254 lv_data_row1 VARCHAR2(32000);
4255
4256 lv_format1 varchar2(32000);
4257
4258 BEGIN
4259
4260 hr_utility.set_location(gv_package_name || '.report_16', 10);
4261
4262 fnd_file.put_line(fnd_file.output, formated_header_string(
4263 'XVI. City Name Change'
4264 ,p_output_file_type
4265 ));
4266
4267 fnd_file.put_line(fnd_file.output, formated_header_string(
4268 'NO ACTION IS REQUIRED. This is for information ONLY.'
4269 ,p_output_file_type
4270 ));
4271
4272 fnd_file.put_line(fnd_file.output, formated_header_string(
4273 'The following are the City Name changes.'
4274 ,p_output_file_type
4275 ));
4276
4277 hr_utility.set_location(gv_package_name || '.report_16', 15);
4278
4279 /*Start of City Name change details section */
4280
4281 if p_output_file_type ='HTML' then
4282 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<body>');
4283 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<table border=1>');
4284 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<tr>');
4285 end if;
4286
4287 hr_utility.set_location(gv_package_name || '.report_16', 20);
4288
4289 lv_format1 :=
4290 formated_data_string (p_input_string => 'Country'
4291 ,p_bold => 'Y'
4292 ,p_output_file_type => p_output_file_type) ||
4293
4294 formated_data_string (p_input_string => 'State Code'
4295 ,p_bold => 'Y'
4296 ,p_output_file_type => p_output_file_type) ||
4297
4298 formated_data_string (p_input_string => 'State Abbreviation'
4299 ,p_bold => 'Y'
4300 ,p_output_file_type => p_output_file_type) ||
4301
4302 formated_data_string (p_input_string => 'County Code'
4303 ,p_bold => 'Y'
4304 ,p_output_file_type => p_output_file_type) ||
4305
4306 formated_data_string (p_input_string => 'City Code'
4307 ,p_bold => 'Y'
4308 ,p_output_file_type => p_output_file_type) ||
4309
4310 formated_data_string (p_input_string => 'Old City Name'
4311 ,p_bold => 'Y'
4312 ,p_output_file_type => p_output_file_type) ||
4313
4314 formated_data_string (p_input_string => 'New City Name'
4315 ,p_bold => 'Y'
4316 ,p_output_file_type => p_output_file_type)
4317 ;
4318
4319 hr_utility.set_location(gv_package_name || '.report_16', 25);
4320
4321 fnd_file.put_line(fnd_file.output, lv_format1);
4322
4323 if p_output_file_type ='HTML' then
4324 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '</tr>');
4325 end if;
4326
4327 /*****************************************************
4328 ** Start of the Data Section
4329 *****************************************************/
4330
4331 hr_utility.set_location(gv_package_name || '.report_16', 30);
4332
4333 open c_city_name_change( p_geocode_patch_name);
4334
4335 loop
4336
4337 hr_utility.set_location(gv_package_name || '.report_16', 35);
4338
4339 fetch c_city_name_change into ln_city_name_change;
4340
4341 if c_city_name_change%notfound then
4342 hr_utility.set_location(gv_package_name || '.report_16', 40);
4343 exit;
4344 end if;
4345
4346 hr_utility.set_location(gv_package_name || '.report_16', 50);
4347
4348 formated_static_data3( ln_city_name_change.country
4349 ,ln_city_name_change.state_code
4350 ,ln_city_name_change.state_abbrev
4351 ,ln_city_name_change.county_code
4352 ,ln_city_name_change.old_city_code
4353 ,ln_city_name_change.old_city_name
4354 ,ln_city_name_change.new_city_name
4355 ,p_output_file_type
4356 ,lv_data_row1);
4357
4358 lv_data_row := lv_data_row1;
4359
4360 hr_utility.set_location(gv_package_name || '.report_16', 60);
4361
4362 lv_data_row := '<tr>' || lv_data_row || '</tr>' ;
4363
4364 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, lv_data_row);
4365
4366 lv_data_row := null;
4367
4368 end loop;
4369
4370 close c_city_name_change;
4371
4372 /*****************************************************
4373 ** Close of the Data Section
4374 *****************************************************/
4375
4376 if p_output_file_type ='HTML' then
4377 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '</table></body></html>');
4378 end if;
4379
4380 /* End of City Name change details section */
4381
4382 fnd_file.put_line(fnd_file.output, formated_header_string(
4383 'The following are the Person Addresses updated for City Name changes.'
4384 ,p_output_file_type
4385 ));
4386
4387 /* Start of Person Address details updated section */
4388
4389 if p_output_file_type ='HTML' then
4390 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<body>');
4391 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<table border=1>');
4392 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<tr>');
4393 end if;
4394
4395 hr_utility.set_location(gv_package_name || '.report_16', 70);
4396
4397 lv_format1 :=
4398 formated_data_string (p_input_string => 'Full Name'
4399 ,p_bold => 'Y'
4400 ,p_output_file_type => p_output_file_type) ||
4401
4402 formated_data_string (p_input_string => 'Person ID'
4403 ,p_bold => 'Y'
4404 ,p_output_file_type => p_output_file_type) ||
4405
4406 formated_data_string (p_input_string => 'Employee Number'
4407 ,p_bold => 'Y'
4408 ,p_output_file_type => p_output_file_type) ||
4409
4410 formated_data_string (p_input_string => 'Jurisdiction Code'
4411 ,p_bold => 'Y'
4412 ,p_output_file_type => p_output_file_type) ||
4413
4414 formated_data_string (p_input_string => 'Old City Name'
4415 ,p_bold => 'Y'
4416 ,p_output_file_type => p_output_file_type) ||
4417
4418 formated_data_string (p_input_string => 'New City Name'
4419 ,p_bold => 'Y'
4420 ,p_output_file_type => p_output_file_type) ||
4421
4422 formated_data_string (p_input_string => 'Updated Address Type'
4423 ,p_bold => 'Y'
4424 ,p_output_file_type => p_output_file_type)
4425 ;
4426
4427 hr_utility.set_location(gv_package_name || '.report_16', 80);
4428
4429 fnd_file.put_line(fnd_file.output, lv_format1);
4430
4431 if p_output_file_type ='HTML' then
4432 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '</tr>');
4433 end if;
4434
4435 hr_utility.set_location(gv_package_name || '.report_16', 90);
4436
4437 /*****************************************************
4438 ** Start of the Data Section
4439 *****************************************************/
4440
4441 open c_person_cursor( p_process_mode
4442 ,p_geocode_patch_name
4443 );
4444
4445 loop
4446 hr_utility.set_location(gv_package_name || '.report_16', 100);
4447
4448 fetch c_person_cursor into ln_full_name
4449 ,ln_person_id
4450 ,ln_employee_number
4451 ,ln_jurisdiction_code
4452 ,ln_old_city_name
4453 ,ln_new_city_name
4454 ,ln_update_description;
4455
4456 if c_person_cursor%notfound then
4457 hr_utility.set_location(gv_package_name || '.report_16', 110);
4458 exit;
4459 end if;
4460
4461 hr_utility.set_location(gv_package_name || '.report_16', 120);
4462
4463 formated_static_data4( ln_full_name
4464 ,ln_person_id
4465 ,ln_employee_number
4466 ,ln_jurisdiction_code
4467 ,ln_old_city_name
4468 ,ln_new_city_name
4469 ,ln_update_description
4470 ,p_output_file_type
4471 ,lv_data_row1);
4472
4473 lv_data_row := lv_data_row1;
4474
4475 hr_utility.set_location(gv_package_name || '.report_16', 130);
4476
4477 lv_data_row := '<tr>' || lv_data_row || '</tr>' ;
4478
4479 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, lv_data_row);
4480
4481 lv_data_row := null;
4482
4483 end loop;
4484
4485 close c_person_cursor;
4486
4487 /*****************************************************
4488 ** Close of the Data Section
4489 *****************************************************/
4490
4491 if p_output_file_type ='HTML' then
4492 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '</table></body></html>');
4493 end if;
4494
4495 /* End of Person Address details updated section */
4496
4497 fnd_file.put_line(fnd_file.output, formated_header_string(
4498 'The following are the Location Addresses updated for City Name changes.'
4499 ,p_output_file_type
4500 ));
4501
4502 /* Start of Location details updated section */
4503
4504 if p_output_file_type ='HTML' then
4505 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<body>');
4506 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<table border=1>');
4507 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<tr>');
4508 end if;
4509
4510 hr_utility.set_location(gv_package_name || '.report_16', 140);
4511
4512 lv_format1 :=
4513 formated_data_string (p_input_string => 'Location Name'
4514 ,p_bold => 'Y'
4515 ,p_output_file_type => p_output_file_type) ||
4516
4517 formated_data_string (p_input_string => 'Location ID'
4518 ,p_bold => 'Y'
4519 ,p_output_file_type => p_output_file_type) ||
4520
4521 formated_data_string (p_input_string => 'Jurisdiction Code'
4522 ,p_bold => 'Y'
4523 ,p_output_file_type => p_output_file_type) ||
4524
4525 formated_data_string (p_input_string => 'Old City Name'
4526 ,p_bold => 'Y'
4527 ,p_output_file_type => p_output_file_type) ||
4528
4529 formated_data_string (p_input_string => 'New City Name'
4530 ,p_bold => 'Y'
4531 ,p_output_file_type => p_output_file_type) ||
4532
4533 formated_data_string (p_input_string => 'Updated Address Type'
4534 ,p_bold => 'Y'
4535 ,p_output_file_type => p_output_file_type)
4536 ;
4537
4538 hr_utility.set_location(gv_package_name || '.report_16', 150);
4539
4540 fnd_file.put_line(fnd_file.output, lv_format1);
4541
4542 if p_output_file_type ='HTML' then
4543 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '</tr>');
4544 end if;
4545
4546 hr_utility.set_location(gv_package_name || '.report_16', 160);
4547
4548 /*****************************************************
4549 ** Start of the Data Section
4550 *****************************************************/
4551
4552 open c_location_cursor( p_process_mode
4553 ,p_geocode_patch_name
4554 );
4555
4556 loop
4557 hr_utility.set_location(gv_package_name || '.report_16', 170);
4558
4559 fetch c_location_cursor into ln_location_name
4560 ,ln_location_id
4561 ,ln_jurisdiction_code
4562 ,ln_old_city_name
4563 ,ln_new_city_name
4564 ,ln_update_description;
4565
4566 if c_location_cursor%notfound then
4567 hr_utility.set_location(gv_package_name || '.report_16', 180);
4568 exit;
4569 end if;
4570
4571 hr_utility.set_location(gv_package_name || '.report_16', 190);
4572
4573 formated_static_data5( ln_location_name
4574 ,ln_location_id
4575 ,ln_jurisdiction_code
4576 ,ln_old_city_name
4577 ,ln_new_city_name
4578 ,ln_update_description
4579 ,p_output_file_type
4580 ,lv_data_row1);
4581
4582 lv_data_row := lv_data_row1;
4583
4584 hr_utility.set_location(gv_package_name || '.report_16', 200);
4585
4586 lv_data_row := '<tr>' || lv_data_row || '</tr>' ;
4587
4588 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, lv_data_row);
4589
4590 lv_data_row := null;
4591
4592 end loop;
4593
4594 close c_location_cursor;
4595
4596 /*****************************************************
4597 ** Close of the Data Section
4598 *****************************************************/
4599
4600 if p_output_file_type ='HTML' then
4601 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '</table></body></html>');
4602 end if;
4603
4604 /* End of Location details updated section */
4605
4606 fnd_file.put_line(fnd_file.output, formated_header_string(
4607 'The following are the Organization Information Details updated for City Name changes.'
4608 ,p_output_file_type
4609 ));
4610
4611 /* Start of Organization Information updated section */
4612
4613 if p_output_file_type ='HTML' then
4614 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<body>');
4615 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<table border=1>');
4616 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<tr>');
4617 end if;
4618
4619 hr_utility.set_location(gv_package_name || '.report_16', 210);
4620
4621 lv_format1 :=
4622 formated_data_string (p_input_string => 'Organization Name'
4623 ,p_bold => 'Y'
4624 ,p_output_file_type => p_output_file_type) ||
4625
4626 formated_data_string (p_input_string => 'Organization ID'
4627 ,p_bold => 'Y'
4628 ,p_output_file_type => p_output_file_type) ||
4629
4630 formated_data_string (p_input_string => 'Jurisdiction Code'
4631 ,p_bold => 'Y'
4632 ,p_output_file_type => p_output_file_type) ||
4633
4634 formated_data_string (p_input_string => 'Old City Name'
4635 ,p_bold => 'Y'
4636 ,p_output_file_type => p_output_file_type) ||
4637
4638 formated_data_string (p_input_string => 'New City Name'
4639 ,p_bold => 'Y'
4640 ,p_output_file_type => p_output_file_type) ||
4641
4642 formated_data_string (p_input_string => 'Updated Information'
4643 ,p_bold => 'Y'
4644 ,p_output_file_type => p_output_file_type)
4645 ;
4646
4647 hr_utility.set_location(gv_package_name || '.report_16', 220);
4648
4649 fnd_file.put_line(fnd_file.output, lv_format1);
4650
4651 if p_output_file_type ='HTML' then
4652 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '</tr>');
4653 end if;
4654
4655 hr_utility.set_location(gv_package_name || '.report_16', 230);
4656
4657 /*****************************************************
4658 ** Start of the Data Section
4659 *****************************************************/
4660
4661 open c_org_cursor( p_process_mode
4662 ,p_geocode_patch_name
4663 );
4664
4665 loop
4666 hr_utility.set_location(gv_package_name || '.report_16', 240);
4667
4668 fetch c_org_cursor into ln_organization_name
4669 ,ln_organization_id
4670 ,ln_jurisdiction_code
4671 ,ln_old_city_name
4672 ,ln_new_city_name
4673 ,ln_update_description;
4674
4675 if c_org_cursor%notfound then
4676 hr_utility.set_location(gv_package_name || '.report_16', 250);
4677 exit;
4678 end if;
4679
4680 hr_utility.set_location(gv_package_name || '.report_16', 260);
4681
4682 formated_static_data6( ln_organization_name
4683 ,ln_organization_id
4684 ,ln_jurisdiction_code
4685 ,ln_old_city_name
4686 ,ln_new_city_name
4687 ,ln_update_description
4688 ,p_output_file_type
4689 ,lv_data_row1);
4690
4691 lv_data_row := lv_data_row1;
4692
4693 hr_utility.set_location(gv_package_name || '.report_16', 270);
4694
4695 lv_data_row := '<tr>' || lv_data_row || '</tr>' ;
4696
4697 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, lv_data_row);
4698
4699 lv_data_row := null;
4700
4701 end loop;
4702
4703 close c_org_cursor;
4704
4705 /*****************************************************
4706 ** Close of the Data Section
4707 *****************************************************/
4708
4709 if p_output_file_type ='HTML' then
4710 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '</table></body></html>');
4711 end if;
4712
4713 /* End of Organization Information updated section */
4714
4715 END report_16;
4716
4717 /*****************************************************************
4718 ** This is the main procedure which is called from the Concurrent
4719 ** Request. All the paramaters are passed based on which it will
4720 ** either print a CSV format or an HTML format file.
4721 *****************************************************************/
4722 PROCEDURE extract_data
4723 (errbuf out nocopy varchar2
4724 ,retcode out nocopy number
4725 ,p_process_mode in varchar2
4726 ,p_geocode_patch_name in varchar2
4727
4728 )
4729 IS
4730
4731 lv_output_file_type varchar2(4);
4732
4733 BEGIN
4734
4735 lv_output_file_type := 'HTML';
4736
4737 hr_utility.set_location(gv_package_name || '.extract_data', 10);
4738
4739 report_1 ( p_process_mode => p_process_mode
4740 ,p_geocode_patch_name => p_geocode_patch_name
4741 ,p_output_file_type => lv_output_file_type);
4742
4743 report_2 ( p_process_mode => p_process_mode
4744 ,p_geocode_patch_name => p_geocode_patch_name
4745 ,p_output_file_type => lv_output_file_type);
4746
4747 report_3 ( p_process_mode => p_process_mode
4748 ,p_geocode_patch_name => p_geocode_patch_name
4749 ,p_output_file_type => lv_output_file_type);
4750
4751 report_4 ( p_process_mode => p_process_mode
4752 ,p_geocode_patch_name => p_geocode_patch_name
4753 ,p_output_file_type => lv_output_file_type);
4754
4755 report_5 ( p_process_mode => p_process_mode
4756 ,p_geocode_patch_name => p_geocode_patch_name
4757 ,p_output_file_type => lv_output_file_type);
4758
4759 report_6 ( p_process_mode => p_process_mode
4760 ,p_geocode_patch_name => p_geocode_patch_name
4761 ,p_output_file_type => lv_output_file_type);
4762
4763 report_7 ( p_process_mode => p_process_mode
4764 ,p_geocode_patch_name => p_geocode_patch_name
4765 ,p_output_file_type => lv_output_file_type);
4766
4767 report_8 ( p_process_mode => p_process_mode
4768 ,p_geocode_patch_name => p_geocode_patch_name
4769 ,p_output_file_type => lv_output_file_type);
4770
4771 report_9 ( p_process_mode => p_process_mode
4772 ,p_geocode_patch_name => p_geocode_patch_name
4773 ,p_output_file_type => lv_output_file_type);
4774
4775 report_10 ( p_process_mode => p_process_mode
4776 ,p_geocode_patch_name => p_geocode_patch_name
4777 ,p_output_file_type => lv_output_file_type);
4778
4779 report_11 ( p_process_mode => p_process_mode
4780 ,p_geocode_patch_name => p_geocode_patch_name
4781 ,p_output_file_type => lv_output_file_type);
4782
4783 report_12 ( p_process_mode => p_process_mode
4784 ,p_geocode_patch_name => p_geocode_patch_name
4785 ,p_output_file_type => lv_output_file_type);
4786
4787 report_13 ( p_process_mode => p_process_mode
4788 ,p_geocode_patch_name => p_geocode_patch_name
4789 ,p_output_file_type => lv_output_file_type);
4790
4791 report_14 ( p_process_mode => p_process_mode
4792 ,p_geocode_patch_name => p_geocode_patch_name
4793 ,p_output_file_type => lv_output_file_type);
4794
4795 report_15 ( p_process_mode => p_process_mode
4796 ,p_geocode_patch_name => p_geocode_patch_name
4797 ,p_output_file_type => lv_output_file_type);
4798
4799 /* Added for Bug#14314081 */
4800
4801 report_16 ( p_process_mode => p_process_mode
4802 ,p_geocode_patch_name => p_geocode_patch_name
4803 ,p_output_file_type => lv_output_file_type);
4804
4805 /* End of changes for Bug#14314081 */
4806
4807 hr_utility.trace('Concurrent Request ID = ' || FND_GLOBAL.CONC_REQUEST_ID);
4808
4809 END extract_data;
4810
4811 /* Added for Bug#14314081 to generate the Output related to City Name changes.
4812 This is required to generate the Output in event of "Geocode Upgrade Concurrent
4813 Program" getting skipped due to absence of Assignments associated with the
4814 Jurisdiction Code changes */
4815
4816 PROCEDURE city_name_change_report
4817 (p_call varchar2,
4818 p_patch_name varchar2,
4819 p_mode varchar2,
4820 p_geo_phase_id pay_us_geo_update.id%TYPE)
4821
4822 IS
4823
4824 lv_output_file_type varchar2(4);
4825 lv_patch_name pay_patch_status.patch_name%TYPE;
4826
4827 BEGIN
4828
4829 lv_output_file_type := 'HTML';
4830
4831 SELECT patch_name
4832 INTO lv_patch_name
4833 FROM pay_patch_status
4834 WHERE id = p_geo_phase_id;
4835
4836 fnd_file.put_line(fnd_file.output, formated_header_string(
4837 'Patch Name : '||lv_patch_name
4838 ,lv_output_file_type
4839 ));
4840
4841 fnd_file.put_line(fnd_file.output, formated_header_string(
4842 'Child Request "Geocode Upgrade Concurrent Program" not submitted for '||lv_patch_name
4843 ,lv_output_file_type
4844 ));
4845
4846 fnd_file.put_line(fnd_file.output, formated_header_string(
4847 'Generating Output for City Name change details in main "Geocode Upgrade Manager" Output'
4848 ,lv_output_file_type
4849 ));
4850
4851 report_16 ( p_process_mode => p_mode
4852 ,p_geocode_patch_name => lv_patch_name
4853 ,p_output_file_type => lv_output_file_type);
4854
4855 END city_name_change_report;
4856
4857 --begin
4858 --hr_utility.trace_on(null, 'ELE');
4859 end pay_us_geocode_report_pkg;