SCC_EXT_IMP1_VW

(SQL View)
 Index Back

Service Impact View by Ext Org

Used by COBOL module

SELECT A.EXT_ORG_ID , A.SRVC_IND_DTTM , A.INSTITUTION , A.SRVC_IND_CD , A.SRVC_IND_ACT_TERM , A.SCC_SI_END_TERM , A.SRVC_IND_ACTIVE_DT , A.SCC_SI_END_DT , A.POS_SRVC_INDICATOR , C.EFFDT , C.SERVICE_IMPACT , C.SCC_IMPACT_TERM , C.SCC_IMPACT_DATE , C.POS_SRVC_IMPACT FROM PS_SCC_SI_ORG A , PS_SERVICE_IMPACT B , PS_SRVC_IMPACT_TBL C WHERE B.INSTITUTION = A.INSTITUTION AND B.SRVC_IND_CD = A.SRVC_IND_CD AND B.EFFDT = ( SELECT MAX(B1.EFFDT) FROM PS_SERVICE_IMPACT B1 WHERE B1.INSTITUTION = B.INSTITUTION AND B1.SRVC_IND_CD = B.SRVC_IND_CD AND B1.EFFDT <= %DatePart(A.SRVC_IND_DTTM) AND B1.SERVICE_IMPACT = B.SERVICE_IMPACT AND B1.TERM_CATEGORY = B.TERM_CATEGORY) AND C.INSTITUTION = B.INSTITUTION AND C.SERVICE_IMPACT = B.SERVICE_IMPACT AND C.EFFDT = ( SELECT MAX(C1.EFFDT) FROM PS_SRVC_IMPACT_TBL C1 WHERE C1.INSTITUTION = C.INSTITUTION AND C1.SERVICE_IMPACT = B.SERVICE_IMPACT AND C1.EFFDT <= B.EFFDT) AND C.EFF_STATUS = 'A'

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 EXT_ORG_ID Character(11) VARCHAR2(11) NOT NULL External Org ID
2 SRVC_IND_DTTM DateTime(26) TIMESTAMP Service Indicator Date-Time field
3 INSTITUTION Character(5) VARCHAR2(5) NOT NULL Academic Institution

Default Value: OPR_DEF_TBL_CS.INSTITUTION

Prompt Table: INSTITUTION_TBL

4 SRVC_IND_CD Character(3) VARCHAR2(3) NOT NULL Service Indicator Cd
5 SRVC_IND_ACT_TERM Character(4) VARCHAR2(4) NOT NULL Service Indicator Start Term
6 SCC_SI_END_TERM Character(4) VARCHAR2(4) NOT NULL Service Indicator End Term
7 SRVC_IND_ACTIVE_DT Date(10) DATE Service Indicator Start Date
8 SCC_SI_END_DT Date(10) DATE Service Indicator End Date
9 POS_SRVC_INDICATOR Character(1) VARCHAR2(1) NOT NULL Positive Service Indicator
10 EFFDT Date(10) DATE Effective Date

Default Value: %date

11 SERVICE_IMPACT Character(5) VARCHAR2(5) NOT NULL Service Impact
12 SCC_IMPACT_TERM Character(1) VARCHAR2(1) NOT NULL Service Indicator Impact attribute - Is this impact term based?
13 SCC_IMPACT_DATE Character(1) VARCHAR2(1) NOT NULL Service Indicator Impact attribute - Is this impact date based?
14 POS_SRVC_IMPACT Character(1) VARCHAR2(1) NOT NULL Positive Service Impact