Incorrect Merge Query for Percent Distribution Ledger to Instrument Allocation Leads to Allocation Stuck in Ongoing Status (Doc ID 1369689.1)

Last updated on OCTOBER 21, 2011

Applies to:

Oracle Financial Services Profitability Management - 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
OFSPM

Symptoms

On Oracle Financial Services Profitability Management (PFT) 5.6 when running a Dynamic Driver allocation with Ledger_Stat Source, Instrument Driver, Output Debit to Instrument, the generated merge query is incorrect.

ERROR
Module Logging OFS Access module data: MERGE INTO FSI_D_LOAN_CONTRACTS 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(DECODE(a.year_s, 2011, a.month_06, 0) )
SOURCE_AMOUNT , COMMON_COA_ID, FINANCIAL_ELEM_ID, GL_ACCOUNT_ID,
PRODUCT_ID
from LEDGER_STAT a
where a.FINANCIAL_ELEM_ID=30457
and a.ORG_UNIT_ID=501
and a.GL_ACCOUNT_ID=66020324000001
and a.COMMON_COA_ID=6602
and a.PRODUCT_ID=99999999
and (a.identity_code) IN (select distinct a.parent_identity_code
from FSI_M_DATA_IDENTITY_DETAIL a
where a.identity_code in (10104, 10105)
and a.as_of_date = '06/ 30/ 2011'
and a.src_drv_type = 'S' )
and a.year_s IN (2011)
GROUP BY COMMON_COA_ID, FINANCIAL_ELEM_ID, GL_ACCOUNT_ID,
PRODUCT_ID ) Src , (SELECT ( ColVal / mTotal ) drv_factor ,
IDENTITY_CODE, ID_NUMBER
from (SELECT a.IDENTITY_CODE, a.ID_NUMBER, (ORG_BOOK_BAL) ColVal,
(SUM (ORG_BOOK_BAL) OVER()) mTotal                                 <== MISSING partition field
from FSI_D_LOAN_CONTRACTS a
where a.ORG_UNIT_ID=501
and a.as_of_date='06/ 30/ 2011')
WHERE mTotal <> 0) Drv) IVW_SOURCE
WHERE IVW_TARGET_AMOUNT <> 0
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 = 10104.000000 , TARGET.ATM_EXP =
DECODE(identity_code_chg, 10104.000000, ATM_EXP, 0)+
(SOURCE.TARGET_AMOUNT * 1)

The merge query hangs and the process and allocation remains stuck in Ongoing status.

STEPS
The issue can be reproduced at will with the following steps:
1. Create Dynamic Driver allocation with Source = Ledger_Stat, Driver = Loan Contracts, Debit = Loan Contracts, Credit= Ledger_Stat
2. Check the generated merge SQL

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