Tpol.sh Rate Card Task Fails With ORA-0001 Errors on FSI_PM_GENERATED_INSTRMTS (Doc ID 2860321.1)

Last updated on MAY 05, 2023

Applies to:

Symptoms

On FTP 8.1.1.1 when running the Rate Card batch, Task1 which runs the tpol.sh is failing with errors and no rows are being inserted into FSI_PM_GENERATED_INSTRMTS. The following error is found in the tpol__GENERATE_RATE_CARDS_20210910_1_20210910.props.err log file:

ERROR
java.sql.SQLIntegrityConstraintViolationException: ORA-00001: unique constraint (FSI_PM_GENERATED_INSTRMTS) violated

at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:509)
at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:461)
...
at com.ofs.fsapps.tpol.Instrument.save(Instrument.java:137)
at com.ofs.fsapps.tpol.InstrumentCreator.run(InstrumentCreator.java:606)
at com.ofs.fsapps.tpol.InstrumentCreator.run(InstrumentCreator.java:423)
at com.ofs.fsapps.tpol.InstrumentCreator.main(InstrumentCreator.java:1473)
Caused by: Error : 1, Position : 0, Sql = INSERT INTO FSI_PM_GENERATED_INSTRMTS (ID_NUMBER, DETAIL_RECORD, AS_OF_DATE, ISO_CURRENCY_CD, ACCRUAL_BASIS_CD, AMRT_TERM, AMRT_TERM_MULT, AMRT_TYPE_CD, COMPOUND_BASIS_CD, CUR_BOOK_BAL, CUR_GROSS_RATE, CUR_NET_BOOK_BAL_C, CUR_NET_PAR_BAL_C, CUR_NET_RATE, CUR_PAYMENT, CUR_PAR_BAL, DATA_SOURCE_CD, DEFERRED_CUR_BAL, DEFERRED_ORG_BAL, INSTRUMENT_TYPE_CD, INTEREST_RATE_CD, ISSUE_DATE, ISSUE_TERM, ISSUE_TERM_MULT, LAST_REPRICE_DATE, MARGIN, MARKET_VALUE_C, MATCHED_SPREAD_C, MATURITY_DATE, NEG_AMRT_AMT, NEG_AMRT_EQ_DATE, NEG_AMRT_EQ_FREQ, NEG_AMRT_EQ_MULT, NEG_AMRT_LIMIT, NEXT_PAYMENT_DATE, NEXT_REPRICE_DATE, ORG_BOOK_BAL, ORG_NET_BOOK_BAL_C, ORG_NET_PAR_BAL_C, ORG_PAYMENT_AMT, ORG_PAR_BAL, ORG_RATE, ORG_TERM, ORG_TERM_MULT, ORIGINATION_DATE, PERCENT_SOLD, PMT_ADJUST_DATE, PMT_CHG_FREQ, PMT_CHG_FREQ_MULT, PMT_DECR_CYCLE, PMT_DECR_LIFE, PMT_FREQ, PMT_FREQ_MULT, PMT_INCR_CYCLE, PMT_INCR_LIFE, RATE_CAP_LIFE, RATE_CHG_MIN, RATE_CHG_RND_CD, RATE_CHG_RND_FAC, RATE_DECR_CYCLE, RATE_DECR_LIFE, RATE_DECR_YEAR, RATE_FLOOR_LIFE, RATE_INCR_CYCLE, RATE_INCR_LIFE, RATE_INCR_YEAR, RATE_SET_LAG, RATE_SET_LAG_MULT, RECORD_COUNT, REMAIN_NO_PMTS_C, REMAIN_TERM_C, REMAIN_TERM_MULT_C, REPRICE_FREQ, REPRICE_FREQ_MULT, TEASER_END_DATE, TRANSFER_RATE, NET_MARGIN_CD, MARGIN_GROSS, MARGIN_T_RATE, T_RATE_INT_RATE_CD, CUR_TP_PER_ADB, PRIOR_TP_PER_ADB, LAST_PAYMENT_DATE, ADJUSTABLE_TYPE_CD, INT_TYPE, LRD_BALANCE, LEAF_NUM_ID, LEAF_NODE, COMMIT_TERM, COMMIT_TERM_MULT, COMMIT_START_DATE, COMMIT_MAT_DATE, TRAN_RATE_REM_TERM, IDENTITY_CODE, ORG_UNIT_ID, GL_ACCOUNT_ID, COMMON_COA_ID, PRODUCT_ID, LEGAL_ENTITY_ID, CUSTOMER_ID) VALUES (:1 , :2 , :3 , :4 , :5 , :6 , :7 , :8 , :9 , :10 , :11 , :12 , :13 , :14 , :15 , :16 , :17 , :18 , :19 , :20 , :21 , :22 , :23 , :24 , :25 , :26 , :27 , :28 , :29 , :30 , :31 , :32 , :33 , :34 , :35 , :36 , :37 , :38 , :39 , :40 , :41 , :42 , :43 , :44 , :45 , :46 , :47 , :48 , :49 , :50 , :51 , :52 , :53 , :54 , :55 , :56 , :57 , :58 , :59 , :60 , :61 , :62 , :63 , :64 , :65 , :66 , :67 , :68 , :69 , :70 , :71 , :72 , :73 , :74 , :75 , :76 , :77 , :78 , :79 , :80 , :81 , :82 , :83 , :84 , :85 , :86 , :87 , :88 , :89 , :90 , :91 , :92 , :93 , :94 , :95 , :96 , :97 , :98 , :99 , :100 ), OriginalSql = INSERT INTO FSI_PM_GENERATED_INSTRMTS (ID_NUMBER, DETAIL_RECORD, AS_OF_DATE, ISO_CURRENCY_CD, ACCRUAL_BASIS_CD, AMRT_TERM, AMRT_TERM_MULT, AMRT_TYPE_CD, COMPOUND_BASIS_CD, CUR_BOOK_BAL, CUR_GROSS_RATE, CUR_NET_BOOK_BAL_C, CUR_NET_PAR_BAL_C, CUR_NET_RATE, CUR_PAYMENT, CUR_PAR_BAL, DATA_SOURCE_CD, DEFERRED_CUR_BAL, DEFERRED_ORG_BAL, INSTRUMENT_TYPE_CD, INTEREST_RATE_CD, ISSUE_DATE, ISSUE_TERM, ISSUE_TERM_MULT, LAST_REPRICE_DATE, MARGIN, MARKET_VALUE_C, MATCHED_SPREAD_C, MATURITY_DATE, NEG_AMRT_AMT, NEG_AMRT_EQ_DATE, NEG_AMRT_EQ_FREQ, NEG_AMRT_EQ_MULT, NEG_AMRT_LIMIT, NEXT_PAYMENT_DATE, NEXT_REPRICE_DATE, ORG_BOOK_BAL, ORG_NET_BOOK_BAL_C, ORG_NET_PAR_BAL_C, ORG_PAYMENT_AMT, ORG_PAR_BAL, ORG_RATE, ORG_TERM, ORG_TERM_MULT, ORIGINATION_DATE, PERCENT_SOLD, PMT_ADJUST_DATE, PMT_CHG_FREQ, PMT_CHG_FREQ_MULT, PMT_DECR_CYCLE, PMT_DECR_LIFE, PMT_FREQ, PMT_FREQ_MULT, PMT_INCR_CYCLE, PMT_INCR_LIFE, RATE_CAP_LIFE, RATE_CHG_MIN, RATE_CHG_RND_CD, RATE_CHG_RND_FAC, RATE_DECR_CYCLE, RATE_DECR_LIFE, RATE_DECR_YEAR, RATE_FLOOR_LIFE, RATE_INCR_CYCLE, RATE_INCR_LIFE, RATE_INCR_YEAR, RATE_SET_LAG, RATE_SET_LAG_MULT, RECORD_COUNT, REMAIN_NO_PMTS_C, REMAIN_TERM_C, REMAIN_TERM_MULT_C, REPRICE_FREQ, REPRICE_FREQ_MULT, TEASER_END_DATE, TRANSFER_RATE, NET_MARGIN_CD, MARGIN_GROSS, MARGIN_T_RATE, T_RATE_INT_RATE_CD, CUR_TP_PER_ADB, PRIOR_TP_PER_ADB, LAST_PAYMENT_DATE, ADJUSTABLE_TYPE_CD, INT_TYPE, LRD_BALANCE, LEAF_NUM_ID, LEAF_NODE, COMMIT_TERM, COMMIT_TERM_MULT, COMMIT_START_DATE, COMMIT_MAT_DATE, TRAN_RATE_REM_TERM, IDENTITY_CODE, ORG_UNIT_ID, GL_ACCOUNT_ID, COMMON_COA_ID, PRODUCT_ID, LEGAL_ENTITY_ID, CUSTOMER_ID) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?), Error Msg = ORA-00001: unique constraint (FSI_PM_GENERATED_INSTRMTS) violated

at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:513)
... 17 more

This was previously working in 8074 before upgrade to 8111.


The issue can be reproduced at will with the following steps:
1. Upgrade to 8111
2. Run rate card batch

Changes

Cause

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