web-erp-developers — Discussion on development of webERP

[Web-erp-developers] Download Managers

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

So could it be disabled for extensions of .pdf by some configuration of the
download manager ie download managers presumably don't work on standard web
pages only on files like .zip, .gz, .exe, .jpg, .gif etc .... or do they?

We seem to be blessed with good cheap bandwidth in NZ so I really need to
get up to speed with these things.

Phil

>Hi,

>I think that the download manager is activated when it gets an url with a
>suffix .pdf
>So while web-erp opens the pdf, then it should be opened with an url with
>suffix .pdf. Probably is this good enough for all download managers.
>Some free download managers at:

>www.freshdevices.com

>www.metaproducts.com

>With best regards,
>Dick Stins

----- Original Message -----
From: "Daintree" <p.d...@pa...>
To: "Web-ERP Developers" <Web...@li...>
Sent: Tuesday, February 24, 2004 7:49 PM
Subject: [Web-erp-developers] Download manager


> I need to try one of these things.
>
> Which one do you use?
>
> The work around sounds good.
>
> Phil

[Web-erp-developers] Download manager

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

I need to try one of these things.

Which one do you use?

The work around sounds good.

Phil

[Web-erp-developers] Re: Faulty download of PDF Files Resolved.

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

The problem it seems is two fold, here is the description of the
problem.

When requesting the print of an order or pdf more than once ( i.e
order/invoice/etc) the system will not print the second request and
instead notify the user it has already been printed.

The nature of downloading using integrated download management software,
such as I use because of bandwidth issues, causes the browser to request
the URL, interpret the headers, and if download a download manager is
plugged in, it will pass the URL to the download manager. This has the
effect of doing a second request to the web-server for the same
document.

The obvious solution is to disable the download plug-in, however this to
my mind is unreasonable considering that a lot of people use these for
purposes of download recovery. In other words they are there.

So can we work around a download manager? The answer is yes we can, by
introducing a document request identifier, random or sequentially
generated. When a document is requested a generated DocID is included as
a parameter, On first request this ID is stored with the
Order/Invoice/etc. When a document is re-requested and the DocID is the
same, allow a reprint.

Is this possible ?? It will eliminate a lot of support requests in
future, I am sure because it took me two days to figure out.

What do you think ??

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

[Web-erp-developers] POS

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

I am almost clueless about what is required for a POS system but here are a 
few links:

The perl ones ... ???
http://gibbon.sourceforge.net/
http://l-ane.sourceforge.net/

a php one ...
http://www.phppointofsale.com/

java ones ..

http://freemercator.sourceforge.net/
http://www.open-mag.com/features/Vol_65/POS/mercator.htm

General POS info:

http://www.linux-pos.org/openspos.html

Phil

Re: [Web-erp-developers] Re: WebERP Customization Done

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

On Tue, 2004-02-24 at 14:22, Phil Daintree wrote:
--Snip--
> This is effectively what we have with the script includes/ConnectDB.inc -
> this script contains all DB specific functions. It is simply (?) a matter of
> making a new ConnectDB.inc for Postgres, MS SQL Server, Oracle, DB2,
> Firebird etc.
I will see what I can do about conversion to Postgres, OK.
--Snip--
> If Perl is the client program there is only ever one user of a POS
> terminal.... its just there are Perl POS systems about - rather than
> complete re-invention of the wheel?
The reason I am pro python is simply that it can scale fairly well, I do
not mean network/user like. And of cause it is a language I know.
However I know about 10, learning number 11 should not present to much
of a problem. Send me a Perl POS link and I will see what it looks like
and if I can understand it. I still in the process of downloading Java
POS ( previous link) to evaluate as well.
--Snip--

> The EDI system could send orders and invoices true. We could manage
> something much simpler for internal transfers of orders though. To keep
> location stock and all aspects of the business updated is much more serious
> though. Would need a very clear spec on which aspects of the business
> transactions are to be reflected on each system.
--Snip--

It seems to me, that simply packaging all SQL relate posts to the
database in some form, would allow for these changes, I will investigate
this. Probably not SQL as it rely on Table ID's which 
is inappropriate. 
--Snip--

P.S. Leap of faiths for most of my clients is not a problem, installing
one today. Installing 1.7. And the next 2 weeks from now.

Kind Regards

Danie Brink su...@na...

Re: [Web-erp-developers] Re: WebERP Customization Done

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

On Tue, 2004-02-24 at 14:22, Phil Daintree wrote:
--Snip--
> This is effectively what we have with the script includes/ConnectDB.inc -
> this script contains all DB specific functions. It is simply (?) a matter of
> making a new ConnectDB.inc for Postgres, MS SQL Server, Oracle, DB2,
> Firebird etc.
I will see what I can do about conversion to Postgres, OK.
--Snip--
> If Perl is the client program there is only ever one user of a POS
> terminal.... its just there are Perl POS systems about - rather than
> complete re-invention of the wheel?
The reason I am pro python is simply that it can scale fairly well, I do
not mean network/user like. And of cause it is a language I know.
However I know about 10, learning number 11 should not present to much
of a problem. Send me a Perl POS link and I will see what it looks like
and if I can understand it. I still in the process of downloading Java
POS ( previous link) to evaluate as well.
--Snip--

> The EDI system could send orders and invoices true. We could manage
> something much simpler for internal transfers of orders though. To keep
> location stock and all aspects of the business updated is much more serious
> though. Would need a very clear spec on which aspects of the business
> transactions are to be reflected on each system.
--Snip--

It seems to me, that simply packaging all SQL relate posts to the
database in some form, would allow for these changes, I will investigate
this. Probably not SQL as it rely on Table ID's which 
is inappropriate. 
--Snip--

P.S. Leap of faiths for most of my clients is not a problem, installing
one today. Installing 1.7. And the next 2 weeks from now.

Kind Regards

Danie Brink su...@na...

Re: [Web-erp-developers] Re: WebERP Customization Done

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

> Yes PSQL does support this format, however another problem might present
> itself. Postgres convert all tablename and fieldnames to lowercase. This
> might present a problem, the correct method is to encase fieldnames in
> quotes, which allows for uppercase letters. Alternatively, we could
> simply keep all fieldnames and tablenames lowercase accross all
> databases. ?? ( I.e use the database default and let it decide whether
> it uses uppercase, lowercase or both.)


I think mysql does this too internally. Certainly using the mysql command
line client all fieldnames and table names are lower case.


> Another alternative would probably be something like what you do with
> the stylesheed/profile. I.e specify a set of functions that perform
> specific duties, like insert client, insert transaction, etc. These
> would then be scripted into one or more PHP files and will be included
> based on the database type. Therefore function names stay the same. This
> also allows for more advanced functionality on databases that supports
> it.

This is effectively what we have with the script includes/ConnectDB.inc -
this script contains all DB specific functions. It is simply (?) a matter of
making a new ConnectDB.inc for Postgres, MS SQL Server, Oracle, DB2,
Firebird etc.


