HRS_JOB_OPENING_IDOPRIDP=Continuous Job Opening
R=Standard Requisition
HRS_SA_JO_OC_I(SQL View) |
Index Back |
|---|---|
Job Openings Opened/ClosedThis view lists job openings WRT to their opened and closed data. |
| SELECT A2.HRS_JOB_OPENING_ID , OPR.OPRID , A2.HRS_PRM_PST_TITLE , A2.OPEN_DT , A2.RECRUITER_ID , COALESCE(( SELECT R.NAME_DISPLAY FROM PS_NAMES R WHERE R.EMPLID = A2.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)),' ') , A2.MANAGER_ID , COALESCE(( SELECT R.NAME_DISPLAY FROM PS_NAMES R WHERE R.EMPLID = A2.MANAGER_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)),' ') , A2.BUSINESS_UNIT , COALESCE(( SELECT BU.DESCR FROM PS_BUS_UNIT_TBL_HR BU WHERE BU.BUSINESS_UNIT = A2.BUSINESS_UNIT),' ') , A2.JOB_FAMILY , COALESCE(( SELECT JF.DESCR FROM PS_JOB_FAMILY_TBL JF WHERE JF.JOB_FAMILY = A2.JOB_FAMILY AND JF.EFFDT = ( SELECT MAX(JF1.EFFDT) FROM PS_JOB_FAMILY_TBL JF1 WHERE JF1.JOB_FAMILY = JF.JOB_FAMILY AND JF1.EFFDT <= %CurrentDateIn)),' ') , A2.DEPTID , COALESCE(( SELECT DEPT.DESCR FROM PS_DEPT_TBL DEPT WHERE DEPT.SETID = A2.SETID_DEPT AND DEPT.DEPTID = A2.DEPTID AND DEPT.EFFDT = ( SELECT MAX(DEPT1.EFFDT) FROM PS_DEPT_TBL DEPT1 WHERE DEPT1.SETID = DEPT.SETID AND DEPT1.DEPTID = DEPT.DEPTID AND DEPT1.EFFDT <= %CurrentDateIn)),' ') , A2.HRS_PRM_JOBCODE , COALESCE(( SELECT JC.DESCR FROM PS_JOBCODE_TBL JC WHERE JC.SETID = A2.SETID_DEPT AND JC.JOBCODE = A2.HRS_PRM_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)),' ') , A2.HRS_PRM_LOC_AREA , COALESCE(( SELECT L.DESCR FROM PS_HRS_LOCATION L WHERE L.HRS_LOCATION_ID = A2.HRS_PRM_LOC_AREA AND L.SETID = A2.SETID_DEPT),' ') , ( SELECT MESSAGE_TEXT FROM PSMSGCATDEFN WHERE MESSAGE_SET_NBR = 18176 AND MESSAGE_NBR = 6199) , %Sql(FUNCLIB_HRS_DATE_YEAR, A2.OPEN_DT)%Concat '-' %Concat %Sql(FUNCLIB_HRS_DATE_MONTH, A2.OPEN_DT), A2.HRS_PRM_POSITION, COALESCE(( SELECT POS.DESCR FROM PS_POSITION_DATA POS WHERE POS.POSITION_NBR = A2.HRS_PRM_POSITION AND POS.EFFDT = ( SELECT MAX(EFFDT) FROM PS_POSITION_DATA WHERE POSITION_NBR = POS.POSITION_NBR AND EFFDT <= %CurrentDateIn) AND POS.EFF_STATUS = 'A'), ' '), A2.HRS_JO_TYPE, %DateNull, %DateNull FROM PS_HRS_OPEN_JO_VW A2, PS_HRS_JO_TEAM TM, PSOPRDEFN OPR WHERE A2.OPEN_DT IS NOT NULL AND TM.HRS_JOB_OPENING_ID = A2.HRS_JOB_OPENING_ID AND OPR.EMPLID = TM.EMPLID AND TM.HRS_JO_TM_TYPE = 'M' UNION SELECT B.HRS_JOB_OPENING_ID , OPR1.OPRID , B.HRS_PRM_PST_TITLE , B.CLOSE_DT , B.RECRUITER_ID , COALESCE(( SELECT N.NAME_DISPLAY FROM PS_NAMES N WHERE N.EMPLID = B.RECRUITER_ID AND N.NAME_TYPE = 'PRI' AND N.EFFDT = ( SELECT MAX(N1.EFFDT) FROM PS_NAMES N1 WHERE N1.EMPLID = N.EMPLID AND N1.NAME_TYPE = N.NAME_TYPE AND N1.EFFDT <= %CurrentDateIn)),' ') , B.MANAGER_ID , COALESCE(( SELECT R.NAME_DISPLAY FROM PS_NAMES R WHERE R.EMPLID = B.MANAGER_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)),' ') , B.BUSINESS_UNIT , COALESCE(( SELECT BUS.DESCR FROM PS_BUS_UNIT_TBL_HR BUS WHERE BUS.BUSINESS_UNIT = B.BUSINESS_UNIT),' ') , B.JOB_FAMILY , COALESCE(( SELECT JFAM.DESCR FROM PS_JOB_FAMILY_TBL JFAM WHERE JFAM.JOB_FAMILY = B.JOB_FAMILY AND JFAM.EFFDT = ( SELECT MAX(JFAM1.EFFDT) FROM PS_JOB_FAMILY_TBL JFAM1 WHERE JFAM1.JOB_FAMILY = JFAM.JOB_FAMILY AND JFAM1.EFFDT <= %CurrentDateIn)),' ') , B.DEPTID , COALESCE(( SELECT DT.DESCR FROM PS_DEPT_TBL DT WHERE DT.SETID = B.SETID_DEPT AND DT.DEPTID = B.DEPTID AND DT.EFFDT = ( SELECT MAX(DT1.EFFDT) FROM PS_DEPT_TBL DT1 WHERE DT1.SETID = DT.SETID AND DT1.DEPTID = DT.DEPTID AND DT1.EFFDT <= %CurrentDateIn)),' ') , B.HRS_PRM_JOBCODE , COALESCE(( SELECT JOC.DESCR FROM PS_JOBCODE_TBL JOC WHERE JOC.SETID = B.SETID_DEPT AND JOC.JOBCODE = B.HRS_PRM_JOBCODE AND JOC.EFFDT = ( SELECT MAX(JOC1.EFFDT) FROM PS_JOBCODE_TBL JOC1 WHERE JOC1.SETID = JOC.SETID AND JOC1.JOBCODE = JOC.JOBCODE AND JOC1.EFFDT <= %CurrentDateIn)),' ') , B.HRS_PRM_LOC_AREA , COALESCE(( SELECT L.DESCR FROM PS_HRS_LOCATION L WHERE L.HRS_LOCATION_ID = B.HRS_PRM_LOC_AREA AND L.SETID = B.SETID_DEPT),' ') , ( SELECT MESSAGE_TEXT FROM PSMSGCATDEFN WHERE MESSAGE_SET_NBR = 18176 AND MESSAGE_NBR = 6200) , %Sql(FUNCLIB_HRS_DATE_YEAR, B.CLOSE_DT) %Concat '-' %Concat %Sql(FUNCLIB_HRS_DATE_MONTH, B.CLOSE_DT) , B.HRS_PRM_POSITION, ( SELECT POS.DESCR FROM PS_POSITION_DATA POS WHERE POS.POSITION_NBR = B.HRS_PRM_POSITION AND POS.EFFDT = ( SELECT MAX(EFFDT) FROM PS_POSITION_DATA WHERE POSITION_NBR = POS.POSITION_NBR AND EFFDT <= %CurrentDateIn) AND POS.EFF_STATUS = 'A'), B.HRS_JO_TYPE, %DateNull, %DateNull FROM PS_HRS_OPEN_JO_VW B, PS_HRS_JO_TEAM TM1, PSOPRDEFN OPR1 WHERE B.CLOSE_DT IS NOT NULL AND TM1.HRS_JOB_OPENING_ID = B.HRS_JOB_OPENING_ID AND OPR1.EMPLID = TM1.EMPLID AND TM1.HRS_JO_TM_TYPE = 'M' |
| # | PeopleSoft Field Name | PeopleSoft Field Type | Database Column Type | Description |
|---|---|---|---|---|
| 1 | HRS_JOB_OPENING_ID |
Number(15,0) | DECIMAL(15) NOT NULL | Job Opening Clone |
| 2 | OPRID |
Character(30) | VARCHAR2(30) NOT NULL | A user's ID (see PSOPRDEFN). |
| 3 | HRS_PRM_PST_TITLE | Character(200) | VARCHAR2(200) NOT NULL | Job posting title |
| 4 | OPEN_DT | Date(10) | DATE | Created |
| 5 | RECRUITER_ID | Character(11) | VARCHAR2(11) NOT NULL | Recruiter ID |
| 6 | NAME | Character(50) | VARCHAR2(50) NOT NULL | Name |
| 7 | MANAGER_ID | Character(11) | VARCHAR2(11) NOT NULL | Manager ID |
| 8 | NAME_DISPLAY | Character(50) | VARCHAR2(50) NOT NULL | Display Name - name formatted for Display based on the Country |
| 9 | BUSINESS_UNIT | Character(5) | VARCHAR2(5) NOT NULL | Business Unit |
| 10 | BUSINESS_DESCR | Character(60) | VARCHAR2(60) NOT NULL | Business Description |
| 11 | JOB_FAMILY | Character(6) | VARCHAR2(6) NOT NULL | Job Family |
| 12 | JOB_DESCR | Character(30) | VARCHAR2(30) NOT NULL | Job Description |
| 13 | DEPTID | Character(10) | VARCHAR2(10) NOT NULL | Department |
| 14 | DEPT_DESCR | Character(30) | VARCHAR2(30) NOT NULL | Department Description |
| 15 | JOBCODE | Character(6) | VARCHAR2(6) NOT NULL | Job Code |
| 16 | JOBCODE_DESCR | Character(30) | VARCHAR2(30) NOT NULL | Job Code Description |
| 17 | HRS_PRM_LOC_AREA | Number(15,0) | DECIMAL(15) NOT NULL | Primary recruiting location area |
| 18 | LOCATION_DESCR | Character(30) | VARCHAR2(30) NOT NULL | Location Description |
| 19 | DESCR | Character(30) | VARCHAR2(30) NOT NULL | Description |
| 20 | DESCR1 | Character(30) | VARCHAR2(30) NOT NULL | Descr |
| 21 | HRS_PRM_POSITION | Character(8) | VARCHAR2(8) NOT NULL | Position Number |
| 22 | POSN_DESCR | Character(30) | VARCHAR2(30) NOT NULL | Position Description |
| 23 | HRS_JO_TYPE | Character(1) | VARCHAR2(1) NOT NULL |
Job Opening Type
P=Continuous Job Opening R=Standard Requisition |
| 24 | FROM_DT | Date(10) | DATE | From Date |
| 25 | TO_DT | Date(10) | DATE | To Date |