SFA_PF_COM_VW

(SQL View)
 Index Back

Student Aid Staging Tbl View

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

SELECT B.OPRCLASS ,A.SFA_PF_QUEUE_INST ,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 (%Substring(A.SSN ,6 ,4)) ELSE A.SSN END ,A.NAME ,A.NAME_PREFIX ,A.EMPLID ,A.LAST_NAME_SRCH ,A.FIRST_NAME_SRCH ,A.SEX ,A.MAR_STATUS ,A.MAR_STATUS_DT ,A.SFA_CR_BIRTH_DT ,CASE WHEN B.MASK_CFG_DOB='1' THEN '**/**/****' WHEN B.MASK_CFG_DOB='2' THEN TO_CHAR(A.SFA_CR_BIRTH_DT , 'MM/DD') ELSE TO_CHAR(A.SFA_CR_BIRTH_DT , '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_SFA_PF_COMM_ST 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 SFA_PF_QUEUE_INST Number(9,0) DECIMAL(9) NOT NULL Load Instance
    3 CSS_ID Character(7) VARCHAR2(7) NOT NULL CSS ID Number
    4 INSTITUTION Character(5) VARCHAR2(5) NOT NULL Academic Institution

    Prompt Table: INSTITUTION_TBL

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

    Prompt Table: STUDENT_AID

    6 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
    7 SSN Character(9) VARCHAR2(9) NOT NULL Social Security #
    8 SSN_MSK Character(9) VARCHAR2(9) NOT NULL Social Security #
    9 NAME Character(50) VARCHAR2(50) NOT NULL Name
    10 NAME_PREFIX Character(4) VARCHAR2(4) NOT NULL Name Prefix
    11 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID

    Prompt Table: PEOPLE_SRCH

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

    Prompt Table: HCR_NM_PREFIX_I

    13 FIRST_NAME_SRCH Character(30) VARCHAR2(30) NOT NULL First Name
    14 SEX Character(1) VARCHAR2(1) NOT NULL Gender
    F=Female
    M=Male
    U=Unknown
    X=Indeterminate/Intersex/Unspec
    15 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
    16 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. */
    17 SFA_CR_BIRTH_DT Character(10) VARCHAR2(10) NOT NULL Birthdate
    18 BIRTHDATE_MSK Character(10) VARCHAR2(10) NOT NULL Date of Birth
    19 CITIZENSHIP_STATUS Character(1) VARCHAR2(1) NOT NULL Citizenship Status

    Prompt Table: HCR_CTZN_STS_I

    20 VISA_WRKPMT_NBR Character(15) VARCHAR2(15) NOT NULL Visa/Permit Number
    21 DRIVERS_LIC_NBR Character(20) VARCHAR2(20) NOT NULL Driver's License #
    22 DRIV_LIC_STATE Character(6) VARCHAR2(6) NOT NULL Issuing State
    23 MIDDLE_NAME Character(30) VARCHAR2(30) NOT NULL MIDDLE_NAME
    24 PHONE Character(24) VARCHAR2(24) NOT NULL Telephone