[Web-erp-developers] Re: Web-erp database structure change request

[Daintree F. <p.d...@xt...>]

MessageHi Paul,

Yeah that would be a good.

There can potentially be many dispatches for the same sales order. eg an
order for railing x and railing y. The railing x is available ex stock and
is dispatched the same day. Invoice 123456 is appended to the order
narrative. A week later manufacturing complete railing y and the remainder
of the order is fulfilled from a back order off the original order. The
dispatch is invoiced and invoice 123567 is appended to the order comment.
The comment contains a list of all the invoices that the order was
dispatched on.

We could and do have a field in the  DebtorTrans table for the order number
but it is tricky going the other way. To go from the order back to invoices
we must create a seperate table that records invoices, orders due to the
potentially unlimited number of invoices for a single order. Do you think
its worth a new table?

Regards
Phil
  ----- Original Message -----
  From: Paul Clark
  To: 'p.d...@xt...'
  Sent: Thursday, September 11, 2003 4:49 AM
  Subject: Web-erp database structure change request


  Hi Phil;

      As set out in your Contributor guidelines, I would like to make the
following request/suggestion for a change in the underlying web-erp database
structure.

  When invoicing a sales order, you mark the sales order as completed by
inserting the value of 1 in the Completed field in the salesorderdetails
table and appending the value "Inv XX", where X is the invoice number, to
the Comments field in the salesorder table. Might I suggest the addition of
a InvoiceNo field (integer data type) to the salesorder table so as to keep
the Comments field and invoice data separate? This would aid in subsequent
reporting on data based on the invoice number and would also allow for the
insertion of an index on the InvoiceNo field to speed the queries.

  I don't believe this would have too much effect on the web page order
processing logic other than the obvious one that the SQL statement on line
338 of the ConfirmDispatch_Invoice.php would need to be altered from

   $SQL = "UPDATE SalesOrders SET Comments = CONCAT(Comments,' Inv ','" .
$InvoiceNo . "') WHERE OrderNo= " . $_SESSION['ProcessingOrder'];

  to

   $SQL = "UPDATE SalesOrders SET InvoiceNo =  '". $InvoiceNo . " WHERE
OrderNo= " . $_SESSION['ProcessingOrder'];

  and the appropriate Comments or InvoiceNo field would need to be
referenced by all the other web pages where that data is displayed.

  Kind regards,

  Paul Clark
  Network Administrator
  Alco Ventures Inc.