Description:

A unique constraint error on BIOTICS_USER.EO_AK3 indicates that the EO_NUM being utilized already exists for that element. To rectify it you need to update the EO Number (NEXT_NUMBER) within the NEXT_EO_NUM table.

Solution:

This stored procedure checks your NEXT_EO_NUM table's next_number values against the maximum value of eo_num for each for each record in the NEXT_EO_NUM table. If the maximum value of eo_num for an EST is less than the value in next_number, the next_number value will be updated with the max value+1. If an EST has no EOs, but has an entry in the NEXT_EO_NUM table that is not = 1, it will be updated so that it is = 1. In addition, if an EST is found to have EOs, but has no entry in the NEXT_EO_NUM table, an entry will be inserted with the maximum eo_num+1 value found for that EST. It will NOT attempt to close up any gaps in the eo_num sequence.

Install it by running the attached SQL script while logged into SQLPlus as biotics_dlink, and the execute the procedure by typing:

SET SERVEROUT ON
EXEC FIX_EO_NUM

You will get the following messages in return:

Number of ESTs out of synch and were corrected: x
Number of ESTs added to NEXT_EO_NUM table: x
Number of ESTs with no EOs updated in NEXT_EO_NUM table: x