GPS_EE_MSG_VW (SQL View)	Index Back
GPS search vw for atstf Search unassigned(vacant or partial vacant)person with GPS informations. Used in unassigned ee message box.Use transfered param :1 (effdt) to calculate current vacant fte

GPS_EE_MSG_VW

(SQL View)

GPS search vw for atstf

Search unassigned(vacant or partial vacant)person with GPS informations. Used in unassigned ee message box.Use transfered param :1 (effdt) to calculate current vacant fte

SELECT GRADE.SETID , EMPL.EMPLID ,EMPL.EMPL_RCD ,EMPL.OPRID , JOB.FTE - SUM(E.GPS_FRACTION) GPS_VACANT_FTE ,JOB_GPS.GPS_CSR_CD ,JOB.GRADE ,GRADE.GPS_GRADE ,EMPL.NAME_DISPLAY ,JOB_GPS.GPS_SC_GROUP ,GRADE.GPS_RANKING_SEQ ,GRADE.GPS_PERS_TYPE ,COSTCENTER.GPS_COST_CENTER_CD FROM PS_PERS_SRCH_GBL EMPL , PS_JOB JOB , PS_JOB_GPS JOB_GPS , PS_GPS_COST_CENTER COSTCENTER , PS_GPS_GRADE_TBL GRADE , PS_GPS_POST_DTL D , PS_GPS_POST_EE E WHERE JOB.EMPLID = EMPL.EMPLID AND job.empl_rcd = empl.empl_rcd AND JOB.EMPLID = JOB_GPS.EMPLID AND JOB.EMPL_RCD = JOB_GPS.EMPL_RCD AND JOB.EFFDT = JOB_GPS.EFFDT AND JOB.EFFSEQ = JOB_GPS.EFFSEQ AND JOB.EMPLID = COSTCENTER.EMPLID AND JOB.EMPL_RCD = COSTCENTER.EMPL_RCD AND JOB.GRADE = GRADE.GRADE AND JOB_GPS.GPS_SC_GROUP = GRADE.GPS_SC_GROUP AND JOB_GPS.GPS_ALLOW_FLG = GRADE.GPS_ALLOWANCE AND JOB.HR_STATUS = 'A' AND JOB.EFFDT = ( SELECT MAX(JOB2.EFFDT) FROM PS_JOB JOB2 WHERE JOB2.EFFDT <=%CurrentDateIn AND JOB2.EMPLID = JOB.EMPLID AND JOB.EMPL_RCD = JOB2.EMPL_RCD) AND JOB.EFFSEQ = ( SELECT MAX(JOB2.EFFSEQ) FROM PS_JOB JOB2 WHERE JOB2.EMPLID = JOB.EMPLID AND JOB.EMPL_RCD = JOB2.EMPL_RCD AND JOB.EFFDT = JOB2.EFFDT) AND COSTCENTER.EFFDT = ( SELECT MAX(COSTCENTER2.EFFDT) FROM PS_GPS_COST_CENTER COSTCENTER2 WHERE COSTCENTER2.EFFDT <= %CurrentDateIn AND COSTCENTER.GPS_COST_CENTER_CD = COSTCENTER2.GPS_COST_CENTER_CD AND COSTCENTER.EMPLID = COSTCENTER2.EMPLID AND COSTCENTER.EMPL_RCD = COSTCENTER2.EMPL_RCD) AND GRADE.EFFDT = ( SELECT MAX(GRADE2.EFFDT) FROM PS_GPS_GRADE_TBL GRADE2 WHERE GRADE2.EFFDT <= %CurrentDateIn AND GRADE.GPS_GRADE = GRADE2.GPS_GRADE AND GRADE.SETID = GRADE2.SETID) AND JOB.APPT_TYPE = '0' AND EMPL.EMPLID = e.EMPLID AND EMPL.EMPL_RCD = e.EMPL_RCD AND D.EFFDT = ( SELECT MAX(EFFDT) FROM PS_GPS_POST_DTL DD WHERE DD.SETID = E.SETID AND DD.EFFDT <= %CurrentDateIn AND DD.GPS_POST_ID = D.GPS_POST_ID) AND D.EFFSEQ = ( SELECT MAX(EFFSEQ) FROM PS_GPS_POST_DTL DDD WHERE DDD.EFFDT = D.EFFDT AND DDD.SETID = D.SETID AND DDD.GPS_POST_ID = D.GPS_POST_ID) AND D.EFFDT = E.EFFDT AND D.EFFSEQ = E.EFFSEQ AND D.SETID = E.SETID AND D.GPS_POST_ID = E.GPS_POST_ID AND D.GPS_STATUS IN ('R', 'O', 'B', 'S') GROUP BY GRADE.SETID , EMPL.EMPLID ,EMPL.EMPL_RCD ,EMPL.OPRID ,JOB_GPS.GPS_CSR_CD ,JOB.GRADE ,GRADE.GPS_GRADE ,EMPL.NAME_DISPLAY ,JOB_GPS.GPS_SC_GROUP ,GRADE.GPS_RANKING_SEQ ,GRADE.GPS_PERS_TYPE ,COSTCENTER.GPS_COST_CENTER_CD ,JOB.FTE HAVING JOB.FTE - SUM(E.GPS_FRACTION) > 0 UNION SELECT DISTINCT GRADE.SETID , EMPL.EMPLID ,EMPL.EMPL_RCD ,EMPL.OPRID , JOB.FTE GPS_VACANT_FTE ,JOB_GPS.GPS_CSR_CD ,JOB.GRADE ,GRADE.GPS_GRADE ,EMPL.NAME_DISPLAY ,JOB_GPS.GPS_SC_GROUP ,GRADE.GPS_RANKING_SEQ ,GRADE.GPS_PERS_TYPE ,COSTCENTER.GPS_COST_CENTER_CD FROM PS_PERS_SRCH_GBL EMPL , PS_JOB JOB , PS_JOB_GPS JOB_GPS , PS_GPS_COST_CENTER COSTCENTER , PS_GPS_GRADE_TBL GRADE WHERE JOB.EMPLID = EMPL.EMPLID AND job.empl_rcd = empl.empl_rcd AND JOB.EMPLID = JOB_GPS.EMPLID AND JOB.EMPL_RCD = JOB_GPS.EMPL_RCD AND JOB.EFFDT = JOB_GPS.EFFDT AND JOB.EFFSEQ = JOB_GPS.EFFSEQ AND JOB.EMPLID = COSTCENTER.EMPLID AND JOB.EMPL_RCD = COSTCENTER.EMPL_RCD AND JOB.GRADE = GRADE.GRADE AND JOB_GPS.GPS_SC_GROUP = GRADE.GPS_SC_GROUP AND JOB_GPS.GPS_ALLOW_FLG = GRADE.GPS_ALLOWANCE AND JOB.HR_STATUS = 'A' AND JOB.EFFDT = ( SELECT MAX(JOB2.EFFDT) FROM PS_JOB JOB2 WHERE JOB2.EFFDT <= %CurrentDateIn AND JOB2.EMPLID = JOB.EMPLID AND JOB.EMPL_RCD = JOB2.EMPL_RCD) AND JOB.EFFSEQ = ( SELECT MAX(JOB2.EFFSEQ) FROM PS_JOB JOB2 WHERE JOB2.EMPLID = JOB.EMPLID AND JOB.EMPL_RCD = JOB2.EMPL_RCD AND JOB.EFFDT = JOB2.EFFDT) AND COSTCENTER.EFFDT = ( SELECT MAX(COSTCENTER2.EFFDT) FROM PS_GPS_COST_CENTER COSTCENTER2 WHERE COSTCENTER2.EFFDT <= %CurrentDateIn AND COSTCENTER.GPS_COST_CENTER_CD = COSTCENTER2.GPS_COST_CENTER_CD AND COSTCENTER.EMPLID = COSTCENTER2.EMPLID AND COSTCENTER.EMPL_RCD = COSTCENTER2.EMPL_RCD) AND GRADE.EFFDT = ( SELECT MAX(GRADE2.EFFDT) FROM PS_GPS_GRADE_TBL GRADE2 WHERE GRADE2.EFFDT <= %CurrentDateIn AND GRADE.GPS_GRADE = GRADE2.GPS_GRADE AND GRADE.SETID = GRADE2.SETID) AND JOB.APPT_TYPE = '0' AND NOT EXISTS ( SELECT 1 FROM PS_GPS_POST_DTL D , PS_GPS_POST_EE E WHERE D.EFFDT = E.EFFDT AND D.EFFSEQ = E.EFFSEQ AND D.SETID = E.SETID AND D.GPS_POST_ID = E.GPS_POST_ID AND EMPL.EMPLID = E.EMPLID AND EMPL.EMPL_RCD = E.EMPL_RCD AND D.GPS_STATUS IN ('R', 'O', 'B', 'S') AND D.EFFDT = ( SELECT MAX(EFFDT) FROM PS_GPS_POST_DTL DD WHERE DD.EFFDT <= %CurrentDateIn AND DD.SETID = D.SETID AND DD.EFFDT <= %CurrentDateIn AND DD.GPS_POST_ID = D.GPS_POST_ID) AND D.EFFSEQ = ( SELECT MAX(EFFSEQ) FROM PS_GPS_POST_DTL DDD WHERE DDD.EFFDT = D.EFFDT AND DDD.SETID = D.SETID AND DDD.GPS_POST_ID = D.GPS_POST_ID))

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description

