Re: [webERP-users] Bug in Planning and MRP

classic Classic list List threaded Threaded
8 messages Options
Reply | Threaded
Open this post in threaded view
|

Re: [webERP-users] Bug in Planning and MRP

phildaintree
Thanks for being constructive Tim. I understand better what you were
trying to do.

In principle I really do prefer to have the purchase orders that are no
longer purchase orders either because they are canceled or rejected
deleted from the purchase order tables. Thus enabling all the other
places where purchase orders are searched by item, for a plethora of
reasons to have nice simple sql to just the retrieve real orders. The
purchorders_deleted retains all the status and narrative of status
changes so this table can be queried or other scripts written to examine
what happened to deleted orders.

By dealing correctly with the data up front, we speed up queries with
less criteria and less redundant records, we simplify the code in all
the other scripts that just need to know what is or was on order.

You are absolutely right, the rejected orders need to be moved to the
purchorders_deleted table too.

I think pending orders unauthorised are ok to show against items -
always assume the best that they will be authorised - only when we know
the order is not authorised (rejected) then should we delete in my view.

You give my ego too much credit - I have often been amazed at the clever
stuff you come up with, but I have found that sometimes the clever stuff
is really unnecessary and doing the simple stuff upfront  makes
everything downstream so much easier.

I just want webERP to retain the simplicity and efficiency that is
important to me and stated in the goals of the project.

You well know that I am keen for someone else to step up and believe in
this stuff as I do.

Phil

>
> If the status of the order is changed to cancelled, either via the
> PO_Header.php or the PO_AuthoriseMyOrders.php scripts then the orders
> don't get transferred to your new tables. They only get moved if the
> "Cancel and Delete" button is pressed, so trunk is now inconsistent.
>
> If however your fix was properly implemented, when a user has created
> a purchase order, but their authoriser has elected to cancel it, then
> the order will appear to have magically disappeared and the original
> creator of the order will not know what has happened to it. Yes if
> they have direct access to the database and a knowledge of sql they
> can run a query against your new tables, but its not the same as being
> able to directly query the order via the interface to find out its
> history.
>
> Orders that are rejected will continue to be shown in the planning
> report under your fix. Should we have separate tables for rejected
> orders as well? It could also be argued that pending orders shouldn't
> be included until they are authorised, maybe they should have separate
> tables too?
>
> I know several companies that have written reports to analyse
> cancelled orders, and your "fix" has broken these as well.
>
> I know that the current design principle for webERP is to do the
> opposite of whatever Tim says, but I really do feel that on this one
> you need to set your ego to one side and look at what is best for
> webERP, and the users of webERP.
>
> Thanks
> Tim
>
>
> On 5 December 2010 06:48, Phil Daintree<[hidden email]>  wrote:
>> Just a heads up anyone using inventory planning reports, MRP or even the
>> stock status information .... a change in purchasing code I guess last
>> year or more ago, has led to purchase orders that are flagged as
>> deleted, unhelpfully remaining on order for the purposes of MRP and
>> planning.
>>
>> I am working on a fix.... apologies to anyone who has lost custom due to
>> stock outs as a result :-(
>>
>> Thanks Julie for pointing it out!!
>>
>> Phil
>>
>> ------------------------------------------------------------------------------
>> What happens now with your Lotus Notes apps - do you make another costly
>> upgrade, or settle for being marooned without product support? Time to move
>> off Lotus Notes and onto the cloud with Force.com, apps are easier to build,
>> use, and manage than apps on traditional platforms. Sign up for the Lotus
>> Notes Migration Kit to learn more. http://p.sf.net/sfu/salesforce-d2d
>> _______________________________________________
>> web-ERP-users mailing list
>> [hidden email]
>> https://lists.sourceforge.net/lists/listinfo/web-erp-users
>>
>
>
>

------------------------------------------------------------------------------
Oracle to DB2 Conversion Guide: Learn learn about native support for PL/SQL,
new data types, scalar functions, improved concurrency, built-in packages,
OCI, SQL*Plus, data movement tools, best practices and more.
http://p.sf.net/sfu/oracle-sfdev2dev 
_______________________________________________
Web-erp-developers mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/web-erp-developers
If anyone is wondering about the persistently nasty comments made by Tim Schofield and wants the full story please see: http://timschofield.blogspot.com/ Hell hath no fury like a woman (or Tim) scorned
Reply | Threaded
Open this post in threaded view
|

Re: [webERP-users] Bug in Planning and MRP

TimSchofield5
This post was updated on .
The thing is Phil, that your solution is only simple because you only
implemented a small and easy part of it. For your solution to work you
need to at the very least provide scripts to enable a user to see what
has happened their orders (whether they have been cancelled or
rejected), you also need a script to enable an authoriser to
re-instate an order. For instance your boss rejects your order, you go
to him and say "but boss if we dont have that order in the morning
production will stop" so boss says "ok you can have the order". Under
the current code that just works fine and an audit trail is kept of
what has happened. Under your code that simply can't happen without a
lot more work.

Merely correcting the reports and inquiries is easy. I have already
done it on the launchpad branch. You will have to go into these
reports at some point, as by ripping out my conversion factor code and
not replacing it you have (re)introduced the bug that stops the on
order quantity showing, making the reports nonsense anyway.

For instance to make InventoryPlanning.php work correctly all you need
to do is to replace the sql at line 264 with the following:

                        $SQL = "SELECT SUM(purchorderdetails.quantityord*(CASE WHEN
purchdata.conversionfactor IS NULL THEN 1 ELSE
purchdata.conversionfactor END)
                                - purchorderdetails.quantityrecd*(CASE WHEN
purchdata.conversionfactor IS NULL THEN 1 ELSE
purchdata.conversionfactor END)) as qtyonorder
                                FROM purchorderdetails
                                LEFT JOIN purchorders
                                ON purchorderdetails.orderno = purchorders.orderno
                                LEFT JOIN purchdata
                                ON purchorders.supplierno=purchdata.supplierno
                                AND purchorderdetails.itemcode=purchdata.stockid
                                WHERE  purchorderdetails.itemcode = '" . $InventoryPlan['stockid'] . "'
                                AND purchorderdetails.completed = 0
                                AND purchorders.status <> 'Cancelled'
                                AND purchorders.status <> 'Rejected'";

This solves both problems very easily. I cannot see what your
objection to this code can be. It seems to me you are rejecting a
simple and elegant solution in favour of a complex and inflexible
solution, or am I missing something?

Thanks
Tim


