[Home] [Help]
PACKAGE BODY: APPS.WF_DIAGNOSTICS_APPS
Source
1 package body WF_DIAGNOSTICS_APPS as
2 /* $Header: WFDGAPPB.pls 120.6.12020000.7 2013/01/09 10:45:09 dhragarw ship $ */
3
4 -- Header
5 g_head varchar2(50) := '<html><body>';
6 g_end varchar2(50) := '</body></html>';
7
8 -- variable to store previous trace level as can be used procedure TRACE_UTIL
9 g_prev_trace_level number := -1;
10
11 -- Queue owner
12 g_qowner varchar2(30) := Wf_Core.Translate('WF_SCHEMA');
13
14 -- No WF Service instance Excpetion
15 No_Service_Instance EXCEPTION;
16 PRAGMA EXCEPTION_INIT(No_Service_Instance, -20100);
17
18
19 --
20 -- Get_GSM_Setup_Info - <Explained in WFDGAPPS.pls>
21 --
22 function Get_GSM_Setup_Info(p_value out nocopy clob)
23 return varchar2 is
24
25 l_service_instances fnd_concurrent.service_instance_tab_type;
26 l_gsm_enabled VARCHAR2(10);
27 l_service_enabed VARCHAR2(20);
28 l_user_concurrent_queue_name VARCHAR2(300);
29 l_temp_result varchar2(32000);
30 l_value clob;
31 l_app_id NUMBER;
32 l_managerid NUMBER;
33 l_activep NUMBER;
34 l_targetp NUMBER;
35 l_pmon_method VARCHAR2(80);
36 l_callstat NUMBER;
37 l_status VARCHAR2(1);
38 l_srv_instance varchar2(30);
39
40 l_temp_value varchar2(1024);
41
42 -- cusrsor to get env. variables
43 cursor cr_env (p_qname in varchar2, p_appId in NUMBER ) IS
44 select VARIABLE_NAME, VALUE
45 from FND_ENV_CONTEXT
46 where CONCURRENT_PROCESS_ID in
47 (select max(CONCURRENT_PROCESS_ID)
48 from FND_CONCURRENT_PROCESSES fcp , FND_CONCURRENT_QUEUES fcq
49 where fcp.CONCURRENT_QUEUE_ID = fcq.CONCURRENT_QUEUE_ID
50 and fcq.CONCURRENT_QUEUE_NAME= p_qname
51 and fcp.QUEUE_APPLICATION_ID = p_appId )
52 and VARIABLE_NAME in ('APPL_TOP', 'APPLCSF', 'APPLLOG', 'FND_TOP',
53 'AF_CLASSPATH', 'AFJVAPRG', 'AFJRETOP', 'CLASSPATH',
54 'PATH', 'LD_LIBRARY_PATH', 'ORACLE_HOME', 'NLS_LANG',
55 'AF_LD_LIBRARY_PATH')
56 order by VARIABLE_NAME;
57 begin
58
59 l_status := 'S';
60
61 dbms_lob.CreateTemporary(l_value, TRUE, dbms_lob.Session);
62 -- Set up Header
63 l_temp_result := g_head;
64 dbms_lob.WriteAppend(l_value, length(l_temp_result), l_temp_result);
65
66 -- If GSM is enabled in ICM
67 select decode(fnd_profile.value('CONC_GSM_ENABLED'), 'Y', 'ENABLED', 'NOT ENABLED')
68 into l_gsm_enabled from dual;
69
70 l_temp_result := '<br><table>' || '<tr><td class="OraHeaderSub" >GSM is ' || l_gsm_enabled ||
71 '</td></tr>' || '</table>';
72
73 dbms_lob.WriteAppend(l_value, length(l_temp_result), l_temp_result);
74
75 if (l_gsm_enabled = 'NOT ENABLED') then
76 l_status := 'E';
77 end if;
78
79 -- Check if ICM is running.
80 FND_CONCURRENT.GET_MANAGER_STATUS (
81 applid => 0,
82 managerid => 1,
83 targetp => l_targetp,
84 activep => l_activep,
85 pmon_method => l_pmon_method,
86 callstat => l_callstat);
87
88 l_temp_result := ' <br> <table>' ;
89
90 IF l_callstat <> 0 THEN
91 l_temp_result := l_temp_result || '<tr><td class="OraHeaderSub" >Could not verify if ICM is running => '
92 || l_callstat || ', Please check $AF_CLASSPATH, $ AF_LD_LIBRARY_PATH '
93 || ' and $PATH etc. on Concurrent Node </td></tr>' || '</table>';
94 l_status := 'E';
95 ELSE
96 IF l_activep > 0 THEN
97 l_temp_result := l_temp_result || '<tr><td class="OraHeaderSub" > ICM is running '
98 || '</td></tr>' || '</table>';
99 ELSE
100 l_temp_result := l_temp_result || '<tr><td class="OraHeaderSub" >ICM is down -> Actual: '
101 || l_activep || ', Target: ' || l_targetp
102 || l_callstat || '</td></tr>' || '</table>';
103 l_status := 'E';
104 END IF;
105 END IF;
106
107 dbms_lob.WriteAppend(l_value, length(l_temp_result), l_temp_result);
108
109 -- If the Service Instance are enabled
110 l_temp_result := ' <br> <table width="100%">';
111 l_temp_result := l_temp_result|| '<tr><td colspan="3" class="OraHeaderSub" >Workflow GSC Services status:</td></tr>';
112
113 l_temp_result := l_temp_result||'<tr><td width="50%" class="OraTableHeaderLink"> Service Type </td>';
114 l_temp_result := l_temp_result||'<td width="20%" class="OraTableHeaderLink"> Enable Status </td>';
115 l_temp_result := l_temp_result||'<td width="30%" class="OraTableHeaderLink"> Running Status </td> </tr>';
116
117 begin
118 -- FND_CP_SERVICES and FND_CONCURRENT_QUEUES tables
119 l_service_instances := fnd_concurrent.get_service_instances (svc_handle => 'FNDCPGSC');
120 exception
121 -- Handle -20100 error
122 When No_Service_Instance then
123 -- WHEN OTHERS THEN
124 l_temp_result := l_temp_result || ' <tr><td colspan="3" class="OraTableCellText" > ' ||
125 ' No Workflow Service Instances Found under '||
126 ' FNDCPGSC Service Handle </td></tr>';
127
128 end;
129
130 -- Check Each Service instance's data
131 IF (l_Service_Instances.COUNT > 0) THEN
132
133
134 FOR i IN 1..l_Service_Instances.COUNT LOOP
135
136 -- show env. vars for Mailer Service only,
137 if( l_service_instances(i).instance_name = 'WFMLRSVC' ) THEN
138 l_srv_instance := l_service_instances(i).instance_name;
139 END if;
140
141 -- If Mailer Service instance name did not find, take any of them.
142 if(l_srv_instance IS null) then
143 l_srv_instance := l_service_instances(i).instance_name;
144 END if;
145
146
147 IF (l_Service_Instances.EXISTS(i)) THEN
148
149 -- Get application_id
150 SELECT application_id INTO l_app_id
151 FROM fnd_application
152 WHERE application_short_name = l_service_instances(i).application;
153
154 -- If we are here it means that seed data is present in DB
155 begin
156
157 -- Get concurrent queue name
158 SELECT user_concurrent_queue_name,
159 concurrent_queue_id,
160 decode(enabled_flag, 'Y', 'ENABLED', 'NOT ENABLED')
161 INTO l_user_concurrent_queue_name, l_managerid, l_service_enabed
162 FROM fnd_concurrent_queues_vl
163 WHERE concurrent_queue_name = l_service_instances(i).instance_name;
164
165 -- Check if the service is enabled and running,
166 l_temp_result := l_temp_result || '<tr><td class="OraTableCellText"> ' ||
167 l_user_concurrent_queue_name || '</td>';
168 l_temp_result := l_temp_result || '<td class="OraTableCellText"> ' ||l_service_enabed || '</td>';
169
170 -- Get each Workflow Service Container status.
171 -- MANAGER_TYPE=1052 and CartType is 'AQCART'
172 -- Other errors are being handled within GET_MANAGER_STATUS API itself
173 FND_CONCURRENT.GET_MANAGER_STATUS (
174 applid => l_app_id,
175 managerid => l_managerid,
176 targetp => l_targetp,
177 activep => l_activep,
178 pmon_method => l_pmon_method,
179 callstat => l_callstat);
180
181 -- l_callstat <>0 mean there is no process exist in
182 -- fnd_concurrent_processes and gv$session
183 IF l_callstat <> 0 THEN
184 l_temp_result := l_temp_result || '<td class="OraTableCellText"> could not get ' ||
185 ' actual and target process count </td></tr>';
186
187 l_status := 'E';
188 ELSE
189 l_temp_result := l_temp_result || '<td class="OraTableCellText"> ' || 'Actual: ' ||
190 l_activep || ', Target: ' || l_targetp || '</td></tr>';
191
192 END IF;
193
194 EXCEPTION
195 -- Handle fetch on fnd_concurrent_queues_vl
196 when no_data_found then
197 l_temp_result := l_temp_result || '<tr><td colspan="3" class="OraTableCellText"> ' ||
198 'Could not find concurrent queue for '||
199 l_service_instances(i).instance_name ||
200 ' Service Instance. </td></tr>';
201 end;
202
203 end if; -- Service Instances EXIST
204
205 END LOOP; -- Loop for each service instance
206
207
208
209 else -- count LT 0
210 l_temp_result := l_temp_result || '<tr><td colspan="3" class="OraTableCellText" >No Service Found under '||
211 'FNDCPGSC Service Handle</td></tr>';
212 l_status := 'E';
213
214 END IF; -- count GT 0
215
216 l_temp_result := l_temp_result ||'</table><br>';
217 dbms_lob.WriteAppend(l_value, length(l_temp_result), l_temp_result);
218
219 -- Show the classpath etc. based on if it has ever started before
220 -- TODO : Whether these VARS should be displayd for each Service Instance or only
221 -- Once.
222 if(l_srv_instance IS not NULL) THEN
223
224 l_temp_result := '<br> <table width="100%"><tr> '||
225 ' <td width="10%" colspan=2 class="OraHeaderSub"> ' ||
226 ' Concurrent Tier Environment Variables being used ' ||
227 ' by Workflow Services </td></tr>';
228
229
230 for l_prec IN cr_env (l_srv_instance, l_app_id) LOOP
231
232 l_temp_result := l_temp_result || '<tr><td class="OraTableColumnHeader"> ' ||
233 l_prec.variable_name ||
234 ' </td> ';
235 l_temp_value := l_prec.value;
236
237 -- Non-Windows OS: Break path's value so that they will apear properly
238 if( instr(l_temp_value ,':') > 0 ) THEN
239 l_temp_value := replace(l_temp_value, ':', ': ');
240 else
241 l_temp_value := replace(l_temp_value, ';', '; ');
242 END if;
243
244 l_temp_result := l_temp_result || '<td class="OraTableCellText"> ' ||
245 l_temp_value ||
246 ' </td> </tr>';
247 end loop;
248
249 l_temp_result := l_temp_result ||'</table><br>';
250 dbms_lob.WriteAppend(l_value, length(l_temp_result), l_temp_result);
251
252 end if; -- Env .variables
253
254 -- Send the final HTML Output to the caller
255 dbms_lob.WriteAppend(l_value, length(g_end), g_end);
256 p_value := l_value;
257
258 return l_status;
259 end Get_GSM_Setup_Info;
260
261 --
262 -- EcxTest - <Explained in WFDGAPPS.pls>
263 --
264 procedure EcxTest(
265 outbound_ret_code out nocopy varchar2,
266 outbound_errbuf out nocopy varchar2,
267 outbound_xmlfile out nocopy varchar2,
268 outbound_logfile out nocopy varchar2,
269 inbound_ret_code out nocopy varchar2,
270 inbound_errbuf out nocopy varchar2,
271 inbound_logfile out nocopy varchar2)
272 is
273 p_xmldoc CLOB;
274 p_ret_code pls_integer;
275 p_errbuf varchar2(2000);
276 p_logfile varchar2(2000);
277 i_tmp clob;
278 i_temp clob;
279 i_buffer varchar2(32767);
280 i_ret_code pls_integer;
281 i_errbuf varchar2(2000);
282 i_logfile varchar2(2000);
283 sid number;
284 file utl_file.file_type;
285 ecx_logging_enabled boolean := false;
286 logging_enabled varchar2(20);
287 module varchar2(2000);
288
289 --i_file_name varchar2(2000);
290 begin
291 fnd_profile.get('AFLOG_ENABLED',logging_enabled);
292 fnd_profile.get('AFLOG_MODULE',module);
293 if(logging_enabled = 'Y'
294 AND instrb(module,'ecx') > 0
295 AND FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) then
296 ecx_logging_enabled := true;
297 end if;
298
299 dbms_lob.createtemporary(p_xmldoc,true,dbms_lob.session);
300 ecx_outbound.GETXML(
301 i_map_code => 'TestingDirectOut',
302 i_debug_level => 3,
303 i_xmldoc => p_xmldoc,
304 i_ret_code => p_ret_code,
305 i_errbuf => p_errbuf,
306 i_log_file => p_logfile
307 );
308
309 -- i_file_name := 'OUT.'||ecx_utils.g_run_id||'.xml';
310 outbound_ret_code := p_ret_code;
311 outbound_errbuf := p_errbuf;
312 -- outbound_logfile := p_logfile;
313 IF (ecx_logging_enabled) THEN
314 outbound_xmlfile := 'FND-Logging AFLOG MODULE Name for XML File :'
315 ||ecx_debug.g_sqlprefix||'out.'||ecx_utils.g_run_id||'.xml';
316 outbound_logfile := 'FND-Logging AFLOG MODULE Name for Log File :'
317 ||ecx_debug.g_sqlprefix||'out.'||ecx_utils.g_run_id||'.log';
318 ElSE
319 outbound_xmlfile := 'No xml file name as logging was not enabled for module: '
320 ||ecx_debug.g_sqlprefix||'%';
321 outbound_logfile := 'No log file name as logging was not enabled for module: '
322 ||ecx_debug.g_sqlprefix||'%';
323 END IF;
324
325 -- dbms_lob.freetemporary(p_xmldoc);
326
327 dbms_lob.createtemporary(i_tmp,true,dbms_lob.session);
328 /* file := utl_file.fopen(ecx_utils.g_logdir,i_file_name, 'r');
329 loop
330 begin
331 utl_file.get_line(file, i_buffer);
332 dbms_lob.writeappend(i_tmp,lengthb(i_buffer),i_buffer);
333 exception
334 when no_data_found then
335 exit;
336 when others then
337 -- dbms_output.put_line(i_buffer);
338 exit;
339 end;
340 end loop;
341 */
342 ecx_inbound_trig.processXML
343 (
344 'TestingDirectIn',
345 p_xmldoc,
346 3,
347 i_ret_code,
348 i_errbuf,
349 i_logfile,
350 i_temp
351 );
352
353 dbms_lob.freetemporary(i_tmp);
354
355 inbound_ret_code := i_ret_code;
356 inbound_errbuf := i_errbuf;
357 -- inbound_logfile := i_logfile;
358 IF (ecx_logging_enabled) THEN
359 inbound_logfile := 'FND-Logging AFLOG MODULE Name for Log File :'
360 ||ecx_debug.g_sqlprefix||'in.'||ecx_utils.g_run_id||'.log';
361 ElSE
362 inbound_logfile := 'No log file name as logging was not enabled for module: '
363 ||ecx_debug.g_sqlprefix||'%';
364 END IF;
365 exception
366 when others then
367 dbms_lob.freetemporary(p_xmldoc);
368 dbms_lob.freetemporary(i_tmp);
369
370 outbound_ret_code := p_ret_code;
371 outbound_errbuf := p_errbuf;
372 outbound_logfile := p_logfile;
373
374 inbound_ret_code := i_ret_code;
375 inbound_errbuf := i_errbuf;
376 inbound_logfile := i_logfile;
377 end ECXTEST;
378
379
380 --
381 -- TRACE_UTIL
382 -- Bug: 6964389
383 -- Enables/disables the SQL Trace at the specified level based
384 -- on the value of current Trace level of a component.
385 -- Constructs the TRACE FILE IDENTIFIER value as combination of
386 -- component id and time stamp.
387 -- Returns Trace file name, audsid and timestamp values
388 -- IN:
389 -- p_current_TraceLevel - Current Trace level value
390 -- p_comp_id - Component id
391 --
392 -- OUT:
393 -- p_trace_filename - The Trace file name
394 -- p_audsid - The audsid value
395 -- p_timestamp - The current timestamp
396 --
397 procedure TRACE_UTIL
398 (p_current_TraceLevel in number,
399 p_comp_id in number,
400 p_trace_filename out nocopy varchar2,
401 p_audsid out nocopy integer,
402 p_timestamp out nocopy varchar2
403 )
404 is
405
406 l_trace_id varchar2(100);
407 l_date date;
408 l_date_str varchar2(20);
409 l_trace_enabled boolean;
410
411
412 begin
413 select sysdate into l_date from dual;
414
415 if (p_current_TraceLevel = -1)then
416 FND_TRACE.STOP_TRACE(g_prev_trace_level);
417 else
418 l_trace_id := 'WFAL_'||p_comp_id||'_'||to_char(l_date,'YYYYMMDDHH24MISS');
419 FND_TRACE.SET_TRACE_IDENTIFIER(l_trace_id);
420 FND_TRACE.START_TRACE(p_current_TraceLevel);
421 end if;
422
423 -- Store the current trace level in variable 'g_prev_trace_level',
424 -- so that it can be used as the previous level in the next call
425 g_prev_trace_level := p_current_TraceLevel;
426
427 p_trace_filename := FND_TRACE.GET_TRACE_FILENAME();
428
429 p_timestamp := to_char(l_date,'YYYYMMDDHH24MISS');
430 p_audsid := sys_context('userenv','sessionid');
431 end TRACE_UTIL;
432
433 end WF_DIAGNOSTICS_APPS;