AcumaticaSQLQueriesAndFunctions/projectmargin.sql

SELECT
    -- Base identifiers
    c.CompanyID,
    c.ContractID,
    c.ContractCD,
    c.NoteID,
    -- Customer (single alias only; avoids duplicate joins)
    cust.BAccountID                     AS CustomerID,
    cust.AcctCD                         AS CustomerCD,
    cust.AcctName                       AS CustomerName,
    -- Branch details
    br.BranchID                         AS BranchKey,
    br.BranchCD                         AS BranchCD,
    ba.AcctName                         AS BranchName,
    -- Contract attributes (single-pass KV collapse)
    ckv.EstimatedComplete,
    ckv.CustomerPO,
    ckv.EventName,
    -- Budget / actuals (set-based rollups)
    COALESCE(rb.RevisedBudgetedIncome,  0) AS RevisedBudgetedIncome,
    COALESCE(rb.RevisedBudgetedExpense, 0) AS RevisedBudgetedExpense,
    COALESCE(tx.TotalExpenses,          0) AS TotalExpenses,
    -- Project metadata
    c.Status                            AS ProjectStatus,
    c.CreatedDateTime                   AS ProjectCreatedDateTime,
    c.LastModifiedDateTime              AS ProjectLastModifiedDateTime,
    -- Project executive (resolved via KV once; no separate execNote scan)
    bAExec.AcctCD                       AS ExecutiveNumber,
    bAExec.AcctName                     AS ExecutiveName,
    ebranch.AcctCD                      AS ParentCategory,
    execEmp.UserID                      AS ExecutivePKID,
    -- Last modified by
    lmu.PKID                            AS LastModifiedByKey,
    lmu.Username                        AS LastModifiedUserName,
    lmu.FullName                        AS LastModifiedName,
    -- Period / notes
    gls.FinPeriodID,
    clna.Value                          AS FinanceNoteOnGMLoss,
    mlna.Value                         AS MarginLossAmount,
    -- Customer location & region
    cl.LocationCD                       AS CustomerServiceLocation,
    ca.City,
    ca.State,
    -- Billing and customer-type
    c.BillingID,
    bakv.CustomerType,
    lkv.Region,
    /* ========= NEW: Per-account GL revenue/expense sums (pre-aggregated, then joined) =========
       - Single scan over GLTran grouped by (CompanyID, ProjectID, FinPeriodID).
       - SUM(CASE...) produces wide, analytic columns without repeating logic in the outer SELECT.
       - Keeps the outer query at the same grain; avoids adding GROUP BY to the main SELECT.
    ============================================================================================ */
    COALESCE(gls.BilledRevenue4001,      0) AS BilledRevenue4001,       -- 4001
    COALESCE(gls.TMUnbilledRevenue4002,  0) AS TMUnbilledRevenue4002,   -- 4002
    COALESCE(gls.LumpSumWIPRevenue4000,  0) AS LumpSumWIPRevenue4000,   -- 4000
    COALESCE(gls.BillingsLumpSum4003,    0) AS BillingsLumpSum4003,     -- 4003
    COALESCE(gls.TotalExpensesCOGS,      0) AS TotalExpensesCOGS,       -- 5000-5999 (net debit)
    COALESCE(gls.ClientDiscounts4007,    0) AS ClientDiscounts4007,     -- 4007
    COALESCE(gls.OtherIncome4500,        0) AS OtherIncome4500,         -- 4500
    COALESCE(gls.Refunds4502,            0) AS Refunds4502,             -- 4502
    COALESCE(gls.JCLabor5000,            0) AS JCLabor5000,             -- 5000
    COALESCE(gls.JCLaborBurden5001,      0) AS JCLaborBurden5001,       -- 5001
    COALESCE(gls.JCSubcontractor5010,    0) AS JCSubcontractor5010,     -- 5010
    COALESCE(gls.JCMaterials5020,        0) AS JCMaterials5020,         -- 5020
    COALESCE(gls.JCEquipmentInternal5030,0) AS JCEquipmentInternal5030, -- 5030
    COALESCE(gls.JCEquipmentExternal5040,0) AS JCEquipmentExternal5040, -- 5040
    COALESCE(gls.JCConsultant5041,       0) AS JCConsultant5041,        -- 5041
    COALESCE(gls.JCTravel5050,           0) AS JCTravel5050,            -- 5050
    COALESCE(gls.JCOther5055,            0) AS JCOther5055,             -- 5055
    COALESCE(gls.JCNonAllocating5056,    0) AS JCNonAllocating5056,     -- 5056
    COALESCE(gls.WIPCOGS5099,            0) AS WIPCOGS5099,             -- 5099
    COALESCE(gls.ShopSuppliesIndirect5100,0) AS ShopSuppliesIndirect5100, -- 5100
    COALESCE(gls.ShopExpensesSmallTools5101,0) AS ShopExpensesSmallTools5101, -- 5101
    COALESCE(gls.ShopExpenseUniforms5102,0) AS ShopExpenseUniforms5102, -- 5102
    COALESCE(gls.ShopIndirectLabor5103,  0) AS ShopIndirectLabor5103,   -- 5103
    COALESCE(gls.DirectLabor5104,        0) AS DirectLabor5104,         -- 5104
    COALESCE(gls.IndirectQuotingCosts5105,0) AS IndirectQuotingCosts5105, -- 5105
    COALESCE(gls.PassThroughCost5108,    0) AS PassThroughCost5108,     -- 5108
    COALESCE(gls.PurchasePriceVariance5109,0) AS PurchasePriceVariance5109, -- 5109
    COALESCE(gls.WarrantyWork5110,       0) AS WarrantyWork5110,        -- 5110
    COALESCE(gls.EstSubcontractorCosts5111,0) AS EstSubcontractorCosts5111, -- 5111
    COALESCE(gls.CatTravelExpenses5115,  0) AS CatTravelExpenses5115,   -- 5115
    COALESCE(gls.DiscountsGiven5201,     0) AS DiscountsGiven5201,      -- 5201
    COALESCE(gls.DiscountsTaken5202,     0) AS DiscountsTaken5202,      -- 5202
    COALESCE(gls.ClientBillingExpense5210,0) AS ClientBillingExpense5210, -- 5210
    COALESCE(gls.BadDebt5300,            0) AS BadDebt5300,             -- 5300
    COALESCE(gls.TruckStock5057,         0) AS TruckStock5057,          -- 5057
    COALESCE(gls.IndirectCOGSFleetExpenses5107,0) AS IndirectCOGSFleetExpenses5107,-- 5107
    COALESCE(gls.IndirectLaborBurden5106,0) AS IndirectLaborBurden5106, -- 5106
        ( COALESCE(gls.JCLabor5000,0)
    + COALESCE(gls.JCLaborBurden5001,0)
    + COALESCE(gls.JCSubcontractor5010,0)
    + COALESCE(gls.JCMaterials5020,0)
    + COALESCE(gls.JCEquipmentExternal5040,0)
    + COALESCE(gls.JCOther5055,0)
    + COALESCE(gls.DirectLabor5104,0)
    + COALESCE(gls.DiscountsTaken5202,0)
    + COALESCE(gls.JCEquipmentInternal5030,0)
    + COALESCE(gls.WIPCOGS5099,0)
    ) AS DirectCOGSSelected,
    ( COALESCE(gls.ShopSuppliesIndirect5100,0)
    + COALESCE(gls.ShopExpensesSmallTools5101,0)
    + COALESCE(gls.ShopExpenseUniforms5102,0)
    + COALESCE(gls.ShopIndirectLabor5103,0)
    + COALESCE(gls.IndirectQuotingCosts5105,0)
    + COALESCE(gls.PassThroughCost5108,0)
    + COALESCE(gls.PurchasePriceVariance5109,0)
    + COALESCE(gls.EstSubcontractorCosts5111,0)
    + COALESCE(gls.ClientBillingExpense5210,0)
    + COALESCE(gls.IndirectLaborBurden5106,0)
    + COALESCE(gls.IndirectCOGSFleetExpenses5107,0)
    + COALESCE(gls.TruckStock5057,0)
    + COALESCE(gls.WarrantyWork5110,0)
    ) AS IndirectCOGSSelected
