My Oracle Support Banner

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

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


In this Document
  Symptoms
  Cause
  Solution
  References

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