Anyone who has supported PeopleSoft applications long enough is all too familiar with the dreaded ORA-0805 error, a unique constraint violation. That error most frequently occurs when a process tries to insert a row into a table with the same key values as a row that is already there, although it can also happen on an update statement.
Fortunately, this is one problem that can be fixed rather rapidly if you're adept at trouble-shooting. I see it most often on the voucher posting process, which is an App Engine, so I'll approach the problem from that standpoint, but a lot of this holds true for SQRs, too, although it's a little trickier because data might not have been committed at any point during the process.
Let's assume that your process has failed. The log file should display the entire insert statement; if you've got AE trace enabled, you can also get it from the trace file. Copy that statement and run it in a SQL tool. I use TOAD, because I can have numerous windows open at a time and statements can be recalled simply by hitting F8. Run the 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 were rolled back after the error occurred. In that case, you'll have to go to the trace file, find the last commit that occurred prior to the error, and run all of the statements up to and including the one that fails. Be sure to keep track of the statements that insert or update data, though, because you'll want to put things back the way they were after you've got the problem resolved and before you restart the AE process. If AE trace was not enabled, turn it on in the Process Scheduler config file and restart the failed process. It will fail again, 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 the error, it's a simple matter of finding the bad row and deleting it from what is usually an AE temp table. How do you find it? First, you need to determine the keys of the table into which the row is being inserted. Second, you need to analyze the insert statement to determine the source of the bad row. In the case of the voucher post process, it's usually VCHR_TEMP_LNx, where x is the instance number of the table being used. Third, you need to write a query to find the row in the source table that is a duplicate of a row in the target table. You do that by querying the two tables for rows with duplicate key values. Your SQL statement will end up looking something like this:
SELECT * FROM PS_VCHR_TEMP_LN11 A
WHERE EXISTS
(SELECT 'X' FROM PS_VCHR_ACCTG_LINE B
WHERE A.BUSINESS_UNIT=B.BUSINESS_UNIT
AND A.VOUCHER_ID=B.VOUCHER_ID
AND A.UNPOST_SEQ=B.UNPOST_SEQ
AND A.APPL_JRNL_ID=B.APPL_JRNL_ID
AND A.DST_ACCT_TYPE=B.DST_ACCT_TYPE
AND A.PYMNT_CNT=B.PYMNT_CNT
AND A.VOUCHER_LINE_NUM=B.VOUCHER_LINE_NUM
AND A.DISTRIB_LINE_NUM=B.DISTRIB_LINE_NUM
AND A.DST_ACCT_TYPE=B.DST_ACCT_TYPE
AND A.CF_BAL_LINE_NUM=B.CF_BAL_LINE_NUM
AND A.LEDGER=B.LEDGER)
If your query returns a row or rows, than you need to query the target table and make absolutely certain that the incoming row is a duplicate. Once that has been ascertained, you can safely delete the row from the temp table. Your work isn't done yet, though, because all you've done so far is solve a problem that occurred with this particular process. To prevent the problem from occurring in the future, you need to go back to the source record and update a flag there. In the case of voucher posting, 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 the problem at its source by adding a few lines to that query:
UPDATE PS_VOUCHER SET POST_STATUS_AP='P' WHERE POST_STATUS_AP='U' AND (BUSINESS_UNIT,VOUCHER_ID) IN
(SELECT BUSINESS_UNIT,VOUCHER_ID FROM PS_VCHR_TEMP_LN11 A
WHERE EXISTS
(SELECT 'X' FROM PS_VCHR_ACCTG_LINE B
WHERE A.BUSINESS_UNIT=B.BUSINESS_UNIT
AND A.VOUCHER_ID=B.VOUCHER_ID
AND A.UNPOST_SEQ=B.UNPOST_SEQ
AND A.APPL_JRNL_ID=B.APPL_JRNL_ID
AND A.DST_ACCT_TYPE=B.DST_ACCT_TYPE
AND A.PYMNT_CNT=B.PYMNT_CNT
AND A.VOUCHER_LINE_NUM=B.VOUCHER_LINE_NUM
AND A.DISTRIB_LINE_NUM=B.DISTRIB_LINE_NUM
AND A.DST_ACCT_TYPE=B.DST_ACCT_TYPE
AND A.CF_BAL_LINE_NUM=B.CF_BAL_LINE_NUM
AND A.LEDGER=B.LEDGER))
Once you've got the problem resolved at its source, you can delete the record from the temp table and restart the process, which should then complete successfully:
DELETE FROM PS_VCHR_TEMP_LN11 A
WHERE EXISTS
(SELECT 'X' FROM PS_VCHR_ACCTG_LINE B
WHERE A.BUSINESS_UNIT=B.BUSINESS_UNIT
AND A.VOUCHER_ID=B.VOUCHER_ID
AND A.UNPOST_SEQ=B.UNPOST_SEQ
AND A.APPL_JRNL_ID=B.APPL_JRNL_ID
AND A.DST_ACCT_TYPE=B.DST_ACCT_TYPE
AND A.PYMNT_CNT=B.PYMNT_CNT
AND A.VOUCHER_LINE_NUM=B.VOUCHER_LINE_NUM
AND A.DISTRIB_LINE_NUM=B.DISTRIB_LINE_NUM
AND A.DST_ACCT_TYPE=B.DST_ACCT_TYPE
AND A.CF_BAL_LINE_NUM=B.CF_BAL_LINE_NUM
AND A.LEDGER=B.LEDGER)
There are also occasions in which there is no duplicate row in the target table and your query does not return any rows. In that case, the problem is usually the result of a bad join between source tables that creates more than one row to insert. It could also be the result of a "GROUP BY" statement that summarizes data on the key fields and one or two non-key fields, and there are two or more rows with different non-key values but identical key values. In that case, you need to figure out why one is different and correct it. Don't try adding that field as a key, though- you might solve today's problems, but you'll just be creating headaches further down the road.
Fortunately, this is one problem that can be fixed rather rapidly if you're adept at trouble-shooting. I see it most often on the voucher posting process, which is an App Engine, so I'll approach the problem from that standpoint, but a lot of this holds true for SQRs, too, although it's a little trickier because data might not have been committed at any point during the process.
Let's assume that your process has failed. The log file should display the entire insert statement; if you've got AE trace enabled, you can also get it from the trace file. Copy that statement and run it in a SQL tool. I use TOAD, because I can have numerous windows open at a time and statements can be recalled simply by hitting F8. Run the 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 were rolled back after the error occurred. In that case, you'll have to go to the trace file, find the last commit that occurred prior to the error, and run all of the statements up to and including the one that fails. Be sure to keep track of the statements that insert or update data, though, because you'll want to put things back the way they were after you've got the problem resolved and before you restart the AE process. If AE trace was not enabled, turn it on in the Process Scheduler config file and restart the failed process. It will fail again, 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 the error, it's a simple matter of finding the bad row and deleting it from what is usually an AE temp table. How do you find it? First, you need to determine the keys of the table into which the row is being inserted. Second, you need to analyze the insert statement to determine the source of the bad row. In the case of the voucher post process, it's usually VCHR_TEMP_LNx, where x is the instance number of the table being used. Third, you need to write a query to find the row in the source table that is a duplicate of a row in the target table. You do that by querying the two tables for rows with duplicate key values. Your SQL statement will end up looking something like this:
SELECT * FROM PS_VCHR_TEMP_LN11 A
WHERE EXISTS
(SELECT 'X' FROM PS_VCHR_ACCTG_LINE B
WHERE A.BUSINESS_UNIT=B.BUSINESS_UNIT
AND A.VOUCHER_ID=B.VOUCHER_ID
AND A.UNPOST_SEQ=B.UNPOST_SEQ
AND A.APPL_JRNL_ID=B.APPL_JRNL_ID
AND A.DST_ACCT_TYPE=B.DST_ACCT_TYPE
AND A.PYMNT_CNT=B.PYMNT_CNT
AND A.VOUCHER_LINE_NUM=B.VOUCHER_LINE_NUM
AND A.DISTRIB_LINE_NUM=B.DISTRIB_LINE_NUM
AND A.DST_ACCT_TYPE=B.DST_ACCT_TYPE
AND A.CF_BAL_LINE_NUM=B.CF_BAL_LINE_NUM
AND A.LEDGER=B.LEDGER)
If your query returns a row or rows, than you need to query the target table and make absolutely certain that the incoming row is a duplicate. Once that has been ascertained, you can safely delete the row from the temp table. Your work isn't done yet, though, because all you've done so far is solve a problem that occurred with this particular process. To prevent the problem from occurring in the future, you need to go back to the source record and update a flag there. In the case of voucher posting, 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 the problem at its source by adding a few lines to that query:
UPDATE PS_VOUCHER SET POST_STATUS_AP='P' WHERE POST_STATUS_AP='U' AND (BUSINESS_UNIT,VOUCHER_ID) IN
(SELECT BUSINESS_UNIT,VOUCHER_ID FROM PS_VCHR_TEMP_LN11 A
WHERE EXISTS
(SELECT 'X' FROM PS_VCHR_ACCTG_LINE B
WHERE A.BUSINESS_UNIT=B.BUSINESS_UNIT
AND A.VOUCHER_ID=B.VOUCHER_ID
AND A.UNPOST_SEQ=B.UNPOST_SEQ
AND A.APPL_JRNL_ID=B.APPL_JRNL_ID
AND A.DST_ACCT_TYPE=B.DST_ACCT_TYPE
AND A.PYMNT_CNT=B.PYMNT_CNT
AND A.VOUCHER_LINE_NUM=B.VOUCHER_LINE_NUM
AND A.DISTRIB_LINE_NUM=B.DISTRIB_LINE_NUM
AND A.DST_ACCT_TYPE=B.DST_ACCT_TYPE
AND A.CF_BAL_LINE_NUM=B.CF_BAL_LINE_NUM
AND A.LEDGER=B.LEDGER))
Once you've got the problem resolved at its source, you can delete the record from the temp table and restart the process, which should then complete successfully:
DELETE FROM PS_VCHR_TEMP_LN11 A
WHERE EXISTS
(SELECT 'X' FROM PS_VCHR_ACCTG_LINE B
WHERE A.BUSINESS_UNIT=B.BUSINESS_UNIT
AND A.VOUCHER_ID=B.VOUCHER_ID
AND A.UNPOST_SEQ=B.UNPOST_SEQ
AND A.APPL_JRNL_ID=B.APPL_JRNL_ID
AND A.DST_ACCT_TYPE=B.DST_ACCT_TYPE
AND A.PYMNT_CNT=B.PYMNT_CNT
AND A.VOUCHER_LINE_NUM=B.VOUCHER_LINE_NUM
AND A.DISTRIB_LINE_NUM=B.DISTRIB_LINE_NUM
AND A.DST_ACCT_TYPE=B.DST_ACCT_TYPE
AND A.CF_BAL_LINE_NUM=B.CF_BAL_LINE_NUM
AND A.LEDGER=B.LEDGER)
There are also occasions in which there is no duplicate row in the target table and your query does not return any rows. In that case, the problem is usually the result of a bad join between source tables that creates more than one row to insert. It could also be the result of a "GROUP BY" statement that summarizes data on the key fields and one or two non-key fields, and there are two or more rows with different non-key values but identical key values. In that case, you need to figure out why one is different and correct it. Don't try adding that field as a key, though- you might solve today's problems, but you'll just be creating headaches further down the road.
No comments:
Post a Comment