Reporting and Analytics for ERP

Get Involved. Join the Conversation.

Topic

    Walter de Wit
    TODATE Function not working in 'Budgetary Control -...
    Topic posted April 16, 2019 by Walter de WitGreen Ribbon: 100+ Points, tagged OTBI 
    47 Views, 3 Comments
    Title:
    TODATE Function not working in 'Budgetary Control - Balances Real Time')
    Summary:
    TODATE Function not working as expected in mentioned SA
    Content:

    When using the TODATE() Function in Subject Area 'Budgetary Control - Balances Real Time' an errors occurs:

     Error
      View Display Error
     
    Odbc driver returned an error (SQLExecDirectW).
      Error Details
    Error Codes: OPR4ONWY:U9IM8TAC:U9IM8TAC:U9IM8TAC:U9IM8TAC:OI2DL65P
    State: HY000. Code: 10058. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred. (HY000)
    State: HY000. Code: 43113. [nQSError: 43113] Message returned from OBIS. (HY000)
    State: HY000. Code: 43119. [nQSError: 43119] Query Failed: (HY000)
    State: HY000. Code: 14078. [nQSError: 14078] This query requires the External Expression to be set in the physical Complex Join FscmTopModelAM.FinGlCalAccAM.GLFiscalPeriodPVO_FscmTopModelAM.FinCcControlEnginePublicModelAM.BalancePVO. (HY000)
    SQL Issued: SET VARIABLE PREFERRED_CURRENCY='Local Currency';SELECT 0 s_0, "Budgetary Control - Balances Real Time"."Control Budget Account"."Concatenated Segements" s_1, "Budgetary Control - Balances Real Time"."Control Budget"."Control Budget Name" s_2, "Budgetary Control - Balances Real Time"."Time"."Budget Period Name" s_3, "Budgetary Control - Balances Real Time"."Time"."Budget Year" s_4, DESCRIPTOR_IDOF("Budgetary Control - Balances Real Time"."Control Budget"."Control Budget Name") s_5, SORTKEY("Budgetary Control - Balances Real Time"."Time"."Budget Period Name") s_6, "Budgetary Control - Balances Real Time"."Budget"."Total Budget" s_7, TODATE("Budgetary Control - Balances Real Time"."Budget"."Total Budget","Time"."Budget - Fiscal Calendar"."Fiscal Year") s_8 FROM "Budgetary Control - Balances Real Time" FETCH FIRST 75001 ROWS ONLY 

    The logical query used is:

    SET VARIABLE PREFERRED_CURRENCY='Local Currency';SELECT
       0 s_0,
       "Budgetary Control - Balances Real Time"."Control Budget Account"."Concatenated Segements" s_1,
       "Budgetary Control - Balances Real Time"."Control Budget"."Control Budget Name" s_2,
       "Budgetary Control - Balances Real Time"."Time"."Budget Period Name" s_3,
       "Budgetary Control - Balances Real Time"."Time"."Budget Year" s_4,
       DESCRIPTOR_IDOF("Budgetary Control - Balances Real Time"."Control Budget"."Control Budget Name") s_5,
       SORTKEY("Budgetary Control - Balances Real Time"."Time"."Budget Period Name") s_6,
       "Budgetary Control - Balances Real Time"."Budget"."Total Budget" s_7,
       TODATE("Budgetary Control - Balances Real Time"."Budget"."Total Budget","Time"."Budget - Fiscal Calendar"."Fiscal Year") s_8
    FROM "Budgetary Control - Balances Real Time"
    ORDER BY 1, 5 ASC NULLS LAST, 7 ASC NULLS LAST, 4 ASC NULLS LAST, 3 ASC NULLS LAST, 6 ASC NULLS LAST, 2 ASC NULLS LAST
    FETCH FIRST 75001 ROWS ONLY

    The column formula used is:

    TODATE("Budget"."Total Budget", "Time"."Budget - Fiscal Calendar"."Fiscal Year")

     

    For Subject area 'General Ledger - Transactional Balances Real Time' the TODATE function is working. Used with column formula:

    TODATE("Base"."Period Net Activity", "Time"."Date - Fiscal Calendar"."Fiscal Year")

    Logical query for the analysis is:

    SET VARIABLE PREFERRED_CURRENCY='Local Currency';SELECT
       0 s_0,
       "General Ledger - Transactional Balances Real Time"."Account"."Concatenated Segments" s_1,
       "General Ledger - Transactional Balances Real Time"."Time"."Fiscal Period" s_2,
       "General Ledger - Transactional Balances Real Time"."Time"."Fiscal Year Number" s_3,
       SORTKEY("General Ledger - Transactional Balances Real Time"."Time"."Fiscal Period") s_4,
       "General Ledger - Transactional Balances Real Time"."Base"."Period Net Activity" s_5,
       TODATE("General Ledger - Transactional Balances Real Time"."Base"."Period Net Activity","Time"."Date - Fiscal Calendar"."Fiscal Year") s_6
    FROM "General Ledger - Transactional Balances Real Time"
    WHERE
    ((DESCRIPTOR_IDOF("General Ledger - Transactional Balances Real Time"."Scenario"."Balance Type") = 'A') AND ("Natural Account Segment"."Natural Account Segment Code" BETWEEN '40000' AND '40100'))
    ORDER BY 1, 4 ASC NULLS LAST, 5 ASC NULLS LAST, 3 ASC NULLS LAST, 2 ASC NULLS LAST
    FETCH FIRST 75001 ROWS ONLY

     

    Why is it not working with the budget data?

     

    Thanks in advance!

    Version:
    Business Intelligence in Oracle Applications Cloud 18C

    Comment