[Home] [Help]
PACKAGE BODY: APPS.IBE_BI_SM_KPI_PVT
Source
1 PACKAGE BODY IBE_BI_SM_KPI_PVT AS
2 /* $Header: IBEVBISMKPIB.pls 120.8 2006/06/26 08:57:11 gjothiku ship $ */
3
4 PROCEDURE GET_NEW_CUST_KPI_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
8 ) IS
9 l_custom_sql VARCHAR2(15000);
10 l_parameter_name VARCHAR2(3200);
11 l_asof_date DATE;
12 l_prev_date DATE;
13 l_record_type_id NUMBER;
14 l_period_type VARCHAR2(3200);
15 l_comparison_type VARCHAR2(3200);
16 l_currency_code VARCHAR2(3200);
17 l_minisite VARCHAR2(3200);
18 l_minisite_id VARCHAR2(3200);
19 l_custom_rec BIS_QUERY_ATTRIBUTES;-- :=
20 -- BIS_PMV_PARAMETERS_PUB.Initialize_Query_Type;
21 dbg_msg VARCHAR2(3200);
22 l_g_p VARCHAR2(30);
23 l_g_s VARCHAR2(30);
24
25 l_c_filter VARCHAR2(1000);
26 l_outer_where_clause VARCHAR2(3200) ;
27
28 BEGIN
29
30 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
31 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,'ibe.plsql.dbi.ibe_bi_sm_kpi_pvt.get_new_cust_kpi_sql.begin','BEGIN');
32 END IF;
33
34 -- initilization of variables
35
36 l_custom_rec := BIS_PMV_PARAMETERS_PUB.Initialize_Query_Type;
37 l_g_p := '''FII_GLOBAL1''';
38 l_g_s := '''FII_GLOBAL2''';
39
40 FOR i IN p_pmv_parameters.FIRST..p_pmv_parameters.LAST
41 LOOP
42 l_parameter_name := p_pmv_parameters(i).parameter_name ;
43
44 IF( l_parameter_name = 'AS_OF_DATE')
45 THEN
46 l_asof_date :=
47 TO_DATE(p_pmv_parameters(i).parameter_value,'DD/MM/YYYY');
48 ELSIF( l_parameter_name = 'IBW_WEB_ANALYTICS_GROUP1+FII_CURRENCIES')
49 THEN
50 l_currency_code := p_pmv_parameters(i).parameter_id;
51 ELSIF( l_parameter_name = 'PERIOD_TYPE')
52 THEN
53 l_period_type := p_pmv_parameters(i).parameter_value;
54 ELSIF( l_parameter_name = 'TIME_COMPARISON_TYPE')
55 THEN
56 l_comparison_type := p_pmv_parameters(i).parameter_value;
57 ELSIF( l_parameter_name = 'SITE+SITE')
58 THEN
59 l_minisite := p_pmv_parameters(i).parameter_value;
60 l_minisite_id := p_pmv_parameters(i).parameter_id;
61 END IF;
62 END LOOP;
63
64 l_record_type_id := IBE_BI_PMV_UTIL_PVT.GET_RECORD_TYPE_ID(l_period_type);
65
66
67 l_prev_date := IBE_BI_PMV_UTIL_PVT.GET_PREV_DATE(
68 p_asof_date => l_asof_date,
69 p_period_type => l_period_type,
70 p_comparison_type => l_comparison_type );
71
72 dbg_msg := 'AS_OF_DATE:'||l_asof_date||','||'CURR_CODE:'||l_currency_code||','
73 ||'PERIOD_TYPE:'||l_period_type||','||'COMPARISION_TYPE:'||l_comparison_type||
74 ','||'MINISITE:'||l_minisite||','||'MINISITE_ID:'||l_minisite_id||','||
75 'RECORD_TYPE_ID:'||l_record_type_id||','||'PREV_DATE:'||l_prev_date;
76
77 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
78 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,'ibe.plsql.dbi.ibe_bi_sm_kpi_pvt.get_new_cust_kpi_sql.parameters',dbg_msg);
79 END IF;
80
81 /**********************************************************************************/
82 /* VIEWBYID : Minisite ID */
83 /* VIEWBY : Minisite Name */
84 /* IBE_VAL1 : Minisite ID */
85 /* IBE_VAL2 : Number of New Customers for Current Period */
86 /* IBE_VAL3 : Number of New Customers for Previous Period */
87 /**********************************************************************************/
88 l_c_filter := '';
89 l_outer_where_clause := '';
90
91
92 /*IF ((l_currency_code = l_g_p) OR (l_currency_code = l_g_s)) THEN
93 l_c_filter := '';
94 ELSIF (l_minisite_id <> 'All') THEN
95 l_c_filter := ' AND FACT.MINISITE_ID in (&SITE+SITE) ';
96 END IF;
97
98 Above code is prior to R12
99 Based on Bug # 4660266 ,we need to put 'AND FACT.MINISITE_ID in (&SITE+SITE)"
100
101 */
102
103 -- Initialising where clause based on the site parameter selection
104 -- Based on Bug # 4660266
105
106 IF upper(l_minisite_id) <> 'ALL' THEN
107 l_outer_where_clause := l_outer_where_clause ||
108 ' AND FACT.MINISITE_ID in (&SITE+SITE) ' ;
109 END IF;
110
111
112
113 --Change in View done for Bug#:4654974. Issue#12 by narao
114 --The Site Dimension now refers to IBW_BI_MSITE_DIMN_V
115 --And hence the MSITES View is changed from IBE_BI_MSITE_DIMN_V to IBW_BI_MSITE_DIMN_V.
116
117 l_custom_sql := 'SELECT MSITES.ID VIEWBYID, MSITES.VALUE VIEWBY,'||
118 ' MSITES.ID IBE_VAL1, '||
119 ' SUM (CASE WHEN CAL.report_date = :l_asof_date '||
120 ' THEN nvl(Total,0) ELSE 0 end) IBE_VAL2, '||
121 ' SUM(CASE WHEN CAL.report_date = :l_prev_date '||
122 ' THEN nvl(Total,0) ELSE 0 end) IBE_VAL3, '||
123 ' SUM(SUM (CASE WHEN CAL.report_date = :l_asof_date '||
124 ' THEN nvl(Total,0) ELSE 0 end)) over() IBE_VAL5, '||
125 ' sum(SUM(CASE WHEN CAL.report_date = :l_prev_date '||
126 ' THEN nvl(Total,0) ELSE 0 end)) over() IBE_VAL6 '||
127 ' FROM '||
128 ' IBE_BI_CUSTTIME_MV FACT, '||
129 ' IBW_BI_MSITE_DIMN_V MSITES, '||
130 ' FII_TIME_RPT_STRUCT_V CAL '||
131 ' WHERE CAL.calendar_id = -1 '||
132 ' AND FACT.Time_Id = CAL.Time_Id '||
133 ' AND FACT.Period_Type_id = CAL.Period_Type_Id '||
134 ' AND BITAND(CAL.Record_Type_Id, :l_record_type_id) = CAL.Record_Type_Id '||
135 ' AND CAL.Report_Date in (:l_asof_date,:l_prev_date) '|| l_c_filter ||
136 ' AND MSITES.ID = FACT.MINISITE_ID '|| l_outer_where_clause || --4660266
137 ' GROUP BY MSITES.ID,MSITES.VALUE';
138
139 x_custom_sql := l_custom_sql;
140
141 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
142 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,'ibe.plsql.dbi.ibe_bi_sm_kpi_pvt.get_new_cust_kpi_sql.query',l_custom_sql);
143 END IF;
144
145 x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
146 x_custom_output.Extend(4);
147
148 l_custom_rec.attribute_name := ':l_asof_date' ;
149 l_custom_rec.attribute_value:= to_char(l_asof_date,'dd/mm/yyyy');
150 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
151 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.DATE_BIND;
152
153 x_custom_output(1) := l_custom_rec;
154
155 l_custom_rec.attribute_name := ':l_prev_date' ;
156 l_custom_rec.attribute_value:= to_char(l_prev_date,'dd/mm/yyyy');
157 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
158 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.DATE_BIND;
159
160 x_custom_output(2) := l_custom_rec;
161
162 l_custom_rec.attribute_name := ':l_record_type_id' ;
163 l_custom_rec.attribute_value:= l_record_type_id;
164 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
165 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
166
167 x_custom_output(3) := l_custom_rec;
168
169 /*
170 l_custom_rec.attribute_name := ':l_minisite_id' ;
171 l_custom_rec.attribute_value:= l_minisite_id;
172 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
173 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
174
175 x_custom_output(4) := l_custom_rec;
176 */
177
178 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
179 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,'ibe.plsql.dbi.ibe_bi_sm_kpi_pvt.get_new_cust_kpi_sql.end','END');
180 END IF;
181
182 END GET_NEW_CUST_KPI_SQL;
183
184
185 PROCEDURE GET_CART_ORD_KPIS_SQL(
186 p_pmv_parameters IN BIS_PMV_PAGE_PARAMETER_tbl,
187 x_custom_sql OUT NOCOPY VARCHAR2,
188 x_custom_output OUT NOCOPY bis_query_attributes_TBL
189 ) IS
190 l_custom_sql VARCHAR2(15000); --4660266
191 l_parameter_name VARCHAR2(3200);
192 l_asof_date DATE;
193 l_prev_date DATE;
194 l_record_type_id NUMBER;
195 l_period_type VARCHAR2(3200);
196 l_comparison_type VARCHAR2(3200);
197 l_c_d VARCHAR2(3200);
198 l_minisite VARCHAR2(3200);
199 l_minisite_id VARCHAR2(3200);
200 l_assist_type VARCHAR2(3200) ;
201 l_custom_rec BIS_QUERY_ATTRIBUTES ;
202 dbg_msg VARCHAR2(3200);
203 l_g_p VARCHAR2(30);
204 l_g_s VARCHAR2(30);
205 l_c_filter VARCHAR2(1000);
206 l_outer_where_clause VARCHAR2(3200) ;
207
208
209 BEGIN
210 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
211 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,'ibe.plsql.dbi.ibe_bi_sm_kpi_pvt.get_cart_ord_kpi_sql.begin','BEGIN');
212 END IF;
213
214 -- initilization of variables
215
216 l_custom_rec := BIS_PMV_PARAMETERS_PUB.Initialize_Query_Type;
217 l_assist_type := 'IBE_ORD_TOTAL';
218 l_g_p := '''FII_GLOBAL1''';
219 l_g_s := '''FII_GLOBAL2''';
220
221 FOR i IN p_pmv_parameters.FIRST..p_pmv_parameters.LAST
222 LOOP
223 l_parameter_name := p_pmv_parameters(i).parameter_name ;
224
225 IF( l_parameter_name = 'AS_OF_DATE')
226 THEN
227 l_asof_date :=
228 TO_DATE(p_pmv_parameters(i).parameter_value,'DD/MM/YYYY');
229 ELSIF( l_parameter_name = 'IBW_WEB_ANALYTICS_GROUP1+FII_CURRENCIES')
230 THEN
231 l_c_d := p_pmv_parameters(i).parameter_id;
232
233 ELSIF( l_parameter_name = 'PERIOD_TYPE')
234 THEN
235 l_period_type := p_pmv_parameters(i).parameter_value;
236 ELSIF( l_parameter_name = 'TIME_COMPARISON_TYPE')
237 THEN
238 l_comparison_type := p_pmv_parameters(i).parameter_value;
239 ELSIF( l_parameter_name = 'SITE+SITE')
240 THEN
241 l_minisite := p_pmv_parameters(i).parameter_value;
242 l_minisite_id := p_pmv_parameters(i).parameter_id;
243 END IF;
244 END LOOP;
245
246 l_record_type_id := IBE_BI_PMV_UTIL_PVT.GET_RECORD_TYPE_ID(l_period_type);
247
248 l_c_filter := '';
249
250 /*
251 IF ((l_c_d = l_g_p) OR (l_c_d = l_g_s)) THEN
252 l_c_filter := '';
253 ELSIF (l_minisite_id <> 'All') THEN
254 l_c_filter := ' AND FACT.MINISITE_ID in (&SITE+SITE)';
255 END IF;
256
257 Above code is prior to R12
258 Based on Bug # 4660266 ,we need to put 'AND FACT.MINISITE_ID in (&SITE+SITE)"
259
260 */
261
262 -- Initialising where clause based on the site parameter selection
263 -- Based on Bug # 4660266
264
265 IF upper(l_minisite_id) <> 'ALL' THEN
266 l_outer_where_clause := l_outer_where_clause ||
267 ' AND FACT.MINISITE_ID in (&SITE+SITE)' ;
268 END IF;
269
270
271 l_prev_date := IBE_BI_PMV_UTIL_PVT.GET_PREV_DATE(
272 p_asof_date => l_asof_date,
273 p_period_type => l_period_type,
274 p_comparison_type => l_comparison_type );
275
276 dbg_msg := 'AS_OF_DATE:'||l_asof_date||','||'CURR_CODE:'||l_c_d||','
277 ||'PERIOD_TYPE:'||l_period_type||','||'COMPARISION_TYPE:'||l_comparison_type||
278 ','||'MINISITE:'||l_minisite||','||'MINISITE_ID:'||l_minisite_id||','||
279 'RECORD_TYPE_ID:'||l_record_type_id||','||'PREV_DATE:'||l_prev_date;
280
281 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
282 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,'ibe.plsql.dbi.ibe_bi_sm_kpi_pvt.get_cart_ord_kpi_sql.parameters',dbg_msg);
283 END IF;
284
285 /*********************************************************************************/
286 /* VIEWBYID : Minisite ID */
287 /* VIEWBY : Minisite Name */
288 /* IBE_VAL1 : Minisite ID */
289 /* IBE_VAL2 : Avg Order Value for Current Period */
290 /* IBE_VAL3 : Avg Order Value for Previous Period */
291 /*********************************************************************************/
292
293 --Change in View done for Bug#:4654974. Issue#12 by narao
294 --The Site Dimension now refers to IBW_BI_MSITE_DIMN_V
295 --And hence the MSITES View is changed from IBE_BI_MSITE_DIMN_V to IBW_BI_MSITE_DIMN_V.
296
297 -- Added nvl to the outer most select clause a part of bug 5253591
298
299 l_custom_sql := 'SELECT ID VIEWBYID, VALUE VIEWBY, '||
300 'id IBE_VAL1, '||
301 'nvl(c_total,0) IBE_VAL2,'||
302 'nvl(p_total,0) IBE_VAL3,'||
303 'sum(nvl(c_total,0)) over() IBE_VAL21,'||
304 'sum(nvl(p_total,0)) over() IBE_VAL22,'||
305 'decode(nvl(c_total,0),0,null,c_ord/c_total)*100 IBE_VAL4,'||
306 'decode(nvl(p_total,0),0,null,p_ord/p_total)*100 IBE_VAL5,'||
307 'decode(sum(nvl(c_total,0)) over(),0,null,(sum(c_ord) over()/sum(c_total)over()))*100 IBE_VAL6,'||
308 'decode(sum(nvl(p_total,0)) over(),0,null,(sum(p_ord) over()/sum(p_total)over()))*100 IBE_VAL7,'||
309 'decode(nvl(Count_Curr,0),0,NULL,Amount_Curr/Count_Curr) IBE_VAL8, '||
310 'decode(nvl(Count_Prev,0),0,NULL,Amount_Prev/Count_Prev) IBE_VAL9,'||
311 'decode(sum(nvl(Count_Curr,0)) over(), 0, NULL, sum(Amount_Curr) over()/sum(Count_Curr) over()) IBE_VAL10,'||
312 'decode(sum(nvl(Count_Prev,0)) over(), 0, NULL, sum(Amount_Prev) over()/sum(Count_Prev) over()) IBE_VAL11,'||
313 'decode(nvl(c_list_amt,0),0,null,c_disc_amt/c_list_amt)*100 IBE_VAL12, '||
314 'decode(nvl(p_list_amt,0),0,null,p_disc_amt/p_list_amt)*100 IBE_VAL13,'||
315 'decode(sum(nvl(c_list_amt,0)) over(),0,null,(sum(c_disc_amt) over ()/sum(c_list_amt) over()))*100 IBE_VAL14, '||
316 'decode(sum(nvl(p_list_amt,0)) over(),0,null,(sum(p_disc_amt) over ()/sum(p_list_amt) over()))*100 IBE_VAL15,'||
317 'nvl(Amount_Curr,0) IBE_VAL16,'||
318 'nvl(Amount_Prev,0) IBE_VAL17, '||
319 'sum(nvl(Amount_Curr,0)) over() IBE_VAL27,'||
320 'sum(nvl(Amount_Prev,0)) over() IBE_VAL28, '||
321 'nvl(c_camp_amt,0) IBE_VAL18,'||
322 'nvl(p_camp_amt,0) IBE_VAL19,'||
323 'sum(nvl(c_camp_amt,0)) over() IBE_VAL30,'||
324 'sum(nvl(p_camp_amt,0)) over() IBE_VAL31'||
325
326 ' FROM ( '||
327 'SELECT MSITES.ID id, MSITES.VALUE VALUE,'||
328 'SUM((CASE WHEN (report_date = :l_asof_date and MEASURE_TYPE = ''IBE_ORD_TOTAL'') '||
329 'THEN decode(:l_c_d,:l_g_p,Amount_g,:l_g_s,Amount_g1,currency_cd_f,amount_f) ELSE 0 end)) Amount_Curr, '||
330 'SUM((CASE WHEN (report_date = :l_asof_date and MEASURE_TYPE = ''IBE_ORD_TOTAL'')'||
331 'THEN Tot_Count ELSE 0 end)) Count_Curr, '||
332 'SUM((CASE WHEN (report_date = :l_prev_date and MEASURE_TYPE = ''IBE_ORD_TOTAL'')'||
333 'THEN decode(:l_c_d,:l_g_p,Amount_g,:l_g_s,Amount_g1,currency_cd_f,amount_f) ELSE 0 end)) Amount_Prev, '||
334 'SUM((CASE WHEN (report_date = :l_prev_date and MEASURE_TYPE = ''IBE_ORD_TOTAL'')'||
335 'THEN Tot_Count ELSE 0 end))Count_Prev,'||
336 'SUM((CASE WHEN (report_date = :l_asof_date and MEASURE_TYPE = ''IBE_ORD_TOTAL'')'||
337 'THEN decode(:l_c_d,:l_g_p,List_Amount_g,:l_g_s,list_amount_g1,currency_cd_f,List_Amount_f) ELSE 0 end)) c_list_amt, '||
338 'SUM((CASE WHEN (report_date = :l_asof_date and MEASURE_TYPE = ''IBE_ORD_TOTAL'')'||
339 'THEN decode(:l_c_d,:l_g_p,Disc_Amount_g,:l_g_s,Disc_Amount_g1,currency_cd_f,Disc_amount_f) ELSE 0 end)) c_disc_amt, '||
340 'SUM((CASE WHEN (report_date = :l_prev_date and MEASURE_TYPE = ''IBE_ORD_TOTAL'')'||
341 'THEN decode(:l_c_d,:l_g_p,List_Amount_g,:l_g_s,List_Amount_g1,currency_cd_f,List_Amount_f) ELSE 0 end)) p_list_amt, '||
342 'SUM((CASE WHEN (report_date = :l_prev_date and MEASURE_TYPE = ''IBE_ORD_TOTAL'')'||
343 'THEN decode(:l_c_d,:l_g_p,Disc_amount_g,:l_g_s,Disc_Amount_g1,currency_cd_f,Disc_Amount_f) ELSE 0 end)) p_disc_amt,'||
344 'SUM((CASE WHEN (report_date = :l_asof_date and MEASURE_TYPE = ''IBE_ORD_CAMPAIGN'')'||
345 'THEN decode(:l_c_d,:l_g_p,Amount_g,:l_g_s,Amount_g1,currency_cd_f,amount_f) ELSE 0 end)) c_camp_amt,'||
346 'SUM((CASE WHEN (report_date = :l_prev_date and MEASURE_TYPE = ''IBE_ORD_CAMPAIGN'')'||
347 'THEN decode(:l_c_d,:l_g_p,Amount_g,:l_g_s,Amount_g1,currency_cd_f,Amount_f) ELSE 0 end)) p_camp_amt,'||
348 'SUM((CASE WHEN (report_date = :l_asof_date and MEASURE_TYPE = ''IBE_QOT_TOTAL'')'||
349 'THEN con_ord ELSE 0 end)) c_ord, '||
350 'SUM((CASE WHEN (report_date = :l_asof_date and MEASURE_TYPE = ''IBE_QOT_TOTAL'')'||
351 'THEN tot_count ELSE 0 end)) c_total, '||
352 'SUM((CASE WHEN (report_date = :l_prev_date and MEASURE_TYPE = ''IBE_QOT_TOTAL'')'||
353 'THEN con_ord ELSE 0 end)) p_ord, '||
354 'SUM((CASE WHEN (report_date = :l_prev_date and MEASURE_TYPE = ''IBE_QOT_TOTAL'')'||
355 'THEN tot_count ELSE 0 end)) p_total '||
356 'FROM IBE_BI_CART_ORD_MV FACT, IBW_BI_MSITE_DIMN_V MSITES,'||
357 'FII_TIME_RPT_STRUCT_V CAL '||
358 'WHERE FACT.MINISITE_ID = MSITES.ID '||
359 'AND CAL.calendar_id = -1 '||
360 'AND FACT.Time_Id = CAL.Time_Id '||
361 'AND FACT.Period_Type_id = CAL.Period_Type_Id '||
362 'AND MEASURE_TYPE in (''IBE_ORD_TOTAL'',''IBE_ORD_CAMPAIGN'',''IBE_QOT_TOTAL'')'||
363 'AND REPORT_DATE IN (:l_asof_date,:l_prev_date) '|| l_outer_where_clause || --4660266
364 'AND BITAND(CAL.Record_Type_Id, :l_record_type_id) = CAL.Record_Type_Id '|| l_c_filter ||
365 'GROUP BY MSITES.ID, MSITES.value)';
366
367
368 x_custom_sql := l_custom_sql;
369
370 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
371 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,'ibe.plsql.dbi.ibe_bi_sm_kpi_pvt.get_cart_ord_kpi_sql.query',l_custom_sql);
372 END IF;
373
374 x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
375 x_custom_output.Extend(8);
376
377 l_custom_rec.attribute_name := ':l_asof_date' ;
378 l_custom_rec.attribute_value:= to_char(l_asof_date,'dd/mm/yyyy');
379 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
380 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.DATE_BIND;
381
382 x_custom_output(1) := l_custom_rec;
383
384 l_custom_rec.attribute_name := ':l_prev_date' ;
385 l_custom_rec.attribute_value:= to_char(l_prev_date,'dd/mm/yyyy');
386 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
387 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.DATE_BIND;
388
389 x_custom_output(2) := l_custom_rec;
390
391 l_custom_rec.attribute_name := ':l_c_d' ;
392 l_custom_rec.attribute_value:= l_c_d;
393 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
394 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
395
396 x_custom_output(3) := l_custom_rec;
397
398 l_custom_rec.attribute_name := ':l_assist_type' ;
399 l_custom_rec.attribute_value:= l_assist_type;
400 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
401 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
402
403 x_custom_output(4) := l_custom_rec;
404
405 l_custom_rec.attribute_name := ':l_record_type_id' ;
406 l_custom_rec.attribute_value:= l_record_type_id;
407 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
408 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
409
410 x_custom_output(5) := l_custom_rec;
411
412 l_custom_rec.attribute_name := ':l_g_p' ;
413 l_custom_rec.attribute_value:= l_g_p;
414 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
415 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
416
417 x_custom_output(6) := l_custom_rec;
418
419 l_custom_rec.attribute_name := ':l_g_s' ;
420 l_custom_rec.attribute_value:= l_g_s;
421 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
422 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
423
424 x_custom_output(7) := l_custom_rec;
425 /*
426 l_custom_rec.attribute_name := ':l_minisite_id' ;
427 l_custom_rec.attribute_value:= l_minisite_id;
428 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
429 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
430
431 x_custom_output(8) := l_custom_rec;
432 */
433
434
435
436
437
438 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
439 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,'ibe.plsql.dbi.ibe_bi_sm_kpi_pvt.get_cart_ord_kpi_sql.end','END');
440 END IF;
441
442 END GET_CART_ORD_KPIS_SQL;
443
444
445 PROCEDURE GET_AVG_ORD_KPI_SQL(
446 p_pmv_parameters IN BIS_PMV_PAGE_PARAMETER_tbl,
447 x_custom_sql OUT NOCOPY VARCHAR2,
448 x_custom_output OUT NOCOPY bis_query_attributes_TBL
449 ) IS
450 l_custom_sql VARCHAR2(4000);
451 l_parameter_name VARCHAR2(3200);
452 l_asof_date DATE;
453 l_prev_date DATE;
454 l_record_type_id NUMBER;
455 l_period_type VARCHAR2(3200);
456 l_comparison_type VARCHAR2(3200);
457 l_currency_code VARCHAR2(3200);
458 l_minisite VARCHAR2(3200);
459 l_minisite_id VARCHAR2(3200);
460 l_assist_type VARCHAR2(3200) ;
461 l_custom_rec BIS_QUERY_ATTRIBUTES ;
462 dbg_msg VARCHAR2(3200);
463 BEGIN
464 NULL;
465 END GET_AVG_ORD_KPI_SQL;
466
467
468 PROCEDURE GET_BOOK_ORD_KPI_SQL(
469 p_pmv_parameters IN BIS_PMV_PAGE_PARAMETER_tbl,
470 x_custom_sql OUT NOCOPY VARCHAR2,
471 x_custom_output OUT NOCOPY bis_query_attributes_TBL
472 ) IS
473 l_custom_sql VARCHAR2(4000);
474 l_parameter_name VARCHAR2(3200);
475 l_asof_date DATE;
476 l_prev_date DATE;
477 l_record_type_id NUMBER;
478 l_period_type VARCHAR2(3200);
479 l_comparison_type VARCHAR2(3200);
480 l_currency_code VARCHAR2(3200);
481 l_minisite VARCHAR2(3200);
482 l_minisite_id VARCHAR2(3200);
483 l_assist_type VARCHAR2(3200) ;
484 l_custom_rec BIS_QUERY_ATTRIBUTES ;
485 dbg_msg VARCHAR2(3200);
486 BEGIN
487
488 NULL;
489 END GET_BOOK_ORD_KPI_SQL;
490
491
492
493 PROCEDURE GET_CARTS_KPI_SQL(
494 p_pmv_parameters IN BIS_PMV_PAGE_PARAMETER_tbl,
495 x_custom_sql OUT NOCOPY VARCHAR2,
496 x_custom_output OUT NOCOPY bis_query_attributes_TBL
497 ) IS
498 l_custom_sql VARCHAR2(4000);
499 l_parameter_name VARCHAR2(3200);
500 l_asof_date DATE;
501 l_prev_date DATE;
502 l_record_type_id NUMBER;
503 l_period_type VARCHAR2(3200);
504 l_comparison_type VARCHAR2(3200);
505 l_currency_code VARCHAR2(3200);
506 l_minisite VARCHAR2(3200);
507 l_minisite_id VARCHAR2(3200);
508 l_assist_type VARCHAR2(3200);
509 l_custom_rec BIS_QUERY_ATTRIBUTES ;
510 dbg_msg VARCHAR2(3200);
511
512 BEGIN
513 NULL;
514 END GET_CARTS_KPI_SQL;
515
516
517 PROCEDURE GET_CARTS_CONV_KPI_SQL(
518 p_pmv_parameters IN BIS_PMV_PAGE_PARAMETER_tbl,
519 x_custom_sql OUT NOCOPY VARCHAR2,
520 x_custom_output OUT NOCOPY bis_query_attributes_TBL
521 ) IS
522 l_custom_sql VARCHAR2(4000);
523 l_parameter_name VARCHAR2(3200);
524 l_asof_date DATE;
525 l_prev_date DATE;
526 l_record_type_id NUMBER;
527 l_period_type VARCHAR2(3200);
528 l_comparison_type VARCHAR2(3200);
529 l_currency_code VARCHAR2(3200);
530 l_minisite VARCHAR2(3200);
531 l_minisite_id VARCHAR2(3200);
532 l_assist_type VARCHAR2(3200) ;
533 l_custom_rec BIS_QUERY_ATTRIBUTES ;
534 dbg_msg VARCHAR2(3200);
535
536 BEGIN
537
538 NULL;
539 END GET_CARTS_CONV_KPI_SQL;
540
541
542
543 PROCEDURE GET_AVG_DISC_KPI_SQL(
544 p_pmv_parameters IN BIS_PMV_PAGE_PARAMETER_tbl,
545 x_custom_sql OUT NOCOPY VARCHAR2,
546 x_custom_output OUT NOCOPY bis_query_attributes_TBL
547 ) IS
548 l_custom_sql VARCHAR2(4000);
549 l_parameter_name VARCHAR2(3200);
550 l_asof_date DATE;
551 l_prev_date DATE;
552 l_record_type_id NUMBER;
553 l_period_type VARCHAR2(3200);
554 l_comparison_type VARCHAR2(3200);
555 l_currency_code VARCHAR2(3200);
556 l_minisite VARCHAR2(3200);
557 l_minisite_id VARCHAR2(3200);
558 l_assist_type VARCHAR2(3200);
559 l_custom_rec BIS_QUERY_ATTRIBUTES ;
560 dbg_msg VARCHAR2(3200);
561 BEGIN
562
563 NULL;
564 END GET_AVG_DISC_KPI_SQL;
565
566
567 PROCEDURE GET_CAMP_ORD_KPI_SQL(
568 p_pmv_parameters IN BIS_PMV_PAGE_PARAMETER_tbl,
569 x_custom_sql OUT NOCOPY VARCHAR2,
570 x_custom_output OUT NOCOPY bis_query_attributes_TBL
571 ) IS
572 l_custom_sql VARCHAR2(4000);
573 l_parameter_name VARCHAR2(3200);
574 l_asof_date DATE;
575 l_prev_date DATE;
576 l_record_type_id NUMBER;
577 l_period_type VARCHAR2(3200);
578 l_comparison_type VARCHAR2(3200);
579 l_currency_code VARCHAR2(3200);
580 l_minisite VARCHAR2(3200);
581 l_minisite_id VARCHAR2(3200);
582 l_assist_type VARCHAR2(3200) ;
583 l_custom_rec BIS_QUERY_ATTRIBUTES ;
584 dbg_msg VARCHAR2(3200);
585
586 BEGIN
587
588 NULL;
589 END GET_CAMP_ORD_KPI_SQL;
590
591
592 END IBE_BI_SM_KPI_PVT;