SSR_RS_CNDTPCVW

(SQL View)
 Index Back

Research Enrollment Tracking

Topic - Research Enrollment Tracking

SELECT A.EMPLID ,A.INSTITUTION ,A.SSR_RS_CANDIT_NBR ,A.SSR_RS_TOPIC_SEQ ,A.SSR_RS_TOPIC_ID ,A.SSR_RS_TOPIC_CAT ,A.SSR_RS_THESIS_TITL ,A.SSR_RS_RSRCH_TOPIC ,A.SSR_RS_STATUS ,A.SSR_RS_ACTION_DT ,A.SSR_RS_DESCRLONG FROM %Table(SSR_RS_TOPIC) A WHERE A.SSR_RS_TOPIC_SEQ = ( SELECT MAX(B.SSR_RS_TOPIC_SEQ) FROM PS_SSR_RS_TOPIC B WHERE B.EMPLID=A.EMPLID AND B.INSTITUTION=A.INSTITUTION AND B.SSR_RS_CANDIT_NBR=A.SSR_RS_CANDIT_NBR) AND A.EFFDT = ( SELECT MAX(B.EFFDT) FROM PS_SSR_RS_TOPIC B WHERE B.EMPLID=A.EMPLID AND B.INSTITUTION=A.INSTITUTION AND B.SSR_RS_CANDIT_NBR=A.SSR_RS_CANDIT_NBR AND B.SSR_RS_TOPIC_SEQ = A.SSR_RS_TOPIC_SEQ AND B.EFFDT <= %CurrentDateIn) AND A.EFFSEQ = ( SELECT MAX(B.EFFSEQ) FROM PS_SSR_RS_TOPIC B WHERE B.EMPLID=A.EMPLID AND B.INSTITUTION=A.INSTITUTION AND B.SSR_RS_CANDIT_NBR=A.SSR_RS_CANDIT_NBR AND B.SSR_RS_TOPIC_SEQ = A.SSR_RS_TOPIC_SEQ AND B.EFFDT=A.EFFDT) AND A.EFF_STATUS = 'A'

  • Parent record: SSR_RS_TOPC_HDR

    • # PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
    1 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID

    Prompt Table: PEOPLE_SRCH

    2 INSTITUTION Character(5) VARCHAR2(5) NOT NULL Academic Institution

    Prompt Table: INST_CAR_SCTY

    3 SSR_RS_CANDIT_NBR Character(12) VARCHAR2(12) NOT NULL Last Candidate Number
    4 SSR_RS_TOPIC_SEQ Number(3,0) SMALLINT NOT NULL This field is used to create a unique key when multiple topics exist for a research project.
    5 SSR_RS_TOPIC_ID Character(6) VARCHAR2(6) NOT NULL This field is a sequential code used to create unique table entries to pre-define topics to be used in research management.
    6 SSR_RS_TOPIC_CAT Character(2) VARCHAR2(2) NOT NULL Field used to identify if the topic is part of a larger grouping

    Prompt Table: SSR_RS_TPCCATVW

    7 SSR_RS_THESIS_TITL Long Character(254) VARCHAR2(254) Thesis Title
    8 SSR_RS_RSRCH_TOPIC Long Character(254) VARCHAR2(254) Research Topic Description
    9 SSR_RS_STATUS Character(12) VARCHAR2(12) NOT NULL Status associated within the status group code.

    Prompt Table: SSR_RS_TPSTS_VW

    10 SSR_RS_ACTION_DT Date(10) DATE NOT NULL Status Date
    11 SSR_RS_DESCRLONG Long Character CLOB This field is used for the descrlong field sin research management