SCH_EMP_SRCH_VW(SQL View) |
Index Back |
|---|---|
Employee Search Schedule AssgEmployee 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 , P.FIRST_NAME , J.BUSINESS_UNIT , J.PER_ORG , J.JOBCODE , JC.DESCR , J.SETID_DEPT , J.DEPTID , D.DESCR , J.SUPERVISOR_ID , J.REPORTS_TO , J.LOCATION , L.DESCR , J.COMPANY , J.PAYGROUP , J.GP_PAYGROUP , J.POSITION_NBR , ED.WORKGROUP , ED.TASKGROUP , S.SCHEDULE_GRP , S.SCHEDULE_ID FROM PS_PERSON_NAME P , PS_JOB J LEFT OUTER JOIN ( SELECT E1.EMPLID , E1.EMPL_RCD , E1.WORKGROUP , E1.TASKGROUP FROM PS_TL_EMPL_DATA E1 WHERE %EffdtCheck(TL_EMPL_DATA E2, E1, %CurrentDateIn)) ED ON J.EMPLID = ED.EMPLID AND J.EMPL_RCD = ED.EMPL_RCD LEFT OUTER JOIN PS_SCH_EMP_DFLT_VW S ON J.EMPLID = S.EMPLID AND J.EMPL_RCD = S.EMPL_RCD , PSCLASSDEFN O , PS_JOBCODE_TBL JC , PSOPRDEFN OPR1 , PS_DEPT_TBL D , PS_LOCATION_TBL L 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) AND D.SETID = J.SETID_DEPT AND D.DEPTID = J.DEPTID AND %EffdtCheck(DEPT_TBL D1, D, %CurrentDateIn) AND J.SETID_LOCATION = L.SETID AND J.LOCATION=L.LOCATION AND %EffdtCheck(LOCATION_TBL L1, L, %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
Prompt Table: SCH_PERSON_VW |
| 4 | EMPL_RCD |
Number(3,0) | SMALLINT NOT NULL | Empl Record |
| 5 | NAME | Character(50) | VARCHAR2(50) NOT NULL | Name |
| 6 | LAST_NAME | Character(30) | VARCHAR2(30) NOT NULL | Last Name |
| 7 | FIRST_NAME | Character(30) | VARCHAR2(30) NOT NULL | First Name |
| 8 | BUSINESS_UNIT | Character(5) | VARCHAR2(5) NOT NULL |
Business Unit
Prompt Table: BUSUNIT_HR_VW |
| 9 | 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 |
| 10 | JOBCODE | Character(6) | VARCHAR2(6) NOT NULL |
Job Code
Prompt Table: JOBCODE_TBL |
| 11 | JOBCODE_DESCR | Character(30) | VARCHAR2(30) NOT NULL | Job Title |
| 12 | SETID_DEPT | Character(5) | VARCHAR2(5) NOT NULL | Department Set ID |
| 13 | DEPTID | Character(10) | VARCHAR2(10) NOT NULL |
Department
Prompt Table: DEPT_TBL |
| 14 | DEPT_DESCR | Character(30) | VARCHAR2(30) NOT NULL | Department Description |
| 15 | SUPERVISOR_ID | Character(11) | VARCHAR2(11) NOT NULL |
Supervisor ID
Prompt Table: TL_SUPRVSR_VW |
| 16 | REPORTS_TO | Character(8) | VARCHAR2(8) NOT NULL |
Reports To Position Number
Prompt Table: POSITION_DATA |
| 17 | LOCATION | Character(10) | VARCHAR2(10) NOT NULL |
Location Code
Prompt Table: LOCATION_TBL |
| 18 | LOCATION_DESCR | Character(30) | VARCHAR2(30) NOT NULL | Location Description |
| 19 | COMPANY | Character(3) | VARCHAR2(3) NOT NULL |
Company
Prompt Table: COMPANY_TBL |
| 20 | PAYGROUP | Character(3) | VARCHAR2(3) NOT NULL |
North American Pay Group
Prompt Table: PAYGROUP_TBL |
| 21 | GP_PAYGROUP | Character(10) | VARCHAR2(10) NOT NULL |
Global Payroll pay group
Prompt Table: GP_PYGRP |
| 22 | POSITION_NBR | Character(8) | VARCHAR2(8) NOT NULL |
Position Number
Prompt Table: POSN_DATA_VW |
| 23 | WORKGROUP | Character(10) | VARCHAR2(10) NOT NULL |
Workgroup
Prompt Table: TL_WRKGRP_TBL |
| 24 | TASKGROUP | Character(10) | VARCHAR2(10) NOT NULL |
Taskgroup
Prompt Table: TL_TASKGRP_TBL |
| 25 | SCHEDULE_GRP | Character(10) | VARCHAR2(10) NOT NULL |
Scheduling Organizational Group Code
Prompt Table: SCH_GROUP_TBL |
| 26 | SCHEDULE_ID | Character(16) | VARCHAR2(16) NOT NULL |
The Schedule ID is a field used to uniquely identify a Schedule Definition or Schedule Calendar (built out Schedule Definition).
Prompt Table: SCH_DEFN_TBL |