SAD_BR_M322_NLD

(SQL View)
 Index Back

SELECT DISTINCT ES.INSTITUTION , ES.SCC_BRINCD_NLD , ES.SAD_BR_EDU_SEC_NLD , ES.EMPLID , ES.SAD_BR_ENRL_SQ_NLD , BPV.SAD_BR_BPV_SEQ_NLD , BPV.SAD_BR_BPV_CRT_DT , BPV.START_DATE , BPV.END_DATE , (%Sql(SAD_BR_322_GET_RDT_BPV_SQL,BPV.CANCEL_DATE,AP.PROG_STATUS,BPV.END_DATE,AP.EFFDT)) , (%Sql(SAD_BR_322_GET_INTCOM_SQL,BPV.EXT_ORG_ID,BPV.ORG_LOCATION,(%Sql(SAD_BR_322_KCID_SQL,CASE WHEN BPV.AKA_HEGIS_CODE = ' ' THEN BPV.HEGIS_CODE ELSE BPV.AKA_HEGIS_CODE END,BPV.SAD_BR_BPV_CRT_DT)))) , CASE WHEN BPV.SAD_TOTHRS_BPV_NLD > 0 THEN BPV.SAD_TOTHRS_BPV_NLD ELSE (BPV.SAD_CONTR_LNGT_NLD * BPV.SAD_WEEK_HRS_NLD) END , (%Sql(SAD_BR_322_KCBRIN_SQL,CASE WHEN BPV.AKA_HEGIS_CODE = ' ' THEN BPV.HEGIS_CODE ELSE BPV.AKA_HEGIS_CODE END,BPV.SAD_BR_BPV_CRT_DT)) , (%Sql(SAD_BR_GET_BPV_ORGNAME_SQL,BPV.EXT_ORG_ID,BPV.ORG_LOCATION,(%Sql(SAD_BR_SETUP_BPV_FLD_SQL, ES.INSTITUTION, ES.SCC_BRINCD_NLD, ES.SAD_BR_EDU_SEC_NLD)), (%Sql(SAD_BR_SETUP_BPV_REC_SQL, ES.INSTITUTION, ES.SCC_BRINCD_NLD, ES.SAD_BR_EDU_SEC_NLD)) )) , (%Sql(SAD_BR_BPV_ADDR_POSTAL_SQL,BPV.EXT_ORG_ID,BPV.ORG_LOCATION,(%Sql(SAD_BR_SETUP_BPV_AD_TYP_SQL, ES.INSTITUTION, ES.SCC_BRINCD_NLD, ES.SAD_BR_EDU_SEC_NLD)) )) , (%Sql(SAD_BR_BPV_ADDR_NUMBER_SQL,BPV.EXT_ORG_ID,BPV.ORG_LOCATION,(%Sql(SAD_BR_SETUP_BPV_AD_TYP_SQL, ES.INSTITUTION, ES.SCC_BRINCD_NLD, ES.SAD_BR_EDU_SEC_NLD)) )) , %Substring(BPV.HEGIS_CODE,1,5) ,BPV.EXT_ORG_ID ,BPV.ORG_LOCATION ,(%Sql(SAD_BR_322_KCID_SQL,CASE WHEN BPV.AKA_HEGIS_CODE = ' ' THEN BPV.HEGIS_CODE ELSE BPV.AKA_HEGIS_CODE END,BPV.SAD_BR_BPV_CRT_DT)) , ES.ACAD_CAREER , ES.STDNT_CAR_NBR FROM PS_SAD_BR_ENRSQNLD ES JOIN PS_SAD_BPV_STC_NLD BPV ON ES.EMPLID = BPV.EMPLID AND ES.ACAD_CAREER = BPV.ACAD_CAREER AND ES.STDNT_CAR_NBR = BPV.STDNT_CAR_NBR JOIN PS_ACAD_PROG AP ON AP.EMPLID = BPV.EMPLID AND BPV.SAD_BR_EX_BRON_NLD <> 'Y' AND BPV.SAD_PR_TSTSTAT_NLD = 'P' AND AP.ACAD_CAREER = BPV.ACAD_CAREER AND AP.STDNT_CAR_NBR = BPV.STDNT_CAR_NBR AND AP.EFFDT = ( SELECT MAX(AP_ED.EFFDT) FROM PS_ACAD_PROG AP_ED WHERE AP_ED.EMPLID = AP.EMPLID AND AP_ED.ACAD_CAREER = AP.ACAD_CAREER AND AP_ED.STDNT_CAR_NBR = AP.STDNT_CAR_NBR AND AP_ED.EFFDT <= BPV.END_DATE) AND AP.EFFSEQ = ( SELECT MAX(AP_ES.EFFSEQ) FROM PS_ACAD_PROG AP_ES WHERE AP.EMPLID = AP_ES.EMPLID AND AP.ACAD_CAREER = AP_ES.ACAD_CAREER AND AP.STDNT_CAR_NBR = AP_ES.STDNT_CAR_NBR AND AP.EFFDT = AP_ES.EFFDT)

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 INSTITUTION Character(5) VARCHAR2(5) NOT NULL Academic Institution
2 SCC_BRINCD_NLD Character(4) VARCHAR2(4) NOT NULL BRINcode
3 SAD_BR_EDU_SEC_NLD Character(2) VARCHAR2(2) NOT NULL Educational Sector
BO=Professional Education
ED=Education
VA=VAVO
4 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID
5 SAD_BR_ENRL_SQ_NLD Character(3) VARCHAR2(3) NOT NULL Enrollment Sequence number
6 SAD_BR_BPV_SEQ_NLD Number(2,0) SMALLINT NOT NULL BPV sequence number
7 SAD_BR_BPV_CRT_DT Date(10) DATE NOT NULL BPV End Date for BRON
8 SAD_BR_BDT_BPV_NLD Date(10) DATE BRON BPV begin date
9 SAD_BR_PDT_BPV_NLD Date(10) DATE Planned End Date BPV
10 SAD_BR_RDT_BPV_NLD Date(10) DATE Real end date BPV
11 SAD_BR_INTSHIP_NLD Character(40) VARCHAR2(40) NOT NULL BRON Internship Company
12 SAD_BR_HRS_BPV_NLD Number(4,0) SMALLINT NOT NULL BRON Hours BPV
13 SAD_BPV_BRIN_ACC Character(4) VARCHAR2(4) NOT NULL BRIN Code of Accreditation Co
14 SAD_BR_NM_BPV_NLD Character(80) VARCHAR2(80) NOT NULL Field used to store descr name external Org for BRON
15 SAD_BR_PST_BPV_NLD Character(12) VARCHAR2(12) NOT NULL Field used to store postcode external organization for BRON delivery
16 SAD_NUM1_OLD_NLD Character(6) VARCHAR2(6) NOT NULL Number 1
17 SAD_BR_HEG_BPV_NLD Character(5) VARCHAR2(5) NOT NULL Field used to store CREBO code BPV students for BRON
18 SAD_BR_EXT_ORG_NLD Character(11) VARCHAR2(11) NOT NULL Field used to store EXT_ORG_ID for BRON delivery
19 SAD_BR_ORG_LOC_NLD Number(3,0) SMALLINT NOT NULL Field used to store ORG LOCATION external organization for BRON delivery
20 SAD_BR_KCID_NLD Character(11) VARCHAR2(11) NOT NULL Field used to store knowledge centre ID for BRON
21 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
22 STDNT_CAR_NBR Number(3,0) SMALLINT NOT NULL Student Career Nbr