EGL9.2: GL Flat File Detail Ledger Import/Budget Ledger Import (GL_LED_IMP) Fails with Unique Constraint Error While Update an Existing Ledger Row (Doc ID 2160763.1)

Last updated on JANUARY 04, 2017

Applies to:

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

Symptoms

Issue:
-----

GL Detail Ledger Import/Budget Ledger Import fails with unique constraint error while attempt to update an existing row.

Steps to replicate:
-------------------
1. Select to Allow Ledger Load Update in Ledgers for a Unit for the Business unit to be tested.
2. Check if period is open for the Fiscal Year & A/C period for which data is being imported for a Business Unit.
3. Review Ledger & pick up a row for a particular year & a/c period for which Ledger update to be done.
4. Create a flat file including at least one row of ledger data (as per the criteria in last step) to be updated for the Business Unit chosen.
5. Navigate to General Ledger > Ledgers > Import Ledger from Flat File and add the file created.
6. Click on the Run button & then OK to initiate GL_LED_IMP.

Please see Replication screenshots for more details.

Error:
---------

Error Position: 0 Return: 805 - ORA-00001: unique constraint (EMDBO.PS_LEDGER) violated

Failed SQL stmt: INSERT INTO PS_LEDGER (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) SELECT 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, '0', 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 FROM PS_LED_IMP_WRK4 WHERE PROCESS_INSTANCE = <Process Instance>

or

Error Position: 0 Return: 805 - ORA-00001: unique constraint (SYSADM.PS_LEDGER_BUDG) violated

Failed SQL stmt: INSERT INTO PS_LEDGER_BUDG (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, BUDGET_PERIOD, SCENARIO, 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) SELECT 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, BUDGET_PERIOD, SCENARIO, BOOK_CODE,GL_ADJUST_TYPE, '0', CURRENCY_CD, STATISTICS_CODE, FISCAL_YEAR,
ACCOUNTING_PERIOD, POSTED_TOTAL_AMT, 0, 0, POSTED_BASE_AMT, POSTED_TRAN_AMT,0, 0, BASE_CURRENCY, DTTM_STAMP_SEC, PROCESS_INSTANCE FROM PS_LEDBD_IMP_WRK4
WHERE PROCESS_INSTANCE = <Process Instance>

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