[Home] [Help]
PACKAGE BODY: APPS.BIS_FND_MENUS_PUB
Source
1 PACKAGE BODY BIS_FND_MENUS_PUB as
2 /* $Header: BISPFMNB.pls 120.0 2005/05/31 18:23:34 appldev noship $ */
3 ----------------------------------------------------------------------------
4 -- PACKAGE: BIS_FND_MENUS_PUB --
5 -- --
6 -- DESCRIPTION: Private package that calls the FND packages to --
7 -- insert records in the FND tables. --
8 -- --
9 -- MODIFICATIONS --
10 -- Date User Modification --
11 -- 03/05/04 nbarik Initial creation --
12 -- 07/19/04 ppalpart Create method DELETE_ROW_MENU_MENUENTRIES --
13 -- 03/01/05 mdamle Added LOCK_ROW, X_MENU_ID In/Out parameter --
14 -- Added validation --
15 -- 03/21/05 ankagarw bug#4235732 - changing count(*) to count(1) --
16 -- 04/5/05 mdamle Check for already deleted row in delete_row --
17 -- 19-MAY-2005 visuri GSCC Issues bug 4363854 --
18 ----------------------------------------------------------------------------
19
20 procedure INSERT_ROW (
21 p_MENU_NAME in VARCHAR2
22 ,p_USER_MENU_NAME in VARCHAR2
23 ,p_TYPE in VARCHAR2 := NULL
24 ,p_DESCRIPTION in VARCHAR2 := NULL
25 ,x_MENU_ID in OUT NOCOPY NUMBER
26 ,x_return_status OUT NOCOPY VARCHAR2
27 ,x_msg_count OUT NOCOPY NUMBER
28 ,x_msg_data OUT NOCOPY VARCHAR2
29 ) is
30
31 l_rowid VARCHAR2(30);
32 l_new_menu_id NUMBER;
33 l_menu_name VARCHAR2(30);
34 l_user_menu_name VARCHAR2(80);
35
36 begin
37
38 fnd_msg_pub.initialize;
39
40 begin
41 select menu_name, user_menu_name
42 into l_menu_name, l_user_menu_name
43 from fnd_menus_vl
44 where menu_name = p_menu_name
45 or user_menu_name = p_user_menu_name;
46 exception
47 when others then null;
48 end;
49
50 if l_menu_name = p_menu_name then
51 FND_MESSAGE.SET_NAME('BIS','BIS_NAME_UNIQUE_ERR');
52 FND_MSG_PUB.ADD;
53 RAISE FND_API.G_EXC_ERROR;
54 end if;
55
56 if l_user_menu_name = p_user_menu_name then
57 FND_MESSAGE.SET_NAME('BIS','BIS_DISPLAY_NAME_UNIQUE_ERR');
58 FND_MSG_PUB.ADD;
59 RAISE FND_API.G_EXC_ERROR;
60 end if;
61
62 if X_MENU_ID IS NULL then
63 select FND_MENUS_S.NEXTVAL into l_new_menu_id from dual;
64 else
65 l_new_menu_id := X_MENU_ID;
66 end if;
67
68 FND_MENUS_PKG.INSERT_ROW(
69 X_ROWID => l_ROWID,
70 X_MENU_ID => l_new_menu_id,
71 X_MENU_NAME => upper(p_MENU_NAME),
72 X_USER_MENU_NAME => p_USER_MENU_NAME,
73 X_MENU_TYPE => p_TYPE,
74 X_DESCRIPTION => p_DESCRIPTION,
75 X_CREATION_DATE => sysdate,
76 X_CREATED_BY => fnd_global.user_id,
77 X_LAST_UPDATE_DATE => sysdate,
78 X_LAST_UPDATED_BY => fnd_global.user_id,
79 X_LAST_UPDATE_LOGIN => fnd_global.user_id);
80
81 if l_ROWID is not null then
82 X_MENU_ID := l_new_menu_id;
83 end if;
84
85 EXCEPTION
86 WHEN FND_API.G_EXC_ERROR THEN
87 x_return_status := FND_API.G_RET_STS_ERROR;
88 FND_MSG_PUB.Count_And_Get( p_encoded => 'F' ,p_count => x_msg_count
89 ,p_data => x_msg_data);
90
91 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
92 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
93 FND_MSG_PUB.Count_And_Get( p_encoded => 'F' ,p_count => x_msg_count
94 ,p_data => x_msg_data);
95
96 WHEN NO_DATA_FOUND THEN
97 x_return_status := FND_API.G_RET_STS_ERROR;
98 FND_MSG_PUB.Count_And_Get( p_encoded => 'F' ,p_count => x_msg_count
99 ,p_data => x_msg_data);
100 WHEN OTHERS THEN
101 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
102 FND_MSG_PUB.Count_And_Get( p_encoded => 'F' ,p_count => x_msg_count
103 ,p_data => x_msg_data);
104 if (x_msg_data is null) then
105 x_msg_data := SQLERRM;
106 end if;
107
108 end INSERT_ROW;
109
110 procedure UPDATE_ROW (
111 p_MENU_ID in NUMBER
112 ,p_USER_MENU_NAME in VARCHAR2 := BIS_COMMON_UTILS.G_DEF_CHAR
113 ,p_DESCRIPTION in VARCHAR2 := BIS_COMMON_UTILS.G_DEF_CHAR
114 ,x_return_status OUT NOCOPY VARCHAR2
115 ,x_msg_count OUT NOCOPY NUMBER
116 ,x_msg_data OUT NOCOPY VARCHAR2
117 ) is
118
119 l_fnd_menu_rec Fnd_Menu_Rec_Type;
120
121 cursor cFndMenu is
122 select menu_name,
123 type,
124 user_menu_name,
125 description
126 from fnd_menus_vl
127 where menu_id = p_MENU_ID;
128
129 l_count NUMBER;
130 begin
131 fnd_msg_pub.initialize;
132
133 if cFndMenu%ISOPEN then
134 CLOSE cFndMenu;
135 end if;
136
137 OPEN cFndMenu;
138 FETCH cFndMenu INTO
139 l_fnd_menu_rec.menu_name,
140 l_fnd_menu_rec.type,
141 l_fnd_menu_rec.user_menu_name,
142 l_fnd_menu_rec.description;
143 CLOSE cFndMenu;
144
145 if (p_user_menu_name <> BIS_COMMON_UTILS.G_DEF_CHAR) then
146 l_fnd_menu_rec.user_menu_name := p_user_menu_name;
147
148 select count(1)
149 into l_count
150 from fnd_menus_vl
151 where menu_id <> p_menu_id
152 and user_menu_name = p_user_menu_name;
153
154 if l_count > 0 then
155 FND_MESSAGE.SET_NAME('BIS','BIS_DISPLAY_NAME_UNIQUE_ERR');
156 FND_MSG_PUB.ADD;
157 RAISE FND_API.G_EXC_ERROR;
158 end if;
159 end if;
160
161 if (p_description <> BIS_COMMON_UTILS.G_DEF_CHAR) then
162 l_fnd_menu_rec.description := p_description;
163 end if;
164
165 FND_MENUS_PKG.UPDATE_ROW(
166 X_MENU_ID => p_MENU_ID,
167 X_MENU_NAME => l_fnd_menu_rec.menu_name,
168 X_USER_MENU_NAME => l_fnd_menu_rec.user_menu_name,
169 X_MENU_TYPE => l_fnd_menu_rec.type,
170 X_DESCRIPTION => l_fnd_menu_rec.description,
171 X_LAST_UPDATE_DATE => sysdate,
172 X_LAST_UPDATED_BY => fnd_global.user_id,
173 X_LAST_UPDATE_LOGIN => fnd_global.user_id
174 );
175 EXCEPTION
176 WHEN FND_API.G_EXC_ERROR THEN
177 if cFndMenu%ISOPEN then
178 CLOSE cFndMenu;
179 end if;
180 x_return_status := FND_API.G_RET_STS_ERROR;
181 FND_MSG_PUB.Count_And_Get( p_encoded => 'F' ,p_count => x_msg_count
182 ,p_data => x_msg_data);
183 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
184 if cFndMenu%ISOPEN then
185 CLOSE cFndMenu;
186 end if;
187 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
188 FND_MSG_PUB.Count_And_Get( p_encoded => 'F' ,p_count => x_msg_count
189 ,p_data => x_msg_data);
190 WHEN NO_DATA_FOUND THEN
191 if cFndMenu%ISOPEN then
192 CLOSE cFndMenu;
193 end if;
194 x_return_status := FND_API.G_RET_STS_ERROR;
195 FND_MSG_PUB.Count_And_Get( p_encoded => 'F' ,p_count => x_msg_count
196 ,p_data => x_msg_data);
197 WHEN OTHERS THEN
198 if cFndMenu%ISOPEN then
199 CLOSE cFndMenu;
200 end if;
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 VALIDATE_DELETE (
212 p_MENU_ID in VARCHAR2
213 ,x_return_status OUT NOCOPY VARCHAR2
214 ,x_msg_count OUT NOCOPY NUMBER
215 ,x_msg_data OUT NOCOPY VARCHAR2
216 ) IS
217
218 cursor cr_menu_usage is
219 select m.user_menu_name
220 from fnd_menus_vl m, fnd_menu_entries_vl me
221 where sub_menu_id = p_menu_id
222 and me.menu_id = m.menu_id;
223
224 cursor cr_resp_usage is
225 select responsibility_name
226 from fnd_responsibility_vl
227 where menu_id = p_menu_id;
228
229 BEGIN
230 fnd_msg_pub.initialize;
231
232 if cr_menu_usage%ISOPEN then
233 CLOSE cr_menu_usage;
234 end if;
235 if cr_resp_usage%ISOPEN then
236 CLOSE cr_resp_usage;
237 end if;
238
239 for c in cr_menu_usage loop
240 FND_MESSAGE.SET_NAME('FND','MENU-USED BY MENUS');
241 FND_MESSAGE.SET_TOKEN('MENU', c.user_menu_name);
242 FND_MSG_PUB.ADD;
243 RAISE FND_API.G_EXC_ERROR;
244 end loop;
245
246 for c in cr_resp_usage loop
247 FND_MESSAGE.SET_NAME('FND','FND-RESP CALLS MENU');
248 FND_MESSAGE.SET_TOKEN('RESPONSIBILITY', c.responsibility_name);
249 FND_MSG_PUB.ADD;
250 RAISE FND_API.G_EXC_ERROR;
251 end loop;
252
253 if cr_menu_usage%ISOPEN then
254 CLOSE cr_menu_usage;
255 end if;
256 if cr_resp_usage%ISOPEN then
257 CLOSE cr_resp_usage;
258 end if;
259
260 EXCEPTION
261 WHEN FND_API.G_EXC_ERROR THEN
262 if cr_menu_usage%ISOPEN then
263 CLOSE cr_menu_usage;
264 end if;
265 if cr_resp_usage%ISOPEN then
266 CLOSE cr_resp_usage;
267 end if;
268 x_return_status := FND_API.G_RET_STS_ERROR;
269 FND_MSG_PUB.Count_And_Get( p_encoded => 'F' ,p_count => x_msg_count
270 ,p_data => x_msg_data);
271 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
272 if cr_menu_usage%ISOPEN then
273 CLOSE cr_menu_usage;
274 end if;
275 if cr_resp_usage%ISOPEN then
276 CLOSE cr_resp_usage;
277 end if;
278 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
279 FND_MSG_PUB.Count_And_Get( p_encoded => 'F' ,p_count => x_msg_count
280 ,p_data => x_msg_data);
281 WHEN NO_DATA_FOUND THEN
282 if cr_menu_usage%ISOPEN then
283 CLOSE cr_menu_usage;
284 end if;
285 if cr_resp_usage%ISOPEN then
286 CLOSE cr_resp_usage;
287 end if;
288 x_return_status := FND_API.G_RET_STS_ERROR;
289 FND_MSG_PUB.Count_And_Get( p_encoded => 'F' ,p_count => x_msg_count
290 ,p_data => x_msg_data);
291 WHEN OTHERS THEN
292 if cr_menu_usage%ISOPEN then
293 CLOSE cr_menu_usage;
294 end if;
295 if cr_resp_usage%ISOPEN then
296 CLOSE cr_resp_usage;
297 end if;
298 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
299 FND_MSG_PUB.Count_And_Get( p_encoded => 'F' ,p_count => x_msg_count
300 ,p_data => x_msg_data);
301 if (x_msg_data is null) then
302 x_msg_data := SQLERRM;
303 end if;
304
305 END VALIDATE_DELETE;
306
307
308 PROCEDURE DELETE_ROW (
309 p_MENU_ID in VARCHAR2
310 ,x_return_status OUT NOCOPY VARCHAR2
311 ,x_msg_count OUT NOCOPY NUMBER
312 ,x_msg_data OUT NOCOPY VARCHAR2
313 ) IS
314
315 l_menu_id number;
316 BEGIN
317 fnd_msg_pub.initialize;
318
319 validate_delete(p_menu_id, x_return_status, x_msg_count, x_msg_data);
320
321 if (x_return_status is null) then
322 begin
323 select menu_id into l_menu_id from fnd_menus where menu_id = p_menu_id for update of menu_id nowait;
324 FND_MENUS_PKG.DELETE_ROW(X_MENU_ID => p_MENU_ID);
325 exception
326 when others then
327 FND_MESSAGE.SET_NAME('BIS','BIS_MENU_DELETED_ERROR');
328 FND_MSG_PUB.ADD;
329 RAISE FND_API.G_EXC_ERROR;
330 end;
331 end if;
332
333 EXCEPTION
334 WHEN FND_API.G_EXC_ERROR THEN
335 x_return_status := FND_API.G_RET_STS_ERROR;
336 FND_MSG_PUB.Count_And_Get( p_encoded => 'F' ,p_count => x_msg_count
337 ,p_data => x_msg_data);
338 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
339 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
340 FND_MSG_PUB.Count_And_Get( p_encoded => 'F' ,p_count => x_msg_count
341 ,p_data => x_msg_data);
342 WHEN NO_DATA_FOUND THEN
343 x_return_status := FND_API.G_RET_STS_ERROR;
344 FND_MSG_PUB.Count_And_Get( p_encoded => 'F' ,p_count => x_msg_count
345 ,p_data => x_msg_data);
346 WHEN OTHERS THEN
347 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
348 FND_MSG_PUB.Count_And_Get( p_encoded => 'F' ,p_count => x_msg_count
349 ,p_data => x_msg_data);
350 if (x_msg_data is null) then
351 x_msg_data := SQLERRM;
352 end if;
353
354 END DELETE_ROW;
355
356 PROCEDURE DELETE_ROW_MENU_MENUENTRIES (
357 p_MENU_ID in VARCHAR2
358 ,x_return_status OUT NOCOPY VARCHAR2
359 ,x_msg_count OUT NOCOPY NUMBER
360 ,x_msg_data OUT NOCOPY VARCHAR2
361 ) IS
362
363 l_return_status VARCHAR2(40);
364 l_msg_count NUMBER;
365 l_msg_data VARCHAR2(40);
366 l_menu_id NUMBER;
367
368 BEGIN
369
370 fnd_msg_pub.initialize;
371
372 validate_delete(p_menu_id, x_return_status, x_msg_count, x_msg_data);
373
374 if (x_return_status is null) then
375
376 BIS_MENU_ENTRIES_PUB.DELETE_ROW (X_MENU_ID => p_MENU_ID,
377 x_return_status => l_return_status,
378 x_msg_count => l_msg_count,
379 x_msg_data => l_msg_data);
380
381 begin
382 FND_MENUS_PKG.DELETE_ROW(X_MENU_ID => p_MENU_ID);
383 exception
384 when no_data_found then
385 FND_MESSAGE.SET_NAME('BIS','BIS_MENU_DELETED_ERROR');
386 FND_MSG_PUB.ADD;
387 RAISE FND_API.G_EXC_ERROR;
388 end;
389 end if;
390
391 EXCEPTION
392 WHEN FND_API.G_EXC_ERROR THEN
393 x_return_status := FND_API.G_RET_STS_ERROR;
394 FND_MSG_PUB.Count_And_Get( p_encoded => 'F' ,p_count => x_msg_count
395 ,p_data => x_msg_data);
396 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
397 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
398 FND_MSG_PUB.Count_And_Get( p_encoded => 'F' ,p_count => x_msg_count
399 ,p_data => x_msg_data);
400 WHEN NO_DATA_FOUND THEN
401 x_return_status := FND_API.G_RET_STS_ERROR;
402 FND_MSG_PUB.Count_And_Get( p_encoded => 'F' ,p_count => x_msg_count
403 ,p_data => x_msg_data);
404 WHEN OTHERS THEN
405 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
406 FND_MSG_PUB.Count_And_Get( p_encoded => 'F' ,p_count => x_msg_count
407 ,p_data => x_msg_data);
408 if (x_msg_data is null) then
409 x_msg_data := SQLERRM;
410 end if;
411
412 END DELETE_ROW_MENU_MENUENTRIES;
413
414 PROCEDURE LOCK_ROW
415 ( p_menu_id IN NUMBER
416 , p_last_update_date IN DATE
417 ) IS
418
419 l_last_update_date date;
420
421 cursor cMenu is
422 select last_update_date
423 from fnd_menus
424 where menu_id = p_menu_id
425 for update of menu_id nowait;
426
427 BEGIN
428
429 fnd_msg_pub.initialize;
430
431 SAVEPOINT SP_LOCK_ROW;
432
433 IF cMenu%ISOPEN THEN
434 CLOSE cMenu;
435 END IF;
436 OPEN cMenu;
437 FETCH cMenu INTO l_last_update_date;
438
439 if (cMenu%notfound) then
440 FND_MESSAGE.SET_NAME('BIS','BIS_MENU_DELETED_ERROR');
441 FND_MSG_PUB.ADD;
442 RAISE FND_API.G_EXC_ERROR;
443 end if;
444
445 if p_last_update_date is not null then
446 if p_last_update_date <> l_last_update_date then
447 FND_MESSAGE.SET_NAME('BIS','BIS_MENU_CHANGED_ERROR');
448 FND_MSG_PUB.ADD;
449 RAISE FND_API.G_EXC_ERROR;
450 end if;
451 end if;
452
453 rollback to SP_LOCK_ROW;
454 CLOSE cMenu;
455
456 EXCEPTION
457 WHEN FND_API.G_EXC_ERROR THEN NULL;
458 WHEN OTHERS THEN
459 close cMenu;
460 rollback to SP_LOCK_ROW;
461 FND_MESSAGE.SET_NAME('BIS','BIS_MENU_LOCKED_ERROR');
462 FND_MSG_PUB.ADD;
463 RAISE FND_API.G_EXC_ERROR;
464 END LOCK_ROW;
465
466
467
468 END BIS_FND_MENUS_PUB;