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