View - dbo.arch

Property	Value
Name	arch_Student
Schema	dbo
Quoted Identifier Status	True
Ansi Nulls Status	True
Returns View Metadata	False
Create Date	10/1/2014 10:06 AM
Description

Column Name	Data Type	Allow Nulls	Fulltext Indexed	Identity	Description / Computed Formula
AcademicDisadvantage	varchar(3)	True	False	False
AddDate	date(10, 0)	True	False	False
AddProgram	varchar(8)	True	False	False
AddTime	varchar(12)	True	False	False
AddUserId	varchar(8)	True	False	False
AlienCardIssueDate	date(10, 0)	True	False	False
AlienCardNumber	varchar(10)	True	False	False
AlienNumber	varchar(10)	True	False	False
AmIndianAKNative	varchar(3)	True	False	False
Appendage	varchar(4)	True	False	False
ApplicationFeeStatus	varchar(25)	True	False	False
Asian	varchar(3)	True	False	False
AuthorizedFinancialAidBookStore	varchar(3)	False	False	False
AwardLevel	varchar(30)	False	False	False
AwardLevelCode	varchar(3)	True	False	False
BankAccountNumber	varchar(20)	True	False	False
BankRoutingNumber	varchar(9)	True	False	False
BasisOfResidency	varchar(30)	False	False	False
BillingSelection	varchar(34)	False	False	False
BlackAfricanAmerican	varchar(3)	True	False	False
CellPhone	varchar(20)	True	False	False
CollegeCreditApplication	varchar(3)	True	False	False
CountryOfBirth	varchar(30)	False	False	False
CreditApplicationDate	date(10, 0)	True	False	False
CreditApplicationTerm	varchar(6)	True	False	False
CreditBasisOfAdmissions	varchar(30)	False	False	False
CreditBasisOfAdmissionsCode	varchar(2)	True	False	False
CreditReAdmitTerm	varchar(6)	True	False	False
DateOfBirth	date(10, 0)	True	False	False
DeceasedDate	date(10, 0)	True	False	False
DeceasedVerification	varchar(30)	False	False	False
EconomicDisadvantage	varchar(3)	True	False	False
EducationLevel	varchar(30)	False	False	False
EftAccountActive	varchar(4)	False	False	False
EftAccountVerified	varchar(3)	False	False	False
EftAuthorizedInd	varchar(3)	False	False	False
EftBankAccountType	varchar(8)	False	False	False
EligibleToReturnTerm	varchar(6)	True	False	False
EmergencyContact	varchar(50)	True	False	False
EmergencyHomePhoneNumber	varchar(20)	True	False	False
EmergencyRelative	varchar(30)	False	False	False
EmergencyRelativeCode	varchar(1)	True	False	False
EmergencyWorkPhoneNumber	varchar(20)	True	False	False
EnteredShortCredictApplication	varchar(3)	False	False	False
Ethnicity	varchar(30)	False	False	False
FactsConfirmationNumber	varchar(23)	True	False	False
FirstGenerationStudent	varchar(3)	True	False	False
FirstName	varchar(20)	True	False	False
Fullname	varchar(50)	True	False	False
GEDGraduated	varchar(3)	False	False	False
Gender	varchar(7)	False	False	False
HasDisabilities	varchar(3)	True	False	False
HawaiianPacIslander	varchar(3)	True	False	False
HighSchoolApplication	varchar(3)	True	False	False
HighSchoolApplicationDate	date(10, 0)	True	False	False
HighSchoolApplicationTerm	varchar(6)	True	False	False
HighSchoolBasisOfAdmissions	varchar(30)	False	False	False
HighSchoolBasisOfAdmissionsCode	varchar(2)	True	False	False
HighSchoolGraduationDate	date(10, 0)	True	False	False
HighSchoolProgramCode	varchar(5)	True	False	False
HighSchoolReAdmitTerm	varchar(6)	True	False	False
HoldField	int(10, 0)	True	False	False
HomeCampus	varchar(50)	False	False	False
HomeCampusPreferenceCode	varchar(5)	True	False	False
HomePhone	varchar(20)	True	False	False
ImmigrationStatus	varchar(30)	False	False	False
ImmigrationStatusCode	varchar(2)	True	False	False
InStateResidencyVerificationDate	date(10, 0)	True	False	False
InStateResident	varchar(3)	False	False	False
InstitutionalEmailAddress	varchar(50)	True	False	False
ISN_ST_STDNT_A	int(10, 0)	False	False	False
LastName	varchar(35)	True	False	False
LastTermAttended	varchar(6)	True	False	False
LICENSE_STATE	varchar(2)	True	False	False
LicenseDate	date(10, 0)	True	False	False
LicenseNumber	varchar(20)	True	False	False
LicenseState	varchar(30)	True	False	False
MaintenanceDate	date(10, 0)	True	False	False
MaintenanceProgram	varchar(8)	True	False	False
MaintenanceTime	varchar(12)	True	False	False
MaintenanceUserId	varchar(8)	True	False	False
MiddleName	varchar(20)	True	False	False
NonCreditApplication	varchar(3)	True	False	False
NonCreditApplicationDate	date(10, 0)	True	False	False
NonCreditApplicationTerm	varchar(6)	True	False	False
NonCreditBasisOfAdmissions	varchar(30)	False	False	False
NonCreditBasisOfAdmissionsCode	varchar(2)	True	False	False
NonCreditProgramCode	varchar(5)	True	False	False
NonCreditReAdmitTerm	varchar(6)	True	False	False
NotReported	varchar(3)	True	False	False
OverallCumGPA	numeric(4, 2)	False	False	False
OverallGpaHours	numeric(5, 2)	False	False	False
OverallGradePoints	numeric(7, 2)	False	False	False
OverallHoursAttempted	numeric(5, 2)	False	False	False
OverallHoursEarned	numeric(5, 2)	False	False	False
PayCurrentTermNonTuitionWithFinancialAid	varchar(3)	True	False	False
PayPriorTermNonTuitionWithFinancialAid	varchar(3)	True	False	False
PersonalEmailAddress	varchar(40)	True	False	False
PinNumber	varchar(6)	True	False	False
PrimaryLanguage	varchar(30)	False	False	False
ProgramCode	varchar(5)	True	False	False
ProspectApplication	varchar(3)	True	False	False
ResidencyCode	varchar(1)	True	False	False
ResidencyCountry	varchar(30)	False	False	False
ResidencyCounty	varchar(30)	False	False	False
ResidencyRelationship	varchar(30)	False	False	False
ResidencyState	varchar(30)	False	False	False
ResidencyStatus	varchar(30)	False	False	False
ResSupportFirstName	varchar(20)	True	False	False
ResSupportLastName	varchar(35)	True	False	False
ResSupportMiddleName	varchar(20)	True	False	False
ResSupportPhoneNumber	varchar(20)	True	False	False
Salutation	varchar(3)	True	False	False
SecurityQuestion	varchar(30)	True	False	False
SecurityResponse	varchar(40)	True	False	False
SiteSpecificField	varchar(50)	True	False	False
SpecialDesignator1	varchar(30)	True	False	False
SpecialDesignator10	varchar(30)	True	False	False
SpecialDesignator2	varchar(30)	True	False	False
SpecialDesignator3	varchar(30)	True	False	False
SpecialDesignator4	varchar(30)	True	False	False
SpecialDesignator5	varchar(30)	True	False	False
SpecialDesignator6	varchar(30)	True	False	False
SpecialDesignator7	varchar(30)	True	False	False
SpecialDesignator8	varchar(30)	True	False	False
SpecialDesignator9	varchar(30)	True	False	False
StudentId	varchar(9)	True	False	False
StudentSSN	varchar(11)	True	False	False
StudentSSNType	varchar(5)	False	False	False
StudentStatus	varchar(15)	True	False	False
SuspensionStartTerm	varchar(6)	True	False	False
VehicleRegistrationDate	date(10, 0)	True	False	False
VehicleRegistrationNumber	varchar(20)	True	False	False
VehicleRegistrationState	varchar(30)	True	False	False
VeteranInd	varchar(3)	True	False	False
VisaExperationDate	date(10, 0)	True	False	False
VisaIssueDate	date(10, 0)	True	False	False
VisaType	varchar(30)	False	False	False
VisaTypeCode	varchar(3)	True	False	False
VocationalApplication	varchar(3)	True	False	False
VocationalApplicationDate	date(10, 0)	True	False	False
VocationalApplicationTerm	varchar(6)	True	False	False
VocationalBasisOfAdmissions	varchar(30)	False	False	False
VocationalBasisOfAdmissionsCode	varchar(2)	True	False	False
VocationalProgramCode	varchar(5)	True	False	False
VocationalReAdmitTerm	varchar(6)	True	False	False
VoterCounty	varchar(30)	True	False	False
VoterNumber	varchar(20)	True	False	False
VoterRegistrationDate	date(10, 0)	True	False	False
VoterState	varchar(30)	True	False	False
White	varchar(3)	True	False	False
WorkPhone	varchar(20)	True	False	False

