APPS.IBE_BI_PROD_CATG_PVT SQL Statements

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

Line: 14

  SELECT DISTINCT LEAF_NODE_FLAG INTO L_LEAF_NODE
  FROM eni_item_prod_cat_lookup_v --4776922
  WHERE ID = replace(l_Category_ID,'''','')
  AND ID = PARENT_ID;

Line: 46

  l_select		VARCHAR2(5000) ;

Line: 47

  l_select1		VARCHAR2(5000) ;

Line: 152

       l_select1:= ' eip.value value,  eip. parent_id  catid,eip.value description ,eip.LEAF_NODE_FLAG ';

Line: 156

       l_select1:= ' eip.value value,  edh.imm_child_id  catid,eip.value description ,eip.LEAF_NODE_FLAG ';

Line: 163

   l_select1:= ' item.value value,  item.id  catid,item.description  description ';

Line: 172

  l_select  := '   VALUE VIEWBY, CATID VIEWBYID, VALUE IBE_ATTR3 , '||
		     '   CATID IBE_ATTR1,  '||
		     '   description IBE_ATTR4, ' ||
		     '   decode(NO_CARTS,0,NULL,CON_ORD/NO_CARTS)*100 IBE_VAL1, '||
		     '   CART_AMT IBE_VAL2, '||
		     '   C_ORD_AMT IBE_VAL3, '||
		     '   P_ORD_AMT IBE_VAL8, '||
                     '   decode(C_ORD_AMT,0,NULL,A_ORD_AMT/C_ORD_AMT)*100 IBE_VAL5, '||
		     '   decode(NO_ORD,0,NULL, C_ORD_AMT/NO_ORD) IBE_VAL6, '||
		     '   decode(LIST_AMT,0,NULL,DISC_AMT/LIST_AMT)*100 IBE_VAL7, '||
		     '   decode(sum(NO_CARTS) over(),0,NULL,sum(CON_ORD) over()/sum(NO_CARTS) over())*100 IBE_TOT_VAL1, '||
		     '   sum(CART_AMT) over() IBE_TOT_VAL2, '||
		     '   sum(C_ORD_AMT) over() IBE_TOT_VAL3, '||
                     '   decode(sum(P_ORD_AMT) over(),0,NULL,(sum(C_ORD_AMT) over()- sum(P_ORD_AMT) over())/sum(P_ORD_AMT) over())*100 IBE_TOT_VAL4, '||
                     '   decode(sum(C_ORD_AMT) over(),0,NULL,sum(A_ORD_AMT) over()/sum(C_ORD_AMT) over())*100 IBE_TOT_VAL5, '||
                     '   decode(sum(NO_ORD) over(),0,NULL,sum(C_ORD_AMT) over()/sum(NO_ORD) over()) IBE_TOT_VAL6, '||
                     '   decode(sum(LIST_AMT) over(),0,NULL,sum(DISC_AMT) over()/sum(LIST_AMT) over())*100 IBE_TOT_VAL7, ' ||l_url ||
		     ' FROM ('||
		     ' SELECT  '||l_select1|| ' , '||
		     ' SUM(decode(REPORT_DATE,&BIS_CURRENT_ASOF_DATE,tot_cart_count,0)) NO_CARTS, '||
		     ' SUM(decode(REPORT_DATE,&BIS_CURRENT_ASOF_DATE,CON_ORD,0)) CON_ORD, '||
                     ' SUM(decode(REPORT_DATE,&BIS_CURRENT_ASOF_DATE,tot_ord_count,0)) NO_ORD, '||
                     ' SUM(decode(REPORT_DATE,&BIS_CURRENT_ASOF_DATE, '||
	             ' decode(:l_ccy,:l_gl_p,carts_amt_g,:l_gl_s,carts_amt_g1,currency_cd_f,carts_amt_F),0)) CART_AMT, '||
                     ' SUM(decode(REPORT_DATE,&BIS_CURRENT_ASOF_DATE, '||
	             ' decode(:l_ccy,:l_gl_p,booked_amount_g,:l_gl_s,booked_amount_g1,currency_cd_f,booked_amount_f),0)) C_ORD_AMT, '||
                     ' SUM(decode(REPORT_DATE,&BIS_PREVIOUS_ASOF_DATE, '||
	             ' decode(:l_ccy,:l_gl_p,booked_amount_g,:l_gl_s,booked_amount_g1,currency_cd_f,booked_amount_F),0)) P_ORD_AMT, '||
                     ' SUM(decode(REPORT_DATE,&BIS_CURRENT_ASOF_DATE, '||
	             ' decode(resource_flag,''Y'',decode(:l_ccy,:l_gl_p,booked_amount_g,:l_gl_s,booked_amount_g1,currency_cd_f,booked_amount_f),0),0)) A_ORD_AMT, '||
                     ' SUM(decode(REPORT_DATE,&BIS_CURRENT_ASOF_DATE, '||
	             ' decode(:l_ccy,:l_gl_p,discount_amount_g,:l_gl_s,discount_amount_g1,currency_cd_f,discount_amount_F),0)) DISC_AMT, '||
                     ' SUM(decode(REPORT_DATE,&BIS_CURRENT_ASOF_DATE, '||
	             ' decode(:l_ccy,:l_gl_p, booked_list_amt_g,:l_gl_s, booked_list_amt_g1,currency_cd_f, booked_list_amt_F),0)) LIST_AMT, '||
                     ' SUM(decode(REPORT_DATE,&BIS_CURRENT_ASOF_DATE,tot_cart_count, 0)) T_NO_CARTS, '||
                     ' SUM(decode(REPORT_DATE,&BIS_CURRENT_ASOF_DATE,con_ord, 0)) T_CON_ORD, '||
                     ' SUM(decode(REPORT_DATE,&BIS_CURRENT_ASOF_DATE,tot_ord_count, 0)) T_NO_ORD, '||
                     ' SUM(decode(REPORT_DATE,&BIS_CURRENT_ASOF_DATE,decode(:l_ccy,:l_gl_p,carts_amt_g,:l_gl_s,carts_amt_g1,currency_cd_f,carts_amt_f),0)) T_CART_AMT, '||
                     ' SUM(decode(REPORT_DATE,&BIS_CURRENT_ASOF_DATE,decode(:l_ccy,:l_gl_p,booked_amount_g,:l_gl_s,booked_amount_g1,currency_cd_f,booked_amount_f),0)) T_C_ORD_AMT, '||
                     ' SUM(decode(REPORT_DATE,&BIS_PREVIOUS_ASOF_DATE,decode(:l_ccy,:l_gl_p,booked_amount_g,:l_gl_s,booked_amount_g1,currency_cd_f,booked_amount_F),0)) T_P_ORD_AMT, '||
                     ' SUM(decode(REPORT_DATE,&BIS_CURRENT_ASOF_DATE,decode( resource_flag,''Y'','||
	             ' decode(:l_ccy,:l_gl_p,booked_amount_g,:l_gl_s,booked_amount_g1,currency_cd_f,booked_amount_f),0),0)) T_A_ORD_AMT, '||
                     ' SUM(decode(REPORT_DATE,&BIS_CURRENT_ASOF_DATE,decode(:l_ccy,:l_gl_p,discount_amount_g,:l_gl_s,discount_amount_g1,currency_cd_f,discount_amount_F),0)) T_DISC_AMT, '||
                     ' SUM(decode(REPORT_DATE,&BIS_CURRENT_ASOF_DATE,decode(:l_ccy,:l_gl_p,booked_list_amt_g,:l_gl_s,booked_list_amt_g1,currency_cd_f,booked_list_amt_F),0)) T_LIST_AMT  ';

Line: 313

    l_custom_sql  := ' SELECT ' ||  l_select  ||
                   ' FROM ' ||  l_from  ||' where '||l_where ||
		   ' group by '||l_groupby||
		   ') where '||  l_where2||
		   ' &ORDER_BY_CLAUSE ';