POSN_PRI_VW

(SQL View)
 Index Back

Main View for Primary Incumben

View to pull incumbents for Primary Incumbeny feature

SELECT A.POSITION_NBR ,A.POSN_PRI_START_DT , J.EFFDT , A.EMPLID ,A.EMPL_RCD , A.NAME_DISPLAY ,A.EMPLID %Concat '-' %Concat %NumToChar(A.EMPL_RCD) , A.PRIMARY_INCUMB ,A.SYSTEM_ASSIGNED FROM PS_POSN_PRI_INCUMB A ,PS_JOB J ,PS_POSITION_DATA_E E WHERE A.POSITION_NBR=J.POSITION_NBR AND A.EMPLID=J.EMPLID AND A.EMPL_RCD=J.EMPL_RCD AND A.POSITION_NBR=E.POSITION_NBR AND J.HR_STATUS = 'A' AND J.EFFDT = ( SELECT MAX(C.EFFDT) FROM PS_JOB C WHERE C.EMPLID = J.EMPLID AND C.EMPL_RCD = J.EMPL_RCD AND C.EFFDT <= %CurrentDateIn) AND J.EFFSEQ = ( SELECT MAX(D.EFFSEQ) FROM PS_JOB D WHERE D.EMPLID = J.EMPLID AND D.EMPL_RCD = J.EMPL_RCD AND D.EFFDT = J.EFFDT) AND E.EFFDT= ( SELECT MAX(D.EFFDT) FROM PS_POSITION_DATA_E D WHERE A.POSITION_NBR = D.POSITION_NBR ) AND E.EFFSEQ =( SELECT MAX(EF.EFFSEQ) FROM PS_POSITION_DATA_E EF WHERE A.POSITION_NBR = EF.POSITION_NBR AND EF.EFFDT=E.EFFDT ) UNION SELECT A.POSITION_NBR ,A.POSN_PRI_START_DT , J.EFFDT , A.EMPLID ,A.EMPL_RCD , A.NAME_DISPLAY ,A.EMPLID %Concat '-' %Concat %NumToChar(A.EMPL_RCD) , A.PRIMARY_INCUMB ,A.SYSTEM_ASSIGNED FROM PS_POSN_PRI_INCUMB A ,PS_JOB J ,PS_POSITION_DATA_E E WHERE A.POSITION_NBR=J.POSITION_NBR AND A.EMPLID=J.EMPLID AND A.EMPL_RCD=J.EMPL_RCD AND A.POSITION_NBR=E.POSITION_NBR AND J.HR_STATUS = 'A' AND J.EFFDT = ( SELECT MAX(C.EFFDT) FROM PS_JOB C WHERE C.EMPLID = J.EMPLID AND C.EMPL_RCD = J.EMPL_RCD AND C.EFFDT > %CurrentDateIn) AND J.EFFSEQ = ( SELECT MAX(D.EFFSEQ) FROM PS_JOB D WHERE D.EMPLID = J.EMPLID AND D.EMPL_RCD = J.EMPL_RCD AND D.EFFDT = J.EFFDT) AND E.EFFDT= ( SELECT MAX(D.EFFDT) FROM PS_POSITION_DATA_E D WHERE A.POSITION_NBR = D.POSITION_NBR ) AND E.EFFSEQ =( SELECT MAX(EF.EFFSEQ) FROM PS_POSITION_DATA_E EF WHERE A.POSITION_NBR = EF.POSITION_NBR AND EF.EFFDT=E.EFFDT ) AND NOT EXISTS ( SELECT 'X' FROM PS_POSN_INCUMB_WS1 C2 WHERE A.POSITION_NBR = C2.POSITION_NBR AND A.EMPLID=C2.EMPLID AND A.EMPL_RCD=C2.EMPL_RCD) UNION SELECT B.POSITION_NBR ,%DateNull ,B.EFFDT , B.EMPLID ,B.EMPL_RCD ,B.NAME ,B.EMPLID %Concat '-' %Concat %NumToChar(B.EMPL_RCD) ,'N' ,'N/A' FROM PS_POSN_INCUMB_FD B WHERE NOT EXISTS ( SELECT 'X' FROM PS_POSN_PRI_INCUMB C2 WHERE B.POSITION_NBR = C2.POSITION_NBR AND B.EMPLID=C2.EMPLID) UNION SELECT B.POSITION_NBR ,%DateNull ,B.EFFDT , B.EMPLID ,B.EMPL_RCD ,B.NAME ,B.EMPLID %Concat '-' %Concat %NumToChar(B.EMPL_RCD) ,'N' ,'N/A' FROM PS_POSN_INCUMB_FD1 B WHERE NOT EXISTS ( SELECT 'X' FROM PS_POSN_PRI_INCUMB C2 WHERE B.POSITION_NBR = C2.POSITION_NBR AND B.EMPLID=C2.EMPLID)

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 POSITION_NBR Character(8) VARCHAR2(8) NOT NULL Position Number
2 POSN_PRI_START_DT Date(10) DATE Primary Incumbent Start Date
3 EFFDT Date(10) DATE Effective Date

Default Value: %date

4 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID
5 EMPL_RCD Number(3,0) SMALLINT NOT NULL Empl Record
6 NAME_DISPLAY Character(50) VARCHAR2(50) NOT NULL Display Name - name formatted for Display based on the Country
7 POSN_EMPLID_RCD Character(15) VARCHAR2(15) NOT NULL Concatenated Key for Manage Primary Incumbents
8 PRIMARY_INCUMB Character(1) VARCHAR2(1) NOT NULL Primary Incumbent
N=No
Y=Yes
9 SYSTEM_ASSIGNED Character(3) VARCHAR2(3) NOT NULL System Assigned
N=No
N/A=Not Applicable
Y=Yes