> --Snip--
> > Python sure sounds great. I think integration to an existing POS system
> > going to be the best thing - ideally Python or Perl? I would not be keen
to
> > adopt a linux only solution though - much as I love this platform.
Whatever
> > we think of the platform, windows represents the biggest bulk of users
and
> > to put a lot of work into a linux only solution .... not sure about that
> > personally.
> --Snip--
> Perl as language does not really lend itself to scalability,
> however the great thing about Python is that it is capable of
> doing what we require. And I have seen this type of cross
> platform work done by another company in SA, and it works in
> our energy supply consortium's offices.

If Perl is the client program there is only ever one user of a POS
terminal.... its just there are Perl POS systems about - rather than
complete re-invention of the wheel?


> --Snip--
> > This sounds like quite a knotty one!!
> --Snip--
> Re : Sync
>
> Could it be that we could solve this problem with an EDI
> like initiative ?

The EDI system could send orders and invoices true. We could manage
something much simpler for internal transfers of orders though. To keep
location stock and all aspects of the business updated is much more serious
though. Would need a very clear spec on which aspects of the business
transactions are to be reflected on each system.

[Web-erp-developers] CVS imported and checked out

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

Gents,

Have re-imported CVS and checked it out to test it all ok. This now includes 
the DB dumps including foreign keys and the upgrade script to apply to dbs 
with data already in from a previous version.

Dick,

I can't repeat the problem you had with invoice printing ? How did you do it.

Phil

Re: [Web-erp-developers] Re: WebERP Customization Done

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

Danie,

> --Snip --
> > EANCOM EDI invoices - almost complete but really requires a test site to
get
> > up and running. I think I will have a go at receiving EDI orders too. I
will
> > include what I have and work with anyone who wants to use it.
> --Snip --
>
> Although I know what EDI is and more or less what it's
> suppose to do, I find that I am a minor here and that I have not yet
> had the opportunity to work with this type of interface. If you have
> some URL that will explain it, or some spec, I could learn it.

Introduction to EANCOM EDI

http://www.ean-int.org/data/introedi.pdf

Where I based the web-erp implementation of invoice messages from ...

http://www.leadtec.com.au/downloads/tradingpartners/HIWGINVOICImplementation
Guidev1.0.pdf


> > Foreign key db integrity - converted all tables to innodb - creation of
> > foreign keys throughout. An SQL script to upgrade users with data
already in.
> > Dick said to go slowly on this .. whoops !
> >
> --Snip --
> I believe this will safeguard the data, and I like your
> choice. I would like to see if I can somehow write a conversion script
> that can convert your MySql to Postgres, this would ease work in the
> future.

I dump the SQL from the current Mysql database and was kind of naively
hoping that with perhaps an afternoons tweaking it may be possible to run
this against postgres or other sql server and it would load up. Then changes
to ConnectDB.inc would be required to the equivalent PHP functions for
Postgres or whatever.

Does Postgres mind queires in the format:

SELECT field1, field2 FROM table1, table2 WHERE table1.field1=table2.field1;

This is perhaps the older school SQL syntax, if it does then this would mean
some re-writing of queries to put the INNER JOIN etc in there. Otherwise I
think it should run quite easily .... maybe ;-)


> --Snip --
> This is great, however, the reason I have done It the other way, is not
really for the benefit of being
> able to email, fax or save the pdf, although this is surely beneficial, it
is because it automates
> the process a little better, Click on print pdf, and up pops printer
interface (Please select, printer OR email,pdf or fax.)
> Although this sounds like much work, it really is not. simply change .pdf
extention to .prpdf under Linux system,
> add Mime-Type to browser to auto exec shell script with this file, then
convert pdf -> PS and pipe to kprinter.
> Under windows this might not be possible.
>
> However, we could integrate fax services directly into WebERP as well, and
maybe stream
> directly to specific printer, by analyzing file name. ex. (
lp1_CustomerInvoice.prpdf )
> which would allow the user to specify which printer he would like to use
and instead
> of using kprinter, it would simply stream directly to printer. ????
> Simple to do, however some terminal information will need to be stored in
a cookie.

This sounds pretty clever stuff - some way to stream directly to a remote
printer would be great too.

If it doesn't work for windows this cuts a lot of users out - sadly most!!
However, if we can make the experience better to linux users fantastic.

> Python is much like Java underneath, in that it is also byte code.
> It runs on more platforms than Java to my knowledge.
> Python as a language can be learned in a few hours and more importantly
> it is fairly easy to program, support classes and inheritance,
> but not some of the finer security restrictions. It can do everything Java
can
> and is a lot faster. To the point where it is possible to create OpenGL
simulations
> with it. It has access to a library called wxWindows which will allow
cross platform
> capability no changes necessary if programed right.
>
> For straight console type POS it is probably better to use Curses with
Python and
> only Linux OS as it should be a stand-alone system and cross platform is
probably
> not necessary.
>

Python sure sounds great. I think integration to an existing POS system
going to be the best thing - ideally Python or Perl? I would not be keen to
adopt a linux only solution though - much as I love this platform. Whatever
we think of the platform, windows represents the biggest bulk of users and
to put a lot of work into a linux only solution .... not sure about that
personally.


> --Snip --
> >
> > > 5. ) Figure out how to share/sync transactions accross
> > > multiple instalations. ( Requirement from a prospect )
> > >
> > Not quite sure what you mean here Danie. Obviously others can see the
> > transactions through the interface. A DB can be replicated accross
sites - or
> > just certain tables. This sounds like it may be a more specific request
> > perhaps than one of allround appeal.
> --Snip --
>
> One thing that people do not know about Africa as a whole is that it is
> incredibly expensive to run Wide Area Networks(WAN). Far more expensive
than
> running it in first world countries. So while the system has great appeal
> in its ability to run on a WAN, the reality is that in Africa it will be
> run on a local network, and the appeal is that we do not have to upgrade
work
> stations, as they will not be doing the work, only providing an interface
to
> the user. This leaves multiple branches that need to synchronize on a
daily
> basis. In terms of your system, Denver and Utha will have to synchronize.
> Utha can take orders for Denver and Denver can take orders for
> Utha, even though they do not have accurate stock movement tracking.
> This problem is resolved with good planning for reserve stock.
> This is standard practice in Africa (for now, until our telecoms companies
> sort things out.). The problem is that we need to synchronize databases
> properly so that each devision can see what happens in all the other
devisions.
> This is important because each director of the company generally
administers
> a location/branch but need full financial and stock information in order
to
> do his budgeting, especially in the retail market.
>
> Sounds funny, I know, but this is the reason none of the companies have
> been able to make a significant impact on the African markets. Even the
> German company I used to work for failed to pay heed to this problem,
> as a result they had to cancel all operations in Africa, and the investors
> backlash forced them to close most of their offices around the world,
> ( Nigeria, Spain, Romenia, America, all but one of their offices in
Britain
> and all but two of their offices in Germany) a lot of money.
>
> Companies that always had this type of synchronization have survived,
> AccPac, SAP, BAAN, Hogan. Although Hogan has gone through a rough patch
because of it's
> association with IBM and the AS400's, these days it runs on Linux.
>

