[Home] [Help]
PACKAGE BODY: APPS.BIX_PMV_EMC_CUSTDET_RPT_PKG
Source
1 PACKAGE BODY BIX_PMV_EMC_CUSTDET_RPT_PKG AS
2 /*$Header: bixecd1r.plb 120.0 2005/05/25 17:25:19 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_record_type_id NUMBER ;
18 l_account VARCHAR2(32000);
19 l_start_date DATE;
20 l_end_date DATE;
21 l_period_from DATE;
22 l_period_to DATE;
23 l_dummy_cust NUMBER;
24 l_max_collect_date VARCHAR2(100);
25 l_period_start_date DATE;
26 l_unident_string VARCHAR2(100);
27 l_application_id NUMBER := 680;
28 l_classification VARCHAR2(32000);
29 l_view_by varchar2(1000);
30 l_row_type varchar2(10) := 'ACP';
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 -- If the account is not 'All'
55
56 IF l_account IS NOT NULL THEN
57 l_where_clause := 'AND email_account_id IN (:l_account) ';
58 END IF;
59
60
61 IF l_classification IS NOT NULL THEN
62 l_where_clause := l_where_clause || ' AND email_classification_id IN (:l_classification) ';
63 END IF;
64 --
65 --Retrieve the dummy (unidentified) customer id which is used by EMC
66 --
67 IF (FND_PROFILE.DEFINED('IEM_DEFAULT_CUSTOMER_ID')) THEN
68 l_dummy_cust := TO_NUMBER(FND_PROFILE.VALUE('IEM_DEFAULT_CUSTOMER_ID'));
69 END IF;
70
71 --
72 --If it is null then set it to some invalid value so the SQL does not error
73 --out
74 --
75 IF l_dummy_cust IS NULL
76 THEN
77 l_dummy_cust := -123456;
78 END IF;
79
80 --
81 --Retrieve the message UNIDENTIFIED from FND_MESSAGES
82 --
83 l_unident_string := FND_MESSAGE.GET_STRING('BIX','BIX_PMV_UNIDENT');
84
85 IF l_unident_string IS NULL OR l_unident_string = 'BIX_PMV_UNIDENT'
86 THEN
87 l_unident_string := 'Not identified';
88 END IF;
89
90 -- If the account is not 'All'
91
92 /* Get the MAX date for which data is collected in Email Summary table */
93
94 BIS_COLLECTION_UTILITIES.get_last_refresh_dates('BIX_EMAIL_DETAILS_F',
95 l_start_date,
96 l_end_date,
97 l_period_from,
98 l_period_to
99 );
100
101
102 l_period_start_date := BIX_PMV_DBI_UTL_PKG.period_start_date(l_as_of_date,l_period_type);
103
104 /* Get the MAX date for which data is collected in Email Summary table */
105
106 BIS_COLLECTION_UTILITIES.get_last_refresh_dates('BIX_EMAIL_DETAILS_F',
107 l_start_date,
108 l_end_date,
109 l_period_from,
110 l_period_to
111 );
112 --
113 --If the data is not collected till AS OF DATE
114 --then get the accumulated measures from the MAX collected DATE time bucket
115 --if the max collect date falls within the current period
116 --
117
118 IF l_period_to BETWEEN l_period_start_date AND l_as_of_date
119 THEN
120 l_max_collect_date := TO_CHAR(l_period_to,'DD/MM/YYYY');
121 ELSE
122 l_max_collect_date := TO_CHAR(l_as_of_date,'DD/MM/YYYY');
123 END IF;
124
125
126 l_sqltext :=
127 'SELECT decode(hzp.party_id,'||l_dummy_cust||',:l_unident_string,hzp.party_name) BIX_EMC_CUSTOMER,
128 nvl(sum(curr_received),0) BIX_EMC_RCVD_CP,
129 nvl(sum(sum(curr_received)) over(), 0) BIX_PMV_TOTAL16,
130 (NVL(SUM(curr_received),0) - SUM(prev_received)) /
131 DECODE(SUM(prev_received),0,NULL,SUM(prev_received)) * 100 BIX_EMC_RCVDCHANGE,
132 (NVL(SUM(SUM(curr_received)) over(),0) - SUM(SUM(prev_received)) over() ) /
133 DECODE(SUM(SUM(prev_received)) over(),0,NULL,sum(SUM(prev_received)) over()) * 100 BIX_PMV_TOTAL17,
134 nvl(sum(CURR_TOT_REPLD),0) BIX_EMC_REPLD_CP,
135 nvl(SUM(sum(CURR_TOT_REPLD)) over(),0) BIX_PMV_TOTAL1,
136 (NVL(SUM(curr_tot_repld),0) - SUM(prev_tot_repld)) /
137 DECODE(SUM(prev_tot_repld),0,NULL,SUM(prev_tot_repld)) * 100 BIX_EMC_REPCHANGE,
138 (NVL(SUM(SUM(curr_tot_repld)) OVER(),0) - SUM(SUM(prev_tot_repld))OVER()) /
139 DECODE(SUM(SUM(prev_tot_repld))OVER(),0,NULL,SUM(SUM(prev_tot_repld))OVER()) * 100 BIX_PMV_TOTAL8,
140 nvl(sum(PREV_TOT_REPLD),0) BIX_EMC_PRREPLD,
141 nvl(sum(CURR_MSGSGOAL),0)*100/DECODE(sum(CURR_TOT_REPLD),0,NULL,sum(CURR_TOT_REPLD)) BIX_EMC_MSGSGOAL_CP,
142 nvl(sum(sum(CURR_MSGSGOAL)) over(),0)*100/
143 DECODE(sum(sum(CURR_TOT_REPLD)) over(),0,NULL,sum(sum(CURR_TOT_REPLD)) over()) BIX_PMV_TOTAL4,
144 100*
145 (
146 (nvl(sum(CURR_MSGSGOAL),0)/DECODE(sum(CURR_TOT_REPLD),0,NULL,sum(CURR_TOT_REPLD))) -
147 (nvl(sum(PREV_MSGSGOAL),0)/DECODE(sum(PREV_TOT_REPLD),0,NULL,sum(PREV_TOT_REPLD)))
148 )
149 BIX_EMC_MGCHANGE,
150
151 100*
152 (
153 (nvl(sum(sum(CURR_MSGSGOAL)) over(),0)/DECODE(sum(sum(CURR_TOT_REPLD)) over(),0,NULL,sum(sum(CURR_TOT_REPLD)) over()))-
154 (nvl(sum(sum(PREV_MSGSGOAL)) over(),0)/DECODE(sum(sum(PREV_TOT_REPLD)) over(),0,NULL,sum(sum(PREV_TOT_REPLD)) over()))
155 )
156 BIX_PMV_TOTAL9,
157 nvl(sum(PREV_MSGSGOAL),0)*100/DECODE(sum(PREV_TOT_REPLD),0,NULL,sum(PREV_TOT_REPLD)) BIX_EMC_PREVMSGSGOAL,
158 nvl(sum(CURR_CRTIME),0)/(3600*DECODE(sum(CURR_TOT_REPLD),0,NULL,sum(CURR_TOT_REPLD))) BIX_EMC_CRTIME_CP,
159 nvl(sum(sum(CURR_CRTIME)) over(),0)/
160 (3600*DECODE(sum(sum(CURR_TOT_REPLD)) over(),0,NULL,sum(sum(CURR_TOT_REPLD)) over())) BIX_PMV_TOTAL5,
161 ((nvl(sum(CURR_CRTIME),0)/DECODE(sum(CURR_TOT_REPLD),0,NULL,sum(CURR_TOT_REPLD)))
162 - (sum(PREV_CRTIME)/DECODE(sum(PREV_TOT_REPLD),0,NULL,sum(PREV_TOT_REPLD))))*100/
163 (sum(PREV_CRTIME)/DECODE(sum(PREV_TOT_REPLD),0,NULL,sum(PREV_TOT_REPLD))) BIX_EMC_CRCHANGE,
164 ((nvl(sum(sum(CURR_CRTIME)) OVER(),0)/DECODE(sum(sum(CURR_TOT_REPLD)) OVER(),0,NULL,sum(sum(CURR_TOT_REPLD)) OVER()))
165 - (sum(sum(PREV_CRTIME)) OVER()/DECODE(sum(sum(PREV_TOT_REPLD)) OVER(),0,NULL,sum(sum(PREV_TOT_REPLD)) OVER())))*100/
166 (sum(sum(PREV_CRTIME)) OVER()/DECODE(sum(sum(PREV_TOT_REPLD)) OVER(),0,NULL,sum(sum(PREV_TOT_REPLD)) OVER())) BIX_PMV_TOTAL10,
167 nvl(sum(PREV_CRTIME),0)/(3600*DECODE(sum(PREV_TOT_REPLD),0,NULL,sum(PREV_TOT_REPLD))) BIX_EMC_PRCRTIME,
168 nvl(sum(CURR_ONEDONE),0)*100/sum(CURR_THREADS) BIX_EMC_ONE_DONE_CP,
169 nvl(sum(sum(CURR_ONEDONE)) over(),0)*100/sum(sum(CURR_THREADS)) over() BIX_PMV_TOTAL6,
170 nvl(sum(CURR_ONEDONE),0)*100/sum(CURR_THREADS)- nvl(sum(PREV_ONEDONE),0)*100/sum(PREV_THREADS) BIX_EMC_ODCHANGE,
171 nvl(sum(sum(CURR_ONEDONE)) over(),0)*100/sum(sum(CURR_THREADS)) over()-
172 nvl(sum(sum(PREV_ONEDONE)) over(),0)*100/sum(sum(PREV_THREADS)) over() BIX_PMV_TOTAL11,
173 nvl(sum(curr_BACKLOG),0) BIX_EMC_BACKLOG_CP,
174 nvl(sum(sum(curr_BACKLOG)) over(),0) BIX_PMV_TOTAL7,
175 (NVL(SUM(curr_backlog),0) - DECODE(SUM(prev_backlog),0,NULL,SUM(prev_backlog)))
176 / DECODE(SUM(prev_backlog),0,NULL,SUM(prev_backlog)) * 100 BIX_EMC_BACKCHANGE,
177 (SUM(SUM(curr_backlog)) OVER() - DECODE(SUM(SUM(prev_backlog)) OVER(),0,NULL,
178 SUM(SUM(prev_backlog)) OVER()))
179 / DECODE(SUM(SUM(prev_backlog)) OVER(),0,NULL,SUM(SUM(prev_backlog)) OVER()) * 100 BIX_PMV_TOTAL12,
180 NVL(SUM(curr_sr),0) BIX_EMC_SR_CP,
181 NVL(SUM(SUM(curr_sr)) OVER(),0) BIX_PMV_TOTAL3,
182 (NVL(SUM(curr_sr),0) - SUM(prev_sr)) / SUM(prev_sr) * 100 BIX_EMC_SRCHANGE,
183 (NVL(SUM(SUM(curr_sr)) OVER(),0) - SUM(SUM(prev_sr)) OVER()) / SUM(SUM(prev_sr)) OVER() * 100 BIX_PMV_TOTAL13,
184 NVL(SUM(curr_leads),0) BIX_EMC_LEADS_CP,
185 NVL(SUM(SUM(curr_leads)) OVER(),0) BIX_PMV_TOTAL14,
186 (NVL(SUM(curr_leads),0) - SUM(prev_leads)) / SUM(prev_leads) * 100 BIX_EMC_LEADSCHANGE,
187 (NVL(SUM(SUM(curr_leads)) OVER(),0) - SUM(SUM(prev_leads)) OVER()) / SUM(SUM(prev_leads)) OVER() * 100 BIX_PMV_TOTAL15
188 FROM (
189 SELECT party_id PARTY_ID,
190 sum(DECODE(cal.report_date,&BIS_CURRENT_ASOF_DATE,EMAILS_OFFERED_IN_PERIOD,NULL)) CURR_RECEIVED,
191 sum(DECODE(cal.report_date,&BIS_PREVIOUS_ASOF_DATE,EMAILS_OFFERED_IN_PERIOD,NULL)) PREV_RECEIVED,
192 sum(DECODE(cal.report_date,&BIS_CURRENT_ASOF_DATE,EMAILS_REPLIED_IN_PERIOD,NULL)) CURR_REPLD,
193 sum(DECODE(cal.report_date,&BIS_PREVIOUS_ASOF_DATE,EMAILS_REPLIED_IN_PERIOD,NULL)) PREV_REPLD,
194 sum(DECODE(cal.report_date,&BIS_CURRENT_ASOF_DATE,EMAILS_DELETED_IN_PERIOD,NULL)) CURR_DEL,
195 sum(DECODE(cal.report_date,&BIS_PREVIOUS_ASOF_DATE,EMAILS_DELETED_IN_PERIOD,NULL)) PREV_DEL,
196 SUM(decode(cal.report_date,&BIS_CURRENT_ASOF_DATE,
197 NVL(EMAILS_REPLIED_IN_PERIOD,0) + NVL(EMAILS_AUTO_REPLIED_IN_PERIOD,0),NULL)) CURR_TOT_REPLD,
198 SUM(decode(cal.report_date,&BIS_PREVIOUS_ASOF_DATE,
199 NVL(EMAILS_REPLIED_IN_PERIOD,0) + NVL(EMAILS_AUTO_REPLIED_IN_PERIOD,0),NULL)) PREV_TOT_REPLD,
200 sum(DECODE(cal.report_date,&BIS_CURRENT_ASOF_DATE,EMAILS_RPLD_BY_GOAL_IN_PERIOD,NULL)) CURR_MSGSGOAL,
201 sum(DECODE(cal.report_date,&BIS_PREVIOUS_ASOF_DATE,EMAILS_RPLD_BY_GOAL_IN_PERIOD,NULL)) PREV_MSGSGOAL,
202 sum(DECODE(cal.report_date,&BIS_CURRENT_ASOF_DATE,EMAILS_RSL_AND_TRFD_IN_PERIOD,NULL)) CURR_TRAN,
203 sum(DECODE(cal.report_date,&BIS_PREVIOUS_ASOF_DATE,EMAILS_RSL_AND_TRFD_IN_PERIOD,NULL)) PREV_TRAN,
204 sum(DECODE(cal.report_date,&BIS_CURRENT_ASOF_DATE,SR_CREATED_IN_PERIOD,NULL)) CURR_SR,
205 sum(DECODE(cal.report_date,&BIS_PREVIOUS_ASOF_DATE,SR_CREATED_IN_PERIOD,NULL)) PREV_SR,
206 sum(DECODE(cal.report_date,&BIS_CURRENT_ASOF_DATE,LEADS_CREATED_IN_PERIOD,NULL)) CURR_LEADS,
207 sum(DECODE(cal.report_date,&BIS_PREVIOUS_ASOF_DATE,LEADS_CREATED_IN_PERIOD,NULL)) PREV_LEADS,
208 sum(DECODE(cal.report_date,&BIS_CURRENT_ASOF_DATE,EMAIL_RESP_TIME_IN_PERIOD,NULL)) CURR_CRTIME,
209 sum(DECODE(cal.report_date,&BIS_PREVIOUS_ASOF_DATE,EMAIL_RESP_TIME_IN_PERIOD,NULL)) PREV_CRTIME,
210 sum(DECODE(cal.report_date,&BIS_CURRENT_ASOF_DATE,ONE_RSLN_IN_PERIOD,NULL)) CURR_ONEDONE,
211 sum(DECODE(cal.report_date,&BIS_PREVIOUS_ASOF_DATE,ONE_RSLN_IN_PERIOD,NULL)) PREV_ONEDONE,
212 sum(DECODE(cal.report_date,&BIS_CURRENT_ASOF_DATE,INTERACTION_THREADS_IN_PERIOD,NULL)) CURR_THREADS,
213 sum(DECODE(cal.report_date,&BIS_PREVIOUS_ASOF_DATE,INTERACTION_THREADS_IN_PERIOD,NULL)) PREV_THREADS
214 FROM bix_email_details_mv fact,
215 fii_time_rpt_struct cal
216 WHERE fact.time_id = cal.time_id
217 AND fact.period_type_id = cal.period_type_id
218 AND row_type = :l_row_type
219 AND cal.report_date IN ( &BIS_CURRENT_ASOF_DATE,&BIS_PREVIOUS_ASOF_DATE)
220 AND bitand(cal.record_type_id,&BIS_NESTED_PATTERN)=cal.record_type_id
221 ' || l_where_clause ||
222 ' GROUP BY party_id
223 ) email,
224 (
225 SELECT party_id PARTY_ID,
226 SUM(DECODE(period_start_date,:l_max_collect_date,
227 NVL(ACCUMULATED_OPEN_EMAILS,0) + NVL(ACCUMULATED_EMAILS_IN_QUEUE,0) )) curr_backlog,
228 SUM(DECODE(period_start_date,&BIS_PREVIOUS_ASOF_DATE,
229 NVL(ACCUMULATED_OPEN_EMAILS,0) + NVL(ACCUMULATED_EMAILS_IN_QUEUE,0) )) prev_backlog
230 FROM bix_email_details_mv
231 WHERE time_id IN (TO_CHAR(:l_max_collect_date,''J''),TO_CHAR(&BIS_PREVIOUS_ASOF_DATE,''J''))
232 AND period_type_id = :l_period_type_id
233 AND row_type = :l_row_type
234 ' || l_where_clause ||
235 ' GROUP BY party_id
236 ) accu,
237 hz_parties hzp
238 WHERE email.party_id = hzp.party_id
239 AND email.party_id = accu.party_id (+)
240 GROUP BY hzp.party_id,decode(hzp.party_id,'||l_dummy_cust||',:l_unident_string,hzp.party_name) ';
241
242 --START 001
243
244 l_sqltext := 'SELECT * FROM ( '||l_sqltext ||' ) WHERE
245 ABS(NVL(BIX_EMC_REPLD_CP ,0))+ABS(NVL(BIX_EMC_REPCHANGE ,0))+ABS(NVL(BIX_EMC_MSGSGOAL_CP,0))+ABS(NVL(BIX_EMC_MGCHANGE ,0))+
246 ABS(NVL(BIX_EMC_CRTIME_CP ,0))+ABS(NVL(BIX_EMC_CRCHANGE ,0))+ABS(NVL(BIX_EMC_ONE_DONE_CP,0))+ABS(NVL(BIX_EMC_ODCHANGE ,0))+
247 ABS(NVL(BIX_EMC_RCVD_CP ,0))+ABS(NVL(BIX_EMC_RCVDCHANGE ,0))+ABS(NVL(BIX_EMC_BACKLOG_CP ,0))+ABS(NVL(BIX_EMC_BACKCHANGE ,0))+
248 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))
249 !=0 &ORDER_BY_CLAUSE ';
250
251 --END 001
252
253 --
254 --NOTE: DO not make the join to hz_parties as a OUTER JOIN.
255 --If you do so, there will be NULL customer names which are not really sub-totals,
256 --but the program will interpret those rows to be sub-totals as well.
257 --
258 p_sql_text := l_sqltext;
259
260
261 -- insert Period Type ID bind variable
262
263 l_custom_rec.attribute_name := ':l_period_type_id' ;
264 l_custom_rec.attribute_value:= 1;
265 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
266 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.NUMERIC_BIND;
267
268 p_custom_output.Extend();
269 p_custom_output(p_custom_output.count) := l_custom_rec;
270
271 -- Insert account Bind Variable
272
273 IF ( l_account IS NOT NULL) THEN
274 l_custom_rec.attribute_name := ':l_account' ;
275 l_custom_rec.attribute_value:= l_account;
276 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
277 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
278
279 p_custom_output.Extend();
280 p_custom_output(p_custom_output.count) := l_custom_rec;
281 END IF;
282
283
284 IF ( l_classification IS NOT NULL) THEN
285 l_custom_rec.attribute_name := ':l_classification' ;
286 l_custom_rec.attribute_value:= l_classification;
287 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
288 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
289
290 p_custom_output.Extend();
291 p_custom_output(p_custom_output.count) := l_custom_rec;
292 END IF;
293
294 l_custom_rec.attribute_name := ':l_max_collect_date' ;
295 l_custom_rec.attribute_value:= l_max_collect_date;
296 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
297 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.DATE_BIND;
298
299 p_custom_output.Extend();
300 p_custom_output(p_custom_output.count) := l_custom_rec;
301
302 l_custom_rec.attribute_name := ':l_unident_string' ;
303 l_custom_rec.attribute_value:= l_unident_string;
304 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
305 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
306
307 p_custom_output.Extend();
308 p_custom_output(p_custom_output.count) := l_custom_rec;
309
310 l_custom_rec.attribute_name := ':l_application_id';
311 l_custom_rec.attribute_value:= l_application_id;
312 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
313 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.NUMERIC_BIND;
314
315 p_custom_output.EXTEND;
316 p_custom_output(p_custom_output.COUNT) := l_custom_rec;
317
318 l_custom_rec.attribute_name := ':l_row_type' ;
319 l_custom_rec.attribute_value:= l_row_type;
320 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
321 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
322
323 p_custom_output.Extend();
324 p_custom_output(p_custom_output.count) := l_custom_rec;
325
326 EXCEPTION
327 WHEN OTHERS THEN
328 NULL;
329 END GET_SQL;
330 END BIX_PMV_EMC_CUSTDET_RPT_PKG;