select 'restime' Umarker, MINISITE_ID, (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, TIME.ent_year_id,TIME.ent_qtr_id,TIME.ent_period_id, TIME.report_date_julian, TIME.week_id, GROUPING_ID (ORD.MINISITE_ID,TIME.ent_year_id,TIME.ent_qtr_id,TIME.ent_period_id, TIME.week_id,TIME.report_date_julian) D_gid, Count(ORD.CUSTOMER_ID) Total, Count(*) D_Total_Cnt from APPS.IBE_BI_ORDERS_MV ORD, APPS.IBE_BI_CUST_MV NEW_ORD, FII.FII_TIME_DAY TIME WHERE ORD.CUSTOMER_ID = NEW_ORD.CUSTOMER_ID AND ORD.BOOKED_DATE = NEW_ORD.BOOKED_DATE AND ORD.TIME_BOOKED_DATE_ID = Time.report_date and ord.published_quote_flag = 'N' GROUP BY ORD.MINISITE_ID, TIME.ent_year_id, ROLLUP(TIME.ent_qtr_id,TIME.ent_period_id,TIME.week_id,TIME.report_date_julian)
[Home] [Help]