[Home] [Help]
PACKAGE BODY: APPS.BIX_PMV_EMC_ERPT_RPT_PKG
Source
1 PACKAGE BODY BIX_PMV_EMC_ERPT_RPT_PKG AS
2 /*$Header: bixerptr.plb 120.0 2005/05/25 17:16:32 appldev 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_as_of_date DATE;
12 l_period_type varchar2(2000);
13 l_comp_type varchar2(2000);
14 l_sql_errm varchar2(32000);
15 l_agent_cost NUMBER;
16 l_custom_rec BIS_QUERY_ATTRIBUTES;
17 l_period_type_id NUMBER := 1;
18 l_record_type_id NUMBER ;
19 l_account VARCHAR2(32000);
20 l_start_date DATE;
21 l_end_date DATE;
22 l_period_from DATE;
23 l_period_to DATE;
24 l_max_collect_date VARCHAR2(100);
25 l_period_start_date DATE;
26 l_application_id NUMBER := 680;
27 l_classification VARCHAR2(32000);
28 l_view_by varchar2(1000);
29 l_row_type varchar2(10) := 'AC';
30
31 BEGIN
32
33 --
34 --Initialize p_custom_output
35 --
36
37 p_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
38 l_custom_rec := BIS_PMV_PARAMETERS_PUB.Initialize_Query_Type;
39
40 -- Get the parameters
41
42 BIX_PMV_DBI_UTL_PKG.get_emc_page_params( p_page_parameter_tbl,
43 l_as_of_date,
44 l_period_type,
45 l_record_type_id,
46 l_comp_type,
47 l_account,
48 l_classification,
49 l_view_by
50 );
51
52
53 -- If the account is not 'All'
54
55 IF l_account IS NOT NULL THEN
56 l_where_clause := 'AND email_account_id IN (:l_account) ';
57 END IF;
58
59
60 IF l_classification IS NOT NULL THEN
61 l_where_clause := l_where_clause || ' AND email_classification_id IN (:l_classification) ';
62 END IF;
63
64 /* Get the MAX date for which data is collected in Email Summary table */
65
66 BIS_COLLECTION_UTILITIES.get_last_refresh_dates('BIX_EMAIL_DETAILS_F',
67 l_start_date,
68 l_end_date,
69 l_period_from,
70 l_period_to
71 );
72
73
74 l_period_start_Date := BIX_PMV_DBI_UTL_PKG.period_start_date(l_as_of_date,l_period_type);
75
76 /* Get the MAX date for which data is collected in Email Summary table */
77
78 BIS_COLLECTION_UTILITIES.get_last_refresh_dates('BIX_EMAIL_DETAILS_F',
79 l_start_date,
80 l_end_date,
81 l_period_from,
82 l_period_to
83 );
84
85
86 /* if the data is not collected till AS OF DATE
87 then get the accumulated measures from the MAX collected DATE time bucket
88 if the max collect date falls between current period */
89
90 IF (NVL(l_period_to,l_as_of_date) >= l_as_of_date ) THEN
91 l_max_collect_date := TO_CHAR(l_as_of_date,'DD/MM/YYYY');
92 ELSIF ( l_period_to < l_period_start_date ) THEN
93 l_max_collect_date := TO_CHAR(l_as_of_date,'DD/MM/YYYY');
94 ELSE
95 l_max_collect_date := TO_CHAR(l_period_to,'DD/MM/YYYY');
96 END IF;
97
98
99 l_sqltext :=
100 '
101 SELECT * from
102 (
103 SELECT lookup_table.value VIEWBY,
104 lookup_table.id VIEWBYID,
105 nvl(sum(CURR_MSGSGOAL),0)*100/DECODE(sum(CURR_TOT_REPLD),0,NULL,sum(CURR_TOT_REPLD)) BIX_EMC_MSGSGOAL_CP
106 ,
107 nvl(sum(CURR_MSGSGOAL),0)*100/DECODE(sum(CURR_TOT_REPLD),0,NULL,sum(CURR_TOT_REPLD)) BIX_CALC_ITEM1,
108 nvl(sum(sum(CURR_MSGSGOAL)) over(),0)*100/
109 DECODE(sum(sum(CURR_TOT_REPLD)) over(),0,NULL,sum(sum(CURR_TOT_REPLD)) over()) BIX_PMV_TOTAL1,
110 nvl(sum(sum(CURR_MSGSGOAL)) over(),0)*100/
111 DECODE(sum(sum(CURR_TOT_REPLD)) over(),0,NULL,sum(sum(CURR_TOT_REPLD)) over()) BIX_CALC_ITEM2,
112 100*
113 (
114 (nvl(sum(CURR_MSGSGOAL),0)/DECODE(sum(CURR_TOT_REPLD),0,NULL,sum(CURR_TOT_REPLD))) -
115 (nvl(sum(PREV_MSGSGOAL),0)/DECODE(sum(PREV_TOT_REPLD),0,NULL,sum(PREV_TOT_REPLD)))
116 )
117 BIX_EMC_MGCHANGE,
118 100*
119 (
120 (nvl(sum(sum(CURR_MSGSGOAL)) over(),0)/DECODE(sum(sum(CURR_TOT_REPLD)) over(),0,NULL,sum(sum(CURR_TOT_REPLD)) over()))-
121 (nvl(sum(sum(PREV_MSGSGOAL)) over(),0)/DECODE(sum(sum(PREV_TOT_REPLD)) over(),0,NULL,sum(sum(PREV_TOT_REPLD)) over()))
122 )
123 BIX_PMV_TOTAL2,
124 nvl(sum(PREV_MSGSGOAL),0)*100/DECODE(sum(PREV_TOT_REPLD),0,NULL,sum(PREV_TOT_REPLD)) BIX_EMC_PREVMSGSGOAL,
125 nvl(sum(PREV_MSGSGOAL),0)*100/DECODE(sum(PREV_TOT_REPLD),0,NULL,sum(PREV_TOT_REPLD)) BIX_CALC_ITEM3,
126 nvl(sum(sum(PREV_MSGSGOAL)) over(),0)*100/DECODE(sum(sum(PREV_TOT_REPLD)) over(),0,NULL,sum(sum(PREV_TOT_REPLD))
127 over()) BIX_CALC_ITEM4,
128 nvl(sum(CURR_TOT_REPLD),0) BIX_EMC_REPLD_CP,
129 nvl(sum(sum(CURR_TOT_REPLD)) over(),0) BIX_PMV_TOTAL3,
130 (nvl(sum(CURR_TOT_REPLD),0) - sum(PREV_TOT_REPLD))*100/DECODE(sum(PREV_TOT_REPLD),0,NULL,sum(PREV_TOT_REPLD)) BIX_EMC_REPCHANGE,
131 (nvl(sum(sum(CURR_TOT_REPLD)) over(),0)-sum(sum(PREV_TOT_REPLD)) over())*100/
132 DECODE(sum(sum(PREV_TOT_REPLD)) over(),0,NULL,sum(sum(PREV_TOT_REPLD)) over()) BIX_PMV_TOTAL4,
133 nvl(sum(prev_tot_repld),0) BIX_EMC_PRREPLD,
134 nvl(sum(CURR_AUTO_REPLD),0)*100/
135 decode(SUM(CURR_TOT_REPLD),0,NULL,SUM(CURR_TOT_REPLD)) BIX_EMC_AUTO_RPLD_RATE_CP,
136 nvl(sum(sum(CURR_AUTO_REPLD)) over(),0)*100/
137 decode(SUM(SUM(CURR_TOT_REPLD)) OVER(),0,NULL,SUM(SUM(CURR_TOT_REPLD)) OVER()) BIX_PMV_TOTAL11,
138 ((nvl(sum(CURR_AUTO_REPLD),0)/ decode(SUM(CURR_TOT_REPLD),0,NULL,SUM(CURR_TOT_REPLD))) -
139 (sum(PREV_AUTO_REPLD)/ decode(SUM(PREV_TOT_REPLD),0,NULL,SUM(PREV_TOT_REPLD))))*100 BIX_EMC_AUTO_PRLD_CHANGE,
140 ((nvl(SUM(sum(CURR_AUTO_REPLD)) OVER(),0)/ decode(SUM(SUM(CURR_TOT_REPLD)) OVER(),0,NULL,SUM(SUM(CURR_TOT_REPLD)) OVER())) -
141 (SUM(sum(PREV_AUTO_REPLD))OVER()/ decode(SUM(SUM(PREV_TOT_REPLD)) OVER(),0,NULL,SUM(SUM(PREV_TOT_REPLD)) OVER())))*100 BIX_PMV_TOTAL13,
142 NVL(SUM(CURR_TRFD),0)*100/DECODE(SUM(CURR_RESOLVED),0,NULL,SUM(CURR_RESOLVED)) BIX_EMC_TRANRATIO_CP,
143 NVL(SUM(SUM(CURR_TRFD)) OVER(),0)*100/
144 DECODE(SUM(SUM(CURR_RESOLVED)) OVER(),0,NULL,SUM(SUM(CURR_RESOLVED)) OVER()) BIX_PMV_TOTAL12,
145
146 ((NVL(SUM(CURR_TRFD),0)/DECODE(SUM(CURR_RESOLVED),0,NULL,SUM(CURR_RESOLVED))) -
147 (SUM(PREV_TRFD)/DECODE(SUM(PREV_RESOLVED),0,NULL,SUM(PREV_RESOLVED)))) * 100
148 BIX_EMC_TRATIOCHANGE,
149
150 ((NVL(SUM(SUM(CURR_TRFD)) OVER(),0)/DECODE(SUM(SUM(CURR_RESOLVED)) OVER(),0,NULL,SUM(SUM(CURR_RESOLVED)) OVER())) -
151 (SUM(SUM(PREV_TRFD)) OVER()/DECODE(SUM(SUM(PREV_RESOLVED)) OVER(),0,NULL,SUM(SUM(PREV_RESOLVED)) OVER()))) * 100
152 BIX_PMV_TOTAL14,
153 nvl(sum(CURR_CRTIME),0)/(3600*DECODE(sum(CURR_TOT_REPLD),0,NULL,sum(CURR_TOT_REPLD))) BIX_EMC_CRTIME_CP,
154 nvl(sum(CURR_CRTIME),0)/(3600*DECODE(sum(CURR_TOT_REPLD),0,NULL,sum(CURR_TOT_REPLD))) BIX_CALC_ITEM5,
155 nvl(sum(sum(CURR_CRTIME)) over(),0)/
156 (3600*DECODE(sum(sum(CURR_TOT_REPLD)) over(),0,NULL,sum(sum(CURR_TOT_REPLD)) over())) BIX_PMV_TOTAL5,
157 nvl(sum(sum(CURR_CRTIME)) over(),0)/
158 (3600*DECODE(sum(sum(CURR_TOT_REPLD)) over(),0,NULL,sum(sum(CURR_TOT_REPLD)) over())) BIX_CALC_ITEM6,
159 ((nvl(sum(CURR_CRTIME),0)/DECODE(sum(CURR_TOT_REPLD),0,NULL,sum(CURR_TOT_REPLD)))
160 - (sum(PREV_CRTIME)/DECODE(sum(PREV_TOT_REPLD),0,NULL,sum(PREV_TOT_REPLD))))*100/
161 (sum(PREV_CRTIME)/DECODE(sum(PREV_TOT_REPLD),0,NULL,sum(PREV_TOT_REPLD))) BIX_EMC_CRCHANGE,
162 ((nvl(sum(sum(CURR_CRTIME)) OVER(),0)/DECODE(sum(sum(CURR_TOT_REPLD)) OVER(),0,NULL,sum(sum(CURR_TOT_REPLD)) OVER()))
163 - (sum(sum(PREV_CRTIME)) OVER()/DECODE(sum(sum(PREV_TOT_REPLD)) OVER(),0,NULL,sum(sum(PREV_TOT_REPLD)) OVER())))*100/
164 (sum(sum(PREV_CRTIME)) OVER()/DECODE(sum(sum(PREV_TOT_REPLD)) OVER(),0,NULL,sum(sum(PREV_TOT_REPLD)) OVER())) BIX_PMV_TOTAL6,
165 nvl(sum(PREV_CRTIME),0)/(3600*DECODE(sum(PREV_TOT_REPLD),0,NULL,sum(PREV_TOT_REPLD))) BIX_EMC_PRCRTIME,
166 nvl(sum(PREV_CRTIME),0)/(3600*DECODE(sum(PREV_TOT_REPLD),0,NULL,sum(PREV_TOT_REPLD))) BIX_CALC_ITEM7,
167 sum(sum(PREV_CRTIME)) over()/
168 (3600*DECODE(sum(sum(PREV_TOT_REPLD)) over(),0,NULL,sum(sum(PREV_TOT_REPLD)) over())) BIX_CALC_ITEM8,
169 nvl(sum(CURR_ARTIME),0)/(3600*sum(CURR_REPLD)) BIX_EMC_ARTIME_CP,
170 nvl(sum(sum(CURR_ARTIME)) over(),0)/(3600*sum(sum(CURR_REPLD)) over()) BIX_PMV_TOTAL7,
171 ((nvl(sum(CURR_ARTIME),0)/(3600*sum(CURR_REPLD))) - (nvl(sum(PREV_ARTIME),0)/(3600*sum(PREV_REPLD))))*100 /
172 (nvl(sum(PREV_ARTIME),0)/(3600*sum(PREV_REPLD))) BIX_EMC_ARCHANGE,
173 ((nvl(sum(sum(CURR_ARTIME)) over(),0)/(3600*sum(sum(CURR_REPLD)) over())) - (nvl(sum(sum(PREV_ARTIME)) over(),0)/(3600*sum(sum(PREV_REPLD)) over())))*100 /
174 (nvl(sum(sum(PREV_ARTIME)) over(),0)/(3600*sum(sum(PREV_REPLD)) over())) BIX_PMV_TOTAL8,
175 nvl(sum(CURR_ONEDONE),0)*100/sum(CURR_THREADS) BIX_EMC_ONE_DONE_CP,
176 nvl(sum(CURR_ONEDONE),0)*100/sum(CURR_THREADS) BIX_CALC_ITEM9,
177 nvl(sum(sum(CURR_ONEDONE)) over(),0)*100/sum(sum(CURR_THREADS)) over() BIX_PMV_TOTAL9,
178 nvl(sum(sum(CURR_ONEDONE)) over(),0)*100/sum(sum(CURR_THREADS)) over() BIX_CALC_ITEM10,
179 nvl(sum(PREV_ONEDONE),0)*100/sum(PREV_THREADS) BIX_CALC_ITEM11,
180 nvl(sum(sum(PREV_ONEDONE)) over(),0)*100/sum(sum(PREV_THREADS)) over() BIX_CALC_ITEM12,
181 nvl(sum(CURR_ONEDONE),0)*100/sum(CURR_THREADS)- nvl(sum(PREV_ONEDONE),0)*100/sum(PREV_THREADS) BIX_EMC_ODCHANGE,
182 nvl(sum(sum(CURR_ONEDONE)) over(),0)*100/sum(sum(CURR_THREADS)) over()-
183 nvl(sum(sum(PREV_ONEDONE)) over(),0)*100/sum(sum(PREV_THREADS)) over() BIX_PMV_TOTAL10 ,
184 (NVL(SUM(curr_deleted),0)+ NVL(SUM(curr_auto_deleted),0))/ DECODE(SUM(curr_completed),0,NULL,SUM(curr_completed))*100 BIX_EMC_DELRATIO_CP,
185 (NVL(SUM(curr_deleted),0)+ NVL(SUM(curr_auto_deleted),0))/ DECODE(SUM(curr_completed),0,NULL,SUM(curr_completed))*100 BIX_CALC_ITEM13,
186 (((NVL(SUM(curr_deleted),0)+ NVL(SUM(curr_auto_deleted),0))/ DECODE(SUM(curr_completed),0,NULL,SUM(curr_completed))) -
187 ((NVL(SUM(prev_deleted),0)+ NVL(SUM(prev_auto_deleted),0))/ DECODE(SUM(prev_completed),0,NULL,SUM(prev_completed))))* 100 BIX_EMC_DRATIOCHANGE,
188 (NVL(SUM(SUM(curr_deleted)) OVER(),0) + NVL(SUM(SUM(curr_auto_deleted)) OVER(),0) )/
189 DECODE(SUM(SUM(curr_completed)) OVER(),0,NULL,SUM(SUM(curr_completed)) OVER())*100 BIX_PMV_TOTAL15,
190 (NVL(SUM(SUM(curr_deleted)) OVER(),0) + NVL(SUM(SUM(curr_auto_deleted)) OVER(),0) )/
191 DECODE(SUM(SUM(curr_completed)) OVER(),0,NULL,SUM(SUM(curr_completed)) OVER())*100 BIX_CALC_ITEM14,
192 (((NVL(SUM(SUM(curr_deleted)) OVER(),0)+ NVL(SUM(SUM(curr_auto_deleted)) OVER(),0))/ DECODE(SUM(SUM(curr_completed)) OVER(),0,NULL,SUM(SUM(curr_completed)) OVER())) -
193 ((NVL(SUM(SUM(prev_deleted)) OVER(),0)+ NVL(SUM(SUM(prev_auto_deleted)) OVER(),0))/ DECODE(SUM(SUM(prev_completed)) OVER(),0,NULL,SUM(SUM(prev_completed)) OVER())))* 100 BIX_PMV_TOTAL16,
194 (NVL(SUM(prev_deleted),0)+ NVL(SUM(prev_auto_deleted),0))/ DECODE(SUM(prev_completed),0,NULL,SUM(prev_completed))*100 BIX_CALC_ITEM15,
195 (NVL(SUM(SUM(prev_deleted)) OVER(),0) + NVL(SUM(SUM(prev_auto_deleted)) OVER(),0) )/
196 DECODE(SUM(SUM(prev_completed)) OVER(),0,NULL,SUM(SUM(prev_completed)) OVER())*100 BIX_CALC_ITEM16,
197 NVL(SUM(CURR_TRFD),0)*100/DECODE(SUM(CURR_RESOLVED),0,NULL,SUM(CURR_RESOLVED)) BIX_CALC_ITEM17,
198 NVL(SUM(SUM(CURR_TRFD)) OVER(),0)*100/
199 DECODE(SUM(SUM(CURR_RESOLVED)) OVER(),0,NULL,SUM(SUM(CURR_RESOLVED)) OVER()) BIX_CALC_ITEM18,
200 NVL(SUM(PREV_TRFD),0)*100/DECODE(SUM(PREV_RESOLVED),0,NULL,SUM(PREV_RESOLVED)) BIX_CALC_ITEM19,
201 SUM(SUM(PREV_TRFD)) OVER()*100/
202 DECODE(SUM(SUM(PREV_RESOLVED)) OVER(),0,NULL,SUM(SUM(PREV_RESOLVED)) OVER()) BIX_CALC_ITEM20
203 FROM ( ';
204
205 IF l_view_by = 'EMAIL ACCOUNT+EMAIL ACCOUNT' THEN
206 l_sqltext := l_sqltext || ' SELECT email_account_id ID,
207 sum(decode(cal.report_date,&BIS_CURRENT_ASOF_DATE,
208 EMAILS_RPLD_BY_GOAL_IN_PERIOD,NULL)) CURR_MSGSGOAL,
209 sum(decode(cal.report_date,&BIS_PREVIOUS_ASOF_DATE,
210 EMAILS_RPLD_BY_GOAL_IN_PERIOD,NULL)) PREV_MSGSGOAL,
211 sum(decode(cal.report_date,&BIS_CURRENT_ASOF_DATE,
212 EMAILS_REPLIED_IN_PERIOD,NULL)) CURR_REPLD,
213 sum(decode(cal.report_date,&BIS_PREVIOUS_ASOF_DATE,
214 EMAILS_REPLIED_IN_PERIOD,NULL)) PREV_REPLD,
215 sum(decode(cal.report_date,&BIS_CURRENT_ASOF_DATE,EMAILS_AUTO_REPLIED_IN_PERIOD,NULL)) CURR_AUTO_REPLD,
216 sum(decode(cal.report_date,&BIS_PREVIOUS_ASOF_DATE,EMAILS_AUTO_REPLIED_IN_PERIOD,NULL)) PREV_AUTO_REPLD,
217 SUM(decode(cal.report_date,&BIS_CURRENT_ASOF_DATE,
218 NVL(EMAILS_REPLIED_IN_PERIOD,0) + NVL(EMAILS_AUTO_REPLIED_IN_PERIOD,0),NULL)) CURR_TOT_REPLD,
219 SUM(decode(cal.report_date,&BIS_PREVIOUS_ASOF_DATE,
220 NVL(EMAILS_REPLIED_IN_PERIOD,0) + NVL(EMAILS_AUTO_REPLIED_IN_PERIOD,0),NULL)) PREV_TOT_REPLD,
221 sum(decode(cal.report_date,&BIS_CURRENT_ASOF_DATE,EMAILS_RSL_AND_TRFD_IN_PERIOD,NULL)) CURR_TRFD,
222 sum(decode(cal.report_date,&BIS_PREVIOUS_ASOF_DATE,EMAILS_RSL_AND_TRFD_IN_PERIOD,NULL)) PREV_TRFD,
223 sum( decode(cal.report_date,&BIS_CURRENT_ASOF_DATE,
224 NVL(EMAILS_REPLIED_IN_PERIOD,0) + NVL(EMAILS_DELETED_IN_PERIOD,0),NULL)) CURR_RESOLVED,
225 sum( decode(cal.report_date,&BIS_PREVIOUS_ASOF_DATE,
226 NVL(EMAILS_REPLIED_IN_PERIOD,0) + NVL(EMAILS_DELETED_IN_PERIOD,0),NULL)) PREV_RESOLVED,
227 sum(decode(cal.report_date,&BIS_CURRENT_ASOF_DATE,
228 EMAIL_RESP_TIME_IN_PERIOD,NULL)) CURR_CRTIME,
229 sum(decode(cal.report_date,&BIS_PREVIOUS_ASOF_DATE,
230 EMAIL_RESP_TIME_IN_PERIOD,NULL)) PREV_CRTIME,
231 sum(decode(cal.report_date,&BIS_CURRENT_ASOF_DATE,
232 AGENT_RESP_TIME_IN_PERIOD,NULL)) CURR_ARTIME,
233 sum(decode(cal.report_date,&BIS_PREVIOUS_ASOF_DATE,
234 AGENT_RESP_TIME_IN_PERIOD,NULL)) PREV_ARTIME,
235 sum(decode(cal.report_date,&BIS_CURRENT_ASOF_DATE,
236 ONE_RSLN_IN_PERIOD,NULL)) CURR_ONEDONE,
237 sum(decode(cal.report_date,&BIS_PREVIOUS_ASOF_DATE,
238 ONE_RSLN_IN_PERIOD,NULL)) PREV_ONEDONE,
239 sum(DECODE(cal.report_date,&BIS_CURRENT_ASOF_DATE,
240 EMAILS_DELETED_IN_PERIOD)) CURR_DELETED,
241 sum(DECODE(cal.report_date,&BIS_PREVIOUS_ASOF_DATE,
242 EMAILS_DELETED_IN_PERIOD)) PREV_DELETED,
243 sum(DECODE(cal.report_date,&BIS_CURRENT_ASOF_DATE,
244 EMAILS_AUTO_DELETED_IN_PERIOD)) CURR_AUTO_DELETED,
245 sum(DECODE(cal.report_date,&BIS_PREVIOUS_ASOF_DATE,
246 EMAILS_AUTO_DELETED_IN_PERIOD)) PREV_AUTO_DELETED,
247 sum(DECODE(cal.report_date,&BIS_CURRENT_ASOF_DATE,
248 NVL(EMAILS_REPLIED_IN_PERIOD,0) +
249 NVL(EMAILS_DELETED_IN_PERIOD,0) +
250 NVL(EMAILS_AUTO_UPTD_SR_IN_PERIOD,0) +
251 NVL(EMAILS_AUTO_REPLIED_IN_PERIOD,0) +
252 NVL(EMAILS_AUTO_DELETED_IN_PERIOD,0) +
253 NVL(EMAILS_AUTO_RESOLVED_IN_PERIOD,0))) CURR_COMPLETED,
254 sum(DECODE(cal.report_date,&BIS_PREVIOUS_ASOF_DATE,
255 NVL(EMAILS_REPLIED_IN_PERIOD,0) +
256 NVL(EMAILS_DELETED_IN_PERIOD,0) +
257 NVL(EMAILS_AUTO_UPTD_SR_IN_PERIOD,0) +
258 NVL(EMAILS_AUTO_REPLIED_IN_PERIOD,0) +
259 NVL(EMAILS_AUTO_DELETED_IN_PERIOD,0) +
260 NVL(EMAILS_AUTO_RESOLVED_IN_PERIOD,0))) PREV_COMPLETED,
261 sum(decode(cal.report_date,&BIS_CURRENT_ASOF_DATE,
262 INTERACTION_THREADS_IN_PERIOD,NULL)) CURR_THREADS,
263 sum(decode(cal.report_date,&BIS_PREVIOUS_ASOF_DATE,
264 INTERACTION_THREADS_IN_PERIOD,NULL)) PREV_THREADS,
265 sum(sum(decode(cal.report_date,&BIS_CURRENT_ASOF_DATE,
266 EMAILS_REPLIED_IN_PERIOD,NULL))) over() CURR_TOTALREPLD,
267 sum(sum(decode(cal.report_date,&BIS_PREVIOUS_ASOF_DATE,
268 EMAILS_REPLIED_IN_PERIOD,NULL))) over() PREV_TOTALREPLD
269 FROM bix_email_details_mv fact,
270 fii_time_rpt_struct cal
271 WHERE fact.time_id = cal.time_id
272 AND fact.period_type_id = cal.period_type_id
273 AND fact.row_type = :l_row_type
274 AND cal.report_date IN (&BIS_CURRENT_ASOF_DATE, &BIS_PREVIOUS_ASOF_DATE)
275 AND bitand(cal.record_type_id,&BIS_NESTED_PATTERN)=cal.record_type_id '
276 || l_where_clause ||
277 ' GROUP BY email_account_id
278 ) summ, ';
279 ELSE
280 l_sqltext := l_sqltext || ' SELECT email_classification_id ID,
281 sum(decode(cal.report_date,&BIS_CURRENT_ASOF_DATE,
282 EMAILS_RPLD_BY_GOAL_IN_PERIOD,NULL)) CURR_MSGSGOAL,
283 sum(decode(cal.report_date,&BIS_PREVIOUS_ASOF_DATE,
284 EMAILS_RPLD_BY_GOAL_IN_PERIOD,NULL)) PREV_MSGSGOAL,
285 sum(decode(cal.report_date,&BIS_CURRENT_ASOF_DATE,
286 EMAILS_REPLIED_IN_PERIOD,NULL)) CURR_REPLD,
287 sum(decode(cal.report_date,&BIS_PREVIOUS_ASOF_DATE,
288 EMAILS_REPLIED_IN_PERIOD,NULL)) PREV_REPLD,
289 sum(decode(cal.report_date,&BIS_CURRENT_ASOF_DATE,EMAILS_AUTO_REPLIED_IN_PERIOD,NULL)) CURR_AUTO_REPLD,
290 sum(decode(cal.report_date,&BIS_PREVIOUS_ASOF_DATE,EMAILS_AUTO_REPLIED_IN_PERIOD,NULL)) PREV_AUTO_REPLD,
291 SUM(decode(cal.report_date,&BIS_CURRENT_ASOF_DATE,
292 NVL(EMAILS_REPLIED_IN_PERIOD,0) + NVL(EMAILS_AUTO_REPLIED_IN_PERIOD,0),NULL)) CURR_TOT_REPLD,
293 SUM(decode(cal.report_date,&BIS_PREVIOUS_ASOF_DATE,
294 NVL(EMAILS_REPLIED_IN_PERIOD,0) + NVL(EMAILS_AUTO_REPLIED_IN_PERIOD,0),NULL)) PREV_TOT_REPLD,
295 sum(decode(cal.report_date,&BIS_CURRENT_ASOF_DATE,EMAILS_RSL_AND_TRFD_IN_PERIOD,NULL)) CURR_TRFD,
296 sum(decode(cal.report_date,&BIS_PREVIOUS_ASOF_DATE,EMAILS_RSL_AND_TRFD_IN_PERIOD,NULL)) PREV_TRFD,
297 sum( decode(cal.report_date,&BIS_CURRENT_ASOF_DATE,
298 NVL(EMAILS_REPLIED_IN_PERIOD,0) + NVL(EMAILS_DELETED_IN_PERIOD,0),NULL)) CURR_RESOLVED,
299 sum( decode(cal.report_date,&BIS_PREVIOUS_ASOF_DATE,
300 NVL(EMAILS_REPLIED_IN_PERIOD,0) + NVL(EMAILS_DELETED_IN_PERIOD,0),NULL)) PREV_RESOLVED,
301 sum(decode(cal.report_date,&BIS_CURRENT_ASOF_DATE,
302 EMAIL_RESP_TIME_IN_PERIOD,NULL)) CURR_CRTIME,
303 sum(decode(cal.report_date,&BIS_PREVIOUS_ASOF_DATE,
304 EMAIL_RESP_TIME_IN_PERIOD,NULL)) PREV_CRTIME,
305 sum(decode(cal.report_date,&BIS_CURRENT_ASOF_DATE,
306 AGENT_RESP_TIME_IN_PERIOD,NULL)) CURR_ARTIME,
307 sum(decode(cal.report_date,&BIS_PREVIOUS_ASOF_DATE,
308 AGENT_RESP_TIME_IN_PERIOD,NULL)) PREV_ARTIME,
309 sum(decode(cal.report_date,&BIS_CURRENT_ASOF_DATE,
310 ONE_RSLN_IN_PERIOD,NULL)) CURR_ONEDONE,
311 sum(decode(cal.report_date,&BIS_PREVIOUS_ASOF_DATE,
312 ONE_RSLN_IN_PERIOD,NULL)) PREV_ONEDONE,
313 sum(DECODE(cal.report_date,&BIS_CURRENT_ASOF_DATE,
314 EMAILS_DELETED_IN_PERIOD)) CURR_DELETED,
315 sum(DECODE(cal.report_date,&BIS_PREVIOUS_ASOF_DATE,
316 EMAILS_DELETED_IN_PERIOD)) PREV_DELETED,
317 sum(DECODE(cal.report_date,&BIS_CURRENT_ASOF_DATE,
318 EMAILS_AUTO_DELETED_IN_PERIOD)) CURR_AUTO_DELETED,
319 sum(DECODE(cal.report_date,&BIS_PREVIOUS_ASOF_DATE,
320 EMAILS_AUTO_DELETED_IN_PERIOD)) PREV_AUTO_DELETED,
321 sum(DECODE(cal.report_date,&BIS_CURRENT_ASOF_DATE,
322 NVL(EMAILS_REPLIED_IN_PERIOD,0) +
323 NVL(EMAILS_DELETED_IN_PERIOD,0) +
324 NVL(EMAILS_AUTO_UPTD_SR_IN_PERIOD,0) +
325 NVL(EMAILS_AUTO_REPLIED_IN_PERIOD,0) +
326 NVL(EMAILS_AUTO_DELETED_IN_PERIOD,0) +
327 NVL(EMAILS_AUTO_RESOLVED_IN_PERIOD,0))) CURR_COMPLETED,
328 sum(DECODE(cal.report_date,&BIS_PREVIOUS_ASOF_DATE,
329 NVL(EMAILS_REPLIED_IN_PERIOD,0) +
330 NVL(EMAILS_DELETED_IN_PERIOD,0) +
331 NVL(EMAILS_AUTO_UPTD_SR_IN_PERIOD,0) +
332 NVL(EMAILS_AUTO_REPLIED_IN_PERIOD,0) +
333 NVL(EMAILS_AUTO_DELETED_IN_PERIOD,0) +
334 NVL(EMAILS_AUTO_RESOLVED_IN_PERIOD,0))) PREV_COMPLETED,
335 sum(decode(cal.report_date,&BIS_CURRENT_ASOF_DATE,
336 INTERACTION_THREADS_IN_PERIOD,NULL)) CURR_THREADS,
337 sum(decode(cal.report_date,&BIS_PREVIOUS_ASOF_DATE,
338 INTERACTION_THREADS_IN_PERIOD,NULL)) PREV_THREADS,
339 sum(sum(decode(cal.report_date,&BIS_CURRENT_ASOF_DATE,
340 EMAILS_REPLIED_IN_PERIOD,NULL))) over() CURR_TOTALREPLD,
341 sum(sum(decode(cal.report_date,&BIS_PREVIOUS_ASOF_DATE,
342 EMAILS_REPLIED_IN_PERIOD,NULL))) over() PREV_TOTALREPLD
343 FROM bix_email_details_mv fact,
344 fii_time_rpt_struct cal
345 WHERE fact.time_id = cal.time_id
346 AND fact.period_type_id = cal.period_type_id
347 AND fact.row_type = :l_row_type
348 AND cal.report_date IN (&BIS_CURRENT_ASOF_DATE, &BIS_PREVIOUS_ASOF_DATE)
349 AND bitand(cal.record_type_id,&BIS_NESTED_PATTERN)=cal.record_type_id '
350 || l_where_clause ||
351 ' GROUP BY email_classification_id
352 ) summ, ';
353 END IF;
354
355 IF l_view_by = 'EMAIL ACCOUNT+EMAIL ACCOUNT' THEN
356 l_sqltext := l_sqltext || ' bix_email_accounts_v lookup_table ';
357 ELSE
358 l_sqltext := l_sqltext || ' bix_email_classifications_v lookup_table ';
359 END IF;
360
361 l_sqltext := l_sqltext || ' WHERE summ.id = lookup_table.id
362 GROUP BY lookup_table.value, lookup_table.id
363
364 )
365 where
366 (nvl(BIX_EMC_PRREPLD,0)
367 +nvl(BIX_EMC_REPLD_CP,0)
368 +abs(nvl(BIX_EMC_REPCHANGE,0))
369 +nvl(BIX_EMC_PREVMSGSGOAL,0)
370 +nvl(BIX_EMC_MSGSGOAL_CP,0)
371 +abs(nvl(BIX_EMC_MGCHANGE,0))
372 +nvl(BIX_EMC_AUTO_RPLD_RATE_CP,0)
373 +abs(nvl(BIX_EMC_AUTO_PRLD_CHANGE,0))
374 +nvl(BIX_EMC_TRANRATIO_CP,0)
375 +abs(nvl(BIX_EMC_TRATIOCHANGE,0))
376 +nvl(BIX_EMC_DELRATIO_CP,0)
377 +abs(nvl(BIX_EMC_DRATIOCHANGE,0))
378 +nvl(BIX_EMC_ONE_DONE_CP,0)
379 +abs(nvl(BIX_EMC_ODCHANGE,0))
380 +nvl(BIX_EMC_PRCRTIME,0)
381 +nvl(BIX_EMC_CRTIME_CP,0)
382 +abs(nvl(BIX_EMC_CRCHANGE,0))
383 +nvl(BIX_EMC_ARTIME_CP,0)
384 +abs(nvl(BIX_EMC_ARCHANGE,0)))<>0
385 &ORDER_BY_CLAUSE ';
386
387 p_sql_text := l_sqltext;
388
389 -- Insert account Bind Variable
390
391 IF ( l_account IS NOT NULL) THEN
392 l_custom_rec.attribute_name := ':l_account' ;
393 l_custom_rec.attribute_value:= l_account;
394 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
395 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
396
397 p_custom_output.Extend();
398 p_custom_output(p_custom_output.count) := l_custom_rec;
399 END IF;
400
401 IF ( l_classification IS NOT NULL) THEN
402 l_custom_rec.attribute_name := ':l_classification' ;
403 l_custom_rec.attribute_value:= l_classification;
404 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
405 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
406
407 p_custom_output.Extend();
408 p_custom_output(p_custom_output.count) := l_custom_rec;
409 END IF;
410
411 l_custom_rec.attribute_name := ':l_application_id';
412 l_custom_rec.attribute_value:= l_application_id;
413 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
414 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.NUMERIC_BIND;
415
416 p_custom_output.EXTEND;
417 p_custom_output(p_custom_output.COUNT) := l_custom_rec;
418
419 l_custom_rec.attribute_name := BIS_PMV_PARAMETERS_PUB.VIEW_BY_VALUE;
420 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.View_By_Type;
421 l_custom_rec.attribute_value := l_view_by;
422
423 p_custom_output.EXTEND();
424 p_custom_output(p_custom_output.COUNT) := l_custom_rec;
425
426 l_custom_rec.attribute_name := ':l_row_type' ;
427 l_custom_rec.attribute_value:= l_row_type;
428 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
429 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
430
431 p_custom_output.Extend();
432 p_custom_output(p_custom_output.count) := l_custom_rec;
433
434 EXCEPTION
435 WHEN OTHERS THEN
436 NULL;
437 END GET_SQL;
438 END BIX_PMV_EMC_ERPT_RPT_PKG;