SSF_ORG_CT_D_VW(SQL View) |
Index Back |
|---|---|
Organization Contact TableUSE 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 ,CNT_DPT.COUNTRY ,CNT_DPT.ADDRESS1 ,CNT_DPT.ADDRESS2 ,CNT_DPT.ADDRESS3 ,CNT_DPT.ADDRESS4 ,CNT_DPT.CITY ,CNT_DPT.NUM1 ,CNT_DPT.NUM2 ,CNT_DPT.HOUSE_TYPE ,CNT_DPT.ADDR_FIELD1 ,CNT_DPT.ADDR_FIELD2 ,CNT_DPT.ADDR_FIELD3 ,CNT_DPT.COUNTY ,CNT_DPT.STATE ,CNT_DPT.POSTAL ,CNT_DPT.GEO_CODE ,CNT_DPT.IN_CITY_LIMIT FROM PS_ORG_CONTACT A , PS_SCC_ORGCNTC_PRI B , PS_ORG_DEPT DEPT , PS_ORG_LOCATION CNT_DPT 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) AND A.SCC_CNTC_ADDR_TYPE = 'DEPT' AND DEPT.EXT_ORG_ID = A.EXT_ORG_ID AND DEPT.ORG_DEPARTMENT = A.ORG_DEPARTMENT AND DEPT.EFFDT = ( SELECT MAX(DEPT1.EFFDT) FROM PS_ORG_DEPT DEPT1 WHERE DEPT1.EXT_ORG_ID = DEPT.EXT_ORG_ID AND DEPT1.ORG_DEPARTMENT = DEPT.ORG_DEPARTMENT AND DEPT1.EFFDT <= %CurrentDateIn) AND DEPT.EFF_STATUS = 'A' AND CNT_DPT.EXT_ORG_ID = DEPT.EXT_ORG_ID AND CNT_DPT.ORG_LOCATION = DEPT.ORG_LOCATION AND CNT_DPT.EFFDT = ( SELECT MAX(CNT_DPT2.EFFDT) FROM PS_ORG_LOCATION CNT_DPT2 WHERE CNT_DPT2.EXT_ORG_ID = CNT_DPT.EXT_ORG_ID AND CNT_DPT2.ORG_LOCATION = CNT_DPT.ORG_LOCATION AND CNT_DPT2.EFFDT <= %CurrentDateIn) AND CNT_DPT.EFF_STATUS = 'A' |
| # | 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. |
| 17 | COUNTRY | Character(3) | VARCHAR2(3) NOT NULL |
Country
Prompt Table: SCC_CNT_ADFMTVW |
| 18 | ADDRESS1 | Character(55) | VARCHAR2(55) NOT NULL | Address 1 |
| 19 | ADDRESS2 | Character(55) | VARCHAR2(55) NOT NULL | Address 2 |
| 20 | ADDRESS3 | Character(55) | VARCHAR2(55) NOT NULL | Address 3 |
| 21 | ADDRESS4 | Character(55) | VARCHAR2(55) NOT NULL | Address 4 |
| 22 | CITY | Character(30) | VARCHAR2(30) NOT NULL | City |
| 23 | NUM1 | Character(6) | VARCHAR2(6) NOT NULL | Number 1 |
| 24 | NUM2 | Character(6) | VARCHAR2(6) NOT NULL | Number 2 |
| 25 | HOUSE_TYPE | Character(2) | VARCHAR2(2) NOT NULL |
House Type
AB=House Boat Reference WW=House Trailer Reference |
| 26 | ADDR_FIELD1 | Character(2) | VARCHAR2(2) NOT NULL | Address Field 1 |
| 27 | ADDR_FIELD2 | Character(4) | VARCHAR2(4) NOT NULL | Address Field 2 |
| 28 | ADDR_FIELD3 | Character(4) | VARCHAR2(4) NOT NULL | Address Field 3 |
| 29 | COUNTY | Character(30) | VARCHAR2(30) NOT NULL | County |
| 30 | STATE | Character(6) | VARCHAR2(6) NOT NULL |
State
Prompt Table: STATE_TBL |
| 31 | POSTAL | Character(12) | VARCHAR2(12) NOT NULL | Postal |
| 32 | GEO_CODE | Character(11) | VARCHAR2(11) NOT NULL | Geo Code |
| 33 | IN_CITY_LIMIT | Character(1) | VARCHAR2(1) NOT NULL |
In City Limit
Y/N Table Edit |