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.

[Home] [Help]

APPS.ZX_TAX_CONTENT_UPLOAD dependencies on HZ_GEOGRAPHIES

Line 575: FROM HZ_GEOGRAPHIES

571: ) IS
572:
573: CURSOR C_CNTRY_ID IS
574: SELECT GEOGRAPHY_ID
575: FROM HZ_GEOGRAPHIES
576: WHERE GEOGRAPHY_CODE = 'US'
577: AND GEOGRAPHY_TYPE = 'COUNTRY'
578: AND GEOGRAPHY_USE = 'MASTER_REF';
579:

Line 600: HZ_GEOGRAPHIES Y,

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

Line 629: FROM HZ_GEOGRAPHIES Y

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

Line 678: FROM HZ_GEOGRAPHIES Y,

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

Line 735: FROM HZ_GEOGRAPHIES Y,

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

Line 805: set geography_id = hz_geographies_s.nextval,

801: l_start := DBMS_UTILITY.GET_TIME;
802:
803: -- Get new id for new state and county
804: update ZX_DATA_UPLOAD_INTERFACE
805: set geography_id = hz_geographies_s.nextval,
806: status = 'CREATE'
807: where record_type in (1,3)
808: and geography_id is null
809: and effective_to is null;

Line 825: set geography_id = hz_geographies_s.nextval,

821:
822: -- Get new id for new city. In the case of city divided in two or more
823: -- jurisdictions, get new id only for the first one
824: update ZX_DATA_UPLOAD_INTERFACE
825: set geography_id = hz_geographies_s.nextval,
826: status = 'CREATE'
827: where record_type = 6
828: and geography_id is null
829: and effective_to is null

Line 918: set x.zone_geography_id = (select y.geography_id from hz_geographies y

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))

Line 946: set x.zone_geography_id = (select y.geography_id from hz_geographies y

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))

Line 974: set x.zone_geography_id = (select y.geography_id from hz_geographies y

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))

Line 1002: set x.zone_geography_id = hz_geographies_s.nextval

