SSR_BR_ENRDL_VW

(SQL View)
 Index Back

BRON HO Enrollment Current VW

BRON HO Delivery of Enrollment Current View

SELECT DISTINCT AP.INSTITUTION , SP1.EMPLID ,SP1.SSR_BRHO_ENRSQ_NLD , %Substring(%Coalesce((%Sql(SSR_BR_NAT_ID_NLD,AP.EMPLID,'PR')) ,' ' ),1,9) , %Substring(%Coalesce((%Sql(SSR_BR_NAT_ID_NLD,AP.EMPLID,'OND')),' ') ,1,9) ,SP2.SCC_BRINCD_NLD ,SP2.SCC_SUBBRINCD_NLD ,%Coalesce((SELECT FOSX.SSR_BR_FORMSTD_NLD FROM PS_SSR_BR_FMST_NLD FOSX WHERE FOSX.SSR_FORM_STUDY_NLD = SP2.SSR_FORM_STUDY_NLD),' ') ,SP2.SSR_SL_PROG_CD_NLD ,%Coalesce((SELECT ACLDX.SSR_BR_AC_LDAP_NLD FROM PS_SSR_BR_ACLD_NLD ACLDX WHERE ACLDX.ACAD_LOAD_APPR = AP.ACAD_LOAD_APPR),' ') ,%Coalesce((SELECT ACLVLX.SSR_BR_LEVEL_NLD FROM PS_SSR_BR_ALVL_NLD ACLVLX WHERE ACLVLX.SSR_ACAD_LEVEL_NLD = SP1.SSR_ACAD_LEVEL_NLD),' ') , SP1.EFFDT , %Coalesce((SELECT (AP3.EFFDT) FROM PS_ACAD_PROG AP3 WHERE AP3.EMPLID = AP.EMPLID AND AP3.ACAD_CAREER = AP.ACAD_CAREER AND AP3.STDNT_CAR_NBR = AP.STDNT_CAR_NBR AND %EffdtCheck(ACAD_PROG AP4, AP3, (SELECT T.TERM_END_DT FROM ps_term_tbl T WHERE T.institution = AP.INSTITUTION AND T.TERM_CATEGORY = (SELECT TERM_CATEGORY FROM PS_SSR_BR_PRCO_NLD) AND T.acad_career = AP.ACAD_CAREER AND T.ACAD_YEAR = SP2.ACAD_YEAR)) AND AP3.EFFSEQ = (select max(AP3S.EFFSEQ) from PS_ACAD_PROG AP3S where AP3.EMPLID = AP3S.EMPLID and AP3.ACAD_CAREER = AP3S.ACAD_CAREER and AP3.STDNT_CAR_NBR = AP3S.STDNT_CAR_NBR AND AP3.EFFDT = AP3S.EFFDT) AND AP3.PROG_ACTION IN (SELECT ENRLEND.PROG_ACTION FROM PS_SSR_BR_EEND_NLD ENRLEND) AND AP3.PROG_REASON IN (SELECT ENRLEND_1.PROG_REASON FROM PS_SSR_BR_EEND_NLD ENRLEND_1 WHERE ENRLEND_1.PROG_ACTION = AP3.PROG_ACTION)) , (SELECT T.TERM_END_DT FROM ps_term_tbl T WHERE T.institution = AP.INSTITUTION AND T.TERM_CATEGORY = (SELECT TERM_CATEGORY FROM PS_SSR_BR_PRCO_NLD) AND T.acad_career = AP.ACAD_CAREER AND T.ACAD_YEAR = SP2.ACAD_YEAR) ) , ( SELECT ENDRS.SSR_BR_END_RSN_NLD FROM PS_SSR_BR_EEND_NLD ENDRS WHERE ENDRS.PROG_ACTION = AP.PROG_ACTION AND ENDRS.PROG_REASON = AP.PROG_REASON) ,%Coalesce((SELECT FNDX.SSR_BR_FNDCD_X_NLD FROM PS_SSR_BR_XFND_NLD FNDX WHERE FNDX.SSR_BR_FUND_CD_NLD = SP1.SSR_FUNDING_CD_NLD),' ') , SP1.SSR_BR_INTPRG_NLD ,AP.ACAD_CAREER ,AP.STDNT_CAR_NBR FROM PS_SSR_STD_PRG_NLD SP , PS_SSR_STD_PRG_NLD SP1 , PS_SSR_STD_PRG_NLD SP2 , PS_ACAD_PROG AP ,PS_SSR_BR_INST_NLD INST WHERE SP.ssr_prg_action_nld = ( SELECT ssr_prg_action_nld FROM ps_ssr_br_ecnf_nld) AND AP.EMPLID = SP.EMPLID AND AP.ACAD_CAREER = SP.ACAD_CAREER AND AP.STDNT_CAR_NBR = SP.STDNT_CAR_NBR AND SP.SSR_BRHO_ENRSQ_NLD = sp1.SSR_BRHO_ENRSQ_NLD AND SP.SSR_BRHO_ENRSQ_NLD = SP2.SSR_BRHO_ENRSQ_NLD AND SP1.EMPLID = SP.EMPLID AND SP1.ACAD_CAREER = SP.ACAD_CAREER AND SP1.STDNT_CAR_NBR = SP.STDNT_CAR_NBR AND SP1.effdt = ( SELECT MAX(SP1_1.EFFDT) FROM PS_SSR_STD_PRG_NLD SP1_1 WHERE SP1.emplid = SP1_1.EMPLID AND SP1.SSR_BRHO_ENRSQ_NLD = SP1_1.SSR_BRHO_ENRSQ_NLD AND (SP1_1.ssr_prg_action_nld IN ( SELECT ET.ssr_prg_action_nld FROM ps_SSR_BR_ETRG_NLD ET) OR SP1_1.ssr_prg_action_nld = 'MATR')) AND SP1.EFFSEQ = ( SELECT MAX(SP1_2.EFFSEQ) FROM PS_SSR_STD_PRG_NLD SP1_2 WHERE SP1.emplid = SP1_2.EMPLID AND SP1.SSR_BRHO_ENRSQ_NLD = SP1_2.SSR_BRHO_ENRSQ_NLD AND (SP1_2.SSR_PRG_ACTION_NLD IN ( SELECT ET2.SSR_PRG_ACTION_NLD FROM PS_SSR_BR_ETRG_NLD ET2) OR SP1_2.ssr_prg_action_nld = 'MATR') AND SP1_2.EFFDT = SP1.effdt) AND SP1.EMPLID = SP2.EMPLID AND SP1.ACAD_CAREER = SP2.ACAD_CAREER AND SP1.STDNT_CAR_NBR = SP2.STDNT_CAR_NBR AND SP2.effdt = ( SELECT MAX(SP2_1.EFFDT) FROM PS_SSR_STD_PRG_NLD SP2_1 WHERE SP1.emplid = SP2_1.EMPLID AND SP1.SSR_BRHO_ENRSQ_NLD = SP2_1.SSR_BRHO_ENRSQ_NLD ) AND SP2.EFFSEQ = ( SELECT MAX(SP2_2.EFFSEQ) FROM PS_SSR_STD_PRG_NLD SP2_2 WHERE SP1.emplid = SP2_2.EMPLID AND SP1.SSR_BRHO_ENRSQ_NLD = SP2_2.SSR_BRHO_ENRSQ_NLD AND SP2_2.EFFDT = SP2.effdt) AND AP.EFFDT = ( SELECT MAX(AP1.EFFDT) FROM PS_ACAD_PROG AP1 WHERE AP1.EMPLID = AP.EMPLID AND AP1.ACAD_CAREER = AP.ACAD_CAREER AND AP1.STDNT_CAR_NBR = AP.STDNT_CAR_NBR AND AP1.EFFDT <= ( SELECT T.TERM_END_DT FROM PS_TERM_TBL T WHERE T.INSTITUTION = AP.INSTITUTION AND T.TERM_CATEGORY = ( SELECT TERM_CATEGORY FROM PS_SSR_BR_PRCO_NLD) AND T.ACAD_CAREER = AP.ACAD_CAREER AND T.ACAD_YEAR = SP2.ACAD_YEAR) ) AND AP.EFFSEQ = ( SELECT MAX(AP2.EFFSEQ) FROM PS_ACAD_PROG AP2 WHERE AP.EMPLID = AP2.EMPLID AND AP.ACAD_CAREER = AP2.ACAD_CAREER AND AP.STDNT_CAR_NBR = AP2.STDNT_CAR_NBR AND AP2.EFFDT = AP.EFFDT) AND INST.INSTITUTION = AP.INSTITUTION AND ((AP.ACAD_CAREER IN ( SELECT SETUP.ACAD_CAREER FROM PS_SSR_BR_ACAR_NLD SETUP WHERE SETUP.INSTITUTION = AP.INSTITUTION AND INST.SSR_BR_EXIN_CR_NLD = 'I' )) OR (AP.ACAD_CAREER IN ( SELECT PRMPT.ACAD_CAREER FROM PS_ACAD_CAR_TBL PRMPT WHERE INST.SSR_BR_EXIN_CR_NLD = 'E' AND PRMPT.INSTITUTION = AP.INSTITUTION AND %EffdtCheck(ACAD_CAR_TBL B2,PRMPT,%CurrentDateIn) AND PRMPT.ACAD_CAREER NOT IN ( SELECT SETUP2.ACAD_CAREER FROM PS_SSR_BR_ACAR_NLD SETUP2 WHERE SETUP2.INSTITUTION = AP.INSTITUTION) )) ) AND ((AP.ACAD_PROG IN ( SELECT SETUP.ACAD_PROG FROM PS_SSR_BR_PROG_NLD SETUP WHERE SETUP.INSTITUTION = AP.INSTITUTION AND INST.SSR_BR_EXIN_PR_NLD = 'I' ) ) OR (AP.ACAD_PROG IN ( SELECT PRMPT.ACAD_PROG FROM PS_ACAD_PROG_TBL PRMPT WHERE INST.SSR_BR_EXIN_PR_NLD = 'E' AND PRMPT.INSTITUTION = AP.INSTITUTION AND %EffdtCheck(ACAD_PROG_TBL B2,PRMPT,%CurrentDateIn) AND PRMPT.ACAD_PROG NOT IN ( SELECT SETUP2.ACAD_PROG FROM PS_SSR_BR_PROG_NLD SETUP2 WHERE SETUP2.INSTITUTION = AP.INSTITUTION) )) ) AND ((SP1.SSR_ACAD_LEVEL_NLD IN ( SELECT SETUP.SSR_ACAD_LEVEL_NLD FROM PS_SSR_BR_LVLS_NLD SETUP WHERE SETUP.INSTITUTION = AP.INSTITUTION AND INST.SSR_BR_EXIN_LV_NLD = 'I' )) OR (SP1.SSR_ACAD_LEVEL_NLD IN ( SELECT FIELDVALUE FROM psxlatitem WHERE INST.SSR_BR_EXIN_LV_NLD = 'E' AND fieldname = 'SSR_ACAD_LEVEL_NLD' AND fieldvalue NOT IN ( SELECT SETUP2.SSR_ACAD_LEVEL_NLD FROM PS_SSR_BR_LVLS_NLD SETUP2 WHERE SETUP2.INSTITUTION = AP.INSTITUTION )) ) ) AND ((SP2.SSR_FORM_STUDY_NLD IN ( SELECT SETUP.SSR_FORM_STUDY_NLD FROM PS_SSR_BR_FOS_NLD SETUP WHERE SETUP.INSTITUTION = AP.INSTITUTION AND INST.SSR_BR_EXIN_FS_NLD = 'I' ) ) OR (SP2.SSR_FORM_STUDY_NLD IN ( SELECT FIELDVALUE FROM psxlatitem WHERE INST.SSR_BR_EXIN_FS_NLD = 'E' AND fieldname = 'SSR_FORM_STUDY_NLD' AND fieldvalue NOT IN ( SELECT SETUP2.SSR_FORM_STUDY_NLD FROM PS_SSR_BR_FOS_NLD SETUP2 WHERE SETUP2.INSTITUTION = AP.INSTITUTION )) ) ) AND EXISTS ( SELECT 'X' FROM PS_ACAD_PLAN PLANX , PS_SSR_STD_PRG_NLD C WHERE AP.EMPLID =PLANX.EMPLID AND AP.ACAD_CAREER = PLANX.ACAD_CAREER AND AP.STDNT_CAR_NBR = PLANX.STDNT_CAR_NBR AND AP.EFFDT = PLANX.EFFDT AND AP.EFFSEQ = PLANX.EFFSEQ AND AP.EMPLID =C.EMPLID AND AP.ACAD_CAREER = C.ACAD_CAREER AND AP.STDNT_CAR_NBR = C.STDNT_CAR_NBR AND ((PLANX.ACAD_PLAN IN ( SELECT SETUP.ACAD_PLAN FROM PS_SSR_BR_PLAN_NLD SETUP WHERE SETUP.INSTITUTION = AP.INSTITUTION AND INST.SSR_BR_EXIN_PL_NLD = 'I' )) OR (PLANX.ACAD_PLAN IN ( SELECT PRMPT.ACAD_PLAN FROM PS_ACAD_PLAN_TBL PRMPT WHERE INST.SSR_BR_EXIN_PL_NLD = 'E' AND PRMPT.INSTITUTION = AP.INSTITUTION AND %EffdtCheck(ACAD_PLAN_TBL B2,PRMPT,%CurrentDateIn) AND PRMPT.ACAD_PLAN NOT IN ( SELECT SETUP2.ACAD_PLAN FROM PS_SSR_BR_PLAN_NLD SETUP2 WHERE SETUP2.INSTITUTION = AP.INSTITUTION) ) )))

  • Parent record: SSR_BR_TRIN_NLD

    • # PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
    1 INSTITUTION Character(5) VARCHAR2(5) NOT NULL Academic Institution
    2 SAD_SL_STUD_NBR Character(12) VARCHAR2(12) NOT NULL Student Number
    3 SSR_BRHO_ENRSQ_NLD Character(20) VARCHAR2(20) NOT NULL BRON Enrolment Seq Nbr
    4 SAD_SL_SOFI_NBR Character(9) VARCHAR2(9) NOT NULL Social Fiscal Number
    5 SAD_SL_O_NBR Character(9) VARCHAR2(9) NOT NULL Onumber
    6 SCC_BRINCD_NLD Character(4) VARCHAR2(4) NOT NULL BRINcode
    7 SCC_SUBBRINCD_NLD Character(2) VARCHAR2(2) NOT NULL Sub Institute
    8 SSR_FORM_STUDY_NLD Character(4) VARCHAR2(4) NOT NULL Form of Study Higher Education
    A=Auditor
    D=Participant
    E=Extraneus
    S=Student
    T=Coop/Dual
    9 HEGIS_CODE Character(8) VARCHAR2(8) NOT NULL HEGIS Code
    10 SSR_BR_AC_LDAP_NLD Character(2) VARCHAR2(2) NOT NULL Approved Academic Load
    DT=Part-Time
    DU=Dual
    VT=Full-Time
    11 SSR_ACAD_LEVEL_NLD Character(4) VARCHAR2(4) NOT NULL Academ. Level Higher Education
    A=Associate Degree
    B=Bachelor
    D=Propaedeutic Bachelor
    H=Main
    I=Initial Phase
    K=Candidate
    L=University teacher education
    M=Master
    P=Propaedeutic Phase
    Q=Post-Initial
    T=Interim Doctoral Exam
    V=Continuation Phase
    W=Continuing study
    12 ENROLLMENT_DT Date(10) DATE Original Enrollment Date
    13 SAD_BR_ENR_END_DT Date(10) DATE Enrollment End Date
    14 SSR_BR_END_RSN_NLD Character(80) VARCHAR2(80) NOT NULL Funding Source
    15 SSR_FUNDING_CD_NLD Character(1) VARCHAR2(1) NOT NULL Funding Applies
    16 SSR_BR_INTPRG_NLD Character(1) VARCHAR2(1) NOT NULL Indication Intensive Program
    0=No
    1=Yes
    17 ACAD_CAREER Character(4) VARCHAR2(4) NOT NULL Academic Career
    BAC=Bachelor (NLD)
    BBL=Vocational Coaching (NLD)
    BOL=Vocational Training (NLD)
    BUSN=Graduate Business
    CNED=Continuing Education
    CRED=Semester Credit
    EDU=Education (NLD)
    EXED=Extended Education
    GRAD=Graduate
    LAW=Law
    MEDS=Medical School
    NONA=Non Award
    PGRD=Postgraduate
    RSCH=Research
    TECH=Technical
    UENG=Undergraduate Engineering
    UGRD=Undergraduate
    VAVO=Advanced General Educ. (NLD)
    VETM=Veterinary Medicine
    18 STDNT_CAR_NBR Number(3,0) SMALLINT NOT NULL Student Career Nbr