Need a structured and efficient method to handle inventory for a transaction table which would accurately display holding inward and outward inventories. Should be able to clearly define different scenarios where current inventories are in as well as when inventories are out. Inventory scenarios include product purchase inventory (inward type of entry) as well as product returns accounted for in the inventory.
Also needed is a section for ordered inventory (outward type of entry) and correlating dispatched inventory. Inward Inventory = Outward Inventory which is similar to accounting practice.
Need design to roll up on order level, product level, product inventory, and account level. Design should be able to help form a scalability perspective with the minimum amount of code change.