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