[Home] [Help]
801: l_start := DBMS_UTILITY.GET_TIME;
802:
803: -- Get new id for new state and county
804: update ZX_DATA_UPLOAD_INTERFACE
805: set geography_id = hz_geographies_s.nextval,
806: status = 'CREATE'
807: where record_type in (1,3)
808: and geography_id is null
809: and effective_to is null;
821:
822: -- Get new id for new city. In the case of city divided in two or more
823: -- jurisdictions, get new id only for the first one
824: update ZX_DATA_UPLOAD_INTERFACE
825: set geography_id = hz_geographies_s.nextval,
826: status = 'CREATE'
827: where record_type = 6
828: and geography_id is null
829: and effective_to is null
998:
999: -- Now, update the zone_geography_id for new records,
1000: -- but do this only if a rate exists
1001: update ZX_DATA_UPLOAD_INTERFACE x
1002: set x.zone_geography_id = hz_geographies_s.nextval
1003: where x.record_type IN (1,3,6)
1004: and x.zone_geography_id is null
1005: and x.effective_to is null;
1006: /*and EXISTS (select null
1077: -- Now, update the zone_geography_id for override rates
1078: -- Update only the first row as there could be multiple rates for one
1079: -- overriding jurisdiction
1080: update ZX_DATA_UPLOAD_INTERFACE x
1081: set x.zone_geography_id = hz_geographies_s.nextval
1082: where x.record_type IN (9,10,11,12)
1083: and x.zone_geography_id is null
1084: and x.STATE_JURISDICTION_CODE is not null
1085: and x.COUNTY_JURISDICTION_CODE is not null
1174: -- Now, update the zone_geography_id for override rates
1175: -- Update only the first row as there could be multiple rates for one
1176: -- overriding jurisdiction
1177: update ZX_DATA_UPLOAD_INTERFACE x
1178: set x.zone_geography_id = hz_geographies_s.nextval
1179: where x.record_type IN (9,10,11,12)
1180: and x.zone_geography_id is null
1181: and x.STATE_JURISDICTION_CODE is not null
1182: and x.COUNTY_JURISDICTION_CODE is not null
1248:
1249: -- Now, update the zone_geography_id for new records,
1250: -- but do this only if a rate exists
1251: update ZX_DATA_UPLOAD_INTERFACE x
1252: set x.zone_geography_id = hz_geographies_s.nextval
1253: where x.record_type = 6
1254: and x.zone_geography_id is null
1255: and x.effective_to is null;
1256: /*and EXISTS (select null
3883: LAST_UPDATE_LOGIN
3884: )
3885: VALUES
3886: (
3887: hz_geographies_s.nextval,
3888: 1,
3889: geography_type,
3890: geography_name,
3891: 'MASTER_REF',
3904: geography_element4,
3905: geography_element4_id,
3906: geography_element4_code,
3907: geography_name,
3908: hz_geographies_s.nextval,
3909: null,
3910: G_CREATED_BY_MODULE,
3911: country_code,
3912: 'PST',
3936: LAST_UPDATE_LOGIN
3937: )
3938: VALUES
3939: (
3940: hz_geographies_s.nextval,
3941: p_tax_content_source,
3942: 'STANDARD_NAME',
3943: geography_name,
3944: 1,
3974: LAST_UPDATE_LOGIN
3975: )
3976: VALUES
3977: (
3978: hz_geographies_s.nextval,
3979: p_tax_content_source,
3980: 'FIPS_CODE',
3981: geography_code,
3982: 1,
4026: hz_relationships_s.nextval,
4027: parent_geography_id,
4028: parent_geography_type,
4029: 'HZ_GEOGRAPHIES',
4030: hz_geographies_s.nextval,
4031: geography_type,
4032: 'HZ_GEOGRAPHIES',
4033: 'PARENT_OF',
4034: 'F',
4081: )
4082: VALUES
4083: (
4084: hz_relationships_s.nextval,
4085: hz_geographies_s.nextval,
4086: geography_type,
4087: 'HZ_GEOGRAPHIES',
4088: parent_geography_id,
4089: parent_geography_type,
4134: )
4135: VALUES
4136: (
4137: 'MASTER_REF',
4138: hz_geographies_s.nextval,
4139: 'HZ_GEOGRAPHIES',
4140: geography_type,
4141: hz_geographies_s.nextval,
4142: 'HZ_GEOGRAPHIES',
4137: 'MASTER_REF',
4138: hz_geographies_s.nextval,
4139: 'HZ_GEOGRAPHIES',
4140: geography_type,
4141: hz_geographies_s.nextval,
4142: 'HZ_GEOGRAPHIES',
4143: geography_type,
4144: 0 ,
4145: 'N',
4184: 'MASTER_REF',
4185: parent_geography_id,
4186: 'HZ_GEOGRAPHIES',
4187: parent_geography_type,
4188: hz_geographies_s.nextval,
4189: 'HZ_GEOGRAPHIES',
4190: geography_type,
4191: 1,
4192: '',
4232: 'MASTER_REF',
4233: geography_element3_id,
4234: 'HZ_GEOGRAPHIES',
4235: 'COUNTY',
4236: hz_geographies_s.nextval,
4237: 'HZ_GEOGRAPHIES',
4238: geography_type,
4239: 2 ,
4240: '',
4279: 'MASTER_REF',
4280: geography_element2_id,
4281: 'HZ_GEOGRAPHIES',
4282: 'STATE',
4283: hz_geographies_s.nextval,
4284: 'HZ_GEOGRAPHIES',
4285: geography_type,
4286: 3 ,
4287: '',
4326: 'MASTER_REF',
4327: geography_element1_id,
4328: 'HZ_GEOGRAPHIES',
4329: 'COUNTRY',
4330: hz_geographies_s.nextval,
4331: 'HZ_GEOGRAPHIES',
4332: geography_type,
4333: 4 ,
4334: '',