web-erp-developers — Discussion on development of webERP

Re: [Web-erp-developers] Serialised stock - how to code this up

[Daintree <p.d...@pa...>]

Appreciate all input ...

We could add these fields to LocStock quite easily and perhaps a field or
two in StockMaster for DaysToHold, DaysToSpoil to auto calculate these based
on the date of the goods arriving - just an extension of the
goodsReceived.php script.

Gosh bin locations too!
I fear its a good point....

Maybe warehousing and bin locations should be a seperate exercise though
since we would wish to have bin locations for controlled and uncontrolled
stock (inventory). With potentially several bin locations holding one
controlled item. eg a batch of  polypropylene granuals may be stored in
several hoppers. So the ContentsOfBins table is joined on the StockID as a
many to one with the LocStock table.

Phil
----- Original Message -----
From: "Danie Brink" <br...@na...>
To: "Phil Daintree" <Web...@li...>
Sent: Wednesday, March 24, 2004 9:15 PM
Subject: Re: [Web-erp-developers] Serialised stock - how to code this up


> Hi Phil
>
> Sorry for jumping in here Phil, while you are thinking of adding
> batches, why not add hold until and spoil dates and bin ids to the
> batches as this is used by retail warehouses, to maintain their batch
> stock. I realize this would have to allow for transfer of stock from bin
> to bin as well, however no journal entries or stock valuation
> functionality would be required for movement inside a warehouse. There
> is a valuation issue for spoilable goods though as they should be
> devalued or scraped and would be seen as a loss to the company.
>
> Kind Regards
> Danie Brink br...@na...
>
>
>
>
> On Wed, 2004-03-24 at 10:23, Daintree wrote:
> > Hi Jessie,
> >
> > We are thinking along the same lines.
> >
> > Sit back grab a coffee ... this is a biggie!
> >
> > The stock master contains info about the item ....
> >
> > We already have a field to define if we want to reference items
individually
> > (controlled) or not.
> >
> > However we also need to define the level of control. If it is
controlled -
> > then how many items per bundle, lot or serial number. Lets call this
field
> > QtyControl
> >
> > If it is only one item allowed per lot then this is serial number
control -
> > one reference per item.
> > In some businesses it could also be that up to 1000 kgs are allowed in a
> > batch of cheese production then a food manufacturer would be able to
trace
> > where the batch was received and sold to for quality control purposes.
> > If up to 150 metres of cloth could be on a roll - a clothing
manufacturer
> > would want to trace where each roll of cloth was used.
> >
> > So you see the two fields Controlled and QtyControl allow the item to be
> > serialised - ie a reference for every unit of the item, or a reference
that
> > can cover up to 999999999 units of the item - or whatever the limit of
an
> > int data type  is.
> >
> > Also, while we are adding fields to the stock master - the number of
decimal
> > places to display would be a cool addition to the feature set.
> >
> > When items are received ..... the stock master is examined to see if the
> > item is controlled or not ... if its controlled then we need to use the
new
> > script to put the stock in and enter the reference for the bundle lot or
> > serial number - with reference to the stock master QtyControl - if its 1
> > (serialised stock) then the quantity would not need to be displayed it
would
> > be defaulted to 1 for each reference (serial number) entered. If its
more
> > than 1 then input of the quantity in the lot would be required too.
Perhaps
> > this screen should show a table of  reference text inputs and
quantities.
> >
> > Need to check for not pre-existing references and quantities less than
or
> > equal to QtyControl.
> >
> > When items are (purchase) ordered - then there is no need for lot
control
> > (is there?) since you don't know the lot until the order turns up at the
> > warehouse gates.
> >
> > When items are sold - at the Confirm_DispatchInvoice.php stage the
> > quantities and lot numbers would need to be noted by the storeman/picker
and
> > would need to entered in this script for controlled items - with
quantities
> > (if not serialised ie QtyControl ==1). When the invoice is created it
> > creates a stockmove for each lot with the quantities entered - we
already
> > have the bundle field to record the lot (or bundle or serial number) of
the
> > item sold.
> >
> > The updates to the LocStock would need modification since LocStock table
now
> > needs a Bundle field.
> > the LocStock table contains information about quantities at each stock
> > location - and it should also show the quantities in each bundle - for
> > serialised items each quantity would be 1 - and the LocStock record for
the
> > bundle would be deleted when the item/serial number is invoiced or
> > transferred to another location.
> >
> > If controlled==1 then the StockStatus inquiry needs to show the bundles
of
> > stock held arriving at a total at each location. Or at least an option
to
> > show the bundles.
> >
> > Since the stockmoves are created for every serialised item then the
stock
> > movement inquiry will show the full detail for each bundle - we could
even
> > show just the movements relating to a specific bundle - coming in and
going
> > out. The invoices and credit notes would also display - the movements
> > relating to the invoice - serial number by serial number.
> >
> > So I am proposing ....
> >
> > New fields in StockMaster:
> >
> > QtyControlled  int
> > DecimalPlaces tinyint
> >
> > New fields in LocStock
> >
> > Bundle varchar(50)
> >
> > Modification to the following scripts:
> >
> > GoodsReceived.php
> >
> > If controlled we need an additional form to enter bundle refs and qties
or
> > just serial numbers if QtyControl==1 then insert the multiple stock
moves
> > per item being received with the bundle ref against StockMoves.Bundle.
We
> > need to be able to cache the data in an array of Bundles which is a
child of
> > the item information in a similar way to the Item->LineItems->Bundle
> >
> > Inserts into LocStock for new bundles with quantities in each.
> >
> >  ConfirmDispatch_Invoice.php
> >
> > if controlled need form to enter bundle refs. - need some array off the
> > Items->Bundles to hold quantities and bundle refs. Then insert stock
> > movements with bundle reference. May need to delete LocStock records if
the
> > quantity of a controlled item gets to 0. Total quantity per line item
needs
> > to be maintained for most of the other updates as usual.
> >
> > We get into difficulties if a component of an assembly item is
controlled -
> > we should dissallow this on the BOM.php script and check for it when an
item
> > is modified to become controlled ie any parent parts that are
assemblies.
> > Otherwise we would have to specify the bundle refs for assembly
components
> > being invoiced - this kind of defeats the value of assemblies.
> >
> >  CreditInvoice.php
> >
> > if controlled items coming back into stock the bundle refs could be read
off
> > the stock movements from the original invoice - need some array off the
> > Items->LineItems->Bundles to hold quantities and bundle refs. Then
insert
> > stock movements for goods coming back in with bundle reference. Total
> > quantity per line item needs to be maintained for most of the other
updates
> > as usual. LocStock inserts if the bundle doesn't exist.
> >
> >  SelectCreditItems.php
> >
> > if controlled need form to enter bundle refs. for goods coming back into
> > stock these need to be entered in a similar way to
> > ConfirmDispatch_invoice.php - the same array off the
> > Items->LineItems->Bundles to hold quantities and bundle refs used again.
> > Then insert stock movements with bundle reference. Total quantity per
line
> > item needs to be maintained for most of the other updates as usual.
> >
> > StockTransfers.php
> >
> > if controlled need a form to enter the bundle refs and quantites (if
> > QtyControl>1) StockMoves created with bundle ref.
> >
> > StockAdjustments.php
> >
> > if controlled entry of bundle ref and checking QtyControl - defaulting
qty
> > to 1 where QtyControl ==1 and not displaying the quantity field or
perhaps
> > the adjustment quantity could be entered in total and a form for entry
of
> > the bundle refs (or serial numbers) of all items being adjusted and
> > quantities being adjusted from each bundle (s/n)
> >
> > All the Stock Check scripts need to allow entry/reporting of bundle refs
> > too. QtyControl =1 will just need to list the s/n of each item no
quantity
> > required. QtyControl>1 will need the quantity shown next to each
reference.
> >
> > StockStatus.php
> > Display option to show bundles/lots/serial numbers of amounts held. SQL
will
> > need to sum(Qty) WHERE LocCode='$Location' AND StockID='$StockID'  -
'cos
> > LocStock now contains (potentially) multiple records for each
> > item/location - this table now holds the total stock of the
> > bundle/lot/serial number (1).
> >
> > ReOrderLevel.php
> > For controlled items i think we should still maintain a generic serial
> > number 1 item with a quantity of 0 that has the re-order level in it.
The
> > re-order level can then be determined. Some of the SQL in other scripts
> > depends on a LocStock record existing for an item in each location even
> > though the quantity may be 0.
> >
> > StockValuation reports - detail will need to have an option to show the
> > detail of controlled items - listing the references (s/n) and quantities
in
> > each lot - this will be 1 for s/n items.
> >
> > PlanningReport needs to sum the quantity at the location accross all
> > bundles.
> >
> > I am not sure about any issues in having many millions of stockmoves -
SCSI
> > disk size/speed/RAID and plenty of additional RAM to deal with the data.
The
> > script limits the data returned by date for stockmoves inquiries by
date -
> > retrieving moves for invoices and credits might slow from less than a
milli
> > second to a tenth or something. It would need some trial - with
hopefully
> > minimal error. The performance of the system is really quite good.
Having
> > additional tables with links etc would probably be slower than one
biggie.
> > Indexing is also a critical one to watch.
> >
> > Sorry this is a biggie - this is how I did serial number control in a
> > previous client server SQL Server system - it worked well. Variable
batch
> > control is a substantial undertaking and will trash the system
throughout
> > until we get it stable. It does add a fair bit of complexity to scripts
and
> > is not a project for the feint of heart!
> >
> > Are you going to take it on ?
> >
> > Phil
> >
> > ----- Original Message -----
> > From: "jesse" <je...@st...>
> > To: <web...@li...>
> > Sent: Wednesday, March 24, 2004 5:49 PM
> > Subject: RE: [Web-erp-developers] Serialised stock - how to code this up
> >
> >
> > > I definitely see how just using StockMaster will work. I'm trying to
make
> > it
> > > work better in environments with large stock needs. Really, I could
load
> > > over several million items into this... think about all those stock
moves.
> > > I think both our thoughts will work together together fine, though.
What
> > if
> > > we made StockMaster types both serialised and controlled? - controlled
> > would
> > > indicate that a bundle reference (stockmoves.bundle) is to be
associated
> > > with it, serialised would indicate that both a bundle ref. and
> > > individualised serial numbers existed. best of both worlds? Maybe you
> > could
> > > bounce a couple scenarios off me to make sure I'm not forgetting
> > something?
> > > also, what about the methods of importing all those items that I
> > mentioned?
> > > or printing an invoice w/ even a few thousand items?
> > >
> > >
> > > jesse
> > >
> >
> >
> >
> > -------------------------------------------------------
> > This SF.Net email is sponsored by: IBM Linux Tutorials
> > Free Linux tutorial presented by Daniel Robbins, President and CEO of
> > GenToo technologies. Learn everything from fundamentals to system
> > administration.http://ads.osdn.com/?ad_id=1470&alloc_id=3638&op=click
> > _______________________________________________
> > Web-erp-developers mailing list
> > Web...@li...
> > https://lists.sourceforge.net/lists/listinfo/web-erp-developers
> >
>
>
>
> -------------------------------------------------------
> This SF.Net email is sponsored by: IBM Linux Tutorials
> Free Linux tutorial presented by Daniel Robbins, President and CEO of
> GenToo technologies. Learn everything from fundamentals to system
> administration.http://ads.osdn.com/?ad_id=1470&alloc_id=3638&op=click
> _______________________________________________
> Web-erp-developers mailing list
> Web...@li...
> https://lists.sourceforge.net/lists/listinfo/web-erp-developers
>