This sounds like quite a knotty one!!

> --Snip --
> > Yes please!! If you could email me the amended scripts or better still
the
> > diffs I will include them.
> --Snip --
>
> I will as soon as I have figured out a way to safeguard for
interoperability.
> i.e. I need to make sure it does not break windows functionality. Once I
have
> done that I will email you a description of changes I made to
class.pdf.php
> and also to other changes to *.php files as required. I will also include
the
> shell-scripts with explanation on how to integrate it into Linux,
> possibly some setup script as well.
>
> I will also send the manuals once we complete them, firstly for comments
and revision,
> secondly for updates to get the manual compatible with 1.8.

I look forward to this.

> Further more, I would like to ask for system 1.8 for testing purposes.
> The CVS version as downloaded though the weekend does not work as it
> complains as soon as I try to create any PDF, through invoicing,
> however no problem with reprints. As we are currently concentrating
> on the manuals this will have to hold for a little while.
>
> I will then also send a chart of accounts for South Africa as soon as I
can
> convert it for 1.8.


I will hold any other work on the manuals in the interim - won't be hard -
its my least favourite part of this project. Amazed and chuffed that there
is another mug who believes in this enough to put the work in ... many
thanks.

>
> I have currently 5 clients that would like to use the WebERP system.
> One is a large retailer, by South African Standards, retailing
> and shipping furniture. (Looking at middle march.)


The POS problem .....


> Another is a supplier of scales to the farming community is
> South Africa and surrounding countries, they have a
> manufacturing requirement in addition. (Whenever I'm ready.)

Ideal - gives me a focus for the manufacturing functionality.

>
> Then there is a small manufacturing company, an Insurance company
> and a building company, specializing in fittings. (As soon as possible.)
> All have expressed and interest in WebERP.
> Then of cause we would like to run on WebERP ourselves.

>
> Any comments in this regard will be appreciated, even if you think we are
nuts and should
> trash the whole idea. That would not stop us from further assisting in the
development of WebERP.
>

No, I think you are wise!!

I use it in a subsidiary of my company with great success AUD 11 million.
Locations in Melbourne and Brisbane, plumbing products wholesaler. It is
solid, dependable and fast. Not that any reference from me would be
independent! But it really has been a raging success in our subsidiary. It
has been in use since July 2003 - there were a few bugs in pricing
initially - very demanding requirements here, but really no problems at all
since then. A failed mirrored disk had to be replaced - but zero down time.
I spent a Saturday there!!


Phil

[Web-erp-developers] New Input

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

> Great. New input!. What is Kiwi? Fruit?
Kiwi is what New Zealand people call A New Zealander! - it is also a fruit
(cake!) - (a "fruitcake" is english for a mad person!)

> Do you already have any new scripts? When are they available in CVS?

I have done the EDIMessageFormat.php I thought you were doing the tax levels
one !

>
> POS is interesting, but only when it supports multiple locations (lots of
> stores) with a good architecture and good support of a central backoffice
at
> the headquarter of the company.
> This might be a good start:
> http://sourceforge.net/projects/freemercator
>
Danie may be interested in this.

Phil

[Web-erp-developers] Re: Foreign Keys

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

Anything that is not DB specific!
Phil


> Phil,
>
> It sounds promishing. I hope this will be soon the standard for all
hosting
> providers.
> When that happens, then we might consider then database triggers, stored
> procedures ...
>
> With best regards,
>
> Dick Stins

> > MaxDB is now part of MySQL - all that is required is to change the table
> > type....
> >
> > Phil
> >
> > > Phil,
> > >
> > > I think that's great. I would be nice when we can support Oracle (my
> > > speciality) and the free maxdb, because clients do like the safety and
> > they
> > > have already an oracle dba and not an mysql dba.
> > >
> > > I will tell you soon how I do think we should setup the application
> > > architecture to get better control over changing database engines and
> our
> > > code.
> > >
> > > With best regards,
> > >
> > > Dick Stins
> > >

[Web-erp-developers] Re: recent sql create script?

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

Dick,

I will add you and Danie as developers on the project. With respect to CVS
updates I have not uploaded recent changes to CVS - the CVS was as at
Thursday last week. I did the script Friday/Sat. Danie reported some problem
with CVS too so I will sort this out too.

I would like to review code submissions in the first instance. Developer
access to CVS allows you to update the CVS. I would prefer to do all the
updating to CVS at least in the short term.

Phil


----- Original Message -----
From: "Stins, D.R." <d.r...@wo...>
To: <ph...@du...>
Sent: Saturday, February 21, 2004 11:30 AM
Subject: recent sql create script?


> Phil,
>
> I need to upgrade the create script too.
>
> Please make the script you wrote available in CVS.
>
> I would like to reserve/lock the upgrade create script, but I need to be a
> developer on the project:
>  Developer CVS Access via SSH
> Only project developers can access the CVS tree via this method. A SSH
> client must be installed on your client machine. Substitute modulename and
> developername with the proper values. Enter your site password when
> prompted.
>
>
>
> Please add me to the project in a developer role.
>
> My username is drstins
>
> Thanks for your help.
>
> With best regards,
>
> Dick Stins
>

[Web-erp-developers] DB Designer

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

Hi Julian,

I think Dick wrote to me with this link.

http://www.fabforce.net/dbdesigner4/

this is quite a cool tool - I did a little schamtic of the GL structure.

Phil

[Web-erp-developers] Foreign Keys

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

I started to work through this making a script up then realised ...

Starting from version 3.23.50, InnoDB allows you to add a new foreign key 
constraint to a table. 

This is actually quite a recent version and many folks would not have this 
functionality on their ISP's servers - so I think I'll make the script up and 
keep it as an option for those that wish to have the xtra database 
integrity/security. I have converted all the tables to Innodb as discussed 
and included this in the update script.

I'm currently using mysql-max 3.23.49!! Probably due for an update anyway.

Phil

Re: [Web-erp-developers] Understanding Tax structure

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

I think I see where you are headed. But to clarify, you think it would be
better to be able to have different levels for each item for each tax
authority.

I would agree with this if we were considering sales from locations outside
the single tax authority ie sales from the Paris warehouse compared to the
Mordijk warehouse. Since we are primarily concerned with the accounting for
tax due from sales from Mordijk I tend to think that the solution we have at
the moment is at least adequete. Although I have this nagging feeling that
really we should be considering making it work for sales from many different
tax authorities. I will think fuirther

Phil

----- Original Message -----
From: "Stins, D.R." <d.r...@wo...>
To: "Phil Daintree" <ph...@du...>
Sent: Friday, February 13, 2004 12:08 PM
Subject: Re: [Web-erp-developers] Understanding Tax structure


