GPS_POST_V_VW (SQL View)	Index Back
GPS Post vacant Fractions GPS Post vacant Fractions view.Use current date to compare effectivedate.

GPS_POST_V_VW

(SQL View)

GPS Post vacant Fractions

GPS Post vacant Fractions view.Use current date to compare effectivedate.

SELECT A.SETID , A.GPS_POST_ID , A.GPS_ROWID , A.DEPTID , A.GPS_GRADE , A.GPS_RANK , A.GPS_POST_TYPE , A.EFFDT , A.EFFSEQ , A.GPS_STATUS , A.GPS_ACTION , A.GPS_FRACTION , A.GPS_FRACTION , A.GPS_RANKING_SEQ ,A.gps_cost_center_cd ,A.gps_remark_id ,A.gps_remark_seq ,A.gps_pers_type ,A.gps_sc_group FROM %Table(gps_p_lst_flt_V) A WHERE A.effdt <= %CurrentDateIn AND A.gps_status IN ('R','N','V','O') AND NOT EXISTS ( SELECT 1 FROM PS_GPS_POST_EE EE WHERE EE.SETID = A.SETID AND EE.GPS_POST_ID = A.GPS_POST_ID AND EE.EFFDT = A.EFFDT AND EE.EFFSEQ = A.EFFSEQ) UNION SELECT A.SETID , A.GPS_POST_ID , A.GPS_ROWID , A.DEPTID , A.GPS_GRADE , A.GPS_RANK , A.GPS_POST_TYPE , A.EFFDT , A.EFFSEQ , A.GPS_STATUS , A.GPS_ACTION , A.GPS_FRACTION , A.gps_fraction - SUM(e1.GPS_FRACTION) AS gps_vacant , A.gps_ranking_seq , MAX(A.gps_cost_center_cd) , A.gps_remark_id , A.gps_remark_seq , MAX(A.gps_pers_type) , A.gps_sc_group FROM PS_GPS_P_LST_FLT_V A , ps_gps_post_ee e1 WHERE a.setid = e1.setid AND a.gps_post_id = e1.gps_post_id AND a.effdt = e1.effdt AND a.effseq = e1.effseq AND A.effdt <= %CurrentDateIn AND A.gps_status IN ('R', 'N', 'V', 'O') GROUP BY A.SETID, A.GPS_POST_ID, A.GPS_ROWID, A.DEPTID, A.GPS_GRADE, A.GPS_RANK, A.GPS_POST_TYPE, A.EFFDT, A.EFFSEQ, A.GPS_STATUS, A.GPS_ACTION, A.GPS_FRACTION, A.gps_ranking_seq, A.gps_remark_id, A.gps_remark_seq, A.gps_sc_group

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description

1 SETID Character(5) VARCHAR2(5) NOT NULL SetID
Default Value: OPR_DEF_TBL_HR.SETID
Prompt Table: SET_VLD_HR_02

2 GPS_POST_ID Character(8) VARCHAR2(8) NOT NULL GPS post id

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

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

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

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

7 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)

8 EFFDT Date(10) DATE NOT NULL Effective Date
Default Value: %date

9 EFFSEQ Number(3,0) SMALLINT NOT NULL Effective Sequence

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
Default Value: A

11 GPS_ACTION Character(8) VARCHAR2(8) NOT NULL Action as executed on GPS Post

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

13 GPS_VACANT Number(9,2) DECIMAL(8,2) NOT NULL GPS displays the total number of vacant fractions

14 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.

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

16 GPS_REMARK_ID Number(7,0) INTEGER NOT NULL GPS remark id [Vermerk] Container ID

17 GPS_REMARK_SEQ Number(4,0) SMALLINT NOT NULL GPS remark seq The individual remark sequence number. A post plan row gets associated a container ID. How many remarks (of a certain type) are associated to this post plan row is determined by the number of rows associated to this remark container.

18 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

19 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

#	PeopleSoft Field Name	PeopleSoft Field Type	Database Column Type	Description
1	SETID	Character(5)	VARCHAR2(5) NOT NULL	SetID Default Value: OPR_DEF_TBL_HR.SETID Prompt Table: SET_VLD_HR_02
2	GPS_POST_ID	Character(8)	VARCHAR2(8) NOT NULL	GPS post id
3	GPS_ROWID	Character(45)	VARCHAR2(45) NOT NULL	RowID, a condensed version of the key fields of a budgeting system
4	DEPTID	Character(10)	VARCHAR2(10) NOT NULL	Department Prompt Table: DEPT_TBL
5	GPS_GRADE	Character(8)	VARCHAR2(8) NOT NULL	Salary Grade. Prompt Table: GPS_POST_GRD_VW
6	GPS_RANK	Character(15)	VARCHAR2(15) NOT NULL	Rank for GPS
7	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)
8	EFFDT	Date(10)	DATE NOT NULL	Effective Date Default Value: %date
9	EFFSEQ	Number(3,0)	SMALLINT NOT NULL	Effective Sequence
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 Default Value: A
11	GPS_ACTION	Character(8)	VARCHAR2(8) NOT NULL	Action as executed on GPS Post
12	GPS_FRACTION	Number(9,2)	DECIMAL(8,2) NOT NULL	GPS Fraction
13	GPS_VACANT	Number(9,2)	DECIMAL(8,2) NOT NULL	GPS displays the total number of vacant fractions
14	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.
15	GPS_COST_CENTER_CD	Character(15)	VARCHAR2(15) NOT NULL	German Public Sector Cost Center.
16	GPS_REMARK_ID	Number(7,0)	INTEGER NOT NULL	GPS remark id [Vermerk] Container ID
17	GPS_REMARK_SEQ	Number(4,0)	SMALLINT NOT NULL	GPS remark seq The individual remark sequence number. A post plan row gets associated a container ID. How many remarks (of a certain type) are associated to this post plan row is determined by the number of rows associated to this remark container.
18	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
19	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