Operations Flow & Data Model Discussion
Wharehousing
The central point where all stock movements are tracked is the mis_wh_mov table. Every stock movement is clearly related to a specific product in the mis_products table and to a specific store in the mis_stores table, which in turn is related to a specific plant. Furthermore the movements can optionally refer to a specific location in the mis_store_locations and to a specific lot number in the mis_product_lots table. There is also another reference which can be set to an account (mis_accounts), a project (mis_projects) or cost center (mis_centers) and defaulted via parameter. The mis_wh_mov_types table specifies the movement description, its sign, the quantity type (on hand, reserved, on order) and if the movement type is manually allowed or not.
The great majority of stock movements are created automatically, triggered by events such as:
- Purchase Order approval and reset
- Receipt Note approval and reset
- Sales Order approval and reset
- Delivery Note approval and reset
and in this case the stock movements are linked to the corresponding business objects.
A stock movement creation also updates the mis_inventories table (where the quantities on hand are summarized by product, store, account, location and lot no) and the mis_product_wh table. The latter table summarizes the quantities on hand, reserved and on order by plant and product, along with other plant specific data such as minimum inventory quantity, reorder quantity and product default location, so allowing the plant manager to define its own policy data and view its own inventory data.
Purchasing
The purchasing life cycle can optionally begin with the issue of a Material Requisition (MR), whose header and detail lines are stored into the mis_mrs and mis_mr_lines tables. A Material Requisition can be accounted to a project, a cost center or stock account and, once approved, can be automatically transformed into a Purchase Order (PO), after choosing the vendor (mis_parties).
The vendor selection makes available by default its standard order conditions (payment, bank, port etc.) into the Purchase Order header (mis_pos) while the detail lines are stored into the mis_po_lines table. The buyer is free to combine one or more MR into a single PO or to split a MR in two or more PO or to begin the purchasing life cycle directly from the PO.
The products prices can be set manually, but more often they are selected from the vendor Price List or from specific Quotations. The Price Lists are stored into the corresponding header (mis_purc_price_lists) and detail (mis_purc_list_prices) tables, while the Quotations are stored into the mis_quotations table. Beyond the products price and various discount, both the Price Lists and Quotations specify the purchasing unit of measure of the related products and the conversion factor toward the internal unit of measure. They are kept into the database forever, so as to be able to examine their historical trends, and are selected or viewed based on the desired validity date.
The approval of the purchase order triggers the updating of the inventory, increasing the on order quantity, and blocks any further modification of the PO. Actually it is possible, before the material receipt, to reset the PO status to unapproved, reverting all the updates done to the inventory, and enabling the PO modification.
The next logical step in the purchasing life cycle is the material receipt. A Receipt Note document is stored into the mis_rec_notes and mis_rec_note_lines tables. The system helps its creation presenting all the outstanding PO lines with their expected quantities, leaving to the user the ability to check the lines to include, to confirm or modify the quantity and to indicate if the receipt is complete or partial.
The approval of the Receipt Note triggers a lot of activity:
- for each product the inventory is updated incrementing the on hand quantity on the store indicated by the Receipt Note.
- for each product the inventory is updated decrementing the on order quantity on the plant implied by the store.
- for each product the corresponding PO line receipt status is changed to Complete or Partial
- the PO receipt status is changed to Complete or Partial
and blocks any further modification of the Receipt Note. Actually it is possible, before the recording of the Purchase Invoice, to reset the Receipt Note status to unapproved, reverting all the updates done to the inventory and to the PO, and enabling the Receipt Note modification.
The final step is the recording of the Purchase Invoice, which is spread on several tables:
- the mis_purc_invoices table holds the invoice header info and the total invoice amount
- the mis_purc_iva holds the tax (VAT) related amounts
- the mis_purc_invoice_lines holds the various invoice accounts and the corresponding amounts
- the mis_purc_pay_dates holds the amounts to pay grouped by due date
The system helps the creation of the invoice details presenting all the outstanding Receipt Note lines allowing the user to include them and immediately pinpointing any discrepancies. Furthermore, even if they can be set manually, the due amounts and dates are calculated automatically based on the vendor payment code, which deals with almost every conceivable payment term.
The approval of the purchase invoice marks the Receipt Note lines as invoiced, automatically updates the accounting, creating the necessary journal entries for all the accounts involved and blocks any further modification of the invoice. Actually it is possible, before the recording of invoice payments, to reset the invoice status to unapproved, reverting all the updates done (but only if the accounting books are not yet printed), and enabling the invoice modification.
... more to follow ...