[Home] [Help]
PACKAGE BODY: APPS.BIX_PMV_EMC_AGTDET_RPT_PKG
Source
1 PACKAGE BODY BIX_PMV_EMC_AGTDET_RPT_PKG AS
2 /*$Header: bixead1r.plb 120.1 2005/09/14 16:24:03 anasubra noship $ */
3
4 PROCEDURE GET_SQL(p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL,
5 p_sql_text OUT NOCOPY VARCHAR2,
6 p_custom_output OUT NOCOPY bis_query_attributes_TBL
7 )
8 AS
9 l_sqltext VARCHAR2(32000) ;
10 l_where_clause VARCHAR2(1000) ;
11 l_where_clause2 VARCHAR2(1000) ;
12 l_ses_where_clause VARCHAR2(1000);
13 l_as_of_date DATE;
14 l_period_type varchar2(2000);
15 l_comp_type varchar2(2000);
16 l_sql_errm varchar2(32000);
17 l_agent_cost NUMBER;
18 l_custom_rec BIS_QUERY_ATTRIBUTES;
19 l_period_type_id NUMBER := 1;
20 l_record_type_id NUMBER ;
21 l_account VARCHAR2(32000);
22 l_start_date DATE;
23 l_end_date DATE;
24 l_period_from DATE;
25 l_period_to DATE;
26 l_period_start_date DATE;
27 l_classification VARCHAR2(32000);
28 l_view_by varchar2(1000);
29 l_agent_group VARCHAR2(1000);
30 l_application_id NUMBER := 680;
31 l_row_type VARCHAR2(10) := 'ACR';
32
33 BEGIN
34
35 --
36 --Initialize p_custom_output
37 --
38
39 p_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
40 l_custom_rec := BIS_PMV_PARAMETERS_PUB.Initialize_Query_Type;
41
42 -- Get the parameters
43
44 BIX_PMV_DBI_UTL_PKG.get_emc_page_params( p_page_parameter_tbl,
45 l_as_of_date,
46 l_period_type,
47 l_record_type_id,
48 l_comp_type,
49 l_account,
50 l_classification,
51 l_view_by
52 );
53
54
55 IF (p_page_parameter_tbl.count > 0) THEN
56
57 FOR i IN p_page_parameter_tbl.first..p_page_parameter_tbl.last LOOP
58
59 IF p_page_parameter_tbl(i).parameter_name = 'ORGANIZATION+JTF_ORG_SUPPORT_GROUP' THEN
60 l_agent_group := p_page_parameter_tbl(i).parameter_id;
61 END IF;
62
63 END LOOP;
64 END IF;
65
66
67 -- If the account is not 'All'
68
69 IF l_account IS NOT NULL THEN
70 l_where_clause := 'AND email_account_id IN (:l_account) ';
71 l_where_clause2 := 'WHERE email_account_id IN (:l_account) ';
72 END IF;
73
74 IF l_classification IS NOT NULL THEN
75 l_where_clause := l_where_clause || ' AND email_classification_id IN (:l_classification) ';
76 END IF;
77
78 IF l_agent_group IS NOT NULL THEN
79 l_where_clause := l_where_clause || ' AND EXISTS (
80 SELECT 1
81 FROM jtf_rs_group_members mem
82 WHERE fact.agent_id = mem.resource_id
83 AND mem.group_id IN (:l_agent_group)
84 AND nvl(mem.delete_flag, ''N'') <> ''Y'' ) ';
85
86
87 l_ses_where_clause := ' AND EXISTS (
88 SELECT 1
89 FROM jtf_rs_group_members mem
90 WHERE fact.agent_id = mem.resource_id
91 AND mem.group_id IN (:l_agent_group)
92 AND nvl(mem.delete_flag, ''N'') <> ''Y'' ) ';
93 IF l_account IS NOT NULL THEN
94 l_where_clause2 := l_where_clause2 || ' AND resource_id IN (
95 SELECT mem.resource_id
96 FROM jtf_rs_group_members mem
97 WHERE mem.group_id IN (:l_agent_group)
98 AND nvl(mem.delete_flag, ''N'') <> ''Y'' ) ';
99 ELSE
100
101 l_where_clause2 := ' WHERE resource_id IN (
102 SELECT mem.resource_id
103 FROM jtf_rs_group_members mem
104 WHERE mem.group_id IN (:l_agent_group)
105 AND nvl(mem.delete_flag, ''N'') <> ''Y'' ) ';
106 END IF;
107 END IF;
108
109
110
111 l_sqltext :=
112 'SELECT
113 vl.resource_name BIX_AGENT,
114 nvl(sum(CURR_INBOX),0) BIX_EMC_RECCOUNT,
115 nvl(sum(sum(CURR_INBOX)) over(),0) BIX_PMV_TOTAL1,
116 nvl(sum(CURR_FETCH),0) BIX_EMC_FETCH,
117 nvl(sum(sum(CURR_FETCH)) over(),0) BIX_PMV_TOTAL2,
118 nvl(sum(CURR_TRANIN),0) BIX_EMC_TRANIN,
119 nvl(sum(sum(CURR_TRANIN)) over(),0) BIX_PMV_TOTAL3,
120 nvl(sum(CURR_ASSIGN),0) BIX_EMC_ASSIGN,
121 nvl(sum(sum(CURR_ASSIGN)) over(),0) BIX_PMV_TOTAL4,
122 nvl(sum(CURR_AUTO),0) BIX_EMC_AUTOROUTE,
123 nvl(sum(sum(CURR_AUTO)) over(),0) BIX_PMV_TOTAL5,
124 nvl(sum(CURR_PROC),0) BIX_EMC_PROCECOUNT,
125 nvl(sum(sum(CURR_PROC)) over(),0) BIX_PMV_TOTAL6,
126 nvl(sum(CURR_REPLD),0) BIX_EMC_REPLD,
127 nvl(sum(sum(CURR_REPLD)) over(),0) BIX_PMV_TOTAL7,
128 nvl(sum(PREV_REPLD),0) BIX_EMC_PRREPLD,
129 nvl(sum(CURR_DEL),0) BIX_EMC_DELETED,
130 nvl(sum(sum(CURR_DEL)) over(),0) BIX_PMV_TOTAL8,
131 nvl(sum(CURR_TRANOUT),0) BIX_EMC_TRANOUT,
132 nvl(sum(sum(CURR_TRANOUT)) over(),0) BIX_PMV_TOTAL9,
133 nvl(sum(CURR_REROUTED),0) BIX_EMC_REROUTED,
134 nvl(sum(sum(CURR_REROUTED)) OVER(),0) BIX_PMV_TOTAL16,
135 nvl(sum(CURR_MSGSGOAL),0)*100/sum(CURR_REPLD) BIX_EMC_MSGSGOAL_CP,
136 nvl(sum(sum(CURR_MSGSGOAL)) over(),0)*100/
137 sum(sum(CURR_REPLD)) over() BIX_PMV_TOTAL14,
138 (nvl(sum(CURR_MSGSGOAL),0)*100/sum(CURR_REPLD)) -
139 (nvl(sum(sum(CURR_MSGSGOAL)) over(),0)*100/(sum(sum(CURR_REPLD)) over())) BIX_EMC_AGCOMP1,
140 nvl(sum(PREV_MSGSGOAL),0)*100/sum(PREV_REPLD) BIX_EMC_PREVMSGSGOAL,
141 (NVL(SUM(curr_repld),0)/DECODE(SUM(curr_login_time),0,NULL,sum(curr_login_time))) BIX_EMC_REPPERHR_CP,
142 (NVL(SUM(SUM(curr_repld)) OVER(),0)/DECODE(SUM(SUM(curr_login_time)) OVER(),0,NULL,SUM(sum(curr_login_time)) OVER() )) BIX_PMV_TOTAL17,
143 (NVL(SUM(curr_repld),0)/DECODE(SUM(curr_login_time),0,NULL,sum(curr_login_time)) ) -
144 ( NVL(SUM(SUM(curr_repld)) OVER(),0)/DECODE(SUM(SUM(curr_login_time)) OVER(),0,NULL,SUM(sum(curr_login_time)) OVER() ) ) BIX_EMC_AGCOMP3,
145 NVL(SUM(prev_repld),0)/DECODE(SUM(prev_login_time),0,NULL,sum(prev_login_time)) BIX_EMC_PREVREPPERHR,
146 nvl(sum(CURR_ARTIME),0)/(3600*sum(CURR_REPLD)) BIX_EMC_ARTIME_CP,
147 nvl(sum(sum(CURR_ARTIME)) over(),0)/
148 (3600*sum(sum(CURR_REPLD)) over()) BIX_PMV_TOTAL15,
149 (nvl(sum(CURR_ARTIME),0)/(3600*sum(CURR_REPLD)))- (nvl(sum(sum(CURR_ARTIME)) over(),0)/(3600*sum(sum(CURR_REPLD)) over())) BIX_EMC_AGCOMP2,
150 nvl(sum(CURR_SR),0) BIX_EMC_SR_CP,
151 nvl(sum(sum(CURR_SR)) over(),0) BIX_PMV_TOTAL10,
152 nvl(sum(CURR_SR),0)*100/sum(sum(CURR_SR)) over() BIX_EMC_PERTOTAL1,
153 nvl(sum(CURR_LEADS),0) BIX_EMC_LEADS_CP,
154 nvl(sum(sum(CURR_LEADS)) over(),0) BIX_PMV_TOTAL18,
155 nvl(sum(CURR_LEADS),0)*100/sum(sum(CURR_LEADS)) over() BIX_EMC_PERTOTAL2,
156 (NVL(SUM(curr_repld),0)/DECODE(SUM(curr_login_time),0,NULL,sum(curr_login_time))) BIX_CALC_ITEM1,
157 (NVL(SUM(SUM(curr_repld)) OVER(),0)/DECODE(SUM(SUM(curr_login_time)) OVER(),0,NULL,SUM(sum(curr_login_time)) OVER() )) BIX_CALC_ITEM2,
158 (NVL(SUM(prev_repld),0)/DECODE(SUM(prev_login_time),0,NULL,sum(prev_login_time))) BIX_CALC_ITEM3,
159 (NVL(SUM(SUM(prev_repld)) OVER(),0)/DECODE(SUM(SUM(prev_login_time)) OVER(),0,NULL,SUM(sum(prev_login_time)) OVER() )) BIX_CALC_ITEM4
160 FROM (
161 SELECT agent_id AGENT_ID,
162 sum(decode(cal.report_date,&BIS_CURRENT_ASOF_DATE,
163 nvl(EMAILS_FETCHED_IN_PERIOD,0)+nvl(EMAILS_TRNSFRD_IN_IN_PERIOD,0)+
164 nvl(EMAILS_AUTO_ROUTED_IN_PERIOD,0)+nvl(EMAILS_ASSIGNED_IN_PERIOD,0),NULL)) CURR_INBOX,
165 sum(decode(cal.report_date,&BIS_CURRENT_ASOF_DATE,
166 EMAILS_FETCHED_IN_PERIOD,NULL)) CURR_FETCH,
167 sum(decode(cal.report_date,&BIS_CURRENT_ASOF_DATE,
168 EMAILS_TRNSFRD_IN_IN_PERIOD,NULL)) CURR_TRANIN,
169 sum(decode(cal.report_date,&BIS_CURRENT_ASOF_DATE,
170 EMAILS_ASSIGNED_IN_PERIOD,NULL)) CURR_ASSIGN,
171 sum(decode(cal.report_date,&BIS_CURRENT_ASOF_DATE,
172 EMAILS_AUTO_ROUTED_IN_PERIOD,NULL)) CURR_AUTO,
173 sum(decode(cal.report_date,&BIS_CURRENT_ASOF_DATE,
174 nvl(EMAILS_REPLIED_IN_PERIOD,0)+nvl(EMAILS_TRNSFRD_OUT_IN_PERIOD,0)+
175 nvl(EMAILS_DELETED_IN_PERIOD,0)+nvl(EMAILS_REROUTED_IN_PERIOD,0) ,NULL)) CURR_PROC,
176 sum(decode(cal.report_date,&BIS_CURRENT_ASOF_DATE,
177 EMAILS_REPLIED_IN_PERIOD,NULL)) CURR_REPLD,
178 sum(decode(cal.report_date,&BIS_PREVIOUS_ASOF_DATE,
179 EMAILS_REPLIED_IN_PERIOD,NULL)) PREV_REPLD,
180 sum(decode(cal.report_date,&BIS_CURRENT_ASOF_DATE,
181 EMAILS_DELETED_IN_PERIOD,NULL)) CURR_DEL,
182 sum(decode(cal.report_date,&BIS_CURRENT_ASOF_DATE,
183 EMAILS_TRNSFRD_OUT_IN_PERIOD,NULL)) CURR_TRANOUT,
184 sum(decode(cal.report_date,&BIS_CURRENT_ASOF_DATE,
185 EMAILS_REROUTED_IN_PERIOD,NULL)) CURR_REROUTED,
186 sum(decode(cal.report_date,&BIS_CURRENT_ASOF_DATE,
187 AGENT_EMAILS_RPLD_BY_GOAL,NULL)) CURR_MSGSGOAL,
188 sum(decode(cal.report_date,&BIS_PREVIOUS_ASOF_DATE,
189 AGENT_EMAILS_RPLD_BY_GOAL,NULL)) PREV_MSGSGOAL,
190 sum(decode(cal.report_date,&BIS_CURRENT_ASOF_DATE,
191 AGENT_RESP_TIME_IN_PERIOD,NULL)) CURR_ARTIME,
192 sum(decode(cal.report_date,&BIS_CURRENT_ASOF_DATE,
193 SR_CREATED_IN_PERIOD,NULL)) CURR_SR,
194 sum(decode(cal.report_date,&BIS_CURRENT_ASOF_DATE,
195 LEADS_CREATED_IN_PERIOD,NULL)) CURR_LEADS,
196 NULL CURR_LOGIN_TIME,
197 NULL PREV_LOGIN_TIME
198 FROM bix_email_details_mv fact,
199 fii_time_rpt_struct cal
200 WHERE fact.time_id = cal.time_id
201 AND fact.row_type = :l_row_type
202 AND fact.period_type_id = cal.period_type_id
203 AND cal.report_date IN (&BIS_CURRENT_ASOF_DATE, &BIS_PREVIOUS_ASOF_DATE)
204 AND bitand(cal.record_type_id,&BIS_NESTED_PATTERN)=cal.record_type_id '
205 || l_where_clause ||
206 ' GROUP BY agent_id
207 UNION ALL
208 SELECT agent_id,
209 NULL CURR_INBOX,
210 NULL CURR_FETCHED,
211 NULL CURR_TRANIN,
212 NULL CURR_ASSIGN,
213 NULL CURR_AUTO,
214 NULL CURR_PROC,
215 NULL CURR_REPLD,
216 NULL PREV_REPLD,
217 NULL CURR_DEL,
218 NULL CURR_TRANOUT,
219 NULL CURR_REROUTED,
220 NULL CURR_MSGSGOAL,
221 NULL PREV_MSGSGOAL,
222 NULL CURR_ARTIME,
223 NULL CURR_SR,
224 NULL CURR_LEADS ,
225 SUM(DECODE(cal.report_date,&BIS_CURRENT_ASOF_DATE,LOGIN_TIME))/3600 CURR_LOGIN_TIME_IN_PERIOD,
226 SUM(DECODE(cal.report_date,&BIS_PREVIOUS_ASOF_DATE,LOGIN_TIME))/3600 PREV_LOGIN_TIME_IN_PERIOD
227 FROM bix_agent_session_f fact,
228 fii_time_rpt_struct cal
229 WHERE fact.application_id = :l_application_id
230 AND fact.time_id = cal.time_id
231 AND fact.period_type_id = cal.period_type_id
232 AND cal.report_date IN (&BIS_CURRENT_ASOF_DATE,&BIS_PREVIOUS_ASOF_DATE)
233 AND bitand(cal.record_type_id,&BIS_NESTED_PATTERN) = cal.record_type_id ' || l_ses_where_clause ||
234 ' GROUP BY agent_id
235 UNION ALL
236 SELECT resource_id AGENT_ID,
237 NULL CURR_INBOX,
238 NULL CURR_FETCHED,
239 NULL CURR_TRANIN,
240 NULL CURR_ASSIGN,
241 NULL CURR_AUTO,
242 NULL CURR_PROC,
243 NULL CURR_REPLD,
244 NULL PREV_REPLD,
245 NULL CURR_DEL,
246 NULL CURR_TRANOUT,
247 NULL CURR_REROUTED,
248 NULL CURR_MSGSGOAL,
249 NULL PREV_MSGSGOAL,
250 NULL CURR_ARTIME,
251 NULL CURR_SR,
252 NULL CURR_LEADS ,
253 NULL CURR_LOGIN_TIME,
254 NULL PREV_LOGIN_TIME
255 from iem_agents '
256 || l_where_clause2 ||
257 ' ) summ,
258 jtf_rs_resource_extns_vl vl
259 WHERE summ.agent_id = vl.resource_id
260 GROUP BY vl.resource_id, vl.resource_name ';
261
262 --START 001
263
264 l_sqltext := 'SELECT * FROM ( '||l_sqltext ||' ) WHERE
265 ABS(NVL(BIX_EMC_RECCOUNT ,0))+ABS(NVL(BIX_EMC_FETCH ,0))+ABS(NVL(BIX_EMC_TRANIN,0))+ABS(NVL(BIX_EMC_ASSIGN ,0))+
266 ABS(NVL(BIX_EMC_AUTOROUTE ,0))+ABS(NVL(BIX_EMC_PROCECOUNT ,0))+ABS(NVL(BIX_EMC_REPLD,0))+ABS(NVL(BIX_EMC_DELETED ,0))+
267 ABS(NVL(BIX_EMC_TRANOUT ,0))+ABS(NVL(BIX_EMC_REROUTED ,0))+ABS(NVL(BIX_EMC_MSGSGOAL_CP ,0))+ABS(NVL(BIX_EMC_AGCOMP1 ,0))+
268 ABS(NVL(BIX_EMC_REPPERHR_CP ,0))+ABS(NVL(BIX_EMC_AGCOMP3 ,0))+ABS(NVL(BIX_EMC_ARTIME_CP ,0))+ABS(NVL(BIX_EMC_AGCOMP2,0))+
269 +ABS(NVL(BIX_EMC_SR_CP,0))+ABS(NVL(BIX_EMC_PERTOTAL1,0))+ABS(NVL(BIX_EMC_LEADS_CP,0))+ABS(NVL(BIX_EMC_PERTOTAL2,0))
270 !=0 &ORDER_BY_CLAUSE ';
271
272 --END 001
273
274 p_sql_text := l_sqltext;
275
276 -- Insert account Bind Variable
277
278 l_custom_rec.attribute_name := ':l_application_id';
279 l_custom_rec.attribute_value:= l_application_id;
280 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
281 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.NUMERIC_BIND;
282
283 p_custom_output.EXTEND;
284 p_custom_output(p_custom_output.COUNT) := l_custom_rec;
285
286 IF ( l_account IS NOT NULL) THEN
287 l_custom_rec.attribute_name := ':l_account' ;
288 l_custom_rec.attribute_value:= l_account;
289 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
290 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
291
292 p_custom_output.Extend();
293 p_custom_output(p_custom_output.count) := l_custom_rec;
294 END IF;
295
296 IF ( l_classification IS NOT NULL) THEN
297 l_custom_rec.attribute_name := ':l_classification' ;
298 l_custom_rec.attribute_value:= l_classification;
299 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
300 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
301
302 p_custom_output.Extend();
303 p_custom_output(p_custom_output.count) := l_custom_rec;
304 END IF;
305
306
307 IF ( l_agent_group IS NOT NULL) THEN
308 l_custom_rec.attribute_name := ':l_agent_group' ;
309 l_custom_rec.attribute_value:= l_agent_group;
310 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
311 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
312
313 p_custom_output.Extend();
314 p_custom_output(p_custom_output.count) := l_custom_rec;
315 END IF;
316
317 l_custom_rec.attribute_name := ':l_row_type' ;
318 l_custom_rec.attribute_value:= l_row_type;
319 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
320 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
321
322 p_custom_output.Extend();
323 p_custom_output(p_custom_output.count) := l_custom_rec;
324
325 EXCEPTION
326 WHEN OTHERS THEN
327 NULL;
328 END GET_SQL;
329 END BIX_PMV_EMC_AGTDET_RPT_PKG;