[Home] [Help]
PACKAGE BODY: APPS.IEC_RETURNS_UTIL_PVT
Source
1 PACKAGE BODY IEC_RETURNS_UTIL_PVT AS
2 /* $Header: IECVRETB.pls 115.24 2004/05/11 13:35:32 jezhu ship $ */
3
4 -- Sub-Program Unit Declarations
5
6 PROCEDURE ADD_ENTRY
7 (P_LIST_ENTRY_ID IN NUMBER
8 ,P_LIST_HEADER_ID IN NUMBER
9 ,P_SUBSET_ID IN NUMBER
10 ,P_CPN_SCHEDULE_ID IN NUMBER
11 ,P_OUTCOME_ID IN NUMBER
12 ,P_REASON_ID IN NUMBER
13 ,P_RESULT_ID IN NUMBER
14 ,P_CONTACT_POINT IN VARCHAR2
15 ,P_CONTACT_POINT_ID IN NUMBER
16 ,P_CALL_START_TIME IN VARCHAR2
17 ,P_CALL_END_TIME IN VARCHAR2
18 ,P_NEXT_CALL_TIME IN VARCHAR2
19 ,P_CALL_TYPE IN VARCHAR2
20 ,P_DELIVER_IH_FLAG IN VARCHAR2
21 ,P_LIST_VIEW_NAME IN VARCHAR2
22 ,P_RECYCLE_FLAG IN VARCHAR2
23 ,X_RETURNS_ID OUT NOCOPY NUMBER
24 )
25 AS
26 BEGIN
27 ADD_ENTRY( P_LIST_ENTRY_ID
28 , P_LIST_HEADER_ID
29 , P_SUBSET_ID
30 , P_CPN_SCHEDULE_ID
31 , P_OUTCOME_ID
32 , P_REASON_ID
33 , P_RESULT_ID
34 , P_CONTACT_POINT
35 , P_CONTACT_POINT_ID
36 , to_date( P_CALL_START_TIME, 'YYYY-MM-DD HH24:MI:SS')
37 , to_date( P_CALL_END_TIME, 'YYYY-MM-DD HH24:MI:SS')
38 , to_date( P_NEXT_CALL_TIME, 'YYYY-MM-DD HH24:MI:SS')
39 , P_CALL_TYPE
40 , P_DELIVER_IH_FLAG
41 , P_LIST_VIEW_NAME
42 , P_RECYCLE_FLAG
43 , X_RETURNS_ID
44 );
45 EXCEPTION
46 WHEN OTHERS THEN
47 RAISE;
48
49 END ADD_ENTRY;
50
51 PROCEDURE ADD_ENTRY
52 (P_LIST_ENTRY_ID IN NUMBER
53 ,P_LIST_HEADER_ID IN NUMBER
54 ,P_SUBSET_ID IN NUMBER
55 ,P_CPN_SCHEDULE_ID IN NUMBER
56 ,P_OUTCOME_ID IN NUMBER
57 ,P_REASON_ID IN NUMBER
58 ,P_RESULT_ID IN NUMBER
59 ,P_CONTACT_POINT IN VARCHAR2
60 ,P_CONTACT_POINT_ID IN NUMBER
61 ,P_CALL_START_TIME IN DATE
62 ,P_CALL_END_TIME IN DATE
63 ,P_NEXT_CALL_TIME IN DATE
64 ,P_CALL_TYPE IN VARCHAR2
65 ,P_DELIVER_IH_FLAG IN VARCHAR2
66 ,P_LIST_VIEW_NAME IN VARCHAR2
67 ,P_RECYCLE_FLAG IN VARCHAR2
68 ,X_RETURNS_ID OUT NOCOPY NUMBER
69 )
70 AS
71 l_user_id NUMBER;
72 l_login_id NUMBER;
73 BEGIN
74
75 l_user_id := nvl( FND_GLOBAL.user_id, -1 );
76 l_login_id := nvl( FND_GLOBAL.conc_login_id, -1 );
77 IF( ( P_LIST_ENTRY_ID is null )
78 OR( P_LIST_HEADER_ID is null )
79 OR( P_CPN_SCHEDULE_ID is null ) )
80 THEN
81 raise_application_error
82 ( -20000
83 , 'P_LIST_ENTRY_ID , P_LIST_HEADER_ID, P_CPN_SCHEDULE_ID'
84 || ' cannot be null.'
85 || 'Values sent are list entry id (' || P_LIST_ENTRY_ID || ')'
86 || 'list header id (' || P_LIST_HEADER_ID || ')'
87 || 'campaign schedule id (' || P_CPN_SCHEDULE_ID || ')'
88 ,TRUE
89 );
90 END IF;
91
92 insert into IEC_G_RETURN_ENTRIES
93 ( RETURNS_ID
94 , LIST_ENTRY_ID
95 , LIST_HEADER_ID
96 , SUBSET_ID
97 , OUTCOME_ID
98 , RESULT_ID
99 , REASON_ID
100 , CONTACT_POINT
101 , CONTACT_POINT_ID
102 , CALL_START_TIME
103 , CALL_END_TIME
104 , NEXT_CALL_TIME
105 , DELIVER_IH_FLAG
106 , CALL_TYPE
107 , CAMPAIGN_SCHEDULE_ID
108 , LIST_VIEW_NAME
109 , RECYCLE_FLAG
110 , CREATED_BY
111 , CREATION_DATE
112 , LAST_UPDATED_BY
113 , LAST_UPDATE_DATE
114 , LAST_UPDATE_LOGIN )
115 values ( IEC_G_RETURN_ENTRIES_S.NEXTVAL
116 , P_LIST_ENTRY_ID
117 , P_LIST_HEADER_ID
118 , P_SUBSET_ID
119 , P_OUTCOME_ID
120 , P_RESULT_ID
121 , P_REASON_ID
122 , P_CONTACT_POINT
123 , P_CONTACT_POINT_ID
124 , to_date( P_CALL_START_TIME, 'YYYY-MM-DD HH24:MI:SS')
125 , to_date( P_CALL_END_TIME, 'YYYY-MM-DD HH24:MI:SS')
126 , to_date( P_NEXT_CALL_TIME, 'YYYY-MM-DD HH24:MI:SS')
127 , nvl(P_DELIVER_IH_FLAG, 'N')
128 , P_CALL_TYPE
129 , P_CPN_SCHEDULE_ID
130 , P_LIST_VIEW_NAME
131 , NVL( P_RECYCLE_FLAG, 'N' )
132 , l_user_id
133 , sysdate
134 , l_login_id
135 , sysdate
136 , l_login_id
137 ) returning RETURNS_ID into X_RETURNS_ID;
138
139 EXCEPTION
140 WHEN OTHERS THEN
141 RAISE;
142
143 END ADD_ENTRY;
144
145 -- Mainly used by AODS to return the entries.
146 PROCEDURE UPDATE_ENTRY
147 (P_RETURNS_ID IN NUMBER
148 ,P_SUBSET_ID IN NUMBER
149 ,P_CALL_START_TIME IN VARCHAR2
150 ,P_CALL_END_TIME IN VARCHAR2
151 ,P_NEXT_CALL_TIME IN VARCHAR2
152 ,P_OUTCOME_ID IN NUMBER
153 ,P_REASON_ID IN NUMBER
154 ,P_RESULT_ID IN NUMBER
155 ,P_DELIVER_IH_FLAG IN VARCHAR2
156 )
157 AS
158 l_user_id NUMBER;
159 l_callback_flag VARCHAR2(1);
160
161 BEGIN
162 l_user_id := nvl( FND_GLOBAL.user_id, -1 );
163 l_callback_flag := 'N';
164 IF( ( P_RETURNS_ID is null ) )
165 THEN
166 raise_application_error
167 ( -20000
168 , 'P_RETURNS_ID cannot be null.'
169 ,TRUE
170 );
171 END IF;
172
173 IF (P_NEXT_CALL_TIME IS NOT NULL)
174 THEN
175 l_callback_flag := 'Y';
176
177 END IF;
178
179 IF (P_OUTCOME_ID = 37 AND P_RESULT_ID = 11)
180 THEN
181
182 update IEC_G_RETURN_ENTRIES
183 set OUTCOME_ID = P_OUTCOME_ID
184 , RESULT_ID = P_RESULT_ID
185 , LAST_UPDATE_DATE = sysdate
186 , RECORD_OUT_FLAG = 'N'
187 where RETURNS_ID = P_RETURNS_ID;
188
189 ELSIF (P_OUTCOME_ID = 31 OR P_OUTCOME_ID = 38)
190 THEN
191
192 update IEC_G_RETURN_ENTRIES
193 set CALL_START_TIME = to_date(P_CALL_START_TIME, 'YYYY-MM-DD HH24:MI:SS')
194 , CALL_END_TIME = to_date(P_CALL_END_TIME, 'YYYY-MM-DD HH24:MI:SS')
195 , NEXT_CALL_TIME = to_date(P_NEXT_CALL_TIME, 'YYYY-MM-DD HH24:MI:SS')
196 , CALLBACK_FLAG = l_callback_flag
197 , OUTCOME_ID = P_OUTCOME_ID
198 , RESULT_ID = P_RESULT_ID
199 , REASON_ID = P_REASON_ID
200 , DELIVER_IH_FLAG = nvl(P_DELIVER_IH_FLAG, 'N')
201 , RECYCLE_FLAG = 'Y'
202 , LAST_UPDATED_BY = l_user_id
203 , LAST_UPDATE_DATE = sysdate
204 , RECORD_OUT_FLAG = 'Y'
205 where RETURNS_ID = P_RETURNS_ID;
206
207 ELSE
208 update IEC_G_RETURN_ENTRIES
209 set CALL_START_TIME = to_date(P_CALL_START_TIME, 'YYYY-MM-DD HH24:MI:SS')
210 , CALL_END_TIME = to_date(P_CALL_END_TIME, 'YYYY-MM-DD HH24:MI:SS')
211 , NEXT_CALL_TIME = to_date(P_NEXT_CALL_TIME, 'YYYY-MM-DD HH24:MI:SS')
212 , OUTCOME_ID = P_OUTCOME_ID
213 , RESULT_ID = P_RESULT_ID
214 , REASON_ID = P_REASON_ID
215 , DELIVER_IH_FLAG = nvl(P_DELIVER_IH_FLAG, 'N')
216 , RECYCLE_FLAG = 'Y'
217 , LAST_UPDATED_BY = l_user_id
218 , LAST_UPDATE_DATE = sysdate
219 , CALLBACK_FLAG = l_callback_flag
220 where RETURNS_ID = P_RETURNS_ID;
221
222 END IF;
223
224
225 EXCEPTION
226 WHEN OTHERS THEN
227 RAISE;
228
229
230 END UPDATE_ENTRY;
231
232
233 PROCEDURE UPDATE_ENTRY
234 (P_RETURNS_ID IN NUMBER
235 ,P_SUBSET_ID IN NUMBER
236 ,P_CALL_START_TIME IN DATE
237 ,P_CALL_END_TIME IN DATE
238 ,P_NEXT_CALL_TIME IN DATE
239 ,P_OUTCOME_ID IN NUMBER
240 ,P_REASON_ID IN NUMBER
241 ,P_RESULT_ID IN NUMBER
242 ,P_DELIVER_IH_FLAG IN VARCHAR2
243 )
244 AS
245
246 BEGIN
247 UPDATE_ENTRY( P_RETURNS_ID
248 , P_SUBSET_ID
249 , to_char( P_CALL_START_TIME, 'YYYY-MM-DD HH24:MI:SS')
250 , to_char( P_CALL_END_TIME, 'YYYY-MM-DD HH24:MI:SS')
251 , to_char( P_NEXT_CALL_TIME, 'YYYY-MM-DD HH24:MI:SS')
252 , P_OUTCOME_ID
253 , P_REASON_ID
254 , P_RESULT_ID
255 , P_DELIVER_IH_FLAG
256 );
257
258 EXCEPTION
259 WHEN OTHERS THEN
260 RAISE;
261
262 END UPDATE_ENTRY;
263
264 PROCEDURE UPDATE_ENTRY
265 (P_RETURNS_ID IN NUMBER
266 ,P_SUBSET_ID IN NUMBER
267 ,P_CALL_START_TIME IN DATE
268 ,P_CALL_END_TIME IN DATE
269 ,P_AGENT_RECYCLE_ACTION IN VARCHAR2
270 ,P_OUTCOME_ID IN NUMBER
271 ,P_REASON_ID IN NUMBER
272 ,P_RESULT_ID IN NUMBER
273 ,P_DELIVER_IH_FLAG IN VARCHAR2
274 )
275 AS
276 l_user_id NUMBER;
277
278 BEGIN
279 l_user_id := nvl( FND_GLOBAL.user_id, -1 );
280 IF( ( P_RETURNS_ID is null ) )
281 THEN
282 raise_application_error
283 ( -20000
284 , 'P_RETURNS_ID cannot be null.'
285 ,TRUE
286 );
287 END IF;
288
289 IF (P_OUTCOME_ID = 37 AND P_RESULT_ID = 11)
290 THEN
291
292 update IEC_G_RETURN_ENTRIES
293 set OUTCOME_ID = P_OUTCOME_ID
294 , RESULT_ID = P_RESULT_ID
295 , LAST_UPDATE_DATE = sysdate
296 , RECORD_OUT_FLAG = 'N'
297 where RETURNS_ID = P_RETURNS_ID;
298
299 ELSE
300
301 update IEC_G_RETURN_ENTRIES
302 set CALL_START_TIME = P_CALL_START_TIME
303 , CALL_END_TIME = P_CALL_END_TIME
304 , AGENT_RECYCLE_ACTION = P_AGENT_RECYCLE_ACTION
305 , OUTCOME_ID = P_OUTCOME_ID
306 , RESULT_ID = P_RESULT_ID
307 , REASON_ID = P_REASON_ID
308 , DELIVER_IH_FLAG = nvl(P_DELIVER_IH_FLAG, 'N')
309 , RECYCLE_FLAG = 'Y'
310 , LAST_UPDATED_BY = l_user_id
311 , LAST_UPDATE_DATE = sysdate
312 , RECORD_OUT_FLAG = 'Y'
313 where RETURNS_ID = P_RETURNS_ID;
314
315 END IF;
316
317
318 EXCEPTION
319 WHEN OTHERS THEN
320 RAISE;
321
322
323 END UPDATE_ENTRY;
324
325 -- used by OCS to update the subset_id
326 PROCEDURE UPDATE_ENTRY
327 (P_LIST_ENTRY_ID IN NUMBER
328 ,P_LIST_HEADER_ID IN NUMBER
329 ,P_CPN_SCHEDULE_ID IN NUMBER
330 ,P_SUBSET_ID IN NUMBER
331 ,X_RETURNS_ID OUT NOCOPY NUMBER
332 )
333 AS
334 l_user_id NUMBER;
335 BEGIN
336 l_user_id := nvl( FND_GLOBAL.user_id, -1 );
337
338 IF( ( P_LIST_ENTRY_ID is null )
339 OR( P_LIST_HEADER_ID is null )
340 OR( P_CPN_SCHEDULE_ID is null ) )
341 THEN
342 raise_application_error
343 ( -20000
344 , 'P_LIST_ENTRY_ID , P_LIST_HEADER_ID, P_CPN_SCHEDULE_ID'
345 || ' cannot be null.'
346 || 'Values sent are list entry id (' || P_LIST_ENTRY_ID || ')'
347 || 'list header id (' || P_LIST_HEADER_ID || ')'
348 || 'campaign schedule id (' || P_CPN_SCHEDULE_ID || ')'
349 ,TRUE
350 );
351 END IF;
352
353 update IEC_G_RETURN_ENTRIES
354 set RECYCLE_FLAG = 'N'
355 , LAST_UPDATED_BY = l_user_id
356 , LAST_UPDATE_DATE = sysdate
357 where LIST_ENTRY_ID = P_LIST_ENTRY_ID
358 and LIST_HEADER_ID = P_LIST_HEADER_ID
359 and CAMPAIGN_SCHEDULE_ID = P_CPN_SCHEDULE_ID
360 returning RETURNS_ID into X_RETURNS_ID;
361
362 EXCEPTION
363 WHEN OTHERS THEN
364 RAISE;
365
366 END UPDATE_ENTRY;
367
368 -- used by recycle to add call history
369 PROCEDURE ADD_CALL_HISTORY
370 (P_RETURNS_ID IN NUMBER
371 ,P_CONTACT_POINT IN VARCHAR2
372 ,P_OUTCOME_ID IN NUMBER
373 ,P_TIME IN DATE
374 )
375 AS
376 l_user_id NUMBER;
377 l_login_id NUMBER;
378 l_call_attempt NUMBER;
379 l_outcome_ids t_outcome;
380 l_times t_time;
381 BEGIN
382 l_user_id := nvl( FND_GLOBAL.user_id, -1 );
383 l_login_id := nvl( FND_GLOBAL.conc_login_id, -1 );
384 l_outcome_ids := t_outcome(null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null);
385 l_times := t_time(null,null,null,null,null,null,null,null,null,null,null,null,
386 null,null,null,null,null,null,null,null,null);
387 BEGIN
388 SELECT /*+ index(iec_o_rcy_call_histories iec_o_rcy_call_histories_n1)*/ call_attempt,outcome_id_0,outcome_id_1,outcome_id_2,outcome_id_3,
389 outcome_id_4,outcome_id_5,outcome_id_6,outcome_id_7,outcome_id_8,outcome_id_9,
390 outcome_id_10,outcome_id_11,outcome_id_12,outcome_id_13,outcome_id_14,
391 outcome_id_15,outcome_id_16,outcome_id_17,outcome_id_18,outcome_id_19,
392 outcome_id_20,time_0,time_1,time_2,time_3,time_4,time_5,time_6,time_7,time_8,
393 time_9,time_10,time_11,time_12,time_13,time_14,time_15,time_16,time_17,
394 time_18,time_19,time_20
395 INTO l_call_attempt,l_outcome_ids(1),l_outcome_ids(2),l_outcome_ids(3),
396 l_outcome_ids(4),l_outcome_ids(5),l_outcome_ids(6),l_outcome_ids(7),l_outcome_ids(8),
397 l_outcome_ids(9),l_outcome_ids(10),l_outcome_ids(11),l_outcome_ids(12),l_outcome_ids(13),
398 l_outcome_ids(14),l_outcome_ids(15),l_outcome_ids(16),l_outcome_ids(17),l_outcome_ids(18),
399 l_outcome_ids(19),l_outcome_ids(20),l_outcome_ids(21),l_times(1),l_times(2),l_times(3),l_times(4),
400 l_times(5),l_times(6),l_times(7),l_times(8),l_times(9),l_times(10),l_times(11),
401 l_times(12),l_times(13),l_times(14),l_times(15),l_times(16),l_times(17),
402 l_times(18),l_times(19),l_times(20),l_times(21)
403 FROM iec_o_rcy_call_histories
404 WHERE returns_id = P_RETURNS_ID AND contact_point is null
405 FOR UPDATE;
406
407 IF l_call_attempt = 21 THEN
408 UPDATE iec_o_rcy_call_histories /*+ index(iec_o_rcy_call_histories iec_o_rcy_call_histories_n1) */
409 SET call_attempt= 21,
410 outcome_id_0 = l_outcome_ids(2),
411 outcome_id_1 = l_outcome_ids(3),
412 outcome_id_2 = l_outcome_ids(4),
413 outcome_id_3 = l_outcome_ids(5),
414 outcome_id_4 = l_outcome_ids(6),
415 outcome_id_5 = l_outcome_ids(7),
416 outcome_id_6 = l_outcome_ids(8),
417 outcome_id_7 = l_outcome_ids(9),
418 outcome_id_8 = l_outcome_ids(10),
419 outcome_id_9 = l_outcome_ids(11),
420 outcome_id_10 = l_outcome_ids(12),
421 outcome_id_11 = l_outcome_ids(13),
422 outcome_id_12 = l_outcome_ids(14),
423 outcome_id_13 = l_outcome_ids(15),
424 outcome_id_14 = l_outcome_ids(16),
425 outcome_id_15 = l_outcome_ids(17),
426 outcome_id_16 = l_outcome_ids(18),
427 outcome_id_17 = l_outcome_ids(19),
428 outcome_id_18 = l_outcome_ids(20),
429 outcome_id_19 = l_outcome_ids(21),
430 outcome_id_20 = P_OUTCOME_ID,
431 time_0 = l_times(2),
432 time_1 = l_times(3),
433 time_2 = l_times(4),
434 time_3 = l_times(5),
435 time_4 = l_times(6),
436 time_5 = l_times(7),
437 time_6 = l_times(8),
438 time_7 = l_times(9),
439 time_8 = l_times(10),
440 time_9 = l_times(11),
441 time_10 = l_times(12),
442 time_11 = l_times(13),
443 time_12 = l_times(14),
444 time_13 = l_times(15),
445 time_14 = l_times(16),
446 time_15 = l_times(17),
447 time_16 = l_times(18),
448 time_17 = l_times(19),
449 time_18 = l_times(20),
450 time_19 = l_times(21),
451 time_20 = P_TIME,
452 last_update_date = sysdate
453 WHERE returns_id = P_RETURNS_ID AND contact_point is null;
454
455 ELSIF l_call_attempt < 21 THEN
456
457 EXECUTE IMMEDIATE 'update iec_o_rcy_call_histories /*+ index(iec_o_rcy_call_histories iec_o_rcy_call_histories_n1) */ ' ||
458 'set call_attempt = :1 ' ||
459 ', outcome_id_'||l_call_attempt||' = :2 ' ||
460 ', time_'||l_call_attempt||' = :3 ' ||
461 ', last_update_date = SYSDATE ' ||
462 'where returns_id = :4 AND contact_point is null'
463 USING l_call_attempt+1
464 , P_OUTCOME_ID
465 , P_TIME
466 , P_RETURNS_ID;
467
468 END IF;
469
470 EXCEPTION
471 WHEN NO_DATA_FOUND THEN
472 BEGIN
473
474 INSERT INTO iec_o_rcy_call_histories
475 (
476 call_history_id,
477 returns_id,
478 call_attempt,
479 contact_point,
480 outcome_id_0,
481 time_0,
482 created_by,
483 creation_date,
484 last_updated_by,
485 last_update_date,
486 last_update_login
487 )
488 VALUES
489 (
490 iec_o_rcy_call_histories_s.nextval,
491 P_RETURNS_ID,
492 1,
493 null,
494 P_OUTCOME_ID,
495 P_TIME,
496 nvl(FND_GLOBAL.USER_ID,-1),
497 sysdate,
498 nvl(FND_GLOBAL.USER_ID,-1),
499 sysdate,
500 nvl(FND_GLOBAL.CONC_LOGIN_ID,-1)
501 );
502
503 INSERT INTO iec_o_rcy_call_histories
504 (
505 call_history_id,
506 returns_id,
507 call_attempt,
508 contact_point,
509 outcome_id_0,
510 time_0,
511 created_by,
512 creation_date,
513 last_updated_by,
514 last_update_date,
515 last_update_login
516 )
517 VALUES
518 (
519 iec_o_rcy_call_histories_s.nextval,
520 P_RETURNS_ID,
521 1,
522 P_CONTACT_POINT,
526 sysdate,
523 P_OUTCOME_ID,
524 P_TIME,
525 nvl(FND_GLOBAL.USER_ID,-1),
527 nvl(FND_GLOBAL.USER_ID,-1),
528 sysdate,
529 nvl(FND_GLOBAL.CONC_LOGIN_ID,-1)
530 );
531
532 END; -- END INSERT BLOCK
533 RETURN;
534
535 END; -- END SELECT BLOCK
536
537 l_call_attempt := 0; --re initial
538
539 BEGIN
540 SELECT /*+ index(iec_o_rcy_call_histories iec_o_rcy_call_histories_n1) */ call_attempt,outcome_id_0,outcome_id_1,outcome_id_2,outcome_id_3,
541 outcome_id_4,outcome_id_5,outcome_id_6,outcome_id_7,outcome_id_8,outcome_id_9,
542 outcome_id_10,outcome_id_11,outcome_id_12,outcome_id_13,outcome_id_14,
543 outcome_id_15,outcome_id_16,outcome_id_17,outcome_id_18,outcome_id_19,
544 outcome_id_20,time_0,time_1,time_2,time_3,time_4,time_5,time_6,time_7,time_8,
545 time_9,time_10,time_11,time_12,time_13,time_14,time_15,time_16,time_17,
546 time_18,time_19,time_20
547 INTO l_call_attempt,l_outcome_ids(1),l_outcome_ids(2),l_outcome_ids(3),
548 l_outcome_ids(4),l_outcome_ids(5),l_outcome_ids(6),l_outcome_ids(7),l_outcome_ids(8),
549 l_outcome_ids(9),l_outcome_ids(10),l_outcome_ids(11),l_outcome_ids(12),l_outcome_ids(13),
550 l_outcome_ids(14),l_outcome_ids(15),l_outcome_ids(16),l_outcome_ids(17),l_outcome_ids(18),
551 l_outcome_ids(19),l_outcome_ids(20),l_outcome_ids(21),l_times(1),l_times(2),l_times(3),l_times(4),
552 l_times(5),l_times(6),l_times(7),l_times(8),l_times(9),l_times(10),l_times(11),
553 l_times(12),l_times(13),l_times(14),l_times(15),l_times(16),l_times(17),
554 l_times(18),l_times(19),l_times(20),l_times(21)
555 FROM iec_o_rcy_call_histories
556 WHERE returns_id = P_RETURNS_ID AND contact_point = P_CONTACT_POINT
557 FOR UPDATE;
558
559 IF l_call_attempt = 21 THEN
560 UPDATE iec_o_rcy_call_histories /*+ index(iec_o_rcy_call_histories iec_o_rcy_call_histories_n1) */
561 SET call_attempt= 21,
562 outcome_id_0 = l_outcome_ids(2),
563 outcome_id_1 = l_outcome_ids(3),
564 outcome_id_2 = l_outcome_ids(4),
565 outcome_id_3 = l_outcome_ids(5),
566 outcome_id_4 = l_outcome_ids(6),
567 outcome_id_5 = l_outcome_ids(7),
568 outcome_id_6 = l_outcome_ids(8),
569 outcome_id_7 = l_outcome_ids(9),
570 outcome_id_8 = l_outcome_ids(10),
571 outcome_id_9 = l_outcome_ids(11),
572 outcome_id_10 = l_outcome_ids(12),
573 outcome_id_11 = l_outcome_ids(13),
574 outcome_id_12 = l_outcome_ids(14),
575 outcome_id_13 = l_outcome_ids(15),
576 outcome_id_14 = l_outcome_ids(16),
577 outcome_id_15 = l_outcome_ids(17),
578 outcome_id_16 = l_outcome_ids(18),
579 outcome_id_17 = l_outcome_ids(19),
580 outcome_id_18 = l_outcome_ids(20),
581 outcome_id_19 = l_outcome_ids(21),
582 outcome_id_20 = P_OUTCOME_ID,
583 time_0 = l_times(2),
584 time_1 = l_times(3),
585 time_2 = l_times(4),
586 time_3 = l_times(5),
587 time_4 = l_times(6),
588 time_5 = l_times(7),
589 time_6 = l_times(8),
590 time_7 = l_times(9),
591 time_8 = l_times(10),
592 time_9 = l_times(11),
593 time_10 = l_times(12),
594 time_11 = l_times(13),
595 time_12 = l_times(14),
596 time_13 = l_times(15),
597 time_14 = l_times(16),
598 time_15 = l_times(17),
599 time_16 = l_times(18),
600 time_17 = l_times(19),
601 time_18 = l_times(20),
602 time_19 = l_times(21),
603 time_20 = P_TIME,
604 last_update_date = sysdate
605 WHERE returns_id = P_RETURNS_ID AND contact_point = P_CONTACT_POINT;
606
607 ELSIF l_call_attempt < 21 THEN
608 EXECUTE IMMEDIATE 'update iec_o_rcy_call_histories /*+ index(iec_o_rcy_call_histories iec_o_rcy_call_histories_n1) */ ' ||
609 'set call_attempt = :1 ' ||
610 ', outcome_id_'||l_call_attempt||' = :2 ' ||
611 ', time_'||l_call_attempt||' = :3 ' ||
612 ', last_update_date = SYSDATE ' ||
613 'where returns_id = :4 AND contact_point = :5'
614 USING l_call_attempt+1
615 , P_OUTCOME_ID
616 , P_TIME
617 , P_RETURNS_ID
618 , P_CONTACT_POINT;
619
620 END IF;
621
622 EXCEPTION
623 WHEN NO_DATA_FOUND THEN
624 BEGIN
625
626 INSERT INTO iec_o_rcy_call_histories
627 (
628 call_history_id,
629 returns_id,
630 call_attempt,
631 contact_point,
632 outcome_id_0,
633 time_0,
634 created_by,
635 creation_date,
636 last_updated_by,
637 last_update_date,
638 last_update_login
639 )
640 VALUES
641 (
642 iec_o_rcy_call_histories_s.nextval,
643 P_RETURNS_ID,
644 1,
645 P_CONTACT_POINT,
646 P_OUTCOME_ID,
647 P_TIME,
648 nvl(FND_GLOBAL.USER_ID,-1),
649 sysdate,
650 nvl(FND_GLOBAL.USER_ID,-1),
651 sysdate,
652 nvl(FND_GLOBAL.CONC_LOGIN_ID,-1)
653 );
654
655 END; -- END INSERT BLOCK
656 RETURN;
657
658 END; -- END SELECT BLOCK
659
660 EXCEPTION
664
661 WHEN OTHERS THEN
662 RAISE;
663 END ADD_CALL_HISTORY;
665 END IEC_RETURNS_UTIL_PVT;