My Oracle Support Banner

SQL Predicate Generates UNION ALL SELECT After Modifying Criteria In Person Security Profile (Doc ID 2305145.1)

Last updated on APRIL 08, 2019

Applies to:

Oracle Fusion Global Human Resources Cloud Service - Version and later
Information in this document applies to any platform.


SQL Predicate generates UNION ALL SELECT after modifying criteria in Person Security Profile

The SQL Predicate generates UNION ALL SELECT after modifying criteria in Person Security Profile. This is causing huge performance issues (Reports -> time-out) and long SQL predicate (strings sometimes longer than 4000 characters).

SQL predicate should not create union all and increase in size

The issue can be reproduced at will with the following steps:

1. For this example we’re using a Department Tree (inside Org. Sec. Profile) to determine which persons they query on. But we also saw the same issue when we’re using a list of Legal Employers (inside Org. Sec. Profile), and also when we’re using a Custom SQL statement. Then the SQL predicate will becomes twice as long.

Security Profile:
Include future people
Secure by Department
Assignments to Evaluate: Active, suspended, or most recently terminated

The SQL Predicate looks fine.

2. When  assigning this Person Security Profile to a Data role, and when we’re changing the person security profile again, the SQL predicate will increase.


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.