Penn Computing

University of Pennsylvania
Penn Computing << go backback
ST_ADDRESS Table . Pennant Student Records Home . Data Warehouse Home

ST_ADDRESS Table - Data Element Index

Select a data element to view its definition and its indexed, format, and null values.

Data element Definition
Activity_Date

Indexed - no
Format - Date
May be Null? Y

The last date any part of this record was updated in the source. Source field: SPRADDR_ACTIVITY_DATE.

Address_Type

Indexed - no
Format - Varchar2(2)
May be Null? Y

Code for the type of address. Note that a student can have more than one address of the same type. Each student address has a start and end date (end dates can be null, which should be treated the same as an end date far in the future.) If a student has more than one address of the same type, to find the most recent one you can either look at the start/end dates, or you can look for the one with the highest sequence number of that type.

Only one address for a student will be flagged as the current. See Current_Indicator.

Multiple local address types: Address_Type of 'LI' is for residence in a University-maintained building, such as a dorm or greek housing. Address_type of 'LO' means local address off-campus. Address_Type 'LF' is the address the student has indicated they are "learning from" for remote learning.
See Cautions and Notes for ST_ADDRESS Table Source field: SPRADDR_ATYP_CODE. Source validation table: STVTYP. Warehouse validation table: V_ADDRESS_TYPE

Addr_End

Indexed - no
Format - Date
May be Null? Y

The last date the address is in effect. Source field: SPRADDR_TO_DATE.

Addr_Start

Indexed - no
Format - Date
May be Null? Y

The date the address is first in effect. Source field: SPRADDR_FROM_DATE.

Addr_Type_Desc

Indexed - no
Format - Varchar2(30)
May be Null? Y

Description of the address type code. Source field: STVATYP_DESC

City

Indexed - no
Format - Varchar2(50)
May be Null? Y

The name of the city of the address

Country

Indexed - no
Format - Varchar2(5)
May be Null? Y

The code for the country of the address. Source field: SPRADDR_NATN_CODE. Validation table: STVNATN. Warehouse validation table: V_COUNTRY.

Country_Name

Indexed - no
Format - Varchar2(30)
May be Null? Y

The description of the country code. Source field: STVNATN_NATION.

Current_Indicator

Indexed - no
Format - Varchar2(1)
May be Null? Y

Calculated value, based on business rules that determine 'where is the student living today?' Rules: If the student has a current LF address use that, else if the student has a current TE address use that, else if the student has a current LI address use that, else if the student has a current LO address use that, else if the student has a current PR address use that, else if the student has a current BU address use that. Y = student is most likely currently at this address or can most likely be reached at this address, as of current date. Note that it is possible for a student to have no current address. The Current_Indicator = 'Y' does not necessarily mean mail should be sent to that address: consider instead using the address type of 'MA' for Mailing Address. See details in the notes for the ST_ADDRESS table.

Data_Source

Indexed - no
Format - Varchar2(4)
May be Null? Y

Code for the source of the address record. Source field: SPRADDR_ASRC_CODE.

Penn_ID

Indexed - no
Format - Varchar2(9)
May be Null? Y

Penn's identity management system identification number. Source field: SPRIDEN_ID

College House

Indexed - no
Format - Varchar2(100)
May be Null? Y

Based on the student's address, where Address Type = 'LI', when the address relates to a Penn College House. Note that a student's affiliation with a College House can change. A student's affiliation with a College House may persist even if the address type 'LI' has an end date that has passed. To find the most recent College House affiliation, use the max start date of any of the addresses with type 'LI'.

Pidm

Indexed - no
Format - Number
May be Null? Y

Internal identification number. Source field: SPRADDR_PIDM

Postal_Code

Indexed - no
Format - Varchar2(30)
May be Null? Y

The US zip code or foreign postal code of the address. Source field: SPRADDR_ZIP.

Refresh_Date

Indexed - no
Format - Date
May be Null? Y

The last date this warehouse table was refreshed.

Seqno

Indexed - no
Format - Number
May be Null? Y

Source internal sequence number used to sort address records. Source field: SPRADDR_SEQNO.

State

Indexed - no
Format - Varchar2(3)
May be Null? Y

The code for the US State or territory, or Canadian province, of the address. Source field: SPRADDR_STAT_CODE.

Status

Indexed - no
Format - Varchar2(1)
May be Null? Y

Indicates whether the address record is no longer considered valid/active. Inactive records will have "I" in this column. Source field: SPRADDR_STATUS_IND.

Street_1

Indexed - no
Format - Varchar2(75)
May be Null? Y

The first line of the street portion of the address. Source field: SPRADDR_STREET_LINE1.

Street_2

Indexed - no
Format - Varchar2(75)
May be Null? Y

The second line of the street portion of the address. Source field: SPRADDR_STREET_LINE2.

Street_3

Indexed - no
Format - Varchar2(75)
May be Null? Y

The third line of the street portion of the address. Source field: SPRADDR_STREET_LINE3.

Street_4

Indexed - no
Format - Varchar2(75)
May be Null? Y

The fourth line of the street portion of the address. Source field: SPRADDR_STREET_LINE4.

St_Address_Pk

Indexed - yes
Format - Varchar2(82)
May be Null? N

internal warehouse primary key: based on PIDM, ATYP_CODE, and SEQNO

Us_County

Indexed - no
Format - Varchar2(5)
May be Null? Y

The name of the county of the address. Source field: SPRADDR_CNTY_CODE. Source validation table: STVCNTY. Warehouse validation table: V_US_COUNTY.

ST_ADDRESS Table . Pennant Student Records Home . Data Warehouse Home

Questions about this page? Email us at da-staff@isc.upenn.edu

Information Systems and Computing
University of Pennsylvania
Information Systems and Computing, University of Pennsylvania