POI_TRN2_VW (SQL View)	Index Back
POI_TRN2_VW Ext Instructor View of Persons of Interest with the 00008 Type Relationship - External Instructors. Does a Left outer join to the TRN_INSTRUCTOR table to get the status information.

POI_TRN2_VW

(SQL View)

POI_TRN2_VW Ext Instructor

View of Persons of Interest with the 00008 Type Relationship - External Instructors. Does a Left outer join to the TRN_INSTRUCTOR table to get the status information.

SELECT DISTINCT '00008' ,P.EMPLID ,' ' ,A.EFF_STATUS ,A.EFFDT ,%DateNull FROM PS_PER_POI_TYPE P , PS_TRN_INSTRCT_TBL A WHERE P.POI_TYPE = '00008' AND P.EMPLID = A.INSTRUCTOR_ID (+) AND 'E' = A.INTERNAL_EXTERNAL (+) AND A.EFFDT = ( SELECT MAX(EFFDT) FROM PS_TRN_INSTRCT_TBL B WHERE A.INSTRUCTOR_ID = B.INSTRUCTOR_ID AND B.EFFDT <= %CurrentDateIn)

Parent record: PERSON

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description

1 POI_TYPE Character(5) VARCHAR2(5) NOT NULL Defines the Transactional usage of POIs in HRMS.
Prompt Table: POI_TYPE_TBL

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

3 EMPL_RCD_CHAR Character(3) VARCHAR2(3) NOT NULL Empl_rcd# character field

4 EFF_STATUS Character(1) VARCHAR2(1) NOT NULL Effective Status
A=Active
I=Inactive

5 START_DATE Date(10) DATE Start Date for Gen Standing PO

6 END_DATE Date(10) DATE End Date

#	PeopleSoft Field Name	PeopleSoft Field Type	Database Column Type	Description
1	POI_TYPE	Character(5)	VARCHAR2(5) NOT NULL	Defines the Transactional usage of POIs in HRMS. Prompt Table: POI_TYPE_TBL
2	EMPLID	Character(11)	VARCHAR2(11) NOT NULL	Employee ID
3	EMPL_RCD_CHAR	Character(3)	VARCHAR2(3) NOT NULL	Empl_rcd# character field
4	EFF_STATUS	Character(1)	VARCHAR2(1) NOT NULL	Effective Status A=Active I=Inactive
5	START_DATE	Date(10)	DATE	Start Date for Gen Standing PO
6	END_DATE	Date(10)	DATE	End Date