> Phil,
>
> I will do the script to modify the tax rates and adding and
> deleting of tax levels -ie the manipulation of the data in TaxAuthLevels.
>
> I agree with you to supply with every new release an upgrade script which
> converts the datamodel of the previous version to the datamodel of the new
> version without loosing any data.
>
> It might be an good idea to add an extra table in the datamodel which hold
> the release number to be able to check from which release the current
> datamodel is.
> It would be perfect when the upgrade script refuses to run when the
upgrade
> of that release is not supported by the script.
>
> To prevent lost of data, it is also better to have a separate drop (table)
> script and a separate create table script for a new install.
>
> With best regards,
>
> Dick Stins
>
> ----- Original Message -----
> From: "Phil Daintree" <ph...@du...>
> To: "Web ERP Developers" <Web...@li...>
> Sent: Thursday, February 12, 2004 9:30 PM
> Subject: [Web-erp-developers] Understanding Tax structure
>
>
> > In the example Gouda is alway TaxLevel 2 - from the single stockmaster
> > entry.
> > Tulips always taxLevel 3. These levels are not unique to each stock
item -
> > daffodils would probably also be level 3 and any other item that is
taxed
> in
> > the same manner as daffodils and tulips. Similarly TaxLevel 2 is for all
> > those items that should be taxed at 6% VAT for sales within TaxAuthority
> 1 -
> > VAT, 0% in TaxAuthority 2 and 0% in TaxAuthority3.
> >
> >
> > Dick, I have actually already coded all this up and it seems to work ok.
I
> > take your point in that the difficulty is in explaining how it works and
> how
> > to set it up - I will start work on that perhaps using some of our
> examples
> > from our discussion. Perhaps if I send you the latest scripts together
> with
> > our example stock items you may start to feel more comfortable with
this?
> I
> > am happy to change anything that you're not happy with.
> >
> > There are a number of changes to the db too so I made up an SQL script
to
> > allow the upgrade from 2.7 to 2.8 more without losing the data. I think
I
> > should make such a script with each new release and update the install
> > instructions.
> >
> > What I haven't done is the script to modify the tax rates and adding and
> > deleting of tax levels -ie the manipulation of the data in
TaxAuthLevels.
> >
> >
> > Phil
> >
> > ----- Original Message -----
> > From: "Stins, D.R." <d.r...@wo...>
> > To: "Phil Daintree" <ph...@du...>
> > Sent: Thursday, February 12, 2004 7:38 PM
> > Subject: Re: Re(2): [Web-erp-developers] Re: Level Description
> >
> >
> > > Phil,
> > >
> > > I will setup the tables (later)
> > >
> > > In you example, what are you doing when Gouda is related in
taxauthority
> 1
> > > to level 1
> > > and in taxuthority level 2 and Gouda is related in taxauthority to
level
> > 3?
> > >
> > > When I understand you quite well you want to setup a special dedicated
> > level
> > > for Gouda.
> > >
> > > With best regards,
> > >
> > > Dick Stins
> > >
> > > ----- Original Message -----
> > > From: "Phil Daintree" <ph...@du...>
> > > To: "Web ERP Developers" <Web...@li...>
> > > Sent: Thursday, February 12, 2004 3:33 AM
> > > Subject: Re: Re(2): [Web-erp-developers] Re: Level Description
> > >
> > >
> > > > Dick,
> > > >
> > > > I'm getting a little lost ...
> > > >
> > > > Could you make up the SQL for the tables as you think best and the
> > schema
> > > > for how it might work and then I can get the whole picture. I am
sorry
> > to
> > > be
> > > > a bit stupid on this. I have in my mind how I think it should work
but
> > > > clearly this does not work for you. I must understand what you have
in
> > > mind
> > > > and the SQL is much easier than a whole lot of words!!
> > > >
> > > > > Now back to the functional discussion:
> > > > > - one taxlevel per stock item is not good enough. This might be
only
> > > > > appropriate when you have to deal with one and only one
taxauthority
> > per
> > > > > stock item.
> > > > >
> > > > > When we decide that one taxauthority is appropriate, we can create
a
> > > > unique
> > > > > index at the level column in the taxauthlevel table. This implies
> that
> > > we
> > > > > follow the theory: a unique key level in the authtaxlevel table
and
> a
> > > > detail
> > > > > level key in the stockmaster.
> > > >
> > > > What I am proposing is as follows:
> > > >
> > > > Tax Authorities
> > > > Tax ID  TaxAuthorityDescription
> > > > 1                VAT
> > > > 2                Export
> > > > 3                Customer Pays VAT
> > > >
> > > > the field TaxID has a matching field in CustBranch
> > > >
> > > >
> > > > TaxAuthLevels
> > > >
> > > > Level    TaxAuthority    Rate
> > > > 1                1                    0
> > > > 2                1                    .06
> > > > 3                1                    .15
> > > > 1                2                    0
> > > > 2                2                    0
> > > > 3                2                    0
> > > > 1                3                     0
> > > > 2                3                    0
> > > > 3                3                    0
> > > >
> > > > The field Level has a matching field in StockMaster
> > > >
> > > > Consider Gouda cheese which is defined as having a tax level 2 in
the
> > > stock
> > > > master. We sell Gouda to a customer's branch in Amsterdam who is set
> up
> > as
> > > > belonging to TaxAuth 1 - VAT.
> > > >
> > > > The tax rate is found:
> > > >
> > > > SELECT Rate FROM TaxAuthLevels WHERE Level=2 AND TaxAuthority=1 -
VAT;
> > > >
> > > > and returns 6%.
> > > >
> > > > Consider tulips which are defined as having a tax level 3 in the
stock
> > > > master. We sell tulips to the same customer in Amsterdam who is set
up
> > as
> > > > belonging to TaxAuth 1 - VAT.
> > > >
> > > > The tax rate is found:
> > > >
> > > > SELECT Rate FROM TaxAuthLevels WHERE Level=3 AND TaxAuthority=1;
> > > >
> > > > and returns 15%.
> > > >
> > > > We also sell tulips to the same customer but to their branch in
> Zurich -
> > > > these are special none perishable tulips - the Zurich branch is set
up
> > as
> > > > belonging to TaxAuth 2 - Export.
> > > >
> > > > The tax rate is found:
> > > >
> > > > SELECT Rate FROM TaxAuthLevels WHERE Level=3 AND TaxAuthority=2;
> > > >
> > > > and returns 0%.
> > > >
> > > > So whilst TaxLevel is only one per item in the StockMaster, there
are
> > > > several in the TaxAuthLevels table it is only in combination with
the
> > > > TaxAuthority that we get a unique rate - hence the table definition.
> > > > TaxLevel in the TaxAuthLevels is not unique.
> > > >
> > > > Still nervous ?
> > > >
> > > > Phil
> > > >
> > > >
> > > >
> > > > -------------------------------------------------------
> > > > SF.Net is sponsored by: Speed Start Your Linux Apps Now.
> > > > Build and deploy apps & Web services for Linux with
> > > > a free DVD software kit from IBM. Click Now!
> > > > http://ads.osdn.com/?ad_id=1356&alloc_id=3438&op=click
> > > > _______________________________________________
> > > > Web-erp-developers mailing list
> > > > Web...@li...
> > > > https://lists.sourceforge.net/lists/listinfo/web-erp-developers
> > > >
> > >
> > >
> >
> >
> >
> > -------------------------------------------------------
> > SF.Net is sponsored by: Speed Start Your Linux Apps Now.
> > Build and deploy apps & Web services for Linux with
> > a free DVD software kit from IBM. Click Now!
> > http://ads.osdn.com/?ad_id=1356&alloc_id=3438&op=click
> > _______________________________________________
> > Web-erp-developers mailing list
> > Web...@li...
> > https://lists.sourceforge.net/lists/listinfo/web-erp-developers
>
>

