Table Management
Payroll Interface is a table-driven export and import engine. As you set up and maintain an interface definition, you may need to work directly with various Payroll Interface tables.
Note: If using PeopleSoft Data Mover to run DELETE, IMPORT, and EXPORT scripts, use the internal name for the table, for example, PI_PARTIC. If using Microsoft SQL Query Analyzer or some other non-PeopleSoft tool to run SQL statements, use the external name for the table, for example PS_PI_PARTIC.
See PeopleTools: Data Management
Managing the Process Tables
Occasionally, you may want to delete the data in the Payroll Interface process tables to free disk space, reduce processing times, or send a full record export file to the third-party payroll system.
Warning! PeopleSoft does not support a specific purge strategy. Back up tables before you run the SQL DELETE statement.
To purge the process tables, follow these general guidelines:
Stop all data input into the PeopleSoft system.
Run the normal daily process (usually a changes-only export run).
Delete the data in the selected process tables.
Run the normal daily process again (the system will return by default to a full-records export run).
You should delete the data in the tables whenever changes are made to the export file definition (for example, changing field characteristics such as increasing or decreasing field sizes or edit masks or adding or deleting fields from the interface definition).
The PI_PARTIC and PI_PARTIC_EXPT tables can get very large. You should monitor the size of these tables on a regular basis to ensure that Payroll Interface functions properly. You may want to archive historical data in these tables.
The PI_RUN_TBL tables contains the PI_RUN_NUM field. If you purge this table, the PI run number is reset to 1. If you do not want to reset run number, do not purge the PI_RUN_TBL or the PI_RUN_PYGRP tables. When you run the Export process, the run number will be incremented to the next number.
Warning! Do not purge the PI_EMPLID_TBL if you have mapped the PeopleSoft EMPLID to the third-party payroll system employee ID.
The Payroll Interface process tables include:
AERUNCONTROL
PI_RUNCTL
PI_RUNCTL_AE
PI_RUNCTL_SID
PI_RUN_TBL
PI_RUN_PYGRP
PI_PRESELECT
PI_EMPLID_TBL
PI_PARTIC
PI_PARTIC_EXPT
PI_EXTIO
PI_PARTIC_EXTBL
PI_MESSAGE
DED_CALC
DED_LINE
DED_MESSAGE
Managing the Control Tables
Occasionally, you may want to transfer the Payroll Interface control tables (also referred to as the setup tables) from one database to another for example, when you move from a test environment to a production environment.
Warning! Never copy the process tables (PI_PARTIC, PI_PARTIC_EXPT, and so on) from a test environment to a production environment. These tables are automatically populated in the production environment.
To export tables, run this script:
SET LOG C:\TEMP\PI_EXPORT.LOG
SET OUTPUT C:\TEMP\PI_TABLES.DAT
EXPORT PI_SYSTEM_TBL
EXPORT PI_SYSTEM_STAT
EXPORT PI_SYSTEM_LANG
EXPORT PI_PS_RECORD
EXPORT PI_PSREC_FLD
EXPORT PI_PS_REC_LANG
EXPORT PI_INSTANCE_TBL
EXPORT PI_INST_VALUE
EXPORT PI_INSTANC_LANG
EXPORT PI_PROCESS_TBL
EXPORT PI_PROCESS_VAL
EXPORT PI_PROC_TB_LANG
EXPORT PI_PROC_VA_LANG
EXPORT PI_FIELD_TBL
EXPORT PI_FIELD_XLAT
EXPORT PI_FIELD_LANG
EXPORT PI_GROUP_TBL
EXPORT PI_GROUP_LANG
EXPORT PI_DEFN_FILE
EXPORT PI_DEFN_RECORD
EXPORT PI_DEFN_FIELD
EXPORT PI_DEFN_F_LANG
EXPORT PI_DEFN_R_LANG
EXPORT PI_CONFIG_TBL
EXPORT PI_CONFIG_FILE
EXPORT PI_CONFIG_LANG
EXPORT FILE_HANDLE_LNG
EXPORT FILE_HANDLE_TBL
To import tables, run this script:
SET LOG C:\TEMP\PI_IMPORT.LOG
SET INPUT C:\TEMP\PI_TABLES.DAT
REPLACE_ALL *
SET LOG C:\TEMP\PI_IMPORT.LOG
SET INPUT C:\TEMP\PI_TABLES.DAT
IMPORT * IGNORE_DUPS
REPLACE_ALL *;—Deletes all data from the PeopleSoft table and replaces it with the data in your import file.
IMPORT * IGNORE_DUPS;—Leaves all data in the PeopleSoft table and loads only new rows of data. It will not replace any data already in the table. So if you have a row that exists in both the source table and the target table, no action is taken at all, even if some of the fields on that row have changed