[Home] [Help]
PACKAGE BODY: APPS.JTF_DISPLAYCONTEXT_GRP
Source
1 PACKAGE BODY JTF_DisplayContext_GRP AS
2 /* $Header: JTFGCTXB.pls 115.12 2004/07/09 18:49:30 applrt ship $ */
3
4 -----------------------------------------------------------------
5 -- NOTES
6 -- 1. Raises an exception if the api_version is not valid
7 -- 2. If the context_id is passed in the record, the existing display
8 -- context is updated.
9 -- 3. If the context_id is set to null, a new display context record
10 -- is inserted
11 -- 4. If the context_id is passed for update operation, and the object
12 -- version number does not match , an exception is raised
13 --- 5. If deliverable_id is passed, then the deliverable_id should have
14 -- the DELIVERABLE_TYPE_CODE (JTF_AMV_ITEMS_B) same as context_type
15 -- Valid context_types are TEMPLATE OR MEDIA
16 -- 6. Access name is unique for a context_type
17 -- 7. Raises an exception if the access name is null
18 ---------------------------------------------------------------------
19 PROCEDURE save_display_context(
20 p_api_version IN NUMBER,
21 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
22 p_commit IN VARCHAR2 := FND_API.g_false,
23 x_return_status OUT VARCHAR2,
24 x_msg_count OUT NUMBER,
25 x_msg_data OUT VARCHAR2,
26 p_display_context_rec IN OUT DISPLAY_CONTEXT_REC_TYPE
27 )
28 IS
29 l_api_name CONSTANT VARCHAR2(30) := 'save_display_context';
30 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
31 l_operation_type VARCHAR2(10) := 'INSERT';
32 l_return_status VARCHAR2(1);
33 l_index NUMBER ;
34 l_context_id NUMBER;
35 l_deliverable_id NUMBER := null;
36
37 l_access_name VARCHAR2(40);
38
39 CURSOR context_id_seq IS
40 SELECT jtf_dsp_context_b_s1.NEXTVAL
41 FROM DUAL;
42
43 BEGIN
44
45 --------------------- initialize -----------------------
46 SAVEPOINT save_display_context;
47
48 IF NOT FND_API.compatible_api_call(
49 g_api_version,
50 p_api_version,
51 l_api_name,
52 g_pkg_name
53 ) THEN
54 RAISE FND_API.g_exc_unexpected_error;
55 END IF;
56
57
58 IF FND_API.to_boolean(p_init_msg_list) THEN
59 FND_MSG_PUB.initialize;
60 END IF;
61
62
63 x_return_status := FND_API.G_RET_STS_SUCCESS;
64
65 -- Check if the context type is valid
66 IF jtf_dspmgrvalidation_grp.check_valid_context_type(p_display_context_rec.context_type) = false
67 then
68 raise FND_API.g_exc_error;
69 end if;
70
71
72 --- Check if the context_id exists if not null
73 IF p_display_context_rec.context_id IS NOT NULL
74 THEN
75 ---dbms_output.put_line('Context id is passed ' );
76
77 if jtf_dspmgrvalidation_grp.check_context_exists(p_display_context_rec.context_id,
78 p_display_context_rec.context_type,
79 p_display_context_rec.Object_version_Number)
80 = false then
81 raise FND_API.g_exc_error;
82 end if;
83
84 l_operation_type:='UPDATE';
85
86 ---dbms_output.put_line('Operation is an update ' );
87
88 END IF;
89
90 ---dbms_output.put_line('Access name cannot be null' || p_display_context_rec.access_name );
91
92 --- Check if the access name of the context exists if not null
93 l_access_name := trim(p_display_context_rec.access_name);
94
95 IF l_access_name is not null
96 then
97
98
99 if not jtf_dspmgrvalidation_grp.check_context_accessname(l_access_name,
100 p_display_context_rec.context_type,
101 p_display_context_rec.context_id)
102 then
103 ---dbms_output.put_line('Access name already exists' );
104 raise FND_API.g_exc_error;
105 end if;
106 ---dbms_output.put_line('Passed unique Access name ' );
107 else
108 ---dbms_output.put_line('Access name cannot be null' );
109 RAISE jtf_dspmgrvalidation_grp.context_accname_req_exception;
110 end if;
111
112
113 --- Check if the deliverable id exists if deliverable is not null, else ignore.
114 IF p_display_context_rec.default_deliverable_id is not null and
115 p_display_context_rec.default_deliverable_id <> FND_API.g_miss_num
116 then
117 IF jtf_dspmgrvalidation_grp.check_deliverable_type_exists(p_display_context_rec.Default_deliverable_id ,
118 p_display_context_rec.context_type)
119 then
120 l_deliverable_id := p_display_context_rec.Default_deliverable_id;
121 else
122 raise FND_API.g_exc_error;
123 END IF;
124 end if;
125
126
127 ---dbms_output.put_line('PASSED DELIVERABLE_ ID TEST');
128
129 IF l_operation_type = 'INSERT'
130 THEN
131 ---dbms_output.put_line('INSERT OPERATIOn ');
132
133 ---dbms_output.put_line('PASSED CONTEXT_TYPE TEST');
134
135 OPEN context_id_seq;
136 FETCH context_id_seq INTO l_context_id;
137 CLOSE context_id_seq;
138
139 ---dbms_output.put_line('Operation is an insert ' || l_context_id || '---' || FND_GLOBAL.user_id);
140
141 END IF;
142
143 IF l_operation_type = 'INSERT'
144 THEN
145 INSERT INTO JTF_DSP_CONTEXT_B (
146 CONTEXT_ID,
147 OBJECT_VERSION_NUMBER,
148 LAST_UPDATE_DATE,
149 LAST_UPDATED_BY,
150 CREATION_DATE,
151 CREATED_BY,
152 LAST_UPDATE_LOGIN,
153 ACCESS_NAME,
154 CONTEXT_TYPE_CODE,
155 ITEM_ID
156 )
157 VALUES (
158 l_context_id,
159 1,
160 SYSDATE,
161 FND_GLOBAL.user_id,
162 SYSDATE,
163 FND_GLOBAL.user_id,
164 FND_GLOBAL.user_id,
165 p_display_context_rec.access_name,
166 p_display_context_rec.context_type,
167 l_deliverable_id);
168
169 --- Insert into the TL table
170 insert into JTF_DSP_CONTEXT_TL (
171 CONTEXT_ID,
172 LAST_UPDATE_DATE,
173 LAST_UPDATED_BY,
174 CREATION_DATE,
175 CREATED_BY,
176 LAST_UPDATE_LOGIN,
177 OBJECT_VERSION_NUMBER,
178 NAME,
179 DESCRIPTION,
180 LANGUAGE,
181 SOURCE_LANG
182 ) select
183 l_context_id,
184 sysdate,
185 FND_GLOBAL.user_id,
186 sysdate,
187 FND_GLOBAL.user_id,
188 FND_GLOBAL.user_id,
189 1,
190 p_display_context_rec.display_name,
191 p_display_context_rec.description,
192 L.LANGUAGE_CODE,
193 userenv('LANG')
194 from FND_LANGUAGES L
195 where L.INSTALLED_FLAG in ('I', 'B')
196 and not exists
197 (select NULL
198 from JTF_DSP_CONTEXT_TL T
199 where T.CONTEXT_ID =l_context_id
200 and T.LANGUAGE = L.LANGUAGE_CODE);
201 p_display_context_rec.context_id := l_context_id;
202 p_display_context_rec.object_version_number := 1;
203
204 ELSIF l_operation_type = 'UPDATE'
205 THEN
206
207 UPDATE JTF_DSP_CONTEXT_B SET
208 LAST_UPDATE_DATE = SYSDATE,
209 LAST_UPDATED_BY = FND_GLOBAL.user_id,
210 LAST_UPDATE_LOGIN= FND_GLOBAL.user_id,
211 ACCESS_NAME = p_display_context_rec.access_name,
212 CONTEXT_TYPE_CODE = p_display_context_rec.context_type,
213 ITEM_ID = l_deliverable_id ,
214 OBJECT_VERSION_NUMBER = p_display_context_rec.object_version_number + 1
215 WHERE
216 CONTEXT_ID = p_display_context_rec.context_id and
217 OBJECT_VERSION_NUMBER = p_display_context_rec.object_version_number;
218 --- Update the TL table
219
220 update JTF_DSP_CONTEXT_TL set
221 NAME = decode( p_display_context_rec.display_name,
222 FND_API.G_MISS_CHAR, NAME, p_display_context_rec.display_name),
223 DESCRIPTION = decode( p_display_context_rec.description,
224 FND_API.G_MISS_CHAR, DESCRIPTION, p_display_context_rec.description),
225 LAST_UPDATE_DATE = SYSDATE,
226 LAST_UPDATED_BY = FND_GLOBAL.user_id,
227 LAST_UPDATE_LOGIN = FND_GLOBAL.user_id,
228 OBJECT_VERSION_NUMBER= p_display_context_rec.object_version_number +1 ,
229 SOURCE_LANG = userenv('LANG')
230 where context_id = p_display_context_rec.context_id
231 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
232
233 END IF;
234
235 ---dbms_output.put_line('Operation is successful ' );
236 --- Check if the caller requested to commit ,
237 --- If p_commit set to true, commit the transaction
238 IF FND_API.to_boolean(p_commit) THEN
239 COMMIT;
240 END IF;
241
242 x_return_status := FND_API.G_RET_STS_SUCCESS;
243
244 FND_MSG_PUB.count_and_get(
245 p_encoded => FND_API.g_false,
246 p_count => x_msg_count,
247 p_data => x_msg_data
248 );
249
250 EXCEPTION
251
252 WHEN FND_API.g_exc_error THEN
253 ROLLBACK TO save_display_context;
254 x_return_status := FND_API.g_ret_sts_error;
255 FND_MSG_PUB.count_and_get(
256 p_encoded => FND_API.g_false,
257 p_count => x_msg_count,
258 p_data => x_msg_data
259 );
260
261 WHEN FND_API.g_exc_unexpected_error THEN
262 ROLLBACK TO save_display_context;
263 x_return_status := FND_API.g_ret_sts_unexp_error ;
264 FND_MSG_PUB.count_and_get(
265 p_encoded => FND_API.g_false,
266 p_count => x_msg_count,
267 p_data => x_msg_data
268 );
269
270 WHEN jtf_dspmgrvalidation_grp.context_accname_req_exception THEN
271 ROLLBACK TO save_display_context;
272 x_return_status := FND_API.g_ret_sts_error;
273 FND_MESSAGE.set_name('JTF','JTF_DSP_CONTEXT_ACCNAME_REQ');
274 FND_MSG_PUB.ADD;
275 FND_MSG_PUB.count_and_get(
276 p_encoded => FND_API.g_false,
277 p_count => x_msg_count,
278 p_data => x_msg_data
279 );
280
281 WHEN OTHERS THEN
282 ROLLBACK TO save_display_context;
283 x_return_status := FND_API.g_ret_sts_unexp_error ;
284 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error)
285 THEN
286 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
287 END IF;
288 FND_MSG_PUB.count_and_get(
289 p_encoded => FND_API.g_false,
290 p_count => x_msg_count,
291 p_data => x_msg_data
292 );
293
294 END save_display_context;
295
296
297 ---------------------------------------------------------------
298 -- NOTES
299 -- 1. Raise exception if the p_api_version doesn't match.
300 -- 2. Raises exception if the context_id does not exist
301 -- 3. The context_id passed should have context_type and the correct
302 -- object_version_number to be deleted.Else an exception is raised
303 -- 4. All corresponding entries for the context_id are also deleted
304 -- from TL tables
305 --------------------------------------------------------------------
306 PROCEDURE delete_display_context(
307 p_api_version IN NUMBER,
308 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
309 p_commit IN VARCHAR2 := FND_API.g_false,
310 x_return_status OUT VARCHAR2,
311 x_msg_count OUT NUMBER,
312 x_msg_data OUT VARCHAR2,
313 p_display_context_rec IN OUT DISPLAY_CONTEXT_REC_TYPE
314 )
315 IS
316 l_api_name CONSTANT VARCHAR2(30) := 'delete_display_context';
317 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
318 l_index NUMBER;
319 l_context_id NUMBER;
320 BEGIN
321
322 --------------------- initialize -----------------------
323 SAVEPOINT delete_display_context;
324
325 IF NOT FND_API.compatible_api_call(
326 g_api_version,
327 p_api_version,
328 l_api_name,
329 g_pkg_name
330 ) THEN
331 RAISE FND_API.g_exc_unexpected_error;
332 END IF;
333
334 IF FND_API.to_boolean(p_init_msg_list) THEN
335 FND_MSG_PUB.initialize;
336 END IF;
337
338 x_return_status := FND_API.G_RET_STS_SUCCESS;
339
340 --- Check if the context_id exists
341 IF p_display_context_rec.context_id <> FND_API.g_miss_num
342 and p_display_context_rec.context_id is not null
343 THEN
344
345 -- Check if the context_id is valid
346 if jtf_dspmgrvalidation_grp.check_context_exists(p_display_context_rec.context_id,
347 p_display_context_rec.context_type,
348 p_display_context_rec.Object_version_Number)
349 = false then
350 raise FND_API.g_exc_error;
351 end if;
352
353 DELETE FROM JTF_DSP_CONTEXT_B WHERE
354 CONTEXT_ID = p_display_context_rec.context_id AND
355 CONTEXT_TYPE_CODE = p_display_context_rec.context_type AND
356 OBJECT_VERSION_NUMBER= p_display_context_rec.object_version_number;
357
358 p_display_context_rec.context_id := null;
359
360 DELETE FROM JTF_DSP_CONTEXT_TL WHERE
361 CONTEXT_ID = p_display_context_rec.context_id;
362
363 --- Delete all entries from jtf_dsp_obj_lgl_ctnt which use the context_id
364 JTF_LogicalContent_GRP.delete_context(p_display_context_rec.context_id);
365
366 JTF_DSP_SECTION_GRP.Update_Dsp_Context_To_Null(
367 p_api_version,
368 FND_API.g_false,
369 FND_API.g_false,
370 FND_API.G_VALID_LEVEL_FULL,
371 p_display_context_rec.context_id,
372 x_return_status,
373 x_msg_count,
374 x_msg_data
375 );
376
377 else
378 FND_MESSAGE.set_name('JTF','JTF_DSP_CONTEXT_ID_REQ');
379 FND_MSG_PUB.ADD;
380 raise FND_API.g_exc_error;
381 END IF;
382
383
384 x_return_status := FND_API.G_RET_STS_SUCCESS;
385
386 --- Check if the caller requested to commit ,
387 --- If p_commit set to true, commit the transaction
388 IF FND_API.to_boolean(p_commit) THEN
389 COMMIT;
390 END IF;
391
392 FND_MSG_PUB.count_and_get(
393 p_encoded => FND_API.g_false,
394 p_count => x_msg_count,
395 p_data => x_msg_data
396 );
397
398
399 EXCEPTION
400
401 WHEN FND_API.g_exc_error THEN
402 ROLLBACK TO delete_display_context;
403 x_return_status := FND_API.g_ret_sts_error;
404 FND_MSG_PUB.count_and_get(
405 p_encoded => FND_API.g_false,
406 p_count => x_msg_count,
407 p_data => x_msg_data
408 );
409
410 WHEN FND_API.g_exc_unexpected_error THEN
411 ROLLBACK TO delete_display_context;
412 x_return_status := FND_API.g_ret_sts_unexp_error ;
413 FND_MSG_PUB.count_and_get(
414 p_encoded => FND_API.g_false,
415 p_count => x_msg_count,
416 p_data => x_msg_data
417 );
418
419
420 WHEN OTHERS THEN
421 ROLLBACK TO delete_display_context;
422 x_return_status := FND_API.g_ret_sts_unexp_error ;
423 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error)
424 THEN
425 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
426 END IF;
427 FND_MSG_PUB.count_and_get(
428 p_encoded => FND_API.g_false,
429 p_count => x_msg_count,
430 p_data => x_msg_data
431 );
432
433
434 END DELETE_DISPLAY_CONTEXT;
435
436 ---------------------------------------------------------------
437 -- NOTES
438 -- 1. Raise exception if the p_api_version doesn't match.
439 -- 2. If context_delete is FND_API.g_true, then operation is
440 -- delete and delete_display_context is called with appropriate
441 -- parameters
445 -- is inserted and context_delete is FND_API.g_false
442 -- 3. If the context_id is passed in the record, the existing display
443 -- context is updated.
444 -- 4. If the context_id is set to null, a new display context record
446 -- 5. If the operation is an insert/update, save_display_context with
447 -- appropriate parameters is called
448 -- 6. Raises exception if the context_id does not exist for an update
449 -- operation
450 -- 7. If the context_id is passed for update operation, and the object
451 -- version number does not match , the update operation fails and
452 -- an exception is raised
453 -- 8. All corresponding entries for the context_id are also inserted
454 -- updated,deleted from TL tables depending on the operation (2,3)
455 --------------------------------------------------------------------
456 PROCEDURE save_delete_display_context(
457 p_api_version IN NUMBER,
458 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
459 p_commit IN VARCHAR2 := FND_API.g_false,
460 x_return_status OUT VARCHAR2,
461 x_msg_count OUT NUMBER,
462 x_msg_data OUT VARCHAR2,
463 p_display_context_tbl IN OUT DISPLAY_CONTEXT_TBL_TYPE
464 ) IS
465 l_api_name CONSTANT VARCHAR2(30) := 'save_delete_display_context';
466 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
467 l_return_status VARCHAR2(1);
468 l_index NUMBER;
469 l_context_id NUMBER;
470 l_msg_count NUMBER;
471 l_msg_data VARCHAR2(80);
472
473 BEGIN
474 --------------------- initialize -----------------------
475 SAVEPOINT save_delete_display_context;
476
477 IF NOT FND_API.compatible_api_call(
478 g_api_version,
479 p_api_version,
480 l_api_name,
481 g_pkg_name
482 ) THEN
483 RAISE FND_API.g_exc_unexpected_error;
484 END IF;
485
486 IF FND_API.to_boolean(p_init_msg_list) THEN
487 FND_MSG_PUB.initialize;
488 END IF;
489
490 x_return_status := FND_API.G_RET_STS_SUCCESS;
491
492 FOR l_index IN 1..p_display_context_tbl.COUNT
493 LOOP
494 ---
495 IF p_display_context_tbl(l_index).context_delete = FND_API.g_true
496 then
497 delete_display_context(
498 p_api_version,
499 FND_API.g_false,
500 FND_API.g_false,
501 l_return_status,
502 l_msg_count,
503 l_msg_data,
504 p_display_context_tbl(l_index));
505
506 ---dbms_output.put_line('Return from delete_display_context:' || l_return_status);
507
508
509 ELSIF p_display_context_tbl(l_index).context_delete = FND_API.g_false
510 then
511 save_display_context(
512 p_api_version,
513 FND_API.g_false,
514 FND_API.g_false,
515 l_return_status,
516 l_msg_count,
517 l_msg_data,
518 p_display_context_tbl(l_index));
519 ---dbms_output.put_line('Return from save_display_context:' || l_return_status);
520 END IF;
521
522 if l_return_status <> FND_API.G_RET_STS_SUCCESS
523 then
524 x_return_status := l_return_status;
525 end if;
526
527 END LOOP;
528
529 --- Check if the caller requested to commit ,
530 --- If p_commit set to true, commit the transaction
531 IF FND_API.to_boolean(p_commit) THEN
532 COMMIT;
533 END IF;
534
535 FND_MSG_PUB.count_and_get(
536 p_encoded => FND_API.g_false,
537 p_count => x_msg_count,
538 p_data => x_msg_data
539 );
540 EXCEPTION
541
542 WHEN FND_API.g_exc_unexpected_error THEN
543 ROLLBACK TO save_delete_display_context;
544 x_return_status := FND_API.g_ret_sts_unexp_error ;
545 FND_MSG_PUB.count_and_get(
546 p_encoded => FND_API.g_false,
547 p_count => x_msg_count,
548 p_data => x_msg_data
549 );
550
551
552 WHEN OTHERS THEN
553 ROLLBACK TO save_delete_display_context;
554 x_return_status := FND_API.g_ret_sts_unexp_error ;
555
556 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error)
557 THEN
558 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
559 END IF;
560 FND_MSG_PUB.count_and_get(
561 p_encoded => FND_API.g_false,
562 p_count => x_msg_count,
563 p_data => x_msg_data
564 );
565 END save_delete_display_context;
566
567
568 ---------------------------------------------------------------
569 -- NOTES
570 -- 1. Raise exception if there is a database error
571 -- 2. Sets the item_id in JTF_DSP_CONTEXT_B to null for
572 -- the deliverable id passed
573 -- 3. No api level exceptions are raised
574 --------------------------------------------------------------------
575 PROCEDURE delete_deliverable(
576 p_deliverable_id IN NUMBER
577 )
578 IS
579 BEGIN
580
581 SAVEPOINT delete_deliverable;
582
583 if p_deliverable_id <> FND_API.g_miss_num or p_deliverable_id is not null
584 then
588 end if;
585 -- Set the deliverable id to null for any display context
586 update JTF_DSP_CONTEXT_B set item_id = null where
587 item_id = p_deliverable_id;
589
590
591 EXCEPTION
592 WHEN OTHERS THEN
593 ROLLBACK TO delete_deliverable;
594
595 END delete_deliverable;
596
597 /* ------ Begin Insert_row ------------- */
598
599 procedure INSERT_ROW (
600 X_ROWID in out VARCHAR2,
601 X_CONTEXT_ID in NUMBER,
602 X_SECURITY_GROUP_ID in NUMBER,
603 X_OBJECT_VERSION_NUMBER in NUMBER,
604 X_ACCESS_NAME in VARCHAR2,
605 X_CONTEXT_TYPE_CODE in VARCHAR2,
606 X_ITEM_ID in NUMBER,
607 X_NAME in VARCHAR2,
608 X_DESCRIPTION in VARCHAR2,
609 X_CREATION_DATE in DATE,
610 X_CREATED_BY in NUMBER,
611 X_LAST_UPDATE_DATE in DATE,
612 X_LAST_UPDATED_BY in NUMBER,
613 X_LAST_UPDATE_LOGIN in NUMBER) IS
614
615 cursor C is select ROWID from JTF_DSP_CONTEXT_B
616 where CONTEXT_ID = X_CONTEXT_ID
617 ;
618 begin
619 insert into JTF_DSP_CONTEXT_B (
620 SECURITY_GROUP_ID,
621 CONTEXT_ID,
622 OBJECT_VERSION_NUMBER,
623 ACCESS_NAME,
624 CONTEXT_TYPE_CODE,
625 ITEM_ID,
626 CREATION_DATE,
627 CREATED_BY,
628 LAST_UPDATE_DATE,
629 LAST_UPDATED_BY,
630 LAST_UPDATE_LOGIN
631 ) values (
632 X_SECURITY_GROUP_ID,
633 X_CONTEXT_ID,
634 X_OBJECT_VERSION_NUMBER,
635 X_ACCESS_NAME,
636 X_CONTEXT_TYPE_CODE,
637 X_ITEM_ID,
638 X_CREATION_DATE,
639 X_CREATED_BY,
640 X_LAST_UPDATE_DATE,
641 X_LAST_UPDATED_BY,
642 X_LAST_UPDATE_LOGIN
643 );
644
645 insert into JTF_DSP_CONTEXT_TL (
646 SECURITY_GROUP_ID,
647 CONTEXT_ID,
648 OBJECT_VERSION_NUMBER,
649 CREATED_BY,
650 CREATION_DATE,
651 LAST_UPDATED_BY,
652 LAST_UPDATE_DATE,
653 LAST_UPDATE_LOGIN,
654 NAME,
655 DESCRIPTION,
656 LANGUAGE,
657 SOURCE_LANG
658 ) select
659 X_SECURITY_GROUP_ID,
660 X_CONTEXT_ID,
661 X_OBJECT_VERSION_NUMBER,
662 X_CREATED_BY,
663 X_CREATION_DATE,
664 X_LAST_UPDATED_BY,
665 X_LAST_UPDATE_DATE,
666 X_LAST_UPDATE_LOGIN,
667 X_NAME,
668 X_DESCRIPTION,
669 L.LANGUAGE_CODE,
670 userenv('LANG')
671 from FND_LANGUAGES L
672 where L.INSTALLED_FLAG in ('I', 'B')
673 and not exists
674 (select NULL
675 from JTF_DSP_CONTEXT_TL T
676 where T.CONTEXT_ID = X_CONTEXT_ID
677 and T.LANGUAGE = L.LANGUAGE_CODE);
678
679 open c;
680 fetch c into X_ROWID;
681 if (c%notfound) then
682 close c;
683 raise no_data_found;
684 end if;
685 close c;
686
687 end INSERT_ROW;
688
689 /* ---- End INSERT_ROW Procedure ----- */
690
691 /* ---- Start LOCK_ROW Procedue ------ */
692
693 procedure LOCK_ROW (
694 X_CONTEXT_ID in NUMBER,
695 X_SECURITY_GROUP_ID in NUMBER,
696 X_OBJECT_VERSION_NUMBER in NUMBER,
697 X_ACCESS_NAME in VARCHAR2,
698 X_CONTEXT_TYPE_CODE in VARCHAR2,
699 X_ITEM_ID in NUMBER,
700 X_NAME in VARCHAR2,
701 X_DESCRIPTION in VARCHAR2
702 ) IS
703
704 cursor c is select
705 SECURITY_GROUP_ID,
706 OBJECT_VERSION_NUMBER,
707 ACCESS_NAME,
708 CONTEXT_TYPE_CODE,
709 ITEM_ID
710 from JTF_DSP_CONTEXT_B
711 where CONTEXT_ID = X_CONTEXT_ID
712 for update of CONTEXT_ID nowait;
713 recinfo c%rowtype;
714
715 cursor c1 is select
716 NAME,
717 DESCRIPTION,
718 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
719 from JTF_DSP_CONTEXT_TL
720 where CONTEXT_ID = X_CONTEXT_ID
721 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
722 for update of CONTEXT_ID nowait;
723 begin
724 open c;
725 fetch c into recinfo;
726 if (c%notfound) then
727 close c;
728 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
729 app_exception.raise_exception;
730 end if;
731 close c;
732 if ( ((recinfo.SECURITY_GROUP_ID = X_SECURITY_GROUP_ID)
733 OR ((recinfo.SECURITY_GROUP_ID is null) AND (X_SECURITY_GROUP_ID is null)))
734 AND (recinfo.OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER)
735 AND (recinfo.ACCESS_NAME = X_ACCESS_NAME)
736 AND (recinfo.CONTEXT_TYPE_CODE = X_CONTEXT_TYPE_CODE)
737 AND ((recinfo.ITEM_ID = X_ITEM_ID)
738 OR ((recinfo.ITEM_ID is null) AND (X_ITEM_ID is null)))
739 ) then
740 null;
741 else
742 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
743 app_exception.raise_exception;
744 end if;
745
746 for tlinfo in c1 loop
747 if (tlinfo.BASELANG = 'Y') then
748 if ( ((tlinfo.NAME = X_NAME)
749 OR ((tlinfo.NAME is null) AND (X_NAME is null)))
750 AND ((tlinfo.DESCRIPTION = X_DESCRIPTION)
754 else
751 OR ((tlinfo.DESCRIPTION is null) AND (X_DESCRIPTION is null)))
752 ) then
753 null;
755 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
756 app_exception.raise_exception;
757 end if;
758 end if;
759 end loop;
760 return;
761 end LOCK_ROW;
762
763 /* ------- End LOCK_ROW Procedure ---------- */
764
765 /* ------- start UPDATE_ROW Procedure ------- */
766
767 procedure UPDATE_ROW (
768 X_CONTEXT_ID in NUMBER,
769 X_SECURITY_GROUP_ID in NUMBER,
770 X_OBJECT_VERSION_NUMBER in NUMBER,
771 X_ACCESS_NAME in VARCHAR2,
772 X_CONTEXT_TYPE_CODE in VARCHAR2,
773 X_ITEM_ID in NUMBER,
774 X_NAME in VARCHAR2,
775 X_DESCRIPTION in VARCHAR2,
776 X_LAST_UPDATE_DATE in DATE,
777 X_LAST_UPDATED_BY in NUMBER,
778 X_LAST_UPDATE_LOGIN in NUMBER
779 ) IS
780
781 begin
782 update JTF_DSP_CONTEXT_B set
783 SECURITY_GROUP_ID = X_SECURITY_GROUP_ID,
784 OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
785 ACCESS_NAME = X_ACCESS_NAME,
786 CONTEXT_TYPE_CODE = X_CONTEXT_TYPE_CODE,
787 ITEM_ID = X_ITEM_ID,
788 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
789 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
790 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
791 where CONTEXT_ID = X_CONTEXT_ID;
792
793 if (sql%notfound) then
794 raise no_data_found;
795 end if;
796
797 update JTF_DSP_CONTEXT_TL set
798 NAME = X_NAME,
799 DESCRIPTION = X_DESCRIPTION,
800 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
801 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
802 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
803 SOURCE_LANG = userenv('LANG')
804 where CONTEXT_ID = X_CONTEXT_ID
805 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
806
807 if (sql%notfound) then
808 raise no_data_found;
809 end if;
810 end UPDATE_ROW;
811
812 /* ---- End UPDATE_ROW Procedure ---------- */
813
814 /* ---- Start DELETE_ROW Procedure --------- */
815
816 procedure DELETE_ROW (
817 X_CONTEXT_ID in NUMBER
818 ) is
819 begin
820 delete from JTF_DSP_CONTEXT_TL
821 where CONTEXT_ID = X_CONTEXT_ID;
822
823 if (sql%notfound) then
824 raise no_data_found;
825 end if;
826
827 delete from JTF_DSP_CONTEXT_B
828 where CONTEXT_ID = X_CONTEXT_ID;
829
830 if (sql%notfound) then
831 raise no_data_found;
832 end if;
833 end DELETE_ROW;
834
835 /* --- End DELETE_ROW Procedure ---- */
836
837 /* -- Start TRANSLATE_ROW Procedure ---- */
838
839 procedure TRANSLATE_ROW (
840 X_CONTEXT_ID in NUMBER,
841 X_OWNER in VARCHAR2,
842 X_NAME in VARCHAR2,
843 X_DESCRIPTION in VARCHAR2 ) is
844
845 begin
846
847 update jtf_dsp_context_tl
848 set language = USERENV('LANG'),
849 source_lang = USERENV('LANG'),
850 name = X_NAME,
851 description = X_DESCRIPTION,
852 last_updated_by = decode(X_OWNER,'SEED',1,0),
853 last_update_date = sysdate,
854 last_update_login=0
855 Where userenv('LANG') in (language,source_lang)
856 and context_id = X_CONTEXT_ID;
857
858 end TRANSLATE_ROW;
859
860 /* ---- End TRANSLATE_ROW Procedure ---- */
861
862 /* ----- Start LOAD_ROW Procedure ------- */
863
864 procedure LOAD_ROW (
865 X_CONTEXT_ID in NUMBER,
866 X_SECURITY_GROUP_ID in NUMBER,
867 X_OWNER in VARCHAR2,
868 X_OBJECT_VERSION_NUMBER in NUMBER,
869 X_ACCESS_NAME in VARCHAR2,
870 X_CONTEXT_TYPE_CODE in VARCHAR2,
871 X_ITEM_ID in NUMBER,
872 X_NAME in VARCHAR2,
873 X_DESCRIPTION in VARCHAR2) IS
874
875 Owner_id NUMBER := 0;
876 Row_Id VARCHAR2(64);
877
878 Begin
879
880 If X_OWNER = 'SEED' Then
881 Owner_id := 1;
882 End If;
883
884 UPDATE_ROW (
885 X_CONTEXT_ID => X_CONTEXT_ID,
886 X_SECURITY_GROUP_ID => X_SECURITY_GROUP_ID,
887 X_OBJECT_VERSION_NUMBER => X_OBJECT_VERSION_NUMBER,
888 X_ACCESS_NAME => X_ACCESS_NAME,
889 X_CONTEXT_TYPE_CODE => X_CONTEXT_TYPE_CODE,
890 X_ITEM_ID => X_ITEM_ID,
891 X_NAME => X_NAME,
892 X_DESCRIPTION => X_DESCRIPTION,
893 X_LAST_UPDATE_DATE => sysdate,
894 X_LAST_UPDATED_BY => Owner_id,
895 X_LAST_UPDATE_LOGIN => 0);
896 Exception
897 When NO_DATA_FOUND Then
898
899 INSERT_ROW(
900 X_ROWID => Row_id,
901 X_CONTEXT_ID => X_CONTEXT_ID,
902 X_SECURITY_GROUP_ID => X_SECURITY_GROUP_ID,
903 X_OBJECT_VERSION_NUMBER => X_OBJECT_VERSION_NUMBER,
904 X_ACCESS_NAME => X_ACCESS_NAME,
905 X_CONTEXT_TYPE_CODE => X_CONTEXT_TYPE_CODE,
906 X_ITEM_ID => X_ITEM_ID,
907 X_NAME => X_NAME,
908 X_DESCRIPTION => X_DESCRIPTION,
909 X_CREATION_DATE => sysdate,
910 X_CREATED_BY => Owner_id,
911 X_LAST_UPDATE_DATE => sysdate,
912 X_LAST_UPDATED_BY => Owner_id,
913 X_LAST_UPDATE_LOGIN => 0);
914 End LOAD_ROW;
915
916 /* ----- End LOAD_ROW_PROCEDURE ----- */
917
918 procedure ADD_LANGUAGE
919 is
920 begin
921 delete from JTF_DSP_CONTEXT_TL T
922 where not exists
923 (select NULL
924 from JTF_DSP_CONTEXT_B B
925 where B.CONTEXT_ID = T.CONTEXT_ID
926 );
927
928 update JTF_DSP_CONTEXT_TL T set (
929 NAME,
930 DESCRIPTION
931 ) = (select
932 B.NAME,
933 B.DESCRIPTION
934 from JTF_DSP_CONTEXT_TL B
935 where B.CONTEXT_ID = T.CONTEXT_ID
936 and B.LANGUAGE = T.SOURCE_LANG)
937 where (
938 T.CONTEXT_ID,
939 T.LANGUAGE
940 ) in (select
941 SUBT.CONTEXT_ID,
942 SUBT.LANGUAGE
943 from JTF_DSP_CONTEXT_TL SUBB, JTF_DSP_CONTEXT_TL SUBT
944 where SUBB.CONTEXT_ID = SUBT.CONTEXT_ID
945 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
946 and (SUBB.NAME <> SUBT.NAME
947 or (SUBB.NAME is null and SUBT.NAME is not null)
948 or (SUBB.NAME is not null and SUBT.NAME is null)
949 or SUBB.DESCRIPTION <> SUBT.DESCRIPTION
950 or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
951 or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
952 ));
953
954 insert into JTF_DSP_CONTEXT_TL (
955 CONTEXT_ID,
956 OBJECT_VERSION_NUMBER,
957 CREATED_BY,
958 CREATION_DATE,
959 LAST_UPDATED_BY,
960 LAST_UPDATE_DATE,
961 LAST_UPDATE_LOGIN,
962 NAME,
963 DESCRIPTION,
964 LANGUAGE,
965 SOURCE_LANG
966 ) select
967 B.CONTEXT_ID,
968 B.OBJECT_VERSION_NUMBER,
969 B.CREATED_BY,
970 B.CREATION_DATE,
971 B.LAST_UPDATED_BY,
972 B.LAST_UPDATE_DATE,
973 B.LAST_UPDATE_LOGIN,
974 B.NAME,
975 B.DESCRIPTION,
976 L.LANGUAGE_CODE,
977 B.SOURCE_LANG
978 from JTF_DSP_CONTEXT_TL B, FND_LANGUAGES L
979 where L.INSTALLED_FLAG in ('I', 'B')
980 and B.LANGUAGE = userenv('LANG')
981 and not exists
982 (select NULL
983 from JTF_DSP_CONTEXT_TL T
984 where T.CONTEXT_ID = B.CONTEXT_ID
985 and T.LANGUAGE = L.LANGUAGE_CODE);
986
987 end ADD_LANGUAGE;
988
989
990 END JTF_DisplayContext_GRP;
991