[Web-erp-developers] Understanding Tax structure

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

In the example Gouda is alway TaxLevel 2 - from the single stockmaster
entry.
Tulips always taxLevel 3. These levels are not unique to each stock item -
daffodils would probably also be level 3 and any other item that is taxed in
the same manner as daffodils and tulips. Similarly TaxLevel 2 is for all
those items that should be taxed at 6% VAT for sales within TaxAuthority 1 -
VAT, 0% in TaxAuthority 2 and 0% in TaxAuthority3.


Dick, I have actually already coded all this up and it seems to work ok. I
take your point in that the difficulty is in explaining how it works and how
to set it up - I will start work on that perhaps using some of our examples
from our discussion. Perhaps if I send you the latest scripts together with
our example stock items you may start to feel more comfortable with this? I
am happy to change anything that you're not happy with.

There are a number of changes to the db too so I made up an SQL script to
allow the upgrade from 2.7 to 2.8 more without losing the data. I think I
should make such a script with each new release and update the install
instructions.

What I haven't done is the script to modify the tax rates and adding and
deleting of tax levels -ie the manipulation of the data in TaxAuthLevels.


Phil

----- Original Message -----
From: "Stins, D.R." <d.r...@wo...>
To: "Phil Daintree" <ph...@du...>
Sent: Thursday, February 12, 2004 7:38 PM
Subject: Re: Re(2): [Web-erp-developers] Re: Level Description


> Phil,
>
> I will setup the tables (later)
>
> In you example, what are you doing when Gouda is related in taxauthority 1
> to level 1
> and in taxuthority level 2 and Gouda is related in taxauthority to level
3?
>
> When I understand you quite well you want to setup a special dedicated
level
> for Gouda.
>
> With best regards,
>
> Dick Stins
>
> ----- Original Message -----
> From: "Phil Daintree" <ph...@du...>
> To: "Web ERP Developers" <Web...@li...>
> Sent: Thursday, February 12, 2004 3:33 AM
> Subject: Re: Re(2): [Web-erp-developers] Re: Level Description
>
>
> > Dick,
> >
> > I'm getting a little lost ...
> >
> > Could you make up the SQL for the tables as you think best and the
schema
> > for how it might work and then I can get the whole picture. I am sorry
to
> be
> > a bit stupid on this. I have in my mind how I think it should work but
> > clearly this does not work for you. I must understand what you have in
> mind
> > and the SQL is much easier than a whole lot of words!!
> >
> > > Now back to the functional discussion:
> > > - one taxlevel per stock item is not good enough. This might be only
> > > appropriate when you have to deal with one and only one taxauthority
per
> > > stock item.
> > >
> > > When we decide that one taxauthority is appropriate, we can create a
> > unique
> > > index at the level column in the taxauthlevel table. This implies that
> we
> > > follow the theory: a unique key level in the authtaxlevel table and a
> > detail
> > > level key in the stockmaster.
> >
> > What I am proposing is as follows:
> >
> > Tax Authorities
> > Tax ID  TaxAuthorityDescription
> > 1                VAT
> > 2                Export
> > 3                Customer Pays VAT
> >
> > the field TaxID has a matching field in CustBranch
> >
> >
> > TaxAuthLevels
> >
> > Level    TaxAuthority    Rate
> > 1                1                    0
> > 2                1                    .06
> > 3                1                    .15
> > 1                2                    0
> > 2                2                    0
> > 3                2                    0
> > 1                3                     0
> > 2                3                    0
> > 3                3                    0
> >
> > The field Level has a matching field in StockMaster
> >
> > Consider Gouda cheese which is defined as having a tax level 2 in the
> stock
> > master. We sell Gouda to a customer's branch in Amsterdam who is set up
as
> > belonging to TaxAuth 1 - VAT.
> >
> > The tax rate is found:
> >
> > SELECT Rate FROM TaxAuthLevels WHERE Level=2 AND TaxAuthority=1 - VAT;
> >
> > and returns 6%.
> >
> > Consider tulips which are defined as having a tax level 3 in the stock
> > master. We sell tulips to the same customer in Amsterdam who is set up
as
> > belonging to TaxAuth 1 - VAT.
> >
> > The tax rate is found:
> >
> > SELECT Rate FROM TaxAuthLevels WHERE Level=3 AND TaxAuthority=1;
> >
> > and returns 15%.
> >
> > We also sell tulips to the same customer but to their branch in Zurich -
> > these are special none perishable tulips - the Zurich branch is set up
as
> > belonging to TaxAuth 2 - Export.
> >
> > The tax rate is found:
> >
> > SELECT Rate FROM TaxAuthLevels WHERE Level=3 AND TaxAuthority=2;
> >
> > and returns 0%.
> >
> > So whilst TaxLevel is only one per item in the StockMaster, there are
> > several in the TaxAuthLevels table it is only in combination with the
> > TaxAuthority that we get a unique rate - hence the table definition.
> > TaxLevel in the TaxAuthLevels is not unique.
> >
> > Still nervous ?
> >
> > Phil
> >
> >
> >
> > -------------------------------------------------------
> > SF.Net is sponsored by: Speed Start Your Linux Apps Now.
> > Build and deploy apps & Web services for Linux with
> > a free DVD software kit from IBM. Click Now!
> > http://ads.osdn.com/?ad_id=1356&alloc_id=3438&op=click
> > _______________________________________________
> > Web-erp-developers mailing list
> > Web...@li...
> > https://lists.sourceforge.net/lists/listinfo/web-erp-developers
> >
>
>

Re: Re(2): [Web-erp-developers] Re: Level Description

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

Dick,

I'm getting a little lost ...

Could you make up the SQL for the tables as you think best and the schema
for how it might work and then I can get the whole picture. I am sorry to be
a bit stupid on this. I have in my mind how I think it should work but
clearly this does not work for you. I must understand what you have in mind
and the SQL is much easier than a whole lot of words!!

> Now back to the functional discussion:
> - one taxlevel per stock item is not good enough. This might be only
> appropriate when you have to deal with one and only one taxauthority per
> stock item.
>
> When we decide that one taxauthority is appropriate, we can create a
unique
> index at the level column in the taxauthlevel table. This implies that we
> follow the theory: a unique key level in the authtaxlevel table and a
detail
> level key in the stockmaster.

What I am proposing is as follows:

