[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
l_custom_sql :='SELECT MEANING IBE_ATTR1,nvl(TOT_COUNT,0) IBE_VAL1,nvl(CUR_AMT,0) IBE_VAL2,'||
' nvl(OLD_AMT,0) IBE_VAL6,nvl(NO_LINES,0) IBE_VAL4,'||
' DECODE(nvl(TOT_COUNT,0),0,NULL,(CON_ORD/TOT_COUNT)*100) IBE_VAL5 from('||
' SELECT decode(flkup.lookup_code,''IBE_ORD_ASSISTED'', 3,'||
' ''IBE_ORD_UNASSISTED'',4,'||
' ''IBE_ORD_TOTAL'',5,'||
' ''IBE_ORD_CAMPAIGN'',7,'||
' ''IBE_ORD_QUOTING'',9,'||
' ''IBE_QOT_ASSISTED'', 0,'||
' ''IBE_QOT_UNASSISTED'',1,'||
' ''IBE_QOT_TOTAL'',2,'||
' ''IBE_QOT_CAMPAIGN'',6,'||
' ''IBE_QOT_QUOTING'',8 ) OB,'||
' flkup.MEANING, SUM(TOT_COUNT) TOT_COUNT, SUM(NEW_VALUE) CUR_AMT,'||
' SUM(OLD_VALUE) OLD_AMT,SUM(NO_LINES) NO_LINES,'||
' SUM(con_ord) CON_ORD'||
' FROM ('||
' SELECT MEASURE_TYPE ,'||
' SUM(CASE WHEN CAL.report_date = :l_asof_date'||
' THEN Tot_Count else 0 end) TOT_COUNT ,'||
' SUM(CASE WHEN CAL.report_date = :l_asof_date'||
' THEN decode(:l_currency_code,:l_global_primary,Amount_g,:l_global_secondary,Amount_g1,CURRENCY_CD_F,Amount_f)'||
' ELSE 0 END) new_value,'||
' SUM(CASE WHEN CAL.report_date = :l_prev_date'||
' THEN decode(:l_currency_code,:l_global_primary,Amount_g,:l_global_secondary,Amount_g1,CURRENCY_CD_F,Amount_f)'||
' ELSE 0 END) old_value,'||
' SUM(CASE WHEN CAL.report_date = :l_asof_date'||
' THEN FACT.NO_LINES else 0 end) NO_LINES,'||
' SUM(CASE WHEN CAL.report_date = :l_asof_date'||
' THEN con_ord'||
' ELSE NULL END) con_ord'||
' FROM '||l_tableList||
' 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 (:l_asof_date,:l_prev_date)'||l_msiteFilter||
' AND BITAND(CAL.Record_Type_Id, :l_record_type_id) = CAL.Record_Type_Id'||
' GROUP BY MEASURE_TYPE) FACT1, '||
' FND_LOOKUPS flkup'||
' WHERE'||
' flkup.LOOKUP_TYPE = ''IBE_BI_MEASURES'''||
' AND flkup.LOOKUP_CODE = FACT1.MEASURE_TYPE(+)'||
' GROUP BY flkup.meaning,decode(flkup.lookup_code,''IBE_ORD_ASSISTED'', 3,'||
'''IBE_ORD_UNASSISTED'',4,'||
'''IBE_ORD_TOTAL'',5,'||
'''IBE_ORD_CAMPAIGN'',7,'||
'''IBE_ORD_QUOTING'',9,'||
'''IBE_QOT_ASSISTED'', 0,'||
'''IBE_QOT_UNASSISTED'',1,'||
'''IBE_QOT_TOTAL'',2,'||
'''IBE_QOT_CAMPAIGN'',6,'||
'''IBE_QOT_QUOTING'',8 )) ORDER BY OB';