1 SETID Character(5) VARCHAR2(5) NOT NULL SetID

2 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID

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

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

5 GPS_VACANT_FTE Number(13,6) DECIMAL(12,6) NOT NULL GPS displays the total number of vacant FTE o person

6 GPS_CSR_CD Character(15) VARCHAR2(15) NOT NULL This field is used to define Civil Service Ranks.

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

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

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

10 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

11 GPS_RANKING_SEQ Number(5,0) INTEGER NOT NULL GPS Post Grade Ranking Sequence, This field will be used to determine the actual ranking of the grade.

12 GPS_PERS_TYPE Character(1) VARCHAR2(1) NOT NULL association to person type. A post type can be associated with a person type of Civil Servant, a person type of employee or to both.
C=Civil Servant
E=Employee

13 GPS_COST_CENTER_CD Character(15) VARCHAR2(15) NOT NULL German Public Sector Cost Center.

#	PeopleSoft Field Name	PeopleSoft Field Type	Database Column Type	Description
1	SETID	Character(5)	VARCHAR2(5) NOT NULL	SetID
2	EMPLID	Character(11)	VARCHAR2(11) NOT NULL	Employee ID
3	EMPL_RCD	Number(3,0)	SMALLINT NOT NULL	Empl Record
4	OPRID	Character(30)	VARCHAR2(30) NOT NULL	A user's ID (see PSOPRDEFN).
5	GPS_VACANT_FTE	Number(13,6)	DECIMAL(12,6) NOT NULL	GPS displays the total number of vacant FTE o person
6	GPS_CSR_CD	Character(15)	VARCHAR2(15) NOT NULL	This field is used to define Civil Service Ranks.
7	GRADE	Character(3)	VARCHAR2(3) NOT NULL	Salary Grade
8	GPS_GRADE	Character(8)	VARCHAR2(8) NOT NULL	Salary Grade.
9	NAME_DISPLAY	Character(50)	VARCHAR2(50) NOT NULL	Display Name - name formatted for Display based on the Country
10	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
11	GPS_RANKING_SEQ	Number(5,0)	INTEGER NOT NULL	GPS Post Grade Ranking Sequence, This field will be used to determine the actual ranking of the grade.
12	GPS_PERS_TYPE	Character(1)	VARCHAR2(1) NOT NULL	association to person type. A post type can be associated with a person type of Civil Servant, a person type of employee or to both. C=Civil Servant E=Employee
13	GPS_COST_CENTER_CD	Character(15)	VARCHAR2(15) NOT NULL	German Public Sector Cost Center.