998:
999: -- Now, update the zone_geography_id for new records,
1000: -- but do this only if a rate exists
1001: update ZX_DATA_UPLOAD_INTERFACE x
1002: set x.zone_geography_id = hz_geographies_s.nextval
1003: where x.record_type IN (1,3,6)
1004: and x.zone_geography_id is null
1005: and x.effective_to is null;
1006: /*and EXISTS (select null

Line 1028: set x.zone_geography_id = (select y.geography_id from hz_geographies y

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'))
1032: and y.GEOGRAPHY_TYPE = 'US_OVERRIDE_ZONE_TYPE_'||SUBSTRB(p_tax_regime_code, 14,10))

Line 1081: set x.zone_geography_id = hz_geographies_s.nextval

1077: -- Now, update the zone_geography_id for override rates
1078: -- Update only the first row as there could be multiple rates for one
1079: -- overriding jurisdiction
1080: update ZX_DATA_UPLOAD_INTERFACE x
1081: set x.zone_geography_id = hz_geographies_s.nextval
1082: where x.record_type IN (9,10,11,12)
1083: and x.zone_geography_id is null
1084: and x.STATE_JURISDICTION_CODE is not null
1085: and x.COUNTY_JURISDICTION_CODE is not null

Line 1130: set x.zone_geography_id = (select y.geography_id from hz_geographies y

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')
1134: and y.GEOGRAPHY_TYPE = 'US_OVERRIDE_ZONE_TYPE_'||SUBSTRB(p_tax_regime_code, 14,10))

Line 1178: set x.zone_geography_id = hz_geographies_s.nextval

1174: -- Now, update the zone_geography_id for override rates
1175: -- Update only the first row as there could be multiple rates for one
1176: -- overriding jurisdiction
1177: update ZX_DATA_UPLOAD_INTERFACE x
1178: set x.zone_geography_id = hz_geographies_s.nextval
1179: where x.record_type IN (9,10,11,12)
1180: and x.zone_geography_id is null
1181: and x.STATE_JURISDICTION_CODE is not null
1182: and x.COUNTY_JURISDICTION_CODE is not null

Line 1223: set x.zone_geography_id = (select y.geography_id from hz_geographies y

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'))
1227: and y.GEOGRAPHY_USE = 'TAX'

Line 1252: set x.zone_geography_id = hz_geographies_s.nextval

1248:
1249: -- Now, update the zone_geography_id for new records,
1250: -- but do this only if a rate exists
1251: update ZX_DATA_UPLOAD_INTERFACE x
1252: set x.zone_geography_id = hz_geographies_s.nextval
1253: where x.record_type = 6
1254: and x.zone_geography_id is null
1255: and x.effective_to is null;
1256: /*and EXISTS (select null

Line 2001: INTO HZ_GEOGRAPHIES

1997: LOOP*/
1998:
1999: INSERT ALL
2000: WHEN (action_type = 'CREATE' AND existing_geography_id IS NULL AND geography_id IS NOT NULL AND geography_type IS NOT NULL) THEN
2001: INTO HZ_GEOGRAPHIES
2002: (
2003: GEOGRAPHY_ID,
2004: OBJECT_VERSION_NUMBER,
2005: GEOGRAPHY_TYPE,

Line 2129: 'HZ_GEOGRAPHIES',

2125: VALUES
2126: (
2127: 'MASTER_REF',
2128: geography_id,
2129: 'HZ_GEOGRAPHIES',
2130: geography_type,
2131: geography_id,
2132: 'HZ_GEOGRAPHIES',
2133: geography_type,

Line 2132: 'HZ_GEOGRAPHIES',

2128: geography_id,
2129: 'HZ_GEOGRAPHIES',
2130: geography_type,
2131: geography_id,
2132: 'HZ_GEOGRAPHIES',
2133: geography_type,
2134: 0 ,
2135: 'N',
2136: 'Y',

Line 2338: 'HZ_GEOGRAPHIES',

2334: (
2335: hz_relationships_s.nextval,
2336: parent_geography_id,
2337: parent_geography_type,
2338: 'HZ_GEOGRAPHIES',
2339: geography_id,
2340: geography_type,
2341: 'HZ_GEOGRAPHIES',
2342: 'PARENT_OF',

Line 2341: 'HZ_GEOGRAPHIES',

2337: parent_geography_type,
2338: 'HZ_GEOGRAPHIES',
2339: geography_id,
2340: geography_type,
2341: 'HZ_GEOGRAPHIES',
2342: 'PARENT_OF',
2343: 'F',
2344: null,
2345: start_date,

Line 2396: 'HZ_GEOGRAPHIES',

2392: (
2393: hz_relationships_s.nextval,
2394: geography_id,
2395: geography_type,
2396: 'HZ_GEOGRAPHIES',
2397: parent_geography_id,
2398: parent_geography_type,
2399: 'HZ_GEOGRAPHIES',
2400: 'CHILD_OF',

Line 2399: 'HZ_GEOGRAPHIES',

2395: geography_type,
2396: 'HZ_GEOGRAPHIES',
2397: parent_geography_id,
2398: parent_geography_type,
2399: 'HZ_GEOGRAPHIES',
2400: 'CHILD_OF',
2401: 'B',
2402: null,
2403: start_date,

Line 2448: 'HZ_GEOGRAPHIES',

2444: VALUES
2445: (
2446: 'MASTER_REF',
2447: parent_geography_id,
2448: 'HZ_GEOGRAPHIES',
2449: parent_geography_type,
2450: geography_id,
2451: 'HZ_GEOGRAPHIES',
2452: geography_type,

Line 2451: 'HZ_GEOGRAPHIES',

2447: parent_geography_id,
2448: 'HZ_GEOGRAPHIES',
2449: parent_geography_type,
2450: geography_id,
2451: 'HZ_GEOGRAPHIES',
2452: geography_type,
2453: 1,
2454: '',
2455: '',

Line 2495: 'HZ_GEOGRAPHIES',

2491: VALUES
2492: (
2493: 'MASTER_REF',
2494: geography_element1_id,
2495: 'HZ_GEOGRAPHIES',
2496: geography_element1_type,
2497: geography_id,
2498: 'HZ_GEOGRAPHIES',
2499: geography_type,

Line 2498: 'HZ_GEOGRAPHIES',

2494: geography_element1_id,
2495: 'HZ_GEOGRAPHIES',
2496: geography_element1_type,
2497: geography_id,
2498: 'HZ_GEOGRAPHIES',
2499: geography_type,
2500: 2 ,
2501: '',
2502: '',

Line 2542: 'HZ_GEOGRAPHIES',

2538: VALUES
2539: (
2540: 'MASTER_REF',
2541: geography_element2_id,
2542: 'HZ_GEOGRAPHIES',
2543: geography_element2_type,
2544: geography_id,
2545: 'HZ_GEOGRAPHIES',
2546: geography_type,

Line 2545: 'HZ_GEOGRAPHIES',

2541: geography_element2_id,
2542: 'HZ_GEOGRAPHIES',
2543: geography_element2_type,
2544: geography_id,
2545: 'HZ_GEOGRAPHIES',
2546: geography_type,
2547: 2 ,
2548: '',
2549: '',

Line 2588: 'HZ_GEOGRAPHIES',

2584: VALUES
2585: (
2586: 'MASTER_REF',
2587: geography_element1_id,
2588: 'HZ_GEOGRAPHIES',
2589: geography_element1_type,
2590: geography_id,
2591: 'HZ_GEOGRAPHIES',
2592: geography_type,

Line 2591: 'HZ_GEOGRAPHIES',

2587: geography_element1_id,
2588: 'HZ_GEOGRAPHIES',
2589: geography_element1_type,
2590: geography_id,
2591: 'HZ_GEOGRAPHIES',
2592: geography_type,
2593: 3 ,
2594: '',
2595: '',

Line 2685: FROM HZ_GEOGRAPHIES hzg

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

Line 2724: FROM HZ_GEOGRAPHIES hzg

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

Line 2769: FROM HZ_GEOGRAPHIES hzg

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

Line 2897: INTO HZ_GEOGRAPHIES

2893: LOOP**/
2894:
2895: INSERT ALL
2896: WHEN (zone_geography_type IS NOT NULL AND zone_geography_id IS NOT NULL AND existing_geography_id IS NULL) THEN
2897: INTO HZ_GEOGRAPHIES
2898: (
2899: GEOGRAPHY_ID,
2900: OBJECT_VERSION_NUMBER,
2901: GEOGRAPHY_TYPE,

Line 3047: 'HZ_GEOGRAPHIES',

3043: (
3044: hz_relationships_s.nextval,
3045: zone_geography_id,
3046: zone_geography_type,
3047: 'HZ_GEOGRAPHIES',
3048: geography_id,
3049: geography_type,
3050: 'HZ_GEOGRAPHIES',
3051: 'PARENT_OF',

Line 3050: 'HZ_GEOGRAPHIES',

3046: zone_geography_type,
3047: 'HZ_GEOGRAPHIES',
3048: geography_id,
3049: geography_type,
3050: 'HZ_GEOGRAPHIES',
3051: 'PARENT_OF',
3052: 'F',
3053: null,
3054: start_date,

Line 3105: 'HZ_GEOGRAPHIES',

3101: (
3102: hz_relationships_s.nextval,
3103: geography_id,
3104: geography_type,
3105: 'HZ_GEOGRAPHIES',
3106: zone_geography_id,
3107: zone_geography_type,
3108: 'HZ_GEOGRAPHIES',
3109: 'CHILD_OF',

Line 3108: 'HZ_GEOGRAPHIES',

3104: geography_type,
3105: 'HZ_GEOGRAPHIES',
3106: zone_geography_id,
3107: zone_geography_type,
3108: 'HZ_GEOGRAPHIES',
3109: 'CHILD_OF',
3110: 'B',
3111: null,
3112: start_date,

Line 3197: from hz_geographies

3193: and j.tax = v.tax
3194: and j.zone_geography_id = v.zone_geography_id
3195: 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,
3196: (select geography_id
3197: from hz_geographies
3198: where geography_id = v.zone_geography_id) existing_geography_id,
3199: v.inner_city_flag,
3200: -- Bug 6393452
3201: CITY_ROW_NUMBER,

Line 3551: FROM HZ_GEOGRAPHIES hg

3547: SELECT zjb.TAX_JURISDICTION_ID,
3548: DECODE((SELECT SUBSTR(hg.geography_element2_code,1,2)||'-'||
3549: DECODE(hg.geography_element3,null,'',SUBSTR(hg.geography_element3,1,30)||'-')||
3550: DECODE(hg.geography_element4,null,'',SUBSTR(hg.geography_element4,1,30)||'-')
3551: FROM HZ_GEOGRAPHIES hg
3552: WHERE hg.GEOGRAPHY_ID = zjb.zone_geography_id),
3553: '-',
3554: (SELECT SUBSTR(hg.geography_element2_code,1,2)||'-'||
3555: DECODE(hg.geography_element3,null,'',SUBSTR(hg.geography_element3,1,30)||'-')||

Line 3557: FROM hz_geographies hg_zone,

3553: '-',
3554: (SELECT SUBSTR(hg.geography_element2_code,1,2)||'-'||
3555: DECODE(hg.geography_element3,null,'',SUBSTR(hg.geography_element3,1,30)||'-')||
3556: DECODE(hg.geography_element4,null,'',SUBSTR(hg.geography_element4,1,30)||'-')
3557: FROM hz_geographies hg_zone,
3558: hz_relationships hr,
3559: hz_geographies hg
3560: WHERE hg_zone.GEOGRAPHY_ID = zjb.zone_geography_id
3561: AND hr.SUBJECT_ID = hg_zone.GEOGRAPHY_ID

Line 3559: hz_geographies hg

3555: DECODE(hg.geography_element3,null,'',SUBSTR(hg.geography_element3,1,30)||'-')||
3556: DECODE(hg.geography_element4,null,'',SUBSTR(hg.geography_element4,1,30)||'-')
3557: FROM hz_geographies hg_zone,
3558: hz_relationships hr,
3559: hz_geographies hg
3560: WHERE hg_zone.GEOGRAPHY_ID = zjb.zone_geography_id
3561: AND hr.SUBJECT_ID = hg_zone.GEOGRAPHY_ID
3562: AND hr.SUBJECT_TYPE = hg_zone.GEOGRAPHY_TYPE
3563: AND hr.SUBJECT_TABLE_NAME = 'HZ_GEOGRAPHIES'

Line 3563: AND hr.SUBJECT_TABLE_NAME = 'HZ_GEOGRAPHIES'

3559: hz_geographies hg
3560: WHERE hg_zone.GEOGRAPHY_ID = zjb.zone_geography_id
3561: AND hr.SUBJECT_ID = hg_zone.GEOGRAPHY_ID
3562: AND hr.SUBJECT_TYPE = hg_zone.GEOGRAPHY_TYPE
3563: AND hr.SUBJECT_TABLE_NAME = 'HZ_GEOGRAPHIES'
3564: AND hr.RELATIONSHIP_CODE = 'PARENT_OF'
3565: AND hr.DIRECTIONAL_FLAG = 'F'
3566: AND hr.OBJECT_TABLE_NAME = 'HZ_GEOGRAPHIES'
3567: AND hg.GEOGRAPHY_ID = hr.OBJECT_ID

Line 3566: AND hr.OBJECT_TABLE_NAME = 'HZ_GEOGRAPHIES'

3562: AND hr.SUBJECT_TYPE = hg_zone.GEOGRAPHY_TYPE
3563: AND hr.SUBJECT_TABLE_NAME = 'HZ_GEOGRAPHIES'
3564: AND hr.RELATIONSHIP_CODE = 'PARENT_OF'
3565: AND hr.DIRECTIONAL_FLAG = 'F'
3566: AND hr.OBJECT_TABLE_NAME = 'HZ_GEOGRAPHIES'
3567: AND hg.GEOGRAPHY_ID = hr.OBJECT_ID
3568: AND hg.GEOGRAPHY_TYPE = hr.OBJECT_TYPE
3569: AND ROWNUM = 1),
3570: (SELECT SUBSTR(hg.geography_element2_code,1,2)||'-'||

Line 3573: FROM HZ_GEOGRAPHIES hg

3569: AND ROWNUM = 1),
3570: (SELECT SUBSTR(hg.geography_element2_code,1,2)||'-'||
3571: DECODE(hg.geography_element3,null,'',SUBSTR(hg.geography_element3,1,30)||'-')||
3572: DECODE(hg.geography_element4,null,'',SUBSTR(hg.geography_element4,1,30)||'-')
3573: FROM HZ_GEOGRAPHIES hg
3574: WHERE hg.GEOGRAPHY_ID = zjb.zone_geography_id))
3575: || zjb.TAX_JURISDICTION_CODE,
3576: fnd_global.user_id,
3577: sysdate,

Line 3851: INTO HZ_GEOGRAPHIES

3847: LOOP**/
3848:
3849: INSERT ALL
3850: WHEN (1=1) THEN
3851: INTO HZ_GEOGRAPHIES
3852: (
3853: GEOGRAPHY_ID,
3854: OBJECT_VERSION_NUMBER,
3855: GEOGRAPHY_TYPE,

Line 3887: hz_geographies_s.nextval,

3883: LAST_UPDATE_LOGIN
3884: )
3885: VALUES
3886: (
3887: hz_geographies_s.nextval,
3888: 1,
3889: geography_type,
3890: geography_name,
3891: 'MASTER_REF',

Line 3908: hz_geographies_s.nextval,

3904: geography_element4,
3905: geography_element4_id,
3906: geography_element4_code,
3907: geography_name,
3908: hz_geographies_s.nextval,
3909: null,
3910: G_CREATED_BY_MODULE,
3911: country_code,
3912: 'PST',

Line 3940: hz_geographies_s.nextval,

3936: LAST_UPDATE_LOGIN
3937: )
3938: VALUES
3939: (
3940: hz_geographies_s.nextval,
3941: p_tax_content_source,
3942: 'STANDARD_NAME',
3943: geography_name,
3944: 1,

Line 3978: hz_geographies_s.nextval,

3974: LAST_UPDATE_LOGIN
3975: )
3976: VALUES
3977: (
3978: hz_geographies_s.nextval,
3979: p_tax_content_source,
3980: 'FIPS_CODE',
3981: geography_code,
3982: 1,

Line 4029: 'HZ_GEOGRAPHIES',

4025: (
4026: hz_relationships_s.nextval,
4027: parent_geography_id,
4028: parent_geography_type,
4029: 'HZ_GEOGRAPHIES',
4030: hz_geographies_s.nextval,
4031: geography_type,
4032: 'HZ_GEOGRAPHIES',
4033: 'PARENT_OF',

Line 4030: hz_geographies_s.nextval,

4026: hz_relationships_s.nextval,
4027: parent_geography_id,
4028: parent_geography_type,
4029: 'HZ_GEOGRAPHIES',
4030: hz_geographies_s.nextval,
4031: geography_type,
4032: 'HZ_GEOGRAPHIES',
4033: 'PARENT_OF',
4034: 'F',

Line 4032: 'HZ_GEOGRAPHIES',

4028: parent_geography_type,
4029: 'HZ_GEOGRAPHIES',
4030: hz_geographies_s.nextval,
4031: geography_type,
4032: 'HZ_GEOGRAPHIES',
4033: 'PARENT_OF',
4034: 'F',
4035: null,
4036: start_date,

Line 4085: hz_geographies_s.nextval,

4081: )
4082: VALUES
4083: (
4084: hz_relationships_s.nextval,
4085: hz_geographies_s.nextval,
4086: geography_type,
4087: 'HZ_GEOGRAPHIES',
4088: parent_geography_id,
4089: parent_geography_type,

Line 4087: 'HZ_GEOGRAPHIES',

4083: (
4084: hz_relationships_s.nextval,
4085: hz_geographies_s.nextval,
4086: geography_type,
4087: 'HZ_GEOGRAPHIES',
4088: parent_geography_id,
4089: parent_geography_type,
4090: 'HZ_GEOGRAPHIES',
4091: 'CHILD_OF',

Line 4090: 'HZ_GEOGRAPHIES',

4086: geography_type,
4087: 'HZ_GEOGRAPHIES',
4088: parent_geography_id,
4089: parent_geography_type,
4090: 'HZ_GEOGRAPHIES',
4091: 'CHILD_OF',
4092: 'B',
4093: null,
4094: start_date,

Line 4138: hz_geographies_s.nextval,

4134: )
4135: VALUES
4136: (
4137: 'MASTER_REF',
4138: hz_geographies_s.nextval,
4139: 'HZ_GEOGRAPHIES',
4140: geography_type,
4141: hz_geographies_s.nextval,
4142: 'HZ_GEOGRAPHIES',

Line 4139: 'HZ_GEOGRAPHIES',

4135: VALUES
4136: (
4137: 'MASTER_REF',
4138: hz_geographies_s.nextval,
4139: 'HZ_GEOGRAPHIES',
4140: geography_type,
4141: hz_geographies_s.nextval,
4142: 'HZ_GEOGRAPHIES',
4143: geography_type,

Line 4141: hz_geographies_s.nextval,

4137: 'MASTER_REF',
4138: hz_geographies_s.nextval,
4139: 'HZ_GEOGRAPHIES',
4140: geography_type,
4141: hz_geographies_s.nextval,
4142: 'HZ_GEOGRAPHIES',
4143: geography_type,
4144: 0 ,
4145: 'N',

Line 4142: 'HZ_GEOGRAPHIES',

4138: hz_geographies_s.nextval,
4139: 'HZ_GEOGRAPHIES',
4140: geography_type,
4141: hz_geographies_s.nextval,
4142: 'HZ_GEOGRAPHIES',
4143: geography_type,
4144: 0 ,
4145: 'N',
4146: 'Y',

Line 4186: 'HZ_GEOGRAPHIES',

4182: VALUES
4183: (
4184: 'MASTER_REF',
4185: parent_geography_id,
4186: 'HZ_GEOGRAPHIES',
4187: parent_geography_type,
4188: hz_geographies_s.nextval,
4189: 'HZ_GEOGRAPHIES',
4190: geography_type,

Line 4188: hz_geographies_s.nextval,

4184: 'MASTER_REF',
4185: parent_geography_id,
4186: 'HZ_GEOGRAPHIES',
4187: parent_geography_type,
4188: hz_geographies_s.nextval,
4189: 'HZ_GEOGRAPHIES',
4190: geography_type,
4191: 1,
4192: '',

Line 4189: 'HZ_GEOGRAPHIES',

4185: parent_geography_id,
4186: 'HZ_GEOGRAPHIES',
4187: parent_geography_type,
4188: hz_geographies_s.nextval,
4189: 'HZ_GEOGRAPHIES',
4190: geography_type,
4191: 1,
4192: '',
4193: '',

Line 4234: 'HZ_GEOGRAPHIES',

4230: VALUES
4231: (
4232: 'MASTER_REF',
4233: geography_element3_id,
4234: 'HZ_GEOGRAPHIES',
4235: 'COUNTY',
4236: hz_geographies_s.nextval,
4237: 'HZ_GEOGRAPHIES',
4238: geography_type,

Line 4236: hz_geographies_s.nextval,

4232: 'MASTER_REF',
4233: geography_element3_id,
4234: 'HZ_GEOGRAPHIES',
4235: 'COUNTY',
4236: hz_geographies_s.nextval,
4237: 'HZ_GEOGRAPHIES',
4238: geography_type,
4239: 2 ,
4240: '',

Line 4237: 'HZ_GEOGRAPHIES',

4233: geography_element3_id,
4234: 'HZ_GEOGRAPHIES',
4235: 'COUNTY',
4236: hz_geographies_s.nextval,
4237: 'HZ_GEOGRAPHIES',
4238: geography_type,
4239: 2 ,
4240: '',
4241: '',

Line 4281: 'HZ_GEOGRAPHIES',

4277: VALUES
4278: (
4279: 'MASTER_REF',
4280: geography_element2_id,
4281: 'HZ_GEOGRAPHIES',
4282: 'STATE',
4283: hz_geographies_s.nextval,
4284: 'HZ_GEOGRAPHIES',
4285: geography_type,

Line 4283: hz_geographies_s.nextval,

4279: 'MASTER_REF',
4280: geography_element2_id,
4281: 'HZ_GEOGRAPHIES',
4282: 'STATE',
4283: hz_geographies_s.nextval,
4284: 'HZ_GEOGRAPHIES',
4285: geography_type,
4286: 3 ,
4287: '',

Line 4284: 'HZ_GEOGRAPHIES',

4280: geography_element2_id,
4281: 'HZ_GEOGRAPHIES',
4282: 'STATE',
4283: hz_geographies_s.nextval,
4284: 'HZ_GEOGRAPHIES',
4285: geography_type,
4286: 3 ,
4287: '',
4288: '',

Line 4328: 'HZ_GEOGRAPHIES',

4324: VALUES
4325: (
4326: 'MASTER_REF',
4327: geography_element1_id,
4328: 'HZ_GEOGRAPHIES',
4329: 'COUNTRY',
4330: hz_geographies_s.nextval,
4331: 'HZ_GEOGRAPHIES',
4332: geography_type,

Line 4330: hz_geographies_s.nextval,

4326: 'MASTER_REF',
4327: geography_element1_id,
4328: 'HZ_GEOGRAPHIES',
4329: 'COUNTRY',
4330: hz_geographies_s.nextval,
4331: 'HZ_GEOGRAPHIES',
4332: geography_type,
4333: 4 ,
4334: '',

Line 4331: 'HZ_GEOGRAPHIES',

4327: geography_element1_id,
4328: 'HZ_GEOGRAPHIES',
4329: 'COUNTRY',
4330: hz_geographies_s.nextval,
4331: 'HZ_GEOGRAPHIES',
4332: geography_type,
4333: 4 ,
4334: '',
4335: '',

Line 4407: hz_geographies g

4403: (
4404: val[iteration_number] = from_code[0]+iteration_number
4405: )
4406: ) v,
4407: hz_geographies g
4408: WHERE v.geography_id = g.geography_id
4409: AND g.country_code = 'US'
4410: AND NOT EXISTS ( SELECT /*+ordered */'1'
4411: FROM hz_geographies g1,

Line 4411: FROM hz_geographies g1,

4407: hz_geographies g
4408: WHERE v.geography_id = g.geography_id
4409: AND g.country_code = 'US'
4410: AND NOT EXISTS ( SELECT /*+ordered */'1'
4411: FROM hz_geographies g1,
4412: hz_relationships rel
4413: WHERE rel.subject_id = g.geography_id
4414: AND rel.subject_type = g.geography_type
4415: AND rel.subject_table_name = 'HZ_GEOGRAPHIES'

Line 4415: AND rel.subject_table_name = 'HZ_GEOGRAPHIES'

4411: FROM hz_geographies g1,
4412: hz_relationships rel
4413: WHERE rel.subject_id = g.geography_id
4414: AND rel.subject_type = g.geography_type
4415: AND rel.subject_table_name = 'HZ_GEOGRAPHIES'
4416: AND rel.object_id = g1.geography_id
4417: AND rel.object_type = 'POSTAL_CODE'
4418: AND rel.object_table_name = 'HZ_GEOGRAPHIES'
4419: AND g1.geography_code = v.zip_code

Line 4418: AND rel.object_table_name = 'HZ_GEOGRAPHIES'

4414: AND rel.subject_type = g.geography_type
4415: AND rel.subject_table_name = 'HZ_GEOGRAPHIES'
4416: AND rel.object_id = g1.geography_id
4417: AND rel.object_type = 'POSTAL_CODE'
4418: AND rel.object_table_name = 'HZ_GEOGRAPHIES'
4419: AND g1.geography_code = v.zip_code
4420: AND g1.geography_type = 'POSTAL_CODE'
4421: AND rel.relationship_type = 'MASTER_REF')
4422: GROUP BY v.zip_code ,