CREATE VIEW [dbo].[pal_vInvoiceListing] AS

SELECT	MAX(CONVERT(VARCHAR(10),dateOfService, 120) + RIGHT(CONVERT(VARCHAR(32),dateOfService,100),8)) [InvoicedDateTime]
		,dbo.GetInitials(U.name+' '+U.surname) AS [InvoiceAuthorizedBy]		
		,'INV'+CONVERT(VARCHAR(2),RIGHT(dbo.GetFiscalYear(dateOfService),2))+REPLACE(STR(A.invoiceNo, 6), SPACE(1), '0') AS [InvoiceNumber]
		,A.patientNumber AS [PatientNumber]
		,A.patientName+' '+A.patientSurname AS [Patient]
		,A.medAidName AS [MedicalAid]
		,CONVERT(DECIMAL(18,2),(SUM((100 / (100 + A.vatRate)) * A.amount))-(SUM((100 / (100 + A.vatRate)) * ISNULL(A.allocatedAssist,0)))) [NettAmount]
		,CONVERT(DECIMAL(18,2),SUM((100 / (100 + A.vatRate)) * ISNULL(A.allocatedAssist,0))) [AssistantFee]
		,CONVERT(DECIMAL(18,2),SUM((A.amount) - ((100 / (100 + A.vatRate)) * A.amount))) [VatAmount]
		,SUM(A.amount) [TotalBilling]
FROM	pal_Account A
		JOIN [Framework_EVOL-1].dbo.pal_User U
			ON U.recId = A.userId
WHERE	A.isVisible = 1
		AND procedureType = 'TI'
GROUP BY dbo.GetInitials(U.name+' '+U.surname)		
		,'INV'+CONVERT(VARCHAR(2),RIGHT(dbo.GetFiscalYear(dateOfService),2))+REPLACE(STR(A.invoiceNo, 6), SPACE(1), '0')
		,A.patientNumber
		,A.patientName+' '+A.patientSurname
		,A.medAidName
