GPS_POST_FLATVW (SQL View)	Index Back
GPS Flattende View Flattended out view that retrieves information to a post plan, a post plan row and associated posts

GPS_POST_FLATVW

(SQL View)

GPS Flattende View

Flattended out view that retrieves information to a post plan, a post plan row and associated posts

SELECT TBL.SETID ,TBL.YEAR ,TBL.GPS_POST_PLN_TYPE ,TBL.GPS_ROWID ,DTL.DEPTID ,GRD.SEQ_NBR ,ROW1.GPS_GRADE ,ROW1.GPS_RANK ,ROW1.GPS_POST_TYPE ,ROW1.GPS_PLN_STATUS ,DTL.EFFDT ,DTL.EFFSEQ ,POST.GPS_POST_ID ,GRD.GPS_SC_GROUP ,DTL.GPS_STATUS ,DTL.GPS_ACTION ,POST.SEQ_NBR ,DTL.GPS_FRACTION ,TBL.GPS_PLN_STATUS ,TBL.GPS_BDG_SYSTEM ,TBL.GPS_BDG_ORG1 ,TBL.GPS_BDG_ORG2 ,TBL.GPS_BDG_ORG3 ,TBL.GPS_BDG_ORG4 ,TBL.GPS_BDG_ORG5 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.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 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 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 YEAR Character(4) VARCHAR2(4) NOT NULL Character field to represent a year

3 GPS_POST_PLN_TYPE Character(1) VARCHAR2(1) NOT NULL Post Plan TYpe
0=Base Plan
1=1. Amendment Plan
2=2. Amendment Plan
3=3. Amendment Plan
4=4. Amendment Plan
Default Value: 0

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

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

6 SEQ_NBR1 Number(15,0) DECIMAL(15) NOT NULL Sequence Number

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

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

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

10 GPS_ROW_STATUS Character(4) VARCHAR2(4) NOT NULL Post Plan Row STatus
ACTV=Active
APPR=Approved
CHNG=Changed
CLNP=Clean-up Done
CLSD=Closed
COPY=Copied
DENY=Denied
NEW=New

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

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

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

14 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

15 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

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

17 SEQ_NBR Number(15,0) DECIMAL(15) NOT NULL Sequence Number

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

19 GPS_PLN_STATUS Character(4) VARCHAR2(4) NOT NULL Plan Status
ACTV=Active Plan
APPR=Approved Plan
CHNG=Changed
CLNP=Plan in Clean-Up Phase
CLSD=Plan Closed
COPY=Copied Plan
DENY=Denied
DONE=Clean-Up Done
NEW=New Plan
Default Value: NEW

20 GPS_BDG_SYSTEM Character(1) VARCHAR2(1) NOT NULL Budgeting System
D=Doppik
K=Kameralistik
O=Other

21 GPS_BDG_ORG1 Character(8) VARCHAR2(8) NOT NULL Budgeting Organization
Prompt Table: GPS_ORG_DVW

22 GPS_BDG_ORG2 Character(8) VARCHAR2(8) NOT NULL Budgeting Organization
Prompt Table: %EDITTABLE

23 GPS_BDG_ORG3 Character(8) VARCHAR2(8) NOT NULL Budgeting Organization
Prompt Table: %EDITTABLE2

24 GPS_BDG_ORG4 Character(8) VARCHAR2(8) NOT NULL Budgeting Organization
Prompt Table: %EDITTABLE3

25 GPS_BDG_ORG5 Character(8) VARCHAR2(8) NOT NULL Budgeting Organization
Prompt Table: %EDITTABLE4

#	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	YEAR	Character(4)	VARCHAR2(4) NOT NULL	Character field to represent a year
3	GPS_POST_PLN_TYPE	Character(1)	VARCHAR2(1) NOT NULL	Post Plan TYpe 0=Base Plan 1=1. Amendment Plan 2=2. Amendment Plan 3=3. Amendment Plan 4=4. Amendment Plan Default Value: 0
4	GPS_ROWID	Character(45)	VARCHAR2(45) NOT NULL	RowID, a condensed version of the key fields of a budgeting system
5	DEPTID	Character(10)	VARCHAR2(10) NOT NULL	Department Prompt Table: DEPT_TBL
6	SEQ_NBR1	Number(15,0)	DECIMAL(15) NOT NULL	Sequence Number
7	GPS_GRADE	Character(8)	VARCHAR2(8) NOT NULL	Salary Grade.
8	GPS_RANK	Character(15)	VARCHAR2(15) NOT NULL	Rank for GPS
9	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)
10	GPS_ROW_STATUS	Character(4)	VARCHAR2(4) NOT NULL	Post Plan Row STatus ACTV=Active APPR=Approved CHNG=Changed CLNP=Clean-up Done CLSD=Closed COPY=Copied DENY=Denied NEW=New
11	EFFDT	Date(10)	DATE NOT NULL	Effective Date Default Value: %date
12	EFFSEQ	Number(3,0)	SMALLINT NOT NULL	Effective Sequence
13	GPS_POST_ID	Character(8)	VARCHAR2(8) NOT NULL	GPS post id
14	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
15	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
16	GPS_ACTION	Character(8)	VARCHAR2(8) NOT NULL	Action as executed on GPS Post
17	SEQ_NBR	Number(15,0)	DECIMAL(15) NOT NULL	Sequence Number
18	GPS_FRACTION	Number(9,2)	DECIMAL(8,2) NOT NULL	GPS Fraction
19	GPS_PLN_STATUS	Character(4)	VARCHAR2(4) NOT NULL	Plan Status ACTV=Active Plan APPR=Approved Plan CHNG=Changed CLNP=Plan in Clean-Up Phase CLSD=Plan Closed COPY=Copied Plan DENY=Denied DONE=Clean-Up Done NEW=New Plan Default Value: NEW
20	GPS_BDG_SYSTEM	Character(1)	VARCHAR2(1) NOT NULL	Budgeting System D=Doppik K=Kameralistik O=Other
21	GPS_BDG_ORG1	Character(8)	VARCHAR2(8) NOT NULL	Budgeting Organization Prompt Table: GPS_ORG_DVW
22	GPS_BDG_ORG2	Character(8)	VARCHAR2(8) NOT NULL	Budgeting Organization Prompt Table: %EDITTABLE
23	GPS_BDG_ORG3	Character(8)	VARCHAR2(8) NOT NULL	Budgeting Organization Prompt Table: %EDITTABLE2
24	GPS_BDG_ORG4	Character(8)	VARCHAR2(8) NOT NULL	Budgeting Organization Prompt Table: %EDITTABLE3
25	GPS_BDG_ORG5	Character(8)	VARCHAR2(8) NOT NULL	Budgeting Organization Prompt Table: %EDITTABLE4