View - dbo.arch_StudentPrimaryAward

Property	Value
Name	arch_StudentPrimaryAward
Schema	dbo
Quoted Identifier Status	True
Ansi Nulls Status	True
Returns View Metadata	False
Create Date	10/1/2014 11:23 AM
Description

Column Name	Data Type	Allow Nulls	Fulltext Indexed	Identity	Description / Computed Formula
EffectiveTerm	varchar(6)	True	False	False
InitialProgramCode	varchar(5)	True	False	False
IsPrimaryProgram	varchar(3)	False	False	False
LimitedAccessAcceptanceDate	date(10, 0)	True	False	False
LimitedAccessApplicationDate	date(10, 0)	True	False	False
LimitedAccessApplicationTerm	varchar(6)	True	False	False
LimitedAccessBasisOfAdmission	varchar(30)	True	False	False
LimitedAccessBasisOfAdmissionCode	varchar(2)	True	False	False
LimitedAccessLastTermAttended	varchar(6)	True	False	False
LimitedAccessStatus	varchar(30)	True	False	False
LimitedAccessStatusCode	varchar(4)	True	False	False
MostCurrentEffectiveTerm	varchar(6)	True	False	False
PrimaryObjective	varchar(5)	True	False	False
PrimaryObjectiveAwardType	varchar(30)	True	False	False
PrimaryObjectiveAwardTypeCode	varchar(3)	True	False	False
PrimaryObjectiveSequenceNumber	int(10, 0)	True	False	False
PrimaryObjectiveShortTitle1	varchar(30)	True	False	False
PrimaryObjectiveShortTitle2	varchar(30)	True	False	False
PrimaryObjectiveTitle	varchar(60)	True	False	False
PrimaryObjectiveTrackTitle1	varchar(30)	True	False	False
PrimaryObjectiveTrackTitle2	varchar(30)	True	False	False
StudentId	varchar(9)	True	False	False
TransferInstitution	varchar(8)	True	False	False
TransferInstitutionName	varchar(32)	True	False	False
TransferMajor	varchar(6)	True	False	False
TransferMajorDescription	varchar(30)	True	False	False

Name

Insert

Update

Delete

Instead

Name

Clustered

Columns

Name	Type
ST_INSTITUTION_A	Table
ST_PROGRAMS_A	Table
ST_STDNT_OBJ_AWD_A	Table
UTL_CODE_TABLE	Table
arch_StudentCombined	View

Name	Type
arch_StudentCombined	VIEW
ST_INSTITUTION_A	TABLE
ST_PROGRAMS_A	TABLE
ST_STDNT_OBJ_AWD_A	TABLE
UTL_CODE_TABLE	TABLE

Object

Property

Value

Object

Property

Value

/****** Object: View [dbo].[arch_StudentPrimaryAward] Script Date: 03/09/2017 16:47:09 ******/
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE VIEW [dbo].[arch_StudentPrimaryAward]
AS
SELECT   StObjAwd.STDNT_ID AS StudentId
        ,StObjAwd.PGM_ID AS PrimaryObjective
        ,CASE
           WHEN StObjAwd.PRIM_FLG = 1 THEN 'Yes'
           ELSE 'No'
         END AS IsPrimaryProgram
        ,ST_PROGRAMS_DEMO_V1_1.PGM_TRK_TTL AS PrimaryObjectiveTitle
        ,SUBSTRING(ST_PROGRAMS_DEMO_V1_1.PGM_SHRT_TTL,1,30)  AS PrimaryObjectiveShortTitle1
        ,SUBSTRING(ST_PROGRAMS_DEMO_V1_1.PGM_SHRT_TTL,31,30)  AS PrimaryObjectiveShortTitle2
        ,SUBSTRING(ST_PROGRAMS_DEMO_V1_1.PGM_SHRT_TRK_TTL,1,30) AS PrimaryObjectiveTrackTitle1
        ,SUBSTRING(ST_PROGRAMS_DEMO_V1_1.PGM_SHRT_TRK_TTL,31,30) AS PrimaryObjectiveTrackTitle2
        ,StObjAwd.EFF_TERM AS EffectiveTerm
        ,StObjAwd.AWD_TYPE AS PrimaryObjectiveAwardTypeCode
        ,AwdType.DESCRIPTION AS PrimaryObjectiveAwardType
        ,StObjAwd.SEQ_NUM AS PrimaryObjectiveSequenceNumber
        ,StObjAwd.LA_STAT AS LimitedAccessStatusCode
        ,LaStat.DESCRIPTION AS LimitedAccessStatus
        ,StObjAwd.LA_APPL_TRM AS LimitedAccessApplicationTerm
        ,CONVERT(Date,StObjAwd.LA_APPL_DT) AS LimitedAccessApplicationDate
        ,StObjAwd.LA_BAS_ADM AS LimitedAccessBasisOfAdmissionCode
        ,EnrlCd.DESCRIPTION AS  LimitedAccessBasisOfAdmission
        ,CONVERT(Date,StObjAwd.LA_ACKNLDG_DT) AS LimitedAccessAcceptanceDate
        ,StObjAwd.LA_LST_TRM_ATND AS LimitedAccessLastTermAttended
        ,x.MaxEffTrm MostCurrentEffectiveTerm
        ,x.InitPgmCd InitialProgramCode
        ,StObjAwd.TRNSFR_MJR AS TransferMajor
        ,CASE
           WHEN StObjAwd.TRNSFR_MJR IS NOT NULL AND ST_INSTITUTION_A.INST_ID IS NOT NULL  AND SusMajor.DESCRIPTION IS NULL
              THEN 'Invalid Major'
           ELSE SusMajor.DESCRIPTION
         END AS TransferMajorDescription
        ,CASE
          WHEN ST_INSTITUTION_A.INST_ID IS NOT NULL  THEN StObjAwd.TRNSFR_INST
          ELSE  ST_INSTITUTION_A.INST_ID
         END AS TransferInstitution
        ,ST_INSTITUTION_A.INST_NM AS TransferInstitutionName
