[Home] [Help]
PACKAGE BODY: APPS.IBE_BI_TOP_ACT_PVT
Source
1 PACKAGE BODY IBE_BI_TOP_ACT_PVT AS
2 /* $Header: IBEVBITOPACTB.pls 120.7 2006/03/10 05:18:43 pakrishn ship $ */
3
4 PROCEDURE GET_TOP_ORDERS_SQL(
5 p_pmv_parameters IN BIS_PMV_PAGE_PARAMETER_tbl,
6 x_custom_sql OUT NOCOPY VARCHAR2,
7 x_custom_output OUT NOCOPY bis_query_attributes_TBL ) IS
8 CURSOR FND_CURSOR IS
9 SELECT LOOKUP_TYPE,LOOKUP_CODE, MEANING
10 FROM FND_LOOKUPS
11 WHERE LOOKUP_TYPE IN ('YES_NO', 'IBE_BI_GENERIC')
12 ORDER BY LOOKUP_TYPE, LOOKUP_CODE;
13
14 l_custom_sql VARCHAR2(4000);
15 l_parameter_name VARCHAR2(3200);
16 l_asof_date DATE;
17 l_prev_date DATE;
18 l_record_type_id NUMBER;
19 l_period_type VARCHAR2(3200);
20 l_comparison_type VARCHAR2(3200);
21 l_currency_code VARCHAR2(3200);
22 l_minisite VARCHAR2(3200);
23 l_minisite_id VARCHAR2(3200);
24 l_msiteFilter VARCHAR2(1000);
25 l_tableList VARCHAR2(1000);
26 l_QuoteLabel VARCHAR2(300);
27 l_CartLabel VARCHAR2(300);
28 l_YesLabel VARCHAR2(300);
29 l_NoLabel VARCHAR2(300);
30 l_OrderBy VARCHAR2(2000);
31 l_custom_rec BIS_QUERY_ATTRIBUTES;
32 l_fnd_rec FND_CURSOR%ROWTYPE;
33 dbg_msg VARCHAR2(3200);
34
35 l_global_primary VARCHAR2(15);
36 l_global_secondary VARCHAR2(15) ;
37 BEGIN
38
39 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
40 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,'ibe.plsql.dbi.ibe_bi_top_act_pvt.get_top_orders_sql.begin','BEGIN');
41 END IF;
42
43 -- initilization of variables
44
45 l_custom_rec := BIS_PMV_PARAMETERS_PUB.Initialize_Query_Type;
46 l_global_primary := '''FII_GLOBAL1''';
47 l_global_secondary := '''FII_GLOBAL2''';
48
49 FOR i IN p_pmv_parameters.FIRST..p_pmv_parameters.LAST
50 LOOP
51 l_parameter_name := p_pmv_parameters(i).parameter_name ;
52
53 IF( l_parameter_name = 'AS_OF_DATE')
54 THEN
55 l_asof_date :=
56 TO_DATE(p_pmv_parameters(i).parameter_value,'DD/MM/YYYY');
57 ELSIF( l_parameter_name = 'IBW_WEB_ANALYTICS_GROUP1+FII_CURRENCIES')
58 THEN
59 l_currency_code := p_pmv_parameters(i).parameter_id;
60 ELSIF( l_parameter_name = 'PERIOD_TYPE')
61 THEN
62 l_period_type := p_pmv_parameters(i).parameter_value;
63 ELSIF( l_parameter_name = 'TIME_COMPARISON_TYPE')
64 THEN
65 l_comparison_type := p_pmv_parameters(i).parameter_value;
66 ELSIF( l_parameter_name = 'SITE+SITE')
67 THEN
68 l_minisite := p_pmv_parameters(i).parameter_value;
69 l_minisite_id := p_pmv_parameters(i).parameter_id;
70 ELSIF ( l_parameter_name = 'ORDERBY')
71 THEN
72 l_OrderBy := p_pmv_parameters(i).parameter_value;
73 END IF;
74 END LOOP;
75
76 FOR l_fnd_rec IN fnd_cursor LOOP
77
78 IF l_fnd_rec.lookup_type = 'IBE_BI_GENERIC' AND l_fnd_rec.lookup_code ='QUOTE' THEN
79 l_QuoteLabel := l_fnd_rec.meaning;
80 ELSIF l_fnd_rec.lookup_type = 'IBE_BI_GENERIC' AND l_fnd_rec.lookup_code ='CART' THEN
81 l_CartLabel := l_fnd_rec.meaning;
82 ELSIF l_fnd_rec.lookup_type = 'YES_NO' AND l_fnd_rec.lookup_code ='Y' THEN
83 l_YesLabel := l_fnd_rec.meaning;
84 ELSIF l_fnd_rec.lookup_type = 'YES_NO' AND l_fnd_rec.lookup_code ='N' THEN
85 l_NoLabel := l_fnd_rec.meaning;
86 END IF;
87
88 END LOOP;
89
90
91 l_record_type_id := IBE_BI_PMV_UTIL_PVT.GET_RECORD_TYPE_ID(l_period_type);
92
93
94 l_prev_date := IBE_BI_PMV_UTIL_PVT.Get_Prev_Date(
95 p_asof_date => l_asof_date,
96 p_period_type => l_period_type,
97 p_comparison_type => l_comparison_type );
98
99 dbg_msg := 'AS_OF_DATE:'||l_asof_date||','||'CURR_CODE:'||l_currency_code||
100 ','||'PERIOD_TYPE:'||l_period_type||','||'COMPARISION_TYPE:'||
101 l_comparison_type||','||'MINISITE:'||l_minisite||','||'MINISITE_ID:'||
102 l_minisite_id||','||'ORDER_BY:'||l_OrderBy||','||'LOOKUP_TYPE:'||
103 l_fnd_rec.lookup_type||','||'QUOTE_LABEL:'||l_QuoteLabel||','||'CART_LABEL:'||
104 l_CartLabel||','||'YES_LABEL:'||l_YesLabel||','||'NO_LABEL:'||l_NoLabel||','||
105 'RECORD_TYPE_ID:'||l_record_type_id||','||'PREV_DATE:'||l_prev_date;
106 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
107 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,'ibe.plsql.dbi.ibe_bi_top_act_pvt.get_top_orders_sql.parameters',dbg_msg);
108 END IF;
109
110 --Change in View done for Bug#:4654974. Issue#12 by narao
111 --The Site Dimension now refers to IBW_BI_MSITE_DIMN_V
112 --And hence the MSITES View is changed from IBE_BI_MSITES_V to IBW_BI_MSITE_DIMN_V.
113
114 if (trim(l_minisite_id) is null) then
115 l_tableList := ' IBE_BI_TOP_ORD_MV FACT, IBE_BI_ORDERS_MV ORD, '||
116 -- ' IBE_BI_MSITES_V MSITES, '||
117 ' IBW_BI_MSITE_DIMN_V MSITES, '||
118 ' FII_TIME_RPT_STRUCT_V CAL';
119
120 l_msiteFilter :=' AND MSITES.ID = FACT.MINISITE_ID';
121
122 else
123 l_tableList := ' IBE_BI_TOP_ORD_MV FACT, IBE_BI_ORDERS_MV ORD, '||
124 ' FII_TIME_RPT_STRUCT_V CAL';
125
126 l_msiteFilter := ' AND FACT.MINISITE_ID in (&SITE+SITE)';
127
128 end if;
129
130 /************************************************************************/
131 /* IBE_VAL1 : Order Number */
132 /* IBE_VAL5 : Header Id */
133 /* IBE_ATTR1 : Customer Name */
134 /* IBE_ATTR2 : Order Source */
135 /* IBE_VAL2 : Booked Amount */
136 /* IBE_VAL3 : Discount Percentage */
137 /* IBE_VAL4 : Number of Lines */
138 /* IBE_ATTR3 : Assisted/Unassisted */
139 /************************************************************************/
140
141 --Changed the Position of IBE_VAL5 as per the Region Item Definition
142 --For Bug#:4654974-Issue#6.
143 l_custom_sql := 'SELECT IBE_VAL1, IBE_VAL5, IBE_ATTR1, IBE_ATTR2, IBE_VAL2, IBE_VAL3,'||
144 ' IBE_VAL4, IBE_ATTR3 FROM ('||
145 ' SELECT '||
146 ' ORDERNUMBER IBE_VAL1, '||
147 ' CUST.VALUE IBE_ATTR1, '||
148 ' DECODE(SOURCE,''Y'',:l_QuoteLabel,:l_CartLabel) IBE_ATTR2, '||
149 ' BOOKEDAMOUNT IBE_VAL2, '||
150 ' DISCOUNT IBE_VAL3, '||
151 ' LINES IBE_VAL4, '||
152 ' DECODE(Assisted,''Y'',:l_YesLabel,:l_NoLabel) IBE_ATTR3, '||
153 ' OHEADER_ID IBE_VAL5 '||
154 ' FROM '||
155 ' ( '||
156 ' SELECT CUSTOMERID,'||
157 ' ORDERNUMBER,'||
158 ' SOURCE,'||
159 ' BOOKEDAMOUNT,'||
160 ' DISCOUNT,'||
161 ' LINES,'||
162 ' ASSISTED,'||
163 ' RANK,'||
164 ' OHEADER_ID'||
165 ' FROM '||
166 ' ( '||
167 ' SELECT FACT.CUSTOMERID,'||
168 ' FACT.ordernumber, '||
169 ' FACT.Source, '||
170 ' decode(:l_currency_code,:l_global_primary,fact.Booked_Amt_G,:l_global_secondary,fact.Booked_amt_G1,fact.currency_cd_f,fact.Booked_Amt_F) BookedAmount, '||
171 ' decode(:l_currency_code,:l_global_primary,fact.Discount_G,:l_global_secondary,fact.Discount_G1,fact.currency_cd_f,fact.Discount_F) Discount, '||
172 ' FACT.Lines, '||
173 ' FACT.Assisted , '||
174 ' RANK() OVER (ORDER BY decode(:l_currency_code,:l_global_primary,fact.Booked_Amt_G,:l_global_secondary,fact.Booked_Amt_G1,fact.currency_cd_f,fact.Booked_Amt_F) '||
175 ' DESC NULLS LAST) RANK, '||
176 ' ORD.HEADER_ID OHEADER_ID '||
177 ' FROM '||l_tableList||
178 ' WHERE '||
179 ' CAL.calendar_id = -1'||
180 ' AND FACT.Time_Id = CAL.Time_Id'||
181 ' AND FACT.ORDERNUMBER = ORD.ORDER_NUMBER '||
182 ' AND FACT.Period_Type_id = CAL.Period_Type_Id'||
183 ' AND REPORT_DATE = (:l_asof_date) '||
184 ' AND BITAND(CAL.RECORD_TYPE_ID, :l_record_type_id) = CAL.RECORD_TYPE_ID '||l_msiteFilter||
185 ' ) FACT'||
186 ' WHERE FACT.RANK <= :l_rank) FACT1,'||
187 ' FII_CUSTOMERS_V CUST'||
188 ' WHERE FACT1.CustomerID = CUST.ID )'||
189 ' &ORDER_BY_CLAUSE ' ; --Bug 5076452
190
191 -- ' ORDER BY '||l_OrderBy;
192
193
194 x_custom_sql := l_custom_sql;
195
196
197 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
198 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,'ibe.plsql.dbi.ibe_bi_top_act_pvt.get_top_orders_sql.query',l_custom_sql);
199 END IF;
200
201 x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
202
203 x_custom_output.Extend(11);
204
205 l_custom_rec.attribute_name := ':l_asof_date' ;
206 l_custom_rec.attribute_value:= to_char(l_asof_date,'dd/mm/yyyy');
207 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
208 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.DATE_BIND;
209
210 x_custom_output(1) := l_custom_rec;
211
212
213 l_custom_rec.attribute_name := ':l_prev_date' ;
214 l_custom_rec.attribute_value:= to_char(l_prev_date,'dd/mm/yyyy');
215 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
216 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.DATE_BIND;
217
218 x_custom_output(2) := l_custom_rec;
219
220 l_custom_rec.attribute_name := ':l_record_type_id' ;
221 l_custom_rec.attribute_value:= l_record_type_id;
222 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
223 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
224
225 x_custom_output(3) := l_custom_rec;
226
227 l_custom_rec.attribute_name := ':l_rank' ;
228 l_custom_rec.attribute_value:= NVL(FND_PROFILE.VALUE('IBE_BI_TOP_ACT_NO_ROWS'),25);
229 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
230 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
231
232 x_custom_output(4) := l_custom_rec;
233
234 l_custom_rec.attribute_name := ':l_currency_code' ;
235 l_custom_rec.attribute_value:= l_currency_code;
236 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
237 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
238
239 x_custom_output(5) := l_custom_rec;
240
241 l_custom_rec.attribute_name := ':l_QuoteLabel' ;
242 l_custom_rec.attribute_value:= l_QuoteLabel;
243 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
244 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
245
246 x_custom_output(6) := l_custom_rec;
247
248 l_custom_rec.attribute_name := ':l_CartLabel' ;
249 l_custom_rec.attribute_value:= l_CartLabel;
250 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
251 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
252
253 x_custom_output(7) := l_custom_rec;
254
255 l_custom_rec.attribute_name := ':l_YesLabel' ;
256 l_custom_rec.attribute_value:= l_YesLabel;
257 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
258 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
259
260 x_custom_output(8) := l_custom_rec;
261
262 l_custom_rec.attribute_name := ':l_NoLabel' ;
263 l_custom_rec.attribute_value:= l_NoLabel;
264 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
265 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
266
267 x_custom_output(9) := l_custom_rec;
268
269 l_custom_rec.attribute_name := ':l_global_primary' ;
270 l_custom_rec.attribute_value:= l_global_primary;
271 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
272 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
273
274 x_custom_output(10) := l_custom_rec;
275
276 l_custom_rec.attribute_name := ':l_global_secondary' ;
277 l_custom_rec.attribute_value:= l_global_secondary;
278 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
279 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
280
281 x_custom_output(11) := l_custom_rec;
282
283 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
284 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,'ibe.plsql.dbi.ibe_bi_top_act_pvt.get_top_orders_sql.end','END');
285 END IF;
286
287 END GET_TOP_ORDERS_SQL;
288
289
290 PROCEDURE GET_TOP_CARTS_SQL(
291 p_pmv_parameters IN BIS_PMV_PAGE_PARAMETER_tbl,
292 x_custom_sql OUT NOCOPY VARCHAR2,
293 x_custom_output OUT NOCOPY bis_query_attributes_TBL ) IS
294 CURSOR FND_CURSOR IS
295 SELECT LOOKUP_TYPE,LOOKUP_CODE, MEANING
296 FROM FND_LOOKUPS
297 WHERE LOOKUP_TYPE IN ('YES_NO', 'IBE_BI_GENERIC')
298 ORDER BY LOOKUP_TYPE, LOOKUP_CODE;
299
300 l_custom_sql VARCHAR2(4000);
301 l_parameter_name VARCHAR2(3200);
302 l_asof_date DATE;
303 l_prev_date DATE;
304 l_record_type_id NUMBER;
305 l_period_type VARCHAR2(3200);
306 l_comparison_type VARCHAR2(3200);
307 l_currency_code VARCHAR2(3200);
308 l_minisite VARCHAR2(3200);
309 l_minisite_id VARCHAR2(3200);
310 l_msiteFilter VARCHAR2(1000);
311 l_tableList VARCHAR2(1000);
312 l_YesLabel VARCHAR2(300);
313 l_NoLabel VARCHAR2(300);
314 l_OrderableLabel VARCHAR2(300);
315 l_ExpiredLabel VARCHAR2(300);
316 l_OrderBy VARCHAR2(2000);
317 l_custom_rec BIS_QUERY_ATTRIBUTES ;
318 l_fnd_rec FND_CURSOR%ROWTYPE;
319 dbg_msg VARCHAR2(3200);
320 l_global_primary VARCHAR2(15);
321 l_global_secondary VARCHAR2(15);
322 BEGIN
323
324 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
325 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,'ibe.plsql.dbi.ibe_bi_top_act_pvt.get_top_carts_sql.begin','BEGIN');
326 END IF;
327
328 -- initilization of variables
329
330 l_custom_rec := BIS_PMV_PARAMETERS_PUB.Initialize_Query_Type;
331 l_global_primary := '''FII_GLOBAL1''';
332 l_global_secondary := '''FII_GLOBAL2''';
333
334
335 FOR i IN p_pmv_parameters.FIRST..p_pmv_parameters.LAST
336 LOOP
337 l_parameter_name := p_pmv_parameters(i).parameter_name ;
338
339 IF( l_parameter_name = 'AS_OF_DATE')
340 THEN
341 l_asof_date :=
342 TO_DATE(p_pmv_parameters(i).parameter_value,'DD/MM/YYYY');
343 ELSIF( l_parameter_name = 'IBW_WEB_ANALYTICS_GROUP1+FII_CURRENCIES')
344 THEN
345 l_currency_code := p_pmv_parameters(i).parameter_id;
346 ELSIF( l_parameter_name = 'PERIOD_TYPE')
347 THEN
348 l_period_type := p_pmv_parameters(i).parameter_value;
349 ELSIF( l_parameter_name = 'TIME_COMPARISON_TYPE')
350 THEN
351 l_comparison_type := p_pmv_parameters(i).parameter_value;
352 ELSIF( l_parameter_name = 'SITE+SITE')
353 THEN
354 l_minisite := p_pmv_parameters(i).parameter_value;
355 l_minisite_id := p_pmv_parameters(i).parameter_id;
356 ELSIF ( l_parameter_name = 'ORDERBY')
357 THEN
358 l_OrderBy := p_pmv_parameters(i).parameter_value;
359 END IF;
360 END LOOP;
361
362 FOR l_fnd_rec IN fnd_cursor LOOP
363
364 IF l_fnd_rec.lookup_type = 'IBE_BI_GENERIC' AND l_fnd_rec.lookup_code ='ORDERABLE' THEN
365 l_OrderableLabel := l_fnd_rec.meaning;
366 ELSIF l_fnd_rec.lookup_type = 'IBE_BI_GENERIC' AND l_fnd_rec.lookup_code ='EXPIRED' THEN
367 l_ExpiredLabel := l_fnd_rec.meaning;
368 ELSIF l_fnd_rec.lookup_type = 'YES_NO' AND l_fnd_rec.lookup_code ='Y' THEN
369 l_YesLabel := l_fnd_rec.meaning;
370 ELSIF l_fnd_rec.lookup_type = 'YES_NO' AND l_fnd_rec.lookup_code ='N' THEN
371 l_NoLabel := l_fnd_rec.meaning;
372 END IF;
373
374 END LOOP;
375
376
377
378 l_record_type_id := IBE_BI_PMV_UTIL_PVT.GET_RECORD_TYPE_ID(l_period_type);
379
380
381 l_prev_date := IBE_BI_PMV_UTIL_PVT.Get_Prev_Date(
382 p_asof_date => l_asof_date,
383 p_period_type => l_period_type,
384 p_comparison_type => l_comparison_type );
385
386
387 dbg_msg := 'AS_OF_DATE:'||l_asof_date||','||'CURR_CODE:'||l_currency_code||
388 ','||'PERIOD_TYPE:'||l_period_type||','||'COMPARISION_TYPE:'||
389 l_comparison_type||','||'MINISITE:'||l_minisite||','||'MINISITE_ID:'||
390 l_minisite_id||','||'ORDER_BY:'||l_OrderBy||','||'LOOKUP_TYPE:'||
391 l_fnd_rec.lookup_type||','||'ORDERABLE_LABEL:'||l_OrderableLabel||','||
392 'EXPIRED_LABEL:'||l_ExpiredLabel||','||'YES_LABEL:'||l_YesLabel||','||
393 'NO_LABEL:'||l_NoLabel||','||'RECORD_TYPE_ID:'||l_record_type_id||','||
394 'PREV_DATE:'||l_prev_date;
395
396 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
397 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,'ibe.plsql.dbi.ibe_bi_top_act_pvt.get_top_carts_sql.parameters',dbg_msg);
398 END IF;
399
400 --Change in View done for Bug#:4654974. Issue#12 by narao
401 --The Site Dimension now refers to IBW_BI_MSITE_DIMN_V
402 --And hence the MSITES View is changed from IBE_BI_MSITES_V to IBW_BI_MSITE_DIMN_V.
403
404 if (trim(l_minisite_id) is null) then
405 l_tableList := ' IBE_BI_TOP_CART_MV FACT, '||
406 -- ' IBE_BI_MSITES_V MSITES, '||
407 ' IBW_BI_MSITE_DIMN_V MSITES, '||
408 ' FII_TIME_RPT_STRUCT_V CAL';
409
410 l_msiteFilter :=' AND MSITES.ID = FACT.MINISITE_ID';
411
412 else
413 l_tableList := ' IBE_BI_TOP_CART_MV FACT,'||
414 ' FII_TIME_RPT_STRUCT_V CAL';
415
416 l_msiteFilter := ' AND FACT.MINISITE_ID in (&SITE+SITE)';
417
418 end if;
419
420 /************************************************************************/
421 /* IBE_VAL1 : Cart Number */
422 /* IBE_ATTR1 : Customer Name */
423 /* IBE_VAL2 : Cart Amount */
424 /* IBE_VAL3 : Number of Lines */
425 /* IBE_VAL4 : Age in Days */
426 /* IBE_ATTR3 : Assisted/Unassisted */
427 /************************************************************************/
428
429 l_custom_sql := 'SELECT IBE_VAL1, IBE_ATTR1, IBE_VAL2, IBE_VAL3,'||
430 ' IBE_ATTR2, IBE_VAL4, IBE_ATTR3 FROM ( '||
431 ' SELECT'||
432 ' CARTNUMBER IBE_VAL1,'||
433 ' CUST.VALUE IBE_ATTR1,'||
434 ' AMOUNT IBE_VAL2,'||
435 ' LINES IBE_VAL3,'||
436 ' STATUS IBE_ATTR2,'||
437 ' AGEINDAYS IBE_VAL4,'||
438 ' DECODE(ASSISTED,''Y'',:l_YesLabel,''N'',:l_NoLabel) IBE_ATTR3'||
439 ' FROM'||
440 ' ('||
441 ' SELECT CARTNUMBER,'||
442 ' CUSTOMER,'||
443 ' AMOUNT,'||
444 ' LINES,'||
445 ' STATUS,'||
446 ' AGEINDAYS,'||
447 ' ASSISTED,'||
448 ' RANK'||
449 ' FROM '||
450 ' ('||
451 ' SELECT '||
452 ' CARTNUMBER,'||
453 ' CUSTOMER, '||
454 ' decode(:l_currency_code,:l_global_primary,FACT.BOOKED_AMT_G,:l_global_secondary,FACT.BOOKED_AMT_G1,FACT.currency_cd_f, FACT.BOOKED_AMT_F) AMOUNT,'||
455 ' LINES,'||
456 ' decode(sign(FACT.QUOTE_EXPIRATION_DATE - trunc(SYSDATE)),-1,:l_ExpiredLabel,:l_OrderableLabel) Status,'||
457 ' decode(sign(FACT.QUOTE_EXPIRATION_DATE - trunc(SYSDATE)),-1,FACT.QUOTE_EXPIRATION_DATE-FACT.CREATION_DATE,'||
458 ' trunc(SYSDATE)-FACT.CREATION_DATE) AgeinDays,'||
459 ' FACT.RESOURCE_FLAG Assisted,'||
460 ' RANK() OVER (ORDER BY BOOKED_AMT_G DESC NULLS LAST) RANK'||
461 ' FROM '||l_tableList||
462 ' WHERE '||
463 ' CAL.calendar_id = -1'||
464 ' AND FACT.Time_Id = CAL.Time_Id'||
465 ' AND FACT.Period_Type_id = CAL.Period_Type_Id'||
466 ' AND REPORT_DATE = (:l_asof_date) '||
467 ' AND BITAND(CAL.RECORD_TYPE_ID, :l_record_type_id) = CAL.RECORD_TYPE_ID '||l_msiteFilter||
468 ' ) FACT '||
469 ' WHERE RANK <= :l_rank ) FACT1,'||
470 ' FII_CUSTOMERS_V CUST'||
471 ' WHERE FACT1.CUSTOMER = CUST.ID )'||
472 ' &ORDER_BY_CLAUSE ' ; --Bug 5076452
473
474 -- ' ORDER BY '||l_OrderBy;
475
476
477 x_custom_sql := l_custom_sql;
478
479 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
480 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,'ibe.plsql.dbi.ibe_bi_top_act_pvt.get_top_carts_sql.query',l_custom_sql);
481 END IF;
482 x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
483
484 x_custom_output.Extend(11);
485
486 l_custom_rec.attribute_name := ':l_asof_date' ;
487 l_custom_rec.attribute_value:= to_char(l_asof_date,'dd/mm/yyyy');
488 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
489 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.DATE_BIND;
490
491 x_custom_output(1) := l_custom_rec;
492
493
494 l_custom_rec.attribute_name := ':l_prev_date' ;
495 l_custom_rec.attribute_value:= to_char(l_prev_date,'dd/mm/yyyy');
496 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
497 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.DATE_BIND;
498
499 x_custom_output(2) := l_custom_rec;
500
501 l_custom_rec.attribute_name := ':l_record_type_id' ;
502 l_custom_rec.attribute_value:= l_record_type_id;
503 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
504 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
505
506 x_custom_output(3) := l_custom_rec;
507
508 l_custom_rec.attribute_name := ':l_rank' ;
509 l_custom_rec.attribute_value:= NVL(FND_PROFILE.VALUE('IBE_BI_TOP_ACT_NO_ROWS'),25);
510
511 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
512 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
513
514 x_custom_output(4) := l_custom_rec;
515
516 l_custom_rec.attribute_name := ':l_currency_code' ;
517 l_custom_rec.attribute_value:= l_currency_code;
518 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
519 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
520
521 x_custom_output(5) := l_custom_rec;
522
523 l_custom_rec.attribute_name := ':l_ExpiredLabel' ;
524 l_custom_rec.attribute_value:= l_ExpiredLabel;
525 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
526 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
527
528 x_custom_output(6) := l_custom_rec;
529
530 l_custom_rec.attribute_name := ':l_OrderableLabel' ;
531 l_custom_rec.attribute_value:= l_OrderableLabel;
532 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
533 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
534
535 x_custom_output(7) := l_custom_rec;
536
537 l_custom_rec.attribute_name := ':l_YesLabel' ;
538 l_custom_rec.attribute_value:= l_YesLabel;
539 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
540 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
541
542 x_custom_output(8) := l_custom_rec;
543
544 l_custom_rec.attribute_name := ':l_NoLabel' ;
545 l_custom_rec.attribute_value:= l_NoLabel;
546 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
547 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
548
549 x_custom_output(9) := l_custom_rec;
550
551 l_custom_rec.attribute_name := ':l_global_primary' ;
552 l_custom_rec.attribute_value:= l_global_primary;
553 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
554 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
555
556 x_custom_output(10) := l_custom_rec;
557
558 l_custom_rec.attribute_name := ':l_global_secondary' ;
559 l_custom_rec.attribute_value:= l_global_secondary;
560 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
561 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
562
563 x_custom_output(11) := l_custom_rec;
564
565 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
566 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,'ibe.plsql.dbi.ibe_bi_top_act_pvt.get_top_carts_sql.end','END');
567 END IF;
568
569 END GET_TOP_CARTS_SQL;
570
571
572 PROCEDURE GET_TOP_CUSTOMERS_SQL(
573 p_pmv_parameters IN BIS_PMV_PAGE_PARAMETER_tbl,
574 x_custom_sql OUT NOCOPY VARCHAR2,
575 x_custom_output OUT NOCOPY bis_query_attributes_TBL ) IS
576 l_custom_sql VARCHAR2(4000);
577 l_parameter_name VARCHAR2(3200);
578 l_asof_date DATE;
579 l_prev_date DATE;
580 l_record_type_id NUMBER;
581 l_period_type VARCHAR2(3200);
582 l_comparison_type VARCHAR2(3200);
583 l_currency_code VARCHAR2(3200);
584 l_minisite VARCHAR2(3200);
585 l_minisite_id VARCHAR2(3200);
586 l_msiteFilter VARCHAR2(1000);
587 l_tableList VARCHAR2(1000);
588 l_OrderBy VARCHAR2(2000);
589 l_custom_rec BIS_QUERY_ATTRIBUTES;
590 dbg_msg VARCHAR2(3200);
591 l_global_primary VARCHAR2(15) ;
592 l_global_secondary VARCHAR2(15) ;
593 BEGIN
594
595 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
596 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,'ibe.plsql.dbi.ibe_bi_top_act_pvt.get_top_customers_sql.begin','BEGIN');
597 END IF;
598
599 -- initilization of variables
600
601 l_custom_rec := BIS_PMV_PARAMETERS_PUB.Initialize_Query_Type;
602 l_global_primary := '''FII_GLOBAL1''';
603 l_global_secondary := '''FII_GLOBAL2''';
604
605
606 FOR i IN p_pmv_parameters.FIRST..p_pmv_parameters.LAST
607 LOOP
608 l_parameter_name := p_pmv_parameters(i).parameter_name ;
609
610 IF( l_parameter_name = 'AS_OF_DATE')
611 THEN
612 l_asof_date :=
613 TO_DATE(p_pmv_parameters(i).parameter_value,'DD/MM/YYYY');
614 ELSIF( l_parameter_name = 'IBW_WEB_ANALYTICS_GROUP1+FII_CURRENCIES')
615 THEN
616 l_currency_code := p_pmv_parameters(i).parameter_id;
617 ELSIF( l_parameter_name = 'PERIOD_TYPE')
618 THEN
619 l_period_type := p_pmv_parameters(i).parameter_value;
620 ELSIF( l_parameter_name = 'TIME_COMPARISON_TYPE')
621 THEN
622 l_comparison_type := p_pmv_parameters(i).parameter_value;
623 ELSIF( l_parameter_name = 'SITE+SITE')
624 THEN
625 l_minisite := p_pmv_parameters(i).parameter_value;
626 l_minisite_id := p_pmv_parameters(i).parameter_id;
627 ELSIF ( l_parameter_name = 'ORDERBY')
628 THEN
629 l_OrderBy := p_pmv_parameters(i).parameter_value;
630 END IF;
631 END LOOP;
632
633
634 l_record_type_id := IBE_BI_PMV_UTIL_PVT.GET_RECORD_TYPE_ID(l_period_type);
635
636 l_prev_date := IBE_BI_PMV_UTIL_PVT.Get_Prev_Date(
637 p_asof_date => l_asof_date,
638 p_period_type => l_period_type,
639 p_comparison_type => l_comparison_type );
640
641
642 dbg_msg := 'AS_OF_DATE:'||l_asof_date||','||'CURR_CODE:'||l_currency_code||
643 ','||'PERIOD_TYPE:'||l_period_type||','||'COMPARISION_TYPE:'||
644 l_comparison_type||','||'MINISITE:'||l_minisite||','||'MINISITE_ID:'||
645 l_minisite_id||','||'ORDER_BY:'||l_OrderBy||','||'RECORD_TYPE_ID:'||
646 l_record_type_id||','||'PREV_DATE:'||l_prev_date;
647 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
648 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,'ibe.plsql.dbi.ibe_bi_top_act_pvt.get_top_customers_sql.parameters',dbg_msg);
649 END IF;
650
651 --Change in View done for Bug#:4654974. Issue#12 by narao
652 --The Site Dimension now refers to IBW_BI_MSITE_DIMN_V
653 --And hence the MSITES View is changed from IBE_BI_MSITES_V to IBW_BI_MSITE_DIMN_V.
654
655 if (trim(l_minisite_id) is null) then
656 l_tableList := ' IBE_BI_TOP_CUST_MV MV,'||
657 --' IBE_BI_MSITES_V MSITES,'||
658 ' IBW_BI_MSITE_DIMN_V MSITES,'||
659 ' FII_TIME_RPT_STRUCT_V cal';
660
661 l_msiteFilter :=' AND MSITES.ID = MV.MINISITE_ID';
662
663 else
664 l_tableList := ' IBE_BI_TOP_CUST_MV MV,'||
665 ' FII_TIME_RPT_STRUCT_V cal';
666
667 l_msiteFilter := ' AND MV.MINISITE_ID in (&SITE+SITE)';
668
669 end if;
670
671 /************************************************************************/
672 /* IBE_ATTR1 : Customer Name */
673 /* IBE_VAL1 : Booked Amount */
674 /* IBE_VAL2 : Percentage Change(Calculated in the AK Region) */
675 /* IBE_VAL7 : Prior Booked Amount */
676 /* IBE_VAL3 : Assisted/Unassisted */
677 /* IBE_VAL4 : Number of Orders */
678 /* IBE_VAL5 : Average Order Value */
679 /* IBE_VAL6 : Discount Percentage */
680 /************************************************************************/
681
682 /*
683 Changes done for Bug:4772549
684 ' decode(P_AMOUNT,0,NULL,((C_AMOUNT-P_AMOUNT)/P_AMOUNT))*100 CHANGE,'||
685 is changed to
686 ' P_AMOUNT P_AMOUNT,'||
687 */
688
689
690 l_custom_sql := 'SELECT IBE_ATTR1, IBE_VAL1,'||
691 ' IBE_VAL7, IBE_VAL3,IBE_VAL4,IBE_VAL5, IBE_VAL6'||
692 ' FROM ( SELECT CUST.VALUE IBE_ATTR1,'||
693 ' MV.BOOKED_AMOUNT IBE_VAL1,'||
694 ' MV.P_AMOUNT IBE_VAL7, '||
695 ' MV.ASSISTED IBE_VAL3,'||
696 ' MV.NUM_OF_ORDERS IBE_VAL4,'||
697 ' to_number(MV.AVG_ORD_VAL) IBE_VAL5,'||
698 ' MV.DISCOUNT IBE_VAL6 '||
699 ' FROM ('||
700 ' SELECT CUSTOMER_ID,'||
701 ' C_AMOUNT BOOKED_AMOUNT,'||
702 ' P_AMOUNT P_AMOUNT,'||
703 ' decode(C_AMOUNT,0,NULL,(ASSISTED_AMOUNT/C_AMOUNT))*100 ASSISTED,'||
704 ' NUM_OF_ORDERS,'||
705 ' decode(NUM_OF_ORDERS,0,NULL,C_AMOUNT/NUM_OF_ORDERS) AVG_ORD_VAL,'||
706 ' decode(LIST_AMOUNT,0,NULL,(DISC_AMOUNT/LIST_AMOUNT))*100 DISCOUNT'||
707 ' FROM'||
708 ' ('||
709 ' SELECT MV.CUSTOMER_ID,'||
710 ' SUM(CASE WHEN REPORT_DATE = :l_asof_date THEN'||
711 ' decode(:l_currency_code,:l_global_primary,AMOUNT_G,:l_global_secondary,AMOUNT_G1,CURRENCY_CD_F, AMOUNT_F) ELSE 0 END) C_AMOUNT,'||
712 ' SUM(CASE WHEN REPORT_DATE = :l_prev_date THEN'||
713 ' decode(:l_currency_code,:l_global_primary,AMOUNT_G,:l_global_secondary,AMOUNT_G1,CURRENCY_CD_F, AMOUNT_F) ELSE 0 END) P_AMOUNT,'||
714 ' SUM(CASE WHEN REPORT_DATE = :l_asof_date THEN'||
715 ' decode(:l_currency_code,:l_global_primary,ASSISTED_AMT_G,:l_global_secondary,ASSISTED_AMT_G1, CURRENCY_CD_F,ASSISTED_AMT_F) ELSE 0 END) ASSISTED_AMOUNT,'||
716 ' SUM(CASE WHEN REPORT_DATE = :l_asof_date THEN'||
717 ' ORDERS_CNT ELSE 0 END) NUM_OF_ORDERS,'||
718 ' SUM(CASE WHEN REPORT_DATE = :l_asof_date THEN'||
719 ' decode(:l_currency_code,:l_global_primary,LIST_AMOUNT_G,:l_global_secondary,LIST_AMOUNT_G1,CURRENCY_CD_F, LIST_AMOUNT_F) ELSE 0 END) LIST_AMOUNT,'||
720 ' SUM(CASE WHEN REPORT_DATE = :l_asof_date THEN'||
721 ' decode(:l_currency_code,:l_global_primary,DISC_AMOUNT_G,:l_global_secondary,DISC_AMOUNT_G1, CURRENCY_CD_F,DISC_AMOUNT_F) ELSE 0 END) DISC_AMOUNT,'||
722 ' RANK () OVER (ORDER BY SUM(CASE WHEN REPORT_DATE = :l_asof_date THEN'||
723 ' decode(:l_currency_code,:l_global_primary,AMOUNT_G,:l_global_secondary,AMOUNT_G1,CURRENCY_CD_F, AMOUNT_F) ELSE 0 END) DESC) RANK'||
724 ' FROM '||l_tableList||
725 ' WHERE MV.TIME_ID = CAL.TIME_ID '||l_msiteFilter||
726 ' AND CAL.REPORT_DATE IN (:l_asof_date, :l_prev_date)'||
727 ' AND CAL.PERIOD_TYPE_ID = MV.PERIOD_TYPE_ID'||
728 ' AND BITAND(CAL.RECORD_TYPE_ID, :l_record_type_id) = CAL.RECORD_TYPE_ID'||
729 ' GROUP BY mv.CUSTOMER_ID'||
730 ' ) WHERE RANK <= :l_rank'||
731 ' ORDER BY RANK) mv, FII_CUSTOMERS_V cust'||
732 ' WHERE MV.CUSTOMER_ID = CUST.ID ) '||
733 ' &ORDER_BY_CLAUSE ' ; -- Bug 5076452
734
735 --'ORDER BY '||l_OrderBy;
736
737
738 x_custom_sql := l_custom_sql;
739
740 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
741 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,'ibe.plsql.dbi.ibe_bi_top_act_pvt.get_top_customers_sql.query',l_custom_sql);
742 END IF;
743
744 x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
745
746 x_custom_output.Extend(7);
747
748 l_custom_rec.attribute_name := ':l_asof_date' ;
749 l_custom_rec.attribute_value:= to_char(l_asof_date,'dd/mm/yyyy');
750 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
751 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.DATE_BIND;
752
753 x_custom_output(1) := l_custom_rec;
754
755
756 l_custom_rec.attribute_name := ':l_prev_date' ;
757 l_custom_rec.attribute_value:= to_char(l_prev_date,'dd/mm/yyyy');
758 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
759 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.DATE_BIND;
760
761 x_custom_output(2) := l_custom_rec;
762
763 l_custom_rec.attribute_name := ':l_record_type_id' ;
764 l_custom_rec.attribute_value:= l_record_type_id;
765 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
766 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
767
768 x_custom_output(3) := l_custom_rec;
769
770 l_custom_rec.attribute_name := ':l_rank' ;
771 l_custom_rec.attribute_value:= NVL(FND_PROFILE.VALUE('IBE_BI_TOP_ACT_NO_ROWS'),25);
772
773 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
774 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
775
776 x_custom_output(4) := l_custom_rec;
777
778 l_custom_rec.attribute_name := ':l_currency_code' ;
779 l_custom_rec.attribute_value:= l_currency_code;
780 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
781 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
782
783 x_custom_output(5) := l_custom_rec;
784
785 l_custom_rec.attribute_name := ':l_global_primary' ;
786 l_custom_rec.attribute_value:= l_global_primary;
787 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
788 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
789
790 x_custom_output(6) := l_custom_rec;
791
792 l_custom_rec.attribute_name := ':l_global_secondary' ;
793 l_custom_rec.attribute_value:= l_global_secondary;
794 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
795 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
796
797 x_custom_output(7) := l_custom_rec;
798
799 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
800 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,'ibe.plsql.dbi.ibe_bi_top_act_pvt.get_top_customers_sql.end','END');
801 END IF;
802
803 END GET_TOP_CUSTOMERS_SQL;
804
805
806 PROCEDURE GET_TOP_PRODUCTS_SQL(
807 p_pmv_parameters IN BIS_PMV_PAGE_PARAMETER_tbl,
808 x_custom_sql OUT NOCOPY VARCHAR2,
809 x_custom_output OUT NOCOPY bis_query_attributes_TBL ) IS
810 l_custom_sql VARCHAR2(4000);
811 l_parameter_name VARCHAR2(3200);
812 l_asof_date DATE;
813 l_prev_date DATE;
814 l_record_type_id NUMBER;
815 l_period_type VARCHAR2(3200);
816 l_comparison_type VARCHAR2(3200);
817 l_currency_code VARCHAR2(3200);
818 l_minisite VARCHAR2(3200);
819 l_minisite_id VARCHAR2(3200);
820 l_msiteFilter VARCHAR2(1000);
821 l_tableList VARCHAR2(1000);
822 l_OrderBy VARCHAR2(2000);
823 l_custom_rec BIS_QUERY_ATTRIBUTES ;
824 dbg_msg VARCHAR2(3200);
825 l_global_primary VARCHAR2(15) ;
826 l_global_secondary VARCHAR2(15) ;
827 BEGIN
828
829 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
830 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,'ibe.plsql.dbi.ibe_bi_top_act_pvt.get_top_products_sql.begin','BEGIN');
831 END IF;
832
833 -- initilization of variables
834
835 l_custom_rec := BIS_PMV_PARAMETERS_PUB.Initialize_Query_Type;
836 l_global_primary := '''FII_GLOBAL1''';
837 l_global_secondary := '''FII_GLOBAL2''';
838
839 FOR i IN p_pmv_parameters.FIRST..p_pmv_parameters.LAST
840 LOOP
841 l_parameter_name := p_pmv_parameters(i).parameter_name ;
842
843 IF( l_parameter_name = 'AS_OF_DATE')
844 THEN
845 l_asof_date :=
846 TO_DATE(p_pmv_parameters(i).parameter_value,'DD/MM/YYYY');
847 ELSIF( l_parameter_name = 'IBW_WEB_ANALYTICS_GROUP1+FII_CURRENCIES')
848 THEN
849 l_currency_code := p_pmv_parameters(i).parameter_id;
850 ELSIF( l_parameter_name = 'PERIOD_TYPE')
851 THEN
852 l_period_type := p_pmv_parameters(i).parameter_value;
853 ELSIF( l_parameter_name = 'TIME_COMPARISON_TYPE')
854 THEN
855 l_comparison_type := p_pmv_parameters(i).parameter_value;
856 ELSIF( l_parameter_name = 'SITE+SITE')
857 THEN
858 l_minisite := p_pmv_parameters(i).parameter_value;
859 l_minisite_id := p_pmv_parameters(i).parameter_id;
860 ELSIF ( l_parameter_name = 'ORDERBY')
861 THEN
862 l_OrderBy := p_pmv_parameters(i).parameter_value;
863 END IF;
864 END LOOP;
865
866 l_record_type_id := IBE_BI_PMV_UTIL_PVT.GET_RECORD_TYPE_ID(l_period_type);
867
868 l_prev_date := IBE_BI_PMV_UTIL_PVT.Get_Prev_Date(
869 p_asof_date => l_asof_date,
870 p_period_type => l_period_type,
871 p_comparison_type => l_comparison_type );
872
873
874 dbg_msg := 'AS_OF_DATE:'||l_asof_date||','||'CURR_CODE:'||l_currency_code||
875 ','||'PERIOD_TYPE:'||l_period_type||','||'COMPARISION_TYPE:'||
876 l_comparison_type||','||'MINISITE:'||l_minisite||','||'MINISITE_ID:'||
877 l_minisite_id||','||'ORDER_BY:'||l_OrderBy||','||'RECORD_TYPE_ID:'||
878 l_record_type_id||','||'PREV_DATE:'||l_prev_date;
879 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
880 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,'ibe.plsql.dbi.ibe_bi_top_act_pvt.get_top_products_sql.parameters',dbg_msg);
881 END IF;
882
883 --Change in View done for Bug#:4654974. Issue#12 by narao
884 --The Site Dimension now refers to IBW_BI_MSITE_DIMN_V
885 --And hence the MSITES View is changed from IBE_BI_MSITES_V to IBW_BI_MSITE_DIMN_V.
886
887 if (trim(l_minisite_id) is null) then
888 l_tableList := ' IBE_BI_TOP_PROD_MV FACT,'||
889 -- ' IBE_BI_MSITES_V MSITES,'||
890 ' IBW_BI_MSITE_DIMN_V MSITES,'||
891 ' FII_TIME_RPT_STRUCT_V CAL';
892
893
894 l_msiteFilter :=' AND MSITES.ID = FACT.MINISITE_ID';
895
896 else
897 l_tableList := ' IBE_BI_TOP_PROD_MV FACT,'||
898 ' FII_TIME_RPT_STRUCT_V CAL';
899
900 l_msiteFilter := ' AND FACT.MINISITE_ID in (&SITE+SITE)';
901
902 end if;
903
904 /************************************************************************/
905 /* IBE_ATTR1 : Product Code */
906 /* IBE_ATTR3 : Product Description */
907 /* IBE_VAL1 : Percentage Conversion */
908 /* IBE_VAL2 : Order Amount */
909 /* IBE_VAL3 : Percentage Change(Calculated now on the AK Region)*/
910 /* IBE_VAL8 : Prior Order Amount */
911 /* IBE_VAL4 : Percentage Assisted */
912 /* IBE_VAL5 : Average Order Value */
913 /* IBE_VAL6 : Average Discount */
914 /* IBE_VAL7 : Number of Lines */
915 /************************************************************************/
916 /*
917 Changes done for Bug:4772549
918 ' decode(P_AMOUNT,0,NULL,((C_AMOUNT-P_AMOUNT)/P_AMOUNT))*100 IBE_VAL3,'||
919 is changed to
920 ' P_AMOUNT IBE_VAL8,'||
921 */
922
923 l_custom_sql := 'SELECT IBE_ATTR1, IBE_ATTR3, IBE_VAL1, IBE_VAL2, IBE_VAL8, IBE_VAL4, '||
924 ' IBE_VAL5, IBE_VAL6, IBE_VAL7 FROM ('||
925 ' SELECT VALUE IBE_ATTR1, DESCRIPTION IBE_ATTR3,'||
926 ' IBE_VAL1,IBE_VAL2,IBE_VAL8,IBE_VAL4,to_number(IBE_VAL5) IBE_VAL5,'||
927 ' IBE_VAL6,IBE_VAL7 FROM'||
928 ' ('||
929 ' SELECT'||
930 ' decode(TOTAL_CARTS,0,NULL,(TOTAL_CONV_CARTS/TOTAL_CARTS))*100 IBE_VAL1,'||
931 ' C_AMOUNT IBE_VAL2,'||
932 ' P_AMOUNT IBE_VAL8,'||
933 ' decode(C_AMOUNT,0,NULL,(ASSISTED_AMOUNT/C_AMOUNT))*100 IBE_VAL4,'||
934 ' decode(TOTAL_ORDERS,0,NULL,C_AMOUNT/TOTAL_ORDERS) IBE_VAL5,'||
935 ' decode(LIST_AMOUNT,0,NULL,(DISC_AMOUNT/LIST_AMOUNT))*100 IBE_VAL6,'||
936 ' NUM_OF_LINES IBE_VAL7,'||
937 ' ITEM_ID,'||
938 ' RANK'||
939 ' FROM'||
940 ' ('||
941 ' SELECT FACT.ITEM_ID,'||
942 ' SUM(CASE WHEN REPORT_DATE = :l_asof_date THEN TOT_CART_COUNT ELSE 0 END) TOTAL_CARTS,'||
943 ' SUM(CASE WHEN REPORT_DATE = :l_asof_date THEN CON_ORD ELSE 0 END) TOTAL_CONV_CARTS,'||
944 ' SUM(CASE WHEN REPORT_DATE = :l_asof_date '||
945 ' THEN decode(:l_currency_code,:l_global_primary,BOOKED_AMOUNT_G,:l_global_secondary,BOOKED_AMOUNT_G1,CURRENCY_CD_F, BOOKED_AMOUNT_F) ELSE 0 END) C_AMOUNT,'||
946 ' SUM(CASE WHEN REPORT_DATE = :l_prev_date '||
947 ' THEN decode(:l_currency_code,:l_global_primary,BOOKED_AMOUNT_G,:l_global_secondary,BOOKED_AMOUNT_G1,CURRENCY_CD_F, BOOKED_AMOUNT_F) ELSE 0 END) P_AMOUNT,'||
948 ' SUM(CASE WHEN REPORT_DATE = :l_asof_date THEN decode(RESOURCE_FLAG,''Y'','||
949 ' decode(:l_currency_code,:l_global_primary,BOOKED_AMOUNT_G,:l_global_secondary,BOOKED_AMOUNT_G1,CURRENCY_CD_F, BOOKED_AMOUNT_F),0) ELSE 0 END) ASSISTED_AMOUNT, '||
950 ' SUM(CASE WHEN REPORT_DATE = :l_asof_date THEN TOT_ORD_COUNT ELSE 0 END) TOTAL_ORDERS,'||
951 ' SUM(CASE WHEN REPORT_DATE = :l_asof_date '||
952 ' THEN decode(:l_currency_code,:l_global_primary,BOOKED_LIST_AMT_G,:l_global_secondary,BOOKED_LIST_AMT_G1,CURRENCY_CD_F, BOOKED_LIST_AMT_F) ELSE 0 END) LIST_AMOUNT,'||
953 ' SUM(CASE WHEN REPORT_DATE = :l_asof_date '||
954 ' THEN decode(:l_currency_code,:l_global_primary,DISCOUNT_AMOUNT_G,:l_global_secondary,DISCOUNT_AMOUNT_G1, CURRENCY_CD_F,DISCOUNT_AMOUNT_F) ELSE 0 END) DISC_AMOUNT,'||
955 ' SUM(CASE WHEN REPORT_DATE = :l_asof_date '||
956 ' THEN NUM_OF_ORD_LINES ELSE 0 END) NUM_OF_LINES,'||
957 ' RANK() OVER(ORDER BY SUM(CASE WHEN REPORT_DATE = :l_asof_date'||
958 ' THEN decode(:l_currency_code,:l_global_primary,BOOKED_AMOUNT_G,:l_global_secondary,BOOKED_AMOUNT_G1,CURRENCY_CD_F, BOOKED_AMOUNT_F) ELSE 0 END) DESC) RANK '||
959 ' FROM '|| l_tableList ||
960 ' WHERE CAL.calendar_id = -1'||
961 ' AND FACT.Time_Id = CAL.Time_Id'||
962 ' AND FACT.Period_Type_id = CAL.Period_Type_Id '||l_msiteFilter||
963 ' AND REPORT_DATE IN (:l_asof_date,:l_prev_date)'||
964 ' AND BITAND(CAL.Record_Type_Id, :l_record_type_id) = CAL.Record_Type_Id'||
965 ' GROUP BY '||
966 ' FACT.ITEM_ID'||
967 ' ) WHERE RANK <= :l_rank'||
968 ' ) FACT ,ENI_ITEM_V ENI'||
969 ' WHERE '||
970 ' FACT.ITEM_ID = ENI.ID)'||
971 ' &ORDER_BY_CLAUSE ' ; --Bug 5076452
972
973 --' ORDER BY '||l_orderBy;
974
975
976 x_custom_sql := l_custom_sql;
977 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
978 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,'ibe.plsql.dbi.ibe_bi_top_act_pvt.get_top_products_sql.query',l_custom_sql);
979 END IF;
980
981 x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
982
983 x_custom_output.Extend(7);
984
985 l_custom_rec.attribute_name := ':l_asof_date' ;
986 l_custom_rec.attribute_value:= to_char(l_asof_date,'dd/mm/yyyy');
987 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
988 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.DATE_BIND;
989
990 x_custom_output(1) := l_custom_rec;
991
992
993 l_custom_rec.attribute_name := ':l_prev_date' ;
994 l_custom_rec.attribute_value:= to_char(l_prev_date,'dd/mm/yyyy');
995 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
996 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.DATE_BIND;
997
998 x_custom_output(2) := l_custom_rec;
999
1000 l_custom_rec.attribute_name := ':l_record_type_id' ;
1001 l_custom_rec.attribute_value:= l_record_type_id;
1002 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
1003 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
1004
1005 x_custom_output(3) := l_custom_rec;
1006
1007 l_custom_rec.attribute_name := ':l_rank' ;
1008 l_custom_rec.attribute_value:= NVL(FND_PROFILE.VALUE('IBE_BI_TOP_ACT_NO_ROWS'),25);
1009
1010 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
1011 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
1012
1013 x_custom_output(4) := l_custom_rec;
1014
1015 l_custom_rec.attribute_name := ':l_currency_code' ;
1016 l_custom_rec.attribute_value:= l_currency_code;
1017 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
1018 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
1019
1020 x_custom_output(5) := l_custom_rec;
1021
1022 l_custom_rec.attribute_name := ':l_global_primary' ;
1023 l_custom_rec.attribute_value:= l_global_primary;
1024 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
1025 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
1026
1027 x_custom_output(6) := l_custom_rec;
1028
1029 l_custom_rec.attribute_name := ':l_global_secondary' ;
1030 l_custom_rec.attribute_value:= l_global_secondary;
1031 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
1032 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
1033
1034 x_custom_output(7) := l_custom_rec;
1035
1036 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1037 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,'ibe.plsql.dbi.ibe_bi_top_act_pvt.get_top_products_sql.end','END');
1038 END IF;
1039
1040 END GET_TOP_PRODUCTS_SQL;
1041
1042 END IBE_BI_TOP_ACT_PVT;