Resolving unique constraint violations in PeopleSoft processes

Anyonewho has supported PeopleSoft applications long enough is all too familiar withthe dreaded ORA-0805 error, a unique constraint violation. That error mostfrequently occurs when a process tries to insert a row into a table with thesame key values as a row that is already there, although it can also happen onan update statement. 

Fortunately, this is oneproblem that can be fixed rather rapidly if you're adept at trouble-shooting. Isee it most often on the voucher posting process, which is an App Engine, soI'll approach the problem from that standpoint, but a lot of this holds truefor SQRs, too, although it's a little trickier because data might not have beencommitted at any point during the process.

Let's assume that your processhas failed. The log file should display the entire insert statement; if you'vegot AE trace enabled, you can also get it from the trace file. Copy thatstatement and run it in a SQL tool. I use TOAD, because I can have numerouswindows open at a time and statements can be recalled simply by hitting F8. Runthe statement in the SQL tool and if you're lucky, you'll get the same error.That's not always the case, though, if statements that preceded the insert wererolled back after the error occurred. In that case, you'll have to go to thetrace file, find the last commit that occurred prior to the error, and run allof the statements up to and including the one that fails. Be sure to keep trackof the statements that insert or update data, though, because you'll want toput things back the way they were after you've got the problem resolved andbefore you restart the AE process. If AE trace was not enabled, turn it on inthe Process Scheduler config file and restart the failed process. It will failagain, but now you'll at least have a trace file. Be sure to disable trace,though, or pretty soon you'll have space problems on the Process Scheduler box.

Once you've replicated theerror, it's a simple matter of finding the bad row and deleting it from what isusually an AE temp table. How do you find it? First, you need to determine thekeys of the table into which the row is being inserted. Second, you need toanalyze the insert statement to determine the source of the bad row. In thecase of the voucher post process, it's usually VCHR_TEMP_LNx, where x is theinstance number of the table being used. Third, you need to write a query tofind the row in the source table that is a duplicate of a row in the targettable. You do that by querying the two tables for rows with duplicate keyvalues. Your SQL statement will end up looking something like this:

SELECT * FROM PS_VCHR_TEMP_LN11A
WHERE EXISTS
(SELECT 'X' FROMPS_VCHR_ACCTG_LINE B
WHEREA.BUSINESS_UNIT=B.BUSINESS_UNIT
AND A.VOUCHER_ID=B.VOUCHER_ID
AND A.UNPOST_SEQ=B.UNPOST_SEQ
ANDA.APPL_JRNL_ID=B.APPL_JRNL_ID
ANDA.DST_ACCT_TYPE=B.DST_ACCT_TYPE
AND A.PYMNT_CNT=B.PYMNT_CNT
ANDA.VOUCHER_LINE_NUM=B.VOUCHER_LINE_NUM
ANDA.DISTRIB_LINE_NUM=B.DISTRIB_LINE_NUM
ANDA.DST_ACCT_TYPE=B.DST_ACCT_TYPE
ANDA.CF_BAL_LINE_NUM=B.CF_BAL_LINE_NUM
AND A.LEDGER=B.LEDGER)

If your query returns a row orrows, than you need to query the target table and make absolutely certain thatthe incoming row is a duplicate. Once that has been ascertained, you can safelydelete the row from the temp table. Your work isn't done yet, though, becauseall you've done so far is solve a problem that occurred with this particularprocess. To prevent the problem from occurring in the future, you need to goback to the source record and update a flag there. In the case of voucherposting, that means updating VOUCHER.POST_STATUS_AP to a value of "P"(an incorrect value of "U" is what caused the problem to begin with).You've already written a query to find the offending row, so you can fix theproblem at its source by adding a few lines to that query:

UPDATE PS_VOUCHER SETPOST_STATUS_AP='P' WHERE POST_STATUS_AP='U' AND (BUSINESS_UNIT,VOUCHER_ID) IN
(SELECTBUSINESS_UNIT,VOUCHER_ID FROM PS_VCHR_TEMP_LN11 A
WHERE EXISTS
(SELECT 'X' FROMPS_VCHR_ACCTG_LINE B
WHEREA.BUSINESS_UNIT=B.BUSINESS_UNIT
AND A.VOUCHER_ID=B.VOUCHER_ID
AND A.UNPOST_SEQ=B.UNPOST_SEQ
ANDA.APPL_JRNL_ID=B.APPL_JRNL_ID
ANDA.DST_ACCT_TYPE=B.DST_ACCT_TYPE
AND A.PYMNT_CNT=B.PYMNT_CNT
ANDA.VOUCHER_LINE_NUM=B.VOUCHER_LINE_NUM
ANDA.DISTRIB_LINE_NUM=B.DISTRIB_LINE_NUM
ANDA.DST_ACCT_TYPE=B.DST_ACCT_TYPE
ANDA.CF_BAL_LINE_NUM=B.CF_BAL_LINE_NUM
AND A.LEDGER=B.LEDGER))

Once you've got the problemresolved at its source, you can delete the record from the temp table andrestart the process, which should then complete successfully:

DELETE FROM PS_VCHR_TEMP_LN11 A
WHERE EXISTS
(SELECT 'X' FROMPS_VCHR_ACCTG_LINE B
WHEREA.BUSINESS_UNIT=B.BUSINESS_UNIT
AND A.VOUCHER_ID=B.VOUCHER_ID
AND A.UNPOST_SEQ=B.UNPOST_SEQ
ANDA.APPL_JRNL_ID=B.APPL_JRNL_ID
ANDA.DST_ACCT_TYPE=B.DST_ACCT_TYPE
AND A.PYMNT_CNT=B.PYMNT_CNT
AND A.VOUCHER_LINE_NUM=B.VOUCHER_LINE_NUM
ANDA.DISTRIB_LINE_NUM=B.DISTRIB_LINE_NUM
ANDA.DST_ACCT_TYPE=B.DST_ACCT_TYPE
ANDA.CF_BAL_LINE_NUM=B.CF_BAL_LINE_NUM
AND A.LEDGER=B.LEDGER)

There are also occasions inwhich there is no duplicate row in the target table and your query does notreturn any rows. In that case, the problem is usually the result of a bad joinbetween source tables that creates more than one row to insert. It could alsobe the result of a "GROUP BY" statement that summarizes data on thekey fields and one or two non-key fields, and there are two or more rows withdifferent non-key values but identical key values. In that case, you need tofigure out why one is different and correct it. Don't try adding that field asa key, though- you might solve today's problems, but you'll just be creatingheadaches further down the road.