[Home] [Help]
PACKAGE BODY: APPS.PAY_US_PSD_UPGRADE_PKG
Source
1 PACKAGE BODY pay_us_psd_upgrade_pkg AS
2 /*$Header: pyuspsdu.pkb 120.4.12020000.3 2012/09/04 05:51:33 pracagra noship $*/
3
4 /* This function will be used to see if the UPGRADE Process needs to process
5 for the PSDCODE change. It will return 'N' if the change was already
6 processed, Else 'Y' will be returned
7
8 The Initial execution of this function will load the data into PL/SQL table
9 and any subsequenct queries will verify the data in PL/SQL table and returns
10 the value */
11
12 FUNCTION upgrade_needed ( p_state_code VARCHAR2 DEFAULT NULL,
13 p_county_code VARCHAR2 DEFAULT NULL,
14 p_city_code VARCHAR2 DEFAULT NULL,
15 p_psdcode_change VARCHAR2 DEFAULT NULL
16 )
17 RETURN VARCHAR2
18 IS
19 --
20 --
21 CURSOR get_upgrade_needed_psdcodes IS
22 SELECT city_name psdcode_change,
23 state_code,
24 county_code,
25 old_city_code
26 FROM pay_us_modified_geocodes pumg
27 WHERE process_type = 'PA'
28 AND NOT EXISTS
29 (SELECT NULL
30 FROM pay_us_geo_update pugu,
31 pay_patch_status pps
32 WHERE pugu.process_type = 'PA'
33 AND pugu.process_mode = 'UPGRADE'
34 AND pugu.process_date = pps.applied_date
35 AND pugu.old_juri_code = substr(pumg.city_name,1,6)
36 AND pugu.new_juri_code = substr(pumg.city_name,8,6)
37 AND pps.id = pugu.id
38 AND pugu.assignment_id = -1
39 AND pps.applied_date >= TO_DATE('01-01-2012','DD-MM-YYYY')
40 AND substr(pps.patch_name,1,10) = 'PSDUPGRADE'
41 AND pugu.status = 'C'
42 AND pps.status = 'C');
43
44 l_upgrade_needed VARCHAR2(2) DEFAULT 'N';
45 l_index VARCHAR2(17);
46 l_psdcode_change pay_us_modified_geocodes.city_name%TYPE;
47 l_state_code pay_us_modified_geocodes.state_code%TYPE;
48 l_county_code pay_us_modified_geocodes.county_code%TYPE;
49 l_city_code pay_us_modified_geocodes.old_city_code%TYPE;
50 l_count NUMBER;
51
52 BEGIN
53
54 hr_utility.trace('Entering pay_us_psd_upgrade_pkg.l_upgrade_needed');
55
56 /*Populate the details only for the first time. This function if passed with NULL values for all
57 parameters indicates if we have any PSDCODEs that need upgrade by giving TRUE or FALSE. If
58 a specific PSDCODE Change details are passed as input, indication will be given if the UPGRADE
59 is needed for that specific change or not
60
61 For the first time since g_psdcode_changes_population will be FALSE, the records will be
62 populated into tab_psdcode_upgrade_changes and tab_psdcode_upgrade_changes will be made TRUE
63 so that any subsequent runs does not try to load the data. */
64
65 IF NOT g_psdcode_changes_population THEN
66
67 hr_utility.trace('Populating the PL/SQL Table');
68
69 OPEN get_upgrade_needed_psdcodes;
70 FETCH get_upgrade_needed_psdcodes INTO l_psdcode_change,l_state_code,l_county_code,l_city_code;
71
72 WHILE (get_upgrade_needed_psdcodes%FOUND)
73 LOOP
74
75 l_index := l_state_code||'-'||l_county_code||'-'||l_city_code||'-'||substr(l_psdcode_change,15,5);
76
77 tab_psdcode_upgrade_changes(l_index).psdcode_change := l_psdcode_change;
78 tab_psdcode_upgrade_changes(l_index).state_code := l_state_code;
79 tab_psdcode_upgrade_changes(l_index).county_code := l_county_code;
80 tab_psdcode_upgrade_changes(l_index).city_code := l_city_code;
81
82 FETCH get_upgrade_needed_psdcodes INTO l_psdcode_change,l_state_code,l_county_code,l_city_code;
83
84 END LOOP;
85
86 CLOSE get_upgrade_needed_psdcodes;
87
88 g_psdcode_changes_population := TRUE;
89
90 END IF;
91
92 l_count := tab_psdcode_upgrade_changes.COUNT;
93
94 IF p_state_code IS NULL AND l_count > 0 THEN
95
96 hr_utility.trace('NULL Parameter Call to the function');
97
98 l_upgrade_needed := 'Y';
99
100 ELSIF l_count > 0 THEN
101
102 hr_utility.trace('Checking if Upgrade Needed for '||p_state_code||'-'||p_county_code||'-'||p_city_code||' and PSDCODE '||p_psdcode_change);
103
104 l_index := p_state_code||'-'||p_county_code||'-'||p_city_code||'-'||substr(p_psdcode_change,15,5);
105
106 /*Check if the PSDCODE needs an upgrade. The presence of the PSDCODE related record
107 in the PL/SQL table itself is an indication that upgrade is necessary */
108
109 IF tab_psdcode_upgrade_changes.EXISTS(l_index)
110 THEN
111
112 l_upgrade_needed := 'Y';
113
114 END IF;
115
116 END IF;
117
118 hr_utility.trace('Returning '||l_upgrade_needed);
119
120 hr_utility.trace('Leaving pay_us_psd_upgrade_pkg.l_upgrade_needed');
121
122 RETURN l_upgrade_needed;
123
124 END upgrade_needed;
125
126
127 FUNCTION get_pay_patch_status_entry(p_mode VARCHAR2)
128 RETURN NUMBER
129 IS
130
131 l_prev_upgrade_id pay_patch_status.id%TYPE;
132 l_mode VARCHAR2(8);
133 l_id pay_patch_status.id%TYPE;
134 l_phase pay_patch_status.phase%TYPE;
135 l_date DATE;
136 l_generate_id pay_patch_status.id%TYPE;
137 l_generate_applied_date DATE;
138 l_patch_name pay_patch_status.patch_name%TYPE;
139 l_status pay_patch_status.status%TYPE;
140
141 BEGIN
142
143 hr_utility.trace('Entering pay_us_psd_upgrade_pkg.get_pay_patch_status_entry');
144
145 l_mode := p_mode;
146
147 /*Get the PAY_PATCH_STATUS ID of the Previous Successful execution of
148 UPGRADE mode of PSDUPGRADE*/
149
150 SELECT nvl(MAX(id),0)
151 INTO l_prev_upgrade_id
152 FROM pay_patch_status
153 WHERE patch_name LIKE 'PSDUPGRADE%'
154 AND description = 'UPGRADE'
155 AND status = 'C';
156
157 hr_utility.trace('Previous successful upgrade ID :'||l_prev_upgrade_id);
158
159 IF l_mode = 'UPGRADE' THEN
160
161 BEGIN /*Start of UPGRADE Mode*/
162
163 /*Check if there any incomplete Upgrade Runs from earlier. We should have atmost
164 one unsuccessful run in UPGRADE mode. If we find multiple runs in Unsuccessful
165 state, it needs to be analyzed why the second run did not set right the first
166 one.*/
167
168 hr_utility.trace('Check if there are any incomplete Upgrade Runs');
169
170 SELECT id,phase,applied_date
171 INTO l_id,l_phase,l_date
172 FROM pay_patch_status
173 WHERE patch_name LIKE '%PSDUPGRADE%'
174 AND description = 'UPGRADE'
175 AND id > l_prev_upgrade_id
176 AND nvl(status,'P') <> 'C';
177
178 UPDATE pay_patch_status
179 SET status = 'P'
180 WHERE id = l_id;
181
182 UPDATE pay_us_geo_update
183 SET status = 'C'
184 WHERE id = l_id
185 AND process_type = 'PA'
186 AND process_mode = l_mode
187 AND process_date = l_date
188 AND status = 'P';
189
190 UPDATE pay_us_geo_update
191 SET status = NULL
192 WHERE id = l_id
193 AND process_type = 'PA'
194 AND process_mode = l_mode
195 AND process_date = l_date
196 AND status = 'E';
197
198 EXCEPTION
199
200 WHEN NO_DATA_FOUND THEN
201
202 BEGIN
203
204 hr_utility.trace('Get the Generate Run pay_patch_status table ID');
205
206 SELECT MAX(pps.id)
207 INTO l_generate_id
208 FROM pay_patch_status pps
209 WHERE pps.patch_name LIKE 'PSDUPGRADE%'
210 AND pps.description = 'GENERATE'
211 AND pps.id > l_prev_upgrade_id
212 AND status = 'C';
213
214 SELECT applied_date
215 INTO l_generate_applied_date
216 FROM pay_patch_status pps
217 WHERE id = l_generate_id;
218
219 EXCEPTION
220
221 WHEN NO_DATA_FOUND THEN
222
223 hr_utility.trace('UPGRADE Mode can be executed only after running the process in GENERATE Mode successfully.');
224 hr_utility.trace('Not able to find the PAY_PATCH_STATUS entry corresponding to GENERATE Mode execution.');
225 fnd_file.put_line(fnd_file.log,'UPGRADE Mode can be executed only after running the process in GENERATE Mode successfully.');
226 fnd_file.put_line(fnd_file.log,'Not able to find the PAY_PATCH_STATUS entry corresponding to GENERATE Mode execution.');
227 hr_utility.raise_error;
228
229 END;
230
231 SELECT pay_patch_status_s.NEXTVAL,
232 'PSDUPGRADE_'||to_char(sysdate,'DDMONYYYYHHMISS'),
233 TRUNC(sysdate),
234 'P'
235 INTO l_id,l_patch_name,l_date,l_status
236 FROM DUAL;
237
238 INSERT INTO pay_patch_status
239 (id,patch_number,patch_name,phase,status,applied_date,legislation_code,description)
240 VALUES
241 (l_id,'1111111',l_patch_name,'UPGRADE',l_status,l_date,'US',l_mode);
242
243 hr_utility.trace('Created UPGRADE pay_patch_status entry with ID : '||l_id);
244
245 UPDATE /*+PARALLEL*/ pay_us_geo_update
246 SET id = l_id,
247 process_date = l_date,
248 process_mode = l_mode,
249 status = NULL
250 WHERE id = l_generate_id
251 AND process_date = l_generate_applied_date
252 AND process_type = 'PA'
253 AND process_mode = 'GENERATE'
254 AND assignment_id <> -1;
255
256 WHEN TOO_MANY_ROWS THEN
257
258 hr_utility.trace('Inconsistent state of Upgrade Process. Raise error');
259 hr_utility.trace('Too Many rows of Upgrade Process found');
260 fnd_file.put_line(fnd_file.log,'Inconsistent state of Upgrade Process. Raise error');
261 fnd_file.put_line(fnd_file.log,'Too Many rows of Upgrade Process found');
262 hr_utility.raise_error;
263
264 END;
265
266 /*End of UPGRADE Mode*/
267
268 ELSIF l_mode = 'GENERATE' THEN
269
270 /*Start of GENERATE Mode*/
271
272 hr_utility.trace('Executing stepts For GENERATE Mode');
273
274 /*Check if there is an incomplete Upgrade Run. If Upgrade run already
275 started and is in incomplete state, then we should not run the Generate
276 Mode*/
277
278 BEGIN
279
280 hr_utility.trace('Checking if there are any incomplete UPGRADE Runs from earlier');
281
282 SELECT id,phase,applied_date
283 INTO l_id,l_phase,l_date
284 FROM pay_patch_status
285 WHERE patch_name LIKE '%PSDUPGRADE%'
286 AND description = 'UPGRADE'
287 AND id > l_prev_upgrade_id
288 AND nvl(status,'P') <> 'C';
289
290 hr_utility.trace('Incomplete Upgrade Run Exists. Run the Process in UPGRADE mode');
291 fnd_file.put_line(fnd_file.log,'Incomplete Upgrade Run Exists. Run the Process in UPGRADE mode');
292 hr_utility.raise_error;
293
294 EXCEPTION
295
296 WHEN NO_DATA_FOUND THEN
297
298 hr_utility.trace('No incomplete UPGRADE Runs from earlier');
299
300 END;
301
302 /*Delete Data from earlier GENERATE run before starting the current one*/
303
304 hr_utility.trace('Delete any Data generated earlier before the current one');
305
306 DELETE FROM pay_us_geo_update
307 WHERE process_type = 'PA'
308 AND process_mode = 'GENERATE'
309 AND id IN
310 (SELECT id
311 FROM pay_patch_status
312 WHERE patch_name LIKE 'PSDUPGRADE%'
313 AND legislation_code = 'US'
314 AND description = 'GENERATE'
315 AND id > l_prev_upgrade_id);
316
317 /*Update the Status of Previous GENERATE pay_patch_status entry to D indicating
318 deletion of the Generated Data.*/
319
320 UPDATE pay_patch_status
321 SET status = 'D',
322 phase = NULL
323 WHERE patch_name LIKE 'PSDUPGRADE%'
324 AND legislation_code = 'US'
325 AND id > l_prev_upgrade_id
326 AND description = 'GENERATE';
327
328 SELECT pay_patch_status_s.NEXTVAL,
329 'PSDUPGRADE_'||to_char(sysdate,'DDMONYYYYHHMISS'),
330 TRUNC(sysdate),
331 'P'
332 INTO l_id,l_patch_name,l_date,l_status
333 FROM DUAL;
334
335 INSERT INTO pay_patch_status
336 (id,patch_number,patch_name,phase,status,applied_date,legislation_code,description)
337 VALUES
338 (l_id,'1111111',l_patch_name,'VALIDATE',l_status,l_date,'US',l_mode);
339
340 hr_utility.trace('Created GENERATE pay_patch_status entry with ID : '||l_id);
341
342 /*End of GENARATE Mode*/
343
344 END IF; /*l_mode IF */
345
346 hr_utility.trace('Leaving pay_us_psd_upgrade_pkg.get_pay_patch_status_entry');
347
348 RETURN l_id;
349
350 END get_pay_patch_status_entry;
351
352 PROCEDURE create_jsd_element_set (p_pay_patch_status_id pay_patch_status.id%TYPE)
353 IS
354 --
355 --
356 l_element_set_id pay_element_sets.element_set_id%TYPE;
357
358 BEGIN
359
360 hr_utility.trace('Entering pay_us_psd_upgrade_pkg.create_jsd_element_set');
361
362 /*Create the Element set for Elements having Jurisdiction Code as Input Value*/
363
364 SELECT pay_element_sets_s.nextval
365 INTO l_element_set_id
366 FROM DUAL;
367
368 INSERT INTO pay_element_sets
369 (element_set_id,
370 legislation_code,
371 element_set_name,
372 element_set_type)
373 VALUES
374 (l_element_set_id,
375 'US',
376 'US_JSD_ELEMENTS_'||p_pay_patch_status_id,
377 'D');
378
379 INSERT INTO pay_element_type_rules
380 (element_type_id,
381 element_set_id,
382 include_or_exclude)
383 SELECT DISTINCT pet.element_type_id,
384 l_element_set_id,
385 'I'
386 FROM pay_element_types_f pet,
387 pay_input_values_f piv,
388 per_business_groups pbg
389 WHERE NVL(pet.legislation_code,'US') = 'US'
390 AND NVL(pet.business_group_id,pbg.business_group_id) = pbg.business_group_id
391 AND pbg.legislation_code = 'US'
392 AND NVL(pbg.date_to,TO_DATE('31-12-4712','DD-MM-YYYY'))
393 >= TO_DATE('01-01-2012','DD-MM-YYYY')
394 AND piv.element_type_id = pet.element_type_id
395 AND piv.name = 'Jurisdiction';
396
397 hr_utility.trace('Created Element Set with Name : US_JSD_ELEMENTS_'||p_pay_patch_status_id||', Element Set ID :'||l_element_set_id);
398
399 hr_utility.trace('Leaving pay_us_psd_upgrade_pkg.create_jsd_element_set');
400
401 END create_jsd_element_set;
402
403 PROCEDURE perform_initial_setup(p_payroll_action_id NUMBER) IS
404
405 l_upgrade_needed VARCHAR2(2);
406 l_mode VARCHAR2(8);
407 l_pay_patch_status_id pay_patch_status.id%TYPE;
408
409 BEGIN
410
411 hr_utility.trace('Entering pay_us_psd_upgrade_pkg.perform_initial_setup');
412
413 l_upgrade_needed := upgrade_needed();
414
415 IF l_upgrade_needed = 'Y' THEN
416
417 hr_utility.trace('PSDCODE changes entries found for Upgrade');
418 hr_utility.trace('Processing required');
419 fnd_file.put_line(fnd_file.log,'PSDCODE changes entries found for Upgrade');
420 fnd_file.put_line(fnd_file.log,'Processing required');
421
422 /*Get the MODE of Execution for the PSDCODE Process*/
423
424 SELECT pay_us_geo_upd_pkg.get_parameter('MODE',ppa.legislative_parameters)
425 INTO l_mode
426 FROM pay_payroll_actions ppa
427 WHERE ppa.payroll_action_id = p_payroll_action_id;
428
429 hr_utility.trace('Mode of Execution ' ||l_mode);
430
431 l_pay_patch_status_id := get_pay_patch_status_entry(l_mode);
432
433 hr_utility.trace('PAY_PATCH_STATUS Entry for processing : ' ||l_pay_patch_status_id);
434
435 IF l_mode = 'GENERATE' THEN
436
437 create_jsd_element_set(l_pay_patch_status_id);
438
439 END IF;
440
441 ELSE
442
443 hr_utility.trace('No PSDCODE changes entries for Upgrade');
444 hr_utility.trace('No Processing required');
445 fnd_file.put_line(fnd_file.log,'No PSDCODE changes entries for Upgrade');
446 fnd_file.put_line(fnd_file.log,'No Processing required');
447
448 END IF; /*l_upgrade_needed IF*/
449
450 hr_utility.trace('Leaving pay_us_psd_upgrade_pkg.perform_initial_setup');
451
452 END perform_initial_setup;
453
454 PROCEDURE generate_assignment(p_assignment_id per_all_assignments_f.assignment_id%TYPE,
455 p_id pay_patch_status.id%TYPE,
456 p_date DATE,
457 p_mode VARCHAR2,
458 p_element_set_id pay_element_sets.element_set_id%TYPE) IS
459
460 CURSOR get_assignment_actions(p_assignment_id NUMBER) IS
461 SELECT assignment_action_id
462 FROM pay_payroll_actions ppa,
463 pay_assignment_actions paa
464 WHERE paa.assignment_id = p_assignment_id
465 AND ppa.payroll_action_id = paa.payroll_action_id
466 AND ppa.action_type in ('Q','B','R','I','V')
467 AND ppa.effective_date >= to_date('01-01-2012','DD-MM-YYYY')
468 AND paa.action_status = 'C';
469
470 CURSOR get_archive_asg_actions(p_assignment_id NUMBER) IS
471 SELECT assignment_action_id
472 FROM pay_payroll_actions ppa,
473 pay_assignment_actions paa
474 WHERE paa.assignment_id = p_assignment_id
475 AND ppa.payroll_action_id = paa.payroll_action_id
476 AND ppa.action_type in ('X')
477 AND ppa.report_type = 'XFR_INTERFACE'
478 AND ppa.effective_date >= to_date('01-01-2012','DD-MM-YYYY');
479
480 -- Added for bug 14213838.
481 CURSOR get_psd_archive_asg_actions(p_assignment_id NUMBER) IS
482 SELECT assignment_action_id
483 FROM pay_payroll_actions ppa,
484 pay_assignment_actions paa
485 WHERE paa.assignment_id = p_assignment_id
486 AND ppa.payroll_action_id = paa.payroll_action_id
487 AND ppa.action_type in ('X')
488 AND ppa.report_type = 'PSD_MAG_XML'
489 AND ppa.effective_date >= to_date('01-01-2012','DD-MM-YYYY');
490
491 -- Added for bug 14213838.
492 CURSOR get_psd_archive_item_id(l_assignment_action_id NUMBER) IS
493 SELECT fai.archive_item_id
494 FROM ff_archive_items fai
495 WHERE fai.context1=l_assignment_action_id;
496
497 CURSOR get_psdcode_details IS
498 SELECT substr(pumg.city_name,1,6),
499 substr(pumg.city_name,8,6),
500 substr(pumg.city_name,15,5),
501 state_code||'-'||county_code||'-'||old_city_code
502 FROM pay_us_modified_geocodes pumg
503 WHERE pumg.process_type = 'PA'
504 AND pay_us_psd_upgrade_pkg.upgrade_needed(pumg.state_code,
505 pumg.county_code,
506 pumg.old_city_code,
507 pumg.city_name)='Y'
508 ORDER BY pumg.state_code,pumg.county_code,pumg.old_city_code;
509
510 CURSOR get_swap_psdcode_details IS
511 SELECT substr(pumg1.city_name,1,6),
512 substr(pumg1.city_name,8,6)
513 FROM pay_us_modified_geocodes pumg1,
514 pay_us_modified_geocodes pumg2
515 WHERE pumg1.process_type = 'PA'
516 AND pay_us_psd_upgrade_pkg.upgrade_needed(pumg1.state_code,
517 pumg1.county_code,
518 pumg1.old_city_code,
519 pumg1.city_name)='Y'
520 AND pumg2.process_type = 'PA'
521 AND pay_us_psd_upgrade_pkg.upgrade_needed(pumg2.state_code,
522 pumg2.county_code,
523 pumg2.old_city_code,
524 pumg2.city_name)='Y'
525 AND pumg1.rowid <> pumg2.rowid
526 AND substr(pumg1.city_name,1,6) = substr(pumg2.city_name,8,6)
527 AND substr(pumg2.city_name,1,6) = substr(pumg1.city_name,8,6)
528 ORDER BY 1,2;
529
530 TYPE rec_assignment_action_id IS TABLE OF pay_assignment_actions.assignment_action_id%TYPE INDEX BY BINARY_INTEGER;
531 tab_assignment_action_id rec_assignment_action_id;
532
533 l_old_psd_code VARCHAR2(6);
534 l_new_psd_code VARCHAR2(6);
535 l_school_dsts_code VARCHAR2(5);
536 l_jurisdiction_code VARCHAR2(11);
537 l_person_id per_all_assignments_f.person_id%TYPE;
538 l_archive_item_id NUMBER; -- Added for bug 14213838.
539
540 BEGIN
541
542 hr_utility.trace('Inside pay_us_psd_upgrade_pkg.generate_assignment for Assignment ID :'||p_assignment_id);
543
544 SELECT person_id
545 INTO l_person_id
546 FROM per_all_assignments_f
547 WHERE assignment_id = p_assignment_id
548 AND rownum = 1;
549
550 IF p_mode = 'GENERATE' THEN
551
552 /*GENERATE Mode Ends*/
553
554 /*Start of PAY_US_ASG_REPORTING Section*/
555
556 INSERT INTO pay_us_geo_update
557 (id,
558 assignment_id,
559 person_id,
560 table_name,
561 old_juri_code,
562 new_juri_code,
563 process_type,
564 process_date,
565 process_mode,
566 description)
567 SELECT DISTINCT p_id
568 ,p_assignment_id
569 ,l_person_id
570 ,'PAY_US_ASG_REPORTING'
571 ,puar.jurisdiction_code
572 ,replace(puar.jurisdiction_code,substr(pumg.city_name,1,6),substr(pumg.city_name,8,6))
573 ,pumg.process_type
574 ,p_date
575 ,p_mode
576 ,substr(pumg.city_name,8,6)
577 FROM pay_us_asg_reporting puar,
578 pay_us_modified_geocodes pumg
579 WHERE puar.assignment_id = p_assignment_id
580 AND length(puar.jurisdiction_code) = 16
581 AND substr(puar.jurisdiction_code,1,2) = '39'
582 AND (substr(puar.jurisdiction_code,4,6) = substr(pumg.city_name,1,6) or
583 substr(puar.jurisdiction_code,11,6) = substr(pumg.city_name,1,6))
584 AND pumg.process_type = 'PA'
585 AND pay_us_psd_upgrade_pkg.upgrade_needed(pumg.state_code,
586 pumg.county_code,
587 pumg.old_city_code,
588 pumg.city_name)='Y';
589
590 hr_utility.trace('Done with PAY_US_ASG_REPORTING');
591
592 /*End of PAY_US_ASG_REPORTING Section*/
593
594 /*Start of PAY_ELEMENT_ENTRY_VALUES_F Section*/
595
596 INSERT INTO pay_us_geo_update
597 (id,
598 assignment_id,
599 person_id,
600 table_value_id,
601 table_name,
602 old_juri_code,
603 new_juri_code,
604 process_type,
605 process_date,
606 process_mode,
607 description)
608 SELECT DISTINCT p_id
609 ,p_assignment_id
610 ,l_person_id
611 ,peev.element_entry_value_id
612 ,'PAY_ELEMENT_ENTRY_VALUES_F'
613 ,peev.screen_entry_value
614 ,replace(peev.screen_entry_value,substr(pumg.city_name,1,6),substr(pumg.city_name,8,6))
615 ,pumg.process_type
616 ,p_date
617 ,p_mode
618 ,substr(pumg.city_name,8,6)
619 FROM pay_element_entries_f pee,
620 pay_element_entry_values_f peev,
621 pay_input_values_f piv,
622 pay_element_type_rules petr,
623 pay_us_modified_geocodes pumg
624 WHERE pee.assignment_id = p_assignment_id
625 AND pee.element_type_id = petr.element_type_id
626 AND peev.element_entry_id = pee.element_entry_id
627 AND petr.element_set_id = p_element_set_id
628 AND pee.element_type_id = piv.element_type_id
629 AND piv.name = 'Jurisdiction'
630 AND pee.effective_end_date >= to_date('01-01-2012','DD-MM-YYYY')
631 AND peev.effective_end_date >= to_date('01-01-2012','DD-MM-YYYY')
632 AND piv.effective_end_date >= to_date('01-01-2012','DD-MM-YYYY')
633 AND length(peev.screen_entry_value) = 16
634 AND substr(peev.screen_entry_value,1,2) = '39'
635 AND (substr(peev.screen_entry_value,4,6) = substr(pumg.city_name,1,6) or
636 substr(peev.screen_entry_value,11,6) = substr(pumg.city_name,1,6))
637 AND pumg.process_type = 'PA'
638 AND pay_us_psd_upgrade_pkg.upgrade_needed(pumg.state_code,
639 pumg.county_code,
640 pumg.old_city_code,
641 pumg.city_name)='Y';
642
643 hr_utility.trace('Done with PAY_ELEMENT_ENTRY_VALUES_F');
644
645 /*End of PAY_ELEMENT_ENTRY_VALUES_F Section*/
646
647 /* For tables PAY_RUN_RESULTS,PAY_RUN_RESULT_VALUES, first
648 assignment_action_id will be queried and using that we will look into these
649 tables */
650
651 OPEN get_assignment_actions(p_assignment_id);
652 FETCH get_assignment_actions BULK COLLECT INTO tab_assignment_action_id;
653
654 IF tab_assignment_action_id.COUNT > 0 THEN
655
656 FOR i IN tab_assignment_action_id.FIRST..tab_assignment_action_id.LAST
657 LOOP
658
659 /*Start of PAY_RUN_RESULTS Section*/
660
661 INSERT INTO pay_us_geo_update
662 (id,
663 assignment_id,
664 person_id,
665 table_value_id,
666 table_name,
667 old_juri_code,
668 new_juri_code,
669 process_type,
670 process_date,
671 process_mode,
672 description)
673 SELECT DISTINCT p_id
674 ,p_assignment_id
675 ,l_person_id
676 ,prr.run_result_id
677 ,'PAY_RUN_RESULTS'
678 ,prr.jurisdiction_code
679 ,replace(prr.jurisdiction_code,substr(pumg.city_name,1,6),substr(pumg.city_name,8,6))
680 ,pumg.process_type
681 ,p_date
682 ,p_mode
683 ,substr(pumg.city_name,8,6)
684 FROM pay_run_results prr,
685 pay_us_modified_geocodes pumg,
686 pay_element_types_f pet,
687 pay_input_values_f piv
688 WHERE prr.assignment_action_id = tab_assignment_action_id(i)
689 AND length(prr.jurisdiction_code) = 16
690 AND substr(prr.jurisdiction_code,1,2) = '39'
691 AND (substr(prr.jurisdiction_code,4,6) = substr(pumg.city_name,1,6) or
692 substr(prr.jurisdiction_code,11,6) = substr(pumg.city_name,1,6))
693 AND pumg.process_type = 'PA'
694 AND pet.element_type_id = piv.element_type_id
695 AND piv.name = 'Jurisdiction'
696 AND pet.element_type_id = prr.element_type_id
697 AND pay_us_psd_upgrade_pkg.upgrade_needed(pumg.state_code,
698 pumg.county_code,
699 pumg.old_city_code,
700 pumg.city_name)='Y';
701
702
703 /*End of PAY_RUN_RESULTS Section*/
704
705 IF SQL%ROWCOUNT > 0 THEN
706
707 /*Start of PAY_RUN_RESULT_VALUES Section*/
708
709 INSERT INTO pay_us_geo_update
710 (id,
711 assignment_id,
712 person_id,
713 table_value_id,
714 table_name,
715 old_juri_code,
716 new_juri_code,
717 process_type,
718 process_date,
719 process_mode,
720 description)
721 SELECT DISTINCT p_id
722 ,p_assignment_id
723 ,l_person_id
724 ,prr.run_result_id
725 ,'PAY_RUN_RESULT_VALUES'
726 ,prv.result_value
727 ,replace(prv.result_value,substr(pumg.city_name,1,6),substr(pumg.city_name,8,6))
728 ,pumg.process_type
729 ,p_date
730 ,p_mode
731 ,substr(pumg.city_name,8,6)
732 FROM pay_run_results prr,
733 pay_run_result_values prv,
734 pay_us_modified_geocodes pumg,
735 pay_element_types_f pet,
736 pay_input_values_f piv,
737 pay_element_type_rules petr
738 WHERE prr.assignment_action_id = tab_assignment_action_id(i)
739 AND prr.run_result_id = prv.run_result_id
740 AND length(prv.result_value) = 16
741 AND substr(prv.result_value,1,2) = '39'
742 AND (substr(prv.result_value,4,6) = substr(pumg.city_name,1,6) or
743 substr(prv.result_value,11,6) = substr(pumg.city_name,1,6))
744 AND pumg.process_type = 'PA'
745 AND pet.element_type_id = petr.element_type_id
746 AND petr.element_set_id = p_element_set_id
747 AND pet.element_type_id = prr.element_type_id
748 AND pet.legislation_code = 'US'
749 AND pet.element_type_id = piv.element_type_id
750 AND prv.input_value_id = piv.input_value_id
751 AND piv.name = 'Jurisdiction'
752 AND pay_us_psd_upgrade_pkg.upgrade_needed(pumg.state_code,
753 pumg.county_code,
754 pumg.old_city_code,
755 pumg.city_name)='Y';
756
757 /*End of PAY_RUN_RESULT_VALUES Section*/
758
759 END IF; /*SQL%ROWCOUNT IF*/
760
761 END LOOP; /*Assignment Action ID Loop*/
762
763 END IF;
764
765 CLOSE get_assignment_actions;
766
767 hr_utility.trace('Done with PAY_RUN_RESULTS,PAY_RUN_RESULT_VALUES');
768
769 /*Start of PAY_ACTION_INFORMATION Section*/
770
771 OPEN get_archive_asg_actions(p_assignment_id);
772 FETCH get_archive_asg_actions BULK COLLECT INTO tab_assignment_action_id;
773
774 IF tab_assignment_action_id.COUNT > 0 THEN
775
776 FOR i IN tab_assignment_action_id.FIRST..tab_assignment_action_id.LAST
777 LOOP
778
779 hr_utility.trace('id'||p_id);
780 hr_utility.trace('asg id'||p_assignment_id);
781 hr_utility.trace('mode'||p_mode);
782 hr_utility.trace('archive action id'||tab_assignment_action_id(i));
783
784 INSERT INTO pay_us_geo_update
785 (id,
786 assignment_id,
787 person_id,
788 table_value_id,
789 table_name,
790 old_juri_code,
791 new_juri_code,
792 process_type,
793 process_date,
794 process_mode,
795 description)
796 SELECT DISTINCT p_id
797 ,p_assignment_id
798 ,l_person_id
799 ,pai.action_information_id
800 ,'PAY_ACTION_INFORMATION'
801 ,pai.jurisdiction_code
802 ,replace(pai.jurisdiction_code,substr(pumg.city_name,1,6),substr(pumg.city_name,8,6))
803 ,pumg.process_type
804 ,p_date
805 ,p_mode
806 ,substr(pumg.city_name,8,6)
807 FROM pay_action_information pai,
808 pay_us_modified_geocodes pumg
809 WHERE pai.action_context_id = tab_assignment_action_id(i)
810 AND pai.action_context_type = 'AAP'
811 AND pai.action_information_category = 'US PSD'
812 AND length(pai.jurisdiction_code) = 16
813 AND substr(pai.jurisdiction_code,1,2) = '39'
814 AND (substr(pai.jurisdiction_code,4,6) = substr(pumg.city_name,1,6) or
815 substr(pai.jurisdiction_code,11,6) = substr(pumg.city_name,1,6))
816 AND pumg.process_type = 'PA'
817 AND pay_us_psd_upgrade_pkg.upgrade_needed(pumg.state_code,
818 pumg.county_code,
819 pumg.old_city_code,
820 pumg.city_name)='Y';
821
822 END LOOP; /*Assignment Action ID Loop*/
823
824 END IF; /* tab_assignment_action_id.COUNT IF */
825
826 CLOSE get_archive_asg_actions;
827
828 hr_utility.trace('Done with PAY_ACTION_INFORMATION');
829
830 /*End of PAY_ACTION_INFORMATION Section*/
831
832 -- Added for bug 14213838.
833 /*Start of FF_ARCHVIE_ITEMS_CONTEXTS Section*/
834
835 OPEN get_psd_archive_asg_actions(p_assignment_id);
836 FETCH get_psd_archive_asg_actions BULK COLLECT INTO tab_assignment_action_id;
837
838 IF tab_assignment_action_id.COUNT > 0 THEN
839
840 FOR i IN tab_assignment_action_id.FIRST..tab_assignment_action_id.LAST
841 LOOP
842
843 hr_utility.trace('id'||p_id);
844 hr_utility.trace('asg id'||p_assignment_id);
845 hr_utility.trace('mode'||p_mode);
846 hr_utility.trace('archive action id'||tab_assignment_action_id(i));
847
848
849 OPEN get_psd_archive_item_id(tab_assignment_action_id(i));
850 LOOP
851 FETCH get_psd_archive_item_id INTO l_archive_item_id;
852 EXIT WHEN get_psd_archive_item_id%NOTFOUND;
853
854 INSERT INTO pay_us_geo_update
855 (id,
856 assignment_id,
857 person_id,
858 table_value_id,
859 table_name,
860 old_juri_code,
861 new_juri_code,
862 process_type,
863 process_date,
864 process_mode,
865 description)
866 SELECT DISTINCT p_id
867 ,p_assignment_id
868 ,l_person_id
869 ,faic.archive_item_id
870 ,'FF_ARCHIVE_ITEM_CONTEXTS'
871 ,faic.context
872 ,replace(faic.context,substr(pumg.city_name,1,6),substr(pumg.city_name,8,6))
873 ,pumg.process_type
874 ,p_date
875 ,p_mode
876 ,substr(pumg.city_name,8,6)
877 FROM ff_archive_item_contexts faic,
878 pay_us_modified_geocodes pumg
879 WHERE faic.archive_item_id = l_archive_item_id
880 AND length(faic.context) = 16
881 AND substr(faic.context,1,2) = '39'
882 AND (substr(faic.context,4,6) = substr(pumg.city_name,1,6) or
883 substr(faic.context,11,6) = substr(pumg.city_name,1,6))
884 AND pumg.process_type = 'PA'
885 AND pay_us_psd_upgrade_pkg.upgrade_needed(pumg.state_code,
886 pumg.county_code,
887 pumg.old_city_code,
888 pumg.city_name)='Y';
889 END LOOP; /*Archive Item ID Loop*/
890 END LOOP; /*Assignment Action ID Loop*/
891
892 END IF; /* tab_assignment_action_id.COUNT IF */
893
894 CLOSE get_psd_archive_asg_actions;
895
896 hr_utility.trace('Done with ff_archive_items_contexts');
897
898 /*End of FF_ARCHIVE_ITEM_CONTEXTS Section*/
899
900 /*Check if the assignment is attached to Jurisdiction Code which has multiple school districts
901 with same PSD Code. In that case need to see if the Assignment is attached to the school
902 district that had PSD Code change or not. If the school district is different then no need to
903 do any update. So need to delete that assignment updates for that PSD Update. We are doing this
904 check here instead of in the beginning because we do not want to miss any other PSD Code updates
905 for this assignment*/
906
907 DELETE FROM pay_us_geo_update pugu
908 WHERE pugu.process_type = 'PA'
909 AND pugu.process_mode = p_mode
910 AND pugu.process_date = p_date
911 AND pugu.id = p_id
912 AND pugu.assignment_id = p_assignment_id
913 AND EXISTS
914 (SELECT NULL
915 FROM pay_us_emp_city_tax_rules_f pect,
916 pay_us_city_school_dsts pcsd,
917 pay_us_modified_geocodes pumg
918 WHERE pect.assignment_id = p_assignment_id
919 AND pect.jurisdiction_code = pumg.state_code||'-'||pumg.county_code||'-'||pumg.old_city_code
920 AND pugu.description = substr(pumg.city_name,8,6)
921 AND nvl(pect.school_district_code,substr(pumg.city_name,15,5)) <> substr(pumg.city_name,15,5)
922 AND pumg.process_type = 'PA'
923 AND pay_us_psd_upgrade_pkg.upgrade_needed(pumg.state_code,
924 pumg.county_code,
925 pumg.old_city_code,
926 pumg.city_name)='Y'
927 AND pcsd.state_code = pumg.state_code
928 AND pcsd.county_code = pumg.county_code
929 AND pcsd.city_code = pumg.old_city_code
930 AND pcsd.school_dst_code <> substr(pumg.city_name,15,5));
931
932 OPEN get_psdcode_details;
933 FETCH get_psdcode_details INTO l_old_psd_code,l_new_psd_code,l_school_dsts_code,l_jurisdiction_code;
934
935 WHILE (get_psdcode_details%FOUND)
936 LOOP
937
938 UPDATE pay_us_geo_update
939 SET new_juri_code = replace(new_juri_code,l_old_psd_code,l_new_psd_code)
940 WHERE process_type = 'PA'
941 AND process_mode = p_mode
942 AND process_date = p_date
943 AND id = p_id
944 AND assignment_id = p_assignment_id
945 AND description <> l_old_psd_code
946 AND instr(old_juri_code,l_old_psd_code) > 0;
947
948 FETCH get_psdcode_details INTO l_old_psd_code,l_new_psd_code,l_school_dsts_code,l_jurisdiction_code;
949
950 END LOOP;
951
952 CLOSE get_psdcode_details;
953
954 OPEN get_swap_psdcode_details;
955 FETCH get_swap_psdcode_details INTO l_old_psd_code,l_new_psd_code;
956
957 WHILE (get_swap_psdcode_details%FOUND)
958 LOOP
959
960 UPDATE pay_us_geo_update
961 SET new_juri_code = substr(old_juri_code,1,3)||substr(old_juri_code,11,6)||'-'||substr(old_juri_code,4,6)
962 WHERE process_type = 'PA'
963 AND process_mode = p_mode
964 AND process_date = p_date
965 AND id = p_id
966 AND assignment_id = p_assignment_id
967 AND instr(old_juri_code,l_old_psd_code) > 0
968 AND instr(old_juri_code,l_new_psd_code) > 0
969 AND (description = l_old_psd_code OR
970 description = l_new_psd_code);
971
972 FETCH get_swap_psdcode_details INTO l_old_psd_code,l_new_psd_code;
973
974 END LOOP;
975
976 CLOSE get_swap_psdcode_details;
977
978 /*GENERATE Mode Ends*/
979
980 ELSE
981
982 /*UPGRADE Mode Starts*/
983
984 NULL;
985
986 /*UPGRADE Mode Ends*/
987
988 END IF;
989
990 hr_utility.trace('Leaving pay_us_psd_upgrade_pkg.generate_assignment');
991
992 EXCEPTION
993
994 WHEN OTHERS THEN
995
996 fnd_file.put_line(fnd_file.log,'Exception raised in pay_us_psd_upgrade_pkg.generate_assignment for Assignment ID '||p_assignment_id);
997 fnd_file.put_line(fnd_file.log,'SQLERRM = ' || SQLERRM);
998 hr_utility.trace('Exception raised in pay_us_psdcode_upgrade_pkg.generate_assignment');
999 hr_utility.trace('SQLERRM = ' || SQLERRM);
1000
1001 END;
1002
1003 PROCEDURE upgrade_assignment(p_assignment_id per_all_assignments_f.assignment_id%TYPE,
1004 p_id pay_patch_status.id%TYPE,
1005 p_date DATE,
1006 p_mode VARCHAR2) IS
1007 PRAGMA AUTONOMOUS_TRANSACTION;
1008
1009 CURSOR get_emp_upgrade_details(p_id NUMBER,p_mode VARCHAR2,p_date DATE,
1010 p_assignment_id NUMBER) IS
1011 SELECT DISTINCT
1012 pugu.table_name,
1013 pugu.table_value_id,
1014 pugu.old_juri_code,
1015 pugu.new_juri_code,
1016 decode(pugu.table_name,
1017 'PAY_US_ASG_REPORTING',1,
1018 'PAY_ACTION_INFORMATION',2,
1019 'PAY_ELEMENT_ENTRY_VALUES_F',3,
1020 'PAY_RUN_RESULTS',4,
1021 'PAY_RUN_RESULT_VALUES',5,
1022 'FF_ARCHVIE_ITEM_CONTEXTS', 6) table_order -- Added for bug 14213838.
1023 FROM pay_us_geo_update pugu
1024 WHERE pugu.id = p_id
1025 AND pugu.process_type = 'PA'
1026 AND pugu.process_mode = p_mode
1027 AND pugu.process_date = p_date
1028 AND pugu.assignment_id = p_assignment_id
1029 AND nvl(pugu.status,'P') <> 'C'
1030 ORDER BY table_order;
1031
1032 CURSOR get_emp_counts(p_id NUMBER,p_mode VARCHAR2,p_date DATE,
1033 p_status VARCHAR2) IS
1034 SELECT COUNT(0)
1035 FROM (SELECT DISTINCT assignment_id
1036 FROM pay_us_geo_update pugu
1037 WHERE pugu.id = p_id
1038 AND pugu.process_type = 'PA'
1039 AND pugu.process_mode = p_mode
1040 AND pugu.process_date = p_date
1041 AND pugu.assignment_id > 0
1042 AND NVL(pugu.status,'NULL') = DECODE(p_status,'ALL',NVL(pugu.status,'NULL'),p_status));
1043
1044 l_table_name VARCHAR2(100);
1045 l_table_value_id NUMBER;
1046 l_old_jurisdiction_code VARCHAR2(16);
1047 l_new_jurisdiction_code VARCHAR2(16);
1048 l_table_order NUMBER;
1049
1050 BEGIN
1051
1052 hr_utility.trace('Entering pay_us_psd_upgrade_pkg.upgrade_assignment for '||p_assignment_id);
1053
1054 OPEN get_emp_upgrade_details(p_id,p_mode,p_date,p_assignment_id);
1055 FETCH get_emp_upgrade_details INTO l_table_name,l_table_value_id,
1056 l_old_jurisdiction_code,l_new_jurisdiction_code,l_table_order;
1057
1058 WHILE (get_emp_upgrade_details%FOUND)
1059 LOOP
1060
1061 IF l_table_name = 'PAY_US_ASG_REPORTING' THEN
1062
1063 UPDATE pay_us_asg_reporting
1064 SET jurisdiction_code= l_new_jurisdiction_code
1065 WHERE assignment_id = p_assignment_id
1066 AND jurisdiction_code = l_old_jurisdiction_code;
1067
1068 ELSIF l_table_name = 'PAY_ELEMENT_ENTRY_VALUES_F' THEN
1069
1070 UPDATE pay_element_entry_values_f
1071 SET screen_entry_value = l_new_jurisdiction_code
1072 WHERE element_entry_value_id = l_table_value_id
1073 AND screen_entry_value = l_old_jurisdiction_code;
1074
1075 ELSIF l_table_name = 'PAY_RUN_RESULTS' THEN
1076
1077 UPDATE pay_run_results
1078 SET jurisdiction_code = l_new_jurisdiction_code
1079 WHERE run_result_id = l_table_value_id
1080 AND jurisdiction_code = l_old_jurisdiction_code;
1081
1082 ELSIF l_table_name = 'PAY_RUN_RESULT_VALUES' THEN
1083
1084 UPDATE pay_run_result_values
1085 SET result_value = l_new_jurisdiction_code
1086 WHERE run_result_id = l_table_value_id
1087 AND result_value = l_old_jurisdiction_code;
1088
1089 ELSIF l_table_name = 'PAY_ACTION_INFORMATION' THEN
1090
1091 UPDATE pay_action_information
1092 SET jurisdiction_code = l_new_jurisdiction_code
1093 WHERE action_information_id = l_table_value_id
1094 AND action_context_type = 'AAP'
1095 AND action_information_category = 'US PSD'
1096 AND jurisdiction_code = l_old_jurisdiction_code;
1097
1098 -- Added for bug 14213838.
1099 ELSIF l_table_name = 'FF_ARCHIVE_ITEM_CONTEXTS' THEN
1100
1101 UPDATE ff_archive_item_contexts
1102 SET context = l_new_jurisdiction_code
1103 WHERE archive_item_id = l_table_value_id
1104 AND context = l_old_jurisdiction_code;
1105
1106 END IF;
1107
1108 FETCH get_emp_upgrade_details INTO l_table_name,l_table_value_id,
1109 l_old_jurisdiction_code,l_new_jurisdiction_code,l_table_order;
1110 END LOOP;
1111
1112 IF (get_emp_upgrade_details%NOTFOUND) THEN
1113
1114 /*Bug14082784 Changes */
1115
1116 /*See if there are any duplicate records in PAY_US_ASG_REPORTING resulted due to
1117 the latest changes. In case, if any are present, remove the duplicate records
1118 to retain single record. Below delete statement achieves this.*/
1119
1120 DELETE FROM pay_us_asg_reporting
1121 WHERE ROWID IN (
1122 SELECT a.ROWID
1123 FROM pay_us_asg_reporting a,
1124 pay_us_asg_reporting b
1125 WHERE a.assignment_id = p_assignment_id
1126 AND a.assignment_id = b.assignment_id
1127 AND a.tax_unit_id=b.tax_unit_id
1128 AND a.jurisdiction_code=b.jurisdiction_code
1129 AND a.rowid > b.rowid);
1130
1131 /*End of Bug14082784 Changes */
1132
1133 /*As we are done with the processing of an assignment, we will update the status to P*/
1134
1135 UPDATE pay_us_geo_update
1136 SET status = 'P'
1137 WHERE id = p_id
1138 AND process_mode = p_mode
1139 AND process_date = p_date
1140 AND process_type = 'PA'
1141 AND assignment_id = p_assignment_id;
1142
1143 COMMIT;
1144
1145 END IF;
1146
1147 CLOSE get_emp_upgrade_details;
1148
1149 hr_utility.trace('Leaving pay_us_psd_upgrade_pkg.upgrade_assignment');
1150
1151 EXCEPTION
1152
1153 WHEN OTHERS THEN
1154
1155 UPDATE pay_us_geo_update
1156 SET status = 'E'
1157 WHERE id = p_id
1158 AND process_mode = p_mode
1159 AND process_date = p_date
1160 AND process_type = 'PA'
1161 AND assignment_id = p_assignment_id;
1162
1163 fnd_file.put_line(fnd_file.log,'Exception raised in pay_us_psd_upgrade_pkg.upgrade_assignment for Assignment ID '||p_assignment_id);
1164 hr_utility.trace('Exception raised in pay_us_psdcode_upgrade_pkg.upgrade_assignment');
1165 hr_utility.trace('SQLERRM = ' || SQLERRM);
1166 hr_utility.raise_error;
1167
1168 END upgrade_assignment;
1169
1170 PROCEDURE range_cursor (pactid IN NUMBER,
1171 sqlstr OUT NOCOPY VARCHAR2) IS
1172 --
1173 --
1174
1175 l_mode VARCHAR2(8);
1176 l_upgrade_needed VARCHAR2(2);
1177 l_id pay_patch_status.id%TYPE;
1178 l_date DATE;
1179
1180 BEGIN
1181
1182 hr_utility.trace('Entered pay_us_psd_upgrade_pkg.range_cursor');
1183
1184 /*Range cursor will be determined based on the necessity for upgrade*/
1185
1186 hr_utility.trace('Doing Initial Setup');
1187
1188 perform_initial_setup(pactid);
1189
1190 hr_utility.trace('End of Initial Setup');
1191
1192 l_upgrade_needed := upgrade_needed();
1193
1194 IF l_upgrade_needed = 'Y' THEN
1195
1196 /*Get the MODE of Execution for the PSDCODE Process*/
1197
1198 SELECT pay_us_geo_upd_pkg.get_parameter('MODE',ppa.legislative_parameters)
1199 INTO l_mode
1200 FROM pay_payroll_actions ppa
1201 WHERE ppa.payroll_action_id = pactid;
1202
1203 IF l_mode = 'GENERATE' THEN
1204
1205 /*Under GENERATE Mode, we need to identify the Assignments based on the city tax
1206 records. So the Range Cursor is based on pay_us_emp_city_tax_rules_f */
1207
1208 sqlstr := ' SELECT DISTINCT paf.person_id
1209 FROM per_all_assignments_f paf,
1210 pay_us_emp_city_tax_rules_f pect,
1211 pay_us_modified_geocodes pumg
1212 WHERE pumg.process_type = ''PA''
1213 AND pect.assignment_id = paf.assignment_id
1214 AND pect.state_code = pumg.state_code
1215 AND pect.county_code = pumg.county_code
1216 AND pect.city_code = pumg.old_city_code
1217 AND :pactid IS NOT NULL
1218 AND pay_us_psd_upgrade_pkg.upgrade_needed(pumg.state_code,
1219 pumg.county_code,
1220 pumg.old_city_code,
1221 pumg.city_name) = ''Y''
1222 ORDER BY paf.person_id';
1223
1224 ELSE
1225
1226 /*Under UPGRADE Mode, we already have Assignment changes present in pay_us_geo_update
1227 table. So we will use the pay_us_geo_update table as source for Range Cursor*/
1228
1229 SELECT id,applied_date
1230 INTO l_id,l_date
1231 FROM pay_patch_status
1232 WHERE patch_name LIKE 'PSDUPGRADE%'
1233 AND legislation_code = 'US'
1234 AND status = 'P'
1235 AND description = l_mode;
1236
1237 sqlstr := ' SELECT DISTINCT pugu.person_id
1238 FROM pay_us_geo_update pugu
1239 WHERE pugu.id = '||l_id||
1240 ' AND pugu.process_type = ''PA'' '||
1241 'AND pugu.process_mode = '''||l_mode||''' '||
1242 'AND pugu.process_date = TO_DATE('''||TO_CHAR(l_date,'DD-MM-YYYY')||''',''DD-MM-YYYY'')'||
1243 ' AND nvl(pugu.status,''P'') <> ''C''
1244 AND :pactid IS NOT NULL
1245 AND pugu.assignment_id > 0
1246 ORDER BY pugu.person_id';
1247
1248 END IF;
1249
1250 ELSE
1251
1252 sqlstr := ' SELECT distinct paf.person_id
1253 FROM per_all_assignments_f paf
1254 WHERE 1=2
1255 AND :pactid IS NOT NULL';
1256
1257 END IF;
1258
1259 hr_utility.trace('Range Cursor is');
1260 hr_utility.trace(sqlstr);
1261
1262 hr_utility.trace('Leaving pay_us_psd_upgrade_pkg.range_cursor');
1263
1264 END range_cursor;
1265
1266 PROCEDURE action_creation (pactid IN NUMBER,
1267 stperson IN NUMBER,
1268 endperson IN NUMBER,
1269 chunk IN NUMBER) IS
1270
1271 CURSOR c_mode (p_payroll_action_id number) IS
1272 SELECT pay_us_geo_upd_pkg.get_parameter('MODE',ppa.legislative_parameters)
1273 FROM pay_payroll_actions ppa
1274 WHERE ppa.payroll_action_id = p_payroll_action_id;
1275
1276 CURSOR c_generate_assignments
1277 (
1278 p_stperson NUMBER,
1279 p_endperson NUMBER
1280 ) IS
1281 SELECT DISTINCT ectr.assignment_id
1282 FROM per_all_assignments_f paf,
1283 pay_us_emp_city_tax_rules_f ectr,
1284 pay_us_modified_geocodes pumg
1285 WHERE pumg.state_code = ectr.state_code
1286 AND pumg.county_code = ectr.county_code
1287 AND pumg.old_city_code = ectr.city_code
1288 AND pumg.process_type = 'PA'
1289 AND ectr.assignment_id = paf.assignment_id
1290 AND paf.person_id BETWEEN p_stperson AND p_endperson
1291 AND pay_us_psd_upgrade_pkg.upgrade_needed(pumg.state_code,
1292 pumg.county_code,
1293 pumg.old_city_code,
1294 pumg.city_name)='Y';
1295
1296 CURSOR c_upgrade_assignments
1297 (
1298 p_stperson NUMBER,
1299 p_endperson NUMBER,
1300 p_id pay_us_geo_update.id%TYPE,
1301 p_date DATE,
1302 p_mode pay_us_geo_update.process_mode%TYPE
1303 ) IS
1304 SELECT DISTINCT pugu.assignment_id
1305 FROM pay_us_geo_update pugu,
1306 per_all_assignments_f paf
1307 WHERE pugu.id = p_id
1308 AND pugu.process_type = 'PA'
1309 AND pugu.process_mode = p_mode
1310 AND pugu.process_date = p_date
1311 AND nvl(pugu.status,'P') <> 'C'
1312 AND paf.assignment_id = pugu.assignment_id
1313 AND paf.assignment_id > 0
1314 AND paf.person_id BETWEEN p_stperson AND p_endperson
1315 ORDER BY pugu.assignment_id;
1316
1317 l_mode VARCHAR2(8);
1318 l_assignment_id NUMBER;
1319 l_payact_id NUMBER;
1320 l_locking_action_id NUMBER;
1321 l_id pay_patch_status.id%TYPE;
1322 l_date DATE;
1323
1324 BEGIN
1325
1326 hr_utility.trace('Entering pay_us_psd_upgrade_pkg.action_creation');
1327 hr_utility.trace('Action Creation called with below details');
1328 hr_utility.trace('pactid :'||pactid);
1329 hr_utility.trace('stperson :'||stperson);
1330 hr_utility.trace('endperson :'||endperson);
1331 hr_utility.trace('chunk :'||chunk);
1332
1333 OPEN c_mode(pactid);
1334
1335 FETCH c_mode INTO l_mode;
1336
1337 CLOSE c_mode;
1338
1339 hr_utility.trace('l_mode is '|| l_mode );
1340
1341 IF l_mode = 'GENERATE' THEN
1342
1343 OPEN c_generate_assignments(stperson,endperson);
1344 FETCH c_generate_assignments INTO l_assignment_id;
1345
1346 WHILE (c_generate_assignments%FOUND)
1347
1348 LOOP
1349
1350 SELECT pay_assignment_actions_s.nextval
1351 INTO l_locking_action_id
1352 FROM DUAL;
1353
1354 hr_nonrun_asact.insact(lockingactid => l_locking_action_id,
1355 object_Id => l_assignment_id,
1356 pactid => pactid,
1357 chunk => chunk,
1358 object_type => 'ASG');
1359
1360 hr_utility.trace('Created Assignment Action Id : '||l_locking_action_id||' for Assignment : '||l_assignment_id);
1361
1362 FETCH c_generate_assignments INTO l_assignment_id;
1363
1364 END LOOP;
1365
1366 ELSE
1367
1368 SELECT id,applied_date
1369 INTO l_id,l_date
1370 FROM pay_patch_status
1371 WHERE patch_name LIKE 'PSDUPGRADE%'
1372 AND legislation_code = 'US'
1373 AND status = 'P'
1374 AND description = l_mode;
1375
1376 OPEN c_upgrade_assignments(stperson,endperson,l_id,l_date,l_mode);
1377
1378 FETCH c_upgrade_assignments INTO l_assignment_id;
1379
1380 WHILE (c_upgrade_assignments%FOUND)
1381
1382 LOOP
1383
1384 SELECT pay_assignment_actions_s.nextval
1385 INTO l_locking_action_id
1386 FROM DUAL;
1387
1388 hr_nonrun_asact.insact(lockingactid => l_locking_action_id,
1389 object_Id => l_assignment_id,
1390 pactid => pactid,
1391 chunk => chunk,
1392 object_type => 'ASG');
1393
1394 hr_utility.trace('Created Assignment Action Id : '||l_locking_action_id||' for Assignment : '||l_assignment_id);
1395
1396 FETCH c_upgrade_assignments INTO l_assignment_id;
1397
1398 END LOOP;
1399
1400 END IF;
1401
1402 hr_utility.trace('Leaving pay_us_psd_upgrade_pkg.action_creation');
1403
1404 EXCEPTION
1405
1406 WHEN OTHERS THEN
1407
1408 fnd_file.put_line(fnd_file.log,'Exception raised in pay_us_psd_upgrade_pkg.action_creation');
1409 fnd_file.put_line(fnd_file.log,sqlerrm);
1410
1411 END action_creation;
1412
1413 PROCEDURE archive_code (p_xfr_action_id IN NUMBER,
1414 p_effective_date IN DATE) IS
1415 --
1416 --
1417 CURSOR c_xfr_info (p_assignment_action_id NUMBER) IS
1418 SELECT paa.payroll_action_id,
1419 paa.object_id,
1420 paa.object_type
1421 FROM pay_temp_object_actions paa
1422 WHERE paa.object_action_id = p_assignment_action_id;
1423
1424 CURSOR c_mode ( p_payroll_action_id NUMBER) IS
1425 SELECT pay_us_geo_upd_pkg.get_parameter('MODE',PPa.legislative_parameters)
1426 FROM pay_payroll_actions ppa
1427 WHERE ppa.payroll_action_id = p_payroll_action_id;
1428
1429 l_payroll_action_id pay_payroll_actions.payroll_action_id%TYPE;
1430 l_object_id pay_assignment_actions.object_id%TYPE;
1431 l_object_type pay_assignment_actions.object_type%TYPE;
1432 l_mode VARCHAR2(8);
1433 l_element_set_id pay_element_sets.element_set_id%TYPE;
1434 l_id pay_patch_status.id%TYPE;
1435 l_date DATE;
1436 l_upgrade_needed VARCHAR2(2);
1437
1438 BEGIN
1439
1440 l_upgrade_needed := upgrade_needed();
1441
1442 hr_utility.trace('Inside pay_us_psd_upgrade_pkg.archive_code for Assignment Action ID : '||p_xfr_action_id);
1443
1444 OPEN c_xfr_info (p_xfr_action_id);
1445
1446 FETCH c_xfr_info into l_payroll_action_id,
1447 l_object_id,
1448 l_object_type;
1449
1450 CLOSE c_xfr_info;
1451
1452 OPEN c_mode(l_payroll_action_id);
1453 FETCH c_mode into l_mode;
1454 close c_mode;
1455
1456 hr_utility.trace(' Mode : '||l_mode);
1457
1458 SELECT id,applied_date
1459 INTO l_id,l_date
1460 FROM pay_patch_status
1461 WHERE patch_name LIKE 'PSDUPGRADE%'
1462 AND legislation_code = 'US'
1463 AND status = 'P'
1464 AND description = l_mode;
1465
1466 hr_utility.trace(' PAY_PATCH_STATUS Id : '||l_id);
1467
1468 /*l_object_id will be assignment_id */
1469
1470 IF l_mode = 'GENERATE' THEN
1471
1472 SELECT element_set_id
1473 INTO l_element_set_id
1474 FROM pay_element_sets
1475 WHERE element_set_name = 'US_JSD_ELEMENTS_'||l_id
1476 AND legislation_code = 'US';
1477
1478 hr_utility.trace(' Element Set Id : '||l_element_set_id);
1479
1480 hr_utility.trace(' Object Type ASG , Calling Generate_Assignment '||l_object_id);
1481 generate_assignment(l_object_id,l_id,l_date,l_mode,l_element_set_id);
1482
1483 ELSE
1484
1485 hr_utility.trace(' Object Type ASG , Calling Upgrade_Assignment '||l_object_id);
1486 upgrade_assignment(l_object_id,l_id,l_date,l_mode);
1487
1488 END IF;
1489
1490 hr_utility.trace('Leaving pay_us_psd_upgrade_pkg.archive_code for Assignment Action ID : '||p_xfr_action_id);
1491
1492 EXCEPTION
1493
1494 WHEN OTHERS THEN
1495
1496 fnd_file.put_line(fnd_file.log,'Exception raised in pay_us_psd_upgrade_pkg.archive_code for Assignment Action ID '||p_xfr_action_id);
1497 fnd_file.put_line(fnd_file.log,'Error '||sqlerrm);
1498 hr_utility.raise_error;
1499
1500 END archive_code;
1501
1502 PROCEDURE invalidate_run_balances(p_mode VARCHAR2) IS
1503
1504 CURSOR get_run_balance_details(p_id NUMBER,
1505 p_date DATE,
1506 p_mode VARCHAR2) IS
1507 SELECT DISTINCT pbg.business_group_id,
1508 pbg.name,
1509 pbv.balance_validation_id
1510 FROM per_business_groups pbg,
1511 per_all_assignments_f paf,
1512 pay_us_geo_update pugu,
1513 pay_balance_types pbt,
1514 pay_defined_balances pdb,
1515 pay_balance_validation pbv
1516 WHERE pugu.id = p_id
1517 AND pugu.process_type = 'PA'
1518 AND pugu.process_mode = p_mode
1519 AND pugu.process_date = p_date
1520 AND decode(p_mode,'GENERATE','C',pugu.status) = 'C'
1521 AND pbg.business_group_id = paf.business_group_id
1522 AND pugu.assignment_id = paf.assignment_id
1523 AND pbg.legislation_code = 'US'
1524 AND pbt.balance_name in
1525 ('City PSD Subj Whable',
1526 'City PSD Withheld',
1527 'City RS PSD Subj Whable',
1528 'City RS PSD Withheld',
1529 'City WK PSD Subj Whable',
1530 'City WK PSD Withheld',
1531 'PSD Subj Whable',
1532 'PSD Withheld',
1533 'School PSD Withheld',
1534 'School RS PSD Withheld',
1535 'School WK PSD Withheld')
1536 AND pbt.balance_type_id = pdb.balance_type_id
1537 AND pdb.save_run_balance = 'Y'
1538 AND pbt.legislation_code = 'US'
1539 AND pdb.defined_balance_id = pbv.defined_balance_id
1540 AND pbv.business_group_id = pbg.business_group_id
1541 ORDER BY pbg.business_group_id;
1542
1543
1544 CURSOR get_defined_balance_details IS
1545 SELECT pbt.balance_name,pbd.dimension_name
1546 FROM pay_balance_types pbt,
1547 pay_balance_dimensions pbd,
1548 pay_defined_balances pdb
1549 WHERE pbt.balance_name in
1550 ('City PSD Subj Whable',
1551 'City PSD Withheld',
1552 'City RS PSD Subj Whable',
1553 'City RS PSD Withheld',
1554 'City WK PSD Subj Whable',
1555 'City WK PSD Withheld',
1556 'PSD Subj Whable',
1557 'PSD Withheld',
1558 'School PSD Withheld',
1559 'School RS PSD Withheld',
1560 'School WK PSD Withheld')
1561 AND pbt.balance_type_id = pdb.balance_type_id
1562 AND pdb.save_run_balance = 'Y'
1563 AND pbt.legislation_code = 'US'
1564 AND pdb.balance_dimension_id = pbd.balance_dimension_id
1565 ORDER BY pbt.balance_name,pbd.dimension_name;
1566
1567 l_upgrade_needed VARCHAR2(2);
1568 l_id pay_patch_status.id%TYPE;
1569 l_date DATE;
1570 l_business_group_id pay_balance_validation.business_group_id%TYPE;
1571 l_print_business_group_id pay_balance_validation.business_group_id%TYPE;
1572 l_business_group_name per_business_groups.name%TYPE;
1573 l_balance_name pay_balance_types.balance_name%TYPE;
1574 l_dimension_name pay_balance_dimensions.dimension_name%TYPE;
1575 l_balance_validation_id pay_balance_validation.balance_validation_id%TYPE;
1576 l_balance_load_date DATE;
1577
1578 BEGIN
1579
1580 hr_utility.trace('Entering pay_us_psd_upgrade_pkg.invalidate_run_balances');
1581
1582 l_print_business_group_id := -1;
1583
1584 SELECT id,applied_date
1585 INTO l_id,l_date
1586 FROM pay_patch_status
1587 WHERE patch_name LIKE 'PSDUPGRADE%'
1588 AND legislation_code = 'US'
1589 AND status = 'P'
1590 AND description = p_mode;
1591
1592 OPEN get_run_balance_details(l_id,l_date,p_mode);
1593
1594 FETCH get_run_balance_details INTO l_business_group_id,l_business_group_name,l_balance_validation_id;
1595
1596 IF get_run_balance_details%FOUND THEN
1597
1598 fnd_file.put_line(fnd_file.output,'<H3> Run Balance Invalidation </H3>');
1599
1600 IF p_mode = 'GENERATE' THEN
1601
1602 fnd_file.put_line(fnd_file.output,'<P> Run Balances related to below listed Defined Balances will be invalidated.</P>');
1603 fnd_file.put_line(fnd_file.output,'<P> Need to Run Generate Run Balances Concurrent Program to regenerate them after UPRADE Mode Run. </P>');
1604
1605 ELSIF p_mode = 'UPGRADE' THEN
1606
1607 fnd_file.put_line(fnd_file.output,'<P> Run Balances related to below listed Defined Balances are invalidated.</P>');
1608 fnd_file.put_line(fnd_file.output,'<P> Need to Run Generate Run Balances Concurrent Program to regenerate them.</P>');
1609
1610 END IF;
1611
1612 fnd_file.put_line(fnd_file.output,'<TABLE cellpadding="2" cellspacing="1" border="2" summary="Defined Balances Affected">');
1613 fnd_file.put_line(fnd_file.output,'<TH>Balance Name</TH>');
1614 fnd_file.put_line(fnd_file.output,'<TH>Dimension Name</TH>');
1615
1616 OPEN get_defined_balance_details;
1617 FETCH get_defined_balance_details INTO l_balance_name,l_dimension_name;
1618
1619 WHILE (get_defined_balance_details%FOUND)
1620 LOOP
1621
1622 fnd_file.put_line(fnd_file.output,'<TR><TD>'||l_balance_name||'</TD>');
1623 fnd_file.put_line(fnd_file.output,'<TD>'||l_dimension_name||'</TD></TR>');
1624
1625 FETCH get_defined_balance_details INTO l_balance_name,l_dimension_name;
1626
1627 END LOOP;
1628
1629 fnd_file.put_line(fnd_file.output,'</TABLE>');
1630
1631 CLOSE get_defined_balance_details;
1632
1633 IF p_mode = 'GENERATE' THEN
1634
1635 fnd_file.put_line(fnd_file.output,'<P> Below table indicates the Business Groups for which Run Balances will be invalidated. </P>');
1636
1637 ELSIF p_mode = 'UPGRADE' THEN
1638
1639 fnd_file.put_line(fnd_file.output,'<P> Below table indicates the Business Groups for which Run Balances are invalidated. </P>');
1640
1641 END IF;
1642
1643 fnd_file.put_line(fnd_file.output,'<TABLE cellpadding="2" cellspacing="1" border="2" summary="Business Groups Affected">');
1644 fnd_file.put_line(fnd_file.output,'<TR><TH>Business Group ID</TH>');
1645 fnd_file.put_line(fnd_file.output,'<TH>Business Group Name</TH>');
1646
1647 END IF;
1648
1649 WHILE(get_run_balance_details%FOUND)
1650 LOOP
1651
1652 IF l_print_business_group_id <> l_business_group_id THEN
1653
1654 fnd_file.put_line(fnd_file.output,'<TR><TD>'||l_business_group_id||'</TD>');
1655 fnd_file.put_line(fnd_file.output,'<TD>'||l_business_group_name||'</TD></TR>');
1656 l_print_business_group_id := l_business_group_id;
1657
1658 END IF;
1659
1660 IF p_mode = 'UPGRADE' THEN
1661
1662 UPDATE pay_balance_validation
1663 SET run_balance_status = 'I'
1664 WHERE balance_validation_id = l_balance_validation_id;
1665
1666 END IF;
1667
1668 FETCH get_run_balance_details INTO l_business_group_id,l_business_group_name,l_balance_validation_id;
1669
1670 END LOOP;
1671
1672 CLOSE get_run_balance_details;
1673
1674 hr_utility.trace('Leaving pay_us_psd_upgrade_pkg.invalidate_run_balances');
1675
1676 END invalidate_run_balances;
1677
1678 PROCEDURE deinitialization (p_payroll_action_id IN NUMBER) IS
1679 --
1680 --
1681
1682 CURSOR get_emp_counts(p_id NUMBER,p_mode VARCHAR2,p_date DATE,p_status VARCHAR2) IS
1683 SELECT COUNT(0)
1684 FROM (SELECT DISTINCT assignment_id
1685 FROM pay_us_geo_update pugu
1686 WHERE pugu.id = p_id
1687 AND pugu.process_type = 'PA'
1688 AND pugu.process_mode = p_mode
1689 AND pugu.process_date = p_date
1690 AND pugu.assignment_id > 0
1691 AND NVL(pugu.status,'NULL') = DECODE(p_status,'ALL',NVL(pugu.status,'NULL'),p_status));
1692
1693 CURSOR c_mode ( p_payroll_action_id NUMBER) IS
1694 SELECT pay_us_geo_upd_pkg.get_parameter('MODE',PPa.legislative_parameters)
1695 FROM pay_payroll_actions ppa
1696 WHERE ppa.payroll_action_id = p_payroll_action_id;
1697
1698 -- Added for bug 14213838.
1699 CURSOR get_psd_pact_id IS
1700 SELECT DISTINCT
1701 ppa.payroll_action_id
1702 FROM pay_payroll_actions ppa
1703 WHERE ppa.report_type='PSD_MAG_XML';
1704
1705 -- Added for bug 14213838.
1706 CURSOR get_hr_org_upgrade_details(p_mode VARCHAR2,p_date DATE) IS
1707 SELECT DISTINCT
1708 pugu.table_name,
1709 pugu.table_value_id,
1710 pugu.old_juri_code,
1711 pugu.new_juri_code
1712 FROM pay_us_geo_update pugu
1713 WHERE pugu.table_name = 'HR_ORGANIZATION_INFORMATION'
1714 AND pugu.process_type = 'PA'
1715 AND pugu.process_mode = p_mode
1716 AND pugu.process_date = p_date
1717 AND nvl(pugu.status,'P') <> 'C';
1718
1719 -- Added for bug 14213838.
1720 CURSOR get_ff_archive_upgrade_details(p_mode VARCHAR2,p_date DATE) IS
1721 SELECT DISTINCT
1722 pugu.table_name,
1723 pugu.table_value_id,
1724 pugu.old_juri_code,
1725 pugu.new_juri_code
1726 FROM pay_us_geo_update pugu
1727 WHERE pugu.table_name = 'FF_ARCHIVE_ITEMS'
1728 AND pugu.process_type = 'PA'
1729 AND pugu.process_mode = p_mode
1730 AND pugu.process_date = p_date
1731 AND nvl(pugu.status,'P') <> 'C';
1732
1733 l_upgrade_needed VARCHAR2(2);
1734 l_mode VARCHAR2(8);
1735 l_id pay_patch_status.id%TYPE;
1736 l_date DATE;
1737 l_total_assignments NUMBER;
1738 l_success_assignments NUMBER;
1739 l_error_assignments NUMBER;
1740 l_not_process_assignments NUMBER;
1741 -- Added for bug 14213838.
1742 l_table_name VARCHAR2(100);
1743 l_table_value_id NUMBER;
1744 l_old_jurisdiction_code VARCHAR2(16);
1745 l_new_jurisdiction_code VARCHAR2(16);
1746 l_psd_pact_id NUMBER;
1747 l_er_psd_entity_id NUMBER;
1748
1749 BEGIN
1750
1751 hr_utility.trace('Entering pay_us_psd_upgrade_pkg.deinitialization');
1752
1753 OPEN c_mode(p_payroll_action_id);
1754 FETCH c_mode into l_mode;
1755 close c_mode;
1756
1757 l_upgrade_needed := upgrade_needed();
1758
1759 IF l_upgrade_needed = 'Y' THEN
1760
1761 SELECT id,applied_date
1762 INTO l_id,l_date
1763 FROM pay_patch_status
1764 WHERE patch_name LIKE 'PSDUPGRADE%'
1765 AND legislation_code = 'US'
1766 AND status = 'P'
1767 AND description = l_mode;
1768
1769 IF l_mode = 'GENERATE' THEN
1770
1771 -- Added for bug 14213838.
1772
1773 /*Start of FF_ARCHIVE_ITEMS Section*/
1774
1775 SELECT user_entity_id
1776 INTO l_er_psd_entity_id
1777 FROM ff_user_entities
1778 WHERE user_entity_name='A_PSD_REPORTING_RULES_ORG_EMPLOYER_PSD_CODE';
1779
1780 OPEN get_psd_pact_id;
1781 LOOP
1782 FETCH get_psd_pact_id INTO l_psd_pact_id;
1783 EXIT WHEN get_psd_pact_id%NOTFOUND;
1784
1785 INSERT INTO pay_us_geo_update
1786 (id,
1787 assignment_id,
1788 table_value_id,
1789 table_name,
1790 old_juri_code,
1791 new_juri_code,
1792 process_type,
1793 process_date,
1794 process_mode,
1795 description)
1796 SELECT DISTINCT l_id
1797 ,l_psd_pact_id
1798 ,fai.archive_item_id
1799 ,'FF_ARCHIVE_ITEMS'
1800 ,fai.value
1801 ,replace(fai.value,substr(pumg.city_name,1,6),substr(pumg.city_name,8,6))
1802 ,pumg.process_type
1803 ,l_date
1804 ,l_mode
1805 ,substr(pumg.city_name,8,6)
1806 FROM ff_archive_items fai,
1807 pay_us_modified_geocodes pumg
1808 WHERE fai.context1 = l_psd_pact_id
1809 AND fai.user_entity_id = l_er_psd_entity_id
1810 AND fai.value= substr(pumg.city_name,1,6)
1811 AND pumg.process_type = 'PA'
1812 AND pay_us_psd_upgrade_pkg.upgrade_needed(pumg.state_code,
1813 pumg.county_code,
1814 pumg.old_city_code,
1815 pumg.city_name)='Y';
1816 END LOOP;
1817
1818 CLOSE get_psd_pact_id;
1819
1820 hr_utility.trace('Done with FF_ARCHIVE_ITEMS');
1821 /*End of FF_ARCHIVE_ITEMS Section*/
1822
1823 -- Added for bug 14213838.
1824 /*Start of HR_ORGANIZATION_INFORMATION Section*/
1825
1826 INSERT INTO pay_us_geo_update
1827 (id,
1828 assignment_id,
1829 table_value_id,
1830 table_name,
1831 old_juri_code,
1832 new_juri_code,
1833 process_type,
1834 process_date,
1835 process_mode,
1836 description)
1837 SELECT DISTINCT l_id
1838 ,hou.organization_id
1839 ,hoi.org_information_id
1840 ,'HR_ORGANIZATION_INFORMATION'
1841 ,hoi.org_information10
1842 ,replace(hoi.org_information10,substr(pumg.city_name,1,6),substr(pumg.city_name,8,6))
1843 ,pumg.process_type
1844 ,l_date
1845 ,l_mode
1846 ,substr(pumg.city_name,8,6)
1847 FROM hr_organization_information hoi,
1848 hr_organization_units hou,
1849 pay_us_modified_geocodes pumg
1850 WHERE hoi.org_information_context='PSD Reporting Rules'
1851 AND substr(hoi.org_information10,1,6) = substr(pumg.city_name,1,6)
1852 AND pumg.process_type = 'PA'
1853 AND hou.organization_id =hoi.organization_id
1854 AND pay_us_psd_upgrade_pkg.upgrade_needed(pumg.state_code,
1855 pumg.county_code,
1856 pumg.old_city_code,
1857 pumg.city_name)='Y';
1858
1859 hr_utility.trace('Done with HR_ORGANIZATION_INFORMATION');
1860
1861 /*End of HR_ORGANIZATION_INFORMATION Section*/
1862 pay_us_psd_upgrade_pkg.generate_output(l_mode);
1863
1864 invalidate_run_balances(l_mode);
1865
1866 DELETE FROM pay_element_type_rules
1867 WHERE element_set_id IN
1868 (SELECT element_set_id
1869 FROM pay_element_sets
1870 WHERE element_set_name = 'US_JSD_ELEMENTS_'||l_id);
1871
1872 DELETE FROM pay_element_sets
1873 WHERE legislation_code = 'US'
1874 AND element_set_name = 'US_JSD_ELEMENTS_'||l_id;
1875
1876 UPDATE pay_patch_status
1877 SET phase = NULL,
1878 status = 'C'
1879 WHERE legislation_code = 'US'
1880 AND patch_name like 'PSDUPGRADE%'
1881 AND status = 'P';
1882
1883 ELSE
1884
1885 -- Added for bug 14213838.
1886 OPEN get_hr_org_upgrade_details(l_mode,l_date);
1887 LOOP
1888 FETCH get_hr_org_upgrade_details INTO l_table_name,l_table_value_id,
1889 l_old_jurisdiction_code,l_new_jurisdiction_code;
1890 EXIT WHEN get_hr_org_upgrade_details%NOTFOUND;
1891
1892 UPDATE hr_organization_information
1893 SET org_information10 = l_new_jurisdiction_code
1894 WHERE org_information_id = l_table_value_id
1895 AND org_information10 = l_old_jurisdiction_code;
1896
1897 UPDATE pay_us_geo_update
1898 SET status = 'P'
1899 WHERE id = l_id
1900 AND process_mode = l_mode
1901 AND process_date = l_date
1902 AND process_type = 'PA'
1903 AND table_value_id = l_table_value_id;
1904
1905 COMMIT;
1906
1907 END LOOP;
1908
1909 CLOSE get_hr_org_upgrade_details;
1910
1911 -- Added for bug 14213838.
1912 OPEN get_ff_archive_upgrade_details(l_mode,l_date);
1913 LOOP
1914 FETCH get_ff_archive_upgrade_details INTO l_table_name,l_table_value_id,
1915 l_old_jurisdiction_code,l_new_jurisdiction_code;
1916 EXIT WHEN get_ff_archive_upgrade_details%NOTFOUND;
1917
1918 UPDATE ff_archive_items
1919 SET value = l_new_jurisdiction_code
1920 WHERE archive_item_id = l_table_value_id
1921 AND value = l_old_jurisdiction_code;
1922
1923 UPDATE pay_us_geo_update
1924 SET status = 'P'
1925 WHERE id = l_id
1926 AND process_mode = l_mode
1927 AND process_date = l_date
1928 AND process_type = 'PA'
1929 AND table_value_id = l_table_value_id;
1930
1931 COMMIT;
1932
1933 END LOOP;
1934
1935 CLOSE get_ff_archive_upgrade_details;
1936
1937 UPDATE pay_us_geo_update
1938 SET status = 'C'
1939 WHERE id = l_id
1940 AND process_type = 'PA'
1941 AND process_mode = l_mode
1942 AND process_date = l_date
1943 AND status = 'P';
1944
1945 UPDATE pay_us_geo_update
1946 SET status = 'E'
1947 WHERE id = l_id
1948 AND process_type = 'PA'
1949 AND process_mode = l_mode
1950 AND process_date = l_date
1951 AND assignment_id IN
1952 (SELECT object_id
1953 FROM pay_temp_object_actions
1954 WHERE payroll_action_id = p_payroll_action_id
1955 AND action_status = 'E');
1956
1957 pay_us_psd_upgrade_pkg.generate_output(l_mode);
1958
1959 invalidate_run_balances(l_mode);
1960
1961 OPEN get_emp_counts(l_id,l_mode,l_date,'ALL');
1962 FETCH get_emp_counts INTO l_total_assignments;
1963 CLOSE get_emp_counts;
1964
1965 OPEN get_emp_counts(l_id,l_mode,l_date,'C');
1966 FETCH get_emp_counts INTO l_success_assignments;
1967 CLOSE get_emp_counts;
1968
1969 OPEN get_emp_counts(l_id,l_mode,l_date,'E');
1970 FETCH get_emp_counts INTO l_error_assignments;
1971 CLOSE get_emp_counts;
1972
1973 IF l_total_assignments = l_success_assignments
1974 AND l_error_assignments = 0 THEN
1975
1976 INSERT INTO pay_us_geo_update
1977 (id,
1978 assignment_id,
1979 old_juri_code,
1980 new_juri_code,
1981 process_type,
1982 process_date,
1983 process_mode,
1984 status)
1985 SELECT l_id,
1986 -1,
1987 substr(pumg.city_name,1,6),
1988 substr(pumg.city_name,8,6),
1989 'PA',
1990 l_date,
1991 l_mode,
1992 'C'
1993 FROM pay_us_modified_geocodes pumg
1994 WHERE pumg.process_type = 'PA'
1995 AND pay_us_psd_upgrade_pkg.upgrade_needed(pumg.state_code,
1996 pumg.county_code,
1997 pumg.old_city_code,
1998 pumg.city_name)='Y';
1999
2000 UPDATE pay_patch_status
2001 SET phase = NULL,
2002 status = 'C'
2003 WHERE legislation_code = 'US'
2004 AND patch_name like 'PSDUPGRADE%'
2005 AND status = 'P'
2006 AND description = l_mode;
2007
2008 ELSE
2009
2010 UPDATE pay_patch_status
2011 SET phase = NULL,
2012 status = 'E'
2013 WHERE legislation_code = 'US'
2014 AND patch_name like 'PSDUPGRADE%'
2015 AND status = 'P'
2016 AND description = l_mode;
2017
2018 END IF;
2019
2020 /*End of UPGRADE Mode */
2021
2022 END IF; /* l_mode IF */
2023
2024 ELSE
2025
2026 pay_us_psd_upgrade_pkg.generate_output(l_mode);
2027
2028 END IF; /* l_upgrade_needed IF*/
2029
2030 hr_utility.trace('Leaving pay_us_psd_upgrade_pkg.deinitialization');
2031
2032 END deinitialization;
2033
2034 PROCEDURE generate_output(p_mode VARCHAR2) IS
2035
2036 CURSOR get_psdcode_details IS
2037 SELECT substr(pumg.city_name,1,6),
2038 substr(pumg.city_name,8,6),
2039 substr(pumg.city_name,15,5),
2040 state_code||'-'||county_code||'-'||old_city_code
2041 FROM pay_us_modified_geocodes pumg
2042 WHERE pumg.process_type = 'PA'
2043 AND pay_us_psd_upgrade_pkg.upgrade_needed(pumg.state_code,
2044 pumg.county_code,
2045 pumg.old_city_code,
2046 pumg.city_name)='Y'
2047 ORDER BY state_code,county_code,old_city_code;
2048
2049 CURSOR get_emp_update_details(p_id NUMBER,p_mode VARCHAR2,p_date DATE) IS
2050 SELECT DISTINCT
2051 substr(ppf.full_name,1,100),
2052 pugu.assignment_id,
2053 pugu.old_juri_code,
2054 pugu.new_juri_code,
2055 pugu.table_name
2056 FROM pay_us_geo_update pugu,
2057 per_all_people_f ppf
2058 WHERE pugu.id = p_id
2059 AND pugu.process_type = 'PA'
2060 AND pugu.process_mode = p_mode
2061 AND pugu.process_date = p_date
2062 AND pugu.person_id = ppf.person_id
2063 AND NVL(pugu.status,'NULL') = decode(p_mode,'GENERATE','NULL','UPGRADE','C')
2064 AND NOT EXISTS (
2065 SELECT NULL
2066 FROM per_all_people_f ppf1
2067 WHERE ppf1.person_id =ppf.person_id
2068 AND ppf1.effective_end_date > ppf.effective_end_date
2069 )
2070 GROUP BY ppf.full_name,pugu.assignment_id,pugu.old_juri_code,
2071 pugu.new_juri_code,pugu.table_name
2072 ORDER BY pugu.assignment_id,pugu.old_juri_code,pugu.new_juri_code;
2073
2074 -- Added for bug 14213838.
2075 CURSOR get_gre_update_details(p_id NUMBER,p_mode VARCHAR2,p_date DATE) IS
2076 SELECT DISTINCT
2077 substr(hou.name,1,100),
2078 hou.organization_id,
2079 substr(pugu.old_juri_code,1,6),
2080 substr(pugu.new_juri_code,1,6),
2081 pugu.table_name
2082 FROM pay_us_geo_update pugu,
2083 hr_organization_information hoi,
2084 hr_organization_units hou
2085 WHERE pugu.id = p_id
2086 AND pugu.process_type = 'PA'
2087 AND pugu.process_mode = p_mode
2088 AND pugu.process_date = p_date
2089 AND pugu.table_value_id=hoi.org_information_id
2090 AND hoi.organization_id=hou.organization_id
2091 AND NVL(pugu.status,'NULL') = decode(p_mode,'GENERATE','NULL','UPGRADE','C')
2092 GROUP BY hou.name,hou.organization_id,pugu.old_juri_code,
2093 pugu.new_juri_code,pugu.table_name
2094 ORDER BY 2, 3,4;
2095
2096 CURSOR get_emp_in_error(p_id NUMBER,p_mode VARCHAR2,p_date DATE) IS
2097 SELECT DISTINCT
2098 substr(ppf.full_name,1,100),
2099 pugu.assignment_id
2100 FROM pay_us_geo_update pugu,
2101 per_all_people_f ppf
2102 WHERE pugu.id = p_id
2103 AND pugu.process_type = 'PA'
2104 AND pugu.process_mode = p_mode
2105 AND pugu.process_date = p_date
2106 AND pugu.person_id = ppf.person_id
2107 AND NVL(pugu.status,'X') ='E'
2108 AND NOT EXISTS (
2109 SELECT NULL
2110 FROM per_all_people_f ppf1
2111 WHERE ppf1.person_id =ppf.person_id
2112 AND ppf1.effective_end_date > ppf.effective_end_date
2113 )
2114 GROUP BY ppf.full_name,pugu.assignment_id
2115 ORDER BY pugu.assignment_id;
2116
2117 -- Added for bug 14213838.
2118 CURSOR get_gre_in_error(p_id NUMBER,p_mode VARCHAR2,p_date DATE) IS
2119 SELECT DISTINCT
2120 substr(hou.name,1,100),
2121 hou.organization_id
2122 FROM pay_us_geo_update pugu,
2123 hr_organization_information hoi,
2124 hr_organization_units hou
2125 WHERE pugu.id = p_id
2126 AND pugu.process_type = 'PA'
2127 AND pugu.process_mode = p_mode
2128 AND pugu.process_date = p_date
2129 AND pugu.table_value_id=hoi.org_information_id
2130 AND hoi.organization_id=hou.organization_id
2131 AND NVL(pugu.status,'X') ='E'
2132 GROUP BY hou.name,hou.organization_id
2133 ORDER BY hou.organization_id;
2134
2135 CURSOR get_emp_counts(p_id NUMBER,p_mode VARCHAR2,p_date DATE,p_status VARCHAR2) IS
2136 SELECT COUNT(0)
2137 FROM (SELECT DISTINCT assignment_id
2138 FROM pay_us_geo_update pugu
2139 WHERE pugu.id = p_id
2140 AND pugu.process_type = 'PA'
2141 AND pugu.process_mode = p_mode
2142 AND pugu.process_date = p_date
2143 AND pugu.assignment_id > 0
2144 AND pugu.table_name not in ('HR_ORGANIZATION_INFORMATION', 'FF_ARCHIVE_ITEMS')
2145 AND NVL(pugu.status,'NULL') = DECODE(p_status,'ALL',NVL(pugu.status,'NULL'),p_status));
2146
2147 -- Added for bug 14213838.
2148 CURSOR get_gre_counts(p_id NUMBER,p_mode VARCHAR2,p_date DATE,p_status VARCHAR2) IS
2149 SELECT COUNT(0)
2150 FROM (SELECT DISTINCT assignment_id
2151 FROM pay_us_geo_update pugu
2152 WHERE pugu.id = p_id
2153 AND pugu.process_type = 'PA'
2154 AND pugu.process_mode = p_mode
2155 AND pugu.process_date = p_date
2156 AND pugu.assignment_id > 0
2157 AND pugu.table_name ='HR_ORGANIZATION_INFORMATION'
2158 AND NVL(pugu.status,'NULL') = DECODE(p_status,'ALL',NVL(pugu.status,'NULL'),p_status));
2159
2160 -- Added for bug 14213838.
2161 CURSOR get_ff_archive_counts(p_id NUMBER,p_mode VARCHAR2,p_date DATE,p_status VARCHAR2) IS
2162 SELECT COUNT(0)
2163 FROM (SELECT DISTINCT table_value_id
2164 FROM pay_us_geo_update pugu
2165 WHERE pugu.id = p_id
2166 AND pugu.process_type = 'PA'
2167 AND pugu.process_mode = p_mode
2168 AND pugu.process_date = p_date
2169 AND pugu.assignment_id > 0
2170 AND pugu.table_name ='FF_ARCHIVE_ITEMS'
2171 AND NVL(pugu.status,'NULL') = DECODE(p_status,'ALL',NVL(pugu.status,'NULL'),p_status));
2172
2173 l_upgrade_needed VARCHAR2(2);
2174 l_id pay_patch_status.id%TYPE;
2175 l_date DATE;
2176 l_old_psd_code VARCHAR2(6);
2177 l_new_psd_code VARCHAR2(6);
2178 l_school_dsts_code VARCHAR2(5);
2179 l_jurisdiction_code VARCHAR2(11);
2180 l_full_name per_all_people_f.full_name%TYPE;
2181 l_emp_assignment_id NUMBER;
2182 l_old_jurisdiction_code VARCHAR2(16);
2183 l_new_jurisdiction_code VARCHAR2(16);
2184 l_table_name VARCHAR2(100);
2185 l_print_full_name per_all_people_f.full_name%TYPE;
2186 l_print_emp_assignment_id NUMBER;
2187 l_print_old_juri_code VARCHAR2(16);
2188 l_print_new_juri_code VARCHAR2(16);
2189 l_pay_us_asg_reporting VARCHAR2(10);
2190 l_pay_element_entry_values_f VARCHAR2(10);
2191 l_pay_run_results VARCHAR2(10);
2192 l_pay_run_result_values VARCHAR2(10);
2193 l_pay_action_information VARCHAR2(10);
2194 l_total_assignments NUMBER;
2195 l_success_assignments NUMBER;
2196 l_error_assignments NUMBER;
2197 l_not_process_assignments NUMBER;
2198
2199 -- Added for bug 14213838.
2200 l_gre_name hr_organization_units.name%TYPE;
2201 l_gre_id NUMBER;
2202 l_print_gre_name hr_organization_units.name%TYPE;
2203 l_print_gre_id NUMBER;
2204 l_ff_archive_item_contexts VARCHAR2(10);
2205 l_hr_organization_information VARCHAR2(10);
2206 l_total_gre_changes NUMBER;
2207 l_success_gre_changes NUMBER;
2208 l_error_gre_changes NUMBER;
2209 l_not_process_gre_changes NUMBER;
2210 l_total_ff_arch_changes NUMBER;
2211 l_success_ff_arch_changes NUMBER;
2212 l_error_ff_arch_changes NUMBER;
2213 l_not_process_ff_arch_changes NUMBER;
2214
2215 BEGIN
2216
2217 hr_utility.trace('Entering pay_us_psd_upgrade_pkg.generate_output');
2218
2219 l_upgrade_needed := upgrade_needed();
2220
2221 fnd_file.put_line(fnd_file.output,'<HTML><TITLE>PSDCODE Upgrade Details</TITLE>');
2222 fnd_file.put_line(fnd_file.output,'<HTML><H2><Center>PSDCODE Upgrade Details</Center></H2>');
2223
2224 IF l_upgrade_needed = 'Y' THEN
2225
2226 SELECT id,applied_date
2227 INTO l_id,l_date
2228 FROM pay_patch_status
2229 WHERE patch_name LIKE 'PSDUPGRADE%'
2230 AND legislation_code = 'US'
2231 AND status = 'P'
2232 AND description = p_mode;
2233
2234 fnd_file.put_line(fnd_file.output,'<HTML><P> Process Date : '||to_char(l_date,'DD-MON-YYYY HH:MM:SS')||' </P>');
2235 fnd_file.put_line(fnd_file.output,'<HTML><P> Process Mode : '||p_mode||' </P>');
2236 fnd_file.put_line(fnd_file.output,'<HTML><H3>List of PSDCODES that needs upgrade</H3>');
2237 fnd_file.put_line(fnd_file.output,'<TABLE cellpadding="2" cellspacing="1" border="2" summary="PSDCODES that need upgrade">');
2238 fnd_file.put_line(fnd_file.output,'<TR><TH>Jurisdiction Code</TH>');
2239 fnd_file.put_line(fnd_file.output,'<TH>School District Code</TH>');
2240 fnd_file.put_line(fnd_file.output,'<TH>Old PSDCODE</TH>');
2241 fnd_file.put_line(fnd_file.output,'<TH>New PSDCODE</TH></TR>');
2242
2243 OPEN get_psdcode_details;
2244 FETCH get_psdcode_details INTO l_old_psd_code,l_new_psd_code,l_school_dsts_code,l_jurisdiction_code;
2245
2246 WHILE (get_psdcode_details%FOUND)
2247 LOOP
2248
2249 fnd_file.put_line(fnd_file.output,'<TR><TD>'||l_jurisdiction_code||'</TD><TD>'||l_school_dsts_code||'</TD><TD>'||l_old_psd_code||'</TD><TD>'||l_new_psd_code||'</TD></TR>');
2250
2251 FETCH get_psdcode_details INTO l_old_psd_code,l_new_psd_code,l_school_dsts_code,l_jurisdiction_code;
2252
2253 END LOOP;
2254
2255 CLOSE get_psdcode_details;
2256
2257 fnd_file.put_line(fnd_file.output,'</TABLE>');
2258
2259 /* Start of Employee Update related details printing*/
2260
2261 IF p_mode = 'GENERATE' THEN
2262 fnd_file.put_line(fnd_file.output,'<HTML><H3>List down employees for whom Jurisdiction code and relevant tables are to be updated </H3>');
2263 ELSE
2264 fnd_file.put_line(fnd_file.output,'<HTML><H3>List down employees for whom Jurisdiction code and relevant tables were updated </H3>');
2265 END IF;
2266
2267 fnd_file.put_line(fnd_file.output,'<HTML><H4>Abbrevations for Table Names</H4>');
2268 fnd_file.put_line(fnd_file.output,'<TABLE cellpadding="2" cellspacing="1" border="2" summary="Table showing Abbrevations for Table Names">');
2269 fnd_file.put_line(fnd_file.output,'<TR><TH>Table Name</TH><TH>Abbrevation</TH></TR>');
2270 fnd_file.put_line(fnd_file.output,'<TR><TD>PAY_US_ASG_REPORTING</TD><TD>PUAR</TD></TR>');
2271 fnd_file.put_line(fnd_file.output,'<TR><TD>PAY_ELEMENT_ENTRY_VALUES_F</TD><TD>PEEV</TD></TR>');
2272 fnd_file.put_line(fnd_file.output,'<TR><TD>PAY_RUN_RESULTS</TD><TD>PRR</TD></TR>');
2273 fnd_file.put_line(fnd_file.output,'<TR><TD>PAY_RUN_RESULT_VALUES</TD><TD>PRRV</TD></TR>');
2274 fnd_file.put_line(fnd_file.output,'<TR><TD>PAY_ACTION_INFORMATION</TD><TD>PAI</TD></TR>');
2275 fnd_file.put_line(fnd_file.output,'<TR><TD>FF_ARCHIVE_ITEM_CONTEXTS</TD><TD>FAIC</TD></TR>'); -- Added for bug 14213838.
2276 fnd_file.put_line(fnd_file.output,'</TABLE>');
2277
2278 fnd_file.put_line(fnd_file.output,'<HTML><H4>Update Details </H4>');
2279 fnd_file.put_line(fnd_file.output,'<TABLE cellpadding="2" cellspacing="1" border="2" summary="Employee Data that needs upgrade">');
2280 fnd_file.put_line(fnd_file.output,'<TR><TH colspan=2>Employee Details</TH><TH colspan=2>Jurisdiction Details</TH><TH colspan=6>Tables Updated</TH>');
2281 fnd_file.put_line(fnd_file.output,'<TR><TH>Full Name</TH>');
2282 fnd_file.put_line(fnd_file.output,'<TH>Assignment ID</TH>');
2283 fnd_file.put_line(fnd_file.output,'<TH>Old Jurisdiction Code</TH>');
2284 fnd_file.put_line(fnd_file.output,'<TH>New Jurisdiction Code</TH>');
2285 fnd_file.put_line(fnd_file.output,'<TH>PUAR</TH>');
2286 fnd_file.put_line(fnd_file.output,'<TH>PEEV</TH>');
2287 fnd_file.put_line(fnd_file.output,'<TH>PRR</TH>');
2288 fnd_file.put_line(fnd_file.output,'<TH>PRRV</TH>');
2289 fnd_file.put_line(fnd_file.output,'<TH>PAI</TH>');
2290 fnd_file.put_line(fnd_file.output,'<TH>FAIC</TH></TR>'); -- Added for bug 14213838.
2291
2292 OPEN get_emp_update_details(l_id,p_mode,l_date);
2293 FETCH get_emp_update_details INTO l_full_name,l_emp_assignment_id,l_old_jurisdiction_code,l_new_jurisdiction_code,l_table_name;
2294
2295 l_print_full_name := l_full_name;
2296 l_print_emp_assignment_id := l_emp_assignment_id;
2297 l_print_old_juri_code := l_old_jurisdiction_code;
2298 l_print_new_juri_code := l_new_jurisdiction_code;
2299 l_pay_us_asg_reporting := 'No';
2300 l_pay_element_entry_values_f := 'No';
2301 l_pay_run_results := 'No';
2302 l_pay_run_result_values := 'No';
2303 l_pay_action_information := 'No';
2304 l_ff_archive_item_contexts := 'No'; -- Added for bug 14213838.
2305
2306 WHILE (get_emp_update_details%FOUND)
2307 LOOP
2308
2309 IF (l_print_full_name = l_full_name) AND (l_print_emp_assignment_id = l_emp_assignment_id)
2310 AND (l_print_old_juri_code = l_old_jurisdiction_code) AND (l_print_new_juri_code = l_new_jurisdiction_code) THEN
2311
2312 /*Same employee record for another table. Update the corresponing table flag.*/
2313
2314 IF l_table_name = 'PAY_US_ASG_REPORTING' THEN
2315
2316 l_pay_us_asg_reporting := 'Yes';
2317
2318 ELSIF l_table_name = 'PAY_ELEMENT_ENTRY_VALUES_F' THEN
2319
2320 l_pay_element_entry_values_f := 'Yes';
2321
2322 ELSIF l_table_name = 'PAY_RUN_RESULTS' THEN
2323
2324 l_pay_run_results := 'Yes';
2325
2326 ELSIF l_table_name = 'PAY_RUN_RESULT_VALUES' THEN
2327
2328 l_pay_run_result_values := 'Yes';
2329
2330 ELSIF l_table_name = 'PAY_ACTION_INFORMATION' THEN
2331
2332 l_pay_action_information := 'Yes';
2333
2334 -- Added for bug 14213838.
2335 ELSIF l_table_name = 'FF_ARCHIVE_ITEM_CONTEXTS' THEN
2336
2337 l_ff_archive_item_contexts := 'Yes';
2338
2339 END IF;
2340
2341 ELSE
2342
2343 /*New Employee/New Assignment/New Jurisdiction so print the existing employee details first*/
2344
2345 fnd_file.put_line(fnd_file.output,'<TR><TD>'||l_print_full_name||'</TD><TD>'||l_print_emp_assignment_id||'</TD><TD>'||l_print_old_juri_code||'</TD><TD>'||l_print_new_juri_code||'</TD>');
2346 fnd_file.put_line(fnd_file.output,'<TD>'||l_pay_us_asg_reporting||'</TD><TD>'||l_pay_element_entry_values_f||'</TD><TD>'||l_pay_run_results||'</TD><TD>'||l_pay_run_result_values||'</TD>');
2347 fnd_file.put_line(fnd_file.output,'<TD>'||l_pay_action_information||'</TD><TD>'||l_ff_archive_item_contexts||'</TD></TR>');
2348
2349 l_print_full_name := l_full_name;
2350 l_print_emp_assignment_id := l_emp_assignment_id;
2351 l_print_old_juri_code := l_old_jurisdiction_code;
2352 l_print_new_juri_code := l_new_jurisdiction_code;
2353 l_pay_us_asg_reporting := 'No';
2354 l_pay_element_entry_values_f := 'No';
2355 l_pay_run_results := 'No';
2356 l_pay_run_result_values := 'No';
2357 l_pay_action_information := 'No';
2358 l_ff_archive_item_contexts :='No'; -- Added for bug 14213838.
2359
2360 IF l_table_name = 'PAY_US_ASG_REPORTING' THEN
2361
2362 l_pay_us_asg_reporting := 'Yes';
2363
2364 ELSIF l_table_name = 'PAY_ELEMENT_ENTRY_VALUES_F' THEN
2365
2366 l_pay_element_entry_values_f := 'Yes';
2367
2368 ELSIF l_table_name = 'PAY_RUN_RESULTS' THEN
2369
2370 l_pay_run_results := 'Yes';
2371
2372 ELSIF l_table_name = 'PAY_RUN_RESULT_VALUES' THEN
2373
2374 l_pay_run_result_values := 'Yes';
2375
2376 ELSIF l_table_name = 'PAY_ACTION_INFORMATION' THEN
2377
2378 l_pay_action_information := 'Yes';
2379
2380 -- Added for bug 14213838.
2381 ELSIF l_table_name = 'FF_ARCHIVE_ITEM_CONTEXTS' THEN
2382
2383 l_ff_archive_item_contexts := 'Yes';
2384
2385
2386 END IF;
2387
2388 END IF;
2389
2390 FETCH get_emp_update_details INTO l_full_name,l_emp_assignment_id,l_old_jurisdiction_code,l_new_jurisdiction_code,l_table_name;
2391
2392 IF (get_emp_update_details%NOTFOUND) THEN
2393
2394 /*No more records exist. Print the last employee details*/
2395
2396 fnd_file.put_line(fnd_file.output,'<TR><TD>'||l_print_full_name||'</TD><TD>'||l_print_emp_assignment_id||'</TD><TD>'||l_print_old_juri_code||'</TD><TD>'||l_print_new_juri_code||'</TD>');
2397 fnd_file.put_line(fnd_file.output,'<TD>'||l_pay_us_asg_reporting||'</TD><TD>'||l_pay_element_entry_values_f||'</TD><TD>'||l_pay_run_results||'</TD><TD>'||l_pay_run_result_values||'</TD>');
2398 fnd_file.put_line(fnd_file.output,'<TD>'||l_pay_action_information||'</TD><TD>'||l_ff_archive_item_contexts||'</TD></TR>');
2399 END IF;
2400
2401 END LOOP;
2402
2403 CLOSE get_emp_update_details;
2404
2405 fnd_file.put_line(fnd_file.output,'</TABLE>');
2406
2407 /* End of Employee Update related details printing*/
2408
2409 -- Added for bug 14213838.
2410 /* Start of GRE Update related details printing*/
2411
2412 IF p_mode = 'GENERATE' THEN
2413 fnd_file.put_line(fnd_file.output,'<HTML><H3>List down GREs for whom PSD Code and relevant tables are to be updated </H3>');
2414 ELSE
2415 fnd_file.put_line(fnd_file.output,'<HTML><H3>List down GREs for whom PSD Code and relevant tables were updated </H3>');
2416 END IF;
2417
2418 fnd_file.put_line(fnd_file.output,'<HTML><H4>Abbrevation for Table Name</H4>');
2419 fnd_file.put_line(fnd_file.output,'<TABLE cellpadding="2" cellspacing="1" border="2" summary="Table showing Abbrevations for Table Names">');
2420 fnd_file.put_line(fnd_file.output,'<TR><TH>Table Name</TH><TH>Abbrevation</TH></TR>');
2421 fnd_file.put_line(fnd_file.output,'<TR><TD>HR_ORGANIZATION_INFORMATION</TD><TD>HOI</TD></TR>');
2422 fnd_file.put_line(fnd_file.output,'</TABLE>');
2423
2424 fnd_file.put_line(fnd_file.output,'<HTML><H4>Update Details </H4>');
2425 fnd_file.put_line(fnd_file.output,'<TABLE cellpadding="2" cellspacing="1" border="2" summary="Employee Data that needs upgrade">');
2426 fnd_file.put_line(fnd_file.output,'<TR><TH colspan=2>Governement Reporting Entity Details</TH><TH colspan=2>PSD Code Details</TH><TH colspan=6>Tables Updated</TH>');
2427 fnd_file.put_line(fnd_file.output,'<TR><TH>GRE Name</TH>');
2428 fnd_file.put_line(fnd_file.output,'<TH>GRE ID</TH>');
2429 fnd_file.put_line(fnd_file.output,'<TH>Old PSD Code</TH>');
2430 fnd_file.put_line(fnd_file.output,'<TH>New PSD Code</TH>');
2431 fnd_file.put_line(fnd_file.output,'<TH>HOI</TH></TR>');
2432
2433 OPEN get_gre_update_details(l_id,p_mode,l_date);
2434 FETCH get_gre_update_details INTO l_gre_name, l_gre_id, l_old_jurisdiction_code,l_new_jurisdiction_code,l_table_name;
2435
2436 l_print_gre_name := l_gre_name;
2437 l_print_gre_id := l_gre_id;
2438 l_print_old_juri_code := l_old_jurisdiction_code;
2439 l_print_new_juri_code := l_new_jurisdiction_code;
2440 l_hr_organization_information :='No';
2441
2442 WHILE (get_gre_update_details%FOUND)
2443 LOOP
2444
2445 IF (l_print_gre_name = l_gre_name) AND (l_print_gre_id = l_gre_id)
2446 AND (l_print_old_juri_code = l_old_jurisdiction_code) AND (l_print_new_juri_code = l_new_jurisdiction_code) THEN
2447
2448 /*Same GRE record for another table. Update the corresponing table flag.*/
2449
2450 IF l_table_name = 'HR_ORGANIZATION_INFORMATION' THEN
2451
2452 l_hr_organization_information := 'Yes';
2453
2454 END IF;
2455
2456 ELSE
2457
2458 /*New GRE/New PSD Code so print the existing GRE details first*/
2459
2460 fnd_file.put_line(fnd_file.output,'<TR><TD>'||l_print_gre_name||'</TD><TD>'||l_print_gre_id||'</TD><TD>'||l_print_old_juri_code||'</TD><TD>'||l_print_new_juri_code||'</TD>');
2461 fnd_file.put_line(fnd_file.output,'<TD>'||l_hr_organization_information||'</TD></TR>');
2462
2463 l_print_gre_name := l_gre_name;
2464 l_print_gre_id := l_gre_id;
2465 l_print_old_juri_code := l_old_jurisdiction_code;
2466 l_print_new_juri_code := l_new_jurisdiction_code;
2467 l_hr_organization_information :='No';
2468
2469 IF l_table_name = 'HR_ORGANIZATION_INFORMATION' THEN
2470
2471 l_hr_organization_information := 'Yes';
2472
2473 END IF;
2474
2475 END IF;
2476
2477 FETCH get_gre_update_details INTO l_gre_name,l_gre_id,l_old_jurisdiction_code,l_new_jurisdiction_code,l_table_name;
2478
2479 IF (get_gre_update_details%NOTFOUND) THEN
2480
2481 /*No more records exist. Print the last GRE details*/
2482
2483 fnd_file.put_line(fnd_file.output,'<TR><TD>'||l_print_gre_name||'</TD><TD>'||l_print_gre_id||'</TD><TD>'||l_print_old_juri_code||'</TD><TD>'||l_print_new_juri_code||'</TD>');
2484 fnd_file.put_line(fnd_file.output,'<TD>'||l_hr_organization_information||'</TD></TR>');
2485 END IF;
2486
2487 END LOOP;
2488
2489 CLOSE get_gre_update_details;
2490
2491 fnd_file.put_line(fnd_file.output,'</TABLE>');
2492
2493 /* End of GRE Information Update related details printing*/
2494
2495 IF p_mode = 'UPGRADE' AND l_upgrade_needed = 'Y' THEN
2496
2497 /* Start of Errored Employees printing section */
2498
2499 OPEN get_emp_in_error(l_id,p_mode,l_date);
2500 FETCH get_emp_in_error INTO l_full_name,l_emp_assignment_id;
2501
2502 fnd_file.put_line(fnd_file.output,'<HTML><H3>Employees for which processing ended in error </H3>');
2503
2504 IF get_emp_in_error%FOUND THEN
2505
2506 fnd_file.put_line(fnd_file.output,'<TABLE cellpadding="2" cellspacing="1" border="2" summary="Employees in error">');
2507 fnd_file.put_line(fnd_file.output,'<TR><TH colspan=2>Employee Details</TH>');
2508 fnd_file.put_line(fnd_file.output,'<TR><TH>Full Name</TH><TH>Assignment ID</TH></TR>');
2509
2510 WHILE (get_emp_in_error%FOUND)
2511 LOOP
2512
2513 fnd_file.put_line(fnd_file.output,'<TR><TD>'||l_full_name||'</TD><TD>'||l_emp_assignment_id||'</TD></TR>');
2514 FETCH get_emp_in_error INTO l_full_name,l_emp_assignment_id;
2515
2516 END LOOP;
2517
2518 fnd_file.put_line(fnd_file.output,'</TABLE>');
2519
2520 ELSE
2521
2522 fnd_file.put_line(fnd_file.output,'<P>No Assignments in Error</P>');
2523
2524 END IF;
2525
2526 CLOSE get_emp_in_error;
2527
2528 /* End of Errored Employees printing section */
2529
2530 -- Added for bug 14213838.
2531 /* Start of Errored GREs printing section */
2532
2533 OPEN get_gre_in_error(l_id,p_mode,l_date);
2534 FETCH get_gre_in_error INTO l_gre_name,l_gre_id;
2535
2536 fnd_file.put_line(fnd_file.output,'<HTML><H3>Government Reporting Entities for which processing ended in error </H3>');
2537
2538 IF get_gre_in_error%FOUND THEN
2539
2540 fnd_file.put_line(fnd_file.output,'<TABLE cellpadding="2" cellspacing="1" border="2" summary="GREs in error">');
2541 fnd_file.put_line(fnd_file.output,'<TR><TH colspan=2>GRE Details</TH>');
2542 fnd_file.put_line(fnd_file.output,'<TR><TH>GRE Name</TH><TH>GRE ID</TH></TR>');
2543
2544 WHILE (get_gre_in_error%FOUND)
2545 LOOP
2546
2547 fnd_file.put_line(fnd_file.output,'<TR><TD>'||l_gre_name||'</TD><TD>'||l_gre_id||'</TD></TR>');
2548 FETCH get_gre_in_error INTO l_gre_name,l_gre_id;
2549
2550 END LOOP;
2551
2552 fnd_file.put_line(fnd_file.output,'</TABLE>');
2553
2554 ELSE
2555
2556 fnd_file.put_line(fnd_file.output,'<P>No GREs in Error</P>');
2557
2558 END IF;
2559
2560 CLOSE get_gre_in_error;
2561
2562 /* End of Errored GREs printing section */
2563
2564 END IF;
2565
2566 /*Start printing Summary Section*/
2567
2568 fnd_file.put_line(fnd_file.output,'<H3>Summary </H3>');
2569
2570 /* Printing Assignments Summary */
2571
2572 OPEN get_emp_counts(l_id,p_mode,l_date,'ALL');
2573 FETCH get_emp_counts INTO l_total_assignments;
2574 CLOSE get_emp_counts;
2575
2576 fnd_file.put_line(fnd_file.output,'<P>Number of Assignments identified for Upgrade : '||l_total_assignments||'</P>');
2577
2578 IF p_mode = 'UPGRADE' THEN
2579
2580 /* Print the Number of Employees processed*/
2581
2582 OPEN get_emp_counts(l_id,p_mode,l_date,'C');
2583 FETCH get_emp_counts INTO l_success_assignments;
2584 CLOSE get_emp_counts;
2585
2586 OPEN get_emp_counts(l_id,p_mode,l_date,'E');
2587 FETCH get_emp_counts INTO l_error_assignments;
2588 CLOSE get_emp_counts;
2589
2590 l_not_process_assignments := l_total_assignments - l_success_assignments - l_error_assignments;
2591
2592 fnd_file.put_line(fnd_file.output,'<P>Number of Assignments Upgraded successfully : '||l_success_assignments||'</P>');
2593 fnd_file.put_line(fnd_file.output,'<P>Number of Assignments ended in Error : '||l_error_assignments||'</P>');
2594 fnd_file.put_line(fnd_file.output,'<P>Number of Assignments yet to be processed : '||l_not_process_assignments||'</P>');
2595
2596 /* End of Printing the Number of Employees processed*/
2597
2598 END IF;
2599
2600 -- Added for bug 14213838.
2601 /* Printing GREs Summary */
2602
2603 OPEN get_gre_counts(l_id,p_mode,l_date,'ALL');
2604 FETCH get_gre_counts INTO l_total_gre_changes;
2605 CLOSE get_gre_counts;
2606
2607 fnd_file.put_line(fnd_file.output,'<P>Number of GREs identified for Upgrade : '||l_total_gre_changes||'</P>');
2608
2609 IF p_mode = 'UPGRADE' THEN
2610
2611 /* Print the Number of GREs processed*/
2612
2613 OPEN get_gre_counts(l_id,p_mode,l_date,'C');
2614 FETCH get_gre_counts INTO l_success_gre_changes;
2615 CLOSE get_gre_counts;
2616
2617 OPEN get_gre_counts(l_id,p_mode,l_date,'E');
2618 FETCH get_gre_counts INTO l_error_gre_changes;
2619 CLOSE get_gre_counts;
2620
2621 l_not_process_gre_changes := l_total_gre_changes - l_success_gre_changes - l_error_gre_changes;
2622
2623 fnd_file.put_line(fnd_file.output,'<P>Number of GREs Upgraded successfully : '||l_success_gre_changes||'</P>');
2624 fnd_file.put_line(fnd_file.output,'<P>Number of GREs ended in Error : '||l_error_gre_changes||'</P>');
2625 fnd_file.put_line(fnd_file.output,'<P>Number of GREs yet to be processed : '||l_not_process_gre_changes||'</P>');
2626
2627 /* End of Printing the Number of GREs processed*/
2628 END IF;
2629
2630 OPEN get_ff_archive_counts(l_id,p_mode,l_date,'ALL');
2631 FETCH get_ff_archive_counts INTO l_total_ff_arch_changes;
2632 CLOSE get_ff_archive_counts;
2633
2634 OPEN get_ff_archive_counts(l_id,p_mode,l_date,'C');
2635 FETCH get_ff_archive_counts INTO l_success_ff_arch_changes;
2636 CLOSE get_ff_archive_counts;
2637
2638 OPEN get_ff_archive_counts(l_id,p_mode,l_date,'E');
2639 FETCH get_ff_archive_counts INTO l_error_ff_arch_changes;
2640 CLOSE get_ff_archive_counts;
2641
2642 IF p_mode = 'UPGRADE' THEN
2643 IF (l_total_assignments = l_success_assignments
2644 and l_total_gre_changes = l_success_gre_changes
2645 and l_total_ff_arch_changes = l_success_ff_arch_changes) THEN
2646
2647 IF(l_total_ff_arch_changes<>0) THEN
2648 fnd_file.put_line(fnd_file.output,'<P>Employer PSD Codes related data in FF_ARCHIVE_ITEMS table has been successfully updated</P>');
2649 END IF;
2650
2651 fnd_file.put_line(fnd_file.output,'<P>Upgrade Process completed successfully</P>');
2652
2653 ELSE
2654 IF(l_error_ff_arch_changes<>0) THEN
2655 fnd_file.put_line(fnd_file.output,'<P>Employer PSD Codes related data in FF_ARCHIVE_ITEMS table not updated successfully</P>');
2656 END IF;
2657
2658 fnd_file.put_line(fnd_file.output,'<P>Upgrade Process incomplete.Verify assignments/GREs in error and rerun the Upgrade process</P>');
2659
2660 END IF;
2661
2662 ELSE
2663
2664 fnd_file.put_line(fnd_file.output,'<P>Generate Process completed successfully</P>');
2665
2666 END IF;
2667
2668 /* End of Summary section */
2669
2670 ELSE
2671
2672 fnd_file.put_line(fnd_file.output,'<P>No PSDCODE Changes found that need upgrade</P>');
2673 fnd_file.put_line(fnd_file.output,'<P>No Processing required</P>');
2674
2675 END IF; /* l_upgrade_needed IF*/
2676
2677 hr_utility.trace('Leaving pay_us_psd_upgrade_pkg.generate_output');
2678
2679 END generate_output;
2680
2681 END pay_us_psd_upgrade_pkg;