GPS_POSTEE_SRCH (SQL View)	Index Back
GPS Post Employee Search View View used to search by Post EE

GPS_POSTEE_SRCH

(SQL View)

GPS Post Employee Search View

View used to search by Post EE

SELECT o.OPRID , b.EMPLID ,b.EMPL_RCD ,c.setid ,c.YEAR ,c.gps_post_pln_type ,c.gps_rowid ,c.gps_post_type ,c.deptid ,c.seq_nbr1 ,c.gps_grade ,c.gps_rank ,c.gps_pln_status ,c.gps_post_id ,d.effdt ,d.effseq ,c.gps_sc_group ,c.gps_bdg_system ,c.gps_bdg_org1 ,c.gps_bdg_org2 ,c.gps_bdg_org3 ,c.gps_bdg_org4 ,c.gps_bdg_org5 ,c.gps_status ,d.position_nbr ,d.jobcode ,d.grade ,d.deptid ,d.setid_dept ,a.NAME_DISPLAY ,a.NAME_DISPLAY_SRCH ,a.LAST_NAME_SRCH ,a.SECOND_LAST_SRCH ,a.MIDDLE_NAME ,a.NAME_AC , t.status_dt FROM PSOPRDEFN o ,PS_PERSON_NAME a , ps_gps_post_ee b , PS_GPS_POST_FLATVW c , PS_JOB d ,ps_gps_postpln_tbl t WHERE c.setid = t.setid AND c.year = t.year AND c.gps_post_pln_type = t.gps_post_pln_type AND c.gps_rowid = t.gps_rowid AND d.emplid = b.emplid AND d.empl_rcd = b.empl_rcd AND c.gps_post_id = b.gps_post_id AND c.setid = b.setid AND b.effdt = c.effdt AND b.effseq = c.effseq AND a.emplid = b.emplid AND c.EFFDT = ( SELECT MAX(sub.EFFDT) FROM PS_GPS_POST_FLATVW sub WHERE sub.SETID = c.SETID AND sub.YEAR = c.YEAR AND sub.GPS_POST_PLN_TYPE = c.GPS_POST_PLN_TYPE AND sub.GPS_ROWID = c.GPS_ROWID AND sub.DEPTID = c.DEPTID AND sub.seq_nbr1 = c.seq_nbr1 AND sub.GPS_GRADE = c.GPS_GRADE AND sub.GPS_RANK = c.GPS_RANK AND sub.GPS_POST_TYPE = c.GPS_POST_TYPE AND sub.gps_post_id = c.gps_post_id AND ((sub.EFFDT <= %CurrentDateIn AND c.gps_pln_status <> 'CLSD') OR (sub.effdt <= t.status_dt AND c.gps_pln_status = 'CLSD')) ) AND c.EFFSEQ = ( SELECT MAX(sub2.EFFSEQ) FROM PS_GPS_POST_FLATVW sub2 WHERE sub2.SETID = c.SETID AND sub2.YEAR = c.YEAR AND sub2.GPS_POST_PLN_TYPE = c.GPS_POST_PLN_TYPE AND sub2.GPS_ROWID = c.GPS_ROWID AND sub2.DEPTID = c.DEPTID AND sub2.seq_nbr1 = c.seq_nbr1 AND sub2.GPS_GRADE = c.GPS_GRADE AND sub2.GPS_RANK = c.GPS_RANK AND sub2.GPS_POST_TYPE = c.GPS_POST_TYPE AND sub2.gps_post_id = c.gps_post_id AND sub2.EFFDT = c.EFFDT) AND d.EFFDT =( SELECT MAX(sub_d.EFFDT) FROM PS_JOB sub_d WHERE sub_d.EMPLID=d.EMPLID AND sub_d.EMPL_RCD=d.EMPL_RCD AND ((sub_d.EFFDT <= %CurrentDateIn AND c.gps_pln_status <> 'CLSD') OR (sub_d.effdt <= t.status_dt AND c.gps_pln_status = 'CLSD')) ) AND d.effseq = ( SELECT MAX(sub_d2.effseq) FROM ps_job sub_d2 WHERE sub_d2.emplid = d.emplid AND sub_d2.empl_rcd = d.empl_rcd AND sub_d2.effdt = d.effdt) AND EXISTS ( SELECT 1 FROM PS_GPS_SCRTYPM1_VW sec WHERE SEC.OPRID = o.OPRID AND sec.GPS_ROWID = c.GPS_ROWID AND sec.GPS_SC_GROUP = c.GPS_SC_GROUP ) AND EXISTS ( SELECT 1 FROM PS_GPS_SCRTYPM2_VW sec2 WHERE SEC2.OPRID = o.OPRID AND sec2.SETID = c.setid AND sec2.tree_node = c.deptid )

# 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 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID

3 EMPL_RCD Number(3,0) SMALLINT NOT NULL Empl Record

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

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

6 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

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

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

