nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Oracle Communities

Oracle Analytics and AI

Child Item

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

Ramy Atia

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

Find more posts tagged with

Fusion HCM Analytics

Accepted answers

All comments

Orange

Hi @Ramy Atia ,

I suggest using table PER_PERIODS_OF_SERVICE instead of IRC_OFFERS.

Kind regards

Ramy Atia

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.

Gina DiVenti

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

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

@Orange Are there any updates with this?

Orange

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

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