GPS_POST_SRCH (SQL View)	Index Back
GPS Post Search View View used to search by Post

GPS_POST_SRCH

(SQL View)

GPS Post Search View

View used to search by Post

SELECT o.OPRID , a.setid ,a.year ,a.gps_post_pln_type ,a.gps_rowid ,a.gps_pln_status ,b.gps_post_type , c.seq_nbr ,b.gps_grade ,b.gps_rank , %CurrentDateIn ,b.gps_pln_status ,b.gps_target_soll ,a.gps_bdg_org1 ,a.gps_bdg_org2 ,a.gps_bdg_org3 ,a.gps_bdg_org4 ,a.gps_bdg_org5 ,c.gps_sc_group FROM PS_GPS_POSTPLN_TBL a , PS_GPS_POSTPLN_ROW b , PS_GPS_GRADE_TBL c , PSOPRDEFN o 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 b.SETID = c.SETID AND b.GPS_GRADE = c.GPS_GRADE AND c.EFFDT = ( SELECT MAX(sub.effdt) FROM ps_gps_grade_tbl sub WHERE c.gps_grade = sub.gps_grade AND c.setid = sub.setid AND sub.effdt <= %CurrentDateIn) AND EXISTS ( SELECT 1 FROM PS_GPS_SCRTYPM1_VW sec WHERE SEC.OPRID = o.OPRID AND sec.SETID = a.setid AND sec.GPS_ROWID = a.GPS_ROWID AND sec.GPS_SC_GROUP = c.GPS_SC_GROUP )

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description

1 OPRID Character(30) VARCHAR2(30) NOT NULL A user's ID (see PSOPRDEFN).

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

3 YEAR Character(4) VARCHAR2(4) NOT NULL Character field to represent a year

4 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

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

6 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

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

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

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

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

12 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

13 GPS_TARGET_SOLL Signed Number(10,2) DECIMAL(8,2) NOT NULL plan soll

14 GPS_BDG_ORG1 Character(8) VARCHAR2(8) NOT NULL Budgeting Organization

15 GPS_BDG_ORG2 Character(8) VARCHAR2(8) NOT NULL Budgeting Organization

16 GPS_BDG_ORG3 Character(8) VARCHAR2(8) NOT NULL Budgeting Organization

17 GPS_BDG_ORG4 Character(8) VARCHAR2(8) NOT NULL Budgeting Organization

18 GPS_BDG_ORG5 Character(8) VARCHAR2(8) NOT NULL Budgeting Organization

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	OPRID	Character(30)	VARCHAR2(30) NOT NULL	A user's ID (see PSOPRDEFN).
2	SETID	Character(5)	VARCHAR2(5) NOT NULL	SetID Default Value: OPR_DEF_TBL_HR.SETID Prompt Table: SET_VLD_HR_02
3	YEAR	Character(4)	VARCHAR2(4) NOT NULL	Character field to represent a year
4	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
5	GPS_ROWID	Character(45)	VARCHAR2(45) NOT NULL	RowID, a condensed version of the key fields of a budgeting system
6	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
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	SEQ_NBR	Number(15,0)	DECIMAL(15) NOT NULL	Sequence Number
9	GPS_GRADE	Character(8)	VARCHAR2(8) NOT NULL	Salary Grade.
10	GPS_RANK	Character(15)	VARCHAR2(15) NOT NULL	Rank for GPS
11	EFFDT	Date(10)	DATE	Effective Date Default Value: %date
12	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
13	GPS_TARGET_SOLL	Signed Number(10,2)	DECIMAL(8,2) NOT NULL	plan soll
14	GPS_BDG_ORG1	Character(8)	VARCHAR2(8) NOT NULL	Budgeting Organization
15	GPS_BDG_ORG2	Character(8)	VARCHAR2(8) NOT NULL	Budgeting Organization
16	GPS_BDG_ORG3	Character(8)	VARCHAR2(8) NOT NULL	Budgeting Organization
17	GPS_BDG_ORG4	Character(8)	VARCHAR2(8) NOT NULL	Budgeting Organization
18	GPS_BDG_ORG5	Character(8)	VARCHAR2(8) NOT NULL	Budgeting Organization
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