On 13 December 2010 11:01, Phil Daintree <phil@logicworks.co.nz> wrote:
> Thanks for being constructive Tim. I understand better what you were
> trying to do.
>
> In principle I really do prefer to have the purchase orders that are no
> longer purchase orders either because they are canceled or rejected
> deleted from the purchase order tables. Thus enabling all the other
> places where purchase orders are searched by item, for a plethora of
> reasons to have nice simple sql to just the retrieve real orders. The
> purchorders_deleted retains all the status and narrative of status
> changes so this table can be queried or other scripts written to examine
> what happened to deleted orders.
>
> By dealing correctly with the data up front, we speed up queries with
> less criteria and less redundant records, we simplify the code in all
> the other scripts that just need to know what is or was on order.
>
> You are absolutely right, the rejected orders need to be moved to the
> purchorders_deleted table too.
>
> I think pending orders unauthorised are ok to show against items -
> always assume the best that they will be authorised - only when we know
> the order is not authorised (rejected) then should we delete in my view.
>
> You give my ego too much credit - I have often been amazed at the clever
> stuff you come up with, but I have found that sometimes the clever stuff
> is really unnecessary and doing the simple stuff upfront  makes
> everything downstream so much easier.
>
> I just want webERP to retain the simplicity and efficiency that is
> important to me and stated in the goals of the project.
>
> You well know that I am keen for someone else to step up and believe in
> this stuff as I do.
>
> Phil
>
>>
>> If the status of the order is changed to cancelled, either via the
>> PO_Header.php or the PO_AuthoriseMyOrders.php scripts then the orders
>> don't get transferred to your new tables. They only get moved if the
>> "Cancel and Delete" button is pressed, so trunk is now inconsistent.
>>
>> If however your fix was properly implemented, when a user has created
>> a purchase order, but their authoriser has elected to cancel it, then
>> the order will appear to have magically disappeared and the original
>> creator of the order will not know what has happened to it. Yes if
>> they have direct access to the database and a knowledge of sql they
>> can run a query against your new tables, but its not the same as being
>> able to directly query the order via the interface to find out its
>> history.
>>
>> Orders that are rejected will continue to be shown in the planning
>> report under your fix. Should we have separate tables for rejected
>> orders as well? It could also be argued that pending orders shouldn't
>> be included until they are authorised, maybe they should have separate
>> tables too?
>>
>> I know several companies that have written reports to analyse
>> cancelled orders, and your "fix" has broken these as well.
>>
>> I know that the current design principle for webERP is to do the
>> opposite of whatever Tim says, but I really do feel that on this one
>> you need to set your ego to one side and look at what is best for
>> webERP, and the users of webERP.
>>
>> Thanks
>> Tim
>>
>>
>> On 5 December 2010 06:48, Phil Daintree<phil@logicworks.co.nz>  wrote:
>>> Just a heads up anyone using inventory planning reports, MRP or even the
>>> stock status information .... a change in purchasing code I guess last
>>> year or more ago, has led to purchase orders that are flagged as
>>> deleted, unhelpfully remaining on order for the purposes of MRP and
>>> planning.
>>>
>>> I am working on a fix.... apologies to anyone who has lost custom due to
>>> stock outs as a result :-(
>>>
>>> Thanks Julie for pointing it out!!
>>>
>>> Phil
>>>
>>> ------------------------------------------------------------------------------
>>> What happens now with your Lotus Notes apps - do you make another costly
>>> upgrade, or settle for being marooned without product support? Time to move
>>> off Lotus Notes and onto the cloud with Force.com, apps are easier to build,
>>> use, and manage than apps on traditional platforms. Sign up for the Lotus
>>> Notes Migration Kit to learn more. http://p.sf.net/sfu/salesforce-d2d
>>> _______________________________________________
>>> web-ERP-users mailing list
>>> web-ERP-users@lists.sourceforge.net
>>> https://lists.sourceforge.net/lists/listinfo/web-erp-users
>>>
>>
>>
>>
>
> ------------------------------------------------------------------------------
> Oracle to DB2 Conversion Guide: Learn learn about native support for PL/SQL,
> new data types, scalar functions, improved concurrency, built-in packages,
> OCI, SQL*Plus, data movement tools, best practices and more.
> http://p.sf.net/sfu/oracle-sfdev2dev
> _______________________________________________
> Web-erp-developers mailing list
> Web-erp-developers@lists.sourceforge.net
> https://lists.sourceforge.net/lists/listinfo/web-erp-developers
>



--
Course View Towers,
Plot 21 Yusuf Lule Road,
Kampala
T   +256 (0) 312 314 418
M +256 (0) 752 963 325
www.weberpafrica.com
@TimSchofield2
Blog: http://weberpafrica.blogspot.co.uk/

------------------------------------------------------------------------------
Oracle to DB2 Conversion Guide: Learn learn about native support for PL/SQL,
new data types, scalar functions, improved concurrency, built-in packages,
OCI, SQL*Plus, data movement tools, best practices and more.
http://p.sf.net/sfu/oracle-sfdev2dev 
_______________________________________________
Web-erp-developers mailing list
Web-erp-developers@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/web-erp-developers
For those wondering about the constant nastiness and abuse that Phil Daintree fires at me, the facts can be found here at http://weberpafrica.blogspot.co.uk/
Reply | Threaded
Open this post in threaded view
|

Re: [webERP-users] Bug in Planning and MRP

phildaintree
Responses within ...

Quoting Tim Schofield <[hidden email]>:

> For your solution to work you
> need to at the very least provide scripts to enable a user to see what
> has happened their orders (whether they have been cancelled or
> rejected),

OK - I'll do this too... basically a copy of the purchase order  
inquiry script I guess.

> you also need a script to enable an authoriser to
> re-instate an order. For instance your boss rejects your order, you go
> to him and say "but boss if we dont have that order in the morning
> production will stop" so boss says "ok you can have the order". Under
> the current code that just works fine and an audit trail is kept of
> what has happened. Under your code that simply can't happen without a
> lot more work.
>

Well in organisations I've worked for, when presented with an order  
that seems wrong - a discussion happens at this point between  
authoriser and initiator - rather than a rejection up front without a  
discussion first. If the authorisor rejects it, it is after due  
consideration of the initiators rationale. (Interesting parallels here  
- I certainly tried to ask questions up front before rejecting this  
stuff. Rest assured I have studied and considered this quite carefully  
too.)

We could also do with functionality which allows an authorised user to  
create purchase orders without having to go through the extra  
authorisation hoop - perhaps as a configuration option.

> Merely correcting the reports and inquiries is easy. I have already
> done it on the launchpad branch.

But look at the additional complexity of the SQL - this is wrong... it  
really doesn't need to be so hard. The LEFT JOINS and CASE WHEN ...  
what the!! Yes, it's clever - but unecessary. We really don't need the  
clever here - we need the data to be organised better.

Thinking about the units conversion problems, we really should only  
ever store quantities in our units ... this is why I need to rewrite  
the conversion stuff too. I'd be happier if you understood why though  
and took my points on board. I believe there was an email alerting us  
to these inconsistencies but I am afraid I didn't wake up to it. The  
code is becoming unmaintainable with SQL plasters on top of plasters  
and the data is septic underneath. I am thinking the answer is to  
store the conversion factor used in the purchorderdetails table.

> You will have to go into these
> reports at some point, as by ripping out my conversion factor code and
> not replacing it you have (re)introduced the bug that stops the on
> order quantity showing, making the reports nonsense anyway.
>
> For instance to make InventoryPlanning.php work correctly all you need
> to do is to replace the sql at line 264 with the following:
>
> $SQL = "SELECT SUM(purchorderdetails.quantityord*(CASE WHEN
> purchdata.conversionfactor IS NULL THEN 1 ELSE
> purchdata.conversionfactor END)
> - purchorderdetails.quantityrecd*(CASE WHEN
> purchdata.conversionfactor IS NULL THEN 1 ELSE
> purchdata.conversionfactor END)) as qtyonorder
> FROM purchorderdetails
> LEFT JOIN purchorders
> ON purchorderdetails.orderno = purchorders.orderno
> LEFT JOIN purchdata
> ON purchorders.supplierno=purchdata.supplierno
> AND purchorderdetails.itemcode=purchdata.stockid
> WHERE  purchorderdetails.itemcode = '" . $InventoryPlan['stockid'] . "'
> AND purchorderdetails.completed = 0
> AND purchorders.status <> 'Cancelled'
> AND purchorders.status <> 'Rejected'";
>
> This solves both problems very easily. I cannot see what your
> objection to this code can be. It seems to me you are rejecting a
> simple and elegant solution in favour of a complex and inflexible
> solution, or am I missing something?

