Approval Procedure to change the price of items

[Former Member]

Hi!

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.

What happend?

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_member583013 · Accepted solution]

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.

Suda

[Former Member · Accepted solution]

Hey Suda

Thanks for your answer, that was right, i only changed the query like this

SELECT DISTINCT 'True' FROM POR1 T0 INNER JOIN OITM T1 ON T0.[ItemCode] = T1.[ItemCode] WHERE T1.[ItemCode] = $[$38.1.0] AND T1.[LastPurPrc] <> CAST(

SUBSTRING($[$38.14.0],-4,10) AS DECIMAL(10,2))

That because de currency is at the end of the string and with that i get only the numbre.

Thanks a lot,

best Regards!

[Former Member]

Xavier,

maybe you are with a problem thar you dont' know, i can to expain this point,

1. if you want one approval at level line (comparing prices), not possible to make this way the approval, because you don't have control at level line while the document isn't in database.

2. i bealeve that the better way is to make a store procedure for to insert each record into temp table while to add line in purchase order without (formatead seaches), now, when you create a purchase orden you need create a query for to search record in the temporal table with the condicion that you needed.

3. now, if you want to call a value on the screen (money), the better way is $[$38.1.number]

Good look,

OSCAR KLENNER

> Hi!

> 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.

>

> What happend?

> 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_member583013]

Xavier,

One more option, for future use is to use $[$38.14.Number]

Instead of <b>.0</b> use <b>.Number</b> and it should automatically give you the number part excluding the currency symbol

In this case you need not use the SUBSTRING OR CAST FUNCTIONAL AT ALL.

THIS IS JUST FOR YOUR KNOWLEDGE

I believe you are aware of the point system. If you posted a Question and someone replies to it you can reward them with points. This is how it works

If you designated your thread as a question and are satisfied with one or more of the responses received, you may award points according to the following scheme.

Designation Point Value

Solved My Problem 10

Very Helpful 6

Helpful 2

To award points, go to your user profile and select the relevant thread from the list of Recent Messages. Click on the hyperlinked title. You will be directed to a page that shows all the responses to your original post.

Click on the star icon next to the response for which you wish to reward points. A popup window will appear. Use the radio buttons to mark a response "Solved My Problem," "Very Helpful," or "Helpful."

You are allowed to grant only one 10 point "Solved My Problem" award and one six point "Very Helpful" award, so evaluate all the replies carefully. You may designate an unlimited number of two point "Helpful" awards.

Suda