HRS_JO_BASE_VW2

(SQL View)
 Index Back

Ext and Int Job Openings

Job openings with postings that are available for external and internal applicants.

SELECT A.HRS_JOB_OPENING_ID , A.SETID_DEPT , A.DEPTID , ( SELECT DEPT.DESCR FROM PS_DEPT_TBL DEPT WHERE DEPT.SETID = A.SETID_DEPT AND DEPT.DEPTID = A.DEPTID AND DEPT.EFF_STATUS = 'A' AND %EffdtCheck(DEPT_TBL DEPT1, DEPT, %CURRENTDATEIN)), COALESCE (%TrimSubstr(A.JOB_FAMILY,1,6),JOBFAM.JOB_FAMILY, ' ') , COALESCE(( SELECT JF.DESCR FROM PS_JOB_FAMILY_TBL JF WHERE JF.JOB_FAMILY = A.JOB_FAMILY AND JF.EFF_STATUS = 'A' AND %EffdtCheck(JOB_FAMILY_TBL JF1, JF, %CURRENTDATEIN)),JOBFAM.HRS_JOB_FAMILY ,' '), ( SELECT LOC.COUNTRY FROM PS_LOCATION_TBL LOC WHERE LOC.SETID = A.SETID_DEPT AND LOC.LOCATION = A.HRS_PRM_LOCATION AND LOC.EFF_STATUS = 'A' AND %EffdtCheck(LOCATION_TBL LOC1, LOC, %CURRENTDATEIN)), ( SELECT CN.DESCR FROM PS_COUNTRY_TBL CN WHERE CN.COUNTRY = ( SELECT LOC.COUNTRY FROM PS_LOCATION_TBL LOC WHERE LOC.SETID = A.SETID_DEPT AND LOC.LOCATION = A.HRS_PRM_LOCATION AND LOC.EFF_STATUS = 'A' AND %EffdtCheck(LOCATION_TBL LOC1, LOC, %CURRENTDATEIN))) , ( SELECT LOC.STATE FROM PS_LOCATION_TBL LOC WHERE LOC.SETID = A.SETID_DEPT AND LOC.LOCATION = A.HRS_PRM_LOCATION AND LOC.EFF_STATUS = 'A' AND %EffdtCheck(LOCATION_TBL LOC1, LOC, %CURRENTDATEIN)), ( SELECT ST.DESCR FROM PS_STATE_TBL ST WHERE ST.COUNTRY = ( SELECT LOC.COUNTRY FROM PS_LOCATION_TBL LOC WHERE LOC.SETID = A.SETID_DEPT AND LOC.LOCATION = A.HRS_PRM_LOCATION AND LOC.EFF_STATUS = 'A' AND %EffdtCheck (LOCATION_TBL LOC1, LOC, %CURRENTDATEIN)) AND ST.STATE = ( SELECT LOC.STATE FROM PS_LOCATION_TBL LOC WHERE LOC.SETID = A.SETID_DEPT AND LOC.LOCATION = A.HRS_PRM_LOCATION AND LOC.EFF_STATUS = 'A' AND %EffdtCheck (LOCATION_TBL LOC1, LOC, %CURRENTDATEIN))) , ( SELECT LOC.CITY FROM PS_LOCATION_TBL LOC WHERE LOC.SETID = A.SETID_DEPT AND LOC.LOCATION = A.HRS_PRM_LOCATION AND LOC.EFF_STATUS = 'A' AND %EffdtCheck(LOCATION_TBL LOC1, LOC, %CURRENTDATEIN)), A.HRS_PRM_LOCATION , ( SELECT LOC.DESCR FROM ps_LOCATION_TBL LOC WHERE LOC.SETID = A.SETID_DEPT AND LOC.LOCATION = A.HRS_PRM_LOCATION AND LOC.EFF_STATUS = 'A' AND %EffdtCheck(LOCATION_TBL LOC1, LOC, %CURRENTDATEIN)) , A.HRS_PRM_LOC_AREA , E.DESCR FROM PS_HRS_JOB_OPENING A LEFT OUTER JOIN PS_SET_CNTRL_GROUP D ON A.BUSINESS_UNIT = %Substring(D.SETCNTRLVALUE ,1 ,5) AND D.REC_GROUP_ID = 'RS_LOC' LEFT OUTER JOIN PS_HRS_LOCATION E ON D.SETID = E.SETID AND A.HRS_PRM_LOC_AREA = E.HRS_LOCATION_ID LEFT OUTER JOIN PS_HRS_JBCD_FAM_I JOBFAM ON (A.HRS_PRM_JOBCODE = JOBFAM.JOBCODE AND A.BUSINESS_UNIT = %Substring(JOBFAM.SETCNTRLVALUE, 1, 5)), PSPNLGRPDEFN PNL WHERE PNL.PNLGRPNAME = 'HRS_JO_360'

  • Related Language Record: HRS_JO_BASE_LN2

    • # 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

    Prompt Table: HRS_JO_I

    2 SETID_DEPT Character(5) VARCHAR2(5) NOT NULL Department Set ID
    3 DEPTID Character(10) VARCHAR2(10) NOT NULL Department
    4 DEPT_DESCR Character(30) VARCHAR2(30) NOT NULL Department
    5 JOB_FAMILY Character(6) VARCHAR2(6) NOT NULL Job Family
    6 HRS_JOB_FAMILY Character(30) VARCHAR2(30) NOT NULL Job family description
    7 COUNTRY Character(3) VARCHAR2(3) NOT NULL Country
    8 COUNTRY_DESCR Character(30) VARCHAR2(30) NOT NULL Country Description
    9 STATE Character(6) VARCHAR2(6) NOT NULL State
    10 STATE_DESCR Character(30) VARCHAR2(30) NOT NULL State Description
    11 CITY Character(30) VARCHAR2(30) NOT NULL City
    12 LOCATION Character(10) VARCHAR2(10) NOT NULL Location Code
    13 LOCATION_DESCR Character(30) VARCHAR2(30) NOT NULL Location Description
    14 HRS_PRM_LOC_AREA Number(15,0) DECIMAL(15) NOT NULL Primary recruiting location area
    15 HRS_RECR_LOCATION Character(30) VARCHAR2(30) NOT NULL Job opening recruiting location