FMS query to copy Cost field of SO to PO Unit Price

[pcorp01]

Our pricelist for customers and vendors alike is based on Last Purchase Price, but in our business scenario the last purchase price isn't always the most current one. In the sales order form, the Cost field is defined by SAP as "real time cost" of the item. If the user updates the value of this field -- it gets carried over to Delivery which is perfect. When we use the procurement document wizard, I understand that the PO price is based on the pricelist of the selected vendor. As a work around, is it possible to copy the Cost field to the PO unit price? Hoping to eliminate double data entry...

[kothandaraman_nagarajan · Accepted solution]

Hi,

Try this query,

SELECT T1.[Price] FROM ORDR T0 INNER JOIN RDR1 T1 ON T0.[DocEntry] = T1.[DocEntry] left join POR1 T2 on T2.[BaseEntry] = T1.Docentry and T2.[BaseLine] = T1.[LineNum] INNER JOIN OPOR T3 ON T2.[DocEntry] = T3.[DocEntry] WHERE T3.[DocNum] = T1.[PoTrgNum] and T1.[DocEntry] = $[$38.45.11] and $[$38.46.11] = T1.[LineNum] and $[$38.1.11] = T1.[ItemCode]

Note:

In my query, i am taking unit price from SO to PO. Please change T1.Price to your required field.

Regards,

Nagarajan

[pcorp01 · Accepted solution]

Hi Nagarajan, thanks for the query! How do I change T1.Price to the Cost field, that's the field I want copied to the PO. When I hover the mouse, I don't think Cost belongs to the RDR1 table. Please see screenshot. Thanks

[pcorp01 · Accepted solution]

Hi Nagarajan! I found a field in the RDR1 table that also shows the value of the cost field and used that instead! And it worked!

T1.[GrossBuyPr]

Thanks so much for your help!

[kothandaraman_nagarajan]

The field is $[$38.37.56]

[pcorp01]

Hi Nagarajan! I tried doing that but getting a syntax error.. returns blank. My apologies -- fairly new to sql and fms!

SELECT $[$38.37.56] FROM ORDR T0 INNER JOIN RDR1 T1 ON T0.[DocEntry] = T1.[DocEntry] left join POR1 T2 on T2.[BaseEntry] = T1.Docentry and T2.[BaseLine] = T1.[LineNum] INNER JOIN OPOR T3 ON T2.[DocEntry] = T3.[DocEntry] WHERE T3.[DocNum] = T1.[PoTrgNum] and T1.[DocEntry] = $[$38.45.11] and $[$38.46.11] = T1.[LineNum] and $[$38.1.11] = T1.[ItemCode]<br>

[kothandaraman_nagarajan]

In this case, its possible to fetch cost from SO to add into PO field. Can you share screen for cost field or table name to create FMS.

[pcorp01]

Hi Nagarajan, thanks for replying! Here's the screenshot of the SO and PO. Please let me know if you need more info. The fms would help us tremendously!

[kothandaraman_nagarajan]

Hi,

Its not possible to create FMS in wizards.

Regards,

Nagarajan

[pcorp01]

Hi Nagarajan, sorry I didn't mean FMS in the wizard itself but in the PO form (after it had created a PO from the wizard). What I imagine happening is for the user to manually click on the hourglass icon to refresh/update the fields (Unit Price field) which has the fms query to copy the Cost field in the SO form. Does that make sense?