Using Database Aliases

This chapter provides an overview of database aliases and discusses how to:

• Set up a database alias.

• Use predefined database aliases.

Understanding Database Aliases

Much of the information used by the pension calculation is in your PeopleSoft Human Resources database. To reference information, you need to identify the database table and field where it is stored.

The database alias utility enables you to assign a name to a field. You can then use this user-assigned name, or alias, whenever you want to reference the information in the field.

You can set up database aliases to fields in an employee's personal data record, job data record, or any other record that is appropriately keyed. A database alias must be keyed by one or more of these fields: employee ID, effective date, and plan. When a field is keyed by plan, the system recalculates the field value as you calculate each plan.

PeopleSoft delivers a number of predefined aliases for fields that most customers need to reference. PeopleSoft also delivers a number of special database aliases to reference data not normally available but often used by pension plans. For example, there are several aliases to reference calculation-specific information, such as the event date and the benefit commencement date. There are also some calculated database aliases, such as social security retirement age (SSRA), which varies based on employee birth date.

Setting Up a Database Alias

To set up a database alias, use the Database Alias (ALIAS_PROCESSING) component.

This section lists the page used to set up a database alias and discusses how to create a database alias.

Page Used to Set Up a Database Alias

Creating a Database Alias

The database alias utility can fetch a value from any appropriately keyed table—that is, a table that is keyed by one or more of these fields: employee ID, effective date, and plan.

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

Database Table Alias & Field

Warning! The system does not prevent you from referencing tables with keys other than those the database alias supports. Be diligent about analyzing the keys for an alias field. If you reference a table that has additional keys, the alias can produce unpredictable results.

Overview of Alias Processing

This section provides an overview of alias processing and discusses how to:

• Assign an alias to the JOB table.

• Use database aliases with multiple jobs.

• Use aliases when tables have extended key structure.

• Resolve an alias when no data exists.

Understanding Alias Processing

In most cases, the system resolves an alias by looking up data on an appropriately keyed table. However, the processing is more involved when you use an alias:

• With the JOB table.

• With multiple jobs.

• When tables have extended key structure.

• When no data exists.

These situations are discussed in the following topics.

Assigning an Alias to the JOB Table

