FTP Instrument Table Validation Batch Fails for FSI_D_BREAK_FUNDING_CHARGES With 'No valid unique index found' Errors (Doc ID 1401140.1)

Last updated on JANUARY 27, 2012

Applies to:

Oracle Financial Services Funds Transfer Pricing - Version: 5.6 and later   [Release: 5 and later ]
Information in this document applies to any platform.
Oracle Financial Services Analytical Applications Infrastructure (OFSAAI) - Version 7.2
Oracle Financial Services Analytical Applications (OFSAA) - Version 5.x

Symptoms

On Oracle Financial Services Funds Transfer Pricing (FTP) 5.6 after applying the one-off patch <Patch:13014660> - BREAK IDENTIFICATION SHOULD MOVE 2 RECORDS FOR PARTIAL AND CHG IN ATTR BREAKS, when attempting to run Instrument Table Validation procedure after a model upload the following error occurs:

ERROR
Table FSI_D_BREAK_FUNDING_CHARGES ( 360)
Table FSI_D_BREAK_FUNDING_CHARGES Failed. Validate Instrument.No valid unique index found
Table did not meet stored procedure requirements.Table: FSI_D_BREAK_FUNDING_CHARGES
Table FSI_D_BREAK_FUNDING_CHARGES Failed (360)
Table FSI_D_BREAK_FUNDING_CHARGES ( 530)
Table FSI_D_BREAK_FUNDING_CHARGES Successfull (530)
Table FSI_D_BREAK_FUNDING_CHARGES ( 310)
Table FSI_D_BREAK_FUNDING_CHARGES Failed. Validate Instrument.No valid unique index found
Table did not meet stored procedure requirements.Table: FSI_D_BREAK_FUNDING_CHARGES
Table FSI_D_BREAK_FUNDING_CHARGES Failed (310)
Table FSI_D_BREAK_FUNDING_CHARGES ( 20)
Table FSI_D_BREAK_FUNDING_CHARGES Successfull (20)
Table FSI_D_BREAK_FUNDING_CHARGES ( 200)
Table FSI_D_BREAK_FUNDING_CHARGES Failed. Validate Instrument.No valid unique index found
Table did not meet stored procedure requirements.Table: FSI_D_BREAK_FUNDING_CHARGES
Table FSI_D_BREAK_FUNDING_CHARGES Failed (200)
Table FSI_D_BREAK_FUNDING_CHARGES ( 210)
Table FSI_D_BREAK_FUNDING_CHARGES Failed. Validate Instrument.No valid unique index found
Table did not meet stored procedure requirements.Table: FSI_D_BREAK_FUNDING_CHARGES
Table FSI_D_BREAK_FUNDING_CHARGES Failed (210)
Table FSI_D_BREAK_FUNDING_CHARGES ( 370)
Table FSI_D_BREAK_FUNDING_CHARGES Failed. Validate Instrument.No valid unique index found
Table did not meet stored procedure requirements.Table: FSI_D_BREAK_FUNDING_CHARGES
Table FSI_D_BREAK_FUNDING_CHARGES Failed (370)

The error is caused by the change in the index from p13014660_56_Generic.zip\13014660_GENERIC\SQLScripts\atomic.sql:

Found the following updates to indeed confirm the changes:

DROP INDEX FSI_D_BREAK_FUNDING_CHARGES
/
CREATE UNIQUE INDEX FSI_D_BREAK_FUNDING_CHARGES ON FSI_D_BREAK_FUNDING_CHARGES(ID_NUMBER,IDENTITY_CODE,AS_OF_DATE,BREAKAGE_TYPE_CD)
/

The addition of the BREAKAGE_TYPE_CD column to the unique index on FSI_D_BREAK_FUNDING_CHARGES causes the unique instrument index requirement for ALM Standard tables to fail.

STEPS
The issue can be reproduced at will with the following steps:
1. Apply Patch:13014660- BREAK IDENTIFICATION SHOULD MOVE 2 RECORDS FOR PARTIAL AND CHG IN ATTR BREAKS
2. Rerun the Instrument Table Validation procedure after model upload

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