HRS_JPST_VW(SQL View) |
Index Back |
|---|
| SELECT A.HRS_JOB_OPENING_ID , A.HRS_JO_PST_SEQ , A.HRS_JO_PST_TYPE , A.HRS_SOURCE_ID , A.STATUS_CODE , A.POSTING_TITLE , A.BUSINESS_UNIT ,A.SETID_DEPT , A.DEPTID ,COALESCE (F.DESCR , ' ') ,COALESCE (J.JOB_FAMILY , H.JOB_FAMILY , ' ') , COALESCE (J.HRS_DESCR , H.HRS_JOB_FAMILY , ' ') , COALESCE (H.JOB_FUNCTION , ' ') , COALESCE (B.NAME_DISPLAY , ' ') , COALESCE (C.NAME_DISPLAY , ' ') , COALESCE (D.COUNTRY , ' ') , COALESCE (D.COUNTRY_DESCR , ' ') , COALESCE (D.STATE , ' ') , COALESCE (D.STATE_DESCR , ' ') , COALESCE (D.CITY , ' ') ,A.LOCATION ,COALESCE (D.DESCR , ' ') , COALESCE (E.HRS_JO_MIN_RT , 0) , COALESCE (E.HRS_JO_MAX_RT , 0) , COALESCE (E.PAY_FREQ_ABBRV , ' ') , COALESCE (E.CURRENCY_CD , ' ') ,A.OPEN_DT ,%Cast(%DateOut(A.OPEN_DT) ,DATE ,CHARACTER) ,%DateIn(COALESCE(%DateOut(%Dateadd(A.CLOSE_DT, - 1)) , %DateOut(%DateIn('9999-12-12')))) ,A.LASTUPDDTTM FROM PS_HRS_JO_PSTBTH_I A LEFT OUTER JOIN PS_HRS_PER_NM_I1 B ON A.MANAGER_ID = B.EMPLID LEFT OUTER JOIN PS_SET_CNTRL_TBL I ON A.BUSINESS_UNIT = %Substring(I.SETCNTRLVALUE, 1, 5) LEFT OUTER JOIN PS_HRS_PER_NM_I1 C ON A.RECRUITER_ID = C.EMPLID LEFT OUTER JOIN PS_HRS_JO_LOC_DT_I D ON (A.LOCATION = D.LOCATION AND A.BUSINESS_UNIT = %Substring(D.SETCNTRLVALUE, 1, 5)) LEFT OUTER JOIN PS_HRS_JO_SALRANGE E ON (A.HRS_JOB_OPENING_ID = E.HRS_JOB_OPENING_ID AND A.SETID_DEPT = E.SETID AND A.JOBCODE = E.HRS_PRM_JOBCODE) LEFT OUTER JOIN PS_HRS_CUR_DEPT_VW F ON A.DEPTID = F.DEPTID AND F.SETID = A.SETID_DEPT LEFT OUTER JOIN PS_HRS_JBCD_FAM_I H ON (A.JOBCODE = H.JOBCODE AND A.BUSINESS_UNIT = %Substring(H.SETCNTRLVALUE, 1, 5)) LEFT OUTER JOIN PS_HRS_JOB_FAM_I1 J ON (A.JOB_FAMILY = J.JOB_FAMILY) WHERE (EXISTS ( SELECT 1 FROM PS_HRS_SRCHIDX_CTL CTL WHERE CTL.HRS_SRCH_INDEX_CD = 'J' AND A.OPEN_DT >= CTL.HRS_FROM_DATE) OR NOT EXISTS ( SELECT 1 FROM PS_HRS_SRCHIDX_CTL CTL WHERE CTL.HRS_SRCH_INDEX_CD = 'J' AND A.OPEN_DT <= %CurrentDateIn)) |
| # | 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 | HRS_JO_PST_SEQ |
Number(3,0) | SMALLINT NOT NULL | Job opening posting sequence |
| 3 | HRS_JO_PST_TYPE |
Character(1) | VARCHAR2(1) NOT NULL |
Job Opening Posting Type
C=Contingent Workforce E=External Posting I=Internal Posting L=Labor Office R=Recruitment |
| 4 | HRS_SOURCE_ID |
Number(15,0) | DECIMAL(15) NOT NULL | Source ID |
| 5 | STATUS_CODE | Character(3) | VARCHAR2(3) NOT NULL |
Identifies a status.
Prompt Table: HRS_STS_JO_VW |
| 6 | POSTING_TITLE | Character(200) | VARCHAR2(200) NOT NULL | Job posting title |
| 7 | BUSINESS_UNIT | Character(5) | VARCHAR2(5) NOT NULL |
Business Unit
Prompt Table: HRS_BS_UNT_HR_I |
| 8 | SETID_DEPT | Character(5) | VARCHAR2(5) NOT NULL | Department Set ID |
| 9 | DEPTID | Character(10) | VARCHAR2(10) NOT NULL | Department |
| 10 | DEPT_DESCR | Character(30) | VARCHAR2(30) NOT NULL | Department Description |
| 11 | JOB_FAMILY | Character(6) | VARCHAR2(6) NOT NULL | Job Family |
| 12 | HRS_JOB_FAMILY | Character(30) | VARCHAR2(30) NOT NULL | Job family description |
| 13 | JOB_FUNCTION | Character(3) | VARCHAR2(3) NOT NULL | Job Function |
| 14 | HRS_MGR_NAME | Character(50) | VARCHAR2(50) NOT NULL | Hiring Manager |
| 15 | RECRUITER_NAME | Character(50) | VARCHAR2(50) NOT NULL | Recruiter Name |
| 16 | COUNTRY | Character(3) | VARCHAR2(3) NOT NULL | Country |
| 17 | COUNTRY_DESCR | Character(30) | VARCHAR2(30) NOT NULL | Country Description |
| 18 | STATE | Character(6) | VARCHAR2(6) NOT NULL | State |
| 19 | STATE_DESCR | Character(30) | VARCHAR2(30) NOT NULL | State Description |
| 20 | CITY | Character(30) | VARCHAR2(30) NOT NULL | City |
| 21 | LOCATION | Character(10) | VARCHAR2(10) NOT NULL | Location Code |
| 22 | LOCATION_DESCR | Character(30) | VARCHAR2(30) NOT NULL | Location Description |
| 23 | HRS_JO_MIN_RT | Number(22,6) | DECIMAL(21,6) NOT NULL | Job opening minimum rate |
| 24 | HRS_JO_MAX_RT | Number(22,6) | DECIMAL(21,6) NOT NULL | Job opening maximum rate |
| 25 | PAY_FREQ_ABBRV | Character(1) | VARCHAR2(1) NOT NULL |
Pay Frequency
A=Annual B=Bi-weeky H=Hour M=Month Y=Year |
| 26 | CURRENCY_CD | Character(3) | VARCHAR2(3) NOT NULL | Currency Code |
| 27 | OPEN_DT | Date(10) | DATE | Created |
| 28 | DESCR30 | Character(30) | VARCHAR2(30) NOT NULL | Description |
| 29 | HRS_JO_PST_CLS_DT | Date(10) | DATE | Job opening post close date |
| 30 | LASTUPDDTTM | DateTime(26) | TIMESTAMP | Specifies the date and time of the last update to an entry. This field is maintained by PeopleSoft and is used in a variety of contexts. |