1 PACKAGE BODY BIX_PMV_EMC_CWT_PRTLT_PKG AS
2 /*$Header: bixecwtb.plb 120.0 2005/05/25 17:20:21 appldev noship $ */
3
4 PROCEDURE GET_SQL(p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL,
5 p_custom_sql 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_record_type_id NUMBER;
13 l_comp_type varchar2(2000);
14 l_account varchar2(32000);
15 l_sql_errm varchar2(32000);
16 l_previous_report_start_date DATE;
17 l_current_report_start_date DATE;
18 l_previous_as_of_date DATE;
19 l_period_type_id NUMBER;
20 l_time_id_column VARCHAR2(1000);
21 l_goal NUMBER;
22 l_custom_rec BIS_QUERY_ATTRIBUTES := BIS_PMV_PARAMETERS_PUB.Initialize_Query_Type;
23 l_classification VARCHAR2(32000);
24 l_where_clause VARCHAR2(32000);
25 l_view_by varchar2(1000);
26 l_row_type varchar2(10) := 'AC';
27
28 BEGIN
29
30
31
32 --
33 --Initialize p_custom_output
34 --
35 p_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
36
37 bix_pmv_dbi_utl_pkg.get_emc_page_params (p_page_parameter_tbl,
38 l_as_of_date,
39 l_period_type,
40 l_record_type_id,
41 l_comp_type,
42 l_account,
43 l_classification,
44 l_view_by
45 );
46
47
48 -- If the account is not 'All'
49
50 IF l_account IS NOT NULL THEN
51 l_where_clause := 'AND email_account_id IN (:l_account) ';
52 END IF;
53
54
55 IF l_classification IS NOT NULL THEN
56 l_where_clause := l_where_clause || ' AND email_classification_id IN (:l_classification) ';
57 END IF;
58
59
60
61 IF (FND_PROFILE.DEFINED('BIX_EMAIL_GOAL')) THEN
62 begin
63 l_goal := TO_NUMBER(FND_PROFILE.VALUE('BIX_EMAIL_GOAL'));
64 exception
65 when others then
66 l_goal:=0;
67 end;
68 ELSE
69 l_goal := 0;
70 END IF;
71
72
73
74
75
76 IF ( l_comp_type = 'YEARLY' AND l_period_type = 'FII_TIME_ENT_YEAR' )
77 THEN
78 --This is for year over year comparison and period type is year. We need to get the prior year's values
79 --for display.
80 l_sqltext
81 :='
82 /*Outermost query does the calculations of service level goal and constrains
83 display between current report start date and current year only
84 */
85 select name VIEWBY,
86 ROUND((nvl(sum(CURR_ERT),0)/DECODE(SUM(CURR_REPLD),0,NULL,SUM(CURR_REPLD)))/3600,1) BIX_EMC_CRTIME,
87 ROUND((nvl(sum(PREV_ERT),0)/DECODE(SUM(PREV_REPLD),0,NULL,SUM(PREV_REPLD)))/3600,1) BIX_EMC_PRCRTIME,
88 ' || l_goal ||' BIX_EMC_GOAL
89 from
90 (
91 /* Outer most iview .Uses lag to select prior values for the corresponding year*/
92 select cal.name,cal.start_date,
93 SUM(
94 CASE when (cal.start_date between &BIS_CURRENT_REPORT_START_DATE and &BIS_CURRENT_ASOF_DATE
95 and cal.report_date = least(cal.end_date,&BIS_CURRENT_ASOF_DATE))
96 then
97 EMAIL_RESP_TIME_IN_PERIOD
98 else
99 null
100 end
101 ) CURR_ERT
102 ,lag(
103 SUM(
104 CASE WHEN (cal.start_date between &BIS_PREVIOUS_REPORT_START_DATE and &BIS_PREVIOUS_ASOF_DATE
105 and cal.report_date = least(cal.end_date,&BIS_PREVIOUS_ASOF_DATE ))
106 then
107 EMAIL_RESP_TIME_IN_PERIOD
108 else
109 null
110 end
111 )
112 ) over (order by cal.start_date) PREV_ERT
113 ,
114 SUM(
115 CASE when (cal.start_date between &BIS_CURRENT_REPORT_START_DATE and &BIS_CURRENT_ASOF_DATE
116 and cal.report_date = least(cal.end_date,&BIS_CURRENT_ASOF_DATE))
117 then
118 NVL(EMAILS_REPLIED_IN_PERIOD,0)+NVL(EMAILS_AUTO_REPLIED_IN_PERIOD,0)
119 else
120 null
121 end
122 ) CURR_REPLD
123 ,
124 lag(
125 SUM (
126 CASE WHEN (cal.start_date between &BIS_PREVIOUS_REPORT_START_DATE and &BIS_PREVIOUS_ASOF_DATE
127 and cal.report_date = least(cal.end_date,&BIS_PREVIOUS_ASOF_DATE ))
128 then
129 NVL(EMAILS_REPLIED_IN_PERIOD,0)+NVL(EMAILS_AUTO_REPLIED_IN_PERIOD,0)
130 else
131 null
132 end
133 )
134 ) over (order by cal.start_date) PREV_REPLD
135 from
136 (
137 /* Selects measures for all years in time range from previous report start date to current as of date*/
138 select fii604.name,fii604.start_date,fii604.end_date,cal.report_Date,cal.period_type_id,cal.time_id
139 from fii_time_ent_year fii604, fii_time_rpt_struct cal
140 where
141 fii604.start_date BETWEEN &BIS_PREVIOUS_REPORT_START_DATE AND &BIS_CURRENT_ASOF_DATE
142 and cal.report_date between fii604.start_date and fii604.end_date
143 and cal.report_date in (least(fii604.end_date, &BIS_CURRENT_ASOF_DATE) , &BIS_PREVIOUS_ASOF_DATE)
144 AND bitAND(cal.record_type_id,&BIS_NESTED_PATTERN ) = cal.record_type_id
145 order by fii604.sequence
146 )cal,(
147 select period_type_id,time_id,
148 EMAIL_RESP_TIME_IN_PERIOD,
149 EMAILS_REPLIED_IN_PERIOD,
150 EMAILS_AUTO_REPLIED_IN_PERIOD
151 from bix_Email_Details_mv
152 where row_type=:l_row_type '|| l_where_clause || '
153 )mv
154 where mv.period_type_id(+)=cal.period_type_id
155 and mv.time_id(+)=cal.time_id
156 group by cal.name,cal.start_date
157 ) recset /*End of outermost view */
158 WHERE recset.start_date BETWEEN &BIS_CURRENT_REPORT_START_DATE AND &BIS_CURRENT_ASOF_DATE
159 group by name';
160
161
162
163 ELSIF ( l_comp_type = 'YEARLY' AND l_period_type <> 'FII_TIME_ENT_YEAR' )
164 THEN
165 --
166 --If it enters here it means the comparison is for Week, Month or Quarter
167 --and it is a Year over Year comparison.
168 --
169 l_sqltext := '
170 SELECT fii2.name VIEWBY,
171 ROUND((nvl(sum(CURR_ERT),0)/DECODE(SUM(CURR_REPLD),0,NULL,SUM(CURR_REPLD)))/3600,1) BIX_EMC_CRTIME,
172 ROUND((nvl(sum(PREV_ERT),0)/DECODE(SUM(PREV_REPLD),0,NULL,SUM(PREV_REPLD)))/3600,1) BIX_EMC_PRCRTIME,
173 ' || l_goal ||' BIX_EMC_GOAL
174 FROM
175 ( SELECT fiitb.sequence SEQUENCE,
176 SUM( CASE when
177 (
178 fiitb.start_date between &BIS_CURRENT_REPORT_START_DATE
179 and &BIS_CURRENT_ASOF_DATE
180 and cal.report_date = least(fiitb.end_date,&BIS_CURRENT_ASOF_DATE)
181 )
182 then
183 EMAIL_RESP_TIME_IN_PERIOD
184 else
185 NULL
186 end
187 ) CURR_ERT,
188 SUM( CASE when
189 (
190 fiitb.start_date between &BIS_CURRENT_REPORT_START_DATE
191 and &BIS_CURRENT_ASOF_DATE
192 and cal.report_date = least(fiitb.end_date,&BIS_CURRENT_ASOF_DATE)
193 )
194 then
195 NVL(EMAILS_REPLIED_IN_PERIOD,0)+NVL(EMAILS_AUTO_REPLIED_IN_PERIOD,0)
196 else
197 NULL
198 end
199 ) CURR_REPLD,
200 SUM( CASE when
201 (
202 fiitb.start_date between &BIS_PREVIOUS_REPORT_START_DATE
203 and &BIS_PREVIOUS_ASOF_DATE
204 and cal.report_date = least(fiitb.end_date,&BIS_PREVIOUS_ASOF_DATE)
205 )
206 then
207 EMAIL_RESP_TIME_IN_PERIOD
208 else
209 NULL
210 end
211 ) PREV_ERT,
212 SUM( CASE when
213 (
214 fiitb.start_date between &BIS_PREVIOUS_REPORT_START_DATE
215 and &BIS_PREVIOUS_ASOF_DATE
216 and cal.report_date = least(fiitb.end_date,&BIS_PREVIOUS_ASOF_DATE)
217 )
218 then
219 NVL(EMAILS_REPLIED_IN_PERIOD,0)+NVL(EMAILS_AUTO_REPLIED_IN_PERIOD,0)
220 else
221 NULL
222 end
223 ) PREV_REPLD
224 FROM '||l_period_type||' fiitb,
225 bix_email_details_mv eml,
226 fii_time_rpt_struct cal
227 WHERE eml.time_id = cal.time_id
228 ANd eml.row_type = :l_row_type
229 AND bitand(cal.record_type_id,&BIS_NESTED_PATTERN)=cal.record_type_id
230 AND fiitb.start_date BETWEEN &BIS_PREVIOUS_REPORT_START_DATE AND
231 &BIS_CURRENT_ASOF_DATE
232 AND cal.report_date = (CASE WHEN(
233 fiitb.start_date between
234 &BIS_PREVIOUS_REPORT_START_DATE and
235 &BIS_PREVIOUS_ASOF_DATE
236 )
237 THEN
238 least(fiitb.end_date, &BIS_PREVIOUS_ASOF_DATE)
239 ELSE
240 least(fiitb.end_date, &BIS_CURRENT_ASOF_DATE)
241 END
242 )
243 AND cal.period_type_id = eml.period_type_id ';
244 l_sqltext := l_sqltext || l_where_clause || ' GROUP BY fiitb.sequence
245 ) summ, '
246 ||l_period_type||' fii2
247 WHERE fii2.start_date BETWEEN &BIS_CURRENT_REPORT_START_DATE
248 AND &BIS_CURRENT_ASOF_DATE
249 AND fii2.sequence = summ.sequence (+)
250 GROUP BY fii2.name, fii2.start_date, summ.sequence
251 ORDER BY fii2.start_date ';
252
253 ELSE
254 --
255 --If it reaches here it means it is either a Sequential comparison for
256 --week, month or quarter OR it is a YEAR period type. For YEAR period type
257 --it does not matter whether it is a Y/Y comparison or a Sequential comparison
258 --as both will be treated the same.
259 --
260 l_sqltext := '
261 SELECT fii2.name VIEWBY,
262 ROUND((nvl(sum(CURR_ERT),0)/DECODE(SUM(CURR_REPLD),0,NULL,SUM(CURR_REPLD)))/3600,1) BIX_EMC_CRTIME,
263 NULL BIX_EMC_PRCRTIME,
264 ' || l_goal ||' BIX_EMC_GOAL
265 FROM
266 ( SELECT fiitb.name NAME,
267 sum(EMAIL_RESP_TIME_IN_PERIOD) CURR_ERT,
268 NVL(sum(EMAILS_REPLIED_IN_PERIOD),0) + NVL(SUM(EMAILS_AUTO_REPLIED_IN_PERIOD),0) CURR_REPLD
269 FROM '||l_period_type||' fiitb,
270 bix_email_details_mv eml,
271 fii_time_rpt_struct cal
272 WHERE eml.time_id = cal.time_id
273 AND eml.row_type = :l_row_type
274 AND bitand(cal.record_type_id,&BIS_NESTED_PATTERN)=cal.record_type_id
275 AND fiitb.start_date BETWEEN &BIS_CURRENT_REPORT_START_DATE AND
276 &BIS_CURRENT_ASOF_DATE
277 AND cal.report_date = least(&BIS_CURRENT_ASOF_DATE,fiitb.end_date)
278 AND cal.period_type_id = eml.period_type_id ';
279 l_sqltext := l_sqltext || l_where_clause || ' GROUP BY fiitb.name
280 ) curr, ' ||
281 l_period_type || ' fii2
282 WHERE fii2.start_date BETWEEN &BIS_CURRENT_REPORT_START_DATE
283 AND &BIS_CURRENT_ASOF_DATE
284 AND fii2.name = curr.name (+)
285 GROUP BY fii2.name,fii2.start_date
286 ORDER BY fii2.start_date
287 ';
288
289 END IF;
290
291 p_custom_sql := l_sqltext;
292
293 l_custom_rec.attribute_name := BIS_PMV_PARAMETERS_PUB.VIEW_BY_VALUE;
294 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.View_By_Type;
295 l_custom_rec.attribute_value := 'TIME+'||l_period_type;
296
297 p_custom_output.EXTEND();
298 p_custom_output(p_custom_output.COUNT) := l_custom_rec;
299
300 IF ( l_account IS NOT NULL) THEN
301 l_custom_rec.attribute_name := ':l_account' ;
302 l_custom_rec.attribute_value:= l_account;
303 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
304 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
305
306 p_custom_output.Extend();
307 p_custom_output(p_custom_output.count) := l_custom_rec;
308 END IF;
309
310 IF ( l_classification IS NOT NULL) THEN
311 l_custom_rec.attribute_name := ':l_classification' ;
312 l_custom_rec.attribute_value:= l_classification;
313 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
314 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
315
316 p_custom_output.Extend();
317 p_custom_output(p_custom_output.count) := l_custom_rec;
318 END IF;
319
320 l_custom_rec.attribute_name := ':l_row_type' ;
321 l_custom_rec.attribute_value:= l_row_type;
322 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
323 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
324
325 p_custom_output.Extend();
326 p_custom_output(p_custom_output.count) := l_custom_rec;
327
328 EXCEPTION
329 WHEN OTHERS THEN
330 NULL;
331 END GET_SQL;
332 END BIX_PMV_EMC_CWT_PRTLT_PKG;
[Home] [Help]