AcumaticaSQLQueriesAndFunctions/ProjectDataAccuracyUpdateThree.sql

DROP VIEW IF EXISTS ProjectDataAccuracyUpdateThree
GO
Create View ProjectDataAccuracyUpdateThree
as
SELECT
    c.CompanyID,
    c.ContractID,
    c.ContractCD,
    bAExec.AcctCD AS ExecutiveNumber,
    bAExec.AcctName AS ExecutiveName,
    execEmp.userID AS ExecutivePKID,
    manager.AcctCD AS ManagerNumber,
    manager.AcctName AS ManagerName,
    manEmp.userID AS managerPKID,
        br.BranchID AS BranchKey,
    br.BranchCD AS BranchCD,
    ba.AcctName AS BranchName,
        cust.BAccountID as CustomerID,
    cust.AcctCD as CustomerCD,
    cust.AcctName as CustomerName,
    c.CreatedDateTime,
    dbo.SumReleasedTransactions(c.CompanyID, c.ContractID, 1) as WIPActual ,
    -- Calculate RentalProject via join
    CASE 
        WHEN CHARINDEX('rental', LOWER(d.Description)) > 0 THEN 1 
        ELSE 0 
    END AS RentalProject,
    TRY_CONVERT(DECIMAL(18,2), TRY_CONVERT(MONEY, nte.ValueString)) AS NTE,
    TRY_CONVERT(DECIMAL(18,2), TRY_CONVERT(MONEY, vnte.ValueString)) AS VNTE,
    nte.ValueString AS NTEAsText,
    vnte.ValueString AS VNTEAsText,
    d.description as JobCategory,
    jsd.description as JobStatus,
    -- Aggregated columns from PMBudget
    ISNULL(SUM(CASE WHEN p.[TYPE] = 'E' THEN p.CuryRevisedAmount ELSE 0 END), 0) AS RevisedExpenseBudgetSum,
    ISNULL(SUM(CASE WHEN p.[TYPE] = 'I' THEN p.CuryRevisedAmount ELSE 0 END), 0) AS RevisedIncomeBudgetSum,
    ISNULL(SUM(CASE WHEN p.[TYPE] = 'E' THEN p.CuryActualAmount ELSE 0 END), 0) AS ActualExpenseAmount,
    -- Calculated fields using aggregated values
    CASE WHEN ISNULL(SUM(CASE WHEN p.[TYPE] = 'E' THEN p.CuryRevisedAmount ELSE 0 END), 0) < 5 THEN 1 ELSE 0 END AS RevisedUnderFive,
    CASE WHEN ISNULL(SUM(CASE WHEN p.[TYPE] = 'E' THEN p.CuryActualAmount ELSE 0 END), 0) > ISNULL(SUM(CASE WHEN p.[TYPE] = 'E' THEN p.CuryRevisedAmount ELSE 0 END), 0) THEN 1 ELSE 0 END AS ActualExpenseOverBudget,
    CASE WHEN ISNULL(SUM(CASE WHEN p.[TYPE] = 'E' THEN p.CuryActualAmount ELSE 0 END), 0) > ISNULL(SUM(CASE WHEN p.[TYPE] = 'I' THEN p.CuryRevisedAmount ELSE 0 END), 0) THEN 1 ELSE 0 END AS ExpenseExceedsIncome,
    CASE 
        WHEN ISNULL(SUM(CASE WHEN p.[TYPE] = 'I' THEN p.CuryRevisedAmount ELSE 0 END), 0) 
             + ISNULL(TRY_CONVERT(DECIMAL(18,2), TRY_CONVERT(MONEY, nte.ValueString)), 0) < 5 
        THEN 1 
        ELSE 0 
    END AS BudgetIncomeAndNTEUnderFive,
    CASE 
        WHEN ISNULL(TRY_CONVERT(DECIMAL(18,2), TRY_CONVERT(MONEY, nte.ValueString)), 0) < 5 
             AND ISNULL(SUM(CASE WHEN p.[TYPE] = 'I' THEN p.CuryRevisedAmount ELSE 0 END), 0) > 0  
        THEN 1  
        ELSE 0 
    END AS NTEUnderFiveWithBudget,
    CASE 
        WHEN ISNULL(SUM(CASE WHEN p.[TYPE] = 'I' THEN p.CuryRevisedAmount ELSE 0 END), 0) < 5 
             AND ISNULL(TRY_CONVERT(DECIMAL(18,2), TRY_CONVERT(MONEY, nte.ValueString)), 0) > 0 
        THEN 1  
        ELSE 0 
    END AS BudgetUnderFiveWithNTE,
    ISNULL(SUM(CASE WHEN p.[TYPE] = 'I' THEN p.CuryRevisedAmount ELSE 0 END), 0) 
        - ISNULL(SUM(CASE WHEN p.[TYPE] = 'E' THEN p.CuryRevisedAmount ELSE 0 END), 0) AS EstimatedMargin,
    ((ISNULL(SUM(CASE WHEN p.[TYPE] = 'I' THEN p.CuryRevisedAmount ELSE 0 END), 0) 
        - ISNULL(SUM(CASE WHEN p.[TYPE] = 'E' THEN p.CuryRevisedAmount ELSE 0 END), 0)) 
        / NULLIF(ISNULL(SUM(CASE WHEN p.[TYPE] = 'I' THEN p.CuryRevisedAmount ELSE 0 END), 0), 0)) * 100 AS GrossMargin,
    -- Calculated fields from SecondLayer
    CASE WHEN 
        ((ISNULL(SUM(CASE WHEN p.[TYPE] = 'I' THEN p.CuryRevisedAmount ELSE 0 END), 0) 
        - ISNULL(SUM(CASE WHEN p.[TYPE] = 'E' THEN p.CuryRevisedAmount ELSE 0 END), 0)) 
        / NULLIF(ISNULL(SUM(CASE WHEN p.[TYPE] = 'I' THEN p.CuryRevisedAmount ELSE 0 END), 0), 0)) * 100 < 0 
        THEN 1 ELSE 0 END AS BudgetedGMUnderZero,
    CASE WHEN 
        ((ISNULL(SUM(CASE WHEN p.[TYPE] = 'I' THEN p.CuryRevisedAmount ELSE 0 END), 0) 
        - ISNULL(SUM(CASE WHEN p.[TYPE] = 'E' THEN p.CuryRevisedAmount ELSE 0 END), 0)) 
        / NULLIF(ISNULL(SUM(CASE WHEN p.[TYPE] = 'I' THEN p.CuryRevisedAmount ELSE 0 END), 0), 0)) * 100 < 8 
        THEN 1 ELSE 0 END AS BudgetedGMUnderEight,
    CASE WHEN  d.Description NOT IN (
        'Alarm Monitoring',
        'Cleaning – Biohazard',
        'Equipment Rental – Cooling',
        'Equipment Rental – Heating',
        'File Maintenance',
        'Inspections',
        'Vital Command Sensors'
    )
    AND 
        ((ISNULL(SUM(CASE WHEN p.[TYPE] = 'I' THEN p.CuryRevisedAmount ELSE 0 END), 0) 
        - ISNULL(SUM(CASE WHEN p.[TYPE] = 'E' THEN p.CuryRevisedAmount ELSE 0 END), 0)) 
        / NULLIF(ISNULL(SUM(CASE WHEN p.[TYPE] = 'I' THEN p.CuryRevisedAmount ELSE 0 END), 0), 0)) * 100 < 4.5 
        THEN 1 ELSE 0 END AS BudgetedGMUnderFourFive,
    CASE 
        WHEN 
            CASE 
                WHEN CHARINDEX('rental', LOWER(d.Description)) > 0 THEN 1 
                ELSE 0 
            END = 1 
            AND ((ISNULL(SUM(CASE WHEN p.[TYPE] = 'I' THEN p.CuryRevisedAmount ELSE 0 END), 0) 
                - ISNULL(SUM(CASE WHEN p.[TYPE] = 'E' THEN p.CuryRevisedAmount ELSE 0 END), 0)) 
                / NULLIF(ISNULL(SUM(CASE WHEN p.[TYPE] = 'I' THEN p.CuryRevisedAmount ELSE 0 END), 0), 0)) * 100 < 4.5 THEN 1
        WHEN 
            CASE 
                WHEN CHARINDEX('rental', LOWER(d.Description)) > 0 THEN 1 
                ELSE 0 
            END = 0 
            AND ((ISNULL(SUM(CASE WHEN p.[TYPE] = 'I' THEN p.CuryRevisedAmount ELSE 0 END), 0) 
                - ISNULL(SUM(CASE WHEN p.[TYPE] = 'E' THEN p.CuryRevisedAmount ELSE 0 END), 0)) 
                / NULLIF(ISNULL(SUM(CASE WHEN p.[TYPE] = 'I' THEN p.CuryRevisedAmount ELSE 0 END), 0), 0)) * 100 < 8.0 THEN 1
        ELSE 0 
    END AS BudgetedGMUnderTargetCombined,
    CASE WHEN ISNULL(SUM(CASE WHEN p.[TYPE] = 'I' THEN p.CuryRevisedAmount ELSE 0 END), 0) 
        > ISNULL(TRY_CONVERT(DECIMAL(18,2), TRY_CONVERT(MONEY, nte.ValueString)), 0) THEN 1 ELSE 0 END AS BudgetedIncomeOverNTE,
    -- Final calculation for ProjectProblemExists
    CASE WHEN 
        (CASE WHEN ISNULL(SUM(CASE WHEN p.[TYPE] = 'I' THEN p.CuryRevisedAmount ELSE 0 END), 0) 
            > ISNULL(TRY_CONVERT(DECIMAL(18,2), TRY_CONVERT(MONEY, nte.ValueString)), 0) THEN 1 ELSE 0 END) + -- BudgetedIncomeOverNTE
        (CASE 
            WHEN 
                CASE 
                    WHEN CHARINDEX('rental', LOWER(d.Description)) > 0 THEN 1 
                    ELSE 0 
                END = 1 
                AND ((ISNULL(SUM(CASE WHEN p.[TYPE] = 'I' THEN p.CuryRevisedAmount ELSE 0 END), 0) 
                    - ISNULL(SUM(CASE WHEN p.[TYPE] = 'E' THEN p.CuryRevisedAmount ELSE 0 END), 0)) 
                    / NULLIF(ISNULL(SUM(CASE WHEN p.[TYPE] = 'I' THEN p.CuryRevisedAmount ELSE 0 END), 0), 0)) * 100 < 4.5 THEN 1
            WHEN 
                CASE 
                    WHEN CHARINDEX('rental', LOWER(d.Description)) > 0 THEN 1 
                    ELSE 0 
                END = 0 
                AND ((ISNULL(SUM(CASE WHEN p.[TYPE] = 'I' THEN p.CuryRevisedAmount ELSE 0 END), 0) 
                    - ISNULL(SUM(CASE WHEN p.[TYPE] = 'E' THEN p.CuryRevisedAmount ELSE 0 END), 0)) 
                    / NULLIF(ISNULL(SUM(CASE WHEN p.[TYPE] = 'I' THEN p.CuryRevisedAmount ELSE 0 END), 0), 0)) * 100 < 8.0 THEN 1
            ELSE 0 
        END) + -- BudgetedGMUnderTargetCombined
        (CASE 
            WHEN ISNULL(SUM(CASE WHEN p.[TYPE] = 'I' THEN p.CuryRevisedAmount ELSE 0 END), 0) < 5 
                 AND ISNULL(TRY_CONVERT(DECIMAL(18,2), TRY_CONVERT(MONEY, nte.ValueString)), 0) > 0 
            THEN 1  
            ELSE 0 
        END) + -- BudgetUnderFiveWithNTE
        (CASE 
            WHEN ISNULL(TRY_CONVERT(DECIMAL(18,2), TRY_CONVERT(MONEY, nte.ValueString)), 0) < 5 
                 AND ISNULL(SUM(CASE WHEN p.[TYPE] = 'I' THEN p.CuryRevisedAmount ELSE 0 END), 0) > 0  
            THEN 1  
            ELSE 0 
        END) + -- NTEUnderFiveWithBudget
        (CASE 
            WHEN ISNULL(SUM(CASE WHEN p.[TYPE] = 'I' THEN p.CuryRevisedAmount ELSE 0 END), 0) 
                + ISNULL(TRY_CONVERT(DECIMAL(18,2), TRY_CONVERT(MONEY, nte.ValueString)), 0) < 5 
            THEN 1 
            ELSE 0 
        END) + -- BudgetIncomeAndNTEUnderFive
        (CASE WHEN ISNULL(SUM(CASE WHEN p.[TYPE] = 'E' THEN p.CuryActualAmount ELSE 0 END), 0) 
            > ISNULL(SUM(CASE WHEN p.[TYPE] = 'I' THEN p.CuryRevisedAmount ELSE 0 END), 0) THEN 1 ELSE 0 END) + -- ExpenseExceedsIncome
        (CASE WHEN ISNULL(SUM(CASE WHEN p.[TYPE] = 'E' THEN p.CuryActualAmount ELSE 0 END), 0) 
            > ISNULL(SUM(CASE WHEN p.[TYPE] = 'E' THEN p.CuryRevisedAmount ELSE 0 END), 0) THEN 1 ELSE 0 END) + -- ActualExpenseOverBudget
        (CASE WHEN ISNULL(SUM(CASE WHEN p.[TYPE] = 'E' THEN p.CuryRevisedAmount ELSE 0 END), 0) < 5 THEN 1 ELSE 0 END) -- RevisedUnderFive
        > 0 THEN 1 ELSE 0 END AS ProjectProblemExists
