Oracle Fusion HCM Analytics

Home› Oracle Analytics› Oracle Analytics Forums› Oracle Fusion Data Intelligence› Oracle Fusion HCM Analytics

Categories

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

I need help with building a report that shows the correct Projected Start Date for New Hires

497
Views
7
Comments
Ramy Atia
Ramy Atia Rank 1 - Community Starter
in Oracle Fusion HCM Analytics

Hi,

I have built a report to display expected hires, including employees' projected start dates and other relevant details. I retrieve the projected start date from the assignment with the ID in the ASSIGNMENT_OFFER_ID column in the IRC_OFFERS table.

However, when the projected start date changes with a pending worker assignment, I face difficulty retrieving that information because I'm unsure how to link the assignment in the offer table to the pending worker one.

Could someone assist me?


Thanks

Comments

  • Orange
    Orange Rank 5 - Community Champion

    Hi @Ramy Atia ,

    I suggest using table PER_PERIODS_OF_SERVICE instead of IRC_OFFERS.

    Kind regards

  • Ramy Atia
    Ramy Atia Rank 1 - Community Starter

    Thanks @Orange

    The challenge is that we would like to display some other info that is linked to the offer.

    Like Assignment Info and Other Compensation. Please see screenshot.

    Offer_20240117.jpg


  • Gina DiVenti
    Gina DiVenti Rank 5 - Community Champion

    Was there ever a resolution for this? Like Ramy, we would like to accurately report on the projected start date (including when it is changed) within the TA subject area.

  • JacobW
    JacobW Rank 1 - Community Starter

    I am facing the same issue. Not sure how I can link an assignment to an offer to get the correct projected start date.

  • Gina DiVenti
    Gina DiVenti Rank 5 - Community Champion

    @Orange Are there any updates with this?

  • Orange
    Orange Rank 5 - Community Champion

    IRC_OFFERS join PER_ALL_ASSIGNMENTS_M on PERSON_ID to find the PROJECTED_START_DATE.
    Assignment Type 'O' for Offer, 'P' for Pending Worker, 'E' for Employee, 'C' for Contingent Worker.

  • Orange
    Orange Rank 5 - Community Champion

    Please try the following:

    SELECT
    O.PERSON_ID, O.OFFER_NAME, O.ASSIGNMENT_OFFER_ID, A.ASSIGNMENT_ID, A.ASSIGNMENT_NUMBER, A.ASSIGNMENT_TYPE, TO_CHAR(A.EFFECTIVE_START_DATE, 'YYYY-MM-DD') EFF_STR_DATE, TO_CHAR(A.EFFECTIVE_END_DATE, 'YYYY-MM-DD') EFF_END_DATE, A.PERIOD_OF_SERVICE_ID, TO_CHAR(A. PROJECTED_START_DATE, 'YYYY-MM-DD') PROJECTED_STR_DATE
    FROM IRC_OFFERS O
    JOIN PER_ALL_ASSIGNMENTS_M A
    ON A.PERSON_ID = O.PERSON_ID
    WHERE A.ASSIGNMENT_TYPE IN ('O', 'P')
    AND A.PROJECTED_START_DATE > SYSDATE
    ORDER BY O.PERSON_ID, A.EFFECTIVE_START_DATE