[Home] [Help]
PACKAGE BODY: APPS.IBE_BI_ORD_GRAPH_PVT
Source
1 PACKAGE BODY IBE_BI_ORD_GRAPH_PVT AS
2 /* $Header: IBEVBIORDGRAB.pls 120.4 2005/10/17 20:52:36 narao ship $ */
3 PROCEDURE GET_ORDER_GRAPH_SQL(
4 p_pmv_parameters IN BIS_PMV_PAGE_PARAMETER_tbl,
5 x_custom_sql OUT NOCOPY VARCHAR2,
6 x_custom_output OUT NOCOPY bis_query_attributes_TBL )
7 IS
8 l_custom_sql VARCHAR2(4000);
9 l_parameter_name VARCHAR2(3200);
10 l_asof_date DATE;
11 l_prev_date DATE;
12 l_timespan NUMBER;
13 l_sequence NUMBER;
14 l_cur_start DATE;
15 l_mid_start DATE;
16 l_prev_start DATE;
17 l_pprev_start DATE;
18 l_pcur_start DATE;
19 l_cur_year NUMBER;
20 l_prev_year NUMBER;
21 l_record_type_id NUMBER;
22 l_period_type VARCHAR2(3200);
23 l_comparison_type VARCHAR2(3200);
24 l_currency_code VARCHAR2(3200);
25 l_minisite VARCHAR2(3200);
26 l_minisite_id VARCHAR2(3200);
27 l_custom_rec BIS_QUERY_ATTRIBUTES;
28 l_timetable VARCHAR2(1000);
29 l_whereclause VARCHAR2(3200);
30 l_msiteFilter VARCHAR2(1000);
31 l_tableList VARCHAR2(1000);
32 l_allSelect VARCHAR2(1000);
33 l_allWhere VARCHAR2(1000);
34 dbg_msg VARCHAR2(3200);
35 l_g_p VARCHAR2(15);
36 l_g_s VARCHAR2(15) ;
37
38 /**************************************************************************/
39 /* These two variables are used to eliminate the Records corresponding to */
40 /* those stores that the user does not have access to */
41 /**************************************************************************/
42 BEGIN
43
44 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
45 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,'ibe.plsql.dbi.ibe_bi_ord_graph_pvt.get_order_graph_sql.begin','BEGIN');
46 END IF;
47 -- initilization of variables
48
49 l_custom_rec := BIS_PMV_PARAMETERS_PUB.Initialize_Query_Type;
50 l_g_p := '''FII_GLOBAL1''';
51 l_g_s := '''FII_GLOBAL2''';
52
53 FOR i IN p_pmv_parameters.FIRST..p_pmv_parameters.LAST
54 LOOP
55 l_parameter_name := p_pmv_parameters(i).parameter_name ;
56
57 IF( l_parameter_name = 'AS_OF_DATE')
58 THEN
59 l_asof_date :=
60 TO_DATE(p_pmv_parameters(i).parameter_value,'DD/MM/YYYY');
61 ELSIF( l_parameter_name = 'IBW_WEB_ANALYTICS_GROUP1+FII_CURRENCIES')
62 THEN
63 l_currency_code := p_pmv_parameters(i).parameter_id;
64 ELSIF( l_parameter_name = 'PERIOD_TYPE')
65 THEN
66 l_period_type := p_pmv_parameters(i).parameter_value;
67 ELSIF( l_parameter_name = 'TIME_COMPARISON_TYPE')
68 THEN
69 l_comparison_type := p_pmv_parameters(i).parameter_value;
70 ELSIF( l_parameter_name = 'SITE+SITE')
71 THEN
72 l_minisite := p_pmv_parameters(i).parameter_value;
73 l_minisite_id := p_pmv_parameters(i).parameter_id;
74 END IF;
75 END LOOP;
76
77 --Change in View done for Bug#:4654974. Issue#12 by narao
78 --The Site Dimension now refers to IBW_BI_MSITE_DIMN_V
79 --And hence the MSITES View is changed from IBE_BI_MSITES_V to IBW_BI_MSITE_DIMN_V.
80
81 IF (trim(l_minisite_id) is null) then
82 l_tableList := ' IBE_BI_CART_ORD_MV FACT,'||
83 --' IBE_BI_MSITES_V MSITES';
84 ' IBW_BI_MSITE_DIMN_V MSITES';
85
86 l_msiteFilter :=' AND FACT.MINISITE_ID = MSITES.ID(+)';
87
88 l_allSelect := ' MINISITE_ID,ID,';
89 l_allWhere := ' AND nvl(decode(MINISITE_ID,NULL,NULL,nvl(ID,-999)),0) <> -999 ';
90
91 ELSE
92 l_tableList := ' IBE_BI_CART_ORD_MV FACT';
93
94 l_msiteFilter := ' AND FACT.MINISITE_ID in (&SITE+SITE) ';
95
96 END IF;
97
98 IF(l_period_type = 'FII_TIME_ENT_YEAR') THEN
99 l_comparison_type := 'SEQUENTIAL';
100 l_record_type_id := 119;
101 l_timetable := 'FII_TIME_ENT_YEAR';
102 l_whereclause := ' AND TIME_PERIOD.SEQUENCE BETWEEN :l_sequence-3 AND :l_sequence';
103
104 IBE_BI_PMV_UTIL_PVT.ENT_YR_SPAN(p_asof_date => l_asof_date,
105 x_timespan => l_timespan,
106 x_sequence => l_sequence);
107
108 ELSIF (l_period_type = 'FII_TIME_ENT_QTR') THEN
109 l_record_type_id := 55;
110 l_timetable := 'FII_TIME_ENT_QTR';
111 l_whereclause:= ' AND TIME_PERIOD.start_date between :l_prev_start and :l_cur_start'||
112 ' AND TIME_PERIOD.ent_year_id BETWEEN :l_prev_year AND :l_cur_year ';
113
114 IBE_BI_PMV_UTIL_PVT.ENT_QTR_SPAN(
115 p_asof_date => l_asof_date,
116 p_comparator=> l_comparison_type,
117 x_cur_start => l_cur_start,
118 x_mid_start => l_mid_start,
119 x_prev_start=> l_prev_start,
120 x_cur_year => l_cur_year,
121 x_prev_year => l_prev_year,
122 x_timespan => l_timespan);
123
124 ELSIF (l_period_type = 'FII_TIME_ENT_PERIOD') THEN
125 l_record_type_id := 23;
126 l_timetable := 'FII_TIME_ENT_PERIOD';
127 l_whereclause:= ' AND TIME_PERIOD.start_date between :l_prev_start and :l_cur_start ';
128
129 IBE_BI_PMV_UTIL_PVT.ENT_PRD_SPAN(
130 p_asof_date => l_asof_date,
131 p_comparator=> l_comparison_type,
132 x_cur_start => l_cur_start,
133 x_mid_start => l_mid_start,
134 x_prev_start=> l_prev_start,
135 x_cur_year => l_cur_year,
136 x_prev_year => l_prev_year,
137 x_timespan => l_timespan);
138
139 IF(l_comparison_type = 'SEQUENTIAL') THEN
140 l_prev_start := l_mid_start + 1;
141 END IF;
142
143 ELSIF (l_period_type = 'FII_TIME_WEEK') THEN
144 l_record_type_id := 11;
145 l_timetable := 'FII_TIME_WEEK';
146 l_whereclause:= ' AND TIME_PERIOD.start_date between :l_prev_start and :l_cur_start ';
147
148 IBE_BI_PMV_UTIL_PVT.WEEK_SPAN(
149 p_asof_date => l_asof_date,
150 p_comparator => l_comparison_type,
151 x_cur_start => l_cur_start,
152 x_prev_start => l_prev_start,
153 x_pcur_start => l_pcur_start,
154 x_pprev_start => l_pprev_start,
155 x_timespan => l_timespan);
156 l_mid_start := l_prev_start;
157
158 END IF;
159
160 dbg_msg := 'AS_OF_DATE:'||l_asof_date||','||'CURR_CODE:'||l_currency_code||
161 ','||'PERIOD_TYPE:'||l_period_type||','||'COMPARISION_TYPE:'||
162 l_comparison_type||','||'MINISITE:'||l_minisite||','||'MINISITE_ID:'||
163 l_minisite_id||','||'RECORD_TYPE_ID:'||l_record_type_id||','||'TIME_TABLE:'||
164 l_timetable||','||'CURR_START_DATE:'||l_cur_start||','||'PREV_START_DATE:'||
165 l_prev_start||','||'MID_START_DATE:'||l_mid_start||','||'CURR_YEAR:'||
166 l_cur_year||','||'PREV_YEAR:'||l_prev_year||','||'TIME_SPAN:'||l_timespan;
167
168 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
169 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,'ibe.plsql.dbi.ibe_bi_ord_graph_pvt.get_order_graph_sql.parameters',dbg_msg);
170 END IF;
171
172 IF(l_comparison_type = 'SEQUENTIAL') THEN
173
174 /*************************************************************************/
175 /* The following Query would work for all the Cases where Comparision */
176 /* type is sequential. Depending upon the Period Type selected the */
177 /* Value of TIME_PERIOD and the whereclause would change */
178 /*************************************************************************/
179
180 /*************************************************************************/
181 /* VIEWBY : Period Name */
182 /* IBE_ATTR1 : Period Name */
183 /* IBE_VAL1 : Previous Total */
184 /* IBE_VAL2 : Current Total */
185 /* IBE_VAL3 : Previous Assisted */
186 /* IBE_VAL4 : Current Assisted */
187 /* IBE_VAL5 : Previous Unassisted */
188 /* IBE_VAL6 : Current Unassisted */
189 /*************************************************************************/
190
191
192 l_custom_sql :='SELECT FACT.NAME VIEWBY,FACT.NAME IBE_ATTR1,'||
193 ' SUM(NVL(AMOUNT_TOTAL,0)) IBE_VAL2,'||
194 ' SUM(NVL(AMOUNT_ASSIST,0)) IBE_VAL4,'||
195 ' SUM(NVL(AMOUNT_UNASSIST,0)) IBE_VAL6,'||
196 ' NULL IBE_VAL1,'||
197 ' NULL IBE_VAL3,'||
198 ' NULL IBE_VAL5'||
199 ' FROM ('||
200 ' SELECT TIME.NAME,'||l_allSelect ||
201 ' NVL(MEASURE_TYPE,''NULL'') MEASURE_TYPE,'||
202 ' decode(MEASURE_TYPE,''IBE_ORD_TOTAL'','||
203 ' decode(:l_currency_code,:l_g_p,AMOUNT_G,:l_g_s,AMOUNT_G1,CURRENCY_CD_F,AMOUNT_F),0) AMOUNT_TOTAL,'||
204 ' decode(MEASURE_TYPE,''IBE_ORD_ASSISTED'','||
205 ' decode(:l_currency_code,:l_g_p,AMOUNT_G,:l_g_s,AMOUNT_G1,CURRENCY_CD_F,AMOUNT_F),0) AMOUNT_ASSIST,'||
206 ' decode(MEASURE_TYPE,''IBE_ORD_UNASSISTED'','||
207 ' decode(:l_currency_code,:l_g_p,AMOUNT_G,:l_g_s,AMOUNT_G1,CURRENCY_CD_F,AMOUNT_F),0) AMOUNT_UNASSIST,'||
208 ' REPORT_DATE'||
209 ' FROM'||
210 ' (SELECT' ||
211 ' TIME_PERIOD.NAME,'||
212 ' CAL.REPORT_DATE,'||
213 ' CAL.PERIOD_TYPE_ID,'||
214 ' CAL.TIME_ID'||
215 ' FROM'||
216 ' FII_TIME_RPT_STRUCT_V CAL,'||l_timetable||' TIME_PERIOD'||
217 ' WHERE BITAND(CAL.RECORD_TYPE_ID, :l_record_type_id) = CAL.RECORD_TYPE_ID'||
218 ' AND CAL.Report_Date = LEAST(TIME_PERIOD.END_date,&BIS_CURRENT_ASOF_DATE) ' ||l_whereclause|| ') TIME,'||
219 l_tableList||
220 ' WHERE'||
221 ' FACT.TIME_ID (+)= TIME.TIME_ID'||
222 ' AND FACT.PERIOD_TYPE_ID(+)= TIME.PERIOD_TYPE_ID'||
223 l_msiteFilter||') FACT'||
224 ' WHERE FACT.MEASURE_TYPE IN (''IBE_ORD_TOTAL'',''IBE_ORD_ASSISTED'',''IBE_ORD_UNASSISTED'',''NULL'')'|| l_allWhere ||
225 ' GROUP BY FACT.NAME, REPORT_DATE ORDER BY REPORT_DATE';
226
227
228 ELSE
229
230 if l_period_type = 'FII_TIME_WEEK' THEN
231
232 /*************************************************************************/
233 /* The following Query would work when the Time Period is WEEK and the */
234 /* Comparison type is Year-To-Year. */
235 /*************************************************************************/
236
237 /*************************************************************************/
238 /* VIEWBY : Period Name */
239 /* IBE_ATTR1 : Period Name */
240 /* IBE_VAL1 : Previous Total */
241 /* IBE_VAL2 : Current Total */
242 /* IBE_VAL3 : Previous Assisted */
243 /* IBE_VAL4 : Current Assisted */
244 /* IBE_VAL5 : Previous Unassisted */
245 /* IBE_VAL6 : Current Unassisted */
246 /*************************************************************************/
247
248 l_custom_sql := 'SELECT MAX(NAME) VIEWBY,MAX(NAME) IBE_ATTR1,'||
249 ' SUM(NVL(CT_AMOUNT,0)) IBE_VAL2,'||
250 ' SUM(NVL(CA_AMOUNT,0)) IBE_VAL4,'||
251 ' SUM(NVL(CU_AMOUNT,0)) IBE_VAL6,'||
252 ' SUM(NVL(PT_AMOUNT,0)) IBE_VAL1,'||
253 ' SUM(NVL(PA_AMOUNT,0)) IBE_VAL3,'||
254 ' SUM(NVL(PU_AMOUNT,0)) IBE_VAL5'||
255 ' FROM ('||
256 ' SELECT'|| l_allSelect ||
257 ' (CASE WHEN fact.start_date < :l_mid_start'||
258 ' THEN null else fact.name end) NAME,'||
259 ' (CASE WHEN fact.start_date < :l_mid_start'||
260 ' THEN fact.year445_id+1 else fact.year445_id end) ENT_YEAR_ID,'||
261 ' (CASE WHEN fact.start_date < :l_mid_start'||
262 ' THEN FACT.AMOUNT_TOTAL else 0 end) PT_AMOUNT,'||
263 ' (CASE WHEN fact.start_date >= :l_mid_start'||
264 ' THEN FACT.AMOUNT_TOTAL else 0 end) CT_AMOUNT,'||
265 ' (CASE WHEN fact.start_date < :l_mid_start'||
266 ' THEN FACT.AMOUNT_ASSISTED else 0 end) PA_AMOUNT,'||
267 ' (CASE WHEN fact.start_date >= :l_mid_start'||
268 ' THEN FACT.AMOUNT_ASSISTED else 0 end) CA_AMOUNT,'||
269 ' (CASE WHEN fact.start_date < :l_mid_start'||
270 ' THEN FACT.AMOUNT_UNASSISTED else 0 end) PU_AMOUNT,'||
271 ' (CASE WHEN fact.start_date >= :l_mid_start'||
272 ' THEN FACT.AMOUNT_UNASSISTED else 0 end) CU_AMOUNT,'||
273 ' SEQUENCE'||
274 ' FROM ('||
275 ' SELECT'|| l_allSelect ||
276 ' NVL(MEASURE_TYPE,''NULL'') MEASURE_TYPE,'||
277 ' decode(MEASURE_TYPE,''IBE_ORD_TOTAL'','||
278 ' decode(:l_currency_code,:l_g_p,AMOUNT_G,:l_g_s,AMOUNT_G1,CURRENCY_CD_F,AMOUNT_F),0) AMOUNT_TOTAL,'||
279 ' decode(MEASURE_TYPE,''IBE_ORD_ASSISTED'','||
280 ' decode(:l_currency_code,:l_g_p,AMOUNT_G,:l_g_s,AMOUNT_G1,CURRENCY_CD_F,AMOUNT_F),0) AMOUNT_ASSISTED,'||
281 ' decode(MEASURE_TYPE,''IBE_ORD_UNASSISTED'','||
282 ' decode(:l_currency_code,:l_g_p,AMOUNT_G,:l_g_s,AMOUNT_G1,CURRENCY_CD_F,AMOUNT_F),0) AMOUNT_UNASSISTED,'||
283 ' TIME.PERIOD_TYPE_ID,'||
284 ' TIME.TIME_ID,TIME.SEQUENCE,TIME.START_DATE,'||
285 ' TIME.YEAR445_ID,TIME.NAME'||
286 ' FROM'||
287 ' ( SELECT ' ||
288 ' TIME_PERIOD.NAME,'||
289 ' CAL.REPORT_DATE,'||
290 ' CAL.PERIOD_TYPE_ID,'||
291 ' CAL.TIME_ID, '||
292 ' TIME_PERIOD.SEQUENCE,'||
293 ' TIME_PERIOD.START_DATE,'||
294 ' TIME.year445_id'||
295 ' FROM '||
296 ' FII_TIME_RPT_STRUCT_V CAL,'||
297 ' FII_TIME_WEEK TIME_PERIOD,'||
298 ' FII_TIME_p445 TIME'||
299 ' WHERE '||
300 ' BITAND(CAL.RECORD_TYPE_ID, :l_record_type_id) = CAL.RECORD_TYPE_ID'||
301 ' AND CAL.Report_Date = LEAST(TIME_PERIOD.END_date,&BIS_CURRENT_ASOF_DATE)'||
302 ' AND (TIME_PERIOD.start_date between :l_pprev_start and :l_pcur_start OR'||
303 ' TIME_PERIOD.start_date between :l_prev_start and :l_cur_start)'||
304 ' AND TIME.period445_id = TIME_PERIOD.period445_id ) TIME,'||
305 l_tableList||
306 ' WHERE '||
307 ' FACT.TIME_ID (+)= TIME.TIME_ID'||
308 ' AND FACT.PERIOD_TYPE_ID(+)= TIME.PERIOD_TYPE_ID'||
309 l_msiteFilter||' ) FACT'||
310 ' WHERE'||
314
311 ' FACT.MEASURE_TYPE IN (''IBE_ORD_TOTAL'',''IBE_ORD_ASSISTED'',''IBE_ORD_UNASSISTED'',''NULL'')'|| l_allWhere ||
312 ' ) GROUP BY ENT_YEAR_ID,SEQUENCE'||
313 ' ORDER BY ENT_YEAR_ID,SEQUENCE';
315 ELSE
316
317 /*************************************************************************/
318 /* The following Query would work when the Time Period is anything other */
319 /* than WEEK and the Comparison type is Year-To-Year. */
320 /*************************************************************************/
321
322 /*************************************************************************/
323 /* VIEWBY : Period Name */
324 /* IBE_ATTR1 : Period Name */
325 /* IBE_VAL1 : Previous Total */
326 /* IBE_VAL2 : Current Total */
327 /* IBE_VAL3 : Previous Assisted */
328 /* IBE_VAL4 : Current Assisted */
329 /* IBE_VAL5 : Previous Unassisted */
330 /* IBE_VAL6 : Current Unassisted */
331 /*************************************************************************/
332
333 l_custom_sql :='SELECT MAX(NAME) VIEWBY,MAX(NAME) IBE_ATTR1,'||
334 ' SUM(NVL(CT_AMOUNT,0)) IBE_VAL2,'||
335 ' SUM(NVL(CA_AMOUNT,0)) IBE_VAL4,'||
336 ' SUM(NVL(CU_AMOUNT,0)) IBE_VAL6,'||
337 ' SUM(NVL(PT_AMOUNT,0)) IBE_VAL1,'||
338 ' SUM(NVL(PA_AMOUNT,0)) IBE_VAL3,'||
339 ' SUM(NVL(PU_AMOUNT,0)) IBE_VAL5'||
340 ' FROM ('||
341 ' SELECT'||l_allSelect ||
342 ' (CASE WHEN fact.start_date <= :l_mid_start'||
343 ' THEN null else fact.name end) NAME,'||
344 ' (CASE WHEN fact.start_date <= :l_mid_start'||
345 ' THEN fact.ent_year_id+1 else fact.ent_year_id end) ENT_YEAR_ID,'||
346 ' (CASE WHEN fact.start_date <= :l_mid_start'||
347 ' THEN FACT.AMOUNT_TOTAL else 0 end) PT_AMOUNT,'||
348 ' (CASE WHEN fact.start_date > :l_mid_start'||
349 ' THEN FACT.AMOUNT_TOTAL else 0 end) CT_AMOUNT,'||
350 ' (CASE WHEN fact.start_date <= :l_mid_start'||
351 ' THEN FACT.AMOUNT_ASSISTED else 0 end) PA_AMOUNT,'||
352 ' (CASE WHEN fact.start_date > :l_mid_start'||
353 ' THEN FACT.AMOUNT_ASSISTED else 0 end) CA_AMOUNT,'||
354 ' (CASE WHEN fact.start_date <= :l_mid_start'||
355 ' THEN FACT.AMOUNT_UNASSISTED else 0 end) PU_AMOUNT,'||
356 ' (CASE WHEN fact.start_date > :l_mid_start'||
357 ' THEN FACT.AMOUNT_UNASSISTED else 0 end) CU_AMOUNT,'||
358 ' SEQUENCE'||
359 ' FROM ('||
360 ' SELECT'|| l_allSelect ||
361 ' NVL(MEASURE_TYPE,''NULL'') MEASURE_TYPE,'||
362 ' decode(MEASURE_TYPE,''IBE_ORD_TOTAL'','||
363 ' decode(:l_currency_code,:l_g_p,AMOUNT_G,:l_g_s,AMOUNT_G1,CURRENCY_CD_F,AMOUNT_F),0) AMOUNT_TOTAL,'||
364 ' decode(MEASURE_TYPE,''IBE_ORD_ASSISTED'','||
365 ' decode(:l_currency_code,:l_g_p,AMOUNT_G,:l_g_s,AMOUNT_G1,CURRENCY_CD_F,AMOUNT_F),0) AMOUNT_ASSISTED,'||
366 ' decode(MEASURE_TYPE,''IBE_ORD_UNASSISTED'','||
367 ' decode(:l_currency_code,:l_g_p,AMOUNT_G,:l_g_s,AMOUNT_G1,CURRENCY_CD_F,AMOUNT_F),0) AMOUNT_UNASSISTED,'||
368 ' TIME.PERIOD_TYPE_ID,'||
369 ' TIME.TIME_ID,TIME.SEQUENCE,TIME.START_DATE,'||
370 ' TIME.ENT_YEAR_ID,TIME.NAME'||
371 ' FROM'||
372 ' (SELECT' ||
373 ' TIME_PERIOD.NAME,'||
374 ' CAL.REPORT_DATE,'||
375 ' CAL.PERIOD_TYPE_ID,'||
376 ' CAL.TIME_ID,'||
377 ' TIME_PERIOD.SEQUENCE,'||
378 ' TIME_PERIOD.START_DATE,'||
379 ' TIME_PERIOD.ENT_YEAR_ID'||
380 ' FROM'||
381 ' FII_TIME_RPT_STRUCT_V CAL,'||l_timetable||' TIME_PERIOD'||
382 ' WHERE BITAND(CAL.RECORD_TYPE_ID, :l_record_type_id) = CAL.RECORD_TYPE_ID'||
383 ' AND BITAND(CAL.RECORD_TYPE_ID, :l_record_type_id) = CAL.RECORD_TYPE_ID'||
384 ' AND CAL.Report_Date = LEAST(TIME_PERIOD.END_date,&BIS_CURRENT_ASOF_DATE) '|| l_whereclause|| ') TIME,'||
385 l_tableList||
386 ' WHERE'||
387 ' FACT.TIME_ID (+)= TIME.TIME_ID'||
388 ' AND FACT.PERIOD_TYPE_ID (+)= TIME.PERIOD_TYPE_ID'||
389 l_msiteFilter ||') FACT'||
390 ' WHERE '||
391 ' FACT.MEASURE_TYPE IN (''IBE_ORD_TOTAL'',''IBE_ORD_ASSISTED'',''IBE_ORD_UNASSISTED'',''NULL'')'||l_allWhere ||
392 ' ) GROUP BY ENT_YEAR_ID,SEQUENCE'||
393 ' ORDER BY ENT_YEAR_ID, SEQUENCE';
394
395
396 END IF;
397
398 END IF;
399
400 x_custom_sql := l_custom_sql;
401
402 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
403 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,'ibe.plsql.dbi.ibe_bi_ord_graph_pvt.get_order_graph_sql.query',l_custom_sql);
404 END IF;
405
409 l_custom_rec.attribute_name := ':l_sequence';
406 x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
407 x_custom_output.EXTEND(13);
408
410 l_custom_rec.attribute_value := l_sequence;
411 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
412 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.NUMERIC_BIND;
413
414 x_custom_output(1) := l_custom_rec;
415
416 l_custom_rec.attribute_name := ':l_record_type_id' ;
417 l_custom_rec.attribute_value:= l_record_type_id;
418 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
419 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
420
421 x_custom_output(2) := l_custom_rec;
422
423 l_custom_rec.attribute_name := ':l_prev_start' ;
424 l_custom_rec.attribute_value:= to_char(l_prev_start,'dd/mm/yyyy');
425 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
426 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.DATE_BIND;
427
428 x_custom_output(3) := l_custom_rec;
429
430 l_custom_rec.attribute_name := ':l_cur_start' ;
431 l_custom_rec.attribute_value:= to_char(l_cur_start,'dd/mm/yyyy');
432 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
433 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.DATE_BIND;
434
435 x_custom_output(4) := l_custom_rec;
436
437 l_custom_rec.attribute_name := ':l_prev_year' ;
438 l_custom_rec.attribute_value:= l_prev_year;
439 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
440 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
441
442 x_custom_output(5) := l_custom_rec;
443
444 l_custom_rec.attribute_name := ':l_cur_year' ;
445 l_custom_rec.attribute_value:= l_cur_year;
446 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
447 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
448
449 x_custom_output(6) := l_custom_rec;
450
451 l_custom_rec.attribute_name := ':l_timespan' ;
452 l_custom_rec.attribute_value:= l_timespan;
453 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
454 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
455
456 x_custom_output(7) := l_custom_rec;
457
458
459 l_custom_rec.attribute_name := ':l_mid_start' ;
460 l_custom_rec.attribute_value:= to_char(l_mid_start,'dd/mm/yyyy');
461 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
462 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.DATE_BIND;
463
464 x_custom_output(8) := l_custom_rec;
465
466 l_custom_rec.attribute_name := ':l_pprev_start' ;
467 l_custom_rec.attribute_value:= to_char(l_pprev_start,'dd/mm/yyyy');
468 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
469 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.DATE_BIND;
470
471 x_custom_output(9) := l_custom_rec;
472
473
474 l_custom_rec.attribute_name := ':l_pcur_start' ;
475 l_custom_rec.attribute_value:= to_char(l_pcur_start,'dd/mm/yyyy');
476 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
477 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.DATE_BIND;
478
479 x_custom_output(10) := l_custom_rec;
480
481 l_custom_rec.attribute_name := ':l_currency_code' ;
482 l_custom_rec.attribute_value:= l_currency_code;
483 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
484 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
485
486 x_custom_output(11) := l_custom_rec;
487
488 l_custom_rec.attribute_name := ':l_g_p' ;
489 l_custom_rec.attribute_value:= l_g_p;
490 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
491 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
492
493 x_custom_output(12) := l_custom_rec;
494
495 l_custom_rec.attribute_name := ':l_g_s' ;
496 l_custom_rec.attribute_value:= l_g_s;
497 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
498 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
499
500 x_custom_output(13) := l_custom_rec;
501
502 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
503 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,'ibe.plsql.dbi.ibe_bi_ord_graph_pvt.get_order_graph_sql.end','END');
504 END IF;
505
506 END GET_ORDER_GRAPH_SQL;
507
508
509 PROCEDURE GET_AVG_ORD_GRAPH_SQL(
510 p_pmv_parameters IN BIS_PMV_PAGE_PARAMETER_tbl,
511 x_custom_sql OUT NOCOPY VARCHAR2,
512 x_custom_output OUT NOCOPY bis_query_attributes_TBL )
513 IS
514 l_custom_sql VARCHAR2(4000);
515 l_parameter_name VARCHAR2(3200);
516 l_asof_date DATE;
517 l_prev_date DATE;
518 l_timespan NUMBER;
519 l_sequence NUMBER;
520 l_cur_start DATE;
521 l_mid_start DATE;
522 l_prev_start DATE;
523 l_pprev_start DATE;
524 l_pcur_start DATE;
525 l_cur_year NUMBER;
526 l_prev_year NUMBER;
527 l_record_type_id NUMBER;
528 l_period_type VARCHAR2(3200);
529 l_comparison_type VARCHAR2(3200);
533 l_custom_rec BIS_QUERY_ATTRIBUTES;
530 l_currency_code VARCHAR2(3200);
531 l_minisite VARCHAR2(3200);
532 l_minisite_id VARCHAR2(3200);
534 l_timetable VARCHAR2(1000);
535 l_whereclause VARCHAR2(3200);
536 l_msiteFilter VARCHAR2(1000);
537 l_tableList VARCHAR2(1000);
538 l_allSelect VARCHAR2(1000);
539 l_allWhere VARCHAR2(1000);
540 dbg_msg VARCHAR2(3200);
541 l_g_p VARCHAR2(15);
542 l_g_s VARCHAR2(15) ;
543
544 /**************************************************************************/
545 /* These two variables are used to eliminate the Records corresponding to */
546 /* those stores that the user does not have access to */
547 /**************************************************************************/
548 BEGIN
549
550 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
551 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,'ibe.plsql.dbi.ibe_bi_ord_graph_pvt.get_avg_ord_graph_sql.begin','BEGIN');
552 END IF;
553
554 -- initilization of variables
555
556 l_custom_rec := BIS_PMV_PARAMETERS_PUB.Initialize_Query_Type;
557 l_g_p := '''FII_GLOBAL1''';
558 l_g_s := '''FII_GLOBAL2''';
559
560 FOR i IN p_pmv_parameters.FIRST..p_pmv_parameters.LAST
561 LOOP
562 l_parameter_name := p_pmv_parameters(i).parameter_name ;
563
564 IF( l_parameter_name = 'AS_OF_DATE')
565 THEN
566 l_asof_date :=
567 TO_DATE(p_pmv_parameters(i).parameter_value,'DD/MM/YYYY');
568 ELSIF( l_parameter_name = 'IBW_WEB_ANALYTICS_GROUP1+FII_CURRENCIES')
569 THEN
570 l_currency_code := p_pmv_parameters(i).parameter_id;
571 ELSIF( l_parameter_name = 'PERIOD_TYPE')
572 THEN
573 l_period_type := p_pmv_parameters(i).parameter_value;
574 ELSIF( l_parameter_name = 'TIME_COMPARISON_TYPE')
575 THEN
576 l_comparison_type := p_pmv_parameters(i).parameter_value;
577 ELSIF( l_parameter_name = 'SITE+SITE')
578 THEN
579 l_minisite := p_pmv_parameters(i).parameter_value;
580 l_minisite_id := p_pmv_parameters(i).parameter_id;
581 END IF;
582 END LOOP;
583
584 --Change in View done for Bug#:4654974. Issue#12 by narao
585 --The Site Dimension now refers to IBW_BI_MSITE_DIMN_V
586 --And hence the MSITES View is changed from IBE_BI_MSITES_V to IBW_BI_MSITE_DIMN_V.
587
588 IF (trim(l_minisite_id) is null) then
589 l_tableList := ' IBE_BI_CART_ORD_MV FACT,'||
590 -- ' IBE_BI_MSITES_V MSITES';
591 ' IBW_BI_MSITE_DIMN_V MSITES';
592
593 l_msiteFilter :=' AND FACT.MINISITE_ID = MSITES.ID(+)';
594
595 l_allSelect := ' MINISITE_ID,ID,';
596 l_allWhere := ' AND nvl(decode(MINISITE_ID,NULL,NULL,nvl(ID,-999)),0) <> -999 ';
597
598 ELSE
599 l_tableList := ' IBE_BI_CART_ORD_MV FACT';
600
601 l_msiteFilter := ' AND FACT.MINISITE_ID in (&SITE+SITE) ';
602
603 END IF;
604
605
606 IF(l_period_type = 'FII_TIME_ENT_YEAR') THEN
607 l_comparison_type := 'SEQUENTIAL';
608 l_record_type_id := 119;
609 l_timetable := 'FII_TIME_ENT_YEAR';
610 l_whereclause := ' AND TIME_PERIOD.SEQUENCE BETWEEN :l_sequence-3 AND :l_sequence';
611
612 IBE_BI_PMV_UTIL_PVT.ENT_YR_SPAN(p_asof_date => l_asof_date,
613 x_timespan => l_timespan,
614 x_sequence => l_sequence);
615
616 ELSIF (l_period_type = 'FII_TIME_ENT_QTR') THEN
617 l_record_type_id := 55;
618 l_timetable := 'FII_TIME_ENT_QTR';
619 l_whereclause:= ' AND TIME_PERIOD.start_date between :l_prev_start and :l_cur_start'||
620 ' AND TIME_PERIOD.ent_year_id BETWEEN :l_prev_year AND :l_cur_year ';
621
622 IBE_BI_PMV_UTIL_PVT.ENT_QTR_SPAN(
623 p_asof_date => l_asof_date,
624 p_comparator=> l_comparison_type,
625 x_cur_start => l_cur_start,
626 x_mid_start => l_mid_start,
627 x_prev_start=> l_prev_start,
628 x_cur_year => l_cur_year,
629 x_prev_year => l_prev_year,
630 x_timespan => l_timespan);
631
632 ELSIF (l_period_type = 'FII_TIME_ENT_PERIOD') THEN
633 l_record_type_id := 23;
634 l_timetable := 'FII_TIME_ENT_PERIOD';
635 l_whereclause:= ' AND TIME_PERIOD.start_date between :l_prev_start and :l_cur_start ';
636
637 IBE_BI_PMV_UTIL_PVT.ENT_PRD_SPAN(
638 p_asof_date => l_asof_date,
639 p_comparator=> l_comparison_type,
640 x_cur_start => l_cur_start,
641 x_mid_start => l_mid_start,
642 x_prev_start=> l_prev_start,
643 x_cur_year => l_cur_year,
644 x_prev_year => l_prev_year,
645 x_timespan => l_timespan);
646
647 IF(l_comparison_type = 'SEQUENTIAL') THEN
648 l_prev_start := l_mid_start + 1;
649 END IF;
650
651 ELSIF (l_period_type = 'FII_TIME_WEEK') THEN
652 l_record_type_id := 11;
653 l_timetable := 'FII_TIME_WEEK';
654 l_whereclause:= ' AND TIME_PERIOD.start_date between :l_prev_start and :l_cur_start ';
655
656 IBE_BI_PMV_UTIL_PVT.WEEK_SPAN(
657 p_asof_date => l_asof_date,
658 p_comparator => l_comparison_type,
662 x_pprev_start => l_pprev_start,
659 x_cur_start => l_cur_start,
660 x_prev_start => l_prev_start,
661 x_pcur_start => l_pcur_start,
663 x_timespan => l_timespan);
664
665 l_mid_start := l_prev_start;
666
667
668 END IF;
669
670 dbg_msg := 'AS_OF_DATE:'||l_asof_date||','||'CURR_CODE:'||l_currency_code||
671 ','||'PERIOD_TYPE:'||l_period_type||','||'COMPARISION_TYPE:'||
672 l_comparison_type||','||'MINISITE:'||l_minisite||','||'MINISITE_ID:'||
673 l_minisite_id||','||'RECORD_TYPE_ID:'||l_record_type_id||','||'TIME_TABLE:'||
674 l_timetable||','||'CURR_START_DATE:'||l_cur_start||','||'PREV_START_DATE:'||
675 l_prev_start||','||'MID_START_DATE:'||l_mid_start||','||'CURR_YEAR:'||
676 l_cur_year||','||'PREV_YEAR:'||l_prev_year||','||'TIME_SPAN:'||l_timespan;
677
678 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
679 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,'ibe.plsql.dbi.ibe_bi_ord_graph_pvt.get_avg_ord_graph_sql.parameters',dbg_msg);
680 END IF;
681
682 IF(l_comparison_type = 'SEQUENTIAL') THEN
683
684 /*************************************************************************/
685 /* The following Query would work for all the Cases where Comparision */
686 /* type is sequential. Depending upon the Period Type selected the */
687 /* Value of TIME_PERIOD and the whereclause would change */
688 /*************************************************************************/
689
690 /*************************************************************************/
691 /* VIEWBY : Period Name */
692 /* IBE_ATTR1 : Period Name */
693 /* IBE_VAL1 : Previous Total */
694 /* IBE_VAL2 : Current Total */
695 /* IBE_VAL3 : Previous Assisted */
696 /* IBE_VAL4 : Current Assisted */
697 /* IBE_VAL5 : Previous Unassisted */
698 /* IBE_VAL6 : Current Unassisted */
699 /*************************************************************************/
700
701
702 l_custom_sql :='SELECT FACT.NAME VIEWBY,FACT.NAME IBE_ATTR1,'||
703 ' decode(SUM(NVL(TOTAL_CNT,0)),0,0,(SUM(NVL(AMOUNT_TOTAL,0))/SUM(NVL(TOTAL_CNT,0)))) IBE_VAL2,'||
704 ' decode(SUM(NVL(ASSIST_CNT,0)),0,0,(SUM(NVL(AMOUNT_ASSIST,0))/SUM(NVL(ASSIST_CNT,0)))) IBE_VAL4,'||
705 ' decode(SUM(NVL(UNASSIST_CNT,0)),0,0,(SUM(NVL(AMOUNT_UNASSIST,0))/SUM(NVL(UNASSIST_CNT,0)))) IBE_VAL6,'||
706 ' NULL IBE_VAL1,'||
707 ' NULL IBE_VAL3,'||
708 ' NULL IBE_VAL5'||
709 ' FROM ('||
710 ' SELECT '||l_allSelect ||
711 ' TIME.NAME, NVL(MEASURE_TYPE,''NULL'') MEASURE_TYPE,'||
712 ' decode(MEASURE_TYPE,''IBE_ORD_TOTAL'','||
713 ' decode(:l_currency_code,:l_g_p,AMOUNT_G,:l_g_s,AMOUNT_G1,CURRENCY_CD_F,AMOUNT_F),0) AMOUNT_TOTAL,'||
714 ' decode(MEASURE_TYPE,''IBE_ORD_TOTAL'','||
715 ' TOT_COUNT ,0) TOTAL_CNT,'||
716 ' decode(MEASURE_TYPE,''IBE_ORD_ASSISTED'','||
717 ' decode(:l_currency_code,:l_g_p,AMOUNT_G,:l_g_s,AMOUNT_G1,CURRENCY_CD_F,AMOUNT_F),0) AMOUNT_ASSIST,'||
718 ' decode(MEASURE_TYPE,''IBE_ORD_ASSISTED'','||
719 ' TOT_COUNT ,0) ASSIST_CNT,'||
720 ' decode(MEASURE_TYPE,''IBE_ORD_UNASSISTED'','||
721 ' decode(:l_currency_code,:l_g_p,AMOUNT_G,:l_g_s,AMOUNT_G1,CURRENCY_CD_F,AMOUNT_F),0) AMOUNT_UNASSIST,'||
722 ' decode(MEASURE_TYPE,''IBE_ORD_UNASSISTED'','||
723 ' TOT_COUNT ,0) UNASSIST_CNT,'||
724 ' TIME.PERIOD_TYPE_ID,TIME.TIME_ID,TIME.REPORT_DATE'||
725 ' FROM'||
726 ' (SELECT'||
727 ' TIME_PERIOD.NAME,'||
728 ' CAL.REPORT_DATE,'||
729 ' CAL.PERIOD_TYPE_ID,'||
730 ' CAL.TIME_ID'||
731 ' FROM'||
732 ' FII_TIME_RPT_STRUCT_V CAL,'||l_timetable||' TIME_PERIOD'||
733 ' WHERE BITAND(CAL.RECORD_TYPE_ID, :l_record_type_id) = CAL.RECORD_TYPE_ID'||
734 ' AND CAL.Report_Date = LEAST(TIME_PERIOD.END_date,&BIS_CURRENT_ASOF_DATE) ' ||l_whereclause|| ') TIME,'||
735 l_tableList||
736 ' WHERE'||
737 ' FACT.TIME_ID (+)= TIME.TIME_ID'||
738 ' AND FACT.PERIOD_TYPE_ID(+)= TIME.PERIOD_TYPE_ID'||
739 l_msiteFilter||') FACT'||
740 ' WHERE FACT.MEASURE_TYPE IN (''IBE_ORD_TOTAL'',''IBE_ORD_ASSISTED'',''IBE_ORD_UNASSISTED'',''NULL'')'|| l_allWhere ||
741 ' GROUP BY FACT.NAME, REPORT_DATE ORDER BY REPORT_DATE';
742
743
744
745
746 ELSE
747
748 IF l_period_type = 'FII_TIME_WEEK' THEN
749
750 /*************************************************************************/
751 /* The following Query would work when the Time Period is WEEK and the */
752 /* Comparison type is Year-To-Year. */
753 /*************************************************************************/
754
755 /*************************************************************************/
756 /* VIEWBY : Period Name */
760 /* IBE_VAL3 : Previous Assisted */
757 /* IBE_ATTR1 : Period Name */
758 /* IBE_VAL1 : Previous Total */
759 /* IBE_VAL2 : Current Total */
761 /* IBE_VAL4 : Current Assisted */
762 /* IBE_VAL5 : Previous Unassisted */
763 /* IBE_VAL6 : Current Unassisted */
764 /*************************************************************************/
765
766
767 l_custom_sql :='SELECT MAX(NAME) VIEWBY, MAX(NAME) IBE_ATTR1,'||
768 ' decode(SUM(NVL(CT_COUNT,0)),0,0,(SUM(NVL(CT_AMOUNT,0))/SUM(CT_COUNT))) IBE_VAL2,'||
769 ' decode(SUM(NVL(CA_COUNT,0)),0,0,(SUM(NVL(CA_AMOUNT,0))/SUM(CA_COUNT))) IBE_VAL4,'||
770 ' decode(SUM(NVL(CU_COUNT,0)),0,0,(SUM(NVL(CU_AMOUNT,0))/SUM(CU_COUNT))) IBE_VAL6,'||
771 ' decode(SUM(NVL(PT_COUNT,0)),0,0,(SUM(NVL(PT_AMOUNT,0))/SUM(PT_COUNT))) IBE_VAL1,'||
772 ' decode(SUM(NVL(PA_COUNT,0)),0,0,(SUM(NVL(PA_AMOUNT,0))/SUM(PA_COUNT))) IBE_VAL3,'||
773 ' decode(SUM(NVL(PU_COUNT,0)),0,0,(SUM(NVL(PU_AMOUNT,0))/SUM(PU_COUNT))) IBE_VAL5'||
774 ' FROM ('||
775 ' SELECT'||
776 ' (CASE WHEN fact.start_date < :l_mid_start'||
777 ' THEN null else fact.name end) NAME,'||
778 ' (CASE WHEN fact.start_date < :l_mid_start'||
779 ' THEN fact.year445_id+1 else fact.year445_id end) ENT_YEAR_ID,'||
780 ' (CASE WHEN fact.start_date < :l_mid_start'||
781 ' THEN FACT.AMOUNT_TOTAL else 0 end) PT_AMOUNT,'||
782 ' (CASE WHEN fact.start_date < :l_mid_start'||
783 ' THEN FACT.TOTAL_CNT else 0 end) PT_COUNT,'||
784 ' (CASE WHEN fact.start_date >= :l_mid_start'||
785 ' THEN FACT.AMOUNT_TOTAL else 0 end) CT_AMOUNT,'||
786 ' (CASE WHEN fact.start_date >= :l_mid_start'||
787 ' THEN FACT.TOTAL_CNT else 0 end) CT_COUNT,'||
788 ' (CASE WHEN fact.start_date < :l_mid_start'||
789 ' THEN FACT.AMOUNT_ASSIST else 0 end) PA_AMOUNT,'||
790 ' (CASE WHEN fact.start_date < :l_mid_start'||
791 ' THEN FACT.ASSIST_CNT else 0 end) PA_COUNT,'||
792 ' (CASE WHEN fact.start_date >= :l_mid_start'||
793 ' THEN FACT.AMOUNT_ASSIST else 0 end) CA_AMOUNT,'||
794 ' (CASE WHEN fact.start_date >= :l_mid_start'||
795 ' THEN FACT.ASSIST_CNT else 0 end) CA_COUNT,'||
796 ' (CASE WHEN fact.start_date < :l_mid_start'||
797 ' THEN FACT.AMOUNT_UNASSIST else 0 end) PU_AMOUNT,'||
798 ' (CASE WHEN fact.start_date < :l_mid_start'||
799 ' THEN FACT.UNASSIST_CNT else 0 end) PU_COUNT,'||
800 ' (CASE WHEN fact.start_date >= :l_mid_start'||
801 ' THEN FACT.AMOUNT_UNASSIST else 0 end) CU_AMOUNT,'||
802 ' (CASE WHEN fact.start_date >= :l_mid_start'||
803 ' THEN FACT.UNASSIST_CNT else 0 end) CU_COUNT,'||
804 ' REPORT_DATE,'||
805 ' SEQUENCE'||
806 ' FROM ('||
807 ' SELECT'|| l_allSelect ||
808 ' NVL(MEASURE_TYPE,''NULL'') MEASURE_TYPE,'||
809 ' decode(MEASURE_TYPE,''IBE_ORD_TOTAL'','||
810 ' decode(:l_currency_code,:l_g_p,AMOUNT_G,:l_g_s,AMOUNT_G1,CURRENCY_CD_F,AMOUNT_F),0) AMOUNT_TOTAL,'||
811 ' decode(MEASURE_TYPE,''IBE_ORD_TOTAL'','||
812 ' TOT_COUNT,0) TOTAL_CNT,'||
813 ' decode(MEASURE_TYPE,''IBE_ORD_ASSISTED'','||
814 ' decode(:l_currency_code,:l_g_p,AMOUNT_G,:l_g_s,AMOUNT_G1,CURRENCY_CD_F,AMOUNT_F),0) AMOUNT_ASSIST,'||
815 ' decode(MEASURE_TYPE,''IBE_ORD_ASSISTED'','||
816 ' TOT_COUNT,0) ASSIST_CNT,'||
817 ' decode(MEASURE_TYPE,''IBE_ORD_UNASSISTED'','||
818 ' decode(:l_currency_code,:l_g_p,AMOUNT_G,:l_g_s,AMOUNT_G1,CURRENCY_CD_F,AMOUNT_F),0) AMOUNT_UNASSIST,'||
819 ' decode(MEASURE_TYPE,''IBE_ORD_UNASSISTED'','||
820 ' TOT_COUNT,0) UNASSIST_CNT,'||
821 ' TIME.PERIOD_TYPE_ID,'||
822 ' TIME.TIME_ID,TIME.SEQUENCE,TIME.START_DATE,'||
823 ' TIME.YEAR445_ID,TIME.NAME,TIME.REPORT_DATE'||
824 ' FROM '||
825 ' ( SELECT '||
826 ' TIME_PERIOD.NAME,'||
827 ' CAL.REPORT_DATE,'||
828 ' CAL.PERIOD_TYPE_ID,'||
829 ' CAL.TIME_ID, '||
830 ' TIME_PERIOD.SEQUENCE,'||
831 ' TIME_PERIOD.START_DATE,'||
832 ' TIME.year445_id'||
833 ' FROM '||
834 ' FII_TIME_RPT_STRUCT_V CAL,'||
835 ' FII_TIME_WEEK TIME_PERIOD,'||
836 ' FII_TIME_p445 TIME'||
837 ' WHERE '||
838 ' BITAND(CAL.RECORD_TYPE_ID, :l_record_type_id) = CAL.RECORD_TYPE_ID'||
839 ' AND CAL.Report_Date = LEAST(TIME_PERIOD.END_date,&BIS_CURRENT_ASOF_DATE)'||
840 ' AND (TIME_PERIOD.start_date between :l_pprev_start and :l_pcur_start OR'||
841 ' TIME_PERIOD.start_date between :l_prev_start and :l_cur_start)'||
845 ' FACT.TIME_ID (+)= TIME.TIME_ID'||
842 ' AND TIME.period445_id = TIME_PERIOD.period445_id ) TIME,'||
843 l_tableList||
844 ' WHERE '||
846 ' AND FACT.PERIOD_TYPE_ID(+)= TIME.PERIOD_TYPE_ID'||
847 l_msiteFilter||' ) FACT'||
848 ' WHERE'||
849 ' FACT.MEASURE_TYPE IN (''IBE_ORD_TOTAL'',''IBE_ORD_ASSISTED'',''IBE_ORD_UNASSISTED'',''NULL'')'|| l_allWhere ||
850 ' ) GROUP BY ENT_YEAR_ID,SEQUENCE'||
851 ' ORDER BY ENT_YEAR_ID,SEQUENCE';
852
853
854 ELSE
855
856 /*************************************************************************/
857 /* The following Query would work when the Time Period is anything other */
858 /* than WEEK and the Comparison type is Year-To-Year. */
859 /*************************************************************************/
860
861 /*************************************************************************/
862 /* VIEWBY : Period Name */
863 /* IBE_ATTR1 : Period Name */
864 /* IBE_VAL1 : Previous Total */
865 /* IBE_VAL2 : Current Total */
866 /* IBE_VAL3 : Previous Assisted */
867 /* IBE_VAL4 : Current Assisted */
868 /* IBE_VAL5 : Previous Unassisted */
869 /* IBE_VAL6 : Current Unassisted */
870 /*************************************************************************/
871
872 l_custom_sql :='SELECT MAX(NAME) VIEWBY,MAX(NAME) IBE_ATTR1,'||
873 ' decode(SUM(NVL(CT_COUNT,0)),0,0,(SUM(NVL(CT_AMOUNT,0))/SUM(CT_COUNT))) IBE_VAL2,'||
874 ' decode(SUM(NVL(CA_COUNT,0)),0,0,(SUM(NVL(CA_AMOUNT,0))/SUM(CA_COUNT))) IBE_VAL4,'||
875 ' decode(SUM(NVL(CU_COUNT,0)),0,0,(SUM(NVL(CU_AMOUNT,0))/SUM(CU_COUNT))) IBE_VAL6,'||
876 ' decode(SUM(NVL(PT_COUNT,0)),0,0,(SUM(NVL(PT_AMOUNT,0))/SUM(PT_COUNT))) IBE_VAL1,'||
877 ' decode(SUM(NVL(PA_COUNT,0)),0,0,(SUM(NVL(PA_AMOUNT,0))/SUM(PA_COUNT))) IBE_VAL3,'||
878 ' decode(SUM(NVL(PU_COUNT,0)),0,0,(SUM(NVL(PU_AMOUNT,0))/SUM(PU_COUNT))) IBE_VAL5'||
879 ' FROM ('||
880 ' SELECT'||
881 ' (CASE WHEN fact.start_date <= :l_mid_start'||
882 ' THEN null else fact.name end) NAME,'||
883 ' (CASE WHEN fact.start_date <= :l_mid_start'||
884 ' THEN fact.ent_year_id+1 else fact.ent_year_id end) ENT_YEAR_ID,'||
885 ' (CASE WHEN fact.start_date <= :l_mid_start'||
886 ' THEN FACT.AMOUNT_TOTAL else 0 end) PT_AMOUNT,'||
887 ' (CASE WHEN fact.start_date <= :l_mid_start'||
888 ' THEN FACT.TOTAL_CNT else 0 end) PT_COUNT,'||
889 ' (CASE WHEN fact.start_date > :l_mid_start'||
890 ' THEN FACT.AMOUNT_TOTAL else 0 end) CT_AMOUNT,'||
891 ' (CASE WHEN fact.start_date > :l_mid_start'||
892 ' THEN FACT.TOTAL_CNT else 0 end) CT_COUNT,'||
893 ' (CASE WHEN fact.start_date <= :l_mid_start'||
894 ' THEN FACT.AMOUNT_ASSIST else 0 end) PA_AMOUNT,'||
895 ' (CASE WHEN fact.start_date <= :l_mid_start'||
896 ' THEN FACT.ASSIST_CNT else 0 end) PA_COUNT,'||
897 ' (CASE WHEN fact.start_date > :l_mid_start'||
898 ' THEN FACT.AMOUNT_ASSIST else 0 end) CA_AMOUNT,'||
899 ' (CASE WHEN fact.start_date > :l_mid_start'||
900 ' THEN FACT.ASSIST_CNT else 0 end) CA_COUNT,'||
901 ' (CASE WHEN fact.start_date <= :l_mid_start'||
902 ' THEN FACT.AMOUNT_UNASSIST else 0 end) PU_AMOUNT,'||
903 ' (CASE WHEN fact.start_date <= :l_mid_start'||
904 ' THEN FACT.UNASSIST_CNT else 0 end) PU_COUNT,'||
905 ' (CASE WHEN fact.start_date > :l_mid_start'||
906 ' THEN FACT.AMOUNT_UNASSIST else 0 end) CU_AMOUNT,'||
907 ' (CASE WHEN fact.start_date > :l_mid_start'||
908 ' THEN FACT.UNASSIST_CNT else 0 end) CU_COUNT,'||
909 ' fact.REPORT_DATE,'||
910 ' fact.SEQUENCE'||
911 ' FROM ('||
912 ' SELECT'|| l_allSelect||
913 ' NVL(MEASURE_TYPE,''NULL'') MEASURE_TYPE,'||
914 ' decode(MEASURE_TYPE,''IBE_ORD_TOTAL'','||
915 ' decode(:l_currency_code,:l_g_p,AMOUNT_G,:l_g_s,AMOUNT_G1,CURRENCY_CD_F,AMOUNT_F),0) AMOUNT_TOTAL,'||
916 ' decode(MEASURE_TYPE,''IBE_ORD_TOTAL'','||
917 ' TOT_COUNT,0) TOTAL_CNT,'||
918 ' decode(MEASURE_TYPE,''IBE_ORD_ASSISTED'','||
919 ' decode(:l_currency_code,:l_g_p,AMOUNT_G,:l_g_s,AMOUNT_G1,CURRENCY_CD_F,AMOUNT_F),0) AMOUNT_ASSIST,'||
920 ' decode(MEASURE_TYPE,''IBE_ORD_ASSISTED'','||
921 ' TOT_COUNT,0) ASSIST_CNT,'||
922 ' decode(MEASURE_TYPE,''IBE_ORD_UNASSISTED'','||
923 ' decode(:l_currency_code,:l_g_p,AMOUNT_G,:l_g_s,AMOUNT_G1,CURRENCY_CD_F,AMOUNT_F),0) AMOUNT_UNASSIST,'||
924 ' decode(MEASURE_TYPE,''IBE_ORD_UNASSISTED'','||
925 ' TOT_COUNT,0) UNASSIST_CNT,'||
926 ' TIME.PERIOD_TYPE_ID,'||
927 ' TIME.TIME_ID,TIME.SEQUENCE,TIME.START_DATE,'||
931 ' TIME_PERIOD.NAME,'||
928 ' TIME.ENT_YEAR_ID,TIME.NAME,TIME.REPORT_DATE'||
929 ' FROM'||
930 ' (SELECT'||
932 ' CAL.REPORT_DATE,'||
933 ' CAL.PERIOD_TYPE_ID,'||
934 ' CAL.TIME_ID,'||
935 ' TIME_PERIOD.SEQUENCE,'||
936 ' TIME_PERIOD.START_DATE,'||
937 ' TIME_PERIOD.ENT_YEAR_ID'||
938 ' FROM '||
939 ' FII_TIME_RPT_STRUCT_V CAL,'||l_timetable||' TIME_PERIOD'||
940 ' WHERE BITAND(CAL.RECORD_TYPE_ID, :l_record_type_id) = CAL.RECORD_TYPE_ID'||
941 ' AND BITAND(CAL.RECORD_TYPE_ID, :l_record_type_id) = CAL.RECORD_TYPE_ID'||
942 ' AND CAL.Report_Date = LEAST(TIME_PERIOD.END_date,&BIS_CURRENT_ASOF_DATE) '|| l_whereclause|| ') TIME,'||
943 l_tableList||
944 ' WHERE'||
945 ' FACT.TIME_ID (+)= TIME.TIME_ID'||
946 ' AND FACT.PERIOD_TYPE_ID (+)= TIME.PERIOD_TYPE_ID'||
947 l_msiteFilter ||') FACT'||
948 ' WHERE '||
949 ' FACT.MEASURE_TYPE IN (''IBE_ORD_TOTAL'',''IBE_ORD_ASSISTED'',''IBE_ORD_UNASSISTED'',''NULL'')'||l_allWhere ||
950 ' ) GROUP BY ENT_YEAR_ID,SEQUENCE'||
951 ' ORDER BY ENT_YEAR_ID, SEQUENCE';
952
953
954
955 END IF;
956
957 END IF;
958
959 x_custom_sql := l_custom_sql;
960
961 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
962 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,'ibe.plsql.dbi.ibe_bi_ord_graph_pvt.get_avg_ord_graph_sql.query',l_custom_sql);
963 END IF;
964
965 x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
966 x_custom_output.EXTEND(13);
967
968 l_custom_rec.attribute_name := ':l_sequence';
969 l_custom_rec.attribute_value := l_sequence;
970 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
971 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.NUMERIC_BIND;
972
973 x_custom_output(1) := l_custom_rec;
974
975 l_custom_rec.attribute_name := ':l_record_type_id' ;
976 l_custom_rec.attribute_value:= l_record_type_id;
977 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
978 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
979
980 x_custom_output(2) := l_custom_rec;
981
982 l_custom_rec.attribute_name := ':l_prev_start' ;
983 l_custom_rec.attribute_value:= to_char(l_prev_start,'dd/mm/yyyy');
984 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
985 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.DATE_BIND;
986
987 x_custom_output(3) := l_custom_rec;
988
989 l_custom_rec.attribute_name := ':l_cur_start' ;
990 l_custom_rec.attribute_value:= to_char(l_cur_start,'dd/mm/yyyy');
991 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
992 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.DATE_BIND;
993
994 x_custom_output(4) := l_custom_rec;
995
996 l_custom_rec.attribute_name := ':l_prev_year' ;
997 l_custom_rec.attribute_value:= l_prev_year;
998 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
999 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
1000
1001 x_custom_output(5) := l_custom_rec;
1002
1003 l_custom_rec.attribute_name := ':l_cur_year' ;
1004 l_custom_rec.attribute_value:= l_cur_year;
1005 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
1006 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
1007
1008 x_custom_output(6) := l_custom_rec;
1009
1010 l_custom_rec.attribute_name := ':l_timespan' ;
1011 l_custom_rec.attribute_value:= l_timespan;
1012 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
1013 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
1014
1015 x_custom_output(7) := l_custom_rec;
1016
1017 l_custom_rec.attribute_name := ':l_mid_start' ;
1018 l_custom_rec.attribute_value:= to_char(l_mid_start,'dd/mm/yyyy');
1019 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
1020 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.DATE_BIND;
1021
1022 x_custom_output(8) := l_custom_rec;
1023
1024 l_custom_rec.attribute_name := ':l_pprev_start' ;
1025 l_custom_rec.attribute_value:= to_char(l_pprev_start,'dd/mm/yyyy');
1026 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
1027 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.DATE_BIND;
1028
1029 x_custom_output(9) := l_custom_rec;
1030
1031
1032 l_custom_rec.attribute_name := ':l_pcur_start' ;
1033 l_custom_rec.attribute_value:= to_char(l_pcur_start,'dd/mm/yyyy');
1034 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
1035 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.DATE_BIND;
1036
1037 x_custom_output(10) := l_custom_rec;
1038
1039 l_custom_rec.attribute_name := ':l_currency_code' ;
1040 l_custom_rec.attribute_value:= l_currency_code;
1041 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
1042 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
1043
1047 l_custom_rec.attribute_value:= l_g_p;
1044 x_custom_output(11) := l_custom_rec;
1045
1046 l_custom_rec.attribute_name := ':l_g_p' ;
1048 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
1049 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
1050
1051 x_custom_output(12) := l_custom_rec;
1052
1053 l_custom_rec.attribute_name := ':l_g_s' ;
1054 l_custom_rec.attribute_value:= l_g_s;
1055 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
1056 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
1057
1058 x_custom_output(13) := l_custom_rec;
1059
1060 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1061 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,'ibe.plsql.dbi.ibe_bi_ord_graph_pvt.get_avg_ord_graph_sql.end','END');
1062 END IF;
1063
1064 END GET_AVG_ORD_GRAPH_SQL;
1065
1066 PROCEDURE GET_AVG_DISC_GRAPH_SQL(
1067 p_pmv_parameters IN BIS_PMV_PAGE_PARAMETER_tbl,
1068 x_custom_sql OUT NOCOPY VARCHAR2,
1069 x_custom_output OUT NOCOPY bis_query_attributes_TBL )
1070 IS
1071 l_custom_sql VARCHAR2(4000);
1072 l_parameter_name VARCHAR2(3200);
1073 l_asof_date DATE;
1074 l_prev_date DATE;
1075 l_timespan NUMBER;
1076 l_sequence NUMBER;
1077 l_cur_start Date;
1078 l_mid_start Date;
1079 l_prev_start Date;
1080 l_pprev_start Date;
1081 l_pcur_start DATE;
1082 l_cur_year NUMBER;
1083 l_prev_year NUMBER;
1084 l_record_type_id NUMBER;
1085 l_period_type VARCHAR2(3200);
1086 l_comparison_type VARCHAR2(3200);
1087 l_c_d VARCHAR2(3200);
1088 l_minisite VARCHAR2(3200);
1089 l_minisite_id VARCHAR2(3200);
1090 l_custom_rec BIS_QUERY_ATTRIBUTES;
1091 l_timetable VARCHAR2(1000);
1092 l_whereclause VARCHAR2(3200);
1093 l_msiteFilter VARCHAR2(1000);
1094 l_tableList VARCHAR2(1000);
1095 l_allSelect VARCHAR2(1000);
1096 l_allWhere VARCHAR2(1000);
1097 dbg_msg VARCHAR2(3200);
1098
1099 l_g_p VARCHAR2(15);
1100 l_g_s VARCHAR2(15);
1101
1102
1103 /**************************************************************************/
1104 /* These two variables are used to eliminate the Records corresponding to */
1105 /* those stores that the user does not have access to */
1106 /**************************************************************************/
1107
1108
1109 BEGIN
1110
1111 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1112 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,'ibe.plsql.dbi.ibe_bi_ord_graph_pvt.get_avg_disc_graph_sql.begin','BEGIN');
1113 END IF;
1114
1115 -- initilization of variables
1116
1117 l_custom_rec := BIS_PMV_PARAMETERS_PUB.Initialize_Query_Type;
1118 l_g_p := '''FII_GLOBAL1''';
1119 l_g_s := '''FII_GLOBAL2''';
1120
1121 FOR i IN p_pmv_parameters.FIRST..p_pmv_parameters.LAST
1122 LOOP
1123 l_parameter_name := p_pmv_parameters(i).parameter_name ;
1124
1125 IF( l_parameter_name = 'AS_OF_DATE')
1126 THEN
1127 l_asof_date :=
1128 TO_DATE(p_pmv_parameters(i).parameter_value,'DD/MM/YYYY');
1129 ELSIF( l_parameter_name = 'IBW_WEB_ANALYTICS_GROUP1+FII_CURRENCIES')
1130 THEN
1131 l_c_d := p_pmv_parameters(i).parameter_id;
1132 ELSIF( l_parameter_name = 'PERIOD_TYPE')
1133 THEN
1134 l_period_type := p_pmv_parameters(i).parameter_value;
1135 ELSIF( l_parameter_name = 'TIME_COMPARISON_TYPE')
1136 THEN
1137 l_comparison_type := p_pmv_parameters(i).parameter_value;
1138 ELSIF( l_parameter_name = 'SITE+SITE')
1139 THEN
1140 l_minisite := p_pmv_parameters(i).parameter_value;
1141 l_minisite_id := p_pmv_parameters(i).parameter_id;
1142 END IF;
1143 END LOOP;
1144
1145 --Change in View done for Bug#:4654974. Issue#12 by narao
1146 --The Site Dimension now refers to IBW_BI_MSITE_DIMN_V
1147 --And hence the MSITES View is changed from IBE_BI_MSITES_V to IBW_BI_MSITE_DIMN_V.
1148
1149 IF (trim(l_minisite_id) is null) then
1150 l_tableList := ' IBE_BI_CART_ORD_MV FACT,'||
1151 --' IBE_BI_MSITES_V MSITES';
1152 ' IBW_BI_MSITE_DIMN_V MSITES';
1153
1154 l_msiteFilter :=' AND FACT.MINISITE_ID = MSITES.ID(+)';
1155
1156 l_allSelect := ' MINISITE_ID,ID,';
1157 l_allWhere := ' AND nvl(decode(MINISITE_ID,NULL,NULL,nvl(ID,-999)),0) <> -999 ';
1158
1159 ELSE
1160 l_tableList := ' IBE_BI_CART_ORD_MV FACT';
1161
1162 l_msiteFilter := ' AND FACT.MINISITE_ID in (&SITE+SITE) ';
1163
1164 END IF;
1165
1166
1167 IF(l_period_type = 'FII_TIME_ENT_YEAR') THEN
1168 l_comparison_type := 'SEQUENTIAL';
1169 l_record_type_id := 119;
1170 l_timetable := 'FII_TIME_ENT_YEAR';
1171 l_whereclause := ' AND TIME_PERIOD.SEQUENCE BETWEEN :l_sequence-3 AND :l_sequence';
1172
1173 IBE_BI_PMV_UTIL_PVT.ENT_YR_SPAN(p_asof_date => l_asof_date,
1174 x_timespan => l_timespan,
1175 x_sequence => l_sequence);
1176
1177 ELSIF (l_period_type = 'FII_TIME_ENT_QTR') THEN
1178 l_record_type_id := 55;
1182
1179 l_timetable := 'FII_TIME_ENT_QTR';
1180 l_whereclause:= ' AND TIME_PERIOD.start_date between :l_prev_start and :l_cur_start'||
1181 ' AND TIME_PERIOD.ent_year_id BETWEEN :l_prev_year AND :l_cur_year ';
1183 IBE_BI_PMV_UTIL_PVT.ENT_QTR_SPAN(
1184 p_asof_date => l_asof_date,
1185 p_comparator=> l_comparison_type,
1186 x_cur_start => l_cur_start,
1187 x_mid_start => l_mid_start,
1188 x_prev_start=> l_prev_start,
1189 x_cur_year => l_cur_year,
1190 x_prev_year => l_prev_year,
1191 x_timespan => l_timespan);
1192
1193 ELSIF (l_period_type = 'FII_TIME_ENT_PERIOD') THEN
1194 l_record_type_id := 23;
1195 l_timetable := 'FII_TIME_ENT_PERIOD';
1196 l_whereclause:= ' AND TIME_PERIOD.start_date between :l_prev_start and :l_cur_start ';
1197
1198 IBE_BI_PMV_UTIL_PVT.ENT_PRD_SPAN(
1199 p_asof_date => l_asof_date,
1200 p_comparator=> l_comparison_type,
1201 x_cur_start => l_cur_start,
1202 x_mid_start => l_mid_start,
1203 x_prev_start=> l_prev_start,
1204 x_cur_year => l_cur_year,
1205 x_prev_year => l_prev_year,
1206 x_timespan => l_timespan);
1207 IF(l_comparison_type = 'SEQUENTIAL') THEN
1208 l_prev_start := l_mid_start + 1;
1209 END IF;
1210
1211 ELSIF (l_period_type = 'FII_TIME_WEEK') THEN
1212 l_record_type_id := 11;
1213 l_timetable := 'FII_TIME_WEEK';
1214 l_whereclause:= ' AND TIME_PERIOD.start_date between :l_prev_start and :l_cur_start ';
1215
1216 IBE_BI_PMV_UTIL_PVT.WEEK_SPAN(
1217 p_asof_date => l_asof_date,
1218 p_comparator => l_comparison_type,
1219 x_cur_start => l_cur_start,
1220 x_prev_start => l_prev_start,
1221 x_pcur_start => l_pcur_start,
1222 x_pprev_start => l_pprev_start,
1223 x_timespan => l_timespan);
1224 l_mid_start := l_prev_start;
1225
1226 END IF;
1227
1228 dbg_msg := 'AS_OF_DATE:'||l_asof_date||','||'CURR_CODE:'||l_c_d||
1229 ','||'PERIOD_TYPE:'||l_period_type||','||'COMPARISION_TYPE:'||
1230 l_comparison_type||','||'MINISITE:'||l_minisite||','||'MINISITE_ID:'||
1231 l_minisite_id||','||'RECORD_TYPE_ID:'||l_record_type_id||','||
1232 'TIME_TABLE:'||l_timetable||','||'CURR_START_DATE:'||l_cur_start||','||
1233 'PREV_START_DATE:'||l_prev_start||','||'MID_START_DATE:'||l_mid_start||
1234 ','||'CURR_YEAR:'||l_cur_year||','||'PREV_YEAR:'||l_prev_year||','||
1235 'TIME_SPAN:'||l_timespan;
1236
1237 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1238 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,'ibe.plsql.dbi.ibe_bi_ord_graph_pvt.get_avg_disc_graph_sql.parameters',dbg_msg);
1239 END IF;
1240
1241 IF(l_comparison_type = 'SEQUENTIAL') THEN
1242
1243 /*************************************************************************/
1244 /* The following Query would work for all the Cases where Comparision */
1245 /* type is sequential. Depending upon the Period Type selected the */
1246 /* Value of TIME_PERIOD and the whereclause would change */
1247 /*************************************************************************/
1248
1249 /*************************************************************************/
1250 /* VIEWBY : Period Name */
1251 /* IBE_ATTR1 : Period Name */
1252 /* IBE_VAL1 : Previous Total */
1253 /* IBE_VAL2 : Current Total */
1254 /* IBE_VAL3 : Previous Assisted */
1255 /* IBE_VAL4 : Current Assisted */
1256 /* IBE_VAL5 : Previous Unassisted */
1257 /* IBE_VAL6 : Current Unassisted */
1258 /*************************************************************************/
1259
1260
1261 l_custom_sql :='SELECT FACT.NAME VIEWBY,FACT.NAME IBE_ATTR1,'||
1262 ' decode(SUM(NVL(LIST_TOTAL,0)),0,0,(SUM(NVL(DISC_TOTAL,0))/SUM(LIST_TOTAL)))*100 IBE_VAL2,'||
1263 ' decode(SUM(NVL(LIST_ASSIST,0)),0,0,(SUM(NVL(DISC_ASSIST,0))/SUM(LIST_ASSIST)))*100 IBE_VAL4,'||
1264 ' decode(SUM(NVL(LIST_UNASSIST,0)),0,0,(SUM(NVL(DISC_UNASSIST,0))/SUM(LIST_UNASSIST)))*100 IBE_VAL6,'||
1265 ' NULL IBE_VAL1,'||
1266 ' NULL IBE_VAL3,'||
1267 ' NULL IBE_VAL5'||
1268 ' FROM ('||
1269 ' SELECT '||l_allSelect ||
1270 ' TIME.NAME, NVL(MEASURE_TYPE,''NULL'') MEASURE_TYPE,'||
1271 ' decode(MEASURE_TYPE,''IBE_ORD_TOTAL'','||
1272 ' decode(:l_c_d,:l_g_p,DISC_AMOUNT_G,:l_g_s,DISC_AMOUNT_G1,CURRENCY_CD_F,DISC_AMOUNT_F),0) DISC_TOTAL,'||
1273 ' decode(MEASURE_TYPE,''IBE_ORD_TOTAL'','||
1274 ' decode(:l_c_d,:l_g_p,LIST_AMOUNT_G,:l_g_s,LIST_AMOUNT_G1,CURRENCY_CD_F,LIST_AMOUNT_F),0) LIST_TOTAL,'||
1275 ' decode(MEASURE_TYPE,''IBE_ORD_ASSISTED'','||
1276 ' decode(:l_c_d,:l_g_p,DISC_AMOUNT_G,:l_g_s,DISC_AMOUNT_G1,CURRENCY_CD_F,DISC_AMOUNT_F),0) DISC_ASSIST,'||
1277 ' decode(MEASURE_TYPE,''IBE_ORD_ASSISTED'','||
1278 ' decode(:l_c_d,:l_g_p,LIST_AMOUNT_G,:l_g_s,LIST_AMOUNT_G1,CURRENCY_CD_F,LIST_AMOUNT_F),0) LIST_ASSIST,'||
1282 ' decode(:l_c_d,:l_g_p,LIST_AMOUNT_G,:l_g_s,LIST_AMOUNT_G1,CURRENCY_CD_F,LIST_AMOUNT_F),0) LIST_UNASSIST,'||
1279 ' decode(MEASURE_TYPE,''IBE_ORD_UNASSISTED'','||
1280 ' decode(:l_c_d,:l_g_p,DISC_AMOUNT_G,:l_g_s,DISC_AMOUNT_G1,CURRENCY_CD_F,DISC_AMOUNT_F),0) DISC_UNASSIST,'||
1281 ' decode(MEASURE_TYPE,''IBE_ORD_UNASSISTED'','||
1283 ' TIME.PERIOD_TYPE_ID,TIME.TIME_ID, TIME.REPORT_DATE'||
1284 ' FROM'||
1285 ' (SELECT'||
1286 ' TIME_PERIOD.NAME,'||
1287 ' CAL.REPORT_DATE,'||
1288 ' CAL.PERIOD_TYPE_ID,'||
1289 ' CAL.TIME_ID'||
1290 ' FROM'||
1291 ' FII_TIME_RPT_STRUCT_V CAL,'||l_timetable||' TIME_PERIOD'||
1292 ' WHERE BITAND(CAL.RECORD_TYPE_ID, :l_record_type_id) = CAL.RECORD_TYPE_ID'||
1293 ' AND CAL.Report_Date = LEAST(TIME_PERIOD.END_date,&BIS_CURRENT_ASOF_DATE) ' ||l_whereclause|| ') TIME,'||
1294 l_tableList||
1295 ' WHERE'||
1296 ' FACT.TIME_ID (+)= TIME.TIME_ID'||
1297 ' AND FACT.PERIOD_TYPE_ID(+)= TIME.PERIOD_TYPE_ID'||
1298 l_msiteFilter||') FACT'||
1299 ' WHERE FACT.MEASURE_TYPE IN (''IBE_ORD_TOTAL'',''IBE_ORD_ASSISTED'',''IBE_ORD_UNASSISTED'',''NULL'')'|| l_allWhere ||
1300 ' GROUP BY FACT.NAME, REPORT_DATE ORDER BY REPORT_DATE';
1301
1302
1303
1304 ELSE
1305
1306 IF l_period_type = 'FII_TIME_WEEK' THEN
1307
1308 /*************************************************************************/
1309 /* The following Query would work when the Time Period is WEEK and the */
1310 /* Comparison type is Year-To-Year. */
1311 /*************************************************************************/
1312
1313 /*************************************************************************/
1314 /* VIEWBY : Period Name */
1315 /* IBE_ATTR1 : Period Name */
1316 /* IBE_VAL1 : Previous Total */
1317 /* IBE_VAL2 : Current Total */
1318 /* IBE_VAL3 : Previous Assisted */
1319 /* IBE_VAL4 : Current Assisted */
1320 /* IBE_VAL5 : Previous Unassisted */
1321 /* IBE_VAL6 : Current Unassisted */
1322 /*************************************************************************/
1323
1324 l_custom_sql := 'SELECT MAX(NAME) VIEWBY, MAX(NAME) IBE_ATTR1,'||
1325 ' decode(SUM(NVL(CT_LIST,0)),0,0,(SUM(NVL(CT_DISC,0))/SUM(CT_LIST)))*100 IBE_VAL2,'||
1326 ' decode(SUM(NVL(CA_LIST,0)),0,0,(SUM(NVL(CA_DISC,0))/SUM(CA_LIST)))*100 IBE_VAL4,'||
1327 ' decode(SUM(NVL(CU_LIST,0)),0,0,(SUM(NVL(CU_DISC,0))/SUM(CU_LIST)))*100 IBE_VAL6,'||
1328 ' decode(SUM(NVL(PT_LIST,0)),0,0,(SUM(NVL(PT_DISC,0))/SUM(PT_LIST)))*100 IBE_VAL1,'||
1329 ' decode(SUM(NVL(PA_LIST,0)),0,0,(SUM(NVL(PA_DISC,0))/SUM(PA_LIST)))*100 IBE_VAL3,'||
1330 ' decode(SUM(NVL(PU_LIST,0)),0,0,(SUM(NVL(PU_DISC,0))/SUM(PU_LIST)))*100 IBE_VAL5'||
1331 ' FROM ('||
1332 ' SELECT'||
1333 ' (CASE WHEN fact.start_date < :l_mid_start'||
1334 ' THEN null else fact.name end) NAME,'||
1335 ' (CASE WHEN fact.start_date < :l_mid_start'||
1336 ' THEN fact.year445_id+1 else fact.year445_id end) ENT_YEAR_ID,'||
1337 ' (CASE WHEN fact.start_date < :l_mid_start'||
1338 ' THEN FACT.DISC_TOTAL else 0 end) PT_DISC,'||
1339 ' (CASE WHEN fact.start_date < :l_mid_start'||
1340 ' THEN FACT.LIST_TOTAL else 0 end) PT_LIST,'||
1341 ' (CASE WHEN fact.start_date >= :l_mid_start'||
1342 ' THEN FACT.DISC_TOTAL else 0 end) CT_DISC,'||
1343 ' (CASE WHEN fact.start_date >= :l_mid_start'||
1344 ' THEN FACT.LIST_TOTAL else 0 end) CT_LIST,'||
1345 ' (CASE WHEN fact.start_date < :l_mid_start'||
1346 ' THEN FACT.DISC_ASSIST else 0 end) PA_DISC,'||
1347 ' (CASE WHEN fact.start_date < :l_mid_start'||
1348 ' THEN FACT.LIST_ASSIST else 0 end) PA_LIST,'||
1349 ' (CASE WHEN fact.start_date >= :l_mid_start'||
1350 ' THEN FACT.DISC_ASSIST else 0 end) CA_DISC,'||
1351 ' (CASE WHEN fact.start_date >= :l_mid_start'||
1352 ' THEN FACT.LIST_ASSIST else 0 end) CA_LIST,'||
1353 ' (CASE WHEN fact.start_date < :l_mid_start'||
1354 ' THEN FACT.DISC_UNASSIST else 0 end) PU_DISC,'||
1355 ' (CASE WHEN fact.start_date < :l_mid_start'||
1356 ' THEN FACT.LIST_UNASSIST else 0 end) PU_LIST,'||
1357 ' (CASE WHEN fact.start_date >= :l_mid_start'||
1358 ' THEN FACT.DISC_UNASSIST else 0 end) CU_DISC,'||
1359 ' (CASE WHEN fact.start_date >= :l_mid_start'||
1360 ' THEN FACT.LIST_UNASSIST else 0 end) CU_LIST,'||
1361 ' FACT.REPORT_DATE,'||
1362 ' FACT.SEQUENCE'||
1363 ' FROM ('||
1364 ' SELECT'||l_allSelect ||
1365 ' NVL(MEASURE_TYPE,''NULL'') MEASURE_TYPE,'||
1369 ' decode(:l_c_d,:l_g_p,LIST_AMOUNT_G,:l_g_s,LIST_AMOUNT_G1,CURRENCY_CD_F,LIST_AMOUNT_F),0) LIST_TOTAL,'||
1366 ' decode(MEASURE_TYPE,''IBE_ORD_TOTAL'','||
1367 ' decode(:l_c_d,:l_g_p,DISC_AMOUNT_G,:l_g_s,DISC_AMOUNT_G1,CURRENCY_CD_F,DISC_AMOUNT_F),0) DISC_TOTAL,'||
1368 ' decode(MEASURE_TYPE,''IBE_ORD_TOTAL'','||
1370 ' decode(MEASURE_TYPE,''IBE_ORD_ASSISTED'','||
1371 ' decode(:l_c_d,:l_g_p,DISC_AMOUNT_G,:l_g_s,DISC_AMOUNT_G1,CURRENCY_CD_F,DISC_AMOUNT_F),0) DISC_ASSIST,'||
1372 ' decode(MEASURE_TYPE,''IBE_ORD_ASSISTED'','||
1373 ' decode(:l_c_d,:l_g_p,LIST_AMOUNT_G,:l_g_s,LIST_AMOUNT_G1,CURRENCY_CD_F,LIST_AMOUNT_F),0) LIST_ASSIST,'||
1374 ' decode(MEASURE_TYPE,''IBE_ORD_UNASSISTED'','||
1375 ' decode(:l_c_d,:l_g_p,DISC_AMOUNT_G,:l_g_s,DISC_AMOUNT_G1,CURRENCY_CD_F,DISC_AMOUNT_F),0) DISC_UNASSIST,'||
1376 ' decode(MEASURE_TYPE,''IBE_ORD_UNASSISTED'','||
1377 ' decode(:l_c_d,:l_g_p,LIST_AMOUNT_G,:l_g_s,LIST_AMOUNT_G1,CURRENCY_CD_F,LIST_AMOUNT_F),0) LIST_UNASSIST,'||
1378 ' TIME.PERIOD_TYPE_ID,'||
1379 ' TIME.TIME_ID,TIME.SEQUENCE,TIME.START_DATE,'||
1380 ' TIME.YEAR445_ID,TIME.NAME,TIME.REPORT_DATE'||
1381 ' FROM'||
1382 ' ( SELECT '||
1383 ' TIME_PERIOD.NAME,'||
1384 ' CAL.REPORT_DATE,'||
1385 ' CAL.PERIOD_TYPE_ID,'||
1386 ' CAL.TIME_ID, '||
1387 ' TIME_PERIOD.SEQUENCE,'||
1388 ' TIME_PERIOD.START_DATE,'||
1389 ' TIME.year445_id'||
1390 ' FROM '||
1391 ' FII_TIME_RPT_STRUCT_V CAL,'||
1392 ' FII_TIME_WEEK TIME_PERIOD,'||
1393 ' FII_TIME_p445 TIME'||
1394 ' WHERE '||
1395 ' BITAND(CAL.RECORD_TYPE_ID, :l_record_type_id) = CAL.RECORD_TYPE_ID'||
1396 ' AND CAL.Report_Date = LEAST(TIME_PERIOD.END_date,&BIS_CURRENT_ASOF_DATE)'||
1397 ' AND (TIME_PERIOD.start_date between :l_pprev_start and :l_pcur_start OR'||
1398 ' TIME_PERIOD.start_date between :l_prev_start and :l_cur_start)'||
1399 ' AND TIME.period445_id = TIME_PERIOD.period445_id ) TIME,'||
1400 l_tableList||
1401 ' WHERE '||
1402 ' FACT.TIME_ID (+)= TIME.TIME_ID'||
1403 ' AND FACT.PERIOD_TYPE_ID(+)= TIME.PERIOD_TYPE_ID'||
1404 l_msiteFilter||' ) FACT'||
1405 ' WHERE'||
1406 ' FACT.MEASURE_TYPE IN (''IBE_ORD_TOTAL'',''IBE_ORD_ASSISTED'',''IBE_ORD_UNASSISTED'',''NULL'')'||l_allWhere ||
1407 ' ) GROUP BY ENT_YEAR_ID,SEQUENCE'||
1408 ' ORDER BY ENT_YEAR_ID,SEQUENCE';
1409
1410
1411 ELSE
1412
1413 /*************************************************************************/
1414 /* The following Query would work when the Time Period is anything other */
1415 /* than WEEK and the Comparison type is Year-To-Year. */
1416 /*************************************************************************/
1417
1418 /*************************************************************************/
1419 /* VIEWBY : Period Name */
1420 /* IBE_ATTR1 : Period Name */
1421 /* IBE_VAL1 : Previous Total */
1422 /* IBE_VAL2 : Current Total */
1423 /* IBE_VAL3 : Previous Assisted */
1424 /* IBE_VAL4 : Current Assisted */
1425 /* IBE_VAL5 : Previous Unassisted */
1426 /* IBE_VAL6 : Current Unassisted */
1427 /*************************************************************************/
1428
1429 l_custom_sql := 'SELECT MAX(NAME) VIEWBY, MAX(NAME) IBE_ATTR1,'||
1430 ' decode(SUM(NVL(CT_LIST,0)),0,0,(SUM(NVL(CT_DISC,0))/SUM(CT_LIST)))*100 IBE_VAL2,'||
1431 ' decode(SUM(NVL(CA_LIST,0)),0,0,(SUM(NVL(CA_DISC,0))/SUM(CA_LIST)))*100 IBE_VAL4,'||
1432 ' decode(SUM(NVL(CU_LIST,0)),0,0,(SUM(NVL(CU_DISC,0))/SUM(CU_LIST)))*100 IBE_VAL6,'||
1433 ' decode(SUM(NVL(PT_LIST,0)),0,0,(SUM(NVL(PT_DISC,0))/SUM(PT_LIST)))*100 IBE_VAL1,'||
1434 ' decode(SUM(NVL(PA_LIST,0)),0,0,(SUM(NVL(PA_DISC,0))/SUM(PA_LIST)))*100 IBE_VAL3,'||
1435 ' decode(SUM(NVL(PU_LIST,0)),0,0,(SUM(NVL(PU_DISC,0))/SUM(PU_LIST)))*100 IBE_VAL5'||
1436 ' FROM ('||
1437 ' SELECT'||
1438 ' (CASE WHEN fact.start_date <= :l_mid_start'||
1439 ' THEN null else fact.name end) NAME,'||
1440 ' (CASE WHEN fact.start_date <= :l_mid_start'||
1441 ' THEN fact.ent_year_id+1 else fact.ent_year_id end) ENT_YEAR_ID,'||
1442 ' (CASE WHEN fact.start_date <= :l_mid_start'||
1443 ' THEN FACT.DISC_TOTAL else 0 end) PT_DISC,'||
1444 ' (CASE WHEN fact.start_date <= :l_mid_start'||
1445 ' THEN FACT.LIST_TOTAL else 0 end) PT_LIST,'||
1446 ' (CASE WHEN fact.start_date > :l_mid_start'||
1447 ' THEN FACT.DISC_TOTAL else 0 end) CT_DISC,'||
1448 ' (CASE WHEN fact.start_date > :l_mid_start'||
1449 ' THEN FACT.LIST_TOTAL else 0 end) CT_LIST,'||
1450 ' (CASE WHEN fact.start_date <= :l_mid_start'||
1451 ' THEN FACT.DISC_ASSIST else 0 end) PA_DISC,'||
1452 ' (CASE WHEN fact.start_date <= :l_mid_start'||
1453 ' THEN FACT.LIST_ASSIST else 0 end) PA_LIST,'||
1454 ' (CASE WHEN fact.start_date > :l_mid_start'||
1455 ' THEN FACT.DISC_ASSIST else 0 end) CA_DISC,'||
1456 ' (CASE WHEN fact.start_date > :l_mid_start'||
1457 ' THEN FACT.LIST_ASSIST else 0 end) CA_LIST,'||
1458 ' (CASE WHEN fact.start_date <= :l_mid_start'||
1459 ' THEN FACT.DISC_UNASSIST else 0 end) PU_DISC,'||
1460 ' (CASE WHEN fact.start_date <= :l_mid_start'||
1461 ' THEN FACT.LIST_UNASSIST else 0 end) PU_LIST,'||
1462 ' (CASE WHEN fact.start_date > :l_mid_start'||
1463 ' THEN FACT.DISC_UNASSIST else 0 end) CU_DISC,'||
1464 ' (CASE WHEN fact.start_date > :l_mid_start'||
1465 ' THEN FACT.LIST_UNASSIST else 0 end) CU_LIST,'||
1466 ' fact.REPORT_DATE,'||
1467 ' fact.SEQUENCE'||
1468 ' FROM ('||
1469 ' SELECT'|| l_allSelect ||
1470 ' NVL(MEASURE_TYPE,''NULL'') MEASURE_TYPE,'||
1471 ' decode(MEASURE_TYPE,''IBE_ORD_TOTAL'','||
1472 ' decode(:l_c_d,:l_g_p,DISC_AMOUNT_G,:l_g_s,DISC_AMOUNT_G1,currency_cd_f,DISC_AMOUNT_F),0) DISC_TOTAL,'||
1473 ' decode(MEASURE_TYPE,''IBE_ORD_TOTAL'','||
1474 ' decode(:l_c_d,:l_g_p,LIST_AMOUNT_G,:l_g_s,LIST_AMOUNT_G1,currency_cd_f,LIST_AMOUNT_F),0) LIST_TOTAL,'||
1475 ' decode(MEASURE_TYPE,''IBE_ORD_ASSISTED'','||
1476 ' decode(:l_c_d,:l_g_p,DISC_AMOUNT_G,:l_g_s,DISC_AMOUNT_G1,currency_cd_f,DISC_AMOUNT_F),0) DISC_ASSIST,'||
1477 ' decode(MEASURE_TYPE,''IBE_ORD_ASSISTED'','||
1478 ' decode(:l_c_d,:l_g_p,LIST_AMOUNT_G,:l_g_s,LIST_AMOUNT_G1,currency_cd_f,LIST_AMOUNT_F),0) LIST_ASSIST,'||
1479 ' decode(MEASURE_TYPE,''IBE_ORD_UNASSISTED'','||
1480 ' decode(:l_c_d,:l_g_p,list_amount_g,:l_g_s,LIST_AMOUNT_G1,currency_cd_f,LIST_AMOUNT_F),0) LIST_UNASSIST,'||
1481 ' decode(MEASURE_TYPE,''IBE_ORD_UNASSISTED'','||
1482 ' decode(:l_c_d,:l_g_p,DISC_AMOUNT_G,:l_g_s,DISC_AMOUNT_G1,currency_cd_f,DISC_AMOUNT_F),0) DISC_UNASSIST,'||
1483 ' TIME.PERIOD_TYPE_ID,'||
1484 ' TIME.TIME_ID,TIME.SEQUENCE,TIME.START_DATE,'||
1485 ' TIME.ENT_YEAR_ID,TIME.NAME,TIME.REPORT_DATE'||
1486 ' FROM'||
1487 ' (SELECT'||
1488 ' TIME_PERIOD.NAME,'||
1489 ' CAL.REPORT_DATE,'||
1490 ' CAL.PERIOD_TYPE_ID,'||
1491 ' CAL.TIME_ID,'||
1492 ' TIME_PERIOD.SEQUENCE,'||
1493 ' TIME_PERIOD.START_DATE,'||
1494 ' TIME_PERIOD.ENT_YEAR_ID'||
1495 ' FROM'||
1496 ' FII_TIME_RPT_STRUCT_V CAL,'||l_timetable||' TIME_PERIOD'||
1497 ' WHERE BITAND(CAL.RECORD_TYPE_ID, :l_record_type_id) = CAL.RECORD_TYPE_ID'||
1498 ' AND BITAND(CAL.RECORD_TYPE_ID, :l_record_type_id) = CAL.RECORD_TYPE_ID'||
1499 ' AND CAL.Report_Date = LEAST(TIME_PERIOD.END_date,&BIS_CURRENT_ASOF_DATE) '|| l_whereclause|| ') TIME,'||
1500 l_tableList||
1501 ' WHERE'||
1502 ' FACT.TIME_ID (+)= TIME.TIME_ID'||
1503 ' AND FACT.PERIOD_TYPE_ID (+)= TIME.PERIOD_TYPE_ID'||
1504 l_msiteFilter ||') FACT'||
1505 ' WHERE '||
1506 ' FACT.MEASURE_TYPE IN (''IBE_ORD_TOTAL'',''IBE_ORD_ASSISTED'',''IBE_ORD_UNASSISTED'',''NULL'')'||l_allWhere ||
1507 ' ) GROUP BY ENT_YEAR_ID,SEQUENCE'||
1508 ' ORDER BY ENT_YEAR_ID, SEQUENCE';
1509
1510 END IF;
1511
1512 END IF;
1513
1514 x_custom_sql := l_custom_sql;
1515
1516
1517
1518 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1519 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,'ibe.plsql.dbi.ibe_bi_ord_graph_pvt.get_avg_disc_graph_sql',l_custom_sql);
1520 END IF;
1521
1522 x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
1523 x_custom_output.EXTEND(13);
1524
1525 l_custom_rec.attribute_name := ':l_sequence';
1526 l_custom_rec.attribute_value := l_sequence;
1527 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
1528 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.NUMERIC_BIND;
1529
1530 x_custom_output(1) := l_custom_rec;
1531
1532 l_custom_rec.attribute_name := ':l_record_type_id' ;
1533 l_custom_rec.attribute_value:= l_record_type_id;
1534 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
1535 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
1536
1537 x_custom_output(2) := l_custom_rec;
1538
1539 l_custom_rec.attribute_name := ':l_prev_start' ;
1540 l_custom_rec.attribute_value:= to_char(l_prev_start,'dd/mm/yyyy');
1541 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
1542 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.DATE_BIND;
1543
1544 x_custom_output(3) := l_custom_rec;
1545
1546 l_custom_rec.attribute_name := ':l_cur_start' ;
1547 l_custom_rec.attribute_value:= to_char(l_cur_start,'dd/mm/yyyy');
1548 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
1549 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.DATE_BIND;
1550
1551 x_custom_output(4) := l_custom_rec;
1552
1553 l_custom_rec.attribute_name := ':l_prev_year' ;
1554 l_custom_rec.attribute_value:= l_prev_year;
1555 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
1556 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
1557
1558 x_custom_output(5) := l_custom_rec;
1559
1560 l_custom_rec.attribute_name := ':l_cur_year' ;
1561 l_custom_rec.attribute_value:= l_cur_year;
1562 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
1563 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
1564
1565 x_custom_output(6) := l_custom_rec;
1566
1567 l_custom_rec.attribute_name := ':l_timespan' ;
1568 l_custom_rec.attribute_value:= l_timespan;
1569 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
1570 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
1571
1572 x_custom_output(7) := l_custom_rec;
1573
1574
1575 l_custom_rec.attribute_name := ':l_mid_start' ;
1576 l_custom_rec.attribute_value:= to_char(l_mid_start,'dd/mm/yyyy');
1577 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
1578 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.DATE_BIND;
1579
1580 x_custom_output(8) := l_custom_rec;
1581
1582 l_custom_rec.attribute_name := ':l_pprev_start' ;
1583 l_custom_rec.attribute_value:= to_char(l_pprev_start,'dd/mm/yyyy');
1584 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
1585 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.DATE_BIND;
1586
1587 x_custom_output(9) := l_custom_rec;
1588
1589
1590 l_custom_rec.attribute_name := ':l_pcur_start' ;
1591 l_custom_rec.attribute_value:= to_char(l_pcur_start,'dd/mm/yyyy');
1592 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
1593 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.DATE_BIND;
1594
1595 x_custom_output(10) := l_custom_rec;
1596
1597 l_custom_rec.attribute_name := ':l_c_d' ;
1598 l_custom_rec.attribute_value:= l_c_d;
1599 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
1600 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
1601
1602 x_custom_output(11) := l_custom_rec;
1603
1604 l_custom_rec.attribute_name := ':l_g_p' ;
1605 l_custom_rec.attribute_value:= l_g_p;
1606 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
1607 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
1608
1609 x_custom_output(12) := l_custom_rec;
1610
1611 l_custom_rec.attribute_name := ':l_g_s' ;
1612 l_custom_rec.attribute_value:= l_g_s;
1613 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
1614 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
1615
1616 x_custom_output(13) := l_custom_rec;
1617
1618
1619 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1620 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,'ibe.plsql.dbi.ibe_bi_ord_graph_pvt.get_avg_disc_graph_sql.end','END');
1621 END IF;
1622
1623
1624 END GET_AVG_DISC_GRAPH_SQL;
1625
1626 END IBE_BI_ORD_GRAPH_PVT;