Calculate BCI/CPI using PL/SQL and Oracle table

Recently I was working in a project to calculate BCI (Building Cost Index) for the assets in the asset management system. After messing around with different systems and methodology I have come across a simple solution that calculate the CPI (Consumer Price Index) for a range of years. Suppose if we built a toilet in 2008 and cost us $78000 what will be the today’s cost after apply BCI for the last few years. The BCI is based on each year so it might not be the same for each year.

I have created a table to store the BCI values as below –

Now write a very simple PL/SQL function to calculate the cost by looping through the selected year array. First we have to find the year difference by deducting start year from end year. Once we get year difference then loop through the array of year and get the BCI index as it loops through. Here is the PL/SQL code for the calculation.

DECLARE
   start_year   NUMBER := 2000;
   end_year     NUMBER := 2011;
   year_diff    NUMBER;
   amount       NUMBER (30, 2) := 1700;
   cpi_rate     NUMBER (4, 2);
BEGIN
   year_diff := end_year - start_year;

   FOR i IN 1 .. year_diff
   LOOP
      start_year := start_year + 1;

      -- select cpi rate for the current index
      SELECT annual_increase
        INTO cpi_rate
        FROM rtt_bci_index
       WHERE b_year = start_year;

      amount := amount + (amount * cpi_rate / 100);

      DBMS_OUTPUT.put_line (start_year || ' - ' || amount);
   END LOOP;

   --DBMS_OUTPUT.put_line (amount);
END;

The above code is very straight forward, I have set the parameter for start year, end year  and cost for the start year. Please note in the above code I have set the array position to start from 1 even though by default it starts from 0. The reason for that when we say if something built in 2005 with cost and find out the cost between 2005 to 2010. Now 2005 is excluded because it is base year and we don’t want to apply the BCI for that year. But if you want to include the start year just change the code from –

FOR i IN 1 .. year_diff

to

FOR i IN 0 .. year_diff

Here is the output I get when I run the above script –

Now you can see the price increase and decrease based on the BCI rate applied form the table.

This script can be integrate in a Oracle package or create a function to call this from APEX page or from any other application e.g. ASP.NET page etc.

I will try to post an example of integrating this function in APEX application.