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.

APPS.IBW_BI_PROD_REF_PVT SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 11


 *					      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_WEB_REF_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.

Line: 33


  l_outer_select    VARCHAR2(3000) ;

Line: 34


  l_inner_select    VARCHAR2(3000) ;

Line: 102


  l_outer_select    := '';

Line: 103


  l_inner_select    := '';

Line: 112


 /* &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

--  Not returning value for IBW_VAL2 and returning value for IBW_VAL11 as per bug # 4772549.

  l_outer_select  := '   VIEW_BY VIEWBY, VIEWBYID, '||
                     '   nvl(c_visits,0) IBW_VAL1 '||
                     '  ,nvl(p_visits,0) IBW_VAL11 '||
                     ' , DECODE(nvl(c_visits,0),0,null, (page_views/ c_visits)) IBW_VAL3 ' ||--Avg Page Views
                     ' , nvl(web_registrations,0) IBW_VAL4 ' ||
		     ' , nvl(carts,0)	        IBW_VAL5 ' ||
		     ' , nvl(a_leads,0)	        IBW_VAL6 ' ||
		     ' , DECODE(nvl(carts,0),0,null,(orders/carts)*100)                IBW_VAL7 ' ||    --Fix for Bug 4654866 - issue # 11 G
		     ' , DECODE(nvl(c_visits,0),0,null,(orders_site_visits/c_visits)*100) IBW_VAL8 ' ||   --Fix for Bug 4654866 - issue # 11 H
    	             ' , nvl(booked_amt,0) IBW_VAL9 ' ||
                     ' , DECODE(nvl(orders,0),0,null,booked_amt/orders) IBW_VAL10 ' ||
                     --For Grand Totals
                     ' , SUM(nvl(c_visits,0)) OVER() IBW_G_TOT1 ' ||
                     ' ,  DECODE(sum(nvl(p_visits,0)) over(),0,null, ((sum(c_visits) over() - sum(p_visits) over() )/ sum(p_visits) over() )*100) IBW_G_TOT2 ' || --Fix for Bug 4654866 - issue # 11 C
                     ' , DECODE(sum(nvl(c_visits,0)) over() ,0,null, (sum(page_views) over() / sum(c_visits) over() )) IBW_G_TOT3 ' ||
                     ' , SUM(nvl(web_registrations,0)) OVER() IBW_G_TOT4 '||
		     ' , SUM(nvl(carts,0)) OVER() IBW_G_TOT5 '||
		     ' , SUM(nvl(a_leads,0)) OVER() IBW_G_TOT6 '||
		     ' , DECODE(sum(nvl(carts,0)) over() ,0,null ,(SUM(orders) over() /SUM(carts) over() )*100) IBW_G_TOT7 '||  --Fix for Bug 4654866 - issue # 11 G
		     ' , DECODE(sum(nvl(c_visits,0)) over() ,0,null,(sum(orders_site_visits) over() /sum(c_visits) over())*100) IBW_G_TOT8 '|| --Fix for Bug 4654866 - issue # 11 H
		     ' , SUM(nvl(booked_amt,0)) OVER() IBW_G_TOT9 '||
		     ' , DECODE(sum(nvl(orders,0)) over() ,0,null,sum(booked_amt) over() /sum(orders) over()) IBW_G_TOT10 '
		     ;

Line: 147


    fnd_log.string(fnd_log.level_statement,l_full_path,'After Outer Select ');

Line: 152


   l_inner_select  := ' SUM(decode(cal.report_date,&BIS_CURRENT_ASOF_DATE,   visits   ,0)) c_visits ' ||
		     ' ,SUM(decode(cal.report_date,&BIS_PREVIOUS_ASOF_DATE,visits,0)) p_visits ' ||
                     ' , SUM(decode(cal.report_date,&BIS_CURRENT_ASOF_DATE,  page_views ,0)) page_views ' ||
		     ' , SUM(decode(cal.report_date,&BIS_CURRENT_ASOF_DATE,   web_registrations ,0)) web_registrations ' ||
                     ' , sum(decode(cal.report_date,&BIS_CURRENT_ASOF_DATE,   carts ,0)) carts ' ||
		     ' , sum(decode(cal.report_date,&BIS_CURRENT_ASOF_DATE,  a_leads ,0)) a_leads ' ||
                     ' , sum(decode(cal.report_date,&BIS_CURRENT_ASOF_DATE,  orders ,0))  orders ' ||--for  Avg Web Order Value denomenator
		     ' , sum(decode(cal.report_date,&BIS_CURRENT_ASOF_DATE,  orders_site_visits ,0)) orders_site_visits ' ||--for browse to buy numerator
		     ' , sum(decode(cal.report_date,&BIS_CURRENT_ASOF_DATE,  decode(:l_currency,:l_gp_currency,BOOKED_AMT_G,:l_gs_currency,BOOKED_AMT_G1,CURRENCY_CD_F,BOOKED_AMT_F) ,0)) booked_amt'
		      ;

Line: 166


    fnd_log.string(fnd_log.level_statement,l_full_path,'After Inner Select ');

Line: 203


    l_inner_select    := ' ref_dim.VALUE VIEW_BY, ref_dim.ID VIEWBYID, '||
                         l_inner_select;

Line: 215


    l_inner_select    := ' site.value view_by, site.id viewbyid, ' || l_inner_select;

Line: 258


  l_custom_sql  := ' SELECT ' || l_outer_select ||
                   ' FROM ' ||
                    ' (SELECT '   || l_inner_select ||
                    ' FROM '     || l_from ||
                    ' WHERE '    || l_where ||
                    ' GROUP BY '  || l_inner_group_by ||
                   ')' ||
             ' &ORDER_BY_CLAUSE ';

Line: 334


  l_outer_select        VARCHAR2(3200);

Line: 335


  l_grand_tot_select    VARCHAR2(3200);

Line: 339


  l_inner_select1	VARCHAR2(3000) ;

Line: 340


  l_inner_select2	VARCHAR2(3000) ;

Line: 341


  l_inner_select0	VARCHAR2(3000) ;

Line: 344


  l_inner_select0_group_by  VARCHAR2(1000);

Line: 411


  l_outer_select    := '';

Line: 412


  l_inner_select0   := '';

Line: 413


  l_inner_select1    := '';

Line: 414


  l_inner_select2    := '';

Line: 435


  l_outer_select  := '   VIEW_BY VIEWBY, VIEWBYID,prod_descr IBW_ATTR1, nvl(page_views,0) IBW_VAL1 '||
                     ' , DECODE( nvl(visits,0),0,null, ((product_visit/ visits)*100)) IBW_VAL2 ' ||--Percent Product Visits
                     ' , nvl(duv,0) IBW_VAL3 ' ||--Daily Unique Visitor
                     ' , nvl(carts,0) IBW_VAL4 ' ||--Carts
		     ' , DECODE( nvl(carts,0),0,null, (ordered_carts/carts)*100) IBW_VAL5 ' ||--Cart Conversion
		     ' , nvl(booked_orders,0)    IBW_VAL6 ' ||
		     ' , nvl(booked_amt,0)       IBW_VAL7 ' ||
		     ' , DECODE(nvl(product_visit,0),0,null,(orders_site_visits/product_visit)*100) IBW_VAL8 ' ||--Browse to buy
           --Changes for the ER 4760433
                ' , nvl(carts,0) IBW_VAL10 ' ||
                ' , nvl(page_views,0) IBW_VAL9 '||
            --Changes for the ER 4760433
    	                 --For Grand Totals
                     ' , SUM(nvl(page_views,0)) OVER() IBW_G_TOT1 ' ||
                     ' , DECODE(nvl(visits,0),0,null, ( g_product_visit / visits )*100)  IBW_G_TOT2 ' ||
                     ' , sum(nvl(duv,0)) over() IBW_G_TOT3 ' ||
                     ' , nvl(g_carts,0) IBW_G_TOT4 '||
		     ' , DECODE( nvl(g_carts,0),0,null, (g_ordered_carts/g_carts)*100) IBW_G_TOT5 '||
		     ' , nvl(g_booked_orders,0) IBW_G_TOT6 '||
		     ' , sum(nvl(booked_amt,0)) over() IBW_G_TOT7'||
		     ' , DECODE( nvl(g_product_visit,0) ,0,null,(g_orders_site_visits/g_product_visit )*100) IBW_G_TOT8 ';

Line: 461


    fnd_log.string(fnd_log.level_statement,l_full_path,'After Outer Select ');

Line: 465


l_inner_select0  :=  '  SUM(page_views) page_views ' ||
		     ' , SUM(product_visit) product_visit ' || --Numerator for percent product visits and denominator for Browse to Buy
                     ' , SUM(visits) visits ' ||--Denominator for percent product visits
		     ' , SUM(duv) duv ' ||--Daily unique visitor
                     ' , SUM(carts)  carts ' ||--for carts and carts converesion denominator
		     ' , SUM(ordered_carts) ordered_carts ' ||--for cart conversion numerator
		     ' , SUM(booked_orders) booked_orders ' ||
		     ' , sum(booked_amt) booked_amt'||
		     ' , SUM(orders_site_visits) orders_site_visits '--Browse to buy numerator
		      ;

Line: 479


 l_inner_select1  := '  SUM(page_views) page_views ' ||
		     ' , count(distinct visit_id) product_visit ' || --Numerator for percent product visits and denominator for Browse to Buy
                     ' , null  visits ' ||--Denominator for percent product visits
		     ' , count(visitant_id) duv ' ||--Daily unique visitor
                     ' , count(distinct cart_id)  carts ' ||--for carts and carts converesion denominator
		     ' , count(distinct qot_order_id) ordered_carts ' ||--for cart conversion numerator
		     ' , count(distinct order_id) booked_orders ' ||
		     ' , sum(decode(:l_currency,:l_gp_currency,BOOKED_AMT_G,:l_gs_currency,BOOKED_AMT_G1,CURRENCY_CD_F,BOOKED_AMT_F)) booked_amt'||
		     ' , count(distinct orders_site_visits) orders_site_visits '--Browse to buy numerator
		      ;

Line: 490


l_inner_select2  := '    null page_views ' ||
		     ' , null product_visit ' || --Numerator for percent product visits and denominator for Browse to Buy
                     ' , SUM(no_visits) visits ' ||--Denominator for percent product visits
		     ' , null duv ' ||--Daily unique visitor
                     ' , null  carts ' ||--for carts and carts converesion denominator
		     ' , null ordered_carts ' ||--for cart conversion numerator
		     ' , null booked_orders ' ||
		     ' , null booked_amt'||
		     ' , null orders_site_visits '--Browse to buy numerator
		      ;

Line: 504


    fnd_log.string(fnd_log.level_statement,l_full_path,'After Inner Select ');

Line: 551


    l_outer_select  := l_outer_select||' , DECODE(leaf_node_flag,''Y'','||''''||l_url_str_prod||''''||','||''''||l_url_str_prodcatg||''''||' ) IBW_URL1   ';

Line: 554


    l_inner_select1    :=  ' p.VALUE view_by, edh.imm_child_id viewbyid, '||    -- Change New  Bug 5373132
                           ' edh.leaf_node_flag  leaf_node_flag,null prod_descr , '||      ---  Change New  Bug 5373132
                         l_inner_select1;                                   -- Change New  Bug 5373132

Line: 557


    l_inner_select2    := ' p.value VIEW_BY, p.id  VIEWBYID, p.leaf_node_flag leaf_node_flag,null prod_descr , '||      --- Change  Bug 5373132
                         l_inner_select2;                       -- Change  Bug 5373132

Line: 560


    l_inner_select1    := ' p.value VIEW_BY, p.parent_id  VIEWBYID, p.leaf_node_flag leaf_node_flag,null prod_descr , '||      ---  Change  Bug 5373132
                         l_inner_select1;                                                                                      ---  Change  Bug 5373132

Line: 562


    l_inner_select2    := ' p.value VIEW_BY, p.parent_id  VIEWBYID, p.leaf_node_flag leaf_node_flag,null prod_descr , '||      --- Change  Bug 5373132
                         l_inner_select2;          -- Change  Bug 5373132

Line: 567


    l_inner_select0  := ' view_by,viewbyid,leaf_node_flag,prod_descr, '||l_inner_select0;

Line: 569


    l_inner_select0_group_by := ' view_by,viewbyid,leaf_node_flag,prod_descr ';

Line: 615


     l_outer_select  := l_outer_select||' , null IBW_URL1 ';

Line: 617


    l_inner_select1    := ' item.value view_by, item.id viewbyid,item.description prod_descr , ' || l_inner_select1;

Line: 619


    l_inner_select2    := ' item.value view_by, item.id viewbyid,item.description prod_descr , ' || l_inner_select2;

Line: 621


    l_inner_select0  := ' view_by,viewbyid,prod_descr, '||l_inner_select0;

Line: 623


    l_inner_select0_group_by := ' view_by,viewbyid,prod_descr ';

Line: 701


l_grand_tot_select := 	'  count(distinct visit_id) g_product_visit ' || --Numerator for percent product visits and denominator for Browse to Buy
                     ' , count(visitant_id)  g_duv ' ||--Daily unique visitor
                     ' , count(distinct cart_id)   g_carts ' ||--for carts and carts converesion denominator
		     ' , count(distinct qot_order_id)  g_ordered_carts ' ||--for cart conversion numerator
		     ' , count(distinct order_id)  g_booked_orders ' ||
		     ' , count(distinct orders_site_visits)  g_orders_site_visits '; --Browse to buy numerator

Line: 776


  l_custom_sql  := ' SELECT ' || l_outer_select ||
                   ' FROM ' ||
		    ' ( SELECT '|| l_inner_select0 ||
		    ' FROM '||
                    ' (SELECT '   || l_inner_select1 ||
                    ' FROM '     || l_from1 ||
                    ' WHERE '    || l_where1 ||
                    ' GROUP BY '  || l_inner_group_by ||
		    ' UNION ALL ' ||
		    ' SELECT  '   || l_inner_select2 ||
		     ' FROM '     || l_from2 ||
		     ' WHERE '    || l_where2 ||
		     ' GROUP BY '  || l_inner_group_by1 ||          --- Change Bug 5373132
                   ')' ||
		   ' GROUP BY '|| l_inner_select0_group_by ||
		   ' HAVING ' || l_having ||' ), ( '||
		   ' SELECT '|| l_grand_tot_select ||--SELECT CALUSE FOR GRAND TOTAL RETRICES ONE ROW
		   ' FROM   '|| l_grand_tot_from ||
		   ' WHERE '|| l_grand_tot_where ||
		   ' ) ' ||
             ' &ORDER_BY_CLAUSE ';