9 DEPTID Character(10) VARCHAR2(10) NOT NULL Department

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

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

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

13 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

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

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

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

17 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

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

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

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

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

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

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

24 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

25 POSITION_NBR Character(8) VARCHAR2(8) NOT NULL Position Number

26 JOBCODE Character(6) VARCHAR2(6) NOT NULL Job Code

27 GRADE Character(3) VARCHAR2(3) NOT NULL Salary Grade

28 DEPTID2 Character(10) VARCHAR2(10) NOT NULL Department

29 SETID_DEPT Character(5) VARCHAR2(5) NOT NULL Department Set ID

30 NAME_DISPLAY Character(50) VARCHAR2(50) NOT NULL Display Name - name formatted for Display based on the Country

31 NAME_DISPLAY_SRCH Character(50) VARCHAR2(50) NOT NULL Uppercase version of Name to improve performance on searches.

32 LAST_NAME_SRCH Character(30) VARCHAR2(30) NOT NULL Last Name

33 SECOND_LAST_SRCH Character(30) VARCHAR2(30) NOT NULL Second Last Name

34 MIDDLE_NAME Character(30) VARCHAR2(30) NOT NULL MIDDLE_NAME

35 NAME_AC Character(50) VARCHAR2(50) NOT NULL Alternate Character Name

36 STATUS_DT Date(10) DATE Status Date

#	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	EMPLID	Character(11)	VARCHAR2(11) NOT NULL	Employee ID
3	EMPL_RCD	Number(3,0)	SMALLINT NOT NULL	Empl Record
4	SETID	Character(5)	VARCHAR2(5) NOT NULL	SetID Default Value: OPR_DEF_TBL_HR.SETID Prompt Table: SET_VLD_HR_02
5	YEAR	Character(4)	VARCHAR2(4) NOT NULL	Character field to represent a year
6	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
7	GPS_ROWID	Character(45)	VARCHAR2(45) NOT NULL	RowID, a condensed version of the key fields of a budgeting system
8	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)
9	DEPTID	Character(10)	VARCHAR2(10) NOT NULL	Department
10	SEQ_NBR	Number(15,0)	DECIMAL(15) NOT NULL	Sequence Number
11	GPS_GRADE	Character(8)	VARCHAR2(8) NOT NULL	Salary Grade.
12	GPS_RANK	Character(15)	VARCHAR2(15) NOT NULL	Rank for GPS
13	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
14	GPS_POST_ID	Character(8)	VARCHAR2(8) NOT NULL	GPS post id
15	EFFDT	Date(10)	DATE	Effective Date Default Value: %date
16	EFFSEQ	Number(3,0)	SMALLINT NOT NULL	Effective Sequence
17	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
18	GPS_BDG_SYSTEM	Character(1)	VARCHAR2(1) NOT NULL	Budgeting System D=Doppik K=Kameralistik O=Other
19	GPS_BDG_ORG1	Character(8)	VARCHAR2(8) NOT NULL	Budgeting Organization Prompt Table: GPS_ORG_DVW
20	GPS_BDG_ORG2	Character(8)	VARCHAR2(8) NOT NULL	Budgeting Organization Prompt Table: %EDITTABLE
21	GPS_BDG_ORG3	Character(8)	VARCHAR2(8) NOT NULL	Budgeting Organization Prompt Table: %EDITTABLE2
22	GPS_BDG_ORG4	Character(8)	VARCHAR2(8) NOT NULL	Budgeting Organization Prompt Table: %EDITTABLE3
23	GPS_BDG_ORG5	Character(8)	VARCHAR2(8) NOT NULL	Budgeting Organization Prompt Table: %EDITTABLE4
24	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
25	POSITION_NBR	Character(8)	VARCHAR2(8) NOT NULL	Position Number
26	JOBCODE	Character(6)	VARCHAR2(6) NOT NULL	Job Code
27	GRADE	Character(3)	VARCHAR2(3) NOT NULL	Salary Grade
28	DEPTID2	Character(10)	VARCHAR2(10) NOT NULL	Department
29	SETID_DEPT	Character(5)	VARCHAR2(5) NOT NULL	Department Set ID
30	NAME_DISPLAY	Character(50)	VARCHAR2(50) NOT NULL	Display Name - name formatted for Display based on the Country
31	NAME_DISPLAY_SRCH	Character(50)	VARCHAR2(50) NOT NULL	Uppercase version of Name to improve performance on searches.
32	LAST_NAME_SRCH	Character(30)	VARCHAR2(30) NOT NULL	Last Name
33	SECOND_LAST_SRCH	Character(30)	VARCHAR2(30) NOT NULL	Second Last Name
34	MIDDLE_NAME	Character(30)	VARCHAR2(30) NOT NULL	MIDDLE_NAME
35	NAME_AC	Character(50)	VARCHAR2(50) NOT NULL	Alternate Character Name
36	STATUS_DT	Date(10)	DATE	Status Date