HRS_JOBOPEN_VW(SQL View) |
Index Back |
---|---|
JOB OPEN HEADER VIEWHeader record for HTML email Approval - Job opening Resolution 909391 5/9/14: Replaced LOCATION with HRS_LOCATION_ID to display Recruiting Location instead of Location |
SELECT DISTINCT J.HRS_JOB_OPENING_ID , J.HRS_PRM_PST_TITLE , J.HRS_JO_TYPE , X.XLATLONGNAME , J.HRS_PRM_POSITION , POS.DESCR , J.BUSINESS_UNIT , BU.DESCR , J.HRS_PRM_JOBCODE , JCD.DESCR , J.JOB_FAMILY , JFM.DESCR , J.COMPANY , COM.DESCR , J.SETID_DEPT , J.HRS_PRM_LOC_AREA , LOC.DESCR , J.DEPTID , DPT.DESCR , Z.NAME_DISPLAY , Z1.NAME_DISPLAY , J.OPEN_DT , %Sql(HR_HT_EMAIL_DATE,J.OPEN_DT) , J.DESIRED_START_DT , %Sql(HR_HT_EMAIL_DATE,J.DESIRED_START_DT) , J.OPENINGS_TARGET , ' ' , CASE J.JOB_FAMILY WHEN ' ' THEN ' ' ELSE J.JOB_FAMILY %Concat ' (' %Concat JFM.DESCR %Concat ')' END , CASE J.HRS_PRM_POSITION WHEN ' ' THEN ' ' ELSE J.HRS_PRM_POSITION %Concat ' (' %Concat POS.DESCR %Concat ')' END , CASE J.BUSINESS_UNIT WHEN ' ' THEN ' ' ELSE J.BUSINESS_UNIT %Concat ' (' %Concat BU.DESCR %Concat ')' END , CASE J.HRS_PRM_JOBCODE WHEN ' ' THEN ' ' ELSE J.HRS_PRM_JOBCODE %Concat ' (' %Concat JCD.DESCR %Concat ')' END , CASE J.COMPANY WHEN ' ' THEN ' ' ELSE J.COMPANY %Concat ' (' %Concat COM.DESCR %Concat ')' END , 'X' FROM PS_HRS_JOB_OPENING J LEFT OUTER JOIN PS_HRS_PERSON_NM_I Z ON Z.EMPLID = J.RECRUITER_ID LEFT OUTER JOIN PS_HRS_PERSON_NM_I Z1 ON Z1.EMPLID = J.MANAGER_ID LEFT OUTER JOIN PS_HRS_POS_DTA_VW POS ON POS.POSITION_NBR = J.HRS_PRM_POSITION LEFT OUTER JOIN PS_JOB_FAMILY_EFVW JFM ON JFM.JOB_FAMILY = J.JOB_FAMILY LEFT OUTER JOIN PS_HRS_COMPANY_I2 COM ON COM.COMPANY = J.COMPANY LEFT OUTER JOIN PS_HRS_RECR_LOC_I LOC ON LOC.SETID = J.SETID_DEPT AND LOC.HRS_LOCATION_ID = J.HRS_PRM_LOC_AREA LEFT OUTER JOIN PS_HRS_DEPT_VW2 DPT ON DPT.SETID = J.SETID_DEPT AND DPT.DEPTID = J.DEPTID LEFT OUTER JOIN PS_JOBCODE_TBL_VW JCD ON JCD.SETID = J.SETID_DEPT AND JCD.JOBCODE = J.HRS_PRM_JOBCODE , PS_BUS_UNIT_TBL_HR BU , PS_HRS_XLAT_EFF_VW X WHERE BU.BUSINESS_UNIT = J.BUSINESS_UNIT AND X.FIELDNAME = 'HRS_JO_TYPE' AND X.FIELDVALUE = J.HRS_JO_TYPE |
# | 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 | POSTING_TITLE | Character(200) | VARCHAR2(200) NOT NULL | Job posting title |
3 | HRS_JO_TYPE | Character(1) | VARCHAR2(1) NOT NULL |
Job Opening Type
P=Continuous Job Opening R=Standard Requisition |
4 | XLATLONGNAME | Character(30) | VARCHAR2(30) NOT NULL | Translate Long Name |
5 | POSITION_NBR | Character(8) | VARCHAR2(8) NOT NULL | Position Number |
6 | DESCR6 | Character(30) | VARCHAR2(30) NOT NULL | This field is used in a complex query that shows many descr fields. |
7 | BUSINESS_UNIT | Character(5) | VARCHAR2(5) NOT NULL | Business Unit |
8 | DESCR2 | Character(30) | VARCHAR2(30) NOT NULL | Descr2 |
9 | JOBCODE | Character(6) | VARCHAR2(6) NOT NULL | Job Code |
10 | JOBTITLE | Character(30) | VARCHAR2(30) NOT NULL | Job Title |
11 | JOB_FAMILY | Character(6) | VARCHAR2(6) NOT NULL | Job Family |
12 | DESCR1 | Character(30) | VARCHAR2(30) NOT NULL | Descr |
13 | COMPANY | Character(3) | VARCHAR2(3) NOT NULL | Company |
14 | DESCR3 | Character(30) | VARCHAR2(30) NOT NULL | Descr 3 |
15 | SETID_DEPT | Character(5) | VARCHAR2(5) NOT NULL | Department Set ID |
16 | HRS_LOCATION_ID | Number(15,0) | DECIMAL(15) NOT NULL | Recruiting location area |
17 | DESCR4 | Character(30) | VARCHAR2(30) NOT NULL | Descr 4 |
18 | DEPTID | Character(10) | VARCHAR2(10) NOT NULL | Department |
19 | DESCR5 | Character(30) | VARCHAR2(30) NOT NULL | This field is used in a complex query that shows many descr fields. |
20 | NAME_DISPLAY | Character(50) | VARCHAR2(50) NOT NULL | Display Name - name formatted for Display based on the Country |
21 | NAME_DESCR | Character(50) | VARCHAR2(50) NOT NULL | Description |
22 | OPEN_DT | Date(10) | DATE | Created |
23 | DESCR25 | Character(25) | VARCHAR2(25) NOT NULL | Short description |
24 | DESIRED_START_DT | Date(10) | DATE | Desired Start Date |
25 | DESCR20 | Character(20) | VARCHAR2(20) NOT NULL | Description |
26 | OPENINGS_TARGET | Number(3,0) | SMALLINT NOT NULL | Tracks number of target openings |
27 | DESCR254 | Character(254) | VARCHAR2(254) NOT NULL | Description of length 254 |
28 | DESCR40 | Character(40) | VARCHAR2(40) NOT NULL | Description |
29 | DESCR45 | Character(45) | VARCHAR2(45) NOT NULL | Description |
30 | DESCR50 | Character(50) | VARCHAR2(50) NOT NULL | Description of length 50 |
31 | DESCR50_1 | Character(50) | VARCHAR2(50) NOT NULL | Description of length 50 |
32 | DESCR50_HR1 | Character(50) | VARCHAR2(50) NOT NULL | Description of length 50 |
33 | DESCR_COMMENTS_HIS | Long Character | CLOB | This field is used in a complex query that shows many descr fields. |