cancel
Showing results for 
Search instead for 
Did you mean: 

Inventory As of Specific Date - With Batches and Bins (SAP Business One HANA)

jazparmar
Member
0 Kudos
609

Hi All,

I've been struggling to get a query working properly (in all aspects) to return the following data (in HANA syntax).

We need to return at least the following fields of data:

  • Item Code
  • Item Description
  • WhsCode
  • Bin Location
  • Batch
  • Inventory Count UoM
  • On Hand Qty
  • Batch Cost

I actually need a lot more information than the above, but it would be a great starting point to at least get the above working properly.

Some important considerations:

  • The variable parameter is supposed to be the date (as in run this query with Inventory as of a specific date).
  • The result set should only display inventory on hand (based on the parameter date value) > 0.
  • The result set should break out the data down to the bin level. For example, there can be quantities in stock in bin a and bin b in the same warehouse utilizing the same batch number. Also, the same batch number can be used across various item codes.
  • Item Codes can be in any warehouse and in any batch and in any bin. Bins and batches can repeat across items; meaning Item "A" can use Bin "123" and Batch "456". Item "B" can use Bin "123" and Batch "456"; it may also use only one (meaning on the same Bin but maybe not the same Batch)...etc.

I've tried 10s of combinations utilizing the OINM, OIBT, OBIN, OITW and OITM tables but getting erroneous data (in some aspect). Sometimes the on hand quantities are correct but showing in the wrong warehouse or wrong bin. Sometimes all batches do not show or all bins with inventory on hand as of the specific date do not show...etc.

I am using the batches and serial inventory audit report as a reference to check against the query data.

Any help would be appreciated.

Ty!

View Entire Topic
Neeraj_Jain
Active Contributor
0 Kudos

Hello jazparmar,

Refer below SAP thread. Hope it's clarifies your query.

https://answers.sap.com/questions/12496717/stock-report-with-batch-numbers-query.html

Regards,

Neeraj Jain

divtech
Explorer
0 Kudos

Hi neeraj.jain16,

The query in that thread does not have inventory "as of" a specific date unfortunately.

Thanks,
Jaz