Approval Procedure to change the price of items
i 'm trying to create a query to an Approval Procedure to change the unit price in the Purchase Order i create this query:
SELECT DISTINCT 'True' FROM POR1 T0 INNER JOIN OITM T1 ON T0.[ItemCode] = T1.[ItemCode] WHERE T1.[ItemCode] = $[$38.1.0] AND T1.[LastPurPrc] <> $[$38.14.0]
as you see this query compare is the Last Purchase Price is different than the actual unit price, and these for each line of item.
I think this is correct, and i configure the Approval Procedure, but when i check the AP in the Purchase Order changing the Unit Price, it gets me an error that says
1) [Microsoft][SQL Native Client][SQL Server] Error converting data type nvarchar to numeric.
Am i doing something wrong ?
In easy term, i just want to execute an approval procedure when the user change the unit price in the purchase order.
Hope you can help me !
Thanks in Advance
Best Regards !
Former Member replied
The field $[$38.14.0] has that currency Symbol in front of it which is causing the error. Please change your script to
SELECT DISTINCT 'True' FROM POR1 T0 INNER JOIN OITM T1 ON T0.[ItemCode] = T1.[ItemCode] WHERE T1.[ItemCode] = $[$38.1.0] AND T1.[LastPurPrc] <> <b>CAST(
SUBSTRING($[$38.14.0],4,10) AS DECIMAL(10,2))</b>
Use the Substring to eliminate the currency symbol. In this case I have tried with 'USD ' Change the Starting charecter of the Substring as per your requirement.