Name

Insert

Update

Delete

Instead

Name

Clustered

Columns

Name	Type
FAC192_SITE_A	Table
ST_STDNT_A	Table
ST_STDNT_A_SPCL_DSGNTR	Table
UTL_CODE_TABLE	Table
arch_StudentCombined	View
arch_StudentLastTermAttended	View
uvw_StudentRace	View

Name	Type
arch_StudentCombined	VIEW
uvw_StudentRace	VIEW
arch_StudentLastTermAttended	VIEW
ST_STDNT_A	TABLE
ST_STDNT_A_SPCL_DSGNTR	TABLE
FAC192_SITE_A	TABLE
UTL_CODE_TABLE	TABLE

Object

Property

Value

Object

Property

Value

/****** Object: View [dbo].[arch_Student] Script Date: 03/09/2017 16:47:09 ******/
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE VIEW [dbo].[arch_Student]
AS
    SELECT   ST_STDNT_A.ISN_ST_STDNT_A
            ,CASE
               WHEN ST_STDNT_A.ADD_DATE > ' ' THEN CONVERT(DATE, ST_STDNT_A.ADD_DATE)
               END AS AddDate
            ,SUBSTRING(REPLICATE('0',7-LEN(ST_STDNT_A.ADD_TIME))+CONVERT(VARCHAR,ST_STDNT_A.ADD_TIME),1,2) + ':' +
               SUBSTRING(REPLICATE('0',7-LEN(ST_STDNT_A.ADD_TIME))+CONVERT(VARCHAR,ST_STDNT_A.ADD_TIME),3,2) + ':' +
               SUBSTRING(REPLICATE('0',7-LEN(ST_STDNT_A.ADD_TIME))+CONVERT(VARCHAR,ST_STDNT_A.ADD_TIME),5,2) + '.' +
               SUBSTRING(REPLICATE('0',7-LEN(ST_STDNT_A.ADD_TIME))+CONVERT(VARCHAR,ST_STDNT_A.ADD_TIME),7,1) + '00'
               AS AddTime
             ,ST_STDNT_A.ADD_USER_ID AS AddUserId
            ,ST_STDNT_A.ADD_PGM AS AddProgram
            ,CASE WHEN ST_STDNT_A.MAINT_DATE > ' ' THEN CONVERT(DATE, ST_STDNT_A.MAINT_DATE)
            END AS MaintenanceDate
            ,SUBSTRING(REPLICATE('0',7-LEN(ST_STDNT_A.MAINT_TIME))+CONVERT(VARCHAR,ST_STDNT_A.MAINT_TIME),1,2) + ':' +
               SUBSTRING(REPLICATE('0',7-LEN(ST_STDNT_A.MAINT_TIME))+CONVERT(VARCHAR,ST_STDNT_A.MAINT_TIME),3,2) + ':' +
               SUBSTRING(REPLICATE('0',7-LEN(ST_STDNT_A.MAINT_TIME))+CONVERT(VARCHAR,ST_STDNT_A.MAINT_TIME),5,2) + '.' +
               SUBSTRING(REPLICATE('0',7-LEN(ST_STDNT_A.MAINT_TIME))+CONVERT(VARCHAR,ST_STDNT_A.MAINT_TIME),7,1) + '00'
               As MaintenanceTime
            ,ST_STDNT_A.MAINT_USER_ID AS MaintenanceUserId
            ,ST_STDNT_A.MAINT_PGM AS MaintenanceProgram
            ,ST_STDNT_A.HOLD AS HoldField
            ,ST_STDNT_A.STUDENT_ID AS StudentId
            ,'XXX-XX' + SUBSTRING(ST_STDNT_A.STUDENT_SSN,6,1) + '-' + SUBSTRING(ST_STDNT_A.STUDENT_SSN,7,3) AS StudentSSN
            ,CASE STDNT_TY
               WHEN ' ' THEN 'Valid'
               ELSE 'Dummy'
             END AS StudentSSNType
            ,CAST(
              CASE
                WHEN STUDENT_INACTIVE = 'Y' THEN 'In-Active'
                ELSE 'Active'
              END AS Varchar(15)) AS StudentStatus
            ,ST_STDNT_A.PIN_NUM AS PinNumber
            ,CAST(
              CASE COLL_CRED_FLG
                WHEN 1 THEN 'Yes'
                ELSE 'No'
              END AS VARCHAR(3)) AS CollegeCreditApplication
            ,CAST(
              CASE VOC_FLG
                WHEN 1 THEN 'Yes'
                ELSE 'No'
              END AS VARCHAR(3)) AS VocationalApplication
            ,CAST(
              CASE HS_COMPL_FLG
                WHEN 1 THEN 'Yes'
                ELSE 'No'
              END AS VARCHAR(3)) AS HighSchoolApplication
            ,CAST(
              CASE NON_CRED_FLG
                WHEN 1 THEN 'Yes'
                ELSE 'No'
              END AS VARCHAR(3)) AS NonCreditApplication
            ,CAST(
              CASE PRSPCT_FLG
                WHEN 1 THEN 'Yes'
                ELSE 'No'
              END AS VARCHAR(3)) AS ProspectApplication
            ,ST_STDNT_A.LST_NM AS LastName
            ,ST_STDNT_A.FRST_NM AS FirstName
            ,ST_STDNT_A.MDL_NM AS MiddleName
            ,CAST(ISNULL(ST_STDNT_A.LST_NM, 'N/A') + ', '
                + ISNULL(ST_STDNT_A.FRST_NM, ' ') + ' '
                + ISNULL(ST_STDNT_A.MDL_NM, ' ')
               AS varchar(50)) AS Fullname
            ,ST_STDNT_A.APPEND AS Appendage
            ,ST_STDNT_A.SALUT AS Salutation
            ,CASE
                WHEN ST_STDNT_A.DOB IS NULL THEN '1800-01-01'
                WHEN ISDATE(ST_STDNT_A.DOB) = 1 THEN CONVERT(Date,ST_STDNT_A.DOB)
                ELSE '1800-01-01'
              END AS DateOfBirth
            ,CASE
               WHEN ST_STDNT_A.SEX = 'M' THEN 'Male'
               WHEN ST_STDNT_A.SEX = 'F' THEN 'Female'
               WHEN ST_STDNT_A.SEX = 'U' THEN 'Unknown'
               ELSE 'N/A'
             END AS Gender
            ,CONVERT(DATE, ST_STDNT_A.HS_GRAD_DT) AS HighSchoolGraduationDate
            ,ST_STDNT_A.RES_CD AS ResidencyCode
            ,ISNULL(ResCd.DESCRIPTION,'N/A') AS ResidencyStatus
            ,ISNULL(ResSt.DESCRIPTION,'N/A') AS ResidencyState
            ,ISNULL(ResCounty.DESCRIPTION,'N/A') AS ResidencyCounty
            ,ISNULL(ResCountry.DESCRIPTION,'N/A') AS ResidencyCountry
            ,CONVERT(DATE, ST_STDNT_A.RES_FL_DT) AS InStateResidencyVerificationDate
            ,ST_STDNT_A.IMMIG_STAT AS ImmigrationStatusCode
            ,ISNULL(ImmigStat.DESCRIPTION,'N/A') AS ImmigrationStatus
            ,ST_STDNT_A.ALIEN_NUM AS AlienNumber
            ,ST_STDNT_A.HM_PHN AS HomePhone
            ,ST_STDNT_A.WRK_PHN AS WorkPhone
            ,ST_STDNT_A.FAX_NUM AS CellPhone
            ,CAST(
               CASE
                 WHEN ST_STDNT_A.FMLY_HIST_FLG = 'Y' THEN 'Yes'
                 ELSE 'No'
               END AS VARCHAR(3)) AS FirstGenerationStudent
            ,ST_STDNT_A.AWD_TY AS AwardLevelCode
            ,ISNULL(AwdLvl.DESCRIPTION,'N/A') AS AwardLevel
            ,ST_STDNT_A.PGM_CD AS ProgramCode
            ,ST_STDNT_A.CAMP_PREF AS HomeCampusPreferenceCode
            ,ISNULL(site.SITE_NAME, 'N/A') AS HomeCampus
            ,CAST(
               CASE
                 WHEN ST_STDNT_A.DISAB_CD = 'Y' THEN 'Yes'
                 ELSE 'No'
                END AS VARCHAR(3)) AS HasDisabilities
            ,CAST(CASE VET_FLG
              WHEN 1 THEN 'Yes'
              ELSE 'No'
             END AS Varchar(3)) AS VeteranInd
            ,CONVERT(DATE, ST_STDNT_A.CR_APPL_DT) AS CreditApplicationDate
            ,CONVERT(DATE, ST_STDNT_A.NC_APPL_DT) AS NonCreditApplicationDate
            ,CONVERT(DATE, ST_STDNT_A.VC_APPL_DT) AS VocationalApplicationDate
            ,CONVERT(DATE, ST_STDNT_A.HS_APPL_DT) AS HighSchoolApplicationDate
            ,ST_STDNT_A.CR_APPL_TERM AS CreditApplicationTerm
            ,ST_STDNT_A.CR_READMT_TERM AS CreditReAdmitTerm
            ,ST_STDNT_A.NC_APPL_TERM  AS NonCreditApplicationTerm
            ,ST_STDNT_A.NC_READMT_TERM  AS NonCreditReAdmitTerm
            ,ST_STDNT_A.HS_APPL_TERM AS HighSchoolApplicationTerm
            ,ST_STDNT_A.HS_READMT_TERM AS HighSchoolReAdmitTerm
            ,ST_STDNT_A.VC_APPL_TERM AS VocationalApplicationTerm
            ,ST_STDNT_A.VC_READMT_TERM AS VocationalReAdmitTerm
            ,LTerm.LastTermAttended
            ,ISNULL(PrimLang.DESCRIPTION,'N/A') AS PrimaryLanguage
            ,ISNULL(Ethnic.DESCRIPTION,'N/A') AS Ethnicity
            ,ST_STDNT_A.EMAIL_ADDR AS PersonalEmailAddress
            ,ST_STDNT_A.BA_CC AS CreditBasisOfAdmissionsCode
            ,ISNULL(BaCC.DESCRIPTION,'N/A') AS CreditBasisOfAdmissions
            ,ST_STDNT_A.BA_HS AS HighSchoolBasisOfAdmissionsCode
            ,ISNULL(BaHS.DESCRIPTION,'N/A') AS HighSchoolBasisOfAdmissions
            ,ST_STDNT_A.BA_VC AS VocationalBasisOfAdmissionsCode
            ,ISNULL(BaVC.DESCRIPTION,'N/A') AS VocationalBasisOfAdmissions
            ,ST_STDNT_A.BA_NC AS NonCreditBasisOfAdmissionsCode
            ,ISNULL(BaNC.DESCRIPTION,'N/A') AS NonCreditBasisOfAdmissions
            ,ST_STDNT_A.EMER_CNTCT AS EmergencyContact
            ,ST_STDNT_A.EMER_RLTV_CD AS EmergencyRelativeCode
            ,ISNULL(EmerRel.DESCRIPTION,'N/A') AS EmergencyRelative
            ,ST_STDNT_A.EMER_HM_PHN AS EmergencyHomePhoneNumber
            ,ST_STDNT_A.EMER_WRK_PHN AS EmergencyWorkPhoneNumber
            ,CONVERT(DATE, ST_STDNT_A.DECEASED_DT) AS DeceasedDate
            ,CAST(
               CASE
                 WHEN ST_STDNT_A.DISADV_ACAD = 'Y' THEN 'Yes'
                 ELSE 'No'
               END AS VARCHAR(3)) AS AcademicDisadvantage
            ,CAST(
               CASE
                 WHEN ST_STDNT_A.DISADV_ECON = 'Y' THEN 'Yes'
                 ELSE 'No'
               END AS VARCHAR(3)) AS EconomicDisadvantage
            ,ISNULL(BCountry.DESCRIPTION,'N/A') AS CountryOfBirth
            ,CASE
               WHEN COLL_CRED_SHRT_FLG = 1 THEN 'Yes'
               ELSE 'No'
             END AS EnteredShortCredictApplication
            ,ST_STDNT_A.HS_PGM_CD AS HighSchoolProgramCode
            ,ST_STDNT_A.VOC_PGM_CD AS VocationalProgramCode
            ,ST_STDNT_A.NC_PGM_CD AS NonCreditProgramCode
            ,ISNULL(EducL.DESCRIPTION,'N/A') AS EducationLevel
            ,ISNULL(ST_STDNT_A.ALL_GPA, 0) AS OverallCumGPA
            ,ISNULL(ST_STDNT_A.ALL_GRD_PTS, 0) AS OverallGradePoints
            ,ISNULL(ST_STDNT_A.ALL_HRS_ERN, 0) AS OverallHoursEarned
            ,ISNULL(ST_STDNT_A.ALL_HRS_ATT, 0) AS OverallHoursAttempted
            ,ISNULL(ST_STDNT_A.ALL_GPA_HRS, 0) AS OverallGpaHours
            ,CAST(
               CASE
                  WHEN ST_STDNT_A.APPL_FEE = ' ' THEN 'No Fee Required'
                  WHEN ST_STDNT_A.APPL_FEE = 'U' THEN 'Fee Required, Not Paid'
                  WHEN ST_STDNT_A.APPL_FEE = 'P' THEN 'Fee Required, Paid'
                  ELSE 'N/A'
               END AS VARCHAR(25)) AS ApplicationFeeStatus
            ,ST_STDNT_A.SUSPENSION_TERM AS SuspensionStartTerm
            ,ST_STDNT_A.ELIGIBLE_TO_RETURN AS EligibleToReturnTerm
            ,ISNULL(DecVer.DESCRIPTION,'N/A') AS DeceasedVerification
            ,CASE
               WHEN ST_STDNT_A.GED_DATE_SW = 'Y' THEN 'Yes'
               ELSE 'No'
               END AS GEDGraduated
            ,ST_STDNT_A.BANK_CODE AS BankRoutingNumber
            ,'xxxxxxxxxxxxxxxxx' + SUBSTRING(RIGHT('00000000000000000000'
               + ST_STDNT_A.BANKING_INSTITUTION_ACCOUNT_NBR,
               20), 18, 3) AS BankAccountNumber
            ,CASE
               WHEN EFT_ACCOUNT_ACTIVE_IND = 'Y' THEN 'Yes'
               WHEN EFT_ACCOUNT_ACTIVE_IND = 'N' THEN 'No'
               WHEN EFT_ACCOUNT_ACTIVE_IND = ' ' THEN 'No'
               WHEN EFT_ACCOUNT_ACTIVE_IND = 'T' THEN 'Test'
               ELSE 'N/A'
              END AS EftAccountActive
            ,CASE
               WHEN EFT_AUTHORIZED_IND = ' ' THEN 'No'
               WHEN EFT_AUTHORIZED_IND = 'N' THEN 'No'
               WHEN EFT_AUTHORIZED_IND = 'Y' THEN 'Yes'
               ELSE 'N/A'
              END AS EftAuthorizedInd
            ,CASE
              WHEN EFT_ACCOUNT_OPEN_IND = ' ' THEN 'No'
              WHEN EFT_ACCOUNT_OPEN_IND = 'N' THEN 'No'
              WHEN EFT_ACCOUNT_OPEN_IND = 'Y' THEN 'Yes'
              ELSE 'N/A'
              END AS EftAccountVerified
            ,CASE
               WHEN EFT_BANK_ACCT_TYPE = 'C' THEN 'Checking'
               WHEN EFT_BANK_ACCT_TYPE = 'S' THEN 'Savings'
               ELSE 'N/A'
              END AS EftBankAccountType
            ,CASE
               WHEN ST_STDNT_A.FA_CURRENT_TRM_NON_TUITION_AUTH = 'Y' THEN 'Yes'
               WHEN ST_STDNT_A.FA_CURRENT_TRM_NON_TUITION_AUTH = 'N' THEN 'No'
               ELSE ST_STDNT_A.FA_CURRENT_TRM_NON_TUITION_AUTH
             END AS PayCurrentTermNonTuitionWithFinancialAid
            ,CASE
               WHEN ST_STDNT_A.FA_PRIOR_FUTURE_TRM_AUTH = 'Y' THEN 'Yes'
               WHEN ST_STDNT_A.FA_PRIOR_FUTURE_TRM_AUTH = 'N' THEN 'No'
               ELSE ST_STDNT_A.FA_PRIOR_FUTURE_TRM_AUTH
             END AS PayPriorTermNonTuitionWithFinancialAid
            ,ST_STDNT_A.VISA_TYPE AS VisaTypeCode
            ,ISNULL(VType.DESCRIPTION, 'N/A') AS VisaType
            ,CONVERT(DATE, ST_STDNT_A.VISA_ISSUE_DATE) AS VisaIssueDate
            ,CONVERT(DATE, ST_STDNT_A.VISA_EXP_DT) AS VisaExperationDate
            ,CASE
               WHEN RESIDENT_YN = 'Y' THEN 'Yes'
               ELSE 'No'
              END AS InStateResident
            ,ISNULL(ResBase.DESCRIPTION,'N/A') AS BasisOfResidency
            ,ISNULL(ResRel.DESCRIPTION,'N/A') AS ResidencyRelationship
            ,ST_STDNT_A.RES_LST_NM  AS ResSupportLastName
            ,ST_STDNT_A.RES_FRST_NM  AS ResSupportFirstName
            ,ST_STDNT_A.RES_MDL_NM  AS ResSupportMiddleName
            ,ST_STDNT_A.RES_PHONE AS   ResSupportPhoneNumber
            ,ST_STDNT_A.VOTER_NBR AS VoterNumber
            ,VoterSt.DESCRIPTION AS VoterState
            ,VoterCounty.DESCRIPTION AS VoterCounty
            ,CONVERT(DATE, ST_STDNT_A.VOTER_DATE) AS VoterRegistrationDate
            ,ST_STDNT_A.LICENSE_NBR as LicenseNumber
            ,LicSt.DESCRIPTION AS LicenseState
            ,ST_STDNT_A.LICENSE_STATE
            ,CONVERT(DATE, ST_STDNT_A.LICENSE_DATE) AS LicenseDate
            ,ST_STDNT_A.VEHICLE_NBR AS VehicleRegistrationNumber
            ,VehSt.DESCRIPTION AS VehicleRegistrationState
            ,CONVERT(DATE, ST_STDNT_A.VEHICLE_DATE) AS VehicleRegistrationDate
            ,ST_STDNT_A.ALIEN_NBR AS AlienCardNumber
            ,CASE
               WHEN ISDATE(ST_STDNT_A.ALIEN_DATE) = 1 THEN CONVERT(DATE, ALIEN_DATE)
               WHEN ST_STDNT_A.ALIEN_DATE IS NULL THEN NULL
               ELSE NULL
               END AS AlienCardIssueDate
            ,ST_STDNT_A.CONFIRMATION_NBR AS FactsConfirmationNumber
            ,SecQ.DESCRIPTION AS SecurityQuestion
            ,ST_STDNT_A.SECURITY_RESPONSE AS SecurityResponse
            ,ST_STDNT_A.EMAIL_INST AS InstitutionalEmailAddress
            ,ST_STDNT_A.SITE_SPECIFIC_FIELD AS SiteSpecificField
            ,CASE
               WHEN ST_STDNT_A.FA_BOOKSTORE_AUTH = 'Y' THEN 'Yes'
               ELSE 'No'
             END AS AuthorizedFinancialAidBookStore
            ,CASE
               WHEN ST_STDNT_A.STUDENT_BILLING_SELECTION = 'P' THEN 'Receive Billing Printed and Mailed'
               WHEN ST_STDNT_A.STUDENT_BILLING_SELECTION = 'E' THEN 'Receive Billing via E-Mail'
               ELSE ' '
             END AS BillingSelection
            ,uvw_StudentRace.Asian
            ,uvw_StudentRace.BlackAfricanAmerican
            ,uvw_StudentRace.AmIndianAKNative
            ,uvw_StudentRace.HawaiianPacIslander
            ,uvw_StudentRace.White
            ,uvw_StudentRace.NotReported
            ,SpclDesc1.DESCRIPTION AS SpecialDesignator1
            ,SpclDesc2.DESCRIPTION AS SpecialDesignator2
            ,SpclDesc3.DESCRIPTION AS SpecialDesignator3
            ,SpclDesc4.DESCRIPTION AS SpecialDesignator4
            ,SpclDesc5.DESCRIPTION AS SpecialDesignator5
            ,SpclDesc6.DESCRIPTION AS SpecialDesignator6
            ,SpclDesc7.DESCRIPTION AS SpecialDesignator7
            ,SpclDesc8.DESCRIPTION AS SpecialDesignator8
            ,SpclDesc9.DESCRIPTION AS SpecialDesignator9
            ,SpclDesc10.DESCRIPTION AS SpecialDesignator10
            FROM ST_STDNT_A
            LEFT OUTER JOIN dbo.arch_StudentLastTermAttended LTerm ON ST_STDNT_A.STUDENT_ID = LTerm.Student_id
            LEFT OUTER JOIN uvw_StudentRace
            ON
            (
               uvw_StudentRace.ISN_ST_STDNT_A = ST_STDNT_A.ISN_ST_STDNT_A
            )
            LEFT OUTER JOIN UTL_CODE_TABLE ResCd
            ON
            (
               ResCd.TABLE_NAME = 'RESIDENCY'
               AND ResCd.CODE = ST_STDNT_A.RES_CD
               AND ResCd.STATUS = 'A'
            )
            LEFT OUTER JOIN UTL_CODE_TABLE ResSt
            ON
            (
               ResSt.TABLE_NAME = 'STATE'
               AND ResSt.CODE = ST_STDNT_A.RES_ST_CD
               AND ResSt.STATUS = 'A'
            )
            LEFT OUTER JOIN UTL_CODE_TABLE ResCounty
            ON
            (
               ResCounty.TABLE_NAME = 'COUNTY'
               AND ResCounty.CODE = ST_STDNT_A.RES_CNTY_CD
               AND ResCounty.STATUS = 'A'
            )
            LEFT OUTER JOIN UTL_CODE_TABLE ResCountry
            ON
            (
               ResCountry.TABLE_NAME = 'COUNTRY-ST'
               AND ResCountry.CODE = ST_STDNT_A.RES_CNTRY_CD
               AND ResCountry.STATUS = 'A'
            )
            LEFT OUTER JOIN UTL_CODE_TABLE BCountry
            ON
            (
               BCountry.TABLE_NAME = 'COUNTRY-ST'
               AND BCountry.CODE = ST_STDNT_A.CNTRY_BIRTH
               AND BCountry.STATUS = 'A'
            )
            LEFT OUTER JOIN UTL_CODE_TABLE ImmigStat
            ON
            (
               ImmigStat.TABLE_NAME = 'ST-IMMIGRA'
               AND ImmigStat.CODE = ST_STDNT_A.IMMIG_STAT
               AND ImmigStat.STATUS = 'A'
            )
            LEFT OUTER JOIN UTL_CODE_TABLE AwdLvl
            ON
            (
               AwdLvl.TABLE_NAME = 'AWARD-TYPE'
               AND AwdLvl.CODE = ST_STDNT_A.AWD_TY
               AND AwdLvl.STATUS = 'A'
            )
            LEFT OUTER JOIN UTL_CODE_TABLE PrimLang
            ON
            (
               PrimLang.TABLE_NAME = 'LANGUAGE'
               AND PrimLang.CODE = ST_STDNT_A.PRIM_LANG
               AND PrimLang.STATUS = 'A'
            )
            LEFT OUTER JOIN UTL_CODE_TABLE Ethnic
            ON
            (
               Ethnic.TABLE_NAME = 'ST-ETHNIC'
               AND Ethnic.CODE = ST_STDNT_A.ETHNICITY
               AND Ethnic.STATUS = 'A'
            )
            LEFT OUTER JOIN UTL_CODE_TABLE BaCC
            ON
            (
               BaCC.TABLE_NAME = 'ENRL-CODE'
               AND BaCC.CODE = ST_STDNT_A.BA_CC
               AND BaCC.STATUS = 'A'
            )
            LEFT OUTER JOIN UTL_CODE_TABLE BaHS
            ON
            (
               BaHS.TABLE_NAME = 'ENRL-CODE'
               AND BaHS.CODE = ST_STDNT_A.BA_HS
               AND BaHS.STATUS = 'A'
            )
            LEFT OUTER JOIN UTL_CODE_TABLE BaVC
            ON
            (
               BaVC.TABLE_NAME = 'ENRL-CODE'
               AND BaVC.CODE = ST_STDNT_A.BA_VC
               AND BaVC.STATUS = 'A'
            )
            LEFT OUTER JOIN UTL_CODE_TABLE BaNC
            ON
            (
               BaNC.TABLE_NAME = 'ENRL-CODE'
               AND BaNC.CODE = ST_STDNT_A.BA_NC
               AND BaNC.STATUS = 'A'
            )
            LEFT OUTER JOIN UTL_CODE_TABLE EmerRel
            ON
            (
               EmerRel.TABLE_NAME = 'ST-RELATVE'
               AND EmerRel.CODE = ST_STDNT_A.EMER_RLTV_CD
               AND EmerRel.STATUS = 'A'
            )
            LEFT OUTER JOIN UTL_CODE_TABLE EducL
            ON
            (
               EducL.TABLE_NAME = 'EDUC-LVL'
               AND EducL.CODE = ST_STDNT_A.EDUC_LVL
               AND EducL.STATUS = 'A'
            )
            LEFT OUTER JOIN UTL_CODE_TABLE DecVer
            ON
            (
               DecVer.TABLE_NAME = 'DECEAS-VER'
               AND DecVer.CODE = ST_STDNT_A.DECEASED_VERIF_CD
               AND DecVer.STATUS = 'A'
            )
            LEFT OUTER JOIN UTL_CODE_TABLE VType
            ON
            (
               VType.TABLE_NAME = 'VISA'
               AND VType.CODE = ST_STDNT_A.VISA_TYPE
               AND VType.STATUS = 'A'
            )
            LEFT OUTER JOIN UTL_CODE_TABLE ResBase
            ON
            (
               ResBase.TABLE_NAME = 'BASIS-RES'
               AND ResBase.CODE = ST_STDNT_A.BASIS_RESIDENCY
               AND ResBase.STATUS = 'A'
            )
            LEFT OUTER JOIN UTL_CODE_TABLE ResRel
            ON
            (
               ResRel.TABLE_NAME = 'ST-RELATVE'
               AND ResRel.CODE = ST_STDNT_A.RES_RELATIONSHIP
               AND ResRel.STATUS = 'A'
            )
            LEFT OUTER JOIN UTL_CODE_TABLE VoterSt
            ON
            (
               VoterSt.TABLE_NAME = 'STATE'
               AND VoterSt.CODE = ST_STDNT_A.VOTER_STATE
               AND VoterSt.STATUS = 'A'
            )
            LEFT OUTER JOIN UTL_CODE_TABLE VoterCounty
            ON
            (
               VoterCounty.TABLE_NAME = 'COUNTY'
               AND VoterCounty.CODE = ST_STDNT_A.VOTER_COUNTY
               AND VoterCounty.STATUS = 'A'
            )
            LEFT OUTER JOIN UTL_CODE_TABLE LicSt
            ON
            (
               LicSt.TABLE_NAME = 'STATE'
               AND LicSt.CODE = ST_STDNT_A.LICENSE_STATE
               AND LicSt.STATUS = 'A'
            )
            LEFT OUTER JOIN UTL_CODE_TABLE VehSt
            ON
            (
               VehSt.TABLE_NAME = 'STATE'
               AND VehSt.CODE = ST_STDNT_A.VEHICLE_STATE
               AND VehSt.STATUS = 'A'
            )
            LEFT OUTER JOIN UTL_CODE_TABLE SecQ
            ON
            (
               SecQ.TABLE_NAME = 'SEC-QUESTN'
               AND SecQ.CODE = ST_STDNT_A.SECURITY_QUESTION
               AND SecQ.STATUS = 'A'
            )

