Core Financialspublic

General Ledger & Intercompany

Get Involved. Join the Conversation.

This is a public Forum  public

Topic

    Cristian Betancur
    No Data Found in XLA_TRANSACTION_ENTITIES table...Answered
    Topic posted September 9, 2019 by Cristian BetancurBronze Medal: 1,250+ Points, tagged Assets, Cash Management, Configuration, Expenses, Financials, General Ledger, Payables, Receivables, Reports, Search, Security, Setup / Administration, Subledger Accounting 
    125 Views, 15 Comments
    Title:
    No Data Found in XLA_TRANSACTION_ENTITIES table (Subledger Source Transactions)
    Summary:
    No Data Found in XLA_TRANSACTION_ENTITIES table (Subledger Source Transactions)
    Content:

    Hi

    I have a SQL query with this table for diagnostic. However, I cannot see data into XLA_TRANSACTION_ENTITIES table.

    I want to know if this issue is for missing role or it is other cause, because Payables and Receivables transactions have been posted in General Ledger.

    This is critical in some reports, because they are used in LACLS Colombia Cloud financial process and Subledger Financial Reports.

    Thanks.

    Cristian.
     

    Version:
    Oracle Fusion Release 13 19B
    Document:
    SR_No_Data_Found.docx (205KB)
    Document:
    SR_Creating Accounting.docx (166KB)

    Best Comment

    Cristian Betancur

    Hi everyone

    Oracle Support found issue.

    I had roles and privileges related with SLA. However, from Release 13 19A, in the BI Publisher configuration, a default set up blocked access to SLA tables. 

    This is workaround:

    1) Navigate to BI Publisher Administration => Properties (under Runtime
    Configuration).
    2) Change the value of 'Run Enable FA Session Language Preference for
    Fusion DB' from 'True' to 'False'.
    3) Click 'Apply'.
    4) Re-run the same report using 'ApplicationDB_FSCM' data source.

    With this workaround, I can see data into XLA_TRANSACTION_ENTITIES.

    These are the bugs related with this issue:

     
    Bug 29611244 - BI REPORT WITH SQL QUERY ON SUBLEDGER SCHEMA (SLA) ARE NOT WORKING ANYMORE USING FSCM DATASOURCE
    Bug 30294750 - NO DATA FOUND IN XLA_TRANSACTION_ENTITIES

    Thanks.

    Cristian.

     

    Comment

     

    • Ravi Kanth Vuddagiri

      I just run simple query in our 19C instance. I can see bunch of entries. Used Reports and Analytics > New > Data Model > + SQL Query > Select FSCM

      Select * from XLA_TRANSACTION_ENTITIES order by entity_id desc

      This is a seeded table and 'This table contains a row for each transaction for which events have been raised in Subledger Accounting'. If you have processed transactions and posted to GL you should see data in this table. 

      Check if you are able to view data in your instance using this option. If you still not able to view data share a screenshot from where you are providing SQL query. Want to check what options you are using. Are you able to view data from any other table like AP Invoices etc ?

      • Cristian Betancur

        Hi Ravi

        This is exact SQL that I executed in my instance, like I show in the attachment.

        But, I do not see data in this table. Can it be for missing role or privilege? Can it be for incorrect or parcial set up in Subledger Accounting?

        Thanks.

        Cristian.

        • Ravi Kanth Vuddagiri

          What is the data source you have used while executing this Query ? It should be FSCM not sure if there are any prefix or suffix for this in your instance. Share a screenshot of where you are running this query. That will help me to confirm if you have selected correct Data source.

          • Cristian Betancur

            Hi Ravi

            I used the Application_FSCM Data source, you can see it in the attachment.

            Thanks

            Cristian.

            Data_Set.zip (124KB)
            • Ravi Kanth Vuddagiri

              One other difference I see is the language you are using. Can you change the language to English and try to run the same SQL ? 

              "Oracle Provisioning and RUP Installer install and upgrade only the English language. To add a language or upgrade an existing language, use Language Pack Installer. If patches containing translatable artifacts were previously applied to this environment, you apply the translated versions of each of those patches after you install the new language."

              Are you able to query data using any other tables like below. Can you try this.

              select * from ra_customer_trx_lines_all or 

              SELECT
              LEDGER_ID,
              NAME,
              DESCRIPTION,
              LEDGER_CATEGORY_CODE,
              COMPLETION_STATUS_CODE,
              CURRENCY_CODE,
              SLA_ACCOUNTING_METHOD_CODE
              from GL_LEDGERS

              • Ravi Kanth Vuddagiri

                Run this SQL. You will get the required results.

                Seleccione * de XLA_TRANSACTION_ENTITIES ordenar por entity_id desc

                 

                • Cristian Betancur

                  Hi Ravi

                  I executed SQL query with XLA_TRANSACTION_ENTITIES in English, and issue continues: I cannot see data in this table.

                  I executed other SQL queries (GL_LEDGERS, ra_customer_trx_lines_all ) and I can see data of these tables.

                  Language patch was applied three months ago.

                  Thanks.

    • Cristian Betancur

      Hi Ravi

      I executed SQL query with XLA_TRANSACTION_ENTITIES in English, and issue continues: I cannot see data in this table.

      I executed other SQL queries (GL_LEDGERS, ra_customer_trx_lines_all ) and I can see data of these tables.

      Language patch was applied three months ago.

      Thanks.

    • Pramod Yadav

      Hello Cristian-

      Following duty roles should be sufficient to allow access to XLA_TRANSACTION_ENTITIES data:

      Subledger Accounting Manager

      Subledger Accounting Transaction Analysis

      These are inherited by seeded roles Accounts Receivable Manager (for receivables) and Accounts Payable Manager (for Payables). If you already have any of these roles and necessary data access to these roles (BU /Reference data set), you should see data in this table.

      If you are using a custom role, then ensure that you assign the Duty roles mentioned above, to one of the custom role and the Custom role must have necessary data access (BU /Reference data set).

      Regards,

      Pramod

      • Cristian Betancur

        Hi Pramot

        My user has Accounts Payables Manager and Accounts Receivables Manager, with their data accesses. I have uploaded screenshots.

        If it is not a problem with roles, what kind of issue can it be? Set up in SLA and GL? Other roles in Release 13B? Bug?

        Thanks.

        Cristian.

         

        image_roles_for_users.jpg (108KB)
    • Ravi Kanth Vuddagiri

      Hello Cristian-I confirm having the Duty roles mentioned by Pramod in his last update. Please check the roles ur user has from where you are running the SQL.

    • Cristian Betancur

      Hi everyone

      Oracle Support found issue.

      I had roles and privileges related with SLA. However, from Release 13 19A, in the BI Publisher configuration, a default set up blocked access to SLA tables. 

      This is workaround:

      1) Navigate to BI Publisher Administration => Properties (under Runtime
      Configuration).
      2) Change the value of 'Run Enable FA Session Language Preference for
      Fusion DB' from 'True' to 'False'.
      3) Click 'Apply'.
      4) Re-run the same report using 'ApplicationDB_FSCM' data source.

      With this workaround, I can see data into XLA_TRANSACTION_ENTITIES.

      These are the bugs related with this issue:

       
      Bug 29611244 - BI REPORT WITH SQL QUERY ON SUBLEDGER SCHEMA (SLA) ARE NOT WORKING ANYMORE USING FSCM DATASOURCE
      Bug 30294750 - NO DATA FOUND IN XLA_TRANSACTION_ENTITIES

      Thanks.

      Cristian.

       

    • Jagdish Varadraj

      Hi Cristian

       

      The link for the bug is not working. can you give the Oracle support ink for this. 

      Also i tried the above work around but still it is not working for. 

      I just a BI Administrator role though purely for reporting needs will that be the problem ?

       

      THanks
      Jag

      • Cristian Betancur

        Hi Jag

        I cannot see bugs too. However, when I did workaround, I saw XLA_TRANSACTION_ENTITIES data.

        I have created Custom BI Administrator Role: Oracle Fusion BI: How to Add the BI Administrator Role to a user in Release 12 and higher. (Doc ID 2238277.1).

        If this work around does not work, you should open a new SR in Oracle Support, and attach this information.

        Thanks.

        Cristian.

        • Jagdish Varadraj

          Appreciate your response Cristian. 

          I did created cutost role BI Admin and followed the steps along with the workaround still no luck for me but when i added additional payables role with data security it is fetching data. 

          I will also log a SR and see what Oracle advice. 

          Thanks
          Jag

     

    community forum