NSLDS_AGGR_EC_V

(SQL View)
 Index Back

NSLDS FAT Aggregate Stg Tbl

This is an exact view of NSLDS_AGGR_EC table. The view adds masking for SSN. NSLDS FAT Aggregate Loan data.

SELECT B.OPRCLASS ,A.ECTRANSID ,A.ECQUEUEINSTANCE ,A.ECTRANSINOUTSW ,A.NSLDS_HEADER_NBR ,A.NSLDS_REC_TYPE ,A.NSLDS_DTL_NBR ,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.LN_STG_PROC_STAT ,A.PROCESS_INSTANCE ,A.LN_STG_ERR_CODE ,A.EMPLID ,A.NSLDS_REVIEW_STAT ,A.NSLDS_FIRST_NAME ,A.NSLDS_LAST_NAME ,A.NSLDS_DOB ,CASE WHEN B.MASK_CFG_DOB='1' THEN '**/**/****' WHEN B.MASK_CFG_DOB='2' THEN TO_CHAR(A.NSLDS_DOB , 'MM/DD') ELSE TO_CHAR(A.NSLDS_DOB , 'MM/DD/YYYY') END ,A.CPS_SCHOOL_CODE ,A.INSTIT_OE_BR ,A.NSLDS_FIRST_NM_INF ,A.NSLDS_LAST_NM_INF ,A.NSLDS_DOB_INFORM_O ,A.NSLDS_UPDT_SOURCE ,A.NSLDS_DFLT_LOAN ,A.NSLDS_DSCHRGD_LOAN ,A.NSLDS_SATIS_RPYMNT ,A.NSLDS_ACTV_BNKRPTC ,A.NSLDS_AGGR_SUB_O ,A.NSLDS_AGGR_UNSUB_O ,A.NSLDS_AGGR_CMBO_CH ,A.NSLDS_AGGR_CNSL_O ,A.PEND_AGGR_SUB_O ,A.PEND_AGGR_UNSUB_O ,A.NSLDS_AGGR_PCOMBCH ,A.AGGR_SUB_TOT_O ,A.AGGR_UNSUB_TOT_O ,A.NSLDS_AGGR_COMB_CH ,A.AGGR_CONSL_TOT_O ,A.NSLDS_AGGR_PERK_O ,A.NSLDS_YTD_PERK_O ,A.IWD_DL_MPN_FLAG ,A.NSLDS_PELL_CHNG ,A.NSLDS_LOAN_CHNG ,A.NSLDS_AGGR_CHNG FROM PS_NSLDS_AGGR_EC A , PS_PERS_MSK_CFG B

  • Parent record: NSLDS_HDR_IN_EC

    • # PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
    1 OPRCLASS Character(30) VARCHAR2(30) NOT NULL Operator Class
    2 ECTRANSID Character(15) VARCHAR2(15) NOT NULL Electronic Commerce Transaction ID. Used in EDI Manager. One of three(3) Key Fields(ECTRANSID, ECTRANSINOUTSW, ECQUEUEINSTANCE) used in Electronic Commerce(EC) Staging Tables. ECTRANSID in EDI Manager identifies a transaction type(like "PO" for Purchase Orders)
    3 ECQUEUEINSTANCE Number(9,0) DECIMAL(9) NOT NULL Electronic Commerce Queue Instance ID. Used in EDI Manager. One of three(3) Key Fields(ECTRANSID, ECTRANSINOUTSW, ECQUEUEINSTANCE) used in Electronic Commerce(EC) Staging Tables.
    4 ECTRANSINOUTSW Character(1) VARCHAR2(1) NOT NULL Electronic Commerce Inbound/Outbound Switch. Used in EDI Manager. One of three(3) Key Fields(ECTRANSID, ECTRANSINOUTSW, ECQUEUEINSTANCE) used in Electronic Commerce(EC) Staging Tables. The field value is "I" for Inbound Transactions, and "O" for Outbound.
    I=Inbound
    O=Outbound

    Default Value: I

    5 NSLDS_HEADER_NBR Number(3,0) SMALLINT NOT NULL NSLDS Header Nbr
    6 NSLDS_REC_TYPE Character(1) VARCHAR2(1) NOT NULL NSLDS Record Type Ind
    7 NSLDS_DTL_NBR Number(6,0) INTEGER NOT NULL NSLDS Detail Nbr
    8 SSN Character(9) VARCHAR2(9) NOT NULL Social Security #
    9 SSN_MSK Character(9) VARCHAR2(9) NOT NULL Social Security #
    10 LN_STG_PROC_STAT Character(1) VARCHAR2(1) NOT NULL Loan Staging Record Proc Stat
    E=Error
    K=Skipped
    L=Loaded
    N=Not Processed
    S=Skip
    U=Unprocessed
    X=Suspended
    Y=Processed

    Default Value: N

    11 PROCESS_INSTANCE Number(10,0) DECIMAL(10) NOT NULL Process Instance
    12 LN_STG_ERR_CODE Character(1) VARCHAR2(1) NOT NULL Load Staging Error Code
    C=Invalid SSN Encountered
    D=EFFDT conflict
    M=Multiple matches
    S=Student not found
    13 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID
    14 NSLDS_REVIEW_STAT Character(1) VARCHAR2(1) NOT NULL Alert Record Review Status

    Y/N Table Edit

    Default Value: N

    15 NSLDS_FIRST_NAME Character(12) VARCHAR2(12) NOT NULL First Name
    16 NSLDS_LAST_NAME Character(35) VARCHAR2(35) NOT NULL Last Name
    17 NSLDS_DOB Date(10) DATE NSLDS Date of Birth
    18 NSLDS_DOB_MSK Character(10) VARCHAR2(10) NOT NULL NSLDS Date of Birth
    19 CPS_SCHOOL_CODE Character(6) VARCHAR2(6) NOT NULL CPS School Code. Also known as Title IV School Code
    20 INSTIT_OE_BR Character(2) VARCHAR2(2) NOT NULL Institution OE Branch
    21 NSLDS_FIRST_NM_INF Character(12) VARCHAR2(12) NOT NULL The student's first name that was submitted to NSLDS either through the Inform file or through the NSLDS web page. This name may not match the name in the school database.
    22 NSLDS_LAST_NM_INF Character(35) VARCHAR2(35) NOT NULL The student's last name that was submitted to NSLDS either through the Inform file or through the NSLDS web page. This name may not match the name in the school database.
    23 NSLDS_DOB_INFORM_O Character(8) VARCHAR2(8) NOT NULL The student's date of birth that was submitted to NSLDS either through the Inform file or through the NSLDS web page. This DOB may not match the DOB in the school database.
    24 NSLDS_UPDT_SOURCE Character(1) VARCHAR2(1) NOT NULL NSLDS Transaction Source
    A=NSLDS Alert File
    F=Financial Aid Transcript
    H=NSLDS FA History File
    I=ISIR
    M=Manual

    Default Value: A

    25 NSLDS_DFLT_LOAN Character(1) VARCHAR2(1) NOT NULL NSLDS Default Reported

    Y/N Table Edit

    26 NSLDS_DSCHRGD_LOAN Character(1) VARCHAR2(1) NOT NULL NSLDS Discharged Rptd
    C=Conditional
    D=Death
    M=Multiple
    N=None
    P=Permanent
    R=Reaffirmed
    Y=Yes

    Y/N Table Edit

    27 NSLDS_SATIS_RPYMNT Character(1) VARCHAR2(1) NOT NULL NSLDS Satisfctry Rpymnt

    Y/N Table Edit

    28 NSLDS_ACTV_BNKRPTC Character(1) VARCHAR2(1) NOT NULL NSLDS Active Bankruptcy

    Y/N Table Edit

    29 NSLDS_AGGR_SUB_O Character(6) VARCHAR2(6) NOT NULL NSLDS Aggregate Subsidized
    30 NSLDS_AGGR_UNSUB_O Character(6) VARCHAR2(6) NOT NULL NSLDS Aggregate Unsubsidized
    31 NSLDS_AGGR_CMBO_CH Character(6) VARCHAR2(6) NOT NULL Aggr Combined Out Principal
    32 NSLDS_AGGR_CNSL_O Character(6) VARCHAR2(6) NOT NULL NSLDS Aggregate Consolidated
    33 PEND_AGGR_SUB_O Character(6) VARCHAR2(6) NOT NULL NSLDS Pending Aggr Sub
    34 PEND_AGGR_UNSUB_O Character(6) VARCHAR2(6) NOT NULL NSLDS Pending Aggr Unsub
    35 NSLDS_AGGR_PCOMBCH Character(6) VARCHAR2(6) NOT NULL Aggr Combined Pending Disbt
    36 AGGR_SUB_TOT_O Character(6) VARCHAR2(6) NOT NULL NSLDS Tot Aggr Sub
    37 AGGR_UNSUB_TOT_O Character(6) VARCHAR2(6) NOT NULL NSLDS Tot Aggr Unsub
    38 NSLDS_AGGR_COMB_CH Character(6) VARCHAR2(6) NOT NULL Aggregate Combined Total
    39 AGGR_CONSL_TOT_O Character(6) VARCHAR2(6) NOT NULL Aggregate Consolidated Total
    40 NSLDS_AGGR_PERK_O Character(6) VARCHAR2(6) NOT NULL NSLDS Aggregate Perkins
    41 NSLDS_YTD_PERK_O Character(6) VARCHAR2(6) NOT NULL NSLDS Year-to-Date Perkins
    42 IWD_DL_MPN_FLAG Character(1) VARCHAR2(1) NOT NULL DL Master-Prom Note Flag
    43 NSLDS_PELL_CHNG Character(1) VARCHAR2(1) NOT NULL NSLDS Pell change flag indicates that a changed Pell Grant record is contained in the NSLDS alert file.
    44 NSLDS_LOAN_CHNG Character(1) VARCHAR2(1) NOT NULL NSLDS Loan change flag indicates that a changed loan record is contained in the NSLDS alert file.
    45 NSLDS_AGGR_CHNG Character(1) VARCHAR2(1) NOT NULL NSLDS Aggregate change flag indicates that new aggregate information is contained in the NSLDS alert file.