Most sales reports I have done are based on invoiced (billed) dates and not order dates. Income reports are based on when payments are received. Warehousing reports are based on ship dates. When you receive a Order, don't you create invoices based on that order? Is there a relationship between Orders and Invoicing? Usually when you write and order, it has to be linked to a Purchase Order authorization to make sure you don't Invoice more than has been authorized. If so, the Orders could be tied to the date in the Invoices table for making reports. Would that work?
A blanket order is not really a PO in the sense of your database – it's more like a blueprint for creating POs.
My question is: How have you handled blanket orders in the systems you have built?
I never had the pleasure, but I'd follow the old adage: there's no problem that an additional level of indirection can't solve …
Therefore, I would (speaking hypothetically, until practice proves me a fool) create a new BlanketOrders (BO) table, and add a new foreign key to the PO table. Period.
This way you a) can continue to use your existing reports and relationships within an unchanged basic framework, and b) have a point from which to create consolidated confirmations or other documents that pertain to a BO and everything that stems from it.
The other thing necessary would be an additional line items table (possibly in just a basic form, i.e. item and quantity), because without one a BO wouldn't be much of a blueprint for a PO; and adding BO line items to the existing PO line items table would require the kind of changes and workarounds you want to avoid.
Then think about the workflow that creates POs from BOs, either manually or (semi-)automagically.
Sounds like fun
Thank you for your response.
Our system is designed for manufacturing (specifically a specialized type of printing) and includes many steps before an invoice is actually created.
The basic workflow is: Estimate > PO (from Client) > Sales Order > Work Order(s) > Shipment(s) > Invoice(s) > Payment Receipt(s)
As you can see, we don't make an Invoice at the time we receive a PO from a client. We create a Sales Order, which acts as the PO authorization. As the time between the client PO and the Invoice can be significant, knowing the value of PO's received from our clients, for any particular time period, is one of the important reports that the Sales Order part of our solution provides. Blanket PO's don't wok well with our system and provide screwed reports.
Attached is a pdf illustration showing our entire workflow if your interested.
Also, thank you for your response,
Yep, an additional level it shall be. When a blanket PO is received, we will create multiple Sales Orders to match the production schedule that is required for the blanket PO to be fulfilled. Some new tables will consolidate the multiple Sales Orders (think PO authorization) so that the client can receive the single order confirmation that they expect to receive. I can probably do this in the background so that the user never realizes that the blanket order tables are actually being used. They will just think that they are linking multiple Sales Orders together. Options will be provided when an order confirmation is being generated, for the client, to print either the Sales Order being currently viewed or a consolodated order confirmation for the entire group of sales orders.
I've been thinking about this for a month and could not come up with a decent solution. The act of writing these post and reading your reponses has lead me to a possible solution. Cool!
FlexoEDGE Workflow_sm.pdf 650.7 K