GM_PROP_DEPTJVW (SQL View)	Index Back
Proposal Dept Proj Contact Vw Used on the Project Institution Unit Panel. Gives a prompt view of all the department contacts attached to a department.

GM_PROP_DEPTJVW

(SQL View)

Proposal Dept Proj Contact Vw

Used on the Project Institution Unit Panel. Gives a prompt view of all the department contacts attached to a department.

SELECT A.SETID , A.DEPTID , A.EFFDT , B.EFF_STATUS , A.EMPLID , D.NAME FROM PS_GM_DEPT_CONTACT A , PS_GM_DEPT_CONT_DT B , PS_PERSONAL_DATA D ,PS_JOB G WHERE B.SETID = A.SETID AND B.DEPTID = A.DEPTID AND B.EFFDT = A.EFFDT AND D.EMPLID = A.EMPLID AND G.EMPLID = D.EMPLID AND G.EMPL_STATUS NOT IN ('T','D','I') AND G.EFFDT = ( SELECT MAX(H.EFFDT) FROM PS_JOB H WHERE G.EMPLID = H.EMPLID AND H.EFFDT <= %CurrentDateIn)

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description

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

2 DEPTID Character(10) VARCHAR2(10) NOT NULL Department

3 EFFDT Date(10) DATE Effective Date

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

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

6 NAME Character(50) VARCHAR2(50) NOT NULL Name

#	PeopleSoft Field Name	PeopleSoft Field Type	Database Column Type	Description
1	SETID	Character(5)	VARCHAR2(5) NOT NULL	SetID
2	DEPTID	Character(10)	VARCHAR2(10) NOT NULL	Department
3	EFFDT	Date(10)	DATE	Effective Date
4	EFF_STATUS	Character(1)	VARCHAR2(1) NOT NULL	Effective Status A=Active I=Inactive
5	EMPLID	Character(11)	VARCHAR2(11) NOT NULL	Employee ID
6	NAME	Character(50)	VARCHAR2(50) NOT NULL	Name