3 Replies Latest reply on Aug 19, 2014 7:30 AM by KylePutzier

    Blanket purchase orders

    KylePutzier

      I have a scenario that does not fit the way I designed my database.

       

      When a customer sends us a purchase order, we basically transcribe it into our Sales Order system and send the customer an order confirmation to confirm that we have received and understand the order correctly. Pretty standard procedure for any company.

      Tracking of Sales is done by simply by finding all sales for a particular day, month, year, whatever. Still pretty standard.

       

      Occasionally, we receive a blanket purchase order, whereas we enter a sales order and then send product out at specific intervals. In our current system, this puts the entire value of the purchase order on a single day, which then spikes our sales report. I would like to have parts of this value on its own day so that it is valued in the month it is scheduled to be produced.

       

      I realize that I have simplified the scenario quite a bit or I could simply use that invoicing program to get such a report (and we do). It is valuable to know how much was sold, produced, shipped and billed at some specific interval though. The produced, shipped and billed entries each have their own record (and transaction date). Sales Orders do not. I need to figure out a way to fix that for the 1% of the orders that it affects.

       

      It would be a significant change to our system to incorporate this and I want to make sure I know as many of my options before I commit to making such a drastic change.

       

      My first thought is to enter multiple sales orders, each with it's own transaction date and create a consolodated order confirmation for the customer. Sound easy enough, but our system is rather large and I guarentee that I will break many other parts of the program that I have not forseen.

       

      My question is: How have you handled blanket orders in the systems you have built?

       

      Kyle

        • 1. Re: Blanket purchase orders
          taylorsharpe

          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?

          • 2. Re: Blanket purchase orders
            erolst

            A blanket order is not really a PO in the sense of your database – it's more like a blueprint for creating POs.

            KylePutzier wrote:

            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

            • 3. Re: Blanket purchase orders
              KylePutzier

              Taylor,

              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.

               

              erolst,

              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!

               

              Kyle