[Home] [Help]
PACKAGE BODY: APPS.JTF_LOC_TYPES_PVT
Source
1 PACKAGE BODY JTF_Loc_Types_PVT AS
2 /* $Header: jtfvlotb.pls 120.2 2005/08/18 22:55:44 stopiwal ship $ */
3
4
5 g_pkg_name CONSTANT VARCHAR2(30):='JTF_Loc_Types_PVT';
6
7
8 -------------------------------------------------------------------
9 -- PROCEDURE
10 -- lock_loc_type
11 --
12 -- HISTORY
13 -- 11/17/99 julou Create.
14 --------------------------------------------------------------------
15 PROCEDURE lock_loc_type
16 (
17 p_api_version IN NUMBER,
18 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
19
20 x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
21 x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER,
22 x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
23
24 p_loc_type_id IN NUMBER,
25 p_object_version IN NUMBER
26 )
27 IS
28
29 l_api_version CONSTANT NUMBER := 1.0;
30 l_api_name CONSTANT VARCHAR2(30) := 'lock_loc_type';
31 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
32
33 l_loc_type_id NUMBER;
34
35 CURSOR c_loc_type_b IS
36 SELECT location_type_id
37 FROM JTF_LOC_TYPES_B
38 WHERE location_type_id = p_loc_type_id
39 AND object_version_number = p_object_version
40 FOR UPDATE OF location_type_id NOWAIT;
41
42 CURSOR c_loc_type_tl IS
43 SELECT location_type_id
44 FROM JTF_LOC_TYPES_TL
45 WHERE location_type_id = p_loc_type_id
46 AND USERENV('LANG') IN (language, source_lang)
47 FOR UPDATE OF location_type_id NOWAIT;
48
49 BEGIN
50
51 -------------------- initialize ------------------------
52 IF FND_API.to_boolean(p_init_msg_list) THEN
53 FND_MSG_PUB.initialize;
54 END IF;
55
56 JTF_Utility_PVT.debug_message(l_full_name||': start');
57
58 IF NOT FND_API.compatible_api_call(
59 l_api_version,
60 p_api_version,
61 l_api_name,
62 g_pkg_name
63 ) THEN
64 RAISE FND_API.g_exc_unexpected_error;
65 END IF;
66
67 x_return_status := FND_API.G_RET_STS_SUCCESS;
68
69 ------------------------ lock -------------------------
70 JTF_Utility_PVT.debug_message(l_full_name||': lock');
71
72 OPEN c_loc_type_b;
73 FETCH c_loc_type_b INTO l_loc_type_id;
74 IF (c_loc_type_b%NOTFOUND) THEN
75 CLOSE c_loc_type_b;
76 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
77 FND_MESSAGE.set_name('JTF', 'JTF_API_RECORD_NOT_FOUND');
78 FND_MSG_PUB.add;
79 END IF;
80 RAISE FND_API.g_exc_error;
81 END IF;
82 CLOSE c_loc_type_b;
83
84 OPEN c_loc_type_tl;
85 FETCH c_loc_type_tl INTO l_loc_type_id;
86 IF (c_loc_type_tl%NOTFOUND) THEN
87 CLOSE c_loc_type_tl;
88 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
89 FND_MESSAGE.set_name('JTF', 'JTF_API_RECORD_NOT_FOUND');
90 FND_MSG_PUB.add;
91 END IF;
92 RAISE FND_API.g_exc_error;
93 END IF;
94 CLOSE c_loc_type_tl;
95
96 -------------------- finish --------------------------
97 FND_MSG_PUB.count_and_get(
98 p_encoded => FND_API.g_false,
99 p_count => x_msg_count,
100 p_data => x_msg_data
101 );
102
103 JTF_Utility_PVT.debug_message(l_full_name ||': end');
104
105 EXCEPTION
106
107 WHEN JTF_Utility_PVT.resource_locked THEN
108 x_return_status := FND_API.g_ret_sts_error;
109 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
110 FND_MESSAGE.set_name('JTF', 'JTF_API_RESOURCE_LOCKED');
111 FND_MSG_PUB.add;
112 END IF;
113
114 FND_MSG_PUB.count_and_get(
115 p_encoded => FND_API.g_false,
116 p_count => x_msg_count,
117 p_data => x_msg_data
118 );
119
120 WHEN FND_API.g_exc_error THEN
121 x_return_status := FND_API.g_ret_sts_error;
122 FND_MSG_PUB.count_and_get(
123 p_encoded => FND_API.g_false,
124 p_count => x_msg_count,
125 p_data => x_msg_data
126 );
127
128 WHEN FND_API.g_exc_unexpected_error THEN
129 x_return_status := FND_API.g_ret_sts_unexp_error ;
130 FND_MSG_PUB.count_and_get(
131 p_encoded => FND_API.g_false,
132 p_count => x_msg_count,
133 p_data => x_msg_data
134 );
135
136 WHEN OTHERS THEN
137 x_return_status := FND_API.g_ret_sts_unexp_error ;
138 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error)
139 THEN
140 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
141 END IF;
142
143 FND_MSG_PUB.count_and_get(
144 p_encoded => FND_API.g_false,
145 p_count => x_msg_count,
146 p_data => x_msg_data
147 );
148
149 END lock_loc_type;
150
151
152 ---------------------------------------------------------------------
153 -- PROCEDURE
154 -- update_loc_type
155 --
156 -- HISTORY
157 -- 11/17/99 julou Create.
158 ----------------------------------------------------------------------
159 PROCEDURE update_loc_type
160 (
161 p_api_version IN NUMBER,
162 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
163 p_commit IN VARCHAR2 := FND_API.g_false,
164 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
165
166 x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
167 x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER,
168 x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
169
170 p_loc_type_rec IN loc_type_rec_type
171 )
172 IS
173
174 l_api_version CONSTANT NUMBER := 1.0;
175 l_api_name CONSTANT VARCHAR2(30) := 'update_loc_type';
176 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
177
178 -- l_loc_type_rec loc_type_rec_type := p_loc_type_rec;
179 l_return_status VARCHAR2(1);
180
181 BEGIN
182
183 -------------------- initialize -------------------------
184 SAVEPOINT update_loc_type;
185
186 IF FND_API.to_boolean(p_init_msg_list) THEN
187 FND_MSG_PUB.initialize;
188 END IF;
189
190 JTF_Utility_PVT.debug_message(l_full_name||': start');
191
192 IF NOT FND_API.compatible_api_call
193 (
194 l_api_version,
195 p_api_version,
196 l_api_name,
197 g_pkg_name
198 )
199 THEN
200 RAISE FND_API.g_exc_unexpected_error;
201 END IF;
202
203 x_return_status := FND_API.G_RET_STS_SUCCESS;
204
205 ----------------------- validate ----------------------
206 -- validate
207 JTF_Utility_PVT.debug_message(l_full_name || ': check items');
208
209 check_items
210 (
211 p_validation_mode => JTF_PLSQL_API.g_update,
212 x_return_status => l_return_status,
213 p_loc_type_rec => p_loc_type_rec
214 );
215
216 IF l_return_status = FND_API.g_ret_sts_unexp_error THEN
217 RAISE FND_API.g_exc_unexpected_error;
218 ELSIF l_return_status = FND_API.g_ret_sts_error THEN
219 RAISE FND_API.g_exc_error;
220 END IF;
221
222 -------------------------- update --------------------
223 JTF_Utility_PVT.debug_message(l_full_name ||': update');
224
225 UPDATE JTF_LOC_TYPES_B SET
226 last_update_date = SYSDATE,
227 last_updated_by = FND_GLOBAL.user_id,
228 last_update_login = FND_GLOBAL.conc_login_id,
229 object_version_number = p_loc_type_rec.object_version_number + 1,
230 location_type_code = p_loc_type_rec.location_type_code
231
232 WHERE location_type_id = p_loc_type_rec.location_type_id
233 AND object_version_number = p_loc_type_rec.object_version_number;
234
235 IF (SQL%NOTFOUND) THEN
236 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
237 FND_MESSAGE.set_name('JTF', 'JTF_API_RECORD_NOT_FOUND');
238 FND_MSG_PUB.add;
239 END IF;
240 RAISE FND_API.g_exc_error;
241 END IF;
242
243 UPDATE JTF_LOC_TYPES_TL SET
244 last_update_date = SYSDATE,
245 last_updated_by = FND_GLOBAL.user_id,
246 last_update_login = FND_GLOBAL.conc_login_id,
247 location_type_name = p_loc_type_rec.location_type_name,
248 location_type_description = p_loc_type_rec.description,
249 source_lang = USERENV('LANG')
250
251 WHERE location_type_id = p_loc_type_rec.location_type_id
252 AND USERENV('LANG') IN (language, source_lang);
253
254 IF (SQL%NOTFOUND) THEN
255 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
256 FND_MESSAGE.set_name('JTF', 'JTF_API_RECORD_NOT_FOUND');
257 FND_MSG_PUB.add;
258 END IF;
259 RAISE FND_API.g_exc_error;
260 END IF;
261
262 -------------------- finish --------------------------
263 IF FND_API.to_boolean(p_commit) THEN
264 COMMIT;
265 END IF;
266
267 FND_MSG_PUB.count_and_get(
268 p_encoded => FND_API.g_false,
269 p_count => x_msg_count,
270 p_data => x_msg_data
271 );
272
273 JTF_Utility_PVT.debug_message(l_full_name ||': end');
274
275 EXCEPTION
276
277 WHEN FND_API.g_exc_error THEN
278 ROLLBACK TO update_loc_type;
279 x_return_status := FND_API.g_ret_sts_error;
280 FND_MSG_PUB.count_and_get(
281 p_encoded => FND_API.g_false,
282 p_count => x_msg_count,
283 p_data => x_msg_data
284 );
285
286 WHEN FND_API.g_exc_unexpected_error THEN
287 ROLLBACK TO update_loc_type;
288 x_return_status := FND_API.g_ret_sts_unexp_error ;
289 FND_MSG_PUB.count_and_get(
290 p_encoded => FND_API.g_false,
291 p_count => x_msg_count,
292 p_data => x_msg_data
293 );
294
295 WHEN OTHERS THEN
296 ROLLBACK TO update_loc_type;
297 x_return_status := FND_API.g_ret_sts_unexp_error ;
298
299 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error)
300 THEN
301 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
302 END IF;
303
304 FND_MSG_PUB.count_and_get(
305 p_encoded => FND_API.g_false,
306 p_count => x_msg_count,
307 p_data => x_msg_data
308 );
309
310 END update_loc_type;
311
312
313 /*****************************************************************************/
314 -- Procedure:
315 -- check_items
316 --
317 -- History
318 -- 11/19/1999 julou created
319 -------------------------------------------------------------------------------
320 PROCEDURE check_items
321 (
322 p_validation_mode IN VARCHAR2,
323 x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
324 p_loc_type_rec IN loc_type_rec_type
325 )
326 IS
327
328 l_api_version CONSTANT NUMBER := 1.0;
329 l_api_name CONSTANT VARCHAR2(30) := 'check_items';
330 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name || '.' || l_api_name;
331
332 BEGIN
333 -- initialize
334 JTF_Utility_PVT.debug_message(l_full_name || ': start');
335
336 x_return_status := FND_API.g_ret_sts_success;
337
338 -- check required items
339 JTF_Utility_PVT.debug_message(l_full_name || ': check required items');
340
341 check_loc_type_req_items
342 (
343 p_validation_mode => p_validation_mode,
344 p_loc_type_rec => p_loc_type_rec,
345 x_return_status => x_return_status
346 );
347
348 IF x_return_status <> FND_API.g_ret_sts_success THEN
349 RETURN;
350 END IF;
351
352 -- check unique key items
353 JTF_Utility_PVT.debug_message(l_full_name || ': check uk items');
354 check_loc_type_uk_items
355 (
356 p_validation_mode => p_validation_mode,
357 p_loc_type_rec => p_loc_type_rec,
358 x_return_status => x_return_status
359 );
360
361 IF x_return_status <> FND_API.g_ret_sts_success THEN
362 RETURN;
363 END IF;
364
365 END check_items;
366
367
368 /*****************************************************************************/
369 -- Procedure:
370 -- check_loc_type_req_items
371 --
372 -- Hisory
373 -- 11/18/1999 julou created
374 -------------------------------------------------------------------------------
375 PROCEDURE check_loc_type_req_items
376 (
377 p_validation_mode IN VARCHAR2,
378 p_loc_type_rec IN loc_type_rec_type,
379 x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2
380 )
381 IS
382
383 BEGIN
384
385 x_return_status := FND_API.g_ret_sts_success;
386
387 -- check location_type_id
388 IF p_loc_type_rec.location_type_id IS NULL THEN
389 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
390 FND_MESSAGE.set_name('JTF', 'JTF_LOC_TYPE_NO_TYPE_ID');
391 FND_MSG_PUB.add;
392 END IF;
393
394 x_return_status := FND_API.g_ret_sts_error;
395 RETURN;
396
397 END IF;
398
399 -- check object_version_number
400 IF p_loc_type_rec.object_version_number IS NULL THEN
401 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
402 FND_MESSAGE.set_name('JTF', 'JTF_API_NO_OBJ_VER_NUM');
403 FND_MSG_PUB.add;
404 END IF;
405
406 x_return_status := FND_API.g_ret_sts_error;
407 RETURN;
408 END IF;
409
410 -- check location_type_code
411 IF p_loc_type_rec.location_type_code IS NULL THEN
412 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
413 FND_MESSAGE.set_name('JTF', 'JTF_LOC_TYPE_NO_TYPE_CODE');
414 FND_MSG_PUB.add;
415 END IF;
416
417 x_return_status := FND_API.g_ret_sts_error;
418 RETURN;
419
420 END IF;
421
422 -- check location_type_name
423 IF p_loc_type_rec.location_type_name IS NULL THEN
424 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
425 FND_MESSAGE.set_name('JTF', 'JTF_LOC_TYPE_NO_TYPE_NAME');
426 FND_MSG_PUB.add;
427 END IF;
428
429 x_return_status := FND_API.g_ret_sts_error;
430 RETURN;
431
432 END IF;
433
434 END check_loc_type_req_items;
435
436
437 /*****************************************************************************/
438 -- Procedure:
439 -- check_loc_type_uk_items
440 --
441 -- History
442 -- 11/18/1999 julou created
443 -------------------------------------------------------------------------------
444 PROCEDURE check_loc_type_uk_items
445 (
446 p_validation_mode IN VARCHAR2,
447 p_loc_type_rec IN loc_type_rec_type,
448 x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2
449 )
450 IS
451
452 l_uk_flag VARCHAR2(1);
453
454 BEGIN
455
456 x_return_status := FND_API.g_ret_sts_success;
457
458 l_uk_flag := JTF_Utility_PVT.check_uniqueness
459 (
460 'JTF_LOC_TYPES_TL',
461 'location_type_id <> ' || p_loc_type_rec.location_type_id
462 || ' AND location_type_name = ''' || p_loc_type_rec.location_type_name
463 || ''' AND language = ''' || USERENV('LANG') ||''''
464 );
465
466 IF l_uk_flag = FND_API.g_false THEN
467 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
468 FND_MESSAGE.set_name('JTF', 'JTF_LOC_TYPE_DUP_NAME_LANG');
469 FND_MSG_PUB.add;
470 END IF;
471
472 x_return_status := FND_API.g_ret_sts_error;
473 RETURN;
474 END IF;
475
476 END check_loc_type_uk_items;
477
478 END JTF_Loc_Types_PVT;