UnitFinancialFigures

    ALTER VIEW [dbo].[UnitFinancialFigures] AS

    WITH
    ctePeriodWeekCount AS (
        SELECT Count(Week.WeekNo) AS WeekCount, PrdNo AS Period, PrdYear AS Year
        FROM Week
        INNER JOIN Period on Week.WeekEndDate BETWEEN Period.StartDate AND Period.EndDate
        GROUP BY PrdNo, PrdYear),

    cteLabourFigures AS (
        SELECT Labour.LabUnitId, Labour.LabPeriod, Labour.LabYear,
        SUM(CASE WHEN LabourTypes.LTypeIsPayrollLabour = 1 THEN LabourBreakdown.LBrkAmount ELSE 0 END) AS PayRollLabour,
        SUM(CASE WHEN LabourTypes.LTypeIsPayrollLabour = 0 THEN LabourBreakdown.LBrkAmount ELSE 0 END) AS OtherLabour
        FROM Labour
        INNER JOIN LabourBreakdown on LabourBreakDown.LBrkLabId = Labour.LabId
        INNER JOIN LabourTypes on LabourTypes.LTypeId = LabourBreakdown.LBrkLTypeId
        GROUP BY Labour.LabUnitId, Labour.LabPeriod, Labour.LabYear),

    cteMobileRelief AS (
        SELECT McDtUnitId, McHdPeriod, McHdYear, SUM(McDtAmount) AS MobileReliefTotal
        FROM MobileCostHeader
        INNER JOIN MobileCostDetail on MobileCostDetail.McDtHdId = MobileCostHeader.MCHdId
        GROUP BY McDtUnitId, McHdPeriod, McHdYear),

    cteInvoices AS (
        SELECT Period.PrdNo, Period.PrdYear, Invoice.AccountID, SUM(Invoice.InvTotalInclVat) AS TotalInvoices,
        SUM(Invoice.InvVatAmount) AS TotalInvoicesVat
        FROM [SOP].Invoice
        INNER JOIN Period on Invoice.InvoiceDate BETWEEN Period.StartDate AND Period.EndDate
        GROUP By Period.PrdNo, Period.PrdYear, Invoice.AccountID),

    cteSundryCosts AS (
        SELECT ScDtAccountId, ScDtPeriodNo, ScDtYearNo, SUM(ScdtTotal) AS SundryTotal,
        SUM(ScdtAmount) AS SundryNetTotal,
        SUM(ScdtVatAmount) AS SundryVatAmount
        FROM SundryCostDetail
        WHERE ScdtRepeat = 0
        GROUP BY ScDtAccountId, ScDtPeriodNo, ScDtYearNo),

    cteAdditionalProfit AS (
        SELECT ScDtAccountId, ScDtPeriodNo, ScDtYearNo, SUM(ScdtTotal) AS AdditionalProfitTotal,
        SUM(ScdtAmount) AS AdditionalProfitNetTotal,
        SUM(ScdtVatAmount) AS AdditionalProfitVatAmount
        FROM SundryCostDetail
        WHERE ScdtRepeat = 0 AND SCdtExcludeFromGP = 0
        GROUP BY ScDtAccountId, ScDtPeriodNo, ScDtYearNo),

    cteCashReceived AS (
        SELECT CtUnitId, CtPeriod, CtYear,
        SUM(CtAmount - CtAgentVatAmount) AS GoodsAmount, SUM(CtVatAmount + CtAgentVatAmount) AS VatTotal, SUM(CtTotal) AS Total,
        SUM(VATFree.CanaAmount - VATFree.CanaAgentVatAmount) AS VATFreeGoodsAmount,
        SUM(Catering.CanaAmount - Catering.CanaAgentVatAmount) AS CateringGoodsAmount,
        SUM(Vending.CanaAmount - Vending.CanaAgentVatAmount) AS VendingGoodsAmount,
        SUM(CansConf.CanaAmount - CansConf.CanaAgentVatAmount) AS CansConfGoodsAmount
        FROM CashTran
        LEFT JOIN CashAnalysis AS VATFree ON CashTran.CtId = VATFree.CanaTranId AND VATFree.CanaCode = 'G201'
        LEFT JOIN CashAnalysis AS Catering ON CashTran.CtId = Catering.CanaTranId AND Catering.CanaCode = 'G205'
        LEFT JOIN CashAnalysis AS Vending ON CashTran.CtId = Vending.CanaTranId AND Vending.CanaCode = 'G202'
        LEFT JOIN CashAnalysis AS CansConf ON CashTran.CtId = CansConf.CanaTranId AND CansConf.CanaCode = 'G203'
        WHERE CtCategoryId = 3 AND CtApproved = 1 ANd CtTranType = 'R'
        GROUP BY CtUnitId, CtPeriod, CtYear),

    cteFreeIssues AS (
        SELECT CtUnitId, CtPeriod, CtYear,
        SUM(CtAmount - CtAgentVatAmount) AS GoodsAmount, SUM(CtVatAmount + CtAgentVatAmount) AS VatTotal, SUM(CtTotal) AS Total,
        SUM(Catering.CanaAmount - Catering.CanaAgentVatAmount) AS CateringGoodsAmount,
        SUM(Vending.CanaAmount - Vending.CanaAgentVatAmount) AS VendingGoodsAmount,
        SUM(CansConf.CanaAmount - CansConf.CanaAgentVatAmount) AS CansConfGoodsAmount,
        SUM(Labour.CanaAmount - Labour.CanaAgentVatAmount) AS LabourGoodsAmount
        FROM CashTran
        INNER JOIN [SOP].Accounts ON Accounts.AcUniqueId = CtUnitId
        LEFT JOIN CashAnalysis AS Catering ON CashTran.CtId = Catering.CanaTranId AND Catering.CanaCode = 'G204'
        LEFT JOIN CashAnalysis AS Vending ON CashTran.CtId = Vending.CanaTranId AND Vending.CanaCode = 'G202'
        LEFT JOIN CashAnalysis AS CansConf ON CashTran.CtId = CansConf.CanaTranId AND CansConf.CanaCode = 'G203'
        LEFT JOIN CashAnalysis AS Labour ON CashTran.CtId = Labour.CanaTranId AND Labour.CanaCode = 'H995'
        WHERE CtCategoryId = 2
        GROUP BY CtUnitId, CtPeriod, CtYear),

    cteExternalFreeIssues AS (
        SELECT CtUnitId, CtPeriod, CtYear, SUM(CtAmount - CtAgentVatAmount) AS GoodsAmount, SUM(CtVatAmount + CtAgentVatAmount) AS VatTotal, SUM(CtTotal) AS Total
        FROM CashTran
        INNER JOIN [SOP].Accounts on Accounts.AcUniqueId = CtUnitId
        WHERE CtCategoryId = 4
        GROUP BY CtUnitId, CtPeriod, CtYear),

    cteAgencyLabour AS (
        SELECT PtUnitId, PtYear, PtPeriod, SUM(CASE WHEN PtTranType = 'I' THEN PanaGoodsAmount - PanaAgentVatAmount ELSE -(PanaGoodsAmount - PanaAgentVatAmount) END) Total
        FROM PurchaseTran
        INNER Join PurchaseAnalysis on PurchaseAnalysis.PanaTranId = PurchaseTran.PtId
        INNER JOIN [SOP].Accounts on Accounts.AcUniqueID = PtUnitId
        INNER JOIN UnitAnalysisConfig on UnitAnalysisConfig.UACfgAnalysisCode = PurchaseAnalysis.PanaPCode AND UnitAnalysisConfig.UACfgType = 1
        WHERE PurchaseAnalysis.PanaPCode = 'H995' AND (PtTranType = 'I' Or PtTranType = 'C') AND PtApproved =1
        GROUP BY PtUnitId, PtPeriod, PtYear),

    ctePurchases AS (
        SELECT PurchaseTran.PtUnitId, PurchaseTran.PtPeriod, PurchaseTran.PtYear,
        SUM(CASE WHEN PtTranType = 'I' THEN PtTotal WHEN PtTranType = 'C' THEN - PtTotal ELSE 0 END) AS PurchaseTotal,
        SUM(CASE WHEN PtTranType = 'I' THEN PtAmount - PtAgentVatAmount WHEN PtTranType = 'C' THEN -(PtAmount - PtAgentVatAmount) ELSE 0 END) AS PurchaseNetTotal,
        SUM(CASE WHEN PtTranType = 'I' THEN PtVat + PtAgentVatAmount WHEN PtTranType = 'C' THEN -(PtVat + PtAgentVatAmount) ELSE 0 END) AS VatTotal,
        SUM(CASE WHEN PtSupplierId = CmpCashAccount THEN PtTotal ELSE 0 END) AS TotalCashPaid,
        SUM(CASE WHEN PtTranType = 'I' THEN Catering.PanaGoodsAmount - Catering.PanaAgentVatAmount WHEN PtTranType = 'C' THEN -(Catering.PanaGoodsAmount - Catering.PanaAgentVatAmount) ELSE 0 END) CateringTotal,
        SUM(CASE WHEN PtTranType = 'I' THEN Catering.PanaAgentVatAmount + Catering.PanaVatAmount WHEN PtTranType = 'C' THEN -(Catering.PanaAgentVatAmount + Catering.PanaVatAmount) ELSE 0 END) AS CateringVatAmount,
        SUM(CASE WHEN PtTranType = 'I' THEN Vending.PanaGoodsAmount - Vending.PanaAgentVatAmount WHEN PtTranType = 'C' THEN -(Vending.PanaGoodsAmount - Vending.PanaAgentVatAmount) ELSE 0 END) VendingTotal,
        SUM(CASE WHEN PtTranType = 'I' THEN Vending.PanaAgentVatAmount + Vending.PanaVatAmount WHEN PtTranType = 'C' THEN -(Vending.PanaAgentVatAmount + Vending.PanaVatAmount) ELSE 0 END) AS VendingVatAmount,
        SUM(CASE WHEN PtTranType = 'I' THEN CansConf.PanaGoodsAmount - CansConf.PanaAgentVatAmount WHEN PtTranType = 'C' THEN -(CansConf.PanaGoodsAmount - CansConf.PanaAgentVatAmount) ELSE 0 END) CansConfTotal,
        SUM(CASE WHEN PtTranType = 'I' THEN CansConf.PanaAgentVatAmount + CansConf.PanaVatAmount WHEN PtTranType = 'C' THEN -(CansConf.PanaAgentVatAmount + CansConf.PanaVatAmount) ELSE 0 END) AS CansConfVatAmount
        FROM PurchaseTran
        INNER JOIN CompParam on Compparam.CmpDefault = 1
        INNER JOIN [SOP].Accounts on Accounts.AcUniqueID = PurchaseTran.PtUnitId
        LEFT JOIN PurchaseAnalysis AS Catering on Catering.PanaTranId = PurchaseTran.PtId AND (Catering.PanaPCode = 'H201' OR Catering.PanaPCode = 'H401')
        LEFT JOIN PurchaseAnalysis AS Vending on Vending.PanaTranId = PurchaseTran.PtId AND Vending.PanaPCode = 'H202'
        LEFT JOIN PurchaseAnalysis AS CansConf on CansConf.PanaTranId = PurchaseTran.PtId AND CansConf.PanaPCode = 'H211'
        WHERE (PtTranType = 'I' OR PtTranType = 'C') AND PtApproved = 1
        GROUP BY PtUnitId, PtPeriod, PtYear),

    cteSundryPurchases AS (
        SELECT PtUnitId, PtYear, PtPeriod,
        SUM(CASE WHEN PtTranType = 'I' THEN PanaGoodsAmount - PanaAgentVatAmount ELSE -(PanaGoodsAmount - PanaAgentVatAmount) END) Total,
        SUM(CASE WHEN PtTranType = 'I' THEN PanaAgentVatAmount + PanaVatAmount WHEN PtTranType = 'C' THEN -(PanaAgentVatAmount + PanaVatAmount) ELSE 0 END) AS VatAmount
        FROM PurchaseTran
        INNER Join PurchaseAnalysis on PurchaseAnalysis.PanaTranId = PurchaseTran.PtId
        INNER JOIN [SOP].Accounts on Accounts.AcUniqueID = PurchaseTran.PtUnitId
        INNER JOIN UnitAnalysisConfig on UnitAnalysisConfig.UACfgAnalysisCode = PurchaseAnalysis.PanaPCode AND (UnitAnalysisConfig.UACfgType = 1 OR UnitAnalysisConfig.UACfgType = 3)
        WHere UnitAnalysisConfig.UACfgTandOLabel = 'Sundry' AND (PtTranType = 'I' Or PtTranType = 'C') AND PtApproved = 1
        GROUP BY PtUnitId, PtPeriod, PtYear),

    cteDiscount AS (
        SELECT PurchaseTran.PtUnitId, SUM(CASE WHEN PtTRanType = 'I' THEN PtDiscountAmount ELSE -PtDiscountAmount END) AS DiscountTotal, PtPeriod, PtYear
        FROM PurchaseTran
        WHERE PurchaseTran.PtApproved = 1 And (PurchaseTran.PtTranType = 'I' OR PurchaseTran.PtTranType = 'C')
        GROUP BY PurchaseTran.PtUnitId, PtPeriod, PtYear),

    cteUnitPeriodOpeningStock AS (
        SELECT UStkId, UStkOpening, UnitStock.UStkUnitId, UnitStock.UStkPeriod, UnitStock.UStkYearNo, SUM(OpeningCatering.USAnaAmount) AS OpeningCateringStock, SUM(OpeningBeverage.USAnaAmount) AS OpeningBeverageStock, SUM(OpeningSundry.USAnaAmount) AS OpeningSundryStock, SUM(OpeningCansConf.USAnaAmount) AS OpeningCansConfStock
        FROM UnitStock
        LEFT JOIN UnitStockAnalysis AS OpeningCatering ON UnitStock.UStkId = OpeningCatering.USAnaTranId AND OpeningCatering.USanaType = 'O' AND OpeningCatering.USAnaCode = 'CAT001'
        LEFT JOIN UnitStockAnalysis AS OpeningBeverage ON UnitStock.UStkId = OpeningBeverage.USAnaTranId AND OpeningBeverage.USanaType = 'O' AND OpeningBeverage.USAnaCode = 'BEV001'
        LEFT JOIN UnitStockAnalysis AS OpeningSundry ON UnitStock.UStkId = OpeningSundry.USAnaTranId AND OpeningSundry.USanaType = 'O' AND OpeningSundry.USAnaCode = 'SUN001'
        LEFT JOIN UnitStockAnalysis AS OpeningCansConf ON UnitStock.UStkId = OpeningCansConf.USAnaTranId AND OpeningCansConf.USanaType = 'O' AND OpeningCansConf.USAnaCode = 'CAN001'
        INNER JOIN Week on Week.WeekNo = UnitStock.UStkWeeKNo AND Week.WeekYear = UnitStock.UStkYearNo
        INNER JOIN (
            SELECT UStkUnitId, UStkPeriod, MIN(Week.WeekEndDate) OpeningDate
            FROM UnitStock
            INNER JOIN Week on Week.WeekNo = UnitStock.UStkWeeKNo AND Week.WeekYear = UnitStock.UStkYearNo
            GROUP BY UStkUnitId, UStkPeriod) OpeningData on OpeningData.OpeningDate = Week.WeekEndDate AND OpeningData.UStkUnitId = UnitStock.UStkUnitId
        GROUP BY UStkId, UStkOpening, UnitStock.UStkUnitId, UnitStock.UStkPeriod, UnitStock.UStkYearNo),

    cteUnitPeriodClosingStock AS (
        SELECT UStkId, UStkClosing, UnitStock.UStkUnitId, UnitStock.UStkPeriod, UnitStock.UStkYearNo, SUM(ClosingCatering.USAnaAmount) AS ClosingCateringStock, SUM(ClosingBeverage.USAnaAmount) AS ClosingBeverageStock, SUM(ClosingSundry.USAnaAmount) AS ClosingSundryStock, SUM(ClosingCansConf.USAnaAmount) AS ClosingCansConfStock
        FROM UnitStock
        LEFT JOIN UnitStockAnalysis AS ClosingCatering ON UnitStock.UStkId = ClosingCatering.USAnaTranId AND ClosingCatering.USanaType = 'C' AND ClosingCatering.USAnaCode = 'CAT001'
        LEFT JOIN UnitStockAnalysis AS ClosingBeverage ON UnitStock.UStkId = ClosingBeverage.USAnaTranId AND ClosingBeverage.USanaType = 'C' AND ClosingBeverage.USAnaCode = 'BEV001'
        LEFT JOIN UnitStockAnalysis AS ClosingSundry ON UnitStock.UStkId = ClosingSundry.USAnaTranId AND ClosingSundry.USanaType = 'C' AND ClosingSundry.USAnaCode = 'SUN001'
        LEFT JOIN UnitStockAnalysis AS ClosingCansConf ON UnitStock.UStkId = ClosingCansConf.USAnaTranId AND ClosingCansConf.USanaType = 'C' AND ClosingCansConf.USAnaCode = 'CAN001'
        INNER JOIN Week on Week.WeekNo = UnitStock.UStkWeeKNo AND Week.WeekYear = UnitStock.UStkYearNo
        INNER JOIN (
            SELECT UStkUnitId, UStkPeriod, MAX(Week.WeekEndDate) ClosingDate
            FROM UnitStock
            INNER JOIN Week on Week.WeekNo = UnitStock.UStkWeeKNo AND Week.WeekYear = UnitStock.UStkYearNo
            GROUP BY UStkUnitId, UStkPeriod) ClosingData on ClosingData.ClosingDate = Week.WeekEndDate AND ClosingData.UStkUnitId = UnitStock.UStkUnitId
        GROUP BY UStkId, UStkClosing, UnitStock.UStkUnitId, UnitStock.UStkPeriod, UnitStock.UStkYearNo)

    SELECT
    ASubAccountId,
    AcRef,
    AcName,
    Period.PrdNo,
    Period.PrdYear,
    Period.StartDate,
    Period.EndDate,
    CASE WHEN ASubSubmittedBy IS NULL THEN 0 ELSE 1 END AS Submitted,
    ISNULL(cteDiscount.DiscountTotal ,0) AS DiscountTotal,
    ISNULL(cteLabourFigures.PayRollLabour,0) AS PayrollLabour,
    ISNULL(cteLabourFigures.OtherLabour, 0)  AS OtherLabour,
    ((AcUnitTrainingLevy / 100) * ISNULL(cteLabourFigures.PayRollLabour,0)) AS TotalTrainingLevy,
    ((CmpApprenticeshipLevyPercent / 100) * ISNULL(cteLabourFigures.PayRollLabour,0)) AS ApprenticeshipLevy,
    ISNULL(cteMobileRelief.MobileReliefTotal,0) AS MobileReliefTotal,
    ISNULL(cteAgencyLabour.Total, 0) AS AgencyLabour,
    ISNULL(ctePurchases.PurchaseTotal,0) AS PurchaseTotal,
    ISNULL(cteInvoices.TotalInvoices,0) AS TotalInvoices,
    ISNULL(cteSundryCosts.SundryTotal,0) AS SundryTotal,
    ISNULL(cteCashReceived.Total,0) AS CashReceivedTotal,
    ISNULL(cteCashReceived.VatTotal,0) AS CashReceivedVAT,
    ISNULL(ctePurchases.TotalCashPaid, 0) AS TotalCashPaid,
    ISNULL(cteCashReceived.GoodsAmount,0) AS CashReceivedNetTotal,
    ISNULL(cteInvoices.TotalInvoicesVat,0) AS TotalInvoicesVAT,
    ISNULL(cteUnitPeriodOpeningStock.UStkOpening,0) AS OpeningStock,
    ISNULL(cteUnitPeriodClosingStock.UStkClosing,0) AS ClosingStock,
    ISNULL(cteUnitPeriodOpeningStock.UStkOpening,0) - ISNULL(cteUnitPeriodClosingStock.UStkClosing,0) AS StockBalance,
    ISNULL(cteFreeIssues.GoodsAmount, 0) AS FreeIssuesTotal,
    ISNULL(cteFreeIssues.GoodsAmount, 0) + ISNULL(cteCashReceived.Total,0) AS TotalSales,
    ISNULL(cteFreeIssues.GoodsAmount, 0) + ISNULL(cteCashReceived.GoodsAmount,0) AS TotalNetSales,
    ISNULL(ctePurchases.PurchaseNetTotal,0) + (ISNULL(cteUnitPeriodOpeningStock.UStkOpening,0) - ISNULL(cteUnitPeriodClosingStock.UStkClosing,0)) AS Consumption,
    (ISNULL(cteFreeIssues.GoodsAmount, 0) + ISNULL(cteCashReceived.GoodsAmount,0)) - (ISNULL(ctePurchases.PurchaseNetTotal,0) + (ISNULL(cteUnitPeriodOpeningStock.UStkOpening,0) - ISNULL(cteUnitPeriodClosingStock.UStkClosing,0))) AS Profit,
    ISNULL(cteLabourFigures.PayRollLabour, 0) + ISNULL(cteLabourFigures.OtherLabour, 0) + ISNULL(cteMobileRelief.MobileReliefTotal,0) + ((AcUnitTrainingLevy / 100) * ISNULL(cteLabourFigures.PayRollLabour,0)) + ((CmpApprenticeshipLevyPercent / 100) * ISNULL(cteLabourFigures.PayRollLabour,0)) AS TotalLabour,
    ISNULL(UnitLabourFigures.TotalBacksheetLabour,0) AS TotalBacksheetLabour,
    ISNULL(ctePurchases.CateringTotal,0) AS CateringPurchases, ISNULL(ctePurchases.VendingTotal,0) AS VendingPurchases,
    ISNULL(cteSundryPurchases.Total, 0) AS SundryInvoicesOnly,
    ISNULL(cteSundryPurchases.Total, 0) + ISNULL(cteSundryCosts.SundryNetTotal,0) AS SundryPurchases,
    ISNULL(ctePurchases.CansConfTotal, 0) AS CansCOnfPurchases,
    ISNULL(ctePurchases.CateringTotal,0) + ISNULL(ctePurchases.VendingTotal,0) + ISNULL(cteSundryPurchases.Total, 0) + ISNULL(cteSundryCosts.SundryNetTotal,0) + ISNULL(ctePurchases.CansConfTotal, 0) - ISNULL(cteExternalFreeIssues.GoodsAmount,0) AS TandONetPurchases,
    ISNULL(ctePurchases.CateringTotal,0) + ISNULL(ctePurchases.VendingTotal,0) + ISNULL(cteSundryPurchases.Total, 0) + ISNULL(cteSundryCosts.SundryNetTotal,0) + ISNULL(ctePurchases.CansConfTotal, 0) AS GPPurchases,
    ISNULL(ctePurchases.CateringVatAmount,0) + ISNULL(ctePurchases.VendingVatAmount,0) + ISNULL(cteSundryPurchases.VatAmount, 0) + ISNULL(cteSundryCosts.SundryVatAmount,0) + ISNULL(ctePurchases.CansConfVatAmount, 0) AS TandOVatPurchases,
    ISNULL(ctePurchases.VatTotal,0) + ISNULL(cteSundryCosts.SundryVatAmount,0) AS PurchasesVat,
    ((ISNULL(ctePurchases.CateringTotal,0) + ISNULL(ctePurchases.VendingTotal,0) + ISNULL(cteSundryPurchases.Total, 0) + ISNULL(cteSundryCosts.SundryNetTotal,0) + ISNULL(ctePurchases.CansConfTotal, 0)) + ISNULL(ctePurchases.VatTotal,0) + ISNULL(cteSundryCosts.SundryVatAmount,0)) AS GrossPurchases,
    (ISNULL(ctePurchases.CateringTotal,0) + ISNULL(ctePurchases.VendingTotal,0) + ISNULL(cteSundryPurchases.Total, 0) + ISNULL(cteSundryCosts.SundryNetTotal,0) + ISNULL(ctePurchases.CansConfTotal, 0)  - ISNULL(cteExternalFreeIssues.GoodsAmount,0)) + (ISNULL(cteUnitPeriodOpeningStock.UStkOpening,0) - ISNULL(cteUnitPeriodClosingStock.UStkClosing,0) ) AS TandOConsumption,
    (ISNULL(cteFreeIssues.GoodsAmount, 0) + ISNULL(cteCashReceived.GoodsAmount,0)) - ((ISNULL(ctePurchases.CateringTotal,0) + ISNULL(ctePurchases.VendingTotal,0) + ISNULL(cteSundryPurchases.Total, 0) + ISNULL(cteSundryCosts.SundryNetTotal,0) + ISNULL(ctePurchases.CansConfTotal, 0)) + (ISNULL(cteUnitPeriodOpeningStock.UStkOpening,0) - ISNULL(cteUnitPeriodClosingStock.UStkClosing,0) )) AS TandONetProfit,

    CASE WHEN Accounts.AcUnitTradingType = 'A' THEN ISNULL(cteCashReceived.Total,0) - ((ISNULL(ctePurchases.CateringTotal,0) + ISNULL(ctePurchases.VendingTotal,0) + ISNULL(cteSundryPurchases.Total, 0) + ISNULL(cteSundryCosts.SundryNetTotal,0) + ISNULL(ctePurchases.CansConfTotal, 0) - ISNULL(cteExternalFreeIssues.GoodsAmount,0)) + ISNULL(ctePurchases.VatTotal,0) + ISNULL(cteSundryCosts.SundryVatAmount,0))
    ELSE (ISNULL(cteFreeIssues.GoodsAmount, 0) + ISNULL(cteCashReceived.GoodsAmount,0)) - ((ISNULL(ctePurchases.CateringTotal,0) + ISNULL(ctePurchases.VendingTotal,0) + ISNULL(cteSundryPurchases.Total, 0) + ISNULL(cteSundryCosts.SundryNetTotal,0) + ISNULL(ctePurchases.CansConfTotal, 0) - ISNULL(cteExternalFreeIssues.GoodsAmount,0)) + (ISNULL(cteUnitPeriodOpeningStock.UStkOpening,0) - ISNULL(cteUnitPeriodClosingStock.UStkClosing,0) )) END
    AS TandOProfit,

    CASE WHEN Accounts.AcUnitTradingType = 'A' THEN ISNULL(UnitLabourFigures.TotalBacksheetLabour,0) -  ((ISNULL(cteFreeIssues.GoodsAmount, 0) + ISNULL(cteCashReceived.GoodsAmount,0)) - ((ISNULL(ctePurchases.CateringTotal,0) + ISNULL(ctePurchases.VendingTotal,0) + ISNULL(cteSundryPurchases.Total, 0) + ISNULL(cteSundryCosts.SundryNetTotal,0) + ISNULL(ctePurchases.CansConfTotal, 0)) + (ISNULL(cteUnitPeriodOpeningStock.UStkOpening,0) - ISNULL(cteUnitPeriodClosingStock.UStkClosing,0) ))) + ISNULL(Accounts.AcUnitSoftwareCharge,0)
    ELSE ISNULL(UnitLabourFigures.TotalBacksheetLabour,0) - ((ISNULL(cteFreeIssues.GoodsAmount, 0) + ISNULL(cteCashReceived.GoodsAmount,0)) - ((ISNULL(ctePurchases.CateringTotal,0) + ISNULL(ctePurchases.VendingTotal,0) + ISNULL(cteSundryPurchases.Total, 0) + ISNULL(cteSundryCosts.SundryNetTotal,0) + ISNULL(ctePurchases.CansConfTotal, 0) - ISNULL(cteExternalFreeIssues.GoodsAmount,0)) + (ISNULL(cteUnitPeriodOpeningStock.UStkOpening,0) - ISNULL(cteUnitPeriodClosingStock.UStkClosing,0) ))) + ISNULL(Accounts.AcUnitSoftwareCharge,0) END
    AS OperatingCost,

    CASE WHEN Accounts.AcUnitTradingType = 'A' THEN (((ISNULL(ctePurchases.CateringTotal,0) + ISNULL(ctePurchases.VendingTotal,0) + ISNULL(cteSundryPurchases.Total, 0) + ISNULL(cteSundryCosts.SundryNetTotal,0) + ISNULL(ctePurchases.CansConfTotal, 0)) + ISNULL(ctePurchases.VatTotal,0) + ISNULL(cteSundryCosts.SundryVatAmount,0))) - ISNULL(cteExternalFreeIssues.GoodsAmount,0) + ISNULL(UnitLabourFigures.TotalBacksheetLabour,0) - ((ISNULL(cteCashReceived.Total,0))) + ISNULL(Accounts.AcUnitSoftwareCharge,0) + (ISNULL(Accounts.AcUnitSoftwareCharge,0) * dbo.GetStandardVATRate())
    WHEN Accounts.AcUnitTradingType = 'B' THEN (((ISNULL(cteCashReceived.GoodsAmount,0)) - ((ISNULL(ctePurchases.CateringTotal,0) + ISNULL(ctePurchases.VendingTotal,0) + ISNULL(cteSundryPurchases.Total, 0) + ISNULL(cteSundryCosts.SundryNetTotal,0) + ISNULL(ctePurchases.CansConfTotal, 0)))) + ISNULL(UnitLabourFigures.TotalBacksheetLabour,0))
    WHEN Accounts.AcUnitTradingType = 'C' THEN ISNULL(UnitLabourFigures.TotalBacksheetLabour,0) - ((ISNULL(cteFreeIssues.GoodsAmount, 0) + ISNULL(cteCashReceived.GoodsAmount,0)) - ((ISNULL(ctePurchases.CateringTotal,0) + ISNULL(ctePurchases.VendingTotal,0) + ISNULL(cteSundryPurchases.Total, 0) + ISNULL(cteSundryCosts.SundryNetTotal,0) + ISNULL(ctePurchases.CansConfTotal, 0)) + (ISNULL(cteUnitPeriodOpeningStock.UStkOpening,0) - ISNULL(cteUnitPeriodClosingStock.UStkClosing,0) ))) + ISNULL(Accounts.AcUnitSoftwareCharge,0)
    WHEN Accounts.AcUnitTradingType = 'D' THEN ((ISNULL(ctePurchases.CateringTotal,0) + ISNULL(ctePurchases.VendingTotal,0) + ISNULL(cteSundryPurchases.Total, 0) + ISNULL(cteSundryCosts.SundryNetTotal,0) + ISNULL(ctePurchases.CansConfTotal, 0)) - ISNULL(cteCashReceived.GoodsAmount,0)) + ISNULL(UnitLabourFigures.TotalBacksheetLabour,0) + ISNULL(Accounts.AcUnitSoftwareCharge,0)
    ELSE 0 END AS InvoiceCharge,

    --Fixed GP Shortfall Calculation
    CASE WHEN Accounts.AcUnitIsFixedGP = 1 THEN
        CASE WHEN Accounts.AcUnitUseOverallFixedGP = 1 THEN --Overall Fixed GP

            (ISNULL(cteCashReceived.GoodsAmount,0) * (ISNULL(Accounts.AcUnitOverallFixedGPValue,100) / 100)) --Fixed GP
            -
            (
                ISNULL(cteCashReceived.GoodsAmount,0) --Cash Sales Net
                -
                (
                    (
                        ISNULL(ctePurchases.CateringTotal,0) + ISNULL(ctePurchases.VendingTotal,0) + ISNULL(ctePurchases.CansConfTotal,0)
                        + ISNULL(cteUnitPeriodOpeningStock.OpeningCateringStock,0) + ISNULL(cteUnitPeriodOpeningStock.OpeningBeverageStock,0) + ISNULL(cteUnitPeriodOpeningStock.OpeningCansConfStock,0)
                        - ISNULL(cteUnitPeriodClosingStock.ClosingCateringStock,0) - ISNULL(cteUnitPeriodClosingStock.ClosingBeverageStock,0) - ISNULL(cteUnitPeriodClosingStock.ClosingCansConfStock,0)
                    ) --Consumption No Sundries
                    -
                    (ISNULL(cteFreeIssues.GoodsAmount,0) - ISNULL(cteFreeIssues.LabourGoodsAmount,0)) --Free Issues Less Labour
                ) --ActualConsumption
            ) --Actual GP

        ELSE --Catering/Vending/CansConf Fixed GP

            --Catering
            CASE WHEN Accounts.AcUnitCateringFixedGPValue IS NOT NULL AND Accounts.AcUnitCateringFixedGPValue > 0 THEN
                (
                    (ISNULL(cteCashReceived.CateringGoodsAmount,0) + ISNULL(cteCashReceived.VATFreeGoodsAmount,0)) --Catering cash sales plus zero vat cash sales
                    *
                    (ISNULL(Accounts.AcUnitCateringFixedGPValue,100) / 100) --Catering fixed GP %
                ) --Fixed GP
                -
                (
                    (ISNULL(cteCashReceived.CateringGoodsAmount,0) + ISNULL(cteCashReceived.VATFreeGoodsAmount,0))--Catering cash sales plus zero vat cash sales
                    -
                    (
                        ISNULL(ctePurchases.CateringTotal,0) + ISNULL(cteUnitPeriodOpeningStock.OpeningCateringStock,0) - ISNULL(cteUnitPeriodClosingStock.ClosingCateringStock,0) --Catering consumption
                        -
                        ISNULL(cteFreeIssues.CateringGoodsAmount,0) --Catering free issues
                    ) --Actual catering consumption
                ) --Actual GP

            ELSE 0 END
            +
            --Vending
            CASE WHEN Accounts.AcUnitVendingFixedGPValue IS NOT NULL AND Accounts.AcUnitVendingFixedGPValue > 0 THEN
                (
                    ISNULL(cteCashReceived.VendingGoodsAmount,0) --Vending cash sales
                    *
                    (ISNULL(Accounts.AcUnitVendingFixedGPValue,100) / 100) --Vending fixed GP %
                ) --Fixed GP
                -
                (
                    ISNULL(cteCashReceived.VendingGoodsAmount,0) --Vending cash sales
                    -
                    (
                        ISNULL(ctePurchases.VendingTotal,0) + ISNULL(cteUnitPeriodOpeningStock.OpeningBeverageStock,0) - ISNULL(cteUnitPeriodClosingStock.ClosingBeverageStock,0) --Vending consumption
                        -
                        ISNULL(cteFreeIssues.VendingGoodsAmount,0) --Vending free issues
                    ) --Actual Vending consumption
                ) --Actual GP
            ELSE 0 END
            +
            --CansConf
            CASE WHEN Accounts.AcUnitCansConfFixedGPValue IS NOT NULL AND Accounts.AcUnitCansConfFixedGPValue > 0 THEN
                (
                    ISNULL(cteCashReceived.CansConfGoodsAmount,0) --CansConf cash sales
                    *
                    (ISNULL(Accounts.AcUnitCansConfFixedGPValue,100) / 100) --CansConf fixed GP %
                ) --Fixed GP
                -
                (
                    ISNULL(cteCashReceived.CansConfGoodsAmount,0) --CansConf cash sales
                    -
                    (
                        ISNULL(ctePurchases.CansConfTotal,0) + ISNULL(cteUnitPeriodOpeningStock.OpeningCansConfStock,0) - ISNULL(cteUnitPeriodClosingStock.ClosingCansConfStock,0) --CansConf consumption
                        -
                        ISNULL(cteFreeIssues.CansConfGoodsAmount,0) --CansConf free issues
                    ) --Actual CansConf consumption
                ) --Actual GP
            ELSE 0 END
        END
    ELSE 0 END AS FixedGPShortfall,

    ISNULL(cteAdditionalProfit.AdditionalProfitNetTotal,0) AS AdditionalProfitNetTotal, ISNULL(cteExternalFreeIssues.GoodsAmount, 0) AS ExternalFreeIssue,
    CASE WHEN Accounts.AcUnitTradingType = 'B' THEN (CASE WHEN AcUnitNextAnnualFeeDate IS NULL THEN AcUnitAnnualFee WHEN AcUnitNextAnnualFeeDate > Period.EndDate THEN AcUnitAnnualFee ELSE AcUnitNextAnnualFee END) / 12
    ELSE
        CASE WHEN AcUnitManagementFeePeriod = 'W' THEN ((CASE WHEN AcUnitNextManagementFeeDate IS NULL THEN AcUnitManagementFee WHEN AcUnitNextManagementFeeDate > Period.EndDate THEN AcUnitManagementFee ELSE AcUnitNextManagementFee END) / 52) * WeekCount
        ELSE ((CASE WHEN AcUnitNextManagementFeeDate IS NULL THEN AcUnitManagementFee WHEN AcUnitNextManagementFeeDate > Period.EndDate THEN AcUnitManagementFee ELSE AcUnitNextManagementFee END) / 12)
        END
    END AS MonthlyFees,

    AcUnitStockTarget,
    AcUnitCashPaidTarget,
    AcUnitCashHeldTarget,
    AcUnitAgencyLabourTarget,
    AcUnitAgencyDebtorsTarget,
    WeekCount,
    ISNULL(SUM(ASubCashCarriedForward),0) / WeekCount AS AvgCashCarriedForwrd,
    ISNULL(TotalCashPaid,0) / WeekCount AS AvgCashPaid,
    Accounts.AcProject AS AreaManager,
    AcUnitSoftwareCharge
    FROM AccountDataSubmission
    INNER JOIN [SOP].Accounts on Accounts.AcUniqueId = AccountDataSubmission.ASubAccountID
    INNER JOIN Week on Week.WeekNo = AccountDataSubmission.ASubWeekNo AND Week.WeekYear = AccountDataSubmission.ASubYearNo
    INNER JOIN Period on WEEK.WeekEndDate BETWEEN Period.StartDate AND Period.EndDate
    INNER JOIN CompParam on CompParam.CmpDefault = 1
    LEFT OUTER JOIN ctePeriodWeekCount ON ctePeriodWeekCount.Period = PrdNo AND ctePeriodWeekCount.Year = PrdYear
    LEFT OUTER JOIN cteDiscount on cteDiscount.PtUnitId = Accounts.AcUniqueID AND cteDiscount.PtPeriod = Period.PrdNo AND cteDiscount.PtYear = Period.PrdYear
    LEFT OUTER JOIN cteLabourFigures on cteLabourFigures.LabPeriod = Period.PrdNo AND cteLabourFigures.LabYear = Period.PrdYear AND ASubAccountId = LabUnitId
    LEFT OUTER JOIN cteMobileRelief on cteMobileRelief.McDtUnitId = ASubAccountId AND cteMobileRelief.McHdPeriod = Period.PrdNo AND cteMobileRelief.McHdYear = Period.PrdYear
    LEFT OUTER JOIN ctePurchases on ctePurchases.PtUnitId = AcUniqueID AND ctePurchases.PtPeriod = PrdNo AND ctePurchases.PtYear = Period.PrdYear
    LEFT OUTER JOIN cteSundryPurchases on cteSundryPurchases.PtUnitId = AcUniqueID AND cteSundryPurchases.PtPeriod = PrdNo AND cteSundryPurchases.PtYear = Period.PrdYear
    LEFT OUTER JOIN cteInvoices on cteInvoices.AccountID = ASuBAccountId AND cteInvoices.PrdNo = Period.PrdNo AND Period.PrdYear = cteInvoices.PrdYear
    LEFT OUTER JOIN cteSundryCosts on cteSundryCosts.ScDtAccountId = ASubAccountId AND cteSundryCosts.ScDTPeriodNo = Period.PrdNo AND cteSundryCosts.ScDtYearNo = Period.PrdYear
    LEFT OUTER JOIN cteCashReceived on cteCashReceived.CtUnitId = ASubAccountId AND cteCashReceived.CtPeriod = Period.PrdNo AND cteCashReceived.CtYear = Period.PrdYear
    LEFT OUTER JOIN cteFreeIssues on cteFreeIssues.CtUnitId = ASubAccountId AND cteFreeIssues.CtPeriod = Period.PrdNo AND cteFreeIssues.CtYear = Period.PrdYear
    LEFT OUTER JOIN cteExternalFreeIssues on cteExternalFreeIssues.CtUnitId = ASubAccountId AND cteExternalFreeIssues.CtPeriod = Period.PrdNo AND cteExternalFreeIssues.CtYear = Period.PrdYear
    LEFT OUTER JOIN cteAgencyLabour on cteAgencyLabour.PtUnitId = ASubAccountId AND cteAgencyLabour.PtPeriod = Period.PrdNo AND cteAgencyLabour.PtYear = Period.PrdYear
    LEFT OUTER JOIN cteAdditionalProfit on cteAdditionalProfit.ScDtAccountId = ASubAccountId AND cteAdditionalProfit.ScDTPeriodNo = Period.PrdNo AND cteAdditionalProfit.ScDtYearNo = Period.PrdYear
    LEFT OUTER JOIN cteUnitPeriodOpeningStock on cteUnitPeriodOpeningStock.UStkUnitId = ASubAccountId AND cteUnitPeriodOpeningStock.UStkPeriod = Period.PrdNo AND cteUnitPeriodOpeningStock.UStkYearNo = Period.PrdYear
    LEFT OUTER JOIN cteUnitPeriodClosingStock on cteUnitPeriodClosingStock.UStkUnitId = ASubAccountId AND cteUnitPeriodClosingStock.UStkPeriod = Period.PrdNo AND cteUnitPeriodClosingStock.UStkYearNo = Period.PrdYear
    LEFT OUTER JOIN UnitLabourFigures on AcUniqueID = UnitId AND Period.PrdNo = UnitLabourFigures.Period AND Period.PrdYear = UnitLabourFigures.Year
    GROUP BY AccountDataSubmission.ASubAccountId, AcRef, AcName, Period.PrdNo, StartDate, EndDate, Period.PrdYear, CASE WHEN ASubSubmittedBy IS NULL THEN 0 ELSE 1 END,
    AcUnitTrainingLevy, cteMobileRelief.MobileReliefTotal, cteInvoices.TotalInvoices, ctePurchases.PurchaseTotal, cteSundryCosts.SundryTotal,
    cteLabourFigures.PayRollLabour, cteCashReceived.Total, cteCashReceived.VatTotal, cteInvoices.TotalInvoicesVat, cteUnitPeriodOpeningStock.UStkOpening,
    cteUnitPeriodClosingStock.UStkClosing, cteFreeIssues.GoodsAmount, Accounts.AcUnitTradingType, cteLabourFigures.OtherLabour,
    cteDiscount.DiscountTotal, ctePurchases.PurchaseNetTotal, cteCashReceived.GoodsAmount, ctePurchases.CateringTotal, ctePurchases.VendingTotal,
    cteSundryPurchases.Total, cteSundryCosts.SundryNetTotal, ctePurchases.CansConfTotal, cteAgencyLabour.Total, ctePurchases.VatTotal, cteSundryCosts.SundryVatAmount,
    cteAdditionalProfit.AdditionalProfitNetTotal, ctePurchases.CateringVatAmount, ctePurchases.VendingVatAmount, cteSundryPurchases.VatAmount,
    ctePurchases.CansConfVatAmount, ctePurchases.VatTotal, ISNULL(cteExternalFreeIssues.GoodsAmount,0),
    AcUnitNextAnnualFee, AcUnitNextAnnualFeeDate, AcUnitAnnualFee, AcUnitManagementFeePeriod, AcUnitNextManagementFee, AcUnitNextManagementFeeDate,
    AcUnitManagementFee, ctePeriodWeekCount.WeekCount, CompParam.CmpApprenticeshipLevyPercent, UnitLabourFigures.TotalBacksheetLabour,
    AcUnitStockTarget, ctePurchases.TotalCashPaid, AcUnitCashHeldTarget, AcUnitAgencyLabourTarget, AgencyLabour, AcUnitAgencyDebtorsTarget,
    AcUnitCashPaidTarget, Accounts.AcProject, AcUnitSoftwareCharge,Accounts.AcUnitIsFixedGP, Accounts.AcUnitUseOverallFixedGP, Accounts.AcUnitOverallFixedGPValue,
    cteCashReceived.VATFreeGoodsAmount, cteCashReceived.CateringGoodsAmount, cteCashReceived.VendingGoodsAmount, cteCashReceived.CansConfGoodsAmount,
    cteFreeIssues.CateringGoodsAmount, cteFreeIssues.VendingGoodsAmount, cteFreeIssues.CansConfGoodsAmount, cteFreeIssues.LabourGoodsAmount,
    cteUnitPeriodOpeningStock.OpeningCateringStock, cteUnitPeriodOpeningStock.OpeningBeverageStock, cteUnitPeriodOpeningStock.OpeningCansConfStock,
    cteUnitPeriodClosingStock.ClosingCateringStock, cteUnitPeriodClosingStock.ClosingBeverageStock, cteUnitPeriodClosingStock.ClosingCansConfStock,
    Accounts.AcUnitCateringFixedGPValue, Accounts.AcUnitVendingFixedGPValue, Accounts.AcUnitCansConfFixedGPValue;