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!

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

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

Thanks mate,

I'll give it a try.

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

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

Glad I could assist :-)

Happy to help - on this occasion, by the simple expedient of keeping my nose out