I have a new tax here in British Columbia, Canada (HST) that replaces
the previous two taxes (GST + PST). Coincidentally the new tax rate is
the same as
the total of the two old rates.
My original plan was to remove PST and GST from the system and create
new HST records in the chart of accounts and tax table, change all the
tax tables and eat the issue of old sales/purchase orders and invoices
looking funny on reprint. The totals would still be correct. Unless
there was an edge case where someone didn't pay PST. And then I
realized there were probably edge cases...
Looking at the Tax.pm program, I began fantasizing about inserting a
bit of code to record the end date of a tax. After convincing myself
it might work, I added a new column 'validfrom' that would track the
date a tax was first used. I modified the ledgersmb/bin/am.pl to
show the new field in the System/tax form and it was good. I added
linkage in customertax, vendortax and partstax to bring the new HST
records on line.
I changed ledgersmb/LedgerSMB/Tax.pm, replacing the line:
coalesce(validto::timestamp, 'infinity'::timestamp) >= ?
with the new line:
? BETWEEN SYMMETRIC coalesce(validfrom::timestamp, '-infinity'::timestamp)
AND coalesce(validto::timestamp, 'infinity'::timestamp)
The symmetric bit might be over the top but it works until proper
error checking is coded (yeah...right).
Then I looked around and found ledgersmb/LedgerSMB/AA.pm with
references to the validto field. A few lines below the "# get taxes"
comment I inserted the above new line (replacing the ? with $transdate
of course)
So now I can create a new Sale/Purchase Order and Invoice with the new
HST appearing just fine. The old sales/purchase records seem fine.
A caveat to all of this: my customer uses ledgersmb as a giant
typewriter. Most of the useful features (G/L, Inventory, Purchase
Orders,...) are ignored. As long as the customer invoice looks right
and the Aged Trial Balance is accepted by the bank, they are happy
campers. I can't change them. So if there are problems with my
solution, they may exist in other parts of the system.
Below are the output from psql of the changed table, and diff's of the changed
files in case someone is interested in using them or has improvements to offer.
(I originally attached replacement sources for these but bumped into a
file size
for messages on this list).
lsmb-db=# \d tax
Table "public.tax"
Column | Type | Modifiers
--------------+---------+--------------------
chart_id | integer | not null
rate | numeric |
taxnumber | text |
validto | date |
pass | integer | not null default 0
taxmodule_id | integer | not null default 1
validfrom | date |
Indexes:
"tax_pkey" PRIMARY KEY, btree (chart_id)
Foreign-key constraints:
"tax_chart_id_fkey" FOREIGN KEY (chart_id) REFERENCES chart(id)
"tax_taxmodule_id_fkey" FOREIGN KEY (taxmodule_id) REFERENCES
taxmodule(taxmodule_id)
# diff -w -d -u AM.20100701.pm AM.pm
--- AM.20100701.pm Wed Mar 17 16:55:03 2010
+++ AM.pm Thu Jul 1 15:53:41 2010
@@ -1543,7 +1543,7 @@
my $query = qq|
SELECT c.id, c.accno, c.description,
t.rate * 100 AS rate, t.taxnumber, t.validto,
- t.pass, m.taxmodulename
+ t.pass, m.taxmodulename, t.validfrom
FROM chart c
JOIN tax t ON (c.id = t.chart_id)
JOIN taxmodule m ON (t.taxmodule_id = m.taxmodule_id)
@@ -1589,19 +1589,23 @@
$query = qq|
INSERT INTO tax (chart_id, rate, taxnumber, validto,
- pass, taxmodule_id)
- VALUES (?, ?, ?, ?, ?, ?)|;
+ pass, taxmodule_id, validfrom)
+ VALUES (?, ?, ?, ?, ?, ?, ?)|;
my $sth = $dbh->prepare($query);
foreach my $item ( split / /, $form->{taxaccounts} ) {
my ( $chart_id, $i ) = split /_/, $item;
my $rate =
$form->parse_amount( $myconfig, $form->{"taxrate_$i"} ) / 100;
+ my $validfrom = $form->{"validfrom_$i"};
+ $validfrom = undef if not $validfrom;
+
my $validto = $form->{"validto_$i"};
$validto = undef if not $validto;
+
my @queryargs = (
$chart_id, $rate, $form->{"taxnumber_$i"},
- $validto, $form->{"pass_$i"}, $form->{"taxmodule_id_$i"}
+ $validto, $form->{"pass_$i"}, $form->{"taxmodule_id_$i"},
$validfrom
);
$sth->execute(@queryargs) || $form->dberror($query);
@@ -1888,3 +1892,4 @@
}
1;
+
# diff -w -d -u Tax.20100701.pm Tax.pm
--- Tax.20100701.pm Thu Jul 1 14:23:38 2010
+++ Tax.pm Fri Jul 2 06:51:01 2010
@@ -50,7 +50,8 @@
FROM tax t INNER JOIN chart c ON (t.chart_id = c.id)
INNER JOIN taxmodule m ON (t.taxmodule_id =
m.taxmodule_id)
WHERE c.accno = ? AND
- coalesce(validto::timestamp, 'infinity'::timestamp) >= ?
+ ? BETWEEN SYMMETRIC
coalesce(validfrom::timestamp, '-infinity'::timestamp)
+ AND coalesce(validto::timestamp, 'infinity'::timestamp)
ORDER BY
coalesce(validto::timestamp,
'infinity'::timestamp) ASC|;
my $sth = $dbh->prepare($query);
# diff -w -d -u am.20100701.pl am.pl
--- am.20100701.pl Thu Jul 1 14:42:04 2010
+++ am.pl Thu Jul 1 16:06:12 2010
@@ -2024,7 +2024,7 @@
$form->format_amount( \%myconfig, $ref->{rate} );
$form->{"taxdescription_$i"} = $ref->{description};
- for (qw(taxnumber validto pass taxmodulename)) {
+ for (qw(taxnumber validfrom validto pass taxmodulename)) {
$form->{"${_}_$i"} = $ref->{$_};
}
$form->{taxaccounts} .= "$ref->{id}_$i ";
@@ -2057,6 +2057,7 @@
<th></th>
<th>| . $locale->text('Rate') . qq| (%)</th>
<th>| . $locale->text('Number') . qq|</th>
+ <th>| . $locale->text('Valid From') . qq|</th>
<th>| . $locale->text('Valid To') . qq|</th>
<th>| . $locale->text('Ordering') . qq|</th>
<th>| . $locale->text('Tax Rules') . qq|</th>
@@ -2086,6 +2087,7 @@
print qq|</th>
<td><input name="taxrate_$i" size=6 value=$form->{"taxrate_$i"}></td>
<td><input name="taxnumber_$i" value="$form->{"taxnumber_$i"}"></td>
+ <td><input name="validfrom_$i" size=11
value="$form->{"validfrom_$i"}" title="$myconfig{dateformat}"></td>
<td><input name="validto_$i" size=11
value="$form->{"validto_$i"}" title="$myconfig{dateformat}"></td>
<td><input name="pass_$i" size=6 value="$form->{"pass_$i"}"></td>
<td><select name="taxmodule_id_$i" size=1>|;
@@ -2166,7 +2168,7 @@
#insert line
for ( $j = $ndx + 1 ; $j > $i ; $j-- ) {
$k = $j - 1;
- for (qw(taxrate taxdescription taxnumber validto)) {
+ for (qw(taxrate taxdescription taxnumber
validfrom validto)) {
$form->{"${_}_$j"} = $form->{"${_}_$k"};
}
}
@@ -2175,7 +2177,7 @@
for (qw(taxdescription taxnumber)) {
$form->{"${_}_$k"} = $form->{"${_}_$i"};
}
- for (qw(taxrate validto)) { $form->{"${_}_$k"} = "" }
+ for (qw(taxrate validfrom validto)) { $form->{"${_}_$k"} = "" }
push @t, $accno;
}
}
------------------------------------------------------------------------------
This SF.net email is sponsored by Sprint
What will you do first with EVO, the first 4G phone?
Visit sprint.com/first -- http://p.sf.net/sfu/sprint-com-first
_______________________________________________
Ledger-smb-devel mailing list
..hidden..
https://lists.sourceforge.net/lists/listinfo/ledger-smb-devel