MATERIALIZED VIEW: APPS.ASO_BI_TOP_QOT_MV

Source

SELECT  /* 12.0: bug#4526784 */ umarker, 
		quote_name, 
		quote_number,
		party_id,
		quote_creation_date, 
		quote_expiration_date, 
		order_creation_date, 
		quote_age,
		parent_group_id,
		resource_grp_id,
		resource_id,
		quote_amnt,
		sec_quote_amnt,
		quote_amount_first,
		sec_quote_amount_first,
		num_approvers,
		status, 
		effective_date, 
		year,
		quarter,
		period,
		week,
		year_rank,
		quarter_rank,
		period_rank,
		week_rank
	FROM (
	SELECT 'SLSGRP' umarker,	       
		   quote_name,
		   quote_number,
		   party_id,           
	       quote_creation_date,    
	       quote_expiration_date,
	       order_creation_date,
		   quote_age,    
		   parent_group_id,         
	       resource_grp_id,        
	       resource_id,
	       quote_amnt,
		   sec_quote_amnt,         
           quote_amount_first,
		   sec_quote_amount_first,         
   	       num_approvers,
	       status,        
	       effective_date,
		   year,  
	       quarter,
		   period,
		   week,
		   RANK() OVER(PARTITION BY parent_group_id,year,status ORDER BY quote_amnt DESC)
	       year_rank,
	       RANK() OVER(PARTITION BY parent_group_id, quarter, status ORDER BY quote_amnt DESC)
	       quarter_rank,
	       RANK() OVER(PARTITION BY parent_group_id , period, status ORDER BY quote_amnt DESC)
	       period_rank,
	       RANK() OVER(PARTITION BY parent_group_id , week,status ORDER BY quote_amnt DESC)
	       week_rank
	FROM 
	(		
		SELECT
		   jgd.parent_group_id,         
	       fact.resource_grp_id,        
	       fact.resource_id resource_id,
		   fact.quote_name,
		   fact.quote_number,           
		   fact.quote_amnt * fact.conversion_rate quote_amnt,
		   fact.quote_amnt * fact.sec_conversion_rate sec_quote_amnt,		                
	       fact.quote_amount_first * fact.conversion_rate quote_amount_first,
		   fact.quote_amount_first * fact.sec_conversion_rate sec_quote_amount_first,
	       fact.status,                 
	       fact.quote_creation_date,    
	       fact.quote_expiration_date,
	       fact.order_creation_date,
		   fact.quote_age,    
	       fact.party_id,               
	       fact.effective_date,
	       fact.conversion_rate,        
	       fact.sec_conversion_rate,
	       fact.num_approvers,
		   fact.year,  
	       fact.quarter,
		   fact.period,
		   fact.week
		FROM ASO_BI_TOP_QOTB_MV fact,
		     JTF.JTF_RS_GROUPS_DENORM jgd,
		     JTF.JTF_RS_GROUP_USAGES pjgu
		WHERE fact.resource_grp_id = jgd.group_id
		AND jgd.parent_group_id = pjgu.group_id
		AND jgd.latest_relationship_flag = 'Y'
		AND pjgu.usage = 'SALES'
	)
	UNION ALL
	SELECT 'SLSREP' umarker,
	       quote_name quote_name3,
		   quote_number,
		   party_id,           
	       quote_creation_date,    
	       quote_expiration_date,
	       order_creation_date,
		   quote_age,    
		   parent_group_id,         
	       resource_grp_id,        
	       resource_id,
	       quote_amnt,
		   sec_quote_amnt,         
           quote_amount_first,
		   sec_quote_amount_first,         
   	       num_approvers,
	       status,        
	       effective_date,
		   year,  
	       quarter,
		   period,
		   week,
		   RANK() OVER(PARTITION BY resource_grp_id,resource_id,year,status ORDER BY quote_amnt DESC)
	       year_rank,
	       RANK() OVER(PARTITION BY resource_grp_id,resource_id, quarter, status ORDER BY quote_amnt DESC)
	       quarter_rank,
	       RANK() OVER(PARTITION BY resource_grp_id,resource_id , period, status ORDER BY quote_amnt DESC)
               period_rank,
	       RANK() OVER(PARTITION BY resource_grp_id,resource_id , week,status ORDER BY quote_amnt DESC)
	       week_rank
	FROM 
	(	
	    SELECT
   		   fact.resource_grp_id parent_group_id,         
	       fact.resource_grp_id,        
	       fact.resource_id,
		   fact.quote_name,  
		   fact.quote_number,           
	       (fact.quote_amnt * fact.conversion_rate) quote_amnt,
		   (fact.quote_amnt * fact.sec_conversion_rate) sec_quote_amnt,             
	       (fact.quote_amount_first * fact.conversion_rate) quote_amount_first,
		   (fact.quote_amount_first * fact.sec_conversion_rate) sec_quote_amount_first,             
	       fact.status,                 
	       fact.quote_creation_date,    
	       fact.quote_expiration_date,
	       fact.order_creation_date,
		   fact.quote_age,    
	       fact.party_id,               
	       fact.effective_date,
	       fact.conversion_rate,        
	       fact.sec_conversion_rate,    
	       fact.num_approvers,
		   fact.year,  
	       fact.quarter,
		   fact.period,
		   fact.week
		FROM ASO_BI_TOP_QOTB_MV fact
	)             	    	    
	)
	WHERE  (year = 1 and year_rank < 26)
	   OR (quarter = 1 and quarter_rank < 26)
	   OR (period = 1 and period_rank < 26)
	   OR (week = 1 and week_rank < 26)