SSR_HM_PE_CRS(SQL View) |
Index Back |
|---|---|
HEIMS CoursesCourses eligible for posting to HEIMS |
| SELECT APGT.INSTITUTION ,APGT.ACAD_PROG ,APLT.ACAD_PLAN ,CASE WHEN PLAUS.SSR_CRSE_NAME != ' ' THEN PLAUS.SSR_CRSE_NAME ELSE APGT.DESCR %Concat ':' %Concat APLT.DESCR END ,PRAUS.SSR_EFTSU_AGGRGATD/10 ,CASE WHEN PLAUS.SSR_STD_CRSE_DUR = 0 THEN PRAUS.SSR_STD_CRSE_DUR ELSE PLAUS.SSR_STD_CRSE_DUR END ,PRAUS.SSR_SPCL_PROG_TYPE ,PLAUS.SSR_FLD_OF_EDUCATN ,CASE WHEN PRAUS.SSR_FLD_OF_EDU_SUP = ' ' THEN '000000' ELSE PRAUS.SSR_FLD_OF_EDU_SUP END ,APLT.EFFDT ,APLT.ACAD_PLAN ,PLAUS.SSR_DEST_PLAN ,CA.SSR_PROVIDER_TYPE FROM PS_ACAD_PROG_TBL APGT , PS_SSR_ACD_PRG_AUS PRAUS , PS_ACAD_PLAN_TBL APLT , PS_SSR_ACD_PLN_AUS PLAUS , PS_INSTITUTION_TBL I , PS_ACAD_CAR_TBL AC , PS_SSR_CAREER_AUS CA , PS_SSR_PRG_TYP_TBL PTYPE WHERE I.SCC_AUS_DEST = 'Y' AND I.INSTITUTION = APGT.INSTITUTION AND %EffdtCheck(INSTITUTION_TBL I2, I, %CurrentDateIn) AND PLAUS.SSR_DEST_PLAN = 'Y' AND APGT.INSTITUTION = PLAUS.INSTITUTION AND APGT.INSTITUTION = PRAUS.INSTITUTION AND APGT.INSTITUTION = APLT.INSTITUTION AND APLT.ACAD_PLAN = PLAUS.ACAD_PLAN AND APLT.EFFDT = PLAUS.EFFDT AND APLT.ACAD_PROG = APGT.ACAD_PROG AND APGT.ACAD_PROG = PRAUS.ACAD_PROG AND APGT.EFFDT = PRAUS.EFFDT AND %EffdtCheck(ACAD_PROG_TBL APB, APGT, %CurrentDateIn) AND APGT.EFF_STATUS = 'A' AND %EffdtCheck(ACAD_PLAN_TBL APLB, APLT, %CurrentDateIn) AND APLT.EFF_STATUS = 'A' AND APGT.INSTITUTION = AC.INSTITUTION AND APGT.ACAD_CAREER = AC.ACAD_CAREER AND %EffdtCheck(ACAD_CAR_TBL ACT, AC, %CurrentDateIn) AND AC.INSTITUTION = CA.INSTITUTION AND AC.ACAD_CAREER = CA.ACAD_CAREER AND AC.EFF_STATUS = 'A' AND AC.EFFDT = CA.EFFDT AND PTYPE.SSR_PROG_TYPE_CD = PRAUS.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 <= APGT.EFFDT) UNION SELECT APGT.INSTITUTION ,APGT.ACAD_PROG , ' ' ,CASE WHEN PRAUS.SSR_CRS_STDY_NM != ' ' THEN PRAUS.SSR_CRS_STDY_NM ELSE APGT.DESCR END ,PRAUS.SSR_EFTSU_AGGRGATD/10 ,PRAUS.SSR_STD_CRSE_DUR ,PRAUS.SSR_SPCL_PROG_TYPE ,PRAUS.SSR_FLD_OF_EDUCATN ,CASE WHEN PRAUS.SSR_FLD_OF_EDU_SUP = ' ' THEN '000000' ELSE PRAUS.SSR_FLD_OF_EDU_SUP END ,APGT.EFFDT ,APGT.ACAD_PROG ,'N' ,CA.SSR_PROVIDER_TYPE FROM PS_ACAD_PROG_TBL APGT , PS_SSR_ACD_PRG_AUS PRAUS , PS_INSTITUTION_TBL I , PS_ACAD_CAR_TBL AC , PS_SSR_CAREER_AUS CA , PS_SSR_PRG_TYP_TBL PTYPE WHERE I.SCC_AUS_DEST = 'Y' AND I.INSTITUTION = APGT.INSTITUTION AND %EffdtCheck(INSTITUTION_TBL I2, I, %CurrentDateIn) AND APGT.INSTITUTION = PRAUS.INSTITUTION AND APGT.ACAD_PROG = PRAUS.ACAD_PROG AND %EffdtCheck(ACAD_PROG_TBL APB, APGT, %CurrentDateIn) AND APGT.EFF_STATUS = 'A' AND APGT.EFFDT = PRAUS.EFFDT AND APGT.INSTITUTION = AC.INSTITUTION AND APGT.ACAD_CAREER = AC.ACAD_CAREER AND %EffdtCheck(ACAD_CAR_TBL ACT, AC, %CurrentDateIn) AND AC.INSTITUTION = CA.INSTITUTION AND AC.ACAD_CAREER = CA.ACAD_CAREER AND AC.EFF_STATUS = 'A' AND AC.EFFDT = CA.EFFDT AND PTYPE.SSR_PROG_TYPE_CD = PRAUS.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 <= APGT.EFFDT) AND EXISTS ( SELECT APLNT.ACAD_PROG FROM PS_SSR_ACD_PLN_AUS APLAUS , PS_ACAD_PLAN_TBL APLNT WHERE APLNT.INSTITUTION = APLAUS.INSTITUTION AND APGT.INSTITUTION = APLNT.INSTITUTION AND APLAUS.ACAD_PLAN = APLNT.ACAD_PLAN AND APGT.ACAD_PROG = APLNT.ACAD_PROG AND APLAUS.EFFDT = APLNT.EFFDT AND %EffdtCheck(ACAD_PLAN_TBL APLNTB, APLNT, %CurrentDateIn) AND APLAUS.SSR_DEST_PLAN = 'N') |
| # | 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 | ACAD_PLAN |
Character(10) | VARCHAR2(10) NOT NULL | Academic Plan |
| 4 | SSR_CRSE_NAME | Character(210) | VARCHAR2(210) NOT NULL | Course Name (HEIMS) |
| 5 | SSR_EFTSU_AGGRGATD | Number(2,0) | SMALLINT NOT NULL | Aggregated EFTSU |
| 6 | SSR_STD_CRSE_DUR | Number(5,3) | DECIMAL(4,3) NOT NULL | Standard Course Duration (HEIMS) |
| 7 | SSR_SPCL_PROG_TYPE | Character(2) | VARCHAR2(2) NOT NULL |
Special Program Type - AUS
00=Not Course of Special Interest 15=AUS Maritime College Special 15=AUS Maritime College Special 21=General Nursing Course Req 22=Initial Teacher Training 23=Provisional Reg Medical 25=Veterinary Science 26=Dentistry 27=Clinical Psychology |
| 8 | SSR_FLD_OF_EDUCATN | Character(8) | VARCHAR2(8) NOT NULL | SCS SA LTRA8N2 Created. |
| 9 | SSR_FLD_OF_EDU_SUP | Character(8) | VARCHAR2(8) NOT NULL | SCS SA LTRA8N2 Created. |
| 10 | EFFDT_FROM | Date(10) | DATE | Effective Date From |
| 11 | SSR_CRSE_CODE | Character(10) | VARCHAR2(10) NOT NULL | HEIMS Course Code |
| 12 | SSR_DEST_PLAN | Character(1) | VARCHAR2(1) NOT NULL | DEST Plan |
| 13 | 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 |