ORA-00001 & ORA-01452 Running ALM Process Against FSI_D_FX_CONTRACTS Table

Last updated on JULY 28, 2017

Applies to:

Symptoms

In Oracle Financial Services Asset Liability Management (ALM) 5.6, when you run a Static Deterministic process that includes "Market Value" against the FSI_D_FX_CONTRACTS table, the process populates the RES_DTL_xxxxxx table but fails to populate the FSI_O_RESULT_MASTER and FSI_O_CONSOLIDATED_MASTER tables.  The following errors appear in the ALM debug log in $FIC_DB_HOME/log/FusionApps:

Module Logging OFS errors: (203105) Oracle drv_oci error: OCI Function: [4] - oexec(),
oexn() SQL Function: [3] -
INSERT Oracle Error: ORA- 00001: unique constraint
(<atomic>.XAK1FSI_O_RESULT_MASTER) violated Driver Function:
drv_oci::Execute() SQL Statement:
insert into FSI_O_RESULT_MASTER(result_sys_id, ORG_UNIT_ID,
GL_ACCOUNT_ID, COMMON_COA_ID, PRODUCT_ID,
iso_currency_cd, scenario_num, start_date_index,
result_type_cd, leg_type, financial_rollup, cur_par_bal,
cur_net_par_bal, cur_defer_bal_c, cur_net_rate_w,
cur_transfer_rate, cur_warm, cur_intr_rec_accr,
new_gross_balance, new_net_balance, market_value, duration,
convexity, modified_duration, ytm, average_life,
market_value_clean, CUR_LIQ_PREM_RATE, CUR_BAS_RISK_COST_RATE,
CUR_PRIC_INCENT_RATE, CUR_OTH_ADJ_RATE)
values (:result_sys_id, :ORG_UNIT_ID, :GL_ACCOUNT_ID,
:COMMON_COA_ID, :PRODUCT_ID, :iso_currency_cd, :scenario_num,
:start_date_index, :result_type_cd, :leg_type, :financial_rollup,
:cur_par_bal, :cur_net_par_bal, :cur_defer_bal_c,
:cur_net_rate_w, :cur_transfer_rate, :cur_warm,
:cur_intr_rec_accr, :new_gross_balance, :new_net_balance,
:market_value, :duration, :convexity, :modified_duration, :ytm,
:average_life, :market_value_clean, :CUR_LIQ_PREM_RATE,
:CUR_BAS_RISK_COST_RATE, :CUR_PRIC_INCENT_RATE, :CUR_OTH_ADJ_RATE
)

Module Logging OFS errors: (203105) Oracle drv_oci error: OCI Function: [4] - oexec(),
oexn() SQL Function: [3] -
INSERT Oracle Error: ORA- 00001: unique constraint
(<atomic>.XAK1FSI_O_CONSOLIDATED_MASTER) violated Driver Function:
drv_oci::Execute() SQL Statement:
insert into FSI_O_CONSOLIDATED_MASTER(result_sys_id, ORG_UNIT_ID,
GL_ACCOUNT_ID, COMMON_COA_ID, PRODUCT_ID, iso_currency_cd, scenario_num,
start_date_index, result_type_cd, leg_type, financial_rollup, cur_par_bal,
cur_net_par_bal, cur_defer_bal_c, cur_net_rate_w,
cur_transfer_rate, cur_warm, cur_intr_rec_accr,
new_gross_balance, new_net_balance, market_value, duration,
convexity, modified_duration, ytm, average_life,
market_value_clean, CUR_LIQ_PREM_RATE, CUR_BAS_RISK_COST_RATE,
CUR_PRIC_INCENT_RATE, CUR_OTH_ADJ_RATE)
values (:result_sys_id, :ORG_UNIT_ID, :GL_ACCOUNT_ID,
:COMMON_COA_ID, :PRODUCT_ID, :iso_currency_cd, :scenario_num,
:start_date_index, :result_type_cd, :leg_type, :financial_rollup,
:cur_par_bal, :cur_net_par_bal, :cur_defer_bal_c,
:cur_net_rate_w, :cur_transfer_rate, :cur_warm,
:cur_intr_rec_accr, :new_gross_balance, :new_net_balance,
:market_value, :duration, :convexity, :modified_duration, :ytm,
:average_life, :market_value_clean, :CUR_LIQ_PREM_RATE,
:CUR_BAS_RISK_COST_RATE, :CUR_PRIC_INCENT_RATE, :CUR_OTH_ADJ_RATE
)

Module Logging OFS errors: (203105) Oracle drv_oci error: OCI Function: [4] - oexec(),
oexn() SQL Function: [34] - PL/ SQL EXECUTE Oracle Error: ORA-
01452: cannot
CREATE UNIQUE INDEX; duplicate keys found ORA- 06512: at
"<atomic>.OFSA_DBA", line 1345 ORA- 06512: at "<atomic>.OFSA_DBA",
line 2339 ORA- 06512: at "<atomic>.OFSA_SQL", line 398 ORA- 06512:
at line 1 Driver Function: drv_oci::Execute() SQL Statement:
declare begin ofsa_sql.clone_all_indexes('<atomic>',
'CONS_DTL_208941', 'FSI_O_CONS_DETAIL_TEMPLATE', '208941'); end;

The errors prevent the ALM process from inserting Market Value data into FSI_O_RESULT_MASTER and FSI_O_CONSOLIDATED_MASTER.  Additionally, duplicate rows exist in the CONS_DTL_xxxxxx table.

Cause