Creating Table Lookup Aliases

This chapter provides an overview of table lookups and discusses how to:

• Create a physical table.

• Set up a table lookup alias.

Understanding Table Lookups

This section provides overviews of:

• How table lookups work.

• Regular table lookups.

• Program-generated table lookups.

How Table Lookups Work

Pension plans can use tables for actuarial factors, interest rates, and regulatory information (such as the taxable wage base and the 401(a)(17) and 415 limits).

Tables can also be used to look up unknown information corresponding to other, known information. For example, you can look up the social security normal retirement age (SSRA) that corresponds to an employee's date of birth.

To use the Pension Administration table lookup, you have to create the lookup table.

In its most basic form, a table lookup finds a value corresponding to another known value, which is called the lookup basis. For example, a code might assign numbers to each letter in the alphabet. You can then decipher numeric messages by looking up the letters that correspond to the numbers.

When you look up information on a pension table, the lookup basis is either an employee variable or a date. For example, you look up an early retirement factor based on an employee's age, and you look up federal midterm rates based on a date.

A table lookup consists of two parts:

• The physical table where you store the information.

  This table must conform to strict requirements regarding the number of columns and what is in each column.

• The instructions for using the table.

  These are the parameters you set up on the Table Lookup Alias page. They include:

  • Information about the lookup basis.

    For example, if you're looking up an early retirement factor based on an employee's age, you have to provide an alias for the age.

  • Parameters for handling situations where the lookup basis is not on the table.

    For example, an employee's benefit commencement age is 62.25, and the table includes only whole numbers. The table lookup settings tell the system whether to use the early retirement factor that corresponds to age 62 or age 63.

There are two types of table lookups:

• A regular table lookup has one result that is constant throughout a calculation.

• A program-generated lookup has different values at different times—for example, an interest rate that changes over time.

Regular Table Lookups

A regular table lookup locates information based on an unchanging data value. For example, it can look up a social security retirement age based on an employee's date of birth, and an early retirement factor based on an employee's benefit commencement age.

The lookup basis—for example, the birth date or benefit commencement age—is an alias that is resolved once per calculation. When you set up a regular table lookup, you put this alias directly into the table lookup parameters.

When you set up a regular table lookup on the Table Lookup Alias page, set Lookup Values to Use Alias.

Program-Generated Table Lookups

When you apply interest rates or grant cash balance credits, you may need to look up information that varies from period to period. For example:

• If you use federal midterm rates for interest on employee contributions, you need to look up the new rate every period.

• If cash balance credits are determined at a different rate for earnings above and below the taxable wage base (TWB), you need to look up the new TWB every period.

• If cash balance credits increase based on an employee's age, service, or both, you have to look up the age and service values every period.

In these situations, the function that requires the lookup knows the dates to use for the lookup, and therefore it provides the lookup basis. Such lookups are classified as program-generated because the program provides the lookup basis. When you set up table lookup parameters, you do not enter a lookup basis.

Applying 401(a)(17) limits also requires looking up limits every period. PeopleSoft delivers pre-configured program-generated lookups to use with 401(a)(17):

• The TRA86 lookup uses the TRA 86 Limits table.

• The OBRA93 lookup uses the OBRA 93 Limits table.

You can examine both of these lookups on the Table Lookup Alias page.

When you set up a program-generated table lookup on the Table Lookup Alias page, set Lookup Values to Program Generated.

See Also

Using Database Aliases

Creating a Physical Table

This section provides overviews of delivered tables, table lookup requirements, and sequence numbers and discusses how to:

• Set up two-dimensional tables.

• Plan the lookup data.

Understanding Delivered Tables

Pension Administration provides several tables you might incorporate into a table lookup, including the Taxable Wage Base table, 415 Limits tables, and some interest rate tables. You may want to create other tables, as well, for plan factors, interest rates, or other information.

You use Application Designer to create any custom user tables. You can also use Application Designer to create a page to maintain the data. If you do not create a page for your table, you have to enter the data using SQL.

Understanding Lookup Table Requirements

As you create tables to be used by the table lookup utility, consider the following restrictions:

• The names of tables created by using Application Designer have the PS_ prefix. The names of tables not created by using Application Designer must begin with the PA_TL_ prefix.

• The first column must be an effective date field. Although many tables have only one effective date (with new data always being added under the same effective date), the field is still expected.

• The second column name must be TL_SEQ_NUM. This column contains sequencing information for the table and must be a key for the table.

• The next column or columns must contain the lookup basis. One-dimensional lookups use one column for the lookup basis; two-dimensional lookups use two columns for the lookup bases.

• The final column must have the result value.

• All numerics must be set up as decimals.

Understanding Sequence Numbers

The sequence number controls the order in which the table rows are read into the program. For example, a taxable wage base table might look like this:

If instead this table uses sequence number 15 for 1931 and 10 for 1932, the table lookup may give unexpected results.

Warning! You must explicitly order your table values using the Sequence Number field. The table lookup utility relies on this sequence number and does not otherwise sort the lookup values.

Note. PeopleSoft recommends that you leave gaps between the sequence numbers. Then if it becomes necessary to insert new rows between existing rows, you do not have to alter the sequence numbers in the subsequent rows.

Setting Up Two-Dimensional Tables

A two-dimensional table has two lookup bases. You can picture it as a grid. For example, you could use the following grid to look up information based on both age and service:

You create two-dimensional tables with two columns of lookup values: lookup one and lookup two. Set up the table with all rows for the first lookup one value, all rows with the next lookup one value, and so on.

For example, a two-dimensional lookup table is shown below. Lookup one is age, and lookup two is service. Following the example shown above, the lookup one values are set up as follows: all the service values for age 55, all values for age 60, then all values for age 65. The final table would look like this:

Planning the Lookup Data

When you set up instructions for looking up data on a table, you specify an interpolation method for handling values that are not on the table. When you deal with intermediate values, the instructions depend on how you set up the data.

Suppose you look up social security retirement age based on an employee's date of birth. The following example shows a table you could use to determine SSRA when adjusting 415 limits, but not for social security calculations:

Note. For simplicity's sake, this example uses a table without the two-month increments in the SSRA.

When you enter data in the table, you can only have one birth date per row. You cannot use a range such as "1937 and earlier." However, you do not want to enter every possible birth date in the range.

Assume that your interpolation method tells the system to use the next lower value on the table for intermediate birth dates. You can also have the system use the smallest and largest values on the table as the minimum and maximum. Based on these options, you would set up the table as follows:

When you use the lower value, birth dates between January 1, 1938 and December 31, 1954 use the value for January 1, 1938.

If your interpolation method specifies that the system should use the next higher value for intermediate birth dates, your table would be as follows:

When you use the higher value, birth dates between January 1, 1938 and December 31, 1954 use the value for December 31, 1954.

Setting Up a Table Lookup Alias

To set up a table lookup alias, use the Table Lookup Alias component (TABLE_LOOKUP).

This section provides an overview of the page used to set up a table lookup alias, lists the page used to set up a table lookup alias, and discusses how to specify tables to search and return values.

Understanding the Page Used to Set Up a Table Lookup Alias

Use the Table Lookup Alias page to set up a table lookup alias for searching a table. This enables you to search the table, based on one or two lookup values, and return one or more table values.

Page Used to Set Up a Table Lookup Alias

Specifying Tables to Search and Returning Values

Access the Table Lookup Alias page (Set Up HRMS, Product Related, Pension, Variable Definitions, Table Lookup Alias, Table Lookup Alias).

Table Name

Enter the name of the table that stores the information you want to search. The table name must begin with PS_PA_TL_.

Table Name

Lookup Values

Interpolation Method

Select Higher, Linear, Lower, or Nearest as the method for handling intermediate values on a table.

For example, for the following social security retirement age table, select Lower. To look up the SSRA for someone born August 16, 1944, you would find the next lowest value on the table, January 1, 1938, and use the associated SSRA.

In another example, for the following hours-to-service table select Linear. Then 1000 hours is worth .5 years of service. If you use any other interpolation method, you probably need additional rows in the table.

Lookup Value is Below Minimum and Lookup Value is Above Maximum

Each or these group boxes offers a choice between taking the lowest (or highest) value and ending the calculation with an error. For the SSRA example, select Use Minimum Value and Use Maximum Value. For other tables, you might select Error - End Calculation.

Lookup Value Aliases

For a regular table lookup—that is, if you choose Use Alias to find the lookup values—you have to specify the lookup value aliases. To look up SSRA, the lookup basis is the employee's birth date, accessed through the database alias BIRTH_DT. The same principle applies to all table lookups that are not program generated: The lookup basis is always another alias. If you set up a one-dimensional table, you only specify a value in Lookup Value 1 Alias. If you are setting up a two-dimensional table, you also need to specify a value in Lookup Value 2 Alias.

Options

The following table compares the behavior of the two rounding settings. These examples assume that the final value has two decimal positions.