EGL: LEDGER INTEGRITY ON NON-ORACLE DB, WHEN MORE THAN ONE DATE_CODE EXISTS IN LEDGER TABLE (Doc ID 1667811.1)

Last updated on SEPTEMBER 14, 2016

Applies to:

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

Symptoms

CUSTOMER ENVIRONMENT
======================
Application Release 9.2
PUM image 4
PeopleTools 8.53

ISSUE
====
Data not being handled correctly related to DATE_CODE after upgrade from 9.0 to 9.2..
Fix required at update was not initially included ....but are not on PUM 4. they have tried to fix the data but Since the DATE_CODE is not included in the POST update sql and duplicate rows are being created, if they use the utility it will double up the amounts in the ledger because of the duplicate DATE_CODE rows in the LEDGER

Replication steps- COULD NOT REPLICATE ON ORACLE Was replicated in DB2
===============
1. Enable settlement date....
2. Enable Posting by Date Code Select to enable dual posting of the journal by journal date and by settlement date for the ledger group. When journal date and settlement date are the same, the journal post process posts the transaction to the ledger and the ADB ledger using the journal date. When the journal date and settlement date are different, the journal post process posts two rows in the ledger, differentiated by the Date Code field. The Date Code indicates whether the posting date is derived from the settlement date or the journal date.
My understanding is there could be date_code values of 0, 1, 2, or 3.
3. Enter transactions to journal entry for a certain chartfield combination and post to ledger
4. Enter a second transaction that would have a different date_code with the same chartfield combination and post
5. Make sure more than one date_code value is in LEDGER table for that one chartfield combination
6. Make another entry that would update one of the two rows of data and post

The post does not include the date code in the SQL, so it updates both rows of data


EXPECTED RESULTS
=================
Journals should have DATE_CODE properly populated for converted and new transactions
LEDGER should only have DATE_CODE = 0 and post should properly include DATE_CODE in the update script


ACTUAL RESULTS
===============
Since data exists from conversion that still has data code = blank and have data from new data that has date code of 0, when the data gets updated into the LEDGER table, the post process does not have data code in the update statement..so it updates the existing row where blank exists and then it sees that a new combination exists where the date code would be 0 ...so it inserts a new row for the new transactions that come in created with a row with 0


BUSINESS IMPACT
================
Data integrity issues between ledger and journals

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