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

[Sherif O. <sh...@ky...>]

I think Paul's idea is very good, I remember wanting to query for which
invoices are related to a certain sales order, this currently is not
possible. Completeness of information can only be a good thing. So I would
vote yes even if it requires a new table :)

BTW, I just noticed that on Order Invoicing (ConfirmDispatch_Invoice.php)
the date is not saved properly.

Sherif


----- Original Message ----- 
From: "Daintree Family" <p.d...@xt...>
To: "Web-ERP Developers" <Web...@li...>; "Paul
Clark" <PC...@al...>
Sent: Thursday, September 11, 2003 11:24 PM
Subject: [Web-erp-developers] Re: Web-erp database structure change request


> 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.
>
>
>
>
> -------------------------------------------------------
> This sf.net email is sponsored by:ThinkGeek
> Welcome to geek heaven.
> http://thinkgeek.com/sf
> _______________________________________________
> Web-erp-developers mailing list
> Web...@li...
> https://lists.sourceforge.net/lists/listinfo/web-erp-developers
>