My Oracle Support Banner

Dynamic Custom Security Profile Based on Organization Hierarchy Does not Work with OTL Authorized Delegate Timecard Entry V2.0 (Doc ID 3025495.1)

Last updated on MAY 31, 2024

Applies to:

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


 Setup dynamic security based on set of dummy organizations  and it worked with HR and person form but did not rerun data with Authorized Delegate.

Steps to replicate the issue
1. A new organization type “Timecard Approval Group“ is created to identify the fact that the new dummy organizations are used for OTL timecard approval.
Navigate to (R) US Super HRMS Manager > Other Definitions > Lookup Tables and query on ‘ORG_TYPE’. Create the new lookup code below:
Code TAG
Meaning Timecard Approval Group
Description (null)
Tag (null)
Effective From 1/1/1951
Effective To (null)
Enabled Yes
2. Organizations
The organizations listed below are created to group the HR organizations together into approval groups. Thus, a manager will be able to view and approve the timesheets and absence requests of any employee in the organization group.
Navigate to (R) US Super HRMS Manager > Work Structures > Organization > Description.
Set the session date to 1/1/2024 and enter the organization names below. In each case, set the Organization Type to ‘Timecard Approval Group’ and leave all other fields blank.
Organization Name
*** Group1
*** Group 2
3 .Organization Hierarchy
The organization hierarchy ‘XXX Timecard Approval’ is created to associate the various HR organizations with the timecard approval group. The top level of the hierarchy is set to the business group organization. The new group organizations is on the second level, and the HR organizations is on the third level.
Navigate to (R) US Super HRMS Manager > Work Structures > Organization > Hierarchy.
Set the session date to 1/1/2024 and enter the values below:
Hierarchy Name XXX Timecard Approval

4. Created Dynamic security  using the following SQL and it works correctly in the Person form. But with authorized delegate person LOV does not return any data .

Navigate to (R) US Super HRMS Manager > Security > Profile, click on the Custom Security tab, and create a new record and add the following query  

 PERSON.person_id in
(select paf2.person_id
from per_all_assignments_f paf2
, per_org_structure_elements ose2
, per_organization_structures ost
, per_org_structure_versions osv
, per_org_structure_elements ose1
, per_all_assignments_f paf
, fnd_sessions ses
where 1=1
and paf2.assignment_type in ('E', 'C')
and paf2.primary_flag = 'Y'
and ses.effective_date between paf2.effective_start_date and paf2.effective_end_date
and paf2.organization_id = ose2.organization_id_child
and ose2.organization_id_parent = ose1.organization_id_parent
and ose2.org_structure_version_id = ose1.org_structure_version_id
and ='XXX Timecard Approval'  -- Hierarchy Name
and ost.organization_structure_id = osv.organization_structure_id
and ses.effective_date between osv.date_from and nvl(osv.date_to, ses.effective_date)
and osv.org_structure_version_id = ose1.org_structure_version_id
and ose1.organization_id_child = paf.organization_id
and paf.assignment_type in ('E', 'C')
and paf.primary_flag = 'Y'
and ses.effective_date between paf.effective_start_date and paf.effective_end_date
and paf.person_id = FND_GLOBAL.employee_id
and ses.session_id = userenv('sessionid')


To view full details, sign in with your My Oracle Support account.

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

In this Document

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.