EGL9.2: Journal Posting to Ledger is Incorrect in Non-Oracle Database When Account - Alternate Account Relationship is One to Many (Doc ID 1948150.1)

Last updated on SEPTEMBER 14, 2016

Applies to:

PeopleSoft Enterprise FIN General Ledger - Version 9.2 to 9.2 [Release 9]
Information in this document applies to any platform.

Symptoms

On : 9.2 version, Journal Post

ACTUAL BEHAVIOR
---------------
Ledger balance for a combination of Account - Alternate Account is incorrectly updated when a journal is posted with journal lines having Account - Alternate Account combinations of the same Account but different Alternate Accounts. (i.e. Account - Alternate Account relationship is one to many and two or more journal lines have the same Account but different Alternate Accounts).
In the Ledger, the Amount value is summed up at Account for each journal line having the same Account (irrespective of the Alternate Account in the line) and not for each unique combination of Account - Alternate Account. (i.e. the Group By happens for Account and for Account and Alternate Account combination).

This is only happening in Microsoft SQL Server.
Same scenario is working fine when using Oracle database.

EXPECTED BEHAVIOR
-----------------------
Journal posting to Ledger should be correct when Account - Alternate Account relationship is one to many.

STEPS
-----------------------
The issue can be reproduced at will with the following steps:

  1. Enable Alternate Account in Installation Options and Ledgers For A Unit.
  2. Account 120001 is linked to multiple Alternate Accounts (290000, 290600 etc). Account 100000 is linked to one Alternate Account (570000).
  3. Create a new journal for BLGE1.
    • Line 1 is linked to Account 120001 and Alternate Account 290000. Amount 1000.
    • Line 2 is linked to Account 120001 and Alternate Account 290600. Amount 5000.
    • Line 3 is linked to Account 100000 and Alternate Account 570000. Amount -6000.
    • Edit and post journal.
  4. Verify PS_LEDGER. Data is looking fine.
    • Account 120001 and Alternate Account 290000, Amount 1000.
    • Account 120001 and Alternate Account 290600, Amount 5000.
    • Account 100000 and Alternate Account 670000, Amount -6000.
  5. Create another journal for the same business unit and same period.
    • Line 1 is linked to Account 120001 and Alternate Account 290000. Amount 10.
    • Line 2 is also linked to Account 120001 and Alternate Account 290000. Amount 50.
    • Line 3 is linked to Account 100000 and Alternate Account 570000. Amount -60.
    • Edit and post journal.
  6. Verify PS_LEDGER. Ledger data is wrong for Alternate Account 290600!
    • Account 120001 and Alternate Account 290000, Amount 1060.
    • Account 120001 and Alternate Account 290600, Amount 5060. This should be Amount 5000.
    • Account 100000 and Alternate Account 670000, Amount -6060.


BUSINESS IMPACT
-----------------------
The issue has the following business impact:
Due to this issue, ledger data is corrupt.

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