1 package body FND_FORM_FUNCTIONS_PKG as
2 /* $Header: AFFMFUNB.pls 120.2 2006/02/17 09:19:42 jvalenti ship $ */
3
4 -- private
5 function FUNCTION_VALIDATION (application_id in out nocopy number,
6 form_id in out nocopy number,
7 type in out nocopy varchar2,
8 parameters in out nocopy varchar2,
9 web_html_call in out nocopy varchar2,
10 web_host_name in varchar2,
11 region_application_id in out nocopy number,
12 region_code in out nocopy varchar2,
13 function_name in varchar2) return varchar2 is
14 columns_name varchar2(2000) := '';
15 begin
16 /*
17 ** Restore UNKNOWN functions
18 ** functions with region_application_id were converted to REGION
19 ** - region_application_id and region code used only in WEBPORTLET
20 ** - form reference (with no web call) is a FORM
21 ** - %.JSP web call is a JSP
22 ** - %.% web call is a WWW
23 ** - web call with no '%.%' is a DBPORTLET
24 ** - REGION type function not used
25 */
26
27 if type is null then
28 type := 'UNKNOWN';
29 columns_name := 'TYPE = UNKNOWN';
30 end if;
31
32 if ( (type = 'REGION' or type = 'UNKNOWN') and
33 (region_application_id is not null) and
34 (region_code is not null) and
35 (web_html_call is not null) and
36 (form_id is null) ) then
37 type := 'WEBPORTLET';
38 columns_name := 'TYPE = WEBPORTLET';
39 end if;
40
41 /* Note: SUBFUNCTIONS will be converted to FORMS... */
42 if ( (type = 'REGION' or type = 'UNKNOWN') and
43 (application_id is not null) and
44 (web_html_call is null) and
45 (form_id is not null) ) then
46 type := 'FORM';
47 columns_name := 'TYPE = FORM';
48 end if;
49
50 if ( (type = 'REGION' or type = 'UNKNOWN') and
51 (upper(web_html_call) like '%.JSP%')) then
52 type := 'JSP';
53 columns_name := 'TYPE = JSP';
54 end if;
55
56 if ( (type = 'REGION' or type = 'UNKNOWN') and
57 (upper(web_html_call) like '%.%')) then
58 type := 'WWW';
59 columns_name := 'TYPE = WWW';
60 end if;
61
62 if ( (type = 'WWW' or type = 'REGION' or type = 'UNKNOWN') and
63 (web_html_call not like '%.%') and
64 (web_html_call not like '%=%')) then
65 type := 'DBPORTLET';
66 columns_name := 'TYPE = DBPORTLET';
67 end if;
68
69 if (type = 'REGION') then
70 type := 'UNKNOWN';
71 columns_name := 'TYPE = UNKNOWN';
72 end if;
73
74 /*
75 ** Restore UNKNOWN functions
76 ** functions with no form, region, or web call were set to UNKNOWN
77 ** - restore PROCESS type functions
78 ** - convert to SUBFUNCTION
79 */
80
81 if (type = 'UNKNOWN' and
82 parameters like '%:%' and
83 parameters not like '%=%') then
84 type := 'PROCESS';
85 columns_name := 'TYPE = PROCESS';
86 end if;
87
88 if (type = 'UNKNOWN' and
89 form_id is null and
90 application_id is null and
91 web_html_call is null) then
92 type := 'SUBFUNCTION';
93 columns_name := 'TYPE = SUBFUNCTION';
94 end if;
95
96 /*
97 ** SUBFUNCTIONs with wrong type
98 ** - FORM type with no form_id
99 ** - "WEB" type with no web_html_call
100 */
101
102 if (type = 'FORM' and
103 form_id is null and
104 application_id is null) then
105 type := 'SUBFUNCTION';
106 columns_name := 'TYPE = SUBFUNCTION';
107 end if;
108
109 if ((type = 'WWW' or
110 type = 'WWL' or
111 type = 'WWLG' or
112 type = 'WWK') and
113 (web_html_call is null) and
114 (web_host_name is null)) then
115 type := 'SUBFUNCTION';
116 columns_name := 'TYPE = SUBFUNCTION';
117 end if;
118
119 if ((type = 'JSP' or
120 type = 'INTEROPJSP' or
121 type = 'SERVLET' or
122 type = 'WEBPORTLET' or
123 type = 'JTFWEBPORTLET' or
124 type = 'DBPORTLET' or
125 type = 'MOBILE') and
126 (web_html_call is null)) then
127 type := 'SUBFUNCTION';
128 columns_name := 'TYPE = SUBFUNCTION';
129 end if;
130
131 /*
132 ** Mispellings
133 */
134 if (type like 'SUB%' and
135 type <> 'SUBFUNCTION') then
136 type := 'SUBFUNCTION';
137 columns_name := 'TYPE = SUBFUNCTION';
138 end if;
139
140 if (type = 'SERVELET') then
141 type := 'SERVLET';
142 columns_name := 'TYPE = SERVLET';
143 end if;
144
145 /*
146 ** Obsolete types: PL/SQL web type functions
147 ** web functions must be one of
148 ** WWW - general web page
149 ** WWK - "kiosk" web page
150 ** WWL - Responsibility-specific PHP plug-in
151 ** WWLG - Global PHP plug-in
152 ** DBPORTLET - Portal portlet
153 */
154 if ((web_html_call is not null) and
155 (form_id is null) and
156 (application_id is null) and
157 (type like 'WW%') and
158 (type not in ('WWW', 'WWK', 'WWL', 'WWLG')) and
159 (type = 'WWP') )then
160 type := 'DBPORTLET';
161 columns_name := 'TYPE = DBPORTLET';
162 end if;
163
164 if ((web_html_call is not null) and
165 (form_id is null) and
166 (application_id is null) and
167 (type like 'WW%') and
168 (type not in ('WWW', 'WWK', 'WWL', 'WWLG')) and
169 (type = 'WWR') )then
170 type := 'WWL';
171 columns_name := 'TYPE = WWL';
172 end if;
173
174 if ((web_html_call is not null) and
175 (form_id is null) and
176 (application_id is null) and
177 (type like 'WW%') and
178 (type not in ('WWW', 'WWK', 'WWL', 'WWLG')) and
179 (type = 'WWRG') )then
180 type := 'WWLG';
181 columns_name := 'TYPE = WWLG';
182 end if;
183
184 if ((web_html_call is not null) and
185 (form_id is null) and
186 (application_id is null) and
187 (type like 'WW%') and
188 (type not in ('WWW', 'WWK', 'WWL', 'WWLG'))) then
189 type := 'WWW';
190 columns_name := 'TYPE = WWW';
191 end if;
192
193 /*
194 ** Obsolete / unrecognized types: FORM type functions
195 */
196
197 if (type <> 'FORM'
198 and type <> 'SUBFUNCTION'
199 and type <> 'PROCESS'
200 and type <> 'WWW'
201 and type <> 'JSP'
202 and type <> 'SERVLET'
203 and type <> 'DBPORTLET'
204 and type <> 'WEBPORTLET'
205 and type <> 'WWK'
206 and type <> 'WWL'
207 and type <> 'WWLG'
208 and form_id is not null
209 and application_id is not null) then
210 type := 'FORM';
211 columns_name := 'TYPE = FORM';
212 end if;
213
214
215 /*
216 ** June 2003: Clean up invalid function type.
217 ** The script is developed after product team have reviewed
218 ** our proposal.
219 ** Note: Case 1 to 6 is being done in the above sql scripts.
220 */
221
222 -- For formatting purpose
223 if (columns_name is not null) then
224 columns_name := columns_name||fnd_global.newline;
225 end if;
226
227 declare
228 applname varchar2(8);
229 begin
230
231 if (((type='FORM' and substr(function_name,1,3)
232 in ('WIP','INV','WMS','WMA')) or
233 (type='FORM' and substr(function_name,1,2) = 'QA') or
234 (type = 'WWW' and substr(function_name,1,3)
235 in ('WIP','INV','WMS','WMA')) or
236 (type = 'WWW' and substr(function_name,1,2) = 'QA')) and
237 (web_html_call is not null) and
238 (application_id is not null) and
239 (upper(web_html_call) not like 'ORACLE.APPS.%') and
240 (upper(web_html_call) not like '%.JSP%') ) then
241
242 select lower(application_short_name)
243 into applname
244 from fnd_application
245 where application_id = FUNCTION_VALIDATION.application_id;
246
247 web_html_call := 'oracle.apps.'||applname||'.'||web_html_call;
248 columns_name := columns_name||'WEB_HTML_CALL = '||web_html_call;
249 end if;
250 end;
251
252 if (form_id is not null and
253 web_html_call is not null and
254 upper(web_html_call) not like '%.JSP%' and
255 ((type='FORM' and substr(function_name,1,3) in ('WIP','INV','WMS','WMA')) or
256 (type='FORM' and substr(function_name,1,2)='QA') or
257 (type='WWW' and substr(function_name,1,3) in ('INV','WMS','WMA', 'WIP')) or
258 (type='WWW' and substr(function_name,1,2) = 'QA'))) then
259 form_id := '';
260 application_id := '';
261 type := 'MOBILE';
262 columns_name := columns_name||'FORM_ID = null'||fnd_global.newline;
263 columns_name := columns_name||'APPLICATION_ID = null'||fnd_global.newline;
264 columns_name := columns_name||'TYPE = MOBILE'||fnd_global.newline;
265 end if;
266
267 if (upper(web_html_call) like '%MOBILE%' and
268 function_name in ('PN_MOBILE_CUST_DIR',
269 'HZ_MOBILE_CUSTOMER',
270 'AP_OME_EXPENSES',
271 'AP_OME_EXPENSES_QUICK',
272 'AR_MAM_APPLICATION_FUNCTION')) then
273 type := 'MOBILE';
274 form_id := '';
275 application_id := '';
276 columns_name := columns_name||'FORM_ID = null'||fnd_global.newline;
277 columns_name := columns_name||'APPLICATION_ID = null'||fnd_global.newline;
278 columns_name := columns_name||'TYPE = MOBILE'||fnd_global.newline;
279 end if;
280
281 if (type = 'FORM' and
282 web_html_call is not null) then
283 web_html_call := '';
284 columns_name := columns_name||'WEB_HTML_CALL = null'||fnd_global.newline;
285 end if;
286
287 /*
288 +-----+-------------------------------+---------------------------------+-----+
289 | 8 | TYPE='FORM' & | Set REGION_APPLICATION_ID to | 30 |
290 | | REGION_APPLICATION_ID has data| null | |
291 +-----+-------------------------------+---------------------------------+-----+
292 */
293 if (type = 'FORM' and
294 region_application_id is not null) then
295 region_application_id := '';
296 columns_name := columns_name||'REGION_APPLICATION_ID = null'||
297 fnd_global.newline;
298 end if;
299
300 /*
301 +-----+-------------------------------+---------------------------------+-----+
302 | 9 | TYPE='JSP' & | Change TYPE to UNKNOWN | 36 |
303 | | WEB_HTML_CALL not like '%jsp%'| | |
304 +-----+-------------------------------+---------------------------------+-----+
305 */
306 if (type = 'JSP' and
307 upper(web_html_call) not like '%JSP%' and
308 upper(web_html_call) not like '%JAVASCRIPT%') then
309 type := 'UNKNOWN';
310 columns_name := columns_name||'TYPE = UNKNOWN'||fnd_global.newline;
311 end if;
312
313 /*
314 +-----+-------------------------------+---------------------------------+-----+
315 | 10 | TYPE='JSP' & | Set FORM_ID to NULL | 44 |
316 | | FORM_ID has data & | Set APPLICATION_ID to NULL | |
317 | | APPLICATION_ID has data | | |
318 +-----+-------------------------------+---------------------------------+-----+
319 */
320 if (type = 'JSP' and
321 form_id is not null and
322 application_id is not null) then
323 form_id := '';
324 application_id := '';
325 columns_name := columns_name||'FORM_ID = null'||fnd_global.newline;
326 columns_name := columns_name||'APPLICATION_ID = null'||fnd_global.newline;
327 end if;
328
329 /*
330 +-----+-------------------------------+---------------------------------+-----+
331 | 11 | TYPE='WWW' & | Set FORM_ID to NULL | 98 |
332 | | FORM_ID has data & | Set APPLICATION_ID to NULL | |
333 | | APPLICATION_ID has data | | |
334 +-----+-------------------------------+---------------------------------+-----+
335 */
336 if (type = 'WWW' and
337 (form_id is not null or application_id is not null)) then
338 form_id := '';
339 application_id := '';
340 columns_name := columns_name||'FORM_ID = null'||fnd_global.newline;
341 columns_name := columns_name||'APPLICATION_ID = null'||fnd_global.newline;
342 end if;
343
344 /*
348 | | PARAMETERS is null & | | |
345 +-----+-------------------------------+---------------------------------+-----+
346 | 12 | TYPE='SUBFUNCTION' & | Chage TYPE to FORM | 353 |
347 | | FORM_ID has data & | | |
349 | | WEB_HTML_CALL is null & | | |
350 | | REGION_CODE is null | | |
351 +-----+----------------------------- -+---------------------------------+-----+
352 */
353 if ( type = 'SUBFUNCTION'
354 and form_id is not null
355 and parameters is null
356 and web_html_call is null
357 and region_code is null) then
358 type := 'FORM';
359 columns_name := columns_name||'TYPE = FORM'||fnd_global.newline;
360 end if;
361
362 /*
363 +-----+----------------------------- -+---------------------------------+-----+
364 | 13 | TYPE='SUBFUNCTION' & | Set REGION_CODE to null | 114 |
365 | | REGION_CODE has data & | | |
366 | | FORM_ID is null& | | |
367 | | PARAMETERS is null & | | |
368 | | WEB_HTML_CALL is null & | | |
369 +-----+----------------------------- -+---------------------------------+-----+
370 */
371 if ( type = 'SUBFUNCTION'
372 and form_id is null
373 and parameters is null
374 and web_html_call is null
375 and region_code is not null) then
376 region_code := '';
377 columns_name := columns_name||'REGION_CODE = null'||fnd_global.newline;
378 end if;
379
380 /*
381 +-----+----------------------------- -+---------------------------------+-----+
382 | 14 | TYPE='SUBFUNCTION' & | Change TYPE to JSP | 6 |
383 | | WEB_HTML_CALL has data & | | |
384 | | WEB_HTML_CALL like '%jsp%' | | |
385 | | REGION_CODE is null & | | |
386 | | FORM_ID is null & | | |
387 | | PARAMETERS is null | | |
388 +-----+-------------------------------+---------------------------------+-----+
389 */
390 if ( type = 'SUBFUNCTION'
391 and form_id is null
392 and parameters is null
393 and web_html_call is not null
394 and upper(web_html_call) like '%JSP%'
395 and region_code is null) then
396 type := 'JSP';
397 columns_name := columns_name||'TYPE = JSP'||fnd_global.newline;
398 end if;
399
400 /*
401 +-----+-------------------------------+---------------------------------+-----+
402 | 15 | TYPE='SUBFUNCTION' & | Change TYPE to UNKNOWN | 1 |
403 | | WEB_HTML_CALL has data & | | |
404 | | WEB_HTML_CALL like '%mailto%' | | |
405 | | REGION_CODE is null & | | |
406 | | FORM_ID is null & | | |
407 | | PARAMETERS is null | | |
408 +-----+-------------------------------+---------------------------------+-----+
409 */
410 if ( type = 'SUBFUNCTION'
411 and form_id is null
412 and parameters is null
413 and web_html_call is not null
414 and upper(web_html_call) not like '%JSP%'
415 and region_code is null) then
416 type := 'UNKNOWN';
417 columns_name := columns_name||'TYPE = UNKNOWN'||fnd_global.newline;
418 end if;
419
420 /*
421 +-----+-------------------------------+---------------------------------+-----+
422 | 16 | TYPE='SUBFUNCTION' & | Change TYPE to FORM | 16 |
423 | | FORM_ID has data & | | |
424 | | PARAMETERS has data & | | |
425 | | WEB_HTML_CALL is null & | | |
426 | | REGION_CODE is null | | |
427 +-----+-------------------------------+---------------------------------+-----+
428 */
429 if ( type = 'SUBFUNCTION'
430 and form_id is not null
431 and parameters is not null
432 and web_html_call is null
433 and region_code is null ) then
434 type := 'FORM';
435 columns_name := columns_name||'TYPE = FORM'||fnd_global.newline;
436 end if;
437
438 /*
439 +-----+-------------------------------+---------------------------------+-----+
440 | 17 | TYPE='JTFWEBPORTLET' & | Change TYPE to UNKNOWN | 3 |
441 | | WEB_HTML_CALL is null | | |
442 +-----+-------------------------------+---------------------------------+-----+
443 */
444 if ( type = 'JTFWEBPORTLET'
445 and web_html_call is null) then
446 type := 'UNKNOWN';
447 columns_name := columns_name||'TYPE = UNKNOWN'||fnd_global.newline;
448 end if;
449
450 /*
451 +-----+-------------------------------+---------------------------------+-----+
452 | 18 | TYPE='INTEROPJSP' & | Change TYPE to UNKNOWN | 5 |
453 | | WEB_HTML_CALL is null | | |
454 +-----+-------------------------------+---------------------------------+-----+
455 */
456 if ( type = 'INTEROPJSP'
457 and web_html_call is null) then
458 type := 'UNKNOWN';
459 columns_name := columns_name||'TYPE = UNKNOWN'||fnd_global.newline;
460 end if;
461
462 /*
463 +-----+-------------------------------+---------------------------------+-----+
464 | 20 | TYPE='OBJECT' | Change TYPE to UNKNOWN | 26 |
465 +-----+-------------------------------+---------------------------------+-----+
466 */
470 end if;
467 if (type = 'OBJECT') then
468 type := 'UNKNOWN';
469 columns_name := columns_name||'TYPE = UNKNOWN'||fnd_global.newline;
471
472 /*
473 +-----+-------------------------------+---------------------------------+-----+
474 | 21 | TYPE='HTML' & | Change TYPE to JSP | 2 |
475 | | WEB_HTML_CALL like %jsp% | | |
476 +-----+-------------------------------+---------------------------------+-----+
477 */
478 if (type = 'HTML') then
479 type := 'UNKNOWN';
480 columns_name := columns_name||'TYPE = UNKNOWN'||fnd_global.newline;
481 end if;
482
483 /* Bug4507567 - Added for R12
484 +-----+-------------------------------+---------------------------------+-----+
485 | | TYPE='INTERFACE'|| | Change TYPE to UNKNOWN | |
486 | | TYPE='SB_INDIRECT_OP'& | | |
487 | | irep_method_name is null | | |
488 | | irep_scope is null | | |
489 | | irep_lifecycle is null | | |
490 | | irep_description is null | | |
491 | | Irep_class_id is null | | |
492 +-----+-------------------------------+---------------------------------+-----+
493
494 if ((type = 'INTERFACE' or type = 'SB_INDIRECT_OP')
495 and irep_method_name is null
496 and irep_scope is null
497 and irep_lifecycle is null
498 and irep_description is null
499 and irep_class_id is null) then
500 type := 'UNKNOWN';
501 columns_name := columns_name||'TYPE = UNKNOWN'||fnd_global.newline;
502 end if;
503 */
504
505 return(columns_name);
506
507 end FUNCTION_VALIDATION;
508
509
510 procedure INSERT_ROW (
511 X_ROWID in out nocopy VARCHAR2,
512 X_FUNCTION_ID in NUMBER,
513 X_WEB_HOST_NAME in VARCHAR2,
514 X_WEB_AGENT_NAME in VARCHAR2,
515 X_WEB_HTML_CALL in VARCHAR2,
516 X_WEB_ENCRYPT_PARAMETERS in VARCHAR2,
517 X_WEB_SECURED in VARCHAR2,
518 X_WEB_ICON in VARCHAR2,
519 X_OBJECT_ID in NUMBER,
520 X_REGION_APPLICATION_ID in NUMBER,
521 X_REGION_CODE in VARCHAR2,
522 X_FUNCTION_NAME in VARCHAR2,
523 X_APPLICATION_ID in NUMBER,
524 X_FORM_ID in NUMBER,
525 X_PARAMETERS in VARCHAR2,
526 X_TYPE in VARCHAR2,
527 X_USER_FUNCTION_NAME in VARCHAR2,
528 X_DESCRIPTION in VARCHAR2,
529 X_CREATION_DATE in DATE,
530 X_CREATED_BY in NUMBER,
531 X_LAST_UPDATE_DATE in DATE,
532 X_LAST_UPDATED_BY in NUMBER,
533 X_LAST_UPDATE_LOGIN in NUMBER
534 ) is
535 begin
536 fnd_form_functions_pkg.INSERT_ROW(
537 X_ROWID => X_ROWID,
538 X_FUNCTION_ID => X_FUNCTION_ID,
539 X_WEB_HOST_NAME => X_WEB_HOST_NAME,
540 X_WEB_AGENT_NAME => X_WEB_AGENT_NAME,
541 X_WEB_HTML_CALL => X_WEB_HTML_CALL,
542 X_WEB_ENCRYPT_PARAMETERS => X_WEB_ENCRYPT_PARAMETERS,
543 X_WEB_SECURED => X_WEB_SECURED,
544 X_WEB_ICON => X_WEB_ICON,
545 X_OBJECT_ID => X_OBJECT_ID,
546 X_REGION_APPLICATION_ID => X_REGION_APPLICATION_ID,
547 X_REGION_CODE => X_REGION_CODE,
548 X_FUNCTION_NAME => X_FUNCTION_NAME,
549 X_APPLICATION_ID => X_APPLICATION_ID,
550 X_FORM_ID => X_FORM_ID,
551 X_PARAMETERS => X_PARAMETERS,
552 X_TYPE => X_TYPE,
553 X_USER_FUNCTION_NAME => X_USER_FUNCTION_NAME,
554 X_DESCRIPTION => X_DESCRIPTION,
555 X_CREATION_DATE => X_CREATION_DATE,
556 X_CREATED_BY => X_CREATED_BY,
557 X_LAST_UPDATE_DATE => X_LAST_UPDATE_DATE,
558 X_LAST_UPDATED_BY => X_LAST_UPDATED_BY,
559 X_LAST_UPDATE_LOGIN => X_LAST_UPDATE_LOGIN,
560 X_MAINTENANCE_MODE_SUPPORT => NULL,
561 X_CONTEXT_DEPENDENCE => NULL,
562 X_JRAD_REF_PATH => NULL);
563 end INSERT_ROW;
564
565 procedure LOCK_ROW (
566 X_FUNCTION_ID in NUMBER,
567 X_WEB_HOST_NAME in VARCHAR2,
568 X_WEB_AGENT_NAME in VARCHAR2,
569 X_WEB_HTML_CALL in VARCHAR2,
570 X_WEB_ENCRYPT_PARAMETERS in VARCHAR2,
571 X_WEB_SECURED in VARCHAR2,
572 X_WEB_ICON in VARCHAR2,
573 X_OBJECT_ID in NUMBER,
574 X_REGION_APPLICATION_ID in NUMBER,
575 X_REGION_CODE in VARCHAR2,
576 X_FUNCTION_NAME in VARCHAR2,
577 X_APPLICATION_ID in NUMBER,
578 X_FORM_ID in NUMBER,
579 X_PARAMETERS in VARCHAR2,
580 X_TYPE in VARCHAR2,
581 X_USER_FUNCTION_NAME in VARCHAR2,
582 X_DESCRIPTION in VARCHAR2
583 ) is
584 begin
585 fnd_form_functions_pkg.LOCK_ROW(
586 X_FUNCTION_ID => X_FUNCTION_ID,
587 X_WEB_HOST_NAME => X_WEB_HOST_NAME,
588 X_WEB_AGENT_NAME => X_WEB_AGENT_NAME,
589 X_WEB_HTML_CALL => X_WEB_HTML_CALL,
590 X_WEB_ENCRYPT_PARAMETERS => X_WEB_ENCRYPT_PARAMETERS,
591 X_WEB_SECURED => X_WEB_SECURED,
592 X_WEB_ICON => X_WEB_ICON,
593 X_OBJECT_ID => X_OBJECT_ID,
594 X_REGION_APPLICATION_ID => X_REGION_APPLICATION_ID,
595 X_REGION_CODE => X_REGION_CODE,
596 X_FUNCTION_NAME => X_FUNCTION_NAME,
597 X_APPLICATION_ID => X_APPLICATION_ID,
598 X_FORM_ID => X_FORM_ID,
599 X_PARAMETERS => X_PARAMETERS,
600 X_TYPE => X_TYPE,
601 X_USER_FUNCTION_NAME => X_USER_FUNCTION_NAME,
602 X_DESCRIPTION => X_DESCRIPTION,
606
603 X_MAINTENANCE_MODE_SUPPORT => NULL,
604 X_CONTEXT_DEPENDENCE => NULL,
605 X_JRAD_REF_PATH => NULL);
607 end LOCK_ROW;
608
609 procedure UPDATE_ROW (
610 X_FUNCTION_ID in NUMBER,
611 X_WEB_HOST_NAME in VARCHAR2,
612 X_WEB_AGENT_NAME in VARCHAR2,
613 X_WEB_HTML_CALL in VARCHAR2,
614 X_WEB_ENCRYPT_PARAMETERS in VARCHAR2,
615 X_WEB_SECURED in VARCHAR2,
616 X_WEB_ICON in VARCHAR2,
617 X_OBJECT_ID in NUMBER,
618 X_REGION_APPLICATION_ID in NUMBER,
619 X_REGION_CODE in VARCHAR2,
620 X_FUNCTION_NAME in VARCHAR2,
621 X_APPLICATION_ID in NUMBER,
622 X_FORM_ID in NUMBER,
623 X_PARAMETERS in VARCHAR2,
624 X_TYPE in VARCHAR2,
625 X_USER_FUNCTION_NAME in VARCHAR2,
626 X_DESCRIPTION in VARCHAR2,
627 X_LAST_UPDATE_DATE in DATE,
628 X_LAST_UPDATED_BY in NUMBER,
629 X_LAST_UPDATE_LOGIN in NUMBER
630 ) is
631 begin
632 fnd_form_functions_pkg.UPDATE_ROW(
633 X_FUNCTION_ID => X_FUNCTION_ID,
634 X_WEB_HOST_NAME => X_WEB_HOST_NAME,
635 X_WEB_AGENT_NAME => X_WEB_AGENT_NAME,
636 X_WEB_HTML_CALL => X_WEB_HTML_CALL,
637 X_WEB_ENCRYPT_PARAMETERS => X_WEB_ENCRYPT_PARAMETERS,
638 X_WEB_SECURED => X_WEB_SECURED,
639 X_WEB_ICON => X_WEB_ICON,
640 X_OBJECT_ID => X_OBJECT_ID,
641 X_REGION_APPLICATION_ID => X_REGION_APPLICATION_ID,
642 X_REGION_CODE => X_REGION_CODE,
643 X_FUNCTION_NAME => X_FUNCTION_NAME,
644 X_APPLICATION_ID => X_APPLICATION_ID,
645 X_FORM_ID => X_FORM_ID,
646 X_PARAMETERS => X_PARAMETERS,
647 X_TYPE => X_TYPE,
648 X_USER_FUNCTION_NAME => X_USER_FUNCTION_NAME,
649 X_DESCRIPTION => X_DESCRIPTION,
650 X_LAST_UPDATE_DATE => X_LAST_UPDATE_DATE,
651 X_LAST_UPDATED_BY => X_LAST_UPDATED_BY,
652 X_LAST_UPDATE_LOGIN => X_LAST_UPDATE_LOGIN,
653 X_MAINTENANCE_MODE_SUPPORT => NULL,
654 X_CONTEXT_DEPENDENCE => NULL,
655 X_JRAD_REF_PATH => NULL);
656 end UPDATE_ROW;
657
658 /* Overloaded version below */
659 procedure LOAD_ROW (
660 X_FUNCTION_NAME in VARCHAR2,
661 X_APPLICATION_SHORT_NAME in VARCHAR2,
662 X_FORM_NAME in VARCHAR2,
663 X_PARAMETERS in VARCHAR2,
664 X_TYPE in VARCHAR2,
665 X_WEB_HOST_NAME in VARCHAR2,
666 X_WEB_AGENT_NAME in VARCHAR2,
667 X_WEB_HTML_CALL in VARCHAR2,
668 X_WEB_ENCRYPT_PARAMETERS in VARCHAR2,
669 X_WEB_SECURED in VARCHAR2,
670 X_WEB_ICON in VARCHAR2,
671 X_OBJECT_NAME in VARCHAR2,
672 X_REGION_APPLICATION_NAME in VARCHAR2,
673 X_REGION_CODE in VARCHAR2,
674 X_USER_FUNCTION_NAME in VARCHAR2,
675 X_DESCRIPTION in VARCHAR2,
676 X_OWNER in VARCHAR2,
677 X_CUSTOM_MODE in VARCHAR2
678 ) is
679 begin
680 fnd_form_functions_pkg.LOAD_ROW (
681 X_FUNCTION_NAME => X_FUNCTION_NAME,
682 X_APPLICATION_SHORT_NAME => X_APPLICATION_SHORT_NAME,
683 X_FORM_NAME => X_FORM_NAME,
684 X_PARAMETERS => X_PARAMETERS,
685 X_TYPE => X_TYPE,
686 X_WEB_HOST_NAME => X_WEB_HOST_NAME,
687 X_WEB_AGENT_NAME => X_WEB_AGENT_NAME,
688 X_WEB_HTML_CALL => X_WEB_HTML_CALL,
689 X_WEB_ENCRYPT_PARAMETERS => X_WEB_ENCRYPT_PARAMETERS,
690 X_WEB_SECURED => X_WEB_SECURED,
691 X_WEB_ICON => X_WEB_ICON,
692 X_OBJECT_NAME => X_OBJECT_NAME,
693 X_REGION_APPLICATION_NAME => X_REGION_APPLICATION_NAME,
694 X_REGION_CODE => X_REGION_CODE,
695 X_USER_FUNCTION_NAME => X_USER_FUNCTION_NAME,
696 X_DESCRIPTION => X_DESCRIPTION,
697 X_OWNER => X_OWNER,
698 X_CUSTOM_MODE => X_CUSTOM_MODE,
699 X_LAST_UPDATE_DATE => NULL,
700 X_MAINTENANCE_MODE_SUPPORT => NULL,
701 X_CONTEXT_DEPENDENCE => NULL,
702 X_JRAD_REF_PATH => NULL
703 );
704 end LOAD_ROW;
705
706 /* Overloaded version above */
707 procedure LOAD_ROW (
708 X_FUNCTION_NAME in VARCHAR2,
709 X_APPLICATION_SHORT_NAME in VARCHAR2,
710 X_FORM_NAME in VARCHAR2,
711 X_PARAMETERS in VARCHAR2,
712 X_TYPE in VARCHAR2,
713 X_WEB_HOST_NAME in VARCHAR2,
714 X_WEB_AGENT_NAME in VARCHAR2,
715 X_WEB_HTML_CALL in VARCHAR2,
716 X_WEB_ENCRYPT_PARAMETERS in VARCHAR2,
717 X_WEB_SECURED in VARCHAR2,
718 X_WEB_ICON in VARCHAR2,
719 X_OBJECT_NAME in VARCHAR2,
720 X_REGION_APPLICATION_NAME in VARCHAR2,
721 X_REGION_CODE in VARCHAR2,
722 X_USER_FUNCTION_NAME in VARCHAR2,
723 X_DESCRIPTION in VARCHAR2,
724 X_OWNER in VARCHAR2,
725 X_CUSTOM_MODE in VARCHAR2,
726 X_LAST_UPDATE_DATE in VARCHAR2,
727 X_MAINTENANCE_MODE_SUPPORT in VARCHAR2, /* NULL='NONE' */
728 X_CONTEXT_DEPENDENCE in VARCHAR2, /* NULL='RESP' */
729 X_JRAD_REF_PATH in VARCHAR2 default NULL
730 ) is
731 -- The following four variables could be null;
732 app_id number;
733 rapp_id number;
734 frm_id number;
735 obj_id number;
736 fun_id number;
737 row_id varchar2(64);
738 f_luby number; -- entity owner in file
739 f_ludate date; -- entity update date in file
740 db_luby number; -- entity owner in db
741 db_ludate date; -- entity update date in db
742 l_form_name varchar2(4000); -- bug2438503
743 l_object_name varchar2(4000); -- bug2438503
744 l_region_application_name varchar2(4000); -- bug2438503
745 l_parameters varchar2(4000); --bug2662933 added the following variables.
746 l_web_host_name varchar2(4000);
750 l_web_secured varchar2(4000);
747 l_web_agent_name varchar2(4000);
748 l_web_html_call varchar2(4000);
749 l_web_encrypt_parameters varchar2(4000);
751 l_web_icon varchar2(4000);
752 l_region_code varchar2(4000);
753
754 L_MAINTENANCE_MODE_SUPPORT VARCHAR2(8) := X_MAINTENANCE_MODE_SUPPORT;
755 L_CONTEXT_DEPENDENCE VARCHAR2(8) := X_CONTEXT_DEPENDENCE;
756
757 -- added for adding new column JRAD_REF_PATH for bug 2747925
758 L_JRAD_REF_PATH VARCHAR2(1000) := X_JRAD_REF_PATH;
759
760 begin
761 if (L_MAINTENANCE_MODE_SUPPORT is NULL) then
762 L_MAINTENANCE_MODE_SUPPORT := 'NONE';
763 end if;
764
765 if (L_CONTEXT_DEPENDENCE is NULL) then
766 L_CONTEXT_DEPENDENCE := 'RESP';
767 end if;
768
769
770 begin
771 select application_id into app_id
772 from fnd_application
773 where application_short_name = X_APPLICATION_SHORT_NAME;
774 exception when no_data_found then
775 app_id := null;
776 end;
777
778 -- Bug2662933 Get possible null values before calling UPDATE_ROW.
779
780 select decode(X_PARAMETERS, fnd_load_util.null_value, null,
781 null, X_PARAMETERS, X_PARAMETERS),
782 decode(X_WEB_HOST_NAME, fnd_load_util.null_value, null,
783 null, X_WEB_HOST_NAME, X_WEB_HOST_NAME),
784 decode(X_WEB_AGENT_NAME, fnd_load_util.null_value, null,
785 null, X_WEB_AGENT_NAME, X_WEB_AGENT_NAME),
786 decode(X_WEB_HTML_CALL, fnd_load_util.null_value, null,
787 null, X_WEB_HTML_CALL, X_WEB_HTML_CALL),
788 decode(X_WEB_ENCRYPT_PARAMETERS, fnd_load_util.null_value, null,
789 null, X_WEB_ENCRYPT_PARAMETERS, X_WEB_ENCRYPT_PARAMETERS),
790 decode(X_WEB_SECURED, fnd_load_util.null_value, null,
791 null, X_WEB_SECURED, X_WEB_SECURED),
792 decode(X_WEB_ICON, fnd_load_util.null_value, null,
793 null, X_WEB_ICON, X_WEB_ICON),
794 decode(X_REGION_CODE, fnd_load_util.null_value, null,
795 null, X_REGION_CODE, X_REGION_CODE),
796 decode(X_JRAD_REF_PATH, fnd_load_util.null_value, null,
797 null, X_JRAD_REF_PATH, X_JRAD_REF_PATH)
798 into l_parameters, l_web_host_name, l_web_agent_name,
799 l_web_html_call, l_web_encrypt_parameters, l_web_secured,
800 l_web_icon, l_region_code, L_JRAD_REF_PATH
801 from dual;
802
803 select decode(X_FORM_NAME,
804 fnd_load_util.null_value, null,
805 null, X_FORM_NAME,
806 X_FORM_NAME) into l_form_name from dual;
807
808 if (l_form_name is not null) then
809 begin
810 select form_id into frm_id
811 from fnd_form
812 where form_name = X_FORM_NAME
813 and application_id = app_id;
814 exception when no_data_found then
815 frm_id := null;
816 end;
817 else frm_id := null;
818 end if;
819
820 select decode(X_OBJECT_NAME,
821 fnd_load_util.null_value, null,
822 null, X_OBJECT_NAME,
823 X_OBJECT_NAME) into l_object_name from dual;
824
825 if (l_object_name is not null) then
826 begin
827 select object_id into obj_id
828 from fnd_objects
829 where obj_name = X_OBJECT_NAME;
830 exception when no_data_found then
831 obj_id := null;
832 end;
833 else obj_id := null;
834 end if;
835
836 select decode(X_REGION_APPLICATION_NAME,
837 fnd_load_util.null_value, null,
838 null, X_REGION_APPLICATION_NAME,
839 X_REGION_APPLICATION_NAME)
840 into l_region_application_name from dual;
841
842 if (l_region_application_name is not null) then
843 begin
844 select application_id into rapp_id
845 from fnd_application
846 where application_short_name = X_REGION_APPLICATION_NAME;
847 exception when no_data_found then
848 rapp_id := null;
849 end;
850 else rapp_id := null;
851 end if;
852
853 -- Translate owner to file_last_updated_by
854 f_luby := fnd_load_util.owner_id(x_owner);
855
856 -- Translate char last_update_date to date
857 f_ludate := nvl(to_date(x_last_update_date, 'YYYY/MM/DD'), sysdate);
858
859 select function_id, last_updated_by, last_update_date
860 into fun_id, db_luby, db_ludate
861 from fnd_form_functions
862 where function_name = X_FUNCTION_NAME;
863
864 if (fnd_load_util.upload_test(f_luby, f_ludate, db_luby,
865 db_ludate, X_CUSTOM_MODE)) then
866 fnd_form_functions_pkg.UPDATE_ROW (
867 X_FUNCTION_ID => fun_id,
868 X_WEB_HOST_NAME => l_WEB_HOST_NAME,
869 X_WEB_AGENT_NAME => l_WEB_AGENT_NAME,
870 X_WEB_HTML_CALL => l_WEB_HTML_CALL,
871 X_WEB_ENCRYPT_PARAMETERS => l_WEB_ENCRYPT_PARAMETERS,
872 X_WEB_SECURED => l_WEB_SECURED,
873 X_WEB_ICON => l_WEB_ICON,
874 X_OBJECT_ID => obj_id,
875 X_REGION_APPLICATION_ID => rapp_id,
876 X_REGION_CODE => l_REGION_CODE,
877 X_FUNCTION_NAME => X_FUNCTION_NAME,
878 X_APPLICATION_ID => app_id,
879 X_FORM_ID => frm_id,
880 X_PARAMETERS => l_PARAMETERS,
881 X_TYPE => X_TYPE,
882 X_USER_FUNCTION_NAME => X_USER_FUNCTION_NAME,
883 X_DESCRIPTION => X_DESCRIPTION,
884 X_LAST_UPDATE_DATE => f_ludate,
885 X_LAST_UPDATED_BY => f_luby,
886 X_LAST_UPDATE_LOGIN => 0,
887 X_MAINTENANCE_MODE_SUPPORT => L_MAINTENANCE_MODE_SUPPORT,
888 X_CONTEXT_DEPENDENCE => L_CONTEXT_DEPENDENCE,
889 X_JRAD_REF_PATH => L_JRAD_REF_PATH);
890 end if;
894
891
892 exception
893 when NO_DATA_FOUND then
895 select fnd_form_functions_s.nextval into fun_id from dual;
896
897 fnd_form_functions_pkg.INSERT_ROW(
898 X_ROWID => row_id,
899 X_FUNCTION_ID => fun_id,
900 X_WEB_HOST_NAME => l_WEB_HOST_NAME,
901 X_WEB_AGENT_NAME => l_WEB_AGENT_NAME,
902 X_WEB_HTML_CALL => l_WEB_HTML_CALL,
903 X_WEB_ENCRYPT_PARAMETERS => l_WEB_ENCRYPT_PARAMETERS,
904 X_WEB_SECURED => l_WEB_SECURED,
905 X_WEB_ICON => l_WEB_ICON,
906 X_OBJECT_ID => obj_id,
907 X_REGION_APPLICATION_ID => rapp_id,
908 X_REGION_CODE => l_REGION_CODE,
909 X_FUNCTION_NAME => X_FUNCTION_NAME,
910 X_APPLICATION_ID => app_id,
911 X_FORM_ID => frm_id,
912 X_PARAMETERS => l_PARAMETERS,
913 X_TYPE => X_TYPE,
914 X_USER_FUNCTION_NAME => X_USER_FUNCTION_NAME,
915 X_DESCRIPTION => X_DESCRIPTION,
916 X_CREATION_DATE => f_ludate,
917 X_CREATED_BY => f_luby,
918 X_LAST_UPDATE_DATE => f_ludate,
919 X_LAST_UPDATED_BY => f_luby,
920 X_LAST_UPDATE_LOGIN => 0,
921 X_MAINTENANCE_MODE_SUPPORT => L_MAINTENANCE_MODE_SUPPORT,
922 X_CONTEXT_DEPENDENCE => L_CONTEXT_DEPENDENCE,
923 X_JRAD_REF_PATH => L_JRAD_REF_PATH);
924 end LOAD_ROW;
925
926 procedure DELETE_ROW (
927 X_FUNCTION_ID in NUMBER
928 ) is
929 begin
930 delete from FND_FORM_FUNCTIONS
931 where FUNCTION_ID = X_FUNCTION_ID;
932
933 if (sql%notfound) then
934 raise no_data_found;
935 else
936 -- This means that a function was deleted.
937 -- Added for Function Security Cache Invalidation Project
938 fnd_function_security_cache.delete_function(X_FUNCTION_ID);
939 end if;
940
941 delete from FND_FORM_FUNCTIONS_TL
942 where FUNCTION_ID = X_FUNCTION_ID;
943
944 if (sql%notfound) then
945 raise no_data_found;
946 end if;
947 end DELETE_ROW;
948
949 procedure ADD_LANGUAGE
950 is
951 begin
952 /* Mar/19/03 requested by Ric Ginsberg */
953 /* The following delete and update statements are commented out */
954 /* as a quick workaround to fix the time-consuming table handler issue */
955 /* Eventually we'll need to turn them into a separate fix_language procedure */
956 /*
957
958 delete from FND_FORM_FUNCTIONS_TL T
959 where not exists
960 (select NULL
961 from FND_FORM_FUNCTIONS B
962 where B.FUNCTION_ID = T.FUNCTION_ID
963 );
964
965 update FND_FORM_FUNCTIONS_TL T set (
966 USER_FUNCTION_NAME,
967 DESCRIPTION
968 ) = (select
969 B.USER_FUNCTION_NAME,
970 B.DESCRIPTION
971 from FND_FORM_FUNCTIONS_TL B
972 where B.FUNCTION_ID = T.FUNCTION_ID
973 and B.LANGUAGE = T.SOURCE_LANG)
974 where (
975 T.FUNCTION_ID,
976 T.LANGUAGE
977 ) in (select
978 SUBT.FUNCTION_ID,
979 SUBT.LANGUAGE
980 from FND_FORM_FUNCTIONS_TL SUBB, FND_FORM_FUNCTIONS_TL SUBT
981 where SUBB.FUNCTION_ID = SUBT.FUNCTION_ID
982 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
983 and (SUBB.USER_FUNCTION_NAME <> SUBT.USER_FUNCTION_NAME
984 or SUBB.DESCRIPTION <> SUBT.DESCRIPTION
985 or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
986 or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
987 ));
988 */
989
990 insert /*+ append parallel(TT) */ into
991 FND_FORM_FUNCTIONS_TL TT(
992 FUNCTION_ID,
993 USER_FUNCTION_NAME,
994 CREATION_DATE,
995 CREATED_BY,
996 LAST_UPDATE_DATE,
997 LAST_UPDATED_BY,
998 LAST_UPDATE_LOGIN,
999 DESCRIPTION,
1000 LANGUAGE,
1001 SOURCE_LANG
1002 ) select /*+ parallel(V) parallel(T) use_nl(T) */ V.* from
1003 ( select /*+ no_merge ordered parallel(B) */
1004 B.FUNCTION_ID,
1005 B.USER_FUNCTION_NAME,
1006 B.CREATION_DATE,
1007 B.CREATED_BY,
1008 B.LAST_UPDATE_DATE,
1009 B.LAST_UPDATED_BY,
1010 B.LAST_UPDATE_LOGIN,
1011 B.DESCRIPTION,
1012 L.LANGUAGE_CODE,
1013 B.SOURCE_LANG
1014 from FND_FORM_FUNCTIONS_TL B, FND_LANGUAGES L
1015 where L.INSTALLED_FLAG in ('I', 'B')
1016 and B.LANGUAGE = userenv('LANG')
1017 )V, FND_FORM_FUNCTIONS_TL T
1018 where T.function_id(+) = V.function_id
1019 and T.language(+) = V.language_code
1020 and T.function_id is NULL;
1021 end ADD_LANGUAGE;
1022
1023 /* Overloaded version below */
1024 procedure TRANSLATE_ROW (
1025 X_FUNCTION_ID in NUMBER,
1026 X_USER_FUNCTION_NAME in VARCHAR2,
1027 X_DESCRIPTION in VARCHAR2,
1028 X_OWNER in VARCHAR2,
1029 X_CUSTOM_MODE in VARCHAR2
1030 ) is
1031 begin
1032 fnd_form_functions_pkg.TRANSLATE_ROW (
1033 X_FUNCTION_ID => X_FUNCTION_ID,
1034 X_USER_FUNCTION_NAME => X_USER_FUNCTION_NAME,
1035 X_DESCRIPTION => X_DESCRIPTION,
1036 X_OWNER => X_OWNER,
1037 X_CUSTOM_MODE => X_CUSTOM_MODE,
1038 X_LAST_UPDATE_DATE => null
1039 );
1040 end TRANSLATE_ROW;
1041
1042 /* Overloaded version above */
1043 procedure TRANSLATE_ROW (
1044 X_FUNCTION_ID in NUMBER,
1045 X_USER_FUNCTION_NAME in VARCHAR2,
1046 X_DESCRIPTION in VARCHAR2,
1047 X_OWNER in VARCHAR2,
1048 X_CUSTOM_MODE in VARCHAR2,
1049 X_LAST_UPDATE_DATE in VARCHAR2
1050 ) is
1054 db_ludate date; -- entity update date in db
1051 f_luby number; -- entity owner in file
1052 f_ludate date; -- entity update date in file
1053 db_luby number; -- entity owner in db
1055 begin
1056 -- Translate owner to file_last_updated_by
1057 f_luby := fnd_load_util.owner_id(x_owner);
1058
1059 -- Translate char last_update_date to date
1060 f_ludate := nvl(to_date(x_last_update_date, 'YYYY/MM/DD'), sysdate);
1061
1062 select LAST_UPDATED_BY, LAST_UPDATE_DATE
1063 into db_luby, db_ludate
1064 from FND_FORM_FUNCTIONS_TL
1065 where FUNCTION_ID = X_FUNCTION_ID
1066 and userenv('LANG') = LANGUAGE;
1067
1068 if (fnd_load_util.upload_test(f_luby, f_ludate, db_luby,
1069 db_ludate, X_CUSTOM_MODE)) then
1070 update FND_FORM_FUNCTIONS_TL set
1071 USER_FUNCTION_NAME = X_USER_FUNCTION_NAME,
1072 DESCRIPTION = X_DESCRIPTION,
1073 LAST_UPDATE_DATE = f_ludate,
1074 LAST_UPDATED_BY = f_luby,
1075 LAST_UPDATE_LOGIN = 0,
1076 SOURCE_LANG = userenv('LANG')
1077 where FUNCTION_ID = X_FUNCTION_ID
1078 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
1079 end if;
1080
1081 if (sql%notfound) then
1082 raise no_data_found;
1083 end if;
1084 end TRANSLATE_ROW;
1085
1086 procedure INSERT_ROW (
1087 X_ROWID in out nocopy VARCHAR2,
1088 X_FUNCTION_ID in NUMBER,
1089 X_WEB_HOST_NAME in VARCHAR2,
1090 X_WEB_AGENT_NAME in VARCHAR2,
1091 X_WEB_HTML_CALL in VARCHAR2,
1092 X_WEB_ENCRYPT_PARAMETERS in VARCHAR2,
1093 X_WEB_SECURED in VARCHAR2,
1094 X_WEB_ICON in VARCHAR2,
1095 X_OBJECT_ID in NUMBER,
1096 X_REGION_APPLICATION_ID in NUMBER,
1097 X_REGION_CODE in VARCHAR2,
1098 X_FUNCTION_NAME in VARCHAR2,
1099 X_APPLICATION_ID in NUMBER,
1100 X_FORM_ID in NUMBER,
1101 X_PARAMETERS in VARCHAR2,
1102 X_TYPE in VARCHAR2,
1103 X_USER_FUNCTION_NAME in VARCHAR2,
1104 X_DESCRIPTION in VARCHAR2,
1105 X_CREATION_DATE in DATE,
1106 X_CREATED_BY in NUMBER,
1107 X_LAST_UPDATE_DATE in DATE,
1108 X_LAST_UPDATED_BY in NUMBER,
1109 X_LAST_UPDATE_LOGIN in NUMBER,
1110 X_MAINTENANCE_MODE_SUPPORT in VARCHAR2, /* NULL='NONE' */
1111 X_CONTEXT_DEPENDENCE in VARCHAR2, /* NULL='RESP' */
1112 X_JRAD_REF_PATH in VARCHAR2 default NULL
1113 ) is
1114 cursor C is select ROWID from FND_FORM_FUNCTIONS
1115 where FUNCTION_ID = X_FUNCTION_ID;
1116 L_MAINTENANCE_MODE_SUPPORT VARCHAR2(8) := X_MAINTENANCE_MODE_SUPPORT;
1117 L_CONTEXT_DEPENDENCE VARCHAR2(8) := X_CONTEXT_DEPENDENCE;
1118
1119 -- Function type validation purpose arguments
1120 L_APPLICATION_ID NUMBER;
1121 L_FORM_ID NUMBER;
1122 L_TYPE VARCHAR2(30);
1123 L_PARAMETERS VARCHAR2(2000);
1124 L_WEB_HTML_CALL VARCHAR2(240);
1125 L_REGION_APPLICATION_ID NUMBER;
1126 L_REGION_CODE VARCHAR2(30);
1127 columns_name VARCHAR2(2000);
1128
1129 begin
1130 if (L_MAINTENANCE_MODE_SUPPORT is NULL) then
1131 L_MAINTENANCE_MODE_SUPPORT := 'NONE';
1132 end if;
1133
1134 if (L_CONTEXT_DEPENDENCE is NULL) then
1135 L_CONTEXT_DEPENDENCE := 'RESP';
1136 end if;
1137
1138 -- Function type validation
1139 L_APPLICATION_ID := X_APPLICATION_ID;
1140 L_FORM_ID := X_FORM_ID;
1141 L_TYPE := X_TYPE;
1142 L_PARAMETERS := X_PARAMETERS;
1143 L_WEB_HTML_CALL := X_WEB_HTML_CALL;
1144 L_REGION_APPLICATION_ID := X_REGION_APPLICATION_ID;
1145 L_REGION_CODE := X_REGION_CODE;
1146
1147 columns_name := FUNCTION_VALIDATION(L_APPLICATION_ID,
1148 L_FORM_ID,
1149 L_TYPE,
1150 L_PARAMETERS,
1151 L_WEB_HTML_CALL,
1152 X_WEB_HOST_NAME,
1153 L_REGION_APPLICATION_ID,
1154 L_REGION_CODE,
1155 X_FUNCTION_NAME);
1156
1157 insert into FND_FORM_FUNCTIONS (
1158 WEB_ICON,
1159 WEB_HOST_NAME,
1160 WEB_AGENT_NAME,
1161 WEB_HTML_CALL,
1162 WEB_ENCRYPT_PARAMETERS,
1163 WEB_SECURED,
1164 OBJECT_ID,
1165 REGION_APPLICATION_ID,
1166 REGION_CODE,
1167 FUNCTION_ID,
1168 FUNCTION_NAME,
1169 APPLICATION_ID,
1170 FORM_ID,
1171 PARAMETERS,
1172 TYPE,
1173 CREATION_DATE,
1174 CREATED_BY,
1175 LAST_UPDATE_DATE,
1176 LAST_UPDATED_BY,
1177 LAST_UPDATE_LOGIN,
1178 MAINTENANCE_MODE_SUPPORT,
1179 CONTEXT_DEPENDENCE,
1180 JRAD_REF_PATH
1181 ) values (
1182 X_WEB_ICON,
1183 X_WEB_HOST_NAME,
1184 X_WEB_AGENT_NAME,
1185 L_WEB_HTML_CALL,
1186 X_WEB_ENCRYPT_PARAMETERS,
1187 X_WEB_SECURED,
1188 X_OBJECT_ID,
1189 L_REGION_APPLICATION_ID,
1190 L_REGION_CODE,
1191 X_FUNCTION_ID,
1192 X_FUNCTION_NAME,
1193 L_APPLICATION_ID,
1194 L_FORM_ID,
1195 L_PARAMETERS,
1196 L_TYPE,
1197 X_CREATION_DATE,
1198 X_CREATED_BY,
1199 X_LAST_UPDATE_DATE,
1200 X_LAST_UPDATED_BY,
1201 X_LAST_UPDATE_LOGIN,
1202 L_MAINTENANCE_MODE_SUPPORT,
1203 L_CONTEXT_DEPENDENCE,
1204 X_JRAD_REF_PATH
1205 );
1206
1207 -- Added for Function Security Cache Invalidation Project
1208 fnd_function_security_cache.insert_function(X_FUNCTION_ID);
1209
1210 if (columns_name is not null) then
1211 -- print out message about which column has been changed to
1212 -- meet the function type validation rule
1213 fnd_message.set_name('FND', 'FUNCTION_TYPE_COLUMNS_CHANGED');
1214 fnd_message.set_token('NAME', X_FUNCTION_NAME);
1215 fnd_message.set_token('COLUMNS', columns_name);
1216 end if;
1217
1221 CREATION_DATE,
1218 insert into FND_FORM_FUNCTIONS_TL (
1219 FUNCTION_ID,
1220 USER_FUNCTION_NAME,
1222 CREATED_BY,
1223 LAST_UPDATE_DATE,
1224 LAST_UPDATED_BY,
1225 LAST_UPDATE_LOGIN,
1226 DESCRIPTION,
1227 LANGUAGE,
1228 SOURCE_LANG
1229 ) select
1230 X_FUNCTION_ID,
1231 X_USER_FUNCTION_NAME,
1232 X_CREATION_DATE,
1233 X_CREATED_BY,
1234 X_LAST_UPDATE_DATE,
1235 X_LAST_UPDATED_BY,
1236 X_LAST_UPDATE_LOGIN,
1237 X_DESCRIPTION,
1238 L.LANGUAGE_CODE,
1239 userenv('LANG')
1240 from FND_LANGUAGES L
1241 where L.INSTALLED_FLAG in ('I', 'B')
1242 and not exists
1243 (select NULL
1244 from FND_FORM_FUNCTIONS_TL T
1245 where T.FUNCTION_ID = X_FUNCTION_ID
1246 and T.LANGUAGE = L.LANGUAGE_CODE);
1247
1248 open c;
1249 fetch c into X_ROWID;
1250 if (c%notfound) then
1251 close c;
1252 raise no_data_found;
1253 end if;
1254 close c;
1255
1256 end INSERT_ROW;
1257
1258 procedure LOCK_ROW (
1259 X_FUNCTION_ID in NUMBER,
1260 X_WEB_HOST_NAME in VARCHAR2,
1261 X_WEB_AGENT_NAME in VARCHAR2,
1262 X_WEB_HTML_CALL in VARCHAR2,
1263 X_WEB_ENCRYPT_PARAMETERS in VARCHAR2,
1264 X_WEB_SECURED in VARCHAR2,
1265 X_WEB_ICON in VARCHAR2,
1266 X_OBJECT_ID in NUMBER,
1267 X_REGION_APPLICATION_ID in NUMBER,
1268 X_REGION_CODE in VARCHAR2,
1269 X_FUNCTION_NAME in VARCHAR2,
1270 X_APPLICATION_ID in NUMBER,
1271 X_FORM_ID in NUMBER,
1272 X_PARAMETERS in VARCHAR2,
1273 X_TYPE in VARCHAR2,
1274 X_USER_FUNCTION_NAME in VARCHAR2,
1275 X_DESCRIPTION in VARCHAR2,
1276 X_MAINTENANCE_MODE_SUPPORT in VARCHAR2, /* NULL='NONE' */
1277 X_CONTEXT_DEPENDENCE in VARCHAR2, /* NULL='RESP' */
1278 X_JRAD_REF_PATH in VARCHAR2 default NULL
1279
1280 ) is
1281 cursor c is select
1282 WEB_ICON,
1283 WEB_HOST_NAME,
1284 WEB_AGENT_NAME,
1285 WEB_HTML_CALL,
1286 WEB_ENCRYPT_PARAMETERS,
1287 WEB_SECURED,
1288 OBJECT_ID,
1289 REGION_APPLICATION_ID,
1290 REGION_CODE,
1291 FUNCTION_NAME,
1292 APPLICATION_ID,
1293 FORM_ID,
1294 PARAMETERS,
1295 TYPE,
1296 MAINTENANCE_MODE_SUPPORT,
1297 CONTEXT_DEPENDENCE,
1298 JRAD_REF_PATH
1299 from FND_FORM_FUNCTIONS
1300 where FUNCTION_ID = X_FUNCTION_ID
1301 for update of FUNCTION_ID nowait;
1302 recinfo c%rowtype;
1303
1304 cursor c1 is select
1305 USER_FUNCTION_NAME,
1306 DESCRIPTION
1307 from FND_FORM_FUNCTIONS_TL
1308 where FUNCTION_ID = X_FUNCTION_ID
1309 and LANGUAGE = userenv('LANG')
1310 for update of FUNCTION_ID nowait;
1311 tlinfo c1%rowtype;
1312
1313 L_MAINTENANCE_MODE_SUPPORT VARCHAR2(8) := X_MAINTENANCE_MODE_SUPPORT;
1314 L_CONTEXT_DEPENDENCE VARCHAR2(8) := X_CONTEXT_DEPENDENCE;
1315 begin
1316 if (L_MAINTENANCE_MODE_SUPPORT is NULL) then
1317 L_MAINTENANCE_MODE_SUPPORT := 'NONE';
1318 end if;
1319
1320 if (L_CONTEXT_DEPENDENCE is NULL) then
1321 L_CONTEXT_DEPENDENCE := 'RESP';
1322 end if;
1323
1324 open c;
1325 fetch c into recinfo;
1326 if (c%notfound) then
1327 close c;
1328 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
1329 app_exception.raise_exception;
1330 end if;
1331 close c;
1332 if ( ((recinfo.WEB_ICON = X_WEB_ICON)
1333 OR ((recinfo.WEB_ICON is null) AND (X_WEB_ICON is null)))
1334 AND ((recinfo.WEB_HOST_NAME = X_WEB_HOST_NAME)
1335 OR ((recinfo.WEB_HOST_NAME is null) AND (X_WEB_HOST_NAME is null)))
1336 AND ((recinfo.WEB_AGENT_NAME = X_WEB_AGENT_NAME)
1337 OR ((recinfo.WEB_AGENT_NAME is null) AND (X_WEB_AGENT_NAME is null)))
1338 AND ((recinfo.WEB_HTML_CALL = X_WEB_HTML_CALL)
1339 OR ((recinfo.WEB_HTML_CALL is null) AND (X_WEB_HTML_CALL is null)))
1340 AND ((recinfo.WEB_ENCRYPT_PARAMETERS = X_WEB_ENCRYPT_PARAMETERS)
1341 OR ((recinfo.WEB_ENCRYPT_PARAMETERS is null) AND (X_WEB_ENCRYPT_PARAMETERS is null)))
1342 AND ((recinfo.WEB_SECURED = X_WEB_SECURED)
1343 OR ((recinfo.WEB_SECURED is null) AND (X_WEB_SECURED is null)))
1344 AND ((recinfo.OBJECT_ID = X_OBJECT_ID)
1345 OR ((recinfo.OBJECT_ID is null) AND (X_OBJECT_ID is null)))
1346 AND ((recinfo.REGION_APPLICATION_ID = X_REGION_APPLICATION_ID)
1347 OR ((recinfo.REGION_APPLICATION_ID is null) AND
1348 (X_REGION_APPLICATION_ID is null)))
1349 AND ((recinfo.REGION_CODE = X_REGION_CODE)
1350 OR ((recinfo.REGION_CODE is null) AND (X_REGION_CODE is null)))
1351 AND (recinfo.FUNCTION_NAME = X_FUNCTION_NAME)
1352 AND ((recinfo.APPLICATION_ID = X_APPLICATION_ID)
1353 OR ((recinfo.APPLICATION_ID is null) AND (X_APPLICATION_ID is null)))
1354 AND ((recinfo.FORM_ID = X_FORM_ID)
1355 OR ((recinfo.FORM_ID is null) AND (X_FORM_ID is null)))
1356 AND ((recinfo.PARAMETERS = X_PARAMETERS)
1357 OR ((recinfo.PARAMETERS is null) AND (X_PARAMETERS is null)))
1358 AND ((recinfo.TYPE = X_TYPE)
1359 OR ((recinfo.TYPE is null) AND (X_TYPE is null)))
1360 AND (recinfo.MAINTENANCE_MODE_SUPPORT = L_MAINTENANCE_MODE_SUPPORT)
1361 AND (recinfo.CONTEXT_DEPENDENCE = L_CONTEXT_DEPENDENCE)
1362 AND ((recinfo.JRAD_REF_PATH = X_JRAD_REF_PATH)
1363 OR ((recinfo.JRAD_REF_PATH is null) AND (X_JRAD_REF_PATH is null)))
1364 ) then
1365 null;
1366 else
1367 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
1368 app_exception.raise_exception;
1369 end if;
1370
1371 open c1;
1372 fetch c1 into tlinfo;
1373 if (c1%notfound) then
1377 close c1;
1374 close c1;
1375 return;
1376 end if;
1378
1379 if ( (tlinfo.USER_FUNCTION_NAME = X_USER_FUNCTION_NAME)
1380 AND ((tlinfo.DESCRIPTION = X_DESCRIPTION)
1381 OR ((tlinfo.DESCRIPTION is null) AND (X_DESCRIPTION is null)))
1382 ) then
1383 null;
1384 else
1385 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
1386 app_exception.raise_exception;
1387 end if;
1388 return;
1389 end LOCK_ROW;
1390
1391 procedure UPDATE_ROW (
1392 X_FUNCTION_ID in NUMBER,
1393 X_WEB_HOST_NAME in VARCHAR2,
1394 X_WEB_AGENT_NAME in VARCHAR2,
1395 X_WEB_HTML_CALL in VARCHAR2,
1396 X_WEB_ENCRYPT_PARAMETERS in VARCHAR2,
1397 X_WEB_SECURED in VARCHAR2,
1398 X_WEB_ICON in VARCHAR2,
1399 X_OBJECT_ID in NUMBER,
1400 X_REGION_APPLICATION_ID in NUMBER,
1401 X_REGION_CODE in VARCHAR2,
1402 X_FUNCTION_NAME in VARCHAR2,
1403 X_APPLICATION_ID in NUMBER,
1404 X_FORM_ID in NUMBER,
1405 X_PARAMETERS in VARCHAR2,
1406 X_TYPE in VARCHAR2,
1407 X_USER_FUNCTION_NAME in VARCHAR2,
1408 X_DESCRIPTION in VARCHAR2,
1409 X_LAST_UPDATE_DATE in DATE,
1410 X_LAST_UPDATED_BY in NUMBER,
1411 X_LAST_UPDATE_LOGIN in NUMBER,
1412 X_MAINTENANCE_MODE_SUPPORT in VARCHAR2, /* NULL='NONE' */
1413 X_CONTEXT_DEPENDENCE in VARCHAR2, /* NULL='RESP' */
1414 X_JRAD_REF_PATH in VARCHAR2 default NULL
1415 ) is
1416 L_MAINTENANCE_MODE_SUPPORT VARCHAR2(8) := X_MAINTENANCE_MODE_SUPPORT;
1417 L_CONTEXT_DEPENDENCE VARCHAR2(8) := X_CONTEXT_DEPENDENCE;
1418 -- Function type validation purpose arguments
1419 L_APPLICATION_ID NUMBER;
1420 L_FORM_ID NUMBER;
1421 L_TYPE VARCHAR2(30);
1422 L_PARAMETERS VARCHAR2(2000);
1423 L_WEB_HTML_CALL VARCHAR2(240);
1424 L_REGION_APPLICATION_ID NUMBER;
1425 L_REGION_CODE VARCHAR2(30);
1426 columns_name VARCHAR2(2000);
1427 begin
1428 if (L_MAINTENANCE_MODE_SUPPORT is NULL) then
1429 L_MAINTENANCE_MODE_SUPPORT := 'NONE';
1430 end if;
1431
1432 if (L_CONTEXT_DEPENDENCE is NULL) then
1433 L_CONTEXT_DEPENDENCE := 'RESP';
1434 end if;
1435
1436 -- Function type validation
1437 L_APPLICATION_ID := X_APPLICATION_ID;
1438 L_FORM_ID := X_FORM_ID;
1439 L_TYPE := X_TYPE;
1440 L_PARAMETERS := X_PARAMETERS;
1441 L_WEB_HTML_CALL := X_WEB_HTML_CALL;
1442 L_REGION_APPLICATION_ID := X_REGION_APPLICATION_ID;
1443 L_REGION_CODE := X_REGION_CODE;
1444
1445 columns_name := FUNCTION_VALIDATION(L_APPLICATION_ID,
1446 L_FORM_ID,
1447 L_TYPE,
1448 L_PARAMETERS,
1449 L_WEB_HTML_CALL,
1450 X_WEB_HOST_NAME,
1451 L_REGION_APPLICATION_ID,
1452 L_REGION_CODE,
1453 X_FUNCTION_NAME);
1454
1455 update FND_FORM_FUNCTIONS set
1456 WEB_ICON = X_WEB_ICON,
1457 WEB_HOST_NAME = X_WEB_HOST_NAME,
1458 WEB_AGENT_NAME = X_WEB_AGENT_NAME,
1459 WEB_HTML_CALL = L_WEB_HTML_CALL,
1460 WEB_ENCRYPT_PARAMETERS = X_WEB_ENCRYPT_PARAMETERS,
1461 WEB_SECURED = X_WEB_SECURED,
1462 OBJECT_ID = X_OBJECT_ID,
1463 REGION_APPLICATION_ID = L_REGION_APPLICATION_ID,
1464 REGION_CODE = L_REGION_CODE,
1465 APPLICATION_ID = L_APPLICATION_ID,
1466 FORM_ID = L_FORM_ID,
1467 PARAMETERS = L_PARAMETERS,
1468 TYPE = L_TYPE,
1469 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
1470 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
1471 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
1472 MAINTENANCE_MODE_SUPPORT = L_MAINTENANCE_MODE_SUPPORT,
1473 CONTEXT_DEPENDENCE = L_CONTEXT_DEPENDENCE,
1474 JRAD_REF_PATH = X_JRAD_REF_PATH
1475 where FUNCTION_ID = X_FUNCTION_ID;
1476
1477 if (columns_name is not null) then
1478 -- print out message about which column has been changed to
1479 -- meet the function type validation rule
1480 fnd_message.set_name('FND', 'FUNCTION_TYPE_COLUMNS_CHANGED');
1481 fnd_message.set_token('NAME', X_FUNCTION_NAME);
1482 fnd_message.set_token('COLUMNS', columns_name);
1483 end if;
1484
1485 if (sql%notfound) then
1486 raise no_data_found;
1487 else
1488 -- This means that a function was updated.
1489 -- Added for Function Security Cache Invalidation Project
1490 fnd_function_security_cache.update_function(X_FUNCTION_ID);
1491 end if;
1492
1493 update FND_FORM_FUNCTIONS_TL set
1494 USER_FUNCTION_NAME = X_USER_FUNCTION_NAME,
1495 DESCRIPTION = X_DESCRIPTION,
1496 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
1497 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
1498 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
1499 SOURCE_LANG = userenv('LANG')
1500 where FUNCTION_ID = X_FUNCTION_ID
1501 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
1502
1503 if (sql%notfound) then
1504 raise no_data_found;
1505 end if;
1506 end UPDATE_ROW;
1507
1508 procedure SET_FUNCTION_MODE (x_function_name in varchar2,
1509 x_function_mode in varchar2) is
1510 valid_mode number;
1511 begin
1512 if (x_function_name is null) then
1513 return;
1514 end if;
1515
1516 -- Validate the function maintenance mode
1517 begin
1518 select 1 into valid_mode
1519 from dual
1520 where x_function_mode in (
1521 select lv.lookup_code
1522 from fnd_lookup_values lv
1523 where lv.lookup_type = 'APPS_MAINTENANCE_MODE_SUPPORT'
1524 and lv.language = userenv('LANG'));
1525 exception
1526 when no_data_found then
1527 fnd_message.set_name('FND', 'FND_INVALID_MAINTENANCE_MODE');
1528 fnd_message.set_token('MODE', x_function_mode);
1529 app_exception.raise_exception;
1530 end;
1531
1535
1532 update fnd_form_functions
1533 set maintenance_mode_support = x_function_mode
1534 where function_name like x_function_name;
1536 -- Function name does not exist
1537 if (sql%notfound) then
1538 fnd_message.set_name('FND', 'FND_FUNCTION_NOT_FOUND');
1539 fnd_message.set_token('NAME', x_function_name);
1540 app_exception.raise_exception;
1541 end if;
1542
1543
1544 end SET_FUNCTION_MODE;
1545
1546
1547
1548 end FND_FORM_FUNCTIONS_PKG;
[Home] [Help]