Who's Online Now
0 registered members (), 2 guests, and 4 spiders.
Key: Admin, Global Mod, Mod
Newest Members
Lovey Fernandes, DawnT, Ravi Pasapula, kumar1900, B_DOYLE
1298 Registered Users
Recent Posts
Top Posters(30 Days)
Popular Topics(Views)
500,237 PAYE RTI
61,873 HR_PF.K RUP4
Previous Thread
Next Thread
Print Thread
Rate This Thread
#12245 - 03/12/19 09:55 AM Retrieving Balances using SQL  
Joined: Mar 2005
Posts: 1,311
delboy Offline
The Grand Daddy
delboy  Offline
The Grand Daddy
*****

Joined: Mar 2005
Posts: 1,311
Somewhere in South of England
I am trying to create a list of taxable pay ytd values using sql. I can see that the result_value is stored in pay_gb_balances_v but I am struggling to get the current ytd value for each assignment.

Any pointers gratefully received.

Yours,

A humble payroll funky!

#12246 - 03/12/19 10:05 AM Re: Retrieving Balances using SQL [Re: delboy]  
Joined: Mar 2005
Posts: 439
paulgos Offline
battle-hardened campaigner
paulgos  Offline
battle-hardened campaigner
****

Joined: Mar 2005
Posts: 439
Gosport
Hi Del,

Been over a year now since I touch e-Bus and I am sure CT or the great techie funky's out there will assist but are you linking this to the balance dimension table??

I used to find it easier to pull all the run result values and sum them during a predefined date parameter i.e. 01-APR-YYYY to the said period end I was looking to pull back to.

Good luck and hope all is well my good man


Gozza

#12247 - 03/12/19 10:10 AM Re: Retrieving Balances using SQL [Re: delboy]  
Joined: Mar 2005
Posts: 439
paulgos Offline
battle-hardened campaigner
paulgos  Offline
battle-hardened campaigner
****

Joined: Mar 2005
Posts: 439
Gosport
Del,

Found this in an email I sent myself, try it and see if it helps;

SELECT paaf.assignment_number,
paaf.assignment_id,
pbt.balance_name,
SUM(prb.balance_value),
pbd.database_item_suffix,
papf.payroll_name
FROM apps.per_all_assignments_f paaf,
pay_run_balances prb,
pay_defined_balances pdb,
pay_balance_types pbt,
pay_balance_dimensions pbd,
apps.pay_all_payrolls_f papf,
pay_payroll_actions ppa,
pay_assignment_actions paa
WHERE paaf.assignment_id = paa.assignment_id
AND paa.payroll_action_id = ppa.payroll_action_id
AND paa.assignment_action_id = prb.assignment_action_id
AND prb.defined_balance_id = pdb.defined_balance_id
AND pdb.balance_type_id = pbt.balance_type_id
AND paaf.payroll_id = papf.payroll_id
--AND paaf.assignment_id IN (1916644,820506,1608632)
AND pbt.balance_type_id = 174
AND pbd.balance_dimension_id = 71
AND (ppa.action_type = 'B' OR (ppa.action_type = 'R' AND prb.balance_value < 0 ))
AND prb.effective_date BETWEEN TO_DATE('01-apr-2012', 'DD-MON-YYYY') AND TO_DATE('31-MAY-2012', 'DD-MON-YYYY')
AND TRUNC(SYSDATE) BETWEEN paaf.effective_start_date AND paaf.effective_end_date
AND TRUNC(SYSDATE) BETWEEN papf.effective_start_date AND papf.effective_end_date
GROUP BY paaf.assignment_number,
paaf.assignment_id,
pbt.balance_name,
pbd.database_item_suffix,
papf.payroll_name
ORDER BY assignment_number

#12248 - 03/12/19 10:21 AM Re: Retrieving Balances using SQL [Re: delboy]  
Joined: Mar 2005
Posts: 1,311
delboy Offline
The Grand Daddy
delboy  Offline
The Grand Daddy
*****

Joined: Mar 2005
Posts: 1,311
Somewhere in South of England
Thanks mate,

I'll give it a try.

#12250 - 04/12/19 06:47 AM Re: Retrieving Balances using SQL [Re: paulgos]  
Joined: Mar 2005
Posts: 1,311
delboy Offline
The Grand Daddy
delboy  Offline
The Grand Daddy
*****

Joined: Mar 2005
Posts: 1,311
Somewhere in South of England

Originally Posted by paulgos
Del,

Found this in an email I sent myself, try it and see if it helps;



....And in the words of Thomas Skinner (The Apprentice).....BOSH!!!

With a bit of tweeking, works a treat. Thanks my man.

#12251 - 04/12/19 08:51 AM Re: Retrieving Balances using SQL [Re: delboy]  
Joined: Mar 2005
Posts: 439
paulgos Offline
battle-hardened campaigner
paulgos  Offline
battle-hardened campaigner
****

Joined: Mar 2005
Posts: 439
Gosport
Glad I could assist :-)

#12253 - 04/12/19 11:01 AM Re: Retrieving Balances using SQL [Re: delboy]  
Joined: Mar 2005
Posts: 2,119
CT Offline
CT  Offline

****

Joined: Mar 2005
Posts: 2,119
Happy to help smile - on this occasion, by the simple expedient of keeping my nose out laugh


CT

Moderated by  CT, delboy 

Forum Statistics
Forums60
Topics2,229
Posts12,038
Members1,299
Most Online283
Dec 25th, 2019
Today's Birthdays
No Birthdays
Recent vacancies
Top Posters(All Time)
CT 2,119
delboy 1,311
bcooper 1,293
paulgos 439
SBi 427
pat 254
Gus 252
vkumar 223
October
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 31
Powered by UBB.threads™ PHP Forum Software 7.6.0
Page Time: 0.028s Queries: 15 (0.009s) Memory: 2.7833 MB (Peak: 2.9591 MB) Zlib disabled. Server Time: 2020-10-30 05:14:01 UTC