SETIDEMPLIDEMPL_RCDC=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_STF_ALLEE_V(SQL View) |
Index Back |
|---|---|
Payee info view for staffingSelect person information by current date. |
| SELECT DISTINCT GRADE.SETID SETID , EMPL.EMPLID , JOB.EMPL_RCD , JOB.SETID_DEPT , JOB.DEPTID , JOB.JOBCODE , JOB.POSITION_NBR , JOB.FTE , JOB.GRADE , GRADE.GPS_RANKING_SEQ , GRADE.GPS_PERS_TYPE , JOB_GPS.GPS_SC_GROUP , JOB_GPS.GPS_CSR_CD , GRADE.GPS_ALLOWANCE , COSTCENTER.GPS_COST_CENTER_CD ,JOB.LOCATION ,GRADE.GPS_GRADE ,empl.oprid FROM PS_PERS_SRCH_GBL EMPL JOIN PS_JOB JOB ON JOB.EMPLID = EMPL.EMPLID AND job.empl_rcd = empl.empl_rcd JOIN PS_JOB_GPS JOB_GPS ON JOB.EMPLID = JOB_GPS.EMPLID AND JOB.EMPL_RCD = JOB_GPS.EMPL_RCD AND JOB.EFFDT = JOB_GPS.EFFDT AND JOB.EFFSEQ = JOB_GPS.EFFSEQ JOIN PS_GPS_COST_CENTER COSTCENTER ON JOB.EMPLID = COSTCENTER.EMPLID AND JOB.EMPL_RCD = COSTCENTER.EMPL_RCD JOIN PS_GPS_GRADE_TBL GRADE ON JOB.GRADE = GRADE.GRADE AND JOB_GPS.GPS_SC_GROUP = GRADE.GPS_SC_GROUP AND JOB_GPS.GPS_ALLOW_FLG = GRADE.GPS_ALLOWANCE WHERE JOB.HR_STATUS = 'A' AND JOB.EFFDT = ( SELECT MAX(JOB2.EFFDT) FROM PS_JOB JOB2 WHERE JOB2.EFFDT <= %CurrentDateIn AND JOB2.EMPLID = JOB.EMPLID AND JOB.EMPL_RCD = JOB2.EMPL_RCD) AND JOB.EFFSEQ = ( SELECT MAX(JOB2.EFFSEQ) FROM PS_JOB JOB2 WHERE JOB2.EMPLID = JOB.EMPLID AND JOB.EMPL_RCD = JOB2.EMPL_RCD AND JOB.EFFDT = JOB2.EFFDT) AND COSTCENTER.EFFDT = ( SELECT MAX(COSTCENTER2.EFFDT) FROM PS_GPS_COST_CENTER COSTCENTER2 WHERE COSTCENTER2.EFFDT <= %CurrentDateIn AND COSTCENTER.GPS_COST_CENTER_CD = COSTCENTER2.GPS_COST_CENTER_CD AND COSTCENTER.EMPLID = COSTCENTER2.EMPLID AND COSTCENTER.EMPL_RCD = COSTCENTER2.EMPL_RCD ) AND GRADE.EFFDT = ( SELECT MAX(GRADE2.EFFDT) FROM PS_GPS_GRADE_TBL GRADE2 WHERE GRADE2.EFFDT < %CurrentDateIn AND GRADE.GPS_GRADE = GRADE2.GPS_GRADE AND GRADE.SETID = GRADE2.SETID) AND JOB.APPT_TYPE = '0' |
| # | 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 | SETID_DEPT | Character(5) | VARCHAR2(5) NOT NULL | Department Set ID |
| 5 | DEPTID | Character(10) | VARCHAR2(10) NOT NULL | Department |
| 6 | JOBCODE | Character(6) | VARCHAR2(6) NOT NULL | Job Code |
| 7 | POSITION_NBR | Character(8) | VARCHAR2(8) NOT NULL | Position Number |
| 8 | FTE | Number(8,6) | DECIMAL(7,6) NOT NULL | This field represent Full Time Equivalence |
| 9 | GRADE | Character(3) | VARCHAR2(3) NOT NULL | Salary Grade |
| 10 | 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. |
| 11 | 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 |
| 12 | 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 |
| 13 | GPS_CSR_CD | Character(15) | VARCHAR2(15) NOT NULL | This field is used to define Civil Service Ranks. |
| 14 | GPS_ALLOWANCE | Character(1) | VARCHAR2(1) NOT NULL | allowance Flag |
| 15 | GPS_COST_CENTER_CD | Character(15) | VARCHAR2(15) NOT NULL | German Public Sector Cost Center. |
| 16 | LOCATION | Character(10) | VARCHAR2(10) NOT NULL | Location Code |
| 17 | GPS_GRADE | Character(8) | VARCHAR2(8) NOT NULL | Salary Grade. |
| 18 | OPRID | Character(30) | VARCHAR2(30) NOT NULL | A user's ID (see PSOPRDEFN). |