PeopleSoft Integration

RAS integrates with the PeopleSoft Financial system to transmit data and budget info about awarded grants. This page details that integration at a high level. More detailed specs can be found at the RAS-PeopleSoft-Axiom Integration folder on Box at https://tufts.app.box.com/folder/16439032930.

Data Flow

Data about awarded grants is entered by OVPR's Post-Award team in the RAS Award Module (step 1, below) and Award Budget Tool (step 2). The final step of this process is for the Post-Award Specialists to approve the final budgets, which puts the budget records in the "To Be Posted" status (step 3).

Every five minutes, the RAS-PeopleSoft integration job runs and scans for all Award Budgets in the "To Be Posted" state. For each budget to be posted, the job assembles all of the necessary data from the Award and Budget tables, and creates a new transaction in a set of tables within the RAS database which are visible to PeopleSoft (step 4).

The PeopleSoft Financial system monitors these tables for new transactions. When a transaction is found, PeopleSoft reads the data and creates or updates the Funding Source, Grant, and Journal according to the given data (step 5). PeopleSoft then updates the transaction in the shared tables with a success or error status (step 6) and sends email notifications to the user informing them of the success or failure of the operations (step 7).

Technical Components

The core of the RAS-PS integration is a SQL script which is run every five minutes via a cron job. (See the Automated Tasks section of the Operational Environment page.) This script contains all of the logic for pulling award/budget data from the RAS database tables and populating the shared PS tables with the appropriately. It also handles comparison of current budget data with the budget history so that the PeopleSoft totals can be adjusted by the correct amount. This is important because, for security reasons, RAS does not have direct visibility into the PeopleSoft transactions. Instead it uses the budget history within the Award Budget Tool to determine how much the account should be changed by. (Example: If budget 1.0 allocates $1000, and budget 2.0 increases that amount to $1500, RAS compares the two and sends PS the net change of $500.)

The primary database tables used in the integration are TUPS_FUNDING_SOURCE, TUPS_GRANT, and TUPS_JOURNAL. These are the tables which PeopleSoft reads to create or update the Funding Source, Grant, and Budget Journals.

The schemas for these tables are below. More detailed information on the fields and specifications for the integration can be found on Box at: https://tufts.app.box.com/folder/22708717333

TUPS_FUNDING_SOURCE

TUPS_GRANT

TUPS_JOURNAL

Troubleshooting

When any error is encountered by the PS Sync SQL script, it will be recorded in the log file and an email notification will be sent to the RAS support email list with the subject "RAS/PS Integration Error Detected".

Grant was not created

The most common error to occur in the PS Sync script is: "ERROR: Grant for award XXXXXX-0001 was not created". 

This is normal, and usually means that a user submitted a budget without first filling out all of the required data on the associated RAS award. The built-in validations in the award module are designed to ensure that all of the necessary data is filled out, but older awards in the system may have been created before some fields were required, and thus those validations have never been run. Since the Award Budget Tool is a standalone application, it can't verify this directly.

When encountering this error, the associated budget will be placed into the "Error in Posting" state, and it is up to the user to go back and fix the award record and resubmit the budget.

Other SQL Errors

If the award or budget data is malformed in some unexpected way, the PS Sync script may fail with a SQL error of some kind. 

This usually indicates that one of the main INSERT statements for either the TUPS_FUNDING_SOURCE or TUPS_GRANT tables encountered an issue with the award in question. Determining the exact issue will require some investigation, but starting with the error and line number from the message and examining the SQL script is a good starting point.

A useful troubleshooting technique is to copy the internal 'select' within those insert statements to see exactly what data the script is trying to process. This can reveal if required fields are blank, if multiple rows are returned in a case where only a single row should be present, etc. If the select statement still fails, it can be helpful to start commenting out fields and/or joins to determine which part of the select is causing the issues.

Integration ERROR Report

 If the PS Sync script successfully passes the data to PeopleSoft, an operation may still fail on the PS side. If this happens, PeopleSoft will generate an ERROR report which is emailed to the grant submitter as well as the RAS/PS support teams (see below for an example).

The error message specifies which grant/award number is at issue, and usually spells out the cause of the error in (relatively) plain English: Grant is inactive, Dept ID is invalid, etc. Often, these errors are the result of a data entry issue or logical mistake in setting up the award/budget/grant and can be corrected by the end user. 

There may be cases where the error is less clear, or where data in PeopleSoft doesn't appear to match what the user entered in RAS. In these cases, a comparison of the underlying RAS and PeopleSoft data may be necessary to determine what happened in the interface and what the problem is. Currently, no single person has a full view of both systems – the RAS team cannot see the PeopleSoft data, and the PS team cannot see internal RAS data. So working with the finance team and providing them with a view of what data was passed over to PeopleSoft (via the TUPS_FUNDING_SOURCE and TUPS_GRANT tables) will help to assemble a complete picture of what happened and where the error originated.