--WARNING! ERRORS ENCOUNTERED DURING SQL PARSING!
SELECT prds_prod_ver_id AS prod_id
	,prds_prod_id AS pk_prod_id
	,INITCAP(prod_status) AS status_flag
	,prds_prod_code
	,coalesce(display_title, title) AS display_title
	,CASE
		WHEN (prod_status = 'ACTIVE')
			THEN '2-' || TO_CHAR(cover_date, 'yyyymmdd')
		ELSE '1-' || TO_CHAR(cover_date, 'yyyymmdd')
		END AS default_sort_order
	,CASE
		WHEN (prod_status = 'ACTIVE')
			THEN '2-' || title
		ELSE '1-' || title
		END AS title_sort_order
	,author_code AS type_of_author
	,distributable_flag AS distributable_flag
	,prod_status AS product_status
	,prds_prod_code AS product_number
	,title AS title
	,cover_date AS cover_date
	,prds_prod_type_code AS product_type
	,display_name AS product_type_group
	,summary
	,html.url AS display_url
	,summ.url AS summary_url
	,pdf.url AS pdf_url
	,subject AS product_keywords
	,TO_CHAR(cover_date, 'yyyymmdd') AS cover_date_int
	,TO_CHAR(v.verified_date_tm, 'YYYYMMDDHH24MISS') AS verified_date
	,TO_CHAR(proc_comp_date_tm, 'YYYYMMDDHH24MISS') AS proc_comp_date
	,translate(UPPER(prds_prod_code), '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ-', '0123456789') AS NUM_PROD_CODE
	,prds_action_type_code AS action_type_code
	,CASE
		WHEN to_date(to_char(sysdate, 'mm/dd/yyyy'), 'mm/dd/yyyy') - cover_date >= 0
			AND to_date(to_char(sysdate, 'mm/dd/yyyy'), 'mm/dd/yyyy') - cover_date <= 7
			THEN '0-Past Week|1-Past Month|2-Past Year'
		WHEN to_date(to_char(sysdate, 'mm/dd/yyyy'), 'mm/dd/yyyy') - cover_date >= 0
			AND to_date(to_char(sysdate, 'mm/dd/yyyy'), 'mm/dd/yyyy') - cover_date <= 30
			THEN '1-Past Month|2-Past Year'
		WHEN to_date(to_char(sysdate, 'mm/dd/yyyy'), 'mm/dd/yyyy') - cover_date >= 0
			AND to_date(to_char(sysdate, 'mm/dd/yyyy'), 'mm/dd/yyyy') - cover_date <= 365
			THEN '2-Past Year'
		END AS cover_date_range_list
	,prds_prod_type_group_code AS prod_type_group_code
	,display_name AS prod_type_group
	,icon_name AS ptg_icon_name
	,sub_desc AS ptg_sub_desc
	,prod_type_display_name
	,rc.report_content AS report_content
	,no_of_pages
FROM prds_prod_cur_vw p
LEFT OUTER JOIN PRDS_PROD_TYPE_GROUP USING (PRDS_PROD_TYPE_GROUP_CODE)
LEFT OUTER JOIN (
	SELECT prds_prod_id
		,url
	FROM prds_prod_url_vw
	WHERE prds_format_type_code IN ('SUMM')
	) summ USING (prds_prod_id)
LEFT OUTER JOIN (
	SELECT prds_prod_id
		,url
	FROM prds_prod_url_vw
	WHERE prds_format_type_code IN (
			'HTML'
			,'SHTML'
			,'HTM'
			,'ASPX'
			)
	) html USING (prds_prod_id)
LEFT OUTER JOIN (
	SELECT prds_prod_id
		,url
	FROM prds_prod_url_vw
	WHERE prds_format_type_code IN ('PDF')
	) pdf USING (prds_prod_id)
LEFT OUTER JOIN (
	SELECT prds_prod_id
		,verified_date_tm
	FROM prds_prod_ver
	WHERE seq_number = 1
	) v USING (prds_prod_id)
LEFT OUTER JOIN (
	SELECT prds_prod_ver_id
		,report_content
	FROM prds_prod_format f
		,prds_prod_type_format_type ptft
	WHERE f.prds_prod_type_format_type_id = PTFT.PRDS_PROD_TYPE_FORMAT_TYPE_ID
		AND (
			upper(PTFT.PRDS_FORMAT_TYPE_CODE) = 'WORDML'
			OR upper(PTFT.PRDS_FORMAT_TYPE_CODE) = 'OPC'
			)
	) rc USING (prds_prod_ver_id)
WHERE p.distributable_flag = 'Y'
	AND prds_prod_type_code NOT IN (
		'IS'
		,'AST'
		)
ORDER BY prds_prod_ver_id