CREATE VIEW [dbo].[pal_vReceiptListing] AS

SELECT	MAX(CONVERT(VARCHAR(10),A.dateOfService, 120)) [PaymentDateTime]
		,CASE WHEN U.name IS NULL THEN '' ELSE dbo.GetInitials(U.name+' '+U.surname) END AS [PaymentReceiptingAuthorizedBy]		
		,'REC'+CONVERT(VARCHAR(2),RIGHT(dbo.GetFiscalYear(A.dateOfService),2))+REPLACE(STR(A.receiptNo, 6), SPACE(1), '0') AS [ReceiptNumber]
		,A.patientNumber AS [PatientNumber]
		,A.patientName+' '+A.patientSurname AS [Patient]
		,A.medAidName AS [MedicalAid]
		,A.procedureCode AS [PaymentMethod]
		,SUM(A.amount)*-1 AS [TotalReceiptedAmount]
		,dbo.GetAmountPaidToAssistantFee(A.allocatedReference) AS [AmountPaidToAssistantFee]
FROM	pal_Account A
		LEFT OUTER JOIN [Framework_EVOL-1].dbo.pal_User U
			ON U.recId = A.userId
WHERE	A.isVisible = 1
		AND A.procedureType IN ('PM')
GROUP BY CASE WHEN U.name IS NULL THEN '' ELSE dbo.GetInitials(U.name+' '+U.surname) END		
		,'REC'+CONVERT(VARCHAR(2),RIGHT(dbo.GetFiscalYear(A.dateOfService),2))+REPLACE(STR(A.receiptNo, 6), SPACE(1), '0')
		,A.patientNumber
		,A.patientName+' '+A.patientSurname
		,A.medAidName
		,A.procedureCode
		,dbo.GetAmountPaidToAssistantFee(A.allocatedReference)
