Prev
Next
Forums Supply Chain Management Lawson S3 Supply Chain

Need Help Pulling in UOM pricing in PO28

 5 Replies
 1 Subscribed to this topic
 98 Subscribed to this forum
Sort:
Author
Messages
David M
Basic Member
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
    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
      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
        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
          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
            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