An important exception to the database alias key restrictions is the JOB table, which is keyed by employee ID, effective date, job number (Rcd#), and sequence number.

If an employee has multiple job records in a single job environment, a lookup alias finds the value from the first job record. All other job records are ignored, except in the case of multiple jobs processing, when the single job environment has been cleared.

Because the JOB table uses sequence number as an additional key field, you can enter multiple personnel actions on the same day, but specify an order for the actions. When you create an alias for a field on the JOB table, the system automatically uses the row with the last sequence number. This means the alias uses the final value for that date.

For example, if Fred receives a regular pay increase and a merit increase during his review, an alias to his compensation rate finds the final rate, after both increases have been applied.

Using Database Aliases with Multiple Jobs

When you use multiple jobs processing, database aliases are assigned according to the primary job record. For example, if you reference an employee's pay rate, the pay rate from the primary job record is returned.

See Defining the Primary Job.

Multiple Jobs Considerations for Database Table Alias and Field

To access the service function result status of accruing or not accruing:

1. Select People Soft Table.

2. Enter PA_PEN_SVC_STAT in the Record field.

3. Enter Status in the Field Name field.

4. Select the Associated Plan and the Service Function Result in the Multiple Jobs group box.

Using Aliases When Tables Have Extended Key Structure

Normally, you only use database aliases when a field is on a table that is keyed by some combination of employee ID, effective date, and plan. If there are additional keys, you would not normally be able to use a database alias. You've already looked at one exception to this rule, the JOB table. There is also a workaround for getting to other inappropriately keyed tables.

When a table has another key structure, you can create an appropriately keyed view with the needed information. For example, the benefit program participation table, BEN_PROG_PARTIC, is keyed by employee ID, record number, COBRA event ID, and effective date.

To create an alias to the BENEFIT_PROGRAM field on this table, you create a view containing only three fields: the allowable keys, EMPLID and EFFDT, and the field you need to access, BENEFIT_PROGRAM.

The select statement for your view does the work of getting the right row when there are multiple rows with the same EMPLID and EFFDT—in this case, by only selecting records where the employee record number is zero and the COBRA event ID is blank.

The SQL statement in this example is:

SELECT EMPLID,
                
EFFDT,
                
BENEFIT_PROGRAM
                
FROM PS_BEN_PROG_PARTIC
                
WHERE EMPL_RCD#=0
                
AND COBRA_EVENT_ID = ' '

Now, to set up an alias for this field, you reference the view instead of the original table.

Resolving an Alias When No Data Exists

The PERSON, PERSON_NAME, PERSONAL_DT_FST, and JOB tables always have rows for every employee who has completed the hire process. Other tables, however, may not contain rows for all your employees. For example, if you create a custom table to record effective-dated union code information, employees who have never belonged to a union do not have a row on the table.

If you create an alias to such a table and the system can't find a row for a particular employee, the alias takes a default value, depending on the field type:

To prevent an error in the case of a date field, you can create rows for all employees. You might consider creating a workflow process that creates a row when an employee is first hired. For example, if you have a custom table that tracks employees' leave statuses, an employee that has never been on leave does not have any entries in this table. You could use PeopleSoft Workflow to create a row when the employee is hired. You would probably use the hire date in this particular table.

Using Predefined Database Aliases

Most pension plans need certain common aliases, such as employee date of birth. The following table shows the database aliases that are supplied with the system.

Lookup Aliases

The following aliases have no special processing logic; they simply reference the value in the indicated field.

Calculation Input Aliases

The following aliases are specially configured to reference calculation parameters, rather than employee data.

Calculated Aliases

The following aliases provide enhanced access to data through special calculation logic.

Using Beneficiary Aliases

Pension Administration supports non-spouse beneficiaries. You record non-spouse beneficiaries on the Plan Beneficiaries page. If you do not record a beneficiary, the system assumes that the spouse is the beneficiary.

The three beneficiary aliases incorporate special corporation logic to determine who the beneficiary is, then to look up specific information about that beneficiary: birth date, relationship to the employee, and gender. The beneficiary information ultimately comes from the dependent and beneficiary table (DEPENDENT_BENEF).

If there is no beneficiary or spouse on record, these aliases do not have values.

See Also

Maintaining Pension Beneficiary Data

Using the Plan Eligibility Alias

The plan eligibility function result produces a single eligibility status: Eligible, Ineligible, or Previously Eligible. However, the PLAN_ELIG alias accesses the effective-dated eligibility history produced during eligibility calculations. Referencing this history enables you to set up different processing for employees during periods of ineligibility (for example, interest-only processing of cash balance accounts).

Associated with this alias are two delivered group custom statements:

You do not explicitly use the PLAN_ELIG alias or either of the custom statements; instead, you use a shortcut for applying it. When you set up function results, you can incorporate eligibility criteria by choosing whether a definition applies to eligible periods, ineligible periods, or both.

When you select Eligible or Ineligible, the system automatically applies the appropriate custom statement to any existing grouping criteria.

See Also

Setting Up Function Results

Using QDRO Aliases

When you run a calculation for a QDRO alternate payee, QDRO_AMT is the amount recorded for that person under the specified plan.

When you run a calculation for an employee, QDRO_AMT is the sum of all amounts recorded against that person under the specified plan.

For example, John has two ex-spouses. Sophia is entitled to 2,000 USD of his benefit from Plan A; Vanessa is entitled to 1,000 USD from plan A and 500 USD from plan B. The QDRO_AMT has the following values, depending on whose calculation is running and which plan is being processed:

The QDRO_FLAG alias returns the COURT_DOC_ID.

The QDRO_EE_BD alias is only valid when you run a calculation for a QDRO alternate payee. It provides the birth date of the original employee. For example, during calculations for Sophia and Vanessa, this alias would provide John's birth date. During calculations for John, this alias does not have any value.

Using the Social Security Alias

The alias SSR_DT calculates, rather than looks up, an employee's social security normal retirement date based on the employee's birth date and the following social security normal retirement age table: