[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
l_custom_sql := 'SELECT MSITES.ID VIEWBYID, MSITES.VALUE VIEWBY,'||
' MSITES.ID IBE_VAL1, '||
' SUM (CASE WHEN CAL.report_date = :l_asof_date '||
' THEN nvl(Total,0) ELSE 0 end) IBE_VAL2, '||
' SUM(CASE WHEN CAL.report_date = :l_prev_date '||
' THEN nvl(Total,0) ELSE 0 end) IBE_VAL3, '||
' SUM(SUM (CASE WHEN CAL.report_date = :l_asof_date '||
' THEN nvl(Total,0) ELSE 0 end)) over() IBE_VAL5, '||
' sum(SUM(CASE WHEN CAL.report_date = :l_prev_date '||
' THEN nvl(Total,0) ELSE 0 end)) over() IBE_VAL6 '||
' FROM '||
' IBE_BI_CUSTTIME_MV FACT, '||
' IBW_BI_MSITE_DIMN_V MSITES, '||
' FII_TIME_RPT_STRUCT_V CAL '||
' WHERE CAL.calendar_id = -1 '||
' AND FACT.Time_Id = CAL.Time_Id '||
' AND FACT.Period_Type_id = CAL.Period_Type_Id '||
' AND BITAND(CAL.Record_Type_Id, :l_record_type_id) = CAL.Record_Type_Id '||
' AND CAL.Report_Date in (:l_asof_date,:l_prev_date) '|| l_c_filter ||
' AND MSITES.ID = FACT.MINISITE_ID '|| l_outer_where_clause || --4660266
' GROUP BY MSITES.ID,MSITES.VALUE';
l_custom_sql := 'SELECT ID VIEWBYID, VALUE VIEWBY, '||
'id IBE_VAL1, '||
'nvl(c_total,0) IBE_VAL2,'||
'nvl(p_total,0) IBE_VAL3,'||
'sum(nvl(c_total,0)) over() IBE_VAL21,'||
'sum(nvl(p_total,0)) over() IBE_VAL22,'||
'decode(nvl(c_total,0),0,null,c_ord/c_total)*100 IBE_VAL4,'||
'decode(nvl(p_total,0),0,null,p_ord/p_total)*100 IBE_VAL5,'||
'decode(sum(nvl(c_total,0)) over(),0,null,(sum(c_ord) over()/sum(c_total)over()))*100 IBE_VAL6,'||
'decode(sum(nvl(p_total,0)) over(),0,null,(sum(p_ord) over()/sum(p_total)over()))*100 IBE_VAL7,'||
'decode(nvl(Count_Curr,0),0,NULL,Amount_Curr/Count_Curr) IBE_VAL8, '||
'decode(nvl(Count_Prev,0),0,NULL,Amount_Prev/Count_Prev) IBE_VAL9,'||
'decode(sum(nvl(Count_Curr,0)) over(), 0, NULL, sum(Amount_Curr) over()/sum(Count_Curr) over()) IBE_VAL10,'||
'decode(sum(nvl(Count_Prev,0)) over(), 0, NULL, sum(Amount_Prev) over()/sum(Count_Prev) over()) IBE_VAL11,'||
'decode(nvl(c_list_amt,0),0,null,c_disc_amt/c_list_amt)*100 IBE_VAL12, '||
'decode(nvl(p_list_amt,0),0,null,p_disc_amt/p_list_amt)*100 IBE_VAL13,'||
'decode(sum(nvl(c_list_amt,0)) over(),0,null,(sum(c_disc_amt) over ()/sum(c_list_amt) over()))*100 IBE_VAL14, '||
'decode(sum(nvl(p_list_amt,0)) over(),0,null,(sum(p_disc_amt) over ()/sum(p_list_amt) over()))*100 IBE_VAL15,'||
'nvl(Amount_Curr,0) IBE_VAL16,'||
'nvl(Amount_Prev,0) IBE_VAL17, '||
'sum(nvl(Amount_Curr,0)) over() IBE_VAL27,'||
'sum(nvl(Amount_Prev,0)) over() IBE_VAL28, '||
'nvl(c_camp_amt,0) IBE_VAL18,'||
'nvl(p_camp_amt,0) IBE_VAL19,'||
'sum(nvl(c_camp_amt,0)) over() IBE_VAL30,'||
'sum(nvl(p_camp_amt,0)) over() IBE_VAL31'||
' FROM ( '||
'SELECT MSITES.ID id, MSITES.VALUE VALUE,'||
'SUM((CASE WHEN (report_date = :l_asof_date and MEASURE_TYPE = ''IBE_ORD_TOTAL'') '||
'THEN decode(:l_c_d,:l_g_p,Amount_g,:l_g_s,Amount_g1,currency_cd_f,amount_f) ELSE 0 end)) Amount_Curr, '||
'SUM((CASE WHEN (report_date = :l_asof_date and MEASURE_TYPE = ''IBE_ORD_TOTAL'')'||
'THEN Tot_Count ELSE 0 end)) Count_Curr, '||
'SUM((CASE WHEN (report_date = :l_prev_date and MEASURE_TYPE = ''IBE_ORD_TOTAL'')'||
'THEN decode(:l_c_d,:l_g_p,Amount_g,:l_g_s,Amount_g1,currency_cd_f,amount_f) ELSE 0 end)) Amount_Prev, '||
'SUM((CASE WHEN (report_date = :l_prev_date and MEASURE_TYPE = ''IBE_ORD_TOTAL'')'||
'THEN Tot_Count ELSE 0 end))Count_Prev,'||
'SUM((CASE WHEN (report_date = :l_asof_date and MEASURE_TYPE = ''IBE_ORD_TOTAL'')'||
'THEN decode(:l_c_d,:l_g_p,List_Amount_g,:l_g_s,list_amount_g1,currency_cd_f,List_Amount_f) ELSE 0 end)) c_list_amt, '||
'SUM((CASE WHEN (report_date = :l_asof_date and MEASURE_TYPE = ''IBE_ORD_TOTAL'')'||
'THEN decode(:l_c_d,:l_g_p,Disc_Amount_g,:l_g_s,Disc_Amount_g1,currency_cd_f,Disc_amount_f) ELSE 0 end)) c_disc_amt, '||
'SUM((CASE WHEN (report_date = :l_prev_date and MEASURE_TYPE = ''IBE_ORD_TOTAL'')'||
'THEN decode(:l_c_d,:l_g_p,List_Amount_g,:l_g_s,List_Amount_g1,currency_cd_f,List_Amount_f) ELSE 0 end)) p_list_amt, '||
'SUM((CASE WHEN (report_date = :l_prev_date and MEASURE_TYPE = ''IBE_ORD_TOTAL'')'||
'THEN decode(:l_c_d,:l_g_p,Disc_amount_g,:l_g_s,Disc_Amount_g1,currency_cd_f,Disc_Amount_f) ELSE 0 end)) p_disc_amt,'||
'SUM((CASE WHEN (report_date = :l_asof_date and MEASURE_TYPE = ''IBE_ORD_CAMPAIGN'')'||
'THEN decode(:l_c_d,:l_g_p,Amount_g,:l_g_s,Amount_g1,currency_cd_f,amount_f) ELSE 0 end)) c_camp_amt,'||
'SUM((CASE WHEN (report_date = :l_prev_date and MEASURE_TYPE = ''IBE_ORD_CAMPAIGN'')'||
'THEN decode(:l_c_d,:l_g_p,Amount_g,:l_g_s,Amount_g1,currency_cd_f,Amount_f) ELSE 0 end)) p_camp_amt,'||
'SUM((CASE WHEN (report_date = :l_asof_date and MEASURE_TYPE = ''IBE_QOT_TOTAL'')'||
'THEN con_ord ELSE 0 end)) c_ord, '||
'SUM((CASE WHEN (report_date = :l_asof_date and MEASURE_TYPE = ''IBE_QOT_TOTAL'')'||
'THEN tot_count ELSE 0 end)) c_total, '||
'SUM((CASE WHEN (report_date = :l_prev_date and MEASURE_TYPE = ''IBE_QOT_TOTAL'')'||
'THEN con_ord ELSE 0 end)) p_ord, '||
'SUM((CASE WHEN (report_date = :l_prev_date and MEASURE_TYPE = ''IBE_QOT_TOTAL'')'||
'THEN tot_count ELSE 0 end)) p_total '||
'FROM IBE_BI_CART_ORD_MV FACT, IBW_BI_MSITE_DIMN_V MSITES,'||
'FII_TIME_RPT_STRUCT_V CAL '||
'WHERE FACT.MINISITE_ID = MSITES.ID '||
'AND CAL.calendar_id = -1 '||
'AND FACT.Time_Id = CAL.Time_Id '||
'AND FACT.Period_Type_id = CAL.Period_Type_Id '||
'AND MEASURE_TYPE in (''IBE_ORD_TOTAL'',''IBE_ORD_CAMPAIGN'',''IBE_QOT_TOTAL'')'||
'AND REPORT_DATE IN (:l_asof_date,:l_prev_date) '|| l_outer_where_clause || --4660266
'AND BITAND(CAL.Record_Type_Id, :l_record_type_id) = CAL.Record_Type_Id '|| l_c_filter ||
'GROUP BY MSITES.ID, MSITES.value)';