SSF_ORG_CTC_VW (SQL View)	Index Back
Organization Contact Table USE ORG_CONTACT to maintain information about a contact's address within an organization. The address information is maintained in the organization location table and gets associated to a contact by setting either the department number or the location number in this table.

SSF_ORG_CTC_VW

(SQL View)

Organization Contact Table

USE ORG_CONTACT to maintain information about a contact's address within an organization. The address information is maintained in the organization location table and gets associated to a contact by setting either the department number or the location number in this table.

SELECT A.EXT_ORG_ID ,B.ORG_CONTACT_TYPE ,A.ORG_CONTACT ,A.EFFDT ,A.EFF_STATUS ,A.EMPLID ,A.CONTACT_NAME ,A.JOBTITLE ,A.ORG_LOCATION ,A.ORG_DEPARTMENT ,A.EMAILID ,A.URL_ADDRESS ,A.SCC_CNTC_ADDR_TYPE ,A.ADDRESS_TYPE ,A.LASTUPDDTTM ,A.LASTUPDOPRID FROM PS_ORG_CONTACT A , PS_SCC_ORGCNTC_PRI B WHERE A.EXT_ORG_ID = B.EXT_ORG_ID AND A.ORG_CONTACT = B.ORG_CONTACT AND A.EFFDT = ( SELECT MAX(A1.EFFDT) FROM PS_ORG_CONTACT A1 WHERE A1.EXT_ORG_ID = A.EXT_ORG_ID AND A1.ORG_CONTACT = A.ORG_CONTACT AND A1.EFFDT <= %CurrentDateIn) AND A.EFF_STATUS = 'A' AND B.EFFDT = ( SELECT MAX(B1.EFFDT) FROM PS_SCC_ORGCNTC_PRI B1 WHERE B1.EXT_ORG_ID = B.EXT_ORG_ID AND B1.ORG_CONTACT_TYPE = B.ORG_CONTACT_TYPE AND B1.EFFDT <= %CurrentDateIn)

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description

1 EXT_ORG_ID Character(11) VARCHAR2(11) NOT NULL External Org ID
Prompt Table: EXT_ORG_TBL

2 ORG_CONTACT_TYPE Character(3) VARCHAR2(3) NOT NULL Contact Type
Prompt Table: ORG_CNT_TYP_TBL

3 ORG_CONTACT Number(4,0) SMALLINT NOT NULL Contact Nbr

4 EFFDT Date(10) DATE Effective Date
Default Value: %date

5 EFF_STATUS Character(1) VARCHAR2(1) NOT NULL Effective Status
A=Active
I=Inactive
Default Value: A

6 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID
Prompt Table: PEOPLE_SRCH

7 CONTACT_NAME Character(50) VARCHAR2(50) NOT NULL Contact Name

8 JOBTITLE Character(30) VARCHAR2(30) NOT NULL Job Title

9 ORG_LOCATION Number(3,0) SMALLINT NOT NULL Location Nbr
Prompt Table: ORG_LOCATION

10 ORG_DEPARTMENT Number(3,0) SMALLINT NOT NULL Department Nbr
Prompt Table: ORG_DEPT

11 EMAILID Character(70) VARCHAR2(70) NOT NULL A user's E-mail address

12 URL_ADDRESS Character(254) VARCHAR2(254) NOT NULL URL Address

13 SCC_CNTC_ADDR_TYPE Character(4) VARCHAR2(4) NOT NULL Contact Address Type
ADDR=Address Type
DEPT=Department
LOCN=Location

14 ADDRESS_TYPE Character(4) VARCHAR2(4) NOT NULL Address Type
Prompt Table: HCR_PER_ADDR_I

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

16 LASTUPDOPRID Character(30) VARCHAR2(30) NOT NULL Specifies the User ID which made the last update to an entry. This field is maintained by PeopleSoft and is used in a variety of contexts.

#	PeopleSoft Field Name	PeopleSoft Field Type	Database Column Type	Description
1	EXT_ORG_ID	Character(11)	VARCHAR2(11) NOT NULL	External Org ID Prompt Table: EXT_ORG_TBL
2	ORG_CONTACT_TYPE	Character(3)	VARCHAR2(3) NOT NULL	Contact Type Prompt Table: ORG_CNT_TYP_TBL
3	ORG_CONTACT	Number(4,0)	SMALLINT NOT NULL	Contact Nbr
4	EFFDT	Date(10)	DATE	Effective Date Default Value: %date
5	EFF_STATUS	Character(1)	VARCHAR2(1) NOT NULL	Effective Status A=Active I=Inactive Default Value: A
6	EMPLID	Character(11)	VARCHAR2(11) NOT NULL	Employee ID Prompt Table: PEOPLE_SRCH
7	CONTACT_NAME	Character(50)	VARCHAR2(50) NOT NULL	Contact Name
8	JOBTITLE	Character(30)	VARCHAR2(30) NOT NULL	Job Title
9	ORG_LOCATION	Number(3,0)	SMALLINT NOT NULL	Location Nbr Prompt Table: ORG_LOCATION
10	ORG_DEPARTMENT	Number(3,0)	SMALLINT NOT NULL	Department Nbr Prompt Table: ORG_DEPT
11	EMAILID	Character(70)	VARCHAR2(70) NOT NULL	A user's E-mail address
12	URL_ADDRESS	Character(254)	VARCHAR2(254) NOT NULL	URL Address
13	SCC_CNTC_ADDR_TYPE	Character(4)	VARCHAR2(4) NOT NULL	Contact Address Type ADDR=Address Type DEPT=Department LOCN=Location
14	ADDRESS_TYPE	Character(4)	VARCHAR2(4) NOT NULL	Address Type Prompt Table: HCR_PER_ADDR_I
15	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.
16	LASTUPDOPRID	Character(30)	VARCHAR2(30) NOT NULL	Specifies the User ID which made the last update to an entry. This field is maintained by PeopleSoft and is used in a variety of contexts.