SSR_RS_TOPSEQVW

(SQL View)
 Index Back

Topic Sequence prompt for 3C

used as prompt in COMM_VAR_DATA 3Cs subrecord

SELECT DISTINCT A.emplid ,A.SSR_RS_CANDIT_NBR ,A.ssr_Rs_topic_seq , B.SSR_RS_RSRCH_TOPIC FROM %Table(SSR_RS_TOPC_HDR) A , %Table(SSR_RS_TOPIC) B WHERE A.emplid = B.emplid AND A.institution = B.institution AND A.SSR_RS_CANDIT_NBR = B.SSR_RS_CANDIT_NBR AND A.SSR_RS_TOPIC_SEQ = B.SSR_RS_TOPIC_SEQ AND B.EFFDT = ( SELECT MAX(ef1.EFFDT) FROM %Table(SSR_RS_TOPIC) ef1 WHERE ef1.emplid = B.emplid AND ef1.institution = B.institution AND ef1.SSR_RS_CANDIT_NBR = B.SSR_RS_CANDIT_NBR AND ef1.SSR_RS_TOPIC_SEQ = B.SSR_RS_TOPIC_SEQ ) AND B.EFFSEQ = ( SELECT MAX(ef2.EFFSEQ) FROM %Table(SSR_RS_TOPIC) ef2 WHERE ef2.emplid = B.emplid AND ef2.institution = B.institution AND ef2.SSR_RS_CANDIT_NBR = B.SSR_RS_CANDIT_NBR AND ef2.SSR_RS_TOPIC_SEQ = B.SSR_RS_TOPIC_SEQ AND ef2.EFFDT = B.effdt ) AND B.eff_status='A'

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID
2 SSR_RS_CANDIT_NBR Character(12) VARCHAR2(12) NOT NULL Last Candidate Number
3 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.
4 SSR_RS_RSRCH_TOPIC Long Character(254) VARCHAR2(254) Research Topic Description