Sorry Tim I disagree - we have serious data structure problems to end  
up with SQL like this.

The things I can't accept are that:

1. When searching for purchase orders by item, that cancelled and  
rejected orders should show - so you can select them to change their  
status to something else. They are gone - deleted never want to see  
them again - except perhaps when invetigating the arrival of stock or  
an invoice for goods which are not on order. I accept a utility script  
should allow for searching just deleted orders - in the deleted orders  
table.

2. That everywhere we look for the purchase order quantity outstanding  
- this is a whole raft of places that you would need to really hunt  
down - we would need would to have more complex SQL - looking through  
potentially a heap of redundant (cancelled and rejected) records.

3. We mixup data in the same tables. Each table must have data  
specific to its purpose. It is possible to have the gltrans inside  
purchorderdetails table and we just have more tricky sql to carve the  
table up at run time - it is stupid though obviously. This is the same  
principle underlying my concern for fixed assets and stock.

I am hoping you understand - maybe you will once I simplify stuff  
again. I'd really appreciate your help to set things straight.

Phil

>
> Thanks
> Tim
>
>
> On 13 December 2010 11:01, Phil Daintree <[hidden email]> wrote:
>> Thanks for being constructive Tim. I understand better what you were
>> trying to do.
>>
>> In principle I really do prefer to have the purchase orders that are no
>> longer purchase orders either because they are canceled or rejected
>> deleted from the purchase order tables. Thus enabling all the other
>> places where purchase orders are searched by item, for a plethora of
>> reasons to have nice simple sql to just the retrieve real orders. The
>> purchorders_deleted retains all the status and narrative of status
>> changes so this table can be queried or other scripts written to examine
>> what happened to deleted orders.
>>
>> By dealing correctly with the data up front, we speed up queries with
>> less criteria and less redundant records, we simplify the code in all
>> the other scripts that just need to know what is or was on order.
>>
>> You are absolutely right, the rejected orders need to be moved to the
>> purchorders_deleted table too.
>>
>> I think pending orders unauthorised are ok to show against items -
>> always assume the best that they will be authorised - only when we know
>> the order is not authorised (rejected) then should we delete in my view.
>>
>> You give my ego too much credit - I have often been amazed at the clever
>> stuff you come up with, but I have found that sometimes the clever stuff
>> is really unnecessary and doing the simple stuff upfront  makes
>> everything downstream so much easier.
>>
>> I just want webERP to retain the simplicity and efficiency that is
>> important to me and stated in the goals of the project.
>>
>> You well know that I am keen for someone else to step up and believe in
>> this stuff as I do.
>>
>> Phil
>>
>>>
>>> If the status of the order is changed to cancelled, either via the
>>> PO_Header.php or the PO_AuthoriseMyOrders.php scripts then the orders
>>> don't get transferred to your new tables. They only get moved if the
>>> "Cancel and Delete" button is pressed, so trunk is now inconsistent.
>>>
>>> If however your fix was properly implemented, when a user has created
>>> a purchase order, but their authoriser has elected to cancel it, then
>>> the order will appear to have magically disappeared and the original
>>> creator of the order will not know what has happened to it. Yes if
>>> they have direct access to the database and a knowledge of sql they
>>> can run a query against your new tables, but its not the same as being
>>> able to directly query the order via the interface to find out its
>>> history.
>>>
>>> Orders that are rejected will continue to be shown in the planning
>>> report under your fix. Should we have separate tables for rejected
>>> orders as well? It could also be argued that pending orders shouldn't
>>> be included until they are authorised, maybe they should have separate
>>> tables too?
>>>
>>> I know several companies that have written reports to analyse
>>> cancelled orders, and your "fix" has broken these as well.
>>>
>>> I know that the current design principle for webERP is to do the
>>> opposite of whatever Tim says, but I really do feel that on this one
>>> you need to set your ego to one side and look at what is best for
>>> webERP, and the users of webERP.
>>>
>>> Thanks
>>> Tim
>>>
>>>
>>> On 5 December 2010 06:48, Phil Daintree<[hidden email]>  wrote:
>>>> Just a heads up anyone using inventory planning reports, MRP or even the
>>>> stock status information .... a change in purchasing code I guess last
>>>> year or more ago, has led to purchase orders that are flagged as
>>>> deleted, unhelpfully remaining on order for the purposes of MRP and
>>>> planning.
>>>>
>>>> I am working on a fix.... apologies to anyone who has lost custom due to
>>>> stock outs as a result :-(
>>>>
>>>> Thanks Julie for pointing it out!!
>>>>
>>>> Phil
>>>>
>>>> ------------------------------------------------------------------------------
>>>> What happens now with your Lotus Notes apps - do you make another costly
>>>> upgrade, or settle for being marooned without product support?  
>>>> Time to move
>>>> off Lotus Notes and onto the cloud with Force.com, apps are  
>>>> easier to build,
>>>> use, and manage than apps on traditional platforms. Sign up for the Lotus
>>>> Notes Migration Kit to learn more. http://p.sf.net/sfu/salesforce-d2d
>>>> _______________________________________________
>>>> web-ERP-users mailing list
>>>> [hidden email]
>>>> https://lists.sourceforge.net/lists/listinfo/web-erp-users
>>>>
>>>
>>>
>>>
>>
>> ------------------------------------------------------------------------------
>> Oracle to DB2 Conversion Guide: Learn learn about native support for PL/SQL,
>> new data types, scalar functions, improved concurrency, built-in packages,
>> OCI, SQL*Plus, data movement tools, best practices and more.
>> http://p.sf.net/sfu/oracle-sfdev2dev
>> _______________________________________________
>> Web-erp-developers mailing list
>> [hidden email]
>> https://lists.sourceforge.net/lists/listinfo/web-erp-developers
>>
>
>
>
> --
> WebERP Africa Ltd
> +447710427049
> +256752963327
> +255784602561
> www.weberpafrica.com
>


------------------------------------------------------------------------------
Lotusphere 2011
Register now for Lotusphere 2011 and learn how
to connect the dots, take your collaborative environment
to the next level, and enter the era of Social Business.
http://p.sf.net/sfu/lotusphere-d2d
_______________________________________________
Web-erp-developers mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/web-erp-developers
If anyone is wondering about the persistently nasty comments made by Tim Schofield and wants the full story please see: http://timschofield.blogspot.com/ Hell hath no fury like a woman (or Tim) scorned
Reply | Threaded
Open this post in threaded view
|

Re: [webERP-users] Bug in Planning and MRP

TimSchofield5
This post was updated on .
Hi Phil,

On 14 December 2010 01:38, Phil Daintree <phil@logicworks.co.nz> wrote:
> Responses within ...
>
> Quoting Tim Schofield <tim@weberpafrica.com>:
>
>> For your solution to work you
>> need to at the very least provide scripts to enable a user to see what
>> has happened their orders (whether they have been cancelled or
>> rejected),
>
> OK - I'll do this too... basically a copy of the purchase order inquiry
> script I guess.

Of course the "complex" solution doesn't require the writing of extra
scripts, just a few small changes to the code :)

