OSI - Stop Payment Error Message

 

Issue:

Trying to place a Stop Payment and keep getting error message: "An exception occurred while attemping to post a transaction.  Account #######, Type: CS --> ORA-00001; unique constraint (OSIBANK.PK_ACCTSTOPPMTREQ) violated.  Because of this exception the information could not be saved to the database. Click OK to clear the detail area and try again or CANCEL to close the form."    (see attached for error message example)

 

Resolution:

Forward to Scot or Roland

 

From Scot:
Going to update the nextstoppmtreqnbr in the Acct record for acct# 12345.  Somehow it didn’t update the last time an AcctStopPmtReq record was written for acct# 12345.  You have to query the Acct record to get the current nextstoppmtreqnbr.

The following is an example of the group of queries and update sql’s used to fix this issue.

SELECT ac.nextstoppmtreqnbr,ac.* from acct ac where ac.acctnbr=12345

At that point you query to see what the actual current last stoppmtreqnbr for that acctnbr is

select * from acctstoppmtreq ar where ar.acctnbr=12345order by ar.stoppmtreqnbr desc

At this point what probably exists is the current stoppmtreqnbr is equal to or > than the nextstoppmtreqnbr in the acct record.

The actual fix is to take the current number which in this case was 1165 and add 1 to it and update the Acct record. 

update acct ac set ac.nextstoppmtreqnbr=1166,ac.datelastmaint=sysdate where ac.acctnbr=12345