Re: Handling Orders

[Jeff Kowalczyk <..hidden..>]

On Wed, 04 Mar 2009 16:47:10 +0000, beamends wrote:
> ... keeping track of what is on order from suppliers and who it is
> for. ... I've found that LSMB leaves ordered items on the "short" stock
> report, with no indication of how many are on order (and ideally for
> whom), allowing often expensive double (or more!) ordering of stock, or
> sometimes simply failing to order at all.

With regards to the point about tracking the intended recipient of
ordered stock, I have this need as well, not yet implemented. Might as
well put some ideas out for feedback. I may be using trunk (1.3) before
implementing this, so the details may change.

With branches/1.2, see what happens when using the AP Generate Purchase
Orders from Sales Orders function on one Sales Order, with two line items,
each from a different vendor:
 
  -SELECT pg_catalog.setval('id', 49502, true);
  +SELECT pg_catalog.setval('id', 49504, true);
   
  -SELECT pg_catalog.setval('orderitemsid', 3627, true);
  +SELECT pg_catalog.setval('orderitemsid', 3629, true);
  
   COPY defaults (setting_key, value) FROM stdin;
  -ponumber	1000
  +ponumber	1002
   \.
   
   COPY oe (id, ordnumber, transdate, vendor_id, customer_id, ...
  +49503	1001	2009-03-04	12091	...
  +49504	1002	2009-03-04	49297	...
   \.
  
   COPY orderitems (id, trans_id, parts_id, ..., project_id, ...
  +3628	49503	10177	(description)	6	36	\N		\N	\N	0	\N	\N
  +3629	49504	11213	(description)	2	33	\N		\N	\N	0	\N	\N
   \.
   
   COPY transactions (id, table_name) FROM stdin;
  +49503	oe
  +49504	oe
   \.

I was initially surprised to see that the oe/orderitems tables are used
for both Sales Orders and Purchase Orders. Note also that the parts_id is
used for the Purchase Order, not the partsvendor.entry_id

Several customization opportunities seem like they would work to track
stock-to-order workflow:

Track by project
----------------

The first option is to customize to fill in a project_id on the generated
purchase orders. This won't require modfication to the orderitems table
schema.

If you elect to use convention of a 1:1 customernumber:projectnumber
(automated with triggers and enforced with contstraints, etc), a project
can seem very much like a customer from your user's perspective.

I am doing this now, and it makes the use of timecards for the workflow of
logged services to sales orders much quicker. The users don't really
notice a difference between a customer and a project, as the number is the
same.

There will probably need to be some customizations to display and make use
of this project entry. But there at least seems a natural place to store
the link between the Sales and Purchase orders for stock-to-order.


Add orderitem_id column to orderitems
-------------------------------------

This would require a bit more planning and execution, but what we're
really trying to track is a Sales Order line item out for order on a
Purchase Order line item. Even (especially) if you had a huge trading
partner with dozens of orders open, you are tracking item availability at
the line item level. So perhaps the most sensible thing is to link the two
by id.

It's only an implementation detail of LedgerSMB as of branches/1.2
that these are in the same table. Otherwise, I suspect we'd have had a
(optional) link between Purchase Order and Sales Order line items already.

Any thoughts?

BTW, I have the same requirement when it comes to payments. The user wants
to relate AR payment (probably AP too) per line item.  I think if
LedgerSMB can evolve to support these detailed tracking workflows,
although perhaps not as recommended for the typical user, it will be
better for it.

Jeff