eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
PACKAGE BODY: APPS.BIX_DM_REAL_QUEUE_SUMMARY_PKG

Source


1 PACKAGE BODY BIX_DM_REAL_QUEUE_SUMMARY_PKG AS
2 /*$Header: bixxrqsb.pls 115.7 2003/01/09 20:19:48 achanda noship $ */
3 
4 
5 FUNCTION GET_CLASSIFICATION (p_classification in varchar2) return NUMBER is
6 
7   v_classification_id number;
8   v_classification_count number;
9 
10 
11 BEGIN
12 
13 select count(classification_id)
14 into   v_classification_count
15 from   cct_classifications
16 where classification = p_classification;
17 
18 if (v_classification_count = 0) then
19    v_classification_id := -9999;
20 else
21   select classification_id into v_classification_id from cct_classifications where classification = p_classification;
22 end if;
23 
24 return v_classification_id;
25 
26 EXCEPTION
27 	WHEN OTHERS THEN
28 		return NULL;
29 END;
30 -- GET_CALLS collects calls from OLTP to the temporary
31 -- table BIX_DM_REAL_QUEUE_SUM:
32 
33 
34 PROCEDURE GET_CALLS(p_session_id IN NUMBER)
35 AS
36  v_calls_offered NUMBER;
37  v_calls_abandoned NUMBER;
38  v_abandon_time    NUMBER;
39  v_talk_time    NUMBER;
40  v_calls_answrd_within_x_time NUMBER;
41  v_queue_time_answered NUMBER;
42  v_calls_answered      NUMBER;
43  v_calls_handled       NUMBER;
44  v_period_start_date   DATE;
45  v_period_start_time   VARCHAR2(30);
46  v_period_start_date_time DATE;
47  v_server_group_id    NUMBER;
48  v_classification    VARCHAR2(64);
49  v_classification_id    NUMBER;
50  v_default_goal    NUMBER;
51  v_goal    NUMBER;
52 
53  l_start_date  DATE;
54  l_end_date  DATE;
55 
56 
57 
58  CURSOR call_info IS
59  SELECT sum(DECODE(UPPER(ih_mitem.direction),'INBOUND',1,0)) CALLS_OFFERED,
60         sum(DECODE(UPPER(ih_mitem.direction),'INBOUND',DECODE(UPPER(ih_mitem.media_abandon_flag),'Y',1,0),0)) CALLS_ABANDONED,
61 	ih_mitem.server_group_id SERVER_GROUP_ID,
62         ih_mitem.classification CLASSIFICATION,
63         TRUNC(ih_mitem.start_date_time) PERIOD_START_DATE,
64         LPAD(TO_CHAR(ih_mitem.start_date_time,'HH24:'),3,'0')|| DECODE(SIGN(TO_NUMBER(TO_CHAR(ih_mitem.start_date_time,'MI'))-29),0,'00',1,'30',-1,'00') PERIOD_START_TIME,
65 	TO_DATE(TO_CHAR(ih_mitem.start_date_time,'YYYY/MM/DD ')||LPAD(TO_CHAR(ih_mitem.start_date_time,'HH24:'),3,'0') || DECODE(SIGN(TO_NUMBER(TO_CHAR(ih_mitem.start_date_time,'MI'))-29),0,'00',1,'30',-1,'00'),'YYYY/MM/DD HH24:MI') PERIOD_START_DATE_TIME
66    FROM      JTF_IH_MEDIA_ITEMS ih_mitem
67  WHERE  ih_mitem.start_date_time BETWEEN l_start_date and l_end_date
68  AND
69  (
70  ih_mitem.media_item_type = 'TELE_INB' or
71  ih_mitem.media_item_type = 'TELE_DIRECT' or
72  ih_mitem.media_item_type = 'TELE_MANUAL' or
73  ih_mitem.media_item_type = 'TELE_WEB'
74  )
75  AND    ih_mitem.active = 'N'
76 GROUP BY ih_mitem.server_group_id,
77          ih_mitem.classification,
78          TRUNC(ih_mitem.start_date_time),
79          LPAD(TO_CHAR(ih_mitem.start_date_time,'HH24:'),3,'0')|| DECODE(SIGN(TO_NUMBER(TO_CHAR(ih_mitem.start_date_time,'MI'))-29),0,'00',1,'30',-1,'00'),
80 	TO_DATE(TO_CHAR(ih_mitem.start_date_time,'YYYY/MM/DD ')||LPAD(TO_CHAR(ih_mitem.start_date_time,'HH24:'),3,'0') || DECODE(SIGN(TO_NUMBER(TO_CHAR(ih_mitem.start_date_time,'MI'))-29),0,'00',1,'30',-1,'00'),'YYYY/MM/DD HH24:MI');
81 
82 CURSOR call_data1(p_server_group_id NUMBER,
83                  p_classification VARCHAR2,
84                  p_period_start_date DATE,
85                  p_period_start_time VARCHAR2,
86                  p_period_start_date_time DATE) IS
87 SELECT		SUM(NVL(msegs.duration,0)) ABANDON_TIME
88 FROM   jtf_ih_media_items ih_mitem,
89        JTF_IH_MEDIA_ITEM_LC_SEGS msegs,
90        JTF_IH_MEDIA_ITM_LC_SEG_TYS mtyps
91 where ih_mitem.server_group_id =  p_server_group_id
92 and   ih_mitem.classification = p_classification
93 and   TRUNC(ih_mitem.start_date_time) = p_period_start_date
94 and   LPAD(TO_CHAR(ih_mitem.start_date_time,'HH24:'),3,'0')|| DECODE(SIGN(TO_NUMBER(TO_CHAR(ih_mitem.start_date_time,'MI'))-29),0,'00',1,'30',-1,'00') = p_period_start_time
95 and 	TO_DATE(TO_CHAR(ih_mitem.start_date_time,'YYYY/MM/DD ')||LPAD(TO_CHAR(ih_mitem.start_date_time,'HH24:'),3,'0') ||
96 DECODE(SIGN(TO_NUMBER(TO_CHAR(ih_mitem.start_date_time,'MI'))-29),0,'00',1,'30',-1,'00'),'YYYY/MM/DD HH24:MI') = p_period_start_date_time
97 and  ih_mitem.media_id = msegs.media_id
98 and  mtyps.milcs_type_id = msegs.milcs_type_id
99 and  mtyps.milcs_code = 'IN_QUEUE'
100 and  ih_mitem.direction = 'INBOUND'
101 and  ih_mitem.media_abandon_flag = 'Y' ;
102 
103 CURSOR call_data2(p_server_group_id NUMBER,
104                  p_classification VARCHAR2,
105 			  p_classification_id NUMBER,
106                  p_period_start_date DATE,
107                  p_period_start_time VARCHAR2,
108                  p_period_start_date_time DATE) IS
109 SELECT count(distinct msegs.media_id) CALLS_ANSWRD_WITHIN_X_TIME
110 FROM   jtf_ih_media_items ih_mitem,
111        JTF_IH_MEDIA_ITEM_LC_SEGS msegs,
112        JTF_IH_MEDIA_ITM_LC_SEG_TYS mtyps
113 where ih_mitem.server_group_id =  p_server_group_id
114 and   ih_mitem.classification = p_classification
115 and   TRUNC(ih_mitem.start_date_time) = p_period_start_date
116 and   LPAD(TO_CHAR(ih_mitem.start_date_time,'HH24:'),3,'0')|| DECODE(SIGN(TO_NUMBER(TO_CHAR(ih_mitem.start_date_time,'MI'))-29),0,'00',1,'30',-1,'00') = p_period_start_time
117 and 	TO_DATE(TO_CHAR(ih_mitem.start_date_time,'YYYY/MM/DD ')||LPAD(TO_CHAR(ih_mitem.start_date_time,'HH24:'),3,'0') ||
118 DECODE(SIGN(TO_NUMBER(TO_CHAR(ih_mitem.start_date_time,'MI'))-29),0,'00',1,'30',-1,'00'),'YYYY/MM/DD HH24:MI') = p_period_start_date_time
119 and  ih_mitem.media_id = msegs.media_id
120 and  mtyps.milcs_type_id = msegs.milcs_type_id
121 and  mtyps.milcs_code = 'IN_QUEUE'
122 and  ih_mitem.direction = 'INBOUND'
123 and  msegs.duration <= v_goal
124 and (ih_mitem.media_abandon_flag = 'N' or ih_mitem.media_abandon_flag is null);
125 
126 CURSOR call_data3(p_server_group_id NUMBER,
127                  p_classification VARCHAR2,
128                  p_period_start_date DATE,
129                  p_period_start_time VARCHAR2,
130                  p_period_start_date_time DATE) IS
131 SELECT		SUM(NVL(msegs.duration,0)) queue_time_answered,
132                 count(distinct(ih_mitem.media_id)) calls_answered
133 FROM   jtf_ih_media_items ih_mitem,
134        JTF_IH_MEDIA_ITEM_LC_SEGS msegs,
135        JTF_IH_MEDIA_ITM_LC_SEG_TYS mtyps
136 where ih_mitem.server_group_id =  p_server_group_id
137 and   ih_mitem.classification = p_classification
138 and   ih_mitem.active = 'N'
139 and   TRUNC(ih_mitem.start_date_time) = p_period_start_date
140 and   LPAD(TO_CHAR(ih_mitem.start_date_time,'HH24:'),3,'0')|| DECODE(SIGN(TO_NUMBER(TO_CHAR(ih_mitem.start_date_time,'MI'))-29),0,'00',1,'30',-1,'00') = p_period_start_time
141 and 	TO_DATE(TO_CHAR(ih_mitem.start_date_time,'YYYY/MM/DD ')||LPAD(TO_CHAR(ih_mitem.start_date_time,'HH24:'),3,'0') ||
142 DECODE(SIGN(TO_NUMBER(TO_CHAR(ih_mitem.start_date_time,'MI'))-29),0,'00',1,'30',-1,'00'),'YYYY/MM/DD HH24:MI') = p_period_start_date_time
143 and  ih_mitem.media_id = msegs.media_id
144 and  mtyps.milcs_type_id = msegs.milcs_type_id
145 and  mtyps.milcs_code = 'IN_QUEUE'
146 and  ih_mitem.direction = 'INBOUND'
147 and (ih_mitem.media_abandon_flag = 'N' or ih_mitem.media_abandon_flag is null);
148 
149 CURSOR call_data4(p_server_group_id NUMBER,
150                  p_classification VARCHAR2,
151                  p_period_start_date DATE,
152                  p_period_start_time VARCHAR2,
153                  p_period_start_date_time DATE) IS
154 SELECT		SUM(NVL(msegs.duration,0)) talk_time,
155                 count(distinct(msegs.media_id)) calls_handled
156 FROM   jtf_ih_media_items ih_mitem,
157        JTF_IH_MEDIA_ITEM_LC_SEGS msegs,
158        JTF_IH_MEDIA_ITM_LC_SEG_TYS mtyps
159 where ih_mitem.server_group_id =  p_server_group_id
160 and   ih_mitem.classification = p_classification
161 and   ih_mitem.active = 'N'
162 and   TRUNC(ih_mitem.start_date_time) = p_period_start_date
163 and   LPAD(TO_CHAR(ih_mitem.start_date_time,'HH24:'),3,'0')|| DECODE(SIGN(TO_NUMBER(TO_CHAR(ih_mitem.start_date_time,'MI'))-29),0,'00',1,'30',-1,'00') = p_period_start_time
164 and 	TO_DATE(TO_CHAR(ih_mitem.start_date_time,'YYYY/MM/DD ')||LPAD(TO_CHAR(ih_mitem.start_date_time,'HH24:'),3,'0')
165 || DECODE(SIGN(TO_NUMBER(TO_CHAR(ih_mitem.start_date_time,'MI'))-29),0,'00',1,'30',-1,'00'),'YYYY/MM/DD HH24:MI') = p_period_start_date_time
166 and  ih_mitem.media_id = msegs.media_id
167 and  mtyps.milcs_type_id = msegs.milcs_type_id
168 and  mtyps.milcs_code = 'WITH_AGENT'
169 and  ih_mitem.direction = 'INBOUND';
170 
171 CURSOR goal_default_c
172 IS  SELECT goals.SL_SECONDS_GOAL goal from bix_dm_goals goals
173 where goals.call_type_id = -999
174 and  goals.end_date_active is null;
175 
176 CURSOR goal_c
177 IS   SELECT goals.SL_SECONDS_GOAL goal from bix_dm_goals goals
178 where goals.call_type_id = v_classification_id
179 and  goals.end_date_active is null;
180 
181 BEGIN
182 l_start_date := trunc(sysdate);
183 l_end_date   := sysdate;
184 delete from bix_dm_real_queue_sum
185 where session_id = p_session_id;
186 
187 FOR call in call_info LOOP
188     v_calls_offered := call.calls_offered;
189     v_calls_abandoned := call.calls_abandoned;
190     v_period_start_date := call.period_start_date;
191     v_period_start_time := call.period_start_time;
192     v_period_start_date_time := call.period_start_date_time;
193     v_classification := call.classification;
194     v_server_group_id := call.server_group_id;
195     v_classification_id := GET_CLASSIFICATION(v_classification);
196     v_default_goal := 30;
197     for defaultgoal in goal_default_c
198     LOOP
199           v_default_goal := defaultgoal.goal;
200     END LOOP;
201     v_goal := v_default_goal;
202     for goalclass in goal_c
203     LOOP
204           v_goal := goalclass.goal;
205     END LOOP;
206     for calldata1 in call_data1(v_server_group_id, v_classification,
207 	     v_period_start_date, v_period_start_time, v_period_start_date_time)
208     LOOP
209              v_abandon_time := calldata1.abandon_time;
210     end LOOP;
211     for calldata2 in call_data2(v_server_group_id, v_classification, v_classification_id ,
212 	     v_period_start_date, v_period_start_time, v_period_start_date_time)
213     LOOP
214              v_calls_answrd_within_x_time := calldata2.calls_answrd_within_x_time;
215     end LOOP;
216     for calldata3 in call_data3(v_server_group_id, v_classification,
217 	     v_period_start_date, v_period_start_time, v_period_start_date_time)
218     LOOP
219              v_queue_time_answered := calldata3.queue_time_answered;
220              v_calls_answered := calldata3.calls_answered;
221     end LOOP;
222     for calldata4 in call_data4(v_server_group_id, v_classification,
223 	     v_period_start_date, v_period_start_time, v_period_start_date_time)
224     LOOP
225              v_talk_time := calldata4.talk_time;
226              v_calls_handled := calldata4.calls_handled;
227     end LOOP;
228 
229     insert into BIX_DM_REAL_QUEUE_SUM
230 		(
231 		calls_offered,
232 		calls_abandoned,
233 		abandon_time,
234 		talk_time,
235 		calls_answrd_within_x_time,
236 		queue_time_answered,
237 		calls_answered,
238 		calls_handled,
239 		server_group_id,
240 		classification,
241 		classification_id,
242 		period_start_date,
243 		period_start_time,
244 		period_start_date_time,
245 		session_id
246 		)
247 		values
248 		(
249 		v_calls_offered,
250 		v_calls_abandoned,
251 		v_abandon_time,
252 		decode(v_talk_time, NULL, 0, v_talk_time),
253 		decode(v_calls_answrd_within_x_time, NULL, 0, v_calls_answrd_within_x_time),
254 		v_queue_time_answered,
255 		v_calls_answered,
256 		v_calls_handled,
257 		v_server_group_id,
258 		v_classification,
259 		v_classification_id,
260 		v_period_start_date,
261 		v_period_start_time,
262 		v_period_start_date_time,
263 		p_session_id
264 		);
265   END LOOP;
266 COMMIT;
267 END  GET_CALLS;
268 END BIX_DM_REAL_QUEUE_SUMMARY_PKG;