[Home] [Help]
848: l_start := DBMS_UTILITY.GET_TIME;
849:
850: -- Get new id for new state and county
851: UPDATE ZX_DATA_UPLOAD_INTERFACE
852: SET geography_id = hz_geographies_s.nextval,
853: status = 'CREATE'
854: WHERE record_type IN (1,3)
855: AND geography_id IS NULL
856: AND effective_to IS NULL;
868:
869: -- Get new id for new city. In the case of city divided in two or more
870: -- jurisdictions, get new id only for the first one
871: UPDATE ZX_DATA_UPLOAD_INTERFACE
872: SET geography_id = hz_geographies_s.nextval,
873: status = 'CREATE'
874: WHERE record_type = 6
875: AND geography_id IS NULL
876: AND effective_to IS NULL
936: l_start := DBMS_UTILITY.GET_TIME;
937:
938: -- Update a new geography id for all remaining cities.
939: UPDATE ZX_DATA_UPLOAD_INTERFACE
940: SET geography_id = hz_geographies_s.nextval,
941: status = 'CREATE'
942: WHERE record_type = 6
943: AND geography_id IS NULL
944: AND effective_to IS NULL;
1109: l_start := DBMS_UTILITY.GET_TIME;
1110:
1111: -- Now, UPDATE the zone_geography_id for new records for counties AND states
1112: UPDATE ZX_DATA_UPLOAD_INTERFACE x
1113: SET x.zone_geography_id = hz_geographies_s.nextval
1114: WHERE x.record_type IN (1,3)
1115: AND x.zone_geography_id IS NULL
1116: AND x.effective_to IS NULL;
1117: -- AND EXISTS (SELECT NULL
1133: l_start := DBMS_UTILITY.GET_TIME;
1134:
1135: -- UPDATE the zone geography id for primary cities first.
1136: UPDATE ZX_DATA_UPLOAD_INTERFACE x
1137: SET x.zone_geography_id = hz_geographies_s.nextval
1138: WHERE x.record_type = 6
1139: AND x.zone_geography_id IS NULL
1140: AND x.effective_to IS NULL
1141: AND x.primary_flag = 'Y';
1243: -- Now, UPDATE the zone_geography_id for override rates
1244: -- UPDATE only the first row as there could be multiple rates for one
1245: -- overriding jurisdiction
1246: UPDATE ZX_DATA_UPLOAD_INTERFACE x
1247: SET x.zone_geography_id = hz_geographies_s.nextval
1248: WHERE x.record_type IN (9,10,11,12)
1249: AND x.zone_geography_id IS NULL
1250: AND x.STATE_JURISDICTION_CODE IS NOT NULL
1251: AND x.COUNTY_JURISDICTION_CODE IS NOT NULL
1349: -- Now, UPDATE the zone_geography_id for override rates
1350: -- UPDATE only the first row as there could be multiple rates for one
1351: -- overriding jurisdiction
1352: UPDATE ZX_DATA_UPLOAD_INTERFACE x
1353: SET x.zone_geography_id = hz_geographies_s.nextval
1354: WHERE x.record_type IN (9,10,11,12)
1355: AND x.zone_geography_id IS NULL
1356: AND x.STATE_JURISDICTION_CODE IS NOT NULL
1357: AND x.COUNTY_JURISDICTION_CODE IS NOT NULL
1452: l_start := DBMS_UTILITY.GET_TIME;
1453:
1454: -- Now, UPDATE the zone_geography_id for new records,
1455: UPDATE ZX_DATA_UPLOAD_INTERFACE x
1456: SET x.zone_geography_id = hz_geographies_s.nextval
1457: WHERE x.record_type = 6
1458: AND x.zone_geography_id IS NULL
1459: AND x.effective_to IS NULL
1460: AND x.primary_flag = 'Y';
4249: LAST_UPDATE_LOGIN
4250: )
4251: VALUES
4252: (
4253: hz_geographies_s.nextval,
4254: 1,
4255: geography_type,
4256: geography_name,
4257: 'MASTER_REF',
4270: geography_element4,
4271: geography_element4_id,
4272: geography_element4_code,
4273: geography_name,
4274: hz_geographies_s.nextval,
4275: null,
4276: G_CREATED_BY_MODULE,
4277: country_code,
4278: 'PST',
4302: LAST_UPDATE_LOGIN
4303: )
4304: VALUES
4305: (
4306: hz_geographies_s.nextval,
4307: p_tax_content_source,
4308: 'STANDARD_NAME',
4309: geography_name,
4310: 1,
4340: LAST_UPDATE_LOGIN
4341: )
4342: VALUES
4343: (
4344: hz_geographies_s.nextval,
4345: p_tax_content_source,
4346: 'FIPS_CODE',
4347: geography_code,
4348: 1,
4392: hz_relationships_s.nextval,
4393: parent_geography_id,
4394: parent_geography_type,
4395: 'HZ_GEOGRAPHIES',
4396: hz_geographies_s.nextval,
4397: geography_type,
4398: 'HZ_GEOGRAPHIES',
4399: 'PARENT_OF',
4400: 'F',
4447: )
4448: VALUES
4449: (
4450: hz_relationships_s.nextval,
4451: hz_geographies_s.nextval,
4452: geography_type,
4453: 'HZ_GEOGRAPHIES',
4454: parent_geography_id,
4455: parent_geography_type,
4500: )
4501: VALUES
4502: (
4503: 'MASTER_REF',
4504: hz_geographies_s.nextval,
4505: 'HZ_GEOGRAPHIES',
4506: geography_type,
4507: hz_geographies_s.nextval,
4508: 'HZ_GEOGRAPHIES',
4503: 'MASTER_REF',
4504: hz_geographies_s.nextval,
4505: 'HZ_GEOGRAPHIES',
4506: geography_type,
4507: hz_geographies_s.nextval,
4508: 'HZ_GEOGRAPHIES',
4509: geography_type,
4510: 0 ,
4511: 'N',
4550: 'MASTER_REF',
4551: parent_geography_id,
4552: 'HZ_GEOGRAPHIES',
4553: parent_geography_type,
4554: hz_geographies_s.nextval,
4555: 'HZ_GEOGRAPHIES',
4556: geography_type,
4557: 1,
4558: '',
4598: 'MASTER_REF',
4599: geography_element3_id,
4600: 'HZ_GEOGRAPHIES',
4601: 'COUNTY',
4602: hz_geographies_s.nextval,
4603: 'HZ_GEOGRAPHIES',
4604: geography_type,
4605: 2 ,
4606: '',
4645: 'MASTER_REF',
4646: geography_element2_id,
4647: 'HZ_GEOGRAPHIES',
4648: 'STATE',
4649: hz_geographies_s.nextval,
4650: 'HZ_GEOGRAPHIES',
4651: geography_type,
4652: 3 ,
4653: '',
4692: 'MASTER_REF',
4693: geography_element1_id,
4694: 'HZ_GEOGRAPHIES',
4695: 'COUNTRY',
4696: hz_geographies_s.nextval,
4697: 'HZ_GEOGRAPHIES',
4698: geography_type,
4699: 4 ,
4700: '',