[Home] [Help]
PACKAGE BODY: APPS.OKC_AQ_WRITE_ERROR_PVT
Source
1 PACKAGE BODY OKC_AQ_WRITE_ERROR_PVT AS
2 /* $Header: OKCRAQWB.pls 120.1 2006/03/31 17:32:09 vjramali noship $ */
3
4 l_debug VARCHAR2(1) := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'),'N');
5
6 -- Start of comments
7 -- Procedure Name : write_msgdata
8 -- Description : Inserts records into okc_aqerrors and okc_aqmsgstacks
9 -- Version : 1.0
10 -- End of comments
11 PROCEDURE WRITE_MSGDATA(p_api_version IN NUMBER,
12 p_init_msg_list IN VARCHAR2 ,
13 p_source_name IN VARCHAR2,
14 p_datetime IN DATE,
15 p_msg_tab IN OKC_AQ_PVT.msg_tab_typ,
16 p_q_name IN VARCHAR2 ,
17 p_corrid IN VARCHAR2,
18 p_msgid IN RAW ,
19 p_message_name IN VARCHAR2 ,
20 p_msg_count IN NUMBER,
21 p_msg_data IN VARCHAR2,
22 p_commit IN VARCHAR2 ) IS
23 PRAGMA AUTONOMOUS_TRANSACTION;
24 ctr NUMBER := 0;
25 l_msg_text VARCHAR2(4000);
26 l_return_status VARCHAR2(1);
27 l_msg_count NUMBER;
28 l_msg_data VARCHAR2(240);
29 G_FIRST CONSTANT NUMBER := -1;
30 l_msg_clob CLOB := EMPTY_CLOB();
31 l_retry_count NUMBER := 0;
32 l_api_name CONSTANT VARCHAR2(30) := 'WRITE_MSGDATA';
33 l_init_msg_list VARCHAR2(3) ;
34 proc_notfound NUMBER := 0;
35 l_commit VARCHAR2(3) := 'T';
36
37 --Select the retry count from events table
38 Cursor retry_cur(p_msgid IN RAW) is
39 select /*+ INDEX (OKC_AQ_EV_TAB) */ retry_count
40 from OKC_AQ_EV_TAB
41 where msgid = p_msgid;
42
43 BEGIN
44 --Initialize return status
45 l_return_status := OKC_API.G_RET_STS_SUCCESS;
46
47 -- Standard START OF API SAVEPOINT
48 DBMS_TRANSACTION.SAVEPOINT(l_api_name || '_PVT');
49
50 --Get the retry count from events table
51 IF p_msgid IS NOT NULL THEN
52 OPEN retry_cur(p_msgid);
53 FETCH retry_cur into l_retry_count;
54 CLOSE retry_cur;
55 END IF;
56
57 --Get the Queue contents
58 OKC_AQ_WRITE_ERROR_PVT.get_clob_msg(p_msg_tab => p_msg_tab,
59 p_q_name => p_q_name,
60 p_corrid => p_corrid,
61 p_msg_clob => l_msg_clob,
62 x_return_status => l_return_status,
63 x_msg_count => l_msg_count,
64 x_msg_data => l_msg_data);
65 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
66 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
67 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
68 RAISE OKC_API.G_EXCEPTION_ERROR;
69 END IF;
70
71 --Retrieve the first message from the stack
72 IF p_msg_count = 1 or p_msg_count IS NULL THEN
73 --Populate record for errors
74 l_aqev_rec.source_name := p_source_name;
75 l_aqev_rec.datetime := p_datetime;
76 l_aqev_rec.q_name := p_q_name;
77 l_aqev_rec.msgid := p_msgid;
78 l_aqev_rec.queue_contents := l_msg_clob;
79 l_aqev_rec.retry_count := l_retry_count;
80
81 --Populate the error messages table
82 l_msg_text := FND_MSG_PUB.Get(p_msg_index => G_FIRST,
83 p_encoded => FND_API.G_FALSE);
84 proc_notfound := instr(l_msg_text,'ORA-06508',1,1);
85 l_aqmv_tbl(1).message_text := SUBSTR(l_msg_text, 1, 1995);
86 l_aqmv_tbl(1).msg_seq_no := 1;
87
88 --Call the public api to insert records into error and message stack tables
89 OKC_AQERRMSG_PUB.create_err_msg(
90 p_api_version => p_api_version,
91 p_init_msg_list => p_init_msg_list,
92 x_return_status => l_return_status,
93 x_msg_count => l_msg_count,
94 x_msg_data => l_msg_data,
95 p_aqev_rec => l_aqev_rec,
96 p_aqmv_tbl => l_aqmv_tbl,
97 x_aqev_rec => x_aqev_rec,
98 x_aqmv_tbl => x_aqmv_tbl);
99 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
100 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
101 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
102 RAISE OKC_API.G_EXCEPTION_ERROR;
103 ELSIF l_return_status = OKC_API.G_RET_STS_SUCCESS THEN
104 l_commit := 'T';
105 END IF;
106
107 --Retrieves more than a single message
108 ELSIF p_msg_count > 1 THEN
109 l_aqev_rec.source_name := p_source_name;
110 l_aqev_rec.datetime := p_datetime;
111 l_aqev_rec.q_name := p_q_name;
112 l_aqev_rec.msgid := p_msgid;
113 l_aqev_rec.queue_contents := l_msg_clob;
114 l_aqev_rec.retry_count := l_retry_count;
115
116 FOR i IN 1..p_msg_count LOOP
117 ctr := ctr + 1;
118 l_msg_text := FND_MSG_PUB.Get(p_msg_index => ctr,
119 p_encoded => FND_API.G_FALSE);
120 proc_notfound := instr(l_msg_text,'ORA-06508',1,1);
121 l_aqmv_tbl(ctr).message_text := SUBSTR(l_msg_text, 1, 1995);
122 l_aqmv_tbl(ctr).msg_seq_no := ctr;
123 IF proc_notfound <> 0 THEN
124 EXIT;
125 END IF;
126 END LOOP;
127 --Call the Public API to insert error records into the okc_aqerrors
128 --and okc_aqmsgstacks
129 OKC_AQERRMSG_PUB.create_err_msg(
130 p_api_version => p_api_version,
131 p_init_msg_list => p_init_msg_list,
132 x_return_status => l_return_status,
133 x_msg_count => l_msg_count,
134 x_msg_data => l_msg_data,
135 p_aqev_rec => l_aqev_rec,
136 p_aqmv_tbl => l_aqmv_tbl,
137 x_aqev_rec => x_aqev_rec,
138 x_aqmv_tbl => x_aqmv_tbl);
139 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
140 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
141 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
142 RAISE OKC_API.G_EXCEPTION_ERROR;
143 ELSIF l_return_status = OKC_API.G_RET_STS_SUCCESS THEN
144 l_commit := 'T';
145 END IF;
146 END IF;
147 IF l_commit = 'T' THEN
148 IF proc_notfound <> 0 THEN
149 -- if ORA-06508 is found in the msg stack stop the listeners
150 OKC_AQ_PVT.stop_listener;
151 END IF;
152 commit;
153 END IF;
154 OKC_API.END_ACTIVITY(l_msg_count, l_msg_data);
155 EXCEPTION
156 WHEN OKC_API.G_EXCEPTION_ERROR THEN
157 l_return_status := OKC_API.HANDLE_EXCEPTIONS
158 (
159 l_api_name,
160 G_PKG_NAME,
161 'OKC_API.G_RET_STS_ERROR',
162 l_msg_count,
163 l_msg_data,
164 '_PVT'
165 );
166
167 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
168 l_return_status := OKC_API.HANDLE_EXCEPTIONS
169 (
170 l_api_name,
171 G_PKG_NAME,
172 'OKC_API.G_RET_STS_UNEXP_ERROR',
173 l_msg_count,
174 l_msg_data,
175 '_PVT'
176 );
177
178 WHEN OTHERS THEN
179 l_return_status := OKC_API.HANDLE_EXCEPTIONS
180 (
181 l_api_name,
182 G_PKG_NAME,
183 'OTHERS',
184 l_msg_count,
185 l_msg_data,
186 '_PVT'
187 );
188 END WRITE_MSGDATA;
189
190 -- Start of comments
191 -- Procedure Name : update_error
192 -- Description : updates records in tables okc_aqerrors and okc_aqmsgstacks
193 -- Version : 1.0
194 -- End of comments
195 PROCEDURE UPDATE_ERROR(p_api_version IN NUMBER,
196 p_init_msg_list IN VARCHAR2 ,
197 p_id IN NUMBER,
198 p_aqe_id IN NUMBER,
199 p_msg_seq_no IN NUMBER,
200 p_source_name IN VARCHAR2,
201 p_datetime IN DATE,
202 p_q_name IN VARCHAR2 ,
203 p_msgid IN RAW ,
204 p_message_no IN NUMBER,
205 p_message_name IN VARCHAR2,
206 p_message_text IN VARCHAR2,
207 x_msg_count OUT NOCOPY NUMBER,
208 x_msg_data OUT NOCOPY VARCHAR2,
209 x_return_status OUT NOCOPY VARCHAR2) IS
210 BEGIN
211 NULL;
212 END UPDATE_ERROR;
213
214 -- Start of comments
215 -- Procedure Name : write_msgdata
216 -- Description : Deletes records from tables okc_aqerrors and okc_aqmsgstacks
217 -- Version : 1.0
218 -- End of comments
219 PROCEDURE DELETE_ERROR(p_api_version IN NUMBER,
220 p_init_msg_list IN VARCHAR2 ,
221 p_id IN NUMBER,
222 x_msg_count OUT NOCOPY NUMBER,
223 x_msg_data OUT NOCOPY VARCHAR2,
224 x_return_status OUT NOCOPY VARCHAR2) IS
225 BEGIN
226 NULL;
227 END DELETE_ERROR;
228
229 PROCEDURE get_clob_msg(p_msg_tab IN OKC_AQ_PVT.msg_tab_typ,
230 p_q_name IN VARCHAR2,
231 p_corrid IN VARCHAR2,
232 p_msg_clob OUT NOCOPY CLOB,
233 x_return_status OUT NOCOPY VARCHAR2,
234 x_msg_count OUT NOCOPY VARCHAR2,
235 x_msg_data OUT NOCOPY VARCHAR2) IS
236
237 l_msg_clob CLOB:=EMPTY_CLOB();
238 l_msg_string VARCHAR2(32767);
239 i NUMBER := 0;
240 ctr NUMBER := 0;
241 l_action_name okc_actions_tl.name%TYPE;
242 l_api_name CONSTANT VARCHAR2(30) := 'get_clob_msg';
243 l_return_status VARCHAR2(1);
244
245 Cursor action_csr(p_corrid IN VARCHAR2) is
246 select name
247 from okc_actions_v
248 where correlation = p_corrid;
249 BEGIN
250 -- Standard START OF API SAVEPOINT
251 DBMS_TRANSACTION.SAVEPOINT(l_api_name || '_PVT');
252
253 --Initialize return status
254 l_return_status := OKC_API.G_RET_STS_SUCCESS;
255
256 IF p_q_name = 'Events Queue' THEN
257 IF p_msg_tab.COUNT > 0 THEN
258 i := p_msg_tab.FIRST;
259 l_msg_string := p_msg_tab(i).element_name||'='||p_msg_tab(i).element_value;
260 i := p_msg_tab.FIRST + 1;
261 LOOP
262 l_msg_string := l_msg_string||','||p_msg_tab(i).element_name||'='||p_msg_tab(i).element_value;
263 EXIT WHEN (i = p_msg_tab.LAST);
264 i := p_msg_tab.NEXT(i);
265 END LOOP;
266 END IF;
267
268 --Fetch the action name for a specific correlation
269 OPEN action_csr(p_corrid);
270 FETCH action_csr INTO l_action_name;
271 CLOSE action_csr;
272
273 --Append the action name to the string
274 l_msg_string := l_action_name||','||l_msg_string;
275 ELSIF p_q_name = 'Outcome Queue' THEN
276 IF p_msg_tab.COUNT > 0 THEN
277 i := p_msg_tab.FIRST;
278 l_msg_string := p_msg_tab(i).element_value;
279 i := p_msg_tab.NEXT(i);
280 l_msg_string := l_msg_string ||'('||p_msg_tab(i).element_value;
281 i := p_msg_tab.NEXT(i);
282 l_msg_string := l_msg_string ||' '||p_msg_tab(i).element_value;
283 i := p_msg_tab.NEXT(i);
284 l_msg_string := l_msg_string ||' '||p_msg_tab(i).element_value;
285 i := p_msg_tab.NEXT(i);
286 LOOP
287 l_msg_string := l_msg_string||', '||p_msg_tab(i).element_value;
288 i := p_msg_tab.NEXT(i);
289 l_msg_string := l_msg_string||' '||p_msg_tab(i).element_value;
290 i := p_msg_tab.NEXT(i);
291 l_msg_string := l_msg_string||' '||p_msg_tab(i).element_value;
292 EXIT WHEN (i = p_msg_tab.LAST);
293 i := p_msg_tab.NEXT(i);
294 END LOOP;
295 l_msg_string := l_msg_string ||')';
296 END IF;
297 ELSE
298 l_return_status := OKC_API.G_RET_STS_ERROR;
299 RAISE OKC_API.G_EXCEPTION_ERROR;
300 END IF;
301
302 --Build the clob
303 DBMS_LOB.CREATETEMPORARY(l_msg_clob,TRUE, DBMS_LOB.SESSION);
304 DBMS_LOB.OPEN (l_msg_clob, DBMS_LOB.LOB_READWRITE);
305 ctr := LENGTH(l_msg_string);
306 DBMS_LOB.WRITE(l_msg_clob, ctr, 1, l_msg_string);
307 DBMS_LOB.CLOSE(l_msg_clob);
308 p_msg_clob := l_msg_clob;
309
310 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
311 EXCEPTION
312 WHEN OKC_API.G_EXCEPTION_ERROR THEN
313 l_return_status := OKC_API.HANDLE_EXCEPTIONS
314 (
315 l_api_name,
316 G_PKG_NAME,
317 'OKC_API.G_RET_STS_ERROR',
318 x_msg_count,
319 x_msg_data,
320 '_PVT'
321 );
322
323 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
324 l_return_status := OKC_API.HANDLE_EXCEPTIONS
325 (
326 l_api_name,
327 G_PKG_NAME,
328 'OKC_API.G_RET_STS_UNEXP_ERROR',
329 x_msg_count,
330 x_msg_data,
331 '_PVT'
332 );
333
334 WHEN OTHERS THEN
335 l_return_status := OKC_API.HANDLE_EXCEPTIONS
336 (
337 l_api_name,
338 G_PKG_NAME,
339 'OTHERS',
340 x_msg_count,
341 x_msg_data,
342 '_PVT'
343 );
344 END get_clob_msg;
345 end OKC_AQ_WRITE_ERROR_PVT;