Fusion OTBI "Subledger Accounting - Journals Real Time" Subject Area returns *too many* records when a DATE filter is included (Doc ID 2042413.1)

Last updated on SEPTEMBER 21, 2016

Applies to:

Oracle Fusion Financials Common Module - Version 11.1.9.2.0 and later
Oracle Fusion Application Toolkit Cloud Service - Version 11.1.9.2.0 and later
Oracle Fusion Application Toolkit - Version 11.1.9.2.0 and later
Information in this document applies to any platform.

Symptoms

Fusion Financials R9 - accounting date filter causes extra records
OnPremise

Unexpected behavior
-------------------------------
Although there is a filter on Reference Journal Header identifier, this seems to be ignored and records where the value does not match this filter are included in the result.
The analysis result contains more rows , that when the date filter does not exist.

The extra rows have a zero value for Reference Journal Line Number


Steps
------------

Custom analysis, issue occurs when this filter is added : AND "Journal Details"."Accounting Date" = timestamp '2015-06-24 00:00:00'

SET VARIABLE OBIS_REFRESH_CACHE=1,QUERY_SRC_CD='Report',SAW_SRC_PATH='/shared/Custom/Financials/General Ledger/Report contents/XXPGS_GL_JOURNAL_LINE_INQUIRY__WITH_SLA_TEST_PAYABLES_ONLY',PREFERRED_CURRENCY='Local Currency', DISABLE_CACHE_HIT=1, LOGLEVEL=5; SELECT
   0 s_0,
   "Subledger Accounting - Journals Real Time"."Journal Details"."Accounting Date" s_1,
   "Subledger Accounting - Journals Real Time"."Journal Details"."Reference Journal Header Identifier" s_2,
   "Subledger Accounting - Journals Real Time"."Journals"."Apps Document Currency Code" s_3,
   "Subledger Accounting - Journals Real Time"."Journals"."Apps Local Currency Code" s_4,
   "Subledger Accounting - Journals Real Time"."Journals"."Journal Line Accounted Amount CR" s_5,
   "Subledger Accounting - Journals Real Time"."Journals"."Journal Line Accounted Amount DR" s_6,
   "Subledger Accounting - Journals Real Time"."Journals"."Journal Line Entered Amount CR" s_7,
   "Subledger Accounting - Journals Real Time"."Journals"."Journal Line Entered Amount DR" s_8
FROM "Subledger Accounting - Journals Real Time"
WHERE
(("Balancing Segment"."Balancing Segment Tree Filter" = 'PGS_LE-CURRENT') AND ("Natural Account"."Account Tree Filter" = 'PGS_ACCOUNT-CURRENT') AND ("Journal Details"."Reference Journal Header Identifier" = 47768) AND ("Journal Details"."Accounting Date" = timestamp '2015-06-24 00:00:00'))
ORDER BY 1, 3 ASC NULLS LAST, 2 ASC NULLS LAST, 4 ASC NULLS LAST, 5 ASC NULLS LAST
FETCH FIRST 65001 ROWS ONLY

In original analysis which had many more columns, the filter for Reference Journal Header Identifier" = 47768 seemed to be ignored when the date filter was included

Changes

 Filter by date was added to the analysis definition

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