FROM Contract c
LEFT JOIN BAccount cust
  ON cust.CompanyID  = c.CompanyID
 AND cust.BAccountID = c.CustomerID
LEFT JOIN Branch br
  ON br.CompanyID = c.CompanyID
 AND br.BranchID  = c.DefaultBranchID
LEFT JOIN BAccount ba
  ON ba.CompanyID   = br.CompanyID
 AND ba.BAccountID  = br.BAccountID  -- corrected from BACCountID
/* ===== Contract attributes (one scan, many columns) =======================
   Single pass over ContractKvExt; MAX(CASE...) safely collapses duplicates.
   Eliminates multiple separate KV joins and improves SARGability.
=========================================================================== */
LEFT JOIN (
    SELECT
        CompanyID,
        RecordID,
        MAX(CASE WHEN FieldName = 'AttributeESTCOMPL'    THEN ValueDate END) AS EstimatedComplete,
        MAX(CASE WHEN FieldName = 'AttributePO'          THEN ValueString END) AS CustomerPO,
        MAX(CASE WHEN FieldName = 'AttributeEVENT'       THEN ValueString END) AS EventName,
        MAX(CASE WHEN FieldName = 'AttributePROJECTEXE'  THEN ValueString END) AS ProjectExecutiveAcctCD
    FROM ContractKvExt
    WHERE FieldName IN ('AttributeESTCOMPL','AttributePO','AttributeEVENT','AttributePROJECTEXE')
    GROUP BY CompanyID, RecordID
) AS ckv
  ON ckv.CompanyID = c.CompanyID
 AND ckv.RecordID  = c.NoteID
