[Home] [Help]
PACKAGE BODY: APPS.FND_MENUS_PKG
Source
1 package body FND_MENUS_PKG as
2 /* $Header: AFMNMNUB.pls 120.1.12010000.2 2008/11/07 23:24:17 jvalenti ship $ */
3
4 procedure INSERT_ROW (
5 X_ROWID in out nocopy VARCHAR2,
6 X_MENU_ID in NUMBER,
7 X_MENU_NAME in VARCHAR2,
8 X_USER_MENU_NAME in VARCHAR2,
9 X_MENU_TYPE in VARCHAR2,
10 X_DESCRIPTION in VARCHAR2,
11 X_CREATION_DATE in DATE,
12 X_CREATED_BY in NUMBER,
13 X_LAST_UPDATE_DATE in DATE,
14 X_LAST_UPDATED_BY in NUMBER,
15 X_LAST_UPDATE_LOGIN in NUMBER
16 )
17 is
18 cursor C is select ROWID from FND_MENUS
19 where MENU_ID = X_MENU_ID;
20
21 begin
22
23 insert into FND_MENUS (
24 MENU_ID,
25 MENU_NAME,
26 TYPE,
27 CREATION_DATE,
28 CREATED_BY,
29 LAST_UPDATE_DATE,
30 LAST_UPDATED_BY,
31 LAST_UPDATE_LOGIN
32 )
33 values (
34 X_MENU_ID,
35 X_MENU_NAME,
36 X_MENU_TYPE,
37 X_CREATION_DATE,
38 X_CREATED_BY,
39 X_LAST_UPDATE_DATE,
40 X_LAST_UPDATED_BY,
41 X_LAST_UPDATE_LOGIN
42 );
43
44 -- Added for Function Security Cache Invalidation Project
45 fnd_function_security_cache.insert_menu(X_MENU_ID);
46
47 insert into FND_MENUS_TL (
48 MENU_ID,
49 USER_MENU_NAME,
50 LAST_UPDATE_DATE,
51 LAST_UPDATED_BY,
52 LAST_UPDATE_LOGIN,
53 CREATION_DATE,
54 CREATED_BY,
55 DESCRIPTION,
56 LANGUAGE,
57 SOURCE_LANG
58 ) select X_MENU_ID,
59 X_USER_MENU_NAME,
60 X_LAST_UPDATE_DATE,
61 X_LAST_UPDATED_BY,
62 X_LAST_UPDATE_LOGIN,
63 X_CREATION_DATE,
64 X_CREATED_BY,
65 X_DESCRIPTION,
66 L.LANGUAGE_CODE,
67 userenv('LANG')
68 from FND_LANGUAGES L
69 where L.INSTALLED_FLAG in ('I', 'B')
70 and not exists (select NULL
71 from FND_MENUS_TL T
72 where T.MENU_ID = X_MENU_ID
73 and T.LANGUAGE = L.LANGUAGE_CODE);
74
75 open c;
76 fetch c into X_ROWID;
77 if (c%notfound) then
78 close c;
79 raise no_data_found;
80 end if;
81 close c;
82
83 end INSERT_ROW;
84
85 procedure LOCK_ROW (
86 X_MENU_ID in NUMBER,
87 X_MENU_NAME in VARCHAR2,
88 X_USER_MENU_NAME in VARCHAR2,
89 X_MENU_TYPE in VARCHAR2,
90 X_DESCRIPTION in VARCHAR2
91 ) is
92 cursor c is select
93 MENU_NAME, TYPE
94 from FND_MENUS
95 where MENU_ID = X_MENU_ID
96 for update of MENU_ID nowait;
97 recinfo c%rowtype;
98
99 cursor c1 is select
100 USER_MENU_NAME,
101 DESCRIPTION
102 from FND_MENUS_TL
103 where MENU_ID = X_MENU_ID
104 and LANGUAGE = userenv('LANG')
105 for update of MENU_ID nowait;
106 tlinfo c1%rowtype;
107
108 begin
109 open c;
110 fetch c into recinfo;
111 if (c%notfound) then
112 close c;
113 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
114 app_exception.raise_exception;
115 end if;
116 close c;
117 if ( (recinfo.MENU_NAME = X_MENU_NAME)
118 AND ((recinfo.TYPE = X_MENU_TYPE)
119 OR ((recinfo.TYPE is null) AND (X_MENU_TYPE is null)))
120 ) then
121 null;
122 else
123 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
124 app_exception.raise_exception;
125 end if;
126
127 open c1;
128 fetch c1 into tlinfo;
129 if (c1%notfound) then
130 close c1;
131 return;
132 end if;
133 close c1;
134
135 if ( (tlinfo.USER_MENU_NAME = X_USER_MENU_NAME)
136 AND ((tlinfo.DESCRIPTION = X_DESCRIPTION)
137 OR ((tlinfo.DESCRIPTION is null) AND (X_DESCRIPTION is null)))
138 ) then
139 null;
140 else
141 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
142 app_exception.raise_exception;
143 end if;
144 return;
145 end LOCK_ROW;
146
147 procedure UPDATE_ROW (
148 X_MENU_ID in NUMBER,
149 X_MENU_NAME in VARCHAR2,
150 X_USER_MENU_NAME in VARCHAR2,
151 X_MENU_TYPE in VARCHAR2,
152 X_DESCRIPTION in VARCHAR2,
153 X_LAST_UPDATE_DATE in DATE,
154 X_LAST_UPDATED_BY in NUMBER,
155 X_LAST_UPDATE_LOGIN in NUMBER
156 )
157 is
158
159 begin
160 update FND_MENUS
161 set MENU_NAME = X_MENU_NAME,
162 TYPE = X_MENU_TYPE,
163 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
164 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
165 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
166 where MENU_ID = X_MENU_ID;
167
168 if (sql%notfound) then
169 raise no_data_found;
170 else
171 -- This means that a menu was updated.
172 -- Added for Function Security Cache Invalidation Project
173 fnd_function_security_cache.update_menu(X_MENU_ID);
174 end if;
175
176 update FND_MENUS_TL
177 set USER_MENU_NAME = X_USER_MENU_NAME,
178 DESCRIPTION = X_DESCRIPTION,
179 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
180 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
181 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
182 SOURCE_LANG = userenv('LANG')
183 where MENU_ID = X_MENU_ID
184 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
185
186 if (sql%notfound) then
187 raise no_data_found;
188 end if;
189 end UPDATE_ROW;
190
191 /* Overloaded version below */
192 procedure LOAD_ROW (
193 X_MENU_NAME in VARCHAR2,
194 X_MENU_TYPE in VARCHAR2,
195 X_USER_MENU_NAME in VARCHAR2,
196 X_DESCRIPTION in VARCHAR2,
197 X_OWNER in VARCHAR2,
198 X_CUSTOM_MODE in VARCHAR2
199 ) is
200 begin
201 fnd_menus_pkg.LOAD_ROW (
202 X_MENU_NAME => X_MENU_NAME,
203 X_MENU_TYPE => X_MENU_TYPE,
204 X_USER_MENU_NAME => X_USER_MENU_NAME,
205 X_DESCRIPTION => X_DESCRIPTION,
206 X_OWNER => X_OWNER,
207 X_CUSTOM_MODE => X_CUSTOM_MODE,
208 X_LAST_UPDATE_DATE => null
209 );
210 end LOAD_ROW;
211
212 /* Overloaded version above */
213 procedure LOAD_ROW (
214 X_MENU_NAME in VARCHAR2,
215 X_MENU_TYPE in VARCHAR2,
216 X_USER_MENU_NAME in VARCHAR2,
217 X_DESCRIPTION in VARCHAR2,
218 X_OWNER in VARCHAR2,
219 X_CUSTOM_MODE in VARCHAR2,
220 X_LAST_UPDATE_DATE in VARCHAR2
221 ) is
222 man_id number;
223 row_id varchar2(64);
224 f_luby number; -- entity owner in file
225 f_ludate date; -- entity update date in file
226 db_luby number; -- entity owner in db
227 db_ludate date; -- entity update date in db
228 begin
229 -- Translate owner to file_last_updated_by
230 f_luby := fnd_load_util.owner_id(x_owner);
231
232 -- Translate char last_update_date to date
233 f_ludate := nvl(to_date(x_last_update_date, 'YYYY/MM/DD'), sysdate);
234
235 select menu_id, last_updated_by, last_update_date
236 into man_id, db_luby, db_ludate
237 from fnd_menus
238 where menu_name = X_MENU_NAME;
239
240 if (fnd_load_util.upload_test(f_luby, f_ludate, db_luby,
241 db_ludate, X_CUSTOM_MODE)) then
242 fnd_menus_pkg.UPDATE_ROW (
243 X_MENU_ID => man_id,
244 X_MENU_NAME => X_MENU_NAME,
245 X_USER_MENU_NAME => X_USER_MENU_NAME,
246 X_MENU_TYPE => X_MENU_TYPE,
247 X_DESCRIPTION => X_DESCRIPTION,
248 X_LAST_UPDATE_DATE => f_ludate,
249 X_LAST_UPDATED_BY => f_luby,
250 X_LAST_UPDATE_LOGIN => 0 );
251 end if;
252 exception
253 when NO_DATA_FOUND then
254
255 select fnd_menus_s.nextval into man_id from dual;
256
257 fnd_menus_pkg.INSERT_ROW(
258 X_ROWID => row_id,
259 X_MENU_ID => man_id,
260 X_MENU_NAME => X_MENU_NAME,
261 X_USER_MENU_NAME => X_USER_MENU_NAME,
262 X_MENU_TYPE => X_MENU_TYPE,
263 X_DESCRIPTION => X_DESCRIPTION,
264 X_CREATION_DATE => f_ludate,
265 X_CREATED_BY => f_luby,
266 X_LAST_UPDATE_DATE => f_ludate,
267 X_LAST_UPDATED_BY => f_luby,
268 X_LAST_UPDATE_LOGIN => 0 );
269 end LOAD_ROW;
270
271 procedure DELETE_ROW (
272 X_MENU_ID in NUMBER
273 )
274 is
275
276 begin
277
278 delete from FND_MENUS
279 where MENU_ID = X_MENU_ID;
280
281 if (sql%notfound) then
282 raise no_data_found;
283 else
284 -- This means that a menu was deleted.
285 -- Added for Function Security Cache Invalidation Project
286 fnd_function_security_cache.delete_menu(X_MENU_ID);
287 end if;
288
289 delete from FND_MENUS_TL
290 where MENU_ID = X_MENU_ID;
291
292 if (sql%notfound) then
293 raise no_data_found;
294 end if;
295 end DELETE_ROW;
296
297 procedure ADD_LANGUAGE
298 is
299 begin
300 /* Mar/19/03 requested by Ric Ginsberg */
301 /* The following delete and update statements are commented out */
302 /* as a quick workaround to fix the time-consuming table handler issue */
303 /* Eventually we'll need to turn them into a separate fix_language procedure */
304 /*
305
306 delete from FND_MENUS_TL T
307 where not exists
308 (select NULL
309 from FND_MENUS B
310 where B.MENU_ID = T.MENU_ID
311 );
312
313 update FND_MENUS_TL T set (
314 USER_MENU_NAME,
315 DESCRIPTION
316 ) = (select
317 B.USER_MENU_NAME,
318 B.DESCRIPTION
319 from FND_MENUS_TL B
320 where B.MENU_ID = T.MENU_ID
321 and B.LANGUAGE = T.SOURCE_LANG)
322 where (
323 T.MENU_ID,
324 T.LANGUAGE
325 ) in (select
326 SUBT.MENU_ID,
327 SUBT.LANGUAGE
328 from FND_MENUS_TL SUBB, FND_MENUS_TL SUBT
329 where SUBB.MENU_ID = SUBT.MENU_ID
330 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
331 and (SUBB.USER_MENU_NAME <> SUBT.USER_MENU_NAME
332 or SUBB.DESCRIPTION <> SUBT.DESCRIPTION
333 or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
334 or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
335 ));
336 */
337
338 insert into FND_MENUS_TL (
339 MENU_ID,
340 USER_MENU_NAME,
341 LAST_UPDATE_DATE,
342 LAST_UPDATED_BY,
343 LAST_UPDATE_LOGIN,
344 CREATION_DATE,
345 CREATED_BY,
346 DESCRIPTION,
347 LANGUAGE,
348 SOURCE_LANG
349 ) select
350 B.MENU_ID,
351 B.USER_MENU_NAME,
352 B.LAST_UPDATE_DATE,
353 B.LAST_UPDATED_BY,
354 B.LAST_UPDATE_LOGIN,
355 B.CREATION_DATE,
356 B.CREATED_BY,
357 B.DESCRIPTION,
358 L.LANGUAGE_CODE,
359 B.SOURCE_LANG
360 from FND_MENUS_TL B, FND_LANGUAGES L
361 where L.INSTALLED_FLAG in ('I', 'B')
362 and B.LANGUAGE = userenv('LANG')
363 and not exists
364 (select NULL
365 from FND_MENUS_TL T
366 where T.MENU_ID = B.MENU_ID
367 and T.LANGUAGE = L.LANGUAGE_CODE);
368 end ADD_LANGUAGE;
369
370 /* Overloaded version below */
371 procedure TRANSLATE_ROW (
372 X_MENU_ID in NUMBER,
373 X_USER_MENU_NAME in VARCHAR2,
374 X_DESCRIPTION in VARCHAR2,
375 X_OWNER in VARCHAR2,
376 X_CUSTOM_MODE in VARCHAR2
377 ) is
378 begin
379 fnd_menus_pkg.TRANSLATE_ROW (
380 X_MENU_ID => X_MENU_ID,
381 X_USER_MENU_NAME => X_USER_MENU_NAME,
382 X_DESCRIPTION => X_DESCRIPTION,
383 X_OWNER => X_OWNER,
384 X_CUSTOM_MODE => X_CUSTOM_MODE,
385 X_LAST_UPDATE_DATE => null
386 );
387 end TRANSLATE_ROW;
388
389 /* Overloaded version above */
390 procedure TRANSLATE_ROW (
391 X_MENU_ID in NUMBER,
392 X_USER_MENU_NAME in VARCHAR2,
393 X_DESCRIPTION in VARCHAR2,
394 X_OWNER in VARCHAR2,
395 X_CUSTOM_MODE in VARCHAR2,
396 X_LAST_UPDATE_DATE in VARCHAR2
397 ) is
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 begin
403 -- Translate owner to file_last_updated_by
404 f_luby := fnd_load_util.owner_id(x_owner);
405
406 -- Translate char last_update_date to date
407 f_ludate := nvl(to_date(x_last_update_date, 'YYYY/MM/DD'), sysdate);
408
409 select LAST_UPDATED_BY, LAST_UPDATE_DATE
410 into db_luby, db_ludate
411 from FND_MENUS_TL
412 where MENU_ID = X_MENU_ID
413 and userenv('LANG') = LANGUAGE;
414
415 if (fnd_load_util.upload_test(f_luby, f_ludate, db_luby,
416 db_ludate, X_CUSTOM_MODE)) then
417 update FND_MENUS_TL set
418 USER_MENU_NAME = X_USER_MENU_NAME,
419 DESCRIPTION = X_DESCRIPTION,
420 LAST_UPDATE_DATE = f_ludate,
421 LAST_UPDATED_BY = f_luby,
422 LAST_UPDATE_LOGIN = 0,
423 SOURCE_LANG = userenv('LANG')
424 where MENU_ID = X_MENU_ID
425 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
426 end if;
427
428 if (sql%notfound) then
429 raise no_data_found;
430 end if;
431
432 end TRANSLATE_ROW;
433
434 procedure SET_NEW_MENU is
435 begin
436 FND_MENUS_PKG.currentryseq := 0;
437 end SET_NEW_MENU;
438
439 function NEXT_ENTRY_SEQUENCE return number is
440 begin
441 --Bug6525216 Making the sequence in increments of 5.
442
443 FND_MENUS_PKG.currentryseq := FND_MENUS_PKG.currentryseq + 5;
444 return(FND_MENUS_PKG.currentryseq);
445 end NEXT_ENTRY_SEQUENCE;
446
447 function VALIDATE_MENU_TYPE(X_MENU_TYPE in VARCHAR2) return boolean is
448 buffer varchar2(80);
449 begin
450 begin
451 if (X_MENU_TYPE is null) then
452 return(true);
453 end if;
454
455 select meaning into buffer
456 from fnd_lookups
457 where LOOKUP_TYPE = 'MENU_TYPE'
458 and lookup_code = upper(X_MENU_TYPE);
459 exception
460 when no_data_found then
461 return(false);
462 end;
463
464 return(true);
465
466 end VALIDATE_MENU_TYPE;
467
468 end FND_MENUS_PKG;