
-- CVH 2018-02-28 NOT BEING USED ANYMORE. Not deleting it because we're using the object when calling the stored procedure. Recursive function didn't work in View. 

CREATE VIEW [dbo].[pal_vRemittanceListing] AS
SELECT   LEFT(CONVERT(VARCHAR(150),l.originalProcessDate, 120), 10) 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,
e.eraReferenceNumber AS EraReferenceNumber,
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 ProcessedStatus, 
                         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, ISNULL(r.responseDescription, '') AS ResponseDescription, g.generalComments AS GeneralComments, e.disclaimer AS Disclaimer,
						 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 LEFT OUTER JOIN 
							(SELECT DISTINCT r2.eraClaimItemId, 
								SUBSTRING(
									(
										SELECT ', '+ r1.responseDescription AS [text()]
										FROM pal_MSEraClaimItemResponse r1
										WHERE r1.eraClaimItemId = r2.eraClaimItemId
										FOR XML PATH ('')
									), 3, 4000) AS responseDescription
							FROM pal_MSEraClaimItemResponse r2 
							) r ON r.eraClaimItemId = l.eraClaimItemId 
							LEFT OUTER JOIN 
							 (SELECT DISTINCT g2.eraClaimItemId,
								SUBSTRING(
									(
										SELECT ', '+ g1.generalComments AS [text()]
										FROM pal_MSEraClaimItemGeneralComments g1
										WHERE g1.eraClaimItemId = g2.eraClaimItemId
										FOR XML PATH ('')
									), 3, 4000) AS generalComments
							FROM pal_MSEraClaimItemGeneralComments g2 ) g ON g.eraClaimItemId = i.recId 







GO


