Re: looking for technology

On 25 Jul 2005 12:52:37 -0700, hegyvari_at_ardents.hu (Hegyvari Krisztian) wrote:

>Hello,
>
>I am looking for a database technology to use, I will try to describe
>the situation.
>
>The system is a loan originating system, using Magic 9.3sp2 (RAD
>tool), direct sql commands and lots of packages. The money flowing
>into the system is tied to the monthly installments, generating
>accounting information. The installments can be changed even after
>some money received, which means to "untie" the money, delete the old
>installment, write the new one and put the money back. This also
>generates accounting information of course. After an event like this
>happens, the financial manager has no way of telling exactly why the
>old accounting records are there. Therefore they want us to put a
>journal on each table that decides the sum and the ledger, so they can
>go back and check the journals. Some lists and online screens need to
>be modified in a way that they need information from the journals
>rather than the table itself. This information must be kept years
>back. The whole database is currently 75gigs, and the number of the
>tables we need this journal on is about 15-20. Ora 8.1.7.2, but a
>newer one can be arranged, if it shows significant advantage in
>showing our problem.
>
>Any ideas about simple ways to achieve this? Any way this can be done
>"automagically" with some cool Oracle function (I would like to say I
>need an old version of the table, not the current one, for example).
>
>Our current approach is to put a journal behind each table, fill them
>with triggers and have a job which periodically puts a stamp on each
>journal record and accounting record not yet stamped.
>
>Of course we have to maintain consistency when we go back in time,
>assigning money to an installment means writing to 3-4 tables, more
>than one updates each table, etc...
>
>Any ideas would be appreciated.
>
>Please reply to hegyvari at ardents.hu as well, but I am going to
>check back in the next few days.
>
>Thanks in advance
>
>Krisztian Hegyvari

What you describe is a flashback query. Available in 9i and 10g.

--
Sybrand Bakker, Senior Oracle DBA