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