New Chartfield Fund Code Get Locked When Creating Online Journal (Doc ID 2091874.1)

Last updated on SEPTEMBER 27, 2017

Applies to:

PeopleSoft Enterprise FIN General Ledger - Version 9.2 and later
Information in this document applies to any platform.

Symptoms

On : 9.2 version, Journal Entry

ACTUAL BEHAVIOR
---------------
When creating an online journal with multiple fund codes the journal gets locked and fail with unique constraint error:

15:10:10.596 ..........(GL_JP_POST.ProcLedg.Upsert) (SQL)

File: ................................................ error. Stmt #: 1703 Error Position: 0 Return: 805 - ORA-00001: unique constraint (SYSADM.PS_LEDGER) violated
Failed SQL stmt: MERGE /*+ LEADING (T) */ INTO PS_LEDGER L USING ( SELECT * FROM PS_JP_PST1_TAO4 WHERE PROCESS_INSTANCE = 337552 ORDER BY BUSINESS_UNIT, LEDGER, ACCOUNT, ALTACCT, DEPTID, OPERATING_UNIT, PRODUCT, FUND_CODE, CLASS_FLD, PROGRAM_CODE, BUDGET_REF, AFFILIATE, AFFILIATE_INTRA1, AFFILIATE_INTRA2, CHARTFIELD1, CHARTFIELD2, CHARTFIELD3, PROJECT_ID, BOOK_CODE, GL_ADJUST_TYPE, DATE_CODE, CURRENCY_CD, STATISTICS_CODE, FISCAL_YEAR, ACCOUNTING_PERIOD) T ON (L.BUSINESS_UNIT = 'FUND' AND L.BUSINESS_UNIT = T.BUSINESS_UNIT AND L.LEDGER = T.LEDGER AND L.ACCOUNT = T.ACCOUNT AND L.ALTACCT = T.ALTACCT AND L.DEPTID = T.DEPTID AND L.OPERATING_UNIT = T.OPERATING_UNIT AND L.PRODUCT = T.PRODUCT AND L.FUND_CODE = T.FUND_CODE AND L.CLASS_FLD = T.CLASS_FLD AND L.PROGRAM_CODE = T.PROGRAM_CODE AND L.BUDGET_REF = T.BUDGET_REF AND L.AFFILIATE = T.AFFILIATE AND L.AFFILIATE_INTRA1 = T.AFFILIATE_INTRA1 AND L.AFFILIATE_INTRA2 = T.AFFILIATE_INTRA2 AND L.CHARTFIELD1 = T.CHARTFIELD1 AND L.CHARTFIELD2 = T.CHARTFIELD2 AND L.CHARTFIELD3 = T.CHARTFIELD3 AND L.PROJECT_ID = T.PROJECT_ID AND L.BOOK_CODE = T.BOOK_CODE AND L.GL_ADJUST_TYPE = T.GL_ADJUST_TYPE AND L.DATE_CODE = T.DATE_CODE AND L.CURRENCY_CD = T.CURRENCY_CD AND L.STATISTICS_CODE = T.STATISTICS_CODE AND L.FISCAL_YEAR = T.FISCAL_YEAR AND L.ACCOUNTING_PERIOD = T.ACCOUNTING_PERIOD AND T.PROCESS_INSTANCE = 337552) WHEN MATCHED THEN UPDATE SET PROCESS_INSTANCE = 337552 , DTTM_STAMP_SEC = CAST(SYSTIMESTAMP AS TIMESTAMP) , POSTED_TOTAL_AMT = L.POSTED_TOTAL_AMT + T.POSTED_TOTAL_AMT , POSTED_BASE_AMT = L.POSTED_BASE_AMT + T.POSTED_BASE_AMT , POSTED_TRAN_AMT = L.POSTED_TRAN_AMT + T.POSTED_TRAN_AMT , POSTED_TOTAL_DR =L.POSTED_TOTAL_DR + T.POSTED_TOTAL_DR, POSTED_TOTAL_CR = L.POSTED_TOTAL_CR + T.POSTED_TOTAL_CR ,POSTED_TRAN_DR = L.POSTED_TRAN_DR + T.POSTED_TRAN_DR, POSTED_TRAN_CR = L.POSTED_TRAN_CR + T.POSTED_TRAN_CR WHERE PROCESS_INSTANCE = 337552 WHEN NOT MATCHED THEN INSERT (BUSINESS_UNIT, LEDGER, ACCOUNT, ALTACCT, DEPTID, OPERATING_UNIT, PRODUCT, FUND_CODE, CLASS_FLD, PROGRAM_CODE, BUDGET_REF, AFFILIATE, AFFILIATE_INTRA1, AFFILIATE_INTRA2, CHARTFIELD1, CHARTFIELD2, CHARTFIELD3, PROJECT_ID, BOOK_CODE, GL_ADJUST_TYPE, DATE_CODE, CURRENCY_CD, STATISTICS_CODE, FISCAL_YEAR, ACCOUNTING_PERIOD, POSTED_TOTAL_AMT, POSTED_TOTAL_DR, POSTED_TOTAL_CR, POSTED_BASE_AMT, POSTED_TRAN_AMT, POSTED_TRAN_DR, POSTED_TRAN_CR, BASE_CURRENCY, DTTM_STAMP_SEC, PROCESS_INSTANCE) VALUES (T.BUSINESS_UNIT, T.LEDGER, T.ACCOUNT, T.ALTACCT, T.DEPTID, T.OPERATING_UNIT, T.PRODUCT, T.FUND_CODE, T.CLASS_FLD, T.PROGRAM_CODE, T.BUDGET_REF, T.AFFILIATE, T.AFFILIATE_INTRA1, T.AFFILIATE_INTRA2, T.CHARTFIELD1, T.CHARTFIELD2, T.CHARTFIELD3, T.PROJECT_ID, T.BOOK_CODE, T.GL_ADJUST_TYPE, T.DATE_CODE, T.CURRENCY_CD, T.STATISTICS_CODE, T.FISCAL_YEAR, T.ACCOUNTING_PERIOD, T.POSTED_TOTAL_AMT, T.POSTED_TOTAL_DR, T.POSTED_TOTAL_CR, T.POSTED_BASE_AMT, T.POSTED_TRAN_AMT, T.POSTED_TRAN_DR, T.POSTED_TRAN_CR, T.BASE_CURRENCY, T.DTTM_STAMP_SEC, T.PROCESS_INSTANCE) WHERE T.PROCESS_INSTANCE = 337552
2015-12-16-15.10.10.385894 GetNextStateRecord [68] Exception logged: RC=3.
2015-12-16-15.10.10.385894 ExecuteSql [1335] Exception logged: RC=8.
2015-12-16-15.10.10.385894 ExecuteSql [1454] Exception logged: RC=8.
2015-12-16-15.10.10.385895 DoSqlAction [2544] Exception logged: RC=8.
2015-12-16-15.10.10.385895 DoSqlAction [2592] Exception logged: RC=8.
2015-12-16-15.10.10.385895 DoStepActions [2013] Exception logged: RC=8.

Process 337552 ABENDED at Step GL_JP_POST.ProcLedg.Upsert (SQL) -- RC = 805 (108,524)



Cause

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