FROM
    Contract c
    LEFT JOIN ContractKvExt nte 
        ON nte.CompanyID = c.CompanyID 
        AND nte.RecordID = c.NoteID 
        AND nte.FieldName = 'AttributeNTE'
    LEFT JOIN ContractKvExt vnte 
        ON vnte.CompanyID = c.CompanyID 
        AND vnte.RecordID = c.NoteID 
        AND vnte.FieldName = 'AttributeVNTE'
    LEFT JOIN ContractKvExt e 
        ON e.CompanyID = c.CompanyID 
        AND e.RecordID = c.NoteID 
        AND e.FieldName = 'AttributeCATEGORY'
    LEFT JOIN CSAttributeDetail d 
        ON d.CompanyID = e.CompanyID 
        AND d.AttributeID = 'CATEGORY' 
        AND e.ValueString = d.ValueID
    LEFT JOIN ContractKvExt js
        ON js.CompanyID = c.CompanyID 
        AND js.RecordID = c.NoteID 
        AND js.FieldName = 'AttributeJOBSTATUS'
    LEFT JOIN CSAttributeDetail jsd 
        ON jsd.CompanyID = e.CompanyID 
        AND jsd.AttributeID = 'JOBSTATUS' 
        AND js.ValueString = jsd.ValueID
    LEFT JOIN PMBudget p 
        ON p.CompanyID = c.CompanyID 
        AND p.ProjectID = c.ContractID
    LEFT JOIN BAccount manager ON manager.CompanyID = c.CompanyID AND manager.DefContactID = c.OwnerID
    LEFT JOIN EPEmployee manEmp ON manager.CompanyID = manEmp.CompanyID AND manEmp.BAccountID = manager.BAccountID
    LEFT JOIN ContractKvExt execNote ON execNote.CompanyID = c.CompanyID AND execNote.RecordID = c.NoteID AND execNote.FieldName = 'AttributePROJECTEXE'
    LEFT JOIN BAccount bAExec ON bAExec.CompanyID = execNote.CompanyID AND bAExec.AcctCD = execNote.ValueString
    LEFT JOIN EPEmployee execEmp ON bAExec.CompanyID = execEmp.CompanyID AND bAExec.BAccountID = execEmp.BAccountID
    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
    LEFT JOIN BAccount cust ON cust.CompanyID = c.CompanyID AND cust.BAccountID = c.CustomerID
GROUP BY
    c.CompanyID,
    c.ContractID,
    c.ContractCD,
    d.Description,
    nte.ValueString,
    vnte.ValueString,
    d.description,
    bAExec.AcctCD,
    bAExec.AcctName,
    execEmp.userID,
    manager.AcctCD,
    manager.AcctName,
    manEmp.userID,
    br.BranchID,
    br.BranchCD,
    ba.AcctName,
    cust.BAccountID,
    cust.AcctCD,
    cust.AcctName,
    c.CreatedDateTime,
    jsd.description