[Home] [Help]
PACKAGE BODY: APPS.BIX_AGT_COLLECT
Source
1 PACKAGE BODY BIX_AGT_COLLECT AS
2 /* $Header: bixsesso.plb 115.0 2004/09/14 00:41:29 anasubra noship $ */
3 PROCEDURE WRITE_LOG(p_msg IN VARCHAR2, p_proc_name IN VARCHAR2);
4
5 PROCEDURE COLLATE_AGENT(p_start_date IN DATE,
6 p_end_date IN DATE)
7 AS
8 v_available_time NUMBER;
9 v_wrap_time NUMBER;
10 v_talk_time NUMBER;
11 v_idle_time NUMBER;
12 v_logged_in_time NUMBER;
13 v_calls_answered NUMBER;
14 v_resource_id NUMBER;
15 v_ses_start_date DATE;
16 v_ses_end_date DATE;
17 v_center_id NUMBER;
18
19 CURSOR get_login_time IS
20 select sum((iss.end_date_time - iss.begin_date_time) * 24 * 3600) login_time,
21 iss.resource_id,
22 iss.begin_date_time ,
23 iss.end_date_time
24 from ieu_sh_sessions iss
25 where iss.begin_date_time between p_start_date and p_end_date
26 and iss.active_flag is NULL
27 group by iss.resource_id, iss.begin_date_time,iss.end_date_time;
28
29 CURSOR get_available_time IS
30 select sum((isa.end_date_time - isa.begin_date_time) * 24 * 3600) available_time
31 from ieu_sh_activities isa, ieu_sh_sessions iss
32 where isa.session_id = iss.session_id
33 and isa.activity_type_code = 'WORK_REQUEST'
34 and iss.begin_date_time = v_ses_start_date
35 and iss.ACTIVE_FLAG is NULL
36 and iss.resource_id = v_resource_id;
37
38 CURSOR get_times IS
39 SELECT interaction_center_id,
40 sum(talk_time) talk_time,
41 sum(wrap_time) wrap_time,
42 SUM(DECODE(NVL(USER_ATTRIBUTE2,'F'),'T',1,NULL)) calls_answered
43 FROM bix_interactions
44 WHERE resource_id = v_resource_id
45 AND start_ts BETWEEN v_ses_start_date AND v_ses_end_date
46 group by interaction_center_id;
47
48
49 /*
50 CURSOR get_wrap_time IS
51 SELECT sum((act.end_date_time - act.start_date_time) * 24 * 3600) wrap_time
52 FROM jtf_ih_activities act, ieu_sh_activities isa, jtf_ih_interactions int, jtf_ih_action_items_vl actitems
53 WHERE act.media_id = isa.media_id
54 AND act.interaction_id = int.interaction_id
55 AND int.resource_id = v_resource_id
56 AND (act.start_date_time, 'DD/MON/YYYY') = v_day
57 AND act.action_item_id = actitems.action_item_id
58 AND actitems.action_item = 'Wrapup';
59 */
60
61 /*
62 CURSOR get_wrap_time IS
63 SELECT sum((int.end_date_time - seg.end_date_time) * 24 * 3600) wrap_time
64 FROM jtf_ih_interactions int, jtf_ih_media_item_lc_segs seg, jtf_ih_media_itm_lc_seg_tys tys, ieu_sh_activities isa, ieu_sh_sessions iss
65 WHERE iss.resource_id = v_resource_id
66 AND iss.begin_date_time = v_day
67 AND iss.active_flag is null
68 AND isa.session_id = iss.session_id
69 AND int.productive_time_amount = isa.media_id
70 AND seg.media_id = int.productive_time_amount
71 AND seg.resource_id = int.resource_id
72 AND seg.milcs_type_id = tys.milcs_type_id
73 AND tys.milcs_code = 'WITH_AGENT'
74 AND int.end_date_time > seg.end_date_time;
75 */
76
77
78 CURSOR center_id IS
79 SELECT server_group_id center_id
80 FROM jtf_rs_resource_extns
81 WHERE resource_id = v_resource_id;
82
83
84 BEGIN
85 -- delete the existing data for the selected date range
86
87 DELETE from BIX_SUM_AGENT
88 WHERE day between p_start_date and p_end_date;
89
90 OPEN get_login_time;
91 FETCH get_login_time
92 INTO v_logged_in_time, v_resource_id, v_ses_start_date,v_ses_end_date;
93 /*
94 for center_data in center_id LOOP
95 v_center_id := center_data.center_id;
96 end LOOP;
97 */
98 WHILE get_login_time%FOUND LOOP
99
100 v_available_time := 0;
101 v_talk_time := 0;
102 v_wrap_time := 0;
103 v_idle_time := 0;
104 v_calls_answered := 0;
105 v_center_id := NULL;
106
107 for available_data IN get_available_time LOOP
108 v_available_time := available_data.available_time;
109 end loop;
110
111 for call_times IN get_times LOOP
112 v_center_id := call_times.interaction_center_id;
113 v_talk_time := call_times.talk_time;
114 v_wrap_time := call_times.wrap_time;
115 v_calls_answered := call_times.calls_answered;
116 end loop;
117
118 /*
119 for talk_data IN get_talk_time LOOP
120 v_talk_time := talk_data.talk_time;
121 v_calls_answered := talk_data.calls_answered;
122 end loop;
123 for wrap_data IN get_wrap_time LOOP
124 v_wrap_time := wrap_data.wrap_time;
125 end loop;
126 */
127 -- if talk time is undefined set to 0
128 if (v_talk_time is NULL) then
129 v_talk_time := 0;
130 end if;
131 -- if available time is undefined set to 0
132 if (v_available_time is NULL) then
133 v_available_time := 0;
134 end if;
135 -- if wrap time is undefined set to 0
136 if (v_wrap_time is NULL) then
137 v_wrap_time := 0;
138 end if;
139 -- v_day_date := to_date(v_day, 'DD/MON/YYYY');
140 v_idle_time := v_logged_in_time - v_talk_time - v_available_time - v_wrap_time;
141
142 IF ( v_center_id IS NULL) THEN
143 for center_data in center_id LOOP
144 v_center_id := center_data.center_id;
145 end LOOP;
146 END IF;
147
148 INSERT INTO BIX_SUM_AGENT
149 (
150 INTERACTION_CENTER_ID,
151 RESOURCE_ID,
152 TALK_TIME,
153 AVAILABLE_TIME,
154 WRAP_TIME,
155 IDLE_TIME,
156 LOGGED_IN_TIME,
157 CALLS_ANSWERED,
158 DAY
159 )
160 VALUES
161 (
162 v_center_id,
163 v_resource_id,
164 v_talk_time,
165 v_available_time,
166 v_wrap_time,
167 v_idle_time,
168 v_logged_in_time,
169 v_calls_answered,
170 v_ses_start_date
171 );
172 FETCH get_login_time
173 INTO v_logged_in_time, v_resource_id, v_ses_start_date,v_ses_end_date;
174 /*
175 for center_data in center_id LOOP
176 v_center_id := center_data.center_id;
177 end LOOP;
178
179 */
180
181 END LOOP;
182 CLOSE get_login_time;
183 COMMIT;
184 END COLLATE_AGENT;
185
186 PROCEDURE COLLECT_AGT_DATA( errbuf out nocopy varchar2,
187 retcode out nocopy varchar2,
188 p_start_date IN varchar2,
189 p_end_date IN varchar2)
190 AS
191
192 no_messages exception;
193 pragma exception_init (no_messages, -25228);
194 l_start_date DATE;
195 l_end_date DATE;
196
197 l_ih_interaction_id NUMBER(15,0);
198 l_num_interactions NUMBER;
199
200 l_b_remove BOOLEAN;
201
202 l_num_processed PLS_INTEGER := 0;
203 l_num_skipped PLS_INTEGER := 0;
204 l_num_missing PLS_INTEGER := 0;
205
206 l_start_secs NUMBER;
207 BEGIN
208 -- dbms_output.put_line('starting');
209 l_start_date := to_date(p_start_date, 'YYYY/MM/DD HH24:MI:SS');
210 l_end_date := to_date(p_end_date, 'YYYY/MM/DD HH24:MI:SS');
211 -- defaults for request set program
212 -- default start date to end date -1 if the dates are equal
213 IF (l_start_date = l_end_date) THEN
214 l_start_date := l_end_date - 1;
215 END IF;
216 COLLATE_AGENT(l_start_date, l_end_date);
217 EXCEPTION
218 WHEN OTHERS THEN
219 write_log('Error:' || sqlerrm, 'BIX_AGT_COLLECT.COLLECT_AGT_DATA');
220 END COLLECT_AGT_DATA;
221
222 PROCEDURE COLLECT_AGT_DATA(p_start_date IN VARCHAR2,
223 p_end_date IN VARCHAR2)
224 AS
225 l_ih_interaction_id NUMBER(15,0);
226 l_num_interactions NUMBER;
227 l_start_date DATE;
228 l_end_date DATE;
229 BEGIN
230 l_start_date := to_date(p_start_date, 'YYYY/MM/DD HH24:MI:SS');
231 l_end_date := to_date(p_end_date, 'YYYY/MM/DD HH24:MI:SS');
232 COLLATE_AGENT(l_start_date, l_end_date);
233 EXCEPTION
234 WHEN OTHERS THEN
235 write_log('Error:' || sqlerrm, 'BIX_AGT_COLLECT.COLLECT_AGT_DATA');
236 END COLLECT_AGT_DATA;
237
238 PROCEDURE WRITE_LOG(p_msg VARCHAR2, p_proc_name VARCHAR2) IS
239 BEGIN
240 FND_FILE.PUT_LINE(fnd_file.log,'Load Interactions Log - ' || p_msg || ': '|| p_proc_name);
241 END WRITE_LOG;
242
243 END BIX_AGT_COLLECT;