[Home] [Help]
PACKAGE BODY: APPS.BIX_PMV_EMC_ACCOUNT_RPT_PKG
Source
1 PACKAGE BODY BIX_PMV_EMC_ACCOUNT_RPT_PKG AS
2 /*$Header: bixeactr.plb 120.0 2005/05/25 17:28:49 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 := 0;
16 l_cust_resp_time_goal NUMBER;
17 l_service_level_goal 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_max_collect_date VARCHAR2(100);
27 l_period_start_Date DATE;
28 l_classification VARCHAR2(32000);
29 l_view_by varchar2(1000);
30 l_row_type varchar2(10) := 'AC';
31
32 BEGIN
33
34 --
35 --Initialize p_custom_output
36 --
37
38 p_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
39 l_custom_rec := BIS_PMV_PARAMETERS_PUB.Initialize_Query_Type;
40
41 -- Get the parameters
42
43 BIX_PMV_DBI_UTL_PKG.get_emc_page_params( p_page_parameter_tbl,
44 l_as_of_date,
45 l_period_type,
46 l_record_type_id,
47 l_comp_type,
48 l_account,
49 l_classification,
50 l_view_by
51 );
52
53
54
55 -- If the account is not 'All'
56
57 IF l_account IS NOT NULL THEN
58 l_where_clause := 'AND email_account_id IN (:l_account) ';
59 END IF;
60
61
62 IF l_classification IS NOT NULL THEN
63 l_where_clause := l_where_clause || ' AND email_classification_id IN (:l_classification) ';
64 END IF;
65
66 l_period_start_Date := BIX_PMV_DBI_UTL_PKG.period_start_date(l_as_of_date,l_period_type);
67
68 /* Get the MAX date for which data is collected in Email Summary table */
69
70 BIS_COLLECTION_UTILITIES.get_last_refresh_dates('BIX_EMAIL_DETAILS_F',
71 l_start_date,
72 l_end_date,
73 l_period_from,
74 l_period_to
75 );
76
77
78 /* if the data is not collected till AS OF DATE
79 then get the accumulated measures from the MAX collected DATE time bucket
80 if the max collect date falls between current period */
81
82 IF (NVL(l_period_to,l_as_of_date) >= l_as_of_date ) THEN
83 l_max_collect_date := TO_CHAR(l_as_of_date,'DD/MM/YYYY');
84 ELSIF ( l_period_to < l_period_start_date ) THEN
85 l_max_collect_date := TO_CHAR(l_as_of_date,'DD/MM/YYYY');
86 ELSE
87 l_max_collect_date := TO_CHAR(l_period_to,'DD/MM/YYYY');
88 END IF;
89
90 /*
91 l_sqltext := '
92 SELECT
93 ''[email protected]'' BIX_EMC_ACCOUNT,
94 1000000000 BIX_EMC_RCVD,
95 20 BIX_EMC_RCVDCHANGE,
96 30 BIX_EMC_COMPLETED,
97 40 BIX_EMC_COMPCHANGE,
98 50 BIX_EMC_REPLD,
99 60 BIX_EMC_REPCHANGE,
100 70 BIX_EMC_DELETED,
101 80 BIX_EMC_DELCHANGE,
102 90 BIX_EMC_BACKLOG,
103 100 BIX_EMC_BACKCHANGE,
104 110 BIX_EMC_TRANRATIO,
105 120 BIX_EMC_TRATIOCHANGE,
106 130 BIX_EMC_DELRATIO,
107 140 BIX_EMC_DRATIOCHANGE,
108 150 BIX_EMC_SR,
109 160 BIX_EMC_SRCHANGE,
110 10 BIX_PMV_TOTAL1,
111 20 BIX_PMV_TOTAL2,
112 30 BIX_PMV_TOTAL3,
113 40 BIX_PMV_TOTAL4,
114 50 BIX_PMV_TOTAL5,
115 60 BIX_PMV_TOTAL6,
116 70 BIX_PMV_TOTAL7,
117 80 BIX_PMV_TOTAL8,
118 90 BIX_PMV_TOTAL9,
119 100 BIX_PMV_TOTAL10,
120 110 BIX_PMV_TOTAL11,
121 120 BIX_PMV_TOTAL12,
122 130 BIX_PMV_TOTAL13,
123 140 BIX_PMV_TOTAL14,
124 150 BIX_PMV_TOTAL15,
125 160 BIX_PMV_TOTAL16,
126 170 BIX_PMV_TOTAL17,
127 180 BIX_PMV_TOTAL18
128 FROM DUAL';
129
130 */
131
132 l_sqltext :=
133 'SELECT lookup_table.value VIEWBY,
134 NVL(SUM(curr_received),0) BIX_EMC_RCVD_CP,
135 ((NVL(SUM(curr_received),0) - SUM(prev_received)) / SUM(prev_received) * 100) BIX_EMC_RCVDCHANGE,
136 NVL(SUM(prev_received),0) BIX_EMC_PRRCVD,
137 NVL(SUM(curr_composed),0) BIX_EMC_COMPOSED_CP,
138 ((NVL(SUM(curr_composed),0) - DECODE(SUM(prev_composed),0,NULL,SUM(prev_composed)))
139 /DECODE(SUM(prev_composed),0,NULL,SUM(prev_composed)) * 100) BIX_EMC_COMPOSE_CHANGE,
140 NVL(SUM(curr_replied),0) + NVL(SUM(curr_auto_replied),0) BIX_EMC_REPLD_CP,
141 (((NVL(SUM(curr_replied),0) + NVL(SUM(curr_auto_replied),0)) - (NVL(SUM(prev_replied),0) + NVL(sum(prev_auto_replied),0)) )
142 /DECODE(NVL(SUM(prev_replied),0) + NVL(SUM(prev_auto_replied),0),0,NULL,NVL(SUM(prev_replied),0) + NVL(SUM(prev_auto_replied),0)) * 100) BIX_EMC_REPCHANGE,
143 NVL(SUM(prev_replied),0) + NVL(SUM(prev_auto_replied),0) BIX_EMC_PRREPLD,
144 NVL(SUM(curr_deleted),0) + NVL(SUM(curr_auto_deleted),0) BIX_EMC_DELETED_CP,
145 (((NVL(SUM(curr_deleted),0) + NVL(SUM(curr_auto_deleted),0)) - (NVL(SUM(prev_deleted),0) + NVL(sum(prev_auto_deleted),0)) )
146 / DECODE(NVL(SUM(prev_deleted),0) + NVL(SUM(prev_auto_deleted),0),0,NULL,NVL(SUM(prev_deleted),0) + NVL(SUM(prev_auto_deleted),0)) * 100) BIX_EMC_DELCHANGE,
147 NVL(SUM(curr_backlog),0) BIX_EMC_BACKLOG_CP,
148 ((NVL(SUM(curr_backlog),0) - DECODE(SUM(prev_backlog),0,NULL,SUM(prev_backlog)))
149 / DECODE(SUM(prev_backlog),0,NULL,SUM(prev_backlog)) * 100) BIX_EMC_BACKCHANGE,
150 NVL(SUM(prev_backlog),0) BIX_EMC_PREVBACKLOG,
151 (NVL(SUM(curr_dist_trfr),0)/ DECODE(SUM(NVL(curr_deleted,0) + NVL(curr_replied,0)),0,NULL,
152 SUM(NVL(curr_deleted,0) + NVL(curr_replied,0)))*100) BIX_EMC_TRANRATIO_CP,
153 ((SUM(curr_dist_trfr)/DECODE(SUM(NVL(curr_deleted,0) + NVL(curr_replied,0)),0,NULL,
154 SUM(NVL(curr_deleted,0) + NVL(curr_replied,0))) - SUM(prev_dist_trfr)/ DECODE(SUM(NVL(prev_deleted,0) + NVL(prev_replied,0)),0,NULL,
155 SUM(NVL(prev_deleted,0) + NVL(prev_replied,0)))) * 100) BIX_EMC_TRATIOCHANGE,
156 ((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,
157 ((((NVL(SUM(curr_deleted),0)+ NVL(SUM(curr_auto_deleted),0))/ DECODE(SUM(curr_completed),0,NULL,SUM(curr_completed))) -
158 ((NVL(SUM(prev_deleted),0)+ NVL(SUM(prev_auto_deleted),0))/ DECODE(SUM(prev_completed),0,NULL,SUM(prev_completed))))* 100) BIX_EMC_DRATIOCHANGE,
159 NVL(SUM(curr_sr_created),0) BIX_EMC_SR_CP,
160 ((NVL(SUM(curr_sr_created),0) - SUM(prev_sr_created)) / SUM(prev_sr_created) * 100) BIX_EMC_SRCHANGE,
161 NVL(SUM(curr_leads_created),0) BIX_EMC_LEADS_CP,
162 ((NVL(SUM(curr_leads_created),0) - SUM(prev_leads_created)) / SUM(prev_leads_created) * 100) BIX_EMC_LEADSCHANGE,
163 NVL(SUM(SUM(curr_received)) OVER(),0) BIX_PMV_TOTAL1,
164 ((NVL(SUM(SUM(curr_received)) OVER(),0) - SUM(SUM(prev_received)) OVER()) / SUM(SUM(prev_received)) OVER() * 100) BIX_PMV_TOTAL2,
165 NVL(SUM(SUM(curr_composed)) OVER(),0) BIX_PMV_TOTAL3,
166 ((NVL(SUM(SUM(curr_composed)) OVER(),0) - DECODE(SUM(SUM(prev_composed)) OVER(),0,NULL,SUM(SUM(prev_composed)) OVER()))
167 / DECODE(SUM(SUM(prev_composed)) OVER(),0,NULL,SUM(SUM(prev_composed)) OVER()) * 100) BIX_PMV_TOTAL4,
168 NVL(SUM(SUM(curr_replied)) OVER(),0) + NVL(SUM(SUM(curr_auto_replied)) OVER(),0) BIX_PMV_TOTAL5,
169 (((NVL(SUM(SUM(curr_replied)) OVER(),0) + NVL(SUM(SUM(curr_auto_replied)) OVER(),0)) -
170 (NVL(SUM(SUM(prev_replied)) OVER(),0) + NVL(SUM(SUM(prev_auto_replied)) OVER(),0)))
171 / DECODE(NVL(SUM(SUM(prev_replied)) OVER(),0) + NVL(SUM(SUM(prev_auto_replied)) OVER(),0),0,NULL,
172 NVL(SUM(SUM(prev_replied)) OVER(),0) + NVL(SUM(SUM(prev_auto_replied)) OVER(),0)) * 100) BIX_PMV_TOTAL6,
173 NVL(SUM(SUM(curr_deleted)) OVER(),0) + NVL(SUM(SUM(curr_auto_deleted)) OVER(),0) BIX_PMV_TOTAL7,
174 (((NVL(SUM(SUM(curr_deleted)) OVER(),0) + NVL(SUM(SUM(curr_auto_deleted)) OVER(),0)) -
175 (NVL(SUM(SUM(prev_deleted)) OVER(),0) + NVL(SUM(SUM(prev_auto_deleted)) OVER(),0)) )
176 / DECODE(NVL(SUM(SUM(prev_deleted)) OVER(),0) + NVL(SUM(SUM(prev_auto_deleted)) OVER(),0),0,NULL,
177 NVL(SUM(SUM(prev_deleted)) OVER(),0) + NVL(SUM(SUM(prev_auto_deleted)) OVER(),0)) * 100) BIX_PMV_TOTAL8,
178 SUM(SUM(curr_backlog)) OVER() BIX_PMV_TOTAL9,
179 ((SUM(SUM(curr_backlog)) OVER() - DECODE(SUM(SUM(prev_backlog)) OVER(),0,NULL,SUM(SUM(prev_backlog)) OVER()))
180 / DECODE(SUM(SUM(prev_backlog)) OVER(),0,NULL,SUM(SUM(prev_backlog)) OVER()) * 100) BIX_PMV_TOTAL10,
181 (NVL(SUM(SUM(curr_dist_trfr)) OVER(),0)/ DECODE(SUM(SUM(NVL(curr_deleted,0) + NVL(curr_replied,0))) OVER(),0,NULL,
182 SUM(SUM(NVL(curr_deleted,0) + NVL(curr_replied,0))) OVER())*100) BIX_PMV_TOTAL11,
183 ((SUM(SUM(curr_dist_trfr)) OVER()/DECODE(SUM(SUM(NVL(curr_deleted,0) + NVL(curr_replied,0))) OVER(),0,NULL,
184 SUM(SUM(NVL(curr_deleted,0) + NVL(curr_replied,0))) OVER()) -
185 SUM(SUM(prev_dist_trfr)) OVER()/ DECODE(SUM(SUM(NVL(prev_deleted,0) + NVL(prev_replied,0))) OVER(),0,NULL,
186 SUM(SUM(NVL(prev_deleted,0) + NVL(prev_replied,0))) OVER())) * 100) BIX_PMV_TOTAL12,
187 ((NVL(SUM(SUM(curr_deleted)) OVER(),0) + NVL(SUM(SUM(curr_auto_deleted)) OVER(),0) )/
188 DECODE(SUM(SUM(curr_completed)) OVER(),0,NULL,SUM(SUM(curr_completed)) OVER())*100) BIX_PMV_TOTAL13,
189 ((((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())) -
190 ((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_TOTAL14,
191 NVL(SUM(SUM(curr_sr_created)) OVER(),0) BIX_PMV_TOTAL15,
192 ((NVL(SUM(SUM(curr_sr_created)) OVER(),0) - SUM(SUM(prev_sr_created)) OVER()) / SUM(SUM(prev_sr_created)) OVER() * 100)BIX_PMV_TOTAL16,
193 NVL(SUM(SUM(curr_leads_created)) OVER(),0) BIX_PMV_TOTAL17,
194 ((NVL(SUM(SUM(curr_leads_created)) OVER(),0) - SUM(SUM(prev_leads_created)) OVER()) / SUM(SUM(prev_leads_created)) OVER() * 100)BIX_PMV_TOTAL18
195 --START 001 added the following new columns for calculation purposes
196 ,NVL(SUM(curr_dist_trfr),0)/ DECODE(SUM(NVL(curr_deleted,0) + NVL(curr_replied,0)),0,NULL,
197 SUM(NVL(curr_deleted,0) + NVL(curr_replied,0)))*100 BIX_CALC_ITEM1
198 ,NVL(SUM(SUM(curr_dist_trfr)) OVER(),0)/ DECODE(SUM(SUM(NVL(curr_deleted,0) + NVL(curr_replied,0))) OVER(),0,NULL,
199 SUM(SUM(NVL(curr_deleted,0) + NVL(curr_replied,0))) OVER())*100 BIX_CALC_ITEM2
200 ,NVL(SUM(prev_dist_trfr),0)/ DECODE(SUM(NVL(prev_deleted,0) + NVL(prev_replied,0)),0,NULL,
201 SUM(NVL(prev_deleted,0) + NVL(prev_replied,0)))*100 BIX_CALC_ITEM3
202 ,NVL(SUM(SUM(prev_dist_trfr)) OVER(),0)/ DECODE(SUM(SUM(NVL(prev_deleted,0) + NVL(prev_replied,0))) OVER(),0,NULL,
203 SUM(SUM(NVL(prev_deleted,0) + NVL(prev_replied,0))) OVER())*100 BIX_CALC_ITEM4
204 ,(NVL(SUM(curr_deleted),0)+ NVL(SUM(curr_auto_deleted),0))/ DECODE(SUM(curr_completed),0,NULL,SUM(curr_completed))*100 BIX_CALC_ITEM5
205 ,(NVL(SUM(SUM(curr_deleted)) OVER(),0) + NVL(SUM(SUM(curr_auto_deleted)) OVER(),0) )/
206 DECODE(SUM(SUM(curr_completed)) OVER(),0,NULL,SUM(SUM(curr_completed)) OVER())*100 BIX_CALC_ITEM6
207 ,(NVL(SUM(prev_deleted),0)+ NVL(SUM(prev_auto_deleted),0))/ DECODE(SUM(prev_completed),0,NULL,SUM(prev_completed))*100 BIX_CALC_ITEM7
208 ,(NVL(SUM(SUM(prev_deleted)) OVER(),0) + NVL(SUM(SUM(prev_auto_deleted)) OVER(),0) )/
209 DECODE(SUM(SUM(prev_completed)) OVER(),0,NULL,SUM(SUM(prev_completed)) OVER())*100 BIX_CALC_ITEM8
210 ,NVL(SUM(curr_received),0) BIX_CALC_ITEM9
211 ,NVL(SUM(SUM(curr_received)) OVER(),0) BIX_CALC_ITEM10
212 ,NVL(SUM(prev_received),0) BIX_CALC_ITEM11
213 ,NVL(SUM(SUM(prev_received)) OVER(),0) BIX_CALC_ITEM12
214 ,NVL(SUM(curr_replied),0) + NVL(SUM(curr_auto_replied),0) BIX_CALC_ITEM13
215 ,NVL(SUM(SUM(curr_replied)) OVER(),0) + NVL(SUM(SUM(curr_auto_replied)) OVER(),0) BIX_CALC_ITEM14
216 ,NVL(SUM(prev_replied),0) + NVL(SUM(prev_auto_replied),0) BIX_CALC_ITEM15
217 ,NVL(SUM(SUM(prev_replied)) OVER(),0) + NVL(SUM(SUM(prev_auto_replied)) OVER(),0) BIX_CALC_ITEM16
218 ,NVL(SUM(curr_backlog),0) BIX_CALC_ITEM17
219 ,NVL(SUM(SUM(curr_backlog)) OVER(),0) BIX_CALC_ITEM18
220 ,NVL(SUM(prev_backlog),0) BIX_CALC_ITEM19
221 ,NVL(SUM(SUM(prev_backlog)) OVER(),0) BIX_CALC_ITEM20
222 ,NVL(SUM(curr_composed),0) BIX_CALC_ITEM21
223 ,NVL(SUM(SUM(curr_composed)) OVER(),0) BIX_CALC_ITEM22
224 ,NVL(SUM(prev_composed),0) BIX_CALC_ITEM23
225 ,NVL(SUM(SUM(prev_composed)) OVER(),0) BIX_CALC_ITEM24
226 ,NVL(SUM(curr_sr_created),0) BIX_CALC_ITEM25
227 ,NVL(SUM(SUM(curr_sr_created)) OVER(),0) BIX_CALC_ITEM26
228 ,NVL(SUM(prev_sr_created),0) BIX_CALC_ITEM27
229 ,NVL(SUM(SUM(prev_sr_created)) OVER(),0) BIX_CALC_ITEM28
230 ,NVL(SUM(curr_leads_created),0) BIX_CALC_ITEM29
231 ,NVL(SUM(SUM(curr_leads_created)) OVER(),0) BIX_CALC_ITEM30
232 ,NVL(SUM(prev_leads_created),0) BIX_CALC_ITEM31
233 ,NVL(SUM(SUM(prev_leads_created)) OVER(),0) BIX_CALC_ITEM32
234 ,NVL(SUM(SUM(curr_trfd)) OVER(),0) BIX_CALC_ITEM33
235 ,NVL(SUM(curr_trfd),0) BIX_EMC_TRANOUT
236 ,((NVL(SUM(curr_trfd),0)-DECODE(SUM(prev_trfd),0,NULL,SUM(prev_trfd)))
237 /DECODE(SUM(prev_trfd),0,NULL,SUM(prev_trfd)) * 100) BIX_PMV_EMC_TRANSOUT_CHNG
238 ,((NVL(SUM(SUM(curr_trfd)) OVER(),0)-DECODE(SUM(SUM(prev_trfd)) OVER(),0,NULL,SUM(SUM(prev_trfd)) OVER()))
239 /DECODE(SUM(SUM(prev_trfd)) OVER(),0,NULL,SUM(SUM(prev_trfd)) OVER()) * 100) BIX_PMV_EMC_TRANSOUT_CHNG_TOTA
240 --End 001 addition of columns done
241 FROM ( ';
242 IF l_view_by = 'EMAIL ACCOUNT+EMAIL ACCOUNT' THEN
243 l_sqltext := l_sqltext || ' SELECT
244 email_account_id id,
245 SUM(DECODE(calendar.report_date,&BIS_CURRENT_ASOF_DATE,EMAILS_OFFERED_IN_PERIOD)) curr_received,
246 SUM(DECODE(calendar.report_date,&BIS_PREVIOUS_ASOF_DATE,EMAILS_OFFERED_IN_PERIOD)) prev_received,
247 SUM(DECODE(calendar.report_date,&BIS_CURRENT_ASOF_DATE,EMAILS_COMPOSED_IN_PERIOD)) curr_composed,
248 SUM(DECODE(calendar.report_date,&BIS_PREVIOUS_ASOF_DATE,EMAILS_COMPOSED_IN_PERIOD)) prev_composed,
249 SUM(DECODE(calendar.report_date,&BIS_CURRENT_ASOF_DATE,NVL(EMAILS_REPLIED_IN_PERIOD,0) +
250 NVL(EMAILS_DELETED_IN_PERIOD,0) + NVL(EMAILS_AUTO_UPTD_SR_IN_PERIOD,0) +
251 NVL(EMAILS_AUTO_REPLIED_IN_PERIOD,0) + NVL(EMAILS_AUTO_DELETED_IN_PERIOD,0) +
252 NVL(EMAILS_AUTO_RESOLVED_IN_PERIOD,0))) curr_completed,
253 SUM(DECODE(calendar.report_date,&BIS_PREVIOUS_ASOF_DATE,NVL(EMAILS_REPLIED_IN_PERIOD,0) +
254 NVL(EMAILS_DELETED_IN_PERIOD,0) + NVL(EMAILS_AUTO_UPTD_SR_IN_PERIOD,0) +
255 NVL(EMAILS_AUTO_REPLIED_IN_PERIOD,0) + NVL(EMAILS_AUTO_DELETED_IN_PERIOD,0) +
256 NVL(EMAILS_AUTO_RESOLVED_IN_PERIOD,0))) prev_completed,
257 SUM(DECODE(calendar.report_date,&BIS_CURRENT_ASOF_DATE,EMAILS_REPLIED_IN_PERIOD)) curr_replied,
258 SUM(DECODE(calendar.report_date,&BIS_PREVIOUS_ASOF_DATE,EMAILS_REPLIED_IN_PERIOD)) prev_replied,
259 SUM(DECODE(calendar.report_date,&BIS_CURRENT_ASOF_DATE,EMAILS_AUTO_REPLIED_IN_PERIOD)) curr_auto_replied,
260 SUM(DECODE(calendar.report_date,&BIS_PREVIOUS_ASOF_DATE,EMAILS_AUTO_REPLIED_IN_PERIOD)) prev_auto_replied,
261 SUM(DECODE(calendar.report_date,&BIS_CURRENT_ASOF_DATE,EMAILS_DELETED_IN_PERIOD)) curr_deleted,
262 SUM(DECODE(calendar.report_date,&BIS_PREVIOUS_ASOF_DATE,EMAILS_DELETED_IN_PERIOD)) prev_deleted,
263 SUM(DECODE(calendar.report_date,&BIS_CURRENT_ASOF_DATE,EMAILS_AUTO_DELETED_IN_PERIOD)) curr_auto_deleted,
264 SUM(DECODE(calendar.report_date,&BIS_PREVIOUS_ASOF_DATE,EMAILS_AUTO_DELETED_IN_PERIOD)) prev_auto_deleted,
265 SUM(DECODE(calendar.report_date,&BIS_CURRENT_ASOF_DATE,EMAILS_RSL_AND_TRFD_IN_PERIOD)) curr_dist_trfr,
266 SUM(DECODE(calendar.report_date,&BIS_PREVIOUS_ASOF_DATE,EMAILS_RSL_AND_TRFD_IN_PERIOD)) prev_dist_trfr,
267 SUM(DECODE(calendar.report_date,&BIS_CURRENT_ASOF_DATE,SR_CREATED_IN_PERIOD)) curr_sr_created,
268 SUM(DECODE(calendar.report_date,&BIS_PREVIOUS_ASOF_DATE,SR_CREATED_IN_PERIOD)) prev_sr_created,
269 SUM(DECODE(calendar.report_date,&BIS_CURRENT_ASOF_DATE,LEADS_CREATED_IN_PERIOD)) curr_leads_created,
270 SUM(DECODE(calendar.report_date,&BIS_PREVIOUS_ASOF_DATE,LEADS_CREATED_IN_PERIOD)) prev_leads_created,
271 NULL curr_backlog,
272 NULL prev_backlog,
273 SUM(DECODE(calendar.report_date,&BIS_CURRENT_ASOF_DATE,emails_rsl_and_trfd_in_period)) curr_trfd,--001
274 SUM(DECODE(calendar.report_date,&BIS_PREVIOUS_ASOF_DATE,emails_rsl_and_trfd_in_period)) prev_trfd --001
275 FROM bix_email_details_mv fact,
276 fii_time_rpt_struct calendar
277 WHERE fact.time_id = calendar.time_id
278 AND fact.period_type_id = calendar.period_type_id
279 AND fact.row_type = :l_row_type
280 AND calendar.report_date IN (&BIS_CURRENT_ASOF_DATE,&BIS_PREVIOUS_ASOF_DATE)
281 AND bitand(calendar.record_type_id,&BIS_NESTED_PATTERN ) = calendar.record_type_id '
282 || l_where_clause || '
283 GROUP BY email_account_id
284 UNION ALL
285 SELECT email_account_id id,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
286 NULL,NULL,NULL,NULL,SUM(DECODE(period_start_date,:l_max_collect_date,NVL(ACCUMULATED_OPEN_EMAILS,0) + NVL(ACCUMULATED_EMAILS_IN_QUEUE,0) )) curr_backlog,
287 SUM(DECODE(period_start_date,&BIS_PREVIOUS_ASOF_DATE,NVL(ACCUMULATED_OPEN_EMAILS,0) + NVL(ACCUMULATED_EMAILS_IN_QUEUE,0) )) prev_backlog,NULL,NULL
288 FROM bix_email_details_mv
289 WHERE time_id IN (TO_CHAR(:l_max_collect_date,''J''),TO_CHAR(&BIS_PREVIOUS_ASOF_DATE,''J''))
290 AND row_type = :l_row_type
291 AND period_type_id = :l_period_type_id ' || l_where_clause || '
292 GROUP BY email_account_id
293 ) fact, ';
294 ELSE
295 l_sqltext := l_sqltext || ' SELECT
296 email_classification_id id,
297 SUM(DECODE(calendar.report_date,&BIS_CURRENT_ASOF_DATE,EMAILS_OFFERED_IN_PERIOD)) curr_received,
298 SUM(DECODE(calendar.report_date,&BIS_PREVIOUS_ASOF_DATE,EMAILS_OFFERED_IN_PERIOD)) prev_received,
299 SUM(DECODE(calendar.report_date,&BIS_CURRENT_ASOF_DATE,EMAILS_COMPOSED_IN_PERIOD)) curr_composed,
300 SUM(DECODE(calendar.report_date,&BIS_PREVIOUS_ASOF_DATE,EMAILS_COMPOSED_IN_PERIOD)) prev_composed,
301 SUM(DECODE(calendar.report_date,&BIS_CURRENT_ASOF_DATE,NVL(EMAILS_REPLIED_IN_PERIOD,0) +
302 NVL(EMAILS_DELETED_IN_PERIOD,0) + NVL(EMAILS_AUTO_UPTD_SR_IN_PERIOD,0) +
303 NVL(EMAILS_AUTO_REPLIED_IN_PERIOD,0) + NVL(EMAILS_AUTO_DELETED_IN_PERIOD,0) +
304 NVL(EMAILS_AUTO_RESOLVED_IN_PERIOD,0))) curr_completed,
305 SUM(DECODE(calendar.report_date,&BIS_PREVIOUS_ASOF_DATE,NVL(EMAILS_REPLIED_IN_PERIOD,0) +
306 NVL(EMAILS_DELETED_IN_PERIOD,0) + NVL(EMAILS_AUTO_UPTD_SR_IN_PERIOD,0) +
307 NVL(EMAILS_AUTO_REPLIED_IN_PERIOD,0) + NVL(EMAILS_AUTO_DELETED_IN_PERIOD,0) +
308 NVL(EMAILS_AUTO_RESOLVED_IN_PERIOD,0))) prev_completed,
309 SUM(DECODE(calendar.report_date,&BIS_CURRENT_ASOF_DATE,EMAILS_REPLIED_IN_PERIOD)) curr_replied,
310 SUM(DECODE(calendar.report_date,&BIS_PREVIOUS_ASOF_DATE,EMAILS_REPLIED_IN_PERIOD)) prev_replied,
311 SUM(DECODE(calendar.report_date,&BIS_CURRENT_ASOF_DATE,EMAILS_AUTO_REPLIED_IN_PERIOD)) curr_auto_replied,
312 SUM(DECODE(calendar.report_date,&BIS_PREVIOUS_ASOF_DATE,EMAILS_AUTO_REPLIED_IN_PERIOD)) prev_auto_replied,
313 SUM(DECODE(calendar.report_date,&BIS_CURRENT_ASOF_DATE,EMAILS_DELETED_IN_PERIOD)) curr_deleted,
314 SUM(DECODE(calendar.report_date,&BIS_PREVIOUS_ASOF_DATE,EMAILS_DELETED_IN_PERIOD)) prev_deleted,
315 SUM(DECODE(calendar.report_date,&BIS_CURRENT_ASOF_DATE,EMAILS_AUTO_DELETED_IN_PERIOD)) curr_auto_deleted,
316 SUM(DECODE(calendar.report_date,&BIS_PREVIOUS_ASOF_DATE,EMAILS_AUTO_DELETED_IN_PERIOD)) prev_auto_deleted,
317 SUM(DECODE(calendar.report_date,&BIS_CURRENT_ASOF_DATE,EMAILS_RSL_AND_TRFD_IN_PERIOD)) curr_dist_trfr,
318 SUM(DECODE(calendar.report_date,&BIS_PREVIOUS_ASOF_DATE,EMAILS_RSL_AND_TRFD_IN_PERIOD)) prev_dist_trfr,
319 SUM(DECODE(calendar.report_date,&BIS_CURRENT_ASOF_DATE,SR_CREATED_IN_PERIOD)) curr_sr_created,
320 SUM(DECODE(calendar.report_date,&BIS_PREVIOUS_ASOF_DATE,SR_CREATED_IN_PERIOD)) prev_sr_created,
321 SUM(DECODE(calendar.report_date,&BIS_CURRENT_ASOF_DATE,LEADS_CREATED_IN_PERIOD)) curr_leads_created,
322 SUM(DECODE(calendar.report_date,&BIS_PREVIOUS_ASOF_DATE,LEADS_CREATED_IN_PERIOD)) prev_leads_created,
323 NULL curr_backlog,
324 NULL prev_backlog,
325 SUM(DECODE(calendar.report_date,&BIS_CURRENT_ASOF_DATE ,emails_rsl_and_trfd_in_period)) curr_trfd,--001 Transferred Out column in the Email Activity table portlet
326 SUM(DECODE(calendar.report_date,&BIS_PREVIOUS_ASOF_DATE,emails_rsl_and_trfd_in_period)) prev_trfd --001
327 FROM bix_email_details_mv fact,
328 fii_time_rpt_struct calendar
329 WHERE fact.time_id = calendar.time_id
330 AND fact.row_type = :l_row_type
331 AND fact.period_type_id = calendar.period_type_id
332 AND calendar.report_date IN (&BIS_CURRENT_ASOF_DATE,&BIS_PREVIOUS_ASOF_DATE)
333 AND bitand(calendar.record_type_id,&BIS_NESTED_PATTERN ) = calendar.record_type_id '
334 || l_where_clause || '
335 GROUP BY email_classification_id
336 UNION ALL
337 SELECT email_classification_id id,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
338 NULL,NULL,NULL,SUM(DECODE(period_start_date,:l_max_collect_date,NVL(ACCUMULATED_OPEN_EMAILS,0) + NVL(ACCUMULATED_EMAILS_IN_QUEUE,0) )) curr_backlog,
339 SUM(DECODE(period_start_date,&BIS_PREVIOUS_ASOF_DATE,NVL(ACCUMULATED_OPEN_EMAILS,0) + NVL(ACCUMULATED_EMAILS_IN_QUEUE,0) )) prev_backlog,NULL,NULL
340 FROM bix_email_details_mv
341 WHERE time_id IN (TO_CHAR(:l_max_collect_date,''J''),TO_CHAR(&BIS_PREVIOUS_ASOF_DATE,''J''))
342 AND row_type = :l_row_type
343 AND period_type_id = :l_period_type_id ' || l_where_clause || '
344 GROUP BY email_classification_id
345 ) fact, ';
346
347 END IF;
348
349 IF l_view_by = 'EMAIL ACCOUNT+EMAIL ACCOUNT' THEN
350 l_sqltext := l_sqltext || ' bix_email_accounts_v lookup_table ';
351 ELSE
352 l_sqltext := l_sqltext || ' bix_email_classifications_v lookup_table ';
353 END IF;
354
355 l_sqltext := l_sqltext || ' WHERE fact.id = lookup_table.id
356 GROUP BY lookup_table.value,lookup_table.id ';
357
358 --Start 002 , added the logic for filtering out rows with only 0 | N/A values
359
360 l_sqltext := 'SELECT * FROM ( '||l_sqltext ||' ) WHERE
361 ABS(NVL(BIX_EMC_RCVD_CP ,0))+ABS(NVL(BIX_EMC_RCVDCHANGE,0))+ABS(NVL(BIX_EMC_REPLD_CP ,0))+ABS(NVL(BIX_EMC_REPCHANGE ,0))+
362 ABS(NVL(BIX_EMC_DELETED_CP,0))+ABS(NVL(BIX_EMC_DELCHANGE ,0))+ABS(NVL(BIX_EMC_TRANOUT ,0))+ABS(NVL(BIX_PMV_EMC_TRANSOUT_CHNG,0))+
363 ABS(NVL(BIX_EMC_BACKLOG_CP ,0))+ABS(NVL(BIX_EMC_BACKCHANGE ,0))+ABS(NVL(BIX_EMC_COMPOSED_CP ,0))+ABS(NVL(BIX_EMC_COMPOSE_CHANGE,0))+
364 ABS(NVL(BIX_EMC_TRANRATIO_CP ,0))+ABS(NVL(BIX_EMC_TRATIOCHANGE ,0))+ABS(NVL(BIX_EMC_DELRATIO_CP ,0))+ABS(NVL(BIX_EMC_DRATIOCHANGE ,0))+
365 ABS(NVL(BIX_EMC_SR_CP ,0))+ABS(NVL(BIX_EMC_SRCHANGE ,0))+ABS(NVL(BIX_EMC_LEADS_CP ,0))+ABS(NVL(BIX_EMC_LEADSCHANGE ,0))
366 !=0 &ORDER_BY_CLAUSE ';
367
368 p_sql_text := REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(l_sqltext,' ',' '),' ',' '),' ',' '),' ',' '),' ',' ');
369
370 --End 002 , added the logic for filtering out rows with only 0 | N/A values and used "REPLACE" to shorten the query length
371
372 p_custom_output.EXTEND();
373 l_custom_rec.attribute_name := ':l_period_type_id' ;
374 l_custom_rec.attribute_value:= l_period_type_id;
375 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
376 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.NUMERIC_BIND;
377
378 p_custom_output.Extend();
379 p_custom_output(p_custom_output.count) := l_custom_rec;
380
381 -- Insert account Bind Variable
382
383 IF ( l_account IS NOT NULL) THEN
384 l_custom_rec.attribute_name := ':l_account' ;
385 l_custom_rec.attribute_value:= l_account;
386 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
387 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
388
389 p_custom_output.Extend();
390 p_custom_output(p_custom_output.count) := l_custom_rec;
391 END IF;
392
393 IF ( l_classification IS NOT NULL) THEN
394 l_custom_rec.attribute_name := ':l_classification' ;
395 l_custom_rec.attribute_value:= l_classification;
396 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
397 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
398
399 p_custom_output.Extend();
400 p_custom_output(p_custom_output.count) := l_custom_rec;
401 END IF;
402
403 l_custom_rec.attribute_name := ':l_max_collect_date' ;
404 l_custom_rec.attribute_value:= l_max_collect_date;
405 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
406 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.DATE_BIND;
407
408 p_custom_output.Extend();
409 p_custom_output(p_custom_output.count) := l_custom_rec;
410
411 l_custom_rec.attribute_name := BIS_PMV_PARAMETERS_PUB.VIEW_BY_VALUE;
412 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.View_By_Type;
413 l_custom_rec.attribute_value := l_view_by;
414
415 p_custom_output.EXTEND();
416 p_custom_output(p_custom_output.COUNT) := l_custom_rec;
417
418 l_custom_rec.attribute_name := ':l_row_type' ;
419 l_custom_rec.attribute_value:= l_row_type;
420 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
421 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
422
423 p_custom_output.Extend();
424 p_custom_output(p_custom_output.count) := l_custom_rec;
425
426 EXCEPTION
427 WHEN OTHERS THEN
428 NULL;
429 END GET_SQL;
430 END BIX_PMV_EMC_ACCOUNT_RPT_PKG;