CREATE VIEW v_ReceiptListing AS

SELECT	MAX(CONVERT(VARCHAR(10),A.dateOfService, 120)) [PaymentDateTime]
		,dbo.GetInitials(U.name+' '+U.surname) AS [PaymentReceiptingAuthorizedBy]		
		,'REC'+CONVERT(VARCHAR(2),RIGHT(dbo.GetFiscalYear(A.dateOfService),2))+REPLACE(STR(A.receiptNo, 4), SPACE(1), '0') AS [ReceiptNumber]
		,A.patientNumber AS [PatientNumber]
		,A.patientName+' '+A.patientSurname AS [Patient]
		,A.medAidName AS [MedicalAid]
		,A.procedureCode AS [PaymentMethod]
		,MAX(A.amount)*-1 AS [TotalReceiptedAmount]
		--,CONVERT(DECIMAL(18,2),(SUM((100 / (100 + A.vatRate)) * A.amount))) [Nett Amount]
		--,CONVERT(DECIMAL(18,2),SUM((A.amount) - ((100 / (100 + A.vatRate)) * A.amount))) [Vat Amount]
		,SUM(ISNULL(P.amount,0)) AS [AmountPaidToAssistantFee]
FROM	pal_Account A
		LEFT JOIN pal_Account P
			ON P.recId IN (SELECT * FROM dbo.SplitString(LEFT(A.allocatedReference,CASE WHEN CHARINDEX(':',A.allocatedReference) > 0 THEN CHARINDEX(':',A.allocatedReference)-1 ELSE 0 END),','))
			AND P.modifier1 IN ('0008','0009')
		JOIN pal_User U
			ON U.recId = A.userId
WHERE	A.isVisible = 1
		AND A.procedureType IN ('PM')
		--AND A.patientNumber = 1016
GROUP BY dbo.GetInitials(U.name+' '+U.surname)		
		,'REC'+CONVERT(VARCHAR(2),RIGHT(dbo.GetFiscalYear(A.dateOfService),2))+REPLACE(STR(A.receiptNo, 4), SPACE(1), '0')
		,A.patientNumber
		,A.patientName+' '+A.patientSurname
		,A.medAidName
		,A.procedureCode