[Home] [Help]
PACKAGE BODY: APPS.IBW_BI_PROD_REF_PVT
Source
1 PACKAGE BODY IBW_BI_PROD_REF_PVT AS
2 /* $Header: ibwbrepb.pls 120.15 2006/07/21 07:10:07 gjothiku noship $ */
3
4 /**********************************************************************************************
5 * PROCEDURE : GET_WEB_REF_SQL *
6 * PURPOSE : This procedure is used to build the portlet query required *
7 * to render the Web Referral Analysis Report. *
8 * *
9 * PARAMETERS : *
10 * p_param varchar2 IN: This is used to get the parameters *
11 * selected from the parameter portlet *
12 * x_custom_sql varchar2 OUT This is used to send the portlet query *
13 * x_cusom_output varchar2 OUT This is used to send the bind variables *
14 * *
15 **********************************************************************************************/
16
17 PROCEDURE GET_WEB_REF_SQL
18 ( p_param IN BIS_PMV_PAGE_PARAMETER_TBL
19 , x_custom_sql OUT NOCOPY VARCHAR2
20 , x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL
21 )
22 IS
23 --Generic Variables
24 l_custom_sql VARCHAR2(15000) ; --Final Sql.
25 l_site VARCHAR2(3200) ; --Site Id
26 l_period_type VARCHAR2(3200) ; --Period Type
27 l_referral VARCHAR2(3200) ; -- Referral Dimension
28 l_cust_class VARCHAR2(3200) ; -- Customer Classification
29 l_currency VARCHAR2(3200) ; -- Currency
30 l_view_by VARCHAR2(3200); ----Either Referral category or Site
31 l_from VARCHAR2(3200) ;
32 l_where VARCHAR2(3200) ;
33 l_outer_select VARCHAR2(3000) ;
34 l_inner_select VARCHAR2(3000) ;
35 l_custom_rec BIS_QUERY_ATTRIBUTES;
36 l_inner_group_by VARCHAR2(1000);
37 l_having VARCHAR2(1000);
38
39 --Un wanted Variables
40
41
42 l_cust VARCHAR2(3200) ; -- Customer
43 l_campaign VARCHAR2(3200) ; -- Campaign
44 l_prod_catg VARCHAR2(3200) ; -- Product Category
45 l_prod VARCHAR2(3200) ; -- Product
46 l_page VARCHAR2(3200) ; --Page
47 l_site_area VARCHAR2(3200) ; --Site Area
48
49 --FND Logging
50 l_full_path VARCHAR2(50) ;
51 gaflog_value VARCHAR2(10) ;
52
53 -- Currency Variables
54 l_gp_currency VARCHAR2(15); --Global Primary Currency
55 l_gs_currency VARCHAR2(15); --Global Secondary Curr
56
57
58 --Profile is : FND: Debug Log Enabled and FND: Debug Log Level for Log Level
59 BEGIN
60
61 --Fetch all the Parameters into the Local Variables.
62 IF gaflog_value ='Y' AND (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
63 fnd_log.string(fnd_log.level_statement,l_full_path,'begin');
64 END IF;
65
66 l_full_path := 'ibw.plsql.ibwbrepb.ref_analysis_sql'; --This would be stored in FND_LOG_MESSAGES.MODULE column
67 gaflog_value := fnd_profile.value('AFLOG_ENABLED'); --Profile is : FND: Debug Log Enabled and FND: Debug Log Level for Log Level
68
69 IF gaflog_value ='Y' AND (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
70 fnd_log.string(fnd_log.level_procedure,l_full_path,'Before the Call to UTL Package');
71 END IF;
72
73 --To get all the Page Parameters.
74 IBW_BI_UTL_PVT.GET_PAGE_PARAMETERS
75 (
76 p_pmv_parameters => p_param
77 , x_period_type => l_period_type --Not Used
78 , x_site => l_site --Site Id
79 , x_currency_code => l_currency --Used
80 , x_site_area => l_site_area --Not Used
81 , x_page => l_page --Not Used
82 , x_referral => l_referral --Used
83 , x_prod_cat => l_prod_catg --Not Used
84 , x_prod => l_prod --Not Used
85 , x_cust_class => l_cust_class --Used
86 , x_cust => l_cust --Not Used
87 , x_campaign => l_campaign --Not Used
88 , x_view_by => l_view_by --Either Site Id or Referral Category
89 );
90
91 IF gaflog_value ='Y' AND (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
92 fnd_log.string(fnd_log.level_procedure,l_full_path,'After the Call to UTL Package');
93 END IF;
94
95 IF gaflog_value ='Y' AND (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
96 fnd_log.string(fnd_log.level_statement,l_full_path,'l_site : ' || l_site ||' l_site_area : '|| l_site_area ||' l_page : '|| l_page || ' l_view_by : ' || l_view_by );
97 END IF;
98
99 --Initializing section.
100 l_gp_currency := '''FII_GLOBAL1''' ;
101 l_gs_currency := '''FII_GLOBAL2''' ;
102 l_outer_select := '';
103 l_inner_select := '';
104 l_having := '';
105 l_from := '';
106 l_where := '';
107 l_inner_group_by := '';
108 l_custom_rec := BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
109
110 /*********************Bind Parameters used are****************************/
111
112 /* &BIS_CURRENT_ASOF_DATE : AS OF DATE selected in the report */
113 /* &BIS_NESTED_PATTERN : Record Type Id of the Period Type selected */
114
115 /************************************************************************/
116
117 --The Outer Select statement is recorded in this variable.
118 --Average Page View Duration := Page View Duration / Page Views
119
120 -- Not returning value for IBW_VAL2 and returning value for IBW_VAL11 as per bug # 4772549.
121
122 l_outer_select := ' VIEW_BY VIEWBY, VIEWBYID, '||
123 ' nvl(c_visits,0) IBW_VAL1 '||
124 ' ,nvl(p_visits,0) IBW_VAL11 '||
125 ' , DECODE(nvl(c_visits,0),0,null, (page_views/ c_visits)) IBW_VAL3 ' ||--Avg Page Views
126 ' , nvl(web_registrations,0) IBW_VAL4 ' ||
127 ' , nvl(carts,0) IBW_VAL5 ' ||
128 ' , nvl(a_leads,0) IBW_VAL6 ' ||
129 ' , DECODE(nvl(carts,0),0,null,(orders/carts)*100) IBW_VAL7 ' || --Fix for Bug 4654866 - issue # 11 G
130 ' , DECODE(nvl(c_visits,0),0,null,(orders_site_visits/c_visits)*100) IBW_VAL8 ' || --Fix for Bug 4654866 - issue # 11 H
131 ' , nvl(booked_amt,0) IBW_VAL9 ' ||
132 ' , DECODE(nvl(orders,0),0,null,booked_amt/orders) IBW_VAL10 ' ||
133 --For Grand Totals
134 ' , SUM(nvl(c_visits,0)) OVER() IBW_G_TOT1 ' ||
135 ' , DECODE(sum(nvl(p_visits,0)) over(),0,null, ((sum(c_visits) over() - sum(p_visits) over() )/ sum(p_visits) over() )*100) IBW_G_TOT2 ' || --Fix for Bug 4654866 - issue # 11 C
136 ' , DECODE(sum(nvl(c_visits,0)) over() ,0,null, (sum(page_views) over() / sum(c_visits) over() )) IBW_G_TOT3 ' ||
137 ' , SUM(nvl(web_registrations,0)) OVER() IBW_G_TOT4 '||
138 ' , SUM(nvl(carts,0)) OVER() IBW_G_TOT5 '||
139 ' , SUM(nvl(a_leads,0)) OVER() IBW_G_TOT6 '||
140 ' , DECODE(sum(nvl(carts,0)) over() ,0,null ,(SUM(orders) over() /SUM(carts) over() )*100) IBW_G_TOT7 '|| --Fix for Bug 4654866 - issue # 11 G
141 ' , DECODE(sum(nvl(c_visits,0)) over() ,0,null,(sum(orders_site_visits) over() /sum(c_visits) over())*100) IBW_G_TOT8 '|| --Fix for Bug 4654866 - issue # 11 H
142 ' , SUM(nvl(booked_amt,0)) OVER() IBW_G_TOT9 '||
143 ' , DECODE(sum(nvl(orders,0)) over() ,0,null,sum(booked_amt) over() /sum(orders) over()) IBW_G_TOT10 '
144 ;
145
146 IF gaflog_value ='Y' AND (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
147 fnd_log.string(fnd_log.level_statement,l_full_path,'After Outer Select ');
148 END IF;
149
150 --The Inner Select statement is recorded in this variable.
151
152 l_inner_select := ' SUM(decode(cal.report_date,&BIS_CURRENT_ASOF_DATE, visits ,0)) c_visits ' ||
153 ' ,SUM(decode(cal.report_date,&BIS_PREVIOUS_ASOF_DATE,visits,0)) p_visits ' ||
154 ' , SUM(decode(cal.report_date,&BIS_CURRENT_ASOF_DATE, page_views ,0)) page_views ' ||
155 ' , SUM(decode(cal.report_date,&BIS_CURRENT_ASOF_DATE, web_registrations ,0)) web_registrations ' ||
156 ' , sum(decode(cal.report_date,&BIS_CURRENT_ASOF_DATE, carts ,0)) carts ' ||
157 ' , sum(decode(cal.report_date,&BIS_CURRENT_ASOF_DATE, a_leads ,0)) a_leads ' ||
158 ' , sum(decode(cal.report_date,&BIS_CURRENT_ASOF_DATE, orders ,0)) orders ' ||--for Avg Web Order Value denomenator
159 ' , sum(decode(cal.report_date,&BIS_CURRENT_ASOF_DATE, orders_site_visits ,0)) orders_site_visits ' ||--for browse to buy numerator
160 ' , sum(decode(cal.report_date,&BIS_CURRENT_ASOF_DATE, decode(:l_currency,:l_gp_currency,BOOKED_AMT_G,:l_gs_currency,BOOKED_AMT_G1,CURRENCY_CD_F,BOOKED_AMT_F) ,0)) booked_amt'
161 ;
162 --The above statement has been changed for Fix for Bug 4654866 - issue # 11 E
163
164
165 IF gaflog_value ='Y' AND (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
166 fnd_log.string(fnd_log.level_statement,l_full_path,'After Inner Select ');
167 END IF;
168
169 --The From Clause is recorded in this variable
170
171 l_from := ' ibw_refanalysis_time_mv ref_mv ' ||
172 ' ,fii_time_rpt_struct_v cal '||
173 ' ,ibw_bi_msite_dimn_v site ' || --Fix for Bug 4654866 - issue # 11 A , 11 B
174 ' ,ibw_ref_cat_v ref_dim '||
175 ' ,fii_party_mkt_class cust_class_map '; -- This is a mapping table between customer classification and customers ( party_id )
176
177 IF gaflog_value ='Y' AND (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
178 fnd_log.string(fnd_log.level_statement,l_full_path,'After Initial From Clause ' );
179 END IF;
180
181 --The Where Clause is recorded in this variable.
182 --This Where clause will have all the Basic Conditions to join between the IBW_REFANALYSIS_TIME_MV and the Time Dimension table
183 --&BIS_CURRENT_ASOF_DATE gives the AS OF DATE selected in the report.
184
185 l_where := ' cal.report_date in ( &BIS_CURRENT_ASOF_DATE,&BIS_PREVIOUS_ASOF_DATE) ' || --In Condition as Compare to is present in the report.
186 ' and cal.period_type_id = ref_mv.period_type_id ' ||
187 ' and bitand(cal.record_type_id,&BIS_NESTED_PATTERN)= cal.record_type_id '||
188 ' and ref_mv.time_id = cal.time_id ' ||
189 ' and cal.calendar_id = -1 '|| --Indicates Enterprise Calendar
190 ' and ref_mv.site_id = site.id '|| --Fix for Bug 4654866 - issue #11 A , 11 B
191 ' and ref_mv.referral_category_id = ref_dim.ID '||
192 ' and ref_mv.customer_id =cust_class_map.party_id ';
193
194 IF gaflog_value ='Y' AND (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
195 fnd_log.string(fnd_log.level_statement,l_full_path,'After Initial Where Clause ' );
196 END IF;
197
198 --------------------------------------------------------------------------------------------------------------
199
200
201 IF l_view_by = 'IBW_REFERRAL_CATEGORY+IBW_REF_CAT' THEN --View by is Referral Category
202
203 l_inner_select := ' ref_dim.VALUE VIEW_BY, ref_dim.ID VIEWBYID, '||
204 l_inner_select;
205
206 /*l_from := l_from || ' ,ibw_ref_cat_v ref_dim ';
207 l_where := l_where || ' AND ref_mv.referral_category_id = ref_dim.ID ' ; */ --Commented for Fix for Bug 4654866 - issue # 11 B
208
209 l_inner_group_by := l_inner_group_by || ' ref_dim.VALUE ,ref_dim.ID ';
210
211
212
213 ELSIF l_view_by ='SITE+SITE' THEN --View by is Site
214
215 l_inner_select := ' site.value view_by, site.id viewbyid, ' || l_inner_select;
216
217 /* l_from := l_from || ' ,ibw_bi_msite_dimn_v site ';
218 l_where := l_where ||' and ref_mv.site_id =site.id and ref_mv.referral_category_id is not null ';*/ --Commented for Fix for Bug 4654866 - issue # 11 A
219
220 l_where := l_where ||' and ref_mv.referral_category_id is not null '; --added for Fix for Bug 4654866 - issue # 11 A
221
222 l_inner_group_by := l_inner_group_by || ' site.value, site.id ';
223
224
225
226
227 END IF; --End if for l_view_by
228
229
230
231 IF UPPER(l_site) <> 'ALL' THEN
232 l_where := l_where || ' AND ref_mv.site_id in (&SITE+SITE) ';
233 END IF;
234
235 IF UPPER(l_referral) <> 'ALL' THEN
236 l_where := l_where || ' AND ref_mv.referral_category_id in (&IBW_REFERRAL_CATEGORY+IBW_REF_CAT) ';
237 END IF;
238
239
240 IF UPPER(l_cust_class) <> 'ALL' THEN
241 -- l_from := l_from || ' ,fii_party_mkt_class cust_class_map ';
242 l_where := l_where || ' and cust_class_map.class_code in (&FII_TRADING_PARTNER_CLASS+FII_TRADING_PARTNER_MKT_CLASS) ';
243 END IF;
244
245
246
247 IF gaflog_value ='Y' AND (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
248 fnd_log.string(fnd_log.level_statement,l_full_path,'After View by Comparisons ' );
249 END IF;
250
251
252
253 IF gaflog_value ='Y' AND (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
254 fnd_log.string(fnd_log.level_statement,l_full_path,'After Site Id Validations ' );
255 END IF;
256
257
258 l_custom_sql := ' SELECT ' || l_outer_select ||
259 ' FROM ' ||
260 ' (SELECT ' || l_inner_select ||
261 ' FROM ' || l_from ||
262 ' WHERE ' || l_where ||
263 ' GROUP BY ' || l_inner_group_by ||
264 ')' ||
265 ' &ORDER_BY_CLAUSE ';
266
267 --For Debug Purpose
268 IF gaflog_value ='Y' AND (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
269 fnd_log.string(fnd_log.level_statement,l_full_path,'The Final Query is ' || l_custom_sql);
270 END IF;
271
272 x_custom_sql := l_custom_sql; --This sql is returned back to the PMV.
273
274
275
276
277 --Build the Tokens
278 x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
279
280
281 l_custom_rec.attribute_name := ':l_currency' ;
282 l_custom_rec.attribute_value:= l_currency;
283 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
284 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
285 x_custom_output.EXTEND;
286 x_custom_output(1) := l_custom_rec;
287
288 l_custom_rec.attribute_name := ':l_gp_currency' ;
289 l_custom_rec.attribute_value:= l_gp_currency;
290 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
291 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
292 x_custom_output.EXTEND;
293 x_custom_output(2) := l_custom_rec;
294
295 l_custom_rec.attribute_name := ':l_gs_currency' ;
296 l_custom_rec.attribute_value:= l_gs_currency;
297 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
298 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
299 x_custom_output.EXTEND;
300 x_custom_output(3) := l_custom_rec;
301
302 IF gaflog_value ='Y' AND (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
303 fnd_log.string(fnd_log.level_statement,l_full_path,'END');
304 END IF;
305
306 EXCEPTION
307 WHEN OTHERS THEN
308 if (fnd_log.level_unexpected >= fnd_log.g_current_runtime_level) then
309 fnd_log.string(fnd_log.level_unexpected,l_full_path,SQLERRM);
310 end if;
311 END GET_WEB_REF_SQL;
312
313 PROCEDURE GET_PROD_INT_SQL
314 (
315 p_param IN BIS_PMV_PAGE_PARAMETER_TBL ,
316 x_custom_sql OUT NOCOPY VARCHAR2,
317 x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL )
318 IS
319
320 -- Generic Variables
321
322 l_custom_sql VARCHAR2(15000) ; --Final Sql.
323 l_custom_rec BIS_QUERY_ATTRIBUTES;
324 l_site VARCHAR2(3200); --Site Id
325 l_period_type VARCHAR2(1000); --Period Type
326 l_prod_catg VARCHAR2(3200); -- Product Category
327 l_prod VARCHAR2(3200); -- Product
328 l_view_by VARCHAR2(3200);
329 l_from1 VARCHAR2(3200);
330 l_where1 VARCHAR2(3200);
331 l_from2 VARCHAR2(3200);
332 l_where2 VARCHAR2(3200);
333 l_group_by VARCHAR2(1000);
334 l_outer_select VARCHAR2(3200);
335 l_grand_tot_select VARCHAR2(3200);
336 l_grand_tot_from VARCHAR2(3200);
337 l_grand_tot_where VARCHAR2(3200);
338 l_outer_where VARCHAR2(3200);
339 l_inner_select1 VARCHAR2(3000) ;
340 l_inner_select2 VARCHAR2(3000) ;
341 l_inner_select0 VARCHAR2(3000) ;
342 l_inner_group_by VARCHAR2(1000);
343 l_inner_group_by1 VARCHAR2(1000); --- Change New Bug 5373132
344 l_inner_select0_group_by VARCHAR2(1000);
345 l_having VARCHAR2(5000);
346 l_url_str_prod VARCHAR2(5000);
347 l_url_str_prodcatg VARCHAR2(5000);
348
349 --Un wanted Variables
350 l_page VARCHAR2(3200); -- Page
351 l_site_area VARCHAR2(3200); -- Site Area
352 l_referral VARCHAR2(3200); -- Referral Dimension
353 l_campaign VARCHAR2(3200); -- Campaign
354 l_cust_class VARCHAR2(3200); -- Customer Classification
355 l_cust VARCHAR2(3200); -- Customer
356
357
358 -- Specific Variables
359
360 l_currency VARCHAR2(3200) ; -- Currency
361 l_gp_currency VARCHAR2(15) := '''FII_GLOBAL1''' ; --Global Primary Currency
362 l_gs_currency VARCHAR2(15) := '''FII_GLOBAL2''' ; --Global Secondary Curr
363 l_f_currency VARCHAR2(15); -- Functional Currency
364
365 --FND Logging
366 l_full_path VARCHAR2(50);
367 gaflog_value VARCHAR2(10);
368
369
370 BEGIN
371
372
373 --Fetch all the Parameters into the Local Variables.
374 IF gaflog_value ='Y' AND (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
375 fnd_log.string(fnd_log.level_statement,l_full_path,'begin');
376 END IF;
377
378 l_full_path := 'ibw.plsql.ibwrepab.ref_analysis_sql'; --This would be stored in FND_LOG_MESSAGES.MODULE column
379 gaflog_value := fnd_profile.value('AFLOG_ENABLED'); --Profile is : FND: Debug Log Enabled and FND: Debug Log Level for Log Level
380
381 IF gaflog_value ='Y' AND (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
382 fnd_log.string(fnd_log.level_procedure,l_full_path,'Before the Call to UTL Package');
383 END IF;
384
385
386 IBW_BI_UTL_PVT.GET_PAGE_PARAMETERS
387 (
388 P_PMV_PARAMETERS => p_param ,
389 X_PERIOD_TYPE => l_period_type,
390 X_SITE => l_site,
391 X_CURRENCY_CODE => l_currency,
392 X_SITE_AREA => l_site_area, --Not Wanted
393 X_PAGE => l_page, --Not Wanted
394 X_VIEW_BY => l_view_by, --Not Wanted
395 X_CAMPAIGN => l_campaign, --Not Wanted
396 X_REFERRAL => l_referral, --Not Wanted
397 X_PROD_CAT => l_prod_catg, --Product Category
398 X_PROD => l_prod, --Product
399 X_CUST_CLASS => l_cust_class, --Not Wanted
400 X_CUST => l_cust --Not Wanted
401 );
402
403 IF gaflog_value ='Y' AND (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
404 fnd_log.string(fnd_log.level_procedure,l_full_path,'After the Call to UTL Package');
405 END IF;
406
407 IF gaflog_value ='Y' AND (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
408 fnd_log.string(fnd_log.level_statement,l_full_path,'l_site : ' || l_site ||' l_site_area : '|| l_site_area ||' l_page : '|| l_page || ' l_view_by : ' || l_view_by );
409 END IF;
410
411 l_outer_select := '';
412 l_inner_select0 := '';
413 l_inner_select1 := '';
414 l_inner_select2 := '';
415
416 l_having := '';
417 l_from1 := '';
418 l_where1 := '';
419 l_from2 := '';
420 l_where2 := '';
421 l_inner_group_by := '';
422 l_inner_group_by1 := '';
423 l_custom_rec := BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
424 l_url_str_prod := 'pFunctionName=IBW_BI_PRDCTINTRST_RPT&pParamIds=Y&VIEW_BY=IBW_WEB_ANALYTICS_GROUP1+ENI_ITEM&VIEW_BY_NAME=VIEW_BY_ID';
425 l_url_str_prodcatg :='pFunctionName=IBW_BI_PRDCTINTRST_RPT&pParamIds=Y&VIEW_BY=ITEM+ENI_ITEM_VBH_CAT&VIEW_BY_NAME=VIEW_BY_ID';
426
427
428
429
430
431 --The Outer Select statement is recorded in this variable.
432
433
434
435 l_outer_select := ' VIEW_BY VIEWBY, VIEWBYID,prod_descr IBW_ATTR1, nvl(page_views,0) IBW_VAL1 '||
436 ' , DECODE( nvl(visits,0),0,null, ((product_visit/ visits)*100)) IBW_VAL2 ' ||--Percent Product Visits
437 ' , nvl(duv,0) IBW_VAL3 ' ||--Daily Unique Visitor
438 ' , nvl(carts,0) IBW_VAL4 ' ||--Carts
439 ' , DECODE( nvl(carts,0),0,null, (ordered_carts/carts)*100) IBW_VAL5 ' ||--Cart Conversion
440 ' , nvl(booked_orders,0) IBW_VAL6 ' ||
441 ' , nvl(booked_amt,0) IBW_VAL7 ' ||
442 ' , DECODE(nvl(product_visit,0),0,null,(orders_site_visits/product_visit)*100) IBW_VAL8 ' ||--Browse to buy
443 --Changes for the ER 4760433
444 ' , nvl(carts,0) IBW_VAL10 ' ||
445 ' , nvl(page_views,0) IBW_VAL9 '||
446 --Changes for the ER 4760433
447 --For Grand Totals
448 ' , SUM(nvl(page_views,0)) OVER() IBW_G_TOT1 ' ||
449 ' , DECODE(nvl(visits,0),0,null, ( g_product_visit / visits )*100) IBW_G_TOT2 ' ||
450 ' , sum(nvl(duv,0)) over() IBW_G_TOT3 ' ||
451 ' , nvl(g_carts,0) IBW_G_TOT4 '||
452 ' , DECODE( nvl(g_carts,0),0,null, (g_ordered_carts/g_carts)*100) IBW_G_TOT5 '||
453 ' , nvl(g_booked_orders,0) IBW_G_TOT6 '||
454 ' , sum(nvl(booked_amt,0)) over() IBW_G_TOT7'||
455 ' , DECODE( nvl(g_product_visit,0) ,0,null,(g_orders_site_visits/g_product_visit )*100) IBW_G_TOT8 ';
456
457
458
459
460 IF gaflog_value ='Y' AND (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
461 fnd_log.string(fnd_log.level_statement,l_full_path,'After Outer Select ');
462 END IF;
463
464
465 l_inner_select0 := ' SUM(page_views) page_views ' ||
466 ' , SUM(product_visit) product_visit ' || --Numerator for percent product visits and denominator for Browse to Buy
467 ' , SUM(visits) visits ' ||--Denominator for percent product visits
468 ' , SUM(duv) duv ' ||--Daily unique visitor
469 ' , SUM(carts) carts ' ||--for carts and carts converesion denominator
470 ' , SUM(ordered_carts) ordered_carts ' ||--for cart conversion numerator
471 ' , SUM(booked_orders) booked_orders ' ||
472 ' , sum(booked_amt) booked_amt'||
473 ' , SUM(orders_site_visits) orders_site_visits '--Browse to buy numerator
474 ;
475
476
477
478 --The Inner Select statement is recorded in this variable.
479 l_inner_select1 := ' SUM(page_views) page_views ' ||
480 ' , count(distinct visit_id) product_visit ' || --Numerator for percent product visits and denominator for Browse to Buy
481 ' , null visits ' ||--Denominator for percent product visits
482 ' , count(visitant_id) duv ' ||--Daily unique visitor
483 ' , count(distinct cart_id) carts ' ||--for carts and carts converesion denominator
484 ' , count(distinct qot_order_id) ordered_carts ' ||--for cart conversion numerator
485 ' , count(distinct order_id) booked_orders ' ||
486 ' , sum(decode(:l_currency,:l_gp_currency,BOOKED_AMT_G,:l_gs_currency,BOOKED_AMT_G1,CURRENCY_CD_F,BOOKED_AMT_F)) booked_amt'||
487 ' , count(distinct orders_site_visits) orders_site_visits '--Browse to buy numerator
488 ;
489
490 l_inner_select2 := ' null page_views ' ||
491 ' , null product_visit ' || --Numerator for percent product visits and denominator for Browse to Buy
492 ' , SUM(no_visits) visits ' ||--Denominator for percent product visits
493 ' , null duv ' ||--Daily unique visitor
494 ' , null carts ' ||--for carts and carts converesion denominator
495 ' , null ordered_carts ' ||--for cart conversion numerator
496 ' , null booked_orders ' ||
497 ' , null booked_amt'||
498 ' , null orders_site_visits '--Browse to buy numerator
499 ;
500
501
502
503 IF gaflog_value ='Y' AND (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
504 fnd_log.string(fnd_log.level_statement,l_full_path,'After Inner Select ');
505 END IF;
506
507 --The From Clause is recorded in this variable
508
509 l_from1 := ' ibw_product_time_mv prod_mv, ' ||
510 'ibw_bi_msite_dimn_v site, ' ||
511 ' fii_time_rpt_struct_v cal ';
512 l_from2 := l_from1;
513
514 IF gaflog_value ='Y' AND (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
515 fnd_log.string(fnd_log.level_statement,l_full_path,'After Initial From Clause ' );
516 END IF;
517
518 --The Where Clause is recorded in this variable.
519 --&BIS_CURRENT_ASOF_DATE gives the AS OF DATE selected in the report.
520
521 l_where1 := ' cal.report_date = (&BIS_CURRENT_ASOF_DATE) ' ||
522 ' and cal.period_type_id = prod_mv.period_type_id ' ||
523 ' and bitand(cal.record_type_id,&BIS_NESTED_PATTERN)= cal.record_type_id '||
524 ' and prod_mv.time_id = cal.time_id ' ||
525 ' and cal.calendar_id = -1 ' || --Indicates Enterprise Calendar
526 ' and prod_mv.site_id = site.id ' ;
527
528 l_where2 :=l_where1||
529 ' and prod_mv.leaf_categ_id=-9999 '||
530 ' and prod_mv.product_id=-9999 ';
531
532 l_having := ' SUM (page_views) is not null '||
533 'or SUM (product_visit) is not null '||
534 'or SUM (duv ) is not null '||
535 'or SUM (carts) is not null '||
536 'or SUM (ordered_carts) is not null '||
537 'or SUM (booked_orders) is not null '||
538 'or SUM (booked_amt) is not null '||
539 'or SUM (orders_site_visits) is not null' ;
540
541
542 IF gaflog_value ='Y' AND (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
543 fnd_log.string(fnd_log.level_statement,l_full_path,'After Initial Where Clause ' );
544 END IF;
545
546 --------------------------------------------------------------------------------------------------------------
547 --Fetching Site Area Name or Page Name according to the View by
548
549 IF l_view_by ='ITEM+ENI_ITEM_VBH_CAT' THEN --View by is Prodcut Category
550
551 l_outer_select := l_outer_select||' , DECODE(leaf_node_flag,''Y'','||''''||l_url_str_prod||''''||','||''''||l_url_str_prodcatg||''''||' ) IBW_URL1 ';
552
553 IF UPPER(l_prod_catg) <> 'ALL' THEN
554 l_inner_select1 := ' p.VALUE view_by, edh.imm_child_id viewbyid, '|| -- Change New Bug 5373132
555 ' edh.leaf_node_flag leaf_node_flag,null prod_descr , '|| --- Change New Bug 5373132
556 l_inner_select1; -- Change New Bug 5373132
557 l_inner_select2 := ' p.value VIEW_BY, p.id VIEWBYID, p.leaf_node_flag leaf_node_flag,null prod_descr , '|| --- Change Bug 5373132
558 l_inner_select2; -- Change Bug 5373132
559 ELSE
560 l_inner_select1 := ' p.value VIEW_BY, p.parent_id VIEWBYID, p.leaf_node_flag leaf_node_flag,null prod_descr , '|| --- Change Bug 5373132
561 l_inner_select1; --- Change Bug 5373132
562 l_inner_select2 := ' p.value VIEW_BY, p.parent_id VIEWBYID, p.leaf_node_flag leaf_node_flag,null prod_descr , '|| --- Change Bug 5373132
563 l_inner_select2; -- Change Bug 5373132
564 END IF;
565
566
567 l_inner_select0 := ' view_by,viewbyid,leaf_node_flag,prod_descr, '||l_inner_select0;
568
569 l_inner_select0_group_by := ' view_by,viewbyid,leaf_node_flag,prod_descr ';
570
571
572
573 l_from1 := l_from1 || ' ,eni_denorm_hierarchies edh,mtl_default_category_sets mdc ';
574 l_where1 := l_where1 || ' AND prod_mv.leaf_categ_id = edh.child_id '||
575 ' AND edh.object_type = ''CATEGORY_SET'' '||
576 ' AND edh.object_id = mdc.category_set_id '||
577 ' AND mdc.functional_area_id = 11 '||
578 ' AND edh.dbi_flag = ''Y'' '
579 ;
580
581 IF UPPER(l_prod_catg) = 'ALL' THEN --- Change New Bug 5373132
582 l_where1 := l_where1 || ' AND edh.parent_id = p.child_id '|| --- Change New Bug 5373132
583 ' AND edh.top_node_flag = ''Y'''; --- Change New Bug 5373132
584 l_where2 := l_where2 || --- Change New Bug 5373132
585 ' AND p.top_node_flag = ''Y'' '|| -- Change New Bug 5373132
586 ' AND p.child_id = p.parent_id '; --- Change New Bug 5373132
587 END IF; --- Change New Bug 5373132
588
589
590
591
592
593
597 l_from2 := l_from2 || ' , eni_item_prod_cat_lookup_v p '; -- Change Bug 5373132
594 IF (upper(l_prod_catg) ='ALL') THEN /*Catgeory equal to all*/
595 l_from1 := l_from1 || ' , eni_item_prod_cat_lookup_v p '; -- Change Bug 5373132
596
598
599 l_inner_group_by := l_inner_group_by||' p.value,p.parent_id,p.leaf_node_flag '; -- Change New Bug 5373132
600
601 elsif (upper(l_prod_catg) <> 'ALL') THEN /*Catgeory not equal to all*/
602
603 l_from1 := l_from1 || ' , eni_item_prod_cat_lookup_v p, eni_item_v item '; -- Change Bug 5373132
604
605 l_from2 := l_from2 || ' , eni_item_prod_cat_lookup_v p '; -- Change Bug 5373132
606
607 l_inner_group_by := l_inner_group_by||' p.VALUE, edh.imm_child_id , edh.leaf_node_flag '; -- Change New Bug 5373132
608
609
610 END IF;
611
612
613 ELSIF l_view_by = 'IBW_WEB_ANALYTICS_GROUP1+ENI_ITEM' THEN --View by is Product
614
615 l_outer_select := l_outer_select||' , null IBW_URL1 ';
616
617 l_inner_select1 := ' item.value view_by, item.id viewbyid,item.description prod_descr , ' || l_inner_select1;
618
619 l_inner_select2 := ' item.value view_by, item.id viewbyid,item.description prod_descr , ' || l_inner_select2;
620
621 l_inner_select0 := ' view_by,viewbyid,prod_descr, '||l_inner_select0;
622
623 l_inner_select0_group_by := ' view_by,viewbyid,prod_descr ';
624
625 l_from1 := l_from1 || ' ,eni_item_v item ';
626 l_where1 := l_where1 ||' and prod_mv.product_id = item.id ';
627
628 l_from2 := l_from2 || ' ,eni_item_v item ';
629 -- l_where2 := l_where2 ||' and prod_mv.product_id = item.id ';
630
631 l_inner_group_by := l_inner_group_by || ' item.value, item.id,item.description ';
632
633
634
635
636 END IF; --End if for l_view_by
637
638
639
640 IF UPPER(l_site) <> 'ALL' THEN
641 l_where1 := l_where1 || ' AND prod_mv.site_id in (&SITE+SITE)';
642 l_where2 := l_where2 || ' AND prod_mv.site_id in (&SITE+SITE)';
643 END IF;
644
645 IF UPPER(l_prod_catg) <> 'ALL' THEN
646 IF (l_view_by = 'ITEM+ENI_ITEM_VBH_CAT') THEN
647 -- l_from1 := l_from1 || ' ,eni_item_v item,eni_item_prod_cat_lookup_v p '; --4776922 -- Change Bug 5373132
648 l_where1 := l_where1 ||' and prod_mv.product_id = item.ID '||
649 -- ' and item.vbh_category_id=p.child_id '||
650 ' AND p.id = edh.imm_child_id '|| -- Change Bug 5373132
651 ' AND p.ID = p.child_id '|| -- Change Bug 5373132
652 ' AND ((p.leaf_node_flag = ''N'' AND p.parent_id <> p.ID) '|| -- Change Bug 5373132
653 ' OR p.leaf_node_flag = ''Y'' '|| -- Change Bug 5373132
654 ' ) '|| -- Change Bug 5373132
655 ' and p.parent_id =(&ITEM+ENI_ITEM_VBH_CAT)'|| -- Change Bug 5373132
656 ' AND p.parent_id = edh.parent_id '; -- Change Bug 5373132
657
658
659 l_where2 := l_where2 || -- Change New Bug 5373132
660 ' AND p.ID = p.child_id '|| -- Change New Bug 5373132
661 ' AND ( ( p.leaf_node_flag = ''N'' '|| -- Change New Bug 5373132
662 ' AND p.parent_id <> p.ID '|| -- Change New Bug 5373132
663 ' ) '|| -- Change New Bug 5373132
664 ' OR p.leaf_node_flag = ''Y'' '|| -- Change New Bug 5373132
665 ' ) '|| -- Change Bug 5373132
666 ' and p.parent_id =(&ITEM+ENI_ITEM_VBH_CAT)'; -- Change Bug 5373132
667
668
669 ELSIF (l_view_by ='IBW_WEB_ANALYTICS_GROUP1+ENI_ITEM' ) THEN
670 l_from1 := l_from1 || ' ,eni_item_prod_cat_lookup_v p '; --4776922
671 l_where1 := l_where1 ||' and item.vbh_category_id=p.child_id '||
672 ' and p.parent_id =(&ITEM+ENI_ITEM_VBH_CAT)';
673
674 END IF;
675
676 END IF;
677
678
679 IF UPPER(l_prod) <> 'ALL' THEN
680 l_where1 := l_where1 || ' and prod_mv.product_id = (&IBW_WEB_ANALYTICS_GROUP1+ENI_ITEM)';
681 -- l_where2 := l_where2 || ' AND prod_mv.product_id = (&IBW_WEB_ANALYTICS_GROUP1+ENI_ITEM)';
682 END IF;
683
684
685
686 IF gaflog_value ='Y' AND (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
687 fnd_log.string(fnd_log.level_statement,l_full_path,'After View by Comparisons ' );
688 END IF;
689
690
691
692 IF gaflog_value ='Y' AND (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
693 fnd_log.string(fnd_log.level_statement,l_full_path,'After Site Id Validations ' );
694 END IF;
695
696
697
698 --Grand Total Logic
699 --~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
700
701 l_grand_tot_select := ' count(distinct visit_id) g_product_visit ' || --Numerator for percent product visits and denominator for Browse to Buy
702 ' , count(visitant_id) g_duv ' ||--Daily unique visitor
703 ' , count(distinct cart_id) g_carts ' ||--for carts and carts converesion denominator
704 ' , count(distinct qot_order_id) g_ordered_carts ' ||--for cart conversion numerator
705 ' , count(distinct order_id) g_booked_orders ' ||
706 ' , count(distinct orders_site_visits) g_orders_site_visits '; --Browse to buy numerator
707
708 l_grand_tot_from :=' ibw_product_time_mv prod_mv, ' ||
709 'ibw_bi_msite_dimn_v site, ' ||
710 ' fii_time_rpt_struct_v cal, ' ||
711 ' eni_denorm_hierarchies edh, '||
712 ' mtl_default_category_sets mdc ';
713
714 l_grand_tot_where := ' cal.report_date = (&BIS_CURRENT_ASOF_DATE) ' ||
715 ' and cal.period_type_id = prod_mv.period_type_id ' ||
716 ' and bitand(cal.record_type_id,&BIS_NESTED_PATTERN)= cal.record_type_id '||
717 ' and prod_mv.time_id = cal.time_id ' ||
718 ' and cal.calendar_id = -1 ' || --Indicates Enterprise Calendar
719 ' and prod_mv.site_id = site.id ' ||
720 ' AND prod_mv.leaf_categ_id = edh.child_id '||----
721 ' AND edh.object_type = ''CATEGORY_SET'' '||
722 ' AND edh.object_id = mdc.category_set_id '||
723 ' AND mdc.functional_area_id = 11 '||
724 ' AND edh.dbi_flag = ''Y'' '||
725 -- ' AND edh.parent_id = p.id '|| -- Change Bug 5373132
726 ' AND p.id = edh.imm_child_id '|| -- Change Bug 5373132
727 ' AND p.ID = p.child_id '|| -- Change Bug 5373132
728 ' AND ((p.leaf_node_flag = ''N'' AND p.parent_id <> p.ID) '|| -- Change Bug 5373132
729 ' OR p.leaf_node_flag = ''Y'' '|| -- Change Bug 5373132
730 ' ) ' ;
731
732
733 IF (upper(l_prod_catg) ='ALL') THEN
734 l_grand_tot_from := l_grand_tot_from ||' , eni_item_prod_cat_lookup_v p '; -- Change Bug 5373132
735
736 else
737 -- l_grand_tot_from := l_grand_tot_from || ' , eni_item_prod_cat_lookup_v p '; -- Change Bug 5373132
738 null; -- Change
739
740 end if;
741
742 IF UPPER(l_site) <> 'ALL' THEN
743 l_grand_tot_where := l_grand_tot_where || ' AND prod_mv.site_id in (&SITE+SITE) ';
744 END IF;
745
746 IF UPPER(l_prod_catg) <> 'ALL' THEN
747 l_grand_tot_from := l_grand_tot_from || ' ,eni_item_v item,eni_item_prod_cat_lookup_v p '; --4776922
748 l_grand_tot_where := l_grand_tot_where ||' and prod_mv.product_id = item.ID '||
749 -- ' and item.vbh_category_id=p.child_id '|| -- Change
750 ' and p.parent_id =(&ITEM+ENI_ITEM_VBH_CAT)';
751 end if;
752
753 IF UPPER(l_prod) <> 'ALL' THEN
754 l_grand_tot_where := l_grand_tot_where || ' and prod_mv.product_id = (&IBW_WEB_ANALYTICS_GROUP1+ENI_ITEM)';
755 END IF;
756
757
758
759 IF l_view_by ='ITEM+ENI_ITEM_VBH_CAT' THEN -- Change New Bug 5373132
760 if (upper(l_prod_catg) <> 'ALL') THEN -- Change New Bug 5373132
761 l_inner_group_by1 := ' p.VALUE, p.id, p.leaf_node_flag '; -- Change New Bug 5373132
762 ELSE -- Change New Bug 5373132
763 l_inner_group_by1 := l_inner_group_by; -- Change New Bug 5373132
764 END if; -- Change New Bug 5373132
765 ELSE -- Change New Bug 5373132
766 l_inner_group_by1 := l_inner_group_by; -- Change New Bug 5373132
767 END if; -- Change New Bug 5373132
768
769
770
771
772
773
774
775
776 l_custom_sql := ' SELECT ' || l_outer_select ||
777 ' FROM ' ||
778 ' ( SELECT '|| l_inner_select0 ||
779 ' FROM '||
780 ' (SELECT ' || l_inner_select1 ||
781 ' FROM ' || l_from1 ||
782 ' WHERE ' || l_where1 ||
783 ' GROUP BY ' || l_inner_group_by ||
784 ' UNION ALL ' ||
785 ' SELECT ' || l_inner_select2 ||
786 ' FROM ' || l_from2 ||
787 ' WHERE ' || l_where2 ||
788 ' GROUP BY ' || l_inner_group_by1 || --- Change Bug 5373132
789 ')' ||
790 ' GROUP BY '|| l_inner_select0_group_by ||
791 ' HAVING ' || l_having ||' ), ( '||
792 ' SELECT '|| l_grand_tot_select ||--SELECT CALUSE FOR GRAND TOTAL RETRICES ONE ROW
793 ' FROM '|| l_grand_tot_from ||
794 ' WHERE '|| l_grand_tot_where ||
795 ' ) ' ||
796 ' &ORDER_BY_CLAUSE ';
797
798 --For Debug Purpose
799 IF gaflog_value ='Y' AND (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
800 fnd_log.string(fnd_log.level_statement,l_full_path,'The Final Query is ' || l_custom_sql);
801 END IF;
802
803
804
805 x_custom_sql := l_custom_sql;
806
807 x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
808
809 l_custom_rec.attribute_name := ':l_currency' ;
810 l_custom_rec.attribute_value:= l_currency;
811 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
812 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
813 x_custom_output.EXTEND;
814 x_custom_output(1) := l_custom_rec;
815
816 l_custom_rec.attribute_name := ':l_gp_currency' ;
817 l_custom_rec.attribute_value:= l_gp_currency;
818 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
819 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
820 x_custom_output.EXTEND;
821 x_custom_output(2) := l_custom_rec;
822
823 l_custom_rec.attribute_name := ':l_gs_currency' ;
824 l_custom_rec.attribute_value:= l_gs_currency;
825 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
826 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
827 x_custom_output.EXTEND;
828 x_custom_output(3) := l_custom_rec;
829
830
831 EXCEPTION
832 WHEN OTHERS THEN
833 if (fnd_log.level_unexpected >= fnd_log.g_current_runtime_level) then
834 fnd_log.string(fnd_log.level_unexpected,l_full_path,SQLERRM);
835 end if;
836
837 END GET_PROD_INT_SQL;
838
839
840 END IBW_BI_PROD_REF_PVT;