[Home] [Help]
PACKAGE BODY: APPS.AMS_ACTRESOURCE_PVT
Source
1 PACKAGE BODY AMS_ActResource_PVT as
2 /*$Header: amsvrscb.pls 120.0 2005/05/31 14:50:17 appldev noship $*/
3
4 /*****************************************************************************************/
5 -- NAME
6 -- AMS_ActResource_PVT
7 --
8 -- HISTORY
9 -- 1/1/2000 rvaka CREATED
10 -- 02/20/2002 gmadana Rewritten the Package as we are doing Role-Resource relations
11 -- are we are using HZ_PARTIES instead of ams_jtf_rs_emp_v.
12 -- 05/28/2002 gmadana Added code in Validate_Act_Rsc_Record
13 -- 08/05/2002 gmadana Added valiadtions with event start date time
14 -- event end date time.
15 -- 08/18/2002 gmadana Bug # 2518686. Added time validations with session
16 -- 08/19/2002 gmadana Resources cannot be created/updated/deleted
17 -- for the event schedules which are cancelled/completed/
18 -- archived/on_hold.
19 -- 08/23/2002 gmadana Bug # 2518686
20 -- 04/28/2003 dbiswas Bug #2924115. Removed if then else statements for Validate_Act_Rsc_Record
21 -- 24-Mar-2005 sikalyan SQL Repository BugFix 4256877
22 /*****************************************************************************************/
23
24 G_PACKAGE_NAME CONSTANT VARCHAR2(30) :='AMS_ActResource_PVT';
25 G_FILE_NAME CONSTANT VARCHAR2(12) :='amsvrscb.pls';
26
27 -- Debug mode
28 g_debug boolean := FALSE;
29 g_debug boolean := TRUE;
30 --
31
32 AMS_DEBUG_HIGH_ON CONSTANT boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH);
33 AMS_DEBUG_LOW_ON CONSTANT boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
34 AMS_DEBUG_MEDIUM_ON CONSTANT boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_MEDIUM);
35
36 PROCEDURE Check_Resource_Booked (
37 p_act_Resource_rec IN act_Resource_rec_type,
38 p_validation_mode IN VARCHAR2 := JTF_PLSQL_API.g_create,
39 x_return_status OUT NOCOPY VARCHAR2
40 );
41
42 -- Procedure AND function declarations.
43 /*****************************************************************************************/
44 -- Start of Comments
45 --
46 -- NAME
47 -- Create_Act_Resource
48 --
49 -- PURPOSE
50 -- This procedure is to create a Resource record that satisfy caller needs
51 --
52 -- HISTORY
53 -- 02/20/2002 gmadana created
54 --
55 /*****************************************************************************************/
56
57 PROCEDURE Create_Act_Resource
58 ( p_api_version IN NUMBER,
59 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
60 p_commit IN VARCHAR2 := FND_API.G_FALSE,
61 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
62 x_return_status OUT NOCOPY VARCHAR2,
63 x_msg_count OUT NOCOPY NUMBER,
64 x_msg_data OUT NOCOPY VARCHAR2,
65 p_act_Resource_rec IN act_Resource_rec_type,
66 x_act_resource_id OUT NOCOPY NUMBER
67 ) IS
68
69 l_api_name CONSTANT VARCHAR2(30) := 'Create_Act_Resource';
70 l_api_version CONSTANT NUMBER := 1.0;
71 l_full_name CONSTANT VARCHAR2(60) := G_PACKAGE_NAME || '.' || l_api_name;
72 l_return_status VARCHAR2(1);
73 l_act_Resource_rec act_Resource_rec_type := p_act_Resource_rec;
74 l_date DATE;
75 l_startdate DATE;
76 l_enddate DATE;
77 l_strTime VARCHAR2(30);
78 l_strDate VARCHAR2(30);
79
80 CURSOR C_act_resource_id IS
81 SELECT ams_act_resources_s.NEXTVAL
82 FROM dual;
83
84 CURSOR c_get_object_date(id_in IN NUMBER, type_in IN VARCHAR2) is
85 SELECT start_date_time FROM ams_agendas_b
86 WHERE agenda_id = id_in
87 AND agenda_type = type_in;
88
89 CURSOR c_get_sys_stat_code(id_in IN NUMBER) is
90 SELECT system_status_code FROM ams_user_statuses_v
91 WHERE user_status_id = id_in;
92
93 BEGIN
94 -- Standard Start of API savepoint
95 SAVEPOINT Create_Act_Resource_PVT;
96
97 -- Standard call to check for call compatibility.
98 IF NOT FND_API.Compatible_API_Call ( l_api_version,
99 p_api_version,
100 l_api_name,
101 G_PACKAGE_NAME)
102 THEN
103 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
104 END IF;
105
106 -- Initialize message list IF p_init_msg_list is set to TRUE.
107 IF FND_API.to_Boolean( p_init_msg_list )
108 THEN
109 FND_MSG_PUB.initialize;
110 END IF;
111
112 -- Initialize API return status to success
113 x_return_status := FND_API.G_RET_STS_SUCCESS;
114
115 ----------------------- validate -----------------------
116 IF (AMS_DEBUG_HIGH_ON) THEN
117
118 AMS_Utility_PVT.debug_message(l_full_name ||': validate');
119 END IF;
120 if (l_act_Resource_rec.arc_act_resource_used_by = 'SESSION')
121 THEN
122 OPEN c_get_object_date(l_act_Resource_rec.act_resource_used_by_id, l_act_Resource_rec.arc_act_resource_used_by);
123 FETCH c_get_object_date into l_date;
124 CLOSE c_get_object_date;
125
126 l_strDate := TO_CHAR(l_date, 'DD-MON-RRRR');
127 l_strTime := TO_CHAR(l_act_Resource_rec.start_date_time, 'HH24:MI');
128 l_strDate := l_strDate || ' ' || l_strTime;
129 l_startdate := TO_DATE (l_strDate, 'DD-MM-YYYY HH24:MI');
130 l_act_Resource_rec.start_date_time := l_startdate;
131 IF (AMS_DEBUG_HIGH_ON) THEN
132
133 AMS_Utility_PVT.debug_message('l_start_date ' ||to_char(l_startdate,'DD-MON-RRRR HH24:MI'));
134 END IF;
135
136 l_strDate := NULL;
137 l_strTime := NULL;
138 l_strDate := TO_CHAR(l_date, 'DD-MON-RRRR');
139 l_strTime := TO_CHAR(l_act_Resource_rec.end_date_time, 'HH24:MI');
140 l_strDate := l_strDate ||' '|| l_strTime;
141 l_enddate := TO_DATE (l_strDate, 'DD-MM-YYYY HH24:MI');
142 l_act_Resource_rec.end_date_time := l_enddate;
143 IF (AMS_DEBUG_HIGH_ON) THEN
144
145 AMS_Utility_PVT.debug_message('l_end_date ' ||to_char(l_enddate,'DD-MON-RRRR HH24:MI'));
146 END IF;
147
148
149 IF (AMS_DEBUG_HIGH_ON) THEN
150
151
152
153
154
155 AMS_Utility_PVT.debug_message('l_start_date ' ||to_char(l_startdate,'DD-MON-RRRR HH24:MI'));
156
157
158 END IF;
159 END IF;
160
161 OPEN c_get_sys_stat_code(l_act_Resource_rec.user_status_id);
162 FETCH c_get_sys_stat_code into l_act_Resource_rec.system_status_code;
163 CLOSE c_get_sys_stat_code;
164
165 Validate_Act_Resource
166 ( p_api_version => 1.0
167 ,p_init_msg_list => p_init_msg_list
168 ,p_validation_level => p_validation_level
169 ,x_return_status => l_return_status
170 ,x_msg_count => x_msg_count
171 ,x_msg_data => x_msg_data
172 ,p_act_Resource_rec => l_act_Resource_rec
173 );
174
175 IF l_return_status = FND_API.G_RET_STS_ERROR
176 THEN
177 RAISE FND_API.G_EXC_ERROR;
178 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR
179 THEN
180 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
181 END IF;
182
183 ----------------------------create----------------------------
184 -- Get ID for activity delivery method FROM sequence.
185 OPEN c_act_resource_id;
186 FETCH c_act_resource_id INTO l_act_Resource_rec.activity_resource_id;
187 CLOSE c_act_resource_id;
188
189
190
191 INSERT INTO AMS_ACT_RESOURCES
192 (
193 activity_resource_id,
194 last_update_date,
195 last_updated_by,
196 creation_date,
197 created_by,
198 last_update_login,
199 object_version_number,
200 act_resource_used_by_id,
201 arc_act_resource_used_by,
202 resource_id,
203 role_cd,
204 user_status_id,
205 SYSTEM_STATUS_CODE,
206 start_date_time,
207 end_date_time,
208 description,
209 --TOP_LEVEL_PARENT_ID
210 --TOP_LEVEL_PARENT_TYPE
211 attribute_category,
212 attribute1,
213 attribute2,
214 attribute3,
215 attribute4,
216 attribute5,
217 attribute6,
218 attribute7,
219 attribute8,
220 attribute9,
221 attribute10,
222 attribute11,
223 attribute12,
224 attribute13,
225 attribute14,
226 attribute15
227 )
228 VALUES
229 (
230 l_act_resource_rec.activity_resource_id,
231 sysdate,
232 FND_GLOBAL.User_Id,
233 sysdate,
234 FND_GLOBAL.User_Id,
235 FND_GLOBAL.Conc_Login_Id,
236 1, -- object_version_number
237 l_act_Resource_rec.act_resource_used_by_id,
238 l_act_Resource_rec.arc_act_resource_used_by,
239 l_act_Resource_rec.resource_id,
240 l_act_Resource_rec.role_cd,
241 l_act_resource_rec.user_status_id,
242 l_act_resource_rec.system_status_code,
243 l_act_resource_rec.start_date_time,
244 l_act_resource_rec.end_date_time,
245 l_act_resource_rec.description,
246 --l_act_resource_rec.top_level_parent_id,
247 --l_act_resource_rec.top_level_parent_type,
248 l_act_Resource_rec.attribute_category,
249 l_act_Resource_rec.attribute1,
250 l_act_Resource_rec.attribute2,
251 l_act_Resource_rec.attribute3,
252 l_act_Resource_rec.attribute4,
253 l_act_Resource_rec.attribute5,
254 l_act_Resource_rec.attribute6,
255 l_act_Resource_rec.attribute7,
256 l_act_Resource_rec.attribute8,
257 l_act_Resource_rec.attribute9,
258 l_act_Resource_rec.attribute10,
259 l_act_Resource_rec.attribute11,
260 l_act_Resource_rec.attribute12,
261 l_act_Resource_rec.attribute13,
262 l_act_Resource_rec.attribute14,
263 l_act_Resource_rec.attribute15
264 );
265 -- set OUT value
266 x_act_resource_id := l_act_Resource_rec.activity_resource_id;
267
268 -- Standard check of p_commit.
269 IF FND_API.To_Boolean ( p_commit )
270 THEN
271 COMMIT WORK;
272 END IF;
273 -- Standard call to get message count AND IF count is 1, get message info.
274 FND_MSG_PUB.Count_AND_Get
275 ( p_count => x_msg_count,
276 p_data => x_msg_data,
277 p_encoded => FND_API.G_FALSE
278 );
279 EXCEPTION
280 WHEN FND_API.G_EXC_ERROR THEN
281 ROLLBACK TO Create_Act_Resource_PVT;
282 x_return_status := FND_API.G_RET_STS_ERROR ;
283 FND_MSG_PUB.Count_AND_Get
284 ( p_count => x_msg_count,
285 p_data => x_msg_data,
286 p_encoded => FND_API.G_FALSE
287 );
288 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
289 -- ROLLBACK TO Create_Act_Resource_PVT;
290 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
291 FND_MSG_PUB.Count_AND_Get
292 ( p_count => x_msg_count,
293 p_data => x_msg_data,
294 p_encoded => FND_API.G_FALSE
295 );
296 WHEN OTHERS THEN
297 ROLLBACK TO Create_Act_Resource_PVT;
298 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
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_PACKAGE_NAME,l_api_name);
302 END IF;
303
304 FND_MSG_PUB.Count_AND_Get
305 ( p_count => x_msg_count,
306 p_data => x_msg_data,
307 p_encoded => FND_API.G_FALSE
308 );
309
310 END Create_Act_Resource;
311
312 /*****************************************************************************************/
313 -- Start of Comments
314 --
315 -- NAME
316 -- Update_Act_Resource
317 --
318 -- PURPOSE
319 -- This procedure is to update a Resource record that satisfy caller needs
320 --
321 -- HISTORY
322 -- 02/20/2002 gmadana created
323 --
324 /*****************************************************************************************/
325
326 PROCEDURE Update_Act_Resource
327 ( p_api_version IN NUMBER,
328 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
329 p_commit IN VARCHAR2 := FND_API.G_FALSE,
330 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
331 x_return_status OUT NOCOPY VARCHAR2,
332 x_msg_count OUT NOCOPY NUMBER,
333 x_msg_data OUT NOCOPY VARCHAR2,
334 p_act_Resource_rec IN act_Resource_rec_type
335 ) IS
336
337 l_api_name CONSTANT VARCHAR2(30) := 'Update_Act_Resource';
338 l_api_version CONSTANT NUMBER := 1.0;
339 l_return_status VARCHAR2(1); -- Return value FROM procedures
340 l_act_Resource_rec act_Resource_rec_type;
341 l_date DATE;
342 l_startdate DATE;
343 l_enddate DATE;
344 l_strTime VARCHAR2(30);
345 l_strDate VARCHAR2(30);
346
347 CURSOR c_get_object_date(id_in IN NUMBER, type_in IN VARCHAR2) is
348 SELECT start_date_time FROM ams_agendas_b
349 WHERE agenda_id = id_in
350 AND agenda_type = type_in;
351
352 CURSOR c_get_sys_stat_code(id_in IN NUMBER) is
353 SELECT system_status_code FROM ams_user_statuses_v
354 WHERE user_status_id = id_in;
355
356 BEGIN
357 -- Standard Start of API savepoint
358 SAVEPOINT Update_Act_Resource_PVT;
359 -- Standard call to check for call compatibility.
360 IF NOT FND_API.Compatible_API_Call ( l_api_version,
361 p_api_version,
362 l_api_name,
363 G_PACKAGE_NAME)
364 THEN
365 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
366 END IF;
367
368 -- Initialize message list IF p_init_msg_list is set to TRUE.
369 IF FND_API.to_Boolean( p_init_msg_list ) THEN
370 FND_MSG_PUB.initialize;
371 END IF;
372
373 -- Initialize API return status to success
374 x_return_status := FND_API.G_RET_STS_SUCCESS;
375
376 complete_act_Resource_rec(
377 p_act_Resource_rec,
378 l_act_Resource_rec
379 );
380
381 if (l_act_Resource_rec.arc_act_resource_used_by = 'SESSION')
382 THEN
383 OPEN c_get_object_date(l_act_Resource_rec.act_resource_used_by_id, l_act_Resource_rec.arc_act_resource_used_by);
384 FETCH c_get_object_date into l_date;
385 CLOSE c_get_object_date;
386
387 l_strDate := TO_CHAR(l_date, 'dd-mon-rrrr');
388 l_strTime := TO_CHAR(l_act_Resource_rec.start_date_time, 'HH24:MI');
389 l_strDate := l_strDate || ' ' || l_strTime;
390 l_startdate := TO_DATE (l_strDate, 'DD-MM-YYYY HH24:MI');
391 l_act_Resource_rec.start_date_time := l_startdate;
392 IF (AMS_DEBUG_HIGH_ON) THEN
393
394 AMS_Utility_PVT.debug_message('l_start_date ' ||to_char(l_startdate,'DD-MON-RRRR HH24:MI'));
395 END IF;
396
397 l_strDate := NULL;
398 l_strTime := NULL;
399 l_strDate := TO_CHAR(l_date, 'dd-mon-rrrr');
400 l_strTime := TO_CHAR(l_act_Resource_rec.end_date_time, 'HH24:MI');
401 l_strDate := l_strDate ||' '|| l_strTime;
402 l_enddate := TO_DATE (l_strDate, 'DD-MM-YYYY HH24:MI');
403 l_act_Resource_rec.end_date_time := l_enddate;
404 IF (AMS_DEBUG_HIGH_ON) THEN
405
406 AMS_Utility_PVT.debug_message('l_end_date ' ||to_char(l_enddate,'DD-MON-RRRR HH24:MI'));
407 END IF;
408
409
410 IF (AMS_DEBUG_HIGH_ON) THEN
411
412
413
414
415
416 AMS_Utility_PVT.debug_message('l_start_date ' ||to_char(l_startdate,'DD-MON-RRRR HH24:MI'));
417
418
419 END IF;
420 end if;
421
422 OPEN c_get_sys_stat_code(l_act_Resource_rec.user_status_id);
423 FETCH c_get_sys_stat_code into l_act_Resource_rec.system_status_code;
424 CLOSE c_get_sys_stat_code;
425
426
427 IF (AMS_DEBUG_HIGH_ON) THEN
428
429
430
431
432
433 AMS_Utility_PVT.debug_message(l_api_name||': check items');
434
435
436 END IF;
437
438 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_item
439 THEN
440 Validate_Act_Resource_Items
441 ( p_act_Resource_rec => l_act_Resource_rec,
442 p_validation_mode => JTF_PLSQL_API.g_update,
443 x_return_status => l_return_status
444 );
445 -- If any errors happen abort API.
446 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR
447 THEN
448 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
449 ELSIF l_return_status = FND_API.G_RET_STS_ERROR
450 THEN
451 RAISE FND_API.G_EXC_ERROR;
452 END IF;
453 END IF;
454
455 -- If any errors happen abort API.
456 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR
457 THEN
458 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
459 ELSIF l_return_status = FND_API.G_RET_STS_ERROR
460 THEN
461 RAISE FND_API.G_EXC_ERROR;
462 END IF;
463
464 -------------- Perform the database operation UPDATE----------------------
465
466 update AMS_ACT_RESOURCES
467 set
468 last_update_date = sysdate
469 ,last_updated_by = FND_GLOBAL.User_Id
470 ,last_update_login = FND_GLOBAL.Conc_Login_Id
471 ,object_version_number = l_act_Resource_rec.object_version_number+1
472 ,act_resource_used_by_id = l_act_resource_rec.act_resource_used_by_id
473 ,arc_act_resource_used_by = l_act_resource_rec.arc_act_resource_used_by
474 ,resource_id = l_act_resource_rec.resource_id
475 ,role_cd = l_act_resource_rec.role_cd
476 ,user_status_id = l_act_resource_rec.user_status_id
477 ,system_status_code = l_act_resource_rec.system_status_code
478 ,start_date_time = l_act_resource_rec.start_date_time
479 ,end_date_time = l_act_resource_rec.end_date_time
480 ,description = l_act_resource_rec.description
481 --,top_level_parten_id = l_act_resource_rec.top_level_parten_id
482 --,top_level_parten_type = l_act_resource_rec.top_level_parten_type
483 ,attribute_category = l_act_Resource_rec.attribute_category
484 ,attribute1 = l_act_Resource_rec.attribute1
485 ,attribute2 = l_act_Resource_rec.attribute2
486 ,attribute3 = l_act_Resource_rec.attribute3
487 ,attribute4 = l_act_Resource_rec.attribute4
488 ,attribute5 = l_act_Resource_rec.attribute5
489 ,attribute6 = l_act_Resource_rec.attribute6
490 ,attribute7 = l_act_Resource_rec.attribute7
491 ,attribute8 = l_act_Resource_rec.attribute8
492 ,attribute9 = l_act_Resource_rec.attribute9
493 ,attribute10 = l_act_Resource_rec.attribute10
494 ,attribute11 = l_act_Resource_rec.attribute11
495 ,attribute12 = l_act_Resource_rec.attribute12
496 ,attribute13 = l_act_Resource_rec.attribute13
497 ,attribute14 = l_act_Resource_rec.attribute14
498 ,attribute15 = l_act_Resource_rec.attribute15
499 WHERE activity_resource_id = l_act_Resource_rec.activity_resource_id
500 AND object_version_number = l_act_Resource_rec.object_version_number;
501
502 IF (SQL%NOTFOUND)
503 THEN
504 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
505 THEN -- MMSG
506 FND_MESSAGE.set_name('AMS', 'AMS_API_RECORD_NOT_FOUND');
507 FND_MSG_PUB.Add;
508 END IF;
509 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
510 END IF;
511
512 -- Standard check of p_commit.
513 IF FND_API.To_Boolean ( p_commit )
514 THEN
515 COMMIT WORK;
516 END IF;
517 -- Standard call to get message count AND IF count is 1, get message info.
518 FND_MSG_PUB.Count_AND_Get
519 ( p_count => x_msg_count,
520 p_data => x_msg_data,
521 p_encoded => FND_API.G_FALSE
522 );
523 EXCEPTION
524 WHEN FND_API.G_EXC_ERROR THEN
525 ROLLBACK TO Update_Act_Resource_PVT;
526 x_return_status := FND_API.G_RET_STS_ERROR ;
527 FND_MSG_PUB.Count_AND_Get
528 ( p_count => x_msg_count,
529 p_data => x_msg_data,
530 p_encoded => FND_API.G_FALSE
531 );
532
533 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
534 ROLLBACK TO Update_Act_Resource_PVT;
535 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
536 FND_MSG_PUB.Count_AND_Get
537 ( p_count => x_msg_count,
538 p_data => x_msg_data,
539 p_encoded => FND_API.G_FALSE
540 );
541
542 WHEN OTHERS THEN
543 ROLLBACK TO Update_Act_Resource_PVT;
544 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
545 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
546 THEN
547 FND_MSG_PUB.Add_Exc_Msg( G_PACKAGE_NAME,l_api_name);
548 END IF;
549
550 FND_MSG_PUB.Count_AND_Get
551 ( p_count => x_msg_count,
552 p_data => x_msg_data,
553 p_encoded => FND_API.G_FALSE
554 );
555
556 END Update_Act_Resource;
557
558 /*****************************************************************************************/
559 -- Start of Comments
560 --
561 -- NAME
562 -- Delete_Act_Resource
563 --
564 -- PURPOSE
565 -- This procedure is to delete a resource record that satisfy caller needs
566 --
567 -- HISTORY
568 -- 02/20/2002 gmadana created
569 --
570 /*****************************************************************************************/
571
572 PROCEDURE Delete_Act_Resource
573 ( p_api_version IN NUMBER,
574 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
575 p_commit IN VARCHAR2 := FND_API.G_FALSE,
576 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
577 x_return_status OUT NOCOPY VARCHAR2,
578 x_msg_count OUT NOCOPY NUMBER,
579 x_msg_data OUT NOCOPY VARCHAR2,
580 p_act_Resource_id IN NUMBER,
581 p_object_version IN NUMBER
582 ) IS
583
584 l_api_name CONSTANT VARCHAR2(30) := 'Delete_Act_Resource';
585 l_api_version CONSTANT NUMBER := 1.0;
586 l_return_status VARCHAR2(1);
587 l_act_resource_id NUMBER := p_act_Resource_id;
588 l_role_relate_id NUMBER;
589
590
591 BEGIN
592 -- Standard Start of API savepoint
593 SAVEPOINT Delete_Act_Resource_PVT;
594
595 -- Standard call to check for call compatibility.
596 IF NOT FND_API.Compatible_API_Call ( l_api_version,
597 p_api_version,
598 l_api_name,
599 G_PACKAGE_NAME)
600 THEN
601 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
602 END IF;
603
604 -- Initialize message list IF p_init_msg_list is set to TRUE.
605 IF FND_API.to_Boolean( p_init_msg_list )
606 THEN
607 FND_MSG_PUB.initialize;
608 END IF;
609
610 -- Initialize API return status to success
611 x_return_status := FND_API.G_RET_STS_SUCCESS;
612
613 ------------------------------- Perform the database operation---------------
614
615 DELETE FROM ams_act_resources
616 WHERE ACTIVITY_RESOURCE_ID = p_act_Resource_id
617 AND p_object_version = p_object_version;
618
619 IF (SQL%NOTFOUND) THEN
620 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
621 FND_MESSAGE.set_name('AMS', 'AMS_API_RECORD_NOT_FOUND');
622 FND_MSG_PUB.add;
623 END IF;
624 RAISE FND_API.g_exc_error;
625 END IF;
626
627
628 -- Standard check of p_commit.
629 IF FND_API.To_Boolean ( p_commit )
630 THEN
631 COMMIT WORK;
632 END IF;
633
634 -- Standard call to get message count AND IF count is 1, get message info.
635 FND_MSG_PUB.Count_AND_Get
636 ( p_count => x_msg_count,
637 p_data => x_msg_data,
638 p_encoded => FND_API.G_FALSE
639 );
640 EXCEPTION
641 WHEN FND_API.G_EXC_ERROR THEN
642 ROLLBACK TO Delete_Act_Resource_PVT;
643 x_return_status := FND_API.G_RET_STS_ERROR ;
644 FND_MSG_PUB.Count_AND_Get
645 ( p_count => x_msg_count,
646 p_data => x_msg_data,
647 p_encoded => FND_API.G_FALSE
648 );
649 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
650 ROLLBACK TO Delete_Act_Resource_PVT;
651 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
652 FND_MSG_PUB.Count_AND_Get
653 ( p_count => x_msg_count,
654 p_data => x_msg_data,
655 p_encoded => FND_API.G_FALSE
656 );
657
658 WHEN OTHERS THEN
659 ROLLBACK TO Delete_Act_Resource_PVT;
660 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
661
662 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
663 THEN
664 FND_MSG_PUB.Add_Exc_Msg( G_PACKAGE_NAME,l_api_name);
665 END IF;
666
667 FND_MSG_PUB.Count_AND_Get
668 ( p_count => x_msg_count,
669 p_data => x_msg_data,
670 p_encoded => FND_API.G_FALSE
671 );
672
673 END Delete_Act_Resource;
674
675 /*****************************************************************************************/
676 -- Start of Comments
677 --
678 -- NAME
679 -- Lock_Act_Resource
680 --
681 -- PURPOSE
682 -- This procedure is to lock a delivery method record that satisfy caller needs
683 --
684 -- HISTORY
685 -- 02/20/2002 gmadana created
686 --
687 /*****************************************************************************************/
688
689 PROCEDURE Lock_Act_Resource
690 ( p_api_version IN NUMBER,
691 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
692 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
693 x_return_status OUT NOCOPY VARCHAR2,
694 x_msg_count OUT NOCOPY NUMBER,
695 x_msg_data OUT NOCOPY VARCHAR2,
696 p_act_resource_id IN NUMBER,
697 p_object_version IN NUMBER
698 ) IS
699 l_api_name CONSTANT VARCHAR2(30) := 'Lock_Act_Resource';
700 l_api_version CONSTANT NUMBER := 1.0;
701 l_return_status VARCHAR2(1);
702 l_act_resource_id NUMBER;
703
704 CURSOR c_act_resource IS
705 SELECT activity_resource_id
706 FROM AMS_ACT_RESOURCES
707 WHERE activity_resource_id = p_act_resource_id
708 AND object_version_number = p_object_version
709 FOR UPDATE of activity_resource_id NOWAIT;
710
711 BEGIN
712 -- Standard call to check for call compatibility.
713 IF NOT FND_API.Compatible_API_Call ( l_api_version,
714 p_api_version,
715 l_api_name,
716 G_PACKAGE_NAME)
717 THEN
718 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
719 END IF;
720
721 -- Initialize message list if p_init_msg_list is set to TRUE.
722 IF FND_API.to_Boolean( p_init_msg_list )
723 THEN
724 FND_MSG_PUB.initialize;
725 END IF;
726
727 -- Initialize API return status to success
728 x_return_status := FND_API.G_RET_STS_SUCCESS;
729
730 OPEN c_act_resource;
731 FETCH c_act_resource INTO l_act_resource_id;
732
733 IF (c_act_resource%NOTFOUND)
734 THEN
735 CLOSE c_act_resource;
736
737 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
738 THEN
739 FND_MESSAGE.Set_Name('AMS', 'AMS_API_RECORD_NOT_FOUND');
740 FND_MSG_PUB.Add;
741 END IF;
742
743 RAISE FND_API.G_EXC_ERROR;
744 END IF;
745 CLOSE c_act_resource;
746 --
747 -- END of API body.
748 --
749 -- Standard call to get message count AND IF count is 1, get message info.
750 FND_MSG_PUB.Count_AND_Get
751 ( p_count => x_msg_count,
752 p_data => x_msg_data,
753 p_encoded => FND_API.G_FALSE
754 );
755 EXCEPTION
756 WHEN FND_API.G_EXC_ERROR THEN
757 x_return_status := FND_API.G_RET_STS_ERROR ;
758 FND_MSG_PUB.Count_AND_Get
759 ( p_count => x_msg_count,
760 p_data => x_msg_data,
761 p_encoded => FND_API.G_FALSE
762 );
763 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
764 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
765 FND_MSG_PUB.Count_AND_Get
766 ( p_count => x_msg_count,
767 p_data => x_msg_data,
768 p_encoded => FND_API.G_FALSE
769 );
770 WHEN AMS_Utility_PVT.resource_locked THEN
771 x_return_status := FND_API.g_ret_sts_error;
772 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
773 FND_MESSAGE.set_name('AMS', 'AMS_API_RESOURCE_LOCKED');
774 FND_MSG_PUB.add;
775 END IF;
776
777 FND_MSG_PUB.Count_AND_Get
778 ( p_count => x_msg_count,
779 p_data => x_msg_data,
780 p_encoded => FND_API.G_FALSE
781 );
782 WHEN OTHERS THEN
783 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
784 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
785 THEN
786 FND_MSG_PUB.Add_Exc_Msg( G_PACKAGE_NAME,l_api_name);
787 END IF;
788
789 FND_MSG_PUB.Count_AND_Get
790 ( p_count => x_msg_count,
791 p_data => x_msg_data,
792 p_encoded => FND_API.G_FALSE
793 );
794 END Lock_Act_Resource;
795
796 /*****************************************************************************************/
797 -- Start of Comments
798 --
799 -- NAME
800 -- Validate_Act_Resource
801 --
802 -- PURPOSE
803 -- This procedure is to validate an activity resource record
804 --
805 -- HISTORY
806 -- 02/20/2002 gmadana created
807 --
808 /*****************************************************************************************/
809
810 PROCEDURE Validate_Act_Resource
811 ( p_api_version IN NUMBER,
812 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
813 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
814 x_return_status OUT NOCOPY VARCHAR2,
815 x_msg_count OUT NOCOPY NUMBER,
816 x_msg_data OUT NOCOPY VARCHAR2,
817 p_act_Resource_rec IN act_Resource_rec_type
818 ) IS
819
820 l_api_name CONSTANT VARCHAR2(30) := 'Validate_Act_Resource';
821 l_api_version CONSTANT NUMBER := 1.0;
822 l_full_name CONSTANT VARCHAR2(60) := G_PACKAGE_NAME || '.' || l_api_name;
823 l_return_status VARCHAR2(1);
824 l_act_Resource_rec act_Resource_rec_type := p_act_Resource_rec;
825 l_default_act_resource_rec act_Resource_rec_type;
826 l_act_resource_id NUMBER;
827
828
829 BEGIN
830 -- Standard call to check for call compatibility.
831 IF NOT FND_API.Compatible_API_Call ( l_api_version,
832 p_api_version,
833 l_api_name,
834 G_PACKAGE_NAME)
835 THEN
836 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
837 END IF;
838
839 -- Initialize message list if p_init_msg_list is set to TRUE.
840 IF FND_API.to_Boolean( p_init_msg_list ) THEN
841 FND_MSG_PUB.initialize;
842 END IF;
843
844 -- Initialize API return status to success
845 x_return_status := FND_API.G_RET_STS_SUCCESS;
846
847 IF (AMS_DEBUG_HIGH_ON) THEN
848
849
850
851 AMS_Utility_PVT.debug_message(l_full_name||': check items');
852
853 END IF;
854 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_item
855 THEN
856 Validate_Act_Resource_Items
857 ( p_act_Resource_rec => l_act_Resource_rec,
858 p_validation_mode => JTF_PLSQL_API.g_create,
859 x_return_status => l_return_status
860 );
861
862 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR
863 THEN
864 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
865 ELSIF l_return_status = FND_API.G_RET_STS_ERROR
866 THEN
867 RAISE FND_API.G_EXC_ERROR;
868 END IF;
869 END IF;
870
871 -- Perform cross attribute validation AND missing attribute checks. Record
872 -- level validation.
873 IF (AMS_DEBUG_HIGH_ON) THEN
874
875 AMS_Utility_PVT.debug_message(l_full_name||': check record level');
876 END IF;
877 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_record
878 THEN
879 Validate_Act_Rsc_Record(
880 p_act_Resource_rec => l_act_Resource_rec,
881 x_return_status => l_return_status
882 );
883
884 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
885 RAISE FND_API.G_EXC_ERROR;
886 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
887 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
888 END IF;
889
890 END IF;
891
892 -------------------- finish --------------------------
893 FND_MSG_PUB.count_and_get(
894 p_encoded => FND_API.g_false,
895 p_count => x_msg_count,
896 p_data => x_msg_data
897 );
898 EXCEPTION
899 WHEN FND_API.G_EXC_ERROR THEN
900 x_return_status := FND_API.G_RET_STS_ERROR ;
901 FND_MSG_PUB.Count_AND_Get
902 ( p_count => x_msg_count,
903 p_data => x_msg_data,
904 p_encoded => FND_API.G_FALSE
905 );
906 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
907 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
908 FND_MSG_PUB.Count_AND_Get
909 ( p_count => x_msg_count,
910 p_data => x_msg_data,
911 p_encoded => FND_API.G_FALSE
912 );
913 WHEN OTHERS THEN
914 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
915 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
916 THEN
917 FND_MSG_PUB.Add_Exc_Msg( G_PACKAGE_NAME,l_api_name);
918 END IF;
919
920 FND_MSG_PUB.Count_AND_Get
921 ( p_count => x_msg_count,
922 p_data => x_msg_data,
923 p_encoded => FND_API.G_FALSE
924 );
925 END Validate_Act_Resource;
926
927 /*****************************************************************************************/
928 -- PROCEDURE
929 -- check_Act_Rsc_uk_items
930 --
931 -- HISTORY
932 -- 02/20/2002 gmadana Created.
933 /*****************************************************************************************/
934 PROCEDURE check_Act_Rsc_uk_items(
935 p_act_Resource_rec IN act_Resource_rec_type,
936 p_validation_mode IN VARCHAR2 := JTF_PLSQL_API.g_create,
937 x_return_status OUT NOCOPY VARCHAR2
938 )
939 IS
940 l_valid_flag VARCHAR2(1);
941 l_dummy NUMBER;
942
943 BEGIN
944 x_return_status := FND_API.g_ret_sts_success;
945
946 -- For create_act_resource, when activity_resource_id is passed in, we need to
947 -- check if this activity_resource_id is unique.
948
949 IF p_validation_mode = JTF_PLSQL_API.g_create
950 AND p_act_Resource_rec.activity_resource_id IS NOT NULL
951 THEN
952 IF AMS_Utility_PVT.check_uniqueness(
953 'ams_act_resources_v',
954 'activity_resource_id = ' || p_act_Resource_rec.activity_resource_id
955 ) = FND_API.g_false
956 THEN
957 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
958 FND_MESSAGE.set_name('AMS', 'AMS_RES_DUPLICATE_ID');
959 FND_MSG_PUB.add;
960 END IF;
961 x_return_status := FND_API.g_ret_sts_error;
962 RETURN;
963 END IF;
964 END IF;
965
966
967
968
969 END check_Act_Rsc_uk_items;
970
971 /*****************************************************************************************/
972 -- PROCEDURE
973 -- check_Act_Rsc_req_items
974 --
975 -- HISTORY
976 -- 02/20/2002 gmadana Created.
977 /*****************************************************************************************/
978 PROCEDURE check_Act_Rsc_req_items(
979 p_act_Resource_rec IN act_Resource_rec_type,
980 x_return_status OUT NOCOPY VARCHAR2
981 )
982 IS
983
984 l_coordinator_id NUMBER;
985
986 CURSOR c_primary_coordinator(l_session_id IN NUMBER) IS
987 SELECT coordinator_id
988 FROM ams_agendas_v
989 WHERE agenda_id = l_session_id;
990
991 BEGIN
992
993 x_return_status := FND_API.g_ret_sts_success;
994
995
996 ------------------------ user_status_id --------------------------
997 IF (p_act_Resource_rec.user_status_id IS NULL OR
998 p_act_Resource_rec.user_status_id = FND_API.g_miss_num)
999 THEN
1000 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1001 FND_MESSAGE.set_name('AMS', 'AMS_EVO_NO_USER_STATUS_ID');
1002 FND_MSG_PUB.add;
1003 END IF;
1004 x_return_status := FND_API.g_ret_sts_error;
1005 END IF;
1006
1007 ------------------------ application_id --------------------------
1008 /* IF (p_act_Resource_rec.application_id IS NULL OR
1009 p_act_Resource_rec.application_id = FND_API.g_miss_num)
1010 THEN
1011 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1012 FND_MESSAGE.set_name('AMS', 'AMS_API_NO_APPLICATION_ID');
1013 FND_MSG_PUB.add;
1014 END IF;
1015 x_return_status := FND_API.g_ret_sts_error;
1016 RETURN;
1017 END IF; */
1018
1019 ------------------------ resource_id--------------------------
1020 IF (p_act_Resource_rec.resource_id IS NULL OR
1021 p_act_Resource_rec.resource_id = FND_API.g_miss_num)
1022 THEN
1023 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1024 FND_MESSAGE.set_name('AMS', 'AMS_ACT_RSC_NO_RESOURCE_ID');
1025 FND_MSG_PUB.add;
1026 END IF;
1027 x_return_status := FND_API.g_ret_sts_error;
1028 RETURN;
1029 ELSE
1030 /* The Session coordinator cannot be booked again at Resource level as
1031 coordinator.
1032 */
1033 OPEN c_primary_coordinator(p_act_Resource_rec.act_resource_used_by_id);
1034 FETCH c_primary_coordinator INTO l_coordinator_id;
1035 CLOSE c_primary_coordinator;
1036
1037 IF( l_coordinator_id = p_act_Resource_rec.resource_id AND p_act_Resource_rec.role_cd = 'COORDINATOR')
1038 THEN
1039 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1040 FND_MESSAGE.set_name('AMS', 'AMS_SAME_COORDINATOR_PRESENT');
1041 FND_MSG_PUB.add;
1042 END IF;
1043 x_return_status := FND_API.g_ret_sts_error;
1044 RETURN;
1045 END IF;
1046 END IF;
1047
1048 ------------------------ role_cd--------------------------
1049 IF (p_act_Resource_rec.role_cd IS NULL OR
1050 p_act_Resource_rec.role_cd = FND_API.g_miss_char)
1051 THEN
1052 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1053 FND_MESSAGE.set_name('AMS', 'AMS_NO_ROLE_CD');
1054 FND_MSG_PUB.add;
1055 END IF;
1056 x_return_status := FND_API.g_ret_sts_error;
1057 RETURN;
1058 END IF;
1059
1060 ------------ ACT_RESOURCE_USED_BY_ID -------------------------------------
1061 IF (p_act_Resource_rec.act_resource_used_by_id = FND_API.G_MISS_NUM OR
1062 p_act_Resource_rec.act_resource_used_by_id IS NULL)
1063 THEN
1064 IF FND_MSG_PUB.check_msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR)
1065 THEN
1066 FND_MESSAGE.set_name('AMS', 'AMS_ACT_RSC_NO_USEDBYID');
1067 FND_MSG_PUB.add;
1068 END IF;
1069 x_return_status := FND_API.G_RET_STS_ERROR;
1070 RETURN;
1071 END IF;
1072
1073 ------------ ACT_RESOURCE_USED_BY_ -------------------------------------
1074 IF (p_act_Resource_rec.ARC_ACT_RESOURCE_USED_BY = FND_API.G_MISS_CHAR OR
1075 p_act_Resource_rec.ARC_ACT_RESOURCE_USED_BY IS NULL)
1076 THEN
1077 -- missing required fields
1078 IF FND_MSG_PUB.check_msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR)
1079 THEN -- MMSG
1080 FND_MESSAGE.set_name('AMS', 'AMS_ACT_RSC_NO_USEDBY');
1081 FND_MSG_PUB.add;
1082 END IF;
1083 x_return_status := FND_API.G_RET_STS_ERROR;
1084 -- If any error happens abort API.
1085 RETURN;
1086 END IF;
1087
1088
1089 END check_Act_Rsc_req_items;
1090
1091
1092 /*****************************************************************************************/
1093 -- PROCEDURE
1094 -- check_Act_Rsc_fk_items
1095 --
1096 -- HISTORY
1097 -- 02/20/2002 gmadana Created.
1098 /*****************************************************************************************/
1099 PROCEDURE check_Act_Rsc_fk_items(
1100 p_act_Resource_rec IN act_Resource_rec_type,
1101 x_return_status OUT NOCOPY VARCHAR2
1102 )
1103 IS
1104 l_table_name VARCHAR2(30);
1105 l_pk_name VARCHAR2(30);
1106 l_pk_value VARCHAR2(30);
1107 l_pk_data_type NUMBER;
1108 l_additional_where_clause VARCHAR2(4000);
1109 l_where_clause VARCHAR2(80) := NULL;
1110
1111 BEGIN
1112 x_return_status := FND_API.g_ret_sts_success;
1113
1114
1115 /*--------------------- application_id ------------------------
1116 IF p_act_Resource_rec.application_id <> FND_API.g_miss_num AND
1117 p_act_Resource_rec.application_id is NOT NULL
1118 THEN
1119 IF AMS_Utility_PVT.check_fk_exists(
1120 'fnd_application',
1121 'application_id',
1122 p_act_Resource_rec.application_id
1123 ) = FND_API.g_false
1124 THEN
1125 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
1126 THEN
1127 FND_MESSAGE.set_name('AMS', 'AMS_EVO_BAD_APP_ID');
1128 FND_MSG_PUB.add;
1129 END IF;
1130 x_return_status := FND_API.g_ret_sts_error;
1131 RETURN;
1132 END IF;
1133 END IF; */
1134
1135 ----------------------- user_status_id ------------------------
1136 IF p_act_Resource_rec.user_status_id <> FND_API.g_miss_num
1137 AND p_act_Resource_rec.user_status_id IS NOT NULL THEN
1138 IF AMS_Utility_PVT.check_fk_exists(
1139 'ams_user_statuses_b',
1140 'user_status_id',
1141 p_act_Resource_rec.user_status_id
1142 ) = FND_API.g_false
1143 THEN
1144 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
1145 THEN
1146 FND_MESSAGE.set_name('AMS', 'AMS_EVO_BAD_USER_ST_ID');
1147 FND_MSG_PUB.add;
1148 END IF;
1149
1150 x_return_status := FND_API.g_ret_sts_error;
1151 RETURN;
1152 END IF;
1153 END IF;
1154
1155 --------------- RESOURCE_ID -------------------------
1156 IF p_act_Resource_rec.resource_id <> FND_API.g_miss_num
1157 THEN
1158 l_table_name := 'HZ_PARTIES';
1159 l_pk_name := 'PARTY_ID';
1160 l_pk_value := p_act_Resource_rec.resource_id;
1161 IF AMS_Utility_PVT.Check_FK_Exists (
1162 p_table_name => l_table_name
1163 ,p_pk_name => l_pk_name
1164 ,p_pk_value => l_pk_value
1165 ) = FND_API.G_FALSE
1166 THEN
1167 IF FND_MSG_PUB.check_msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR)
1168 THEN
1169 FND_MESSAGE.set_name('AMS', 'AMS_ACT_RSC_BAD_RESOURCE');
1170 FND_MSG_PUB.add;
1171 END IF;
1172 x_return_status := FND_API.G_RET_STS_ERROR;
1173 -- If any errors happen abort API/Procedure.
1174 RETURN;
1175 END IF; -- check_fk_exists
1176 END IF;
1177
1178 ---------- ACT_RESOURCE_USED_BY_ID-----------------------------
1179 IF p_act_resource_rec.ACT_RESOURCE_USED_BY_ID <> FND_API.g_miss_num
1180 THEN
1181 IF p_act_Resource_rec.arc_act_resource_used_by ='SESSION'
1182 THEN
1183 l_table_name := 'AMS_AGENDAS_B';
1184 l_pk_name := 'AGENDA_ID';
1185 ELSIF (p_act_Resource_rec.arc_act_resource_used_by = 'EVEO' OR p_act_Resource_rec.arc_act_resource_used_by = 'EONE')
1186 THEN
1187 l_table_name := 'AMS_EVENT_OFFERS_ALL_B';
1188 l_pk_name := 'EVENT_OFFER_ID';
1189 END IF;
1190
1191 l_pk_value := p_act_Resource_rec.act_resource_used_by_id;
1192 IF AMS_Utility_PVT.Check_FK_Exists (
1193 p_table_name => l_table_name
1194 ,p_pk_name => l_pk_name
1195 ,p_pk_value => l_pk_value
1196 ) = FND_API.G_FALSE
1197 THEN
1198 IF FND_MSG_PUB.check_msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR)
1199 THEN
1200 FND_MESSAGE.set_name('AMS', 'AMS_ACT_RSC_INVALID_REFERENCE');
1201 FND_MSG_PUB.add;
1202 END IF;
1203 x_return_status := FND_API.G_RET_STS_ERROR;
1204 -- If any errors happen abort API/Procedure.
1205 RETURN;
1206 END IF; -- check_fk_exists
1207 END IF;
1208
1209 END check_Act_Rsc_fk_items;
1210
1211 /*****************************************************************************************/
1212 -- PROCEDURE
1213 -- check_Act_Rsc_lookup_items
1214 --
1215 -- HISTORY
1216 -- 02/20/2002 gmadana Created.
1217 /*****************************************************************************************/
1218 PROCEDURE check_Act_Rsc_lookup_items(
1219 p_act_Resource_rec IN act_Resource_rec_type,
1220 x_return_status OUT NOCOPY VARCHAR2
1221 )
1222 IS
1223 BEGIN
1224
1225 x_return_status := FND_API.g_ret_sts_success;
1226
1227 ----------------------- role_code ------------------------
1228 IF p_act_Resource_rec.role_cd <> FND_API.g_miss_char
1229 AND p_act_Resource_rec.role_cd IS NOT NULL
1230 THEN
1231 IF AMS_Utility_PVT.check_lookup_exists(
1232 p_lookup_type => 'AMS_EVENT_ROLE',
1233 p_lookup_code => p_act_Resource_rec.role_cd
1234 ) = FND_API.g_false
1235 THEN
1236 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
1237 THEN
1238 FND_MESSAGE.set_name('AMS', 'AMS_BAD_ROLE_TYPE');
1239 FND_MSG_PUB.add;
1240 END IF;
1241 x_return_status := FND_API.g_ret_sts_error;
1242 RETURN;
1243 END IF;
1244 END IF;
1245
1246 ----------------------- status ------------------------
1247 IF p_act_Resource_rec.system_status_code <> FND_API.g_miss_char
1248 AND p_act_Resource_rec.system_status_code IS NOT NULL
1249 THEN
1250 IF AMS_Utility_PVT.check_lookup_exists(
1251 p_lookup_type => 'AMS_EVENT_AGENDA_STATUS',
1252 p_lookup_code => p_act_Resource_rec.system_status_code
1253 ) = FND_API.g_false
1254 THEN
1255 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
1256 THEN
1257 FND_MESSAGE.set_name('AMS', 'AMS_EVENT_BAD_USER_STATUS');
1258 FND_MSG_PUB.add;
1259 END IF;
1260 x_return_status := FND_API.g_ret_sts_error;
1261 RETURN;
1262 END IF;
1263 END IF;
1264
1265 ---------------- ARC_ACT_RESOURCE_USED_BY ------------
1266 IF p_act_Resource_rec.ARC_ACT_RESOURCE_USED_BY <> FND_API.g_miss_char THEN
1267 IF AMS_Utility_PVT.check_lookup_exists(
1268 p_lookup_type => 'AMS_SYS_ARC_QUALIFIER',
1269 p_lookup_code => p_act_Resource_rec.ARC_ACT_RESOURCE_USED_BY
1270 ) = FND_API.g_false
1271 THEN
1272 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
1273 THEN
1274 FND_MESSAGE.set_name('AMS', 'AMS_ACT_RSC_BAD_SYS_ARC');
1275 FND_MSG_PUB.add;
1276 END IF;
1277 x_return_status := FND_API.g_ret_sts_error;
1278 RETURN;
1279 END IF;
1280 END IF;
1281
1282
1283 END check_Act_Rsc_lookup_items;
1284
1285
1286 /*****************************************************************************************/
1287 -- PROCEDURE
1288 -- check_Act_Rsc_flag_items
1289 --
1290 -- HISTORY
1291 -- 02/20/2002 gmadana Created
1292 /*****************************************************************************************/
1293
1294 PROCEDURE check_Act_Rsc_flag_items(
1295 p_act_Resource_rec IN act_Resource_rec_type,
1296 x_return_status OUT NOCOPY VARCHAR2
1297 )
1298 IS
1299 BEGIN
1300
1301 x_return_status := FND_API.g_ret_sts_success;
1302
1303
1304 ----------------------- primary_flag ------------------------
1305 /* IF p_act_Resource_rec.primary_flag <> FND_API.g_miss_char
1306 AND p_act_Resource_rec.primary_flag IS NOT NULL
1307 THEN
1308 IF AMS_Utility_PVT.is_Y_or_N(p_act_Resource_rec.primary_flag) = FND_API.g_false
1309 THEN
1310 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
1311 THEN
1312 FND_MESSAGE.set_name('AMS', 'AMS_OBJ_BAD_PRIMARY_FLAG');
1313 FND_MSG_PUB.add;
1314 END IF;
1315 x_return_status := FND_API.g_ret_sts_error;
1316 RETURN;
1317 END IF;
1318 END IF; */
1319
1320
1321 END check_Act_Rsc_flag_items;
1322
1323 /*****************************************************************************************/
1324 --
1325 -- NAME
1326 -- Validate_Act_Resource_Items
1327 --
1328 -- PURPOSE
1329 -- This procedure is to validate Resource items
1330 --
1331 /*****************************************************************************************/
1332
1333 PROCEDURE Validate_Act_Resource_Items
1334 ( p_act_Resource_rec IN act_Resource_rec_type,
1335 p_validation_mode IN VARCHAR2 := JTF_PLSQL_API.g_create,
1336 x_return_status OUT NOCOPY VARCHAR2
1337 ) IS
1338
1339 l_table_name VARCHAR2(30);
1340 l_pk_name VARCHAR2(30);
1341 l_pk_value VARCHAR2(30);
1342 l_where_clause VARCHAR2(2000);
1343 l_start_date DATE;
1344 l_end_date DATE;
1345 l_resource_id NUMBER;
1346 l_res_start_date DATE;
1347 l_res_end_date DATE;
1348 l_parent_start_date DATE;
1349 l_parent_end_date DATE;
1350 l_event_start_date DATE;
1351 l_event_end_date DATE;
1352 l_count NUMBER;
1353
1354 /* Commented Out
1355 l_res_start_time DATE;
1356 l_res_end_time DATE;
1357 l_parent_start_time DATE;
1358 l_parent_end_time DATE;
1359 */
1360
1361 CURSOR get_session_date (id_in in NUMBER,type_in IN VARCHAR2)is
1362 SELECT start_date_time, end_date_time
1363 FROM AMS_agendas_b
1364 WHERE agenda_id = id_in
1365 AND agenda_TYPE = type_in;
1366
1367 CURSOR get_event_date (id_in in NUMBER,type_in IN VARCHAR2)is
1368 SELECT event_start_date_time, event_end_date_time
1369 FROM ams_event_offers_all_b
1370 WHERE event_offer_id = id_in
1371 AND event_object_type = type_in;
1372
1373 CURSOR c_event_status IS
1374 SELECT count(event_offer_id)
1375 FROM ams_event_offers_all_b
1376 WHERE system_status_code IN ('COMPLETED', 'CANCELLED', 'ON_HOLD','ARCHIVED','CLOSED')
1377 AND event_offer_id = p_act_Resource_rec.act_resource_used_by_id;
1378
1379 CURSOR c_parent_status IS
1380 SELECT count(event_offer_id)
1381 FROM ams_event_offers_all_b
1382 WHERE system_status_code IN ('COMPLETED', 'CANCELLED', 'ON_HOLD','ARCHIVED','CLOSED')
1383 AND event_offer_id = ( SELECT parent_id
1384 FROM ams_agendas_b
1385 WHERE agenda_id = ( SELECT parent_id
1386 FROM ams_agendas_b
1387 WHERE agenda_id = p_act_Resource_rec.act_resource_used_by_id));
1388
1389
1390 BEGIN
1391 -- Initialize API/Procedure return status to success
1392 x_return_status := FND_API.G_RET_STS_SUCCESS;
1393
1394 IF(p_act_Resource_rec.arc_act_resource_used_by = 'SESSION')
1395 THEN
1396 OPEN get_session_date(p_act_Resource_rec.act_resource_used_by_id,p_act_Resource_rec.arc_act_resource_used_by);
1397 FETCH get_session_date into l_parent_start_date, l_parent_end_date;
1398 CLOSE get_session_date;
1399
1400 OPEN c_parent_status;
1401 FETCH c_parent_status INTO l_count;
1402 CLOSE c_parent_status;
1403
1404 IF(l_count > 0)
1405 THEN
1406 IF Fnd_Msg_Pub.check_msg_level(Fnd_Msg_Pub.G_MSG_LVL_ERROR)
1407 THEN
1408 Fnd_Message.set_name('AMS', 'AMS_NO_RESOURCE');
1409 Fnd_Msg_Pub.ADD;
1410 END IF;
1411 RAISE FND_API.g_exc_error;
1412 END IF;
1413
1414 IF p_act_Resource_rec.start_date_time < l_parent_start_date
1415 THEN
1416 FND_MESSAGE.set_name('AMS', 'AMS_RES_STTIME_LS_SES_STTIME');
1417 FND_MSG_PUB.add;
1418 RAISE FND_API.g_exc_error;
1419 ELSIF p_act_Resource_rec.end_date_time > l_parent_end_date
1420 THEN
1421 FND_MESSAGE.set_name('AMS', 'AMS_RES_EDTIME_GT_SES_EDTIME');
1422 FND_MSG_PUB.add;
1423 RAISE FND_API.g_exc_error;
1424 END IF;
1425
1426 ELSIF(p_act_Resource_rec.arc_act_resource_used_by = 'EVEO'
1427 OR p_act_Resource_rec.arc_act_resource_used_by = 'EONE')
1428 THEN
1429 OPEN get_event_date(p_act_Resource_rec.act_resource_used_by_id,p_act_Resource_rec.arc_act_resource_used_by);
1430 FETCH get_event_date into l_parent_start_date, l_parent_end_date;
1431 CLOSE get_event_date;
1432
1433 OPEN c_event_status;
1434 FETCH c_event_status INTO l_count;
1435 CLOSE c_event_status;
1436
1437 IF(l_count > 0)
1438 THEN
1439 IF Fnd_Msg_Pub.check_msg_level(Fnd_Msg_Pub.G_MSG_LVL_ERROR)
1440 THEN
1441 Fnd_Message.set_name('AMS', 'AMS_NO_RESOURCE');
1442 Fnd_Msg_Pub.ADD;
1443 END IF;
1444 RAISE FND_API.g_exc_error;
1445 END IF;
1446
1447 l_res_start_date := TO_DATE(TO_CHAR(p_act_Resource_rec.start_date_time,'DD:MM:YYYY'),'DD:MM:YYYY');
1448 l_res_end_date := TO_DATE(TO_CHAR(p_act_Resource_rec.end_date_time,'DD:MM:YYYY'),'DD:MM:YYYY');
1449 l_start_date := TO_DATE(TO_CHAR(l_parent_start_date,'DD:MM:YYYY'),'DD:MM:YYYY');
1450 l_end_date := TO_DATE(TO_CHAR(l_parent_end_date,'DD:MM:YYYY'),'DD:MM:YYYY');
1451
1452 IF l_res_start_date < l_start_date
1453 THEN
1454 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
1455 THEN
1456 FND_MESSAGE.set_name('AMS', 'AMS_RES_SD_GT_PRNT_SD');
1457 FND_MSG_PUB.add;
1458 END IF;
1459 x_return_status := FND_API.g_ret_sts_error;
1460 RETURN;
1461 ELSIF l_res_start_date > l_end_date
1462 THEN
1463 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
1464 THEN
1465 FND_MESSAGE.set_name('AMS', 'AMS_RES_SD_ST_PRNT_ED');
1466 FND_MSG_PUB.add;
1467 END IF;
1468 x_return_status := FND_API.g_ret_sts_error;
1469 RETURN;
1470 ELSIF l_res_start_date = l_start_date
1471 THEN
1472 IF ( TO_CHAR(p_act_Resource_rec.start_date_time,'HH24:MI') <> '00:00'
1473 AND
1474 TO_CHAR(l_parent_start_date,'HH24:MI') <> '00:00'
1475 AND p_act_Resource_rec.start_date_time < l_parent_start_date )
1476 THEN
1477 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
1478 THEN
1479 FND_MESSAGE.set_name('AMS', 'AMS_RES_STTIME_LS_EVN_STTIME');
1480 FND_MSG_PUB.add;
1481 END IF;
1482 x_return_status := FND_API.g_ret_sts_error;
1483 RETURN;
1484 END IF;
1485 END IF;
1486
1487 IF l_res_end_date < l_start_date
1488 THEN
1489 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
1490 THEN
1491 FND_MESSAGE.set_name('AMS', 'AMS_RES_ED_GT_PRNT_SD');
1492 FND_MSG_PUB.add;
1493 END IF;
1494 x_return_status := FND_API.g_ret_sts_error;
1495 RETURN;
1496 ELSIF l_res_end_date > l_end_date
1497 THEN
1498 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1499 FND_MESSAGE.set_name('AMS', 'AMS_RES_ED_LT_PRNT_ED');
1500 FND_MSG_PUB.add;
1501 END IF;
1502 x_return_status := FND_API.g_ret_sts_error;
1503 RETURN;
1504 ELSIF l_res_end_date = l_end_date
1505 THEN
1506 IF (TO_CHAR(p_act_Resource_rec.end_date_time,'HH24:MI') <> '00:00'
1507 AND
1508 TO_CHAR(l_parent_end_date,'HH24:MI') <> '00:00'
1509 AND p_act_Resource_rec.end_date_time > l_parent_end_date)
1510 THEN
1511 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
1512 THEN
1513 FND_MESSAGE.set_name('AMS', 'AMS_RES_EDTIME_GT_EVN_EDTIME');
1514 FND_MSG_PUB.add;
1515 END IF;
1516 x_return_status := FND_API.g_ret_sts_error;
1517 RETURN;
1518 END IF;
1519 END IF;
1520
1521 END IF;
1522
1523
1524 /* Commented OUT NOCOPY
1525 l_res_start_time := TO_DATE(TO_CHAR(p_act_Resource_rec.start_date_time,'HH24:MI'),'HH24:MI');
1526 l_res_end_time := TO_DATE(TO_CHAR(p_act_Resource_rec.end_date_time,'HH24:MI'),'HH24:MI');
1527 l_parent_start_time := TO_DATE(TO_CHAR(l_event_start_date,'HH24:MI'),'HH24:MI');
1528 l_parent_end_time := TO_DATE(TO_CHAR(l_event_end_date,'HH24:MI'),'HH24:MI');
1529 IF (AMS_DEBUG_HIGH_ON) THEN
1530
1531 AMS_UTILITY_PVT.debug_message('Resource Start Time' || TO_CHAR(l_res_start_time,'DD-MM-YYYY HH24:MI') );
1532 END IF;
1533 IF (AMS_DEBUG_HIGH_ON) THEN
1534
1535 AMS_UTILITY_PVT.debug_message('Resource End Time' || TO_CHAR(l_res_end_time,'DD-MM-YYYY HH24:MI') );
1536 END IF;
1537 IF (AMS_DEBUG_HIGH_ON) THEN
1538
1539 AMS_UTILITY_PVT.debug_message('Parent Start Time' || TO_CHAR(l_parent_start_time,'DD-MM-YYYY HH24:MI') || TO_CHAR(l_event_start_date,'DD-MM-YYYY HH24:MI'));
1540 END IF;
1541 IF (AMS_DEBUG_HIGH_ON) THEN
1542
1543 AMS_UTILITY_PVT.debug_message('Parent End Time' || TO_CHAR(l_parent_End_time,'DD-MM-YYYY HH24:MI') || TO_CHAR(l_event_end_date,'DD-MM-YYYY HH24:MI'));
1544 END IF;
1545 */
1546
1547
1548 --------------------------------------Create mode--------------------------
1549 IF (AMS_DEBUG_HIGH_ON) THEN
1550
1551 AMS_UTILITY_PVT.debug_message('Checking uk_items');
1552 END IF;
1553 check_Act_Rsc_uk_items(
1554 p_act_Resource_rec => p_act_Resource_rec,
1555 p_validation_mode => p_validation_mode,
1556 x_return_status => x_return_status
1557 );
1558
1559 -------------------------- Create or Update Mode ----------------------------
1560 IF (AMS_DEBUG_HIGH_ON) THEN
1561
1562 AMS_UTILITY_PVT.debug_message('Checking req_items');
1563 END IF;
1564 check_Act_Rsc_req_items(
1565 p_act_Resource_rec => p_act_Resource_rec,
1566 x_return_status => x_return_status
1567 );
1568
1569 IF x_return_status <> FND_API.g_ret_sts_success THEN
1570 RETURN;
1571 END IF;
1572
1573 IF (AMS_DEBUG_HIGH_ON) THEN
1574
1575
1576
1577 AMS_UTILITY_PVT.debug_message('Checking fk_items');
1578
1579 END IF;
1580 check_Act_Rsc_fk_items(
1581 p_act_Resource_rec => p_act_Resource_rec,
1582 x_return_status => x_return_status
1583 );
1584 IF x_return_status <> FND_API.g_ret_sts_success THEN
1585 RETURN;
1586 END IF;
1587
1588 IF (AMS_DEBUG_HIGH_ON) THEN
1589
1590
1591
1592 AMS_UTILITY_PVT.debug_message('Checking lookup_items');
1593
1594 END IF;
1595 check_Act_Rsc_lookup_items(
1596 p_act_Resource_rec => p_act_Resource_rec,
1597 x_return_status => x_return_status
1598 );
1599 IF x_return_status <> FND_API.g_ret_sts_success THEN
1600 RETURN;
1601 END IF;
1602
1603 IF (AMS_DEBUG_HIGH_ON) THEN
1604
1605
1606
1607 AMS_UTILITY_PVT.debug_message('Checking flag_items');
1608
1609 END IF;
1610 check_Act_Rsc_flag_items(
1611 p_act_Resource_rec => p_act_Resource_rec,
1612 x_return_status => x_return_status
1613 );
1614 IF x_return_status <> FND_API.g_ret_sts_success THEN
1615 RETURN;
1616 END IF;
1617
1618
1619 /* The End Time has to be greater than Start Time */
1620 IF ( p_act_Resource_rec.start_date_time > p_act_Resource_rec.end_date_time)
1621 THEN
1622 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
1623 THEN
1624 FND_MESSAGE.set_name('AMS', 'AMS_EDTIME_LS_STTIME');
1625 FND_MSG_PUB.add;
1626 END IF;
1627 x_return_status := FND_API.g_ret_sts_error;
1628 RETURN;
1629 END IF;
1630
1631 IF (AMS_DEBUG_HIGH_ON) THEN
1632
1633
1634
1635 AMS_UTILITY_PVT.debug_message('Checking Resource is already booked');
1636
1637 END IF;
1638 check_Resource_booked(
1639 p_act_Resource_rec => p_act_Resource_rec,
1640 p_validation_mode => p_validation_mode,
1641 x_return_status => x_return_status
1642 );
1643 IF x_return_status <> FND_API.g_ret_sts_success THEN
1644 RETURN;
1645 END IF;
1646
1647
1648 END Validate_Act_Resource_Items;
1649
1650
1651 /*****************************************************************************************/
1652 -- Start of Comments
1653 --
1654 -- NAME
1655 -- Validate_Act_Rsc_Record
1656 --
1657 -- PURPOSE
1658 -- This procedure is to validate resource record
1659 --
1660 -- NOTES
1661 --
1662 /*****************************************************************************************/
1663
1664 PROCEDURE Validate_Act_Rsc_Record(
1665 p_act_Resource_rec IN act_Resource_rec_type,
1666 x_return_status OUT NOCOPY VARCHAR2
1667 ) IS
1668
1669 l_api_name CONSTANT VARCHAR2(30) := 'Validate_Act_Rsc_Record';
1670 l_api_version CONSTANT NUMBER := 1.0;
1671 l_return_status VARCHAR2(1);
1672 l_count NUMBER := 0;
1673
1674 BEGIN
1675 -- Standard call to check for call compatibility.
1676 IF NOT FND_API.Compatible_API_Call ( l_api_version,
1677 l_api_version,
1678 l_api_name,
1679 G_PACKAGE_NAME)
1680 THEN
1681 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1682 END IF;
1683
1684 /* dbiswas commented out the following section of if then else code for bug 2924115 on 28-Apr-2003
1685 IF (p_act_Resource_rec.role_cd = 'COORDINATOR' AND
1686 p_act_Resource_rec.system_status_code = 'CONFIRMED')
1687 THEN
1688 BEGIN
1689
1690 SELECT 1 into l_count
1691 FROM ams_act_resources_v
1692 WHERE act_resource_used_by_id = p_act_Resource_rec.act_resource_used_by_id
1693 AND arc_act_resource_used_by = p_act_Resource_rec.arc_act_resource_used_by
1694 AND resource_id = p_act_Resource_rec.resource_id
1695 AND system_status_code = 'CONFIRMED'
1696 -- AND system_status_code = p_act_Resource_rec.system_status_code
1697 AND
1698 (start_date_time BETWEEN p_act_Resource_rec.start_date_time AND p_act_Resource_rec.end_date_time
1699 OR
1700 end_date_time BETWEEN p_act_Resource_rec.start_date_time AND p_act_Resource_rec.end_date_time
1701 OR
1702 p_act_Resource_rec.start_date_time BETWEEN start_date_time AND end_date_time);
1703
1704 EXCEPTION
1705 WHEN NO_DATA_FOUND THEN
1706 l_count := 0;
1707
1708 END;
1709
1710 IF l_count > 0
1711 THEN
1712 IF FND_MSG_PUB.check_msg_level (FND_MSG_PUB.g_msg_lvl_error)
1713 THEN
1714 FND_MESSAGE.set_name ('AMS', 'AMS_SAME_COORDINATOR_PRESENT');
1715 FND_MSG_PUB.add;
1716 END IF;
1717 x_return_status := FND_API.G_RET_STS_ERROR;
1718
1719 ELSE
1720 x_return_status := FND_API.G_RET_STS_SUCCESS;
1721 END IF;
1722
1723 END IF;
1724 end update by dbiswas on Apr 28, 2003
1725 */
1726 x_return_status := FND_API.G_RET_STS_SUCCESS;
1727
1728
1729 END Validate_Act_Rsc_Record;
1730
1731 /*****************************************************************************************/
1732 -- PROCEDURE
1733 -- init_Act_Rsc_Record
1734 --
1735 -- HISTORY
1736 -- 02/20/2002 gmadana Create.
1737 /*****************************************************************************************/
1738 PROCEDURE init_Act_Rsc_Record(
1739 x_act_Resource_rec OUT NOCOPY act_Resource_rec_type
1740 )
1741 IS
1742 BEGIN
1743
1744 x_act_Resource_rec.act_resource_used_by_id := FND_API.g_miss_num;
1745 x_act_Resource_rec.arc_act_resource_used_by := FND_API.g_miss_char;
1746 x_act_Resource_rec.resource_id := FND_API.g_miss_num;
1747 x_act_Resource_rec.role_cd := FND_API.g_miss_char;
1748 x_act_Resource_rec.user_status_id := FND_API.g_miss_num;
1749 x_act_Resource_rec.system_status_code := FND_API.g_miss_char;
1750 x_act_Resource_rec.start_date_time := FND_API.g_miss_date;
1751 x_act_Resource_rec.end_date_time := FND_API.g_miss_date;
1752 x_act_Resource_rec.last_update_date := FND_API.g_miss_date;
1753 x_act_Resource_rec.last_updated_by := FND_API.g_miss_num;
1754 x_act_Resource_rec.creation_date := FND_API.g_miss_date;
1755 x_act_Resource_rec.created_by := FND_API.g_miss_num;
1756 x_act_Resource_rec.last_update_login := FND_API.g_miss_num;
1757 x_act_Resource_rec.object_version_number := FND_API.g_miss_num;
1758 --p_act_Resource_rec.application_id := FND_API.g_miss_num;
1759 x_act_Resource_rec.description := FND_API.g_miss_char;
1760 --x_act_Resource_rec.top_level_parten_id := FND_API.g_miss_num;
1761 --x_act_Resource_rec.top_level_parent_type := FND_API.g_miss_char;
1762 x_act_Resource_rec.attribute_category := FND_API.g_miss_char;
1763 x_act_Resource_rec.attribute1 := FND_API.g_miss_char;
1764 x_act_Resource_rec.attribute2 := FND_API.g_miss_char;
1765 x_act_Resource_rec.attribute3 := FND_API.g_miss_char;
1766 x_act_Resource_rec.attribute4 := FND_API.g_miss_char;
1767 x_act_Resource_rec.attribute5 := FND_API.g_miss_char;
1768 x_act_Resource_rec.attribute6 := FND_API.g_miss_char;
1769 x_act_Resource_rec.attribute7 := FND_API.g_miss_char;
1770 x_act_Resource_rec.attribute8 := FND_API.g_miss_char;
1771 x_act_Resource_rec.attribute9 := FND_API.g_miss_char;
1772 x_act_Resource_rec.attribute10 := FND_API.g_miss_char;
1773 x_act_Resource_rec.attribute11 := FND_API.g_miss_char;
1774 x_act_Resource_rec.attribute12 := FND_API.g_miss_char;
1775 x_act_Resource_rec.attribute13 := FND_API.g_miss_char;
1776 x_act_Resource_rec.attribute14 := FND_API.g_miss_char;
1777 x_act_Resource_rec.attribute15 := FND_API.g_miss_char;
1778
1779 END init_Act_Rsc_Record;
1780
1781
1782 PROCEDURE complete_act_Resource_rec(
1783 p_act_Resource_rec IN act_Resource_rec_type,
1784 x_act_Resource_rec OUT NOCOPY act_Resource_rec_type
1785 ) IS
1786 CURSOR c_resource IS
1787 SELECT *
1788 FROM ams_act_resources
1789 WHERE activity_resource_id = p_act_Resource_rec.activity_resource_id;
1790 l_act_Resource_rec c_resource%ROWTYPE;
1791
1792 BEGIN
1793 x_act_Resource_rec := p_act_Resource_rec;
1794
1795 OPEN c_resource;
1796 FETCH c_resource INTO l_act_Resource_rec;
1797 IF c_resource%NOTFOUND THEN
1798 CLOSE c_resource;
1799 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1800 FND_MESSAGE.set_name('AMS', 'AMS_API_RECORD_NOT_FOUND');
1801 FND_MSG_PUB.add;
1802 END IF;
1803 RAISE FND_API.g_exc_error;
1804 END IF;
1805 CLOSE c_resource;
1806
1807 /* IF p_act_Resource_rec.application_id = FND_API.g_miss_num THEN
1808 x_act_Resource_rec.application_id := l_act_Resource_rec.application_id;
1809 END IF; */
1810
1811 IF p_act_Resource_rec.created_by = FND_API.g_miss_num THEN
1812 x_act_Resource_rec.created_by := l_act_Resource_rec.created_by;
1813 END IF;
1814
1815 IF p_act_Resource_rec.creation_date = FND_API.g_miss_date THEN
1816 x_act_Resource_rec.creation_date := l_act_Resource_rec.creation_date;
1817 END IF;
1818
1819 IF p_act_Resource_rec.last_updated_by = FND_API.g_miss_num THEN
1820 x_act_Resource_rec.last_updated_by := l_act_Resource_rec.last_updated_by;
1821 END IF;
1822
1823 IF p_act_Resource_rec.last_update_date = FND_API.g_miss_date THEN
1824 x_act_Resource_rec.last_update_date := l_act_Resource_rec.last_update_date;
1825 END IF;
1826
1827
1828 IF p_act_Resource_rec.act_resource_used_by_id = FND_API.g_miss_num THEN
1829 x_act_Resource_rec.act_resource_used_by_id :=l_act_Resource_rec.act_resource_used_by_id;
1830 END IF;
1831
1832 IF p_act_Resource_rec.arc_act_resource_used_by = FND_API.g_miss_char THEN
1833 x_act_Resource_rec.arc_act_resource_used_by := l_act_Resource_rec.arc_act_resource_used_by;
1834 END IF;
1835
1836 IF p_act_Resource_rec.resource_id = FND_API.g_miss_num THEN
1837 x_act_Resource_rec.resource_id := l_act_Resource_rec.resource_id;
1838 END IF;
1839
1840 IF p_act_Resource_rec.role_cd = FND_API.g_miss_char THEN
1841 x_act_Resource_rec.role_cd := l_act_Resource_rec.role_cd;
1842 END IF;
1843
1844
1845 IF p_act_Resource_rec.user_status_id = FND_API.g_miss_num THEN
1846 x_act_Resource_rec.user_status_id := l_act_Resource_rec.user_status_id;
1847 END IF;
1848
1849 IF p_act_Resource_rec.system_status_code = FND_API.g_miss_char THEN
1850 x_act_Resource_rec.system_status_code := l_act_Resource_rec.system_status_code;
1851 END IF;
1852
1853 IF p_act_Resource_rec.start_date_time = FND_API.g_miss_date THEN
1854 x_act_Resource_rec.start_date_time := l_act_Resource_rec.start_date_time;
1855 END IF;
1856
1857 IF p_act_Resource_rec.end_date_time = FND_API.g_miss_date THEN
1858 x_act_Resource_rec.end_date_time := l_act_Resource_rec.end_date_time;
1859 END IF;
1860
1861
1862 IF p_act_Resource_rec.description = FND_API.g_miss_char THEN
1863 x_act_Resource_rec.description := l_act_Resource_rec.description;
1864 END IF;
1865
1866 /* IF p_act_Resource_rec.top_level_parten_id = FND_API.g_miss_num THEN
1867 x_act_Resource_rec.top_level_parten_id := l_act_Resource_rec.top_level_parten_id;
1868 END IF;
1869
1870 IF p_act_Resource_rec.top_level_parten_type = FND_API.g_miss_char THEN
1871 x_act_Resource_rec.top_level_parten_type := l_act_Resource_rec.top_level_parten_type;
1872 END IF;
1873 */
1874 IF p_act_Resource_rec.attribute_category = FND_API.g_miss_char THEN
1875 x_act_Resource_rec.attribute_category := l_act_Resource_rec.attribute_CATEGORY;
1876 END IF;
1877
1878 IF p_act_Resource_rec.attribute1 = FND_API.g_miss_char THEN
1879 x_act_Resource_rec.attribute1 := l_act_Resource_rec.attribute1;
1880 END IF;
1881
1882 IF p_act_Resource_rec.attribute2 = FND_API.g_miss_char THEN
1883 x_act_Resource_rec.attribute2 := l_act_Resource_rec.attribute2;
1884 END IF;
1885
1886 IF p_act_Resource_rec.attribute3 = FND_API.g_miss_char THEN
1887 x_act_Resource_rec.attribute3 := l_act_Resource_rec.attribute3;
1888 END IF;
1889
1890 IF p_act_Resource_rec.attribute4 = FND_API.g_miss_char THEN
1891 x_act_Resource_rec.attribute4 := l_act_Resource_rec.attribute4;
1892 END IF;
1893
1894 IF p_act_Resource_rec.attribute5 = FND_API.g_miss_char THEN
1895 x_act_Resource_rec.attribute5 := l_act_Resource_rec.attribute5;
1896 END IF;
1897
1898 IF p_act_Resource_rec.attribute6 = FND_API.g_miss_char THEN
1899 x_act_Resource_rec.attribute6 := l_act_Resource_rec.attribute6;
1900 END IF;
1901
1902 IF p_act_Resource_rec.attribute7 = FND_API.g_miss_char THEN
1903 x_act_Resource_rec.attribute7 := l_act_Resource_rec.attribute7;
1904 END IF;
1905
1906 IF p_act_Resource_rec.attribute8 = FND_API.g_miss_char THEN
1907 x_act_Resource_rec.attribute8 := l_act_Resource_rec.attribute8;
1908 END IF;
1909
1910 IF p_act_Resource_rec.attribute9 = FND_API.g_miss_char THEN
1911 x_act_Resource_rec.attribute9 := l_act_Resource_rec.attribute9;
1912 END IF;
1913
1914 IF p_act_Resource_rec.attribute10 = FND_API.g_miss_char THEN
1915 x_act_Resource_rec.attribute10 := l_act_Resource_rec.attribute10;
1916 END IF;
1917
1918 IF p_act_Resource_rec.attribute11 = FND_API.g_miss_char THEN
1919 x_act_Resource_rec.attribute11 := l_act_Resource_rec.attribute11;
1920 END IF;
1921
1922 IF p_act_Resource_rec.attribute11 = FND_API.g_miss_char THEN
1923 x_act_Resource_rec.attribute11 := l_act_Resource_rec.attribute11;
1924 END IF;
1925
1926 IF p_act_Resource_rec.attribute12 = FND_API.g_miss_char THEN
1927 x_act_Resource_rec.attribute12 := l_act_Resource_rec.attribute12;
1928 END IF;
1929
1930 IF p_act_Resource_rec.attribute13 = FND_API.g_miss_char THEN
1931 x_act_Resource_rec.attribute13 := l_act_Resource_rec.attribute13;
1932 END IF;
1933
1934 IF p_act_Resource_rec.attribute14 = FND_API.g_miss_char THEN
1935 x_act_Resource_rec.attribute14 := l_act_Resource_rec.attribute14;
1936 END IF;
1937
1938 IF p_act_Resource_rec.attribute15 = FND_API.g_miss_char THEN
1939 x_act_Resource_rec.attribute15 := l_act_Resource_rec.attribute15;
1940 END IF;
1941
1942 END complete_act_Resource_rec;
1943
1944
1945 /*****************************************************************************************/
1946 -- Check_Resource_Booked
1947 -- 02/22/2002 gmadana created.
1948 --
1949 -- This Procedure checks whether the requested resource is already booked.
1950 -- If we are adding resource for a Session, then we have to check whether
1951 -- that resource is added to any Session (all across) or to any Event other
1952 -- than the event for which that session is created.
1953 -- If we are updating resource for a Session, then we have to check whether
1954 -- that resource is added to any Session (all across except itself) or to
1955 -- any Event other than the event for which that session is created.
1956 -- If we are adding a resouce to EVEO/EONE, then you have to check whether that
1957 -- resource is attached to any Event (all across) or to any Sessions which are
1958 -- created for other Events than itself
1959 -- If we are updating a resouce to EVEO/EONE, then you have to check whether that
1960 -- resource is attached to any Event (all across except itself) or to any Sessions
1961 -- which are created for other Events than itself
1962 /*****************************************************************************************/
1963
1964 PROCEDURE Check_Resource_Booked (
1965 p_act_Resource_rec IN act_Resource_rec_Type,
1966 p_validation_mode IN VARCHAR2,
1967 x_return_status OUT NOCOPY VARCHAR2
1968 )
1969 IS
1970 l_start_date DATE;
1971 l_end_date DATE;
1972 l_track_id NUMBER;
1973 l_event_id NUMBER;
1974 l_session_id NUMBER;
1975 l_count NUMBER := 0;
1976 l_event_type VARCHAR2(15);
1977
1978
1979 CURSOR C_check_sessions_create(id_in IN NUMBER) IS
1980 SELECT count(*)
1981 FROM ams_act_resources
1982 WHERE resource_id = id_in
1983 AND arc_act_resource_used_by = 'SESSION'
1984 AND system_status_code = 'CONFIRMED'
1985 AND role_cd <> 'COORDINATOR'
1986 AND( p_act_Resource_rec.start_date_time BETWEEN start_date_time AND end_date_time
1987 OR p_act_Resource_rec.end_date_time BETWEEN start_date_time AND end_date_time
1988 OR start_date_time BETWEEN p_act_Resource_rec.start_date_time AND p_act_Resource_rec.end_date_time);
1989
1990 CURSOR C_check_sessions_update IS
1991 SELECT count(*)
1992 FROM ams_act_resources
1993 WHERE resource_id = p_act_Resource_rec.resource_id
1994 AND activity_resource_id <> p_act_Resource_rec.activity_resource_id
1995 AND arc_act_resource_used_by = 'SESSION'
1996 AND system_status_code = 'CONFIRMED'
1997 AND role_cd <> 'COORDINATOR'
1998 AND (p_act_Resource_rec.start_date_time BETWEEN start_date_time AND end_date_time
1999 OR p_act_Resource_rec.end_date_time BETWEEN start_date_time AND end_date_time
2000 OR start_date_time BETWEEN p_act_Resource_rec.start_date_time AND p_act_Resource_rec.end_date_time);
2001
2002 CURSOR C_check_events_session IS
2003 SELECT count(*)
2004 FROM ams_act_resources
2005 WHERE resource_id = p_act_Resource_rec.resource_id
2006 AND act_resource_used_by_id <> ( SELECT parent_id
2007 FROM ams_agendas_b
2008 WHERE agenda_id = (SELECT parent_id
2009 FROM ams_agendas_b
2010 WHERE agenda_id = p_act_Resource_rec.act_resource_used_by_id))
2011 AND arc_act_resource_used_by IN ('EVEO', 'EONE')
2012 AND system_status_code = 'CONFIRMED'
2013 AND role_cd <> 'COORDINATOR'
2014 AND (p_act_Resource_rec.start_date_time BETWEEN start_date_time AND end_date_time
2015 OR p_act_Resource_rec.end_date_time BETWEEN start_date_time AND end_date_time
2016 OR start_date_time BETWEEN p_act_Resource_rec.start_date_time AND p_act_Resource_rec.end_date_time);
2017
2018
2019 CURSOR C_check_events_create IS
2020 SELECT count(*)
2021 FROM ams_act_resources
2022 WHERE resource_id = p_act_Resource_rec.resource_id
2023 AND arc_act_resource_used_by IN ('EVEO', 'EONE')
2024 AND system_status_code = 'CONFIRMED'
2025 AND role_cd <> 'COORDINATOR'
2026 AND (p_act_Resource_rec.start_date_time BETWEEN start_date_time AND end_date_time
2027 OR p_act_Resource_rec.end_date_time BETWEEN start_date_time AND end_date_time
2028 OR start_date_time BETWEEN p_act_Resource_rec.start_date_time AND p_act_Resource_rec.end_date_time);
2029
2030 CURSOR C_check_events_update IS
2031 SELECT count(*)
2032 FROM ams_act_resources
2033 WHERE resource_id = p_act_Resource_rec.resource_id
2034 AND arc_act_resource_used_by IN ('EVEO', 'EONE')
2035 AND activity_resource_id <> p_act_Resource_rec.activity_resource_id
2036 AND system_status_code = 'CONFIRMED'
2037 AND role_cd <> 'COORDINATOR'
2038 AND (p_act_Resource_rec.start_date_time BETWEEN start_date_time AND end_date_time
2039 OR p_act_Resource_rec.end_date_time BETWEEN start_date_time AND end_date_time
2040 OR start_date_time BETWEEN p_act_Resource_rec.start_date_time AND p_act_Resource_rec.end_date_time);
2041
2042
2043 CURSOR C_check_other_sessions(id_in IN NUMBER) IS
2044 SELECT count(*)
2045 FROM ams_act_resources
2046 WHERE arc_act_resource_used_by = 'SESSION'
2047 AND system_status_code = 'CONFIRMED'
2048 AND role_cd <> 'COORDINATOR'
2049 AND act_resource_used_by_id IN ( SELECT agenda_id
2050 FROM ams_agendas_b
2051 WHERE parent_id <> p_act_Resource_rec.act_resource_used_by_id
2052 AND parent_type IN ('EVEO', 'EONE'))
2053 AND( p_act_Resource_rec.start_date_time BETWEEN start_date_time AND end_date_time
2054 OR p_act_Resource_rec.end_date_time BETWEEN start_date_time AND end_date_time
2055 OR start_date_time BETWEEN p_act_Resource_rec.start_date_time AND p_act_Resource_rec.end_date_time);
2056
2057
2058 BEGIN
2059
2060 --Initialize API return status to success
2061 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
2062
2063 /* If we are adding Corinator, we donot check for availability */
2064
2065 IF (p_act_Resource_rec.role_cd <> 'COORDINATOR' )
2066 THEN
2067 IF(p_act_Resource_rec.arc_act_resource_used_by = 'SESSION')
2068 THEN
2069 IF(p_validation_mode = Jtf_Plsql_Api.g_create)
2070 THEN
2071 /* checking across all the sessions for date overlap */
2072 OPEN C_check_sessions_create(p_act_Resource_rec.resource_id);
2073 FETCH C_check_sessions_create INTO l_count;
2074 CLOSE C_check_sessions_create;
2075 ELSIF(p_validation_mode = Jtf_Plsql_Api.g_update)
2076 THEN
2077 /* checking across all the sessions excluding itself for date overlap */
2078 OPEN C_check_sessions_update;
2079 FETCH C_check_sessions_update INTO l_count;
2080 CLOSE C_check_sessions_update;
2081 END IF;
2082
2083 IF (AMS_DEBUG_HIGH_ON) THEN
2084
2085
2086
2087 AMS_Utility_PVT.debug_message('The resource_id is ' || p_act_Resource_rec.resource_id);
2088
2089 END IF;
2090 IF (AMS_DEBUG_HIGH_ON) THEN
2091
2092 AMS_Utility_PVT.debug_message('The count for sessions/SESSION is ' || l_count);
2093 END IF;
2094 /* If l_count > 0 i.e. there are some existing sessions with date overlap.
2095 So Error out.
2096 */
2097 IF (l_count > 0)
2098 THEN
2099 x_return_status := Fnd_Api.g_ret_sts_error;
2100 GOTO ERROR;
2101 END IF;
2102
2103 /* If there are no sessions with date overlap, then check all
2104 the events excluding its parent event for date overlap
2105 */
2106 OPEN C_check_events_session;
2107 FETCH C_check_events_session INTO l_count;
2108 CLOSE C_check_events_session;
2109
2110 IF (AMS_DEBUG_HIGH_ON) THEN
2111
2112
2113
2114 AMS_Utility_PVT.debug_message('The count for events/SESSION is ' || l_count);
2115
2116 END IF;
2117 /* If l_count > 0 i.e. there are some existing events with date overlap.
2118 So Error out.
2119 */
2120 IF (l_count > 0)
2121 THEN
2122 x_return_status := Fnd_Api.g_ret_sts_error;
2123 GOTO ERROR;
2124 END IF;
2125
2126 ELSIF(p_act_Resource_rec.arc_act_resource_used_by = 'EVEO' OR
2127 p_act_Resource_rec.arc_act_resource_used_by = 'EONE')
2128 THEN
2129 IF(p_validation_mode = Jtf_Plsql_Api.g_create)
2130 THEN
2131 /* checking across all the events for date overlap */
2132 OPEN C_check_events_create;
2133 FETCH C_check_events_create INTO l_count;
2134 CLOSE C_check_events_create;
2135 ELSIF(p_validation_mode = Jtf_Plsql_Api.g_update)
2136 THEN
2137 /* checking across all the events except itself, for date overlap */
2138 OPEN C_check_events_update;
2139 FETCH C_check_events_update INTO l_count;
2140 CLOSE C_check_events_update;
2141 END IF;
2142
2143 IF (AMS_DEBUG_HIGH_ON) THEN
2144
2145
2146
2147 AMS_Utility_PVT.debug_message('The count for events/(EVEO/EONE) is ' || l_count);
2148
2149 END IF;
2150 /* If l_count > 0 i.e. there are some existing events with date overlap.
2151 So Error out.
2152 */
2153 IF (l_count > 0)
2154 THEN
2155 x_return_status := Fnd_Api.g_ret_sts_error;
2156 GOTO ERROR;
2157 END IF;
2158
2159 /* If there are no events with date overlap, then check all
2160 the sessions created for all events excluding those created for
2161 its parent event for date overlap.
2162 */
2163 OPEN C_check_other_sessions(l_session_id);
2164 FETCH C_check_other_sessions INTO l_count;
2165 CLOSE C_check_other_sessions;
2166
2167 IF (AMS_DEBUG_HIGH_ON) THEN
2168
2169
2170
2171 AMS_Utility_PVT.debug_message('The count for Sessions/(EVEO/EONE) is ' || l_count);
2172
2173 END IF;
2174 /* If l_count > 0 i.e. there are some existing sessions with date overlap.
2175 So Error out.
2176 */
2177 IF (l_count > 0)
2178 THEN
2179 x_return_status := Fnd_Api.g_ret_sts_error;
2180 GOTO ERROR;
2181 END IF;
2182
2183 END IF; --IF(p_act_Resource_rec.arc_act_resource_used_by = 'SESSION')
2184
2185 <<ERROR>>
2186 IF Fnd_Msg_Pub.check_msg_level(Fnd_Msg_Pub.g_msg_lvl_error)
2187 THEN
2188 IF( x_return_status = Fnd_Api.g_ret_sts_error) -- to avoid flow though
2189 THEN
2190 IF (p_act_Resource_rec.arc_act_resource_used_by = 'SESSION')
2191 THEN
2192 Fnd_Message.set_name('AMS', 'AMS_SESSION_RESOURCE_BOOKED');
2193 ELSE
2194 Fnd_Message.set_name('AMS', 'AMS_RESOURCE_BOOKED');
2195 END IF;
2196 Fnd_Msg_Pub.ADD;
2197 RETURN;
2198 END IF;
2199 END IF;
2200
2201 END IF; --(p_act_Resource_rec.role_cd <> 'COORDINATOR')
2202
2203 END Check_Resource_Booked;
2204
2205 END AMS_ActResource_PVT;