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