I am thinking that when Retropay by Element is run, it does something to the payroll tables which affects the run result data (for the periods which Retropay is running) and thus invalidates my SQL query (above). It may just be that a small tweak is required to the code.

did you try passing diff dates for c_end_date?? using PLSQL

Yes I've tried that and the function works consistently through pl/sql. I've even tried hard-coding the dates in the formula but still have the problem with Retropay.

try deleting/creating new holidy entry to check whether any changes to retro..

Already tried that! I have also tried different employees, in case it was a data issue against a particular employee but the issue is across all employees. I am going to raise an SR with Oracle, but I don't anticipate much help as it is custom code!

any luck? did you get it working?

Not yet - got an OWC with Oracle today.

Just to let you know this seems to be a bug - I managed to replicate the issue with the seeded function pay_balance_pkg.get_value.

Oracle Development are currently investigating...

ohhh surprising..

Just to let you know, this issue was resolved by creating the function as an autonomous transaction...