E-NV: The Delivered Journal Drilldown Is Failing After The 9.2 Upgrade

(Doc ID 2310574.1)

Last updated on OCTOBER 02, 2017

Applies to:

PeopleSoft Enterprise PT PeopleTools - Version 8.55 and later
Information in this document applies to any platform.

Symptoms


Issue
------------
nVision report journal drill down shows blank rows, while running the generated SQL using SQL tool, data comes up
They were, however, consistently able to replicate the issue on particular report - 'Consolidation Statement of Operations (Actual vs Budget)' report
i.e. when drilling down 'Income from JVs' (G63) row for Actuals, while for other rows under Actuals column, it works.

Not Working SQL (where drill down comes up blank, but when SQL is run using SQL tool, data comes up):

SELECT A.JOURNAL_ID, TO_CHAR(A.JOURNAL_DATE,'YYYY-MM-DD'), A.JOURNAL_LINE, A.ACCOUNT, A.DEPTID, A.MONETARY_AMOUNT, A.LINE_DESCR
 FROM PS_JRNL_DRILL_VW A
 WHERE ( A.JRNL_HDR_STATUS IN ('P','U')
    AND A.LEDGER = 'ACTUALS'
    AND A.FISCAL_YEAR = 2016
    AND ( A.ACCOUNTING_PERIOD = 3
    OR A.ACCOUNTING_PERIOD = 903)

    AND EXISTS (SELECT 'X' FROM PSTREESELECT05 A3_5 WHERE
 A3_5.SELECTOR_NUM=168613 AND A.BUSINESS_UNIT>= A3_5.RANGE_FROM_05
AND A.BUSINESS_UNIT <= A3_5.RANGE_TO_05 AND A3_5.TREE_NODE_NUM
BETWEEN 1500000000 AND 2000000000)
    AND EXISTS (SELECT 'X' FROM
PSTREESELECT10 A3_6 WHERE  A3_6.SELECTOR_NUM=168699 AND A.ACCOUNT>=
A3_6.RANGE_FROM_10 AND A.ACCOUNT <= A3_6.RANGE_TO_10 AND
A3_6.TREE_NODE_NUM BETWEEN 1241935460 AND 1258064492)
    AND A.CURRENCY_CD = 'USD')

Environment
---------------------
Upgraded to FSCM 9.2 PUM 23 | PeopleTools 8.55.13 from FSCM 9.1 | PeopleTools 8.52.05

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