[Home] [Help]
PACKAGE BODY: APPS.JTF_IH_BULK
Source
1 PACKAGE BODY JTF_IH_BULK AS
2 /* $Header: JTFIHBKB.pls 120.7 2006/06/13 14:56:27 rdday noship $*/
3
4 -- profile option value that dictates stats data gathering and reporting
5 G_STATS_PROFILE_OPTION VARCHAR2(30);
6 G_STATS_ENABLED BOOLEAN := FALSE;
7
8 -- the following package global variables are the easiest means of tracking them
9 -- across multiple procedures
10 G_XML_PARSE_TIME_TOTAL NUMBER := 0;
11 G_XML_PARSE_TIME_MIN NUMBER := 0;
12 G_XML_PARSE_TIME_MAX NUMBER := 0;
13
14 G_INT_PROC_TIME_CUM NUMBER := 0;
15 G_INT_PROC_TIME_MAX NUMBER;
16 G_INT_PROC_TIME_MIN NUMBER := 100000;
17 G_NUM_INT_TOTAL NUMBER := 0;
18 G_NUM_INT_PROC_TOTAL NUMBER := 0;
19 G_NUM_INT_MAX NUMBER;
20 G_NUM_INT_MIN NUMBER := 100000;
21
22 G_NUM_ACT_TOTAL NUMBER := 0;
23 G_NUM_ACT_MAX NUMBER;
24 G_NUM_ACT_MIN NUMBER := 100000;
25
26 G_NUM_MED_TOTAL NUMBER := 0;
27 G_NUM_MED_MAX NUMBER;
28 G_NUM_MED_MIN NUMBER := 100000;
29
30 -- for now these are not being tracked
31 g_num_mlcs_total NUMBER := 0;
32 g_num_mlcs_max NUMBER;
33 g_num_mlcs_min NUMBER := 100000;
34
35 -- globally used in the package
36 G_DATE_FORMAT CONSTANT VARCHAR2(50) := 'MON DD RRRR HH24:MI:SS';
37 G_GMT_TZ CONSTANT NUMBER := 0; -- GMT is 0
38 G_SERVER_TZ NUMBER;
39 G_CONC_REQUEST_ID NUMBER;
40 G_CONC_PROGRAM_ID NUMBER;
41 G_PROG_APPL_ID NUMBER;
42 G_USER_ID NUMBER;
43 G_LOGIN_ID NUMBER;
44 G_USE_STDOUT BOOLEAN := FALSE;
45 l_fnd_log_msg VARCHAR2(2000);
46
47 -- moved here to track heart-beat across package
48 g_hrt_beat NUMBER;
49
50 --
51 -- Strictly a convenience function to convert and return the input GMT time
52 -- as a db server time. Does nothing but call an HZ provided procedure.
53 --
54 FUNCTION SERVER_DATE_FROM_GMT_STR
55 (
56 p_gmt_date_str IN VARCHAR2
57 ) RETURN DATE IS
58
59 l_gmt_date DATE;
60 l_server_date DATE;
61 l_ret_status VARCHAR2(5);
62 l_msg_count NUMBER;
63 l_msg_data VARCHAR2(2000);
64
65 BEGIN
66
67 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
68 l_fnd_log_msg := 'FUNCTION SERVER_DATE_FROM_GMT_STR In Parameters'||
69 'p_gmt_date_str = '|| p_gmt_date_str;
70 --dbms_output.put_line(l_fnd_log_msg);
71 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
72 'jtf.plsql.JTF_IH_BULK.SERVER_DATE_FROM_GMT_STR.begin', l_fnd_log_msg);
73 END IF;
74
75 IF (p_gmt_date_str IS NULL) THEN
76 RETURN NULL;
77 END IF;
78
79 l_gmt_date := TO_DATE(p_gmt_date_str, G_DATE_FORMAT);
80
81 HZ_TIMEZONE_PUB.GET_TIME(P_API_VERSION => 1.0,
82 P_INIT_MSG_LIST => FND_API.G_FALSE,
83 P_SOURCE_TZ_ID => G_GMT_TZ,
84 P_DEST_TZ_ID => G_SERVER_TZ,
85 P_SOURCE_DAY_TIME => l_gmt_date,
86 X_DEST_DAY_TIME => l_server_date,
87 X_RETURN_STATUS => l_ret_status,
88 X_MSG_COUNT => l_msg_count,
89 X_MSG_DATA => l_msg_data);
90
91 -- what do you do if the time cannot be coverted ?!
92 -- take the time as it is given to be server time
93 IF (l_ret_status <> FND_API.G_RET_STS_SUCCESS) THEN
94 NULL;
95 l_server_date := l_gmt_date;
96 END IF;
97
98 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
99 l_fnd_log_msg := 'FUNCTION SERVER_DATE_FROM_GMT_STR Return Parameter'||
100 'l_server_date = '|| l_server_date;
101 --dbms_output.put_line(l_fnd_log_msg);
102 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
103 'jtf.plsql.JTF_IH_BULK.SERVER_DATE_FROM_GMT_STR.end', l_fnd_log_msg);
104 END IF;
105
106 RETURN l_server_date;
107
108 END SERVER_DATE_FROM_GMT_STR;
109
110
111 -- Main procedure of this package. It is run as a concurrent program.
112 PROCEDURE BULK_PROCESSOR_CONC
113 (
114 errbuf OUT NOCOPY VARCHAR2,
115 retcode OUT NOCOPY VARCHAR2
116 ) IS
117
118 deq_options dbms_aq.dequeue_options_t;
119 deq_del_option dbms_aq.dequeue_options_t;
120 msg_props dbms_aq.message_properties_t;
121 bulk_obj system.ih_bulk_type;
122 l_dummy_payload system.ih_bulk_type;
123 l_dummy_msg_id RAW(16);
124 l_msg_id RAW(16);
125 num_rec NUMBER;
126 l_ret_status VARCHAR2(1);
127
128 l_stats_profile_value varchar2(5);
129
130 average number(20, 2);
131 l_not_eof BOOLEAN;
132 t1_beg NUMBER;
133 t1_end NUMBER;
134 t2_beg NUMBER;
135 t2_end NUMBER;
136 t3_beg NUMBER;
137 t3_end NUMBER;
138 l_time_onerec NUMBER := 0;
139 l_time_cum NUMBER := 0;
140 l_time_max NUMBER; -- these two values need to be such that
141 l_time_min NUMBER := 100000; -- they are easily reset.
142 error NUMBER;
143 errm VARCHAR2(2000);
144 saved_bulk_obj system.IH_BULK_TYPE;
145
146 aq_eof EXCEPTION;
147 pragma EXCEPTION_INIT (aq_eof, -25228);
148
149 -- Bug3781768- Perf fix for literal usuage
150 l_msg_id_perf RAW (16);
151
152 BEGIN
153 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
154 l_fnd_log_msg := 'BULK_PROCESSOR_CONC Begin:';
155 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
156 'jtf.plsql.JTF_IH_BULK.BULK_PROCESSOR_CONC.begin', l_fnd_log_msg);
157 END IF;
158
159 -- global variables initialization to remove GSCC warnings
160 -- G_STATS_PROFILE_OPTION := 'JTF_IH_BULK_STATS_ENABLED';
161 G_INT_PROC_TIME_MAX := -1;
162 G_NUM_INT_MAX := -1;
163 G_NUM_ACT_MAX := -1;
164 G_NUM_MED_MAX := -1;
165 g_num_mlcs_max := -1;
166 G_USER_ID := FND_GLOBAL.USER_ID;
167 G_LOGIN_ID := FND_GLOBAL.LOGIN_ID;
168
169 -- local variables initialization to remove GSCC warnings
170 l_time_max := -1;
171
172 -- To do -
173 -- (Relatively straight forward process to begin with.)
174 -- Check if we need to recover from a crash
175 -- (new step added for recovering from crashes)
176 -- Get first message from queue.
177 -- process it.
178 -- if more available, repeat process; else done.
179
180 -- check if we have to gather stats or not
181 /*l_stats_profile_value := FND_PROFILE.VALUE(G_STATS_PROFILE_OPTION);
182 IF (l_stats_profile_value = 'Y' OR l_stats_profile_value = 'y') THEN
183 G_STATS_ENABLED := TRUE;
184 END IF;*/
185
186 --IF (g_stats_enabled) THEN
187 t2_beg := DBMS_UTILITY.GET_TIME;
188 --END IF;
189
190 -- get server tz value, it is required to transform the start and end date
191 -- time values from gmt. default to GMT if server time zone is not set.
192 G_SERVER_TZ := FND_PROFILE.VALUE('SERVER_TIMEZONE_ID');
193 IF (G_SERVER_TZ IS NULL) THEN
194
195 --FND_MESSAGE.SET_NAME('JTF', 'JTF_IH_BULK_NO_TZ');
196 --FND_FILE.PUT_LINE(FND_FILE.LOG, FND_MESSAGE.GET);
197
198
199 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
200 FND_MESSAGE.SET_NAME('JTF', 'JTF_IH_BULK_NO_TZ');
201 FND_LOG.MESSAGE(FND_LOG.LEVEL_STATEMENT,
202 'jtf.plsql.JTF_IH_BULK.BULK_PROCESSOR_CONC', TRUE);
203 END IF;
204 G_SERVER_TZ := 0;
205
206 END IF;
207
208
209 -- retrieve and save some global values for use throughout
210 -- N O T E - all the following values will be -1 when run in command line mode
211 G_CONC_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
212 G_USER_ID := FND_GLOBAL.USER_ID;
213 G_LOGIN_ID := FND_GLOBAL.LOGIN_ID;
214 G_CONC_PROGRAM_ID := FND_GLOBAL.CONC_PROGRAM_ID;
215 G_PROG_APPL_ID := FND_GLOBAL.PROG_APPL_ID;
216
217 -- All the FND_GLOBAL values are -1 if this program is run in command line
218 -- mode. Some of the values are important for recovery and such, so they are
219 -- being taken care of.
220 IF (G_CONC_REQUEST_ID = -1) THEN
221 SELECT DBMS_UTILITY.get_time INTO G_CONC_REQUEST_ID FROM dual;
222 END IF;
223
224 IF (G_CONC_PROGRAM_ID = -1) THEN
225 G_CONC_PROGRAM_ID := G_CONC_REQUEST_ID;
226 END IF;
227
228 IF (G_USER_ID = -1) THEN
229 SELECT UID INTO G_USER_ID FROM dual;
230 END IF;
231
232 -- Before we get to regular programming, check if any crash recovery needs to
233 -- be done.
234 PERFORM_CRASH_RECOVERY();
235
236 -- Before beginning to process records from AQ, create a record in the
237 -- recovery table. This record will keep getting updated as bulk records
238 -- get processed. It will be deleted before this routine ends.
239
240 -- Perf fix for literal usuage
241 l_msg_id_perf := '0';
242
243 INSERT INTO jtf_ih_bulk_recovery
244 ( recovery_id, request_id, msg_id, bulk_interaction_request,
245 num_int_processed, created_by, creation_date, last_updated_by,
246 last_update_date, last_update_login, program_id, program_application_id,
247 program_update_date)
248 VALUES
249 (jtf_ih_bulk_recovery_s1.nextval, G_CONC_REQUEST_ID, l_msg_id_perf,
250 EMPTY_CLOB(), null, G_USER_ID, sysdate, G_USER_ID, sysdate, G_LOGIN_ID,
251 G_CONC_REQUEST_ID, G_PROG_APPL_ID, sysdate);
252
253 g_hrt_beat := -50;
254 --FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Created row in recovery table');
255
256 -- dequeue options
257 -- dequeue mode shall be LOCKED for now, the data will get deleted after
258 -- saving in RECOVERY table
259 deq_options.dequeue_mode := dbms_aq.LOCKED;
260 deq_options.navigation := dbms_aq.NEXT_MESSAGE;
261 deq_options.wait := dbms_aq.NO_WAIT;
262
263 -- these are the options for the second dequeue
264 deq_del_option.dequeue_mode := dbms_aq.REMOVE_NODATA;
265 deq_del_option.wait := dbms_aq.NO_WAIT;
266
267 num_rec := 0;
268
269 --LOOP
270 l_not_eof := TRUE;
271 WHILE l_not_eof LOOP
272
273 BEGIN
274
275 -- this is being done so that the exception handler way down below can
276 -- log any bulk requests that are fetched but not processed
277 l_msg_id := null;
278
279 DBMS_AQ.DEQUEUE (
280 queue_name => 'JTF_IH_BULK_Q',
281 dequeue_options => deq_options,
282 message_properties => msg_props,
283 payload => bulk_obj,
284 msgid => l_msg_id);
285
286 IF (g_stats_enabled) THEN
287 t1_beg := DBMS_UTILITY.GET_TIME;
288 END IF;
289
290 -- Turns out, at least during testing, that the clob can be empty
291 IF (DBMS_LOB.GETLENGTH(bulk_obj.bulkInteractionRequest) > 0) THEN
292 -- before beginning to process the bulk record, save it in the recovery
293 -- table
294 g_hrt_beat := -51;
295 SELECT user_data INTO saved_bulk_obj
296 FROM jtf_ih_bulk_qtbl
297 WHERE msgid = l_msg_id;
298
299 g_hrt_beat := -51.50;
300 UPDATE jtf_ih_bulk_recovery
301 SET
302 bulk_writer_code = saved_bulk_obj.bulkWriterCode,
303 bulk_batch_type = saved_bulk_obj.bulkBatchType,
304 bulk_batch_id = saved_bulk_obj.bulkBatchId,
305 bulk_interaction_id = saved_bulk_obj.bulkInteractionId,
306 bulk_interaction_request = saved_bulk_obj.bulkInteractionRequest,
307 msg_id = l_msg_id,
308 last_update_date = sysdate,
309 program_update_date = sysdate
310 WHERE request_id = G_CONC_REQUEST_ID;
311
312 g_hrt_beat := -51.60;
313
314 -- Now delete the row from AQ
315 deq_del_option.msgid := l_msg_id;
316
317 DBMS_AQ.DEQUEUE (
318 queue_name => 'JTF_IH_BULK_Q',
319 dequeue_options => deq_del_option,
320 message_properties => msg_props,
321 payload => l_dummy_payload,
322 msgid => l_dummy_msg_id);
323
324 g_hrt_beat := -52;
325 COMMIT WORK;
326
327 g_hrt_beat := -53;
328 --FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Updated row in recovery table and removed from AQ');
329
330 -- process payload data
331 l_ret_status := PROCESS_BULK_RECORD(bulk_obj.bulkWriterCode,
332 bulk_obj.bulkBatchType,
333 bulk_obj.bulkBatchId,
334 bulk_obj.bulkInteractionRequest);
335
336 g_hrt_beat := -54;
337 --FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Process_record returned - ' || l_ret_status);
338 ELSE
339
340 g_hrt_beat := -51;
341
342 -- Now delete row from AQ
343 deq_del_option.msgid := l_msg_id;
344
345 DBMS_AQ.DEQUEUE (
346 queue_name => 'JTF_IH_BULK_Q',
347 dequeue_options => deq_del_option,
348 message_properties => msg_props,
349 payload => l_dummy_payload,
350 msgid => l_dummy_msg_id);
351
352 g_hrt_beat := -52;
353 COMMIT WORK;
354
355 g_hrt_beat := -53;
356
357 -- logging High Level: Found a bulk request of zero length
358 FND_MESSAGE.SET_NAME('JTF', 'JTF_IH_BULK_ZERO_LEN_REQ');
359 FND_MESSAGE.SET_TOKEN('WRITER_CODE', bulk_obj.bulkWriterCode);
360 FND_MESSAGE.SET_TOKEN('BATCH_TYPE', bulk_obj.bulkBatchType);
361 FND_MESSAGE.SET_TOKEN('BATCH_ID', bulk_obj.bulkBatchId);
362 FND_FILE.PUT_LINE(FND_FILE.LOG, FND_MESSAGE.GET);
363
364
365 -- logging Detail Level: Found a bulk request of zero length
366 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
367 FND_MESSAGE.SET_NAME('JTF', 'JTF_IH_BULK_ZERO_LEN_REQ');
368 FND_MESSAGE.SET_TOKEN('WRITER_CODE', bulk_obj.bulkWriterCode);
369 FND_MESSAGE.SET_TOKEN('BATCH_TYPE', bulk_obj.bulkBatchType);
370 FND_MESSAGE.SET_TOKEN('BATCH_ID', bulk_obj.bulkBatchId);
371 FND_LOG.MESSAGE(FND_LOG.LEVEL_STATEMENT,
372 'jtf.plsql.JTF_IH_BULK.BULK_PROCESSOR_CONC', TRUE);
373 END IF;
374
375 END IF;
376
377 t1_end := DBMS_UTILITY.GET_TIME;
378
379 -- processing times - one record, maximum, minimum and cumulative
380 l_time_onerec := (t1_end-t1_beg)*10;
381
382 IF (l_time_onerec > l_time_max) THEN
383 l_time_max := l_time_onerec;
384 END IF;
385
386 IF (l_time_onerec < l_time_min) THEN
387 l_time_min := l_time_onerec;
388 END IF;
389
390 l_time_cum := l_time_cum + l_time_onerec;
391 g_hrt_beat := -55;
392
393 --Logging Detail Level
394 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
395 l_fnd_log_msg := 'Time taken to process one record = '|| l_time_onerec ||
396 ', writer code = ' || bulk_obj.bulkWriterCode ||
397 ', batch type = ' || bulk_obj.bulkBatchType ||
398 ', batch id = ' || bulk_obj.bulkBatchId;
399 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
400 'jtf.plsql.JTF_IH_BULK.BULK_PROCESSOR_CONC', l_fnd_log_msg);
401 END IF;
402
403 num_rec := num_rec + 1;
404
405 EXCEPTION
406 WHEN aq_eof THEN
407
408 -- this error is ok, we just ran out of records in the aq
409 l_not_eof := FALSE;
410
411 -- logging Detail Level: End of records in Advanced Queue
412 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
413 FND_MESSAGE.SET_NAME('JTF', 'JTF_IH_BULK_AQ_EOF');
414 FND_MESSAGE.SET_TOKEN('NUM_REC', num_rec);
415 FND_LOG.MESSAGE(FND_LOG.LEVEL_STATEMENT,
416 'jtf.plsql.JTF_IH_BULK.BULK_PROCESSOR_CONC', TRUE);
417 END IF;
418
419 -- logging High Level: End of records in Advanced Queue
420 FND_MESSAGE.SET_NAME('JTF', 'JTF_IH_BULK_AQ_EOF');
421 FND_MESSAGE.SET_TOKEN('NUM_REC', num_rec);
422 FND_FILE.PUT_LINE(FND_FILE.LOG, FND_MESSAGE.GET);
423
424 -- output this irrespective of stats flag so that there is some
425 -- indication of work done.
426
427 FND_MESSAGE.SET_NAME('JTF', 'JTF_IH_BULK_AQ_EOF');
428 FND_MESSAGE.SET_TOKEN('NUM_REC', num_rec);
429 FND_FILE.PUT_LINE(FND_FILE.LOG, FND_MESSAGE.GET);
430
431 FND_MESSAGE.SET_NAME('JTF', 'JTF_IH_BULK_NUM_REC_PROC');
432 FND_MESSAGE.SET_TOKEN('NUM_REC', num_rec);
433 FND_FILE.PUT_LINE(FND_FILE.LOG, FND_MESSAGE.GET);
434
435 FND_MESSAGE.SET_NAME('JTF', 'JTF_IH_BULK_NUM_TOTAL_INT');
436 FND_MESSAGE.SET_TOKEN('NUM_INT', G_NUM_INT_TOTAL);
437 FND_FILE.PUT_LINE(FND_FILE.LOG, FND_MESSAGE.GET);
438
439 FND_MESSAGE.SET_NAME('JTF', 'JTF_IH_BULK_NUM_OK_INT');
440 FND_MESSAGE.SET_TOKEN('NUM_INT', G_NUM_INT_PROC_TOTAL);
441 FND_FILE.PUT_LINE(FND_FILE.LOG, FND_MESSAGE.GET);
442
443 FND_MESSAGE.SET_NAME('JTF', 'JTF_IH_BULK_NUM_ERR_INT');
444 FND_MESSAGE.SET_TOKEN('NUM_INT', (G_NUM_INT_TOTAL-G_NUM_INT_PROC_TOTAL));
445 FND_FILE.PUT_LINE(FND_FILE.LOG, FND_MESSAGE.GET);
446
447 WHEN OTHERS THEN
448 RAISE;
449 END;
450 END LOOP;
451
452 -- We are done processing bulk records, now get rid of the record in the
453 -- recovery table
454 DELETE FROM jtf_ih_bulk_recovery
455 WHERE request_id = G_CONC_REQUEST_ID;
456
457 -- crazy, but if more than one record is ever deleted here something bad is
458 -- going on - so log the error (and don't commit ?)
459 IF (SQL%ROWCOUNT > 1) THEN
460
461 -- High level logging:The number of records deleted from
462 -- the recovery table during cleanup is not 1 as expected
463 FND_MESSAGE.SET_NAME('JTF', 'JTF_IH_BULK_RECOV_DEL_WARN');
464 FND_MESSAGE.SET_TOKEN('ROW_COUNT', SQL%ROWCOUNT);
465 FND_FILE.PUT_LINE(FND_FILE.LOG, FND_MESSAGE.GET);
466
467 IF( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
468 FND_MESSAGE.SET_NAME('JTF', 'JTF_IH_BULK_RECOV_DEL_WARN');
469 FND_MESSAGE.SET_TOKEN('ROW_COUNT', SQL%ROWCOUNT);
470 FND_LOG.MESSAGE(FND_LOG.LEVEL_UNEXPECTED,
471 'jtf.plsql.JTF_IH_BULK.BULK_PROCESSOR_CONC', TRUE);
472 END IF;
473
474 ELSE
475 --FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Deleted row from recovery table.');
476 COMMIT;
477 END IF;
478
479
480 t2_end := DBMS_UTILITY.GET_TIME;
481
482 --Logging Detail Level
483 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
484 l_fnd_log_msg := 'Cumulative counts - ' ||
485 'Total time taken for this run - ' || (t2_end-t2_beg)*10 ||
486 ', Total number of records processed - ' || num_rec ||
487 ', Total processing time - ' || l_time_cum ||
488 ', Total xml parse time - ' || G_XML_PARSE_TIME_TOTAL ||
489 ', Total interaction processing time - ' || G_INT_PROC_TIME_CUM ||
490 ', Total number of interactions - ' || G_NUM_INT_TOTAL ||
491 ', Total successfull interactions - ' || G_NUM_INT_PROC_TOTAL ||
492 ', Total number of activities - ' || G_NUM_ACT_TOTAL ||
493 ', Total number of media items - ' || G_NUM_MED_TOTAL;
494 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
495 'jtf.plsql.JTF_IH_BULK.BULK_PROCESSOR_CONC', l_fnd_log_msg);
496 END IF;
497
498 --Logging Detail Level
499 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
500 l_fnd_log_msg := 'Maximums and minimums - ' ||
501 'Maximum record processing time - ' || l_time_max ||
502 ', Minimum record processing time - ' || l_time_min ||
503 ', Maximum xml parse time - ' || G_XML_PARSE_TIME_MAX ||
504 ', Minimum xml parse time - ' || G_XML_PARSE_TIME_MIN ||
505 ', Maximum interaction processing time - ' || G_INT_PROC_TIME_MAX ||
506 ', Minimum interaction processing time - ' || G_INT_PROC_TIME_MIN ||
507 ', Maximum number of interactions per request - ' || G_NUM_INT_MAX ||
508 ', Minimum number of interactions per request - ' || G_NUM_INT_MIN ||
509 ', Maximum number of activities per interactions - ' || G_NUM_ACT_MAX ||
510 ', Minimum number of activities per interactions - ' || G_NUM_ACT_MIN ||
511 ', Maximum number of media items per interactions - ' || G_NUM_MED_MAX ||
512 ', Minimum number of media items per interactions - ' || G_NUM_MED_MIN;
513 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
514 'jtf.plsql.JTF_IH_BULK.BULK_PROCESSOR_CONC', l_fnd_log_msg);
515 END IF;
516
517 IF (num_rec > 0) THEN
518 average := l_time_cum/num_rec;
519
520 --Logging Detail Level
521 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
522 l_fnd_log_msg := 'Averages - ' ||
523 'Average record processing time - ' || average;
524 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
525 'jtf.plsql.JTF_IH_BULK.BULK_PROCESSOR_CONC', l_fnd_log_msg);
526 END IF;
527 END IF;
528
529
530 IF (G_NUM_INT_PROC_TOTAL > 0) THEN
531 --Logging Detail Level
532 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
533 l_fnd_log_msg := 'Average interaction processing time - ' ||
534 G_INT_PROC_TIME_CUM/G_NUM_INT_PROC_TOTAL;
535 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
536 'jtf.plsql.JTF_IH_BULK.BULK_PROCESSOR_CONC', l_fnd_log_msg);
537 END IF;
538 END IF;
539
540
541 -- everything ought to have been OK for us to reach this point
542 -- set return values accordingly
543 -- per documentation - The parameter retcode returns 0 for success,
544 -- 1 for success with warnings, and 2 for error.
545 retcode := '0';
546 errbuf := FND_MESSAGE.GET_STRING('JTF', 'JTF_IH_BULK_OK');
547
548 FND_FILE.PUT_LINE(FND_FILE.LOG, errbuf);
549
550 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
551 l_fnd_log_msg := 'PROCESS_BULK_RECORD Out parameters :'||
552 ', errbuf = '|| errbuf ||
553 ', retcode = '|| retcode;
554 --dbms_output.put_line(l_fnd_log_msg);
555 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
556 'jtf.plsql.JTF_IH_BULK.BULK_PROCESSOR_CONC.end', l_fnd_log_msg);
557 END IF;
558
559 EXCEPTION
560 WHEN OTHERS THEN
561
562 DECLARE
563 errm VARCHAR2(2000);
564 BEGIN
565
566 errm := SQLERRM;
567 IF (errm IS NULL) THEN
568 errm := FND_MESSAGE.GET_STRING('JTF', 'JTF_IH_BULK_NOERRM');
569 END IF;
570
571 LOG_EXC_OTHERS('BULK_PROCESSOR_CONC');
572
573 -- error table before we bye-bye
574 /*IF (msg_id IS NOT NULL) THEN
575 NULL;
576 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'payload ought to go into errors ,msg_id = ' || l_msg_id);
577 -- stick message into errors
578 END IF;*/
579
580 retcode := '2';
581
582 FND_MESSAGE.set_name('JTF', 'JTF_IH_BULK_FAIL');
583
584
585 FND_MESSAGE.set_token('ERR_MSG', SQLERRM);
586 errbuf := FND_MSG_PUB.GET();
587 FND_FILE.PUT_LINE(FND_FILE.LOG, errbuf);
588
589 IF( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
590 l_fnd_log_msg := errbuf;
591 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION,
592 'jtf.plsql.JTF_IH_BULK.BULK_PROCESSOR_CONC', l_fnd_log_msg);
593 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION,
594 'jtf.plsql.JTF_IH_BULK.BULK_PROCESSOR_CONC',errbuf);
595 END IF;
596
597 END;
598
599 END BULK_PROCESSOR_CONC;
600
601 --
602 -- Function to process a single bulk record.
603 --
604 -- Description - Processes the input bulk interaction record. The optional
605 -- parameter allows processing to start at some offset besides the
606 -- beginning in the list of interactions.
607 --
608 -- Parameters
609 --
610 -- p_bulk_writer_code IN VARCHAR2 bulk writer code
611 -- p_bulk_batch_type IN VARCHAR2 batch type
612 -- p_bulk_batch_id IN NUMBER batch id
613 -- p_bulk_interaction_request IN CLOB interaction request itself, xml doc
614 -- p_int_offset IN NUMBER optional offset value to not process all
615 -- interactions in the bulk record
616 --
617 --
618 FUNCTION PROCESS_BULK_RECORD
619 (
620 p_bulk_writer_code IN VARCHAR2,
621 p_bulk_batch_type IN VARCHAR2,
622 p_bulk_batch_id IN NUMBER,
623 p_bulk_interaction_request IN CLOB,
624 p_int_offset IN NUMBER DEFAULT 0
625 ) RETURN VARCHAR2 IS
626 xml_p dbms_xmlparser.parser;
627 xml_doc dbms_xmldom.DOMDocument;
628 int_rec JTF_IH_PUB.INTERACTION_REC_TYPE;
629 int_nl dbms_xmldom.DOMNodeList;
630 int_node dbms_xmldom.DOMNode;
631 int_elem dbms_xmldom.DomElement;
632 med_nl dbms_xmldom.DOMNodeList;
633 med_id_tbl media_id_trkr_type;
634 num_int NUMBER;
635 num_med NUMBER;
636 num_act NUMBER;
637 act_nl dbms_xmldom.DOMNodeList;
638 act_tbl JTF_IH_PUB.ACTIVITY_TBL_TYPE;
639
640 -- local variables
641 l_resource_id NUMBER;
642 l_user_id NUMBER;
643 l_bulk_interaction_id NUMBER;
644 l_num_int_done NUMBER;
645 l_commit_int_num NUMBER;
646 l_ret_status varchar2(1);
647 l_msg_count NUMBER;
648 l_msg_data VARCHAR2(2000);
649 l_error VARCHAR2(30);
650 l_err_msg VARCHAR2(2000);
651 l_ret_msg VARCHAR2(2000);
652 t1_beg NUMBER;
653 t1_end NUMBER;
654 l_parse_time NUMBER;
655 l_int_proc_time NUMBER;
656 L_BLANK VARCHAR2(1);
657 L_COMMIT_THRESHOLD NUMBER := 100;
658 l_error_msgLOG VARCHAR2(2000);
659
660 processing_error EXCEPTION;
661 BEGIN
662
663 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
664 l_fnd_log_msg := 'FUNCTION PROCESS_BULK_RECORD In parameters :'||
665 'p_bulk_writer_code = '|| p_bulk_writer_code ||
666 ', p_bulk_batch_type = '|| p_bulk_batch_type ||
667 ', p_bulk_batch_id = '|| p_bulk_batch_id||
668 ', p_bulk_interaction_request = '|| p_bulk_interaction_request ||
669 ', p_int_offset = '|| p_int_offset;
670 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
671 'jtf.plsql.JTF_IH_BULK.PROCESS_BULK_RECORD.begin', l_fnd_log_msg);
672 END IF;
673
674 -- local variables initialization to remove GSCC warnings
675 l_num_int_done := -1;
676 l_commit_int_num := -1;
677 l_ret_msg := '';
678 L_BLANK := '';
679
680 -- optimistic
681 l_ret_status := FND_API.G_RET_STS_SUCCESS;
682
683 g_hrt_beat := 1;
684
685 -- savepoint to roll back any work done for this bulk record
686 SAVEPOINT BULK_RECORD;
687
688 -- get new parser instance
689 xml_p := dbms_xmlparser.newParser();
690
691 -- set parser attributes
692 dbms_xmlparser.setValidationMode(xml_p, FALSE);
693 -- Bug fix 3812373. Commenting out set error log not needed.
694 -- dbms_xmlparser.setErrorLog(xml_p, '/tmp/msistaXmlErrors.txt');
695
696 --FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Calling ParseClob');
697
698 -- parse the bulk record clob
699
700 g_hrt_beat := 1.5;
701 -- Start debug code for bad XML
702 BEGIN
703
704 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
705 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
706 'jtf.plsql.JTF_IH_BULK.PROCESS_BULK_RECORD', 'Calling ParseClob');
707 END IF;
708 dbms_xmlparser.parseClob(xml_p, p_bulk_interaction_request);
709 EXCEPTION
710 WHEN OTHERS THEN
711 DECLARE
712 errm VARCHAR2(2000);
713 BEGIN
714 errm := SQLERRM;
715 IF (errm IS NULL) THEN
716 errm := 'No Error Message in SQLERRM for parsing errors';
717 END IF;
718 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Error Message: ' || errm );
719 IF( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
720 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION,
721 'jtf.plsql.JTF_IH_BULK.PROCESS_BULK_RECORD',errm);
722 END IF;
723 l_ret_status := FND_API.G_RET_STS_ERROR;
724 END;
725 END;
726
727 --IF (g_stats_enabled) THEN
728 t1_beg := DBMS_UTILITY.GET_TIME;
729 --END IF;
730
731
732 --IF (g_stats_enabled) THEN
733 t1_end := DBMS_UTILITY.GET_TIME;
734 --FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Parse returned.');
735 l_parse_time := ((t1_end-t1_beg)*10);
736
737 g_hrt_beat := 1.6;
738 --FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Time taken for Parse - ' || l_parse_time);
739
740 -- globally tracked parse time
741 G_XML_PARSE_TIME_TOTAL := G_XML_PARSE_TIME_TOTAL + l_parse_time;
742 IF (l_parse_time < G_XML_PARSE_TIME_MIN) THEN
743 G_XML_PARSE_TIME_MIN := l_parse_time;
744 END IF;
745 IF (l_parse_time > G_XML_PARSE_TIME_MAX) THEN
746 G_XML_PARSE_TIME_MAX := l_parse_time;
747 END IF;
748 --END IF;
749
750 -- the parse call above would have build the xml document in memory.
751 -- the job now is to get the individual elements out of the document and
752 -- process them with the regular IH public API.
753 g_hrt_beat := 1.7;
754 xml_doc := dbms_xmlparser.getDocument(xml_p);
755
756 g_hrt_beat := 2;
757
758 -- To do -
759 --
760 -- a. get all interaction nodes
761 -- b. for each interaction node -
762 /* 1. convert interaction node to a document
763 i. get all Media nodes from this doc.
764 ii.for each media node
765 a. get childNodes (they would be MLCS)
766 b. for each mlcs node, create a mlcs record and accumulate
767 c. get attributes of media node
768 d. if MEDIA_ID value is not set for the media item,
769 call JTF_IH_PUB.Create_MediaItem with the attributes and mlcs
770 e. if MEDIA_ID is already set, call the Add_MediaLifeCycle and
771 Close_MediaItem methods
772 f. keep track of mediaitem_identifier to media_id values
773 iii.get all Activity nodes from this doc now.
774 iv.for each activity node
775 a.gather each activity's attrs, incl media_id using the mapping from
776 b.1.ii.e
777 b.create a Activity record and save
778 2. gather all interaction attributes
779 3. call JTF_IH_PUB.Create_Interaction using attr and activity records
780 4. release the interaction document resource.
781 c. any errors in recording an interatn will result in logging the interactn
782 to the error table.
783 d. clean up and return.
784 */
785
786 -- a. get all interaction nodes
787 int_nl := dbms_xmldom.getElementsByTagName(xml_doc, 'INTERACTION');
788 num_int := dbms_xmldom.getLength(int_nl);
789
790 g_hrt_beat := 3;
791 --FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '# of interactions in req = ' || num_int);
792
793 -- keep track of interactions processed for use in logging
794 l_num_int_done := 0;
795
796 -- b. for each interaction node -
797 /* 1. convert interaction node to a document
798 i. get all Media nodes from this doc.
799 ...
800 iii.get all Activity nodes from this doc now.
801 ...
802 2. gather all interaction attributes
803 3. call JTF_IH_PUB.Create_Interaction using attr and activity records
804 */
805 FOR i IN p_int_offset..num_int-1 LOOP
806 l_error_msgLOG := '';
807 BEGIN -- dummy block
808
809 g_hrt_beat := 3.4;
810
811 SAVEPOINT BEGIN_INTERACTION;
812
813 g_hrt_beat := 3.41;
814
815 IF (g_stats_enabled) THEN
816 t1_beg := DBMS_UTILITY.GET_TIME;
817 END IF;
818
819 g_hrt_beat := 3.42;
820
821 -- blank out any previous errors
822 l_error := L_BLANK;
823 l_ret_msg := '';
824
825 --dbms_output.new_line();
826 --FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '---------------');
827 --FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'begin INT loop iteration # - ' || i);
828
829 g_hrt_beat := 3.5;
830
831 int_node := dbms_xmldom.item(int_nl, i);
832
833 g_hrt_beat := 4;
834
835 --1. convert interaction node to a document as well as a doc element
836 int_elem := dbms_xmldom.makeElement(int_node);
837 l_bulk_interaction_id := dbms_xmldom.getAttribute(int_elem, 'bulk_interaction_id');
838
839 -- extract the resource id from the interaction element and get its user_id
840 -- we will use the resource's user id for all ih entities of this interaction
841 l_resource_id := dbms_xmldom.getAttribute(int_elem, 'resource_id');
842
843 BEGIN
844 SELECT user_id INTO l_user_id
845 FROM jtf_rs_resource_extns
846 WHERE resource_id = l_resource_id AND
847 ( end_date_active IS NULL OR
848 TRUNC(end_date_active) >= TRUNC(SYSDATE) );
849
850 -- if the resource_id is not valid, don't attempt logging this interaction
851 EXCEPTION
852 WHEN NO_DATA_FOUND THEN
853 FND_MESSAGE.SET_NAME('JTF', 'JTF_IH_INVALID_RESOURCE_ID');
854 FND_MESSAGE.SET_TOKEN('RESOURCE_ID', l_resource_id);
855 l_error_msgLOG := FND_MESSAGE.GET;
856
857 --Logging Detail Level
858 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
859 l_fnd_log_msg := l_error_msgLOG;
860 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
861 'jtf.plsql.JTF_IH_BULK.PROCESS_BULK_RECORD', l_fnd_log_msg);
862 END IF;
863 RAISE processing_error;
864 END;
865
866
867 --i. get all Media nodes from this doc.
868 med_nl := dbms_xmldom.getChildrenByTagName(int_elem, 'MEDIAITEM');
869 num_med := dbms_xmldom.getLength(med_nl);
870
871 --FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '# of media_items in int req num ' || i || ' = ' || num_med);
872
873 --FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'BULK_INTERACTION_ID = ' || l_bulk_interaction_id);
874
875 g_hrt_beat := 5;
876
877 -- delegate media node processing
878 IF (num_med > 0) THEN
879 PROCESS_MEDIA_ITEMS(med_nl, l_bulk_interaction_id, p_bulk_writer_code,
880 p_bulk_batch_type, p_bulk_batch_id, l_user_id, med_id_tbl,
881 l_ret_status, l_ret_msg);
882 IF (l_ret_status <> FND_API.G_RET_STS_SUCCESS) THEN
883 l_error := 'JTF_IH_BULK_MEDIA_FAIL';
884 --FND_FILE.PUT_LINE(FND_FILE.OUTPUT, l_error || ', ' || l_ret_msg);
885 l_error_msgLOG := l_ret_msg;
886 RAISE processing_error;
887 -- raise exception or otherwise handle this
888 ELSE
889 NULL;
890 --FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'PROCESS_MEDIA_ITEM returned OK');
891 END IF;
892 END IF;
893
894 g_hrt_beat := 6;
895
896 -- Media is done, now the activity records
897 -- iii.get all Activity nodes from this doc now.
898 -- iv. for each activity node
899 -- a.gather each activity's attributes, including media_id using the
900 -- mapping from b.1.ii.e
901 -- b.create a Activity record and save
902 act_nl := dbms_xmldom.getChildrenByTagName(int_elem, 'ACTIVITY');
903 num_act := dbms_xmldom.getLength(act_nl);
904
905 g_hrt_beat := 7;
906
907 -- delegate the activity processing
908 IF (num_act > 0) THEN
909 GATHER_ACT_TBL(act_nl, l_bulk_interaction_id, p_bulk_writer_code,
910 p_bulk_batch_type, p_bulk_batch_id, med_id_tbl,
911 act_tbl, l_ret_status, l_ret_msg);
912
913 IF (l_ret_status <> FND_API.G_RET_STS_SUCCESS) THEN
914 l_error := 'JTF_IH_BULK_ACT_FAIL';
915
916 --Logging Detail Level
917 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
918 FND_MESSAGE.SET_NAME('JTF', 'JTF_IH_BULK_ACT_FAIL');
919 FND_LOG.MESSAGE(FND_LOG.LEVEL_STATEMENT,
920 'jtf.plsql.JTF_IH_BULK.PROCESS_BULK_RECORD', TRUE);
921 END IF;
922
923 l_error_msgLOG := l_ret_msg;
924 RAISE processing_error;
925 END IF;
926 END IF;
927
928 g_hrt_beat := 8;
929
930 -- media nodes are done and we have gathered activities,
931 -- if there were no errors, we would be here so process the interaction now
932 --2. gather all interaction attributes
933 --3. call JTF_IH_PUB.Create_Interaction using attr and activity records
934 GATHER_INT_ATTR(int_elem, l_bulk_interaction_id, p_bulk_writer_code,
935 p_bulk_batch_type, p_bulk_batch_id, int_rec,
936 l_ret_status, l_ret_msg);
937
938 IF (l_ret_status <> FND_API.G_RET_STS_SUCCESS) THEN
939 l_error := 'JTF_IH_BULK_ACT_FAIL';
940 FND_MESSAGE.SET_NAME('JTF', 'JTF_IH_BULK_INT_FAIL');
941 l_ret_msg := FND_MESSAGE.GET ||' '|| l_ret_msg;
942 --FND_FILE.PUT_LINE(FND_FILE.LOG, l_ret_msg);
943 l_error_msgLOG := l_ret_msg;
944 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
945 l_fnd_log_msg := l_ret_msg;
946 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
947 'jtf.plsql.JTF_IH_BULK.PROCESS_BULK_RECORD', l_fnd_log_msg);
948 END IF;
949 RAISE processing_error;
950 END IF;
951
952
953 -- the above routine only does a bunch of dbms_xmldom.getAttribute() calls to
954 -- gather all attributes of the interaction; as such there is no error
955 -- catching or processing in the routine.
956 --
957 -- if and when it changes there will need to be some code here to check
958 -- and log any errors.
959
960 g_hrt_beat := 9;
961
962 JTF_IH_PUB.Create_Interaction(p_api_version => 1.0,
963 p_init_msg_list => FND_API.G_TRUE,
964 p_commit => FND_API.G_FALSE,
965 p_user_id => l_user_id,
966 x_return_status => l_ret_status,
967 x_msg_count => l_msg_count,
968 x_msg_data => l_msg_data,
969 p_interaction_rec => int_rec,
970 p_activities => act_tbl);
971
972 --FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Create_Interaction returned - ' || l_ret_status);
973 IF (l_ret_status <> FND_API.G_RET_STS_SUCCESS) THEN
974 l_error := 'JTF_IH_BULK_INT_FAIL';
975 --FND_FILE.PUT_LINE(FND_FILE.OUTPUT, l_error || ', msg_count = ' || l_msg_count);
976
977 --Logging Detail Level
978 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
979 FND_MESSAGE.SET_NAME('JTF', 'JTF_IH_BULK_INT_FAIL2');
980 FND_MESSAGE.SET_TOKEN('RTN_NAME', 'Create_Interaction');
981 FND_MESSAGE.SET_TOKEN('MSG_COUNT', l_msg_count);
982 FND_LOG.MESSAGE(FND_LOG.LEVEL_STATEMENT,
983 'jtf.plsql.JTF_IH_BULK.PROCESS_BULK_RECORD', TRUE);
984 END IF;
985
986 FOR i in 1..l_msg_count LOOP
987 l_msg_data := FND_MSG_PUB.Get(i, 'F');
988 FND_MESSAGE.SET_NAME('JTF', 'JTF_IH_BULK_ERROR_MSG');
989 FND_MESSAGE.SET_TOKEN('MSG_NUM', i);
990 FND_MESSAGE.SET_TOKEN('MSG_TXT', l_msg_data);
991 l_error_msgLOG := l_error_msgLOG ||' '|| FND_MESSAGE.GET;
992 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
993 l_fnd_log_msg := l_error_msgLOG;
994 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
995 'jtf.plsql.JTF_IH_BULK.PROCESS_BULK_RECORD', l_error_msgLOG);
996 END IF;
997
998 --FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Message number - ' || i || ', is - ' || l_msg_data);
999 l_ret_msg := l_ret_msg ||
1000 SUBSTRB(l_msg_data, 0, (2000-LENGTHB(l_ret_msg))) || ' ';
1001 END LOOP;
1002
1003 RAISE processing_error;
1004 END IF;
1005
1006 g_hrt_beat := 10;
1007
1008 -- commit interactions if necessary
1009 IF ( MOD(i,L_COMMIT_THRESHOLD) = 0) THEN
1010
1011 l_commit_int_num := i;
1012
1013 -- update the crash recovery table too
1014 UPDATE jtf_ih_bulk_recovery
1015 SET num_int_processed = l_commit_int_num,
1016 last_update_date = sysdate,
1017 program_update_date = sysdate
1018 WHERE request_id = G_CONC_REQUEST_ID;
1019
1020 COMMIT WORK;
1021
1022 END IF;
1023 -- increment number of interactions processed
1024 l_num_int_done := l_num_int_done+1;
1025
1026 --IF (g_stats_enabled) THEN
1027 t1_end := DBMS_UTILITY.GET_TIME;
1028
1029 l_int_proc_time := (t1_end - t1_beg)*10;
1030
1031 -- this is being done here so that interactions without errors are the
1032 -- only ones tallied for these times
1033 G_INT_PROC_TIME_CUM := G_INT_PROC_TIME_CUM + l_int_proc_time;
1034
1035 IF (l_int_proc_time < G_INT_PROC_TIME_MIN) THEN
1036 G_INT_PROC_TIME_MIN := l_int_proc_time;
1037 END IF;
1038
1039 IF (l_int_proc_time > G_INT_PROC_TIME_MAX) THEN
1040 G_INT_PROC_TIME_MAX := l_int_proc_time;
1041 END IF;
1042
1043 -- activity tallying
1044 G_NUM_ACT_TOTAL := G_NUM_ACT_TOTAL + num_act;
1045
1046 IF (num_act > G_NUM_ACT_MAX ) THEN
1047 G_NUM_ACT_MAX := num_act;
1048 END IF;
1049
1050 IF (num_act < G_NUM_ACT_MIN ) THEN
1051 G_NUM_ACT_MIN := num_act;
1052 END IF;
1053
1054 -- media items tallying
1055 G_NUM_MED_TOTAL := G_NUM_MED_TOTAL + num_act;
1056
1057 IF (num_act > G_NUM_MED_MAX ) THEN
1058 G_NUM_MED_MAX := num_act;
1059 END IF;
1060
1061 IF (num_act < G_NUM_MED_MIN ) THEN
1062 G_NUM_MED_MIN := num_act;
1063 END IF;
1064 --END IF;
1065
1066 -- exception block to catch errors, practice is to log the current int. in
1067 -- the bulk errors table and carry on. unknown errors will use SQLERRM for
1068 -- error message
1069 EXCEPTION
1070 -- the idea is to carry on with the other remaining interactions in the
1071 -- bulk request.
1072 WHEN processing_error THEN
1073
1074 IF( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1075 FND_MESSAGE.SET_NAME('JTF', 'JTF_IH_BULK_INT_NODE_ERR');
1076 FND_LOG.MESSAGE(FND_LOG.LEVEL_EXCEPTION,
1077 'jtf.plsql.JTF_IH_BULK.PROCESS_BULK_RECORD', TRUE);
1078 END IF;
1079
1080
1081 g_hrt_beat := 11.5;
1082
1083 -- rollback all work done for this interaction
1084 ROLLBACK TO SAVEPOINT BEGIN_INTERACTION;
1085
1086 g_hrt_beat := 11.6;
1087
1088 IF (l_error = L_BLANK) THEN
1089 g_hrt_beat := 11.64;
1090 l_error_msgLOG := SQLERRM;
1091 ELSE
1092 g_hrt_beat := 11.61;
1093 l_err_msg := FND_MESSAGE.GET_STRING('JTF', l_error);
1094 END IF;
1095
1096 g_hrt_beat := 11.65;
1097
1098 l_error_msgLOG := l_err_msg ||' '||l_error_msgLOG;
1099 l_ret_status := LOG_BULK_ERROR(int_node, p_bulk_writer_code,
1100 p_bulk_batch_type, p_bulk_batch_id,
1101 l_bulk_interaction_id,
1102 --l_err_msg, l_ret_msg);
1103 l_error_msgLOG, l_ret_msg);
1104 --FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'LOG_BULK_ERROR call done');
1105 g_hrt_beat := 11.7;
1106
1107 END; -- dummy block
1108
1109 g_hrt_beat := 11.8;
1110 --FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'end INT loop iteration # - ' || i);
1111
1112 END LOOP; -- INT nodes
1113
1114 g_hrt_beat := 12;
1115 dbms_xmldom.freeDocument(xml_doc);
1116
1117 g_hrt_beat := 13;
1118 dbms_xmlparser.freeParser(xml_p);
1119
1120 g_hrt_beat := 14;
1121
1122 -- commit any outstanding work.
1123 -- update the crash recovery table too - here the num int processed is diff
1124 -- from above
1125 UPDATE jtf_ih_bulk_recovery
1126 SET num_int_processed = l_num_int_done,
1127 last_update_date = sysdate,
1128 program_update_date = sysdate
1129 WHERE request_id = G_CONC_REQUEST_ID;
1130 COMMIT WORK;
1131
1132 -- the first two totals are gathered irrespective of the stats flag because
1133 -- they are output to the log file as summary information
1134 --
1135 -- gather interaction statistics
1136 G_NUM_INT_PROC_TOTAL := G_NUM_INT_PROC_TOTAL + l_num_int_done;
1137 G_NUM_INT_TOTAL := G_NUM_INT_TOTAL + NUM_INT;
1138
1139 --IF (g_stats_enabled) THEN
1140 IF (num_int > G_NUM_INT_MAX) THEN
1141 G_NUM_INT_MAX := num_int;
1142 END IF;
1143
1144 IF (num_int < G_NUM_INT_MIN) THEN
1145 G_NUM_INT_MIN := num_int;
1146 END IF;
1147 --END IF;
1148
1149 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1150 l_fnd_log_msg := 'FUNCTION PROCESS_BULK_RECORD return variable :'||
1151 'l_ret_status = '|| l_ret_status;
1152 --dbms_output.put_line(l_fnd_log_msg);
1153 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
1154 'jtf.plsql.JTF_IH_BULK.PROCESS_BULK_RECORD.end', l_fnd_log_msg);
1155 END IF;
1156
1157
1158 RETURN l_ret_status;
1159
1160 EXCEPTION
1161 WHEN OTHERS THEN
1162 DECLARE
1163 error NUMBER := SQLCODE;
1164 -- Bug fix 3812373. Increasing the errm size from 300 to 1000
1165 errm varchar2(1000) := SQLERRM;
1166 errbuf varchar2(1000);
1167 BEGIN
1168 NULL;
1169 /*
1170 debug message - uncomment if necessary.
1171
1172 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Exception occurred in PROCESS_BULK_RECORD');
1173 IF (error IS NOT NULL) THEN
1174 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Error code is - ' || error);
1175 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Error msg - ' || errm);
1176 ELSE
1177 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'No error code');
1178 END IF;
1179
1180 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Hrt beat = ' || g_hrt_beat);
1181 */
1182
1183
1184 FND_FILE.PUT_LINE(FND_FILE.LOG, errm);
1185
1186 FND_MESSAGE.SET_NAME('JTF', 'JTF_IH_BULK_REQ_FAIL2');
1187 FND_MESSAGE.SET_TOKEN('NUM_INT_DONE', l_num_int_done);
1188 FND_MESSAGE.SET_TOKEN('NUM_INT_COMMIT', l_commit_int_num);
1189
1190 FND_FILE.PUT_LINE(FND_FILE.LOG, FND_MESSAGE.GET);
1191
1192 IF( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1193 FND_MESSAGE.SET_NAME('JTF', 'JTF_IH_BULK_REQ_FAIL2');
1194 FND_MESSAGE.SET_TOKEN('NUM_INT_DONE', l_num_int_done);
1195 FND_MESSAGE.SET_TOKEN('NUM_INT_COMMIT', l_commit_int_num);
1196 FND_LOG.MESSAGE(FND_LOG.LEVEL_EXCEPTION,
1197 'jtf.plsql.JTF_IH_BULK.PROCESS_BULK_RECORD', TRUE);
1198 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION,
1199 'jtf.plsql.JTF_IH_BULK.PROCESS_BULK_RECORD',errm);
1200 END IF;
1201
1202 -- rollback work in the current bulk record
1203 ROLLBACK TO SAVEPOINT BULK_RECORD;
1204
1205 -- in case of this exception, shunt the entire bulk request to error tbl
1206 l_ret_status := LOG_BULK_ERROR( p_bulk_writer_code,
1207 p_bulk_batch_type,
1208 p_bulk_batch_id,
1209 l_bulk_interaction_id,
1210 p_bulk_interaction_request,
1211 FND_MESSAGE.GET_STRING('JTF','JTF_IH_BULK_REQ_FAIL'),
1212 errm);
1213 l_ret_status := FND_API.G_RET_STS_ERROR;
1214 END;
1215
1216 -- an exception is always unexpected
1217 RETURN FND_API.G_RET_STS_UNEXP_ERROR;
1218 END PROCESS_BULK_RECORD;
1219
1220 --
1221 -- Utility function to handle media nodes for an interaction.
1222 --
1223 -- Parameters
1224 --
1225 -- med_nl - IN - dbms_xmldom.DomNodeList of media nodes
1226 -- p_bulk_interaction_id - IN - self explanatory
1227 -- bulk_writer_code - IN - self explanatory
1228 -- bulk_batch_type - IN - self explanatory
1229 -- bulk_batch_id - IN - self explanatory
1230 -- p_user_id - IN - user id of the user submitting the request
1231 -- x_med_id_tbl - OUT - this carries the media_identifier to media_id relation
1232 -- x_ret_status - OUT - self explanatory
1233 -- x_ret_msg - OUT - self explanatory
1234 --
1235 PROCEDURE PROCESS_MEDIA_ITEMS
1236 (
1237 med_nl IN dbms_xmldom.DomNodeList,
1238 p_bulk_interaction_id IN NUMBER,
1239 p_bulk_writer_code IN VARCHAR2,
1240 p_bulk_batch_type IN VARCHAR2,
1241 p_bulk_batch_id IN NUMBER,
1242 p_user_id IN NUMBER,
1243 x_med_id_tbl IN OUT NOCOPY media_id_trkr_type,
1244 x_ret_status IN OUT NOCOPY VARCHAR2,
1245 x_ret_msg IN OUT NOCOPY VARCHAR2
1246 ) IS
1247
1248 med_ident NUMBER;
1249 med_node dbms_xmldom.DOMNode;
1250 med_elem dbms_xmldom.DOMElement;
1251 mlcs_nl dbms_xmldom.DOMNodeList;
1252 mlcs_node dbms_xmldom.DOMNode;
1253 mlcs_elem dbms_xmldom.DOMElement;
1254 num_mlcs number;
1255 med_rec JTF_IH_PUB.media_rec_type;
1256 mlcs_rec JTF_IH_PUB.media_lc_rec_type;
1257 mlcs_tbl JTF_IH_PUB.mlcs_tbl_type;
1258 med_id number;
1259 l_milcs_id number;
1260
1261 date_str VARCHAR2(50);
1262 num_med NUMBER;
1263 l_media_id_given BOOLEAN;
1264 l_ret_status VARCHAR2(1);
1265 l_rtn_name VARCHAR2(30);
1266 l_msg_count NUMBER;
1267 l_msg_data VARCHAR2(2000);
1268 t1_beg NUMBER;
1269 t1_end NUMBER;
1270 l_err_media VARCHAR2(2000);
1271
1272 media_exception EXCEPTION;
1273 BEGIN
1274
1275 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1276 l_fnd_log_msg := 'PROCESS_MEDIA_ITEMS In parameters :'||
1277 'med_nl TYPE dbms_xmldom.DomNodeList'||
1278 ', p_bulk_interaction_id = '|| p_bulk_interaction_id ||
1279 ', p_bulk_writer_code = '|| p_bulk_writer_code||
1280 ', p_bulk_batch_type = '|| p_bulk_batch_type ||
1281 ', p_bulk_batch_id = '|| p_bulk_batch_id ||
1282 ', p_user_id = '|| p_user_id ||
1283 ', x_med_id_tbl TYPE media_id_trkr_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER' ||
1284 ', x_ret_status = '|| x_ret_status ||
1285 ', x_ret_msg = '|| x_ret_msg;
1286 --dbms_output.put_line(l_fnd_log_msg);
1287 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
1288 'jtf.plsql.JTF_IH_BULK.PROCESS_MEDIA_ITEMS.begin', l_fnd_log_msg);
1289 END IF;
1290 -- to do
1291 /*
1292 ii.for each media node
1293 a. get childNodes (they would be MLCS)
1294 b. for each mlcs node, create a mlcs record and accumulate
1295 c. get attributes of media node
1296 d. if MEDIA_ID value is not set for the media item,
1297 call JTF_IH_PUB.Create_MediaItem with the attributes and mlcs
1298 e. if MEDIA_ID is already set, call the Add_MediaLifeCycle and
1299 Close_MediaItem methods
1300 f. keep track of mediaitem_identifier to media_id values
1301 */
1302
1303 l_ret_status := FND_API.G_RET_STS_SUCCESS;
1304 num_med := dbms_xmldom.getLength(med_nl);
1305
1306 g_hrt_beat := 13.0;
1307
1308 --ii.for each media node
1309 FOR j IN 0..num_med-1 LOOP
1310 med_node := dbms_xmldom.item(med_nl, j);
1311
1312 -- an element is easier to get attribute values from
1313 med_elem := dbms_xmldom.makeElement(med_node);
1314
1315 -- get the media identifier
1316 med_ident := dbms_xmldom.getAttribute(med_elem, 'mediaitem_identifier');
1317
1318 -- we need to note whether we were passed a media id or not
1319 -- generate a media_id first - if it is not passed to us (AO gives us the media_id)
1320 med_id := dbms_xmldom.getAttribute(med_elem, 'media_id');
1321 IF (med_id IS null) THEN
1322 g_hrt_beat := 13.11;
1323 l_media_id_given := FALSE;
1324 --dbms_output.PUT('media_id is null for media_ident ' || med_ident);
1325 SELECT jtf_ih_media_items_s1.NEXTVAL INTO med_id FROM sys.dual;
1326 --FND_FILE.PUT_LINE(FND_FILE.OUTPUT, ' obtained from db med_id = ' || med_id);
1327 ELSE
1328 g_hrt_beat := 13.12;
1329 l_media_id_given := TRUE;
1330 --FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'media_id is NOT null for media_ident ' || med_ident || ', med_id = ' || med_id);
1331 END IF;
1332
1333 --a. get childNodes (they would be MLCS)
1334 mlcs_nl := dbms_xmldom.getChildNodes(med_node);
1335 num_mlcs := dbms_xmldom.getLength(mlcs_nl);
1336
1337 g_hrt_beat := 13.2;
1338
1339 --b. for each mlcs node, create a mlcs record and accumulate
1340 FOR k IN 1..num_mlcs LOOP
1341 mlcs_node := dbms_xmldom.item(mlcs_nl, (k-1)); -- indices run from 0 here
1342
1343 -- each mlcs node has only mlcs attributes, gather them and create an mlcs record
1344 -- elements are easier to get attribute values out of.
1345 mlcs_elem := dbms_xmldom.makeElement(mlcs_node);
1346
1347 -- get all attributes of each mlcs and accumulate in the collection
1348 date_str := dbms_xmldom.getAttribute(mlcs_elem, 'start_date_time');
1349 -- RDD - Bug 5330922 - Removed the call to convert GMT input to server date - Input now in Server Date
1350 mlcs_tbl(k).start_date_time := TO_DATE(date_str, G_DATE_FORMAT);
1351 date_str := dbms_xmldom.getAttribute(mlcs_elem, 'end_date_time');
1352 -- RDD - Bug 5330922 - Removed the call to convert GMT input to server date - Input now in Server Date
1353 mlcs_tbl(k).end_date_time := TO_DATE(date_str, G_DATE_FORMAT);
1354
1355 mlcs_tbl(k).type_type := dbms_xmldom.getAttribute(mlcs_elem, 'type_type');
1356 mlcs_tbl(k).type_id := dbms_xmldom.getAttribute(mlcs_elem, 'type_id');
1357 mlcs_tbl(k).duration := dbms_xmldom.getAttribute(mlcs_elem, 'duration');
1358 mlcs_tbl(k).milcs_type_id := dbms_xmldom.getAttribute(mlcs_elem, 'milcs_type_id');
1359 mlcs_tbl(k).media_id := med_id;
1360 mlcs_tbl(k).handler_id := dbms_xmldom.getAttribute(mlcs_elem, 'handler_id');
1361 mlcs_tbl(k).resource_id := dbms_xmldom.getAttribute(mlcs_elem, 'resource_id');
1362 mlcs_tbl(k).milcs_code := dbms_xmldom.getAttribute(mlcs_elem, 'milcs_code');
1363
1364 -- bulk attributes
1365 mlcs_tbl(k).bulk_writer_code := p_bulk_writer_code;
1366 mlcs_tbl(k).bulk_batch_type := p_bulk_batch_type;
1367 mlcs_tbl(k).bulk_batch_id := p_bulk_batch_id;
1368 mlcs_tbl(k).bulk_interaction_id := p_bulk_interaction_id;
1369
1370 /*Is useful in figuring out which attribute is bad, if necessary
1371 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'mlcs_tbl(k).start_date_time = ' || mlcs_tbl(k).start_date_time);
1372 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'mlcs_tbl(k).end_date_time = ' || mlcs_tbl(k).end_date_time);
1373 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'mlcs_tbl(k).type_type = ' || mlcs_tbl(k).type_type);
1374 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'mlcs_tbl(k).type_id = ' || mlcs_tbl(k).type_id);
1375 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'mlcs_tbl(k).duration = ' || mlcs_tbl(k).duration);
1376 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'mlcs_tbl(k).milcs_type_id = ' || mlcs_tbl(k).milcs_type_id);
1377 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'mlcs_tbl(k).media_id = ' || med_id);
1378 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'mlcs_tbl(k).handler_id = ' || mlcs_tbl(k).handler_id);
1379 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'mlcs_tbl(k).resource_id = ' || mlcs_tbl(k).resource_id);
1380 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'mlcs_tbl(k).milcs_code = ' || mlcs_tbl(k).milcs_code);
1381 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, ' ');
1382 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '-- bulk attributes');
1383 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'mlcs_tbl(k).bulk_writer_code = ' || mlcs_tbl(k).bulk_writer_code);
1384 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'mlcs_tbl(k).bulk_batch_type = ' || mlcs_tbl(k).bulk_batch_type);
1385 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'mlcs_tbl(k).bulk_batch_id = ' || mlcs_tbl(k).bulk_batch_id);
1386 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'mlcs_tbl(k).bulk_interaction_id = ' || mlcs_tbl(k).bulk_interaction_id);
1387 */
1388
1389 END LOOP; -- MLCS nodes
1390
1391 g_hrt_beat := 13.3;
1392
1393 --c. get attributes of media node
1394 date_str := dbms_xmldom.getAttribute(med_elem, 'start_date_time');
1395 -- RDD - Bug 5330922 - Removed the call to convert GMT input to server date - Input now in Server Date
1396 med_rec.start_date_time := TO_DATE(date_str, G_DATE_FORMAT);
1397 date_str := dbms_xmldom.getAttribute(med_elem, 'end_date_time');
1398 -- RDD - Bug 5330922 - Removed the call to convert GMT input to server date - Input now in Server Date
1399 med_rec.end_date_time := TO_DATE(date_str, G_DATE_FORMAT);
1400 med_rec.media_id := med_id;
1401 med_rec.source_id := dbms_xmldom.getAttribute(med_elem, 'source_id');
1402 med_rec.direction := dbms_xmldom.getAttribute(med_elem, 'direction');
1403 med_rec.duration := dbms_xmldom.getAttribute(med_elem, 'duration');
1404 med_rec.interaction_performed := dbms_xmldom.getAttribute(med_elem, 'interaction_performed');
1405 med_rec.media_data := dbms_xmldom.getAttribute(med_elem, 'media_data');
1406 date_str := dbms_xmldom.getAttribute(med_elem, 'source_item_create_date_time');
1407 -- RDD - Bug 5330922 - Removed the call to convert GMT input to server date - Input now in Server Date
1408 med_rec.source_item_create_date_time := TO_DATE(date_str, G_DATE_FORMAT);
1409 med_rec.source_item_id := dbms_xmldom.getAttribute(med_elem, 'source_item_id');
1410 med_rec.media_item_type := dbms_xmldom.getAttribute(med_elem, 'media_item_type');
1411 med_rec.media_item_ref := dbms_xmldom.getAttribute(med_elem, 'media_item_ref');
1412 med_rec.media_abandon_flag := dbms_xmldom.getAttribute(med_elem, 'media_abandon_flag');
1413 med_rec.media_transferred_flag := dbms_xmldom.getAttribute(med_elem, 'media_transferred_flag');
1414 med_rec.server_group_id := dbms_xmldom.getAttribute(med_elem, 'server_group_id');
1415 med_rec.dnis := dbms_xmldom.getAttribute(med_elem, 'dnis');
1416 med_rec.ani := dbms_xmldom.getAttribute(med_elem, 'ani');
1417 med_rec.classification := dbms_xmldom.getAttribute(med_elem, 'classification');
1418 med_rec.address := dbms_xmldom.getAttribute(med_elem, 'address');
1419
1420 -- bulk attributes
1421 med_rec.bulk_writer_code := p_bulk_writer_code;
1422 med_rec.bulk_batch_type := p_bulk_batch_type;
1423 med_rec.bulk_batch_id := p_bulk_batch_id;
1424 med_rec.bulk_interaction_id := p_bulk_interaction_id;
1425
1426 /* Is useful figuring out which attribute is bad if necessary
1427 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'med_rec.start_date_time = ' || med_rec.start_date_time);
1428 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'med_rec.end_date_time = ' || med_rec.end_date_time);
1429 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'med_rec.media_id = ' || med_rec.media_id);
1430 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'med_rec.source_id = ' || med_rec.source_id);
1431 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'med_rec.direction = ' || med_rec.direction);
1432 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'med_rec.duration = ' || med_rec.duration);
1433 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'med_rec.interaction_performed = ' || med_rec.interaction_performed);
1434 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'med_rec.media_data = ' || med_rec.media_data );
1435 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'med_rec.source_item_create_date_time = ' || med_rec.source_item_create_date_time);
1436 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'med_rec.source_item_id = ' || med_rec.source_item_id);
1437 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'med_rec.media_item_type = ' || med_rec.media_item_type);
1438 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'med_rec.media_item_ref = ' || med_rec.media_item_ref );
1439 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'med_rec.media_abandon_flag = ' || med_rec.media_abandon_flag);
1440 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'med_rec.media_transferred_flag = ' || med_rec.media_transferred_flag);
1441 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'med_rec.server_group_id = ' || med_rec.server_group_id );
1442 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'med_rec.dnis = ' || med_rec.dnis);
1443 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'med_rec.ani = ' || med_rec.ani);
1444 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'med_rec.classification = ' || med_rec.classification);
1445 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'med_rec.address = ' || med_rec.address);
1446 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, ' ');
1447 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '-- bulk attributes');
1448 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'med_rec.bulk_writer_code = ' || med_rec.bulk_writer_code);
1449 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'med_rec.bulk_batch_type = ' || med_rec.bulk_batch_type);
1450 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'med_rec.bulk_batch_id = ' || med_rec.bulk_batch_id);
1451 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'med_rec.bulk_interaction_id = ' || med_rec.bulk_interaction_id);
1452 */
1453
1454 g_hrt_beat := 13.4;
1455
1456 -- we have all attributes and any mlcs records of one media item, create it
1457
1458 --d. if MEDIA_ID value is not set for the media item,
1459 -- call JTF_IH_PUB.Create_MediaItem with the attributes and mlcs
1460 IF (l_media_id_given = FALSE) THEN
1461 JTF_IH_PUB.create_mediaitem(p_api_version => 1.0,
1462 p_init_msg_list => FND_API.G_TRUE,
1463 p_commit => FND_API.G_FALSE,
1464 p_user_id => p_user_id,
1465 x_return_status => l_ret_status,
1466 x_msg_count => l_msg_count,
1467 x_msg_data => l_msg_data,
1468 p_media => med_rec,
1469 p_mlcs => mlcs_tbl);
1470
1471 -- The dbms_output will need to be replaced by proper logging for error
1472 -- messages.
1473 --FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Create_MediaItem returned - ' || l_ret_status);
1474 IF (l_ret_status <> FND_API.G_RET_STS_SUCCESS) THEN
1475 l_rtn_name := 'Create_MediaItem';
1476 /*FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Create_MediaItem failed' ||
1477 ', msg_count = ' || l_msg_count ||
1478 ', msg_data = ' || l_msg_data);*/
1479 RAISE media_exception;
1480 END IF;
1481 --e. if MEDIA_ID is already set, call the Add_MediaLifeCycle and
1482 -- Close_MediaItem methods
1483 ELSE -- IF (l_media_id_given = TRUE)
1484 g_hrt_beat := 13.5;
1485 -- loop through all mlcs records and create them
1486 FOR l IN 1..mlcs_tbl.COUNT LOOP
1487 JTF_IH_PUB.Add_MediaLifecycle( p_api_version => 1.0,
1488 p_init_msg_list => FND_API.G_TRUE,
1489 p_commit => FND_API.G_FALSE,
1490 p_user_id => p_user_id,
1491 x_return_status => l_ret_status,
1492 x_msg_count => l_msg_count,
1493 x_msg_data => l_msg_data,
1494 p_media_lc_rec => mlcs_tbl(l),
1495 x_milcs_id => l_milcs_id);
1496 IF (l_ret_status <> FND_API.G_RET_STS_SUCCESS) THEN
1497 l_rtn_name := 'Add_MediaLifecycle';
1498 /*FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Add_MediaLifecycle failed' ||
1499 ', msg_count = ' || l_msg_count ||
1500 ', msg_data = ' || l_msg_data);*/
1501 RAISE media_exception;
1502 END IF;
1503 END LOOP;
1504
1505 -- now update the media item and close it as well.
1506 JTF_IH_PUB.Close_MediaItem(p_api_version => 1.0,
1507 p_init_msg_list => FND_API.G_TRUE,
1508 p_commit => FND_API.G_FALSE,
1509 p_user_id => p_user_id,
1510 x_return_status => l_ret_status,
1511 x_msg_count => l_msg_count,
1512 x_msg_data => l_msg_data,
1513 p_media_rec => med_rec);
1514
1515 IF (l_ret_status <> FND_API.G_RET_STS_SUCCESS) THEN
1516 l_rtn_name := 'Close_MediaItem';
1517 /*FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Close_MediaItem failed' ||
1518 ', msg_count = ' || l_msg_count ||
1519 ', msg_data = ' || l_msg_data);*/
1520 RAISE media_exception;
1521 END IF;
1522 END IF; --(l_media_id_given = TRUE/FALSE)
1523
1524 --f. keep track of mediaitem_identifier to media_id values
1525 -- relate the media_id to media_ident, is needed for activity rec. later
1526 x_med_id_tbl(med_ident) := med_id;
1527
1528 END LOOP; -- MED nodes
1529
1530 g_hrt_beat := 13.6;
1531 x_ret_status := l_ret_status;
1532
1533 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1534 l_fnd_log_msg := 'PROCESS_MEDIA_ITEMS Out parameters:' ||
1535 'x_med_id_tbl TYPE media_id_trkr_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER' ||
1536 'x_ret_status = '|| x_ret_status ||
1537 'x_ret_msg = '|| x_ret_msg;
1538 --dbms_output.put_line(l_fnd_log_msg);
1539 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
1540 'jtf.plsql.JTF_IH_BULK.PROCESS_MEDIA_ITEMS.end', l_fnd_log_msg);
1541 END IF;
1542
1543 EXCEPTION
1544 -- this exception is raised if any of the JTF_IH_PUB's media calls fail.
1545 WHEN media_exception THEN
1546
1547 IF( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1548 FND_MESSAGE.SET_NAME('JTF', 'JTF_IH_BULK_MEDIA_FAIL2');
1549 FND_MESSAGE.SET_TOKEN('RTN_NAME', l_rtn_name);
1550 FND_MESSAGE.SET_TOKEN('MSG_COUNT', l_msg_count);
1551 FND_LOG.MESSAGE(FND_LOG.LEVEL_EXCEPTION,
1552 'jtf.plsql.JTF_IH_BULK.PROCESS_MEDIA_ITEMS.end', TRUE);
1553 END IF;
1554
1555 x_ret_msg := '';
1556 FOR i in 1..l_msg_count LOOP
1557 l_msg_data := FND_MSG_PUB.Get(i, 'F');
1558
1559 FND_MESSAGE.SET_NAME('JTF', 'JTF_IH_BULK_ERROR_MSG');
1560 FND_MESSAGE.SET_TOKEN('MSG_NUM', i);
1561 FND_MESSAGE.SET_TOKEN('MSG_TXT', l_msg_data);
1562 l_err_media := FND_MESSAGE.GET;
1563 --FND_FILE.PUT_LINE(FND_FILE.LOG, l_err_media);
1564
1565 x_ret_msg := x_ret_msg || l_err_media;
1566
1567 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1568 l_fnd_log_msg := l_err_media;
1569 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
1570 'jtf.plsql.JTF_IH_BULK.PROCESS_MEDIA_ITEMS.end', l_fnd_log_msg);
1571 END IF;
1572 --x_ret_msg := x_ret_msg || substrb(l_msg_data, 0, (2000-lengthb(x_ret_msg))) ||
1573 -- substrb(l_msg_data, 0, (2000-lengthb(', ')));
1574 END LOOP;
1575
1576 -- return error
1577 x_ret_status := FND_API.G_RET_STS_ERROR;
1578
1579 WHEN OTHERS THEN
1580 DECLARE
1581 error NUMBER := SQLCODE;
1582 errm varchar2(300) := SQLERRM;
1583 BEGIN
1584 IF( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1585 FND_MESSAGE.SET_NAME('JTF', 'JTF_IH_BULK_MEDIA_FAIL_UNEXP');
1586 FND_LOG.MESSAGE(FND_LOG.LEVEL_EXCEPTION,
1587 'jtf.plsql.JTF_IH_BULK.PROCESS_MEDIA_ITEMS', TRUE);
1588 END IF;
1589
1590 --FND_FILE.PUT_LINE(FND_FILE.LOG, FND_MESSAGE.GET);
1591 IF( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1592 FND_MESSAGE.SET_NAME('JTF', 'JTF_IH_BULK_ERR_CODE_MSG');
1593 FND_MESSAGE.SET_TOKEN('ERR_CODE', error);
1594 FND_MESSAGE.SET_TOKEN('ERR_MSG', errm);
1595 FND_LOG.MESSAGE(FND_LOG.LEVEL_EXCEPTION,
1596 'jtf.plsql.JTF_IH_BULK.PROCESS_MEDIA_ITEMS', TRUE);
1597 END IF;
1598
1599 --FND_FILE.PUT_LINE(FND_FILE.LOG, FND_MESSAGE.GET);
1600
1601 x_ret_msg := errm;
1602
1603 -- an unknown exception is always unexpected
1604 x_ret_status := FND_API.G_RET_STS_UNEXP_ERROR;
1605
1606 END;
1607
1608 END PROCESS_MEDIA_ITEMS;
1609
1610 --
1611 -- Utility function to gather activity attributes for an interaction.
1612 --
1613 -- Parameters
1614 --
1615 -- act_nl - IN - dbms_xmldom.DomNodeList of act nodes
1616 -- p_bulk_interaction_id - IN - self explanatory
1617 -- p_bulk_writer_code - IN - self explanatory
1618 -- p_bulk_batch_type - IN - self explanatory
1619 -- p_bulk_batch_id - IN - self explanatory
1620 -- med_id_tbl - IN - this carries the media_identifier to media_id relation
1621 -- x_act_tbl - OUT - parsed activity records collection
1622 -- x_ret_status - OUT - self explanatory
1623 -- x_ret_msg - OUT - self explanatory
1624 --
1625 PROCEDURE GATHER_ACT_TBL
1626 (
1627 act_nl IN dbms_xmldom.DomNodeList,
1628 p_bulk_interaction_id IN NUMBER,
1629 p_bulk_writer_code IN VARCHAR2,
1630 p_bulk_batch_type IN VARCHAR2,
1631 p_bulk_batch_id IN NUMBER,
1632 med_id_tbl IN media_id_trkr_type,
1633 x_act_tbl IN OUT NOCOPY JTF_IH_PUB.ACTIVITY_TBL_TYPE,
1634 x_ret_status IN OUT NOCOPY VARCHAR2,
1635 x_ret_msg IN OUT NOCOPY VARCHAR2
1636 ) IS
1637
1638 med_ident NUMBER;
1639 act_node dbms_xmldom.DOMNode;
1640 act_elem dbms_xmldom.DOMElement;
1641 act_rec JTF_IH_PUB.activity_rec_type;
1642 med_id NUMBER;
1643 act_id NUMBER;
1644
1645 date_str VARCHAR2(50);
1646 num_act NUMBER;
1647 BEGIN
1648
1649 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1650 l_fnd_log_msg := 'GATHER_ACT_TBL In parameters :'||
1651 'act_nl TYPE dbms_xmldom.DomNodeList'||
1652 ', p_bulk_interaction_id = '|| p_bulk_interaction_id ||
1653 ', p_bulk_writer_code = '|| p_bulk_writer_code||
1654 ', p_bulk_batch_type = '|| p_bulk_batch_type ||
1655 ', p_bulk_batch_id = '|| p_bulk_batch_id ||
1656 ', x_med_id_tbl TYPE media_id_trkr_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER' ||
1657 ', x_act_tbl TYPE JTF_IH_PUB.ACTIVITY_TBL_TYPE' ||
1658 ', x_ret_status = '|| x_ret_status ||
1659 ', x_ret_msg = '|| x_ret_msg;
1660 --dbms_output.put_line(l_fnd_log_msg);
1661 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
1662 'jtf.plsql.JTF_IH_BULK.GATHER_ACT_TBL.begin', l_fnd_log_msg);
1663 END IF;
1664 -- to do
1665 /*
1666 iv.for each activity node
1667 a.gather each activity's attributes, including media_id using the mapping from b.1.ii.e
1668 b.create a Activity record and save
1669 */
1670
1671 num_act := dbms_xmldom.getLength(act_nl);
1672
1673 --ii.for each media node
1674 FOR j IN 1..num_act LOOP
1675 act_node := dbms_xmldom.item(act_nl, j-1);
1676
1677 -- an element is easier to get attribute values from
1678 act_elem := dbms_xmldom.makeElement(act_node);
1679
1680 -- get the media identifier if available
1681 med_id := NULL;
1682 med_ident := dbms_xmldom.getAttribute(act_elem, 'mediaitem_identifier');
1683 IF (med_ident IS NOT NULL) THEN
1684 -- get the corresponding media_id from the med_id_tbl
1685 med_id := med_id_tbl(med_ident);
1686 END IF;
1687
1688
1689 --FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'media_id and media_ident values are ' || med_id || ', ' || med_ident);
1690
1691 --a.gather each activity's attributes, including media_id using the mapping from b.1.ii.e
1692 date_str := dbms_xmldom.getAttribute(act_elem, 'start_date_time');
1693 -- RDD - Bug 5330922 - Removed the call to convert GMT input to server date - Input now in Server Date
1694 x_act_tbl(j).start_date_time := TO_DATE(date_str, G_DATE_FORMAT);
1695 date_str := dbms_xmldom.getAttribute(act_elem, 'end_date_time');
1696 -- RDD - Bug 5330922 - Removed the call to convert GMT input to server date - Input now in Server Date
1697 x_act_tbl(j).end_date_time := TO_DATE(date_str, G_DATE_FORMAT);
1698
1699 IF (med_id IS NOT NULL) THEN
1700 x_act_tbl(j).media_id := med_id;
1701 END IF;
1702
1703 x_act_tbl(j).cust_account_id := dbms_xmldom.getAttribute(act_elem, 'cust_account_id');
1704 x_act_tbl(j).cust_org_id := dbms_xmldom.getAttribute(act_elem, 'cust_org_id');
1705 x_act_tbl(j).role := dbms_xmldom.getAttribute(act_elem, 'role');
1706 x_act_tbl(j).task_id := dbms_xmldom.getAttribute(act_elem, 'task_id');
1707 x_act_tbl(j).doc_id := dbms_xmldom.getAttribute(act_elem, 'doc_id');
1708 x_act_tbl(j).doc_ref := dbms_xmldom.getAttribute(act_elem, 'doc_ref');
1709 x_act_tbl(j).doc_source_object_name := dbms_xmldom.getAttribute(act_elem, 'doc_source_object_name');
1710 x_act_tbl(j).action_item_id := dbms_xmldom.getAttribute(act_elem, 'action_item_id');
1711 x_act_tbl(j).outcome_id := dbms_xmldom.getAttribute(act_elem, 'outcome_id');
1712 x_act_tbl(j).result_id := dbms_xmldom.getAttribute(act_elem, 'result_id');
1713 x_act_tbl(j).reason_id := dbms_xmldom.getAttribute(act_elem, 'reason_id');
1714 x_act_tbl(j).description := dbms_xmldom.getAttribute(act_elem, 'description');
1715 x_act_tbl(j).action_id := dbms_xmldom.getAttribute(act_elem, 'action_id');
1716 x_act_tbl(j).interaction_action_type := dbms_xmldom.getAttribute(act_elem, 'interaction_action_type');
1717 x_act_tbl(j).object_id := dbms_xmldom.getAttribute(act_elem, 'object_id');
1718 x_act_tbl(j).object_type := dbms_xmldom.getAttribute(act_elem, 'object_type');
1719 x_act_tbl(j).source_code_id := dbms_xmldom.getAttribute(act_elem, 'source_code_id');
1720 x_act_tbl(j).source_code := dbms_xmldom.getAttribute(act_elem, 'source_code');
1721 x_act_tbl(j).script_trans_id := dbms_xmldom.getAttribute(act_elem, 'script_trans_id');
1722 x_act_tbl(j).attribute1 := dbms_xmldom.getAttribute(act_elem, 'attribute1');
1723 x_act_tbl(j).attribute2 := dbms_xmldom.getAttribute(act_elem, 'attribute2');
1724 x_act_tbl(j).attribute3 := dbms_xmldom.getAttribute(act_elem, 'attribute3');
1725 x_act_tbl(j).attribute4 := dbms_xmldom.getAttribute(act_elem, 'attribute4');
1726 x_act_tbl(j).attribute5 := dbms_xmldom.getAttribute(act_elem, 'attribute5');
1727 x_act_tbl(j).attribute6 := dbms_xmldom.getAttribute(act_elem, 'attribute6');
1728 x_act_tbl(j).attribute7 := dbms_xmldom.getAttribute(act_elem, 'attribute7');
1729 x_act_tbl(j).attribute8 := dbms_xmldom.getAttribute(act_elem, 'attribute8');
1730 x_act_tbl(j).attribute9 := dbms_xmldom.getAttribute(act_elem, 'attribute9');
1731 x_act_tbl(j).attribute10 := dbms_xmldom.getAttribute(act_elem, 'attribute10');
1732 x_act_tbl(j).attribute11 := dbms_xmldom.getAttribute(act_elem, 'attribute11');
1733 x_act_tbl(j).attribute12 := dbms_xmldom.getAttribute(act_elem, 'attribute12');
1734 x_act_tbl(j).attribute13 := dbms_xmldom.getAttribute(act_elem, 'attribute13');
1735 x_act_tbl(j).attribute14 := dbms_xmldom.getAttribute(act_elem, 'attribute14');
1736 x_act_tbl(j).attribute15 := dbms_xmldom.getAttribute(act_elem, 'attribute15');
1737 x_act_tbl(j).attribute_category := dbms_xmldom.getAttribute(act_elem, 'attribute_category');
1738
1739 -- bulk attributes
1740 x_act_tbl(j).bulk_writer_code := p_bulk_writer_code;
1741 x_act_tbl(j).bulk_batch_type := p_bulk_batch_type;
1742 x_act_tbl(j).bulk_batch_id := p_bulk_batch_id;
1743 x_act_tbl(j).bulk_interaction_id := p_bulk_interaction_id;
1744 END LOOP; -- MED nodes
1745
1746 x_ret_status := FND_API.G_RET_STS_SUCCESS;
1747
1748 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1749 l_fnd_log_msg := 'GATHER_ACT_TBL Out parameters :'||
1750 'x_act_tbl TYPE JTF_IH_PUB.ACTIVITY_TBL_TYPE' ||
1751 ', x_ret_status = '|| x_ret_status ||
1752 ', x_ret_msg = '|| x_ret_msg;
1753 --dbms_output.put_line(l_fnd_log_msg);
1754 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
1755 'jtf.plsql.JTF_IH_BULK.GATHER_ACT_TBL.end', l_fnd_log_msg);
1756 END IF;
1757
1758 EXCEPTION
1759 WHEN OTHERS THEN
1760 DECLARE
1761 error NUMBER := SQLCODE;
1762 errm varchar2(300) := SQLERRM;
1763 BEGIN
1764
1765 IF( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1766 FND_MESSAGE.SET_NAME('JTF', 'JTF_IH_BULK_ACT_FAIL_UNEXP');
1767 FND_LOG.MESSAGE(FND_LOG.LEVEL_EXCEPTION,
1768 'jtf.plsql.JTF_IH_BULK.GATHER_ACT_TBL', TRUE);
1769 END IF;
1770
1771 --FND_FILE.PUT_LINE(FND_FILE.LOG, FND_MESSAGE.GET);
1772
1773 IF( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1774 FND_MESSAGE.SET_NAME('JTF', 'JTF_IH_BULK_ERR_CODE_MSG');
1775 FND_MESSAGE.SET_TOKEN('ERR_CODE', error);
1776 FND_MESSAGE.SET_TOKEN('ERR_MSG', errm);
1777 FND_LOG.MESSAGE(FND_LOG.LEVEL_EXCEPTION,
1778 'jtf.plsql.JTF_IH_BULK.GATHER_ACT_TBL', TRUE);
1779 END IF;
1780
1781 --FND_FILE.PUT_LINE(FND_FILE.LOG, FND_MESSAGE.GET);
1782
1783 x_ret_msg := errm;
1784 END;
1785
1786 -- an exception is always unexpected
1787 x_ret_status := FND_API.G_RET_STS_UNEXP_ERROR;
1788
1789 END GATHER_ACT_TBL;
1790
1791 --
1792 -- Utility function to gather all interaction attributes from xml.
1793 --
1794 -- Parameters
1795 --
1796 -- int_elem - IN - dbms_xmldom.DomElement, with xml data
1797 -- p_bulk_interaction_id - IN - self explanatory
1798 -- p_bulk_writer_code - IN - self explanatory
1799 -- p_bulk_batch_type - IN - self explanatory
1800 -- p_bulk_batch_id - IN - self explanatory
1801 -- int_rec - OUT - this is the return interaction record
1802 -- x_ret_status - OUT - self explanatory
1803 -- x_ret_msg - OUT - self explanatory
1804 --
1805 PROCEDURE GATHER_INT_ATTR
1806 (
1807 int_elem IN dbms_xmldom.DomElement,
1808 p_bulk_interaction_id IN NUMBER,
1809 p_bulk_writer_code IN VARCHAR2,
1810 p_bulk_batch_type IN VARCHAR2,
1811 p_bulk_batch_id IN NUMBER,
1812 x_int_rec IN OUT NOCOPY JTF_IH_PUB.INTERACTION_REC_TYPE,
1813 x_ret_status IN OUT NOCOPY VARCHAR2,
1814 x_ret_msg IN OUT NOCOPY VARCHAR2
1815 ) IS
1816 date_str VARCHAR2(50);
1817 BEGIN
1818
1819 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1820 l_fnd_log_msg := 'GATHER_INT_ATTR In parameters :'||
1821 'int_elem TYPE dbms_xmldom.DomElement'||
1822 ', p_bulk_interaction_id = '|| p_bulk_interaction_id ||
1823 ', p_bulk_writer_code = '|| p_bulk_writer_code||
1824 ', p_bulk_batch_type = '|| p_bulk_batch_type ||
1825 ', p_bulk_batch_id = '|| p_bulk_batch_id ||
1826 ', x_int_rec TYPE JTF_IH_PUB.INTERACTION_REC_TYPE'||
1827 ', x_ret_status = '|| x_ret_status ||
1828 ', x_ret_msg = '|| x_ret_msg;
1829 --dbms_output.put_line(l_fnd_log_msg);
1830 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
1831 'jtf.plsql.JTF_IH_BULK.GATHER_INT_ATTR.begin', l_fnd_log_msg);
1832 END IF;
1833
1834 date_str := dbms_xmldom.getAttribute(int_elem, 'start_date_time');
1835 -- RDD - Bug 5330922 - Removed the call to convert GMT input to server date - Input now in Server Date
1836 x_int_rec.start_date_time := TO_DATE(date_str, G_DATE_FORMAT);
1837 date_str := dbms_xmldom.getAttribute(int_elem, 'end_date_time');
1838 -- RDD - Bug 5330922 - Removed the call to convert GMT input to server date - Input now in Server Date
1839 x_int_rec.end_date_time := TO_DATE(date_str, G_DATE_FORMAT);
1840
1841 x_int_rec.reference_form := dbms_xmldom.getAttribute(int_elem, 'reference_form');
1842 x_int_rec.follow_up_action := dbms_xmldom.getAttribute(int_elem, 'follow_up_action');
1843 x_int_rec.duration := dbms_xmldom.getAttribute(int_elem, 'duration');
1844 x_int_rec.inter_interaction_duration := dbms_xmldom.getAttribute(int_elem, 'inter_interaction_duration');
1845 x_int_rec.non_productive_time_amount := dbms_xmldom.getAttribute(int_elem, 'non_productive_time_amount');
1846 x_int_rec.preview_time_amount := dbms_xmldom.getAttribute(int_elem, 'preview_time_amount');
1847 x_int_rec.productive_time_amount := dbms_xmldom.getAttribute(int_elem, 'productive_time_amount');
1848 x_int_rec.wrapup_time_amount := dbms_xmldom.getAttribute(int_elem, 'wrap_Up_time_amount');
1849 x_int_rec.handler_id := dbms_xmldom.getAttribute(int_elem, 'handler_id');
1850 x_int_rec.script_id := dbms_xmldom.getAttribute(int_elem, 'script_id');
1851 x_int_rec.outcome_id := dbms_xmldom.getAttribute(int_elem, 'outcome_id');
1852 x_int_rec.result_id := dbms_xmldom.getAttribute(int_elem, 'result_id');
1853 x_int_rec.reason_id := dbms_xmldom.getAttribute(int_elem, 'reason_id');
1854 x_int_rec.resource_id := dbms_xmldom.getAttribute(int_elem, 'resource_id');
1855 x_int_rec.party_id := dbms_xmldom.getAttribute(int_elem, 'party_id');
1856 x_int_rec.parent_id := dbms_xmldom.getAttribute(int_elem, 'parent_id');
1857 x_int_rec.object_id := dbms_xmldom.getAttribute(int_elem, 'object_id');
1858 x_int_rec.object_type := dbms_xmldom.getAttribute(int_elem, 'object_type');
1859 x_int_rec.source_code_id := dbms_xmldom.getAttribute(int_elem, 'source_code_id');
1860 x_int_rec.source_code := dbms_xmldom.getAttribute(int_elem, 'source_code');
1861 x_int_rec.attribute1 := dbms_xmldom.getAttribute(int_elem, 'attribute1');
1862 x_int_rec.attribute2 := dbms_xmldom.getAttribute(int_elem, 'attribute2');
1863 x_int_rec.attribute3 := dbms_xmldom.getAttribute(int_elem, 'attribute3');
1864 x_int_rec.attribute4 := dbms_xmldom.getAttribute(int_elem, 'attribute4');
1865 x_int_rec.attribute5 := dbms_xmldom.getAttribute(int_elem, 'attribute5');
1866 x_int_rec.attribute6 := dbms_xmldom.getAttribute(int_elem, 'attribute6');
1867 x_int_rec.attribute7 := dbms_xmldom.getAttribute(int_elem, 'attribute7');
1868 x_int_rec.attribute8 := dbms_xmldom.getAttribute(int_elem, 'attribute8');
1869 x_int_rec.attribute9 := dbms_xmldom.getAttribute(int_elem, 'attribute9');
1870 x_int_rec.attribute10 := dbms_xmldom.getAttribute(int_elem, 'attribute10');
1871 x_int_rec.attribute11 := dbms_xmldom.getAttribute(int_elem, 'attribute11');
1872 x_int_rec.attribute12 := dbms_xmldom.getAttribute(int_elem, 'attribute12');
1873 x_int_rec.attribute13 := dbms_xmldom.getAttribute(int_elem, 'attribute13');
1874 x_int_rec.attribute14 := dbms_xmldom.getAttribute(int_elem, 'attribute14');
1875 x_int_rec.attribute15 := dbms_xmldom.getAttribute(int_elem, 'attribute15');
1876 x_int_rec.attribute_category := dbms_xmldom.getAttribute(int_elem, 'attribute_category');
1877 x_int_rec.method_code := dbms_xmldom.getAttribute(int_elem, 'method_code');
1878 x_int_rec.primary_party_id := dbms_xmldom.getAttribute(int_elem, 'primary_party_id');
1879 x_int_rec.contact_party_id := dbms_xmldom.getAttribute(int_elem, 'contact_party_id');
1880 x_int_rec.contact_rel_party_id := dbms_xmldom.getAttribute(int_elem, 'contact_rel_party_id');
1881
1882 --
1883 -- These two are defaulted, plus they are not exposed so ignore them
1884 --x_int_rec.touchpoint1_type := dbms_xmldom.getAttribute(int_elem, 'touchpoint1_type');
1885 --x_int_rec.touchpoint2_type := dbms_xmldom.getAttribute(int_elem, 'touchpoint2_type');
1886 --
1887
1888 -- bulk ids
1889 x_int_rec.bulk_writer_code := p_bulk_writer_code;
1890 x_int_rec.bulk_batch_type := p_bulk_batch_type;
1891 x_int_rec.bulk_batch_id := p_bulk_batch_id;
1892 x_int_rec.bulk_interaction_id := p_bulk_interaction_id;
1893
1894 x_ret_status := FND_API.G_RET_STS_SUCCESS;
1895
1896 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1897 l_fnd_log_msg := 'GATHER_INT_ATTR Out parameters :'||
1898 'x_int_rec TYPE JTF_IH_PUB.INTERACTION_REC_TYPE'||
1899 'x_ret_status = '|| x_ret_status ||
1900 'x_ret_msg = '|| x_ret_msg;
1901 --dbms_output.put_line(l_fnd_log_msg);
1902 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
1903 'jtf.plsql.JTF_IH_BULK.GATHER_INT_ATTR.end', l_fnd_log_msg);
1904 END IF;
1905 END GATHER_INT_ATTR;
1906
1907 --
1908 -- Utility function to gather all interaction attributes from xml.
1909 --
1910 -- Parameters
1911 --
1912 -- int_node - IN - dbms_xmldom.DomNode, with interaction xml data
1913 -- p_bulk_writer_code - IN - self explanatory
1914 -- p_bulk_batch_type - IN - self explanatory
1915 -- p_bulk_batch_id - IN - self explanatory
1916 -- p_bulk_interaction_id - IN - self explanatory
1917 -- p_error_msg - IN - message describing what failed
1918 -- p_ret_msg - IN - message describing underlying cause
1919 --
1920 -- N O T E - It would be simpler if we could use dbms_xmldom.WriteToClob() to convert
1921 -- the p_int_node into a CLOB and then call the version 2 of this
1922 -- routine. But, that does not seem possible because a CLOB locator
1923 -- has to be tied to db column.
1924 --
1925 FUNCTION LOG_BULK_ERROR
1926 (
1927 p_int_node IN dbms_xmldom.DOMNode,
1928 p_bulk_writer_code IN VARCHAR2,
1929 p_bulk_batch_type IN VARCHAR2,
1930 p_bulk_batch_id IN NUMBER,
1931 p_bulk_interaction_id IN NUMBER,
1932 p_error_msg IN VARCHAR2,
1933 p_ret_msg IN VARCHAR2
1934 ) RETURN VARCHAR2 IS
1935
1936 bad_int_clob CLOB;
1937 rec_id NUMBER;
1938 error_msg_2 VARCHAR2(2000);
1939
1940 -- Bug3781768 Perf issue with literal usage
1941 l_obj_version_perf NUMBER;
1942
1943 BEGIN
1944
1945 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1946 l_fnd_log_msg := 'FUNCTION LOG_BULK_ERROR In parameters :'||
1947 'p_int_node TYPE dbms_xmldom.DOMNode' ||
1948 ', p_bulk_writer_code = '|| p_bulk_writer_code||
1949 ', p_bulk_batch_type = '|| p_bulk_batch_type ||
1950 ', p_bulk_batch_id = '|| p_bulk_batch_id ||
1951 ', p_bulk_interaction_id = '|| p_bulk_interaction_id ||
1952 ', p_error_msg = '|| p_error_msg ||
1953 ', p_ret_msg = '|| p_ret_msg;
1954 --dbms_output.put_line(l_fnd_log_msg);
1955 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
1956 'jtf.plsql.JTF_IH_BULK.LOG_BULK_ERROR.begin', l_fnd_log_msg);
1957 END IF;
1958 SAVEPOINT BULK_ERROR;
1959
1960 -- this is a sort of complicated logic here - basically, we don't want to
1961 -- overrun the buffer length of error_msg_2 so we are trying to fit as much
1962 -- as possible from the three strings into error_msg_2
1963 error_msg_2 := SUBSTRB(p_error_msg, 0, 2000);
1964 error_msg_2 := error_msg_2 || SUBSTRB(' - ', 0, (2000 - LENGTHB(error_msg_2)));
1965 --error_msg_2 := error_msg_2 || SUBSTRB(p_ret_msg, 0, (2000 - LENGTHB(error_msg_2)));
1966
1967 /*FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'In LOG_BULK_ERROR');
1968 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'p_error_msg = ' || p_error_msg);
1969 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'p_ret_msg = ' || p_ret_msg);
1970 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'error_msg_2 = ' || error_msg_2);
1971 */
1972
1973 SELECT JTF_IH_BULK_ERRORS_S1.NEXTVAL INTO rec_id FROM sys.dual;
1974
1975 --FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'BULK_ERROR_ID = ' || rec_id);
1976
1977 -- Bug3781768 Perf issue with literal usage
1978 l_obj_version_perf := 0;
1979
1980 INSERT INTO JTF_IH_BULK_ERRORS
1981 (bulk_error_id, object_version_number, creation_date, created_by,
1982 last_update_date, last_updated_by, last_update_login, program_id,
1983 request_id, program_application_id, program_update_date,
1984 bulk_writer_code, bulk_batch_id, bulk_batch_type, bulk_interaction_id,
1985 error_message, inter_req_xml_doc)
1986 VALUES
1987 (rec_id, l_obj_version_perf, sysdate, G_USER_ID,
1988 sysdate, G_USER_ID, G_LOGIN_ID, G_CONC_PROGRAM_ID,
1989 G_CONC_REQUEST_ID, G_PROG_APPL_ID, sysdate,
1990 p_bulk_writer_code, p_bulk_batch_id, p_bulk_batch_type,
1991 p_bulk_interaction_id, error_msg_2, EMPTY_CLOB());
1992
1993 SELECT inter_req_xml_doc INTO bad_int_clob
1994 FROM jtf_ih_bulk_errors
1995 WHERE bulk_error_id = rec_id;
1996
1997 bad_int_clob := 'AAAA';
1998
1999 dbms_xmldom.writeToClob(p_int_node, bad_int_clob);
2000
2001 UPDATE jtf_ih_bulk_errors
2002 SET inter_req_xml_doc = bad_int_clob
2003 WHERE bulk_error_id = rec_id;
2004
2005 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
2006 l_fnd_log_msg := 'FUNCTION LOG_BULK_ERROR return variable :'||
2007 'FND_API.G_RET_STS_SUCCESS = '|| FND_API.G_RET_STS_SUCCESS;
2008 --dbms_output.put_line(l_fnd_log_msg);
2009 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
2010 'jtf.plsql.JTF_IH_BULK.LOG_BULK_ERROR.end', l_fnd_log_msg);
2011 END IF;
2012
2013 RETURN FND_API.G_RET_STS_SUCCESS;
2014
2015 EXCEPTION
2016 WHEN OTHERS THEN
2017
2018 IF( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
2019 FND_MESSAGE.SET_NAME('JTF', 'JTF_IH_BULK_LOG1_FAIL');
2020 FND_LOG.MESSAGE(FND_LOG.LEVEL_EXCEPTION,
2021 'jtf.plsql.JTF_IH_BULK.LOG_BULK_ERROR', TRUE);
2022 END IF;
2023
2024 --FND_FILE.PUT_LINE(FND_FILE.LOG, FND_MESSAGE.GET);
2025
2026 IF( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
2027 FND_MESSAGE.SET_NAME('JTF', 'JTF_IH_BULK_ERR_CODE_MSG');
2028 FND_MESSAGE.SET_TOKEN('ERR_CODE', SQLCODE);
2029 FND_MESSAGE.SET_TOKEN('ERR_MSG', SQLERRM);
2030 FND_LOG.MESSAGE(FND_LOG.LEVEL_EXCEPTION,
2031 'jtf.plsql.JTF_IH_BULK.LOG_BULK_ERROR', TRUE);
2032 END IF;
2033
2034 --FND_FILE.PUT_LINE(FND_FILE.LOG, FND_MESSAGE.GET);
2035 -- because we got an error saving in the bulk errors, save the xml piece in
2036 -- the concurrent log file
2037
2038 -- tbd.
2039
2040 ROLLBACK WORK TO SAVEPOINT BULK_ERROR;
2041
2042 RETURN FND_API.G_RET_STS_UNEXP_ERROR;
2043 END LOG_BULK_ERROR;
2044
2045
2046 --
2047 -- Version 2 - takes IH_BULK_OBJ
2048 -- Utility function to gather all interaction attributes from xml.
2049 --
2050 -- Parameters
2051 --
2052 -- p_bulk_writer_code - IN - self explanatory
2053 -- p_bulk_batch_type - IN - self explanatory
2054 -- p_bulk_batch_id - IN - self explanatory
2055 -- p_bulk_interaction_id - IN - self explanatory
2056 -- p_bulk_interaction_request - IN - self explanatory
2057 -- p_error_msg - IN - message describing what failed
2058 -- p_ret_msg - IN - message describing underlying cause
2059 --
2060 FUNCTION LOG_BULK_ERROR
2061 (
2062 p_bulk_writer_code IN VARCHAR2,
2063 p_bulk_batch_type IN VARCHAR2,
2064 p_bulk_batch_id IN NUMBER,
2065 p_bulk_interaction_id IN NUMBER,
2066 p_bulk_interaction_request IN CLOB,
2067 p_error_msg IN VARCHAR2,
2068 p_ret_msg IN VARCHAR2
2069 ) RETURN VARCHAR2 IS
2070
2071 rec_id NUMBER;
2072 error_msg_2 VARCHAR2(2000);
2073
2074 -- Bug3781768 Perf issue with literal usage
2075 l_obj_version_perf NUMBER;
2076
2077 BEGIN
2078
2079 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
2080 l_fnd_log_msg := 'FUNCTION LOG_BULK_ERROR In parameters :'||
2081 'p_bulk_writer_code = '|| p_bulk_writer_code||
2082 ', p_bulk_batch_type = '|| p_bulk_batch_type ||
2083 ', p_bulk_batch_id = '|| p_bulk_batch_id ||
2084 ', p_bulk_interaction_id = '|| p_bulk_interaction_id ||
2085 ', p_error_msg = '|| p_error_msg ||
2086 ', p_ret_msg = '|| p_ret_msg;
2087 --dbms_output.put_line(l_fnd_log_msg);
2088 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
2089 'jtf.plsql.JTF_IH_BULK.LOG_BULK_ERROR.begin', l_fnd_log_msg);
2090 END IF;
2091 SAVEPOINT BULK_ERROR;
2092
2093 -- this is a sort of complicated logic here - basically, we don't want to
2094 -- overrun the buffer length of error_msg_2 so we are trying to fit as much
2095 -- as possible from the three strings into error_msg_2
2096 error_msg_2 := SUBSTRB(p_error_msg, 0, 2000);
2097 error_msg_2 := error_msg_2 || SUBSTRB(' - ', 0, (2000 - LENGTHB(error_msg_2)));
2098 error_msg_2 := error_msg_2 || SUBSTRB(p_ret_msg, 0, (2000 - LENGTHB(error_msg_2)));
2099
2100 SELECT JTF_IH_BULK_ERRORS_S1.NEXTVAL INTO rec_id FROM sys.dual;
2101
2102 --FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'BULK_ERROR_ID = ' || rec_id);
2103
2104 -- Bug3781768 Perf issue with literal usage
2105 l_obj_version_perf := 0;
2106
2107 INSERT INTO JTF_IH_BULK_ERRORS
2108 (bulk_error_id, object_version_number, creation_date, created_by,
2109 last_update_date, last_updated_by, last_update_login, program_id,
2110 request_id, program_application_id, program_update_date,
2111 bulk_writer_code, bulk_batch_id, bulk_batch_type, bulk_interaction_id,
2112 error_message, inter_req_xml_doc)
2113 VALUES
2114 (rec_id, l_obj_version_perf, sysdate, G_USER_ID,
2115 sysdate, G_USER_ID, G_LOGIN_ID, G_CONC_PROGRAM_ID,
2116 G_CONC_REQUEST_ID, G_PROG_APPL_ID, sysdate,
2117 p_bulk_writer_code, p_bulk_batch_id, p_bulk_batch_type,
2118 p_bulk_interaction_id, error_msg_2, p_bulk_interaction_request);
2119
2120 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
2121 l_fnd_log_msg := 'FUNCTION LOG_BULK_ERROR return variable :'||
2122 'FND_API.G_RET_STS_SUCCESS = '|| FND_API.G_RET_STS_SUCCESS;
2123 --dbms_output.put_line(l_fnd_log_msg);
2124 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
2125 'jtf.plsql.JTF_IH_BULK.LOG_BULK_ERROR.end', l_fnd_log_msg);
2126 END IF;
2127
2128 RETURN FND_API.G_RET_STS_SUCCESS;
2129
2130 EXCEPTION
2131 WHEN OTHERS THEN
2132
2133 IF( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
2134 FND_MESSAGE.SET_NAME('JTF', 'JTF_IH_BULK_LOG2_FAIL');
2135 FND_LOG.MESSAGE(FND_LOG.LEVEL_EXCEPTION,
2136 'jtf.plsql.JTF_IH_BULK.LOG_BULK_ERROR', TRUE);
2137 END IF;
2138
2139 --FND_FILE.PUT_LINE(FND_FILE.LOG, FND_MESSAGE.GET);
2140
2141 IF( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
2142 FND_MESSAGE.SET_NAME('JTF', 'JTF_IH_BULK_ERR_CODE_MSG');
2143 FND_MESSAGE.SET_TOKEN('ERR_CODE', SQLCODE);
2144 FND_MESSAGE.SET_TOKEN('ERR_MSG', SQLERRM);
2145 FND_LOG.MESSAGE(FND_LOG.LEVEL_EXCEPTION,
2146 'jtf.plsql.JTF_IH_BULK.LOG_BULK_ERROR', TRUE);
2147 END IF;
2148
2149 --FND_FILE.PUT_LINE(FND_FILE.LOG, FND_MESSAGE.GET);
2150
2151 -- because we got an error saving in the bulk errors, save the xml piece in
2152 -- the concurrent log file
2153
2154 -- tbd.
2155
2156 ROLLBACK WORK TO SAVEPOINT BULK_ERROR;
2157
2158 RETURN FND_API.G_RET_STS_UNEXP_ERROR;
2159 END LOG_BULK_ERROR;
2160
2161 FUNCTION FIND_ONE_CRASH_RECD
2162 (
2163 l_rec_recd IN OUT NOCOPY JTF_IH_BULK_RECOVERY%ROWTYPE
2164 ) RETURN BOOLEAN IS
2165
2166 TYPE BulkRecoveryCurTyp IS REF CURSOR RETURN JTF_IH_BULK_RECOVERY%ROWTYPE;
2167 l_rec_cv BulkRecoveryCurTyp;
2168 l_call_status BOOLEAN;
2169 l_found_recd BOOLEAN;
2170 l_phase VARCHAR2(80);
2171 l_status VARCHAR2(80);
2172 l_dev_phase VARCHAR2(30);
2173 l_dev_status VARCHAR2(30);
2174 l_message VARCHAR2(240);
2175
2176 BEGIN
2177 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
2178 l_fnd_log_msg := 'FUNCTION FIND_ONE_CRASH_RECD In parameters :'||
2179 'l_rec_recd TYPE JTF_IH_BULK_RECOVERY%ROWTYPE';
2180 --dbms_output.put_line(l_fnd_log_msg);
2181 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
2182 'jtf.plsql.JTF_IH_BULK.FIND_ONE_CRASH_RECD.begin', l_fnd_log_msg);
2183 END IF;
2184
2185 g_hrt_beat := -70;
2186 --FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Entered find_one_crash_Recd');
2187
2188 OPEN l_rec_cv FOR SELECT * FROM JTF_IH_BULK_RECOVERY;
2189
2190 -- look for a crash victim in the RECORD_ID for each row in this table
2191
2192 l_found_recd := FALSE;
2193 FETCH l_rec_cv INTO l_rec_recd;
2194
2195 WHILE (l_rec_cv%FOUND AND (NOT l_found_recd)) LOOP
2196
2197 g_hrt_beat := -70.5;
2198 /*FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Data in recov tbl - recovery_id, req_id, msg_id, num_int, (bulk_params)= ' ||
2199 l_rec_recd.recovery_id || ', ' ||
2200 l_rec_recd.request_id || ', ' ||
2201 l_rec_recd.msg_id || ', ' ||
2202 l_rec_recd.num_int_processed || ', ' ||
2203 '(' ||
2204 l_rec_recd.bulk_writer_code || ', ' ||
2205 l_rec_recd.bulk_batch_type || ', ' ||
2206 l_rec_recd.bulk_batch_id || ', ' ||
2207 l_rec_recd.bulk_interaction_id ||
2208 ')');*/
2209
2210 l_call_status := FND_CONCURRENT.GET_REQUEST_STATUS(
2211 REQUEST_ID => l_rec_recd.request_id,
2212 PHASE => l_phase,
2213 STATUS => l_status,
2214 DEV_PHASE => l_dev_phase,
2215 DEV_STATUS => l_dev_status,
2216 MESSAGE => l_message);
2217
2218 g_hrt_beat := -70.5;
2219 /*FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'get_request_stat - req_id, phase, status, d_phase, d_status, msg = ' ||
2220 l_rec_recd.request_id || ', ' ||
2221 l_phase || ', ' ||
2222 l_status || ', ' ||
2223 l_dev_phase || ', ' ||
2224 l_dev_status || ', ' ||
2225 l_message);*/
2226
2227 -- if this program has completed without deleting its record,
2228 -- it is a crash victim
2229 IF (l_dev_phase = 'COMPLETE') THEN
2230 g_hrt_beat := -71;
2231
2232 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
2233 FND_MESSAGE.SET_NAME('JTF', 'JTF_IH_BULK_CRASH_FND');
2234 FND_MESSAGE.SET_TOKEN('REQ_ID', l_rec_recd.request_id);
2235 FND_MESSAGE.SET_TOKEN('WRITER_CODE', l_rec_recd.bulk_writer_code);
2236 FND_MESSAGE.SET_TOKEN('BATCH_TYPE', l_rec_recd.bulk_batch_type);
2237 FND_MESSAGE.SET_TOKEN('BATCH_ID', l_rec_recd.bulk_batch_id);
2238 FND_LOG.MESSAGE(FND_LOG.LEVEL_STATEMENT,
2239 'jtf.plsql.JTF_IH_BULK.FIND_ONE_CRASH_RECD', TRUE);
2240 END IF;
2241 --FND_FILE.PUT_LINE(FND_FILE.LOG, FND_MESSAGE.GET);
2242
2243 l_found_recd := TRUE;
2244 END IF;
2245
2246 -- get next row
2247 FETCH l_rec_cv INTO l_rec_recd;
2248
2249 END LOOP; -- WHILE (l_rec_cv%FOUND AND (NOT l_found_recd))
2250
2251 CLOSE l_rec_cv;
2252
2253 IF (l_found_recd = TRUE) THEN
2254
2255 -- 'grab' the record by changing the request_id to our own
2256 UPDATE JTF_IH_BULK_RECOVERY
2257 SET request_id = G_CONC_REQUEST_ID,
2258 program_id = G_CONC_REQUEST_ID,
2259 program_application_id = G_PROG_APPL_ID,
2260 last_update_date = sysdate,
2261 program_update_date = sysdate
2262 WHERE recovery_id = l_rec_recd.recovery_id;
2263
2264 -- make change permanent so that any other crash recovery attempts will ignore
2265 -- this record
2266 COMMIT;
2267
2268 g_hrt_beat := -79;
2269 --FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Return TRUE find_one_crash_Recd');
2270
2271 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
2272 l_fnd_log_msg := 'FUNCTION FIND_ONE_CRASH_RECD Return parameters :'||
2273 'return boolean = TRUE';
2274 --dbms_output.put_line(l_fnd_log_msg);
2275 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
2276 'jtf.plsql.JTF_IH_BULK.FIND_ONE_CRASH_RECD.end', l_fnd_log_msg);
2277 END IF;
2278
2279 RETURN TRUE;
2280 ELSE
2281 g_hrt_beat := -78;
2282 --FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Return FALSE find_one_crash_Recd');
2283
2284 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
2285 l_fnd_log_msg := 'FUNCTION FIND_ONE_CRASH_RECD Return parameters :'||
2286 'return boolean = FALSE';
2287 --dbms_output.put_line(l_fnd_log_msg);
2288 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
2289 'jtf.plsql.JTF_IH_BULK.FIND_ONE_CRASH_RECD.end', l_fnd_log_msg);
2290 END IF;
2291
2292 RETURN FALSE;
2293 END IF;
2294
2295 END FIND_ONE_CRASH_RECD;
2296
2297
2298 PROCEDURE CLEAR_ONE_CRASH_RECD( l_rec_recd IN JTF_IH_BULK_RECOVERY%ROWTYPE) IS
2299
2300 l_ret_status VARCHAR2(5);
2301 BEGIN
2302
2303 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
2304 l_fnd_log_msg := 'CLEAR_ONE_CRASH_RECD In parameters :'||
2305 'l_rec_recd';
2306 --dbms_output.put_line(l_fnd_log_msg);
2307 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
2308 'jtf.plsql.JTF_IH_BULK.CLEAR_ONE_CRASH_RECD.begin', l_fnd_log_msg);
2309 END IF;
2310
2311 g_hrt_beat := -60;
2312 --FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Enter CLEAR_ONE_CRASH_RECD');
2313
2314
2315 /* To Do -
2316 Sort of simple - just pass the bulk record from here to the regular
2317 processing procedure above; it will take care of it. Once that is done,
2318 delete the current record from the recovery table.
2319
2320 The XML parser's NodeList is indexed from 0 to n-1, so the num_int_processed
2321 works fine as an index to the first non-processed interaction.
2322
2323 */
2324 l_ret_status := PROCESS_BULK_RECORD(l_rec_recd.bulk_writer_code,
2325 l_rec_recd.bulk_batch_type,
2326 l_rec_recd.bulk_batch_id,
2327 l_rec_recd.bulk_interaction_request,
2328 l_rec_recd.num_int_processed);
2329
2330 IF (l_ret_status <> FND_API.G_RET_STS_SUCCESS) THEN
2331 g_hrt_beat := -61;
2332 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
2333 FND_MESSAGE.SET_NAME('JTF', 'JTF_IH_BULK_RECOV_FAIL');
2334 FND_MESSAGE.SET_TOKEN('MSG_ID', l_rec_recd.msg_id);
2335 FND_LOG.MESSAGE(FND_LOG.LEVEL_STATEMENT,
2336 'jtf.plsql.JTF_IH_BULK.CLEAR_ONE_CRASH_RECD', TRUE);
2337 END IF;
2338 --FND_FILE.PUT_LINE(FND_FILE.LOG, FND_MESSAGE.GET);
2339 END IF;
2340
2341 -- now that the crash victim has been taken care of, get rid of it
2342 DELETE FROM jtf_ih_bulk_recovery
2343 WHERE request_id = G_CONC_REQUEST_ID;
2344
2345 COMMIT;
2346
2347 g_hrt_beat := -69;
2348 --FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'End CLEAR_ONE_CRASH_RECD');
2349 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
2350 l_fnd_log_msg := 'CLEAR_ONE_CRASH_RECD no Out Parameters';
2351 --dbms_output.put_line(l_fnd_log_msg);
2352 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
2353 'jtf.plsql.JTF_IH_BULK.CLEAR_ONE_CRASH_RECD.end', l_fnd_log_msg);
2354 END IF;
2355
2356 END CLEAR_ONE_CRASH_RECD;
2357
2358
2359 --
2360 -- This procedure attempts to perform crash recovery
2361 --
2362 -- Parameters - none
2363 --
2364 PROCEDURE PERFORM_CRASH_RECOVERY is
2365
2366 l_rec_recd JTF_IH_BULK_RECOVERY%ROWTYPE;
2367
2368 BEGIN
2369
2370 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
2371 l_fnd_log_msg := 'PERFORM_CRASH_RECOVERY No In parameters';
2372 --dbms_output.put_line(l_fnd_log_msg);
2373 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
2374 'jtf.plsql.JTF_IH_BULK.PERFORM_CRASH_RECOVERY.begin', l_fnd_log_msg);
2375 END IF;
2376 /*
2377 To do -
2378
2379 1. Look for crash victim
2380 2. If found -
2381 a. process any left over records
2382 b. go back to 1
2383 3. If not found go back to regular job
2384
2385 */
2386 g_hrt_beat := -100;
2387 --FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Entered crash_recovery');
2388
2389 WHILE (find_one_crash_recd(l_rec_recd)) LOOP
2390 clear_one_crash_recd(l_rec_recd);
2391 END LOOP; -- while(l_rec_recd is not null)
2392
2393 g_hrt_beat := -99;
2394 --FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Normal ending crash_recovery');
2395
2396 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
2397 l_fnd_log_msg := 'PERFORM_CRASH_RECOVERY No Out parameters';
2398 --dbms_output.put_line(l_fnd_log_msg);
2399 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
2400 'jtf.plsql.JTF_IH_BULK.PERFORM_CRASH_RECOVERY.end', l_fnd_log_msg);
2401 END IF;
2402
2403 RETURN;
2404
2405 EXCEPTION
2406 WHEN OTHERS THEN
2407 DECLARE
2408 errm VARCHAR2(2000);
2409 BEGIN
2410 errm := SQLERRM;
2411 IF (errm IS NULL) THEN
2412 errm := 'No Error Message in SQLERRM for parsing errors';
2413 END IF;
2414 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Error Message: ' || errm );
2415 g_hrt_beat := -98;
2416 IF( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
2417 FND_MESSAGE.SET_NAME('JTF', 'JTF_IH_BULK_RECOV_UNEXP');
2418 FND_LOG.MESSAGE(FND_LOG.LEVEL_EXCEPTION,
2419 'jtf.plsql.JTF_IH_BULK.PERFORM_CRASH_RECOVERY', TRUE);
2420 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION,
2421 'jtf.plsql.JTF_IH_BULK.PERFORM_CRASH_RECOVERY',errm);
2422 END IF;
2423 END;
2424
2425 --FND_FILE.PUT_LINE(FND_FILE.LOG, FND_MESSAGE.GET);
2426
2427 LOG_EXC_OTHERS('PERFORM_CRASH_RECOVERY');
2428
2429 END PERFORM_CRASH_RECOVERY;
2430
2431 --
2432 -- Utility procedure to do logging work in case of an unknown exception.
2433 --
2434 -- Purpose - to replace common code in various routines
2435 --
2436 -- Parameters -
2437 -- p_proc_name IN VARCHAR2 Procedure name where the exception happenned
2438 --
2439 PROCEDURE LOG_EXC_OTHERS (p_proc_name IN VARCHAR2) IS
2440 error NUMBER;
2441 errm VARCHAR2(2000);
2442 BEGIN
2443
2444 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
2445 l_fnd_log_msg := 'LOG_EXC_OTHERS In parameters : '||
2446 'p_proc_name =' || p_proc_name;
2447 --dbms_output.put_line(l_fnd_log_msg);
2448 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
2449 'jtf.plsql.JTF_IH_BULK.LOG_EXC_OTHERS.begin', l_fnd_log_msg);
2450 END IF;
2451
2452 error := SQLCODE;
2453 IF (error IS NULL) THEN
2454 error := -1;
2455 END IF;
2456
2457 errm := SQLERRM;
2458 IF (errm IS NULL) THEN
2459 errm := FND_MESSAGE.GET_STRING('JTF', 'JTF_IH_BULK_NOERRM');
2460 END IF;
2461
2462 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
2463 FND_MESSAGE.SET_NAME('JTF', 'JTF_IH_BULK_PROC_EXCP');
2464 FND_MESSAGE.SET_TOKEN('PROC_NAME', p_proc_name);
2465 FND_LOG.MESSAGE(FND_LOG.LEVEL_STATEMENT,
2466 'jtf.plsql.JTF_IH_BULK.LOG_EXC_OTHERS', TRUE);
2467 END IF;
2468
2469 --FND_FILE.PUT_LINE(FND_FILE.LOG, FND_MESSAGE.GET);
2470
2471 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
2472 l_fnd_log_msg := 'LOG_EXC_OTHERS No Out parameters';
2473 --dbms_output.put_line(l_fnd_log_msg);
2474 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
2475 'jtf.plsql.JTF_IH_BULK.LOG_EXC_OTHERS.begin', l_fnd_log_msg);
2476 END IF;
2477
2478 RETURN;
2479
2480 END LOG_EXC_OTHERS;
2481
2482
2483 END JTF_IH_BULK;