Filter Not Being Used in Ledger Stat to Ledger Stat Allocation (Doc ID 1523199.1)

Last updated on JANUARY 30, 2013

Applies to:

Oracle Financial Services Profitability Management - Version 6.0 to 6.0.3 [Release 6]
Information in this document applies to any platform.
Oracle Financial Services Analytical Applications (OFSAA)

Symptoms

In Oracle Financial Services Profitability Management (PFT) 6.0, when you run a Ledger Stat to Ledger Stat Allocation that includes a Filter on the Source screen, the Allocation engine is not including the Filter criteria when it is processed.  As a result, the Allocation is processing all records instead just the records selected by the filter and the results are wrong.

In the code listed in the ofspa log in $FIC_DB_HOME/log/FusionApps, you can see the Global Temporary Table SQL does not include the filter criteria. 

For example, you have a Data Filter that selects GL_ACCOUNT_ID = 100010.

The Global Temporary table does not include this criteria:

Module Logging OFS Access module data: CREATE
CREATE GLOBAL TEMPORARY TABLE GLBL14528TMP ON COMMIT PRESERVE
ROWS AS
SELECT *
FROM LEDGER_STAT a
WHERE a.FINANCIAL_ELEM_ID=100
and a.year_s IN (2012)
and a.iso_currency_cd = 'USD'
AND a.CONSOLIDATION_CD = 100


However, other SQL in the log does include it:

Module Logging OFS calculation module data: select
select distinct a.ISO_CURRENCY_CD
from LEDGER_STAT a
where a.ISO_CURRENCY_CD <> 'USD'
AND a.ISO_CURRENCY_CD <> '002'
and a.FINANCIAL_ELEM_ID=100
and (((a.GL_ACCOUNT_ID = 100010)))
and a.year_s IN (2012)
and a.iso_currency_cd = 'USD'
order by a.ISO_CURRENCY_CD


This issue causes the Allocation to not include the filter criteria and generates incorrect results.

Steps to Reproduce:

  1. Go to Master Maintenance > Filters
  2. Create a new data filter on Ledger Stat
  3. Go to Profitability Management
  4. Go to Rule Specification > Allocation Specification
  5. Create an Allocation that uses Ledger Stat in both the source and target and include the Filter on the Source
  6. Run the Allocation
  7. Review the ofspa log in $FIC_DB_HOME/log/FusionApps

The SQL for the Global Temporary table does not include the data filter criteria.

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