AcumaticaSQLQueriesAndFunctions/ManagerTransactionNeeded.sql

IF OBJECT_ID ('dbo.ManagerTransactionNeeded','V') IS NOT NULL DROP VIEW dbo.ManagerTransactionNeeded
GO 
CREATE VIEW dbo.ManagerTransactionNeeded
AS
/* ───────── existing CTEs (unchanged) ───────── */
WITH CurrentCodeAmounts AS (
SELECT  t.CompanyID,
            t.ProjectID,
            SUM(t.BillableQty) AS BillableQty
    FROM    PMTran t
    JOIN    InventoryItem i
           ON i.CompanyID = t.CompanyID
          AND i.InventoryID = t.InventoryID
    WHERE   i.InventoryCD = 'L023'
    GROUP BY t.CompanyID, t.ProjectID     
),
RateTable AS (           -- one row per $‑range
    SELECT 1 AS ref,     0  AS MinAmt,     1000 AS MaxAmt,  1.0 AS Qty, 90 AS Rate UNION ALL
    SELECT 2,         1001,     2500,  1.5, 90 UNION ALL
    SELECT 3,         2501,     5000,  4.5, 90 UNION ALL
    SELECT 4,         5001,     7500,  4.5, 90 UNION ALL
    SELECT 5,         7501,    10000,  7.5, 90 UNION ALL
    SELECT 6,        10001,    25000,  9.0, 90 UNION ALL
    SELECT 7,        25001,    50000, 10.5, 90 UNION ALL
    SELECT 8,        50001, 99999999999, 10.5, 90
),
CurrentRevisedAmounts AS (
    SELECT  b.CompanyID,
            b.ProjectID,
            SUM(b.CuryRevisedAmount) AS RevisedAmount
    FROM    PMBudget b
    join PMAccountGroup a on b.companyid = a.CompanyID and b.AccountGroupID = a.GroupID
    where a.GroupCD = 'REVENUE'
    GROUP BY b.CompanyID, b.ProjectID
),
/* ───────── new CTEs ───────── */
TargetQty AS (
    SELECT  cra.CompanyID,
            cra.ProjectID,
            cra.RevisedAmount,
            rt.MinAmt,
            rt.MaxAmt,
            rt.Qty         AS TargetQty,
            rt.Rate
    FROM    CurrentRevisedAmounts cra
    JOIN    RateTable rt
         ON cra.RevisedAmount BETWEEN rt.MinAmt AND rt.MaxAmt
),
QtyAdjustment AS (
    SELECT  tq.CompanyID,
            tq.ProjectID,
            tq.RevisedAmount,
            tq.MinAmt,
            tq.MaxAmt,
            COALESCE(ca.BillableQty,0) AS CurrentBillableQty,
            tq.TargetQty,
            tq.Rate,
            CAST(tq.TargetQty - COALESCE(ca.BillableQty,0) AS DECIMAL(18,2)) AS DeltaQty
    FROM    TargetQty tq
    LEFT JOIN CurrentCodeAmounts ca
           ON ca.CompanyID = tq.CompanyID
          AND ca.ProjectID = tq.ProjectID
)
/* ───────── final output ───────── */
SELECT  c.CompanyID,
        c.ContractID,
        c.ContractCD,
        qa.RevisedAmount                             AS RevisedAmount,      -- total revised amount
        qa.MinAmt, qa.MaxAmt,                        -- bracket limits
        qa.CurrentBillableQty,
        qa.CurrentBillableQty * qa.Rate              AS CurrentValue,  -- $ at rate
        qa.TargetQty,
        qa.TargetQty        * qa.Rate                AS TargetValue,
        qa.DeltaQty,
        qa.DeltaQty         * qa.Rate                AS DeltaValue     -- what to add/credit
FROM    QtyAdjustment qa
JOIN    Contract c
      ON c.CompanyID  = qa.CompanyID
     AND c.ContractID = qa.ProjectID