Re: [Web-erp-developers] Serialised stock - how to code this up

[Danie B. <br...@na...>]

Hi Phil

Sorry for jumping in here Phil, while you are thinking of adding
batches, why not add hold until and spoil dates and bin ids to the
batches as this is used by retail warehouses, to maintain their batch
stock. I realize this would have to allow for transfer of stock from bin
to bin as well, however no journal entries or stock valuation
functionality would be required for movement inside a warehouse. There
is a valuation issue for spoilable goods though as they should be
devalued or scraped and would be seen as a loss to the company.

Kind Regards
Danie Brink br...@na...




On Wed, 2004-03-24 at 10:23, Daintree wrote:
> Hi Jessie,
> 
> We are thinking along the same lines.
> 
> Sit back grab a coffee ... this is a biggie!
> 
> The stock master contains info about the item ....
> 
> We already have a field to define if we want to reference items individually
> (controlled) or not.
> 
> However we also need to define the level of control. If it is controlled -
> then how many items per bundle, lot or serial number. Lets call this field
> QtyControl
> 
> If it is only one item allowed per lot then this is serial number control -
> one reference per item.
> In some businesses it could also be that up to 1000 kgs are allowed in a
> batch of cheese production then a food manufacturer would be able to trace
> where the batch was received and sold to for quality control purposes.
> If up to 150 metres of cloth could be on a roll - a clothing manufacturer
> would want to trace where each roll of cloth was used.
> 
> So you see the two fields Controlled and QtyControl allow the item to be
> serialised - ie a reference for every unit of the item, or a reference that
> can cover up to 999999999 units of the item - or whatever the limit of an
> int data type  is.
> 
> Also, while we are adding fields to the stock master - the number of decimal
> places to display would be a cool addition to the feature set.
> 
> When items are received ..... the stock master is examined to see if the
> item is controlled or not ... if its controlled then we need to use the new
> script to put the stock in and enter the reference for the bundle lot or
> serial number - with reference to the stock master QtyControl - if its 1
> (serialised stock) then the quantity would not need to be displayed it would
> be defaulted to 1 for each reference (serial number) entered. If its more
> than 1 then input of the quantity in the lot would be required too. Perhaps
> this screen should show a table of  reference text inputs and quantities.
> 
> Need to check for not pre-existing references and quantities less than or
> equal to QtyControl.
> 
> When items are (purchase) ordered - then there is no need for lot control
> (is there?) since you don't know the lot until the order turns up at the
> warehouse gates.
> 
> When items are sold - at the Confirm_DispatchInvoice.php stage the
> quantities and lot numbers would need to be noted by the storeman/picker and
> would need to entered in this script for controlled items - with quantities
> (if not serialised ie QtyControl ==1). When the invoice is created it
> creates a stockmove for each lot with the quantities entered - we already
> have the bundle field to record the lot (or bundle or serial number) of the
> item sold.
> 
> The updates to the LocStock would need modification since LocStock table now
> needs a Bundle field.
> the LocStock table contains information about quantities at each stock
> location - and it should also show the quantities in each bundle - for
> serialised items each quantity would be 1 - and the LocStock record for the
> bundle would be deleted when the item/serial number is invoiced or
> transferred to another location.
> 
> If controlled==1 then the StockStatus inquiry needs to show the bundles of
> stock held arriving at a total at each location. Or at least an option to
> show the bundles.
> 
> Since the stockmoves are created for every serialised item then the stock
> movement inquiry will show the full detail for each bundle - we could even
> show just the movements relating to a specific bundle - coming in and going
> out. The invoices and credit notes would also display - the movements
> relating to the invoice - serial number by serial number.
> 
> So I am proposing ....
> 
> New fields in StockMaster:
> 
> QtyControlled  int
> DecimalPlaces tinyint
> 
> New fields in LocStock
> 
> Bundle varchar(50)
> 
> Modification to the following scripts:
> 
> GoodsReceived.php
> 
> If controlled we need an additional form to enter bundle refs and qties or
> just serial numbers if QtyControl==1 then insert the multiple stock moves
> per item being received with the bundle ref against StockMoves.Bundle. We
> need to be able to cache the data in an array of Bundles which is a child of
> the item information in a similar way to the Item->LineItems->Bundle
> 
> Inserts into LocStock for new bundles with quantities in each.
> 
>  ConfirmDispatch_Invoice.php
> 
> if controlled need form to enter bundle refs. - need some array off the
> Items->Bundles to hold quantities and bundle refs. Then insert stock
> movements with bundle reference. May need to delete LocStock records if the
> quantity of a controlled item gets to 0. Total quantity per line item needs
> to be maintained for most of the other updates as usual.
> 
> We get into difficulties if a component of an assembly item is controlled -
> we should dissallow this on the BOM.php script and check for it when an item
> is modified to become controlled ie any parent parts that are assemblies.
> Otherwise we would have to specify the bundle refs for assembly components
> being invoiced - this kind of defeats the value of assemblies.
> 
>  CreditInvoice.php
> 
> if controlled items coming back into stock the bundle refs could be read off
> the stock movements from the original invoice - need some array off the
> Items->LineItems->Bundles to hold quantities and bundle refs. Then insert
> stock movements for goods coming back in with bundle reference. Total
> quantity per line item needs to be maintained for most of the other updates
> as usual. LocStock inserts if the bundle doesn't exist.
> 
>  SelectCreditItems.php
> 
> if controlled need form to enter bundle refs. for goods coming back into
> stock these need to be entered in a similar way to
> ConfirmDispatch_invoice.php - the same array off the
> Items->LineItems->Bundles to hold quantities and bundle refs used again.
> Then insert stock movements with bundle reference. Total quantity per line
> item needs to be maintained for most of the other updates as usual.
> 
> StockTransfers.php
> 
> if controlled need a form to enter the bundle refs and quantites (if
> QtyControl>1) StockMoves created with bundle ref.
> 
> StockAdjustments.php
> 
> if controlled entry of bundle ref and checking QtyControl - defaulting qty
> to 1 where QtyControl ==1 and not displaying the quantity field or perhaps
> the adjustment quantity could be entered in total and a form for entry of
> the bundle refs (or serial numbers) of all items being adjusted and
> quantities being adjusted from each bundle (s/n)
> 
> All the Stock Check scripts need to allow entry/reporting of bundle refs
> too. QtyControl =1 will just need to list the s/n of each item no quantity
> required. QtyControl>1 will need the quantity shown next to each reference.
> 
> StockStatus.php
> Display option to show bundles/lots/serial numbers of amounts held. SQL will
> need to sum(Qty) WHERE LocCode='$Location' AND StockID='$StockID'  - 'cos
> LocStock now contains (potentially) multiple records for each
> item/location - this table now holds the total stock of the
> bundle/lot/serial number (1).
> 
> ReOrderLevel.php
> For controlled items i think we should still maintain a generic serial
> number 1 item with a quantity of 0 that has the re-order level in it. The
> re-order level can then be determined. Some of the SQL in other scripts
> depends on a LocStock record existing for an item in each location even
> though the quantity may be 0.
> 
> StockValuation reports - detail will need to have an option to show the
> detail of controlled items - listing the references (s/n) and quantities in
> each lot - this will be 1 for s/n items.
> 
> PlanningReport needs to sum the quantity at the location accross all
> bundles.
> 
> I am not sure about any issues in having many millions of stockmoves -  SCSI
> disk size/speed/RAID and plenty of additional RAM to deal with the data. The
> script limits the data returned by date for stockmoves inquiries by date -
> retrieving moves for invoices and credits might slow from less than a milli
> second to a tenth or something. It would need some trial - with hopefully
> minimal error. The performance of the system is really quite good. Having
> additional tables with links etc would probably be slower than one biggie.
> Indexing is also a critical one to watch.
> 
> Sorry this is a biggie - this is how I did serial number control in a
> previous client server SQL Server system - it worked well. Variable batch
> control is a substantial undertaking and will trash the system throughout
> until we get it stable. It does add a fair bit of complexity to scripts and
> is not a project for the feint of heart!
> 
> Are you going to take it on ?
> 
> Phil
> 
> ----- Original Message -----
> From: "jesse" <je...@st...>
> To: <web...@li...>
> Sent: Wednesday, March 24, 2004 5:49 PM
> Subject: RE: [Web-erp-developers] Serialised stock - how to code this up
> 
> 
> > I definitely see how just using StockMaster will work. I'm trying to make
> it
> > work better in environments with large stock needs. Really, I could load
> > over several million items into this... think about all those stock moves.
> > I think both our thoughts will work together together fine, though. What
> if
> > we made StockMaster types both serialised and controlled? - controlled
> would
> > indicate that a bundle reference (stockmoves.bundle) is to be associated
> > with it, serialised would indicate that both a bundle ref. and
> > individualised serial numbers existed. best of both worlds? Maybe you
> could
> > bounce a couple scenarios off me to make sure I'm not forgetting
> something?
> > also, what about the methods of importing all those items that I
> mentioned?
> > or printing an invoice w/ even a few thousand items?
> >
> >
> > jesse
> >
> 
> 
> 
> -------------------------------------------------------
> This SF.Net email is sponsored by: IBM Linux Tutorials
> Free Linux tutorial presented by Daniel Robbins, President and CEO of
> GenToo technologies. Learn everything from fundamentals to system
> administration.http://ads.osdn.com/?ad_id=1470&alloc_id=3638&op=click
> _______________________________________________
> Web-erp-developers mailing list
> Web...@li...
> https://lists.sourceforge.net/lists/listinfo/web-erp-developers
> 

