• Post category:BI Publisher
  • Post comments:0 Comments
  • Post last modified:August 6, 2021
  • Reading time:2 mins read
You are currently viewing SQL Query to pull Legal Employer and Enterprise Hire Dates
SQL Query to pull Legal Employer and Enterprise Hire Dates
- SQL Query to pull Legal Employer and Enterprise Hire Dates
Total Visits: 14

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

image 16 1024x497 - SQL Query to pull Legal Employer and Enterprise Hire Dates

SQL Query:

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) 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 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_NUMBER

Sample Output will look like:

image 15 1024x455 - SQL Query to pull Legal Employer and Enterprise Hire Dates
If you like the content, please follow us on LinkedInFacebook, and Twitter to get updated with the latest content.