DATABASE QUERY: Find qty of a particular sku that has been purchased/received since date.

SOLVED

As per the subject, could someone help me with the query to find the Quantity Received since a particular date for a particular SKU ?

If an order is placed for 10 of SKU(ABC)

we receive 1 on date X, I'm assuming TODATEQTY would be 1.

we receive 1 on date Y, I'm assuming TODATEQTY would become 2.

But where do you see the date each was received?

As per documentation:

//###PURCHASE HISTORY HEADER###

    PURCHASE_HISTORY_HDR.PH_DATE (purchase order date)

    PURCHASE_HISTORY_HDR.RECV_DATE (The date that the items detailed on the purchase order are actually received)

//### PURCHASE HISTORY DETAIL###

    PURCHASE_HISTORY_DTL.BVORDQTY (The quantity ordered in buy unit of measure)

    PURCHASE_HISTORY_DTL.BVTODATEQTY (The quantity received in buy unit of measure)

Parents
  • 0

    Reason for this:

    When we do a physical inventory count, if we don't submit the data as soon as we are done, inventory can change if items were received after the count date and or items were sold after the count date.  I want to have these values adjust in an excel document so the physical counts are adjusted via a formula that accounts for the sold/received/committed quantities.  This is all mostly done except for purchase/received qty.  Unfortunately, I can't find the receive date of particular items. If we order 10 items, receive 5 before the count date, then receive 1 after the count date, I would only want to adjust our physical count by 1, not 6.  But I need to determine which items were received after the count date.

  • +1 in reply to slacka
    verified answer

    found an alternate solution. but can't delete my post.

Reply Children
No Data