Hi,
**
This reply ended up exploding in size, sorry! It details the overall
methods I’m employing, maybe you might spot any paths you’ve taken or
even rejected.
**
Thanks for taking the time to reply.
Are any of your products promoted on the web, or do you deal on a more
personal basis with clients?
I think I’m getting in fuss about the actual audit trail of inventory
in terms of cradle to grave. I also need to track by delivery batch so
that I can maintain a FIFO when required. It is possible I’m
overcomplicating by trying to simplify things to a more abstract
concept. I started looking at tracking inventory around double entry
accounting principles.
I have models such as:
class InventoryAccount < ActiveRecord::Base
belongs_to :product
belongs_to :storage_location
has_many :inventory_account_entries
has_many :transactions, :through => :inventory_account_entries
end
class InventoryTransaction < ActiveRecord::Base
before_save :check_transaction_balances
has_many :inventory_account_entries
private:
def check_transaction_balances
# check the account entry amounts have balancing debits and credits
end
end
class InventoryAccountEntry < ActiveRecord::Base
belongs_to :inventory_account
belongs_to :inventory_transaction
end
However it becomes complicated as every possible class of product
inventory needs a separate account. For example Product1 would need
account records for:
Product1, Income Account
Product1, Error Account
Product1, At Location 1, Available, From PurchaseOrder1
Product1, At Location 1, Available, From PurchaseOrder2
Product1, At Location 1, Allocated, From PurchaseOrder1
Product1, At Location 1, Allocated, From PurchaseOrder2
That’s 6 accounts just for one product, and each time a new batch
arrives it would also create account objects for each location it is
placed in. I was initially worried that this might make it massively
unmanageable after a while but I suppose it is just disk space.
Eventually every batch accounts would migrate towards a zero balance,
after which they never really need to be referenced unless to look up
previous stock movements. Does this sound logical?
I could almost visualise this working but am struggling to keep the
concept when you need to transfer stock to sales/despatch orders.
Roughly, my sales order processing and despatch models are structured
as follows:
class Customer < ActiveRecord::Base
has_many :sales_orders
has_many :despatch_orders
has_many :invoices
end
class SalesOrder < ActiveRecord::Base
belongs_to :customer
has_many :sales_order_lines
end
class DespatchOrder < ActiveRecord::Base
belongs_to :customer
has_many :despatch_order_lines
end
class Invoice < ActiveRecord::Base
belongs_to :customer
has_many :invoice_lines
end
class PickingBatch < ActiveRecord::Base
picking trolley has fixed number of locations and decides the
maximum number of despatch orders per picking batch
belongs_to :picking_trolley
has_many :despatch_orders
has_many :despatch_order_lines, :through => :despatch_orders
def optimise_picking_order
# assign a sequence number to each despatch_order_line in such a
# way that lines from different despatch_orders are interleaved
during picking
# to achieve an optimal route around the warehouse
end
end
Inventory is allocated to the sales_order which then creates a
despatch order. The despatch order is picked and on pick completion
triggers payment collection at which point an invoice (receipt) is
generated. The picked despatch order then generates a despatch note
and is sent for packing before being barcode scanned to confirm
confirm packing and despatch.
I think the sticking point for me is how to integrate stock movements
using the above double entry method with my SOP models (sales_order,
despatch_order etc). In theory each despatch order (or despatch order
line) would need it own account so stock flowed between
inventory_account_entries from the product income account to the
eventual resting place on the despatch_order_line.
Or maybe I should take a completely different approach (read
simpler!), although I do like the elegance of the double entry
accounting model.
Thanks, Andrew.
On Oct 15, 12:22 pm, Andrew E. [email protected]
