GPS_PLNDTL2_VW (SQL View)	Index Back
GPS Planing Details View Flattended out view that retrieves information to a post plan and its post plan details

GPS_PLNDTL2_VW

(SQL View)

GPS Planing Details View

Flattended out view that retrieves information to a post plan and its post plan details

SELECT a.setid ,a.year ,a.gps_post_pln_type ,CASE WHEN a.GPS_GRADE_TGT > ' ' THEN a.GPS_GRADE_TGT ELSE a.GPS_GRADE END ,CASE WHEN a.GPS_RANK_TGT > ' ' THEN a.GPS_RANK_TGT ELSE a.GPS_RANK END ,CASE WHEN a.GPS_POST_TYPE_TGT > ' ' THEN a.GPS_POST_TYPE_TGT ELSE a.GPS_POST_TYPE END ,CASE WHEN a.GPS_BDG_ORG1_TGT > ' ' THEN a.GPS_BDG_ORG1_TGT ELSE b.GPS_BDG_ORG1 END ,CASE WHEN a.GPS_BDG_ORG2_TGT > ' ' THEN a.GPS_BDG_ORG2_TGT ELSE b.GPS_BDG_ORG2 END ,CASE WHEN a.GPS_BDG_ORG3_TGT > ' ' THEN a.GPS_BDG_ORG3_TGT ELSE b.GPS_BDG_ORG3 END ,CASE WHEN a.GPS_BDG_ORG4_TGT > ' ' THEN a.GPS_BDG_ORG4_TGT ELSE b.GPS_BDG_ORG4 END ,CASE WHEN a.GPS_BDG_ORG5_TGT > ' ' THEN a.GPS_BDG_ORG5_TGT ELSE b.GPS_BDG_ORG5 END ,a.gps_action ,a.GPS_NUMBER ,a.GPS_FRACTION , a.GPS_ROWID , a.GPS_GRADE , a.GPS_RANK , a.GPS_POST_TYPE FROM PS_GPS_POSTPLN_DTL a , PS_GPS_POSTPLN_TBL b WHERE a.SETID = b.SETID AND a.YEAR = b.YEAR AND a.GPS_POST_PLN_TYPE = b.GPS_POST_PLN_TYPE AND a.GPS_ROWID = b.GPS_ROWID AND a.gps_action IN ('INC', 'DEC', 'REL', 'REC')

# 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_GRADE Character(8) VARCHAR2(8) NOT NULL Salary Grade.

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

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

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

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

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

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

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

12 GPS_ACTION Character(8) VARCHAR2(8) NOT NULL Action as executed on GPS Post
Prompt Table: GPS_ACTION_VW

13 GPS_NUMBER Number(6,0) INTEGER NOT NULL GPS Amount of Posts to create

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

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

16 GPS_GRADE_TGT Character(8) VARCHAR2(8) NOT NULL GPS Grade Target

17 GPS_RANK_TGT Character(15) VARCHAR2(15) NOT NULL GPS Rank Target

18 GPS_POST_TYPE_TGT 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 Servi)
LEB=Empty Post (Civil Service)
LEE=Empty Post (Employee)

#	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_GRADE	Character(8)	VARCHAR2(8) NOT NULL	Salary Grade.
5	GPS_RANK	Character(15)	VARCHAR2(15) NOT NULL	Rank for GPS
6	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)
7	GPS_BDG_ORG1	Character(8)	VARCHAR2(8) NOT NULL	Budgeting Organization Prompt Table: GPS_ORG_DVW
8	GPS_BDG_ORG2	Character(8)	VARCHAR2(8) NOT NULL	Budgeting Organization Prompt Table: %EDITTABLE
9	GPS_BDG_ORG3	Character(8)	VARCHAR2(8) NOT NULL	Budgeting Organization Prompt Table: %EDITTABLE2
10	GPS_BDG_ORG4	Character(8)	VARCHAR2(8) NOT NULL	Budgeting Organization Prompt Table: %EDITTABLE3
11	GPS_BDG_ORG5	Character(8)	VARCHAR2(8) NOT NULL	Budgeting Organization Prompt Table: %EDITTABLE4
12	GPS_ACTION	Character(8)	VARCHAR2(8) NOT NULL	Action as executed on GPS Post Prompt Table: GPS_ACTION_VW
13	GPS_NUMBER	Number(6,0)	INTEGER NOT NULL	GPS Amount of Posts to create
14	GPS_FRACTION	Number(9,2)	DECIMAL(8,2) NOT NULL	GPS Fraction
15	GPS_ROWID	Character(45)	VARCHAR2(45) NOT NULL	RowID, a condensed version of the key fields of a budgeting system
16	GPS_GRADE_TGT	Character(8)	VARCHAR2(8) NOT NULL	GPS Grade Target
17	GPS_RANK_TGT	Character(15)	VARCHAR2(15) NOT NULL	GPS Rank Target
18	GPS_POST_TYPE_TGT	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 Servi) LEB=Empty Post (Civil Service) LEE=Empty Post (Employee)