CREATE VIEW [dbo].[pal_vAccountClaimResponseErrors]
AS

SELECT	T.recId 
		,R.claimLogId
		,'Error' AS msgType
		,L.Code+ ' - '+L.Description AS comment
FROM	pal_MSClaimResponse R
		JOIN pal_MSClaimResponseTreatment T
			ON T.claimResponseId = R.recId
		LEFT JOIN pal_MSClaimResponseLine L
			ON L.responseTreatmentId = T.recId
WHERE	L.Code IS NOT NULL

UNION 
SELECT	T.recId 
		,R.claimLogId
		,'Comment' AS msgType
		,C.comment
FROM	pal_MSClaimResponse R
		JOIN pal_MSClaimResponseTreatment T
			ON T.claimResponseId = R.recId
		LEFT JOIN pal_MSClaimResponseComment C
			ON C.responseTreatmentId = T.recId
WHERE	C.comment IS NOT NULL


GO


