eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.ZX_TAX_CONTENT_UPLOAD dependencies on HZ_GEOGRAPHIES

Line 610: FROM HZ_GEOGRAPHIES

606: ) IS
607:
608: CURSOR C_CNTRY_ID IS
609: SELECT GEOGRAPHY_ID
610: FROM HZ_GEOGRAPHIES
611: WHERE GEOGRAPHY_CODE = 'US'
612: AND GEOGRAPHY_TYPE = 'COUNTRY'
613: AND GEOGRAPHY_USE = 'MASTER_REF';
614:

Line 636: HZ_GEOGRAPHIES Y,

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
640: AND UPPER(Y.GEOGRAPHY_NAME) = UPPER(X.GEOGRAPHY_NAME)

Line 666: FROM HZ_GEOGRAPHIES Y

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
669: AND Y.COUNTRY_CODE = 'US'
670: AND Y.GEOGRAPHY_TYPE = 'STATE'

Line 717: FROM HZ_GEOGRAPHIES Y,

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)
720: AND Y.GEOGRAPHY_USE = 'MASTER_REF'
721: AND Y.GEOGRAPHY_TYPE = 'COUNTY'

Line 776: -- FROM HZ_GEOGRAPHIES Y,

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
779: -- WHERE UPPER(Y.GEOGRAPHY_NAME) = UPPER(X.GEOGRAPHY_NAME)
780: -- AND Y.GEOGRAPHY_USE = 'MASTER_REF'

Line 852: SET geography_id = hz_geographies_s.nextval,

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;

Line 872: SET geography_id = hz_geographies_s.nextval,

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

Line 940: SET geography_id = hz_geographies_s.nextval,

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;

