[Home] [Help]
PACKAGE BODY: APPS.PAY_US_USER_CITY_UPGRADE_PKG
Source
1 PACKAGE BODY pay_us_user_city_upgrade_pkg AS
2 /*$Header: pyususru.pkb 120.0.12020000.1 2012/07/17 04:15:57 emunisek noship $*/
3
4 PROCEDURE range_cursor (pactid IN NUMBER,
5 sqlstr OUT NOCOPY VARCHAR2) IS
6 --
7 --
8 l_count NUMBER;
9 l_upgrade_needed VARCHAR2(2);
10 l_id pay_patch_status.id%TYPE;
11 l_patch_name pay_patch_status.patch_name%TYPE;
12 l_applied_date DATE;
13 l_mode VARCHAR2(8);
14
15 BEGIN
16
17 hr_utility.trace('Entered pay_us_user_city_upgrade_pkg.range_cursor');
18
19 /*Range cursor will be determined based on the necessity for upgrade*/
20
21 hr_utility.trace('Check for the number of User Defined Cities Upgrades');
22
23 l_upgrade_needed := 'Y';
24
25 SELECT DECODE(count(0),0,'N','Y')
26 INTO l_upgrade_needed
27 FROM pay_us_geo_update pugu,
28 pay_patch_status pps
29 WHERE pps.patch_name = 'USER_CITY_UPGRADE'
30 AND pps.phase = 'TO_BE_UPDATED'
31 AND pps.process_type = 'USER_CITY_UPGRADE'
32 AND pps.legislation_code = 'US'
33 AND pps.id = pugu.id
34 AND pugu.process_date = pps.applied_date
35 AND pugu.process_type = pps.process_type
36 AND pugu.process_mode = pps.phase;
37
38 hr_utility.trace('Upgrade Needed : '||l_upgrade_needed);
39
40 IF l_upgrade_needed = 'Y' THEN
41
42 hr_utility.trace('User Defined City changes entries found for Upgrade');
43 hr_utility.trace('Processing required');
44
45 fnd_file.put_line(fnd_file.log,'User Defined City changes entries found for Upgrade');
46 fnd_file.put_line(fnd_file.log,'Processing required');
47
48 hr_utility.trace('Start of Initial Setup');
49
50 /*Get the MODE of Execution for the User Defined City Upgrade Process*/
51
52 SELECT pay_us_geo_upd_pkg.get_parameter('MODE',ppa.legislative_parameters)
53 INTO l_mode
54 FROM pay_payroll_actions ppa
55 WHERE ppa.payroll_action_id = pactid;
56
57 SELECT pay_patch_status_s.NEXTVAL
58 INTO l_id FROM dual;
59
60 l_applied_date := trunc(sysdate);
61
62 l_patch_name := 'USER_CITY_UPGRADE_'||TO_CHAR(sysdate,'DDMONYYYY');
63
64 INSERT INTO pay_patch_status
65 (id,
66 patch_number,
67 patch_name,
68 phase,
69 process_type,
70 applied_date,
71 status,
72 description,
73 legislation_code)
74 VALUES(l_id,
75 '1111111',
76 l_patch_name,
77 'UPDATED',
78 'USER_CITY_UPGRADE',
79 l_applied_date,
80 'P',
81 l_mode,
82 'US');
83
84 INSERT INTO pay_us_modified_geocodes
85 (city_name,
86 state_code,
87 county_code,
88 new_city_code,
89 old_city_code,
90 primary_flag,
91 process_type,
92 patch_name,
93 new_county_code)
94 SELECT substr(pugu.description,3),
95 substr(pugu.old_juri_code,1,2),
96 substr(pugu.old_juri_code,4,3),
97 substr(pugu.new_juri_code,8,4),
98 substr(pugu.old_juri_code,8,4),
99 'N',
100 'D',
101 l_patch_name,
102 NULL
103 FROM pay_us_geo_update pugu,
104 pay_patch_status pps
105 WHERE pps.patch_name = 'USER_CITY_UPGRADE'
106 AND pps.phase = 'TO_BE_UPDATED'
107 AND pps.process_type = 'USER_CITY_UPGRADE'
108 AND pps.legislation_code = 'US'
109 AND pps.id = pugu.id
110 AND pugu.process_date = pps.applied_date
111 AND pugu.process_type = pps.process_type
112 AND pugu.process_mode = pps.phase
113 AND NOT EXISTS
114 (SELECT NULL
115 FROM pay_us_modified_geocodes pumg
116 WHERE pumg.city_name = substr(pugu.description,3)
117 AND pumg.state_code = substr(pugu.old_juri_code,1,2)
118 AND pumg.county_code = substr(pugu.old_juri_code,4,3)
119 AND pumg.new_city_code = substr(pugu.new_juri_code,8,4)
120 AND pumg.old_city_code = substr(pugu.old_juri_code,8,4)
121 AND pumg.process_type = 'D'
122 AND pumg.patch_name = l_patch_name);
123
124 hr_utility.trace('Created PAY_PATCH_STATUS Entry with ID :'||l_id);
125 fnd_file.put_line(fnd_file.log,'Created PAY_PATCH_STATUS Entry with ID :'||l_id);
126
127 sqlstr := ' SELECT DISTINCT paf.person_id
128 FROM per_all_assignments_f paf,
129 pay_us_emp_city_tax_rules_f pect,
130 pay_us_geo_update pugu,
131 pay_patch_status pps
132 WHERE pect.assignment_id = paf.assignment_id
133 AND substr(pugu.old_juri_code,1,2) = pect.state_code
134 AND substr(pugu.old_juri_code,4,3) = pect.county_code
135 AND substr(pugu.old_juri_code,8,4) = pect.city_code
136 AND :pactid IS NOT NULL
137 AND pugu.id = pps.id
138 AND pugu.process_date = pps.applied_date
139 AND pugu.process_type = pps.process_type
140 AND pugu.process_mode = pps.phase
141 AND pps.patch_name = ''USER_CITY_UPGRADE''
142 AND pps.phase = ''TO_BE_UPDATED''
143 AND pps.legislation_code = ''US''
144 AND pps.process_type = ''USER_CITY_UPGRADE''
145 ORDER BY paf.person_id';
146
147 ELSE
148
149 hr_utility.trace('No User Defined City changes entries for Upgrade');
150 hr_utility.trace('No Processing required');
151
152 fnd_file.put_line(fnd_file.log,'No User Defined City changes entries for Upgrade');
153 fnd_file.put_line(fnd_file.log,'No Processing required');
154
155 sqlstr := ' SELECT distinct paf.person_id
156 FROM per_all_assignments_f paf
157 WHERE 1=2
158 AND :pactid IS NOT NULL';
159
160 END IF; /*l_upgrade_needed IF*/
161
162 hr_utility.trace('Range Cursor is');
163 hr_utility.trace(sqlstr);
164
165 hr_utility.trace('Leaving pay_us_user_city_upgrade_pkg.range_cursor');
166
167 EXCEPTION
168
169 WHEN OTHERS THEN
170
171 fnd_file.put_line(fnd_file.log,'Exception raised in pay_us_user_city_upgrade_pkg.range_cursor');
172 fnd_file.put_line(fnd_file.log,'Error '||sqlerrm);
173 hr_utility.raise_error;
174
175 END range_cursor;
176
177 PROCEDURE action_creation (pactid IN NUMBER,
178 stperson IN NUMBER,
179 endperson IN NUMBER,
180 chunk IN NUMBER) IS
181
182 CURSOR c_get_assignments
183 (
184 p_stperson NUMBER,
185 p_endperson NUMBER
186 ) IS
187 SELECT DISTINCT ectr.assignment_id
188 FROM per_all_assignments_f paf,
189 pay_us_emp_city_tax_rules_f ectr,
190 pay_us_geo_update pugu,
191 pay_patch_status pps
192 WHERE pps.patch_name = 'USER_CITY_UPGRADE'
193 AND pps.phase = 'TO_BE_UPDATED'
194 AND pps.process_type = 'USER_CITY_UPGRADE'
195 AND pps.legislation_code = 'US'
196 AND pps.id = pugu.id
197 AND pugu.process_date = pps.applied_date
198 AND pugu.process_type = pps.process_type
199 AND pugu.process_mode = pps.phase
200 AND ectr.state_code = substr(pugu.old_juri_code,1,2)
201 AND ectr.county_code = substr(pugu.old_juri_code,4,3)
202 AND ectr.city_code = substr(pugu.old_juri_code,8,4)
203 AND ectr.assignment_id = paf.assignment_id
204 AND paf.person_id BETWEEN p_stperson AND p_endperson
205 ORDER BY ectr.assignment_id;
206
207 l_assignment_id NUMBER;
208 l_assignment_action_id NUMBER;
209
210 BEGIN
211
212 hr_utility.trace('Entering pay_us_user_city_upgrade_pkg.action_creation');
213 hr_utility.trace('Action Creation called with below details');
214 hr_utility.trace('pactid :'||pactid);
215 hr_utility.trace('stperson :'||stperson);
216 hr_utility.trace('endperson :'||endperson);
217 hr_utility.trace('chunk :'||chunk);
218
219 OPEN c_get_assignments(stperson,endperson);
220 FETCH c_get_assignments INTO l_assignment_id;
221
222 WHILE (c_get_assignments%FOUND)
223
224 LOOP
225
226 SELECT pay_assignment_actions_s.nextval
227 INTO l_assignment_action_id
228 FROM DUAL;
229
230 hr_nonrun_asact.insact(lockingactid => l_assignment_action_id,
231 object_Id => l_assignment_id,
232 pactid => pactid,
233 chunk => chunk,
234 object_type => 'ASG');
235
236 hr_utility.trace('Assignment Action '||l_assignment_action_id||' for Assignment '||l_assignment_id);
237
238 FETCH c_get_assignments INTO l_assignment_id;
239
240 END LOOP;
241
242 CLOSE c_get_assignments;
243
244 hr_utility.trace('Leaving pay_us_user_city_upgrade_pkg.action_creation');
245
246 EXCEPTION
247
248 WHEN OTHERS THEN
249
250 hr_utility.trace('Exception raised in pay_us_user_city_upgrade_pkg.action_creation');
251 fnd_file.put_line(fnd_file.log,'Exception raised in pay_us_user_city_upgrade_pkg.action_creation');
252 fnd_file.put_line(fnd_file.log,sqlerrm);
253 hr_utility.raise_error;
254
255 END action_creation;
256
257 PROCEDURE archive_code (p_xfr_action_id IN NUMBER,
258 p_effective_date IN DATE) IS
259 --
260 --
261 CURSOR c_assignment_id (p_assignment_action_id NUMBER) IS
262 SELECT paa.payroll_action_id,
263 paa.object_id
264 FROM pay_temp_object_actions paa
265 WHERE paa.object_action_id = p_assignment_action_id;
266
267 CURSOR c_patch_details(p_mode VARCHAR2) IS
268 SELECT pps.id,pps.patch_name
269 FROM pay_patch_status pps
270 WHERE pps.patch_name LIKE 'USER_CITY_UPGRADE%'
271 AND pps.legislation_code = 'US'
272 AND pps.status = 'P'
273 AND pps.description = p_mode
274 AND NOT EXISTS
275 (SELECT NULL
276 FROM pay_patch_status pps1
277 WHERE pps1.id > pps.id
278 AND pps1.patch_name LIKE 'USER_CITY_UPGRADE%'
279 AND pps.legislation_code = 'US')
280 ORDER BY pps.id DESC;
281
282 CURSOR c_mode (p_payroll_action_id NUMBER) IS
283 SELECT pay_us_geo_upd_pkg.get_parameter('MODE',PPa.legislative_parameters)
284 FROM pay_payroll_actions ppa
285 WHERE ppa.payroll_action_id = p_payroll_action_id;
286
287 l_payroll_action_id pay_payroll_actions.payroll_action_id%TYPE;
288 l_assignment_id pay_assignment_actions.object_id%TYPE;
289 l_mode VARCHAR2(8);
290 l_id pay_patch_status.id%TYPE;
291 l_patch_name pay_patch_status.patch_name%TYPE;
292
293 BEGIN
294
295 hr_utility.trace('Inside pay_us_user_city_upgrade_pkg.archive_code');
296 hr_utility.trace('for Assignment Action ID : '||p_xfr_action_id);
297
298 OPEN c_assignment_id (p_xfr_action_id);
299 FETCH c_assignment_id into l_payroll_action_id,l_assignment_id;
300 CLOSE c_assignment_id;
301
302 OPEN c_mode(l_payroll_action_id);
303 FETCH c_mode into l_mode;
304 close c_mode;
305
306 OPEN c_patch_details(l_mode);
307 FETCH c_patch_details INTO l_id,l_patch_name;
308 CLOSE c_patch_details;
309
310 hr_utility.trace('Calling Geocode Upgrade for Assignment '||l_assignment_id);
311
312 pay_us_geo_upd_pkg.upgrade_geocodes(l_assignment_id,l_assignment_id,l_id,l_mode,l_patch_name);
313
314 hr_utility.trace('Leaving pay_us_user_city_upgrade_pkg.archive_code');
315 hr_utility.trace('for Assignment Action ID : '||p_xfr_action_id);
316
317 EXCEPTION
318
319 WHEN OTHERS THEN
320
321 fnd_file.put_line(fnd_file.log,'Exception raised in pay_us_user_city_upgrade_pkg.archive_code');
322 fnd_file.put_line(fnd_file.log,'Assignment Action ID : '||p_xfr_action_id);
323 fnd_file.put_line(fnd_file.log,'Assignment ID : '||l_assignment_id);
324 fnd_file.put_line(fnd_file.log,'Error '||sqlerrm);
325 hr_utility.raise_error;
326
327 END archive_code;
328
329 PROCEDURE deinitialization (p_payroll_action_id IN NUMBER) IS
330 --
331 --
332
333 CURSOR get_emp_counts(p_id NUMBER,
334 p_mode VARCHAR2,
335 p_status VARCHAR2,
336 p_process_type VARCHAR2) IS
337 SELECT COUNT(0)
338 FROM (SELECT DISTINCT assignment_id
339 FROM pay_us_geo_update pugu
340 WHERE pugu.id = p_id
341 AND pugu.process_type = p_process_type
342 AND pugu.process_mode = p_mode
343 AND pugu.table_name is null
344 AND pugu.table_value_id is null
345 AND DECODE(pugu.status,'C','C','E')
346 = DECODE(p_status,'ALL',DECODE(pugu.status,'C','C','E'),p_status));
347
348 CURSOR c_mode ( p_payroll_action_id NUMBER) IS
349 SELECT pay_us_geo_upd_pkg.get_parameter('MODE',PPa.legislative_parameters)
350 FROM pay_payroll_actions ppa
351 WHERE ppa.payroll_action_id = p_payroll_action_id;
352
353 CURSOR get_user_city_update_details IS
354 SELECT pugu.old_juri_code,
355 pugu.new_juri_code,
356 substr(pugu.description,3) city_name,
357 substr(pugu.description,1,1) primary_flag
358 FROM pay_us_geo_update pugu,
359 pay_patch_status pps
360 WHERE pps.patch_name = 'USER_CITY_UPGRADE'
361 AND pps.phase = 'TO_BE_UPDATED'
362 AND pps.process_type = 'USER_CITY_UPGRADE'
363 AND pps.legislation_code = 'US'
364 AND pps.id = pugu.id
365 AND pugu.process_date = pps.applied_date
366 AND pugu.process_type = pps.process_type
367 AND pugu.process_mode = pps.phase;
368
369 l_upgrade_needed VARCHAR2(2);
370 l_mode VARCHAR2(8);
371 l_id pay_patch_status.id%TYPE;
372 l_process_type pay_us_geo_update.process_type%TYPE;
373 l_applied_date DATE;
374 l_total_assignments NUMBER;
375 l_success_assignments NUMBER;
376 l_error_assignments NUMBER;
377 l_assignments_to_be_updated NUMBER;
378 l_old_juri_code pay_us_geo_update.old_juri_code%TYPE;
379 l_new_juri_code pay_us_geo_update.new_juri_code%TYPE;
380 l_city_name pay_us_city_names.city_name%TYPE;
381 l_primary_flag pay_us_city_names.primary_flag%TYPE;
382
383 BEGIN
384
385 hr_utility.trace('Entering pay_us_user_city_upgrade_pkg.deinitialization');
386
387 hr_utility.trace('Check for the number of User Defined Cities Upgrades');
388
389 OPEN c_mode(p_payroll_action_id);
390 FETCH c_mode into l_mode;
391 close c_mode;
392
393 l_upgrade_needed := 'Y';
394
395 SELECT DECODE(count(0),0,'N','Y')
396 INTO l_upgrade_needed
397 FROM pay_us_geo_update pugu,
398 pay_patch_status pps
399 WHERE pps.patch_name = 'USER_CITY_UPGRADE'
400 AND pps.phase = 'TO_BE_UPDATED'
401 AND pps.process_type = 'USER_CITY_UPGRADE'
402 AND pps.legislation_code = 'US'
403 AND pps.id = pugu.id
404 AND pugu.process_date = pps.applied_date
405 AND pugu.process_type = pps.process_type
406 AND pugu.process_mode = pps.phase;
407
408 IF l_upgrade_needed = 'Y' THEN
409
410 SELECT id,applied_date
411 INTO l_id,l_applied_date
412 FROM pay_patch_status pps
413 WHERE pps.patch_name LIKE 'USER_CITY_UPGRADE%'
414 AND pps.legislation_code = 'US'
415 AND pps.status = 'P'
416 AND pps.description = l_mode
417 AND NOT EXISTS
418 (SELECT NULL
419 FROM pay_patch_status pps1
420 WHERE pps1.id > pps.id
421 AND pps1.patch_name LIKE 'USER_CITY_UPGRADE%'
422 AND pps1.legislation_code = 'US');
423
424 IF l_mode = 'DEBUG' THEN
425
426 pay_us_user_city_upgrade_pkg.generate_output(l_mode,l_upgrade_needed);
427
428 UPDATE pay_patch_status
429 SET status = 'C'
430 WHERE legislation_code = 'US'
431 AND id = l_id
432 AND status = 'P'
433 AND description = l_mode;
434
435 ELSE
436
437 /*Need to use 'D' as PROCESS_TYPE below because Geocode Process has
438 been using D PROCESS_TYPE for User Defined City Upgrade changes*/
439
440 l_process_type := 'D';
441
442 pay_us_user_city_upgrade_pkg.generate_output(l_mode,l_upgrade_needed);
443
444 OPEN get_emp_counts(l_id,l_mode,'ALL',l_process_type);
445 FETCH get_emp_counts INTO l_total_assignments;
446 CLOSE get_emp_counts;
447
448 OPEN get_emp_counts(l_id,l_mode,'C',l_process_type);
449 FETCH get_emp_counts INTO l_success_assignments;
450 CLOSE get_emp_counts;
451
452 OPEN get_emp_counts(l_id,l_mode,'E',l_process_type);
453 FETCH get_emp_counts INTO l_error_assignments;
454 CLOSE get_emp_counts;
455
456 SELECT count(DISTINCT paf.assignment_id)
457 INTO l_assignments_to_be_updated
458 FROM per_all_assignments_f paf,
459 pay_us_emp_city_tax_rules_f pect,
460 pay_us_geo_update pugu,
461 pay_patch_status pps
462 WHERE pect.assignment_id = paf.assignment_id
463 AND substr(pugu.old_juri_code,1,2) = pect.state_code
464 AND substr(pugu.old_juri_code,4,3) = pect.county_code
465 AND substr(pugu.old_juri_code,8,4) = pect.city_code
466 AND pugu.id = pps.id
467 AND pugu.process_date = pps.applied_date
468 AND pugu.process_type = pps.process_type
469 AND pugu.process_mode = pps.phase
470 AND pps.patch_name = 'USER_CITY_UPGRADE'
471 AND pps.phase = 'TO_BE_UPDATED'
472 AND pps.legislation_code = 'US'
473 AND pps.process_type = 'USER_CITY_UPGRADE';
474
475 IF l_total_assignments = l_success_assignments
476 AND l_assignments_to_be_updated = 0
477 AND l_error_assignments = 0 THEN
478
479 OPEN get_user_city_update_details;
480 FETCH get_user_city_update_details
481 INTO l_old_juri_code,l_new_juri_code,l_city_name,l_primary_flag;
482
483 WHILE (get_user_city_update_details%FOUND)
484 LOOP
485
486 /*Update the City Code and Primary Flag as shipped by Oracle*/
487
488 UPDATE pay_us_city_names
489 SET city_code = substr(l_new_juri_code,8,4),
490 primary_flag = l_primary_flag
491 WHERE state_code = substr(l_old_juri_code,1,2)
492 AND county_code = substr(l_old_juri_code,4,3)
493 AND city_code = substr(l_old_juri_code,8,4)
494 AND city_name = l_city_name;
495
496 /*Remove the Zip Codes present with User City Code if the same
497 Zip Codes exist with Oracle Delivered City code.
498 For Example, User maintained 10001-10003,10005-10007,10008-10010.
499 Oracle delivered 10001-10010. So we can remove the user created
500 records as they are already covered by Oracle delivered range*/
501
502 DELETE FROM pay_us_zip_codes puzc
503 WHERE state_code = substr(l_old_juri_code,1,2)
504 AND county_code = substr(l_old_juri_code,4,3)
505 AND city_code = substr(l_old_juri_code,8,4)
506 AND EXISTS
507 ( SELECT NULL
508 FROM pay_us_zip_codes puzc1
509 WHERE puzc1.state_code = puzc.state_code
510 AND puzc1.county_code = puzc.county_code
511 AND puzc1.city_code = substr(l_new_juri_code,8,4)
512 AND puzc1.zip_start <= puzc.zip_start
513 AND puzc1.zip_end >= puzc.zip_end);
514
515 UPDATE pay_us_zip_codes
516 SET city_code = substr(l_new_juri_code,8,4)
517 WHERE state_code = substr(l_old_juri_code,1,2)
518 AND county_code = substr(l_old_juri_code,4,3)
519 AND city_code = substr(l_old_juri_code,8,4);
520
521 /*Below delete statement was executed earlier. But need to execute
522 this again as User might have created a Zip Code which overlaps
523 the Oracle delivered Zip Codes.
524 For Example, User maintained 20000-20050. Oracle delivered 20000-20010.
525 Earlier delete statement was used to delete records from User Maintained
526 Zip Codes. 20000-20050 will not be deleted. Now as the zip codes are
527 merged, the below statement will remove the 20000-20010*/
528
529 DELETE FROM pay_us_zip_codes puzc
530 WHERE state_code = substr(l_new_juri_code,1,2)
531 AND county_code = substr(l_new_juri_code,4,3)
532 AND city_code = substr(l_new_juri_code,8,4)
533 AND EXISTS
534 ( SELECT NULL
535 FROM pay_us_zip_codes puzc1
536 WHERE puzc1.state_code = puzc.state_code
537 AND puzc1.county_code = puzc.county_code
538 AND puzc1.city_code = puzc.city_code
539 AND puzc1.zip_start <= puzc.zip_start
540 AND puzc1.zip_end >= puzc.zip_end
541 AND puzc1.rowid <> puzc.rowid);
542
543 /*For Partial Overlapped Zip Codes, need to remove the overlapping*/
544
545 UPDATE pay_us_zip_codes puzc
546 SET zip_start = (SELECT LPAD(TO_CHAR(MAX(puzc2.zip_end) + 1),5,'0')
547 FROM pay_us_zip_codes puzc2
548 WHERE puzc2.state_code = puzc.state_code
549 AND puzc2.county_code = puzc.county_code
550 AND puzc2.city_code = puzc.city_code
551 AND puzc2.zip_start < puzc.zip_start
552 AND puzc2.zip_end < puzc.zip_end
553 AND puzc2.zip_end >= puzc.zip_start)
554 WHERE state_code = substr(l_new_juri_code,1,2)
555 AND county_code = substr(l_new_juri_code,4,3)
556 AND city_code = substr(l_new_juri_code,8,4)
557 AND EXISTS
558 ( SELECT NULL
559 FROM pay_us_zip_codes puzc1
560 WHERE puzc1.state_code = puzc.state_code
561 AND puzc1.county_code = puzc.county_code
562 AND puzc1.city_code = puzc.city_code
563 AND puzc1.zip_start < puzc.zip_start
564 AND puzc1.zip_end < puzc.zip_end
565 AND puzc1.zip_end >= puzc.zip_start);
566
567 FETCH get_user_city_update_details
568 INTO l_old_juri_code,l_new_juri_code,l_city_name,l_primary_flag;
569
570 END LOOP;
571
572 CLOSE get_user_city_update_details;
573
574 UPDATE pay_us_geo_update pugu
575 SET process_mode = 'UPDATED'
576 WHERE EXISTS
577 (SELECT NULL
578 FROM pay_patch_status pps
579 WHERE pps.patch_name = 'USER_CITY_UPGRADE'
580 AND pps.phase = 'TO_BE_UPDATED'
581 AND pps.process_type = 'USER_CITY_UPGRADE'
582 AND pps.legislation_code = 'US'
583 AND pps.id = pugu.id
584 AND pugu.process_date = pps.applied_date
585 AND pugu.process_type = pps.process_type
586 AND pugu.process_mode = pps.phase);
587
588 UPDATE pay_patch_status pps
589 SET phase = 'UPDATED'
590 WHERE pps.patch_name = 'USER_CITY_UPGRADE'
591 AND pps.phase = 'TO_BE_UPDATED'
592 AND pps.process_type = 'USER_CITY_UPGRADE'
593 AND pps.legislation_code = 'US';
594
595 UPDATE pay_patch_status
596 SET status = 'C'
597 WHERE legislation_code = 'US'
598 AND id = l_id
599 AND status = 'P'
600 AND description = l_mode;
601
602 ELSE
603
604 UPDATE pay_patch_status
605 SET status = 'E'
606 WHERE legislation_code = 'US'
607 AND id = l_id
608 AND status = 'P'
609 AND description = l_mode;
610
611 fnd_file.put_line(fnd_file.log,'User Defined City Upgrade Process completed in error.');
612 hr_utility.raise_error;
613
614 END IF; /*l_total_assignments = l_success_assignments IF*/
615
616 END IF; /* l_mode IF */
617
618 ELSE
619
620 pay_us_user_city_upgrade_pkg.generate_output(l_mode,l_upgrade_needed);
621
622 END IF; /* l_upgrade_needed IF*/
623
624 hr_utility.trace('Leaving pay_us_user_city_upgrade_pkg.deinitialization');
625
626 EXCEPTION
627
628 WHEN OTHERS THEN
629
630 hr_utility.trace('Exception raised in pay_us_user_city_upgrade_pkg.deinitialization');
631 fnd_file.put_line(fnd_file.log,'Exception raised in pay_us_user_city_upgrade_pkg.deinitialization');
632 fnd_file.put_line(fnd_file.log,sqlerrm);
633 hr_utility.raise_error;
634
635 END deinitialization;
636
637 PROCEDURE generate_output(p_mode VARCHAR2,p_upgrade_needed VARCHAR2) IS
638
639 CURSOR get_user_city_update_details IS
640 SELECT pus.state_name,
641 pus.state_abbrev,
642 puc.county_name,
643 substr(pugu.description,3) city_name,
644 pugu.old_juri_code,
645 pugu.new_juri_code
646 FROM pay_us_geo_update pugu,
647 pay_patch_status pps,
648 pay_us_states pus,
649 pay_us_counties puc
650 WHERE pps.patch_name = 'USER_CITY_UPGRADE'
651 AND pps.phase = 'TO_BE_UPDATED'
652 AND pps.process_type = 'USER_CITY_UPGRADE'
653 AND pps.legislation_code = 'US'
654 AND pps.id = pugu.id
655 AND pugu.process_date = pps.applied_date
656 AND pugu.process_type = pps.process_type
657 AND pugu.process_mode = pps.phase
658 AND pus.state_code = substr(pugu.old_juri_code,1,2)
659 AND pus.state_code = puc.state_code
660 AND puc.county_code = substr(pugu.old_juri_code,4,3)
661 ORDER BY pugu.old_juri_code;
662
663 CURSOR get_succesful_employees(p_id NUMBER,
664 p_mode VARCHAR2) IS
665 SELECT pugu.person_id,
666 pugu.assignment_id,
667 pugu.old_juri_code,
668 pugu.new_juri_code,
669 COUNT(DISTINCT pugu.table_name)
670 FROM pay_us_geo_update pugu
671 WHERE pugu.id = p_id
672 AND pugu.process_type = 'D'
673 AND pugu.process_mode = p_mode
674 AND pugu.table_name IS NOT NULL
675 AND EXISTS
676 (SELECT NULL
677 FROM pay_us_geo_update pugu1
678 WHERE pugu1.id = p_id
679 AND pugu1.process_type = 'D'
680 AND pugu1.process_mode = p_mode
681 AND pugu1.person_id = pugu.person_id
682 AND pugu1.assignment_id = pugu.assignment_id
683 AND pugu1.table_name IS NULL
684 AND pugu1.table_value_id IS NULL
685 AND NVL(pugu1.status,'X') = 'C')
686 GROUP BY pugu.person_id,pugu.assignment_id,
687 pugu.old_juri_code,pugu.new_juri_code
688 HAVING COUNT(*) > 0
689 ORDER BY pugu.person_id,pugu.assignment_id,
690 pugu.old_juri_code,pugu.new_juri_code;
691
692 CURSOR get_emp_details (p_person_id NUMBER) IS
693 SELECT substr(ppf.full_name,1,100)
694 FROM per_all_people_f ppf
695 WHERE ppf.person_id = p_person_id
696 AND NOT EXISTS
697 (
698 SELECT NULL
699 FROM per_all_people_f ppf1
700 WHERE ppf1.person_id =ppf.person_id
701 AND ppf1.effective_end_date > ppf.effective_end_date
702 );
703
704 CURSOR get_updated_tables (p_id NUMBER,
705 p_mode VARCHAR2,
706 p_person_id NUMBER,
707 p_assignment_id NUMBER,
708 p_old_juri_code VARCHAR2,
709 p_new_juri_code VARCHAR2) IS
710 SELECT DISTINCT pugu.table_name
711 FROM pay_us_geo_update pugu
712 WHERE pugu.id = p_id
713 AND pugu.process_type = 'D'
714 AND pugu.process_mode = p_mode
715 AND pugu.person_id = p_person_id
716 AND pugu.assignment_id = p_assignment_id
717 AND pugu.table_name IS NOT NULL
718 AND pugu.old_juri_code = p_old_juri_code
719 AND pugu.new_juri_code = p_new_juri_code
720 ORDER BY pugu.table_name;
721
722 CURSOR get_emp_in_error(p_id NUMBER,p_mode VARCHAR2) IS
723 SELECT DISTINCT
724 substr(ppf.full_name,1,100),
725 pugu.assignment_id
726 FROM pay_us_geo_update pugu,
727 per_all_people_f ppf
728 WHERE pugu.id = p_id
729 AND pugu.process_type = 'D'
730 AND pugu.process_mode = p_mode
731 AND pugu.person_id = ppf.person_id
732 AND pugu.table_name IS NULL
733 AND pugu.table_value_id IS NULL
734 AND NVL(pugu.status,'E') <>'C'
735 AND NOT EXISTS (
736 SELECT NULL
737 FROM per_all_people_f ppf1
738 WHERE ppf1.person_id =ppf.person_id
739 AND ppf1.effective_end_date > ppf.effective_end_date
740 )
741 GROUP BY ppf.full_name,pugu.assignment_id
742 ORDER BY pugu.assignment_id;
743
744 CURSOR get_emp_counts(p_id NUMBER,
745 p_mode VARCHAR2,
746 p_status VARCHAR2,
747 p_process_type VARCHAR2) IS
748 SELECT COUNT(0)
749 FROM (SELECT DISTINCT assignment_id
750 FROM pay_us_geo_update pugu
751 WHERE pugu.id = p_id
752 AND pugu.process_type = p_process_type
753 AND pugu.process_mode = p_mode
754 AND pugu.table_name is null
755 AND pugu.table_value_id is null
756 AND DECODE(pugu.status,'C','C','E')
757 = DECODE(p_status,'ALL',DECODE(pugu.status,'C','C','E'),p_status));
758
759 l_id pay_patch_status.id%TYPE;
760 l_applied_date DATE;
761 l_state_name pay_us_states.state_name%TYPE;
762 l_state_abbrev pay_us_states.state_abbrev%TYPE;
763 l_county_name pay_us_counties.county_name%TYPE;
764 l_city_name pay_us_city_names.city_name%TYPE;
765 l_old_juri_code pay_us_geo_update.old_juri_code%TYPE;
766 l_new_juri_code pay_us_geo_update.new_juri_code%TYPE;
767 l_full_name per_all_people_f.full_name%TYPE;
768 l_assignment_id pay_us_geo_update.assignment_id%TYPE;
769 l_person_id pay_us_geo_update.person_id%TYPE;
770 l_table_name pay_us_geo_update.table_name%TYPE;
771 l_table_count NUMBER;
772 l_process_type VARCHAR2(2) := 'D';
773 l_total_assignments NUMBER;
774 l_success_assignments NUMBER;
775 l_error_assignments NUMBER;
776 l_not_process_assignments NUMBER;
777
778 BEGIN
779
780 hr_utility.trace('Entering pay_us_user_city_upgrade_pkg.generate_output');
781
782 fnd_file.put_line(fnd_file.output,'<HTML><TITLE>User Defined City Upgrade Details</TITLE>');
783 fnd_file.put_line(fnd_file.output,'<HTML><H2><Center>User Defined City Upgrade Details</Center></H2>');
784
785 IF p_upgrade_needed = 'Y' THEN
786
787 SELECT id,applied_date
788 INTO l_id,l_applied_date
789 FROM pay_patch_status pps
790 WHERE pps.patch_name LIKE 'USER_CITY_UPGRADE%'
791 AND pps.legislation_code = 'US'
792 AND pps.status = 'P'
793 AND pps.description = p_mode
794 AND NOT EXISTS
795 (SELECT NULL
796 FROM pay_patch_status pps1
797 WHERE pps1.id > pps.id
798 AND pps1.patch_name LIKE 'USER_CITY_UPGRADE%'
799 AND pps1.legislation_code = 'US');
800
801 /* Start of Employee Update related details printing*/
802
803 fnd_file.put_line(fnd_file.output,'<HTML><P> Process Date : '||to_char(l_applied_date,'DD-MON-YYYY HH:MM:SS')||' </P>');
804 fnd_file.put_line(fnd_file.output,'<HTML><P> Process Mode : '||p_mode||' </P>');
805 fnd_file.put_line(fnd_file.output,'<HTML><H3>List of User Defined Cities that needs upgrade</H3>');
806 fnd_file.put_line(fnd_file.output,'<TABLE cellpadding="2" cellspacing="1" border="2" summary="User Defined Cities that need upgrade">');
807 fnd_file.put_line(fnd_file.output,'<TR><TH>State Name</TH>');
808 fnd_file.put_line(fnd_file.output,'<TH>State Abbrev</TH>');
809 fnd_file.put_line(fnd_file.output,'<TH>County Name</TH>');
810 fnd_file.put_line(fnd_file.output,'<TH>City Name</TH>');
811 fnd_file.put_line(fnd_file.output,'<TH>Old User Defined Code</TH>');
812 fnd_file.put_line(fnd_file.output,'<TH>New Jurisdiction Code</TH>');
813
814 OPEN get_user_city_update_details;
815 FETCH get_user_city_update_details INTO
816 l_state_name,l_state_abbrev,l_county_name,
817 l_city_name,l_old_juri_code,l_new_juri_code;
818
819 WHILE (get_user_city_update_details%FOUND)
820 LOOP
821
822 fnd_file.put_line(fnd_file.output,'<TR><TD>'||l_state_name||'</TD><TD>'||l_state_abbrev||
823 '</TD><TD>'||l_county_name||'</TD><TD>'||l_city_name||
824 '</TD><TD>'||l_old_juri_code||'</TD><TD>'||l_new_juri_code||'</TD></TR>');
825
826 FETCH get_user_city_update_details INTO
827 l_state_name,l_state_abbrev,l_county_name,
828 l_city_name,l_old_juri_code,l_new_juri_code;
829
830 END LOOP;
831
832 CLOSE get_user_city_update_details;
833
834 /* Start of Successful Employees printing section */
835
836 fnd_file.put_line(fnd_file.output,'</TABLE>');
837
838 IF p_mode = 'DEBUG' THEN
839 fnd_file.put_line(fnd_file.output,'<H3>List down employees for whom Jurisdiction code and relevant tables are to be updated </H3>');
840 ELSE
841 fnd_file.put_line(fnd_file.output,'<H3>List down employees for whom Jurisdiction code and relevant tables were updated </H3>');
842 END IF;
843
844 fnd_file.put_line(fnd_file.output,'<HTML><H4>Update Details </H4>');
845 fnd_file.put_line(fnd_file.output,'<TABLE cellpadding="2" cellspacing="1" border="2" summary="Employee Data that needs upgrade">');
846 fnd_file.put_line(fnd_file.output,'<TR><TH colspan=2>Employee Details</TH><TH colspan=2>Jurisdiction Details</TH><TH rowspan=2>Tables Updated</TH>');
847 fnd_file.put_line(fnd_file.output,'<TR><TH>Full Name</TH>');
848 fnd_file.put_line(fnd_file.output,'<TH>Assignment ID</TH>');
849 fnd_file.put_line(fnd_file.output,'<TH>Old Jurisdiction Code</TH>');
850 fnd_file.put_line(fnd_file.output,'<TH>New Jurisdiction Code</TH></TR>');
851
852 OPEN get_succesful_employees(l_id,p_mode);
853 FETCH get_succesful_employees
854 INTO l_person_id,l_assignment_id,l_old_juri_code,
855 l_new_juri_code,l_table_count;
856
857 WHILE (get_succesful_employees%FOUND)
858 LOOP
859
860 OPEN get_emp_details(l_person_id);
861 FETCH get_emp_details INTO l_full_name;
862 CLOSE get_emp_details;
863
864 OPEN get_updated_tables(l_id,p_mode,l_person_id,
865 l_assignment_id,l_old_juri_code,l_new_juri_code);
866 FETCH get_updated_tables INTO l_table_name;
867
868 fnd_file.put_line(fnd_file.output,'<TR><TD rowspan = '||l_table_count||'>'||l_full_name||
869 '</TD><TD rowspan = '||l_table_count||'>'||l_assignment_id||
870 '</TD><TD rowspan = '||l_table_count||'>'||l_old_juri_code||
871 '</TD><TD rowspan = '||l_table_count||'>'||l_new_juri_code||'</TD>');
872
873 WHILE (get_updated_tables%FOUND)
874 LOOP
875
876 fnd_file.put_line(fnd_file.output,'<TD>'||l_table_name||'</TD></TR>');
877 FETCH get_updated_tables INTO l_table_name;
878
879 END LOOP; /* get_updated_tables LOOP */
880
881 CLOSE get_updated_tables;
882
883 FETCH get_succesful_employees
884 INTO l_person_id,l_assignment_id,l_old_juri_code,
885 l_new_juri_code,l_table_count;
886
887 END LOOP; /* get_succesful_employees LOOP */
888
889 CLOSE get_succesful_employees;
890
891 fnd_file.put_line(fnd_file.output,'</TABLE>');
892
893 /* End of Successful Employees printing section */
894
895 /* Start of Errored Employees printing section */
896
897 fnd_file.put_line(fnd_file.output,'<H3>Employees for which processing ended in error </H3>');
898
899 OPEN get_emp_in_error(l_id,p_mode);
900 FETCH get_emp_in_error INTO l_full_name,l_assignment_id;
901
902 IF get_emp_in_error%FOUND THEN
903
904 fnd_file.put_line(fnd_file.output,'<TABLE cellpadding="2" cellspacing="1" border="2" summary="Employees in error">');
905 fnd_file.put_line(fnd_file.output,'<TR><TH colspan=2>Employee Details</TH>');
906 fnd_file.put_line(fnd_file.output,'<TR><TH>Full Name</TH><TH>Assignment ID</TH></TR>');
907
908 WHILE (get_emp_in_error%FOUND)
909 LOOP
910
911 fnd_file.put_line(fnd_file.output,'<TR><TD>'||l_full_name||'</TD><TD>'||l_assignment_id||'</TD></TR>');
912 FETCH get_emp_in_error INTO l_full_name,l_assignment_id;
913
914 END LOOP;
915
916 fnd_file.put_line(fnd_file.output,'</TABLE>');
917
918 ELSE
919
920 fnd_file.put_line(fnd_file.output,'<P>No Assignments in Error</P>');
921
922 END IF;
923
924 CLOSE get_emp_in_error;
925
926 /*Start printing Summary Section*/
927
928 fnd_file.put_line(fnd_file.output,'<H3>Summary </H3>');
929
930 /* Print the Number of Employees processed*/
931
932 OPEN get_emp_counts(l_id,p_mode,'C',l_process_type);
933 FETCH get_emp_counts INTO l_success_assignments;
934 CLOSE get_emp_counts;
935
936 OPEN get_emp_counts(l_id,p_mode,'E',l_process_type);
937 FETCH get_emp_counts INTO l_error_assignments;
938 CLOSE get_emp_counts;
939
940 fnd_file.put_line(fnd_file.output,'<P>Number of Assignments Upgraded successfully : '||l_success_assignments||'</P>');
941 fnd_file.put_line(fnd_file.output,'<P>Number of Assignments ended in Error : '||l_error_assignments||'</P>');
942
943 /* End of Printing the Number of Employees processed*/
944
945 IF (l_error_assignments = 0) THEN
946
947 fnd_file.put_line(fnd_file.output,'<P>Upgrade Process completed successfully</P>');
948
949 ELSE
950
951 fnd_file.put_line(fnd_file.output,'<P>Upgrade Process incomplete.Verify assignments in error and rerun the Upgrade process</P>');
952
953 END IF;
954
955 /* End of Summary section */
956
957 ELSE
958
959 fnd_file.put_line(fnd_file.output,'<P>No User Defined City Changes found that need upgrade</P>');
960 fnd_file.put_line(fnd_file.output,'<P>No Processing required</P>');
961
962 END IF; /* l_upgrade_needed IF*/
963
964 hr_utility.trace('Leaving pay_us_user_city_upgrade_pkg.generate_output');
965
966 EXCEPTION
967
968 WHEN OTHERS THEN
969
970 hr_utility.trace('Exception raised in pay_us_user_city_upgrade_pkg.generate_output');
971 fnd_file.put_line(fnd_file.log,'Exception raised in pay_us_user_city_upgrade_pkg.generate_output');
972 fnd_file.put_line(fnd_file.log,sqlerrm);
973 hr_utility.raise_error;
974
975 END generate_output;
976
977 END pay_us_user_city_upgrade_pkg;