2 Replies Latest reply on Oct 19, 2018 1:07 PM by

    Inventory Movement Query

      Hi, I need a query to show inventory movement by item both in and out. This should include all documents that affect inventory which I believe would be AR invoices, goods issues, receipts from production (all outs) and then goods returns, issues for production, and AP invoices (all ins). I'd like it to show the item no, item description, beginning balance, "In" column, "Out" column, and ending balance for a specific time period. A view of a 30, 60, 90 day time period would be best if possible. Please help when you can, thank you!@

        • 1. Re: Inventory Movement Query
          Richard Duffy

          Laura,

           

          This is a very complex query....are you running SQL or HANA?

           

          Have you looked at the standard SAP inventory analysis options?

           

          How do you use plan to use the data in this query - maybe theres an alternative to get you what you need...

           

          Richard

          • 2. Re: Inventory Movement Query

            Hello Laura.  There is also AP Credit Memo, AR Credit Memo, Goods Receipt PO, Goods Receipts, Goods Issues, etc.  The type of documents can be more or less depending upon how you process your documents.  For example, a processing path of Sales Order to Delivery to AR Invoice would require Deliveries instead of AR Invoices - in that path the Delivery impacts Inventory and not the AR Invoice.

             

            The report / SQL you need must be customized to the Customer.  I have written similar SQL for about ten Customers and each ends up being different due to what the Customer is looking for in data fields, displayed information, level of detail, warehouses, etc.  Folks could comment a lot better if you attached an example of your report (drawn up in Excel).  That way we would see the level of detail you are looking for, and as Richard suggests, there might be an alternative.

             

            But it can be done.  Next month I travel to England using a verson of this SQL for a UKISUG presentation to ensure the Inventory Audit Report ("the bible") under the Inventory Module balances with the General Ledger Inventory Accounts. 

             

            The real key to your report is to use the OINM table (a file which contains all transactions affecting Inventory).  Just in case someone is working on your report right now.

             

            OINM is what the Inventory Audit Report under the Inventory Module is using.

             

            Hope this helps!!!