Who's Online Now
0 registered members (), 8 guests, and 7 spiders.
Key: Admin, Global Mod, Mod
Newest Members
Krati, Taposh, Jafar Habeebulla, CloudShine Softw, Aaa
1305 Registered Users
Recent Posts
Top Posters(30 Days)
CT 1
Popular Topics(Views)
508,331 PAYE RTI
63,666 HR_PF.K RUP4
Previous Thread
Next Thread
Print Thread
Rate This Thread
Page 1 of 3 1 2 3
#4526 - 04/06/09 02:09 PM Custom Function issue in Fast Formula  
Joined: Nov 2005
Posts: 38
JayTee Offline
enthusiast
JayTee  Offline
enthusiast

Joined: Nov 2005
Posts: 38
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


#4527 - 04/06/09 03:31 PM Re: Custom Function issue in Fast Formula [Re: JayTee]  
Joined: Mar 2007
Posts: 145
PeterP Offline
nothin' better to do
PeterP  Offline
nothin' better to do
*****

Joined: Mar 2007
Posts: 145
Manchester, UK
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.

#4528 - 04/06/09 03:45 PM Re: Custom Function issue in Fast Formula [Re: PeterP]  
Joined: Nov 2005
Posts: 38
JayTee Offline
enthusiast
JayTee  Offline
enthusiast

Joined: Nov 2005
Posts: 38
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?

#4529 - 04/06/09 09:27 PM Re: Custom Function issue in Fast Formula [Re: JayTee]  
Joined: Mar 2007
Posts: 145
PeterP Offline
nothin' better to do
PeterP  Offline
nothin' better to do
*****

Joined: Mar 2007
Posts: 145
Manchester, UK
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.

#4530 - 05/06/09 06:26 AM Re: Custom Function issue in Fast Formula [Re: PeterP]  
Joined: May 2008
Posts: 427
SBi Offline
battle-hardened campaigner
SBi  Offline
battle-hardened campaigner

Joined: May 2008
Posts: 427
UK
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.

#4531 - 05/06/09 06:27 AM Re: Custom Function issue in Fast Formula [Re: SBi]  
Joined: May 2008
Posts: 427
SBi Offline
battle-hardened campaigner
SBi  Offline
battle-hardened campaigner

Joined: May 2008
Posts: 427
UK
How many retro entries are generated?

#4532 - 05/06/09 06:35 AM Re: Custom Function issue in Fast Formula [Re: SBi]  
Joined: Nov 2005
Posts: 38
JayTee Offline
enthusiast
JayTee  Offline
enthusiast

Joined: Nov 2005
Posts: 38
There is only 1 retro entry produced. This is what I would expect because the element is only present in the May period.

#4533 - 05/06/09 07:19 AM Re: Custom Function issue in Fast Formula [Re: JayTee]  
Joined: May 2008
Posts: 427
SBi Offline
battle-hardened campaigner
SBi  Offline
battle-hardened campaigner

Joined: May 2008
Posts: 427
UK
So you have attached the FF to holiday element?

#4534 - 05/06/09 07:25 AM Re: Custom Function issue in Fast Formula [Re: SBi]  
Joined: May 2008
Posts: 427
SBi Offline
battle-hardened campaigner
SBi  Offline
battle-hardened campaigner

Joined: May 2008
Posts: 427
UK
did u rollback the previous retropay run before you ran the 2nd one? with new dates

Last edited by Shiva Bildikar; 05/06/09 07:26 AM.
#4535 - 05/06/09 07:55 AM Re: Custom Function issue in Fast Formula [Re: SBi]  
Joined: Nov 2005
Posts: 38
JayTee Offline
enthusiast
JayTee  Offline
enthusiast

Joined: Nov 2005
Posts: 38
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

Page 1 of 3 1 2 3

Moderated by  CT, delboy 

Forum Statistics
Forums60
Topics2,230
Posts12,039
Members1,306
Most Online283
Dec 25th, 2019
Today's Birthdays
No Birthdays
Recent vacancies
Top Posters(All Time)
CT 2,120
delboy 1,311
bcooper 1,293
paulgos 439
SBi 427
pat 254
Gus 252
vkumar 223
April
S M T W T F S
1 2 3
4 5 6 7 8 9 10
11 12 13 14 15 16 17
18 19 20 21 22 23 24
25 26 27 28 29 30
Powered by UBB.threads™ PHP Forum Software 7.6.0
Page Time: 0.032s Queries: 16 (0.007s) Memory: 2.8175 MB (Peak: 3.0177 MB) Zlib disabled. Server Time: 2021-04-11 13:22:15 UTC