MATERIALIZED VIEW: APPS.ASO_BI_QOT_DISCB_MV

Source

SELECT
        'NEW' marker,
        'NEWOPN' Row_Type,
        BUK.Low,
        FACT.Resource_grp_id Resource_Grp_Id,
        FACT.Resource_id,
        SUM(FACT.Quote_value*FACT.Conversion_rate) New_Qot_Val,
        SUM(FACT.Quote_value*FACT.sec_Conversion_rate) sec_New_Qot_Val,
        COUNT(*) New_Qot_Cnt,
        SUM(FACT.Quote_value*FACT.Conversion_rate) Opn_Qot_Val,
        SUM(FACT.Quote_value*FACT.sec_Conversion_rate) sec_Opn_Qot_Val,
        COUNT(*) Opn_Qot_Cnt,
        NULL Conv_Qot_Val,
        NULL sec_Conv_Qot_Val,
        NULL Conv_Qot_Cnt,
        COUNT(FACT.Quote_value*FACT.Conversion_rate) New_Qot_Val_Cnt,
        COUNT(FACT.Quote_value*FACT.sec_Conversion_rate) sec_New_Qot_Val_Cnt,
        COUNT(FACT.Quote_value*FACT.Conversion_rate) Opn_Qot_Val_Cnt,
        COUNT(FACT.Quote_value*FACT.sec_Conversion_rate) sec_Opn_Qot_Val_Cnt,
        COUNT(*) Cnt_All,
        Ent_year_id,
        Ent_qtr_id,
        Ent_period_id,
        Week_id,
        Report_date_julian day_id,
        GROUPING_ID(BUK.Low,
          FACT.Resource_Grp_Id,
          FACT.Resource_Id,
          Ent_year_id,
          Ent_qtr_id,
          Ent_period_id,
          Week_Id,
          Report_Date_Julian) Grp_Id_Cnt
FROM
        ASO.ASO_BI_QUOTE_HDRS_ALL FACT,
        ASO_BI_DISC_BUK_MV BUK,
        FII.FII_TIME_DAY TIME
WHERE
        FACT.Total_adjusted_percent IS NOT NULL
        AND BUK.Short_name = 'ASO_DISCOUNT_PERCENT_BUK'
        AND FACT.Total_adjusted_percent >= BUK.Low 
        AND FACT.Total_adjusted_percent < BUK.High
        AND FACT.Quote_creation_date = TIME.Report_date
        AND FACT.RECURRING_CHARGE_FLAG  = 'N'
GROUP BY 
        BUK.Low,
        FACT.Resource_Grp_Id,
        FACT.Resource_Id,
        Ent_year_id,
        Ent_qtr_id,
        Ent_period_id,
        Week_Id,
        Report_Date_Julian
UNION ALL
SELECT
        'OPEN' marker,
        'OPNNGN' Row_Type,
        BUK.Low,
        FACT.Resource_Grp_Id Resource_Grp_Id,
        FACT.Resource_Id,
        NULL New_Qot_Val,
        NULL sec_New_Qot_Val,
        NULL New_Qot_Cnt,
        SUM(FACT.Quote_Value*FACT.Conversion_Rate*-1) Opn_Qot_Val,
        SUM(FACT.Quote_Value*FACT.sec_Conversion_Rate*-1) sec_Opn_Qot_Val,
        COUNT(*) opn_qot_cnt,
        NULL Conv_qot_val,
        NULL sec_Conv_qot_val,
        NULL Conv_qot_cnt,
        NULL New_Qot_Val_Cnt,
        NULL sec_New_Qot_Val_Cnt,
        COUNT(FACT.Quote_Value*FACT.Conversion_Rate*-1) Opn_Qot_Val_Cnt,
        COUNT(FACT.Quote_Value*FACT.sec_Conversion_Rate*-1) sec_Opn_Qot_Val_Cnt,
        COUNT(*) Cnt_All,
        Ent_year_id,
        Ent_qtr_id,
        Ent_period_id,
        Week_id,
        Report_date_julian day_id,
        GROUPING_ID(BUK.Low,
          FACT.Resource_Grp_Id,
          FACT.Resource_Id,
          Ent_year_id,
          Ent_qtr_id,
          Ent_period_id,
          Week_Id,
          Report_Date_Julian) Grp_Id_Cnt
