ETL9.2-Issues With TL_AUTO_ENRL Process When Using Complex Query Criteria (Doc ID 2248421.1)

Last updated on JULY 21, 2023

Applies to:

Goal

On : 9.2 version, PUM20 Other,Issues with TL_AUTO_ENRL process when using complex query criteria ,I'm writing to ask what are the limitations of using criteria in process queries to be evaluated by the TL_AUTO_ENRL process.

Can the customer use a query without the JOB record? I am considering substituting a view of the JOB record with the same keys as JOB. The view of job would contain all necessary joins, OR criteria, and NOT EXISTS subqueries. ?



Customer is implementing Time and Labor and TL Automated Enrollment into a live system. We are prototyping and testing Enrollment Groups and associated Process Queries. The customer has 17 enrollment groups corresponding to unique combinations of workgroups and taskgroups.

Our initial testing of automated enrollment was working well with 17 concurrent active enrollment groups. Each enrollment query was very simple and used only the JOB record with AND criteria.

We then revised all enrollment query criteria to join additional records to JOB, adding OR criteria, and NOT EXISTS criteria with Subqueries. This was done in order to make the selected populations more accurately match the time reporting attributes for each enrollment group and maximize the benefits of automation from the process.

The TL_AUTO_ENRL process is having trouble evaluating the more complex criteria on multiple enrollment groups. When the process is run for a single employee it errors with 'Employee in multiple groups' and lists all 17 groups even though the criteria across all 17 groups is mutually exclusive to the entire population.

To prove the defect exists, we further simplified our setup. We setup a system with one enrollment group having complex criteria (as noted above) and that processed OK. Next, we added a second enrollment group where both queries used only JOB and AND criteria. That processed ok. Next we added OR criteria to one of the group queries and received the 'Multiple Groups' error even though the criteria was mutually exclusive to the one emplid/rcd being tested.

I'm writing to ask what are the limitations of using criteria in process queries to be evaluated by the TL_AUTO_ENRL process.

Can the customer use a query without the JOB record? I am considering substituting a view of the JOB record with the same keys as JOB. The view of job would contain all necessary joins, OR criteria, and NOT EXISTS subqueries.
 

Solution

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