May 29, 2008

calling balances via plsql in fast formula

We all know you can call plsql functions within fast formula. The way to go when it becomes really complicated.

When you need the last 9 months of a particular balance (a running total), you can fix that by calling the seeded pay_balance_pkg.get_value plsql function.

You can even use it to retrieve balance values from the actual period. But in that case you sometimes run into the issue that the data is still in memory of the payroll engine, and not yet flushed into the Oracle tables. So the balance call returns zero, while you know very well it is not.

A trick to force the engine to flush the data (and to guarantee that he balance function call comes with the right values) is to call a YTD balance just before your function call. Since the YTD spans multiple periods, the engine realizes he needs database access, and flushes his memory structures.