[Home] [Help]
218: ln_upgrade_patch pay_patch_status.patch_name%TYPE;
219: --
220: begin
221:
222: hr_utility.trace('reached range_cursor');
223:
224: select ppa.legislative_parameters,
225: pay_us_geo_upd_pkg.get_parameter('PATCH_NAME',PPa.legislative_parameters)
226: into leg_param,
241: and tr.assignment_id = paf.assignment_id
242: and :pactid is not null
243: order by paf.person_id';
244:
245: hr_utility.trace(sqlstr);
246:
247: hr_utility.trace('leaving range_cursor');
248:
249: end range_cursor;
243: order by paf.person_id';
244:
245: hr_utility.trace(sqlstr);
246:
247: hr_utility.trace('leaving range_cursor');
248:
249: end range_cursor;
250:
251:
360: lv_curr_chunk number;
361: /* Bug#7240914: Changes end*/
362: begin
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:
362: begin
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:
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,
373: l_mode;
374:
375: close c_parameters;
376:
377: hr_utility.trace('l_patch_name is '|| l_patch_name );
378:
379:
380: hr_utility.trace('before open c_geo_check ');
381: open c_geo_check (l_patch_name);
376:
377: hr_utility.trace('l_patch_name is '|| l_patch_name );
378:
379:
380: hr_utility.trace('before open c_geo_check ');
381: open c_geo_check (l_patch_name);
382:
383: fetch c_geo_check into lv_phase, lv_status;
384:
382:
383: fetch c_geo_check into lv_phase, lv_status;
384:
385: if c_geo_check%notfound or lv_status <> 'C' then
386: hr_utility.trace('c_geo_check not found ');
387:
388: if c_geo_check%notfound and chunk=1 then
389: hr_utility.trace('c_geo_check not found chunk = 1');
390: /*
385: if c_geo_check%notfound or lv_status <> 'C' then
386: hr_utility.trace('c_geo_check not found ');
387:
388: if c_geo_check%notfound and chunk=1 then
389: hr_utility.trace('c_geo_check not found chunk = 1');
390: /*
391: If both conditions above are true, there is a geocode update
392: underway and a row for this process needs to be added to the
393: pay_patch_status table.
391: If both conditions above are true, there is a geocode update
392: underway and a row for this process needs to be added to the
393: pay_patch_status table.
394: */
395: hr_utility.trace('inserting into pay_patch_status ');
396: insert into pay_patch_status
397: (ID,
398: PATCH_NUMBER,
399: PATCH_NAME,
421: 'Q2' );
422:
423: end if; -- end if for the chunk=1
424:
425: hr_utility.trace('opening c_get_phase_id ');
426:
427: open c_get_phase_id(l_patch_name);
428:
429: fetch c_get_phase_id into l_geo_phase_id;
428:
429: fetch c_get_phase_id into l_geo_phase_id;
430:
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);
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:
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:
468: IF chunk=1 THEN
469:
470: select min(balance_load_date)
473: open c_actions_run_bal(pactid,l_balance_load_date);
474:
475: /* Bug#7240914: Fetch number of chunks created for the current run
476: from table pay_population_ranges */
477: hr_utility.trace( 'Fetching Number of chunks created for this process from pay_population_ranges.');
478: select max(chunk_number)
479: into lv_no_of_chunks
480: from pay_population_ranges
481: where payroll_action_id = pactid;
479: into lv_no_of_chunks
480: from pay_population_ranges
481: where payroll_action_id = pactid;
482:
483: hr_utility.trace( 'Number of chunks: ' || to_char(lv_no_of_chunks));
484: lv_no_of_chunks := NVL(lv_no_of_chunks,0);
485: lv_count := 1 ;
486: /*Bug#7240914: Changes end here*/
487: loop
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: --
506: into lv_curr_chunk
507: from dual;
508: END IF;
509:
510: hr_utility.trace( 'lv_count: ' || to_char(lv_count));
511: hr_utility.trace( 'lv_current_chunk: ' || to_char(lv_curr_chunk));
512: /*Bug#7240914: changes end here */
513: hr_nonrun_asact.insact(lockingactid => lockingactid,
514: Object_id => l_payact_id,
507: from dual;
508: END IF;
509:
510: hr_utility.trace( 'lv_count: ' || to_char(lv_count));
511: hr_utility.trace( 'lv_current_chunk: ' || to_char(lv_curr_chunk));
512: /*Bug#7240914: changes end here */
513: hr_nonrun_asact.insact(lockingactid => lockingactid,
514: Object_id => l_payact_id,
515: pactid => pactid,
536:
537: END IF;
538:
539:
540: hr_utility.trace('leaving action_creation');
541:
542: if c_get_phase_id%isopen then
543: close c_get_phase_id;
544: end if;
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,
751: open c_get_params;
752: fetch c_get_params into lv_cur_geo_patch, ln_patch_number;
753: close c_get_params;
754:
755: --hr_utility.trace(' lv_cur_geo_patch = ' || lv_cur_geo_patch);
756: --hr_utility.trace('archive deinit ln_patch_number = ' || ln_patch_number);
757: fnd_file.put_line(fnd_file.log, ' lv_cur_geo_patch = ' || lv_cur_geo_patch);
758: fnd_file.put_line(fnd_file.log, 'archive deinit ln_patch_number = ' || ln_patch_number);
759: --
752: fetch c_get_params into lv_cur_geo_patch, ln_patch_number;
753: close c_get_params;
754:
755: --hr_utility.trace(' lv_cur_geo_patch = ' || lv_cur_geo_patch);
756: --hr_utility.trace('archive deinit ln_patch_number = ' || ln_patch_number);
757: fnd_file.put_line(fnd_file.log, ' lv_cur_geo_patch = ' || lv_cur_geo_patch);
758: fnd_file.put_line(fnd_file.log, 'archive deinit ln_patch_number = ' || ln_patch_number);
759: --
760: open c_geo_check(lv_cur_geo_patch, ln_patch_number);
787: close c_geo_check;
788:
789: /* EXCEPTION
790: --
791: WHEN hr_utility.hr_error THEN
792: --
793: -- Set up error message and error return code.
794: --
795:
792: --
793: -- Set up error message and error return code.
794: --
795:
796: hr_utility.trace('in the exception 1');
797:
798: errbuf := hr_utility.get_message;
799: retcode := 2;
800: --
794: --
795:
796: hr_utility.trace('in the exception 1');
797:
798: errbuf := hr_utility.get_message;
799: retcode := 2;
800: --
801: --
802: WHEN others THEN
803: --
804: -- Set up error message and return code.
805: --
806:
807: hr_utility.trace('in the exception 2 sqlerrm = ' || sqlerrm);
808:
809: errbuf := sqlerrm;
810: retcode := 2; */
811: end archive_deinit;
826: IS
827:
828: BEGIN
829:
830: hr_utility.trace('Entering pay_us_geo_upd_pkg.write message');
831: /*Added field p_description for Bug#10060041*/
832: IF G_MODE = 'UPGRADE' THEN
833: insert into PAY_US_GEO_UPDATE (ID,
834: ASSIGNMENT_ID,
883: p_description);
884:
885:
886: END IF;
887: hr_utility.trace('Exiting pay_us_geo_upd_pkg.write message');
888:
889: END write_message;
890:
891:
1382:
1383:
1384: BEGIN
1385:
1386: hr_utility.trace('Entering pay_us_geo_upd_pkg.balance_batch_lines');
1387:
1388: IF ((p_proc_type <> 'SU' and p_proc_type <> 'US') and p_api_mode = 'N') THEN
1389:
1390: hr_utility.trace('Before update of balance_batch_lines for assignment id: '||to_char(p_assign_id));
1386: hr_utility.trace('Entering pay_us_geo_upd_pkg.balance_batch_lines');
1387:
1388: IF ((p_proc_type <> 'SU' and p_proc_type <> 'US') and p_api_mode = 'N') THEN
1389:
1390: hr_utility.trace('Before update of balance_batch_lines for assignment id: '||to_char(p_assign_id));
1391:
1392: IF G_MODE = 'UPGRADE' THEN
1393:
1394: UPDATE pay_balance_batch_lines
1477: l_run_balance_count number;
1478:
1479: BEGIN
1480:
1481: hr_utility.trace('Entering pay_us_geo_upd_pkg.pay_run_balances');
1482:
1483: IF ((p_proc_type <> 'SU' and p_proc_type <> 'US') and p_api_mode = 'N') THEN
1484:
1485: hr_utility.trace('Before update of pay_run_balances for assignment id: '||to_char(p_assign_id));
1481: hr_utility.trace('Entering pay_us_geo_upd_pkg.pay_run_balances');
1482:
1483: IF ((p_proc_type <> 'SU' and p_proc_type <> 'US') and p_api_mode = 'N') THEN
1484:
1485: hr_utility.trace('Before update of pay_run_balances for assignment id: '||to_char(p_assign_id));
1486:
1487: IF G_MODE = 'UPGRADE' THEN
1488:
1489: UPDATE pay_run_balances
1582: IS
1583:
1584: BEGIN
1585:
1586: hr_utility.trace('Entering pay_us_geo_upd_pkg.city_tax_records');
1587:
1588: IF ((p_proc_type <> 'SU' and p_proc_type <> 'US') and p_api_mode = 'N') THEN
1589:
1590: hr_utility.trace('Before update of city tax records for assignment id: '||to_char(p_assign_id));
1586: hr_utility.trace('Entering pay_us_geo_upd_pkg.city_tax_records');
1587:
1588: IF ((p_proc_type <> 'SU' and p_proc_type <> 'US') and p_api_mode = 'N') THEN
1589:
1590: hr_utility.trace('Before update of city tax records for assignment id: '||to_char(p_assign_id));
1591:
1592: IF G_MODE = 'UPGRADE' THEN
1593:
1594: UPDATE pay_us_emp_city_tax_rules_f
1676: */
1677:
1678: BEGIN
1679:
1680: hr_utility.trace('Entering pay_us_geo_upd_pkg.del_dup_city_tax_recs');
1681:
1682: IF G_MODE = 'UPGRADE' THEN
1683:
1684: FOR dup_rec IN dup_city_tax_rows
1684: FOR dup_rec IN dup_city_tax_rows
1685:
1686: LOOP
1687:
1688: hr_utility.trace('Deleting dups for Assign ID: ' || to_char(dup_rec.assignment_id) ||
1689: ' Geocode: ' || dup_rec.jurisdiction_code);
1690:
1691: DELETE FROM pay_us_emp_city_tax_rules_f pecto
1692: WHERE pecto.rowid < (SELECT max(pecti.rowid)
1771: l_flag varchar2(2);
1772:
1773: BEGIN
1774:
1775: hr_utility.trace('Entering pay_us_geo_upd_pkg.run_results');
1776:
1777: IF ((p_proc_type <> 'SU' and p_proc_type <> 'US') and p_api_mode = 'N') THEN
1778:
1779: hr_utility.trace('Before update of run result values for assignment_action_id: '||to_char(p_assign_act_id));
1775: hr_utility.trace('Entering pay_us_geo_upd_pkg.run_results');
1776:
1777: IF ((p_proc_type <> 'SU' and p_proc_type <> 'US') and p_api_mode = 'N') THEN
1778:
1779: hr_utility.trace('Before update of run result values for assignment_action_id: '||to_char(p_assign_act_id));
1780:
1781: --
1782: --Per bug 2996546 changed the where clause in
1783: --in the update of pay_run_result_values
1853: END IF;
1854:
1855: END IF;
1856:
1857: hr_utility.trace('After update of run result values for assignment_action_id: '||to_char(p_assign_act_id));
1858:
1859: ELSE
1860:
1861: -- Write a message to the table to be later spooled to a report
1881:
1882:
1883: IF ((p_proc_type <> 'SU' and p_proc_type <> 'US') and p_api_mode = 'N') THEN
1884:
1885: hr_utility.trace('Before update of run results for assignment_action_id: '||to_char(p_assign_act_id));
1886:
1887: IF G_MODE = 'UPGRADE' THEN
1888:
1889: UPDATE pay_run_results
1965: CLOSE ele_run_results;
1966: END IF;
1967:
1968:
1969: hr_utility.trace('After update of run results for assignment_action_id: '||to_char(p_assign_act_id));
1970:
1971: hr_utility.trace('Exiting pay_us_geo_upd_pkg.run_results');
1972:
1973: END run_results;
1967:
1968:
1969: hr_utility.trace('After update of run results for assignment_action_id: '||to_char(p_assign_act_id));
1970:
1971: hr_utility.trace('Exiting pay_us_geo_upd_pkg.run_results');
1972:
1973: END run_results;
1974: --
1975: --
2000: l_pac_inside_exist varchar2(2);
2001:
2002: BEGIN
2003:
2004: hr_utility.trace('Entering pay_us_geo_upd_pkg.pay_action_contexts');
2005:
2006: IF ((p_proc_type <> 'SU' and p_proc_type <> 'US') and p_api_mode = 'N') THEN
2007:
2008: hr_utility.trace('Before update of pay_action_contexts for assignment id: '||to_char(p_assign_id));
2004: hr_utility.trace('Entering pay_us_geo_upd_pkg.pay_action_contexts');
2005:
2006: IF ((p_proc_type <> 'SU' and p_proc_type <> 'US') and p_api_mode = 'N') THEN
2007:
2008: hr_utility.trace('Before update of pay_action_contexts for assignment id: '||to_char(p_assign_id));
2009:
2010: IF G_MODE = 'UPGRADE' THEN
2011:
2012: UPDATE pay_action_contexts
2080: IS
2081:
2082: BEGIN
2083:
2084: hr_utility.trace('Entering pay_us_geo_upd_pkg.archive_item_contexts');
2085:
2086: IF ((p_proc_type <> 'SU' and p_proc_type <> 'US') and p_api_mode = 'N') THEN
2087:
2088: hr_utility.trace('Before update of archive item contexts for assignment_id: '||to_char(p_assign_id));
2084: hr_utility.trace('Entering pay_us_geo_upd_pkg.archive_item_contexts');
2085:
2086: IF ((p_proc_type <> 'SU' and p_proc_type <> 'US') and p_api_mode = 'N') THEN
2087:
2088: hr_utility.trace('Before update of archive item contexts for assignment_id: '||to_char(p_assign_id));
2089:
2090: IF G_MODE = 'UPGRADE' THEN
2091:
2092: UPDATE ff_archive_item_contexts ffaic
2096: AND ffaic.archive_item_id = p_archive_item_id;
2097:
2098: END IF;
2099:
2100: hr_utility.trace('After update of archive item contexts for assignment_id: '||to_char(p_assign_id));
2101:
2102: -- Write a message to the table to be later spooled to a report
2103:
2104: write_message(
2125: p_id => p_archive_item_id);
2126:
2127: END IF;
2128:
2129: hr_utility.trace('Exiting pay_us_geo_upd_pkg.archive_item_contexts');
2130:
2131:
2132:
2133: END archive_item_contexts;
2147:
2148: IS
2149:
2150: BEGIN
2151: hr_utility.trace('Entering pay_us_geo_upd_pkg.element_entries');
2152:
2153: IF ((p_proc_type <> 'SU' and p_proc_type <> 'US') and p_api_mode = 'N') THEN
2154:
2155: hr_utility.trace('Before update of element entry values for assignment_id: '||to_char(p_assign_id));
2151: hr_utility.trace('Entering pay_us_geo_upd_pkg.element_entries');
2152:
2153: IF ((p_proc_type <> 'SU' and p_proc_type <> 'US') and p_api_mode = 'N') THEN
2154:
2155: hr_utility.trace('Before update of element entry values for assignment_id: '||to_char(p_assign_id));
2156:
2157: IF G_MODE = 'UPGRADE' THEN
2158:
2159: UPDATE pay_element_entry_values_f
2162: AND input_value_id+0 = p_input_value_id
2163: AND element_entry_id = p_ele_ent_id;
2164:
2165: END IF;
2166: hr_utility.trace('After update of element entry values for assignment_id: '||to_char(p_assign_id));
2167:
2168: -- Write a message to the table to be later spooled to a report
2169:
2170: write_message(
2190: p_id => p_ele_ent_id);
2191:
2192: END IF;
2193:
2194: hr_utility.trace('Exiting pay_us_geo_upd_pkg.element_entries');
2195:
2196:
2197:
2198: END element_entries;
2213: IS
2214:
2215: BEGIN
2216:
2217: hr_utility.trace('Entering pay_us_geo_upd_pkg.balance_contexts');
2218:
2219: IF ((p_proc_type <> 'SU' and p_proc_type <> 'US') and p_api_mode = 'N') THEN
2220:
2221: hr_utility.trace('Before update of latest balances context for assignment_action_id: '||to_char(p_assign_act_id));
2217: hr_utility.trace('Entering pay_us_geo_upd_pkg.balance_contexts');
2218:
2219: IF ((p_proc_type <> 'SU' and p_proc_type <> 'US') and p_api_mode = 'N') THEN
2220:
2221: hr_utility.trace('Before update of latest balances context for assignment_action_id: '||to_char(p_assign_act_id));
2222:
2223: IF G_MODE = 'UPGRADE' THEN
2224:
2225: UPDATE pay_balance_context_values
2254: p_id => p_lat_bal_id);
2255:
2256: END IF;
2257:
2258: hr_utility.trace('After update of latest balances context for assignment_action_id: '||to_char(p_assign_act_id));
2259:
2260:
2261: hr_utility.trace('Exiting pay_us_geo_upd_pkg.balance_contexts');
2262:
2257:
2258: hr_utility.trace('After update of latest balances context for assignment_action_id: '||to_char(p_assign_act_id));
2259:
2260:
2261: hr_utility.trace('Exiting pay_us_geo_upd_pkg.balance_contexts');
2262:
2263: END balance_contexts;
2264:
2265:
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)
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)
2335: ||' for assignment_id : ' || to_char(p_assignment_id));
2336:
2337: hr_utility.set_location('pay_us_geo_upd_pkg.duplicate_vertex_ee', 2);
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:
2355: entry */
2356:
2357: IF G_MODE = 'UPGRADE' THEN
2358:
2359: hr_utility.trace('The previous element entry id is: '||to_char(l_prev_eleid));
2360:
2361: update pay_element_entry_values_f pev
2362: set pev.screen_entry_value = pev.screen_entry_value + l_percent
2363: where pev.element_entry_id = l_prev_eleid
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:
2479: lc_insert_rec varchar2(1);
2480: l_profile_value varchar2(1):='N';
2481: BEGIN
2482:
2483: hr_utility.trace('Entering pay_us_geo_upd_pkg.insert_ele_entries');
2484:
2485: lc_insert_rec := 'N';
2486:
2487: IF ((p_proc_type = 'SU' or p_proc_type = 'US') and p_api_mode = 'N') THEN
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',
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
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
2570:
2571: /* changes for 7240905*/
2571: /* changes for 7240905*/
2572: if(fnd_profile.value('HR_CROSS_BUSINESS_GROUP'))<> 'Y' then
2573: fnd_profile.put('HR_CROSS_BUSINESS_GROUP','Y');
2574: l_profile_value := 'Y';
2575: hr_utility.trace('modifed the profile to'||to_char(fnd_profile.value('HR_CROSS_BUSINESS_GROUP')));
2576: end if;
2577:
2578: ln_emp_city_tax_rule_id :=
2579: pay_us_emp_dt_tax_rules.insert_def_city_rec(p_assign_id,
2587:
2588: /* changes for 7240905*/
2589: if(l_profile_value ='Y') 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:
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(
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,
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(
2740: percentage NUMBER;
2741:
2742: BEGIN
2743:
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);
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,
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:
2809:
2810: /*
2811: EXCEPTION
2812: WHEN OTHERS THEN
2813: hr_utility.trace(SQLCODE||SQLERRM||'Program error contact support');
2814: hr_utility.raise_error;
2815: */
2816: END check_time;
2817:
2810: /*
2811: EXCEPTION
2812: WHEN OTHERS THEN
2813: hr_utility.trace(SQLCODE||SQLERRM||'Program error contact support');
2814: hr_utility.raise_error;
2815: */
2816: END check_time;
2817:
2818:
2827: g_geo_phase_id := null;
2828: g_mode := null;
2829: g_process_type := null;
2830:
2831: -- hr_utility.trace_on(null,'oracle');
2832:
2833: hr_utility.trace('Entering pay_us_geo_upd_pkg.upgrade_geocodes');
2834:
2835: -- Set the global phase id for the geo update
2829: g_process_type := null;
2830:
2831: -- hr_utility.trace_on(null,'oracle');
2832:
2833: hr_utility.trace('Entering pay_us_geo_upd_pkg.upgrade_geocodes');
2834:
2835: -- Set the global phase id for the geo update
2836:
2837: g_geo_phase_id := p_geo_phase_id;
2835: -- Set the global phase id for the geo update
2836:
2837: g_geo_phase_id := p_geo_phase_id;
2838:
2839: hr_utility.trace('The pay patch status id for this upgrade is: '||to_char(g_geo_phase_id));
2840:
2841: -- Set the global mode for the upgrade
2842:
2843: g_mode := p_mode;
2841: -- Set the global mode for the upgrade
2842:
2843: g_mode := p_mode;
2844:
2845: hr_utility.trace('The mode for this upgrade is: '||g_mode);
2846:
2847:
2848:
2849: --Check if pay_us_asg_reporting table exists as some clients may
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;
2885: -- Set the global variable for g_process_type
2886:
2887: g_process_type := main_proc_type;
2888:
2889: hr_utility.trace('The process type for this pair of geocodes is: '||g_process_type);
2890: hr_utility.trace('The main assignment id is: '||to_char(main_assign_id));
2891: hr_utility.trace('The main old juri code id is: '||main_old_juri_code);
2892: hr_utility.trace('The main new juri code id is: '||main_new_juri_code);
2893: hr_utility.trace('The main person id is: '||to_char(main_person_id));
2886:
2887: g_process_type := main_proc_type;
2888:
2889: hr_utility.trace('The process type for this pair of geocodes is: '||g_process_type);
2890: hr_utility.trace('The main assignment id is: '||to_char(main_assign_id));
2891: hr_utility.trace('The main old juri code id is: '||main_old_juri_code);
2892: hr_utility.trace('The main new juri code id is: '||main_new_juri_code);
2893: hr_utility.trace('The main person id is: '||to_char(main_person_id));
2894: hr_utility.trace('The city name is: '||p_city_name);
2887: g_process_type := main_proc_type;
2888:
2889: hr_utility.trace('The process type for this pair of geocodes is: '||g_process_type);
2890: hr_utility.trace('The main assignment id is: '||to_char(main_assign_id));
2891: hr_utility.trace('The main old juri code id is: '||main_old_juri_code);
2892: hr_utility.trace('The main new juri code id is: '||main_new_juri_code);
2893: hr_utility.trace('The main person id is: '||to_char(main_person_id));
2894: hr_utility.trace('The city name is: '||p_city_name);
2895:
2888:
2889: hr_utility.trace('The process type for this pair of geocodes is: '||g_process_type);
2890: hr_utility.trace('The main assignment id is: '||to_char(main_assign_id));
2891: hr_utility.trace('The main old juri code id is: '||main_old_juri_code);
2892: hr_utility.trace('The main new juri code id is: '||main_new_juri_code);
2893: hr_utility.trace('The main person id is: '||to_char(main_person_id));
2894: hr_utility.trace('The city name is: '||p_city_name);
2895:
2896: -- We first insert a row into PAY_US_GEO_UPDATE to state that we are processing this assignment
2889: hr_utility.trace('The process type for this pair of geocodes is: '||g_process_type);
2890: hr_utility.trace('The main assignment id is: '||to_char(main_assign_id));
2891: hr_utility.trace('The main old juri code id is: '||main_old_juri_code);
2892: hr_utility.trace('The main new juri code id is: '||main_new_juri_code);
2893: hr_utility.trace('The main person id is: '||to_char(main_person_id));
2894: hr_utility.trace('The city name is: '||p_city_name);
2895:
2896: -- We first insert a row into PAY_US_GEO_UPDATE to state that we are processing this assignment
2897: -- Our concern is how do we track an assignment that has errored. So we will start by creating
2890: hr_utility.trace('The main assignment id is: '||to_char(main_assign_id));
2891: hr_utility.trace('The main old juri code id is: '||main_old_juri_code);
2892: hr_utility.trace('The main new juri code id is: '||main_new_juri_code);
2893: hr_utility.trace('The main person id is: '||to_char(main_person_id));
2894: hr_utility.trace('The city name is: '||p_city_name);
2895:
2896: -- We first insert a row into PAY_US_GEO_UPDATE to state that we are processing this assignment
2897: -- Our concern is how do we track an assignment that has errored. So we will start by creating
2898: -- a row for the assignment with a p_status of 'U'. Then at the end of the loop we will
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:
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:
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);
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');
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');
3366:
3367:
3368: EXCEPTION
3369: WHEN OTHERS THEN
3368: EXCEPTION
3369: WHEN OTHERS THEN
3370: l_error_text := 'An error occurred in step: '||l_proc_stage||' The sql error message is: '||SQLERRM|| ' The error code is: '||to_char(SQLCODE);
3371:
3372: hr_utility.trace(to_char(SQLCODE)||SQLERRM||'Program error contact support');
3373: hr_utility.trace('Entered the main program exception handler');
3374: hr_utility.trace('The code failed at process type of: '||l_proc_stage);
3375:
3376: fnd_file.put_line(fnd_file.log, 'Exception ' || l_proc_stage || ' Person id = ' || to_char(main_person_id));
3369: WHEN OTHERS THEN
3370: l_error_text := 'An error occurred in step: '||l_proc_stage||' The sql error message is: '||SQLERRM|| ' The error code is: '||to_char(SQLCODE);
3371:
3372: hr_utility.trace(to_char(SQLCODE)||SQLERRM||'Program error contact support');
3373: hr_utility.trace('Entered the main program exception handler');
3374: hr_utility.trace('The code failed at process type of: '||l_proc_stage);
3375:
3376: fnd_file.put_line(fnd_file.log, 'Exception ' || l_proc_stage || ' Person id = ' || to_char(main_person_id));
3377: fnd_file.put_line(fnd_file.log, 'Exception ' || l_proc_stage || ' Assignment id = ' || to_char(main_assign_id));
3370: l_error_text := 'An error occurred in step: '||l_proc_stage||' The sql error message is: '||SQLERRM|| ' The error code is: '||to_char(SQLCODE);
3371:
3372: hr_utility.trace(to_char(SQLCODE)||SQLERRM||'Program error contact support');
3373: hr_utility.trace('Entered the main program exception handler');
3374: hr_utility.trace('The code failed at process type of: '||l_proc_stage);
3375:
3376: fnd_file.put_line(fnd_file.log, 'Exception ' || l_proc_stage || ' Person id = ' || to_char(main_person_id));
3377: fnd_file.put_line(fnd_file.log, 'Exception ' || l_proc_stage || ' Assignment id = ' || to_char(main_assign_id));
3378: fnd_file.put_line(fnd_file.log, 'Exception ' || l_proc_stage || ' Old Jurisdiction Code = ' || main_old_juri_code);
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:
3527:
3528: g_geo_phase_id := p_geo_phase_id;
3529: g_mode := p_mode;
3530:
3531: hr_utility.trace('Entering pay_us_geo_upd_pkg.update_taxability_rules');
3532: hr_utility.trace('The phase id is: '||to_char(g_geo_phase_id));
3533:
3534: FOR ptax_rec IN ptax_cur LOOP
3535:
3528: g_geo_phase_id := p_geo_phase_id;
3529: g_mode := p_mode;
3530:
3531: hr_utility.trace('Entering pay_us_geo_upd_pkg.update_taxability_rules');
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);
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:
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:
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:
3717:
3718: g_geo_phase_id := p_geo_phase_id;
3719: g_mode := p_mode;
3720:
3721: hr_utility.trace('Entering pay_us_geo_upd_pkg.update_county_name');
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:
3718: g_geo_phase_id := p_geo_phase_id;
3719: g_mode := p_mode;
3720:
3721: hr_utility.trace('Entering pay_us_geo_upd_pkg.update_county_name');
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);
3719: g_mode := p_mode;
3720:
3721: hr_utility.trace('Entering pay_us_geo_upd_pkg.update_county_name');
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:
3720:
3721: hr_utility.trace('Entering pay_us_geo_upd_pkg.update_county_name');
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);
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.';
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;
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.';
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:
4014:
4015: g_geo_phase_id := p_geo_phase_id;
4016: g_mode := p_mode;
4017:
4018: hr_utility.trace('Entering pay_us_geo_upd_pkg.update_city_name');
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:
4015: g_geo_phase_id := p_geo_phase_id;
4016: g_mode := p_mode;
4017:
4018: hr_utility.trace('Entering pay_us_geo_upd_pkg.update_city_name');
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);
4016: g_mode := p_mode;
4017:
4018: hr_utility.trace('Entering pay_us_geo_upd_pkg.update_city_name');
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:
4017:
4018: hr_utility.trace('Entering pay_us_geo_upd_pkg.update_city_name');
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);
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,
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:
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;
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:
4701:
4702: g_geo_phase_id := p_geo_phase_id;
4703: g_mode := p_mode;
4704:
4705: hr_utility.trace('Entering pay_us_geo_upd_pkg.update_org_info');
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:
4702: g_geo_phase_id := p_geo_phase_id;
4703: g_mode := p_mode;
4704:
4705: hr_utility.trace('Entering pay_us_geo_upd_pkg.update_org_info');
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);
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:
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:
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:
4863: l_id number;
4864:
4865: BEGIN
4866:
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);
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');
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');
4940:
4941: EXCEPTION
4942: WHEN OTHERS THEN
4943: hr_utility.trace(to_char(SQLCODE)||SQLERRM||'Program error contact support');
4939: hr_utility.trace('Exiting the Geocode Upgrade API');
4940:
4941: EXCEPTION
4942: WHEN OTHERS THEN
4943: hr_utility.trace(to_char(SQLCODE)||SQLERRM||'Program error contact support');
4944:
4945:
4946: END upgrade_geo_api;
4947:
5022: g_geo_phase_id := p_geo_phase_id;
5023: g_mode := p_mode;
5024:
5025:
5026: hr_utility.trace('Entering pay_us_geo_upd_pkg.update_ca_emp_info');
5027: hr_utility.trace('The phase id is: '||to_char(g_geo_phase_id));
5028:
5029: OPEN canada_emp_fed_tax_cur ;
5030: LOOP
5023: g_mode := p_mode;
5024:
5025:
5026: hr_utility.trace('Entering pay_us_geo_upd_pkg.update_ca_emp_info');
5027: hr_utility.trace('The phase id is: '||to_char(g_geo_phase_id));
5028:
5029: OPEN canada_emp_fed_tax_cur ;
5030: LOOP
5031: FETCH canada_emp_fed_tax_cur into canada_emp_fed_rec;
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,
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,
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,
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:
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: --
5334:
5335: g_geo_phase_id := p_geo_phase_id;
5336: g_mode := p_mode;
5337:
5338: hr_utility.trace('Entering pay_us_geo_upd_pkg. group_level_balance');
5339: hr_utility.trace('The phase id is: '||to_char(g_geo_phase_id));
5340:
5341: OPEN c_legislation_code ( p_start_payroll_action
5342: ,p_end_payroll_action) ;
5335: g_geo_phase_id := p_geo_phase_id;
5336: g_mode := p_mode;
5337:
5338: hr_utility.trace('Entering pay_us_geo_upd_pkg. group_level_balance');
5339: hr_utility.trace('The phase id is: '||to_char(g_geo_phase_id));
5340:
5341: OPEN c_legislation_code ( p_start_payroll_action
5342: ,p_end_payroll_action) ;
5343:
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,
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 ;
5413:
5414: end;
5415:
5416: END LOOP ;
5417: hr_utility.trace('Entering pay_us_geo_upd_pkg. group_level_balance - 7001');
5418:
5419: CLOSE group_level_bal_us ;
5420:
5421: else
5444: exception
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
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'
5454: -- AND pmod.county_code = group_level_bal_ca_rec.jurisdiction_code
5455: AND pmod.county_code = substr(group_level_bal_ca_rec.jurisdiction_code,1,2)
5456: AND pmod.patch_name = p_patch_name;
5457:
5458: hr_utility.trace('Entering pay_us_geo_upd_pkg. group_level_balance - 7003');
5459:
5460: IF G_MODE = 'UPGRADE' THEN
5461:
5462: UPDATE pay_run_balances
5466: -- AND jurisdiction_code = group_level_bal_ca_rec.jurisdiction_code
5467: AND substr(jurisdiction_code,1,2) =
5468: substr(group_level_bal_ca_rec.jurisdiction_code,1,2) ;
5469:
5470: hr_utility.trace('Entering pay_us_geo_upd_pkg. group_level_balance - 7004');
5471:
5472: -- COMMIT;
5473: END IF;
5474:
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,
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';
5502: WHEN OTHERS THEN
5503: l_error_message_text := to_char(SQLCODE)||SQLERRM||
5504: ' Program error contact support';
5505:
5506: hr_utility.trace('Entering pay_us_geo_upd_pkg. group_level_balance - 7005');
5507: hr_utility.trace('l_error_message_text - ' ||l_error_message_text);
5508:
5509:
5510: fnd_file.put_line(fnd_file.log, 'Exception update_ca_emp_info' );
5503: l_error_message_text := to_char(SQLCODE)||SQLERRM||
5504: ' Program error contact support';
5505:
5506: hr_utility.trace('Entering pay_us_geo_upd_pkg. group_level_balance - 7005');
5507: hr_utility.trace('l_error_message_text - ' ||l_error_message_text);
5508:
5509:
5510: fnd_file.put_line(fnd_file.log, 'Exception update_ca_emp_info' );
5511: fnd_file.put_line(fnd_file.log, 'sql error ' || sqlcode || ' - ' || substr(sqlerrm,1,80));
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;
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: