[Home] [Help]
PACKAGE BODY: APPS.JTF_FM_IH_LOGGER_PVT
Source
1 PACKAGE BODY JTF_FM_IH_LOGGER_PVT AS
2 /* $Header: jtffmihb.pls 120.6 2006/06/13 22:52:45 ahattark noship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'JTF_FM_IH_LOGGER_PVT';
5 TOTAL_INTERACTIONS NUMBER := 1000;
6
7 --Function to find the outcome id setup for P_server_id
8 FUNCTION get_outcome_id(P_server_ID IN Number) RETURN NUMBER
9 IS
10
11 cursor outcome_cur (P_server_ID IN Number) is
12 select ih_outcome_id
13 from jtf_fm_service
14 where server_id = P_server_ID;
15
16 l_outcome_id NUMBER;
17
18 BEGIN
19
20 open outcome_cur(P_server_ID);
21 FETCH outcome_cur INTO l_outcome_id ;
22 CLOSE outcome_cur;
23
24 if l_outcome_id is null THEN
25 l_outcome_id := 10; -- 'Req Proc' from jtf_ih_outcomes_vl
26 end if;
27
28 return l_outcome_id;
29
30 END get_outcome_id;
31
32
33 --Function to return not_sent id for P_server_id
34 FUNCTION get_notsent_result(P_server_ID IN Number) RETURN NUMBER
35 IS
36
37 cursor ihresult_cur(P_server_ID IN Number) is
38 select ih_failure_result_id
39 from jtf_fm_service where server_id = P_server_ID;
40
41 l_notsent_result_id NUMBER := 0;
42
43 BEGIN
44
45 open ihresult_cur(P_server_ID);
46 FETCH ihresult_cur INTO l_notsent_result_id ;
47 CLOSE ihresult_cur;
48
49 if l_notsent_result_id is null then
50 l_notsent_result_id := 9; --'Not Sent' from jtf_ih_results_vl
51 end if;
52
53 return l_notsent_result_id;
54
55 END get_notsent_result;
56
57 --Function to return sent id for P_server_id
58 FUNCTION get_sent_result(P_server_ID Number) RETURN NUMBER
59 IS
60
61 cursor ihresult_cur(P_server_ID Number) is
62 select ih_success_result_id
63 from jtf_fm_service where server_id = P_server_ID;
64
65 l_sent_result_id NUMBER := 0;
66 BEGIN
67
68 open ihresult_cur(P_server_ID);
69 FETCH ihresult_cur INTO l_sent_result_id ;
70 CLOSE ihresult_cur;
71
72 if l_sent_result_id is null then
73 l_sent_result_id := 10; --'Sent' from jtf_ih_results_vl
74 end if;
75
76 return l_sent_result_id;
77
78 END get_sent_result;
79
80 --display procedure to troubleshoot
81 PROCEDURE DISPLAY(CLOBSTR IN CLOB)
82 IS
83
84 blb_length INTEGER;
85 len INTEGER;
86 pos INTEGER;
87 amt BINARY_INTEGER;
88 buf VARCHAR2(60); --RAW(40);
89
90
91 BEGIN
92 amt := 50;
93 blb_length := DBMS_LOB.GETLENGTH(CLOBSTR);
94 len := 1;
95 pos := 1;
96
97
98 DBMS_OUTPUT.PUT_LINE(blb_length);
99
100
101 while (len < blb_length) loop
102
103 dbms_lob.read(CLOBSTR, amt, len, buf);
104 DBMS_OUTPUT.PUT_line(buf);
105 len := len + amt;
106
107 end loop;
108
109 END DISPLAY;
110
111 --Function to add header -INTERACTIONREQUEST to the xml
112 FUNCTION ADD_HEADER(Preq_ID IN NUMBER) return VARCHAR2
113 IS
114
115 lBulkWriterCode VARCHAR2(5) := 'JTO';
116 lBulkBatchType VARCHAR2(10) := 'FMREQUEST';
117
118 l_Header_Str VARCHAR2(500);
119
120 BEGIN
121
122 l_Header_Str := '<INTERACTIONREQUEST bulk_writer_code="' || lBulkWriterCode || '" ';
123 l_Header_Str := l_Header_Str || 'bulk_batch_type="' || lBulkBatchType;
124 l_Header_Str := l_Header_Str || '" ' || 'bulk_batch_id="' || Preq_ID || '">';
125
126 return l_Header_Str;
127
128 END ADD_HEADER;
129
130 --Procedure looks up for READYTOLOG records in jtf_fm_request_history_all. Calls api to move line records to jtf_fm_processed for this request_id and then creates an interaction string for the lines
131 PROCEDURE Log_Interaction_History(P_COMMIT IN VARCHAR2 := FND_API.G_FALSE,
132 p_server_id IN NUMBER,
133 x_request_id out nocopy NUMBER,
134 x_return_status out nocopy varchar2,
135 x_msg_count out nocopy number,
136 x_msg_data out nocopy varchar2)
137 IS
138
139 l_api_name CONSTANT VARCHAR2(30) := 'Log_InteractionHistory';
140 l_full_name CONSTANT VARCHAR2(2000) := G_PKG_NAME || '.' || l_api_name;
141
142 l_user_data System.IH_BULK_TYPE;
143 l_string long;
144 l_interaction long ;--VARCHAR2(32767);
145 l_interaction_clob CLOB := EMPTY_CLOB;
146 l_spacechar VARCHAR2(2);
147 l_quote VARCHAR2(2);
148
149 l_BulkWriterCode VARCHAR2(5) := 'JTO';
150 l_BulkBatchType VARCHAR2(10) := 'FMREQUEST';
151
152 l_counter NUMBER := 0;
153
154 l_request_line_id NUMBER;
155 l_mesg_id RAW(16);
156
157 l_result_id Number := 0;
158 l_not_sent_result_id Number := 0;
159 l_sent_result_id Number := 0;
160 l_attribute1 VARCHAR2(25);
161 l_media_type VARCHAR2(10) := 'EMAIL';
162 l_media_direction VARCHAR2(20) := 'OUTBOUND';
163 l_COLLATERAL Number := 3;
164 l_handler_id Number := 690;
165
166 l_MediaItem_Identifier Number := 0;
167
168 l_doc_id NUMBER := 1;
169 l_doc_ref VARCHAR(15) := 'UNSET';
170
171 l_return_status VARCHAR2(10);
172 l_msg_count NUMBER;
173 l_msg_data VARCHAR2(10);
174 l_log_interaction VARCHAR2(3);
175
176 l_outcome_id NUMBER;
177 l_resource_id NUMBER;
178
179 l_line_status VARCHAR2(10);
180 l_header_status_success_flag VARCHAR2(1) := 'F';
181 l_header_status_failure_flag VARCHAR2(1) := 'F';
182
183 resource_not_found_exception EXCEPTION;
184
185 --Header table for Readytolog status
186 cursor ih_header is
187 select fm.HIST_REQ_ID , fm.SOURCE_CODE_ID, fm.SOURCE_CODE, fm.OBJECT_ID, fm.OBJECT_TYPE, fm.OUTCOME_DESC, fm.SERVER_ID, fm.USER_ID, fm.OUTCOME_CODE
188 from JTF_FM_REQUEST_HISTORY_ALL fm
189 where fm.OUTCOME_CODE = 'READYTOLOG'
190 and fm.SERVER_ID = p_server_id
191 and rownum < 2
192 order by fm.PRIORITY, fm.HIST_REQ_ID;
193
194 l_header_rec ih_header%ROWTYPE;
195
196 --Query lines for the readytolog request id
197 cursor ih_lines (l_request_id IN NUMBER, l_sent_result_id IN NUMBER, l_not_sent_result_id IN NUMBER) is
198 select REQUEST_ID, JOB, PARTY_ID, EMAIL_ADDRESS, OUTCOME_CODE, decode(EMAIL_STATUS,'SENT',l_sent_result_id,l_not_sent_result_id) as RESULT_ID,
199 to_char(CREATION_DATE, 'MON DD RRRR HH24:MI:SS') as CREATION_DATE
200 from jtf_fm_processed
201 where request_id = l_request_id;
202
203 --Resource id cursor
204 cursor resource_cur (l_user_id IN NUMBER) IS
205 select resource_id
206 from jtf_rs_resource_extns
207 where user_id = l_user_id;
208
209 BEGIN
210
211 --Initialize message list if p_init_msg_list is TRUE.
212 FND_MSG_PUB.initialize;
213
214 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
215 FND_MESSAGE.Set_Name('JTF', 'JTF_FM_API_DEBUG_MESSAGE');
216 FND_MESSAGE.Set_Token('ARG1', l_full_name||': Start');
217 FND_MSG_PUB.Add;
218 END IF;
219
220 -- Initialize API return status to success
221 x_return_status := FND_API.G_RET_STS_SUCCESS;
222
223 l_spacechar := ' ';
224 l_quote := '"';
225
226 OPEN ih_header;
227 FETCH ih_header into l_header_rec;
228
229 -- if no requests in Readytolog status, then return to calling program with request_id =-1
230 if ih_header%NOTFOUND then
231 x_request_id := -1;
232 return;
233 end if;
234
235 begin
236
237 SAVEPOINT moverequest;
238 --Joby's Api for Move rows with l_header_rec.hist_req_id and user_history;
239 JTF_FM_INT_REQUEST_PKG.move_request(l_header_rec.hist_req_id,l_log_interaction, l_return_status,l_msg_count,l_msg_data) ;
240
241 EXCEPTION
242
243 WHEN FND_API.G_EXC_ERROR THEN
244 ROLLBACK TO moverequest;
245
246 x_request_id := l_header_rec.hist_req_id;
247 x_return_status := FND_API.g_ret_sts_error ;
248 FND_MSG_PUB.Count_AND_Get
249 ( p_count => x_msg_count,
250 p_data => x_msg_data,
251 p_encoded => FND_API.G_FALSE
252 );
253
254 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
255 ROLLBACK TO moverequest;
256
257 x_request_id := l_header_rec.hist_req_id;
258 x_return_status := FND_API.g_ret_sts_unexp_error ;
259 FND_MSG_PUB.Count_AND_Get
260 ( p_count => x_msg_count,
261 p_data => x_msg_data,
262 p_encoded => FND_API.G_FALSE
263 );
264
265 WHEN OTHERS THEN
266 ROLLBACK TO moverequest;
267
268 x_return_status := FND_API.g_ret_sts_unexp_error ;
269 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
270 THEN
271 null;
272 END IF;
273 FND_MSG_PUB.Count_AND_Get
274 ( p_count => x_msg_count,
275 p_data => x_msg_data,
276 p_encoded => FND_API.G_FALSE
277 );
278
279
280 end;
281
282 -- Standard begin of API savepoint
283 SAVEPOINT log_interactionrequest;
284
285 l_sent_result_id := get_sent_result(l_header_rec.SERVER_ID);
286 l_not_sent_result_id := get_notsent_result(l_header_rec.SERVER_ID);
287 l_outcome_id := get_outcome_id(l_header_rec.SERVER_ID);
288
289 OPEN resource_cur(l_header_rec.user_id);
290 FETCH resource_cur into l_resource_id;
291
292 if resource_cur%NOTFOUND then
293 raise RESOURCE_NOT_FOUND_EXCEPTION;
294 end if;
295
296 if ((upper(l_log_interaction) = 'YES') AND (l_return_status = FND_API.G_RET_STS_SUCCESS ))then
297 dbms_lob.createtemporary(l_interaction_clob, TRUE,DBMS_LOB.SESSION);
298 dbms_lob.open(l_interaction_clob, dbms_lob.lob_readwrite);
299
300 --Header
301 l_interaction := ADD_HEADER(l_header_rec.HIST_REQ_ID);
302
303 for j in ih_lines(l_header_rec.HIST_REQ_ID,l_sent_result_id, l_not_sent_result_id) loop
304
305 l_request_line_id := j.job;
306
307 l_line_status := j.outcome_code;
308
309 if ( ( l_line_status = 'SUCCESS' ) or ( l_line_status is null ) )then
310 l_header_status_success_flag := 'T';
311 elsif ( l_line_status = 'FAILURE' ) then
312 l_header_status_failure_flag := 'T';
313 end if;
314
315
316 l_interaction := l_interaction || FND_GLOBAL.Local_Chr(10);
317 l_attribute1 := to_char(l_header_rec.hist_req_id ||'_'|| j.job);
318
319 --Interaction
320 l_interaction := l_interaction || '<INTERACTION bulk_interaction_id=' || l_quote ||j.JOB|| l_quote || l_spacechar;
321 l_interaction := l_interaction || 'party_id=' || l_quote ||j.PARTY_ID|| l_quote || l_spacechar|| 'resource_id=' || l_quote || l_resource_id || l_quote|| l_spacechar;
322 l_interaction := l_interaction || 'handler_id=' || l_quote || l_handler_id|| l_quote || l_spacechar|| 'outcome_id=' || l_quote ||l_outcome_id|| l_quote|| l_spacechar;
323
324 l_interaction := l_interaction || 'result_id=' || l_quote || j.RESULT_ID || l_quote || l_spacechar|| 'source_code_id=' || l_quote ||l_header_rec.SOURCE_CODE_ID|| l_quote|| l_spacechar;
325 l_interaction := l_interaction || 'source_code=' || l_quote ||l_header_rec.SOURCE_CODE|| l_quote || l_spacechar|| 'object_type=' || l_quote ||l_header_rec.OBJECT_TYPE|| l_quote|| l_spacechar ;
326 l_interaction := l_interaction || 'start_date_time=' || l_quote ||j.CREATION_DATE|| l_quote || l_spacechar|| 'end_date_time=' || l_quote ||j.CREATION_DATE|| l_quote|| l_spacechar ;
327 l_interaction := l_interaction || 'object_id=' || l_quote ||l_header_rec.OBJECT_ID|| l_quote||l_spacechar || 'attribute1='|| l_quote || l_attribute1 || l_quote ||'>';
328
329 --activity
330 l_interaction := l_interaction || FND_GLOBAL.Local_Chr(10) || '<ACTIVITY doc_id=' || l_quote ||l_doc_id|| l_quote || l_spacechar;
331
332 l_interaction := l_interaction || 'doc_ref='|| l_quote ||l_doc_ref || l_quote || l_spacechar ||'doc_source_object_name=' || l_quote ||j.REQUEST_ID || l_quote || l_spacechar;
333 l_interaction := l_interaction || 'start_date_time=' || l_quote ||j.CREATION_DATE|| l_quote || l_spacechar|| 'end_date_time=' || l_quote ||j.CREATION_DATE|| l_quote|| l_spacechar ;
334 l_interaction := l_interaction || 'action_item_id=' || l_quote || l_COLLATERAL|| l_quote || l_spacechar || 'outcome_id=' || l_quote || l_outcome_id || l_quote || l_spacechar;
335 l_interaction := l_interaction || 'result_id=' || l_quote || j.RESULT_ID || l_quote || l_spacechar || 'mediaitem_identifier='|| l_quote || l_quote || '/>';
336
337 --Media Item
341 l_interaction := l_interaction || FND_GLOBAL.Local_Chr(10) ||'</MEDIAITEM>';
338 l_interaction := l_interaction || FND_GLOBAL.Local_Chr(10) || '<MEDIAITEM direction=' || l_quote ||l_media_direction || l_quote || l_spacechar || 'source_item_id=' || l_quote ||j.REQUEST_ID|| l_quote || l_spacechar;
339 l_interaction := l_interaction || 'media_item_type=' || l_quote ||l_media_type|| l_quote || l_spacechar || 'media_item_ref=' || l_quote ||null|| l_quote || l_spacechar;
340 l_interaction := l_interaction || 'mediaitem_identifier=' || l_quote ||l_MediaItem_Identifier|| l_quote || l_spacechar || 'address=' || l_quote ||j.EMAIL_ADDRESS|| l_quote || '>';
342 l_interaction := l_interaction || FND_GLOBAL.Local_Chr(10) ||'</INTERACTION>';
343
344 dbms_lob.writeappend(l_interaction_clob, LENGTH(l_interaction), l_interaction);
345 l_interaction :='';
346
347 l_counter := l_counter + 1;
348
349 --Maximum records for one interaction is 1000. Create a new interaction if counter has reached 1000
350 if l_counter = TOTAL_INTERACTIONS THEN
351
352 l_interaction := l_interaction || FND_GLOBAL.Local_Chr(10) ||'</INTERACTIONREQUEST>';
353
354 dbms_lob.writeappend(l_interaction_clob, LENGTH(l_interaction), l_interaction);
355 l_interaction :='';
356
357 --Enqueue
358 JTF_IH_BULK_Q_PKG.CLOBENQUEUE(l_BulkWriterCode,l_BulkBatchType,l_header_rec.hist_req_id,l_request_line_id,l_mesg_id);
359 select user_data into l_user_data from jtf_ih_bulk_qtbl where msgid = hextoraw(l_mesg_id);
360 DBMS_LOB.COPY(l_user_data.BulkInteractionRequest,l_interaction_clob, DBMS_LOB.GETLENGTH(l_interaction_clob) , 1,1);
361 --commit;
362
363 l_counter := 0;
364
365 -- call header again on new request
366 l_interaction := ADD_HEADER(l_header_rec.HIST_REQ_ID);
367
368 --release clob
369 DBMS_LOB.FREETEMPORARY (l_interaction_clob);
370
371 --reinitialize clob
372 dbms_lob.createtemporary(l_interaction_clob, TRUE,DBMS_LOB.SESSION);
373 dbms_lob.open(l_interaction_clob, dbms_lob.lob_readwrite);
374
375 end if;
376
377 end loop;
378
379 ELSIF(l_return_status = FND_API.G_RET_STS_ERROR) THEN
380 RAISE FND_API.G_EXC_ERROR;
381 ELSE
382 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
383 END IF;
384
385 --Enqueue for interaction created from less than 1000 records
386 if l_counter < TOTAL_INTERACTIONS THEN
387
388 l_interaction := l_interaction || FND_GLOBAL.Local_Chr(10) ||'</INTERACTIONREQUEST>';
389 dbms_lob.writeappend(l_interaction_clob, LENGTH(l_interaction), l_interaction);
390 l_interaction :='';
391
392 --Enqueueu
393 JTF_IH_BULK_Q_PKG.CLOBENQUEUE(l_BulkWriterCode,l_BulkBatchType, l_header_rec.hist_req_id,l_request_line_id,l_mesg_id);
394 select user_data into l_user_data from jtf_ih_bulk_qtbl where msgid = hextoraw(l_mesg_id);
395
396 DBMS_LOB.COPY(l_user_data.BulkInteractionRequest,l_interaction_clob, DBMS_LOB.GETLENGTH(l_interaction_clob) , 1,1);
397 --commit;
398
399 end if;
400
401
402 -- DISPLAY(l_interaction_clob);
403
404 --remove header from the jtf_fm_status_all table
405 Remove_from_status(l_header_rec.hist_req_id);
406
407 --Update jtf_fm_request_history_all to success
408 if ( ( l_header_status_success_flag = 'T') and ( l_header_status_failure_flag = 'F')) then
409 Update_history(l_header_rec.hist_req_id, 'SUCCESS');
410 elsif ( ( l_header_status_success_flag = 'F') and ( l_header_status_failure_flag = 'T')) then
411 Update_history(l_header_rec.hist_req_id, 'FAILURE');
412 elsif ( ( l_header_status_success_flag = 'T') and ( l_header_status_failure_flag = 'T')) then
413 Update_history(l_header_rec.hist_req_id, 'PARTIAL_SUCCESS');
414 elsif ( ( l_header_status_success_flag = 'F') and ( l_header_status_failure_flag = 'F')) then
415 Update_history(l_header_rec.hist_req_id, 'FAILURE');
416 end if;
417
418
419 IF p_commit = FND_API.g_true then
420 COMMIT WORK;
421 END IF;
422
423 x_return_status := 'S';
424 x_request_id := l_header_rec.hist_req_id;
425
426 FND_MSG_PUB.Count_AND_Get
427 ( p_count => x_msg_count,
428 p_data => x_msg_data,
429 p_encoded => FND_API.G_FALSE );
430
431
432 EXCEPTION
433
434 WHEN RESOURCE_NOT_FOUND_EXCEPTION THEN
435 ROLLBACK TO Log_interactionrequest;
436
437 --remove header from the jtf_fm_status_all table
438 Remove_from_status(l_header_rec.hist_req_id);
439
440 Update_history(l_header_rec.hist_req_id, 'IHFAILED');
441 commit;
442
443 x_request_id := l_header_rec.hist_req_id;
444 x_return_status := FND_API.g_ret_sts_error ;
445 x_msg_count := 1;
446 x_msg_data := 'No valid resource id for this user';
447
448 WHEN FND_API.G_EXC_ERROR THEN
449 ROLLBACK TO Log_interactionrequest;
450
451 --remove header from the jtf_fm_status_all table
452 Remove_from_status(l_header_rec.hist_req_id);
453
454 Update_history(l_header_rec.hist_req_id, 'IHFAILED');
455 commit;
456
457 x_request_id := l_header_rec.hist_req_id;
458 x_return_status := FND_API.g_ret_sts_error ;
459 FND_MSG_PUB.Count_AND_Get
460 ( p_count => x_msg_count,
461 p_data => x_msg_data,
462 p_encoded => FND_API.G_FALSE
463 );
464
465 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
466 ROLLBACK TO Log_interactionrequest;
467
468 --remove header from the jtf_fm_status_all table
469 Remove_from_status(l_header_rec.hist_req_id);
470
471 Update_history(l_header_rec.hist_req_id, 'IHFAILED');
472 commit;
473
474 x_request_id := l_header_rec.hist_req_id;
475 x_return_status := FND_API.g_ret_sts_unexp_error ;
476 FND_MSG_PUB.Count_AND_Get
477 ( p_count => x_msg_count,
478 p_data => x_msg_data,
479 p_encoded => FND_API.G_FALSE
480 );
481
482 WHEN OTHERS THEN
483 ROLLBACK TO Log_interactionrequest;
484
485 --remove header from the jtf_fm_status_all table
486 Remove_from_status(l_header_rec.hist_req_id);
487
488 Update_history(l_header_rec.hist_req_id, 'IHFAILED');
489 commit;
490
491 x_return_status := FND_API.g_ret_sts_unexp_error ;
492 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
493 THEN
494 null;
495 END IF;
496 FND_MSG_PUB.Count_AND_Get
497 ( p_count => x_msg_count,
498 p_data => x_msg_data,
499 p_encoded => FND_API.G_FALSE
500 );
501
502 END Log_Interaction_History;
503
504 PROCEDURE Remove_from_status(P_Request_ID IN NUMBER) Is
505 BEGIN
506
507 delete from jtf_fm_status_all
508 where request_id = P_Request_ID;
509
510 --commit;
511
512 END;
513
514 PROCEDURE Update_history(P_Request_ID IN NUMBER, P_Status IN VARCHAR) Is
515 BEGIN
516
517 update JTF_FM_REQUEST_HISTORY_ALL
518 set outcome_code = P_Status
519 where hist_req_id = P_Request_id;
520
521 --commit;
522
523 END;
524
525 END JTF_FM_IH_LOGGER_PVT;