Skip to content
  • Register

    • Oracle Transactional Business Intelligence

    Home› Oracle Analytics› Oracle Analytics Forums› More Solutions› Oracle Transactional Business Intelligence

    Welcome!

    Sign in to share your experience and connect with the community.
    Log In
    Register

    Quick Links

    Categories

    Welcome to the Oracle Analytics Community: Please complete your User Profile and upload your Profile Picture

    Inquiry About CMP_CWB_HRCHY_CF_DN Table Population in Oracle Fusion HCM

    Accepted answer
    41
    Views
    6
    Comments
    Rank 4 - Community Specialist
    edited Mar 5, 2025 7:42PM in Oracle Transactional Business Intelligence

    Hi,

     I am currently working on the OTBI subject area "Compensation - Workforce Compensation Real Time" and need more details regarding the CMP_CWB_HRCHY_CF_DN table. 
    Specifically, I would like to understand:

     What process or job populates this table?
    How often is it updated, and is there any process to run for refresh it?
    Are there specific configurations or setups in Compensation Management that influence how this table is populated?

     I would appreciate any insights or references to Oracle documentation that could clarify these points.

     Thank you in advance for your support.

     Best regards

    Tagged:

    Welcome!

    It looks like you're new here. Sign in or register to get started.
    Log In
    Register

    Best Answer

    Answers

    • Rank 4 - Community Specialist

      Hi,

      Thank you so much for your prompt and helpful response! I really appreciate the clarification regarding the Synchronize Hierarchy process and its role in populating the CMP_CWB_HRCHY_CF_DN table.

      Thanks again for your support!

      Best regards

    • Rank 4 - Community Specialist

      Hi @MandeepGupta,

      Last week, we ran the "Synchronize Hierarchy" process to update the CMP_CWB_HRCHY_CF_DN table. After this activity, the report was functioning correctly for the processed plan and period, and the data was present in the table.

      However, since yesterday, something is not working anymore, and the data for the processed period is no longer available in the table. We tried to rerun the "Synchronize Hierarchy" process, which had not been executed again.

      The first time, we ran it without checking the "Force refresh to rebuild the hierarchy even though the synchronization process was run" option, and the logs indicated that the data was processed, but there was no trace of it in the table. On the second run, we used the 'Force refresh to rebuild the hierarchy even though the synchronization process was run' option, and the data was extracted into the table, allowing the report to retrieve them.

      Additionally, we are encountering a strange error that we have never seen before: "Failed to load SQL." This occurs when we try to create a Data Model with a simple query like "SELECT DISTINCT TOP_PERIOD_ID FROM CMP_CWB_HRCHY_CF_DN" or "SELECT DISTINCT TOP_PLAN_ID FROM CMP_CWB_HRCHY_CF_DN."

      2025-03-12 11_14_45-Untitled - Oracle Analytics Publisher _ Data Model — Mozilla Firefox.png

      Have you ever encountered a similar issue?

      Is there any other process that could affect the loading of the CMP_CWB_HRCHY_CF_DN table?

      Thank you very much in advance!

    • Rank 8 - Analytics Strategist
      AM HRE

      Thanks for reaching out.

      Looping in @Gail Langendorf-Oracle for inputs on the synchronize process issue. \

      Hi Gail,

      Hope you are doing well. Can you please help here?

      Hi @AM HRE ,

      The data model issue seems to be temporary. Are you getting the issue only for comp specific tables? Can you try to create one for select * from per_all_people_f?

      Thanks.

    • Rank 4 - Community Specialist

      Hi @MandeepGupta,

      Thank you for your response.

      I have conducted several tests, and I am encountering the error exclusively with the CMP_CWB_HRCHY_CF_DN table. Specifically, the error arises when I use fields that start with "TOP_", such as TOP_MGR_PERSON_ID, TOP_MGR_PERSON_EVENT_ID, TOP_PLAN_ID, or TOP_PERIOD_ID.

      I can create a Data Model without any issues using queries like "SELECT * FROM PER_ALL_PEOPLE_F" or "SELECT * FROM CMP_CWB_HRCHY_CF_DN." However, when I attempt to create a Data Model with a SELECT query that includes any of the TOP_ fields (e.g., "SELECT TOP_PERIOD_ID, PERSON_ID FROM CMP_CWB_HRCHY_CF_DN"), I receive the error "Failed to load SQL." In contrast, if I use a query like "SELECT PERSON_ID, HRCHY_CF_DN_ID, ASSIGNMENT_ID FROM CMP_CWB_HRCHY_CF_DN," no error occurs.

      Once I receive the error, after the click on the OK button in the DataSet, the Data Model becomes unusable, and I have to reload the page.

      Do you have any idea what the problem might be?

      If you can run a test, are you experiencing the same problem?

      Thanks

    • Rank 5 - Community Champion

      Guessing the question to me is about this: "The first time, we ran it without checking the "Force refresh to rebuild the hierarchy even though the synchronization process was run" option, and the logs indicated that the data was processed, but there was no trace of it in the table. On the second run, we used the 'Force refresh to rebuild the hierarchy even though the synchronization process was run' option, and the data was extracted into the table, allowing the report to retrieve them."

      The answer is that it depends. In addition to normal mode, there are 2 options for this process.
      -Force refresh to rebuild the hierarchy even though the synchronization process was run
      -Purge hierarchy to remove a hierarchy that's no longer needed, for example to remove old cycles not required in OTBI
      What's needed depends on what happened in the application. If you never refresh your hierarchy and your manager window is short, then you will only need to run the process once. If you're testing, rebuilding your hierarchy in the application, migrating environments, or running your Refresh process nightly, then you need to update OTBI accordingly, and these options allow you to do that.

    Welcome!

    It looks like you're new here. Sign in or register to get started.
    Log In
    Register