CREATE VIEW [dbo].[pal_vAccountUnprocessedEra]
AS
SELECT        l.recId, l.originalProcessDate AS Date, 
CASE i.claimResponseResultCode WHEN '03' THEN 'Claim Rejected' WHEN '08' THEN 'Claim Adjusted' WHEN '09' THEN 'Claim Paid in Full' WHEN '10' THEN 'Claim Part Paid' ELSE '' END AS EraStatus,
 m.patientFullName AS PatientName, m.patientSurname, m.patientDOB, m.patientIdNumber, e.medicalSchemeName, i.itemClaimAmount AS ClaimAmount, 
                         i.totalItemPaidAmount AS PaidAmount
FROM            dbo.pal_MSEraClaimItemAccount AS l LEFT OUTER JOIN
                         dbo.pal_MSEraClaimItem AS i ON i.recId = l.eraClaimItemId LEFT OUTER JOIN
                         dbo.pal_MSEraMember AS m ON m.recId = i.eraMemberId LEFT OUTER JOIN
                         dbo.pal_MSEra AS e ON e.recId = m.eraId
WHERE        (l.accountId = 0) AND ISNULL(l.rejected, 0) = 0 AND ISNULL(l.deleted, 0) = 0  AND i.totalItemPaidAmount > 0