[Home] [Help]
PACKAGE BODY: APPS.AMS_IBA_PL_SITES_PVT
Source
1 PACKAGE BODY AMS_Iba_Pl_Sites_PVT as
2 /* $Header: amsvsitb.pls 120.0 2005/05/31 14:22:44 appldev noship $ */
3 -- ===============================================================
4 -- Start of Comments
5 -- Package name
6 -- AMS_Iba_Pl_Sites_PVT
7 -- Purpose
8 -- Private api created to insert/update/delete placement sites
9 -- History
10 -- 18-Apr-2001 sodixit Created
11 -- NOTE
12 --
13 -- End of Comments
14 -- ===============================================================
15
16
17 G_PKG_NAME CONSTANT VARCHAR2(30):= 'AMS_Iba_Pl_Sites_PVT';
18 G_FILE_NAME CONSTANT VARCHAR2(12) := 'amsvsitb.pls';
19
20 AMS_DEBUG_HIGH_ON constant boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH);
21 AMS_DEBUG_LOW_ON constant boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
22 AMS_DEBUG_MEDIUM_ON constant boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_MEDIUM);
23
24 function check_site_pages(
25 p_site_id IN NUMBER
26 , p_validation_mode IN VARCHAR2
27 , x_return_status OUT NOCOPY VARCHAR2
28 )
29 return NUMBER
30 IS
31 l_site_page_count NUMBER;
32
33 CURSOR c_site_pages(c_site_id IN NUMBER) IS
34 SELECT COUNT(*)
35 FROM AMS_IBA_PL_PAGES_B
36 where site_id = c_site_id;
37 BEGIN
38 x_return_status := FND_API.g_ret_sts_success;
39
40 -- Enter custom code here
41 OPEN c_site_pages(p_site_id);
42 FETCH c_site_pages INTO l_site_page_count;
43 CLOSE c_site_pages;
44
45 IF l_site_page_count = 0 THEN
46 x_return_status := FND_API.g_ret_sts_success;
47 ELSE
48 x_return_status := fnd_api.g_ret_sts_error;
49 END IF;
50 RETURN l_site_page_count;
51 END check_site_pages;
52
53 function is_site_name_changed(
54 p_iba_pl_sites_rec IN iba_pl_sites_rec_type
55 , p_validation_mode IN VARCHAR2
56 , x_return_status OUT NOCOPY VARCHAR2
57 )
58 return VARCHAR2
59 IS
60 l_site_name_count NUMBER;
61
62 CURSOR c_site_name(c_site_id IN NUMBER, c_site_name IN VARCHAR2) IS
63 SELECT COUNT(*)
64 FROM AMS_IBA_PL_SITES_VL
65 where site_id = c_site_id
66 and name = c_site_name;
67 BEGIN
68 x_return_status := FND_API.g_ret_sts_success;
69
70 -- Enter custom code here
71 OPEN c_site_name(p_iba_pl_sites_rec.site_id, p_iba_pl_sites_rec.name);
72 FETCH c_site_name INTO l_site_name_count;
73 CLOSE c_site_name;
74
75 IF l_site_name_count = 1 THEN
76 x_return_status := FND_API.g_ret_sts_success;
77 RETURN fnd_api.g_false;
78 ELSE
79 x_return_status := fnd_api.g_ret_sts_error;
80 RETURN fnd_api.g_true;
81 END IF;
82 END is_site_name_changed;
83
84 function is_site_ref_changed(
85 p_iba_pl_sites_rec IN iba_pl_sites_rec_type
86 , p_validation_mode IN VARCHAR2
87 , x_return_status OUT NOCOPY VARCHAR2
88 )
89 return VARCHAR2
90 IS
91 l_site_ref_count NUMBER;
92
93 CURSOR c_site_ref(c_site_id IN NUMBER, c_site_ref_code IN VARCHAR2) IS
94 SELECT COUNT(*)
95 FROM AMS_IBA_PL_SITES_B
96 where site_id = c_site_id
97 and site_ref_code = c_site_ref_code;
98 BEGIN
99 x_return_status := FND_API.g_ret_sts_success;
100
101 -- Enter custom code here
102 OPEN c_site_ref(p_iba_pl_sites_rec.site_id, p_iba_pl_sites_rec.site_ref_code);
103 FETCH c_site_ref INTO l_site_ref_count;
104 CLOSE c_site_ref;
105
106 IF l_site_ref_count = 1 THEN
107 x_return_status := FND_API.g_ret_sts_success;
108 RETURN fnd_api.g_false;
109 ELSE
110 x_return_status := fnd_api.g_ret_sts_error;
111 RETURN fnd_api.g_true;
112 END IF;
113
114 END is_site_ref_changed;
115
116 -- Hint: Primary key needs to be returned.
117 PROCEDURE Create_Iba_Pl_Sites(
118 p_api_version_number IN NUMBER,
119 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
120 p_commit IN VARCHAR2 := FND_API.G_FALSE,
121 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
122
123 x_return_status OUT NOCOPY VARCHAR2,
124 x_msg_count OUT NOCOPY NUMBER,
125 x_msg_data OUT NOCOPY VARCHAR2,
126
127 p_iba_pl_sites_rec IN iba_pl_sites_rec_type := g_miss_iba_pl_sites_rec,
128 x_site_id OUT NOCOPY NUMBER
129 )
130
131 IS
132 L_API_NAME CONSTANT VARCHAR2(30) := 'Create_Iba_Pl_Sites';
133 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
134 l_return_status_full VARCHAR2(1);
135 l_object_version_number NUMBER := 1;
136 l_org_id NUMBER := FND_API.G_MISS_NUM;
137 l_site_id NUMBER;
138 l_dummy NUMBER;
139
143
140 CURSOR c_id IS
141 SELECT ams_iba_pl_sites_b_s.NEXTVAL
142 FROM dual;
144 CURSOR c_id_exists (l_id IN NUMBER) IS
145 (SELECT 1 FROM ams_iba_pl_sites_b
146 WHERE site_id = l_id);
147
148 BEGIN
149 -- Standard Start of API savepoint
150 SAVEPOINT CREATE_Iba_Pl_Sites_PVT;
151
152 -- Standard call to check for call compatibility.
153 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
154 p_api_version_number,
155 l_api_name,
156 G_PKG_NAME)
157 THEN
158 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
159 END IF;
160
161 -- Initialize message list if p_init_msg_list is set to TRUE.
162 IF FND_API.to_Boolean( p_init_msg_list )
163 THEN
164 FND_MSG_PUB.initialize;
165 END IF;
166
167 -- Debug Message
168 IF (AMS_DEBUG_HIGH_ON) THEN
169
170 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
171 END IF;
172
173
174 -- Initialize API return status to SUCCESS
175 x_return_status := FND_API.G_RET_STS_SUCCESS;
176
177 -- Local variable initialization
178
179 IF p_iba_pl_sites_rec.SITE_ID IS NULL OR p_iba_pl_sites_rec.SITE_ID = FND_API.g_miss_num THEN
180 LOOP
181 l_dummy := NULL;
182 OPEN c_id;
183 FETCH c_id INTO l_SITE_ID;
184 CLOSE c_id;
185
186 OPEN c_id_exists(l_SITE_ID);
187 FETCH c_id_exists INTO l_dummy;
188 CLOSE c_id_exists;
189 EXIT WHEN l_dummy IS NULL;
190 END LOOP;
191 x_site_id := l_SITE_ID;
192 END IF;
193
194 -- =========================================================================
195 -- Validate Environment
196 -- =========================================================================
197
198 IF FND_GLOBAL.User_Id IS NULL
199 THEN
200 AMS_Utility_PVT.Error_Message(p_message_name => 'USER_PROFILE_MISSING');
201 RAISE FND_API.G_EXC_ERROR;
202 END IF;
203
204 IF ( P_validation_level >= FND_API.G_VALID_LEVEL_FULL)
205 THEN
206 -- Debug message
207 IF (AMS_DEBUG_HIGH_ON) THEN
208
209 AMS_UTILITY_PVT.debug_message('Private API: validate_iba_pl_sites');
210 END IF;
211
212 -- Invoke validation procedures
213 validate_iba_pl_sites(
214 p_api_version_number => 1.0
215 , p_init_msg_list => FND_API.G_FALSE
216 , p_validation_level => p_validation_level
217 , p_iba_pl_sites_rec => p_iba_pl_sites_rec
218 , x_return_status => x_return_status
219 , x_msg_count => x_msg_count
220 , x_msg_data => x_msg_data
221 , p_validation_mode => JTF_PLSQL_API.g_create
222 );
223 END IF;
224
225 IF x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
226 RAISE FND_API.G_EXC_ERROR;
227 END IF;
228
229
230 -- Debug Message
231 IF (AMS_DEBUG_HIGH_ON) THEN
232
233 AMS_UTILITY_PVT.debug_message( 'Private API: Calling create table handler');
234 END IF;
235
236 -- Invoke table handler(AMS_IBA_PL_SITES_B_PKG.Insert_Row)
237 AMS_IBA_PL_SITES_B_PKG.Insert_Row(
238 px_site_id => l_site_id,
239 p_site_ref_code => p_iba_pl_sites_rec.site_ref_code,
240 p_site_category_type => p_iba_pl_sites_rec.site_category_type,
241 p_site_category_object_id => p_iba_pl_sites_rec.site_category_object_id,
242 p_status_code => p_iba_pl_sites_rec.status_code,
243 p_created_by => FND_GLOBAL.USER_ID,
244 p_creation_date => SYSDATE,
245 p_last_updated_by => FND_GLOBAL.USER_ID,
246 p_last_update_date => SYSDATE,
247 p_last_update_login => FND_GLOBAL.CONC_LOGIN_ID,
248 px_object_version_number => l_object_version_number,
249 p_name => p_iba_pl_sites_rec.name,
250 p_description => p_iba_pl_sites_rec.description);
251
252 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
253 RAISE FND_API.G_EXC_ERROR;
254 END IF;
255 --
256 -- End of API body
257 --
258
259 -- Standard check for p_commit
260 IF FND_API.to_Boolean( p_commit )
261 THEN
262 COMMIT WORK;
263 END IF;
264
265
266 -- Debug Message
267 IF (AMS_DEBUG_HIGH_ON) THEN
268
269 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'end');
270 END IF;
271
272 -- Standard call to get message count and if count is 1, get message info.
273 FND_MSG_PUB.Count_And_Get
274 (p_count => x_msg_count,
275 p_data => x_msg_data
276 );
277 EXCEPTION
278
279 WHEN AMS_Utility_PVT.resource_locked THEN
280 x_return_status := FND_API.g_ret_sts_error;
281 AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_API_RESOURCE_LOCKED');
282
283 WHEN FND_API.G_EXC_ERROR THEN
284 ROLLBACK TO CREATE_Iba_Pl_Sites_PVT;
285 x_return_status := FND_API.G_RET_STS_ERROR;
286 -- Standard call to get message count and if count=1, get the message
287 FND_MSG_PUB.Count_And_Get (
288 p_encoded => FND_API.G_FALSE,
289 p_count => x_msg_count,
290 p_data => x_msg_data
291 );
292
296 -- Standard call to get message count and if count=1, get the message
293 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
294 ROLLBACK TO CREATE_Iba_Pl_Sites_PVT;
295 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
297 FND_MSG_PUB.Count_And_Get (
298 p_encoded => FND_API.G_FALSE,
299 p_count => x_msg_count,
300 p_data => x_msg_data
301 );
302
303 WHEN OTHERS THEN
304 ROLLBACK TO CREATE_Iba_Pl_Sites_PVT;
305 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
306 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
307 THEN
308 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
309 END IF;
310 -- Standard call to get message count and if count=1, get the message
311 FND_MSG_PUB.Count_And_Get (
312 p_encoded => FND_API.G_FALSE,
313 p_count => x_msg_count,
314 p_data => x_msg_data
315 );
316 End Create_Iba_Pl_Sites;
317
318
319 PROCEDURE Update_Iba_Pl_Sites(
320 p_api_version_number IN NUMBER,
321 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
322 p_commit IN VARCHAR2 := FND_API.G_FALSE,
323 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
324
325 x_return_status OUT NOCOPY VARCHAR2,
326 x_msg_count OUT NOCOPY NUMBER,
327 x_msg_data OUT NOCOPY VARCHAR2,
328
329 p_iba_pl_sites_rec IN iba_pl_sites_rec_type,
330 x_object_version_number OUT NOCOPY NUMBER
331 )
332
333 IS
334 --/*
335 CURSOR c_get_iba_pl_sites(site_id NUMBER) IS
336 SELECT *
337 FROM AMS_IBA_PL_SITES_B
338 WHERE site_id = site_id;
339 -- Hint: Developer need to provide Where clause
340 --*/
341 L_API_NAME CONSTANT VARCHAR2(30) := 'Update_Iba_Pl_Sites';
342 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
343 -- Local Variables
344 l_object_version_number NUMBER;
345 l_SITE_ID NUMBER;
346 l_ref_iba_pl_sites_rec c_get_Iba_Pl_Sites%ROWTYPE ;
347 l_tar_iba_pl_sites_rec AMS_Iba_Pl_Sites_PVT.iba_pl_sites_rec_type := P_iba_pl_sites_rec;
348 l_rowid ROWID;
349 l_site_pages NUMBER;
350 l_siterefcode_changed VARCHAR2(1);
351 l_sitename_changed VARCHAR2(1);
352
353 BEGIN
354 -- Standard Start of API savepoint
355 SAVEPOINT UPDATE_Iba_Pl_Sites_PVT;
356
357 -- Standard call to check for call compatibility.
358 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
359 p_api_version_number,
360 l_api_name,
361 G_PKG_NAME)
362 THEN
363 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
364 END IF;
365
366 -- Initialize message list if p_init_msg_list is set to TRUE.
367 IF FND_API.to_Boolean( p_init_msg_list )
368 THEN
369 FND_MSG_PUB.initialize;
370 END IF;
371
372 -- Debug Message
373 IF (AMS_DEBUG_HIGH_ON) THEN
374
375 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
376 END IF;
377
378
379 -- Initialize API return status to SUCCESS
380 x_return_status := FND_API.G_RET_STS_SUCCESS;
381
382 -- Debug Message
383 IF (AMS_DEBUG_HIGH_ON) THEN
384
385 AMS_UTILITY_PVT.debug_message('Private API: - Open Cursor to Select');
386 END IF;
387
388 --/*
389 OPEN c_get_Iba_Pl_Sites( l_tar_iba_pl_sites_rec.site_id);
390
391 FETCH c_get_Iba_Pl_Sites INTO l_ref_iba_pl_sites_rec ;
392
393 IF ( c_get_Iba_Pl_Sites%NOTFOUND) THEN
394 AMS_Utility_PVT.Error_Message(
395 p_message_name => 'API_MISSING_UPDATE_TARGET',
396 p_token_name => 'INFO',
397 p_token_value => 'Iba_Pl_Sites') ;
398 RAISE FND_API.G_EXC_ERROR;
399 END IF;
400
401 -- Debug Message
402 IF (AMS_DEBUG_HIGH_ON) THEN
403
404 AMS_UTILITY_PVT.debug_message('Private API: - Close Cursor');
405 END IF;
406 CLOSE c_get_Iba_Pl_Sites;
407 --*/
408
409
410 IF (l_tar_iba_pl_sites_rec.object_version_number is NULL or
411 l_tar_iba_pl_sites_rec.object_version_number = FND_API.G_MISS_NUM )
412 THEN
413 AMS_Utility_PVT.Error_Message(p_message_name => 'API_VERSION_MISSING',
414 p_token_name => 'COLUMN',
415 p_token_value => 'Last_Update_Date') ;
416 RAISE FND_API.G_EXC_ERROR;
417 END IF;
418
419 -- Check Whether record has been changed by someone else
420 IF (l_tar_iba_pl_sites_rec.object_version_number <> l_ref_iba_pl_sites_rec.object_version_number)
421 THEN
422 AMS_Utility_PVT.Error_Message(p_message_name => 'API_RECORD_CHANGED',
423 p_token_name => 'INFO',
424 p_token_value => 'Iba_Pl_Sites') ;
425 RAISE FND_API.G_EXC_ERROR;
426 End if;
427
428 --Check if site_id is < 10000, if so the site is seeded site, so site name and site_ref_code should not be modified.
429 -- Commenting this check as this is being taken care at UI level. UI is making site_ref_code and site_id read only.
430 -- if (p_iba_pl_sites_rec.site_id < 10000)
431 -- then
432 -- l_sitename_changed := is_site_name_changed(
433 -- p_iba_pl_sites_rec => p_iba_pl_sites_rec
434 -- , p_validation_mode => JTF_PLSQL_API.g_update
435 -- , x_return_status => x_return_status
436 -- );
437 --
438 -- l_siterefcode_changed := is_site_ref_changed(
442 -- );
439 -- p_iba_pl_sites_rec => p_iba_pl_sites_rec
440 -- , p_validation_mode => JTF_PLSQL_API.g_update
441 -- , x_return_status => x_return_status
443 --
444 -- IF ((l_sitename_changed=fnd_api.g_true) or (l_siterefcode_changed = fnd_api.g_true))
445 -- THEN
446 -- IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
447 -- THEN
448 -- FND_MESSAGE.set_name('AMS','AMS_PLCE_SEED_SITE_NOUPD');
449 -- FND_MSG_PUB.add;
450 -- END IF;
451 -- RAISE FND_API.g_exc_error;
452 -- END IF;
453 -- end if;
454
455 --Check if the site to be updated has any pages, if so, site_ref_code should not be modified.
456 l_site_pages := check_site_pages(
457 p_site_id => p_iba_pl_sites_rec.site_id
458 , p_validation_mode => JTF_PLSQL_API.g_update
459 , x_return_status => x_return_status
460 );
461
462 IF (l_site_pages <> 0)
463 THEN
464 l_siterefcode_changed := is_site_ref_changed(
465 p_iba_pl_sites_rec => p_iba_pl_sites_rec
466 , p_validation_mode => JTF_PLSQL_API.g_update
467 , x_return_status => x_return_status
468 );
469
470 IF (l_siterefcode_changed = fnd_api.g_true)
471 THEN
472 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
473 FND_MESSAGE.set_name('AMS','AMS_PLCE_SITE_PG_REF_NOUPD');
474 FND_MSG_PUB.add;
475 END IF;
476 RAISE FND_API.g_exc_error;
477 END IF;
478 END IF;
479
480 IF ( P_validation_level >= FND_API.G_VALID_LEVEL_FULL)
481 THEN
482 -- Debug message
483 IF (AMS_DEBUG_HIGH_ON) THEN
484
485 AMS_UTILITY_PVT.debug_message('Private API: validate_iba_pl_sites');
486 END IF;
487
488 -- Invoke validation procedures
489 validate_iba_pl_sites(
490 p_api_version_number => 1.0
491 , p_init_msg_list => FND_API.G_FALSE
492 , p_validation_level => p_validation_level
493 , p_iba_pl_sites_rec => p_iba_pl_sites_rec
494 , x_return_status => x_return_status
495 , x_msg_count => x_msg_count
496 , x_msg_data => x_msg_data
497 , p_validation_mode => JTF_PLSQL_API.g_update
498 );
499 END IF;
500
501 IF (AMS_DEBUG_HIGH_ON) THEN
502
503
504
505 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || ' After Validate call - return_status='||x_return_status);
506
507 END IF;
508 IF x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
509 RAISE FND_API.G_EXC_ERROR;
510 END IF;
511
512
513 -- Debug Message
514 -- IF (AMS_DEBUG_HIGH_ON) THEN AMS_UTILITY_PVT.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'Private API: Calling update table handler'); END IF;
515 IF (AMS_DEBUG_HIGH_ON) THEN
516
517 AMS_UTILITY_PVT.debug_message('Private API: Calling update table handler - mymessage - site_id='||p_iba_pl_sites_rec.site_id);
518 END IF;
519
520 -- Invoke table handler(AMS_IBA_PL_SITES_B_PKG.Update_Row)
521 AMS_IBA_PL_SITES_B_PKG.Update_Row(
522 p_site_id => p_iba_pl_sites_rec.site_id,
523 p_site_ref_code => p_iba_pl_sites_rec.site_ref_code,
524 p_site_category_type => p_iba_pl_sites_rec.site_category_type,
525 p_site_category_object_id => p_iba_pl_sites_rec.site_category_object_id,
526 p_status_code => p_iba_pl_sites_rec.status_code,
527 p_created_by => FND_GLOBAL.USER_ID,
528 p_creation_date => SYSDATE,
529 p_last_updated_by => FND_GLOBAL.USER_ID,
533 p_name => p_iba_pl_sites_rec.name,
530 p_last_update_date => SYSDATE,
531 p_last_update_login => FND_GLOBAL.CONC_LOGIN_ID,
532 p_object_version_number => p_iba_pl_sites_rec.object_version_number,
534 p_description => p_iba_pl_sites_rec.description);
535
536 --
537 -- End of API body.
538 --
539
540 -- Standard check for p_commit
541 IF FND_API.to_Boolean( p_commit )
542 THEN
543 COMMIT WORK;
544 END IF;
545
546
547 -- Debug Message
548 IF (AMS_DEBUG_HIGH_ON) THEN
549
550 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'end');
551 END IF;
552
553 -- Standard call to get message count and if count is 1, get message info.
554 FND_MSG_PUB.Count_And_Get
555 (p_count => x_msg_count,
556 p_data => x_msg_data
557 );
558 EXCEPTION
559
560 WHEN AMS_Utility_PVT.resource_locked THEN
561 x_return_status := FND_API.g_ret_sts_error;
562 AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_API_RESOURCE_LOCKED');
563
564 WHEN FND_API.G_EXC_ERROR THEN
565 ROLLBACK TO UPDATE_Iba_Pl_Sites_PVT;
566 x_return_status := FND_API.G_RET_STS_ERROR;
567 -- Standard call to get message count and if count=1, get the message
568 FND_MSG_PUB.Count_And_Get (
569 p_encoded => FND_API.G_FALSE,
570 p_count => x_msg_count,
571 p_data => x_msg_data
572 );
573
574 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
575 ROLLBACK TO UPDATE_Iba_Pl_Sites_PVT;
576 IF (AMS_DEBUG_HIGH_ON) THEN
577
578 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || ' in Updte method - unexpected exception area');
579 END IF;
580 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
581 -- Standard call to get message count and if count=1, get the message
582 FND_MSG_PUB.Count_And_Get (
583 p_encoded => FND_API.G_FALSE,
584 p_count => x_msg_count,
585 p_data => x_msg_data
586 );
587
588 WHEN OTHERS THEN
589 ROLLBACK TO UPDATE_Iba_Pl_Sites_PVT;
590 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
591 IF (AMS_DEBUG_HIGH_ON) THEN
592
593 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || ' in Update method - others exception area');
594 END IF;
595 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
596 THEN
597 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
598 END IF;
599 -- Standard call to get message count and if count=1, get the message
600 FND_MSG_PUB.Count_And_Get (
601 p_encoded => FND_API.G_FALSE,
602 p_count => x_msg_count,
603 p_data => x_msg_data
604 );
605 End Update_Iba_Pl_Sites;
606
607
608 PROCEDURE Delete_Iba_Pl_Sites(
609 p_api_version_number IN NUMBER,
610 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
611 p_commit IN VARCHAR2 := FND_API.G_FALSE,
612 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
613 x_return_status OUT NOCOPY VARCHAR2,
614 x_msg_count OUT NOCOPY NUMBER,
615 x_msg_data OUT NOCOPY VARCHAR2,
616 p_site_id IN NUMBER,
617 p_object_version_number IN NUMBER
618 )
619
620 IS
621 l_api_name CONSTANT VARCHAR2(30) := 'Delete_Iba_Pl_Sites';
622 l_api_version_number CONSTANT NUMBER := 1.0;
623 l_object_version_number NUMBER;
624 l_site_page_count NUMBER;
625
626 CURSOR c_site_pages(c_site_id IN NUMBER) IS
627 SELECT COUNT(*)
628 FROM AMS_IBA_PL_PAGES_B
629 where site_id = c_site_id;
630
631 BEGIN
632 -- Standard Start of API savepoint
633 SAVEPOINT DELETE_Iba_Pl_Sites_PVT;
634
635 -- Standard call to check for call compatibility.
636 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
637 p_api_version_number,
638 l_api_name,
639 G_PKG_NAME)
640 THEN
641 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
642 END IF;
643
644 -- Initialize message list if p_init_msg_list is set to TRUE.
645 IF FND_API.to_Boolean( p_init_msg_list )
646 THEN
647 FND_MSG_PUB.initialize;
648 END IF;
649
650 -- Debug Message
651 IF (AMS_DEBUG_HIGH_ON) THEN
652
653 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
654 END IF;
655
656
657 -- Initialize API return status to SUCCESS
658 x_return_status := FND_API.G_RET_STS_SUCCESS;
659
660 --
661 -- Api body
662 --
663 -- Debug Message
664 IF (AMS_DEBUG_HIGH_ON) THEN
665
666 AMS_UTILITY_PVT.debug_message( 'Private API: Calling delete table handler');
667 END IF;
668
669 OPEN c_site_pages(p_site_id);
670 FETCH c_site_pages INTO l_site_page_count;
671 CLOSE c_site_pages;
672
673 IF (AMS_DEBUG_HIGH_ON) THEN
674
675
676
677 AMS_UTILITY_PVT.debug_message('Private API: Page Count = ' || l_site_page_count );
678
679 END IF;
680 -- Invoke table handler(AMS_IBA_PL_SITES_B_PKG.Delete_Row)
681 IF l_site_page_count = 0 --MEANING NO PAGES ARE AVAILABLE FOR THIS SITE
682 THEN
683 -- IF l_object_version_number = p_object_version_number -- VERSIONS MATCH
684 -- THEN
685 IF (AMS_DEBUG_HIGH_ON) THEN
686
687 AMS_UTILITY_PVT.debug_message('Private API: Just before calling Delete site_id = ' || p_site_id || 'obj_ver_no = ' || p_object_version_number);
688 END IF;
689
690 AMS_IBA_PL_SITES_B_PKG.Delete_Row
691 (p_site_id => p_site_id,
692 p_object_version_number => p_object_version_number
693 );
694 IF (AMS_DEBUG_HIGH_ON) THEN
695
696 AMS_UTILITY_PVT.debug_message('Private API: Just after calling Delete site_id = ' || p_site_id || 'obj_ver_no = ' || p_object_version_number);
697 END IF;
698 -- ELSE -- VERSIONS DOESN'T MATCH
699 -- IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
700 -- THEN
701 -- FND_MESSAGE.set_name('AMS','AMS_API_VERS_DONT_MATCH');
702 -- FND_MSG_PUB.add;
703 -- END IF;
704 -- RAISE FND_API.g_exc_error;
705 -- END IF;
706 ELSE -- PAGES ARE AVAILABLE FOR THIS SITE
707 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
708 FND_MESSAGE.set_name('AMS','AMS_PLCE_SITE_HAS_PAGES');
709 FND_MSG_PUB.add;
710 END IF;
711 RAISE FND_API.g_exc_error;
712 END IF;
713
714 --
715 -- End of API body
716 --
717
718 IF (AMS_DEBUG_HIGH_ON) THEN
719
720
721
722 AMS_UTILITY_PVT.debug_message( 'Private API: before Commit : p_commit = '|| p_commit);
723
724 END IF;
725 -- Standard check for p_commit
726 IF FND_API.to_Boolean( p_commit )
727 THEN
728 COMMIT WORK;
729 IF (AMS_DEBUG_HIGH_ON) THEN
730
731 AMS_UTILITY_PVT.debug_message( 'Private API: After Commit : p_commit = '|| p_commit);
732 END IF;
733 END IF;
734
735
736 -- Debug Message
737 IF (AMS_DEBUG_HIGH_ON) THEN
738
739 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'end');
740 END IF;
741
742 -- Standard call to get message count and if count is 1, get message info.
743 FND_MSG_PUB.Count_And_Get
744 (p_count => x_msg_count,
745 p_data => x_msg_data
746 );
747 EXCEPTION
748
749 WHEN AMS_Utility_PVT.resource_locked THEN
750 x_return_status := FND_API.g_ret_sts_error;
751 AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_API_RESOURCE_LOCKED');
752
753 WHEN FND_API.G_EXC_ERROR THEN
754 ROLLBACK TO DELETE_Iba_Pl_Sites_PVT;
755 x_return_status := FND_API.G_RET_STS_ERROR;
756 -- Standard call to get message count and if count=1, get the message
757 FND_MSG_PUB.Count_And_Get (
758 p_encoded => FND_API.G_FALSE,
759 p_count => x_msg_count,
760 p_data => x_msg_data
761 );
762
763 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
764 ROLLBACK TO DELETE_Iba_Pl_Sites_PVT;
765 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
766 -- Standard call to get message count and if count=1, get the message
767 FND_MSG_PUB.Count_And_Get (
768 p_encoded => FND_API.G_FALSE,
769 p_count => x_msg_count,
770 p_data => x_msg_data
771 );
772
773 WHEN OTHERS THEN
774 ROLLBACK TO DELETE_Iba_Pl_Sites_PVT;
775 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
776 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
777 THEN
778 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
779 END IF;
780 -- Standard call to get message count and if count=1, get the message
781 FND_MSG_PUB.Count_And_Get (
782 p_encoded => FND_API.G_FALSE,
783 p_count => x_msg_count,
784 p_data => x_msg_data
785 );
786 End Delete_Iba_Pl_Sites;
787
788
789
790 -- Hint: Primary key needs to be returned.
791 PROCEDURE Lock_Iba_Pl_Sites(
792 p_api_version_number IN NUMBER,
793 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
794
795 x_return_status OUT NOCOPY VARCHAR2,
796 x_msg_count OUT NOCOPY NUMBER,
797 x_msg_data OUT NOCOPY VARCHAR2,
798
799 p_site_id IN NUMBER,
800 p_object_version IN NUMBER
801 )
802
803 IS
804 L_API_NAME CONSTANT VARCHAR2(30) := 'Lock_Iba_Pl_Sites';
805 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
806 L_FULL_NAME CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
807 l_SITE_ID NUMBER;
808
809 CURSOR c_Iba_Pl_Sites IS
810 SELECT SITE_ID
811 FROM AMS_IBA_PL_SITES_B
812 WHERE SITE_ID = p_SITE_ID
813 AND object_version_number = p_object_version
814 FOR UPDATE NOWAIT;
815
816 BEGIN
817
818 -- Debug Message
819 IF (AMS_DEBUG_HIGH_ON) THEN
820
821 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
822 END IF;
823
824 -- Initialize message list if p_init_msg_list is set to TRUE.
825 IF FND_API.to_Boolean( p_init_msg_list )
826 THEN
827 FND_MSG_PUB.initialize;
828 END IF;
829
830 -- Standard call to check for call compatibility.
831 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
832 p_api_version_number,
833 l_api_name,
834 G_PKG_NAME)
835 THEN
836 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
837 END IF;
838
839
840 -- Initialize API return status to SUCCESS
841 x_return_status := FND_API.G_RET_STS_SUCCESS;
842
843 ------------------------ lock -------------------------
844
845 IF (AMS_DEBUG_HIGH_ON) THEN
846
847
848
849 AMS_Utility_PVT.debug_message(l_full_name||': start');
850
851 END IF;
852 OPEN c_Iba_Pl_Sites;
853
854 FETCH c_Iba_Pl_Sites INTO l_SITE_ID;
855
856 IF (c_Iba_Pl_Sites%NOTFOUND) THEN
857 CLOSE c_Iba_Pl_Sites;
858 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
859 FND_MESSAGE.set_name('AMS', 'AMS_API_RECORD_NOT_FOUND');
860 FND_MSG_PUB.add;
861 END IF;
862 RAISE FND_API.g_exc_error;
863 END IF;
864
865 CLOSE c_Iba_Pl_Sites;
866
867 -------------------- finish --------------------------
868 FND_MSG_PUB.count_and_get(
869 p_encoded => FND_API.g_false,
870 p_count => x_msg_count,
871 p_data => x_msg_data
872 );
873 IF (AMS_DEBUG_HIGH_ON) THEN
874
875 AMS_Utility_PVT.debug_message(l_full_name ||': end');
876 END IF;
877 EXCEPTION
878
879 WHEN AMS_Utility_PVT.resource_locked THEN
880 x_return_status := FND_API.g_ret_sts_error;
881 AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_API_RESOURCE_LOCKED');
882
883 WHEN FND_API.G_EXC_ERROR THEN
884 ROLLBACK TO LOCK_Iba_Pl_Sites_PVT;
885 x_return_status := FND_API.G_RET_STS_ERROR;
886 -- Standard call to get message count and if count=1, get the message
887 FND_MSG_PUB.Count_And_Get (
888 p_encoded => FND_API.G_FALSE,
889 p_count => x_msg_count,
890 p_data => x_msg_data
891 );
892
893 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
894 ROLLBACK TO LOCK_Iba_Pl_Sites_PVT;
895 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
896 -- Standard call to get message count and if count=1, get the message
897 FND_MSG_PUB.Count_And_Get (
898 p_encoded => FND_API.G_FALSE,
899 p_count => x_msg_count,
900 p_data => x_msg_data
901 );
902
903 WHEN OTHERS THEN
904 ROLLBACK TO LOCK_Iba_Pl_Sites_PVT;
905 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
906 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
907 THEN
908 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
909 END IF;
910 -- Standard call to get message count and if count=1, get the message
911 FND_MSG_PUB.Count_And_Get (
912 p_encoded => FND_API.G_FALSE,
913 p_count => x_msg_count,
914 p_data => x_msg_data
915 );
916 End Lock_Iba_Pl_Sites;
917
918
919 PROCEDURE check_iba_pl_sites_uk_items(
920 p_iba_pl_sites_rec IN iba_pl_sites_rec_type
921 , p_validation_mode IN VARCHAR2 := JTF_PLSQL_API.g_create
922 , x_return_status OUT NOCOPY VARCHAR2
923 )
924 IS
925 l_valid_flag VARCHAR2(1);
926 l_site_id_flag VARCHAR2(1);
927 l_site_name_flag VARCHAR2(1);
928 l_site_ref_code_flag VARCHAR2(1);
929
930 BEGIN
931 x_return_status := FND_API.g_ret_sts_success;
932 --If Validation_mode is create, check uniqueness of site_id, name, site_ref_code
933 if p_validation_mode = JTF_PLSQL_API.g_create then
934 -- Checking if the site id passed is unique
935 l_site_id_flag := ams_uTILITY_pvt.check_uniqueness(
936 'ams_iba_pl_sites_b'
937 , 'site_id = ''' || p_iba_pl_sites_rec.site_id ||''''
938 );
939
940 if l_site_id_flag = fnd_api.g_false then
941 ams_utility_pvt.Error_Message(p_message_name => 'AMS_PLCE_SITE_ID_DUP');
942 x_return_status := fnd_api.g_ret_sts_error;
943 return;
944 end if;
945
946 -- Checking if the site name passed is unique
947 l_site_name_flag := ams_utility_pvt.check_uniqueness(
948 'ams_iba_pl_sites_vl'
949 , 'name = ''' || p_iba_pl_sites_rec.name ||''''
950 );
951 if l_site_name_flag = fnd_api.g_false then
952 ams_uTILITY_pvt.error_Message(p_message_name => 'AMS_PLCE_SITE_NAME_DUP');
953 x_return_status := fnd_api.g_ret_sts_error;
954 return;
955 end if;
956
957 -- Checking if the site ref code passed is unique
958 l_site_ref_code_flag := ams_utility_pvt.check_uniqueness(
959 'ams_iba_pl_sites_vl'
960 , 'site_ref_code = ''' || p_iba_pl_sites_rec.site_ref_code ||''''
961 );
962 if l_site_ref_code_flag = fnd_api.g_false then
963 ams_utility_pvt.error_Message(p_message_name => 'AMS_PLCE_DUP_SITE_REF');
964 x_return_status := fnd_api.g_ret_sts_error;
965 return;
966 end if;
967 end if;
968
969 --If Validation_mode is update, check uniqueness of site name, site_ref_code
970 if p_validation_mode = JTF_PLSQL_API.g_update then
971 -- Checking if the site name passed is unique
972 l_site_name_flag := ams_utility_pvt.check_uniqueness(
973 'ams_iba_pl_sites_vl'
974 , 'name = ''' || p_iba_pl_sites_rec.name ||''' and site_id <> ' || p_iba_pl_sites_rec.site_id
975 );
976 IF (AMS_DEBUG_HIGH_ON) THEN
977
978 AMS_UTILITY_PVT.debug_message('In update unique check - site_name: ' || p_iba_pl_sites_rec.name);
979 END IF;
980 if l_site_name_flag = fnd_api.g_false then
981 ams_uTILITY_pvt.error_Message(p_message_name => 'AMS_PLCE_SITE_NAME_DUP');
982 x_return_status := fnd_api.g_ret_sts_error;
983 return;
984 end if;
985
986 -- Checking if the site ref code passed is unique
987 l_site_ref_code_flag := ams_utility_pvt.check_uniqueness(
988 'ams_iba_pl_sites_vl'
989 , 'site_ref_code = ''' || p_iba_pl_sites_rec.site_ref_code ||''' and site_id <> ' || p_iba_pl_sites_rec.site_id
990 );
991 if l_site_ref_code_flag = fnd_api.g_false then
992 ams_utility_pvt.error_Message(p_message_name => 'AMS_PLCE_DUP_SITE_REF');
993 x_return_status := fnd_api.g_ret_sts_error;
994 return;
995 end if;
996 end if;
997 END check_iba_pl_sites_uk_items;
998
999 PROCEDURE check_iba_pl_sites_req_items(
1000 p_iba_pl_sites_rec IN iba_pl_sites_rec_type,
1001 p_validation_mode IN VARCHAR2,
1002 x_return_status OUT NOCOPY VARCHAR2
1003 )
1004 IS
1005 BEGIN
1006 x_return_status := FND_API.g_ret_sts_success;
1007
1008 IF p_validation_mode = JTF_PLSQL_API.g_create THEN
1009
1010 ---------------
1011 -- sodixit : commenting the following lines as validation of the required fields for mode = create will be done in middle tier
1012 ---------------
1013 AMS_Utility_PVT.Error_Message(p_message_name => 'private API :in check required columns procedure');
1014 -- IF (p_iba_pl_sites_rec.site_id = FND_API.g_miss_num) OR (p_iba_pl_sites_rec.site_id IS NULL)
1015 -- THEN
1016 -- AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_iba_pl_sites_NO_site_id');
1017 -- x_return_status := FND_API.g_ret_sts_error;
1018 -- RETURN;
1019 -- END IF;
1020
1021 -- IF (p_iba_pl_sites_rec.site_ref_code = FND_API.g_miss_char) OR (p_iba_pl_sites_rec.site_ref_code IS NULL)
1022 ---- THEN
1023 -- AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_iba_pl_sites_NO_site_ref_code');
1024 -- x_return_status := FND_API.g_ret_sts_error;
1025 -- RETURN;
1026 -- END IF;
1027
1028
1029 -- IF (p_iba_pl_sites_rec.site_category_type = FND_API.g_miss_char) OR (p_iba_pl_sites_rec.site_category_type IS NULL)
1030 -- THEN
1031 -- AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_iba_pl_sites_NO_site_category_type');
1032 -- x_return_status := FND_API.g_ret_sts_error;
1033 -- RETURN;
1034 -- END IF;
1035
1036
1037 -- IF (p_iba_pl_sites_rec.status_code = FND_API.g_miss_char) OR (p_iba_pl_sites_rec.status_code IS NULL)
1038 -- THEN
1039 -- AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_iba_pl_sites_NO_status_code');
1040 -- x_return_status := FND_API.g_ret_sts_error;
1041 -- RETURN;
1042 -- END IF;
1043
1044
1045 -- IF (p_iba_pl_sites_rec.created_by = FND_API.g_miss_num) OR (p_iba_pl_sites_rec.created_by IS NULL)
1046 -- THEN
1047 -- AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_iba_pl_sites_NO_created_by');
1048 -- x_return_status := FND_API.g_ret_sts_error;
1049 -- RETURN;
1050 -- END IF;
1051
1052
1053 -- IF (p_iba_pl_sites_rec.creation_date = FND_API.g_miss_date) OR (p_iba_pl_sites_rec.creation_date IS NULL)
1054 -- THEN
1055 -- AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_iba_pl_sites_NO_creation_date');
1056 -- x_return_status := FND_API.g_ret_sts_error;
1057 -- RETURN;
1058 -- END IF;
1059 --
1060
1061 -- IF p_iba_pl_sites_rec.last_updated_by = FND_API.g_miss_num OR p_iba_pl_sites_rec.last_updated_by IS NULL
1062 -- THEN
1063 -- AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_iba_pl_sites_NO_last_updated_by');
1064 -- x_return_status := FND_API.g_ret_sts_error;
1065 -- RETURN;
1066 -- END IF;
1067 --
1068
1069 ---- IF p_iba_pl_sites_rec.last_update_date = FND_API.g_miss_date OR p_iba_pl_sites_rec.last_update_date IS NULL
1070 -- THEN
1071 -- AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_iba_pl_sites_NO_last_update_date');
1072 -- x_return_status := FND_API.g_ret_sts_error;
1073 -- RETURN;
1074 -- END IF;
1075
1076 ELSE
1077
1078 IF p_iba_pl_sites_rec.site_id IS NULL THEN
1079 AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_iba_pl_sites_NO_site_id');
1080 x_return_status := FND_API.g_ret_sts_error;
1081 RETURN;
1082 END IF;
1083
1084
1085 IF p_iba_pl_sites_rec.site_ref_code IS NULL THEN
1086 AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_iba_pl_sites_NO_site_ref_code');
1087 x_return_status := FND_API.g_ret_sts_error;
1088 RETURN;
1089 END IF;
1090
1091
1092 IF p_iba_pl_sites_rec.site_category_type IS NULL THEN
1093 AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_iba_pl_sites_NO_site_category_type');
1094 x_return_status := FND_API.g_ret_sts_error;
1095 RETURN;
1096 END IF;
1097
1098
1099 IF p_iba_pl_sites_rec.status_code IS NULL THEN
1100 AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_iba_pl_sites_NO_status_code');
1101 x_return_status := FND_API.g_ret_sts_error;
1102 RETURN;
1103 END IF;
1104
1105
1106 IF p_iba_pl_sites_rec.created_by IS NULL THEN
1107 AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_iba_pl_sites_NO_created_by');
1108 x_return_status := FND_API.g_ret_sts_error;
1109 RETURN;
1110 END IF;
1111
1112
1113 IF p_iba_pl_sites_rec.creation_date IS NULL THEN
1114 AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_iba_pl_sites_NO_creation_date');
1115 x_return_status := FND_API.g_ret_sts_error;
1116 RETURN;
1117 END IF;
1118
1119
1120 IF p_iba_pl_sites_rec.last_updated_by IS NULL THEN
1121 AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_iba_pl_sites_NO_last_updated_by');
1122 x_return_status := FND_API.g_ret_sts_error;
1123 RETURN;
1124 END IF;
1125
1126
1127 IF p_iba_pl_sites_rec.last_update_date IS NULL THEN
1128 AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_iba_pl_sites_NO_last_update_date');
1129 x_return_status := FND_API.g_ret_sts_error;
1130 RETURN;
1131 END IF;
1132 END IF;
1133
1134 END check_iba_pl_sites_req_items;
1135
1136 PROCEDURE check_iba_pl_sites_FK_items(
1137 p_iba_pl_sites_rec IN iba_pl_sites_rec_type
1138 , p_validation_mode IN VARCHAR2
1139 , x_return_status OUT NOCOPY VARCHAR2
1140 )
1141 IS
1142 l_site_page_count NUMBER;
1143
1144 CURSOR c_site_pages(c_site_id IN NUMBER) IS
1145 SELECT COUNT(*)
1146 FROM AMS_IBA_PL_PAGES_B
1147 where site_id = c_site_id;
1148 BEGIN
1149 x_return_status := FND_API.g_ret_sts_success;
1150
1151 -- Enter custom code here
1152 OPEN c_site_pages(p_iba_pl_sites_rec.site_id);
1153 FETCH c_site_pages INTO l_site_page_count;
1154 CLOSE c_site_pages;
1155
1156 IF l_site_page_count = 0 THEN
1157 x_return_status := FND_API.g_ret_sts_success;
1158 ELSE
1159 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1160 FND_MESSAGE.set_name('AMS','AMS_PLCE_SITE_HAS_PAGES');
1161 FND_MSG_PUB.add;
1162 END IF;
1163 x_return_status := fnd_api.g_ret_sts_error;
1164 RAISE FND_API.g_exc_error;
1165 return;
1166 END IF;
1167 END check_iba_pl_sites_FK_items;
1168
1169 PROCEDURE check_iba_pl_sites_Lkup_itm(
1170 p_iba_pl_sites_rec IN iba_pl_sites_rec_type,
1171 x_return_status OUT NOCOPY VARCHAR2
1172 )
1173 IS
1174 BEGIN
1175 x_return_status := FND_API.g_ret_sts_success;
1176
1177 -- Enter custom code here
1178
1179 END check_iba_pl_sites_Lkup_itm;
1180
1181 PROCEDURE check_iba_pl_sites_items (
1182 p_iba_pl_sites_rec IN iba_pl_sites_rec_type
1183 , p_validation_mode IN VARCHAR2
1184 , x_return_status OUT NOCOPY VARCHAR2
1185 )
1186 IS
1187 BEGIN
1188
1189 -- Check Items Uniqueness API calls
1190
1191 check_iba_pl_sites_uk_items(
1192 p_iba_pl_sites_rec => p_iba_pl_sites_rec
1193 , p_validation_mode => p_validation_mode
1194 , x_return_status => x_return_status
1195 );
1196
1197 IF x_return_status <> FND_API.g_ret_sts_success THEN
1198 RETURN;
1199 END IF;
1200
1201 -- Check Items Required/NOT NULL API calls
1202
1203 -- sodixit : commenting following lines : 05/13/01
1204 -- check_iba_pl_sites_req_items(
1205 -- p_iba_pl_sites_rec => p_iba_pl_sites_rec,
1206 -- p_validation_mode => p_validation_mode,
1207 -- x_return_status => x_return_status);
1208 -- IF x_return_status <> FND_API.g_ret_sts_success THEN
1209 -- RETURN;
1210 -- END IF;
1211
1212 -- Check Items Foreign Keys API calls
1213
1214 -- check_iba_pl_sites_FK_items(
1215 -- p_iba_pl_sites_rec => p_iba_pl_sites_rec
1216 -- , p_validation_mode => p_validation_mode
1217 -- , x_return_status => x_return_status);
1218 -- IF x_return_status <> FND_API.g_ret_sts_success THEN
1219 -- RETURN;
1220 -- END IF;
1221
1222 -- Check Items Lookups
1223
1224 -- sodixit : commenting following lines : 05/13/01
1225 -- check_iba_pl_sites_Lkup_itm(
1226 -- p_iba_pl_sites_rec => p_iba_pl_sites_rec,
1227 -- x_return_status => x_return_status);
1228 -- IF x_return_status <> FND_API.g_ret_sts_success THEN
1229 -- RETURN;
1230 -- END IF;
1231
1232 END check_iba_pl_sites_items;
1233
1234
1235 PROCEDURE Complete_iba_pl_sites_Rec (
1236 p_iba_pl_sites_rec IN iba_pl_sites_rec_type,
1237 x_complete_rec OUT NOCOPY iba_pl_sites_rec_type)
1238 IS
1239 l_return_status VARCHAR2(1);
1240
1241 CURSOR c_complete IS
1242 SELECT *
1243 FROM ams_iba_pl_sites_b
1244 WHERE site_id = p_iba_pl_sites_rec.site_id;
1245 l_iba_pl_sites_rec c_complete%ROWTYPE;
1246 BEGIN
1247 x_complete_rec := p_iba_pl_sites_rec;
1248
1249
1250 OPEN c_complete;
1251 FETCH c_complete INTO l_iba_pl_sites_rec;
1252 CLOSE c_complete;
1253
1254 -- site_id
1255 IF p_iba_pl_sites_rec.site_id = FND_API.g_miss_num THEN
1256 x_complete_rec.site_id := l_iba_pl_sites_rec.site_id;
1257 END IF;
1258
1259 -- site_ref_code
1263
1260 IF p_iba_pl_sites_rec.site_ref_code = FND_API.g_miss_char THEN
1261 x_complete_rec.site_ref_code := l_iba_pl_sites_rec.site_ref_code;
1262 END IF;
1264 -- site_category_type
1265 IF p_iba_pl_sites_rec.site_category_type = FND_API.g_miss_char THEN
1266 x_complete_rec.site_category_type := l_iba_pl_sites_rec.site_category_type;
1267 END IF;
1268
1269 -- site_category_object_id
1270 IF p_iba_pl_sites_rec.site_category_object_id = FND_API.g_miss_num THEN
1271 x_complete_rec.site_category_object_id := l_iba_pl_sites_rec.site_category_object_id;
1272 END IF;
1273
1274 -- status_code
1275 IF p_iba_pl_sites_rec.status_code = FND_API.g_miss_char THEN
1276 x_complete_rec.status_code := l_iba_pl_sites_rec.status_code;
1277 END IF;
1278
1279 -- created_by
1280 IF p_iba_pl_sites_rec.created_by = FND_API.g_miss_num THEN
1281 x_complete_rec.created_by := l_iba_pl_sites_rec.created_by;
1282 END IF;
1283
1284 -- creation_date
1285 IF p_iba_pl_sites_rec.creation_date = FND_API.g_miss_date THEN
1286 x_complete_rec.creation_date := l_iba_pl_sites_rec.creation_date;
1287 END IF;
1288
1289 -- last_updated_by
1290 IF p_iba_pl_sites_rec.last_updated_by = FND_API.g_miss_num THEN
1291 x_complete_rec.last_updated_by := l_iba_pl_sites_rec.last_updated_by;
1292 END IF;
1293
1294 -- last_update_date
1295 IF p_iba_pl_sites_rec.last_update_date = FND_API.g_miss_date THEN
1296 x_complete_rec.last_update_date := l_iba_pl_sites_rec.last_update_date;
1297 END IF;
1298
1299 -- last_update_login
1300 IF p_iba_pl_sites_rec.last_update_login = FND_API.g_miss_num THEN
1301 x_complete_rec.last_update_login := l_iba_pl_sites_rec.last_update_login;
1302 END IF;
1303
1304 -- object_version_number
1305 IF p_iba_pl_sites_rec.object_version_number = FND_API.g_miss_num THEN
1306 x_complete_rec.object_version_number := l_iba_pl_sites_rec.object_version_number;
1307 END IF;
1308 -- Note: Developers need to modify the procedure
1309 -- to handle any business specific requirements.
1310 END Complete_iba_pl_sites_Rec;
1311 PROCEDURE validate_iba_pl_sites(
1312 p_api_version_number IN NUMBER
1313 , p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
1314 , p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
1315 , p_iba_pl_sites_rec IN iba_pl_sites_rec_type
1316 , x_return_status OUT NOCOPY VARCHAR2
1317 , x_msg_count OUT NOCOPY NUMBER
1318 , x_msg_data OUT NOCOPY VARCHAR2
1319 , p_validation_mode IN VARCHAR2
1320 )
1321 IS
1322
1323 L_API_NAME CONSTANT VARCHAR2(30) := 'validate_iba_pl_sites';
1324 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
1325 l_object_version_number NUMBER;
1326 l_iba_pl_sites_rec AMS_Iba_Pl_Sites_PVT.iba_pl_sites_rec_type;
1327
1328 BEGIN
1329 -- Standard Start of API savepoint
1330 SAVEPOINT validate_iba_pl_sites_;
1331
1332 -- Standard call to check for call compatibility.
1333 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
1334 p_api_version_number,
1335 l_api_name,
1336 G_PKG_NAME)
1337 THEN
1338 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1339 END IF;
1340
1341 -- Initialize message list if p_init_msg_list is set to TRUE.
1342 IF FND_API.to_Boolean( p_init_msg_list )
1343 THEN
1344 FND_MSG_PUB.initialize;
1345 END IF;
1346 -- sodixit : commenting following lines as site items will be checked in middle tier
1347 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_item THEN
1348 check_iba_pl_sites_items(
1349 p_iba_pl_sites_rec => p_iba_pl_sites_rec
1350 , p_validation_mode => p_validation_mode
1351 , x_return_status => x_return_status
1352 );
1353
1354 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1355 RAISE FND_API.G_EXC_ERROR;
1356 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1357 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1358 END IF;
1359 END IF;
1360
1361 Complete_iba_pl_sites_Rec(
1362 p_iba_pl_sites_rec => p_iba_pl_sites_rec,
1363 x_complete_rec => l_iba_pl_sites_rec
1364 );
1365
1366 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_item THEN
1367 validate_iba_pl_sites_rec(
1368 p_api_version_number => 1.0,
1369 p_init_msg_list => FND_API.G_FALSE,
1370 x_return_status => x_return_status,
1371 x_msg_count => x_msg_count,
1372 x_msg_data => x_msg_data,
1373 p_iba_pl_sites_rec => l_iba_pl_sites_rec);
1374
1375 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1376 RAISE FND_API.G_EXC_ERROR;
1377 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1378 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1379 END IF;
1380 END IF;
1381
1382
1383 -- Debug Message
1384 IF (AMS_DEBUG_HIGH_ON) THEN
1385
1386 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
1387 END IF;
1388
1389
1390 -- Initialize API return status to SUCCESS
1391 x_return_status := FND_API.G_RET_STS_SUCCESS;
1392
1393
1394 -- Debug Message
1395 IF (AMS_DEBUG_HIGH_ON) THEN
1396
1397 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'end');
1398 END IF;
1399
1400 -- Standard call to get message count and if count is 1, get message info.
1401 FND_MSG_PUB.Count_And_Get
1402 (p_count => x_msg_count,
1403 p_data => x_msg_data
1404 );
1405 EXCEPTION
1406
1407 WHEN AMS_Utility_PVT.resource_locked THEN
1408 x_return_status := FND_API.g_ret_sts_error;
1409 AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_API_RESOURCE_LOCKED');
1410
1411 WHEN FND_API.G_EXC_ERROR THEN
1412 ROLLBACK TO validate_iba_pl_sites_;
1413 x_return_status := FND_API.G_RET_STS_ERROR;
1414 -- Standard call to get message count and if count=1, get the message
1415 FND_MSG_PUB.Count_And_Get (
1416 p_encoded => FND_API.G_FALSE,
1417 p_count => x_msg_count,
1418 p_data => x_msg_data
1419 );
1420
1421 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1422 ROLLBACK TO validate_iba_pl_sites_;
1423 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1424 -- Standard call to get message count and if count=1, get the message
1425 FND_MSG_PUB.Count_And_Get (
1426 p_encoded => FND_API.G_FALSE,
1427 p_count => x_msg_count,
1428 p_data => x_msg_data
1429 );
1430
1431 WHEN OTHERS THEN
1432 ROLLBACK TO validate_iba_pl_sites_;
1433 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1434 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1435 THEN
1436 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1437 END IF;
1438 -- Standard call to get message count and if count=1, get the message
1439 FND_MSG_PUB.Count_And_Get (
1440 p_encoded => FND_API.G_FALSE,
1441 p_count => x_msg_count,
1442 p_data => x_msg_data
1443 );
1444 End validate_iba_pl_sites;
1445
1446
1447 PROCEDURE validate_iba_pl_sites_rec(
1448 p_api_version_number IN NUMBER,
1449 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1450 x_return_status OUT NOCOPY VARCHAR2,
1451 x_msg_count OUT NOCOPY NUMBER,
1452 x_msg_data OUT NOCOPY VARCHAR2,
1453 p_iba_pl_sites_rec IN iba_pl_sites_rec_type
1454 )
1455 IS
1456 BEGIN
1457 -- Initialize message list if p_init_msg_list is set to TRUE.
1458 IF FND_API.to_Boolean( p_init_msg_list )
1459 THEN
1460 FND_MSG_PUB.initialize;
1461 END IF;
1462
1463 -- Initialize API return status to SUCCESS
1464 x_return_status := FND_API.G_RET_STS_SUCCESS;
1465
1466 -- Hint: Validate data
1467 -- If data not valid
1468 -- THEN
1469 -- x_return_status := FND_API.G_RET_STS_ERROR;
1470
1471 -- Debug Message
1472 IF (AMS_DEBUG_HIGH_ON) THEN
1473
1474 AMS_UTILITY_PVT.debug_message('Private API: Validate_dm_model_rec');
1475 END IF;
1476 -- Standard call to get message count and if count is 1, get message info.
1477 FND_MSG_PUB.Count_And_Get
1478 (p_count => x_msg_count,
1479 p_data => x_msg_data
1480 );
1481 END validate_iba_pl_sites_rec;
1482
1483 END AMS_Iba_Pl_Sites_PVT;