I have written the following query for an Approval Procedure to require approval for Project Lines that exceed the value of a user defined field on the Sales Order, however it doesn't seem to work. I was wondering if someone from the community could help me figure out why.
DECLARE @Param1 as Numeric(19,6)
SET @Param1 = (SELECT T0.TotalLineBudget FROM vw_ProjectSum T0 WHERE $[$38.U_PO_ProjLine.0] = T0.ProjectLine)
DECLARE @Param2 as Numeric(19,6)
SET @Param2 = (SELECT T0.TotalLinePurchase FROM vw_ProjectSum T0 WHERE $[$38.U_PO_ProjLine.0] = T0.ProjectLine)
DECLARE @Param3 as Numeric(19,6)
SET @Param3 = (SELECT T0.TotalLineActual FROM vw_ProjectSum T0 WHERE $[$38.U_PO_ProjLine.0] = T0.ProjectLine)
IF $[$38.21.0\] >= @Param1 or $[$38.21.0\] >= @Param2 or $[$38.21.0\] >= @Param3
SELECT DISTINCT 'TRUE'
Here is the view that it is based on:
Create View vw_ProjectSum AS
SELECT T0.Project,isnull(T0.U_PO_ProjLine,'') as ProjectLine,isnull(sum(T0.U_SO_cost),0.00) as TotalLineBudget,
isnull(SUM(T1.LineTotal),0.00) as TotalLinePurchase,isnull(SUM(T2.LineTotal),0.00) as TotalLineActual
FROM RDR1 T0 LEFT JOIN POR1 T1 ON T0.U_PO_ProjLine = T1.U_PO_ProjLine
LEFT JOIN PCH1 T2 ON T0.U_PO_ProjLine = T2.U_PO_ProjLine
WHERE T0.Project is not null and T0.Project <> ' '
GROUP BY T0.Project, T0.U_PO_ProjLine
If I replace the variable values with actual data, it works in MS SQL.