Overview of 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.

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.

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 Understanding the Primary Job Selection.

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.

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.