>
>> you also need a script to enable an authoriser to
>> re-instate an order. For instance your boss rejects your order, you go
>> to him and say "but boss if we dont have that order in the morning
>> production will stop" so boss says "ok you can have the order". Under
>> the current code that just works fine and an audit trail is kept of
>> what has happened. Under your code that simply can't happen without a
>> lot more work.
>>
>
> Well in organisations I've worked for, when presented with an order that
> seems wrong - a discussion happens at this point between authoriser and
> initiator - rather than a rejection up front without a discussion first. If
> the authorisor rejects it, it is after due consideration of the initiators
> rationale. (Interesting parallels here - I certainly tried to ask questions
> up front before rejecting this stuff. Rest assured I have studied and
> considered this quite carefully too.)

So you believe there are no circumstances in any organisations where
an authoriser may reject a requisition, and then agree to re-instate
it? Are you sure about this or just floundering around grasping at
straws to defend an increasingly impossible position?

>
> We could also do with functionality which allows an authorised user to
> create purchase orders without having to go through the extra authorisation
> hoop - perhaps as a configuration option.

Agreed, this would be a good use of your time. :-)

>
>> Merely correcting the reports and inquiries is easy. I have already
>> done it on the launchpad branch.
>
> But look at the additional complexity of the SQL - this is wrong... it
> really doesn't need to be so hard. The LEFT JOINS and CASE WHEN ... what
> the!! Yes, it's clever - but unecessary. We really don't need the clever
> here - we need the data to be organised better.

Phil, I have known you a long time, and I know you to be an
intelligent man. This statement is an insult to that intelligence.
Joining tables is a basic and fundamental part of relational
databases. To claim that joining tables is "too clever" for webERP is
clearly nonsense. Code such as this is common throughout webERP and
much of that was contributed by you. If you are really reduced to
claiming we shouldn't have table joins in our sql, then you have lost
the argument :-)

>
> Thinking about the units conversion problems, we really should only ever
> store quantities in our units ... this is why I need to rewrite the
> conversion stuff too. I'd be happier if you understood why though and took
> my points on board. I believe there was an email alerting us to these
> inconsistencies but I am afraid I didn't wake up to it. The code is becoming
> unmaintainable with SQL plasters on top of plasters and the data is septic
> underneath. I am thinking the answer is to store the conversion factor used
> in the purchorderdetails table.

Fortunately I woke up to the email, and corrected the code. However
you came along and took out the fix. You have said yourself that you
don't use webERP and so you probably don't see the harm in ripping out
working code as part of a personal vendetta. However many of us use
this code to run our businesses. Of course code can always be improved
- even yours can. Thats the beauty of open source. However if you are
going to take out code that works, you should replace it with code
that works. You sent me an email a while ago, I dont remember the
exact words but it was something like " was looking at the code and
saw some stuff on uom conversions that I didn't like, so I took it
out". Great. Where does that leave the people who were using that
code? You didn't issue one of your famous "heads up" emails to notify
people that you had broken their code, you just did it, and haven't as
yet replaced it. (BTW you never do issue these emails when it is you
that has broken the code, is this an oversight?) You have spoken a lot
about confidence recently, and if you are really serious about
regaining our confidence in you as a leader of this project then you
must show more respect to the users, and you must show more respect to
the developers.

>
>> You will have to go into these
>> reports at some point, as by ripping out my conversion factor code and
>> not replacing it you have (re)introduced the bug that stops the on
>> order quantity showing, making the reports nonsense anyway.
>>
>> For instance to make InventoryPlanning.php work correctly all you need
>> to do is to replace the sql at line 264 with the following:
>>
>>                        $SQL = "SELECT
>> SUM(purchorderdetails.quantityord*(CASE WHEN
>> purchdata.conversionfactor IS NULL THEN 1 ELSE
>> purchdata.conversionfactor END)
>>                                - purchorderdetails.quantityrecd*(CASE WHEN
>> purchdata.conversionfactor IS NULL THEN 1 ELSE
>> purchdata.conversionfactor END)) as qtyonorder
>>                                FROM purchorderdetails
>>                                LEFT JOIN purchorders
>>                                ON purchorderdetails.orderno =
>> purchorders.orderno
>>                                LEFT JOIN purchdata
>>                                ON
>> purchorders.supplierno=purchdata.supplierno
>>                                AND
>> purchorderdetails.itemcode=purchdata.stockid
>>                                WHERE  purchorderdetails.itemcode = '" .
>> $InventoryPlan['stockid'] . "'
>>                                AND purchorderdetails.completed = 0
>>                                AND purchorders.status <> 'Cancelled'
>>                                AND purchorders.status <> 'Rejected'";
>>
>> This solves both problems very easily. I cannot see what your
>> objection to this code can be. It seems to me you are rejecting a
>> simple and elegant solution in favour of a complex and inflexible
>> solution, or am I missing something?
>
> Sorry Tim I disagree - we have serious data structure problems to end up
> with SQL like this.
>
> The things I can't accept are that:
>
> 1. When searching for purchase orders by item, that cancelled and rejected
> orders should show - so you can select them to change their status to
> something else. They are gone - deleted never want to see them again -
> except perhaps when invetigating the arrival of stock or an invoice for
> goods which are not on order. I accept a utility script should allow for
> searching just deleted orders - in the deleted orders table.

