GPS_STF_EE_DT_V (SQL View)	Index Back
EE infos with effective dates Select EE infos with effective date values.

GPS_STF_EE_DT_V

(SQL View)

EE infos with effective dates

Select EE infos with effective date values.

SELECT DISTINCT GRADE.SETID , EMPL.EMPLID , EMPL.EMPL_RCD , EMPL.OPRID , POST_DTL.GPS_POST_ID , JOB.EFFDT , POST_EE.EFFDT , COSTCENTER.EFFDT ,GRADE.EFFDT , EMPL.NAME_DISPLAY , EMPL.NAME_DISPLAY_SRCH , EMPL.LAST_NAME_SRCH , JOB.FTE , POST_EE.GPS_FRACTION , JOB.GRADE , GRADE.GPS_SC_GROUP , COSTCENTER.GPS_COST_CENTER_CD ,GRADE.GPS_PERS_TYPE ,JOB_GPS.GPS_CSR_CD ,GRADE.GPS_GRADE ,GRADE.GPS_RANKING_SEQ 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_STF_DTL_VW POST_DTL , PS_GPS_POST_EE POST_EE WHERE EMPL.EMPLID = JOB.EMPLID AND EMPL.EMPL_RCD = JOB.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 POST_DTL.EFFDT = POST_EE.EFFDT AND POST_DTL.EFFSEQ = POST_EE.EFFSEQ AND POST_DTL.SETID = POST_EE.SETID AND POST_DTL.GPS_POST_ID = POST_EE.GPS_POST_ID AND EMPL.EMPLID = POST_EE.EMPLID AND EMPL.EMPL_RCD = POST_EE.EMPL_RCD AND JOB.EFFSEQ = ( SELECT MAX(SUB1_JOB.EFFSEQ) FROM PS_JOB SUB1_JOB WHERE SUB1_JOB.EMPLID = JOB.EMPLID AND JOB.EMPL_RCD = SUB1_JOB.EMPL_RCD AND JOB.EFFDT = SUB1_JOB.EFFDT) AND POST_DTL.EFFSEQ = ( SELECT MAX(EFFSEQ) FROM PS_GPS_STF_DTL_VW SUB1_POST_DTL WHERE SUB1_POST_DTL.EFFDT = POST_DTL.EFFDT AND SUB1_POST_DTL.SETID = POST_DTL.SETID AND SUB1_POST_DTL.GPS_POST_ID = POST_DTL.GPS_POST_ID) AND JOB.HR_STATUS = 'A' AND JOB.APPT_TYPE = '0' AND POST_DTL.GPS_STATUS IN ('R', 'O', 'B', 'S') UNION SELECT DISTINCT GRADE.SETID , EMPL.EMPLID , EMPL.EMPL_RCD , EMPL.OPRID , ' ' GPS_POST_ID , JOB.EFFDT ,%DateIn('1900-01-01') , COSTCENTER.EFFDT ,GRADE.EFFDT , EMPL.NAME_DISPLAY , EMPL.NAME_DISPLAY_SRCH , EMPL.LAST_NAME_SRCH , JOB.FTE , 0 , JOB.GRADE , GRADE.GPS_SC_GROUP , COSTCENTER.GPS_COST_CENTER_CD ,GRADE.GPS_PERS_TYPE ,JOB_GPS.GPS_CSR_CD ,GRADE.GPS_GRADE ,GRADE.GPS_RANKING_SEQ 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 EMPL.EMPLID = JOB.EMPLID AND EMPL.EMPL_RCD = JOB.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.EFFSEQ = ( SELECT MAX(SUB1_JOB.EFFSEQ) FROM PS_JOB SUB1_JOB WHERE SUB1_JOB.EMPLID = JOB.EMPLID AND SUB1_JOB.EMPL_RCD = JOB.EMPL_RCD AND SUB1_JOB.EFFDT = JOB.EFFDT) AND NOT EXISTS ( SELECT 1 FROM PS_GPS_STF_DTL_VW POST_DTL , PS_GPS_POST_EE POST_EE WHERE POST_DTL.EFFDT = POST_EE.EFFDT AND POST_DTL.EFFSEQ = POST_EE.EFFSEQ AND POST_DTL.SETID = POST_EE.SETID AND POST_DTL.GPS_POST_ID = POST_EE.GPS_POST_ID AND EMPL.EMPLID = POST_EE.EMPLID AND EMPL.EMPL_RCD = POST_EE.EMPL_RCD AND POST_DTL.GPS_STATUS IN ('R', 'O', 'B', 'S') AND POST_DTL.EFFSEQ = ( SELECT MAX(EFFSEQ) FROM PS_GPS_STF_DTL_VW SUB1_POST_DTL WHERE SUB1_POST_DTL.EFFDT = POST_DTL.EFFDT AND SUB1_POST_DTL.SETID = POST_DTL.SETID AND SUB1_POST_DTL.GPS_POST_ID = POST_DTL.GPS_POST_ID)) AND JOB.HR_STATUS = 'A' AND JOB.APPT_TYPE = '0' AND JOB.FTE > 0

# 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_POST_ID Character(8) VARCHAR2(8) NOT NULL GPS post id

6 GPS_STF_JOB_EFFDT Date(10) DATE Effective Date

7 GPS_STF_DTL_EFFDT Date(10) DATE Effective Date

8 GPS_STF_CC_EFFDT Date(10) DATE Effective Date

9 GPS_STF_GRD_EFFDT Date(10) DATE GRADE EFFDT FOR UNASEE PROMPT VIEW.

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

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

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

13 FTE Number(8,6) DECIMAL(7,6) NOT NULL This field represent Full Time Equivalence

14 GPS_ASND_FRAC Number(13,6) DECIMAL(12,6) NOT NULL Assigned fraction of post. used in auto-staffing

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

16 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

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

18 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

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

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

21 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.

#	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_POST_ID	Character(8)	VARCHAR2(8) NOT NULL	GPS post id
6	GPS_STF_JOB_EFFDT	Date(10)	DATE	Effective Date
7	GPS_STF_DTL_EFFDT	Date(10)	DATE	Effective Date
8	GPS_STF_CC_EFFDT	Date(10)	DATE	Effective Date
9	GPS_STF_GRD_EFFDT	Date(10)	DATE	GRADE EFFDT FOR UNASEE PROMPT VIEW.
10	NAME_DISPLAY	Character(50)	VARCHAR2(50) NOT NULL	Display Name - name formatted for Display based on the Country
11	NAME_DISPLAY_SRCH	Character(50)	VARCHAR2(50) NOT NULL	Uppercase version of Name to improve performance on searches.
12	LAST_NAME_SRCH	Character(30)	VARCHAR2(30) NOT NULL	Last Name
13	FTE	Number(8,6)	DECIMAL(7,6) NOT NULL	This field represent Full Time Equivalence
14	GPS_ASND_FRAC	Number(13,6)	DECIMAL(12,6) NOT NULL	Assigned fraction of post. used in auto-staffing
15	GRADE	Character(3)	VARCHAR2(3) NOT NULL	Salary Grade
16	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
17	GPS_COST_CENTER_CD	Character(15)	VARCHAR2(15) NOT NULL	German Public Sector Cost Center.
18	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
19	GPS_CSR_CD	Character(15)	VARCHAR2(15) NOT NULL	This field is used to define Civil Service Ranks.
20	GPS_GRADE	Character(8)	VARCHAR2(8) NOT NULL	Salary Grade.
21	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.