On hand Inventory by Batch ID


I have been messing around with the InventSumDate classes and its other family members but i haven’t been able to figure out how to list out yesterdays inventory on hand by batch to compare it with today’s. For non-batch controlled items, this is easy but for Batch controlled items i am having an issue. My questions are…

1). once there is no more inventory for a batch, does the InventBatch Record for the batch get deleted?

  1. is this the correct DB relationships between all of the tables?

InventDim.InventDimId = InventSum.InventDimId

InventSum.ItemId = InventTable.itemId

InventDim.inventBatchId = InventBatch.InventBatchId?

  1. to get all of the batches that have ever existed for an item, select * from InventSum where InventSum.itemId = myItemId then join it to inventDim ( on InventDimId from InventDIm and InventSum )to get the batch, then find the InventBatch Record by ItemId and BatchId?