Unified Operations & Dynamics AX Forum

Expand all | Collapse all

Need to pull items with latest PO price - AX 2012 R2

  • 1.  Need to pull items with latest PO price - AX 2012 R2

    TOP CONTRIBUTOR
    Posted 19 days ago

    Hello,

    I need to pull all Raw Materials with their last PO price.  Do you know which table would hold this information?

    We do not tick the box on the item master to use Last PO price so it's not in InventTableModule table.  Is there another table where I could pull that information?

    Thanks in advance for your help.

    Sam

     



    ------------------------------
    Sam Clark
    Business Systems Analyst
    Team Technologies
    Morristown TN
    ------------------------------
    AXUG Summit - Post


  • 2.  RE: Need to pull items with latest PO price - AX 2012 R2

    SILVER CONTRIBUTOR
    Posted 18 days ago
    Since the on hand inventory only shows the value of the inventory I would recommend using the transactions inquiry. Then you can filter by purchase receipts and filter by the oldest batch number that you have in inventory. From there you can pull view the financial cost amount divided by the units received to get the ea purchase price and link to your on hand inventory.

    If this is not clear please let me know and I may be able to assist you through this process.

    Of course if you have and are using Atlas this would make it much easier to pull and link this detail.

    Good Luck.

    ------------------------------
    Ted Jillett
    Business Systems Analyst, Manufacturing AX
    Insulet Corporation
    Acton MA
    ------------------------------

    AXUG Summit - Post


  • 3.  RE: Need to pull items with latest PO price - AX 2012 R2

    TOP CONTRIBUTOR
    Posted 18 days ago
    Thanks Ted.

    I'll try it.

    Sam

    ------------------------------
    Sam Clark
    Business Systems Analyst
    Team Technologies
    Morristown TN
    ------------------------------

    AXUG Summit - Post


  • 4.  RE: Need to pull items with latest PO price - AX 2012 R2

    TOP CONTRIBUTOR
    Posted 18 days ago
    Hi Sam -

    Have a look at this script.

    SELECT PURCHID, ITEMID, PURCHPRICE
    FROM (SELECT PURCHID, ITEMID, PURCHPRICE, rank () over (partition by ITEMID order by RECID desc) AS rnk
    FROM PURCHLINE where DATAAREAID = 'ceu') x WHERE x.rnk = 1 ORDER BY ITEMID

    You'll want to change the DataAreaID and validate the results.  I added the Purchase Order number to help with validation.

    Thanks....Dave

    ------------------------------
    Dave Phillips
    Sr Support Escalation Engineer
    Microsoft
    Fargo ND
    ------------------------------

    AXUG Summit - Post


  • 5.  RE: Need to pull items with latest PO price - AX 2012 R2

    TOP CONTRIBUTOR
    Posted 15 days ago
    Thanks Dave!

    I'll try it.

    Sam

    ------------------------------
    Sam Clark
    Business Systems Analyst
    Team Technologies
    Morristown TN
    ------------------------------

    AXUG Summit - Post


If you've found this thread useful, dive deeper into User Group community content by role