[Advacs-discuss] Expanded General Ledger description
Oliver Elphick
olly@lfix.co.uk
Sat, 14 Aug 2004 06:14:10 +0100
On Sat, 2004-08-14 at 03:50, Helmut Wollmersdorfer wrote:
> Oliver Elphick schrieb:
> > I have expanded the description of the GL, and moved the diagram of the
> > core and GL schemas there out of the sql directory.
To avoid confusion, I am using the pattern <<tablename>> to refer to a
table when I talk about that table in an English sentence (but not in a
quote of an SQL query, of course). Similarly {fieldname} refers to a
column in an SQL table.
> Some comments on this:
>
> > department defines subdivisions of a business which maintain their own
> > sets of balanced books. These can be used for internal
> > reporting.
>
> Around 1994 I sold a ledger sofware for IBM AS/400. I experienced a
> requirement formulated by a potential customer, which sounded exotic
> first, but makes sense and maybe can be designed in a general way. They
> where a few people managing large projects (setup complete factories in
> overseas as whole-seller), moving very large amounts of money. They
> wanted to have a complete report (balance sheet, p&l) for a project at
> any time. Maybe this department=subdivision of business can solve this
> need. But a departement can work on different projects. A departement in
> my understanding is defined as one or a collection of cost centers,
> and a project is a cost object. My idea is, to solve such a requirement
> in the cost accounting, which needs the possibility to relate each
> transaction to any cost account. Usally only profit & loss accounts are
> accounted to cost accounts.
>
> Example:
>
> GL DpmntA DptmntB Project-1 Project-2
> assets 100 70 30 60 40
> liabilities -90 -50 -40 -40 -50
> profit/loss -10 +5 -15 -20 +10
>
> income -200 -150 -50 -100 -100
> costs 190 145 35 80 110
> profit/loss 10 -5 15 20 -10
We could provide a sub-department:
CREATE TABLE core.department (
id dept_code PRIMARY KEY,
name TEXT NOT NULL
CONSTRAINT "valid department name"
CHECK (name != ''),
company company_code NOT NULL
CONSTRAINT "valid company"
REFERENCES core.company (id)
ON UPDATE CASCADE
ON DELETE RESTRICT,
parent_dept dept_code CONSTRAINT "valid parent"
REFERENCES core.department (id)
ON UPDATE CASCADE
ON DELETE RESTRICT
);
Then postings to the sub-department could be grouped in the parent
department. This pattern would be recursive, so the logic for
automating cross-department journal balancing would need to be looked at
rather carefully!
Ordinary project costing should be handled by a separate module.
> > period and define accounting periods for each company (since different
> > subperiod companies may use different accounting periods. Each
> > period is divided into multiple subperiods of equal length.
> > In addition, one or more dummy periods can be defined for
> > the last day of each period. These are used only for final
> > adjustments while accounts are being prepared and audited.
>
> If accounting periods should map the official financial periods, they
> can begin at any day of a year and can end at any day of the year.
Yes. This system copes with that. In British company law, a company
can only change its period end once every 5 (?) years, unless it becomes
part of a group and needs to change to accommodate to the rest of the
group.
There will be triggers on <<period>> and <<subperiod>> to ensure that
periods are created consecutively, are contiguous and are not
overlapping.
...
> Technically you can solve a change of declared fiscal year "by hand",
> i.e. create a new company instance and transfer the balances. But this
> would not be "nice".
Definitely not!
> > ledger [...]
> >
> > Attributes of ledger include the transaction line reference,
> > department, date, account code, control account code,
> > amount, currency, period and foreign currency details.
>
> According to AT.lawful "proper bookkeeping" EACH transaction recorded in
> a computerised ledger MUST reference the related voucher.
>
> Definition of voucher (DE:Beleg):
> A voucher is a document which contains details of one or some business
> transactions. E.g. invoices or bank statements are vouchers. Vouchers
> must be archived - sorted or indexed. A method must be provided, that
> each voucher is related to a transaction.
> For outgoing invoices the AT.VAT-law defines, that these invoices must
> be numbered continiously, for missing numbers you need a documented
> reason. Several invoice-number-cycles are possible, e.g.
> [departement|product-group|billing-system|outlet|<whatever>] can have
> their own cycle.
> Usally all kinds (or subsets) of vouchers have their own voucher-cycle
> (DE:Belegkreis) are represented by a unique tag in front of the number.
I define two reference fields (here SUBST = child of <<ledger>>):
CREATE TABLE SUBST (
...,
jref VARCHAR(12) NOT NULL,
...,
ref VARCHAR(20) NOT NULL
DEFAULT '',
...
);
> Example:
> Bank statements of bank account 1
> tag: BAR
> number: use the number printed by the bank
> result: BAR001
>
> Outgoing Invoices Repair
> tag: ARR
> additional tag: year YY
> number: beginning from 5000
> result: ARR-04-5001
>
> Outgoing Invoices Parts
> result: ARP-04-001
>
> Thus, the easiest (and usual) way to fullfill the need for a reference
> to the voucher, a field [voucher|document]-ID should be included in the
> transaction-record.
>
> This should appear
> - on transaction listing per account
> - transaction journal listing
> - user interface
I think that {jref} should become a SERIAL field (that is an
automatically incrementing integer, which may have sequence gaps) and
{ref} is the document reference. The legal requirement can be fulfilled
by {ref} which is not part of the key and therefore avoids the need for
complicated systems to reuse numbers which might be generated but then
not used (as by an aborted transaction).
In this scenario, {ref} must be NOT NULL UNIQUE: this is best done by
making it an attribute of <<transaction>>, rather than of <<ledger>>.
Since {jref} is the primary key of <<transaction>> and is part of the
key in <<transaction_line>> and <<ledger>>, {jref} is available by a
join of <<transaction>> with <<ledger>>. Its uniqueness can then be
enforced by a UNIQUE NOT NULL constraint in <<transaction>>.
This leaves the possibility of a third reference field (not yet defined)
which has a different meaning according to the context. For a purchase
invoice it would be the supplier's invoice number; for a sales invoice,
the customer's order reference. In some cases it would not be required.
> > ***QUESTION*** The following definition of batch/jref/line was originally
> > invented for a traditional entry system using a 80x24
> > character screen, where the user was expected to enter
> > similar items one by one until all were done; such a
> > group of items could be totalled before entry and the actual
> > total entered compared with the pre-computed total as a
> > check on accuracy. The batch/jref/line provides a unique
> > key for any posting line in the system.
>
> I know this method of comparing totals as DE: Abstimmsumme (EN:check
> sum). It is old fashioned, but most ledgers support it (traditionally?).
> For me it makes sense at cash accounts, where e.g. the balance on the
> bank statement must equal the balance in the ledger. For _me_ it is
> enough, that the user interface displays the actual balance of the
> account after entering of each line of one transaction batch (in case of
> a bank statement, this has usually several lines = business
> transactions). After entering the last line I compare the current
> balance with the balance on the bank statement. If equal, I commit the
> transaction. If unequal, I edit the details to be correct, and commit.
Yes, that is what I had in mind. But this procedure has to be part of
the application program. It isn't feasible to build it into the
database. And, of course, you have to be able to override it, since
your initial total may have been computed wrongly, or the batch may turn
out (while you are entering it) to include an item that should not have
been included, such as an invoice for a different period.
> On mass injection of transactions from an other systems (e.g. Billing)
> comparing sums makes sense (and is used) to prevent bug injection.
I assume you mean "to avoid input errors"; in English, "bug" only refers
to errors in computer programs.
> > Since, in this system, entries could come singly from a
> > web browser, or even by a suitably crafted SQL command,
> > I wonder whether it is worth keeping the structure.
> > The alternative is to eliminate the batch and have simply
> > a unique journal reference; if invoices or payments are
> > batched in the traditional way, this could be controlled in
> > the entry program, which presumably would not be web-based.
> >
> > Any comments?
> > ***END OF QUESTION***
>
> I know this kind of numbering from the old days of line-printing
> terminals. They had carbon copy endless paper. Each line had a number
> called DE: Tagebuchzeile (EN: diary line), and each terminal had a
> number. Entries in the central journal had a compound-ID of
> terminal+diary. Batch transactions from tape or oder sequential media
> got a pseudo terminal-number.
>
> First, a unique identifier of transactions and/or lines is necessary - I
> think. Second, for purposes of auditing etc. it should be recorded
> which user entered which transaction from which location (e.g. IP or
> terminal).
The unique identifier is {jref}, the primary key of <<transaction>>.
The unique identifier of a line is {jref} + {line}.
> > batch Every session in an input program produces a batch of
> > entries. The batch provides a permanent record of which
> > items were entered in any particular session. Batch
> > attributes include time, date and the id of the user who
> > entered the batch.
>
> Let's define "batch":
>
> 1) batch of lines within one transaction
>
> E.g. a single voucher can cause splitting a single business transaction
> into several lines. This is called DE:Split-Buchung (EN: splitted ledger
> transaction).
No, I refer to that as a transaction, which must necessarily have at
least two lines, in order to complete the double entry, but could have
many.
> 2) batch templates
>
> A user can create a template of a complex splitted transaction and store
> this as a template (not commited to database). Later he can reload this
> template to the user interface, put in actual data, and commit the
> transaction. This is called DE:Stapelbuchung (EN: batch transaction).
> Technically this could be implemented by reloading any past transaction
> from the database with option "load it as template".
We could probably use that idea, but I would call it a "transaction
template" rather than a "batch".
> 3) batch from sequential media
I am defining "batch" as:
A collection of transactions (rows in <<transaction>>) entered
from a single source (by a single user) in a single session and
referring to a single subperiod (though it may include late
transactions for an earlier, closed subperiod which are instead
assigned to the current subperiod).
> > transaction A record of every transaction entered into the system. A
> > transaction is defined as a single posting of two or more
> > lines, the total of whose debits equals the total of credits.
> > A journal, an invoice, a payment into or out of a bank or
> > cash account are all examples of transactions.
>
> This definition means, that a single business transactions is always a
> single ledger transaction. In case of the example of a bank statement
> containing more than one payment, a compound (see above "splitted"
> transaction) transaction can be useful. And there are other cases, where
> compound transactions make sense.
Terminology:
In English, "bank statement" refers to one or more pieces of paper (or
the equivalent as an electronic transmission) listing movements on the
customer's bank account during some period. The way you are using "bank
statement" suggests you mean a single entry on such a statement.
For example, I might pay all my employees by bank transfer. A single
line will appear on the bank statement, but the bank will have followed
the instructions sent to them and will have paid the correct amount to
each employee. The transaction in the system will be:
Account A/c name Description Debit Credit
BANK HSBC bank Newport Salaries for July 2004 20315.33
E0001 Fred Bloggs Net salary July 2004 1752.66
E0002 Joe Snooks Net salary July 2004 1672.50
... other employees ... 16890.17
Similarly, I may pay a number of cheques from different people into the
bank together. This will appear on the bank statement as a single
receipt but will be split in the system: there will be a single debit to
the bank account but each cheque will be separately credited to its
appropriate account.
> > gledger This table (or its children) contains all direct postings
> > to the General Ledger; it does not contain entries for
> > control accounts, but these are simulated on reports such
> > as the Trial Balance by totalling the lines in the subsidiary
> > ledger to which the control account refers.
>
> I try to understand what a control account is, and if it is different to
> a summary account.
> The concept of summary account is usally implemented as an attribut of
> an account, that means "sum up this account to that summary account".
> E.g. the customer-account AR4711 has a field summary_account=2300. A
> transaction on AR4711 adds the transaction amount to debit or credit and
> to the balance of the summary account. A display of the transactions of
> the summary account shows nothing, but it shows sum of debit and credit
> and balance like this:
>
> 2300 customers of kind x (summary account)
> debits: 100
> credits: 10
> balance: 90
Yes, that is what I mean by "control account".
> Maybe I do not understand, what a subledger is. I know it as a separated
> system with own journal or log. Periodically the summary of transactions
> is transferred to the general ledger.
By sub(sidiary )ledger I mean a collection of accounts of the same kind
that do not balance within themselves but are summarised by one or more
control accounts in the General Ledger. For example, the Sales Ledger,
the Purchase Ledger and the Cashbook are all examples of subsidiary
ledgers.
In a manual accounting system, entries in the subsidiary ledger would be
listed in a daybook, which would periodically be totalled and the totals
would be entered under the control account in the General Ledger; these
will be "virtual" postings, in the computerised system.
> > A transaction can consist of multiple lines. Requiring these all to be
> > entered in a single SQL transaction can be a problem, so a transaction [and
> > a batch] has a confirmed flag, which is true if the transaction is complete.
>
> Seems to be a more flexible concept.
>
> 1) user interface transaction
>
> The user confirms the transaction per button or command.
>
> 2) mass injection
>
> Operators will like to have a batch job as _one_ transaction. Otherwise
> a interface-driver is necessary, which can handle restart and recovery.
There can be multiple accounting transactions (as defined above) in one
SQL transaction.
> Problem can be, how you handle the unique "line-number" in the journal.
See above on the interrelationship of <<transaction>>,
<<transaction_line>> and <<ledger>>.
> > Unconfirmed transactions are ignored when producing reports.
>
> This can need renumbering the journal entries. Hmm, what will auditors say?
On further consideration, I really don't like this concept. It is a
carry over from my old system, but I think it is no longer useful. If
transactions are to be tentative entries, they should be stored
elsewhere and not put in the database until they are finalised.
> > [OR]
> > A transaction can consist of multiple lines. They must all be entered as
> > part of one SQL transaction and a trigger is run to check that the
> > transaction balances; if it does not, an error is raised and the transaction
> > is not entered.
In fact, I think this is the way to go for the database design. We can
keep the concept of a batch, but it will not be part of the primary key
of <<transaction>>; instead it will be a simple attribute.
> NO, balancing cannot be a trigger for the end of a transaction. Lines
> can balance by mistake or whatever.
I do not mean that balancing forces the end of a transaction, but that
when a transaction is ended (by inserting the corresponding row in
<<transaction>>) a trigger is run to verify that the transaction
balances.
The sequence will be (illustrated by entering sales invoice number
S0037556):
BEGIN;
SELECT nextval('jref_seq');
INSERT INTO transaction_line (jref, line) VALUES (currval('jref_seq'), 1);
-- a trigger generates the tableid column value when the line is entered
-- in <<ledger>>
INSERT INTO sledger (transaction, line, account, ctrl_ac, amount, ...)
VALUES (currval('jref_seq'), 1, 'AR4255', 'CTRLSLS1', 144.32, ...);
INSERT INTO transaction_line (jref, line) VALUES (currval('jref_seq'), 2);
INSERT INTO gledger (transaction, line, account, amount, ...)
VALUES (currval('jref_seq'), 2, 'SALES', 120.00, ...);
...etc...
INSERT INTO transaction (jref, ref, ...)
VALUES (currval('jref_seq'), 'S0037556', ...);
-- trigger fires to check the transaction before this row is inserted
END;
The foreign key references to <<transaction>> have to be deferred, of
course. When the line is inserted into <<transaction>>, a BEFORE
trigger is fired which checks that the lines of the transaction run
consecutively up from 1 and that their totals sum to 0; if that is not
the case, this trigger aborts the SQL transaction.
> Testers voice: everything possible will happen.
...and some things you thought weren't possible!
> Balancing is _only_ one of a set of conditions, which a valid
> transaction should fullfill.
They too can be checked by the <<transaction>> trigger. However,
everything else I can think of can be handled by normalisation of the
tables (beyond what I have designed so far).
> > Every transaction line is flagged with the period to which it belongs, so
> > it is possible to continue posting in a later period while an earlier
> > period is still not finalised.
In fact, this belongs in <<transaction>> rather than
<<transaction_line>> or <<ledger>>.
> BTW: Application logic should NEVER depend on the real date or the real
> time. Real date should only be a default for a choosable logical date.
> For logging (audit logs) the real time stamp can be used, but NEVER use
> this time stamp in application logic, except for sorting, querying or
> searching the log itself.
> Coding against this principle will result in a nice playground for bug
> hunting.
You are right. I include validation constraints, according to context.
The function core.valid_date(date), defined in ledger.sql, checks that a
date falls between two user-defined limits: the earliest allowed date
and CURRENT_DATE + max_future_days. (So if max_future_days is 60, you
cannot post transactions more than 60 days before their actual date.)
> > The ledger tables in the various modules are subdivided into tables for
> > successive periods. If a ledger's postings for a period are to be archived
> > off, all the accounts which they reference are copied to a suitably named
> > child of the ledger's account file, and the foreign key constraints of the
> > table to be archived are changed to refer to the archived account file. In
> > this way, the tables for a particular period can be archived off in a form
> > that maintain the consistency of the data.
>
> According to AT.business law and similar rules of AT.tax regulations
> archiving period is 7 years (and longer in some cases). All transaction
> recordings and related ducuments must be available during the archiving
> period by appropriate methods.
>
> In case of computerised accounting AT.tax regulations demand, that this
> data must be provided on storage media, on demand of tax auditors.
>
> The related guideline (guidelines are not mandatory, only a
> interpretation of a law) gives examples:
>
> - exported data in
> - fixed field format
> OR
> - CSV (character separated)
> OR
> - print files only containing printable characters
> plus end-of-line and end of page
I envisage transferring them to separate tables and then using pg_dump
to archive these to flat files. We could make copies in other formats
at the same time.
If we need to recover them, we feed the pg_dump output into psql in
order to get back a self-consistent set of tables on which we can run
queries.
-----------------
After writing all that, I have revised my opinions on some parts of the
database design. I will be putting further changes up on CVS later.
--
Oliver Elphick olly@lfix.co.uk
Isle of Wight http://www.lfix.co.uk/oliver
GPG: 1024D/A54310EA 92C8 39E7 280E 3631 3F0E 1EC0 5664 7A2F A543 10EA
========================================
"Watch ye therefore, and pray always, that ye may be
accounted worthy to escape all these things that shall
come to pass, and to stand before the Son of man."
Luke 21:36