I have written some code ( ) which I'm hoping will bring in the payroll details of the employee who is logged in... It all works when I hard code in the employee_number, but my problem at the moment is I'm not entirely sure who to link the person logged in to the rest of the code... I hope that makes sense!


select decode(ptp.period_type, 'Week',
decode(to_char(ptp.start_date,'Dy'),
'Mon', 'Weekly - Starts Monday',
'Thu','Weekly - Starts Thursday',
'Wed','Weekly - Starts Wednesday',
'Sun','Weekly - Starts Sunday'
,'Invalid'),
'Weekly - Starts Monday') Period_Name
from per_all_people_f papf,
per_all_assignments_f paaf,
pay_all_payrolls_f ppf,
per_time_periods ptp,
fnd_user fu
where papf.person_id = paaf.person_id
and paaf.payroll_id = ppf.payroll_id
and fu.employee_id = papf.person_id
and ptp.payroll_id = paaf.payroll_id
and trunc(sysdate) between fu.start_date and nvl(fu.end_date,sysdate)
and trunc(sysdate) between ppf.effective_start_date and ppf.effective_end_date
and trunc(sysdate) between papf.effective_start_date and papf.effective_end_date
and trunc(sysdate) between paaf.effective_start_date and paaf.effective_end_date
and trunc(sysdate) between ptp.start_date and ptp.end_date

Lubble! Step away from the SQL prompt!

You need to add something along the lines of

AND FU.user_id = FND_GLOBAL.user_id

This assumes that you have somehow done an FND_GLOBAL.apps_initialize in your sql session prior to running the query. If you are running this from within the app, that initialisation takes place automatically when you log in.

Oh and by the way, you missed a trunc on one of your sysdate references! Bad consultant... bad, bad consultant!

Fled x

Thanks for the code. Works a dream...

Added the trunc around the missed sysdate. I assumed it was the one in the nvl statement

Barn x

...now stepping away from the SQL prompt!!

Yeah, I spotted that too!!!!

Delboy

ok, ok.....