Tax Authorities
Tax ID  TaxAuthorityDescription
1                VAT
2                Export
3                Customer Pays VAT

the field TaxID has a matching field in CustBranch


TaxAuthLevels

Level    TaxAuthority    Rate
1                1                    0
2                1                    .06
3                1                    .15
1                2                    0
2                2                    0
3                2                    0
1                3                     0
2                3                    0
3                3                    0

The field Level has a matching field in StockMaster

Consider Gouda cheese which is defined as having a tax level 2 in the stock
master. We sell Gouda to a customer's branch in Amsterdam who is set up as
belonging to TaxAuth 1 - VAT.

The tax rate is found:

SELECT Rate FROM TaxAuthLevels WHERE Level=2 AND TaxAuthority=1 - VAT;

and returns 6%.

Consider tulips which are defined as having a tax level 3 in the stock
master. We sell tulips to the same customer in Amsterdam who is set up as
belonging to TaxAuth 1 - VAT.

The tax rate is found:

SELECT Rate FROM TaxAuthLevels WHERE Level=3 AND TaxAuthority=1;

and returns 15%.

We also sell tulips to the same customer but to their branch in Zurich -
these are special none perishable tulips - the Zurich branch is set up as
belonging to TaxAuth 2 - Export.

The tax rate is found:

SELECT Rate FROM TaxAuthLevels WHERE Level=3 AND TaxAuthority=2;

and returns 0%.

So whilst TaxLevel is only one per item in the StockMaster, there are
several in the TaxAuthLevels table it is only in combination with the
TaxAuthority that we get a unique rate - hence the table definition.
TaxLevel in the TaxAuthLevels is not unique.

Still nervous ?

Phil

Re: Re(2): [Web-erp-developers] Re: Level Description

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

Wow Dick, that is some db!
Makes our 30 row table look minuscule... but even if it was 600 million rows
...

Do you think we don't need a unique index on these two fields at all?
Perhaps we just use the application to ensure we don't put in two VAT, Level
1s by mistake. I think it is a good think that the db returns an error and
the error is trapped if a user attempts to insert a duplicate TaxAuthority
and TaxLevel record.

If then you consider that we do need this index to ensure the uniqueness of
each TaxAuthority/Level combination then why would we add another field and
another unique index which would take up additional space - and slow us
down!

Every query we will do on this table will require 2 indexes - one for the
TaxLevel and another for the TaxAuthority or better still - in terms of
query speed -  for some SQL servers a joint/compound index as we have to
identify the rate.

I wouldn't claim to be an SQL guru and certainly without the experience you
have, although I have read widely on database theory and used MS SQL Server,
Sybase Adaptive Server, Oracle, and now MySQL. I have put a fair bit of
thought into the dB structure of web-erp and many have complimented it. I
agree completely in the sense of making the system right by design rather
than kludges later on when the error is too painful to fix correctly.

If you could point me at some reading that shows the error of compound
indexes I would be keen to learn.

The process for returning the appropriate rate for a line item is as
follows:

The TaxAuthority comes from the customer branch CustBranch table.
The TaxLevel comes from the StockMaster.

Both the customer branch and the stock item must be retrieved when an
invoice or credit is being created - the customer/branch is a requirement
before anything can start. The stock items are selected individually and
details retrieved - now with the TaxLevel field. The scripts get the tax
rate from the TaxAuthLevels table there is a function in
SQL_CommonFunctions.inc that does this for each line on the invoice.

Phil


----- Original Message -----
From: "Stins, Dick" <DR...@zi...>
To: "Daintree, Phil" <ph...@du...>
Sent: Wednesday, February 11, 2004 8:07 PM
Subject: Re(2): [Web-erp-developers] Re: Level Description


> Phil,
>
> Ofcourse you are right with the additional input. Next to this you still
need the taxauthlevel table with the rates and some method to tell which
taxauthority is applicable for the item in the warehouse.
>
> I know what indexes are and what the effect is at the storage. I have the
maintenance of a database which exists of tables with over 600 million of
rows and a storage of 50 gb per table exclusive the storage of indexes.
Optimising this is something between science and art.
>
> Since we have the datastructure not completed, you can't see any issues
with storage (=speed).
>
> With best regards,
>
> Dick Stins
>
> -----Original Message-----
> From: Daintree, Phil <ph...@du...>
> To: Web...@li...
<Web...@li...>
> Sent: 11-2-2004 01:36
> Subject: Re: [Web-erp-developers] Re: Level Description
>
>
> > But I see this as a workaround and when things get complicated with lots
> of taxathaurisations with lots of artificial created level to get the
stuff
> done  then are the web-erp users stuck in a very big confusing
> administration.
> > A structural stable solution is to keep a separate table (looks like
your
> other suggestion) with two columns:
> > stockid
> > taxauthlevelid
>
> Think of the additional input required too....
>
> >
> > For this solution you should understand that in this case you have a
very
> storage saving technical primary key taxauthlevelid column compared to the
> two columns primary key
> > taxauthorizationid + level in the taxauthlevel table.
> >
> > Concerning your search for the two columns primary key, I do not
> understand where you find which taxauthority is applicable for the level
> registrated at the stockmaster for a specific stockid. Do I miss
something?
> >
>
> we will only be ever inquiring on this table to get the applicable rate of
> VAT/GST or whatever we call it. For this we will need the TaxAuthority and
> the TaxLevel, it matters not that TaxLevel is in one table and
TaxAuthority
> is in another - the index on both fields is actually an advantage for
faster
> searches in some RDBMS eg SQL Server and Oracle use these indexes. I think
> mysql is pretty good with them too.
>
>
>
>
> -------------------------------------------------------
> The SF.Net email is sponsored by EclipseCon 2004
> Premiere Conference on Open Tools Development and Integration
> See the breadth of Eclipse activity. February 3-5 in Anaheim, CA.
> http://www.eclipsecon.org/osdn
> _______________________________________________
> Web-erp-developers mailing list
> Web...@li...
> https://lists.sourceforge.net/lists/listinfo/web-erp-developers
> www.Zion-IT.com
> in...@Zi...
>
> Zion-IT b.v.
> Postbus 28048
> 3828 ZG  Hoogland
> tel. + 31 (0) 33 455 13 34
>
> P.S. Nieuw: Hosting van Websites. Hoge kwaliteit, lage prijs. 6,5 euro per
maand voor 100mb schijfruimte, 5000mb bandbreedte + ....
>                     Tijdelijk: eerste basis pagina gratis!!
>

Re: [Web-erp-developers] Re: Level Description

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

> For the taxlevel stuff you suggest:
> "
> However, these weird things could equally be accomodated by having an
extra
> tax level. Cheddar, milk, bread all level 1, Gouda, Edam, Brie level 2,
> childrens clothes level 1 OSH KOSH level 2, Nike, Hugo Boss mensware,
level
> 2. Level 2 tax rates would be 0% in many TaxAuthorities as well as Level 1
> in Australia though level 2 is taxable at 10%.
> "
>
> But I see this as a workaround and when things get complicated with lots
of taxathaurisations with lots of artificial created level to get the stuff
done  then are the web-erp users stuck in a very big confusing
administration.
> A structural stable solution is to keep a separate table (looks like your
other suggestion) with two columns:
> stockid
> taxauthlevelid

