• Search Forums
    Advanced Search Topics Posts
Prev
Next
Forums Supply Chain Management Lawson S3 Supply Chain

Need Help Pulling in UOM pricing in PO28

 5 Replies
 0 Subscribed to this topic
 38 Subscribed to this forum
Sort:
Author
Messages
David M
Basic Member Send Private Message
Posts: 11
Basic Member
10/16/2013 5:00 PM
Hello,

I need to build an Excel query or application that will pull in all Units of Measure and there pricing for all "active" items. The direct screen that I know I can find this info for "manually" is PO28. I can't find a report in Lawson that will do this and I have tried to build the query and applicaton from Excel addins myself and have had no luck.

expanded description of problem-- If i pull in the item master data, it only will pull in the base cost which is usually the highest unit of measure price. the problem is that lets say the price is at Case and I need the Box or Each price for my report. I have no easy way to get this info other than manually doing the math/conversion to get the other UOM pricing. Or looking it up in PO28 which for 100 items or more can take all day. I hope this all makes sense.

Can anyone help, any ideas?

Thanks so much

David
Tags: PO28, pricing, unit of measure, UOM
Kat V
Veteran Member Send Private Message
Posts: 1020
Veteran Member
10/16/2013 5:51 PM
If you have the UOM in stock, the "Last Cost" on ITEMLOC is for the stock uom.

Otherwise - no, we do it in SQL:
(PF.BASE_COST / CASE PF.UOM WHEN IM.ALT_UOM_01 THEN IM.ALT_UOM_CONV_01
WHEN IM.ALT_UOM_02 THEN IM.ALT_UOM_CONV_02
WHEN IM.ALT_UOM_03 THEN IM.ALT_UOM_CONV_03
WHEN IM.ALT_UOM_04 THEN IM.ALT_UOM_CONV_04
WHEN IM.ALT_UOM_05 THEN IM.ALT_UOM_CONV_05
WHEN IM.ALT_UOM_06 THEN IM.ALT_UOM_CONV_06
ELSE IM.ALT_UOM_CONV_01
END) AS "STOCKCOST",
(CASE PF.UOM WHEN IM.ALT_UOM_01 THEN IM.ALT_UOM_CONV_01
WHEN IM.ALT_UOM_02 THEN IM.ALT_UOM_CONV_02
WHEN IM.ALT_UOM_03 THEN IM.ALT_UOM_CONV_03
WHEN IM.ALT_UOM_04 THEN IM.ALT_UOM_CONV_04
WHEN IM.ALT_UOM_05 THEN IM.ALT_UOM_CONV_05
WHEN IM.ALT_UOM_06 THEN IM.ALT_UOM_CONV_06
ELSE IM.ALT_UOM_CONV_01
END) AS "CONV",

PF = POVAGRMTLN
IM = ITEMMAST
Tags: PO28, pricing, unit of measure, UOM
David M
Basic Member Send Private Message
Posts: 11
Basic Member
10/16/2013 8:52 PM
Thanks for your response. The problem now is that I do not know SQL.
Tags: PO28, pricing, unit of measure, UOM
JonA
Veteran Member Send Private Message
Posts: 1163
Veteran Member
10/17/2013 2:04 PM
Are you looking for the cost at the stock uom? Can you use LAST-REC-COST from the ITEMLOC table? Otherwise if you're limited with Addins as your only route to the data you can get to the stock UOM cost from the base cost on contract with two queries and a nested IF/VLOOKUP formula in Excel.

Query POVAGRMTLN (filter by HOLD-FLAG = N) and pull

ITEM
DESCRIPTION
BASE-COST
UOM

Then query ITEMMAST (filter by ACTIVE-STATUS = A) and pull

ITEM
ALT-UOM-1
ALT-UOM-2
ALM-UOM-CONV-2
ALT-UOM-3
ALM-UOM-CONV-3
ALT-UOM-4
ALM-UOM-CONV-4
ALT-UOM-5
ALM-UOM-CONV-5
ALT-UOM-6
ALM-UOM-CONV-6

Now you should have two spreadsheets: Book1 with the contract cost data and Book2 with the Item Master UOM conversions.

Copy the formula below and paste in cell E1 in Book1 (contract data):

=IF(D2=VLOOKUP(A2,[Book2]Sheet1!$A$2:$L$82716,2,FALSE),C2,IF(D2=VLOOKUP(A2,[Book2]Sheet1!$A$2:$L$82716,3,FALSE),C2/VLOOKUP(A2,[Book2]Sheet1!$A$2:$L$82716,4,FALSE),IF(D2=VLOOKUP(A2,[Book2]Sheet1!$A$2:$L$82716,5,FALSE),C2/VLOOKUP(A2,[Book2]Sheet1!$A$2:$L$82716,6,FALSE),IF(D2=VLOOKUP(A2,[Book2]Sheet1!$A$2:$L$82716,7,FALSE),C2/VLOOKUP(A2,[Book2]Sheet1!$A$2:$L$82716,8,FALSE),IF(D2=VLOOKUP(A2,[Book2]Sheet1!$A$2:$L$82716,9,FALSE),C2/VLOOKUP(A2,[Book2]Sheet1!$A$2:$L$82716,10,FALSE),IF(D2=VLOOKUP(A2,[Book2]Sheet1!$A$2:$L$82716,11,FALSE),C2/VLOOKUP(A2,[Book2]Sheet1!$A$2:$L$82716,12,FALSE),FALSE))))))

You will have to tweak the VLOOKUP range to match the number of active items in your item master but what this does is lookup the item number in the item master conversion data and divides the contract cost by conversion number for the contract uom. Drag or copy the formula down the column to the end of your contract data.
Tags: PO28, pricing, unit of measure, UOM
Jon Athey - Sr. Supply Chain Analyst - Materials Management - MyMichigan Health
klemmyjb
Basic Member Send Private Message
Posts: 14
Basic Member
6/15/2015 2:07 PM
Kat V,

please am trying to pull a report that shows all items that are marked billable (IC12) and include the cost of item in Oracle can you help please

clem
Tags: PO28, pricing, unit of measure, UOM
Kat V
Veteran Member Send Private Message
Posts: 1020
Veteran Member
6/15/2015 5:07 PM
Clem - my sql for including the unit cost is above. What did you need?
Tags: PO28, pricing, unit of measure, UOM