Login
Register
Search
Home
Forums
Jobs
LawsonGuru
LawsonGuru Letter
LawsonGuru Blog
Worthwhile Reading
Infor Lawson News Feed
Store
Store FAQs
About
Forums
Supply Chain Management
Lawson S3 Supply Chain
Need Help Pulling in UOM pricing in PO28
Home
Forums
Jobs
LawsonGuru
LawsonGuru Letter
LawsonGuru Blog
Worthwhile Reading
Infor Lawson News Feed
Store
Store FAQs
About
Who's On?
Membership:
Latest:
Wayne Willis
Past 24 Hours:
0
Prev. 24 Hours:
1
Overall:
5190
People Online:
Visitors:
294
Members:
0
Total:
294
Online Now:
New Topics
Lawson Landmark
Executing IPA from PR160
5/3/2024 2:46 PM
We're facing a situation where we need to upda
IPA/ProcessFlow
Executing IPA from PR160
5/3/2024 2:46 PM
We're facing a situation where we need to upda
S3 Customization/Development
Cobol - Extract Current Time
4/24/2024 7:21 PM
How do you extract just the Current System Time in
Lawson Landmark
LPL INSTR Functions
4/5/2024 8:32 PM
I'm writing a simple report using the Create R
Infor SCM
Translating 856 to get the ~ REF^CN^ field
4/3/2024 8:24 PM
We are trying to get the tracking number which is
IPA/ProcessFlow
Sample XML file create Flow
4/3/2024 3:43 PM
Hello everyone, I am new to creating XML files
Lawson S3 HR/Payroll/Benefits
bn105 error message
3/26/2024 6:40 PM
I need to change some of the set ups in our Life I
IPA/ProcessFlow
IPA executing Job
3/13/2024 7:08 PM
New to the IPA world and was wondering, can an IPA
Lawson S3 HR/Payroll/Benefits
Life Age Reduction on benefits plans
3/12/2024 7:15 PM
For our optional life we have an age based coverag
Lawson S3 HR/Payroll/Benefits
BN53.1 Add-In
3/7/2024 3:31 PM
We are migrating to Solstice. They require a
Top Forum Posters
Name
Points
Greg Moeller
4184
David Williams
3349
JonA
3288
Kat V
2984
Woozy
1973
Jimmy Chiu
1883
Kwane McNeal
1437
Ragu Raghavan
1354
Roger French
1311
mark.cook
1244
Forums
Unanswered
Active Topics
Most Liked
Most Replies
Search Forums
Search
Advanced Search
Topics
Posts
Prev
Next
Forums
Lawson S3 Supply Chain
Need Help Pulling in UOM pricing in PO28
Sort:
Oldest First
Most Recent First
You are not authorized to post a reply.
Author
Messages
David M
Basic Member
Posts: 11
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
Posts: 1020
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
Posts: 11
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
Posts: 1162
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
klemmyjb
Basic Member
Posts: 12
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
Posts: 1020
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
You are not authorized to post a reply.