D365 Finance & Operations and 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 Jun 06, 2019 12:20 PM

    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
    ------------------------------


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

    SILVER CONTRIBUTOR
    Posted Jun 07, 2019 06:03 AM
    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
    ------------------------------



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

    TOP CONTRIBUTOR
    Posted Jun 07, 2019 09:03 AM
    Thanks Ted.

    I'll try it.

    Sam

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



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

    TOP CONTRIBUTOR
    Posted Jun 07, 2019 03:44 PM
    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
    ------------------------------



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

    TOP CONTRIBUTOR
    Posted Jun 10, 2019 07:17 AM
    Thanks Dave!

    I'll try it.

    Sam

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



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