[Home] [Help]
PACKAGE BODY: APPS.ZX_GEO_JUR_MIGR_PKG
Source
1 PACKAGE BODY ZX_GEO_JUR_MIGR_PKG AS
2 /* $Header: zxgeojurmigrb.pls 120.24.12010000.2 2008/11/12 12:28:45 spasala ship $ */
3
4
5 PROCEDURE CREATE_ZONE_RANGE(p_zone_type IN VARCHAR2,
6 p_zone_name IN VARCHAR2,
7 p_zone_code IN VARCHAR2,
8 p_zone_code_type IN VARCHAR2,
9 p_zone_name_prefix IN VARCHAR2,
10 p_start_date IN DATE,
11 p_end_date IN DATE,
12 p_zone_relation_tbl IN HZ_GEOGRAPHY_PUB.ZONE_RELATION_TBL_TYPE,
13 x_zone_geography_id OUT NOCOPY NUMBER) IS
14
15 l_geography_range_rec HZ_GEOGRAPHY_PUB.GEOGRAPHY_RANGE_REC_TYPE;
16 l_zone_name VARCHAR2(360);
17 i BINARY_INTEGER;
18 x_msg_count NUMBER;
19 x_msg_data VARCHAR2(2000);
20 x_return_status VARCHAR2(1);
21 l_count NUMBER;
22 l_zone_exists VARCHAR2(6);
23 BEGIN
24 l_zone_name := p_zone_name_prefix || ' '||p_zone_name;
25 --
26 BEGIN
27 SELECT geography_id
28 INTO x_zone_geography_id
29 FROM hz_geographies
30 WHERE geography_type = p_zone_type
31 AND geography_name = l_zone_name;
32 --
33 l_zone_exists := 'TRUE';
34 --
35 EXCEPTION WHEN NO_DATA_FOUND THEN
36 l_zone_exists := 'FALSE';
37 END;
38
39 IF l_zone_exists = 'FALSE' THEN
40 HZ_GEOGRAPHY_PUB.create_zone(
41 p_init_msg_list => 'T',
42 p_zone_type => p_zone_type,
43 p_zone_name => l_zone_name,
44 p_zone_code => p_zone_code,
45 p_zone_code_type => 'FIPS_CODE',
46 p_start_date => to_date('01-01-1952', 'MM-DD-YYYY'),
47 p_end_date => null,
48 p_geo_data_provider => NULL,
49 p_language_code => NULL,
50 p_zone_relation_tbl => p_zone_relation_tbl,
51 p_geometry => NULL,
52 p_timezone_code => NULL,
53 x_geography_id => x_zone_geography_id,
54 p_created_by_module => 'EBTAX_MIGRAION',
55 p_application_id => 235,
56 x_return_status => x_return_status,
57 x_msg_count => x_msg_count,
58 x_msg_data => x_msg_data
59 );
60 END IF;
61 --
62
63 END;
64
65
66 PROCEDURE CREATE_GEO_TYPE IS
67
68 CURSOR geo_type IS
69 SELECT segment_attribute_type
70 FROM fnd_segment_attribute_values seg, ar_system_parameters_all sys
71 WHERE seg.id_flex_code = 'RLOC'
72 AND seg.id_flex_num = sys.location_structure_id
73 AND seg.attribute_value = 'Y'
74 AND segment_attribute_type NOT IN ('TAX_ACCOUNT', 'EXEMPT_LEVEL')
75 GROUP BY segment_attribute_type;
76
77 l_geo_type VARCHAR2(30);
78 l_geography_type_rec HZ_GEOGRAPHY_STRUCTURE_PUB.GEOGRAPHY_TYPE_REC_TYPE;
79 x_geography_type VARCHAR2(30);
80 x_return_status VARCHAR2(1);
81 x_msg_count NUMBER;
82 x_msg_data VARCHAR2(2000);
83 BEGIN
84 OPEN geo_type;
85 LOOP
86 FETCH geo_type INTO l_geography_type_rec.geography_type;
87 EXIT WHEN geo_type%NOTFOUND;
88 l_geography_type_rec.created_by_module := 'EBTAX_MIGRATION';
89 l_geography_type_rec.application_id := 235;
90
91 -- Create Postal Code also though it is not in use in any structure.
92
93 HZ_GEOGRAPHY_STRUCTURE_PUB.create_geography_type('T',
94 l_geography_type_rec,
95 x_return_status,
96 x_msg_count,
97 x_msg_data);
98 END LOOP;
99 l_geography_type_rec.geography_type := 'REGION';
100 l_geography_type_rec.created_by_module := 'EBTAX_MIGRATION';
101 l_geography_type_rec.application_id := 235;
102
103
104 HZ_GEOGRAPHY_STRUCTURE_PUB.create_geography_type('T',
105 l_geography_type_rec,
106 x_return_status,
107 x_msg_count,
108 x_msg_data);
109 --EXCEPTION WHEN OTHERS THEN
110 -- NULL;
111
112 END;
113
114 PROCEDURE CREATE_ZONE_TYPE IS
115 l_zone_type_rec HZ_GEOGRAPHY_STRUCTURE_PUB.ZONE_TYPE_REC_TYPE;
116 TYPE seg_attr_type_tbl IS TABLE OF VARCHAR2(30) INDEX BY binary_integer;
117 l_segment_attribute_type_tbl seg_attr_type_tbl;
118 TYPE country_code_tbl IS TABLE OF VARCHAR2(60) INDEX BY BINARY_INTEGER;
119 l_country_code_tbl country_code_tbl;
120 TYPE location_structure_id_tbl IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
121 l_location_structure_id_tbl location_structure_id_tbl;
122 TYPE geography_id_tbl IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
123 l_geography_id_tbl geography_id_tbl;
124 l_incl_geo_type HZ_GEOGRAPHY_STRUCTURE_PUB.INCL_GEO_TYPE_TBL_TYPE;
125 l_incl_geo_type1 HZ_GEOGRAPHY_STRUCTURE_PUB.INCL_GEO_TYPE_TBL_TYPE;
126 l_geo_rel_type_rec HZ_GEOGRAPHY_STRUCTURE_PUB.GEO_REL_TYPE_REC_TYPE;
127 n BINARY_INTEGER;
128 l_geography_id NUMBER;
129 l_prev_structure_id NUMBER := -99;
130 l_prev_country_code VARCHAR2(10) := '-1';
131 x_relationship_type_id NUMBER;
132 x_return_status VARCHAR2(1);
133 x_msg_count NUMBER;
134 x_msg_data VARCHAR2(2000);
135 BEGIN
136 --Create city tax zone type for all countries with location based tax code.
137 --Create override geo type for state and county in case of US
138 SELECT sys.location_structure_id,
139 SUBSTRB(sys.default_country,1, 2) default_country,
140 attr.segment_attribute_type,
141 geo.geography_id
142 BULK COLLECT INTO
143 l_location_structure_id_tbl, l_country_code_tbl,
144 l_segment_attribute_type_tbl, l_geography_id_tbl
145 FROM fnd_id_flex_structures struct, fnd_id_flex_segments_vl seg, fnd_segment_attribute_values attr,
146 ar_system_parameters_all sys, hz_geographies geo
147 WHERE struct.application_id = seg.application_id
148 AND struct.id_flex_code = seg.id_flex_code
149 AND struct.id_flex_num = seg.id_flex_num
150 AND struct.freeze_flex_definition_flag = 'Y'
151 AND struct.enabled_flag = 'Y'
152 AND seg.application_id = attr.application_id
153 AND seg.id_flex_code = attr.id_flex_code
154 AND seg.id_flex_num = attr.id_flex_num
155 AND seg.application_column_name = attr.application_column_name
156 AND seg.id_flex_num = sys.location_structure_id
157 AND seg.id_flex_code = 'RLOC'
158 AND seg.enabled_flag = 'Y'
159 AND segment_attribute_type NOT IN ('TAX_ACCOUNT', 'EXEMPT_LEVEL')
160 AND attr.attribute_value = 'Y'
161 AND sys.default_country = geo.country_code
162 AND geo.geography_type = 'COUNTRY'
163 GROUP BY sys.location_structure_id, sys.default_country,
164 segment_attribute_type, geo.geography_id
165 ORDER BY sys.location_structure_id, sys.default_country,
166 segment_attribute_type, geo.geography_id;
167 --
168 FOR i IN l_segment_attribute_type_tbl.first..l_segment_attribute_type_tbl.last LOOP
169 l_incl_geo_type(1) := l_segment_attribute_type_tbl(i);
170 IF l_segment_attribute_type_tbl(i) = 'CITY' THEN
171 l_incl_geo_type(2) := 'POSTAL_CODE';
172 END IF;
173 l_zone_type_rec.geography_type := substrb(l_country_code_tbl(i)||'_'||
174 l_segment_attribute_type_tbl(i)||'_ZONE_TYPE_'||
175 substrb(to_char(l_location_structure_id_tbl(i)), 1, 6),1,30);
176 l_zone_type_rec.included_geography_type := l_incl_geo_type;
177 l_zone_type_rec.postal_code_range_flag := 'Y';
178 l_zone_type_rec.geography_use := 'TAX';
179 IF l_incl_geo_type(1) = 'COUNTRY' THEN
180 l_zone_type_rec.limited_by_geography_id := null;
181 ELSE
182 l_zone_type_rec.limited_by_geography_id := l_geography_id_tbl(i);
183 END IF;
184 l_zone_type_rec.created_by_module := 'EBTAX_MIGRATION';
185 l_zone_type_rec.application_id := 235;
186 HZ_GEOGRAPHY_STRUCTURE_PUB.create_zone_type('F',l_zone_type_rec,
187 x_return_status,
188 x_msg_count,
189 x_msg_data);
190 IF l_country_code_tbl(i) = 'US' THEN
191 l_incl_geo_type(1) := 'COUNTY';
192 l_incl_geo_type(2) := 'CITY';
193 l_incl_geo_type(3) := 'STATE';
194 l_zone_type_rec.geography_type := substrb('US' || '_'|| 'OVERRIDE_ZONE_TYPE_' ||
195 substrb(to_char(l_location_structure_id_tbl(i)), 1, 6),1,30);
196 l_zone_type_rec.included_geography_type := l_incl_geo_type;
197 l_zone_type_rec.postal_code_range_flag := 'Y';
198 l_zone_type_rec.geography_use := 'TAX';
199 l_zone_type_rec.limited_by_geography_id := l_geography_id_tbl(i);
200 l_zone_type_rec.created_by_module := 'EBTAX_MIGRATION';
201 l_zone_type_rec.application_id := 235;
202 HZ_GEOGRAPHY_STRUCTURE_PUB.create_zone_type('F',l_zone_type_rec,
203 x_return_status,
204 x_msg_count,
205 x_msg_data);
206 END IF;
207 l_incl_geo_type.delete;
208 IF l_prev_country_code <> l_country_code_tbl(i) THEN
209 IF l_country_code_tbl(i) <> 'US' THEN
210 l_incl_geo_type1(1) := l_segment_attribute_type_tbl(i);
211 l_zone_type_rec.geography_type := substrb(l_country_code_tbl(i)||'_'|| 'OVERRIDE_ZONE_TYPE_' ||
212 substrb(to_char(l_location_structure_id_tbl(i)), 1, 6),1,30);
213 l_zone_type_rec.included_geography_type := l_incl_geo_type1;
214 l_zone_type_rec.postal_code_range_flag := 'Y';
215 l_zone_type_rec.geography_use := 'TAX';
216 l_zone_type_rec.limited_by_geography_id := l_geography_id_tbl(i);
217 l_zone_type_rec.created_by_module := 'EBTAX_MIGRATION';
218 l_zone_type_rec.application_id := 235;
219 HZ_GEOGRAPHY_STRUCTURE_PUB.create_zone_type('F',l_zone_type_rec,
220 x_return_status,
221 x_msg_count,
222 x_msg_data);
223 END IF;
224 l_prev_country_code := l_country_code_tbl(i);
225 ELSE
226 IF l_country_code_tbl(i) <> 'US' THEN
227 l_geo_rel_type_rec.geography_type := l_segment_attribute_type_tbl(i);
228 l_geo_rel_type_rec.parent_geography_type :=
229 substrb(l_country_code_tbl(i)||'_'|| 'OVERRIDE_ZONE_TYPE_' ||
230 substrb(to_char(l_location_structure_id_tbl(i)), 1, 6),1,30);
231 l_geo_rel_type_rec.status := 'A';
232 l_geo_rel_type_rec.created_by_module := 'EBTAX_MIGRATION';
233 l_geo_rel_type_rec.application_id := 235;
234
235
236 HZ_GEOGRAPHY_STRUCTURE_PUB.create_geo_rel_type(
237 p_init_msg_list => 'F',
238 p_geo_rel_type_rec => l_geo_rel_type_rec,
239 x_relationship_type_id => x_relationship_type_id,
240 x_return_status => x_return_status,
241 x_msg_count => x_msg_count,
242 x_msg_data => x_msg_data
243 );
244 END IF;
245 l_prev_country_code := l_country_code_tbl(i);
246
247
248
249 END IF;
250 END LOOP;
251 END;
252
253 END;