SETIDEMPLIDEMPL_RCDOPRIDC=Civil Servant
E=Employee
1=Without University Degree
2=With University Degree
ED=Simple Service
GD=Upper Service
HD=Supreme Service
MD=Midrange Service
GPS_VACANT_EE_V(SQL View) |
Index Back |
|---|---|
GPS search vw for atstfSELECT TOTALLY UNASSIGNED EES. Only select the ee who hasn't assigned to any post. |
| SELECT SEC.SETID , SEC.EMPLID , SEC.EMPL_RCD , SEC.OPRID , SEC.SETID_DEPT , SEC.DEPTID , SEC.JOBCODE , SEC.POSITION_NBR , SEC.FTE GPS_VACANT_FTE , SEC.GRADE , SEC.GPS_RANKING_SEQ , SEC.GPS_PERS_TYPE , SEC.GPS_SC_GROUP , SEC.GPS_CSR_CD , SEC.GPS_ALLOWANCE , SEC.GPS_COST_CENTER_CD , SEC.GPS_GRADE FROM %Table(GPS_STF_ALLEE_V) SEC WHERE SEC.FTE > 0 AND NOT EXISTS( SELECT 1 FROM PS_GPS_POST_EE EE WHERE EE.EMPLID = SEC.EMPLID AND EE.EMPL_RCD = SEC.EMPL_RCD AND EE.EFFDT >%CurrentDateIn) AND NOT EXISTS ( SELECT 1 FROM PS_GPS_STF_DTL_VW D , PS_GPS_POST_EE E WHERE D.EFFDT = E.EFFDT AND D.EFFSEQ = E.EFFSEQ AND D.SETID = E.SETID AND D.GPS_POST_ID = E.GPS_POST_ID AND SEC.EMPLID = E.EMPLID AND SEC.EMPL_RCD = E.EMPL_RCD AND D.EFFDT = ( SELECT MAX(EFFDT) FROM PS_GPS_STF_DTL_VW DD WHERE DD.SETID = E.SETID AND DD.EFFDT <=%CurrentDateIn AND DD.GPS_POST_ID = D.GPS_POST_ID) AND D.EFFSEQ = ( SELECT MAX(EFFSEQ) FROM PS_GPS_STF_DTL_VW DDD WHERE DDD.EFFDT = D.EFFDT AND DDD.SETID = D.SETID AND DDD.GPS_POST_ID = D.GPS_POST_ID) ) |
| # | PeopleSoft Field Name | PeopleSoft Field Type | Database Column Type | Description |
|---|---|---|---|---|
| 1 | SETID |
Character(5) | VARCHAR2(5) NOT NULL | SetID |
| 2 | EMPLID |
Character(11) | VARCHAR2(11) NOT NULL | Employee ID |
| 3 | EMPL_RCD |
Number(3,0) | SMALLINT NOT NULL | Empl Record |
| 4 | OPRID |
Character(30) | VARCHAR2(30) NOT NULL | A user's ID (see PSOPRDEFN). |
| 5 | SETID_DEPT | Character(5) | VARCHAR2(5) NOT NULL | Department Set ID |
| 6 | DEPTID | Character(10) | VARCHAR2(10) NOT NULL | Department |
| 7 | JOBCODE | Character(6) | VARCHAR2(6) NOT NULL | Job Code |
| 8 | POSITION_NBR | Character(8) | VARCHAR2(8) NOT NULL | Position Number |
| 9 | GPS_VACANT_FTE | Number(13,6) | DECIMAL(12,6) NOT NULL | GPS displays the total number of vacant FTE o person |
| 10 | GRADE | Character(3) | VARCHAR2(3) NOT NULL | Salary Grade |
| 11 | GPS_RANKING_SEQ | Number(5,0) | INTEGER NOT NULL | GPS Post Grade Ranking Sequence, This field will be used to determine the actual ranking of the grade. |
| 12 | GPS_PERS_TYPE | Character(1) | VARCHAR2(1) NOT NULL |
association to person type. A post type can be associated with a person type of Civil Servant, a person type of employee or to both.
C=Civil Servant E=Employee |
| 13 | GPS_SC_GROUP | Character(4) | VARCHAR2(4) NOT NULL |
This field is used to define Service Class Group values.
1=Without University Degree 2=With University Degree ED=Simple Service GD=Upper Service HD=Supreme Service MD=Midrange Service |
| 14 | GPS_CSR_CD | Character(15) | VARCHAR2(15) NOT NULL | This field is used to define Civil Service Ranks. |
| 15 | GPS_ALLOWANCE | Character(1) | VARCHAR2(1) NOT NULL | allowance Flag |
| 16 | GPS_COST_CENTER_CD | Character(15) | VARCHAR2(15) NOT NULL | German Public Sector Cost Center. |
| 17 | GPS_GRADE | Character(8) | VARCHAR2(8) NOT NULL | Salary Grade. |