HR_TP_RB_MBR_VW

(SQL View)
 Index Back

talent pool - view on member

used on succession plan / processing - view to retrieve list of TP memebers flagged as searchable on succession plans

SELECT t.HR_TP_ID ,t.EFFDT ,rt.HRXAXISRATINGS ,rb.EMPLID ,rb.EMPL_RCD FROM PS_HR_TPOOL t , PS_HR_TPOOL_MBR tm , PS_HR_TP_RB_DATA rb , PS_HR_RB_X_RATINGS rt WHERE t.HR_SP_CAND_SRCH = 'Y' AND t.STATUS = 'A' AND tm.STATUS = 'A' AND t.HR_TP_ID = tm.HR_TP_ID AND t.EFFDT = tm.EFFDT AND t.EFFDT = ( SELECT MAX(t1.EFFDT) FROM PS_HR_TPOOL t1 WHERE t1.HR_TP_ID = t.HR_TP_ID AND t1.EFFDT <= %CurrentDateIn ) AND rb.HR_TP_ID = tm.HR_TP_ID AND rb.EFFDT = tm.EFFDT AND rb.EMPLID = tm.EMPLID AND rb.EMPL_RCD = TM.EMPL_RCD AND rb.HR_RATINGS_BOX_ID = rt.HR_RATINGS_BOX_ID AND rb.HR_RB_XAXISRANK = rt.RANK AND rb.EFFDT = ( SELECT MAX(rb1.EFFDT) FROM PS_HR_TP_RB_DATA rb1 WHERE rb1.HR_TP_ID = rb.HR_TP_ID AND rb1.HR_RATINGS_BOX_ID = rb.HR_RATINGS_BOX_ID AND rb1.EMPLID = rb.EMPLID AND rb1.EMPL_RCD = rb.EMPL_RCD AND rb1.EFFDT >= rt.EFFDT )

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 HR_TP_ID Character(30) VARCHAR2(30) NOT NULL Talent Pool ID
2 EFFDT Date(10) DATE NOT NULL Effective Date

Default Value: %date

3 HR_TP_CAT_ID Character(30) VARCHAR2(30) NOT NULL Talent Pool Category
4 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID

Prompt Table: HR_TP_PERS_SRCH

5 EMPL_RCD Number(3,0) SMALLINT NOT NULL Empl Record

Prompt Table: HR_TP_PERS_SRCH