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