No Year_S in the Where Clause for the Global Temp Table for Ledger Stat Allocations with Expressions or Macros (Doc ID 1610098.1)

Last updated on DECEMBER 23, 2013

Applies to:

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

Symptoms

On Oracle Financial Services Profitability Management (PFT) 6.1, when running allocations on Ledger Stat using either a macro like [Accrual Basis] or an Expression (like Constant 1) on Source which generate global temp tables, no where clause is added in the create statement for the global temp tables for year_s

ACTUAL BEHAVIOR
When executing an allocation rule on Ledger Stat the global temp table doesn't distinguish the data based on the year_s classification. This is resulting in incorrect results for ledger stat:

Module Logging OFS Access module data:
CREATE GLOBAL TEMPORARY TABLE GLBL10355TMP ON COMMIT PRESERVE
ROWS AS
SELECT *
FROM (SELECT DECODE(OFSA_DETAIL_LEAVES.accrual_basis_cd, 1, 30/
360, 2, 31/ 360, 3, 31/ 365, 4, 30/ 365, 5, 30/ 365, 6, 31/ 365,
31) acr, x.*
FROM LEDGER_STAT x, OFSA_DETAIL_LEAVES
WHERE OFSA_DETAIL_LEAVES.LEAF_NODE=x.COMMON_COA_ID) a
WHERE a.FINANCIAL_ELEM_ID=100
and a.ORG_UNIT_ID=5014146
and a.GL_ACCOUNT_ID=112011
and a.COMMON_COA_ID=112011
and a.PRODUCT_ID=112011
AND a.CONSOLIDATION_CD = 100


==> No year_s statement above

EXPECTED BEHAVIOR
Expect the generated global temporary table to contain the year_s in which corresponds to the As Of Date for execution. This is correctly generated if no Expression is used or if [Current Month] macro is used:

Module Logging OFS Access module data: CREATE
CREATE GLOBAL TEMPORARY TABLE GLBL5583TMP ON COMMIT PRESERVE ROWS
AS
SELECT *
FROM LEDGER_STAT a
WHERE a.FINANCIAL_ELEM_ID=100
and a.ORG_UNIT_ID=5014145
and a.GL_ACCOUNT_ID=112011
and a.year_s IN (1999)
AND a.CONSOLIDATION_CD = 100


This appears to affect all Ledger Stat allocations.

Steps to reproduce:
The issue can be reproduced at will with the following steps:

1. Create a LS allocation -- can be Static Driver
2. In Source choose Ledger Stat table and [Accrual Basis] for the column
3. Save and run the allocation
4. Check the debug log for statement on create global temporary table
5. Update allocation and for Source choose [Current Month]
6. Save and run the allocation
7. Check the debug log for statement on create global temporary table ==> see and a.year_s in ( ) is generated

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