[Home] [Help]
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,
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
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),
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 ' ||
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,
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,
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,
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,
523: P_OUTCOME_ID,
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,
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
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),
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 ' ||
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,
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,