Since the solution doesn't work with inventory locations invoicing in
different tax authorities most installations will only have 1 or 2 tax
authorities. In my industry, plumbing wholesale, all products are taxable at
10% in Australia - there is no need at all for this TaxLevel stuff. In the
UK only where a businesses product range spans food/healthcare/childrens
clothes and other taxable stuff would several tax levels be necessary - if
there is only local market, no export only one tax authority would be
required. If export is required at 0% then another tax authority solves this
nicely.

In Holland it sounds as though there may be a couple of  TaxLevels and
possible 2 TaxAuthorities - 1 to deal with the approved customer paying the
VAT the other for normal supplies. Both tax levels would be 0% for the
Customer pay VAT Tax Authority. Potentially a 3rd tax authority for exports
although the Customer Pays VAT would probably cover that too.

The whole purpose of these tax levels is to allow different rates for
different products. I think we have a very flexible and adaptable solution
here.

>
> For this solution you should understand that in this case you have a very
storage saving technical primary key taxauthlevelid column compared to the
two columns primary key
> taxauthorizationid + level in the taxauthlevel table.

We need an index on both these fields to ensure we only have unique
TaxLevel/TaxAuthority s in any event. In fact we are actually saving space
by not having an additional and redundant index and field even in a large
table this would be the right answer - not that it really matters for a 30
row table !!

Must be keeping you up over there - must be time you were asleep!!

Phil

Re: [Web-erp-developers] Re: Level Description

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

> But I see this as a workaround and when things get complicated with lots
of taxathaurisations with lots of artificial created level to get the stuff
done  then are the web-erp users stuck in a very big confusing
administration.
> A structural stable solution is to keep a separate table (looks like your
other suggestion) with two columns:
> stockid
> taxauthlevelid

Think of the additional input required too....

>
> For this solution you should understand that in this case you have a very
storage saving technical primary key taxauthlevelid column compared to the
two columns primary key
> taxauthorizationid + level in the taxauthlevel table.
>
> Concerning your search for the two columns primary key, I do not
understand where you find which taxauthority is applicable for the level
registrated at the stockmaster for a specific stockid. Do I miss something?
>

we will only be ever inquiring on this table to get the applicable rate of
VAT/GST or whatever we call it. For this we will need the TaxAuthority and
the TaxLevel, it matters not that TaxLevel is in one table and TaxAuthority
is in another - the index on both fields is actually an advantage for faster
searches in some RDBMS eg SQL Server and Oracle use these indexes. I think
mysql is pretty good with them too.

[Web-erp-developers] Tax on supplies

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

For AP invoices is it best to leave tax for the user's input or calculate it 
out at a default rate. 
Perhaps it would be best to have a default tax level that might apply to a 
suppliers supplies??

Any thoughts Dick.

Phil

[Web-erp-developers] Modifying Tax Rates

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

Dick,

I was hoping you would write a script to enable tax rates to be updated?

Phil
> 
> It might be a good idea to create a separate function/procedure to
> insert/update the rates, so we can add later very easy any functionality
> (like logging rates.... ) or turn it off again.

 

[Web-erp-developers] Fw: Confirm Dispatch Invoice.php

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

Hi Dick,

I dont think it is not a major to change the rates on the day of the change.
The rate at which tax is paid on every invoices is recorded firstly in the
GL and secondly against the stock movement - so I think we have ample
logging of rates used.

Tax Authorities are the authority that collects the taxes. Different
authorities in different countries mainly or different states in the USA. I
don't know how it works in the US but lets say the sales tax in New York is
10% and sales to customers in Michigan attract sales tax at a different rate
payable to a different state tax authority.

In the UK Australia and NZ, the Inland Revenue, Australian Tax Office and
the IRD are the relevant tax authorities. Sales to customers that fall under
the ATO from a company in NZ do not attract any tax - ie exempt export.
However, the same customer who has a branch in Auckland NZ will fall under a
different tax authority the IRD - attracting GST @ 12.5% for the same item
delivered there.

So the tax authority is the factor relevant based on where the branch of the
customer is who is receiving the supply. This is probably stuff that should
go in the manual.

Hope this description of what a tax authority is helps.

Phil

----- Original Message -----
From: "Stins, Dick" <DR...@Zi...>
To: <ph...@du...>
Sent: Monday, February 09, 2004 11:29 AM
Subject: Re: Confirm Dispatch Invoice.php


> Phil,
>
> I think we miss a very important feature. All serious system do have it.
>
> The goverment change rules from time to time.
>
> What happens when a stock item is move from a high to low rate?
>
> What happens when the government decides to change rates?
>
> So what do we need to registrate these changes:
> - historical data! (sometimes called journalling or logging data)
>
> But we do not like slow systems. So we need a small denormalised system.
>
> (I do not yes really understand what a taxauthority is?)
>
> For example:
> Taxauthlevels needs a extra column with a startdate.
>
> Taxauthlevelshist is a new table wich contains all data of taxauthlevels
> (also the current active row, because we
> lose a join condition when we are querying current + historical data).
> The taxauthlevelhist table needs a end date too, because this speeds up
> queries. Also in oracle databases of versions lower than 10g.
>
> For a start, we try never to use historical data, but we need it to be
able
> to audit calculations and ... for ... (you as an accountant should be able
> to tell me why).
>
> When we create a invoice (I guess this is table debtortrans), it will be
> harder, because the VAT tax level is dependent of the date. The VAT level
is
> also dependend of the date.
>
> So to simplify this issue, we should denormalise the VAT calculation. When
> the invoice is created (the date when the order is delivered?), we create
> for every VAT level of the appropriate Taxauthority an extra invoice line
> with the total VAT level amount.
> This prevents us to query in the historical tables to calculate the
invoice
> after a change in the VAT level of the ordered product and/or rate.
> We definitely need to put the VAT amounts per level at the invoice.
>
> I hope you understand my example?
>
> I apologise to be late with this proposal, but unfortunately is my time
> limited.
>
> Where stands GST for?
>
> With best regards,
>
> Dick Stins
>
>
> ----- Original Message -----
> From: "Phil Daintree" <ph...@du...>
> To: <DR...@Zi...>
> Sent: Sunday, February 08, 2004 11:04 AM
> Subject: Confirm Dispatch Invoice.php
>
>
> > Dick,
> >
> > Pls find updated ConfirmDispatchInvoice.php
> > I think this works ok for the line item tax rates.
> >
> > Perhaps invoices/credits should show the line item GST rates and amounts
> too??
> >
> > I have dropped the field in TaxAuthorities that holds the taxrate. As
all
> tax
> > rates should now come from TaxAuthLevels taxble.
> >
> > Still have to work through the two credit note scripts - and then
> > suppliersinvoices....
> >
> > Phil
>
>

