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_BACK_ACT_RPT_PKG

Source


1 PACKAGE BODY BIX_PMV_EMC_BACK_ACT_RPT_PKG AS
2 /*$Header: bixebacr.plb 120.1 2006/05/11 02:15:47 pubalasu 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_custom_rec       BIS_QUERY_ATTRIBUTES;
16 l_period_type_id   NUMBER := 1;
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_max_collect_date   VARCHAR2(100);
24 l_period_start_date  DATE;
25 l_application_id NUMBER := 680;
26 l_classification VARCHAR2(32000);
27 l_view_by  varchar2(1000);
28 l_row_type varchar2(10) := 'AC';
29 
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 SELECT
101 ''[email protected]'' VIEWBY,
102 10 BIX_EMC_BACKLOG,
103 20 BIX_EMC_BACKCHANGE,
104 30 BIX_EMC_AGT_HR_EQ,
105 40 BIX_EMC_COST_EQ,
106 50 BIX_EMC_0_1_DAY,
107 60 BIX_EMC_0_1_DAY_CHANGE,
108 70 BIX_EMC_2_3_DAY,
109 80 BIX_EMC_2_3_DAY_CHANGE,
110 90 BIX_EMC_4_7_DAY,
111 100 BIX_EMC_4_7_DAY_CHANGE,
112 110 BIX_EMC_OVER_7_DAY,
113 120 BIX_EMC_OVER_7_DAY_CHANGE,
114 130 BIX_PMV_TOTAL1,
115 140 BIX_PMV_TOTAL2,
116 150 BIX_PMV_TOTAL3,
117 160 BIX_PMV_TOTAL4,
118 170 BIX_PMV_TOTAL5,
119 180 BIX_PMV_TOTAL6,
120 190 BIX_PMV_TOTAL7,
121 200 BIX_PMV_TOTAL8,
122 210 BIX_PMV_TOTAL9,
123 220 BIX_PMV_TOTAL10,
124 230 BIX_PMV_TOTAL11,
125 240 BIX_PMV_TOTAL12
126 FROM DUAL';
127 
128 p_sql_text := l_sqltext;
129 */
130 
131    l_sqltext :=
132   '
133   SELECT * FROM (
134   SELECT
135     lookup_table.value VIEWBY,
136     lookup_table.id    VIEWBYID,
137     SUM(curr_backlog)  BIX_EMC_BACKLOG_CP,
138     (NVL(SUM(curr_backlog),0) -SUM(prev_backlog))/
139            DECODE(SUM(prev_backlog),0,NULL,SUM(prev_backlog)) * 100 BIX_EMC_BACKCHANGE,
140     SUM(prev_backlog) BIX_EMC_PREVBACKLOG,
141     SUM(curr_backlog)/(SUM(total_emails_replied)/SUM(total_login_hours)) BIX_EMC_AGT_HR_EQ,
142     NVL(SUM(curr_one_day),0)/DECODE(SUM(curr_backlog),0,NULL,SUM(curr_backlog))*100 BIX_EMC_0_1_DAY_CP,
143     (NVL(SUM(curr_one_day),0)/DECODE(SUM(curr_backlog),0,NULL,SUM(curr_backlog)) -
144            NVL(SUM(prev_one_day),0)/DECODE(SUM(prev_backlog),0,NULL,SUM(prev_backlog))) * 100 BIX_EMC_0_1_DAY_CHANGE,
145     NVL(SUM(curr_three_day),0)/DECODE(SUM(curr_backlog),0,NULL,SUM(curr_backlog))*100 BIX_EMC_2_3_DAY_CP,
146     (NVL(SUM(curr_three_day),0)/DECODE(SUM(curr_backlog),0,NULL,SUM(curr_backlog)) -
147            NVL(SUM(prev_three_day),0)/DECODE(SUM(prev_backlog),0,NULL,SUM(prev_backlog))) * 100 BIX_EMC_2_3_DAY_CHANGE,
148     NVL(SUM(curr_week),0)/DECODE(SUM(curr_backlog),0,NULL,SUM(curr_backlog))*100 BIX_EMC_4_7_DAY_CP,
149     (NVL(SUM(curr_week),0)/DECODE(SUM(curr_backlog),0,NULL,SUM(curr_backlog)) -
150            NVL(SUM(prev_week),0)/DECODE(SUM(prev_backlog),0,NULL,SUM(prev_backlog))) * 100 BIX_EMC_4_7_DAY_CHANGE,
151     NVL(SUM(curr_week_plus),0)/DECODE(SUM(curr_backlog),0,NULL,SUM(curr_backlog))*100 BIX_EMC_OVER_7_DAY_CP,
152     (NVL(SUM(curr_week_plus),0)/DECODE(SUM(curr_backlog),0,NULL,SUM(curr_backlog)) -
153            NVL(SUM(prev_week_plus),0)/DECODE(SUM(prev_backlog),0,NULL,SUM(prev_backlog))) * 100 BIX_EMC_OVER_7_DAY_CHANGE,
154     SUM(SUM(curr_backlog)) OVER() BIX_PMV_TOTAL1,
155     (NVL(SUM(SUM(curr_backlog)) OVER(),0) - SUM(SUM(prev_backlog)) OVER())/
156            DECODE(SUM(SUM(prev_backlog)) OVER(),0,NULL,SUM(SUM(prev_backlog)) OVER()) * 100 BIX_PMV_TOTAL2,
157     SUM(SUM(curr_backlog)) OVER()/(SUM(SUM(total_emails_replied)) OVER()/SUM(SUM(total_login_hours)) OVER()) BIX_PMV_TOTAL3,
158     NVL(SUM(SUM(curr_one_day)) OVER(),0)/DECODE(SUM(SUM(curr_backlog))  OVER(),0,NULL,SUM(SUM(curr_backlog)) OVER())*100 BIX_PMV_TOTAL5,
159     (NVL(SUM(SUM(curr_one_day)) OVER(),0)/DECODE(SUM(SUM(curr_backlog)) OVER(),0,NULL,SUM(SUM(curr_backlog)) OVER()) -
160            NVL(SUM(SUM(prev_one_day)) OVER(),0)/DECODE(SUM(SUM(prev_backlog)) OVER(),0,NULL,SUM(SUM(prev_backlog)) OVER())) * 100 BIX_PMV_TOTAL6,
161     NVL(SUM(SUM(curr_three_day)) OVER(),0)/DECODE(SUM(SUM(curr_backlog)) OVER(),0,NULL,SUM(SUM(curr_backlog)) OVER())*100 BIX_PMV_TOTAL7,
162     (NVL(SUM(SUM(curr_three_day)) OVER(),0)/DECODE(SUM(SUM(curr_backlog)) OVER(),0,NULL,SUM(SUM(curr_backlog)) OVER()) -
163            NVL(SUM(SUM(prev_three_day)) OVER(),0)/DECODE(SUM(SUM(prev_backlog)) OVER(),0,NULL,SUM(SUM(prev_backlog)) OVER())) * 100 BIX_PMV_TOTAL8,
164     NVL(SUM(SUM(curr_week)) OVER(),0)/DECODE(SUM(SUM(curr_backlog)) OVER(),0,NULL,SUM(SUM(curr_backlog)) OVER())*100 BIX_PMV_TOTAL9,
165     (NVL(SUM(SUM(curr_week)) OVER(),0)/DECODE(SUM(SUM(curr_backlog)) OVER(),0,NULL,SUM(SUM(curr_backlog)) OVER()) -
166            NVL(SUM(SUM(prev_week)) OVER(),0)/DECODE(SUM(SUM(prev_backlog)) OVER(),0,NULL,SUM(SUM(prev_backlog)) OVER())) * 100 BIX_PMV_TOTAL10,
167     NVL(SUM(SUM(curr_week_plus)) OVER(),0)/DECODE(SUM(SUM(curr_backlog)) OVER(),0,NULL,SUM(SUM(curr_backlog)) OVER())*100 BIX_PMV_TOTAL11,
168     (NVL(SUM(SUM(curr_week_plus)) OVER(),0)/DECODE(SUM(SUM(curr_backlog)) OVER(),0,NULL,SUM(SUM(curr_backlog)) OVER()) -
169            NVL(SUM(SUM(prev_week_plus)) OVER(),0)/DECODE(SUM(SUM(prev_backlog)) OVER(),0,NULL,SUM(SUM(prev_backlog)) OVER())) * 100 BIX_PMV_TOTAL12
170   FROM
171   ( ';
172 
173   IF l_view_by = 'EMAIL ACCOUNT+EMAIL ACCOUNT' THEN
174   l_sqltext := l_sqltext || '  SELECT
175    email_account_id id,
176    SUM(DECODE(period_start_date,:l_max_collect_date,
177         NVL(ACCUMULATED_OPEN_EMAILS,0) + NVL(ACCUMULATED_EMAILS_IN_QUEUE,0) )) curr_backlog,
178    SUM(DECODE(period_start_date,&BIS_PREVIOUS_ASOF_DATE,
179        NVL(ACCUMULATED_OPEN_EMAILS,0) + NVL(ACCUMULATED_EMAILS_IN_QUEUE,0) )) prev_backlog,
180    NVL(SUM(DECODE(period_start_date,:l_max_collect_date,ACCUMULATED_EMAILS_ONE_DAY)),0) curr_one_day,
181    SUM(DECODE(period_start_date,&BIS_PREVIOUS_ASOF_DATE,ACCUMULATED_EMAILS_ONE_DAY)) prev_one_day,
182    NVL(SUM(DECODE(period_start_date,:l_max_collect_date,ACCUMULATED_EMAILS_THREE_DAYS)),0) curr_three_day,
183    SUM(DECODE(period_start_date,&BIS_PREVIOUS_ASOF_DATE,ACCUMULATED_EMAILS_THREE_DAYS)) prev_three_day,
184    NVL(SUM(DECODE(period_start_date,:l_max_collect_date,ACCUMULATED_EMAILS_WEEK)),0) curr_week,
185    SUM(DECODE(period_start_date,&BIS_PREVIOUS_ASOF_DATE,ACCUMULATED_EMAILS_WEEK)) prev_week,
186    NVL(SUM(DECODE(period_start_date,:l_max_collect_date,ACCUMULATED_EMAILS_WEEK_PLUS)),0) curr_week_plus,
187    SUM(DECODE(period_start_date,&BIS_PREVIOUS_ASOF_DATE,ACCUMULATED_EMAILS_WEEK_PLUS)) prev_week_plus
188    FROM   bix_email_details_mv
189    WHERE  time_id IN (TO_CHAR(:l_max_collect_date,''J''),TO_CHAR(&BIS_PREVIOUS_ASOF_DATE,''J''))
190    AND    row_type = :l_row_type
191    AND   period_type_id = :l_period_type_id ' || l_where_clause || '
192    GROUP BY email_account_id
193    ) fact, ';
194   ELSE
195   l_sqltext := l_sqltext || '  SELECT
196    email_classification_id id,
197    SUM(DECODE(period_start_date,:l_max_collect_date,
198         NVL(ACCUMULATED_OPEN_EMAILS,0) + NVL(ACCUMULATED_EMAILS_IN_QUEUE,0) )) curr_backlog,
199    SUM(DECODE(period_start_date,&BIS_PREVIOUS_ASOF_DATE,
200        NVL(ACCUMULATED_OPEN_EMAILS,0) + NVL(ACCUMULATED_EMAILS_IN_QUEUE,0) )) prev_backlog,
201    NVL(SUM(DECODE(period_start_date,:l_max_collect_date,ACCUMULATED_EMAILS_ONE_DAY)),0) curr_one_day,
202    SUM(DECODE(period_start_date,&BIS_PREVIOUS_ASOF_DATE,ACCUMULATED_EMAILS_ONE_DAY)) prev_one_day,
203    NVL(SUM(DECODE(period_start_date,:l_max_collect_date,ACCUMULATED_EMAILS_THREE_DAYS)),0) curr_three_day,
204    SUM(DECODE(period_start_date,&BIS_PREVIOUS_ASOF_DATE,ACCUMULATED_EMAILS_THREE_DAYS)) prev_three_day,
205    NVL(SUM(DECODE(period_start_date,:l_max_collect_date,ACCUMULATED_EMAILS_WEEK)),0) curr_week,
206    SUM(DECODE(period_start_date,&BIS_PREVIOUS_ASOF_DATE,ACCUMULATED_EMAILS_WEEK)) prev_week,
207    NVL(SUM(DECODE(period_start_date,:l_max_collect_date,ACCUMULATED_EMAILS_WEEK_PLUS)),0) curr_week_plus,
208    SUM(DECODE(period_start_date,&BIS_PREVIOUS_ASOF_DATE,ACCUMULATED_EMAILS_WEEK_PLUS)) prev_week_plus
209    FROM   bix_email_details_mv
210    WHERE  time_id IN (TO_CHAR(:l_max_collect_date,''J''),TO_CHAR(&BIS_PREVIOUS_ASOF_DATE,''J''))
211    AND    row_type = :l_row_type
212    AND   period_type_id = :l_period_type_id ' || l_where_clause || '
213    GROUP BY email_classification_id
214    ) fact, ';
215   END IF;
216 
217   l_sqltext := l_sqltext || '  (
218       SELECT SUM(EMAILS_REPLIED_IN_PERIOD) total_emails_replied
219       FROM bix_email_details_mv fact,
220            fii_time_rpt_struct calendar
221       WHERE fact.time_id = calendar.time_id
222       AND fact.period_type_id = calendar.period_type_id
223       AND calendar.report_date = &BIS_CURRENT_ASOF_DATE
224 	 AND fact.row_type = :l_row_type
225       AND bitand(calendar.record_type_id,&BIS_NESTED_PATTERN ) =  calendar.record_type_id
226      ),
227     (
228       SELECT SUM(LOGIN_TIME)/3600 total_login_hours
229       FROM bix_agent_session_f fact,
230            fii_time_rpt_struct calendar
231       WHERE fact.application_id = :l_application_id
232       AND  fact.time_id = calendar.time_id
233       AND fact.period_type_id = calendar.period_type_id
234       AND calendar.report_date = &BIS_CURRENT_ASOF_DATE
235       AND bitand(calendar.record_type_id,&BIS_NESTED_PATTERN ) =  calendar.record_type_id
236      ), ';
237 
238 
239 	IF l_view_by = 'EMAIL ACCOUNT+EMAIL ACCOUNT' THEN
240 	l_sqltext := l_sqltext || ' bix_email_accounts_v lookup_table ';
241 	ELSE
242 	l_sqltext := l_sqltext || ' bix_email_classifications_v lookup_table ';
243 	END IF;
244 
245 
246      l_sqltext := l_sqltext || ' WHERE fact.id = lookup_table.id
247      GROUP BY lookup_table.value,lookup_table.id
248 	)
249      where
250 	(
251 	nvl(BIX_EMC_PREVBACKLOG,0)+
252 	nvl(BIX_EMC_BACKLOG_CP,0)+
253 	abs(nvl(BIX_EMC_BACKCHANGE,0))+
254 	nvl(BIX_EMC_AGT_HR_EQ,0)+
255 	nvl(BIX_EMC_0_1_DAY_CP,0)+
256 	abs(nvl(BIX_EMC_0_1_DAY_CHANGE,0))+
257 	nvl(BIX_EMC_2_3_DAY_CP,0)+
258 	abs(nvl(BIX_EMC_2_3_DAY_CHANGE,0))+
259 	nvl(BIX_EMC_4_7_DAY_CP,0)+
260 	abs(nvl(BIX_EMC_4_7_DAY_CHANGE,0))+
261 	nvl(BIX_EMC_OVER_7_DAY_CP,0)+
262 	abs(nvl(BIX_EMC_OVER_7_DAY_CHANGE,0))
263 	) <> 0
264 	&ORDER_BY_CLAUSE ';
265 
266 p_sql_text := l_sqltext;
267 
268 
269 -- insert Period Type ID bind variable
270 
271 l_custom_rec.attribute_name := ':l_period_type_id' ;
272 l_custom_rec.attribute_value:= l_period_type_id;
273 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
274 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.NUMERIC_BIND;
275 
276 p_custom_output.Extend();
277 p_custom_output(p_custom_output.count) := l_custom_rec;
278 
279 -- Insert account Bind Variable
280 
281 IF ( l_account IS NOT NULL) THEN
282 l_custom_rec.attribute_name := ':l_account' ;
283 l_custom_rec.attribute_value:= l_account;
284 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
285 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
286 
287 p_custom_output.Extend();
288 p_custom_output(p_custom_output.count) := l_custom_rec;
289 END IF;
290 
291 IF ( l_classification IS NOT NULL) THEN
292 l_custom_rec.attribute_name := ':l_classification' ;
293 l_custom_rec.attribute_value:= l_classification;
294 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
295 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
296 
297 p_custom_output.Extend();
298 p_custom_output(p_custom_output.count) := l_custom_rec;
299 END IF;
300 
301 l_custom_rec.attribute_name := ':l_max_collect_date' ;
302 l_custom_rec.attribute_value:= l_max_collect_date;
303 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
304 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.DATE_BIND;
305 
306 p_custom_output.Extend();
307 p_custom_output(p_custom_output.count) := l_custom_rec;
308 
309 l_custom_rec.attribute_name := ':l_application_id';
310 l_custom_rec.attribute_value:= l_application_id;
311 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
312 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.NUMERIC_BIND;
313 
314 p_custom_output.EXTEND;
315 p_custom_output(p_custom_output.COUNT) := l_custom_rec;
316 
317 l_custom_rec.attribute_name := BIS_PMV_PARAMETERS_PUB.VIEW_BY_VALUE;
318 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.View_By_Type;
319 l_custom_rec.attribute_value := l_view_by;
320 
321 p_custom_output.EXTEND();
322 p_custom_output(p_custom_output.COUNT) := l_custom_rec;
323 
324 l_custom_rec.attribute_name := ':l_row_type' ;
325 l_custom_rec.attribute_value:= l_row_type;
326 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
327 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
328 
329 p_custom_output.Extend();
330 p_custom_output(p_custom_output.count) := l_custom_rec;
331 
332 EXCEPTION
333 WHEN OTHERS THEN
334 NULL;
335 END GET_SQL;
336 END  BIX_PMV_EMC_BACK_ACT_RPT_PKG;