My Oracle Support Banner

ETL 9.2: Need Additional Information For Oracle Support Document 2029636.1 regarding ACA (Doc ID 2134031.1)

Last updated on DECEMBER 11, 2019

Applies to:

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


Qn1:Is solution still valid for 9.2? Already applied this workaround to Production 9.1 environment, however an upgrade from 9.1 to 9.2 is in progress. To date, have applied PUM16 and all ACA PRPs have been applied after PUM16 for 9.2 (PRPs released from Feb 2 - March 24). Reviewing the SQL Server fix/workaround in Oracle Support Document 2029636.1 for our 9.2 environment and would like to know if this solution is still valid for SQL Server Customers how are up to date on PRPs for ACA? Noticed two new configuration pages available for Weekly and Monthly time frames (Set Up HCM > Common Definitions > Afordable Care Act > ACA Weekly Setup/ACA Monthly Setup) and the SQL Views have changed to reflect these new configuration tables. Please advise?
Qn2:1. Is there currently an existing bug for 9.2 ACA TL views to address the below issues where are running into?

2. For sqls views ACA_WEEKLY_TL and ACA_MONTHLY_TL, the payable status does not include TP - Taken By Payroll in the criteria. i.e. AND T.PAYABLE_STATUS IN ('AP', 'CL', 'DL', 'ES', 'NA', 'PD'). Most of our time in the TL_PAYABLE_TIME record is listed a TP. Are other customers modifying the SQL view to include their company specific payable status. Also, why was TP excluded from the the delivered SQL.

3. Duplicate Entries in ACA_WEEKLY_TL and ACA_MONTHLY_TL. Sqls are joining to the ACA_PER_ELIG table with just an EMPLID join. It is missing Max Effdt logic for the ACA_PER_ELIG table based on the ACA_BGN_MTH_TBL (End Date) which causes duplicate records. We are on SQL server.

4. What is the purpose or functional use for the two ACA Weekly Setup/ACA Monthly Setup pages. Was the idea for them? I am guessing the users will need to enter in/load the TL Months and Weeks for each year. In 9.1 the SQLs was using the TL_DATES_TBL table instead of the new configuration tables of ACA_BGN_MTH_TBL and ACA_BGN_WK_TBL? Why was it changed? Using the TL_DATES_TBL doesn't require a user to input setup months or weeks on a yearly basis.

5. The records ACA_MONTHLY_JOB and ACA_WEEKLY_JOB seems to be calculating the monthly hours for Salary Employees incorrect. From what we see, the Hours are rounded when it divides by 5. Decimal is truncated causing a shortage of hours. Also the sql is not using actual hours for the months that an employee has more than one Job row for the month. It will short them the month. View attached labeled Research2 for more details.

6. The last thing noticed is that terminated employees ARE NOT showing up on the ACA_MONTHLY_JOB and ACA_WEEKLY_JOB views. the sql joins with PS_EMPLOYEES and this only contains active employees?
Qn3:4. ACA Weekly and Monthly setup pages are used to restrict the materialized view creation based on what the customer has setup in these pages. This was added for performance reasons.
(Q)I understand that materialized views option is not available for SQL Server customers. Can you provide any documentation on how this helped with performance. Bug/Document ID. It is needed to determine if these setup pages will need to be removed from our security (If these pages where created only for the materialized view creation. Is it the recommendation to modify the view for SQL Server Customers to exclude these configuration tables from the sql views?

5. This seems to be a factor on SQL server only. We are looking into this.
(Q)Is there a recommended workaround to get the correct hours?

6. That is correct we are joining on PS_EMPLOYEES as the TL solution is for ongoing active employees only.
Two Questions for Clarification
(1) These views can also be used if we are audited by IRS to provide the hours? If this is not correct, what views/queries where provided to assist with this?
(2) Also these queries are used to load hours for our Monthly Measurement for ACA Eligibility. Using the Monthly Measurement vs. the Look Back method. That means it is needed to load for the previous month. Example: load Monthly Measurement Hours on April 1 for the previous month March 1 through March 31. It is possible that the employee will be termed that month but still need to load the hours for the month if eligibility changed. Should not be loading hours for the last month the employee is termed?
Qn4:Can you please provide the name of the queries delivered to help analyze etc the What query is able to display hours for a term employee?
Qn5:Are these the only two queries delivered?
It was mentioned that there are queries delivered to help analyze the terminated employee situations. Is that under another query name?
Qn6:So for terminated employee. Lets say the employee terminated on 3/25/2016. When running the PS_ACA_WEEKLY_TL delivered query on 3/25/2016 to pick up hours for their last week of work or run PS_ACA_MONTHLY_TL after the end of the month on 4/1/2016 to pick up the last month of hours the terminated employee will not show up. So PeopleSoft's direction/recommendation is to create a custom query or process etc to pick them up? Currently nothing is delivered to assist with this correct?


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.