Hi all,

I have a custom function in a formula that returns the previous 3 months' Taxable Earnings. When the formula is processed in a payroll run, the function works as expected and returns the correct value.

However when the formula is processed through Retropay by Element, a different value is calculated. To complicate matters further, when I process retropay using different start/end dates the value returned by the function varies.

The sql code I am using is below:

SELECT sum(TO_NUMBER(prrv.result_value))
FROM apps.pay_run_results prr,
apps.pay_run_result_values prrv,
apps.pay_assignment_actions paa,
apps.pay_payroll_actions ppa,
apps.pay_input_values_f piv,
apps.pay_element_types_f pet,
apps.pay_element_classifications pec,
apps.per_all_assignments_f asg,
apps.per_all_people_f per,
apps.pay_payrolls_f pay
WHERE prrv.run_result_id = prr.run_result_id
AND asg.assignment_id = paa.assignment_id
AND asg.business_group_id = c_business_group_id
AND pay.payroll_name = c_payroll_name
AND per.person_id = asg.person_id
AND asg.payroll_id = pay.payroll_id
AND prr.assignment_action_id = paa.assignment_action_id
AND paa.payroll_action_id = ppa.payroll_action_id
AND piv.input_value_id = prrv.input_value_id
AND piv.NAME = 'Pay Value'
AND ppa.action_type IN ('Q','R','V')
AND ppa.action_status = 'C'
AND paa.action_status = 'C'
AND nvl(paa.source_action_id,0) <> 0
AND pet.element_type_id = piv.element_type_id
AND pec.classification_id = pet.classification_id
AND TRUNC (ppa.date_earned) >= last_day(add_months(c_end_date,-3))+1
AND TRUNC (ppa.date_earned) <= c_end_date
and asg.assignment_id = c_assignment_id
and piv.input_value_id IN (
select distinct pbf.input_value_id
from pay_balance_feeds_f pbf
, pay_balance_types pbt
where pbf.balance_type_id = pbt.balance_type_id
and pbt.balance_name IN ('Taxable Pay','NIable Pay')
and c_end_date between pbf.effective_start_date and pbf.effective_end_date
)
AND ppa.date_earned BETWEEN asg.effective_start_date
AND asg.effective_end_date
AND ppa.date_earned BETWEEN per.effective_start_date
AND per.effective_end_date
AND ppa.date_earned BETWEEN pay.effective_start_date
AND pay.effective_end_date
AND ppa.date_earned BETWEEN pet.effective_start_date
AND pet.effective_end_date
AND ppa.date_earned BETWEEN piv.effective_start_date
AND piv.effective_end_date;


Has anyone come across this before, or can you see anything in the SQL code that may cause problems with Retropay?

Thanks in advance,
Jon

I think you'll find it's down to your dates. Retropay is probably using the payroll_action.date_earned from the payroll runs it's recalculating. Hence each call to the function is actually based around a retrospective date.

The formula in question is linked to a Holiday element. I have created a holiday absence that starts and ends on 6th May 09, and the element effective start and end date is 6th May 09. The May payroll has already been run, so Retropay by Element picks this entry up and creates the Retro entry in the June period.

If I run Retropay with a start date of 1st May 09 and end date of 1st June 09, the calculation from the function is correct. However, if I run Retropay with a start date of 1st April 09 and end date of 1st June 09 the value returned is incorrect.

I don't understand how that can happen, because the element being processed by retropay is only effective in the May period, so running Retropay for April shouldn't affect the calculation?

Without digging in to your payroll process, I can't answer your question as to why. However, since your query is based around the ppa.date_earned (pay_payroll_actions) as the key date, then when RetroPay is processing the April period, the query will be executing for that period when it's run.

retropay process re-runs the each month's payroll and does a simple subtraction of the each of the run result values between previous run and retropay run.
It then creates single element entry for each respective month in the current pay period.

if you run from 1st apr to 1 jun there should be 3 retro entries for that element.

How many retro entries are generated?

There is only 1 retro entry produced. This is what I would expect because the element is only present in the May period.

So you have attached the FF to holiday element?

did u rollback the previous retropay run before you ran the 2nd one? with new dates

Yes the FF is attached to the holiday element and I rolled back the previous retropay run before I ran the second one.

The issue is definitely with the custom function because I have enabled GMFZT logging on Retropay and I can see what parameters are being passed to the function and the value that is being returned - the odd thing is when I pass the same parameters in SQL PLUS, the function returns the correct value (similarly when I enter a holiday absence in the current month and run a payroll the correct value is calculated).

Thanks