HRS_PG_DA_OFFR

(SQL View)
 Index Back

Applicant Offer Data

Applicant Offer data used for Application Diversity Analytics.

SELECT DISTINCT OFFR.HRS_PERSON_ID , OFFR.HRS_RCMNT_ID , OFFR.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 = OFFR.BUSINESS_UNIT) AND JC.JOBCODE = OFFR.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)),' ') , OFFR.POSITION_NBR , COALESCE (( SELECT POSN.DESCR FROM PS_POSITION_DATA POSN WHERE OFFR.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)),' ') , OFFR.RECRUITER_ID , COALESCE (( SELECT R.NAME_DISPLAY FROM PS_NAMES R WHERE R.EMPLID = OFFR.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)),' ') , OFFR.HRS_HIRING_MGR_ID ,' ', COALESCE (( SELECT R.NAME_DISPLAY FROM PS_NAMES R WHERE R.EMPLID = OFFR.HRS_HIRING_MGR_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)),' ') , OFFR.BUSINESS_UNIT , COALESCE (( SELECT BU.DESCR FROM PS_BUS_UNIT_TBL_HR BU WHERE BU.BUSINESS_UNIT = OFFR.BUSINESS_UNIT),' ') FROM PS_HRS_DAAPLN_STG1 JO, PS_HRS_OFFER OFFR, PS_HRS_RCMNT RCMNT WHERE JO.HRS_PERSON_ID = OFFR.HRS_PERSON_ID AND JO.HRS_RCMNT_ID = OFFR.HRS_RCMNT_ID AND OFFR.HRS_OFF_ID = ( SELECT MAX(OFFR1.HRS_OFF_ID) FROM PS_HRS_OFFER OFFR1 WHERE OFFR.HRS_PERSON_ID = OFFR1.HRS_PERSON_ID AND OFFR.HRS_RCMNT_ID = OFFR1.HRS_RCMNT_ID) AND JO.HRS_PERSON_ID = RCMNT.HRS_PERSON_ID AND JO.HRS_RCMNT_ID = RCMNT.HRS_RCMNT_ID AND RCMNT.STATUS_CODE < '080'

# 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_RCMNT_ID Number(15,0) DECIMAL(15) NOT NULL App Tracking Recruitment ID
3 JOBCODE Character(6) VARCHAR2(6) NOT NULL Job Code
4 JOBCODE_DESCR Character(30) VARCHAR2(30) NOT NULL Job Code Description
5 POSITION_NBR Character(8) VARCHAR2(8) NOT NULL Position Number
6 POSN_DESCR Character(30) VARCHAR2(30) NOT NULL Position Description
7 RECRUITER_ID Character(11) VARCHAR2(11) NOT NULL Recruiter ID
8 RECRUITER_NAME Character(50) VARCHAR2(50) NOT NULL Recruiter Name
9 HRS_HIRING_MGR_ID Character(11) VARCHAR2(11) NOT NULL Hiring Manager
10 MANAGER_NAME Character(30) VARCHAR2(30) NOT NULL Manager Name
11 HRS_MGR_NAME Character(50) VARCHAR2(50) NOT NULL Hiring Manager
12 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit
13 BUSINESS_DESCR Character(60) VARCHAR2(60) NOT NULL Business Description