CREATE MATERIALIZED VIEW EDW.MV_OPPTY_ACTIVITY_AGG 
TABLESPACE EDW_DIM_DAT
NOCACHE
LOGGING
NOCOMPRESS
NOPARALLEL
BUILD IMMEDIATE
REFRESH COMPLETE ON DEMAND
WITH PRIMARY KEY
AS 
select 
    OPPTY_ID OPPTY_ID,
    OPPORTUNITY_KEY OPPORTUNITY_KEY,
    oppty_number oppty_number , 
    SALES_CONSULTANT_HIST_KEY SALES_CONSULTANT_HIST_KEY  ,
    company_add_source_key  company_add_source_key, 
    SALES_CONSULTANT_ID SALES_CONSULTANT_ID,
    ACTIVITY_TYPE ACTIVITY_TYPE  ,
    activity_DATE activity_DATE ,
    LAST_MODIFIED_DATE LAST_MODIFIED_DATE ,
    CUSTOMER_FLAG   CUSTOMER_FLAG,
    SALES_EMPLOYEE_HIST_KEY,
    milestone_KEY,
    oppty_milestone_name_hist stage_name,
    oppty_milestone_id_hist stage_id, 
    milestone_prob prob_to_close,
    start_date oppty_start_date,
    oppty_close_date oppty_close_date,
    end_date oppty_end_date,
   
    sum(DURATION) TOTAL_DURATION,
--
   sum (CASE WHEN ACTIVITY_type IN ('CU14', 'CU15', 'CU16', 'CU17', 

                      'CU18', 'CU19', 'CU20', 'CU21') THEN DURATION ELSE 0 END ) CQM_DURATION, 

   sum  (CASE WHEN ACTIVITY_type = 'CU01' THEN DURATION ELSE 0 END ) TRADE_SEM_DURATION, 

   sum( CASE WHEN ACTIVITY_type = 'CU02' THEN DURATION ELSE 0 END ) RFP_RFI_DURATION, 

   sum(  CASE WHEN ACTIVITY_type = 'CU03' THEN DURATION ELSE 0 END ) INFO_GATH_DURATION, 

   sum( CASE WHEN ACTIVITY_type = 'CU04' THEN DURATION ELSE 0 END ) PREP_DURATION, 

   sum(  CASE WHEN ACTIVITY_type = 'CU05' THEN DURATION ELSE 0 END ) PRES_DEMO_DURATION, 

   sum(  CASE WHEN ACTIVITY_type = 'CU06' THEN DURATION ELSE 0 END ) POC_DURATION, 

   sum ( CASE WHEN ACTIVITY_type = 'CU07' THEN DURATION ELSE 0 END ) PRE_CUST_SUPP_DURATION, 

   sum(  CASE WHEN ACTIVITY_type = 'CU08' THEN DURATION ELSE 0 END ) POST_CUST_SUPP_DURATION, 

   sum(  CASE WHEN ACTIVITY_type = 'CU09' THEN DURATION ELSE 0 END ) PARTNER_SUPP_DURATION, 

   sum(  CASE WHEN ACTIVITY_type = 'CU10' THEN DURATION ELSE 0 END ) TRAVEL_DURATION, 

   sum(  CASE WHEN ACTIVITY_type = 'CU11' THEN DURATION ELSE 0 END ) KEYCODES_DURATION, 

   sum(  CASE WHEN ACTIVITY_type = 'CU12' THEN DURATION ELSE 0 END ) SI_SUPP_DURATION, 

   sum ( CASE WHEN ACTIVITY_type = 'CU13' THEN DURATION ELSE 0 END ) CHANNEL_SUPP_DURATION,

   sum(  CASE WHEN ACTIVITY_type = 'CU23' THEN DURATION ELSE 0 END ) MIGRATION_DURATION ,   


--    
    max(LICENSE_USD) LICENSE_USD ,
            MAX(LICENSE_LOCAL) LICENSE_LOCAL,
            MAX(maintAINANCE_usd) maintAINANCE_usd,
            MAX(maintAINANCE_LOCAL) maintAINANCE_LOCAL,
            MAX(TRAINING_USD) training_usd,
            MAX(TRAINING_LOCAL) training_LOCAL,
            MAX(CONSULTING_USD) consulting_usd,
            MAX(CONSULTING_LOCAL) consulting_LOCAL,
            MAX(RENEWALS_USD) renewals_usd ,
            MAX(RENEWAL_local) RENEWAL_local,
            max(LOBANALYTICS2_LOCAL) epm_local,   
            max(LOBDATAINTEGRATION2_LOCAL) eim_local,
            max(IDD_AMOUNT_LOCAL) idd_local,
            max(ONDEMAND_AMOUNT_LOCAL) ondemand_local,
            max(LOBANALYTICS2_usd) epm_usd,   
            max(LOBDATAINTEGRATION2_usd) eim_usd,
            max(IDD_AMOUNT_usd) idd_usd,
            max(ONDEMAND_AMOUNT_usd) ondemand_usd
  
FROM edw.sales_consulting_activity_fct t1 ,
     (
    select 
    o.oppty_id_number,
             o.oppty_id,
             O.OPPORTUNITY_KEY,

            LICENSE_USD LICENSE_USD ,
            LICENSE_LOCAL LICENSE_LOCAL,
            o.FYM_USD maintAINANCE_usd,
            o.FYM_LOCAL maintAINANCE_LOCAL,
            o.TRAINING_USD training_usd,
            o.TRAINING_LOCAL training_LOCAL,
            o.CONSULTING_USD consulting_usd,
            o.CONSULTING_LOCAL consulting_LOCAL,
            o.RENEWAL_USD renewals_usd ,
            o.RENEWAL_local RENEWAL_local  ,
            o.LOBANALYTICS2_LOCAL,            
            o.LOBANALYTICS2_usd,
            o.LOBDATAINTEGRATION2_LOCAL,
            o.IDD_AMOUNT_LOCAL,
            o.ONDEMAND_AMOUNT_LOCAL,
            o.LOBDATAINTEGRATION2_usd,
            o.IDD_AMOUNT_usd,
            o.ONDEMAND_AMOUNT_usd


            from edw.opportunity_hist_fact o 
            where  o.SOURCE_SYSTEM='SFDC' 
            and upper(status)='ACTIVE'
            and o.last_modified_date = (select max(last_modified_date )from edw.opportunity_hist_fact o2 where o.opportunity_key = o2.opportunity_key))T2,
             ( Select activity_key,OPPTY_ID_NUMBER, MIL.milestone_KEY,MIL.milestone_name oppty_milestone_name_hist,milestone_id 
                oppty_milestone_id_hist, milestone_prob,start_date,oppty_close_date,end_date FROM
                EDW.opportunity_hist_fact OPP,
                edw.milestone MIL,
                edw.sales_consulting_activity_fct act WHERE 
                ACTIVITY_DATE  >=start_date (+) and activity_date  <=opp.last_modified_date(+)  
                and OPP.OPPTY_ID_NUMBER (+) =act.OPPTY_NUMBER   
                and OPP.milestone_key =MIL.milestone_key(+) ) T3
             where t1.oppty_NUMBER (+) =T2.OPPTY_ID_NUMBER 
             and   t1.activity_key=t3.activity_key
             --and oppty_number ='279749'
GROUP BY   OPPTY_ID,
     oppty_number,
     OPPORTUNITY_KEY,
     SALES_CONSULTANT_HIST_KEY  ,
     company_add_source_key,
     SALES_CONSULTANT_ID,
     SALES_EMPLOYEE_HIST_KEY,
     ACTIVITY_TYPE  ,
     ACTIVITY_DATE ,
     LAST_MODIFIED_DATE,
     CUSTOMER_FLAG,
     milestone_KEY,
    oppty_milestone_name_hist,
    oppty_milestone_id_hist, 
    milestone_prob,
    start_date,
    oppty_close_date,
    end_date;

select 
    OPPTY_ID OPPTY_ID,
    milestone_KEY,
    oppty_milestone_name_hist stage_name,
    oppty_milestone_id_hist stage_id, 
    milestone_prob prob_to_close,
    start_date oppty_start_date,
    oppty_close_date oppty_close_date,
    end_date oppty_end_date,
    OPPORTUNITY_KEY OPPORTUNITY_KEY,
    oppty_number oppty_number , 
    SALES_CONSULTANT_HIST_KEY SALES_CONSULTANT_HIST_KEY  ,
    company_add_source_key  company_add_source_key, 
    SALES_CONSULTANT_ID SALES_CONSULTANT_ID,
    ACTIVITY_TYPE ACTIVITY_TYPE  ,
    activity_DATE activity_DATE ,
    LAST_MODIFIED_DATE LAST_MODIFIED_DATE ,
    CUSTOMER_FLAG   CUSTOMER_FLAG,
    SALES_EMPLOYEE_HIST_KEY,