OFSAA Ledger Stat Load Process Fails With ORA-00001: unique constraint Loading Multiple Currencies (Doc ID 1511637.1)

Last updated on NOVEMBER 22, 2016

Applies to:

Oracle Financial Services Analytical Applications Infrastructure - Version 7.3 and later
Information in this document applies to any platform.
Oracle Financial Services Analytical Applications Infrastructure (OFSAAI)
Oracle Financial Services Analytical Applications (OFSAA)

Symptoms

On OFSAAI 7.3, when attempting to run Ledger Load I or Ledger Load III using the same data with multiple ISO_CURRENCY_CD, the following error occurs:

ERROR
WRAPPER_LEDGER_STAT_LOAD- Error: -1: ORA-00001: unique constraint (ATOMIC.PK_LS_LOAD_T1) violated
WRAPPER_LEDGER_STAT_LOAD: Starting batch table update/insert
WRAPPER_LEDGER_STAT_LOAD: Batch table updated/inserted with data. Executing ledger load program


On OFSAAI 8.0, when attempting to run a Type 3 Ledger Load using the same data with multiple ISO_CURRENCY_CD, the following error occurs:

ERROR
21-NOV-2016 10:27:39 - INSERT INTO LS_LOAD_TABLE_GTT_V...
WRAPPER_LEDGER_STAT_LOAD- Error: -1: ORA-00001: unique constraint (ATOMIC.UK_GTT) violated


The issue can be reproduced at will with the following steps:
1. Create Ledger Load table and index in Erwin
2. Run model upload
3. Create Ledger Load view
4. Update lsl view
5. Create two rows which only differ by ISO_CURRENCY_CD like:

LS_LOAD_T1
DS YEAR_S ACCUM_TYPE CONSOLIDAT CRNCYTYPCD ISOCRNCYCD FINANC_ID ORG_ID GL_ACCT_ID CMN_COA_ID PRDCT_ID ONE_MONTH_AMT BALTYPECD
GLEXP 2012 D 100 1 GBP 100 41950 831020 300211 0 45,992.00 101
GLEXP 2012 D 100 1 USD 100 41950 831020 300211 0 96,773.62 101


6. Run the fn_ledgerDataLoader:

Batch INFODOM1_LS_LOAD

Task Definition
Database Type EDW
Datastore Name INFODOM1
IP Address
Rule Name fn_ledgerDataLoader
Parameter List 'LS_LOAD_T1','FISCAL_ONE_MONTH', 'REPLACE','N','10','10'

7. Check generated errors in fsi_message_log:

CONTEXT_SPECIFIC_TEXT
WRAPPER_LEDGER_STAT_LOAD: Starting batch table update/insert
WRAPPER_LEDGER_STAT_LOAD: Batch table updated/inserted with data. Executing ledger load program
WRAPPER_LEDGER_STAT_LOAD- Error: -1: ORA-00001: unique constraint (ATOMICUSER.PK_LS_LOAD_T1) violated

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