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