LSQL - Connect Job Applicant and Employee Assignment
I am writing an LSQL analysis in OTBI which requires us to add the Hire Date for applicants who are hired. We're familiar with the Projected Start Date and even the Event Date when the State = 'Processed', but we've seen discrepancies even between the official hire date and the date when an applicant was processed. The only way we can find the official hire date is by using the "Workforce Management - Worker Assignment Events Real Time" subject area to pull in the Effective Start Date when the Action Code IN ('HIRE','REHIRE').
The challenge is trying to find an ID or other type of link between the Job Application and the Worker Assignment so that I can correctly JOIN the two subject areas. The best I can find currently is that the Candidate Person ID matches the Worker Person ID. However, this only joins the subject areas at the candidate level, not the job applicant level. With 100,000s of applicants, there are more than a few that have been hired and then rehired on different requisitions. So if I can only link the Candidate with the Worker Assignment,