FROM
        ASO.ASO_BI_QUOTE_HDRS_ALL FACT,
        ASO_BI_DISC_BUK_MV BUK,
        FII.FII_TIME_DAY TIME
WHERE
        FACT.Order_id IS NULL
        AND BUK.short_name = 'ASO_DISCOUNT_PERCENT_BUK'
        AND FACT.total_adjusted_percent IS NOT NULL
        AND FACT.Total_adjusted_percent >= BUK.Low 
        AND FACT.Total_adjusted_percent < BUK.High
        AND FACT.quote_expiration_date = TIME.Report_date
        AND FACT.RECURRING_CHARGE_FLAG  = 'N'
GROUP BY 
        BUK.Low,
        FACT.Resource_Grp_Id,
        FACT.Resource_Id,
        Ent_year_id,
        Ent_qtr_id,
        Ent_period_id,
        Week_Id,
        Report_Date_Julian
        
UNION ALL
SELECT
        'CONV' marker,
        'OPNNGN' Row_Type,        
        BUK.Low,
        FACT.Resource_Grp_Id Resource_Grp_Id,
        FACT.Resource_Id,
        NULL New_Qot_Val,
        NULL sec_New_Qot_Val,
        NULL New_Qot_Cnt,
        SUM(FACT.Quote_Value*FACT.Conversion_Rate*-1) Opn_Qot_Val,
        SUM(FACT.Quote_Value*FACT.sec_Conversion_Rate*-1) sec_Opn_Qot_Val,
        COUNT(*) Opn_Qot_Cnt,
        SUM(FACT.Quote_Value*FACT.Conversion_Rate) Conv_Qot_Val,
        SUM(FACT.Quote_Value*FACT.sec_Conversion_Rate) sec_Conv_Qot_Val,
        COUNT(*) Conv_Qot_Cnt,
        COUNT(FACT.Quote_Value*FACT.Conversion_Rate) New_Qot_Val_Cnt,
        COUNT(FACT.Quote_Value*FACT.sec_Conversion_Rate) sec_New_Qot_Val_Cnt,
        COUNT(FACT.Quote_Value*FACT.Conversion_Rate*-1) Opn_Qot_Val_Cnt,
        COUNT(FACT.Quote_Value*FACT.sec_Conversion_Rate*-1) sec_Opn_Qot_Val_Cnt,
        COUNT(*) Cnt_All,        
        Ent_year_id,
        Ent_qtr_id,
        Ent_period_id,
        Week_Id,
        Report_Date_Julian day_id,
        GROUPING_ID(BUK.Low,
          FACT.Resource_Grp_Id,
          FACT.Resource_Id,
          Ent_year_id,
          Ent_qtr_id,
          Ent_period_id,
          Week_Id,
          Report_Date_Julian) Grp_Id_Cnt
FROM
        ASO.ASO_BI_QUOTE_HDRS_ALL FACT,
        ASO_BI_DISC_BUK_MV BUK,
        FII.FII_TIME_DAY TIME
WHERE
        FACT.Total_adjusted_percent IS NOT NULL
        AND FACT.Order_id IS NOT NULL
        AND BUK.Short_name = 'ASO_DISCOUNT_PERCENT_BUK'
        AND FACT.Total_adjusted_percent >= BUK.Low 
        AND FACT.Total_adjusted_percent < BUK.High
        AND FACT.Order_creation_date = TIME.Report_date
        AND FACT.RECURRING_CHARGE_FLAG  = 'N'
GROUP BY 
        BUK.Low,
        FACT.Resource_Grp_Id,
        FACT.Resource_Id,
        Ent_year_id,
        Ent_qtr_id,
        Ent_period_id,
        Week_Id,
        Report_Date_Julian