Ron is a business analist in a medium size company. The company derives its right to existence to the excellent execution of projects for clients, partly by building management on recalculation basis and partly by privately renovating risk buildings in the cities. The economic crisis has left deep traces in the credit lines and the auditor is looking with a critical eye at the support of the achieved turnover and the balance sheet. It is still not too bad considering they do not have to work according to IFRS, but it is still nervous and very busy every year. This year will be even more exciting, because the moneylender is nervous and wants to make sure that his money will return and he wants to make sure that there is little risk since the managers have a demonstrable control on the company operations.
The team leader John of the accounting team checks in with Ron on the second control day. He is a bit worried about the big position that was acquired two years ago with the purpose to redevelop the entire Kloosterdreef area. The problem is, last year it was good for a nice amount on the balance sheet and now only a small one. John wonders whether the controller did not unknowingly estimate an overly positive situation last year regarding the revenue.
John would like to know from Ron how the insights have developed in time.
Pff, Ron is not too happy. That is typically one of those questions that will take a lot of time to answer properly. And it will lead to an endorsement, because the processes are well under control. That will be working late again. Time to take a moment and see if the question can be answered in a smarter way by gathering all the underlays under the impairments .
Ron remembers that it is possible to travel back in time in Invantive Estate by entering the desired moment of insight in the web or Outlook user interface. For example a few weeks back:
All reports will emerge as the situation of the chosen moment was. But well, the auditor is mainly interested in the mutations and the months in which they occur. So that will still mean having to compare the reports in Excel format with each other and sifting through it all. It still beats having to go through the thick binders, but still… There is no doubt that there will be a question about it.
Is there not a smarter way? In the Invantive Estate course it was explained that it is very easy to travel back in time. Perhaps Ron with his limited experience with SQL can quickly find a result that way. With the manual open Ron discovers that he first needs to log on to the company layer in the SQL editor:
begin -- -- Log on to business layer of Invantive Estate. -- bubs_session.set_session_info ( 'Mijn querytool' , 'Analyseren' , 'ron' , 'Analyse opbrengsten grondpositie voor John' , 'Mijn netwerk' , 'Mijn PC' , 'Handmatig' , 'Mijn sessie om ' || to_char(sysdate, 'YYYYMMDDHH24MISS') , 'GeheimenDeelJeMetSandeman' ); end;
What is the actual budgeted revenue? All data are already premade in a SQL view, so that makes it easier. You do not have to ransack the entire data model:
select sum(obt_gebudgetteerde_opbr_unit)
gebudgetteerde_opbrengst
from bubs_opbrengsten_v
where 1=1
and pjt_code='Kloosterdreef'
And thus Ron thinks that the situation as it is now is 1,2 million Euro. Now let us check the quarterly insights on the first of the month of how it was then. First the situation of how it was according to the reference datum of the 1st of January should be reproduced. According to the project version, those numbers have been made definitive on the 15th of January on 10:23. To travel back to that moment is quite simple:
begin
bubs_session.set_point_in_time(to_date('15-01-2010 10:23:00', 'DD-MM-YYYY HH24:MI:SS'));
end;
To get the budgeted revenue of that moment all that is needed is a small adjustment, which is the change of ‘bubs_’ in the name of the SQL view:
select sum(obt_gebudgetteerde_opbr_unit)
gebudgetteerde_opbrengst
from bubspopbrengsten_v
where 1=1
and pjt_code='Kloosterdreef'
Hmm, John does have a point, at the start of last year it was well above the two million, Ron thinks. Now how do I get a little table with a number for every reference date? It should also be possible to solve that with a query directly on the underlying history, but that is a little too difficult for me. I have run the above query a dozen times. For every reporting period I find the moment of insight and then I print the budgeted revenue. After five minutes of work it is done:
declare
g_pjt_code bubs_project_versies_v.pjt_code%type := 'Kloosterdreef';
l_gebudgetteerde_opbrengst bubs_opbrengsten_v.obt_gebudgetteerde_opbr_unit%type;
begin
for r_pve
in
( select pve.pve_datum_rapportage
, pve.pjt_code
, pve.pve_code
from bubs_project_versies_v pve
where 1=1
and pve.pjt_code = g_pjt_code
and pve.pce_code = 'Boekhouding'
and pve.pve_code in ( '201001', '201004', '201007', '201010', '201101')
order
by pve.pve_code
)
loop
select sum(obt_gebudgetteerde_opbr_unit)
gebudgetteerde_opbrengst
into l_gebudgetteerde_opbrengst
from bubspopbrengsten_v
where 1=1
and pjt_code = r_pve.pjt_code
;
dbms_output.put_line
( 'Periode ' || r_pve.pve_code
|| ' (inzichtsmoment '
|| to_char(r_pve.pve_datum_rapportage, 'DD-MM-YYYY HH24:MI:SS')
|| '): '
|| to_char(l_gebudgetteerde_opbrengst)
)
;
end loop;
end;
A few seconds later the numbers are on the screen:
Period 201001 (moment of insight 03/03/2011 19:37:07): 2.013.488
Period 201001 (moment of insight 03/03/2011 19:37:07): 2.013.488
Period 201001 (moment of insight 03/03/2011 19:37:07): 1.740.354
Period 201001 (moment of insight 03/03/2011 19:37:07): 1.233.000
Period 201001 (moment of insight 03/03/2011 19:37:07): 1.233.000
That is not too bad. Ron sends the numbers to John and saves the query to his favorites. There is already a lot of information coming out of Invantive Estate standard, but this analysis question he will be able to answer quickly in the future as well. How did the control end? It ended well! John adds another extra paragraph in the report in which he says that John often travelled back in time with Ron and that he was rarely surprised of the speed in which analyses where provided.