FPAEMPLOYM_SRCZ

(SQL View)
 Index Back

Search every employee + securi

Selection of records for career history. Restriction to employees having career.

SELECT DISTINCT %Sql(SCRTY_SEL_PKEY, OPR,SEC) ,NM.NAME ,NM.LAST_NAME_SRCH ,SEC.SETID_DEPT ,SEC.DEPTID FROM PSOPRDEFN OPR , PS_SJT_CLASS_ALL CLS , PS_FPAEEPOST_HIST SEC , PS_SJT_OPR_CLS SOC , PS_FPANAMES_HIST NM WHERE (CLS.SCRTY_SET_CD = 'PPLJOB' ) AND (CLS.SCRTY_TYPE_CD = '001' AND CLS.SCRTY_KEY1 = SEC.SETID_DEPT AND CLS.SCRTY_KEY2 = SEC.DEPTID) AND SOC.OPRID=OPR.OPRID AND SOC.CLASSID = CLS.CLASSID AND (( CLS.TREE = 'Y' AND SOC.CLASSID = OPR.ROWSECCLASS AND ( SOC.SEC_RSC_FLG = '1' OR SOC.SEC_RSC_FLG = '3') ) OR ( CLS.TREE = 'N' ) ) AND SEC.EMPLID = NM.EMPLID AND NM.NAME_TYPE = 'PRI' AND NM.EFFDT = ( SELECT MAX(B.EFFDT) FROM PS_FPANAMES_HIST B WHERE B.EMPLID = NM.EMPLID AND B.NAME_TYPE = 'PRI' AND B.EFFDT <= %CurrentDateIn )

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 OPRID Character(30) VARCHAR2(30) NOT NULL A user's ID (see PSOPRDEFN).
2 ROWSECCLASS Character(30) VARCHAR2(30) NOT NULL The class used to determine row level security
3 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID
4 EMPL_RCD Number(3,0) SMALLINT NOT NULL Empl Record
5 NAME Character(50) VARCHAR2(50) NOT NULL Name
6 LAST_NAME_SRCH Character(30) VARCHAR2(30) NOT NULL Last Name
7 SETID_DEPT Character(5) VARCHAR2(5) NOT NULL Department Set ID
8 DEPTID Character(10) VARCHAR2(10) NOT NULL Department