[Home] [Help]
46: --
47: end get_parameter;
48:
49: function get_city(p_person_id number,
50: p_location_id number,
51: p_state_code varchar2,
52: p_county_code varchar2,
53: p_city_code varchar2,
54: p_city_name varchar2,
94: and pus.state_code = p_state_code
95: and puc.county_code = p_county_code
96: union
97: select hl.town_or_city
98: from hr_locations_all hl,
99: pay_us_states pus,
100: pay_us_counties puc
101: where pus.state_abbrev = hl.region_2
102: and pus.state_code = puc.state_code
100: pay_us_counties puc
101: where pus.state_abbrev = hl.region_2
102: and pus.state_code = puc.state_code
103: and nvl(p_county_name,puc.county_name) = hl.region_1
104: and hl.location_id = p_location_id
105: and pus.state_code = p_state_code
106: and puc.county_code = p_county_code
107: union
108: select loc_information18
105: and pus.state_code = p_state_code
106: and puc.county_code = p_county_code
107: union
108: select loc_information18
109: from hr_locations_all hl,
110: pay_us_states pus,
111: pay_us_counties puc
112: where pus.state_abbrev = hl.loc_information17
113: and pus.state_code = puc.state_code
113: and pus.state_code = puc.state_code
114: and nvl(p_county_name,puc.county_name) = hl.loc_information19
115: and pus.state_code = p_state_code
116: and puc.county_code = p_county_code
117: and hl.location_id = p_location_id;
118:
119: cursor c_get_city_names(p_emp_city_name pay_us_city_names.city_name%TYPE) is
120: select pucn.city_name
121: from pay_us_city_names pucn
292: AND pmod.process_type in ('UP','US','PU','D','SU')
293: AND pmod.patch_name = l_patch_name
294: AND ectr.assignment_id = paf.assignment_id
295: AND paf.person_id between stperson and endperson
296: AND get_city(paf.person_id, paf.location_id, ectr.state_code,
297: ectr.county_code,ectr.city_code,pmod.city_name,l_patch_name,pmod.process_type) = pmod.city_name
298: AND NOT EXISTS (select 'Y' from PAY_US_GEO_UPDATE pugu
299: where pugu.assignment_id = ectr.assignment_id
300: and pugu.new_juri_code = pmod.state_code||'-'||pmod.county_code||'-'||pmod.new_city_code
363:
364: -- hr_utility.trace_on('','TCL');
365:
366: hr_utility.trace('entering action_creation');
367: hr_utility.set_location('geocode_action_creation',1);
368:
369: open c_parameters(pactid);
370:
371: fetch c_parameters into leg_param,
403: STATUS,
404: DESCRIPTION,
405: UPDATE_DATE,
406: LEGISLATION_CODE,
407: APPLICATION_RELEASE,
408: PREREQ_PATCH_NAME)
409: values
410: (PAY_PATCH_STATUS_S.nextval,
411: '1111111',
431:
432: hr_utility.trace('value of l_geo_phase id is '|| to_char(l_geo_phase_id ));
433:
434:
435: hr_utility.set_location('geocode_action_creation',2);
436: open c_actions_assignment(pactid,stperson,endperson);
437:
438: loop
439: hr_utility.set_location('geocode_action_creation',3);
435: hr_utility.set_location('geocode_action_creation',2);
436: open c_actions_assignment(pactid,stperson,endperson);
437:
438: loop
439: hr_utility.set_location('geocode_action_creation',3);
440: fetch c_actions_assignment into l_assignment_id;
441:
442: exit when c_actions_assignment%notfound;
443:
440: fetch c_actions_assignment into l_assignment_id;
441:
442: exit when c_actions_assignment%notfound;
443:
444: hr_utility.set_location('geocode_action_creation',4);
445: select pay_assignment_actions_s.nextval
446: into lockingactid
447: from dual;
448:
459:
460:
461: -- Create actions for GRE level Run balances
462:
463: hr_utility.set_location('geocode_action_creation',5);
464:
465:
466: hr_utility.trace('before update_taxability_rules value of l_geo_phase_Id is '|| to_char(l_geo_phase_Id));
467:
484: lv_no_of_chunks := NVL(lv_no_of_chunks,0);
485: lv_count := 1 ;
486: /*Bug#7240914: Changes end here*/
487: loop
488: hr_utility.set_location('gocode_action_creation',6);
489: fetch c_actions_run_bal into l_payact_id;
490:
491: exit when c_actions_run_bal%notfound;
492: --
490:
491: exit when c_actions_run_bal%notfound;
492: --
493:
494: hr_utility.set_location('gocode_action_creation',7);
495: select pay_assignment_actions_s.nextval
496: into lockingactid
497: from dual;
498: --
608: l_patch_name pay_patch_status.patch_name%type;
609:
610: BEGIN
611:
612: hr_utility.set_location ('pay_us_geo_update.action_code', 1);
613:
614: open c_xfr_info (p_xfr_action_id);
615:
616: fetch c_xfr_info into l_payroll_action_id,
817: p_person_id IN NUMBER,
818: p_assign_id IN NUMBER,
819: p_old_juri_code IN VARCHAR2,
820: p_new_juri_code IN VARCHAR2,
821: p_location IN VARCHAR2,
822: p_id IN NUMBER,
823: p_status IN VARCHAR2 DEFAULT NULL,
824: p_description IN VARCHAR2 DEFAULT NULL)
825:
844: DESCRIPTION)
845: VALUES(g_geo_phase_id,
846: p_assign_id,
847: p_person_id,
848: p_location,
849: p_id,
850: p_old_juri_code,
851: p_new_juri_code,
852: p_proc_type,
871: DESCRIPTION)
872: VALUES(g_geo_phase_id,
873: p_assign_id,
874: p_person_id,
875: p_location,
876: p_id,
877: p_old_juri_code,
878: p_new_juri_code,
879: p_proc_type,
1127: pay_assignment_actions paa,
1128: pay_payroll_actions ppa,
1129: ff_contexts ffc
1130: WHERE ppa.report_type = 'YREND'
1131: AND ppa.report_category = 'RT'
1132: AND ppa.report_qualifier = 'FED'
1133: AND ppa.payroll_action_id = paa.payroll_action_id
1134: AND paa.assignment_id = assign_id
1135: AND fai.context1 = paa.assignment_action_id
1158: pay_assignment_actions paa,
1159: pay_payroll_actions ppa,
1160: ff_contexts ffc
1161: WHERE ppa.report_type in ('T4', 'T4A', 'RL1', 'RL2', 'YREND')
1162: and ppa.report_category in ('RT', 'CAEOYRL1', 'CAEOYRL2', 'CAEOY', 'CAEOY')
1163: and report_qualifier in ('FED','CAEOYRL1', 'CAEOYRL2', 'CAEOY', 'CAEOY')
1164: and ppa.payroll_action_id = paa.payroll_action_id
1165: and paa.assignment_id = assign_id
1166: and fai.context1 = paa.assignment_action_id
1411: p_person_id => p_person_id,
1412: p_assign_id => p_assign_id,
1413: p_old_juri_code => p_old_juri_code,
1414: p_new_juri_code => p_new_juri_code,
1415: p_location => 'PAY_BALANCE_BATCH_LINES',
1416: p_id => p_assign_id);
1417:
1418: END IF;
1419: CLOSE bal_batch_cur;
1433: p_person_id => p_person_id,
1434: p_assign_id => p_assign_id,
1435: p_old_juri_code => p_old_juri_code,
1436: p_new_juri_code => p_new_juri_code,
1437: p_location => 'PAY_BALANCE_BATCH_LINES',
1438: p_id => p_assign_id);
1439:
1440:
1441: END IF;
1499: p_person_id => p_person_id,
1500: p_assign_id => p_assign_id,
1501: p_old_juri_code => p_old_juri_code,
1502: p_new_juri_code => p_new_juri_code,
1503: p_location => 'PAY_RUN_BALANCES',
1504: p_id => p_assign_id);
1505:
1506: END IF;
1507:
1530: p_person_id => p_person_id,
1531: p_assign_id => p_assign_id,
1532: p_old_juri_code => p_old_juri_code,
1533: p_new_juri_code => p_new_juri_code,
1534: p_location => 'PAY_RUN_BALANCES',
1535: p_id => p_assign_id);
1536:
1537:
1538: END IF;
1552: p_person_id => p_person_id,
1553: p_assign_id => p_assign_id,
1554: p_old_juri_code => p_old_juri_code,
1555: p_new_juri_code => p_new_juri_code,
1556: p_location => 'PAY_RUN_BALANCES',
1557: p_id => p_assign_id);
1558:
1559:
1560: END IF;
1617: p_person_id => p_person_id,
1618: p_assign_id => p_assign_id,
1619: p_old_juri_code => p_old_juri_code,
1620: p_new_juri_code => p_new_juri_code,
1621: p_location => 'PAY_US_EMP_CITY_TAX_RULES_F',
1622: p_id => p_city_tax_record_id);
1623:
1624: /*END IF;*/
1625: ELSE
1632: p_person_id => p_person_id,
1633: p_assign_id => p_assign_id,
1634: p_old_juri_code => p_old_juri_code,
1635: p_new_juri_code => p_new_juri_code,
1636: p_location => 'PAY_US_EMP_CITY_TAX_RULES_F',
1637: p_id => p_city_tax_record_id);
1638:
1639: END IF;
1640:
1818: p_person_id => p_person_id,
1819: p_assign_id => p_assign_id,
1820: p_old_juri_code => p_old_juri_code,
1821: p_new_juri_code => p_new_juri_code,
1822: p_location => 'PAY_RUN_RESULT_VALUES',
1823: p_id => p_run_result_id);
1824:
1825: CLOSE ele_run_result_val;
1826:
1844: p_person_id => p_person_id,
1845: p_assign_id => p_assign_id,
1846: p_old_juri_code => p_old_juri_code,
1847: p_new_juri_code => p_new_juri_code,
1848: p_location => 'PAY_RUN_RESULT_VALUES',
1849: p_id => p_run_result_id);
1850:
1851: CLOSE ele_run_result_val;
1852:
1869: p_person_id => p_person_id,
1870: p_assign_id => p_assign_id,
1871: p_old_juri_code => p_old_juri_code,
1872: p_new_juri_code => p_new_juri_code,
1873: p_location => 'PAY_RUN_RESULT_VALUES',
1874: p_id => p_run_result_id);
1875:
1876: END IF;
1877:
1906: p_person_id => p_person_id,
1907: p_assign_id => p_assign_id,
1908: p_old_juri_code => p_old_juri_code,
1909: p_new_juri_code => p_new_juri_code,
1910: p_location => 'PAY_RUN_RESULTS',
1911: p_id => p_run_result_id);
1912:
1913: CLOSE ele_run_results;
1914:
1932: p_person_id => p_person_id,
1933: p_assign_id => p_assign_id,
1934: p_old_juri_code => p_old_juri_code,
1935: p_new_juri_code => p_new_juri_code,
1936: p_location => 'PAY_RUN_RESULTS',
1937: p_id => p_run_result_id);
1938:
1939: CLOSE ele_run_results;
1940:
1956: p_person_id => p_person_id,
1957: p_assign_id => p_assign_id,
1958: p_old_juri_code => p_old_juri_code,
1959: p_new_juri_code => p_new_juri_code,
1960: p_location => 'PAY_RUN_RESULTS',
1961: p_id => p_run_result_id);
1962:
1963: END IF;
1964:
2029: p_person_id => p_person_id,
2030: p_assign_id => p_assign_id,
2031: p_old_juri_code => p_old_juri_code,
2032: p_new_juri_code => p_new_juri_code,
2033: p_location => 'PAY_ACTION_CONTEXTS',
2034: p_id => p_assign_id);
2035:
2036: END IF;
2037: CLOSE pac_inside_cur;
2049: p_person_id => p_person_id,
2050: p_assign_id => p_assign_id,
2051: p_old_juri_code => p_old_juri_code,
2052: p_new_juri_code => p_new_juri_code,
2053: p_location => 'PAY_ACTION_CONTEXTS',
2054: p_id => p_assign_id);
2055:
2056: END IF;
2057: CLOSE pac_inside_cur;
2106: p_person_id => p_person_id,
2107: p_assign_id => p_assign_id,
2108: p_old_juri_code => p_old_juri_code,
2109: p_new_juri_code => p_new_juri_code,
2110: p_location => 'FF_ARCHIVE_ITEM_CONTEXTS',
2111: p_id => p_archive_item_id);
2112:
2113:
2114: ELSE
2120: p_person_id => p_person_id,
2121: p_assign_id => p_assign_id,
2122: p_old_juri_code => p_old_juri_code,
2123: p_new_juri_code => p_new_juri_code,
2124: p_location => 'FF_ARCHIVE_ITEM_CONTEXTS',
2125: p_id => p_archive_item_id);
2126:
2127: END IF;
2128:
2172: p_person_id => p_person_id,
2173: p_assign_id => p_assign_id,
2174: p_old_juri_code => p_old_juri_code,
2175: p_new_juri_code => p_new_juri_code,
2176: p_location => 'PAY_ELEMENT_ENTRY_VALUES_F',
2177: p_id => p_ele_ent_id);
2178:
2179: ELSE
2180:
2185: p_person_id => p_person_id,
2186: p_assign_id => p_assign_id,
2187: p_old_juri_code => p_old_juri_code,
2188: p_new_juri_code => p_new_juri_code,
2189: p_location => 'PAY_ELEMENT_ENTRY_VALUES_F',
2190: p_id => p_ele_ent_id);
2191:
2192: END IF;
2193:
2236: p_person_id => p_person_id,
2237: p_assign_id => p_assign_id,
2238: p_old_juri_code => p_old_juri_code,
2239: p_new_juri_code => p_new_juri_code,
2240: p_location => 'PAY_BALANCE_CONTEXT_VALUES',
2241: p_id => p_lat_bal_id);
2242:
2243: ELSE
2244:
2249: p_person_id => p_person_id,
2250: p_assign_id => p_assign_id,
2251: p_old_juri_code => p_old_juri_code,
2252: p_new_juri_code => p_new_juri_code,
2253: p_location => 'PAY_BALANCE_CONTEXT_VALUES',
2254: p_id => p_lat_bal_id);
2255:
2256: END IF;
2257:
2262:
2263: END balance_contexts;
2264:
2265:
2266: -- This procedure will take out duplicate VERTEX element entries and add the percentages
2267: -- of the previously duplicated element entries togethor
2268: -- This used to be script pydeldup.sql earlier
2269:
2270: PROCEDURE duplicate_vertex_ee(p_assignment_id IN NUMBER)
2263: END balance_contexts;
2264:
2265:
2266: -- This procedure will take out duplicate VERTEX element entries and add the percentages
2267: -- of the previously duplicated element entries togethor
2268: -- This used to be script pydeldup.sql earlier
2269:
2270: PROCEDURE duplicate_vertex_ee(p_assignment_id IN NUMBER)
2271:
2266: -- This procedure will take out duplicate VERTEX element entries and add the percentages
2267: -- of the previously duplicated element entries togethor
2268: -- This used to be script pydeldup.sql earlier
2269:
2270: PROCEDURE duplicate_vertex_ee(p_assignment_id IN NUMBER)
2271:
2272: IS
2273:
2274: -- This cursor will get us the element entries of the assignments processed
2319: l_effective_end_date pay_element_entry_values_f.effective_end_date%TYPE;
2320:
2321: BEGIN
2322:
2323: hr_utility.trace('Entering pay_us_geo_upd_pkg.duplicate_vertex_ee');
2324:
2325: l_prev_screen := null;
2326: l_prev_eleid := null;
2327:
2326: l_prev_eleid := null;
2327:
2328: for j in csr_get_dup(p_assignment_id) loop
2329:
2330: hr_utility.set_location('pay_us_geo_upd_pkg.duplicate_vertex_ee', 1);
2331:
2332: if j.sev = l_prev_screen and j.eei <> l_prev_eleid then
2333: hr_utility.trace('Element Entry Id : '|| to_char(j.eei)
2334: ||' is a duplicate of : ' || to_char(l_prev_eleid)
2330: hr_utility.set_location('pay_us_geo_upd_pkg.duplicate_vertex_ee', 1);
2331:
2332: if j.sev = l_prev_screen and j.eei <> l_prev_eleid then
2333: hr_utility.trace('Element Entry Id : '|| to_char(j.eei)
2334: ||' is a duplicate of : ' || to_char(l_prev_eleid)
2335: ||' for assignment_id : ' || to_char(p_assignment_id));
2336:
2337: hr_utility.set_location('pay_us_geo_upd_pkg.duplicate_vertex_ee', 2);
2338:
2333: hr_utility.trace('Element Entry Id : '|| to_char(j.eei)
2334: ||' is a duplicate of : ' || to_char(l_prev_eleid)
2335: ||' for assignment_id : ' || to_char(p_assignment_id));
2336:
2337: hr_utility.set_location('pay_us_geo_upd_pkg.duplicate_vertex_ee', 2);
2338:
2339: /* get the percentages for the record to be deleted */
2340: open csr_get_percentage(j.eei);
2341: loop
2339: /* get the percentages for the record to be deleted */
2340: open csr_get_percentage(j.eei);
2341: loop
2342:
2343: hr_utility.set_location('pay_us_geo_upd_pkg.duplicate_vertex_ee', 3);
2344:
2345: /* Get the %age for each datetracked record */
2346:
2347: fetch csr_get_percentage into l_percent,
2348: l_effective_start_date,
2349: l_effective_end_date;
2350: exit when csr_get_percentage%NOTFOUND;
2351:
2352: hr_utility.set_location('pay_us_geo_upd_pkg.duplicate_vertex_ee', 4);
2353:
2354: /* Add the %age of the current element entry to the earlier
2355: entry */
2356:
2383: and pev.effective_start_date=l_effective_start_date
2384: and pev.effective_end_date=l_effective_end_date;
2385:
2386: END IF;
2387: hr_utility.set_location('pay_us_geo_upd_pkg.duplicate_vertex_ee', 5);
2388:
2389: end loop;
2390: close csr_get_percentage;
2391:
2388:
2389: end loop;
2390: close csr_get_percentage;
2391:
2392: hr_utility.set_location('pay_us_geo_upd_pkg.duplicate_vertex_ee', 6);
2393:
2394: /* Now delete the current entry */
2395:
2396: delete pay_element_entries_f
2396: delete pay_element_entries_f
2397: where element_entry_id = j.eei
2398: and assignment_id = p_assignment_id;
2399:
2400: hr_utility.set_location('pay_us_geo_upd_pkg.duplicate_vertex_ee', 7);
2401:
2402: else
2403:
2404: hr_utility.set_location('pay_us_geo_upd_pkg.duplicate_vertex_ee', 8);
2400: hr_utility.set_location('pay_us_geo_upd_pkg.duplicate_vertex_ee', 7);
2401:
2402: else
2403:
2404: hr_utility.set_location('pay_us_geo_upd_pkg.duplicate_vertex_ee', 8);
2405:
2406: l_prev_screen := j.sev;
2407: l_prev_eleid := j.eei;
2408:
2405:
2406: l_prev_screen := j.sev;
2407: l_prev_eleid := j.eei;
2408:
2409: hr_utility.set_location('pay_us_geo_upd_pkg.duplicate_vertex_ee', 9);
2410:
2411: end if;
2412:
2413: hr_utility.set_location('pay_us_geo_upd_pkg.duplicate_vertex_ee', 10);
2409: hr_utility.set_location('pay_us_geo_upd_pkg.duplicate_vertex_ee', 9);
2410:
2411: end if;
2412:
2413: hr_utility.set_location('pay_us_geo_upd_pkg.duplicate_vertex_ee', 10);
2414:
2415: end loop;
2416:
2417: hr_utility.set_location('pay_us_geo_upd_pkg.duplicate_vertex_ee', 11);
2413: hr_utility.set_location('pay_us_geo_upd_pkg.duplicate_vertex_ee', 10);
2414:
2415: end loop;
2416:
2417: hr_utility.set_location('pay_us_geo_upd_pkg.duplicate_vertex_ee', 11);
2418:
2419: hr_utility.trace('Exiting pay_us_geo_upd_pkg.duplicate_vertex_ee');
2420:
2421: end duplicate_vertex_ee;
2415: end loop;
2416:
2417: hr_utility.set_location('pay_us_geo_upd_pkg.duplicate_vertex_ee', 11);
2418:
2419: hr_utility.trace('Exiting pay_us_geo_upd_pkg.duplicate_vertex_ee');
2420:
2421: end duplicate_vertex_ee;
2422:
2423:
2417: hr_utility.set_location('pay_us_geo_upd_pkg.duplicate_vertex_ee', 11);
2418:
2419: hr_utility.trace('Exiting pay_us_geo_upd_pkg.duplicate_vertex_ee');
2420:
2421: end duplicate_vertex_ee;
2422:
2423:
2424: -- This procedure will create element entries for assignments that have geocodes
2425: -- which have split from the upgrade.
2490: ln_county_code := substr(p_new_juri_code,4,3);
2491: ln_city_code := substr(p_new_juri_code,8);
2492: ln_old_city_code := substr(p_old_juri_code,8);
2493:
2494: hr_utility.set_location('pay_us_geo_upd_pkg.insert_ele_entries',1);
2495:
2496: open c_county_rec(p_assign_id,
2497: ln_state_code,
2498: ln_county_code);
2496: open c_county_rec(p_assign_id,
2497: ln_state_code,
2498: ln_county_code);
2499:
2500: hr_utility.set_location('pay_us_geo_upd_pkg.insert_ele_entries',2);
2501:
2502: fetch c_county_rec into lc_exists;
2503: if c_county_rec%notfound then
2504:
2501:
2502: fetch c_county_rec into lc_exists;
2503: if c_county_rec%notfound then
2504:
2505: hr_utility.set_location('pay_us_geo_upd_pkg.insert_ele_entries',3);
2506:
2507: -- Call write message to store information that their is no county record for this assignment
2508: write_message(
2509: p_proc_type => 'MISSING_COUNTY_RECORDS',
2510: p_person_id => p_person_id,
2511: p_assign_id => p_assign_id,
2512: p_old_juri_code => ln_state_code||'-'||ln_county_code,
2513: p_new_juri_code => p_new_juri_code,
2514: p_location => 'PAY_US_EMP_COUNTY_TAX_RULES_F',
2515: p_id => null);
2516:
2517: hr_utility.set_location('pay_us_geo_upd_pkg.insert_ele_entries',4);
2518:
2513: p_new_juri_code => p_new_juri_code,
2514: p_location => 'PAY_US_EMP_COUNTY_TAX_RULES_F',
2515: p_id => null);
2516:
2517: hr_utility.set_location('pay_us_geo_upd_pkg.insert_ele_entries',4);
2518:
2519:
2520: ELSE
2521:
2518:
2519:
2520: ELSE
2521:
2522: hr_utility.set_location('pay_us_geo_upd_pkg.insert_ele_entries',5);
2523:
2524: open c_tax_rec(p_assign_id,
2525: ln_state_code,
2526: ln_county_code,
2527: ln_old_city_code);
2528:
2529: fetch c_tax_rec into ln_check;
2530:
2531: hr_utility.set_location('pay_us_geo_upd_pkg.insert_ele_entries',6);
2532:
2533: if c_tax_rec%found then -- changed notfound to found
2534: close c_tax_rec;
2535: open c_tax_rec(p_assign_id,
2541: lc_insert_rec := 'Y';
2542: end if;
2543: end if;
2544:
2545: hr_utility.set_location('pay_us_geo_upd_pkg.insert_ele_entries',7);
2546:
2547: close c_tax_rec;
2548:
2549: if lc_insert_rec = 'Y' then
2547: close c_tax_rec;
2548:
2549: if lc_insert_rec = 'Y' then
2550:
2551: hr_utility.set_location('pay_us_geo_upd_pkg.insert_ele_entries',8);
2552:
2553: open c_elig_date(p_assign_id);
2554:
2555: fetch c_elig_date into ld_eff_start_date, ld_eff_end_date, ln_business_group_id;
2554:
2555: fetch c_elig_date into ld_eff_start_date, ld_eff_end_date, ln_business_group_id;
2556:
2557: if c_elig_date%notfound then
2558: hr_utility.set_location('pay_us_geo_upd_pkg.insert_ele_entries',9);
2559: --Exiting if there are no city Tax Records.
2560: end if;
2561:
2562: hr_utility.set_location('pay_us_geo_upd_pkg.insert_ele_entries',10);
2558: hr_utility.set_location('pay_us_geo_upd_pkg.insert_ele_entries',9);
2559: --Exiting if there are no city Tax Records.
2560: end if;
2561:
2562: hr_utility.set_location('pay_us_geo_upd_pkg.insert_ele_entries',10);
2563: close c_elig_date;
2564:
2565: hr_utility.set_location('pay_us_geo_upd_pkg.insert_ele_entries',11);
2566:
2561:
2562: hr_utility.set_location('pay_us_geo_upd_pkg.insert_ele_entries',10);
2563: close c_elig_date;
2564:
2565: hr_utility.set_location('pay_us_geo_upd_pkg.insert_ele_entries',11);
2566:
2567: hr_utility.trace('The business group id is: '||to_char(ln_business_group_id));
2568:
2569: IF G_MODE = 'UPGRADE' THEN
2590: fnd_profile.put('HR_CROSS_BUSINESS_GROUP','N');
2591: hr_utility.trace('modifed the profile to'||to_char(fnd_profile.value('HR_CROSS_BUSINESS_GROUP')));
2592: end if;
2593:
2594: hr_utility.set_location('pay_us_geo_upd_pkg.insert_ele_entries',12);
2595:
2596: -- Write to the table with the new city information
2597:
2598: write_message(
2600: p_person_id => p_person_id,
2601: p_assign_id => p_assign_id,
2602: p_old_juri_code => null,
2603: p_new_juri_code => p_new_juri_code,
2604: p_location => 'PAY_US_EMP_CITY_TAX_RULES_F',
2605: p_id => ln_emp_city_tax_rule_id);
2606:
2607: else /* Modified for bug 6864396*/
2608:
2611: p_person_id => p_person_id,
2612: p_assign_id => p_assign_id,
2613: p_old_juri_code => null,
2614: p_new_juri_code => p_new_juri_code,
2615: p_location => 'PAY_US_EMP_CITY_TAX_RULES_F',
2616: p_id => null);
2617:
2618: END IF;
2619:
2616: p_id => null);
2617:
2618: END IF;
2619:
2620: hr_utility.set_location('pay_us_geo_upd_pkg.insert_ele_entries',12);
2621:
2622: /*
2623: pay_us_emp_dt_tax_rules.maintain_element_entry
2624: (p_assignment_id => p_assign_id,
2637: p_person_id => p_person_id,
2638: p_assign_id => p_assign_id,
2639: p_old_juri_code => null,
2640: p_new_juri_code => p_new_juri_code,
2641: p_location => 'PAY_ELEMENT_ENTRIES_F',
2642: p_id => null);
2643:
2644: hr_utility.set_location('pay_us_geo_upd_pkg.insert_ele_entries',13);
2645:
2640: p_new_juri_code => p_new_juri_code,
2641: p_location => 'PAY_ELEMENT_ENTRIES_F',
2642: p_id => null);
2643:
2644: hr_utility.set_location('pay_us_geo_upd_pkg.insert_ele_entries',13);
2645:
2646: /* END IF; County 6864396*/
2647: END IF;
2648:
2647: END IF;
2648:
2649: end if;
2650:
2651: hr_utility.set_location('pay_us_geo_upd_pkg.insert_ele_entries',14);
2652:
2653: ELSE -- p_proc_type != 'SU' and p_proc_type != 'US'
2654:
2655: write_message(
2657: p_person_id => p_person_id,
2658: p_assign_id => p_assign_id,
2659: p_old_juri_code => null,
2660: p_new_juri_code => p_new_juri_code,
2661: p_location => 'PAY_ELEMENT_ENTRIES_F',
2662: p_id => null);
2663:
2664: END IF;
2665: END insert_ele_entries;
2744: hr_utility.trace('Entering pay_us_geo_upd_pkg.check_time');
2745:
2746: tot_percentage := 0;
2747:
2748: hr_utility.set_location('pay_us_geo_upd_pkg.check_time',1);
2749:
2750: -- Get each state for the assignment.
2751: FOR state_rec IN state_cur(p_assign_id) LOOP
2752:
2749:
2750: -- Get each state for the assignment.
2751: FOR state_rec IN state_cur(p_assign_id) LOOP
2752:
2753: hr_utility.set_location('pay_us_geo_upd_pkg.check_time',2);
2754:
2755: -- Get the percentage of time worked in that state.
2756:
2757: OPEN sum_cur(p_assign_id, state_rec.effective_start_date,
2756:
2757: OPEN sum_cur(p_assign_id, state_rec.effective_start_date,
2758: state_rec.effective_end_date, state_rec.state_code);
2759: FETCH sum_cur INTO percentage;
2760: hr_utility.set_location('pay_us_geo_upd_pkg.check_time',3);
2761:
2762: IF sum_cur%FOUND THEN
2763: hr_utility.set_location('pay_us_geo_upd_pkg.check_time',4);
2764:
2759: FETCH sum_cur INTO percentage;
2760: hr_utility.set_location('pay_us_geo_upd_pkg.check_time',3);
2761:
2762: IF sum_cur%FOUND THEN
2763: hr_utility.set_location('pay_us_geo_upd_pkg.check_time',4);
2764:
2765: tot_percentage := tot_percentage + percentage;
2766: hr_utility.set_location('pay_us_geo_upd_pkg.check_time',5);
2767:
2762: IF sum_cur%FOUND THEN
2763: hr_utility.set_location('pay_us_geo_upd_pkg.check_time',4);
2764:
2765: tot_percentage := tot_percentage + percentage;
2766: hr_utility.set_location('pay_us_geo_upd_pkg.check_time',5);
2767:
2768: END IF;
2769: hr_utility.set_location('pay_us_geo_upd_pkg.check_time',6);
2770:
2765: tot_percentage := tot_percentage + percentage;
2766: hr_utility.set_location('pay_us_geo_upd_pkg.check_time',5);
2767:
2768: END IF;
2769: hr_utility.set_location('pay_us_geo_upd_pkg.check_time',6);
2770:
2771: CLOSE sum_cur;
2772: hr_utility.set_location('pay_us_geo_upd_pkg.check_time',7);
2773:
2768: END IF;
2769: hr_utility.set_location('pay_us_geo_upd_pkg.check_time',6);
2770:
2771: CLOSE sum_cur;
2772: hr_utility.set_location('pay_us_geo_upd_pkg.check_time',7);
2773:
2774: END LOOP; -- state_cur
2775: hr_utility.set_location('pay_us_geo_upd_pkg.check_time',8);
2776:
2771: CLOSE sum_cur;
2772: hr_utility.set_location('pay_us_geo_upd_pkg.check_time',7);
2773:
2774: END LOOP; -- state_cur
2775: hr_utility.set_location('pay_us_geo_upd_pkg.check_time',8);
2776:
2777: IF (tot_percentage > 100) THEN
2778:
2779: hr_utility.set_location('pay_us_geo_upd_pkg.check_time',9);
2775: hr_utility.set_location('pay_us_geo_upd_pkg.check_time',8);
2776:
2777: IF (tot_percentage > 100) THEN
2778:
2779: hr_utility.set_location('pay_us_geo_upd_pkg.check_time',9);
2780:
2781: SELECT ppf.person_id
2782: INTO l_person_id
2783: FROM per_all_people_f ppf,
2794: p_person_id => l_person_id,
2795: p_assign_id => p_assign_id,
2796: p_old_juri_code => null,
2797: p_new_juri_code => null,
2798: p_location => 'PAY_ELEMENT_ENTRY_VALUES_F',
2799: p_id => null);
2800:
2801:
2802: hr_utility.set_location('pay_us_geo_upd_pkg.check_time',10);
2798: p_location => 'PAY_ELEMENT_ENTRY_VALUES_F',
2799: p_id => null);
2800:
2801:
2802: hr_utility.set_location('pay_us_geo_upd_pkg.check_time',10);
2803:
2804: END IF;
2805: hr_utility.set_location('pay_us_geo_upd_pkg.check_time',11);
2806:
2801:
2802: hr_utility.set_location('pay_us_geo_upd_pkg.check_time',10);
2803:
2804: END IF;
2805: hr_utility.set_location('pay_us_geo_upd_pkg.check_time',11);
2806:
2807: -- Taking out the exception here because if the procedure errors let it go to the calling block and
2808: -- use that exception handler as that errors to the savepoint and continues with the assignment.
2809:
2849: --Check if pay_us_asg_reporting table exists as some clients may
2850: --not have this table on their database.
2851: SELECT count(*)
2852: INTO table_exist
2853: FROM cat
2854: WHERE table_name = tab_name;
2855:
2856: hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geocodes',1);
2857:
2852: INTO table_exist
2853: FROM cat
2854: WHERE table_name = tab_name;
2855:
2856: hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geocodes',1);
2857:
2858: --Bug 2996546 call procedure load_input_values
2859: load_input_values;
2860: --hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geocodes',230);
2856: hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geocodes',1);
2857:
2858: --Bug 2996546 call procedure load_input_values
2859: load_input_values;
2860: --hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geocodes',230);
2861:
2862:
2863: OPEN main_driving_cur(P_ASSIGN_START, P_ASSIGN_END, P_CITY_NAME, P_API_MODE);
2864:
2861:
2862:
2863: OPEN main_driving_cur(P_ASSIGN_START, P_ASSIGN_END, P_CITY_NAME, P_API_MODE);
2864:
2865: hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geocodes',5);
2866:
2867: LOOP
2868:
2869: BEGIN
2869: BEGIN
2870:
2871: FETCH main_driving_cur into main_old_juri_code, main_assign_id, main_new_juri_code, main_person_id,
2872: main_new_city_code, main_proc_type, main_city_tax_rule_id;
2873: hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geocodes',10);
2874:
2875: EXIT when main_driving_cur%NOTFOUND;
2876:
2877:
2874:
2875: EXIT when main_driving_cur%NOTFOUND;
2876:
2877:
2878: hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geocodes',15);
2879:
2880:
2881: hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geocodes',20);
2882:
2877:
2878: hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geocodes',15);
2879:
2880:
2881: hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geocodes',20);
2882:
2883: hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geocodes',25);
2884:
2885: -- Set the global variable for g_process_type
2879:
2880:
2881: hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geocodes',20);
2882:
2883: hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geocodes',25);
2884:
2885: -- Set the global variable for g_process_type
2886:
2887: g_process_type := main_proc_type;
2903: l_proc_stage := 'START';
2904:
2905: OPEN chk_assign_error_cur(main_assign_id, main_new_juri_code, main_old_juri_code);
2906:
2907: hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geocodes',30);
2908:
2909: FETCH chk_assign_error_cur INTO l_chk_assign_error;
2910: IF (chk_assign_error_cur%FOUND or p_api_mode = 'Y') THEN
2911: hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geocodes',35);
2907: hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geocodes',30);
2908:
2909: FETCH chk_assign_error_cur INTO l_chk_assign_error;
2910: IF (chk_assign_error_cur%FOUND or p_api_mode = 'Y') THEN
2911: hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geocodes',35);
2912:
2913: NULL; /* We do nothing here because we want the assignment to re-processed
2914: but do not create another row in the pay_us_geo_update table */
2915:
2913: NULL; /* We do nothing here because we want the assignment to re-processed
2914: but do not create another row in the pay_us_geo_update table */
2915:
2916: ELSE
2917: hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geocodes',40);
2918:
2919: -- We need to store a process type because the same geocode can have two records for different city names
2920: -- Thus we would get two rows in PAY_US_GEO_UPDATE for the same assignment id.
2921:
2924: p_person_id => main_person_id,
2925: p_assign_id => main_assign_id,
2926: p_old_juri_code => main_old_juri_code,
2927: p_new_juri_code => main_new_juri_code,
2928: p_location => null,
2929: p_id => null,
2930: p_status => 'P');
2931: hr_utility.set_location('before commit',1);
2932: -- commit;
2927: p_new_juri_code => main_new_juri_code,
2928: p_location => null,
2929: p_id => null,
2930: p_status => 'P');
2931: hr_utility.set_location('before commit',1);
2932: -- commit;
2933:
2934: END IF;
2935:
2933:
2934: END IF;
2935:
2936: CLOSE chk_assign_error_cur;
2937: hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geocodes',45);
2938:
2939: -- Create element entries and a new city record for new jusrisdictions for the assignment. We do this first
2940: -- because we want to commit based on an assignment.
2941:
2973:
2974:
2975: --Update element entry values
2976:
2977: hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geocodes',50);
2978:
2979: OPEN pev_cur(main_old_juri_code, main_assign_id);
2980: LOOP
2981: FETCH pev_cur INTO pev_rec;
2980: LOOP
2981: FETCH pev_cur INTO pev_rec;
2982: EXIT WHEN pev_cur%NOTFOUND;
2983:
2984: hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geocodes',55);
2985:
2986: l_proc_stage := 'ELEMENT_ENTRIES';
2987:
2988: element_entries(
2993: p_ele_ent_id => pev_rec.element_entry_id,
2994: p_old_juri_code => main_old_juri_code,
2995: p_new_juri_code => main_new_juri_code);
2996:
2997: hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geocodes',60);
2998:
2999:
3000: END LOOP;
3001: CLOSE pev_cur;
2999:
3000: END LOOP;
3001: CLOSE pev_cur;
3002:
3003: hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geocodes',65);
3004:
3005:
3006: -- Conditionally Update run results and run result values
3007: --Per bug 2996546 included another condition
3040: LOOP
3041: FETCH prr_cur INTO prr_rec;
3042: EXIT WHEN prr_cur%NOTFOUND;
3043:
3044: hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geocodes',70);
3045:
3046: l_proc_stage := 'RUN_RESULTS';
3047:
3048: run_results(
3054: p_old_juri_code => main_old_juri_code,
3055: p_new_juri_code => main_new_juri_code);
3056:
3057:
3058: hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geocodes',75);
3059: END LOOP;
3060: CLOSE prr_cur;
3061: END LOOP;
3062: CLOSE paa_cur;
3076: FETCH pac_cur INTO pac_rec;
3077: EXIT WHEN pac_cur%NOTFOUND;
3078:
3079:
3080: hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geocodes',240);
3081:
3082: l_proc_stage := 'PAY_ACTION_CONTEXTS';
3083:
3084: pay_action_contexts(
3090: p_old_juri_code => main_old_juri_code,
3091: p_new_juri_code => main_new_juri_code);
3092:
3093:
3094: hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geocodes',250);
3095:
3096: END LOOP;
3097: CLOSE pac_cur;
3098:
3103:
3104:
3105: OPEN fac_cur(main_assign_id, main_old_juri_code);
3106: LOOP
3107: hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geocodes',80);
3108:
3109: FETCH fac_cur INTO fac_rec;
3110: EXIT WHEN fac_cur%NOTFOUND;
3111:
3108:
3109: FETCH fac_cur INTO fac_rec;
3110: EXIT WHEN fac_cur%NOTFOUND;
3111:
3112: hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geocodes',85);
3113:
3114: l_proc_stage := 'ARCHIVE_ITEM_CONTEXTS';
3115:
3116: archive_item_contexts(
3122: P_OLD_JURi_code => main_old_juri_code,
3123: p_new_juri_code => main_new_juri_code);
3124:
3125:
3126: hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geocodes',90);
3127:
3128: END LOOP;
3129: CLOSE fac_cur;
3130:
3128: END LOOP;
3129: CLOSE fac_cur;
3130:
3131:
3132: hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geocodes',95);
3133:
3134:
3135: -- Update person balance context values.
3136:
3139: LOOP
3140: FETCH pbcv_cur INTO pbcv_rec;
3141: EXIT WHEN pbcv_cur%NOTFOUND;
3142:
3143: hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geocodes',100);
3144:
3145: l_proc_stage := 'PERSON_BALANCE_CONTEXTS';
3146:
3147: balance_contexts(
3153: p_lat_bal_id => pbcv_rec.latest_balance_id,
3154: p_old_juri_code => main_old_juri_code,
3155: p_new_juri_code => main_new_juri_code);
3156:
3157: hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geocodes',105);
3158:
3159:
3160: END LOOP;
3161: CLOSE pbcv_cur;
3162:
3163: -- Update assignment balance context values.
3164:
3165: OPEN pacv_cur(main_old_juri_code, main_assign_id, main_person_id);
3166: hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geocodes',110);
3167:
3168: LOOP
3169: FETCH pacv_cur INTO pacv_rec;
3170: EXIT WHEN pacv_cur%NOTFOUND;
3168: LOOP
3169: FETCH pacv_cur INTO pacv_rec;
3170: EXIT WHEN pacv_cur%NOTFOUND;
3171:
3172: hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geocodes',115);
3173:
3174: l_proc_stage := 'ASSIGNMENT_BALANCE_CONTEXTS';
3175:
3176: balance_contexts(
3183: p_old_juri_code => main_old_juri_code,
3184: p_new_juri_code => main_new_juri_code);
3185:
3186:
3187: hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geocodes',120);
3188:
3189:
3190: END LOOP;
3191: CLOSE pacv_cur;
3189:
3190: END LOOP;
3191: CLOSE pacv_cur;
3192:
3193: hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geocodes',125);
3194:
3195: -- Rosie Monge 10/17/2005
3196: -- Update Pay_Latest_balances context values.
3197:
3195: -- Rosie Monge 10/17/2005
3196: -- Update Pay_Latest_balances context values.
3197:
3198: OPEN plbcv_cur(main_old_juri_code, main_assign_id, main_person_id);
3199: hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geocodes',126 );
3200: LOOP
3201: FETCH plbcv_cur INTO plbcv_rec;
3202:
3203: EXIT WHEN plbcv_cur%NOTFOUND;
3201: FETCH plbcv_cur INTO plbcv_rec;
3202:
3203: EXIT WHEN plbcv_cur%NOTFOUND;
3204:
3205: hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geocodes',127);
3206:
3207: l_proc_stage := 'PAY_LATEST_BALANCES_CONTEXT';
3208:
3209: balance_contexts(
3216: p_old_juri_code => main_old_juri_code,
3217: p_new_juri_code => main_new_juri_code);
3218:
3219:
3220: hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geocodes',128);
3221:
3222: END LOOP;
3223: CLOSE plbcv_cur;
3224: -- End Rosie Monge addition for bug 4602222
3233: p_assign_id => main_assign_id,
3234: p_old_juri_code => main_old_juri_code,
3235: p_new_juri_code => main_new_juri_code);
3236:
3237: hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geocodes',130);
3238:
3239: ---
3240: ---
3241: ---
3251: p_new_city_code => main_new_city_code,
3252: p_old_juri_code => main_old_juri_code,
3253: p_new_juri_code => main_new_juri_code);
3254:
3255: hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geocodes',131);
3256: ---
3257: ---
3258: ---
3259:
3257: ---
3258: ---
3259:
3260:
3261: -- Check for and delete any duplicate Vertex element entries
3262: -- This can be caused by two geocodes combining.
3263: -- We will then add the percentages togethor before deleting.
3264:
3265: hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geocodes',135);
3261: -- Check for and delete any duplicate Vertex element entries
3262: -- This can be caused by two geocodes combining.
3263: -- We will then add the percentages togethor before deleting.
3264:
3265: hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geocodes',135);
3266:
3267: l_proc_stage := 'DUPLICATE_VERTEX_EE';
3268:
3269: duplicate_vertex_ee(main_assign_id);
3263: -- We will then add the percentages togethor before deleting.
3264:
3265: hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geocodes',135);
3266:
3267: l_proc_stage := 'DUPLICATE_VERTEX_EE';
3268:
3269: duplicate_vertex_ee(main_assign_id);
3270:
3271: hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geocodes',140);
3265: hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geocodes',135);
3266:
3267: l_proc_stage := 'DUPLICATE_VERTEX_EE';
3268:
3269: duplicate_vertex_ee(main_assign_id);
3270:
3271: hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geocodes',140);
3272:
3273:
3267: l_proc_stage := 'DUPLICATE_VERTEX_EE';
3268:
3269: duplicate_vertex_ee(main_assign_id);
3270:
3271: hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geocodes',140);
3272:
3273:
3274: --Update the pay_us_emp_city_tax_rules_f table.
3275:
3273:
3274: --Update the pay_us_emp_city_tax_rules_f table.
3275:
3276: OPEN city_rec_cur(main_new_juri_code, main_old_juri_code, main_assign_id, main_city_tax_rule_id);
3277: hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geocodes',145);
3278:
3279: FETCH city_rec_cur INTO l_city_tax_exists;
3280: CLOSE city_rec_cur;
3281:
3290: p_new_juri_code => main_new_juri_code,
3291: p_new_city_code => main_new_city_code,
3292: p_city_tax_record_id => main_city_tax_rule_id);
3293:
3294: hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geocodes',150);
3295:
3296: END IF;
3297:
3298:
3297:
3298:
3299:
3300:
3301: hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geocodes',155);
3302:
3303:
3304: -- Now we check for assignments with more than 100% time in jurisdiction
3305:
3306: l_proc_stage := 'CHECK_TIME';
3307:
3308: check_time(p_assign_id => main_assign_id);
3309:
3310: hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geocodes',160);
3311:
3312: -- Now we update the SU and US cases to a status of 'A' for assignments that need to be updated
3313: -- via the API. If the cursor is found then we will update the status to 'A', only if the assignment
3314: -- was not updated because the same jurisdiction also had another type.
3315:
3316: l_proc_stage := 'SET API';
3317:
3318: OPEN chk_assign_api_cur(main_assign_id, main_new_juri_code, main_old_juri_code);
3319: hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geocodes',165);
3320:
3321: FETCH chk_assign_api_cur into l_chk_assign_api;
3322: hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geocodes',170);
3323:
3318: OPEN chk_assign_api_cur(main_assign_id, main_new_juri_code, main_old_juri_code);
3319: hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geocodes',165);
3320:
3321: FETCH chk_assign_api_cur into l_chk_assign_api;
3322: hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geocodes',170);
3323:
3324: CLOSE chk_assign_api_cur;
3325:
3326: IF (l_chk_assign_api = 'Y' and p_api_mode = 'N') THEN
3324: CLOSE chk_assign_api_cur;
3325:
3326: IF (l_chk_assign_api = 'Y' and p_api_mode = 'N') THEN
3327:
3328: hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geocodes',175);
3329:
3330: UPDATE PAY_US_GEO_UPDATE
3331: SET status = 'A', description = null
3332: WHERE assignment_id = main_assign_id
3339:
3340: ELSE
3341: -- Now we update the assignment that has just processed to a status of 'C' in PAY_US_GEO_UPDATE
3342:
3343: hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geocodes',180);
3344:
3345: l_proc_stage := 'END';
3346:
3347: UPDATE PAY_US_GEO_UPDATE
3353: AND table_value_id is null
3354: AND status in ('P','A')
3355: AND process_type = main_proc_type;
3356:
3357: hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geocodes',185);
3358:
3359: END IF;
3360:
3361: hr_utility.set_location('before commit',2);
3357: hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geocodes',185);
3358:
3359: END IF;
3360:
3361: hr_utility.set_location('before commit',2);
3362: -- commit; /* We commit at this point so if it fails at any point let it rollback to the savepoint and continue */
3363:
3364:
3365: hr_utility.trace('Exiting pay_us_geo_upd_pkg.upgrade_geocodes');
3380: fnd_file.put_line(fnd_file.log, 'sql error ' || sqlcode || ' - ' || substr(sqlerrm,1,80));
3381:
3382: rollback to GEO_UPDATE_SAVEPOINT;
3383:
3384: hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geocodes',170);
3385:
3386: UPDATE PAY_US_GEO_UPDATE
3387: SET description = l_error_text
3388: WHERE assignment_id = main_assign_id
3434: IF chk_assign_api_cur%ISOPEN THEN
3435: CLOSE chk_assign_api_cur;
3436: END IF;
3437:
3438: hr_utility.set_location('before commit',3);
3439: -- commit;
3440:
3441: END;
3442:
3443: END LOOP;
3444:
3445: CLOSE main_driving_cur;
3446:
3447: -- Remove duplicate city tax records created
3448: -- by geocode updates for all assignment ids
3449: -- in the range processed.
3450:
3451: del_dup_city_tax_recs;
3532: hr_utility.trace('The phase id is: '||to_char(g_geo_phase_id));
3533:
3534: FOR ptax_rec IN ptax_cur LOOP
3535:
3536: hr_utility.set_location('pay_us_geo_upd_pkg.update_taxability_rules',1);
3537:
3538: SELECT pmod.state_code||'-'||pmod.county_code||'-'||pmod.new_city_code,
3539: process_type
3540: INTO jd_code, l_proc_type
3546: --city taxability rules don't carry a county-code so we have to pull the first
3547: -- row in the case of a city that spans a county.
3548: and rownum = 1;
3549:
3550: hr_utility.set_location('pay_us_geo_upd_pkg.update_taxability_rules',2);
3551:
3552: select count(*) into l_count
3553: from pay_taxability_rules ptax
3554: where ptax.jurisdiction_code = substr(jd_code,1,2)||'-000-'||substr(jd_code,8,4);
3567:
3568: END IF;
3569:
3570: END IF;
3571: hr_utility.set_location('pay_us_geo_upd_pkg.update_taxability_rules',3);
3572:
3573: -- write to the message table so that if this fails unexpectedly we can track which taxability
3574: -- rules have been upgraded already.
3575:
3578: p_person_id => null,
3579: p_assign_id => null,
3580: p_old_juri_code => ptax_rec.jurisdiction_code,
3581: p_new_juri_code => substr(jd_code,1,2)||'-000-'||substr(jd_code,8,4),
3582: p_location => 'PAY_TAXABILITY_RULES',
3583: p_id => null);
3584:
3585: hr_utility.set_location('pay_us_geo_upd_pkg.update_taxability_rules',4);
3586:
3581: p_new_juri_code => substr(jd_code,1,2)||'-000-'||substr(jd_code,8,4),
3582: p_location => 'PAY_TAXABILITY_RULES',
3583: p_id => null);
3584:
3585: hr_utility.set_location('pay_us_geo_upd_pkg.update_taxability_rules',4);
3586:
3587: END LOOP;
3588: --
3589: --
3596: LOOP
3597: FETCH ptax_ca_cur INTO ptax_ca_rec;
3598: EXIT WHEN ptax_ca_cur%NOTFOUND;
3599:
3600: hr_utility.set_location('pay_us_geo_upd_pkg.update_taxability_rules',15);
3601:
3602: SELECT pmod.new_county_code,
3603: process_type
3604: INTO jd_code, l_proc_type
3615:
3616: -- COMMIT;
3617:
3618: END IF;
3619: hr_utility.set_location('pay_us_geo_upd_pkg.update_taxability_rules',20);
3620:
3621: -- write to the message table so that if this fails unexpectedly we can track which taxability
3622: -- rules have been upgraded already.
3623:
3626: p_person_id => null,
3627: p_assign_id => null,
3628: p_old_juri_code => ptax_ca_rec.jurisdiction_code,
3629: p_new_juri_code => jd_code||'-000-'||'0000',
3630: p_location => 'PAY_TAXABILITY_RULES',
3631: p_id => null);
3632:
3633: hr_utility.set_location('pay_us_geo_upd_pkg.update_taxability_rules',25);
3634:
3629: p_new_juri_code => jd_code||'-000-'||'0000',
3630: p_location => 'PAY_TAXABILITY_RULES',
3631: p_id => null);
3632:
3633: hr_utility.set_location('pay_us_geo_upd_pkg.update_taxability_rules',25);
3634:
3635:
3636: END LOOP;
3637: CLOSE ptax_ca_cur ;
3637: CLOSE ptax_ca_cur ;
3638: --
3639: --
3640: --
3641: hr_utility.set_location('pay_us_geo_upd_pkg.update_taxability_rules',5);
3642:
3643: EXCEPTION
3644: WHEN OTHERS THEN
3645:
3644: WHEN OTHERS THEN
3645:
3646: l_error_message_text := to_char(SQLCODE)||SQLERRM||' Program error contact support';
3647: rollback;
3648: hr_utility.set_location('pay_us_geo_upd_pkg.update_taxability_rules',6);
3649:
3650: fnd_file.put_line(fnd_file.log, 'Exception update_taxability_rules' );
3651: fnd_file.put_line(fnd_file.log, 'sql error ' || sqlcode || ' - ' || substr(sqlerrm,1,80));
3652:
3649:
3650: fnd_file.put_line(fnd_file.log, 'Exception update_taxability_rules' );
3651: fnd_file.put_line(fnd_file.log, 'sql error ' || sqlcode || ' - ' || substr(sqlerrm,1,80));
3652:
3653: raise_application_error(-20001,l_error_message_text);
3654:
3655:
3656: END update_taxability_rules;
3657:
3666: --into pay_us_modified_geocodes table with process_type as 'CN'. It has Old County Name
3667: --stored in city_name field. old_city_code and new_city_code will be shown as '0000'
3668: --which will differentiate it from regular city_code changes stored in that table.
3669: --Process_Type will be 'CN'. For every year, the County_Name changes will be found
3670: --from pay_us_modified_geocodes table and corresponding Address and Location details
3671: --are updated with new county name.
3672:
3673: /*This procedure is called from pay_us_geo_upd_pkg.action_creation. For an Year, if there
3674: are no assignments impacted by the city_name changes delivered the Submission of
3722: hr_utility.trace('The phase id is: '||to_char(g_geo_phase_id));
3723: hr_utility.trace('The Patch Name is : '||P_PATCH_NAME);
3724: hr_utility.trace('Call type to procedure: '||P_CALL);
3725:
3726: hr_utility.set_location('pay_us_geo_upd_pkg.update_county_name',1);
3727:
3728: OPEN get_county_name_changes(p_patch_name);
3729: FETCH get_county_name_changes INTO l_county_name_change;
3730:
3748: AND add_information17 = l_county_name_change.state_abbrev;
3749:
3750: l_count := l_count + SQL%ROWCOUNT;
3751:
3752: hr_utility.set_location('pay_us_geo_upd_pkg.update_county_name',2);
3753:
3754: ELSE
3755:
3756: l_count := 0;
3768: AND add_information17 = l_county_name_change.state_abbrev;
3769:
3770: l_count := l_count + l_override_count;
3771:
3772: hr_utility.set_location('pay_us_geo_upd_pkg.update_county_name',3);
3773:
3774: END IF; /*G_MODE = 'UPGRADE' if*/
3775:
3776: IF l_count > 0 THEN
3774: END IF; /*G_MODE = 'UPGRADE' if*/
3775:
3776: IF l_count > 0 THEN
3777:
3778: hr_utility.set_location('pay_us_geo_upd_pkg.update_county_name',4);
3779:
3780: l_description := 'County '||l_county_name_change.old_county_name||', '||
3781: l_county_name_change.state_abbrev||' renamed to '||
3782: l_county_name_change.new_county_name||'. Corresponding Person Address Records Updated.';
3792: p_person_id => null,
3793: p_assign_id => null,
3794: p_old_juri_code => null,
3795: p_new_juri_code => l_count,
3796: p_location => 'PER_ADDRESSES',
3797: p_id => p_geo_phase_id,
3798: p_description => l_description);
3799:
3800: END IF; /* l_count > 0 if*/
3802: IF G_MODE = 'UPGRADE' THEN
3803:
3804: l_count := 0;
3805:
3806: UPDATE hr_locations_all
3807: SET region_1 = l_county_name_change.new_county_name
3808: WHERE region_1 = l_county_name_change.old_county_name
3809: AND region_2 = l_county_name_change.state_abbrev
3810: AND country = l_county_name_change.country;
3810: AND country = l_county_name_change.country;
3811:
3812: l_count := SQL%ROWCOUNT;
3813:
3814: UPDATE hr_locations_all
3815: SET loc_information19 = l_county_name_change.new_county_name
3816: WHERE loc_information19 = l_county_name_change.old_county_name
3817: AND loc_information17 = l_county_name_change.state_abbrev;
3818:
3817: AND loc_information17 = l_county_name_change.state_abbrev;
3818:
3819: l_count := l_count + SQL%ROWCOUNT;
3820:
3821: hr_utility.set_location('pay_us_geo_upd_pkg.update_county_name',5);
3822:
3823: ELSE
3824:
3825: l_count := 0;
3825: l_count := 0;
3826: l_override_count := 0;
3827:
3828: SELECT count(*) INTO l_count
3829: FROM hr_locations_all
3830: WHERE region_1 = l_county_name_change.old_county_name
3831: AND region_2 = l_county_name_change.state_abbrev
3832: AND country = l_county_name_change.country;
3833:
3831: AND region_2 = l_county_name_change.state_abbrev
3832: AND country = l_county_name_change.country;
3833:
3834: SELECT count(*) INTO l_override_count
3835: FROM hr_locations_all
3836: WHERE LOC_INFORMATION19 = l_county_name_change.old_county_name
3837: AND LOC_INFORMATION17 = l_county_name_change.state_abbrev;
3838:
3839: l_count := l_count + l_override_count;
3837: AND LOC_INFORMATION17 = l_county_name_change.state_abbrev;
3838:
3839: l_count := l_count + l_override_count;
3840:
3841: hr_utility.set_location('pay_us_geo_upd_pkg.update_county_name',6);
3842:
3843: END IF; /*G_MODE = 'UPGRADE' if*/
3844:
3845: IF l_count > 0 THEN
3843: END IF; /*G_MODE = 'UPGRADE' if*/
3844:
3845: IF l_count > 0 THEN
3846:
3847: hr_utility.set_location('pay_us_geo_upd_pkg.update_county_name',7);
3848:
3849: l_description := 'County '||l_county_name_change.old_county_name||', '||
3850: l_county_name_change.state_abbrev||' renamed to '||
3851: l_county_name_change.new_county_name||'. Corresponding Location Address Records Updated.';
3847: hr_utility.set_location('pay_us_geo_upd_pkg.update_county_name',7);
3848:
3849: l_description := 'County '||l_county_name_change.old_county_name||', '||
3850: l_county_name_change.state_abbrev||' renamed to '||
3851: l_county_name_change.new_county_name||'. Corresponding Location Address Records Updated.';
3852:
3853: IF P_CALL = 'EXTERNAL' THEN
3854:
3855: fnd_file.put_line(fnd_file.log, l_description);
3861: p_person_id => null,
3862: p_assign_id => null,
3863: p_old_juri_code => null,
3864: p_new_juri_code => l_count,
3865: p_location => 'HR_LOCATIONS_ALL',
3866: p_id => p_geo_phase_id,
3867: p_description => l_description);
3868:
3869: END IF; /* l_count > 0 if*/
3873: END LOOP;
3874:
3875: CLOSE get_county_name_changes;
3876:
3877: hr_utility.set_location('pay_us_geo_upd_pkg.update_county_name',8);
3878:
3879: EXCEPTION
3880: WHEN OTHERS THEN
3881:
3880: WHEN OTHERS THEN
3881:
3882: l_error_message_text := to_char(SQLCODE)||SQLERRM||' Program error contact support';
3883: rollback;
3884: hr_utility.set_location('pay_us_geo_upd_pkg.update_county_name',11);
3885:
3886: fnd_file.put_line(fnd_file.log, 'Exception update_county_name' );
3887: fnd_file.put_line(fnd_file.log, 'sql error ' || sqlcode || ' - ' || substr(sqlerrm,1,80));
3888:
3885:
3886: fnd_file.put_line(fnd_file.log, 'Exception update_county_name' );
3887: fnd_file.put_line(fnd_file.log, 'sql error ' || sqlcode || ' - ' || substr(sqlerrm,1,80));
3888:
3889: raise_application_error(-20001,l_error_message_text);
3890:
3891: END update_county_name;
3892:
3893: --End Bug#9541247
3895: /* Added for Annual GEO 2012 for Bug#14314081
3896:
3897: This Procedure is added to take care of the City Name changes delivered. The
3898: City Name delivered in PAY_US_CITY_NAMES gets copied into other tables as
3899: it is used in Person Address or Location Address etc. Since we are changing
3900: the City Name we delivered earlier, it is necessary to update the City Name
3901: details stored in other tables.
3902:
3903: For each of the City Name that got modified, an entry will be created in table
4019: hr_utility.trace('The phase id is: '||to_char(g_geo_phase_id));
4020: hr_utility.trace('The Patch Name is : '||P_PATCH_NAME);
4021: hr_utility.trace('Call type to procedure: '||P_CALL);
4022:
4023: hr_utility.set_location('pay_us_geo_upd_pkg.update_city_name',1);
4024:
4025: OPEN get_city_name_changes(p_patch_name);
4026: FETCH get_city_name_changes INTO l_city_name_change;
4027:
4125: AND add_information17 = l_city_name_change.state_abbrev;
4126:
4127: END IF;
4128:
4129: hr_utility.set_location('pay_us_geo_upd_pkg.update_city_name',2);
4130:
4131: INSERT INTO pay_us_geo_update
4132: (
4133: id,
4146: SELECT DISTINCT
4147: p_geo_phase_id,
4148: NULL,
4149: NULL,
4150: 'HR_LOCATIONS_ALL',
4151: hl.location_id,
4152: l_jurisdiction_code,
4153: l_jurisdiction_code,
4154: 'CY',
4147: p_geo_phase_id,
4148: NULL,
4149: NULL,
4150: 'HR_LOCATIONS_ALL',
4151: hl.location_id,
4152: l_jurisdiction_code,
4153: l_jurisdiction_code,
4154: 'CY',
4155: sysdate,
4155: sysdate,
4156: p_mode,
4157: NULL,
4158: 'Address'||':'||l_city_name_change.old_city_name
4159: FROM hr_locations_all hl
4160: WHERE town_or_city = l_city_name_change.old_city_name
4161: AND region_1 = l_county_name
4162: AND NVL(region_2,l_city_name_change.state_abbrev) =
4163: DECODE(country,'US',l_city_name_change.state_abbrev,NVL(region_2,l_city_name_change.state_abbrev))
4162: AND NVL(region_2,l_city_name_change.state_abbrev) =
4163: DECODE(country,'US',l_city_name_change.state_abbrev,NVL(region_2,l_city_name_change.state_abbrev))
4164: AND country = l_city_name_change.country;
4165:
4166: UPDATE hr_locations_all
4167: SET town_or_city = l_city_name_change.new_city_name,
4168: derived_locale = replace(derived_locale,
4169: l_city_name_change.old_city_name,
4170: l_city_name_change.new_city_name)
4194: SELECT DISTINCT
4195: p_geo_phase_id,
4196: NULL,
4197: NULL,
4198: 'HR_LOCATIONS_ALL',
4199: hl.location_id,
4200: l_jurisdiction_code,
4201: l_jurisdiction_code,
4202: 'CY',
4195: p_geo_phase_id,
4196: NULL,
4197: NULL,
4198: 'HR_LOCATIONS_ALL',
4199: hl.location_id,
4200: l_jurisdiction_code,
4201: l_jurisdiction_code,
4202: 'CY',
4203: sysdate,
4203: sysdate,
4204: p_mode,
4205: NULL,
4206: 'Payroll Tax Address'||':'||l_city_name_change.old_city_name
4207: FROM hr_locations_all hl
4208: WHERE loc_information18 = l_city_name_change.old_city_name
4209: AND loc_information19 = l_county_name
4210: AND loc_information17 = l_city_name_change.state_abbrev;
4211:
4208: WHERE loc_information18 = l_city_name_change.old_city_name
4209: AND loc_information19 = l_county_name
4210: AND loc_information17 = l_city_name_change.state_abbrev;
4211:
4212: UPDATE hr_locations_all
4213: SET loc_information18 = l_city_name_change.new_city_name
4214: WHERE loc_information18 = l_city_name_change.old_city_name
4215: AND loc_information19 = l_county_name
4216: AND loc_information17 = l_city_name_change.state_abbrev;
4257: END IF;
4258:
4259: ELSE
4260:
4261: hr_utility.set_location('pay_us_geo_upd_pkg.update_city_name',3);
4262:
4263: OPEN get_new_county_name(l_city_name_change.state_code,l_city_name_change.county_code);
4264: FETCH get_new_county_name INTO l_county_name;
4265:
4357: SELECT DISTINCT
4358: p_geo_phase_id,
4359: NULL,
4360: NULL,
4361: 'HR_LOCATIONS_ALL',
4362: hl.location_id,
4363: l_jurisdiction_code,
4364: l_jurisdiction_code,
4365: 'CY',
4358: p_geo_phase_id,
4359: NULL,
4360: NULL,
4361: 'HR_LOCATIONS_ALL',
4362: hl.location_id,
4363: l_jurisdiction_code,
4364: l_jurisdiction_code,
4365: 'CY',
4366: sysdate,
4366: sysdate,
4367: p_mode,
4368: NULL,
4369: 'Address'||':'||l_city_name_change.old_city_name
4370: FROM hr_locations_all hl
4371: WHERE town_or_city = l_city_name_change.old_city_name
4372: AND region_1 = l_county_name
4373: AND NVL(region_2,l_city_name_change.state_abbrev) =
4374: DECODE(country,'US',l_city_name_change.state_abbrev,NVL(region_2,l_city_name_change.state_abbrev))
4394: SELECT DISTINCT
4395: p_geo_phase_id,
4396: NULL,
4397: NULL,
4398: 'HR_LOCATIONS_ALL',
4399: hl.location_id,
4400: l_jurisdiction_code,
4401: l_jurisdiction_code,
4402: 'CY',
4395: p_geo_phase_id,
4396: NULL,
4397: NULL,
4398: 'HR_LOCATIONS_ALL',
4399: hl.location_id,
4400: l_jurisdiction_code,
4401: l_jurisdiction_code,
4402: 'CY',
4403: sysdate,
4403: sysdate,
4404: p_mode,
4405: NULL,
4406: 'Payroll Tax Address'||':'||l_city_name_change.old_city_name
4407: FROM hr_locations_all hl
4408: WHERE loc_information18 = l_city_name_change.old_city_name
4409: AND loc_information19 = l_county_name
4410: AND loc_information17 = l_city_name_change.state_abbrev;
4411:
4416: END LOOP;
4417:
4418: CLOSE get_new_county_name;
4419:
4420: hr_utility.set_location('pay_us_geo_upd_pkg.update_city_name',4);
4421:
4422: OPEN get_county_name(l_city_name_change.state_code,l_city_name_change.county_code);
4423: FETCH get_county_name INTO l_county_name;
4424: CLOSE get_county_name;
4513: SELECT DISTINCT
4514: p_geo_phase_id,
4515: NULL,
4516: NULL,
4517: 'HR_LOCATIONS_ALL',
4518: hl.location_id,
4519: l_jurisdiction_code,
4520: l_jurisdiction_code,
4521: 'CY',
4514: p_geo_phase_id,
4515: NULL,
4516: NULL,
4517: 'HR_LOCATIONS_ALL',
4518: hl.location_id,
4519: l_jurisdiction_code,
4520: l_jurisdiction_code,
4521: 'CY',
4522: sysdate,
4522: sysdate,
4523: p_mode,
4524: NULL,
4525: 'Address'||':'||l_city_name_change.old_city_name
4526: FROM hr_locations_all hl
4527: WHERE town_or_city = l_city_name_change.old_city_name
4528: AND region_1 = l_county_name
4529: AND NVL(region_2,l_city_name_change.state_abbrev) =
4530: DECODE(country,'US',l_city_name_change.state_abbrev,NVL(region_2,l_city_name_change.state_abbrev))
4550: SELECT DISTINCT
4551: p_geo_phase_id,
4552: NULL,
4553: NULL,
4554: 'HR_LOCATIONS_ALL',
4555: hl.location_id,
4556: l_jurisdiction_code,
4557: l_jurisdiction_code,
4558: 'CY',
4551: p_geo_phase_id,
4552: NULL,
4553: NULL,
4554: 'HR_LOCATIONS_ALL',
4555: hl.location_id,
4556: l_jurisdiction_code,
4557: l_jurisdiction_code,
4558: 'CY',
4559: sysdate,
4559: sysdate,
4560: p_mode,
4561: NULL,
4562: 'Payroll Tax Address'||':'||l_city_name_change.old_city_name
4563: FROM hr_locations_all hl
4564: WHERE loc_information18 = l_city_name_change.old_city_name
4565: AND loc_information19 = l_county_name
4566: AND loc_information17 = l_city_name_change.state_abbrev;
4567:
4599: AND org_information_context = 'EEO_REPORT';
4600:
4601: END IF;
4602:
4603: hr_utility.set_location('pay_us_geo_upd_pkg.update_city_name',5);
4604:
4605: END IF; /*G_MODE = 'UPGRADE' if*/
4606:
4607: FETCH get_city_name_changes INTO l_city_name_change;
4615: pay_us_geocode_report_pkg.city_name_change_report('EXTERNAL',P_PATCH_NAME,G_MODE,p_geo_phase_id);
4616:
4617: END IF;
4618:
4619: hr_utility.set_location('pay_us_geo_upd_pkg.update_city_name',6);
4620:
4621: EXCEPTION
4622: WHEN OTHERS THEN
4623:
4622: WHEN OTHERS THEN
4623:
4624: l_error_message_text := to_char(SQLCODE)||SQLERRM||' Program error contact support';
4625: ROLLBACK;
4626: hr_utility.set_location('pay_us_geo_upd_pkg.update_city_name',99);
4627:
4628: fnd_file.put_line(fnd_file.log, 'Exception update_city_name' );
4629: fnd_file.put_line(fnd_file.log, 'sql error ' || sqlcode || ' - ' || substr(sqlerrm,1,80));
4630:
4627:
4628: fnd_file.put_line(fnd_file.log, 'Exception update_city_name' );
4629: fnd_file.put_line(fnd_file.log, 'sql error ' || sqlcode || ' - ' || substr(sqlerrm,1,80));
4630:
4631: raise_application_error(-20001,l_error_message_text);
4632:
4633: END update_city_name;
4634:
4635: /* End of Changes for Bug#14314081 */
4706: hr_utility.trace('The phase id is: '||to_char(g_geo_phase_id));
4707:
4708: FOR org_info_rec IN org_info_cur LOOP
4709:
4710: hr_utility.set_location('pay_us_geo_upd_pkg.update_org_info',1);
4711:
4712: SELECT pmod.state_code||'-'||pmod.county_code||'-'||pmod.new_city_code,
4713: process_type
4714: INTO new_geocode, l_proc_type
4718: AND pmod.old_city_code = substr(org_info_rec.org_information1,8,4)
4719: AND pmod.process_type in ('UP','PU','RP','U')
4720: AND pmod.patch_name = p_patch_name;
4721:
4722: hr_utility.set_location('pay_us_geo_upd_pkg.update_org_info',2);
4723:
4724: IF G_MODE = 'UPGRADE' THEN
4725:
4726: UPDATE hr_organization_information
4731: -- COMMIT;
4732:
4733: END IF;
4734:
4735: hr_utility.set_location('pay_us_geo_upd_pkg.update_org_info',3);
4736:
4737: -- write to the message table so that if this fails unexpectedly we can track which taxability
4738: -- rules have been upgraded already.
4739:
4742: p_person_id => null,
4743: p_assign_id => null,
4744: p_old_juri_code => org_info_rec.org_information1,
4745: p_new_juri_code => new_geocode,
4746: p_location => 'HR_ORGANIZATION_INFORMATION',
4747: p_id => null);
4748:
4749: hr_utility.set_location('pay_us_geo_upd_pkg.update_org_info',4);
4750:
4745: p_new_juri_code => new_geocode,
4746: p_location => 'HR_ORGANIZATION_INFORMATION',
4747: p_id => null);
4748:
4749: hr_utility.set_location('pay_us_geo_upd_pkg.update_org_info',4);
4750:
4751: END LOOP;
4752:
4753: hr_utility.set_location('pay_us_geo_upd_pkg.update_org_info',5);
4749: hr_utility.set_location('pay_us_geo_upd_pkg.update_org_info',4);
4750:
4751: END LOOP;
4752:
4753: hr_utility.set_location('pay_us_geo_upd_pkg.update_org_info',5);
4754: --
4755: --
4756: --
4757:
4763: LOOP
4764: FETCH org_info_ca_cur into org_info_ca_rec;
4765: EXIT WHEN org_info_ca_cur%NOTFOUND;
4766:
4767: hr_utility.set_location('pay_us_geo_upd_pkg.update_org_info',15);
4768:
4769:
4770: SELECT pmod.new_county_code,
4771: process_type
4793: -- COMMIT;
4794:
4795:
4796: END IF;
4797: hr_utility.set_location('pay_us_geo_upd_pkg.update_org_info',15);
4798:
4799: -- write to the message table so that if this fails unexpectedly
4800: --
4801:
4804: p_person_id => null,
4805: p_assign_id => null,
4806: p_old_juri_code => org_info_rec.org_information1,
4807: p_new_juri_code => new_geocode,
4808: p_location => 'HR_ORGANIZATION_INFORMATION',
4809: p_id => null);
4810:
4811: hr_utility.set_location('pay_us_geo_upd_pkg.update_org_info',20);
4812: END LOOP ;
4807: p_new_juri_code => new_geocode,
4808: p_location => 'HR_ORGANIZATION_INFORMATION',
4809: p_id => null);
4810:
4811: hr_utility.set_location('pay_us_geo_upd_pkg.update_org_info',20);
4812: END LOOP ;
4813: CLOSE org_info_ca_cur;
4814: --
4815: --
4818: EXCEPTION
4819: WHEN OTHERS THEN
4820: l_error_message_text := to_char(SQLCODE)||SQLERRM||' Program error contact support';
4821: rollback;
4822: hr_utility.set_location('pay_us_geo_upd_pkg.update_org_info',6);
4823:
4824: fnd_file.put_line(fnd_file.log, 'Exception update_org_info' );
4825: fnd_file.put_line(fnd_file.log, 'sql error ' || sqlcode || ' - ' || substr(sqlerrm,1,80));
4826:
4823:
4824: fnd_file.put_line(fnd_file.log, 'Exception update_org_info' );
4825: fnd_file.put_line(fnd_file.log, 'sql error ' || sqlcode || ' - ' || substr(sqlerrm,1,80));
4826:
4827: raise_application_error(-20001,l_error_message_text);
4828:
4829: END update_org_info;
4830:
4831:
4867: hr_utility.trace('Entering the Geocode Upgrade API');
4868:
4869:
4870: OPEN pay_patch_id(p_patch_name);
4871: hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geo_api',1);
4872:
4873: FETCH pay_patch_id INTO l_id;
4874: hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geo_api',5);
4875:
4870: OPEN pay_patch_id(p_patch_name);
4871: hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geo_api',1);
4872:
4873: FETCH pay_patch_id INTO l_id;
4874: hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geo_api',5);
4875:
4876: CLOSE pay_patch_id;
4877:
4878: hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geo_api',10);
4874: hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geo_api',5);
4875:
4876: CLOSE pay_patch_id;
4877:
4878: hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geo_api',10);
4879:
4880: IF p_mode = 'UPGRADE' THEN
4881:
4882: hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geo_api',15);
4878: hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geo_api',10);
4879:
4880: IF p_mode = 'UPGRADE' THEN
4881:
4882: hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geo_api',15);
4883:
4884: upgrade_geocodes(P_ASSIGN_START => p_assign_id,
4885: P_ASSIGN_END => p_assign_id,
4886: P_GEO_PHASE_ID => l_id,
4888: P_PATCH_NAME => p_patch_name,
4889: P_CITY_NAME => p_city_name,
4890: P_API_MODE => 'Y');
4891:
4892: hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geo_api',20);
4893:
4894: ELSE
4895:
4896: hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geo_api',25);
4892: hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geo_api',20);
4893:
4894: ELSE
4895:
4896: hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geo_api',25);
4897:
4898: upgrade_geocodes(P_ASSIGN_START => p_assign_id,
4899: P_ASSIGN_END => p_assign_id,
4900: P_GEO_PHASE_ID => l_id,
4902: P_PATCH_NAME => p_patch_name,
4903: P_CITY_NAME => p_city_name,
4904: P_API_MODE => 'Y');
4905:
4906: hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geo_api',30);
4907:
4908: END IF;
4909:
4910: hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geo_api',35);
4906: hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geo_api',30);
4907:
4908: END IF;
4909:
4910: hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geo_api',35);
4911:
4912: OPEN chk_last_api(l_id, p_mode);
4913:
4914: hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geo_api',40);
4910: hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geo_api',35);
4911:
4912: OPEN chk_last_api(l_id, p_mode);
4913:
4914: hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geo_api',40);
4915:
4916: FETCH chk_last_api INTO l_chk_last_api;
4917:
4918: hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geo_api',45);
4914: hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geo_api',40);
4915:
4916: FETCH chk_last_api INTO l_chk_last_api;
4917:
4918: hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geo_api',45);
4919:
4920: IF chk_last_api%NOTFOUND THEN /* Everything is complete we can update pay_patch_status to complete */
4921:
4922: hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geo_api',50);
4918: hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geo_api',45);
4919:
4920: IF chk_last_api%NOTFOUND THEN /* Everything is complete we can update pay_patch_status to complete */
4921:
4922: hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geo_api',50);
4923:
4924: UPDATE pay_patch_status
4925: SET status = 'C', phase = null
4926: WHERE id = l_id;
4923:
4924: UPDATE pay_patch_status
4925: SET status = 'C', phase = null
4926: WHERE id = l_id;
4927: hr_utility.set_location('before commit ',4);
4928:
4929: -- commit;
4930:
4931: hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geo_api',55);
4927: hr_utility.set_location('before commit ',4);
4928:
4929: -- commit;
4930:
4931: hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geo_api',55);
4932:
4933: END IF;
4934:
4935: hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geo_api',60);
4931: hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geo_api',55);
4932:
4933: END IF;
4934:
4935: hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geo_api',60);
4936:
4937: CLOSE chk_last_api;
4938:
4939: hr_utility.trace('Exiting the Geocode Upgrade API');
5030: LOOP
5031: FETCH canada_emp_fed_tax_cur into canada_emp_fed_rec;
5032: EXIT WHEN canada_emp_fed_tax_cur%NOTFOUND;
5033:
5034: hr_utility.set_location('pay_us_geo_upd_pkg.update_ca_emp_info',1);
5035: SELECT pmod.new_county_code,
5036: pmod.process_type
5037: INTO new_geocode, l_proc_type
5038: FROM pay_us_modified_geocodes pmod
5053:
5054: END IF;
5055:
5056:
5057: hr_utility.set_location('pay_us_geo_upd_pkg.update_ca_emp_info',2);
5058: -- write to the message table so that if this fails unexpectedly
5059: write_message(
5060: p_proc_type => l_proc_type,
5061: p_person_id => null,
5061: p_person_id => null,
5062: p_assign_id => canada_emp_fed_rec.assignment_id,
5063: p_old_juri_code => canada_emp_fed_rec.employment_province,
5064: p_new_juri_code => new_geocode,
5065: p_location => 'PAY_CA_EMP_FED_TAX_INFO_F',
5066: p_id => null);
5067:
5068: hr_utility.set_location('pay_us_geo_upd_pkg.update_ca_emp_info',3);
5069:
5064: p_new_juri_code => new_geocode,
5065: p_location => 'PAY_CA_EMP_FED_TAX_INFO_F',
5066: p_id => null);
5067:
5068: hr_utility.set_location('pay_us_geo_upd_pkg.update_ca_emp_info',3);
5069:
5070: END LOOP ;
5071: CLOSE canada_emp_fed_tax_cur ;
5072:
5074: LOOP
5075: FETCH canada_emp_prov_tax_cur into canada_emp_prov_rec;
5076: EXIT WHEN canada_emp_prov_tax_cur%NOTFOUND;
5077:
5078: hr_utility.set_location('pay_us_geo_upd_pkg.update_ca_emp_info',4);
5079:
5080: SELECT pmod.new_county_code,
5081: pmod.process_type
5082: INTO new_geocode1, l_proc_type
5099:
5100: END IF;
5101:
5102:
5103: hr_utility.set_location('pay_us_geo_upd_pkg.update_ca_emp_info',5);
5104: -- write to the message table so that if this fails unexpectedly
5105: write_message(
5106: p_proc_type => l_proc_type,
5107: p_person_id => null,
5107: p_person_id => null,
5108: p_assign_id => canada_emp_prov_rec.assignment_id,
5109: p_old_juri_code => canada_emp_prov_rec.province_code,
5110: p_new_juri_code => new_geocode1,
5111: p_location => 'PAY_CA_EMP_PROV_TAX_INFO_F',
5112: p_id => null);
5113:
5114: hr_utility.set_location('pay_us_geo_upd_pkg.update_ca_emp_info',6);
5115: END LOOP ;
5110: p_new_juri_code => new_geocode1,
5111: p_location => 'PAY_CA_EMP_PROV_TAX_INFO_F',
5112: p_id => null);
5113:
5114: hr_utility.set_location('pay_us_geo_upd_pkg.update_ca_emp_info',6);
5115: END LOOP ;
5116: CLOSE canada_emp_prov_tax_cur ;
5117:
5118:
5121: LOOP
5122: FETCH canada_leg_info_cur into canada_leg_info_rec ;
5123: EXIT WHEN canada_leg_info_cur%NOTFOUND;
5124:
5125: hr_utility.set_location('pay_us_geo_upd_pkg.update_ca_emp_info',7);
5126: SELECT pmod.new_county_code,
5127: pmod.process_type
5128: INTO new_geocode2, l_proc_type
5129: FROM pay_us_modified_geocodes pmod
5140:
5141:
5142: END IF;
5143:
5144: hr_utility.set_location('pay_us_geo_upd_pkg.update_ca_emp_info',8);
5145: -- write to the message table so that if this fails unexpectedly
5146: write_message(
5147: p_proc_type => l_proc_type,
5148: p_person_id => null,
5148: p_person_id => null,
5149: p_assign_id => null,
5150: p_old_juri_code => canada_leg_info_rec.jurisdiction_code,
5151: p_new_juri_code => new_geocode2,
5152: p_location => 'PAY_CA_LEGISLATION_INFO',
5153: p_id => null);
5154:
5155: hr_utility.set_location('pay_us_geo_upd_pkg.update_ca_emp_info',9);
5156: END LOOP ;
5151: p_new_juri_code => new_geocode2,
5152: p_location => 'PAY_CA_LEGISLATION_INFO',
5153: p_id => null);
5154:
5155: hr_utility.set_location('pay_us_geo_upd_pkg.update_ca_emp_info',9);
5156: END LOOP ;
5157:
5158: CLOSE canada_leg_info_cur;
5159:
5156: END LOOP ;
5157:
5158: CLOSE canada_leg_info_cur;
5159:
5160: hr_utility.set_location('pay_us_geo_upd_pkg.update_ca_emp_info',10);
5161: EXCEPTION
5162: WHEN OTHERS THEN
5163: l_error_message_text := to_char(SQLCODE)||SQLERRM||
5164: ' Program error contact support';
5162: WHEN OTHERS THEN
5163: l_error_message_text := to_char(SQLCODE)||SQLERRM||
5164: ' Program error contact support';
5165: rollback;
5166: hr_utility.set_location('pay_us_geo_upd_pkg.update_ca_emp_info',11);
5167:
5168: fnd_file.put_line(fnd_file.log, 'Exception update_ca_emp_info' );
5169: fnd_file.put_line(fnd_file.log, 'sql error ' || sqlcode || ' - ' || substr(sqlerrm,1,80));
5170:
5167:
5168: fnd_file.put_line(fnd_file.log, 'Exception update_ca_emp_info' );
5169: fnd_file.put_line(fnd_file.log, 'sql error ' || sqlcode || ' - ' || substr(sqlerrm,1,80));
5170:
5171: raise_application_error(-20001,l_error_message_text);
5172:
5173: hr_utility.set_location('before commit ',5);
5174: -- commit;
5175: END update_ca_emp_info ;
5169: fnd_file.put_line(fnd_file.log, 'sql error ' || sqlcode || ' - ' || substr(sqlerrm,1,80));
5170:
5171: raise_application_error(-20001,l_error_message_text);
5172:
5173: hr_utility.set_location('before commit ',5);
5174: -- commit;
5175: END update_ca_emp_info ;
5176: --
5177: --
5373: exception
5374:
5375: when no_data_found then
5376:
5377: hr_utility.set_location('pay_us_geo_upd_pkg. group_level_balance',1);
5378: SELECT pmod.state_code||'-'||pmod.county_code||'-'||pmod.new_city_code,
5379: process_type, pmod.new_city_code
5380: INTO l_geocode, l_proc_type, l_new_city_code
5381: FROM pay_us_modified_geocodes pmod
5397: -- COMMIT;
5398: END IF;
5399:
5400:
5401: hr_utility.set_location('pay_us_geo_upd_pkg. group_level_balance',2);
5402: -- write to the message table so that if this fails unexpectedly
5403: write_message(
5404: p_proc_type => l_proc_type,
5405: p_person_id => group_level_bal_us_rec.run_balance_id,
5405: p_person_id => group_level_bal_us_rec.run_balance_id,
5406: p_assign_id => null,
5407: p_old_juri_code => group_level_bal_us_rec.jurisdiction_code,
5408: p_new_juri_code => l_geocode,
5409: p_location => 'PAY_RUN_BALANCES',
5410: p_id => null);
5411:
5412: hr_utility.set_location('pay_us_geo_upd_pkg. group_level_balance',3);
5413:
5408: p_new_juri_code => l_geocode,
5409: p_location => 'PAY_RUN_BALANCES',
5410: p_id => null);
5411:
5412: hr_utility.set_location('pay_us_geo_upd_pkg. group_level_balance',3);
5413:
5414: end;
5415:
5416: END LOOP ;
5445:
5446: when no_data_found then
5447:
5448: hr_utility.trace('Entering pay_us_geo_upd_pkg. group_level_balance - 7002');
5449: hr_utility.set_location('pay_us_geo_upd_pkg. group_level_balance',4);
5450: SELECT pmod.new_county_code, pmod.process_type
5451: INTO l_geocode, l_proc_type
5452: FROM pay_us_modified_geocodes pmod
5453: WHERE pmod.state_code = 'CA'
5471:
5472: -- COMMIT;
5473: END IF;
5474:
5475: hr_utility.set_location('pay_us_geo_upd_pkg. group_level_balance',5);
5476: -- write to the message table so that if this fails unexpectedly
5477: write_message(
5478: p_proc_type => l_proc_type,
5479: p_person_id => group_level_bal_ca_rec.run_balance_id,
5479: p_person_id => group_level_bal_ca_rec.run_balance_id,
5480: p_assign_id => null,
5481: p_old_juri_code => group_level_bal_ca_rec.jurisdiction_code,
5482: p_new_juri_code => l_geocode,
5483: p_location => 'PAY_RUN_BALANCES',
5484: p_id => null);
5485:
5486: hr_utility.set_location('pay_us_geo_upd_pkg. group_level_balance',6);
5487:
5482: p_new_juri_code => l_geocode,
5483: p_location => 'PAY_RUN_BALANCES',
5484: p_id => null);
5485:
5486: hr_utility.set_location('pay_us_geo_upd_pkg. group_level_balance',6);
5487:
5488: end;
5489:
5490: END LOOP ;
5496: END LOOP;
5497:
5498: CLOSE c_legislation_code;
5499:
5500: hr_utility.set_location('pay_us_geo_upd_pkg. group_level_balance',7);
5501: EXCEPTION
5502: WHEN OTHERS THEN
5503: l_error_message_text := to_char(SQLCODE)||SQLERRM||
5504: ' Program error contact support';
5514: rollback;
5515:
5516:
5517:
5518: hr_utility.set_location('pay_us_geo_upd_pkg. group_level_balance',8);
5519: raise_application_error(-20001,l_error_message_text);
5520:
5521: hr_utility.set_location('before commit ',6);
5522: -- commit;
5515:
5516:
5517:
5518: hr_utility.set_location('pay_us_geo_upd_pkg. group_level_balance',8);
5519: raise_application_error(-20001,l_error_message_text);
5520:
5521: hr_utility.set_location('before commit ',6);
5522: -- commit;
5523: END group_level_balance ;
5517:
5518: hr_utility.set_location('pay_us_geo_upd_pkg. group_level_balance',8);
5519: raise_application_error(-20001,l_error_message_text);
5520:
5521: hr_utility.set_location('before commit ',6);
5522: -- commit;
5523: END group_level_balance ;
5524: --
5525: