[Home] [Help]
PACKAGE BODY: APPS.JTF_MENU_PUB
Source
1 PACKAGE BODY jtf_menu_pub AS
2 /* $Header: jtfmenub.pls 120.2 2005/10/25 05:22:20 psanyal ship $ */
3 /* this is test version 'sql5.1' */
4
5 type num_tab_ibbi is table of number index by binary_integer;
6
7 -- =======================================================================
8 -- Types and Constants
9 -- =======================================================================
10 TYPE function_data is record(
11 web_host_name fnd_form_functions_vl.web_host_name%type, -- varchar2(80),
12 web_agent_name fnd_form_functions_vl.web_agent_name%type, -- varchar2(80),
13 web_html_call fnd_form_functions_vl.web_html_call%type, -- varchar2(240),
14 web_encrypt_parameters fnd_form_functions_vl.web_encrypt_parameters%type, -- varchar2(1),
15 web_secured fnd_form_functions_vl.web_secured%type, -- varchar2(1),
16 web_icon fnd_form_functions_vl.web_icon%type, -- varchar2(30),
17 function_id fnd_form_functions_vl.function_id%type, -- number,
18 function_name fnd_form_functions_vl.function_name%type, -- varchar2(30),
19 application_id fnd_form_functions_vl.application_id%type, -- number,
20 creation_date fnd_form_functions_vl.creation_date%type, -- date,
21 type fnd_form_functions_vl.type%type, -- varchar2(30),
22 user_function_name fnd_form_functions_vl.user_function_name%type, -- varchar2(80),
23 description fnd_form_functions_vl.description%type -- varchar2(240)
24 );
25
26 cursor get_app_menu(p_menuid number) is
27 select a.sub_menu_id , prompt,description,function_id,menu_name
28 from fnd_menu_entries_vl a, fnd_menus b
29 where a.menu_id = p_menuid and b.menu_id(+) = a.sub_menu_id ;
30
31 cursor get_function_c(p_functionid number) is
32 select web_host_name, web_agent_name,web_html_call,
33 web_encrypt_parameters, web_secured,
34 web_icon,function_id, function_name,application_id, creation_date,type,
35 user_function_name, description
36 from fnd_form_functions_vl
37 where function_id = p_functionid;
38
39 -- this private function is the basis of 'menu exclusion' functionality.
40 -- (this functionality used to be done only in the Java layer, but
41 -- we moved it to PL/SQL).
42 --
43 -- returns true if the users with (p_respid, p_appid) are able
44 -- to see this menu_data (i.e. if they're NOT excluded from it)
45 -- If the menu object contains a m_subMenuId, then we only consider
46 -- whether we're excluded from the specified menu, else we only look
47 -- at the m_functionId to see whether we're excluded from that.
48 -- p_sub_menu_id and p_function_id are simply the sub_menu_id
49 -- and function_id from the candidate menu_data
50 function check_exclusion(p_respid number, p_appid number,
51 p_sub_menu_id number, p_function_id number) return boolean is
52 temp fnd_resp_functions.rule_type%type;
53
54 -- this cursors job is simply to help us decide whether there exists
55 -- any row which matches the given respid/appid/action_id/rule_type.
56 cursor c1(pp_respid number, pp_appid number, pp_action_id number,
57 pp_rule_type varchar2) is
58 select rule_type from fnd_resp_functions where
59 application_id = pp_appid and responsibility_id = pp_respid and
60 action_id = pp_action_id and rule_type = pp_rule_type;
61 begin
62 if p_sub_menu_id is not null then
63 -- check exclusion only based on the p_sub_menu_id!
64 open c1(p_respid, p_appid, p_sub_menu_id, 'M');
65 fetch c1 into temp;
66
67 -- if temp is no longer null, then there IS an exclusion
68 -- on this menu_id!
69 return temp is null;
70 else
71 -- check exclusion only based on the p_function_id
72 open c1(p_respid, p_appid, p_function_id, 'F');
73 fetch c1 into temp;
74
75 -- if temp is no longer null, then there IS an exclusion
76 -- on this function_id!
77 return temp is null;
78 end if;
79 end;
80
81
82 procedure get_excl_menu_tree_recurs_tl(p_lang varchar2, p_menu_id number,
83 p_respid number, p_appid number,
84 p_max_depth number, p_kids_menu_ids in out nocopy number_table,
85 p_kids_menu_data in out nocopy menu_table)
86 is
87 t_new_ids num_tab_ibbi;
88 t_mt menu_table;
89 cnt number;
90 loc number;
91 begin
92 get_excluded_menu_entries_tl(p_lang, p_menu_id, p_respid, p_appid, t_mt);
93
94 if t_mt is null or t_mt.count = 0 then return; end if;
95
96 cnt := t_mt.first;
97 while true loop
98 -- put the p_menu_id in the p_kids_menu_ids, and the new menu_data
99 -- from t_mt into the p_kids_menu_data
100 loc := p_kids_menu_ids.count+1;
101 p_kids_menu_ids(loc) := p_menu_id;
102 p_kids_menu_data(loc) := t_mt(cnt);
103
104 -- if this child also points at a menu, and we're not past our maximum
105 -- depth, then add it to t_new_ids, so we'll remember to recurse
106 if p_max_depth > 0 and t_mt(cnt).sub_menu_id is not null then
107 t_new_ids(t_new_ids.count+1) := t_mt(cnt).sub_menu_id;
108 end if;
109
110 -- next...
111 if cnt = t_mt.last then exit; end if;
112 cnt := t_mt.next(cnt);
113 end loop;
114
115 -- if no new_ids were saved, then just return
116 if t_new_ids is null or t_new_ids.count = 0 then return; end if;
117
118 -- recurse
119 cnt := t_new_ids.first;
120 while true loop
121 get_excl_menu_tree_recurs_tl(p_lang, t_new_ids(cnt), p_respid, p_appid,
122 p_max_depth-1, p_kids_menu_ids, p_kids_menu_data);
123
124 -- next...
125 if cnt = t_new_ids.last then exit; end if;
126 cnt := t_new_ids.next(cnt);
127 end loop;
128 end get_excl_menu_tree_recurs_tl;
129
130 -- this'll call new get_root_menu_tl, then recursively
131 -- get_menu_entries_tl, up to p_max_depth
132 procedure get_excl_entire_menu_tree_tl(
133 p_lang varchar2,
134 p_respid number,
135 p_appid number,
136 p_max_depth number,
137 p_responsibility_table OUT NOCOPY /* file.sql.39 change */ responsibility_table, -- from get_root_menu
138 p_root_menu_data OUT NOCOPY /* file.sql.39 change */ root_menu_data, -- from get_root_menu
139 p_root_menu_table OUT NOCOPY /* file.sql.39 change */ menu_table,
140 p_kids_menu_ids OUT NOCOPY /* file.sql.39 change */ number_table,
141 p_kids_menu_data OUT NOCOPY /* file.sql.39 change */ menu_table) -- all menus except the root
142 is
143 cnt number;
144 t_mt menu_table;
145 t_kids_menu_ids number_table;
146 t_kids_menu_data menu_table;
147 t_lang varchar2(25); -- probably '5' was enuf, I don't know the max
148 begin
149 if p_lang is null then
150 select userenv('lang') into t_lang from dual;
151 else
152 t_lang := p_lang;
153 end if;
154
155 -- first, get the root menu info
156 get_excluded_root_menu_tl(t_lang, p_respid, p_appid,
157 p_responsibility_table, p_root_menu_data, t_mt);
158
159 -- now, recursively get the children of the root menu, up to
160 -- depth p_max_depth
161 if t_mt is not null and t_mt.count > 0 then
162 cnt := t_mt.first;
163 while true loop
164 if t_mt(cnt).sub_menu_id is not null then
165 get_excl_menu_tree_recurs_tl(t_lang, t_mt(cnt).sub_menu_id,
166 p_respid, p_appid,
167 p_max_depth, t_kids_menu_ids, t_kids_menu_data);
168 end if;
169
170 -- next...
171 if cnt = t_mt.last then exit; end if;
172 cnt := t_mt.next(cnt);
173 end loop;
174 end if;
175
176 -- copy temp values to OUT NOCOPY /* file.sql.39 change */ variables
177 p_root_menu_table := t_mt;
178 p_kids_menu_ids := t_kids_menu_ids;
179 p_kids_menu_data := t_kids_menu_data;
180 end get_excl_entire_menu_tree_tl;
181
182
183 PROCEDURE get_excluded_root_menu_tl(
184 p_lang in varchar2
185 , p_respid in number
186 , p_appid in number
187 , p_responsibility_table OUT NOCOPY /* file.sql.39 change */ responsibility_table
188 , p_root_menu_data OUT NOCOPY /* file.sql.39 change */ root_menu_data
189 , p_menu_table OUT NOCOPY /* file.sql.39 change */ menu_table
190 )
191 IS
192 t_lang varchar2(25); -- probably '5' was enuf, I don't know the max
193 p_function_data function_data := null;
194 cnt number := 0;
195 l_resp_name varchar2(256);
196 cursor root_menu(pp_respid number, pp_appid number, pp_lang varchar2) is
197 select b.menu_id, b.menu_name , t.user_menu_name, t.description
198 from fnd_menus_tl t, fnd_menus b, fnd_responsibility r
199 where b.menu_id = t.menu_id and
200 t.language = pp_lang and
201 b.menu_id = r.menu_id and
202 r.responsibility_id = pp_respid and
203 r.application_id = pp_appid;
204
205 -- cursor get_responsibilities(pp_menuid number, pp_lang varchar2) is
206 -- select b.responsibility_id, t.responsibility_name
207 -- from fnd_responsibility_tl t, fnd_responsibility b
208 -- where b.responsibility_id = t.responsibility_id and
209 -- b.application_id = t.application_id and
210 -- t.language = pp_lang and
211 -- b.menu_id = pp_menuid;
212 BEGIN
213 if p_lang is null then
214 select userenv('lang') into t_lang from dual;
215 else
216 t_lang := p_lang;
217 end if;
218
219 open root_menu(p_respid, p_appid, t_lang);
220 fetch root_menu into p_root_menu_data;
221 close root_menu;
222
223 get_excluded_menu_entries_tl(t_lang, p_root_menu_data.menu_id,
224 p_respid, p_appid,
225 p_menu_table);
226
227 --for c1 in get_responsibilities(p_root_menu_data.menu_id, t_lang)
228 --LOOP
229 --cnt := cnt + 1;
230 --p_responsibility_table(cnt).responsibility_id := c1.responsibility_id;
231 --p_responsibility_table(cnt).responsibility_name := c1.responsibility_name;
232 cnt := cnt + 1;
233 p_responsibility_table(cnt).responsibility_id := p_respid;
234 select t.responsibility_name into l_resp_name
235 from fnd_responsibility_tl t, fnd_responsibility b
236 where b.responsibility_id = t.responsibility_id and
237 b.application_id = t.application_id and
238 t.language = t_lang and b.responsibility_id = p_respid and b.application_id = p_appid;
239 p_responsibility_table(cnt).responsibility_name := l_resp_name;
240 --END LOOP;
241 END get_excluded_root_menu_tl;
242
243 PROCEDURE get_root_menu_tl(
244 p_lang in varchar2
245 , p_respid in number
246 , p_appid in number
247 , p_responsibility_table OUT NOCOPY /* file.sql.39 change */ responsibility_table
248 , p_root_menu_data OUT NOCOPY /* file.sql.39 change */ root_menu_data
249 , p_menu_table OUT NOCOPY /* file.sql.39 change */ menu_table
250 )
251 IS
252 t_lang varchar2(25); -- probably '5' was enuf, I don't know the max
253 p_function_data function_data := null;
254 cnt number := 0;
255 l_resp_name varchar2(256);
256 -- here's the old cursor definition, which assumed userenv('lang')
257 -- was set correctly:
258 -- cursor root_menu(pp_respid number, pp_appid number) is
259 --select a.menu_id, a.menu_name , a.user_menu_name, a.description
260 --from fnd_menus_vl a , fnd_responsibility b
261 --where a.menu_id = b.menu_id and b.responsibility_id = pp_respid
262 --and b.application_id = pp_appid;
263
264 -- here's the new one, which assumes we use pp_lang instead
265 cursor root_menu(pp_respid number, pp_appid number, pp_lang varchar2) is
266 select b.menu_id, b.menu_name , t.user_menu_name, t.description
267 from fnd_menus_tl t, fnd_menus b, fnd_responsibility r
268 where b.menu_id = t.menu_id and
269 t.language = pp_lang and
270 b.menu_id = r.menu_id and
271 r.responsibility_id = pp_respid and
272 r.application_id = pp_appid;
273
274 -- here's the old cursor definition, which assumed userenv('lang')
275 -- was set correctly:
276 -- cursor get_responsibilities(pp_menuid number) is
277 -- select a.responsibility_id, a.responsibility_name
278 -- from fnd_responsibility_vl a
279 -- where a.menu_id = pp_menuid;
280
281 -- here's the new one, which assumes we use pp_lang instead
282 -- cursor get_responsibilities(pp_menuid number, pp_lang varchar2) is
283 -- select b.responsibility_id, t.responsibility_name
284 -- from fnd_responsibility_tl t, fnd_responsibility b
285 -- where b.responsibility_id = t.responsibility_id and
286 -- b.application_id = t.application_id and
287 -- t.language = pp_lang and
288 -- b.menu_id = pp_menuid;
289 BEGIN
290 if p_lang is null then
291 select userenv('lang') into t_lang from dual;
292 else
293 t_lang := p_lang;
294 end if;
295
296 open root_menu(p_respid, p_appid, t_lang);
297 fetch root_menu into p_root_menu_data;
298 close root_menu;
299
300 get_menu_entries_tl(t_lang, p_root_menu_data.menu_id, p_menu_table);
301
302 --for c1 in get_responsibilities(p_root_menu_data.menu_id, t_lang)
303 --LOOP
304 --cnt := cnt + 1;
305 --p_responsibility_table(cnt).responsibility_id := c1.responsibility_id;
306 --p_responsibility_table(cnt).responsibility_name := c1.responsibility_name;
307 cnt := cnt + 1;
308 p_responsibility_table(cnt).responsibility_id := p_respid;
309 select t.responsibility_name into l_resp_name
310 from fnd_responsibility_tl t, fnd_responsibility b
311 where b.responsibility_id = t.responsibility_id and
312 b.application_id = t.application_id and
313 t.language = t_lang and b.responsibility_id = p_respid and b.application_id = p_appid;
314 p_responsibility_table(cnt).responsibility_name := l_resp_name;
315 --END LOOP;
316 END get_root_menu_tl;
317
318 PROCEDURE get_root_menu(
319 p_respid in number
320 , p_appid in number
321 , p_responsibility_table OUT NOCOPY /* file.sql.39 change */ responsibility_table
322 , p_root_menu_data OUT NOCOPY /* file.sql.39 change */ root_menu_data
323 , p_menu_table OUT NOCOPY /* file.sql.39 change */ menu_table
324 )
325 IS
326
327 p_function_data function_data := null;
328 cnt number := 0;
329 l_resp_name varchar2(256);
330 cursor root_menu(pp_respid number, pp_appid number) is
331 select a.menu_id, a.menu_name , a.user_menu_name, a.description
332 from fnd_menus_vl a , fnd_responsibility b
333 where a.menu_id = b.menu_id and b.responsibility_id = pp_respid
334 and b.application_id = pp_appid;
335
336 --cursor get_responsibilities(pp_menuid number) is
337 --select a.responsibility_id, a.responsibility_name
338 --from fnd_responsibility_vl a
339 --where a.menu_id = pp_menuid;
340
341 BEGIN
342
343 open root_menu(p_respid,p_appid);
344 fetch root_menu into p_root_menu_data;
345 close root_menu;
346
347 get_menu_entries(p_root_menu_data.menu_id, p_menu_table);
348
349 --for c1 in get_responsibilities(p_root_menu_data.menu_id, t_lang)
350 --LOOP
351 --cnt := cnt + 1;
352 --p_responsibility_table(cnt).responsibility_id := c1.responsibility_id;
353 --p_responsibility_table(cnt).responsibility_name := c1.responsibility_name;
354 cnt := cnt + 1;
355 p_responsibility_table(cnt).responsibility_id := p_respid;
356 select a.responsibility_name into l_resp_name
357 from fnd_responsibility_vl a
358 where a.responsibility_id = p_respid and a.application_id = p_appid;
359 p_responsibility_table(cnt).responsibility_name := l_resp_name;
360 --END LOOP;
361
362 END get_root_menu;
363
364
365 PROCEDURE get_function(
366 p_function_id in number
367 , p_function_data OUT NOCOPY /* file.sql.39 change */ function_data
368 ) IS
369
370 BEGIN
371
372 open get_function_c(p_function_id);
373 fetch get_function_c into p_function_data;
374 close get_function_c;
375
376 END get_function;
377
378 procedure get_excluded_menu_entries_tl(
379 p_lang varchar2,
380 p_menu_id number,
381 p_respid number,
382 p_appid number,
383 p_menu_table OUT NOCOPY /* file.sql.39 change */ menu_table) is
384 t_lang varchar2(25); -- probably '5' was enuf, I don't know the max
385 cnt number := 0;
386 p_function_data function_data := null;
387
388 cursor get_sub_menus(p_mid number, pp_lang varchar2) is
389 SELECT
390 rowidtochar(B.ROWID) menu_entry_rowid, B.MENU_ID, B.ENTRY_SEQUENCE,
391 B.SUB_MENU_ID, B.FUNCTION_ID,
392 B.GRANT_FLAG, B.LAST_UPDATE_DATE, B.LAST_UPDATED_BY, B.LAST_UPDATE_LOGIN,
393 B.CREATION_DATE , B.CREATED_BY , T.PROMPT, T.DESCRIPTION, fm.menu_name
394 FROM FND_MENU_ENTRIES_TL T, FND_MENU_ENTRIES B, fnd_menus fm
395 WHERE B.MENU_ID = T.MENU_ID AND B.ENTRY_SEQUENCE = T.ENTRY_SEQUENCE
396 and b.menu_id = p_mid and fm.menu_id(+) = b.sub_menu_id
397 AND T.LANGUAGE = pp_lang
398 order by b.entry_sequence;
399 begin
400 if p_lang is null then
401 select userenv('lang') into t_lang from dual;
402 else
403 t_lang := p_lang;
404 end if;
405
406 for c1 in get_sub_menus(p_menu_id, t_lang) loop
407 if check_exclusion(p_respid, p_appid, c1.sub_menu_id,
408 c1.function_id) then
409 cnt := cnt + 1;
410
411 p_menu_table(cnt).sub_menu_id := c1.sub_menu_id;
412 p_menu_table(cnt).prompt := c1.prompt;
413 p_menu_table(cnt).description := c1.description;
414 p_menu_table(cnt).function_id := c1.function_id;
415 p_menu_table(cnt).menu_name := c1.menu_name;
416 p_menu_table(cnt).menu_entry_rowid := c1.menu_entry_rowid;
417
418 if c1.function_id > 0 then
419 get_function(c1.function_id, p_function_data);
420 p_menu_table(cnt).func_web_host_name := p_function_data.web_host_name;
421 p_menu_table(cnt).func_web_agent_name := p_function_data.web_agent_name;
422 p_menu_table(cnt).func_web_html_call := p_function_data.web_html_call;
423 p_menu_table(cnt).func_web_encrypt_parameters := p_function_data.web_encrypt_parameters;
424 p_menu_table(cnt).func_web_secured := p_function_data.web_secured;
425 p_menu_table(cnt).func_web_icon := p_function_data.web_icon;
426 p_menu_table(cnt).func_function_id := p_function_data.function_id;
427 p_menu_table(cnt).func_function_name := p_function_data.function_name;
428 p_menu_table(cnt).func_application_id := p_function_data.application_id;
429 p_menu_table(cnt).func_creation_date := p_function_data.creation_date;
430 p_menu_table(cnt).func_type := p_function_data.type;
431 p_menu_table(cnt).func_user_function_name := p_function_data.user_function_name;
432 p_menu_table(cnt).func_description := p_function_data.description;
433 end if;
434 end if;
435 end loop;
436 end get_excluded_menu_entries_tl;
437
438 procedure get_menu_entries_tl(
439 p_lang varchar2,
440 p_menu_id number,
441 p_menu_table OUT NOCOPY /* file.sql.39 change */ menu_table) is
442 t_lang varchar2(25); -- probably '5' was enuf, I don't know the max
443 cnt number := 0;
444 p_function_data function_data := null;
445
446 cursor get_sub_menus(p_mid number, pp_lang varchar2) is
447 -- here's the query, before we parameterized it by language
448 -- select sub_menu_id, prompt, description, function_id, menu_name,
449 -- rowidtochar(a.rowid) menu_entry_rowid
450 -- from fnd_menu_entries_vl a, fnd_menus b
451 -- where a.menu_id = p_mid and b.menu_id(+) = a.sub_menu_id
452 -- order by entry_sequence;
453 SELECT
454 rowidtochar(B.ROWID) menu_entry_rowid, B.MENU_ID, B.ENTRY_SEQUENCE,
455 B.SUB_MENU_ID, B.FUNCTION_ID,
456 B.GRANT_FLAG, B.LAST_UPDATE_DATE, B.LAST_UPDATED_BY, B.LAST_UPDATE_LOGIN,
457 B.CREATION_DATE , B.CREATED_BY , T.PROMPT, T.DESCRIPTION, fm.menu_name
458 FROM FND_MENU_ENTRIES_TL T, FND_MENU_ENTRIES B, fnd_menus fm
459 WHERE B.MENU_ID = T.MENU_ID AND B.ENTRY_SEQUENCE = T.ENTRY_SEQUENCE
460 and b.menu_id = p_mid and fm.menu_id(+) = b.sub_menu_id
461 AND T.LANGUAGE = pp_lang
462 order by b.entry_sequence;
463 begin
464 if p_lang is null then
465 select userenv('lang') into t_lang from dual;
466 else
467 t_lang := p_lang;
468 end if;
469
470 for c1 in get_sub_menus(p_menu_id, t_lang) loop
471 cnt := cnt + 1;
472
473 p_menu_table(cnt).sub_menu_id := c1.sub_menu_id;
474 p_menu_table(cnt).prompt := c1.prompt;
475 p_menu_table(cnt).description := c1.description;
476 p_menu_table(cnt).function_id := c1.function_id;
477 p_menu_table(cnt).menu_name := c1.menu_name;
478 p_menu_table(cnt).menu_entry_rowid := c1.menu_entry_rowid;
479
480 if c1.function_id > 0 then
481 get_function(c1.function_id, p_function_data);
482 p_menu_table(cnt).func_web_host_name := p_function_data.web_host_name;
483 p_menu_table(cnt).func_web_agent_name := p_function_data.web_agent_name;
484 p_menu_table(cnt).func_web_html_call := p_function_data.web_html_call;
485 p_menu_table(cnt).func_web_encrypt_parameters := p_function_data.web_encrypt_parameters;
486 p_menu_table(cnt).func_web_secured := p_function_data.web_secured;
487 p_menu_table(cnt).func_web_icon := p_function_data.web_icon;
488 p_menu_table(cnt).func_function_id := p_function_data.function_id;
489 p_menu_table(cnt).func_function_name := p_function_data.function_name;
490 p_menu_table(cnt).func_application_id := p_function_data.application_id;
491 p_menu_table(cnt).func_creation_date := p_function_data.creation_date;
492 p_menu_table(cnt).func_type := p_function_data.type;
493 p_menu_table(cnt).func_user_function_name := p_function_data.user_function_name;
494 p_menu_table(cnt).func_description := p_function_data.description;
495 end if;
496 end loop;
497 end get_menu_entries_tl;
498
499 PROCEDURE get_menu_entries(
500 p_menu_id in number
501 , p_menu_table OUT NOCOPY /* file.sql.39 change */ menu_table
502 ) IS
503
504 cnt number := 0;
505 p_function_data function_data := null;
506
507 cursor get_sub_menus(p_mid number) is
508 --select sub_menu_id, prompt, description, function_id, menu_name, rowidtochar(a.rowid) menu_entry_rowid
509 select sub_menu_id, prompt, description, function_id, menu_name, rowidtochar(a.row_id) menu_entry_rowid
510 from fnd_menu_entries_vl a, fnd_menus b
511 where a.menu_id = p_mid and b.menu_id(+) = a.sub_menu_id
512 order by entry_sequence;
513
514 BEGIN
515
516 for c1 in get_sub_menus(p_menu_id) loop
517 cnt := cnt + 1;
518
519 -- if (c1.function_id > 0 ) then
520 -- get_function(c1.function_id, p_function_data);
521 -- end if;
522
523 p_menu_table(cnt).sub_menu_id := c1.sub_menu_id;
524 p_menu_table(cnt).prompt := c1.prompt;
525 p_menu_table(cnt).description := c1.description;
526 p_menu_table(cnt).function_id := c1.function_id;
527 p_menu_table(cnt).menu_name := c1.menu_name;
528 p_menu_table(cnt).menu_entry_rowid := c1.menu_entry_rowid;
529
530 if c1.function_id > 0 then
531 get_function(c1.function_id, p_function_data);
532 p_menu_table(cnt).func_web_host_name := p_function_data.web_host_name;
533 p_menu_table(cnt).func_web_agent_name := p_function_data.web_agent_name;
534 p_menu_table(cnt).func_web_html_call := p_function_data.web_html_call;
535 p_menu_table(cnt).func_web_encrypt_parameters := p_function_data.web_encrypt_parameters;
536 p_menu_table(cnt).func_web_secured := p_function_data.web_secured;
537 p_menu_table(cnt).func_web_icon := p_function_data.web_icon;
538 p_menu_table(cnt).func_function_id := p_function_data.function_id;
539 p_menu_table(cnt).func_function_name := p_function_data.function_name;
540 p_menu_table(cnt).func_application_id := p_function_data.application_id;
541 p_menu_table(cnt).func_creation_date := p_function_data.creation_date;
542 p_menu_table(cnt).func_type := p_function_data.type;
543 p_menu_table(cnt).func_user_function_name := p_function_data.user_function_name;
544 p_menu_table(cnt).func_description := p_function_data.description;
545 end if;
546 END LOOP;
547
548 END get_menu_entries;
549
550 PROCEDURE get_func_entries(
551 p_menu_id in number
552 , p_menu_table OUT NOCOPY /* file.sql.39 change */ menu_table
553 ) IS
554
555 cnt number := 0;
556 p_function_data function_data := null;
557
558 cursor get_sub_menus(p_mid number) is
559 -- select sub_menu_id, prompt, description, function_id, '' menu_name, rowidtochar(rowid) menu_entry_rowid
560 select sub_menu_id, prompt, description, function_id, '' menu_name, rowidtochar(row_id) menu_entry_rowid
561 from fnd_menu_entries_vl
562 where menu_id = p_mid
563 order by entry_sequence;
564
565 BEGIN
566
567 for c1 in get_sub_menus(p_menu_id)
568 LOOP
569 cnt := cnt + 1;
570
571 if (c1.function_id > 0 ) then
572 get_function(c1.function_id, p_function_data);
573 end if;
574
575 p_menu_table(cnt).sub_menu_id := c1.sub_menu_id;
576 p_menu_table(cnt).prompt := c1.prompt;
577 p_menu_table(cnt).description := c1.description;
578 p_menu_table(cnt).function_id := c1.function_id;
579 p_menu_table(cnt).menu_name := c1.menu_name;
580 p_menu_table(cnt).menu_entry_rowid := c1.menu_entry_rowid;
581 p_menu_table(cnt).func_web_host_name := p_function_data.web_host_name;
582 p_menu_table(cnt).func_web_agent_name := p_function_data.web_agent_name;
583 p_menu_table(cnt).func_web_html_call := p_function_data.web_html_call;
584 p_menu_table(cnt).func_web_encrypt_parameters := p_function_data.web_encrypt_parameters;
585 p_menu_table(cnt).func_web_secured := p_function_data.web_secured;
586 p_menu_table(cnt).func_web_icon := p_function_data.web_icon;
587 p_menu_table(cnt).func_function_id := p_function_data.function_id;
588 p_menu_table(cnt).func_function_name := p_function_data.function_name;
589 p_menu_table(cnt).func_application_id := p_function_data.application_id;
590 p_menu_table(cnt).func_creation_date := p_function_data.creation_date;
591 p_menu_table(cnt).func_type := p_function_data.type;
592 p_menu_table(cnt).func_user_function_name := p_function_data.user_function_name;
593 p_menu_table(cnt).func_description := p_function_data.description;
594
595 END LOOP;
596
597 END get_func_entries;
598
599
600 FUNCTION get_function_name_tl(
601 p_lang varchar2,
602 p_function_id number) return varchar2 is
603 t_lang varchar2(25); -- probably '5' was enuf, I don't know the max
604 l_function_name varchar2(80);
605
606 -- the old SELECT statement
607 -- cursor get_func_name(p_func_id number) is
608 -- select user_function_name
609 -- from fnd_form_functions_vl
610 -- where function_id = p_func_id;
611 cursor get_func_name(p_func_id number, pp_lang varchar2) is
612 SELECT
613 T.USER_FUNCTION_NAME
614 FROM FND_FORM_FUNCTIONS_TL T, FND_FORM_FUNCTIONS B
615 WHERE B.FUNCTION_ID = T.FUNCTION_ID AND T.LANGUAGE = pp_lang and
616 b.function_id = p_func_id;
617 begin
618 if p_lang is null then
619 select userenv('lang') into t_lang from dual;
620 else
621 t_lang := p_lang;
622 end if;
623
624 open get_func_name(p_function_id, t_lang);
625 fetch get_func_name into l_function_name;
626 close get_func_name;
627
628 return l_function_name;
629 end get_function_name_tl;
630
631 FUNCTION get_function_name(
632 p_function_id in number
633 ) RETURN VARCHAR2
634 IS
635 l_function_name varchar2(80);
636
637 cursor get_func_name(p_func_id number) is
638 select user_function_name
639 from fnd_form_functions_vl
640 where function_id = p_func_id;
641
642 BEGIN
643 open get_func_name(p_function_id);
644 fetch get_func_name into l_function_name;
645 close get_func_name;
646
647 return l_function_name;
648
649 END get_function_name;
650
651
652 FUNCTION get_menu_name_tl(
653 p_lang varchar2,
654 p_menu_row_id varchar2) return varchar2 is
655 t_lang varchar2(25); -- probably '5' was enuf, I don't know the max
656 l_prompt_name varchar2(30);
657 -- here's the query, before we parameterized it by language
658 -- cursor get_menu_name(p_row_id varchar2) is
659 -- select prompt
660 -- from fnd_menu_entries_vl
661 -- where rowid = chartorowid(p_row_id);
662 cursor get_menu_name(p_row_id varchar2, pp_lang varchar2) is
663 SELECT T.PROMPT
664 FROM FND_MENU_ENTRIES_TL T, FND_MENU_ENTRIES B
665 WHERE B.MENU_ID = T.MENU_ID AND B.ENTRY_SEQUENCE = T.ENTRY_SEQUENCE
666 AND T.LANGUAGE = pp_lang and
667 b.rowid = chartorowid(p_row_id);
668 begin
669 if p_lang is null then
670 select userenv('lang') into t_lang from dual;
671 else
672 t_lang := p_lang;
673 end if;
674
675 open get_menu_name(p_menu_row_id, t_lang);
676 fetch get_menu_name into l_prompt_name;
677 close get_menu_name;
678
679 return l_prompt_name;
680 end get_menu_name_tl;
681
682 FUNCTION get_menu_name(
683 p_menu_row_id in varchar2
684 ) RETURN VARCHAR2
685 IS
686 l_prompt_name varchar2(30);
687
688 cursor get_menu_name(p_row_id varchar2) is
689 select prompt
690 from fnd_menu_entries_vl
691 --where rowid = chartorowid(p_row_id);
692 where row_id = chartorowid(p_row_id);
693
694 BEGIN
695 open get_menu_name(p_menu_row_id);
696 fetch get_menu_name into l_prompt_name;
697 close get_menu_name;
698
699 return l_prompt_name;
700
701 END get_menu_name;
702
703 function get_root_menu_name_tl(
704 p_lang varchar2,
705 p_menu_id in number) return varchar2 is
706 t_lang varchar2(25); -- probably '5' was enuf, I don't know the max
707 l_prompt_name varchar2(80);
708 -- here's the query, before we parameterized it by language
709 -- cursor get_root_name(p_menu_id number) is
710 -- select user_menu_name
711 -- from fnd_menus_vl
712 -- where menu_id = p_menu_id;
713 cursor get_root_name(p_menu_id number, pp_lang varchar2) is
714 SELECT T.USER_MENU_NAME
715 FROM FND_MENUS_TL T, FND_MENUS B
716 WHERE B.MENU_ID = T.MENU_ID AND T.LANGUAGE = pp_lang and
717 b.menu_id = p_menu_id;
718 begin
719 open get_root_name(p_menu_id, t_lang);
720 fetch get_root_name into l_prompt_name;
721 close get_root_name;
722
723 return l_prompt_name;
724 end get_root_menu_name_tl;
725
726 FUNCTION get_root_menu_name(
727 p_menu_id in number
728 ) RETURN VARCHAR2
729 IS
730 l_prompt_name varchar2(80);
731
732 cursor get_root_name(p_menu_id number) is
733 select user_menu_name
734 from fnd_menus_vl
735 where menu_id = p_menu_id;
736
737 BEGIN
738 open get_root_name(p_menu_id);
739 fetch get_root_name into l_prompt_name;
740 close get_root_name;
741
742 return l_prompt_name;
743
744 END get_root_menu_name;
745
746 end jtf_menu_pub;