[Home] [Help]
PACKAGE BODY: APPS.JTF_RS_RES_AVAILABILITY_PUB
Source
1 PACKAGE BODY jtf_rs_res_availability_pub AS
2 /* $Header: jtfrspzb.pls 120.2 2005/07/26 20:59:58 repuri ship $ */
3
4 /*****************************************************************************************
5 This is a public API that user API will invoke.
6 It provides procedures for managing seed data of jtf_rs_res_availability tables
7 create, update and delete rows
8 Its main procedures are as following:
9 Create res_availability
10 Update res_availability
11 Delete res_availability
12 Calls to these procedures will call procedures of jtf_rs_res_availability_pvt
13 to do inserts, updates and deletes into tables.
14 ******************************************************************************************/
15
16 /* Package variables. */
17
18 G_PKG_NAME VARCHAR2(30) := 'JTF_RS_RES_AVAILABILITY_PUB';
19 G_NAME VARCHAR2(240);
20
21 /* Procedure to create table attributes
22 based on input values passed by calling routines. */
23 PROCEDURE create_res_availability
24 (P_API_VERSION IN NUMBER,
25 P_INIT_MSG_LIST IN VARCHAR2 DEFAULT FND_API.G_FALSE,
26 P_COMMIT IN VARCHAR2 DEFAULT FND_API.G_FALSE,
27 P_RESOURCE_ID IN JTF_RS_RES_AVAILABILITY.RESOURCE_ID%TYPE,
28 P_AVAILABLE_FLAG IN JTF_RS_RES_AVAILABILITY.AVAILABLE_FLAG%TYPE,
29 P_REASON_CODE IN JTF_RS_RES_AVAILABILITY.REASON_CODE%TYPE DEFAULT NULL,
30 P_START_DATE IN JTF_RS_RES_AVAILABILITY.START_DATE%TYPE DEFAULT NULL,
31 P_END_DATE IN JTF_RS_RES_AVAILABILITY.END_DATE%TYPE DEFAULT NULL,
32 P_MODE_OF_AVAILABILITY IN JTF_RS_RES_AVAILABILITY.MODE_OF_AVAILABILITY%TYPE,
33 P_ATTRIBUTE1 IN JTF_RS_RES_AVAILABILITY.ATTRIBUTE1%TYPE DEFAULT NULL,
34 P_ATTRIBUTE2 IN JTF_RS_RES_AVAILABILITY.ATTRIBUTE2%TYPE DEFAULT NULL,
35 P_ATTRIBUTE3 IN JTF_RS_RES_AVAILABILITY.ATTRIBUTE3%TYPE DEFAULT NULL,
36 P_ATTRIBUTE4 IN JTF_RS_RES_AVAILABILITY.ATTRIBUTE4%TYPE DEFAULT NULL,
37 P_ATTRIBUTE5 IN JTF_RS_RES_AVAILABILITY.ATTRIBUTE5%TYPE DEFAULT NULL,
38 P_ATTRIBUTE6 IN JTF_RS_RES_AVAILABILITY.ATTRIBUTE6%TYPE DEFAULT NULL,
39 P_ATTRIBUTE7 IN JTF_RS_RES_AVAILABILITY.ATTRIBUTE7%TYPE DEFAULT NULL,
40 P_ATTRIBUTE8 IN JTF_RS_RES_AVAILABILITY.ATTRIBUTE8%TYPE DEFAULT NULL,
41 P_ATTRIBUTE9 IN JTF_RS_RES_AVAILABILITY.ATTRIBUTE9%TYPE DEFAULT NULL,
42 P_ATTRIBUTE10 IN JTF_RS_RES_AVAILABILITY.ATTRIBUTE10%TYPE DEFAULT NULL,
43 P_ATTRIBUTE11 IN JTF_RS_RES_AVAILABILITY.ATTRIBUTE11%TYPE DEFAULT NULL,
44 P_ATTRIBUTE12 IN JTF_RS_RES_AVAILABILITY.ATTRIBUTE12%TYPE DEFAULT NULL,
45 P_ATTRIBUTE13 IN JTF_RS_RES_AVAILABILITY.ATTRIBUTE13%TYPE DEFAULT NULL,
46 P_ATTRIBUTE14 IN JTF_RS_RES_AVAILABILITY.ATTRIBUTE14%TYPE DEFAULT NULL,
47 P_ATTRIBUTE15 IN JTF_RS_RES_AVAILABILITY.ATTRIBUTE15%TYPE DEFAULT NULL,
48 P_ATTRIBUTE_CATEGORY IN JTF_RS_RES_AVAILABILITY.ATTRIBUTE_CATEGORY%TYPE DEFAULT NULL,
49 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
50 X_MSG_COUNT OUT NOCOPY NUMBER,
51 X_MSG_DATA OUT NOCOPY VARCHAR2,
52 X_AVAILABILITY_ID OUT NOCOPY JTF_RS_RES_AVAILABILITY.AVAILABILITY_ID%TYPE
53 )IS
54
55 l_api_name CONSTANT VARCHAR2(30) := 'CREATE_RES_AVAILABILITY';
56 l_api_version CONSTANT NUMBER := 1.0;
57 l_object_version_number NUMBER;
58 l_availability_id NUMBER;
59 l_resource_id NUMBER := p_resource_id;
60 l_return_status VARCHAR2(200);
61 l_msg_count NUMBER;
62 l_msg_data VARCHAR2(200);
63 l_num NUMBER;
64
65 -- Cursor to check if a valid resource_id is being passed.
66 CURSOR c_resource_id_valid (l_resource_id NUMBER) IS
67 SELECT 1
68 FROM jtf_rs_resource_extns
69 WHERE resource_id = l_resource_id;
70
71 BEGIN
72 --Standard Start of API SAVEPOINT
73 SAVEPOINT CREATE_RES_AVAILABILITY_SP;
74
75 x_return_status := fnd_api.g_ret_sts_success;
76
77 --Standard Call to check API compatibility
78 IF NOT FND_API.Compatible_API_CALL(L_API_VERSION,P_API_VERSION,L_API_NAME,G_PKG_NAME)
79 THEN
80 RAISE FND_API.G_EXC_ERROR;
81 END IF;
82
83 --Initialize the message List if P_INIT_MSG_LIST is set to TRUE
84 IF FND_API.To_boolean(P_INIT_MSG_LIST)
85 THEN
86 FND_MSG_PUB.Initialize;
87 END IF;
88
89 --Check if the passed resource_id is valid.
90 OPEN c_resource_id_valid (l_resource_id);
91 FETCH c_resource_id_valid INTO l_num;
92 IF c_resource_id_valid%NOTFOUND THEN
93 IF c_resource_id_valid%ISOPEN THEN
94 CLOSE c_resource_id_valid;
95 END IF;
96 fnd_message.set_name ('JTF', 'JTF_RS_RES_ID_INVALID');
97 FND_MSG_PUB.add;
98 raise fnd_api.g_exc_error;
99 END IF;
100 IF c_resource_id_valid%ISOPEN THEN
101 CLOSE c_resource_id_valid;
102 END IF;
103
104 JTF_RS_RES_AVAILABILITY_PVT.CREATE_RES_AVAILABILITY(
105 P_API_VERSION => l_api_version,
106 P_INIT_MSG_LIST => p_init_msg_list,
107 P_COMMIT => p_commit,
108 P_RESOURCE_ID => p_resource_id,
109 P_AVAILABLE_FLAG => p_available_flag,
110 P_REASON_CODE => p_reason_code,
111 P_START_DATE => p_start_date,
112 P_END_DATE => p_end_date,
113 P_MODE_OF_AVAILABILITY => p_mode_of_availability,
114 P_ATTRIBUTE1 => p_attribute1,
115 P_ATTRIBUTE2 => p_attribute2,
116 P_ATTRIBUTE3 => p_attribute3,
117 P_ATTRIBUTE4 => p_attribute4,
118 P_ATTRIBUTE5 => p_attribute5,
119 P_ATTRIBUTE6 => p_attribute6,
120 P_ATTRIBUTE7 => p_attribute7,
121 P_ATTRIBUTE8 => p_attribute8,
122 P_ATTRIBUTE9 => p_attribute9,
123 P_ATTRIBUTE10 => p_attribute10,
124 P_ATTRIBUTE11 => p_attribute11,
125 P_ATTRIBUTE12 => p_attribute12,
126 P_ATTRIBUTE13 => p_attribute13,
127 P_ATTRIBUTE14 => p_attribute14,
128 P_ATTRIBUTE15 => p_attribute15,
129 P_ATTRIBUTE_CATEGORY => p_attribute_category,
130 X_RETURN_STATUS => l_return_status,
131 X_MSG_COUNT => l_msg_count ,
132 X_MSG_DATA => l_msg_data,
133 X_AVAILABILITY_ID => l_availability_id
134 );
135
136 X_AVAILABILITY_ID := l_availability_id;
137 X_RETURN_STATUS := l_return_status;
138 X_MSG_COUNT := l_msg_count;
139 X_MSG_DATA := l_msg_data;
140
141
142 --standard commit
143 IF fnd_api.to_boolean (p_commit)
144 THEN
145 COMMIT WORK;
146 END IF;
147
148 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
149
150 EXCEPTION
151 WHEN fnd_api.g_exc_unexpected_error
152 THEN
153 ROLLBACK TO CREATE_RES_AVAILABILITY_SP;
154 x_return_status := fnd_api.g_ret_sts_unexp_error;
155 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
156 WHEN fnd_api.g_exc_error
157 THEN
158 ROLLBACK TO CREATE_RES_AVAILABILITY_SP;
159 x_return_status := fnd_api.g_ret_sts_error;
160 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
161 WHEN OTHERS
162 THEN
163 ROLLBACK TO CREATE_RES_AVAILABILITY_SP;
164 fnd_message.set_name ('JTF', 'JTF_RS_UNEXP_ERROR');
165 fnd_message.set_token('P_SQLCODE',SQLCODE);
166 fnd_message.set_token('P_SQLERRM',SQLERRM);
167 fnd_message.set_token('P_API_NAME', l_api_name);
168 FND_MSG_PUB.add;
169 x_return_status := fnd_api.g_ret_sts_unexp_error;
170 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
171
172 END CREATE_RES_AVAILABILITY;
173
174
175 /* Procedure to update resource availability
176 based on input values passed by calling routines. */
177
178 PROCEDURE update_res_availability
179 (P_API_VERSION IN NUMBER,
180 P_INIT_MSG_LIST IN VARCHAR2 DEFAULT FND_API.G_FALSE,
181 P_COMMIT IN VARCHAR2 DEFAULT FND_API.G_FALSE,
182 P_AVAILABILITY_ID IN JTF_RS_RES_AVAILABILITY.AVAILABILITY_ID%TYPE,
183 P_RESOURCE_ID IN JTF_RS_RES_AVAILABILITY.RESOURCE_ID%TYPE DEFAULT FND_API.G_MISS_NUM,
184 P_AVAILABLE_FLAG IN JTF_RS_RES_AVAILABILITY.AVAILABLE_FLAG%TYPE DEFAULT FND_API.G_MISS_CHAR,
185 P_REASON_CODE IN JTF_RS_RES_AVAILABILITY.REASON_CODE%TYPE DEFAULT FND_API.G_MISS_CHAR,
186 P_START_DATE IN JTF_RS_RES_AVAILABILITY.START_DATE%TYPE DEFAULT FND_API.G_MISS_DATE,
187 P_END_DATE IN JTF_RS_RES_AVAILABILITY.END_DATE%TYPE DEFAULT FND_API.G_MISS_DATE,
188 P_MODE_OF_AVAILABILITY IN JTF_RS_RES_AVAILABILITY.MODE_OF_AVAILABILITY%TYPE DEFAULT FND_API.G_MISS_CHAR,
189 P_OBJECT_VERSION_NUM IN OUT NOCOPY JTF_RS_RES_AVAILABILITY.OBJECT_VERSION_NUMBER%TYPE,
190 P_ATTRIBUTE1 IN JTF_RS_RES_AVAILABILITY.ATTRIBUTE1%TYPE DEFAULT FND_API.G_MISS_CHAR,
191 P_ATTRIBUTE2 IN JTF_RS_RES_AVAILABILITY.ATTRIBUTE2%TYPE DEFAULT FND_API.G_MISS_CHAR,
192 P_ATTRIBUTE3 IN JTF_RS_RES_AVAILABILITY.ATTRIBUTE3%TYPE DEFAULT FND_API.G_MISS_CHAR,
193 P_ATTRIBUTE4 IN JTF_RS_RES_AVAILABILITY.ATTRIBUTE4%TYPE DEFAULT FND_API.G_MISS_CHAR,
194 P_ATTRIBUTE5 IN JTF_RS_RES_AVAILABILITY.ATTRIBUTE5%TYPE DEFAULT FND_API.G_MISS_CHAR,
195 P_ATTRIBUTE6 IN JTF_RS_RES_AVAILABILITY.ATTRIBUTE6%TYPE DEFAULT FND_API.G_MISS_CHAR,
196 P_ATTRIBUTE7 IN JTF_RS_RES_AVAILABILITY.ATTRIBUTE7%TYPE DEFAULT FND_API.G_MISS_CHAR,
197 P_ATTRIBUTE8 IN JTF_RS_RES_AVAILABILITY.ATTRIBUTE8%TYPE DEFAULT FND_API.G_MISS_CHAR,
198 P_ATTRIBUTE9 IN JTF_RS_RES_AVAILABILITY.ATTRIBUTE9%TYPE DEFAULT FND_API.G_MISS_CHAR,
199 P_ATTRIBUTE10 IN JTF_RS_RES_AVAILABILITY.ATTRIBUTE10%TYPE DEFAULT FND_API.G_MISS_CHAR,
200 P_ATTRIBUTE11 IN JTF_RS_RES_AVAILABILITY.ATTRIBUTE11%TYPE DEFAULT FND_API.G_MISS_CHAR,
201 P_ATTRIBUTE12 IN JTF_RS_RES_AVAILABILITY.ATTRIBUTE12%TYPE DEFAULT FND_API.G_MISS_CHAR,
202 P_ATTRIBUTE13 IN JTF_RS_RES_AVAILABILITY.ATTRIBUTE13%TYPE DEFAULT FND_API.G_MISS_CHAR,
203 P_ATTRIBUTE14 IN JTF_RS_RES_AVAILABILITY.ATTRIBUTE14%TYPE DEFAULT FND_API.G_MISS_CHAR,
204 P_ATTRIBUTE15 IN JTF_RS_RES_AVAILABILITY.ATTRIBUTE15%TYPE DEFAULT FND_API.G_MISS_CHAR,
205 P_ATTRIBUTE_CATEGORY IN JTF_RS_RES_AVAILABILITY.ATTRIBUTE_CATEGORY%TYPE DEFAULT FND_API.G_MISS_CHAR,
206 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
207 X_MSG_COUNT OUT NOCOPY NUMBER,
208 X_MSG_DATA OUT NOCOPY VARCHAR2
209 )IS
210
211 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_RES_AVAILABILITY';
212 l_api_version CONSTANT NUMBER := 1.0;
213 l_object_version_number JTF_RS_RES_AVAILABILITY.OBJECT_VERSION_NUMBER%TYPE := P_OBJECT_VERSION_NUM;
214 l_resource_id NUMBER := p_resource_id;
215 l_availability_id NUMBER := p_availability_id;
216 l_return_status VARCHAR2(200);
217 l_msg_count NUMBER;
218 l_msg_data VARCHAR2(200);
219 l_num NUMBER;
220
221 -- Cursor to check if a valid resource_id is being passed.
222 CURSOR c_resource_id_valid (l_resource_id NUMBER) IS
223 SELECT 1
224 FROM jtf_rs_resource_extns
225 WHERE resource_id = l_resource_id;
226
227 -- Cursor to check if a valid availability_id is being passed.
228 CURSOR c_availability_id_valid (l_availability_id NUMBER) IS
229 SELECT 1
230 FROM jtf_rs_res_availability
231 WHERE availability_id = l_availability_id;
232
233 BEGIN
234 --Standard Start of API SAVEPOINT
235 SAVEPOINT UPDATE_RES_AVAILABILITY_SP;
236
237 x_return_status := fnd_api.g_ret_sts_success;
238
239 --Standard Call to check API compatibility
240 IF NOT FND_API.Compatible_API_CALL(L_API_VERSION,P_API_VERSION,L_API_NAME,G_PKG_NAME)
241 THEN
242 RAISE FND_API.G_EXC_ERROR;
243 END IF;
244
245 --Initialize the message List if P_INIT_MSG_LIST is set to TRUE
246 IF FND_API.To_boolean(P_INIT_MSG_LIST)
247 THEN
248 FND_MSG_PUB.Initialize;
249 END IF;
250
251 --Check if the passed availability_id is valid.
252 OPEN c_availability_id_valid (l_availability_id);
253 FETCH c_availability_id_valid INTO l_num;
254 IF c_availability_id_valid%NOTFOUND THEN
255 IF c_availability_id_valid%ISOPEN THEN
256 CLOSE c_availability_id_valid;
257 END IF;
258 fnd_message.set_name ('JTF', 'JTF_RS_AVAIL_ID_INVALID');
259 FND_MSG_PUB.add;
260 raise fnd_api.g_exc_error;
261 END IF;
262 IF c_availability_id_valid%ISOPEN THEN
263 CLOSE c_availability_id_valid;
264 END IF;
265
266 --Check if the passed resource_id is valid.
267 OPEN c_resource_id_valid (l_resource_id);
268 FETCH c_resource_id_valid INTO l_num;
269 IF c_resource_id_valid%NOTFOUND THEN
270 IF c_resource_id_valid%ISOPEN THEN
271 CLOSE c_resource_id_valid;
272 END IF;
273 fnd_message.set_name ('JTF', 'JTF_RS_RES_ID_INVALID');
274 FND_MSG_PUB.add;
275 raise fnd_api.g_exc_error;
276 END IF;
277 IF c_resource_id_valid%ISOPEN THEN
278 CLOSE c_resource_id_valid;
279 END IF;
280
281
282 JTF_RS_RES_AVAILABILITY_PVT.UPDATE_RES_AVAILABILITY(
283 P_API_VERSION => l_api_version,
284 P_INIT_MSG_LIST => p_init_msg_list,
285 P_COMMIT => p_commit,
286 P_AVAILABILITY_ID => p_availability_id,
287 P_RESOURCE_ID => p_resource_id,
288 P_AVAILABLE_FLAG => p_available_flag,
289 P_REASON_CODE => p_reason_code,
290 P_START_DATE => p_start_date,
291 P_END_DATE => p_end_date,
292 P_MODE_OF_AVAILABILITY => p_mode_of_availability,
293 P_OBJECT_VERSION_NUM => l_object_version_number,
294 P_ATTRIBUTE1 => p_attribute1,
295 P_ATTRIBUTE2 => P_attribute2,
296 P_ATTRIBUTE3 => p_attribute3,
297 P_ATTRIBUTE4 => p_attribute4,
298 P_ATTRIBUTE5 => p_attribute5,
299 P_ATTRIBUTE6 => p_attribute6,
300 P_ATTRIBUTE7 => p_attribute7,
301 P_ATTRIBUTE8 => p_attribute8,
302 P_ATTRIBUTE9 => p_attribute9,
303 P_ATTRIBUTE10 => p_attribute10,
304 P_ATTRIBUTE11 => p_attribute11,
305 P_ATTRIBUTE12 => p_attribute12,
306 P_ATTRIBUTE13 => p_attribute13,
307 P_ATTRIBUTE14 => p_attribute14,
308 P_ATTRIBUTE15 => p_attribute15,
309 P_ATTRIBUTE_CATEGORY => p_attribute_category,
310 X_RETURN_STATUS => l_return_status,
311 X_MSG_COUNT => l_msg_count,
312 X_MSG_DATA => l_msg_data
313 );
314
315 X_RETURN_STATUS := l_return_status;
316 X_MSG_COUNT := l_msg_count;
317 X_MSG_DATA := l_msg_data;
318 P_OBJECT_VERSION_NUM := l_object_version_number;
319
320
321 IF fnd_api.to_boolean (p_commit)
322 THEN
323 COMMIT WORK;
324 END IF;
325
326 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
327
328 EXCEPTION
329 WHEN fnd_api.g_exc_error
330 THEN
331 ROLLBACK TO UPDATE_RES_AVAILABILITY_SP;
332 x_return_status := fnd_api.g_ret_sts_error;
333 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
334 WHEN fnd_api.g_exc_unexpected_error
335 THEN
336 ROLLBACK TO UPDATE_RES_AVAILABILITY_SP;
340 THEN
337 x_return_status := fnd_api.g_ret_sts_unexp_error;
338 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
339 WHEN OTHERS
341 ROLLBACK TO UPDATE_RES_AVAILABILITY_SP;
342 fnd_message.set_name ('JTF', 'JTF_RS_UNEXP_ERROR');
343 fnd_message.set_token('P_SQLCODE',SQLCODE);
344 fnd_message.set_token('P_SQLERRM',SQLERRM);
345 fnd_message.set_token('P_API_NAME',l_api_name);
346 FND_MSG_PUB.add;
347 x_return_status := fnd_api.g_ret_sts_unexp_error;
348 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
349 END update_res_availability;
350
351
352 /* Procedure to delete the resource availability */
353
354 PROCEDURE delete_res_availability
355 (P_API_VERSION IN NUMBER,
356 P_INIT_MSG_LIST IN VARCHAR2 DEFAULT FND_API.G_FALSE,
357 P_COMMIT IN VARCHAR2 DEFAULT FND_API.G_FALSE,
358 P_AVAILABILITY_ID IN JTF_RS_RES_AVAILABILITY.AVAILABILITY_ID%TYPE,
359 P_OBJECT_VERSION_NUM IN JTF_RS_RES_AVAILABILITY.OBJECT_VERSION_NUMBER%TYPE,
360 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
361 X_MSG_COUNT OUT NOCOPY NUMBER,
362 X_MSG_DATA OUT NOCOPY VARCHAR2
363 )IS
364
365 l_api_name CONSTANT VARCHAR2(30) := 'DELETE_RES_AVAILABILITY';
366 l_api_version CONSTANT NUMBER := 1.0;
367 l_availability_id NUMBER := p_availability_id;
368 l_return_status VARCHAR2(200);
369 l_msg_count NUMBER;
370 l_msg_data VARCHAR2(200);
371 l_num NUMBER;
372
373 -- Cursor to check if a valid availability_id is being passed.
374 CURSOR c_availability_id_valid (l_availability_id NUMBER) IS
375 SELECT 1
376 FROM jtf_rs_res_availability
377 WHERE availability_id = l_availability_id;
378
379 BEGIN
380 --Standard Start of API SAVEPOINT
381 SAVEPOINT DELETE_RES_AVAILABILITY_SP;
382
383 x_return_status := fnd_api.g_ret_sts_success;
384
385 --Standard Call to check API compatibility
386 IF NOT FND_API.Compatible_API_CALL(L_API_VERSION,P_API_VERSION,L_API_NAME,G_PKG_NAME)
387 THEN
388 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
389 END IF;
390
391 --Initialize the message List if P_INIT_MSG_LIST is set to TRUE
392 IF FND_API.To_boolean(P_INIT_MSG_LIST)
393 THEN
394 FND_MSG_PUB.Initialize;
395 END IF;
396
397 --Check if the passed availability_id is valid.
398 OPEN c_availability_id_valid (l_availability_id);
399 FETCH c_availability_id_valid INTO l_num;
400 IF c_availability_id_valid%NOTFOUND THEN
401 IF c_availability_id_valid%ISOPEN THEN
402 CLOSE c_availability_id_valid;
403 END IF;
404 fnd_message.set_name ('JTF', 'JTF_RS_AVAIL_ID_INVALID');
405 FND_MSG_PUB.add;
406 raise fnd_api.g_exc_error;
407 END IF;
408 IF c_availability_id_valid%ISOPEN THEN
409 CLOSE c_availability_id_valid;
410 END IF;
411
412
413 JTF_RS_RES_AVAILABILITY_PVT.DELETE_RES_AVAILABILITY
414 (P_API_VERSION => l_api_version,
415 P_INIT_MSG_LIST => p_init_msg_list,
416 P_COMMIT => p_commit,
417 P_AVAILABILITY_ID => p_availability_id,
418 P_OBJECT_VERSION_NUM => p_object_version_num,
419 X_RETURN_STATUS => l_return_status,
420 X_MSG_COUNT => l_msg_count,
421 X_MSG_DATA => l_msg_data
422 );
423
424 X_RETURN_STATUS := l_return_status;
425 X_MSG_COUNT := l_msg_count;
426 X_MSG_DATA := l_msg_data;
427
428 IF fnd_api.to_boolean (p_commit)
429 THEN
430 COMMIT WORK;
431 END IF;
432
433 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
434
435 EXCEPTION
436 WHEN fnd_api.g_exc_unexpected_error
437 THEN
438 ROLLBACK TO DELETE_RES_AVAILABILITY_SP;
439 x_return_status := fnd_api.g_ret_sts_unexp_error;
440 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
441 WHEN fnd_api.g_exc_error
442 THEN
443 ROLLBACK TO DELETE_RES_AVAILABILITY_SP;
444 x_return_status := fnd_api.g_ret_sts_error;
445 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
446 WHEN OTHERS
447 THEN
448 ROLLBACK TO DELETE_RES_AVAILABILITY_SP;
449 fnd_message.set_name ('JTF', 'JTF_RS_UNEXP_ERROR');
450 fnd_message.set_token('P_SQLCODE',SQLCODE);
451 fnd_message.set_token('P_SQLERRM',SQLERRM);
452 fnd_message.set_token('P_API_NAME',l_api_name);
453 FND_MSG_PUB.add;
454 x_return_status := fnd_api.g_ret_sts_unexp_error;
455 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
456
457 END delete_res_availability;
458
459 END jtf_rs_res_availability_pub;