MATERIALIZED VIEW: APPS.IBE_BI_TOP_ORD_MV

Source

SELECT TIME_ID, PERIOD_TYPE_ID, ORDERNUMBER, CUSTOMERID, SOURCE, CURRENCY_CD_F, BOOKED_AMT_G, BOOKED_AMT_G1, BOOKED_AMT_F, DISCOUNT_G, DISCOUNT_G1, DISCOUNT_F, LINES, ASSISTED, MINISITE_ID, RANK FROM ( SELECT ORDERNUMBER, CUSTOMERID, SOURCE, CURRENCY_CD_F, ASSISTED, MINISITE_ID, BOOKED_AMT_G,BOOKED_AMT_G1, BOOKED_AMT_F, DISCOUNT_G,DISCOUNT_G1, DISCOUNT_F, LINES, TIME_ID, PERIOD_TYPE_ID, RANK() OVER (PARTITION BY MINISITE_ID,TIME_ID,PERIOD_TYPE_ID ORDER BY BOOKED_AMT_G DESC NULLS LAST) RANK  FROM ( SELECT  (CASE WHEN GROUPING (TIME.report_date_julian) = 0 THEN TIME.report_date_julian WHEN GROUPING (TIME.week_id)=0 THEN TIME.week_id WHEN GROUPING (TIME.ent_period_id)=0 THEN TIME.ent_period_id WHEN GROUPING (TIME.ent_qtr_id)=0 THEN TIME.ent_qtr_id Else TIME.ent_year_id End) Time_Id, (CASE WHEN GROUPING (TIME.report_date_julian) = 0 THEN 1 WHEN GROUPING (TIME.week_id)=0 THEN 16 WHEN GROUPING (TIME.ent_period_id)=0 THEN 32 WHEN GROUPING (TIME.ent_qtr_id)=0 THEN 64 Else 128 END) Period_Type_id, ORDER_NUMBER ORDERNUMBER, CUSTOMER_ID  CUSTOMERID, PUBLISHED_QUOTE_FLAG SOURCE, CURRENCY_CD_F CURRENCY_CD_F, SUM(AMOUNT_G) BOOKED_AMT_G, SUM(AMOUNT_G1) BOOKED_AMT_G1, SUM(AMOUNT_F) BOOKED_AMT_F, SUM(DECODE(LIST_AMOUNT_G,0,0,(DISC_AMOUNT_G/LIST_AMOUNT_G)*100)) DISCOUNT_G, SUM(DECODE(LIST_AMOUNT_G1,0,0,(DISC_AMOUNT_G1/LIST_AMOUNT_G1)*100)) DISCOUNT_G1, SUM(DECODE(LIST_AMOUNT_F,0,0,(DISC_AMOUNT_F/LIST_AMOUNT_F)*100)) DISCOUNT_F, SUM(ORDER_LINES) LINES, RESOURCE_FLAG ASSISTED, MINISITE_ID FROM APPS.IBE_BI_ORDERS_MV FACT, FII.FII_TIME_DAY TIME WHERE TIME_BOOKED_DATE_ID = TIME.REPORT_DATE GROUP BY ORDER_NUMBER, CUSTOMER_ID, PUBLISHED_QUOTE_FLAG, CURRENCY_CD_F, RESOURCE_FLAG, MINISITE_ID, TIME.ENT_YEAR_ID, ROLLUP(TIME.ENT_QTR_ID,TIME.ENT_PERIOD_ID,TIME.WEEK_ID,TIME.REPORT_DATE_JULIAN) )) WHERE RANK <= NVL(FND_PROFILE.VALUE('IBE_BI_TOP_ACT_NO_ROWS'),25)