1 PACKAGE BODY BIX_PMV_EMC_ART_PRTLT_PKG AS
2 /*$Header: bixeartb.plb 120.0 2005/05/25 17:19:18 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 IF ( l_comp_type = 'YEARLY' AND l_period_type = 'FII_TIME_ENT_YEAR' )
61 THEN
62 --This is for year over year comparison and period type is year. We need to get the prior year's values
63 --for display.
64 l_sqltext
65 :='
66 /*Outermost query does the calculations of service level goal and constrains
67 display between current report start date and current year only
68 */
69 select name VIEWBY,
70 ROUND((nvl(sum(CURR_ART),0)/SUM(CURR_REPLD))/3600,1) BIX_EMC_ARTIME,
71 ROUND((nvl(sum(PREV_ART),0)/SUM(PREV_REPLD))/3600,1) BIX_EMC_PRARTIME
72 from
73 (
74 /* Outer most iview .Uses lag to select prior values for the corresponding year*/
75 select cal.name,cal.start_date,
76 SUM(
77 CASE when (cal.start_date between &BIS_CURRENT_REPORT_START_DATE and &BIS_CURRENT_ASOF_DATE
78 and cal.report_date = least(cal.end_date,&BIS_CURRENT_ASOF_DATE))
79 then
80 AGENT_RESP_TIME_IN_PERIOD
81 else
82 null
83 end
84 ) CURR_ART
85 ,lag(
86 SUM(
87 CASE WHEN (cal.start_date between &BIS_PREVIOUS_REPORT_START_DATE and &BIS_PREVIOUS_ASOF_DATE
88 and cal.report_date = least(cal.end_date,&BIS_PREVIOUS_ASOF_DATE ))
89 then
90 AGENT_RESP_TIME_IN_PERIOD
91 else
92 null
93 end
94 )
95 ) over (order by cal.start_date) PREV_ART
96 ,
97 SUM(
98 CASE when (cal.start_date between &BIS_CURRENT_REPORT_START_DATE and &BIS_CURRENT_ASOF_DATE
99 and cal.report_date = least(cal.end_date,&BIS_CURRENT_ASOF_DATE))
100 then
101 EMAILS_REPLIED_IN_PERIOD
102 else
103 null
104 end
105 ) CURR_REPLD
106 ,
107 lag(
108 SUM (
109 CASE WHEN (cal.start_date between &BIS_PREVIOUS_REPORT_START_DATE and &BIS_PREVIOUS_ASOF_DATE
110 and cal.report_date = least(cal.end_date,&BIS_PREVIOUS_ASOF_DATE ))
111 then
112 EMAILS_REPLIED_IN_PERIOD
113 else
114 null
115 end
116 )
117 ) over (order by cal.start_date) PREV_REPLD
118 from
119 (
120 /* Selects measures for all years in time range from previous report start date to current as of date*/
121 select fii1.name,fii1.start_date,fii1.end_date,cal.report_Date,cal.period_type_id,cal.time_id
122 from fii_time_ent_year fii1, fii_time_rpt_struct cal
123 where
124 fii1.start_date BETWEEN &BIS_PREVIOUS_REPORT_START_DATE AND &BIS_CURRENT_ASOF_DATE
125 and cal.report_date between fii1.start_date and fii1.end_date
126 and cal.report_date in (least(fii1.end_date, &BIS_CURRENT_ASOF_DATE) , &BIS_PREVIOUS_ASOF_DATE)
127 AND bitAND(cal.record_type_id,&BIS_NESTED_PATTERN ) = cal.record_type_id
128 order by fii1.sequence
129 )cal,(
130 select period_type_id,time_id,
131 AGENT_RESP_TIME_IN_PERIOD,
132 EMAILS_REPLIED_IN_PERIOD
133 from bix_Email_Details_mv
134 where row_type=:l_row_type '|| l_where_clause || '
135 )mv
136 where mv.period_type_id(+)=cal.period_type_id
137 and mv.time_id(+)=cal.time_id
138 group by cal.name,cal.start_date
139 ) recset /*End of outermost view */
140 WHERE recset.start_date BETWEEN &BIS_CURRENT_REPORT_START_DATE AND &BIS_CURRENT_ASOF_DATE
141 group by name';
142
143
144
145 ELSIF ( l_comp_type = 'YEARLY' AND l_period_type <> 'FII_TIME_ENT_YEAR' )
146 THEN
147 --
148 --If it enters here it means the comparison is for Week, Month or Quarter
149 --and it is a Year over Year comparison.
150 --
151 l_sqltext := '
152 SELECT fii2.name VIEWBY,
153 ROUND((nvl(sum(CURR_ART),0)/SUM(CURR_REPLD))/3600,1) BIX_EMC_ARTIME,
154 ROUND((nvl(sum(PREV_ART),0)/SUM(PREV_REPLD))/3600,1) BIX_EMC_PRARTIME
155 FROM
156 ( SELECT fii604.sequence SEQUENCE,
157 SUM( CASE when
158 (
159 fii604.start_date between &BIS_CURRENT_REPORT_START_DATE
160 and &BIS_CURRENT_ASOF_DATE
161 and cal.report_date = least(fii604.end_date,&BIS_CURRENT_ASOF_DATE)
162 )
163 then
164 AGENT_RESP_TIME_IN_PERIOD
165 else
166 NULL
167 end
168 ) CURR_ART,
169 SUM( CASE when
170 (
171 fii604.start_date between &BIS_CURRENT_REPORT_START_DATE
172 and &BIS_CURRENT_ASOF_DATE
173 and cal.report_date = least(fii604.end_date,&BIS_CURRENT_ASOF_DATE)
174 )
175 then
176 EMAILS_REPLIED_IN_PERIOD
177 else
178 NULL
179 end
180 ) CURR_REPLD,
181 SUM( CASE when
182 (
183 fii604.start_date between &BIS_PREVIOUS_REPORT_START_DATE
184 and &BIS_PREVIOUS_ASOF_DATE
185 and cal.report_date = least(fii604.end_date,&BIS_PREVIOUS_ASOF_DATE)
186 )
187 then
188 AGENT_RESP_TIME_IN_PERIOD
189 else
190 NULL
191 end
192 ) PREV_ART,
193 SUM( CASE when
194 (
195 fii604.start_date between &BIS_PREVIOUS_REPORT_START_DATE
196 and &BIS_PREVIOUS_ASOF_DATE
197 and cal.report_date = least(fii604.end_date,&BIS_PREVIOUS_ASOF_DATE)
198 )
199 then
200 EMAILS_REPLIED_IN_PERIOD
201 else
202 NULL
203 end
204 ) PREV_REPLD
205 FROM '||l_period_type||' fii604,
206 bix_email_details_mv eml,
207 fii_time_rpt_struct cal
208 WHERE eml.time_id = cal.time_id
209 AND eml.row_type = :l_row_type
210 AND bitand(cal.record_type_id,&BIS_NESTED_PATTERN)=cal.record_type_id
211 AND fii604.start_date BETWEEN &BIS_PREVIOUS_REPORT_START_DATE AND
212 &BIS_CURRENT_ASOF_DATE
213 AND cal.report_date = (CASE WHEN(
214 fii604.start_date between
215 &BIS_PREVIOUS_REPORT_START_DATE and
216 &BIS_PREVIOUS_ASOF_DATE
217 )
218 THEN
219 least(fii604.end_date, &BIS_PREVIOUS_ASOF_DATE)
220 ELSE
221 least(fii604.end_date, &BIS_CURRENT_ASOF_DATE)
222 END
223 )
224 AND cal.period_type_id = eml.period_type_id ';
225 l_sqltext := l_sqltext || l_where_clause || ' GROUP BY fii604.sequence
226 ) summ, '
227 ||l_period_type||' fii2
228 WHERE fii2.start_date BETWEEN &BIS_CURRENT_REPORT_START_DATE
229 AND &BIS_CURRENT_ASOF_DATE
230 AND fii2.sequence = summ.sequence (+)
231 GROUP BY fii2.name, fii2.start_date, summ.sequence
232 ORDER BY fii2.start_date ';
233
234 ELSE
235 --
236 --If it reaches here it means it is either a Sequential comparison for
237 --week, month or quarter OR it is a YEAR period type. For YEAR period type
238 --it does not matter whether it is a Y/Y comparison or a Sequential comparison
239 --as both will be treated the same.
240 --
241 l_sqltext := '
242 SELECT fii604.name VIEWBY,
243 ROUND((nvl(sum(CURR_ART),0)/SUM(CURR_REPLD))/3600,1) BIX_EMC_ARTIME,
244 NULL BIX_EMC_PRARTIME
245 FROM
246 ( SELECT fii604.name NAME,
247 sum(AGENT_RESP_TIME_IN_PERIOD) CURR_ART,
248 sum(EMAILS_REPLIED_IN_PERIOD) CURR_REPLD
249 FROM '||l_period_type||' fii604,
250 bix_email_details_mv eml,
251 fii_time_rpt_struct cal
252 WHERE eml.time_id = cal.time_id
253 AND eml.row_type = :l_row_type
254 AND bitand(cal.record_type_id,&BIS_NESTED_PATTERN)=cal.record_type_id
255 AND fii604.start_date BETWEEN &BIS_CURRENT_REPORT_START_DATE AND
256 &BIS_CURRENT_ASOF_DATE
257 AND cal.report_date = least(&BIS_CURRENT_ASOF_DATE,fii604.end_date)
258 AND cal.period_type_id = eml.period_type_id ';
259 l_sqltext := l_sqltext || l_where_clause || ' GROUP BY fii604.name
260 ) curr, ' ||
261 l_period_type || ' fii604
262 WHERE fii604.start_date BETWEEN &BIS_CURRENT_REPORT_START_DATE
263 AND &BIS_CURRENT_ASOF_DATE
264 AND fii604.name = curr.name (+)
265 GROUP BY fii604.name,fii604.start_date
266 ORDER BY fii604.start_date
267 ';
268
269 END IF;
270
271 p_custom_sql := l_sqltext;
272
273 l_custom_rec.attribute_name := BIS_PMV_PARAMETERS_PUB.VIEW_BY_VALUE;
274 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.View_By_Type;
275 l_custom_rec.attribute_value := 'TIME+'||l_period_type;
276
277 p_custom_output.EXTEND();
278 p_custom_output(p_custom_output.COUNT) := l_custom_rec;
279
280 IF ( l_account IS NOT NULL) THEN
281 l_custom_rec.attribute_name := ':l_account' ;
282 l_custom_rec.attribute_value:= l_account;
283 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
284 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
285
286 p_custom_output.Extend();
287 p_custom_output(p_custom_output.count) := l_custom_rec;
288 END IF;
289
290 IF ( l_classification IS NOT NULL) THEN
291 l_custom_rec.attribute_name := ':l_classification' ;
292 l_custom_rec.attribute_value:= l_classification;
293 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
294 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
295
296 p_custom_output.Extend();
297 p_custom_output(p_custom_output.count) := l_custom_rec;
298 END IF;
299
300 l_custom_rec.attribute_name := ':l_row_type' ;
301 l_custom_rec.attribute_value:= l_row_type;
302 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
303 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
304
305 p_custom_output.Extend();
306 p_custom_output(p_custom_output.count) := l_custom_rec;
307
308 EXCEPTION
309 WHEN OTHERS THEN
310 NULL;
311 END GET_SQL;
312 END BIX_PMV_EMC_ART_PRTLT_PKG;
[Home] [Help]