INSTITUTIONACAD_PROGEFFDTDefault Value: %date
HEP=Higher Education Provider
NA=Not Applicable
TAC=Tertiary Admission Centre
VET=Vocational Education&Training
SSR_HM_COS_VW(SQL View) |
Index Back |
|---|---|
Course Of Study viewLists all the Courses of Study to be posted to HEIMS |
| SELECT AP.INSTITUTION , AP.ACAD_PROG , AP.EFFDT , AP.ACAD_PROG , CASE WHEN APA.SSR_CRS_STDY_NM != ' ' THEN APA.SSR_CRS_STDY_NM ELSE AP.DESCR END , APA.SSR_PROG_TYPE_CD , APA.SSR_EFTSU_AGGRGATD/10.0 , APA.SSR_COMBINED_CRSE , CA.SSR_PROVIDER_TYPE , AP.DESCRSHORT FROM PS_ACAD_PROG_TBL AP , PS_SSR_ACD_PRG_AUS APA , PS_SSR_CAREER_AUS CA , PS_ACAD_CAR_TBL AC ,PS_INSTITUTION_TBL I , PS_SSR_PRG_TYP_TBL PTYPE WHERE I.SCC_AUS_DEST = 'Y' AND I.INSTITUTION = AP.INSTITUTION AND AP.INSTITUTION = APA.INSTITUTION AND AP.INSTITUTION = AC.INSTITUTION AND AP.ACAD_PROG = APA.ACAD_PROG AND AP.EFFDT = APA.EFFDT AND AP.ACAD_CAREER = CA.ACAD_CAREER AND AC.INSTITUTION = CA.INSTITUTION AND AC.ACAD_CAREER = CA.ACAD_CAREER AND AC.EFFDT = CA.EFFDT AND %EffdtCheck(ACAD_PROG_TBL APB, AP, %CurrentDateIn) AND AP.EFF_STATUS = 'A' AND %EffdtCheck(ACAD_CAR_TBL ACT, AC, %CurrentDateIn) AND AC.EFF_STATUS = 'A' AND %EffdtCheck(INSTITUTION_TBL I2, I, %CurrentDateIn) AND CA.SSR_PROVIDER_TYPE = 'HEP' AND PTYPE.SSR_PROG_TYPE_CD = APA.SSR_PROG_TYPE_CD AND PTYPE.SSR_DEST_NON_DEST = 'N' AND PTYPE.EFF_STATUS = 'A' AND PTYPE.EFFDT = ( SELECT MAX(TYPE2.EFFDT) FROM PS_SSR_PRG_TYP_TBL TYPE2 WHERE TYPE2.SSR_PROG_TYPE_CD = PTYPE.SSR_PROG_TYPE_CD AND TYPE2.EFFDT <= AP.EFFDT) AND EXISTS ( SELECT ACAD_PROG FROM PS_ACAD_PLAN_TBL APLAN WHERE APLAN.INSTITUTION = AP.INSTITUTION AND APLAN.ACAD_PROG = AP.ACAD_PROG AND APLAN.EFF_STATUS = 'A' AND %EffdtCheck(ACAD_PLAN_TBL APLAN2, APLAN, %CURRENTDATEIN)) |
| # | PeopleSoft Field Name | PeopleSoft Field Type | Database Column Type | Description |
|---|---|---|---|---|
| 1 | INSTITUTION |
Character(5) | VARCHAR2(5) NOT NULL | Academic Institution |
| 2 | ACAD_PROG |
Character(5) | VARCHAR2(5) NOT NULL | Academic Program |
| 3 | EFFDT |
Date(10) | DATE |
Effective Date
Default Value: %date |
| 4 | SSR_CRSE_STDY_CODE | Character(10) | VARCHAR2(10) NOT NULL | HEIMS Course of Study Code |
| 5 | SSR_CRSE_STDY_NAME | Character(210) | VARCHAR2(210) NOT NULL | HEIMS Course of Study Name |
| 6 | SSR_CRSE_STDY_TYPE | Character(2) | VARCHAR2(2) NOT NULL | HEIMS Course of Study Type Code |
| 7 | SSR_CRSE_STDY_LOAD | Number(3,1) | DECIMAL(2,1) NOT NULL | HEIMS - E350 Course of Study Load (VET) |
| 8 | SSR_CRSE_STDY_CIND | Character(1) | VARCHAR2(1) NOT NULL | HEIMS Combined Course of Study Indicator |
| 9 | SSR_PROVIDER_TYPE | Character(4) | VARCHAR2(4) NOT NULL |
Provider Type
HEP=Higher Education Provider NA=Not Applicable TAC=Tertiary Admission Centre VET=Vocational Education&Training |
| 10 | SSR_HM_COS_SHORT | Character(10) | VARCHAR2(10) NOT NULL | Short descr COS |