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

