RS_XS_ELGBLWRKR (SQL View)	Index Back
Eligible Worker view This view selects EMPLID , SUPERVISOR_ID and PER_STATUS of all Employees whose employee status is not in ('I','D','S','T','U','V'). It selects the current effective dated row whether or not the employee is eligible. This view is used in the PS Query RS_SRCH_ELGBL_WRKR which inputs to the Search Framework for PTSF index feed generation. It handles both full index create and incremental index update. It replaces both SQL statements: RS_XS_WRKR_ELGBL & RS_XS_WRKR_ELGBL_LOG. For incremental updates: 1) RS_SRCH_LOG is left joined to select employees that were updated since the last index update. 2) RS_XS_WRKRDUEDT is left joined to select employees with future dates in the RS_MEMBER_DATES table that are coming due. This ensures any future effective dated rows entered after the last index update are included in subsequent incremental updates.

RS_XS_ELGBLWRKR

(SQL View)

Eligible Worker view

This view selects EMPLID , SUPERVISOR_ID and PER_STATUS of all Employees whose employee status is not in ('I','D','S','T','U','V'). It selects the current effective dated row whether or not the employee is eligible. This view is used in the PS Query RS_SRCH_ELGBL_WRKR which inputs to the Search Framework for PTSF index feed generation. It handles both full index create and incremental index update. It replaces both SQL statements: RS_XS_WRKR_ELGBL & RS_XS_WRKR_ELGBL_LOG. For incremental updates: 1) RS_SRCH_LOG is left joined to select employees that were updated since the last index update. 2) RS_XS_WRKRDUEDT is left joined to select employees with future dates in the RS_MEMBER_DATES table that are coming due. This ensures any future effective dated rows entered after the last index update are included in subsequent incremental updates.

SELECT A.EMPLID ,A.SUPERVISOR_ID ,C.PER_STATUS , %Coalesce(L.DTTM_MODIFIED, %Dttm(B.EFFDT, %TIMEIN('23.59.00.000000'))) AS LASTUPDDTTM , %Coalesce(B2.DTTM_IN_EFFECT, %DATETIMEIN('1900-01-01 00.00.00.000000')) FROM %Table(JOB) A , %Table(PERSONAL_DATA) C , (%Table(RS_WRKR_EFFDT) B LEFT OUTER JOIN PS_RS_SRCH_LOG L ON L.DOCUMENT = B.EMPLID AND L.SRCH_TYPE_CD = 'RSFSR') LEFT OUTER JOIN PS_RS_XS_WRKRDUEDT B2 ON B.EMPLID = B2.EMPLID WHERE A.EMPLID = B.EMPLID AND B.SYSTEM_SOURCE = 'RS' AND B.EMPLID = C.EMPLID AND B.EFFDT = ( SELECT MAX(D.EFFDT) FROM %Table(RS_WRKR_EFFDT) D WHERE D.EMPLID = B.EMPLID AND D.SYSTEM_SOURCE = B.SYSTEM_SOURCE) AND A.EMPL_RCD = B.EMPL_RCD AND A.EFFDT = ( SELECT MAX(E.EFFDT) FROM %Table(JOB) E WHERE E.EMPLID = A.EMPLID AND E.EMPL_RCD = A.EMPL_RCD AND E.EFFDT <= %CurrentDateIn) AND A.EFFSEQ = ( SELECT MAX(F.EFFSEQ) FROM %Table(JOB) F WHERE F.EMPLID = A.EMPLID AND F.EMPL_RCD = A.EMPL_RCD AND F.EFFDT = A.EFFDT) AND A.EMPL_STATUS NOT IN ('I','T','S','D','U','V','Q','R','X')

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description

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

2 SUPERVISOR_ID Character(11) VARCHAR2(11) NOT NULL Supervisor ID

3 PER_STATUS Character(1) VARCHAR2(1) NOT NULL Personnel Status
E=Employee
N=Non-Employee

4 LASTUPDDTTM DateTime(26) TIMESTAMP Specifies the date and time of the last update to an entry. This field is maintained by PeopleSoft and is used in a variety of contexts.

5 DTTM_IN_EFFECT DateTime(26) TIMESTAMP Effective Date-Time

#	PeopleSoft Field Name	PeopleSoft Field Type	Database Column Type	Description
1	EMPLID	Character(11)	VARCHAR2(11) NOT NULL	Employee ID
2	SUPERVISOR_ID	Character(11)	VARCHAR2(11) NOT NULL	Supervisor ID
3	PER_STATUS	Character(1)	VARCHAR2(1) NOT NULL	Personnel Status E=Employee N=Non-Employee
4	LASTUPDDTTM	DateTime(26)	TIMESTAMP	Specifies the date and time of the last update to an entry. This field is maintained by PeopleSoft and is used in a variety of contexts.
5	DTTM_IN_EFFECT	DateTime(26)	TIMESTAMP	Effective Date-Time