So this should also apply to completed orders then? In fact the
logical end to you argument is that all the different statuses should
have their own tables, and the orders should be moved between them.

There are basically two ways to organise the data, one way would be to
keep the orders in the same table, and use sql to filter out those
orders that you need. The other way is to avoid filtering the data and
move the orders around between different tables depending on their
status. I know which one I think is the better and less complex
structure.

>
> 2. That everywhere we look for the purchase order quantity outstanding -
> this is a whole raft of places that you would need to really hunt down - we
> would need would to have more complex SQL - looking through potentially a
> heap of redundant (cancelled and rejected) records.

I have already hunted these down. It wasn't that complex actually.
Certainly whole heap less complex than your "simple" solution. Sorting
large datasets is exactly what a RDMS is supposed to do.

>
> 3. We mixup data in the same tables. Each table must have data specific to
> its purpose. It is possible to have the gltrans inside purchorderdetails
> table and we just have more tricky sql to carve the table up at run time -
> it is stupid though obviously. This is the same principle underlying my
> concern for fixed assets and stock.

I am sorry can you point me to where I suggested keeping gltrans
inside purchorderdetails. My suggestion is to keep all purchase orders
in two tables, one for headers and one for the line details. Your
suggestion is to have multiple tables for rejected, cancelled,
completed etc orders. In fact if you dont want to be "carving the
table at run time" surely each supplier should also have its own
separate table for their orders. Otherwise you are having to use
complex sql to carve it up. Do you not see the stupidity of your
suggestions?

>
> I am hoping you understand - maybe you will once I simplify stuff again. I'd
> really appreciate your help to set things straight.
>
> Phil
>

Tim

--
Course View Towers,
Plot 21 Yusuf Lule Road,
Kampala
T   +256 (0) 312 314 418
M +256 (0) 752 963 325
www.weberpafrica.com
@TimSchofield2
Blog: http://weberpafrica.blogspot.co.uk/

------------------------------------------------------------------------------
Lotusphere 2011
Register now for Lotusphere 2011 and learn how
to connect the dots, take your collaborative environment
to the next level, and enter the era of Social Business.
http://p.sf.net/sfu/lotusphere-d2d
_______________________________________________
Web-erp-developers mailing list
Web-erp-developers@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/web-erp-developers
For those wondering about the constant nastiness and abuse that Phil Daintree fires at me, the facts can be found here at http://weberpafrica.blogspot.co.uk/
Reply | Threaded
Open this post in threaded view
|

Re: [webERP-users] Bug in Planning and MRP

David Lamotte
Hi Guys,

As a passionate webErp user/developer I would like to share my concern
with the current situation.

The difference in opinion between having a 'simple' SQL statement and a
'simple' data model in inevitable I think given the stated development
philosophy of having "A major goal of webERP is to have the scripts
readable by business people" and "the coding conventions used by webERP
are not widely appreciated by computer scientists since logic is mixed
with presentation".

While these are admirable goals, I feel that the sophistication of the
current code is making these goals unworkable.

As I professional programmer I can say that webErp breaks many
development guidelines that have proven over time to produce readable,
reliable and  maintainable code. A major one is cohesion  - "cohesion is
a measure of how strongly-related or focused the responsibilities of a
single module are". By mixing data extraction, business logic and
presentation all in the one script makes each script try to do many
different functions. It also means that a single function appears in
many different scripts hence making changes very difficult and laborious
to change.

"Modules with high cohesion tend to be preferable because high cohesion
is associated with several desirable traits of software including
robustness, reliability, reusability, and understandability whereas low
cohesion is associated with undesirable traits such as being difficult
to maintain, difficult to test, difficult to reuse, and even difficult
to understand. Please see
http://en.wikipedia.org/wiki/Cohesion_%28computer_science%29 for a good
summary.

The 'correct' solution is to have a Purchase order class that produces
an object that knows how to add, authorise, print or list purchase
orders - but this is probably too big a change to achieve in the one step.

Perhaps the best solution to the current impasse would be to have a
library of purchase order functions where all the logic for dealing with
orders is contained. Scripts can then include this library and only need
to access functions like get_ all_open_orders() or authorise_order() so
that all the logic for dealing with orders is contained in the one file,
but available to all scripts. I can't see how you can get something more
'readable and understandable' than that.

As for the proposal to have a 'deleted orders' table - this is a very
inefficient data management approach. As an accountant and business
manager with 30 years experience, I know of many occasions where a
purchase requestion may have been initially rejected due to budgetary,
supplier or item concerns but after further discussion/explanation
approval is given.  I would think that having an audit trail of all
generated purchase orders would be a desirable thing as well.

Having separate tables requires the order data to move between tables
when the status changes - when really only the status field should
change.  Concerns about seemingly complex SQL statements being
slow/difficult to execute are also misplaced as this is something that
databases do well. It would seem that you are worrying about the speed
of executing a query before it is actually a problem. Query performance
is more a product of index coverage and other database tuning factors
rather than the complexity of the SQL statement. Database engines love
complex SQL statements as it allows the query to be broken up into a
number of simple tasks which can probably be executed in parallel eg one
thread will extract the list of active orders while others retrieve the
required data for each active order.

Anyway, I have gone on long enough. I am happy to help with any
development questions, but can only plead that the two 'fathers' of
webErp focus on setting a timeline and goals that we can all follow.

Thanks
David

--
Regards,

David Lamotte
Managing Consultant

Rams-Pro Technology Pty Ltd
ABN: 43 126 822 026

Phone: (02) 4021 1079
Fax:   (02) 8569 0320
Mobile: 0422 978 643
Int'l: +61 240 211 079
        +61 422 978 643
Web: http://rams-pro.com.au       

Disclaimer: This email and any attached files are intended solely for the named addressee, are confidential and may contain legally privileged information. The copying or distribution of them or any information they contain, by anyone other than the addressee, is prohibited. If you have received this email in error, please advise the sender by telephone or return the email before destroying all copies. Thank you.


------------------------------------------------------------------------------
Lotusphere 2011
Register now for Lotusphere 2011 and learn how
to connect the dots, take your collaborative environment
to the next level, and enter the era of Social Business.
http://p.sf.net/sfu/lotusphere-d2d
_______________________________________________
Web-erp-developers mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/web-erp-developers
Reply | Threaded
Open this post in threaded view
|

Re: [webERP-users] Bug in Planning and MRP

phildaintree
Hi David,

Interesting post....

