VAR_DATA_SEQ Value not Always Incremented When Creating New Variable Data for Checklist or Communication (Doc ID 660150.1)

Last updated on AUGUST 15, 2017

Applies to:

PeopleSoft Enterprise CS Campus Community - Version 9 to 9 [Release 9]
Information in this document applies to any platform.
This document was previously published as Customer Connection Solution 201062214

Symptoms

When assigning a new checklist to a student, the associated variable data is not always being properly created.  For example, a student currently contains two rows of data in the table PS_VAR_DATA_FINA for aid years 2006 and 2007.  When attempting to add a checklist code for aid year 2008, the system appears to set the value of the VAR_DATA_SEQ field to the same value associated with the 2007 data row.

Investigation into the problem determined that the problem exists within the PeopleCode program FUNCLIB_CS.ADMIN_FUNCTION.FieldFormula, function CheckVarData.  This function contains a SQL object that selects all of the rows from the PS_VAR_DATA_XXXX table for a given Common ID value.  The code uses the data from the SQL object to check if existing data matches the variable data being used to create the checklist .  However, it assumed the data will be sort by VAR_DATA_SEQ, in ascending order.  If the data is retrieved in any other order, the last VAR_DATA_SEQ value may not necessarily be the maximum value on the table.  When it is determined that a new row needs to be inserted, the last VAR_DATA_SEQ value read is used to increment the field for the new row.  This new value will match the value of an existing row on the table.  As a result, when the RECORD.Insert() statement is executed, the insert does occur due to a duplicate key error, which is never displayed.

Sample Error:

ORA-00001: unique constraint (SYSADM.PS_VAR_DATA_SENR) violated

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms