VAR_DATA_SEQ Value not Always Incremented When Creating New Variable Data for Checklist or Communication
Last updated on NOVEMBER 01, 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
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.
ORA-00001: unique constraint (SYSADM.PS_VAR_DATA_SENR) violated
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