[Web-erp-developers] Re: development architecture

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

BlankRight oh then, I will update the database tables today - should be able
to send you later on today. I have done quite a bit on EDI invoices and
sending these. This is a little technical to set up - so I have been writing
up the manual at the same time. Not started orders yet. I obtained the
EANCOM standard format as well as the hardware industry message
implementation guidelines (MIG) these are similar to the retail industry -
the main users of EDI. It seems there are slight differences between trading
partners so I have made it customisable for each customer.

I think due to the complexity of the tax issues and implications at every
turn it might be best for me to have a crack at this. I hope this is ok.
Could you could make a form to maintain the TaxAuthLevels table? This would
allow the levels that are entered against the tax authorities rates to be
maintained. Add a level and delete a level (only if no stock items have the
level against them). Edit the rate and name of the level. Perhaps link to
from the tax authorities form to edit the rates.

Emailing of invoices is easy - but of course would only wish to email a
particular customers invoices, this would need a new option to print only
the selected customer's invoices in the range selected. I think emailing of
pdfs would be preferable to the html. Many customers - at least in NZ and
Australia do not want email invoices they still like paper.

Phil
  ----- Original Message -----
  From: Stins, D.R.
  To: Daintree
  Sent: Sunday, February 08, 2004 12:53 AM
  Subject: Re: development architecture


  Phil,

  This is ok. Now we need only an enhancement to send the invoices
automatically to the client per e-mail.


  With best regards,

  Dick Stins
    ----- Original Message -----
    From: Daintree
    To: Stins, Dick
    Sent: Wednesday, February 04, 2004 3:07 AM
    Subject: Re: development architecture


    Dick,

    The system produces html invoices and credit notes which are of course
emailable under most mail clients.

    Phil

      For invoices per e-mail, it might be great to support also a plain
text invoices (the e-mail body), but for me it has low priority (the
language issue is of low priority too).


      What do you mean with OA?

      sorry ..Open Accounting

[Web-erp-developers] Re: Latest

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

A few more thoughts about tax calcs. Sherif may like to cut in here too -
although I think we have decided to go only half way on the OA solution ie
not Tax groups with mutiple tax rates per line as this is not applicable in
many enviroments. We are really only looking at as many different rates as
the user wishes for a tax authority depending on the item being sold.

With the additional table I described for TaxAuthLevels - recapping with
another field I thought of !

TaxAuthLevelID - unique id for the record - primary key - to be held against
each stock movement - the tax authority to be stored in the DebtorTrans /
SuppTrans
TaxAuthority - related to the tax authority recorded in the customer
branch - GL codes extracted from the Tax Authority record
TaxAuthLevelDescription VarChar(20)  (zero rated, taxable supplies, exempt
or some such)
LevelCode TinyInt  - the tax level to be held against the stock item (stock
item gives us finer control over tax than stock categories)

The rate is then determined from this table using the StockMaster -
TaxLevelCode - a new field in stock master and the TaxAuthority from the
CustBranch table - already there. There is a unique entry in the above table
for the Level. We will need a new script perhaps linked from the Tax
Authorities page that enables the input of the levels of tax applicable to
the Tax Authority at the levels existing in the StockMaster TaxLevelCode for
all parts. Perhaps these could be created automatically when a new
TaxAuthority is created with 0% for all levels and be available for editing
the rates only. The stock.php script will also need to allow entry of the
tax level - if the level does not exist in the table above for all
TaxAuthorities it will need to be created. If the item is changed to a
different level then if no other stock items need this level then the
TaxAuthLevel that was defined for this part should then be deleted. -
referntial integrity rules to ensure when looking up a tax rate one can
always be returned. perhaps the TaxAuthLevel in the stock master can only
contain 1 to 4 to limit the ability to stuff up!!

We need 2 additional fields in StockMoves - the TaxAuthorityLevel and  the
TaxRate - or maybe just one the rate - we dont need to store the amount
since the price x quantity x rate will do the trick.

All scripts that create invoices or credit notes need to be updated to
obtain the correct tax rate. 2 credit note scripts and one invoice script.
Perhaps a function in SQL_CommonFunctions.inc that takes the StockID and the
TaxAuthority the DebtorNo and the BranchCode (probably a db pointer too) -
returning an array of the Level Description and the rate. I normally like to
minimise the round trips to the sql server by getting all the information
required in one bite - even though the SQL can get a little tricky.
Unfortunately, this process we are considering will mean several extra trips
to the database for information - one for each line item - but its all for a
good cause. The existing SQL that gets the rate will need to be modified in
each of these scripts. The field for TaxRate in TaxAuthorities can now be
dropped too. Each script will need the creation of stock movements to be
amended with the rate and the TaxAuthLevel code. Invoice/credit note pages
for pdf and html versions will need mods too, to show tax on each line and
accumulate the total. The ConfirmDispatchInvoice.php script will need to
allow manual over-ride of tax calculations at the line level. The manual
entry in total would no longer be appropriate.

GL integration may be interesting it would be nice to consolidate the tax as
one entry per invoice ... I think ? rather than a gl entry for every line on
every invoice may be going overboard?? This would probably mean that the GL
integration could be left alone once the total tax is accumulated into one
total.

This whole tax thing, is a serious blue in the fundamental design really.
Sorry chaps!!

Phil

----- Original Message -----
From: "Stins, D.R." <d.r...@wo...>
To: <ph...@du...>
Sent: Wednesday, February 04, 2004 12:35 PM
Subject: Re: Latest


> Phil,
>
> Thanks.  I develop still at window2 2000, because I do not yet have a
> machine for linux.
> I use ultimate zip (freeware/adware). I handles bz2 fine.
>
> For the VAT/TAX development, when we agree the design/functionality to
> implement + the datamodel, I would be pleased to implement this or develop
a
> prototype. It would be a good excercise to get familiar with lots of
web-erp
> scripts.
>
> Did you receive my first proposal/brainwave for VAT implementation?
>
> When you want a complicated version, we can consider a datamodel with vat
> per regions, vat per subregions, rate per region, vat codes, cross
reference
> region vat code + product. And ofcourse several complicated not very easy
to
> understand relations between those entities. Ofcourse is this much harder
to
> implement.
>
> I also read the information of Sherif. For The Netherlands, we probably do
> not need taxgroups. We only have two tax groups. VAT and other tax. May be
> is the current tax type good enough, because like you said, you can join a
> gl account automatically.
>
> With best regards,
>
> Dick Stins
>
> ----- Original Message -----
> From: "Phil Daintree" <ph...@du...>
> To: <DR...@Zi...>
> Sent: Tuesday, February 03, 2004 9:59 AM
> Subject: Latest
>
>
> > Hi Dick,
> >
> > Here is the latest... there is some work on the EDI - and the bug
fixes -
> > excluding the contract link - this script is yet to be written. No tax
> > developments yet  - this will be after EDI.
> >
> > Not sure if you develop in windows or linux - assumed linux bz2 its the
> best
> > compression.
> >
> > Phil
>
>