[Home] [Help]
PACKAGE BODY: APPS.JTF_RS_RES_AVAILABILITY_PVT
Source
1 PACKAGE BODY JTF_RS_RES_AVAILABILITY_PVT AS
2 /* $Header: jtfrsvzb.pls 120.2 2005/07/26 21:01:45 repuri ship $ */
3
4 /*****************************************************************************************
5 Its main procedures are as following:
6 Create resource availability
7 Update resource availability
8 Delete resource availability
9 Calls to these procedures will invoke procedures from JTF_RS_RES_AVAILABILITY_PUB
10 to do business validations and to do actual inserts, updates and deletes into tables.
11 ******************************************************************************************/
12 /* Package variables. */
13
14 G_PKG_NAME VARCHAR2(30) := 'JTF_RS_RES_AVAILABILITY_PVT';
15 G_NAME VARCHAR2(240);
16
17 /* Procedure to create the resource availability
18 based on input values passed by calling routines. */
19
20 PROCEDURE create_res_availability
21 (P_API_VERSION IN NUMBER,
22 P_INIT_MSG_LIST IN VARCHAR2,
23 P_COMMIT IN VARCHAR2,
24 P_RESOURCE_ID IN JTF_RS_RES_AVAILABILITY.RESOURCE_ID%TYPE,
25 P_AVAILABLE_FLAG IN JTF_RS_RES_AVAILABILITY.AVAILABLE_FLAG%TYPE,
26 P_REASON_CODE IN JTF_RS_RES_AVAILABILITY.REASON_CODE%TYPE,
27 P_START_DATE IN JTF_RS_RES_AVAILABILITY.START_DATE%TYPE,
28 P_END_DATE IN JTF_RS_RES_AVAILABILITY.END_DATE%TYPE,
29 P_MODE_OF_AVAILABILITY IN JTF_RS_RES_AVAILABILITY.MODE_OF_AVAILABILITY%TYPE,
30 P_ATTRIBUTE1 IN JTF_RS_RES_AVAILABILITY.ATTRIBUTE1%TYPE,
31 P_ATTRIBUTE2 IN JTF_RS_RES_AVAILABILITY.ATTRIBUTE2%TYPE,
32 P_ATTRIBUTE3 IN JTF_RS_RES_AVAILABILITY.ATTRIBUTE3%TYPE,
33 P_ATTRIBUTE4 IN JTF_RS_RES_AVAILABILITY.ATTRIBUTE4%TYPE,
34 P_ATTRIBUTE5 IN JTF_RS_RES_AVAILABILITY.ATTRIBUTE5%TYPE,
35 P_ATTRIBUTE6 IN JTF_RS_RES_AVAILABILITY.ATTRIBUTE6%TYPE,
36 P_ATTRIBUTE7 IN JTF_RS_RES_AVAILABILITY.ATTRIBUTE7%TYPE,
37 P_ATTRIBUTE8 IN JTF_RS_RES_AVAILABILITY.ATTRIBUTE8%TYPE,
38 P_ATTRIBUTE9 IN JTF_RS_RES_AVAILABILITY.ATTRIBUTE9%TYPE,
39 P_ATTRIBUTE10 IN JTF_RS_RES_AVAILABILITY.ATTRIBUTE10%TYPE,
40 P_ATTRIBUTE11 IN JTF_RS_RES_AVAILABILITY.ATTRIBUTE11%TYPE,
41 P_ATTRIBUTE12 IN JTF_RS_RES_AVAILABILITY.ATTRIBUTE12%TYPE,
42 P_ATTRIBUTE13 IN JTF_RS_RES_AVAILABILITY.ATTRIBUTE13%TYPE,
43 P_ATTRIBUTE14 IN JTF_RS_RES_AVAILABILITY.ATTRIBUTE14%TYPE,
44 P_ATTRIBUTE15 IN JTF_RS_RES_AVAILABILITY.ATTRIBUTE15%TYPE,
45 P_ATTRIBUTE_CATEGORY IN JTF_RS_RES_AVAILABILITY.ATTRIBUTE_CATEGORY%TYPE,
46 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
47 X_MSG_COUNT OUT NOCOPY NUMBER,
48 X_MSG_DATA OUT NOCOPY VARCHAR2,
49 X_AVAILABILITY_ID OUT NOCOPY JTF_RS_RES_AVAILABILITY.AVAILABILITY_ID%TYPE
50 )IS
51
52 l_api_name CONSTANT VARCHAR2(30) := 'CREATE_RES_AVAILABILITY';
53 l_api_version CONSTANT NUMBER := 1.0;
54 l_resource_id JTF_RS_RES_AVAILABILITY.RESOURCE_ID%TYPE := p_resource_id;
55 l_mode_of_availability JTF_RS_RES_AVAILABILITY.MODE_OF_AVAILABILITY%TYPE := P_MODE_OF_AVAILABILITY;
56 l_object_version_number NUMBER;
57 l_availability_id JTF_RS_RES_AVAILABILITY.AVAILABILITY_ID%TYPE;
58 l_return_status VARCHAR2(200);
59 l_msg_count NUMBER;
60 l_msg_data VARCHAR2(200);
61 l_rowid VARCHAR2(200);
62 l_num NUMBER;
63
64 -- uncommenting and modifying the cursor and the related code for fixing bug 4309007.
65 CURSOR c_dup_res_avail (l_resource_id IN NUMBER, l_mode_of_availability IN VARCHAR2)
66 IS
67 SELECT 1
68 FROM JTF_RS_RES_AVAILABILITY
69 WHERE resource_id = l_resource_id
70 AND mode_of_availability = l_mode_of_availability;
71
72 l_date Date;
73 l_user_id Number;
74 l_login_id Number;
75
76 BEGIN
77 --Standard Start of API SAVEPOINT
78 SAVEPOINT RESOURCE_AVAILABILITY_SP;
79
80 x_return_status := fnd_api.g_ret_sts_success;
81
82 --Standard Call to check API compatibility
83 IF NOT FND_API.Compatible_API_CALL(L_API_VERSION,P_API_VERSION,L_API_NAME,G_PKG_NAME)
84 THEN
85 RAISE FND_API.G_EXC_ERROR;
86 END IF;
87
88 --Initialize the message List if P_INIT_MSG_LIST is set to TRUE
89 IF FND_API.To_boolean(P_INIT_MSG_LIST)
90 THEN
91 FND_MSG_PUB.Initialize;
92 END IF;
93
94 --GET USER ID AND SYSDATE
95 l_date := sysdate;
96 l_user_id := NVL(FND_PROFILE.Value('USER_ID'), -1);
97 l_login_id := NVL(FND_PROFILE.Value('LOGIN_ID'), -1);
98
99 OPEN c_dup_res_avail (l_resource_id, l_mode_of_availability);
100 FETCH c_dup_res_avail into l_availability_id;
101 IF (c_dup_res_avail%NOTFOUND) THEN
102
103 SELECT jtf_rs_res_availability_s.nextval
104 INTO l_availability_id
105 FROM dual;
106
107 JTF_RS_RES_AVAILABILITY_PKG.INSERT_ROW(
108 X_ROWID => l_rowid,
109 X_AVAILABILITY_ID => l_availability_id,
110 X_RESOURCE_ID => l_resource_id,
111 X_AVAILABLE_FLAG => P_AVAILABLE_FLAG,
112 X_REASON_CODE => P_REASON_CODE,
113 X_START_DATE => P_START_DATE,
114 X_END_DATE => P_END_DATE,
115 X_MODE_OF_AVAILABILITY => P_MODE_OF_AVAILABILITY,
116 X_OBJECT_VERSION_NUMBER => l_object_version_number,
117 X_ATTRIBUTE1 => p_attribute1,
118 X_ATTRIBUTE2 => p_attribute2,
119 X_ATTRIBUTE3 => p_attribute3,
120 X_ATTRIBUTE4 => p_attribute4,
121 X_ATTRIBUTE5 => p_attribute5,
122 X_ATTRIBUTE6 => p_attribute6,
123 X_ATTRIBUTE7 => p_attribute7,
124 X_ATTRIBUTE8 => p_attribute8,
125 X_ATTRIBUTE9 => p_attribute9,
126 X_ATTRIBUTE10 => p_attribute10,
127 X_ATTRIBUTE11 => p_attribute11,
128 X_ATTRIBUTE12 => p_attribute12,
129 X_ATTRIBUTE13 => p_attribute13,
130 X_ATTRIBUTE14 => p_attribute14,
131 X_ATTRIBUTE15 => p_attribute15,
132 X_ATTRIBUTE_CATEGORY => p_attribute_category,
133 X_CREATION_DATE => sysdate,
134 X_CREATED_BY => l_user_id,
135 X_LAST_UPDATE_DATE => sysdate,
136 X_LAST_UPDATED_BY => l_user_id,
137 X_LAST_UPDATE_LOGIN => 0);
138
139 ELSE
140 IF c_dup_res_avail%ISOPEN THEN
141 CLOSE c_dup_res_avail;
142 END IF;
143 fnd_message.set_name ('JTF', 'JTF_RS_DUP_RES_AVAIL');
144 FND_MSG_PUB.add;
145 raise fnd_api.g_exc_error;
146 END IF;
147 IF c_dup_res_avail%ISOPEN THEN
148 CLOSE c_dup_res_avail;
149 END IF;
150
151 -- Return Availability ID
152 x_availability_id := l_availability_id;
153
154 --standard commit
155 IF fnd_api.to_boolean (p_commit)
156 THEN
157 COMMIT WORK;
158 END IF;
159
160 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
161
162 EXCEPTION
163 WHEN fnd_api.g_exc_unexpected_error
164 THEN
165 ROLLBACK TO RESOURCE_AVAILABILITY_SP;
166 x_return_status := fnd_api.g_ret_sts_unexp_error;
167 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
168 WHEN fnd_api.g_exc_error
169 THEN
170 ROLLBACK TO RESOURCE_AVAILABILITY_SP;
171 x_return_status := fnd_api.g_ret_sts_error;
172 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
173 WHEN OTHERS
174 THEN
175 ROLLBACK TO RESOURCE_AVAILABILITY_SP;
176 fnd_message.set_name ('JTF', 'JTF_RS_UNEXP_ERROR');
177 fnd_message.set_token('P_SQLCODE',SQLCODE);
178 fnd_message.set_token('P_SQLERRM',SQLERRM);
179 fnd_message.set_token('P_API_NAME', l_api_name);
180 FND_MSG_PUB.add;
181 x_return_status := fnd_api.g_ret_sts_unexp_error;
182 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
183
184 END create_res_availability;
185
186
187 /* Procedure to update resource availability
188 based on input values passed by calling routines. */
189
190 PROCEDURE update_res_availability
191 (P_API_VERSION IN NUMBER,
192 P_INIT_MSG_LIST IN VARCHAR2,
193 P_COMMIT IN VARCHAR2,
194 P_AVAILABILITY_ID IN JTF_RS_RES_AVAILABILITY.AVAILABILITY_ID%TYPE,
195 P_RESOURCE_ID IN JTF_RS_RES_AVAILABILITY.RESOURCE_ID%TYPE,
196 P_AVAILABLE_FLAG IN JTF_RS_RES_AVAILABILITY.AVAILABLE_FLAG%TYPE,
197 P_REASON_CODE IN JTF_RS_RES_AVAILABILITY.REASON_CODE%TYPE,
198 P_START_DATE IN JTF_RS_RES_AVAILABILITY.START_DATE%TYPE,
199 P_END_DATE IN JTF_RS_RES_AVAILABILITY.END_DATE%TYPE,
200 P_MODE_OF_AVAILABILITY IN JTF_RS_RES_AVAILABILITY.MODE_OF_AVAILABILITY%TYPE,
201 P_OBJECT_VERSION_NUM IN OUT NOCOPY JTF_RS_RES_AVAILABILITY.OBJECT_VERSION_NUMBER%TYPE,
202 P_ATTRIBUTE1 IN JTF_RS_RES_AVAILABILITY.ATTRIBUTE1%TYPE,
203 P_ATTRIBUTE2 IN JTF_RS_RES_AVAILABILITY.ATTRIBUTE2%TYPE,
204 P_ATTRIBUTE3 IN JTF_RS_RES_AVAILABILITY.ATTRIBUTE3%TYPE,
205 P_ATTRIBUTE4 IN JTF_RS_RES_AVAILABILITY.ATTRIBUTE4%TYPE,
206 P_ATTRIBUTE5 IN JTF_RS_RES_AVAILABILITY.ATTRIBUTE5%TYPE,
207 P_ATTRIBUTE6 IN JTF_RS_RES_AVAILABILITY.ATTRIBUTE6%TYPE,
208 P_ATTRIBUTE7 IN JTF_RS_RES_AVAILABILITY.ATTRIBUTE7%TYPE,
209 P_ATTRIBUTE8 IN JTF_RS_RES_AVAILABILITY.ATTRIBUTE8%TYPE,
210 P_ATTRIBUTE9 IN JTF_RS_RES_AVAILABILITY.ATTRIBUTE9%TYPE,
211 P_ATTRIBUTE10 IN JTF_RS_RES_AVAILABILITY.ATTRIBUTE10%TYPE,
212 P_ATTRIBUTE11 IN JTF_RS_RES_AVAILABILITY.ATTRIBUTE11%TYPE,
213 P_ATTRIBUTE12 IN JTF_RS_RES_AVAILABILITY.ATTRIBUTE12%TYPE,
214 P_ATTRIBUTE13 IN JTF_RS_RES_AVAILABILITY.ATTRIBUTE13%TYPE,
215 P_ATTRIBUTE14 IN JTF_RS_RES_AVAILABILITY.ATTRIBUTE14%TYPE,
216 P_ATTRIBUTE15 IN JTF_RS_RES_AVAILABILITY.ATTRIBUTE15%TYPE,
217 P_ATTRIBUTE_CATEGORY IN JTF_RS_RES_AVAILABILITY.ATTRIBUTE_CATEGORY%TYPE,
218 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
219 X_MSG_COUNT OUT NOCOPY NUMBER,
220 X_MSG_DATA OUT NOCOPY VARCHAR2
221 )IS
222 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_RES_AVAILABILITY';
223 l_api_version CONSTANT NUMBER := 1.0;
224
225 L_ATTRIBUTE1 JTF_RS_RES_AVAILABILITY.ATTRIBUTE1%TYPE;
226 L_ATTRIBUTE2 JTF_RS_RES_AVAILABILITY.ATTRIBUTE2%TYPE;
227 L_ATTRIBUTE3 JTF_RS_RES_AVAILABILITY.ATTRIBUTE3%TYPE;
228 L_ATTRIBUTE4 JTF_RS_RES_AVAILABILITY.ATTRIBUTE4%TYPE;
229 L_ATTRIBUTE5 JTF_RS_RES_AVAILABILITY.ATTRIBUTE5%TYPE;
230 L_ATTRIBUTE6 JTF_RS_RES_AVAILABILITY.ATTRIBUTE6%TYPE;
231 L_ATTRIBUTE7 JTF_RS_RES_AVAILABILITY.ATTRIBUTE7%TYPE;
232 L_ATTRIBUTE8 JTF_RS_RES_AVAILABILITY.ATTRIBUTE8%TYPE;
233 L_ATTRIBUTE9 JTF_RS_RES_AVAILABILITY.ATTRIBUTE9%TYPE;
234 L_ATTRIBUTE10 JTF_RS_RES_AVAILABILITY.ATTRIBUTE10%TYPE;
235 L_ATTRIBUTE11 JTF_RS_RES_AVAILABILITY.ATTRIBUTE11%TYPE;
236 L_ATTRIBUTE12 JTF_RS_RES_AVAILABILITY.ATTRIBUTE12%TYPE;
237 L_ATTRIBUTE13 JTF_RS_RES_AVAILABILITY.ATTRIBUTE13%TYPE;
238 L_ATTRIBUTE14 JTF_RS_RES_AVAILABILITY.ATTRIBUTE14%TYPE;
239 L_ATTRIBUTE15 JTF_RS_RES_AVAILABILITY.ATTRIBUTE15%TYPE;
240 L_ATTRIBUTE_CATEGORY JTF_RS_RES_AVAILABILITY.ATTRIBUTE_CATEGORY%TYPE;
241
242
243 CURSOR resource_cur(ll_availability_id JTF_RS_RES_AVAILABILITY.AVAILABILITY_ID%TYPE)
244 IS
245 SELECT AVAILABILITY_ID,
246 RESOURCE_ID,
247 AVAILABLE_FLAG,
248 REASON_CODE,
249 START_DATE,
250 END_DATE ,
251 MODE_OF_AVAILABILITY,
252 OBJECT_VERSION_NUMBER,
253 ATTRIBUTE1,
254 ATTRIBUTE2,
255 ATTRIBUTE3,
256 ATTRIBUTE4,
257 ATTRIBUTE5,
258 ATTRIBUTE6,
259 ATTRIBUTE7,
260 ATTRIBUTE8,
261 ATTRIBUTE9,
262 ATTRIBUTE10,
263 ATTRIBUTE11,
264 ATTRIBUTE12,
265 ATTRIBUTE13,
266 ATTRIBUTE14,
267 ATTRIBUTE15,
268 ATTRIBUTE_CATEGORY,
269 CREATED_BY,
270 CREATION_DATE,
271 LAST_UPDATED_BY,
272 LAST_UPDATE_DATE,
273 LAST_UPDATE_LOGIN
274 FROM jtf_rs_res_availability
275 WHERE availability_id = ll_availability_id;
276
277 resource_rec resource_cur%rowtype;
278
279 -- Cursor to check duplicates.
280 CURSOR c_dup_res_avail (l_resource_id IN NUMBER, l_mode_of_availability IN VARCHAR2) IS
281 SELECT 1
282 FROM jtf_rs_res_availability
283 WHERE resource_id = l_resource_id
284 AND mode_of_availability = l_mode_of_availability;
285
286 l_availability_id JTF_RS_RES_AVAILABILITY.availability_ID%TYPE := p_availability_id;
287 l_resource_id JTF_RS_RES_AVAILABILITY.RESOURCE_ID%TYPE := p_resource_id;
288 l_available_flag JTF_RS_RES_AVAILABILITY.AVAILABLE_FLAG%TYPE := p_available_flag;
289 l_reason_code JTF_RS_RES_AVAILABILITY.REASON_CODE%TYPE := p_reason_code;
290 l_start_date JTF_RS_RES_AVAILABILITY.START_DATE%TYPE := p_start_date;
291 l_end_date JTF_RS_RES_AVAILABILITY.END_DATE%TYPE := p_end_date;
292 l_mode_of_availability JTF_RS_RES_AVAILABILITY.MODE_OF_AVAILABILITY%TYPE := p_mode_of_availability;
293 l_object_version_number JTF_RS_RES_AVAILABILITY.OBJECT_VERSION_NUMBER%TYPE := P_OBJECT_VERSION_NUM;
294
295 l_return_status VARCHAR2(200);
296 l_msg_count NUMBER;
297 l_msg_data VARCHAR2(200);
298 l_rowid VARCHAR2(200);
299 l_num NUMBER;
300
301 l_date Date;
302 l_user_id Number;
303 l_login_id Number;
304
305 BEGIN
306 --Standard Start of API SAVEPOINT
307 SAVEPOINT RES_AVAILABILITY_SP;
308
309 x_return_status := fnd_api.g_ret_sts_success;
310
311 --Standard Call to check API compatibility
312 IF NOT FND_API.Compatible_API_CALL(L_API_VERSION,P_API_VERSION,L_API_NAME,G_PKG_NAME)
313 THEN
314 RAISE FND_API.G_EXC_ERROR;
315 END IF;
316
317 --Initialize the message List if P_INIT_MSG_LIST is set to TRUE
318 IF FND_API.To_boolean(P_INIT_MSG_LIST)
319 THEN
320 FND_MSG_PUB.Initialize;
321 END IF;
322
323
324 --GET USER ID AND SYSDATE
325 l_date := sysdate;
326 l_user_id := NVL(FND_PROFILE.Value('USER_ID'), -1);
327 l_login_id := NVL(FND_PROFILE.Value('LOGIN_ID'), -1);
328
329 OPEN resource_cur(l_availability_id);
330 FETCH resource_cur INTO resource_rec;
331
332 IF (resource_cur%found) THEN
333
334 IF (p_resource_id = FND_API.G_MISS_NUM)
335 THEN
336 l_resource_id := resource_rec.resource_id;
337 ELSE
338 l_resource_id := p_resource_id;
339 END IF;
340
341 IF (p_available_flag = FND_API.G_MISS_CHAR)
342 THEN
343 l_available_flag := resource_rec.available_flag;
344 ELSE
345 l_available_flag := p_available_flag;
346 END IF;
347
348 IF (p_reason_code = FND_API.G_MISS_CHAR)
349 THEN
350 l_reason_code := resource_rec.reason_code;
351 ELSE
352 l_reason_code := p_reason_code;
353 END IF;
354
355 IF (p_start_date = FND_API.G_MISS_DATE)
356 THEN
357 l_start_date := resource_rec.start_date;
358 ELSE
359 l_start_date := p_start_date;
360 END IF;
361
362 IF (p_end_date = FND_API.G_MISS_DATE)
363 THEN
364 l_end_date := resource_rec.end_date;
365 ELSE
366 l_end_date := p_end_date;
367 END IF;
368
369 IF (p_mode_of_availability = FND_API.G_MISS_CHAR)
370 THEN
371 l_mode_of_availability := resource_rec.mode_of_availability;
372 ELSE
373 l_mode_of_availability := p_mode_of_availability;
374 END IF;
375
376 IF(p_attribute1 = FND_API.G_MISS_CHAR)
377 THEN
378 l_attribute1 := resource_rec.attribute1;
379 ELSE
380 l_attribute1 := p_attribute1;
381 END IF;
382
383 IF(p_attribute2 = FND_API.G_MISS_CHAR)
384 THEN
385 l_attribute2 := resource_rec.attribute2;
386 ELSE
387 l_attribute2 := p_attribute2;
388 END IF;
389
390 IF(p_attribute3 = FND_API.G_MISS_CHAR)
391 THEN
392 l_attribute3 := resource_rec.attribute3;
393 ELSE
394 l_attribute3 := p_attribute3;
395 END IF;
396
397 IF(p_attribute4 = FND_API.G_MISS_CHAR)
398 THEN
399 l_attribute4 := resource_rec.attribute4;
400 ELSE
401 l_attribute4 := p_attribute4;
402 END IF;
403
404 IF(p_attribute5 = FND_API.G_MISS_CHAR)
405 THEN
406 l_attribute5 := resource_rec.attribute5;
407 ELSE
408 l_attribute5 := p_attribute5;
409 END IF;
410
411 IF(p_attribute6 = FND_API.G_MISS_CHAR)
412 THEN
413 l_attribute6 := resource_rec.attribute6;
414 ELSE
415 l_attribute6 := p_attribute6;
416 END IF;
417
418 IF(p_attribute7 = FND_API.G_MISS_CHAR)
419 THEN
420 l_attribute7 := resource_rec.attribute7;
421 ELSE
422 l_attribute7 := p_attribute7;
423 END IF;
424
425 IF(p_attribute8 = FND_API.G_MISS_CHAR)
426 THEN
427 l_attribute8 := resource_rec.attribute8;
428 ELSE
429 l_attribute8 := p_attribute8;
430 END IF;
431
432 IF(p_attribute9 = FND_API.G_MISS_CHAR)
433 THEN
434 l_attribute9 := resource_rec.attribute9;
435 ELSE
436 l_attribute9 := p_attribute9;
437 END IF;
438
439 IF(p_attribute10 = FND_API.G_MISS_CHAR)
440 THEN
441 l_attribute10 := resource_rec.attribute10;
442 ELSE
443 l_attribute10 := p_attribute10;
444 END IF;
445
446 IF(p_attribute11 = FND_API.G_MISS_CHAR)
447 THEN
448 l_attribute11 := resource_rec.attribute11;
449 ELSE
450 l_attribute11 := p_attribute11;
451 END IF;
452
453 IF(p_attribute12 = FND_API.G_MISS_CHAR)
454 THEN
455 l_attribute12 := resource_rec.attribute12;
456 ELSE
457 l_attribute12 := p_attribute12;
458 END IF;
459
460 IF(p_attribute13 = FND_API.G_MISS_CHAR)
461 THEN
462 l_attribute13 := resource_rec.attribute13;
463 ELSE
464 l_attribute13 := p_attribute13;
465 END IF;
466
467 IF(p_attribute14 = FND_API.G_MISS_CHAR)
468 THEN
469 l_attribute14 := resource_rec.attribute14;
470 ELSE
471 l_attribute14 := p_attribute14;
472 END IF;
473
474 IF(p_attribute15 = FND_API.G_MISS_CHAR)
475 THEN
476 l_attribute15 := resource_rec.attribute15;
477 ELSE
478 l_attribute15 := p_attribute15;
479 END IF;
480
481 IF(p_attribute_category = FND_API.G_MISS_CHAR)
482 THEN
483 l_attribute_category := resource_rec.attribute_category;
484 ELSE
485 l_attribute_category := p_attribute_category;
486 END IF;
487
488 OPEN c_dup_res_avail (l_resource_id, l_mode_of_availability);
489 FETCH c_dup_res_avail INTO l_num;
490 IF c_dup_res_avail%FOUND THEN
491 IF c_dup_res_avail%ISOPEN THEN
492 CLOSE c_dup_res_avail;
493 END IF;
494 fnd_message.set_name ('JTF', 'JTF_RS_DUP_RES_AVAIL');
495 FND_MSG_PUB.add;
496 raise fnd_api.g_exc_error;
497 END IF;
498 IF c_dup_res_avail%ISOPEN THEN
499 CLOSE c_dup_res_avail;
500 END IF;
501
502 BEGIN
503
504 jtf_rs_res_availability_pkg.lock_row(
505 x_availability_id => l_availability_id,
506 x_object_version_number => p_object_version_num
507 );
508
509 EXCEPTION
510
511 WHEN OTHERS THEN
512 x_return_status := fnd_api.g_ret_sts_error;
513 fnd_message.set_name('JTF', 'JTF_RS_ROW_LOCK_ERROR');
514 fnd_msg_pub.add;
515 RAISE fnd_api.g_exc_error;
516
517 END;
518
519
520 l_object_version_number := l_object_version_number +1;
521
522 jtf_rs_res_availability_pkg.update_row(
523 X_AVAILABILITY_ID => l_availability_id,
524 X_RESOURCE_ID => l_resource_id,
525 X_AVAILABLE_FLAG => l_AVAILABLE_FLAG,
526 X_REASON_CODE => l_REASON_CODE,
527 X_START_DATE => l_START_DATE,
528 X_END_DATE => l_END_DATE,
529 X_MODE_OF_AVAILABILITY => l_MODE_OF_AVAILABILITY,
530 X_OBJECT_VERSION_NUMBER => l_object_version_number,
531 X_ATTRIBUTE1 => l_attribute1,
532 X_ATTRIBUTE2 => l_attribute2,
533 X_ATTRIBUTE3 => l_attribute3,
534 X_ATTRIBUTE4 => l_attribute4,
535 X_ATTRIBUTE5 => l_attribute5,
536 X_ATTRIBUTE6 => l_attribute6,
537 X_ATTRIBUTE7 => l_attribute7,
538 X_ATTRIBUTE8 => l_attribute8,
539 X_ATTRIBUTE9 => l_attribute9,
540 X_ATTRIBUTE10 => l_attribute10,
541 X_ATTRIBUTE11 => l_attribute11,
542 X_ATTRIBUTE12 => l_attribute12,
543 X_ATTRIBUTE13 => l_attribute13,
544 X_ATTRIBUTE14 => l_attribute14,
545 X_ATTRIBUTE15 => l_attribute15,
546 X_ATTRIBUTE_CATEGORY => l_attribute_category,
547 X_LAST_UPDATE_DATE => l_date,
548 X_LAST_UPDATED_BY => l_user_id,
549 X_LAST_UPDATE_LOGIN => l_login_id);
550
551
552
553 P_OBJECT_VERSION_NUM := l_object_version_number;
554
555 ELSIF (resource_cur%notfound) THEN
556 x_return_status := fnd_api.g_ret_sts_error;
557 fnd_message.set_name ('JTF', 'JTF_RS_AVAILABILITY_ID_INVALID');
558 FND_MSG_PUB.add;
559 RAISE fnd_api.g_exc_error;
560
561 END IF;
562
563 CLOSE resource_cur;
564
565 IF fnd_api.to_boolean (p_commit)
566 THEN
567 COMMIT WORK;
568 END IF;
569
570 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
571
572 EXCEPTION
573 WHEN fnd_api.g_exc_error
574 THEN
575 ROLLBACK TO RES_AVAILABILITY_SP;
576 x_return_status := fnd_api.g_ret_sts_error;
577 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
578 WHEN fnd_api.g_exc_unexpected_error
579 THEN
580 ROLLBACK TO RES_AVAILABILITY_SP;
581 x_return_status := fnd_api.g_ret_sts_unexp_error;
582 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
583 WHEN OTHERS
584 THEN
585 ROLLBACK TO RES_AVAILABILITY_SP;
586 fnd_message.set_name ('JTF', 'JTF_RS_UNEXP_ERROR');
587 fnd_message.set_token('P_SQLCODE',SQLCODE);
588 fnd_message.set_token('P_SQLERRM',SQLERRM);
589 fnd_message.set_token('P_API_NAME',l_api_name);
590 FND_MSG_PUB.add;
591 x_return_status := fnd_api.g_ret_sts_unexp_error;
592 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
593 END update_res_availability;
594
595
596 /* Procedure to delete the resource availability */
597
598 PROCEDURE delete_res_availability
599 (P_API_VERSION IN NUMBER,
600 P_INIT_MSG_LIST IN VARCHAR2,
601 P_COMMIT IN VARCHAR2,
602 P_AVAILABILITY_ID IN JTF_RS_RES_AVAILABILITY.AVAILABILITY_ID%TYPE,
603 P_OBJECT_VERSION_NUM IN JTF_RS_RES_AVAILABILITY.OBJECT_VERSION_NUMBER%TYPE,
604 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
605 X_MSG_COUNT OUT NOCOPY NUMBER,
606 X_MSG_DATA OUT NOCOPY VARCHAR2
607 )IS
608
609
610 CURSOR chk_res_exist_cur(ll_availability_id JTF_RS_RES_AVAILABILITY.AVAILABILITY_ID%TYPE)
611 IS
612 SELECT resource_id
613 FROM JTF_RS_RES_AVAILABILITY
614 WHERE availability_id = ll_availability_id;
615
616 chk_res_exist_rec chk_res_exist_cur%rowtype;
617
618 l_availability_id JTF_RS_RES_AVAILABILITY.AVAILABILITY_ID%TYPE := p_availability_id;
619
620 l_api_name CONSTANT VARCHAR2(30) := 'DELETE_RES_AVAILABILITY';
621 l_api_version CONSTANT NUMBER := 1.0;
622
623 l_date Date;
624 l_user_id Number;
625 l_login_id Number;
626
627 l_return_status VARCHAR2(200);
628 l_msg_count NUMBER;
629 l_msg_data VARCHAR2(200);
630
631 BEGIN
632 --Standard Start of API SAVEPOINT
633 SAVEPOINT RES_AVAILABILITY_SP;
634
635 x_return_status := fnd_api.g_ret_sts_success;
636
637 --Standard Call to check API compatibility
638 IF NOT FND_API.Compatible_API_CALL(L_API_VERSION,P_API_VERSION,L_API_NAME,G_PKG_NAME)
639 THEN
640 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
641 END IF;
642
643 --Initialize the message List if P_INIT_MSG_LIST is set to TRUE
644 IF FND_API.To_boolean(P_INIT_MSG_LIST)
645 THEN
646 FND_MSG_PUB.Initialize;
647 END IF;
648
649 OPEN chk_res_exist_cur(l_availability_id);
650 FETCH chk_res_exist_cur INTO chk_res_exist_rec;
651 IF (chk_res_exist_cur%FOUND)
652 THEN
653
654 JTF_RS_RES_AVAILABILITY_PKG.DELETE_ROW(
655 X_AVAILABILITY_ID => l_availability_id);
656
657 ELSIF (chk_res_exist_cur%notfound) THEN
658 x_return_status := fnd_api.g_ret_sts_error;
659 fnd_message.set_name ('JTF', 'JTF_RS_AVAILABILITY_ID_INVALID');
660 FND_MSG_PUB.add;
661 RAISE fnd_api.g_exc_error;
662
663 END IF;
664
665 CLOSE chk_res_exist_cur;
666
667 IF fnd_api.to_boolean (p_commit)
668 THEN
669 COMMIT WORK;
670 END IF;
671
672 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
673
674 EXCEPTION
675 WHEN fnd_api.g_exc_unexpected_error
676 THEN
677 ROLLBACK TO RES_AVAILABILITY_SP;
678 x_return_status := fnd_api.g_ret_sts_unexp_error;
679 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
680 WHEN fnd_api.g_exc_error
681 THEN
682 ROLLBACK TO RES_AVAILABILITY_SP;
683 x_return_status := fnd_api.g_ret_sts_error;
684 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
685 WHEN OTHERS
686 THEN
687 ROLLBACK TO RES_AVAILABILITY_SP;
688 fnd_message.set_name ('JTF', 'JTF_RS_UNEXP_ERROR');
689 fnd_message.set_token('P_SQLCODE',SQLCODE);
690 fnd_message.set_token('P_SQLERRM',SQLERRM);
691 fnd_message.set_token('P_API_NAME',l_api_name);
692 FND_MSG_PUB.add;
693 x_return_status := fnd_api.g_ret_sts_unexp_error;
694 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
695
696 END delete_res_availability;
697
698 END JTF_RS_RES_AVAILABILITY_PVT;