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_PMV_EMC_RESOLV_RPT_PKG

Source


1 PACKAGE BODY BIX_PMV_EMC_RESOLV_RPT_PKG AS
2 /*$Header: bixeresb.plb 120.0 2005/05/25 17:22:28 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 -- 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 /*
66 l_sqltext := '
67 SELECT
68 ''[email protected]'' VIEWBY,
69 10 BIX_EMC_COMPLETED,
70 20 BIX_EMC_COMPCHANGE,
71 30 BIX_EMC_REPLD,
72 40 BIX_EMC_REPCHANGE,
73 50 BIX_EMC_DELETED,
74 60 BIX_EMC_DELCHANGE,
75 70 BIX_EMC_AUTOREPLD,
76 80 BIX_EMC_AUTOREPLD_CHANGE,
77 90  BIX_EMC_AUTODELETED,
78 100 BIX_EMC_AUTODELETED_CHANGE,
79 10 BIX_EMC_AUTORESOLV,
80 20 BIX_EMC_AUTORESOLV_CHANGE,
81 30 BIX_EMC_AUTOUPDATESR,
82 40 BIX_EMC_AUTOUPDATESR_CHANGE,
83 10 BIX_PMV_TOTAL3,
84 20 BIX_PMV_TOTAL4,
85 30 BIX_PMV_TOTAL5,
86 40 BIX_PMV_TOTAL6,
87 50 BIX_PMV_TOTAL7,
88 60 BIX_PMV_TOTAL8,
89 70 BIX_PMV_TOTAL9,
90 80 BIX_PMV_TOTAL10,
91 90 BIX_PMV_TOTAL11,
92 100 BIX_PMV_TOTAL12,
93 10 BIX_PMV_TOTAL13,
94 20 BIX_PMV_TOTAL14,
95 30 BIX_PMV_TOTAL15,
96 40 BIX_PMV_TOTAL16,
97 1000 BIX_EMC_AUTORESPONSE,
98 2000 BIX_EMC_RESPONSE
99 FROM DUAL';
100 
101 p_sql_text := l_sqltext;
102 */
103 
104    l_sqltext :=
105   '
106   SELECT * FROM
107   (
108   SELECT
109     lookup_table.value VIEWBY,
110     lookup_table.id    VIEWBYID,
111     NVL(SUM(curr_completed),0) BIX_EMC_COMPLETED_CP,
112     (NVL(SUM(curr_completed),0) - DECODE(SUM(prev_completed),0,NULL,SUM(prev_completed)))
113     / DECODE(SUM(prev_completed),0,NULL,SUM(prev_completed)) * 100 BIX_EMC_COMPCHANGE,
114     NVL(SUM(curr_replied),0) BIX_EMC_REPLD,
115     (NVL(SUM(curr_replied),0) - SUM(prev_replied)) / SUM(prev_replied) * 100 BIX_EMC_REPCHANGE,
116     NVL(SUM(curr_deleted),0) BIX_EMC_DELETED,
117     (NVL(SUM(curr_deleted),0) - SUM(prev_deleted)) / SUM(prev_deleted) * 100 BIX_EMC_DELCHANGE,
118     NVL(SUM(curr_auto_replied),0) BIX_EMC_AUTOREPLD,
119     (NVL(SUM(curr_auto_replied),0) - SUM(prev_auto_replied)) / SUM(prev_auto_replied) * 100 BIX_EMC_AUTOREPLD_CHANGE,
120     NVL(SUM(curr_auto_deleted),0) BIX_EMC_AUTODELETED,
121     (NVL(SUM(curr_auto_deleted),0) - SUM(prev_auto_deleted)) / SUM(prev_auto_deleted) * 100 BIX_EMC_AUTODELETED_CHANGE,
122     NVL(SUM(curr_auto_resolv),0) BIX_EMC_AUTORESOLV,
123     (NVL(SUM(curr_auto_resolv),0) - SUM(prev_auto_resolv)) / SUM(prev_auto_resolv) * 100 BIX_EMC_AUTORESOLV_CHANGE,
124     NVL(SUM(curr_auto_sr),0) BIX_EMC_AUTOUPDATESR,
125     (NVL(SUM(curr_auto_sr),0) - SUM(prev_auto_sr)) / SUM(prev_auto_sr) * 100 BIX_EMC_AUTOUPDATESR_CHANGE,
126     NVL(SUM(curr_replied),0) +  NVL(SUM(curr_deleted),0)  BIX_EMC_RESPONSE,
127     NVL(SUM(curr_auto_replied),0) +  NVL(SUM(curr_auto_deleted),0) +
128     NVL(SUM(curr_auto_sr),0) + NVL(SUM(curr_auto_resolv),0)    BIX_EMC_AUTORESPONSE,
129     NVL(SUM(SUM(curr_completed)) OVER(),0) BIX_PMV_TOTAL3,
130     (NVL(SUM(SUM(curr_completed)) OVER(),0) - DECODE(SUM(SUM(prev_completed)) OVER(),0,NULL,SUM(SUM(prev_completed)) OVER() ))
131     / DECODE(SUM(SUM(prev_completed)) OVER(),0,NULL,SUM(SUM(prev_completed)) OVER()) * 100 BIX_PMV_TOTAL4,
132     NVL(SUM(SUM(curr_replied)) OVER(),0) BIX_PMV_TOTAL5,
133     (NVL(SUM(SUM(curr_replied)) OVER(),0) - SUM(SUM(prev_replied)) OVER()) / SUM(SUM(prev_replied)) OVER() * 100 BIX_PMV_TOTAL6,
134     NVL(SUM(SUM(curr_deleted)) OVER(),0) BIX_PMV_TOTAL7,
135     (NVL(SUM(SUM(curr_deleted)) OVER(),0) - SUM(SUM(prev_deleted)) OVER()) / SUM(SUM(prev_deleted)) OVER() * 100 BIX_PMV_TOTAL8,
136     NVL(SUM(SUM(curr_auto_replied)) OVER(),0) BIX_PMV_TOTAL9,
137     (NVL(SUM(SUM(curr_auto_replied)) OVER(),0) - SUM(SUM(prev_auto_replied)) OVER()) / SUM(SUM(prev_auto_replied)) OVER() * 100 BIX_PMV_TOTAL10,
138     NVL(SUM(SUM(curr_auto_deleted)) OVER() ,0) BIX_PMV_TOTAL11,
139     (NVL(SUM(SUM(curr_auto_deleted)) OVER(),0) - SUM(SUM(prev_auto_deleted)) OVER()) / SUM(SUM(prev_auto_deleted)) OVER() * 100 BIX_PMV_TOTAL12,
140     NVL(SUM(SUM(curr_auto_resolv)) OVER(),0) BIX_PMV_TOTAL13,
141     (NVL(SUM(SUM(curr_auto_resolv)) OVER(),0) - SUM(SUM(prev_auto_resolv)) OVER()) / SUM(SUM(prev_auto_resolv)) OVER() * 100 BIX_PMV_TOTAL14,
142     NVL(SUM(SUM(curr_auto_sr)) OVER(),0) BIX_PMV_TOTAL15,
143     (NVL(SUM(SUM(curr_auto_sr)) OVER(),0) - SUM(SUM(prev_auto_sr)) OVER()) / SUM(SUM(prev_auto_sr)) OVER() * 100 BIX_PMV_TOTAL16,
144     NVL(SUM(SUM(curr_replied)) OVER(),0) +  NVL(SUM(SUM(curr_deleted)) OVER(),0)  BIX_CALC_ITEM33,--001
145     NVL(SUM(SUM(curr_auto_replied)) OVER(),0) +  NVL(SUM(SUM(curr_auto_deleted)) OVER(),0) +
146     NVL(SUM(SUM(curr_auto_sr)) OVER(),0) + NVL(SUM(SUM(curr_auto_resolv)) OVER(),0)    BIX_CALC_ITEM34 --001
147   FROM
148   ( ';
149 
150   IF l_view_by = 'EMAIL ACCOUNT+EMAIL ACCOUNT' THEN
151     l_sqltext := l_sqltext || ' SELECT
152      email_account_id id,
153      SUM(DECODE(calendar.report_date,&BIS_CURRENT_ASOF_DATE,
154                 NVL(EMAILS_REPLIED_IN_PERIOD,0) +
155                 NVL(EMAILS_AUTO_REPLIED_IN_PERIOD,0) +
156                 NVL(EMAILS_DELETED_IN_PERIOD,0) +
157                 NVL(EMAILS_AUTO_DELETED_IN_PERIOD,0)+
158                 NVL(EMAILS_AUTO_UPTD_SR_IN_PERIOD,0)+
159                 NVL(EMAILS_AUTO_RESOLVED_IN_PERIOD,0)
160                 )) curr_completed,
161      SUM(DECODE(calendar.report_date,&BIS_PREVIOUS_ASOF_DATE,
162                 NVL(EMAILS_REPLIED_IN_PERIOD,0) +
163                 NVL(EMAILS_AUTO_REPLIED_IN_PERIOD,0) +
164                 NVL(EMAILS_DELETED_IN_PERIOD,0) +
165                 NVL(EMAILS_AUTO_DELETED_IN_PERIOD,0)+
166                 NVL(EMAILS_AUTO_UPTD_SR_IN_PERIOD,0)+
167                 NVL(EMAILS_AUTO_RESOLVED_IN_PERIOD,0)
168                 )) prev_completed,
169      SUM(DECODE(calendar.report_date,&BIS_CURRENT_ASOF_DATE,EMAILS_REPLIED_IN_PERIOD)) curr_replied,
170      SUM(DECODE(calendar.report_date,&BIS_PREVIOUS_ASOF_DATE,EMAILS_REPLIED_IN_PERIOD)) prev_replied,
171      SUM(DECODE(calendar.report_date,&BIS_CURRENT_ASOF_DATE,EMAILS_DELETED_IN_PERIOD)) curr_deleted,
172      SUM(DECODE(calendar.report_date,&BIS_PREVIOUS_ASOF_DATE,EMAILS_DELETED_IN_PERIOD)) prev_deleted,
173      SUM(DECODE(calendar.report_date,&BIS_CURRENT_ASOF_DATE,EMAILS_AUTO_REPLIED_IN_PERIOD)) curr_auto_replied,
174      SUM(DECODE(calendar.report_date,&BIS_PREVIOUS_ASOF_DATE,EMAILS_AUTO_REPLIED_IN_PERIOD)) prev_auto_replied,
175      SUM(DECODE(calendar.report_date,&BIS_CURRENT_ASOF_DATE,EMAILS_AUTO_DELETED_IN_PERIOD)) curr_auto_deleted,
176      SUM(DECODE(calendar.report_date,&BIS_PREVIOUS_ASOF_DATE,EMAILS_AUTO_DELETED_IN_PERIOD)) prev_auto_deleted,
177      SUM(DECODE(calendar.report_date,&BIS_CURRENT_ASOF_DATE,EMAILS_AUTO_UPTD_SR_IN_PERIOD)) curr_auto_sr,
178      SUM(DECODE(calendar.report_date,&BIS_PREVIOUS_ASOF_DATE,EMAILS_AUTO_UPTD_SR_IN_PERIOD)) prev_auto_sr,
179      SUM(DECODE(calendar.report_date,&BIS_CURRENT_ASOF_DATE,EMAILS_AUTO_RESOLVED_IN_PERIOD)) curr_auto_resolv,
180      SUM(DECODE(calendar.report_date,&BIS_PREVIOUS_ASOF_DATE,EMAILS_AUTO_RESOLVED_IN_PERIOD)) prev_auto_resolv
181      FROM bix_email_details_mv fact,
182           fii_time_rpt_struct calendar
183         WHERE fact.time_id = calendar.time_id
184 	   AND   fact.row_type = :l_row_type
185         AND fact.period_type_id = calendar.period_type_id
186         AND calendar.report_date IN (&BIS_CURRENT_ASOF_DATE,&BIS_PREVIOUS_ASOF_DATE)
187         AND bitand(calendar.record_type_id,&BIS_NESTED_PATTERN ) =  calendar.record_type_id '
188         || l_where_clause || '
189      GROUP BY email_account_id
190      ) fact, ';
191 	ELSE
192     l_sqltext := l_sqltext || ' SELECT
193      email_classification_id id,
194      SUM(DECODE(calendar.report_date,&BIS_CURRENT_ASOF_DATE,
195                 NVL(EMAILS_REPLIED_IN_PERIOD,0) +
196                 NVL(EMAILS_AUTO_REPLIED_IN_PERIOD,0) +
197                 NVL(EMAILS_DELETED_IN_PERIOD,0) +
198                 NVL(EMAILS_AUTO_DELETED_IN_PERIOD,0)+
199                 NVL(EMAILS_AUTO_UPTD_SR_IN_PERIOD,0)+
200                 NVL(EMAILS_AUTO_RESOLVED_IN_PERIOD,0)
201                 )) curr_completed,
202      SUM(DECODE(calendar.report_date,&BIS_PREVIOUS_ASOF_DATE,
203                 NVL(EMAILS_REPLIED_IN_PERIOD,0) +
204                 NVL(EMAILS_AUTO_REPLIED_IN_PERIOD,0) +
205                 NVL(EMAILS_DELETED_IN_PERIOD,0) +
206                 NVL(EMAILS_AUTO_DELETED_IN_PERIOD,0)+
207                 NVL(EMAILS_AUTO_UPTD_SR_IN_PERIOD,0)+
208                 NVL(EMAILS_AUTO_RESOLVED_IN_PERIOD,0)
209                 )) prev_completed,
210      SUM(DECODE(calendar.report_date,&BIS_CURRENT_ASOF_DATE,EMAILS_REPLIED_IN_PERIOD)) curr_replied,
211      SUM(DECODE(calendar.report_date,&BIS_PREVIOUS_ASOF_DATE,EMAILS_REPLIED_IN_PERIOD)) prev_replied,
212      SUM(DECODE(calendar.report_date,&BIS_CURRENT_ASOF_DATE,EMAILS_DELETED_IN_PERIOD)) curr_deleted,
213      SUM(DECODE(calendar.report_date,&BIS_PREVIOUS_ASOF_DATE,EMAILS_DELETED_IN_PERIOD)) prev_deleted,
214      SUM(DECODE(calendar.report_date,&BIS_CURRENT_ASOF_DATE,EMAILS_AUTO_REPLIED_IN_PERIOD)) curr_auto_replied,
215      SUM(DECODE(calendar.report_date,&BIS_PREVIOUS_ASOF_DATE,EMAILS_AUTO_REPLIED_IN_PERIOD)) prev_auto_replied,
216      SUM(DECODE(calendar.report_date,&BIS_CURRENT_ASOF_DATE,EMAILS_AUTO_DELETED_IN_PERIOD)) curr_auto_deleted,
217      SUM(DECODE(calendar.report_date,&BIS_PREVIOUS_ASOF_DATE,EMAILS_AUTO_DELETED_IN_PERIOD)) prev_auto_deleted,
218      SUM(DECODE(calendar.report_date,&BIS_CURRENT_ASOF_DATE,EMAILS_AUTO_UPTD_SR_IN_PERIOD)) curr_auto_sr,
219      SUM(DECODE(calendar.report_date,&BIS_PREVIOUS_ASOF_DATE,EMAILS_AUTO_UPTD_SR_IN_PERIOD)) prev_auto_sr,
220      SUM(DECODE(calendar.report_date,&BIS_CURRENT_ASOF_DATE,EMAILS_AUTO_RESOLVED_IN_PERIOD)) curr_auto_resolv,
221      SUM(DECODE(calendar.report_date,&BIS_PREVIOUS_ASOF_DATE,EMAILS_AUTO_RESOLVED_IN_PERIOD)) prev_auto_resolv
222      FROM bix_email_details_mv fact,
223           fii_time_rpt_struct calendar
224         WHERE fact.time_id = calendar.time_id
225 	   AND   fact.row_type = :l_row_type
226         AND fact.period_type_id = calendar.period_type_id
227         AND calendar.report_date IN (&BIS_CURRENT_ASOF_DATE,&BIS_PREVIOUS_ASOF_DATE)
228         AND bitand(calendar.record_type_id,&BIS_NESTED_PATTERN ) =  calendar.record_type_id '
229         || l_where_clause || '
230      GROUP BY email_classification_id
231      ) fact, ';
232 	END IF;
233 
234 	IF l_view_by = 'EMAIL ACCOUNT+EMAIL ACCOUNT' THEN
235 	    l_sqltext := l_sqltext || ' bix_email_accounts_v lookup_table ';
236 	ELSE
237 	    l_sqltext := l_sqltext || ' bix_email_classifications_v lookup_table ';
238 	END IF;
239 
240 	l_sqltext := l_sqltext || ' WHERE fact.id = lookup_table.id
241      GROUP BY lookup_table.value,lookup_table.id
242 	)
243 	where
244 	(
245 	nvl(BIX_EMC_COMPLETED_CP,0)+
246 	abs(nvl(BIX_EMC_COMPCHANGE,0))+
247 	nvl(BIX_EMC_RESPONSE,0)+
248 	nvl(BIX_EMC_AUTORESPONSE,0)+
249 	nvl(BIX_EMC_REPLD,0)+
250 	abs(nvl(BIX_EMC_REPCHANGE,0))+
251 	nvl(BIX_EMC_DELETED,0)+
252 	abs(nvl(BIX_EMC_DELCHANGE,0))+
253 	nvl(BIX_EMC_AUTOREPLD,0)+
254 	abs(nvl(BIX_EMC_AUTOREPLD_CHANGE,0))+
255 	nvl(BIX_EMC_AUTODELETED,0)+
256 	abs(nvl(BIX_EMC_AUTODELETED_CHANGE,0))+
257 	nvl(BIX_EMC_AUTORESOLV,0)+
258 	abs(nvl(BIX_EMC_AUTORESOLV_CHANGE,0))+
259 	nvl(BIX_EMC_AUTOUPDATESR,0)+
260 	abs(nvl(BIX_EMC_AUTOUPDATESR_CHANGE,0))
261 	)<> 0
262 	&ORDER_BY_CLAUSE ';
263 
264 p_sql_text := l_sqltext;
265 
266 p_custom_output.EXTEND();
267 l_custom_rec.attribute_name := ':l_period_type_id' ;
268 l_custom_rec.attribute_value:= l_period_type_id;
269 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
270 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.NUMERIC_BIND;
271 
272 p_custom_output.Extend();
273 p_custom_output(p_custom_output.count) := l_custom_rec;
274 
275 -- Insert account Bind Variable
276 
277 IF ( l_account IS NOT NULL) THEN
278 l_custom_rec.attribute_name := ':l_account' ;
279 l_custom_rec.attribute_value:= l_account;
280 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
281 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
282 
283 p_custom_output.Extend();
284 p_custom_output(p_custom_output.count) := l_custom_rec;
285 END IF;
286 
287 IF ( l_classification IS NOT NULL) THEN
288 l_custom_rec.attribute_name := ':l_classification' ;
289 l_custom_rec.attribute_value:= l_classification;
290 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
291 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
292 
293 p_custom_output.Extend();
294 p_custom_output(p_custom_output.count) := l_custom_rec;
295 END IF;
296 
297 l_custom_rec.attribute_name := ':l_max_collect_date' ;
298 l_custom_rec.attribute_value:= l_max_collect_date;
299 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
300 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.DATE_BIND;
301 
302 p_custom_output.Extend();
303 p_custom_output(p_custom_output.count) := l_custom_rec;
304 
305 l_custom_rec.attribute_name := BIS_PMV_PARAMETERS_PUB.VIEW_BY_VALUE;
306 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.View_By_Type;
307 l_custom_rec.attribute_value := l_view_by;
308 
309 p_custom_output.EXTEND();
310 p_custom_output(p_custom_output.COUNT) := l_custom_rec;
311 
312 l_custom_rec.attribute_name := ':l_row_type' ;
313 l_custom_rec.attribute_value:= l_row_type;
314 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
315 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
316 
317 p_custom_output.Extend();
318 p_custom_output(p_custom_output.count) := l_custom_rec;
319 
320 EXCEPTION
321 WHEN OTHERS THEN
322 NULL;
323 END GET_SQL;
324 END  BIX_PMV_EMC_RESOLV_RPT_PKG;