Re: [Web-erp-developers] Re: Some small things

[Daintree <p.d...@pa...>]

> > The returned page txt format ... you mean formated html text? Perhaps
better
> > still, the output could be re-directed to an email to the purchasing
> > officer - a config.php parameter??

> For automation this is possible to do, however I think the best thing to
> do, is to link a responsible user to a location from the www_users table
> to the location table, and then on the location define whether the
> report should be printed emailed in auto-mode for confirmation of
> location, and also which orders was emailed to suppliers and which
> should be faxed or printed.

Better still !

>
> Could we add some fax functionality to this ??? (hylafax may be)
>

Sure we could - your more up on this I suspect than me (zero knowledge).

> This sounds like a lot of work, I think I will just do a notification
> report for now. And then implement the above this will allow us to poke
> at it a little more.

Don't think it'll be too bad.

Phil

Re: [Web-erp-developers] Re: Some small things

[Danie B. <br...@na...>]

On Wed, 2004-03-24 at 09:17, Daintree wrote:
> Like it.
> 

> Perhaps the form should also show the suggested order quantity for
> modification or acceptance of - showing order value too ??
I agree..
<--shifted-->
> The only snag is getting the order to the supplier. Would it email the order
> off to a contact - need to perhaps have a flag on the suppliers contacts to
> determine which contacts should receive the email order. Some suppliers may
> not want or have email  - there are still a few suppliers about who do not!
<--shifted-->
> The returned page txt format ... you mean formated html text? Perhaps better
> still, the output could be re-directed to an email to the purchasing
> officer - a config.php parameter??
For automation this is possible to do, however I think the best thing to
do, is to link a responsible user to a location from the www_users table
to the location table, and then on the location define whether the
report should be printed emailed in auto-mode for confirmation of
location, and also which orders was emailed to suppliers and which
should be faxed or printed.

Could we add some fax functionality to this ??? (hylafax may be)

This sounds like a lot of work, I think I will just do a notification
report for now. And then implement the above this will allow us to poke
at it a little more.
Kind Regards
Danie Brink br...@na...

> 
> Phil
> 
> ----- Original Message -----
> From: "Danie Brink" <br...@na...>
> To: "Phil Daintree" <Web...@li...>
> Sent: Wednesday, March 24, 2004 6:44 PM
> Subject: Re: [Web-erp-developers] Re: Some small things
> 
> 
> > On Tue, 2004-03-23 at 22:06, Daintree wrote:
> > > There is no automatic creation of purchase orders and currently the
> re-order
> > > level is disused.
> > > A pdf report to show the orders created - or that would be created if
> the
> > > script were run in create orders mode. Also, the PO pdf containing all
> the
> > > orders created might be good too.
> >
> > I prefer the human touch to this, i.e. I should probably rather create a
> > form showing which items should be re-ordered and allow the user with
> > check-boxes to select which items he would like to place orders for.
> >
> > Also we could add another data field next to the re-order level
> > indicating automatic reorder. Calling the reorder page with a parameter
> > say "AutoOrder=True" will place orders for items marked as automatic and
> > simply return a page in txt format stating order numbers and items with
> > quantities ordered. The page could then be called from a cron job with
> > wget and the output could be redirected to a log file.
> >
> > What do you think ?
> >
> > Kind Regards
> > Danie Brink
> >
> >
> > > Phil
> > >
> > > ----- Original Message -----
> > > From: "Danie Brink" <br...@na...>
> > > To: "Phil Daintree" <Web...@li...>
> > > Sent: Wednesday, March 24, 2004 1:22 AM
> > > Subject: [Web-erp-developers] Re: Some small things
> > >
> > >
> > > > Sorry, I meant to complete the message, and tried to save as draft,
> but
> > > > my mouse click missed.
> > > >
> > > > Firstly, the attachments to previous message is correct coorect and I
> > > > have included the content here.
> > > >
> > > > Bank Account Checking For South Africa does not work.
> > > >
> > > > I have discovered the account number verification in Supplier.php does
> > > > not work for South Africa, so I added a Variable the variable to
> > > > config.php that enable/disable bank account checking. And also added
> it
> > > > into the if statement that calls the validation function.
> > > >
> > > > No Report or automatic order creation for reordering based on
> reordering
> > > > levels and preferred supplier is available.
> > > >
> > > > I will try to add one right now, at least the report for now, I will
> try
> > > > to use a similar report as the base.
> > > >
> > > > Please let me know if some of the work has already been done. I am
> > > > slightly worried about duplicating work.
> > > >
> > > > Kind Regards
> > > > Danie Brink br...@na...
> > > >
> > > >
> > > >
> > > >
> > > > -------------------------------------------------------
> > > > This SF.Net email is sponsored by: IBM Linux Tutorials
> > > > Free Linux tutorial presented by Daniel Robbins, President and CEO of
> > > > GenToo technologies. Learn everything from fundamentals to system
> > > > administration.http://ads.osdn.com/?ad_id=1470&alloc_id=3638&op=click
> > > > _______________________________________________
> > > > Web-erp-developers mailing list
> > > > Web...@li...
> > > > https://lists.sourceforge.net/lists/listinfo/web-erp-developers
> > > >
> > >
> > >
> > >
> > > -------------------------------------------------------
> > > This SF.Net email is sponsored by: IBM Linux Tutorials
> > > Free Linux tutorial presented by Daniel Robbins, President and CEO of
> > > GenToo technologies. Learn everything from fundamentals to system
> > > administration.http://ads.osdn.com/?ad_id=1470&alloc_id=3638&op=click
> > > _______________________________________________
> > > Web-erp-developers mailing list
> > > Web...@li...
> > > https://lists.sourceforge.net/lists/listinfo/web-erp-developers
> > >
> >
> >
> >
> > -------------------------------------------------------
> > This SF.Net email is sponsored by: IBM Linux Tutorials
> > Free Linux tutorial presented by Daniel Robbins, President and CEO of
> > GenToo technologies. Learn everything from fundamentals to system
> > administration.http://ads.osdn.com/?ad_id=1470&alloc_id=3638&op=click
> > _______________________________________________
> > Web-erp-developers mailing list
> > Web...@li...
> > https://lists.sourceforge.net/lists/listinfo/web-erp-developers
> >
> 
> 
> 
> -------------------------------------------------------
> This SF.Net email is sponsored by: IBM Linux Tutorials
> Free Linux tutorial presented by Daniel Robbins, President and CEO of
> GenToo technologies. Learn everything from fundamentals to system
> administration.http://ads.osdn.com/?ad_id=1470&alloc_id=3638&op=click
> _______________________________________________
> Web-erp-developers mailing list
> Web...@li...
> https://lists.sourceforge.net/lists/listinfo/web-erp-developers
> 

Re: [Web-erp-developers] Serialised stock - how to code this up

[Daintree <p.d...@pa...>]

Hi Jessie,

We are thinking along the same lines.

Sit back grab a coffee ... this is a biggie!

The stock master contains info about the item ....

We already have a field to define if we want to reference items individually
(controlled) or not.

However we also need to define the level of control. If it is controlled -
then how many items per bundle, lot or serial number. Lets call this field
QtyControl

If it is only one item allowed per lot then this is serial number control -
one reference per item.
In some businesses it could also be that up to 1000 kgs are allowed in a
batch of cheese production then a food manufacturer would be able to trace
where the batch was received and sold to for quality control purposes.
If up to 150 metres of cloth could be on a roll - a clothing manufacturer
would want to trace where each roll of cloth was used.

So you see the two fields Controlled and QtyControl allow the item to be
serialised - ie a reference for every unit of the item, or a reference that
can cover up to 999999999 units of the item - or whatever the limit of an
int data type  is.

Also, while we are adding fields to the stock master - the number of decimal
places to display would be a cool addition to the feature set.

When items are received ..... the stock master is examined to see if the
item is controlled or not ... if its controlled then we need to use the new
script to put the stock in and enter the reference for the bundle lot or
serial number - with reference to the stock master QtyControl - if its 1
(serialised stock) then the quantity would not need to be displayed it would
be defaulted to 1 for each reference (serial number) entered. If its more
than 1 then input of the quantity in the lot would be required too. Perhaps
this screen should show a table of  reference text inputs and quantities.

Need to check for not pre-existing references and quantities less than or
equal to QtyControl.

When items are (purchase) ordered - then there is no need for lot control
(is there?) since you don't know the lot until the order turns up at the
warehouse gates.

When items are sold - at the Confirm_DispatchInvoice.php stage the
quantities and lot numbers would need to be noted by the storeman/picker and
would need to entered in this script for controlled items - with quantities
(if not serialised ie QtyControl ==1). When the invoice is created it
creates a stockmove for each lot with the quantities entered - we already
have the bundle field to record the lot (or bundle or serial number) of the
item sold.