/*  Executive resolution via KV value (AcctCD) */
LEFT JOIN BAccount bAExec
  ON bAExec.CompanyID = c.CompanyID
 AND bAExec.AcctCD    = ckv.ProjectExecutiveAcctCD
LEFT JOIN EPEmployee execEmp
  ON execEmp.CompanyID  = bAExec.CompanyID
 AND execEmp.BAccountID = bAExec.BAccountID
LEFT JOIN BAccount ebranch
  ON ebranch.CompanyID  = bAExec.CompanyID
 AND ebranch.BAccountID = bAExec.ParentBAccountID
/*  Last modified user (dimension) */
LEFT JOIN Users lmu
  ON lmu.CompanyID = c.CompanyID
 AND lmu.PKID      = c.LastModifiedByID
/*  GM loss notes */
LEFT JOIN CSAnswers clna
  ON clna.CompanyID  = c.CompanyID
 AND clna.RefNoteID  = c.NoteID
 AND clna.AttributeID = 'GMLOSSNOTE'
LEFT JOIN CSAnswers mlna
  ON mlna.CompanyID  = c.CompanyID
 AND mlna.RefNoteID  = c.NoteID
 AND mlna.AttributeID = 'GMLOSS'
/*  Customer service location & address */
LEFT JOIN Location cl
  ON cl.CompanyID  = c.CompanyID
 AND cl.LocationID = c.LocationID
LEFT JOIN Address ca
  ON ca.CompanyID = cl.CompanyID
 AND ca.AddressID = cl.DefAddressID
/*  ===== Location attributes (Region) in one pass ========================= */
LEFT JOIN (
    SELECT
        CompanyID, RecordID,
        MAX(CASE WHEN FieldName = 'AttributeREGION' THEN ValueString END) AS Region
    FROM LocationKvExt
    WHERE FieldName IN ('AttributeREGION')
    GROUP BY CompanyID, RecordID
) AS lkv
  ON lkv.CompanyID = cl.CompanyID
 AND lkv.RecordID  = cl.NoteID
/*  ===== Customer attributes (Type) in one pass =========================== */
LEFT JOIN (
    SELECT
        CompanyID, RecordID,
        MAX(CASE WHEN FieldName = 'AttributeCTYPE' THEN ValueString END) AS CustomerType
    FROM BAccountKvExt
    WHERE FieldName IN ('AttributeCTYPE')
    GROUP BY CompanyID, RecordID
) AS bakv
  ON bakv.CompanyID = cust.CompanyID
 AND bakv.RecordID  = cust.NoteID
/*  ===== Budget rollup (set-based) ======================================= */
LEFT JOIN (
    SELECT
        CompanyID,
        ProjectID,
        SUM(CASE WHEN [Type] = 'I' THEN RevisedAmount ELSE 0 END) AS RevisedBudgetedIncome,
        SUM(CASE WHEN [Type] = 'E' THEN RevisedAmount ELSE 0 END) AS RevisedBudgetedExpense
    FROM PMBudget
    GROUP BY CompanyID, ProjectID
) AS rb
  ON rb.CompanyID = c.CompanyID
 AND rb.ProjectID = c.ContractID
/*  ===== Expense rollup (set-based) ====================================== */
LEFT JOIN (
    SELECT
        p.CompanyID,
        p.ProjectID,
        SUM(p.Amount) AS TotalExpenses
    FROM PMTran p
    JOIN PMAccountGroup pg
      ON pg.CompanyID = p.CompanyID
     AND pg.GroupID   = p.AccountGroupID
    WHERE pg.IsExpense = 1
      AND p.Released  = 1
      AND p.Allocated = 0
    GROUP BY p.CompanyID, p.ProjectID
) AS tx
  ON tx.CompanyID = c.CompanyID
 AND tx.ProjectID = c.ContractID