Line 991: SET x.zone_geography_id = (SELECT y.geography_id FROM hz_geographies y

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',
995: 'OTHERS','ST-'||x.STATE_JURISDICTION_CODE||'0000000',

Line 1022: SET x.zone_geography_id = (SELECT y.geography_id FROM hz_geographies y

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',
1026: 'OTHERS','CO-'||x.STATE_JURISDICTION_CODE||x.COUNTY_JURISDICTION_CODE||'0000',

Line 1054: SET x.zone_geography_id = (SELECT y.geography_id FROM hz_geographies y

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'),
1058: 'OTHERS','CI-'||x.STATE_JURISDICTION_CODE||x.COUNTY_JURISDICTION_CODE||LPAD(x.CITY_JURISDICTION_CODE,4,'0'),

Line 1113: SET x.zone_geography_id = hz_geographies_s.nextval

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

Line 1137: SET x.zone_geography_id = hz_geographies_s.nextval

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';

Line 1187: SET x.zone_geography_id = (SELECT y.geography_id FROM hz_geographies y

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'),
1191: 'OTHERS','CI-'||x.STATE_JURISDICTION_CODE||x.COUNTY_JURISDICTION_CODE||LPAD(x.CITY_JURISDICTION_CODE,4,'0'),

Line 1247: SET x.zone_geography_id = hz_geographies_s.nextval

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

Line 1299: SET x.zone_geography_id = (SELECT y.geography_id FROM hz_geographies y

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',
1303: 'OTHERS','CO-'||x.STATE_JURISDICTION_CODE||x.COUNTY_JURISDICTION_CODE||'0000',

Line 1353: SET x.zone_geography_id = hz_geographies_s.nextval

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

Line 1399: SET x.zone_geography_id = (SELECT y.geography_id FROM hz_geographies y

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'),
1403: 'OTHERS','CI-'||x.STATE_JURISDICTION_CODE||x.COUNTY_JURISDICTION_CODE||LPAD(x.CITY_JURISDICTION_CODE,4,'0'),

Line 1456: SET x.zone_geography_id = hz_geographies_s.nextval

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';

Line 2290: INTO HZ_GEOGRAPHIES

2286: );
2287:
2288: INSERT ALL
2289: WHEN (action_type = 'CREATE' AND existing_geography_id IS NULL AND geography_id IS NOT NULL AND geography_type IS NOT NULL) THEN
2290: INTO HZ_GEOGRAPHIES
2291: (
2292: GEOGRAPHY_ID,
2293: OBJECT_VERSION_NUMBER,
2294: GEOGRAPHY_TYPE,

Line 2418: 'HZ_GEOGRAPHIES',

2414: VALUES
2415: (
2416: 'MASTER_REF',
2417: geography_id,
2418: 'HZ_GEOGRAPHIES',
2419: geography_type,
2420: geography_id,
2421: 'HZ_GEOGRAPHIES',
2422: geography_type,

Line 2421: 'HZ_GEOGRAPHIES',

2417: geography_id,
2418: 'HZ_GEOGRAPHIES',
2419: geography_type,
2420: geography_id,
2421: 'HZ_GEOGRAPHIES',
2422: geography_type,
2423: 0 ,
2424: 'N',
2425: 'Y',

Line 2627: 'HZ_GEOGRAPHIES',

2623: (
2624: hz_relationships_s.nextval,
2625: parent_geography_id,
2626: parent_geography_type,
2627: 'HZ_GEOGRAPHIES',
2628: geography_id,
2629: geography_type,
2630: 'HZ_GEOGRAPHIES',
2631: 'PARENT_OF',

Line 2630: 'HZ_GEOGRAPHIES',

2626: parent_geography_type,
2627: 'HZ_GEOGRAPHIES',
2628: geography_id,
2629: geography_type,
2630: 'HZ_GEOGRAPHIES',
2631: 'PARENT_OF',
2632: 'F',
2633: null,
2634: start_date,

Line 2685: 'HZ_GEOGRAPHIES',

2681: (
2682: hz_relationships_s.nextval,
2683: geography_id,
2684: geography_type,
2685: 'HZ_GEOGRAPHIES',
2686: parent_geography_id,
2687: parent_geography_type,
2688: 'HZ_GEOGRAPHIES',
2689: 'CHILD_OF',

Line 2688: 'HZ_GEOGRAPHIES',

2684: geography_type,
2685: 'HZ_GEOGRAPHIES',
2686: parent_geography_id,
2687: parent_geography_type,
2688: 'HZ_GEOGRAPHIES',
2689: 'CHILD_OF',
2690: 'B',
2691: null,
2692: start_date,

Line 2737: 'HZ_GEOGRAPHIES',

2733: VALUES
2734: (
2735: 'MASTER_REF',
2736: parent_geography_id,
2737: 'HZ_GEOGRAPHIES',
2738: parent_geography_type,
2739: geography_id,
2740: 'HZ_GEOGRAPHIES',
2741: geography_type,

Line 2740: 'HZ_GEOGRAPHIES',

2736: parent_geography_id,
2737: 'HZ_GEOGRAPHIES',
2738: parent_geography_type,
2739: geography_id,
2740: 'HZ_GEOGRAPHIES',
2741: geography_type,
2742: 1,
2743: '',
2744: '',

Line 2784: 'HZ_GEOGRAPHIES',

2780: VALUES
2781: (
2782: 'MASTER_REF',
2783: geography_element1_id,
2784: 'HZ_GEOGRAPHIES',
2785: geography_element1_type,
2786: geography_id,
2787: 'HZ_GEOGRAPHIES',
2788: geography_type,

Line 2787: 'HZ_GEOGRAPHIES',

2783: geography_element1_id,
2784: 'HZ_GEOGRAPHIES',
2785: geography_element1_type,
2786: geography_id,
2787: 'HZ_GEOGRAPHIES',
2788: geography_type,
2789: 2 ,
2790: '',
2791: '',

Line 2831: 'HZ_GEOGRAPHIES',

2827: VALUES
2828: (
2829: 'MASTER_REF',
2830: geography_element2_id,
2831: 'HZ_GEOGRAPHIES',
2832: geography_element2_type,
2833: geography_id,
2834: 'HZ_GEOGRAPHIES',
2835: geography_type,

Line 2834: 'HZ_GEOGRAPHIES',

2830: geography_element2_id,
2831: 'HZ_GEOGRAPHIES',
2832: geography_element2_type,
2833: geography_id,
2834: 'HZ_GEOGRAPHIES',
2835: geography_type,
2836: 2 ,
2837: '',
2838: '',

Line 2877: 'HZ_GEOGRAPHIES',

2873: VALUES
2874: (
2875: 'MASTER_REF',
2876: geography_element1_id,
2877: 'HZ_GEOGRAPHIES',
2878: geography_element1_type,
2879: geography_id,
2880: 'HZ_GEOGRAPHIES',
2881: geography_type,

Line 2880: 'HZ_GEOGRAPHIES',

2876: geography_element1_id,
2877: 'HZ_GEOGRAPHIES',
2878: geography_element1_type,
2879: geography_id,
2880: 'HZ_GEOGRAPHIES',
2881: geography_type,
2882: 3 ,
2883: '',
2884: '',

Line 2970: FROM HZ_GEOGRAPHIES hzg

2966: nvl(state.effective_to,to_date('12/31/4712','mm/dd/yyyy')) end_date,
2967: state.country_code,
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

Line 3009: FROM HZ_GEOGRAPHIES hzg

3005: nvl(county.effective_to,to_date('12/31/4712','mm/dd/yyyy')) end_date,
3006: county.country_code,
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

Line 3054: FROM HZ_GEOGRAPHIES hzg

3050: nvl(city.effective_to,to_date('12/31/4712','mm/dd/yyyy')) end_date,
3051: city.country_code,
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

Line 3195: INTO HZ_GEOGRAPHIES

3191:
3192: INSERT ALL
3193: WHEN (zone_geography_type IS NOT NULL AND zone_geography_id IS NOT NULL
3194: AND existing_geography_id IS NULL AND primary_flag = 'Y') THEN
3195: INTO HZ_GEOGRAPHIES
3196: (
3197: GEOGRAPHY_ID,
3198: OBJECT_VERSION_NUMBER,
3199: GEOGRAPHY_TYPE,

Line 3350: 'HZ_GEOGRAPHIES',

3346: (
3347: hz_relationships_s.nextval,
3348: zone_geography_id,
3349: zone_geography_type,
3350: 'HZ_GEOGRAPHIES',
3351: geography_id,
3352: geography_type,
3353: 'HZ_GEOGRAPHIES',
3354: 'PARENT_OF',

Line 3353: 'HZ_GEOGRAPHIES',

3349: zone_geography_type,
3350: 'HZ_GEOGRAPHIES',
3351: geography_id,
3352: geography_type,
3353: 'HZ_GEOGRAPHIES',
3354: 'PARENT_OF',
3355: 'F',
3356: null,
3357: start_date,

Line 3409: 'HZ_GEOGRAPHIES',

3405: (
3406: hz_relationships_s.nextval,
3407: geography_id,
3408: geography_type,
3409: 'HZ_GEOGRAPHIES',
3410: zone_geography_id,
3411: zone_geography_type,
3412: 'HZ_GEOGRAPHIES',
3413: 'CHILD_OF',

Line 3412: 'HZ_GEOGRAPHIES',

3408: geography_type,
3409: 'HZ_GEOGRAPHIES',
3410: zone_geography_id,
3411: zone_geography_type,
3412: 'HZ_GEOGRAPHIES',
3413: 'CHILD_OF',
3414: 'B',
3415: null,
3416: start_date,

Line 3501: FROM hz_geographies

3497: AND j.tax = v.tax
3498: AND j.zone_geography_id = v.zone_geography_id
3499: AND j.effective_from = DECODE(GREATEST(v.start_date,G_RECORD_EFFECTIVE_START),v.start_date,v.start_date,G_RECORD_EFFECTIVE_START)) existing_zone_geography_id,
3500: (SELECT geography_id
3501: FROM hz_geographies
3502: WHERE geography_id = v.zone_geography_id) existing_geography_id,
3503: v.inner_city_flag,
3504: -- Bug 6393452
3505: CITY_ROW_NUMBER,

Line 3905: FROM HZ_GEOGRAPHIES hg

3901: SELECT zjb.TAX_JURISDICTION_ID,
3902: DECODE((SELECT SUBSTR(hg.geography_element2_code,1,2)||'-'||
3903: DECODE(hg.geography_element3,null,'',SUBSTR(hg.geography_element3,1,30)||'-')||
3904: DECODE(hg.geography_element4,null,'',SUBSTR(hg.geography_element4,1,30)||'-')
3905: FROM HZ_GEOGRAPHIES hg
3906: WHERE hg.GEOGRAPHY_ID = zjb.zone_geography_id),
3907: '-',
3908: (SELECT SUBSTR(hg.geography_element2_code,1,2)||'-'||
3909: DECODE(hg.geography_element3,null,'',SUBSTR(hg.geography_element3,1,30)||'-')||

Line 3911: FROM hz_geographies hg_zone,

3907: '-',
3908: (SELECT SUBSTR(hg.geography_element2_code,1,2)||'-'||
3909: DECODE(hg.geography_element3,null,'',SUBSTR(hg.geography_element3,1,30)||'-')||
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

Line 3913: hz_geographies hg,

3909: DECODE(hg.geography_element3,null,'',SUBSTR(hg.geography_element3,1,30)||'-')||
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'

Line 3920: AND hr.SUBJECT_TABLE_NAME = 'HZ_GEOGRAPHIES'

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
3919: AND hr.SUBJECT_TYPE = hg_zone.GEOGRAPHY_TYPE
3920: AND hr.SUBJECT_TABLE_NAME = 'HZ_GEOGRAPHIES'
3921: AND hr.RELATIONSHIP_CODE = 'PARENT_OF'
3922: AND hr.DIRECTIONAL_FLAG = 'F'
3923: AND hr.OBJECT_TABLE_NAME = 'HZ_GEOGRAPHIES'
3924: AND zd.geography_id = hg.GEOGRAPHY_ID

Line 3923: AND hr.OBJECT_TABLE_NAME = 'HZ_GEOGRAPHIES'

3919: AND hr.SUBJECT_TYPE = hg_zone.GEOGRAPHY_TYPE
3920: AND hr.SUBJECT_TABLE_NAME = 'HZ_GEOGRAPHIES'
3921: AND hr.RELATIONSHIP_CODE = 'PARENT_OF'
3922: AND hr.DIRECTIONAL_FLAG = 'F'
3923: AND hr.OBJECT_TABLE_NAME = 'HZ_GEOGRAPHIES'
3924: AND zd.geography_id = hg.GEOGRAPHY_ID
3925: AND hg.GEOGRAPHY_ID = hr.OBJECT_ID
3926: AND hg.GEOGRAPHY_TYPE = hr.OBJECT_TYPE
3927: AND ROWNUM = 1),

Line 3931: FROM HZ_GEOGRAPHIES hg

3927: AND ROWNUM = 1),
3928: (SELECT SUBSTR(hg.geography_element2_code,1,2)||'-'||
3929: DECODE(hg.geography_element3,null,'',SUBSTR(hg.geography_element3,1,30)||'-')||
3930: DECODE(hg.geography_element4,null,'',SUBSTR(hg.geography_element4,1,30)||'-')
3931: FROM HZ_GEOGRAPHIES hg
3932: WHERE hg.GEOGRAPHY_ID = zjb.zone_geography_id))
3933: || zjb.TAX_JURISDICTION_CODE,
3934: fnd_global.user_id,
3935: sysdate,

Line 4217: INTO HZ_GEOGRAPHIES

4213: );
4214:
4215: INSERT ALL
4216: WHEN (1=1) THEN
4217: INTO HZ_GEOGRAPHIES
4218: (
4219: GEOGRAPHY_ID,
4220: OBJECT_VERSION_NUMBER,
4221: GEOGRAPHY_TYPE,

Line 4253: hz_geographies_s.nextval,

4249: LAST_UPDATE_LOGIN
4250: )
4251: VALUES
4252: (
4253: hz_geographies_s.nextval,
4254: 1,
4255: geography_type,
4256: geography_name,
4257: 'MASTER_REF',

Line 4274: hz_geographies_s.nextval,

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',

Line 4306: hz_geographies_s.nextval,

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,

Line 4344: hz_geographies_s.nextval,

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,

Line 4395: 'HZ_GEOGRAPHIES',

4391: (
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',

Line 4396: hz_geographies_s.nextval,

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',

Line 4398: 'HZ_GEOGRAPHIES',

4394: parent_geography_type,
4395: 'HZ_GEOGRAPHIES',
4396: hz_geographies_s.nextval,
4397: geography_type,
4398: 'HZ_GEOGRAPHIES',
4399: 'PARENT_OF',
4400: 'F',
4401: null,
4402: start_date,

Line 4451: hz_geographies_s.nextval,

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,

Line 4453: 'HZ_GEOGRAPHIES',

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,
4456: 'HZ_GEOGRAPHIES',
4457: 'CHILD_OF',

Line 4456: 'HZ_GEOGRAPHIES',

4452: geography_type,
4453: 'HZ_GEOGRAPHIES',
4454: parent_geography_id,
4455: parent_geography_type,
4456: 'HZ_GEOGRAPHIES',
4457: 'CHILD_OF',
4458: 'B',
4459: null,
4460: start_date,

Line 4504: hz_geographies_s.nextval,

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',

Line 4505: 'HZ_GEOGRAPHIES',

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',
4509: geography_type,

Line 4507: hz_geographies_s.nextval,

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',

Line 4508: 'HZ_GEOGRAPHIES',

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',
4512: 'Y',

Line 4552: 'HZ_GEOGRAPHIES',

4548: VALUES
4549: (
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,

Line 4554: hz_geographies_s.nextval,

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: '',

Line 4555: 'HZ_GEOGRAPHIES',

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: '',
4559: '',

Line 4600: 'HZ_GEOGRAPHIES',

4596: VALUES
4597: (
4598: 'MASTER_REF',
4599: geography_element3_id,
4600: 'HZ_GEOGRAPHIES',
4601: 'COUNTY',
4602: hz_geographies_s.nextval,
4603: 'HZ_GEOGRAPHIES',
4604: geography_type,

Line 4602: hz_geographies_s.nextval,

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: '',

Line 4603: 'HZ_GEOGRAPHIES',

4599: geography_element3_id,
4600: 'HZ_GEOGRAPHIES',
4601: 'COUNTY',
4602: hz_geographies_s.nextval,
4603: 'HZ_GEOGRAPHIES',
4604: geography_type,
4605: 2 ,
4606: '',
4607: '',

Line 4647: 'HZ_GEOGRAPHIES',

4643: VALUES
4644: (
4645: 'MASTER_REF',
4646: geography_element2_id,
4647: 'HZ_GEOGRAPHIES',
4648: 'STATE',
4649: hz_geographies_s.nextval,
4650: 'HZ_GEOGRAPHIES',
4651: geography_type,

Line 4649: hz_geographies_s.nextval,

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: '',

Line 4650: 'HZ_GEOGRAPHIES',

4646: geography_element2_id,
4647: 'HZ_GEOGRAPHIES',
4648: 'STATE',
4649: hz_geographies_s.nextval,
4650: 'HZ_GEOGRAPHIES',
4651: geography_type,
4652: 3 ,
4653: '',
4654: '',

Line 4694: 'HZ_GEOGRAPHIES',

4690: VALUES
4691: (
4692: 'MASTER_REF',
4693: geography_element1_id,
4694: 'HZ_GEOGRAPHIES',
4695: 'COUNTRY',
4696: hz_geographies_s.nextval,
4697: 'HZ_GEOGRAPHIES',
4698: geography_type,

Line 4696: hz_geographies_s.nextval,

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: '',

Line 4697: 'HZ_GEOGRAPHIES',

4693: geography_element1_id,
4694: 'HZ_GEOGRAPHIES',
4695: 'COUNTRY',
4696: hz_geographies_s.nextval,
4697: 'HZ_GEOGRAPHIES',
4698: geography_type,
4699: 4 ,
4700: '',
4701: '',

Line 4773: hz_geographies g

4769: (
4770: val[iteration_number] = from_code[0]+iteration_number
4771: )
4772: ) v,
4773: hz_geographies g
4774: WHERE v.geography_id = g.geography_id
4775: AND g.country_code = 'US'
4776: AND NOT EXISTS ( SELECT /*+ordered */'1'
4777: FROM hz_geographies g1,

Line 4777: FROM hz_geographies g1,

4773: hz_geographies g
4774: WHERE v.geography_id = g.geography_id
4775: AND g.country_code = 'US'
4776: AND NOT EXISTS ( SELECT /*+ordered */'1'
4777: FROM hz_geographies g1,
4778: hz_relationships rel
4779: WHERE rel.subject_id = g.geography_id
4780: AND rel.subject_type = g.geography_type
4781: AND rel.subject_table_name = 'HZ_GEOGRAPHIES'

Line 4781: AND rel.subject_table_name = 'HZ_GEOGRAPHIES'

4777: FROM hz_geographies g1,
4778: hz_relationships rel
4779: WHERE rel.subject_id = g.geography_id
4780: AND rel.subject_type = g.geography_type
4781: AND rel.subject_table_name = 'HZ_GEOGRAPHIES'
4782: AND rel.object_id = g1.geography_id
4783: AND rel.object_type = 'POSTAL_CODE'
4784: AND rel.object_table_name = 'HZ_GEOGRAPHIES'
4785: AND g1.geography_code = v.zip_code

Line 4784: AND rel.object_table_name = 'HZ_GEOGRAPHIES'

4780: AND rel.subject_type = g.geography_type
4781: AND rel.subject_table_name = 'HZ_GEOGRAPHIES'
4782: AND rel.object_id = g1.geography_id
4783: AND rel.object_type = 'POSTAL_CODE'
4784: AND rel.object_table_name = 'HZ_GEOGRAPHIES'
4785: AND g1.geography_code = v.zip_code
4786: AND g1.geography_type = 'POSTAL_CODE'
4787: AND rel.relationship_type = 'MASTER_REF')
4788: GROUP BY v.zip_code ,