The updates to the LocStock would need modification since LocStock table now
needs a Bundle field.
the LocStock table contains information about quantities at each stock
location - and it should also show the quantities in each bundle - for
serialised items each quantity would be 1 - and the LocStock record for the
bundle would be deleted when the item/serial number is invoiced or
transferred to another location.

If controlled==1 then the StockStatus inquiry needs to show the bundles of
stock held arriving at a total at each location. Or at least an option to
show the bundles.

Since the stockmoves are created for every serialised item then the stock
movement inquiry will show the full detail for each bundle - we could even
show just the movements relating to a specific bundle - coming in and going
out. The invoices and credit notes would also display - the movements
relating to the invoice - serial number by serial number.

So I am proposing ....

New fields in StockMaster:

QtyControlled  int
DecimalPlaces tinyint

New fields in LocStock

Bundle varchar(50)

Modification to the following scripts:

GoodsReceived.php

If controlled we need an additional form to enter bundle refs and qties or
just serial numbers if QtyControl==1 then insert the multiple stock moves
per item being received with the bundle ref against StockMoves.Bundle. We
need to be able to cache the data in an array of Bundles which is a child of
the item information in a similar way to the Item->LineItems->Bundle

Inserts into LocStock for new bundles with quantities in each.

 ConfirmDispatch_Invoice.php

if controlled need form to enter bundle refs. - need some array off the
Items->Bundles to hold quantities and bundle refs. Then insert stock
movements with bundle reference. May need to delete LocStock records if the
quantity of a controlled item gets to 0. Total quantity per line item needs
to be maintained for most of the other updates as usual.

We get into difficulties if a component of an assembly item is controlled -
we should dissallow this on the BOM.php script and check for it when an item
is modified to become controlled ie any parent parts that are assemblies.
Otherwise we would have to specify the bundle refs for assembly components
being invoiced - this kind of defeats the value of assemblies.

 CreditInvoice.php

if controlled items coming back into stock the bundle refs could be read off
the stock movements from the original invoice - need some array off the
Items->LineItems->Bundles to hold quantities and bundle refs. Then insert
stock movements for goods coming back in with bundle reference. Total
quantity per line item needs to be maintained for most of the other updates
as usual. LocStock inserts if the bundle doesn't exist.

 SelectCreditItems.php

if controlled need form to enter bundle refs. for goods coming back into
stock these need to be entered in a similar way to
ConfirmDispatch_invoice.php - the same array off the
Items->LineItems->Bundles to hold quantities and bundle refs used again.
Then insert stock movements with bundle reference. Total quantity per line
item needs to be maintained for most of the other updates as usual.

StockTransfers.php

if controlled need a form to enter the bundle refs and quantites (if
QtyControl>1) StockMoves created with bundle ref.

StockAdjustments.php

if controlled entry of bundle ref and checking QtyControl - defaulting qty
to 1 where QtyControl ==1 and not displaying the quantity field or perhaps
the adjustment quantity could be entered in total and a form for entry of
the bundle refs (or serial numbers) of all items being adjusted and
quantities being adjusted from each bundle (s/n)

All the Stock Check scripts need to allow entry/reporting of bundle refs
too. QtyControl =1 will just need to list the s/n of each item no quantity
required. QtyControl>1 will need the quantity shown next to each reference.

StockStatus.php
Display option to show bundles/lots/serial numbers of amounts held. SQL will
need to sum(Qty) WHERE LocCode='$Location' AND StockID='$StockID'  - 'cos
LocStock now contains (potentially) multiple records for each
item/location - this table now holds the total stock of the
bundle/lot/serial number (1).

ReOrderLevel.php
For controlled items i think we should still maintain a generic serial
number 1 item with a quantity of 0 that has the re-order level in it. The
re-order level can then be determined. Some of the SQL in other scripts
depends on a LocStock record existing for an item in each location even
though the quantity may be 0.

StockValuation reports - detail will need to have an option to show the
detail of controlled items - listing the references (s/n) and quantities in
each lot - this will be 1 for s/n items.

PlanningReport needs to sum the quantity at the location accross all
bundles.

I am not sure about any issues in having many millions of stockmoves -  SCSI
disk size/speed/RAID and plenty of additional RAM to deal with the data. The
script limits the data returned by date for stockmoves inquiries by date -
retrieving moves for invoices and credits might slow from less than a milli
second to a tenth or something. It would need some trial - with hopefully
minimal error. The performance of the system is really quite good. Having
additional tables with links etc would probably be slower than one biggie.
Indexing is also a critical one to watch.

Sorry this is a biggie - this is how I did serial number control in a
previous client server SQL Server system - it worked well. Variable batch
control is a substantial undertaking and will trash the system throughout
until we get it stable. It does add a fair bit of complexity to scripts and
is not a project for the feint of heart!

Are you going to take it on ?

Phil

----- Original Message -----
From: "jesse" <je...@st...>
To: <web...@li...>
Sent: Wednesday, March 24, 2004 5:49 PM
Subject: RE: [Web-erp-developers] Serialised stock - how to code this up


> I definitely see how just using StockMaster will work. I'm trying to make
it
> work better in environments with large stock needs. Really, I could load
> over several million items into this... think about all those stock moves.
> I think both our thoughts will work together together fine, though. What
if
> we made StockMaster types both serialised and controlled? - controlled
would
> indicate that a bundle reference (stockmoves.bundle) is to be associated
> with it, serialised would indicate that both a bundle ref. and
> individualised serial numbers existed. best of both worlds? Maybe you
could
> bounce a couple scenarios off me to make sure I'm not forgetting
something?
> also, what about the methods of importing all those items that I
mentioned?
> or printing an invoice w/ even a few thousand items?
>
>
> jesse
>

Re: [Web-erp-developers] Re: Some small things

[Daintree <p.d...@pa...>]

Like it.

The only snag is getting the order to the supplier. Would it email the order
off to a contact - need to perhaps have a flag on the suppliers contacts to
determine which contacts should receive the email order. Some suppliers may
not want or have email  - there are still a few suppliers about who do not!

Perhaps the form should also show the suggested order quantity for
modification or acceptance of - showing order value too ??

The returned page txt format ... you mean formated html text? Perhaps better
still, the output could be re-directed to an email to the purchasing
officer - a config.php parameter??

Phil

----- Original Message -----
From: "Danie Brink" <br...@na...>
To: "Phil Daintree" <Web...@li...>
Sent: Wednesday, March 24, 2004 6:44 PM
Subject: Re: [Web-erp-developers] Re: Some small things


> On Tue, 2004-03-23 at 22:06, Daintree wrote:
> > There is no automatic creation of purchase orders and currently the
re-order
> > level is disused.
> > A pdf report to show the orders created - or that would be created if
the
> > script were run in create orders mode. Also, the PO pdf containing all
the
> > orders created might be good too.
>
> I prefer the human touch to this, i.e. I should probably rather create a
> form showing which items should be re-ordered and allow the user with
> check-boxes to select which items he would like to place orders for.
>
> Also we could add another data field next to the re-order level
> indicating automatic reorder. Calling the reorder page with a parameter
> say "AutoOrder=True" will place orders for items marked as automatic and
> simply return a page in txt format stating order numbers and items with
> quantities ordered. The page could then be called from a cron job with
> wget and the output could be redirected to a log file.
>
> What do you think ?
>
> Kind Regards
> Danie Brink
>
>
> > Phil
> >
> > ----- Original Message -----
> > From: "Danie Brink" <br...@na...>
> > To: "Phil Daintree" <Web...@li...>
> > Sent: Wednesday, March 24, 2004 1:22 AM
> > Subject: [Web-erp-developers] Re: Some small things
> >
> >
> > > Sorry, I meant to complete the message, and tried to save as draft,
but
> > > my mouse click missed.
> > >
> > > Firstly, the attachments to previous message is correct coorect and I
> > > have included the content here.
> > >
> > > Bank Account Checking For South Africa does not work.
> > >
> > > I have discovered the account number verification in Supplier.php does
> > > not work for South Africa, so I added a Variable the variable to
> > > config.php that enable/disable bank account checking. And also added
it
> > > into the if statement that calls the validation function.
> > >
> > > No Report or automatic order creation for reordering based on
reordering
> > > levels and preferred supplier is available.
> > >
> > > I will try to add one right now, at least the report for now, I will
try
> > > to use a similar report as the base.
> > >
> > > Please let me know if some of the work has already been done. I am
> > > slightly worried about duplicating work.
> > >
> > > Kind Regards
> > > Danie Brink br...@na...
> > >
> > >
> > >
> > >
> > > -------------------------------------------------------
> > > This SF.Net email is sponsored by: IBM Linux Tutorials
> > > Free Linux tutorial presented by Daniel Robbins, President and CEO of
> > > GenToo technologies. Learn everything from fundamentals to system
> > > administration.http://ads.osdn.com/?ad_id=1470&alloc_id=3638&op=click
> > > _______________________________________________
> > > Web-erp-developers mailing list
> > > Web...@li...
> > > https://lists.sourceforge.net/lists/listinfo/web-erp-developers
> > >
> >
> >
> >
> > -------------------------------------------------------
> > This SF.Net email is sponsored by: IBM Linux Tutorials
> > Free Linux tutorial presented by Daniel Robbins, President and CEO of
> > GenToo technologies. Learn everything from fundamentals to system
> > administration.http://ads.osdn.com/?ad_id=1470&alloc_id=3638&op=click
> > _______________________________________________
> > Web-erp-developers mailing list
> > Web...@li...
> > https://lists.sourceforge.net/lists/listinfo/web-erp-developers
> >
>
>
>
> -------------------------------------------------------
> This SF.Net email is sponsored by: IBM Linux Tutorials
> Free Linux tutorial presented by Daniel Robbins, President and CEO of
> GenToo technologies. Learn everything from fundamentals to system
> administration.http://ads.osdn.com/?ad_id=1470&alloc_id=3638&op=click
> _______________________________________________
> Web-erp-developers mailing list
> Web...@li...
> https://lists.sourceforge.net/lists/listinfo/web-erp-developers
>

