IF OBJECT_ID(N'PBI_OSInvoices', N'V') IS NOT NULL
    DROP VIEW [dbo].[PBI_OSInvoices]
GO

/**** Object: View [dbo].[PBI_OSInvoices] Script Date: 17/07/2024 17:02:51 *****/

CREATE VIEW [dbo].[PBI_OSInvoices] 
AS
    SELECT
        dbo.Contacts.Code,
        dbo.Contacts.[Name], 
        dbo.Contacts.Tel, 
        dbo.Contacts.email, 
        dbo.DebtorsLedger.[DATE], 
        DATEDIFF(day, dbo.DebtorsLedger.[DATE], { fn NOW() }) AS DaysOS, 
        dbo.DebtorsLedger.REF, 
        dbo.DebtorsLedger.OSVALUE, 
        dbo.DebtorsLedger.ORIGINAL, 
        dbo.DebtorsLedger.BATCHNO,
        dbo.DebtorsLedger.NARR, 
        dbo.DebtorsLedger.[YEAR], 
        dbo.DebtorsLedger.PER, 
        CASE 
            WHEN debtorsledger.FEECODE = '' THEN matters.fecode
            ELSE debtorsledger.FEECode
        END AS FeeCode,
        dbo.DebtorsLedger.[TYPE], 
        DATEDIFF(MONTH, dbo.DebtorsLedger.[DATE], { fn NOW() }) AS MonthsOS,
        FeeEarnerCodes_1.[Name] AS FeeEarner,
        dbo.DebtorsLedger.OSFEES, 
        dbo.DebtorsLedger.OSOUTLAY, 
        dbo.DebtorsLedger.OSVAT, 
        dbo.DebtorsLedger.ORFEES, 
        dbo.DebtorsLedger.OROUTLAY, 
        dbo.DebtorsLedger.ORVAT,
        dbo.Departments.[DESCRIPTION] AS Department,
        dbo.DebtorsLedger.MATTER,
        dbo.matters.[Description] AS MatterDescription,
        dbo.FeeEarnerCodes.[Name] AS MatterFeeEarner,
        dbo.matters.FECode AS MatterFeCode
    FROM dbo.Contacts 
    INNER JOIN dbo.matters
        ON dbo.Contacts.Code = dbo.matters.ClientCode
    INNER JOIN dbo.DebtorsLedger
        ON dbo.matters.Code = dbo.DebtorsLedger.MATTER
    LEFT OUTER JOIN dbo.FeeEarnerCodes
        ON dbo.matters.FECode = dbo.FeeEarnerCodes.Code
    LEFT OUTER JOIN dbo.FeeEarnerCodes AS FeeEarnerCodes_1
        ON dbo.DebtorsLedger.FEECODE = FeeEarnerCodes_1.Code
    LEFT OUTER JOIN dbo.Departments
        ON dbo.matters.Dept = dbo.Departments.CODE
    WHERE (dbo.DebtorsLedger.OSVALUE <> 0)

GO