[Home] [Help]
PACKAGE BODY: APPS.AMS_TEMPLATE_RES_PVT
Source
1 PACKAGE BODY AMS_template_res_PVT as
2 /* $Header: amsvptrb.pls 115.4 2002/11/18 18:23:48 abhola ship $ */
3 -- ===============================================================
4 -- Start of Comments
5 -- Package name
6 -- AMS_template_res_PVT
7 -- Purpose
8 --
9 -- History
10 --
11 -- NOTE
12 --
13 -- End of Comments
14 -- ===============================================================
15
16
17 G_PKG_NAME CONSTANT VARCHAR2(30):= 'AMS_template_res_PVT';
18 G_FILE_NAME CONSTANT VARCHAR2(12) := 'amsvptrb.pls';
19
20
21 -- Hint: Primary key needs to be returned.
22 AMS_DEBUG_HIGH_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH);
23 AMS_DEBUG_LOW_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
24 AMS_DEBUG_MEDIUM_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_MEDIUM);
25
26 PROCEDURE Create_template_res(
27 p_api_version_number IN NUMBER,
28 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
29 p_commit IN VARCHAR2 := FND_API.G_FALSE,
30 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
31
32 x_return_status OUT NOCOPY VARCHAR2,
33 x_msg_count OUT NOCOPY NUMBER,
34 x_msg_data OUT NOCOPY VARCHAR2,
35
36 p_template_res_rec IN template_res_rec_type := g_miss_template_res_rec,
37 x_templ_responsibility_id OUT NOCOPY NUMBER
38 )
39
40 IS
41 L_API_NAME CONSTANT VARCHAR2(30) := 'Create_template_res';
42 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
43 l_return_status_full VARCHAR2(1);
44 l_object_version_number NUMBER := 1;
45 l_org_id NUMBER := FND_API.G_MISS_NUM;
46 l_TEMPL_RESPONSIBILITY_ID NUMBER;
47 l_dummy NUMBER;
48
49 CURSOR c_id IS
50 SELECT AMS_TEMPL_RESPONSIBILITY_s.NEXTVAL
51 FROM dual;
52
53 CURSOR c_id_exists (l_id IN NUMBER) IS
54 SELECT 1
55 FROM AMS_TEMPL_RESPONSIBILITY
56 WHERE TEMPL_RESPONSIBILITY_ID = l_id;
57
58 BEGIN
59 -- Standard Start of API savepoint
60 SAVEPOINT CREATE_template_res_PVT;
61
62 -- Standard call to check for call compatibility.
63 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
64 p_api_version_number,
65 l_api_name,
66 G_PKG_NAME)
67 THEN
68 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
69 END IF;
70
71 -- Initialize message list if p_init_msg_list is set to TRUE.
72 IF FND_API.to_Boolean( p_init_msg_list )
73 THEN
74 FND_MSG_PUB.initialize;
75 END IF;
76
77 -- Debug Message
78 IF (AMS_DEBUG_HIGH_ON) THEN
79
80 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
81 END IF;
82
83
84 -- Initialize API return status to SUCCESS
85 x_return_status := FND_API.G_RET_STS_SUCCESS;
86
87 -- Local variable initialization
88
89 IF p_template_res_rec.TEMPL_RESPONSIBILITY_ID IS NULL OR p_template_res_rec.TEMPL_RESPONSIBILITY_ID = FND_API.g_miss_num THEN
90 LOOP
91 l_dummy := NULL;
92 OPEN c_id;
93 FETCH c_id INTO l_TEMPL_RESPONSIBILITY_ID;
94 CLOSE c_id;
95
96 OPEN c_id_exists(l_TEMPL_RESPONSIBILITY_ID);
97 FETCH c_id_exists INTO l_dummy;
98 CLOSE c_id_exists;
99 EXIT WHEN l_dummy IS NULL;
100 END LOOP;
101 END IF;
102
103 -- =========================================================================
104 -- Validate Environment
105 -- =========================================================================
106
107 IF FND_GLOBAL.User_Id IS NULL
108 THEN
109 AMS_Utility_PVT.Error_Message(p_message_name => 'USER_PROFILE_MISSING');
110 RAISE FND_API.G_EXC_ERROR;
111 END IF;
112
113 IF ( P_validation_level >= FND_API.G_VALID_LEVEL_FULL)
114 THEN
115 -- Debug message
116 IF (AMS_DEBUG_HIGH_ON) THEN
117
118 AMS_UTILITY_PVT.debug_message('Private API: Validate_template_res');
119 END IF;
120
121 -- Invoke validation procedures
122 Validate_template_res(
123 p_api_version_number => 1.0,
124 p_init_msg_list => FND_API.G_FALSE,
125 p_validation_level => p_validation_level,
126 p_validation_mode => JTF_PLSQL_API.g_create,
127 p_template_res_rec => p_template_res_rec,
128 x_return_status => x_return_status,
129 x_msg_count => x_msg_count,
130 x_msg_data => x_msg_data);
131 END IF;
132
133 IF x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
134 RAISE FND_API.G_EXC_ERROR;
135 END IF;
136
137 INSERT INTO AMS_TEMPL_RESPONSIBILITY(
138 templ_responsibility_id,
139 template_id,
140 responsibility_id,
141 last_update_date,
142 last_updated_by,
143 creation_date,
144 created_by,
145 object_version_number,
146 last_update_login
147 ) VALUES (
148 l_templ_responsibility_id,
149 DECODE( p_template_res_rec.template_id, FND_API.g_miss_num, NULL, p_template_res_rec.template_id),
150 DECODE( p_template_res_rec.responsibility_id, FND_API.g_miss_num, NULL, p_template_res_rec.responsibility_id),
151 sysdate,
152 fnd_global.user_id,
153 sysdate,
154 fnd_global.user_id,
155 1,
156 FND_GLOBAL.CONC_LOGIN_ID
157 );
158
159 x_templ_responsibility_id := l_templ_responsibility_id;
160 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
161 RAISE FND_API.G_EXC_ERROR;
162 END IF;
163 --
164 -- End of API body
165 --
166
167 -- Standard check for p_commit
168 IF FND_API.to_Boolean( p_commit )
169 THEN
170 COMMIT WORK;
171 END IF;
172
173
174 -- Debug Message
175 IF (AMS_DEBUG_HIGH_ON) THEN
176
177 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'end');
178 END IF;
179
180 -- Standard call to get message count and if count is 1, get message info.
181 FND_MSG_PUB.Count_And_Get
182 (p_count => x_msg_count,
183 p_data => x_msg_data
184 );
185 EXCEPTION
186
187 WHEN AMS_Utility_PVT.resource_locked THEN
188 x_return_status := FND_API.g_ret_sts_error;
189 AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_API_RESOURCE_LOCKED');
190
191 WHEN FND_API.G_EXC_ERROR THEN
192 ROLLBACK TO CREATE_template_res_PVT;
193 x_return_status := FND_API.G_RET_STS_ERROR;
194 -- Standard call to get message count and if count=1, get the message
195 FND_MSG_PUB.Count_And_Get (
196 p_encoded => FND_API.G_FALSE,
197 p_count => x_msg_count,
198 p_data => x_msg_data
199 );
200
201 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
202 ROLLBACK TO CREATE_template_res_PVT;
203 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
204 -- Standard call to get message count and if count=1, get the message
205 FND_MSG_PUB.Count_And_Get (
206 p_encoded => FND_API.G_FALSE,
207 p_count => x_msg_count,
208 p_data => x_msg_data
209 );
210
211 WHEN OTHERS THEN
212 ROLLBACK TO CREATE_template_res_PVT;
213 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
214 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
215 THEN
216 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
217 END IF;
218 -- Standard call to get message count and if count=1, get the message
219 FND_MSG_PUB.Count_And_Get (
220 p_encoded => FND_API.G_FALSE,
221 p_count => x_msg_count,
222 p_data => x_msg_data
223 );
224 End Create_template_res;
225
226
227 PROCEDURE Update_template_res(
228 p_api_version_number IN NUMBER,
229 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
230 p_commit IN VARCHAR2 := FND_API.G_FALSE,
231 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
232
233 x_return_status OUT NOCOPY VARCHAR2,
234 x_msg_count OUT NOCOPY NUMBER,
235 x_msg_data OUT NOCOPY VARCHAR2,
236
237 p_template_res_rec IN template_res_rec_type
238 )
239
240 IS
241
242 CURSOR c_get_template_res(p_templ_responsibility_id NUMBER) IS
243 SELECT *
244 FROM AMS_TEMPL_RESPONSIBILITY
245 WHERE templ_responsibility_id = p_templ_responsibility_id;
246 -- Hint: Developer need to provide Where clause
247
248 L_API_NAME CONSTANT VARCHAR2(30) := 'Update_template_res';
249 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
250 -- Local Variables
251 l_object_version_number NUMBER;
252 l_TEMPL_RESPONSIBILITY_ID NUMBER;
253 l_ref_template_res_rec c_get_template_res%ROWTYPE ;
254 l_tar_template_res_rec AMS_template_res_PVT.template_res_rec_type := P_template_res_rec;
255 l_rowid ROWID;
256
257 BEGIN
258 -- Standard Start of API savepoint
259 SAVEPOINT UPDATE_template_res_PVT;
260
261 -- Standard call to check for call compatibility.
262 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
263 p_api_version_number,
264 l_api_name,
265 G_PKG_NAME)
266 THEN
267 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
268 END IF;
269
270 -- Initialize message list if p_init_msg_list is set to TRUE.
271 IF FND_API.to_Boolean( p_init_msg_list )
272 THEN
273 FND_MSG_PUB.initialize;
274 END IF;
275
276 -- Debug Message
277 IF (AMS_DEBUG_HIGH_ON) THEN
278
279 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
280 END IF;
281
282
283 -- Initialize API return status to SUCCESS
284 x_return_status := FND_API.G_RET_STS_SUCCESS;
285
286 -- Debug Message
287 IF (AMS_DEBUG_HIGH_ON) THEN
288
289 AMS_UTILITY_PVT.debug_message('Private API: - Open Cursor to Select');
290 END IF;
291
292 /*
293 OPEN c_get_template_res( l_tar_template_res_rec.templ_responsibility_id);
294
295 FETCH c_get_template_res INTO l_ref_template_res_rec ;
296
297 If ( c_get_template_res%NOTFOUND) THEN
298 AMS_Utility_PVT.Error_Message(p_message_name => 'API_MISSING_UPDATE_TARGET',
299 p_token_name => 'INFO',
300 p_token_value => 'template_res') ;
301 RAISE FND_API.G_EXC_ERROR;
302 END IF;
303 -- Debug Message
304 IF (AMS_DEBUG_HIGH_ON) THEN
305
306 AMS_UTILITY_PVT.debug_message('Private API: - Close Cursor');
307 END IF;
308 CLOSE c_get_template_res;
309 */
310
311
312 If (l_tar_template_res_rec.object_version_number is NULL or
313 l_tar_template_res_rec.object_version_number = FND_API.G_MISS_NUM ) Then
314 AMS_Utility_PVT.Error_Message(p_message_name => 'API_VERSION_MISSING',
315 p_token_name => 'COLUMN',
316 p_token_value => 'Last_Update_Date') ;
317 raise FND_API.G_EXC_ERROR;
318 End if;
319 -- Check Whether record has been changed by someone else
320 If (l_tar_template_res_rec.object_version_number <> l_ref_template_res_rec.object_version_number) Then
321 AMS_Utility_PVT.Error_Message(p_message_name => 'API_RECORD_CHANGED',
322 p_token_name => 'INFO',
323 p_token_value => 'template_res') ;
324 raise FND_API.G_EXC_ERROR;
325 End if;
326 IF ( P_validation_level >= FND_API.G_VALID_LEVEL_FULL)
327 THEN
328 -- Debug message
329 IF (AMS_DEBUG_HIGH_ON) THEN
330
331 AMS_UTILITY_PVT.debug_message('Private API: Validate_template_res');
332 END IF;
333
334 -- Invoke validation procedures
335 Validate_template_res(
336 p_api_version_number => 1.0,
337 p_init_msg_list => FND_API.G_FALSE,
338 p_validation_level => p_validation_level,
339 p_validation_mode => JTF_PLSQL_API.g_update,
340 p_template_res_rec => p_template_res_rec,
341 x_return_status => x_return_status,
342 x_msg_count => x_msg_count,
343 x_msg_data => x_msg_data);
344 END IF;
345
346 IF x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
347 RAISE FND_API.G_EXC_ERROR;
348 END IF;
349
350 Update AMS_TEMPL_RESPONSIBILITY
351 SET templ_responsibility_id = DECODE( p_template_res_rec.templ_responsibility_id, FND_API.g_miss_num, templ_responsibility_id, p_template_res_rec.templ_responsibility_id),
352 template_id = DECODE( p_template_res_rec.template_id, FND_API.g_miss_num, template_id, p_template_res_rec.template_id),
353 responsibility_id = DECODE( p_template_res_rec.responsibility_id, FND_API.g_miss_num, responsibility_id, p_template_res_rec.responsibility_id),
354 last_update_date = sysdate,
355 last_updated_by = fnd_global.user_id,
356 object_version_number = p_template_res_rec.object_version_number + 1,
357 last_update_login = FND_GLOBAL.CONC_LOGIN_ID
358 WHERE TEMPL_RESPONSIBILITY_ID = p_template_res_rec.templ_responsibility_id
362 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
359 AND object_version_number = p_template_res_rec.object_version_number;
360
361 IF (SQL%NOTFOUND) THEN
363 END IF;
364
365 --
366 -- End of API body.
367 --
368
369 -- Standard check for p_commit
370 IF FND_API.to_Boolean( p_commit )
371 THEN
372 COMMIT WORK;
373 END IF;
374
375
376 -- Debug Message
377 IF (AMS_DEBUG_HIGH_ON) THEN
378
379 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'end');
380 END IF;
381
382 -- Standard call to get message count and if count is 1, get message info.
383 FND_MSG_PUB.Count_And_Get
384 (p_count => x_msg_count,
385 p_data => x_msg_data
386 );
387 EXCEPTION
388
389 WHEN AMS_Utility_PVT.resource_locked THEN
390 x_return_status := FND_API.g_ret_sts_error;
391 AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_API_RESOURCE_LOCKED');
392
393 WHEN FND_API.G_EXC_ERROR THEN
394 ROLLBACK TO UPDATE_template_res_PVT;
395 x_return_status := FND_API.G_RET_STS_ERROR;
396 -- Standard call to get message count and if count=1, get the message
397 FND_MSG_PUB.Count_And_Get (
398 p_encoded => FND_API.G_FALSE,
399 p_count => x_msg_count,
400 p_data => x_msg_data
401 );
402
403 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
404 ROLLBACK TO UPDATE_template_res_PVT;
405 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
406 -- Standard call to get message count and if count=1, get the message
407 FND_MSG_PUB.Count_And_Get (
408 p_encoded => FND_API.G_FALSE,
409 p_count => x_msg_count,
410 p_data => x_msg_data
411 );
412
413 WHEN OTHERS THEN
414 ROLLBACK TO UPDATE_template_res_PVT;
415 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
416 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
417 THEN
418 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
419 END IF;
420 -- Standard call to get message count and if count=1, get the message
421 FND_MSG_PUB.Count_And_Get (
422 p_encoded => FND_API.G_FALSE,
423 p_count => x_msg_count,
424 p_data => x_msg_data
425 );
426 End Update_template_res;
427
428
429 PROCEDURE Delete_template_res(
430 p_api_version_number IN NUMBER,
431 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
432 p_commit IN VARCHAR2 := FND_API.G_FALSE,
433 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
434 x_return_status OUT NOCOPY VARCHAR2,
435 x_msg_count OUT NOCOPY NUMBER,
436 x_msg_data OUT NOCOPY VARCHAR2,
437 p_templ_responsibility_id IN NUMBER,
438 p_object_version_number IN NUMBER
439 )
440
441 IS
442 L_API_NAME CONSTANT VARCHAR2(30) := 'Delete_template_res';
443 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
444 l_object_version_number NUMBER;
445
446 BEGIN
447 -- Standard Start of API savepoint
448 SAVEPOINT DELETE_template_res_PVT;
449
450 -- Standard call to check for call compatibility.
451 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
452 p_api_version_number,
453 l_api_name,
454 G_PKG_NAME)
455 THEN
456 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
457 END IF;
458
459 -- Initialize message list if p_init_msg_list is set to TRUE.
460 IF FND_API.to_Boolean( p_init_msg_list )
461 THEN
462 FND_MSG_PUB.initialize;
463 END IF;
464
465 -- Debug Message
466 IF (AMS_DEBUG_HIGH_ON) THEN
467
468 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
469 END IF;
470
471
472 -- Initialize API return status to SUCCESS
473 x_return_status := FND_API.G_RET_STS_SUCCESS;
474
475 DELETE FROM AMS_TEMPL_RESPONSIBILITY
476 WHERE TEMPL_RESPONSIBILITY_ID = p_TEMPL_RESPONSIBILITY_ID;
477
478 If (SQL%NOTFOUND) then
479 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
480 End If;
481
482 -- Standard check for p_commit
483 IF FND_API.to_Boolean( p_commit )
484 THEN
485 COMMIT WORK;
486 END IF;
487
488
489 -- Debug Message
490 IF (AMS_DEBUG_HIGH_ON) THEN
491
492 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'end');
493 END IF;
494
495 -- Standard call to get message count and if count is 1, get message info.
496 FND_MSG_PUB.Count_And_Get
497 (p_count => x_msg_count,
498 p_data => x_msg_data
499 );
500 EXCEPTION
501
502 WHEN AMS_Utility_PVT.resource_locked THEN
503 x_return_status := FND_API.g_ret_sts_error;
504 AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_API_RESOURCE_LOCKED');
505
506 WHEN FND_API.G_EXC_ERROR THEN
507 ROLLBACK TO DELETE_template_res_PVT;
508 x_return_status := FND_API.G_RET_STS_ERROR;
509 -- Standard call to get message count and if count=1, get the message
510 FND_MSG_PUB.Count_And_Get (
511 p_encoded => FND_API.G_FALSE,
512 p_count => x_msg_count,
516 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
513 p_data => x_msg_data
514 );
515
517 ROLLBACK TO DELETE_template_res_PVT;
518 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
519 -- Standard call to get message count and if count=1, get the message
520 FND_MSG_PUB.Count_And_Get (
521 p_encoded => FND_API.G_FALSE,
522 p_count => x_msg_count,
523 p_data => x_msg_data
524 );
525
526 WHEN OTHERS THEN
527 ROLLBACK TO DELETE_template_res_PVT;
528 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
529 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
530 THEN
531 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
532 END IF;
533 -- Standard call to get message count and if count=1, get the message
534 FND_MSG_PUB.Count_And_Get (
535 p_encoded => FND_API.G_FALSE,
536 p_count => x_msg_count,
537 p_data => x_msg_data
538 );
539 End Delete_template_res;
540
541
542
543 -- Hint: Primary key needs to be returned.
544 PROCEDURE Lock_template_res(
545 p_api_version_number IN NUMBER,
546 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
547
548 x_return_status OUT NOCOPY VARCHAR2,
549 x_msg_count OUT NOCOPY NUMBER,
550 x_msg_data OUT NOCOPY VARCHAR2,
551
552 p_templ_responsibility_id IN NUMBER,
553 p_object_version IN NUMBER
554 )
555
556 IS
557 L_API_NAME CONSTANT VARCHAR2(30) := 'Lock_template_res';
558 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
559 L_FULL_NAME CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
560 l_TEMPL_RESPONSIBILITY_ID NUMBER;
561
562 CURSOR c_template_res IS
563 SELECT TEMPL_RESPONSIBILITY_ID
564 FROM AMS_TEMPL_RESPONSIBILITY
565 WHERE TEMPL_RESPONSIBILITY_ID = p_TEMPL_RESPONSIBILITY_ID
566 AND object_version_number = p_object_version
567 FOR UPDATE NOWAIT;
568
569 BEGIN
570
571 -- Debug Message
572 IF (AMS_DEBUG_HIGH_ON) THEN
573
574 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
575 END IF;
576
577 -- Initialize message list if p_init_msg_list is set to TRUE.
578 IF FND_API.to_Boolean( p_init_msg_list )
579 THEN
580 FND_MSG_PUB.initialize;
581 END IF;
582
583 -- Standard call to check for call compatibility.
584 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
585 p_api_version_number,
586 l_api_name,
587 G_PKG_NAME)
588 THEN
589 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
590 END IF;
591
592
593 -- Initialize API return status to SUCCESS
594 x_return_status := FND_API.G_RET_STS_SUCCESS;
595
596
597 ------------------------ lock -------------------------
598
599 IF (AMS_DEBUG_HIGH_ON) THEN
600
601
602
603 AMS_Utility_PVT.debug_message(l_full_name||': start');
604
605 END IF;
606 OPEN c_template_res;
607
608 FETCH c_template_res INTO l_TEMPL_RESPONSIBILITY_ID;
609
610 IF (c_template_res%NOTFOUND) THEN
611 CLOSE c_template_res;
612 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
613 FND_MESSAGE.set_name('AMS', 'AMS_API_RECORD_NOT_FOUND');
614 FND_MSG_PUB.add;
615 END IF;
616 RAISE FND_API.g_exc_error;
617 END IF;
618
619 CLOSE c_template_res;
620
621 -------------------- finish --------------------------
622 FND_MSG_PUB.count_and_get(
623 p_encoded => FND_API.g_false,
624 p_count => x_msg_count,
625 p_data => x_msg_data);
626 IF (AMS_DEBUG_HIGH_ON) THEN
627
628 AMS_Utility_PVT.debug_message(l_full_name ||': end');
629 END IF;
630 EXCEPTION
631
632 WHEN AMS_Utility_PVT.resource_locked THEN
633 x_return_status := FND_API.g_ret_sts_error;
634 AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_API_RESOURCE_LOCKED');
635
636 WHEN FND_API.G_EXC_ERROR THEN
637 ROLLBACK TO LOCK_template_res_PVT;
638 x_return_status := FND_API.G_RET_STS_ERROR;
639 -- Standard call to get message count and if count=1, get the message
640 FND_MSG_PUB.Count_And_Get (
641 p_encoded => FND_API.G_FALSE,
642 p_count => x_msg_count,
643 p_data => x_msg_data
644 );
645
646 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
647 ROLLBACK TO LOCK_template_res_PVT;
648 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
649 -- Standard call to get message count and if count=1, get the message
650 FND_MSG_PUB.Count_And_Get (
651 p_encoded => FND_API.G_FALSE,
652 p_count => x_msg_count,
653 p_data => x_msg_data
654 );
655
656 WHEN OTHERS THEN
657 ROLLBACK TO LOCK_template_res_PVT;
658 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
659 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
660 THEN
661 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
662 END IF;
663 -- Standard call to get message count and if count=1, get the message
664 FND_MSG_PUB.Count_And_Get (
665 p_encoded => FND_API.G_FALSE,
666 p_count => x_msg_count,
667 p_data => x_msg_data
668 );
672 PROCEDURE check_template_res_uk_items(
669 End Lock_template_res;
670
671
673 p_template_res_rec IN template_res_rec_type,
674 p_validation_mode IN VARCHAR2 := JTF_PLSQL_API.g_create,
675 x_return_status OUT NOCOPY VARCHAR2)
676 IS
677 l_valid_flag VARCHAR2(1);
678
679 BEGIN
680 x_return_status := FND_API.g_ret_sts_success;
681 IF p_validation_mode = JTF_PLSQL_API.g_create THEN
682 l_valid_flag := AMS_Utility_PVT.check_uniqueness(
683 'AMS_TEMPL_RESPONSIBILITY',
684 'TEMPL_RESPONSIBILITY_ID = ''' || p_template_res_rec.TEMPL_RESPONSIBILITY_ID ||''''
685 );
686 ELSE
687 l_valid_flag := AMS_Utility_PVT.check_uniqueness(
688 'AMS_TEMPL_RESPONSIBILITY',
689 'TEMPL_RESPONSIBILITY_ID = ''' || p_template_res_rec.TEMPL_RESPONSIBILITY_ID ||
690 ''' AND TEMPL_RESPONSIBILITY_ID <> ' || p_template_res_rec.TEMPL_RESPONSIBILITY_ID
691 );
692 END IF;
693
694 IF l_valid_flag = FND_API.g_false THEN
695 AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_TEMPL_RESPONSIBILITY_ID_DUPLICATE');
696 x_return_status := FND_API.g_ret_sts_error;
697 RETURN;
698 END IF;
699
700 END check_template_res_uk_items;
701
702 PROCEDURE check_template_res_req_items(
703 p_template_res_rec IN template_res_rec_type,
704 p_validation_mode IN VARCHAR2 := JTF_PLSQL_API.g_create,
705 x_return_status OUT NOCOPY VARCHAR2
706 )
707 IS
708 BEGIN
709 x_return_status := FND_API.g_ret_sts_success;
710
711 IF p_validation_mode = JTF_PLSQL_API.g_create THEN
712
713 IF p_template_res_rec.template_id = FND_API.g_miss_num OR p_template_res_rec.template_id IS NULL THEN
714 FND_MESSAGE.set_name('AMS', 'AMS_API_MISSING_FIELD');
715 FND_MESSAGE.set_token('MISS_FIELD','TEMPLATE_ID');
716 x_return_status := FND_API.g_ret_sts_error;
717 RETURN;
718 END IF;
719
720
721 IF p_template_res_rec.responsibility_id = FND_API.g_miss_num OR p_template_res_rec.responsibility_id IS NULL THEN
722 FND_MESSAGE.set_name('AMS', 'AMS_API_MISSING_FIELD');
723 FND_MESSAGE.set_token('MISS_FIELD','RESPONSIBILITY_ID');
724 x_return_status := FND_API.g_ret_sts_error;
725 RETURN;
726 END IF;
727
728
729 ELSE
730
731
732 IF p_template_res_rec.templ_responsibility_id IS NULL THEN
733 AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_template_res_NO_templ_responsibility_id');
734 x_return_status := FND_API.g_ret_sts_error;
735 RETURN;
736 END IF;
737
738
739 IF p_template_res_rec.template_id IS NULL THEN
740 AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_template_res_NO_template_id');
741 x_return_status := FND_API.g_ret_sts_error;
742 RETURN;
743 END IF;
744
745
746 IF p_template_res_rec.responsibility_id IS NULL THEN
747 AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_template_res_NO_responsibility_id');
748 x_return_status := FND_API.g_ret_sts_error;
749 RETURN;
750 END IF;
751
752
753 END IF;
754
755 END check_template_res_req_items;
756
757 PROCEDURE check_template_res_FK_items(
758 p_template_res_rec IN template_res_rec_type,
759 x_return_status OUT NOCOPY VARCHAR2
760 )
761 IS
762 BEGIN
763 x_return_status := FND_API.g_ret_sts_success;
764
765 -- Enter custom code here
766
767 END check_template_res_FK_items;
768
769 PROCEDURE check_template_res_Lkup_items(
770 p_template_res_rec IN template_res_rec_type,
771 x_return_status OUT NOCOPY VARCHAR2
772 )
773 IS
774 BEGIN
775 x_return_status := FND_API.g_ret_sts_success;
776
777 -- Enter custom code here
778
779 END check_template_res_Lkup_items;
780
781 PROCEDURE Check_template_res_Items (
782 P_template_res_rec IN template_res_rec_type,
783 p_validation_mode IN VARCHAR2,
784 x_return_status OUT NOCOPY VARCHAR2
785 )
786 IS
787 BEGIN
788
789 -- Check Items Uniqueness API calls
790
791 check_template_res_uk_items(
792 p_template_res_rec => p_template_res_rec,
793 p_validation_mode => p_validation_mode,
794 x_return_status => x_return_status);
795 IF x_return_status <> FND_API.g_ret_sts_success THEN
796 RETURN;
797 END IF;
798
799 -- Check Items Required/NOT NULL API calls
800
801 check_template_res_req_items(
802 p_template_res_rec => p_template_res_rec,
803 p_validation_mode => p_validation_mode,
804 x_return_status => x_return_status);
805 IF x_return_status <> FND_API.g_ret_sts_success THEN
806 RETURN;
807 END IF;
808 -- Check Items Foreign Keys API calls
809
810 check_template_res_FK_items(
811 p_template_res_rec => p_template_res_rec,
812 x_return_status => x_return_status);
813 IF x_return_status <> FND_API.g_ret_sts_success THEN
814 RETURN;
815 END IF;
816 -- Check Items Lookups
817
818 check_template_res_Lkup_items(
819 p_template_res_rec => p_template_res_rec,
820 x_return_status => x_return_status);
821 IF x_return_status <> FND_API.g_ret_sts_success THEN
822 RETURN;
823 END IF;
824
825 END Check_template_res_Items;
826
827
828
829 PROCEDURE Complete_template_res_Rec (
830 p_template_res_rec IN template_res_rec_type,
834
831 x_complete_rec OUT NOCOPY template_res_rec_type)
832 IS
833 l_return_status VARCHAR2(1);
835 CURSOR c_complete IS
836 SELECT *
837 FROM ams_templ_responsibility
838 WHERE templ_responsibility_id = p_template_res_rec.templ_responsibility_id;
839 l_template_res_rec c_complete%ROWTYPE;
840 BEGIN
841 x_complete_rec := p_template_res_rec;
842
843
844 OPEN c_complete;
845 FETCH c_complete INTO l_template_res_rec;
846 CLOSE c_complete;
847
848 -- templ_responsibility_id
849 IF p_template_res_rec.templ_responsibility_id = FND_API.g_miss_num THEN
850 x_complete_rec.templ_responsibility_id := l_template_res_rec.templ_responsibility_id;
851 END IF;
852
853 -- template_id
854 IF p_template_res_rec.template_id = FND_API.g_miss_num THEN
855 x_complete_rec.template_id := l_template_res_rec.template_id;
856 END IF;
857
858 -- responsibility_id
859 IF p_template_res_rec.responsibility_id = FND_API.g_miss_num THEN
860 x_complete_rec.responsibility_id := l_template_res_rec.responsibility_id;
861 END IF;
862
863 -- last_update_date
864 IF p_template_res_rec.last_update_date = FND_API.g_miss_date THEN
865 x_complete_rec.last_update_date := l_template_res_rec.last_update_date;
866 END IF;
867
868 -- last_updated_by
869 IF p_template_res_rec.last_updated_by = FND_API.g_miss_num THEN
870 x_complete_rec.last_updated_by := l_template_res_rec.last_updated_by;
871 END IF;
872
873 -- creation_date
874 IF p_template_res_rec.creation_date = FND_API.g_miss_date THEN
875 x_complete_rec.creation_date := l_template_res_rec.creation_date;
876 END IF;
877
878 -- created_by
879 IF p_template_res_rec.created_by = FND_API.g_miss_num THEN
880 x_complete_rec.created_by := l_template_res_rec.created_by;
881 END IF;
882
883 -- object_version_number
884 IF p_template_res_rec.object_version_number = FND_API.g_miss_num THEN
885 x_complete_rec.object_version_number := l_template_res_rec.object_version_number;
886 END IF;
887
888 -- last_update_login
889 IF p_template_res_rec.last_update_login = FND_API.g_miss_num THEN
890 x_complete_rec.last_update_login := l_template_res_rec.last_update_login;
891 END IF;
892
893 -- security_group_id
894 IF p_template_res_rec.security_group_id = FND_API.g_miss_num THEN
895 x_complete_rec.security_group_id := l_template_res_rec.security_group_id;
896 END IF;
897 -- Note: Developers need to modify the procedure
898 -- to handle any business specific requirements.
899 END Complete_template_res_Rec;
900 PROCEDURE Validate_template_res(
901 p_api_version_number IN NUMBER,
902 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
903 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
904 p_template_res_rec IN template_res_rec_type,
905 p_validation_mode IN VARCHAR2,
906 x_return_status OUT NOCOPY VARCHAR2,
907 x_msg_count OUT NOCOPY NUMBER,
908 x_msg_data OUT NOCOPY VARCHAR2
909 )
910 IS
911 L_API_NAME CONSTANT VARCHAR2(30) := 'Validate_template_res';
912 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
913 l_object_version_number NUMBER;
914 l_template_res_rec AMS_template_res_PVT.template_res_rec_type;
915
916 BEGIN
917 -- Standard Start of API savepoint
918 SAVEPOINT VALIDATE_template_res_;
919
920 -- Standard call to check for call compatibility.
921 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
922 p_api_version_number,
923 l_api_name,
924 G_PKG_NAME)
925 THEN
926 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
927 END IF;
928
929 -- Initialize message list if p_init_msg_list is set to TRUE.
930 IF FND_API.to_Boolean( p_init_msg_list )
931 THEN
932 FND_MSG_PUB.initialize;
933 END IF;
934 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_item THEN
935 Check_template_res_Items(
936 p_template_res_rec => p_template_res_rec,
937 p_validation_mode => p_validation_mode,
938 x_return_status => x_return_status
939 );
940
941 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
942 RAISE FND_API.G_EXC_ERROR;
943 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
944 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
945 END IF;
946 END IF;
947
948 Complete_template_res_Rec(
949 p_template_res_rec => p_template_res_rec,
950 x_complete_rec => l_template_res_rec
951 );
952
953 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_item THEN
954 Validate_template_res_Rec(
955 p_api_version_number => 1.0,
956 p_init_msg_list => FND_API.G_FALSE,
957 x_return_status => x_return_status,
958 x_msg_count => x_msg_count,
959 x_msg_data => x_msg_data,
960 p_template_res_rec => l_template_res_rec);
961
962 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
963 RAISE FND_API.G_EXC_ERROR;
964 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
965 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
966 END IF;
967 END IF;
968
969
973 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
970 -- Debug Message
971 IF (AMS_DEBUG_HIGH_ON) THEN
972
974 END IF;
975
976
977 -- Initialize API return status to SUCCESS
978 x_return_status := FND_API.G_RET_STS_SUCCESS;
979
980
981 -- Debug Message
982 IF (AMS_DEBUG_HIGH_ON) THEN
983
984 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'end');
985 END IF;
986
987 -- Standard call to get message count and if count is 1, get message info.
988 FND_MSG_PUB.Count_And_Get
989 (p_count => x_msg_count,
990 p_data => x_msg_data
991 );
992 EXCEPTION
993
994 WHEN AMS_Utility_PVT.resource_locked THEN
995 x_return_status := FND_API.g_ret_sts_error;
996 AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_API_RESOURCE_LOCKED');
997
998 WHEN FND_API.G_EXC_ERROR THEN
999 ROLLBACK TO VALIDATE_template_res_;
1000 x_return_status := FND_API.G_RET_STS_ERROR;
1001 -- Standard call to get message count and if count=1, get the message
1002 FND_MSG_PUB.Count_And_Get (
1003 p_encoded => FND_API.G_FALSE,
1004 p_count => x_msg_count,
1005 p_data => x_msg_data
1006 );
1007
1008 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1009 ROLLBACK TO VALIDATE_template_res_;
1010 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1011 -- Standard call to get message count and if count=1, get the message
1012 FND_MSG_PUB.Count_And_Get (
1013 p_encoded => FND_API.G_FALSE,
1014 p_count => x_msg_count,
1015 p_data => x_msg_data
1016 );
1017
1018 WHEN OTHERS THEN
1019 ROLLBACK TO VALIDATE_template_res_;
1020 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1021 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1022 THEN
1023 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1024 END IF;
1025 -- Standard call to get message count and if count=1, get the message
1026 FND_MSG_PUB.Count_And_Get (
1027 p_encoded => FND_API.G_FALSE,
1028 p_count => x_msg_count,
1029 p_data => x_msg_data
1030 );
1031 End Validate_template_res;
1032
1033
1034 PROCEDURE Validate_template_res_rec(
1035 p_api_version_number IN NUMBER,
1036 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1037 x_return_status OUT NOCOPY VARCHAR2,
1038 x_msg_count OUT NOCOPY NUMBER,
1039 x_msg_data OUT NOCOPY VARCHAR2,
1040 p_template_res_rec IN template_res_rec_type
1041 )
1042 IS
1043 BEGIN
1044 -- Initialize message list if p_init_msg_list is set to TRUE.
1045 IF FND_API.to_Boolean( p_init_msg_list )
1046 THEN
1047 FND_MSG_PUB.initialize;
1048 END IF;
1049
1050 -- Initialize API return status to SUCCESS
1051 x_return_status := FND_API.G_RET_STS_SUCCESS;
1052
1053 -- Hint: Validate data
1054 -- If data not valid
1055 -- THEN
1056 -- x_return_status := FND_API.G_RET_STS_ERROR;
1057
1058 -- Debug Message
1059 IF (AMS_DEBUG_HIGH_ON) THEN
1060
1061 AMS_UTILITY_PVT.debug_message('Private API: Validate_dm_model_rec');
1062 END IF;
1063 -- Standard call to get message count and if count is 1, get message info.
1064 FND_MSG_PUB.Count_And_Get
1065 (p_count => x_msg_count,
1066 p_data => x_msg_data
1067 );
1068 END Validate_template_res_Rec;
1069
1070 END AMS_template_res_PVT;