CREATE VIEW [dbo].[pal_vAccountClaimHistoryLog]
AS

SELECT	DISTINCT ISNULL(C.claimLogId,0) claimLogId
		,L.requestDate
		,A.invoiceNo
		,ISNULL(R.recId,0) responseId
		,ISNULL(CASE R.responseLevelInd
			WHEN '01' THEN 'Patient record'
			WHEN '02' THEN 'Treatment record'
			WHEN '03' THEN 'Item record'
			END,'0') AS ResponseLevel
		,ISNULL(CAST(CASE R.responseResultCode
			WHEN '01' THEN 'Claim Accepted for delivery'
			WHEN '02' THEN 'Claim Accepted for processing'
			WHEN '03' THEN 'Claim Rejected'
			WHEN '04' THEN 'Claim Approved for Payment'
			WHEN '05' THEN 'Claim Approved for Part Payment  '
			WHEN '06' THEN 'Claim Reversal Accepted'
			WHEN '07' THEN 'Claim Reversal Rejected'
			WHEN '' THEN 'Variant Response'
			END AS VARCHAR(MAX)),L.response) AS ResponseResult
		,ISNULL(CASE R.respondingParty
			WHEN '01' THEN 'MediSwitch'
			WHEN '02' THEN 'Medical Scheme / Administrator'
			END,'N/A') AS RespondingParty
		,ISNULL(CASE R.msDeliveryTypeInd
			WHEN '01' THEN 'Real-Time'
			WHEN '02' THEN 'Batched'
			WHEN '03' THEN 'Queued'
			WHEN '04' THEN 'Rejected'
			END,'N/A') AS DeliveryType
--SELECT	L.*
FROM	pal_Account A
		JOIN pal_MSClaim C
			ON C.claimRefNo = A.invoiceNo
		JOIN pal_MSLog L
			ON L.recId = C.claimLogId
		LEFT JOIN pal_MSClaimResponse R
			ON R.claimLogId = L.recId
--WHERE	A.invoiceNo = 182

--SELECT	*
--FROM	pal_MSLog
--WHERE	recId = 203


GO


