[Home] [Help]
PACKAGE BODY: APPS.JTF_DSP_MSITE_SCT_SECT_PVT
Source
1 PACKAGE BODY JTF_DSP_MSITE_SCT_SECT_PVT AS
2 /* $Header: JTFVCMSB.pls 115.15 2004/07/09 18:51:37 applrt ship $ */
3
4
5 --
6 --
7 -- Start of Comments
8 --
9 -- NAME
10 -- JTF_DSP_MSITE_SCT_SECT_PVT
11 --
12 -- PURPOSE
13 -- Private API for saving, retrieving and updating mini site
14 -- section sections.
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 -- sections
20
21 -- HISTORY
22 -- 11/28/99 VPALAIYA Created
23 -- **************************************************************************
24
25 G_PKG_NAME CONSTANT VARCHAR2(30):= 'JTF_DSP_MSITE_SCT_SECT_PVT';
26 G_FILE_NAME CONSTANT VARCHAR2(12):= 'JTFVCMSB.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-section
41 -- ****************************************************************************
42
43 PROCEDURE insert_row
44 (
45 p_mini_site_section_section_id IN NUMBER,
46 p_object_version_number IN NUMBER,
47 p_mini_site_id IN NUMBER,
48 p_parent_section_id IN NUMBER,
49 p_child_section_id IN NUMBER,
50 p_start_date_active IN DATE,
51 p_end_date_active IN DATE,
52 p_level_number IN NUMBER,
53 p_sort_order IN NUMBER,
54 p_concat_ids IN VARCHAR2,
55 p_creation_date IN DATE,
56 p_created_by IN NUMBER,
57 p_last_update_date IN DATE,
58 p_last_updated_by IN NUMBER,
59 p_last_update_login IN NUMBER,
60 x_rowid OUT VARCHAR2,
61 x_mini_site_section_section_id OUT NUMBER
62 )
63 IS
64 CURSOR c IS SELECT rowid FROM jtf_dsp_msite_sct_sects
65 WHERE mini_site_section_section_id = x_mini_site_section_section_id;
66 CURSOR c2 IS SELECT jtf_dsp_msite_sct_sects_s1.nextval FROM dual;
67
68 BEGIN
69
70 -- Primary key validation check
71 x_mini_site_section_section_id := p_mini_site_section_section_id;
72 IF ((x_mini_site_section_section_id IS NULL) OR
73 (x_mini_site_section_section_id = FND_API.G_MISS_NUM))
74 THEN
75 OPEN c2;
76 FETCH c2 INTO x_mini_site_section_section_id;
77 CLOSE c2;
78 END IF;
79
80 -- insert base
81 INSERT INTO jtf_dsp_msite_sct_sects
82 (
83 mini_site_section_section_id,
84 object_version_number,
85 mini_site_id,
86 parent_section_id,
87 child_section_id,
88 start_date_active,
89 end_date_active,
90 level_number,
91 sort_order,
92 concat_ids,
93 creation_date,
94 created_by,
95 last_update_date,
96 last_updated_by,
97 last_update_login
98 )
99 VALUES
100 (
101 x_mini_site_section_section_id,
102 p_object_version_number,
103 p_mini_site_id,
104 decode(p_parent_section_id, FND_API.G_MISS_NUM, NULL, p_parent_section_id),
105 p_child_section_id,
106 p_start_date_active,
107 decode(p_end_date_active, FND_API.G_MISS_DATE, NULL, p_end_date_active),
108 decode(p_level_number, FND_API.G_MISS_NUM, NULL, p_level_number),
109 decode(p_sort_order, FND_API.G_MISS_NUM, NULL, p_sort_order),
110 decode(p_concat_ids, FND_API.G_MISS_CHAR, NULL, p_concat_ids),
111 decode(p_creation_date, FND_API.G_MISS_DATE, sysdate, NULL, sysdate,
112 p_creation_date),
113 decode(p_created_by, FND_API.G_MISS_NUM, FND_GLOBAL.user_id,
114 NULL, FND_GLOBAL.user_id, p_created_by),
115 decode(p_last_update_date, FND_API.G_MISS_DATE, sysdate, NULL, sysdate,
116 p_last_update_date),
117 decode(p_last_updated_by, FND_API.G_MISS_NUM, FND_GLOBAL.user_id,
118 NULL, FND_GLOBAL.user_id, p_last_updated_by),
119 decode(p_last_update_login, FND_API.G_MISS_NUM, FND_GLOBAL.login_id,
120 NULL, FND_GLOBAL.login_id, p_last_update_login)
121 );
122
123 OPEN c;
124 FETCH c INTO x_rowid;
125 IF (c%NOTFOUND)
126 THEN
127 CLOSE c;
128 RAISE NO_DATA_FOUND;
129 END IF;
130 CLOSE c;
131
132 END insert_row;
133
134 -- ****************************************************************************
135 -- update row
136 -- ****************************************************************************
137
138 PROCEDURE update_row
139 (
140 p_mini_site_section_section_id IN NUMBER,
141 p_object_version_number IN NUMBER := FND_API.G_MISS_NUM,
142 p_start_date_active IN DATE,
143 p_end_date_active IN DATE,
144 p_level_number IN NUMBER,
145 p_sort_order IN NUMBER,
146 p_concat_ids IN VARCHAR2,
147 p_last_update_date IN DATE,
148 p_last_updated_by IN NUMBER,
149 p_last_update_login IN NUMBER
150 )
151 IS
152 BEGIN
153
154 -- update base
155 UPDATE jtf_dsp_msite_sct_sects SET
156 object_version_number = object_version_number + 1,
157 level_number = decode(p_level_number, FND_API.G_MISS_NUM,
158 level_number, p_level_number),
159 sort_order = decode(p_sort_order, FND_API.G_MISS_NUM,
160 sort_order, p_sort_order),
161 concat_ids = decode(p_concat_ids, FND_API.G_MISS_CHAR,
162 concat_ids, p_concat_ids),
163 start_date_active = decode(p_start_date_active, FND_API.G_MISS_DATE,
164 start_date_active, p_start_date_active),
165 end_date_active = decode(p_end_date_active, FND_API.G_MISS_DATE,
166 end_date_active, p_end_date_active),
167 last_update_date = decode(p_last_update_date, FND_API.G_MISS_DATE,
168 sysdate, NULL, sysdate, p_last_update_date),
169 last_updated_by = decode(p_last_updated_by, FND_API.G_MISS_NUM,
170 FND_GLOBAL.user_id, NULL, FND_GLOBAL.user_id,
171 p_last_updated_by),
172 last_update_login = decode(p_last_update_login, FND_API.G_MISS_NUM,
173 FND_GLOBAL.login_id, NULL,
174 FND_GLOBAL.login_id, p_last_update_login)
175 WHERE mini_site_section_section_id = p_mini_site_section_section_id
176 AND object_version_number = decode(p_object_version_number,
177 FND_API.G_MISS_NUM,
178 object_version_number,
179 p_object_version_number);
180
181 IF (sql%NOTFOUND) THEN
182 RAISE NO_DATA_FOUND;
183 END IF;
184
185 END update_row;
186
187
188 -- ****************************************************************************
189 -- delete row
190 -- ****************************************************************************
191
192 PROCEDURE delete_row
193 (
194 p_mini_site_section_section_id IN NUMBER
195 )
196 IS
197 BEGIN
198
199 DELETE FROM jtf_dsp_msite_sct_sects
200 WHERE mini_site_section_section_id = p_mini_site_section_section_id;
201
202 IF (sql%NOTFOUND) THEN
203 RAISE NO_DATA_FOUND;
204 END IF;
205
206 END delete_row;
207
208 --
209 -- To be called from jtfmste.lct only
210 --
211 PROCEDURE load_row
212 (
213 p_owner IN VARCHAR2,
214 p_mini_site_section_section_id IN NUMBER,
215 p_object_version_number IN NUMBER := FND_API.G_MISS_NUM,
216 p_mini_site_id IN NUMBER,
217 p_parent_section_id IN NUMBER,
218 p_child_section_id IN NUMBER,
219 p_start_date_active IN DATE,
220 p_end_date_active IN DATE,
221 p_level_number IN NUMBER,
222 p_sort_order IN NUMBER,
223 p_concat_ids IN VARCHAR2
224 )
225 IS
226 l_user_id NUMBER := 0;
227 l_rowid VARCHAR2(256);
228 l_mini_site_section_section_id NUMBER;
229 l_object_version_number NUMBER := 1;
230 BEGIN
231
232 IF (p_owner = 'SEED') THEN
233 l_user_id := 1;
234 END IF;
235
236 IF ((p_object_version_number IS NOT NULL) AND
237 (p_object_version_number <> FND_API.G_MISS_NUM))
238 THEN
239 l_object_version_number := p_object_version_number;
240 END IF;
241
242 BEGIN
243
244 update_row
245 (
246 p_mini_site_section_section_id => p_mini_site_section_section_id,
247 p_object_version_number => p_object_version_number,
248 p_start_date_active => p_start_date_active,
249 p_end_date_active => p_end_date_active,
250 p_level_number => p_level_number,
251 p_sort_order => p_sort_order,
252 p_concat_ids => p_concat_ids,
253 p_last_update_date => sysdate,
254 p_last_updated_by => l_user_id,
255 p_last_update_login => 0
256 );
257
258 EXCEPTION
259
260 WHEN NO_DATA_FOUND THEN
261
262 insert_row
263 (
264 p_mini_site_section_section_id => p_mini_site_section_section_id,
265 p_object_version_number => l_object_version_number,
266 p_mini_site_id => p_mini_site_id,
267 p_parent_section_id => p_parent_section_id,
268 p_child_section_id => p_child_section_id,
269 p_start_date_active => p_start_date_active,
270 p_end_date_active => p_end_date_active,
271 p_level_number => p_level_number,
272 p_sort_order => p_sort_order,
273 p_concat_ids => p_concat_ids,
274 p_creation_date => sysdate,
275 p_created_by => l_user_id,
276 p_last_update_date => sysdate,
277 p_last_updated_by => l_user_id,
278 p_last_update_login => 0,
279 x_rowid => l_rowid,
280 x_mini_site_section_section_id => l_mini_site_section_section_id
281 );
282 END;
283
284 END load_row;
285
286
287 -- ****************************************************************************
288 --*****************************************************************************
289 --
290 --APIs
291 --
292 -- 1. Create_MSite_Section_Section
293 -- 2. Update_MSite_Section_Section
294 -- 3. Delete_MSite_Section_Section
295 -- 4. Check_Duplicate_Entry
296 --
297 --*****************************************************************************
298 --*****************************************************************************
299
300
301 --*****************************************************************************
302 -- PROCEDURE Check_Duplicate_Entry()
303 --*****************************************************************************
304
305 --
306 -- x_return_status = FND_API.G_RET_STS_SUCCESS, if the section is duplicate
307 -- x_return_status = FND_API.G_RET_STS_ERROR, if the section is not duplicate
308 --
309 --
310 PROCEDURE Check_Duplicate_Entry
311 (
312 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
313 p_mini_site_id IN NUMBER,
314 p_parent_section_id IN NUMBER,
315 p_child_section_id IN NUMBER,
316 x_return_status OUT VARCHAR2,
317 x_msg_count OUT NUMBER,
318 x_msg_data OUT VARCHAR2
319 )
320 IS
321 l_api_name CONSTANT VARCHAR2(30) := 'Check_Duplicate_Entry';
322 l_api_version CONSTANT NUMBER := 1.0;
323
324 l_tmp_msite_sct_sect_id NUMBER;
325 BEGIN
326
327 -- Initialize message list if p_init_msg_list is set to TRUE.
328 IF FND_API.to_Boolean(p_init_msg_list) THEN
329 FND_MSG_PUB.initialize;
330 END IF;
331
332 -- Initialize API return status to error, i.e, its not duplicate
333 x_return_status := FND_API.G_RET_STS_ERROR;
334
335 -- Check duplicate entry
336 BEGIN
337
338 IF (p_parent_section_id IS NOT NULL) THEN
339
340 SELECT mini_site_section_section_id INTO l_tmp_msite_sct_sect_id
341 FROM jtf_dsp_msite_sct_sects
342 WHERE mini_site_id = p_mini_site_id
343 AND parent_section_id = p_parent_section_id
344 AND child_section_id = p_child_section_id;
345
346 ELSE
347
348 SELECT mini_site_section_section_id INTO l_tmp_msite_sct_sect_id
349 FROM jtf_dsp_msite_sct_sects
350 WHERE mini_site_id = p_mini_site_id
351 AND parent_section_id IS NULL
352 AND child_section_id = p_child_section_id;
353
354 END IF;
355
356 EXCEPTION
357
358 WHEN NO_DATA_FOUND THEN
359 -- not duplicate
360 -- do nothing
361 NULL;
362
363 WHEN OTHERS THEN
364 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
365
366 END;
367
368 IF (l_tmp_msite_sct_sect_id IS NOT NULL) THEN
369 -- found duplicate
370 RAISE FND_API.G_EXC_ERROR;
371 END IF;
372
373 EXCEPTION
374
375 WHEN FND_API.G_EXC_ERROR THEN
376 x_return_status := FND_API.G_RET_STS_SUCCESS; -- found duplicate
377 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
378 p_data => x_msg_data,
379 p_encoded => 'F');
380
381 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
382 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
383 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
384 p_data => x_msg_data,
385 p_encoded => 'F');
386
387 WHEN OTHERS THEN
388 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
389
390 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
391 THEN
392 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
393 END IF;
394
395 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
396 p_data => x_msg_data,
397 p_encoded => 'F');
398
399 END Check_Duplicate_Entry;
400
401
402 --*****************************************************************************
403 -- PROCEDURE Validate_Create()
404 --*****************************************************************************
405 -- IF x_return_status := FND_API.G_RET_STS_ERROR, then invalid
406 -- IF x_return_status := FND_API.G_RET_STS_SUCCESS, then valid
407
408 PROCEDURE Validate_Create
409 (
410 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
411 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
412 p_mini_site_id IN NUMBER,
413 p_parent_section_id IN NUMBER,
414 p_child_section_id IN NUMBER,
415 p_start_date_active IN DATE,
416 p_end_date_active IN DATE,
417 p_level_number IN NUMBER,
418 p_sort_order IN NUMBER,
419 p_concat_ids IN VARCHAR2,
420 x_return_status OUT VARCHAR2,
421 x_msg_count OUT NUMBER,
422 x_msg_data OUT VARCHAR2
423 )
424 IS
425 l_api_name CONSTANT VARCHAR2(30) := 'Validate_Create';
426 l_api_version CONSTANT NUMBER := 1.0;
427 l_msg_count NUMBER;
428 l_msg_data VARCHAR2(2000);
429
430 l_mini_site_id NUMBER;
431 l_parent_section_id NUMBER;
432 l_child_section_id NUMBER;
433 l_return_status VARCHAR2(1);
434 BEGIN
435
436 -- Initialize message list if p_init_msg_list is set to TRUE.
437 IF FND_API.to_Boolean(p_init_msg_list) THEN
438 FND_MSG_PUB.initialize;
439 END IF;
440
441 -- Initialize API return status to success
442 x_return_status := FND_API.G_RET_STS_SUCCESS;
443
444 --
445 -- Check null values for required fields
446 --
447
448 -- mini site id
449 IF ((p_mini_site_id IS NULL) OR
450 (p_mini_site_id = FND_API.G_MISS_NUM))
451 THEN
452 FND_MESSAGE.Set_Name('JTF', 'JTF_DSP_INVALID_MSITE_ID');
453 FND_MSG_PUB.Add;
454 RAISE FND_API.G_EXC_ERROR;
455 END IF;
456
457 -- child section id
458 IF ((p_child_section_id IS NULL) OR
459 (p_child_section_id = FND_API.G_MISS_NUM))
460 THEN
461 FND_MESSAGE.Set_Name('JTF', 'JTF_DSP_INVALID_CHILD_SCT_ID');
462 FND_MSG_PUB.Add;
463 RAISE FND_API.G_EXC_ERROR;
464 END IF;
465
466 -- start_date_active
467 IF ((p_start_date_active IS NULL) OR
468 (p_start_date_active = FND_API.G_MISS_DATE))
469 THEN
470 FND_MESSAGE.Set_Name('JTF', 'JTF_DSP_INVALID_START_DATE');
471 FND_MSG_PUB.Add;
472 RAISE FND_API.G_EXC_ERROR;
473 END IF;
474
475 --
476 -- non-null field validation
477 --
478
479 -- level number
480 IF ((p_level_number IS NOT NULL) AND
481 (p_level_number <> FND_API.G_MISS_NUM))
482 THEN
483 IF(p_level_number < 0) THEN
484 FND_MESSAGE.Set_Name('JTF', 'JTF_DSP_INVALID_SCT_LVL_NUM');
485 FND_MSG_PUB.Add;
486 RAISE FND_API.G_EXC_ERROR;
487 END IF;
488 END IF;
489
490 -- sort order
491 IF ((p_sort_order IS NOT NULL) AND
492 (p_sort_order <> FND_API.G_MISS_NUM))
493 THEN
494 IF(p_sort_order < 0) THEN
495 FND_MESSAGE.Set_Name('JTF', 'JTF_DSP_INVALID_SCT_SORT_ORDER');
496 FND_MSG_PUB.Add;
497 RAISE FND_API.G_EXC_ERROR;
498 END IF;
499 END IF;
500
501 --
502 -- Foreign key integrity constraint check
503 --
504
505 -- mini site id
506 -- note that mini site id cannot be null due to previous checks
507 BEGIN
508 SELECT msite_id INTO l_mini_site_id FROM jtf_msites_b
509 WHERE msite_id = p_mini_site_id;
510 EXCEPTION
511 WHEN NO_DATA_FOUND THEN
512 FND_MESSAGE.Set_Name('JTF', 'JTF_DSP_SCT_NO_MSITE_ID');
513 FND_MESSAGE.Set_Token('MINI_SITE_ID', p_mini_site_id);
514 FND_MSG_PUB.Add;
515 RAISE FND_API.G_EXC_ERROR;
516 WHEN OTHERS THEN
517 FND_MESSAGE.Set_Name('JTF', 'JTF_DSP_SCT_GET_MSITE_ID');
518 FND_MESSAGE.Set_Token('MINI_SITE_ID', p_mini_site_id);
519 FND_MSG_PUB.Add;
520 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
521 END;
522
523 -- parent section id
524 IF ((p_parent_section_id IS NOT NULL) AND
525 (p_parent_section_id <> FND_API.G_MISS_NUM))
526 THEN
527 BEGIN
528 SELECT section_id INTO l_parent_section_id FROM jtf_dsp_sections_b
529 WHERE section_id = p_parent_section_id;
530 EXCEPTION
531 WHEN NO_DATA_FOUND THEN
532 FND_MESSAGE.Set_Name('JTF', 'JTF_DSP_SCT_NO_SCT_ID');
533 FND_MESSAGE.Set_Token('SECTION_ID', p_parent_section_id);
534 FND_MSG_PUB.Add;
535 RAISE FND_API.G_EXC_ERROR;
536 WHEN OTHERS THEN
537 FND_MESSAGE.Set_Name('JTF', 'JTF_DSP_SCT_GET_SCT_ID');
538 FND_MESSAGE.Set_Token('SECTION_ID', p_parent_section_id);
539 FND_MSG_PUB.Add;
540 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
541 END;
542 END IF;
543
544 -- child section id
545 -- note that child section id cannot be null due to previous checks
546 BEGIN
547 SELECT section_id INTO l_child_section_id FROM jtf_dsp_sections_b
548 WHERE section_id = p_child_section_id;
549 EXCEPTION
550 WHEN NO_DATA_FOUND THEN
551 FND_MESSAGE.Set_Name('JTF', 'JTF_DSP_SCT_NO_SCT_ID');
552 FND_MESSAGE.Set_Token('SECTION_ID', p_child_section_id);
553 FND_MSG_PUB.Add;
554 RAISE FND_API.G_EXC_ERROR;
555 WHEN OTHERS THEN
556 FND_MESSAGE.Set_Name('JTF', 'JTF_DSP_SCT_GET_SCT_ID');
557 FND_MESSAGE.Set_Token('SECTION_ID', p_child_section_id);
558 FND_MSG_PUB.Add;
559 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
560 END;
561
562 -- Validate if the entry is duplicate
563 Check_Duplicate_Entry(p_init_msg_list => FND_API.G_FALSE,
564 p_mini_site_id => p_mini_site_id,
565 p_parent_section_id => p_parent_section_id,
566 p_child_section_id => p_child_section_id,
567 x_return_status => l_return_status,
568 x_msg_count => l_msg_count,
569 x_msg_data => l_msg_data);
570
571 IF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
572 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
573 ELSIF (l_return_status = FND_API.G_RET_STS_SUCCESS) THEN
574 FND_MESSAGE.Set_Name('JTF', 'JTF_DSP_DUPLICATE_ENTRY');
575 FND_MSG_PUB.Add;
576 RAISE FND_API.G_EXC_ERROR; -- duplicate entry
577 END IF;
578
579 EXCEPTION
580
581 WHEN FND_API.G_EXC_ERROR THEN
582 x_return_status := FND_API.G_RET_STS_ERROR;
583 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
584 p_data => x_msg_data,
585 p_encoded => 'F');
586
587 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
588 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
589 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
590 p_data => x_msg_data,
591 p_encoded => 'F');
592
593 WHEN OTHERS THEN
594 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
595
596 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
597 THEN
598 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
599 END IF;
600
601 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
602 p_data => x_msg_data,
603 p_encoded => 'F');
604
605 END Validate_Create;
606
607
608 --*****************************************************************************
609 -- PROCEDURE Validate_Update()
610 --*****************************************************************************
611 -- IF x_return_status := FND_API.G_RET_STS_ERROR, then invalid
612 -- IF x_return_status := FND_API.G_RET_STS_SUCCESS, then valid
613
614 PROCEDURE Validate_Update
615 (
616 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
617 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
618 p_mini_site_section_section_id IN NUMBER,
619 p_object_version_number IN NUMBER,
620 p_start_date_active IN DATE,
621 p_end_date_active IN DATE,
622 p_level_number IN NUMBER,
623 p_sort_order IN NUMBER,
624 p_concat_ids IN VARCHAR2,
625 x_return_status OUT VARCHAR2,
626 x_msg_count OUT NUMBER,
627 x_msg_data OUT VARCHAR2
628 )
629 IS
630 l_api_name CONSTANT VARCHAR2(30) := 'Validate_Update';
631 l_api_version CONSTANT NUMBER := 1.0;
632 l_msg_count NUMBER;
633 l_msg_data VARCHAR2(2000);
634
635 l_msite_sct_sect_id NUMBER;
636 l_mini_site_id NUMBER;
637 l_parent_section_id NUMBER;
638 l_child_section_id NUMBER;
639 l_return_status VARCHAR2(1);
640
641 BEGIN
642
643 -- Initialize message list if p_init_msg_list is set to TRUE.
644 IF FND_API.to_Boolean(p_init_msg_list) THEN
645 FND_MSG_PUB.initialize;
646 END IF;
647
648 -- Initialize API return status to success
649 x_return_status := FND_API.G_RET_STS_SUCCESS;
650
651 --
652 -- Check null values for required fields
653 --
654
655 -- mini_site_section_section_id
656 IF (p_mini_site_section_section_id IS NULL) THEN
657 FND_MESSAGE.Set_Name('JTF', 'JTF_DSP_NULL_PRIMARY_KEY');
658 FND_MSG_PUB.Add;
659 RAISE FND_API.G_EXC_ERROR;
660 END IF;
661
662 -- start_date_active
663 IF (p_start_date_active IS NULL) THEN
664 FND_MESSAGE.Set_Name('JTF', 'JTF_DSP_INVALID_START_DATE');
665 FND_MSG_PUB.Add;
666 RAISE FND_API.G_EXC_ERROR;
667 END IF;
668
669 --
670 -- non-null field validation
671 --
672
673 -- level number
674 IF ((p_level_number IS NOT NULL) AND
675 (p_level_number <> FND_API.G_MISS_NUM))
676 THEN
677 IF(p_level_number < 0) THEN
678 FND_MESSAGE.Set_Name('JTF', 'JTF_DSP_INVALID_SCT_LVL_NUM');
679 FND_MSG_PUB.Add;
680 RAISE FND_API.G_EXC_ERROR;
681 END IF;
682 END IF;
683
684 -- sort order
685 IF ((p_sort_order IS NOT NULL) AND
686 (p_sort_order <> FND_API.G_MISS_NUM))
687 THEN
688 IF(p_sort_order < 0) THEN
689 FND_MESSAGE.Set_Name('JTF', 'JTF_DSP_INVALID_SCT_SORT_ORDER');
690 FND_MSG_PUB.Add;
691 RAISE FND_API.G_EXC_ERROR;
692 END IF;
693 END IF;
694
695 EXCEPTION
696
697 WHEN FND_API.G_EXC_ERROR THEN
698 x_return_status := FND_API.G_RET_STS_ERROR;
699 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
700 p_data => x_msg_data,
701 p_encoded => 'F');
702
703 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
704 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
705 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
706 p_data => x_msg_data,
707 p_encoded => 'F');
708
709 WHEN OTHERS THEN
710 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
711
712 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
713 THEN
714 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
715 END IF;
716
717 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
718 p_data => x_msg_data,
719 p_encoded => 'F');
720
721 END Validate_Update;
722
723
724 -- ****************************************************************************
725 --*****************************************************************************
726
727 PROCEDURE Create_MSite_Section_Section
728 (
729 p_api_version IN NUMBER,
730 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
731 p_commit IN VARCHAR2 := FND_API.G_FALSE,
732 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
733 p_mini_site_id IN NUMBER,
734 p_parent_section_id IN NUMBER := FND_API.G_MISS_NUM,
735 p_child_section_id IN NUMBER,
736 p_start_date_active IN DATE,
737 p_end_date_active IN DATE := FND_API.G_MISS_DATE,
738 p_level_number IN NUMBER := FND_API.G_MISS_NUM,
739 p_sort_order IN NUMBER := FND_API.G_MISS_NUM,
740 p_concat_ids IN VARCHAR2 := FND_API.G_MISS_CHAR,
741 x_mini_site_section_section_id OUT NUMBER,
742 x_return_status OUT VARCHAR2,
743 x_msg_count OUT NUMBER,
744 x_msg_data OUT VARCHAR2
745 )
746 IS
747 l_api_name CONSTANT VARCHAR2(30)
748 := 'Create_MSite_Section_Section';
749 l_api_version CONSTANT NUMBER := 1.0;
750 l_msg_count NUMBER;
751 l_msg_data VARCHAR2(2000);
752 l_return_status VARCHAR2(1);
753
754 l_object_version_number CONSTANT NUMBER := 1;
755 l_rowid VARCHAR2(30);
756
757 BEGIN
758
759 -- Standard Start of API savepoint
760 SAVEPOINT CREATE_MSITE_SCT_SECT_PVT;
761
762 -- Standard call to check for call compatibility.
763 IF NOT FND_API.Compatible_API_Call(l_api_version,
764 p_api_version,
765 l_api_name,
766 G_PKG_NAME)
767 THEN
768 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
769 END IF;
770
771 -- Initialize message list if p_init_msg_list is set to TRUE.
772 IF FND_API.to_Boolean(p_init_msg_list) THEN
773 FND_MSG_PUB.initialize;
774 END IF;
775
776 -- Initialize API return status to success
777 x_return_status := FND_API.G_RET_STS_SUCCESS;
778
779 -- API body
780 -- CALL FLOW :
781 -- 1. Validate
782 -- 2. Insert row
783 --
784
785 --
786 -- 1. Validate
787 --
788 Validate_Create
789 (
790 p_init_msg_list => FND_API.G_FALSE,
791 p_validation_level => p_validation_level,
792 p_mini_site_id => p_mini_site_id,
793 p_parent_section_id => p_parent_section_id,
794 p_child_section_id => p_child_section_id,
795 p_start_date_active => p_start_date_active,
796 p_end_date_active => p_end_date_active,
797 p_level_number => p_level_number,
798 p_sort_order => p_sort_order,
799 p_concat_ids => p_concat_ids,
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_MSS_INVALID_CREATE');
809 FND_MSG_PUB.Add;
810 RAISE FND_API.G_EXC_ERROR; -- invalid
811 END IF;
812
813 --
814 -- 2. Insert row
815 --
816 BEGIN
817 insert_row
818 (
819 FND_API.G_MISS_NUM,
820 l_object_version_number,
821 p_mini_site_id,
822 p_parent_section_id,
823 p_child_section_id,
824 p_start_date_active,
825 p_end_date_active,
826 p_level_number,
827 p_sort_order,
828 p_concat_ids,
829 SYSDATE,
830 FND_GLOBAL.user_id,
831 SYSDATE,
832 FND_GLOBAL.user_id,
833 FND_GLOBAL.login_id,
834 l_rowid,
835 x_mini_site_section_section_id
836 );
837 EXCEPTION
838 WHEN NO_DATA_FOUND THEN
839 FND_MESSAGE.Set_Name('JTF', 'JTF_DSP_MSS_INSERT_FAIL');
840 FND_MSG_PUB.Add;
841 RAISE FND_API.G_EXC_ERROR;
842 WHEN OTHERS THEN
843 FND_MESSAGE.Set_Name('JTF', 'JTF_DSP_MSS_INSERT_FAIL');
844 FND_MSG_PUB.Add;
845 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
846 END;
847
848 --
849 -- End of main API body.
850
851 -- Standard check of p_commit.
852 IF (FND_API.To_Boolean(p_commit)) THEN
853 COMMIT WORK;
854 END IF;
855
856 -- Standard call to get message count and if count is 1, get message info.
857 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
858 p_data => x_msg_data,
859 p_encoded => 'F');
860
861 EXCEPTION
862
863 WHEN FND_API.G_EXC_ERROR THEN
864 ROLLBACK TO CREATE_MSITE_SCT_SECT_PVT;
865 x_return_status := FND_API.G_RET_STS_ERROR;
866 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
867 p_data => x_msg_data,
868 p_encoded => 'F');
869
870 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
871 ROLLBACK TO CREATE_MSITE_SCT_SECT_PVT;
872 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
873 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
874 p_data => x_msg_data,
875 p_encoded => 'F');
876
877 WHEN OTHERS THEN
878 ROLLBACK TO CREATE_MSITE_SCT_SECT_PVT;
879 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
880
881 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
882 THEN
883 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
884 END IF;
885
886 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
887 p_data => x_msg_data,
888 p_encoded => 'F');
889
890 END Create_MSite_Section_Section;
891
892 PROCEDURE Update_MSite_Section_Section
893 (
894 p_api_version IN NUMBER,
895 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
896 p_commit IN VARCHAR2 := FND_API.G_FALSE,
897 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
898 p_mini_site_section_section_id IN NUMBER := FND_API.G_MISS_NUM,
899 p_object_version_number IN NUMBER,
900 p_mini_site_id IN NUMBER := FND_API.G_MISS_NUM,
901 p_parent_section_id IN NUMBER := FND_API.G_MISS_NUM,
902 p_child_section_id IN NUMBER := FND_API.G_MISS_NUM,
903 p_start_date_active IN DATE := FND_API.G_MISS_DATE,
904 p_end_date_active IN DATE := FND_API.G_MISS_DATE,
905 p_level_number IN NUMBER := FND_API.G_MISS_NUM,
906 p_sort_order IN NUMBER := FND_API.G_MISS_NUM,
907 p_concat_ids IN VARCHAR2 := FND_API.G_MISS_CHAR,
908 x_return_status OUT VARCHAR2,
909 x_msg_count OUT NUMBER,
910 x_msg_data OUT VARCHAR2
911 )
912 IS
913 l_api_name CONSTANT VARCHAR2(30) := 'Update_MSite_Section_Section';
914 l_api_version CONSTANT NUMBER := 1.0;
915 l_msg_count NUMBER;
916 l_msg_data VARCHAR2(2000);
917
918 l_msite_sct_sect_id NUMBER;
919 l_return_status VARCHAR2(1);
920
921 BEGIN
922
923 -- Standard Start of API savepoint
924 SAVEPOINT UPDATE_MSITE_SCT_SECT_PVT;
925
926 -- Standard call to check for call compatibility.
927 IF NOT FND_API.Compatible_API_Call(l_api_version,
928 p_api_version,
929 l_api_name,
930 G_PKG_NAME)
931 THEN
932 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
933 END IF;
934
935 -- Initialize message list if p_init_msg_list is set to TRUE.
936 IF FND_API.to_Boolean(p_init_msg_list) THEN
937 FND_MSG_PUB.initialize;
938 END IF;
939
940 -- Initialize API return status to success
941 x_return_status := FND_API.G_RET_STS_SUCCESS;
942
943 -- API body
944 -- CALL FLOW :
945 -- 1. Check if either mini_site_section_section_id or combination of
946 -- mini_site_id, parent_section_id and child_section_id is specified
947 -- 2. Update row
948 --
949
950 -- 1. Check if either mini_site_section_section_id or combination of
951 -- mini_site_id, parent_section_id and child_section_id is specified
952 IF ((p_mini_site_section_section_id IS NOT NULL) AND
953 (p_mini_site_section_section_id <> FND_API.G_MISS_NUM))
954 THEN
955 -- mini_site_section_section_id specified, continue
956 l_msite_sct_sect_id := p_mini_site_section_section_id;
957 ELSIF ((p_mini_site_id IS NOT NULL) AND
958 (p_mini_site_id <> FND_API.G_MISS_NUM) AND
959 (p_parent_section_id <> FND_API.G_MISS_NUM) AND -- parent can be NULL
960 (p_child_section_id IS NOT NULL) AND
961 (p_child_section_id <> FND_API.G_MISS_NUM))
962 THEN
963 -- If combination of mini_site_id, parent_section_id and child_section_id
964 -- is specified, then query for mini_site_section_section_id
965 BEGIN
966 IF (p_parent_section_id IS NOT NULL) THEN
967
968 SELECT mini_site_section_section_id INTO l_msite_sct_sect_id
969 FROM jtf_dsp_msite_sct_sects
970 WHERE mini_site_id = p_mini_site_id
971 AND parent_section_id = p_parent_section_id
972 AND child_section_id = p_child_section_id;
973 ELSE
974
975 SELECT mini_site_section_section_id INTO l_msite_sct_sect_id
976 FROM jtf_dsp_msite_sct_sects
977 WHERE mini_site_id = p_mini_site_id
978 AND parent_section_id IS NULL
979 AND child_section_id = p_child_section_id;
980
981 END IF;
982
983 EXCEPTION
984 WHEN NO_DATA_FOUND THEN
985 FND_MESSAGE.Set_Name('JTF', 'JTF_DSP_NO_MSS_ID');
986 FND_MESSAGE.Set_Token('MINI_SITE_ID', p_mini_site_id);
987 IF (p_parent_section_id IS NOT NULL) THEN
988 FND_MESSAGE.Set_Token('PARENT_SECTION_ID', p_parent_section_id);
989 ELSE
990 FND_MESSAGE.Set_Token('PARENT_SECTION_ID', 'NULL');
991 END IF;
992 FND_MESSAGE.Set_Token('CHILD_SECTION_ID', p_child_section_id);
993 FND_MSG_PUB.Add;
994 RAISE FND_API.G_EXC_ERROR;
995 WHEN OTHERS THEN
996 FND_MESSAGE.Set_Name('JTF', 'JTF_DSP_NO_MSS_ID');
997 FND_MESSAGE.Set_Token('MINI_SITE_ID', p_mini_site_id);
998 IF (p_parent_section_id IS NOT NULL) THEN
999 FND_MESSAGE.Set_Token('PARENT_SECTION_ID', p_parent_section_id);
1000 ELSE
1001 FND_MESSAGE.Set_Token('PARENT_SECTION_ID', 'NULL');
1002 END IF;
1003 FND_MESSAGE.Set_Token('CHILD_SECTION_ID', p_child_section_id);
1004 FND_MSG_PUB.Add;
1005 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1006 END;
1007 ELSE
1008 -- neither mini_site_section_section_id nor combination of
1009 -- mini_site_id, parent_section_id and child_section_id is specified
1010 FND_MESSAGE.Set_Name('JTF', 'JTF_DSP_NO_MSS_IDS_SPEC');
1011 FND_MSG_PUB.Add;
1012 RAISE FND_API.G_EXC_ERROR;
1013 END IF;
1014
1015
1016 --
1017 -- 1. Validate the input data
1018 --
1019 Validate_Update
1020 (
1021 p_init_msg_list => FND_API.G_FALSE,
1022 p_validation_level => p_validation_level,
1023 p_mini_site_section_section_id => l_msite_sct_sect_id,
1024 p_object_version_number => p_object_version_number,
1025 p_start_date_active => p_start_date_active,
1026 p_end_date_active => p_end_date_active,
1027 p_level_number => p_level_number,
1028 p_sort_order => p_sort_order,
1029 p_concat_ids => p_concat_ids,
1030 x_return_status => l_return_status,
1031 x_msg_count => l_msg_count,
1032 x_msg_data => l_msg_data
1033 );
1034
1035 IF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
1036 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1037 ELSIF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
1038 FND_MESSAGE.Set_Name('JTF', 'JTF_DSP_MSS_INVALID_UPDATE');
1039 FND_MSG_PUB.Add;
1040 RAISE FND_API.G_EXC_ERROR; -- invalid
1041 END IF;
1042
1043 -- 2. update row
1044 BEGIN
1045 update_row
1046 (
1047 l_msite_sct_sect_id,
1048 p_object_version_number,
1049 p_start_date_active,
1050 p_end_date_active,
1051 p_level_number,
1052 p_sort_order,
1053 p_concat_ids,
1054 SYSDATE,
1055 FND_GLOBAL.user_id,
1056 FND_GLOBAL.login_id
1057 );
1058 EXCEPTION
1059 WHEN NO_DATA_FOUND THEN
1060 FND_MESSAGE.Set_Name('JTF', 'JTF_DSP_MSS_UPDATE_FAIL');
1061 FND_MSG_PUB.Add;
1062 RAISE FND_API.G_EXC_ERROR;
1063 WHEN OTHERS THEN
1064 FND_MESSAGE.Set_Name('JTF', 'JTF_DSP_MSS_UPDATE_FAIL');
1065 FND_MSG_PUB.Add;
1066 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1067 END;
1068
1069 --
1070 -- End of main API body.
1071
1072 -- Standard check of p_commit.
1073 IF (FND_API.To_Boolean(p_commit)) THEN
1074 COMMIT WORK;
1075 END IF;
1076
1077 -- Standard call to get message count and if count is 1, get message info.
1078 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
1079 p_data => x_msg_data,
1080 p_encoded => 'F');
1081
1082 EXCEPTION
1083
1084 WHEN FND_API.G_EXC_ERROR THEN
1085 ROLLBACK TO UPDATE_MSITE_SCT_SECT_PVT;
1086 x_return_status := FND_API.G_RET_STS_ERROR;
1087 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
1088 p_data => x_msg_data,
1089 p_encoded => 'F');
1090
1091 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1092 ROLLBACK TO UPDATE_MSITE_SCT_SECT_PVT;
1093 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1094 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
1095 p_data => x_msg_data,
1096 p_encoded => 'F');
1097
1098 WHEN OTHERS THEN
1099 ROLLBACK TO UPDATE_MSITE_SCT_SECT_PVT;
1100 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1101
1102 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1103 THEN
1104 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
1105 END IF;
1106
1107 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
1108 p_data => x_msg_data,
1109 p_encoded => 'F');
1110
1111 END Update_MSite_Section_Section;
1112
1113
1114 PROCEDURE Delete_MSite_Section_Section
1115 (
1116 p_api_version IN NUMBER,
1117 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1118 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1119 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1120 p_mini_site_section_section_id IN NUMBER := FND_API.G_MISS_NUM,
1121 p_mini_site_id IN NUMBER := FND_API.G_MISS_NUM,
1122 p_parent_section_id IN NUMBER := FND_API.G_MISS_NUM,
1123 p_child_section_id IN NUMBER := FND_API.G_MISS_NUM,
1124 x_return_status OUT VARCHAR2,
1125 x_msg_count OUT NUMBER,
1126 x_msg_data OUT VARCHAR2
1127 )
1128 IS
1129 l_api_name CONSTANT VARCHAR2(30) := 'Delete_MSite_Section_Section';
1130 l_api_version CONSTANT NUMBER := 1.0;
1131
1132 l_msite_sct_sect_id NUMBER;
1133 BEGIN
1134 -- Standard Start of API savepoint
1135 SAVEPOINT DELETE_MSITE_SCT_SECT_PVT;
1136
1137 -- Standard call to check for call compatibility.
1138 IF NOT FND_API.Compatible_API_Call(l_api_version,
1139 p_api_version,
1140 l_api_name,
1141 G_PKG_NAME)
1142 THEN
1143 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1144 END IF;
1145
1146 -- Initialize message list if p_init_msg_list is set to TRUE.
1147 IF FND_API.to_Boolean(p_init_msg_list) THEN
1148 FND_MSG_PUB.initialize;
1149 END IF;
1150
1151 -- Initialize API return status to success
1152 x_return_status := FND_API.G_RET_STS_SUCCESS;
1153
1154 -- CALL FLOW
1155 -- 1. If mini_site_section_section_id specified, delete all references for it
1156 -- 2. If combination of mini_site_id, parent_section_id and child_section_id
1157 -- is specified, then query for mini_site_section_section_id and delete
1158 -- all references
1159
1160 -- 1. If mini_site_section_section_id specified, delete all references for it
1161 IF ((p_mini_site_section_section_id IS NOT NULL) AND
1162 (p_mini_site_section_section_id <> FND_API.G_MISS_NUM))
1163 THEN
1164 -- mini_site_section_section_id specified, continue
1165 l_msite_sct_sect_id := p_mini_site_section_section_id;
1166 ELSIF ((p_mini_site_id IS NOT NULL) AND
1167 (p_mini_site_id <> FND_API.G_MISS_NUM) AND
1168 (p_parent_section_id <> FND_API.G_MISS_NUM) AND -- parent can be NULL
1169 (p_child_section_id IS NOT NULL) AND
1170 (p_child_section_id <> FND_API.G_MISS_NUM))
1171 THEN
1172 -- If combination of mini_site_id, parent_section_id and child_section_id
1173 -- is specified, then query for mini_site_section_section_id
1174 BEGIN
1175 IF (p_parent_section_id IS NOT NULL) THEN
1176
1177 SELECT mini_site_section_section_id INTO l_msite_sct_sect_id
1178 FROM jtf_dsp_msite_sct_sects
1179 WHERE mini_site_id = p_mini_site_id
1180 AND parent_section_id = p_parent_section_id
1181 AND child_section_id = child_section_id;
1182 ELSE
1183
1184 SELECT mini_site_section_section_id INTO l_msite_sct_sect_id
1185 FROM jtf_dsp_msite_sct_sects
1186 WHERE mini_site_id = p_mini_site_id
1187 AND parent_section_id IS NULL
1188 AND child_section_id = child_section_id;
1189
1190 END IF;
1191
1192 EXCEPTION
1193 WHEN NO_DATA_FOUND THEN
1194 FND_MESSAGE.Set_Name('JTF', 'JTF_DSP_NO_MSS_ID');
1195 FND_MESSAGE.Set_Token('MINI_SITE_ID', p_mini_site_id);
1196 IF (p_parent_section_id IS NOT NULL) THEN
1197 FND_MESSAGE.Set_Token('PARENT_SECTION_ID', p_parent_section_id);
1198 ELSE
1199 FND_MESSAGE.Set_Token('PARENT_SECTION_ID', 'NULL');
1200 END IF;
1201 FND_MESSAGE.Set_Token('CHILD_SECTION_ID', p_child_section_id);
1202 FND_MSG_PUB.Add;
1203 RAISE FND_API.G_EXC_ERROR;
1204 WHEN OTHERS THEN
1205 FND_MESSAGE.Set_Name('JTF', 'JTF_DSP_NO_MSS_ID');
1206 FND_MESSAGE.Set_Token('MINI_SITE_ID', p_mini_site_id);
1207 IF (p_parent_section_id IS NOT NULL) THEN
1208 FND_MESSAGE.Set_Token('PARENT_SECTION_ID', p_parent_section_id);
1209 ELSE
1210 FND_MESSAGE.Set_Token('PARENT_SECTION_ID', 'NULL');
1211 END IF;
1212 FND_MESSAGE.Set_Token('CHILD_SECTION_ID', p_child_section_id);
1213 FND_MSG_PUB.Add;
1214 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1215 END;
1216
1217 ELSE
1218 -- neither mini_site_section_section_id nor combination of
1219 -- mini_site_id, parent_section_id and child_section_id is specified
1220 FND_MESSAGE.Set_Name('JTF', 'JTF_DSP_NO_MSS_IDS_SPEC');
1221 FND_MSG_PUB.Add;
1222 RAISE FND_API.G_EXC_ERROR;
1223 END IF;
1224
1225 -- delete row
1226 delete_row(l_msite_sct_sect_id);
1227
1228 EXCEPTION
1229
1230 WHEN FND_API.G_EXC_ERROR THEN
1231 ROLLBACK TO DELETE_MSITE_SCT_SECT_PVT;
1232 x_return_status := FND_API.G_RET_STS_ERROR;
1233 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
1234 p_data => x_msg_data,
1235 p_encoded => 'F');
1236
1237 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1238 ROLLBACK TO DELETE_MSITE_SCT_SECT_PVT;
1239 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1240 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
1241 p_data => x_msg_data,
1242 p_encoded => 'F');
1243
1244 WHEN OTHERS THEN
1245 ROLLBACK TO DELETE_MSITE_SCT_SECT_PVT;
1246 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1247
1248 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
1249 THEN
1250 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
1251 END IF;
1252
1253 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
1254 p_data => x_msg_data,
1255 p_encoded => 'F');
1256
1257 END Delete_MSite_Section_Section;
1258
1259 PROCEDURE Update_MSite_Section_Sections
1260 (
1261 p_api_version IN NUMBER,
1262 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1263 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1264 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1265 p_msite_section_section_ids IN JTF_NUMBER_TABLE,
1266 p_object_version_numbers IN JTF_NUMBER_TABLE,
1267 p_start_date_actives IN JTF_DATE_TABLE,
1268 p_end_date_actives IN JTF_DATE_TABLE,
1269 p_sort_orders IN JTF_NUMBER_TABLE,
1270 x_return_status OUT VARCHAR2,
1271 x_msg_count OUT NUMBER,
1272 x_msg_data OUT VARCHAR2
1273 )
1274 IS
1275 l_api_name CONSTANT VARCHAR2(30) := 'Update_MSite_Section_Sections';
1276 l_api_version CONSTANT NUMBER := 1.0;
1277 l_msg_count NUMBER;
1278 l_msg_data VARCHAR2(2000);
1279
1280 l_msite_sct_sect_id NUMBER;
1281 l_return_status VARCHAR2(1);
1282
1283 BEGIN
1284
1285 -- Standard Start of API savepoint
1286 SAVEPOINT UPDATE_MSITE_SCT_SECTS_PVT;
1287
1288 -- Standard call to check for call compatibility.
1289 IF NOT FND_API.Compatible_API_Call(l_api_version,
1290 p_api_version,
1291 l_api_name,
1292 G_PKG_NAME)
1293 THEN
1294 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1295 END IF;
1296
1297 -- Initialize message list if p_init_msg_list is set to TRUE.
1298 IF FND_API.to_Boolean(p_init_msg_list) THEN
1299 FND_MSG_PUB.initialize;
1300 END IF;
1301
1302 -- Initialize API return status to success
1303 x_return_status := FND_API.G_RET_STS_SUCCESS;
1304
1305 -- API body
1306 -- CALL FLOW :
1307 -- 1.
1308 FOR i IN 1..p_msite_section_section_ids.COUNT LOOP
1309
1310 Update_MSite_Section_Section
1311 (
1312 p_api_version => p_api_version,
1313 p_init_msg_list => FND_API.G_FALSE,
1314 p_commit => FND_API.G_FALSE,
1315 p_validation_level => p_validation_level,
1316 p_mini_site_section_section_id => p_msite_section_section_ids(i),
1317 p_object_version_number => p_object_version_numbers(i),
1318 p_mini_site_id => FND_API.G_MISS_NUM,
1319 p_parent_section_id => FND_API.G_MISS_NUM,
1320 p_child_section_id => FND_API.G_MISS_NUM,
1321 p_start_date_active => p_start_date_actives(i),
1322 p_end_date_active => p_end_date_actives(i),
1323 p_level_number => FND_API.G_MISS_NUM,
1324 p_sort_order => p_sort_orders(i),
1325 p_concat_ids => FND_API.G_MISS_CHAR,
1326 x_return_status => l_return_status,
1327 x_msg_count => x_msg_count,
1328 x_msg_data => x_msg_data
1329 );
1330
1331 IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
1332 RAISE FND_API.G_EXC_ERROR;
1333 ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
1334 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1335 END IF;
1336
1337 END LOOP;
1338
1339 --
1340 -- End of main API body.
1341
1342 -- Standard check of p_commit.
1343 IF (FND_API.To_Boolean(p_commit)) THEN
1344 COMMIT WORK;
1345 END IF;
1346
1347 -- Standard call to get message count and if count is 1, get message info.
1348 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
1349 p_data => x_msg_data,
1350 p_encoded => 'F');
1351
1352 EXCEPTION
1353
1354 WHEN FND_API.G_EXC_ERROR THEN
1355 ROLLBACK TO UPDATE_MSITE_SCT_SECTS_PVT;
1356 x_return_status := FND_API.G_RET_STS_ERROR;
1357 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
1358 p_data => x_msg_data,
1359 p_encoded => 'F');
1360
1361 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1362 ROLLBACK TO UPDATE_MSITE_SCT_SECTS_PVT;
1363 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1364 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
1365 p_data => x_msg_data,
1366 p_encoded => 'F');
1367
1368 WHEN OTHERS THEN
1369 ROLLBACK TO UPDATE_MSITE_SCT_SECTS_PVT;
1370 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1371
1372 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1373 THEN
1374 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
1375 END IF;
1376
1377 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
1378 p_data => x_msg_data,
1379 p_encoded => 'F');
1380
1381 END Update_MSite_Section_Sections;
1382
1383 PROCEDURE Update_Delete_Sct_Scts
1384 (
1385 p_api_version IN NUMBER,
1386 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1387 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1388 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1389 p_msite_section_section_ids IN JTF_NUMBER_TABLE,
1390 p_object_version_numbers IN JTF_NUMBER_TABLE,
1391 p_start_date_actives IN JTF_DATE_TABLE,
1392 p_end_date_actives IN JTF_DATE_TABLE,
1393 p_sort_orders IN JTF_NUMBER_TABLE,
1394 p_delete_flags IN JTF_VARCHAR2_TABLE_300,
1395 x_return_status OUT VARCHAR2,
1396 x_msg_count OUT NUMBER,
1397 x_msg_data OUT VARCHAR2
1398 )
1399 IS
1400 l_api_name CONSTANT VARCHAR2(30) := 'Update_Delete_Sct_Scts';
1401 l_api_version CONSTANT NUMBER := 1.0;
1402 l_msg_count NUMBER;
1403 l_msg_data VARCHAR2(2000);
1404
1405 l_section_item_id NUMBER;
1406 l_return_status VARCHAR2(1);
1407
1408 BEGIN
1409
1410 -- Standard Start of API savepoint
1411 SAVEPOINT UPDATE_DELETE_SCT_SCTS_PVT;
1412
1413 -- Standard call to check for call compatibility.
1414 IF NOT FND_API.Compatible_API_Call(l_api_version,
1415 p_api_version,
1416 l_api_name,
1417 G_PKG_NAME)
1418 THEN
1419 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1420 END IF;
1421
1422 -- Initialize message list if p_init_msg_list is set to TRUE.
1423 IF FND_API.to_Boolean(p_init_msg_list) THEN
1424 FND_MSG_PUB.initialize;
1425 END IF;
1426
1427 -- Initialize API return status to success
1428 x_return_status := FND_API.G_RET_STS_SUCCESS;
1429
1430 -- API body
1431 -- CALL FLOW :
1432 -- 1.
1433
1434 FOR i IN 1..p_msite_section_section_ids.COUNT LOOP
1435
1436 IF (p_delete_flags(i) = 'Y') THEN
1437
1438 Delete_MSite_Section_Section
1439 (
1440 p_api_version => p_api_version,
1441 p_init_msg_list => FND_API.G_FALSE,
1442 p_commit => FND_API.G_FALSE,
1443 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1444 p_mini_site_section_section_id => p_msite_section_section_ids(i),
1445 p_mini_site_id => FND_API.G_MISS_NUM,
1446 p_parent_section_id => FND_API.G_MISS_NUM,
1447 p_child_section_id => FND_API.G_MISS_NUM,
1448 x_return_status => x_return_status,
1449 x_msg_count => x_msg_count,
1450 x_msg_data => x_msg_data
1451 );
1452
1453 IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
1454 RAISE FND_API.G_EXC_ERROR;
1455 ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
1456 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1457 END IF;
1458
1459 ELSE
1460
1461 Update_Msite_Section_Section
1462 (
1463 p_api_version => p_api_version,
1464 p_init_msg_list => FND_API.G_FALSE,
1465 p_commit => FND_API.G_FALSE,
1466 p_validation_level => p_validation_level,
1467 p_mini_site_section_section_id => p_msite_section_section_ids(i),
1468 p_object_version_number => p_object_version_numbers(i),
1469 p_mini_site_id => FND_API.G_MISS_NUM,
1470 p_parent_section_id => FND_API.G_MISS_NUM,
1471 p_child_section_id => FND_API.G_MISS_NUM,
1472 p_start_date_active => p_start_date_actives(i),
1473 p_end_date_active => p_end_date_actives(i),
1474 p_level_number => FND_API.G_MISS_NUM,
1475 p_sort_order => p_sort_orders(i),
1476 p_concat_ids => FND_API.G_MISS_CHAR,
1477 x_return_status => l_return_status,
1478 x_msg_count => x_msg_count,
1479 x_msg_data => x_msg_data
1480 );
1481
1482 IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
1483 RAISE FND_API.G_EXC_ERROR;
1484 ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
1485 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1486 END IF;
1487
1488 END IF;
1489
1490 END LOOP;
1491
1492 --
1493 -- End of main API body.
1494
1495 -- Standard check of p_commit.
1496 IF (FND_API.To_Boolean(p_commit)) THEN
1497 COMMIT WORK;
1498 END IF;
1499
1500 -- Standard call to get message count and if count is 1, get message info.
1501 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
1502 p_data => x_msg_data,
1503 p_encoded => 'F');
1504
1505 EXCEPTION
1506
1507 WHEN FND_API.G_EXC_ERROR THEN
1508 ROLLBACK TO UPDATE_DELETE_SCT_SCTS_PVT;
1509 x_return_status := FND_API.G_RET_STS_ERROR;
1510 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
1511 p_data => x_msg_data,
1512 p_encoded => 'F');
1513
1514 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1515 ROLLBACK TO UPDATE_DELETE_SCT_SCTS_PVT;
1516 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1517 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
1518 p_data => x_msg_data,
1519 p_encoded => 'F');
1520
1521 WHEN OTHERS THEN
1522 ROLLBACK TO UPDATE_DELETE_SCT_SCTS_PVT;
1523 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1524
1525 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1526 THEN
1527 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
1528 END IF;
1529
1530 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
1531 p_data => x_msg_data,
1532 p_encoded => 'F');
1533
1534 END Update_Delete_Sct_Scts;
1535
1536 END JTF_DSP_MSITE_SCT_SECT_PVT;