SSR_DSTADD_SA_V(SQL View) |
Index Back |
|---|---|
SAH Add view |
| SELECT SA.BUSINESS_UNIT , SA.EMPLID , SA.STRM , SA.ACAD_CAREER , SA.ACAD_PROG , SA.SSR_HECS_XMPT_STAT , B.SSF_INCURRAL_DT , SUM(SA.SSF_HECS_CHARGE) , SUM(SA.SSF_HECS_PAYMENT) , SUM(SA.SSF_HECS_DEFERRAL) FROM PS_SSF_SAH_RES SA , PS_SSF_SAH_DEF_TM B , PS_SSR_DESTWRK_HDR C WHERE SA.EFFDT = ( SELECT MAX(SA4.EFFDT) FROM PS_SSF_SAH_RES SA4 WHERE SA4.BUSINESS_UNIT = SA.BUSINESS_UNIT AND SA4.EMPLID = SA.EMPLID AND SA4.STRM = SA.STRM AND SA4.ACAD_CAREER = SA.ACAD_CAREER AND SA4.ACAD_PROG = SA.ACAD_PROG AND SA4.SESSION_CODE = SA.SESSION_CODE) AND SA.SEQ_NUM = ( SELECT MAX(SA5.SEQ_NUM) FROM PS_SSF_SAH_RES SA5 WHERE SA5.BUSINESS_UNIT = SA.BUSINESS_UNIT AND SA5.EMPLID = SA.EMPLID AND SA5.STRM = SA.STRM AND SA5.ACAD_CAREER = SA.ACAD_CAREER AND SA5.ACAD_PROG = SA.ACAD_PROG AND SA5.SESSION_CODE = SA.SESSION_CODE AND SA5.EFFDT = SA.EFFDT) AND B.STRM = SA.STRM AND B.SESSION_CODE = SA.SESSION_CODE AND B.SETID = ( SELECT SETIDSA.SETID FROM PS_SET_CNTRL_REC SETIDSA WHERE SETIDSA.RECNAME = 'SSF_SAH_DEF' AND SETIDSA.SETCNTRLVALUE = SA.BUSINESS_UNIT) AND SA.BUSINESS_UNIT IN ( SELECT BUSA.BUSINESS_UNIT FROM PS_BUS_UNIT_TBL_SF BUSA WHERE BUSA.INSTITUTION = C.INSTITUTION) AND NOT EXISTS ( SELECT 'X' FROM PS_SSR_DESTWRK_SA DUPSA WHERE DUPSA.INSTITUTION = C.INSTITUTION AND DUPSA.SSR_REF_YEAR = C.SSR_REF_YEAR AND DUPSA.SSR_REF_YR_HALF = C.SSR_REF_YR_HALF AND DUPSA.EMPLID = SA.EMPLID AND DUPSA.ACAD_PROG = SA.ACAD_PROG AND DUPSA.SSR_HECS_XMPT_STAT = SA.SSR_HECS_XMPT_STAT AND DUPSA.STRM = SA.STRM AND DUPSA.SSF_INCURRAL_DT = B.SSF_INCURRAL_DT) AND SA.SSF_HECS_DEFERRAL > 0 AND SA.STRM IN ( SELECT Q.STRM FROM PS_TERM_TBL Q WHERE Q.INSTITUTION = C.INSTITUTION AND Q.ACAD_CAREER = SA.ACAD_CAREER AND Q.STRM = SA.STRM AND Q.TERM_BEGIN_DT BETWEEN C.SSR_CENSUS_STRT_DT AND C.SSR_CENSUS_END_DT ) GROUP BY SA.BUSINESS_UNIT, SA.EMPLID, SA.STRM, SA.ACAD_CAREER, SA.ACAD_PROG, SA.SSR_HECS_XMPT_STAT, B.SSF_INCURRAL_DT |
| # | PeopleSoft Field Name | PeopleSoft Field Type | Database Column Type | Description |
|---|---|---|---|---|
| 1 |  BUSINESS_UNIT |
Character(5) | VARCHAR2(5) NOT NULL | Business Unit |
| 2 | EMPLID |
Character(11) | VARCHAR2(11) NOT NULL | Employee ID |
| 3 | STRM |
Character(4) | VARCHAR2(4) NOT NULL | Term |
| 4 | 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 |
| 5 | ACAD_PROG |
Character(5) | VARCHAR2(5) NOT NULL | Academic Program |
| 6 | SSR_HECS_XMPT_STAT |
Character(3) | VARCHAR2(3) NOT NULL | HECS Exempt Status |
| 7 | SSF_INCURRAL_DT | Date(10) | DATE | Due Date |
| 8 | SSF_HECS_CHARGE | Signed Number(18,2) | DECIMAL(16,2) NOT NULL | HECS Charge |
| 9 | SSF_HECS_PAYMENT | Signed Number(18,2) | DECIMAL(16,2) NOT NULL | HECS Payment |
| 10 | SSF_HECS_DEFERRAL | Signed Number(18,2) | DECIMAL(16,2) NOT NULL | HECS Deferral |