eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
MATERIALIZED VIEW: APPS.ASO_BI_QOT_L1_MV

Source


SELECT  'NEW' Umarker, 
        FACT.Resource_grp_id Resource_Grp_Id, 
        FACT.Resource_Id, 
        FACT.Party_id, 
        SUM(FACT.QUOTE_VALUE * CONVERSION_RATE) Newqot_amnt, 
        SUM(FACT.QUOTE_VALUE * SEC_CONVERSION_RATE) sec_Newqot_amnt, 
        COUNT(*) Newqot_number, 
        SUM(FACT.QUOTE_VALUE * CONVERSION_RATE) Openqot_amnt, 
        SUM(FACT.QUOTE_VALUE * SEC_CONVERSION_RATE) sec_Openqot_amnt, 
        COUNT(*) Openqot_number, 
        NULL  Convqot_amnt, 
        NULL  sec_Convqot_amnt, 
        NULL  Convqot_number, 
        COUNT(*) All_cnt, 
        NULL Conv_days, 
        'NEWOPN' Row_Type, 
        NULL Conv_days_cnt, 
        COUNT(FACT.QUOTE_VALUE * CONVERSION_RATE) Newqot_amnt_cnt, 
        COUNT(FACT.QUOTE_VALUE * SEC_CONVERSION_RATE) sec_Newqot_amnt_cnt, 
        COUNT(FACT.QUOTE_VALUE * CONVERSION_RATE) Openqot_amnt_cnt, 
        COUNT(FACT.QUOTE_VALUE * SEC_CONVERSION_RATE) sec_Openqot_amnt_cnt, 
        COUNT(*) Openqot_number_cnt, 
        NULL  Convqot_amnt_cnt, 
        NULL  sec_Convqot_amnt_cnt, 
        TIME.Ent_year_id  Ent_year_id, 
        TIME.Ent_qtr_id Ent_qtr_id, 
        TIME.Ent_period_id  Ent_period_id, 
        TIME.Week_id  Week_id , 
        TIME.Report_date_julian Day_id, 
        GROUPING_ID(FACT.Resource_grp_id, FACT.Resource_id, FACT.Party_id, 
          TIME.Ent_year_id, TIME.Ent_qtr_id, TIME.Ent_period_id, 
          TIME.Week_id, TIME.Report_date_julian) grp_id 
FROM  ASO.ASO_BI_QUOTE_HDRS_ALL FACT, 
      FII.FII_TIME_DAY  TIME 
WHERE 
      FACT.Quote_creation_date = TIME.Report_date 
      AND FACT. RECURRING_CHARGE_FLAG  = 'N' 
GROUP BY  FACT.Resource_grp_id, FACT.Resource_id, FACT.Party_id, 
          TIME.Ent_year_id, TIME.Ent_qtr_id, TIME.Ent_period_id, 
          TIME.Week_id, TIME.Report_date_julian 
UNION ALL 
SELECT  'OPEN' umarker, 
        FACT.Resource_grp_id Resource_Grp_Id, 
        FACT.Resource_Id, 
        FACT.Party_id, 
        NULL  Newqot_amnt, 
        NULL  sec_Newqot_amnt, 
        NULL  Newqot_number, 
        SUM(FACT.QUOTE_VALUE * -1 * CONVERSION_RATE)  Openqot_amnt, 
        SUM(FACT.QUOTE_VALUE * -1 * SEC_CONVERSION_RATE)  sec_Openqot_amnt, 
        COUNT(*) Openqot_number, 
        NULL Convqot_amnt, 
        NULL sec_Convqot_amnt, 
        NULL Convqot_number, 
        COUNT(*) All_cnt, 
        NULL Conv_days, 
        'OPNNGN' Row_Type, 
        NULL Conv_days_cnt, 
        NULL  Newqot_amnt_cnt, 
        NULL  sec_Newqot_amnt_cnt, 
        COUNT(FACT.QUOTE_VALUE * -1 * CONVERSION_RATE) Openqot_amnt_cnt, 
        COUNT(FACT.QUOTE_VALUE * -1 * SEC_CONVERSION_RATE) sec_Openqot_amnt_cnt, 
        NULL  Openqot_number_cnt, 
        NULL Convqot_amnt_cnt, 
        NULL sec_Convqot_amnt_cnt, 
        TIME.Ent_year_id  Ent_year_id, 
        TIME.Ent_qtr_id Ent_qtr_id, 
        TIME.Ent_period_id  Ent_period_id, 
        TIME.Week_id  Week_id , 
        TIME.report_date_julian Day_id, 
        GROUPING_ID(FACT.Resource_grp_id, FACT.Resource_id, FACT.Party_id, 
          TIME.Ent_year_id, TIME.Ent_qtr_id, TIME.Ent_period_id, 
          TIME.Week_id, TIME.Report_date_julian) grp_id 
