[Home] [Help]
132: AND pmod.process_type in ('UP','US','PU','D','SU')
133: AND pmod.patch_name = l_patch_name
134: AND ectr.assignment_id = paf.assignment_id
135: AND paf.person_id between stperson and endperson
136: AND NOT EXISTS (select 'Y' from PAY_US_GEO_UPDATE pugu
137: where pugu.assignment_id = ectr.assignment_id
138: and pugu.new_juri_code = pmod.state_code||'-'||pmod.county_code||'-'||pmod.new_city_code
139: and pugu.old_juri_code = ectr.jurisdiction_code
140: and pugu.table_value_id is null
409: l_patch_name pay_patch_status.patch_name%type;
410:
411: BEGIN
412:
413: hr_utility.set_location ('pay_us_geo_update.action_code', 1);
414:
415: open c_xfr_info (p_xfr_action_id);
416:
417: fetch c_xfr_info into l_payroll_action_id,
471: and patch_number = p_patch_number
472: and legislation_code = 'US';
473:
474:
475: -- Bug 3354053 -- Changed the cursor query to remove the FTS from pay_us_geo_update.
476: Cursor c_geo_upd (p_patch_id in number,
477: p_patch_status in varchar2) is
478: select 'x' from dual
479: where exists(select 'x' from pay_us_geo_update
475: -- Bug 3354053 -- Changed the cursor query to remove the FTS from pay_us_geo_update.
476: Cursor c_geo_upd (p_patch_id in number,
477: p_patch_status in varchar2) is
478: select 'x' from dual
479: where exists(select 'x' from pay_us_geo_update
480: where id = p_patch_id
481: and status = p_patch_status
482: and rownum < 2);
483:
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,
631: PERSON_ID,
632: TABLE_NAME,
633: TABLE_VALUE_ID,
649: 'UPGRADE',
650: p_status);
651:
652: ELSE
653: insert into PAY_US_GEO_UPDATE (ID,
654: ASSIGNMENT_ID,
655: PERSON_ID,
656: TABLE_NAME,
657: TABLE_VALUE_ID,
744: AND pmod.patch_name = p_patch_name
745: AND ectr.assignment_id = paf.assignment_id
746: AND pmod.city_name = nvl(p_city_name, pmod.city_name)
747: AND paf.assignment_id between P_ASSIGN_START and P_ASSIGN_END
748: AND NOT EXISTS (select 'Y' from PAY_US_GEO_UPDATE pugu
749: where pugu.assignment_id = ectr.assignment_id
750: and pugu.new_juri_code = pmod.state_code||'-'||pmod.county_code||'-'||pmod.new_city_code
751: and pugu.old_juri_code = ectr.jurisdiction_code
752: and pugu.table_value_id is null
800: FROM pay_us_emp_city_tax_rules_f puecf
801: WHERE puecf.jurisdiction_code = p_old_juri_code
802: AND puecf.assignment_id = p_assign_id
803: AND puecf.emp_city_tax_rule_id = p_city_tax_record_id
804: AND NOT EXISTS (select 'Y' from PAY_US_GEO_UPDATE pugu
805: where pugu.assignment_id = p_assign_id
806: and pugu.table_value_id = puecf.emp_city_tax_rule_id
807: and pugu.old_juri_code = p_old_juri_code
808: and pugu.table_name = 'PAY_US_EMP_CITY_TAX_RULES_F'
837: AND pev.input_value_id = piv.input_value_id
838: AND piv.name = 'Jurisdiction'
839: -- AND piv.legislation_code = 'US'
840: AND IS_US_OR_CA_LEGISLATION(piv.input_value_id) = piv.input_value_id
841: AND NOT EXISTS (select 'Y' from PAY_US_GEO_UPDATE pugu
842: where pugu.assignment_id = assign_id
843: and pugu.table_value_id = pev.element_entry_id
844: and pugu.old_juri_code = geocode
845: and pugu.table_name = 'PAY_ELEMENT_ENTRY_VALUES_F'
872: SELECT distinct prr.run_result_id,
873: prr.assignment_action_id, prr.jurisdiction_code
874: FROM pay_run_results prr
875: WHERE prr.assignment_action_id = assign_action_id
876: AND NOT EXISTS (select 'Y' from PAY_US_GEO_UPDATE pugu
877: where pugu.assignment_id = assign_id
878: and pugu.table_value_id = prr.run_result_id
879: and pugu.old_juri_code = prr.jurisdiction_code
880: and pugu.table_name = 'PAY_RUN_RESULTS'
927: AND fai.archive_item_id = faic.archive_item_id
928: AND faic.context = geocode
929: AND ffc.context_id = faic.context_id
930: AND ffc.context_name = 'JURISDICTION_CODE'
931: AND NOT EXISTS (select 'Y' from PAY_US_GEO_UPDATE pugu
932: where pugu.assignment_id = assign_id
933: and pugu.table_value_id = faic.archive_item_id
934: and pugu.old_juri_code = faic.context
935: and pugu.table_name = 'FF_ARCHIVE_ITEM_CONTEXTS'
958: and fai.archive_item_id = faic.archive_item_id
959: and faic.context = geocode
960: and ffc.context_id = faic.context_id
961: and ffc.context_name = 'JURISDICTION_CODE'
962: and not exists (select 'Y' from PAY_US_GEO_UPDATE pugu
963: where pugu.assignment_id = assign_id
964: and pugu.table_value_id = faic.archive_item_id
965: and pugu.old_juri_code = faic.context
966: and pugu.table_name = 'FF_ARCHIVE_ITEM_CONTEXTS'
988: AND pbcv.latest_balance_id = plb.latest_balance_id
989: AND pbcv.value = geocode
990: AND fcon.context_id = pbcv.context_id
991: AND fcon.context_name = 'JURISDICTION_CODE'
992: AND NOT EXISTS (select 'Y' from PAY_US_GEO_UPDATE pugu
993: where pugu.assignment_id = assign_id
994: and pugu.table_value_id = plb.latest_balance_id
995: and pugu.old_juri_code = geocode
996: and pugu.table_name = 'PAY_BALANCE_CONTEXT_VALUES'
1013: AND pbcv.latest_balance_id = plb.latest_balance_id
1014: AND pbcv.value = geocode
1015: AND fcon.context_id = pbcv.context_id
1016: AND fcon.context_name = 'JURISDICTION_CODE'
1017: AND NOT EXISTS (select 'Y' from PAY_US_GEO_UPDATE pugu
1018: where pugu.assignment_id = assign_id
1019: and pugu.table_value_id = plb.latest_balance_id
1020: and pugu.old_juri_code = geocode
1021: and pugu.table_name = 'PAY_BALANCE_CONTEXT_VALUES'
1041: AND pbcv.latest_balance_id = plb.latest_balance_id
1042: AND pbcv.value = geocode
1043: AND fcon.context_id = pbcv.context_id
1044: AND fcon.context_name = 'JURISDICTION_CODE'
1045: AND NOT EXISTS (select 'Y' from PAY_US_GEO_UPDATE pugu
1046: where pugu.assignment_id = assign_id
1047: and pugu.table_value_id = plb.latest_balance_id
1048: and pugu.old_juri_code = geocode
1049: and pugu.table_name = 'PAY_BALANCE_CONTEXT_VALUES'
1057:
1058: -- This cursor will check if a particular assignment is errored.
1059: CURSOR chk_assign_error_cur(p_assign_id NUMBER, p_new_juri_code VARCHAR2, p_old_juri_code VARCHAR2)
1060: IS
1061: SELECT 'Y' from PAY_US_GEO_UPDATE pugu
1062: WHERE pugu.assignment_id = p_assign_id
1063: AND pugu.process_mode = g_mode
1064: AND pugu.id = g_geo_phase_id
1065: AND pugu.table_name is null
1074: -- If it does then we will update the status to 'A' in the main procedure.
1075:
1076: CURSOR chk_assign_api_cur(p_assign_id NUMBER, p_new_juri_code VARCHAR2, p_old_juri_code VARCHAR2)
1077: IS select distinct 'Y'
1078: from pay_us_geo_update pugu
1079: where pugu.process_type in ('SU','US')
1080: and pugu.table_name is null
1081: and pugu.process_mode = g_mode
1082: and pugu.id = g_geo_phase_id
1159: IS select 'Y'
1160: FROM pay_balance_batch_lines
1161: WHERE jurisdiction_code = p_old_juri_code
1162: AND assignment_id = p_assign_id
1163: AND NOT EXISTS (select 'Y' from PAY_US_GEO_UPDATE pugu
1164: where pugu.assignment_id = p_assign_id
1165: and pugu.old_juri_code = p_old_juri_code
1166: and pugu.new_juri_code = p_new_juri_code
1167: and pugu.table_name = 'PAY_BALANCE_BATCH_LINES'
1254: IS select 'Y'
1255: FROM pay_run_balances
1256: WHERE jurisdiction_code = p_old_juri_code
1257: AND assignment_id = p_assign_id
1258: AND NOT EXISTS (select 'Y' from PAY_US_GEO_UPDATE pugu
1259: where pugu.assignment_id = p_assign_id
1260: and pugu.old_juri_code = p_old_juri_code
1261: and pugu.new_juri_code = p_new_juri_code
1262: and pugu.table_name = 'PAY_RUN_BALANCES'
1358: city_code = p_new_city_code
1359: WHERE jurisdiction_code = p_old_juri_code
1360: AND assignment_id = p_assign_id
1361: AND emp_city_tax_rule_id = p_city_tax_record_id
1362: AND NOT EXISTS (select 'Y' from PAY_US_GEO_UPDATE pugu
1363: where pugu.assignment_id = p_assign_id
1364: and pugu.table_value_id = p_city_tax_record_id
1365: and pugu.old_juri_code = p_old_juri_code
1366: and pugu.table_name = 'PAY_US_EMP_CITY_TAX_RULES_F'
1502: piv.name = 'jd_rs' OR
1503: piv.name = 'jd_wk')
1504: -- AND piv.legislation_code = 'US')
1505: AND IS_US_OR_CA_LEGISLATION(piv.input_value_id) = piv.input_value_id )
1506: AND NOT EXISTS (select 'Y' from PAY_US_GEO_UPDATE pugu
1507: where pugu.assignment_id = p_assign_id
1508: and pugu.table_value_id = prv.run_result_id
1509: and pugu.old_juri_code = p_old_juri_code
1510: and pugu.table_name = 'PAY_RUN_RESULT_VALUES'
1519: FROM pay_run_results prr
1520: WHERE prr.jurisdiction_code = p_new_juri_code
1521: AND prr.run_result_id = p_run_result_id
1522: AND prr.assignment_action_id = p_assign_act_id
1523: AND NOT EXISTS (select 'Y' from PAY_US_GEO_UPDATE pugu
1524: where pugu.assignment_id = p_assign_id
1525: and pugu.table_value_id = prr.run_result_id
1526: and pugu.old_juri_code = p_old_juri_code
1527: and pugu.table_name = 'PAY_RUN_RESULTS'
2389: FROM pay_us_states pus,
2390: pay_us_emp_state_tax_rules_f pusf
2391: WHERE pusf.assignment_id = p_assign_id
2392: AND pusf.state_code = pus.state_abbrev
2393: AND NOT EXISTS (select 'Y' from PAY_US_GEO_UPDATE pugu
2394: where pugu.table_name = 'PAY_ELEMENT_ENTRY_VALUES_F'
2395: and pugu.process_type = 'PERCENTAGE_OVER_100'
2396: and pugu.assignment_id = p_assign_id
2397: and pugu.process_mode = g_mode
2599: hr_utility.trace('The main new juri code id is: '||main_new_juri_code);
2600: hr_utility.trace('The main person id is: '||to_char(main_person_id));
2601: hr_utility.trace('The city name is: '||p_city_name);
2602:
2603: -- We first insert a row into PAY_US_GEO_UPDATE to state that we are processing this assignment
2604: -- Our concern is how do we track an assignment that has errored. So we will start by creating
2605: -- a row for the assignment with a p_status of 'U'. Then at the end of the loop we will
2606: -- change the p_status to 'C' before commiting
2607:
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:
2623: ELSE
2624: hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geocodes',40);
2625:
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:
2629: write_message(
2630: p_proc_type => main_proc_type,
2631: p_person_id => main_person_id,
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
3040: AND old_juri_code = main_old_juri_code
3041: AND new_juri_code = main_new_juri_code
3044: AND status = 'P'
3045: AND process_type = main_proc_type;
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';
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
3055: SET status = 'C', description = null
3056: WHERE assignment_id = main_assign_id
3057: AND old_juri_code = main_old_juri_code
3058: AND new_juri_code = main_new_juri_code
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
3096: AND old_juri_code = main_old_juri_code
3097: AND new_juri_code = main_new_juri_code
3190: WHERE ptax.jurisdiction_code = pmod.state_code||'-000-'||pmod.old_city_code
3191: AND pmod.process_type in ('UP','RP')
3192: AND pmod.patch_name = p_patch_name
3193: AND substr(ptax.jurisdiction_code,8,4) <> '0000'
3194: AND NOT EXISTS (select 'Y' from PAY_US_GEO_UPDATE pugu
3195: where pugu.table_name = 'PAY_TAXABILITY_RULES'
3196: and pugu.new_juri_code = ptax.jurisdiction_code
3197: and pugu.process_mode = g_mode
3198: and pugu.process_type = g_process_type
3385: AND pmod.old_city_code = substr(hoi.org_information1,8,4)
3386: AND pmod.process_type in ('UP','PU','RP')
3387: AND pmod.patch_name = p_patch_name
3388: AND hoi.org_information_context = 'Local Tax Rules'
3389: AND NOT EXISTS (select 'Y' from PAY_US_GEO_UPDATE pugu
3390: where pugu.table_name = 'HR_ORGANIZATION_INFORMATION'
3391: and pugu.new_juri_code = hoi.org_information1
3392: and pugu.process_mode = g_mode
3393: and pugu.process_type = g_process_type
3564: P_MODE VARCHAR2,
3565: P_CITY_NAME VARCHAR2)
3566: IS
3567:
3568: -- Bug 3319878 -- Changed the query to remove FTS from PAY_US_GEO_UPDATE
3569:
3570: CURSOR chk_last_api(p_geo_phase_id NUMBER, p_mode VARCHAR2) IS
3571: SELECT 'Y'
3572: FROM dual
3569:
3570: CURSOR chk_last_api(p_geo_phase_id NUMBER, p_mode VARCHAR2) IS
3571: SELECT 'Y'
3572: FROM dual
3573: WHERE exists (SELECT /*+index(pugu PAY_US_GEO_UPDATE_N2) */ 'Y'
3574: FROM PAY_US_GEO_UPDATE pugu
3575: WHERE pugu.id = p_geo_phase_id
3576: AND pugu.process_mode = p_mode
3577: AND pugu.table_name is null
3570: CURSOR chk_last_api(p_geo_phase_id NUMBER, p_mode VARCHAR2) IS
3571: SELECT 'Y'
3572: FROM dual
3573: WHERE exists (SELECT /*+index(pugu PAY_US_GEO_UPDATE_N2) */ 'Y'
3574: FROM PAY_US_GEO_UPDATE pugu
3575: WHERE pugu.id = p_geo_phase_id
3576: AND pugu.process_mode = p_mode
3577: AND pugu.table_name is null
3578: AND pugu.table_value_id is null
3938: and pmod.old_city_code = substr(prb.jurisdiction_code,8,4)
3939: and pmod.process_type in ('PU', 'UP')
3940: and pmod.patch_name = p_patch_name;
3941:
3942: /* and NOT EXISTS (select 'Y' from PAY_US_GEO_UPDATE pugu
3943: where pugu.process_type = g_process_type
3944: and pugu.process_mode = g_mode
3945: and pugu.assignment_id is null
3946: and pugu.old_juri_code = prb.jurisdiction_code
3969: and pmod.state_code = substr(prb.jurisdiction_code,1,2)
3970: and pmod.county_code = substr(prb.jurisdiction_code,4,3)
3971: and pmod.old_city_code = substr(prb.jurisdiction_code,8,4)
3972: and pmod.patch_name = p_patch_name
3973: and NOT EXISTS (select 'Y' from PAY_US_GEO_UPDATE pugu
3974: where pugu.process_type = g_process_type
3975: and pugu.process_mode = g_mode
3976: and pugu.assignment_id is null
3977: and pugu.old_juri_code = pmod.state_code || '-' || pmod.county_code || '-' || pmod.old_city_code --prb.jurisdiction_code
3994: and pmod.process_type in ('PU', 'UP')
3995: and pmod.patch_name = p_patch_name;
3996:
3997: /*
3998: and NOT EXISTS (select 'Y' from PAY_US_GEO_UPDATE pugu
3999: where pugu.old_juri_code = prb.jurisdiction_code
4000: and pugu.assignment_id is null
4001: and pugu.person_id = prb.run_balance_id
4002: and pugu.table_name = 'PAY_RUN_BALANCES'
4024: and pbd.database_item_suffix like '%JD%'
4025: and pmod.state_code = 'CA'
4026: and pmod.county_code = substr(prb.jurisdiction_code,1,2)
4027: and pmod.patch_name = p_patch_name
4028: and NOT EXISTS (select 'Y' from PAY_US_GEO_UPDATE pugu
4029: where pugu.old_juri_code = prb.jurisdiction_code
4030: and pugu.assignment_id is null
4031: and pugu.person_id = prb.payroll_action_id
4032: and pugu.table_name = 'PAY_RUN_BALANCES'
4088: l_row_updated := 'N';
4089:
4090: select 'Y'
4091: into l_row_updated
4092: from PAY_US_GEO_UPDATE pugu
4093: where pugu.old_juri_code = group_level_bal_us_rec.jurisdiction_code
4094: and pugu.assignment_id is null
4095: and pugu.person_id = group_level_bal_us_rec.run_balance_id
4096: and pugu.table_name = 'PAY_RUN_BALANCES'
4159: l_row_updated := 'N';
4160:
4161: select 'Y'
4162: into l_row_updated
4163: from PAY_US_GEO_UPDATE pugu
4164: where pugu.old_juri_code = group_level_bal_ca_rec.jurisdiction_code
4165: and pugu.assignment_id is null
4166: and pugu.person_id = group_level_bal_ca_rec.run_balance_id
4167: and pugu.table_name = 'PAY_RUN_BALANCES'