Re: [Web-erp-developers] Re: Some small things

[Danie B. <br...@na...>]

On Tue, 2004-03-23 at 22:06, Daintree wrote:
> There is no automatic creation of purchase orders and currently the re-order
> level is disused.
> A pdf report to show the orders created - or that would be created if the
> script were run in create orders mode. Also, the PO pdf containing all the
> orders created might be good too.

I prefer the human touch to this, i.e. I should probably rather create a
form showing which items should be re-ordered and allow the user with
check-boxes to select which items he would like to place orders for.

Also we could add another data field next to the re-order level
indicating automatic reorder. Calling the reorder page with a parameter
say "AutoOrder=True" will place orders for items marked as automatic and
simply return a page in txt format stating order numbers and items with
quantities ordered. The page could then be called from a cron job with
wget and the output could be redirected to a log file.

What do you think ?

Kind Regards
Danie Brink


> Phil
> 
> ----- Original Message -----
> From: "Danie Brink" <br...@na...>
> To: "Phil Daintree" <Web...@li...>
> Sent: Wednesday, March 24, 2004 1:22 AM
> Subject: [Web-erp-developers] Re: Some small things
> 
> 
> > Sorry, I meant to complete the message, and tried to save as draft, but
> > my mouse click missed.
> >
> > Firstly, the attachments to previous message is correct coorect and I
> > have included the content here.
> >
> > Bank Account Checking For South Africa does not work.
> >
> > I have discovered the account number verification in Supplier.php does
> > not work for South Africa, so I added a Variable the variable to
> > config.php that enable/disable bank account checking. And also added it
> > into the if statement that calls the validation function.
> >
> > No Report or automatic order creation for reordering based on reordering
> > levels and preferred supplier is available.
> >
> > I will try to add one right now, at least the report for now, I will try
> > to use a similar report as the base.
> >
> > Please let me know if some of the work has already been done. I am
> > slightly worried about duplicating work.
> >
> > Kind Regards
> > Danie Brink br...@na...
> >
> >
> >
> >
> > -------------------------------------------------------
> > This SF.Net email is sponsored by: IBM Linux Tutorials
> > Free Linux tutorial presented by Daniel Robbins, President and CEO of
> > GenToo technologies. Learn everything from fundamentals to system
> > administration.http://ads.osdn.com/?ad_id=1470&alloc_id=3638&op=click
> > _______________________________________________
> > Web-erp-developers mailing list
> > Web...@li...
> > https://lists.sourceforge.net/lists/listinfo/web-erp-developers
> >
> 
> 
> 
> -------------------------------------------------------
> This SF.Net email is sponsored by: IBM Linux Tutorials
> Free Linux tutorial presented by Daniel Robbins, President and CEO of
> GenToo technologies. Learn everything from fundamentals to system
> administration.http://ads.osdn.com/?ad_id=1470&alloc_id=3638&op=click
> _______________________________________________
> Web-erp-developers mailing list
> Web...@li...
> https://lists.sourceforge.net/lists/listinfo/web-erp-developers
> 

RE: [Web-erp-developers] Serialised stock - how to code this up

[jesse <je...@st...>]

I definitely see how just using StockMaster will work. I'm trying to make it
work better in environments with large stock needs. Really, I could load
over several million items into this... think about all those stock moves.
I think both our thoughts will work together together fine, though. What if
we made StockMaster types both serialised and controlled? - controlled would
indicate that a bundle reference (stockmoves.bundle) is to be associated
with it, serialised would indicate that both a bundle ref. and
individualised serial numbers existed. best of both worlds? Maybe you could
bounce a couple scenarios off me to make sure I'm not forgetting something?
also, what about the methods of importing all those items that I mentioned?
or printing an invoice w/ even a few thousand items?


jesse

