I need help on the design of a database for shipment tracking requisitions of materials form a hub.
Here's the flow:
I receive a text file from another application that contains requisition information (header and details), this is uploaded onto respective requisition table and requisitiondetails table, status set to open.
Requisition is submitted for approval, status set to approved/rejected (flow ended). Here required qty can be modified by approver.
For approved items:
Lines are reviewed to check if inventory is available (in any amount), then rest of requested qty (or the complete line) are then and set for diferent possibilities of procure (Local, Overseas), status set to In Process.
If inventory available, shipment is processed (can be partial), else purchase order is created.
Upon material arrival, shipment is processed (can be partial delivery).