1 package body FND_WEB_CONFIG as
2 /* $Header: AFWBCFGB.pls 115.26 2003/04/29 01:24:00 sdstratt ship $ */
3
4
5 /* PLSQL_AGENT- get the name of the PLSQL web agent
6 **
7 ** Returns the value of the APPS_WEB_AGENT profile, with
8 ** a guaranteed trailing slash.
9 **
10 ** Note: if this routine fails, it will return NULL, and
11 ** there will be an error message on the message stack.
12 ** The caller is responsible for either displaying the message
13 ** or clearing the message stack upon failure.
14 **
15 ** IN:
16 ** help_mode - Look for HELP_WEB_AGENT profile over-ride
17 ** 'APPS' - Use APPS_WEB_AGENT
18 ** 'HELP' - Use HELP_WEB_AGENT
19 **
20 */
21
22 g_db_id varchar2(255) default null; -- 1585055: Global variable;
23
24 function PLSQL_AGENT (
25 help_mode in varchar2 default 'APPS')
26 return VARCHAR2
27 is
28 agent_url varchar2(2000) := NULL;
29 begin
30
31 if (upper(help_mode) = 'HELP') then
32 agent_url := fnd_profile.value('HELP_WEB_AGENT');
33 end if;
34 if (agent_url is null) then
35 agent_url := fnd_profile.value('APPS_WEB_AGENT');
36 end if;
37 if (agent_url is null) then
38 FND_MESSAGE.SET_NAME('FND', 'PROFILES-CANNOT READ');
39 FND_MESSAGE.SET_TOKEN('OPTION', help_mode||'_WEB_AGENT');
40 return NULL;
41 else
42 return FND_WEB_CONFIG.TRAIL_SLASH(agent_url);
43 end if;
44
45 end PLSQL_AGENT;
46
47
48 /* WEB_SERVER- get the URL of the web server machine
49 **
50 ** Returns the value of the web server from the APPS_WEB_AGENT with
51 ** a guaranteed trailing slash.
52 **
53 ** e.g. if APPS_WEB_AGENT = 'http://mysun.us.oracle.com:1234/dad1'
54 ** it returns 'http://mysun.us.oracle.com:1234/'
55 **
56 ** Note: if this routine fails, it will return NULL, and
57 ** there will be an error message on the message stack.
58 ** The caller is responsible for either displaying the message
59 ** or clearing the message stack upon failure.
60 **
61 ** IN:
62 ** help_mode - Look for HELP_WEB_AGENT profile over-ride
63 ** 'APPS' - Use APPS_WEB_AGENT
64 ** 'HELP' - Use HELP_WEB_AGENT
65 **
66 */
67 function WEB_SERVER (
68 help_mode in varchar2 default 'APPS')
69 return VARCHAR2 is
70 ws_url varchar2(2000);
71 index1 number;
72 index2 number;
73 begin
74
75 ws_url := FND_WEB_CONFIG.PLSQL_AGENT(help_mode);
76
77 if(ws_url is null) then
78 return NULL;
79 end if;
80
81
82 index1 := INSTRB(ws_url, '//', 1) + 2; /* skip 'http://' */
83
84 index2 := INSTRB(ws_url, '/', index1); /* get to 'http://serv:port/' */
85
86 if(index1 <> index2) AND (index1 <> 2) AND (index2 > 2)
87 AND (index1 is not NULL) AND (index2 is not NULL) then
88 return FND_WEB_CONFIG.TRAIL_SLASH(SUBSTRB(ws_url, 1, index2-1));
89 else
90 /* Incorrect format; give an error message */
91 FND_MESSAGE.SET_NAME('FND', 'AF_WCFG_BAD_AGENT_URL_FORMAT');
92 FND_MESSAGE.SET_TOKEN('URL', ws_url);
93 FND_MESSAGE.SET_TOKEN('PROFILE', help_mode||'_WEB_AGENT');
94 FND_MESSAGE.SET_TOKEN('FORMAT', 'http://server[:port]/DAD[/]');
95 return NULL;
96 end if;
97
98 end WEB_SERVER;
99
100
101
102 /* DAD- get the DAD component of the URL of the web server machine
103 **
104 ** Returns the value of the DAD (Database Access Descriptor) from the
105 ** APPS_WEB_AGENT with a guaranteed trailing slash.
106 **
107 ** e.g. if APPS_WEB_AGENT = 'http://mysun.us.oracle.com:1234/dad1'
108 ** it returns 'dad1/'
109 **
110 ** Note: if this routine fails, it will return NULL, and
111 ** there will be an error message on the message stack.
112 ** The caller is responsible for either displaying the message
113 ** or clearing the message stack upon failure.
114 **
115 ** IN:
116 ** help_mode - Look for HELP_WEB_AGENT profile over-ride
117 ** 'APPS' - Use APPS_WEB_AGENT
118 ** 'HELP' - Use HELP_WEB_AGENT
119 **
120 */
121 function DAD (
122 help_mode in varchar2 default 'APPS')
123 return VARCHAR2
124 is
125 dad_url varchar2(2000);
126 index1 number;
127 index2 number;
128 index3 number;
129 begin
130
131 dad_url := FND_WEB_CONFIG.PLSQL_AGENT(help_mode);
132
133 if(dad_url is null) then
134 return NULL;
135 end if;
136
137 dad_url := TRAIL_SLASH(dad_url);
138 index1 := INSTRB(dad_url, '//', 1) + 2; /* skip 'http://' */
139 index2 := INSTRB(dad_url, '/', index1)+1; /* get to 'http://serv:port/' */
140 index3 := INSTRB(dad_url, '/', index2);/* get to 'http://serv:port/dad/' */
141
142 if(index2 <> index3) AND (index1 > 2) AND (index2 > 4) AND (index3 > 5) then
143 return FND_WEB_CONFIG.TRAIL_SLASH(SUBSTRB(dad_url, index2,
144 index3-index2));
145 else
146 /* Incorrect format; give an error message */
147 FND_MESSAGE.SET_NAME('FND', 'AF_WCFG_BAD_AGENT_URL_FORMAT');
148 FND_MESSAGE.SET_TOKEN('URL', dad_url);
149 FND_MESSAGE.SET_TOKEN('PROFILE', help_mode||'_WEB_AGENT');
150 FND_MESSAGE.SET_TOKEN('FORMAT', 'http://server[:port]/DAD[/]');
151 return NULL;
152 end if;
153
154 end DAD;
155
156
157 /* GFM_AGENT- get the GFM agent of the web server machine
158 **
159 ** Returns the value of the Generic File Manager agent by parsing
160 ** the APPS_WEB_AGENT. Has a guaranteed trailing slash.
161 **
162 ** Note: Now that we are using webdb, calling this routine is equivalent
163 ** to calling fnd_web_config.plsql_agent
164 **
165 ** Note: if this routine fails, it will return NULL, and
166 ** there will be an error message on the message stack.
167 ** The caller is responsible for either displaying the message
168 ** or clearing the message stack upon failure.
169 **
170 ** IN:
171 ** help_mode - Look for HELP_WEB_AGENT profile over-ride
172 ** 'APPS' - Use APPS_WEB_AGENT
173 ** 'HELP' - Use HELP_WEB_AGENT
174 */
175 function GFM_AGENT (help_mode in varchar2 default 'APPS')
176 return VARCHAR2
177 is
178 begin
179 return fnd_web_config.plsql_agent(help_mode);
180 end GFM_AGENT;
181
182
183 /* PROTOCOL- get the protocol identifier
184 **
185 ** Returns the protocol of the APPS_WEB_AGENT profile.
186 **
187 ** e.g. if APPS_WEB_AGENT = 'http://mysun.us.oracle.com:1234/dad1/plsql'
188 ** it returns 'http:'
189 **
190 ** Note: if this routine fails, it will return NULL, and
191 ** there will be an error message on the message stack.
192 ** The caller is responsible for either displaying the message
193 ** or clearing the message stack upon failure.
194 **
195 ** Note: As an accomodation to the ICX team who will be using this routine
196 ** even when the profiles are not set, this routine will return
197 ** 'http:' if the profiles are not set, in order to allow their code
198 ** to work.
199 **
200 ** IN:
201 ** help_mode - Look for HELP_WEB_AGENT profile over-ride
202 ** 'APPS' - Use APPS_WEB_AGENT
203 ** 'HELP' - Use HELP_WEB_AGENT
204 **
205 */
206 function PROTOCOL (
207 help_mode in varchar2 default 'APPS')
208 return VARCHAR2
209 is
210 proto_url varchar2(2000) := NULL;
211 index1 number;
212 begin
213
214 proto_url := FND_WEB_CONFIG.PLSQL_AGENT(help_mode);
215
216 if(proto_url is null) then
217 FND_MESSAGE.CLEAR; /* Get rid of "Profile not found" error message */
218 return 'http:';
219 end if;
220
221 index1 := INSTRB(proto_url, '://', 1); /* Find end of 'http://' */
222
223 if(index1 > 0) then
224 return SUBSTRB(proto_url, 1, index1);
225 else
226 /* Incorrect format; give an error message */
227 FND_MESSAGE.SET_NAME('FND', 'AF_WCFG_BAD_AGENT_URL_FORMAT');
228 FND_MESSAGE.SET_TOKEN('URL', proto_url);
229 FND_MESSAGE.SET_TOKEN('PROFILE', help_mode||'_WEB_AGENT');
230 FND_MESSAGE.SET_TOKEN('FORMAT', 'http://server[:port]/DAD[/]');
231 return NULL;
232 end if;
233
234 end PROTOCOL;
235
236 /* TRAIL_SLASH- make sure there is a trailing slash on the URL passed in
237 **
238 ** If URL has a trailing slash, just returns URL
239 ** otherwise adds a trailing slash
240 **
241 */
242 function TRAIL_SLASH(INVAL in VARCHAR2) return VARCHAR2 is
243 copy_val varchar2(2000);
244 begin
245 copy_val := INVAL;
246 while (substr(copy_val, -1, 1) = '/') loop
247 copy_val := substr(copy_val, 1, length(copy_val)-1);
248 end loop;
249 return copy_val || '/';
250 end TRAIL_SLASH;
251
252
253 /* DATABASE_ID- get the database host id
254 **
255 ** Returns the database host id, lowercased.
256 **
257 ** The implementation will return an identifier which forms a unique
258 ** database identifier, suitable as a filename for the dbc file.
259 **
260 */
261 function DATABASE_ID return VARCHAR2 is
262 lhost varchar2(2000);
263 linstance varchar2(2000);
264 ldot pls_integer;
265 begin
266 -- Look to see if already cached
267 if (g_db_id is null) then
268 -- Check for profile over-ride
269 g_db_id := fnd_profile.value('APPS_DATABASE_ID');
270
271 if (g_db_id is null) then
272 -- Get default value of <host>_<sid>.
273 select lower(host_name), lower(instance_name)
274 into lhost, linstance
275 from v$instance;
276
277 -- If the host has a domain embedded in it - <host>.<domain>
278 -- then strip off the domain bit.
279 ldot := instr(lhost, '.');
280 if (ldot > 0) then
281 lhost := substr(lhost, 1, ldot-1);
282 end if;
283
284 g_db_id := lhost||'_'||linstance;
285 end if;
286 end if;
287
288 return g_db_id;
289 end DATABASE_ID;
290
291
292 /* JSP_AGENT- get the name of the apps JSP agent
293 **
294 ** Returns the value of the APPS_SERVLET_AGENT profile, with
295 ** a guaranteed trailing slash. [with servlet zone stuff removed]
296 **
297 ** Note: if this routine fails, it will return NULL, and
298 ** there will be an error message on the message stack.
299 ** The caller is responsible for either displaying the message
300 ** or clearing the message stack upon failure.
301 **
302 */
303 function JSP_AGENT return VARCHAR2 is
304 agent_url varchar2(2000) := NULL;
305 index1 number;
306 index2 number;
307 begin
308 agent_url := fnd_profile.value('APPS_SERVLET_AGENT');
309 if (agent_url is null) then
310 FND_MESSAGE.SET_NAME('FND', 'PROFILES-CANNOT READ');
311 FND_MESSAGE.SET_TOKEN('OPTION','APPS_SERVLET_AGENT');
312 return NULL;
313 end if;
314
315 agent_url := FND_WEB_CONFIG.TRAIL_SLASH(agent_url);
316
317 index1 := INSTRB(agent_url, '//', 1) + 2; /* skip 'http://' */
318
319 index2 := INSTRB(agent_url, '/', index1); /* get to 'http://serv:port/' */
320
321 if(index1 <> index2) AND (index1 <> 2) AND (index2 > 2)
322 AND (index1 is not NULL) AND (index2 is not NULL) then
323 return FND_WEB_CONFIG.TRAIL_SLASH(SUBSTRB(agent_url, 1, index2-1)) ||
324 'OA_HTML/';
325 else
326 /* Incorrect format; give an error message */
327 FND_MESSAGE.SET_NAME('FND', 'AF_WCFG_BAD_AGENT_URL_FORMAT');
328 FND_MESSAGE.SET_TOKEN('URL', agent_url);
329 FND_MESSAGE.SET_TOKEN('PROFILE', 'APPS_SERVLET_AGENT');
330 FND_MESSAGE.SET_TOKEN('FORMAT', 'http://server[:port]/');
331 return NULL;
332 end if;
333
334 end JSP_AGENT;
335
336 /*
337 ** server_name - Returns owa_util.get_cgi_env('SERVER_NAME').
338 */
339 function server_name return varchar2 is
340 name varchar2(255);
341 begin
342 name := owa_util.get_cgi_env('SERVER_NAME');
343 return name;
344 exception
345 when others then
346 return '';
347 end server_name;
348
349 /*
350 ** server_port - Returns owa_util.get_cgi_env('SERVER_PORT').
351 */
352 function server_port return varchar2 is
353 port varchar2(255);
354 begin
355 port := owa_util.get_cgi_env('SERVER_PORT');
356 return port;
357 exception
358 when others then
359 return '';
360 end server_port;
361
362 /*
363 ** check_enabled - Returns 'Y' if a PL/SQL procedure is enabled, 'N' otherwise.
364 **
365 ** The presence of a row in FND_ENABLED_PLQSL for a packaged procedure overrides
366 ** one for the procedure's package. For example if there are rows in
367 ** FND_ENABLED_PLSQL for PKG.PROC and PKG then this function returns the value
368 ** of the ENABLED column on the row for PKG.PROC
369 **
370 */
371 function check_enabled (proc in varchar2) return varchar2 is
372 curproc varchar2(100);
373 x_package varchar2(100);
374 dot_location number;
375 retval varchar2(1) := 'N';
376 found boolean := FALSE;
377 l_local_server varchar2(255);
378 l_external_server varchar2(255);
379 l_defined boolean;
380 is_external varchar2(1);
381
382 cursor proc_curs(v_procedure varchar2, v_external varchar2) is
383 SELECT ENABLED
384 FROM FND_ENABLED_PLSQL
385 WHERE PLSQL_TYPE = 'PROCEDURE'
386 AND PLSQL_NAME = v_procedure
387 AND ENABLED = 'Y'
388 AND decode(v_external, 'Y', EXTERNAL, '*') =
389 decode(v_external, 'Y', 'Y', '*');
390
391 cursor pack_curs(v_pack varchar2, v_external varchar2) is
392 SELECT ENABLED
393 FROM FND_ENABLED_PLSQL
394 WHERE PLSQL_TYPE = 'PACKAGE' AND PLSQL_NAME = v_pack
395 AND ENABLED = 'Y'
396 AND decode(v_external, 'Y', EXTERNAL, '*') =
397 decode(v_external, 'Y', 'Y', '*');
398
399 cursor packproc_curs(v_packproc varchar2, v_external varchar2) is
400 SELECT decode(v_external, 'Y',
401 decode(nvl(EXTERNAL, 'N'), 'N', 'N', enabled), enabled)
402 FROM FND_ENABLED_PLSQL
403 WHERE PLSQL_TYPE = 'PACKAGE.PROCEDURE' AND PLSQL_NAME = v_packproc;
404
405 begin
406 -- Get package being executed.
407 curproc := upper(proc);
408
409 dot_location := instr(curproc, '.');
410
411 l_local_server := fnd_web_config.protocol || '//' ||
412 fnd_web_config.server_name || ':' || fnd_web_config.server_port;
413
414 FND_PROFILE.GET_SPECIFIC (
415 NAME_Z => 'EXTERNAL_SERVERS' ,
416 USER_ID_Z => NULL ,
417 RESPONSIBILITY_ID_Z => NULL ,
418 APPLICATION_ID_Z => NULL ,
419 VAL_Z => l_external_server ,
420 DEFINED_Z => l_defined );
421
422 if ( instr(l_external_server,l_local_server) > 0 ) then
423 is_external := 'Y';
424 else
425 is_external := 'N';
426 end if;
427
428 if (dot_location = 0) then
429 -- This is a standalone procedure
430 -- Check for procedure match.
431 open proc_curs(curproc, is_external);
432 fetch proc_curs into retval;
433 if (proc_curs%notfound) then
434 retval := 'N';
435 end if;
436 close proc_curs;
437 else
438 -- This is a package.procedure
439 -- Check for package.procedure match.
440 open packproc_curs(curproc, is_external);
441 fetch packproc_curs into retval;
442 found := packproc_curs%found;
443 close packproc_curs;
444
445 if (not found) then
446 -- Check for package match.
447 x_package := substr(curproc, 0, dot_location-1);
448 open pack_curs(x_package, is_external);
449 fetch pack_curs into retval;
450 if (pack_curs%notfound) then
451 retval := 'N';
452 end if;
453 close pack_curs;
454
455 end if;
456
457 end if;
458
459 return (retval);
460
461 end check_enabled;
462
463 END FND_WEB_CONFIG;
464