1 of 1 people found this helpful
You need to have a field for ordered and delivered in your purchase order line table. Its only the delivered stock you need to count against you on hand inventory,
That might solve the problem but I usually don't pay my vender in advanced. Instead, I pay them based on what they delivered.
If they deliver 50 pc of candies along with a bill showing 50 candies delivered from certain PO. I will use that bill to pay my vender. Thus, it would be better if I can have a bill table created to help me keep tracking my POs.
Any idea how to do so?
There are a couple of options, another field for quantity paid for in the POLine, this is the simplest way.
If you go down your bill table route you will get better information on the history of both deliveries and payments to suppliers. It depends on how much you need to see.
This is easier if you have a generic Transaction table – Order, Delivery, and even Sales – each with a factor that determines how a certain line item is calculated into stock: Order: 0, Delivery: 1, Sales: -1
So the figures for an Order transaction will not be reflected in the stock calculation; once the goods arrive for a certain PO, create a Delivery transaction tied to that PO (and as required, duplicate and adapt that Order's line items) to reflect the actual delivery (e.g. 50 delivered where 100 were ordered). ow those line items will be considered for your stock calculations.
A Bill table could simply be a correspondence table that records any correspondence created from a transaction, but hasn't its own line items – it is simply a container for communication events that pertain to to transactions, where Transaction --< Correspondence.
I usually don't pay my vender in advanced.
“vender” = vendor
Yes and no. Personally I would lean towards your suggestion and have in out internal solution however transactional models are far harder to code well. For example all transactions against a PO will need a payment, shipping transactions will require an invoice, you will need to chop the data from this table into these reports for payment/invoice required. You will need to filter portals, tweak list views and all sorts of other stuff.
Also I would not calculate stock live (i.e. all the ins minus the outs) unless the system was simple.
I am thinking the OP was leaning towards a simpler system so was aiming my suggestions there. There is no benefit in complexity beyond the minimum required.
To avoid making it over complicated, I spare "Transactions" till later.
Your "Order_Linitem" and "PO_Lineitem" tables should both have two stored fields storing "Ordered quantity" and "Delivered quantity" (you could even calculate "left to deliver"). And Your "Inventory" table should have a field for storing "quantity in stock".
Then you need two scripts (identical for "order" and "PO") that when a "delivery" (in or out) happens, the delivered quantity is correctly stored in appropriate "LineItem" and either add to or subtract from "quantity in stock".
With best regards Magnus Fransson.