HRS_PG_DA_HIRE2

(SQL View)
 Index Back

Applicant Hire Data

Applicant Hire data used for Diversity Analytics Overview.

SELECT DISTINCT HIRE.HRS_PERSON_ID , HIRE.HRS_PROFILE_SEQ , HIRE.HRS_RCMNT_ID , HIRE.JOBCODE , COALESCE (( SELECT JC.DESCR FROM PS_JOBCODE_TBL JC WHERE JC.SETID = ( SELECT SC.DEFAULT_SETID FROM PS_BUS_UNIT_TBL_HR SC WHERE SC.BUSINESS_UNIT = HIRE.BUSINESS_UNIT) AND JC.JOBCODE = HIRE.JOBCODE AND JC.EFFDT = ( SELECT MAX(JC1.EFFDT) FROM PS_JOBCODE_TBL JC1 WHERE JC1.SETID = JC.SETID AND JC1.JOBCODE = JC.JOBCODE AND JC1.EFFDT <= %CurrentDateIn)),' ') , HIRE.POSITION_NBR , COALESCE (( SELECT POSN.DESCR FROM PS_POSITION_DATA POSN WHERE HIRE.POSITION_NBR = POSN.POSITION_NBR AND POSN.EFFDT = ( SELECT MAX(POSN1.EFFDT) FROM PS_POSITION_DATA POSN1 WHERE POSN.POSITION_NBR = POSN1.POSITION_NBR AND POSN1.EFFDT <= %CurrentDateIn)),' ') , HIRE.RECRUITER_ID , COALESCE (( SELECT R.NAME_DISPLAY FROM PS_NAMES R WHERE R.EMPLID = HIRE.RECRUITER_ID AND R.NAME_TYPE = 'PRI' AND R.EFFDT = ( SELECT MAX(R1.EFFDT) FROM PS_NAMES R1 WHERE R1.EMPLID = R.EMPLID AND R1.NAME_TYPE = R.NAME_TYPE AND R1.EFFDT <= %CurrentDateIn)),' '), HIRE.BUSINESS_UNIT , COALESCE (( SELECT BU.DESCR FROM PS_BUS_UNIT_TBL_HR BU WHERE BU.BUSINESS_UNIT = HIRE.BUSINESS_UNIT),' ') , HIRE.DEPTID , COALESCE (( SELECT DEPT.DESCR FROM PS_DEPT_TBL DEPT WHERE DEPT.SETID = ( SELECT SC.DEFAULT_SETID FROM PS_BUS_UNIT_TBL_HR SC WHERE SC.BUSINESS_UNIT = HIRE.BUSINESS_UNIT) AND DEPT.DEPTID = HIRE.DEPTID AND %EffdtCheck(DEPT_TBL DEPT2, DEPT, %currentdatein) AND DEPT.EFF_STATUS = 'A'), ' ') FROM PS_HRS_DAAPLN_STG1 JO, PS_HRS_HIRE HIRE WHERE JO.HRS_PERSON_ID = HIRE.HRS_PERSON_ID AND HIRE.HRS_HIRE_SEQ = ( SELECT MAX(HIRE1.HRS_HIRE_SEQ) FROM PS_HRS_HIRE HIRE1 WHERE HIRE.HRS_PERSON_ID = HIRE1.HRS_PERSON_ID)

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 HRS_PERSON_ID Number(15,0) DECIMAL(15) NOT NULL Person Id
2 HRS_PROFILE_SEQ Number(3,0) SMALLINT NOT NULL Profile sequece
3 HRS_RCMNT_ID Number(15,0) DECIMAL(15) NOT NULL App Tracking Recruitment ID
4 JOBCODE Character(6) VARCHAR2(6) NOT NULL Job Code
5 JOBCODE_DESCR Character(30) VARCHAR2(30) NOT NULL Job Code Description
6 POSITION_NBR Character(8) VARCHAR2(8) NOT NULL Position Number
7 POSN_DESCR Character(30) VARCHAR2(30) NOT NULL Position Description
8 RECRUITER_ID Character(11) VARCHAR2(11) NOT NULL Recruiter ID
9 RECRUITER_NAME Character(50) VARCHAR2(50) NOT NULL Recruiter Name
10 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit
11 BUSINESS_DESCR Character(60) VARCHAR2(60) NOT NULL Business Description
12 DEPTID Character(10) VARCHAR2(10) NOT NULL Department
13 DEPT_DESCR Character(30) VARCHAR2(30) NOT NULL Department Description