Export and Import Testing

This topic discusses:

  • Testing exports and imports.

  • Successful exports.

  • Unsuccessful exports.

  • Detecting errors.

To test export and import interface definitions, run the Payroll Interface Export and Import processes until no errors occur and the data results are correct. Import interface definitions are usually set up and tested after the export interface definition is verified.

Testing Exports

To test an export interface definition the first time:

  1. Delete the contents of the Payroll Interface tables shown below.

  2. (optional) Run the Calculate Deductions and Confirmation processes.

  3. Run a full-records Export process.

    Use the Pay Run ID prior to the pay run that is to be used for production.

  4. When the Export process completes, check for errors.

Delete the content of these tables before running the Export process the first time:

  • PI_RUNCTL

  • PI_RUN_TBL

  • PI_RUN_PYGRP

  • PI_PARTIC

  • PI_PARTIC_EXPT

  • PI_EXTIO

  • PI_PARTIC_EXTBL

  • PI_MESSAGE

  • DED_LINE

  • DED_CALC

  • DED_MESSAGE

Testing Imports

To test the import interface definition the first time:

  1. Delete the contents of the Payroll Interface tables shown in the succeeding table.

  2. Obtain a copy of the import file that is produced by the third-party payroll system.

  3. Run the Import process.

    Use the pay run ID for the associated Export process.

  4. When the Import process finishes, check for errors.

Delete the content of these tables before running the Import process the first time:

  • PI_RUNCTL

  • PI_RUN_TBL

  • PI_RUN_PYGRP

  • PI_MESSAGE

  • PI_EXTIO

  • PI_CHECK

  • PI_EARN_DETAIL

  • PI_DED_DETAIL

  • PI_TAX_DETAIL

  • PI_EARN_BAL

  • PI_DED_BAL

  • PI_TAX_BAL

You can identify a successful export run in the following ways:

  • Changes that occurred during the pay period to PeopleSoft HCM mapped-data elements are exported and written to a file or table.

  • The compare process identifies correct changes.

  • The implementation of additional processing logic (beyond the delivered supported application functionality) works correctly, including:

    • Modifying the delivered Payroll Interface COBOL, for example, creating a new process ID.

    • Processing files using Application Engine, Structured Query Report (SQR), or other programming languages, for example, reformatting a record layout.

    • Writing PeopleCode, for example, to force a new set of export records given a specific job action (that is, rehire or transfer).

    • Creating new views, incorporating any American National Standards Institute (ANSI) SQL statement. For example, join the Department table with Job to extract department GL expense codes or managers, develop specific WHERE clauses, and perform Logic/Arithmetic/SQL functions.

  • The contents of the export file (or table) are imported into the third-party payroll system and the data elements and transactions have been processed accurately per the third-party payroll system's import processing requirements. These types of transactions involve updating, inserting, or deleting employee data.

For debugging purposes, sometimes it may be useful to run the PIPINRUN process directly. First, use the Import/Export Payroll Data component, Import/Export Payroll Data run control page to enter and save the Run Control ID and Pay Run ID. Then, instead of submitting the request to the process scheduler using the RUN button, run PIPINRUN at the command line of the process server or any connected computer with fully compiled current COBOL. During the run, PIPINRUN calls PIPCMPAR and PIPXPORT, the results are written to the PI_EXTIO table, and then processing stops. To rerun the PIPINRUN process, the contents of the PI_EXTIO table as well as the PI_RUNCTL table must be deleted.

Example 1

Export Result: PI_PARTIC and PI_PARTIC_EXPT are both empty.

If the failure of the export is not obvious, for example, the process terminates or a Payroll Interface error message such as MISSING RUN CONTROL is displayed, then analyze the employees. The Pay Run ID determines the pay calendar's company and pay group. PIPCMPAR selects against JOB.COMPANY and JOB.PAYGROUP to retrieve the appropriate employees. The DMS statement used in PIPCMPAR.DMS to select these employees performs an insert into PI_PARTIC. Therefore, if PI_PARTIC is empty, it is because the PIPCMPAR SQL that is trying to select employees and do the insert failed.

Most likely, an interface error message is in PI_MESSAGE stating that there were no records to export. However, to determine why no employees were selected, you will need to analyze the pay run ID, the associated pay calendar, the Pay Group table, and the corresponding employee job records.

Example 2

Export Result: PI_PARTIC is loaded and PI_PARTIC_EXPT is empty.

