Skip to Content
author's profile photo Former Member
Former Member

Last Purchase price

Hi,

I need to populate the last purchase price for items which happen only through A/P Invoice.

Table OITM has last purchase price but its not equal to the actual price from A/P Invoice which can be checked from Inventory-Inventory Reports-Last Price Reports.

Item Master (OITM) price and this above report price are both different.

How to get the last purchase price based on A/P in a query?

Thanks

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

6 Answers

  • author's profile photo Former Member
    Former Member
    Posted on Nov 02, 2011 at 07:39 PM

    Hi,

    Try:

    SELECT T0.ItemCode,MAX(T0.DocDate) 'Latest', (SELECT Price FROM PCH1 WHERE ItemCode=T0.ItemCode AND DocDate= MAX(T0.DocDate)) 'Last Price'

    FROM PCH1 T0

    Group By T0.ItemCode

    Thanks,

    Gordon

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Nov 03, 2011 at 04:25 AM

    Hi........

    Please try this.......

    SELECT Top 1 T0.[DocNum] 'PO No.', T0.[DocDate] 'PO Date', T0.CardName 'Supplier', T1.[Dscription] 'Product Name', T1.[Quantity], T1.[Price], T2.[PymntGroup] 'Payment Terms' FROM OPCH T0  INNER JOIN PCH1 T1 ON T0.DocEntry = T1.DocEntry Inner Join OCTG T2 On T0.GroupNum=T2.GroupNum
    WHERE T1.[Dscription] ='[%0]'
    Order By T0.[DocDate] DESC

    Regards,

    Rahul

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Oct 09, 2013 at 07:41 AM

    Try this one...

    SELECT ROW_NUMBER()


    OVER (PARTITION BY PCH1.ItemCode ORDER BY OPCH.DocDate DESC) AS RawNo,

    OPCH.DocNum, OPCH.DocDate, PCH1.ItemCode, PCH1.Price, OPCH.DocCur, OPCH.DocRate, PCH1.Price * OPCH.DocRate AS 'Price(LC)'

    INTO #PurchasingItems

    FROM OPCH


    INNER JOIN PCH1 ON OPCH.DocEntry = PCH1.DocEntry

    WHERE


    OPCH.DocType = 'I'

    ORDER BY


    PCH1.ItemCode, OPCH.DocDate DESC

    SELECT *

    FROM #PurchasingItems

    WHERE


    #PurchasingItems.RawNo = 1
    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Nov 17, 2013 at 10:32 PM

    Hello,

    You don't have to do this via query. Simply create a PO, add a new row with an item, click in the price for that item and push CTRL + TAB. Then you can uncheck all the boxes except for AP Invoice.

    I wrote a short article with an example here: http://www.battleshipcobra.com/2013/last-price-report-in-sales-documents/

    Thanks,

    Mike

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Oct 12, 2015 at 07:16 AM

    SELECT T0.ItemCode, MAX(T0.DocDate) 'Latest',

    (SELECT top 1 Price FROM PCH1 WHERE ItemCode=T0.ItemCode order by DocDate desc) 'Last Purchase Price'

    FROM PCH1 T0

    Group By T0.ItemCode

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Nov 08, 2019 at 07:47 AM
    SELECT
      T1."ItemCode",
      T1."Price"
    FROM OPCH T0
    INNER JOIN PCH1 T1
      ON T0."DocEntry" = T1."DocEntry"
    INNER JOIN (SELECT
      T1."ItemCode",
      MAX(T0."DocDate") "LastDocDate",
      MAX(T0."DocNum") "LastDocNum"
    FROM OPCH T0
    INNER JOIN PCH1 T1
      ON T0."DocEntry" = T1."DocEntry"
    GROUP BY T1."ItemCode") T2
      ON (T1."ItemCode" = T2."ItemCode"
      AND T0."DocDate" = T2."LastDocDate"
      AND T0."DocNum" = T2."LastDocNum")
    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.