EGL9.2 : Journal Post (GL_JP) Fails With Unique Constraint Error When Data Is Being Inserted To PS_LEDGER_ADB (Doc ID 1920334.1)

Last updated on FEBRUARY 06, 2019

Applies to:

Symptoms

Issue:
-------

When two Journal Post Processes are run concurrently, occasionally journal post process abends with Unique constraint error. Below error message is received -

Process XXXXXX ABENDED at Step GL_ADB_POST.LedgBals.Merge (Action SQL) -- RC = 805

The issue could not be replicated in GCS environment since the chances of getting into the problematic section is quite rare even though the process is run at the same time. A customer reported the issue and below is the analysis where and why the process abended -

Two process were run almost at the same moment (Process Instance 55635354 and 55635355). As it can be seen the subsequent journal post process failed. This happened since commit was issued suring the processing of first journal post, while the MERGE statement for the second process was still being executed.

AE_GL_JP_55635354 -

-- 22.29.40 ...............(GL_ADB_POST.LedgBals.Merge) (SQL)
MERGE INTO PS_LEDGER_ADB A USING PS_LEDG_ADB2_TAO10 T ON(A.BUSINESS_UNIT =
-- Row(s) affected: 8

-- 22.29.59 ..............(GL_ADB_POST.PostBals.iAdjADB) (Call Section GL_ADB_POST.ADBAdjs)

-- 22.30.00 ........(GL_JP_ADB.MAIN.unsetadb) (PeopleCode)
COMMIT

AE_GL_JP_55635355 -

-- 22.29.40 ...............(GL_ADB_POST.LedgBals.Merge) (SQL)
MERGE INTO PS_LEDGER_ADB A USING PS_LEDG_ADB2_TAO11 T ON(A.BUSINESS_UNIT =

-- Row(s) affected: 0
-- 22.30.01 Process 55635355 ABENDED at Step GL_ADB_POST.LedgBals.Merge (Action SQL) -- RC = 805
ROLLBACK

As it can be seen, it is the MERGE statement where the error is appearing. Hence, the issue is specific to Oracle database since Oracle database supports Merge statement.

(Note - GL_JP process which is introduced in 9.2 would insert ADB data to LEDGER_ADB. Whereas, in prior version separate process used to push data to LEDGER_ADB)

Changes

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.