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