[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
* selected from the parameter portlet *
* x_custom_sql varchar2 OUT This is used to send the portlet query *
* x_cusom_output varchar2 OUT This is used to send the bind variables *
* *
**********************************************************************************************/
PROCEDURE get_page_int_sql
( p_param IN BIS_PMV_PAGE_PARAMETER_TBL
, x_custom_sql OUT NOCOPY VARCHAR2
, x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL
)
IS
--Generic Variables
l_custom_sql VARCHAR2(15000) ; --Final Sql.
l_outer_select VARCHAR2(3000) ;
l_middle_select VARCHAR2(3000);
l_inner_select VARCHAR2(3000) ;
l_inner_select_sec VARCHAR2(3000);
l_select_1 VARCHAR2(3000);
l_second_select VARCHAR2(3000);
l_third_select VARCHAR2(3000);
l_select_3 VARCHAR2(3000);
l_select_2 VARCHAR2(3000);
l_select_q1 VARCHAR2(3000);
l_select_q2 VARCHAR2(3000);
l_outer_select := '';
l_middle_select := '';
l_inner_select := '';
l_inner_select_sec :='';
l_select_1 :='';
l_select_q1 :='';
l_select_q2 :='';
l_second_select :='' ;
l_select_2 :='';
l_third_select :='' ;
l_select_3 :='' ;
/* &BIS_CURRENT_ASOF_DATE : AS OF DATE selected in the report */
/* &BIS_NESTED_PATTERN : Record Type Id of the Period Type selected */
/************************************************************************/
--The Outer Select statement is recorded in this variable.
--Average Page View Duration := Page View Duration / Page Views
l_outer_select := ' nvl(page_views,0) IBW_VAL1 '||
--If Page Views is Zero then Null is returned otherwise a Zero Divide would occur.
' , DECODE(nvl(page_views,0),0,null, nvl(page_view_duration,0)/page_views) IBW_VAL2 ' ||
' , nvl(daily_un_visitors,0) IBW_VAL3 ' ||
' , nvl(visits,0) IBW_VAL4 ' ||
-- Added on 30/11/2005 for Bug# 4763103 Issue# 3
' , DECODE(nvl(page_views,0),0,null, nvl(page_view_duration,0)/page_views) IBW_VAL5 ' ||
' , nvl(page_views,0) IBW_VAL6 '||
--For Grand Totals
' , SUM(nvl(page_views,0)) OVER() IBW_G_TOT1 ' ||
' , DECODE(SUM(nvl(page_views,0)) OVER(),0,NULL,(SUM(PAGE_VIEW_DURATION) OVER()/ SUM(PAGE_VIEWS) OVER() )) IBW_G_TOT2 ' ;
fnd_log.string(fnd_log.level_statement,l_full_path,'After Outer Select ');
l_middle_select := 'inner1.*'||
',inner2.daily_unique_visitor_gt daily_unique_visitor_gt'||
',inner3.visits_gt visits_gt';
l_inner_select := ' NVL(SUM(page_views),0) page_views ' ||
' , NVL(SUM(page_view_duration/60000),0) page_view_duration ' || --Milli Seconds converted into Minutes
' , COUNT(DISTINCT(visitant_id)) daily_un_visitors ' ||
' , COUNT(DISTINCT(visit_id)) visits ' ;
fnd_log.string(fnd_log.level_statement,l_full_path,'After Inner Select ');
IF upper(l_page ) ='ALL' THEN --Included this IF condition so that data is fetched when viewed by sitearea and selecting a site area
l_grouping_id := 8; --8 Indicates Grouping Set Id for Site Area
l_select_1 := ' Q1.SITE_AREA_ID SITE_AREA_ID,Q1.PAGE_INSTANCE_ID PAGE_INSTANCE_ID,Q1.VIEW_BY VIEW_BY, Q1.VIEWBYID VIEWBYID ';
l_select_q1 :='PAGE_SA_MV.SITE_AREA_ID SITE_AREA_ID , PAGE_SA_MV.PAGE_INSTANCE_ID ' ;
l_select_q2 := ' PAGE_SA_MV.SITE_AREA_ID SITE_AREA_ID, PAGE_SA_MV.PAGE_INSTANCE_ID';
l_where_q1 := l_where_q1 || ' AND PAGE_SA_MV.PAGE_INSTANCE_ID = (&IBW_PAGE+IBW_PAGES)';--Equality condition as Page is Single Select
l_second_select := ' SUM(DAILY_UNIQUE_VISITOR_GT) DAILY_UNIQUE_VISITOR_GT ';
l_third_select := ' SUM(VISITS_GT) VISITS_GT ';
l_select_2 := ' COUNT ( DISTINCT VISITANT_ID ) DAILY_UNIQUE_VISITOR_GT ';
l_select_3 := ' COUNT ( DISTINCT VISIT_ID ) VISITS_GT ';
l_second_select := ' SITE_AREA_ID , '||l_second_select;
l_third_select := ' SITE_AREA_ID , '||l_third_select;
l_select_2 := ' SITE_AREA_ID , '||l_select_2;
l_select_3 := ' SITE_AREA_ID , '||l_select_3;
l_outer_select := ' VIEW_BY VIEWBY, VIEWBYID, ' ||
' NULLIF(MEANING , ' ||
'(SELECT MEANING FROM FND_LOOKUP_VALUES LOOKUP2 ' ||
' WHERE LOOKUP2.LOOKUP_TYPE = ''IBW_BUSINESS_CONTEXT'' '||
' AND LOOKUP2.LOOKUP_CODE = ''NONE''' ||
' AND LOOKUP2.LANGUAGE = USERENV(''LANG'') ' ||
')) IBW_ATTR1 , ' ||
' CONTEXT_INSTANCE_CODE IBW_ATTR2, ' || l_outer_select;
l_select_q1 := l_select_q1|| ' , PAGE.VALUE VIEW_BY , PAGE.ID VIEWBYID, '||
'PAGE.BUSINESS_CONTEXT BUSINESS_CONTEXT, PAGE.CONTEXT_INSTANCE_CODE CONTEXT_INSTANCE_CODE, ' ||
'LOOKUP.MEANING';
l_select_q2 := l_select_q2||' , PAGE.VALUE VIEW_BY, PAGE.ID VIEWBYID, '||
'PAGE.BUSINESS_CONTEXT BUSINESS_CONTEXT, PAGE.CONTEXT_INSTANCE_CODE CONTEXT_INSTANCE_CODE, ' ||
'LOOKUP.MEANING';
l_select_1 := l_select_1 || ' , Q1.BUSINESS_CONTEXT BUSINESS_CONTEXT, Q1.CONTEXT_INSTANCE_CODE CONTEXT_INSTANCE_CODE, ' ||
' Q1.MEANING ';
l_outer_select := ' VIEW_BY VIEWBY, VIEWBYID, NULL IBW_ATTR1 , NULL IBW_ATTR2, ' || l_outer_select ;
l_select_q1 := l_select_q1||' , SITE_AREA.VALUE VIEW_BY, SITE_AREA.ID VIEWBYID ';
l_select_q2 := l_select_q2||' , SITE_AREA.VALUE VIEW_BY, SITE_AREA.ID VIEWBYID ';
l_select_q1 := l_select_q1||' , NVL (SUM (PAGE_VIEWS), 0) PAGE_VIEWS, '||
'NVL (SUM (PAGE_VIEW_DURATION / 60000), 0) PAGE_VIEW_DURATION, NULL DAILY_UN_VISITORS, COUNT (DISTINCT (VISIT_ID)) VISITS ';
l_select_q2 := l_select_q2|| ' , NULL PAGE_VIEWS, '||
' NULL PAGE_VIEW_DURATION, COUNT ( DISTINCT (VISITANT_ID)) DAILY_UN_VISITORS, NULL VISITS' ;
l_select_1 := l_select_1|| ' , Q1.PAGE_VIEWS PAGE_VIEWS, '||
'Q1.PAGE_VIEW_DURATION PAGE_VIEW_DURATION, SUM(Q2.DAILY_UN_VISITORS) DAILY_UN_VISITORS, Q1.VISITS VISITS ';
l_outer_select := l_outer_select|| ' , DAILY_UNIQUE_VISITOR_GT IBW_G_TOT3 ' ||' , VISITS_GT IBW_G_TOT4 ';
l_outer_select :=l_outer_select||' , DAILY_UN_VISITORS IBW_G_TOT3, VISITS IBW_G_TOT4 ';
l_where := l_where || ' AND PAGE_SA_MV.SITE_ID IN (&SITE+SITE) ' ; --In condition as Site is Multi Select
l_custom_sql := 'SELECT ' || l_outer_select ||
' FROM ' ||
' (SELECT ' || l_middle_select ||
' FROM ' ||
' (SELECT ' || l_select_1 ||
' FROM ' ||
' (SELECT ' || l_select_q1 ||
' FROM ' || l_from_q1||
' WHERE ' || l_where_q1 ||
' GROUP BY ' || l_group_by_q1 ||' ) q1 , '||
' (SELECT ' || l_select_q2 ||
' FROM ' || l_from_q1||
' WHERE ' || l_where_q2 ||
' GROUP BY ' || l_group_by_q2 ||' ) q2 '||
' WHERE '|| l_where_1||
' GROUP BY ' || l_group_by_1 ||
' HAVING '|| l_having_1|| ' ) inner1 , '||
' (SELECT ' || l_second_select ||
' FROM ' ||
' (SELECT ' || l_select_2 ||
' FROM ' || l_from_2||
' WHERE ' || l_where_2 ||
' GROUP BY ' || l_group_by_2 ||' ) '||
' GROUP BY ' || l_second_group_by ||' ) inner2 , '||
' (SELECT ' || l_third_select ||
' FROM ' ||
' (SELECT ' || l_select_3 ||
' FROM ' || l_from_3||
' WHERE ' || l_where_3 ||
' GROUP BY ' || l_group_by_3 ||' ) '||
' GROUP BY ' || l_third_group_by ||' ) inner3 ' ||
' WHERE ' || l_middle_where ||' ) '||
' &ORDER_BY_CLAUSE ';
l_custom_sql := 'SELECT ' || l_outer_select ||
' FROM ' ||
' (SELECT ' || l_middle_select ||
' FROM ' ||
' (SELECT ' || l_select_1 ||
' FROM ' ||
' (SELECT ' || l_select_q1 ||
' FROM ' || l_from_q1||
' WHERE ' || l_where_q1 ||
' GROUP BY ' || l_group_by_q1 ||' ) q1 , '||
' (SELECT ' || l_select_q2 ||
' FROM ' || l_from_q1||
' WHERE ' || l_where_q2 ||
' GROUP BY ' || l_group_by_q2 ||' ) q2 '||
' WHERE '|| l_where_1||
' GROUP BY ' || l_group_by_1 ||
' HAVING '|| l_having_1|| ' ) inner1 , '||
' (SELECT ' || l_second_select ||
' FROM ' ||
' (SELECT ' || l_select_2 ||
' FROM ' || l_from_2||
' WHERE ' || l_where_2 ||
' GROUP BY ' || l_group_by_2 ||' ) ) '||
' inner2 , '||
' (SELECT ' || l_third_select ||
' FROM ' ||
' (SELECT ' || l_select_3 ||
' FROM ' || l_from_3||
' WHERE ' || l_where_3 || ' ) '||
' ) inner3 '||
' WHERE ' || l_middle_where ||' ) '||
' &ORDER_BY_CLAUSE ';
l_custom_sql := 'SELECT ' || l_outer_select ||
' FROM ' ||
' (SELECT ' || l_select_1 ||
' FROM ' ||
' (SELECT ' || l_select_q1 ||
' FROM ' || l_from_q1||
' WHERE ' || l_where_q1 ||
' GROUP BY ' || l_group_by_q1 ||' ) q1 , '||
' (SELECT ' || l_select_q2 ||
' FROM ' || l_from_q1||
' WHERE ' || l_where_q2 ||
' GROUP BY ' || l_group_by_q2 ||' ) q2 '||
' WHERE '|| l_where_1||
' GROUP BY ' || l_group_by_1 ||
' HAVING '|| l_having_1|| ' ) '||
' &ORDER_BY_CLAUSE ';
* selected from the parameter portlet *
* x_custom_sql varchar2 OUT This is used to send the portlet query *
* x_cusom_output varchar2 OUT This is used to send the bind variables *
* *
**********************************************************************************************/
PROCEDURE get_page_int_trend_sql
(
p_param IN BIS_PMV_PAGE_PARAMETER_TBL
, x_custom_sql OUT NOCOPY VARCHAR2
, x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL
) IS
--Generic Variables
l_custom_sql VARCHAR2(15000) ; --Final Sql.
l_select_col_name VARCHAR2(100);
l_select_col_name := '';
l_where := l_where || ' AND PAGE_SA_MV.SITE_ID IN (&SITE+SITE) ' ; --In condition as Site is Multi Select
l_where := l_where || ' AND PAGE_SA_MV.PAGE_INSTANCE_ID = (&IBW_PAGE+IBW_PAGES)';--Equality condition as Page is Single Select
l_where := l_where || ' AND PAGE_SA_MV.SITE_AREA_ID = (&IBW_PAGE+IBW_SITE_AREAS)'; --Equality condition as Site Area is Single Select
/* &BIS_CURRENT_ASOF_DATE : AS OF DATE selected in the report */
/* &BIS_NESTED_PATTERN : Record Type Id of the Period Type selected */
/************************************************************************/
IF l_period_type ='FII_TIME_DAY' THEN
l_select_col_name := 'TIME_DIM.REPORT_DATE'; --In Day Dimension level Table NAME Column does not exist and hence using Report_Date Column.
l_select_col_name := 'TIME_DIM.NAME';
'SELECT
' || l_select_col_name || ' VIEWBY
, NVL(page_views_p,0) IBW_VAL7 --Bug#4727078 Issue#:21
, NVL(page_views_c,0) IBW_VAL1
, DECODE(page_views_p,null,null,0,null,
(NVL(page_view_duration_p,0)/page_views_p)) IBW_VAL8 --Bug#4727078 Issue#:21
, DECODE(page_views_c,null,null,0,null,
(NVL(page_view_duration_c,0)/page_views_c)) IBW_VAL3
, nvl(daily_un_visitors_c,0) IBW_VAL5
, nvl(visit_id_c,0) IBW_VAL6
, DECODE(page_views_c,null,null,0,null,(NVL(page_view_duration_c,0)/page_views_c)) IBW_VAL9 --NARAO for 4916959
FROM (
select
inner1.*,
inner2.daily_un_visitors_c daily_un_visitors_c
FROM (
SELECT
start_date start_date
, SUM(page_views_c) page_views_c
, SUM(page_views_p) page_views_p
, SUM(page_view_duration_c)/60000 page_view_duration_c
, SUM(page_view_duration_p)/60000 page_view_duration_p
, COUNT(DISTINCT(visit_id_c)) visit_id_c
FROM
(
SELECT
dates.start_date start_date
, decode(dates.period,''C'',page_views,0) page_views_c
, decode(dates.period,''P'',page_views,0) page_views_p
, decode(dates.period,''C'',page_view_duration,0) page_view_duration_c
, decode(dates.period,''P'',page_view_duration,0) page_view_duration_p
, decode(dates.period,''C'',visit_id,null) visit_id_c -- Fix for Bug 4916772
FROM
(
SELECT
time_dim.start_date START_DATE,
''C'' PERIOD,
least(time_dim.end_date, &BIS_CURRENT_ASOF_DATE) REPORT_DATE
FROM
'||l_period_type||' time_dim
WHERE
time_dim.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_from ||'
WHERE
cal.report_date = dates.report_date ' || l_where || '
)
GROUP BY start_date
) inner1,
(
SELECT
start_date start_date
, sum(daily_un_visitors_c) daily_un_visitors_c
from (
SELECT
start_date start_date
, COUNT(DISTINCT(daily_un_visitors_c)) daily_un_visitors_c
FROM
(
SELECT
dates.start_date start_date
, transaction_date
, decode(dates.period,''C'',visitant_id,null) daily_un_visitors_c -- Fix for Bug 4916772
FROM
(
SELECT
time_dim.start_date START_DATE,
''C'' PERIOD,
least(time_dim.end_date, &BIS_CURRENT_ASOF_DATE) REPORT_DATE
FROM
'||l_period_type||' time_dim
WHERE
time_dim.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_from ||'
WHERE
cal.report_date = dates.report_date ' || l_where || '
)
GROUP BY
start_date,
transaction_date
)
GROUP BY
start_date
) inner2
where
inner1.start_date = inner2.start_date
)
s,'|| l_period_type||' time_dim
WHERE
time_dim.start_date BETWEEN &BIS_CURRENT_REPORT_START_DATE AND &BIS_CURRENT_ASOF_DATE
AND time_dim.start_date = s.start_date(+)
ORDER BY time_dim.start_date ';