[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.3 2009/02/09 18:50:52 sachandr 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;
133 x_msg_count NUMBER;
130 l_prev_country_code VARCHAR2(10) := '-1';
131 x_relationship_type_id NUMBER;
132 x_return_status VARCHAR2(1);
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 IF l_segment_attribute_type_tbl.count > 0 THEN
169 FOR i IN l_segment_attribute_type_tbl.first..l_segment_attribute_type_tbl.last LOOP
170 l_incl_geo_type(1) := l_segment_attribute_type_tbl(i);
171 IF l_segment_attribute_type_tbl(i) = 'CITY' THEN
172 l_incl_geo_type(2) := 'POSTAL_CODE';
173 END IF;
174 l_zone_type_rec.geography_type := substrb(l_country_code_tbl(i)||'_'||
175 l_segment_attribute_type_tbl(i)||'_ZONE_TYPE_'||
176 substrb(to_char(l_location_structure_id_tbl(i)), 1, 6),1,30);
177 l_zone_type_rec.included_geography_type := l_incl_geo_type;
178 l_zone_type_rec.postal_code_range_flag := 'Y';
179 l_zone_type_rec.geography_use := 'TAX';
180 IF l_incl_geo_type(1) = 'COUNTRY' THEN
181 l_zone_type_rec.limited_by_geography_id := null;
182 ELSE
183 l_zone_type_rec.limited_by_geography_id := l_geography_id_tbl(i);
184 END IF;
185 l_zone_type_rec.created_by_module := 'EBTAX_MIGRATION';
186 l_zone_type_rec.application_id := 235;
187 HZ_GEOGRAPHY_STRUCTURE_PUB.create_zone_type('F',l_zone_type_rec,
188 x_return_status,
189 x_msg_count,
190 x_msg_data);
191 IF l_country_code_tbl(i) = 'US' THEN
192 l_incl_geo_type(1) := 'COUNTY';
193 l_incl_geo_type(2) := 'CITY';
194 l_incl_geo_type(3) := 'STATE';
195 l_zone_type_rec.geography_type := substrb('US' || '_'|| 'OVERRIDE_ZONE_TYPE_' ||
196 substrb(to_char(l_location_structure_id_tbl(i)), 1, 6),1,30);
197 l_zone_type_rec.included_geography_type := l_incl_geo_type;
198 l_zone_type_rec.postal_code_range_flag := 'Y';
199 l_zone_type_rec.geography_use := 'TAX';
200 l_zone_type_rec.limited_by_geography_id := l_geography_id_tbl(i);
201 l_zone_type_rec.created_by_module := 'EBTAX_MIGRATION';
202 l_zone_type_rec.application_id := 235;
203 HZ_GEOGRAPHY_STRUCTURE_PUB.create_zone_type('F',l_zone_type_rec,
204 x_return_status,
205 x_msg_count,
206 x_msg_data);
207 END IF;
208 l_incl_geo_type.delete;
209 IF l_prev_country_code <> l_country_code_tbl(i) THEN
210 IF l_country_code_tbl(i) <> 'US' THEN
211 l_incl_geo_type1(1) := l_segment_attribute_type_tbl(i);
212 l_zone_type_rec.geography_type := substrb(l_country_code_tbl(i)||'_'|| 'OVERRIDE_ZONE_TYPE_' ||
213 substrb(to_char(l_location_structure_id_tbl(i)), 1, 6),1,30);
214 l_zone_type_rec.included_geography_type := l_incl_geo_type1;
215 l_zone_type_rec.postal_code_range_flag := 'Y';
216 l_zone_type_rec.geography_use := 'TAX';
217 l_zone_type_rec.limited_by_geography_id := l_geography_id_tbl(i);
218 l_zone_type_rec.created_by_module := 'EBTAX_MIGRATION';
219 l_zone_type_rec.application_id := 235;
220 HZ_GEOGRAPHY_STRUCTURE_PUB.create_zone_type('F',l_zone_type_rec,
221 x_return_status,
222 x_msg_count,
223 x_msg_data);
224 END IF;
225 l_prev_country_code := l_country_code_tbl(i);
226 ELSE
227 IF l_country_code_tbl(i) <> 'US' THEN
228 l_geo_rel_type_rec.geography_type := l_segment_attribute_type_tbl(i);
229 l_geo_rel_type_rec.parent_geography_type :=
230 substrb(l_country_code_tbl(i)||'_'|| 'OVERRIDE_ZONE_TYPE_' ||
231 substrb(to_char(l_location_structure_id_tbl(i)), 1, 6),1,30);
235
232 l_geo_rel_type_rec.status := 'A';
233 l_geo_rel_type_rec.created_by_module := 'EBTAX_MIGRATION';
234 l_geo_rel_type_rec.application_id := 235;
236
237 HZ_GEOGRAPHY_STRUCTURE_PUB.create_geo_rel_type(
238 p_init_msg_list => 'F',
239 p_geo_rel_type_rec => l_geo_rel_type_rec,
240 x_relationship_type_id => x_relationship_type_id,
241 x_return_status => x_return_status,
242 x_msg_count => x_msg_count,
243 x_msg_data => x_msg_data
244 );
245 END IF;
246 l_prev_country_code := l_country_code_tbl(i);
247
248
249
250 END IF;
251 END LOOP;
252 END IF;
253 END;
254
255 END;