FROM ST_STDNT_OBJ_AWD_A AS StObjAwd
LEFT OUTER JOIN UTL_CODE_TABLE AS SusMajor
ON
(
   SusMajor.code = StObjAwd.TRNSFR_MJR
   AND SusMajor.TABLE_NAME = 'SUS-MAJORS'
   AND SusMajor.STATUS = 'A'
)
--FROM [UTL_CODE_TABLE] AS SusMajor
--RIGHT OUTER JOIN ST_STDNT_OBJ_AWD_A AS StObjAwd
--ON
-- (SusMajor.code = StObjAwd.TRNSFR_MJR)
LEFT OUTER JOIN ST_INSTITUTION_A
ON
  (StObjAwd.TRNSFR_INST = ST_INSTITUTION_A.INST_ID)
LEFT OUTER JOIN ST_PROGRAMS_A AS ST_PROGRAMS_DEMO_V1_1
INNER JOIN
   ( SELECT PGM_CD AS InitPgmCd ,
            MAX(EFF_TRM_D) AS MaxEffTrm
     FROM   ST_PROGRAMS_A
     WHERE (ST_PROGRAMS_A.PGM_CD IS NOT NULL) AND (ST_PROGRAMS_A.EFF_TRM_D IS NOT NULL)
     GROUP BY ST_PROGRAMS_A.PGM_CD
    ) AS x
    ON
         ST_PROGRAMS_DEMO_V1_1.PGM_CD = x.InitPgmCd
         AND ST_PROGRAMS_DEMO_V1_1.EFF_TRM_D = x.MaxEffTrm ON StObjAwd.PGM_ID = x.InitPgmCd

LEFT OUTER JOIN UTL_CODE_TABLE AwdType
ON
(
     AwdType.TABLE_NAME = 'AWARD-TYPE'
     AND  AwdType.CODE = StObjAwd.AWD_TYPE
     AND AwdType.STATUS = 'A'
)
LEFT OUTER JOIN UTL_CODE_TABLE LaStat
ON
(
     LaStat.TABLE_NAME = 'LAPS-STAT'
     AND  LaStat.CODE = StObjAwd.LA_STAT
     AND LaStat.STATUS = 'A'
)
LEFT OUTER JOIN UTL_CODE_TABLE EnrlCd
ON
(
     EnrlCd.TABLE_NAME = 'ENRL-CODE'
     AND  EnrlCd.CODE = StObjAwd.LA_BAS_ADM
     AND EnrlCd.STATUS = 'A'
)
WHERE  -- SusMajor.TABLE_NAME = 'SUS-MAJORS' AND SusMajor.STATUS = 'A' AND
( StObjAwd.PRIM_FLG = '1' )
        AND ( ST_INSTITUTION_A.INST_TY = 'P'
              OR ST_INSTITUTION_A.INST_TY IS NULL
            )
AND (ST_PROGRAMS_DEMO_V1_1.PGM_CD IS NOT NULL) AND (ST_PROGRAMS_DEMO_V1_1.EFF_TRM_D IS NOT NULL)
AND (StObjAwd.PGM_STAT <> 'DI' AND StObjAwd.PGM_STAT <> 'SU' AND StObjAwd.PGM_STAT <> 'IN')