SUSP_PRF_COM_VW

(SQL View)
 Index Back

Base Student Aid Suspense Tbl

This is an exact view of SUSP_PROF_COMM table. The view adds masking for SSN and BIRTHDATE. This record houses data in the institutional application data.

SELECT B.OPRCLASS ,A.CSS_ID ,A.INSTITUTION ,A.AID_YEAR ,A.APP_SOURCE_CODE ,A.SSN ,CASE WHEN B.MASK_CFG_NID='1' THEN '*********' WHEN B.MASK_CFG_NID='2' THEN CONCAT('*****' , SUBSTR(A.SSN , 6 ,4)) ELSE A.SSN END ,A.NAME ,A.NAME_PREFIX ,A.EMPLID ,A.LAST_NAME_SRCH ,A.FIRST_NAME_SRCH ,A.COUNTRY ,A.ADDRESS1 ,A.ADDRESS2 ,A.ADDRESS3 ,A.ADDRESS4 ,A.CITY ,A.NUM1 ,A.NUM2 ,A.HOUSE_TYPE ,A.ADDR_FIELD1 ,A.ADDR_FIELD2 ,A.ADDR_FIELD3 ,A.COUNTY ,A.STATE ,A.POSTAL ,A.GEO_CODE ,A.IN_CITY_LIMIT ,A.SEX ,A.MAR_STATUS ,A.MAR_STATUS_DT ,A.BIRTHDATE ,CASE WHEN B.MASK_CFG_DOB='1' THEN '**/**/****' WHEN B.MASK_CFG_DOB='2' THEN TO_CHAR(A.BIRTHDATE , 'MM/DD') ELSE TO_CHAR(A.BIRTHDATE , 'MM/DD/YYYY') END ,A.CITIZENSHIP_STATUS ,A.VISA_WRKPMT_NBR ,A.DRIVERS_LIC_NBR ,A.DRIV_LIC_STATE ,A.MIDDLE_NAME ,A.PHONE FROM PS_SUSP_PROF_COMM A , PS_PERS_MSK_CFG B

  • Parent record: STUDENT_AID

    • # PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
    1 OPRCLASS Character(30) VARCHAR2(30) NOT NULL Operator Class
    2 CSS_ID Character(7) VARCHAR2(7) NOT NULL CSS ID Number
    3 INSTITUTION Character(5) VARCHAR2(5) NOT NULL Academic Institution

    Prompt Table: INSTITUTION_TBL

    4 AID_YEAR Character(4) VARCHAR2(4) NOT NULL Aid Year

    Prompt Table: STUDENT_AID

    5 APP_SOURCE_CODE Character(1) VARCHAR2(1) NOT NULL Application Source Code
    0=Need Access
    0=Need Access
    1=Electronic Initial Application
    2=Electronic Renewal Aplication
    3=Electronic Correction
    4=FAFSA Express
    5=ACT
    6=I-NET
    7=NCS
    8=Profile
    9=Institutional Application
    O=FT Canada Student Loan
    P=PT Canada Student Loan
    6 SSN Character(9) VARCHAR2(9) NOT NULL Social Security #
    7 SSN_MSK Character(9) VARCHAR2(9) NOT NULL Social Security #
    8 NAME Character(50) VARCHAR2(50) NOT NULL Name
    9 NAME_PREFIX Character(4) VARCHAR2(4) NOT NULL Name Prefix
    10 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID

    Prompt Table: PEOPLE_SRCH

    11 LAST_NAME_SRCH Character(30) VARCHAR2(30) NOT NULL Last Name

    Prompt Table: HCR_NM_PREFIX_I

    12 FIRST_NAME_SRCH Character(30) VARCHAR2(30) NOT NULL First Name
    13 COUNTRY Character(3) VARCHAR2(3) NOT NULL Country

    Prompt Table: SCC_CNT_ADFMTVW

    14 ADDRESS1 Character(55) VARCHAR2(55) NOT NULL Address 1
    15 ADDRESS2 Character(55) VARCHAR2(55) NOT NULL Address 2
    16 ADDRESS3 Character(55) VARCHAR2(55) NOT NULL Address 3
    17 ADDRESS4 Character(55) VARCHAR2(55) NOT NULL Address 4
    18 CITY Character(30) VARCHAR2(30) NOT NULL City
    19 NUM1 Character(6) VARCHAR2(6) NOT NULL Number 1
    20 NUM2 Character(6) VARCHAR2(6) NOT NULL Number 2
    21 HOUSE_TYPE Character(2) VARCHAR2(2) NOT NULL House Type
    AB=House Boat Reference
    WW=House Trailer Reference
    22 ADDR_FIELD1 Character(2) VARCHAR2(2) NOT NULL Address Field 1
    23 ADDR_FIELD2 Character(4) VARCHAR2(4) NOT NULL Address Field 2
    24 ADDR_FIELD3 Character(4) VARCHAR2(4) NOT NULL Address Field 3
    25 COUNTY Character(30) VARCHAR2(30) NOT NULL County
    26 STATE Character(6) VARCHAR2(6) NOT NULL State

    Prompt Table: STATE_TBL

    27 POSTAL Character(12) VARCHAR2(12) NOT NULL Postal
    28 GEO_CODE Character(11) VARCHAR2(11) NOT NULL Geo Code
    29 IN_CITY_LIMIT Character(1) VARCHAR2(1) NOT NULL In City Limit

    Y/N Table Edit

    30 SEX Character(1) VARCHAR2(1) NOT NULL Gender
    F=Female
    M=Male
    U=Unknown
    X=Indeterminate/Intersex/Unspec
    31 MAR_STATUS Character(1) VARCHAR2(1) NOT NULL Marital Status
    C=Common-Law
    D=Divorced
    E=Separated
    H=Head of Household
    L=DissDeclLost Civil Partner
    M=Married
    P=Civil Partnership
    S=Single
    T=Surviving Civil Partner
    U=Unknown
    V=Dissolved Civil Partnership
    W=Widowed
    32 MAR_STATUS_DT Date(10) DATE This is the date that the employee's Marital status is/was effective. /* Marital Status date is stored redundantly on PERS_DATA_EFFDT for two reasons. 1. To allow the date to be entered prior to the first PERS_DATA_EFFDT so that the actual date for the marital status on the hire row can be entered. This is the only time that the field is enterable by the user. 2. To allow for easier reporting on the data via SQR and Crystal. This is denormalizing the PERS_DATA_EFFDT record, but the number of requests for this redundancy coupled with the need for #1, has provided the justification for denormalizing this information and taking on the added maintenance. */
    33 BIRTHDATE Date(10) DATE Date of Birth
    34 BIRTHDATE_MSK Character(10) VARCHAR2(10) NOT NULL Date of Birth
    35 CITIZENSHIP_STATUS Character(1) VARCHAR2(1) NOT NULL Citizenship Status

    Prompt Table: HCR_CTZN_STS_I

    36 VISA_WRKPMT_NBR Character(15) VARCHAR2(15) NOT NULL Visa/Permit Number
    37 DRIVERS_LIC_NBR Character(20) VARCHAR2(20) NOT NULL Driver's License #
    38 DRIV_LIC_STATE Character(6) VARCHAR2(6) NOT NULL Issuing State
    39 MIDDLE_NAME Character(30) VARCHAR2(30) NOT NULL MIDDLE_NAME
    40 PHONE Character(24) VARCHAR2(24) NOT NULL Telephone