GPS_DTLCOUNT_VW (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_DTLCOUNT_VW

(SQL View)

GPS Flattende View

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

SELECT A.SETID ,A.YEAR ,A.GPS_POST_PLN_TYPE ,A.GPS_ROWID ,A.DEPTID ,A.GPS_GRADE ,A.SEQ_NBR1 ,A.GPS_RANK ,A.GPS_POST_TYPE ,A.EFFDT ,A.EFFSEQ ,A.GPS_STATUS ,A.GPS_ACTION ,A.SEQ_NBR ,A.GPS_POST_ID ,A.GPS_FRACTION ,A.GPS_ROW_STATUS FROM PS_GPS_POST_FLATVW A WHERE A.EFFDT = ( SELECT MAX(sub.EFFDT) FROM PS_GPS_POST_FLATVW sub WHERE sub.SETID = A.SETID AND sub.YEAR = A.YEAR AND sub.GPS_POST_PLN_TYPE = A.GPS_POST_PLN_TYPE AND sub.GPS_ROWID = A.GPS_ROWID AND sub.DEPTID = A.DEPTID AND sub.SEQ_NBR1 = A.SEQ_NBR1 AND sub.GPS_GRADE = A.GPS_GRADE AND sub.GPS_RANK = A.GPS_RANK AND sub.GPS_POST_TYPE = A.GPS_POST_TYPE AND sub.GPS_POST_ID = A.GPS_POST_ID AND sub.EFFDT <= %CurrentDateIn) AND A.EFFSEQ = ( SELECT MAX(sub2.EFFSEQ) FROM PS_GPS_POST_FLATVW sub2 WHERE sub2.SETID = A.SETID AND sub2.YEAR = A.YEAR AND sub2.GPS_POST_PLN_TYPE = A.GPS_POST_PLN_TYPE AND sub2.GPS_ROWID = A.GPS_ROWID AND sub2.DEPTID = A.DEPTID AND sub2.SEQ_NBR1 = A.SEQ_NBR1 AND sub2.GPS_GRADE = A.GPS_GRADE AND sub2.GPS_RANK = A.GPS_RANK AND sub2.GPS_POST_TYPE = A.GPS_POST_TYPE AND sub2.GPS_POST_ID = A.GPS_POST_ID AND sub2.EFFDT = A.EFFDT) 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.YEAR ,A.GPS_POST_PLN_TYPE ,A.GPS_ROWID ,A.DEPTID ,A.GPS_GRADE ,A.SEQ_NBR1 ,A.GPS_RANK ,A.GPS_POST_TYPE ,A.EFFDT ,A.EFFSEQ ,A.GPS_STATUS ,A.GPS_ACTION ,A.SEQ_NBR ,A.GPS_POST_ID ,A.GPS_FRACTION ,A.GPS_ROW_STATUS FROM PS_GPS_POST_FLATVW A ,PS_GPS_POST_EE B WHERE A.GPS_POST_ID = B.GPS_POST_ID AND A.EFFDT = B.EFFDT AND A.EFFSEQ = B.EFFSEQ AND A.EFFDT = ( SELECT MAX(sub.EFFDT) FROM PS_GPS_POST_FLATVW sub WHERE sub.SETID = A.SETID AND sub.YEAR = A.YEAR AND sub.GPS_POST_PLN_TYPE = A.GPS_POST_PLN_TYPE AND sub.GPS_ROWID = A.GPS_ROWID AND sub.DEPTID = A.DEPTID AND sub.SEQ_NBR1 = A.SEQ_NBR1 AND sub.GPS_GRADE = A.GPS_GRADE AND sub.GPS_RANK = A.GPS_RANK AND sub.GPS_POST_TYPE = A.GPS_POST_TYPE AND sub.GPS_POST_ID = A.GPS_POST_ID AND sub.EFFDT <= %CurrentDateIn) AND A.EFFSEQ = ( SELECT MAX(sub2.EFFSEQ) FROM PS_GPS_POST_FLATVW sub2 WHERE sub2.SETID = A.SETID AND sub2.YEAR = A.YEAR AND sub2.GPS_POST_PLN_TYPE = A.GPS_POST_PLN_TYPE AND sub2.GPS_ROWID = A.GPS_ROWID AND sub2.DEPTID = A.DEPTID AND sub2.SEQ_NBR1 = A.SEQ_NBR1 AND sub2.GPS_GRADE = A.GPS_GRADE AND sub2.GPS_RANK = A.GPS_RANK AND sub2.GPS_POST_TYPE = A.GPS_POST_TYPE AND sub2.GPS_POST_ID = A.GPS_POST_ID AND sub2.EFFDT = A.EFFDT)

# 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 GPS_GRADE Character(8) VARCHAR2(8) NOT NULL Salary Grade.
Prompt Table: GPS_POST_GRD_VW

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

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 EFFDT Date(10) DATE NOT NULL Effective Date
Default Value: %date

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

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 SEQ_NBR Number(15,0) DECIMAL(15) NOT NULL Sequence Number

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

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

17 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

#	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	GPS_GRADE	Character(8)	VARCHAR2(8) NOT NULL	Salary Grade. Prompt Table: GPS_POST_GRD_VW
7	SEQ_NBR1	Number(15,0)	DECIMAL(15) NOT NULL	Sequence Number
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	EFFDT	Date(10)	DATE NOT NULL	Effective Date Default Value: %date
11	EFFSEQ	Number(3,0)	SMALLINT NOT NULL	Effective Sequence
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	SEQ_NBR	Number(15,0)	DECIMAL(15) NOT NULL	Sequence Number
15	GPS_POST_ID	Character(8)	VARCHAR2(8) NOT NULL	GPS post id
16	GPS_FRACTION	Number(9,2)	DECIMAL(8,2) NOT NULL	GPS Fraction
17	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