/*  ===== NEW: GL per-account sums (set-based pre-aggregation) =============
    - Grouped by (CompanyID, ProjectID, FinPeriodID) so numbers align with the period in the SELECT.
    - Applies the same account filter as the outer WHERE for consistency.
    - Keeps outer query ungrouped; avoids row explosion and repeated CASE expressions.
=========================================================================== */
LEFT JOIN (
    SELECT
        gt.CompanyID,
        gt.ProjectID,
        bb.FinPeriodID,
        SUM(CASE WHEN a.AccountCD = '4001' THEN COALESCE(gt.CreditAmt,0) - COALESCE(gt.DebitAmt,0) ELSE 0 END) AS BilledRevenue4001,
        SUM(CASE WHEN a.AccountCD = '4002' THEN COALESCE(gt.CreditAmt,0) - COALESCE(gt.DebitAmt,0) ELSE 0 END) AS TMUnbilledRevenue4002,
        SUM(CASE WHEN a.AccountCD = '4000' THEN COALESCE(gt.CreditAmt,0) - COALESCE(gt.DebitAmt,0) ELSE 0 END) AS LumpSumWIPRevenue4000,
        SUM(CASE WHEN a.AccountCD = '4003' THEN COALESCE(gt.CreditAmt,0) - COALESCE(gt.DebitAmt,0) ELSE 0 END) AS BillingsLumpSum4003,
        SUM(CASE WHEN TRY_CAST(a.AccountCD AS INT) BETWEEN 5000 AND 5999
                 THEN COALESCE(gt.DebitAmt,0) - COALESCE(gt.CreditAmt,0) ELSE 0 END) AS TotalExpensesCOGS,
        SUM(CASE WHEN a.AccountCD = '4007' THEN COALESCE(gt.CreditAmt,0) - COALESCE(gt.DebitAmt,0) ELSE 0 END) AS ClientDiscounts4007,
        SUM(CASE WHEN a.AccountCD = '4500' THEN COALESCE(gt.CreditAmt,0) - COALESCE(gt.DebitAmt,0) ELSE 0 END) AS OtherIncome4500,
        SUM(CASE WHEN a.AccountCD = '4502' THEN COALESCE(gt.CreditAmt,0) - COALESCE(gt.DebitAmt,0) ELSE 0 END) AS Refunds4502,
        SUM(CASE WHEN a.AccountCD = '5000' THEN COALESCE(gt.DebitAmt,0) - COALESCE(gt.CreditAmt,0) ELSE 0 END) AS JCLabor5000,
        SUM(CASE WHEN a.AccountCD = '5001' THEN COALESCE(gt.DebitAmt,0) - COALESCE(gt.CreditAmt,0) ELSE 0 END) AS JCLaborBurden5001,
        SUM(CASE WHEN a.AccountCD = '5010' THEN COALESCE(gt.DebitAmt,0) - COALESCE(gt.CreditAmt,0) ELSE 0 END) AS JCSubcontractor5010,
        SUM(CASE WHEN a.AccountCD = '5020' THEN COALESCE(gt.DebitAmt,0) - COALESCE(gt.CreditAmt,0) ELSE 0 END) AS JCMaterials5020,
        SUM(CASE WHEN a.AccountCD = '5030' THEN COALESCE(gt.DebitAmt,0) - COALESCE(gt.CreditAmt,0) ELSE 0 END) AS JCEquipmentInternal5030,
        SUM(CASE WHEN a.AccountCD = '5040' THEN COALESCE(gt.DebitAmt,0) - COALESCE(gt.CreditAmt,0) ELSE 0 END) AS JCEquipmentExternal5040,
        SUM(CASE WHEN a.AccountCD = '5041' THEN COALESCE(gt.DebitAmt,0) - COALESCE(gt.CreditAmt,0) ELSE 0 END) AS JCConsultant5041,
        SUM(CASE WHEN a.AccountCD = '5050' THEN COALESCE(gt.DebitAmt,0) - COALESCE(gt.CreditAmt,0) ELSE 0 END) AS JCTravel5050,
        SUM(CASE WHEN a.AccountCD = '5055' THEN COALESCE(gt.DebitAmt,0) - COALESCE(gt.CreditAmt,0) ELSE 0 END) AS JCOther5055,
        SUM(CASE WHEN a.AccountCD = '5056' THEN COALESCE(gt.DebitAmt,0) - COALESCE(gt.CreditAmt,0) ELSE 0 END) AS JCNonAllocating5056,
        SUM(CASE WHEN a.AccountCD = '5099' THEN COALESCE(gt.DebitAmt,0) - COALESCE(gt.CreditAmt,0) ELSE 0 END) AS WIPCOGS5099,
        SUM(CASE WHEN a.AccountCD = '5100' THEN COALESCE(gt.DebitAmt,0) - COALESCE(gt.CreditAmt,0) ELSE 0 END) AS ShopSuppliesIndirect5100,
        SUM(CASE WHEN a.AccountCD = '5101' THEN COALESCE(gt.DebitAmt,0) - COALESCE(gt.CreditAmt,0) ELSE 0 END) AS ShopExpensesSmallTools5101,
        SUM(CASE WHEN a.AccountCD = '5102' THEN COALESCE(gt.DebitAmt,0) - COALESCE(gt.CreditAmt,0) ELSE 0 END) AS ShopExpenseUniforms5102,
        SUM(CASE WHEN a.AccountCD = '5103' THEN COALESCE(gt.DebitAmt,0) - COALESCE(gt.CreditAmt,0) ELSE 0 END) AS ShopIndirectLabor5103,
        SUM(CASE WHEN a.AccountCD = '5104' THEN COALESCE(gt.DebitAmt,0) - COALESCE(gt.CreditAmt,0) ELSE 0 END) AS DirectLabor5104,
        SUM(CASE WHEN a.AccountCD = '5105' THEN COALESCE(gt.DebitAmt,0) - COALESCE(gt.CreditAmt,0) ELSE 0 END) AS IndirectQuotingCosts5105,
        SUM(CASE WHEN a.AccountCD = '5108' THEN COALESCE(gt.DebitAmt,0) - COALESCE(gt.CreditAmt,0) ELSE 0 END) AS PassThroughCost5108,
        SUM(CASE WHEN a.AccountCD = '5109' THEN COALESCE(gt.DebitAmt,0) - COALESCE(gt.CreditAmt,0) ELSE 0 END) AS PurchasePriceVariance5109,
        SUM(CASE WHEN a.AccountCD = '5110' THEN COALESCE(gt.DebitAmt,0) - COALESCE(gt.CreditAmt,0) ELSE 0 END) AS WarrantyWork5110,
        SUM(CASE WHEN a.AccountCD = '5111' THEN COALESCE(gt.DebitAmt,0) - COALESCE(gt.CreditAmt,0) ELSE 0 END) AS EstSubcontractorCosts5111,
        SUM(CASE WHEN a.AccountCD = '5115' THEN COALESCE(gt.DebitAmt,0) - COALESCE(gt.CreditAmt,0) ELSE 0 END) AS CatTravelExpenses5115,
        SUM(CASE WHEN a.AccountCD = '5201' THEN COALESCE(gt.DebitAmt,0) - COALESCE(gt.CreditAmt,0) ELSE 0 END) AS DiscountsGiven5201,
        SUM(CASE WHEN a.AccountCD = '5202' THEN COALESCE(gt.DebitAmt,0) - COALESCE(gt.CreditAmt,0) ELSE 0 END) AS DiscountsTaken5202,
        SUM(CASE WHEN a.AccountCD = '5210' THEN COALESCE(gt.DebitAmt,0) - COALESCE(gt.CreditAmt,0) ELSE 0 END) AS ClientBillingExpense5210,
        SUM(CASE WHEN a.AccountCD = '5300' THEN COALESCE(gt.DebitAmt,0) - COALESCE(gt.CreditAmt,0) ELSE 0 END) AS BadDebt5300,
        SUM(CASE WHEN a.AccountCD = '5057' THEN COALESCE(gt.DebitAmt,0) - COALESCE(gt.CreditAmt,0) ELSE 0 END) AS TruckStock5057,
        SUM(CASE WHEN a.AccountCD = '5107' THEN COALESCE(gt.DebitAmt,0) - COALESCE(gt.CreditAmt,0) ELSE 0 END) AS IndirectCOGSFleetExpenses5107,
        SUM(CASE WHEN a.AccountCD = '5106' THEN COALESCE(gt.DebitAmt,0) - COALESCE(gt.CreditAmt,0) ELSE 0 END) AS IndirectLaborBurden5106
    FROM GLTran gt
    JOIN Account a
      ON a.CompanyID  = gt.CompanyID
     AND a.AccountID  = gt.AccountID
    JOIN Batch bb
      ON bb.CompanyID = gt.CompanyID
     AND bb.BatchNbr  = gt.BatchNbr
     AND bb.Module    = gt.Module
    WHERE (a.AccountCD IN ('4001','4002','4000','4003','4007','4500','4502')
           OR TRY_CAST(a.AccountCD AS INT) BETWEEN 5000 AND 5999)
    GROUP BY gt.CompanyID, gt.ProjectID, bb.FinPeriodID
) AS gls
  ON gls.CompanyID  = c.CompanyID
 AND gls.ProjectID  = c.ContractID