[Home] [Help]
PACKAGE BODY: APPS.JTF_LOC_PVT
Source
1 PACKAGE BODY JTF_Loc_PVT AS
2 /* $Header: jtfvhldb.pls 120.2 2005/08/18 22:55:07 stopiwal ship $ */
3
4
5 TYPE loc_area_rec_type IS RECORD(
6 location_area_id NUMBER,
7 location_area_code VARCHAR2(30),
8 location_type_code VARCHAR2(30),
9 parent_location_area_id NUMBER,
10 request_id NUMBER,
11 program_application_id NUMBER,
12 program_id NUMBER,
13 program_update_date DATE,
14 start_date_active DATE,
15 end_date_active DATE,
16 location_postal_code_id number
17 );
18
19 TYPE loc_hierarchy_rec_type IS RECORD(
20 location_hierarchy_id NUMBER,
21 location_type_code VARCHAR2(30),
22 area1_id NUMBER,
23 area1_code VARCHAR2(30),
24 area2_id NUMBER,
25 area2_code VARCHAR2(30),
26 country_id NUMBER,
27 country_code VARCHAR2(30),
28 country_region_id NUMBER,
29 country_region_code VARCHAR2(30),
30 state_id NUMBER,
31 state_code VARCHAR2(30),
32 state_region_id NUMBER,
33 state_region_code VARCHAR2(30),
34 city_id NUMBER,
35 city_code VARCHAR2(30),
36 postal_code_id NUMBER
37 );
38
39
40 -----------------------------------------------------------------------
41 -- FUNCTION
42 -- get_loc_hierarchy_id
43 -- PURPOSE
44 -- get hierarchy id if the area is existing in hierarchy table.
45 -- NOTES
46 -----------------------------------------------------------------------
47 FUNCTION get_loc_hierarchy_id(p_hier_rec IN loc_hierarchy_rec_type)
48 RETURN NUMBER
49 IS
50
51 l_hier_id NUMBER;
52
53 CURSOR c_hier IS
54 SELECT location_hierarchy_id
55 FROM jtf_loc_hierarchies_b
56 WHERE location_type_code = p_hier_rec.location_type_code
57 AND DECODE(p_hier_rec.location_type_code,
58 'AREA1', area1_id,
59 'AREA2', area2_id,
60 'COUNTRY', country_id,
61 'CREGION', country_region_id,
62 'STATE', state_id,
63 'SREGION', state_region_id,
64 'CITY', city_id,
65 'POSTAL_CODE', postal_code_id
66 ) =
67 DECODE(p_hier_rec.location_type_code,
68 'AREA1', p_hier_rec.area1_id,
69 'AREA2', p_hier_rec.area2_id,
70 'COUNTRY', p_hier_rec.country_id,
71 'CREGION', p_hier_rec.country_region_id,
72 'STATE', p_hier_rec.state_id,
73 'SREGION', p_hier_rec.state_region_id,
74 'CITY', p_hier_rec.city_id,
75 'POSTAL_CODE', p_hier_rec.postal_code_id
76 );
77
78 BEGIN
79
80 OPEN c_hier;
81 FETCH c_hier INTO l_hier_id;
82 CLOSE c_hier;
83
84 RETURN l_hier_id;
85
86 END;
87
88
89 -----------------------------------------------------------------------
90 -- PROCEDURE
91 -- load_hierarchy(
92 -- PURPOSE
93 -- load areas to hierarchy table
94 -- NOTES
95 -- Create or update depending on the id returned by the function get_loc_hierarchy_id.
96 -----------------------------------------------------------------------
97 PROCEDURE load_hierarchy(
98 p_area_rec IN loc_area_rec_type,
99 p_hier_rec IN loc_hierarchy_rec_type
100 )
101 IS
102
103 l_hier_id NUMBER;
104 l_count NUMBER;
105
106 CURSOR c_hier_seq IS
107 SELECT jtf_loc_hierarchies_b_s.NEXTVAL
108 FROM DUAL;
109
110 CURSOR c_hier_count IS
111 SELECT count(*)
112 FROM jtf_loc_hierarchies_b
113 WHERE location_hierarchy_id = l_hier_id;
114
115 BEGIN
116
117 l_hier_id := get_loc_hierarchy_id(p_hier_rec);
118
119 IF l_hier_id IS NOT NULL THEN
120 UPDATE jtf_loc_hierarchies_b
121 SET location_hierarchy_id = l_hier_id,
122 last_update_date = SYSDATE,
123 last_updated_by = FND_GLOBAL.user_id,
124 last_update_login = FND_GLOBAL.conc_login_id,
125 object_version_number = object_version_number + 1,
126 request_id = p_area_rec.request_id,
127 program_application_id = p_area_rec.program_application_id,
128 program_id = p_area_rec.program_id,
129 program_update_date = p_area_rec.program_update_date,
130 created_by_application_id = 530,
131 start_date_active = p_area_rec.start_date_active,
132 end_date_active = p_area_rec.end_date_active,
133 location_type_code = p_hier_rec.location_type_code,
134 area1_id = p_hier_rec.area1_id,
135 area1_code = p_hier_rec.area1_code,
136 area2_id = p_hier_rec.area2_id,
137 area2_code = p_hier_rec.area2_code,
138 country_id = p_hier_rec.country_id,
139 country_code = p_hier_rec.country_code,
140 country_region_id = p_hier_rec.country_region_id,
141 country_region_code = p_hier_rec.country_region_code,
142 state_id = p_hier_rec.state_id,
143 state_code = p_hier_rec.state_code,
144 state_region_id = p_hier_rec.state_region_id,
145 state_region_code = p_hier_rec.state_region_code,
146 city_id = p_hier_rec.city_id,
147 city_code = p_hier_rec.city_code,
148 postal_code_id = p_hier_rec.postal_code_id
149 WHERE location_hierarchy_id = l_hier_id;
150 ELSE
151 LOOP
152 OPEN c_hier_seq;
153 FETCH c_hier_seq INTO l_hier_id;
154 CLOSE c_hier_seq;
155
156 OPEN c_hier_count;
157 FETCH c_hier_count INTO l_count;
158 CLOSE c_hier_count;
159
160 EXIT WHEN l_count = 0;
161 END LOOP;
162
163 INSERT INTO jtf_loc_hierarchies_b(
164 location_hierarchy_id,
165 last_update_date,
166 last_updated_by,
167 creation_date,
168 created_by,
169 last_update_login,
170 object_version_number,
171 request_id,
172 program_application_id,
173 program_id,
174 program_update_date,
175 created_by_application_id,
176 start_date_active,
177 end_date_active,
178 location_type_code,
179 area1_id,
180 area1_code,
181 area2_id,
182 area2_code,
183 country_id,
184 country_code,
185 country_region_id,
186 country_region_code,
187 state_id,
188 state_code,
189 state_region_id,
190 state_region_code,
191 city_id,
192 city_code,
193 postal_code_id
194 )
195 VALUES(
196 l_hier_id,
197 SYSDATE,
198 FND_GLOBAL.user_id,
199 SYSDATE,
200 FND_GLOBAL.user_id,
201 FND_GLOBAL.conc_login_id,
202 1,
203 p_area_rec.request_id,
204 p_area_rec.program_application_id,
205 p_area_rec.program_id,
206 p_area_rec.program_update_date,
207 530,
208 p_area_rec.start_date_active,
209 p_area_rec.end_date_active,
210 p_hier_rec.location_type_code,
211 p_hier_rec.area1_id,
212 p_hier_rec.area1_code,
213 p_hier_rec.area2_id,
214 p_hier_rec.area2_code,
215 p_hier_rec.country_id,
216 p_hier_rec.country_code,
217 p_hier_rec.country_region_id,
218 p_hier_rec.country_region_code,
219 p_hier_rec.state_id,
220 p_hier_rec.state_code,
221 p_hier_rec.state_region_id,
222 p_hier_rec.state_region_code,
223 p_hier_rec.city_id,
224 p_hier_rec.city_code,
225 p_hier_rec.postal_code_id
226 );
227 END IF;
228 END;
229
230
231 -----------------------------------------------------------------------
232 -- PROCEDURE
233 -- load_loc_areas
234 -- PURPOSE
235 -- Construct the hierarchy record from location areas
236 -- NOTES
237 -----------------------------------------------------------------------
238 PROCEDURE load_loc_areas
239 IS
240
241 l_hier_rec loc_hierarchy_rec_type;
242 l_area_rec loc_area_rec_type;
243 l_area_id NUMBER;
244 l_area_code VARCHAR2(30);
245 l_type_code VARCHAR2(30);
246 l_parent_id NUMBER;
247 l_post_id NUMBER;
248
249 CURSOR c_loc_areas IS
250 SELECT area.location_area_id,
251 area.location_area_code,
252 area.location_type_code,
253 area.parent_location_area_id,
254 area.request_id,
255 area.program_application_id,
256 area.program_id,
257 area.program_update_date,
258 area.start_date_active,
259 area.end_date_active,
260 postal.location_postal_code_id
261 FROM jtf_loc_areas_vl area, jtf_loc_postal_codes postal
262 WHERE area.location_area_id = postal.location_area_id (+);
263
264 CURSOR c_parent_area IS
265 SELECT location_area_id,
266 location_area_code,
267 location_type_code,
268 parent_location_area_id
269 FROM jtf_loc_areas_vl
270 WHERE location_area_id = l_parent_id;
271
272 CURSOR c_postal_codes IS
273 SELECT location_postal_code_id
274 FROM jtf_loc_postal_codes
275 WHERE location_area_id = l_area_id;
276
277 BEGIN
278
279 FOR l_area_rec IN c_loc_areas LOOP
280 l_hier_rec := NULL;
281 l_area_id := l_area_rec.location_area_id;
282 l_area_code := l_area_rec.location_area_code;
283 l_type_code := l_area_rec.location_type_code;
284 l_parent_id := l_area_rec.parent_location_area_id;
285 l_post_id := l_area_rec.location_postal_code_id;
286
287 -- this loop will construct the hierarchy record for this area
288 LOOP
289 IF l_type_code = 'AREA1' THEN
290 l_hier_rec.area1_id := l_area_id;
291 l_hier_rec.area1_code := l_area_code;
292 ELSIF l_type_code = 'AREA2' THEN
293 l_hier_rec.area2_id := l_area_id;
294 l_hier_rec.area2_code := l_area_code;
295 ELSIF l_type_code = 'COUNTRY' THEN
296 l_hier_rec.country_id := l_area_id;
297 l_hier_rec.country_code := l_area_code;
298 ELSIF l_type_code = 'CREGION' THEN
299 l_hier_rec.country_region_id := l_area_id;
300 l_hier_rec.country_region_code := l_area_code;
301 ELSIF l_type_code = 'STATE' THEN
302 l_hier_rec.state_id := l_area_id;
303 l_hier_rec.state_code := l_area_code;
304 ELSIF l_type_code = 'SREGION' THEN
305 l_hier_rec.state_region_id := l_area_id;
306 l_hier_rec.state_region_code := l_area_code;
307 ELSIF l_type_code = 'CITY' THEN
308 l_hier_rec.city_id := l_area_id;
309 l_hier_rec.city_code := l_area_code;
310 ELSIF l_type_code = 'POSTAL_CODE' THEN
311 l_hier_rec.city_id := l_parent_id;
312 l_hier_rec.postal_code_id := l_post_id;
313
314 /*
315 l_hier_rec.location_type_code := 'POSTAL_CODE';
316 OPEN c_postal_codes;
317 LOOP
318 FETCH c_postal_codes INTO l_post_id;
319 EXIT WHEN c_postal_codes%NOTFOUND;
320 l_hier_rec.city_id := l_parent_id;
321 l_hier_rec.city_code := l_area_code;
322 l_hier_rec.postal_code_id := l_post_id;
323 --l_hier_rec.city_code := l_area_code;
324 load_hierarchy(l_area_rec, l_hier_rec);
325 END LOOP;
326 CLOSE c_postal_codes;
327 */
328 END IF;
329
330 EXIT WHEN l_parent_id IS NULL;
331
332 OPEN c_parent_area;
333 FETCH c_parent_area INTO l_area_id, l_area_code, l_type_code, l_parent_id;
334 IF c_parent_area%NOTFOUND THEN
335 CLOSE c_parent_area;
336 EXIT;
337 END IF;
338 CLOSE c_parent_area;
339 END LOOP;
340
341 -- insert or update jtf_loc_hierarchies_b table
342 l_hier_rec.location_type_code := l_area_rec.location_type_code;
343 load_hierarchy(l_area_rec, l_hier_rec);
344
345 -- if this area is a city, load all postal codes
346 /*
347 IF l_type_code = 'POSTAL_CODE' THEN
348 l_hier_rec.location_type_code := 'POSTAL_CODE';
349 OPEN c_postal_codes;
350 LOOP
351 FETCH c_postal_codes INTO l_post_id;
352 EXIT WHEN c_postal_codes%NOTFOUND;
353 l_hier_rec.postal_code_id := l_post_id;
354 load_hierarchy(l_area_rec, l_hier_rec);
355 END LOOP;
356 CLOSE c_postal_codes;
357 END IF;
358 */
359 END LOOP;
360 END;
361
362
363 -- Start of Comments
364 --
365 -- NAME
366 -- Load_Locations
367 --
368 -- PURPOSE
369 -- This procedure is created to as a concurrent program wrapper which
370 -- will call the Load_Loc_Areas and will return errors if any
371 --
372 -- NOTES
373 --
374 --
375 -- HISTORY
376 -- 05/03/1999 ptendulk created
377 -- End of Comments
378
379 PROCEDURE Load_Locations
380 (errbuf OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
381 retcode OUT NOCOPY /* file.sql.39 change */ NUMBER)
382 IS
383 BEGIN
384 -- Call the procedure to refresh the Market Segment
385
386 Load_Loc_Areas;
387
388 retcode :=0;
389
390 EXCEPTION
391 WHEN OTHERS THEN
392 retcode := 1 ;
393 END Load_Locations ;
394
395 END JTF_Loc_PVT;