ALTER VIEW [dbo].[pal_vCreditListing] AS

SELECT	MAX(CONVERT(VARCHAR(10),dateOfTransaction, 120) + RIGHT(CONVERT(VARCHAR(32),dateOfTransaction,100),8)) [CreditNoteDateTime]
		,dbo.GetInitials(U.name+' '+U.surname) AS [CreditNoteAuthorizedBy]		
		,'C/N'+CONVERT(VARCHAR(2),RIGHT(dbo.GetFiscalYear(dateOfTransaction),2))+REPLACE(STR(A.receiptNo, 6), SPACE(1), '0') AS [CreditNoteNumber]
		,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))) [NettAmount]
		,CONVERT(DECIMAL(18,2),SUM((A.amount) - ((100 / (100 + A.vatRate)) * A.amount))) [VatAmount]
		,SUM(A.amount) [TotalCreditNoteAmount]
FROM	pal_Account A
		JOIN [Framework_EVOL-1].dbo.pal_User U
			ON U.recId = A.userId
WHERE	A.isVisible = 1
		AND procedureType = 'CJ'
		AND A.patientNumber = 1133
GROUP BY dbo.GetInitials(U.name+' '+U.surname)		
		,'C/N'+CONVERT(VARCHAR(2),RIGHT(dbo.GetFiscalYear(dateOfTransaction),2))+REPLACE(STR(A.receiptNo, 6), SPACE(1), '0')
		,A.patientNumber
		,A.patientName+' '+A.patientSurname
		,A.medAidName


GO


