[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT LOOKUP_TYPE,LOOKUP_CODE, MEANING
FROM FND_LOOKUPS
WHERE LOOKUP_TYPE IN ('YES_NO', 'IBE_BI_GENERIC')
ORDER BY LOOKUP_TYPE, LOOKUP_CODE;
l_custom_sql := 'SELECT IBE_VAL1, IBE_VAL5, IBE_ATTR1, IBE_ATTR2, IBE_VAL2, IBE_VAL3,'||
' IBE_VAL4, IBE_ATTR3 FROM ('||
' SELECT '||
' ORDERNUMBER IBE_VAL1, '||
' CUST.VALUE IBE_ATTR1, '||
' DECODE(SOURCE,''Y'',:l_QuoteLabel,:l_CartLabel) IBE_ATTR2, '||
' BOOKEDAMOUNT IBE_VAL2, '||
' DISCOUNT IBE_VAL3, '||
' LINES IBE_VAL4, '||
' DECODE(Assisted,''Y'',:l_YesLabel,:l_NoLabel) IBE_ATTR3, '||
' OHEADER_ID IBE_VAL5 '||
' FROM '||
' ( '||
' SELECT CUSTOMERID,'||
' ORDERNUMBER,'||
' SOURCE,'||
' BOOKEDAMOUNT,'||
' DISCOUNT,'||
' LINES,'||
' ASSISTED,'||
' RANK,'||
' OHEADER_ID'||
' FROM '||
' ( '||
' SELECT FACT.CUSTOMERID,'||
' FACT.ordernumber, '||
' FACT.Source, '||
' decode(:l_currency_code,:l_global_primary,fact.Booked_Amt_G,:l_global_secondary,fact.Booked_amt_G1,fact.currency_cd_f,fact.Booked_Amt_F) BookedAmount, '||
' decode(:l_currency_code,:l_global_primary,fact.Discount_G,:l_global_secondary,fact.Discount_G1,fact.currency_cd_f,fact.Discount_F) Discount, '||
' FACT.Lines, '||
' FACT.Assisted , '||
' RANK() OVER (ORDER BY decode(:l_currency_code,:l_global_primary,fact.Booked_Amt_G,:l_global_secondary,fact.Booked_Amt_G1,fact.currency_cd_f,fact.Booked_Amt_F) '||
' DESC NULLS LAST) RANK, '||
' ORD.HEADER_ID OHEADER_ID '||
' FROM '||l_tableList||
' WHERE '||
' CAL.calendar_id = -1'||
' AND FACT.Time_Id = CAL.Time_Id'||
' AND FACT.ORDERNUMBER = ORD.ORDER_NUMBER '||
' AND FACT.Period_Type_id = CAL.Period_Type_Id'||
' AND REPORT_DATE = (:l_asof_date) '||
' AND BITAND(CAL.RECORD_TYPE_ID, :l_record_type_id) = CAL.RECORD_TYPE_ID '||l_msiteFilter||
' ) FACT'||
' WHERE FACT.RANK <= :l_rank) FACT1,'||
' FII_CUSTOMERS_V CUST'||
' WHERE FACT1.CustomerID = CUST.ID )'||
' &ORDER_BY_CLAUSE ' ; --Bug 5076452
SELECT LOOKUP_TYPE,LOOKUP_CODE, MEANING
FROM FND_LOOKUPS
WHERE LOOKUP_TYPE IN ('YES_NO', 'IBE_BI_GENERIC')
ORDER BY LOOKUP_TYPE, LOOKUP_CODE;
l_custom_sql := 'SELECT IBE_VAL1, IBE_ATTR1, IBE_VAL2, IBE_VAL3,'||
' IBE_ATTR2, IBE_VAL4, IBE_ATTR3 FROM ( '||
' SELECT'||
' CARTNUMBER IBE_VAL1,'||
' CUST.VALUE IBE_ATTR1,'||
' AMOUNT IBE_VAL2,'||
' LINES IBE_VAL3,'||
' STATUS IBE_ATTR2,'||
' AGEINDAYS IBE_VAL4,'||
' DECODE(ASSISTED,''Y'',:l_YesLabel,''N'',:l_NoLabel) IBE_ATTR3'||
' FROM'||
' ('||
' SELECT CARTNUMBER,'||
' CUSTOMER,'||
' AMOUNT,'||
' LINES,'||
' STATUS,'||
' AGEINDAYS,'||
' ASSISTED,'||
' RANK'||
' FROM '||
' ('||
' SELECT '||
' CARTNUMBER,'||
' CUSTOMER, '||
' decode(:l_currency_code,:l_global_primary,FACT.BOOKED_AMT_G,:l_global_secondary,FACT.BOOKED_AMT_G1,FACT.currency_cd_f, FACT.BOOKED_AMT_F) AMOUNT,'||
' LINES,'||
' decode(sign(FACT.QUOTE_EXPIRATION_DATE - trunc(SYSDATE)),-1,:l_ExpiredLabel,:l_OrderableLabel) Status,'||
' decode(sign(FACT.QUOTE_EXPIRATION_DATE - trunc(SYSDATE)),-1,FACT.QUOTE_EXPIRATION_DATE-FACT.CREATION_DATE,'||
' trunc(SYSDATE)-FACT.CREATION_DATE) AgeinDays,'||
' FACT.RESOURCE_FLAG Assisted,'||
' RANK() OVER (ORDER BY BOOKED_AMT_G DESC NULLS LAST) RANK'||
' 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 = (:l_asof_date) '||
' AND BITAND(CAL.RECORD_TYPE_ID, :l_record_type_id) = CAL.RECORD_TYPE_ID '||l_msiteFilter||
' ) FACT '||
' WHERE RANK <= :l_rank ) FACT1,'||
' FII_CUSTOMERS_V CUST'||
' WHERE FACT1.CUSTOMER = CUST.ID )'||
' &ORDER_BY_CLAUSE ' ; --Bug 5076452
l_custom_sql := 'SELECT IBE_ATTR1, IBE_VAL1,'||
' IBE_VAL7, IBE_VAL3,IBE_VAL4,IBE_VAL5, IBE_VAL6'||
' FROM ( SELECT CUST.VALUE IBE_ATTR1,'||
' MV.BOOKED_AMOUNT IBE_VAL1,'||
' MV.P_AMOUNT IBE_VAL7, '||
' MV.ASSISTED IBE_VAL3,'||
' MV.NUM_OF_ORDERS IBE_VAL4,'||
' to_number(MV.AVG_ORD_VAL) IBE_VAL5,'||
' MV.DISCOUNT IBE_VAL6 '||
' FROM ('||
' SELECT CUSTOMER_ID,'||
' C_AMOUNT BOOKED_AMOUNT,'||
' P_AMOUNT P_AMOUNT,'||
' decode(C_AMOUNT,0,NULL,(ASSISTED_AMOUNT/C_AMOUNT))*100 ASSISTED,'||
' NUM_OF_ORDERS,'||
' decode(NUM_OF_ORDERS,0,NULL,C_AMOUNT/NUM_OF_ORDERS) AVG_ORD_VAL,'||
' decode(LIST_AMOUNT,0,NULL,(DISC_AMOUNT/LIST_AMOUNT))*100 DISCOUNT'||
' FROM'||
' ('||
' SELECT MV.CUSTOMER_ID,'||
' SUM(CASE WHEN 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) C_AMOUNT,'||
' SUM(CASE WHEN 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) P_AMOUNT,'||
' SUM(CASE WHEN REPORT_DATE = :l_asof_date THEN'||
' decode(:l_currency_code,:l_global_primary,ASSISTED_AMT_G,:l_global_secondary,ASSISTED_AMT_G1, CURRENCY_CD_F,ASSISTED_AMT_F) ELSE 0 END) ASSISTED_AMOUNT,'||
' SUM(CASE WHEN REPORT_DATE = :l_asof_date THEN'||
' ORDERS_CNT ELSE 0 END) NUM_OF_ORDERS,'||
' SUM(CASE WHEN REPORT_DATE = :l_asof_date THEN'||
' decode(:l_currency_code,:l_global_primary,LIST_AMOUNT_G,:l_global_secondary,LIST_AMOUNT_G1,CURRENCY_CD_F, LIST_AMOUNT_F) ELSE 0 END) LIST_AMOUNT,'||
' SUM(CASE WHEN REPORT_DATE = :l_asof_date THEN'||
' decode(:l_currency_code,:l_global_primary,DISC_AMOUNT_G,:l_global_secondary,DISC_AMOUNT_G1, CURRENCY_CD_F,DISC_AMOUNT_F) ELSE 0 END) DISC_AMOUNT,'||
' RANK () OVER (ORDER BY SUM(CASE WHEN 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) DESC) RANK'||
' FROM '||l_tableList||
' WHERE MV.TIME_ID = CAL.TIME_ID '||l_msiteFilter||
' AND CAL.REPORT_DATE IN (:l_asof_date, :l_prev_date)'||
' AND CAL.PERIOD_TYPE_ID = MV.PERIOD_TYPE_ID'||
' AND BITAND(CAL.RECORD_TYPE_ID, :l_record_type_id) = CAL.RECORD_TYPE_ID'||
' GROUP BY mv.CUSTOMER_ID'||
' ) WHERE RANK <= :l_rank'||
' ORDER BY RANK) mv, FII_CUSTOMERS_V cust'||
' WHERE MV.CUSTOMER_ID = CUST.ID ) '||
' &ORDER_BY_CLAUSE ' ; -- Bug 5076452
l_custom_sql := 'SELECT IBE_ATTR1, IBE_ATTR3, IBE_VAL1, IBE_VAL2, IBE_VAL8, IBE_VAL4, '||
' IBE_VAL5, IBE_VAL6, IBE_VAL7 FROM ('||
' SELECT VALUE IBE_ATTR1, DESCRIPTION IBE_ATTR3,'||
' IBE_VAL1,IBE_VAL2,IBE_VAL8,IBE_VAL4,to_number(IBE_VAL5) IBE_VAL5,'||
' IBE_VAL6,IBE_VAL7 FROM'||
' ('||
' SELECT'||
' decode(TOTAL_CARTS,0,NULL,(TOTAL_CONV_CARTS/TOTAL_CARTS))*100 IBE_VAL1,'||
' C_AMOUNT IBE_VAL2,'||
' P_AMOUNT IBE_VAL8,'||
' decode(C_AMOUNT,0,NULL,(ASSISTED_AMOUNT/C_AMOUNT))*100 IBE_VAL4,'||
' decode(TOTAL_ORDERS,0,NULL,C_AMOUNT/TOTAL_ORDERS) IBE_VAL5,'||
' decode(LIST_AMOUNT,0,NULL,(DISC_AMOUNT/LIST_AMOUNT))*100 IBE_VAL6,'||
' NUM_OF_LINES IBE_VAL7,'||
' ITEM_ID,'||
' RANK'||
' FROM'||
' ('||
' SELECT FACT.ITEM_ID,'||
' SUM(CASE WHEN REPORT_DATE = :l_asof_date THEN TOT_CART_COUNT ELSE 0 END) TOTAL_CARTS,'||
' SUM(CASE WHEN REPORT_DATE = :l_asof_date THEN CON_ORD ELSE 0 END) TOTAL_CONV_CARTS,'||
' SUM(CASE WHEN REPORT_DATE = :l_asof_date '||
' THEN decode(:l_currency_code,:l_global_primary,BOOKED_AMOUNT_G,:l_global_secondary,BOOKED_AMOUNT_G1,CURRENCY_CD_F, BOOKED_AMOUNT_F) ELSE 0 END) C_AMOUNT,'||
' SUM(CASE WHEN REPORT_DATE = :l_prev_date '||
' THEN decode(:l_currency_code,:l_global_primary,BOOKED_AMOUNT_G,:l_global_secondary,BOOKED_AMOUNT_G1,CURRENCY_CD_F, BOOKED_AMOUNT_F) ELSE 0 END) P_AMOUNT,'||
' SUM(CASE WHEN REPORT_DATE = :l_asof_date THEN decode(RESOURCE_FLAG,''Y'','||
' decode(:l_currency_code,:l_global_primary,BOOKED_AMOUNT_G,:l_global_secondary,BOOKED_AMOUNT_G1,CURRENCY_CD_F, BOOKED_AMOUNT_F),0) ELSE 0 END) ASSISTED_AMOUNT, '||
' SUM(CASE WHEN REPORT_DATE = :l_asof_date THEN TOT_ORD_COUNT ELSE 0 END) TOTAL_ORDERS,'||
' SUM(CASE WHEN REPORT_DATE = :l_asof_date '||
' THEN decode(:l_currency_code,:l_global_primary,BOOKED_LIST_AMT_G,:l_global_secondary,BOOKED_LIST_AMT_G1,CURRENCY_CD_F, BOOKED_LIST_AMT_F) ELSE 0 END) LIST_AMOUNT,'||
' SUM(CASE WHEN REPORT_DATE = :l_asof_date '||
' THEN decode(:l_currency_code,:l_global_primary,DISCOUNT_AMOUNT_G,:l_global_secondary,DISCOUNT_AMOUNT_G1, CURRENCY_CD_F,DISCOUNT_AMOUNT_F) ELSE 0 END) DISC_AMOUNT,'||
' SUM(CASE WHEN REPORT_DATE = :l_asof_date '||
' THEN NUM_OF_ORD_LINES ELSE 0 END) NUM_OF_LINES,'||
' RANK() OVER(ORDER BY SUM(CASE WHEN REPORT_DATE = :l_asof_date'||
' THEN decode(:l_currency_code,:l_global_primary,BOOKED_AMOUNT_G,:l_global_secondary,BOOKED_AMOUNT_G1,CURRENCY_CD_F, BOOKED_AMOUNT_F) ELSE 0 END) DESC) RANK '||
' FROM '|| l_tableList ||
' WHERE CAL.calendar_id = -1'||
' AND FACT.Time_Id = CAL.Time_Id'||
' AND FACT.Period_Type_id = CAL.Period_Type_Id '||l_msiteFilter||
' AND REPORT_DATE IN (:l_asof_date,:l_prev_date)'||
' AND BITAND(CAL.Record_Type_Id, :l_record_type_id) = CAL.Record_Type_Id'||
' GROUP BY '||
' FACT.ITEM_ID'||
' ) WHERE RANK <= :l_rank'||
' ) FACT ,ENI_ITEM_V ENI'||
' WHERE '||
' FACT.ITEM_ID = ENI.ID)'||
' &ORDER_BY_CLAUSE ' ; --Bug 5076452