FTP Multiple Transfer Pricing Engine Tasks Executed From a Single Batch in Parallel is Failing With ORA-00001 : unique constraint Errors

(Doc ID 1565730.1)

Last updated on OCTOBER 04, 2016

Applies to:

Oracle Financial Services Funds Transfer Pricing - Version 6.1 to 6.1 [Release 6]
Information in this document applies to any platform.
Oracle Financial Services Analytical Applications Infrastructure (OFSAAI)
Oracle Financial Services Analytical Applications (OFSAA)

Symptoms

On Oracle Financial Services Funds Transfer Pricing (FTP) 6.1, for same batch when running multiple Transfer Pricing Engine rules in parallel, the batch fails.

Example:
Batch Name: FTP_EXECUTION_FIRST_RUN
(Transfer Pricing Engine rule) Task 1: TP Disc CF Term Deposits
(Transfer Pricing Engine rule) Task7: TP Cash to Savings CASA

Error message: Oracle Error: ORA- 00001: unique constraint

On checking the log, it appears both tasks are trying to delete/ insert data into the FSI_RESULT_QUEUE table with same values.

Primary key in FSI_RESULT_QUEUE table: JOB_NUM, RESULT_TYPE.  This issue did not occur in FTP 5.6, where no rows were written to the FSI_RESULT_QUEUE table.

ERROR
Module Logging OFS errors: (203105) Oracle drv_oci error: OCI Function: [4] -
oexec(),
oexn() SQL Function: [3] -
INSERT Oracle Error: ORA- 00001: unique constraint
(OFSAAATOMIC.PK_FSI_RESULT_QUEUE) violated Driver Function:
drv_oci::Execute() SQL Statement:
insert into fsi_result_queue(job_num, result_type,
result_message)
values (:job_num, :result_type, :result_message )


Module Logging OFS errors: (203055) Error Bound Parameters: :job_num =
OFSAAINFOD_Alina_test_parallel_20120811_1 :result_type = 1
:result_message = SQL statement.

The issue can be reproduced at will with the following steps:

1. Create one FTP standard for term deposits
2. Create one FTP standard process for CASA
3. Define a batch with 2 tasks based on above 2 TP processes, not depending one on another
4. Execute the batch ===> the second task is failing

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