[Advacs-discuss] Expanded General Ledger description
Helmut Wollmersdorfer
helmut@wollmersdorfer.at
Sat, 14 Aug 2004 04:50:16 +0200
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.
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
> 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.
Example:
2001-05-13 Start of company,
declared fiscal year = calendar year;
opening balance (AT.mandatory on startup)
2001-12-31 end of period-1
2002-12-31 end of period-2
2003-09-25 end of period-3
reason: change of declared fiscal year
2003-09-26 begin of period-4
2004-01-31 end of period-4
reason: liquidation
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".
> 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.
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
> ***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.
On mass injection of transactions from an other systems (e.g. Billing)
comparing sums makes sense (and is used) to prevent bug injection.
> 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).
> 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).
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".
3) batch from sequential media
> 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.
> 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
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.
> 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.
Problem can be, how you handle the unique "line-number" in the journal.
> Unconfirmed transactions are ignored when producing reports.
This can need renumbering the journal entries. Hmm, what will auditors say?
> [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.
NO, balancing cannot be a trigger for the end of a transaction. Lines
can balance by mistake or whatever.
Testers voice: everything possible will happen.
Balancing is _only_ one of a set of conditions, which a valid
transaction should fullfill.
> 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.
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.
> 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
Helmut Wollmersdorfer