[Home] [Help]
PACKAGE BODY: APPS.FND_OAM_DASHBOARD_UTIL
Source
1 PACKAGE BODY fnd_oam_dashboard_util AS
2 /* $Header: AFOAMDUB.pls 115.3 2004/04/16 10:49:53 swpattab noship $ */
3
4 -- Name
5 -- get_trans_name_values
6 --
7 -- Purpose
8 -- Gets the translated list of name values that will be used in the
9 -- system alert raised by the dashboard collection program.
10 --
11 -- Input Arguments
12 -- p_message_type - Value should be either 'MET' for message based on
13 -- threshold values
14 -- or 'STATUS' for metrics based on status values
15 -- p_name_val_codes:
16 -- If p_message_type is 'MET' then this should be a comma delimited
17 -- list of metric_short_names:threshold_operator:threshold_value
18 -- values. For example:
19 -- 'ACTIVE_USERS:G:300,DB_SESSIONS:G:300'
20 -- If p_message_type is 'STATUS' then this should be a comma delimited
21 -- list of application_id:concurrent_queue_id:status_code or
22 -- metric_short_name:statis_code values
23 --
24 -- For example:
25 -- '0:3042:2,0:10434:2,PHP_GEN:2'
26 --
27 -- Output Arguments
28 --
29 -- Returns
30 -- If p_message_type is 'MET' then returns the list of
31 -- translated metric display name, operator and threshold values
32 --
33 -- If p_message_type is 'STATUS' then returns the list of
34 -- translated service instance display name and status values.
35 --
36 -- For example:
37 -- MET: 'Active Users Greater than 300;Database Sessions Greater than 404;'
38 -- STATUS: 'Workflow Mailer: Down; Standard Manager: Down; PHP: Down'
39 -- Notes:
40 -- INTERNAL_USE_ONLY - This function is for use by the dashboard
41 -- collection program only.
42 --
43 FUNCTION get_trans_name_values (
44 p_message_type varchar2,
45 p_name_val_codes varchar2) RETURN varchar2
46 IS
47 v_remainder varchar2(4000) := p_name_val_codes;
48 v_entity varchar2(1024);
49 v_retu varchar2(2000) := null;
50 BEGIN
51 if (p_message_type is null or p_name_val_codes is null or
52 length(p_name_val_codes) <= 0) then
53 return NULL;
54 end if;
55 if (p_message_type <> 'MET' and p_message_type <> 'STATUS') then
56 return 'UNKNOWN_MSG_TYPE: ' || p_message_type;
57 end if;
58 --dbms_output.put_line('INITIAL v_remainder: ' || v_remainder);
59 --dbms_output.put_line('last comma: '|| instr(p_name_val_codes, ',', -1));
60 --dbms_output.put_line('length: '|| length(p_name_val_codes));
61
62 if (instr(p_name_val_codes, ',', -1) < length(p_name_val_codes)) then
63 --dbms_output.put_line('No comma at the end');
64 v_remainder := v_remainder || ',';
65 --dbms_output.put_line('Added comma at the end');
66 end if;
67
68 while (length(v_remainder) > 0) loop
69 --dbms_output.put_line('v_remainder: ' || v_remainder);
70 v_entity := substr(v_remainder,1,instr(v_remainder,',') -1);
71
72 --dbms_output.put_line('v_entity: ' || v_entity);
73
74 declare
75 v_first_delim number;
76 v_second_delim number;
77
78 v_first_portion varchar2(256);
79 v_second_portion varchar2(256);
80 v_third_portion varchar2(256);
81
82 v_name varchar2(256);
83 v_threshold_oper varchar2(256);
84 v_value varchar2(256);
85
86 v_name_value_pair varchar2(1024);
87 begin
88
89 v_first_delim := instr(v_entity,':',1,1);
90 v_second_delim := instr(v_entity,':',-1,1);
91
95 v_second_portion := substr(v_entity,v_first_delim +1);
92 v_first_portion := substr(v_entity,1,v_first_delim-1);
93 if (v_first_delim = v_second_delim) then
94 -- we only have two portions
96 v_third_portion := null;
97 else
98 -- we have three portions
99 v_second_portion := substr(v_entity,v_first_delim+1,v_second_delim-v_first_delim-1);
100 v_third_portion := substr(v_entity,v_second_delim+1);
101 end if;
102
103 --dbms_output.put_line('POR 1: ' || v_first_portion);
104 --dbms_output.put_line('POR 2: ' || v_second_portion);
105 --dbms_output.put_line('POR 3: ' || v_third_portion);
106 if (p_message_type = 'MET') then
107 select metric_display_name into v_name
108 from fnd_oam_metval_vl
109 where metric_short_name=v_first_portion;
110 select meaning into v_threshold_oper
111 from fnd_lookups
112 where lookup_type = 'OAM_DASHBOARD_THRESHOLD_OPER'
113 and lookup_code = v_second_portion;
114 v_value := v_third_portion;
115
116 -- we wont keep the value in the message since this may
117 -- raise a new alert every time value changes
118 v_name_value_pair := v_name||' '||v_threshold_oper||' '||v_value||';';
119 elsif (p_message_type = 'STATUS') then
120
121 if (v_third_portion is null) then
122 -- only two portions so this is dashboard status metric
123 select metric_display_name into v_name
124 from fnd_oam_metval_vl
125 where metric_short_name=v_first_portion;
126 select meaning into v_value
127 from fnd_lookups
128 where lookup_type = 'OAM_DASHBOARD_METRIC_STATUS'
129 and lookup_code = v_second_portion;
130 else
131 -- three portions so this is a service instance
132 select user_concurrent_queue_name into v_name
133 from fnd_concurrent_queues_vl
134 where to_char(application_id) = v_first_portion
135 and to_char(concurrent_queue_id) = v_second_portion;
136 select meaning into v_value
137 from fnd_lookups
138 where lookup_type = 'OAM_DASHBOARD_METRIC_STATUS'
139 and lookup_code = v_third_portion;
140 end if;
141
142 fnd_message.clear;
143 fnd_message.set_name('FND','OAM_DASHBOARD_NAME_VALUE');
144 fnd_message.set_token('NAME',v_name);
145 fnd_message.set_token('VALUE',v_value);
146 v_name_value_pair := fnd_message.get;
147 end if;
148
149
150
151 v_retu := substr(v_retu || ' ' || v_name_value_pair,1,2000);
152
153 end;
154 v_remainder := substr(v_remainder, instr(v_remainder,',') +1);
155 end loop;
156
157 --dbms_output.put_line('OUTPUT: ' || v_retu);
158 return v_retu;
159 EXCEPTION
160 when others then
161 v_retu := '*EXCEPTION* ' || SQLERRM;
162 return v_retu;
163 END get_trans_name_values;
164
165
166 -- Name
167 -- load_svci_info
168 --
169 -- Purpose
170 -- Loads services instances related alerting, collection information
171 -- into fnd_oam_svci_info. For the given service instance if a row
172 -- already exists it updates the row; otherwise it inserts a new row.
173 --
174 -- Input Arguments
175 --
176 -- Output Arguments
177 --
178 -- Returns
179 --
180 -- Notes:
181 --
182 PROCEDURE load_svci_info(
183 p_application_id number,
184 p_concurrent_queue_name varchar2,
185 p_alert_enabled_flag varchar2,
186 p_collection_enabled_flag varchar2,
187 p_threshold_value varchar2,
188 p_owner varchar2)
189 IS
190 v_x number;
191 v_userid number := 0;
192 BEGIN
193 begin
194 select user_id into v_userid
195 from fnd_user where upper(user_name) = upper(p_owner);
196 exception
197 when no_data_found then
198 v_userid := 0;
199 end;
200
201 select 1 into v_x
202 from fnd_oam_svci_info
203 where application_id = p_application_id
204 and concurrent_queue_name = p_concurrent_queue_name;
205
206 -- update
207 update fnd_oam_svci_info
208 set alert_enabled_flag = p_alert_enabled_flag,
209 collection_enabled_flag = p_collection_enabled_flag,
210 threshold_value = p_threshold_value,
211 last_updated_by = v_userid,
212 last_update_date = sysdate,
213 last_update_login = 0
214 where application_id = p_application_id
215 and concurrent_queue_name = p_concurrent_queue_name;
216 EXCEPTION
217 when no_data_found then
218 insert into fnd_oam_svci_info (
219 application_id,
220 concurrent_queue_name,
221 alert_enabled_flag,
222 collection_enabled_flag,
223 threshold_value,
224 created_by,
225 creation_date,
226 last_updated_by,
227 last_update_date,
228 last_update_login)
229 values (
230 p_application_id,
231 p_concurrent_queue_name,
232 p_alert_enabled_flag,
233 p_collection_enabled_flag,
234 p_threshold_value,
235 v_userid,
236 sysdate,
237 v_userid,
238 sysdate,
239 0);
240 when others then
241 raise;
242 END load_svci_info;
243
244 -- Name
245 -- save_web_ping_timeout
246 --
247 -- Purpose
248 -- Saves the value for the new web ping timeout by simply updating
249 -- the profile option "OAM_DASHBOARD_WEB_PING_TIMEOUT"
250 --
251 -- Input Arguments
252 --
253 -- Output Arguments
254 --
255 -- Returns
256 --
257 -- Notes:
258 --
259 FUNCTION save_web_ping_timeout(p_new_val VARCHAR2) return number
260 IS
261 v_ret boolean;
262 v_ret_val number;
263 BEGIN
264 v_ret := fnd_profile.save(
265 x_name => 'OAM_DASHBOARD_WEB_PING_TIMEOUT',
266 x_value => p_new_val,
267 x_level_name => 'SITE');
268
269 if (v_ret = true) then
270 v_ret_val := 0;
271 fnd_profile.put(
272 name => 'OAM_DASHBOARD_WEB_PING_TIMEOUT',
273 val => p_new_val);
274 else
275 v_ret_val := 1;
276 end if;
277 return v_ret_val;
278 EXCEPTION
279 when others then
280 raise;
281 END save_web_ping_timeout;
282
283 -- Name
284 -- format_time
285 --
286 -- Purpose
287 -- Formats the given number of seconds into 'HH:MM:SS' format.
288 -- e.g. 66 is converted to 00:01:06
289 --
290 -- Input Arguments
291 -- p_seconds - Time in seconds
292 --
293 -- Output Arguments
294 --
295 -- Returns
296 -- Formated String in 'HH:MM:SS' format
297 --
298 -- Notes:
299 --
300 FUNCTION format_time(p_seconds number) return varchar2
301 IS
302 v_retu varchar2(50) := '00:00:00';
303 v_hh number;
304 v_mm number;
305 v_ss number;
306
307 --v_hh_tmp number;
308 --v_mm_tmp number;
309 --v_ss_tmp number;
310
311 v_seconds number;
312 BEGIN
313 select round(nvl(p_seconds, 0)) into v_seconds from dual;
314
315 v_hh := floor(v_seconds/60/60);
316 v_mm := floor((v_seconds - (v_hh*3600))/60);
317 v_ss := floor((v_seconds - (v_hh*3600) - (v_mm*60)));
318 if (v_hh = 0 and v_mm = 0 and v_ss = 0) then
319 v_ss := 1;
320 end if;
321 v_retu := to_char(v_hh) || ':' ||
322 to_char(v_mm) || ':' ||
323 to_char(v_ss);
324 return v_retu;
325 EXCEPTION
326 when others then
327 v_retu := substr(SQLERRM,1,49);
328 return v_retu;
329 END format_time;
330
331 -- Name
332 -- get_meaning
333 --
334 -- Purpose
335 -- Gets the meaning for the given lookup_type and comma seperated
336 -- look_up codes from the fnd_lookups table
337 --
338 -- Input Arguments
339 -- p_lookup_type - Look up type (String)
340 -- p_lookup_codes - Comma separated lookup codes with no space in between
341 -- them. for eg: '2,1,3,4' etc.
342 --
343 -- Output Arguments
344 --
345 -- Returns
346 -- Comma separated meanings corresponding to each code.
347 --
348 -- Notes:
349 --
350 FUNCTION get_meaning (p_lookup_type varchar2,
351 p_lookup_codes varchar2) RETURN varchar2
352 IS
353 v_remainder varchar2(1000) := p_lookup_codes;
354 v_entity varchar2(1024);
355 v_retu varchar2(2000) := null;
356 v_status varchar2(100);
357 BEGIN
358 if (p_lookup_codes is null or
359 length(p_lookup_codes) <= 0) then
360 return NULL;
361 end if;
362
363 -- code for putting a comma at the end of the line
364 -- if not already present
365 if (instr(p_lookup_codes, ',', -1, 1) < length(p_lookup_codes)) then
366 v_remainder := v_remainder || ',';
367 end if;
368
369 while (length(v_remainder) > 0) loop
370 --dbms_output.put_line('v_remainder: ' || v_remainder);
371 v_entity := substr(v_remainder,1,instr(v_remainder,',',1,1) -1);
372
373 select meaning into v_status
374 from fnd_lookups
375 where lookup_type = p_lookup_type
376 and lookup_code = v_entity;
377
378
379
380 v_remainder := substr(v_remainder, instr(v_remainder,',') +1);
381
382 v_retu := v_retu || v_status;
383
384 if (length(v_remainder) > 0) then
385 v_retu := v_retu || ', ';
386 end if;
387
388 end loop;
389
390 --dbms_output.put_line('OUTPUT: ' || v_retu);
391 return v_retu;
392 EXCEPTION
393 when others then
394 v_retu := '*EXCEPTION* ' || SQLERRM;
395 return v_retu;
396 END get_meaning;
397
398 END fnd_oam_dashboard_util;