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