Defining Reward Data Source Information
This topic lists the common elements that comprise a Reward Data Source and discusses how to define reward data.
|
Page Name |
Definition Name |
Usage |
|---|---|---|
|
TRW_DATASRC_GNRL |
Enter a general description, a detail description, and a high level data source category for the reward data source. |
|
|
TRW_DATASRC_EXTBL |
Set up the Extract tables for the reward data source. Users will have the ability to enter a record name defined in Application Designer. They also can view the SQL text for the record view. |
|
|
TRW_DATASRC_EXFIL |
Define extract filters that are used to retrieve reward data for the statement being processed. The extract filters are used during the extract process to retrieve data from extract tables into extract temporary table. A link initiates a secondary page allowing the user to enter the extract filter for a specific extract filter type. |
|
|
TRW_DATASRC_RIFIL |
Create reward item filters from a field in the Extract table. Reward item filters are additional filters defined at the reward item level that enable the compensation administrator to control the rewards data that is extracted. |
|
|
TRW_DATASRC_RIOUT |
Map fields from the Extract temporary table to the Statement Reward Item Detail table. Users can also control the selection of the table to retrieve the description for a detail data field along with the Detail Grid Column label. |
|
|
TRW_DATASRC_RICALC |
Specify the path to an application class that will perform a calculation. This page also displays the configurable matrix if the application class is retrieving rate information to perform a calculation. |
|
|
TRW_TEST_DATASRC |
Enter reward statement run parameters to test if the reward data source is working correctly. |
Field or Control |
Description |
|---|---|
Data Source ID and Description |
Enter an abbreviation that uniquely identifies the Reward Data Source. Then enter a description of the Reward Data Source. These fields become display only on the other pages within the component. |
Extract Temporary Table |
The Extract Temporary table is a temporary table defined in Application Designer. It will be populated with data retrieved from the Extract tables. It is the table that the system will use as the reward data source. In addition, reward item filters are created from the fields on the Extract temporary table. |
Use the Define Reward Data Source - General page (TRW_DATASRC_GNRL) to enter a general description, a detail description, and a high level data source category for the reward data source.
Navigation:
This example illustrates the fields and controls on the Define Reward Data Source - General page.
Field or Control |
Description |
|---|---|
Detail Description |
A detail description providing a functional explanation of where the rewards data is being extracted. |
Data Source Category |
A high level category assigned to the Reward Data Source. Listed below are the data source categories that are available for selection:
Note: The system uses the data source category to determine if validation is necessary when administering total rewards statements. Specifically, if the statement that is being processed contains reward items that are associated with data sources in the External Data category, the Validate button becomes available on the Administer Statement page for the administrator to confirm that external data has been loaded to the PeopleSoft system successfully. |
Use the Define Reward Data Source – Extract Tables page (TRW_DATASRC_EXTBL) to set up the Extract tables for the reward data source.
Navigation:
This example illustrates the fields and controls on the Define Reward Data Source – Extract Tables page.
Users will have the ability to enter a record name defined in Application Designer. They also can view the SQL text for the record view.
Field or Control |
Description |
|---|---|
Extract Temporary Table |
Select a target table that is used to store total rewards data to be extracted from the tables listed in the Reward Data Source Extract Tables section. |
Reward Data Source Extract Tables
Field or Control |
Description |
|---|---|
Extract Table |
The Extract Table is defined in the Application Designer and designed to extract rewards data from PeopleSoft. |
Description |
Displays the table description defined in Application Designer. |
View SQL |
This link is enabled when the table is defined as a view in Application Designer. Click the View SQL link to display a secondary page that will show the SQL statement used by the view. |
Use the Define Reward Data Source - Extract Filters page (TRW_DATASRC_EXFIL) to define extract filters that are used to retrieve reward data for the statement being processed.
Navigation:
This example illustrates the fields and controls on the Define Reward Data Source - Extract Filters page.
Reward Data Source Extract Filters
The extract filters are used during the extract process to retrieve data from extract tables into extract temporary table. A link initiates a secondary page allowing the user to enter the extract filter for a specific extract filter type.
Field or Control |
Description |
|---|---|
Filter Type |
The filter type controls the Reward Statement run parameters that will be included in the Extract Filter. Listed below are the delivered filter types:
|
Extract Filter |
Display only field. The extract filter is the filter text specified for the filter type. The filter parameters are the variables that need to be included in the extract filter. These variables are required to substitute the variable with the reward statement run parameter at runtime. Listed below are the filter types and the associated substitution variables when a filter type is selected:
|
Edit Filter |
Click to display a secondary page that enables users to enter extract filters. |
Use the Define Reward Data Source – Reward Filters page (TRW_DATASRC_RIFIL) to create reward item filters from a field in the Extract table.
Navigation:
This example illustrates the fields and controls on the Define Reward Data Source - Reward Filters page.
Reward Item Filters
Reward item filters are additional filters defined at the reward item level that enable the compensation administrator to control the rewards data that is extracted.
Field or Control |
Description |
|---|---|
Field Name |
Select a field name from the Extract temporary table to define a reward item filter. Certain fields that should not be used as reward item filters include:
|
Required |
Click this check box to mark a reward item filter as required. This will require the compensation administrator to configure the reward item filter when using this reward data source to define a new reward item. |
Edit Type |
To ensure that the compensation administrator enters a valid value, when configuring the reward item filter at the reward item level, an edit type can be associated to the Reward Filter field. Listed below are the valid edit types:
If the edit type of Prompt Table is chosen, then a prompt table must be specified to retrieve valid values for the Field Name when the compensation administrator configures the reward item filter at the reward item level. Note: If the field is not the highest key on the prompt table, then a view against the prompt table will need to be created in order to select the Reward Item Filter value when defining a reward item. |
Use the Define Reward Data Source - Reward Output page (TRW_DATASRC_RIOUT) to enable users to map fields from the Extract temporary table to the Statement Reward Item Detail table.
Navigation:
This example illustrates the fields and controls on the Define Reward Data Source - Reward Output page.
Reward Item Output Fields
Users can also control the selection of the table to retrieve the description for a detail data field along with the Detail Grid Column label.
Field or Control |
Description |
|---|---|
Field Description |
Display only field that displays the field description of a field in the Extract temporary table. Fields are populated when the Extract Temp table is entered on the Rewards Data Source - Extract Tables page. |
Field Name |
Display only field that displays the field name of a field in the Extract temporary table. Fields are populated when the Extract Temp table is entered on the Rewards Data Source - Extract Tables page. |
Key |
Display only field that displays if the field name is a key in the Extract temporary table. Fields are populated when the Extract table is entered on the Rewards Data Source - Extract Tables page. |
Detail Output Column |
Fields from the Extract table can be mapped to a Detail Output Column that exists in the Statement Reward Item Detail table. The fields mapped to a Detail Output Column are the valid fields that can appear in the Reward Item Detail grid displayed in the My Total Rewards statement. Listed below are the valid values for the Detail Output Column:
A Detail Output Column can only be mapped to one field from the Extract temporary table. In addition to this, one or more of the numeric reward data fields must be mapped (for example, Employee Paid, Employer Paid, Units, or Company Provided). |
Detail Description Table |
In order to translate a field value into a description to be displayed on the Reward Item Detail grid, a Detail Description table must be specified. |
Detail Description Field |
In order to translate a field value into a description to be displayed on the Reward Item Detail grid, a Detail Description field must be specified from the Detail Description table selected. |
Detail Grid Column Label |
For every field mapped to a Detail Output Column, a Detail Grid Column Label must be specified. |
Use the Define Reward Data Source – Reward Calculations page (TRW_DATASRC_RICALC) to specify the path to an application class that will perform a calculation.
Navigation:
This example illustrates the fields and controls on the Define Reward Data Source - Reward Calculations page.
Reward Item Calculations
This page also displays the configurable matrix if the application class is retrieving rate information to perform a calculation.
Field or Control |
Description |
|---|---|
Sequence |
Multiple calculations can be specified for the reward data source. The Sequence field controls the order in which the reward item calculations will be performed. For example, a reward data source for stock administration may require the calculation of exercisable shares, and then a calculation to calculate the estimated value of the exercisable shares. |
Application Class Path |
Enter the path of the application class to retrieve the calculation. Application classes are called to perform reward item calculations against the Statement Reward Item Detail table by the Rewards Extract process . |
Description |
Description of the application class. |
Method Name |
Enter the name of the method within the application class that will perform the calculation. |
Matrix Name |
Depending upon the selected application class, a matrix may be used to retrieve a rate the calculation requires. The matrix is specific to the application class and will require the user to specify a rate in that table for the reward statement being processed. |
Note: Application Class must be registered under Application Class Registry found under with the Class Usage set to REWARD CALCS.
The extraction of reward data for a total reward statement can be challenging to set up and validate. The Test Reward Data Source component assists in validating and reviewing how data is extracted from a reward data source.
This component will accept test parameters similar to the test parameters to run a total reward statement. It will generate the SQL use to extract reward data into the Extract Temporary table. If you specify a reward item, it will also generate the SQL to extract data for the reward item.
On this page you will be able to view and run the SQL, as well as view that data populated in the tables.
If the reward data source has reward calculations, then they will display when you test the reward data source. Reward calculations can only be run if you have specified a reward item as a test parameter.
Note: If SQL errors are encountered on this page, then the error messages that are generated may not be specific to the actual problem with the SQL statement. Therefore, use the View SQL link to review the SQL statement generated and copy it into a SQL tool to identify the exact problem with the SQL statement.
Use the Test Reward Data Source page (TRW_TEST_DATASRC) to enable the user to enter reward statement run parameters to test if the reward data source is working correctly.
Navigation:
This example illustrates the fields and controls on the Test Reward Data Source page.
Test Parameters
Field or Control |
Description |
|---|---|
From Date |
The start date that you want the system to use to test the reward data source. |
Thru Date |
The end date that you want the system to use to test the reward data source. |
Group ID |
The group ID that you want the system to use to test the reward data source. Note: If the group ID is specified, then the employee ID and employee record are disabled. |
Employee ID |
The Employee ID that you want the system to use to test the reward data source. Note: If the employee ID is specified, then the group ID will be disabled. |
Employment Record |
The employment record that you want the system to use to test the reward data source. |
Reward Item ID |
The Reward Item that you want the system to use to test the reward data source. |
Test Reward Data Source |
This button enables the user to submit the Rewards Extract process in test mode through the Process Scheduler. |
Delete Test & Reset Parameters |
This button enables the user to delete the current reward data source test data. Clicking this button deletes the Extract SQL, Reward Item SQL, Reward Item Calculations, test data populated in the Extract Temporary table or Employee Statement Reward Item table, and any temporary queries. |
View Extract Temporary Table |
Clicking this button will create a temporary query and open a new browser to allow the user to view the data populated in the Extract Temporary table. |
View Employee Reward Item Table |
Clicking this button will create a temporary query and open a new browser to allow the user to view the data populated in the Employee Statement Reward Item table. Note: To generate the temporary query, the user needs to have proper query access to the Extract Temporary table and the Employee Reward Item table. |
Refresh |
When the Rewards Extract process is running in test mode, this button is enabled to allow the user to refresh the test reward data source page. When the Rewards Extract process is not processing, this button is disabled. |
Check Messages |
If the Rewards Extract process or a Reward Calculation generates a warning or error to the Statement Message log table for the data source test processed, then the system enables the Check Message link. This enables the user to access a subpage to view the messages. |
Generated Extract SQL
Field or Control |
Description |
|---|---|
Description |
A description of the Extract SQL generated for the reward data source test. |
View SQL |
Click this link to view the extract SQL generated for the reward data source test. |
Execute SQL |
Click this link to run the SQL to populate either the Extract Temporary table or the Employee Statement Reward Item table. Extracting SQL based on an Extract table populates the Extract Temporary table. Extracting SQL based on a reward item populates the Employee Statement Reward Item table. |
SQL Status |
A description of the current status of the SQL statement displayed. Listed below are the valid SQL statuses:
|
Rows |
Displays the number of rows inserted into the Extract Temporary table or the Employee Statement Reward Item table. |
Reward Item Calculations
Field or Control |
Description |
|---|---|
Application Class Name |
Displays the application class for the reward calculation. |
Description |
Displays the description of the application class. |
Method Name |
Displays the method name within the application class that will be run to perform the reward calculation. |
Execute Calculation |
Click this link to enable the user to perform the reward calculation. |
Calculation Status |
Displays a description of the current status of the reward calculation. Listed below are the valid calculation statuses:
|
Note: Depending upon the design of the reward calculation, messages may be written out to the Statement Message Log table when an error is encountered. In this scenario, the Check Message link is activated to allow the user to view any warning or error messages.