[Home] [Help]
PACKAGE BODY: APPS.XDP_ERRORS_PKG
Source
1 PACKAGE BODY XDP_ERRORS_PKG AS
2 /* $Header: XDPERRRB.pls 120.1 2005/06/15 22:57:59 appldev $ */
3
4 Procedure LogWFError(itemtype in varchar2, itemkey in varchar2, actid in number);
5
6 PROCEDURE Set_Message (
7 p_object_type IN VARCHAR2,
8 p_object_key IN VARCHAR2,
9 p_message_name IN VARCHAR2,
10 p_message_parameters IN VARCHAR2,
11 p_error_type IN VARCHAR2 DEFAULT pv_typeSystem)
12 IS
13 l_message_parameters xdp_error_log.message_parameters%TYPE;
14 MSG varchar2(2000);
15 begin
16
17 --
18 -- NOTE:
19 -- Any caller of this procedure should not use any message longer than 2000 characters.
20 -- This API can handle it, but when Get_Message is called, Fnd_Message.Get returns a maximum
21 -- of 2000 characters and breaks if the message was bigger than this
22 --
23
24 if ((p_object_type is NULL) OR (p_object_key is NULL) OR (p_message_name is NULL)) then
25 return;
26 end if;
27
28 MSG := FND_MESSAGE.GET_STRING ('XDP', p_message_name);
29
30 -- Fnd_Message.Get fails if chr(0) is within the text
31 l_message_parameters := REPLACE(p_message_parameters, chr(0));
32
33 if ((MSG is NOT NULL) AND (LENGTH(MSG) <> 0)) then
34
35 insert into XDP_ERROR_LOG (
36 ERROR_ID,
37 OBJECT_TYPE,
38 OBJECT_KEY,
39 ERROR_TIMESTAMP,
40 MESSAGE_NAME,
41 MESSAGE_PARAMETERS,
42 ERROR_TYPE,
43 CREATED_BY,
44 CREATION_DATE,
45 LAST_UPDATED_BY,
46 LAST_UPDATE_DATE,
47 LAST_UPDATE_LOGIN) values (
48 XDP_ERRORS_S.NEXTVAL,
49 p_object_type,
50 p_object_key,
51 SYSDATE,
52 p_message_name,
53 nvl(l_message_parameters, NULL),
54 p_error_type,
55 FND_GLOBAL.USER_ID,
56 SYSDATE,
57 FND_GLOBAL.USER_ID,
58 SYSDATE,
59 FND_GLOBAL.LOGIN_ID);
60 END IF;
61
62 EXCEPTION
63 WHEN OTHERS THEN
64 Rollback;
65 xdp_utilities.generic_error('XDP_ERRORS_PKG', 'Set_Message', sqlcode, sqlerrm);
66 END SET_MESSAGE;
67
68 PROCEDURE Set_Message_Auto (
69 p_object_type IN VARCHAR2,
70 p_object_key IN VARCHAR2,
71 p_message_name IN VARCHAR2,
72 p_message_parameters IN VARCHAR2,
73 p_error_type IN VARCHAR2 DEFAULT pv_typeSystem)
74 IS
75 PRAGMA AUTONOMOUS_TRANSACTION;
76
77 begin
78 Set_Message (
79 p_object_type => p_object_type,
80 p_object_key => p_object_key,
81 p_message_name => p_message_name,
82 p_message_parameters => p_message_parameters,
83 p_error_type => p_error_type);
84 commit;
85
86 END SET_MESSAGE_AUTO;
87
88
89 -- Wrapper to log workflow errors..
90 Procedure LOG_WF_ERROR (itemtype in varchar2,
91 itemkey in varchar2,
92 actid in number,
93 funcmode in varchar2,
94 resultout OUT NOCOPY varchar2) IS
95
96 l_result varchar2(10);
97 x_Progress VARCHAR2(2000);
98
99 BEGIN
100 -- RUN mode - normal process execution
101 --
102 IF (funcmode = 'RUN') THEN
103 LogWFError(itemtype, itemkey, actid);
104 resultout := 'COMPLETE';
105 return;
106 END IF;
107
108
109 EXCEPTION
110 WHEN OTHERS THEN
111 wf_core.context('XDP_ERRORS_PKG', 'LOG_WF_ERROR', itemtype, itemkey, to_char(actid), funcmode);
112 raise;
113
114 END LOG_WF_ERROR;
115
116
117 Procedure LogWFError(itemtype in varchar2,
118 itemkey in varchar2,
119 actid in NUMBER ) IS
120
121 l_ObjectType VARCHAR2(100);
122 l_ObjectKey_char VARCHAR2(100);
123 l_ObjectKey NUMBER;
124 l_order_count NUMBER;
125 l_line_count NUMBER;
126 l_pkg_count NUMBER;
127 l_workitem_count NUMBER;
128 l_fa_count NUMBER;
129 l_message_type VARCHAR2(100);
130 l_message_params xdp_error_log.message_parameters%TYPE;
131 l_message xdp_error_log.message_parameters%TYPE;
132 l_error_type VARCHAR2(100);
133
134 BEGIN
135
136 l_order_count := instr(itemkey,'MAIN') ;
137 l_line_count := instr(itemkey,'SVC') ;
138 l_pkg_count := instr(itemkey,'LINE');
139 l_workitem_count := instr(itemkey,'WI') ;
140 l_fa_count := instr(itemkey,'FA') ;
141
142 IF l_order_count > 0 THEN
143 l_ObjectType := 'ORDER';
144 l_ObjectKey := WF_ENGINE.GETITEMATTRNUMBER(itemtype => LogWFError.itemtype,
145 itemkey => LogWFError.itemkey,
146 aname => 'ORDER_ID');
147
148 ELSIF (l_line_count > 0 OR l_pkg_count > 0 ) THEN
149 l_ObjectType := 'LINE';
150 l_ObjectKey := WF_ENGINE.GETITEMATTRNUMBER(itemtype => LogWFError.itemtype,
151 itemkey => LogWFError.itemkey,
152 aname => 'LINE_ITEM_ID');
153
154 ELSIF (l_workitem_count > 0 AND l_fa_count = 0) THEN
155 l_ObjectType := 'WI';
156 l_ObjectKey := wf_engine.GetItemAttrNumber(itemtype => LogWFError.itemtype,
157 itemkey => LogWFError.itemkey,
158 aname => 'WORKITEM_INSTANCE_ID');
159
160 ELSIF l_fa_count > 0 THEN
161 l_ObjectType := 'FA';
162 l_ObjectKey := wf_engine.GetItemAttrNumber(itemtype => LogWFError.itemtype,
163 itemkey => LogWFError.itemkey,
164 aname => 'FA_INSTANCE_ID');
165 END IF;
166
167 --skilaru 01/27/02
168 --If we couldnt find any key then this node is being used some where..
169 --So lets log for what order ID this thing went wrong..
170 --ideally we should never get into this kind of a situation..
171 IF l_ObjectKey IS NULL THEN
172 l_ObjectType := 'ORDER';
173 l_ObjectKey := WF_ENGINE.GETITEMATTRNUMBER(itemtype => LogWFError.itemtype,
174 itemkey => LogWFError.itemkey,
175 aname => 'ORDER_ID');
176 END IF;
177
178 --SET_MESSAGE accepts object key as varchar..
179 l_ObjectKey_Char := TO_CHAR( l_ObjectKey );
180
181 --Who ever uses this activity in workflow have to pass what they want to log
182 --and whether its a BUSINESS or SYSTEM error.
183 l_message := WF_ENGINE.GetActivityattrtext(itemtype =>LogWFError.itemtype,
184 itemkey =>LogWFError.itemkey,
185 actid =>LogWFError.actid,
186 aname =>'MESSAGE');
187
188 l_error_type := WF_ENGINE.GetActivityattrtext(itemtype =>LogWFError.itemtype,
189 itemkey =>LogWFError.itemkey,
190 actid =>LogWFError.actid,
191 aname =>'ERROR_TYPE');
192
193 --build the message parameters fro setmessage signature..
194 l_message_params := 'MESSAGE='||l_message||'#XDP#';
195 -- skutil.sk_log( l_message_params );
196
197 SET_MESSAGE( l_ObjectType, l_ObjectKey, 'XDP_ERROR_MESSAGE', l_message_params, l_error_type );
198 END LogWFError;
199
200
201 FUNCTION GET_MESSAGE (
202 p_message_name IN VARCHAR2,
203 p_message_parameters IN VARCHAR2)
204 return VARCHAR2
205 is
206 l_temp_1 xdp_error_log.message_parameters%TYPE;
207 l_temp_2 xdp_error_log.message_parameters%TYPE;
208 l_name xdp_error_log.message_parameters%TYPE;
209 l_value xdp_error_log.message_parameters%TYPE;
210 l_offset NUMBER;
211 l_offset1 NUMBER;
212 l_last_offset NUMBER;
213 l_message_text xdp_error_log.message_parameters%TYPE;
214
215 BEGIN
216 l_message_text := '';
217
218 if p_message_name is not null then
219 FND_MESSAGE.SET_NAME ('XDP', p_message_name);
220 else
221 return l_message_text;
222 end if;
223
224 if p_message_parameters is not null then
225
226 -- p_message_parameters should be of format:
227 -- 'ADAPTER_NAME=SuperTel1#XDP#STATUS=SUSPENDED#XDP#'
228
229 l_last_offset := 1;
230 l_temp_1 := p_message_parameters;
231
232 for i in 1..100 loop
233 l_offset := INSTR (l_temp_1, pv_MsgParamSeparator, 1, i);
234 if (l_offset <> 0) then
235 -- Tag found
236 l_temp_2 := substr (l_temp_1, l_last_offset,
237 l_offset-l_last_offset);
238 -- DBMS_OUTPUT.PUT_LINE (l_temp_2);
239 l_offset1 := INSTR (l_temp_2, pv_NameValueSeparator);
240 if (l_offset1 <> 0) then
241 l_name := substr (l_temp_2, 1, l_offset1-1);
242 l_value := substr (l_temp_2, l_offset1+1);
243 -- XDP_UTILITIES.DISPLAY ('Name:'||l_name);
244 -- XDP_UTILITIES.DISPLAY ('Value:'||l_value);
245 FND_MESSAGE.SET_TOKEN (l_name, l_value);
246 else
247 -- error, = missing between name, value
248 exit;
249 end if;
250 l_last_offset := l_offset+pv_MsgParamSeparatorSize;
251 else
252 -- No more tags
253 exit;
254 end if;
255 END LOOP;
256 END IF;
257
258 l_message_text := FND_MESSAGE.GET();
259 return l_message_text;
260
261 EXCEPTION
262 WHEN OTHERS THEN
263 xdp_utilities.generic_error('XDP_ERRORS_PKG', 'Get_Message', sqlcode, sqlerrm);
264 END GET_MESSAGE;
265
266 PROCEDURE Get_Last_Message (
267 p_object_type IN VARCHAR2,
268 p_object_key IN VARCHAR2,
269 p_message OUT NOCOPY VARCHAR2,
270 p_error_type OUT NOCOPY VARCHAR2,
271 p_message_timestamp OUT NOCOPY DATE)
272 is
273 BEGIN
274 begin
275 select message, error_type, MAX(error_timestamp)
276 into p_message, p_error_type, p_message_timestamp
277 from XDP_ERROR_LOG_V
278 where object_type = p_object_type and object_key = p_object_key;
279 exception
280 WHEN NO_DATA_FOUND then
281 null;
282 END;
283
284 EXCEPTION
285 WHEN OTHERS THEN
286 xdp_utilities.generic_error('XDP_ERRORS_PKG', 'Get_Last_Message', sqlcode, sqlerrm);
287 END GET_LAST_MESSAGE;
288
289 PROCEDURE Update_Error_Count (
290 p_object_type IN VARCHAR2,
291 p_object_key IN VARCHAR2,
292 p_error_count IN NUMBER DEFAULT 0)
293 is
294 begin
295
296 UPDATE xdp_error_count SET
297 error_count = p_error_count,
298 last_updated_by = FND_GLOBAL.USER_ID,
299 last_update_date = sysdate,
300 last_update_login = FND_GLOBAL.USER_ID
301 WHERE
302 object_type = p_object_type and object_key = p_object_key;
303
304 if (sql%notfound) then
305
306 INSERT INTO XDP_ERROR_COUNT (
307 object_type,
308 object_key,
309 error_count,
310 created_by,
311 creation_date,
312 last_updated_by,
313 last_update_date,
314 last_update_login
315 ) VALUES (
316 p_object_type,
317 p_object_key,
318 p_error_count,
319 FND_GLOBAL.USER_ID,
320 sysdate,
321 FND_GLOBAL.USER_ID,
322 sysdate,
323 FND_GLOBAL.LOGIN_ID);
324 end if;
325
326 END UPDATE_ERROR_COUNT;
327
328 FUNCTION Get_Error_Count (
329 p_object_type IN VARCHAR2,
330 p_object_key IN VARCHAR2)
331 return NUMBER
332 is
333 l_errorCount NUMBER := 0;
334 begin
335
336 begin
337 SELECT error_count into l_errorCount
338 FROM xdp_error_count
339 WHERE object_type = p_object_type and object_key = p_object_key;
340
341 EXCEPTION
342 when no_data_found then
343 null;
344 END;
345
346 return l_errorCount;
347
348 END Get_Error_Count;
349
350 END XDP_ERRORS_PKG;