SCH_EMPLMT_SRCH

(SQL View)
 Index Back

Employee Search Schedule Assg

Employee Search View for Schedule Assignment component combining T&L Group Security and dept based security views.

SELECT OPR1.OPRID , O.CLASSID , J.EMPLID , J.EMPL_RCD , P.NAME , P.LAST_NAME_SRCH , J.BUSINESS_UNIT , J.SETID_DEPT , J.DEPTID , J.PER_ORG , P.NAME_AC , J.JOBCODE , JC.DESCR , J.GP_PAYGROUP FROM PS_PERSON_NAME P , PS_JOB J , PSCLASSDEFN O , PS_JOBCODE_TBL JC , PSOPRDEFN OPR1 WHERE P.EMPLID = J.EMPLID AND ((EXISTS ( SELECT 'X' FROM PS_TL_GRP_SECURITY S WHERE S.ROWSECCLASS = O.CLASSID) AND J.EMPLID IN ( SELECT F.EMPLID FROM PS_TL_GRP_SECURITY S , PS_TL_GROUP_DTL F WHERE S.TL_GROUP_ID = F.TL_GROUP_ID AND F.EMPLID = J.EMPLID AND F.EMPL_RCD = J.EMPL_RCD AND S.ROWSECCLASS = O.CLASSID)) OR (NOT EXISTS ( SELECT 'X' FROM PS_TL_GRP_SECURITY S WHERE S.ROWSECCLASS = O.CLASSID) AND J.EMPLID IN ( SELECT SEC.EMPLID FROM %Sql(SCRTY_PER_FROM) WHERE %Sql(SCRTY_NO_APPT1) AND %Sql(SCRTY_WHERE, 'PPLJOB') AND J.EMPLID = SEC.EMPLID AND J.EMPL_RCD = SEC.EMPL_RCD AND OPR.ROWSECCLASS = OPR1.ROWSECCLASS AND OPR.OPRID = OPR1.OPRID)) OR (EXISTS( SELECT 'X' FROM PS_TL_GRP_SECURITY S WHERE S.ROWSECCLASS = O.CLASSID) AND J.EMPLID IN ( SELECT SEC.EMPLID FROM %Sql(SCRTY_PER_FROM) WHERE %Sql(SCRTY_NO_APPT1) AND %Sql(SCRTY_WHERE, 'PPLJOB') AND J.EMPLID = SEC.EMPLID AND J.EMPL_RCD = SEC.EMPL_RCD AND OPR.ROWSECCLASS = OPR1.ROWSECCLASS AND OPR.OPRID = OPR1.OPRID) AND J.EFFDT > %CurrentDateIn AND J.ACTION IN ('HIR', 'REH'))) 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) AND JC.SETID = J.SETID_JOBCODE AND JC.JOBCODE = J.JOBCODE AND %EffdtCheck(JOBCODE_TBL JC1, JC, %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 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit
8 SETID_DEPT Character(5) VARCHAR2(5) NOT NULL Department Set ID
9 DEPTID Character(10) VARCHAR2(10) NOT NULL Department
10 PER_ORG Character(3) VARCHAR2(3) NOT NULL Defines the Organizational Relationship(s) that a Person has to the Organization. These are Employee, Contingent Worker, and Persons of Interest.
CWR=Contingent Worker
EMP=Employee
POI=Person of Interest
11 NAME_AC Character(50) VARCHAR2(50) NOT NULL Alternate Character Name
12 JOBCODE Character(6) VARCHAR2(6) NOT NULL Job Code

Prompt Table: JOBCODE_TBL

13 JOBCODE_DESCR Character(30) VARCHAR2(30) NOT NULL Job Code Description
14 GP_PAYGROUP Character(10) VARCHAR2(10) NOT NULL Global Payroll pay group