[Home] [Help]
PACKAGE BODY: APPS.IBE_DSP_SECTION_GRP
Source
1 PACKAGE BODY IBE_DSP_SECTION_GRP AS
2 /* $Header: IBEGCSCB.pls 120.5 2006/06/30 21:14:16 abhandar noship $ */
3
4 --
5 --
6 -- Start of Comments
7 --
8 -- NAME
9 -- IBE_DSP_SECTION_GRP
10 --
11 -- PURPOSE
12 -- Private API for saving, retrieving and updating sections.
13 --
14 -- NOTES
15 -- This is a pulicly accessible pacakge. It should be used by all
16 -- sources for saving, retrieving and updating personalized queries
17 -- within the personalization framework.
18 --
19
20 -- HISTORY
21 -- 11/28/99 VPALAIYA Created
22 -- 12/12/02 SCHAK Modified for NOCOPY (Bug # 2691704) Changes.
23 -- **************************************************************************
24
25 G_PKG_NAME CONSTANT VARCHAR2(30):='IBE_DSP_SECTION_GRP';
26 G_FILE_NAME CONSTANT VARCHAR2(12):='IBEGCSCB.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 sections
41 -- ****************************************************************************
42
43 --
44 -- Valid the SQL in p_sql_stmt
45 --
46 PROCEDURE Is_SQL_Valid
47 (
48 p_sql_stmt IN VARCHAR2,
49 x_return_status OUT NOCOPY VARCHAR2
50 )
51 IS
52 l_api_name CONSTANT VARCHAR2(30) := 'Is_SQL_Valid';
53 l_cursor NUMBER;
54 BEGIN
55
56 l_cursor := DBMS_SQL.open_cursor;
57 BEGIN
58 DBMS_SQL.parse(l_cursor, p_sql_stmt, DBMS_SQL.NATIVE);
59 x_return_status := FND_API.G_RET_STS_SUCCESS;
60 DBMS_SQL.close_cursor(l_cursor);
61 EXCEPTION
62 WHEN OTHERS THEN
63 FND_MESSAGE.Set_Name('FND', 'SQL_PLSQL_ERROR');
64 FND_MESSAGE.Set_Token('ROUTINE', l_api_name);
65 FND_MESSAGE.Set_Token('ERRNO', SQLCODE);
66 FND_MESSAGE.Set_Token('REASON', SQLERRM);
67 FND_MSG_PUB.Add;
68
69 x_return_status := FND_API.G_RET_STS_ERROR;
70 DBMS_SQL.close_cursor(l_cursor);
71 END;
72
73 END Is_SQL_Valid;
74
75 PROCEDURE insert_row
76 (
77 p_section_id IN NUMBER,
78 p_object_version_number IN NUMBER,
79 p_access_name IN VARCHAR2,
80 p_start_date_active IN DATE,
81 p_end_date_active IN DATE,
82 p_section_type_code IN VARCHAR2,
83 p_status_code IN VARCHAR2,
84 p_display_context_id IN NUMBER,
85 p_deliverable_id IN NUMBER,
86 p_available_in_all_sites_flag IN VARCHAR2,
87 p_auto_placement_rule IN VARCHAR2,
88 p_order_by_clause IN VARCHAR2,
89 p_attribute_category IN VARCHAR2,
90 p_attribute1 IN VARCHAR2,
91 p_attribute2 IN VARCHAR2,
92 p_attribute3 IN VARCHAR2,
93 p_attribute4 IN VARCHAR2,
94 p_attribute5 IN VARCHAR2,
95 p_attribute6 IN VARCHAR2,
96 p_attribute7 IN VARCHAR2,
97 p_attribute8 IN VARCHAR2,
98 p_attribute9 IN VARCHAR2,
99 p_attribute10 IN VARCHAR2,
100 p_attribute11 IN VARCHAR2,
101 p_attribute12 IN VARCHAR2,
102 p_attribute13 IN VARCHAR2,
103 p_attribute14 IN VARCHAR2,
104 p_attribute15 IN VARCHAR2,
105 p_display_name IN VARCHAR2,
106 p_description IN VARCHAR2,
107 p_long_description IN VARCHAR2,
108 p_keywords IN VARCHAR2,
109 p_creation_date IN DATE,
110 p_created_by IN NUMBER,
111 p_last_update_date IN DATE,
112 p_last_updated_by IN NUMBER,
113 p_last_update_login IN NUMBER,
114 x_rowid OUT NOCOPY VARCHAR2,
115 x_section_id OUT NOCOPY NUMBER
116 )
117 IS
118 l_display_context_id NUMBER;
119 l_deliverable_id NUMBER;
120
121 CURSOR c IS SELECT rowid FROM ibe_dsp_sections_b
122 WHERE section_id = x_section_id;
123 CURSOR c2 IS SELECT ibe_dsp_sections_b_s1.nextval FROM dual;
124
125 BEGIN
126
127 -- Primary key validation check
128 x_section_id := p_section_id;
129 IF ((x_section_id IS NULL) OR
130 (x_section_id = FND_API.G_MISS_NUM))
131 THEN
132 OPEN c2;
133 FETCH c2 INTO x_section_id;
134 CLOSE c2;
135 END IF;
136
137 -- insert base
138 INSERT INTO ibe_dsp_sections_b
139 (
140 section_id,
141 object_version_number,
142 access_name,
143 start_date_active,
144 end_date_active,
145 section_type_code,
146 status_code,
147 display_context_id,
148 deliverable_id,
149 available_in_all_sites_flag,
150 auto_placement_rule,
151 order_by_clause,
152 attribute_category,
153 attribute1,
154 attribute2,
155 attribute3,
156 attribute4,
157 attribute5,
158 attribute6,
159 attribute7,
160 attribute8,
161 attribute9,
162 attribute10,
163 attribute11,
164 attribute12,
165 attribute13,
166 attribute14,
167 attribute15,
168 creation_date,
169 created_by,
170 last_update_date,
171 last_updated_by,
172 last_update_login
173 )
174 VALUES
175 (
176 x_section_id,
177 p_object_version_number,
178 decode(p_access_name, FND_API.G_MISS_CHAR, NULL, p_access_name),
179 p_start_date_active,
180 decode(p_end_date_active, FND_API.G_MISS_DATE, NULL, p_end_date_active),
181 p_section_type_code,
182 p_status_code,
183 decode(p_display_context_id, FND_API.G_MISS_NUM,NULL,p_display_context_id),
184 decode(p_deliverable_id, FND_API.G_MISS_NUM, NULL, p_deliverable_id),
185 decode(p_available_in_all_sites_flag, FND_API.G_MISS_CHAR, 'Y', NULL, 'Y',
186 p_available_in_all_sites_flag),
187 decode(p_auto_placement_rule, FND_API.G_MISS_CHAR, NULL,
188 p_auto_placement_rule),
189 decode(p_order_by_clause, FND_API.G_MISS_CHAR, NULL, p_order_by_clause),
190 decode(p_attribute_category, FND_API.G_MISS_CHAR, NULL,p_attribute_category),
191 decode(p_attribute1, FND_API.G_MISS_CHAR, NULL, p_attribute1),
192 decode(p_attribute2, FND_API.G_MISS_CHAR, NULL, p_attribute2),
193 decode(p_attribute3, FND_API.G_MISS_CHAR, NULL, p_attribute3),
194 decode(p_attribute4, FND_API.G_MISS_CHAR, NULL, p_attribute4),
195 decode(p_attribute5, FND_API.G_MISS_CHAR, NULL, p_attribute5),
196 decode(p_attribute6, FND_API.G_MISS_CHAR, NULL, p_attribute6),
197 decode(p_attribute7, FND_API.G_MISS_CHAR, NULL, p_attribute7),
198 decode(p_attribute8, FND_API.G_MISS_CHAR, NULL, p_attribute8),
199 decode(p_attribute9, FND_API.G_MISS_CHAR, NULL, p_attribute9),
200 decode(p_attribute10, FND_API.G_MISS_CHAR, NULL, p_attribute10),
201 decode(p_attribute11, FND_API.G_MISS_CHAR, NULL, p_attribute11),
202 decode(p_attribute12, FND_API.G_MISS_CHAR, NULL, p_attribute12),
203 decode(p_attribute13, FND_API.G_MISS_CHAR, NULL, p_attribute13),
204 decode(p_attribute14, FND_API.G_MISS_CHAR, NULL, p_attribute14),
205 decode(p_attribute15, FND_API.G_MISS_CHAR, NULL, p_attribute15),
206 decode(p_creation_date, FND_API.G_MISS_DATE, sysdate, NULL, sysdate,
207 p_creation_date),
208 decode(p_created_by, FND_API.G_MISS_NUM, FND_GLOBAL.user_id,
209 NULL, FND_GLOBAL.user_id, p_created_by),
210 decode(p_last_update_date, FND_API.G_MISS_DATE, sysdate, NULL, sysdate,
211 p_last_update_date),
212 decode(p_last_updated_by, FND_API.G_MISS_NUM, FND_GLOBAL.user_id,
213 NULL, FND_GLOBAL.user_id, p_last_updated_by),
214 decode(p_last_update_login, FND_API.G_MISS_NUM, FND_GLOBAL.login_id,
215 NULL, FND_GLOBAL.login_id, p_last_update_login)
216 );
217
218 OPEN c;
219 FETCH c INTO x_rowid;
220 IF (c%NOTFOUND)
221 THEN
222 CLOSE c;
223 RAISE NO_DATA_FOUND;
224 END IF;
225 CLOSE c;
226
227 -- insert tl
228 INSERT INTO ibe_dsp_sections_tl
229 (
230 last_update_login,
231 display_name,
232 description,
233 long_description,
234 keywords,
235 last_updated_by,
236 last_update_date,
237 creation_date,
238 section_id,
239 object_version_number,
240 created_by,
241 language,
242 source_lang
243 )
244 SELECT
245 decode(p_last_update_login, FND_API.G_MISS_NUM, FND_GLOBAL.login_id,
246 NULL, FND_GLOBAL.login_id, p_last_update_login),
247 p_display_name,
248 decode(p_description,FND_API.G_MISS_CHAR, NULL, p_description),
249 decode(p_long_description, FND_API.G_MISS_CHAR, NULL, p_long_description),
250 decode(p_keywords, FND_API.G_MISS_CHAR, NULL, p_keywords),
251 decode(p_last_updated_by, FND_API.G_MISS_NUM, FND_GLOBAL.user_id,
252 NULL, FND_GLOBAL.user_id, p_last_updated_by),
253 decode(p_last_update_date, FND_API.G_MISS_DATE, sysdate,
254 NULL, sysdate, p_last_update_date),
255 decode(p_creation_date, FND_API.G_MISS_DATE, sysdate,
256 NULL, sysdate, p_creation_date),
257 x_section_id,
258 p_object_version_number,
259 decode(p_created_by, FND_API.G_MISS_NUM, FND_GLOBAL.user_id,
260 NULL, FND_GLOBAL.user_id, p_created_by),
261 L.language_code,
262 USERENV('LANG')
263 FROM fnd_languages L
264 WHERE L.installed_flag IN ('I', 'B')
265 AND NOT EXISTS
266 (SELECT NULL
267 FROM ibe_dsp_sections_tl T
268 WHERE T.section_id = x_section_id
269 AND T.language = L.language_code);
270
271 OPEN c;
272 FETCH c INTO x_rowid;
273 IF (c%NOTFOUND)
274 THEN
275 CLOSE c;
276 RAISE NO_DATA_FOUND;
277 END IF;
278 CLOSE c;
279
280 END insert_row;
281
282 -- ****************************************************************************
283 -- update row into sections
284 -- ****************************************************************************
285
286 PROCEDURE update_row
287 (
288 p_section_id IN NUMBER,
289 p_object_version_number IN NUMBER := FND_API.G_MISS_NUM,
290 p_access_name IN VARCHAR2,
291 p_start_date_active IN DATE,
292 p_end_date_active IN DATE,
293 p_section_type_code IN VARCHAR2,
294 p_status_code IN VARCHAR2,
295 p_display_context_id IN NUMBER,
296 p_deliverable_id IN NUMBER,
297 p_available_in_all_sites_flag IN VARCHAR2,
298 p_auto_placement_rule IN VARCHAR2,
299 p_order_by_clause IN VARCHAR2,
300 p_attribute_category IN VARCHAR2,
301 p_attribute1 IN VARCHAR2,
302 p_attribute2 IN VARCHAR2,
303 p_attribute3 IN VARCHAR2,
304 p_attribute4 IN VARCHAR2,
305 p_attribute5 IN VARCHAR2,
306 p_attribute6 IN VARCHAR2,
307 p_attribute7 IN VARCHAR2,
308 p_attribute8 IN VARCHAR2,
309 p_attribute9 IN VARCHAR2,
310 p_attribute10 IN VARCHAR2,
311 p_attribute11 IN VARCHAR2,
312 p_attribute12 IN VARCHAR2,
313 p_attribute13 IN VARCHAR2,
314 p_attribute14 IN VARCHAR2,
315 p_attribute15 IN VARCHAR2,
316 p_display_name IN VARCHAR2,
317 p_description IN VARCHAR2,
318 p_long_description IN VARCHAR2,
319 p_keywords IN VARCHAR2,
320 p_last_update_date IN DATE,
321 p_last_updated_by IN NUMBER,
322 p_last_update_login IN NUMBER
323 )
324 IS
325 BEGIN
326
327 -- update base
328 UPDATE ibe_dsp_sections_b SET
329 object_version_number = object_version_number + 1,
330 access_name = decode(p_access_name, FND_API.G_MISS_CHAR,
331 access_name, p_access_name),
332 start_date_active = decode(p_start_date_active, FND_API.G_MISS_DATE,
333 start_date_active, p_start_date_active),
334 end_date_active = decode(p_end_date_active, FND_API.G_MISS_DATE,
335 end_date_active, p_end_date_active),
336 section_type_code = decode(p_section_type_code, FND_API.G_MISS_CHAR,
337 section_type_code, p_section_type_code),
338 status_code = decode(p_status_code, FND_API.G_MISS_CHAR,
339 status_code, p_status_code),
340 display_context_id = decode(p_display_context_id, FND_API.G_MISS_NUM,
341 display_context_id, p_display_context_id),
342 deliverable_id = decode(p_deliverable_id, FND_API.G_MISS_NUM,
343 deliverable_id, p_deliverable_id),
344 available_in_all_sites_flag =
345 decode(p_available_in_all_sites_flag, FND_API.G_MISS_CHAR,
346 available_in_all_sites_flag, p_available_in_all_sites_flag),
347 auto_placement_rule = decode(p_auto_placement_rule, FND_API.G_MISS_CHAR,
348 auto_placement_rule, p_auto_placement_rule),
349 order_by_clause = decode(p_order_by_clause, FND_API.G_MISS_CHAR,
350 order_by_clause, p_order_by_clause),
351 attribute_category = decode(p_attribute_category, FND_API.G_MISS_CHAR,
352 attribute_category, p_attribute_category),
353 attribute1 = decode(p_attribute1, FND_API.G_MISS_CHAR,
354 attribute1, p_attribute1),
355 attribute2 = decode(p_attribute2, FND_API.G_MISS_CHAR,
356 attribute2, p_attribute2),
357 attribute3 = decode(p_attribute3, FND_API.G_MISS_CHAR,
358 attribute3, p_attribute3),
359 attribute4 = decode(p_attribute4, FND_API.G_MISS_CHAR,
360 attribute4, p_attribute4),
361 attribute5 = decode(p_attribute5, FND_API.G_MISS_CHAR,
362 attribute5, p_attribute5),
363 attribute6 = decode(p_attribute6, FND_API.G_MISS_CHAR,
364 attribute6, p_attribute6),
365 attribute7 = decode(p_attribute7, FND_API.G_MISS_CHAR,
366 attribute7, p_attribute7),
367 attribute8 = decode(p_attribute8, FND_API.G_MISS_CHAR,
368 attribute8, p_attribute8),
369 attribute9 = decode(p_attribute9, FND_API.G_MISS_CHAR,
370 attribute9, p_attribute9),
371 attribute10 = decode(p_attribute10, FND_API.G_MISS_CHAR,
372 attribute10, p_attribute10),
373 attribute11 = decode(p_attribute11, FND_API.G_MISS_CHAR,
374 attribute11, p_attribute11),
375 attribute12 = decode(p_attribute12, FND_API.G_MISS_CHAR,
376 attribute12, p_attribute12),
377 attribute13 = decode(p_attribute13, FND_API.G_MISS_CHAR,
378 attribute13, p_attribute13),
379 attribute14 = decode(p_attribute14, FND_API.G_MISS_CHAR,
380 attribute14, p_attribute14),
381 attribute15 = decode(p_attribute15, FND_API.G_MISS_CHAR,
382 attribute15, p_attribute15),
383 last_update_date = decode(p_last_update_date, FND_API.G_MISS_DATE, sysdate,
384 NULL, sysdate, p_last_update_date),
385 last_updated_by = decode(p_last_updated_by, FND_API.G_MISS_NUM,
386 FND_GLOBAL.user_id, NULL, FND_GLOBAL.user_id,
387 p_last_updated_by),
388 last_update_login = decode(p_last_update_login, FND_API.G_MISS_NUM,
389 FND_GLOBAL.login_id, NULL, FND_GLOBAL.login_id,
390 p_last_update_login)
391 WHERE section_id = p_section_id
392 AND object_version_number = decode(p_object_version_number,
393 FND_API.G_MISS_NUM,
394 object_version_number,
395 p_object_version_number);
396
397
398 IF (sql%NOTFOUND) THEN
399 RAISE NO_DATA_FOUND;
400 END IF;
401
402 UPDATE ibe_dsp_sections_tl SET
403 object_version_number = object_version_number + 1,
404 display_name = decode(p_display_name, FND_API.G_MISS_CHAR,
405 display_name, p_display_name),
406 description = decode(p_description, FND_API.G_MISS_CHAR,
407 description, p_description),
408 long_description = decode(p_long_description, FND_API.G_MISS_CHAR,
409 long_description, p_long_description),
410 keywords = decode(p_keywords, FND_API.G_MISS_CHAR, keywords, p_keywords),
411 last_update_date = decode(p_last_update_date, FND_API.G_MISS_DATE, sysdate,
412 NULL, sysdate, p_last_update_date),
413 last_updated_by = decode(p_last_updated_by, FND_API.G_MISS_NUM,
414 FND_GLOBAL.user_id, NULL, FND_GLOBAL.user_id,
415 p_last_updated_by),
416 last_update_login = decode(p_last_update_login, FND_API.G_MISS_NUM,
417 FND_GLOBAL.login_id, NULL, FND_GLOBAL.login_id,
418 p_last_update_login),
419 source_lang = USERENV('LANG')
420 WHERE section_id = p_section_id
421 -- AND object_version_number = decode(p_object_version_number,
422 -- FND_API.G_MISS_NUM,
423 -- object_version_number,
424 -- p_object_version_number)
425 AND USERENV('LANG') IN (language, source_lang);
426
427 IF (sql%NOTFOUND) THEN
428 RAISE NO_DATA_FOUND;
429 END IF;
430
431 END update_row;
432
433 -- ****************************************************************************
434 -- delete row from sections
435 -- ****************************************************************************
436
437 PROCEDURE delete_row
438 (
439 p_section_id IN NUMBER
440 )
441 IS
442 BEGIN
443
444 DELETE FROM ibe_dsp_sections_tl
445 WHERE section_id = p_section_id;
446
447 IF (sql%NOTFOUND) THEN
448 RAISE NO_DATA_FOUND;
449 END IF;
450
451 DELETE FROM ibe_dsp_sections_b
452 WHERE section_id = p_section_id;
453
454 IF (sql%NOTFOUND) THEN
455 RAISE NO_DATA_FOUND;
456 END IF;
457
458 END delete_row;
459
460 --
461 -- To be called from ibemste.lct only
462 --
463 PROCEDURE load_row
464 (
465 p_owner IN VARCHAR2,
466 p_section_id IN NUMBER,
467 p_object_version_number IN NUMBER := FND_API.G_MISS_NUM,
468 p_access_name IN VARCHAR2,
469 p_start_date_active IN DATE,
470 p_end_date_active IN DATE,
471 p_section_type_code IN VARCHAR2,
472 p_status_code IN VARCHAR2,
473 p_display_context_id IN NUMBER,
474 p_deliverable_id IN NUMBER,
475 p_available_in_all_sites_flag IN VARCHAR2,
476 p_auto_placement_rule IN VARCHAR2,
477 p_order_by_clause IN VARCHAR2,
478 p_attribute_category IN VARCHAR2,
479 p_attribute1 IN VARCHAR2,
480 p_attribute2 IN VARCHAR2,
481 p_attribute3 IN VARCHAR2,
482 p_attribute4 IN VARCHAR2,
483 p_attribute5 IN VARCHAR2,
484 p_attribute6 IN VARCHAR2,
485 p_attribute7 IN VARCHAR2,
486 p_attribute8 IN VARCHAR2,
487 p_attribute9 IN VARCHAR2,
488 p_attribute10 IN VARCHAR2,
489 p_attribute11 IN VARCHAR2,
490 p_attribute12 IN VARCHAR2,
491 p_attribute13 IN VARCHAR2,
492 p_attribute14 IN VARCHAR2,
493 p_attribute15 IN VARCHAR2,
494 p_display_name IN VARCHAR2,
495 p_description IN VARCHAR2,
496 p_long_description IN VARCHAR2,
497 p_keywords IN VARCHAR2,
498 P_LAST_UPDATE_DATE IN varchar2,
499 P_CUSTOM_MODE IN Varchar2
500 )
501 IS
502 l_user_id NUMBER := 0;
503 l_rowid VARCHAR2(256);
504 l_section_id NUMBER;
505 l_object_version_number NUMBER := 1;
506 f_luby number; -- entity owner in file
507 f_ludate date; -- entity update date in file
508 db_luby number; -- entity owner in db
509 db_ludate date; -- entity update date in db
510
511 BEGIN
512 -- Translate owner to file_last_updated_by
513 f_luby := fnd_load_util.owner_id(P_OWNER);
514 -- Translate char last_update_date to date
515 f_ludate := nvl(to_date(P_LAST_UPDATE_DATE, 'YYYY/MM/DD'), sysdate);
516 -- get the value of the db_luby and db_ludate from the database
517 select LAST_UPDATED_BY, LAST_UPDATE_DATE
518 into db_luby, db_ludate
519 from ibe_dsp_sections_b
520 where SECTION_ID = p_section_id;
521
522 IF ((p_object_version_number IS NOT NULL) AND
523 (p_object_version_number <> FND_API.G_MISS_NUM))
524 THEN
525 l_object_version_number := p_object_version_number;
526 END IF;
527
528
529 IF (fnd_load_util.upload_test(f_luby, f_ludate, db_luby, db_ludate, P_CUSTOM_MODE)) then
530 update_row
531 (
532 p_section_id => p_section_id,
533 p_object_version_number => p_object_version_number,
534 p_access_name => p_access_name,
535 p_start_date_active => p_start_date_active,
536 p_end_date_active => p_end_date_active,
537 p_section_type_code => p_section_type_code,
538 p_status_code => p_status_code,
539 p_display_context_id => p_display_context_id,
540 p_deliverable_id => p_deliverable_id,
541 p_available_in_all_sites_flag => p_available_in_all_sites_flag,
542 p_auto_placement_rule => p_auto_placement_rule,
543 p_order_by_clause => p_order_by_clause,
544 p_attribute_category => p_attribute_category,
545 p_attribute1 => p_attribute1,
546 p_attribute2 => p_attribute2,
547 p_attribute3 => p_attribute3,
548 p_attribute4 => p_attribute4,
549 p_attribute5 => p_attribute5,
550 p_attribute6 => p_attribute6,
551 p_attribute7 => p_attribute7,
552 p_attribute8 => p_attribute8,
553 p_attribute9 => p_attribute9,
554 p_attribute10 => p_attribute10,
555 p_attribute11 => p_attribute11,
556 p_attribute12 => p_attribute12,
557 p_attribute13 => p_attribute13,
558 p_attribute14 => p_attribute14,
559 p_attribute15 => p_attribute15,
560 p_display_name => p_display_name,
561 p_description => p_description,
562 p_long_description => p_long_description,
563 p_keywords => p_keywords,
564 p_last_update_date => f_ludate, --sysdate,
565 p_last_updated_by => f_luby,--l_user_id,
566 p_last_update_login => 0
567 );
568 END IF;
569 EXCEPTION
570
571 WHEN NO_DATA_FOUND THEN
572
573 insert_row
574 (
575 p_section_id => p_section_id,
576 p_object_version_number => l_object_version_number,
577 p_access_name => p_access_name,
578 p_start_date_active => p_start_date_active,
579 p_end_date_active => p_end_date_active,
580 p_section_type_code => p_section_type_code,
581 p_status_code => p_status_code,
582 p_display_context_id => p_display_context_id,
583 p_deliverable_id => p_deliverable_id,
584 p_available_in_all_sites_flag => p_available_in_all_sites_flag,
585 p_auto_placement_rule => p_auto_placement_rule,
586 p_order_by_clause => p_order_by_clause,
587 p_attribute_category => p_attribute_category,
588 p_attribute1 => p_attribute1,
589 p_attribute2 => p_attribute2,
590 p_attribute3 => p_attribute3,
591 p_attribute4 => p_attribute4,
592 p_attribute5 => p_attribute5,
593 p_attribute6 => p_attribute6,
594 p_attribute7 => p_attribute7,
595 p_attribute8 => p_attribute8,
596 p_attribute9 => p_attribute9,
597 p_attribute10 => p_attribute10,
598 p_attribute11 => p_attribute11,
599 p_attribute12 => p_attribute12,
600 p_attribute13 => p_attribute13,
601 p_attribute14 => p_attribute14,
602 p_attribute15 => p_attribute15,
603 p_display_name => p_display_name,
604 p_description => p_description,
605 p_long_description => p_long_description,
606 p_keywords => p_keywords,
607 p_creation_date => f_ludate, --sysdate,
608 p_created_by => f_luby,--l_user_id,
609 p_last_update_date => f_ludate, --sysdate,
610 p_last_updated_by => f_luby,--l_user_id,
611 p_last_update_login => 0,
612 x_rowid => l_rowid,
613 x_section_id => l_section_id
614 );
615
616
617 END load_row;
618
619
620 -- ****************************************************************************
621 --*****************************************************************************
622 --
623 --APIs
624 --
625 -- 1.Create_Section
626 -- 2.Update_Section
627 -- 3.Delete_Section
628 -- 4.Save_Section
629 -- 5.Get_Section
630 -- 6.check_section_duplicates
631 --
632 --*****************************************************************************
633 --*****************************************************************************
634
635
636 --*****************************************************************************
637 -- PROCEDURE Check_Duplicate_Entry()
638 --*****************************************************************************
639
640 --
641 -- x_return_status = FND_API.G_RET_STS_SUCCESS, if the section is duplicate
642 -- x_return_status = FND_API.G_RET_STS_ERROR, if the section is not duplicate
643 --
644 -- p_section_id is set to NULL by default for create section
645 --
646 --
647 -- This procedure is used by both when creating and updating sections
648 -- When creating sections, the p_section_id should be FND_API.G_MISS_NUM, and
649 -- when updating sections, the p_section_id should be not FND_API.G_MISS_NUM
650 --
651 PROCEDURE Check_Duplicate_Entry
652 (
653 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
654 p_section_id IN NUMBER := FND_API.G_MISS_NUM,
655 p_access_name IN VARCHAR2,
656 p_display_name IN VARCHAR2,
657 x_return_status OUT NOCOPY VARCHAR2,
658 x_msg_count OUT NOCOPY NUMBER,
659 x_msg_data OUT NOCOPY VARCHAR2
660 )
661 IS
662 l_api_name CONSTANT VARCHAR2(30) := 'Check_Duplicate_Entry';
663 l_api_version CONSTANT NUMBER := 1.0;
664 l_in_section_id NUMBER;
665 l_tmp_section_id NUMBER;
666 l_tmp_sql_str VARCHAR2(240) := NULL;
667
668 CURSOR c1(l_c_access_name IN VARCHAR2)
669 IS SELECT section_id
670 FROM ibe_dsp_sections_b
671 WHERE access_name = l_c_access_name;
672
673 -- comment out (bug 2699543, since the code using the cursor is
674 -- already commented out)
675 --CURSOR c2(l_c_display_name IN VARCHAR2, l_c_tmp_sql_str IN VARCHAR2)
676 --IS SELECT section_id
677 -- FROM ibe_dsp_sections_tl
678 -- WHERE display_name = l_c_display_name || l_c_tmp_sql_str;
679
680
681 BEGIN
682
683 l_in_section_id := p_section_id;
684
685 -- To prevent comparison condition disasters with NULL
686 IF(l_in_section_id IS NULL) THEN
687 -- l_in_section_id will be NULL only if Creating Section
688 l_in_section_id := FND_API.G_MISS_NUM;
689 END IF;
690
691 -- Initialize message list if p_init_msg_list is set to TRUE.
692 IF FND_API.to_Boolean(p_init_msg_list) THEN
693 FND_MSG_PUB.initialize;
694 END IF;
695
696 -- Initialize API return status to error, i.e, its not duplicate
697 x_return_status := FND_API.G_RET_STS_ERROR;
698
699 -- Check duplicate access_name
700 IF ((p_access_name IS NOT NULL) AND
701 (p_access_name <> FND_API.G_MISS_CHAR))
702 THEN
703
704 OPEN c1(p_access_name);
705 FETCH c1 INTO l_tmp_section_id;
706 IF (c1%FOUND) THEN
707
708 CLOSE c1;
709 IF (l_in_section_id = FND_API.G_MISS_NUM) THEN
710 -- For Create Section
711 IF (l_tmp_section_id IS NOT NULL) THEN
712 -- found duplicate
713 RAISE FND_API.G_EXC_ERROR;
714 END IF;
715 ELSE
716 -- For Update Section
717 IF (l_tmp_section_id <> l_in_section_id) THEN
718 -- found duplicate
719 RAISE FND_API.G_EXC_ERROR;
720 END IF;
721 END IF;
722 ELSE
723 -- not duplicate
724 -- do nothing
725 CLOSE c1;
726 END IF;
727
728 END IF;
729
730 -- Check duplicate display_name
731 -- Commented out as we can have sections with duplicate section names
732 -- IF ((p_display_name IS NOT NULL) AND
733 -- (p_display_name <> FND_API.G_MISS_CHAR))
734 -- THEN
735 --
736 -- -- If Update Section, add the following string to WHERE clause
737 -- IF(l_in_section_id <> FND_API.G_MISS_NUM) THEN
738 -- l_tmp_sql_str := ' AND language = USERENV(''LANG''))';
739 -- END IF;
740 --
741 -- OPEN c2(p_display_name, l_tmp_sql_str);
742 -- FETCH c2 INTO l_tmp_section_id;
743 -- IF (c2%FOUND) THEN
744 --
745 -- CLOSE c2;
746 -- IF (l_in_section_id = FND_API.G_MISS_NUM) THEN
747 -- -- For Create Section
748 -- IF (l_tmp_section_id IS NOT NULL) THEN
749 -- -- found duplicate
750 -- RAISE FND_API.G_EXC_ERROR;
751 -- END IF;
752 -- ELSE
753 -- -- For Update Section
754 -- IF (l_tmp_section_id <> l_in_section_id) THEN
755 -- -- found duplicate
756 -- RAISE FND_API.G_EXC_ERROR;
757 -- END IF;
758 -- END IF;
759 -- ELSE
760 -- -- not duplicate
761 -- -- do nothing
762 -- CLOSE c2;
763 -- END IF;
764 --
765 -- END IF;
766
767 EXCEPTION
768
769 WHEN FND_API.G_EXC_ERROR THEN
770 x_return_status := FND_API.G_RET_STS_SUCCESS; -- found duplicate
771 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
772 p_data => x_msg_data,
773 p_encoded => 'F');
774
775 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
776 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
777 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
778 p_data => x_msg_data,
779 p_encoded => 'F');
780
781 WHEN OTHERS THEN
782 FND_MESSAGE.Set_Name('FND', 'SQL_PLSQL_ERROR');
783 FND_MESSAGE.Set_Token('ROUTINE', l_api_name);
784 FND_MESSAGE.Set_Token('ERRNO', SQLCODE);
785 FND_MESSAGE.Set_Token('REASON', SQLERRM);
786 FND_MSG_PUB.Add;
787
788 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
789
790 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
791 THEN
792 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
793 END IF;
794
795 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
796 p_data => x_msg_data,
797 p_encoded => 'F');
798
799 END Check_Duplicate_Entry;
800
801
802 --*****************************************************************************
803 -- PROCEDURE Validate_Create()
804 --*****************************************************************************
805 -- IF x_return_status := FND_API.G_RET_STS_ERROR, then invalid
806 -- IF x_return_status := FND_API.G_RET_STS_SUCCESS, then valid
807
808 PROCEDURE Validate_Create
809 (
810 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
811 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
812 p_access_name IN VARCHAR2,
813 p_start_date_active IN DATE,
814 p_end_date_active IN DATE,
815 p_section_type_code IN VARCHAR2,
816 p_status_code IN VARCHAR2,
817 p_display_context_id IN NUMBER,
818 p_deliverable_id IN NUMBER,
819 p_available_in_all_sites_flag IN VARCHAR2,
820 p_auto_placement_rule IN VARCHAR2,
821 p_order_by_clause IN VARCHAR2,
822 p_display_name IN VARCHAR2,
823 p_description IN VARCHAR2,
824 p_long_description IN VARCHAR2,
825 p_keywords IN VARCHAR2,
826 p_attribute_category IN VARCHAR2,
827 p_attribute1 IN VARCHAR2,
828 p_attribute2 IN VARCHAR2,
829 p_attribute3 IN VARCHAR2,
830 p_attribute4 IN VARCHAR2,
831 p_attribute5 IN VARCHAR2,
832 p_attribute6 IN VARCHAR2,
833 p_attribute7 IN VARCHAR2,
834 p_attribute8 IN VARCHAR2,
835 p_attribute9 IN VARCHAR2,
836 p_attribute10 IN VARCHAR2,
837 p_attribute11 IN VARCHAR2,
838 p_attribute12 IN VARCHAR2,
839 p_attribute13 IN VARCHAR2,
840 p_attribute14 IN VARCHAR2,
841 p_attribute15 IN VARCHAR2,
842 x_return_status OUT NOCOPY VARCHAR2,
843 x_msg_count OUT NOCOPY NUMBER,
844 x_msg_data OUT NOCOPY VARCHAR2
845 )
846 IS
847 l_api_name CONSTANT VARCHAR2(30) := 'Validate_Create';
848 l_api_version CONSTANT NUMBER := 1.0;
849 l_msg_count NUMBER;
850 l_msg_data VARCHAR2(2000);
851
852 l_section_id NUMBER;
853 l_display_context_id NUMBER;
854 l_deliverable_id NUMBER;
855 l_return_status VARCHAR2(1);
856 l_tmp_str VARCHAR2(30);
857
858 CURSOR c3(l_c_status_code IN VARCHAR2)
859 IS SELECT lookup_code FROM fnd_lookup_values
860 WHERE lookup_type = 'IBE_SECTION_STATUS' AND
861 lookup_code = l_c_status_code AND
862 language = USERENV('LANG');
863
864 BEGIN
865
866 -- Initialize message list if p_init_msg_list is set to TRUE.
867 IF FND_API.to_Boolean(p_init_msg_list) THEN
868 FND_MSG_PUB.initialize;
869 END IF;
870
871 -- Initialize API return status to success
872 x_return_status := FND_API.G_RET_STS_SUCCESS;
873
874 --
875 -- Check null values for required fields
876 --
877 -- display_name
878 IF ((p_display_name IS NULL) OR
879 (p_display_name = FND_API.G_MISS_CHAR))
880 THEN
881 FND_MESSAGE.Set_Name('IBE', 'IBE_DSP_SCT_INVALID_DSP_NAME');
882 FND_MSG_PUB.Add;
883 RAISE FND_API.G_EXC_ERROR;
884 END IF;
885
886 -- section_type_code
887 IF ((p_section_type_code IS NULL) OR
888 (p_section_type_code = FND_API.G_MISS_CHAR))
889 THEN
890 FND_MESSAGE.Set_Name('IBE', 'IBE_DSP_SCT_INVALID_SCT_TYPE');
891 FND_MSG_PUB.Add;
892 RAISE FND_API.G_EXC_ERROR;
893 END IF;
894
895 -- status_code
896 IF ((p_status_code IS NULL) OR
897 (p_status_code = FND_API.G_MISS_CHAR))
898 THEN
899 FND_MESSAGE.Set_Name('IBE', 'IBE_DSP_SCT_INVALID_STATUS');
900 FND_MSG_PUB.Add;
901 RAISE FND_API.G_EXC_ERROR;
902 END IF;
903
904 -- start_date_active
905 IF ((p_start_date_active IS NULL) OR
906 (p_start_date_active = FND_API.G_MISS_DATE))
907 THEN
908 FND_MESSAGE.Set_Name('IBE', 'IBE_DSP_SCT_INVALID_START_DATE');
909 FND_MSG_PUB.Add;
910 RAISE FND_API.G_EXC_ERROR;
911 END IF;
912
913 --
914 -- non-null field validation
915 --
916 -- p_available_in_all_sites_flag
917 IF ((p_available_in_all_sites_flag IS NOT NULL) AND
918 (p_available_in_all_sites_flag <> FND_API.G_MISS_CHAR))
919 THEN
920 IF(p_available_in_all_sites_flag <> 'Y' AND
921 p_available_in_all_sites_flag <> 'N')
922 THEN
923 FND_MESSAGE.Set_Name('IBE', 'IBE_DSP_SCT_INVALID_AVL_FLAG');
924 FND_MSG_PUB.Add;
925 RAISE FND_API.G_EXC_ERROR;
926 END IF;
927 END IF;
928
929 -- p_auto_placement_rule
930 IF ((p_auto_placement_rule IS NOT NULL) AND
931 (p_auto_placement_rule <> FND_API.G_MISS_CHAR))
932 THEN
933 Is_SQL_Valid
934 (
935 p_auto_placement_rule,
936 x_return_status
937 );
938
939 IF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
940 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
941 ELSIF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
942 FND_MESSAGE.Set_Name('IBE', 'IBE_DSP_SCT_INVALID_AUTO_PLACE');
943 FND_MESSAGE.Set_Token('AUTO_PLACEMENT_RULE', p_auto_placement_rule);
944 FND_MSG_PUB.Add;
945 RAISE FND_API.G_EXC_ERROR;
946 END IF;
947
948 END IF;
949
950 -- p_order_by_clause
951 IF ((p_order_by_clause IS NOT NULL) AND
952 (p_order_by_clause <> FND_API.G_MISS_CHAR))
953 THEN
954 Is_SQL_Valid
955 (
956 'SELECT rowid FROM mtl_system_items_vl WHERE rownum < 1 '
957 || ' ORDER BY ' || p_order_by_clause,
958 x_return_status
959 );
960
961 IF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
962 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
963 ELSIF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
964 FND_MESSAGE.Set_Name('IBE', 'IBE_DSP_SCT_INVALID_ORDER_BY');
965 FND_MESSAGE.Set_Token('ORDER_BY_CLAUSE', p_order_by_clause);
966 FND_MSG_PUB.Add;
967 RAISE FND_API.G_EXC_ERROR;
968 END IF;
969
970 END IF;
971
972 -- Validate if the section is duplicate
973 Check_Duplicate_Entry(p_init_msg_list => FND_API.G_FALSE,
974 p_section_id => FND_API.G_MISS_NUM,
975 p_access_name => p_access_name,
976 p_display_name => p_display_name,
977 x_return_status => l_return_status,
978 x_msg_count => l_msg_count,
979 x_msg_data => l_msg_data);
980
981 IF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
982 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
983 ELSIF (l_return_status = FND_API.G_RET_STS_SUCCESS) THEN
984 FND_MESSAGE.Set_Name('IBE', 'IBE_DSP_SCT_DUPLICATE_SECT');
985 FND_MSG_PUB.Add;
986 RAISE FND_API.G_EXC_ERROR; -- duplicate section
987 END IF;
988
989 --
990 -- Foreign key integrity constraint check
991 --
992 -- display context id
993 IF ((p_display_context_id IS NOT NULL) AND
994 (p_display_context_id <> FND_API.G_MISS_NUM))
995 THEN
996 BEGIN
997 SELECT context_id INTO l_display_context_id FROM ibe_dsp_context_b
998 WHERE context_id = p_display_context_id;
999 EXCEPTION
1000 WHEN NO_DATA_FOUND THEN
1001 FND_MESSAGE.Set_Name('IBE', 'IBE_DSP_SCT_NO_DSP_CTX');
1002 FND_MESSAGE.Set_Token('DISPLAY_CONTEXT', p_display_context_id);
1003 FND_MSG_PUB.Add;
1004 RAISE FND_API.G_EXC_ERROR;
1005 WHEN OTHERS THEN
1006 FND_MESSAGE.Set_Name('FND', 'SQL_PLSQL_ERROR');
1007 FND_MESSAGE.Set_Token('ROUTINE', l_api_name);
1008 FND_MESSAGE.Set_Token('ERRNO', SQLCODE);
1009 FND_MESSAGE.Set_Token('REASON', SQLERRM);
1010 FND_MSG_PUB.Add;
1011
1012 FND_MESSAGE.Set_Name('IBE', 'IBE_DSP_SCT_GET_DSP_CTX');
1013 FND_MSG_PUB.Add;
1014 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1015 END;
1016 END IF;
1017
1018 -- deliverable id
1019 IF ((p_deliverable_id IS NOT NULL) AND
1020 (p_deliverable_id <> FND_API.G_MISS_NUM))
1021 THEN
1022 BEGIN
1023 SELECT item_id INTO l_deliverable_id FROM jtf_amv_items_b
1024 WHERE item_id = p_deliverable_id;
1025 EXCEPTION
1026 WHEN NO_DATA_FOUND THEN
1027 FND_MESSAGE.Set_Name('IBE', 'IBE_DSP_SCT_NO_DLVRBL');
1028 FND_MESSAGE.Set_Token('DELIVERABLE', p_deliverable_id);
1029 FND_MSG_PUB.Add;
1030 RAISE FND_API.G_EXC_ERROR;
1031 WHEN OTHERS THEN
1032 FND_MESSAGE.Set_Name('FND', 'SQL_PLSQL_ERROR');
1033 FND_MESSAGE.Set_Token('ROUTINE', l_api_name);
1034 FND_MESSAGE.Set_Token('ERRNO', SQLCODE);
1035 FND_MESSAGE.Set_Token('REASON', SQLERRM);
1036 FND_MSG_PUB.Add;
1037
1038 FND_MESSAGE.Set_Name('IBE', 'IBE_DSP_SCT_GET_DLVRBL');
1039 FND_MSG_PUB.Add;
1040 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1041 END;
1042 END IF;
1043
1044 -- section type code
1045 -- note that p_section_type_code won't be NULL due to previous checks
1046 BEGIN
1047 SELECT lookup_code INTO l_tmp_str FROM fnd_lookup_values
1048 WHERE lookup_type = 'IBE_SECTION_TYPE' AND
1049 lookup_code = p_section_type_code AND
1050 language = USERENV('LANG');
1051 EXCEPTION
1052 WHEN NO_DATA_FOUND THEN
1053 FND_MESSAGE.Set_Name('IBE', 'IBE_DSP_SCT_NO_SCT_TYPE');
1054 FND_MESSAGE.Set_Token('SECTION_TYPE', p_section_type_code);
1055 FND_MSG_PUB.Add;
1056 RAISE FND_API.G_EXC_ERROR;
1057 WHEN OTHERS THEN
1058 FND_MESSAGE.Set_Name('FND', 'SQL_PLSQL_ERROR');
1059 FND_MESSAGE.Set_Token('ROUTINE', l_api_name);
1060 FND_MESSAGE.Set_Token('ERRNO', SQLCODE);
1061 FND_MESSAGE.Set_Token('REASON', SQLERRM);
1062 FND_MSG_PUB.Add;
1063
1064 FND_MESSAGE.Set_Name('IBE', 'IBE_DSP_SCT_GET_SCT_TYPE');
1065 FND_MSG_PUB.Add;
1066 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1067 END;
1068
1069 -- status code
1070 -- note that p_status_code won't be NULL due to previous checks
1071 OPEN c3(p_status_code);
1072 FETCH c3 INTO l_tmp_str;
1073 IF (c3%NOTFOUND) THEN
1074 CLOSE c3;
1075 FND_MESSAGE.Set_Name('IBE', 'IBE_DSP_SCT_NO_STATUS');
1076 FND_MESSAGE.Set_Token('STATUS', p_status_code);
1077 FND_MSG_PUB.Add;
1078 RAISE FND_API.G_EXC_ERROR;
1079 END IF;
1080 CLOSE c3;
1081
1082 EXCEPTION
1083
1084 WHEN FND_API.G_EXC_ERROR THEN
1085 x_return_status := FND_API.G_RET_STS_ERROR;
1086 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
1087 p_data => x_msg_data,
1088 p_encoded => 'F');
1089
1090 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1091 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1092 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
1093 p_data => x_msg_data,
1094 p_encoded => 'F');
1095
1096 WHEN OTHERS THEN
1097 FND_MESSAGE.Set_Name('FND', 'SQL_PLSQL_ERROR');
1098 FND_MESSAGE.Set_Token('ROUTINE', l_api_name);
1099 FND_MESSAGE.Set_Token('ERRNO', SQLCODE);
1100 FND_MESSAGE.Set_Token('REASON', SQLERRM);
1101 FND_MSG_PUB.Add;
1102
1103 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1104
1105 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1106 THEN
1107 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
1108 END IF;
1109
1110 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
1111 p_data => x_msg_data,
1112 p_encoded => 'F');
1113
1114 END Validate_Create;
1115
1116
1117 --*****************************************************************************
1118 -- PROCEDURE Validate_Update()
1119 --*****************************************************************************
1120 -- IF x_return_status := FND_API.G_RET_STS_ERROR, then invalid
1121 -- IF x_return_status := FND_API.G_RET_STS_SUCCESS, then valid
1122
1123 PROCEDURE Validate_Update
1124 (
1125 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1126 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1127 p_section_id IN NUMBER,
1128 p_object_version_number IN NUMBER,
1129 p_access_name IN VARCHAR2,
1130 p_start_date_active IN DATE,
1131 p_end_date_active IN DATE,
1132 p_section_type_code IN VARCHAR2,
1133 p_status_code IN VARCHAR2,
1134 p_display_context_id IN NUMBER,
1135 p_deliverable_id IN NUMBER,
1136 p_available_in_all_sites_flag IN VARCHAR2,
1137 p_auto_placement_rule IN VARCHAR2,
1138 p_order_by_clause IN VARCHAR2,
1139 p_display_name IN VARCHAR2,
1140 p_description IN VARCHAR2,
1141 p_long_description IN VARCHAR2,
1142 p_keywords IN VARCHAR2,
1143 p_attribute_category IN VARCHAR2,
1144 p_attribute1 IN VARCHAR2,
1145 p_attribute2 IN VARCHAR2,
1146 p_attribute3 IN VARCHAR2,
1147 p_attribute4 IN VARCHAR2,
1148 p_attribute5 IN VARCHAR2,
1149 p_attribute6 IN VARCHAR2,
1150 p_attribute7 IN VARCHAR2,
1151 p_attribute8 IN VARCHAR2,
1152 p_attribute9 IN VARCHAR2,
1153 p_attribute10 IN VARCHAR2,
1154 p_attribute11 IN VARCHAR2,
1155 p_attribute12 IN VARCHAR2,
1156 p_attribute13 IN VARCHAR2,
1157 p_attribute14 IN VARCHAR2,
1158 p_attribute15 IN VARCHAR2,
1159 x_return_status OUT NOCOPY VARCHAR2,
1160 x_msg_count OUT NOCOPY NUMBER,
1161 x_msg_data OUT NOCOPY VARCHAR2
1162 )
1163 IS
1164 l_api_name CONSTANT VARCHAR2(30) := 'Validate_Update';
1165 l_api_version CONSTANT NUMBER := 1.0;
1166 l_msg_count NUMBER;
1167 l_msg_data VARCHAR2(2000);
1168
1169 l_section_id NUMBER;
1170 l_display_context_id NUMBER;
1171 l_deliverable_id NUMBER;
1172 l_tmp_str VARCHAR2(30);
1173 l_return_status VARCHAR2(1);
1174
1175 CURSOR c3(l_c_status_code IN VARCHAR2)
1176 IS SELECT lookup_code FROM fnd_lookup_values
1177 WHERE lookup_type = 'IBE_SECTION_STATUS' AND
1178 lookup_code = l_c_status_code AND
1179 language = USERENV('LANG');
1180
1181 BEGIN
1182
1183 -- Initialize message list if p_init_msg_list is set to TRUE.
1184 IF FND_API.to_Boolean(p_init_msg_list) THEN
1185 FND_MSG_PUB.initialize;
1186 END IF;
1187
1188 -- Initialize API return status to success
1189 x_return_status := FND_API.G_RET_STS_SUCCESS;
1190
1191 --
1192 -- Check null values for required fields
1193 --
1194 -- section_id
1195 IF (p_section_id IS NULL) THEN
1196 FND_MESSAGE.Set_Name('IBE', 'IBE_DSP_SCT_NULL_SCT_ID');
1197 FND_MSG_PUB.Add;
1198 RAISE FND_API.G_EXC_ERROR;
1199 END IF;
1200
1201 -- display_name
1202 IF (p_display_name IS NULL) THEN
1203 FND_MESSAGE.Set_Name('IBE', 'IBE_DSP_SCT_NULL_DSP_NAME');
1204 FND_MSG_PUB.Add;
1205 RAISE FND_API.G_EXC_ERROR;
1206 END IF;
1207
1208 -- section_type_code
1209 IF (p_section_type_code IS NULL) THEN
1210 FND_MESSAGE.Set_Name('IBE', 'IBE_DSP_SCT_NULL_SCT_TYPE');
1211 FND_MSG_PUB.Add;
1212 RAISE FND_API.G_EXC_ERROR;
1213 END IF;
1214
1215 -- status_code
1216 IF (p_status_code IS NULL) THEN
1217 FND_MESSAGE.Set_Name('IBE', 'IBE_DSP_SCT_NULL_STATUS');
1218 FND_MSG_PUB.Add;
1219 RAISE FND_API.G_EXC_ERROR;
1220 END IF;
1221
1222 -- start_date_active
1223 IF (p_start_date_active IS NULL) THEN
1224 FND_MESSAGE.Set_Name('IBE', 'IBE_DSP_SCT_NULL_START_DATE');
1225 FND_MSG_PUB.Add;
1226 RAISE FND_API.G_EXC_ERROR;
1227 END IF;
1228
1229 -- non-null field validation
1230 IF ((p_available_in_all_sites_flag IS NOT NULL) AND
1231 (p_available_in_all_sites_flag <> FND_API.G_MISS_CHAR))
1232 THEN
1233 IF(p_available_in_all_sites_flag <> 'Y' AND
1234 p_available_in_all_sites_flag <> 'N')
1235 THEN
1236 FND_MESSAGE.Set_Name('IBE', 'IBE_DSP_SCT_INVALID_AVL_FLAG');
1237 FND_MSG_PUB.Add;
1238 RAISE FND_API.G_EXC_ERROR;
1239 END IF;
1240 END IF;
1241
1242 -- p_auto_placement_rule
1243 IF ((p_auto_placement_rule IS NOT NULL) AND
1244 (p_auto_placement_rule <> FND_API.G_MISS_CHAR))
1245 THEN
1246 Is_SQL_Valid
1247 (
1248 p_auto_placement_rule,
1249 x_return_status
1250 );
1251
1252 IF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
1253 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1254 ELSIF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
1255 FND_MESSAGE.Set_Name('IBE', 'IBE_DSP_SCT_INVALID_AUTO_PLACE');
1256 FND_MESSAGE.Set_Token('AUTO_PLACEMENT_RULE', p_auto_placement_rule);
1257 FND_MSG_PUB.Add;
1258 RAISE FND_API.G_EXC_ERROR;
1259 END IF;
1260
1261 END IF;
1262
1263 -- p_order_by_clause
1264 IF ((p_order_by_clause IS NOT NULL) AND
1265 (p_order_by_clause <> FND_API.G_MISS_CHAR))
1266 THEN
1267 Is_SQL_Valid
1268 (
1269 'SELECT rowid FROM mtl_system_items_vl WHERE rownum < 1 '
1270 || ' ORDER BY ' || p_order_by_clause,
1271 x_return_status
1272 );
1273
1274 IF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
1275 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1276 ELSIF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
1277 FND_MESSAGE.Set_Name('IBE', 'IBE_DSP_SCT_INVALID_ORDER_BY');
1278 FND_MESSAGE.Set_Token('ORDER_BY_CLAUSE', p_order_by_clause);
1279 FND_MSG_PUB.Add;
1280 RAISE FND_API.G_EXC_ERROR;
1281 END IF;
1282
1283 END IF;
1284
1285 -- Validate if the (unique) fields to be updated doesn't already exist
1286 -- for some other section
1287 Check_Duplicate_Entry(p_init_msg_list => FND_API.G_FALSE,
1288 p_section_id => p_section_id,
1289 p_access_name => p_access_name,
1290 p_display_name => p_display_name,
1291 x_return_status => l_return_status,
1292 x_msg_count => l_msg_count,
1293 x_msg_data => l_msg_data);
1294
1295 IF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
1296 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1297 ELSIF (l_return_status = FND_API.G_RET_STS_SUCCESS) THEN
1298 FND_MESSAGE.Set_Name('IBE', 'IBE_DSP_SCT_DUPLICATE_SECT');
1299 FND_MSG_PUB.Add;
1300 RAISE FND_API.G_EXC_ERROR; -- duplicate fields
1301 END IF;
1302
1303 --
1304 -- Foreign key integrity constraint check
1305 --
1306 -- display context id
1307 IF ((p_display_context_id IS NOT NULL) AND
1308 (p_display_context_id <> FND_API.G_MISS_NUM))
1309 THEN
1310 BEGIN
1311 SELECT context_id INTO l_display_context_id FROM ibe_dsp_context_b
1312 WHERE context_id = p_display_context_id;
1313 EXCEPTION
1314 WHEN NO_DATA_FOUND THEN
1315 FND_MESSAGE.Set_Name('IBE', 'IBE_DSP_SCT_NO_DSP_CTX');
1316 FND_MESSAGE.Set_Token('DISPLAY_CONTEXT', p_display_context_id);
1317 FND_MSG_PUB.Add;
1318 RAISE FND_API.G_EXC_ERROR;
1319 WHEN OTHERS THEN
1320 FND_MESSAGE.Set_Name('FND', 'SQL_PLSQL_ERROR');
1321 FND_MESSAGE.Set_Token('ROUTINE', l_api_name);
1322 FND_MESSAGE.Set_Token('ERRNO', SQLCODE);
1323 FND_MESSAGE.Set_Token('REASON', SQLERRM);
1324 FND_MSG_PUB.Add;
1325
1326 FND_MESSAGE.Set_Name('IBE', 'IBE_DSP_SCT_GET_DSP_CTX');
1327 FND_MSG_PUB.Add;
1328 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1329 END;
1330 END IF;
1331
1332 -- deliverable id
1333 IF ((p_deliverable_id IS NOT NULL) AND
1334 (p_deliverable_id <> FND_API.G_MISS_NUM))
1335 THEN
1336 BEGIN
1337 SELECT item_id INTO l_deliverable_id FROM jtf_amv_items_b
1338 WHERE item_id = p_deliverable_id;
1339 EXCEPTION
1340 WHEN NO_DATA_FOUND THEN
1341 FND_MESSAGE.Set_Name('IBE', 'IBE_DSP_SCT_NO_DLVRBL');
1342 FND_MESSAGE.Set_Token('DELIVERABLE', p_deliverable_id);
1343 FND_MSG_PUB.Add;
1344 RAISE FND_API.G_EXC_ERROR;
1345 WHEN OTHERS THEN
1346 FND_MESSAGE.Set_Name('FND', 'SQL_PLSQL_ERROR');
1347 FND_MESSAGE.Set_Token('ROUTINE', l_api_name);
1348 FND_MESSAGE.Set_Token('ERRNO', SQLCODE);
1349 FND_MESSAGE.Set_Token('REASON', SQLERRM);
1350 FND_MSG_PUB.Add;
1351
1352 FND_MESSAGE.Set_Name('IBE', 'IBE_DSP_SCT_GET_DLVRBL');
1353 FND_MSG_PUB.Add;
1354 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1355 END;
1356 END IF;
1357
1358 -- section type code
1359 -- note that p_section_type_code won't be NULL due to previous checks
1360 IF (p_section_type_code <> FND_API.G_MISS_CHAR) THEN
1361 BEGIN
1362 SELECT lookup_code INTO l_tmp_str FROM fnd_lookup_values
1363 WHERE lookup_type = 'IBE_SECTION_TYPE' AND
1364 lookup_code = p_section_type_code AND
1365 language = USERENV('LANG');
1366 EXCEPTION
1367 WHEN NO_DATA_FOUND THEN
1368 FND_MESSAGE.Set_Name('IBE', 'IBE_DSP_SCT_NO_SCT_TYPE');
1369 FND_MESSAGE.Set_Token('SECTION_TYPE', p_section_type_code);
1370 FND_MSG_PUB.Add;
1371 RAISE FND_API.G_EXC_ERROR;
1372 WHEN OTHERS THEN
1373 FND_MESSAGE.Set_Name('FND', 'SQL_PLSQL_ERROR');
1374 FND_MESSAGE.Set_Token('ROUTINE', l_api_name);
1375 FND_MESSAGE.Set_Token('ERRNO', SQLCODE);
1376 FND_MESSAGE.Set_Token('REASON', SQLERRM);
1377 FND_MSG_PUB.Add;
1378
1379 FND_MESSAGE.Set_Name('IBE', 'IBE_DSP_SCT_GET_SCT_TYPE');
1380 FND_MSG_PUB.Add;
1381 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1382 END;
1383 END IF;
1384
1385 -- status code
1386 -- note that p_status_code won't be NULL due to previous checks
1387 IF (p_status_code <> FND_API.G_MISS_CHAR) THEN
1388 OPEN c3(p_status_code);
1389 FETCH c3 INTO l_tmp_str;
1390 IF (c3%NOTFOUND) THEN
1391 CLOSE c3;
1392 FND_MESSAGE.Set_Name('IBE', 'IBE_DSP_SCT_NO_STATUS');
1393 FND_MESSAGE.Set_Token('STATUS', p_status_code);
1394 FND_MSG_PUB.Add;
1395 RAISE FND_API.G_EXC_ERROR;
1396 END IF;
1397 CLOSE c3;
1398 END IF;
1399
1400 EXCEPTION
1401
1402 WHEN FND_API.G_EXC_ERROR THEN
1403 x_return_status := FND_API.G_RET_STS_ERROR;
1404 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
1405 p_data => x_msg_data,
1406 p_encoded => 'F');
1407
1408 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1409 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1410 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
1411 p_data => x_msg_data,
1412 p_encoded => 'F');
1413
1414 WHEN OTHERS THEN
1415 FND_MESSAGE.Set_Name('FND', 'SQL_PLSQL_ERROR');
1416 FND_MESSAGE.Set_Token('ROUTINE', l_api_name);
1417 FND_MESSAGE.Set_Token('ERRNO', SQLCODE);
1418 FND_MESSAGE.Set_Token('REASON', SQLERRM);
1419 FND_MSG_PUB.Add;
1420
1421 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1422
1423 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1424 THEN
1425 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
1426 END IF;
1427
1428 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
1429 p_data => x_msg_data,
1430 p_encoded => 'F');
1431
1432 END Validate_Update;
1433
1434
1435 -- ****************************************************************************
1436 --*****************************************************************************
1437
1438 PROCEDURE Create_Section
1439 (
1440 p_api_version IN NUMBER,
1441 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1442 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1443 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1444 p_access_name IN VARCHAR2 := FND_API.G_MISS_CHAR,
1445 p_start_date_active IN DATE,
1446 p_end_date_active IN DATE := FND_API.G_MISS_DATE,
1447 p_section_type_code IN VARCHAR2,
1448 p_status_code IN VARCHAR2,
1449 p_display_context_id IN NUMBER := FND_API.G_MISS_NUM,
1450 p_deliverable_id IN NUMBER := FND_API.G_MISS_NUM,
1451 p_available_in_all_sites_flag IN VARCHAR2 := FND_API.G_MISS_CHAR,
1452 p_auto_placement_rule IN VARCHAR2 := FND_API.G_MISS_CHAR,
1453 p_order_by_clause IN VARCHAR2 := FND_API.G_MISS_CHAR,
1454 p_display_name IN VARCHAR2,
1455 p_description IN VARCHAR2 := FND_API.G_MISS_CHAR,
1456 p_long_description IN VARCHAR2 := FND_API.G_MISS_CHAR,
1457 p_keywords IN VARCHAR2 := FND_API.G_MISS_CHAR,
1458 p_attribute_category IN VARCHAR2 := FND_API.G_MISS_CHAR,
1459 p_attribute1 IN VARCHAR2 := FND_API.G_MISS_CHAR,
1460 p_attribute2 IN VARCHAR2 := FND_API.G_MISS_CHAR,
1461 p_attribute3 IN VARCHAR2 := FND_API.G_MISS_CHAR,
1462 p_attribute4 IN VARCHAR2 := FND_API.G_MISS_CHAR,
1463 p_attribute5 IN VARCHAR2 := FND_API.G_MISS_CHAR,
1464 p_attribute6 IN VARCHAR2 := FND_API.G_MISS_CHAR,
1465 p_attribute7 IN VARCHAR2 := FND_API.G_MISS_CHAR,
1466 p_attribute8 IN VARCHAR2 := FND_API.G_MISS_CHAR,
1467 p_attribute9 IN VARCHAR2 := FND_API.G_MISS_CHAR,
1468 p_attribute10 IN VARCHAR2 := FND_API.G_MISS_CHAR,
1469 p_attribute11 IN VARCHAR2 := FND_API.G_MISS_CHAR,
1470 p_attribute12 IN VARCHAR2 := FND_API.G_MISS_CHAR,
1471 p_attribute13 IN VARCHAR2 := FND_API.G_MISS_CHAR,
1472 p_attribute14 IN VARCHAR2 := FND_API.G_MISS_CHAR,
1473 p_attribute15 IN VARCHAR2 := FND_API.G_MISS_CHAR,
1474 x_section_id OUT NOCOPY NUMBER,
1475 x_return_status OUT NOCOPY VARCHAR2,
1476 x_msg_count OUT NOCOPY NUMBER,
1477 x_msg_data OUT NOCOPY VARCHAR2
1478 )
1479 IS
1480 l_api_name CONSTANT VARCHAR2(30) := 'Create_Section';
1481 l_api_version CONSTANT NUMBER := 1.0;
1482 l_msg_count NUMBER;
1483 l_msg_data VARCHAR2(2000);
1484 l_return_status VARCHAR2(1);
1485
1486 l_object_version_number CONSTANT NUMBER := 1;
1487 l_rowid VARCHAR2(30);
1488
1489 BEGIN
1490
1491 -- Standard Start of API savepoint
1492 SAVEPOINT CREATE_SECTION_GRP;
1493
1494 -- Standard call to check for call compatibility.
1495 IF NOT FND_API.Compatible_API_Call(l_api_version,
1496 p_api_version,
1497 l_api_name,
1498 G_PKG_NAME)
1499 THEN
1500 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1501 END IF;
1502
1503 -- Initialize message list if p_init_msg_list is set to TRUE.
1504 IF FND_API.to_Boolean(p_init_msg_list) THEN
1505 FND_MSG_PUB.initialize;
1506 END IF;
1507
1508 -- Initialize API return status to success
1509 x_return_status := FND_API.G_RET_STS_SUCCESS;
1510
1511 -- API body
1512 -- CALL FLOW :
1513 -- 1. Check if section is valid
1514 -- 2. Insert row with section data into section table
1515 --
1516
1517 --
1518 -- 1. Check if section is valid
1519 --
1520 Validate_Create
1521 (
1522 p_init_msg_list => FND_API.G_FALSE,
1523 p_validation_level => p_validation_level,
1524 p_access_name => p_access_name,
1525 p_start_date_active => p_start_date_active,
1526 p_end_date_active => p_end_date_active,
1527 p_section_type_code => p_section_type_code,
1528 p_status_code => p_status_code,
1529 p_display_context_id => p_display_context_id,
1530 p_deliverable_id => p_deliverable_id,
1531 p_available_in_all_sites_flag => p_available_in_all_sites_flag,
1532 p_auto_placement_rule => p_auto_placement_rule,
1533 p_order_by_clause => p_order_by_clause,
1534 p_display_name => p_display_name,
1535 p_description => p_description,
1536 p_long_description => p_long_description,
1537 p_keywords => p_keywords,
1538 p_attribute_category => p_attribute_category,
1539 p_attribute1 => p_attribute1,
1540 p_attribute2 => p_attribute2,
1541 p_attribute3 => p_attribute3,
1542 p_attribute4 => p_attribute4,
1543 p_attribute5 => p_attribute5,
1544 p_attribute6 => p_attribute6,
1545 p_attribute7 => p_attribute7,
1546 p_attribute8 => p_attribute8,
1547 p_attribute9 => p_attribute9,
1548 p_attribute10 => p_attribute10,
1549 p_attribute11 => p_attribute11,
1550 p_attribute12 => p_attribute12,
1551 p_attribute13 => p_attribute13,
1552 p_attribute14 => p_attribute14,
1553 p_attribute15 => p_attribute15,
1554 x_return_status => l_return_status,
1555 x_msg_count => l_msg_count,
1556 x_msg_data => l_msg_data
1557 );
1558
1559 IF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
1560 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1561 ELSIF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
1562 FND_MESSAGE.Set_Name('IBE', 'IBE_DSP_SCT_INVALID_CREATE');
1563 FND_MSG_PUB.Add;
1564 RAISE FND_API.G_EXC_ERROR; -- invalid section
1565 END IF;
1566
1567 --
1568 -- 2. Insert row with query data into query table
1569 --
1570 BEGIN
1571 insert_row
1572 (
1573 FND_API.G_MISS_NUM,
1574 l_object_version_number,
1575 p_access_name,
1576 p_start_date_active,
1577 p_end_date_active,
1578 p_section_type_code,
1579 p_status_code,
1580 p_display_context_id,
1581 p_deliverable_id,
1582 p_available_in_all_sites_flag,
1583 p_auto_placement_rule,
1584 p_order_by_clause,
1585 p_attribute_category,
1586 p_attribute1,
1587 p_attribute2,
1588 p_attribute3,
1589 p_attribute4,
1590 p_attribute5,
1591 p_attribute6,
1592 p_attribute7,
1593 p_attribute8,
1594 p_attribute9,
1595 p_attribute10,
1596 p_attribute11,
1597 p_attribute12,
1598 p_attribute13,
1599 p_attribute14,
1600 p_attribute15,
1601 p_display_name,
1602 p_description,
1603 p_long_description,
1604 p_keywords,
1605 SYSDATE,
1606 FND_GLOBAL.user_id,
1607 SYSDATE,
1608 FND_GLOBAL.user_id,
1609 FND_GLOBAL.login_id,
1610 l_rowid,
1611 x_section_id
1612 );
1613 EXCEPTION
1614 WHEN NO_DATA_FOUND THEN
1615 FND_MESSAGE.Set_Name('IBE', 'IBE_DSP_SCT_INSERT_FAIL');
1616 FND_MSG_PUB.Add;
1617 RAISE FND_API.G_EXC_ERROR;
1618 WHEN OTHERS THEN
1619 FND_MESSAGE.Set_Name('FND', 'SQL_PLSQL_ERROR');
1620 FND_MESSAGE.Set_Token('ROUTINE', l_api_name);
1621 FND_MESSAGE.Set_Token('ERRNO', SQLCODE);
1622 FND_MESSAGE.Set_Token('REASON', SQLERRM);
1623 FND_MSG_PUB.Add;
1624
1625 FND_MESSAGE.Set_Name('IBE', 'IBE_DSP_SCT_INSERT_FAIL');
1626 FND_MSG_PUB.Add;
1627 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1628 END;
1629
1630 --
1631 -- End of main API body.
1632
1633 -- Standard check of p_commit.
1634 IF (FND_API.To_Boolean(p_commit)) THEN
1635 COMMIT WORK;
1636 END IF;
1637
1638 -- Standard call to get message count and if count is 1, get message info.
1639 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
1640 p_data => x_msg_data,
1641 p_encoded => 'F');
1642
1643 EXCEPTION
1644
1645 WHEN FND_API.G_EXC_ERROR THEN
1646 ROLLBACK TO CREATE_SECTION_GRP;
1647 x_return_status := FND_API.G_RET_STS_ERROR;
1648 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
1649 p_data => x_msg_data,
1650 p_encoded => 'F');
1651
1652 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1653 ROLLBACK TO CREATE_SECTION_GRP;
1654 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1655 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
1656 p_data => x_msg_data,
1657 p_encoded => 'F');
1658
1659 WHEN OTHERS THEN
1660 ROLLBACK TO CREATE_SECTION_GRP;
1661
1662 FND_MESSAGE.Set_Name('FND', 'SQL_PLSQL_ERROR');
1663 FND_MESSAGE.Set_Token('ROUTINE', l_api_name);
1664 FND_MESSAGE.Set_Token('ERRNO', SQLCODE);
1665 FND_MESSAGE.Set_Token('REASON', SQLERRM);
1666 FND_MSG_PUB.Add;
1667
1668 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1669
1670 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1671 THEN
1672 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
1673 END IF;
1674
1675 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
1676 p_data => x_msg_data,
1677 p_encoded => 'F');
1678
1679 END Create_Section;
1680
1681 PROCEDURE Update_Section
1682 (
1683 p_api_version IN NUMBER,
1684 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1685 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1686 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1687 p_section_id IN NUMBER := FND_API.G_MISS_NUM,
1688 p_object_version_number IN NUMBER,
1689 p_access_name IN VARCHAR2 := FND_API.G_MISS_CHAR,
1690 p_start_date_active IN DATE := FND_API.G_MISS_DATE,
1691 p_end_date_active IN DATE := FND_API.G_MISS_DATE,
1692 p_section_type_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
1693 p_status_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
1694 p_display_context_id IN NUMBER := FND_API.G_MISS_NUM,
1695 p_deliverable_id IN NUMBER := FND_API.G_MISS_NUM,
1696 p_available_in_all_sites_flag IN VARCHAR2 := FND_API.G_MISS_CHAR,
1697 p_auto_placement_rule IN VARCHAR2 := FND_API.G_MISS_CHAR,
1698 p_order_by_clause IN VARCHAR2 := FND_API.G_MISS_CHAR,
1699 p_display_name IN VARCHAR2 := FND_API.G_MISS_CHAR,
1700 p_description IN VARCHAR2 := FND_API.G_MISS_CHAR,
1701 p_long_description IN VARCHAR2 := FND_API.G_MISS_CHAR,
1702 p_keywords IN VARCHAR2 := FND_API.G_MISS_CHAR,
1703 p_attribute_category IN VARCHAR2 := FND_API.G_MISS_CHAR,
1704 p_attribute1 IN VARCHAR2 := FND_API.G_MISS_CHAR,
1705 p_attribute2 IN VARCHAR2 := FND_API.G_MISS_CHAR,
1706 p_attribute3 IN VARCHAR2 := FND_API.G_MISS_CHAR,
1707 p_attribute4 IN VARCHAR2 := FND_API.G_MISS_CHAR,
1708 p_attribute5 IN VARCHAR2 := FND_API.G_MISS_CHAR,
1709 p_attribute6 IN VARCHAR2 := FND_API.G_MISS_CHAR,
1710 p_attribute7 IN VARCHAR2 := FND_API.G_MISS_CHAR,
1711 p_attribute8 IN VARCHAR2 := FND_API.G_MISS_CHAR,
1712 p_attribute9 IN VARCHAR2 := FND_API.G_MISS_CHAR,
1713 p_attribute10 IN VARCHAR2 := FND_API.G_MISS_CHAR,
1714 p_attribute11 IN VARCHAR2 := FND_API.G_MISS_CHAR,
1715 p_attribute12 IN VARCHAR2 := FND_API.G_MISS_CHAR,
1716 p_attribute13 IN VARCHAR2 := FND_API.G_MISS_CHAR,
1717 p_attribute14 IN VARCHAR2 := FND_API.G_MISS_CHAR,
1718 p_attribute15 IN VARCHAR2 := FND_API.G_MISS_CHAR,
1719 x_return_status OUT NOCOPY VARCHAR2,
1720 x_msg_count OUT NOCOPY NUMBER,
1721 x_msg_data OUT NOCOPY VARCHAR2
1722 )
1723 IS
1724 l_api_name CONSTANT VARCHAR2(30) := 'Update_Section';
1725 l_api_version CONSTANT NUMBER := 1.0;
1726 l_msg_count NUMBER;
1727 l_msg_data VARCHAR2(2000);
1728
1729 l_section_id NUMBER;
1730 l_return_status VARCHAR2(1);
1731
1732 BEGIN
1733
1734 -- Standard Start of API savepoint
1735 SAVEPOINT UPDATE_SECTION_GRP;
1736
1737 -- Standard call to check for call compatibility.
1738 IF NOT FND_API.Compatible_API_Call(l_api_version,
1739 p_api_version,
1740 l_api_name,
1741 G_PKG_NAME)
1742 THEN
1743 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1744 END IF;
1745
1746 -- Initialize message list if p_init_msg_list is set to TRUE.
1747 IF FND_API.to_Boolean(p_init_msg_list) THEN
1748 FND_MSG_PUB.initialize;
1749 END IF;
1750
1751 -- Initialize API return status to success
1752 x_return_status := FND_API.G_RET_STS_SUCCESS;
1753
1754 -- API body
1755 -- CALL FLOW :
1756 -- 1. Check if either section_id or access_name is specified
1757 -- 2. Update row with section data into section table
1758
1759 -- 1. Check if either section_id or access_name is specified
1760 IF ((p_section_id IS NOT NULL) AND
1761 (p_section_id <> FND_API.G_MISS_NUM))
1762 THEN
1763 l_section_id := p_section_id; -- section_id specified, continue
1764 ELSIF ((p_access_name IS NOT NULL) AND
1765 (p_access_name <> FND_API.G_MISS_CHAR))
1766 THEN
1767 -- If access_name specified and section_id is not specified, then
1768 -- query for section id
1769 BEGIN
1770 SELECT section_id INTO l_section_id FROM ibe_dsp_sections_b
1771 WHERE access_name = p_access_name;
1772 EXCEPTION
1773 WHEN NO_DATA_FOUND THEN
1774 FND_MESSAGE.Set_Name('IBE', 'IBE_DSP_SCT_NO_SCT_ACSS_NAME');
1775 FND_MESSAGE.Set_Token('ACCESS_NAME', p_access_name);
1776 FND_MSG_PUB.Add;
1777 RAISE FND_API.G_EXC_ERROR;
1778 WHEN OTHERS THEN
1779 FND_MESSAGE.Set_Name('FND', 'SQL_PLSQL_ERROR');
1780 FND_MESSAGE.Set_Token('ROUTINE', l_api_name);
1781 FND_MESSAGE.Set_Token('ERRNO', SQLCODE);
1782 FND_MESSAGE.Set_Token('REASON', SQLERRM);
1783 FND_MSG_PUB.Add;
1784
1785 FND_MESSAGE.Set_Name('IBE', 'IBE_DSP_SCT_GET_SCT_ACSS_NAME');
1786 FND_MESSAGE.Set_Token('ACCESS_NAME', p_access_name);
1787 FND_MSG_PUB.Add;
1788 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1789 END;
1790 ELSE
1791 -- neither section_id nor access_name is specified
1792 FND_MESSAGE.Set_Name('IBE', 'IBE_DSP_SCT_NO_ID_OR_ACSS');
1793 FND_MSG_PUB.Add;
1794 RAISE FND_API.G_EXC_ERROR;
1795 END IF;
1796
1797
1798 --
1799 -- 1. Validate the input data
1800 --
1801 Validate_Update
1802 (
1803 p_init_msg_list => FND_API.G_FALSE,
1804 p_validation_level => p_validation_level,
1805 p_section_id => l_section_id,
1806 p_object_version_number => p_object_version_number,
1807 p_access_name => p_access_name,
1808 p_start_date_active => p_start_date_active,
1809 p_end_date_active => p_end_date_active,
1810 p_section_type_code => p_section_type_code,
1811 p_status_code => p_status_code,
1812 p_display_context_id => p_display_context_id,
1813 p_deliverable_id => p_deliverable_id,
1814 p_available_in_all_sites_flag => p_available_in_all_sites_flag,
1815 p_auto_placement_rule => p_auto_placement_rule,
1816 p_order_by_clause => p_order_by_clause,
1817 p_attribute_category => p_attribute_category,
1818 p_attribute1 => p_attribute1,
1819 p_attribute2 => p_attribute2,
1820 p_attribute3 => p_attribute3,
1821 p_attribute4 => p_attribute4,
1822 p_attribute5 => p_attribute5,
1823 p_attribute6 => p_attribute6,
1824 p_attribute7 => p_attribute7,
1825 p_attribute8 => p_attribute8,
1826 p_attribute9 => p_attribute9,
1827 p_attribute10 => p_attribute10,
1828 p_attribute11 => p_attribute11,
1829 p_attribute12 => p_attribute12,
1830 p_attribute13 => p_attribute13,
1831 p_attribute14 => p_attribute14,
1832 p_attribute15 => p_attribute15,
1833 p_display_name => p_display_name,
1834 p_description => p_description,
1835 p_long_description => p_long_description,
1836 p_keywords => p_keywords,
1837 x_return_status => l_return_status,
1838 x_msg_count => l_msg_count,
1839 x_msg_data => l_msg_data
1840 );
1841
1842 IF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
1843 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1844 ELSIF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
1845 FND_MESSAGE.Set_Name('IBE', 'IBE_DSP_SCT_INVALID_CREATE');
1846 FND_MSG_PUB.Add;
1847 RAISE FND_API.G_EXC_ERROR; -- invalid section
1848 END IF;
1849
1850 -- 2. update row with section data into section table
1851 BEGIN
1852 update_row
1853 (
1854 l_section_id,
1855 p_object_version_number,
1856 p_access_name,
1857 p_start_date_active,
1858 p_end_date_active,
1859 p_section_type_code,
1860 p_status_code,
1861 p_display_context_id,
1862 p_deliverable_id,
1863 p_available_in_all_sites_flag,
1864 p_auto_placement_rule,
1865 p_order_by_clause,
1866 p_attribute_category,
1867 p_attribute1,
1868 p_attribute2,
1869 p_attribute3,
1870 p_attribute4,
1871 p_attribute5,
1872 p_attribute6,
1873 p_attribute7,
1874 p_attribute8,
1875 p_attribute9,
1876 p_attribute10,
1877 p_attribute11,
1878 p_attribute12,
1879 p_attribute13,
1880 p_attribute14,
1881 p_attribute15,
1882 p_display_name,
1883 p_description,
1884 p_long_description,
1885 p_keywords,
1886 SYSDATE,
1887 FND_GLOBAL.user_id,
1888 FND_GLOBAL.login_id
1889 );
1890 EXCEPTION
1891 WHEN NO_DATA_FOUND THEN
1892 FND_MESSAGE.Set_Name('IBE', 'IBE_DSP_SCT_UPDATE_FAIL');
1893 FND_MSG_PUB.Add;
1894 RAISE FND_API.G_EXC_ERROR;
1895 WHEN OTHERS THEN
1896 FND_MESSAGE.Set_Name('FND', 'SQL_PLSQL_ERROR');
1897 FND_MESSAGE.Set_Token('ROUTINE', l_api_name);
1898 FND_MESSAGE.Set_Token('ERRNO', SQLCODE);
1899 FND_MESSAGE.Set_Token('REASON', SQLERRM);
1900 FND_MSG_PUB.Add;
1901
1902 FND_MESSAGE.Set_Name('IBE', 'IBE_DSP_SCT_UPDATE_FAIL');
1903 FND_MSG_PUB.Add;
1904 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1905 END;
1906
1907 --
1908 -- End of main API body.
1909
1910 -- Standard check of p_commit.
1911 IF (FND_API.To_Boolean(p_commit)) THEN
1912 COMMIT WORK;
1913 END IF;
1914
1915 -- Standard call to get message count and if count is 1, get message info.
1916 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
1917 p_data => x_msg_data,
1918 p_encoded => 'F');
1919
1920 EXCEPTION
1921
1922 WHEN FND_API.G_EXC_ERROR THEN
1923 ROLLBACK TO UPDATE_SECTION_GRP;
1924 x_return_status := FND_API.G_RET_STS_ERROR;
1925 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
1926 p_data => x_msg_data,
1927 p_encoded => 'F');
1928
1929 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1930 ROLLBACK TO UPDATE_SECTION_GRP;
1931 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1932 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
1933 p_data => x_msg_data,
1934 p_encoded => 'F');
1935
1936 WHEN OTHERS THEN
1937 ROLLBACK TO UPDATE_SECTION_GRP;
1938
1939 FND_MESSAGE.Set_Name('FND', 'SQL_PLSQL_ERROR');
1940 FND_MESSAGE.Set_Token('ROUTINE', l_api_name);
1941 FND_MESSAGE.Set_Token('ERRNO', SQLCODE);
1942 FND_MESSAGE.Set_Token('REASON', SQLERRM);
1943 FND_MSG_PUB.Add;
1944
1945 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1946
1947 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1948 THEN
1949 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
1950 END IF;
1951
1952 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
1953 p_data => x_msg_data,
1954 p_encoded => 'F');
1955
1956 END Update_Section;
1957
1958 PROCEDURE Delete_Section
1959 (
1960 p_api_version IN NUMBER,
1961 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1962 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1963 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1964 p_section_id IN NUMBER := FND_API.G_MISS_NUM,
1965 p_access_name IN VARCHAR2 := FND_API.G_MISS_CHAR,
1966 x_return_status OUT NOCOPY VARCHAR2,
1967 x_msg_count OUT NOCOPY NUMBER,
1968 x_msg_data OUT NOCOPY VARCHAR2
1969 )
1970 IS
1971 l_api_name CONSTANT VARCHAR2(30) := 'Delete_Section';
1972 l_api_version CONSTANT NUMBER := 1.0;
1973
1974 l_section_id NUMBER;
1975 l_mini_site_id NUMBER;
1976
1977 CURSOR c1(l_c_child_section_id IN NUMBER) IS
1978 SELECT mini_site_section_section_id FROM ibe_dsp_msite_sct_sects
1979 WHERE child_section_id = l_c_child_section_id;
1980
1981 CURSOR c2(l_c_section_id IN NUMBER) IS
1982 SELECT section_item_id FROM ibe_dsp_section_items
1983 WHERE section_id = l_c_section_id;
1984
1985 CURSOR c3(l_c_section_id IN NUMBER) IS
1986 SELECT msite_id FROM ibe_msites_b
1987 WHERE msite_root_section_id = l_c_section_id and site_type = 'I';
1988
1989 BEGIN
1990 -- Standard Start of API savepoint
1991 SAVEPOINT DELETE_SECTION_GRP;
1992
1993 -- Standard call to check for call compatibility.
1994 IF NOT FND_API.Compatible_API_Call(l_api_version,
1995 p_api_version,
1996 l_api_name,
1997 G_PKG_NAME)
1998 THEN
1999 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2000 END IF;
2001
2002 -- Initialize message list if p_init_msg_list is set to TRUE.
2003 IF FND_API.to_Boolean(p_init_msg_list) THEN
2004 FND_MSG_PUB.initialize;
2005 END IF;
2006
2007 -- Initialize API return status to success
2008 x_return_status := FND_API.G_RET_STS_SUCCESS;
2009
2010 -- CALL FLOW
2011 -- 1. If section_id specified, delete all references for section id
2012 -- 2. If access_name specified and section_id is not specified, then
2013 -- query for section id and delete all references
2014
2015 -- 1. If section_id specified, delete all references for section id
2016 IF ((p_section_id IS NOT NULL) AND
2017 (p_section_id <> FND_API.G_MISS_NUM))
2018 THEN
2019 l_section_id := p_section_id; -- section_id specified, continue
2020 ELSIF ((p_access_name IS NOT NULL) AND
2021 (p_access_name <> FND_API.G_MISS_CHAR))
2022 THEN
2023 -- 2. If access_name specified and section_id is not specified, then
2024 -- query for section id and delete all references
2025 BEGIN
2026 SELECT section_id INTO l_section_id FROM ibe_dsp_sections_b
2027 WHERE access_name = p_access_name;
2028 EXCEPTION
2029 WHEN NO_DATA_FOUND THEN
2030 FND_MESSAGE.Set_Name('IBE', 'IBE_DSP_SCT_NO_SCT_ACSS_NAME');
2031 FND_MESSAGE.Set_Token('ACCESS_NAME', p_access_name);
2032 FND_MSG_PUB.Add;
2033 RAISE FND_API.G_EXC_ERROR;
2034 WHEN OTHERS THEN
2035 FND_MESSAGE.Set_Name('FND', 'SQL_PLSQL_ERROR');
2036 FND_MESSAGE.Set_Token('ROUTINE', l_api_name);
2037 FND_MESSAGE.Set_Token('ERRNO', SQLCODE);
2038 FND_MESSAGE.Set_Token('REASON', SQLERRM);
2039 FND_MSG_PUB.Add;
2040
2041 FND_MESSAGE.Set_Name('IBE', 'IBE_DSP_SCT_NO_SCT_ACSS_NAME');
2042 FND_MESSAGE.Set_Token('ACCESS_NAME', p_access_name);
2043 FND_MSG_PUB.Add;
2044 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2045 END;
2046 ELSE
2047 -- neither section_id nor access_name is specified, therefore cannot delete
2048 FND_MESSAGE.Set_Name('IBE', 'IBE_DSP_SCT_NO_ID_OR_ACSS');
2049 FND_MSG_PUB.Add;
2050 RAISE FND_API.G_EXC_ERROR;
2051 END IF;
2052
2053 --
2054 -- Delete references from other tables
2055 --
2056
2057 -- Check if this section id is a root section for any mini-site id.
2058 -- If yes, then cannot delete this section
2059 OPEN c3(l_section_id);
2060 FETCH c3 INTO l_mini_site_id;
2061 IF (c3%FOUND) THEN
2062 FND_MESSAGE.Set_Name('IBE', 'IBE_DSP_SCT_MSITE_REF');
2063 FND_MESSAGE.Set_Token('SECTION_ID', l_section_id);
2064 FND_MESSAGE.Set_Token('MINI_SITE_ID', l_mini_site_id);
2065 FND_MSG_PUB.Add;
2066 RAISE FND_API.G_EXC_ERROR;
2067 END IF;
2068
2069 -- delete from ibe_dsp_msite_sct_sects table
2070 FOR r1 in c1(l_section_id) LOOP
2071
2072 IBE_DSP_MSITE_SCT_SECT_PVT.Delete_MSite_Section_Section
2073 (
2074 p_api_version => p_api_version,
2075 p_init_msg_list => FND_API.G_FALSE,
2076 p_commit => FND_API.G_FALSE,
2077 p_validation_level => p_validation_level,
2078 p_mini_site_section_section_id => r1.mini_site_section_section_id,
2079 p_mini_site_id => FND_API.G_MISS_NUM,
2080 p_parent_section_id => FND_API.G_MISS_NUM,
2081 p_child_section_id => FND_API.G_MISS_NUM,
2082 x_return_status => x_return_status,
2083 x_msg_count => x_msg_count,
2084 x_msg_data => x_msg_data
2085 );
2086
2087 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
2088 RAISE FND_API.G_EXC_ERROR;
2089 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
2090 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2091 END IF;
2092
2093 END LOOP;
2094
2095 -- delete for ibe_dsp_section_items table
2096 FOR r2 in c2(l_section_id) LOOP
2097
2098 IBE_DSP_SECTION_ITEM_PVT.Delete_Section_Item
2099 (
2100 p_api_version => p_api_version,
2101 p_init_msg_list => FND_API.G_FALSE,
2102 p_commit => FND_API.G_FALSE,
2103 p_validation_level => p_validation_level,
2104 p_call_from_trigger => FALSE,
2105 p_section_item_id => r2.section_item_id,
2106 p_section_id => FND_API.G_MISS_NUM,
2107 p_inventory_item_id => FND_API.G_MISS_NUM,
2108 p_organization_id => FND_API.G_MISS_NUM,
2109 x_return_status => x_return_status,
2110 x_msg_count => x_msg_count,
2111 x_msg_data => x_msg_data
2112 );
2113
2114 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
2115 RAISE FND_API.G_EXC_ERROR;
2116 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
2117 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2118 END IF;
2119
2120 END LOOP;
2121
2122 -- delete for relation ship tables
2123 IBE_PROD_RELATION_PVT.Section_Deleted
2124 (
2125 p_section_id => l_section_id
2126 );
2127
2128 -- delete for other tables (Templates, Media, etc)
2129 IBE_LOGICALCONTENT_GRP.Delete_Section(l_section_id);
2130
2131 -- delete for ibe_dsp_sections_b and _tl tables
2132 delete_row(l_section_id);
2133
2134 EXCEPTION
2135
2136 WHEN FND_API.G_EXC_ERROR THEN
2137 ROLLBACK TO DELETE_SECTION_GRP;
2138 x_return_status := FND_API.G_RET_STS_ERROR;
2139 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
2140 p_data => x_msg_data,
2141 p_encoded => 'F');
2142
2143 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2144 ROLLBACK TO DELETE_SECTION_GRP;
2145 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2146 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
2147 p_data => x_msg_data,
2148 p_encoded => 'F');
2149
2150 WHEN OTHERS THEN
2151 ROLLBACK TO DELETE_SECTION_GRP;
2152
2153 FND_MESSAGE.Set_Name('FND', 'SQL_PLSQL_ERROR');
2154 FND_MESSAGE.Set_Token('ROUTINE', l_api_name);
2155 FND_MESSAGE.Set_Token('ERRNO', SQLCODE);
2156 FND_MESSAGE.Set_Token('REASON', SQLERRM);
2157 FND_MSG_PUB.Add;
2158
2159 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2160
2161 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
2162 THEN
2163 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
2164 END IF;
2165
2166 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
2167 p_data => x_msg_data,
2168 p_encoded => 'F');
2169
2170 END Delete_Section;
2171
2172 PROCEDURE Update_Dsp_Context_To_Null
2173 (
2174 p_api_version IN NUMBER,
2175 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2176 p_commit IN VARCHAR2 := FND_API.G_FALSE,
2177 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
2178 p_display_context_id IN NUMBER,
2179 x_return_status OUT NOCOPY VARCHAR2,
2180 x_msg_count OUT NOCOPY NUMBER,
2181 x_msg_data OUT NOCOPY VARCHAR2
2182 )
2183 IS
2184 l_api_name CONSTANT VARCHAR2(30) :='Update_Dsp_Context_To_Null';
2185 l_api_version CONSTANT NUMBER := 1.0;
2186 l_msg_count NUMBER;
2187 l_msg_data VARCHAR2(2000);
2188
2189 BEGIN
2190
2191 -- Standard Start of API savepoint
2192 SAVEPOINT UPDATE_DSP_CONTEXT_TO_NULL_GRP;
2193
2194 -- Standard call to check for call compatibility.
2195 IF NOT FND_API.Compatible_API_Call(l_api_version,
2196 p_api_version,
2197 l_api_name,
2198 G_PKG_NAME)
2199 THEN
2200 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2201 END IF;
2202
2203 -- Initialize message list if p_init_msg_list is set to TRUE.
2204 IF FND_API.to_Boolean(p_init_msg_list) THEN
2205 FND_MSG_PUB.initialize;
2206 END IF;
2207
2208 -- Initialize API return status to success
2209 x_return_status := FND_API.G_RET_STS_SUCCESS;
2210
2211 -- API body
2212 -- CALL FLOW :
2213 -- 1. Update all rows with display_context_id set to null and
2214 -- object version number set to +1
2215
2216 -- 1. update all rows
2217 UPDATE ibe_dsp_sections_b
2218 SET display_context_id = NULL,
2219 object_version_number = object_version_number + 1
2220 WHERE display_context_id = p_display_context_id;
2221
2222 --
2223 -- End of main API body.
2224
2225 -- Standard check of p_commit.
2226 IF (FND_API.To_Boolean(p_commit)) THEN
2227 COMMIT WORK;
2228 END IF;
2229
2230 -- Standard call to get message count and if count is 1, get message info.
2231 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
2232 p_data => x_msg_data,
2233 p_encoded => 'F');
2234
2235 EXCEPTION
2236
2237 WHEN FND_API.G_EXC_ERROR THEN
2238 ROLLBACK TO UPDATE_DSP_CONTEXT_TO_NULL_GRP;
2239 x_return_status := FND_API.G_RET_STS_ERROR;
2240 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
2241 p_data => x_msg_data,
2242 p_encoded => 'F');
2243
2244 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2245 ROLLBACK TO UPDATE_DSP_CONTEXT_TO_NULL_GRP;
2246 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2247 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
2248 p_data => x_msg_data,
2249 p_encoded => 'F');
2250
2251 WHEN OTHERS THEN
2252 ROLLBACK TO UPDATE_DSP_CONTEXT_TO_NULL_GRP;
2253
2254 FND_MESSAGE.Set_Name('FND', 'SQL_PLSQL_ERROR');
2255 FND_MESSAGE.Set_Token('ROUTINE', l_api_name);
2256 FND_MESSAGE.Set_Token('ERRNO', SQLCODE);
2257 FND_MESSAGE.Set_Token('REASON', SQLERRM);
2258 FND_MSG_PUB.Add;
2259
2260 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2261
2262 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2263 THEN
2264 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
2265 END IF;
2266
2267 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
2268 p_data => x_msg_data,
2269 p_encoded => 'F');
2270
2271 END Update_Dsp_Context_To_Null;
2272
2273 PROCEDURE Update_Deliverable_To_Null
2274 (
2275 p_api_version IN NUMBER,
2276 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2277 p_commit IN VARCHAR2 := FND_API.G_FALSE,
2278 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
2279 p_deliverable_id IN NUMBER,
2280 x_return_status OUT NOCOPY VARCHAR2,
2281 x_msg_count OUT NOCOPY NUMBER,
2282 x_msg_data OUT NOCOPY VARCHAR2
2283 )
2284 IS
2285 l_api_name CONSTANT VARCHAR2(30) :='Update_Deliverable_To_Null';
2286 l_api_version CONSTANT NUMBER := 1.0;
2287 l_msg_count NUMBER;
2288 l_msg_data VARCHAR2(2000);
2289
2290 BEGIN
2291
2292 -- Standard Start of API savepoint
2293 SAVEPOINT UPDATE_DELIVERABLE_TO_NULL_GRP;
2294
2295 -- Standard call to check for call compatibility.
2296 IF NOT FND_API.Compatible_API_Call(l_api_version,
2297 p_api_version,
2298 l_api_name,
2299 G_PKG_NAME)
2300 THEN
2301 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2302 END IF;
2303
2304 -- Initialize message list if p_init_msg_list is set to TRUE.
2305 IF FND_API.to_Boolean(p_init_msg_list) THEN
2306 FND_MSG_PUB.initialize;
2307 END IF;
2308
2309 -- Initialize API return status to success
2310 x_return_status := FND_API.G_RET_STS_SUCCESS;
2311
2312 -- API body
2313 -- CALL FLOW :
2314 -- 1. Update all rows with deliverable_id set to null and
2315 -- object version number set to +1
2316
2317 -- 1. update all rows
2318 UPDATE ibe_dsp_sections_b
2319 SET deliverable_id = NULL,
2320 object_version_number = object_version_number + 1
2321 WHERE deliverable_id = p_deliverable_id;
2322
2323 --
2324 -- End of main API body.
2325
2326 -- Standard check of p_commit.
2327 IF (FND_API.To_Boolean(p_commit)) THEN
2328 COMMIT WORK;
2329 END IF;
2330
2331 -- Standard call to get message count and if count is 1, get message info.
2332 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
2333 p_data => x_msg_data,
2334 p_encoded => 'F');
2335
2336 EXCEPTION
2337
2338 WHEN FND_API.G_EXC_ERROR THEN
2339 ROLLBACK TO UPDATE_DELIVERABLE_TO_NULL_GRP;
2340 x_return_status := FND_API.G_RET_STS_ERROR;
2341 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
2342 p_data => x_msg_data,
2343 p_encoded => 'F');
2344
2345 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2346 ROLLBACK TO UPDATE_DELIVERABLE_TO_NULL_GRP;
2347 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2348 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
2349 p_data => x_msg_data,
2350 p_encoded => 'F');
2351
2352 WHEN OTHERS THEN
2353 ROLLBACK TO UPDATE_DELIVERABLE_TO_NULL_GRP;
2354
2355 FND_MESSAGE.Set_Name('FND', 'SQL_PLSQL_ERROR');
2356 FND_MESSAGE.Set_Token('ROUTINE', l_api_name);
2357 FND_MESSAGE.Set_Token('ERRNO', SQLCODE);
2358 FND_MESSAGE.Set_Token('REASON', SQLERRM);
2359 FND_MSG_PUB.Add;
2360
2361 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2362
2363 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2364 THEN
2365 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
2366 END IF;
2367
2368 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
2369 p_data => x_msg_data,
2370 p_encoded => 'F');
2371
2372 END Update_Deliverable_To_Null;
2373
2374 --
2375 -- procedure to add the languages to the section table
2376 -- included from generated code
2377 --
2378 procedure ADD_LANGUAGE
2379 is
2380 begin
2381 delete from IBE_DSP_SECTIONS_TL T
2382 where not exists
2383 (select NULL
2384 from IBE_DSP_SECTIONS_B B
2385 where B.SECTION_ID = T.SECTION_ID
2386 );
2387
2388 update IBE_DSP_SECTIONS_TL T set (
2389 DISPLAY_NAME,
2390 DESCRIPTION,
2391 LONG_DESCRIPTION,
2392 KEYWORDS
2393 ) = (select
2394 B.DISPLAY_NAME,
2395 B.DESCRIPTION,
2396 B.LONG_DESCRIPTION,
2397 B.KEYWORDS
2398 from IBE_DSP_SECTIONS_TL B
2399 where B.SECTION_ID = T.SECTION_ID
2400 and B.LANGUAGE = T.SOURCE_LANG)
2401 where (
2402 T.SECTION_ID,
2403 T.LANGUAGE
2404 ) in (select
2405 SUBT.SECTION_ID,
2406 SUBT.LANGUAGE
2407 from IBE_DSP_SECTIONS_TL SUBB, IBE_DSP_SECTIONS_TL SUBT
2408 where SUBB.SECTION_ID = SUBT.SECTION_ID
2409 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
2410 and (SUBB.DISPLAY_NAME <> SUBT.DISPLAY_NAME
2411 or SUBB.DESCRIPTION <> SUBT.DESCRIPTION
2412 or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
2413 or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
2414 or SUBB.LONG_DESCRIPTION <> SUBT.LONG_DESCRIPTION
2415 or (SUBB.LONG_DESCRIPTION is null and SUBT.LONG_DESCRIPTION is not null)
2416 or (SUBB.LONG_DESCRIPTION is not null and SUBT.LONG_DESCRIPTION is null)
2417 or SUBB.KEYWORDS <> SUBT.KEYWORDS
2418 or (SUBB.KEYWORDS is null and SUBT.KEYWORDS is not null)
2419 or (SUBB.KEYWORDS is not null and SUBT.KEYWORDS is null)
2420 ));
2421
2422 insert into IBE_DSP_SECTIONS_TL (
2423 SECTION_ID,
2424 OBJECT_VERSION_NUMBER,
2425 CREATED_BY,
2426 CREATION_DATE,
2427 LAST_UPDATED_BY,
2428 LAST_UPDATE_DATE,
2429 LAST_UPDATE_LOGIN,
2430 DISPLAY_NAME,
2431 DESCRIPTION,
2432 LONG_DESCRIPTION,
2433 KEYWORDS,
2434 LANGUAGE,
2435 SOURCE_LANG
2436 ) select
2437 B.SECTION_ID,
2438 B.OBJECT_VERSION_NUMBER,
2439 B.CREATED_BY,
2440 B.CREATION_DATE,
2441 B.LAST_UPDATED_BY,
2442 B.LAST_UPDATE_DATE,
2443 B.LAST_UPDATE_LOGIN,
2444 B.DISPLAY_NAME,
2445 B.DESCRIPTION,
2446 B.LONG_DESCRIPTION,
2447 B.KEYWORDS,
2448 L.LANGUAGE_CODE,
2449 B.SOURCE_LANG
2450 from IBE_DSP_SECTIONS_TL B, FND_LANGUAGES L
2451 where L.INSTALLED_FLAG in ('I', 'B')
2452 and B.LANGUAGE = userenv('LANG')
2453 and not exists
2454 (select NULL
2455 from IBE_DSP_SECTIONS_TL T
2456 where T.SECTION_ID = B.SECTION_ID
2457 and T.LANGUAGE = L.LANGUAGE_CODE);
2458 end ADD_LANGUAGE;
2459
2460 PROCEDURE translate_row
2461 (
2462 p_section_id IN NUMBER,
2463 p_display_name IN VARCHAR2,
2464 p_description IN VARCHAR2,
2465 p_long_description IN VARCHAR2,
2466 p_keywords IN VARCHAR2,
2467 x_owner IN VARCHAR2,
2468 P_LAST_UPDATE_DATE IN varchar2,
2469 P_CUSTOM_MODE IN Varchar2
2470 )
2471 IS
2472 f_luby number; -- entity owner in file
2473 f_ludate date; -- entity update date in file
2474 db_luby number; -- entity owner in db
2475 db_ludate date; -- entity update date in db
2476
2477 BEGIN
2478 f_luby := fnd_load_util.owner_id(x_owner);
2479 f_ludate := nvl(to_date(P_LAST_UPDATE_DATE, 'YYYY/MM/DD'), sysdate);
2480 select LAST_UPDATED_BY, LAST_UPDATE_DATE
2481 into db_luby, db_ludate
2482 from ibe_dsp_sections_tl
2483 where SECTION_ID = p_section_id
2484 and language=userenv('LANG'); -- bug #5089259
2485
2486 IF (fnd_load_util.upload_test(f_luby, f_ludate, db_luby, db_ludate, P_CUSTOM_MODE)) then
2487 UPDATE ibe_dsp_sections_tl SET
2488 section_id = p_section_id,
2489 display_name = p_display_name,
2490 description = p_description,
2491 long_description = p_long_description,
2492 keywords = p_keywords,
2493 last_update_date = f_ludate,--sysdate,
2494 last_updated_by = f_luby, --decode(X_OWNER, 'SEED', 1, 0),
2495 last_update_login = 0,
2496 source_lang = userenv('LANG')
2497 WHERE userenv('LANG') in (LANGUAGE, SOURCE_LANG) AND
2498 section_id = p_section_id;
2499 END IF;
2500
2501 END translate_row;
2502
2503 PROCEDURE LOAD_SEED_ROW
2504 (
2505 P_SECTION_ID IN NUMBER,
2506 P_DISPLAY_NAME IN VARCHAR2,
2507 P_DESCRIPTION IN VARCHAR2,
2508 P_LONG_DESCRIPTION IN VARCHAR2,
2509 P_KEYWORDS IN VARCHAR2,
2510 P_OWNER IN VARCHAR2,
2511 P_OBJECT_VERSION_NUMBER IN NUMBER := FND_API.G_MISS_NUM,
2512 P_ACCESS_NAME IN VARCHAR2,
2513 P_START_DATE_ACTIVE IN VARCHAR2,--IN DATE,
2514 P_END_DATE_ACTIVE IN VARCHAR2,-- IN DATE,
2515 P_SECTION_TYPE_CODE IN VARCHAR2,
2516 P_STATUS_CODE IN VARCHAR2,
2517 P_DISPLAY_CONTEXT_ID IN NUMBER,
2518 P_DELIVERABLE_ID IN NUMBER,
2519 P_AVAILABLE_IN_ALL_SITES_FLAG IN VARCHAR2,
2520 P_AUTO_PLACEMENT_RULE IN VARCHAR2,
2521 P_ORDER_BY_CLAUSE IN VARCHAR2,
2522 P_ATTRIBUTE_CATEGORY IN VARCHAR2,
2523 P_ATTRIBUTE1 IN VARCHAR2,
2524 P_ATTRIBUTE2 IN VARCHAR2,
2525 P_ATTRIBUTE3 IN VARCHAR2,
2526 P_ATTRIBUTE4 IN VARCHAR2,
2527 P_ATTRIBUTE5 IN VARCHAR2,
2528 P_ATTRIBUTE6 IN VARCHAR2,
2529 P_ATTRIBUTE7 IN VARCHAR2,
2530 P_ATTRIBUTE8 IN VARCHAR2,
2531 P_ATTRIBUTE9 IN VARCHAR2,
2532 P_ATTRIBUTE10 IN VARCHAR2,
2533 P_ATTRIBUTE11 IN VARCHAR2,
2534 P_ATTRIBUTE12 IN VARCHAR2,
2535 P_ATTRIBUTE13 IN VARCHAR2,
2536 P_ATTRIBUTE14 IN VARCHAR2,
2537 P_ATTRIBUTE15 IN VARCHAR2,
2538 P_LAST_UPDATE_DATE IN VARCHAR2,
2539 P_CUSTOM_MODE IN VARCHAR2,
2540 P_UPLOAD_MODE IN VARCHAR2
2541 )
2542 IS
2543 BEGIN
2544 IF (P_UPLOAD_MODE = 'NLS') then
2545 TRANSLATE_ROW(
2546 P_SECTION_ID,
2547 P_DISPLAY_NAME,
2548 P_DESCRIPTION,
2549 P_LONG_DESCRIPTION,
2550 P_KEYWORDS,
2551 p_OWNER,
2552 P_LAST_UPDATE_DATE,
2553 P_CUSTOM_MODE
2554 );
2555 ELSE
2556 LOAD_ROW(
2557 P_OWNER,
2558 P_SECTION_ID,
2559 P_OBJECT_VERSION_NUMBER,
2560 P_ACCESS_NAME,
2561 to_date(P_START_DATE_ACTIVE,'YYYY/MM/DD'),
2562 to_date(P_END_DATE_ACTIVE,'YYYY/MM/DD'),
2563 P_SECTION_TYPE_CODE,
2564 P_STATUS_CODE,
2565 P_DISPLAY_CONTEXT_ID,
2566 P_DELIVERABLE_ID,
2567 P_AVAILABLE_IN_ALL_SITES_FLAG,
2568 P_AUTO_PLACEMENT_RULE,
2569 P_ORDER_BY_CLAUSE,
2570 P_ATTRIBUTE_CATEGORY,
2571 P_ATTRIBUTE1,
2572 P_ATTRIBUTE2,
2573 P_ATTRIBUTE3,
2574 P_ATTRIBUTE4,
2575 P_ATTRIBUTE5,
2576 P_ATTRIBUTE6,
2577 P_ATTRIBUTE7,
2578 P_ATTRIBUTE8,
2579 P_ATTRIBUTE9,
2580 P_ATTRIBUTE10,
2581 P_ATTRIBUTE11,
2582 P_ATTRIBUTE12,
2583 P_ATTRIBUTE13,
2584 P_ATTRIBUTE14,
2585 P_ATTRIBUTE15,
2586 P_DISPLAY_NAME,
2587 P_DESCRIPTION,
2588 P_LONG_DESCRIPTION,
2589 P_KEYWORDS,
2590 P_LAST_UPDATE_DATE,
2591 P_CUSTOM_MODE);
2592
2593 End IF;
2594
2595 END LOAD_SEED_ROW;
2596
2597
2598
2599 END IBE_DSP_SECTION_GRP;