Creating And Maintaining ACA Materialized Views
The ACA Materialized Views are used to create summary data stores which are used in reporting and the ACA Eligibility Load Query. The materialized views take the results of complex SELECT statements and saves them to the database.
There are six materialized views delivered as part of this feature. These six views are broken into two sets of three which vary by time period, monthly or weekly.
|
Record Name |
Record Title |
Description |
|---|---|---|
|
ACA_MONTH |
ACA Year Monthly Totals View |
To deliver data for the export to Excel for the monthly period. This view contains the summarized hours and calculated average by month. The ACA_MONTHLY_TOT column reflects the total hours for all employee records under the same common id. |
|
ACA_MONTHLY_JOB |
ACA Monthly Total JOB |
The view contains salaried hours by month. Salaried and non-salaried employees are contained in this view to facilitate the joining of this record in queries. Non-salaried employee types have zero in the ACA_MONTHLY_TOT column. |
|
ACA_MONTHLY_TL |
ACA Monthly Total TL |
The view contains the payable time information for all employees for those TRCs set up in the ACA Time Reporting Codes table. These hours are summed by month. |
|
ACA_WEEK |
ACA Weekly Totals View |
The view is used to deliver data for the export to Excel for the weekly period. This view contains the summarized hours and calculated average by week. The ACA_WEEKLY_TOT column reflects the total hours for all employee records under the same common id. |
|
ACA_WEEKLY_JOB |
ACA Monthly Total JOB |
The view contains salaried hours by week. Salaried and non-salaried employees are contained in this view to facilitate the joining of this record in queries. Non-salaried employee types have zero in the ACA_WEEKLY_TOT column. |
|
ACA_WEEKLY_TL |
ACA Weekly Total TL |
The view contains the payable time information for all employees for those TRCs set up in the ACA Time Reporting Codes table. These hours are summed by week. |
Creating the ACA Materialized Views
Before creating the materialized views, the Administrator has to complete the following tasks:
The ACA Company Table must be populated. The most current effective date on this table controls the earliest date for which rows are created. For instance, if the most current effective dated row in the ACA Company table is dated 1/1/2014, then the materialized views will be created with the earliest row encompassing 1/1/2014.
An initial row of basic data must be populated in the ACA Employee Eligibility page.
The basic data includes:
ACA Common ID
Effective Date
Effective Sequence
Work Period
Note: If you are on a database other than Oracle, please refer to your system documentation regarding creation of materialized views for more information.
|
Page Name |
Definition Name |
Usage |
|---|---|---|
|
PTMAT_MAINT |
Refresh the materialized views. |
Use the Materialized View Maintenance Page (PTMAT_MAINT) for ongoing maintenance of the ACA materialized views.
Navigation:
This example illustrates the fields and controls on the Materialized View Maintenance Page
Field or Control |
Description |
|---|---|
Owner |
To limit the grid to records that belong to a specific owner, enter the name and click Search. The owner of the ACA materialized views is HTL. |
Refresh? |
Click to schedule a refresh for the record. |
Materialized View Name |
Lists the name of the record. You can change the value in this field. |
Materialized |
A display-only field that indicates if the record is materialized Yes or not No. |
Refresh Method |
Lists the refresh method, either complete or fast. |
Refresh Mode |
Lists the refresh mode, either on demand or on commit. |
Build Mode |
Lists the build mode, either immediate or deferred. |
Refreshed |
Indicates if the record is refreshed Yesor not No. |
Refresh Interval |
The time interval in seconds for the refresh procedure to run. Enter the interval in seconds to refresh the record. |
Last Refresh Date |
List the date and time the record was last refreshed. |
Staleness |
Indicates if the record is stale or fresh based on the Oracle System Catalog. |
Run |
Click to execute the PTMATREFVW Application Engine program that will refresh the selected records. |