Receive ORA-01427 Running Propogate Rates in TP (Doc ID 292385.1)

Last updated on JULY 10, 2009

Applies to:

Oracle Financial Services Applications (OFSA) - Version: 4.5
Oracle Transfer Pricing - Version: 4.5.39
This problem can occur on any platform.

Symptoms

When running a Oracle Transfer Pricing (TP) Process ID in OFSA 4.5.39 to Propagate Rates, the following error is received:

ERROR
Oracle Error: ORA-01427: single-row subquery returns more than one row
Error(Oracle Financial Services Application error) (203055) Error
Bound Parameters:
:TP_COA_ID = 0
:ORG_UNIT_ID = 41600007595 SQL statement.
Error(Oracle Financial Services Application error) (203055) Error Executing SQL statement.

The error is received when the process tries to update the instrument table by running the following statement:

update COMMERCIAL_LOAN a set (a.transfer_rate, a.matched_spread_c)
= (select transfer_rate, matched_spread_c
from GEN1_COMM_LN old
where old.id_number = a.id_number
and old.as_of_date = to_date('07/31/2004','MM/DD/YYYY') )
where a.TP_COA_ID = :TP_COA_ID
and a.ORG_UNIT_ID = :ORG_UNIT_ID
and (((a.DATA_SOURCE = '10'))
and ((a.MATCH_FUND_FLAG < 'Y' or a.MATCH_FUND_FLAG > 'Y'))
and ((a.TRANSFER_RATE = 0.0000)))
and a.as_of_date = to_date('08/31/2004','MM/DD/YYYY')
and ( a.reprice_freq = 0 or a.last_reprice_date <= to_date('07/31/2004','MM/DD/YYYY') )
and exists
(select id_number
from GEN1_COMM_LN old
where old.id_number = a.id_number
and old.as_of_date = to_date('07/31/2004','MM/DD/YYYY') )
and not exists
(select leaf_node
from ofsa_idt_transfer_price t
where t.leaf_node = a.TP_COA_ID
and t.transfer_price_sys_id = 100147.000000
and t.leaf_data_source_cd = 2)

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