[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
/* &BIS_CURRENT_ASOF_DATE : AS OF DATE selected in the report */
/* &BIS_PREVIOUS_ASOF_DATE : Previous period as of date */
/* &BIS_CURRENT_REPORT_START_DATE : Current period start date */
/* &BIS_PREVIOUS_REPORT_START_DATE : Previous period startdate */
/* &BIS_NESTED_PATTERN : Record Type Id of the Period Type selected */
/*********************************************************************************/
l_custom_sql:= 'SELECT fii.NAME VIEWBY,
nvl(VISITS_CUR,0) IBW_VAL1,
nvl(ANONYMOUS_CARTS_CUR,0) IBW_VAL2,
nvl(WEB_REGISTRATIONS_CUR,0) IBW_VAL3,
(nvl(CARTS_CUR,0) - nvl(ANONYMOUS_CARTS_CUR,0)) IBW_VAL4,
nvl(A_LEADS_CUR,0) IBW_VAL5,
decode(nvl(CARTS_CUR,0),0
,null -- 0
,(nvl(ORDERS_CUR,0)/nvl(CARTS_CUR,0))*100) IBW_VAL6, --Changed by AANNAMAL to Consider Total Carts instead of Registered Carts
nvl(TOTAL_BOOKED_ORDERS_CUR,0) IBW_VAL7,
nvl(REPEAT_WEB_ORDERS_CUR,0) IBW_VAL8,
decode(nvl(VISITS_CUR,0),0,null,(ORDERS_SITE_VISITS_CUR/VISITS_CUR)*100) IBW_VAL9, -- Returned null instead of 0 for bug 5253591
nvl(BOOKED_AMOUNT_CUR,0) IBW_VAL10
FROM ( SELECT START_DATE,
sum(nvl(visits_c,0)) VISITS_CUR,
sum(nvl(visits_p,0)) VISITS_PRE,
sum(nvl(anonymous_carts_c,0)) ANONYMOUS_CARTS_CUR,
sum(nvl(web_registrations_c,0)) WEB_REGISTRATIONS_CUR,
sum(nvl(carts_c,0)) CARTS_CUR,
sum(nvl(a_leads_c,0)) A_LEADS_CUR,
sum(nvl(orders_c,0)) ORDERS_CUR,
sum(nvl(total_booked_orders_C,0)) TOTAL_BOOKED_ORDERS_CUR,
sum(nvl(repeat_web_orders_C,0)) REPEAT_WEB_ORDERS_CUR,
sum(nvl(orders_site_visits_C,0)) ORDERS_SITE_VISITS_CUR,
sum(nvl(booked_amount_c,0)) BOOKED_AMOUNT_CUR
FROM (SELECT
dates.start_date START_DATE,
decode(dates.period, ''C'',visits,0) visits_C,
decode(dates.period, ''P'',visits,0) visits_P,
decode(dates.period, ''C'',anonymous_carts,0) anonymous_carts_C,
decode(dates.period, ''C'',web_registrations,0) web_registrations_C,
decode(dates.period, ''C'',carts,0) carts_C,
decode(dates.period, ''C'',a_leads,0) a_leads_C,
decode(dates.period, ''C'',orders,0) orders_C,
decode(dates.period, ''C'',BOOKED_WEB_ORDERS,0) total_booked_orders_C,
decode(dates.period, ''C'',repeat_web_orders,0) repeat_web_orders_C,
decode(dates.period, ''C'',orders_site_visits,0) orders_site_visits_C,
decode(dates.period, ''C'',decode(:l_currency,:l_gp_currency,booked_amt_g,:l_gs_currency,booked_amt_g1
,currency_cd_f,booked_amt_f),0) BOOKED_AMOUNT_C /* Change for issue 20 bug 4636308) */
FROM
( SELECT
fii.start_date START_DATE,
''C'' PERIOD,
least(fii.end_date, &BIS_CURRENT_ASOF_DATE) REPORT_DATE
FROM '||l_period_type||' fii
WHERE fii.start_date BETWEEN &BIS_CURRENT_REPORT_START_DATE AND &BIS_CURRENT_ASOF_DATE
UNION ALL
SELECT
p2.start_date START_DATE,
''P'' PERIOD,
p1.report_date REPORT_DATE
FROM
(
SELECT
REPORT_DATE,
rownum id
FROM
(
SELECT
least(time_dim.end_date, &BIS_PREVIOUS_ASOF_DATE) REPORT_DATE
FROM
' ||l_period_type||' time_dim
WHERE
time_dim.start_date BETWEEN &BIS_PREVIOUS_REPORT_START_DATE AND &BIS_PREVIOUS_ASOF_DATE
ORDER BY time_dim.start_date DESC
)
) p1,
(
SELECT
START_DATE,
rownum id
FROM
(
SELECT time_dim.start_date START_DATE
FROM ' ||l_period_type||' time_dim
WHERE time_dim.start_date BETWEEN &BIS_CURRENT_REPORT_START_DATE AND &BIS_CURRENT_ASOF_DATE
ORDER BY time_dim.start_date DESC
)
) p2
WHERE p1.id(+) = p2.id) dates, '|| l_table_list ||'
WHERE
cal.report_date = dates.report_date AND
VISIT_MV.time_id = cal.time_id AND
VISIT_MV.period_type_id = cal.period_type_id AND
bitand(cal.record_type_id, &BIS_NESTED_PATTERN) = cal.record_type_id'|| l_outer_where_clause ||')
GROUP BY START_DATE
) s,'|| l_period_type||' fii
WHERE fii.start_date BETWEEN &BIS_CURRENT_REPORT_START_DATE AND &BIS_CURRENT_ASOF_DATE AND
fii.start_date = s.start_date(+)
ORDER BY fii.start_date';
/* &BIS_CURRENT_ASOF_DATE : AS OF DATE selected in the report */
/* &BIS_PREVIOUS_ASOF_DATE : Previous period as of date */
/* &BIS_CURRENT_REPORT_START_DATE : Current period start date */
/* &BIS_PREVIOUS_REPORT_START_DATE : Previous period startdate */
/* &BIS_NESTED_PATTERN : Record Type Id of the Period Type selected */
/*********************************************************************************/
-- Not returning value for IBW_VAL2 and IBW_VAL9 as per bug # 4772549.
l_custom_sql:= 'SELECT fii.NAME VIEWBY,
nvl(VISITS_PRE,0) IBW_VAL10, --Bug#4727078 Issue#:21
nvl(VISITS_CUR,0) IBW_VAL1,
nvl(REPEAT_VISITS_CUR,0) IBW_VAL3,
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
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
nvl(DAILY_UNIQ_VISITORS_CUR,0) IBW_VAL6,
nvl(OPT_OUTS_CUR,0) IBW_VAL7,
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
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
FROM (
SELECT
START_DATE,
sum(visits_c) VISITS_CUR,
sum(visits_p) VISITS_PRE,
sum(repeat_visits_c) REPEAT_VISITS_CUR,
sum(visit_duration_c) VISIT_DURATION_CUR,
sum(page_views_c) page_views_cur,
sum(daily_uniq_visitors_c) DAILY_UNIQ_VISITORS_CUR,
sum(opt_outs_c) OPT_OUTS_CUR,
sum(orders_site_visits_C) orders_site_visits_cur,
sum(orders_site_visits_P) orders_site_visits_pre
FROM
(
SELECT
dates.start_date START_DATE,
decode(dates.period, ''C'',visits,0) visits_C,
decode(dates.period, ''P'',visits,0) visits_P,
decode(dates.period, ''C'',repeat_visits,0) repeat_visits_c,
decode(dates.period, ''C'',visit_duration,0) visit_duration_c,
decode(dates.period, ''C'',page_views,0) page_views_c,
decode(dates.period, ''C'',daily_uniq_visitors,0) daily_uniq_visitors_c,
decode(dates.period, ''C'',opt_outs,0) opt_outs_c,
decode(dates.period, ''C'',orders_site_visits,0) orders_site_visits_C,
decode(dates.period, ''P'',orders_site_visits,0) orders_site_visits_P
FROM
(
SELECT
fii.start_date START_DATE,
''C'' PERIOD,
least(fii.end_date, &BIS_CURRENT_ASOF_DATE) REPORT_DATE
FROM '||l_period_type||' fii
WHERE fii.start_date BETWEEN &BIS_CURRENT_REPORT_START_DATE AND &BIS_CURRENT_ASOF_DATE
UNION ALL
SELECT
p2.start_date START_DATE,
''P'' PERIOD,
p1.report_date REPORT_DATE
FROM
(
SELECT
REPORT_DATE,
rownum id
FROM
(
SELECT
least(time_dim.end_date, &BIS_PREVIOUS_ASOF_DATE) REPORT_DATE
FROM
' ||l_period_type||' time_dim
WHERE
time_dim.start_date BETWEEN &BIS_PREVIOUS_REPORT_START_DATE AND &BIS_PREVIOUS_ASOF_DATE
ORDER BY time_dim.start_date DESC
)
) p1,
(
SELECT
START_DATE,
rownum id
FROM
(
SELECT time_dim.start_date START_DATE
FROM ' ||l_period_type||' time_dim
WHERE time_dim.start_date BETWEEN &BIS_CURRENT_REPORT_START_DATE AND &BIS_CURRENT_ASOF_DATE
ORDER BY time_dim.start_date DESC
)
) p2
WHERE p1.id(+) = p2.id) dates, '|| l_table_list ||'
WHERE
cal.report_date = dates.report_date AND
VISIT_MV.time_id = cal.time_id AND
VISIT_MV.period_type_id = cal.period_type_id AND
bitand(cal.record_type_id, &BIS_NESTED_PATTERN) = cal.record_type_id'|| l_outer_where_clause ||')
GROUP BY START_DATE
) s,'|| l_period_type||' fii
WHERE fii.start_date BETWEEN &BIS_CURRENT_REPORT_START_DATE AND &BIS_CURRENT_ASOF_DATE AND
fii.start_date = s.start_date(+)
ORDER BY fii.start_date';
l_outer_select VARCHAR2(30000) ;
l_inner_select VARCHAR2(30000) ;
/* &BIS_CURRENT_ASOF_DATE : AS OF DATE selected in the report */
/* &BIS_NESTED_PATTERN : Record Type Id of the Period Type selected */
/************************************************************************/
-- Added by pipandey for Bug# 4687647 Issue# 3
-- Changed Average Visit Duration - added division by 60000,Cart Conversion multiply by 100
--------------------------------------------------------------------------------------------------------------
l_outer_select := ' nvl(visits,0) IBW_VAL1, ' || --Visits
' DECODE(visits,0,null,null,null,((visit_duration/visits)/60000)) IBW_VAL2, ' || --Average Visit Duration
' DECODE(visits,0,null,null,null,page_views/ visits) IBW_VAL3, ' || --Average Page Views
' nvl(daily_uniq_visitors,0) IBW_VAL4, ' || --Daily Unique Visitors
' nvl(web_registrations,0) IBW_VAL5, ' || --Registrations
' nvl(a_leads,0) IBW_VAL6, ' || --A Leads
' nvl(carts,0) IBW_VAL7, ' || --Carts
' DECODE(carts,0,null,null,null, ((orders/carts)*100)) IBW_VAL8, ' || --Cart Conversion Ratio.
' nvl(booked_orders_amount,0) IBW_VAL9, ' || --Booked Orders Amount
--Changes for the ER 4760433
' nvl(booked_orders_amount,0) IBW_VAL11, ' ||
' nvl(a_leads,0) IBW_VAL10, ' ||
--Changes for the ER 4760433
--For Grand Totals
' SUM(nvl(visits,0)) OVER() IBW_G_TOT1, ' ||
--' SUM(DECODE(visits,0,null,null,null, visit_duration / visits)) OVER() IBW_G_TOT2, '|| Grand Total Change
--' SUM(DECODE(visits,0,null,null,null,page_views/ visits)) OVER() IBW_G_TOT3, ' || Grand Total Change
' DECODE(SUM(visits) over(),0,null,null,null, (((SUM(visit_duration) over() / SUM(visits) over()))/60000)) IBW_G_TOT2, '||
' DECODE(SUM(visits) over(),0,null,null,null,(SUM(page_views) over()/ SUM(visits) over())) IBW_G_TOT3, ' ||
' SUM(nvl(daily_uniq_visitors,0)) OVER() IBW_G_TOT4, '||
' SUM(nvl(web_registrations,0)) OVER() IBW_G_TOT5, ' ||
' SUM(nvl(a_leads,0)) OVER() IBW_G_TOT6, '||
' SUM(nvl(carts,0)) OVER() IBW_G_TOT7, ' ||
--' SUM(DECODE(Carts,0,null,null,null, orders / carts)) OVER() IBW_G_TOT8, '|| Grand Total Change
' DECODE(SUM(Carts) over(),0,null,null,null, ((SUM(orders) over() / SUM(Carts) over())*100)) IBW_G_TOT8, '||
' SUM(nvl(booked_orders_amount,0)) OVER() IBW_G_TOT9 ';
fnd_log.string(fnd_log.level_statement,l_full_path,'After Outer Select' );
l_inner_select := ' SUM(visits) visits, SUM(visit_duration) visit_duration, ' ||
' SUM(page_views) page_views, SUM(daily_uniq_visitors) daily_uniq_visitors, ' ||
' SUM(web_registrations) web_registrations, SUM(a_leads) a_leads, ' ||
' SUM(carts) carts, SUM(orders) orders, ' ||
' 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 ';
fnd_log.string(fnd_log.level_statement,l_full_path,'After Inner Select' );
l_inner_select := ' SITE.VALUE VIEW_BY , SITE.ID VIEWBYID, ' || l_inner_select; --SITE.VALUE will give the Site Name.
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.
l_where := l_where || ' AND CMP_MV.SITE_ID IN (&SITE+SITE) ' ; --In condition as Site is Multi Select
l_where := l_where || 'AND CMP_MV.PRIOR_ID = (&CAMPAIGN+CAMPAIGN) '; --Equal condition as Campaign dimension is single select
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
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 ' ;
l_where := l_where || 'AND CMP_MV.PRIOR_ID = (&CAMPAIGN+CAMPAIGN) '; --Equal condition as Campaign dimension is single select
l_where := l_where || ' AND CMP_MV.SITE_ID IN (&SITE+SITE) ' ; --In condition as Site is Multi Select
l_custom_sql := 'SELECT ' || l_outer_select ||
' FROM ' ||
' (SELECT ' || l_inner_select ||
' FROM ' || l_from ||
' WHERE ' || l_where ||
' GROUP BY ' || l_inner_group_by ||
' HAVING ' || l_having ||
' ) ' ||
' &ORDER_BY_CLAUSE ' ;
fnd_log.string(fnd_log.level_statement,l_full_path,'l_outer_select' || l_outer_select);
fnd_log.string(fnd_log.level_statement,l_full_path,'l_inner_select' || l_inner_select);
l_custom_sql1:= 'SELECT ID VIEWBYID, SITE_VAL VIEWBY, ID IBW_VAL1,
nvl(Visits_cur,0) IBW_VAL2,
nvl(Visits_pre,0) IBW_VAL3,
sum(nvl(Visits_cur,0)) over() IBW_VAL5,
sum(nvl(Visits_pre,0)) over() IBW_VAL6,
nvl(WEB_REGISTRATIONS_cur,0) IBW_VAL7,
nvl(WEB_REGISTRATIONS_PRE,0) IBW_VAL8,
sum(nvl(WEB_REGISTRATIONS_cur,0)) over() IBW_VAL10,
sum(nvl(WEB_REGISTRATIONS_PRE,0)) over() IBW_VAL11,
nvl(BOOKED_WEB_ORDERS_cur,0) IBW_VAL17,
nvl(BOOKED_WEB_ORDERS_pre,0) IBW_VAL18,
sum(nvl(BOOKED_WEB_ORDERS_cur,0)) over() IBW_VAL20,
sum(nvl(BOOKED_WEB_ORDERS_pre,0)) over() IBW_VAL21,
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
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
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
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
nvl(NEW_WEB_CUSTOMERS_cur,0) IBW_VAL27,
nvl(NEW_WEB_CUSTOMERS_pre,0) IBW_VAL28,
sum(nvl(NEW_WEB_CUSTOMERS_cur,0)) over() IBW_VAL30,
sum(nvl(NEW_WEB_CUSTOMERS_pre,0)) over() IBW_VAL31,
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
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
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
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
nvl(REPEAT_WEB_ORDERS_cur,0) IBW_VAL42,
nvl(REPEAT_WEB_ORDERS_pre,0) IBW_VAL43,
sum(nvl(REPEAT_WEB_ORDERS_cur,0)) over() IBW_VAL45,
sum(nvl(REPEAT_WEB_ORDERS_pre,0)) over() IBW_VAL46,
nvl(REGISTERED_CARTS_cur,0) IBW_VAL47,
nvl(REGISTERED_CARTS_pre,0) IBW_VAL48,
sum(nvl(REGISTERED_CARTS_cur,0)) over() IBW_VAL50,
sum(nvl(REGISTERED_CARTS_pre,0)) over() IBW_VAL51 ';
SELECT ID, SITE_VAL,
sum(Visits_cur) Visits_cur,
sum(Visits_pre) Visits_pre,
sum(WEB_REGISTRATIONS_cur) WEB_REGISTRATIONS_cur,
sum(WEB_REGISTRATIONS_pre) WEB_REGISTRATIONS_pre,
sum(CARTS_cur) CARTS_cur,
sum(CARTS_pre) CARTS_pre,
sum(BOOKED_WEB_ORDERS_cur) BOOKED_WEB_ORDERS_cur,
sum(BOOKED_WEB_ORDERS_pre) BOOKED_WEB_ORDERS_pre,
sum(ORDERS_SITE_VISITS_cur) ORDERS_SITE_VISITS_cur,
sum(ORDERS_SITE_VISITS_pre) ORDERS_SITE_VISITS_pre,
sum(NEW_WEB_CUSTOMERS_cur) NEW_WEB_CUSTOMERS_cur,
sum(NEW_WEB_CUSTOMERS_pre) NEW_WEB_CUSTOMERS_pre,
sum(AMT_cur) AMT_cur,
sum(AMT_pre ) AMT_pre,
sum(TOTAL_BOOKED_ORDERS_cur)TOTAL_BOOKED_ORDERS_cur ,
sum(TOTAL_BOOKED_ORDERS_pre) TOTAL_BOOKED_ORDERS_pre,
sum(REPEAT_WEB_ORDERS_cur) REPEAT_WEB_ORDERS_cur,
sum(REPEAT_WEB_ORDERS_pre) REPEAT_WEB_ORDERS_pre,
sum(REGISTERED_CARTS_cur) REGISTERED_CARTS_cur,
sum(REGISTERED_CARTS_pre) REGISTERED_CARTS_pre
FROM(
SELECT SITE.ID ID,SITE.VALUE SITE_VAL,
sum(decode(cal.report_date,&BIS_CURRENT_ASOF_DATE,fact.visits,0)) Visits_cur,
sum(decode(cal.report_date,&BIS_PREVIOUS_ASOF_DATE,fact.visits,0)) Visits_pre,
sum(decode(cal.report_date,&BIS_CURRENT_ASOF_DATE,fact.WEB_REGISTRATIONS,0)) WEB_REGISTRATIONS_cur,
sum(decode(cal.report_date,&BIS_PREVIOUS_ASOF_DATE,fact.WEB_REGISTRATIONS,0)) WEB_REGISTRATIONS_pre,
sum(decode(cal.report_date,&BIS_CURRENT_ASOF_DATE,fact.CARTS,0)) CARTS_cur,
sum(decode(cal.report_date,&BIS_PREVIOUS_ASOF_DATE,fact.CARTS,0)) CARTS_pre,
sum(decode(cal.report_date,&BIS_CURRENT_ASOF_DATE,fact.BOOKED_WEB_ORDERS,0)) BOOKED_WEB_ORDERS_cur,
sum(decode(cal.report_date,&BIS_PREVIOUS_ASOF_DATE,fact.BOOKED_WEB_ORDERS,0)) BOOKED_WEB_ORDERS_pre,
sum(decode(cal.report_date,&BIS_CURRENT_ASOF_DATE,fact.ORDERS_SITE_VISITS,0)) ORDERS_SITE_VISITS_cur,
sum(decode(cal.report_date,&BIS_PREVIOUS_ASOF_DATE,fact.ORDERS_SITE_VISITS,0)) ORDERS_SITE_VISITS_pre,
sum(decode(cal.report_date,&BIS_CURRENT_ASOF_DATE,fact.NEW_WEB_CUSTOMERS,0)) NEW_WEB_CUSTOMERS_cur,
sum(decode(cal.report_date,&BIS_PREVIOUS_ASOF_DATE,fact.NEW_WEB_CUSTOMERS,0)) NEW_WEB_CUSTOMERS_pre,
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,
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,
NULL TOTAL_BOOKED_ORDERS_cur,
NULL TOTAL_BOOKED_ORDERS_pre,
sum(decode(cal.report_date,&BIS_CURRENT_ASOF_DATE,fact.REPEAT_WEB_ORDERS,0)) REPEAT_WEB_ORDERS_cur,
sum(decode(cal.report_date,&BIS_PREVIOUS_ASOF_DATE,fact.REPEAT_WEB_ORDERS,0)) REPEAT_WEB_ORDERS_pre,
sum(decode(cal.report_date,&BIS_CURRENT_ASOF_DATE,(nvl(fact.CARTS,0) - nvl(fact.ANONYMOUS_CARTS,0)),0)) REGISTERED_CARTS_cur,
sum(decode(cal.report_date,&BIS_PREVIOUS_ASOF_DATE,(nvl(fact.CARTS,0) - nvl(fact.ANONYMOUS_CARTS,0)),0)) REGISTERED_CARTS_pre
from IBW_KPI_METRICS_TIME_MV FACT ,
FII_TIME_RPT_STRUCT_V CAL,
IBW_BI_MSITE_DIMN_V SITE
where CAL.calendar_id = -1
AND FACT.Time_Id = CAL.Time_Id
AND FACT.Period_Type_id = CAL.Period_Type_Id
AND REPORT_DATE IN (&BIS_CURRENT_ASOF_DATE,&BIS_PREVIOUS_ASOF_DATE)
AND BITAND(CAL.Record_Type_Id, &BIS_NESTED_PATTERN) = CAL.Record_Type_Id
AND FACT.SITE_ID = SITE.ID
'|| l_outer_where_clause ||' --4660266
GROUP BY SITE.ID, SITE.VALUE
UNION ALL
SELECT SITE.ID ID,SITE.VALUE SITE_VAL,
NULL Visits_cur,
NULL Visits_pre,
NULL WEB_REGISTRATIONS_cur,
NULL WEB_REGISTRATIONS_pre,
NULL CARTS_cur,
NULL CARTS_pre,
NULL BOOKED_WEB_ORDERS_cur,
NULL BOOKED_WEB_ORDERS_pre,
NULL ORDERS_SITE_VISITS_cur,
NULL ORDERS_SITE_VISITS_pre,
NULL NEW_WEB_CUSTOMERS_cur,
NULL NEW_WEB_CUSTOMERS_pre,
NULL AMT_cur,
NULL AMT_pre,
sum(decode(cal.report_date,&BIS_CURRENT_ASOF_DATE,fact.TOTAL_BOOKED_ORDERS,0)) TOTAL_BOOKED_ORDERS_cur,
sum(decode(cal.report_date,&BIS_PREVIOUS_ASOF_DATE,fact.TOTAL_BOOKED_ORDERS,0)) TOTAL_BOOKED_ORDERS_pre,
NULL REPEAT_WEB_ORDERS_cur,
NULL REPEAT_WEB_ORDERS_pre,
NULL REGISTERED_CARTS_cur,
NULL REGISTERED_CARTS_pre
from IBW_KPI_METRICS_TIME_MV FACT ,
FII_TIME_RPT_STRUCT_V CAL,
IBW_BI_MSITE_DIMN_V SITE
where CAL.calendar_id = -1
AND FACT.Time_Id = CAL.Time_Id
AND FACT.Period_Type_id = CAL.Period_Type_Id
AND REPORT_DATE IN (&BIS_CURRENT_ASOF_DATE,&BIS_PREVIOUS_ASOF_DATE)
AND BITAND(CAL.Record_Type_Id, &BIS_NESTED_PATTERN) = CAL.Record_Type_Id
AND FACT.SITE_ID = -9999
GROUP BY SITE.ID, SITE.VALUE
)
GROUP BY ID,SITE_VAL
)';