GPS_UNASEE_SRCH

(SQL View)
 Index Back

GPS search vw for atstf

Search unassigned(vacant or partial vacant)person with GPS informations. Used in auto-staffing person search.Exclude the EE who has been staffed in future efeective date.

SELECT SEC.SETID , SEC.EMPLID , SEC.EMPL_RCD , SEC.OPRID , NM.NAME_DISPLAY_SRCH , NM.FIRST_NAME_SRCH , NM.LAST_NAME_SRCH , NM.SECOND_LAST_SRCH , NM.MIDDLE_NAME , SEC.SETID_DEPT , SEC.DEPTID , SEC.JOBCODE , SEC.POSITION_NBR , SEC.FTE - ASEE.GPS_FRACTION GPS_VACANT_FTE , SEC.GRADE , SEC.GPS_RANKING_SEQ , SEC.GPS_PERS_TYPE , SEC.GPS_SC_GROUP , SEC.GPS_CSR_CD , SEC.GPS_ALLOWANCE , SEC.GPS_COST_CENTER_CD , SEC.GPS_GRADE , ASEE.GPS_DIVIDED_FRAC FROM %Table(GPS_STF_ALLEE_V) SEC , PS_NAMES NM , %Table(GPS_STF_ASFTE_V) ASEE WHERE %Sql(SCRTY_NAME) AND SEC.EMPLID = ASEE.EMPLID AND SEC.EMPL_RCD = ASEE.EMPL_RCD AND SEC.FTE - ASEE.GPS_FRACTION > 0 AND NOT EXISTS( SELECT 1 FROM PS_GPS_POST_EE EE WHERE EE.EMPLID = SEC.EMPLID AND EE.EMPL_RCD = SEC.EMPL_RCD AND EE.EFFDT >%CurrentDateIn) UNION SELECT SEC.SETID , SEC.EMPLID , SEC.EMPL_RCD , SEC.OPRID , NM.NAME_DISPLAY_SRCH , NM.FIRST_NAME_SRCH , NM.LAST_NAME_SRCH , NM.SECOND_LAST_SRCH , NM.MIDDLE_NAME , SEC.SETID_DEPT , SEC.DEPTID , SEC.JOBCODE , SEC.POSITION_NBR , SEC.FTE GPS_VACANT_FTE , SEC.GRADE , SEC.GPS_RANKING_SEQ , SEC.GPS_PERS_TYPE , SEC.GPS_SC_GROUP , SEC.GPS_CSR_CD , SEC.GPS_ALLOWANCE , SEC.GPS_COST_CENTER_CD , SEC.GPS_GRADE , 0 FROM %Table(GPS_STF_ALLEE_V) SEC , PS_NAMES NM WHERE %Sql(SCRTY_NAME) AND SEC.FTE > 0 AND NOT EXISTS( SELECT 1 FROM PS_GPS_POST_EE EE WHERE EE.EMPLID = SEC.EMPLID AND EE.EMPL_RCD = SEC.EMPL_RCD AND EE.EFFDT >%CurrentDateIn) AND NOT EXISTS ( SELECT 1 FROM PS_GPS_STF_DTL_VW 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 D.EFFDT = ( SELECT MAX(EFFDT) FROM PS_GPS_STF_DTL_VW 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_STF_DTL_VW DDD WHERE DDD.EFFDT = D.EFFDT AND DDD.SETID = D.SETID AND DDD.GPS_POST_ID = D.GPS_POST_ID) AND SEC.EMPLID = E.EMPLID AND SEC.EMPL_RCD = E.EMPL_RCD AND D.GPS_STATUS IN ('R', 'O', 'B', 'S') GROUP BY E.SETID, E.EMPLID, E.EMPL_RCD)

# 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 NAME_DISPLAY_SRCH Character(50) VARCHAR2(50) NOT NULL Uppercase version of Name to improve performance on searches.
6 FIRST_NAME_SRCH Character(30) VARCHAR2(30) NOT NULL First Name
7 LAST_NAME_SRCH Character(30) VARCHAR2(30) NOT NULL Last Name
8 SECOND_LAST_SRCH Character(30) VARCHAR2(30) NOT NULL Second Last Name
9 MIDDLE_NAME Character(30) VARCHAR2(30) NOT NULL MIDDLE_NAME
10 SETID_DEPT Character(5) VARCHAR2(5) NOT NULL Department Set ID
11 DEPTID Character(10) VARCHAR2(10) NOT NULL Department
12 JOBCODE Character(6) VARCHAR2(6) NOT NULL Job Code
13 POSITION_NBR Character(8) VARCHAR2(8) NOT NULL Position Number
14 GPS_VACANT_FTE Number(13,6) DECIMAL(12,6) NOT NULL GPS displays the total number of vacant FTE o person
15 GRADE Character(3) VARCHAR2(3) NOT NULL Salary Grade
16 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.
17 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
18 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
19 GPS_CSR_CD Character(15) VARCHAR2(15) NOT NULL This field is used to define Civil Service Ranks.
20 GPS_ALLOWANCE Character(1) VARCHAR2(1) NOT NULL allowance Flag
21 GPS_COST_CENTER_CD Character(15) VARCHAR2(15) NOT NULL German Public Sector Cost Center.
22 GPS_GRADE Character(8) VARCHAR2(8) NOT NULL Salary Grade.
23 GPS_DIVIDED_FRAC Number(2,0) SMALLINT NOT NULL Number of EE splitted .