[Home] [Help]
1: PACKAGE BODY pay_us_geo_upd_pkg as
2: /* $Header: pyusgeou.pkb 120.14.12010000.3 2008/08/14 11:14:12 pannapur ship $ */
3:
4: --Bug 2996546 declare pl/sql table variables in order to load
5: --input_value_id from pay_input_values_f
61:
62: hr_utility.trace('reached range_cursor');
63:
64: select ppa.legislative_parameters,
65: pay_us_geo_upd_pkg.get_parameter('PATCH_NAME',PPa.legislative_parameters)
66: into leg_param,
67: ln_upgrade_patch
68: from pay_payroll_actions ppa
69: where ppa.payroll_action_id = pactid;
107:
108:
109: cursor c_parameters ( pactid number) is
110: select ppa.legislative_parameters,
111: pay_us_geo_upd_pkg.get_parameter('PATCH_NAME',PPa.legislative_parameters),
112: pay_us_geo_upd_pkg.get_parameter('MODE',PPa.legislative_parameters)
113: from pay_payroll_actions ppa
114: where ppa.payroll_action_id = pactid;
115:
108:
109: cursor c_parameters ( pactid number) is
110: select ppa.legislative_parameters,
111: pay_us_geo_upd_pkg.get_parameter('PATCH_NAME',PPa.legislative_parameters),
112: pay_us_geo_upd_pkg.get_parameter('MODE',PPa.legislative_parameters)
113: from pay_payroll_actions ppa
114: where ppa.payroll_action_id = pactid;
115:
116:
328: end loop; -- loop 1
329: close c_actions_run_bal;
330:
331:
332: pay_us_geo_upd_pkg.update_taxability_rules(l_geo_phase_id,l_mode,l_patch_name); --l_patch_name);
333:
334: pay_us_geo_upd_pkg.update_org_info(l_geo_phase_id,l_mode,l_patch_name); --l_patch_name);
335:
336: pay_us_geo_upd_pkg.update_ca_emp_info(l_geo_phase_id,l_mode,l_patch_name);
330:
331:
332: pay_us_geo_upd_pkg.update_taxability_rules(l_geo_phase_id,l_mode,l_patch_name); --l_patch_name);
333:
334: pay_us_geo_upd_pkg.update_org_info(l_geo_phase_id,l_mode,l_patch_name); --l_patch_name);
335:
336: pay_us_geo_upd_pkg.update_ca_emp_info(l_geo_phase_id,l_mode,l_patch_name);
337:
338: END IF;
332: pay_us_geo_upd_pkg.update_taxability_rules(l_geo_phase_id,l_mode,l_patch_name); --l_patch_name);
333:
334: pay_us_geo_upd_pkg.update_org_info(l_geo_phase_id,l_mode,l_patch_name); --l_patch_name);
335:
336: pay_us_geo_upd_pkg.update_ca_emp_info(l_geo_phase_id,l_mode,l_patch_name);
337:
338: END IF;
339:
340:
385: where ptoa.object_action_id = cp_assignment_action;
386:
387: cursor c_parameters ( pactid number) is
388: select ppa.legislative_parameters,
389: pay_us_geo_upd_pkg.get_parameter('PATCH_NAME',PPa.legislative_parameters),
390: pay_us_geo_upd_pkg.get_parameter('MODE',PPa.legislative_parameters)
391: from pay_payroll_actions ppa
392: where ppa.payroll_action_id = pactid;
393:
386:
387: cursor c_parameters ( pactid number) is
388: select ppa.legislative_parameters,
389: pay_us_geo_upd_pkg.get_parameter('PATCH_NAME',PPa.legislative_parameters),
390: pay_us_geo_upd_pkg.get_parameter('MODE',PPa.legislative_parameters)
391: from pay_payroll_actions ppa
392: where ppa.payroll_action_id = pactid;
393:
394: cursor c_get_phase_id (p_patch_name varchar2)
433:
434:
435: if l_object_type = 'ASG' THEN
436:
437: pay_us_geo_upd_pkg.upgrade_geocodes (p_assign_start => l_object_id,
438: p_assign_end => l_object_id,
439: p_geo_phase_id => l_geo_phase_id,
440: p_mode => l_mode,
441: p_patch_name => l_patch_name);
442:
443:
444: elsif l_object_type = 'PER' Then
445:
446: pay_us_geo_upd_pkg.group_level_balance (P_START_PAYROLL_ACTION => l_object_id,
447: P_END_PAYROLL_ACTION => l_object_id,
448: P_GEO_PHASE_ID => l_geo_phase_id,
449: P_MODE => l_mode,
450: P_PATCH_NAME => l_patch_name);
482: and rownum < 2);
483:
484: cursor c_parameters ( pactid number) is
485: select ppa.legislative_parameters,
486: pay_us_geo_upd_pkg.get_parameter('PATCH_NAME',PPa.legislative_parameters),
487: pay_us_geo_upd_pkg.get_parameter('MODE',PPa.legislative_parameters)
488: from pay_payroll_actions ppa
489: where ppa.payroll_action_id = pactid;
490:
483:
484: cursor c_parameters ( pactid number) is
485: select ppa.legislative_parameters,
486: pay_us_geo_upd_pkg.get_parameter('PATCH_NAME',PPa.legislative_parameters),
487: pay_us_geo_upd_pkg.get_parameter('MODE',PPa.legislative_parameters)
488: from pay_payroll_actions ppa
489: where ppa.payroll_action_id = pactid;
490:
491:
622: IS
623:
624: BEGIN
625:
626: hr_utility.trace('Entering pay_us_geo_upd_pkg.write message');
627:
628: IF G_MODE = 'UPGRADE' THEN
629: insert into PAY_US_GEO_UPDATE (ID,
630: ASSIGNMENT_ID,
674: p_status);
675:
676:
677: END IF;
678: hr_utility.trace('Exiting pay_us_geo_upd_pkg.write message');
679:
680: END write_message;
681:
682:
1173:
1174:
1175: BEGIN
1176:
1177: hr_utility.trace('Entering pay_us_geo_upd_pkg.balance_batch_lines');
1178:
1179: IF ((p_proc_type <> 'SU' and p_proc_type <> 'US') and p_api_mode = 'N') THEN
1180:
1181: hr_utility.trace('Before update of balance_batch_lines for assignment id: '||to_char(p_assign_id));
1268:
1269:
1270: BEGIN
1271:
1272: hr_utility.trace('Entering pay_us_geo_upd_pkg.pay_run_balances');
1273:
1274: IF ((p_proc_type <> 'SU' and p_proc_type <> 'US') and p_api_mode = 'N') THEN
1275:
1276: hr_utility.trace('Before update of pay_run_balances for assignment id: '||to_char(p_assign_id));
1344: IS
1345:
1346: BEGIN
1347:
1348: hr_utility.trace('Entering pay_us_geo_upd_pkg.city_tax_records');
1349:
1350: IF ((p_proc_type <> 'SU' and p_proc_type <> 'US') and p_api_mode = 'N') THEN
1351:
1352: hr_utility.trace('Before update of city tax records for assignment id: '||to_char(p_assign_id));
1438: */
1439:
1440: BEGIN
1441:
1442: hr_utility.trace('Entering pay_us_geo_upd_pkg.del_dup_city_tax_recs');
1443:
1444: IF G_MODE = 'UPGRADE' THEN
1445:
1446: FOR dup_rec IN dup_city_tax_rows
1533: l_flag varchar2(2);
1534:
1535: BEGIN
1536:
1537: hr_utility.trace('Entering pay_us_geo_upd_pkg.run_results');
1538:
1539: IF ((p_proc_type <> 'SU' and p_proc_type <> 'US') and p_api_mode = 'N') THEN
1540:
1541: hr_utility.trace('Before update of run result values for assignment_action_id: '||to_char(p_assign_act_id));
1677:
1678:
1679: hr_utility.trace('After update of run results for assignment_action_id: '||to_char(p_assign_act_id));
1680:
1681: hr_utility.trace('Exiting pay_us_geo_upd_pkg.run_results');
1682:
1683: END run_results;
1684: --
1685: --
1710: l_pac_inside_exist varchar2(2);
1711:
1712: BEGIN
1713:
1714: hr_utility.trace('Entering pay_us_geo_upd_pkg.pay_action_contexts');
1715:
1716: IF ((p_proc_type <> 'SU' and p_proc_type <> 'US') and p_api_mode = 'N') THEN
1717:
1718: hr_utility.trace('Before update of pay_action_contexts for assignment id: '||to_char(p_assign_id));
1790: IS
1791:
1792: BEGIN
1793:
1794: hr_utility.trace('Entering pay_us_geo_upd_pkg.archive_item_contexts');
1795:
1796: IF ((p_proc_type <> 'SU' and p_proc_type <> 'US') and p_api_mode = 'N') THEN
1797:
1798: hr_utility.trace('Before update of archive item contexts for assignment_id: '||to_char(p_assign_id));
1835: p_id => p_archive_item_id);
1836:
1837: END IF;
1838:
1839: hr_utility.trace('Exiting pay_us_geo_upd_pkg.archive_item_contexts');
1840:
1841:
1842:
1843: END archive_item_contexts;
1857:
1858: IS
1859:
1860: BEGIN
1861: hr_utility.trace('Entering pay_us_geo_upd_pkg.element_entries');
1862:
1863: IF ((p_proc_type <> 'SU' and p_proc_type <> 'US') and p_api_mode = 'N') THEN
1864:
1865: hr_utility.trace('Before update of element entry values for assignment_id: '||to_char(p_assign_id));
1900: p_id => p_ele_ent_id);
1901:
1902: END IF;
1903:
1904: hr_utility.trace('Exiting pay_us_geo_upd_pkg.element_entries');
1905:
1906:
1907:
1908: END element_entries;
1923: IS
1924:
1925: BEGIN
1926:
1927: hr_utility.trace('Entering pay_us_geo_upd_pkg.balance_contexts');
1928:
1929: IF ((p_proc_type <> 'SU' and p_proc_type <> 'US') and p_api_mode = 'N') THEN
1930:
1931: hr_utility.trace('Before update of latest balances context for assignment_action_id: '||to_char(p_assign_act_id));
1967:
1968: hr_utility.trace('After update of latest balances context for assignment_action_id: '||to_char(p_assign_act_id));
1969:
1970:
1971: hr_utility.trace('Exiting pay_us_geo_upd_pkg.balance_contexts');
1972:
1973: END balance_contexts;
1974:
1975:
2029: l_effective_end_date pay_element_entry_values_f.effective_end_date%TYPE;
2030:
2031: BEGIN
2032:
2033: hr_utility.trace('Entering pay_us_geo_upd_pkg.duplicate_vertex_ee');
2034:
2035: l_prev_screen := null;
2036: l_prev_eleid := null;
2037:
2036: l_prev_eleid := null;
2037:
2038: for j in csr_get_dup(p_assignment_id) loop
2039:
2040: hr_utility.set_location('pay_us_geo_upd_pkg.duplicate_vertex_ee', 1);
2041:
2042: if j.sev = l_prev_screen and j.eei <> l_prev_eleid then
2043: hr_utility.trace('Element Entry Id : '|| to_char(j.eei)
2044: ||' is a duplicate of : ' || to_char(l_prev_eleid)
2043: hr_utility.trace('Element Entry Id : '|| to_char(j.eei)
2044: ||' is a duplicate of : ' || to_char(l_prev_eleid)
2045: ||' for assignment_id : ' || to_char(p_assignment_id));
2046:
2047: hr_utility.set_location('pay_us_geo_upd_pkg.duplicate_vertex_ee', 2);
2048:
2049: /* get the percentages for the record to be deleted */
2050: open csr_get_percentage(j.eei);
2051: loop
2049: /* get the percentages for the record to be deleted */
2050: open csr_get_percentage(j.eei);
2051: loop
2052:
2053: hr_utility.set_location('pay_us_geo_upd_pkg.duplicate_vertex_ee', 3);
2054:
2055: /* Get the %age for each datetracked record */
2056:
2057: fetch csr_get_percentage into l_percent,
2058: l_effective_start_date,
2059: l_effective_end_date;
2060: exit when csr_get_percentage%NOTFOUND;
2061:
2062: hr_utility.set_location('pay_us_geo_upd_pkg.duplicate_vertex_ee', 4);
2063:
2064: /* Add the %age of the current element entry to the earlier
2065: entry */
2066:
2090: and piv.legislation_code = 'US'
2091: and pet.legislation_code = 'US');
2092:
2093: END IF;
2094: hr_utility.set_location('pay_us_geo_upd_pkg.duplicate_vertex_ee', 5);
2095:
2096: end loop;
2097: close csr_get_percentage;
2098:
2095:
2096: end loop;
2097: close csr_get_percentage;
2098:
2099: hr_utility.set_location('pay_us_geo_upd_pkg.duplicate_vertex_ee', 6);
2100:
2101: /* Now delete the current entry */
2102:
2103: delete pay_element_entries_f
2103: delete pay_element_entries_f
2104: where element_entry_id = j.eei
2105: and assignment_id = p_assignment_id;
2106:
2107: hr_utility.set_location('pay_us_geo_upd_pkg.duplicate_vertex_ee', 7);
2108:
2109: else
2110:
2111: hr_utility.set_location('pay_us_geo_upd_pkg.duplicate_vertex_ee', 8);
2107: hr_utility.set_location('pay_us_geo_upd_pkg.duplicate_vertex_ee', 7);
2108:
2109: else
2110:
2111: hr_utility.set_location('pay_us_geo_upd_pkg.duplicate_vertex_ee', 8);
2112:
2113: l_prev_screen := j.sev;
2114: l_prev_eleid := j.eei;
2115:
2112:
2113: l_prev_screen := j.sev;
2114: l_prev_eleid := j.eei;
2115:
2116: hr_utility.set_location('pay_us_geo_upd_pkg.duplicate_vertex_ee', 9);
2117:
2118: end if;
2119:
2120: hr_utility.set_location('pay_us_geo_upd_pkg.duplicate_vertex_ee', 10);
2116: hr_utility.set_location('pay_us_geo_upd_pkg.duplicate_vertex_ee', 9);
2117:
2118: end if;
2119:
2120: hr_utility.set_location('pay_us_geo_upd_pkg.duplicate_vertex_ee', 10);
2121:
2122: end loop;
2123:
2124: hr_utility.set_location('pay_us_geo_upd_pkg.duplicate_vertex_ee', 11);
2120: hr_utility.set_location('pay_us_geo_upd_pkg.duplicate_vertex_ee', 10);
2121:
2122: end loop;
2123:
2124: hr_utility.set_location('pay_us_geo_upd_pkg.duplicate_vertex_ee', 11);
2125:
2126: hr_utility.trace('Exiting pay_us_geo_upd_pkg.duplicate_vertex_ee');
2127:
2128: end duplicate_vertex_ee;
2122: end loop;
2123:
2124: hr_utility.set_location('pay_us_geo_upd_pkg.duplicate_vertex_ee', 11);
2125:
2126: hr_utility.trace('Exiting pay_us_geo_upd_pkg.duplicate_vertex_ee');
2127:
2128: end duplicate_vertex_ee;
2129:
2130:
2186: lc_insert_rec varchar2(1);
2187: l_profile_value varchar2(1):='N';
2188: BEGIN
2189:
2190: hr_utility.trace('Entering pay_us_geo_upd_pkg.insert_ele_entries');
2191:
2192: lc_insert_rec := 'N';
2193:
2194: IF ((p_proc_type = 'SU' or p_proc_type = 'US') and p_api_mode = 'N') THEN
2197: ln_county_code := substr(p_new_juri_code,4,3);
2198: ln_city_code := substr(p_new_juri_code,8);
2199: ln_old_city_code := substr(p_old_juri_code,8);
2200:
2201: hr_utility.set_location('pay_us_geo_upd_pkg.insert_ele_entries',1);
2202:
2203: open c_county_rec(p_assign_id,
2204: ln_state_code,
2205: ln_county_code);
2203: open c_county_rec(p_assign_id,
2204: ln_state_code,
2205: ln_county_code);
2206:
2207: hr_utility.set_location('pay_us_geo_upd_pkg.insert_ele_entries',2);
2208:
2209: fetch c_county_rec into lc_exists;
2210: if c_county_rec%notfound then
2211:
2208:
2209: fetch c_county_rec into lc_exists;
2210: if c_county_rec%notfound then
2211:
2212: hr_utility.set_location('pay_us_geo_upd_pkg.insert_ele_entries',3);
2213:
2214: -- Call write message to store information that their is no county record for this assignment
2215: write_message(
2216: p_proc_type => 'MISSING_COUNTY_RECORDS',
2220: p_new_juri_code => p_new_juri_code,
2221: p_location => 'PAY_US_EMP_COUNTY_TAX_RULES_F',
2222: p_id => null);
2223:
2224: hr_utility.set_location('pay_us_geo_upd_pkg.insert_ele_entries',4);
2225:
2226:
2227: ELSE
2228:
2225:
2226:
2227: ELSE
2228:
2229: hr_utility.set_location('pay_us_geo_upd_pkg.insert_ele_entries',5);
2230:
2231: open c_tax_rec(p_assign_id,
2232: ln_state_code,
2233: ln_county_code,
2234: ln_old_city_code);
2235:
2236: fetch c_tax_rec into ln_check;
2237:
2238: hr_utility.set_location('pay_us_geo_upd_pkg.insert_ele_entries',6);
2239:
2240: if c_tax_rec%found then -- changed notfound to found
2241: close c_tax_rec;
2242: open c_tax_rec(p_assign_id,
2248: lc_insert_rec := 'Y';
2249: end if;
2250: end if;
2251:
2252: hr_utility.set_location('pay_us_geo_upd_pkg.insert_ele_entries',7);
2253:
2254: close c_tax_rec;
2255:
2256: if lc_insert_rec = 'Y' then
2254: close c_tax_rec;
2255:
2256: if lc_insert_rec = 'Y' then
2257:
2258: hr_utility.set_location('pay_us_geo_upd_pkg.insert_ele_entries',8);
2259:
2260: open c_elig_date(p_assign_id);
2261:
2262: fetch c_elig_date into ld_eff_start_date, ld_eff_end_date, ln_business_group_id;
2261:
2262: fetch c_elig_date into ld_eff_start_date, ld_eff_end_date, ln_business_group_id;
2263:
2264: if c_elig_date%notfound then
2265: hr_utility.set_location('pay_us_geo_upd_pkg.insert_ele_entries',9);
2266: --Exiting if there are no city Tax Records.
2267: end if;
2268:
2269: hr_utility.set_location('pay_us_geo_upd_pkg.insert_ele_entries',10);
2265: hr_utility.set_location('pay_us_geo_upd_pkg.insert_ele_entries',9);
2266: --Exiting if there are no city Tax Records.
2267: end if;
2268:
2269: hr_utility.set_location('pay_us_geo_upd_pkg.insert_ele_entries',10);
2270: close c_elig_date;
2271:
2272: hr_utility.set_location('pay_us_geo_upd_pkg.insert_ele_entries',11);
2273:
2268:
2269: hr_utility.set_location('pay_us_geo_upd_pkg.insert_ele_entries',10);
2270: close c_elig_date;
2271:
2272: hr_utility.set_location('pay_us_geo_upd_pkg.insert_ele_entries',11);
2273:
2274: hr_utility.trace('The business group id is: '||to_char(ln_business_group_id));
2275:
2276: IF G_MODE = 'UPGRADE' THEN
2297: fnd_profile.put('HR_CROSS_BUSINESS_GROUP','N');
2298: hr_utility.trace('modifed the profile to'||to_char(fnd_profile.value('HR_CROSS_BUSINESS_GROUP')));
2299: end if;
2300:
2301: hr_utility.set_location('pay_us_geo_upd_pkg.insert_ele_entries',12);
2302:
2303: -- Write to the table with the new city information
2304:
2305: write_message(
2323: p_id => null);
2324:
2325: END IF;
2326:
2327: hr_utility.set_location('pay_us_geo_upd_pkg.insert_ele_entries',12);
2328:
2329: /*
2330: pay_us_emp_dt_tax_rules.maintain_element_entry
2331: (p_assignment_id => p_assign_id,
2347: p_new_juri_code => p_new_juri_code,
2348: p_location => 'PAY_ELEMENT_ENTRIES_F',
2349: p_id => null);
2350:
2351: hr_utility.set_location('pay_us_geo_upd_pkg.insert_ele_entries',13);
2352:
2353: /* END IF; County 6864396*/
2354: END IF;
2355:
2354: END IF;
2355:
2356: end if;
2357:
2358: hr_utility.set_location('pay_us_geo_upd_pkg.insert_ele_entries',14);
2359:
2360: ELSE -- p_proc_type != 'SU' and p_proc_type != 'US'
2361:
2362: write_message(
2447: percentage NUMBER;
2448:
2449: BEGIN
2450:
2451: hr_utility.trace('Entering pay_us_geo_upd_pkg.check_time');
2452:
2453: tot_percentage := 0;
2454:
2455: hr_utility.set_location('pay_us_geo_upd_pkg.check_time',1);
2451: hr_utility.trace('Entering pay_us_geo_upd_pkg.check_time');
2452:
2453: tot_percentage := 0;
2454:
2455: hr_utility.set_location('pay_us_geo_upd_pkg.check_time',1);
2456:
2457: -- Get each state for the assignment.
2458: FOR state_rec IN state_cur(p_assign_id) LOOP
2459:
2456:
2457: -- Get each state for the assignment.
2458: FOR state_rec IN state_cur(p_assign_id) LOOP
2459:
2460: hr_utility.set_location('pay_us_geo_upd_pkg.check_time',2);
2461:
2462: -- Get the percentage of time worked in that state.
2463:
2464: OPEN sum_cur(p_assign_id, state_rec.effective_start_date,
2463:
2464: OPEN sum_cur(p_assign_id, state_rec.effective_start_date,
2465: state_rec.effective_end_date, state_rec.state_code);
2466: FETCH sum_cur INTO percentage;
2467: hr_utility.set_location('pay_us_geo_upd_pkg.check_time',3);
2468:
2469: IF sum_cur%FOUND THEN
2470: hr_utility.set_location('pay_us_geo_upd_pkg.check_time',4);
2471:
2466: FETCH sum_cur INTO percentage;
2467: hr_utility.set_location('pay_us_geo_upd_pkg.check_time',3);
2468:
2469: IF sum_cur%FOUND THEN
2470: hr_utility.set_location('pay_us_geo_upd_pkg.check_time',4);
2471:
2472: tot_percentage := tot_percentage + percentage;
2473: hr_utility.set_location('pay_us_geo_upd_pkg.check_time',5);
2474:
2469: IF sum_cur%FOUND THEN
2470: hr_utility.set_location('pay_us_geo_upd_pkg.check_time',4);
2471:
2472: tot_percentage := tot_percentage + percentage;
2473: hr_utility.set_location('pay_us_geo_upd_pkg.check_time',5);
2474:
2475: END IF;
2476: hr_utility.set_location('pay_us_geo_upd_pkg.check_time',6);
2477:
2472: tot_percentage := tot_percentage + percentage;
2473: hr_utility.set_location('pay_us_geo_upd_pkg.check_time',5);
2474:
2475: END IF;
2476: hr_utility.set_location('pay_us_geo_upd_pkg.check_time',6);
2477:
2478: CLOSE sum_cur;
2479: hr_utility.set_location('pay_us_geo_upd_pkg.check_time',7);
2480:
2475: END IF;
2476: hr_utility.set_location('pay_us_geo_upd_pkg.check_time',6);
2477:
2478: CLOSE sum_cur;
2479: hr_utility.set_location('pay_us_geo_upd_pkg.check_time',7);
2480:
2481: END LOOP; -- state_cur
2482: hr_utility.set_location('pay_us_geo_upd_pkg.check_time',8);
2483:
2478: CLOSE sum_cur;
2479: hr_utility.set_location('pay_us_geo_upd_pkg.check_time',7);
2480:
2481: END LOOP; -- state_cur
2482: hr_utility.set_location('pay_us_geo_upd_pkg.check_time',8);
2483:
2484: IF (tot_percentage > 100) THEN
2485:
2486: hr_utility.set_location('pay_us_geo_upd_pkg.check_time',9);
2482: hr_utility.set_location('pay_us_geo_upd_pkg.check_time',8);
2483:
2484: IF (tot_percentage > 100) THEN
2485:
2486: hr_utility.set_location('pay_us_geo_upd_pkg.check_time',9);
2487:
2488: SELECT ppf.person_id
2489: INTO l_person_id
2490: FROM per_all_people_f ppf,
2505: p_location => 'PAY_ELEMENT_ENTRY_VALUES_F',
2506: p_id => null);
2507:
2508:
2509: hr_utility.set_location('pay_us_geo_upd_pkg.check_time',10);
2510:
2511: END IF;
2512: hr_utility.set_location('pay_us_geo_upd_pkg.check_time',11);
2513:
2508:
2509: hr_utility.set_location('pay_us_geo_upd_pkg.check_time',10);
2510:
2511: END IF;
2512: hr_utility.set_location('pay_us_geo_upd_pkg.check_time',11);
2513:
2514: -- Taking out the exception here because if the procedure errors let it go to the calling block and
2515: -- use that exception handler as that errors to the savepoint and continues with the assignment.
2516:
2536: g_process_type := null;
2537:
2538: -- hr_utility.trace_on(null,'oracle');
2539:
2540: hr_utility.trace('Entering pay_us_geo_upd_pkg.upgrade_geocodes');
2541:
2542: -- Set the global phase id for the geo update
2543:
2544: g_geo_phase_id := p_geo_phase_id;
2559: INTO table_exist
2560: FROM cat
2561: WHERE table_name = tab_name;
2562:
2563: hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geocodes',1);
2564:
2565: --Bug 2996546 call procedure load_input_values
2566: load_input_values;
2567: --hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geocodes',230);
2563: hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geocodes',1);
2564:
2565: --Bug 2996546 call procedure load_input_values
2566: load_input_values;
2567: --hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geocodes',230);
2568:
2569:
2570: OPEN main_driving_cur(P_ASSIGN_START, P_ASSIGN_END, P_CITY_NAME, P_API_MODE);
2571:
2568:
2569:
2570: OPEN main_driving_cur(P_ASSIGN_START, P_ASSIGN_END, P_CITY_NAME, P_API_MODE);
2571:
2572: hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geocodes',5);
2573:
2574: LOOP
2575:
2576: BEGIN
2576: BEGIN
2577:
2578: FETCH main_driving_cur into main_old_juri_code, main_assign_id, main_new_juri_code, main_person_id,
2579: main_new_city_code, main_proc_type, main_city_tax_rule_id;
2580: hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geocodes',10);
2581:
2582: EXIT when main_driving_cur%NOTFOUND;
2583:
2584:
2581:
2582: EXIT when main_driving_cur%NOTFOUND;
2583:
2584:
2585: hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geocodes',15);
2586:
2587:
2588: hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geocodes',20);
2589:
2584:
2585: hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geocodes',15);
2586:
2587:
2588: hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geocodes',20);
2589:
2590: hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geocodes',25);
2591:
2592: -- Set the global variable for g_process_type
2586:
2587:
2588: hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geocodes',20);
2589:
2590: hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geocodes',25);
2591:
2592: -- Set the global variable for g_process_type
2593:
2594: g_process_type := main_proc_type;
2610: l_proc_stage := 'START';
2611:
2612: OPEN chk_assign_error_cur(main_assign_id, main_new_juri_code, main_old_juri_code);
2613:
2614: hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geocodes',30);
2615:
2616: FETCH chk_assign_error_cur INTO l_chk_assign_error;
2617: IF (chk_assign_error_cur%FOUND or p_api_mode = 'Y') THEN
2618: hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geocodes',35);
2614: hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geocodes',30);
2615:
2616: FETCH chk_assign_error_cur INTO l_chk_assign_error;
2617: IF (chk_assign_error_cur%FOUND or p_api_mode = 'Y') THEN
2618: hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geocodes',35);
2619:
2620: NULL; /* We do nothing here because we want the assignment to re-processed
2621: but do not create another row in the pay_us_geo_update table */
2622:
2620: NULL; /* We do nothing here because we want the assignment to re-processed
2621: but do not create another row in the pay_us_geo_update table */
2622:
2623: ELSE
2624: hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geocodes',40);
2625:
2626: -- We need to store a process type because the same geocode can have two records for different city names
2627: -- Thus we would get two rows in PAY_US_GEO_UPDATE for the same assignment id.
2628:
2640:
2641: END IF;
2642:
2643: CLOSE chk_assign_error_cur;
2644: hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geocodes',45);
2645:
2646: -- Create element entries and a new city record for new jusrisdictions for the assignment. We do this first
2647: -- because we want to commit based on an assignment.
2648:
2680:
2681:
2682: --Update element entry values
2683:
2684: hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geocodes',50);
2685:
2686: OPEN pev_cur(main_old_juri_code, main_assign_id);
2687: LOOP
2688: FETCH pev_cur INTO pev_rec;
2687: LOOP
2688: FETCH pev_cur INTO pev_rec;
2689: EXIT WHEN pev_cur%NOTFOUND;
2690:
2691: hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geocodes',55);
2692:
2693: l_proc_stage := 'ELEMENT_ENTRIES';
2694:
2695: element_entries(
2700: p_ele_ent_id => pev_rec.element_entry_id,
2701: p_old_juri_code => main_old_juri_code,
2702: p_new_juri_code => main_new_juri_code);
2703:
2704: hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geocodes',60);
2705:
2706:
2707: END LOOP;
2708: CLOSE pev_cur;
2706:
2707: END LOOP;
2708: CLOSE pev_cur;
2709:
2710: hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geocodes',65);
2711:
2712:
2713: -- Conditionally Update run results and run result values
2714: --Per bug 2996546 included another condition
2747: LOOP
2748: FETCH prr_cur INTO prr_rec;
2749: EXIT WHEN prr_cur%NOTFOUND;
2750:
2751: hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geocodes',70);
2752:
2753: l_proc_stage := 'RUN_RESULTS';
2754:
2755: run_results(
2761: p_old_juri_code => main_old_juri_code,
2762: p_new_juri_code => main_new_juri_code);
2763:
2764:
2765: hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geocodes',75);
2766: END LOOP;
2767: CLOSE prr_cur;
2768: END LOOP;
2769: CLOSE paa_cur;
2783: FETCH pac_cur INTO pac_rec;
2784: EXIT WHEN pac_cur%NOTFOUND;
2785:
2786:
2787: hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geocodes',240);
2788:
2789: l_proc_stage := 'PAY_ACTION_CONTEXTS';
2790:
2791: pay_action_contexts(
2797: p_old_juri_code => main_old_juri_code,
2798: p_new_juri_code => main_new_juri_code);
2799:
2800:
2801: hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geocodes',250);
2802:
2803: END LOOP;
2804: CLOSE pac_cur;
2805:
2810:
2811:
2812: OPEN fac_cur(main_assign_id, main_old_juri_code);
2813: LOOP
2814: hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geocodes',80);
2815:
2816: FETCH fac_cur INTO fac_rec;
2817: EXIT WHEN fac_cur%NOTFOUND;
2818:
2815:
2816: FETCH fac_cur INTO fac_rec;
2817: EXIT WHEN fac_cur%NOTFOUND;
2818:
2819: hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geocodes',85);
2820:
2821: l_proc_stage := 'ARCHIVE_ITEM_CONTEXTS';
2822:
2823: archive_item_contexts(
2829: P_OLD_JURi_code => main_old_juri_code,
2830: p_new_juri_code => main_new_juri_code);
2831:
2832:
2833: hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geocodes',90);
2834:
2835: END LOOP;
2836: CLOSE fac_cur;
2837:
2835: END LOOP;
2836: CLOSE fac_cur;
2837:
2838:
2839: hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geocodes',95);
2840:
2841:
2842: -- Update person balance context values.
2843:
2846: LOOP
2847: FETCH pbcv_cur INTO pbcv_rec;
2848: EXIT WHEN pbcv_cur%NOTFOUND;
2849:
2850: hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geocodes',100);
2851:
2852: l_proc_stage := 'PERSON_BALANCE_CONTEXTS';
2853:
2854: balance_contexts(
2860: p_lat_bal_id => pbcv_rec.latest_balance_id,
2861: p_old_juri_code => main_old_juri_code,
2862: p_new_juri_code => main_new_juri_code);
2863:
2864: hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geocodes',105);
2865:
2866:
2867: END LOOP;
2868: CLOSE pbcv_cur;
2869:
2870: -- Update assignment balance context values.
2871:
2872: OPEN pacv_cur(main_old_juri_code, main_assign_id, main_person_id);
2873: hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geocodes',110);
2874:
2875: LOOP
2876: FETCH pacv_cur INTO pacv_rec;
2877: EXIT WHEN pacv_cur%NOTFOUND;
2875: LOOP
2876: FETCH pacv_cur INTO pacv_rec;
2877: EXIT WHEN pacv_cur%NOTFOUND;
2878:
2879: hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geocodes',115);
2880:
2881: l_proc_stage := 'ASSIGNMENT_BALANCE_CONTEXTS';
2882:
2883: balance_contexts(
2890: p_old_juri_code => main_old_juri_code,
2891: p_new_juri_code => main_new_juri_code);
2892:
2893:
2894: hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geocodes',120);
2895:
2896:
2897: END LOOP;
2898: CLOSE pacv_cur;
2896:
2897: END LOOP;
2898: CLOSE pacv_cur;
2899:
2900: hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geocodes',125);
2901:
2902: -- Rosie Monge 10/17/2005
2903: -- Update Pay_Latest_balances context values.
2904:
2902: -- Rosie Monge 10/17/2005
2903: -- Update Pay_Latest_balances context values.
2904:
2905: OPEN plbcv_cur(main_old_juri_code, main_assign_id, main_person_id);
2906: hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geocodes',126 );
2907: LOOP
2908: FETCH plbcv_cur INTO plbcv_rec;
2909:
2910: EXIT WHEN plbcv_cur%NOTFOUND;
2908: FETCH plbcv_cur INTO plbcv_rec;
2909:
2910: EXIT WHEN plbcv_cur%NOTFOUND;
2911:
2912: hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geocodes',127);
2913:
2914: l_proc_stage := 'PAY_LATEST_BALANCES_CONTEXT';
2915:
2916: balance_contexts(
2923: p_old_juri_code => main_old_juri_code,
2924: p_new_juri_code => main_new_juri_code);
2925:
2926:
2927: hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geocodes',128);
2928:
2929: END LOOP;
2930: CLOSE plbcv_cur;
2931: -- End Rosie Monge addition for bug 4602222
2940: p_assign_id => main_assign_id,
2941: p_old_juri_code => main_old_juri_code,
2942: p_new_juri_code => main_new_juri_code);
2943:
2944: hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geocodes',130);
2945:
2946: ---
2947: ---
2948: ---
2958: p_new_city_code => main_new_city_code,
2959: p_old_juri_code => main_old_juri_code,
2960: p_new_juri_code => main_new_juri_code);
2961:
2962: hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geocodes',131);
2963: ---
2964: ---
2965: ---
2966:
2968: -- Check for and delete any duplicate Vertex element entries
2969: -- This can be caused by two geocodes combining.
2970: -- We will then add the percentages togethor before deleting.
2971:
2972: hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geocodes',135);
2973:
2974: l_proc_stage := 'DUPLICATE_VERTEX_EE';
2975:
2976: duplicate_vertex_ee(main_assign_id);
2974: l_proc_stage := 'DUPLICATE_VERTEX_EE';
2975:
2976: duplicate_vertex_ee(main_assign_id);
2977:
2978: hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geocodes',140);
2979:
2980:
2981: --Update the pay_us_emp_city_tax_rules_f table.
2982:
2980:
2981: --Update the pay_us_emp_city_tax_rules_f table.
2982:
2983: OPEN city_rec_cur(main_new_juri_code, main_old_juri_code, main_assign_id, main_city_tax_rule_id);
2984: hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geocodes',145);
2985:
2986: FETCH city_rec_cur INTO l_city_tax_exists;
2987: CLOSE city_rec_cur;
2988:
2997: p_new_juri_code => main_new_juri_code,
2998: p_new_city_code => main_new_city_code,
2999: p_city_tax_record_id => main_city_tax_rule_id);
3000:
3001: hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geocodes',150);
3002:
3003: END IF;
3004:
3005:
3004:
3005:
3006:
3007:
3008: hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geocodes',155);
3009:
3010:
3011: -- Now we check for assignments with more than 100% time in jurisdiction
3012:
3013: l_proc_stage := 'CHECK_TIME';
3014:
3015: check_time(p_assign_id => main_assign_id);
3016:
3017: hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geocodes',160);
3018:
3019: -- Now we update the SU and US cases to a status of 'A' for assignments that need to be updated
3020: -- via the API. If the cursor is found then we will update the status to 'A', only if the assignment
3021: -- was not updated because the same jurisdiction also had another type.
3022:
3023: l_proc_stage := 'SET API';
3024:
3025: OPEN chk_assign_api_cur(main_assign_id, main_new_juri_code, main_old_juri_code);
3026: hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geocodes',165);
3027:
3028: FETCH chk_assign_api_cur into l_chk_assign_api;
3029: hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geocodes',170);
3030:
3025: OPEN chk_assign_api_cur(main_assign_id, main_new_juri_code, main_old_juri_code);
3026: hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geocodes',165);
3027:
3028: FETCH chk_assign_api_cur into l_chk_assign_api;
3029: hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geocodes',170);
3030:
3031: CLOSE chk_assign_api_cur;
3032:
3033: IF (l_chk_assign_api = 'Y' and p_api_mode = 'N') THEN
3031: CLOSE chk_assign_api_cur;
3032:
3033: IF (l_chk_assign_api = 'Y' and p_api_mode = 'N') THEN
3034:
3035: hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geocodes',175);
3036:
3037: UPDATE PAY_US_GEO_UPDATE
3038: SET status = 'A', description = null
3039: WHERE assignment_id = main_assign_id
3046:
3047: ELSE
3048: -- Now we update the assignment that has just processed to a status of 'C' in PAY_US_GEO_UPDATE
3049:
3050: hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geocodes',180);
3051:
3052: l_proc_stage := 'END';
3053:
3054: UPDATE PAY_US_GEO_UPDATE
3060: AND table_value_id is null
3061: AND status in ('P','A')
3062: AND process_type = main_proc_type;
3063:
3064: hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geocodes',185);
3065:
3066: END IF;
3067:
3068: hr_utility.set_location('before commit',2);
3068: hr_utility.set_location('before commit',2);
3069: -- commit; /* We commit at this point so if it fails at any point let it rollback to the savepoint and continue */
3070:
3071:
3072: hr_utility.trace('Exiting pay_us_geo_upd_pkg.upgrade_geocodes');
3073:
3074:
3075: EXCEPTION
3076: WHEN OTHERS THEN
3087: fnd_file.put_line(fnd_file.log, 'sql error ' || sqlcode || ' - ' || substr(sqlerrm,1,80));
3088:
3089: rollback to GEO_UPDATE_SAVEPOINT;
3090:
3091: hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geocodes',170);
3092:
3093: UPDATE PAY_US_GEO_UPDATE
3094: SET description = l_error_text
3095: WHERE assignment_id = main_assign_id
3234:
3235: g_geo_phase_id := p_geo_phase_id;
3236: g_mode := p_mode;
3237:
3238: hr_utility.trace('Entering pay_us_geo_upd_pkg.update_taxability_rules');
3239: hr_utility.trace('The phase id is: '||to_char(g_geo_phase_id));
3240:
3241: FOR ptax_rec IN ptax_cur LOOP
3242:
3239: hr_utility.trace('The phase id is: '||to_char(g_geo_phase_id));
3240:
3241: FOR ptax_rec IN ptax_cur LOOP
3242:
3243: hr_utility.set_location('pay_us_geo_upd_pkg.update_taxability_rules',1);
3244:
3245: SELECT pmod.state_code||'-'||pmod.county_code||'-'||pmod.new_city_code,
3246: process_type
3247: INTO jd_code, l_proc_type
3253: --city taxability rules don't carry a county-code so we have to pull the first
3254: -- row in the case of a city that spans a county.
3255: and rownum = 1;
3256:
3257: hr_utility.set_location('pay_us_geo_upd_pkg.update_taxability_rules',2);
3258:
3259: select count(*) into l_count
3260: from pay_taxability_rules ptax
3261: where ptax.jurisdiction_code = substr(jd_code,1,2)||'-000-'||substr(jd_code,8,4);
3274:
3275: END IF;
3276:
3277: END IF;
3278: hr_utility.set_location('pay_us_geo_upd_pkg.update_taxability_rules',3);
3279:
3280: -- write to the message table so that if this fails unexpectedly we can track which taxability
3281: -- rules have been upgraded already.
3282:
3288: p_new_juri_code => substr(jd_code,1,2)||'-000-'||substr(jd_code,8,4),
3289: p_location => 'PAY_TAXABILITY_RULES',
3290: p_id => null);
3291:
3292: hr_utility.set_location('pay_us_geo_upd_pkg.update_taxability_rules',4);
3293:
3294: END LOOP;
3295: --
3296: --
3303: LOOP
3304: FETCH ptax_ca_cur INTO ptax_ca_rec;
3305: EXIT WHEN ptax_ca_cur%NOTFOUND;
3306:
3307: hr_utility.set_location('pay_us_geo_upd_pkg.update_taxability_rules',15);
3308:
3309: SELECT pmod.new_county_code,
3310: process_type
3311: INTO jd_code, l_proc_type
3322:
3323: -- COMMIT;
3324:
3325: END IF;
3326: hr_utility.set_location('pay_us_geo_upd_pkg.update_taxability_rules',20);
3327:
3328: -- write to the message table so that if this fails unexpectedly we can track which taxability
3329: -- rules have been upgraded already.
3330:
3336: p_new_juri_code => jd_code||'-000-'||'0000',
3337: p_location => 'PAY_TAXABILITY_RULES',
3338: p_id => null);
3339:
3340: hr_utility.set_location('pay_us_geo_upd_pkg.update_taxability_rules',25);
3341:
3342:
3343: END LOOP;
3344: CLOSE ptax_ca_cur ;
3344: CLOSE ptax_ca_cur ;
3345: --
3346: --
3347: --
3348: hr_utility.set_location('pay_us_geo_upd_pkg.update_taxability_rules',5);
3349:
3350: EXCEPTION
3351: WHEN OTHERS THEN
3352:
3351: WHEN OTHERS THEN
3352:
3353: l_error_message_text := to_char(SQLCODE)||SQLERRM||' Program error contact support';
3354: rollback;
3355: hr_utility.set_location('pay_us_geo_upd_pkg.update_taxability_rules',6);
3356:
3357: fnd_file.put_line(fnd_file.log, 'Exception update_taxability_rules' );
3358: fnd_file.put_line(fnd_file.log, 'sql error ' || sqlcode || ' - ' || substr(sqlerrm,1,80));
3359:
3429:
3430: g_geo_phase_id := p_geo_phase_id;
3431: g_mode := p_mode;
3432:
3433: hr_utility.trace('Entering pay_us_geo_upd_pkg.update_org_info');
3434: hr_utility.trace('The phase id is: '||to_char(g_geo_phase_id));
3435:
3436: FOR org_info_rec IN org_info_cur LOOP
3437:
3434: hr_utility.trace('The phase id is: '||to_char(g_geo_phase_id));
3435:
3436: FOR org_info_rec IN org_info_cur LOOP
3437:
3438: hr_utility.set_location('pay_us_geo_upd_pkg.update_org_info',1);
3439:
3440: SELECT pmod.state_code||'-'||pmod.county_code||'-'||pmod.new_city_code,
3441: process_type
3442: INTO new_geocode, l_proc_type
3446: AND pmod.old_city_code = substr(org_info_rec.org_information1,8,4)
3447: AND pmod.process_type in ('UP','PU','RP','U')
3448: AND pmod.patch_name = p_patch_name;
3449:
3450: hr_utility.set_location('pay_us_geo_upd_pkg.update_org_info',2);
3451:
3452: IF G_MODE = 'UPGRADE' THEN
3453:
3454: UPDATE hr_organization_information
3459: -- COMMIT;
3460:
3461: END IF;
3462:
3463: hr_utility.set_location('pay_us_geo_upd_pkg.update_org_info',3);
3464:
3465: -- write to the message table so that if this fails unexpectedly we can track which taxability
3466: -- rules have been upgraded already.
3467:
3473: p_new_juri_code => new_geocode,
3474: p_location => 'HR_ORGANIZATION_INFORMATION',
3475: p_id => null);
3476:
3477: hr_utility.set_location('pay_us_geo_upd_pkg.update_org_info',4);
3478:
3479: END LOOP;
3480:
3481: hr_utility.set_location('pay_us_geo_upd_pkg.update_org_info',5);
3477: hr_utility.set_location('pay_us_geo_upd_pkg.update_org_info',4);
3478:
3479: END LOOP;
3480:
3481: hr_utility.set_location('pay_us_geo_upd_pkg.update_org_info',5);
3482: --
3483: --
3484: --
3485:
3491: LOOP
3492: FETCH org_info_ca_cur into org_info_ca_rec;
3493: EXIT WHEN org_info_ca_cur%NOTFOUND;
3494:
3495: hr_utility.set_location('pay_us_geo_upd_pkg.update_org_info',15);
3496:
3497:
3498: SELECT pmod.new_county_code,
3499: process_type
3521: -- COMMIT;
3522:
3523:
3524: END IF;
3525: hr_utility.set_location('pay_us_geo_upd_pkg.update_org_info',15);
3526:
3527: -- write to the message table so that if this fails unexpectedly
3528: --
3529:
3535: p_new_juri_code => new_geocode,
3536: p_location => 'HR_ORGANIZATION_INFORMATION',
3537: p_id => null);
3538:
3539: hr_utility.set_location('pay_us_geo_upd_pkg.update_org_info',20);
3540: END LOOP ;
3541: CLOSE org_info_ca_cur;
3542: --
3543: --
3546: EXCEPTION
3547: WHEN OTHERS THEN
3548: l_error_message_text := to_char(SQLCODE)||SQLERRM||' Program error contact support';
3549: rollback;
3550: hr_utility.set_location('pay_us_geo_upd_pkg.update_org_info',6);
3551:
3552: fnd_file.put_line(fnd_file.log, 'Exception update_org_info' );
3553: fnd_file.put_line(fnd_file.log, 'sql error ' || sqlcode || ' - ' || substr(sqlerrm,1,80));
3554:
3595: hr_utility.trace('Entering the Geocode Upgrade API');
3596:
3597:
3598: OPEN pay_patch_id(p_patch_name);
3599: hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geo_api',1);
3600:
3601: FETCH pay_patch_id INTO l_id;
3602: hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geo_api',5);
3603:
3598: OPEN pay_patch_id(p_patch_name);
3599: hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geo_api',1);
3600:
3601: FETCH pay_patch_id INTO l_id;
3602: hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geo_api',5);
3603:
3604: CLOSE pay_patch_id;
3605:
3606: hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geo_api',10);
3602: hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geo_api',5);
3603:
3604: CLOSE pay_patch_id;
3605:
3606: hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geo_api',10);
3607:
3608: IF p_mode = 'UPGRADE' THEN
3609:
3610: hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geo_api',15);
3606: hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geo_api',10);
3607:
3608: IF p_mode = 'UPGRADE' THEN
3609:
3610: hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geo_api',15);
3611:
3612: upgrade_geocodes(P_ASSIGN_START => p_assign_id,
3613: P_ASSIGN_END => p_assign_id,
3614: P_GEO_PHASE_ID => l_id,
3616: P_PATCH_NAME => p_patch_name,
3617: P_CITY_NAME => p_city_name,
3618: P_API_MODE => 'Y');
3619:
3620: hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geo_api',20);
3621:
3622: ELSE
3623:
3624: hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geo_api',25);
3620: hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geo_api',20);
3621:
3622: ELSE
3623:
3624: hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geo_api',25);
3625:
3626: upgrade_geocodes(P_ASSIGN_START => p_assign_id,
3627: P_ASSIGN_END => p_assign_id,
3628: P_GEO_PHASE_ID => l_id,
3630: P_PATCH_NAME => p_patch_name,
3631: P_CITY_NAME => p_city_name,
3632: P_API_MODE => 'Y');
3633:
3634: hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geo_api',30);
3635:
3636: END IF;
3637:
3638: hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geo_api',35);
3634: hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geo_api',30);
3635:
3636: END IF;
3637:
3638: hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geo_api',35);
3639:
3640: OPEN chk_last_api(l_id, p_mode);
3641:
3642: hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geo_api',40);
3638: hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geo_api',35);
3639:
3640: OPEN chk_last_api(l_id, p_mode);
3641:
3642: hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geo_api',40);
3643:
3644: FETCH chk_last_api INTO l_chk_last_api;
3645:
3646: hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geo_api',45);
3642: hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geo_api',40);
3643:
3644: FETCH chk_last_api INTO l_chk_last_api;
3645:
3646: hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geo_api',45);
3647:
3648: IF chk_last_api%NOTFOUND THEN /* Everything is complete we can update pay_patch_status to complete */
3649:
3650: hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geo_api',50);
3646: hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geo_api',45);
3647:
3648: IF chk_last_api%NOTFOUND THEN /* Everything is complete we can update pay_patch_status to complete */
3649:
3650: hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geo_api',50);
3651:
3652: UPDATE pay_patch_status
3653: SET status = 'C', phase = null
3654: WHERE id = l_id;
3655: hr_utility.set_location('before commit ',4);
3656:
3657: -- commit;
3658:
3659: hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geo_api',55);
3660:
3661: END IF;
3662:
3663: hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geo_api',60);
3659: hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geo_api',55);
3660:
3661: END IF;
3662:
3663: hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geo_api',60);
3664:
3665: CLOSE chk_last_api;
3666:
3667: hr_utility.trace('Exiting the Geocode Upgrade API');
3750: g_geo_phase_id := p_geo_phase_id;
3751: g_mode := p_mode;
3752:
3753:
3754: hr_utility.trace('Entering pay_us_geo_upd_pkg.update_ca_emp_info');
3755: hr_utility.trace('The phase id is: '||to_char(g_geo_phase_id));
3756:
3757: OPEN canada_emp_fed_tax_cur ;
3758: LOOP
3758: LOOP
3759: FETCH canada_emp_fed_tax_cur into canada_emp_fed_rec;
3760: EXIT WHEN canada_emp_fed_tax_cur%NOTFOUND;
3761:
3762: hr_utility.set_location('pay_us_geo_upd_pkg.update_ca_emp_info',1);
3763: SELECT pmod.new_county_code,
3764: pmod.process_type
3765: INTO new_geocode, l_proc_type
3766: FROM pay_us_modified_geocodes pmod
3781:
3782: END IF;
3783:
3784:
3785: hr_utility.set_location('pay_us_geo_upd_pkg.update_ca_emp_info',2);
3786: -- write to the message table so that if this fails unexpectedly
3787: write_message(
3788: p_proc_type => l_proc_type,
3789: p_person_id => null,
3792: p_new_juri_code => new_geocode,
3793: p_location => 'PAY_CA_EMP_FED_TAX_INFO_F',
3794: p_id => null);
3795:
3796: hr_utility.set_location('pay_us_geo_upd_pkg.update_ca_emp_info',3);
3797:
3798: END LOOP ;
3799: CLOSE canada_emp_fed_tax_cur ;
3800:
3802: LOOP
3803: FETCH canada_emp_prov_tax_cur into canada_emp_prov_rec;
3804: EXIT WHEN canada_emp_prov_tax_cur%NOTFOUND;
3805:
3806: hr_utility.set_location('pay_us_geo_upd_pkg.update_ca_emp_info',4);
3807:
3808: SELECT pmod.new_county_code,
3809: pmod.process_type
3810: INTO new_geocode1, l_proc_type
3827:
3828: END IF;
3829:
3830:
3831: hr_utility.set_location('pay_us_geo_upd_pkg.update_ca_emp_info',5);
3832: -- write to the message table so that if this fails unexpectedly
3833: write_message(
3834: p_proc_type => l_proc_type,
3835: p_person_id => null,
3838: p_new_juri_code => new_geocode1,
3839: p_location => 'PAY_CA_EMP_PROV_TAX_INFO_F',
3840: p_id => null);
3841:
3842: hr_utility.set_location('pay_us_geo_upd_pkg.update_ca_emp_info',6);
3843: END LOOP ;
3844: CLOSE canada_emp_prov_tax_cur ;
3845:
3846:
3849: LOOP
3850: FETCH canada_leg_info_cur into canada_leg_info_rec ;
3851: EXIT WHEN canada_leg_info_cur%NOTFOUND;
3852:
3853: hr_utility.set_location('pay_us_geo_upd_pkg.update_ca_emp_info',7);
3854: SELECT pmod.new_county_code,
3855: pmod.process_type
3856: INTO new_geocode2, l_proc_type
3857: FROM pay_us_modified_geocodes pmod
3868:
3869:
3870: END IF;
3871:
3872: hr_utility.set_location('pay_us_geo_upd_pkg.update_ca_emp_info',8);
3873: -- write to the message table so that if this fails unexpectedly
3874: write_message(
3875: p_proc_type => l_proc_type,
3876: p_person_id => null,
3879: p_new_juri_code => new_geocode2,
3880: p_location => 'PAY_CA_LEGISLATION_INFO',
3881: p_id => null);
3882:
3883: hr_utility.set_location('pay_us_geo_upd_pkg.update_ca_emp_info',9);
3884: END LOOP ;
3885:
3886: CLOSE canada_leg_info_cur;
3887:
3884: END LOOP ;
3885:
3886: CLOSE canada_leg_info_cur;
3887:
3888: hr_utility.set_location('pay_us_geo_upd_pkg.update_ca_emp_info',10);
3889: EXCEPTION
3890: WHEN OTHERS THEN
3891: l_error_message_text := to_char(SQLCODE)||SQLERRM||
3892: ' Program error contact support';
3890: WHEN OTHERS THEN
3891: l_error_message_text := to_char(SQLCODE)||SQLERRM||
3892: ' Program error contact support';
3893: rollback;
3894: hr_utility.set_location('pay_us_geo_upd_pkg.update_ca_emp_info',11);
3895:
3896: fnd_file.put_line(fnd_file.log, 'Exception update_ca_emp_info' );
3897: fnd_file.put_line(fnd_file.log, 'sql error ' || sqlcode || ' - ' || substr(sqlerrm,1,80));
3898:
4062:
4063: g_geo_phase_id := p_geo_phase_id;
4064: g_mode := p_mode;
4065:
4066: hr_utility.trace('Entering pay_us_geo_upd_pkg. group_level_balance');
4067: hr_utility.trace('The phase id is: '||to_char(g_geo_phase_id));
4068:
4069: OPEN c_legislation_code ( p_start_payroll_action
4070: ,p_end_payroll_action) ;
4101: exception
4102:
4103: when no_data_found then
4104:
4105: hr_utility.set_location('pay_us_geo_upd_pkg. group_level_balance',1);
4106: SELECT pmod.state_code||'-'||pmod.county_code||'-'||pmod.new_city_code,
4107: process_type, pmod.new_city_code
4108: INTO l_geocode, l_proc_type, l_new_city_code
4109: FROM pay_us_modified_geocodes pmod
4125: -- COMMIT;
4126: END IF;
4127:
4128:
4129: hr_utility.set_location('pay_us_geo_upd_pkg. group_level_balance',2);
4130: -- write to the message table so that if this fails unexpectedly
4131: write_message(
4132: p_proc_type => l_proc_type,
4133: p_person_id => group_level_bal_us_rec.run_balance_id,
4136: p_new_juri_code => l_geocode,
4137: p_location => 'PAY_RUN_BALANCES',
4138: p_id => null);
4139:
4140: hr_utility.set_location('pay_us_geo_upd_pkg. group_level_balance',3);
4141:
4142: end;
4143:
4144: END LOOP ;
4141:
4142: end;
4143:
4144: END LOOP ;
4145: hr_utility.trace('Entering pay_us_geo_upd_pkg. group_level_balance - 7001');
4146:
4147: CLOSE group_level_bal_us ;
4148:
4149: else
4172: exception
4173:
4174: when no_data_found then
4175:
4176: hr_utility.trace('Entering pay_us_geo_upd_pkg. group_level_balance - 7002');
4177: hr_utility.set_location('pay_us_geo_upd_pkg. group_level_balance',4);
4178: SELECT pmod.new_county_code, pmod.process_type
4179: INTO l_geocode, l_proc_type
4180: FROM pay_us_modified_geocodes pmod
4173:
4174: when no_data_found then
4175:
4176: hr_utility.trace('Entering pay_us_geo_upd_pkg. group_level_balance - 7002');
4177: hr_utility.set_location('pay_us_geo_upd_pkg. group_level_balance',4);
4178: SELECT pmod.new_county_code, pmod.process_type
4179: INTO l_geocode, l_proc_type
4180: FROM pay_us_modified_geocodes pmod
4181: WHERE pmod.state_code = 'CA'
4182: -- AND pmod.county_code = group_level_bal_ca_rec.jurisdiction_code
4183: AND pmod.county_code = substr(group_level_bal_ca_rec.jurisdiction_code,1,2)
4184: AND pmod.patch_name = p_patch_name;
4185:
4186: hr_utility.trace('Entering pay_us_geo_upd_pkg. group_level_balance - 7003');
4187:
4188: IF G_MODE = 'UPGRADE' THEN
4189:
4190: UPDATE pay_run_balances
4194: -- AND jurisdiction_code = group_level_bal_ca_rec.jurisdiction_code
4195: AND substr(jurisdiction_code,1,2) =
4196: substr(group_level_bal_ca_rec.jurisdiction_code,1,2) ;
4197:
4198: hr_utility.trace('Entering pay_us_geo_upd_pkg. group_level_balance - 7004');
4199:
4200: -- COMMIT;
4201: END IF;
4202:
4199:
4200: -- COMMIT;
4201: END IF;
4202:
4203: hr_utility.set_location('pay_us_geo_upd_pkg. group_level_balance',5);
4204: -- write to the message table so that if this fails unexpectedly
4205: write_message(
4206: p_proc_type => l_proc_type,
4207: p_person_id => group_level_bal_ca_rec.run_balance_id,
4210: p_new_juri_code => l_geocode,
4211: p_location => 'PAY_RUN_BALANCES',
4212: p_id => null);
4213:
4214: hr_utility.set_location('pay_us_geo_upd_pkg. group_level_balance',6);
4215:
4216: end;
4217:
4218: END LOOP ;
4224: END LOOP;
4225:
4226: CLOSE c_legislation_code;
4227:
4228: hr_utility.set_location('pay_us_geo_upd_pkg. group_level_balance',7);
4229: EXCEPTION
4230: WHEN OTHERS THEN
4231: l_error_message_text := to_char(SQLCODE)||SQLERRM||
4232: ' Program error contact support';
4230: WHEN OTHERS THEN
4231: l_error_message_text := to_char(SQLCODE)||SQLERRM||
4232: ' Program error contact support';
4233:
4234: hr_utility.trace('Entering pay_us_geo_upd_pkg. group_level_balance - 7005');
4235: hr_utility.trace('l_error_message_text - ' ||l_error_message_text);
4236:
4237:
4238: fnd_file.put_line(fnd_file.log, 'Exception update_ca_emp_info' );
4242: rollback;
4243:
4244:
4245:
4246: hr_utility.set_location('pay_us_geo_upd_pkg. group_level_balance',8);
4247: raise_application_error(-20001,l_error_message_text);
4248:
4249: hr_utility.set_location('before commit ',6);
4250: -- commit;
4250: -- commit;
4251: END group_level_balance ;
4252: --
4253:
4254: END pay_us_geo_upd_pkg;