Timekeeper: How Do You Limit The Project/Task LOVs Using The Recurring Period as a Parameter? (Doc ID 405112.1)

Last updated on JULY 15, 2016

Applies to:

Oracle Time and Labor - Version 11.5.10 and later
Information in this document applies to any platform.

Goal

You are trying to limit the values returned for the project and task LOVs within OTL Timekeeper Entry. The projects and tasks should be limited by comparing the start and end dates of the project and/or task with the start and end date of the payroll period. In your attempt to use the Flexfield Value Sets for the project and task you have been unable to access either the start date or the end date of the payroll period.

When you use fnd_profile.value('OTL_TK_END_DATE') it seems to not return anything.

TABLES:

PA_PROJECTS p, PA_PROJECT_STATUSES ps

WHERE CLAUSE:

where p.project_status_code = ps.project_status_code
and p.template_flag = 'N'
and ps.status_type = 'PROJECT'

and to_date(fnd_profile.value('OTL_TK_END_DATE'),'DD-MON-YYYY') >=
nvl(ps.start_date_active,to_date(fnd_profile.value('OTL_TK_END_DATE'),'DD-MON-YYYY'))

and to_date(fnd_profile.value('OTL_TK_START_DATE'),'DD-MON-YYYY') <=
nvl(ps.end_date_active,to_date(fnd_profile.value('OTL_TK_START_DATE'),'DD-MON-YYYY'))

and to_date(fnd_profile.value('OTL_TK_END_DATE'),'DD-MON-YYYY') >=
nvl(p.start_date,to_date(fnd_profile.value('OTL_TK_END_DATE'),'DD-MON-YYYY'))

and to_date(fnd_profile.value('OTL_TK_START_DATE'),'DD-MON-YYYY') <=
nvl(p.completion_date,to_date(fnd_profile.value('OTL_TK_START_DATE'),'DD-MON-YYYY'))

and exists (select 1 from PA_PROJECT_STATUS_CONTROLS psc
where ps.project_status_code = psc.project_status_code
and nvl(psc.copy_to_user_status_flag,'Y') <> 'N'
and psc.action_code = 'NEW_TXNS'
and psc.enabled_flag = 'Y')
order by p.segment1

Why isn't this returning anything in the LOV?

Solution

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms