NoetixViews for Oracle Payroll
Oracle Payroll stores all payroll information at the most granular level. This makes the data structure highly complex and the data difficult to manage. NoetixViews for Oracle Payroll simplifies the complex data structure by providing predesigned views for key payroll areas, such as employee earnings, employee deductions, employee taxes, employer liabilities, registers, costing, and various employee exceptions.
To create reports from such complex and huge data, the data must be summarized first. This process is very time-consuming. NoetixViews for Oracle Payroll accelerates this process by creating intermediate summary tables. These tables are created by computing balances for every assignment per payroll run.
Oracle makes frequent changes to the data structure of Payroll due to the frequent changes in the employment law. The upgrade protection feature of NoetixViews protects the reports developed by NoetixViews for Oracle Payroll from these changes.
NoetixViews for Oracle Payroll uses the data mart technology to simplify the complexities of Payroll. Most of the views of the NoetixViews for Oracle Payroll module are built on Noetix Payroll Data Mart. For a list of these views, see About Noetix Payroll Data Mart.
NoetixViews for Oracle Payroll contains approximately 30 views, providing both a broad and deep insight into the payroll data. Many of these views can be joined to the Oracle Human Resources views for more information about employees, pay rates, and so on. The Payroll views are available for the United States, Canadian, Australian, and United Kingdom legislations. However, Cross Operations Extension (XOP) views cannot be generated for payroll views because they would not return valid results.
NoetixViews for Oracle Payroll uses the same security model as NoetixViews for Oracle Human Resources. For more information about Human Resources security in NoetixViews, see NoetixViews for Oracle Payroll.
For more information about the payroll views, see the Noetix Help File.
About Noetix Payroll Data Mart
Noetix Payroll Data Mart helps in computing and saving balances for every assignment per payroll run. These balances can be used for reporting and not for making a balance call.
In the Noetix Payroll Data Mart, data will not be populated for a balance if its name ends with “Eligible Comp”.
Noetix Payroll Data Mart computes all the balances for each payroll run for the most commonly used dimensions that include RUN, Period-to-Date, Month-to-Date, Quarter-to-Date, and Year-to-Date. These balances will be used for reporting because it makes the query performance faster than the Oracle delivered balance calls.
The following views of the NoetixViews for Oracle Payroll module are built on Noetix Payroll Data Mart either directly or indirectly through a base table that uses the data mart technology:
PAY Accruals
PAY Balances
PAY Custom Balances
PAY Deductions
PAY Earnings
PAY Employer Ben Liability
PAY Employer Tax Liability
PAY Garnishment Balances
PAY Gre Totals
PAY Gross And Net Balances
PAY Gross To Net Details
PAY Gross To Net Summary
PAY Negative Balances
PAY Tax Balances
PAY Third Party Balances
PAY US Payroll Register
PAY US W2 Register
Before Installing Noetix Payroll Data Mart
This section provides information about the tasks to be performed before installing the Noetix Payroll Data Mart.
Install Oracle Payroll
The Payroll application must be successfully installed before installing Noetix Payroll Data Mart.
Calculate Tablespace
You must calculate the tablespace necessary for the data mart indexes and tables before beginning the generation. The storage required for 1,000 employees per pay period is approximately 7 MB. The EIS Datamart DB Stats.xls workbook, found in the Payroll Datamart.zip file, can be used to calculate the tablespace. The workbook will help you to estimate the data storage space needed for the number of employees and payroll frequency in your organization. However, the calculation may not always be accurate.
You can use an existing tablespace if it has the required space. Otherwise, you must create a tablespace. During the generation of Noetix Payroll Data Mart, a script is run to select or create this tablespace. It is recommended that you create separate tablespaces for indexes and tables.
Installing Noetix Payroll Data Mart
This section provides information about the tasks that must be performed to install Noetix Payroll Data Mart.
Noetix Payroll Data Mart must be installed before generating Noetix views. Otherwise, you will not be able to install the NoetixViews for Payroll module.
For information about generating views, see Pregeneration Considerations and Pregeneration Considerations in “Generation of Views.” For information about upgrading NoetixViews, see Installing NoetixViews in “Installing Noetix Views.” Verify the generation before continuing.
How to Install Noetix Payroll Data Mart
If you are upgrading from one version of the data mart to another, see Upgrade Noetix Payroll Data Mart.
To install Noetix Payroll Data Mart
Navigate to Datamart/Payroll.
Unzip the Payroll Datamart.zip file, and extract all the files to a local directory. The directory path cannot contain spaces and more than 79 characters. The maximum number of characters is a limitation of SQL*Plus.
Log on to SQL*Plus as the APPS user.
Modify the EIS Datamart DBA Install.sql script to change the path and size of the data file (tablespace). The default is a locally managed tablespace. You can change this to a dictionary managed tablespace to suit your environment. If an existing tablespace is used, delete the section in the script that is used to create the tablespace.
Run the EIS Datamart DBA Install.sql script. This script prompts for the following parameters:
SID: Database connect string where the data mart should be installed.
SYSTEM Password: SYSTEM password to create the database user XXEIS and the tablespaces required for tables and indexes.
- Run the EIS Datamart Install.sql script. This script prompts for the following parameters:
SID: Database connect string where the data mart should be installed.
APPS User Name: User name of the APPS user. It is usually APPS unless changed during the installation of Oracle E-Business Suite.
APPS Password: APPS Password to give grants to XXEIS on the objects and create the custom application, responsibility, and user.
XXEIS Password: The password to create the tables, sequences, packages, synonyms, and triggers in the XXEIS schema. This password was set while running the EIS Datamart DBA Install.sql script to create the XXEIS user. If the password has not been changed, it would be xxeis.
Tablespace for XXEIS Tables: Tablespace in which the data mart tables will be created. Proper sizing of the tablespace must be done. Give the same tablespace that was created for tables using the EIS Datamart DBA Install.sql script.
Tablespace for XXEIS Indexes: Tablespace in which the data mart indexes will be created. Give the same tablespace that was created for indexes using the EIS Datamart DBA Install.sql script.
Datagroup Name: Datagroup to which the XXEIS user must be attached. The default value is Standard.
Directory: Directory in which the zip file has been extracted. Do not put a \ at the end.
Objects Created During Generation
The following application objects are created during generation.
Object Name | Object Type | Description |
---|---|---|
XXEIS | Database User | It is used to store all the objects of the data mart. The same user will be used for any future applications from Noetix Payroll Data Mart. |
XXEIS Custom Application | Custom Application | It is registered as part of the generation. All the concurrent programs will be created with this application. |
XXEIS Payroll Data Mart Menu | Application Menu | It will be used to submit any concurrent programs of the data mart. |
XXEIS DM | Application User | It is the default data mart user created with the XXEIS Payroll Data Mart responsibility to submit the concurrent programs. The default password is welcome1. |
XXEIS Payroll Process | Request Group | It has all the data mart concurrent programs. |
XXEIS Payroll Data Mart | Responsibility | It is granted to any user who has to submit the concurrent programs, mainly EIS Generate Balances, which is used to populate the data mart. |
EIS Generate Balances Process | Concurrent Program | It is used to populate the data mart. For more information, see step On the Concurrent Manager, click XXEIS Payroll Datamart > Processes and Reports > Submit Processes and Reports to run the EIS Generate Balances Process program to gather the balances and store them in the data mart. in “Populate Data Automatically.” |
EIS Generate Balances Date Range Process | Concurrent Program | It is used to populate the data mart. This enables you to set a start date and an end date for which balances are to be populated. For more information, see the note after step On the Concurrent Manager, click XXEIS Payroll Datamart > Processes and Reports > Submit Processes and Reports to run the EIS Generate Balances Process program to gather the balances and store them in the data mart. in “Populate Data Automatically.” |
EIS Payroll Datamart Purge Process | Concurrent Program | It is used to purge the balance in the data mart that is not required. For more information, see Purge Noetix Payroll Data Mart. |
EIS Payroll Datamart Purge Date Range Process | Concurrent Program | It is used to purge the balance in the data mart that is not required. This enables you to set a start date and an end date for which balances are to be purged. For more information, see Purge Noetix Payroll Data Mart. |
EIS Worker Process | Concurrent Program | It is the thread program spawned by the EIS Generate Balances for multi-threading the payroll process. This can be manipulated for performance reasons. For more information, see the Oracle documentation. |
EIS Grant Access to Accrual Plans Process | Concurrent Program | It must be run when any accrual plan is created. For more information, see Grant Access to Accrual Plans. |
Populating Noetix Payroll Data Mart
After Noetix Payroll Data Mart is installed, balances must be extracted for the past payroll runs to populate the data mart tables. An administrator who is aware of the reporting needs for this data must perform this task. The administrator must know how many years of data need to be extracted. It is recommended that you use at least the previous year and the current year’s data.
Do not begin the population of data prior to running Stage 4. Otherwise, the generation will fail because the system cannot grant execute permission to the EIS Utility package. If you want to run these processes at the same time, you may begin populating data after the role prefix editing step begins in the Noetix views generation.
The data mart tables can be populated either automatically or by scheduling.
When the data is populated automatically, the data mart gets populated every time a payroll is run. Typically, it would add 1-12% of the overall payroll processing time.
When the data population is scheduled, the data mart gets populated at the scheduled time. It can be scheduled periodically based on the reporting needs of the organization. You can also choose to do it manually without scheduling. If you do so, you will have to populate the data manually every time you run a payroll.
When the number of employees in the organization is not very large, it is recommended that the data be populated automatically. Otherwise, it is recommended that the data population job be scheduled.
Populate Data Automatically
To populate data automatically
Ensure that the Oracle Balance Initialization process has been completed and verified in the Payroll application.
Log on to Oracle E-Business Suite with the following credentials:
User name: XXEIS DM
Password: welcome1
Responsibility: XXEIS Payroll Datamart
When you log on for the first time, you will be prompted to change the password.
On the Concurrent Manager, click XXEIS Payroll Datamart > Processes and Reports > Submit Processes and Reports to run the EIS Generate Balances Process program to gather the balances and store them in the data mart.
This process requires the following parameters:
Start Year: Start year for which balances need to be extracted.
End Year: End year for which balances need to be extracted.
Enable Triggers (Yes or No): Set it to Yes. It enables the data mart triggers. After every successful payroll run, this populates the data mart summary tables automatically for all the future payroll runs. Data will be immediately available for reporting. The default value is Yes.
Remove All Current Data (Yes or No): When set to Yes, it removes all the existing data and repopulates the data for the given parameters. The default value is No. Set this value to Yes only if needed.
Instead of the EIS Generate Balances process, you can also run the EIS Generate Balances Date Range process. This process enables you to set a start date and an end date for which balances are to be populated in the data mart. There is also an optional balance name parameter that can be used to generate the balances in the data mart for only a single balance. This option is useful when a specific balance has been incorrectly populated in the data mart and needs to be repopulated. But before repopulating the data mart, the specific balance needs to be purged in the data mart from the time the data mart has been populated. The remaining parameters are the same for both the processes.
While the EIS Generate Balances process is running, gather statistics for the XXEIS schema every 30 to 40 minutes. This will greatly improve the performance of the process. This is needed only during the initial population of the data mart with payroll history, and not for subsequent populations.
Populate Data by Scheduling
The only difference between populating data automatically and scheduling it is in the value that is set for the Enable Triggers (Yes or No) parameter. The value for the Enable Triggers (Yes or No) parameter is set to “No” when you schedule the population of data. All other steps are the same.
For information about scheduling data population job, see steps Ensure that the Oracle Balance Initialization process has been completed and verified in the Payroll application. through On the Concurrent Manager, click XXEIS Payroll Datamart > Processes and Reports > Submit Processes and Reports to run the EIS Generate Balances Process program to gather the balances and store them in the data mart. in “Populate Data Automatically.” After you specify the values for all the parameters, you can schedule the job as a Concurrent Manager task.
Maintain Noetix Payroll Data Mart
To maintain Noetix Payroll Data Mart, some tasks must be performed periodically after the initial population of data. This section provides information about all those tasks.
Turn Off Real-Time Update of Noetix Payroll Data Mart
If you want to turn off the real-time updates of the data mart without submitting a Concurrent Manager request, run the following statement in the XXEIS schema:
update EIS ACTION PARAMETERS
set parameter value = 'OFF'
where parameter name = 'DATAMART ON/OFF';
commit;
Running this statement will turn off real-time updates even if the data mart triggers were turned on through the EIS Generate Balance Process program. After running this statement, if you wish to turn on real-time updates to the data mart at a later time, run the EIS Generate Balances Process program with the value of the Enable Triggers (Yes or No) parameter set to “Yes”.
Purge Noetix Payroll Data Mart
You can use the EIS Payroll Datamart Purge process to purge the data that is not required.
To purge balances
Log on to Oracle E-Business Suite as the APPS user.
On the Concurrent Manager, run the EIS Payroll Datamart Purge process. This process requires the following parameters:
Start Year: Start year for which the balances need to be purged.
End Year: End year for which the balances need to be purged.
Balance Name: This is an optional parameter that allows purging a specific balance from the data mart. If this parameter is not supplied, then all balances will be purged from the data mart.
Note: Instead of the EIS Payroll Datamart Purge process, you can also run the EIS Payroll Datamart Purge Date Range process. This process enables you to set a start date and an end date for which balances are to be purged in the data mart. The remaining parameters are the same as the EIS Payroll Datamart Purge process.
Update Noetix Payroll Balances
Whenever there is a change in the balance feeds, the data mart must be updated with the latest data. You can update the data mart in the following two ways:
Update automatically: This is same as populating the data automatically. For information, see steps Ensure that the Oracle Balance Initialization process has been completed and verified in the Payroll application. through On the Concurrent Manager, click XXEIS Payroll Datamart > Processes and Reports > Submit Processes and Reports to run the EIS Generate Balances Process program to gather the balances and store them in the data mart. in “Populate Data Automatically.”
Update by scheduling: This is same as populating the data by scheduling. For information, see Populate Data by Scheduling.
You would either need to purge the data mart or set the Remove All Current Data (Yes or No) parameter to “Yes” depending on the changes that are made to the data mart.
Gather Statistics
Many of the Noetix Payroll views use cost-based optimization. To take full advantage of this, it is necessary to gather statistics on a regular basis, ensuring that the XXEIS schema is included. For more information, see the Oracle documentation.
Grant Access to Accrual Plans
In Oracle Human Resources Management System (HRMS)/Payroll, whenever a accrual plan is created or any change is made to an existing accrual plan, Payroll dynamically creates fast formula packages. For NoetixViews to function properly, the XXEIS user needs to have grants for these newly created packages.
The EIS Grant Access to Accrual Plans concurrent program will create these grants. This process needs to be run each time an accrual plan is created or changed.
To grant the XXEIS user the access to accrual plans
Log on to Oracle E-Business Suite with the following credentials:
User name: XXEIS DM.
Password: The password that you defined earlier. If you have not changed it, then it is welcome1.
Responsibility: XXEIS Payroll Datamart.
On the Concurrent Manager, click XXEIS Payroll Datamart > Processes and Reports > Submit Processes and Reports to run the EIS Grant Access to Accrual Plans process to create grants to any new or modified accrual plans. This process does not require any parameters.
After running the concurrent program, the grants for these dynamically created packages are given to XXEIS, and the NoetixViews accrual views will reflect the changes.
Upgrade Noetix Payroll Data Mart
If you have previously installed Noetix Payroll Data Mart, you have to upgrade your generation for the latest version of the data mart. Upgrading the data mart will not change the current data that is stored in the data mart; it will only upgrade the version with latest software components related to Noetix Payroll Data Mart.
Noetix Payroll Data Mart must be upgraded before upgrading NoetixViews for Oracle Payroll. Otherwise, you will not be able to upgrade the views.
To upgrade Noetix Payroll Data Mart
Navigate to Datamart/Payroll.
Unzip the Payroll Datamart Upgrade.zip file, and extract all the files to a local directory. The directory path cannot contain spaces and more than 79 characters. The maximum number of characters is a limitation of SQL*Plus.
Log on to SQL*Plus as the APPS user.
Run the EIS Datamart Upgrade.sql script. This script prompts for the same parameters that are prompted when running the EIS Datamart Install.sql script. Only the parameters for tablespaces are not prompted as you have it already during your first generation. Give the same information that you had given during your first generation. To continue with the upgrade process, see step Run the EIS Datamart Install.sql script. This script prompts for the following parameters: of “Installing Noetix Payroll Data Mart.”
Uninstall Noetix Payroll Data Mart
To uninstall Noetix Payroll Data Mart
Run the EIS Datamart Uninstall.sql script to delete all XXEIS objects. This script prompts for the following parameters:
SID: Database connect string where the data mart is located.
APPS User Name: User name of the APPS user. It is usually APPS unless changed during installation of Oracle E-Business Suite.
APPS Password: APPS Password to delete the user, responsibility, and custom application.
XXEIS Password: The password to delete the packages, triggers, synonyms, tables, and sequences in the XXEIS schema. This password was set while running the EIS Datamart DBA Install.sql script to create the XXEIS user. If the password has not been changed, then it would be xxeis.
Manually remove the XXEIS user and tablespaces. They were originally created while running the EIS Datamart DBA Install.sql script.