OPRIDEMPLIDEMPL_RCDTL_TR_ESSRCD_VW(SQL View) |
Index Back |
|---|---|
TL TR Self Service Search VwSearch view intended for use with Self Service events that are not date driven. For the Operator who is logged in, the view returns a row for each current job associated to the Operator's Employee ID. |
| SELECT DISTINCT O.OPRID , E.EMPLID , E.EMPL_RCD , P.NAME , J.DEPTID , J.JOBCODE , JC.DESCR FROM PS_TL_EMPL_DATA E , PS_PERSON_NAME P , PS_JOB J , PS_JOBCODE_TBL JC , PS_TL_OPR_CLASS_VW O WHERE E.EMPLID = O.EMPLID 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 J1.EFFDT <= E.EFFDT) 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 JC.EFFDT = ( SELECT MAX(JC1.EFFDT) FROM PS_JOBCODE_TBL JC1 WHERE JC1.SETID = J.SETID_JOBCODE AND JC1.JOBCODE = J.JOBCODE AND JC1.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 | EMPLID |
Character(11) | VARCHAR2(11) NOT NULL | Employee ID |
| 3 | EMPL_RCD |
Number(3,0) | SMALLINT NOT NULL | Empl Rcd Nbr |
| 4 | NAME | Character(50) | VARCHAR2(50) NOT NULL | Name |
| 5 | DEPTID | Character(10) | VARCHAR2(10) NOT NULL | Department |
| 6 | JOBCODE | Character(6) | VARCHAR2(6) NOT NULL | Job Code |
| 7 | JOBCODE_DESCR | Character(30) | VARCHAR2(30) NOT NULL | Job Code Description |