[Home] [Help]
294: AND ectr.assignment_id = paf.assignment_id
295: AND paf.person_id between stperson and endperson
296: AND get_city(paf.person_id, paf.location_id, ectr.state_code,
297: ectr.county_code,ectr.city_code,pmod.city_name,l_patch_name,pmod.process_type) = pmod.city_name
298: AND NOT EXISTS (select 'Y' from PAY_US_GEO_UPDATE pugu
299: where pugu.assignment_id = ectr.assignment_id
300: and pugu.new_juri_code = pmod.state_code||'-'||pmod.county_code||'-'||pmod.new_city_code
301: and pugu.old_juri_code = ectr.jurisdiction_code
302: and pugu.table_value_id is null
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,
670: and patch_number = p_patch_number
671: and legislation_code = 'US';
672:
673:
674: -- Bug 3354053 -- Changed the cursor query to remove the FTS from pay_us_geo_update.
675: Cursor c_geo_upd (p_patch_id in number,
676: p_patch_status in varchar2) is
677: select 'x' from dual
678: where exists(select 'x' from pay_us_geo_update
674: -- Bug 3354053 -- Changed the cursor query to remove the FTS from pay_us_geo_update.
675: Cursor c_geo_upd (p_patch_id in number,
676: p_patch_status in varchar2) is
677: select 'x' from dual
678: where exists(select 'x' from pay_us_geo_update
679: where id = p_patch_id
680: and status = p_patch_status
681: and rownum < 2);
682:
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,
835: PERSON_ID,
836: TABLE_NAME,
837: TABLE_VALUE_ID,
856: p_description);
857:
858: ELSE
859: /*Added field p_description for Bug#10060041*/
860: insert into PAY_US_GEO_UPDATE (ID,
861: ASSIGNMENT_ID,
862: PERSON_ID,
863: TABLE_NAME,
864: TABLE_VALUE_ID,
953: AND pmod.patch_name = p_patch_name
954: AND ectr.assignment_id = paf.assignment_id
955: AND pmod.city_name = nvl(p_city_name, pmod.city_name)
956: AND paf.assignment_id between P_ASSIGN_START and P_ASSIGN_END
957: AND NOT EXISTS (select 'Y' from PAY_US_GEO_UPDATE pugu
958: where pugu.assignment_id = ectr.assignment_id
959: and pugu.new_juri_code = pmod.state_code||'-'||pmod.county_code||'-'||pmod.new_city_code
960: and pugu.old_juri_code = ectr.jurisdiction_code
961: and pugu.table_value_id is null
1009: FROM pay_us_emp_city_tax_rules_f puecf
1010: WHERE puecf.jurisdiction_code = p_old_juri_code
1011: AND puecf.assignment_id = p_assign_id
1012: AND puecf.emp_city_tax_rule_id = p_city_tax_record_id
1013: AND NOT EXISTS (select 'Y' from PAY_US_GEO_UPDATE pugu
1014: where pugu.assignment_id = p_assign_id
1015: and pugu.table_value_id = puecf.emp_city_tax_rule_id
1016: and pugu.old_juri_code = p_old_juri_code
1017: and pugu.table_name = 'PAY_US_EMP_CITY_TAX_RULES_F'
1046: AND pev.input_value_id = piv.input_value_id
1047: AND piv.name = 'Jurisdiction'
1048: -- AND piv.legislation_code = 'US'
1049: AND IS_US_OR_CA_LEGISLATION(piv.input_value_id) = piv.input_value_id
1050: AND NOT EXISTS (select 'Y' from PAY_US_GEO_UPDATE pugu
1051: where pugu.assignment_id = assign_id
1052: and pugu.table_value_id = pev.element_entry_id
1053: and pugu.old_juri_code = geocode
1054: and pugu.table_name = 'PAY_ELEMENT_ENTRY_VALUES_F'
1081: SELECT distinct prr.run_result_id,
1082: prr.assignment_action_id, prr.jurisdiction_code
1083: FROM pay_run_results prr
1084: WHERE prr.assignment_action_id = assign_action_id
1085: AND NOT EXISTS (select 'Y' from PAY_US_GEO_UPDATE pugu
1086: where pugu.assignment_id = assign_id
1087: and pugu.table_value_id = prr.run_result_id
1088: and pugu.old_juri_code = prr.jurisdiction_code
1089: and pugu.table_name = 'PAY_RUN_RESULTS'
1136: AND fai.archive_item_id = faic.archive_item_id
1137: AND faic.context = geocode
1138: AND ffc.context_id = faic.context_id
1139: AND ffc.context_name = 'JURISDICTION_CODE'
1140: AND NOT EXISTS (select 'Y' from PAY_US_GEO_UPDATE pugu
1141: where pugu.assignment_id = assign_id
1142: and pugu.table_value_id = faic.archive_item_id
1143: and pugu.old_juri_code = faic.context
1144: and pugu.table_name = 'FF_ARCHIVE_ITEM_CONTEXTS'
1167: and fai.archive_item_id = faic.archive_item_id
1168: and faic.context = geocode
1169: and ffc.context_id = faic.context_id
1170: and ffc.context_name = 'JURISDICTION_CODE'
1171: and not exists (select 'Y' from PAY_US_GEO_UPDATE pugu
1172: where pugu.assignment_id = assign_id
1173: and pugu.table_value_id = faic.archive_item_id
1174: and pugu.old_juri_code = faic.context
1175: and pugu.table_name = 'FF_ARCHIVE_ITEM_CONTEXTS'
1197: AND pbcv.latest_balance_id = plb.latest_balance_id
1198: AND pbcv.value = geocode
1199: AND fcon.context_id = pbcv.context_id
1200: AND fcon.context_name = 'JURISDICTION_CODE'
1201: AND NOT EXISTS (select 'Y' from PAY_US_GEO_UPDATE pugu
1202: where pugu.assignment_id = assign_id
1203: and pugu.table_value_id = plb.latest_balance_id
1204: and pugu.old_juri_code = geocode
1205: and pugu.table_name = 'PAY_BALANCE_CONTEXT_VALUES'
1222: AND pbcv.latest_balance_id = plb.latest_balance_id
1223: AND pbcv.value = geocode
1224: AND fcon.context_id = pbcv.context_id
1225: AND fcon.context_name = 'JURISDICTION_CODE'
1226: AND NOT EXISTS (select 'Y' from PAY_US_GEO_UPDATE pugu
1227: where pugu.assignment_id = assign_id
1228: and pugu.table_value_id = plb.latest_balance_id
1229: and pugu.old_juri_code = geocode
1230: and pugu.table_name = 'PAY_BALANCE_CONTEXT_VALUES'
1250: AND pbcv.latest_balance_id = plb.latest_balance_id
1251: AND pbcv.value = geocode
1252: AND fcon.context_id = pbcv.context_id
1253: AND fcon.context_name = 'JURISDICTION_CODE'
1254: AND NOT EXISTS (select 'Y' from PAY_US_GEO_UPDATE pugu
1255: where pugu.assignment_id = assign_id
1256: and pugu.table_value_id = plb.latest_balance_id
1257: and pugu.old_juri_code = geocode
1258: and pugu.table_name = 'PAY_BALANCE_CONTEXT_VALUES'
1266:
1267: -- This cursor will check if a particular assignment is errored.
1268: CURSOR chk_assign_error_cur(p_assign_id NUMBER, p_new_juri_code VARCHAR2, p_old_juri_code VARCHAR2)
1269: IS
1270: SELECT 'Y' from PAY_US_GEO_UPDATE pugu
1271: WHERE pugu.assignment_id = p_assign_id
1272: AND pugu.process_mode = g_mode
1273: AND pugu.id = g_geo_phase_id
1274: AND pugu.table_name is null
1283: -- If it does then we will update the status to 'A' in the main procedure.
1284:
1285: CURSOR chk_assign_api_cur(p_assign_id NUMBER, p_new_juri_code VARCHAR2, p_old_juri_code VARCHAR2)
1286: IS select distinct 'Y'
1287: from pay_us_geo_update pugu
1288: where pugu.process_type in ('SU','US')
1289: and pugu.table_name is null
1290: and pugu.process_mode = g_mode
1291: and pugu.id = g_geo_phase_id
1368: IS select 'Y'
1369: FROM pay_balance_batch_lines
1370: WHERE jurisdiction_code = p_old_juri_code
1371: AND assignment_id = p_assign_id
1372: AND NOT EXISTS (select 'Y' from PAY_US_GEO_UPDATE pugu
1373: where pugu.assignment_id = p_assign_id
1374: and pugu.old_juri_code = p_old_juri_code
1375: and pugu.new_juri_code = p_new_juri_code
1376: and pugu.table_name = 'PAY_BALANCE_BATCH_LINES'
1463: IS select 'Y'
1464: FROM pay_run_balances
1465: WHERE jurisdiction_code = p_old_juri_code
1466: AND assignment_id = p_assign_id
1467: AND NOT EXISTS (select 'Y' from PAY_US_GEO_UPDATE pugu
1468: where pugu.assignment_id = p_assign_id
1469: and pugu.old_juri_code = p_old_juri_code
1470: and pugu.new_juri_code = p_new_juri_code
1471: and pugu.table_name = 'PAY_RUN_BALANCES'
1596: city_code = p_new_city_code
1597: WHERE jurisdiction_code = p_old_juri_code
1598: AND assignment_id = p_assign_id
1599: AND emp_city_tax_rule_id = p_city_tax_record_id
1600: AND NOT EXISTS (select 'Y' from PAY_US_GEO_UPDATE pugu
1601: where pugu.assignment_id = p_assign_id
1602: and pugu.table_value_id = p_city_tax_record_id
1603: and pugu.old_juri_code = p_old_juri_code
1604: and pugu.table_name = 'PAY_US_EMP_CITY_TAX_RULES_F'
1740: piv.name = 'jd_rs' OR
1741: piv.name = 'jd_wk')
1742: -- AND piv.legislation_code = 'US')
1743: AND IS_US_OR_CA_LEGISLATION(piv.input_value_id) = piv.input_value_id )
1744: AND NOT EXISTS (select 'Y' from PAY_US_GEO_UPDATE pugu
1745: where pugu.assignment_id = p_assign_id
1746: and pugu.table_value_id = prv.run_result_id
1747: and pugu.old_juri_code = p_old_juri_code
1748: and pugu.table_name = 'PAY_RUN_RESULT_VALUES'
1757: FROM pay_run_results prr
1758: WHERE prr.jurisdiction_code = p_new_juri_code
1759: AND prr.run_result_id = p_run_result_id
1760: AND prr.assignment_action_id = p_assign_act_id
1761: AND NOT EXISTS (select 'Y' from PAY_US_GEO_UPDATE pugu
1762: where pugu.assignment_id = p_assign_id
1763: and pugu.table_value_id = prr.run_result_id
1764: and pugu.old_juri_code = p_old_juri_code
1765: and pugu.table_name = 'PAY_RUN_RESULTS'
2682: FROM pay_us_states pus,
2683: pay_us_emp_state_tax_rules_f pusf
2684: WHERE pusf.assignment_id = p_assign_id
2685: AND pusf.state_code = pus.state_abbrev
2686: AND NOT EXISTS (select 'Y' from PAY_US_GEO_UPDATE pugu
2687: where pugu.table_name = 'PAY_ELEMENT_ENTRY_VALUES_F'
2688: and pugu.process_type = 'PERCENTAGE_OVER_100'
2689: and pugu.assignment_id = p_assign_id
2690: and pugu.process_mode = g_mode
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
2899: -- change the p_status to 'C' before commiting
2900:
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:
2916: ELSE
2917: hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geocodes',40);
2918:
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:
2922: write_message(
2923: p_proc_type => main_proc_type,
2924: p_person_id => main_person_id,
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
3333: AND old_juri_code = main_old_juri_code
3334: AND new_juri_code = main_new_juri_code
3337: AND status = 'P'
3338: AND process_type = main_proc_type;
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';
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
3348: SET status = 'C', description = null
3349: WHERE assignment_id = main_assign_id
3350: AND old_juri_code = main_old_juri_code
3351: AND new_juri_code = main_new_juri_code
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
3389: AND old_juri_code = main_old_juri_code
3390: AND new_juri_code = main_new_juri_code
3483: WHERE ptax.jurisdiction_code = pmod.state_code||'-000-'||pmod.old_city_code
3484: AND pmod.process_type in ('UP','RP')
3485: AND pmod.patch_name = p_patch_name
3486: AND substr(ptax.jurisdiction_code,8,4) <> '0000'
3487: AND NOT EXISTS (select 'Y' from PAY_US_GEO_UPDATE pugu
3488: where pugu.table_name = 'PAY_TAXABILITY_RULES'
3489: and pugu.new_juri_code = ptax.jurisdiction_code
3490: and pugu.process_mode = g_mode
3491: and pugu.process_type = g_process_type
4007: ORDER BY patch_name;
4008:
4009: l_city_name_change get_city_name_changes%ROWTYPE;
4010: l_county_name pay_us_counties.county_name%TYPE;
4011: l_jurisdiction_code pay_us_geo_update.old_juri_code%TYPE;
4012:
4013: BEGIN
4014:
4015: g_geo_phase_id := p_geo_phase_id;
4036: OPEN get_county_name(l_city_name_change.state_code,l_city_name_change.county_code);
4037: FETCH get_county_name INTO l_county_name;
4038: CLOSE get_county_name;
4039:
4040: INSERT INTO pay_us_geo_update
4041: (
4042: id,
4043: assignment_id,
4044: person_id,
4084: AND country = l_city_name_change.country;
4085:
4086: IF l_city_name_change.country = 'US' THEN
4087:
4088: INSERT INTO pay_us_geo_update
4089: (
4090: id,
4091: assignment_id,
4092: person_id,
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,
4134: assignment_id,
4135: person_id,
4175: AND country = l_city_name_change.country;
4176:
4177: IF l_city_name_change.country = 'US' THEN
4178:
4179: INSERT INTO pay_us_geo_update
4180: (
4181: id,
4182: assignment_id,
4183: person_id,
4214: WHERE loc_information18 = l_city_name_change.old_city_name
4215: AND loc_information19 = l_county_name
4216: AND loc_information17 = l_city_name_change.state_abbrev;
4217:
4218: INSERT INTO pay_us_geo_update
4219: (
4220: id,
4221: assignment_id,
4222: person_id,
4266: WHILE ( get_new_county_name%FOUND )
4267:
4268: LOOP
4269:
4270: INSERT INTO pay_us_geo_update
4271: (
4272: id,
4273: assignment_id,
4274: person_id,
4303: AND country = l_city_name_change.country;
4304:
4305: IF l_city_name_change.country = 'US' THEN
4306:
4307: INSERT INTO pay_us_geo_update
4308: (
4309: id,
4310: assignment_id,
4311: person_id,
4338: AND add_information17 = l_city_name_change.state_abbrev;
4339:
4340: END IF;
4341:
4342: INSERT INTO pay_us_geo_update
4343: (
4344: id,
4345: assignment_id,
4346: person_id,
4375: AND country = l_city_name_change.country;
4376:
4377: IF l_city_name_change.country = 'US' THEN
4378:
4379: INSERT INTO pay_us_geo_update
4380: (
4381: id,
4382: assignment_id,
4383: person_id,
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;
4425:
4426: INSERT INTO pay_us_geo_update
4427: (
4428: id,
4429: assignment_id,
4430: person_id,
4459: AND country = l_city_name_change.country;
4460:
4461: IF l_city_name_change.country = 'US' THEN
4462:
4463: INSERT INTO pay_us_geo_update
4464: (
4465: id,
4466: assignment_id,
4467: person_id,
4494: AND add_information17 = l_city_name_change.state_abbrev;
4495:
4496: END IF;
4497:
4498: INSERT INTO pay_us_geo_update
4499: (
4500: id,
4501: assignment_id,
4502: person_id,
4531: AND country = l_city_name_change.country;
4532:
4533: IF l_city_name_change.country = 'US' THEN
4534:
4535: INSERT INTO pay_us_geo_update
4536: (
4537: id,
4538: assignment_id,
4539: person_id,
4564: WHERE loc_information18 = l_city_name_change.old_city_name
4565: AND loc_information19 = l_county_name
4566: AND loc_information17 = l_city_name_change.state_abbrev;
4567:
4568: INSERT INTO pay_us_geo_update
4569: (
4570: id,
4571: assignment_id,
4572: person_id,
4657: AND pmod.old_city_code = substr(hoi.org_information1,8,4)
4658: AND pmod.process_type in ('UP','PU','RP')
4659: AND pmod.patch_name = p_patch_name
4660: AND hoi.org_information_context = 'Local Tax Rules'
4661: AND NOT EXISTS (select 'Y' from PAY_US_GEO_UPDATE pugu
4662: where pugu.table_name = 'HR_ORGANIZATION_INFORMATION'
4663: and pugu.new_juri_code = hoi.org_information1
4664: and pugu.process_mode = g_mode
4665: and pugu.process_type = g_process_type
4836: P_MODE VARCHAR2,
4837: P_CITY_NAME VARCHAR2)
4838: IS
4839:
4840: -- Bug 3319878 -- Changed the query to remove FTS from PAY_US_GEO_UPDATE
4841:
4842: CURSOR chk_last_api(p_geo_phase_id NUMBER, p_mode VARCHAR2) IS
4843: SELECT 'Y'
4844: FROM dual
4841:
4842: CURSOR chk_last_api(p_geo_phase_id NUMBER, p_mode VARCHAR2) IS
4843: SELECT 'Y'
4844: FROM dual
4845: WHERE exists (SELECT /*+index(pugu PAY_US_GEO_UPDATE_N2) */ 'Y'
4846: FROM PAY_US_GEO_UPDATE pugu
4847: WHERE pugu.id = p_geo_phase_id
4848: AND pugu.process_mode = p_mode
4849: AND pugu.table_name is null
4842: CURSOR chk_last_api(p_geo_phase_id NUMBER, p_mode VARCHAR2) IS
4843: SELECT 'Y'
4844: FROM dual
4845: WHERE exists (SELECT /*+index(pugu PAY_US_GEO_UPDATE_N2) */ 'Y'
4846: FROM PAY_US_GEO_UPDATE pugu
4847: WHERE pugu.id = p_geo_phase_id
4848: AND pugu.process_mode = p_mode
4849: AND pugu.table_name is null
4850: AND pugu.table_value_id is null
5210: and pmod.old_city_code = substr(prb.jurisdiction_code,8,4)
5211: and pmod.process_type in ('PU', 'UP')
5212: and pmod.patch_name = p_patch_name;
5213:
5214: /* and NOT EXISTS (select 'Y' from PAY_US_GEO_UPDATE pugu
5215: where pugu.process_type = g_process_type
5216: and pugu.process_mode = g_mode
5217: and pugu.assignment_id is null
5218: and pugu.old_juri_code = prb.jurisdiction_code
5241: and pmod.state_code = substr(prb.jurisdiction_code,1,2)
5242: and pmod.county_code = substr(prb.jurisdiction_code,4,3)
5243: and pmod.old_city_code = substr(prb.jurisdiction_code,8,4)
5244: and pmod.patch_name = p_patch_name
5245: and NOT EXISTS (select 'Y' from PAY_US_GEO_UPDATE pugu
5246: where pugu.process_type = g_process_type
5247: and pugu.process_mode = g_mode
5248: and pugu.assignment_id is null
5249: and pugu.old_juri_code = pmod.state_code || '-' || pmod.county_code || '-' || pmod.old_city_code --prb.jurisdiction_code
5266: and pmod.process_type in ('PU', 'UP')
5267: and pmod.patch_name = p_patch_name;
5268:
5269: /*
5270: and NOT EXISTS (select 'Y' from PAY_US_GEO_UPDATE pugu
5271: where pugu.old_juri_code = prb.jurisdiction_code
5272: and pugu.assignment_id is null
5273: and pugu.person_id = prb.run_balance_id
5274: and pugu.table_name = 'PAY_RUN_BALANCES'
5296: and pbd.database_item_suffix like '%JD%'
5297: and pmod.state_code = 'CA'
5298: and pmod.county_code = substr(prb.jurisdiction_code,1,2)
5299: and pmod.patch_name = p_patch_name
5300: and NOT EXISTS (select 'Y' from PAY_US_GEO_UPDATE pugu
5301: where pugu.old_juri_code = prb.jurisdiction_code
5302: and pugu.assignment_id is null
5303: and pugu.person_id = prb.payroll_action_id
5304: and pugu.table_name = 'PAY_RUN_BALANCES'
5360: l_row_updated := 'N';
5361:
5362: select 'Y'
5363: into l_row_updated
5364: from PAY_US_GEO_UPDATE pugu
5365: where pugu.old_juri_code = group_level_bal_us_rec.jurisdiction_code
5366: and pugu.assignment_id is null
5367: and pugu.person_id = group_level_bal_us_rec.run_balance_id
5368: and pugu.table_name = 'PAY_RUN_BALANCES'
5431: l_row_updated := 'N';
5432:
5433: select 'Y'
5434: into l_row_updated
5435: from PAY_US_GEO_UPDATE pugu
5436: where pugu.old_juri_code = group_level_bal_ca_rec.jurisdiction_code
5437: and pugu.assignment_id is null
5438: and pugu.person_id = group_level_bal_ca_rec.run_balance_id
5439: and pugu.table_name = 'PAY_RUN_BALANCES'