GPS_POST2_SRCH

(SQL View)
 Index Back

GPS Post Search View

View used to search by Post

SELECT o.OPRID , c.setid , c.YEAR ,c.gps_post_pln_type ,c.gps_rowid ,c.deptid ,c.gps_post_type ,c.seq_nbr1 ,c.gps_grade ,c.gps_rank , COUNT(*) ,c.gps_pln_status ,c.gps_row_status ,c.gps_status ,MAX(c.gps_bdg_org1) ,MAX(c.gps_bdg_org2) ,MAX(c.gps_bdg_org3) ,MAX(c.gps_bdg_org4) ,MAX(c.gps_bdg_org5) ,c.gps_sc_group , t.status_dt FROM PS_GPS_POST_FLATVW c , PSOPRDEFN o ,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 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 EXISTS ( SELECT 1 FROM PS_GPS_SCRTYPM1_VW sec WHERE SEC.OPRID = o.OPRID AND sec.SETID = c.setid 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 ) GROUP BY o.OPRID , c.setid , c.YEAR ,c.gps_post_pln_type ,c.gps_rowid ,c.deptid ,c.gps_post_type ,c.seq_nbr1 ,c.gps_grade ,c.gps_rank ,c.gps_pln_status ,c.gps_row_status ,c.gps_status ,c.gps_sc_group, t.status_dt

# 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 DEPTID Character(10) VARCHAR2(10) NOT NULL Department
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 GPS_NUMBER Number(6,0) INTEGER NOT NULL GPS Amount of Posts to create
12 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
13 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
14 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
15 GPS_BDG_ORG1 Character(8) VARCHAR2(8) NOT NULL Budgeting Organization
16 GPS_BDG_ORG2 Character(8) VARCHAR2(8) NOT NULL Budgeting Organization
17 GPS_BDG_ORG3 Character(8) VARCHAR2(8) NOT NULL Budgeting Organization
18 GPS_BDG_ORG4 Character(8) VARCHAR2(8) NOT NULL Budgeting Organization
19 GPS_BDG_ORG5 Character(8) VARCHAR2(8) NOT NULL Budgeting Organization
20 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
21 STATUS_DT Date(10) DATE Status Date