> > > Dont have head completely around the stock movement issues. In a
> previous
> > > system I wrote the stockmoves table held the information about the
> item's
> > > serial number a stock move being created for all serial
> number items eg
> a
> > > stock movement of 3 invoiced out for a controlled item would require
> > > specification of each individual item so this would not be possible -
> this
> > > would need to be a rule enforced by reference to the stock
> master. Stock
> > > Master also needs additional fields for the number of units of measure
> per
> > > bundle - this would determine whether there is only allowed one
> > > item (in the
> > > units of measure) per serial number or potentially 1000 of the unit of
> > > measure in a lot or 99999 metres per bundle of cloth. Also, the number
> of
> > > decimal places to display for the stock item in inquiries. eg
> > > stock held per
> > > tonne but .01 of a tonne is of interest at stock checks etc.
> > Ok. 1st, if I hadn't mentioned it, I don't come from a 'physical
> inventory'
> > world, I come from a 'service/usage world' (which, if you don't know,
> you're
> > not going to like :) ), so I'm probably not as familiar with that as you
> are
> > and you may get dumb questions on things...that said...
> > I don't really see a need for that. I would consider all of those
> > situtations just quantities ordered - the stockmaster item has units,
> which
> > I had always interpreted as usually being 'each' (as defaulted) or a
> > standard unit of weight or measurement, both of which can be easily
> > accounted for as a Quantity. Some user friendliness may be in order for
> user
> > interfaces (order entry, reports...), though,  as most people would not
> want
> > to have to figure out that they wanted to order 0.001 tonne of something
> > when they want a kilogram, though...maybe that leads to a sort
> of product
> > catalog, kinda like the BOMs, with 'preconfigured' StockMoves
> of what you
> > actually sell (as opposed to what you order & stock).
> >
>
> Of course the most appropriate unit of measure is used - the point is some
> things can and should be allowed to be issued and sold in fractions of a
> unit and if so the system should allow the user to determine how many
> decimal places to display. Currently we have no way to specify this.
> > > In the eg above the stockmoves table would actually record 3 seperate
> > > records each with the Bundle field set to the serial number.
> The invoice
> > > would show the serial number of each item sold (or the bundle/lot etc)
> the
> > > stock quantity by location would reflect the changes effected by the 3
> > > separate stockmoves. However we would need to expand the
> LocStock table
> to
> > > have bundle (or serialNo or such) in it too, the stock status
> > > inquiry would
> > > then sum the stock of all serial number items by location.
> > Going on the above, and assuming that we can keep up accounting
> (tedious,
> > but doable) such that the Qty in the StockMove is correctly reflected in
> the
> > StockItems (ie, the reconciliation program would never complain), that
> extra
> > work shouldn't be necessary.
>
> I guess I am saying I don't see the necessity for a StockItems table as
> distinct from the StockMaster and the StockMoves - all information about
> each movement is recorded against the StockMoves and all info
> about the item
> itself is in the StockMaster, so what is the purpose of the extra table ??
>
> >In my thoughts, a 'StockMove' becomes an
> > extension of the Invoice item - there are options in invoicing (possibly
> > attached to StockMaster instead, as a 'Controlled'-only variable) that
> allow
> > the detail for each item/serialnum sold to be shown, or not, on an
> invoice.
>
> Yes that's it in fact on the printing of invoices and credit notes the
> StockMoves are what makes up the detail of the invoice.
>
> > The Bundle field, which could be added to the invoice, could
> make a great
> > tracking or lot number to represent Qty sold instead of printing a
> StockMove
> > num, but may need to be lengthened (though we may also need further ids
> for
> > shipping purposes (5 of the 25 '30 yard rolls of cloth' per box??).
>
> We already have a quantity field in the StockMoves table - the
> bundle field
> is meant to refer to the actual serial number of the item moving. This is
> defaulted to 1 where there is no bundle/bath/lot/serial number control
> required.
>
> >I can
> > also see a need in various stockmodules to produce a manifest
> of the items
> > the actually went with an invoice.
>
> These should be spelled out on the invoice - this would simply be
> a listing
> of the stock movements displaying the 'bundle' as well on the invoice.
>
> > Keep in mind that I am planning for quite large tables of items - like
> > creating 100,000 items and selling them 50,000 at a time, but still
> wanting
> > to know exactly where every single one of them is.
>
>
> A fair bit of input - bar coding may well be appropriate too.
>
> Phil
>
>
>
> -------------------------------------------------------
> This SF.Net email is sponsored by: IBM Linux Tutorials
> Free Linux tutorial presented by Daniel Robbins, President and CEO of
> GenToo technologies. Learn everything from fundamentals to system
> administration.http://ads.osdn.com/?ad_id=1470&alloc_id=3638&op=click
> _______________________________________________
> Web-erp-developers mailing list
> Web...@li...
> https://lists.sourceforge.net/lists/listinfo/web-erp-developers
>

Re: [Web-erp-developers] Re: Some small things

[Daintree <p.d...@pa...>]

There is no automatic creation of purchase orders and currently the re-order
level is disused.
A pdf report to show the orders created - or that would be created if the
script were run in create orders mode. Also, the PO pdf containing all the
orders created might be good too.

Phil

----- Original Message -----
From: "Danie Brink" <br...@na...>
To: "Phil Daintree" <Web...@li...>
Sent: Wednesday, March 24, 2004 1:22 AM
Subject: [Web-erp-developers] Re: Some small things


> Sorry, I meant to complete the message, and tried to save as draft, but
> my mouse click missed.
>
> Firstly, the attachments to previous message is correct coorect and I
> have included the content here.
>
> Bank Account Checking For South Africa does not work.
>
> I have discovered the account number verification in Supplier.php does
> not work for South Africa, so I added a Variable the variable to
> config.php that enable/disable bank account checking. And also added it
> into the if statement that calls the validation function.
>
> No Report or automatic order creation for reordering based on reordering
> levels and preferred supplier is available.
>
> I will try to add one right now, at least the report for now, I will try
> to use a similar report as the base.
>
> Please let me know if some of the work has already been done. I am
> slightly worried about duplicating work.
>
> Kind Regards
> Danie Brink br...@na...
>
>
>
>
> -------------------------------------------------------
> This SF.Net email is sponsored by: IBM Linux Tutorials
> Free Linux tutorial presented by Daniel Robbins, President and CEO of
> GenToo technologies. Learn everything from fundamentals to system
> administration.http://ads.osdn.com/?ad_id=1470&alloc_id=3638&op=click
> _______________________________________________
> Web-erp-developers mailing list
> Web...@li...
> https://lists.sourceforge.net/lists/listinfo/web-erp-developers
>

Re: [Web-erp-developers] Re: Help Required with ReOrder SQL

[Daintree <p.d...@pa...>]

All looks reasonable for Location 'HQ'.

> SUM(`PurchOrderDetails`.`QuantityOrd`) -
> SUM(`PurchOrderDetails`.`QuantityRecd`) as OnOrder

could also be
SUM(`PurchOrderDetails`.`QuantityOrd` - `PurchOrderDetails`.`QuantityRecd`)
as OnOrder

I think.

You could then insert new PO records based on the preferred supplier for
each and create a pdf for all the orders created.

However, in planning what to buy where the volumes of items is managable I
prefer a human reality check about expected future requirements using the
information provided on the planning report which also suggests quantities
to buy based on the historical usage of each item over the last 3 months -
taking the maximum usage and multiplying by 3 or 4 and taking off what is in
stock and on purchase orders but adding back what is on demand - sales
orders not yet invoiced.

Phil

----- Original Message -----
From: "Danie Brink" <br...@na...>
To: "Phil Daintree" <Web...@li...>
Sent: Wednesday, March 24, 2004 3:17 AM
Subject: [Web-erp-developers] Re: Help Required with ReOrder SQL


> Hi Phil
>
> I am trying to solve the reorder question as follows, is this acceptable
> ??
>
> SQL Query 1 (SQL_Q1): Provides Stock Items for which Reorder levels have
> been activated.
>
> SELECT `StockMaster`.`StockID`,
> `StockMaster`.`Description`,
> `StockMaster`.`EOQ` as ReOrderQty,
> `LocStock`.`Quantity` As CurQty,
> `LocStock`.`ReorderLevel` As ROL
> FROM StockMaster, LocStock
> WHERE (`StockMaster`.`StockID` = `LocStock`.`StockID` AND
> `LocStock`.`Quantity` <= `LocStock`.`ReorderLevel` AND
> `LocStock`.`LocCode`="HQ")
>
> SQL Query 2 (SQL_Q2) : Provides A list of Stock Quantities already on
> order.
>
> SELECT `PurchOrders`.`IntoStockLocation`,
>  `PurchOrderDetails`.`ItemCode`,
> SUM(`PurchOrderDetails`.`QuantityOrd`) -
> SUM(`PurchOrderDetails`.`QuantityRecd`) as OnOrder
> FROM `PurchOrders`,`PurchOrderDetails`
> WHERE ( `PurchOrderDetails`.`OrderNo` = `PurchOrders`.`OrderNo` AND
> `PurchOrderDetails`.`Completed` = 0 AND
> `PurchOrders`.`IntoStockLocation` = "HQ" )
> GROUP BY `PurchOrderDetails`.`ItemCode`
> HAVING OnOrder > 0
>
> Solutions :
> the equation for stock that should be re-ordered is as folows for each
> unique item per location.
>
> Actual-Qty-Already-Actioned = SQL_Q1.CurQty + SQL_Q2.OnOrder
> if Actual-Qty-Already-Actioned < SQL_Q1.ROL then
> Do-Re-Order-Qty = SQL_Q1.ReOrderQty
> else
> Do-Re-Order-Qty = 0
>
> Could you please tell me if my assumptions are correct.
>
> Kind Regards
> Danie Brink br...@na...
>
>
>
>
> -------------------------------------------------------
> This SF.Net email is sponsored by: IBM Linux Tutorials
> Free Linux tutorial presented by Daniel Robbins, President and CEO of
> GenToo technologies. Learn everything from fundamentals to system
> administration.http://ads.osdn.com/?ad_id=1470&alloc_id=3638&op=click
> _______________________________________________
> Web-erp-developers mailing list
> Web...@li...
> https://lists.sourceforge.net/lists/listinfo/web-erp-developers
>

[Web-erp-developers] Re: Help Required with ReOrder SQL

[Danie B. <br...@na...>]

Hi Phil

I am trying to solve the reorder question as follows, is this acceptable
??

SQL Query 1 (SQL_Q1): Provides Stock Items for which Reorder levels have
been activated.

SELECT `StockMaster`.`StockID`, 
`StockMaster`.`Description`,
`StockMaster`.`EOQ` as ReOrderQty, 
`LocStock`.`Quantity` As CurQty, 
`LocStock`.`ReorderLevel` As ROL
FROM StockMaster, LocStock
WHERE (`StockMaster`.`StockID` = `LocStock`.`StockID` AND 
`LocStock`.`Quantity` <= `LocStock`.`ReorderLevel` AND 
`LocStock`.`LocCode`="HQ")

SQL Query 2 (SQL_Q2) : Provides A list of Stock Quantities already on
order.

SELECT `PurchOrders`.`IntoStockLocation`,
 `PurchOrderDetails`.`ItemCode`,
SUM(`PurchOrderDetails`.`QuantityOrd`) -
SUM(`PurchOrderDetails`.`QuantityRecd`) as OnOrder
FROM `PurchOrders`,`PurchOrderDetails` 
WHERE ( `PurchOrderDetails`.`OrderNo` = `PurchOrders`.`OrderNo` AND
`PurchOrderDetails`.`Completed` = 0 AND 
`PurchOrders`.`IntoStockLocation` = "HQ" )
GROUP BY `PurchOrderDetails`.`ItemCode`
HAVING OnOrder > 0

Solutions :
the equation for stock that should be re-ordered is as folows for each
unique item per location.

Actual-Qty-Already-Actioned = SQL_Q1.CurQty + SQL_Q2.OnOrder
if Actual-Qty-Already-Actioned < SQL_Q1.ROL then 
	Do-Re-Order-Qty = SQL_Q1.ReOrderQty 
else
	Do-Re-Order-Qty = 0

Could you please tell me if my assumptions are correct.

Kind Regards
Danie Brink br...@na...

[Web-erp-developers] Re: Some small things

[Danie B. <br...@na...>]

Sorry, I meant to complete the message, and tried to save as draft, but
my mouse click missed.

Firstly, the attachments to previous message is correct coorect and I
have included the content here.

Bank Account Checking For South Africa does not work.

I have discovered the account number verification in Supplier.php does
not work for South Africa, so I added a Variable the variable to
config.php that enable/disable bank account checking. And also added it
into the if statement that calls the validation function.

No Report or automatic order creation for reordering based on reordering
levels and preferred supplier is available.

I will try to add one right now, at least the report for now, I will try
to use a similar report as the base. 

Please let me know if some of the work has already been done. I am
slightly worried about duplicating work.

Kind Regards
Danie Brink br...@na...

[Web-erp-developers] Some Small Things

[Danie B. <br...@na...>]

Bank Account Checking For South Africa does not work.

I have discovered the account number verification in Supplier.php does
not work for South Africa, so I added a Variable the variable to
config.php that enable/disable bank account checking. And also added it
into the if statement that calls the validation function.

No Report or automatic order creation for reordering based on reordering
levels and preferred supplier.

[Web-erp-developers] Serialised stock - how to code this up

[Daintree <p.d...@pa...>]

> > Dont have head completely around the stock movement issues. In a
previous
> > system I wrote the stockmoves table held the information about the
item's
> > serial number a stock move being created for all serial number items eg
a
> > stock movement of 3 invoiced out for a controlled item would require
> > specification of each individual item so this would not be possible -
this
> > would need to be a rule enforced by reference to the stock master. Stock
> > Master also needs additional fields for the number of units of measure
per
> > bundle - this would determine whether there is only allowed one
> > item (in the
> > units of measure) per serial number or potentially 1000 of the unit of
> > measure in a lot or 99999 metres per bundle of cloth. Also, the number
of
> > decimal places to display for the stock item in inquiries. eg
> > stock held per
> > tonne but .01 of a tonne is of interest at stock checks etc.
> Ok. 1st, if I hadn't mentioned it, I don't come from a 'physical
inventory'
> world, I come from a 'service/usage world' (which, if you don't know,
you're
> not going to like :) ), so I'm probably not as familiar with that as you
are
> and you may get dumb questions on things...that said...
> I don't really see a need for that. I would consider all of those
> situtations just quantities ordered - the stockmaster item has units,
which
> I had always interpreted as usually being 'each' (as defaulted) or a
> standard unit of weight or measurement, both of which can be easily
> accounted for as a Quantity. Some user friendliness may be in order for
user
> interfaces (order entry, reports...), though,  as most people would not
want
> to have to figure out that they wanted to order 0.001 tonne of something
> when they want a kilogram, though...maybe that leads to a sort of product
> catalog, kinda like the BOMs, with 'preconfigured' StockMoves of what you
> actually sell (as opposed to what you order & stock).
>

Of course the most appropriate unit of measure is used - the point is some
things can and should be allowed to be issued and sold in fractions of a
unit and if so the system should allow the user to determine how many
decimal places to display. Currently we have no way to specify this.


> > In the eg above the stockmoves table would actually record 3 seperate
> > records each with the Bundle field set to the serial number. The invoice
> > would show the serial number of each item sold (or the bundle/lot etc)
the
> > stock quantity by location would reflect the changes effected by the 3
> > separate stockmoves. However we would need to expand the LocStock table
to
> > have bundle (or serialNo or such) in it too, the stock status
> > inquiry would
> > then sum the stock of all serial number items by location.
> Going on the above, and assuming that we can keep up accounting (tedious,
> but doable) such that the Qty in the StockMove is correctly reflected in
the
> StockItems (ie, the reconciliation program would never complain), that
extra
> work shouldn't be necessary.

