1 PACKAGE BODY fnd_oam_kbf AS
2 /* $Header: AFOAMBFB.pls 120.3 2005/11/16 21:46:28 ppradhan noship $ */
3
4 -------------------------------------------------------------------------------
5 -- Exceptions Related
6 -------------------------------------------------------------------------------
7 --
8 -- Name
9 -- get_sysalcnt
10 --
11 -- Purpose
12 -- Returns the count of System Alerts across various severities and
13 -- acknowledged states by querying the fnd_log_unique_exceptions table
14 --
15 -- Input Arguments
16 -- p_category - category for which to return the counts. Null by
17 -- by default.
18 -- Output Arguments
19 -- critical_n - count of new alerts which are of CRITICAL severity
20 -- error_n - count of new alerts which are of ERROR severity
21 -- warning_n - count of new alerts which are of WARNING severity
22 -- critical_o - count of open alerts which are of CRITICAL severity
23 -- error_o - count of open alerts which are of ERROR severity
24 -- warning_o - count of open alerts which are of WARNING severity
25 -- critical_c - count of closed alerts which are of CRITICAL severity
26 -- error_c - count of closed alerts which are of ERROR severity
27 -- warning_c - count of closed alerts which are of WARNING severity
28 --
29 -- Notes:
30 --
31 --
32 PROCEDURE get_sysal_cnt
33 (critical_n OUT NOCOPY number,
34 error_n OUT NOCOPY number,
35 warning_n OUT NOCOPY number,
36 critical_o OUT NOCOPY number,
37 error_o OUT NOCOPY number,
38 warning_o OUT NOCOPY number,
39 p_category in varchar2 default null)
40 IS
41
42 BEGIN
43 if p_category is null then
44 select count(*) into critical_n from fnd_log_unique_exceptions where
45 severity='CRITICAL' and status='N';
46 select count(*) into error_n from fnd_log_unique_exceptions where
47 severity='ERROR' and status='N';
48 select count(*) into warning_n from fnd_log_unique_exceptions where
49 severity='WARNING' and status='N';
50 select count(*) into critical_o from fnd_log_unique_exceptions where
51 severity='CRITICAL' and status='O';
52 select count(*) into error_o from fnd_log_unique_exceptions where
53 severity='ERROR' and status='O';
54 select count(*) into warning_o from fnd_log_unique_exceptions where
55 severity='WARNING' and status='O';
56 /*
57 select count(*) into critical_c from fnd_log_unique_exceptions where
58 severity='CRITICAL' and status='C';
59 select count(*) into error_c from fnd_log_unique_exceptions where
60 severity='ERROR' and status='C';
61 select count(*) into warning_c from fnd_log_unique_exceptions where
62 severity='WARNING' and status='C';
63 */
64 else
65 select count(*) into critical_n from fnd_log_unique_exceptions where
66 severity='CRITICAL' and status='N' and category=p_category;
67 select count(*) into error_n from fnd_log_unique_exceptions where
68 severity='ERROR' and status='N' and category=p_category;
69 select count(*) into warning_n from fnd_log_unique_exceptions where
70 severity='WARNING' and status='N' and category=p_category;
71 select count(*) into critical_o from fnd_log_unique_exceptions where
72 severity='CRITICAL' and status='O' and category=p_category;
73 select count(*) into error_o from fnd_log_unique_exceptions where
74 severity='ERROR' and status='O' and category=p_category;
75 select count(*) into warning_o from fnd_log_unique_exceptions where
76 severity='WARNING' and status='O' and category=p_category;
77 /*
78 select count(*) into critical_c from fnd_log_unique_exceptions where
79 severity='CRITICAL' and status='C' and category=p_category;
80 select count(*) into error_c from fnd_log_unique_exceptions where
81 severity='ERROR' and status='C' and category=p_category;
82 select count(*) into warning_c from fnd_log_unique_exceptions where
83 severity='WARNING' and status='C' and category=p_category;
84 */
85 end if;
86 END get_sysal_cnt;
87
88
89 --
90 -- Name
91 -- get_occ_cnt
92 --
93 -- Purpose
94 -- Returns the count of Occurrances of alerts across various severities
95 -- and acknowledged states by querying
96 -- the fnd_log_exceptions table.
97 --
98 -- Input Arguments
99 -- p_category - category for which to return the counts. Null by
100 -- by default.
101 -- Output Arguments
102 -- critical_n - count of new occurrances which are of CRITICAL severity
103 -- error_n - count of new occurrances which are of ERROR severity
104 -- warning_n - count of new occurrances which are of WARNING severity
105 -- critical_o - count of open occurrances which are of CRITICAL severity
106 -- error_o - count of open occurrances which are of ERROR severity
107 -- warning_o - count of open occurrances which are of WARNING severity
108 -- critical_c - count of closed occurrances which are of CRITICAL severity
109 -- error_c - count of closed occurrances which are of ERROR severity
110 -- warning_c - count of closed occrrances which are of WARNING severity
111 --
112 -- Notes:
113 --
114 --
115 PROCEDURE get_occ_cnt
116 (critical_n OUT NOCOPY number,
117 error_n OUT NOCOPY number,
118 warning_n OUT NOCOPY number,
119 critical_o OUT NOCOPY number,
120 error_o OUT NOCOPY number,
121 warning_o OUT NOCOPY number,
122 p_category in varchar2 default null)
123 IS
124
125 BEGIN
126 -- Added an optimization for getting occurence count using the sum of
127 -- count columns instead of joining with fnd_log_exceptions for
128 -- bug 4653173
129 if p_category is null then
130 select sum(count) into critical_n
131 from fnd_log_unique_exceptions flue
132 where flue.severity = 'CRITICAL' and flue.status='N';
133 select sum(count) into error_n
134 from fnd_log_unique_exceptions flue
135 where flue.severity = 'ERROR' and flue.status='N';
136 select sum(count) into warning_n
137 from fnd_log_unique_exceptions flue
138 where flue.severity = 'WARNING' and flue.status='N';
139 select sum(count) into critical_o
140 from fnd_log_unique_exceptions flue
141 where flue.severity = 'CRITICAL' and flue.status='O';
142 select sum(count) into error_o
143 from fnd_log_unique_exceptions flue
144 where flue.severity = 'ERROR' and flue.status='O';
145 select sum(count) into warning_o
146 from fnd_log_unique_exceptions flue
147 where flue.severity = 'WARNING' and flue.status='O';
148 /*
149 select sum(count) into critical_c
150 from fnd_log_unique_exceptions flue
151 where flue.severity = 'CRITICAL' and flue.status='C';
152 select sum(count) into error_c
153 from fnd_log_unique_exceptions flue
154 where flue.severity = 'ERROR' and flue.status='C';
155 select sum(count) into warning_c
156 from fnd_log_unique_exceptions flue
157 where flue.severity = 'WARNING' and flue.status='C';
158 */
159 else
160 select sum(count) into critical_n
161 from fnd_log_unique_exceptions flue
162 where flue.severity = 'CRITICAL' and flue.status='N'
163 and flue.category = p_category;
164 select sum(count) into error_n
165 from fnd_log_unique_exceptions flue
166 where flue.severity = 'ERROR' and flue.status='N'
167 and flue.category = p_category;
168 select sum(count) into warning_n
169 from fnd_log_unique_exceptions flue
170 where flue.severity = 'WARNING' and flue.status='N'
171 and flue.category = p_category;
172 select sum(count) into critical_o
173 from fnd_log_unique_exceptions flue
174 where flue.severity = 'CRITICAL' and flue.status='O'
175 and flue.category = p_category;
176 select sum(count) into error_o
177 from fnd_log_unique_exceptions flue
178 where flue.severity = 'ERROR' and flue.status='O'
179 and flue.category = p_category;
180 select sum(count) into warning_o
181 from fnd_log_unique_exceptions flue
182 where flue.severity = 'WARNING' and flue.status='O'
183 and flue.category = p_category;
184 /*
185 select sum(count) into critical_c
186 from fnd_log_unique_exceptions flue
187 where flue.severity = 'CRITICAL' and flue.status='C'
188 and flue.category = p_category;
189 select sum(count) into error_c
190 from fnd_log_unique_exceptions flue
191 where flue.severity = 'ERROR' and flue.status='C'
192 and flue.category = p_category;
193 select sum(count) into warning_c
194 from fnd_log_unique_exceptions flue
195 where flue.severity = 'WARNING' and flue.status='C'
196 and flue.category = p_category;
197 */
198 end if;
199 END get_occ_cnt;
200
201 --
202 -- Name
203 -- change_state
204 --
205 -- Purpose
206 -- To change the state of a set of system alerts to 'O' - Open or 'C'
207 -- to close the alert. Newly generated alerts have the state 'N'. This
208 -- procedure will also insert a row into fnd_exception_notes to indicate
209 -- that the alert's state has been changed by the given user.
210 --
211 -- Input Arguments
212 -- p_logidset - a single logid or a set of ',' delimited log ids. e.g.
213 -- '1234' or '1234,1235,1236'. The log IDs need to be the
214 -- unique_exception_id
215 -- p_newstate - 'O' for Open or 'C' for Closed.
216 -- p_username - user name of the apps user who is changing the state.
217 --
218 -- Notes:
219 -- The purpose for this procedure is so that users can move the state
220 -- of system alerts from the OAM UI.
221 --
222 PROCEDURE change_state
223 (p_logidset IN varchar2,
224 p_newstate IN varchar2,
225 p_username IN varchar2)
226 IS
227 e_invalid_state exception;
228 v_idset varchar2(3500) := p_logidset;
229 delim constant varchar2(1) := ',';
230 v_userid number;
231 nl varchar2(2) := '
232 ';
233 BEGIN
234 -- make sure new state is valid.
235 if p_newstate <> 'O' and p_newstate <> 'C' then
236 raise e_invalid_state;
237 end if;
238
239 -- first strip off any redundant delimitors at the beginning and end
240
241 if instr(v_idset, delim, 1) = 1 then
242 v_idset := substr(v_idset, 2);
243 end if;
244 if instr(v_idset, delim, length(v_idset)) > 0 then
245 v_idset := substr(v_idset, 1, length(v_idset) - 1);
246 end if;
247
248 -- now need to insert a line into fnd_exception_notes for each exceptions
249 -- so, retrieve user id
250
251 select user_id into v_userid
252 from fnd_user where upper(user_name) = upper(p_username);
253
254 declare
255 v_curr_index number := 1;
256 v_curr_id varchar2(100);
257 v_ack_phrase varchar2(100);
258 v_newstate_disp varchar2(50);
259 begin
260 -- This should probably eventually come from a lookup
261 if p_newstate = 'O' then
262 v_newstate_disp := 'Open';
263 elsif p_newstate = 'C' then
264 v_newstate_disp := 'Closed';
265 else
266 v_newstate_disp := p_newstate;
267 end if;
268
269 v_ack_phrase := '*** Changed state to ' || v_newstate_disp || ' by '
270 || upper(p_username) || ' at '
271 || to_char(sysdate, 'mm/dd/yy HH12:MI:SS AM') || ' ***' || nl;
272 v_idset := v_idset || delim; -- putting delim at end so all processing
273 -- happens within loop
274 while instr(v_idset, delim, v_curr_index) > 0 loop
275 v_curr_id := substr(v_idset, v_curr_index,
276 instr(v_idset, delim, v_curr_index) - v_curr_index);
277
278 -- Update the status
279 update fnd_log_unique_exceptions
280 set status = p_newstate,
281 last_updated_by = v_userid,
282 last_update_date = sysdate
283 where unique_exception_id = to_number(v_curr_id);
284
285 -- Now insert a note to keep track of the state change.
286 declare
287 v_notes CLOB;
288 begin
289 select notes into v_notes
290 from fnd_exception_notes
291 where unique_exception_id = to_number(v_curr_id)
292 for update;
293
294 dbms_lob.writeappend(v_notes, length(v_ack_phrase),
295 v_ack_phrase);
296 exception
297 when no_data_found then
298 insert into fnd_exception_notes (
299 notes, creation_date, created_by,
300 last_update_date, last_updated_by,
301 last_update_login,
302 log_sequence,
303 unique_exception_id) values
304 (v_ack_phrase, sysdate, v_userid, sysdate,
305 v_userid, 0, -1,
306 to_number(v_curr_id));
307 end;
308
309 v_curr_index := instr(v_idset, delim, v_curr_index) + 1;
310 end loop;
311 end;
312 commit;
313
314 EXCEPTION
315 when others then
316 rollback;
317 raise;
318 END change_state;
319
320 END fnd_oam_kbf;
321
322
[Home] [Help]