alter VIEW [dbo].[V_APM_PATCH_MAIN]
	AS 
SELECT
		P.id,
		2 [class_type],
		P.guid,
		B.bulletin_id,
		CONCAT(B.summary,' ',P.informational_comment) summary,
		P.kb_number sqnumber,
		P.name,
		B.bulletin_page [bulletin_url],
		P.cve_ids,
		P.severity_id,
		CONCAT(B.title, ' ', L.short_name) bulletin_title,
		P.date_released posted_date,
		CASE WHEN  P.is_deployable=1 and P.is_downloadable=1 THEN 1 ELSE 0 END deployable,
		SCP.is_installed,
		P.patch_type_id,
		DD.id download_id,
		DD.download_status_id,
		DD.download_date,
		DD.scheduled_date,
		DD.url,
		DD.size,
		L.id language_id,
		DD.language_id AS downloadLanguageId
	FROM (
		SELECT DISTINCT
			patch_id,
			CASE WHEN SUM(is_installed) = COUNT(*) THEN 1 ELSE 0 END AS is_installed,--CASE WHEN SUM(is_installed) > 0 THEN 1 ELSE 0 END AS is_installed
			l.id lang
		FROM APM_SCANNED_PATCHES scp
		INNER JOIN APM_REGION R ON scp.region_id = R.id
		INNER JOIN APM_LANGUAGE L ON L.id = R.language_id
		GROUP BY patch_id, l.id
	) SCP
	INNER JOIN APM_PATCH_DETAIL P ON SCP.patch_id = P.id
	INNER JOIN APM_BULLETIN_DETAIL B ON B.id = P.bulletin_id
	INNER JOIN APM_LANGUAGE L ON L.id = SCP.lang
	INNER JOIN APM_DOWNLOAD_DETAIL DD ON DD.patch_detail_id = P.id
	AND (DD.language_id = L.id 
	AND EXISTS(SELECT 1 FROM APM_DOWNLOAD_DETAIL dd1 WHERE dd1.patch_detail_id=dd.patch_detail_id AND dd1.language_id=l.id) 
	OR NOT EXISTS(SELECT 1 FROM APM_DOWNLOAD_DETAIL dd1 WHERE dd1.patch_detail_id=dd.patch_detail_id AND dd1.language_id=l.id) 
	AND dd.language_id =(SELECT id FROM APM_LANGUAGE WHERE language_id=0))
UNION ALL
SELECT
	SP.id,
	1 [class_type],
	SP.guid,
	SP.name bulletin_id,
	NULL summary,
	SP.name sqnumber,
	NULL name,
	NULL [bulletin_url],
	NULL cve_ids,
	2 severity_id,
	CONCAT(SP.name,' ',L.short_name,' (', DD.shavlik_file_name, ')') bulletin_title,
	SP.created_date posted_date,
	SP.is_deployable deployable,
	0 is_installed,
	0 [patch_type_id], 
	DD.id download_id,
	DD.download_status_id,
	DD.download_date,
	DD.scheduled_date,
	DD.url,
	DD.size,
	L.id language_id,
	DD.language_id
FROM (
		SELECT DISTINCT
			service_pack_id,
			l.id lang
		FROM APM_APPLICABLE_SERV_PACK aps
		INNER JOIN APM_REGION R ON aps.region_id = R.id
		INNER JOIN APM_LANGUAGE L ON L.id = R.language_id
	) APS
	INNER JOIN APM_SERVICE_PACK_DETAIL SP ON SP.id = APS.service_pack_id
	INNER JOIN APM_LANGUAGE L ON APS.lang = L.id
	INNER JOIN APM_DOWNLOAD_DETAIL DD ON DD.service_pack_id = SP.id 
	AND (DD.language_id = L.id 
	AND EXISTS(SELECT 1 FROM APM_DOWNLOAD_DETAIL dd1 WHERE dd1.service_pack_id=dd.service_pack_id AND dd1.language_id=l.id) 
	OR NOT EXISTS(SELECT 1 FROM APM_DOWNLOAD_DETAIL dd2 WHERE dd2.service_pack_id=dd.service_pack_id AND dd2.language_id=l.id)
	AND dd.language_id =(SELECT id FROM APM_LANGUAGE WHERE language_id=0))
GO