[Home] [Help]
PACKAGE BODY: APPS.IBE_DSP_MSITE_SCT_ITEM_PVT
Source
1 PACKAGE BODY IBE_DSP_MSITE_SCT_ITEM_PVT AS
2 /* $Header: IBEVCMIB.pls 120.0 2005/05/30 02:35:27 appldev noship $ */
3
4
5 --
6 --
7 -- Start of Comments
8 --
9 -- NAME
10 -- IBE_DSP_MSITE_SCT_ITEM_PVT
11 --
12 -- PURPOSE
13 -- Private API for saving, retrieving and updating mini site
14 -- section items.
15 --
16 -- NOTES
17 -- This is a pulicly accessible package. It should be used by all
18 -- sources for saving, retrieving and updating mini site section
19 -- items
20
21 -- HISTORY
22 -- 11/28/99 VPALAIYA Created
23 -- 12/12/02 SCHAK Modified for NOCOPY (Bug # 2691704) Changes.
24
25 -- **************************************************************************
26
27 G_PKG_NAME CONSTANT VARCHAR2(30):= 'IBE_DSP_MSITE_SCT_ITEM_PVT';
28 G_FILE_NAME CONSTANT VARCHAR2(12):= 'IBEVCMIB.pls';
29
30
31 -- ****************************************************************************
32 -- ****************************************************************************
33 -- TABLE HANDLERS
34 -- 1. insert_row
35 -- 2. update_row
36 -- 3. delete_row
37 -- ****************************************************************************
38 -- ****************************************************************************
39
40
41 -- ****************************************************************************
42 -- insert row into mini site section-item
43 -- ****************************************************************************
44
45 PROCEDURE insert_row
46 (
47 p_mini_site_section_item_id IN NUMBER,
48 p_object_version_number IN NUMBER,
49 p_mini_site_id IN NUMBER,
50 p_section_item_id IN NUMBER,
51 p_start_date_active IN DATE,
52 p_end_date_active IN DATE,
53 p_creation_date IN DATE,
54 p_created_by IN NUMBER,
55 p_last_update_date IN DATE,
56 p_last_updated_by IN NUMBER,
57 p_last_update_login IN NUMBER,
58 x_rowid OUT NOCOPY VARCHAR2,
59 x_mini_site_section_item_id OUT NOCOPY NUMBER
60 )
61 IS
62
63 CURSOR c IS SELECT rowid FROM ibe_dsp_msite_sct_items
64 WHERE mini_site_section_item_id = x_mini_site_section_item_id;
65 CURSOR c2 IS SELECT ibe_dsp_msite_sct_items_s1.nextval FROM dual;
66
67 BEGIN
68
69 -- Primary key validation check
70 x_mini_site_section_item_id := p_mini_site_section_item_id;
71 IF ((x_mini_site_section_item_id IS NULL) OR
72 (x_mini_site_section_item_id = FND_API.G_MISS_NUM))
73 THEN
74 OPEN c2;
75 FETCH c2 INTO x_mini_site_section_item_id;
76 CLOSE c2;
77 END IF;
78
79 -- insert base
80 INSERT INTO ibe_dsp_msite_sct_items
81 (
82 mini_site_section_item_id,
83 object_version_number,
84 mini_site_id,
85 section_item_id,
86 start_date_active,
87 end_date_active,
88 creation_date,
89 created_by,
90 last_update_date,
91 last_updated_by,
92 last_update_login
93 )
94 VALUES
95 (
96 x_mini_site_section_item_id,
97 p_object_version_number,
98 p_mini_site_id,
99 p_section_item_id,
100 p_start_date_active,
101 decode(p_end_date_active, FND_API.G_MISS_DATE, NULL, p_end_date_active),
102 decode(p_creation_date, FND_API.G_MISS_DATE, sysdate, NULL, sysdate,
103 p_creation_date),
104 decode(p_created_by, FND_API.G_MISS_NUM, FND_GLOBAL.user_id,
105 NULL, FND_GLOBAL.user_id, p_created_by),
106 decode(p_last_update_date, FND_API.G_MISS_DATE, sysdate, NULL, sysdate,
107 p_last_update_date),
108 decode(p_last_updated_by, FND_API.G_MISS_NUM, FND_GLOBAL.user_id,
109 NULL, FND_GLOBAL.user_id, p_last_updated_by),
110 decode(p_last_update_login, FND_API.G_MISS_NUM, FND_GLOBAL.login_id,
111 NULL, FND_GLOBAL.login_id, p_last_update_login)
112 );
113
114 OPEN c;
115 FETCH c INTO x_rowid;
116 IF (c%NOTFOUND)
117 THEN
118 CLOSE c;
119 RAISE NO_DATA_FOUND;
120 END IF;
121 CLOSE c;
122
123 END insert_row;
124
125 -- ****************************************************************************
126 -- update row
127 -- ****************************************************************************
128
129 PROCEDURE update_row
130 (
131 p_mini_site_section_item_id IN NUMBER,
132 p_object_version_number IN NUMBER := FND_API.G_MISS_NUM,
133 p_start_date_active IN DATE,
134 p_end_date_active IN DATE,
135 p_last_update_date IN DATE,
136 p_last_updated_by IN NUMBER,
137 p_last_update_login IN NUMBER
138 )
139 IS
140 BEGIN
141
142 -- update base
143 UPDATE ibe_dsp_msite_sct_items SET
144 object_version_number = object_version_number + 1,
145 start_date_active = decode(p_start_date_active, FND_API.G_MISS_DATE,
146 start_date_active, p_start_date_active),
147 end_date_active = decode(p_end_date_active, FND_API.G_MISS_DATE,
148 end_date_active, p_end_date_active),
149 last_update_date = decode(p_last_update_date, FND_API.G_MISS_DATE,
150 sysdate, NULL, sysdate, p_last_update_date),
151 last_updated_by = decode(p_last_updated_by, FND_API.G_MISS_NUM,
152 FND_GLOBAL.user_id, NULL, FND_GLOBAL.user_id,
153 p_last_updated_by),
154 last_update_login = decode(p_last_update_login, FND_API.G_MISS_NUM,
155 FND_GLOBAL.login_id, NULL,
156 FND_GLOBAL.login_id, p_last_update_login)
157 WHERE mini_site_section_item_id = p_mini_site_section_item_id
158 AND object_version_number = decode(p_object_version_number,
159 FND_API.G_MISS_NUM,
160 object_version_number,
161 p_object_version_number);
162
163 IF (sql%NOTFOUND) THEN
164 RAISE NO_DATA_FOUND;
165 END IF;
166
167 END update_row;
168
169
170 -- ****************************************************************************
171 -- delete row
172 -- ****************************************************************************
173
174 PROCEDURE delete_row
175 (
176 p_mini_site_section_item_id IN NUMBER
177 )
178 IS
179 BEGIN
180
181 DELETE FROM ibe_dsp_msite_sct_items
182 WHERE mini_site_section_item_id = p_mini_site_section_item_id;
183
184 IF (sql%NOTFOUND) THEN
185 RAISE NO_DATA_FOUND;
186 END IF;
187
188 END delete_row;
189
190 -- ****************************************************************************
191 --*****************************************************************************
192 --
193 --APIs
194 --
195 -- 1. Create_MSite_Section_Item
196 -- 2. Update_MSite_Section_Item
197 -- 3. Delete_MSite_Section_Item
198 -- 4. Check_Duplicate_Entry
199 --
200 --*****************************************************************************
201 --*****************************************************************************
202
203
204 --*****************************************************************************
205 -- PROCEDURE Check_Duplicate_Entry()
206 --*****************************************************************************
207
208 --
209 -- x_return_status = FND_API.G_RET_STS_SUCCESS, if the section is duplicate
210 -- x_return_status = FND_API.G_RET_STS_ERROR, if the section is not duplicate
211 --
212 --
213 PROCEDURE Check_Duplicate_Entry
214 (
215 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
216 p_mini_site_id IN NUMBER,
217 p_section_item_id IN NUMBER,
218 x_return_status OUT NOCOPY VARCHAR2,
219 x_msg_count OUT NOCOPY NUMBER,
220 x_msg_data OUT NOCOPY VARCHAR2
221 )
222 IS
223 l_api_name CONSTANT VARCHAR2(30) := 'Check_Duplicate_Entry';
224 l_api_version CONSTANT NUMBER := 1.0;
225
226 l_tmp_msite_sct_item_id NUMBER;
227 BEGIN
228
229 -- Initialize message list if p_init_msg_list is set to TRUE.
230 IF FND_API.to_Boolean(p_init_msg_list) THEN
231 FND_MSG_PUB.initialize;
232 END IF;
233
234 -- Initialize API return status to error, i.e, its not duplicate
235 x_return_status := FND_API.G_RET_STS_ERROR;
236
237 -- Check duplicate entry
238 BEGIN
239
240 SELECT mini_site_section_item_id INTO l_tmp_msite_sct_item_id
241 FROM ibe_dsp_msite_sct_items
242 WHERE mini_site_id = p_mini_site_id
243 AND section_item_id = p_section_item_id;
244
245 EXCEPTION
246
247 WHEN NO_DATA_FOUND THEN
248 -- not duplicate
249 -- do nothing
250 NULL;
251
252 WHEN OTHERS THEN
253 FND_MESSAGE.Set_Name('FND', 'SQL_PLSQL_ERROR');
254 FND_MESSAGE.Set_Token('ROUTINE', l_api_name);
255 FND_MESSAGE.Set_Token('ERRNO', SQLCODE);
256 FND_MESSAGE.Set_Token('REASON', SQLERRM);
257 FND_MSG_PUB.Add;
258 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
259
260 END;
261
262 IF (l_tmp_msite_sct_item_id IS NOT NULL) THEN
263 -- found duplicate
264 RAISE FND_API.G_EXC_ERROR;
265 END IF;
266
267 EXCEPTION
268
269 WHEN FND_API.G_EXC_ERROR THEN
270 x_return_status := FND_API.G_RET_STS_SUCCESS; -- found duplicate
271 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
272 p_data => x_msg_data,
273 p_encoded => 'F');
274
275 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
276 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
277 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
278 p_data => x_msg_data,
279 p_encoded => 'F');
280
281 WHEN OTHERS THEN
282 FND_MESSAGE.Set_Name('FND', 'SQL_PLSQL_ERROR');
283 FND_MESSAGE.Set_Token('ROUTINE', l_api_name);
284 FND_MESSAGE.Set_Token('ERRNO', SQLCODE);
285 FND_MESSAGE.Set_Token('REASON', SQLERRM);
286 FND_MSG_PUB.Add;
287
288 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
289
290 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
291 THEN
292 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
293 END IF;
294
295 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
296 p_data => x_msg_data,
297 p_encoded => 'F');
298
299 END Check_Duplicate_Entry;
300
301
302 --*****************************************************************************
303 -- PROCEDURE Validate_Create()
304 --*****************************************************************************
305 -- IF x_return_status := FND_API.G_RET_STS_ERROR, then invalid
306 -- IF x_return_status := FND_API.G_RET_STS_SUCCESS, then valid
307
308 PROCEDURE Validate_Create
309 (
310 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
311 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
312 p_mini_site_id IN NUMBER,
313 p_section_item_id IN NUMBER,
314 p_start_date_active IN DATE,
315 p_end_date_active IN DATE,
316 x_return_status OUT NOCOPY VARCHAR2,
317 x_msg_count OUT NOCOPY NUMBER,
318 x_msg_data OUT NOCOPY VARCHAR2
319 )
320 IS
321 l_api_name CONSTANT VARCHAR2(30) := 'Validate_Create';
322 l_api_version CONSTANT NUMBER := 1.0;
323 l_msg_count NUMBER;
324 l_msg_data VARCHAR2(2000);
325
326 l_msite_sct_item_id NUMBER;
327 l_mini_site_id NUMBER;
328 l_section_item_id NUMBER;
329 l_return_status VARCHAR2(1);
330 BEGIN
331
332 -- Initialize message list if p_init_msg_list is set to TRUE.
333 IF FND_API.to_Boolean(p_init_msg_list) THEN
334 FND_MSG_PUB.initialize;
335 END IF;
336
337 -- Initialize API return status to success
338 x_return_status := FND_API.G_RET_STS_SUCCESS;
339
340 --
341 -- Check null values for required fields
342 --
343
344 -- mini site id
345 IF ((p_mini_site_id IS NULL) OR
346 (p_mini_site_id = FND_API.G_MISS_NUM))
347 THEN
348 FND_MESSAGE.Set_Name('IBE', 'IBE_DSP_INVALID_MSITE_ID');
349 FND_MSG_PUB.Add;
350 RAISE FND_API.G_EXC_ERROR;
351 END IF;
352
353 -- section item id
354 IF ((p_section_item_id IS NULL) OR
355 (p_section_item_id = FND_API.G_MISS_NUM))
356 THEN
357 FND_MESSAGE.Set_Name('IBE', 'IBE_DSP_INVALID_SI_ID');
358 FND_MSG_PUB.Add;
359 RAISE FND_API.G_EXC_ERROR;
360 END IF;
361
362 -- start_date_active
363 IF ((p_start_date_active IS NULL) OR
364 (p_start_date_active = FND_API.G_MISS_DATE))
365 THEN
366 FND_MESSAGE.Set_Name('IBE', 'IBE_DSP_INVALID_START_DATE');
367 FND_MSG_PUB.Add;
368 RAISE FND_API.G_EXC_ERROR;
369 END IF;
370
371 --
372 -- Foreign key integrity constraint check
373 --
374
375 -- mini site id
376 -- note that mini site id cannot be null due to previous checks
377 BEGIN
378 SELECT msite_id INTO l_mini_site_id FROM ibe_msites_b
379 WHERE msite_id = p_mini_site_id and site_type = 'I';
380 EXCEPTION
381 WHEN NO_DATA_FOUND THEN
382 FND_MESSAGE.Set_Name('IBE', 'IBE_DSP_SCT_NO_MSITE_ID');
383 FND_MESSAGE.Set_Token('MINI_SITE_ID', p_mini_site_id);
384 FND_MSG_PUB.Add;
385 RAISE FND_API.G_EXC_ERROR;
386 WHEN OTHERS THEN
387 FND_MESSAGE.Set_Name('FND', 'SQL_PLSQL_ERROR');
388 FND_MESSAGE.Set_Token('ROUTINE', l_api_name);
389 FND_MESSAGE.Set_Token('ERRNO', SQLCODE);
390 FND_MESSAGE.Set_Token('REASON', SQLERRM);
391 FND_MSG_PUB.Add;
392
393 FND_MESSAGE.Set_Name('IBE', 'IBE_DSP_SCT_GET_MSITE_ID');
394 FND_MESSAGE.Set_Token('MINI_SITE_ID', p_mini_site_id);
395 FND_MSG_PUB.Add;
396 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
397 END;
398
399 -- section item id
400 -- note that section item id cannot be null due to previous checks
401 BEGIN
402 SELECT section_item_id INTO l_section_item_id FROM ibe_dsp_section_items
403 WHERE section_item_id = p_section_item_id;
404 EXCEPTION
405 WHEN NO_DATA_FOUND THEN
406 FND_MESSAGE.Set_Name('IBE', 'IBE_DSP_SCT_NO_SI_ID');
407 FND_MESSAGE.Set_Token('SECTION_ITEM_ID', p_section_item_id);
408 FND_MSG_PUB.Add;
409 RAISE FND_API.G_EXC_ERROR;
410 WHEN OTHERS THEN
411 FND_MESSAGE.Set_Name('FND', 'SQL_PLSQL_ERROR');
412 FND_MESSAGE.Set_Token('ROUTINE', l_api_name);
413 FND_MESSAGE.Set_Token('ERRNO', SQLCODE);
414 FND_MESSAGE.Set_Token('REASON', SQLERRM);
415 FND_MSG_PUB.Add;
416
417 FND_MESSAGE.Set_Name('IBE', 'IBE_DSP_SCT_GET_SI_ID');
418 FND_MESSAGE.Set_Token('SECTION_ITEM_ID', p_section_item_id);
419 FND_MSG_PUB.Add;
420 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
421 END;
422
426 p_section_item_id => p_section_item_id,
423 -- Validate if the entry is duplicate
424 Check_Duplicate_Entry(p_init_msg_list => FND_API.G_FALSE,
425 p_mini_site_id => p_mini_site_id,
427 x_return_status => l_return_status,
428 x_msg_count => l_msg_count,
429 x_msg_data => l_msg_data);
430
431 IF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
432 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
433 ELSIF (l_return_status = FND_API.G_RET_STS_SUCCESS) THEN
434 FND_MESSAGE.Set_Name('IBE', 'IBE_DSP_DUPLICATE_ENTRY');
435 FND_MSG_PUB.Add;
436 RAISE FND_API.G_EXC_ERROR; -- duplicate entry
437 END IF;
438
439 EXCEPTION
440
441 WHEN FND_API.G_EXC_ERROR THEN
442 x_return_status := FND_API.G_RET_STS_ERROR;
443 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
444 p_data => x_msg_data,
445 p_encoded => 'F');
446
447 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
448 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
449 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
450 p_data => x_msg_data,
451 p_encoded => 'F');
452
453 WHEN OTHERS THEN
454 FND_MESSAGE.Set_Name('FND', 'SQL_PLSQL_ERROR');
455 FND_MESSAGE.Set_Token('ROUTINE', l_api_name);
456 FND_MESSAGE.Set_Token('ERRNO', SQLCODE);
457 FND_MESSAGE.Set_Token('REASON', SQLERRM);
458 FND_MSG_PUB.Add;
459
460 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
461
462 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
463 THEN
464 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
465 END IF;
466
467 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
468 p_data => x_msg_data,
469 p_encoded => 'F');
470
471 END Validate_Create;
472
473
474 --*****************************************************************************
475 -- PROCEDURE Validate_Update()
476 --*****************************************************************************
477 -- IF x_return_status := FND_API.G_RET_STS_ERROR, then invalid
478 -- IF x_return_status := FND_API.G_RET_STS_SUCCESS, then valid
479
480 PROCEDURE Validate_Update
481 (
482 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
483 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
484 p_mini_site_section_item_id IN NUMBER,
485 p_object_version_number IN NUMBER,
486 p_start_date_active IN DATE,
487 p_end_date_active IN DATE,
488 x_return_status OUT NOCOPY VARCHAR2,
489 x_msg_count OUT NOCOPY NUMBER,
490 x_msg_data OUT NOCOPY VARCHAR2
491 )
492 IS
493 l_api_name CONSTANT VARCHAR2(30) := 'Validate_Update';
494 l_api_version CONSTANT NUMBER := 1.0;
495 l_msg_count NUMBER;
496 l_msg_data VARCHAR2(2000);
497
498 l_msite_sct_item_id NUMBER;
499 l_mini_site_id NUMBER;
500 l_section_item_id NUMBER;
501 l_return_status VARCHAR2(1);
502
503 BEGIN
504
505 -- Initialize message list if p_init_msg_list is set to TRUE.
506 IF FND_API.to_Boolean(p_init_msg_list) THEN
507 FND_MSG_PUB.initialize;
508 END IF;
509
510 -- Initialize API return status to success
511 x_return_status := FND_API.G_RET_STS_SUCCESS;
512
513 --
514 -- Check null values for required fields
515 --
516
517 -- mini_site_section_item_id
518 IF (p_mini_site_section_item_id IS NULL) THEN
519 FND_MESSAGE.Set_Name('IBE', 'IBE_DSP_NULL_PRIMARY_KEY');
520 FND_MSG_PUB.Add;
521 RAISE FND_API.G_EXC_ERROR;
522 END IF;
523
524 -- start_date_active
525 IF (p_start_date_active IS NULL) THEN
526 FND_MESSAGE.Set_Name('IBE', 'IBE_DSP_INVALID_START_DATE');
527 FND_MSG_PUB.Add;
528 RAISE FND_API.G_EXC_ERROR;
529 END IF;
530
531
532 EXCEPTION
533
534 WHEN FND_API.G_EXC_ERROR THEN
535 x_return_status := FND_API.G_RET_STS_ERROR;
536 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
537 p_data => x_msg_data,
538 p_encoded => 'F');
539
540 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
541 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
542 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
543 p_data => x_msg_data,
544 p_encoded => 'F');
545
546 WHEN OTHERS THEN
547 FND_MESSAGE.Set_Name('FND', 'SQL_PLSQL_ERROR');
548 FND_MESSAGE.Set_Token('ROUTINE', l_api_name);
549 FND_MESSAGE.Set_Token('ERRNO', SQLCODE);
550 FND_MESSAGE.Set_Token('REASON', SQLERRM);
551 FND_MSG_PUB.Add;
552
556 THEN
553 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
554
555 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
557 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
558 END IF;
559
560 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
561 p_data => x_msg_data,
562 p_encoded => 'F');
563
564 END Validate_Update;
565
566
567 -- ****************************************************************************
568 --*****************************************************************************
569
570 PROCEDURE Create_MSite_Section_Item
571 (
572 p_api_version IN NUMBER,
573 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
574 p_commit IN VARCHAR2 := FND_API.G_FALSE,
575 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
576 p_mini_site_id IN NUMBER,
577 p_section_item_id IN NUMBER,
578 p_start_date_active IN DATE,
579 p_end_date_active IN DATE := FND_API.G_MISS_DATE,
580 x_mini_site_section_item_id OUT NOCOPY NUMBER,
581 x_return_status OUT NOCOPY VARCHAR2,
582 x_msg_count OUT NOCOPY NUMBER,
583 x_msg_data OUT NOCOPY VARCHAR2
584 )
585 IS
586 l_api_name CONSTANT VARCHAR2(30)
587 := 'Create_MSite_Section_Item';
588 l_api_version CONSTANT NUMBER := 1.0;
589 l_msg_count NUMBER;
590 l_msg_data VARCHAR2(2000);
591 l_return_status VARCHAR2(1);
592
593 l_object_version_number CONSTANT NUMBER := 1;
594 l_rowid VARCHAR2(30);
595
596 BEGIN
597
598 -- Standard Start of API savepoint
599 SAVEPOINT CREATE_MSITE_SCT_ITEM_PVT;
600
601 -- Standard call to check for call compatibility.
602 IF NOT FND_API.Compatible_API_Call(l_api_version,
603 p_api_version,
604 l_api_name,
605 G_PKG_NAME)
606 THEN
607 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
608 END IF;
609
610 -- Initialize message list if p_init_msg_list is set to TRUE.
611 IF FND_API.to_Boolean(p_init_msg_list) THEN
612 FND_MSG_PUB.initialize;
613 END IF;
614
615 -- Initialize API return status to success
616 x_return_status := FND_API.G_RET_STS_SUCCESS;
617
618 -- API body
619 -- CALL FLOW :
620 -- 1. Validate
621 -- 2. Insert row with section data into section table
622 --
623
624 --
625 -- 1. Validate
626 --
627 Validate_Create
628 (
629 p_init_msg_list => FND_API.G_FALSE,
630 p_validation_level => p_validation_level,
631 p_mini_site_id => p_mini_site_id,
632 p_section_item_id => p_section_item_id,
633 p_start_date_active => p_start_date_active,
634 p_end_date_active => p_end_date_active,
635 x_return_status => l_return_status,
636 x_msg_count => l_msg_count,
637 x_msg_data => l_msg_data
638 );
639
640 IF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
641 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
642 ELSIF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
643 FND_MESSAGE.Set_Name('IBE', 'IBE_DSP_MSI_INVALID_CREATE');
644 FND_MSG_PUB.Add;
645 RAISE FND_API.G_EXC_ERROR; -- invalid
646 END IF;
647
648 --
649 -- 2. Insert row
650 --
651 BEGIN
652 insert_row
653 (
654 FND_API.G_MISS_NUM,
655 l_object_version_number,
656 p_mini_site_id,
657 p_section_item_id,
658 p_start_date_active,
659 p_end_date_active,
660 SYSDATE,
661 FND_GLOBAL.user_id,
662 SYSDATE,
663 FND_GLOBAL.user_id,
664 FND_GLOBAL.login_id,
665 l_rowid,
666 x_mini_site_section_item_id
667 );
668 EXCEPTION
669 WHEN NO_DATA_FOUND THEN
670 FND_MESSAGE.Set_Name('IBE', 'IBE_DSP_MSI_INSERT_FAIL');
671 FND_MSG_PUB.Add;
672 RAISE FND_API.G_EXC_ERROR;
673 WHEN OTHERS THEN
674 FND_MESSAGE.Set_Name('FND', 'SQL_PLSQL_ERROR');
675 FND_MESSAGE.Set_Token('ROUTINE', l_api_name);
676 FND_MESSAGE.Set_Token('ERRNO', SQLCODE);
677 FND_MESSAGE.Set_Token('REASON', SQLERRM);
678 FND_MSG_PUB.Add;
679
680 FND_MESSAGE.Set_Name('IBE', 'IBE_DSP_MSI_INSERT_FAIL');
681 FND_MSG_PUB.Add;
682 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
683 END;
684
685 --
686 -- End of main API body.
687
688 -- Standard check of p_commit.
689 IF (FND_API.To_Boolean(p_commit)) THEN
690 COMMIT WORK;
691 END IF;
692
693 -- Standard call to get message count and if count is 1, get message info.
694 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
695 p_data => x_msg_data,
699
696 p_encoded => 'F');
697
698 EXCEPTION
700 WHEN FND_API.G_EXC_ERROR THEN
701 ROLLBACK TO CREATE_MSITE_SCT_ITEM_PVT;
702 x_return_status := FND_API.G_RET_STS_ERROR;
703 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
704 p_data => x_msg_data,
705 p_encoded => 'F');
706
707 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
708 ROLLBACK TO CREATE_MSITE_SCT_ITEM_PVT;
709 FND_MESSAGE.Set_Name('FND', 'SQL_PLSQL_ERROR');
710 FND_MESSAGE.Set_Token('ROUTINE', l_api_name || 'xxx');
711 FND_MESSAGE.Set_Token('ERRNO', SQLCODE);
712 FND_MESSAGE.Set_Token('REASON', p_mini_site_id || ':' || p_section_item_id);
713 FND_MSG_PUB.Add;
714 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
715 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
716 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
717 p_data => x_msg_data,
718 p_encoded => 'F');
719
720 WHEN OTHERS THEN
721 ROLLBACK TO CREATE_MSITE_SCT_ITEM_PVT;
722
723 FND_MESSAGE.Set_Name('FND', 'SQL_PLSQL_ERROR');
724 FND_MESSAGE.Set_Token('ROUTINE', l_api_name);
725 FND_MESSAGE.Set_Token('ERRNO', SQLCODE);
726 FND_MESSAGE.Set_Token('REASON', SQLERRM);
727 FND_MSG_PUB.Add;
728
729 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
730
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
736 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
737 p_data => x_msg_data,
738 p_encoded => 'F');
739
740 END Create_MSite_Section_Item;
741
742 PROCEDURE Update_MSite_Section_Item
743 (
744 p_api_version IN NUMBER,
745 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
746 p_commit IN VARCHAR2 := FND_API.G_FALSE,
747 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
748 p_mini_site_section_item_id IN NUMBER := FND_API.G_MISS_NUM,
749 p_object_version_number IN NUMBER,
750 p_mini_site_id IN NUMBER := FND_API.G_MISS_NUM,
751 p_section_item_id IN NUMBER := FND_API.G_MISS_NUM,
752 p_start_date_active IN DATE := FND_API.G_MISS_DATE,
753 p_end_date_active IN DATE := FND_API.G_MISS_DATE,
754 x_return_status OUT NOCOPY VARCHAR2,
755 x_msg_count OUT NOCOPY NUMBER,
756 x_msg_data OUT NOCOPY VARCHAR2
757 )
758 IS
759 l_api_name CONSTANT VARCHAR2(30) := 'Update_MSite_Section_Item';
760 l_api_version CONSTANT NUMBER := 1.0;
761 l_msg_count NUMBER;
762 l_msg_data VARCHAR2(2000);
763
764 l_msite_sct_item_id NUMBER;
765 l_return_status VARCHAR2(1);
766
767 BEGIN
768
769 -- Standard Start of API savepoint
770 SAVEPOINT UPDATE_MSITE_SCT_ITEM_PVT;
771
772 -- Standard call to check for call compatibility.
773 IF NOT FND_API.Compatible_API_Call(l_api_version,
774 p_api_version,
775 l_api_name,
776 G_PKG_NAME)
777 THEN
778 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
779 END IF;
780
781 -- Initialize message list if p_init_msg_list is set to TRUE.
782 IF FND_API.to_Boolean(p_init_msg_list) THEN
783 FND_MSG_PUB.initialize;
784 END IF;
785
786 -- Initialize API return status to success
787 x_return_status := FND_API.G_RET_STS_SUCCESS;
788
789 -- API body
790 -- CALL FLOW :
791 -- 1. Check if either mini_site_section_item_id or combination of
792 -- mini_site_id and section_item_id is specified
793 -- 2. Update row
794 --
795
796 -- 1. Check if either mini_site_section_item_id or combination of
797 -- mini_site_id, section_item_id is specified
798 IF ((p_mini_site_section_item_id IS NOT NULL) AND
799 (p_mini_site_section_item_id <> FND_API.G_MISS_NUM))
800 THEN
801 -- mini_site_section_item_id specified, continue
802 l_msite_sct_item_id := p_mini_site_section_item_id;
803 ELSIF ((p_mini_site_id IS NOT NULL) AND
804 (p_mini_site_id <> FND_API.G_MISS_NUM) AND
805 (p_section_item_id IS NOT NULL) AND
806 (p_section_item_id <> FND_API.G_MISS_NUM))
807 THEN
808 -- If combination of mini_site_id and section_item_id
809 -- is specified, then query for mini_site_section_item_id
810 BEGIN
811
812 SELECT mini_site_section_item_id INTO l_msite_sct_item_id
813 FROM ibe_dsp_msite_sct_items
814 WHERE mini_site_id = p_mini_site_id
815 AND section_item_id = p_section_item_id;
816
817 EXCEPTION
818 WHEN NO_DATA_FOUND THEN
819 FND_MESSAGE.Set_Name('IBE', 'IBE_DSP_NO_MSI_ID');
820 FND_MESSAGE.Set_Token('MINI_SITE_ID', p_mini_site_id);
824 WHEN OTHERS THEN
821 FND_MESSAGE.Set_Token('SECTION_ITEM_ID', p_section_item_id);
822 FND_MSG_PUB.Add;
823 RAISE FND_API.G_EXC_ERROR;
825 FND_MESSAGE.Set_Name('FND', 'SQL_PLSQL_ERROR');
826 FND_MESSAGE.Set_Token('ROUTINE', l_api_name);
827 FND_MESSAGE.Set_Token('ERRNO', SQLCODE);
828 FND_MESSAGE.Set_Token('REASON', SQLERRM);
829 FND_MSG_PUB.Add;
830
831 FND_MESSAGE.Set_Name('IBE', 'IBE_DSP_NO_MSI_ID');
832 FND_MESSAGE.Set_Token('MINI_SITE_ID', p_mini_site_id);
833 FND_MESSAGE.Set_Token('SECTION_ITEM_ID', p_section_item_id);
834 FND_MSG_PUB.Add;
835 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
836 END;
837 ELSE
838 -- neither mini_site_section_item_id nor combination of
839 -- mini_site_id and section_item_id is specified
840 FND_MESSAGE.Set_Name('IBE', 'IBE_DSP_NO_MSI_IDS_SPEC');
841 FND_MSG_PUB.Add;
842 RAISE FND_API.G_EXC_ERROR;
843 END IF;
844
845 --
846 -- 1. Validate the input data
847 --
848 Validate_Update
849 (
850 p_init_msg_list => FND_API.G_FALSE,
851 p_validation_level => p_validation_level,
852 p_mini_site_section_item_id => l_msite_sct_item_id,
853 p_object_version_number => p_object_version_number,
854 p_start_date_active => p_start_date_active,
855 p_end_date_active => p_end_date_active,
856 x_return_status => l_return_status,
857 x_msg_count => l_msg_count,
858 x_msg_data => l_msg_data
859 );
860
861 IF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
862 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
863 ELSIF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
864 FND_MESSAGE.Set_Name('IBE', 'IBE_DSP_MSI_INVALID_UPDATE');
865 FND_MSG_PUB.Add;
866 RAISE FND_API.G_EXC_ERROR; -- invalid
867 END IF;
868
869 -- 2. update row
870 BEGIN
871 update_row
872 (
873 l_msite_sct_item_id,
874 p_object_version_number,
875 p_start_date_active,
876 p_end_date_active,
877 SYSDATE,
878 FND_GLOBAL.user_id,
879 FND_GLOBAL.login_id
880 );
881 EXCEPTION
882 WHEN NO_DATA_FOUND THEN
883 FND_MESSAGE.Set_Name('IBE', 'IBE_DSP_MSI_UPDATE_FAIL');
884 FND_MSG_PUB.Add;
885 RAISE FND_API.G_EXC_ERROR;
886 WHEN OTHERS THEN
887 FND_MESSAGE.Set_Name('FND', 'SQL_PLSQL_ERROR');
888 FND_MESSAGE.Set_Token('ROUTINE', l_api_name);
889 FND_MESSAGE.Set_Token('ERRNO', SQLCODE);
890 FND_MESSAGE.Set_Token('REASON', SQLERRM);
891 FND_MSG_PUB.Add;
892
893 FND_MESSAGE.Set_Name('IBE', 'IBE_DSP_MSI_UPDATE_FAIL');
894 FND_MSG_PUB.Add;
895 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
896 END;
897
898 --
899 -- End of main API body.
900
901 -- Standard check of p_commit.
902 IF (FND_API.To_Boolean(p_commit)) THEN
903 COMMIT WORK;
904 END IF;
905
906 -- Standard call to get message count and if count is 1, get message info.
907 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
908 p_data => x_msg_data,
909 p_encoded => 'F');
910
911 EXCEPTION
912
913 WHEN FND_API.G_EXC_ERROR THEN
914 ROLLBACK TO UPDATE_MSITE_SCT_ITEM_PVT;
915 x_return_status := FND_API.G_RET_STS_ERROR;
916 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
917 p_data => x_msg_data,
918 p_encoded => 'F');
919
920 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
921 ROLLBACK TO UPDATE_MSITE_SCT_ITEM_PVT;
922 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
923 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
924 p_data => x_msg_data,
925 p_encoded => 'F');
926
927 WHEN OTHERS THEN
928 ROLLBACK TO UPDATE_MSITE_SCT_ITEM_PVT;
929
930 FND_MESSAGE.Set_Name('FND', 'SQL_PLSQL_ERROR');
931 FND_MESSAGE.Set_Token('ROUTINE', l_api_name);
932 FND_MESSAGE.Set_Token('ERRNO', SQLCODE);
933 FND_MESSAGE.Set_Token('REASON', SQLERRM);
934 FND_MSG_PUB.Add;
935
936 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
937
938 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
939 THEN
940 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
941 END IF;
942
943 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
944 p_data => x_msg_data,
945 p_encoded => 'F');
946
947 END Update_MSite_Section_Item;
948
949
950 PROCEDURE Delete_MSite_Section_Item
951 (
952 p_api_version IN NUMBER,
953 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
954 p_commit IN VARCHAR2 := FND_API.G_FALSE,
955 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
959 p_section_item_id IN NUMBER := FND_API.G_MISS_NUM,
956 p_call_from_trigger IN BOOLEAN := FALSE,
957 p_mini_site_section_item_id IN NUMBER := FND_API.G_MISS_NUM,
958 p_mini_site_id IN NUMBER := FND_API.G_MISS_NUM,
960 x_return_status OUT NOCOPY VARCHAR2,
961 x_msg_count OUT NOCOPY NUMBER,
962 x_msg_data OUT NOCOPY VARCHAR2
963 )
964 IS
965 l_api_name CONSTANT VARCHAR2(30) := 'Delete_MSite_Section_Item';
966 l_api_version CONSTANT NUMBER := 1.0;
967
968 l_msite_sct_item_id NUMBER;
969 BEGIN
970
971 IF (p_call_from_trigger = FALSE) THEN
972 -- Standard Start of API savepoint
973 SAVEPOINT DELETE_MSITE_SCT_ITEM_PVT;
974 END IF;
975
976 -- Standard call to check for call compatibility.
977 IF NOT FND_API.Compatible_API_Call(l_api_version,
978 p_api_version,
979 l_api_name,
980 G_PKG_NAME)
981 THEN
982 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
983 END IF;
984
985 -- Initialize message list if p_init_msg_list is set to TRUE.
986 IF FND_API.to_Boolean(p_init_msg_list) THEN
987 FND_MSG_PUB.initialize;
988 END IF;
989
990 -- Initialize API return status to success
991 x_return_status := FND_API.G_RET_STS_SUCCESS;
992
993 -- CALL FLOW
994 -- 1. If mini_site_section_item_id specified, delete all references for it
995 -- 2. If combination of mini_site_id and section_item_id is specified, then
996 -- query for mini_site_section_item_id and delete all references
997
998 -- 1. If mini_site_section_item_id specified, delete all references for it
999 IF ((p_mini_site_section_item_id IS NOT NULL) AND
1000 (p_mini_site_section_item_id <> FND_API.G_MISS_NUM))
1001 THEN
1002 -- mini_site_section_item_id specified, continue
1003 l_msite_sct_item_id := p_mini_site_section_item_id;
1004 ELSIF ((p_mini_site_id IS NOT NULL) AND
1005 (p_mini_site_id <> FND_API.G_MISS_NUM) AND
1006 (p_section_item_id IS NOT NULL) AND
1007 (p_section_item_id <> FND_API.G_MISS_NUM))
1008 THEN
1009 -- If combination of mini_site_id and section_item_id is specified, then
1010 -- query for mini_site_section_item_id
1011 BEGIN
1012
1013 SELECT mini_site_section_item_id INTO l_msite_sct_item_id
1014 FROM ibe_dsp_msite_sct_items
1015 WHERE mini_site_id = p_mini_site_id
1016 AND section_item_id = p_section_item_id;
1017
1018 EXCEPTION
1019 WHEN NO_DATA_FOUND THEN
1020 FND_MESSAGE.Set_Name('IBE', 'IBE_DSP_NO_MSI_ID');
1021 FND_MESSAGE.Set_Token('MINI_SITE_ID', p_mini_site_id);
1022 FND_MESSAGE.Set_Token('SECTION_ITEM_ID', p_section_item_id);
1023 FND_MSG_PUB.Add;
1024 RAISE FND_API.G_EXC_ERROR;
1025 WHEN OTHERS THEN
1026 FND_MESSAGE.Set_Name('FND', 'SQL_PLSQL_ERROR');
1027 FND_MESSAGE.Set_Token('ROUTINE', l_api_name);
1028 FND_MESSAGE.Set_Token('ERRNO', SQLCODE);
1029 FND_MESSAGE.Set_Token('REASON', SQLERRM);
1030 FND_MSG_PUB.Add;
1031
1032 FND_MESSAGE.Set_Name('IBE', 'IBE_DSP_NO_MSI_ID');
1033 FND_MESSAGE.Set_Token('MINI_SITE_ID', p_mini_site_id);
1034 FND_MESSAGE.Set_Token('SECTION_ITEM_ID', p_section_item_id);
1035 FND_MSG_PUB.Add;
1036 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1037 END;
1038
1039 ELSE
1040 -- neither mini_site_section_item_id nor combination of
1041 -- mini_site_id and section_item_id is specified
1042 FND_MESSAGE.Set_Name('IBE', 'IBE_DSP_NO_MSI_IDS_SPEC');
1043 FND_MSG_PUB.Add;
1044 RAISE FND_API.G_EXC_ERROR;
1045 END IF;
1046
1047 -- delete row
1048 delete_row(l_msite_sct_item_id);
1049
1050 EXCEPTION
1051
1052 WHEN FND_API.G_EXC_ERROR THEN
1053 IF (p_call_from_trigger = FALSE) THEN
1054 ROLLBACK TO DELETE_MSITE_SCT_ITEM_PVT;
1055 END IF;
1056 x_return_status := FND_API.G_RET_STS_ERROR;
1057 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
1058 p_data => x_msg_data,
1059 p_encoded => 'F');
1060
1061 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1062 IF (p_call_from_trigger = FALSE) THEN
1063 ROLLBACK TO DELETE_MSITE_SCT_ITEM_PVT;
1064 END IF;
1065 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1066 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
1067 p_data => x_msg_data,
1068 p_encoded => 'F');
1069
1070 WHEN OTHERS THEN
1071 IF (p_call_from_trigger = FALSE) THEN
1072 ROLLBACK TO DELETE_MSITE_SCT_ITEM_PVT;
1073 END IF;
1074
1075 FND_MESSAGE.Set_Name('FND', 'SQL_PLSQL_ERROR');
1076 FND_MESSAGE.Set_Token('ROUTINE', l_api_name);
1077 FND_MESSAGE.Set_Token('ERRNO', SQLCODE);
1078 FND_MESSAGE.Set_Token('REASON', SQLERRM);
1079 FND_MSG_PUB.Add;
1080
1081 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1082
1083 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
1084 THEN
1085 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
1086 END IF;
1087
1088 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
1089 p_data => x_msg_data,
1090 p_encoded => 'F');
1091
1092 END Delete_MSite_Section_Item;
1093
1094 END IBE_DSP_MSITE_SCT_ITEM_PVT;