As noted webERP does not follow traditional software development 'best
practice'. However, I think we have always been able to pick up a script
and follow it easily due to the long variable names, simple consistent
syntax, simple baby SQL and having all the code in the one place. I have
always thought of webERP as being fantastically maintainable as a result
- it is mostly so easy to find/see bugs.

Perhaps I am alone in this, but I hope not? It has been most gratifying
to see new developers pick it up so quickly.

Also thinking carefully about the data required and the structure of the
data required for a given function has ensured we have a supremely
efficient application. I am always careful to avoid duplicate round
trips with SQL calls - not because the RDMS or hardware might struggle -
but out of a desire for ultimate efficiency. When you look at the data
in any table the table name describes pretty well what is in there. I
believe it is this care and attention to detail that underpins our great
application. I object to the injection of additional ad-hoc SQL calls,
because we don't have the data there at the time - the simple quick fix
is to throw in another SQL call. Rather we should think again about how
the data is retrieved and retrieve it in one or two trips - and store it
in the object/class for when we need it. When I see code that is going
back to the DB more than once for the same data then alarm bells go off
with me - the functionality and data requirements have not been thought
through properly.

> The 'correct' solution is to have a Purchase order class that produces
> an object that knows how to add, authorise, print or list purchase
> orders - but this is probably too big a change to achieve in the one step.
>

Of course we do actually have a purchase order object, although it is
not used quite as the complete black-box object you refer to - it knows
how to add and delete order lines at least.

> Perhaps the best solution to the current impasse would be to have a
> library of purchase order functions where all the logic for dealing with
> orders is contained. Scripts can then include this library and only need
> to access functions like get_ all_open_orders() or authorise_order() so
> that all the logic for dealing with orders is contained in the one file,
> but available to all scripts. I can't see how you can get something more
> 'readable and understandable' than that.
>

I am certainly not keen to reinvent the wheel here and I believe the
coding methodology and conventions used pretty well throughout have
proven (at least to me!) to be well grounded. In most cases I am happy
that webERP scripts could not be much more 'readable and
understandable'. There are obviously a couple of exceptions....

> As for the proposal to have a 'deleted orders' table - this is a very
> inefficient data management approach. As an accountant and business
> manager with 30 years experience, I know of many occasions where a
> purchase requestion may have been initially rejected due to budgetary,
> supplier or item concerns but after further discussion/explanation
> approval is given.  I would think that having an audit trail of all
> generated purchase orders would be a desirable thing as well.
>

I am rethinking this ... since my proposal has caused such ructions... I
may well be wrong.

My real concern is that the purchorders table is meant to contain
purchase orders. I really don't see canceled purchase orders or rejected
purchase orders as purchase orders at all and therefore in my simple
'call it what it is world' they have no place in the purchorders table
(similarly with purhorderdetails). When is a purchase order not a
purchase order .... when it's canceled/rejected.

I also recognise, as you do, the requirement for a trail of evidence and
the wisdom of retaining canceled/rejected purchase orders and therefore
my logical solution is to have a table of purchorders_deleted. If one
day an invoice turns up for goods which have never been on order - the
purchorders_deleted and the status change comments etc may well reveal
the perpetrator. So an inquiry on purchorders_deleted is required - for
the odd occasion when it might be necessary.

Yes we can make the purchorders table cover canceled and rejected
orders, we could have all sorts of other records in there too, but isn't
this confusing? Isn't this counter to proper normalisation of data?

Exploring the authorisation logic a little further, can anyone explain
why we have both canceled and rejected purchase orders and how do they
differ? There maybe a good reason, I suppose rejected by a manager is
slightly different to being canceled - perhaps the supplier couldn't
supply so an alternative was used, but we have the narrative status
comments to record the reasons for cancellation/rejection?

Do we need a separate cancel order button and also a separate way to
change the status to canceled - then another to change to rejected.
Perhaps just a single method to cancel/reject?

Whilst I wasn't proposing to write code to allow a cancelled order to be
reinstated - this would certainly be possible. It would seem more
efficient simply not to authorise an order as a manager and ask for
information to justify it before canceling/rejecting. I don't think this
is too much of a leap.

> Having separate tables requires the order data to move between tables
> when the status changes - when really only the status field should
> change.  Concerns about seemingly complex SQL statements being
> slow/difficult to execute are also misplaced as this is something that
> databases do well. It would seem that you are worrying about the speed
> of executing a query before it is actually a problem.

This is pretty marginal point of course, it is more the principle of
making the SQL more complex than it needs to be with normalised data.
Probably not so much the speed of the query as the compromised super
efficiency of the application and the ease of reading baby SQL with the
least criteria.

Query performance
> is more a product of index coverage and other database tuning factors
> rather than the complexity of the SQL statement. Database engines love
> complex SQL statements as it allows the query to be broken up into a
> number of simple tasks which can probably be executed in parallel eg one
> thread will extract the list of active orders while others retrieve the
> required data for each active order.

But I am always trying to keep it as simple as possible - so the
database engine has less to do.

The coding conventions have been around along time and I can't see why
they need to change - although happy to consider any amendments:

http://www.weberp.org/ContributingtowebERP

There have been a number of these types of emails over the years which
possibly also serve to clarify things.

Thanks for your input David.

Phil

------------------------------------------------------------------------------
Lotusphere 2011
Register now for Lotusphere 2011 and learn how
to connect the dots, take your collaborative environment
to the next level, and enter the era of Social Business.
http://p.sf.net/sfu/lotusphere-d2d
_______________________________________________
Web-erp-developers mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/web-erp-developers
If anyone is wondering about the persistently nasty comments made by Tim Schofield and wants the full story please see: http://timschofield.blogspot.com/ Hell hath no fury like a woman (or Tim) scorned
Reply | Threaded
Open this post in threaded view
|

Re: [webERP-users] Bug in Planning and MRP

TimSchofield5
This post was updated on .
Phil,

You are missing the point that people are trying to tell you. A
purchase order that has a status of rejected is still a purchase
order. Yesterday you said that it was like putting gltrans in the
purchase orders table. This is clearly nonsense.

What is more your change goes against all the design principles that
have been used in webERP before. Take sales orders. Over time 95%+ of
the records in the salesorders table are of completed orders. They are
no longer active open sales orders. Redundant data in your
terminology. Every time a script looks for a sales order it will
search through all of these records. This is fine, as the RDBMS is
optimised to do this, but you must use "complex" sql to sort the
table. This code was written by you. The same is true in the work
orders code, all the work orders are kept in one place regardless of
the status. In fact thats the design principle that runs through all
of webERP. Now this has changed.

Its strange that we discussed this in private, I pleaded with you not
to split the purchase order tables up. Within an hour you had
committed the change that badly implemented this split. Why did you do
this?

Anyway its good that you are reconsidering this,
Tim


