Saturday, May 27, 2006
Effective Date and Notice Date
If we consider an accounts package, it is very common for details of transactions, entries, to be entered after they have taken place. There are two dates that need to be recorded. First is the date and time the transaction is entered. Second there is the effective date, or the date on which the transaction happened.
Given these dates there are some quite complex queries that can be written that answer some difficult questions.
For example, we can ask the question, what were the accounts at the end of January?
We can also ask what should the accounts have been at the end of January?
Very subtle difference between 'were' and 'should'.
There is another feature of accounts. Accountants like to close the accounts for a period. That means they don't want to accept any new entries where the effective date is earlier than the period end.
So lets say we want to close the books for the end of the year. We allow entries to be made up to the end of January, but after that we will close the books.
The first question will be what are the accounts at the end of the year.
All very simple.
It is also important to know what adjustments were made after year end.
All simple.
There are two approaches to locking the accounts down. One is to not allow any entries made after the end of January with an effective date in the last year. That is no back valued transactions that affect the previous end of year accounts. The alternative is to allow them, and change the queries.
The end of year accounts then become.
Here then 31-JAN-2006 is the closing date of the books.
You can continue making adjustments to the previous year's books. However, the entry screen should warn that this is happening.
We then add one more query. Next closing of the books, we need to know the adjustments to the previous year's accounts.
and the actual accounts themselves uses the same query
Just with the addition of two dates, we can get queries that distinguish between two important questions, what should have been the state and what was the state.
Given these dates there are some quite complex queries that can be written that answer some difficult questions.
For example, we can ask the question, what were the accounts at the end of January?
Select * from Entries where notice_date <= #31-JAN-2006#We can also ask what should the accounts have been at the end of January?
Select * from Entries where effective_date <= #31-JAN-2006#Very subtle difference between 'were' and 'should'.
There is another feature of accounts. Accountants like to close the accounts for a period. That means they don't want to accept any new entries where the effective date is earlier than the period end.
So lets say we want to close the books for the end of the year. We allow entries to be made up to the end of January, but after that we will close the books.
The first question will be what are the accounts at the end of the year.
Select * from Entries where effective_date <= #31-DEC-2006#All very simple.
It is also important to know what adjustments were made after year end.
Select * from Entries where effective_date <= #31-DEC-2006# and notice_date > #31-DEC-2006#All simple.
There are two approaches to locking the accounts down. One is to not allow any entries made after the end of January with an effective date in the last year. That is no back valued transactions that affect the previous end of year accounts. The alternative is to allow them, and change the queries.
The end of year accounts then become.
Select * from Entries where effective_date <= #31-DEC-2006# and notice_date <= #31-JAN-2006#Here then 31-JAN-2006 is the closing date of the books.
You can continue making adjustments to the previous year's books. However, the entry screen should warn that this is happening.
We then add one more query. Next closing of the books, we need to know the adjustments to the previous year's accounts.
Select * from Entries where effective_date <= #31-DEC-2007# and notice_date > #31-JAN-2006#and the actual accounts themselves uses the same query
Select * from Entries where effective_date <= #31-DEC-2007#Just with the addition of two dates, we can get queries that distinguish between two important questions, what should have been the state and what was the state.
Subscribe to Posts [Atom]