[Home] [Help]
64: p_county_name pay_us_counties.county_name%TYPE;
65:
66: cursor c_county_name is
67: select city_name
68: from pay_us_modified_geocodes
69: where patch_name = p_patch_name
70: and process_type = 'CN'
71: and state_code = p_state_code
72: and county_code = p_county_code;
124: and pucn.city_code = p_city_code
125: and pucn.city_name = p_emp_city_name
126: and not exists
127: (select null
128: from pay_us_modified_geocodes
129: where patch_name = p_patch_name
130: and new_city_code = p_city_code
131: and old_city_code <> p_city_code)
132: union
131: and old_city_code <> p_city_code)
132: union
133: select pucn.city_name
134: from pay_us_city_names pucn,
135: pay_us_modified_geocodes pumg
136: where pucn.state_code = p_state_code
137: and pucn.county_code = p_county_code
138: and pucn.city_name = p_emp_city_name
139: and pumg.state_code = pucn.state_code
228: from pay_payroll_actions ppa
229: where ppa.payroll_action_id = pactid;
230:
231: sqlstr := ' select distinct paf.person_id
232: from pay_us_modified_geocodes mg,
233: pay_us_emp_city_tax_rules_f tr,
234: per_all_assignments_f paf,
235: pay_us_states pus
236: where mg.patch_name = '''||ln_upgrade_patch||'''
283:
284: SELECT distinct ectr.assignment_id
285: FROM per_all_assignments_f paf,
286: pay_us_emp_city_tax_rules_f ectr,
287: pay_us_modified_geocodes pmod
288: WHERE pmod.state_code = ectr.state_code
289: AND pmod.county_code = ectr.county_code
290: AND pmod.new_county_code is null
291: AND pmod.old_city_code = ectr.city_code
308:
309: SELECT distinct pac.assignment_id
310: FROM per_all_assignments_f paf,
311: pay_action_contexts pac,
312: pay_us_modified_geocodes pmod
313: WHERE pmod.state_code = 'CA'
314: AND pmod.county_code = pac.context_value
315: AND pac.context_id in (select context_id
316: from ff_contexts
319: AND pac.assignment_id = paf.assignment_id
320: AND paf.person_id between stperson and endperson ;
321:
322:
323: /* Changing the hint to use index PAY_US_MODIFIED_GEOCODES_N1 */
324: CURSOR c_actions_run_bal
325: (
326: pactid number,
327: p_balance_load_date date
912: pmod.state_code||'-'||pmod.county_code||'-'
913: ||pmod.new_city_code jd_code,
914: paf.person_id
915: FROM per_assignments_f paf,
916: pay_us_modified_geocodes pmod,
917: per_assignment_extra_info paei
918: WHERE paei.information_type = 'LOCALITY'
919: AND substr(paei.aei_information2,8,4) <> '0000'
920: AND pmod.city_name = paei.aei_information13
943: pmod.state_code||'-'||pmod.county_code||'-'||pmod.new_city_code jd_code,
944: paf.person_id, pmod.new_city_code, pmod.process_type, ectr.emp_city_tax_rule_id
945: FROM per_all_assignments_f paf,
946: pay_us_emp_city_tax_rules_f ectr,
947: pay_us_modified_geocodes pmod
948: WHERE pmod.state_code = ectr.state_code
949: AND pmod.county_code = ectr.county_code
950: AND pmod.new_county_code is null
951: AND pmod.old_city_code = ectr.city_code
971: paf.person_id, pmod.new_city_code, pmod.process_type,
972: pac.context_id
973: FROM per_all_assignments_f paf,
974: pay_action_contexts pac,
975: pay_us_modified_geocodes pmod
976: WHERE pmod.state_code = 'CA'
977: AND pmod.county_code = pac.context_value
978: AND pac.context_id in (select context_id
979: from ff_contexts
1114:
1115:
1116: --Retrieve all affected rows in the ff_archive_item_contexts table.
1117: --This cursor will check for a specific geocode that is passed in.
1118: --The passed in geocode will be the old one from pay_us_modified_geocodes.
1119: --We are joining with the archive item id, so we don't need this logic
1120: --in the procedure archive_items.
1121:
1122: /* CURSOR fac_cur(assign_id NUMBER, geocode VARCHAR2) IS
1293: and pugu.table_name is null
1294: and pugu.table_value_id is null
1295: and pugu.old_juri_code = p_old_juri_code
1296: and pugu.new_juri_code = p_new_juri_code
1297: and NOT EXISTS (select 'Y' from pay_us_modified_geocodes pmod
1298: where pmod.state_code = substr(pugu.new_juri_code,1,2)
1299: and pmod.county_code = substr(pugu.new_juri_code,4,3)
1300: and pmod.old_city_code = substr(pugu.old_juri_code,8)
1301: and pmod.new_city_code = substr(pugu.new_juri_code,8)
2468: ld_eff_end_date date;
2469: ln_state_code pay_us_emp_state_tax_rules_f.state_code%TYPE;
2470: ln_county_code pay_us_emp_county_tax_rules_f.county_code%TYPE;
2471: ln_city_code pay_us_emp_city_tax_rules_f.city_code%TYPE;
2472: ln_old_city_code pay_us_modified_geocodes.old_city_code%TYPE;
2473:
2474: ln_business_group_id number;
2475: ln_check number;
2476: ln_emp_city_tax_rule_id number;
3473:
3474: --Bug 3319878 -- Changed the cursor query to reduce cost.
3475: --Bug 5042715 -- Added hints to reduce cost.
3476: CURSOR ptax_cur IS
3477: SELECT /*+index( pmod PAY_US_MODIFIED_GEOCODES_N2 ,
3478: ptax PAY_TAXABILITY_RULES_UK)
3479: use_nl(pmod ptax)*/
3480: distinct ptax.jurisdiction_code
3481: FROM pay_us_modified_geocodes pmod,
3477: SELECT /*+index( pmod PAY_US_MODIFIED_GEOCODES_N2 ,
3478: ptax PAY_TAXABILITY_RULES_UK)
3479: use_nl(pmod ptax)*/
3480: distinct ptax.jurisdiction_code
3481: FROM pay_us_modified_geocodes pmod,
3482: pay_taxability_rules ptax
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
3504: --Bug 3319878 -- Changed the query to improve performance
3505:
3506: CURSOR ptax_ca_cur IS
3507: SELECT distinct ptax.jurisdiction_code
3508: FROM pay_us_modified_geocodes pmod,
3509: pay_taxability_rules ptax
3510: WHERE pmod.state_code = 'CA'
3511: AND ptax.jurisdiction_code = pmod.county_code || '000-0000'
3512: AND pmod.patch_name = p_patch_name
3519:
3520:
3521:
3522: jd_code pay_taxability_rules.jurisdiction_code%TYPE;
3523: l_proc_type pay_us_modified_geocodes.process_type%TYPE;
3524: l_error_message_text varchar2(240);
3525: l_count number;
3526: BEGIN
3527:
3537:
3538: SELECT pmod.state_code||'-'||pmod.county_code||'-'||pmod.new_city_code,
3539: process_type
3540: INTO jd_code, l_proc_type
3541: FROM pay_us_modified_geocodes pmod
3542: WHERE pmod.state_code = substr(ptax_rec.jurisdiction_code,1,2)
3543: AND pmod.old_city_code = substr(ptax_rec.jurisdiction_code,8,4)
3544: AND pmod.process_type in ('UP','RP')
3545: AND pmod.patch_name = p_patch_name
3601:
3602: SELECT pmod.new_county_code,
3603: process_type
3604: INTO jd_code, l_proc_type
3605: FROM pay_us_modified_geocodes pmod
3606: WHERE pmod.state_code = 'CA'
3607: AND pmod.county_code = substr(ptax_ca_rec.jurisdiction_code,1,2)
3608: AND pmod.patch_name = p_patch_name;
3609:
3662:
3663: --Modified the function for Bug#10060041.
3664: --Since we saw more changes and requests coming up for County Name Change/Correction,
3665: --the process of County Name Change was standardized. A Record will now be inserted
3666: --into pay_us_modified_geocodes table with process_type as 'CN'. It has Old County Name
3667: --stored in city_name field. old_city_code and new_city_code will be shown as '0000'
3668: --which will differentiate it from regular city_code changes stored in that table.
3669: --Process_Type will be 'CN'. For every year, the County_Name changes will be found
3670: --from pay_us_modified_geocodes table and corresponding Address and Location details
3666: --into pay_us_modified_geocodes table with process_type as 'CN'. It has Old County Name
3667: --stored in city_name field. old_city_code and new_city_code will be shown as '0000'
3668: --which will differentiate it from regular city_code changes stored in that table.
3669: --Process_Type will be 'CN'. For every year, the County_Name changes will be found
3670: --from pay_us_modified_geocodes table and corresponding Address and Location details
3671: --are updated with new county name.
3672:
3673: /*This procedure is called from pay_us_geo_upd_pkg.action_creation. For an Year, if there
3674: are no assignments impacted by the city_name changes delivered the Submission of
3700: pus.state_abbrev,
3701: puc.county_code,
3702: pumg.city_name old_county_name,
3703: puc.county_name new_county_name
3704: FROM pay_us_modified_geocodes pumg,
3705: pay_us_states pus,
3706: pay_us_counties puc
3707: WHERE pus.state_code = puc.state_code
3708: AND pus.state_code = pumg.state_code
3900: the City Name we delivered earlier, it is necessary to update the City Name
3901: details stored in other tables.
3902:
3903: For each of the City Name that got modified, an entry will be created in table
3904: PAY_US_MODIFIED_GEOCODES with process_type as 'CY'. It has Old City Name stored
3905: in city_name field. The Jurisdiction Code details corresponding to the City
3906: Name is also stored in PAY_US_MODIFIED_GEOCODES table.
3907:
3908: Since for a given Jurisdiction Code we can have multiple cities (i.e Primary
3902:
3903: For each of the City Name that got modified, an entry will be created in table
3904: PAY_US_MODIFIED_GEOCODES with process_type as 'CY'. It has Old City Name stored
3905: in city_name field. The Jurisdiction Code details corresponding to the City
3906: Name is also stored in PAY_US_MODIFIED_GEOCODES table.
3907:
3908: Since for a given Jurisdiction Code we can have multiple cities (i.e Primary
3909: city and Secondary Cities), it is necessary to have a mechanism to distinguish
3910: for which City Name entry the name actually got modified.
3908: Since for a given Jurisdiction Code we can have multiple cities (i.e Primary
3909: city and Secondary Cities), it is necessary to have a mechanism to distinguish
3910: for which City Name entry the name actually got modified.
3911:
3912: For this purpose, another entry will be created in PAY_US_MODIFIED_GEOCODES
3913: table with process_type as 'NC'. It has the New City corresponding to the
3914: Old City that got updated. In case if there are multiple city name changes for
3915: a same city code, in order to make it easy to identify which city name got
3916: changed to which city name, the old city name will be saved in the field
3922:
3923: SELECT pumg1.patch_name,
3924: pumg1.city_name old_city_name,
3925: pumg2.city_name new_city_name
3926: FROM pay_us_modified_geocodes pumg1,
3927: pay_us_modified_geocodes pumg2
3928: WHERE pumg1.process_type = 'CY'
3929: AND pumg2.process_type = 'NC'
3930: AND pumg1.state_code = pumg2.state_code
3923: SELECT pumg1.patch_name,
3924: pumg1.city_name old_city_name,
3925: pumg2.city_name new_city_name
3926: FROM pay_us_modified_geocodes pumg1,
3927: pay_us_modified_geocodes pumg2
3928: WHERE pumg1.process_type = 'CY'
3929: AND pumg2.process_type = 'NC'
3930: AND pumg1.state_code = pumg2.state_code
3931: AND pumg1.county_code = pumg2.county_code
3958: l_count number;
3959: l_override_count number;
3960: l_description varchar2(500);
3961:
3962: CURSOR get_city_name_changes (P_PATCH_NAME pay_us_modified_geocodes.patch_name%TYPE) IS
3963: SELECT decode(pumg1.state_code,'70','CA','US') country,
3964: pus.state_code,
3965: pus.state_abbrev,
3966: puc.county_code,
3966: puc.county_code,
3967: pumg1.old_city_code,
3968: pumg1.city_name old_city_name,
3969: pumg2.city_name new_city_name
3970: FROM pay_us_modified_geocodes pumg1,
3971: pay_us_modified_geocodes pumg2,
3972: pay_us_states pus,
3973: pay_us_counties puc
3974: WHERE pus.state_code = puc.state_code
3967: pumg1.old_city_code,
3968: pumg1.city_name old_city_name,
3969: pumg2.city_name new_city_name
3970: FROM pay_us_modified_geocodes pumg1,
3971: pay_us_modified_geocodes pumg2,
3972: pay_us_states pus,
3973: pay_us_counties puc
3974: WHERE pus.state_code = puc.state_code
3975: AND pus.state_code = pumg1.state_code
3996: FROM pay_us_counties
3997: WHERE state_code = p_state_code
3998: AND county_code = p_county_code;
3999:
4000: CURSOR get_new_county_name (P_STATE_CODE pay_us_modified_geocodes.state_code%TYPE,
4001: P_COUNTY_CODE pay_us_modified_geocodes.county_code%TYPE) IS
4002: SELECT city_name county_name
4003: FROM pay_us_modified_geocodes
4004: WHERE process_type = 'CN'
3997: WHERE state_code = p_state_code
3998: AND county_code = p_county_code;
3999:
4000: CURSOR get_new_county_name (P_STATE_CODE pay_us_modified_geocodes.state_code%TYPE,
4001: P_COUNTY_CODE pay_us_modified_geocodes.county_code%TYPE) IS
4002: SELECT city_name county_name
4003: FROM pay_us_modified_geocodes
4004: WHERE process_type = 'CN'
4005: AND state_code = p_state_code
3999:
4000: CURSOR get_new_county_name (P_STATE_CODE pay_us_modified_geocodes.state_code%TYPE,
4001: P_COUNTY_CODE pay_us_modified_geocodes.county_code%TYPE) IS
4002: SELECT city_name county_name
4003: FROM pay_us_modified_geocodes
4004: WHERE process_type = 'CN'
4005: AND state_code = p_state_code
4006: AND county_code = p_county_code
4007: ORDER BY patch_name;
4649: --Retrieve all changed geocodes in the hr_organization_information table
4650:
4651: CURSOR org_info_cur IS
4652: SELECT distinct org_information1
4653: FROM pay_us_modified_geocodes pmod,
4654: hr_organization_information hoi
4655: WHERE pmod.state_code = substr(hoi.org_information1,1,2)
4656: AND pmod.county_code = substr(hoi.org_information1,4,3)
4657: AND pmod.old_city_code = substr(hoi.org_information1,8,4)
4676: --
4677:
4678: CURSOR org_info_ca_cur IS
4679: SELECT distinct hoi.org_information1, hoi.org_information_id
4680: FROM pay_us_modified_geocodes pmod,
4681: hr_organization_information hoi
4682: WHERE pmod.state_code = 'CA'
4683: AND pmod.county_code = substr(hoi.org_information1,1,2)
4684: AND pmod.patch_name = p_patch_name
4693: org_info_ca_rec org_info_ca_cur%ROWTYPE;
4694: --
4695: --
4696: new_geocode hr_organization_information.org_information1%TYPE;
4697: l_proc_type pay_us_modified_geocodes.process_type%TYPE;
4698: l_error_message_text varchar2(240);
4699:
4700: BEGIN
4701:
4711:
4712: SELECT pmod.state_code||'-'||pmod.county_code||'-'||pmod.new_city_code,
4713: process_type
4714: INTO new_geocode, l_proc_type
4715: FROM pay_us_modified_geocodes pmod
4716: WHERE pmod.state_code = substr(org_info_rec.org_information1,1,2)
4717: AND pmod.county_code = substr(org_info_rec.org_information1,4,3)
4718: AND pmod.old_city_code = substr(org_info_rec.org_information1,8,4)
4719: AND pmod.process_type in ('UP','PU','RP','U')
4769:
4770: SELECT pmod.new_county_code,
4771: process_type
4772: INTO new_geocode, l_proc_type
4773: FROM pay_us_modified_geocodes pmod
4774: WHERE pmod.state_code = 'CA'
4775: AND pmod.county_code = substr(org_info_ca_rec.org_information1,1,2)
4776: AND pmod.patch_name = p_patch_name;
4777:
4981: IS
4982: CURSOR canada_emp_fed_tax_cur IS
4983: SELECT distinct cafed.employment_province, cafed.assignment_id
4984: FROM pay_ca_emp_fed_tax_info_f cafed,
4985: pay_us_modified_geocodes pmod
4986: WHERE pmod.state_code = 'CA'
4987: AND pmod.county_code = cafed.employment_province
4988: AND pmod.patch_name = p_patch_name;
4989:
4991:
4992: CURSOR canada_emp_prov_tax_cur IS
4993: SELECT distinct caprov.province_code, caprov.assignment_id
4994: FROM pay_ca_emp_prov_tax_info_f caprov,
4995: pay_us_modified_geocodes pmod
4996: WHERE pmod.state_code = 'CA'
4997: AND pmod.county_code = caprov.province_code
4998: AND pmod.patch_name = p_patch_name;
4999:
5001:
5002: CURSOR canada_leg_info_cur IS
5003: SELECT distinct caleg.jurisdiction_code
5004: FROM pay_ca_legislation_info caleg,
5005: pay_us_modified_geocodes pmod
5006: WHERE pmod.state_code = 'CA'
5007: AND pmod.county_code = caleg.jurisdiction_code
5008: AND pmod.patch_name = p_patch_name ;
5009:
5013:
5014: new_geocode pay_ca_emp_fed_tax_info_f .employment_province%TYPE;
5015: new_geocode1 pay_ca_emp_prov_tax_info_f.province_code%TYPE;
5016: new_geocode2 pay_ca_legislation_info. jurisdiction_code%TYPE;
5017: l_proc_type pay_us_modified_geocodes.process_type%TYPE;
5018: l_error_message_text varchar2(240);
5019:
5020: BEGIN
5021:
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
5039: WHERE pmod.state_code = 'CA'
5040: AND pmod.county_code = canada_emp_fed_rec.employment_province
5041: AND pmod.patch_name = p_patch_name;
5042:
5079:
5080: SELECT pmod.new_county_code,
5081: pmod.process_type
5082: INTO new_geocode1, l_proc_type
5083: FROM pay_us_modified_geocodes pmod
5084: WHERE pmod.state_code = 'CA'
5085: AND pmod.county_code = canada_emp_prov_rec.province_code
5086: AND pmod.patch_name = p_patch_name;
5087:
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
5130: WHERE pmod.state_code = 'CA'
5131: AND pmod.county_code = canada_leg_info_rec.jurisdiction_code
5132: AND pmod.patch_name = p_patch_name;
5133:
5192: P_MODE IN VARCHAR2,
5193: P_PATCH_NAME IN VARCHAR2)
5194: IS
5195:
5196: /* Bug# 3679984 Forced the index PAY_US_MODIFIED_GEOCODES_PK on pay_us_modified_geocodes
5197: and rearranged the order of the where clause in the subquery */
5198:
5199: /* Bug 4773276 Changing the hint to PAY_US_MODIFIED_GEOCODES_N1 */
5200:
5195:
5196: /* Bug# 3679984 Forced the index PAY_US_MODIFIED_GEOCODES_PK on pay_us_modified_geocodes
5197: and rearranged the order of the where clause in the subquery */
5198:
5199: /* Bug 4773276 Changing the hint to PAY_US_MODIFIED_GEOCODES_N1 */
5200:
5201: CURSOR group_level_bal_us (c_payroll_action_id number) IS
5202: select
5203: prb.run_balance_id, prb.jurisdiction_code, prb.jurisdiction_comp3
5200:
5201: CURSOR group_level_bal_us (c_payroll_action_id number) IS
5202: select
5203: prb.run_balance_id, prb.jurisdiction_code, prb.jurisdiction_comp3
5204: from pay_run_balances prb, pay_us_modified_geocodes pmod
5205: Where prb.payroll_action_id = c_payroll_action_id
5206: --between p_start_payroll_action and p_end_payroll_action
5207: and prb.assignment_id is null
5208: and pmod.state_code = substr(prb.jurisdiction_code,1,2)
5220: and pugu.table_name = 'PAY_RUN_BALANCES'
5221: and pugu.id = g_geo_phase_id);*/
5222:
5223: /* select /*+ ORDERED
5224: index(pmod PAY_US_MODIFIED_GEOCODES_N1)
5225: USE_NL(prb pdb pbd pmod) */
5226: /* prb.run_balance_id,
5227: prb.jurisdiction_code,
5228: prb.jurisdiction_comp3
5228: prb.jurisdiction_comp3
5229: from pay_run_balances prb,
5230: pay_defined_balances pdb,
5231: pay_balance_dimensions pbd,
5232: pay_us_modified_geocodes pmod
5233: Where prb.payroll_action_id = c_payroll_action_id
5234: --between p_start_payroll_action and p_end_payroll_action
5235: and prb.assignment_id is null
5236: and prb.defined_balance_id = pdb.defined_balance_id
5256:
5257: CURSOR group_level_bal_ca (c_payroll_action_id number) IS
5258: select
5259: prb.run_balance_id, prb.jurisdiction_code, prb.jurisdiction_comp3
5260: from pay_run_balances prb, pay_us_modified_geocodes pmod
5261: Where prb.payroll_action_id = c_payroll_action_id
5262: --between p_start_payroll_action and p_end_payroll_action
5263: and prb.assignment_id is null
5264: and pmod.state_code = 'CA'
5276: and pugu.process_type = g_process_type
5277: and pugu.id = g_geo_phase_id); */
5278:
5279: /*select /*+ ORDERED
5280: index(pmod PAY_US_MODIFIED_GEOCODES_N1)
5281: USE_NL(prb pdb pbd pmod) */
5282: /* prb.run_balance_id,
5283: prb.jurisdiction_code,
5284: prb.jurisdiction_comp3
5284: prb.jurisdiction_comp3
5285: from pay_run_balances prb,
5286: pay_defined_balances pdb,
5287: pay_balance_dimensions pbd,
5288: pay_us_modified_geocodes pmod
5289: Where prb.payroll_action_id = c_payroll_action_id
5290: --between p_start_payroll_action and p_end_payroll_action
5291: and prb.assignment_id is null
5292: and prb.defined_balance_id = pdb.defined_balance_id
5319:
5320: group_level_bal_ca_rec group_level_bal_ca%ROWTYPE;
5321:
5322:
5323: l_proc_type pay_us_modified_geocodes.process_type%TYPE;
5324: l_geocode pay_run_balances.jurisdiction_code%TYPE;
5325: l_new_city_code pay_us_modified_geocodes.new_city_code%TYPE;
5326: l_legislation_code per_business_groups.legislation_code%TYPE;
5327: l_pactid pay_payroll_actions.payroll_action_id%TYPE;
5321:
5322:
5323: l_proc_type pay_us_modified_geocodes.process_type%TYPE;
5324: l_geocode pay_run_balances.jurisdiction_code%TYPE;
5325: l_new_city_code pay_us_modified_geocodes.new_city_code%TYPE;
5326: l_legislation_code per_business_groups.legislation_code%TYPE;
5327: l_pactid pay_payroll_actions.payroll_action_id%TYPE;
5328:
5329: l_row_updated varchar2(1);
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
5382: WHERE pmod.state_code = substr(group_level_bal_us_rec.jurisdiction_code,1,2)
5383: AND pmod.county_code = substr(group_level_bal_us_rec.jurisdiction_code,4,3)
5384: AND pmod.old_city_code = substr(group_level_bal_us_rec.jurisdiction_code,8,4)
5385: AND pmod.process_type in ('UP','PU','RP','U','US','D','SU')
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;