HR_SRCH_SP_VW

(SQL View)
 Index Back

Succession Plan Global Search

This view has been created as a search record for Succession Plan Launcher component.

SELECT A.HR_SUCC_PLAN_ID , A.HR_SUCC_PLAN_TYPE , A.HR_SUCC_PLAN_CTL ,B.EMPLID ,B.EMPL_RCD FROM PS_HR_SUCC_PLN_HDR A , PS_HR_SRCH_JOB_VW B WHERE ( A.EFFDT = ( SELECT MAX(A_ED.EFFDT) FROM PS_HR_SUCC_PLN_HDR A_ED WHERE A.HR_SUCC_PLAN_ID = A_ED.HR_SUCC_PLAN_ID AND A.HR_SUCC_PLAN_TYPE = A_ED.HR_SUCC_PLAN_TYPE AND A.HR_SUCC_PLAN_CTL = A_ED.HR_SUCC_PLAN_CTL AND A_ED.EFFDT <= %CurrentDateIn)) AND ((A.HR_SUCC_PLAN_TYPE='2' AND A.HR_SUCC_PLAN_CTL=B.EMPLID) OR (A.HR_SUCC_PLAN_TYPE='3' AND A.HR_SUCC_PLAN_CTL=B.POSITION_NBR) OR (A.HR_SUCC_PLAN_TYPE='1' AND A.HR_SUCC_PLAN_CTL=B.JOBCODE)) AND B.EFFDT = ( SELECT MAX(B_ED.EFFDT) FROM PS_HR_SRCH_JOB_VW B_ED WHERE B.EMPLID = B_ED.EMPLID AND B.EMPL_RCD = B_ED.EMPL_RCD AND B_ED.EFFDT <= %CurrentDateIn) AND B.EFFSEQ = ( SELECT MAX(B_ES.EFFSEQ) FROM PS_HR_SRCH_JOB_VW B_ES WHERE B.EMPLID = B_ES.EMPLID AND B.EMPL_RCD = B_ES.EMPL_RCD AND B.EFFDT = B_ES.EFFDT)

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 HR_SUCC_PLAN_ID Character(11) VARCHAR2(11) NOT NULL Succession Plan ID
2 HR_SUCC_PLAN_TYPE Character(1) VARCHAR2(1) NOT NULL Succession Plan Type
1=Job Code
2=Person
3=Position
3 HR_SUCC_PLAN_CTL Character(11) VARCHAR2(11) NOT NULL Succession Plan Control Value
4 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID
5 EMPL_RCD Number(3,0) SMALLINT NOT NULL Empl Record