EGL9.2 GL_JP Abended at GL_JP_POST.ProcLedg.Upsert with Error: "ORA-00001: unique constraint (EMDBO.PS_LEDGER) violated" When Period is Open by Ledger Code.
Last updated on SEPTEMBER 12, 2017
Applies to:PeopleSoft Enterprise FIN General Ledger - Version 9.2 to 9.2 [Release 9]
Information in this document applies to any platform.
When attempting to run Journal Post, process runs to no success at step GL_JP_POST.ProcLedg.Upsert
00.52.52 ..........(GL_JP_POST.ProcLedg.Upsert) (SQL)
File: e:\pt85312b-retail\peopletools\src\psappeng\aedebug.hSQL error. Stmt #: 1623 Error Position: 0 Return: 805 - ORA-00001: unique constraint (SYSADM.PS_LEDGER) violated
Failed SQL stmt: MERGE INTO PS_LEDGER L USING PS_JP_PST1_TAO6 T ON(L.BUSINESS_UNIT= '11050' 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 = 768054) WHEN MATCHED THEN UPDATE SET PROCESS_INSTANCE= 768054, 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= 768054 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= 768054
Process 768054 ABENDED at Step GL_JP_POST.ProcLedg.Upsert (SQL) -- RC = 805 (108,524)
The issue can be reproduced at will with the following steps:
1). Enable Book Code at Overall Installation Page
2) Ensure ‘Enter Adjustment Type Journal’ selected on the User Preferences for the User ID used in test.
3) Set up at least 2 Ledger Codes and 2 Adjustment Type.
4) Open Period by ledger code.
- Open Period by 2 ledger codes
- For the period to which journal will be posted, it is open for one ledger code and closed for another ledger code. ( This is crux to the issue replication)
5) Create 1 journal each for 3 business units. ( Please note they are not interunit journal)
- All 3 journals share same ledger group and same journal source.
- All 3 journals have same journal ID.
- All 3 journals are dated in the same period, which is open for one ledger code but closed for another ledger code.
- All 3 journals have reversal requested on a date in the next period ( the 1st day of next period)
- All 3 journals have journal lines with a book code, which is common for both ledger codes ( so journal will pass edit without period closed error)
6) Edit all 3 journals so journal header status are in 'V' - ready for post.
7) Run journal post by ledger group and source so these 3 journals will be picked up by post.
8) Process runs to 'No Success' with above abend/error.
- Reversals are created for all 3 original journals but last original journal has JRNL_HDR_STATUS = 'I'. Both this journal and its reversal have JOURNAL_LOCKED = 'Y'
- There is no adjusting entry journal created for ledger code, which has period closed although it is expected.
Customer reported that Unique Constraint may also occur at step GL_JP_RVRSAL.Process.InsHdr.
What is Working:
If running journal post on above journals one journal at each run, process will run to success for all 3 journals.
If running journal post on other journals with reversal requested,which would not generate ledger code adjusting entry ( not posted to the closed period open by ledger code), process will run to success.
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