FROM 
      ASO.ASO_BI_QUOTE_HDRS_ALL FACT, 
      FII.FII_TIME_DAY  TIME 
WHERE 
      FACT.Quote_expiration_date = TIME.REPORT_DATE 
      AND   FACT.Order_id IS NULL 
      AND FACT.RECURRING_CHARGE_FLAG  = 'N' 
GROUP BY  FACT.Resource_grp_id, FACT.Resource_id, FACT.Party_id, 
          TIME.Ent_year_id, TIME.Ent_qtr_id, TIME.Ent_period_id, 
          TIME.Week_id, TIME.Report_date_julian 
UNION ALL 
SELECT 
        'CONV' umarker, 
        FACT.Resource_grp_id Resource_Grp_Id, 
        FACT.Resource_Id, 
        FACT.Party_id, 
        NULL  Newqot_amnt, 
        NULL  sec_Newqot_amnt, 
        NULL  Newqot_number, 
        SUM(FACT.QUOTE_VALUE * -1 * CONVERSION_RATE) Openqot_amnt, 
        SUM(FACT.QUOTE_VALUE * -1 * SEC_CONVERSION_RATE) sec_Openqot_amnt, 
        COUNT(*) Openqot_number, 
        SUM(FACT.QUOTE_VALUE * CONVERSION_RATE) Convqot_amnt, 
        SUM(FACT.QUOTE_VALUE * SEC_CONVERSION_RATE) sec_Convqot_amnt, 
        COUNT(*)  Convqot_number, 
        COUNT(*) All_cnt, 
        SUM(FACT.Order_creation_date - FACT.Quote_creation_date) conv_days, 
        'OPNNGN' Row_Type, 
        COUNT(FACT.Order_creation_date - FACT.Quote_creation_date) conv_days_cnt, 
        NULL  Newqot_amnt_cnt, 
        NULL  sec_Newqot_amnt_cnt, 
        COUNT(FACT.QUOTE_VALUE * -1 * CONVERSION_RATE) Openqot_amnt_cnt, 
        COUNT(FACT.QUOTE_VALUE * -1 * SEC_CONVERSION_RATE) sec_Openqot_amnt_cnt, 
        NULL  Openqot_number_cnt, 
        COUNT(FACT.QUOTE_VALUE * CONVERSION_RATE) Convqot_amnt_cnt, 
        COUNT(FACT.QUOTE_VALUE * SEC_CONVERSION_RATE) sec_Convqot_amnt_cnt, 
        TIME.Ent_year_id  Ent_year_id, 
        TIME.Ent_qtr_id Ent_qtr_id, 
        TIME.Ent_period_id  Ent_period_id, 
        TIME.Week_id  Week_id , 
        TIME.report_date_julian Day_id, 
        GROUPING_ID(FACT.Resource_grp_id, FACT.Resource_id, FACT.Party_id, 
          TIME.Ent_year_id, TIME.Ent_qtr_id, TIME.Ent_period_id, 
          TIME.Week_id, TIME.Report_date_julian) grp_id 
FROM 
      ASO.ASO_BI_QUOTE_HDRS_ALL FACT, 
      FII.FII_TIME_DAY  TIME 
WHERE 
      FACT.Order_creation_date = TIME.REPORT_DATE 
     AND FACT.RECURRING_CHARGE_FLAG  = 'N' 
GROUP BY  FACT.Resource_grp_id, FACT.Resource_id, FACT.Party_id, 
          TIME.Ent_year_id, TIME.Ent_qtr_id, TIME.Ent_period_id, 
          TIME.Week_id, TIME.Report_date_julian