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_KPI_PRTLT_PKG

Source


1 PACKAGE BODY BIX_PMV_EMC_KPI_PRTLT_PKG AS
2 /*$Header: bixekpib.plb 115.11 2003/12/20 02:14:42 djambula 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_as_of_date   DATE;
11 l_period_type	varchar2(2000);
12 l_comp_type    varchar2(2000);
13 l_account      varchar2(1000);
14 l_record_type_id NUMBER;
15 l_sql_errm      varchar2(32000);
16 l_agent_cost      NUMBER := 0;
17 l_cust_resp_time_goal NUMBER;
18 l_service_level_goal  NUMBER;
19 l_custom_rec       BIS_QUERY_ATTRIBUTES;
20 l_period_type_id   NUMBER := 1;
21 l_start_date   DATE;
22 l_end_date     DATE;
23 l_period_from  DATE;
24 l_period_to    DATE;
25 l_max_collect_date   VARCHAR2(100);
26 l_period_start_Date  DATE;
27 l_dummy_cust     NUMBER;
28 l_application_id NUMBER := 680;
29 l_classification VARCHAR2(32000);
30 l_where_clause VARCHAR2(32000);
31 l_view_by varchar2(1000);
32 l_row_type varchar2(10) := 'ACP';
33 
34 BEGIN
35  p_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
36  l_custom_rec := BIS_PMV_PARAMETERS_PUB.Initialize_Query_Type;
37 
38 /*
39  l_sqltext :=  '
40 	SELECT
41 	75   BIX_EMC_SVCLVL,
42 	80   BIX_PMV_TOTAL1,
43 	90   BIX_EMC_GOAL,
44 	90   BIX_PMV_TOTAL2,
45 	36   BIX_EMC_CRTIME,
46 	34.2 BIX_PMV_TOTAL3,
47 	30   BIX_EMC_MSGSGOAL,
48 	30   BIX_PMV_TOTAL4,
49 	273000 BIX_EMC_RCVD,
50 	242970 BIX_PMV_TOTAL5,
51 	268000 BIX_EMC_REPLD,
52 	246560 BIX_PMV_TOTAL6,
53 	18500  BIX_EMC_BACKLOG,
54 	19240  BIX_PMV_TOTAL7,
55 	5.2    BIX_EMC_REPPERHR,
56 	5.824  BIX_PMV_TOTAL8,
57 	56     BIX_EMC_ONE_DONE,
58 	54.5   BIX_PMV_TOTAL9,
59 	15     BIX_EMC_TRANRATIO,
60 	7      BIX_PMV_TOTAL10,
61 	0.7    BIX_EMC_DELRATIO,
62 	-0.3   BIX_PMV_TOTAL11,
63 	357    BIX_EMC_CUST_COUNT,
64 	317.73 BIX_PMV_TOTAL12,
65 	44250  BIX_EMC_SR,
66 	40267.5 BIX_PMV_TOTAL13,
67 	3.2     BIX_EMC_COSTPERMSG,
68 	2.88    BIX_PMV_TOTAL14,
69 	110000  BIX_EMC_LABOR_COST,
70 	101750  BIX_PMV_TOTAL15
71 	FROM DUAL';
72 */
73 
74  BEGIN
75  IF (FND_PROFILE.DEFINED('BIX_EMAIL_GOAL')) THEN
76     l_cust_resp_time_goal := TO_NUMBER(FND_PROFILE.VALUE('BIX_EMAIL_GOAL'));
77  END IF;
78 
79  IF l_cust_resp_time_goal IS NULL THEN
80     l_cust_resp_time_goal := 0;
81  END IF;
82  EXCEPTION
83  WHEN OTHERS THEN
84  l_cust_resp_time_goal := 0;
85  END;
86 
87  BEGIN
88  IF (FND_PROFILE.DEFINED('BIX_EMAIL_SLVL_GOAL')) THEN
89     l_service_level_goal := ROUND(TO_NUMBER(FND_PROFILE.VALUE('BIX_EMAIL_SLVL_GOAL')),1);
90  END IF;
91 
92  IF l_service_level_goal IS NULL THEN
93     l_service_level_goal := 0;
94  END IF;
95 
96  EXCEPTION
97  WHEN OTHERS THEN
98   l_service_level_goal := 0;
99  END;
100 
101  --
102  --Retrieve the dummy (unidentified) customer id which is used by EMC
103  --
104  IF (FND_PROFILE.DEFINED('IEM_DEFAULT_CUSTOMER_ID')) THEN
105    l_dummy_cust := TO_NUMBER(FND_PROFILE.VALUE('IEM_DEFAULT_CUSTOMER_ID'));
106  END IF;
107 
108  IF l_dummy_cust IS NULL THEN
109     l_dummy_cust := -1;
110  END IF;
111 
112  -- Get the parameters
113 
114 BIX_PMV_DBI_UTL_PKG.get_emc_page_params( p_page_parameter_tbl,
115                                          l_as_of_date,
116                                          l_period_type,
117                                          l_record_type_id,
118                                          l_comp_type,
119                                          l_account,
120 					 l_classification,
121 					 l_view_by
122                                       );
123 
124 
125 -- If the account is not 'All'
126 
127  IF l_account IS NOT NULL THEN
128  l_where_clause := 'AND email_account_id IN (:l_account) ';
129  END IF;
130 
131 
132  IF l_classification IS NOT NULL THEN
133  l_where_clause := l_where_clause || ' AND email_classification_id IN (:l_classification) ';
134  END IF;
135 
136 l_period_start_Date := BIX_PMV_DBI_UTL_PKG.period_start_date(l_as_of_date,l_period_type);
137 
138  /* Get the MAX date for which data is collected in Email Summary table */
139 
140     BIS_COLLECTION_UTILITIES.get_last_refresh_dates('BIX_EMAIL_DETAILS_F',
141                                                      l_start_date,
142                                                      l_end_date,
143                                                      l_period_from,
144                                                      l_period_to
145                                                    );
146 
147 
148  /* if the data is not collected till AS OF DATE
149     then get the accumulated measures from the MAX collected DATE time bucket
150     if the max collect date falls between current period */
151 
152   IF (NVL(l_period_to,l_as_of_date) >= l_as_of_date ) THEN
153    l_max_collect_date := TO_CHAR(l_as_of_date,'DD/MM/YYYY');
154   ELSIF ( l_period_to < l_period_start_date ) THEN
155     l_max_collect_date := TO_CHAR(l_as_of_date,'DD/MM/YYYY');
156   ELSE
157     l_max_collect_date := TO_CHAR(l_period_to,'DD/MM/YYYY');
158   END IF;
159 
160 
161    l_sqltext :=
162   'SELECT
163     ROUND(SUM(curr_slvl) * 100,1) BIX_EMC_SVCLVL,
164     ROUND(SUM(prev_slvl) * 100,1) BIX_PMV_TOTAL1,
165     ' || l_service_level_goal || ' BIX_EMC_GOAL,'||  l_service_level_goal || ' BIX_PMV_TOTAL2,
166     ROUND(SUM(curr_avg_resp_time)/3600,1) BIX_EMC_CRTIME,
167     ROUND(SUM(prev_avg_resp_time)/3600,1) BIX_PMV_TOTAL3,
168     ' || l_cust_resp_time_goal || ' BIX_EMC_MSGSGOAL,'||  l_cust_resp_time_goal || ' BIX_PMV_TOTAL4,
169     NVL(SUM(curr_received),0) BIX_EMC_RCVD,
170     SUM(prev_received)  BIX_PMV_TOTAL5,
171     SUM(NVL(curr_replied,0)+ NVL(curr_auto_replied,0)) BIX_EMC_REPLD,
172     SUM(NVL(prev_replied,0)+ NVL(prev_auto_replied,0)) BIX_PMV_TOTAL6,
173     NVL(SUM(curr_backlog),0) BIX_EMC_BACKLOG,
174     DECODE(SUM(prev_backlog),0,NULL,SUM(prev_backlog)) BIX_PMV_TOTAL7,
175     ROUND(NVL(SUM(curr_replied),0)/sum(CURR_LOGIN_TIME_IN_PERIOD),1) BIX_EMC_REPPERHR,
176     ROUND(SUM(prev_replied)/ sum(PREV_LOGIN_TIME_IN_PERIOD),1) BIX_PMV_TOTAL8,
177     ROUND(SUM(curr_one_done)*100,1) BIX_EMC_ONE_DONE,
178     ROUND(SUM(prev_one_done)*100,1) BIX_PMV_TOTAL9,
179     ROUND(NVL(SUM(curr_transferred),0)/ DECODE(SUM(NVL(curr_deleted,0) + NVL(curr_replied,0)),0,NULL,
180 		   SUM(NVL(curr_deleted,0) + NVL(curr_replied,0)))*100,1) BIX_EMC_TRANRATIO,
181     ROUND(SUM(prev_transferred)/ DECODE(SUM(NVL(prev_deleted,0) + NVL(prev_replied,0)),0,NULL,
182 		   SUM(NVL(prev_deleted,0) + NVL(prev_replied,0)))*100,1) BIX_PMV_TOTAL10,
183     ROUND((NVL(SUM(curr_deleted),0)+NVL(SUM(curr_auto_deleted),0))/
184            DECODE(SUM(curr_completed) ,0,NULL,SUM(curr_completed))*100,1) BIX_EMC_DELRATIO,
185     ROUND((NVL(SUM(prev_deleted),0)+NVL(SUM(prev_auto_deleted),0))/
186           DECODE(SUM(prev_completed) ,0,NULL,SUM(prev_completed))*100,1) BIX_PMV_TOTAL11,
187     NVL(SUM(curr_customer_count),0) BIX_EMC_CUST_COUNT,
188     DECODE(SUM(prev_customer_count),0,NULL,SUM(prev_customer_count)) BIX_PMV_TOTAL12,
189     NVL(SUM(curr_sr_created),0) BIX_EMC_SR,
190     SUM(prev_sr_created) BIX_PMV_TOTAL13,
191     NVL(SUM(curr_composed),0) BIX_EMC_COMPOSED,
192     SUM(prev_composed) BIX_PMV_TOTAL14,
193     NVL(SUM(curr_leads),0) BIX_EMC_LEADS,
194     SUM(prev_leads) BIX_PMV_TOTAL15
195   FROM
196   (
197      SELECT
198            NVL(SUM(DECODE(calendar.report_date,&BIS_CURRENT_ASOF_DATE,EMAILS_RPLD_BY_GOAL_IN_PERIOD)),0)/
199            SUM(DECODE(calendar.report_date,&BIS_CURRENT_ASOF_DATE,
200 		  DECODE(NVL(EMAILS_REPLIED_IN_PERIOD,0) + NVL(EMAILS_AUTO_REPLIED_IN_PERIOD,0),0,NULL,
201 		         NVL(EMAILS_REPLIED_IN_PERIOD,0) + NVL(EMAILS_AUTO_REPLIED_IN_PERIOD,0) )
202 		  )) curr_slvl,
203            NVL(SUM(DECODE(calendar.report_date,&BIS_PREVIOUS_ASOF_DATE,EMAILS_RPLD_BY_GOAL_IN_PERIOD)),0)/
204            SUM(DECODE(calendar.report_date,&BIS_PREVIOUS_ASOF_DATE,
205 		  DECODE(NVL(EMAILS_REPLIED_IN_PERIOD,0) + NVL(EMAILS_AUTO_REPLIED_IN_PERIOD,0),0,NULL,
206 		         NVL(EMAILS_REPLIED_IN_PERIOD,0) + NVL(EMAILS_AUTO_REPLIED_IN_PERIOD,0) )
207 		  )) prev_slvl,
208            NVL(SUM(DECODE(calendar.report_date,&BIS_CURRENT_ASOF_DATE,EMAIL_RESP_TIME_IN_PERIOD)),0)/
209            SUM(DECODE(calendar.report_date,&BIS_CURRENT_ASOF_DATE,
210 		  DECODE(NVL(EMAILS_REPLIED_IN_PERIOD,0) + NVL(EMAILS_AUTO_REPLIED_IN_PERIOD,0),0,NULL,
211 		                     NVL(EMAILS_REPLIED_IN_PERIOD,0) + NVL(EMAILS_AUTO_REPLIED_IN_PERIOD,0) ))) curr_avg_resp_time,
212            NVL(SUM(DECODE(calendar.report_date,&BIS_PREVIOUS_ASOF_DATE,EMAIL_RESP_TIME_IN_PERIOD)),0)/
213            SUM(DECODE(calendar.report_date,&BIS_PREVIOUS_ASOF_DATE,
214 		  DECODE(NVL(EMAILS_REPLIED_IN_PERIOD,0) + NVL(EMAILS_AUTO_REPLIED_IN_PERIOD,0),0,NULL,
215 		                     NVL(EMAILS_REPLIED_IN_PERIOD,0) + NVL(EMAILS_AUTO_REPLIED_IN_PERIOD,0) ))) prev_avg_resp_time,
216            SUM(DECODE(calendar.report_date,&BIS_CURRENT_ASOF_DATE,EMAILS_OFFERED_IN_PERIOD)) curr_received,
217            SUM(DECODE(calendar.report_date,&BIS_PREVIOUS_ASOF_DATE,EMAILS_OFFERED_IN_PERIOD)) prev_received,
218            SUM(DECODE(calendar.report_date,&BIS_CURRENT_ASOF_DATE,EMAILS_REPLIED_IN_PERIOD)) curr_replied,
219            SUM(DECODE(calendar.report_date,&BIS_PREVIOUS_ASOF_DATE,EMAILS_REPLIED_IN_PERIOD)) prev_replied,
220            SUM(DECODE(calendar.report_date,&BIS_CURRENT_ASOF_DATE,EMAILS_AUTO_REPLIED_IN_PERIOD)) curr_auto_replied,
221            SUM(DECODE(calendar.report_date,&BIS_PREVIOUS_ASOF_DATE,EMAILS_AUTO_REPLIED_IN_PERIOD)) prev_auto_replied,
222            NVL(SUM(DECODE(calendar.report_date,&BIS_CURRENT_ASOF_DATE,ONE_RSLN_IN_PERIOD)),0)/
223            SUM(DECODE(calendar.report_date,&BIS_CURRENT_ASOF_DATE,INTERACTION_THREADS_IN_PERIOD))  curr_one_done,
224            SUM(DECODE(calendar.report_date,&BIS_PREVIOUS_ASOF_DATE,ONE_RSLN_IN_PERIOD))/
225            SUM(DECODE(calendar.report_date,&BIS_PREVIOUS_ASOF_DATE,INTERACTION_THREADS_IN_PERIOD))  prev_one_done,
226            SUM(DECODE(calendar.report_date,&BIS_CURRENT_ASOF_DATE,EMAILS_RSL_AND_TRFD_IN_PERIOD)) curr_transferred,
227            SUM(DECODE(calendar.report_date,&BIS_PREVIOUS_ASOF_DATE,EMAILS_RSL_AND_TRFD_IN_PERIOD)) prev_transferred,
228            SUM(DECODE(calendar.report_date,&BIS_CURRENT_ASOF_DATE,EMAILS_DELETED_IN_PERIOD)) curr_deleted,
229            SUM(DECODE(calendar.report_date,&BIS_PREVIOUS_ASOF_DATE,EMAILS_DELETED_IN_PERIOD)) prev_deleted,
230            SUM(DECODE(calendar.report_date,&BIS_CURRENT_ASOF_DATE,EMAILS_AUTO_DELETED_IN_PERIOD)) curr_auto_deleted,
231            SUM(DECODE(calendar.report_date,&BIS_PREVIOUS_ASOF_DATE,EMAILS_AUTO_DELETED_IN_PERIOD)) prev_auto_deleted,
232            SUM(DECODE(calendar.report_date,&BIS_CURRENT_ASOF_DATE,NVL(EMAILS_REPLIED_IN_PERIOD,0) +
233                 NVL(EMAILS_DELETED_IN_PERIOD,0) + NVL(EMAILS_AUTO_UPTD_SR_IN_PERIOD,0) +
234 			 NVL(EMAILS_AUTO_REPLIED_IN_PERIOD,0) + NVL(EMAILS_AUTO_DELETED_IN_PERIOD,0) +
235 			 NVL(EMAILS_AUTO_RESOLVED_IN_PERIOD,0))) curr_completed,
236            SUM(DECODE(calendar.report_date,&BIS_PREVIOUS_ASOF_DATE,NVL(EMAILS_REPLIED_IN_PERIOD,0) +
237                 NVL(EMAILS_DELETED_IN_PERIOD,0) + NVL(EMAILS_AUTO_UPTD_SR_IN_PERIOD,0) +
238 			 NVL(EMAILS_AUTO_REPLIED_IN_PERIOD,0) + NVL(EMAILS_AUTO_DELETED_IN_PERIOD,0) +
239 			 NVL(EMAILS_AUTO_RESOLVED_IN_PERIOD,0))) prev_completed,
240            COUNT(DISTINCT(CASE WHEN calendar.report_date = &BIS_CURRENT_ASOF_DATE
241 						 AND fact.party_id <> -1
242 						 AND fact.party_id <> ' || l_dummy_cust ||
243                ' THEN fact.PARTY_ID END )) curr_customer_count,
244            COUNT(DISTINCT(CASE WHEN calendar.report_date = &BIS_PREVIOUS_ASOF_DATE
245 						 AND fact.party_id <> -1
246 						 AND fact.party_id <> ' || l_dummy_cust ||
247                ' THEN fact.PARTY_ID END )) prev_customer_count,
248            SUM(DECODE(calendar.report_date,&BIS_CURRENT_ASOF_DATE,SR_CREATED_IN_PERIOD)) curr_sr_created,
249            SUM(DECODE(calendar.report_date,&BIS_PREVIOUS_ASOF_DATE,SR_CREATED_IN_PERIOD)) prev_sr_created,
250 		 SUM(DECODE(calendar.report_date,&BIS_CURRENT_ASOF_DATE,EMAILS_COMPOSED_IN_PERIOD)) curr_composed,
251 		 SUM(DECODE(calendar.report_date,&BIS_PREVIOUS_ASOF_DATE,EMAILS_COMPOSED_IN_PERIOD)) prev_composed,
252            SUM(DECODE(calendar.report_date,&BIS_CURRENT_ASOF_DATE,LEADS_CREATED_IN_PERIOD)) curr_leads,
253            SUM(DECODE(calendar.report_date,&BIS_PREVIOUS_ASOF_DATE,LEADS_CREATED_IN_PERIOD)) prev_leads
254      FROM bix_email_details_mv fact,
255           fii_time_rpt_struct calendar
256         WHERE fact.time_id = calendar.time_id
257 	   AND   fact.row_type = :l_row_type
258         AND fact.period_type_id = calendar.period_type_id
259         AND calendar.report_date IN (&BIS_CURRENT_ASOF_DATE,&BIS_PREVIOUS_ASOF_DATE)
260         AND bitand(calendar.record_type_id,&BIS_NESTED_PATTERN ) =  calendar.record_type_id ';
261  l_sqltext := l_sqltext || l_where_clause || ' ),
262        (
263          SELECT
264          SUM(DECODE(period_start_date,:l_max_collect_date,
265                NVL(ACCUMULATED_OPEN_EMAILS,0) + NVL(ACCUMULATED_EMAILS_IN_QUEUE,0) )) curr_backlog,
266          SUM(DECODE(period_start_date,&BIS_PREVIOUS_ASOF_DATE,
267                 NVL(ACCUMULATED_OPEN_EMAILS,0) + NVL(ACCUMULATED_EMAILS_IN_QUEUE,0) )) prev_backlog
268          FROM   bix_email_details_mv
269 	    WHERE  time_id IN (TO_CHAR(:l_max_collect_date,''J''),TO_CHAR(&BIS_PREVIOUS_ASOF_DATE,''J''))
270 	    ANd    row_type = :l_row_type
271          AND   period_type_id = :l_period_type_id ';
272  l_sqltext := l_sqltext || l_where_clause || ' ),
273    (
274     SELECT
275       SUM(DECODE(calendar.report_date,&BIS_CURRENT_ASOF_DATE,LOGIN_TIME))/3600 CURR_LOGIN_TIME_IN_PERIOD,
276       SUM(DECODE(calendar.report_date,&BIS_PREVIOUS_ASOF_DATE,LOGIN_TIME))/3600 PREV_LOGIN_TIME_IN_PERIOD
277       FROM   bix_agent_session_f fact,
278           fii_time_rpt_struct calendar
279         WHERE fact.application_id = :l_application_id
280         AND fact.time_id = calendar.time_id
281         AND fact.period_type_id = calendar.period_type_id
282         AND calendar.report_date IN (&BIS_CURRENT_ASOF_DATE,&BIS_PREVIOUS_ASOF_DATE)
283        AND bitand(calendar.record_type_id,&BIS_NESTED_PATTERN) =  calendar.record_type_id
284 	  ) ';
285 
286 
287 p_sql_text := l_sqltext;
288 
289 
290 l_custom_rec.attribute_name := ':l_period_type_id' ;
291 l_custom_rec.attribute_value:= l_period_type_id;
292 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
293 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.NUMERIC_BIND;
294 
295 p_custom_output.Extend();
296 p_custom_output(p_custom_output.count) := l_custom_rec;
297 
298 l_custom_rec.attribute_name := ':l_max_collect_date' ;
299 l_custom_rec.attribute_value:= l_max_collect_date;
300 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
301 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.DATE_BIND;
302 
303 p_custom_output.Extend();
304 p_custom_output(p_custom_output.count) := l_custom_rec;
305 
306 
307 l_custom_rec.attribute_name := ':l_application_id';
308 l_custom_rec.attribute_value:= l_application_id;
309 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
310 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.NUMERIC_BIND;
311 
312 p_custom_output.EXTEND;
313 p_custom_output(p_custom_output.COUNT) := l_custom_rec;
314 
315 IF ( l_account IS NOT NULL) THEN
316 l_custom_rec.attribute_name := ':l_account' ;
317 l_custom_rec.attribute_value:= l_account;
318 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
319 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
320 
321 p_custom_output.Extend();
322 p_custom_output(p_custom_output.count) := l_custom_rec;
323 END IF;
324 
325 IF ( l_classification IS NOT NULL) THEN
326 l_custom_rec.attribute_name := ':l_classification' ;
327 l_custom_rec.attribute_value:= l_classification;
328 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
329 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
330 
331 p_custom_output.Extend();
332 p_custom_output(p_custom_output.count) := l_custom_rec;
333 END IF;
334 
335 l_custom_rec.attribute_name := ':l_row_type' ;
336 l_custom_rec.attribute_value:= l_row_type;
337 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
338 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
339 
340 p_custom_output.Extend();
341 p_custom_output(p_custom_output.count) := l_custom_rec;
342 
343 EXCEPTION
344 WHEN OTHERS THEN
345 NULL;
346 END GET_SQL;
347 END  BIX_PMV_EMC_KPI_PRTLT_PKG;