Skip to Content
0
Nov 08, 2012 at 06:27 PM

Approval Procedure

34 Views

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.