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