[Home] [Help]
PACKAGE BODY: APPS.BIX_SUMMARY_PUB
Source
1 package body BIX_SUMMARY_PUB AS
2 /* $Header: BIXSUMPB.pls 115.19 2003/01/10 00:31:11 achanda ship $: */
3
4 g_start_date DATE := NULL;
5 g_end_date DATE := NULL;
6
7
8 PROCEDURE WRITE_LOG(p_msg VARCHAR2, p_proc_name VARCHAR2) IS
9 BEGIN
10 FND_FILE.PUT_LINE(fnd_file.log,'Load Interactions Log - ' || p_msg || ': '|| p_proc_name);
11 END WRITE_LOG;
12
13 procedure POPULATE_BIX_SUM_GRP_CLS
14 IS
15 CURSOR POP_BIX_SUM_GRP_CLS
16 IS
17 SELECT interaction_type,
18 media_item_type,
19 interaction_center_id,
20 campaign_id,
21 resource_id,
22 TO_DATE(to_char(start_ts, 'DD-MM-YYYY-HH24'),'DD-MM-YYYY-HH24') hour,
23 interaction_classification,
24 SUM(ivr_time) ivr_time,
25 SUM(route_time) route_time,
26 SUM(party_wait_time) party_wait_time,
27 SUM(talk_time) talk_time,
28 SUM(wrap_time) wrap_time,
29 SUM(idle_time) idle_time,
30 SUM(DECODE(interaction_subtype,'TRANSFER',1,NULL)) transfers,
31 SUM(DECODE(interaction_subtype,'ABANDON',1,NULL)) abandoned_count,
32 SUM(DECODE(interaction_subtype,'ABANDON',party_wait_time,NULL)) wait_time_to_abandon,
33 SUM(DECODE(first_interaction_resoln_flag,1,1,NULL)) first_interaction_resoln_count,
34 SUM(DECODE(NVL(USER_ATTRIBUTE2,'F'),'T',1,NULL)) interactions_answered_live,
35 SUM(preview_time) preview_time,
36 SUM(non_productive_time) non_productive_time,
37 SUM(response_time) response_time,
38 SUM(resolution_time_internal) resolution_time_internal,
39 SUM(resolution_time) resolution_time,
40 SUM(DECODE(NVL(USER_ATTRIBUTE1,'F'),'T',1,NULL)) number_of_interactions
41 FROM bix_interactions
42 GROUP BY interaction_type, media_item_type, interaction_center_id,
43 campaign_id, resource_id, to_char(start_ts, 'DD-MM-YYYY-HH24'),
44 interaction_classification;
45
46 BEGIN
47 begin
48 -- dbms_output.put_line('POPULATE_BIX_SUM_X=');
49
50 ---dbms_output.put_line('g_Start_date : '|| g_start_date );
51 --dbms_output.put_line('g_end_date : '|| g_end_date );
52
53 DELETE FROM bix_sum_grp_cls
54 WHERE hour BETWEEN g_start_date AND g_end_date;
55
56 DELETE FROM bix_sum_agt_cls
57 WHERE hour BETWEEN g_start_date AND g_end_date;
58
59
60 FOR call IN POP_BIX_SUM_GRP_CLS LOOP
61 INSERT INTO BIX_SUM_GRP_CLS
62 (
63 sum_grp_cls_id,
64 campaign_id,
65 interaction_classification,
66 media_item_type,
67 interaction_center_id,
68 resource_group_id,
69 interaction_type,
70 hour,
71 transfers,
72 abandoned_count,
73 wait_time_to_abandon,
74 interactions_answered_live,
75 first_interaction_resoln_count,
76 ivr_time,
77 route_time,
78 party_wait_time,
79 speed_to_answer,
80 talk_time,
81 wrap_time,
82 idle_time,
83 preview_time,
84 non_productive_time,
85 response_time,
86 resolution_time_internal,
87 number_of_interactions
88 )
89 VALUES
90 (
91 BIX_SUM_GRP_CLS_S.nextval,
92 call.campaign_id,
93 call.interaction_classification,
94 call.media_item_type,
95 call.interaction_center_id,
96 call.resource_id,
97 call.interaction_type,
98 call.hour,
99 call.transfers,
100 call.abandoned_count,
101 call.wait_time_to_abandon,
102 call.interactions_answered_live,
103 call.first_interaction_resoln_count,
104 call.ivr_time,
105 call.route_time,
106 call.party_wait_time,
107 call.party_wait_time,
108 call.talk_time,
109 call.wrap_time,
110 call.idle_time,
111 call.preview_time,
112 call.non_productive_time,
113 call.response_time,
114 call.resolution_time_internal,
115 call.number_of_interactions
116 );
117
118 INSERT INTO BIX_SUM_AGT_CLS
119 (
120 sum_agt_cls_id,
121 campaign_id,
122 interaction_classification,
123 media_item_type,
124 interaction_center_id,
125 resource_id,
126 resource_group_id,
127 interaction_type,
128 hour,
129 transfers,
130 abandoned_count,
131 wait_time_to_abandon,
132 interactions_answered_live,
133 first_interaction_resoln_count,
134 ivr_time,
135 route_time,
136 party_wait_time,
137 speed_to_answer_time,
138 talk_time,
139 wrap_time,
140 idle_time,
141 preview_time,
142 non_productive_time,
143 response_time,
144 resolution_time_internal,
145 number_of_interactions
146 )
147 VALUES
148 (
149 BIX_SUM_AGT_CLS_S.nextval,
150 call.campaign_id,
151 call.interaction_classification,
152 call.media_item_type,
153 call.interaction_center_id,
154 call.resource_id,
155 call.resource_id,
156 call.interaction_type,
157 call.hour,
158 call.transfers,
159 call.abandoned_count,
160 call.wait_time_to_abandon,
161 call.interactions_answered_live,
162 call.first_interaction_resoln_count,
163 call.ivr_time,
164 call.route_time,
165 call.party_wait_time,
166 call.party_wait_time,
167 call.talk_time,
168 call.wrap_time,
169 call.idle_time,
170 call.preview_time,
171 call.non_productive_time,
172 call.response_time,
173 call.resolution_time_internal,
174 call.number_of_interactions
175 );
176 END LOOP;
177 exception
178 WHEN OTHERS THEN
179 write_log('Error: '||sqlerrm,' POPULATE_BIX_SUM_GRP_CLS');
180 rollback;
181 raise;
182 end;
183 END POPULATE_BIX_SUM_GRP_CLS;
184
185 procedure POPULATE_BIX_SUM_X
186 IS
187 BEGIN
188 populate_bix_sum_grp_cls;
189 exception
190 when others then
191 write_log('Error : '||sqlerrm,'POPULATE_BIX_SUM_X');
192 rollback;
193 raise;
194 END POPULATE_BIX_SUM_X;
195
196 procedure POPULATE_BIX_SUM_X(errbuf out nocopy varchar2,
197 retcode out nocopy varchar2
198 )
199 IS
200 BEGIN
201 declare
202 l_message varchar2(2000);
203 l_count NUMBER := 0;
204 begin
205
206 SELECT COUNT(*) INTO l_count
207 FROM BIX_INTERACTIONS;
208
209 /* Since we capture data in summary tables at 1 hour level. We need delete the complete bucket
210 and re collect again
211 */
212
213 --dbms_output.put_line('Count of rows in interactions : ' || l_count);
214
215 IF l_count > 0 THEN
216 SELECT TO_DATE(TO_CHAR(MIN(start_ts),'DD/MM/YYYY HH24'),'DD/MM/YYYY HH24')
217 INTO g_start_date
218 FROM bix_interactions;
219 SELECT TO_DATE(TO_CHAR(MAX(start_ts),'DD/MM/YYYY HH24'),'DD/MM/YYYY HH24')
220 INTO g_end_date
221 FROM bix_interactions;
222
223 --dbms_output.put_line('Start date : '|| g_start_date);
224 --dbms_output.put_line('End date : '|| g_end_date);
225
226
227 BIX_SUMMARY_PUB.POPULATE_BIX_SUM_X;
228 COMMIT;
229 END IF;
230
231 exception
232 WHEN OTHERS THEN
233 errbuf := sqlerrm;
234 retcode := sqlcode;
235 l_message := errbuf;
236 write_log('Error : '||errbuf,'POPULATE_BIX_SUM_X');
237 ROLLBACK;
238 raise;
239 end;
240 END POPULATE_BIX_SUM_X; /* end of conc mgr for POPULATE_BIX_SUM_X */
241
242 END BIX_SUMMARY_PUB;