MATERIALIZED VIEW: APPS.ASO_BI_TOP_QOTB_MV

Source

SELECT     qot.quote_name,
		       qot.quote_number,
		       qot.party_id,			   
   		       qot.quote_creation_date,    
		       qot.quote_expiration_date,
		       qot.order_creation_date,
			   qot.quote_age,			   
	           qot.resource_grp_id,        
		       qot.resource_id,
  		       qot.num_approvers,			   			              
		       qot.quote_amnt,
		       qot.quote_amount_first,             
		       qot.status,
		       qot.effective_date,
		       qot.conversion_rate,        
		       qot.sec_conversion_rate,
			   DECODE(qot.STATUS,'OPEN' , 1,
			             (CASE WHEN qot.effective_date BETWEEN fiiday.ent_year_start_date AND trunc(sysdate)
		                        THEN 1 ELSE 0 END)) year,
		       DECODE(qot.STATUS,'OPEN' , 1,
			   			 (CASE WHEN qot.effective_date BETWEEN fiiday.ent_qtr_start_date AND trunc(sysdate)
		                        THEN 1 ELSE 0 END)) quarter,
			   DECODE(qot.STATUS,'OPEN' , 1,
			   			 (CASE WHEN qot.effective_date BETWEEN fiiday.ent_period_start_date AND trunc(sysdate) 
		                        THEN 1 ELSE 0 END)) period,
		       DECODE(qot.STATUS,'OPEN' , 1,
			             (CASE WHEN qot.effective_date BETWEEN fiiday.week_start_date AND trunc(sysdate)
		                        THEN 1 ELSE 0 END)) week
		FROM(
		  SELECT		       
			   fact.quote_name,  
		       fact.quote_number,
		       fact.party_id ,
		       fact.Quote_creation_date,
		       (fact.Quote_expiration_date - 1) Quote_expiration_date,
		       fact.order_creation_date ,			   
		       (trunc(sysdate)- fact.Quote_creation_date) Quote_age ,
		       fact.resource_grp_id,
		       fact.resource_id ,		       
			   apr.num_approvers,
			   fact.Quote_value Quote_amnt ,
			   fact.quote_amount_first,			   
			   CASE WHEN fact.Quote_expiration_date > trunc(sysdate) AND 
			             (fact.order_creation_date is null or fact.order_creation_date  > trunc(sysdate)) AND 
						 fact.Quote_creation_date <= trunc(sysdate) THEN
						 'OPEN'			 
					WHEN fact.Order_creation_date is null THEN
						 'EXP' 
					WHEN fact.order_creation_date is not null THEN
					     'CONV'			
			        ELSE NULL
			   END status,
			   CASE WHEN fact.Quote_expiration_date > trunc(sysdate) AND 
			             (fact.order_creation_date is null or fact.order_creation_date  > trunc(sysdate)) AND 
						 fact.Quote_creation_date <= trunc(sysdate) THEN
						 trunc(sysdate)			 
					WHEN fact.Order_creation_date is null THEN
						 fact.quote_expiration_date
					WHEN fact.order_creation_date is not null THEN
					     fact.order_creation_date
			   END  effective_date,			   
		       fact.conversion_rate,
		       fact.sec_conversion_rate,
			  NULL
		FROM  aso_bi_quote_hdrs_all fact,
		      aso_bi_apr_f apr
		WHERE fact.quote_number = apr.quote_number(+)
    AND FACT.RECURRING_CHARGE_FLAG  = 'N' 
		) qot, 
		  fii_time_day fiiday
		WHERE fiiday.report_date = trunc(sysdate)
		AND qot.effective_date between LEAST(fiiday.week_start_date,fiiday.ent_year_start_date)
		AND GREATEST(fiiday.week_end_date,fiiday.ent_year_end_date)