GPS_ATSTF_RS_VW (SQL View)	Index Back
GPS autostaff result View GPS autostaff result View

GPS_ATSTF_RS_VW

(SQL View)

GPS autostaff result View

SELECT DISTINCT aers.gps_atstf_seq ,aers.emplid , aers.empl_rcd , aers.setid , dtl.gps_rowid , dtl.gps_grade , dtl.gps_rank , dtl.gps_post_type ,aers.gps_atstf_rs_sta , 'O' AS gps_status , allee.grade ,allee.gps_pers_type ,allee.gps_sc_group , aers.gps_post_id , allee.deptid , dtl.gps_fraction , aers.gps_fraction ,allee.gps_csr_cd ,allee.position_nbr ,allee.gps_cost_Center_cd ,allee.location FROM ps_gps_atstf_rslt aers , %Table(gps_stf_alee_v2) allee ,ps_gps_post_lst_vw dtl WHERE aers.emplid = allee.emplid AND aers.setid = allee.setid AND aers.empl_rcd = allee.empl_rcd AND aers.gps_post_id = dtl.gps_post_id AND aers.setid = dtl.setid AND ALLEE.GPS_GRADE = ( SELECT MAX(GPS_GRADE) FROM %Table(gps_stf_alee_v2) EE2 WHERE ALLEE.EMPLID = EE2.EMPLID AND ALLEE.EMPL_RCD = EE2.EMPL_RCD AND ALLEE.SETID = EE2.SETID)

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description

1 GPS_ATSTF_SEQ Number(10,0) DECIMAL(10) NOT NULL Auto-Staffing Sequence

2 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID

3 EMPL_RCD Number(3,0) SMALLINT NOT NULL Empl Record

4 SETID Character(5) VARCHAR2(5) NOT NULL SetID
Prompt Table: SET_VLD_HR_02

5 GPS_ROWID Character(45) VARCHAR2(45) NOT NULL RowID, a condensed version of the key fields of a budgeting system

6 GPS_GRADE Character(8) VARCHAR2(8) NOT NULL Salary Grade.
Prompt Table: GPS_POST_GRD_VW

7 GPS_RANK Character(15) VARCHAR2(15) NOT NULL Rank for GPS

8 GPS_POST_TYPE Character(3) VARCHAR2(3) NOT NULL Post Type
AND=Other (Civil Service)
ANE=Other (Employee)
ANG=Public Sector Employee
ANW=Civil Servant on Recall
AZU=Apprentice
BEA=Civil Servant
ERE=Replacement Post (Employee)
ERS=Replacement Post (Civil Serv.)
LEB=Empty Post (Civil Service)
LEE=Empty Post (Employee)

9 GPS_ATSTF_RS_STA Character(1) VARCHAR2(1) NOT NULL Row status of auto staffing result.
A=in Process...
F=Assignment not Possible
N=New
S=Assigned
Default Value: N

10 GPS_STATUS Character(1) VARCHAR2(1) NOT NULL status of row in gps_post_dtl table
A=Active
B=Blocked
C=Copied Post
I=Inactive
N=New Post
O=Occupied
P=Pending
R=Reserved
S=Temporarily Blocked
T=Terminated
U=Cut
V=Vacant

11 GRADE Character(3) VARCHAR2(3) NOT NULL Salary 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_POST_ID Character(8) VARCHAR2(8) NOT NULL GPS post id

15 DEPTID Character(10) VARCHAR2(10) NOT NULL Department
Prompt Table: DEPT_TBL

16 GPS_FRACTION Number(9,2) DECIMAL(8,2) NOT NULL GPS Fraction

17 GPS_ASND_FRAC Number(13,6) DECIMAL(12,6) NOT NULL Assigned fraction of post. used in auto-staffing

18 GPS_CSR_CD Character(15) VARCHAR2(15) NOT NULL This field is used to define Civil Service Ranks.

19 POSITION_NBR Character(8) VARCHAR2(8) NOT NULL Position Number

20 GPS_COST_CENTER_CD Character(15) VARCHAR2(15) NOT NULL German Public Sector Cost Center.

21 LOCATION Character(10) VARCHAR2(10) NOT NULL Location Code

#	PeopleSoft Field Name	PeopleSoft Field Type	Database Column Type	Description
1	GPS_ATSTF_SEQ	Number(10,0)	DECIMAL(10) NOT NULL	Auto-Staffing Sequence
2	EMPLID	Character(11)	VARCHAR2(11) NOT NULL	Employee ID
3	EMPL_RCD	Number(3,0)	SMALLINT NOT NULL	Empl Record
4	SETID	Character(5)	VARCHAR2(5) NOT NULL	SetID Prompt Table: SET_VLD_HR_02
5	GPS_ROWID	Character(45)	VARCHAR2(45) NOT NULL	RowID, a condensed version of the key fields of a budgeting system
6	GPS_GRADE	Character(8)	VARCHAR2(8) NOT NULL	Salary Grade. Prompt Table: GPS_POST_GRD_VW
7	GPS_RANK	Character(15)	VARCHAR2(15) NOT NULL	Rank for GPS
8	GPS_POST_TYPE	Character(3)	VARCHAR2(3) NOT NULL	Post Type AND=Other (Civil Service) ANE=Other (Employee) ANG=Public Sector Employee ANW=Civil Servant on Recall AZU=Apprentice BEA=Civil Servant ERE=Replacement Post (Employee) ERS=Replacement Post (Civil Serv.) LEB=Empty Post (Civil Service) LEE=Empty Post (Employee)
9	GPS_ATSTF_RS_STA	Character(1)	VARCHAR2(1) NOT NULL	Row status of auto staffing result. A=in Process... F=Assignment not Possible N=New S=Assigned Default Value: N
10	GPS_STATUS	Character(1)	VARCHAR2(1) NOT NULL	status of row in gps_post_dtl table A=Active B=Blocked C=Copied Post I=Inactive N=New Post O=Occupied P=Pending R=Reserved S=Temporarily Blocked T=Terminated U=Cut V=Vacant
11	GRADE	Character(3)	VARCHAR2(3) NOT NULL	Salary 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_POST_ID	Character(8)	VARCHAR2(8) NOT NULL	GPS post id
15	DEPTID	Character(10)	VARCHAR2(10) NOT NULL	Department Prompt Table: DEPT_TBL
16	GPS_FRACTION	Number(9,2)	DECIMAL(8,2) NOT NULL	GPS Fraction
17	GPS_ASND_FRAC	Number(13,6)	DECIMAL(12,6) NOT NULL	Assigned fraction of post. used in auto-staffing
18	GPS_CSR_CD	Character(15)	VARCHAR2(15) NOT NULL	This field is used to define Civil Service Ranks.
19	POSITION_NBR	Character(8)	VARCHAR2(8) NOT NULL	Position Number
20	GPS_COST_CENTER_CD	Character(15)	VARCHAR2(15) NOT NULL	German Public Sector Cost Center.
21	LOCATION	Character(10)	VARCHAR2(10) NOT NULL	Location Code