[Home] [Help]
PACKAGE BODY: APPS.BIX_CCI_COLLECT
Source
1 PACKAGE BODY BIX_CCI_COLLECT AS
2 /* $Header: BIXCCIVB.pls 115.30 2003/01/10 00:31:33 achanda noship $ */
3 PROCEDURE WRITE_LOG(p_msg IN VARCHAR2, p_proc_name IN VARCHAR2);
4
5 PROCEDURE COLLATE_CALLS(p_start_date IN DATE,
6 p_end_date IN DATE)
7 AS
8 l_wrap_time NUMBER;
9 l_queue_segment CHAR := NULL;
10
11 CURSOR all_calls IS
12 SELECT media_id,
13 media_item_type,
14 media_item_ref,
15 media_data,
16 DECODE(media_item_type,'Telephony, Inbound','INBOUND',NULL) direction,
17 start_date_time,
18 end_date_time,
19 source_id,
20 DECODE(media_abandon_flag,'Y','ABANDON',
21 DECODE(media_transferred_flag,'Y','TRANSFER',NULL)) interaction_subtype
22 FROM jtf_ih_media_items
23 WHERE start_date_time between p_start_date and p_end_date
24 and active = 'N';
25
26 CURSOR c_milcs (cv_ih_mi_id NUMBER) IS
27 SELECT
28 ih_milcs.DURATION,
29 ih_milcs_ty.MILCS_CODE,
30 ih_milcs.HANDLER_ID,
31 ih_milcs.RESOURCE_ID,
32 DECODE(ih_milcs_ty.MILCS_CODE,'IN_QUEUE',1,'WITH_AGENT',2) segment_order
33 FROM
34 JTF_IH_MEDIA_ITEMS ih_mitem,
35 JTF_IH_MEDIA_ITEM_LC_SEGS ih_milcs,
36 JTF_IH_MEDIA_ITM_LC_SEG_TYS ih_milcs_ty
37 WHERE
38 (ih_mitem.MEDIA_ID = cv_ih_mi_id) and
39 (ih_mitem.MEDIA_ID = ih_milcs.MEDIA_ID) and
40 (ih_milcs.MILCS_TYPE_ID = ih_milcs_ty.MILCS_TYPE_ID)
41 order by segment_order;
42 /*
43 CURSOR get_wrap_time(p_media_id NUMBER) IS
44 SELECT sum((act.end_date_time - act.start_date_time) * 24 * 3600) wrap_time
45 FROM jtf_ih_activities act, jtf_ih_action_items_vl actitems
46 WHERE act.media_id = p_media_id
47 AND act.action_item_id = actitems.action_item_id
48 AND actitems.action_item = 'Wrapup';
49 */
50
51 CURSOR get_wrap_time(p_media_id NUMBER,p_resource_id NUMBER) IS
52 SELECT sum((int.end_date_time - seg.end_date_time) * 24 * 3600) wrap_time
53 FROM jtf_ih_interactions int, jtf_ih_media_item_lc_segs seg, jtf_ih_media_itm_lc_seg_tys tys
54 WHERE int.productive_time_amount = p_media_id
55 AND int.resource_id = p_resource_id
56 AND seg.media_id = p_media_id
57 AND seg.resource_id = p_resource_id
58 AND tys.milcs_type_id = seg.milcs_type_id
59 AND tys.milcs_code = 'WITH_AGENT'
60 AND int.end_date_time > seg.end_date_time;
61
62 BEGIN
63
64 DELETE from BIX_INTERACTIONS;
65 --WHERE start_ts between p_start_date and p_end_date;
66
67
68 FOR act IN all_calls LOOP
69
70 l_queue_segment := NULL;
71
72 INSERT INTO BIX_INTERACTIONS
73 (
74 INTERACTIONS_ID,
75 START_TS,
76 COMPLETED_TS,
77 MEDIA_ITEM_TYPE,
78 MEDIA_ITEM_REF,
79 INTERACTION_TYPE,
80 INTERACTION_SUBTYPE,
81 RESOURCE_ID,
82 INTERACTION_CENTER_ID
83 )
84 VALUES
85 (
86 act.media_id,
87 act.START_DATE_TIME,
88 act.END_DATE_TIME,
89 act.MEDIA_ITEM_TYPE,
90 act.MEDIA_ITEM_REF,
91 act.DIRECTION,
92 act.INTERACTION_SUBTYPE,
93 '-1',
94 act.source_id
95 );
96
97 IF ( act.MEDIA_ID IS NOT NULL )
98 THEN
99
100 FOR milcs IN c_milcs( act.MEDIA_ID ) LOOP
101
102 IF (milcs.MILCS_CODE = 'IVR') THEN
103
104 UPDATE BIX_INTERACTIONS
105 SET IVR_TIME = milcs.DURATION
106 WHERE INTERACTIONS_ID = act.media_id
107 AND resource_id = -1;
108
109 ELSIF (UPPER(milcs.MILCS_CODE) = 'ROUTING') THEN
110
111 UPDATE BIX_INTERACTIONS
112 SET ROUTE_TIME = milcs.DURATION
113 WHERE INTERACTIONS_ID = act.media_id
114 AND resource_id = -1;
115
116 ELSIF (milcs.MILCS_CODE = 'IN_QUEUE') THEN
117
118 l_queue_segment := 'T';
119
120 UPDATE BIX_INTERACTIONS
121 SET PARTY_WAIT_TIME = milcs.DURATION,USER_ATTRIBUTE1 = 'T'
122 WHERE INTERACTIONS_ID = act.media_id
123 AND resource_id = -1;
124
125 ELSIF (milcs.MILCS_CODE = 'WITH_AGENT') THEN
126
127 IF ( l_queue_segment = 'T') THEN
128
129 l_wrap_time := 0;
130
131 FOR wrapdata in get_wrap_time(act.MEDIA_ID,milcs.resource_id) LOOP
132 l_wrap_time := wrapdata.wrap_time;
133 END LOOP;
134
135 -- if wrap time is undefined or null set it to 0
136
137 IF l_wrap_time is NULL THEN
138 l_wrap_time := 0;
139 END IF;
140
141 -- multiple agents can be involved in same call. Insert separate row for each agent in the interface
142 -- table
143
144 INSERT INTO BIX_INTERACTIONS
145 (
146 INTERACTIONS_ID,
147 START_TS,
148 COMPLETED_TS,
149 MEDIA_ITEM_TYPE,
150 MEDIA_ITEM_REF,
151 INTERACTION_TYPE,
152 RESOURCE_ID,
153 HANDLER_ID,
154 INTERACTION_CENTER_ID,
155 WRAP_TIME,
156 TALK_TIME,
157 USER_ATTRIBUTE2
158 )
159 VALUES
160 (
161 act.media_id,
162 act.START_DATE_TIME,
163 act.END_DATE_TIME,
164 act.MEDIA_ITEM_TYPE,
165 act.MEDIA_ITEM_REF,
166 act.DIRECTION,
167 milcs.resource_id,
168 milcs.HANDLER_ID,
169 act.source_id,
170 l_wrap_time,
171 milcs.duration,
172 DECODE(l_queue_segment,'T','T')
173 );
174 END IF;
175
176 END IF; -- End of life cycle segment IF statement.
177
178 END LOOP; -- end of life cycle segment loop
179
180 END IF; -- end of IF act.media_id is NOT NULL
181
182 END LOOP; -- End of media loop
183
184 EXCEPTION
185 WHEN OTHERS THEN
186 write_log('Error: '||sqlerrm,' POPULATE_BIX_SUM_GRP_CLS');
187 ROLLBACK;
188 RAISE;
189 END COLLATE_CALLS;
190
191 PROCEDURE COLLECT_CCI_DATA( errbuf out nocopy varchar2,
192 retcode out nocopy varchar2,
193 p_start_date IN varchar2,
194 p_end_date IN varchar2)
195 AS
196
197 no_messages exception;
198 pragma exception_init (no_messages, -25228);
199 l_start_date DATE;
200 l_end_date DATE;
201
202 l_ih_interaction_id NUMBER(15,0);
203 l_num_interactions NUMBER;
204
205 l_b_remove BOOLEAN;
206
207 l_num_processed PLS_INTEGER := 0;
208 l_num_skipped PLS_INTEGER := 0;
209 l_num_missing PLS_INTEGER := 0;
210
211 l_start_secs NUMBER;
212 BEGIN
213 l_start_date := to_date(p_start_date, 'YYYY/MM/DD HH24:MI:SS');
214 l_end_date := to_date(p_end_date, 'YYYY/MM/DD HH24:MI:SS');
215 -- defaults for request set program
216 -- default start date to end date -1 if the dates are equal
217 IF (l_start_date = l_end_date) THEN
218 l_start_date := l_end_date - 1;
219 END IF;
220
221 COLLATE_CALLS(l_start_date, l_end_date);
222
223 COMMIT;
224 EXCEPTION
225 WHEN OTHERS THEN
226 ROLLBACK;
227 write_log('Error:' || sqlerrm, 'BIX_CCI_COLLECT.COLLECT_CCI_DATA');
228 RAISE;
229 END COLLECT_CCI_DATA;
230
231 PROCEDURE COLLECT_CCI_DATA(p_start_date IN VARCHAR2,
232 p_end_date IN VARCHAR2)
233 AS
234 l_ih_interaction_id NUMBER(15,0);
235 l_num_interactions NUMBER;
236 l_start_date DATE;
237 l_end_date DATE;
238 BEGIN
239 l_start_date := to_date(p_start_date, 'YYYY/MM/DD HH24:MI:SS');
240 l_end_date := to_date(p_end_date, 'YYYY/MM/DD HH24:MI:SS');
241
242 COLLATE_CALLS(l_start_date, l_end_date);
243
244 COMMIT;
245 EXCEPTION
246 WHEN OTHERS THEN
247 ROLLBACK;
248 write_log('Error:' || sqlerrm, 'BIX_CCI_COLLECT.COLLECT_CCI_DATA');
249 RAISE;
250 END COLLECT_CCI_DATA;
251
252 PROCEDURE WRITE_LOG(p_msg VARCHAR2, p_proc_name VARCHAR2) IS
253 BEGIN
254 FND_FILE.PUT_LINE(fnd_file.log,'Load Interactions Log - ' || p_msg || ': '|| p_proc_name);
255 EXCEPTION
256 WHEN OTHERS THEN
257 RAISE;
258 END WRITE_LOG;
259
260 END BIX_CCI_COLLECT;