HRS_JOBOPEN_VW

(SQL View)
Index Back

JOB OPEN HEADER VIEW

Header 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.