On 15 December 2010 09:31, Phil Daintree <phil@logicworks.co.nz> wrote:
> Hi David,
>
> Interesting post....
>
> As noted webERP does not follow traditional software development 'best
> practice'. However, I think we have always been able to pick up a script
> and follow it easily due to the long variable names, simple consistent
> syntax, simple baby SQL and having all the code in the one place. I have
> always thought of webERP as being fantastically maintainable as a result
> - it is mostly so easy to find/see bugs.
>
> Perhaps I am alone in this, but I hope not? It has been most gratifying
> to see new developers pick it up so quickly.
>
> Also thinking carefully about the data required and the structure of the
> data required for a given function has ensured we have a supremely
> efficient application. I am always careful to avoid duplicate round
> trips with SQL calls - not because the RDMS or hardware might struggle -
> but out of a desire for ultimate efficiency. When you look at the data
> in any table the table name describes pretty well what is in there. I
> believe it is this care and attention to detail that underpins our great
> application. I object to the injection of additional ad-hoc SQL calls,
> because we don't have the data there at the time - the simple quick fix
> is to throw in another SQL call. Rather we should think again about how
> the data is retrieved and retrieve it in one or two trips - and store it
> in the object/class for when we need it. When I see code that is going
> back to the DB more than once for the same data then alarm bells go off
> with me - the functionality and data requirements have not been thought
> through properly.
>
>> The 'correct' solution is to have a Purchase order class that produces
>> an object that knows how to add, authorise, print or list purchase
>> orders - but this is probably too big a change to achieve in the one step.
>>
>
> Of course we do actually have a purchase order object, although it is
> not used quite as the complete black-box object you refer to - it knows
> how to add and delete order lines at least.
>
>> Perhaps the best solution to the current impasse would be to have a
>> library of purchase order functions where all the logic for dealing with
>> orders is contained. Scripts can then include this library and only need
>> to access functions like get_ all_open_orders() or authorise_order() so
>> that all the logic for dealing with orders is contained in the one file,
>> but available to all scripts. I can't see how you can get something more
>> 'readable and understandable' than that.
>>
>
> I am certainly not keen to reinvent the wheel here and I believe the
> coding methodology and conventions used pretty well throughout have
> proven (at least to me!) to be well grounded. In most cases I am happy
> that webERP scripts could not be much more 'readable and
> understandable'. There are obviously a couple of exceptions....
>
>> As for the proposal to have a 'deleted orders' table - this is a very
>> inefficient data management approach. As an accountant and business
>> manager with 30 years experience, I know of many occasions where a
>> purchase requestion may have been initially rejected due to budgetary,
>> supplier or item concerns but after further discussion/explanation
>> approval is given.  I would think that having an audit trail of all
>> generated purchase orders would be a desirable thing as well.
>>
>
> I am rethinking this ... since my proposal has caused such ructions... I
> may well be wrong.
>
> My real concern is that the purchorders table is meant to contain
> purchase orders. I really don't see canceled purchase orders or rejected
> purchase orders as purchase orders at all and therefore in my simple
> 'call it what it is world' they have no place in the purchorders table
> (similarly with purhorderdetails). When is a purchase order not a
> purchase order .... when it's canceled/rejected.
>
> I also recognise, as you do, the requirement for a trail of evidence and
> the wisdom of retaining canceled/rejected purchase orders and therefore
> my logical solution is to have a table of purchorders_deleted. If one
> day an invoice turns up for goods which have never been on order - the
> purchorders_deleted and the status change comments etc may well reveal
> the perpetrator. So an inquiry on purchorders_deleted is required - for
> the odd occasion when it might be necessary.
>
> Yes we can make the purchorders table cover canceled and rejected
> orders, we could have all sorts of other records in there too, but isn't
> this confusing? Isn't this counter to proper normalisation of data?
>
> Exploring the authorisation logic a little further, can anyone explain
> why we have both canceled and rejected purchase orders and how do they
> differ? There maybe a good reason, I suppose rejected by a manager is
> slightly different to being canceled - perhaps the supplier couldn't
> supply so an alternative was used, but we have the narrative status
> comments to record the reasons for cancellation/rejection?
>
> Do we need a separate cancel order button and also a separate way to
> change the status to canceled - then another to change to rejected.
> Perhaps just a single method to cancel/reject?
>
> Whilst I wasn't proposing to write code to allow a cancelled order to be
> reinstated - this would certainly be possible. It would seem more
> efficient simply not to authorise an order as a manager and ask for
> information to justify it before canceling/rejecting. I don't think this
> is too much of a leap.
>
>> Having separate tables requires the order data to move between tables
>> when the status changes - when really only the status field should
>> change.  Concerns about seemingly complex SQL statements being
>> slow/difficult to execute are also misplaced as this is something that
>> databases do well. It would seem that you are worrying about the speed
>> of executing a query before it is actually a problem.
>
> This is pretty marginal point of course, it is more the principle of
> making the SQL more complex than it needs to be with normalised data.
> Probably not so much the speed of the query as the compromised super
> efficiency of the application and the ease of reading baby SQL with the
> least criteria.
>
> Query performance
>> is more a product of index coverage and other database tuning factors
>> rather than the complexity of the SQL statement. Database engines love
>> complex SQL statements as it allows the query to be broken up into a
>> number of simple tasks which can probably be executed in parallel eg one
>> thread will extract the list of active orders while others retrieve the
>> required data for each active order.
>
> But I am always trying to keep it as simple as possible - so the
> database engine has less to do.
>
> The coding conventions have been around along time and I can't see why
> they need to change - although happy to consider any amendments:
>
> http://www.weberp.org/ContributingtowebERP
>
> There have been a number of these types of emails over the years which
> possibly also serve to clarify things.
>
> Thanks for your input David.
>
> Phil
>
> ------------------------------------------------------------------------------
> Lotusphere 2011
> Register now for Lotusphere 2011 and learn how
> to connect the dots, take your collaborative environment
> to the next level, and enter the era of Social Business.
> http://p.sf.net/sfu/lotusphere-d2d
> _______________________________________________
> Web-erp-developers mailing list
> Web-erp-developers@lists.sourceforge.net
> https://lists.sourceforge.net/lists/listinfo/web-erp-developers
>



--
Course View Towers,
Plot 21 Yusuf Lule Road,
Kampala
T   +256 (0) 312 314 418
M +256 (0) 752 963 325
www.weberpafrica.com
@TimSchofield2
Blog: http://weberpafrica.blogspot.co.uk/

------------------------------------------------------------------------------
Lotusphere 2011
Register now for Lotusphere 2011 and learn how
to connect the dots, take your collaborative environment
to the next level, and enter the era of Social Business.
http://p.sf.net/sfu/lotusphere-d2d
_______________________________________________
Web-erp-developers mailing list
Web-erp-developers@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/web-erp-developers
For those wondering about the constant nastiness and abuse that Phil Daintree fires at me, the facts can be found here at http://weberpafrica.blogspot.co.uk/
Reply | Threaded
Open this post in threaded view
|

