ORA-00904 Errors Generated for Ledger_Stat Allocations With Dynamic Driver Due to Incorrect Merge Sql When Currency 'Match Source & Driver' Is Selected On Output

(Doc ID 1572032.1)

Last updated on AUGUST 01, 2013

Applies to:

Oracle Financial Services Profitability Management - Version 6.0 and later
Information in this document applies to any platform.
Oracle Financial Services Analytical Applications (OFSAA) - Version 6.x

Symptoms

On Oracle Financial Services Profitability Management (PFT) 6.0.3/6.1, when attempting to run any dynamic driver allocations with Match Source & Driver selected for the Currency dimension on Output, the allocation hangs and remains in ongoing status. The PFT debug log ends with the merge statement. If the merge statement is run in SQL Developer, the following error occurs:

ERROR

Error at Command Line:32 Column:13
Error report:
SQL Error: ORA-00904: "LOSS_OCC_YR_IDISO_CURRENCY_CD": invalid identifier
00904. 00000 - "%s: invalid identifier"
*Cause:
*Action:


The merge statement generated is identified as being is incorrect with a missing space and , between columns:

MERGE INTO FS_FA_INDM_CAT TARGET USING (SELECT SUM
(IVW_SOURCE.IVW_TARGET_AMOUNT) TARGET_AMOUNT , IDENTITY_CODE,
ID_NUMBER
FROM (
SELECT ( Src.SOURCE_AMOUNT * Drv.drv_factor) IVW_TARGET_AMOUNT ,
Drv.IDENTITY_CODE , Drv.ID_NUMBER
FROM (SELECT (SUM((a.CONSOLIDATION_CD/ 100* 1) ) ) SOURCE_AMOUNT
, PRODUCT_ID, COMPANY_ID, LRSV_RISK_TYPE_ID, PROP_CAS_LOB_ID,
LOCATION_ID, LOSS_OCC_YR_ID, ISO_CURRENCY_CD
from LEDGER_STAT a
where a.FINANCIAL_ELEM_ID=20000
and a.COMMON_COA_ID=10101
and a.CAT_NCAT_ID=1
and (a.identity_code) IN (select distinct a.parent_identity_code
from FSI_M_DATA_IDENTITY_DETAIL a
where a.identity_code in (10111)
and a.as_of_date = '02/ 28/ 2013'
and a.src_drv_type = 'S' )
and a.year_s IN (2013)
and a.CONSOLIDATION_CD = 100
GROUP BY PRODUCT_ID, COMPANY_ID, LRSV_RISK_TYPE_ID,
PROP_CAS_LOB_ID, LOCATION_ID, LOSS_OCC_YR_ID, ISO_CURRENCY_CD )
Src JOIN (SELECT ( ColVal / mTotal ) drv_factor , PRODUCT_ID,
COMPANY_ID, LRSV_RISK_TYPE_ID, PROP_CAS_LOB_ID, LOCATION_ID,
LOSS_OCC_YR_IDISO_CURRENCY_CD, IDENTITY_CODE, ID_NUMBER
from (SELECT a.IDENTITY_CODE, a.ID_NUMBER, (RECORD_COUNT )
ColVal, (1) mTotal , PRODUCT_ID, COMPANY_ID, LRSV_RISK_TYPE_ID,
PROP_CAS_LOB_ID, LOCATION_ID, LOSS_OCC_YR_IDISO_CURRENCY_CD from FS_FA_INDM_CAT a
where a.as_of_date='02/ 28/ 2013'
AND (PRODUCT_ID, COMPANY_ID, LRSV_RISK_TYPE_ID, PROP_CAS_LOB_ID,
LOCATION_ID, LOSS_OCC_YR_IDISO_CURRENCY_CD) IN (
SELECT PRODUCT_ID, COMPANY_ID, LRSV_RISK_TYPE_ID,
PROP_CAS_LOB_ID, LOCATION_ID, LOSS_OCC_YR_IDISO_CURRENCY_CD from LEDGER_STAT a
where a.FINANCIAL_ELEM_ID=20000
and a.COMMON_COA_ID=10101
and a.CAT_NCAT_ID=1
and (a.identity_code) IN (select distinct a.parent_identity_code
from FSI_M_DATA_IDENTITY_DETAIL a
where a.identity_code in (10111)
and a.as_of_date = '02/ 28/ 2013'
and a.src_drv_type = 'S' )
and a.year_s IN (2013)
and a.CONSOLIDATION_CD = 100
GROUP BY PRODUCT_ID, COMPANY_ID, LRSV_RISK_TYPE_ID,
PROP_CAS_LOB_ID, LOCATION_ID, LOSS_OCC_YR_ID, ISO_CURRENCY_CD ))
where mTotal <> 0) Drv ON (Src.PRODUCT_ID = Drv.PRODUCT_ID)
AND (Src.COMPANY_ID = Drv.COMPANY_ID)
AND (Src.LRSV_RISK_TYPE_ID = Drv.LRSV_RISK_TYPE_ID)
AND (Src.PROP_CAS_LOB_ID = Drv.PROP_CAS_LOB_ID)
AND (Src.LOCATION_ID = Drv.LOCATION_ID)
AND (Src.LOSS_OCC_YR_ID = Drv.LOSS_OCC_YR_ID) ) IVW_SOURCE
GROUP BY IDENTITY_CODE , ID_NUMBER ) SOURCE ON
(SOURCE.IDENTITY_CODE = TARGET.IDENTITY_CODE
AND SOURCE.ID_NUMBER = TARGET.ID_NUMBER ) WHEN MATCHED THEN
UPDATE
SET IDENTITY_CODE_CHG = 10111.000000 , TARGET.BASE_RULE_MATCH_NUM
= (SOURCE.TARGET_AMOUNT * 1)


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

1. Create and run any dynamic driver allocation in 6.0.3 or 6.1.

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