TL_TR_COMP_SRCH

(SQL View)
 Index Back

T&L Comp Plan Assign Srch View

This view is a clone of TL_ADM_SRCH_VW with EMP_RCD set as a key and without Comparing the Effdt with Current Date. Access is determined by the TL Group Security defined for the Operator's Row Security Class. When the Operator's Row Security Class has no specific Group Security, HR Department Security is used to determine access.

SELECT OPR1.OPRID , O.CLASSID , E.EMPLID , E.EMPL_RCD , P.NAME FROM PS_JOB J , PS_PERSON_NAME P , PS_TL_EMPL_DATA E , PSCLASSDEFN O , PSOPRDEFN OPR1 WHERE ( (EXISTS ( SELECT 'X' FROM PS_TL_GRP_SECURITY S WHERE S.ROWSECCLASS = O.CLASSID) AND E.EMPLID IN ( SELECT F.EMPLID FROM PS_TL_GRP_SECURITY S , PS_TL_GROUP_DTL F WHERE S.GROUP_ID = F.GROUP_ID AND F.EMPLID = E.EMPLID AND F.EMPL_RCD = E.EMPL_RCD AND S.ROWSECCLASS = O.CLASSID )) OR (NOT EXISTS ( SELECT 'X' FROM PS_TL_GRP_SECURITY S WHERE S.ROWSECCLASS = O.CLASSID ) AND E.EMPLID IN ( SELECT SEC.EMPLID FROM %Sql(SCRTY_PER_FROM_BEFORE) WHERE %Sql(SCRTY_NO_APPT1) AND %Sql(SCRTY_WHERE_BEFORE, 'PPLJOB') AND E.EMPLID = SEC.EMPLID AND E.EMPL_RCD = SEC.EMPL_RCD AND OPR.ROWSECCLASS = OPR1.ROWSECCLASS AND OPR.OPRID = OPR1.OPRID) ) ) AND E.EFFDT = ( SELECT MAX(E1.EFFDT) FROM PS_TL_EMPL_DATA E1 WHERE E1.EMPLID = E.EMPLID AND E1.EMPL_RCD = E.EMPL_RCD ) AND E.EMPLID = P.EMPLID AND E.EMPLID = J.EMPLID AND E.EMPL_RCD = J.EMPL_RCD AND J.EFFDT = ( SELECT MAX(J1.EFFDT) FROM PS_JOB J1 WHERE J1.EMPLID = J.EMPLID AND J1.EMPL_RCD = J.EMPL_RCD ) AND J.EFFSEQ = ( SELECT MAX(J2.EFFSEQ) FROM PS_JOB J2 WHERE J2.EMPLID = J.EMPLID AND J2.EMPL_RCD = J.EMPL_RCD AND J2.EFFDT = J.EFFDT )

# 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 Rcd Nbr
5 NAME Character(50) VARCHAR2(50) NOT NULL Name