RE: [webERP-users] Bug in Planning and MRP

Mark
This post has NOT been accepted by the mailing list yet.
In reply to this post by phildaintree

Hi Phil

 

Conceptually I agree with both David and Tim – moving deleted purchase orders out of the purchase orders table does not make sense. In my experience a purchase orders may be in a number of states including, raised authorised cancelled rejected and deleted. Queries on Purchase orders may need to include one or more states depending on the requirement. The same is true of many entities for examples stock items may be active or inactive but you would never consider removing an inactive stock item from the items table.

 

Mark

 

From: Phil Daintree [via webERP accounting] [mailto:[hidden email]]
Sent: 12/15/2010 08:28
To: Mark
Subject: Re: [webERP-users] Bug in Planning and MRP

 

Hi David,

Interesting post....

As noted webERP does not follow traditional software development 'best
practice'. However, I think we have always been able to pick up a script
and follow it easily due to the long variable names, simple consistent
syntax, simple baby SQL and having all the code in the one place. I have
always thought of webERP as being fantastically maintainable as a result
- it is mostly so easy to find/see bugs.

Perhaps I am alone in this, but I hope not? It has been most gratifying
to see new developers pick it up so quickly.

Also thinking carefully about the data required and the structure of the
data required for a given function has ensured we have a supremely
efficient application. I am always careful to avoid duplicate round
trips with SQL calls - not because the RDMS or hardware might struggle -
but out of a desire for ultimate efficiency. When you look at the data
in any table the table name describes pretty well what is in there. I
believe it is this care and attention to detail that underpins our great
application. I object to the injection of additional ad-hoc SQL calls,
because we don't have the data there at the time - the simple quick fix
is to throw in another SQL call. Rather we should think again about how
the data is retrieved and retrieve it in one or two trips - and store it
in the object/class for when we need it. When I see code that is going
back to the DB more than once for the same data then alarm bells go off
with me - the functionality and data requirements have not been thought
through properly.

> The 'correct' solution is to have a Purchase order class that produces
> an object that knows how to add, authorise, print or list purchase
> orders - but this is probably too big a change to achieve in the one step.
>

Of course we do actually have a purchase order object, although it is
not used quite as the complete black-box object you refer to - it knows
how to add and delete order lines at least.

> Perhaps the best solution to the current impasse would be to have a
> library of purchase order functions where all the logic for dealing with
> orders is contained. Scripts can then include this library and only need
> to access functions like get_ all_open_orders() or authorise_order() so
> that all the logic for dealing with orders is contained in the one file,
> but available to all scripts. I can't see how you can get something more
> 'readable and understandable' than that.
>

I am certainly not keen to reinvent the wheel here and I believe the
coding methodology and conventions used pretty well throughout have
proven (at least to me!) to be well grounded. In most cases I am happy
that webERP scripts could not be much more 'readable and
understandable'. There are obviously a couple of exceptions....

> As for the proposal to have a 'deleted orders' table - this is a very
> inefficient data management approach. As an accountant and business
> manager with 30 years experience, I know of many occasions where a
> purchase requestion may have been initially rejected due to budgetary,
> supplier or item concerns but after further discussion/explanation
> approval is given.  I would think that having an audit trail of all
> generated purchase orders would be a desirable thing as well.
>

I am rethinking this ... since my proposal has caused such ructions... I
may well be wrong.

My real concern is that the purchorders table is meant to contain
purchase orders. I really don't see canceled purchase orders or rejected
purchase orders as purchase orders at all and therefore in my simple
'call it what it is world' they have no place in the purchorders table
(similarly with purhorderdetails). When is a purchase order not a
purchase order .... when it's canceled/rejected.

I also recognise, as you do, the requirement for a trail of evidence and
the wisdom of retaining canceled/rejected purchase orders and therefore
my logical solution is to have a table of purchorders_deleted. If one
day an invoice turns up for goods which have never been on order - the
purchorders_deleted and the status change comments etc may well reveal
the perpetrator. So an inquiry on purchorders_deleted is required - for
the odd occasion when it might be necessary.

Yes we can make the purchorders table cover canceled and rejected
orders, we could have all sorts of other records in there too, but isn't
this confusing? Isn't this counter to proper normalisation of data?

Exploring the authorisation logic a little further, can anyone explain
why we have both canceled and rejected purchase orders and how do they
differ? There maybe a good reason, I suppose rejected by a manager is
slightly different to being canceled - perhaps the supplier couldn't
supply so an alternative was used, but we have the narrative status
comments to record the reasons for cancellation/rejection?

Do we need a separate cancel order button and also a separate way to
change the status to canceled - then another to change to rejected.
Perhaps just a single method to cancel/reject?

Whilst I wasn't proposing to write code to allow a cancelled order to be
reinstated - this would certainly be possible. It would seem more
efficient simply not to authorise an order as a manager and ask for
information to justify it before canceling/rejecting. I don't think this
is too much of a leap.

> Having separate tables requires the order data to move between tables
> when the status changes - when really only the status field should
> change.  Concerns about seemingly complex SQL statements being
> slow/difficult to execute are also misplaced as this is something that
> databases do well. It would seem that you are worrying about the speed
> of executing a query before it is actually a problem.

This is pretty marginal point of course, it is more the principle of
making the SQL more complex than it needs to be with normalised data.
Probably not so much the speed of the query as the compromised super
efficiency of the application and the ease of reading baby SQL with the
least criteria.

Query performance
> is more a product of index coverage and other database tuning factors
> rather than the complexity of the SQL statement. Database engines love
> complex SQL statements as it allows the query to be broken up into a
> number of simple tasks which can probably be executed in parallel eg one
> thread will extract the list of active orders while others retrieve the
> required data for each active order.

But I am always trying to keep it as simple as possible - so the
database engine has less to do.

The coding conventions have been around along time and I can't see why
they need to change - although happy to consider any amendments:

http://www.weberp.org/ContributingtowebERP

There have been a number of these types of emails over the years which
possibly also serve to clarify things.

Thanks for your input David.

Phil

------------------------------------------------------------------------------
Lotusphere 2011
Register now for Lotusphere 2011 and learn how
to connect the dots, take your collaborative environment
to the next level, and enter the era of Social Business.
http://p.sf.net/sfu/lotusphere-d2d
_______________________________________________
Web-erp-developers mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/web-erp-developers


View message @ http://weberp-accounting.1478800.n4.nabble.com/Re-webERP-users-Bug-in-Planning-and-MRP-tp3085185p3088570.html
To start a new topic under web-erp-developers, email [hidden email]
To unsubscribe from web-erp-developers, click here.