eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
PACKAGE BODY: APPS.IBW_BI_VST_CMP_KPI_PVT

Source


1 PACKAGE BODY IBW_BI_VST_CMP_KPI_PVT AS
2 /* $Header: ibwbvckb.pls 120.19 2006/06/26 08:55:56 gjothiku noship $ */
3 -- Procedure for the Visitor Conversion Report
4 
5 /*
6 ----------------------------------------------------------------------------
7 Procedure name       : GET_VISTR_CONV_TRND_SQL
8 Parameters
9 IN                   : p_param        pl/sql table
10 OUT                  : x_custom_sql   varchar2
11 OUT                  : x_cusom_output pl/sql table
12 
13 Description          : This procedure will be called from
14                        the 'Web Analytics Visitor Conversion Trend portlet'
15 ------------------------------------------------------------------------------
16 */
17 
18 PROCEDURE GET_VISTR_CONV_TRND_SQL(
19                             p_pmv_parameters IN BIS_PMV_PAGE_PARAMETER_tbl,
20                             x_custom_sql     OUT NOCOPY VARCHAR2,
21                             x_custom_output  OUT NOCOPY bis_query_attributes_TBL )
22 IS
23 -- Generic Variables
24 
25   l_custom_sql              VARCHAR2(15000) ; --Final Sql.
26   l_asof_dt                 DATE;             --As of Date
27   l_site                    VARCHAR2(3200) ;  --Site Id
28   l_period_type             VARCHAR2(1000) ;  --Period Type
29   l_parameter_name          VARCHAR2(3200) ;  --Parameter Name
30   l_page                    VARCHAR2(3200) ;  -- Page  Not required for this report
31   l_site_area               VARCHAR2(3200) ;  -- Site Area  Not required for this report
32   l_view_by                 VARCHAR2(3200);   -- View By   Not required for this report
33   l_rec_id                  NUMBER;
34   l_table_list              VARCHAR2(3200) ;
35   l_outer_where_clause      VARCHAR2(3200) ;
36   l_custom_rec              BIS_QUERY_ATTRIBUTES;
37   l_dimension_id            NUMBER;
38   l_dimension_view          VARCHAR2(3200) ;
39   l_dim_where               VARCHAR2(3200);
40   l_group_by                VARCHAR2(1000);
41   l_order_by                VARCHAR2(100);
42   l_referral                VARCHAR2(3200) ;  -- Referral Dimension  Not required for this report
43   l_campaign                VARCHAR2(3200) ;  --  Not required for this report
44 
45 
46 -- Specific for trend reports
47 
48   l_timetable        VARCHAR2(3200);
49   l_timespan         NUMBER;
50   l_sequence         NUMBER;
51   l_cur_start        DATE;
52   l_mid_start        DATE;
53   l_prev_start       DATE;
54   l_pprev_start      DATE;
55   l_pcur_start       DATE;
56   l_cur_year         NUMBER;
57   l_prev_year        NUMBER;
58 
59 -- Specific Variables
60 
61   l_cust_class   VARCHAR2(3200) ;   -- Customer Classification
62   l_cust         VARCHAR2(3200) ;   -- Customer  Not required for this report
63   l_currency     VARCHAR2(50) ;   -- Currency
64   l_comp_to      VARCHAR2(50) ;   -- Compare To
65   l_prev_date    DATE;            -- Previous Date
66   l_prod_catg    VARCHAR2(50) ;   -- Product Category  Not required for this report
67   l_prod         VARCHAR2(50) ;   -- Product  Not required for this report
68   l_gp_currency  VARCHAR2(15) ;   -- Global Primary Currency
69   l_gs_currency  VARCHAR2(15) ;   -- Global Secondary Curr
70   l_f_currency   VARCHAR2(15) ;   -- Functional Currency
71   l_gp_amount    NUMBER ;         -- Primary Currency Amount
72   l_gs_amount    NUMBER ;         -- Secondary Currency Amount
73   l_f_amount     NUMBER ;         -- Functional Amount
74 
75 
76   --FND Logging
77   l_full_path     VARCHAR2(50) ;
78   --gaflog_value    CONSTANT VARCHAR2(10) ;
79   gaflog_value     CONSTANT VARCHAR2(10) := fnd_profile.value('AFLOG_ENABLED');
80   --Profile is : FND: Debug Log Enabled and FND: Debug Log Level for Log Level
81 
82 BEGIN
83 
84  --gaflog_value   := fnd_profile.value('AFLOG_ENABLED');
85 
86  IF gaflog_value ='Y' AND (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
87     fnd_log.string(fnd_log.level_statement,l_full_path,'Visitor Conversion Trend Report BEGIN');
88   END IF;
89 
90 
91 -- initilization
92 
93   l_gp_currency   := '''FII_GLOBAL1''' ; --Global Primary Currency
94   l_gs_currency   := '''FII_GLOBAL2''' ; --Global Secondary Curr
95 
96   l_full_path    := 'ibw.plsql.ibwbvcob.get_visitor_conv_trend_sql';
97   --gaflog_value   := fnd_profile.value('AFLOG_ENABLED');
98 
99 --Fetch all the Parameters into the Local Variables.
100 
101 
102   IF gaflog_value ='Y' AND (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
103      fnd_log.string(fnd_log.level_procedure,l_full_path,'Before the Call to UTL Package to get parameter values');
104   END IF;
105 
106  IBW_BI_UTL_PVT.GET_PAGE_PARAMETERS
107    (
108      P_PMV_PARAMETERS   =>  p_pmv_parameters,
109      X_PERIOD_TYPE	    =>  l_period_type,
110      X_SITE             =>  l_site,
111      X_CURRENCY_CODE    =>  l_currency,   --Not Wanted
112      X_SITE_AREA        =>  l_site_area,  --Not Wanted
113      X_PAGE             =>  l_page,       --Not Wanted
114      X_REFERRAL         =>  l_referral,   --Not Wanted
115      X_PROD_CAT         =>  l_prod_catg,  --Not Wanted
116      X_PROD             =>  l_prod,       --Not Wanted
117      X_CUST_CLASS       =>  l_cust_class,
118      X_CUST             =>  l_cust,        --Not Wanted
119      X_CAMPAIGN		      =>  l_campaign,   --Not Wanted
120      X_VIEW_BY          =>  l_view_by     --Not Wanted
121     );
122 
123  IF gaflog_value ='Y' AND (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
124     fnd_log.string(fnd_log.level_procedure,l_full_path,'After the Call to UTL Package');
125  END IF;
126 
127  IF gaflog_value ='Y' AND (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
128     fnd_log.string(fnd_log.level_statement,l_full_path,'l_site : ' || l_site || ' l_cust_class : ' || l_cust_class );
129  END IF;
130 
131 
132   --Initializing section starts
133 
134   l_outer_where_clause  := '';
135   l_custom_rec		:=  BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
136 
137   --Initializing section completed
138 
139   --Get the Table List
140 
141   l_table_list   := ' IBW_VISIT_CUST_TIME_MV VISIT_MV' ||  --  To be replaced by the mv when it is ready
142                     ' ,FII_TIME_RPT_STRUCT_V CAL' ||
143 	 	    ' ,FII_PARTY_MKT_CLASS CUST_CLASS_MAP';
144 
145 
146 
147 
148 -- Initialising where clause based on the parameter selection
149 
150   IF upper(l_site) <> 'ALL' THEN
151     l_outer_where_clause   := l_outer_where_clause ||
152 				' AND VISIT_MV.SITE_ID in (&SITE+SITE)' ;
153 
154   ELSE
155     l_table_list := l_table_list  || ', IBW_BI_MSITE_DIMN_V SITE ';
156     l_outer_where_clause   := l_outer_where_clause ||
157 				' AND VISIT_MV.SITE_ID = SITE.ID ';
158   END IF;
159 
160   IF upper(l_cust_class) <> 'ALL' THEN
161 
162     l_outer_where_clause   := l_outer_where_clause ||
163 				' AND VISIT_MV.CUSTOMER_ID = CUST_CLASS_MAP.PARTY_ID '||
164 				' AND CUST_CLASS_MAP.class_code in (&FII_TRADING_PARTNER_CLASS+FII_TRADING_PARTNER_MKT_CLASS)';
165   ELSE
166     l_table_list      := l_table_list || ' ,FII_TRADING_PARTNER_MKTCLASS_V CUSTCLASS';
167     l_outer_where_clause    := l_outer_where_clause ||
168 				' AND VISIT_MV.CUSTOMER_ID = CUST_CLASS_MAP.PARTY_ID '||
169 				' AND CUST_CLASS_MAP.class_code = CUSTCLASS.ID ';
170   END IF;
171 
172 
173 
174  /*********************************************************************************/
175  /* Metrics used in Visit Trend Report:                                           */
176  /* IBW_VAL1                  : Visits                                            */
177  /* IBE_VAL2                  : Anonymous Carts				                            */
178  /* IBW_VAL3                  : Registrations                                     */
179  /* IBW_VAL4                  : Registered Carts                                  */
180  /* IBW_VAL5                  : 'A' Leads                                         */
181  /* IBW_VAL6                  : Cart Conversion                                   */
182  /* IBW_VAL7                  : Booked Orders                                     */
183  /* IBW_VAL8                  : Repeat Orders                                     */
184  /* IBW_VAL9                  : Browse to Buy                                     */
185  /* IBW_VAL10                 : Booked Orders Amount                              */
186   /*********************Bind Parameters used are***********************************/
187  /*                                                                               */
188  /* &BIS_CURRENT_ASOF_DATE          :  AS OF DATE selected in the report          */
189  /* &BIS_PREVIOUS_ASOF_DATE         : Previous period as of date                  */
190  /* &BIS_CURRENT_REPORT_START_DATE  : Current period start date                   */
191  /* &BIS_PREVIOUS_REPORT_START_DATE : Previous period startdate                   */
192  /* &BIS_NESTED_PATTERN             :  Record Type Id of the Period Type selected */
193  /*********************************************************************************/
194 
195 
196 
197 
198    l_custom_sql:= 'SELECT  fii.NAME VIEWBY,
199 	                   nvl(VISITS_CUR,0)                                                      IBW_VAL1,
200                      nvl(ANONYMOUS_CARTS_CUR,0)                                             IBW_VAL2,
201                      nvl(WEB_REGISTRATIONS_CUR,0)                                           IBW_VAL3,
202                      (nvl(CARTS_CUR,0) - nvl(ANONYMOUS_CARTS_CUR,0))                        IBW_VAL4,
203                      nvl(A_LEADS_CUR,0)                                                     IBW_VAL5,
204                      decode(nvl(CARTS_CUR,0),0
205                              ,null -- 0
206                              ,(nvl(ORDERS_CUR,0)/nvl(CARTS_CUR,0))*100)                     IBW_VAL6,    --Changed by AANNAMAL to Consider Total Carts instead of Registered Carts
207                      nvl(TOTAL_BOOKED_ORDERS_CUR,0)                                         IBW_VAL7,
208                      nvl(REPEAT_WEB_ORDERS_CUR,0)                                           IBW_VAL8,
209                      decode(nvl(VISITS_CUR,0),0,null,(ORDERS_SITE_VISITS_CUR/VISITS_CUR)*100)  IBW_VAL9,    -- Returned null instead of 0 for bug 5253591
210                      nvl(BOOKED_AMOUNT_CUR,0)                                               IBW_VAL10
211 	     FROM (        SELECT START_DATE,
212 			   sum(nvl(visits_c,0)) VISITS_CUR,
213 			   sum(nvl(visits_p,0)) VISITS_PRE,
214 			   sum(nvl(anonymous_carts_c,0)) ANONYMOUS_CARTS_CUR,
215 			   sum(nvl(web_registrations_c,0)) WEB_REGISTRATIONS_CUR,
216 			   sum(nvl(carts_c,0)) CARTS_CUR,
217 			   sum(nvl(a_leads_c,0)) A_LEADS_CUR,
218 			   sum(nvl(orders_c,0)) ORDERS_CUR,
219 			   sum(nvl(total_booked_orders_C,0)) TOTAL_BOOKED_ORDERS_CUR,
220 			   sum(nvl(repeat_web_orders_C,0)) REPEAT_WEB_ORDERS_CUR,
221 			   sum(nvl(orders_site_visits_C,0)) ORDERS_SITE_VISITS_CUR,
222 			   sum(nvl(booked_amount_c,0)) BOOKED_AMOUNT_CUR
223 		       FROM (SELECT
224 			     dates.start_date  START_DATE,
225 			     decode(dates.period, ''C'',visits,0) visits_C,
226 			     decode(dates.period, ''P'',visits,0) visits_P,
227 			     decode(dates.period, ''C'',anonymous_carts,0) anonymous_carts_C,
228 			     decode(dates.period, ''C'',web_registrations,0) web_registrations_C,
229 			     decode(dates.period, ''C'',carts,0) carts_C,
230 			     decode(dates.period, ''C'',a_leads,0) a_leads_C,
231 			     decode(dates.period, ''C'',orders,0) orders_C,
232 			     decode(dates.period, ''C'',BOOKED_WEB_ORDERS,0) total_booked_orders_C,
233 			     decode(dates.period, ''C'',repeat_web_orders,0) repeat_web_orders_C,
234 			     decode(dates.period, ''C'',orders_site_visits,0) orders_site_visits_C,
235 			     decode(dates.period, ''C'',decode(:l_currency,:l_gp_currency,booked_amt_g,:l_gs_currency,booked_amt_g1
236 					 ,currency_cd_f,booked_amt_f),0) BOOKED_AMOUNT_C  /* Change for issue 20 bug 4636308)  */
237 				FROM
238 				      ( SELECT
239 					   fii.start_date START_DATE,
240 					   ''C'' PERIOD,
241 					   least(fii.end_date, &BIS_CURRENT_ASOF_DATE) REPORT_DATE
242 					   FROM '||l_period_type||'   fii
243 					   WHERE fii.start_date BETWEEN &BIS_CURRENT_REPORT_START_DATE AND &BIS_CURRENT_ASOF_DATE
244 					    UNION ALL
245             SELECT
246               p2.start_date START_DATE,
247               ''P'' PERIOD,
248               p1.report_date REPORT_DATE
249             FROM
250               (
251                 SELECT
252                   REPORT_DATE,
253                   rownum id
254                 FROM
255                 (
256                 SELECT
257                   least(time_dim.end_date, &BIS_PREVIOUS_ASOF_DATE) REPORT_DATE
258                 FROM
259                   ' ||l_period_type||'   time_dim
260                 WHERE
261                   time_dim.start_date BETWEEN &BIS_PREVIOUS_REPORT_START_DATE AND &BIS_PREVIOUS_ASOF_DATE
262                 ORDER BY time_dim.start_date DESC
263                 )
264               )  p1,
265               (
266               SELECT
267                 START_DATE,
268                 rownum id
269               FROM
270               (
271                 SELECT time_dim.start_date START_DATE
272                 FROM  ' ||l_period_type||'  time_dim
273                 WHERE time_dim.start_date BETWEEN &BIS_CURRENT_REPORT_START_DATE AND &BIS_CURRENT_ASOF_DATE
274                 ORDER BY time_dim.start_date DESC
275                 )
276               ) p2
277 					  WHERE p1.id(+) = p2.id) dates, '|| l_table_list ||'
278 				WHERE
279 				   cal.report_date	  = dates.report_date AND
280 				   VISIT_MV.time_id  =	cal.time_id AND
281 				   VISIT_MV.period_type_id = cal.period_type_id AND
285 WHERE fii.start_date BETWEEN &BIS_CURRENT_REPORT_START_DATE AND &BIS_CURRENT_ASOF_DATE AND
282 				   bitand(cal.record_type_id, &BIS_NESTED_PATTERN) = cal.record_type_id'|| l_outer_where_clause ||')
283 		          GROUP BY START_DATE
284                        ) s,'|| l_period_type||' fii
286 fii.start_date = s.start_date(+)
287 ORDER BY fii.start_date';
288 
289 
290 --IF gaflog_value ='Y' AND (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
291 --    fnd_log.string(fnd_log.level_unexpected,l_full_path,'Visitor Conversion Trend Portlet Query : ' || l_custom_sql);
292 -- END IF;
293 
294 
295     x_custom_sql  := l_custom_sql;
296 
297 
298   x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
299   x_custom_output.Extend(3);
300 
301   l_custom_rec.attribute_name := ':l_currency' ;
302   l_custom_rec.attribute_value:= l_currency;
303   l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
304   l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
305   x_custom_output.EXTEND;
306   x_custom_output(1) := l_custom_rec;
307 
308   l_custom_rec.attribute_name := ':l_gp_currency' ;
309   l_custom_rec.attribute_value:= l_gp_currency;
310   l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
311   l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
312   x_custom_output.EXTEND;
313   x_custom_output(2) := l_custom_rec;
314 
315   l_custom_rec.attribute_name := ':l_gs_currency' ;
316   l_custom_rec.attribute_value:= l_gs_currency;
317   l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
318   l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
319   x_custom_output.EXTEND;
320   x_custom_output(3) := l_custom_rec;
321 
322  --IF gaflog_value ='Y' AND (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
323  --   fnd_log.string(fnd_log.level_unexpected,l_full_path,'Visitor Conversion Trend Portlet Query ended');
324 -- END IF;
325 
326  EXCEPTION
327    WHEN OTHERS THEN
328     if (fnd_log.level_unexpected >= fnd_log.g_current_runtime_level) then
329         fnd_log.string(fnd_log.level_unexpected,l_full_path,SQLERRM);
330     end if;
331 END GET_VISTR_CONV_TRND_SQL;
332 
333 
334 
335 
336 -- Procedure for the Visit Trend Report
337 
338 /*
339 ------------------------------------------------------------
340 Procedure name       : GET_VISIT_TREND_SQL
341 Parameters
342 IN                   : p_param        pl/sql table
343 OUT                  : x_custom_sql   varchar2
344 OUT                  : x_cusom_output pl/sql table
345 
346 Description          : This procedure will be called from
347                        the 'Web Analytics Visit Trend portlet'
348 ------------------------------------------------------------
349 */
350 PROCEDURE GET_VISIT_TREND_SQL(
351                             p_pmv_parameters IN BIS_PMV_PAGE_PARAMETER_tbl,
352                             x_custom_sql     OUT NOCOPY VARCHAR2,
353                             x_custom_output  OUT NOCOPY bis_query_attributes_TBL )
354 IS
355 -- Generic Variables
356 
357   l_custom_sql            VARCHAR2(15000) ; --Final Sql.
358   l_asof_dt               DATE;             --As of Date
359   l_site                  VARCHAR2(3200) ;  --Site Id
360   l_period_type           VARCHAR2(1000) ;  --Period Type
361   l_parameter_name        VARCHAR2(3200) ;  --Parameter Name
362   l_page                  VARCHAR2(3200) ;  -- Page  Not required for this report
363   l_site_area             VARCHAR2(3200) ;  -- Site Area  Not required for this report
364   l_view_by               VARCHAR2(3200);   -- View By   Not required for this report
365   l_rec_id                NUMBER;
366   l_table_list            VARCHAR2(3200) ;
367   l_inner_where_clause    VARCHAR2(3200) ;
368   l_outer_where_clause    VARCHAR2(3200) ;
369   l_custom_rec            BIS_QUERY_ATTRIBUTES;
370   l_dimension_id          NUMBER;
371   l_dimension_view        VARCHAR2(3200);
372   l_dim_where             VARCHAR2(3200);
373   l_group_by              VARCHAR2(1000);
374   l_order_by              VARCHAR2(100);
375   l_referral              VARCHAR2(3200) ; -- Referral Dimension  Not required for this report
376   l_campaign              VARCHAR2(3200) ;  --  Not required for this report
377 
378 
379 -- Specific for trend reports
380 
381   l_timetable        VARCHAR2(3200);
382   l_timespan         NUMBER;
383   l_sequence         NUMBER;
384   l_cur_start        DATE;
385   l_mid_start        DATE;
386   l_prev_start       DATE;
387   l_pprev_start      DATE;
388   l_pcur_start       DATE;
389   l_cur_year         NUMBER;
390   l_prev_year        NUMBER;
391 
392 -- Specific Variables
393 
394   l_cust_class   VARCHAR2(3200) ; -- Customer Classification
395   l_cust         VARCHAR2(3200) ; -- Customer  Not required for this report
396   l_currency     VARCHAR2(3200) ; -- Currency  Not required for this report
397   l_comp_to      VARCHAR2(3200) ; -- Compare To
398   l_prev_date    DATE;            -- Previous Date
399   l_prod_catg    VARCHAR2(3200) ; -- Product Category  Not required for this report
400   l_prod         VARCHAR2(3200) ; -- Product  Not required for this report
401   l_gp_currency  VARCHAR2(15) ; --Global Primary Currency  Not required for this report
402   l_gs_currency  VARCHAR2(15) ; --Global Secondary Curr  Not required for this report
403   l_f_currency   VARCHAR2(15) ;   -- Functional Currency  Not required for this report
404   l_gp_amount    NUMBER ;         -- Primary Currency Amount  Not required for this report
405   l_gs_amount    NUMBER ;         -- Secondary Currency Amount  Not required for this report
406   l_f_amount     NUMBER ;         -- Functional Amount  Not required for this report
407 
408 
409   --FND Logging
410   l_full_path   VARCHAR2(50) ;
414   --Profile is : FND: Debug Log Enabled and FND: Debug Log Level for Log Level
411   --gaflog_value    CONSTANT VARCHAR2(10);
412 
413   gaflog_value     CONSTANT VARCHAR2(10) := fnd_profile.value('AFLOG_ENABLED');
415 
416 BEGIN
417 
418   --gaflog_value   := fnd_profile.value('AFLOG_ENABLED');
419 
420   IF gaflog_value ='Y' AND (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
421     fnd_log.string(fnd_log.level_statement,l_full_path,'Visit Trend Report BEGIN');
422   END IF;
423 
424   --initialization
425 
426   l_gp_currency   := '''FII_GLOBAL1''' ; --Global Primary Currency  Not required for this report
427   l_gs_currency   := '''FII_GLOBAL2''' ; --Global Secondary Curr  Not required for this report
428 
429    l_full_path    := 'ibw.plsql.ibwbvtrb.get_visit_trend_sql';
430    --gaflog_value   := fnd_profile.value('AFLOG_ENABLED');
431 --Fetch all the Parameters into the Local Variables.
432 
433   IF gaflog_value ='Y' AND (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
434      fnd_log.string(fnd_log.level_procedure,l_full_path,'Before the Call to UTL Package to get parameter values');
435   END IF;
436 
437     IBW_BI_UTL_PVT.GET_PAGE_PARAMETERS
438    (
439      P_PMV_PARAMETERS   =>  p_pmv_parameters,
440      X_PERIOD_TYPE	    =>  l_period_type,
441      X_SITE             =>  l_site,
442      X_CURRENCY_CODE    =>  l_currency,   --Not Wanted
443      X_SITE_AREA        =>  l_site_area,  --Not Wanted
444      X_PAGE             =>  l_page,       --Not Wanted
445      X_REFERRAL         =>  l_referral,   --Not Wanted
446      X_PROD_CAT         =>  l_prod_catg,  --Not Wanted
447      X_PROD             =>  l_prod,       --Not Wanted
448      X_CUST_CLASS       =>  l_cust_class,
449      X_CUST             =>  l_cust,        --Not Wanted
450      X_CAMPAIGN		      =>  l_campaign,   --Not Wanted
451      X_VIEW_BY          =>  l_view_by     --Not Wanted
452     );
453 
454  IF gaflog_value ='Y' AND (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
455     fnd_log.string(fnd_log.level_procedure,l_full_path,'After the Call to UTL Package');
456  END IF;
457 
458  IF gaflog_value ='Y' AND (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
459     fnd_log.string(fnd_log.level_statement,l_full_path,'l_site : ' || l_site || ' l_cust_class : ' || l_cust_class );
460  END IF;
461 
462 
463 
464   --Initializing section starts
465 
466   l_outer_where_clause  := '';
467   l_custom_rec		:=  BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
468 
469   --Initializing section completed
470 
471   --Get the Table List
472 
473   l_table_list   := ' IBW_VISIT_CUST_TIME_MV VISIT_MV' ||  --  To be replaced by the mv when it is ready
474                     ' ,FII_TIME_RPT_STRUCT_V CAL' ||
475 	 	    ' ,FII_PARTY_MKT_CLASS CUST_CLASS_MAP';
476 
477 --  l_inner_where_clause (time specific)
478 
479 
480 -- Initialising where clause based on the parameter selection
481 
482   IF upper(l_site) <> 'ALL' THEN
483     l_outer_where_clause   := l_outer_where_clause ||
484 				' AND VISIT_MV.SITE_ID in (&SITE+SITE)' ;
485 
486   ELSE
487     l_table_list := l_table_list  || ', IBW_BI_MSITE_DIMN_V SITE';
488     l_outer_where_clause   := l_outer_where_clause ||
489 				' AND VISIT_MV.SITE_ID = SITE.ID ';
490   END IF;
491 
492   IF upper(l_cust_class) <> 'ALL' THEN
493 
494     l_outer_where_clause   := l_outer_where_clause ||
495 				' AND VISIT_MV.CUSTOMER_ID = CUST_CLASS_MAP.PARTY_ID '||
496 				' AND CUST_CLASS_MAP.class_code in (&FII_TRADING_PARTNER_CLASS+FII_TRADING_PARTNER_MKT_CLASS)';
497   ELSE
498     l_table_list      := l_table_list || ' ,FII_TRADING_PARTNER_MKTCLASS_V CUSTCLASS';
499     l_outer_where_clause    := l_outer_where_clause ||
500 				' AND VISIT_MV.CUSTOMER_ID = CUST_CLASS_MAP.PARTY_ID '||
501 				' AND CUST_CLASS_MAP.class_code = CUSTCLASS.ID ';
502   END IF;
503 
504 
505  /*************************************************************************/
506  /* Metrics used in Visit Trend Report:                                   */
507  /* IBW_VAL10                 : Prior                                     */
508  /* IBW_VAL1                  : Visits                                    */
509  /* IBW_VAL2                  : Change					                          */
510  /* IBW_VAL3                  : Repeat Visits                             */
511  /* IBW_VAL4                  : Average Visit Duartion (minutes)          */
512  /* IBW_VAL5                  : Average Page Views                        */
513  /* IBW_VAL6                  : Daily Unique Visitors                     */
514  /* IBW_VAL7                  : Opt Outs                                  */
515  /* IBW_VAL11                 : Prior                                     */
516  /* IBW_VAL8                  : Browse to Buy                             */
517  /* IBW_VAL9                  : Change                                    */
518  /*                                                                       */
519  /*********************Bind Parameters used are****************************/
520  /*                                                                               */
521  /* &BIS_CURRENT_ASOF_DATE          :  AS OF DATE selected in the report          */
522  /* &BIS_PREVIOUS_ASOF_DATE         : Previous period as of date                  */
523  /* &BIS_CURRENT_REPORT_START_DATE  : Current period start date                   */
524  /* &BIS_PREVIOUS_REPORT_START_DATE : Previous period startdate                   */
525  /* &BIS_NESTED_PATTERN             :  Record Type Id of the Period Type selected */
526  /*********************************************************************************/
527 
528 
529 --  Not returning value for IBW_VAL2 and IBW_VAL9 as per bug # 4772549.
530 
531 l_custom_sql:= 'SELECT  fii.NAME VIEWBY,
535 		           decode(nvl(visit_duration_cur,0),0,null,(nvl(visit_duration_cur,0)/nvl(visits_cur,0))/60000) IBW_VAL4, --Bug#5014704 Issue# 1.  Returned null instead of 0 for bug 5253591
532                nvl(VISITS_PRE,0)  IBW_VAL10, --Bug#4727078 Issue#:21
533 	             nvl(VISITS_CUR,0)  IBW_VAL1,
534                nvl(REPEAT_VISITS_CUR,0) IBW_VAL3,
536                decode(nvl(visits_cur,0),0,null, nvl(page_views_cur,0)/nvl(visits_cur,0)) IBW_VAL5,      -- Returned null instead of 0 for bug 5253591
537                nvl(DAILY_UNIQ_VISITORS_CUR,0) IBW_VAL6,
538                nvl(OPT_OUTS_CUR,0) IBW_VAL7,
539                decode(nvl(visits_pre,0),0,null, (nvl(orders_site_visits_pre,0) /nvl(visits_pre,0))*100)  IBW_VAL11, --Bug#4727078 Issue#:21    Returned null instead of 0 for bug 5253591
540                decode(nvl(visits_cur,0),0,null, (nvl(orders_site_visits_cur,0) /nvl(visits_cur,0))*100)  IBW_VAL8    -- Returned null instead of 0 for bug 5253591
541 	     FROM (
542                SELECT
543                  START_DATE,
544                  sum(visits_c) VISITS_CUR,
545                  sum(visits_p) VISITS_PRE,
546                  sum(repeat_visits_c) REPEAT_VISITS_CUR,
547                  sum(visit_duration_c) VISIT_DURATION_CUR,
548                  sum(page_views_c) page_views_cur,
549                  sum(daily_uniq_visitors_c) DAILY_UNIQ_VISITORS_CUR,
550                  sum(opt_outs_c) OPT_OUTS_CUR,
551                  sum(orders_site_visits_C) orders_site_visits_cur,
552                  sum(orders_site_visits_P) orders_site_visits_pre
553 		           FROM
554 			         (
555                  SELECT
556                    dates.start_date  START_DATE,
557                    decode(dates.period, ''C'',visits,0) visits_C,
558                    decode(dates.period, ''P'',visits,0) visits_P,
559                    decode(dates.period, ''C'',repeat_visits,0) repeat_visits_c,
560                    decode(dates.period, ''C'',visit_duration,0) visit_duration_c,
561                    decode(dates.period, ''C'',page_views,0) page_views_c,
562                    decode(dates.period, ''C'',daily_uniq_visitors,0) daily_uniq_visitors_c,
563                    decode(dates.period, ''C'',opt_outs,0) opt_outs_c,
564                    decode(dates.period, ''C'',orders_site_visits,0) orders_site_visits_C,
565                    decode(dates.period, ''P'',orders_site_visits,0) orders_site_visits_P
566 				         FROM
567 				         (
568                    SELECT
569                    fii.start_date START_DATE,
570                    ''C'' PERIOD,
571                    least(fii.end_date, &BIS_CURRENT_ASOF_DATE) REPORT_DATE
572                    FROM '||l_period_type||'   fii
573                    WHERE fii.start_date BETWEEN &BIS_CURRENT_REPORT_START_DATE AND &BIS_CURRENT_ASOF_DATE
574                     UNION ALL
575             SELECT
576               p2.start_date START_DATE,
577               ''P'' PERIOD,
578               p1.report_date REPORT_DATE
579             FROM
580               (
581                 SELECT
582                   REPORT_DATE,
583                   rownum id
584                 FROM
585                 (
586                 SELECT
587                   least(time_dim.end_date, &BIS_PREVIOUS_ASOF_DATE) REPORT_DATE
588                 FROM
589                   ' ||l_period_type||'   time_dim
590                 WHERE
591                   time_dim.start_date BETWEEN &BIS_PREVIOUS_REPORT_START_DATE AND &BIS_PREVIOUS_ASOF_DATE
592                 ORDER BY time_dim.start_date DESC
593                 )
594               )  p1,
595               (
596               SELECT
597                 START_DATE,
598                 rownum id
599               FROM
600               (
601                 SELECT time_dim.start_date START_DATE
602                 FROM  ' ||l_period_type||'  time_dim
603                 WHERE time_dim.start_date BETWEEN &BIS_CURRENT_REPORT_START_DATE AND &BIS_CURRENT_ASOF_DATE
604                 ORDER BY time_dim.start_date DESC
605                 )
606               ) p2
607 					  WHERE p1.id(+) = p2.id) dates, '|| l_table_list ||'
608 				WHERE
609 				   cal.report_date	  = dates.report_date AND
610 				   VISIT_MV.time_id  =	cal.time_id AND
611 				   VISIT_MV.period_type_id = cal.period_type_id AND
612 				   bitand(cal.record_type_id, &BIS_NESTED_PATTERN) = cal.record_type_id'|| l_outer_where_clause ||')
613 		          GROUP BY START_DATE
614                        ) s,'|| l_period_type||' fii
615 WHERE fii.start_date BETWEEN &BIS_CURRENT_REPORT_START_DATE AND &BIS_CURRENT_ASOF_DATE AND
616 fii.start_date = s.start_date(+)
617 ORDER BY fii.start_date';
618 
619 -- IF gaflog_value ='Y' AND (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
620 --    fnd_log.string(fnd_log.level_unexpected,l_full_path,'Visit Trend Portlet Query : ' || l_custom_sql);
621 -- END IF;
622 
623 
624 
625     x_custom_sql  := l_custom_sql;
626 
627 
628 
629 
630 
631 
632 EXCEPTION
633    WHEN OTHERS THEN
634     if (fnd_log.level_unexpected >= fnd_log.g_current_runtime_level) then
635       fnd_log.string(fnd_log.level_unexpected,l_full_path,SQLERRM);
636     end if;
637 END GET_VISIT_TREND_SQL;
638 
639 
640 -- Procedure for the Web Campaign Analysis Report
641 
642 /*
643 ------------------------------------------------------------
644 Procedure name       : GET_WEB_CAMPAIGN_SQL
645 Parameters           :
646 IN                   : p_param        pl/sql table
647 OUT                  : x_custom_sql   varchar2
648 OUT                  : x_cusom_output pl/sql table
649 
650 Description          : This procedure will be called from the Campaign Analysis report
651 
652 Modification History :
653 Date        Name       Desc
654 ----------  ---------  ----------------------------------
658 */
655 03/08/2005  Narao      Campaign Analysis Report UI Query.
656 
657 ------------------------------------------------------------
659 
660 PROCEDURE GET_WEB_CAMPAIGN_SQL  ---  change name to campaign_nontrend_sql
661 ( p_param         IN  BIS_PMV_PAGE_PARAMETER_TBL
662 , x_custom_sql    OUT NOCOPY VARCHAR2
663 , x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL
664 )
665 IS
666 --Generic Variables
667   l_custom_sql     VARCHAR2(15000) ; --Final Sql.
668   l_asof_dt        DATE            ; --As of Date
669   l_site           VARCHAR2(3200)  ; --Site Id
670   l_period_type    VARCHAR2(3200)  ; --Period Type
671   l_parameter_name VARCHAR2(3200)  ; --Parameter Name
672   l_page           VARCHAR2(3200)  ; -- Page
673   l_site_area      VARCHAR2(3200)  ; -- Site Area
674   l_view_by        VARCHAR2(3200)  ;
675   l_rec_id         NUMBER          ;
676   l_from           VARCHAR2(32000) ;
677   l_where          VARCHAR2(32000) ;
678   l_outer_select   VARCHAR2(30000) ;
679   l_inner_select   VARCHAR2(30000) ;
680   l_custom_rec     BIS_QUERY_ATTRIBUTES;
681   l_dimension_id   NUMBER  ;
682   l_dimension_view VARCHAR2(3200) ;
683   l_dim_where      VARCHAR2(3200) ;
684   l_view_by_name   VARCHAR2(100)  ;     --Either Campaign or Site
685   l_group_by       VARCHAR2(1000) ;
686   l_inner_group_by VARCHAR2(1000) ;
687   l_outer_group_by VARCHAR2(1000) ;
688   l_order_by       VARCHAR2(100)  ;
689   l_url_str        VARCHAR2(1000) ; --For URL String Construction.
690   l_gp_currency    VARCHAR2(30) := '''FII_GLOBAL1''' ; --Global Primary Currency
691   l_gs_currency    VARCHAR2(30) := '''FII_GLOBAL2''' ; --Global Secondary Curr
692   l_f_currency     VARCHAR2(30) ;   -- Functional Currency
693   l_f_amount       NUMBER ;         -- Functional Amount
694   l_having         VARCHAR2(2000);
695 
696   --Un wanted Variables
697 
698   l_referral       VARCHAR2(3200) ; -- Referral Dimension
699   l_cust_class     VARCHAR2(3200) ; -- Customer Classification
700   l_cust           VARCHAR2(3200) ; -- Customer
701   l_campaign       VARCHAR2(3200) ; -- Campaign
702   l_currency       VARCHAR2(3200) ; -- Currency
703   l_comp_to        VARCHAR2(3200) ; -- Compare To
704   l_ord_by         VARCHAR2(3200) ; -- Order By
705   l_prev_date      DATE;            -- Previous Date
706   l_prod_catg      VARCHAR2(3200) ; -- Product Category
707   l_prod           VARCHAR2(3200) ; -- Product
708   --FND Logging
709   l_full_path      VARCHAR2(50) ;
710 
711   --Profile is : FND: Debug Log Enabled
712   gaflog_value     CONSTANT VARCHAR2(10) := fnd_profile.value('AFLOG_ENABLED');
713 
714 
715 BEGIN
716 
717 
718   /*
719   Open issues :
720   1. fnd_log.string(fnd_log.level_unexpected,l_full_path,l_custom_sql);  to be removed
721   2. gaflog_value     CONSTANT VARCHAR2(10) := fnd_profile.value('AFLOG_ENABLED') to be removed
722   3. Sum of Grand Totals with Division to be handled.
723 
724   */
725 
726   --Fetch all the Parameters into the Local Variables.
727   if gaflog_value ='Y' and (fnd_log.level_statement >= fnd_log.g_current_runtime_level) then
728     fnd_log.string(fnd_log.level_statement,l_full_path,'begin');
729   end if;
730 
731 
732   l_full_path := 'ibw.plsql.ibwrepab.page_int_nontrend_sql'; --This is the path which would be referred in fnd_log_messages.module
733 
734   IF gaflog_value ='Y' AND (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
735     fnd_log.string(fnd_log.level_procedure,l_full_path,'Before the Call to UTL Package');
736   END IF;
737 
738   --To get all the Page Parameters.
739   IBW_BI_UTL_PVT.GET_PAGE_PARAMETERS
740     (
741         p_pmv_parameters  =>  p_param
742       , x_period_type     =>  l_period_type    --Period type
743       , x_site            =>  l_site           --Site Id
744       , x_currency_code   =>  l_currency       --Currency
745       , x_site_area       =>  l_site_area      --Not used
746       , x_page            =>  l_page           --Not used
747       , x_referral        =>  l_referral       --Not Used
748       , x_prod_cat        =>  l_prod_catg      --Not Used
749       , x_prod            =>  l_prod           --Not Used
750       , x_cust_class      =>  l_cust_class     --Not Used
751       , x_cust            =>  l_cust           --Not Used
752       , x_campaign        =>  l_campaign       --Campaign
753       , x_view_by         =>  l_view_by        --Either Campaign or Site.
754     );
755 
756   IF gaflog_value ='Y' AND (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
757     fnd_log.string(fnd_log.level_procedure,l_full_path,'After the Call to UTL Package');
758   END IF;
759 
760   if gaflog_value ='Y' and (fnd_log.level_statement >= fnd_log.g_current_runtime_level) then
761     fnd_log.string(fnd_log.level_statement,l_full_path,'l_site' || l_site ||' l_view_by '|| l_view_by ||' l_campaign: '|| l_campaign||' l_currency '|| l_currency );
762   end if;
763 
764 
765   l_where           := '';
766   l_url_str         := null; --Initialising the String to Null
767   l_custom_rec      :=  BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
768 
769   --Initializing section starts
770   /********************Metrics in Campaign Analysis Report*********************/
771    /* IBW_VAL1                  : Visits                                */
772    /* IBW_VAL2                  : Average Visit Duration					      */
773    /* IBW_VAL3                  : Average Page Views                    */
774    /* IBW_VAL4                  : Daily Unique Visitors					        */
775    /* IBW_VAL5                  : Registrations                         */
776    /* IBW_VAL6                  : A Leads					                      */
777    /* IBW_VAL7                  : Carts                                 */
778    /* IBW_VAL8                  : Cart Conversion Ratio					        */
782    /* IBW_G_TOT3                : Sum of Average Page Views             */
779    /* IBW_VAL9                  : Booked Orders Amount 					        */
780    /* IBW_G_TOT1                : Sum of Visits                         */
781    /* IBW_G_TOT2                : Sum of Average Visit Duration         */
783    /* IBW_G_TOT4                : Sum of Daily Unique Visitors          */
784    /* IBW_G_TOT5                : Sum of Registrations                  */
785    /* IBW_G_TOT6                : Sum of A Leads                        */
786    /* IBW_G_TOT7                : Sum of Carts                          */
787    /* IBW_G_TOT8                : Sum of Cart Conversion Ratio	        */
788    /* IBW_G_TOT9                : Sum of Booked Orders Amount 	        */
789    /*********************Bind Parameters used are****************************/
790 
791    /* &BIS_CURRENT_ASOF_DATE  :  AS OF DATE selected in the report          */
792    /* &BIS_NESTED_PATTERN     :  Record Type Id of the Period Type selected */
793 
794     /************************************************************************/
795 
796   -- Added by pipandey for Bug# 4687647 Issue# 3
797   -- Changed Average Visit Duration - added division by 60000,Cart Conversion  multiply by 100
798   --------------------------------------------------------------------------------------------------------------
799   l_outer_select  := ' nvl(visits,0) IBW_VAL1, ' ||                                                --Visits
800                      ' DECODE(visits,0,null,null,null,((visit_duration/visits)/60000)) IBW_VAL2, ' || --Average Visit Duration
801                      ' DECODE(visits,0,null,null,null,page_views/ visits) IBW_VAL3, ' ||    --Average Page Views
802                      ' nvl(daily_uniq_visitors,0) IBW_VAL4, ' ||                                   --Daily Unique Visitors
803                      ' nvl(web_registrations,0) IBW_VAL5, ' ||                                     --Registrations
804                      ' nvl(a_leads,0) IBW_VAL6, ' ||                                               --A Leads
805                      ' nvl(carts,0) IBW_VAL7, ' ||                                                 --Carts
806                      ' DECODE(carts,0,null,null,null, ((orders/carts)*100)) IBW_VAL8, ' ||          --Cart Conversion Ratio.
807                      ' nvl(booked_orders_amount,0) IBW_VAL9, ' ||                                  --Booked Orders Amount
808                      --Changes for the ER 4760433
809                       ' nvl(booked_orders_amount,0) IBW_VAL11, ' ||
810                        ' nvl(a_leads,0) IBW_VAL10, ' ||
811                      --Changes for the ER 4760433
812                       --For Grand Totals
813                      ' SUM(nvl(visits,0)) OVER() IBW_G_TOT1, ' ||
814                      --' SUM(DECODE(visits,0,null,null,null, visit_duration / visits)) OVER() IBW_G_TOT2, '||    Grand Total Change
815                      --' SUM(DECODE(visits,0,null,null,null,page_views/ visits)) OVER() IBW_G_TOT3, ' ||         Grand Total Change
816                      ' DECODE(SUM(visits) over(),0,null,null,null, (((SUM(visit_duration) over() / SUM(visits) over()))/60000)) IBW_G_TOT2, '||
817                      ' DECODE(SUM(visits) over(),0,null,null,null,(SUM(page_views) over()/ SUM(visits) over())) IBW_G_TOT3, ' ||
818                      ' SUM(nvl(daily_uniq_visitors,0)) OVER() IBW_G_TOT4, '||
819                      ' SUM(nvl(web_registrations,0)) OVER() IBW_G_TOT5, ' ||
820                      ' SUM(nvl(a_leads,0)) OVER() IBW_G_TOT6, '||
821                      ' SUM(nvl(carts,0)) OVER() IBW_G_TOT7, ' ||
822                      --' SUM(DECODE(Carts,0,null,null,null, orders / carts)) OVER() IBW_G_TOT8, '||  Grand Total Change
823                      ' DECODE(SUM(Carts) over(),0,null,null,null, ((SUM(orders) over() / SUM(Carts) over())*100))  IBW_G_TOT8, '||
824                      ' SUM(nvl(booked_orders_amount,0)) OVER() IBW_G_TOT9 ';
825 
826   if gaflog_value ='Y' and (fnd_log.level_statement >= fnd_log.g_current_runtime_level) then
827     fnd_log.string(fnd_log.level_statement,l_full_path,'After Outer Select'  );
828   end if;
829 
830   l_inner_select  := ' SUM(visits) visits, SUM(visit_duration) visit_duration, ' ||
831                      ' SUM(page_views) page_views,  SUM(daily_uniq_visitors) daily_uniq_visitors, ' ||
832                      ' SUM(web_registrations) web_registrations, SUM(a_leads) a_leads, ' ||
833                      ' SUM(carts) carts, SUM(orders) orders, ' ||
834                      ' SUM(decode(:l_currency,:l_gp_currency,booked_amt_g,:l_gs_currency,booked_amt_g1,cmp_mv.currency_cd_f,booked_amt_f)) booked_orders_amount ';
835                      --' SUM( booked_amt_g ) a_booked_orders_amount ';
836   l_having        := ' SUM(visits) > 0 ' ||
837                      ' OR SUM(visit_duration) > 0 ' ||
838                      ' OR SUM(page_views) > 0 ' ||
839                      ' OR SUM(daily_uniq_visitors) > 0 ' ||
840                      ' OR SUM(web_registrations) > 0 ' ||
841                      ' OR SUM(a_leads) > 0 ' ||
842                      ' OR SUM(carts) > 0 '   ||
843                      ' OR SUM(orders) > 0 ' ||
844                      ' OR SUM(decode(:l_currency,:l_gp_currency,booked_amt_g,:l_gs_currency,booked_amt_g1,cmp_mv.currency_cd_f,booked_amt_f)) > 0 ' ;
845 
846   if gaflog_value ='Y' and (fnd_log.level_statement >= fnd_log.g_current_runtime_level) then
847     fnd_log.string(fnd_log.level_statement,l_full_path,'After Inner Select'  );
848   end if;
849 
850   l_from          := ' IBW_CMPANLYS_CMPDIM_TIME_MV CMP_MV ' ||
851                      ' , FII_TIME_RPT_STRUCT_V      CAL    ';
852 
853    l_where         := ' CAL.report_date = &BIS_CURRENT_ASOF_DATE ' ||
854                       ' AND CAL.period_type_id = CMP_MV.period_type_id ' ||
855                       ' AND BITAND(CAL.RECORD_TYPE_ID,&BIS_NESTED_PATTERN)= CAL.RECORD_TYPE_ID '||
856                       ' AND CMP_MV.TIME_ID = CAL.TIME_ID ' ||
857                       ' AND CAL.CALENDAR_ID = -1 '; --Indicates Enterprise Calendar
858 
859   if gaflog_value ='Y' and (fnd_log.level_statement >= fnd_log.g_current_runtime_level) then
860     fnd_log.string(fnd_log.level_statement,l_full_path,'Before the View By Condition'  );
864   IF l_view_by = 'SITE+SITE'   THEN --View by is Site
861   end if;
862 
863   -- Fetching Site Name or Campaign Name according to the View by
865 
866       l_url_str         := ' , NULL ';
867       l_inner_select    := ' SITE.VALUE VIEW_BY , SITE.ID VIEWBYID, ' || l_inner_select;  --SITE.VALUE will give the Site Name.
868       l_outer_select    := ' VIEW_BY VIEWBY,VIEWBYID,  NULL IBW_ATTR1, ' || l_outer_select || l_url_str || ' IBW_URL1 ' ; --IBW_ATTR1 would be hidden when View by is Site.
869       l_from            := l_from || ' , IBW_BI_MSITE_DIMN_V SITE '; --Site Dimension View.
870       l_where           := l_where || ' AND CMP_MV.SITE_ID = SITE.ID ';
871       l_inner_group_by  := ' SITE.VALUE , SITE.ID';
872 
873 
874       IF UPPER(l_site) <> 'ALL' THEN
875         l_where := l_where || ' AND CMP_MV.SITE_ID IN (&SITE+SITE) ' ; --In condition as Site is Multi Select
876         --Else is not required as the join of CMP_MV.SITE_ID = SITE.ID is already done.
877       END IF;
878 
879       --Added by pipandey for Bug# 4687647 Issue# 3
880       IF UPPER(l_campaign) <> 'ALL' THEN
881         l_where := l_where || 'AND CMP_MV.PRIOR_ID = (&CAMPAIGN+CAMPAIGN) '; --Equal condition as Campaign dimension is single select
882       ELSE
883         l_where := l_where || 'AND CMP_MV.PRIOR_ID IS NULL ';
884       END IF;
885 
886   ELSIF l_view_by = 'CAMPAIGN+CAMPAIGN' THEN --View by is Campaign
887 
888       l_url_str:='pFunctionName=IBW_BI_CMPGNANLYS_RPT&pParamIds=Y&VIEW_BY='||l_view_by||'&VIEW_BY_NAME=VIEW_BY_ID';
889       l_url_str         :=' ,DECODE(nvl(object_type,'||''''||'CSCH'||''''||'),'||''''||'CSCH'||''''||',NULL,'||''''||l_url_str||''''||' ) ';
890       l_inner_select    := ' CAMPAIGN.NAME VIEW_BY , CAMPAIGN.SOURCE_CODE_ID VIEWBYID ,CAMPAIGN.OBJECT_TYPE_MEAN CAMPAIGN_TYPE, CAMPAIGN.OBJECT_TYPE OBJECT_TYPE, ' || l_inner_select;  --Campaign Name and Campaign Type
891       l_outer_select    := ' VIEW_BY VIEWBY ,VIEWBYID,DECODE(object_type,''CSCH'',''N'',''Y'') DRILLPIVOTVB, CAMPAIGN_TYPE IBW_ATTR1, ' ||l_outer_select || l_url_str || ' IBW_URL1 ' ;
892       l_from            := l_from || ' , BIM_I_OBJ_NAME_MV CAMPAIGN ' ;
893       l_where           := l_where || ' AND CMP_MV.PARENT_SOURCE_CODE_ID = CAMPAIGN.SOURCE_CODE_ID AND CAMPAIGN.language = USERENV(''LANG'') ';
894       l_inner_group_by  := ' CAMPAIGN.NAME ,   CAMPAIGN.SOURCE_CODE_ID, CAMPAIGN.OBJECT_TYPE_MEAN, CAMPAIGN.OBJECT_TYPE ' ;
895 
896       IF UPPER(l_campaign) <> 'ALL' THEN
897         l_where := l_where || 'AND CMP_MV.PRIOR_ID = (&CAMPAIGN+CAMPAIGN) '; --Equal condition as Campaign dimension is single select
898       ELSE
899         l_where := l_where || 'AND CMP_MV.PRIOR_ID IS NULL ';
900       END IF;
901 
902       IF UPPER(l_site) <> 'ALL' THEN
903         l_where := l_where || ' AND CMP_MV.SITE_ID IN (&SITE+SITE) ' ; --In condition as Site is Multi Select
904       ELSE
905         l_from  := l_from || ', IBW_BI_MSITE_DIMN_V SITE ';
906         l_where := l_where || ' AND CMP_MV.SITE_ID = SITE.ID ';
907       END IF;
908 
909   END IF; --End if for l_view_by
910 
911   -- Final Query
912   l_custom_sql := 'SELECT ' || l_outer_select ||
913              ' FROM ' ||
914                     ' (SELECT '   || l_inner_select ||
915                     ' FROM '     || l_from ||
916                     ' WHERE '    || l_where ||
917                     ' GROUP BY ' || l_inner_group_by ||
918                     ' HAVING '   || l_having ||
919                    ' ) '  ||
920              ' &ORDER_BY_CLAUSE ' ;
921 
922   --fnd_log.string(fnd_log.level_unexpected,l_full_path,l_custom_sql);  --To be removed Later
923 
924 
925  if gaflog_value ='Y' and (fnd_log.level_statement >= fnd_log.g_current_runtime_level) then
926     fnd_log.string(fnd_log.level_statement,l_full_path,'l_outer_select' || l_outer_select);
927     fnd_log.string(fnd_log.level_statement,l_full_path,'l_inner_select' || l_inner_select);
928     fnd_log.string(fnd_log.level_statement,l_full_path,'l_from' || l_from);
929     fnd_log.string(fnd_log.level_statement,l_full_path,'l_where' || l_where);
930     fnd_log.string(fnd_log.level_statement,l_full_path,'l_inner_group_by ' || l_inner_group_by);
931     fnd_log.string(fnd_log.level_statement,l_full_path,'l_custom_sql' || l_custom_sql);
932   end if;
933 
934 
935    --Build the Tokens
936    -- Out parameters
937 
938   x_custom_sql := l_custom_sql;
939 
940   x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
941 
942 
943   l_custom_rec.attribute_name := ':l_currency' ;
944   l_custom_rec.attribute_value:= l_currency;
945   l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
946   l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
947   x_custom_output.EXTEND;
948   x_custom_output(1) := l_custom_rec;
949 
950   l_custom_rec.attribute_name := ':l_gp_currency' ;
951   l_custom_rec.attribute_value:= l_gp_currency;
952   l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
953   l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
954   x_custom_output.EXTEND;
955   x_custom_output(2) := l_custom_rec;
956 
957   l_custom_rec.attribute_name := ':l_gs_currency' ;
958   l_custom_rec.attribute_value:= l_gs_currency;
959   l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
960   l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
961   x_custom_output.EXTEND;
962 
963   x_custom_output(3) := l_custom_rec;
964 
965  if gaflog_value ='Y' and (fnd_log.level_statement >= fnd_log.g_current_runtime_level) then
966    fnd_log.string(fnd_log.level_statement,l_full_path,'end');
967  end if;
968 
969 EXCEPTION
970    WHEN OTHERS THEN
971     if (fnd_log.level_unexpected >= fnd_log.g_current_runtime_level) then
972       fnd_log.string(fnd_log.level_unexpected,l_full_path,SQLERRM);
973     end if;
974 END GET_WEB_CAMPAIGN_SQL;
975 
976 -- Procedure for the KPI's
977 
978 /*
982 IN                   : p_param        pl/sql table
979 ----------------------------------------------------------------------------
980 Procedure name       : GET_KPI_SQL
981 Parameters
983 OUT                  : x_custom_sql   varchar2
984 OUT                  : x_cusom_output pl/sql table
985 
986 Description          : This procedure will be called from
987                        the 'KPI in the Site Management Dashboard '
988 ------------------------------------------------------------------------------
989 */
990 
991 PROCEDURE GET_KPI_SQL(
992                             p_pmv_parameters IN BIS_PMV_PAGE_PARAMETER_tbl,
993                             x_custom_sql     OUT NOCOPY VARCHAR2,
994                             x_custom_output  OUT NOCOPY bis_query_attributes_TBL )
995 IS
996 
997 -- Generic Variables
998 
999   l_custom_sql            VARCHAR2(15000) ; --Final Sql.
1000   l_custom_sql1           VARCHAR2(15000) ; --Final Sql.
1001   l_custom_sql2           VARCHAR2(15000) ; --Final Sql.
1002   l_asof_dt               DATE;             --As of Date
1003   l_site                  VARCHAR2(3200) ;  --Site Id
1004   l_period_type           VARCHAR2(1000) ;  --Period Type
1005   l_parameter_name        VARCHAR2(3200) ;  --Parameter Name
1006   l_page                  VARCHAR2(3200) ;  -- Page  Not required for this report
1007   l_site_area             VARCHAR2(3200) ;  -- Site Area  Not required for this report
1008   l_view_by               VARCHAR2(3200);   -- View By   Not required for this report
1009   l_rec_id                NUMBER;
1010   l_table_list            VARCHAR2(3200) ;
1011   l_inner_where_clause    VARCHAR2(3200) ;  --??????
1012   l_outer_where_clause    VARCHAR2(3200) ; --?????
1013   l_custom_rec            BIS_QUERY_ATTRIBUTES;
1014   l_dimension_id          NUMBER;
1015   l_dimension_view        VARCHAR2(3200);
1016   l_dim_where             VARCHAR2(3200);
1017   l_group_by              VARCHAR2(1000);
1018   l_order_by              VARCHAR2(100);
1019   l_referral              VARCHAR2(3200) ; -- Referral Dimension  Not required for this report
1020   l_campaign	            VARCHAR2(3200) ;  --  Not required for this report
1021 
1022 
1023 -- Specific for trend reports
1024 
1025   l_timetable        VARCHAR2(3200);
1026   l_timespan         NUMBER;
1027   l_sequence         NUMBER;
1028   l_cur_start        DATE;
1029   l_mid_start        DATE;
1030   l_prev_start       DATE;
1031   l_pprev_start      DATE;
1032   l_pcur_start       DATE;
1033   l_cur_year         NUMBER;
1034   l_prev_year        NUMBER;
1035 
1036 -- Specific Variables
1037 
1038   l_cust_class   VARCHAR2(3200) ; -- Customer Classification  Not required for this report
1039   l_cust         VARCHAR2(3200) ; -- Customer  Not required for this report
1040   l_currency     VARCHAR2(3200) ; -- Currency
1041   l_comp_to      VARCHAR2(3200) ; -- Compare To
1042   l_prev_date    DATE;            -- Previous Date
1043   l_prod_catg    VARCHAR2(3200) ; -- Product Category  Not required for this report
1044   l_prod         VARCHAR2(3200) ; -- Product  Not required for this report
1045   l_gp_currency  VARCHAR2(30) ; --Global Primary Currency
1046   l_gs_currency  VARCHAR2(30)  ; --Global Secondary Curr
1047   l_f_currency   VARCHAR2(15) ;   -- Functional Currency
1048   l_gp_amount    NUMBER ;         -- Primary Currency Amount
1049   l_gs_amount    NUMBER ;         -- Secondary Currency Amount
1050   l_f_amount     NUMBER ;         -- Functional Amount
1051 
1052   l_full_path       VARCHAR2(50);
1053   gaflog_value      VARCHAR2(10);
1054 
1055 
1056 BEGIN
1057 
1058   --Profiles for FND Debugging are  : FND: Log Enabled , FND: Log Level
1059   l_full_path  := 'ibw.plsql.ibwbvckb.GET_KPI_SQL'; --This would be stored in FND_LOG_MESSAGES.MODULE column
1060   gaflog_value := fnd_profile.value('AFLOG_ENABLED');
1061 
1062 -- initialization
1063 
1064   l_gp_currency   := '''FII_GLOBAL1''' ;
1065   l_gs_currency   := '''FII_GLOBAL2''' ;
1066 
1067 --Fetch all the Parameters into the Local Variables.
1068 
1069   IF gaflog_value ='Y' AND (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1070     fnd_log.string(fnd_log.level_statement,l_full_path,'Begin');
1071   END IF;
1072 
1073   IF gaflog_value ='Y' AND (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
1074     fnd_log.string(fnd_log.level_procedure,l_full_path,'Before the Call to UTL Package');
1075   END IF;
1076 
1077     IBW_BI_UTL_PVT.GET_PAGE_PARAMETERS
1078    (
1079      P_PMV_PARAMETERS   =>  p_pmv_parameters,
1080      X_PERIOD_TYPE	    =>  l_period_type,
1081      X_SITE             =>  l_site,
1082      X_CURRENCY_CODE    =>  l_currency,
1083      X_SITE_AREA        =>  l_site_area,  --Not Wanted
1084      X_PAGE             =>  l_page,       --Not Wanted
1085      X_REFERRAL         =>  l_referral,   --Not Wanted
1086      X_PROD_CAT         =>  l_prod_catg,  --Not Wanted
1087      X_PROD             =>  l_prod,       --Not Wanted
1088      X_CUST_CLASS       =>  l_cust_class,  --Not Wanted
1089      X_CUST             =>  l_cust,        --Not Wanted
1090      X_CAMPAIGN		      =>  l_campaign,   --Not Wanted
1091      X_VIEW_BY          =>  l_view_by     --Not Wanted
1092     );
1093 
1094   IF gaflog_value ='Y' AND (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
1095     fnd_log.string(fnd_log.level_procedure,l_full_path,'After the Call to UTL Package');
1096   END IF;
1097 
1098   IF gaflog_value ='Y' AND (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1099     fnd_log.string(fnd_log.level_statement,l_full_path,'l_site : ' || l_site ||' l_currency :  '|| l_currency );
1100   END IF;
1101   --Initializing section starts
1102 
1103   l_outer_where_clause  := '';
1104   l_custom_rec		:=  BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
1105 
1106   --Initializing section completed
1107 
1108   --Get the Table List
1109 
1113 
1110   l_table_list   := ' IBW_KPI_METRICS_TIME_MV  FACT' ||
1111                     ' ,FII_TIME_RPT_STRUCT_V CAL' ;
1112 
1114   IF gaflog_value ='Y' AND (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1115     fnd_log.string(fnd_log.level_statement,l_full_path,'l_table_list: ' || l_table_list);
1116   END IF;
1117 
1118   -- Initialising where clause based on the site parameter selection
1119   --Added for Bug#:4660266
1120   IF upper(l_site) <> 'ALL' THEN
1121     l_outer_where_clause   := l_outer_where_clause || ' AND FACT.SITE_ID in (&SITE+SITE) ' ;
1122    --The Else Condition is not needed here as the same is used already in the custom_sql below.
1123   END IF;
1124 
1125   IF gaflog_value ='Y' AND (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1126     fnd_log.string(fnd_log.level_statement,l_full_path,'l_outer_where_clause: ' || l_outer_where_clause);
1127   END IF;
1128 
1129 l_custom_sql1:= 'SELECT ID VIEWBYID, SITE_VAL VIEWBY, ID  IBW_VAL1,
1130        nvl(Visits_cur,0) IBW_VAL2,
1131        nvl(Visits_pre,0) IBW_VAL3,
1132        sum(nvl(Visits_cur,0)) over() IBW_VAL5,
1133        sum(nvl(Visits_pre,0)) over() IBW_VAL6,
1134        nvl(WEB_REGISTRATIONS_cur,0) IBW_VAL7,
1135        nvl(WEB_REGISTRATIONS_PRE,0)  IBW_VAL8,
1136        sum(nvl(WEB_REGISTRATIONS_cur,0)) over() IBW_VAL10,
1137        sum(nvl(WEB_REGISTRATIONS_PRE,0)) over() IBW_VAL11,
1138        nvl(BOOKED_WEB_ORDERS_cur,0) IBW_VAL17,
1139        nvl(BOOKED_WEB_ORDERS_pre,0) IBW_VAL18,
1140        sum(nvl(BOOKED_WEB_ORDERS_cur,0)) over() IBW_VAL20,
1141        sum(nvl(BOOKED_WEB_ORDERS_pre,0)) over() IBW_VAL21,
1142        decode(nvl(Visits_cur,0),0,null,(nvl(ORDERS_SITE_VISITS_cur,0)/visits_cur)*100) IBW_VAL22,   -- Returned null instead of 0 for bug 5253591
1143        decode(nvl(Visits_pre,0),0,null,(nvl(ORDERS_SITE_VISITS_pre,0)/visits_pre)*100) IBW_VAL23,   -- Returned null instead of 0 for bug 5253591
1144        decode(SUM(nvl(Visits_cur,0)) over(),0,null,(SUM(nvl(ORDERS_SITE_VISITS_cur,0)) over()/SUM(nvl(Visits_cur,0)) over())*100)  IBW_VAL25,  -- Returned null instead of 0 for bug 5253591
1145        decode(SUM(nvl(Visits_pre,0)) over(),0,null,(SUM(nvl(ORDERS_SITE_VISITS_pre,0)) over()/SUM(nvl(Visits_pre,0)) over())*100)  IBW_VAL26,  -- Returned null instead of 0 for bug 5253591
1146        nvl(NEW_WEB_CUSTOMERS_cur,0) IBW_VAL27,
1147        nvl(NEW_WEB_CUSTOMERS_pre,0)  IBW_VAL28,
1148        sum(nvl(NEW_WEB_CUSTOMERS_cur,0)) over() IBW_VAL30,
1149        sum(nvl(NEW_WEB_CUSTOMERS_pre,0)) over() IBW_VAL31,
1150        decode(nvl(TOTAL_BOOKED_ORDERS_cur,0),0,null, (nvl(BOOKED_WEB_ORDERS_cur,0)/TOTAL_BOOKED_ORDERS_cur)*100) IBW_VAL37,     -- Returned null instead of 0 for bug 5253591
1151        decode(nvl(TOTAL_BOOKED_ORDERS_pre,0),0,null, (nvl(BOOKED_WEB_ORDERS_pre,0)/TOTAL_BOOKED_ORDERS_pre)*100) IBW_VAL38,      -- Returned null instead of 0 for bug 5253591
1152        decode(NVL(TOTAL_BOOKED_ORDERS_cur,0),0,null, (SUM(nvl(BOOKED_WEB_ORDERS_cur,0)) over()/NVL(TOTAL_BOOKED_ORDERS_cur,0))*100) IBW_VAL40,   -- Returned null instead of 0 for bug 5253591
1153        decode(NVL(TOTAL_BOOKED_ORDERS_pre,0),0,null, (SUM(nvl(BOOKED_WEB_ORDERS_pre,0)) over()/NVL(TOTAL_BOOKED_ORDERS_pre,0))*100) IBW_VAL41,   -- Returned null instead of 0 for bug 5253591
1154        nvl(REPEAT_WEB_ORDERS_cur,0) IBW_VAL42,
1155        nvl(REPEAT_WEB_ORDERS_pre,0)  IBW_VAL43,
1156        sum(nvl(REPEAT_WEB_ORDERS_cur,0)) over() IBW_VAL45,
1157        sum(nvl(REPEAT_WEB_ORDERS_pre,0)) over() IBW_VAL46,
1158        nvl(REGISTERED_CARTS_cur,0) IBW_VAL47,
1159        nvl(REGISTERED_CARTS_pre,0)  IBW_VAL48,
1160        sum(nvl(REGISTERED_CARTS_cur,0)) over() IBW_VAL50,
1161        sum(nvl(REGISTERED_CARTS_pre,0)) over() IBW_VAL51 ';
1162 
1163   IF gaflog_value ='Y' AND (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1164     fnd_log.string(fnd_log.level_statement,l_full_path,'l_custom_sql1: ' || l_custom_sql1);
1165   END IF;
1166 
1167 l_custom_sql2:=' FROM (
1168        SELECT ID, SITE_VAL,
1169        sum(Visits_cur) Visits_cur,
1170        sum(Visits_pre) Visits_pre,
1171        sum(WEB_REGISTRATIONS_cur) WEB_REGISTRATIONS_cur,
1172        sum(WEB_REGISTRATIONS_pre) WEB_REGISTRATIONS_pre,
1173        sum(CARTS_cur) CARTS_cur,
1174        sum(CARTS_pre) CARTS_pre,
1175        sum(BOOKED_WEB_ORDERS_cur) BOOKED_WEB_ORDERS_cur,
1176        sum(BOOKED_WEB_ORDERS_pre) BOOKED_WEB_ORDERS_pre,
1177        sum(ORDERS_SITE_VISITS_cur) ORDERS_SITE_VISITS_cur,
1178        sum(ORDERS_SITE_VISITS_pre) ORDERS_SITE_VISITS_pre,
1179        sum(NEW_WEB_CUSTOMERS_cur) NEW_WEB_CUSTOMERS_cur,
1180        sum(NEW_WEB_CUSTOMERS_pre) NEW_WEB_CUSTOMERS_pre,
1181        sum(AMT_cur) AMT_cur,
1182        sum(AMT_pre ) AMT_pre,
1183        sum(TOTAL_BOOKED_ORDERS_cur)TOTAL_BOOKED_ORDERS_cur ,
1184        sum(TOTAL_BOOKED_ORDERS_pre) TOTAL_BOOKED_ORDERS_pre,
1185        sum(REPEAT_WEB_ORDERS_cur) REPEAT_WEB_ORDERS_cur,
1186        sum(REPEAT_WEB_ORDERS_pre) REPEAT_WEB_ORDERS_pre,
1187        sum(REGISTERED_CARTS_cur) REGISTERED_CARTS_cur,
1188        sum(REGISTERED_CARTS_pre) REGISTERED_CARTS_pre
1189        FROM(
1190        SELECT SITE.ID ID,SITE.VALUE SITE_VAL,
1191        sum(decode(cal.report_date,&BIS_CURRENT_ASOF_DATE,fact.visits,0)) Visits_cur,
1192        sum(decode(cal.report_date,&BIS_PREVIOUS_ASOF_DATE,fact.visits,0)) Visits_pre,
1193        sum(decode(cal.report_date,&BIS_CURRENT_ASOF_DATE,fact.WEB_REGISTRATIONS,0)) WEB_REGISTRATIONS_cur,
1194        sum(decode(cal.report_date,&BIS_PREVIOUS_ASOF_DATE,fact.WEB_REGISTRATIONS,0)) WEB_REGISTRATIONS_pre,
1195        sum(decode(cal.report_date,&BIS_CURRENT_ASOF_DATE,fact.CARTS,0)) CARTS_cur,
1196        sum(decode(cal.report_date,&BIS_PREVIOUS_ASOF_DATE,fact.CARTS,0)) CARTS_pre,
1197        sum(decode(cal.report_date,&BIS_CURRENT_ASOF_DATE,fact.BOOKED_WEB_ORDERS,0)) BOOKED_WEB_ORDERS_cur,
1198        sum(decode(cal.report_date,&BIS_PREVIOUS_ASOF_DATE,fact.BOOKED_WEB_ORDERS,0)) BOOKED_WEB_ORDERS_pre,
1199        sum(decode(cal.report_date,&BIS_CURRENT_ASOF_DATE,fact.ORDERS_SITE_VISITS,0)) ORDERS_SITE_VISITS_cur,
1200        sum(decode(cal.report_date,&BIS_PREVIOUS_ASOF_DATE,fact.ORDERS_SITE_VISITS,0)) ORDERS_SITE_VISITS_pre,
1204        sum(decode(cal.report_date,&BIS_PREVIOUS_ASOF_DATE,decode(:l_currency,:l_gp_currency,BOOKED_AMT_G,:l_gs_currency,BOOKED_AMT_G1,CURRENCY_CD_F,BOOKED_AMT_F,0),0)) AMT_pre,
1201        sum(decode(cal.report_date,&BIS_CURRENT_ASOF_DATE,fact.NEW_WEB_CUSTOMERS,0)) NEW_WEB_CUSTOMERS_cur,
1202        sum(decode(cal.report_date,&BIS_PREVIOUS_ASOF_DATE,fact.NEW_WEB_CUSTOMERS,0)) NEW_WEB_CUSTOMERS_pre,
1203        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),0)) AMT_cur,
1205        NULL TOTAL_BOOKED_ORDERS_cur,
1206        NULL TOTAL_BOOKED_ORDERS_pre,
1207        sum(decode(cal.report_date,&BIS_CURRENT_ASOF_DATE,fact.REPEAT_WEB_ORDERS,0)) REPEAT_WEB_ORDERS_cur,
1208        sum(decode(cal.report_date,&BIS_PREVIOUS_ASOF_DATE,fact.REPEAT_WEB_ORDERS,0)) REPEAT_WEB_ORDERS_pre,
1209        sum(decode(cal.report_date,&BIS_CURRENT_ASOF_DATE,(nvl(fact.CARTS,0) - nvl(fact.ANONYMOUS_CARTS,0)),0)) REGISTERED_CARTS_cur,
1210        sum(decode(cal.report_date,&BIS_PREVIOUS_ASOF_DATE,(nvl(fact.CARTS,0) - nvl(fact.ANONYMOUS_CARTS,0)),0)) REGISTERED_CARTS_pre
1211 from IBW_KPI_METRICS_TIME_MV  FACT ,
1212      FII_TIME_RPT_STRUCT_V CAL,
1213      IBW_BI_MSITE_DIMN_V SITE
1214 where CAL.calendar_id = -1
1215   AND FACT.Time_Id = CAL.Time_Id
1216   AND FACT.Period_Type_id = CAL.Period_Type_Id
1217   AND REPORT_DATE IN (&BIS_CURRENT_ASOF_DATE,&BIS_PREVIOUS_ASOF_DATE)
1218   AND BITAND(CAL.Record_Type_Id, &BIS_NESTED_PATTERN) = CAL.Record_Type_Id
1219  AND FACT.SITE_ID = SITE.ID
1220  '|| l_outer_where_clause ||' --4660266
1221 GROUP BY SITE.ID, SITE.VALUE
1222 UNION ALL
1223 SELECT SITE.ID ID,SITE.VALUE SITE_VAL,
1224        NULL Visits_cur,
1225        NULL Visits_pre,
1226        NULL WEB_REGISTRATIONS_cur,
1227        NULL WEB_REGISTRATIONS_pre,
1228        NULL CARTS_cur,
1229        NULL CARTS_pre,
1230        NULL BOOKED_WEB_ORDERS_cur,
1231        NULL BOOKED_WEB_ORDERS_pre,
1232        NULL ORDERS_SITE_VISITS_cur,
1233        NULL ORDERS_SITE_VISITS_pre,
1234        NULL NEW_WEB_CUSTOMERS_cur,
1235        NULL NEW_WEB_CUSTOMERS_pre,
1236        NULL AMT_cur,
1237        NULL AMT_pre,
1238        sum(decode(cal.report_date,&BIS_CURRENT_ASOF_DATE,fact.TOTAL_BOOKED_ORDERS,0)) TOTAL_BOOKED_ORDERS_cur,
1239        sum(decode(cal.report_date,&BIS_PREVIOUS_ASOF_DATE,fact.TOTAL_BOOKED_ORDERS,0)) TOTAL_BOOKED_ORDERS_pre,
1240        NULL REPEAT_WEB_ORDERS_cur,
1241        NULL REPEAT_WEB_ORDERS_pre,
1242        NULL REGISTERED_CARTS_cur,
1243        NULL REGISTERED_CARTS_pre
1244 from IBW_KPI_METRICS_TIME_MV  FACT ,
1245      FII_TIME_RPT_STRUCT_V CAL,
1246      IBW_BI_MSITE_DIMN_V SITE
1247 where CAL.calendar_id = -1
1248   AND FACT.Time_Id = CAL.Time_Id
1249   AND FACT.Period_Type_id = CAL.Period_Type_Id
1250   AND REPORT_DATE IN (&BIS_CURRENT_ASOF_DATE,&BIS_PREVIOUS_ASOF_DATE)
1251   AND BITAND(CAL.Record_Type_Id, &BIS_NESTED_PATTERN) = CAL.Record_Type_Id
1252  AND FACT.SITE_ID = -9999
1253  GROUP BY SITE.ID, SITE.VALUE
1254 )
1255 GROUP BY ID,SITE_VAL
1256 )';
1257 
1258 IF gaflog_value ='Y' AND (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1259   fnd_log.string(fnd_log.level_statement,l_full_path,'l_custom_sql2: ' || l_custom_sql2);
1260 END IF;
1261 
1262     x_custom_sql  := l_custom_sql1 ||l_custom_sql2;
1263 
1264 
1265 
1266  x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
1267   x_custom_output.Extend(3);
1268 
1269   l_custom_rec.attribute_name := ':l_currency' ;
1270   l_custom_rec.attribute_value:= l_currency;
1271   l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
1272   l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
1273 
1274   x_custom_output(1) := l_custom_rec;
1275 
1276   l_custom_rec.attribute_name := ':l_gp_currency' ;
1277   l_custom_rec.attribute_value:= l_gp_currency;
1278   l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
1279   l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
1280 
1281   x_custom_output(2) := l_custom_rec;
1282 
1283   l_custom_rec.attribute_name := ':l_gs_currency' ;
1284   l_custom_rec.attribute_value:= l_gs_currency;
1285   l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
1286   l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
1287 
1288   x_custom_output(3) := l_custom_rec;
1289 
1290   IF gaflog_value ='Y' AND (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1291     fnd_log.string(fnd_log.level_statement,l_full_path,'End ' );
1292   END IF;
1293 
1294 EXCEPTION
1295    WHEN OTHERS THEN
1296     if gaflog_value ='Y' AND (fnd_log.level_unexpected >= fnd_log.g_current_runtime_level) then
1297       fnd_log.string(fnd_log.level_unexpected,l_full_path,SQLERRM);
1298     end if;
1299 
1300 END GET_KPI_SQL;
1301 
1302 END IBW_BI_VST_CMP_KPI_PVT;