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