[Home] [Help]
PACKAGE BODY: APPS.BIS_FORM_FUNCTIONS_PUB
Source
1 PACKAGE BODY BIS_FORM_FUNCTIONS_PUB as
2 /* $Header: BISPFFNB.pls 120.3 2005/09/20 03:55:02 akoduri noship $ */
3 ----------------------------------------------------------------------------
4 -- PACKAGE: BIS_FORM_FUNCTIONS_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 Page Definer Integration - overloaded for addnl --
17 -- functionality and error messaging --
18 -- 06/07/04 mdamle Added delete_function_and_menu_ent --
19 -- 07/19/04 ppalpart Create method DELETE_ROW_MENU_MENUENTRIES --
20 -- 08/04/04 mdamle Bug#3823878 - Add lock_row --
21 -- 09/24/04 mdamle Bug#3893663 - Return SQLERRM for all unexp errs --
22 -- Added rollback within the lock procedure --
23 -- 09/28/04 mdamle Bug#3919538 - Update function menu prompts --
24 -- 10/27/04 mdamle Bug#3972992 - Region code and app id in form fn --
25 -- 11/29/04 mdamle Enh#4024237 - Application id in form fn --
26 -- 01/03/05 mdamle Enh#3014083 - Integrate with Extension table --
27 -- 01/13/05 vtulasi Bug#4102897 - Change in size of variables --
28 -- 01/29/05 akoduri Bug#4083833 - Select Content FROM OA Region --
29 -- 02/02/05 rpenneru Bug#4139236 - Update description if p_description-
30 -- is NULL --
31 -- 03/21/05 ankagarw bug#4235732 - changing count(*) to count(1) --
32 -- 04/04/05 mdamle Bug#4204828 - Call api to delete menu entries --
33 -- so cache is invalidated --
34 -- 04/12/05 arhegde Bug#4273118 - Remove the check for FA customer --
35 -- defnd before call to Create_Form_Func_Extension --
36 -- 05/22/05 akoduri Enhancement#3865711 -- Obsolete Seeded Objects --
37 -- 05/03/05 rpenneru Enhancement#4346994 -- HTML Portlet --
38 -- 19-MAY-2005 visuri GSCC Issues bug 4363854 --
39 -- 17-AUG-2005 kyadamak Bug#4516889 added regioncode,regionapplid to update_row --
40 -- 20-SEP-2005 akoduri bug#4607348 - Obsoletion of measures is not --
41 -- changing the last_update_date and last_updated_by--
42 ----------------------------------------------------------------------------
43
44 procedure INSERT_ROW (
45 X_ROWID in out NOCOPY VARCHAR2,
46 X_USER_ID in NUMBER,
47 X_FUNCTION_ID in out NOCOPY VARCHAR2,
48 X_WEB_HTML_CALL in VARCHAR2,
49 X_FUNCTION_NAME in VARCHAR2,
50 X_PARAMETERS in VARCHAR2,
51 X_TYPE in VARCHAR2,
52 X_USER_FUNCTION_NAME in VARCHAR2,
53 X_DESCRIPTION in VARCHAR2) is
54
55 l_new_function_id NUMBER;
56
57 begin
58 select FND_FORM_FUNCTIONS_S.NEXTVAL into l_new_function_id from dual;
59
60 FND_FORM_FUNCTIONS_PKG.INSERT_ROW(
61 X_ROWID => X_ROWID,
62 X_FUNCTION_ID => l_new_function_id,
63 X_WEB_HOST_NAME => null,
64 X_WEB_AGENT_NAME => null,
65 X_WEB_HTML_CALL => X_WEB_HTML_CALL,
66 X_WEB_ENCRYPT_PARAMETERS => c_WEB_ENCRYPT_PARAMETERS,
67 X_WEB_SECURED => c_WEB_SECURED,
68 X_WEB_ICON => null,
69 X_OBJECT_ID => null,
70 X_REGION_APPLICATION_ID => null,
71 X_REGION_CODE => null,
72 X_FUNCTION_NAME => upper(X_FUNCTION_NAME),
73 X_APPLICATION_ID => null,
74 X_FORM_ID => null,
75 X_PARAMETERS => X_PARAMETERS,
76 X_TYPE => X_TYPE,
77 X_USER_FUNCTION_NAME => X_USER_FUNCTION_NAME,
78 X_DESCRIPTION => X_DESCRIPTION,
79 X_CREATION_DATE => sysdate,
80 X_CREATED_BY => X_USER_ID,
81 X_LAST_UPDATE_DATE => sysdate,
82 X_LAST_UPDATED_BY => X_USER_ID,
83 X_LAST_UPDATE_LOGIN => X_USER_ID,
84 X_MAINTENANCE_MODE_SUPPORT => NULL,
85 X_CONTEXT_DEPENDENCE => NULL);
86
87 if X_ROWID is not null then
88 X_FUNCTION_ID := l_new_function_id;
89 end if;
90
91 end INSERT_ROW;
92
93 -- mdamle 12/25/2003 - overloaded for additional functionality & error messaging
94 procedure INSERT_ROW (
95 p_FUNCTION_NAME in VARCHAR2
96 ,p_WEB_HTML_CALL in VARCHAR2
97 ,p_PARAMETERS in VARCHAR2
98 ,p_TYPE in VARCHAR2
99 ,p_USER_FUNCTION_NAME in VARCHAR2
100 ,p_DESCRIPTION in VARCHAR2 := NULL
101 ,x_FUNCTION_ID OUT NOCOPY NUMBER
102 ,x_return_status OUT NOCOPY VARCHAR2
103 ,x_msg_count OUT NOCOPY NUMBER
104 ,x_msg_data OUT NOCOPY VARCHAR2
105 ,p_REGION_CODE in VARCHAR2 := BIS_COMMON_UTILS.G_DEF_CHAR
106 ,p_REGION_APPLICATION_ID in NUMBER := BIS_COMMON_UTILS.G_DEF_NUM
107 ,p_APPLICATION_ID in NUMBER := BIS_COMMON_UTILS.G_DEF_NUM
108 ,p_OBJECT_TYPE in VARCHAR2 := BIS_COMMON_UTILS.G_DEF_CHAR
109 ,p_FUNCTIONAL_AREA_ID in NUMBER := BIS_COMMON_UTILS.G_DEF_NUM
110 ) is
111
112 l_rowid VARCHAR2(30);
113 l_new_function_id NUMBER;
114 l_region_application_id NUMBER := null;
115 l_region_code VARCHAR2(30) := null;
116 l_application_id NUMBER := null;
117 l_Form_Func_Extn_Rec BIS_OBJECT_EXTENSIONS_PUB.Form_Function_Extension_Type;
118 l_custom_functional_area_id number;
119
120 begin
121 select FND_FORM_FUNCTIONS_S.NEXTVAL into l_new_function_id from dual;
122
123 if p_region_application_id <> BIS_COMMON_UTILS.G_DEF_NUM then
124 l_region_application_id := p_region_application_id;
125 end if;
126
127 if p_region_code <> BIS_COMMON_UTILS.G_DEF_CHAR then
128 l_region_code := p_region_code;
129 end if;
130
131 if p_application_id <> BIS_COMMON_UTILS.G_DEF_NUM then
132 l_application_id := p_application_id;
133 end if;
134
135
136 FND_FORM_FUNCTIONS_PKG.INSERT_ROW(
137 X_ROWID => l_ROWID,
138 X_FUNCTION_ID => l_new_function_id,
139 X_WEB_HOST_NAME => null,
140 X_WEB_AGENT_NAME => null,
141 X_WEB_HTML_CALL => p_WEB_HTML_CALL,
142 X_WEB_ENCRYPT_PARAMETERS => c_WEB_ENCRYPT_PARAMETERS,
143 X_WEB_SECURED => c_WEB_SECURED,
144 X_WEB_ICON => null,
145 X_OBJECT_ID => null,
146 X_REGION_APPLICATION_ID => l_region_application_id,
147 X_REGION_CODE => l_region_code,
148 X_FUNCTION_NAME => upper(p_FUNCTION_NAME),
149 X_APPLICATION_ID => null,
150 X_FORM_ID => null,
151 X_PARAMETERS => p_PARAMETERS,
152 X_TYPE => p_TYPE,
153 X_USER_FUNCTION_NAME => p_USER_FUNCTION_NAME,
154 X_DESCRIPTION => p_DESCRIPTION,
155 X_CREATION_DATE => sysdate,
156 X_CREATED_BY => fnd_global.user_id,
157 X_LAST_UPDATE_DATE => sysdate,
158 X_LAST_UPDATED_BY => fnd_global.user_id,
159 X_LAST_UPDATE_LOGIN => fnd_global.user_id,
160 X_MAINTENANCE_MODE_SUPPORT => NULL,
161 X_CONTEXT_DEPENDENCE => NULL);
162
163 if l_ROWID is not null then
164 x_FUNCTION_ID := l_new_function_id;
165 end if;
166
167 -- mdamle 01/03/2005 - Integrate with extension table
168 if p_functional_area_id <> BIS_COMMON_UTILS.G_DEF_NUM and p_object_type <> BIS_COMMON_UTILS.G_DEF_CHAR and p_application_id <> BIS_COMMON_UTILS.G_DEF_NUM then
169 l_Form_Func_Extn_Rec.object_type := p_object_type;
170 l_Form_Func_Extn_Rec.object_name := upper(p_FUNCTION_NAME);
171 l_Form_Func_Extn_Rec.application_id := l_application_id;
172 l_Form_Func_Extn_Rec.func_area_id := p_functional_area_id;
173 BIS_OBJECT_EXTENSIONS_PUB.Create_Form_Func_Extension(
174 p_Api_Version => 1.0
175 , p_Commit => FND_API.G_FALSE
176 , p_Form_Func_Extn_Rec => l_Form_Func_Extn_Rec
177 , x_Return_Status => x_return_status
178 , x_Msg_Count => x_msg_count
179 , x_Msg_Data => x_msg_data);
180 end if;
181 EXCEPTION
182 WHEN FND_API.G_EXC_ERROR THEN
183 x_return_status := FND_API.G_RET_STS_ERROR;
184 FND_MSG_PUB.Count_And_Get( p_encoded => 'F' ,p_count => x_msg_count
185 ,p_data => x_msg_data);
186 WHEN OTHERS THEN
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 if (x_msg_data is null) then
191 x_msg_data := 'BIS_FORM_FUNCTIONS_PUB.INSERT_ROW: ' || SQLERRM;
192 end if;
193
194 end INSERT_ROW;
195
196
197 procedure UPDATE_ROW (
198 X_USER_ID in NUMBER,
199 X_FUNCTION_ID in NUMBER,
200 X_PARAMETERS in VARCHAR2,
201 X_DESCRIPTION in VARCHAR2) is
202
203 l_formFunction_rec FormFunction_Rec_Type;
204
205 cursor cFormFunction is
206 select function_name,
207 user_function_name,
208 type,
209 web_html_call,
210 web_host_name,
211 web_agent_name,
212 web_encrypt_parameters,
213 web_secured,
214 web_icon,
215 object_id,
216 region_application_id,
217 region_code,
218 application_id,
219 form_id,
220 maintenance_mode_support,
221 context_dependence
222 from fnd_form_functions_vl
223 where function_id = X_FUNCTION_ID;
224
225 begin
226
227 if cFormFunction%ISOPEN then
228 CLOSE cFormFunction;
229 end if;
230
231 OPEN cFormFunction;
232 FETCH cFormFunction INTO
233 l_formFunction_rec.function_name,
234 l_formFunction_rec.user_function_name,
235 l_formFunction_rec.type,
236 l_formFunction_rec.web_html_call,
237 l_formFunction_rec.web_host_name,
238 l_formFunction_rec.web_agent_name,
239 l_formFunction_rec.web_encrypt_parameters,
240 l_formFunction_rec.web_secured,
241 l_formFunction_rec.web_icon,
242 l_formFunction_rec.object_id,
243 l_formFunction_rec.region_application_id,
244 l_formFunction_rec.region_code,
245 l_formFunction_rec.application_id,
246 l_formFunction_rec.form_id,
247 l_formFunction_rec.maintenance_mode_support,
248 l_formFunction_rec.context_dependence;
249 CLOSE cFormFunction;
250
251 FND_FORM_FUNCTIONS_PKG.UPDATE_ROW(
252 X_FUNCTION_ID => X_FUNCTION_ID,
253 X_WEB_HOST_NAME => l_formFunction_rec.web_host_name,
254 X_WEB_AGENT_NAME => l_formFunction_rec.web_agent_name,
255 X_WEB_HTML_CALL => l_formFunction_rec.web_html_call,
256 X_WEB_ENCRYPT_PARAMETERS => l_formFunction_rec.web_encrypt_parameters,
257 X_WEB_SECURED => l_formFunction_rec.web_secured,
258 X_WEB_ICON => l_formFunction_rec.web_icon,
259 X_OBJECT_ID => l_formFunction_rec.object_id,
260 X_REGION_APPLICATION_ID => l_formFunction_rec.region_application_id,
261 X_REGION_CODE => l_formFunction_rec.region_code,
262 X_FUNCTION_NAME => l_formFunction_rec.function_name,
263 X_APPLICATION_ID => l_formFunction_rec.application_id,
264 X_FORM_ID => l_formFunction_rec.form_id,
265 X_PARAMETERS => X_PARAMETERS,
266 X_TYPE => l_formFunction_rec.type,
267 X_USER_FUNCTION_NAME => l_formFunction_rec.user_function_name,
268 X_DESCRIPTION => X_DESCRIPTION,
269 X_LAST_UPDATE_DATE => sysdate,
270 X_LAST_UPDATED_BY => X_USER_ID,
271 X_LAST_UPDATE_LOGIN => X_USER_ID,
272 X_MAINTENANCE_MODE_SUPPORT => l_formFunction_rec.maintenance_mode_support,
273 X_CONTEXT_DEPENDENCE => l_formFunction_rec.context_dependence);
274
275 end UPDATE_ROW;
276
277 -- mdamle 12/25/2003 - overloaded for additional functionality & error messaging
278 PROCEDURE UPDATE_ROW (
279 p_FUNCTION_ID IN NUMBER
280 ,p_USER_FUNCTION_NAME IN VARCHAR2 := BIS_COMMON_UTILS.G_DEF_CHAR
281 ,p_PARAMETERS IN VARCHAR2 := BIS_COMMON_UTILS.G_DEF_CHAR
282 ,p_DESCRIPTION IN VARCHAR2 := BIS_COMMON_UTILS.G_DEF_CHAR
283 ,p_WEB_HTML_CALL IN VARCHAR2 := BIS_COMMON_UTILS.G_DEF_CHAR
284 ,p_APPLICATION_ID IN NUMBER := BIS_COMMON_UTILS.G_DEF_NUM
285 ,p_OBJECT_TYPE IN VARCHAR2 := BIS_COMMON_UTILS.G_DEF_CHAR
286 ,p_FUNCTIONAL_AREA_ID IN NUMBER := BIS_COMMON_UTILS.G_DEF_NUM
287 ,x_return_status OUT NOCOPY VARCHAR2
288 ,x_msg_count OUT NOCOPY NUMBER
289 ,x_msg_data OUT NOCOPY VARCHAR2
290 ,p_REGION_CODE IN VARCHAR2 := NULL
291 ,p_REGION_APPLICATION_ID IN NUMBER := NULL
292 ) IS
293
294 l_formFunction_rec FormFunction_Rec_Type;
295 l_Form_Func_Extn_Rec BIS_OBJECT_EXTENSIONS_PUB.Form_Function_Extension_Type;
296 l_custom_functional_area_id number;
297 l_function_name FND_FORM_FUNCTIONS.FUNCTION_NAME%TYPE;
298 l_count number;
299
300 cursor cFormFunction is
301 select function_name,
302 type,
303 web_html_call,
304 web_host_name,
305 web_agent_name,
306 web_encrypt_parameters,
307 web_secured,
308 web_icon,
309 object_id,
310 region_application_id,
311 region_code,
312 application_id,
313 form_id,
314 maintenance_mode_support,
315 context_dependence,
316 user_function_name,
317 description,
318 parameters
319 from fnd_form_functions_vl
320 where function_id = p_FUNCTION_ID;
321
322 begin
323
324 if cFormFunction%ISOPEN then
325 CLOSE cFormFunction;
326 end if;
327
328 OPEN cFormFunction;
329 FETCH cFormFunction INTO
330 l_formFunction_rec.function_name,
331 l_formFunction_rec.type,
332 l_formFunction_rec.web_html_call,
333 l_formFunction_rec.web_host_name,
334 l_formFunction_rec.web_agent_name,
335 l_formFunction_rec.web_encrypt_parameters,
336 l_formFunction_rec.web_secured,
337 l_formFunction_rec.web_icon,
338 l_formFunction_rec.object_id,
339 l_formFunction_rec.region_application_id,
340 l_formFunction_rec.region_code,
341 l_formFunction_rec.application_id,
342 l_formFunction_rec.form_id,
343 l_formFunction_rec.maintenance_mode_support,
344 l_formFunction_rec.context_dependence,
345 l_formFunction_rec.user_function_name,
346 l_formFunction_rec.description,
347 l_formFunction_rec.parameters;
348 CLOSE cFormFunction;
349
350 if (p_user_function_name <> BIS_COMMON_UTILS.G_DEF_CHAR) then
351 l_formFunction_rec.user_function_name := p_user_function_name;
352 end if;
353 if (p_description IS NULL ) THEN
354 l_formFunction_rec.description := NULL;
355 elsif (p_description <> BIS_COMMON_UTILS.G_DEF_CHAR) then
356 l_formFunction_rec.description := p_description;
357 end if;
358 if (p_parameters <> BIS_COMMON_UTILS.G_DEF_CHAR) then
359 l_formFunction_rec.parameters := p_parameters;
360 end if;
361 if (p_web_html_call <> BIS_COMMON_UTILS.G_DEF_CHAR) then
365 l_formFunction_rec.region_code := p_REGION_CODE; --We need to update with null even in update case
362 l_formFunction_rec.web_html_call := p_web_html_call;
363 end if;
364
366 l_formFunction_rec.region_application_id := p_REGION_APPLICATION_ID;
367
368 FND_FORM_FUNCTIONS_PKG.UPDATE_ROW(
369 X_FUNCTION_ID => p_FUNCTION_ID,
370 X_WEB_HOST_NAME => l_formFunction_rec.web_host_name,
371 X_WEB_AGENT_NAME => l_formFunction_rec.web_agent_name,
372 X_WEB_HTML_CALL => l_formFunction_rec.web_html_call,
373 X_WEB_ENCRYPT_PARAMETERS => l_formFunction_rec.web_encrypt_parameters,
374 X_WEB_SECURED => l_formFunction_rec.web_secured,
375 X_WEB_ICON => l_formFunction_rec.web_icon,
376 X_OBJECT_ID => l_formFunction_rec.object_id,
377 X_REGION_APPLICATION_ID => l_formFunction_rec.region_application_id,
378 X_REGION_CODE => l_formFunction_rec.region_code,
379 X_FUNCTION_NAME => l_formFunction_rec.function_name,
380 X_APPLICATION_ID => l_formFunction_rec.application_id,
381 X_FORM_ID => l_formFunction_rec.form_id,
382 X_PARAMETERS => l_formFunction_rec.parameters,
383 X_TYPE => l_formFunction_rec.type,
384 X_USER_FUNCTION_NAME => l_formFunction_rec.user_function_name,
385 X_DESCRIPTION => l_formFunction_rec.description,
386 X_LAST_UPDATE_DATE => sysdate,
387 X_LAST_UPDATED_BY => fnd_global.user_id,
388 X_LAST_UPDATE_LOGIN => fnd_global.user_id,
389 X_MAINTENANCE_MODE_SUPPORT => l_formFunction_rec.maintenance_mode_support,
390 X_CONTEXT_DEPENDENCE => l_formFunction_rec.context_dependence);
391 if p_functional_area_id <> BIS_COMMON_UTILS.G_DEF_NUM and p_object_type <> BIS_COMMON_UTILS.G_DEF_CHAR and p_application_id <> BIS_COMMON_UTILS.G_DEF_NUM then
392 select function_name into l_function_name
393 from fnd_form_functions_vl
394 where function_id = p_FUNCTION_ID;
395 select count(1) into l_count from bis_form_function_extension where upper(object_name) = upper(l_function_name);
396 l_Form_Func_Extn_Rec.object_type := p_object_type;
397 l_Form_Func_Extn_Rec.object_name := upper(l_function_name);
398 l_Form_Func_Extn_Rec.application_id := p_application_id;
399 l_Form_Func_Extn_Rec.func_area_id := p_functional_area_id;
400 if l_count > 0 then
401 BIS_OBJECT_EXTENSIONS_PUB.Update_Form_Func_Extension(
402 p_Api_Version => 1.0
406 , x_Msg_Count => x_msg_count
403 , p_Commit => FND_API.G_FALSE
404 , p_Form_Func_Extn_Rec => l_Form_Func_Extn_Rec
405 , x_Return_Status => x_return_status
407 , x_Msg_Data => x_msg_data);
408 else
409 BIS_OBJECT_EXTENSIONS_PUB.Create_Form_Func_Extension(
410 p_Api_Version => 1.0
411 , p_Commit => FND_API.G_FALSE
412 , p_Form_Func_Extn_Rec => l_Form_Func_Extn_Rec
413 , x_Return_Status => x_return_status
414 , x_Msg_Count => x_msg_count
415 , x_Msg_Data => x_msg_data);
416
417 end if;
418 end if;
419
420 EXCEPTION
421 WHEN FND_API.G_EXC_ERROR THEN
422 x_return_status := FND_API.G_RET_STS_ERROR;
423 FND_MSG_PUB.Count_And_Get( p_encoded => 'F' ,p_count => x_msg_count
424 ,p_data => x_msg_data);
425 WHEN OTHERS THEN
426 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
427 FND_MSG_PUB.Count_And_Get( p_encoded => 'F' ,p_count => x_msg_count
428 ,p_data => x_msg_data);
429 if (x_msg_data is null) then
430 x_msg_data := 'BIS_FORM_FUNCTIONS_PUB.UPDATE_ROW: ' || SQLERRM;
431 end if;
432
433 end UPDATE_ROW;
434
435 -- mdamle 12/25/2003
436 PROCEDURE DELETE_ROW (
437 p_FUNCTION_ID in VARCHAR2
438 ,x_return_status OUT NOCOPY VARCHAR2
439 ,x_msg_count OUT NOCOPY NUMBER
440 ,x_msg_data OUT NOCOPY VARCHAR2
441 ) IS
445
442 l_function_name FND_FORM_FUNCTIONS.FUNCTION_NAME%TYPE;
443 l_Form_Func_Extn_Rec BIS_OBJECT_EXTENSIONS_PUB.Form_Function_Extension_Type;
444 l_count number;
446 BEGIN
447
448 select function_name into l_function_name
449 from fnd_form_functions
450 where function_id = p_function_id;
451
452 FND_FORM_FUNCTIONS_PKG.DELETE_ROW(
453 X_FUNCTION_ID => p_FUNCTION_ID
454 );
455
456 -- mdamle 01/03/2005 - Integrate with extension table
457 l_Form_Func_Extn_Rec.object_name := l_function_name;
458 select count(1) into l_count from bis_form_function_extension where object_name = l_function_name;
459 if l_count > 0 then
460
461 BIS_OBJECT_EXTENSIONS_PUB.Delete_Form_Func_Extension(
462 p_Api_Version => 1.0
463 , p_Commit => FND_API.G_FALSE
464 , p_Form_Func_Extn_Rec => l_Form_Func_Extn_Rec
465 , x_Return_Status => x_return_status
466 , x_Msg_Count => x_msg_count
467 , x_Msg_Data => x_msg_data);
468 end if;
469
470 EXCEPTION
471 WHEN FND_API.G_EXC_ERROR THEN
472 x_return_status := FND_API.G_RET_STS_ERROR;
473 FND_MSG_PUB.Count_And_Get( p_encoded => 'F' ,p_count => x_msg_count
474 ,p_data => x_msg_data);
475 WHEN OTHERS THEN
476 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
477 FND_MSG_PUB.Count_And_Get( p_encoded => 'F' ,p_count => x_msg_count
478 ,p_data => x_msg_data);
479 if (x_msg_data is null) then
480 x_msg_data := 'BIS_FORM_FUNCTIONS_PUB.DELETE_ROW: ' || SQLERRM;
481 end if;
482
483 END DELETE_ROW;
484
485 --Copied from BIS_KPILIST_WIZARD_PKG
486 PROCEDURE DELETE_FUNCTION_AND_MENU_ENT
487 (p_function_name IN VARCHAR2
488 ,x_return_status OUT NOCOPY VARCHAR2
489 ,x_msg_count OUT NOCOPY NUMBER
490 ,x_msg_data OUT NOCOPY VARCHAR2
491 )
492 IS
493 l_function_id fnd_form_functions.function_id%TYPE;
494
495 CURSOR function_id_crsr IS
496 SELECT function_id
497 FROM fnd_form_functions
498 WHERE function_name = p_function_name;
499
500 CURSOR c_menu_entries (p_function_id fnd_menu_entries.function_id%TYPE) IS
501 SELECT menu_id, entry_sequence
502 FROM fnd_menu_entries
503 WHERE function_id = p_function_id;
504 BEGIN
505 x_return_status := FND_API.G_RET_STS_SUCCESS;
506
507 IF (function_id_crsr%ISOPEN) THEN
508 CLOSE function_id_crsr;
509 END IF;
510
511 OPEN function_id_crsr;
512 FETCH function_id_crsr INTO l_function_id;
513 CLOSE function_id_crsr;
514
515 /* Also delete the menu entries corresponding to this function */
516 if c_menu_entries%ISOPEN then
517 CLOSE c_menu_entries;
518 end if;
519
520 for mentry in c_menu_entries(l_function_id) loop
521 bis_menu_entries_pub.delete_row(x_menu_id=>mentry.menu_id,
522 x_entry_sequence => mentry.entry_sequence,
523 x_return_status=> x_return_status,
524 x_msg_count => x_msg_count,
525 x_msg_data => x_msg_data);
526
527 end loop;
528
529 delete_row(p_function_id => l_function_id,
530 x_return_status=> x_return_status,
531 x_msg_count => x_msg_count,
532 x_msg_data => x_msg_data);
533
534 IF (function_id_crsr%ISOPEN) THEN
535 CLOSE function_id_crsr;
536 END IF;
537
538 IF (c_menu_entries%ISOPEN) THEN
539 CLOSE c_menu_entries;
540 END IF;
541
542 EXCEPTION
543 WHEN FND_API.G_EXC_ERROR THEN
544 x_return_status := FND_API.G_RET_STS_ERROR;
545 FND_MSG_PUB.Count_And_Get( p_encoded => 'F' ,p_count => x_msg_count
546 ,p_data => x_msg_data);
547 WHEN OTHERS THEN
548 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
549 FND_MSG_PUB.Count_And_Get( p_encoded => 'F' ,p_count => x_msg_count
550 ,p_data => x_msg_data);
551 if (x_msg_data is null) then
552 x_msg_data := 'BIS_FORM_FUNCTIONS_PUB.DELETE_FUNCTION_AND_MENU_ENT: ' || SQLERRM;
553 end if;
554
555 END DELETE_FUNCTION_AND_MENU_ENT;
556
557 PROCEDURE DELETE_ROW_FUNC_MENUENTRIES (
558 p_FUNCTION_ID in VARCHAR2
559 ,x_return_status OUT NOCOPY VARCHAR2
560 ,x_msg_count OUT NOCOPY NUMBER
561 ,x_msg_data OUT NOCOPY VARCHAR2
562 ) IS
563
564 l_return_status VARCHAR2(40);
565 l_msg_count NUMBER;
566 l_msg_data VARCHAR2(40);
570 where function_id = p_FUNCTION_ID;
567 cursor entry_seq_menu_cursor is
568 select menu_id, entry_sequence
569 from fnd_menu_entries
571 BEGIN
572
573 for ent_seq_menu_cur in entry_seq_menu_cursor loop
574
575 BIS_MENU_ENTRIES_PUB.DELETE_ROW (X_MENU_ID => ent_seq_menu_cur.menu_id,
576 X_ENTRY_SEQUENCE => ent_seq_menu_cur.entry_sequence,
577 x_return_status => l_return_status,
578 x_msg_count => l_msg_count,
579 x_msg_data => l_msg_data);
580
581 end loop;
582
583 FND_FORM_FUNCTIONS_PKG.DELETE_ROW(
584 X_FUNCTION_ID => p_FUNCTION_ID
585 );
586
587 EXCEPTION
588 WHEN FND_API.G_EXC_ERROR THEN
589 x_return_status := FND_API.G_RET_STS_ERROR;
590 FND_MSG_PUB.Count_And_Get( p_encoded => 'F' ,p_count => x_msg_count
591 ,p_data => x_msg_data);
592 WHEN OTHERS THEN
593 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
594 FND_MSG_PUB.Count_And_Get( p_encoded => 'F' ,p_count => x_msg_count
595 ,p_data => x_msg_data);
596 if (x_msg_data is null) then
597 x_msg_data := 'BIS_FORM_FUNCTIONS_PUB.DELETE_ROW_FUNC_MENUENTRIES: ' || SQLERRM;
598 end if;
599
600 END DELETE_ROW_FUNC_MENUENTRIES;
601
602
603 PROCEDURE LOCK_FUNCTION_ROW
604 ( p_function_id IN NUMBER
605 , p_last_update_date IN VARCHAR2
606 , x_record_status OUT NOCOPY VARCHAR2
607 ) IS
608
609 l_last_update_date date;
610
611 cursor cFunction is select last_update_date
612 from fnd_form_functions
613 where function_id = p_function_id
614 for update of function_id nowait;
615
616 BEGIN
617
618 SAVEPOINT SP_LOCK_FUNCTION_ROW;
619
620 IF cFunction%ISOPEN THEN
621 CLOSE cFunction;
622 END IF;
623 OPEN cFunction;
624 FETCH cFunction INTO l_last_update_date;
625
626 if (cFunction%notfound) then
627 x_record_status := BIS_FORM_FUNCTIONS_PUB.c_RECORD_DELETED;
628 end if;
629
630 if p_last_update_date is not null then
631 if p_last_update_date <> TO_CHAR(l_last_update_date, BIS_FORM_FUNCTIONS_PUB.C_LAST_UPDATE_DATE_FORMAT) then
632 x_record_status := BIS_FORM_FUNCTIONS_PUB.c_RECORD_CHANGED;
633 end if;
634 end if;
635
636 rollback to SP_LOCK_FUNCTION_ROW;
637 CLOSE cFunction;
638
639 EXCEPTION
640 WHEN OTHERS THEN
641 close cFunction;
642 x_record_status := BIS_FORM_FUNCTIONS_PUB.c_RECORD_CHANGED;
643 rollback to SP_LOCK_FUNCTION_ROW;
644 END LOCK_FUNCTION_ROW;
645
646 -- mdamle 09/28/2004 - Update menu prompts from user_function_name
647 PROCEDURE UPDATE_FUNCTION_MENU_PROMPTS
648 (p_function_id IN NUMBER
649 ,p_user_function_name IN VARCHAR2
650 ,x_return_status OUT NOCOPY VARCHAR2
651 ,x_msg_count OUT NOCOPY NUMBER
652 ,x_msg_data OUT NOCOPY VARCHAR2
653 )
654 IS
655 CURSOR menu_crsr (p_function_id fnd_menu_entries.function_id%TYPE) IS
656 SELECT menu_id, entry_sequence
657 FROM fnd_menu_entries
658 WHERE function_id = p_function_id;
659 BEGIN
660 x_return_status := FND_API.G_RET_STS_SUCCESS;
661
662 for rec in menu_crsr(p_function_id) loop
663 BIS_MENU_ENTRIES_PUB.UPDATE_PROMPT (
664 X_USER_ID => fnd_global.user_id,
665 X_MENU_ID => rec.menu_id,
666 X_OLD_ENTRY_SEQUENCE => rec.entry_sequence,
667 X_FUNCTION_ID => p_function_id,
668 X_PROMPT => p_user_function_name);
669 end loop;
670
671 if (menu_crsr%ISOPEN) then
672 close menu_crsr;
673 end if;
674
675 EXCEPTION
676 WHEN FND_API.G_EXC_ERROR THEN
677 x_return_status := FND_API.G_RET_STS_ERROR;
678 FND_MSG_PUB.Count_And_Get( p_encoded => 'F' ,p_count => x_msg_count
679 ,p_data => x_msg_data);
680 WHEN OTHERS THEN
681 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
682 FND_MSG_PUB.Count_And_Get( p_encoded => 'F' ,p_count => x_msg_count
683 ,p_data => x_msg_data);
684 if (x_msg_data is null) then
685 x_msg_data := 'BIS_FORM_FUNCTIONS_PUB.UPDATE_FUNCTION_MENU_PROMPTS: ' || SQLERRM;
686 end if;
687
688 END UPDATE_FUNCTION_MENU_PROMPTS;
689
690
691 PROCEDURE Update_Form_Func_Obsolete_Flag (
692 p_commit IN VARCHAR2 := FND_API.G_FALSE,
693 p_func_name IN VARCHAR2,
694 p_obsolete IN VARCHAR2,
695 x_return_status OUT nocopy VARCHAR2,
696 x_Msg_Count OUT NOCOPY NUMBER,
697 x_msg_data OUT nocopy VARCHAR2
698 ) IS
699 l_form_func_parameters FND_FORM_FUNCTIONS_VL.PARAMETERS%TYPE;
700 l_form_function_id FND_FORM_FUNCTIONS_VL.FUNCTION_ID%TYPE;
701 l_form_func_description FND_FORM_FUNCTIONS_VL.DESCRIPTION%TYPE;
702
703 BEGIN
704 SAVEPOINT FormFunctionObsoleteUpdate;
705 IF (p_func_name IS NULL OR p_func_name = '') THEN
706 FND_MESSAGE.SET_NAME('BIS','BIS_INV_FORM_FUNC_VAL');
707 FND_MSG_PUB.ADD;
708 RAISE FND_API.G_EXC_ERROR;
709 END IF;
710
711 IF (p_obsolete IS NULL OR (p_obsolete <> 'Y' AND p_obsolete <> 'N')) THEN
712 FND_MESSAGE.SET_NAME('BIS','BIS_PMF_INVALID_OBSOLETE_FLAG');
713 FND_MSG_PUB.ADD;
714 RAISE FND_API.G_EXC_ERROR;
715 END IF;
716
717 SELECT function_id,parameters,description
718 INTO l_form_function_id,l_form_func_parameters,l_form_func_description
719 FROM fnd_form_functions_vl
720 WHERE function_name = p_func_name;
721
722 IF (p_obsolete = 'Y') THEN
723 l_form_func_parameters := l_form_func_parameters || '&pObsoleteFlag=Y';
724 END IF;
725
726 IF (p_obsolete = 'N') THEN
727 l_form_func_parameters := REPLACE(l_form_func_parameters,'&pObsoleteFlag=Y');
728 END IF;
729
730 BIS_FORM_FUNCTIONS_PUB.UPDATE_ROW(
731 X_USER_ID => FND_GLOBAL.USER_ID,
732 X_FUNCTION_ID => l_form_function_id,
733 X_PARAMETERS => l_form_func_parameters,
734 X_DESCRIPTION => l_form_func_description
735 );
736
737 IF(p_Commit = FND_API.G_TRUE) THEN
738 commit;
739 END IF;
740 x_return_status := FND_API.G_RET_STS_SUCCESS;
741 RETURN;
742 EXCEPTION
743 WHEN FND_API.G_EXC_ERROR THEN
744 IF (x_msg_data IS NULL) THEN
745 FND_MSG_PUB.Count_And_Get
746 ( p_encoded => FND_API.G_FALSE
747 , p_count => x_msg_count
748 , p_data => x_msg_data
749 );
750 END IF;
751 x_Return_Status := FND_API.G_RET_STS_ERROR;
752 WHEN OTHERS THEN
753 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
754 ROLLBACK TO FormFunctionObsoleteUpdate;
755 IF (x_msg_data IS NOT NULL) THEN
756 x_msg_data := x_msg_data||' -> BIS_FORM_FUNCTIONS_PUB.Update_Form_Func_Obsolete_Flag ';
757 ELSE
758 x_msg_data := SQLERRM||' at BIS_FORM_FUNCTIONS_PUB.Update_Form_Func_Obsolete_Flag ';
759 END IF;
760 END Update_Form_Func_Obsolete_Flag;
761
762
763 PROCEDURE Check_Form_Function (
764 p_functionName IN VARCHAR2
765 ,p_user_functionName IN VARCHAR2
766 ,x_return_status OUT NOCOPY VARCHAR2
767 ,x_msg_count OUT NOCOPY NUMBER
768 ,x_msg_data OUT NOCOPY VARCHAR2
769 ) IS
770 l_ret_status VARCHAR2(10);
771 l_msg_data VARCHAR2(100);
772 l_parent_obj_table BIS_RSG_PUB_API_PKG.t_BIA_RSG_Obj_Table;
773 l_index INTEGER;
774 l_dep_obj_list VARCHAR2(2000);
775 BEGIN
776 FND_MSG_PUB.Initialize;
777 x_msg_data := '';
778 IF (p_functionName IS NULL OR p_functionName = '') THEN
779 FND_MESSAGE.SET_NAME('BIS','BIS_INV_FORM_FUNC_VAL');
780 FND_MSG_PUB.ADD;
781 RAISE FND_API.G_EXC_ERROR;
782 END IF;
783 x_return_status := FND_API.G_RET_STS_SUCCESS;
784 l_parent_obj_table := BIS_RSG_PUB_API_PKG.GetParentObjects(p_functionName,'PORTLET','PAGE',l_ret_status,l_msg_data);
785
786 IF ((l_ret_status IS NOT NULL) AND (l_ret_status <> FND_API.G_RET_STS_SUCCESS)) THEN
787 FND_MESSAGE.SET_NAME('BIS',l_msg_data);
788 FND_MSG_PUB.ADD;
789 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
790 ELSE
791 IF (l_parent_obj_table.COUNT > 0) THEN
792 l_dep_obj_list := '';
793 l_index := l_parent_obj_table.first;
794 LOOP
795 l_dep_obj_list := l_dep_obj_list ||'<li>'|| l_parent_obj_table(l_index).object_name ||'</li>';
796 EXIT WHEN l_index = l_parent_obj_table.last;
797 l_index := l_parent_obj_table.next(l_index);
798 END LOOP;
799 FND_MESSAGE.SET_NAME('BIS','BIS_HTML_PORTLET_DELETE');
800 FND_MESSAGE.SET_TOKEN('PORTLET', p_user_functionName);
801 FND_MESSAGE.SET_TOKEN('DEP_OBJ_LIST',l_dep_obj_list);
802 FND_MSG_PUB.ADD;
803 END IF;
804 END IF;
805 FND_MSG_PUB.Count_And_Get
806 ( p_encoded => FND_API.G_FALSE
807 , p_count => x_msg_count
808 , p_data => x_msg_data
809 );
810 EXCEPTION
811 WHEN FND_API.G_EXC_ERROR THEN
812 IF (x_msg_data IS NULL) THEN
813 FND_MSG_PUB.Count_And_Get
814 ( p_encoded => FND_API.G_FALSE
815 , p_count => x_msg_count
816 , p_data => x_msg_data
817 );
818 END IF;
819 x_Return_Status := FND_API.G_RET_STS_ERROR;
820 WHEN OTHERS THEN
821 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
822 IF (x_msg_data IS NOT NULL) THEN
823 x_msg_data := x_msg_data||' -> BIS_FORM_FUNCTIONS_PUB.Check_Form_Function ';
824 ELSE
825 x_msg_data := SQLERRM||' at BIS_FORM_FUNCTIONS_PUB.Check_Form_Function ';
826 END IF;
827 END Check_Form_Function;
828
829
830 END BIS_FORM_FUNCTIONS_PUB;