CREATE VIEW [dbo].[pal_vRemittanceListing]
AS
SELECT   LEFT(CONVERT(VARCHAR(150),l.originalProcessDate, 120), 10) AS Date,
CASE WHEN l.rejected = 1 THEN 'Rejected' WHEN l.accountId = 0 THEN 'Unprocessed' 
ELSE CASE 
WHEN l.manualProcessed = 1 THEN 'Processed by ' + ISNULL(u.name, '') 
WHEN a.allocatedReference = '' THEN 'Processed - Unallocated' 
WHEN l.manualAllocated = 1 THEN 'Processed by ERA, Allocated by ' + ISNULL(u1.name, '')
ELSE 'Processed by ERA' END END AS Status, 
                         CASE WHEN l.rejected = 1 OR a.recId IS NULL THEN '' ELSE 'REC' + CONVERT(VARCHAR(2), RIGHT(dbo.GetFiscalYear(A.dateOfTransaction), 2)) + REPLACE(STR(A.receiptNo, 6), SPACE(1), '0') END AS ReceiptNumber, ISNULL(a.patientNumber, '') 
                         AS PatientNumber, CASE WHEN a.recId IS NULL THEN m.patientFullName + ' ' + m.patientSurname ELSE A.patientName + ' ' + A.patientSurname END AS Patient, 
						 LEFT(CONVERT(VARCHAR(150),m.patientDOB, 120), 10) AS patientDOB, m.patientIdNumber, ISNULL(a.medAidName, 
                         e.medicalSchemeName) AS MedicalAid, 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 LEFT OUTER JOIN
                         dbo.pal_Account AS a ON a.recId = l.accountId LEFT OUTER JOIN
                         dbo.pal_vUserShared AS u ON l.manualProcessUserId = u.recId LEFT OUTER JOIN
                         dbo.pal_vUserShared AS u1 ON l.manualAllocateUserId = u1.recId