LEFT OUTER JOIN FAC192_SITE_A sitelocal
   ON (
       sitelocal.SITE_LOCAL_NUM = LEFT(ST_STDNT_A.CAMP_PREF,1)
       AND sitelocal.REC_TYPE = 'S'
       )
/* Get Home Campus - Site Name *//
LEFT OUTER JOIN FAC192_SITE_A site
   ON (
       site.SITE_NUM = sitelocal.SITE_NUM
       AND site.REC_TYPE = 'I'
       )
LEFT OUTER JOIN ST_STDNT_A_SPCL_DSGNTR Spcl1
ON
(
  ST_STDNT_A.ISN_ST_STDNT_A = Spcl1.ISN_ST_STDNT_A
  AND Spcl1.cnxarraycolumn = 0
)
LEFT OUTER JOIN UTL_CODE_TABLE SpclDesc1
ON
(
   SpclDesc1.TABLE_NAME = 'ST-SPEC-DE'
   AND SpclDesc1.CODE = Spcl1.SPCL_DSGNTR
   AND SpclDesc1.STATUS = 'A'
)
LEFT OUTER JOIN ST_STDNT_A_SPCL_DSGNTR Spcl2
ON
(
  ST_STDNT_A.ISN_ST_STDNT_A = Spcl2.ISN_ST_STDNT_A
  AND Spcl2.cnxarraycolumn = 1
)
LEFT OUTER JOIN UTL_CODE_TABLE SpclDesc2
ON
(
   SpclDesc2.TABLE_NAME = 'ST-SPEC-DE'
   AND SpclDesc2.CODE = Spcl2.SPCL_DSGNTR
   AND SpclDesc2.STATUS = 'A'
)
LEFT OUTER JOIN ST_STDNT_A_SPCL_DSGNTR Spcl3
ON
(
  ST_STDNT_A.ISN_ST_STDNT_A = Spcl3.ISN_ST_STDNT_A
  AND Spcl3.cnxarraycolumn = 2
)
LEFT OUTER JOIN UTL_CODE_TABLE SpclDesc3
ON
(
   SpclDesc3.TABLE_NAME = 'ST-SPEC-DE'
   AND SpclDesc3.CODE = Spcl3.SPCL_DSGNTR
   AND SpclDesc3.STATUS = 'A'
)
LEFT OUTER JOIN ST_STDNT_A_SPCL_DSGNTR Spcl4
ON
(
  ST_STDNT_A.ISN_ST_STDNT_A = Spcl4.ISN_ST_STDNT_A
  AND Spcl4.cnxarraycolumn = 3
)
LEFT OUTER JOIN UTL_CODE_TABLE SpclDesc4
ON
(
   SpclDesc4.TABLE_NAME = 'ST-SPEC-DE'
   AND SpclDesc4.CODE = Spcl4.SPCL_DSGNTR
   AND SpclDesc4.STATUS = 'A'
)
LEFT OUTER JOIN ST_STDNT_A_SPCL_DSGNTR Spcl5
ON
(
  ST_STDNT_A.ISN_ST_STDNT_A = Spcl5.ISN_ST_STDNT_A
  AND Spcl5.cnxarraycolumn = 4
)
LEFT OUTER JOIN UTL_CODE_TABLE SpclDesc5
ON
(
   SpclDesc5.TABLE_NAME = 'ST-SPEC-DE'
   AND SpclDesc5.CODE = Spcl5.SPCL_DSGNTR
   AND SpclDesc5.STATUS = 'A'
)
LEFT OUTER JOIN ST_STDNT_A_SPCL_DSGNTR Spcl6
ON
(
  ST_STDNT_A.ISN_ST_STDNT_A = Spcl6.ISN_ST_STDNT_A
  AND Spcl6.cnxarraycolumn = 5
)
LEFT OUTER JOIN UTL_CODE_TABLE SpclDesc6
ON
(
   SpclDesc6.TABLE_NAME = 'ST-SPEC-DE'
   AND SpclDesc6.CODE = Spcl6.SPCL_DSGNTR
   AND SpclDesc6.STATUS = 'A'
)
LEFT OUTER JOIN ST_STDNT_A_SPCL_DSGNTR Spcl7
ON
(
  ST_STDNT_A.ISN_ST_STDNT_A = Spcl7.ISN_ST_STDNT_A
  AND Spcl7.cnxarraycolumn = 6
)
LEFT OUTER JOIN UTL_CODE_TABLE SpclDesc7
ON
(
   SpclDesc7.TABLE_NAME = 'ST-SPEC-DE'
   AND SpclDesc7.CODE = Spcl7.SPCL_DSGNTR
   AND SpclDesc7.STATUS = 'A'
)
LEFT OUTER JOIN ST_STDNT_A_SPCL_DSGNTR Spcl8
ON
(
  ST_STDNT_A.ISN_ST_STDNT_A = Spcl8.ISN_ST_STDNT_A
  AND Spcl8.cnxarraycolumn = 7
)
LEFT OUTER JOIN UTL_CODE_TABLE SpclDesc8
ON
(
   SpclDesc8.TABLE_NAME = 'ST-SPEC-DE'
   AND SpclDesc8.CODE = Spcl8.SPCL_DSGNTR
   AND SpclDesc8.STATUS = 'A'
)
LEFT OUTER JOIN ST_STDNT_A_SPCL_DSGNTR Spcl9
ON
(
  ST_STDNT_A.ISN_ST_STDNT_A = Spcl9.ISN_ST_STDNT_A
  AND Spcl9.cnxarraycolumn = 8
)
LEFT OUTER JOIN UTL_CODE_TABLE SpclDesc9
ON
(
   SpclDesc9.TABLE_NAME = 'ST-SPEC-DE'
   AND SpclDesc9.CODE = Spcl9.SPCL_DSGNTR
   AND SpclDesc9.STATUS = 'A'
)
LEFT OUTER JOIN ST_STDNT_A_SPCL_DSGNTR Spcl10
ON
(
  ST_STDNT_A.ISN_ST_STDNT_A = Spcl10.ISN_ST_STDNT_A
  AND Spcl10.cnxarraycolumn = 9
)
LEFT OUTER JOIN UTL_CODE_TABLE SpclDesc10
ON
(
   SpclDesc10.TABLE_NAME = 'ST-SPEC-DE'
   AND SpclDesc10.CODE = Spcl10.SPCL_DSGNTR
   AND SpclDesc10.STATUS = 'A'
)

    WHERE   ST_STDNT_A.STUDENT_ID IS NOT NULL AND ST_STDNT_A.STUDENT_SSN IS NOT NULL