[Home] [Help]
631: ) IS
632: SELECT DISTINCT
633: X.ROWID,
634: Y.GEOGRAPHY_ID
635: FROM ZX_DATA_UPLOAD_INTERFACE X,
636: HZ_GEOGRAPHIES Y,
637: ZX_DATA_UPLOAD_INTERFACE Z,
638: ZX_DATA_UPLOAD_INTERFACE ZZ
639: WHERE X.RECORD_TYPE = 6
633: X.ROWID,
634: Y.GEOGRAPHY_ID
635: FROM ZX_DATA_UPLOAD_INTERFACE X,
636: HZ_GEOGRAPHIES Y,
637: ZX_DATA_UPLOAD_INTERFACE Z,
638: ZX_DATA_UPLOAD_INTERFACE ZZ
639: WHERE X.RECORD_TYPE = 6
640: AND UPPER(Y.GEOGRAPHY_NAME) = UPPER(X.GEOGRAPHY_NAME)
641: AND Y.GEOGRAPHY_USE = 'MASTER_REF'
634: Y.GEOGRAPHY_ID
635: FROM ZX_DATA_UPLOAD_INTERFACE X,
636: HZ_GEOGRAPHIES Y,
637: ZX_DATA_UPLOAD_INTERFACE Z,
638: ZX_DATA_UPLOAD_INTERFACE ZZ
639: WHERE X.RECORD_TYPE = 6
640: AND UPPER(Y.GEOGRAPHY_NAME) = UPPER(X.GEOGRAPHY_NAME)
641: AND Y.GEOGRAPHY_USE = 'MASTER_REF'
642: AND Y.GEOGRAPHY_TYPE = 'CITY'
660: l_start := DBMS_UTILITY.GET_TIME;
661:
662: -- Find the state geography id using abbreviation code. Note that since we
663: -- are using code, even the name change records will get the geography_id.
664: UPDATE ZX_DATA_UPLOAD_INTERFACE x
665: SET x.geography_id = (SELECT Y.GEOGRAPHY_ID
666: FROM HZ_GEOGRAPHIES Y
667: WHERE Y.GEOGRAPHY_NAME = X.COUNTRY_STATE_ABBREVIATION
668: AND Y.GEOGRAPHY_CODE = X.COUNTRY_STATE_ABBREVIATION
686:
687: l_start := DBMS_UTILITY.GET_TIME;
688:
689: -- Now update the status of the name change record.
690: UPDATE ZX_DATA_UPLOAD_INTERFACE x
691: SET x.status = 'UPDATE'
692: WHERE x.record_type = 1
693: AND x.effective_to IS NULL
694: AND EXISTS (SELECT NULL
691: SET x.status = 'UPDATE'
692: WHERE x.record_type = 1
693: AND x.effective_to IS NULL
694: AND EXISTS (SELECT NULL
695: FROM ZX_DATA_UPLOAD_INTERFACE y
696: WHERE y.record_type = 1
697: AND y.state_jurisdiction_code = x.state_jurisdiction_code
698: AND y.country_state_abbreviation = x.country_state_abbreviation
699: AND y.effective_to IS NOT NULL);
711:
712: -- Geography Id's for County for pre-existing geographies
713: -- The state record could have been ended or it could have been sent twice
714: -- with name change, so rownum clause is used.
715: UPDATE ZX_DATA_UPLOAD_INTERFACE x
716: SET x.geography_id = (SELECT Y.GEOGRAPHY_ID
717: FROM HZ_GEOGRAPHIES Y,
718: ZX_DATA_UPLOAD_INTERFACE Z
719: WHERE UPPER(Y.GEOGRAPHY_NAME) = UPPER(X.GEOGRAPHY_NAME)
714: -- with name change, so rownum clause is used.
715: UPDATE ZX_DATA_UPLOAD_INTERFACE x
716: SET x.geography_id = (SELECT Y.GEOGRAPHY_ID
717: FROM HZ_GEOGRAPHIES Y,
718: ZX_DATA_UPLOAD_INTERFACE Z
719: WHERE UPPER(Y.GEOGRAPHY_NAME) = UPPER(X.GEOGRAPHY_NAME)
720: AND Y.GEOGRAPHY_USE = 'MASTER_REF'
721: AND Y.GEOGRAPHY_TYPE = 'COUNTY'
722: AND Y.GEOGRAPHY_ELEMENT1_ID = L_CNTRY_GEOGRAPHY_ID
741: l_start := DBMS_UTILITY.GET_TIME;
742:
743: -- Geography Id's for County for pre-existing geographies, which had name
744: -- change. Use existing record with old name to find the id.
745: UPDATE ZX_DATA_UPLOAD_INTERFACE x
746: SET x.geography_id = (SELECT Y.GEOGRAPHY_ID
747: FROM ZX_DATA_UPLOAD_INTERFACE y
748: WHERE y.RECORD_TYPE = 3
749: AND y.STATE_JURISDICTION_CODE = x.STATE_JURISDICTION_CODE
743: -- Geography Id's for County for pre-existing geographies, which had name
744: -- change. Use existing record with old name to find the id.
745: UPDATE ZX_DATA_UPLOAD_INTERFACE x
746: SET x.geography_id = (SELECT Y.GEOGRAPHY_ID
747: FROM ZX_DATA_UPLOAD_INTERFACE y
748: WHERE y.RECORD_TYPE = 3
749: AND y.STATE_JURISDICTION_CODE = x.STATE_JURISDICTION_CODE
750: AND y.COUNTY_JURISDICTION_CODE = x.COUNTY_JURISDICTION_CODE
751: AND y.GEOGRAPHY_ID IS NOT NULL),
770: -- or city with multiple counties has been created as multiple geographies.
771: -- rownum = 1 clause is used as the there could be multiple county
772: -- or state records with different effective dates
773: --
774: -- UPDATE ZX_DATA_UPLOAD_INTERFACE x
775: -- SET x.geography_id = (SELECT Y.GEOGRAPHY_ID
776: -- FROM HZ_GEOGRAPHIES Y,
777: -- ZX_DATA_UPLOAD_INTERFACE Z,
778: -- ZX_DATA_UPLOAD_INTERFACE ZZ
773: --
774: -- UPDATE ZX_DATA_UPLOAD_INTERFACE x
775: -- SET x.geography_id = (SELECT Y.GEOGRAPHY_ID
776: -- FROM HZ_GEOGRAPHIES Y,
777: -- ZX_DATA_UPLOAD_INTERFACE Z,
778: -- ZX_DATA_UPLOAD_INTERFACE ZZ
779: -- WHERE UPPER(Y.GEOGRAPHY_NAME) = UPPER(X.GEOGRAPHY_NAME)
780: -- AND Y.GEOGRAPHY_USE = 'MASTER_REF'
781: -- AND Y.GEOGRAPHY_TYPE = 'CITY'
774: -- UPDATE ZX_DATA_UPLOAD_INTERFACE x
775: -- SET x.geography_id = (SELECT Y.GEOGRAPHY_ID
776: -- FROM HZ_GEOGRAPHIES Y,
777: -- ZX_DATA_UPLOAD_INTERFACE Z,
778: -- ZX_DATA_UPLOAD_INTERFACE ZZ
779: -- WHERE UPPER(Y.GEOGRAPHY_NAME) = UPPER(X.GEOGRAPHY_NAME)
780: -- AND Y.GEOGRAPHY_USE = 'MASTER_REF'
781: -- AND Y.GEOGRAPHY_TYPE = 'CITY'
782: -- AND Y.GEOGRAPHY_ELEMENT1_ID = L_CNTRY_GEOGRAPHY_ID
803: l_rowcount := c_get_city_rows%rowcount;
804: l_rows_processed := l_rows_processed + l_rowcount;
805:
806: FORALL i IN l_rowids.FIRST..l_rowids.LAST
807: UPDATE zx_data_upload_interface
808: SET geography_id = l_geography_ids(i),
809: status = 'NOCHANGE'
810: WHERE rowid = l_rowids(i);
811:
821: --
822: -- Geography Id's for City for pre-existing geographies, which had name
823: -- change. Use existing record with old name to find the id.
824: --
825: -- UPDATE ZX_DATA_UPLOAD_INTERFACE x
826: -- SET x.geography_id = (SELECT Y.GEOGRAPHY_ID
827: -- FROM ZX_DATA_UPLOAD_INTERFACE y
828: -- WHERE y.RECORD_TYPE = 6
829: -- AND y.STATE_JURISDICTION_CODE = x.STATE_JURISDICTION_CODE
823: -- change. Use existing record with old name to find the id.
824: --
825: -- UPDATE ZX_DATA_UPLOAD_INTERFACE x
826: -- SET x.geography_id = (SELECT Y.GEOGRAPHY_ID
827: -- FROM ZX_DATA_UPLOAD_INTERFACE y
828: -- WHERE y.RECORD_TYPE = 6
829: -- AND y.STATE_JURISDICTION_CODE = x.STATE_JURISDICTION_CODE
830: -- AND y.COUNTY_JURISDICTION_CODE = x.COUNTY_JURISDICTION_CODE
831: -- AND y.CITY_JURISDICTION_CODE = x.CITY_JURISDICTION_CODE
847:
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
867: l_start := DBMS_UTILITY.GET_TIME;
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
890: GEOGRAPHY_NAME
891: ORDER BY GEOGRAPHY_ID,
892: EFFECTIVE_FROM)
893: AS CITY_ROW_NUMBER
894: FROM ZX_DATA_UPLOAD_INTERFACE
895: WHERE RECORD_TYPE = 6
896: --AND GEOGRAPHY_ID IS NULL
897: AND EFFECTIVE_TO IS NULL
898: )
910:
911: l_start := DBMS_UTILITY.GET_TIME;
912:
913: -- Use the id from previous step for multi jurisdiction cities
914: UPDATE ZX_DATA_UPLOAD_INTERFACE a
915: SET geography_id = (SELECT b.geography_id
916: FROM ZX_DATA_UPLOAD_INTERFACE b
917: WHERE b.geography_name = a.geography_name
918: AND b.STATE_JURISDICTION_CODE = a.STATE_JURISDICTION_CODE
912:
913: -- Use the id from previous step for multi jurisdiction cities
914: UPDATE ZX_DATA_UPLOAD_INTERFACE a
915: SET geography_id = (SELECT b.geography_id
916: FROM ZX_DATA_UPLOAD_INTERFACE b
917: WHERE b.geography_name = a.geography_name
918: AND b.STATE_JURISDICTION_CODE = a.STATE_JURISDICTION_CODE
919: AND b.COUNTY_JURISDICTION_CODE = a.COUNTY_JURISDICTION_CODE
920: AND b.record_type = 6
935:
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
954:
955: l_start := DBMS_UTILITY.GET_TIME;
956:
957: -- Update the zip range with their corresponding city rows
958: UPDATE ZX_DATA_UPLOAD_INTERFACE a
959: SET (geography_id,status) = (SELECT b.geography_id, b.status
960: FROM ZX_DATA_UPLOAD_INTERFACE b
961: WHERE b.STATE_JURISDICTION_CODE = a.STATE_JURISDICTION_CODE
962: AND b.COUNTY_JURISDICTION_CODE = a.COUNTY_JURISDICTION_CODE
956:
957: -- Update the zip range with their corresponding city rows
958: UPDATE ZX_DATA_UPLOAD_INTERFACE a
959: SET (geography_id,status) = (SELECT b.geography_id, b.status
960: FROM ZX_DATA_UPLOAD_INTERFACE b
961: WHERE b.STATE_JURISDICTION_CODE = a.STATE_JURISDICTION_CODE
962: AND b.COUNTY_JURISDICTION_CODE = a.COUNTY_JURISDICTION_CODE
963: AND b.CITY_JURISDICTION_CODE = a.CITY_JURISDICTION_CODE
964: AND b.GEOGRAPHY_NAME = a.GEOGRAPHY_NAME
986:
987: l_start := DBMS_UTILITY.GET_TIME;
988:
989: -- First UPDATE the zone_geography_id for pre-existing states
990: UPDATE ZX_DATA_UPLOAD_INTERFACE x
991: SET x.zone_geography_id = (SELECT y.geography_id FROM hz_geographies y
992: WHERE y.geography_name = DECODE(p_tax_content_source,
993: 'TAXWARE','ST-'||x.COUNTRY_STATE_ABBREVIATION,
994: 'VERTEX','ST-'||x.STATE_JURISDICTION_CODE||'0000000',
999: = TO_DATE('12-31-4712', 'MM-DD-YYYY'))
1000: WHERE x.record_type = 1
1001: AND x.zone_geography_id IS NULL;
1002: -- AND EXISTS (SELECT NULL
1003: -- FROM ZX_DATA_UPLOAD_INTERFACE y
1004: -- WHERE y.STATE_JURISDICTION_CODE = x.STATE_JURISDICTION_CODE
1005: -- AND y.COUNTY_JURISDICTION_CODE = x.COUNTY_JURISDICTION_CODE
1006: -- AND y.CITY_JURISDICTION_CODE = x.CITY_JURISDICTION_CODE
1007: -- AND y.record_type in (9,10,11,12));
1017:
1018: l_start := DBMS_UTILITY.GET_TIME;
1019:
1020: -- First UPDATE the zone_geography_id for pre-existing counties
1021: UPDATE ZX_DATA_UPLOAD_INTERFACE x
1022: SET x.zone_geography_id = (SELECT y.geography_id FROM hz_geographies y
1023: WHERE y.geography_name = DECODE(p_tax_content_source,
1024: 'TAXWARE','CO-'||x.COUNTRY_STATE_ABBREVIATION||'-'||UPPER(SUBSTRB(x.GEOGRAPHY_NAME,1,21)),
1025: 'VERTEX','CO-'||x.STATE_JURISDICTION_CODE||x.COUNTY_JURISDICTION_CODE||'0000',
1031: WHERE x.record_type = 3
1032: AND x.zone_geography_id IS NULL
1033: AND x.geography_id IS NOT NULL;
1034: -- AND EXISTS (SELECT NULL
1035: -- FROM ZX_DATA_UPLOAD_INTERFACE y
1036: -- WHERE y.STATE_JURISDICTION_CODE = x.STATE_JURISDICTION_CODE
1037: -- AND y.COUNTY_JURISDICTION_CODE = x.COUNTY_JURISDICTION_CODE
1038: -- AND y.CITY_JURISDICTION_CODE = x.CITY_JURISDICTION_CODE
1039: -- AND y.record_type in (9,10,11,12));
1049:
1050: l_start := DBMS_UTILITY.GET_TIME;
1051:
1052: -- First UPDATE the zone_geography_id for pre-existing cities
1053: UPDATE ZX_DATA_UPLOAD_INTERFACE x
1054: SET x.zone_geography_id = (SELECT y.geography_id FROM hz_geographies y
1055: WHERE y.geography_name = DECODE(p_tax_content_source,
1056: 'TAXWARE','CI-'||x.COUNTRY_STATE_ABBREVIATION||'-'||UPPER(SUBSTRB(x.GEOGRAPHY_NAME,1,12))||'-'||x.CITY_JURISDICTION_CODE,
1057: 'VERTEX','CI-'||x.STATE_JURISDICTION_CODE||x.COUNTY_JURISDICTION_CODE||LPAD(x.CITY_JURISDICTION_CODE,4,'0'),
1063: WHERE x.record_type = 6
1064: AND x.zone_geography_id IS NULL
1065: AND x.geography_id IS NOT NULL;
1066: -- AND EXISTS (SELECT NULL
1067: -- FROM ZX_DATA_UPLOAD_INTERFACE y
1068: -- WHERE y.STATE_JURISDICTION_CODE = x.STATE_JURISDICTION_CODE
1069: -- AND y.COUNTY_JURISDICTION_CODE = x.COUNTY_JURISDICTION_CODE
1070: -- AND y.CITY_JURISDICTION_CODE = x.CITY_JURISDICTION_CODE
1071: -- AND y.record_type in (9,10,11,12));
1081:
1082: l_start := DBMS_UTILITY.GET_TIME;
1083:
1084: -- UPDATE the existing zone_geography_id for cities with same geocode
1085: UPDATE ZX_DATA_UPLOAD_INTERFACE x
1086: SET x.zone_geography_id = (SELECT y.zone_geography_id
1087: FROM zx_data_upload_interface y
1088: WHERE x.state_jurisdiction_code = y.state_jurisdiction_code
1089: AND x.county_jurisdiction_code = y.county_jurisdiction_code
1083:
1084: -- UPDATE the existing zone_geography_id for cities with same geocode
1085: UPDATE ZX_DATA_UPLOAD_INTERFACE x
1086: SET x.zone_geography_id = (SELECT y.zone_geography_id
1087: FROM zx_data_upload_interface y
1088: WHERE x.state_jurisdiction_code = y.state_jurisdiction_code
1089: AND x.county_jurisdiction_code = y.county_jurisdiction_code
1090: AND x.city_jurisdiction_code = y.city_jurisdiction_code
1091: AND x.geography_name <> y.geography_name
1108:
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;
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
1118: -- FROM ZX_DATA_UPLOAD_INTERFACE y
1119: -- WHERE y.STATE_JURISDICTION_CODE = x.STATE_JURISDICTION_CODE
1120: -- AND NVL(y.COUNTY_JURISDICTION_CODE,'-1') = NVL(x.COUNTY_JURISDICTION_CODE,'-1')
1121: -- AND NVL(y.CITY_JURISDICTION_CODE,'-1') = NVL(x.CITY_JURISDICTION_CODE,'-1')
1122: -- AND y.record_type in (9,10,11,12));
1132:
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
1151:
1152: l_start := DBMS_UTILITY.GET_TIME;
1153:
1154: -- UPDATE the zone geography id for non primary cities.
1155: UPDATE ZX_DATA_UPLOAD_INTERFACE x
1156: SET x.zone_geography_id = (SELECT y.zone_geography_id
1157: FROM zx_data_upload_interface y
1158: WHERE x.state_jurisdiction_code = y.state_jurisdiction_code
1159: AND x.county_jurisdiction_code = y.county_jurisdiction_code
1153:
1154: -- UPDATE the zone geography id for non primary cities.
1155: UPDATE ZX_DATA_UPLOAD_INTERFACE x
1156: SET x.zone_geography_id = (SELECT y.zone_geography_id
1157: FROM zx_data_upload_interface y
1158: WHERE x.state_jurisdiction_code = y.state_jurisdiction_code
1159: AND x.county_jurisdiction_code = y.county_jurisdiction_code
1160: AND x.city_jurisdiction_code = y.city_jurisdiction_code
1161: AND x.geography_name <> y.geography_name
1182:
1183: -- Now, UPDATE the zone_geography_id for override rates
1184: -- UPDATE only the first row as there could be multiple rates for one
1185: -- overriding jurisdiction. First case is for city overriding state/county
1186: UPDATE ZX_DATA_UPLOAD_INTERFACE x
1187: SET x.zone_geography_id = (SELECT y.geography_id FROM hz_geographies y
1188: WHERE y.geography_name = DECODE(p_tax_content_source,
1189: 'TAXWARE','CI-'||x.COUNTRY_STATE_ABBREVIATION||'-'||UPPER(SUBSTRB(x.GEOGRAPHY_NAME,1,12))||'-'||x.CITY_JURISDICTION_CODE,
1190: 'VERTEX','CI-'||x.STATE_JURISDICTION_CODE||x.COUNTY_JURISDICTION_CODE||LPAD(x.CITY_JURISDICTION_CODE,4,'0'),
1210: COUNTY_JURISDICTION_CODE,
1211: CITY_JURISDICTION_CODE
1212: ORDER BY ROWID)
1213: AS ROW_NUMBER
1214: FROM ZX_DATA_UPLOAD_INTERFACE
1215: WHERE record_type IN (9,10,11,12)
1216: AND last_updation_version > p_last_run_version
1217: AND state_jurisdiction_code IS NOT NULL
1218: AND county_jurisdiction_code IS NOT NULL
1242:
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
1262: COUNTY_JURISDICTION_CODE,
1263: CITY_JURISDICTION_CODE
1264: ORDER BY ROWID)
1265: AS ROW_NUMBER
1266: FROM ZX_DATA_UPLOAD_INTERFACE
1267: WHERE record_type IN (9,10,11,12)
1268: AND last_updation_version > p_last_run_version
1269: AND state_jurisdiction_code IS NOT NULL
1270: AND county_jurisdiction_code IS NOT NULL
1294:
1295: -- Now, UPDATE the zone_geography_id for override rates
1296: -- UPDATE only the first row as there could be multiple rates for one
1297: -- overriding jurisdiction. First case is for county overriding state
1298: UPDATE ZX_DATA_UPLOAD_INTERFACE x
1299: SET x.zone_geography_id = (SELECT y.geography_id FROM hz_geographies y
1300: WHERE y.geography_name = DECODE(p_tax_content_source,
1301: 'TAXWARE','CO-'||x.COUNTRY_STATE_ABBREVIATION||'-'||UPPER(SUBSTRB(x.GEOGRAPHY_NAME,1,21)),
1302: 'VERTEX','CO-'||x.STATE_JURISDICTION_CODE||x.COUNTY_JURISDICTION_CODE||'0000',
1320: OVER (PARTITION BY STATE_JURISDICTION_CODE,
1321: COUNTY_JURISDICTION_CODE
1322: ORDER BY ROWID)
1323: AS ROW_NUMBER
1324: FROM ZX_DATA_UPLOAD_INTERFACE
1325: WHERE record_type IN (9,10,11,12)
1326: AND last_updation_version > p_last_run_version
1327: AND state_jurisdiction_code IS NOT NULL
1328: AND county_jurisdiction_code IS NOT NULL
1348:
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
1366: OVER (PARTITION BY STATE_JURISDICTION_CODE,
1367: COUNTY_JURISDICTION_CODE
1368: ORDER BY ROWID)
1369: AS ROW_NUMBER
1370: FROM ZX_DATA_UPLOAD_INTERFACE
1371: WHERE record_type IN (9,10,11,12)
1372: AND last_updation_version > p_last_run_version
1373: AND state_jurisdiction_code IS NOT NULL
1374: AND county_jurisdiction_code IS NOT NULL
1394:
1395: l_start := DBMS_UTILITY.GET_TIME;
1396:
1397: -- First UPDATE the zone_geography_id for pre-existing cities,
1398: UPDATE ZX_DATA_UPLOAD_INTERFACE x
1399: SET x.zone_geography_id = (SELECT y.geography_id FROM hz_geographies y
1400: WHERE y.geography_name = DECODE(p_tax_content_source,
1401: 'TAXWARE','CI-'||x.COUNTRY_STATE_ABBREVIATION||'-'||UPPER(SUBSTRB(x.GEOGRAPHY_NAME,1,12))||'-'||x.CITY_JURISDICTION_CODE,
1402: 'VERTEX','CI-'||x.STATE_JURISDICTION_CODE||x.COUNTY_JURISDICTION_CODE||LPAD(x.CITY_JURISDICTION_CODE,4,'0'),
1408: = TO_DATE('12-31-4712', 'MM-DD-YYYY'))
1409: WHERE x.record_type = 6
1410: AND x.zone_geography_id IS NULL;
1411: -- AND EXISTS (SELECT NULL
1412: -- FROM ZX_DATA_UPLOAD_INTERFACE y
1413: -- WHERE y.STATE_JURISDICTION_CODE = x.STATE_JURISDICTION_CODE
1414: -- AND y.COUNTY_JURISDICTION_CODE = x.COUNTY_JURISDICTION_CODE
1415: -- AND y.CITY_JURISDICTION_CODE = x.CITY_JURISDICTION_CODE
1416: -- AND y.record_type in (9,10,11,12));
1426:
1427: l_start := DBMS_UTILITY.GET_TIME;
1428:
1429: -- UPDATE the zone_geography_id for cities
1430: UPDATE ZX_DATA_UPLOAD_INTERFACE x
1431: SET x.zone_geography_id = (SELECT y.zone_geography_id
1432: FROM zx_data_upload_interface y
1433: WHERE x.state_jurisdiction_code = y.state_jurisdiction_code
1434: AND x.county_jurisdiction_code = y.county_jurisdiction_code
1428:
1429: -- UPDATE the zone_geography_id for cities
1430: UPDATE ZX_DATA_UPLOAD_INTERFACE x
1431: SET x.zone_geography_id = (SELECT y.zone_geography_id
1432: FROM zx_data_upload_interface y
1433: WHERE x.state_jurisdiction_code = y.state_jurisdiction_code
1434: AND x.county_jurisdiction_code = y.county_jurisdiction_code
1435: AND x.city_jurisdiction_code = y.city_jurisdiction_code
1436: AND x.geography_name <> y.geography_name
1451:
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
1458: AND x.zone_geography_id IS NULL
1459: AND x.effective_to IS NULL
1460: AND x.primary_flag = 'Y';
1461: -- AND EXISTS (SELECT null
1462: -- FROM ZX_DATA_UPLOAD_INTERFACE y
1463: -- WHERE y.STATE_JURISDICTION_CODE = x.STATE_JURISDICTION_CODE
1464: -- AND y.COUNTY_JURISDICTION_CODE = x.COUNTY_JURISDICTION_CODE
1465: -- AND y.CITY_JURISDICTION_CODE = x.CITY_JURISDICTION_CODE
1466: -- AND y.record_type in (9,10,11,12));
1473: FND_FILE.LOG,
1474: l_log
1475: );
1476:
1477: UPDATE ZX_DATA_UPLOAD_INTERFACE x
1478: SET x.zone_geography_id = (SELECT y.zone_geography_id
1479: FROM zx_data_upload_interface y
1480: WHERE x.state_jurisdiction_code = y.state_jurisdiction_code
1481: AND x.county_jurisdiction_code = y.county_jurisdiction_code
1475: );
1476:
1477: UPDATE ZX_DATA_UPLOAD_INTERFACE x
1478: SET x.zone_geography_id = (SELECT y.zone_geography_id
1479: FROM zx_data_upload_interface y
1480: WHERE x.state_jurisdiction_code = y.state_jurisdiction_code
1481: AND x.county_jurisdiction_code = y.county_jurisdiction_code
1482: AND x.city_jurisdiction_code = y.city_jurisdiction_code
1483: AND x.geography_name <> y.geography_name
1522: state_jurisdiction_code,
1523: state_jurisdiction_code||county_jurisdiction_code||city_jurisdiction_code||geography_name concat_code,
1524: zip_begin,
1525: zip_end
1526: FROM zx_data_upload_interface
1527: WHERE record_type = 08
1528: AND last_updation_version > p_last_run_version
1529: AND effective_to IS NULL;
1530:
1626: 'PERCENTAGE' rate_type_code,
1627: decode(record_type,9,sales_tax_rate,10,rental_tax_rate,11,use_tax_rate,12,lease_tax_rate) percentage_rate,
1628: decode(to_char(record_type),'9',sales_tax_rate_active_flag,'10',rental_tax_rate_active_flag,'11',use_tax_rate_active_flag,'12',lease_tax_rate_active_flag) active_flag
1629: FROM
1630: zx_data_upload_interface
1631: WHERE record_type in (9,10,11,12)
1632: AND last_updation_version > p_last_run_version
1633: ) x
1634: ) v,
1631: WHERE record_type in (9,10,11,12)
1632: AND last_updation_version > p_last_run_version
1633: ) x
1634: ) v,
1635: zx_data_upload_interface jur
1636: WHERE jur.record_type = decode(v.city_jurisdiction_code,null,decode(v.county_jurisdiction_code,null,1,3),6)
1637: AND jur.state_jurisdiction_code = v.state_jurisdiction_code
1638: AND NVL(jur.county_jurisdiction_code,'-1') = NVL(v.county_jurisdiction_code,'-1')
1639: AND NVL(jur.city_jurisdiction_code,'-1') = NVL(v.city_jurisdiction_code,'-1')
1723: -99 content_owner_id,
1724: decode(p_migrated_tax_regime_flag,'Y','STANDARD','STD')||decode(to_char(record_type),'10','-RENTAL','11','-USE','12','-LEASE') tax_rate_code,
1725: effective_from,
1726: decode(to_char(record_type),'9',sales_tax_rate_active_flag,'10',rental_tax_rate_active_flag,'11',use_tax_rate_active_flag,'12',lease_tax_rate_active_flag) active_flag
1727: FROM zx_data_upload_interface
1728: WHERE record_type in (9,10,11,12)
1729: AND NVL(status,'CREATE') <> 'ERROR'
1730: AND last_updation_version > p_last_run_version
1731: ) x
1729: AND NVL(status,'CREATE') <> 'ERROR'
1730: AND last_updation_version > p_last_run_version
1731: ) x
1732: ) v,
1733: zx_data_upload_interface jur
1734: WHERE jur.record_type = DECODE(v.city_jurisdiction_code,NULL,DECODE(v.county_jurisdiction_code,NULL,1,3),6)
1735: AND jur.state_jurisdiction_code = v.state_jurisdiction_code
1736: AND NVL(jur.county_jurisdiction_code,'-1') = NVL(v.county_jurisdiction_code,'-1')
1737: AND NVL(jur.city_jurisdiction_code,'-1') = NVL(v.city_jurisdiction_code,'-1')
1785: city_jurisdiction_code,
1786: DECODE(to_char(record_type),'9',sales_tax_authority_level,'10',rental_tax_authority_level,'11',use_tax_authority_level,'12',lease_tax_authority_level) tax,
1787: effective_from,
1788: DECODE(TO_CHAR(record_type),'9',sales_tax_rate_active_flag,'10',rental_tax_rate_active_flag,'11',use_tax_rate_active_flag,'12',lease_tax_rate_active_flag) active_flag
1789: FROM zx_data_upload_interface
1790: WHERE record_type in (9,10,11,12)
1791: AND NVL(status,'CREATE') <> 'ERROR' ) v,
1792: zx_data_upload_interface v1
1793: WHERE v1.record_type = DECODE(v.city_jurisdiction_code,NULL,DECODE(v.county_jurisdiction_code,NULL,1,3),6)
1788: DECODE(TO_CHAR(record_type),'9',sales_tax_rate_active_flag,'10',rental_tax_rate_active_flag,'11',use_tax_rate_active_flag,'12',lease_tax_rate_active_flag) active_flag
1789: FROM zx_data_upload_interface
1790: WHERE record_type in (9,10,11,12)
1791: AND NVL(status,'CREATE') <> 'ERROR' ) v,
1792: zx_data_upload_interface v1
1793: WHERE v1.record_type = DECODE(v.city_jurisdiction_code,NULL,DECODE(v.county_jurisdiction_code,NULL,1,3),6)
1794: AND v1.state_jurisdiction_code = v.state_jurisdiction_code
1795: AND NVL(v1.county_jurisdiction_code,'-1') = NVL(v.county_jurisdiction_code,'-1')
1796: AND NVL(v1.city_jurisdiction_code,'-1') = NVL(v.city_jurisdiction_code,'-1')
1864: (l_previous_zip_begin(i) BETWEEN ref_zip.zip_begin AND ref_zip.zip_end))
1865: THEN
1866: l_msg := 'Overlapping zip range exists for the state: '||ref_zip.state_jurisdiction_code||', county: '||ref_zip.county_jurisdiction_code||', city : '||ref_zip.city_jurisdiction_code||'.';
1867: /* Commented for Bug#7298430
1868: UPDATE zx_data_upload_interface
1869: SET STATUS = 'ERROR',
1870: ERROR_MESSAGE = l_msg
1871: WHERE ROWID = ref_zip.row_id;
1872: FND_FILE.PUT_LINE
1916: THEN
1917: l_msg := l_msg||', city: '||ref_rates.city_jurisdiction_code;
1918: END IF;
1919: l_msg := l_msg||' has effective date of '||to_char(ref_rates.new_effective_from,'MM/DD/YYYY')||', which is earlier than the tax zone''s effective date '||to_char(ref_rates.jur_effective_from,'MM/DD/YYYY')||'.';
1920: UPDATE zx_data_upload_interface
1921: SET STATUS = 'ERROR',
1922: ERROR_MESSAGE = l_msg
1923: WHERE ROWID = ref_rates.row_id;
1924: FND_FILE.PUT_LINE
2039: l_msg := l_msg||', city: '||ref_rates.city_jurisdiction_code;
2040: END IF;
2041: l_msg := l_msg||'.';
2042: /* -- Commented for Bug#7298430
2043: UPDATE zx_data_upload_interface
2044: SET STATUS = 'ERROR',
2045: ERROR_MESSAGE = l_msg
2046: WHERE ROWID = ref_rates.row_id;
2047: FND_FILE.PUT_LINE
2085: l_msg := l_msg||', city: '||ref_rates.city_jurisdiction_code;
2086: END IF;
2087: l_msg := l_msg||' has already been ended by user.';
2088: /* -- Commented for Bug#7298430
2089: UPDATE zx_data_upload_interface
2090: SET STATUS = 'ERROR',
2091: ERROR_MESSAGE = l_msg
2092: WHERE ROWID = ref_rates.row_id;
2093: FND_FILE.PUT_LINE
2194: -- End : Added for Bug#7527399
2195:
2196: -- Start : Added for Bug#7298430
2197: FORALL i IN INDICES OF l_upload_rec.row_id
2198: UPDATE zx_data_upload_interface
2199: SET STATUS = l_upload_rec.status(i),
2200: ERROR_MESSAGE = l_upload_rec.log_msg(i)
2201: WHERE ROWID = l_upload_rec.row_id(i);
2202:
2244: -- Ad parallelization variables
2245: -----------------------------------------------------
2246: l_table_owner VARCHAR2(30) := 'ZX';
2247: l_any_rows_to_process BOOLEAN;
2248: l_table_name VARCHAR2(30) := 'ZX_DATA_UPLOAD_INTERFACE';
2249: l_start_rowid ROWID;
2250: l_end_rowid ROWID;
2251: l_rows_processed NUMBER;
2252:
2968: state.status,
2969: (SELECT hzg.geography_id
2970: FROM HZ_GEOGRAPHIES hzg
2971: WHERE hzg.geography_id = state.geography_id) existing_geography_id
2972: FROM zx_data_upload_interface state
2973: WHERE state.record_type = 01
2974: AND state.LAST_UPDATION_VERSION > p_last_run_version
2975: AND state.geography_id IS NOT NULL
2976: AND nvl(state.status,'ERROR') IN ('CREATE','UPDATE')
3007: county.status,
3008: (SELECT hzg.geography_id
3009: FROM HZ_GEOGRAPHIES hzg
3010: WHERE hzg.geography_id = county.geography_id) existing_geography_id
3011: FROM zx_data_upload_interface county,
3012: zx_data_upload_interface state
3013: WHERE county.record_type = 03
3014: AND county.LAST_UPDATION_VERSION > p_last_run_version
3015: AND county.geography_id IS NOT NULL
3008: (SELECT hzg.geography_id
3009: FROM HZ_GEOGRAPHIES hzg
3010: WHERE hzg.geography_id = county.geography_id) existing_geography_id
3011: FROM zx_data_upload_interface county,
3012: zx_data_upload_interface state
3013: WHERE county.record_type = 03
3014: AND county.LAST_UPDATION_VERSION > p_last_run_version
3015: AND county.geography_id IS NOT NULL
3016: AND nvl(county.status,'ERROR') IN ('CREATE','UPDATE')
3052: city.status,
3053: (SELECT hzg.geography_id
3054: FROM HZ_GEOGRAPHIES hzg
3055: WHERE hzg.geography_id = city.geography_id) existing_geography_id
3056: FROM zx_data_upload_interface city,
3057: zx_data_upload_interface county,
3058: zx_data_upload_interface state
3059: WHERE city.record_type = 06
3060: AND city.LAST_UPDATION_VERSION > p_last_run_version
3053: (SELECT hzg.geography_id
3054: FROM HZ_GEOGRAPHIES hzg
3055: WHERE hzg.geography_id = city.geography_id) existing_geography_id
3056: FROM zx_data_upload_interface city,
3057: zx_data_upload_interface county,
3058: zx_data_upload_interface state
3059: WHERE city.record_type = 06
3060: AND city.LAST_UPDATION_VERSION > p_last_run_version
3061: AND city.geography_id IS NOT NULL
3054: FROM HZ_GEOGRAPHIES hzg
3055: WHERE hzg.geography_id = city.geography_id) existing_geography_id
3056: FROM zx_data_upload_interface city,
3057: zx_data_upload_interface county,
3058: zx_data_upload_interface state
3059: WHERE city.record_type = 06
3060: AND city.LAST_UPDATION_VERSION > p_last_run_version
3061: AND city.geography_id IS NOT NULL
3062: AND nvl(city.status,'ERROR') IN ('CREATE','UPDATE')
3082:
3083: /*
3084: SELECT COUNT(*)
3085: INTO l_rows_processed
3086: FROM zx_data_upload_interface
3087: WHERE rowid between l_start_rowid and l_end_rowid;
3088: --l_rows_processed := SQL%ROWCOUNT;
3089:
3090: ad_parallel_updates_pkg.processed_rowid_range(l_rows_processed,l_end_rowid);*/
3147: -- Ad parallelization variables
3148: -----------------------------------------------------
3149: l_table_owner VARCHAR2(30) := 'ZX';
3150: l_any_rows_to_process BOOLEAN;
3151: l_table_name VARCHAR2(30) := 'ZX_DATA_UPLOAD_INTERFACE';
3152: l_start_rowid ROWID;
3153: l_end_rowid ROWID;
3154: l_rows_processed NUMBER;
3155:
3523: 275 precedence_level,
3524: 'N' inner_city_flag,
3525: 1 CITY_ROW_NUMBER,
3526: (SELECT /*+first_rows(1)*/ 1
3527: FROM zx_data_upload_interface rate
3528: WHERE rate.record_type IN (09,10,11,12)
3529: AND rate.state_jurisdiction_code = inter.state_jurisdiction_code
3530: AND rate.county_jurisdiction_code IS NULL
3531: AND rate.city_jurisdiction_code IS NULL
3531: AND rate.city_jurisdiction_code IS NULL
3532: AND rate.LAST_UPDATION_VERSION > p_last_run_version
3533: AND ROWNUM = 1) existing_tax_rate,
3534: 'Y' primary_flag
3535: FROM zx_data_upload_interface inter
3536: WHERE inter.record_type = 01
3537: AND inter.geography_id IS NOT NULL
3538: AND inter.effective_to IS NULL
3539: AND inter.LAST_UPDATION_VERSION > p_last_run_version
3556: 275 precedence_level,
3557: 'N' inner_city_flag,
3558: 1 CITY_ROW_NUMBER,
3559: (SELECT /*+first_rows(1)*/ 1
3560: FROM zx_data_upload_interface rate
3561: WHERE rate.record_type IN (09,10,11,12)
3562: AND rate.state_jurisdiction_code = inter.state_jurisdiction_code
3563: AND rate.county_jurisdiction_code IS NULL
3564: AND rate.city_jurisdiction_code IS NULL
3564: AND rate.city_jurisdiction_code IS NULL
3565: AND rate.LAST_UPDATION_VERSION > p_last_run_version
3566: AND ROWNUM = 1) existing_tax_rate,
3567: 'Y' primary_flag
3568: FROM zx_data_upload_interface inter
3569: WHERE inter.record_type = 01
3570: AND inter.zone_geography_id IS NOT NULL
3571: AND inter.effective_to IS NULL
3572: AND p_tax_zone_type IS NULL -- Means migrated regime
3570: AND inter.zone_geography_id IS NOT NULL
3571: AND inter.effective_to IS NULL
3572: AND p_tax_zone_type IS NULL -- Means migrated regime
3573: -- AND EXISTS (SELECT NULL
3574: -- FROM zx_data_upload_interface rate
3575: -- WHERE rate.record_type IN (09,10,11,12)
3576: -- AND rate.state_jurisdiction_code = inter.state_jurisdiction_code
3577: -- AND rate.county_jurisdiction_code IS NULL
3578: -- AND rate.city_jurisdiction_code IS NULL
3595: 175 precedence_level,
3596: 'N' inner_city_flag,
3597: 1 CITY_ROW_NUMBER,
3598: (SELECT /*+first_rows(1)*/ 1
3599: FROM zx_data_upload_interface rate
3600: WHERE rate.record_type IN (09,10,11,12)
3601: AND rate.state_jurisdiction_code = inter.state_jurisdiction_code
3602: AND rate.county_jurisdiction_code = inter.county_jurisdiction_code
3603: AND rate.city_jurisdiction_code IS NULL
3603: AND rate.city_jurisdiction_code IS NULL
3604: AND rate.LAST_UPDATION_VERSION > p_last_run_version
3605: AND ROWNUM = 1) existing_tax_rate,
3606: 'Y' primary_flag
3607: FROM zx_data_upload_interface inter
3608: WHERE inter.record_type = 03
3609: AND inter.geography_id IS NOT NULL
3610: AND inter.effective_to IS NULL
3611: AND inter.LAST_UPDATION_VERSION > p_last_run_version
3628: 175 precedence_level,
3629: 'N' inner_city_flag,
3630: 1 CITY_ROW_NUMBER,
3631: (SELECT /*+first_rows(1)*/ 1
3632: FROM zx_data_upload_interface rate
3633: WHERE rate.record_type IN (09,10,11,12)
3634: AND rate.state_jurisdiction_code = inter.state_jurisdiction_code
3635: AND rate.county_jurisdiction_code = inter.county_jurisdiction_code
3636: AND rate.city_jurisdiction_code IS NULL
3636: AND rate.city_jurisdiction_code IS NULL
3637: AND rate.LAST_UPDATION_VERSION > p_last_run_version
3638: AND ROWNUM = 1) existing_tax_rate,
3639: 'Y' primary_flag
3640: FROM zx_data_upload_interface inter
3641: WHERE inter.record_type = 03
3642: AND inter.zone_geography_id IS NOT NULL
3643: AND inter.effective_to IS NULL
3644: AND p_tax_zone_type IS NULL -- Means migrated regime
3642: AND inter.zone_geography_id IS NOT NULL
3643: AND inter.effective_to IS NULL
3644: AND p_tax_zone_type IS NULL -- Means migrated regime
3645: -- AND EXISTS (SELECT NULL
3646: -- FROM zx_data_upload_interface rate
3647: -- WHERE rate.record_type IN (09,10,11,12)
3648: -- AND rate.state_jurisdiction_code = inter.state_jurisdiction_code
3649: -- AND rate.county_jurisdiction_code = inter.county_jurisdiction_code
3650: -- AND rate.city_jurisdiction_code IS NULL
3667: 75 precedence_level,
3668: DECODE(TO_CHAR(inter.JURISDICTION_SERIAL_NUMBER),'1','Y','N') inner_city_flag,
3669: 1 CITY_ROW_NUMBER,
3670: (SELECT /*+first_rows(1)*/ 1
3671: FROM zx_data_upload_interface rate
3672: WHERE rate.record_type IN (09,10,11,12)
3673: AND rate.state_jurisdiction_code = inter.state_jurisdiction_code
3674: AND rate.county_jurisdiction_code = inter.county_jurisdiction_code
3675: AND rate.city_jurisdiction_code = inter.city_jurisdiction_code
3675: AND rate.city_jurisdiction_code = inter.city_jurisdiction_code
3676: AND rate.LAST_UPDATION_VERSION > p_last_run_version
3677: AND ROWNUM = 1) existing_tax_rate,
3678: primary_flag
3679: FROM zx_data_upload_interface inter
3680: WHERE inter.record_type = 06
3681: AND inter.zone_geography_id IS NOT NULL
3682: AND inter.effective_to IS NULL
3683: -- cities should be considered always as they might have been created earlier but their zip range or rates are sent for the first time
3681: AND inter.zone_geography_id IS NOT NULL
3682: AND inter.effective_to IS NULL
3683: -- cities should be considered always as they might have been created earlier but their zip range or rates are sent for the first time
3684: -- AND EXISTS (SELECT NULL
3685: -- FROM zx_data_upload_interface rate
3686: -- WHERE rate.record_type IN (08,09,10,11,12)
3687: -- AND rate.state_jurisdiction_code = inter.state_jurisdiction_code
3688: -- AND rate.county_jurisdiction_code = inter.county_jurisdiction_code
3689: -- AND rate.city_jurisdiction_code = inter.city_jurisdiction_code
3718: ,DECODE(z.primary_flag,'N',2,1)
3719: ) AS CITY_ROW_NUMBER,
3720: 1 existing_tax_rate,
3721: z.primary_flag
3722: FROM zx_data_upload_interface inter,
3723: zx_data_upload_interface z
3724: WHERE inter.record_type IN (09,10,11,12)
3725: AND (inter.SALES_TAX_AUTHORITY_LEVEL = 'STATE'
3726: OR inter.SALES_TAX_AUTHORITY_LEVEL = 'COUNTY'
3719: ) AS CITY_ROW_NUMBER,
3720: 1 existing_tax_rate,
3721: z.primary_flag
3722: FROM zx_data_upload_interface inter,
3723: zx_data_upload_interface z
3724: WHERE inter.record_type IN (09,10,11,12)
3725: AND (inter.SALES_TAX_AUTHORITY_LEVEL = 'STATE'
3726: OR inter.SALES_TAX_AUTHORITY_LEVEL = 'COUNTY'
3727: OR inter.RENTAL_TAX_AUTHORITY_LEVEL = 'STATE'
3762: 'N' inner_city_flag,
3763: 1 CITY_ROW_NUMBER,
3764: 1 existing_tax_rate,
3765: 'Y' primary_flag
3766: FROM zx_data_upload_interface inter,
3767: zx_data_upload_interface z
3768: WHERE inter.record_type IN (09,10,11,12)
3769: AND (inter.SALES_TAX_AUTHORITY_LEVEL = 'STATE'
3770: OR inter.RENTAL_TAX_AUTHORITY_LEVEL = 'STATE'
3763: 1 CITY_ROW_NUMBER,
3764: 1 existing_tax_rate,
3765: 'Y' primary_flag
3766: FROM zx_data_upload_interface inter,
3767: zx_data_upload_interface z
3768: WHERE inter.record_type IN (09,10,11,12)
3769: AND (inter.SALES_TAX_AUTHORITY_LEVEL = 'STATE'
3770: OR inter.RENTAL_TAX_AUTHORITY_LEVEL = 'STATE'
3771: OR inter.USE_TAX_AUTHORITY_LEVEL = 'STATE'
3910: DECODE(hg.geography_element4,null,'',SUBSTR(hg.geography_element4,1,30)||'-')
3911: FROM hz_geographies hg_zone,
3912: hz_relationships hr,
3913: hz_geographies hg,
3914: zx_data_upload_interface zd
3915: WHERE hg_zone.GEOGRAPHY_ID = zjb.zone_geography_id
3916: AND zd.zone_geography_id = hg_zone.GEOGRAPHY_ID
3917: AND NVL(zd.primary_flag,'Y') = 'Y'
3918: AND hr.SUBJECT_ID = hg_zone.GEOGRAPHY_ID
3986: ) hgr_row_id,
3987: ROW_NUMBER()
3988: OVER (PARTITION BY zip.zip_begin, city.zone_geography_id,city.JURISDICTION_SERIAL_NUMBER,
3989: zip.effective_from order by zip.zip_end DESC ) as postal_code_num
3990: FROM ZX_DATA_UPLOAD_INTERFACE zip,
3991: ZX_DATA_UPLOAD_INTERFACE city
3992: WHERE zip.record_type = 08
3993: AND city.record_type = 06
3994: AND city.STATE_JURISDICTION_CODE = zip.STATE_JURISDICTION_CODE
3987: ROW_NUMBER()
3988: OVER (PARTITION BY zip.zip_begin, city.zone_geography_id,city.JURISDICTION_SERIAL_NUMBER,
3989: zip.effective_from order by zip.zip_end DESC ) as postal_code_num
3990: FROM ZX_DATA_UPLOAD_INTERFACE zip,
3991: ZX_DATA_UPLOAD_INTERFACE city
3992: WHERE zip.record_type = 08
3993: AND city.record_type = 06
3994: AND city.STATE_JURISDICTION_CODE = zip.STATE_JURISDICTION_CODE
3995: AND city.COUNTY_JURISDICTION_CODE = zip.COUNTY_JURISDICTION_CODE
4099: nvl(effective_to,to_date('12/31/4712','mm/dd/yyyy')) end_date,
4100: DECODE(TO_CHAR(record_type),'1','US_STATE_ZONE_TYPE_'||SUBSTRB(p_tax_regime_code, 14,10),
4101: '3','US_COUNTY_ZONE_TYPE_'||SUBSTRB(p_tax_regime_code, 14,10)) zone_geography_type,
4102: geography_id
4103: FROM ZX_DATA_UPLOAD_INTERFACE inter
4104: WHERE record_type in (1,3)
4105: AND zone_geography_id IS NOT NULL
4106: AND NOT EXISTS (SELECT NULL
4107: FROM hz_geography_ranges hgr
4170: -- Ad parallelization variables
4171: -----------------------------------------------------
4172: l_table_owner VARCHAR2(30) := 'ZX';
4173: l_any_rows_to_process BOOLEAN;
4174: l_table_name VARCHAR2(30) := 'ZX_DATA_UPLOAD_INTERFACE';
4175: l_start_rowid ROWID;
4176: l_end_rowid ROWID;
4177: l_rows_processed NUMBER;
4178:
4747: effective_from,
4748: effective_to,
4749: zip_begin,
4750: zip_end
4751: from zx_data_upload_interface
4752: where record_type = 08
4753: and last_updation_version > p_last_run_version
4754: and city_jurisdiction_code is not null
4755: and geography_id is not null
4870: -- Ad parallelization variables
4871: -----------------------------------------------------
4872: l_table_owner VARCHAR2(30) := 'ZX';
4873: l_any_rows_to_process BOOLEAN;
4874: l_table_name VARCHAR2(30) := 'ZX_DATA_UPLOAD_INTERFACE';
4875: l_start_rowid ROWID;
4876: l_end_rowid ROWID;
4877: l_rows_processed NUMBER;
4878:
4953: )
4954: SELECT DISTINCT inter.geography_id,
4955: inter.geography_name geography_name,
4956: 'CITY' geography_type
4957: FROM ZX_DATA_UPLOAD_INTERFACE inter
4958: WHERE inter.record_type = 07
4959: AND inter.last_updation_version > p_last_run_version
4960: AND inter.geography_id IS NOT NULL
4961: AND NOT EXISTS (SELECT 1
5039: WHERE tax_regime_code = b_regime_code;
5040:
5041: l_table_owner VARCHAR2(30) := 'ZX';
5042: l_any_rows_to_process BOOLEAN;
5043: l_table_name VARCHAR2(30) := 'ZX_DATA_UPLOAD_INTERFACE';
5044: l_start_rowid ROWID;
5045: l_end_rowid ROWID;
5046: l_rows_processed NUMBER;
5047: l_migrated_tax_regime_flag VARCHAR2(1);
5196: '6',jur.state_jurisdiction_code||jur.county_jurisdiction_code||TRIM(SUBSTR(jur.city_jurisdiction_code,1,19))))
5197: AND tax_rate_code = decode(l_migrated_tax_regime_flag,'Y','STANDARD','STD')||decode(to_char(rt.record_type),'10','-RENTAL','11','-USE','12','-LEASE')
5198: AND effective_from = decode(greatest(rt.effective_from,G_RECORD_EFFECTIVE_START),rt.effective_from,rt.effective_from,G_RECORD_EFFECTIVE_START)
5199: ) l_count
5200: FROM zx_data_upload_interface rt,
5201: zx_data_upload_interface jur
5202: where rt.record_type in (9,10,11,12)
5203: and rt.last_updation_version > p_last_run_version
5204: and nvl(rt.status,'CREATE') <> 'ERROR'
5197: AND tax_rate_code = decode(l_migrated_tax_regime_flag,'Y','STANDARD','STD')||decode(to_char(rt.record_type),'10','-RENTAL','11','-USE','12','-LEASE')
5198: AND effective_from = decode(greatest(rt.effective_from,G_RECORD_EFFECTIVE_START),rt.effective_from,rt.effective_from,G_RECORD_EFFECTIVE_START)
5199: ) l_count
5200: FROM zx_data_upload_interface rt,
5201: zx_data_upload_interface jur
5202: where rt.record_type in (9,10,11,12)
5203: and rt.last_updation_version > p_last_run_version
5204: and nvl(rt.status,'CREATE') <> 'ERROR'
5205: and jur.record_type = decode(rt.city_jurisdiction_code,null,decode(rt.county_jurisdiction_code,null,1,3),6)
5408: '6',jur.state_jurisdiction_code||jur.county_jurisdiction_code||TRIM(SUBSTR(jur.city_jurisdiction_code,1,19))))
5409: AND tax_rate_code = decode(l_migrated_tax_regime_flag,'Y','STANDARD','STD')||decode(to_char(rt.record_type),'10','-RENTAL','11','-USE','12','-LEASE')
5410: AND effective_from = decode(greatest(rt.effective_from,G_RECORD_EFFECTIVE_START),rt.effective_from,rt.effective_from,G_RECORD_EFFECTIVE_START)
5411: ) l_count
5412: FROM zx_data_upload_interface rt,
5413: zx_data_upload_interface jur
5414: where rt.record_type in (9,10,11,12)
5415: and rt.last_updation_version > p_last_run_version
5416: and nvl(rt.status,'CREATE') <> 'ERROR'
5409: AND tax_rate_code = decode(l_migrated_tax_regime_flag,'Y','STANDARD','STD')||decode(to_char(rt.record_type),'10','-RENTAL','11','-USE','12','-LEASE')
5410: AND effective_from = decode(greatest(rt.effective_from,G_RECORD_EFFECTIVE_START),rt.effective_from,rt.effective_from,G_RECORD_EFFECTIVE_START)
5411: ) l_count
5412: FROM zx_data_upload_interface rt,
5413: zx_data_upload_interface jur
5414: where rt.record_type in (9,10,11,12)
5415: and rt.last_updation_version > p_last_run_version
5416: and nvl(rt.status,'CREATE') <> 'ERROR'
5417: and jur.record_type = decode(rt.city_jurisdiction_code,null,decode(rt.county_jurisdiction_code,null,1,3),6)
6156:
6157: CURSOR c_get_max_version
6158: IS
6159: SELECT MAX(LAST_UPDATION_VERSION)
6160: FROM ZX_DATA_UPLOAD_INTERFACE;
6161:
6162: CURSOR c_get_regime_name
6163: (
6164: b_tax_regime_code VARCHAR2
6307:
6308: CURSOR c_check_data
6309: IS
6310: SELECT COUNT(*)
6311: FROM ZX_DATA_UPLOAD_INTERFACE;
6312:
6313: l_api_name CONSTANT VARCHAR2(30):= 'load_file';
6314: l_error EXCEPTION;
6315: l_request_id NUMBER;