I guess I am saying I don't see the necessity for a StockItems table as
distinct from the StockMaster and the StockMoves - all information about
each movement is recorded against the StockMoves and all info about the item
itself is in the StockMaster, so what is the purpose of the extra table ??

>In my thoughts, a 'StockMove' becomes an
> extension of the Invoice item - there are options in invoicing (possibly
> attached to StockMaster instead, as a 'Controlled'-only variable) that
allow
> the detail for each item/serialnum sold to be shown, or not, on an
invoice.

Yes that's it in fact on the printing of invoices and credit notes the
StockMoves are what makes up the detail of the invoice.

> The Bundle field, which could be added to the invoice, could make a great
> tracking or lot number to represent Qty sold instead of printing a
StockMove
> num, but may need to be lengthened (though we may also need further ids
for
> shipping purposes (5 of the 25 '30 yard rolls of cloth' per box??).

We already have a quantity field in the StockMoves table - the bundle field
is meant to refer to the actual serial number of the item moving. This is
defaulted to 1 where there is no bundle/bath/lot/serial number control
required.

>I can
> also see a need in various stockmodules to produce a manifest of the items
> the actually went with an invoice.

These should be spelled out on the invoice - this would simply be a listing
of the stock movements displaying the 'bundle' as well on the invoice.

> Keep in mind that I am planning for quite large tables of items - like
> creating 100,000 items and selling them 50,000 at a time, but still
wanting
> to know exactly where every single one of them is.


A fair bit of input - bar coding may well be appropriate too.

Phil

Re: [Web-erp-developers] Re: Marketing Inquiries

[Danie B. <br...@na...>]

Thanks Phil

I think only the Interest Calculation is requested often, the other was
as I see it an interesting scenario and a different way of doing it. If
I get more requirements I will let you know. My prospect said she want
the product next month, also she would like to run it past their
accountant. I am not to worried, however there might be some requests.

P.S. the user manual we are re-doing is looking good from what I've
seen, at last view 48 pages, and we still need to include images, revise
for version 2.8 and 2.9. Just takes time sorry for the wait. I have
requested that all manuals be finished by the end of the month.

Kind Regards
Danie Brink br...@na...


On Wed, 2004-03-17 at 10:15, Daintree wrote:
> Danie,
> 
> > During the last two days, clients have asked me many times about
> > discounts, discount matrixes, discount on early payment etc.
> 
> We are pretty good here. We have a full discount matrix as well as
> individual pricing. I think I wrote this up in the manual.
> 
> >A question
> > some of them asked was about interest calculation on late payment. I
> > have not seen anything like this in the manuals or in the software. How
> > difficult would it be to add something like this, calculating interest
> > on late payment and issuing updated statements. I seems many of the
> > businesses are struggling to keep track of this type of thing.
> 
> Peice of cake - all we need to do is run a process before statements are
> issued to create invoices for a dummy item - interest on late payment. Will
> need another field on the DebtorsMaster for interest rate and whether or not
> to charge. Invoices and statements should perhaps have appropriate messages
> warning about this as part of the terms. The process would run through the
> DebtorsMaster and pull out all the customers flagged as applicable.
> Calculate overdue amount and insert invoices for interest.
> 
> 
> > Yet another company asked me if the early payment discount could be
> > deferred, in other words, the client does not receive the discount
> > immediately, instead it is available on future purchases as a discount.
> 
> All things are possible. Would need some thought - if it would swing a sale
> ... no problem!
> 
> > Naturally we do not have this type of functionality. The problem I see
> > with this is that the discount is simply a promise and is not part of
> > the actual GL accounting. It might however be usefully, as it encourages
> > future sales more effectively (according to the prospect/business) They
> > also have a rule set whereby the total discount value allowed on a per
> > invoice basis may be no more than a certain percentage of the total
> > invoice value, and also the discount is only receivable on prompt
> > payment, before payment period 1, thereafter up to payment period 2 it
> > is still considered on time payment with no discount, after period 2
> > interest starts to accrue on the invoice.
> >
> Interesting policy. We would need to have some additional fields against the
> debtors master that are re-calculated monthly.
> 
> > What would the impact of this type of functionality requirement be.
> 
> Since no GL impact - really just memo fields but picked up on
> ConfirmDispatch_Invoice.php
> 
> Phil
> 
> 
> 
> 
> -------------------------------------------------------
> This SF.Net email is sponsored by: IBM Linux Tutorials
> Free Linux tutorial presented by Daniel Robbins, President and CEO of
> GenToo technologies. Learn everything from fundamentals to system
> administration.http://ads.osdn.com/?ad_id=1470&alloc_id=3638&op=click
> _______________________________________________
> Web-erp-developers mailing list
> Web...@li...
> https://lists.sourceforge.net/lists/listinfo/web-erp-developers
> 

[Web-erp-developers] Re: Marketing Inquiries

[Daintree <p.d...@pa...>]

Danie,

> During the last two days, clients have asked me many times about
> discounts, discount matrixes, discount on early payment etc.

We are pretty good here. We have a full discount matrix as well as
individual pricing. I think I wrote this up in the manual.

>A question
> some of them asked was about interest calculation on late payment. I
> have not seen anything like this in the manuals or in the software. How
> difficult would it be to add something like this, calculating interest
> on late payment and issuing updated statements. I seems many of the
> businesses are struggling to keep track of this type of thing.

Peice of cake - all we need to do is run a process before statements are
issued to create invoices for a dummy item - interest on late payment. Will
need another field on the DebtorsMaster for interest rate and whether or not
to charge. Invoices and statements should perhaps have appropriate messages
warning about this as part of the terms. The process would run through the
DebtorsMaster and pull out all the customers flagged as applicable.
Calculate overdue amount and insert invoices for interest.


> Yet another company asked me if the early payment discount could be
> deferred, in other words, the client does not receive the discount
> immediately, instead it is available on future purchases as a discount.

All things are possible. Would need some thought - if it would swing a sale
... no problem!

> Naturally we do not have this type of functionality. The problem I see
> with this is that the discount is simply a promise and is not part of
> the actual GL accounting. It might however be usefully, as it encourages
> future sales more effectively (according to the prospect/business) They
> also have a rule set whereby the total discount value allowed on a per
> invoice basis may be no more than a certain percentage of the total
> invoice value, and also the discount is only receivable on prompt
> payment, before payment period 1, thereafter up to payment period 2 it
> is still considered on time payment with no discount, after period 2
> interest starts to accrue on the invoice.
>
Interesting policy. We would need to have some additional fields against the
debtors master that are re-calculated monthly.

> What would the impact of this type of functionality requirement be.

Since no GL impact - really just memo fields but picked up on
ConfirmDispatch_Invoice.php

Phil

RE: [Web-erp-developers] Intro & Questions

[Jesse P. <jes...@st...>]

yep, looks like I was wrong. I'm still getting used to the system and it =
didn't work as I'd expected. Sorry for the false alarm.


jesse

-----Original Message-----
From: Phil Daintree [mailto:ph...@du...]
Sent: Tuesday, March 16, 2004 03:45
To: web...@li...
Subject: Re: [Web-erp-developers] Intro & Questions



>
> > A couple Quickies...
> > * SelectOrderItems.php =3D>  Lookup by Cust Name does not seem to =
work in
>
> 2.8 or 2.9
>

I can't repeat this Jessie entering a portion of the customer name =
appears to=20
return the customers concerned for me. Can you give me any more =
specifics on=20
the snag?

Phil

Re: [Web-erp-developers] Intro & Questions

[Daintree <p.d...@pa...>]

hello luv! r u having fun? just watching the tv for a bit, lazy eh? nite
nite H
----- Original Message -----
From: "Phil Daintree" <ph...@du...>
To: <web...@li...>
Sent: Tuesday, March 16, 2004 9:44 PM
Subject: Re: [Web-erp-developers] Intro & Questions


>
> >
> > > A couple Quickies...
> > > * SelectOrderItems.php =>  Lookup by Cust Name does not seem to work
in
> >
> > 2.8 or 2.9
> >
>
> I can't repeat this Jessie entering a portion of the customer name appears
to
> return the customers concerned for me. Can you give me any more specifics
on
> the snag?
>
> Phil
>
>
> -------------------------------------------------------
> This SF.Net email is sponsored by: IBM Linux Tutorials
> Free Linux tutorial presented by Daniel Robbins, President and CEO of
> GenToo technologies. Learn everything from fundamentals to system
> administration.http://ads.osdn.com/?ad_id=1470&alloc_id=3638&op=click
> _______________________________________________
> Web-erp-developers mailing list
> Web...@li...
> https://lists.sourceforge.net/lists/listinfo/web-erp-developers
>

Re: [Web-erp-developers] Intro & Questions

[Phil D. <ph...@du...>]

>
> > A couple Quickies...
> > * SelectOrderItems.php =>  Lookup by Cust Name does not seem to work in
>
> 2.8 or 2.9
>

I can't repeat this Jessie entering a portion of the customer name appears to 
return the customers concerned for me. Can you give me any more specifics on 
the snag?

Phil

Re: [Web-erp-developers] Intro & Questions

[Daintree <p.d...@pa...>]

Hi Jesse,

Thanks for chosing to help us!

diffs from the current cvs copy of the script would be excellent. However
I'm just happy to get contributions.

>
> A couple Quickies...
> * SelectOrderItems.php =>  Lookup by Cust Name does not seem to work in
2.8 or 2.9

OK I'll look into this.


> * I noticed discussions of PDF libs to use - my 2 cents is that R&OS is
the best opensource one for php that I've worked with.

This is what we are using ... phew!
>
> My Top 3....

> * Individual Stock Items w/ Barcode (= serial no/mac addr/etc)

This is actually quite tricky - I have a field in stock movements and
LocStock for Bundle - this could also be called serialno, lot or whatever.
There is also ability to choose whether the item is serial controlled or
not. However, the code around it for choosing items ie which items from the
stock at location x are to be sold and entering serial number (lot number or
bundle ref) of goods coming back in on a credit note are not coded. This
will involve rework of SelectOrderItems.php - or may elect only to specify
seria numbers on invoicing in this case ConfirmDispatch_Invoice.php,
SelectCreditItems.php,   CreditInvoice.php - also receiving controlled items
would require rework and possibly a new form for entering lot numbers/serial
numbers for goods coming in. StockTransfers.php naturally for specifying
serial numbers of stock transferred. Also, stock check functionality is also
a biggie.


> * More Sales Functions?
> - SalesGroups (that contain Persons/agents).
> - Let them do acct mgmt of their customers. ie,
> + Agent could view his customers accounts & submit orders, but not all
Group accounts.
> + Groups of course could view all of their Agents' accounts

That shouldn't be too bad .. just an extra field in the customer master and
some ifs regarding permissions.

>
> * Saw a mention of Utility Billing - anything to look at? That should be
what I know as 'Usage Based' billing. I'm quite interested in that. Further
thoughts - with Utility/Usage billing you generally need some very granular
access to each Item. For example, I live in the telecom world and would want
something like:
> - Travel Card: I consider this a Telephone Calling Card where someone is
Post billed, ie. they make calls for the month and are billed for their
usage.
> + If I talk to that customer, there is a good chance I will need to look
at the calls they've been making, so I would want to be able to write some
sort of module to 'Maintain' the Travel Card Item. There probably would need
to be a way to create Credit Notes for Usage as well, then, so I could
'credit calls'
> ... that's one of the most simple cases...


I have no knowledge in this area, but from what you say it is probably
different for each type of usage and therefore difficult to do anything
generically? I would go with whatever anyone wished to contribute.

> Other Ponderings....
> * DB Independence - I don't care, but why not ADODB to help that?

Performance snags - but could be an option - things are fantastically lean
as things stand and have a strange aversion to fat of any kind.

> * Template support, just to clean up code?

Probably should, but comments above apply here too - I don't know much about
it either.

Look forward to working with you Jessie - all comments/suggestions/direction
gratefully received.

Phil

[Web-erp-developers] Intro & Questions

[Jesse P. <jes...@st...>]

Hello all. I've been looking over web-erp and similar opensource apps =
for some days now and have decided this one is best suited for my =
current needs. Of course I have many, many more, so I will certainely be =
developing against it. If I have to do that, it would certainley seem =
best if it could help out the project too. The top 3 items I am =
concerned about in it's current state are Serialisable Items, Usage =
based billing, and extended Sales Functionality (with some notes on each =
below).
I don't want to duplicate efforts, so I'm wondering what is planned for =
those 3 areas, the top priorities the current developers have, and if =
there are any other plans already set out. I've seen the Phil Daintree =
has asked people before to send source straight to him - if I do, do you =
want the full PHP files, or diffs?

k, here goes...=20

A couple Quickies...
* SelectOrderItems.php =3D>  Lookup by Cust Name does not seem to work =
in 2.8 or 2.9
* I noticed discussions of PDF libs to use - my 2 cents is that R&OS is =
the best opensource one for php that I've worked with.

My Top 3....
* Individual Stock Items w/ Barcode (=3D serial no/mac addr/etc)
-------[I see it's been started]--------------
PO_Items.php:if ($hide_incomplete_features =3D=3D False)    {
PO_Items.php:if ($hide_incomplete_features=3D=3DFalse)      {
Stocks.php:                     if ($hide_incomplete_features=3D=3DTrue) =
{
Stocks.php:if ($hide_incomplete_features=3D=3DFalse) {
SupplierInvoice.php:if ($hide_incomplete_features=3D=3DFalse) {
----------------------------------------------
* More Sales Functions?=20
	- SalesGroups (that contain Persons/agents).
	- Let them do acct mgmt of their customers. ie,
		+ Agent could view his customers accounts & submit orders, but not all =
Group accounts.=20
		+ Groups of course could view all of their Agents' accounts

* Saw a mention of Utility Billing - anything to look at? That should be =
what I know as 'Usage Based' billing. I'm quite interested in that. =
Further thoughts - with Utility/Usage billing you generally need some =
very granular access to each Item. For example, I live in the telecom =
world and would want something like:
	- Travel Card: I consider this a Telephone Calling Card where someone =
is Post billed, ie. they make calls for the month and are billed for =
their usage.
		+ If I talk to that customer, there is a good chance I will need to =
look at the calls they've been making, so I would want to be able to =
write some sort of module to 'Maintain' the Travel Card Item. There =
probably would need to be a way to create Credit Notes for Usage as =
well, then, so I could 'credit calls'
... that's one of the most simple cases...

Other Ponderings....
* DB Independence - I don't care, but why not ADODB to help that?
* Template support, just to clean up code?



Thanks for you time.


jesse

Re: [Web-erp-developers] $Revision$ CVS +

[Stins, D. <DR...@Zi...>]

Phil,

Thanks. This will save us lot's of debugging the wrong revision when every
bug/issue/question/problem is reported inclusive revision number.

I agree that putting the revision in html comment sometimes can be tricky
(or impossible?).

With best regards,

Dick Stins
----- Original Message -----
From: "Phil Daintree" <ph...@du...>
To: <web...@li...>
Sent: Monday, March 15, 2004 10:21 AM
Subject: Re: [Web-erp-developers] $Revision$ CVS +


>
> Dick,
>
> I used your Revision thing in all scripts - but not as html comment as a
php
> comment - sometime output of an echo statemet can stuff up the headers
where
> we actually want pdf - I just put it in as a php comment. so its available
in
> the source only.
>
> Phil
>
>
> -------------------------------------------------------
> This SF.Net email is sponsored by: IBM Linux Tutorials
> Free Linux tutorial presented by Daniel Robbins, President and CEO of
> GenToo technologies. Learn everything from fundamentals to system
> administration.http://ads.osdn.com/?ad_id=1470&alloc_id=3638&op=click
> _______________________________________________
> Web-erp-developers mailing list
> Web...@li...
> https://lists.sourceforge.net/lists/listinfo/web-erp-developers

Re: [Web-erp-developers] $Revision$ CVS +

[Phil D. <ph...@du...>]

Dick,

I used your Revision thing in all scripts - but not as html comment as a php 
comment - sometime output of an echo statemet can stuff up the headers where 
we actually want pdf - I just put it in as a php comment. so its available in 
the source only.

Phil

Re: [Web-erp-developers] Re: First Mysql 2 Pgsql util andConnectDB for Postgres

[Phil D. <ph...@du...>]

Danie,

> I like the scenario you are painting, there are still some concerns from
> my side though. I am worried about some of the actual SQL used in some
> of the php files. For those functions that do fail, I would like to
> introduce separate functions in ConfigDB.inc and ConfigDB_pg.inc just
> keeping it separate for now while I am testing.
>

You mean ConnectDB.inc? Yes I agree we should perhaps hold off publishing
any files or claims of Postgres compatibility until you are convinced it is
stable on Postgres.


> This might cause some discomfort for you, so I would like to run this
> past you first. What would happen is that in the PHP code where the SQL
> use to be, there would then be a function call which would exec the
> required functionality and return data as expected.

Could we consider making the SQL more generic in those areas where there is
a problem? If there is a KIS - Keep It Simple - solution then this would be
my preference.

How widespread are the problems?

>
> Once again I think this would leave the way open for other developers
> that might have an interest in other Databases like ( FireBird or Oracle
> for instance ). Also in the case of RDBMs additional Stored Procedures,
> Triggers and Views might be implemented on the DB side to help speed
> things up and these would be an-add on DB script loaded after the
> standard MySQL converted script has been loaded. Therefore 2 phase
> approach.
>

This would create one almighty include file for parsing on every script
would it not? If  so it might actually slow things down!

I like the concept of keeping required triggers inside the application
wherever possible and being rigorous in maintaining db integrity with a few
extra sql calls. I am not convinced at the value of Stored procedures
either. In a client server application it is one thing where the network is
saturated, but this application is so lean it is hard to envisage this. I
would much rather modify every sql statement in the application to keep it
generic. Also, the network traffic for db calls is all between web-server
and sql-server in installations less than 100 seats this will most likely be
the same machine - the throughput of the network will not be a snag. In
terms of speed of processing of pre-compiled stored procedures there may be
some small advantage, but an increase in the amount of RAM for the server or
having a separate DB server from the web server will be a much better
investment in boosting performance. No, my feeling about stored procedures
is it is a way for DB vendors to lock in users - absolutely counter to
open-source philosophy. I am confident of Dick's input here and I will of
course listen to reason. I worked extensively with MS SQL Server
storedprocedures and DBCC update statistics before every run does tend to
slow performance - I guess things have come on a little since 6.5 but a sour
taste does tend to stick in the throat for some time!

Irrespective of that, to make the application most portable the SQL should
be as generic as possible. There may be scope for improving this - eg. I
tried to ensure all data and time work is done in php rather than SQL
functions to avoid proprietry functions.

 Phil

Re: [Web-erp-developers] .doc = ms word?

[Phil D. <ph...@du...>]

.pdf is good since a reader is required for web-erp anyway ... It does kind
of prohibit anyone from modifying it for their own needs though without
expensive software.

.doc format is portable enough with open office and word available for most
every platform I think.

I hope the manual is of some use - your questions have been good since the
manual has been lacking in the questions you posed and I have added bits to
it - extracts from our list postings - although this may cause a problem for
Danie - hope not.

Phil


----- Original Message -----
From: "Stins, Dick" <DR...@Zi...>
To: "Web-ERP Developers" <Web...@li...>
Sent: Sunday, March 14, 2004 11:21 PM
Subject: [Web-erp-developers] .doc = ms word?


> BlankDear All,
>
> I found the web-erp/doc/webERP_Manual.doc
>
> Please use not the .doc format but the more portable .rtf format (or .pdf
or
> .html or ....).
>
> With best regards,
>
> Dick Stins