[Home] [Help]
PACKAGE BODY: APPS.JTF_TTY_GEOSOURCE_PUB
Source
1 PACKAGE BODY JTF_TTY_GEOSOURCE_PUB AS
2 /* $Header: jtftgspb.pls 120.0 2005/06/02 18:21:15 appldev ship $ */
3 -- ---------------------------------------------------
4 -- Start of Comments
5 -- ---------------------------------------------------
6 -- PACKAGE NAME: JTF_TTY_GEOSOURCE_PUB
7 -- ---------------------------------------------------
8 -- PURPOSE
9 -- This package contains APIs for populating geographies source
10 -- table for Territory Manager
11 --
12 -- Procedures:
13 -- (see below for specification)
14 --
15 -- NOTES
16 -- This package is publicly available for use
17 --
18 -- HISTORY
19 -- 08/11/03 SGKUMAR Created
20 -- 06/01/04 SGKUMAR changed create API for performance fix
21 --
22 -- End of Comments
23 --
24 /***********************************************************
25 * Creates a Geography
26 * Non Required parameters: state code, province code, county
27 * code, city and postal code
28 ************************************************************/
29 PROCEDURE create_geo(
30 p_geo_type IN VARCHAR2,
31 p_geo_name IN VARCHAR2,
32 p_geo_code IN VARCHAR2,
33 p_country_code IN VARCHAR2,
34 p_state_code IN VARCHAR2 default null,
35 p_province_code IN VARCHAR2 default null,
36 p_county_code IN VARCHAR2 default null,
37 p_city_code IN VARCHAR2 default null,
38 p_postal_code IN VARCHAR2 default null,
39 x_return_status IN OUT NOCOPY VARCHAR2,
40 x_error_msg IN OUT NOCOPY VARCHAR2)
41 AS
42 l_api_name CONSTANT VARCHAR2(30) := 'CREATE_GEO';
43 p_parent_exists_flag VARCHAR2(1) DEFAULT 'Y';
44 p_exists_flag VARCHAR2(1) DEFAULT 'Y';
45 p_geo_type_exists_flag VARCHAR2(1) DEFAULT 'Y';
46 p_user_id NUMBER;
47 p_date DATE;
48 BEGIN
49 p_user_id := fnd_global.user_id;
50 p_date := sysdate;
51 BEGIN
52 SELECT 'Y'
53 into p_geo_type_exists_flag
54 FROM fnd_lookups
55 WHERE lookup_type = 'JTF_TTY_GEO_TYPE'
56 AND lookup_code = p_geo_type
57 AND ROWNUM < 2;
58 EXCEPTION
59 WHEN NO_DATA_FOUND THEN
60 p_geo_type_exists_flag := 'N';
61 END;
62
63 -- do validations
64 x_return_status := fnd_api.g_ret_sts_success;
65 IF(p_geo_type is null OR p_geo_name is null
66 OR p_geo_code is null OR p_country_code is null) THEN
67 x_return_status := fnd_api.g_ret_sts_error;
68 fnd_message.set_name('JTF', 'JTF_TTY_NOT_NULL');
69 x_error_msg := fnd_message.Get();
70 -- check if parent geo is there
71 elsif (p_geo_type_exists_flag = 'N') THEN
72 x_return_status := fnd_api.g_ret_sts_error;
73 fnd_message.set_name('JTF', 'JTF_TTY_GEO_TYPE_INVALID');
74 fnd_message.set_token('p_geo_type',p_geo_type);
75 x_error_msg := fnd_message.Get();
76 elsif (p_geo_type = 'STATE') THEN
77 BEGIN
78 SELECT 'Y'
79 into p_parent_exists_flag
80 FROM jtf_tty_geographies
81 WHERE geo_type = 'COUNTRY'
82 AND geo_code = p_country_code
83 AND ROWNUM < 2;
84 EXCEPTION
85 WHEN NO_DATA_FOUND THEN
86 p_parent_exists_flag := 'N';
87 END;
88 if (p_parent_exists_flag = 'N') then
89 x_return_status := fnd_api.g_ret_sts_error;
90 fnd_message.set_name('JTF', 'JTF_TTY_GEO_PARENT_NOTEXIST');
91 fnd_message.set_token('p_geo_code',p_geo_code);
92 fnd_message.set_token('p_geo_type',p_geo_type);
93 fnd_message.set_token('p_country_code',p_country_code);
94 fnd_message.set_token('p_geo_name',p_geo_name);
95 x_error_msg := fnd_message.Get();
96 end if;
97 BEGIN
98 SELECT 'Y'
99 into p_exists_flag
100 FROM jtf_tty_geographies
101 WHERE geo_type = 'STATE'
102 AND country_code = p_country_code
103 AND state_code = p_state_code
104 AND ROWNUM < 2;
105 EXCEPTION
106 WHEN NO_DATA_FOUND THEN
107 p_exists_flag := 'N';
108 END;
109 if (p_exists_flag = 'Y') then
110 x_return_status := fnd_api.g_ret_sts_error;
111 fnd_message.set_name('JTF', 'JTF_TTY_GEO_UNIQUE');
112 -- fnd_message.set_token('p_geo_code',p_geo_code);
113 -- fnd_message.set_token('p_geo_type',p_geo_type);
114 -- fnd_message.set_token('p_country_code',p_country_code);
115 -- fnd_message.set_token('p_geo_name',p_geo_name);
116 x_error_msg := fnd_message.Get();
117 end if;
118 elsif (p_geo_type = 'PROVINCE') THEN
119 BEGIN
120 SELECT 'Y'
121 into p_parent_exists_flag
122 FROM jtf_tty_geographies
123 WHERE geo_type = 'COUNTRY'
124 AND geo_code = p_country_code
125 AND ROWNUM < 2;
126 EXCEPTION
127 WHEN NO_DATA_FOUND THEN
128 p_parent_exists_flag := 'N';
129 END;
130 if (p_parent_exists_flag = 'N') then
131 x_return_status := fnd_api.g_ret_sts_error;
132 fnd_message.set_name('JTF', 'JTF_TTY_GEO_PARENT_NOTEXIST');
133 fnd_message.set_token('p_geo_code',p_geo_code);
134 fnd_message.set_token('p_geo_type',p_geo_type);
135 fnd_message.set_token('p_country_code',p_country_code);
136 fnd_message.set_token('p_geo_name',p_geo_name);
137 x_error_msg := fnd_message.Get();
138 end if;
139 BEGIN
140 SELECT 'Y'
141 into p_exists_flag
142 FROM jtf_tty_geographies
143 WHERE geo_type = 'PROVINCE'
144 AND country_code = p_country_code
145 AND province_code = p_province_code
146 AND ROWNUM < 2;
147 EXCEPTION
148 WHEN NO_DATA_FOUND THEN
149 p_exists_flag := 'N';
150 END;
151 if (p_exists_flag = 'Y') then
152 x_return_status := fnd_api.g_ret_sts_error;
153 fnd_message.set_name('JTF', 'JTF_TTY_GEO_UNIQUE');
154 -- fnd_message.set_token('p_geo_code',p_geo_code);
155 -- fnd_message.set_token('p_geo_type',p_geo_type);
156 -- fnd_message.set_token('p_country_code',p_country_code);
157 -- fnd_message.set_token('p_geo_name',p_geo_name);
158 x_error_msg := fnd_message.Get();
159 end if;
160 elsif (p_geo_type = 'COUNTY') THEN
161 BEGIN
162 IF (p_state_code is not NULL) THEN
163 SELECT 'Y'
164 into p_parent_exists_flag
165 FROM jtf_tty_geographies
166 WHERE geo_type = 'STATE'
167 AND country_code = p_country_code
168 AND state_code = p_state_code
169 AND ROWNUM < 2;
170 ELSE
171 SELECT 'Y'
172 into p_parent_exists_flag
173 FROM jtf_tty_geographies
174 WHERE geo_type = 'PROVINCE'
175 AND country_code = p_country_code
176 AND province_code = p_province_code
177 AND ROWNUM < 2;
178 END IF;
179 EXCEPTION
180 WHEN NO_DATA_FOUND THEN
181 p_parent_exists_flag := 'N';
182 END;
183 if (p_parent_exists_flag = 'N') then
184 x_return_status := fnd_api.g_ret_sts_error;
185 fnd_message.set_name('JTF', 'JTF_TTY_GEO_PARENT_NOTEXIST');
186 fnd_message.set_token('p_geo_code',p_geo_code);
187 fnd_message.set_token('p_geo_type',p_geo_type);
188 fnd_message.set_token('p_country_code',p_country_code);
189 fnd_message.set_token('p_geo_name',p_geo_name);
190 x_error_msg := fnd_message.Get();
191 end if;
192 elsif (p_geo_type = 'CITY') THEN
193 BEGIN
194 SELECT 'Y'
195 into p_parent_exists_flag
196 FROM jtf_tty_geographies
197 WHERE ((geo_type = 'STATE' and (p_state_code is not null and p_county_code is null))
198 or
199 (geo_type = 'PROVINCE' and (p_province_code is not null and p_county_code is null))
200 or
201 (geo_type = 'COUNTY' and p_county_code is not null))
202 AND country_code = p_country_code
203 AND ((state_code = p_state_code and p_state_code is not null)
204 OR
205 (province_code = p_province_code and p_province_code is not null))
206 AND (p_county_code is null or county_code = p_county_code)
207 AND ROWNUM < 2;
208 EXCEPTION
209 WHEN NO_DATA_FOUND THEN
210 p_parent_exists_flag := 'N';
211 END;
212 if (p_parent_exists_flag = 'N') then
213 x_return_status := fnd_api.g_ret_sts_error;
214 fnd_message.set_name('JTF', 'JTF_TTY_GEO_PARENT_NOTEXIST');
215 fnd_message.set_token('p_geo_code',p_geo_code);
216 fnd_message.set_token('p_geo_type',p_geo_type);
217 fnd_message.set_token('p_country_code',p_country_code);
218 fnd_message.set_token('p_geo_name',p_geo_name);
219 x_error_msg := fnd_message.Get();
220 end if;
221 elsif (p_geo_type = 'POSTAL_CODE') THEN
222 BEGIN
223 SELECT 'Y'
224 into p_parent_exists_flag
225 FROM jtf_tty_geographies
226 WHERE (
227 (p_city_code is not null and geo_type = 'CITY')
228 OR
229 ((p_city_code is null and p_county_code is not null) and geo_type = 'COUNTY')
230 OR
231 ((p_city_code is null and p_county_code is null and p_state_code is not null) and geo_type = 'STATE')
232 OR
233 ((p_city_code is null and p_county_code is null and p_province_code is not null) and geo_type = 'PROVINCE')
234 )
235 AND country_code = p_country_code
236 AND ((state_code = p_state_code and p_state_code is not null)
237 OR
238 (province_code = p_province_code and p_province_code is not null))
239 AND (p_county_code is null or county_code = p_county_code)
240 AND (p_city_code is null or city_code = p_city_code)
241 AND ROWNUM < 2;
242 EXCEPTION
243 WHEN NO_DATA_FOUND THEN
244 p_parent_exists_flag := 'N';
245 END;
246 if (p_parent_exists_flag = 'N') then
247 x_return_status := fnd_api.g_ret_sts_error;
248 fnd_message.set_name('JTF', 'JTF_TTY_GEO_PARENT_NOTEXIST');
249 fnd_message.set_token('p_geo_code',p_geo_code);
250 fnd_message.set_token('p_geo_type',p_geo_type);
251 fnd_message.set_token('p_country_code',p_country_code);
252 fnd_message.set_token('p_geo_name',p_geo_name);
253 x_error_msg := fnd_message.Get();
254 end if;
255 END IF;
256 IF (x_return_status <> fnd_api.g_ret_sts_success) THEN
257 RAISE fnd_api.g_exc_error;
258 END IF;
259 -- Create the geography, validations done
260 INSERT INTO jtf_tty_geographies(
261 geo_id,
262 geo_name,
263 geo_type,
264 geo_code,
265 country_code,
266 state_code,
267 province_code,
268 county_code,
269 city_code,
270 postal_code,
274 last_updated_by,
271 object_version_number,
272 created_by,
273 creation_date,
275 last_update_date)
276 VALUES(
277 jtf_tty_geographies_s.nextval,
278 p_geo_name,
279 p_geo_type,
280 p_geo_code,
281 p_country_code,
282 decode(p_geo_type, 'STATE', p_geo_code, p_state_code),
283 decode(p_geo_type, 'PROVINCE', p_geo_code, p_province_code),
284 decode(p_geo_type, 'COUNTY', p_geo_code, p_county_code),
285 decode(p_geo_type, 'CITY', p_geo_code, p_city_code),
286 decode(p_geo_type, 'POSTAL_CODE', p_geo_code, p_postal_code),
287 1,
288 p_user_id,
289 p_date,
290 p_user_id,
291 p_date);
292 COMMIT;
293 EXCEPTION
294
295 WHEN fnd_api.g_exc_unexpected_error THEN
296 x_return_status := fnd_api.g_ret_sts_unexp_error;
297
298 WHEN fnd_api.g_exc_error THEN
299 x_return_status := fnd_api.g_ret_sts_error;
300
301 WHEN OTHERS THEN
302 fnd_message.set_name('JTF', 'JTF_TTY_GEO_API_OTHERS');
303 fnd_message.set_token('P_SQLCODE', SQLCODE);
304 fnd_message.set_token('P_SQLERRM', SQLERRM);
305 fnd_message.set_token('P_API_NAME', l_api_name);
306 x_error_msg := fnd_message.Get();
307 -- FND_MSG_PUB.add;
308 x_return_status := fnd_api.g_ret_sts_unexp_error;
309
310 END create_geo;
311
312 /***********************************************************
313 * Updates a Geography
314 * Non Required parameters: state code, province code, county
315 * code, city and postal code
316 ************************************************************/
317 PROCEDURE update_geo(
318 p_geo_id IN VARCHAR2,
319 p_geo_name IN VARCHAR2,
320 x_return_status IN OUT NOCOPY VARCHAR2,
321 x_error_msg IN OUT NOCOPY VARCHAR2)
322 AS
323 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_GEO';
324 p_exists_flag VARCHAR2(1) DEFAULT 'Y';
325 p_user_id NUMBER;
326 p_date DATE;
327 BEGIN
328 p_user_id := fnd_global.user_id;
329 p_date := sysdate;
330
331 -- do validations
332 x_return_status := fnd_api.g_ret_sts_success;
333 IF (p_geo_id is null OR p_geo_name is null) THEN
334 x_return_status := fnd_api.g_ret_sts_error;
335 x_return_status := fnd_api.g_ret_sts_error;
336 fnd_message.set_name('JTF', 'JTF_TTY_UPDATE_NOT_ENOUGHVALUES');
337 x_error_msg := fnd_message.Get();
338 END IF;
339 BEGIN
340 SELECT 'Y'
341 into p_exists_flag
342 FROM jtf_tty_geographies
343 WHERE geo_id = p_geo_id;
344 EXCEPTION
345 WHEN NO_DATA_FOUND THEN
346 p_exists_flag := 'N';
347 END;
348 IF (p_exists_flag = 'N') THEN
349 x_return_status := fnd_api.g_ret_sts_error;
350 fnd_message.set_name('JTF', 'JTF_TTY_GEOID_NOTEXIST');
351 fnd_message.set_token('p_geo_id',p_geo_id);
352 fnd_message.set_token('p_action_type','updated');
353 x_error_msg := fnd_message.Get();
354 END IF;
355 IF (x_return_status <> fnd_api.g_ret_sts_success) THEN
356 RAISE fnd_api.g_exc_error;
357 END IF;
358 -- passed all validations, now perform update operation
359 UPDATE jtf_tty_geographies
360 SET geo_name = p_geo_name,
361 last_updated_by = p_user_id,
362 last_update_date = p_date
363 WHERE geo_id = p_geo_id;
364 COMMIT;
365 EXCEPTION
366
367 WHEN fnd_api.g_exc_unexpected_error THEN
368 x_return_status := fnd_api.g_ret_sts_unexp_error;
369
370 WHEN fnd_api.g_exc_error THEN
371 x_return_status := fnd_api.g_ret_sts_error;
372
373 WHEN OTHERS THEN
374 fnd_message.set_name('JTF', 'JTF_TTY_GEO_API_OTHERS');
375 fnd_message.set_token('P_SQLCODE', SQLCODE);
376 fnd_message.set_token('P_SQLERRM', SQLERRM);
377 fnd_message.set_token('P_API_NAME', l_api_name);
378 x_error_msg := fnd_message.Get();
379 x_return_status := fnd_api.g_ret_sts_unexp_error;
380 END update_geo;
381
382 /***********************************************************
383 * Deletes a Geography
384 * Non Required parameters: state code, province code, county
385 * code, city and postal code
386 ************************************************************/
387 PROCEDURE delete_geo(
388 p_geo_type IN VARCHAR2,
389 p_geo_code IN VARCHAR2,
390 p_country_code IN VARCHAR2,
391 p_state_code IN VARCHAR2 default null,
392 p_province_code IN VARCHAR2 default null,
393 p_county_code IN VARCHAR2 default null,
394 p_city_code IN VARCHAR2 default null,
395 p_postal_code IN VARCHAR2 default null,
396 p_delete_cascade_flag IN VARCHAR2 default 'N',
397 x_return_status IN OUT NOCOPY VARCHAR2,
398 x_error_msg IN OUT NOCOPY VARCHAR2)
399 AS
400 l_api_name CONSTANT VARCHAR2(30) := 'DELETE_GEO';
401 p_child_exists_flag VARCHAR2(1) DEFAULT 'Y';
402 p_parent_exists_flag VARCHAR2(1) DEFAULT 'Y';
403 p_geo_type_exists_flag VARCHAR2(1) DEFAULT 'Y';
404 p_exists_flag VARCHAR2(1) DEFAULT 'Y';
405 p_user_id NUMBER;
406 p_date DATE;
407 BEGIN
408 p_user_id := fnd_global.user_id;
409 p_date := sysdate;
410 BEGIN
411 SELECT 'Y'
412 INTO p_exists_flag
413 FROM jtf_tty_geographies
414 WHERE country_code = p_country_code
415 AND geo_code = p_geo_code
416 AND geo_type = p_geo_type
417 AND (p_state_code is null or state_code = p_state_code)
418 AND (p_province_code is null or province_code = p_province_code)
419 AND (p_county_code is null or county_code = p_county_code)
420 AND (p_city_code is null or city_code = p_city_code)
421 AND (p_postal_code is null or postal_code = p_postal_code)
422 AND ROWNUM < 2;
423 EXCEPTION
424 WHEN NO_DATA_FOUND THEN
425 p_exists_flag := 'N';
426 END;
427 BEGIN
428 SELECT 'Y'
429 into p_geo_type_exists_flag
430 FROM fnd_lookups
431 WHERE lookup_type = 'JTF_TTY_GEO_TYPE'
432 AND lookup_code = p_geo_type
433 AND ROWNUM < 2;
434 EXCEPTION
435 WHEN NO_DATA_FOUND THEN
436 p_geo_type_exists_flag := 'N';
437 END;
438
439 -- do validations
440 x_return_status := fnd_api.g_ret_sts_success;
441 IF(p_geo_type is null
442 OR p_geo_code is null OR p_country_code is null) THEN
443 x_return_status := fnd_api.g_ret_sts_error;
444 fnd_message.set_name('JTF', 'JTF_TTY_NOT_NULL');
445 x_error_msg := fnd_message.Get();
446 elsif(p_exists_flag = 'N') THEN
447 x_return_status := fnd_api.g_ret_sts_error;
448 fnd_message.set_name('JTF', 'JTF_TTY_GEO_NOTEXIST');
449 x_error_msg := fnd_message.Get();
450 -- check if parent geo is there
451 elsif (p_geo_type_exists_flag = 'N') THEN
452 x_return_status := fnd_api.g_ret_sts_error;
453 fnd_message.set_name('JTF', 'JTF_TTY_GEO_TYPE_INVALID');
454 fnd_message.set_token('p_geo_type',p_geo_type);
455 x_error_msg := fnd_message.Get();
456 elsif (p_geo_type = 'COUNTRY') THEN
457 if (p_delete_cascade_flag = 'N') THEN
458 BEGIN
459 SELECT 'Y'
460 into p_child_exists_flag
461 FROM jtf_tty_geographies
462 WHERE geo_type <> 'COUNTRY'
463 AND country_code = p_country_code
464 AND ROWNUM < 2;
465 EXCEPTION
466 WHEN NO_DATA_FOUND THEN
467 p_child_exists_flag := 'N';
468 END;
469 if (p_child_exists_flag = 'Y') then
470 x_return_status := fnd_api.g_ret_sts_error;
471 fnd_message.set_name('JTF', 'JTF_TTY_GEO_CHILDEXIST');
472 x_error_msg := fnd_message.Get();
473 end if;
474 end if;
475 elsif (p_geo_type = 'STATE') THEN
476 if (p_delete_cascade_flag = 'N') THEN
477 BEGIN
478 SELECT 'Y'
479 into p_child_exists_flag
480 FROM jtf_tty_geographies
481 WHERE (geo_type = 'COUNTY'
482 or geo_type = 'CITY'
483 or geo_type = 'POSTAL_CODE')
484 AND country_code = p_country_code
485 AND state_code = p_geo_code
486 AND ROWNUM < 2;
487 EXCEPTION
488 WHEN NO_DATA_FOUND THEN
489 p_child_exists_flag := 'N';
490 END;
491 if (p_child_exists_flag = 'Y') then
492 x_return_status := fnd_api.g_ret_sts_error;
493 fnd_message.set_name('JTF', 'JTF_TTY_GEO_CHILDEXIST');
494 x_error_msg := fnd_message.Get();
495 end if;
496 end if;
497 elsif (p_geo_type = 'PROVINCE') THEN
498 if (p_delete_cascade_flag = 'N') THEN
499 BEGIN
500 SELECT 'Y'
501 into p_child_exists_flag
502 FROM jtf_tty_geographies
503 WHERE (geo_type = 'COUNTY'
504 or geo_type = 'CITY'
505 or geo_type = 'POSTAL_CODE')
506 AND country_code = p_country_code
507 AND province_code = p_geo_code
508 AND ROWNUM < 2;
509 EXCEPTION
510 WHEN NO_DATA_FOUND THEN
511 p_child_exists_flag := 'N';
512 END;
513 if (p_child_exists_flag = 'Y') then
514 x_return_status := fnd_api.g_ret_sts_error;
515 fnd_message.set_name('JTF', 'JTF_TTY_GEO_CHILDEXIST');
516 x_error_msg := fnd_message.Get();
517 end if;
518 end if;
519 elsif (p_geo_type = 'COUNTY') THEN
520 if (p_delete_cascade_flag = 'N') THEN
521 BEGIN
522 SELECT 'Y'
523 into p_child_exists_flag
524 FROM jtf_tty_geographies
525 WHERE (geo_type = 'CITY' or geo_type = 'POSTAL_CODE')
526 AND country_code = p_country_code
527 AND ((p_province_code is not null or province_code = p_province_code)
528 OR
529 (p_state_code is not null or state_code = p_state_code))
530 AND county_code = p_geo_code
531 AND ROWNUM < 2;
532 EXCEPTION
533 WHEN NO_DATA_FOUND THEN
534 p_child_exists_flag := 'N';
535 END;
536 if (p_child_exists_flag = 'Y') then
537 x_return_status := fnd_api.g_ret_sts_error;
538 fnd_message.set_name('JTF', 'JTF_TTY_GEO_CHILDEXIST');
539 x_error_msg := fnd_message.Get();
540 end if;
541 end if;
542 elsif (p_geo_type = 'CITY') THEN
543 if (p_delete_cascade_flag = 'N') THEN
544 BEGIN
545 SELECT 'Y'
546 into p_child_exists_flag
547 FROM jtf_tty_geographies
548 WHERE geo_type = 'POSTAL_CODE'
549 AND country_code = p_country_code
550 AND ((p_province_code is not null or province_code = p_province_code)
551 OR
552 (p_state_code is not null or state_code = p_state_code))
553 AND (p_county_code is null or county_code = p_county_code)
554 AND city_code = p_city_code
555 AND ROWNUM < 2;
556 EXCEPTION
557 WHEN NO_DATA_FOUND THEN
558 p_child_exists_flag := 'N';
559 END;
560 if (p_child_exists_flag = 'Y') then
561 x_return_status := fnd_api.g_ret_sts_error;
562 fnd_message.set_name('JTF', 'JTF_TTY_GEO_CHILDEXIST');
563 x_error_msg := fnd_message.Get();
564 end if;
565 end if;
566 elsif (p_geo_type = 'POSTAL_CODE') THEN
567 null;
568 END IF;
569 IF (x_return_status <> fnd_api.g_ret_sts_success) THEN
570 RAISE fnd_api.g_exc_error;
571 END IF;
572 -- delete the geography, validations done
573 IF (p_geo_type = 'STATE') THEN
574 if (p_delete_cascade_flag = 'Y') THEN
575 DELETE from jtf_tty_geographies
576 WHERE state_code = p_geo_code
577 AND country_code = p_country_code;
578 else
579 DELETE from jtf_tty_geographies
580 WHERE state_code = p_geo_code
581 AND geo_type = 'STATE'
582 AND country_code = p_country_code;
583 end if;
584 END IF;
585
586 COMMIT;
587 EXCEPTION
588
589 WHEN fnd_api.g_exc_unexpected_error THEN
590 x_return_status := fnd_api.g_ret_sts_unexp_error;
591
592 WHEN fnd_api.g_exc_error THEN
593 x_return_status := fnd_api.g_ret_sts_error;
594
595 WHEN OTHERS THEN
596 fnd_message.set_name('JTF', 'JTF_TTY_GEO_API_OTHERS');
597 fnd_message.set_token('P_SQLCODE', SQLCODE);
598 fnd_message.set_token('P_SQLERRM', SQLERRM);
599 fnd_message.set_token('P_API_NAME', l_api_name);
600 x_error_msg := fnd_message.Get();
601 -- FND_MSG_PUB.add;
602 x_return_status := fnd_api.g_ret_sts_unexp_error;
603
604 END delete_geo;
605
606 END JTF_TTY_GEOSOURCE_PUB;