1 package body FND_MENU_ENTRIES_PKG as
2 /* $Header: AFMNENTB.pls 120.3 2006/10/16 13:22:48 stadepal ship $ */
3
4 C_PKG_NAME CONSTANT VARCHAR2(30) := 'FND_FUNCTION';
5 C_LOG_HEAD CONSTANT VARCHAR2(30) := 'fnd.plsql.FND_FUNCTION.';
6
7 procedure INSERT_ROW (
8 X_ROWID in out nocopy VARCHAR2,
9 X_MENU_ID in NUMBER,
10 X_ENTRY_SEQUENCE in NUMBER,
11 X_SUB_MENU_ID in NUMBER,
12 X_FUNCTION_ID in NUMBER,
13 X_GRANT_FLAG in VARCHAR2,
14 X_PROMPT in VARCHAR2,
15 X_DESCRIPTION in VARCHAR2,
16 X_CREATION_DATE in DATE,
17 X_CREATED_BY in NUMBER,
18 X_LAST_UPDATE_DATE in DATE,
19 X_LAST_UPDATED_BY in NUMBER,
20 X_LAST_UPDATE_LOGIN in NUMBER
21 ) is
22 cursor C is select ROWID from FND_MENU_ENTRIES
23 where MENU_ID = X_MENU_ID
24 and ENTRY_SEQUENCE = X_ENTRY_SEQUENCE
25 ;
26 L_GRANT_FLAG VARCHAR2(1);
27 begin
28 /* for bug 2216556 default the grant_flag to maintain compatibility with*/
29 /* old loader data files that don't have GRANT_FLAG */
30 if (X_GRANT_FLAG is NULL) then
31 L_GRANT_FLAG := 'Y';
32 else
33 L_GRANT_FLAG := substrb(X_GRANT_FLAG,1,1);
34 end if;
35
36 insert into FND_MENU_ENTRIES (
37 MENU_ID,
38 ENTRY_SEQUENCE,
39 SUB_MENU_ID,
40 FUNCTION_ID,
41 GRANT_FLAG,
42 CREATION_DATE,
43 CREATED_BY,
44 LAST_UPDATE_DATE,
45 LAST_UPDATED_BY,
46 LAST_UPDATE_LOGIN
47 ) values (
48 X_MENU_ID,
49 X_ENTRY_SEQUENCE,
50 X_SUB_MENU_ID,
51 X_FUNCTION_ID,
52 L_GRANT_FLAG,
53 X_CREATION_DATE,
54 X_CREATED_BY,
55 X_LAST_UPDATE_DATE,
56 X_LAST_UPDATED_BY,
57 X_LAST_UPDATE_LOGIN
58 );
59
60 -- Added for Function Security Cache Invalidation Project.
61 fnd_function_security_cache.insert_menu_entry(X_MENU_ID, X_SUB_MENU_ID, X_FUNCTION_ID);
62
63 insert into FND_MENU_ENTRIES_TL (
64 MENU_ID,
65 ENTRY_SEQUENCE,
66 LAST_UPDATE_DATE,
67 LAST_UPDATED_BY,
68 LAST_UPDATE_LOGIN,
69 CREATION_DATE,
70 CREATED_BY,
71 PROMPT,
72 DESCRIPTION,
73 LANGUAGE,
74 SOURCE_LANG
75 ) select
76 X_MENU_ID,
77 X_ENTRY_SEQUENCE,
78 X_LAST_UPDATE_DATE,
79 X_LAST_UPDATED_BY,
80 X_LAST_UPDATE_LOGIN,
81 X_CREATION_DATE,
82 X_CREATED_BY,
83 decode(x_PROMPT,
84 fnd_load_util.null_value, null,
85 null, x_prompt,
86 X_PROMPT),
87 X_DESCRIPTION,
88 L.LANGUAGE_CODE,
89 userenv('LANG')
90 from FND_LANGUAGES L
91 where L.INSTALLED_FLAG in ('I', 'B')
92 and not exists
93 (select NULL
94 from FND_MENU_ENTRIES_TL T
95 where T.MENU_ID = X_MENU_ID
96 and T.ENTRY_SEQUENCE = X_ENTRY_SEQUENCE
97 and T.LANGUAGE = L.LANGUAGE_CODE);
98
99 open c;
100 fetch c into X_ROWID;
101 if (c%notfound) then
102 close c;
103 raise no_data_found;
104 end if;
105 close c;
106
107 end INSERT_ROW;
108
109 procedure LOCK_ROW (
110 X_MENU_ID in NUMBER,
111 X_ENTRY_SEQUENCE in NUMBER,
112 X_SUB_MENU_ID in NUMBER,
113 X_FUNCTION_ID in NUMBER,
114 X_GRANT_FLAG in VARCHAR2,
115 X_PROMPT in VARCHAR2,
116 X_DESCRIPTION in VARCHAR2
117 ) is
118 L_GRANT_FLAG VARCHAR2(1);
119
120 cursor c is select
121 SUB_MENU_ID,
122 FUNCTION_ID,
123 GRANT_FLAG
124 from FND_MENU_ENTRIES
125 where MENU_ID = X_MENU_ID
126 and ENTRY_SEQUENCE = X_ENTRY_SEQUENCE
127 for update of MENU_ID nowait;
128 recinfo c%rowtype;
129
130 cursor c1 is select
131 PROMPT,
132 DESCRIPTION
133 from FND_MENU_ENTRIES_TL
134 where MENU_ID = X_MENU_ID
135 and ENTRY_SEQUENCE = X_ENTRY_SEQUENCE
136 and LANGUAGE = userenv('LANG')
137 for update of MENU_ID nowait;
138 tlinfo c1%rowtype;
139
140 begin
141 /* for bug 2216556 default the grant_flag to maintain compatibility with*/
142 /* old loader data files that don't have GRANT_FLAG */
143 if (X_GRANT_FLAG is NULL) then
144 L_GRANT_FLAG := 'Y';
145 else
146 L_GRANT_FLAG := substrb(X_GRANT_FLAG,1,1);
147 end if;
148
149 open c;
150 fetch c into recinfo;
151 if (c%notfound) then
152 close c;
153 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
154 app_exception.raise_exception;
155 end if;
156 close c;
157 if ( ((recinfo.SUB_MENU_ID = X_SUB_MENU_ID)
158 OR ((recinfo.SUB_MENU_ID is null) AND (X_SUB_MENU_ID is null)))
159 AND ((recinfo.FUNCTION_ID = X_FUNCTION_ID)
160 OR ((recinfo.FUNCTION_ID is null) AND (X_FUNCTION_ID is null)))
161 AND ((recinfo.GRANT_FLAG = L_GRANT_FLAG)
162 OR ((recinfo.GRANT_FLAG is null) AND (L_GRANT_FLAG is null)))
163 ) then
164 null;
165 else
166 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
167 app_exception.raise_exception;
168 end if;
169
170 open c1;
171 fetch c1 into tlinfo;
172 if (c1%notfound) then
173 close c1;
174 return;
175 end if;
176 close c1;
177
178 if ( ((tlinfo.PROMPT = X_PROMPT)
179 OR ((tlinfo.PROMPT is null) AND (X_PROMPT is null)))
180 AND ((tlinfo.DESCRIPTION = X_DESCRIPTION)
181 OR ((tlinfo.DESCRIPTION is null) AND (X_DESCRIPTION is null)))
182 ) then
183 null;
184 else
185 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
186 app_exception.raise_exception;
187 end if;
188 return;
189 end LOCK_ROW;
190
191 procedure UPDATE_ROW (
192 X_MENU_ID in NUMBER,
193 X_ENTRY_SEQUENCE in NUMBER,
194 X_SUB_MENU_ID in NUMBER,
195 X_FUNCTION_ID in NUMBER,
196 X_GRANT_FLAG in VARCHAR2,
197 X_PROMPT in VARCHAR2,
198 X_DESCRIPTION in VARCHAR2,
199 X_LAST_UPDATE_DATE in DATE,
200 X_LAST_UPDATED_BY in NUMBER,
201 X_LAST_UPDATE_LOGIN in NUMBER
202 ) is
203 L_GRANT_FLAG VARCHAR2(1);
204 L_SUB_MENU_ID NUMBER;
205 L_FUNCTION_ID NUMBER;
206 begin
207 /* for bug 2216556 default the grant_flag to maintain compatibility with*/
208 /* old loader data files that don't have GRANT_FLAG */
209 if (X_GRANT_FLAG is NULL) then
210 L_GRANT_FLAG := 'Y';
211 else
212 L_GRANT_FLAG := substrb(X_GRANT_FLAG,1,1);
213 end if;
214
215 -- Added for Function Security Cache Invalidation Project
216 begin
217 -- Acquire sub_menu_id using menu_id and entry_sequence.
218 select sub_menu_id into L_SUB_MENU_ID
219 from fnd_menu_entries
220 where menu_id = X_MENU_ID
221 and entry_sequence = X_ENTRY_SEQUENCE;
222
223 exception
224 when no_data_found then
225 L_SUB_MENU_ID := null;
226 return;
227 end;
228
229 -- Added for Function Security Cache Invalidation Project
230 begin
231 -- Acquire function_id using menu_id and entry_sequence.
232 select function_id into L_FUNCTION_ID
233 from fnd_menu_entries
234 where menu_id = X_MENU_ID
235 and entry_sequence = X_ENTRY_SEQUENCE;
236
237 exception
238 when no_data_found then
239 L_FUNCTION_ID := null;
240 return;
241 end;
242
243 update FND_MENU_ENTRIES set
244 SUB_MENU_ID = X_SUB_MENU_ID,
245 FUNCTION_ID = X_FUNCTION_ID,
246 GRANT_FLAG = L_GRANT_FLAG,
247 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
248 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
249 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
250 where MENU_ID = X_MENU_ID
251 and ENTRY_SEQUENCE = X_ENTRY_SEQUENCE;
252
253 if (sql%notfound) then
254 raise no_data_found;
255 else
256 -- This means that a menu entry was updated.
257 -- Added for Function Security Cache Invalidation Project
258 fnd_function_security_cache.update_menu_entry(X_MENU_ID, L_SUB_MENU_ID, L_FUNCTION_ID);
259 fnd_function_security_cache.update_menu_entry(X_MENU_ID, X_SUB_MENU_ID, X_FUNCTION_ID);
260 end if;
261
262 update FND_MENU_ENTRIES_TL
263 set prompt = X_PROMPT,
264 DESCRIPTION = X_DESCRIPTION,
265 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
266 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
267 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
268 SOURCE_LANG = userenv('LANG')
269 where MENU_ID = X_MENU_ID
270 and ENTRY_SEQUENCE = X_ENTRY_SEQUENCE
271 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
272
273 if (sql%notfound) then
274 raise no_data_found;
275 end if;
276 end UPDATE_ROW;
277
278 procedure BUMP_ROW(
279 X_USER_ID in NUMBER,
280 X_SHIFT_VALUE in NUMBER,
281 X_ENTRY_SEQUENCE in NUMBER,
282 X_MENU_ID in NUMBER
283 )is
284
285 l_sub_menu_id number;
286 l_function_id number;
287
288 begin
289 -- Bump tl table
290 -- Bug 5579233. Commented WHO col's update during bumping.
291 -- This is becoz of the changes in fnd_load_util.upload_test() api in R12
292 -- which is now considering only LUD but not LUB to return TRUE/FALSE.
293 -- Complete details can be found in bug#5579233
294 update fnd_menu_entries_tl
295 set entry_sequence = entry_sequence + X_SHIFT_VALUE
296 --last_update_date = sysdate,
297 --last_updated_by = 1,
298 --last_update_login = 0
299 where menu_id = X_MENU_ID
300 and entry_sequence = X_ENTRY_SEQUENCE;
301
302 begin
303 -- Added for Function Security Cache Invalidation Project
304 -- Acquire sub_menu_id using menu_id and entry_sequence.
305 select sub_menu_id into l_sub_menu_id
306 from fnd_menu_entries
307 where menu_id = X_MENU_ID
308 and entry_sequence = X_ENTRY_SEQUENCE;
309
310 exception
311 when no_data_found then
312 l_sub_menu_id := null;
313 return;
314 end;
315
316 begin
317 -- Added for Function Security Cache Invalidation Project
318 -- Acquire function_id using menu_id and entry_sequence.
319 select function_id into l_function_id
320 from fnd_menu_entries
321 where menu_id = X_MENU_ID
322 and entry_sequence = X_ENTRY_SEQUENCE;
323
324 exception
325 when no_data_found then
326 l_function_id := null;
327 return;
328 end;
329
330 -- Bump base table
331 -- Bug 5579233. Commented WHO col's update during bumping.
332 -- This is becoz of the changes in fnd_load_util.upload_test() api in R12
333 -- which is now considering only LUD but not LUB to return TRUE/FALSE.
334 -- Complete details can be found in bug#5579233
335 update fnd_menu_entries
336 set entry_sequence = entry_sequence + X_SHIFT_VALUE
337 --last_update_date = sysdate,
338 --last_updated_by = 1,
339 --last_update_login = 0
340 where menu_id = X_MENU_ID
341 and entry_sequence = X_ENTRY_SEQUENCE;
342
343 fnd_function_security_cache.update_menu_entry(X_MENU_ID, l_sub_menu_id, l_function_id);
344
345 end BUMP_ROW;
346
347 /* Overloaded version below */
348 procedure LOAD_ROW (
349 X_MODE in VARCHAR2,
350 X_ENT_SEQUENCE VARCHAR2,
351 X_MENU_NAME in VARCHAR2,
352 X_SUB_MENU_NAME in VARCHAR2,
353 X_FUNCTION_NAME in VARCHAR2,
354 X_GRANT_FLAG in VARCHAR2,
355 X_PROMPT in VARCHAR2,
356 X_DESCRIPTION in VARCHAR2,
357 X_OWNER in VARCHAR2
358 ) is
359 begin
360 fnd_menu_entries_pkg.LOAD_ROW (
361 X_MODE => X_MODE,
362 X_ENT_SEQUENCE => X_ENT_SEQUENCE,
363 X_MENU_NAME => X_MENU_NAME,
364 X_SUB_MENU_NAME => X_SUB_MENU_NAME,
365 X_FUNCTION_NAME => X_FUNCTION_NAME,
366 X_GRANT_FLAG => X_GRANT_FLAG,
367 X_PROMPT => X_PROMPT,
368 X_DESCRIPTION => X_DESCRIPTION,
369 X_OWNER => X_OWNER,
370 X_CUSTOM_MODE => null,
371 X_LAST_UPDATE_DATE => null
372 );
373 end LOAD_ROW;
374
375 /* Overloaded version above */
376 procedure LOAD_ROW (
377 X_MODE in VARCHAR2,
378 X_ENT_SEQUENCE VARCHAR2,
379 X_MENU_NAME in VARCHAR2,
380 X_SUB_MENU_NAME in VARCHAR2,
381 X_FUNCTION_NAME in VARCHAR2,
382 X_GRANT_FLAG in VARCHAR2,
383 X_PROMPT in VARCHAR2,
384 X_DESCRIPTION in VARCHAR2,
385 X_OWNER in VARCHAR2,
386 X_CUSTOM_MODE in VARCHAR2,
387 X_LAST_UPDATE_DATE in VARCHAR2
388 ) is
389 row_id varchar2(64);
390 sub_mnu_id number;
391 mnu_id number;
392 fun_id number;
393 eseq number;
394 eseqmatch varchar2(1);
395 shiftseq number;
396 X_ENTRY_SEQUENCE number;
397 v_mode varchar2(20);
398 f_luby number; -- entity owner in file
399 f_ludate date; -- entity update date in file
400 db_luby number; -- entity owner in db
401 db_ludate date; -- entity update date in db
402 L_GRANT_FLAG VARCHAR2(1);
403 l_sub_menu_name varchar2(4000); -- bug2438503
404 l_function_name varchar2(4000); -- bug2438503
405 l_sub_menu_id number; -- Function Security Cache Invalidation
406 l_function_id number; -- Function Security Cache Invalidation
407
408 CURSOR c_mnu_entry IS
409 SELECT sub_menu_id, function_id
410 FROM fnd_menu_entries E1
411 WHERE E1.MENU_ID = mnu_id
412 and exists (select NULL
413 from FND_MENU_ENTRIES E2
414 where E1.MENU_ID = E2.MENU_ID
415 and NVL(E1.SUB_MENU_ID, -1) = NVL(E2.SUB_MENU_ID, -1)
416 and NVL(E1.FUNCTION_ID, -1) = NVL(E2.FUNCTION_ID, -1)
417 and E1.ENTRY_SEQUENCE > E2.ENTRY_SEQUENCE);
418
419 begin
420 /* for bug 2216556 default the grant_flag to maintain compatibility with*/
421 /* old loader data files that don't have GRANT_FLAG */
422 if (X_GRANT_FLAG is NULL) then
423 L_GRANT_FLAG := 'Y';
424 else
425 L_GRANT_FLAG := substrb(X_GRANT_FLAG,1,1);
426 end if;
427
428 if (X_MODE = 'REPLACE' and X_CUSTOM_MODE = 'FORCE') then
429 v_mode := 'REPLACE_OVERWRITE';
430 elsif (X_MODE = 'MERGE' and X_CUSTOM_MODE = 'FORCE') then
431 v_mode := 'MERGE_OVERWRITE';
432 elsif (X_MODE = 'MERGE' and X_CUSTOM_MODE <> 'FORCE') then
433 v_mode := 'MERGE_NOOVERWRITE';
434 else
435 v_mode := 'MERGE_NOOVERWRITE';
436 end if;
437
438 X_ENTRY_SEQUENCE := to_number(X_ENT_SEQUENCE);
439
440 select decode(X_SUB_MENU_NAME,
441 fnd_load_util.null_value, null,
442 null, X_SUB_MENU_NAME,
443 X_SUB_MENU_NAME) into l_sub_menu_name from dual;
444
445 sub_mnu_id := NULL;
446
447 if (l_sub_menu_name is not null) then
448 begin
449 select menu_id into sub_mnu_id
450 from fnd_menus
451 where menu_name = X_SUB_MENU_NAME;
452 exception
453 when no_data_found then
454 /* The sub menu doesn't yet exist so create a dummy menu*/
455 /* to serve as a temporary placeholder. This solves bug */
456 /* 2225482 about uploading menus whose children hadn't */
457 /* yet been uploaded. This dummy menu will end up getting */
458 /* updated with the real menu information later on during */
459 /* the load when the real menu data gets uploaded. */
460 fnd_menus_pkg.LOAD_ROW(
461 x_menu_name => X_SUB_MENU_NAME,
462 x_menu_type => NULL,
463 x_user_menu_name => X_SUB_MENU_NAME,
464 x_description => NULL,
465 x_owner => X_OWNER,
466 x_custom_mode => X_CUSTOM_MODE,
467 x_last_update_date => X_LAST_UPDATE_DATE);
468 begin
469 select menu_id into sub_mnu_id
470 from fnd_menus
471 where menu_name = X_SUB_MENU_NAME;
472 exception /* This should never happen since we have already loaded*/
473 when no_data_found then
474 fnd_message.set_name('FND', 'SQL_NO_DATA_FOUND');
475 fnd_message.set_token('TABLE', 'FND_MENUS');
476 fnd_message.set_token('COLUMN', 'MENU_NAME');
477 fnd_message.set_token('VALUE', x_sub_menu_name);
478 app_exception.raise_exception;
479 return;
480 end;
481 end;
482 else
483 sub_mnu_id := null;
484 end if;
485
486 select decode(X_FUNCTION_NAME,
487 fnd_load_util.null_value, null,
488 null, X_FUNCTION_NAME,
489 X_FUNCTION_NAME) into l_function_name from dual;
490
491 fun_id := NULL;
492 if (l_function_name is not null) then
493 begin
494 select function_id into fun_id
495 from fnd_form_functions
496 where function_name = X_FUNCTION_NAME;
497 exception
498 when no_data_found then
499 fnd_message.set_name('FND', 'SQL_NO_DATA_FOUND');
500 fnd_message.set_token('TABLE', 'FND_FORM_FUNCTIONS');
501 fnd_message.set_token('COLUMN', 'FUNCTION_NAME');
502 fnd_message.set_token('VALUE', x_function_name);
503 app_exception.raise_exception;
504 return;
505 end;
506 else fun_id := null;
507 end if;
508
509 mnu_id := NULL;
510 begin
511 -- FOR UPDATE is added, to make the upload of entries for the same menu
512 -- from diff ldt files sequential. This is for bug 3657426.
513 select menu_id into mnu_id
514 from fnd_menus
515 where menu_name = X_MENU_NAME
516 for update;
517 exception
518 when no_data_found then
519 fnd_message.set_name('FND', 'SQL_NO_DATA_FOUND');
520 fnd_message.set_token('TABLE', 'FND_MENUS');
521 fnd_message.set_token('COLUMN', 'MENU_NAME');
522 fnd_message.set_token('VALUE', x_menu_name);
523 app_exception.raise_exception;
524 return;
525 end;
526
527 -- Translate owner to file_last_updated_by
528 f_luby := fnd_load_util.owner_id(x_owner);
529
530 -- Translate char last_update_date to date
531 f_ludate := nvl(to_date(x_last_update_date, 'YYYY/MM/DD'), sysdate);
532
533 -- Caculate max sequence for bumping purpose
534 select nvl(max(entry_sequence), 0) + 1
535 into shiftseq
536 from fnd_menu_entries
537 where menu_id = mnu_id;
538
539 -- Delete orphaned rows from the TL table so they don't cause conflicts.
540 -- There shouldn't ever be any, but sometimes the best laid plans...
541 delete from fnd_menu_entries_tl
542 where menu_id = mnu_id
543 and entry_sequence >= shiftseq;
544
545 if (v_mode = 'REPLACE_OVERWRITE') then
546 -- All entries had been pre-deleted in the menu level.
547 -- So, all we have to do is insert.
548 fnd_menu_entries_pkg.bump_row(f_luby, shiftseq, X_ENTRY_SEQUENCE, mnu_id);
549
550 fnd_menu_entries_pkg.insert_row(
551 X_ROWID => row_id,
552 X_MENU_ID => mnu_id,
553 X_ENTRY_SEQUENCE => X_ENTRY_SEQUENCE,
554 X_SUB_MENU_ID => sub_mnu_id,
555 X_FUNCTION_ID => fun_id,
556 X_GRANT_FLAG => L_GRANT_FLAG,
557 X_PROMPT => X_PROMPT,
558 X_DESCRIPTION => X_DESCRIPTION,
559 X_CREATION_DATE => f_ludate,
560 X_CREATED_BY => f_luby,
561 X_LAST_UPDATE_DATE => f_ludate,
562 X_LAST_UPDATED_BY => f_luby,
563 X_LAST_UPDATE_LOGIN => 0);
564
565 return;
566 end if;
567
568 -- Predelete any duplicate entries on this menu to avoid any
569 -- problems later. Theoretically duplicates are not allowed, this
570 -- is to fix problems with existing bad data in databases.
571 delete from FND_MENU_ENTRIES_TL T
572 where T.MENU_ID = mnu_id
573 and exists (select NULL
574 from FND_MENU_ENTRIES E1, FND_MENU_ENTRIES E2
575 where T.MENU_ID = E1.MENU_ID
576 and T.ENTRY_SEQUENCE = E1.ENTRY_SEQUENCE
577 and E1.MENU_ID = E2.MENU_ID
578 and NVL(E1.SUB_MENU_ID, -1) = NVL(E2.SUB_MENU_ID, -1)
579 and NVL(E1.FUNCTION_ID, -1) = NVL(E2.FUNCTION_ID, -1)
580 and E1.ENTRY_SEQUENCE > E2.ENTRY_SEQUENCE);
581
582 -- Since this delete statement may affect more than 1 record, a cursor has been created to
583 -- determine the records for deletion.
584 delete from FND_MENU_ENTRIES E1
585 where E1.MENU_ID = mnu_id
586 and exists (select NULL
587 from FND_MENU_ENTRIES E2
588 where E1.MENU_ID = E2.MENU_ID
589 and NVL(E1.SUB_MENU_ID, -1) = NVL(E2.SUB_MENU_ID, -1)
590 and NVL(E1.FUNCTION_ID, -1) = NVL(E2.FUNCTION_ID, -1)
591 and E1.ENTRY_SEQUENCE > E2.ENTRY_SEQUENCE);
592
593 -- Added for Function Security Cache Invalidation Project.
594 -- Seems that I need make sure that each menu entry deleted is taken into account.
595 -- This loop uses the cursor c_mnu_entry defined.
596 for mentry in c_mnu_entry loop
597 fnd_function_security_cache.delete_menu_entry(mnu_id,
598 mentry.sub_menu_id, mentry.function_id);
599 end loop;
600
601 begin
602 -- Select this entry
603 select decode(e.entry_sequence, X_ENTRY_SEQUENCE, 'Y', 'N') seqmatch,
604 e.entry_sequence, e.last_updated_by, e.last_update_date
605 into eseqmatch, eseq, db_luby, db_ludate
606 from fnd_menu_entries e, fnd_menu_entries_tl t
607 where e.menu_id = mnu_id
608 and nvl(e.sub_menu_id, -1) = nvl(sub_mnu_id, -1)
609 and nvl(e.function_id, -1) = nvl(fun_id, -1)
610 and e.menu_id = t.menu_id
611 and e.entry_sequence = t.entry_sequence
612 and userenv('LANG') = t.language;
613
614
615 if ((v_mode = 'MERGE_OVERWRITE') or
616 (fnd_load_util.upload_test(f_luby, f_ludate, db_luby,
617 db_ludate, X_CUSTOM_MODE))) then
618
619 if (eseqmatch = 'N') then
620 -- If row is found, but position mismatches,
621 -- or if row is not found, then we are either going to
622 -- update a row to a position which might conflict,
623 -- or we are going to create a row with a possible
624 -- position conflict. To avoid this, any existing
625 -- rows with the same sequence value must be moved
626
627 fnd_menu_entries_pkg.bump_row(f_luby,shiftseq,X_ENTRY_SEQUENCE,mnu_id);
628
629 -- Update sequence in tl
630 update fnd_menu_entries_tl
631 set entry_sequence = X_ENTRY_SEQUENCE,
632 last_update_date = f_ludate,
633 last_updated_by = f_luby,
634 last_update_login = 0
635 where menu_id = mnu_id
636 and entry_sequence = eseq;
637
638 -- Added for Function Security Cache Invalidation Project
639 begin
640 -- Acquire sub_menu_id using menu_id and entry_sequence.
641 select sub_menu_id into l_sub_menu_id
642 from fnd_menu_entries
643 where menu_id = mnu_id
644 and entry_sequence = eseq;
645
646 exception
647 when no_data_found then
648 l_sub_menu_id := null;
649 return;
650 end;
651
652 -- Added for Function Security Cache Invalidation Project
653 begin
654 -- Acquire function_id using menu_id and entry_sequence.
655 select function_id into l_function_id
656 from fnd_menu_entries
657 where menu_id = mnu_id
658 and entry_sequence = eseq;
659
660 exception
661 when no_data_found then
662 l_function_id := null;
663 return;
664 end;
665
666 -- Update sequence in base
667 update fnd_menu_entries
668 set entry_sequence = X_ENTRY_SEQUENCE,
669 last_update_date = f_ludate,
670 last_updated_by = f_luby,
671 last_update_login = 0
672 where menu_id = mnu_id
673 and entry_sequence = eseq;
674
675 fnd_function_security_cache.update_menu_entry(mnu_id, l_sub_menu_id, l_function_id);
676
677 end if;
678
679 -- entry found. and sequence has been taken care of if different.
680 -- Check other columns.
681
682
683 -- Added for Function Security Cache Invalidation Project
684 begin
685 -- Acquire sub_menu_id using menu_id and entry_sequence.
686 select sub_menu_id into l_sub_menu_id
687 from fnd_menu_entries
688 where menu_id = mnu_id
689 and entry_sequence = X_ENTRY_SEQUENCE;
690
691 exception
692 when no_data_found then
693 l_sub_menu_id := null;
694 return;
695 end;
696
697 -- Added for Function Security Cache Invalidation Project
698 begin
699 -- Acquire function_id using menu_id and entry_sequence.
700 select function_id into l_function_id
701 from fnd_menu_entries
702 where menu_id = mnu_id
703 and entry_sequence = X_ENTRY_SEQUENCE;
704
705 exception
706 when no_data_found then
707 l_function_id := null;
708 return;
709 end;
710
711 /* Bug 3227451 - Removed grant flag change check.
712 The last_update_date of the base table needs to be updated
713 when the upload test passes even if the base table grant flag
714 is not updated */
715
716 update fnd_menu_entries
717 set grant_flag = L_GRANT_FLAG,
718 last_update_date = f_ludate,
719 last_update_login = 0,
720 last_updated_by = f_luby
721 where entry_sequence = X_ENTRY_SEQUENCE
722 and menu_id = mnu_id;
723
724 fnd_function_security_cache.update_menu_entry(mnu_id, l_sub_menu_id, l_function_id);
725
726 -- Bug2410699 - Modified condition to ensure that
727 -- an update occurs when the PROMPT or DESCRIPTION
728 -- from the database has a NULL value. Also no
729 -- update occurs if the PROMPT or DESCRIPTION in the
730 -- data file has NULL value. Update will occur when
731 -- the LDT file has the *NULL* constant defined.
732
733 /* Bug 3227451 - Removed prompt and description change check.
734 The last_update_date of the tl table needs to be updated
735 when the upload test passes even if neither prompt nor
736 description have changed. */
737 update fnd_menu_entries_tl
738 set prompt = decode(X_PROMPT,
739 fnd_load_util.null_value, null,
740 null, prompt,
741 X_PROMPT),
742 description = X_DESCRIPTION,
743 last_update_date = f_ludate,
744 last_update_login = 0,
745 last_updated_by = f_luby
746 where entry_sequence = X_ENTRY_SEQUENCE
747 and menu_id = mnu_id
748 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
749
750 /* Bug 3227451 - Removed update to base table version info.
751 This is no longer needed since we are now updating both base and
752 tl tables if either one is updated. */
753 end if;
754 exception
755 when no_data_found then
756 -- Both MERGE_OVERWRITE and MERGE_NO_OVERWRITE mode
757 -- create new one in base and tl
758 fnd_menu_entries_pkg.bump_row(f_luby,shiftseq, X_ENTRY_SEQUENCE, mnu_id);
759
760 fnd_menu_entries_pkg.insert_row(
761 X_ROWID => row_id,
762 X_MENU_ID => mnu_id,
763 X_ENTRY_SEQUENCE => X_ENTRY_SEQUENCE,
764 X_SUB_MENU_ID => sub_mnu_id,
765 X_FUNCTION_ID => fun_id,
766 X_GRANT_FLAG => L_GRANT_FLAG,
767 X_PROMPT => X_PROMPT,
768 X_DESCRIPTION => X_DESCRIPTION,
769 X_CREATION_DATE => f_ludate,
770 X_CREATED_BY => f_luby,
771 X_LAST_UPDATE_DATE => f_ludate,
772 X_LAST_UPDATED_BY => f_luby,
773 X_LAST_UPDATE_LOGIN => 0);
774 end;
775 -- Delete unreferenced entries, i.e. any entries which
776 -- are greater than the linear count or which have fractional values.
777 -- Do NOT delete unreferenced entries if running in insert-only mode.
778 if (v_mode <> 'INSERT') then
779 -- delete them
780 null;
781 end if;
782
783 end LOAD_ROW;
784
785
786 procedure DELETE_ROW (
787 X_MENU_ID in NUMBER,
788 X_ENTRY_SEQUENCE in NUMBER
789 ) is
790
791 l_sub_menu_id number;
792 l_function_id number;
793
794 begin
795
796 -- Added for Function Security Cache Invalidation Project
797 begin
798 -- Acquire sub_menu_id using menu_id and entry_sequence.
799 select sub_menu_id into l_sub_menu_id
800 from fnd_menu_entries
801 where menu_id = X_MENU_ID
802 and entry_sequence = X_ENTRY_SEQUENCE;
803
804 exception
805 when no_data_found then
806 l_sub_menu_id := null;
807 return;
808 end;
809
810 -- Added for Function Security Cache Invalidation Project
811 begin
812 -- Acquire function_id using menu_id and entry_sequence.
813 select function_id into l_function_id
814 from fnd_menu_entries
815 where menu_id = X_MENU_ID
816 and entry_sequence = X_ENTRY_SEQUENCE;
817
818 exception
819 when no_data_found then
820 l_function_id := null;
821 return;
822 end;
823
824 delete from FND_MENU_ENTRIES
825 where MENU_ID = X_MENU_ID
826 and ENTRY_SEQUENCE = X_ENTRY_SEQUENCE;
827
828 if (sql%notfound) then
829 raise no_data_found;
830 else
831 -- This means that the menu entry was updated.
832 -- Added for Function Security Cache Invalidation Project
833 -- Acquire sub_menu_id and function_id using menu_id and entry_sequence
834
835 fnd_function_security_cache.delete_menu_entry(X_MENU_ID, l_sub_menu_id, l_function_id);
836 end if;
837
838 delete from FND_MENU_ENTRIES_TL
839 where MENU_ID = X_MENU_ID
840 and ENTRY_SEQUENCE = X_ENTRY_SEQUENCE;
841
842 if (sql%notfound) then
843 raise no_data_found;
844 end if;
845 end DELETE_ROW;
846
847 procedure ADD_LANGUAGE
848 is
849 begin
850 /* Mar/19/03 requested by Ric Ginsberg */
851 /* The following delete and update statements are commented out */
852 /* as a quick workaround to fix the time-consuming table handler issue */
853 /* Eventually we'll need to turn them into a separate fix_language procedure */
854 /*
855
856 delete from FND_MENU_ENTRIES_TL T
857 where not exists
858 (select NULL
859 from FND_MENU_ENTRIES B
860 where B.MENU_ID = T.MENU_ID
861 and B.ENTRY_SEQUENCE = T.ENTRY_SEQUENCE
862 );
863
864 update FND_MENU_ENTRIES_TL T set (
865 PROMPT,
866 DESCRIPTION
867 ) = (select
868 B.PROMPT,
869 B.DESCRIPTION
870 from FND_MENU_ENTRIES_TL B
871 where B.MENU_ID = T.MENU_ID
872 and B.ENTRY_SEQUENCE = T.ENTRY_SEQUENCE
873 and B.LANGUAGE = T.SOURCE_LANG)
874 where (
875 T.MENU_ID,
876 T.ENTRY_SEQUENCE,
877 T.LANGUAGE
878 ) in (select
879 SUBT.MENU_ID,
880 SUBT.ENTRY_SEQUENCE,
881 SUBT.LANGUAGE
882 from FND_MENU_ENTRIES_TL SUBB, FND_MENU_ENTRIES_TL SUBT
883 where SUBB.MENU_ID = SUBT.MENU_ID
884 and SUBB.ENTRY_SEQUENCE = SUBT.ENTRY_SEQUENCE
885 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
886 and (SUBB.PROMPT <> SUBT.PROMPT
887 or (SUBB.PROMPT is null and SUBT.PROMPT is not null)
888 or (SUBB.PROMPT is not null and SUBT.PROMPT is null)
889 or SUBB.DESCRIPTION <> SUBT.DESCRIPTION
890 or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
891 or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
892 ));
893 */
894
895 insert into FND_MENU_ENTRIES_TL (
896 MENU_ID,
897 ENTRY_SEQUENCE,
898 LAST_UPDATE_DATE,
899 LAST_UPDATED_BY,
900 LAST_UPDATE_LOGIN,
901 CREATION_DATE,
902 CREATED_BY,
903 PROMPT,
904 DESCRIPTION,
905 LANGUAGE,
906 SOURCE_LANG
907 ) select
908 B.MENU_ID,
909 B.ENTRY_SEQUENCE,
910 B.LAST_UPDATE_DATE,
911 B.LAST_UPDATED_BY,
912 B.LAST_UPDATE_LOGIN,
913 B.CREATION_DATE,
914 B.CREATED_BY,
915 B.PROMPT,
916 B.DESCRIPTION,
917 L.LANGUAGE_CODE,
918 B.SOURCE_LANG
919 from FND_MENU_ENTRIES_TL B, FND_LANGUAGES L
920 where L.INSTALLED_FLAG in ('I', 'B')
921 and B.LANGUAGE = userenv('LANG')
922 and not exists
923 (select NULL
924 from FND_MENU_ENTRIES_TL T
925 where T.MENU_ID = B.MENU_ID
926 and T.ENTRY_SEQUENCE = B.ENTRY_SEQUENCE
927 and T.LANGUAGE = L.LANGUAGE_CODE);
928 end ADD_LANGUAGE;
929
930 /* Overloaded version below */
931 procedure TRANSLATE_ROW (
932 X_MENU_ID in NUMBER,
933 X_SUB_MENU_ID in NUMBER,
934 X_FUNCTION_ID in NUMBER,
935 X_PROMPT in VARCHAR2,
936 X_DESCRIPTION in VARCHAR2,
937 X_OWNER in VARCHAR2,
938 X_CUSTOM_MODE in VARCHAR2
939 ) is
940 begin
941 fnd_menu_entries_pkg.TRANSLATE_ROW (
942 X_MENU_ID => X_MENU_ID,
943 X_SUB_MENU_ID => X_SUB_MENU_ID,
944 X_FUNCTION_ID => X_FUNCTION_ID,
945 X_PROMPT => X_PROMPT,
946 X_DESCRIPTION => X_DESCRIPTION,
947 X_OWNER => X_OWNER,
948 X_CUSTOM_MODE => X_CUSTOM_MODE,
949 X_LAST_UPDATE_DATE => null
950 );
951 end TRANSLATE_ROW;
952
953 /* Overloaded version above */
954 procedure TRANSLATE_ROW (
955 X_MENU_ID in NUMBER,
956 X_SUB_MENU_ID in NUMBER,
957 X_FUNCTION_ID in NUMBER,
958 X_PROMPT in VARCHAR2,
959 X_DESCRIPTION in VARCHAR2,
960 X_OWNER in VARCHAR2,
961 X_CUSTOM_MODE in VARCHAR2,
962 X_LAST_UPDATE_DATE in VARCHAR2
963 ) is
964 ent_seq NUMBER;
965 f_luby number; -- entity owner in file
966 f_ludate date; -- entity update date in file
967 db_luby number; -- entity owner in db
968 db_ludate date; -- entity update date in db
969
970 begin
971 select entry_sequence into ent_seq
972 from fnd_menu_entries
973 where nvl(sub_menu_id, -1) = nvl(X_SUB_MENU_ID, -1)
974 and nvl(function_id, -1) = nvl(X_FUNCTION_ID, -1)
975 and menu_id = X_MENU_ID;
976
977 -- Translate owner to file_last_updated_by
978 f_luby := fnd_load_util.owner_id(x_owner);
979
980 -- Translate char last_update_date to date
981 f_ludate := nvl(to_date(x_last_update_date, 'YYYY/MM/DD'), sysdate);
982
983 select LAST_UPDATED_BY, LAST_UPDATE_DATE
984 into db_luby, db_ludate
985 from FND_MENU_ENTRIES_TL
986 where MENU_ID = X_MENU_ID
987 and ENTRY_SEQUENCE = ent_seq
988 and userenv('LANG') = LANGUAGE;
989
990 if (fnd_load_util.upload_test(f_luby, f_ludate, db_luby,
991 db_ludate, X_CUSTOM_MODE)) then
992 update FND_MENU_ENTRIES_TL
993 set prompt = decode(X_PROMPT,
994 fnd_load_util.null_value, null,
995 null, prompt,
996 X_PROMPT),
997 DESCRIPTION = X_DESCRIPTION,
998 LAST_UPDATE_DATE = f_ludate,
999 LAST_UPDATED_BY = f_luby,
1000 LAST_UPDATE_LOGIN = 0,
1001 SOURCE_LANG = userenv('LANG')
1002 where MENU_ID = X_MENU_ID
1003 and ENTRY_SEQUENCE = ent_seq
1004 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
1005 end if;
1006
1007
1008 -- Bug 3571184 - Removed 'sql%notfound' and 'raise no_data_found', replaced with
1009 -- an exception handler so all SQL in this block is covered - per request of GB
1010 -- as in bug - we do not want to roll back any changes made for other children in
1011 -- this tree ... MSKEES
1012 EXCEPTION
1013 WHEN NO_DATA_FOUND THEN NULL;
1014
1015 end TRANSLATE_ROW;
1016
1017
1018 /* SUBMIT_COMPILE- Submit a concurrent request to compile the menu/entries*/
1019 /* This routine must be called after loading, inserting, updating, or */
1020 /* deleting data in the menu entries table. It will submit a concurrent */
1021 /* request which will compile that data into the */
1022 /* FND_COMPILED_MENU_FUNCTIONS table. This can be called just once at */
1023 /* the end of loading a number or menu entries. */
1024 /* This routine will check to see if a request has been submitted and */
1025 /* is pending, and will submit one if there is not one pending. */
1026 /* RETURNs: status- 'P' if the request is already pending */
1027 /* 'S' if the request was submitted */
1028 /* 'E' if an error prevented request from being submitted*/
1029 function SUBMIT_COMPILE return varchar2 is
1030 begin
1031 return(FND_JOBS_PKG.SUBMIT_MENU_COMPILE);
1032 end SUBMIT_COMPILE;
1033
1034 end FND_MENU_ENTRIES_PKG;
[Home] [Help]