SELECT 
	id, 
	title, 
	TO_CHAR(a.publish_date, 'MM-DD-YYYY'), publish_date,
	file_size, 
	format,
	format_id,
	access_level, 
	access_level_id
	
FROM 
( 
  SELECT 
	ROWNUM r,
	id, 
	title, 
	TO_CHAR(a.publish_date, 'MM-DD-YYYY'),
	file_size, 
	format,
	format_id,
	access_level, 
	access_level_id
	
	
   FROM

	( 
	SELECT DISTINCT
    	
    	a.id, 
	a.title, 
	TO_CHAR(a.publish_date, 'MM-DD-YYYY'),
	a.file_size, 
 	tr.refvalue format, 
 	a.format AS format_id,
	tr2.refvalue  access_level ,
	a.access_level AS access_level_id
	
	FROM 
    
	SP_ARTICLE a,
	T_CONTENT_LOCATION_MAP clm,
	T_LOCATION_LEVEL_MAP llm,
	T_LOCALE l,
	T_REFERENCE tr,
	T_REFERENCE tr2
    
    
   	WHERE a.id = clm.content_id
        AND clm.location_id  = llm.id
        AND llm.level1_id  = ( SELECT level1_id FROM T_LOCATION_LEVEL_MAP WHERE id = 75)
        

        AND llm.level5_id  = ( SELECT level5_id FROM T_LOCATION_LEVEL_MAP WHERE id = 75)                
        
        AND clm.locale_id = l.id
	    AND l.region_id = ( SELECT id FROM T_REGION WHERE regioncode_short = 'US' )
	    AND l.language_id = ( SELECT id FROM T_LANGUAGE WHERE languagecode = 'EN' )
        

	AND a.status!='VO' 
	AND (a.publish_date <= SYSDATE) 
	AND 	(
			CASE WHEN a.use_advanced_rules = 'Y'
			THEN a.headline_expiration_date 
			ELSE a.expiration_date
			END IS NULL
			OR 
			CASE
			WHEN a.use_advanced_rules = 'Y'
			THEN a.headline_expiration_date
   			ELSE a.expiration_date
			END > SYSDATE
		)	

	AND a.format=tr.id 
	AND tr.refcategory='FORMAT'
	
	AND tr2.refnum = a.access_level 
	AND tr2.refcategory = 'ACCESSLEVEL'	

	
	)

)