ALM Failed with ORA-01722 Invalid Number for FSI_D_FX_CONTRACTS (Doc ID 2088041.1)

Last updated on MAY 05, 2023

Applies to:

Symptoms

On : 6.1.1.1 version, Calculation Engine

When attempting to run Asset Liability Management (ALM) process on FSI_D_FX_CONTRACTS, RES_DTL, RESULT_MASTER and Cash Flow table are not populated and the following error occurs.

Error
Module Logging OFS errors: (203105) Oracle drv_oci error: OCI Function: [12] - ofetch(), ofen() SQL Function: [4] -
SELECT Oracle Error: ORA- 01722: invalid number Driver Function:
drv_oci::FetchNext() SQL Statement:
select a.rowid, a.ORG_UNIT_ID, a.GL_ACCOUNT_ID, a.COMMON_COA_ID,
a.PRODUCT_ID, a.LOB_ID, a.CUSTOMER_ID, a.iso_currency_cd,
a.id_number, a.identity_code, a.account_number,
a.HOLIDAY_CALENDAR_CODE, a.HOLIDAY_ROLLING_CONVENTION_CD,
a.HOLIDAY_CALC_OPTION_CD, a.instrument_type_cd, a.as_of_date,
a.accrual_basis_cd, a.amrt_term, a.amrt_term_mult,
a.amrt_type_cd, a.cur_book_bal, a.cur_par_bal, a.cur_gross_rate,
a.cur_net_rate, a.cur_payment, a.interest_rate_cd, a.margin,
a.last_reprice_date, a.maturity_date, a.next_payment_date,
a.next_reprice_date, a.org_term, a.org_term_mult, a.pmt_freq,
a.pmt_freq_mult, a.remain_no_pmts_c, a.reprice_freq,
a.reprice_freq_mult, a.origination_date, a.int_type,
a.last_payment_date, a.compound_basis_cd, a.issue_date,
a.adjustable_type_cd, a.EMBEDDED_OPTIONS_FLG, a.org_payment_amt,
a.org_par_bal, a.AMORT_METH_PDFC_CD, a.cur_tp_per_adb,
a.deferred_cur_bal, a.deferred_org_bal, a.EFF_INTEREST_RATE_C,
a.fees_eir, a.margin_gross, a.matched_spread_c, a.net_margin_cd,
a.percent_sold, a.prior_tp_per_adb, a.rate_cap_life,
a.rate_chg_min, a.rate_chg_rnd_cd, a.rate_chg_rnd_fac,
a.rate_decr_cycle, a.rate_floor_life, a.rate_incr_cycle,
a.rate_set_lag, a.rate_set_lag_mult, a.transfer_rate,
a.tran_rate_rem_term, a.lrd_balance, a.liquidity_premium_rate,
a.basis_risk_cost_rate, a.pricing_incentive_rate,
a.other_adjustments_rate, a.margin_t_rate, a.neg_amrt_eq_date,
a.neg_amrt_eq_freq, a.neg_amrt_eq_mult, a.neg_amrt_limit,
a.neg_amrt_amt, a.pmt_adjust_date, a.pmt_chg_freq,
a.pmt_chg_freq_mult, a.pmt_decr_cycle, a.pmt_decr_life,
a.pmt_incr_cycle, a.pmt_incr_life, a.teaser_end_date,
a.t_rate_int_rate_cd, a.RESIDUAL_AMOUNT, a.MATURITY_AMOUNT,
a.EXPECTED_BAL, a.EXPECTED_BAL_GROWTH_PCT, a.OFFSET_PERCENT,
a.MINIMUM_BALANCE, a.BEHAVIOUR_TYPE_CD, a.BEHAVIOUR_SUB_TYPE_CD,
a.DEVOLVEMENT_STATUS_CD, a.leg_type, a.EXCHG_OF_PRINCIPAL,
a.int_calc_type
from FSI_D_FX_CONTRACTS a
where a.as_of_date = :as_of_date
and a.LEG_TYPE = :LEG_TYPE
and a.GL_ACCOUNT_ID = :GL_ACCOUNT_ID
and a.ISO_CURRENCY_CD = :ISO_CURRENCY_CD
and AS_OF_DATE = TO_DATE('09/ 16/ 2015', 'MM/ DD/ YYYY')

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.