1 PACKAGE BODY CCT_CONCURRENT_PUB as
2 /* $Header: cctpconb.pls 115.22 2004/05/26 17:57:22 svinamda noship $*/
3
4 G_PKG_NAME CONSTANT varchar2(30) :='CCT_CONCURRENT_PUB';
5
6
7 PROCEDURE START_PROCESS(
8 ERRBUF OUT NOCOPY VARCHAR2,
9 RETCODE OUT NOCOPY VARCHAR2,
10 p_close_interval IN NUMBER
11
12 )
13 IS
14 l_submit_request_id NUMBER;
15 l_is_repeat_options_set BOOLEAN;
16 error_msg VARCHAR2(256);
17 l_return_value BOOLEAN;
18 REPEAT_OPTIONS_NOT_SET EXCEPTION;
19 REQUEST_NOT_SUBMITTED EXCEPTION;
20
21 BEGIN
22
23 -- fnd_file.put_line(fnd_file.log, 'Starting Processing');
24 -- fnd_file.put_line(fnd_file.log, 'p_close_interval = ' || to_char(p_close_interval));
25 l_is_repeat_options_set := fnd_request.set_repeat_options(
26 repeat_interval => p_close_interval,
27 repeat_unit => 'MINUTES',
28 repeat_type => 'START');
29 if not l_is_repeat_options_set then
30 rollback;
31 raise REPEAT_OPTIONS_NOT_SET;
32 end if;
33
34 l_submit_request_id := fnd_request.submit_request(
35 application=>'CCT',
36 program => 'CCT_CLOSE_MEDIA_ITEMS',
37 description => 'Concurrent program to close IH media items');
38
39 -- fnd_file.put_line(fnd_file.log, 'Request Id ' || to_char(l_submit_request_id));
40
41 if l_submit_request_id = 0 then
42 rollback;
43 raise REQUEST_NOT_SUBMITTED;
44 else
45 commit;
46 end if;
47 -- fnd_file.put_line(fnd_file.log, 'Controller Exited');
48
49 EXCEPTION
50 WHEN REPEAT_OPTIONS_NOT_SET THEN
51 FND_MESSAGE.SET_NAME('CCT','CCT_IH_REPEAT_OPTIONS_NOT_SET');
52 error_msg := FND_MESSAGE.GET;
53 fnd_file.put_line(fnd_file.log, error_msg);
54 l_return_value := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR', error_msg);
55
56 WHEN REQUEST_NOT_SUBMITTED THEN
57 FND_MESSAGE.SET_NAME('CCT','CCT_IH_REQUEST_NOT_SUBMITTED');
58 error_msg := FND_MESSAGE.GET;
59 fnd_file.put_line(fnd_file.log, error_msg);
60 l_return_value := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR', error_msg);
61
62 WHEN OTHERS THEN
63 FND_MESSAGE.SET_NAME('CCT','CCT_UNEXPECTED');
64 error_msg := FND_MESSAGE.GET;
65 fnd_file.put_line(fnd_file.log, error_msg);
66 l_return_value := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR', error_msg);
67 END START_PROCESS;
68
69
70
71 PROCEDURE CLOSE_MEDIA_ITEMS
72 (
73 ERRBUF OUT NOCOPY VARCHAR2,
74 RETCODE OUT NOCOPY VARCHAR2
75 )
76 IS
77 l_api_name CONSTANT VARCHAR2(30) := 'CLOSE_MEDIA_ITEMS';
78 l_api_version CONSTANT NUMBER := 1.0;
79
80 l_return_status VARCHAR2(256);
81 l_msg_count NUMBER;
82 l_msg_data VARCHAR2(256);
83 l_duration NUMBER;
84 l_milcs_duration NUMBER;
85 l_unclosed_wa_lc_segs NUMBER;
86 l_total_wa_lc_segs NUMBER;
87 l_end_date_time DATE;
88 l_start_date_time DATE Default null;
89 l_in_queue_start_date_time DATE Default null;
90 media_item_at_route_point_ex EXCEPTION;
91 no_data_found_ex EXCEPTION;
92 p_api_version NUMBER;
93 p_init_msg_list VARCHAR2(256);
94 p_commit VARCHAR2(256);
95
96 BEGIN
97 -- Standard Start of API savepoint
98
99 -- fnd_file.put_line(fnd_file.log, 'Worker Program Started 1');
100 p_api_version := 1.0;
101 p_init_msg_list := FND_API.G_FALSE;
102 p_commit := FND_API.G_TRUE;
103
104
105 SAVEPOINT CLOSE_MEDIA_ITEMS_PUB;
106 -- Standard call to check for call compatibility.
107 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
108 p_api_version ,
109 l_api_name ,
110 G_PKG_NAME )
111
112 THEN
113 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
114 END IF;
115 -- Initialize message list if p_init_msg_list is set to TRUE.
116 IF FND_API.To_Boolean( p_init_msg_list ) THEN
117 FND_MSG_PUB.initialize;
118 END IF;
119 -- Initialize API return status to success
120 RETCODE := FND_API.G_RET_STS_SUCCESS;
121 -- API body
122
123
124 -- fnd_file.put_line(fnd_file.log, 'Worker Program Started 2');
125 -- dbms_output.put_line('Worker Program Started 2');
126
127 declare cursor c1 is
128 select a.media_item_id, a.last_update_date, a.status, a.classification, a.attribute1, a.attribute2, b.start_date_time
129 from cct_media_items a, jtf_ih_media_items b
130 where a.media_type <> 1 and a.media_item_id = b.media_id and b.active = 'Y';
131
132 begin
133 -- fnd_file.put_line(fnd_file.log,'Cursor declared');
134 -- dbms_output.put_line('Cursor declared');
135 for c1_rec in c1 loop
136
137 begin
138 -- fnd_file.put_line(fnd_file.log,'Processing media item id ' || c1_rec.media_item_id);
139 -- dbms_output.put_line('Processing media item id ' || c1_rec.media_item_id);
140 if c1_rec.attribute2 = 'Y' then
141 begin
142 -- call was abandoned.
143 -- if routing milcs segment exists and is open close it.
144 -- if inqueue milcs segment exists and is open close it.
145 l_end_date_time := c1_rec.last_update_date;
146 -- fnd_file.put_line(fnd_file.log,'Media item id is abandoned ' || c1_rec.media_item_id);
147 -- dbms_output.put_line('Media item id is abandoned ' || c1_rec.media_item_id);
148 declare cursor c2 is
149 select milcs_id, start_date_time from jtf_ih_media_item_lc_segs where active = 'Y'
150 and media_id = c1_rec.media_item_id;
151 begin
152 for c2_rec in c2 loop
153 begin
154 l_milcs_duration := l_end_date_time - c2_rec.start_date_time;
155
156 JTF_IH_PUB_W.Update_MediaLifecycle
157 (p_api_version=>1.0
158 ,p_init_msg_list=>FND_API.G_FALSE
159 ,p_commit=>FND_API.G_TRUE
160 ,p_resp_appl_id=>1 -- IN RESP APPL ID
161 ,p_resp_id=>1 -- IN RESP ID
162 ,p_user_id=>FND_GLOBAL.USER_ID -- IN USER ID
163 ,p_login_id=>NULL -- IN LOGIN ID
164 ,p10_a3=>l_milcs_duration -- IN duration
165 ,p10_a4=>l_end_date_time -- IN end date time
166 ,p10_a5=>c2_rec.milcs_id -- IN milcs id
167 ,p10_a7=>c1_rec.media_item_id -- IN media id
168 ,p10_a8=>CCT_IH_PUB.G_IH_CCT_HANDLER_ID -- IN handler id
169 ,x_return_status=>l_return_status
170 ,x_msg_count=>l_msg_count
171 ,x_msg_data=>l_msg_data );
172
173 exception
174 when others then
175 begin
176 null;
177 -- fnd_file.put_line(fnd_file.log,'Others exception for abandoned media item ');
178 end;
179 end ;
180 end loop;
181 end;
182 end;
183 else
184 begin
185 -- dbms_output.put_line('Media item id is NOT abandoned ' || c1_rec.media_item_id);
186
187 select count(*) into l_unclosed_wa_lc_segs from jtf_ih_media_item_lc_segs c
188 where c.end_date_time is null
189 and c.media_id = c1_rec.media_item_id
190 and c.milcs_type_id = CCT_IH_PUB.G_IH_LCS_TYPE_WITH_AGENT;
191
192 select count(*) into l_total_wa_lc_segs from jtf_ih_media_item_lc_segs c
193 where c.media_id = c1_rec.media_item_id
194 and c.milcs_type_id = CCT_IH_PUB.G_IH_LCS_TYPE_WITH_AGENT;
195
196 if ((l_unclosed_wa_lc_segs > 0 ) or (l_total_wa_lc_segs = 0)) then raise no_data_found_ex;
197 end if;
198
199 select max(c.end_date_time) into l_end_date_time from jtf_ih_media_item_lc_segs c
200 where c.media_id = c1_rec.media_item_id and c.milcs_type_id = CCT_IH_PUB.G_IH_LCS_TYPE_WITH_AGENT;
201
202 -- if sql%notfound then raise no_data_found_ex;
203 -- end if;
204 if l_end_date_time is null then raise no_data_found_ex;
205 end if;
206
207 select max(c.start_date_time) into l_start_date_time from jtf_ih_media_item_lc_segs c
208 where c.media_id = c1_rec.media_item_id and c.milcs_type_id = CCT_IH_PUB.G_IH_LCS_TYPE_WITH_AGENT;
209
210 l_in_queue_start_date_time := null;
211
212 select max(c.start_date_time) into l_in_queue_start_date_time from jtf_ih_media_item_lc_segs c
213 where c.media_id = c1_rec.media_item_id and c.milcs_type_id = CCT_IH_PUB.G_IH_LCS_TYPE_IN_QUEUE;
214
215 -- if sql%notfound then null;
216 -- end if;
217 if (l_in_queue_start_date_time is not null) and
218 (l_start_date_time is not null) and
219 (l_start_date_time < l_in_queue_start_date_time)
220 then raise media_item_at_route_point_ex;
221 -- dbms_output.put_line('media item id transferred');
222 end if;
223
224 end;
225 end if;
226 -- fnd_file.put_line(fnd_file.log,'l_end_date_time' || to_char(l_end_date_time));
227 -- dbms_output.put_line('l_end_date_time' || to_char(l_end_date_time));
228
229 l_duration := l_end_date_time - c1_rec.start_date_time;
230 l_duration := round(24*60*60*l_duration);
231
232 -- fnd_file.put_line(fnd_file.log,'l_duration' || to_char(l_duration));
233 -- dbms_output.put_line('l_duration' || to_char(l_duration));
234
235
236 JTF_IH_PUB_W.CLOSE_MEDIAITEM
237 (p_api_version=>1.0 -- IN api version
238 ,p_init_msg_list=>p_init_msg_list -- IN init msg list
239 ,p_commit=>p_commit -- IN commit
240 ,p_resp_appl_id=>1 -- IN resp appl id
241 ,p_resp_id=>1 -- IN resp id
242 ,p_user_id=>FND_GLOBAL.USER_ID -- IN user id
243 ,p_login_id=>NULL -- IN login id
244 ,p10_a0=>c1_rec.media_item_id -- IN media_id
245 ,p10_a3=>l_duration -- IN duration
246 ,p10_a4=>l_end_date_time -- IN end_date_time
247 ,p10_a17=>c1_rec.classification -- IN Classification
248 ,p10_a12=>c1_rec.attribute2 -- IN media_abandon_flag
249 ,p10_a13=>c1_rec.attribute1 -- IN media_transferred_flag
250 ,x_return_status=>l_return_status
251 ,x_msg_count=>l_msg_count
252 ,x_msg_data=>l_msg_data );
253
254 -- fnd_file.put_line(fnd_file.log,'Closed mi');
255 -- dbms_output.put_line('Closed mi');
256
257 delete from cct_media_items where media_item_id = c1_rec.media_item_id;
258
259 -- fnd_file.put_line(fnd_file.log,'deleted mi');
260 -- dbms_output.put_line('deleted mi');
261 exception
262 when media_item_at_route_point_ex then
263 begin
264 null;
265 -- fnd_file.put_line(fnd_file.log,'media item is at route point, cannot close.');
266 -- dbms_output.put_line('media item is at route point, cannot close.');
267 end;
268 when no_data_found_ex then
269 begin
270 null;
271 -- fnd_file.put_line(fnd_file.log,'no data found for media item ');
272 -- dbms_output.put_line('no data found for media item ');
273 end;
274 when others then
275 begin
276 null;
277 -- fnd_file.put_line(fnd_file.log,'Others exception for media item ');
278 -- dbms_output.put_line('Others exception for media item ');
279 end;
280 end;
281 end loop;
282
283
284 end;
285
286 -- fnd_file.put_line(fnd_file.log, 'Worker Program Ended ');
287 -- End of API body.
288 -- Standard check of p_commit.
289 IF FND_API.To_Boolean( p_commit ) THEN
290 COMMIT WORK;
291 END IF;
292 -- Standard call to get message count and if count is 1, get message info.
293 FND_MSG_PUB.Count_And_Get
294 ( p_count => l_msg_count ,
295 p_data => ERRBUF
296 );
297 EXCEPTION
298
299 WHEN FND_API.G_EXC_ERROR THEN
300 ROLLBACK TO CLOSE_MEDIA_ITEMS_PUB;
301 RETCODE := FND_API.G_RET_STS_ERROR ;
302 FND_MSG_PUB.Count_And_Get
303 ( p_count => l_msg_count ,
304 p_data => ERRBUF
305 );
306 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
307 ROLLBACK TO CLOSE_MEDIA_ITEMS_PUB;
308 RETCODE := FND_API.G_RET_STS_UNEXP_ERROR ;
309 FND_MSG_PUB.Count_And_Get
310 ( p_count => l_msg_count ,
311 p_data => ERRBUF
312 );
313 WHEN OTHERS THEN
314 ROLLBACK TO CLOSE_MEDIA_ITEMS_PUB;
315 RETCODE := FND_API.G_RET_STS_UNEXP_ERROR ;
316 IF FND_MSG_PUB.Check_Msg_Level
317 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
318 THEN
319 FND_MSG_PUB.Add_Exc_Msg
320 ( G_PKG_NAME ,
321 l_api_name
322 );
323 END IF;
324 FND_MSG_PUB.Count_And_Get
325 ( p_count => l_msg_count ,
326 p_data => ERRBUF
327 );
328
329 END CLOSE_MEDIA_ITEMS;
330
331
332
333
334 PROCEDURE TIMEOUT_PROCESS(
335 ERRBUF OUT NOCOPY VARCHAR2,
336 RETCODE OUT NOCOPY VARCHAR2,
337 p_timeout_interval IN NUMBER,
341 l_submit_request_id NUMBER;
338 p_check_timeout_interval IN NUMBER
339 )
340 IS
342 l_is_repeat_options_set BOOLEAN;
343 error_msg VARCHAR2(256);
344 l_return_value BOOLEAN;
345 REPEAT_OPTIONS_NOT_SET EXCEPTION;
346 REQUEST_NOT_SUBMITTED EXCEPTION;
347
348 BEGIN
349
350 -- fnd_file.put_line(fnd_file.log, 'Starting TIMEOUT Processing');
351 -- fnd_file.put_line(fnd_file.log, 'p_timeout_interval = ' || to_char(p_timeout_interval));
352 -- fnd_file.put_line(fnd_file.log, 'p_check_timeout_interval = ' || to_char(p_check_timeout_interval));
353 l_is_repeat_options_set := fnd_request.set_repeat_options(
354 repeat_interval => p_check_timeout_interval,
355 repeat_unit => 'HOURS',
356 repeat_type => 'START');
357 if not l_is_repeat_options_set then
358 rollback;
359 raise REPEAT_OPTIONS_NOT_SET;
360 end if;
361
362 l_submit_request_id := fnd_request.submit_request(
363 application=>'CCT',
364 program => 'CCT_TIMEOUT_MEDIA_ITEMS',
365 description => 'Concurrent program to timeout IH media items',
366 argument1 => '1.0',
367 argument2 => FND_API.G_FALSE,
368 argument3 => FND_API.G_TRUE,
369 argument4 => p_timeout_interval);
370
371 -- fnd_file.put_line(fnd_file.log, 'Request Id ' || to_char(l_submit_request_id));
372
373 if l_submit_request_id = 0 then
374 rollback;
375 raise REQUEST_NOT_SUBMITTED;
376 else
377 commit;
378 end if;
379 -- fnd_file.put_line(fnd_file.log, 'Timeout Controller Exited');
380
381 EXCEPTION
382 WHEN REPEAT_OPTIONS_NOT_SET THEN
383 FND_MESSAGE.SET_NAME('CCT','CCT_IH_REPEAT_OPTIONS_NOT_SET');
384 error_msg := FND_MESSAGE.GET;
385 fnd_file.put_line(fnd_file.log, error_msg);
386 l_return_value := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR', error_msg);
387
388 WHEN REQUEST_NOT_SUBMITTED THEN
389 FND_MESSAGE.SET_NAME('CCT','CCT_IH_REQUEST_NOT_SUBMITTED');
390 error_msg := FND_MESSAGE.GET;
391 fnd_file.put_line(fnd_file.log, error_msg);
392 l_return_value := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR', error_msg);
393
394 WHEN OTHERS THEN
395 FND_MESSAGE.SET_NAME('CCT','CCT_UNEXPTECTED');
396 error_msg := FND_MESSAGE.GET;
397 fnd_file.put_line(fnd_file.log, error_msg);
398 l_return_value := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR', error_msg);
399 END TIMEOUT_PROCESS;
400
401
402 PROCEDURE TIMEOUT_MEDIA_ITEMS_RS
403 (
404 ERRBUF OUT NOCOPY VARCHAR2,
405 RETCODE OUT NOCOPY VARCHAR2,
406 p_timeout_in_hrs IN NUMBER
407 )
408 IS
409 l_api_version CONSTANT NUMBER := 1.0;
410 BEGIN
411 TIMEOUT_MEDIA_ITEMS(
412 ERRBUF,
413 RETCODE,
414 l_api_version,
415 FND_API.G_FALSE,
416 FND_API.G_FALSE,
417 p_timeout_in_hrs
418 );
419 END TIMEOUT_MEDIA_ITEMS_RS;
420
421
422 PROCEDURE TIMEOUT_MEDIA_ITEMS
423 (
424 ERRBUF OUT NOCOPY VARCHAR2,
425 RETCODE OUT NOCOPY VARCHAR2,
426 p_api_version IN NUMBER,
427 p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE,
428 p_commit IN VARCHAR2 Default FND_API.G_FALSE,
429 p_timeout_in_hrs IN NUMBER
430 )
431 IS
432 l_api_name CONSTANT VARCHAR2(30) := 'TIMEOUT_MEDIA_ITEMS';
433 l_api_version CONSTANT NUMBER := 1.0;
434 l_return_status VARCHAR2(256);
435 l_msg_count NUMBER;
436 l_msg_data VARCHAR2(256);
437 l_duration NUMBER;
438 l_milcs_duration NUMBER;
439 l_timeout_in_minutes NUMBER;
440 l_end_date_time DATE ;
441 l_max_milcs_end_date_time DATE;
442 l_max_milcs_start_date_time DATE;
443 l_classification VARCHAR2(256);
444 l_attribute1 VARCHAR2(150);
445 l_attribute2 VARCHAR2(150);
446
447 BEGIN
448 -- Standard Start of API savepoint
449
450 -- fnd_file.put_line(fnd_file.log, 'Worker Program Started 1');
451
452 SAVEPOINT TIMEOUT_MEDIA_ITEMS_PUB;
453 -- Standard call to check for call compatibility.
454 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
455 p_api_version ,
456 l_api_name ,
457 G_PKG_NAME )
458
459 THEN
460 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
461 END IF;
462 -- Initialize message list if p_init_msg_list is set to TRUE.
463 IF FND_API.To_Boolean( p_init_msg_list ) THEN
464 FND_MSG_PUB.initialize;
465 END IF;
466 -- Initialize API return status to success
467 RETCODE := FND_API.G_RET_STS_SUCCESS;
468 -- API body
469
470
471 -- fnd_file.put_line(fnd_file.log, 'Timeout Worker Program Started 2');
472 ---- dbms_output.put_line('Timeout Worker Program Started 2');
473
474 l_timeout_in_minutes := 60*p_timeout_in_hrs;
475
476 declare cursor c1 is
477 select media_id, start_date_time from jtf_ih_media_items where active = 'Y' and media_id in
478 (select media_item_id from cct_media_items where media_type <> 1 and status NOT IN (1,2))
479 and start_date_time <= (sysdate - (l_timeout_in_minutes/1440));
480
481 begin
482 -- fnd_file.put_line(fnd_file.log,'Cursor declared');
483 -- dbms_output.put_line('Cursor declared');
484 for c1_rec in c1 loop
485
486 begin
487 -- fnd_file.put_line(fnd_file.log,'Processing media item id ' || c1_rec.media_id);
488 -- dbms_output.put_line('Processing media item id ' || c1_rec.media_id);
489
493 select max(c.end_date_time) into l_max_milcs_end_date_time from jtf_ih_media_item_lc_segs c
490 l_duration := 0;
491 l_end_date_time := c1_rec.start_date_time;
492
494 where c.media_id = c1_rec.media_id;
495 select max(c.start_date_time) into l_max_milcs_start_date_time from jtf_ih_media_item_lc_segs c
496 where c.media_id = c1_rec.media_id;
497
498 if (l_max_milcs_end_date_time is not null) then
499 -- dbms_output.put_line('there is at least 1 lcs segment for this media item');
500 if (l_max_milcs_end_date_time > l_max_milcs_start_date_time) then
501 l_end_date_time := l_max_milcs_end_date_time;
502 else
503 -- dbms_output.put_line('more than 1 lcs segment, last lcs segement was never ended.');
504 l_end_date_time := l_max_milcs_start_date_time;
505 end if;
506
507 else
508 -- dbms_output.put_line(' no ended segment, check if there is unended lcs segment');
509 if (l_max_milcs_start_date_time is not null) then
510 -- dbms_output.put_line('at least 1 unended lcs segment.');
511 l_end_date_time := l_max_milcs_start_date_time;
512 end if;
513 end if;
514
515 -- fnd_file.put_line(fnd_file.log,'l_end_date_time' || to_char(l_end_date_time));
516
517 -- dbms_output.put_line('l_end_date_time' || to_char(l_end_date_time));
518
519 l_duration := l_end_date_time - c1_rec.start_date_time;
520 l_duration := round(24*60*60*l_duration);
521
522 -- dbms_output.put_line('Before classify');
523 l_classification := 'unClassified';
524
525 select classification, attribute1, attribute2 into l_classification, l_attribute1, l_attribute2 from cct_media_items where media_item_id = c1_rec.media_id;
526
527
528 -- dbms_output.put_line('After classify');
529
530 -- fnd_file.put_line(fnd_file.log,'l_duration' || to_char(l_duration));
531 -- dbms_output.put_line('l_duration' || to_char(l_duration));
532 declare cursor c2 is
533 select milcs_id, start_date_time from jtf_ih_media_item_lc_segs where active = 'Y'
534 and media_id = c1_rec.media_id;
535 begin
536 for c2_rec in c2 loop
537 begin
538
539 l_milcs_duration := l_end_date_time - c2_rec.start_date_time;
540 -- dbms_output.put_line('l_milcs_duration' || to_char(l_milcs_duration));
541 JTF_IH_PUB_W.Update_MediaLifecycle
542 (p_api_version=>1.0
543 ,p_init_msg_list=>FND_API.G_FALSE
544 ,p_commit=>FND_API.G_TRUE
545 ,p_resp_appl_id=>1 -- IN RESP APPL ID
546 ,p_resp_id=>1 -- IN RESP ID
547 ,p_user_id=>FND_GLOBAL.USER_ID -- IN USER ID
548 ,p_login_id=>NULL -- IN LOGIN ID
549 ,p10_a3=>l_milcs_duration -- IN duration
550 ,p10_a4=>l_end_date_time -- IN end date time
551 ,p10_a5=>c2_rec.milcs_id -- IN milcs id
552 ,p10_a7=>c1_rec.media_id -- IN media id
553 ,p10_a8=>CCT_IH_PUB.G_IH_CCT_HANDLER_ID -- IN handler id
554 ,x_return_status=>l_return_status
555 ,x_msg_count=>l_msg_count
556 ,x_msg_data=>l_msg_data );
557
558 exception
559 when others then
560 begin
561 null;
562 -- fnd_file.put_line(fnd_file.log,'Others exception for abandoned media item ');
563 end;
564 end ;
565 end loop;
566 end;
567
568 JTF_IH_PUB_W.CLOSE_MEDIAITEM
569 (p_api_version=>1.0 -- IN api version
570 ,p_init_msg_list=>p_init_msg_list -- IN init msg list
571 ,p_commit=>p_commit -- IN commit
572 ,p_resp_appl_id=>1 -- IN resp appl id
573 ,p_resp_id=>1 -- IN resp id
574 ,p_user_id=>FND_GLOBAL.USER_ID -- IN user id
575 ,p_login_id=>NULL -- IN login id
576 ,p10_a0=>c1_rec.media_id -- IN media_id
577 ,p10_a3=>l_duration -- IN duration
578 ,p10_a4=>l_end_date_time -- IN end_date_time
579 ,p10_a17=>l_classification -- IN Classification
580 ,p10_a12=>l_attribute2 -- IN media_abandon_flag
581 ,p10_a13=>l_attribute1 -- IN media_transferred_flag
582 ,x_return_status=>l_return_status
583 ,x_msg_count=>l_msg_count
584 ,x_msg_data=>l_msg_data );
585
586 -- fnd_file.put_line(fnd_file.log,'Closed mi');
587 -- dbms_output.put_line('Closed mi');
588 delete from cct_media_items where media_item_id = c1_rec.media_id;
589 -- fnd_file.put_line(fnd_file.log,'deleted mi');
590 -- dbms_output.put_line('deleted mi');
591
592 exception
593 when others then
594 begin
595 null;
596 -- fnd_file.put_line(fnd_file.log,'Others exception for media item ');
597 -- dbms_output.put_line('Others exception for media item ');
598 end;
599 end;
600 end loop;
601
602 -- force delete remainder media items that are in cct_media_items but are inactive in jtf_ih_media_items.
603
604 delete from CCT_MEDIA_ITEMS where
605 media_type <> 1 and status NOT IN (1,2) and creation_date <= (sysdate - (l_timeout_in_minutes/1440)) ;
606
607 end;
608
609 -- fnd_file.put_line(fnd_file.log, 'Worker Program Ended ');
610 -- End of API body.
611 -- Standard check of p_commit.
612 IF FND_API.To_Boolean( p_commit ) THEN
613 COMMIT WORK;
614 END IF;
618 p_data => ERRBUF
615 -- Standard call to get message count and if count is 1, get message info.
616 FND_MSG_PUB.Count_And_Get
617 ( p_count => l_msg_count ,
619 );
620 EXCEPTION
621
622 WHEN FND_API.G_EXC_ERROR THEN
623 ROLLBACK TO TIMEOUT_MEDIA_ITEMS_PUB;
624 RETCODE := FND_API.G_RET_STS_ERROR ;
625 FND_MSG_PUB.Count_And_Get
626 ( p_count => l_msg_count ,
627 p_data => ERRBUF
628 );
629 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
630 ROLLBACK TO TIMEOUT_MEDIA_ITEMS_PUB;
631 RETCODE := FND_API.G_RET_STS_UNEXP_ERROR ;
632 FND_MSG_PUB.Count_And_Get
633 ( p_count => l_msg_count ,
634 p_data => ERRBUF
635 );
636 WHEN OTHERS THEN
637 ROLLBACK TO TIMEOUT_MEDIA_ITEMS_PUB;
638 RETCODE := FND_API.G_RET_STS_UNEXP_ERROR ;
639 IF FND_MSG_PUB.Check_Msg_Level
640 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
641 THEN
642 FND_MSG_PUB.Add_Exc_Msg
643 ( G_PKG_NAME ,
644 l_api_name
645 );
646 END IF;
647 FND_MSG_PUB.Count_And_Get
648 ( p_count => l_msg_count ,
649 p_data => ERRBUF
650 );
651
652 END TIMEOUT_MEDIA_ITEMS;
653
654 END CCT_CONCURRENT_PUB;
655
[Home] [Help]