MIL_RANK_CTL_VW

(SQL View)
 Index Back

Search Record for MilRankChng

This viewe will be used as the Add search record for the Military Rank Change Component.

SELECT A.EMPLID , A.EMPL_RCD , C.NAME_DISPLAY , B.MIL_RANK , B.MIL_WORN_RANK , D.MILITARY_SERVICE FROM PS_JOB A , PS_JOB_MIL B , PS_NAMES C , PS_PER_ORG_INS_MIL D ,PS_PER_ORG_ASGN E WHERE ( A.EFFDT = ( SELECT MAX(A_ED.EFFDT) FROM PS_JOB A_ED WHERE A.EMPLID = A_ED.EMPLID AND A.EMPL_RCD = A_ED.EMPL_RCD AND A_ED.EFFDT <= %CurrentDateIn) AND A.EFFSEQ = ( SELECT MAX(A_ES.EFFSEQ) FROM PS_JOB A_ES WHERE A.EMPLID = A_ES.EMPLID AND A.EMPL_RCD = A_ES.EMPL_RCD AND A.EFFDT = A_ES.EFFDT) AND A.EMPLID = B.EMPLID AND A.EMPL_RCD = B.EMPL_RCD AND B.EFFDT = ( SELECT MAX(B_ED.EFFDT) FROM PS_JOB_MIL B_ED WHERE B.EMPLID = B_ED.EMPLID AND B.EMPL_RCD = B_ED.EMPL_RCD AND B_ED.EFFDT <= A.EFFDT) AND B.EFFSEQ = ( SELECT MAX(B_ES.EFFSEQ) FROM PS_JOB_MIL B_ES WHERE B.EMPLID = B_ES.EMPLID AND B.EMPL_RCD = B_ES.EMPL_RCD AND B.EFFDT = B_ES.EFFDT) AND B.EMPLID = C.EMPLID AND C.EFFDT = ( SELECT MAX(C_ED.EFFDT) FROM PS_NAMES C_ED WHERE C.EMPLID = C_ED.EMPLID AND C.NAME_TYPE = C_ED.NAME_TYPE AND C_ED.EFFDT <= B.EFFDT) AND C.EMPLID = D.EMPLID ) AND A.PER_ORG ='EMP' AND E.EMPLID = D.EMPLID AND E.EMPL_RCD = A.EMPL_RCD AND E.ORG_INSTANCE_ERN = D.ORG_INSTANCE_ERN AND E.PER_ORG = 'EMP'

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID
2 EMPL_RCD Number(3,0) SMALLINT NOT NULL Empl Record
3 DISPLAY_NAME Character(50) VARCHAR2(50) NOT NULL Used to display the person's name in the PERS_SRCH_SBP, EMPL_SRCH_SBP and the components that allow a person to be created
4 MIL_RANK Character(5) VARCHAR2(5) NOT NULL Military Rank
5 MIL_WORN_RANK Character(5) VARCHAR2(5) NOT NULL Worn Rank
6 MILITARY_SERVICE Character(8) VARCHAR2(8) NOT NULL Military Service