[Home] [Help]
595: ) IS
596: SELECT DISTINCT
597: X.ROWID,
598: Y.GEOGRAPHY_ID
599: FROM ZX_DATA_UPLOAD_INTERFACE X,
600: HZ_GEOGRAPHIES Y,
601: ZX_DATA_UPLOAD_INTERFACE Z,
602: ZX_DATA_UPLOAD_INTERFACE ZZ
603: WHERE X.RECORD_TYPE = 6
597: X.ROWID,
598: Y.GEOGRAPHY_ID
599: FROM ZX_DATA_UPLOAD_INTERFACE X,
600: HZ_GEOGRAPHIES Y,
601: ZX_DATA_UPLOAD_INTERFACE Z,
602: ZX_DATA_UPLOAD_INTERFACE ZZ
603: WHERE X.RECORD_TYPE = 6
604: AND UPPER(Y.GEOGRAPHY_NAME) = UPPER(X.GEOGRAPHY_NAME)
605: AND Y.GEOGRAPHY_USE = 'MASTER_REF'
598: Y.GEOGRAPHY_ID
599: FROM ZX_DATA_UPLOAD_INTERFACE X,
600: HZ_GEOGRAPHIES Y,
601: ZX_DATA_UPLOAD_INTERFACE Z,
602: ZX_DATA_UPLOAD_INTERFACE ZZ
603: WHERE X.RECORD_TYPE = 6
604: AND UPPER(Y.GEOGRAPHY_NAME) = UPPER(X.GEOGRAPHY_NAME)
605: AND Y.GEOGRAPHY_USE = 'MASTER_REF'
606: AND Y.GEOGRAPHY_TYPE = 'CITY'
623: l_start := DBMS_UTILITY.GET_TIME;
624:
625: -- Find the state geography id using abbreviation code. Note that since we
626: -- are using code, even the name change records will get the geography_id.
627: update ZX_DATA_UPLOAD_INTERFACE x
628: set x.geography_id = (SELECT Y.GEOGRAPHY_ID
629: FROM HZ_GEOGRAPHIES Y
630: WHERE Y.GEOGRAPHY_NAME = X.COUNTRY_STATE_ABBREVIATION
631: AND Y.GEOGRAPHY_CODE = X.COUNTRY_STATE_ABBREVIATION
647:
648: l_start := DBMS_UTILITY.GET_TIME;
649:
650: -- Now update the status of the name change record.
651: update ZX_DATA_UPLOAD_INTERFACE x
652: set x.status = 'UPDATE'
653: where x.record_type = 1
654: and x.effective_to IS NULL
655: and exists (select null
652: set x.status = 'UPDATE'
653: where x.record_type = 1
654: and x.effective_to IS NULL
655: and exists (select null
656: from ZX_DATA_UPLOAD_INTERFACE y
657: where y.record_type = 1
658: and y.state_jurisdiction_code = x.state_jurisdiction_code
659: and y.country_state_abbreviation = x.country_state_abbreviation
660: and y.effective_to IS NOT NULL);
672:
673: -- Geography Id's for County for pre-existing geographies
674: -- The state record could have been ended or it could have been sent twice
675: -- with name change, so rownum clause is used.
676: update ZX_DATA_UPLOAD_INTERFACE x
677: set x.geography_id = (SELECT Y.GEOGRAPHY_ID
678: FROM HZ_GEOGRAPHIES Y,
679: ZX_DATA_UPLOAD_INTERFACE Z
680: WHERE UPPER(Y.GEOGRAPHY_NAME) = UPPER(X.GEOGRAPHY_NAME)
675: -- with name change, so rownum clause is used.
676: update ZX_DATA_UPLOAD_INTERFACE x
677: set x.geography_id = (SELECT Y.GEOGRAPHY_ID
678: FROM HZ_GEOGRAPHIES Y,
679: ZX_DATA_UPLOAD_INTERFACE Z
680: WHERE UPPER(Y.GEOGRAPHY_NAME) = UPPER(X.GEOGRAPHY_NAME)
681: AND Y.GEOGRAPHY_USE = 'MASTER_REF'
682: AND Y.GEOGRAPHY_TYPE = 'COUNTY'
683: AND Y.GEOGRAPHY_ELEMENT1_ID = L_CNTRY_GEOGRAPHY_ID
700: l_start := DBMS_UTILITY.GET_TIME;
701:
702: -- Geography Id's for County for pre-existing geographies, which had name
703: -- change. Use existing record with old name to find the id.
704: update ZX_DATA_UPLOAD_INTERFACE x
705: set x.geography_id = (SELECT Y.GEOGRAPHY_ID
706: FROM ZX_DATA_UPLOAD_INTERFACE y
707: WHERE y.record_type = 3
708: AND y.STATE_JURISDICTION_CODE = x.STATE_JURISDICTION_CODE
702: -- Geography Id's for County for pre-existing geographies, which had name
703: -- change. Use existing record with old name to find the id.
704: update ZX_DATA_UPLOAD_INTERFACE x
705: set x.geography_id = (SELECT Y.GEOGRAPHY_ID
706: FROM ZX_DATA_UPLOAD_INTERFACE y
707: WHERE y.record_type = 3
708: AND y.STATE_JURISDICTION_CODE = x.STATE_JURISDICTION_CODE
709: AND y.COUNTY_JURISDICTION_CODE = x.COUNTY_JURISDICTION_CODE
710: AND y.geography_id is not null),
729: -- or city with multiple counties has been created as multiple geographies.
730: -- rownum = 1 clause is used as the there could be multiple county
731: -- or state records with different effective dates
732: /**
733: update ZX_DATA_UPLOAD_INTERFACE x
734: set x.geography_id = (SELECT Y.GEOGRAPHY_ID
735: FROM HZ_GEOGRAPHIES Y,
736: ZX_DATA_UPLOAD_INTERFACE Z,
737: ZX_DATA_UPLOAD_INTERFACE ZZ
732: /**
733: update ZX_DATA_UPLOAD_INTERFACE x
734: set x.geography_id = (SELECT Y.GEOGRAPHY_ID
735: FROM HZ_GEOGRAPHIES Y,
736: ZX_DATA_UPLOAD_INTERFACE Z,
737: ZX_DATA_UPLOAD_INTERFACE ZZ
738: WHERE UPPER(Y.GEOGRAPHY_NAME) = UPPER(X.GEOGRAPHY_NAME)
739: AND Y.GEOGRAPHY_USE = 'MASTER_REF'
740: AND Y.GEOGRAPHY_TYPE = 'CITY'
733: update ZX_DATA_UPLOAD_INTERFACE x
734: set x.geography_id = (SELECT Y.GEOGRAPHY_ID
735: FROM HZ_GEOGRAPHIES Y,
736: ZX_DATA_UPLOAD_INTERFACE Z,
737: ZX_DATA_UPLOAD_INTERFACE ZZ
738: WHERE UPPER(Y.GEOGRAPHY_NAME) = UPPER(X.GEOGRAPHY_NAME)
739: AND Y.GEOGRAPHY_USE = 'MASTER_REF'
740: AND Y.GEOGRAPHY_TYPE = 'CITY'
741: AND Y.GEOGRAPHY_ELEMENT1_ID = L_CNTRY_GEOGRAPHY_ID
758: bulk collect into l_rowids, l_geography_ids;
759: l_rowcount := c_get_city_rows%rowcount;
760: l_rows_processed := l_rows_processed + l_rowcount;
761: forall i in l_rowids.first..l_rowids.last
762: update zx_data_upload_interface
763: set geography_id = l_geography_ids(i),
764: status = 'NOCHANGE'
765: where rowid = l_rowids(i);
766: l_end := DBMS_UTILITY.GET_TIME;
774: l_start := DBMS_UTILITY.GET_TIME;
775:
776: -- Geography Id's for City for pre-existing geographies, which had name
777: -- change. Use existing record with old name to find the id.
778: update ZX_DATA_UPLOAD_INTERFACE x
779: set x.geography_id = (SELECT Y.GEOGRAPHY_ID
780: FROM ZX_DATA_UPLOAD_INTERFACE y
781: WHERE y.record_type = 6
782: AND y.STATE_JURISDICTION_CODE = x.STATE_JURISDICTION_CODE
776: -- Geography Id's for City for pre-existing geographies, which had name
777: -- change. Use existing record with old name to find the id.
778: update ZX_DATA_UPLOAD_INTERFACE x
779: set x.geography_id = (SELECT Y.GEOGRAPHY_ID
780: FROM ZX_DATA_UPLOAD_INTERFACE y
781: WHERE y.record_type = 6
782: AND y.STATE_JURISDICTION_CODE = x.STATE_JURISDICTION_CODE
783: AND y.COUNTY_JURISDICTION_CODE = x.COUNTY_JURISDICTION_CODE
784: AND y.CITY_JURISDICTION_CODE = x.CITY_JURISDICTION_CODE
800:
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
820: l_start := DBMS_UTILITY.GET_TIME;
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
839: STATE_JURISDICTION_CODE,
840: GEOGRAPHY_ID,
841: ROW_NUMBER()
842: OVER (PARTITION BY STATE_JURISDICTION_CODE,COUNTY_JURISDICTION_CODE,GEOGRAPHY_NAME ORDER BY GEOGRAPHY_ID, EFFECTIVE_FROM) AS CITY_ROW_NUMBER
843: FROM ZX_DATA_UPLOAD_INTERFACE
844: WHERE RECORD_TYPE = 6
845: --AND GEOGRAPHY_ID IS NULL
846: AND EFFECTIVE_TO IS NULL)
847: WHERE CITY_ROW_NUMBER = 1
858:
859: l_start := DBMS_UTILITY.GET_TIME;
860:
861: -- Use the id from previous step for multi jurisdiction cities
862: update ZX_DATA_UPLOAD_INTERFACE a
863: set geography_id = (select b.geography_id
864: from ZX_DATA_UPLOAD_INTERFACE b
865: where b.geography_name = a.geography_name
866: and b.STATE_JURISDICTION_CODE = a.STATE_JURISDICTION_CODE
860:
861: -- Use the id from previous step for multi jurisdiction cities
862: update ZX_DATA_UPLOAD_INTERFACE a
863: set geography_id = (select b.geography_id
864: from ZX_DATA_UPLOAD_INTERFACE b
865: where b.geography_name = a.geography_name
866: and b.STATE_JURISDICTION_CODE = a.STATE_JURISDICTION_CODE
867: and b.COUNTY_JURISDICTION_CODE = a.COUNTY_JURISDICTION_CODE
868: --and b.CITY_JURISDICTION_CODE = a.CITY_JURISDICTION_CODE --TANIYA
884:
885: l_start := DBMS_UTILITY.GET_TIME;
886:
887: -- Update the zip range and alternate city rows with their corresponding city rows
888: update ZX_DATA_UPLOAD_INTERFACE a
889: set (geography_id,status) = (select b.geography_id,b.status
890: from ZX_DATA_UPLOAD_INTERFACE b
891: where b.STATE_JURISDICTION_CODE = a.STATE_JURISDICTION_CODE
892: and b.COUNTY_JURISDICTION_CODE = a.COUNTY_JURISDICTION_CODE
886:
887: -- Update the zip range and alternate city rows with their corresponding city rows
888: update ZX_DATA_UPLOAD_INTERFACE a
889: set (geography_id,status) = (select b.geography_id,b.status
890: from ZX_DATA_UPLOAD_INTERFACE b
891: where b.STATE_JURISDICTION_CODE = a.STATE_JURISDICTION_CODE
892: and b.COUNTY_JURISDICTION_CODE = a.COUNTY_JURISDICTION_CODE
893: and b.CITY_JURISDICTION_CODE = a.CITY_JURISDICTION_CODE
894: and b.record_type = 6
913: l_start := DBMS_UTILITY.GET_TIME;
914:
915: -- First update the zone_geography_id for pre-existing states,
916: -- but do this only if a rate exists
917: update ZX_DATA_UPLOAD_INTERFACE x
918: set x.zone_geography_id = (select y.geography_id from hz_geographies y
919: where y.geography_name = DECODE(p_tax_content_source,
920: 'TAXWARE','ST-'||x.COUNTRY_STATE_ABBREVIATION,
921: 'ST-'||x.STATE_JURISDICTION_CODE||'0000000')
922: and y.GEOGRAPHY_TYPE = 'US_STATE_ZONE_TYPE_'||SUBSTRB(p_tax_regime_code, 14,10))
923: where x.record_type = 1
924: and x.zone_geography_id is null;
925: /*and EXISTS (select null
926: from ZX_DATA_UPLOAD_INTERFACE y
927: where y.STATE_JURISDICTION_CODE = x.STATE_JURISDICTION_CODE
928: and y.COUNTY_JURISDICTION_CODE = x.COUNTY_JURISDICTION_CODE
929: and y.CITY_JURISDICTION_CODE = x.CITY_JURISDICTION_CODE
930: and y.record_type in (9,10,11,12));*/
941: l_start := DBMS_UTILITY.GET_TIME;
942:
943: -- First update the zone_geography_id for pre-existing counties,
944: -- but do this only if a rate exists
945: update ZX_DATA_UPLOAD_INTERFACE x
946: set x.zone_geography_id = (select y.geography_id from hz_geographies y
947: where y.geography_name = DECODE(p_tax_content_source,
948: 'TAXWARE','CO-'||x.COUNTRY_STATE_ABBREVIATION||'-'||UPPER(SUBSTRB(x.GEOGRAPHY_NAME,1,21)),
949: 'CO-'||x.STATE_JURISDICTION_CODE||x.COUNTY_JURISDICTION_CODE||'0000')
950: and y.GEOGRAPHY_TYPE = 'US_COUNTY_ZONE_TYPE_'||SUBSTRB(p_tax_regime_code, 14,10))
951: where x.record_type = 3
952: and x.zone_geography_id is null;
953: /*and EXISTS (select null
954: from ZX_DATA_UPLOAD_INTERFACE y
955: where y.STATE_JURISDICTION_CODE = x.STATE_JURISDICTION_CODE
956: and y.COUNTY_JURISDICTION_CODE = x.COUNTY_JURISDICTION_CODE
957: and y.CITY_JURISDICTION_CODE = x.CITY_JURISDICTION_CODE
958: and y.record_type in (9,10,11,12)); */
969: l_start := DBMS_UTILITY.GET_TIME;
970:
971: -- First update the zone_geography_id for pre-existing cities,
972: -- but do this only if a rate exists
973: update ZX_DATA_UPLOAD_INTERFACE x
974: set x.zone_geography_id = (select y.geography_id from hz_geographies y
975: where y.geography_name = DECODE(p_tax_content_source,
976: 'TAXWARE','CI-'||x.COUNTRY_STATE_ABBREVIATION||'-'||UPPER(SUBSTRB(x.GEOGRAPHY_NAME,1,12))||'-'||x.CITY_JURISDICTION_CODE,
977: 'CI-'||x.STATE_JURISDICTION_CODE||x.COUNTY_JURISDICTION_CODE||LPAD(x.CITY_JURISDICTION_CODE,4,'0'))
978: and y.GEOGRAPHY_TYPE = 'US_CITY_ZONE_TYPE_'||SUBSTRB(p_tax_regime_code, 14,10))
979: where x.record_type = 6
980: and x.zone_geography_id is null;
981: /*and EXISTS (select null
982: from ZX_DATA_UPLOAD_INTERFACE y
983: where y.STATE_JURISDICTION_CODE = x.STATE_JURISDICTION_CODE
984: and y.COUNTY_JURISDICTION_CODE = x.COUNTY_JURISDICTION_CODE
985: and y.CITY_JURISDICTION_CODE = x.CITY_JURISDICTION_CODE
986: and y.record_type in (9,10,11,12));*/
997: l_start := DBMS_UTILITY.GET_TIME;
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;
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
1007: from ZX_DATA_UPLOAD_INTERFACE y
1008: where y.STATE_JURISDICTION_CODE = x.STATE_JURISDICTION_CODE
1009: and nvl(y.COUNTY_JURISDICTION_CODE,'-1') = nvl(x.COUNTY_JURISDICTION_CODE,'-1')
1010: and nvl(y.CITY_JURISDICTION_CODE,'-1') = nvl(x.CITY_JURISDICTION_CODE,'-1')
1011: and y.record_type in (9,10,11,12));*/
1023:
1024: -- Now, update the zone_geography_id for override rates
1025: -- Update only the first row as there could be multiple rates for one
1026: -- overriding jurisdiction. First case is for city overriding state/county
1027: update ZX_DATA_UPLOAD_INTERFACE x
1028: set x.zone_geography_id = (select y.geography_id from hz_geographies y
1029: where y.geography_name = DECODE(p_tax_content_source,
1030: 'TAXWARE','CI-'||x.COUNTRY_STATE_ABBREVIATION||'-'||UPPER(SUBSTRB(x.GEOGRAPHY_NAME,1,12))||'-'||x.CITY_JURISDICTION_CODE,
1031: 'CI-'||x.STATE_JURISDICTION_CODE||x.COUNTY_JURISDICTION_CODE||LPAD(x.CITY_JURISDICTION_CODE,4,'0'))
1044: county_jurisdiction_code,
1045: city_jurisdiction_code,
1046: ROW_NUMBER()
1047: OVER (PARTITION BY STATE_JURISDICTION_CODE,COUNTY_JURISDICTION_CODE,CITY_JURISDICTION_CODE ORDER BY ROWID) AS ROW_NUMBER
1048: from ZX_DATA_UPLOAD_INTERFACE
1049: where record_type IN (9,10,11,12)
1050: and last_updation_version > p_last_run_version
1051: and state_jurisdiction_code is not null
1052: and county_jurisdiction_code is not null
1076:
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
1093: county_jurisdiction_code,
1094: city_jurisdiction_code,
1095: ROW_NUMBER()
1096: OVER (PARTITION BY STATE_JURISDICTION_CODE,COUNTY_JURISDICTION_CODE,CITY_JURISDICTION_CODE ORDER BY ROWID) AS ROW_NUMBER
1097: from ZX_DATA_UPLOAD_INTERFACE
1098: where record_type IN (9,10,11,12)
1099: and last_updation_version > p_last_run_version
1100: and state_jurisdiction_code is not null
1101: and county_jurisdiction_code is not null
1125:
1126: -- Now, update the zone_geography_id for override rates
1127: -- Update only the first row as there could be multiple rates for one
1128: -- overriding jurisdiction. First case is for county overriding state
1129: update ZX_DATA_UPLOAD_INTERFACE x
1130: set x.zone_geography_id = (select y.geography_id from hz_geographies y
1131: where y.geography_name = DECODE(p_tax_content_source,
1132: 'TAXWARE','CO-'||x.COUNTRY_STATE_ABBREVIATION||'-'||UPPER(SUBSTRB(x.GEOGRAPHY_NAME,1,21)),
1133: 'CO-'||x.STATE_JURISDICTION_CODE||x.COUNTY_JURISDICTION_CODE||'0000')
1145: state_jurisdiction_code,
1146: county_jurisdiction_code,
1147: ROW_NUMBER()
1148: OVER (PARTITION BY STATE_JURISDICTION_CODE,COUNTY_JURISDICTION_CODE ORDER BY ROWID) AS ROW_NUMBER
1149: from ZX_DATA_UPLOAD_INTERFACE
1150: where record_type IN (9,10,11,12)
1151: and last_updation_version > p_last_run_version
1152: and state_jurisdiction_code is not null
1153: and county_jurisdiction_code is not null
1173:
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
1189: state_jurisdiction_code,
1190: county_jurisdiction_code,
1191: ROW_NUMBER()
1192: OVER (PARTITION BY STATE_JURISDICTION_CODE,COUNTY_JURISDICTION_CODE ORDER BY ROWID) AS ROW_NUMBER
1193: from ZX_DATA_UPLOAD_INTERFACE
1194: where record_type IN (9,10,11,12)
1195: and last_updation_version > p_last_run_version
1196: and state_jurisdiction_code is not null
1197: and county_jurisdiction_code is not null
1218: l_start := DBMS_UTILITY.GET_TIME;
1219:
1220: -- First update the zone_geography_id for pre-existing cities,
1221: -- but do this only if a rate exists
1222: update ZX_DATA_UPLOAD_INTERFACE x
1223: set x.zone_geography_id = (select y.geography_id from hz_geographies y
1224: where y.geography_name = DECODE(p_tax_content_source,
1225: 'TAXWARE','CI-'||x.COUNTRY_STATE_ABBREVIATION||'-'||UPPER(SUBSTRB(x.GEOGRAPHY_NAME,1,12))||'-'||x.CITY_JURISDICTION_CODE,
1226: 'CI-'||x.STATE_JURISDICTION_CODE||x.COUNTY_JURISDICTION_CODE||LPAD(x.CITY_JURISDICTION_CODE,4,'0'))
1228: and y.GEOGRAPHY_TYPE = p_tax_zone_type)
1229: where x.record_type = 6
1230: and x.zone_geography_id is null;
1231: /*and EXISTS (select null
1232: from ZX_DATA_UPLOAD_INTERFACE y
1233: where y.STATE_JURISDICTION_CODE = x.STATE_JURISDICTION_CODE
1234: and y.COUNTY_JURISDICTION_CODE = x.COUNTY_JURISDICTION_CODE
1235: and y.CITY_JURISDICTION_CODE = x.CITY_JURISDICTION_CODE
1236: and y.record_type in (9,10,11,12));*/
1247: l_start := DBMS_UTILITY.GET_TIME;
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;
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
1257: from ZX_DATA_UPLOAD_INTERFACE y
1258: where y.STATE_JURISDICTION_CODE = x.STATE_JURISDICTION_CODE
1259: and y.COUNTY_JURISDICTION_CODE = x.COUNTY_JURISDICTION_CODE
1260: and y.CITY_JURISDICTION_CODE = x.CITY_JURISDICTION_CODE
1261: and y.record_type in (9,10,11,12));*/
1292: state_jurisdiction_code,
1293: state_jurisdiction_code||county_jurisdiction_code||city_jurisdiction_code concat_code,
1294: zip_begin,
1295: zip_end
1296: FROM zx_data_upload_interface
1297: WHERE record_type = 08
1298: AND last_updation_version > p_last_run_version
1299: AND effective_to IS NULL;
1300:
1372: decode(record_type,9,sales_tax_rate,10,rental_tax_rate,11,use_tax_rate,12,lease_tax_rate) percentage_rate,
1373: 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,
1374: ROW_NUMBER()
1375: OVER(PARTITION BY RECORD_TYPE,STATE_JURISDICTION_CODE,COUNTY_JURISDICTION_CODE,CITY_JURISDICTION_CODE ORDER BY EFFECTIVE_FROM ASC) AS rate_row_num
1376: FROM zx_data_upload_interface
1377: WHERE record_type in (9,10,11,12)
1378: AND last_updation_version > p_last_run_version) v,
1379: zx_data_upload_interface jur
1380: WHERE v.rate_row_num = 1
1375: OVER(PARTITION BY RECORD_TYPE,STATE_JURISDICTION_CODE,COUNTY_JURISDICTION_CODE,CITY_JURISDICTION_CODE ORDER BY EFFECTIVE_FROM ASC) AS rate_row_num
1376: FROM zx_data_upload_interface
1377: WHERE record_type in (9,10,11,12)
1378: AND last_updation_version > p_last_run_version) v,
1379: zx_data_upload_interface jur
1380: WHERE v.rate_row_num = 1
1381: AND jur.record_type = decode(v.city_jurisdiction_code,null,decode(v.county_jurisdiction_code,null,1,3),6)
1382: AND jur.state_jurisdiction_code = v.state_jurisdiction_code
1383: AND NVL(jur.county_jurisdiction_code,'-1') = NVL(v.county_jurisdiction_code,'-1')
1449: effective_from,
1450: 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,
1451: ROW_NUMBER()
1452: OVER(PARTITION BY RECORD_TYPE,STATE_JURISDICTION_CODE,COUNTY_JURISDICTION_CODE,CITY_JURISDICTION_CODE ORDER BY EFFECTIVE_FROM ASC) AS rate_row_num
1453: FROM zx_data_upload_interface
1454: WHERE record_type in (9,10,11,12)
1455: AND NVL(status,'CREATE') <> 'ERROR'
1456: AND last_updation_version > p_last_run_version) v,
1457: zx_data_upload_interface jur
1453: FROM zx_data_upload_interface
1454: WHERE record_type in (9,10,11,12)
1455: AND NVL(status,'CREATE') <> 'ERROR'
1456: AND last_updation_version > p_last_run_version) v,
1457: zx_data_upload_interface jur
1458: WHERE v.rate_row_num = 1
1459: AND jur.record_type = DECODE(v.city_jurisdiction_code,NULL,DECODE(v.county_jurisdiction_code,NULL,1,3),6)
1460: AND jur.state_jurisdiction_code = v.state_jurisdiction_code
1461: AND NVL(jur.county_jurisdiction_code,'-1') = NVL(v.county_jurisdiction_code,'-1')
1508: city_jurisdiction_code,
1509: 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,
1510: effective_from,
1511: 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
1512: FROM zx_data_upload_interface
1513: WHERE record_type in (9,10,11,12)
1514: AND NVL(status,'CREATE') <> 'ERROR' ) v,
1515: zx_data_upload_interface v1
1516: WHERE v1.record_type = DECODE(v.city_jurisdiction_code,NULL,DECODE(v.county_jurisdiction_code,NULL,1,3),6)
1511: 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
1512: FROM zx_data_upload_interface
1513: WHERE record_type in (9,10,11,12)
1514: AND NVL(status,'CREATE') <> 'ERROR' ) v,
1515: zx_data_upload_interface v1
1516: WHERE v1.record_type = DECODE(v.city_jurisdiction_code,NULL,DECODE(v.county_jurisdiction_code,NULL,1,3),6)
1517: AND v1.state_jurisdiction_code = v.state_jurisdiction_code
1518: AND NVL(v1.county_jurisdiction_code,'-1') = NVL(v.county_jurisdiction_code,'-1')
1519: AND NVL(v1.city_jurisdiction_code,'-1') = NVL(v.city_jurisdiction_code,'-1')
1584: (l_previous_zip_begin(i) BETWEEN ref_zip.zip_begin AND ref_zip.zip_end))
1585: THEN
1586: 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||'.';
1587: /* Commented for Bug#7298430
1588: UPDATE zx_data_upload_interface
1589: SET STATUS = 'ERROR',
1590: ERROR_MESSAGE = l_msg
1591: WHERE ROWID = ref_zip.row_id;
1592: FND_FILE.PUT_LINE
1636: THEN
1637: l_msg := l_msg||', city: '||ref_rates.city_jurisdiction_code;
1638: END IF;
1639: 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')||'.';
1640: UPDATE zx_data_upload_interface
1641: SET STATUS = 'ERROR',
1642: ERROR_MESSAGE = l_msg
1643: WHERE ROWID = ref_rates.row_id;
1644: FND_FILE.PUT_LINE
1757: l_msg := l_msg||', city: '||ref_rates.city_jurisdiction_code;
1758: END IF;
1759: l_msg := l_msg||'.';
1760: /* -- Commented for Bug#7298430
1761: UPDATE zx_data_upload_interface
1762: SET STATUS = 'ERROR',
1763: ERROR_MESSAGE = l_msg
1764: WHERE ROWID = ref_rates.row_id;
1765: FND_FILE.PUT_LINE
1803: l_msg := l_msg||', city: '||ref_rates.city_jurisdiction_code;
1804: END IF;
1805: l_msg := l_msg||' has already been ended by user.';
1806: /* -- Commented for Bug#7298430
1807: UPDATE zx_data_upload_interface
1808: SET STATUS = 'ERROR',
1809: ERROR_MESSAGE = l_msg
1810: WHERE ROWID = ref_rates.row_id;
1811: FND_FILE.PUT_LINE
1911: -- End : Added for Bug#7527399
1912:
1913: -- Start : Added for Bug#7298430
1914: FORALL i IN INDICES OF l_upload_rec.row_id
1915: UPDATE zx_data_upload_interface
1916: SET STATUS = l_upload_rec.status(i),
1917: ERROR_MESSAGE = l_upload_rec.log_msg(i)
1918: WHERE ROWID = l_upload_rec.row_id(i);
1919:
1961: -- Ad parallelization variables
1962: -----------------------------------------------------
1963: l_table_owner VARCHAR2(30) := 'ZX';
1964: l_any_rows_to_process BOOLEAN;
1965: l_table_name VARCHAR2(30) := 'ZX_DATA_UPLOAD_INTERFACE';
1966: l_start_rowid ROWID;
1967: l_end_rowid ROWID;
1968: l_rows_processed NUMBER;
1969:
2683: state.status,
2684: (SELECT hzg.geography_id
2685: FROM HZ_GEOGRAPHIES hzg
2686: WHERE hzg.geography_id = state.geography_id) existing_geography_id
2687: FROM zx_data_upload_interface state
2688: WHERE state.record_type = 01
2689: AND state.LAST_UPDATION_VERSION > p_last_run_version
2690: AND state.geography_id IS NOT NULL
2691: AND nvl(state.status,'ERROR') IN ('CREATE','UPDATE')
2722: county.status,
2723: (SELECT hzg.geography_id
2724: FROM HZ_GEOGRAPHIES hzg
2725: WHERE hzg.geography_id = county.geography_id) existing_geography_id
2726: FROM zx_data_upload_interface county,
2727: zx_data_upload_interface state
2728: WHERE county.record_type = 03
2729: AND county.LAST_UPDATION_VERSION > p_last_run_version
2730: AND county.geography_id IS NOT NULL
2723: (SELECT hzg.geography_id
2724: FROM HZ_GEOGRAPHIES hzg
2725: WHERE hzg.geography_id = county.geography_id) existing_geography_id
2726: FROM zx_data_upload_interface county,
2727: zx_data_upload_interface state
2728: WHERE county.record_type = 03
2729: AND county.LAST_UPDATION_VERSION > p_last_run_version
2730: AND county.geography_id IS NOT NULL
2731: AND nvl(county.status,'ERROR') IN ('CREATE','UPDATE')
2767: city.status,
2768: (SELECT hzg.geography_id
2769: FROM HZ_GEOGRAPHIES hzg
2770: WHERE hzg.geography_id = city.geography_id) existing_geography_id
2771: FROM zx_data_upload_interface city,
2772: zx_data_upload_interface county,
2773: zx_data_upload_interface state
2774: WHERE city.record_type = 06
2775: AND city.LAST_UPDATION_VERSION > p_last_run_version
2768: (SELECT hzg.geography_id
2769: FROM HZ_GEOGRAPHIES hzg
2770: WHERE hzg.geography_id = city.geography_id) existing_geography_id
2771: FROM zx_data_upload_interface city,
2772: zx_data_upload_interface county,
2773: zx_data_upload_interface state
2774: WHERE city.record_type = 06
2775: AND city.LAST_UPDATION_VERSION > p_last_run_version
2776: AND city.geography_id IS NOT NULL
2769: FROM HZ_GEOGRAPHIES hzg
2770: WHERE hzg.geography_id = city.geography_id) existing_geography_id
2771: FROM zx_data_upload_interface city,
2772: zx_data_upload_interface county,
2773: zx_data_upload_interface state
2774: WHERE city.record_type = 06
2775: AND city.LAST_UPDATION_VERSION > p_last_run_version
2776: AND city.geography_id IS NOT NULL
2777: AND nvl(city.status,'ERROR') IN ('CREATE','UPDATE')
2791:
2792:
2793: /*SELECT COUNT(*)
2794: INTO l_rows_processed
2795: FROM zx_data_upload_interface
2796: WHERE rowid between l_start_rowid and l_end_rowid;
2797: --l_rows_processed := SQL%ROWCOUNT;
2798:
2799: ad_parallel_updates_pkg.processed_rowid_range(l_rows_processed,l_end_rowid);*/
2856: -- Ad parallelization variables
2857: -----------------------------------------------------
2858: l_table_owner VARCHAR2(30) := 'ZX';
2859: l_any_rows_to_process BOOLEAN;
2860: l_table_name VARCHAR2(30) := 'ZX_DATA_UPLOAD_INTERFACE';
2861: l_start_rowid ROWID;
2862: l_end_rowid ROWID;
2863: l_rows_processed NUMBER;
2864:
3217: 275 precedence_level,
3218: 'N' inner_city_flag,
3219: 1 CITY_ROW_NUMBER,
3220: (SELECT 1
3221: FROM zx_data_upload_interface rate
3222: WHERE rate.record_type IN (09,10,11,12)
3223: AND rate.state_jurisdiction_code = inter.state_jurisdiction_code
3224: AND rate.county_jurisdiction_code IS NULL
3225: AND rate.city_jurisdiction_code IS NULL
3224: AND rate.county_jurisdiction_code IS NULL
3225: AND rate.city_jurisdiction_code IS NULL
3226: AND rate.LAST_UPDATION_VERSION > p_last_run_version
3227: AND rownum = 1) existing_tax_rate
3228: FROM zx_data_upload_interface inter
3229: WHERE inter.record_type = 01
3230: AND inter.geography_id IS NOT NULL
3231: AND inter.effective_to IS NULL
3232: AND inter.LAST_UPDATION_VERSION > p_last_run_version
3247: 275 precedence_level,
3248: 'N' inner_city_flag,
3249: 1 CITY_ROW_NUMBER,
3250: (SELECT 1
3251: FROM zx_data_upload_interface rate
3252: WHERE rate.record_type IN (09,10,11,12)
3253: AND rate.state_jurisdiction_code = inter.state_jurisdiction_code
3254: AND rate.county_jurisdiction_code IS NULL
3255: AND rate.city_jurisdiction_code IS NULL
3254: AND rate.county_jurisdiction_code IS NULL
3255: AND rate.city_jurisdiction_code IS NULL
3256: AND rate.LAST_UPDATION_VERSION > p_last_run_version
3257: AND rownum = 1) existing_tax_rate
3258: FROM zx_data_upload_interface inter
3259: WHERE inter.record_type = 01
3260: AND inter.zone_geography_id IS NOT NULL
3261: AND inter.effective_to IS NULL
3262: AND p_tax_zone_type IS NULL -- Means migrated regime
3260: AND inter.zone_geography_id IS NOT NULL
3261: AND inter.effective_to IS NULL
3262: AND p_tax_zone_type IS NULL -- Means migrated regime
3263: /*AND EXISTS (SELECT NULL
3264: FROM zx_data_upload_interface rate
3265: WHERE rate.record_type IN (09,10,11,12)
3266: AND rate.state_jurisdiction_code = inter.state_jurisdiction_code
3267: AND rate.county_jurisdiction_code IS NULL
3268: AND rate.city_jurisdiction_code IS NULL
3283: 175 precedence_level,
3284: 'N' inner_city_flag,
3285: 1 CITY_ROW_NUMBER,
3286: (SELECT 1
3287: FROM zx_data_upload_interface rate
3288: WHERE rate.record_type IN (09,10,11,12)
3289: AND rate.state_jurisdiction_code = inter.state_jurisdiction_code
3290: AND rate.county_jurisdiction_code = inter.county_jurisdiction_code
3291: AND rate.city_jurisdiction_code IS NULL
3290: AND rate.county_jurisdiction_code = inter.county_jurisdiction_code
3291: AND rate.city_jurisdiction_code IS NULL
3292: AND rate.LAST_UPDATION_VERSION > p_last_run_version
3293: AND rownum = 1) existing_tax_rate
3294: FROM zx_data_upload_interface inter
3295: WHERE inter.record_type = 03
3296: AND inter.geography_id IS NOT NULL
3297: AND inter.effective_to IS NULL
3298: AND inter.LAST_UPDATION_VERSION > p_last_run_version
3313: 175 precedence_level,
3314: 'N' inner_city_flag,
3315: 1 CITY_ROW_NUMBER,
3316: (SELECT 1
3317: FROM zx_data_upload_interface rate
3318: WHERE rate.record_type IN (09,10,11,12)
3319: AND rate.state_jurisdiction_code = inter.state_jurisdiction_code
3320: AND rate.county_jurisdiction_code = inter.county_jurisdiction_code
3321: AND rate.city_jurisdiction_code IS NULL
3320: AND rate.county_jurisdiction_code = inter.county_jurisdiction_code
3321: AND rate.city_jurisdiction_code IS NULL
3322: AND rate.LAST_UPDATION_VERSION > p_last_run_version
3323: AND rownum = 1) existing_tax_rate
3324: FROM zx_data_upload_interface inter
3325: WHERE inter.record_type = 03
3326: AND inter.zone_geography_id IS NOT NULL
3327: AND inter.effective_to IS NULL
3328: AND p_tax_zone_type IS NULL -- Means migrated regime
3326: AND inter.zone_geography_id IS NOT NULL
3327: AND inter.effective_to IS NULL
3328: AND p_tax_zone_type IS NULL -- Means migrated regime
3329: /*AND EXISTS (SELECT NULL
3330: FROM zx_data_upload_interface rate
3331: WHERE rate.record_type IN (09,10,11,12)
3332: AND rate.state_jurisdiction_code = inter.state_jurisdiction_code
3333: AND rate.county_jurisdiction_code = inter.county_jurisdiction_code
3334: AND rate.city_jurisdiction_code IS NULL
3349: 75 precedence_level,
3350: DECODE(TO_CHAR(inter.JURISDICTION_SERIAL_NUMBER),'1','Y','N') inner_city_flag,
3351: 1 CITY_ROW_NUMBER,
3352: (SELECT 1
3353: FROM zx_data_upload_interface rate
3354: WHERE rate.record_type IN (09,10,11,12)
3355: AND rate.state_jurisdiction_code = inter.state_jurisdiction_code
3356: AND rate.county_jurisdiction_code = inter.county_jurisdiction_code
3357: AND rate.city_jurisdiction_code = inter.city_jurisdiction_code
3356: AND rate.county_jurisdiction_code = inter.county_jurisdiction_code
3357: AND rate.city_jurisdiction_code = inter.city_jurisdiction_code
3358: AND rate.LAST_UPDATION_VERSION > p_last_run_version
3359: AND rownum = 1) existing_tax_rate
3360: FROM zx_data_upload_interface inter
3361: WHERE inter.record_type = 06
3362: AND inter.zone_geography_id IS NOT NULL
3363: AND inter.effective_to IS NULL
3364: -- cities should be considered always as they might have been created earlier but their zip range or rates are sent for the first time
3362: AND inter.zone_geography_id IS NOT NULL
3363: AND inter.effective_to IS NULL
3364: -- cities should be considered always as they might have been created earlier but their zip range or rates are sent for the first time
3365: /*AND EXISTS (SELECT NULL
3366: FROM zx_data_upload_interface rate
3367: WHERE rate.record_type IN (08,09,10,11,12)
3368: AND rate.state_jurisdiction_code = inter.state_jurisdiction_code
3369: AND rate.county_jurisdiction_code = inter.county_jurisdiction_code
3370: AND rate.city_jurisdiction_code = inter.city_jurisdiction_code
3392: ,decode(to_char(inter.record_type),'9',inter.SALES_TAX_AUTHORITY_LEVEL,'10',inter.RENTAL_TAX_AUTHORITY_LEVEL,'11',inter.USE_TAX_AUTHORITY_LEVEL,'12',inter.LEASE_TAX_AUTHORITY_LEVEL)
3393: ,DECODE(p_tax_zone_type,null,inter.zone_geography_id,z.geography_id)
3394: ,z.effective_from ORDER BY z.effective_from ) AS CITY_ROW_NUMBER,
3395: 1 existing_tax_rate
3396: FROM zx_data_upload_interface inter,
3397: zx_data_upload_interface z
3398: WHERE inter.record_type IN (09,10,11,12)
3399: AND (inter.SALES_TAX_AUTHORITY_LEVEL = 'STATE'
3400: OR inter.SALES_TAX_AUTHORITY_LEVEL = 'COUNTY'
3393: ,DECODE(p_tax_zone_type,null,inter.zone_geography_id,z.geography_id)
3394: ,z.effective_from ORDER BY z.effective_from ) AS CITY_ROW_NUMBER,
3395: 1 existing_tax_rate
3396: FROM zx_data_upload_interface inter,
3397: zx_data_upload_interface z
3398: WHERE inter.record_type IN (09,10,11,12)
3399: AND (inter.SALES_TAX_AUTHORITY_LEVEL = 'STATE'
3400: OR inter.SALES_TAX_AUTHORITY_LEVEL = 'COUNTY'
3401: OR inter.RENTAL_TAX_AUTHORITY_LEVEL = 'STATE'
3433: 175 precedence_level,
3434: 'N' inner_city_flag,
3435: 1 CITY_ROW_NUMBER,
3436: 1 existing_tax_rate
3437: FROM zx_data_upload_interface inter,
3438: zx_data_upload_interface z
3439: WHERE inter.record_type IN (09,10,11,12)
3440: AND (inter.SALES_TAX_AUTHORITY_LEVEL = 'STATE'
3441: OR inter.RENTAL_TAX_AUTHORITY_LEVEL = 'STATE'
3434: 'N' inner_city_flag,
3435: 1 CITY_ROW_NUMBER,
3436: 1 existing_tax_rate
3437: FROM zx_data_upload_interface inter,
3438: zx_data_upload_interface z
3439: WHERE inter.record_type IN (09,10,11,12)
3440: AND (inter.SALES_TAX_AUTHORITY_LEVEL = 'STATE'
3441: OR inter.RENTAL_TAX_AUTHORITY_LEVEL = 'STATE'
3442: OR inter.USE_TAX_AUTHORITY_LEVEL = 'STATE'
3611: AND hgr.START_DATE = zip.effective_from
3612: AND ROWNUM=1
3613: --AND hgr.GEOGRAPHY_TO = zip.zip_end
3614: ) hgr_row_id
3615: FROM ZX_DATA_UPLOAD_INTERFACE zip,
3616: ZX_DATA_UPLOAD_INTERFACE city
3617: WHERE zip.record_type = 08
3618: AND city.record_type = 06
3619: AND city.STATE_JURISDICTION_CODE = zip.STATE_JURISDICTION_CODE
3612: AND ROWNUM=1
3613: --AND hgr.GEOGRAPHY_TO = zip.zip_end
3614: ) hgr_row_id
3615: FROM ZX_DATA_UPLOAD_INTERFACE zip,
3616: ZX_DATA_UPLOAD_INTERFACE city
3617: WHERE zip.record_type = 08
3618: AND city.record_type = 06
3619: AND city.STATE_JURISDICTION_CODE = zip.STATE_JURISDICTION_CODE
3620: AND city.COUNTY_JURISDICTION_CODE = zip.COUNTY_JURISDICTION_CODE
3621: AND city.CITY_JURISDICTION_CODE = zip.CITY_JURISDICTION_CODE
3622: AND city.zone_geography_id IS NOT NULL
3623: AND city.geography_id IS NOT NULL
3624: AND EXISTS (SELECT NULL
3625: FROM zx_data_upload_interface rate
3626: WHERE rate.record_type IN (08,09,10,11,12)
3627: AND rate.state_jurisdiction_code = zip.state_jurisdiction_code
3628: AND rate.county_jurisdiction_code = zip.county_jurisdiction_code
3629: AND rate.city_jurisdiction_code = zip.city_jurisdiction_code
3739: nvl(effective_to,to_date('12/31/4712','mm/dd/yyyy')) end_date,
3740: DECODE(TO_CHAR(record_type),'1','US_STATE_ZONE_TYPE_'||SUBSTRB(p_tax_regime_code, 14,10),
3741: '3','US_COUNTY_ZONE_TYPE_'||SUBSTRB(p_tax_regime_code, 14,10)) zone_geography_type,
3742: geography_id
3743: FROM ZX_DATA_UPLOAD_INTERFACE inter
3744: WHERE record_type in (1,3)
3745: AND zone_geography_id IS NOT NULL
3746: AND NOT EXISTS (SELECT NULL
3747: FROM hz_geography_ranges hgr
3810: -- Ad parallelization variables
3811: -----------------------------------------------------
3812: l_table_owner VARCHAR2(30) := 'ZX';
3813: l_any_rows_to_process BOOLEAN;
3814: l_table_name VARCHAR2(30) := 'ZX_DATA_UPLOAD_INTERFACE';
3815: l_start_rowid ROWID;
3816: l_end_rowid ROWID;
3817: l_rows_processed NUMBER;
3818:
4381: effective_from,
4382: effective_to,
4383: zip_begin,
4384: zip_end
4385: from zx_data_upload_interface
4386: where record_type = 08
4387: and last_updation_version > p_last_run_version
4388: and city_jurisdiction_code is not null
4389: and geography_id is not null
4498: -- Ad parallelization variables
4499: -----------------------------------------------------
4500: l_table_owner VARCHAR2(30) := 'ZX';
4501: l_any_rows_to_process BOOLEAN;
4502: l_table_name VARCHAR2(30) := 'ZX_DATA_UPLOAD_INTERFACE';
4503: l_start_rowid ROWID;
4504: l_end_rowid ROWID;
4505: l_rows_processed NUMBER;
4506:
4575: )
4576: SELECT DISTINCT inter.geography_id,
4577: inter.geography_name geography_name,
4578: 'CITY' geography_type
4579: FROM ZX_DATA_UPLOAD_INTERFACE inter
4580: WHERE inter.record_type = 07
4581: AND inter.last_updation_version > p_last_run_version
4582: AND inter.geography_id IS NOT NULL
4583: AND NOT EXISTS (SELECT 1
4655: WHERE tax_regime_code = b_regime_code;
4656:
4657: l_table_owner VARCHAR2(30) := 'ZX';
4658: l_any_rows_to_process BOOLEAN;
4659: l_table_name VARCHAR2(30) := 'ZX_DATA_UPLOAD_INTERFACE';
4660: l_start_rowid ROWID;
4661: l_end_rowid ROWID;
4662: l_rows_processed NUMBER;
4663: l_migrated_tax_regime_flag VARCHAR2(1);
4781: '6',jur.state_jurisdiction_code||jur.county_jurisdiction_code||LPAD(jur.city_jurisdiction_code,4,'0')))
4782: 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')
4783: AND effective_from = decode(greatest(rt.effective_from,G_RECORD_EFFECTIVE_START),rt.effective_from,rt.effective_from,G_RECORD_EFFECTIVE_START)
4784: ) l_count
4785: FROM zx_data_upload_interface rt,
4786: zx_data_upload_interface jur
4787: where rt.record_type in (9,10,11,12)
4788: and rt.last_updation_version > p_last_run_version
4789: and nvl(rt.status,'CREATE') <> 'ERROR'
4782: 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')
4783: AND effective_from = decode(greatest(rt.effective_from,G_RECORD_EFFECTIVE_START),rt.effective_from,rt.effective_from,G_RECORD_EFFECTIVE_START)
4784: ) l_count
4785: FROM zx_data_upload_interface rt,
4786: zx_data_upload_interface jur
4787: where rt.record_type in (9,10,11,12)
4788: and rt.last_updation_version > p_last_run_version
4789: and nvl(rt.status,'CREATE') <> 'ERROR'
4790: and jur.record_type = decode(rt.city_jurisdiction_code,null,decode(rt.county_jurisdiction_code,null,1,3),6)
4956: '6',jur.state_jurisdiction_code||jur.county_jurisdiction_code||LPAD(jur.city_jurisdiction_code,4,'0')))
4957: 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')
4958: AND effective_from = decode(greatest(rt.effective_from,G_RECORD_EFFECTIVE_START),rt.effective_from,rt.effective_from,G_RECORD_EFFECTIVE_START)
4959: ) l_count
4960: FROM zx_data_upload_interface rt,
4961: zx_data_upload_interface jur
4962: where rt.record_type in (9,10,11,12)
4963: and rt.last_updation_version > p_last_run_version
4964: and nvl(rt.status,'CREATE') <> 'ERROR'
4957: 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')
4958: AND effective_from = decode(greatest(rt.effective_from,G_RECORD_EFFECTIVE_START),rt.effective_from,rt.effective_from,G_RECORD_EFFECTIVE_START)
4959: ) l_count
4960: FROM zx_data_upload_interface rt,
4961: zx_data_upload_interface jur
4962: where rt.record_type in (9,10,11,12)
4963: and rt.last_updation_version > p_last_run_version
4964: and nvl(rt.status,'CREATE') <> 'ERROR'
4965: and jur.record_type = decode(rt.city_jurisdiction_code,null,decode(rt.county_jurisdiction_code,null,1,3),6)
5630:
5631: CURSOR c_get_max_version
5632: IS
5633: SELECT MAX(LAST_UPDATION_VERSION)
5634: FROM ZX_DATA_UPLOAD_INTERFACE;
5635:
5636: CURSOR c_get_regime_name
5637: (
5638: b_tax_regime_code VARCHAR2
5781:
5782: CURSOR c_check_data
5783: IS
5784: SELECT COUNT(*)
5785: FROM ZX_DATA_UPLOAD_INTERFACE;
5786:
5787: l_api_name CONSTANT VARCHAR2(30):= 'load_file';
5788: l_error EXCEPTION;
5789: l_request_id NUMBER;