GPS_P_LST_FLT_V

(SQL View)
 Index Back

post last flat view

select newest post with flat values. Only select post which plan status = actv

SELECT TBL.SETID ,POST.GPS_POST_ID ,DTL.EFFDT ,DTL.EFFSEQ ,TBL.YEAR ,TBL.GPS_ROWID ,DTL.DEPTID ,ROW1.GPS_GRADE ,ROW1.GPS_RANK ,ROW1.GPS_POST_TYPE ,GRD.GPS_SC_GROUP ,DTL.GPS_STATUS ,DTL.GPS_ACTION ,DTL.GPS_FRACTION ,TBL.GPS_COST_CENTER_CD ,GRD.GPS_RANKING_SEQ ,dtl.gps_remark_id ,dtl.gps_remark_seq ,grd.gps_pers_type FROM PS_GPS_POSTPLN_TBL TBL , PS_GPS_POSTPLN_ROW ROW1 , PS_GPS_POST POST , PS_GPS_POST_DTL DTL , PS_GPS_GRADE_TBL GRD WHERE TBL.GPS_PLN_STATUS IN ('ACTV' ,'CLNP') AND DTL.GPS_POST_TYPE IN('ANG','BEA') AND tbl.year = ( SELECT MAX(YEAR) FROM PS_GPS_POSTPLN_TBL SUBTBL WHERE SUBTBL.SETID = TBL.SETID AND SUBTBL.GPS_PLN_STATUS IN ('ACTV' ,'CLNP')) AND DTL.EFFDT = ( SELECT MAX(sub.EFFDT) FROM PS_GPS_POST_DTL sub WHERE sub.SETID = DTL.SETID AND sub.GPS_POST_ID = DTL.GPS_POST_ID ) AND DTL.EFFSEQ = ( SELECT MAX(sub2.EFFSEQ) FROM PS_GPS_POST_DTL sub2 WHERE sub2.SETID = DTL.SETID AND sub2.GPS_POST_ID = DTL.GPS_POST_ID AND sub2.EFFDT = DTL.EFFDT) AND DTL.SETID = ROW1.SETID AND DTL.GPS_POST_ID = POST.GPS_POST_ID AND DTL.GPS_ROWID = POST.GPS_ROWID AND DTL.GPS_GRADE = POST.GPS_GRADE AND DTL.GPS_RANK = POST.GPS_RANK AND DTL.GPS_POST_TYPE = POST.GPS_POST_TYPE AND TBL.SETID = ROW1.SETID AND TBL.YEAR = ROW1.YEAR AND TBL.GPS_POST_PLN_TYPE = ROW1.GPS_POST_PLN_TYPE AND TBL.GPS_ROWID = ROW1.GPS_ROWID AND POST.SETID = ROW1.SETID AND POST.YEAR = ROW1.YEAR AND POST.GPS_POST_PLN_TYPE = ROW1.GPS_POST_PLN_TYPE AND POST.GPS_ROWID = ROW1.GPS_ROWID AND POST.GPS_GRADE = ROW1.GPS_GRADE AND POST.GPS_RANK = ROW1.GPS_RANK AND POST.GPS_POST_TYPE = ROW1.GPS_POST_TYPE AND ROW1.SETID = GRD.SETID AND ROW1.GPS_GRADE = GRD.GPS_GRADE AND GRD.EFFDT = ( SELECT MAX(sub.effdt) FROM PS_GPS_GRADE_TBL sub WHERE GRD.gps_grade = sub.gps_grade AND GRD.setid = sub.setid AND GRD.EFFDT <= %CurrentDateIn)

# 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 EFFDT Date(10) DATE NOT NULL Effective Date

Default Value: %date

4 EFFSEQ Number(3,0) SMALLINT NOT NULL Effective Sequence
5 YEAR Character(4) VARCHAR2(4) NOT NULL Character field to represent a year
6 GPS_ROWID Character(45) VARCHAR2(45) NOT NULL RowID, a condensed version of the key fields of a budgeting system
7 DEPTID Character(10) VARCHAR2(10) NOT NULL Department

Prompt Table: DEPT_TBL

8 GPS_GRADE Character(8) VARCHAR2(8) NOT NULL Salary Grade.

Prompt Table: GPS_POST_GRD_VW

9 GPS_RANK Character(15) VARCHAR2(15) NOT NULL Rank for GPS
10 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)
11 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
12 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

13 GPS_ACTION Character(8) VARCHAR2(8) NOT NULL Action as executed on GPS Post
14 GPS_FRACTION Number(9,2) DECIMAL(8,2) NOT NULL GPS Fraction
15 GPS_COST_CENTER_CD Character(15) VARCHAR2(15) NOT NULL German Public Sector Cost Center.
16 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.
17 GPS_REMARK_ID Number(7,0) INTEGER NOT NULL GPS remark id [Vermerk] Container ID
18 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.
19 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