HRS_PG_JO_OC_M

(SQL View)
 Index Back

Job Openings Opened/Closed

This view lists job openings WRT to their opened and closed data according to the security setup in Direct Line Reports.

SELECT A.HRS_JOB_OPENING_ID , OPR.OPRID ,'O' , A.HRS_PRM_PST_TITLE , A.OPEN_DT , A.RECRUITER_ID , COALESCE(( SELECT R.NAME_DISPLAY FROM PS_NAMES R WHERE R.EMPLID = A.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)),' ') , A.BUSINESS_UNIT , COALESCE(( SELECT BU.DESCR FROM PS_BUS_UNIT_TBL_HR BU WHERE BU.BUSINESS_UNIT = A.BUSINESS_UNIT),' ') , A.JOB_FAMILY , COALESCE(( SELECT JF.DESCR FROM PS_JOB_FAMILY_TBL JF WHERE JF.JOB_FAMILY = A.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)),' ') , A.DEPTID , COALESCE(( SELECT DEPT.DESCR FROM PS_DEPT_TBL DEPT WHERE DEPT.SETID = A.SETID_DEPT AND DEPT.DEPTID = A.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)),' ') , A.HRS_PRM_JOBCODE , COALESCE(( SELECT JC.DESCR FROM PS_JOBCODE_TBL JC WHERE JC.SETID = A.SETID_DEPT AND JC.JOBCODE = A.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)),' ') , A.HRS_PRM_LOCATION , COALESCE(( SELECT LOC.DESCR FROM PS_LOCATION_TBL LOC WHERE LOC.SETID = A.SETID_DEPT AND LOC.LOCATION = A.HRS_PRM_LOCATION AND LOC.EFFDT = ( SELECT MAX(LOC1.EFFDT) FROM PS_LOCATION_TBL LOC1 WHERE LOC1.SETID = LOC.SETID AND LOC1.LOCATION = LOC.LOCATION AND LOC1.EFFDT <= %CurrentDateIn)),' ') , COALESCE(( SELECT LC.COUNTRY FROM PS_LOCATION_TBL LC WHERE LC.SETID = A.SETID_DEPT AND LC.LOCATION = A.HRS_PRM_LOCATION AND LC.EFFDT = ( SELECT MAX(LC1.EFFDT) FROM PS_LOCATION_TBL LC1 WHERE LC1.SETID = LC.SETID AND LC1.LOCATION = LC.LOCATION AND LC1.EFFDT <= %CurrentDateIn)),' ') , COALESCE(( SELECT C.DESCR FROM PS_COUNTRY_TBL C WHERE C.COUNTRY = ( SELECT LC.COUNTRY FROM PS_LOCATION_TBL LC WHERE LC.SETID = A.SETID_DEPT AND LC.LOCATION = A.HRS_PRM_LOCATION AND LC.EFFDT = ( SELECT MAX(LC1.EFFDT) FROM PS_LOCATION_TBL LC1 WHERE LC1.SETID = LC.SETID AND LC1.LOCATION = LC.LOCATION AND LC1.EFFDT <= %CurrentDateIn))),' ') , ( SELECT MESSAGE_TEXT FROM PSMSGCATDEFN WHERE MESSAGE_SET_NBR = 18176 AND MESSAGE_NBR = 6199) ,%Sql(FUNCLIB_HRS_DATE_YEAR, A.OPEN_DT)%Concat '-' %Concat %Sql(FUNCLIB_HRS_DATE_MONTH, A.OPEN_DT), %DateNull, %DateNull FROM PS_HRS_PG_JO_OPN_I A, PS_HRS_EMPL_SEC_I DIR, PSOPRDEFN OPR WHERE A.RECRUITER_ID = DIR.EMPLID AND DIR.SUPERVISOR_ID = OPR.EMPLID AND DIR.ACCESS_TYPE = ( SELECT ATYPE.ACCESS_TYPE FROM PS_SS_LINK_TBL ATYPE WHERE ATYPE.PNLGRPNAME = 'HRS_PE_MGR_SUMM' AND ATYPE.MARKET = 'GBL') UNION ALL SELECT B.HRS_JOB_OPENING_ID , OPR.OPRID ,'C' , 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.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_LOCATION ,COALESCE( ( SELECT LOCN.DESCR FROM PS_LOCATION_TBL LOCN WHERE LOCN.SETID = B.SETID_DEPT AND LOCN.LOCATION = B.HRS_PRM_LOCATION AND LOCN.EFFDT = ( SELECT MAX(LOCN1.EFFDT) FROM PS_LOCATION_TBL LOCN1 WHERE LOCN1.SETID = LOCN.SETID AND LOCN1.LOCATION = LOCN.LOCATION AND LOCN1.EFFDT <= %CurrentDateIn)),' ') , COALESCE(( SELECT LCN.COUNTRY FROM PS_LOCATION_TBL LCN WHERE LCN.SETID = B.SETID_DEPT AND LCN.LOCATION = B.HRS_PRM_LOCATION AND LCN.EFFDT = ( SELECT MAX(LCN1.EFFDT) FROM PS_LOCATION_TBL LCN1 WHERE LCN1.SETID = LCN.SETID AND LCN1.LOCATION = LCN.LOCATION AND LCN1.EFFDT <= %CurrentDateIn)),' ') , COALESCE(( SELECT CN.DESCR FROM PS_COUNTRY_TBL CN WHERE CN.COUNTRY = ( SELECT LC1.COUNTRY FROM PS_LOCATION_TBL LC1 WHERE LC1.SETID = B.SETID_DEPT AND LC1.LOCATION = B.HRS_PRM_LOCATION AND LC1.EFFDT = ( SELECT MAX(LC2.EFFDT) FROM PS_LOCATION_TBL LC2 WHERE LC2.SETID = LC1.SETID AND LC2.LOCATION = LC1.LOCATION AND LC2.EFFDT <= %CurrentDateIn))),' ') , ( 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), %DateNull, %DateNull FROM PS_HRS_JOB_OPENING B, PS_HRS_EMPL_SEC_I DIR, PSOPRDEFN OPR WHERE B.RECRUITER_ID = DIR.EMPLID AND DIR.SUPERVISOR_ID = OPR.EMPLID AND DIR.ACCESS_TYPE = ( SELECT ATYPE.ACCESS_TYPE FROM PS_SS_LINK_TBL ATYPE WHERE ATYPE.PNLGRPNAME = 'HRS_PE_MGR_SUMM' AND ATYPE.MARKET = 'GBL') AND B.CLOSE_DT IS NOT NULL

  • Related Language Record: HRS_PG_JOOCM_LN

    • # 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 FLAG Character(1) VARCHAR2(1) NOT NULL Flag
    4 HRS_PRM_PST_TITLE Character(200) VARCHAR2(200) NOT NULL Job posting title
    5 OPEN_DT Date(10) DATE Created
    6 RECRUITER_ID Character(11) VARCHAR2(11) NOT NULL Recruiter ID
    7 NAME Character(50) VARCHAR2(50) NOT NULL Name
    8 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit
    9 BUSINESS_DESCR Character(60) VARCHAR2(60) NOT NULL Business Description
    10 JOB_FAMILY Character(6) VARCHAR2(6) NOT NULL Job Family
    11 JOB_DESCR Character(30) VARCHAR2(30) NOT NULL Job Description
    12 DEPTID Character(10) VARCHAR2(10) NOT NULL Department
    13 DEPT_DESCR Character(30) VARCHAR2(30) NOT NULL Department Description
    14 JOBCODE Character(6) VARCHAR2(6) NOT NULL Job Code
    15 JOBCODE_DESCR Character(30) VARCHAR2(30) NOT NULL Job Code Description
    16 LOCATION Character(10) VARCHAR2(10) NOT NULL Location Code
    17 LOCATION_DESCR Character(30) VARCHAR2(30) NOT NULL Location Description
    18 COUNTRY Character(3) VARCHAR2(3) NOT NULL Country
    19 COUNTRY_DESCR Character(30) VARCHAR2(30) NOT NULL Country Description
    20 DESCR1 Character(30) VARCHAR2(30) NOT NULL Descr
    21 DESCR Character(30) VARCHAR2(30) NOT NULL Description
    22 FROM_DT Date(10) DATE From Date
    23 TO_DT Date(10) DATE To Date