[Home] [Help]
PACKAGE BODY: APPS.IEC_EXECOCS_PVT
Source
1 PACKAGE BODY IEC_EXECOCS_PVT AS
2 /* $Header: IECOCEXB.pls 115.25.1158.3 2002/10/02 18:25:56 lcrew ship $ */
3
4 g_pkg_name CONSTANT VARCHAR2(30) := 'IEC_SVR_UTIL_PVT';
5
6 -----------------------------++++++-------------------------------
7 --
8 -- Start of comments
9 --
10 -- API name : UPDATE_LIST_STATUS
11 -- Type : Public
12 -- Pre-reqs : None
13 -- Function : Makes call to AMS_LISTHEADER_PVT.UpdateListheader to change the value of the
14 -- particular list's status value.
15 -- Parameters : P_LIST_HEADER_ID IN NUMBER Required
16 -- P_STATUS IN VARCHAR2 Required
17 -- X_RETURN_CODE OUT VARCHAR2 Required
18 --
19 -- Version : Initial version 1.0
20 --
21 -- End of comments
22 --
23 -----------------------------++++++-------------------------------
24 PROCEDURE UPDATE_LIST_STATUS
25 ( P_LIST_HEADER_ID IN NUMBER
26 , P_SOURCE_ID IN NUMBER
27 , P_STATUS IN NUMBER
28 , X_RETURN_CODE OUT VARCHAR2
29 )
30 IS
31 PRAGMA AUTONOMOUS_TRANSACTION;
32 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_LIST_STATUS';
33 l_list_rec AMS_LISTHEADER_PVT.list_header_rec_type;
34 l_api_version CONSTANT NUMBER := 1.0;
35 l_msg_count NUMBER;
36 l_msg_data VARCHAR2(4000);
37 l_source_id IEO_LNA_SOURCES.SOURCE_ID%TYPE;
38 l_record_id IEO_LNA_RECORDS.RECORD_ID%TYPE;
39 l_desc_arr IEC_SQL_LOGGER_PVT.VARCHAR2_TABLE;
40 l_desc_app IEC_SQL_LOGGER_PVT.VARCHAR2_TABLE;
41 l_desc_pos_arr IEC_SQL_LOGGER_PVT.NUMBER_TABLE;
42 l_param_msg_name_arr IEC_SQL_LOGGER_PVT.VARCHAR2_TABLE;
43 l_param_msg_app IEC_SQL_LOGGER_PVT.VARCHAR2_TABLE;
44 l_parm_value_arr IEC_SQL_LOGGER_PVT.VARCHAR2_TABLE;
45 l_parm_value_type_arr IEC_SQL_LOGGER_PVT.NUMBER_TABLE;
46
47 BEGIN
48 X_RETURN_CODE := 'S';
49
50 AMS_LISTHEADER_PVT.Init_ListHeader_rec(x_listheader_rec => l_list_rec);
51
52 l_list_rec.list_header_id := P_LIST_HEADER_ID ;
53 l_list_rec.user_status_id := P_STATUS ;
54
55 AMS_LISTHEADER_PVT.Update_ListHeader
56 ( p_api_version => l_api_version,
57 p_init_msg_list => FND_API.G_FALSE,
58 p_commit => FND_API.G_FALSE,
59 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
60 x_return_status => X_RETURN_CODE,
61 x_msg_count => l_msg_count,
62 x_msg_data => l_msg_data ,
63 p_listheader_rec => l_list_rec
64 );
65
66 -- If any errors happen abort API.
67 IF X_RETURN_CODE <> FND_API.G_RET_STS_SUCCESS THEN
68 l_msg_count := FND_MSG_PUB.count_msg;
69 IEC_SQL_LOGGER_PVT.log( P_SOURCE_ID
70 , IEC_SQL_LOGGER_PVT.G_TL_INFO
71 , SYSDATE
72 , 0
73 , IEC_SQL_LOGGER_PVT.G_ALERT_NONE
74 , IEC_SQL_LOGGER_PVT.G_TL_DEBUG
75 , 'IEC_COMM_TRACE'
76 , 'IEC'
77 , ''
78 , l_record_id);
79 FOR i IN 1..FND_MSG_PUB.count_msg LOOP
80 l_msg_data := FND_MSG_PUB.GET(i, FND_API.G_FALSE);
81 l_desc_arr(1) := 'IEC_COMM_TRACE_DESC';
82 l_desc_app(1) := 'IEC';
83 IEC_SQL_LOGGER_PVT.LOG_DESCRIPTION( l_record_id, l_desc_arr, l_desc_app);
84 l_desc_pos_arr(1) := 1;
85 l_param_msg_name_arr(1) := 'IEC_TRACE_TOKEN';
86 l_param_msg_app(1) := 'IEC';
87 l_parm_value_arr(1) := l_msg_data;
88 l_parm_value_type_arr(1) := 1;
89 IEC_SQL_LOGGER_PVT.DESCRIPTION_PARAMS( l_record_id
90 , l_desc_pos_arr
91 , l_param_msg_name_arr
92 , l_param_msg_app
93 , l_parm_value_arr
94 , l_parm_value_type_arr);
95 END LOOP;
96 END IF;
97
98 IF X_RETURN_CODE = FND_API.G_RET_STS_ERROR THEN
99 RAISE FND_API.G_EXC_ERROR;
100 ELSIF X_RETURN_CODE = FND_API.G_RET_STS_UNEXP_ERROR THEN
101 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
102 END IF;
103 COMMIT;
104
105 EXCEPTION
106 WHEN FND_API.G_EXC_ERROR THEN
107 ROLLBACK;
108 X_RETURN_CODE := 'E';
109 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
110 ROLLBACK;
111 X_RETURN_CODE := 'E';
112 WHEN OTHERS THEN
113 ROLLBACK;
114 X_RETURN_CODE := 'U';
115 END UPDATE_LIST_STATUS;
116
117 -----------------------------++++++-------------------------------
118 --
119 -- Start of comments
120 --
121 -- API name : REFRESH_ENTRIES
122 -- Type : Public
123 -- Pre-reqs : None
124 -- Function : Refresh entries that have been checked out of AMS_LIST_ENTRIES for longer
125 -- than the time sent in as P_STALE_INTERVAL.
126 -- Parameters : P_LOST_INTERVAL IN NUMBER Required
127 -- X_RETURN_CODE OUT VARCHAR2 Required
128 --
129 -- Version : Initial version 1.0
130 --
131 -- End of comments
132 --
133 -----------------------------++++++-------------------------------
134 /* Called by the Recover Plugin. */
135 PROCEDURE REFRESH_ENTRIES
136 ( P_SOURCE_ID IN NUMBER
137 , P_STALE_INTERVAL IN NUMBER
138 , X_RETURN_CODE OUT VARCHAR2
139 ) AS LANGUAGE JAVA NAME 'oracle.apps.iec.storedproc.ocs.common.IecExecocsPvt.refreshStaleEntries(long, long, java.lang.String[])';
140
141
142 -----------------------------++++++-------------------------------
143 --
144 -- Start of comments
145 --
146 -- API name : RECOVER_ENTRIES
147 -- Type : Public
148 -- Pre-reqs : None
149 -- Function : Recover entries that have been checked out of AMS_LIST_ENTRIES for longer
150 -- than the time sent in as P_LOST_INTERVAL.
151 -- Parameters : P_LOST_INTERVAL IN NUMBER Required
152 -- X_RETURN_CODE OUT VARCHAR2 Required
153 --
154 -- Version : Initial version 1.0
155 --
156 -- End of comments
157 --
158 -----------------------------++++++-------------------------------
159 /* Called by the Recover Plugin. */
160 PROCEDURE RECOVER_ENTRIES
161 ( P_SOURCE_ID IN NUMBER
162 , P_LOST_INTERVAL IN NUMBER
163 , X_RETURN_CODE OUT VARCHAR2
164 ) AS LANGUAGE JAVA NAME 'oracle.apps.iec.storedproc.ocs.common.IecExecocsPvt.recoverLostEntries(long, long, java.lang.String[])';
165
166 -----------------------------++++++-------------------------------
167 --
168 -- Start of comments
169 --
170 -- API name : HANDLE_CALLBACKS
171 -- Type : Public
172 -- Pre-reqs : None
173 -- Function :
174 -- Parameters : P_SOURCE_ID IN NUMBER Required
175 -- X_RETURN_CODE OUT VARCHAR2 Required
176 --
177 -- Version : Initial version 1.0
178 --
179 -- End of comments
180 --
181 -----------------------------++++++-------------------------------
182 /* Called by the Callback Plugin. */
183 PROCEDURE HANDLE_CALLBACKS
184 ( P_SOURCE_ID IN NUMBER
185 , P_SCHED_ID IN NUMBER
186 , X_RETURN_CODE OUT VARCHAR2
187 ) AS LANGUAGE JAVA NAME 'oracle.apps.iec.storedproc.ocs.common.IecExecocsPvt.handleCallbacks(long, long, java.lang.String[])';
188
189 -----------------------------++++++-------------------------------
190 --
191 -- Start of comments
192 --
193 -- API name : HANDLE_STATUS_TRANSITIONS
194 -- Type : Public
195 -- Pre-reqs : None
196 -- Function :
197 -- Parameters : P_SOURCE_ID IN NUMBER Required
198 -- P_SERVER_ID IN NUMBER Required
199 -- X_RETURN_CODE OUT VARCHAR2 Required
200 --
201 -- Version : Initial version 1.0
202 --
203 -- End of comments
204 --
205 -----------------------------++++++-------------------------------
206 /* Called by the Status Plugin. */
207 PROCEDURE HANDLE_STATUS_TRANSITIONS
208 ( P_SOURCE_ID IN NUMBER
209 , P_SERVER_ID IN NUMBER
210 , X_RETURN_CODE OUT VARCHAR2
211 ) AS LANGUAGE JAVA NAME 'oracle.apps.iec.storedproc.ocs.common.IecExecocsPvt.handleStatusTransitions(long, long, java.lang.String[])';
212
213 -----------------------------++++++-------------------------------
214 --
215 -- Start of comments
216 --
217 -- API name : POPULATE_CACHE
218 -- Type : Public
219 -- Pre-reqs : None
220 -- Function :
221 -- Parameters : P_SOURCE_ID IN NUMBER Required
222 -- P_SCHEDULE_ID IN NUMBER Required
223 -- P_LOW_THRESH_PCT IN NUMBER Required
224 -- P_HIGH_THRESH_PCT IN NUMBER Required
225 -- P_LIST_INCREASE_PCT IN NUMBER Required
226 -- P_INIT_CACHE_PCT IN NUMBER Required
227 -- X_RETURN_CODE OUT VARCHAR2 Required
228 --
229 -- Version : Initial version 1.0
230 --
231 -- End of comments
232 --
233 -----------------------------++++++-------------------------------
234 /* Called by the Retrieve Plugin. */
235 PROCEDURE POPULATE_CACHE
236 ( P_SOURCE_ID IN NUMBER
237 , P_SCHEDULE_ID IN NUMBER
238 , P_LOW_THRESH_PCT IN NUMBER
239 , P_HIGH_THRESH_PCT IN NUMBER
240 , P_LIST_INCREASE_PCT IN NUMBER
241 , P_INIT_CACHE_PCT IN NUMBER
242 , X_RETURN_CODE OUT VARCHAR2
243 ) AS LANGUAGE JAVA NAME 'oracle.apps.iec.storedproc.ocs.common.IecExecocsPvt.populateCache(long, long, int, int, int, int, java.lang.String[])';
244
245 -----------------------------++++++-------------------------------
246 --
247 -- Start of comments
248 --
249 -- API name : RECYCLE_ENTRIES
250 -- Type : Public
251 -- Pre-reqs : None
252 -- Function : Called by the Recycle plugin to recycle entries in IEC_G_RETURNS.
253 -- Parameters : X_RETURN_CODE OUT VARCHAR2 Required
254 --
255 -- Version : Initial version 1.0
256 --
257 -- End of comments
258 --
259 -----------------------------++++++-------------------------------
260 /* Called by the Recycle Plugin. */
261 PROCEDURE RECYCLE_ENTRIES
262 ( P_SOURCE_ID IN NUMBER
263 , X_RETURN_CODE OUT VARCHAR2
264 )
265 AS LANGUAGE JAVA NAME 'oracle.apps.iec.storedproc.algorithms.AlgWrapSPUJ.recycleEntries(int, java.lang.String [])';
266
267
268 -----------------------------++++++-------------------------------
269 --
270 -- Start of comments
271 --
272 -- API name : CALL_IH
273 -- Type : Public
274 -- Pre-reqs : None
275 -- Function : Called by the Recycle process to record the IH related information for Advanced Outbound Predicitive dialing.
276 -- Parameters : X_RETURN_CODE OUT VARCHAR2 Required
277 --
278 -- Version : Initial version 1.0
279 --
280 -- End of comments
281 --
282 -----------------------------++++++-------------------------------
283 /* Called by the Recycle Process to record the IH related information. */
284 PROCEDURE CALL_IH
285 ( P_PARTY_ID IN NUMBER
286 , P_START_TIME IN DATE
287 , P_END_TIME IN DATE
288 , P_OUTCOME_ID IN NUMBER
289 , P_REASON_ID IN NUMBER
290 , P_RESULT_ID IN NUMBER
291 , P_ACTION_ITEM_ID IN NUMBER
292 ,X_RETURN_STATUS OUT VARCHAR2
293 )
294 IS
295 PRAGMA AUTONOMOUS_TRANSACTION;
296 l_api_version CONSTANT NUMBER := 1.0;
297 l_init_msg_list VARCHAR2(1) :=FND_API.G_TRUE;
298 l_commit VARCHAR2(1) :=FND_API.G_TRUE;
299 l_user_id NUMBER := 0;
300 l_media_id NUMBER;
301 l_return_status VARCHAR2(1);
302 l_msg_count NUMBER;
303 l_msg_data VARCHAR2(2000);
304 l_null CHAR(1);
305 BEGIN
306 -- DBMS_OUTPUT.PUT_LINE('----begin create interaction-----0');
307 IF(L_INITIAL = 0 ) THEN
308 l_interaction_rec.interaction_id := NULL;
309 l_interaction_rec.reference_form := NULL;
310 l_interaction_rec.follow_up_action := NULL;
311 l_interaction_rec.inter_interaction_duration := NULL;
312 l_interaction_rec.non_productive_time_amount := NULL;
313 l_interaction_rec.preview_time_amount := NULL;
314 l_interaction_rec.productive_time_amount := NULL;
315 l_interaction_rec.wrapUp_time_amount := NULL;
316 l_interaction_rec.handler_id := 545;
317 l_interaction_rec.script_id := NULL;
318
319 --- temp set resource_id
320 l_interaction_rec.resource_id := 1;
321
322 l_interaction_rec.parent_id := NULL;
323 l_interaction_rec.object_id := NULL;
327 l_interaction_rec.attribute1 := NULL;
324 l_interaction_rec.object_type := NULL;
325 l_interaction_rec.source_code_id := NULL;
326 l_interaction_rec.source_code := NULL;
328 l_interaction_rec.attribute2 := NULL;
329 l_interaction_rec.attribute3 := NULL;
330 l_interaction_rec.attribute4 := NULL;
331 l_interaction_rec.attribute5 := NULL;
332 l_interaction_rec.attribute6 := NULL;
333 l_interaction_rec.attribute7 := NULL;
334 l_interaction_rec.attribute8 := NULL;
335 l_interaction_rec.attribute9 := NULL;
336 l_interaction_rec.attribute10 := NULL;
337 l_interaction_rec.attribute11 := NULL;
338 l_interaction_rec.attribute12 := NULL;
339 l_interaction_rec.attribute13 := NULL;
340 l_interaction_rec.attribute14 := NULL;
341 l_interaction_rec.attribute15 := NULL;
342 l_interaction_rec.attribute_category := NULL;
343
344 l_activities_tbl(1).activity_id := NULL;
345 l_activities_tbl(1).cust_account_id := NULL;
346 l_activities_tbl(1).cust_org_id := NULL;
347 l_activities_tbl(1).role := NULL;
348 l_activities_tbl(1).task_id := NULL;
349 l_activities_tbl(1).doc_id := NULL;
350 l_activities_tbl(1).doc_ref := NULL;
351 l_activities_tbl(1).doc_source_object_name := NULL;
352 l_activities_tbl(1).media_id := NULL;
353 l_activities_tbl(1).interaction_id := NULL;
354 l_activities_tbl(1).description := NULL;
355 l_activities_tbl(1).action_id := NULL;
356 l_activities_tbl(1).interaction_action_type := NULL;
357 l_activities_tbl(1).object_id := NULL;
358 l_activities_tbl(1).object_type := NULL;
359 l_activities_tbl(1).source_code_id := NULL;
360 l_activities_tbl(1).source_code := NULL;
361 l_activities_tbl(1).script_trans_id := NULL;
362
363 l_media_rec.media_id := NULL;
364 l_media_rec.source_id := NULL;
365 l_media_rec.direction := 'OUTBOUND';
366 l_media_rec.interaction_performed := NULL;
367 l_media_rec.media_data := NULL;
368 l_media_rec.source_item_create_date_time := NULL;
369 l_media_rec.source_item_id := NULL;
370 l_media_rec.media_item_type := 'TELEPHONE';
371 l_media_rec.media_item_ref := NULL;
372 l_media_rec.media_abandon_flag := NULL;
373 l_media_rec.media_transferred_flag := NULL;
374
375 l_media_lc_rec.type_type := NULL;
376 l_media_lc_rec.type_id := NULL;
377 l_media_lc_rec.milcs_id := NULL;
378
379 --- temp set milcs_type_id
380 l_media_lc_rec.milcs_type_id := 10015;
381
382 l_media_lc_rec.handler_id := 545;
383
384 --- temp set resource_id
385 l_media_lc_rec.resource_id := 1;
386
387 l_media_lc_rec.milcs_code := NULL;
388
389 L_INITIAL := 1;
390 END IF;
391
392 l_interaction_rec.duration := ROUND((p_end_time - p_start_time) *24*60);
393 l_interaction_rec.end_date_time := p_end_time;
394 l_interaction_rec.start_date_time := p_start_time;
395 l_interaction_rec.outcome_id := p_outcome_id;
396 l_interaction_rec.result_id := p_result_id;
397 l_interaction_rec.reason_id := p_reason_id;
398
399 l_interaction_rec.party_id := p_party_id;
400 l_activities_tbl(1).duration := ROUND((p_end_time - p_start_time) *24*60);
401 l_activities_tbl(1).end_date_time := p_end_time;
402 l_activities_tbl(1).start_date_time := p_start_time;
403 l_activities_tbl(1).action_item_id := p_action_item_id;
404 l_activities_tbl(1).outcome_id := p_outcome_id;
405 l_activities_tbl(1).result_id := p_result_id;
406 l_activities_tbl(1).reason_id := p_reason_id;
407 -- DBMS_OUTPUT.PUT_LINE('----before call JTF_IH_PUB create interaction-----');
408
409 JTF_IH_PUB.Create_Interaction(
410 p_api_version=>l_api_version,
411 p_init_msg_list=>l_init_msg_list,
412 p_commit=>l_commit,
413 p_user_id=>l_user_id,
414 x_return_status=>l_return_status,
415 x_msg_count=>l_msg_count,
416 x_msg_data=>l_msg_data,
417 p_interaction_rec=>l_interaction_rec,
418 p_activities=>l_activities_tbl);
419 x_return_status := l_return_status;
420
421 -- DBMS_OUTPUT.PUT_LINE('----end create interaction----- with x_return_status '||l_return_status);
422 -- DBMS_OUTPUT.PUT_LINE('----end create interaction----- with x_msg_data '||l_msg_data);
423
424 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
425 RAISE FND_API.G_EXC_ERROR;
426 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
427 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
428 ELSE
429 x_return_status := l_return_status;
430 END IF;
431 -- DBMS_OUTPUT.PUT_LINE('----begin create MediaItem-----');
432
433 l_media_rec.duration := ROUND((p_end_time - p_start_time) *24*60);
434 l_media_rec.end_date_time := p_end_time;
435 l_media_rec.start_date_time := p_start_time;
436
437 JTF_IH_PUB.Create_MediaItem(
438 p_api_version=>l_api_version,
439 p_init_msg_list=>l_init_msg_list,
440 p_commit=>l_commit,
441 p_user_id=>l_user_id,
442 x_return_status=>l_return_status,
443 x_msg_count=>l_msg_count,
444 x_msg_data=>l_msg_data,
445 p_media_rec=>l_media_rec,
446 x_media_id=>l_media_id);
447 x_return_status := l_return_status;
451 RAISE FND_API.G_EXC_ERROR;
448 -- DBMS_OUTPUT.PUT_LINE('----end create MediaItem----- with x_return_status '||l_return_status);
449
450 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
452 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
453 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
454 ELSE
455 x_return_status := l_return_status;
456 END IF;
457 -- DBMS_OUTPUT.PUT_LINE('----begin Create_MediaLifecycle-----');
458 l_media_lc_rec.start_date_time := p_start_time;
459 l_media_lc_rec.duration := ROUND((p_end_time - p_start_time)*24*60);
460 l_media_lc_rec.end_date_time := p_end_time;
461 l_media_lc_rec.media_id := l_media_id;
462
463 JTF_IH_PUB.Create_MediaLifecycle(
464 p_api_version=>l_api_version,
465 p_init_msg_list=>l_init_msg_list,
466 p_commit=>l_commit,
467 p_user_id=>l_user_id,
468 x_return_status=>l_return_status,
469 x_msg_count=>l_msg_count,
470 x_msg_data=>l_msg_data,
471 p_media_lc_rec=>l_media_lc_rec);
472 x_return_status := l_return_status;
473 -- DBMS_OUTPUT.PUT_LINE('----end Create_MediaLifecycle----- with x_return_status '||l_return_status);
474
475 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
476 RAISE FND_API.G_EXC_ERROR;
477 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
478 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
479 ELSE
480 x_return_status := l_return_status;
481 END IF;
482 EXCEPTION
483 WHEN FND_API.G_EXC_ERROR THEN
484 ROLLBACK;
485 X_RETURN_STATUS := 'E';
486 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
487 ROLLBACK;
488 X_RETURN_STATUS := 'U';
489 WHEN OTHERS THEN
490 ROLLBACK;
491 X_RETURN_STATUS := 'E';
492 END CALL_IH;
493
494 /* Called by Recover plugin to remove uneccesary entries from cache. */
495 PROCEDURE REMOVE_OLD_ENTRIES
496 ( SCHED_ID IN NUMBER
497 , LIST_ID IN NUMBER
498 , SUBSET_ID IN NUMBER
499 , X_RETURN_STATUS OUT VARCHAR2
500 )
501 IS
502 PRAGMA AUTONOMOUS_TRANSACTION;
503 TYPE EntryList IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
504 deleteList EntryList;
505 maxRecordId NUMBER;
506 recordCount NUMBER := 0;
507 recordOutFlag VARCHAR2(1);
508 startTime1 CHAR(5);
509 endTime1 CHAR(5);
510 startTime2 CHAR(5);
511 endTime2 CHAR(5);
512 startTime3 CHAR(5);
513 endTime3 CHAR(5);
514 startTime4 CHAR(5);
515 endTime4 CHAR(5);
516
517 CURSOR entry_cursor(listId NUMBER, subsetId NUMBER, callbackFlag VARCHAR2) IS
518 SELECT A.LIST_ENTRY_ID, A.CACHE_RECORD_ID
519 FROM IEC_G_CACHE_RECORDS A
520 WHERE A.LIST_HEADER_ID = listId
521 AND A.SUBSET_ID = subsetId
522 AND A.CALLBACK_FLAG = callbackFlag
523 AND A.CACHE_RECORD_ID < (SELECT B.RECORD_CACHE_SEQ
524 FROM IEC_G_CACHE_MKTG_ITEMS B
525 WHERE B.LIST_HEADER_ID = A.LIST_HEADER_ID
526 AND B.SUBSET_ID = A.SUBSET_ID
527 AND B.CALLBACK_FLAG = A.CALLBACK_FLAG);
528 BEGIN
529 X_RETURN_STATUS := 'S';
530
531 SELECT TO_CHAR(SYSDATE, 'SSSSS') INTO startTime1 FROM DUAL;
532 startTime4 := startTime1;
533
534 -- Remove unnecessary non-callback cache entries.
535 FOR entry_rec IN entry_cursor(LIST_ID, SUBSET_ID, 'Y')
536 LOOP
537
538 -- If there exist a cache record with an entryid and listid
539 -- that has the check out flag set to N in the list entries table
540 -- then we can assume that this entry is no longer of any use.
541 BEGIN
542 SELECT /*+ INDEX (AMS_LIST_ENTRIES AMS_LIST_ENTRIES_U1) */ RECORD_OUT_FLAG
543 INTO recordOutFlag
544 FROM AMS_LIST_ENTRIES
545 WHERE LIST_ENTRY_ID = entry_rec.LIST_ENTRY_ID
546 AND LIST_HEADER_ID = LIST_ID;
547 EXCEPTION
548 -- Fixme add logic to handle if entry does not exist.
549 -- This should no happen.
550 WHEN OTHERS THEN
551 RAISE;
552 END;
553
554 IF (recordOutFlag = 'N')
555 THEN
556 recordCount := recordCount + 1;
557 deleteList(recordCount) := entry_rec.CACHE_RECORD_ID;
558 ELSE
559
560 -- If there exist a cache record with the same entryid and listid
561 -- that has a higher cache sequence then we can assume that this
562 -- entry is no longer of any use.
563 BEGIN
564 SELECT MAX(CACHE_RECORD_ID)
565 INTO maxRecordId
566 FROM IEC_G_CACHE_RECORDS
567 WHERE LIST_HEADER_ID = LIST_ID
568 AND LIST_ENTRY_ID = entry_rec.LIST_ENTRY_ID;
569 EXCEPTION
570 -- Fixme add logic to handle if entry does not exist.
571 -- This should no happen.
572 WHEN OTHERS THEN
573 RAISE;
574 END;
575
576 IF (maxRecordId > entry_rec.CACHE_RECORD_ID)
577 THEN
578 recordCount := recordCount + 1;
579 deleteList(recordCount) := entry_rec.CACHE_RECORD_ID;
580 END IF;
581
582 END IF;
583 END LOOP;
584 SELECT TO_CHAR(SYSDATE, 'SSSSS') INTO endTime1 FROM DUAL;
585
586 startTime2 := endTime1;
587
588 -- Remove unnecessary callback cache entries.
592 -- then we can assume that this entry is no longer of any use.
589 FOR entry_rec IN entry_cursor(LIST_ID, SUBSET_ID, 'N') LOOP
590 -- If there exist a cache record with an entryid and listid
591 -- that has the check out flag set to N in the list entries table
593 BEGIN
594 SELECT /*+ INDEX (AMS_LIST_ENTRIES AMS_LIST_ENTRIES_U1) */ RECORD_OUT_FLAG
595 INTO recordOutFlag
596 FROM AMS_LIST_ENTRIES
597 WHERE LIST_ENTRY_ID = entry_rec.LIST_ENTRY_ID
598 AND LIST_HEADER_ID = LIST_ID;
599 EXCEPTION
600 -- Fixme add logic to handle if entry does not exist.
601 -- This should no happen.
602 WHEN OTHERS THEN
603 RAISE;
604 END;
605
606 IF (recordOutFlag = 'N')
607 THEN
608 recordCount := recordCount + 1;
609 deleteList(recordCount) := entry_rec.CACHE_RECORD_ID;
610 ELSE
611
612 -- If there exist a cache record with the same entryid and listid
613 -- that has a higher cache sequence then we can assume that this
614 -- entry is no longer of any use.
615 BEGIN
616 SELECT MAX(CACHE_RECORD_ID)
617 INTO maxRecordId
618 FROM IEC_G_CACHE_RECORDS
619 WHERE LIST_HEADER_ID = LIST_ID
620 AND LIST_ENTRY_ID = entry_rec.LIST_ENTRY_ID;
621 EXCEPTION
622 -- Fixme add logic to handle if entry does not exist.
623 -- This should no happen.
624 WHEN OTHERS THEN
625 RAISE;
626 END;
627
628 IF (maxRecordId > entry_rec.CACHE_RECORD_ID)
629 THEN
630 recordCount := recordCount + 1;
631 deleteList(recordCount) := entry_rec.CACHE_RECORD_ID;
632 END IF;
633
634 END IF;
635 END LOOP;
636
637 SELECT TO_CHAR(SYSDATE, 'SSSSS') INTO endTime2 FROM DUAL;
638 startTime3 := endTime2;
639
640 FORALL j IN 1..recordCount
641 DELETE FROM IEC_G_CACHE_RECORDS WHERE CACHE_RECORD_ID = deleteList(j);
642
643 SELECT TO_CHAR(SYSDATE, 'SSSSS') INTO endTime3 FROM DUAL;
644
645 COMMIT;
646 SELECT TO_CHAR(SYSDATE, 'SSSSS') INTO endTime4 FROM DUAL;
647
648 -- DBMS_OUTPUT.PUT_LINE('Execution Time (secs)');
649 -- DBMS_OUTPUT.PUT_LINE('Number deleted: ' || TO_CHAR(recordCount));
650 -- DBMS_OUTPUT.PUT_LINE('Identifying non-callbacks: ' || TO_CHAR(endTime1 - startTime1));
651 -- DBMS_OUTPUT.PUT_LINE('Identifying callbacks: ' || TO_CHAR(endTime2 - startTime2));
652 -- DBMS_OUTPUT.PUT_LINE('Deleting: ' || TO_CHAR(endTime3 - startTime3));
653 -- DBMS_OUTPUT.PUT_LINE('Total Execution: ' || TO_CHAR(endTime4 - startTime4));
654
655 EXCEPTION
656 -- Fixme add logic to handle if entry does not exist.
657 -- This should no happen.
658 WHEN OTHERS THEN
659 ROLLBACK;
660 RAISE;
661
662 END REMOVE_OLD_ENTRIES;
663
664 /* Called by Recover plugin to remove uneccesary entries from cache. */
665 PROCEDURE INSERT_LIST_RETURNS_RECORDS
666 ( P_SCHED_ID IN NUMBER
667 , P_LIST_ID IN NUMBER
668 , P_VIEW_NAME IN VARCHAR2
669 , P_DIALING_METHOD IN VARCHAR2
670 , X_RETURN_STATUS OUT VARCHAR2
671 )
672 IS
673 L_RETURN_STATUS VARCHAR2(1) := 'S';
674 L_VIEW_NAME VARCHAR2(250);
675 L_ENTRY_COUNT NUMBER := 1000;
676 L_LAST_ENTRY_ID NUMBER := -1;
677 startTime1 CHAR(5);
678 endTime1 CHAR(5);
679
680 TYPE ListEntryTab IS TABLE OF NUMBER;
681 L_LIST_ENTRIES_TAB ListEntryTab;
682
683 CURSOR entry_cursor(listId NUMBER, lastEntrySeq NUMBER) IS
684 SELECT LIST_ENTRY_ID
685 FROM AMS_LIST_ENTRIES
686 WHERE LIST_HEADER_ID = listId
687 AND LIST_ENTRY_ID > lastEntrySeq
688 AND ROWNUM <= 1000
689 ORDER BY LIST_ENTRY_ID;
690 BEGIN
691 -- Create a savepoint so that we can rollback to this point.
692
693 X_RETURN_STATUS := 'S';
694
695 SAVEPOINT remove_old_entries;
696
697 SELECT TO_CHAR(SYSDATE, 'SSSSS') INTO startTime1 FROM DUAL;
698
699 WHILE (L_ENTRY_COUNT = 1000)
700 LOOP
701
702 OPEN entry_cursor( P_LIST_ID
703 , L_LAST_ENTRY_ID);
704
705 FETCH entry_cursor BULK COLLECT INTO L_LIST_ENTRIES_TAB;
706
707 FORALL j IN L_LIST_ENTRIES_TAB.FIRST..L_LIST_ENTRIES_TAB.LAST
708 INSERT INTO IEC_G_RETURN_ENTRIES
709 ( RETURNS_ID
710 , LIST_ENTRY_ID
711 , LIST_HEADER_ID
712 , SUBSET_ID
713 , OUTCOME_ID
714 , RESULT_ID
715 , REASON_ID
716 , CONTACT_POINT
717 , CONTACT_POINT_ID
718 , DELIVER_IH_FLAG
719 , CALL_TYPE
720 , CAMPAIGN_SCHEDULE_ID
721 , LIST_VIEW_NAME
722 , RECYCLE_FLAG
723 , CREATED_BY
724 , CREATION_DATE
725 , LAST_UPDATED_BY
726 , LAST_UPDATE_DATE
727 , LAST_UPDATE_LOGIN
728 )
729 VALUES
730 (
731 IEC_G_RETURN_ENTRIES_S.NEXTVAL
732 , L_LIST_ENTRIES_TAB(j)
733 , P_LIST_ID
734 , -1
735 , -1
736 , -1
737 , -1
738 , FND_API.G_MISS_CHAR
739 , 0
740 , 'N'
741 , P_DIALING_METHOD
742 , P_SCHED_ID
743 , P_VIEW_NAME
744 , 'N'
745 , nvl( FND_GLOBAL.user_id, -1 )
746 , SYSDATE
747 , nvl( FND_GLOBAL.conc_login_id, -1 )
748 , SYSDATE
749 , nvl( FND_GLOBAL.conc_login_id, -1 )
750 );
751
752 L_LAST_ENTRY_ID := L_LIST_ENTRIES_TAB(L_LIST_ENTRIES_TAB.LAST);
753
754 L_ENTRY_COUNT := L_LIST_ENTRIES_TAB.COUNT;
755
756 CLOSE entry_cursor;
757
758 COMMIT;
759
760 L_LIST_ENTRIES_TAB.DELETE;
761 END LOOP;
762
763 EXCEPTION
764 WHEN OTHERS THEN
765 ROLLBACK TO remove_old_entries;
766 DELETE FROM IEC_G_RETURN_ENTRIES
767 WHERE LIST_HEADER_ID = P_LIST_ID;
768 COMMIT;
769 X_RETURN_STATUS := 'E';
770
771 END INSERT_LIST_RETURNS_RECORDS;
772
773
774 -- PL/SQL Block
775 END IEC_EXECOCS_PVT;