ETL9.2: ACA Materialized View Missing Effdt Logic For PS_ACA_PER_ELIG (Doc ID 2135555.1)

Last updated on SEPTEMBER 20, 2017

Applies to:

PeopleSoft Enterprise HCM Time and Labor - Version 9.2 to 9.2 [Release 9]
Information in this document applies to any platform.

Symptoms

HCM 9.2.16
It appears Materialized Views are missing the effective dated logic for the table PS_ACA_PER_ELIG. Therefore, ACA_WEEKLY query returning multiple rows due to the ACA_TL_WEEKLY and ACA_JOB_WEEKLY.

Steps to Recreate:
1. Select test employee.
2. Run ACA WEEK query for the dates of Effective Date = 2016-01-31,Evaluation Begin Date=2014-10-13,Evaluation End Date=2015-10-11,Administration Begin Date=2015-10-12,Administration End Date=2015-12-31,Stability Begin
  Date=2016-01-01,Stability End Date=2016-12-31,Time Group=ACA1.
3. Reviewed the accuracy of the data for employee 00319189 in the results of ACA WEEK.
4. Discovered the Hours were Overstated, resulting in an incorrect calculation of the average payable hours.
5. Queried the underlying tables: ACA WEEK, ACA WEEKLY TL, ACA WEEKLY JOB and ACA PER ELIG.
6. The code for the ACA WEEKLY TL and ACA WEEKLY JOB uses the ACA PER ELIG in a join, but does not select based upon max effective date and therefore returns a row for every row the employee has in this table.
7. Review the results of the attached ACA WEEK query.
8. Review results of attached an excel workbook with multiple spreadsheets, ACA TL WEEKLY ACA WEEKLY JOB and ACA PER ELIG.

Cause

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