My Oracle Support Banner

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

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.