[Home] [Help]
PACKAGE BODY: APPS.BIS_MENU_ENTRIES_PUB
Source
1 PACKAGE BODY BIS_MENU_ENTRIES_PUB as
2 /* $Header: BISPMNEB.pls 120.3 2006/07/12 09:53:50 ankgoel noship $ */
3 ----------------------------------------------------------------------------
4 -- PACKAGE: BIS_MENU_ENTRIES_PUB --
5 -- --
6 -- DESCRIPTION: Private package that calls the FND packages to --
7 -- insert records in the FND tables. --
8 --
9 -- --
10 -- MODIFICATIONS --
11 -- Date User Modification
12 -- XX-XXX-XX XXXXXXXX Modifications made, which procedures changed & --
13 -- list bug number, if fixing a bug. --
14 -- --
15 -- 11/21/01 mdamle Initial creation --
16 -- 12/25/03 mdamle Added a generic routine to attach function to --
17 -- menus --
18 -- 06/24/04 bewong Added a procedure to update the prompt of --
19 -- the function --
20 -- 07/14/04 ppalpart Added a procedures to delete roles --
21 -- 07/19/04 ppalpart Added a procedure to delete roles taking --
22 -- only Menu_Id --
23 -- 03/01/05 mdamle Added UPDATE_ROW, LOCK_ROW --
24 -- 03/21/05 ankagarw bug#4235732 - changing count(*) to count(1) --
25 -- 11/03/05 rpenneru bug#4698198 Added SUBMIT_COMPILE function to --
26 -- submit menu concurrent request --
27 -- 26/06/06 hengliu bug#5091570 Update menu row when menu entries --
28 -- have been changed --
29 -- 12/07/06 ankgoel Bug#5383908 Update menu row in UPDATE_ROW API --
30 ----------------------------------------------------------------------------
31
32 -- Defaults
33 X_WEB_SECURED constant varchar2(1) := 'N';
34 X_WEB_ENCRYPT_PARAMETERS constant varchar2(1) := 'N';
35
36 procedure INSERT_ROW (
37 X_ROWID in out NOCOPY VARCHAR2,
38 X_USER_ID in NUMBER,
39 X_MENU_ID in NUMBER,
40 X_FUNCTION_ID in NUMBER,
41 X_PROMPT in VARCHAR2,
42 X_DESCRIPTION in VARCHAR2) is
43
44 l_new_entry_sequence NUMBER;
45 l_result VARCHAR2(1);
46 l_return_status varchar2(1);
47 l_msg_count number;
48 l_msg_data varchar2(200);
49
50 begin
51
52 begin
53 select nvl(max(entry_sequence), 0) + 1
54 into l_new_entry_sequence
55 from fnd_menu_entries
56 where menu_id = X_MENU_ID;
57 exception
58 when no_data_found then l_new_entry_sequence :=1;
59 end;
60
61 FND_MENU_ENTRIES_PKG.INSERT_ROW(
62 X_ROWID => X_ROWID,
63 X_MENU_ID => X_MENU_ID,
64 X_ENTRY_SEQUENCE => l_new_entry_sequence,
65 X_SUB_MENU_ID => null,
66 X_FUNCTION_ID => X_FUNCTION_ID ,
67 X_GRANT_FLAG => null,
68 X_PROMPT => X_PROMPT,
69 X_DESCRIPTION => X_DESCRIPTION,
70 X_CREATION_DATE => sysdate,
71 X_CREATED_BY => X_USER_ID,
72 X_LAST_UPDATE_DATE => sysdate,
73 X_LAST_UPDATED_BY => X_USER_ID,
74 X_LAST_UPDATE_LOGIN => X_USER_ID);
75
76 BIS_FND_MENUS_PUB.UPDATE_ROW(
77 P_MENU_ID => X_MENU_ID,
78 X_RETURN_STATUS => l_return_status,
79 X_MSG_COUNT => l_msg_count,
80 X_MSG_DATA => l_msg_data);
81
82 l_result := BIS_MENU_ENTRIES_PUB.SUBMIT_COMPILE;
83
84 end INSERT_ROW;
85
86 procedure INSERT_ROW (
87 X_MENU_ID in NUMBER,
88 X_ENTRY_SEQUENCE in NUMBER,
89 X_SUB_MENU_ID in NUMBER,
90 X_FUNCTION_ID in NUMBER,
91 X_GRANT_FLAG in VARCHAR2,
92 X_PROMPT in VARCHAR2,
93 X_DESCRIPTION in VARCHAR2,
94 x_return_status OUT NOCOPY VARCHAR2,
95 x_msg_count OUT NOCOPY NUMBER,
96 x_msg_data OUT NOCOPY VARCHAR2) is
97
98 l_rowid varchar2(240);
99 l_result VARCHAR2(1);
100 begin
101
102 fnd_msg_pub.initialize;
103
104 FND_MENU_ENTRIES_PKG.INSERT_ROW(
105 X_ROWID => l_ROWID,
106 X_MENU_ID => X_MENU_ID,
107 X_ENTRY_SEQUENCE => X_ENTRY_SEQUENCE,
108 X_SUB_MENU_ID => X_SUB_MENU_ID,
109 X_FUNCTION_ID => X_FUNCTION_ID ,
110 X_GRANT_FLAG => X_GRANT_FLAG,
111 X_PROMPT => X_PROMPT,
112 X_DESCRIPTION => X_DESCRIPTION,
113 X_CREATION_DATE => sysdate,
114 X_CREATED_BY => fnd_global.user_id,
115 X_LAST_UPDATE_DATE => sysdate,
116 X_LAST_UPDATED_BY => fnd_global.user_id,
117 X_LAST_UPDATE_LOGIN => fnd_global.user_id);
118
119 BIS_FND_MENUS_PUB.UPDATE_ROW(
120 P_MENU_ID => X_MENU_ID,
121 X_RETURN_STATUS => X_RETURN_STATUS,
122 X_MSG_COUNT => X_MSG_COUNT,
123 X_MSG_DATA => X_MSG_DATA);
124
125 l_result := BIS_MENU_ENTRIES_PUB.SUBMIT_COMPILE;
126
127 EXCEPTION
128 WHEN FND_API.G_EXC_ERROR THEN
129 x_return_status := FND_API.G_RET_STS_ERROR;
130 FND_MSG_PUB.Count_And_Get( p_encoded => 'F' ,p_count => x_msg_count
131 ,p_data => x_msg_data);
132 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
133 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
134 FND_MSG_PUB.Count_And_Get( p_encoded => 'F' ,p_count => x_msg_count
135 ,p_data => x_msg_data);
136 WHEN NO_DATA_FOUND THEN
137 x_return_status := FND_API.G_RET_STS_ERROR;
138 FND_MSG_PUB.Count_And_Get( p_encoded => 'F' ,p_count => x_msg_count
139 ,p_data => x_msg_data);
140 WHEN OTHERS THEN
141 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
142 FND_MSG_PUB.Count_And_Get( p_encoded => 'F' ,p_count => x_msg_count
143 ,p_data => x_msg_data);
144 if (x_msg_data is null) then
145 x_msg_data := SQLERRM;
146 end if;
147
148 end INSERT_ROW;
149
150 procedure UPDATE_ROW (
151 X_MENU_ID in NUMBER,
152 X_ENTRY_SEQUENCE in NUMBER,
153 X_SUB_MENU_ID in NUMBER,
154 X_FUNCTION_ID in NUMBER,
155 X_GRANT_FLAG in VARCHAR2,
156 X_PROMPT in VARCHAR2,
157 X_DESCRIPTION in VARCHAR2,
158 x_return_status OUT NOCOPY VARCHAR2,
159 x_msg_count OUT NOCOPY NUMBER,
160 x_msg_data OUT NOCOPY VARCHAR2) is
161
162 l_result VARCHAR2(1);
163 begin
164
165 fnd_msg_pub.initialize;
166
167 FND_MENU_ENTRIES_PKG.UPDATE_ROW(
168 X_MENU_ID => X_MENU_ID,
169 X_ENTRY_SEQUENCE => X_ENTRY_SEQUENCE,
170 X_SUB_MENU_ID => X_SUB_MENU_ID,
171 X_FUNCTION_ID => X_FUNCTION_ID ,
172 X_GRANT_FLAG => X_GRANT_FLAG,
173 X_PROMPT => X_PROMPT,
174 X_DESCRIPTION => X_DESCRIPTION,
175 X_LAST_UPDATE_DATE => sysdate,
176 X_LAST_UPDATED_BY => fnd_global.user_id,
177 X_LAST_UPDATE_LOGIN => fnd_global.user_id);
178
179 BIS_FND_MENUS_PUB.UPDATE_ROW(
180 P_MENU_ID => X_MENU_ID,
181 X_RETURN_STATUS => X_RETURN_STATUS,
182 X_MSG_COUNT => X_MSG_COUNT,
183 X_MSG_DATA => X_MSG_DATA);
184
185 l_result := BIS_MENU_ENTRIES_PUB.SUBMIT_COMPILE;
186
187 EXCEPTION
188 WHEN FND_API.G_EXC_ERROR THEN
189 x_return_status := FND_API.G_RET_STS_ERROR;
190 FND_MSG_PUB.Count_And_Get( p_encoded => 'F' ,p_count => x_msg_count
191 ,p_data => x_msg_data);
192 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
193 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
194 FND_MSG_PUB.Count_And_Get( p_encoded => 'F' ,p_count => x_msg_count
195 ,p_data => x_msg_data);
196 WHEN NO_DATA_FOUND THEN
197 x_return_status := FND_API.G_RET_STS_ERROR;
198 FND_MSG_PUB.Count_And_Get( p_encoded => 'F' ,p_count => x_msg_count
199 ,p_data => x_msg_data);
200 WHEN OTHERS THEN
201 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
202 FND_MSG_PUB.Count_And_Get( p_encoded => 'F' ,p_count => x_msg_count
203 ,p_data => x_msg_data);
204 if (x_msg_data is null) then
205 x_msg_data := SQLERRM;
206 end if;
207
208 end UPDATE_ROW;
209
210
211 procedure UPDATE_PROMPT (
212 X_USER_ID in NUMBER,
213 X_MENU_ID in NUMBER,
214 X_OLD_ENTRY_SEQUENCE in NUMBER,
215 X_FUNCTION_ID in NUMBER,
216 X_PROMPT in VARCHAR2) is
217
218 l_des fnd_menu_entries_vl.description%TYPE;
219 CURSOR c1 is
220 SELECT description
221 FROM fnd_menu_entries_vl f
222 WHERE f.menu_id = X_MENU_ID
223 AND f.entry_sequence = X_OLD_ENTRY_SEQUENCE;
224
225 begin
226 -- get description from query
227 OPEN c1;
228 FETCH c1 INTO l_des;
229 CLOSE c1;
230
231 FND_MENU_ENTRIES_PKG.UPDATE_ROW(
232 X_MENU_ID => X_MENU_ID,
233 X_ENTRY_SEQUENCE => X_OLD_ENTRY_SEQUENCE,
234 X_SUB_MENU_ID => null,
235 X_FUNCTION_ID => X_FUNCTION_ID ,
236 X_GRANT_FLAG => null,
237 X_PROMPT => X_PROMPT,
238 X_DESCRIPTION => l_des,
239 X_LAST_UPDATE_DATE => sysdate,
240 X_LAST_UPDATED_BY => X_USER_ID,
241 X_LAST_UPDATE_LOGIN => X_USER_ID);
242
243 end UPDATE_PROMPT;
244
245 procedure DELETE_ROW (
246 X_MENU_ID in NUMBER,
247 X_ENTRY_SEQUENCE in NUMBER,
248 x_return_status OUT NOCOPY VARCHAR2,
249 x_msg_count OUT NOCOPY NUMBER,
250 x_msg_data OUT NOCOPY VARCHAR2) is
251
252 l_result VARCHAR2(1);
253 begin
254
255 fnd_msg_pub.initialize;
256
257 FND_MENU_ENTRIES_PKG.DELETE_ROW(
258 X_MENU_ID => X_MENU_ID,
259 X_ENTRY_SEQUENCE => X_ENTRY_SEQUENCE);
260
261 BIS_FND_MENUS_PUB.UPDATE_ROW(
262 P_MENU_ID => X_MENU_ID,
263 X_RETURN_STATUS => X_RETURN_STATUS,
264 X_MSG_COUNT => X_MSG_COUNT,
265 X_MSG_DATA => X_MSG_DATA);
266
267 l_result := BIS_MENU_ENTRIES_PUB.SUBMIT_COMPILE;
268
269 EXCEPTION
270 WHEN FND_API.G_EXC_ERROR THEN
271 x_return_status := FND_API.G_RET_STS_ERROR;
272 FND_MSG_PUB.Count_And_Get( p_encoded => 'F' ,p_count => x_msg_count
273 ,p_data => x_msg_data);
274 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
275 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
276 FND_MSG_PUB.Count_And_Get( p_encoded => 'F' ,p_count => x_msg_count
277 ,p_data => x_msg_data);
278 WHEN NO_DATA_FOUND THEN
279 x_return_status := FND_API.G_RET_STS_ERROR;
280 FND_MSG_PUB.Count_And_Get( p_encoded => 'F' ,p_count => x_msg_count
281 ,p_data => x_msg_data);
282 WHEN OTHERS THEN
283 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
284 FND_MSG_PUB.Count_And_Get( p_encoded => 'F' ,p_count => x_msg_count
285 ,p_data => x_msg_data);
286 if (x_msg_data is null) then
287 x_msg_data := SQLERRM;
288 end if;
289 end DELETE_ROW;
290
291 procedure DELETE_ROW (
292 X_MENU_ID in NUMBER,
293 x_return_status OUT NOCOPY VARCHAR2,
294 x_msg_count OUT NOCOPY NUMBER,
295 x_msg_data OUT NOCOPY VARCHAR2) is
296
297 cursor entry_sequence_cursor is
298 select entry_sequence
299 from fnd_menu_entries
300 where menu_id = X_MENU_ID;
301 l_result VARCHAR2(1);
302 begin
303
304 fnd_msg_pub.initialize;
305
306 for entry_seq_cursor in entry_sequence_cursor loop
307
308 FND_MENU_ENTRIES_PKG.DELETE_ROW(
309 X_MENU_ID => X_MENU_ID,
310 X_ENTRY_SEQUENCE => entry_seq_cursor.entry_sequence);
311 end loop;
312
313 BIS_FND_MENUS_PUB.UPDATE_ROW(
314 P_MENU_ID => X_MENU_ID,
315 X_RETURN_STATUS => X_RETURN_STATUS,
316 X_MSG_COUNT => X_MSG_COUNT,
317 X_MSG_DATA => X_MSG_DATA);
318
319 l_result := BIS_MENU_ENTRIES_PUB.SUBMIT_COMPILE;
320
321 EXCEPTION
322 WHEN FND_API.G_EXC_ERROR THEN
323 x_return_status := FND_API.G_RET_STS_ERROR;
324 FND_MSG_PUB.Count_And_Get( p_encoded => 'F' ,p_count => x_msg_count
325 ,p_data => x_msg_data);
326 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
327 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
328 FND_MSG_PUB.Count_And_Get( p_encoded => 'F' ,p_count => x_msg_count
329 ,p_data => x_msg_data);
330 WHEN NO_DATA_FOUND THEN
331 x_return_status := FND_API.G_RET_STS_ERROR;
332 FND_MSG_PUB.Count_And_Get( p_encoded => 'F' ,p_count => x_msg_count
333 ,p_data => x_msg_data);
334 WHEN OTHERS THEN
335 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
336 FND_MSG_PUB.Count_And_Get( p_encoded => 'F' ,p_count => x_msg_count
337 ,p_data => x_msg_data);
338 if (x_msg_data is null) then
339 x_msg_data := SQLERRM;
340 end if;
341 end DELETE_ROW;
342
343
344 procedure AttachFunctionToMenus(
345 p_function_id IN NUMBER
346 ,p_menu_ids IN FND_TABLE_OF_NUMBER
347 ,x_return_status OUT NOCOPY VARCHAR2
348 ,x_msg_count OUT NOCOPY NUMBER
349 ,x_msg_data OUT NOCOPY VARCHAR2
350 ) is
351
352 l_count NUMBER;
353 l_prompt VARCHAR2(80);
354 l_row_id VARCHAR2(30);
355 begin
356
357 fnd_msg_pub.initialize;
358
359 for i in 1..p_menu_ids.count loop
360 -- Check if menu attachment already exists, then leave it alone
361 select count(1) into l_count
362 from fnd_menu_entries
363 where function_id = p_function_id
364 and menu_id = p_menu_ids(i);
365
366 if (l_count = 0) then
367 -- Attach the menu
368
369 select user_function_name into l_prompt
370 from fnd_form_functions_vl
371 where function_id = p_function_id;
372
373 INSERT_ROW (
374 X_ROWID => l_row_id,
375 X_USER_ID => fnd_global.user_id,
376 X_MENU_ID => p_menu_ids(i),
377 X_FUNCTION_ID => p_function_id,
378 X_PROMPT => l_prompt,
379 X_DESCRIPTION => l_prompt);
380 end if;
381
382 end loop;
383
384 EXCEPTION
385 WHEN FND_API.G_EXC_ERROR THEN
386 x_return_status := FND_API.G_RET_STS_ERROR;
387 FND_MSG_PUB.Count_And_Get( p_encoded => 'F' ,p_count => x_msg_count
388 ,p_data => x_msg_data);
389 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
390 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
391 FND_MSG_PUB.Count_And_Get( p_encoded => 'F' ,p_count => x_msg_count
392 ,p_data => x_msg_data);
393 WHEN NO_DATA_FOUND THEN
394 x_return_status := FND_API.G_RET_STS_ERROR;
395 FND_MSG_PUB.Count_And_Get( p_encoded => 'F' ,p_count => x_msg_count
396 ,p_data => x_msg_data);
397 WHEN OTHERS THEN
398 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
399 FND_MSG_PUB.Count_And_Get( p_encoded => 'F' ,p_count => x_msg_count
400 ,p_data => x_msg_data);
401 if (x_msg_data is null) then
402 x_msg_data := SQLERRM;
403 end if;
404 end AttachFunctionToMenus;
405
406
407 procedure AttachFunctionsToMenu(
408 p_menu_id IN NUMBER
409 ,p_function_ids IN FND_TABLE_OF_NUMBER
410 ,x_return_status OUT NOCOPY VARCHAR2
411 ,x_msg_count OUT NOCOPY NUMBER
412 ,x_msg_data OUT NOCOPY VARCHAR2
413 ) is
414
415 l_count NUMBER;
416 l_prompt VARCHAR2(80);
417 l_row_id VARCHAR2(30);
418 begin
419
420 fnd_msg_pub.initialize;
421
422 for i in 1..p_function_ids.count loop
423 -- Check if function is added to the menu, then leave it alone
424 select count(1) into l_count
425 from fnd_menu_entries
426 where function_id = p_function_ids(i)
427 and menu_id = p_menu_id;
428
429 if (l_count = 0) then
430 -- Attach the function
431
432 select user_function_name into l_prompt
433 from fnd_form_functions_vl
434 where function_id = p_function_ids(i);
435
436 INSERT_ROW (
437 X_ROWID => l_row_id,
438 X_USER_ID => fnd_global.user_id,
439 X_MENU_ID => p_menu_id,
440 X_FUNCTION_ID => p_function_ids(i),
441 X_PROMPT => l_prompt,
442 X_DESCRIPTION => l_prompt);
443 end if;
444
445 end loop;
446
447 EXCEPTION
448 WHEN FND_API.G_EXC_ERROR THEN
449 x_return_status := FND_API.G_RET_STS_ERROR;
450 FND_MSG_PUB.Count_And_Get( p_encoded => 'F' ,p_count => x_msg_count
451 ,p_data => x_msg_data);
452 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
453 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
454 FND_MSG_PUB.Count_And_Get( p_encoded => 'F' ,p_count => x_msg_count
455 ,p_data => x_msg_data);
456 WHEN NO_DATA_FOUND THEN
457 x_return_status := FND_API.G_RET_STS_ERROR;
458 FND_MSG_PUB.Count_And_Get( p_encoded => 'F' ,p_count => x_msg_count
459 ,p_data => x_msg_data);
460 WHEN OTHERS THEN
461 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
462 FND_MSG_PUB.Count_And_Get( p_encoded => 'F' ,p_count => x_msg_count
463 ,p_data => x_msg_data);
464 if (x_msg_data is null) then
465 x_msg_data := SQLERRM;
466 end if;
467 end AttachFunctionsToMenu;
468
469 procedure DeleteFunctionsFromMenu(
470 p_menu_id IN NUMBER
471 ,p_function_ids IN FND_TABLE_OF_NUMBER
472 ,x_return_status OUT NOCOPY VARCHAR2
473 ,x_msg_count OUT NOCOPY NUMBER
474 ,x_msg_data OUT NOCOPY VARCHAR2
475 )
476
477 is
478
479 cursor entry_seq_cursor(p_function_id NUMBER) is
480 select entry_sequence
481 from fnd_menu_entries
482 where function_id = p_function_id
483 and menu_id = p_menu_id;
484
485 p_return_status VARCHAR2(40);
486 p_msg_count NUMBER;
487 p_msg_data VARCHAR2(40);
488
489 begin
490
491 fnd_msg_pub.initialize;
492
493 for i in 1..p_function_ids.count loop
494
495 for ent_seq_cur in entry_seq_cursor(p_function_ids(i)) loop
496
497 DELETE_ROW (X_MENU_ID => p_menu_id,
498 X_ENTRY_SEQUENCE => ent_seq_cur.entry_sequence,
499 x_return_status => p_return_status,
500 x_msg_count => p_msg_count,
501 x_msg_data => p_msg_data);
502
503 end loop;
504
505 end loop;
506
507 EXCEPTION
508 WHEN FND_API.G_EXC_ERROR THEN
509 x_return_status := FND_API.G_RET_STS_ERROR;
510 FND_MSG_PUB.Count_And_Get( p_encoded => 'F' ,p_count => x_msg_count
511 ,p_data => x_msg_data);
512 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
513 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
514 FND_MSG_PUB.Count_And_Get( p_encoded => 'F' ,p_count => x_msg_count
515 ,p_data => x_msg_data);
516 WHEN NO_DATA_FOUND THEN
517 x_return_status := FND_API.G_RET_STS_ERROR;
518 FND_MSG_PUB.Count_And_Get( p_encoded => 'F' ,p_count => x_msg_count
519 ,p_data => x_msg_data);
520 WHEN OTHERS THEN
521 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
522 FND_MSG_PUB.Count_And_Get( p_encoded => 'F' ,p_count => x_msg_count
523 ,p_data => x_msg_data);
524 if (x_msg_data is null) then
525 x_msg_data := SQLERRM;
526 end if;
527 end DeleteFunctionsFromMenu;
528
529 procedure DeleteFunctionFromMenus(
530 p_function_id IN NUMBER
531 ,p_menu_ids IN FND_TABLE_OF_NUMBER
532 ,x_return_status OUT NOCOPY VARCHAR2
533 ,x_msg_count OUT NOCOPY NUMBER
534 ,x_msg_data OUT NOCOPY VARCHAR2
535 ) is
536
537 cursor entry_seq_cursor(p_menu_id NUMBER) is
538 select entry_sequence
539 from fnd_menu_entries
540 where function_id = p_function_id
541 and menu_id = p_menu_id;
542
543 p_return_status VARCHAR2(40);
544 p_msg_count NUMBER;
545 p_msg_data VARCHAR2(40);
546
547 begin
548
549 fnd_msg_pub.initialize;
550
551 for i in 1..p_menu_ids.count loop
552
553 for ent_seq_cur in entry_seq_cursor(p_menu_ids(i)) loop
554
555 DELETE_ROW (X_MENU_ID => p_menu_ids(i),
556 X_ENTRY_SEQUENCE => ent_seq_cur.entry_sequence,
557 x_return_status => p_return_status,
558 x_msg_count => p_msg_count,
559 x_msg_data => p_msg_data);
560
561 end loop;
562
563 end loop;
564
565 EXCEPTION
566 WHEN FND_API.G_EXC_ERROR THEN
567 x_return_status := FND_API.G_RET_STS_ERROR;
568 FND_MSG_PUB.Count_And_Get( p_encoded => 'F' ,p_count => x_msg_count
569 ,p_data => x_msg_data);
570 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
571 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
572 FND_MSG_PUB.Count_And_Get( p_encoded => 'F' ,p_count => x_msg_count
573 ,p_data => x_msg_data);
574 WHEN NO_DATA_FOUND THEN
575 x_return_status := FND_API.G_RET_STS_ERROR;
576 FND_MSG_PUB.Count_And_Get( p_encoded => 'F' ,p_count => x_msg_count
577 ,p_data => x_msg_data);
578 WHEN OTHERS THEN
579 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
580 FND_MSG_PUB.Count_And_Get( p_encoded => 'F' ,p_count => x_msg_count
581 ,p_data => x_msg_data);
582 if (x_msg_data is null) then
583 x_msg_data := SQLERRM;
584 end if;
585 end DeleteFunctionFromMenus;
586
587
588 PROCEDURE LOCK_ROW
589 ( p_menu_id IN NUMBER
590 , p_entry_sequence IN NUMBER
591 , p_last_update_date IN DATE
592 ) IS
593
594 l_last_update_date date;
595
596 cursor cMenuEntry is
597 select last_update_date
598 from fnd_menu_entries
599 where menu_id = p_menu_id
600 and entry_sequence = p_entry_sequence
601 for update of menu_id, entry_sequence nowait;
602
603 BEGIN
604
605 fnd_msg_pub.initialize;
606
607 SAVEPOINT SP_LOCK_ROW;
608
609 IF cMenuEntry%ISOPEN THEN
610 CLOSE cMenuEntry;
611 END IF;
612 OPEN cMenuEntry;
613 FETCH cMenuEntry INTO l_last_update_date;
614
615 if (cMenuEntry%notfound) then
616 FND_MESSAGE.SET_NAME('BIS','BIS_MENU_ENTRY_DELETED_ERROR');
617 FND_MSG_PUB.ADD;
618 RAISE FND_API.G_EXC_ERROR;
619 end if;
620
621 if p_last_update_date is not null then
622 if p_last_update_date <> l_last_update_date then
623 FND_MESSAGE.SET_NAME('BIS','BIS_MENU_ENTRY_CHANGED_ERROR');
624 FND_MSG_PUB.ADD;
625 RAISE FND_API.G_EXC_ERROR;
626 end if;
627 end if;
628
629 rollback to SP_LOCK_ROW;
630 CLOSE cMenuEntry;
631
632 EXCEPTION
633 WHEN FND_API.G_EXC_ERROR THEN NULL;
634 WHEN OTHERS THEN
635 close cMenuEntry;
636 rollback to SP_LOCK_ROW;
637 FND_MESSAGE.SET_NAME('BIS','BIS_MENU_ENTRY_LOCKED_ERROR');
638 FND_MSG_PUB.ADD;
639 RAISE FND_API.G_EXC_ERROR;
640 END LOCK_ROW;
641
642
643 /*
644 * Function used to submit the concurrent request to compile
645 * Menus after delete, insert or update
646 *
647 * This function will store the application context and
648 * restore back once the request is submitted.
649 */
650
651 FUNCTION submit_compile RETURN VARCHAR2
652 IS
653 l_result VARCHAR2(1);
654 l_userId NUMBER;
655 l_respId NUMBER;
656 l_respAppId NUMBER;
657 BEGIN
658
659 /* Store the FND_GLOBAL user_id, resp_id and appl_id. to
660 restore back after SUBMIT_COMPILE */
661
662 l_userId := FND_GLOBAL.USER_ID;
663 l_respId := FND_GLOBAL.RESP_ID;
664 l_respAppId := FND_GLOBAL.RESP_APPL_ID;
665
666 l_result := FND_MENU_ENTRIES_PKG.SUBMIT_COMPILE;
667
668 /** restore the application context back */
669
670 FND_GLOBAL.APPS_INITIALIZE(l_userId,l_respId,l_respAppId);
671
672 RETURN(l_result);
673 END submit_compile;
674
675
676 END BIS_MENU_ENTRIES_PUB;