DBA Data[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;