Setting Up the PS Tables

To set up the PS Tables, use the PS Tables (PS_TABLES) component.

Page Name

Definition Name

Usage

PS Tables Page

PI_PSREC_PNL

Select PeopleSoft tables and fields that contain employee payroll-related data. Data is extracted from these tables and fields during the Export process.

PeopleSoft Field Inquiry

PI_PSFLD_INQ

View attributes for a selected field.

Use the PS Tables component to identify the PeopleSoft HCM tables and fields that contain the data that will be exported to the third-party payroll system or used during the Export process.

You can export data from any table or view in PeopleSoft HCM, as long as the record includes the PeopleSoft EMPLID field. The system uses the parameters on the PS Tables page to construct the SQL select statements.

Note: Any field that is identified in the PS Tables component can be added to the Field Definition component. Any field that is defined in the Field Definition Table component can be added to the Definition Table component.

Use the PS Tables (PeopleSoft Tables) page (PI_PSREC_PNL) to select PeopleSoft tables and fields that contain employee payroll-related data.

Navigation

Set Up HCM > Product Related > Payroll Interface > Interface Controls > PS Tables > PS Tables

Image: PS Tables page

This example illustrates the fields and controls on the PS Tables page.

PS Tables page

Data is extracted from these tables and fields during the Export process.

Field or Control

Definition
System ID

The system ID for the third-party payroll system.
Process Type

Select to indicate which Payroll Interface process to perform with the table or view.
Where Clause Type

Select to indicate which type of SQL Where clause to construct when retrieving the data from the database:

  • Empl ID: Employee ID only.

  • ID and Active Effdt: Employee ID and the active effective date as of the pay end date.

  • ID, Active Effdt, Active EffSeq: Employee ID, the active effective date, and the latest effective sequence as of the pay end date.

  • ID, All Effdts, Active EffSeq: Employee ID, all effective-dated rows that fall between the pay-begin date, the pay-end date (non-retroactive processing), and the latest effective sequence for each effective date.

  • ID, All Effdts: Employee ID and all the effective-dated rows that fall between the pay-begin date and the pay-end date (non-retroactive processing).

  • ID, Effdt=Pay End Dt: Employee ID and effective date equal the pay end date.

To become more familiar with the differences between these Where clause types, try them out using PeopleSoft Query.

See PeopleTools: PeopleSoft Query

Note: If you select Import in theProcess Type field, this field is not available.
Paygroup Company in Where

Select to indicate whether the Where clause for retrieving data from this PeopleSoft table should include a test on the Company field. When this check box is selected, the system compares the Company field in the record against the Company field in the employee's job data.

Select this check box when an employee could have more than one entry in the PeopleSoft table that could be uniquely identified by the Company field. An example of this is the employee's tax data.
Paygroup Country in Where

Select to indicate whether the Where clause for retrieving data for this PeopleSoft table should include a test on the Country field. When this check box is selected, the system compares the Country field in the record against the Country field from the company/pay group to which the employee belongs. The company/pay group is based on the employee's job data.

Select this check box when an employee could have more than one entry in the PeopleSoft table that could be uniquely identified by the Country field. An example of this is the employee's national ID data.
Ded Calc Data (deduction calculation data)

Select to indicate that one or more fields listed for this table or view comes from the PI_DED_CALC table. When you run the Export process with a run control defined to exclude DedCalc, the system excludes any record for which this check box is selected.

When the PS record table name is PI_DED_CALC, this check box is always selected.
Data Required

Select to indicate that a row of data must be returned from the database for this table, but not that every field that is defined for this record must contain data.

If a row of data is not returned for this record, the system issues an error message, and no data for the employee is written to the export file.
Job Data

Select if the record contains COMPANY, PAYGROUP, or ACTION.

If the PS record table name is JOB, this check box is always selected.

Field Details

Use the Field Details group box to define field attributes.

Note: You should enter Order by (sequence) values for all key fields. The EFFDT and EFFSEQ fields must be in descending order (Collate Order Desc). All other fields should be in ascending order (Collate Order Asc).

Field or Control

Definition
Order by

Select to indicate the sequence in which the field should be processed. A number from zero (blank) to 99 represents the sequence of the field in the ORDER BY clause of the SQL select statement.
Collate Order

This field is applicable only when the value for the Order by field is greater than zero. Select to indicate the sequence order of the field in the ORDER BY clause of the SQL select statement. (If the value of theOrder by field is zero or not specified, the collate order is none by default.)

Asc: Ascending order. Sequences data from lowest to highest. This is the default value for all fields.

Desc: Descending order. Sequences data from highest to lowest.

Note: The EFFDT and EFFSEQ fields must be in descending order.

See Working with Key Fields.
Key Field

This check box is automatically selected if the field is defined in PeopleSoft Application Designer as a key for a PS record.

You must manually select this check box for views.
Effective Date

Only one field can be the Effective Date field. The system uses theEffective Date field name in constructing the Where clause of the SQL select statement when the SELECT_WHERE_TYPE includes the effective date.

The Effective Date field for the DED_CALC Table isPay End Date.
Effective Sequence

Only one field can be marked as the Effective Date sequence number. The system uses the effective date sequence number in constructing the Where clause of the SQL select statement when the SELECT_WHERE_TYPE includes the Effective Date sequence field.

This field is used with the JOB record and any other records with both an effective date and effective sequence.
Field Attribute

Select to view the PeopleSoft Field Inquiry secondary page.