Skip to Content
author's profile photo Former Member
Former Member

Formatted Search Error - Help Needed

Hi All,

My client has a peculiar purchase process. They are into chemical trading and do their purchase as follows:

The vendor gives discount based on the volume and not on the quantities purchased. Thus, an item X if purchased in 250gm will cost $ 20 while the same item if purchased in 50gm may come to $15. So, the user wants to enter this values in the purchase order and want the unit price to be calculated based on this. Assuming the clients wants 1kg quantities of item X, he would like to fill 250 x gm @ $20 in separate UDFs and based n these values, the system should calculate the unit rice for this item.

What I did was as follows:

a) I created 3 UDFs each for the Base Quantity (U_BPQty), Base UoM(U_BPUnit), Base Price (U_BasePrice). Thus the user enters 250, gm, 20 respectively as explained in the above example.

b) I created one field (U_UnitQty) to convert the base unit into purchasing unit. For this, I am writing a query to get the conversion factor value into the PO. Thus if the Purchasing UoM is KG and the base unit in which the supplier is charging is G, then I am populating 1000 in this field.

c) One more UDF to convert the base price into the unit price is captured is created. So, if 250gm cost $20 and my client wants 1kg, I am first getting the value of 4 in this UDF. This I am getting by dividing the value obtained from the step b) with the value in U_BPQty.

Once I get this value, I then calculate the unit price by multiplying 4 with $20 which will populate $80 in my Unit Price field through another FMS.

I wrote a formatted search for converting this base unit to purchasing units. Given here is the FMS which is giving an error. For the sake of another functionality, I created a UDF for Purchasing UoM as well (U_UoM).

Declare @UQty as varchar

Declare @BPC as varchar

set @UQty = $[POR1.U_UnitQty]

set @BPC = SELECT

(Case

when $[POR1.U_UoM] = 'kg' and $[POR1.U_BPUnit] = 'kg' then 1

when $[POR1.U_UoM] = 'kg' and $[POR1.U_BPUnit] = 'g' then (@UQty/$[POR1.U_BPQuantity.NUMBER])

when $[POR1.U_UoM] = 'kg' and $[POR1.U_BPUnit] = 'mg' then (@UQty/$[POR1.U_BPQuantity.NUMBER])

when $[POR1.U_UoM] = 'g' and $[POR1.U_BPUnit] = 'kg' then (@UQty/$[POR1.U_BPQuantity.NUMBER])

when $[POR1.U_UoM] = 'g' and $[POR1.U_BPUnit] = 'g' then 1

when $[POR1.U_UoM] = 'g' and $[POR1.U_BPUnit] = 'mg' then (@UQty/$[POR1.U_BPQuantity.NUMBER])

when $[POR1.U_UoM] = 'mg' and $[POR1.U_BPUnit] = 'g' then (@UQty/$[POR1.U_BPQuantity.NUMBER])

when $[POR1.U_UoM] = 'mg' and $[POR1.U_BPUnit] = 'kg' then (@UQty/$[POR1.U_BPQuantity.NUMBER])

when $[POR1.U_UoM] = 'mg' and $[POR1.U_BPUnit] = 'mg' then 1

when $[POR1.U_UoM] = 'kl' and $[POR1.U_BPUnit] = 'kl' then 1

when $[POR1.U_UoM] = 'kl' and $[POR1.U_BPUnit] = 'l' then (@UQty/$[POR1.U_BPQuantity.NUMBER])

when $[POR1.U_UoM] = 'kl' and $[POR1.U_BPUnit] = 'ml' then (@UQty/$[POR1.U_BPQuantity.NUMBER])

when $[POR1.U_UoM] = 'l' and $[POR1.U_BPUnit] = 'kl' then (@UQty/$[POR1.U_BPQuantity.NUMBER])

when $[POR1.U_UoM] = 'l' and $[POR1.U_BPUnit] = 'l' then 1

when $[POR1.U_UoM] = 'l' and $[POR1.U_BPUnit] = 'ml' then (@UQty/$[POR1.U_BPQuantity.NUMBER])

when $[POR1.U_UoM] = 'ml' and $[POR1.U_BPUnit] = 'l' then (@UQty/$[POR1.U_BPQuantity.NUMBER])

when $[POR1.U_UoM] = 'ml' and $[POR1.U_BPUnit] = 'kl' then (@UQty/$[POR1.U_BPQuantity.NUMBER])

when $[POR1.U_UoM] = 'ml' and $[POR1.U_BPUnit] = 'ml' then 1

else 1

end)

Select @BPC

I would like to know where I am going wrong or if there is any simpler method for the above scenario?

Thanks and regards,

Bharath S

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

1 Answer

  • Posted on Oct 25, 2010 at 11:43 AM

    Try the third line change to:

    set @UQty = $[POR1.U_UnitQty.number]

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.