CREATE VIEW [dbo].[pal_vAccountClaimHistory]
AS

SELECT  A.surfaceItemId, A.invoiceNo, A.dateOfService, A.patientName, A.patientSurname, A.icd1, R.recId AS responseId, R.claimLogId, L.swref, 
        CASE R.responseLevelInd WHEN '01' THEN 'Patient record' WHEN '02' THEN 'Treatment record' WHEN '03' THEN 'Item record' END AS ResponseLevel, 
        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 ResponseResult, 
        CASE R.respondingParty WHEN '01' THEN 'MediSwitch' WHEN '02' THEN 'Medical Scheme / Administrator' END AS RespondingParty, 
        CASE R.msDeliveryTypeInd WHEN '01' THEN 'Real-Time' WHEN '02' THEN 'Batched' WHEN '03' THEN 'Queued' WHEN '04' THEN 'Rejected' END AS DeliveryType, 
        CAST(CASE WHEN M.msReversalAllowed = 1 AND R.respondingParty = '02' AND R.responseResultCode NOT IN ('06', '07') THEN 1 ELSE 0 END AS BIT) AS allowReversal, 
        CAST(CASE WHEN R.msDeliveryTypeInd = '03' THEN 1 ELSE 0 END AS BIT) AS allowForceFetch, CAST(CASE WHEN R.respondingParty = '01' AND R.responseResultCode = '03' THEN 1 ELSE 0 END AS BIT) 
        AS allowResubmit, A.externalCause
FROM    dbo.pal_Account AS A INNER JOIN
        dbo.pal_MSClaimResponse AS R ON R.claimRefNo = A.invoiceNo INNER JOIN
        dbo.pal_MSLog AS L ON L.recId = R.claimLogId INNER JOIN
        dbo.pal_MedicalScheme AS S ON S.code = A.medCode INNER JOIN
        dbo.pal_MedicalAdministrator AS M ON M.msDestinationCode = S.msDestinationCode
WHERE   (A.claimSend = 1) AND (R.recId =
        (SELECT        MAX(recId) AS Expr1
        FROM            dbo.pal_MSClaimResponse AS C
        WHERE        (claimRefNo = A.invoiceNo)))
GROUP BY A.surfaceItemId, A.invoiceNo, A.dateOfService, A.patientName, A.patientSurname, A.icd1, R.recId, R.claimLogId, L.swref, 
        CASE R.responseLevelInd WHEN '01' THEN 'Patient record' WHEN '02' THEN 'Treatment record' WHEN '03' THEN 'Item record' END, 
        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, 
        CASE R.respondingParty WHEN '01' THEN 'MediSwitch' WHEN '02' THEN 'Medical Scheme / Administrator' END, 
        CASE R.msDeliveryTypeInd WHEN '01' THEN 'Real-Time' WHEN '02' THEN 'Batched' WHEN '03' THEN 'Queued' WHEN '04' THEN 'Rejected' END, CAST(CASE WHEN M.msReversalAllowed = 1 AND 
        R.respondingParty = '02' AND R.responseResultCode NOT IN ('06', '07') THEN 1 ELSE 0 END AS BIT), CAST(CASE WHEN R.msDeliveryTypeInd = '03' THEN 1 ELSE 0 END AS BIT), 
        CAST(CASE WHEN R.respondingParty = '01' AND R.responseResultCode = '03' THEN 1 ELSE 0 END AS BIT), A.externalCause

GO


