1 PACKAGE BODY HZ_ADDRESS_USAGES_PUB AS
2 /* $Header: ARHGNRSB.pls 120.4 2006/04/11 00:27:07 nsinghai noship $ */
3
4 PROCEDURE do_create_address_usages(
5 p_address_usages_rec IN address_usages_rec_type,
6 p_address_usage_dtls_tbl IN address_usage_dtls_tbl_type,
7 x_usage_id OUT NOCOPY NUMBER,
8 x_return_status OUT NOCOPY VARCHAR2) IS
9
10 l_map_id NUMBER;
11 l_usage_id NUMBER;
12 l_usage_dtl_id NUMBER;
13 l_usage_row_id VARCHAR2(50);
14 x_usage_dtl_row_id VARCHAR2(50);
15 l_usage_code VARCHAR2(30);
16 l_geotype VARCHAR2(360);
17
18 i BINARY_INTEGER;
19 l_error_code VARCHAR2(30);
20 x_status VARCHAR2(30);
21 l_temp VARCHAR2(100);
22 l_map_dtl_extsts VARCHAR2(1);
23 l_usg_country_found VARCHAR2(1);
24 p_index_name VARCHAR2(30);
25
26 BEGIN
27
28 x_return_status := FND_API.G_RET_STS_SUCCESS;
29 l_error_code := NULL;
30
31 -- Standard start of API savepoint
32 SAVEPOINT do_create_address_usages;
33
34 -- Map Id is mandatory
35
36 IF p_address_usages_rec.map_id IS NULL THEN
37 x_return_status := FND_API.G_RET_STS_ERROR;
38 FND_MESSAGE.SET_NAME('AR', 'HZ_GEO_INVALID_MAP_ID');
39 -- The Map Id passed is NULL. Please use a valid Map Id to create a Address Usage.
40 FND_MSG_PUB.ADD;
41 RAISE FND_API.G_EXC_ERROR;
42 ELSE
43 BEGIN
44 SELECT map_id
45 INTO l_map_id
46 FROM hz_geo_struct_map
47 WHERE map_id = p_address_usages_rec.map_id;
48
49 EXCEPTION WHEN NO_DATA_FOUND THEN
50 x_return_status := FND_API.G_RET_STS_ERROR;
51 FND_MESSAGE.SET_NAME('AR', 'HZ_GEO_INVALID_MAP_ID');
52 -- The mapping record that you are trying to create address usage does not exist.
53 FND_MSG_PUB.ADD;
54 RAISE FND_API.G_EXC_ERROR;
55
56 END;
57 END IF;
58
59 -- Usage code is mandatory
60
61 IF p_address_usages_rec.usage_code IS NULL THEN
62 x_return_status := FND_API.G_RET_STS_ERROR;
63 FND_MESSAGE.SET_NAME('AR', 'HZ_GEO_INVALID_USAGE_CODE');
64 -- Please enter a valid usage.
65 FND_MSG_PUB.ADD;
66 RAISE FND_API.G_EXC_ERROR;
67 ELSE
68 BEGIN
69 SELECT lookup_code
70 INTO l_usage_code
71 FROM ar_lookups
72 WHERE lookup_type = 'HZ_GEOGRAPHY_USAGE'
73 AND lookup_code = p_address_usages_rec.usage_code;
74 EXCEPTION WHEN NO_DATA_FOUND THEN
75 x_return_status := FND_API.G_RET_STS_ERROR;
76 FND_MESSAGE.SET_NAME('AR', 'HZ_GEO_INVALID_USAGE_CODE');
77 -- The Usage Code is invalid. Please pass a valid Usage Code
78 FND_MSG_PUB.ADD;
79 RAISE FND_API.G_EXC_ERROR;
80 END;
81 END IF;
82
83 IF p_address_usage_dtls_tbl.COUNT < 2 THEN
84 x_return_status := FND_API.G_RET_STS_ERROR;
85 FND_MESSAGE.SET_NAME('AR', 'HZ_GEO_2_USAGE_DTL_MAND');
86 -- Please enter two or more geography types for a usage.
87 FND_MSG_PUB.ADD;
88 RAISE FND_API.G_EXC_ERROR;
89 END IF;
90
91 -- Validate that one of the geography_type passed is COUNTRY
92 i := p_address_usage_dtls_tbl.first;
93 LOOP
94 IF p_address_usage_dtls_tbl(i).geography_type <> 'COUNTRY' THEN
95 l_usg_country_found := 'N';
96 ELSE
97 l_usg_country_found := 'Y';
98 EXIT;
99 END IF;
100 EXIT WHEN i = p_address_usage_dtls_tbl.LAST;
101 i := p_address_usage_dtls_tbl.NEXT(i);
102 END LOOP;
103
104 IF l_usg_country_found <> 'Y' then
105 x_return_status := FND_API.G_RET_STS_ERROR;
106 FND_MESSAGE.SET_NAME('AR', 'HZ_GEO_GEO_TYPE_NOT_COUNTRY');
107 -- Please enter a geography type COUNTRY
108 FND_MSG_PUB.ADD;
109 RAISE FND_API.G_EXC_ERROR;
110 END IF;
111
112 -- Call table handler to insert row and make a
113 -- call to create map packages
114
115
116 BEGIN --insert row
117
118 SELECT HZ_ADDRESS_USAGES_S.nextval
119 INTO l_usage_id
120 FROM dual;
121
122 hz_address_usages_pkg.insert_row(
123 l_usage_row_id,
124 l_usage_id,
125 p_address_usages_rec.map_id,
126 p_address_usages_rec.usage_code,
127 p_address_usages_rec.status_flag,
128 1,
129 p_address_usages_rec.created_by_module,
130 p_address_usages_rec.application_id);
131 END;
132
133 --dbms_output.put_line(' message : Usage Created');
134
135 BEGIN
136 i := p_address_usage_dtls_tbl.first;
137 WHILE i IS NOT NULL LOOP
138
139 IF p_address_usage_dtls_tbl(i).geography_type IS NULL THEN
140 x_return_status := FND_API.G_RET_STS_ERROR;
141 FND_MESSAGE.SET_NAME('AR', 'HZ_GEO_GEOGRAPHY_TYPE_MAND');
142 -- Please enter a valid geography type
143 FND_MSG_PUB.ADD;
144 RAISE FND_API.G_EXC_ERROR;
145 ELSE
146 BEGIN
147 SELECT 'X'
148 INTO l_map_dtl_extsts
149 FROM hz_geo_struct_map_dtl
150 WHERE map_id = p_address_usages_rec.map_id
151 AND geography_type = p_address_usage_dtls_tbl(i).geography_type;
152
153 EXCEPTION WHEN NO_DATA_FOUND THEN
154 x_return_status := FND_API.G_RET_STS_ERROR;
155 FND_MESSAGE.SET_NAME('AR', 'HZ_GEO_GEOTYPE_INVALID');
156 -- Geography Type does not mapped with a location
157 FND_MSG_PUB.ADD;
158 RAISE FND_API.G_EXC_ERROR;
159
160 END;
161 END IF;
162
163 -- The below variable is to display the geography type in case of unique error from pkg API
164 l_geotype := p_address_usage_dtls_tbl(i).geography_type;
165
166 SELECT HZ_ADDRESS_USAGES_S.nextval
167 INTO l_usage_dtl_id
168 FROM dual;
169
170 hz_address_usage_dtls_pkg.insert_row(
171 x_usage_dtl_row_id,
172 l_usage_dtl_id,
173 l_usage_id,
174 p_address_usage_dtls_tbl(i).geography_type,
175 1,
176 p_address_usage_dtls_tbl(i).created_by_module,
177 p_address_usage_dtls_tbl(i).application_id);
178 i := p_address_usage_dtls_tbl.next(i);
179 END LOOP;
180 END; -- insert row
181
182 x_usage_id := l_usage_id;
183
184 EXCEPTION
185 WHEN DUP_VAL_ON_INDEX THEN
186 ROLLBACK TO do_create_address_usages;
187 x_return_status := FND_API.G_RET_STS_ERROR;
188 HZ_UTILITY_V2PUB.find_index_name(p_index_name);
189 IF p_index_name = 'HZ_ADDRESS_USAGES_U1' THEN
190 FND_MESSAGE.SET_NAME('AR', 'HZ_GEO_DUP_USAGE_ID');
191 FND_MESSAGE.SET_TOKEN('P_USAGE_ID',l_usage_id);
192 -- Usage ID already exists. Please use a unique ID
193 FND_MSG_PUB.ADD;
194
195 ELSIF p_index_name = 'HZ_ADDRESS_USAGES_U2' THEN
196 FND_MESSAGE.SET_NAME('AR', 'HZ_GEO_USAGE_ROW_EXISTS');
197 FND_MESSAGE.SET_TOKEN('P_USAGE',p_address_usages_rec.usage_code);
198 -- The mapping already exists for this usage. Please use another mapping.
199 FND_MSG_PUB.ADD;
200
201 ELSIF p_index_name = 'HZ_ADDRESS_USAGE_DTLS_U1' THEN
202 FND_MESSAGE.SET_NAME('AR', 'HZ_GEO_DUP_USAGE_DTL_ID');
203 FND_MESSAGE.SET_TOKEN('P_USAGE_DTL_ID',l_usage_dtl_id);
204 -- Usage detail ID already exists. Please use a unique ID.
205 FND_MSG_PUB.ADD;
206
207 ELSIF p_index_name = 'HZ_ADDRESS_USAGE_DTLS_U2' THEN
208 FND_MESSAGE.SET_NAME('AR', 'HZ_GEO_DUP_USAGE_GEOTYPE');
209 FND_MESSAGE.SET_TOKEN('P_GEOTYPE',l_geotype);
210 FND_MESSAGE.SET_TOKEN('P_USAGE',p_address_usages_rec.usage_code);
211 -- This geography type is already mapped for this usage.
212 FND_MSG_PUB.ADD;
213 END IF;
214
215 END do_create_address_usages;
216
217 PROCEDURE create_address_usages(
218 p_address_usages_rec IN address_usages_rec_type,
219 p_address_usage_dtls_tbl IN address_usage_dtls_tbl_type,
220 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
221 x_usage_id OUT NOCOPY NUMBER,
222 x_return_status OUT NOCOPY VARCHAR2,
223 x_msg_count OUT NOCOPY NUMBER,
224 x_msg_data OUT NOCOPY VARCHAR2) IS
225 p_index_name VARCHAR2(30);
226 l_temp VARCHAR2(1000);
227 l_pkgname VARCHAR2(50);
228 l_status VARCHAR2(30);
229
230 BEGIN
231
232 -- Standard start of API savepoint
233 SAVEPOINT create_address_usages;
234
235 -- Initialize message list if p_init_msg_list is set to TRUE.
236 IF FND_API.to_Boolean(p_init_msg_list) THEN
237 FND_MSG_PUB.initialize;
238 END IF;
239
240 -- Initialize API return status to success.
241 x_return_status := FND_API.G_RET_STS_SUCCESS;
242
243 -- Call to business logic.
244 do_create_address_usages(
245 p_address_usages_rec,
246 p_address_usage_dtls_tbl,
247 x_usage_id,
248 x_return_status
249 );
250
251
252 -- if validation failed at any point, then raise an exception to stop processing
253 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
254 RAISE FND_API.G_EXC_ERROR;
255 END IF;
256
257 IF p_address_usages_rec.status_flag = 'A' THEN
258 --dbms_output.put_line('Before Gen pkg Create usage : map id :'|| p_address_usages_rec.map_id);
259 BEGIN
260 hz_gnr_gen_pkg.genpkg(p_address_usages_rec.map_id,l_pkgname,l_status);
261 IF l_status = FND_API.G_RET_STS_ERROR THEN
262 FND_MESSAGE.SET_NAME('AR', 'HZ_GEO_GNR_PKG_ERR');
263 FND_MSG_PUB.ADD;
264 RAISE FND_API.G_EXC_ERROR;
265 END IF;
266 EXCEPTION WHEN OTHERS THEN
267 FND_MESSAGE.SET_NAME('AR', 'HZ_GEO_GNR_INTERNAL_ERROR');
268 FND_MSG_PUB.ADD;
269 RAISE FND_API.G_EXC_ERROR;
270 END;
271 --dbms_output.put_line('After Gen pkg Create usage : map id :'|| p_address_usages_rec.map_id);
272 END IF;
273
274 -- Standard call to get message count and if count is 1, get message info.
275 FND_MSG_PUB.Count_And_Get(
276 p_encoded => FND_API.G_FALSE,
277 p_count => x_msg_count,
278 p_data => x_msg_data);
279
280 EXCEPTION
281 WHEN FND_API.G_EXC_ERROR THEN
282 ROLLBACK TO create_address_usages;
283 x_return_status := FND_API.G_RET_STS_ERROR;
284 FND_MSG_PUB.Count_And_Get(
285 p_encoded => FND_API.G_FALSE,
286 p_count => x_msg_count,
287 p_data => x_msg_data);
288 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
289 ROLLBACK TO create_address_usages;
290 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
291 FND_MSG_PUB.Count_And_Get(
292 p_encoded => FND_API.G_FALSE,
293 p_count => x_msg_count,
294 p_data => x_msg_data);
295 WHEN OTHERS THEN
296 ROLLBACK TO create_address_usages;
297 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
298 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
299 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
300 FND_MSG_PUB.ADD;
301 FND_MSG_PUB.Count_And_Get(
302 p_encoded => FND_API.G_FALSE,
303 p_count => x_msg_count,
304 p_data => x_msg_data);
305
306 END create_address_usages;
307
308 PROCEDURE update_address_usages
309 (p_usage_id IN NUMBER,
310 p_map_id IN NUMBER,
311 p_usage_code IN VARCHAR2,
312 p_status_flag IN VARCHAR2,
313 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
314 x_object_version_number IN OUT NOCOPY NUMBER,
315 x_return_status OUT NOCOPY VARCHAR2,
316 x_msg_count OUT NOCOPY NUMBER,
317 x_msg_data OUT NOCOPY VARCHAR2
318 ) IS
319
320 l_map_id NUMBER;
321 l_usage_id NUMBER;
322 l_usage_row_id VARCHAR2(50);
323 l_object_version_number NUMBER;
324 l_pkgname VARCHAR2(50);
325 l_status VARCHAR2(30);
326 l_count NUMBER;
327 pkg_name VARCHAR2(1000);
328 l_drp_sql VARCHAR2(1000);
329
330 db_object_version_number hz_address_usages.object_version_number%TYPE;
331 db_map_id hz_address_usages.map_id%TYPE;
332 db_usage_code hz_address_usages.usage_code%TYPE;
333 db_status_flag hz_address_usages.status_flag%TYPE;
334 db_created_by hz_address_usages.created_by%TYPE;
335 db_creation_date hz_address_usages.creation_date%TYPE;
336 db_last_updated_by hz_address_usages.last_updated_by%TYPE;
337 db_last_update_date hz_address_usages.last_update_date%TYPE;
338 db_last_update_login hz_address_usages.last_update_login%TYPE;
339 db_created_by_module hz_address_usages.created_by_module%TYPE;
340 db_application_id hz_address_usages.application_id%TYPE;
341
342 BEGIN
343
344 -- Standard start of API savepoint
345 SAVEPOINT update_address_usages;
346
347 -- Initialize message list if p_init_msg_list is set to TRUE.
348 IF FND_API.to_Boolean(p_init_msg_list) THEN
349 FND_MSG_PUB.initialize;
350 END IF;
351
352 -- Initialize API return status to success.
353 x_return_status := FND_API.G_RET_STS_SUCCESS;
354
355 IF p_usage_id IS NOT NULL THEN
356
357 -- Check the passed usage_id is valid or not
358 BEGIN
359 SELECT rowid, usage_id, object_version_number, map_id,
360 usage_code, status_flag, created_by, creation_date, last_updated_by,
361 last_update_date, last_update_login, created_by_module, application_id
362 INTO l_usage_row_id, l_usage_id, db_object_version_number, db_map_id,
363 db_usage_code, db_status_flag, db_created_by, db_creation_date, db_last_updated_by,
364 db_last_update_date, db_last_update_login, db_created_by_module, db_application_id
365 FROM hz_address_usages
366 WHERE usage_id = p_usage_id;
367 EXCEPTION WHEN NO_DATA_FOUND THEN
368 x_return_status := FND_API.G_RET_STS_ERROR;
369 FND_MESSAGE.SET_NAME('AR', 'HZ_GEO_INVALID_USAGE_ID');
370 -- Usage record does not exists for updation
371 FND_MSG_PUB.ADD;
372 RAISE FND_API.G_EXC_ERROR;
373 END;
374 ELSE
375
376 IF p_map_id IS NULL THEN
377 x_return_status := FND_API.G_RET_STS_ERROR;
378 FND_MESSAGE.SET_NAME('AR', 'HZ_GEO_INVALID_MAP_ID');
379 -- The Map Id passed is NULL. Please use a valid Map Id to create an Address Usage.
380 FND_MSG_PUB.ADD;
381 RAISE FND_API.G_EXC_ERROR;
382 END IF;
383
384 IF p_usage_code IS NULL THEN
385 x_return_status := FND_API.G_RET_STS_ERROR;
386 FND_MESSAGE.SET_NAME('AR', 'HZ_GEO_INVALID_USAGE_CODE');
387 -- The usage code passed is NULL. Please use a valid usage code.
388 FND_MSG_PUB.ADD;
389 RAISE FND_API.G_EXC_ERROR;
390 END IF;
391
392 -- Check the valid combination for map_id and usage_code
393 BEGIN
394 SELECT rowid, usage_id, object_version_number, map_id,
395 usage_code, status_flag, created_by, creation_date, last_updated_by,
396 last_update_date, last_update_login, created_by_module, application_id
397 INTO l_usage_row_id, l_usage_id, db_object_version_number, db_map_id,
398 db_usage_code, db_status_flag, db_created_by, db_creation_date, db_last_updated_by,
399 db_last_update_date, db_last_update_login, db_created_by_module, db_application_id
400 FROM hz_address_usages
401 WHERE map_id = p_map_id
402 AND usage_code = p_usage_code;
403 EXCEPTION WHEN NO_DATA_FOUND THEN
404 x_return_status := FND_API.G_RET_STS_ERROR;
405 FND_MESSAGE.SET_NAME('AR', 'HZ_GEO_INVALID_USAGE_ID');
406 -- Usage record does not exists for updation
407 FND_MSG_PUB.ADD;
408 RAISE FND_API.G_EXC_ERROR;
409 END;
410
411 END IF;
412
413 IF db_object_version_number <> x_object_version_number THEN
414 FND_MESSAGE.SET_NAME('AR', 'HZ_API_RECORD_CHANGED');
415 FND_MESSAGE.SET_TOKEN('TABLE', 'HZ_ADDRESS_USAGES');
416 FND_MSG_PUB.ADD;
417 RAISE FND_API.G_EXC_ERROR;
418 END IF;
419
420 hz_address_usages_pkg.Lock_Row (
421 x_rowid => l_usage_row_id,
422 x_usage_id => l_usage_id,
423 x_map_id => db_map_id,
424 x_usage_code => db_usage_code,
425 x_status_flag => db_status_flag,
426 x_created_by => db_created_by,
427 x_creation_date => db_creation_date,
428 x_last_updated_by => db_last_updated_by,
429 x_last_update_date => db_last_update_date,
430 x_last_update_login => db_last_update_login,
431 x_object_version_number => x_object_version_number,
432 x_created_by_module => db_created_by_module,
433 x_application_id => db_application_id);
434
435 --dbms_output.put_line(' message update : l_usage_row_id '|| l_usage_row_id);
436 l_object_version_number := x_object_version_number +1;
437
438 hz_address_usages_pkg.Update_Row (
439 x_rowid => l_usage_row_id,
440 x_usage_id => l_usage_id,
441 x_map_id => db_map_id,
442 x_usage_code => db_usage_code,
443 x_status_flag => p_status_flag,
444 x_object_version_number => l_object_version_number,
445 x_created_by_module => db_created_by_module,
446 x_application_id => db_application_id);
447
448
449 --dbms_output.put_line(' message after update :');
450 x_object_version_number := l_object_version_number;
451
452 SELECT count(*)
453 INTO l_count
454 FROM Hz_address_usages usg, Hz_address_usage_dtls dtl
455 WHERE usg.map_id = db_map_id
456 AND usg.status_flag = 'A'
457 AND dtl.usage_id = usg.usage_id;
458
459 -- If count is 0, that means there is no active usage details for this map_id
460 IF l_count > 0 THEN
461 --dbms_output.put_line('Before Gen pkg Create usage : map id :'|| p_address_usages_rec.map_id);
462 BEGIN
463 hz_gnr_gen_pkg.genpkg(db_map_id,l_pkgname,l_status);
464 IF l_status = FND_API.G_RET_STS_ERROR THEN
465 FND_MESSAGE.SET_NAME('AR', 'HZ_GEO_GNR_PKG_ERR');
466 FND_MSG_PUB.ADD;
467 RAISE FND_API.G_EXC_ERROR;
468 END IF;
469 EXCEPTION WHEN OTHERS THEN
470 FND_MESSAGE.SET_NAME('AR', 'HZ_GEO_GNR_INTERNAL_ERROR');
471 FND_MSG_PUB.ADD;
472 RAISE FND_API.G_EXC_ERROR;
473 END;
474 --dbms_output.put_line('After Gen pkg Create usage : map id :'|| p_address_usages_rec.map_id);
475 ELSE
476 BEGIN
477 pkg_name := 'HZ_GNR_MAP' ||to_char(db_map_id);
478 l_drp_sql := 'Drop Package Body '|| pkg_name;
479 EXECUTE IMMEDIATE l_drp_sql;
480
481 l_drp_sql := 'Drop Package '|| pkg_name;
482
483 EXECUTE IMMEDIATE l_drp_sql;
484 EXCEPTION when OTHERS then
485 NULL;
486 END;
487 END IF;
488
489 -- Standard call to get message count and if count is 1, get message info.
490 FND_MSG_PUB.Count_And_Get(
491 p_encoded => FND_API.G_FALSE,
492 p_count => x_msg_count,
493 p_data => x_msg_data);
494
495 EXCEPTION
496 WHEN FND_API.G_EXC_ERROR THEN
497 ROLLBACK TO update_address_usages;
498 x_return_status := FND_API.G_RET_STS_ERROR;
499 FND_MSG_PUB.Count_And_Get(
500 p_encoded => FND_API.G_FALSE,
501 p_count => x_msg_count,
502 p_data => x_msg_data);
503 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
504 ROLLBACK TO update_address_usages;
505 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
506 FND_MSG_PUB.Count_And_Get(
507 p_encoded => FND_API.G_FALSE,
508 p_count => x_msg_count,
509 p_data => x_msg_data);
510 WHEN OTHERS THEN
511 ROLLBACK TO update_address_usages;
512 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
513 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
514 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
515 FND_MSG_PUB.ADD;
516 FND_MSG_PUB.Count_And_Get(
517 p_encoded => FND_API.G_FALSE,
518 p_count => x_msg_count,
519 p_data => x_msg_data);
520
521 END update_address_usages;
522
523 PROCEDURE create_address_usage_dtls(
524 p_usage_id IN NUMBER,
525 p_address_usage_dtls_tbl IN address_usage_dtls_tbl_type,
526 x_usage_dtl_id OUT NOCOPY NUMBER,
527 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
528 x_return_status OUT NOCOPY VARCHAR2,
529 x_msg_count OUT NOCOPY NUMBER,
530 x_msg_data OUT NOCOPY VARCHAR2) IS
531
532 p_index_name VARCHAR2(30);
533 l_temp VARCHAR2(1000);
534
535 l_map_id NUMBER;
536 l_usage_dtl_id NUMBER;
537 x_usage_dtl_row_id VARCHAR2(50);
538 l_usage_code VARCHAR2(50);
539 l_geotype VARCHAR2(360);
540 l_status_flag VARCHAR2(1);
541
542 i BINARY_INTEGER;
543 l_error_code VARCHAR2(30);
544 x_status VARCHAR2(30);
545 l_map_dtl_extsts VARCHAR2(1);
546 l_usage_extsts VARCHAR2(1);
547 l_pkgname VARCHAR2(50);
548 l_status VARCHAR2(30);
549
550 BEGIN
551
552 -- Standard start of API savepoint
553 SAVEPOINT create_address_usage_dtls;
554
555 -- Initialize message list if p_init_msg_list is set to TRUE.
556 IF FND_API.to_Boolean(p_init_msg_list) THEN
557 FND_MSG_PUB.initialize;
558 END IF;
559
560 -- Initialize API return status to success.
561 x_return_status := FND_API.G_RET_STS_SUCCESS;
562
563 IF p_usage_id IS NULL THEN
564 x_return_status := FND_API.G_RET_STS_ERROR;
565 FND_MESSAGE.SET_NAME('AR', 'HZ_GEO_INVALID_USAGE_ID');
566 -- Usage Id cannot be null. Please enter a valid Usage Id
567 FND_MSG_PUB.ADD;
568 RAISE FND_API.G_EXC_ERROR;
569 ELSE
570 BEGIN
571 SELECT map_id, usage_code, status_flag
572 INTO l_map_id, l_usage_code, l_status_flag
573 FROM hz_address_usages
574 WHERE usage_id = p_usage_id;
575
576 EXCEPTION WHEN NO_DATA_FOUND THEN
577 x_return_status := FND_API.G_RET_STS_ERROR;
578 FND_MESSAGE.SET_NAME('AR', 'HZ_GEO_INVALID_USAGE_ID');
579 -- Usage Id passed is not valid. Please enter a valid Usage Id.
580 FND_MSG_PUB.ADD;
581 RAISE FND_API.G_EXC_ERROR;
582
583 END;
584 END IF;
585
586 -- Call to business logic.
587
588 --dbms_output.put_line(' message : Usage Created');
589
590 BEGIN
591 i := p_address_usage_dtls_tbl.first;
592 WHILE i IS NOT NULL LOOP
593
594 IF p_address_usage_dtls_tbl(i).geography_type IS NULL THEN
595 x_return_status := FND_API.G_RET_STS_ERROR;
596 FND_MESSAGE.SET_NAME('AR', 'HZ_GEO_GEOGRAPHY_TYPE_MAND');
597 -- Please enter a valid geography type
598 FND_MSG_PUB.ADD;
599 RAISE FND_API.G_EXC_ERROR;
600 ELSE
601 BEGIN
602 SELECT 'X'
603 INTO l_map_dtl_extsts
604 FROM hz_geo_struct_map_dtl
605 WHERE map_id = l_map_id
606 AND geography_type = p_address_usage_dtls_tbl(i).geography_type;
607
608 EXCEPTION WHEN NO_DATA_FOUND THEN
609 x_return_status := FND_API.G_RET_STS_ERROR;
610 FND_MESSAGE.SET_NAME('AR', 'HZ_GEO_GEOTYPE_INVALID');
611 -- Geography Type does not mapped with a location
612 FND_MSG_PUB.ADD;
613 RAISE FND_API.G_EXC_ERROR;
614
615 END;
616 END IF;
617
618 -- The below variable is to display the geography type in case of unique error from pkg API
619 l_geotype := p_address_usage_dtls_tbl(i).geography_type;
620
621 SELECT HZ_ADDRESS_USAGES_S.nextval
622 INTO l_usage_dtl_id
623 FROM dual;
624
625 hz_address_usage_dtls_pkg.insert_row(
626 x_usage_dtl_row_id,
627 l_usage_dtl_id,
628 p_usage_id,
629 p_address_usage_dtls_tbl(i).geography_type,
630 1,
631 p_address_usage_dtls_tbl(i).created_by_module,
632 p_address_usage_dtls_tbl(i).application_id);
633 i := p_address_usage_dtls_tbl.next(i);
634 END LOOP;
635 END; -- insert row
636
637 x_usage_dtl_id := l_usage_dtl_id;
638
639 -- if validation failed at any point, then raise an exception to stop processing
640 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
641 RAISE FND_API.G_EXC_ERROR;
642 END IF;
643
644 IF l_status_flag = 'A' THEN
645 --dbms_output.put_line('Before Gen pkg Create usage dtls : map id :'|| l_map_id);
646 BEGIN
647 hz_gnr_gen_pkg.genpkg(l_map_id,l_pkgname,l_status);
648 IF l_status = FND_API.G_RET_STS_ERROR THEN
649 FND_MESSAGE.SET_NAME('AR', 'HZ_GEO_GNR_PKG_ERR');
650 FND_MSG_PUB.ADD;
651 RAISE FND_API.G_EXC_ERROR;
652 END IF;
653 EXCEPTION WHEN OTHERS THEN
654 FND_MESSAGE.SET_NAME('AR', 'HZ_GEO_GNR_INTERNAL_ERROR');
655 FND_MSG_PUB.ADD;
656 RAISE FND_API.G_EXC_ERROR;
657 END;
658 --dbms_output.put_line('After Gen pkg Create usage dtls : map id :'|| l_map_id);
659 END IF;
660
661 -- Standard call to get message count and if count is 1, get message info.
662 FND_MSG_PUB.Count_And_Get(
663 p_encoded => FND_API.G_FALSE,
664 p_count => x_msg_count,
665 p_data => x_msg_data);
666
667 EXCEPTION
668 WHEN FND_API.G_EXC_ERROR THEN
669 ROLLBACK TO create_address_usage_dtls;
670 x_return_status := FND_API.G_RET_STS_ERROR;
671 FND_MSG_PUB.Count_And_Get(
672 p_encoded => FND_API.G_FALSE,
673 p_count => x_msg_count,
674 p_data => x_msg_data);
675 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
676 ROLLBACK TO create_address_usage_dtls;
677 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
678 FND_MSG_PUB.Count_And_Get(
679 p_encoded => FND_API.G_FALSE,
680 p_count => x_msg_count,
681 p_data => x_msg_data);
682
683 WHEN DUP_VAL_ON_INDEX THEN
684 ROLLBACK TO create_address_usage_dtls;
685 x_return_status := FND_API.G_RET_STS_ERROR;
686 HZ_UTILITY_V2PUB.find_index_name(p_index_name);
687 IF p_index_name = 'HZ_ADDRESS_USAGE_DTLS_U1' THEN
688 FND_MESSAGE.SET_NAME('AR', 'HZ_GEO_DUP_USAGE_DTL_ID');
689 -- Usage detail ID already exists. Please use a unique ID.
690 FND_MESSAGE.SET_TOKEN('P_USAGE_DTL_ID',l_usage_dtl_id);
691 FND_MSG_PUB.ADD;
692 ELSIF p_index_name = 'HZ_ADDRESS_USAGE_DTLS_U2' THEN
693 FND_MESSAGE.SET_NAME('AR', 'HZ_GEO_DUP_USAGE_GEOTYPE');
694 FND_MESSAGE.SET_TOKEN('P_GEOTYPE',l_geotype);
695 FND_MESSAGE.SET_TOKEN('P_USAGE',l_usage_code);
696 -- This geography type is already mapped for this usage.
697 FND_MSG_PUB.ADD;
698 END IF;
699
700 FND_MSG_PUB.Count_And_Get(
701 p_encoded => FND_API.G_FALSE,
702 p_count => x_msg_count,
703 p_data => x_msg_data);
704 WHEN OTHERS THEN
705 ROLLBACK TO create_address_usage_dtls;
706 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
707 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
708 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
709 FND_MSG_PUB.ADD;
710 FND_MSG_PUB.Count_And_Get(
711 p_encoded => FND_API.G_FALSE,
712 p_count => x_msg_count,
713 p_data => x_msg_data);
714
715 END create_address_usage_dtls;
716
717 PROCEDURE delete_address_usages(
718 p_usage_id IN NUMBER,
719 p_address_usage_dtls_tbl IN address_usage_dtls_tbl_type,
720 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
721 x_return_status OUT NOCOPY VARCHAR2,
722 x_msg_count OUT NOCOPY NUMBER,
723 x_msg_data OUT NOCOPY VARCHAR2
724 ) IS
725
726 l_map_id NUMBER;
727 l_usage_dtl_id NUMBER;
728 l_usage_code VARCHAR2(50);
729 l_count NUMBER;
730 l_pkgname VARCHAR2(50);
731 l_status VARCHAR2(30);
732 i NUMBER;
733 l_status_flag VARCHAR2(1);
734 pkg_name VARCHAR2(1000);
735 l_drp_sql VARCHAR2(1000);
736 l_last_usg_dtl_id NUMBER;
737
738 CURSOR c_address_usage_dtls IS
739 SELECT usage_dtl_id
740 FROM hz_address_usage_dtls
741 WHERE usage_id = p_usage_id;
742
743 BEGIN
744
745 -- Standard start of API savepoint
746 SAVEPOINT delete_address_usages;
747
748 -- Initialize message list if p_init_msg_list is set to TRUE.
749 IF FND_API.to_Boolean(p_init_msg_list) THEN
750 FND_MSG_PUB.initialize;
751 END IF;
752
753 -- Initialize API return status to success.
754 x_return_status := FND_API.G_RET_STS_SUCCESS;
755
756 IF p_usage_id IS NULL THEN
757
758 x_return_status := FND_API.G_RET_STS_ERROR;
759 FND_MESSAGE.SET_NAME('AR', 'HZ_GEO_INVALID_USAGE_ID');
760 -- Usage Id cannot be null. Please enter a valid Usage Id
761 FND_MSG_PUB.ADD;
762 RAISE FND_API.G_EXC_ERROR;
763
764 ELSE
765
766 BEGIN
767 SELECT map_id, usage_code, status_flag
768 INTO l_map_id, l_usage_code, l_status_flag
769 FROM hz_address_usages
770 WHERE usage_id = p_usage_id;
771
772 EXCEPTION WHEN NO_DATA_FOUND THEN
773 x_return_status := FND_API.G_RET_STS_ERROR;
774 FND_MESSAGE.SET_NAME('AR', 'HZ_GEO_INVALID_USAGE_ID');
775 -- Usage Id passed is not valid. Please enter a valid Usage Id.
776 FND_MSG_PUB.ADD;
777 RAISE FND_API.G_EXC_ERROR;
778 END;
779
780 IF p_address_usage_dtls_tbl.COUNT > 0 THEN
781
782 i:= p_address_usage_dtls_tbl.FIRST;
783 LOOP
784 IF p_address_usage_dtls_tbl(i).geography_type IS NOT NULL then
785 BEGIN
786 SELECT usage_dtl_id
787 INTO l_usage_dtl_id
788 FROM hz_address_usage_dtls
789 WHERE usage_id = p_usage_id
790 AND geography_type = p_address_usage_dtls_tbl(i).geography_type;
791
792 EXCEPTION WHEN NO_DATA_FOUND THEN
793 x_return_status := FND_API.G_RET_STS_ERROR;
794 FND_MESSAGE.SET_NAME('AR', 'HZ_GEO_USAGE_GEOTYPE_INVALID');
795 FND_MESSAGE.SET_TOKEN('P_GEOTYPE',p_address_usage_dtls_tbl(i).geography_type);
796 FND_MESSAGE.SET_TOKEN('P_USAGE',l_usage_code);
797 -- Geography type does not exists for the given usage
798 FND_MSG_PUB.ADD;
799 RAISE FND_API.G_EXC_ERROR;
800 END;
801
802 BEGIN
803 SELECT count(*)
804 INTO l_count
805 FROM hz_address_usage_dtls
806 WHERE usage_id = p_usage_id;
807
808 IF l_count < 3 then
809
810 BEGIN
811 SELECT usage_dtl_id
812 INTO l_last_usg_dtl_id
813 FROM hz_address_usage_dtls
814 WHERE usage_id = p_usage_id
815 AND usage_dtl_id <> l_usage_dtl_id;
816
817 -- delete the other usage detail also because it is only
818 -- one left can can not exist without any other usage detail
819 -- Bug 5096570 (Nishant 10-Apr-2006)
820 hz_address_usage_dtls_pkg.delete_row(l_last_usg_dtl_id);
821 hz_address_usages_pkg.delete_row(p_usage_id);
822 -- Set i to LAST, so that it does not check any other geo type
823 -- as there is no use (all are deleted)
824 i := p_address_usage_dtls_tbl.LAST;
825 EXCEPTION WHEN NO_DATA_FOUND THEN
826 -- it means only 1 usage detail was there. Delete the usage only
827 -- usage detail will be deleted below
828 /*
829 x_return_status := FND_API.G_RET_STS_ERROR;
830 FND_MESSAGE.SET_NAME('AR', 'HZ_GEO_2_USAGE_DTL_MAND');
831 -- At least two geography types are required for a usage
832 FND_MSG_PUB.ADD;
833 RAISE FND_API.G_EXC_ERROR;
834 */
835 hz_address_usages_pkg.delete_row(p_usage_id);
836 i := p_address_usage_dtls_tbl.LAST;
837 END;
838
839 END IF;
840 END;
841
842 hz_address_usage_dtls_pkg.delete_row(l_usage_dtl_id);
843 END IF;
844 EXIT WHEN i = p_address_usage_dtls_tbl.LAST;
845 i := p_address_usage_dtls_tbl.NEXT(i);
846 END LOOP;
847 ELSE
848 FOR i IN c_address_usage_dtls
849 LOOP
850 hz_address_usage_dtls_pkg.delete_row(i.usage_dtl_id);
851 EXIT WHEN c_address_usage_dtls%NOTFOUND;
852 END LOOP;
853 hz_address_usages_pkg.delete_row(p_usage_id);
854 END IF;
855
856 SELECT count(*)
857 INTO l_count
858 FROM Hz_address_usages usg, Hz_address_usage_dtls dtl
859 WHERE usg.map_id = l_map_id
860 AND usg.status_flag = 'A'
861 AND dtl.usage_id = usg.usage_id;
862
863 -- If count is 0, that means there is no active usage details for this map_id
864 -- In this case we can drop the package
865 IF l_count < 1 THEN
866 BEGIN
867 pkg_name := 'HZ_GNR_MAP' ||to_char(l_map_id);
868 l_drp_sql := 'Drop Package Body '|| pkg_name;
869 EXECUTE IMMEDIATE l_drp_sql;
870
871 l_drp_sql := 'Drop Package '|| pkg_name;
872
873 EXECUTE IMMEDIATE l_drp_sql;
874 EXCEPTION when OTHERS then
875 NULL;
876 END;
877 ELSE
878 -- Checking whether the usage is active or not.
879 IF l_status_flag = 'A' THEN
880 --dbms_output.put_line('Before Gen pkg Delete usage : map id :'|| l_map_id);
881 BEGIN
882 hz_gnr_gen_pkg.genpkg(l_map_id,l_pkgname,l_status);
883 IF l_status = FND_API.G_RET_STS_ERROR THEN
884 FND_MESSAGE.SET_NAME('AR', 'HZ_GEO_GNR_PKG_ERR');
885 FND_MSG_PUB.ADD;
886 RAISE FND_API.G_EXC_ERROR;
887 END IF;
888 EXCEPTION WHEN OTHERS THEN
889 FND_MESSAGE.SET_NAME('AR', 'HZ_GEO_GNR_INTERNAL_ERROR');
890 FND_MSG_PUB.ADD;
891 RAISE FND_API.G_EXC_ERROR;
892 END;
893 --dbms_output.put_line('After Gen pkg Delete usage : map id :'|| l_map_id);
894 END IF;
895 END IF;
896 END IF;
897
898 -- Standard call to get message count and if count is 1, get message info.
899 FND_MSG_PUB.Count_And_Get(
900 p_encoded => FND_API.G_FALSE,
901 p_count => x_msg_count,
902 p_data => x_msg_data);
903
904 EXCEPTION
905 WHEN FND_API.G_EXC_ERROR THEN
906 ROLLBACK TO delete_address_usages;
907 x_return_status := FND_API.G_RET_STS_ERROR;
908 FND_MSG_PUB.Count_And_Get(
909 p_encoded => FND_API.G_FALSE,
910 p_count => x_msg_count,
911 p_data => x_msg_data);
912 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
913 ROLLBACK TO delete_address_usages;
914 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
915 FND_MSG_PUB.Count_And_Get(
916 p_encoded => FND_API.G_FALSE,
917 p_count => x_msg_count,
918 p_data => x_msg_data);
919 WHEN OTHERS THEN
920 ROLLBACK TO delete_address_usages;
921 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
922 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
923 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
924 FND_MSG_PUB.ADD;
925 FND_MSG_PUB.Count_And_Get(
926 p_encoded => FND_API.G_FALSE,
927 p_count => x_msg_count,
928 p_data => x_msg_data);
929
930 END delete_address_usages;
931
932 END HZ_ADDRESS_USAGES_PUB;
[Home] [Help]