If the PI_PARTIC table is loaded, then the Export process select phase was successful (which means that the pay run ID, the pay calendar, the Pay Group table, and the Job records were set up correctly). If the process did not end, but the PI_PARTIC_EXPT table is empty, then the Export process compare phase was not successful. Do an inquiry on the interface messages (PI_MESSAGE) to determine why the process failed. When all of the issues are fixed, you should be able to reprocess the export.

Example 3

Export Result: PI_PARTIC is loaded, PI_PARTIC_EXPT is loaded, PI_EXTIO is loaded (when it should be empty) and no export file is created.

If PI_EXTIO is loaded, the Export process select, compare, and export phases finished successfully. However, the Application Engine's file I/O process that writes the contents of PI_EXTIO to the export file (and then deletes PI_EXTIO) did not finish successfully. Make sure the file IDs and file handles are set up correctly. If they are, then analyze the Application Engine programs PI_INRUN and PI_EXTFL.

Note: This does not apply if you are exporting data to a table.

Example 4

Export Result: PI_PARTIC is loaded, PI_PARTIC_EXPT is loaded, PI_EXTIO is empty, and no export file is created.

This scenario could mean that the Export process was successful and no changes exist. It also could mean that the Export process was successful and the external file was created, but the file is not located where you expected it.

If known data changes have occurred and the file cannot be found, then evaluate the PSPSEQIO COBOL program for issues surrounding the insert into the PI_EXTIO table. It would be helpful to run the COBOL process separately to insure that PI_EXTIO is being loaded at the end of the compare and export phases. At that point, the analysis should shift to the Application Engine's write to the external file.

Note: This does not apply if you are exporting data to a table.

To determine whether an error has occurred after running the Calculate Deductions or Deduction Confirmation processes, first refer to the Review Deductions and Review Error Messages components.

See Reviewing Deductions.

To determine whether an error has occurred after running the Export or Import processes, first refer to the Error Messages (PI_MESSAGES) component and various reporting components.

See Reviewing Exports or Imports.

Error Messages

If the explanation in the Error Message component is not clear, you can use the message number to determine where in the COBOL routine the problem occurred. For example, suppose message 004030 returns the following information:

The Dedcalc process was not confirmed. This data is not available until the confirm is processed. The message data below identifies 2 field values. 1. Company 2. Paygroup.

To locate the problem:

  1. Search for 004030 in PSCPYMSG.CBL to determine the corresponding 88 level name for MSGID.

    In this case, it is MSGID-DEDCALC-NOT-CONFIRMED.

  2. Using a search tool, do a search against PI*.CBL to look for all the occurrences of MSGID-DEDCALC-NOT-CONFIRMED.

    The result is in PIPINRUN.CBL, paragraph MM100-CONFIRM-DEDCALC.

The location of the error in the COBOL routine may point to a SQL statement or array search that may give additional clues to the problem.

Error: Dynamic SQL Failure Due to Invalid PS Table Setup

If you use PeopleSoft Application Designer to change any of the tables or fields that are set up in the PS Tables (PS_TABLES) component, you must make sure that these changes are reflected in the PS Tables component as well. If you do not, the Export process will end when attempting to run the dynamically created SQL statement. If it is not obvious which table or field is causing the error, you can run the Export process from a DOS prompt.

To run PIPINRUN from the DOS prompt:

  1. From the DOS prompt, run PIPINRUN with the SQL Trace turned on.

  2. At the SQL Trace prompt, enter 191.

    A trace file is written to C:\TEMP\PS\<dbname>\COBSQL_. The SQL causing the process to end should be toward the end of the trace file with a SQL statement name such as S_JOB, S_GENL_DEDUCTION, and so on.

  3. Copy the SQL to the database's SQL Inquiry tool and run it using the parameters listed below the statement in the trace file.

    The SQL Inquiry tool should point to the problem field.

See PeopleTools: Application Designer Developer’s Guide

Error: Maximum Number of Export Records Exceeded for EMPLID

PIEXPT-COUNT-MAX represents the maximum number of records that can be generated for an EMPLID. During the Export process, this maximum number can be reached when you are processing files that comprise a high number of rows in PS_JOB.

PIEXPT-COUNT-MAX is set to 200 by default. If you want to increase this number, you need to modify to the COBOL in two places:

  • In PICIEXPT.CBL, insert the desired number where you see 200 in bold.

    88 PIEXPT-COUNT-MAX VALUE 200

  • In PIPIEXPT.CBL, insert the desired number where you see 200 in bold.

    02 PIEXPT-DATA OCCURS 200

Be sure to recompile the programs when you are done.

You may encounter similar error messages for "Maximum number of export records exceeded." Again, just follow the code line through and increase the 88 VALUE and the corresponding OCCURS values.