1 PACKAGE BODY HZ_GEO_STRUCT_MAP_PUB AS
2 /* $Header: ARHGNRMB.pls 120.17 2006/04/11 00:28:16 nsinghai noship $ */
3
4 TYPE l_geo_struct_map_dtl_rec_type IS RECORD
5 (loc_seq_num NUMBER,
6 loc_comp VARCHAR2(30),
7 geo_type VARCHAR2(30),
8 geo_element_col VARCHAR2(30)
9 );
10
11 TYPE l_geo_struct_map_dtl_tbl_type IS TABLE of l_geo_struct_map_dtl_rec_type INDEX BY BINARY_INTEGER;
12
13 PROCEDURE validate_address_context(
14 p_location_table_name IN VARCHAR2,
15 p_context IN VARCHAR2,
16 p_territory_code IN VARCHAR2,
17 x_ret_status OUT NOCOPY VARCHAR2
18 );
19
20 PROCEDURE check_valid_loc_comp(
21 p_loc_comp IN VARCHAR2,
22 p_location_table_name IN VARCHAR2,
23 x_ret_status OUT NOCOPY VARCHAR2,
24 x_error_code OUT NOCOPY VARCHAR2
25 );
26
27 PROCEDURE check_valid_geo_type(
28 p_geo_type IN VARCHAR2,
29 x_ret_status OUT NOCOPY VARCHAR2,
30 x_error_code OUT NOCOPY VARCHAR2
31 );
32
33 PROCEDURE find_geo_element_col(
34 p_geography_type IN VARCHAR2,
35 p_parent_geography_type IN VARCHAR2,
36 p_country IN VARCHAR2,
37 p_geo_element_col OUT NOCOPY VARCHAR2,
38 x_ret_status OUT NOCOPY VARCHAR2
39 );
40
41 PROCEDURE validate_address_context(p_location_table_name IN VARCHAR2,
42 p_context IN VARCHAR2,
43 p_territory_code IN VARCHAR2,
44 x_ret_status OUT NOCOPY VARCHAR2
45 ) IS
46 l_descriptive_flexfield_name fnd_descr_flex_contexts_vl.descriptive_flexfield_name%TYPE;
47 l_context fnd_descr_flex_contexts_vl.descriptive_flex_context_code%TYPE;
48 l_application_id NUMBER;
49 BEGIN
50 IF UPPER(p_location_table_name) = 'HR_LOCATIONS_ALL' THEN
51 l_descriptive_flexfield_name := 'Address Location';
52 l_application_id := 800;
53 -- Removed PO_VENDOR_SITES_ALL from the below if condition. Bug # 4584465
54 --ELSIF UPPER(p_location_table_name) = 'PO_VENDOR_SITES_ALL' THEN
55 -- l_descriptive_flexfield_name := 'Site Address';
56 -- l_application_id := 200;
57 ELSIF UPPER(p_location_table_name) = 'HZ_LOCATIONS' THEN
58 l_descriptive_flexfield_name := 'Remit Address HZ';
59 l_application_id := 222;
60 END IF;
61
62 BEGIN
63 SELECT address_style
64 INTO l_context
65 FROM fnd_territories
66 WHERE territory_code = p_territory_code
67 AND l_application_id = 222
68 AND address_style = p_context ;
69
70 EXCEPTION WHEN NO_DATA_FOUND THEN
71
72 BEGIN
73 SELECT descriptive_flex_context_code
74 INTO l_context
75 FROM fnd_descr_flex_contexts_vl
76 WHERE application_id = l_application_id
77 AND descriptive_flexfield_name = l_descriptive_flexfield_name
78 AND descriptive_flex_context_code = p_context;
79
80 EXCEPTION WHEN NO_DATA_FOUND THEN
81 x_ret_status := FND_API.G_RET_STS_ERROR;
82 FND_MESSAGE.SET_NAME('AR', 'HZ_GEO_ADDRESS_STYLE_INVALID');
83 FND_MSG_PUB.ADD;
84 RAISE FND_API.G_EXC_ERROR;
85 END;
86 END;
87
88 END validate_address_context;
89
90 PROCEDURE check_valid_loc_comp(p_loc_comp IN VARCHAR2,
91 p_location_table_name IN VARCHAR2,
92 x_ret_status OUT NOCOPY VARCHAR2,
93 x_error_code OUT NOCOPY VARCHAR2) IS
94 l_exists VARCHAR2(6);
95 l_application_id NUMBER;
96 BEGIN
97
98 x_ret_status := FND_API.G_RET_STS_SUCCESS;
99
100 IF UPPER(p_location_table_name) = 'HR_LOCATIONS_ALL' THEN
101 l_application_id := 800;
102 ELSIF UPPER(p_location_table_name) = 'HZ_LOCATIONS' THEN
103 l_application_id := 222;
104 END IF;
105
106 SELECT 'Exists'
107 INTO l_exists
108 FROM fnd_columns col, fnd_tables tbl
109 WHERE tbl.table_id = col.table_id
110 AND tbl.application_id = col.application_id
111 AND tbl.application_id= l_application_id
112 AND col.column_name = p_loc_comp
113 AND tbl.table_name = p_location_table_name
114 AND col.column_name NOT IN ('LAST_UPDATED_BY', 'CREATION_DATE', 'CREATED_BY',
115 'LAST_UPDATE_DATE', 'LAST_UPDATE_LOGIN');
116
117 EXCEPTION WHEN NO_DATA_FOUND THEN
118 x_ret_status := FND_API.G_RET_STS_ERROR;
119 x_error_code := 'HZ_GEO_LOC_COMP_INVALID';
120
121 END;
122
123 PROCEDURE check_valid_geo_type(p_geo_type IN VARCHAR2,
124 x_ret_status OUT NOCOPY VARCHAR2,
125 x_error_code OUT NOCOPY VARCHAR2) IS
126 l_exists VARCHAR2(6);
127 BEGIN
128
129 x_ret_status := FND_API.G_RET_STS_SUCCESS;
130
131 SELECT 'Exists'
132 INTO l_exists
133 FROM hz_geography_types_b
134 WHERE geography_type = UPPER(p_geo_type);
135
136 EXCEPTION WHEN NO_DATA_FOUND THEN
137 x_ret_status := FND_API.G_RET_STS_ERROR;
138 x_error_code := 'HZ_GEO_GEO_TYPE_INVALID';
139
140 END;
141
142 PROCEDURE find_geo_element_col(p_geography_type IN VARCHAR2,
143 p_parent_geography_type IN VARCHAR2,
144 p_country IN VARCHAR2,
145 p_geo_element_col OUT NOCOPY VARCHAR2,
146 x_ret_status OUT NOCOPY VARCHAR2) IS
147 CURSOR determine_geo_element_col(p_parent_geo_type VARCHAR2,
148 p_country VARCHAR2) IS
149 SELECT geography_type, geography_element_column
150 FROM hz_geo_structure_levels
151 WHERE country_code = p_country
152 START WITH parent_geography_type = p_parent_geo_type
153 AND country_code = p_country
154 CONNECT BY PRIOR geography_type = parent_geography_type
155 AND country_code = p_country;
156 l_geo_type hz_geography_types_b.geography_type%TYPE;
157 l_geo_element_col hz_geo_structure_levels.geography_element_column%TYPE;
158
159 BEGIN
160
161 x_ret_status := FND_API.G_RET_STS_SUCCESS;
162
163 BEGIN
167 WHERE geography_type = p_geography_type
164 SELECT geography_element_column
165 INTO p_geo_element_col
166 FROM hz_geo_structure_levels
168 AND parent_geography_type = p_parent_geography_type
169 AND country_code = p_country;
170
171 EXCEPTION WHEN NO_DATA_FOUND THEN
172 p_geo_element_col := null;
173 END;
174 --
175 IF p_geo_element_col IS NULL THEN
176 OPEN determine_geo_element_col(p_parent_geography_type, p_country);
177 LOOP
178 FETCH determine_geo_element_col INTO l_geo_type, l_geo_element_col;
179 EXIT WHEN determine_geo_element_col%NOTFOUND;
180 IF l_geo_type = p_geography_type THEN
181 p_geo_element_col := l_geo_element_col;
182 EXIT;
183 END IF;
184 END LOOP;
185 CLOSE determine_geo_element_col;
186 IF p_geo_element_col IS NULL THEN
187 x_ret_status := FND_API.G_RET_STS_ERROR;
188 END IF;
189 END IF;
190
191 END find_geo_element_col;
192
193 PROCEDURE do_create_geo_struct_mapping
194 (p_geo_struct_map_rec IN geo_struct_map_rec_type,
195 p_geo_struct_map_dtl_tbl IN geo_struct_map_dtl_tbl_type,
196 x_map_id OUT NOCOPY NUMBER,
197 x_return_status OUT NOCOPY VARCHAR2) IS
198
199 i BINARY_INTEGER;
200 n BINARY_INTEGER;
201 m BINARY_INTEGER;
202 l_map_id NUMBER;
203 l_country VARCHAR2(2);
204 l_loc_seq_num NUMBER;
205 l_loc_comp VARCHAR2(30);
206 l_geo_type VARCHAR2(30);
207 l_parent_geo_type VARCHAR2(30);
208 l_error_code VARCHAR2(30);
209 l_geo_struct_map_dtl_tbl l_geo_struct_map_dtl_tbl_type ;
210 p_mltbl HZ_GNR_UTIL_PKG.maploc_rec_tbl_type;
211 x_map_row_id VARCHAR2(50);
212 x_map_dtl_row_id VARCHAR2(50);
213 l_token_name VARCHAR2(30);
214 l_token_value VARCHAR2(30);
215 l_temp VARCHAR2(100);
216
217 CURSOR c_determine_geo_element_col(c_geo_type VARCHAR2, c_country VARCHAR2) IS
218 SELECT level+1 seq_num
219 FROM hz_geo_structure_levels
220 WHERE geography_type = c_geo_type
221 START WITH parent_geography_type = 'COUNTRY'
222 AND country_code = c_country
223 CONNECT BY PRIOR geography_type = parent_geography_type
224 AND country_code = c_country;
225
226 BEGIN
227
228 x_return_status := FND_API.G_RET_STS_SUCCESS;
229 l_error_code := NULL;
230
231 -- Location table name is mandatory
232
233 IF p_geo_struct_map_rec.loc_tbl_name IS NULL THEN
234 x_return_status := FND_API.G_RET_STS_ERROR;
235 FND_MESSAGE.SET_NAME('AR', 'HZ_GEO_LOC_TABLE_MAND');
236 FND_MSG_PUB.ADD;
237 RAISE FND_API.G_EXC_ERROR;
238 END IF;
239
240 -- Country code is mandatory
241
242 IF p_geo_struct_map_rec.country_code IS NULL THEN
243 x_return_status := FND_API.G_RET_STS_ERROR;
244 FND_MESSAGE.SET_NAME('AR', 'HZ_GEO_COUNTRY_MAND');
245 FND_MSG_PUB.ADD;
246 RAISE FND_API.G_EXC_ERROR;
247 END IF;
248
249 -- location table name should be one of
250 -- PO_VENDOR_SITES_ALL, HR_LOCATIONS_ALL, HZ_LOCATIONS
251 -- Removed PO_VENDOR_SITES_ALL from the below if condition. Bug # 4584465
252
253 IF UPPER(p_geo_struct_map_rec.loc_tbl_name) NOT IN ('HR_LOCATIONS_ALL',
254 'HZ_LOCATIONS') THEN
255 x_return_status := FND_API.G_RET_STS_ERROR;
256 FND_MESSAGE.SET_NAME('AR', 'HZ_GEO_LOC_TABLE_INVALID');
257 FND_MSG_PUB.ADD;
258 RAISE FND_API.G_EXC_ERROR;
259 END IF;
260
261 -- country should exist in fnd_territories
262
263 BEGIN
264 SELECT territory_code
265 INTO l_country
266 FROM fnd_territories
267 WHERE territory_code = UPPER(p_geo_struct_map_rec.country_code);
268
269 EXCEPTION WHEN NO_DATA_FOUND THEN
270 x_return_status := FND_API.G_RET_STS_ERROR;
271 FND_MESSAGE.SET_NAME('AR', 'HZ_GEO_COUNTRY_INVALID');
272 FND_MSG_PUB.ADD;
273 RAISE FND_API.G_EXC_ERROR;
274
275 END;
276
277 -- Address_style is nullable
278 -- If not null then should exist as a context defined under the respective product's
279 -- address flexfield
280
281 IF p_geo_struct_map_rec.address_style IS NULL THEN
282 NULL;
283 ELSE
284 validate_address_context(UPPER(p_geo_struct_map_rec.loc_tbl_name),
285 UPPER(p_geo_struct_map_rec.address_style),
286 UPPER(p_geo_struct_map_rec.country_code),
287 x_return_status);
288 END IF;
289
290 -- At least one row is mandatory in map details
291 -- Not more than 10 rows can be passed in map details
292
293 IF p_geo_struct_map_dtl_tbl.COUNT < 2 THEN
294 x_return_status := FND_API.G_RET_STS_ERROR;
295 FND_MESSAGE.SET_NAME('AR', 'HZ_GEO_2_MAP_DTL_MAND');
296 -- Please enter at least two location components.
297 FND_MSG_PUB.ADD;
298 RAISE FND_API.G_EXC_ERROR;
299
300 ELSIF p_geo_struct_map_dtl_tbl.COUNT > 10 THEN
301 x_return_status := FND_API.G_RET_STS_ERROR;
302 FND_MESSAGE.SET_NAME('AR', 'HZ_GEO_TOO_MANY_MAP_DTLS');
303 FND_MSG_PUB.ADD;
304 RAISE FND_API.G_EXC_ERROR;
305
306 END IF;
307
308 -- sort the map details table into a temporary
309 -- table which will be indexed by loc_seq_num
310 -- Before sorting validate that loc seq num, loc comp,
311 -- geo type is mandatory in each row.
312 -- If not null then check that loc comp and geo type are
313 -- valid. If the sorted table has fewer number of rows than
314 -- what is passed, then it means that loc_seq_num was duplicate.
315
316 BEGIN
317
318 i := p_geo_struct_map_dtl_tbl.first;
319 WHILE i IS NOT NULL LOOP
320
321 -- Modified the below code to derive loc_seq_num from hz_geo_structure_levels
322 -- The new loc_seq_num will override the passed loc_seq_num value.
323 l_loc_seq_num := NULL;
324 IF p_geo_struct_map_dtl_tbl(i).geo_type = 'COUNTRY' then
325 l_loc_seq_num := 1;
326 ELSE
327 OPEN c_determine_geo_element_col(p_geo_struct_map_dtl_tbl(i).geo_type, p_geo_struct_map_rec.country_code);
328 FETCH c_determine_geo_element_col INTO l_loc_seq_num;
329 CLOSE c_determine_geo_element_col;
330 END IF;
331
332 IF l_loc_seq_num IS NULL THEN
333 x_return_status := FND_API.G_RET_STS_ERROR;
334 l_error_code := 'HZ_GEO_EL_COL_NOT_FOUND';
335 l_token_name := 'GEOTYPE';
336 l_token_value := p_geo_struct_map_dtl_tbl(i).geo_type;
337 EXIT;
338 END IF;
339 IF p_geo_struct_map_dtl_tbl(i).loc_comp IS NULL THEN
340 x_return_status := FND_API.G_RET_STS_ERROR;
341 l_error_code := 'HZ_GEO_LOC_COMP_MAND';
342 EXIT;
343 ELSE
344 check_valid_loc_comp(p_geo_struct_map_dtl_tbl(i).loc_comp, p_geo_struct_map_rec.loc_tbl_name,
345 x_return_status, l_error_code);
346 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
347 l_token_name := 'LOCCOMP';
348 l_token_value := p_geo_struct_map_dtl_tbl(i).loc_comp;
349 EXIT;
350 END IF;
351 END IF;
352 IF p_geo_struct_map_dtl_tbl(i).geo_type IS NULL THEN
353 x_return_status := FND_API.G_RET_STS_ERROR;
354 l_error_code := 'HZ_GEO_GEO_TYPE_MAND';
355 EXIT;
356 ELSE
357 check_valid_geo_type(p_geo_struct_map_dtl_tbl(i).geo_type, x_return_status, l_error_code);
358 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
359 l_token_name := 'GEOTYPE';
360 l_token_value := p_geo_struct_map_dtl_tbl(i).geo_type;
361 EXIT;
362 END IF;
363 END IF;
364 l_geo_struct_map_dtl_tbl(l_loc_seq_num).loc_seq_num := l_loc_seq_num;
365 l_geo_struct_map_dtl_tbl(l_loc_seq_num).loc_comp := p_geo_struct_map_dtl_tbl(i).loc_comp;
366 l_geo_struct_map_dtl_tbl(l_loc_seq_num).geo_type := p_geo_struct_map_dtl_tbl(i).geo_type;
367 i := p_geo_struct_map_dtl_tbl.next(i);
368 END LOOP;
369
370 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
371 FND_MESSAGE.SET_NAME('AR', l_error_code);
372 IF l_token_value IS NOT NULL THEN
373 FND_MESSAGE.SET_TOKEN(l_token_name, l_token_value);
374 END IF;
375 FND_MSG_PUB.ADD;
376 RAISE FND_API.G_EXC_ERROR;
377 END IF;
378 END;
379
380 BEGIN
381 IF p_geo_struct_map_dtl_tbl.count <> l_geo_struct_map_dtl_tbl.count THEN
382 x_return_status := FND_API.G_RET_STS_ERROR;
383 FND_MESSAGE.SET_NAME('AR', 'HZ_GEO_DUP_LOC_SEQ_NUM');
384 FND_MSG_PUB.ADD;
385 RAISE FND_API.G_EXC_ERROR;
386 END IF;
387
388 END;
389
390 -- Validate that the first geo_type is COUNTRY in the sorted table
391 -- Assign geography_element1 to COUNTRY
392 -- Find geography_element column for the others
393 BEGIN
394 i := l_geo_struct_map_dtl_tbl.first;
395 IF l_geo_struct_map_dtl_tbl(i).geo_type <> 'COUNTRY' THEN
396 x_return_status := FND_API.G_RET_STS_ERROR;
397 FND_MESSAGE.SET_NAME('AR', 'HZ_GEO_GEO_TYPE_NOT_COUNTRY');
398 FND_MSG_PUB.ADD;
399 RAISE FND_API.G_EXC_ERROR;
400 ELSE
401 l_geo_struct_map_dtl_tbl(i).geo_element_col := 'GEOGRAPHY_ELEMENT1';
402 END IF;
403 i := l_geo_struct_map_dtl_tbl.next(i);
404
405 WHILE i IS NOT NULL LOOP
406 l_geo_type := l_geo_struct_map_dtl_tbl(i).geo_type;
407 n := l_geo_struct_map_dtl_tbl.PRIOR(i);
408 l_parent_geo_type := l_geo_struct_map_dtl_tbl(n).geo_type;
409 find_geo_element_col(l_geo_type,
410 l_parent_geo_type,
411 p_geo_struct_map_rec.country_code,
412 l_geo_struct_map_dtl_tbl(i).geo_element_col,
413 x_return_status);
414 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
415 EXIT;
416 END IF;
417 i := l_geo_struct_map_dtl_tbl.next(i);
418 END LOOP;
419
420 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
421 FND_MESSAGE.SET_NAME('AR', 'HZ_GEO_EL_COL_NOT_FOUND');
422 FND_MESSAGE.SET_TOKEN('GEOTYPE',l_geo_struct_map_dtl_tbl(i).geo_type);
423 FND_MSG_PUB.ADD;
424 RAISE FND_API.G_EXC_ERROR;
425 END IF;
426 END;
427
428 -- Call table handler to insert row and make a
429 -- call to create map packages
430
431 BEGIN --insert row
432 SELECT HZ_GEO_STRUCT_MAP_S.nextval
433 INTO l_map_id
434 FROM dual;
435 hz_geo_struct_map_pvt.insert_row(
436 x_map_row_id,
437 l_map_id,
438 p_geo_struct_map_rec.country_code,
439 p_geo_struct_map_rec.loc_tbl_name,
440 p_geo_struct_map_rec.address_style);
441 END;
442
443 BEGIN
444 i := l_geo_struct_map_dtl_tbl.first;
445 m := 0;
446 WHILE i IS NOT NULL LOOP
447 --
448 m := m + 1;
449 p_mltbl(m).loc_seq_num := l_geo_struct_map_dtl_tbl(i).loc_seq_num;
450 p_mltbl(m).loc_component := l_geo_struct_map_dtl_tbl(i).loc_comp;
451 p_mltbl(m).geography_type := l_geo_struct_map_dtl_tbl(i).geo_type;
452 p_mltbl(m).geo_element_col := l_geo_struct_map_dtl_tbl(i).geo_element_col;
453 p_mltbl(m).loc_compval := null;
454 p_mltbl(m).geography_id := null;
455 --
456 hz_geo_struct_map_dtl_pvt.insert_row(
457 x_map_dtl_row_id,
458 l_map_id,
459 l_geo_struct_map_dtl_tbl(i).loc_seq_num,
460 l_geo_struct_map_dtl_tbl(i).loc_comp,
461 l_geo_struct_map_dtl_tbl(i).geo_type,
462 l_geo_struct_map_dtl_tbl(i).geo_element_col);
463 i := l_geo_struct_map_dtl_tbl.next(i);
464 END LOOP;
465 END; -- insert row
466
467 x_map_id := l_map_id;
468
469 END do_create_geo_struct_mapping;
470
471 PROCEDURE create_geo_struct_mapping(
472 p_geo_struct_map_rec IN geo_struct_map_rec_type,
473 p_geo_struct_map_dtl_tbl IN geo_struct_map_dtl_tbl_type,
474 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
475 x_map_id OUT NOCOPY NUMBER,
476 x_return_status OUT NOCOPY VARCHAR2,
477 x_msg_count OUT NOCOPY NUMBER,
478 x_msg_data OUT NOCOPY VARCHAR2) IS
479 p_index_name VARCHAR2(30);
480 l_temp VARCHAR2(1000);
481 BEGIN
482
483 -- Standard start of API savepoint
484 SAVEPOINT create_geo_struct_map;
485
486 -- Initialize message list if p_init_msg_list is set to TRUE.
487 IF FND_API.to_Boolean(p_init_msg_list) THEN
488 FND_MSG_PUB.initialize;
489 END IF;
490
491 -- Initialize API return status to success.
492 x_return_status := FND_API.G_RET_STS_SUCCESS;
493
494 -- Call to business logic.
495 do_create_geo_struct_mapping(
496 p_geo_struct_map_rec,
497 p_geo_struct_map_dtl_tbl,
498 x_map_id,
499 x_return_status
500 );
501
502 -- if validation failed at any point, then raise an exception to stop processing
503 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
504 RAISE FND_API.G_EXC_ERROR;
505 END IF;
506
507 -- Standard call to get message count and if count is 1, get message info.
508 FND_MSG_PUB.Count_And_Get(
509 p_encoded => FND_API.G_FALSE,
510 p_count => x_msg_count,
511 p_data => x_msg_data);
512
513 EXCEPTION
514 WHEN FND_API.G_EXC_ERROR THEN
515 ROLLBACK TO create_geo_struct_map;
516 x_return_status := FND_API.G_RET_STS_ERROR;
517 FND_MSG_PUB.Count_And_Get(
518 p_encoded => FND_API.G_FALSE,
519 p_count => x_msg_count,
520 p_data => x_msg_data);
521 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
522 ROLLBACK TO create_geo_struct_map;
523 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
524 FND_MSG_PUB.Count_And_Get(
525 p_encoded => FND_API.G_FALSE,
526 p_count => x_msg_count,
527 p_data => x_msg_data);
528
529 WHEN DUP_VAL_ON_INDEX THEN
530 ROLLBACK TO create_geo_struct_map;
531 x_return_status := FND_API.G_RET_STS_ERROR;
532 HZ_UTILITY_V2PUB.find_index_name(p_index_name);
533 IF p_index_name = 'HZ_GEO_STRUCT_MAP_U1' THEN
534 FND_MESSAGE.SET_NAME('AR', 'HZ_GEO_DUP_MAP_ID');
535 FND_MSG_PUB.ADD;
536
537 ELSIF p_index_name = 'HZ_GEO_STRUCT_MAP_U2' THEN
538 FND_MESSAGE.SET_NAME('AR', 'HZ_GEO_MAP_ROW_EXISTS');
539 FND_MESSAGE.SET_TOKEN('TABLENAME', p_geo_struct_map_rec.loc_tbl_name);
540 FND_MESSAGE.SET_TOKEN('COUNTRY', p_geo_struct_map_rec.country_code);
541 FND_MESSAGE.SET_TOKEN('ADDRSTYLE', p_geo_struct_map_rec.address_style);
542 FND_MSG_PUB.ADD;
543
544 ELSIF p_index_name = 'HZ_GEO_STRUCT_MAP_DTL_U1' THEN
545 FND_MESSAGE.SET_NAME('AR', 'HZ_GEO_DUP_LOC_SEQ_NUM');
546 FND_MSG_PUB.ADD;
547
548 ELSIF p_index_name = 'HZ_GEO_STRUCT_MAP_DTL_U2' THEN
549 FND_MESSAGE.SET_NAME('AR', 'HZ_GEO_DUP_LOC_COMP');
550 FND_MSG_PUB.ADD;
551
552 ELSIF p_index_name = 'HZ_GEO_STRUCT_MAP_DTL_U3' THEN
553 FND_MESSAGE.SET_NAME('AR', 'HZ_GEO_DUP_GEO_TYPE');
554 FND_MSG_PUB.ADD;
555 END IF;
556 FND_MSG_PUB.Count_And_Get(
557 p_encoded => FND_API.G_FALSE,
558 p_count => x_msg_count,
559 p_data => x_msg_data);
560 WHEN OTHERS THEN
561 ROLLBACK TO create_geo_struct_map;
562 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
563 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
564 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
565 FND_MSG_PUB.ADD;
566 FND_MSG_PUB.Count_And_Get(
567 p_encoded => FND_API.G_FALSE,
568 p_count => x_msg_count,
569 p_data => x_msg_data);
570
571 END create_geo_struct_mapping;
572
573 PROCEDURE delete_geo_struct_mapping(
574 p_map_id IN NUMBER,
575 p_location_table_name IN VARCHAR2,
576 p_country IN VARCHAR2,
577 p_address_style IN VARCHAR2,
578 p_geo_struct_map_dtl_tbl IN geo_struct_map_dtl_tbl_type,
579 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
580 x_return_status OUT NOCOPY VARCHAR2,
581 x_msg_count OUT NOCOPY NUMBER,
582 x_msg_data OUT NOCOPY VARCHAR2
583 ) IS
584 l_map_id NUMBER;
585 l_usage_id NUMBER;
586 l_count NUMBER;
587 l_pkgname VARCHAR2(50);
588 l_status VARCHAR2(30);
589 -- pkg_name VARCHAR2(1000);
590 -- l_drp_sql VARCHAR2(1000);
591 l_address_usage_dtls_tbl HZ_ADDRESS_USAGES_PUB.address_usage_dtls_tbl_type;
592
593 CURSOR c_address_usages IS
594 SELECT usage_id
595 FROM hz_address_usages
596 WHERE map_id = p_map_id;
597
598 CURSOR c_address_usage_dtls(c_geography_type varchar2) IS
599 SELECT dtl.usage_id
600 FROM Hz_address_usages usg, Hz_address_usage_dtls dtl
601 WHERE usg.map_id = p_map_id
602 AND dtl.geography_type = c_geography_type
603 AND dtl.usage_id = usg.usage_id;
604
605 l_return_status VARCHAR2(1);
606 l_msg_count NUMBER;
607 l_msg_data VARCHAR2(2000);
608 i BINARY_INTEGER;
609 l_delete_mapping_also VARCHAR(1);
610 l_del_geo_type VARCHAR2(100);
611
612 BEGIN
613
614 -- delete row using country, address style, location table name
615 -- If above is not provided map_id should be provided, delete as
616 -- per map id.
617
618 x_return_status := FND_API.G_RET_STS_SUCCESS;
619 IF p_map_id IS NULL THEN
620 IF p_location_table_name IS NULL THEN
621 x_return_status := FND_API.G_RET_STS_ERROR;
622 FND_MESSAGE.SET_NAME('AR', 'HZ_GEO_LOC_TABLE_MAND');
623 FND_MSG_PUB.ADD;
624 RAISE FND_API.G_EXC_ERROR;
625 END IF;
626
627 IF p_country IS NULL THEN
628 x_return_status := FND_API.G_RET_STS_ERROR;
629 FND_MESSAGE.SET_NAME('AR', 'HZ_GEO_COUNTRY_MAND');
630 FND_MSG_PUB.ADD;
631 RAISE FND_API.G_EXC_ERROR;
632 END IF;
633
634 IF p_address_style IS NULL THEN
635 BEGIN
636 SELECT map_id
637 INTO l_map_id
638 FROM hz_geo_struct_map
639 WHERE country_code = p_country
640 AND loc_tbl_name = p_location_table_name
641 AND address_style IS NULL;
642 EXCEPTION WHEN NO_DATA_FOUND THEN
643 x_return_status := FND_API.G_RET_STS_ERROR;
644 FND_MESSAGE.SET_NAME('AR', 'HZ_GEO_NO_MAPPING_ROW');
645 FND_MSG_PUB.ADD;
646
647 END;
648 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
649 RAISE FND_API.G_EXC_ERROR;
650 END IF;
651 ELSE
652 BEGIN
653 SELECT map_id
654 INTO l_map_id
655 FROM hz_geo_struct_map
656 WHERE country_code = p_country
657 AND loc_tbl_name = p_location_table_name
658 AND address_style = p_address_style;
659 EXCEPTION WHEN NO_DATA_FOUND THEN
660 x_return_status := FND_API.G_RET_STS_ERROR;
661 FND_MESSAGE.SET_NAME('AR', 'HZ_GEO_NO_MAPPING_ROW');
662 FND_MSG_PUB.ADD;
663 END;
664 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
665 RAISE FND_API.G_EXC_ERROR;
666 END IF;
667
668 END IF;
669 ELSE
670 BEGIN
671 SELECT map_id
672 INTO l_map_id
673 FROM hz_geo_struct_map
674 WHERE map_id = p_map_id;
675
676 EXCEPTION WHEN NO_DATA_FOUND THEN
677 x_return_status := FND_API.G_RET_STS_ERROR;
678 FND_MESSAGE.SET_NAME('AR', 'HZ_GEO_NO_MAPPING_ROW');
679 FND_MSG_PUB.ADD;
680 END;
681
682 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
683 RAISE FND_API.G_EXC_ERROR;
684 END IF;
685
686 END IF;
687
688 IF p_geo_struct_map_dtl_tbl.COUNT > 0 THEN
689 i:= p_geo_struct_map_dtl_tbl.FIRST;
690 LOOP
691 BEGIN
692 SELECT count(*)
693 INTO l_count
694 FROM hz_geo_struct_map_dtl
695 WHERE map_id = p_map_id;
696
697 -- At least two location components is required for the mapping.
698 -- So, if the count is more than 2 we can delete the mapping details.
699 -- If count is 2 or less, we delete both mappings (Bug 5096570) Nishant 06-Apr-2006
700 IF l_count < 3 then
701
702 l_delete_mapping_also := 'N';
703
704 BEGIN
705 SELECT geography_type
706 INTO l_del_geo_type
707 FROM hz_geo_struct_map_dtl
708 WHERE map_id = p_map_id
709 AND geography_type <> p_geo_struct_map_dtl_tbl(i).geo_type;
710
711 l_delete_mapping_also := 'Y';
712 EXCEPTION WHEN NO_DATA_FOUND THEN
713 /*
714 x_return_status := FND_API.G_RET_STS_ERROR;
715 FND_MESSAGE.SET_NAME('AR', 'HZ_GEO_2_MAP_DTL_MAND');
716 -- At least two location components is required for the mapping
717 FND_MSG_PUB.ADD;
718 RAISE FND_API.G_EXC_ERROR;
719 */
720 -- only 1 mapping detail that will be deleted below, so just delete the
721 -- mapping itself.
722 l_delete_mapping_also := 'Y';
723 END;
724
725 END IF;
726
727 IF p_geo_struct_map_dtl_tbl(i).geo_type = 'COUNTRY' THEN
728 x_return_status := FND_API.G_RET_STS_ERROR;
729 FND_MESSAGE.SET_NAME('AR', 'HZ_GEO_GEO_TYPE_NOT_COUNTRY');
730 FND_MSG_PUB.ADD;
731 RAISE FND_API.G_EXC_ERROR;
732 END IF;
733 END;
734
735 -- If there is a record in the usage dtls table, detete that record before
736 -- deleting the mapping dtl record.
737 OPEN c_address_usage_dtls(p_geo_struct_map_dtl_tbl(i).geo_type);
738 LOOP
739 FETCH c_address_usage_dtls INTO l_usage_id;
740 EXIT WHEN c_address_usage_dtls%NOTFOUND;
741 l_address_usage_dtls_tbl(1).geography_type := p_geo_struct_map_dtl_tbl(i).geo_type;
742 HZ_ADDRESS_USAGES_PUB.delete_address_usages(
743 p_usage_id => l_usage_id,
744 p_address_usage_dtls_tbl => l_address_usage_dtls_tbl,
745 p_init_msg_list => 'F',
746 x_return_status => l_return_status,
747 x_msg_count => l_msg_count,
748 x_msg_data => l_msg_data);
749 IF NOT (l_return_status = fnd_api.g_ret_sts_success) THEN
750 FND_MESSAGE.SET_NAME('AR', 'HZ_GEO_ERR_IN_USAGE_DEL_API');
751 -- Error in Usage Delete API
752 FND_MSG_PUB.ADD;
753 RAISE FND_API.G_EXC_ERROR;
754 END IF;
755 END LOOP;
756 CLOSE c_address_usage_dtls;
757
758 hz_geo_struct_map_dtl_pvt.delete_row(l_map_id,p_geo_struct_map_dtl_tbl(i).geo_type);
759
760 -- If count is 2 or less, we delete both mapping details (Bug 5096570) Nishant 06-Apr-2006
761 IF (l_del_geo_type) IS NOT NULL THEN
762 -- This is the last mapping which cannot exist on its own
763 -- delete mapping detail for other remaining record
764 hz_geo_struct_map_dtl_pvt.delete_row(l_map_id,l_del_geo_type);
765 END IF;
766
767 IF (l_delete_mapping_also = 'Y') THEN
768 -- delete mapping record also
769 hz_geo_struct_map_pvt.delete_row(l_map_id);
770 i := p_geo_struct_map_dtl_tbl.LAST; -- make i = LAST so that it exits loop
771 END IF;
772
773 EXIT WHEN i = p_geo_struct_map_dtl_tbl.LAST;
774 i := p_geo_struct_map_dtl_tbl.NEXT(i);
775 END LOOP;
776
777 /* -- Commenting out regeneration call here because Usage details deletion
778 -- will regenerate the package
779
780 -- After deleting all the usage dtls and mapping dtls
781 -- call the genpkg to recreate gnr package.
782 --dbms_output.put_line('Before Gen pkg Create usage : map id :'|| p_address_usages_rec.map_id);
783 BEGIN
784 hz_gnr_gen_pkg.genpkg(p_map_id,l_pkgname,l_status);
785 IF l_status = FND_API.G_RET_STS_ERROR THEN
786 FND_MESSAGE.SET_NAME('AR', 'HZ_GEO_GNR_PKG_ERR');
787 FND_MSG_PUB.ADD;
788 RAISE FND_API.G_EXC_ERROR;
789 END IF;
790 EXCEPTION WHEN OTHERS THEN
791 FND_MESSAGE.SET_NAME('AR', 'HZ_GEO_GNR_INTERNAL_ERROR');
792 FND_MSG_PUB.ADD;
793 RAISE FND_API.G_EXC_ERROR;
794 END;
795 --dbms_output.put_line('After Gen pkg Create usage : map id :'|| p_address_usages_rec.map_id);
796 */
797
798 ELSE
799 FOR i IN c_address_usages
800 LOOP
801
802 SELECT count(*)
803 INTO l_count
804 FROM hz_address_usage_dtls
805 WHERE usage_id = i.usage_id;
806
807 -- If count is > 0 there is usage exists and we need to delete the usage records.
808 -- Else, there is no need to call the delete usage API
809 IF l_count > 0 then
810 HZ_ADDRESS_USAGES_PUB.delete_address_usages(
811 p_usage_id => i.usage_id,
812 p_address_usage_dtls_tbl => l_address_usage_dtls_tbl,
813 p_init_msg_list => 'F',
814 x_return_status => l_return_status,
815 x_msg_count => l_msg_count,
816 x_msg_data => l_msg_data);
817 IF NOT (l_return_status = fnd_api.g_ret_sts_success) THEN
821 RAISE FND_API.G_EXC_ERROR;
818 FND_MESSAGE.SET_NAME('AR', 'HZ_GEO_ERR_IN_USAGE_DEL_API');
819 -- Error in Usage Delete API
820 FND_MSG_PUB.ADD;
822 END IF;
823 END IF;
824
825 EXIT WHEN c_address_usages%NOTFOUND;
826 END LOOP;
827
828 SELECT count(*)
829 INTO l_count
830 FROM hz_geo_struct_map_dtl
831 WHERE map_id = l_map_id;
832
833 IF l_count > 0 THEN
834 hz_geo_struct_map_dtl_pvt.delete_row(l_map_id);
835 END IF;
836 hz_geo_struct_map_pvt.delete_row(l_map_id);
837 END IF;
838
839 -- Commented the below code, since it also called from delete usages API.
840 -- pkg_name := 'HZ_GNR_MAP' ||to_char(l_map_id);
841 -- l_drp_sql := 'Drop Package Body '|| pkg_name;
842
843 -- EXECUTE IMMEDIATE l_drp_sql;
844
845 -- l_drp_sql := 'Drop Package '|| pkg_name;
846
847 -- EXECUTE IMMEDIATE l_drp_sql;
848
849 EXCEPTION
850 WHEN FND_API.G_EXC_ERROR THEN
851 FND_MSG_PUB.Count_And_Get(
852 p_encoded => FND_API.G_FALSE,
853 p_count => x_msg_count,
854 p_data => x_msg_data);
855
856 WHEN OTHERS THEN
857 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
858 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
859 FND_MSG_PUB.ADD;
860 FND_MSG_PUB.Count_And_Get(
861 p_encoded => FND_API.G_FALSE,
862 p_count => x_msg_count,
863 p_data => x_msg_data);
864
865 END delete_geo_struct_mapping;
866
867 PROCEDURE create_geo_struct_map_dtls
868 (p_map_id IN NUMBER,
869 p_geo_struct_map_dtl_tbl IN geo_struct_map_dtl_tbl_type,
870 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
871 x_return_status OUT NOCOPY VARCHAR2,
872 x_msg_count OUT NOCOPY NUMBER,
873 x_msg_data OUT NOCOPY VARCHAR2
874 ) IS
875
876 i BINARY_INTEGER;
877 n BINARY_INTEGER;
878 l_country VARCHAR2(30);
879 l_loc_tbl_name VARCHAR2(30);
880 l_loc_seq_num NUMBER;
881 l_loc_comp VARCHAR2(30);
882 l_geo_type VARCHAR2(30);
883 l_geo_element_column VARCHAR2(30);
884 l_error_code VARCHAR2(30);
885 x_map_dtl_row_id VARCHAR2(50);
886 l_token_name VARCHAR2(30);
887 l_token_value VARCHAR2(30);
888 p_index_name VARCHAR2(30);
889 l_map_dtl_count NUMBER;
890 l_geo_struct_map_dtl_tbl l_geo_struct_map_dtl_tbl_type ;
891
892 CURSOR c_struct_map(c_map_id number) IS
893 SELECT country_code, loc_tbl_name
894 FROM hz_geo_struct_map
895 WHERE map_id = c_map_id;
896
897 CURSOR c_map_dtl_count(c_map_id number) IS
898 SELECT count(*)
899 FROM hz_geo_struct_map_dtl
900 WHERE map_id = c_map_id;
901
902 CURSOR c_determine_geo_element_col(c_geo_type VARCHAR2, c_country VARCHAR2) IS
903 SELECT level+1 seq_num, geography_element_column
904 FROM hz_geo_structure_levels
905 WHERE geography_type = c_geo_type
906 START WITH parent_geography_type = 'COUNTRY'
907 AND country_code = c_country
908 CONNECT BY PRIOR geography_type = parent_geography_type
909 AND country_code = c_country;
910
911 BEGIN
912
913 -- Standard start of API savepoint
914 SAVEPOINT create_geo_struct_map_dtls;
915
916 -- Initialize message list if p_init_msg_list is set to TRUE.
917 IF FND_API.to_Boolean(p_init_msg_list) THEN
918 FND_MSG_PUB.initialize;
919 END IF;
920
921 -- Initialize API return status to success.
922 x_return_status := FND_API.G_RET_STS_SUCCESS;
923
924 l_country := NULL;
925 l_loc_tbl_name := NULL;
926 OPEN c_struct_map(p_map_id);
927 FETCH c_struct_map INTO l_country, l_loc_tbl_name;
928 CLOSE c_struct_map;
929
930 IF (l_country IS NULL OR l_loc_tbl_name IS NULL) THEN
931 x_return_status := FND_API.G_RET_STS_ERROR;
932 FND_MESSAGE.SET_NAME('AR', 'HZ_GEO_INVALID_MAP_ID');
933 -- Please pass a valid map ID that is not NULL.
934 FND_MSG_PUB.ADD;
935 RAISE FND_API.G_EXC_ERROR;
936 END IF;
937
938 l_map_dtl_count := 0;
939 OPEN c_map_dtl_count(p_map_id);
940 FETCH c_map_dtl_count INTO l_map_dtl_count;
941 CLOSE c_map_dtl_count;
942
943 -- Not more than 10 rows can be created in map details
944 IF (p_geo_struct_map_dtl_tbl.COUNT+l_map_dtl_count) > 10 THEN
945 x_return_status := FND_API.G_RET_STS_ERROR;
946 FND_MESSAGE.SET_NAME('AR', 'HZ_GEO_TOO_MANY_MAP_DTLS');
947 FND_MSG_PUB.ADD;
948 RAISE FND_API.G_EXC_ERROR;
949 END IF;
950
951 -- sort the map details table into a temporary
952 -- table which will be indexed by loc_seq_num
953 -- Before sorting validate that loc seq num, loc comp,
954 -- geo type is mandatory in each row.
955 -- If not null then check that loc comp and geo type are
956 -- valid. If the sorted table has fewer number of rows than
957 -- what is passed, then it means that loc_seq_num was duplicate.
958
959 BEGIN
960
961 i := p_geo_struct_map_dtl_tbl.first;
962 WHILE i IS NOT NULL LOOP
963
964 -- Added the below code to derive loc_seq_num from hz_geo_structure_levels
965 -- The new loc_seq_num will override the passed loc_seq_num value.
966 l_loc_seq_num := NULL;
967 IF p_geo_struct_map_dtl_tbl(i).geo_type = 'COUNTRY' then
968 l_loc_seq_num := 1;
969 l_geo_element_column := 'GEOGRAPHY_ELEMENT1';
970 ELSE
971 OPEN c_determine_geo_element_col(p_geo_struct_map_dtl_tbl(i).geo_type, l_country);
972 FETCH c_determine_geo_element_col INTO l_loc_seq_num, l_geo_element_column;
973 CLOSE c_determine_geo_element_col;
974 END IF;
975
976 IF (l_loc_seq_num IS NULL OR l_geo_element_column IS NULL) THEN
977 x_return_status := FND_API.G_RET_STS_ERROR;
978 FND_MESSAGE.SET_NAME('AR', 'HZ_GEO_EL_COL_NOT_FOUND');
979 FND_MESSAGE.SET_TOKEN('GEOTYPE',p_geo_struct_map_dtl_tbl(i).geo_type);
980 FND_MSG_PUB.ADD;
981 RAISE FND_API.G_EXC_ERROR;
982 END IF;
983
984 IF p_geo_struct_map_dtl_tbl(i).loc_comp IS NULL THEN
985 x_return_status := FND_API.G_RET_STS_ERROR;
986 l_error_code := 'HZ_GEO_LOC_COMP_MAND';
987 EXIT;
988 ELSE
989 check_valid_loc_comp(p_geo_struct_map_dtl_tbl(i).loc_comp, l_loc_tbl_name,
990 x_return_status, l_error_code);
991 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
992 l_token_name := 'LOCCOMP';
993 l_token_value := p_geo_struct_map_dtl_tbl(i).loc_comp;
994 EXIT;
995 END IF;
996 END IF;
997
998 IF p_geo_struct_map_dtl_tbl(i).geo_type IS NULL THEN
999 x_return_status := FND_API.G_RET_STS_ERROR;
1000 l_error_code := 'HZ_GEO_GEO_TYPE_MAND';
1001 EXIT;
1002 ELSE
1003 check_valid_geo_type(p_geo_struct_map_dtl_tbl(i).geo_type, x_return_status, l_error_code);
1004 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1005 l_token_name := 'GEOTYPE';
1006 l_token_value := p_geo_struct_map_dtl_tbl(i).geo_type;
1007 EXIT;
1008 END IF;
1009 END IF;
1010
1011
1012 l_geo_struct_map_dtl_tbl(l_loc_seq_num).loc_seq_num := l_loc_seq_num;
1013 l_geo_struct_map_dtl_tbl(l_loc_seq_num).loc_comp := p_geo_struct_map_dtl_tbl(i).loc_comp;
1014 l_geo_struct_map_dtl_tbl(l_loc_seq_num).geo_type := p_geo_struct_map_dtl_tbl(i).geo_type;
1015 l_geo_struct_map_dtl_tbl(l_loc_seq_num).geo_element_col := l_geo_element_column;
1016 i := p_geo_struct_map_dtl_tbl.next(i);
1017
1018 END LOOP;
1019
1020 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1021 FND_MESSAGE.SET_NAME('AR', l_error_code);
1022 IF l_token_value IS NOT NULL THEN
1023 FND_MESSAGE.SET_TOKEN(l_token_name, l_token_value);
1024 END IF;
1025 FND_MSG_PUB.ADD;
1026 RAISE FND_API.G_EXC_ERROR;
1027 END IF;
1028 END;
1029
1030 -- Call table handler to insert row and make a
1031 -- call to create map packages
1032
1033 BEGIN --insert row
1034 i := l_geo_struct_map_dtl_tbl.first;
1035 WHILE i IS NOT NULL LOOP
1036
1037 hz_geo_struct_map_dtl_pvt.insert_row(
1038 x_map_dtl_row_id,
1039 p_map_id,
1040 l_geo_struct_map_dtl_tbl(i).loc_seq_num,
1041 l_geo_struct_map_dtl_tbl(i).loc_comp,
1042 l_geo_struct_map_dtl_tbl(i).geo_type,
1043 l_geo_struct_map_dtl_tbl(i).geo_element_col);
1044 i := l_geo_struct_map_dtl_tbl.next(i);
1045 END LOOP;
1046 END; -- insert row
1047
1048 -- if validation failed at any point, then raise an exception to stop processing
1049 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1050 RAISE FND_API.G_EXC_ERROR;
1051 END IF;
1052
1053 -- Standard call to get message count and if count is 1, get message info.
1054 FND_MSG_PUB.Count_And_Get(
1055 p_encoded => FND_API.G_FALSE,
1056 p_count => x_msg_count,
1057 p_data => x_msg_data);
1058
1059 EXCEPTION
1060 WHEN FND_API.G_EXC_ERROR THEN
1061 ROLLBACK TO create_geo_struct_map_dtls;
1062 x_return_status := FND_API.G_RET_STS_ERROR;
1063 FND_MSG_PUB.Count_And_Get(
1064 p_encoded => FND_API.G_FALSE,
1065 p_count => x_msg_count,
1066 p_data => x_msg_data);
1067 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1068 ROLLBACK TO create_geo_struct_map_dtls;
1069 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1070 FND_MSG_PUB.Count_And_Get(
1071 p_encoded => FND_API.G_FALSE,
1072 p_count => x_msg_count,
1073 p_data => x_msg_data);
1074 WHEN DUP_VAL_ON_INDEX THEN
1075 ROLLBACK TO create_geo_struct_map_dtls;
1076 x_return_status := FND_API.G_RET_STS_ERROR;
1077 HZ_UTILITY_V2PUB.find_index_name(p_index_name);
1078
1079 IF p_index_name = 'HZ_GEO_STRUCT_MAP_DTL_U1' THEN
1080 -- FND_MESSAGE.SET_NAME('AR', 'HZ_GEO_DUP_LOC_SEQ_NUM');
1081 -- Changed the error message from HZ_GEO_DUP_LOC_SEQ_NUM to HZ_GEO_DUP_GEO_TYPE
1082 -- In the new design we are overriding the LOC_SEQ_NUM passed by the user
1083 -- and deriving it based on GEO_TYPE. So, it is better to display GEO_TYPE
1084 -- unique index error message for LOC_SEQ_NUM also.
1085 FND_MESSAGE.SET_NAME('AR', 'HZ_GEO_DUP_GEO_TYPE');
1086 FND_MSG_PUB.ADD;
1087
1088 ELSIF p_index_name = 'HZ_GEO_STRUCT_MAP_DTL_U2' THEN
1089 FND_MESSAGE.SET_NAME('AR', 'HZ_GEO_DUP_LOC_COMP');
1090 FND_MSG_PUB.ADD;
1091
1092 ELSIF p_index_name = 'HZ_GEO_STRUCT_MAP_DTL_U3' THEN
1093 FND_MESSAGE.SET_NAME('AR', 'HZ_GEO_DUP_GEO_TYPE');
1094 FND_MSG_PUB.ADD;
1095 END IF;
1096
1097 FND_MSG_PUB.Count_And_Get(
1098 p_encoded => FND_API.G_FALSE,
1099 p_count => x_msg_count,
1100 p_data => x_msg_data);
1101 WHEN OTHERS THEN
1102 ROLLBACK TO create_geo_struct_map_dtls;
1103 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1104 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
1105 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
1106 FND_MSG_PUB.ADD;
1107 FND_MSG_PUB.Count_And_Get(
1108 p_encoded => FND_API.G_FALSE,
1109 p_count => x_msg_count,
1110 p_data => x_msg_data);
1111
1112 END create_geo_struct_map_dtls;
1113
1114 PROCEDURE update_geo_struct_map_dtls
1115 (p_map_id IN NUMBER,
1116 p_geo_struct_map_dtl_tbl IN geo_struct_map_dtl_tbl_type,
1117 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1118 x_return_status OUT NOCOPY VARCHAR2,
1119 x_msg_count OUT NOCOPY NUMBER,
1120 x_msg_data OUT NOCOPY VARCHAR2
1121 ) IS
1122
1123 p_index_name VARCHAR2(30);
1124 l_country VARCHAR2(30);
1125 l_loc_tbl_name VARCHAR2(30);
1126 l_loc_seq_num NUMBER;
1127 l_geo_type VARCHAR2(30);
1128 l_geo_element_column VARCHAR2(30);
1129 l_rowid VARCHAR2(50);
1130 l_pkgname VARCHAR2(50);
1131 l_status VARCHAR2(30);
1132 l_count NUMBER;
1133 l_error_code VARCHAR2(30);
1134 l_token_name VARCHAR2(30);
1135 l_token_value VARCHAR2(30);
1136 i BINARY_INTEGER;
1137
1138 CURSOR c_struct_map(c_map_id number) IS
1139 SELECT country_code, loc_tbl_name
1140 FROM hz_geo_struct_map
1141 WHERE map_id = c_map_id;
1142
1143 CURSOR c_struct_map_dtl(c_geography_type varchar2) IS
1144 SELECT rowid, loc_seq_num,geo_element_col
1145 FROM hz_geo_struct_map_dtl
1146 WHERE map_id = p_map_id
1147 AND geography_type = c_geography_type;
1148
1149 BEGIN
1150
1151 -- Standard start of API savepoint
1152 SAVEPOINT update_geo_struct_map_dtls;
1153
1154 -- Initialize message list if p_init_msg_list is set to TRUE.
1155 IF FND_API.to_Boolean(p_init_msg_list) THEN
1156 FND_MSG_PUB.initialize;
1157 END IF;
1158
1159 -- Initialize API return status to success.
1160 x_return_status := FND_API.G_RET_STS_SUCCESS;
1161
1162 l_country := NULL;
1163 l_loc_tbl_name := NULL;
1164 OPEN c_struct_map(p_map_id);
1165 FETCH c_struct_map INTO l_country, l_loc_tbl_name;
1166 CLOSE c_struct_map;
1167
1168 IF (l_country IS NULL OR l_loc_tbl_name IS NULL) THEN
1169 x_return_status := FND_API.G_RET_STS_ERROR;
1170 FND_MESSAGE.SET_NAME('AR', 'HZ_GEO_INVALID_MAP_ID');
1171 -- Please pass a valid map ID that is not NULL.
1172 FND_MSG_PUB.ADD;
1173 RAISE FND_API.G_EXC_ERROR;
1174 END IF;
1175
1176 IF p_geo_struct_map_dtl_tbl.COUNT > 0 THEN
1177 i := p_geo_struct_map_dtl_tbl.FIRST;
1178 LOOP
1179
1180 l_loc_seq_num := NULL;
1181 l_geo_element_column := NULL;
1182 OPEN c_struct_map_dtl(p_geo_struct_map_dtl_tbl(i).geo_type);
1183 FETCH c_struct_map_dtl INTO l_rowid, l_loc_seq_num, l_geo_element_column;
1184 CLOSE c_struct_map_dtl;
1185
1186 IF (l_loc_seq_num IS NULL OR l_geo_element_column IS NULL) THEN
1187 x_return_status := FND_API.G_RET_STS_ERROR;
1188 FND_MESSAGE.SET_NAME('AR', 'HZ_GEO_GEOTYPE_INVALID');
1189 -- A mapping does not exist for this geography type. Please map the geography type.
1190 FND_MSG_PUB.ADD;
1191 RAISE FND_API.G_EXC_ERROR;
1192 END IF;
1193
1194 IF p_geo_struct_map_dtl_tbl(i).loc_comp IS NULL THEN
1195 x_return_status := FND_API.G_RET_STS_ERROR;
1196 l_error_code := 'HZ_GEO_LOC_COMP_MAND';
1197 EXIT;
1198 ELSE
1199 check_valid_loc_comp(p_geo_struct_map_dtl_tbl(i).loc_comp, l_loc_tbl_name,
1203 l_token_value := p_geo_struct_map_dtl_tbl(i).loc_comp;
1200 x_return_status, l_error_code);
1201 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1202 l_token_name := 'LOCCOMP';
1204 EXIT;
1205 END IF;
1206 END IF;
1207
1208 hz_geo_struct_map_dtl_pvt.Update_Row (
1209 x_rowid => l_rowid,
1210 x_map_id => p_map_id,
1211 x_loc_seq_num => l_loc_seq_num,
1212 x_loc_component => p_geo_struct_map_dtl_tbl(i).loc_comp,
1213 x_geography_type => p_geo_struct_map_dtl_tbl(i).geo_type,
1214 x_geo_element_col => l_geo_element_column);
1215
1216 EXIT WHEN i = p_geo_struct_map_dtl_tbl.LAST;
1217 i := p_geo_struct_map_dtl_tbl.NEXT(i);
1218 END LOOP;
1219
1220 -- if validation failed at any point, then raise an exception to stop processing
1221 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1222 RAISE FND_API.G_EXC_ERROR;
1223 END IF;
1224
1225 SELECT count(*)
1226 INTO l_count
1227 FROM Hz_address_usages usg, Hz_address_usage_dtls dtl
1228 WHERE usg.map_id = p_map_id
1229 AND usg.status_flag = 'A'
1230 AND dtl.usage_id = usg.usage_id;
1231
1232 -- If count is 0, that means there is no active usage details for this map_id
1233 IF l_count > 0 THEN
1234 --dbms_output.put_line('Before Gen pkg Create usage : map id :'|| p_address_usages_rec.map_id);
1235 BEGIN
1236 hz_gnr_gen_pkg.genpkg(p_map_id,l_pkgname,l_status);
1237 IF l_status = FND_API.G_RET_STS_ERROR THEN
1238 FND_MESSAGE.SET_NAME('AR', 'HZ_GEO_GNR_PKG_ERR');
1239 FND_MSG_PUB.ADD;
1240 RAISE FND_API.G_EXC_ERROR;
1241 END IF;
1242 EXCEPTION WHEN OTHERS THEN
1243 FND_MESSAGE.SET_NAME('AR', 'HZ_GEO_GNR_INTERNAL_ERROR');
1244 FND_MSG_PUB.ADD;
1245 RAISE FND_API.G_EXC_ERROR;
1246 END;
1247 --dbms_output.put_line('After Gen pkg Create usage : map id :'|| p_address_usages_rec.map_id);
1248 END IF;
1249
1250 END IF;
1251
1252 -- Standard call to get message count and if count is 1, get message info.
1253 FND_MSG_PUB.Count_And_Get(
1254 p_encoded => FND_API.G_FALSE,
1255 p_count => x_msg_count,
1256 p_data => x_msg_data);
1257
1258 EXCEPTION
1259 WHEN FND_API.G_EXC_ERROR THEN
1260 ROLLBACK TO update_geo_struct_map_dtls;
1261 x_return_status := FND_API.G_RET_STS_ERROR;
1262 FND_MSG_PUB.Count_And_Get(
1263 p_encoded => FND_API.G_FALSE,
1264 p_count => x_msg_count,
1265 p_data => x_msg_data);
1266 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1267 ROLLBACK TO update_geo_struct_map_dtls;
1268 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1269 FND_MSG_PUB.Count_And_Get(
1270 p_encoded => FND_API.G_FALSE,
1271 p_count => x_msg_count,
1272 p_data => x_msg_data);
1273 WHEN DUP_VAL_ON_INDEX THEN
1274 ROLLBACK TO update_geo_struct_map_dtls;
1275 x_return_status := FND_API.G_RET_STS_ERROR;
1276 HZ_UTILITY_V2PUB.find_index_name(p_index_name);
1277
1278 IF p_index_name = 'HZ_GEO_STRUCT_MAP_DTL_U1' THEN
1279 FND_MESSAGE.SET_NAME('AR', 'HZ_GEO_DUP_LOC_SEQ_NUM');
1280 FND_MSG_PUB.ADD;
1281
1282 ELSIF p_index_name = 'HZ_GEO_STRUCT_MAP_DTL_U2' THEN
1283 FND_MESSAGE.SET_NAME('AR', 'HZ_GEO_DUP_LOC_COMP');
1284 FND_MSG_PUB.ADD;
1285
1286 ELSIF p_index_name = 'HZ_GEO_STRUCT_MAP_DTL_U3' THEN
1287 FND_MESSAGE.SET_NAME('AR', 'HZ_GEO_DUP_GEO_TYPE');
1288 FND_MSG_PUB.ADD;
1289 END IF;
1290
1291 FND_MSG_PUB.Count_And_Get(
1292 p_encoded => FND_API.G_FALSE,
1293 p_count => x_msg_count,
1294 p_data => x_msg_data);
1295 WHEN OTHERS THEN
1296 ROLLBACK TO update_geo_struct_map_dtls;
1297 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1298 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
1299 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
1300 FND_MSG_PUB.ADD;
1301 FND_MSG_PUB.Count_And_Get(
1302 p_encoded => FND_API.G_FALSE,
1303 p_count => x_msg_count,
1304 p_data => x_msg_data);
1305
1306 END update_geo_struct_map_dtls;
1307
1308 END;
[Home] [Help]