In this article, we will look into pulling the Enterprise and Legal Employer seniority dates for employees.
If you haven’t heard of these dates, please go through our previous article to know the details.
We can find these dates on the Work Relationship screen for an employee.
My Client Groups > Person Management
Search for an Employee
SELECT
B.PERSON_NUMBER,
D.ASSIGNMENT_NUMBER,
D.ASSIGNMENT_STATUS_TYPE,
D.ASSIGNMENT_SEQUENCE,
TO_CHAR(A.START_DATE, 'MM/DD/YYYY') AS PERSON_START_DATE,
TO_CHAR(A.DATE_OF_BIRTH, 'MM/DD/YYYY') AS DATE_OF_BIRTH,
(SELECT TO_CHAR(MIN(E.DATE_START),'MM/DD/YYYY') FROM PER_PERIODS_OF_SERVICE E
WHERE E.PERSON_ID = A.PERSON_ID AND PERIOD_TYPE = 'E') AS ENTERPRISE_HIRE_DATE,
TO_CHAR(C.ORIGINAL_DATE_OF_HIRE, 'MM/DD/YYYY') AS ENTERPRISE_SENIORITY_DATE,
TO_CHAR(C.DATE_START, 'MM/DD/YYYY') AS LEGAL_EMPLOYER_HIRE_DATE,
TO_CHAR(C.ADJUSTED_SVC_DATE, 'MM/DD/YYYY') AS LEGAL_EMPLOYER_SENIORITY_DATE
FROM PER_PERSONS A ,
PER_ALL_PEOPLE_F B,
PER_PERIODS_OF_SERVICE C,
PER_ALL_ASSIGNMENTS_F D
WHERE A.PERSON_ID = B.PERSON_ID
AND A.PERSON_ID = C.PERSON_ID
AND A.PERSON_ID = D.PERSON_ID
AND C.PERIOD_OF_SERVICE_ID = D.PERIOD_OF_SERVICE_ID
AND C.PERIOD_TYPE = 'E'
AND SYSDATE BETWEEN B.EFFECTIVE_START_DATE AND B.EFFECTIVE_END_DATE
AND SYSDATE BETWEEN D.EFFECTIVE_START_DATE AND D.EFFECTIVE_END_DATE
AND D.ASSIGNMENT_TYPE = 'E'
AND D.PRIMARY_FLAG = 'Y'
AND D.EFFECTIVE_LATEST_CHANGE = 'Y'
AND D.ASSIGNMENT_STATUS_TYPE = 'ACTIVE'
ORDER BY B.PERSON_NUMBERSample Output will look like:
