Stored Procedure - usp_FactApplicationStatus

Property	Value
Name	usp_FactApplicationStatus_Select
Schema	dbo
Is Encrypted	False
Ansi Nulls Status	True
Quoted Identifier Status	True
Description

Name

Data Type

Direction

Description

Name	Type
ST_ED_PLAN_A	Table
ST_ERR_CNTL_A	Table
ST_HOLDS_A	Table
ST_PROGRAMS_A	Table
ST_SESSION_A	Table
ST_STDNT_A	Table
ST_STDNT_ADV_HIST_A	Table
ST_STDNT_CLS_A	Table
ST_STDNT_LOG_A	Table
ST_STDNT_OBJ_AWD_A	Table
ST_STDNT_TEST_DEMO_A	Table
UTL_CODE_TABLE	Table
UTL_CODE_TABLE_GENERIC	Table

Name	Type
UTL_CODE_TABLE	TABLE
UTL_CODE_TABLE_GENERIC	TABLE
ST_ED_PLAN_A	TABLE
ST_ERR_CNTL_A	TABLE
ST_HOLDS_A	TABLE
ST_PROGRAMS_A	TABLE
ST_SESSION_A	TABLE
ST_STDNT_A	TABLE
ST_STDNT_ADV_HIST_A	TABLE
ST_STDNT_CLS_A	TABLE
ST_STDNT_LOG_A	TABLE
ST_STDNT_OBJ_AWD_A	TABLE
ST_STDNT_TEST_DEMO_A	TABLE
udf_StringToDate	USERDEFINEDFUNCTION

Object

Property

Value

Object

Property

Value

/****** Object: StoredProcedure [dbo].[usp_FactApplicationStatus_Select] Script Date: 03/09/2017 16:47:10 ******/
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
/*
Name:            usp_FactApplicationStatus_Select
Title:            Primary Select for the Student Application Fact
System/Project:    Admissions Dashboard
Description:    This procedure will retrieve the record of the current status of student's application to the college
                including outstanding document requirements and indication of subsequent enrollment.
Revision History:
08/19/2014 Ian Peterson    Created.
                            Commented out code that only set the status measures to 1 if the current date
                            was within the application term.
*/
CREATE PROC [dbo].[usp_FactApplicationStatus_Select]
AS
SET NOCOUNT ON
/********************************************************************************************************
    The following is done to specify the final output columns from this stored procedure.
    It is needed because we do many other select statements for variables and temporary tables,
    before finally doing the main (output) select.
********************************************************************************************************/
IF 2=3
    BEGIN
    SELECT
          CAST('aaa' AS varchar(23))    AS ApplicationAK
        , CAST('aaa' AS varchar(9))        AS StudentId
        , CAST('aaa' AS varchar(6))        AS ApplicationTerm
        , CAST('aaa' AS varchar(10))    AS ApplicationSource
        , CAST('aaa' AS varchar(23))    AS StudentObjectiveAK
        , CAST(GETDATE() AS datetime)    AS ApplicationDate
        , CAST(1 as smallint)    AS NeedTesting
        , CAST(1 as smallint)    AS NeedAdvising
        , CAST(1 as smallint)    AS NeedOrientation
        , CAST(1 as smallint)    AS NeedTranscripts
        , CAST(1 as smallint)    AS ResidencyHold
        , CAST(1 as smallint)    AS NeedEducationalPlan
        , CAST(1 as smallint)    AS OtherRegistrationHold
        , CAST(1 as smallint)    AS NoAdmissionBarriers
        , CAST(1 as smallint)    AS NotEnrolled
        , CAST(1 as smallint)    AS EnrolledAfterApplicationTerm
        , CAST(1 as smallint)    AS EnrolledInApplicationTerm
    END

/********************************************************************************************************
    Get the Implementation Term of Integrow at the member institution.
    This is used to only retrieve student applications since that term.
********************************************************************************************************/
DECLARE @ImplTerm VARCHAR(6)
SET @ImplTerm =
(
  SELECT
     CAST(CASE
       WHEN SiteParam.DESCRIPTION IS NULL THEN '20001'
       ELSE SUBSTRING(DESCRIPTION,1,6)
       END
        AS VARCHAR(6))
  FROM UTL_CODE_TABLE SiteParam
  WHERE SiteParam.TABLE_NAME = 'SITE-PARAM'
  AND SiteParam.CODE = 'IMPLNT-TRM' AND SiteParam.STATUS = 'A'
)
/********************************************************************************************************
    Get the enforcement condition for the requirement of a student educational plan.
********************************************************************************************************/
DECLARE @EdPlanReq VARCHAR(1)
SET @EdPlanReq =
(
    SELECT
        CAST(CASE
            WHEN ST_ERR_CNTL_A.ENFRC_FLG = 'E' THEN 'Y'    -- Is the requirement enforced?
            ELSE 'N'
            END AS VARCHAR(1))
    FROM ST_ERR_CNTL_A
    WHERE ST_ERR_CNTL_A.MSG_CD = '1199'                    -- Education Plan is Required edit in registration
)
/********************************************************************************************************
    Retrieve all the basis of admission values for each application source.
********************************************************************************************************/
/* If temp table already exists, drop it. */
IF OBJECT_ID('tempdb.dbo.#ApplicationSource') IS NOT NULL
    DROP TABLE #ApplicationSource
/* Create new temp table to contain the application sources and basis of admissions for each. */
CREATE TABLE #ApplicationSource
(
    AppSourceCode varchar(10) NULL,
    BasisOfAdmissionCode varchar(1) NULL
)
INSERT INTO #ApplicationSource
(
     AppSourceCode
    ,BasisOfAdmissionCode
)
    SELECT
        appSrc.CODE
        ,appSrcBA.FIELD_VALUE
    FROM UTL_CODE_TABLE appSrc
    LEFT OUTER JOIN UTL_CODE_TABLE_GENERIC appSrcBA
       ON (
            appSrc.ISN_UTL_CODE_TABLE = appSrcBA.ISN_UTL_CODE_TABLE
            AND appSrcBA.FIELD_VALUE IS NOT NULL
          )
    WHERE (
        appSrc.TABLE_NAME = 'DWAPPSOURC' AND
        appSrc.[STATUS] = 'A'
    )
/********************************************************************************************************
    Retrieve the enrollment codes that require advising.
********************************************************************************************************/
/* If temp table already exists, drop it. */
IF OBJECT_ID('tempdb.dbo.#EnrollmentCodeTbl') IS NOT NULL
    DROP TABLE #EnrollmentCodeTbl
CREATE TABLE #EnrollmentCodeTbl
(
    EnrollmentCode varchar(2) NULL,
    Exceptions varchar(30)                 NULL-- Either empty of a comma-delimited list of 1-digit program codes
)
INSERT INTO #EnrollmentCodeTbl
(
     EnrollmentCode
    ,Exceptions
)
    SELECT
        EnrlTbl.CODE
        ,CASE
            WHEN EnrlSubTbl.FIELD_VALUE IS NULL THEN NULL
            WHEN LEN(EnrlSubTbl.FIELD_VALUE) < 3 THEN NULL
            ELSE SUBSTRING(EnrlSubTbl.FIELD_VALUE,3,LEN(EnrlSubTbl.FIELD_VALUE)-2)
            END
    FROM UTL_CODE_TABLE EnrlTbl
    LEFT OUTER JOIN UTL_CODE_TABLE_GENERIC EnrlSubTbl
       ON (
            EnrlTbl.ISN_UTL_CODE_TABLE = EnrlSubTbl.ISN_UTL_CODE_TABLE
            AND EnrlSubTbl.cnxarraycolumn =         0-- First table sub-attribute - Advisement Required
          )
    WHERE (
        EnrlTbl.TABLE_NAME = 'ENRL-CODE' AND
        EnrlTbl.[STATUS] = 'A' AND
        LEFT(ISNULL(EnrlSubTbl.FIELD_VALUE,'N'),1) = 'Y'            -- Enrollment code requires advising
    )
/********************************************************************************************************
    Retrieve the Orientation criteria from the tables.
********************************************************************************************************/
IF OBJECT_ID('tempdb.dbo.#OrientationTbl') IS NOT NULL
    DROP TABLE #OrientationTbl
CREATE TABLE #OrientationTbl
(
    StartTerm varchar(6) NULL
    ,EndTerm varchar(6) NULL
    ,BasisOfAdmission varchar(30) NULL
    ,AwardType varchar(30) NULL
    ,MustBeComplete varchar(1) NULL
    ,CourseId varchar(30) NULL
)
INSERT INTO #OrientationTbl
(
    StartTerm
    ,EndTerm
    ,BasisOfAdmission
    ,AwardType
    ,MustBeComplete
    ,CourseId
)
    SELECT
        LEFT(UTL_CODE_TABLE.CODE,6)
        ,LEFT(ISNULL(EndTermTbl.FIELD_VALUE,'999999'),6)
        ,BasisAdmTbl.FIELD_VALUE
        ,AwardTypeTbl.FIELD_VALUE
        ,LEFT(ISNULL(CompleteTbl.FIELD_VALUE,'N'),1)
        ,    CASE-- Only get first course or course prefix; eliminate '*' wildcard.
            WHEN CHARINDEX(',',CrsTbl.FIELD_VALUE) > 0 THEN SUBSTRING(CrsTbl.FIELD_VALUE,1,CHARINDEX(',',CrsTbl.FIELD_VALUE)-1)
            WHEN CHARINDEX('*',CrsTbl.FIELD_VALUE) > 0 THEN SUBSTRING(CrsTbl.FIELD_VALUE,1,CHARINDEX('*',CrsTbl.FIELD_VALUE)-1)
            ELSE CrsTbl.FIELD_VALUE
            END
    FROM UTL_CODE_TABLE
    LEFT OUTER JOIN UTL_CODE_TABLE_GENERIC EndTermTbl
       ON (
            EndTermTbl.ISN_UTL_CODE_TABLE = UTL_CODE_TABLE.ISN_UTL_CODE_TABLE
            AND EndTermTbl.cnxarraycolumn =         0-- First table sub-attribute - Ending Term
          )
    LEFT OUTER JOIN UTL_CODE_TABLE_GENERIC BasisAdmTbl
       ON (
            BasisAdmTbl.ISN_UTL_CODE_TABLE = UTL_CODE_TABLE.ISN_UTL_CODE_TABLE
            AND BasisAdmTbl.cnxarraycolumn =         1-- Second table sub-attribute - Basis of Admissions
          )
    LEFT OUTER JOIN UTL_CODE_TABLE_GENERIC AwardTypeTbl
       ON (
            AwardTypeTbl.ISN_UTL_CODE_TABLE = UTL_CODE_TABLE.ISN_UTL_CODE_TABLE
            AND AwardTypeTbl.cnxarraycolumn =         2-- Third table sub-attribute - Award Types
          )
    LEFT OUTER JOIN UTL_CODE_TABLE_GENERIC CompleteTbl
       ON (
            CompleteTbl.ISN_UTL_CODE_TABLE = UTL_CODE_TABLE.ISN_UTL_CODE_TABLE
            AND CompleteTbl.cnxarraycolumn =         4-- Fifth table sub-attribute - Must Be Complete
          )
    LEFT OUTER JOIN UTL_CODE_TABLE_GENERIC CrsTbl
       ON (
            CrsTbl.ISN_UTL_CODE_TABLE = UTL_CODE_TABLE.ISN_UTL_CODE_TABLE
            AND CrsTbl.cnxarraycolumn =             7-- Eighth table sub-attribute - Course Id
          )
    WHERE UTL_CODE_TABLE.TABLE_NAME = 'ORIENTREQ'
        AND UTL_CODE_TABLE.[STATUS] = 'A'
/********************************************************************************************************
    Find list of students who have completed Orientation.
********************************************************************************************************/
IF OBJECT_ID('tempdb.dbo.#CompletedOrientation') IS NOT NULL
    DROP TABLE #CompletedOrientation
CREATE TABLE #CompletedOrientation
(
    StudentId varchar(9) NULL
)
INSERT INTO #CompletedOrientation
(
    StudentId
)
    SELECT
        STDNT_ID
    FROM ST_STDNT_CLS_A
    INNER JOIN #OrientationTbl
        ON (
            #OrientationTbl.CourseId = SUBSTRING(ST_STDNT_CLS_A.CRS_ID,1,LEN(#OrientationTbl.CourseId))
            AND ST_STDNT_CLS_A.EFF_TRM BETWEEN #OrientationTbl.StartTerm AND #OrientationTbl.EndTerm
            AND (#OrientationTbl.MustBeComplete = 'N' OR ST_STDNT_CLS_A.GRADE IS NOT NULL)
        )
    WHERE STDNT_ID IS NOT NULL AND EFF_TRM IS NOT NULL
        AND ST_STDNT_CLS_A.TRNSCTN_TY = 'A'
    GROUP BY STDNT_ID
/********************************************************************************************************
    Retrieve all the hold type values for each application status requirement.
********************************************************************************************************/
/* If temp table already exists, drop it. */
IF OBJECT_ID('tempdb.dbo.#ApplicationHolds') IS NOT NULL
    DROP TABLE #ApplicationHolds
/* Create new temp table to contain the application status requirement and hold types for each. */
CREATE TABLE #ApplicationHolds
(
    AppStatusReq varchar(10) NULL,
    HoldType varchar(2) NULL
)
INSERT INTO #ApplicationHolds
(
     AppStatusReq
    ,HoldType
)
    SELECT
        appStat.CODE
        ,appStatHold.FIELD_VALUE
    FROM UTL_CODE_TABLE appStat
    LEFT OUTER JOIN UTL_CODE_TABLE_GENERIC appStatHold
       ON (
            appStat.ISN_UTL_CODE_TABLE = appStatHold.ISN_UTL_CODE_TABLE
            AND appStatHold.FIELD_VALUE IS NOT NULL
          )
    WHERE (
        appStat.TABLE_NAME = 'DWHOLDS' AND
        appStat.[STATUS] = 'A'
    )
/********************************************************************************************************
    Retrieve the students who have an advising hold.
********************************************************************************************************/
IF OBJECT_ID('tempdb.dbo.#AdvisingHolds') IS NOT NULL
    DROP TABLE #AdvisingHolds
CREATE TABLE #AdvisingHolds
(
    StudentId varchar(9) NULL
)
INSERT INTO #AdvisingHolds
(
     StudentId
)
    SELECT DISTINCT
        STUDENT_ID
    FROM ST_HOLDS_A
    INNER JOIN #ApplicationHolds
       ON (
            #ApplicationHolds.AppStatusReq = 'ADVISING' AND
            #ApplicationHolds.HoldType = HOLD_TY
          )
/********************************************************************************************************
    Retrieve the students who have a transcript hold.
********************************************************************************************************/
IF OBJECT_ID('tempdb.dbo.#TranscriptHolds') IS NOT NULL
    DROP TABLE #TranscriptHolds
CREATE TABLE #TranscriptHolds
(
    StudentId varchar(9) NULL
)
INSERT INTO #TranscriptHolds
(
     StudentId
)
    SELECT DISTINCT
        STUDENT_ID
    FROM ST_HOLDS_A
    INNER JOIN #ApplicationHolds
       ON (
            #ApplicationHolds.AppStatusReq = 'TRANSCRPTS' AND
            #ApplicationHolds.HoldType = HOLD_TY
          )
/********************************************************************************************************
    Retrieve the students who have a residency hold.
********************************************************************************************************/
IF OBJECT_ID('tempdb.dbo.#ResidencyHolds') IS NOT NULL
    DROP TABLE #ResidencyHolds
CREATE TABLE #ResidencyHolds
(
    StudentId varchar(9) NULL
)
INSERT INTO #ResidencyHolds
(
     StudentId
)
    SELECT DISTINCT
        STUDENT_ID
    FROM ST_HOLDS_A
    INNER JOIN #ApplicationHolds
       ON (
            #ApplicationHolds.AppStatusReq = 'RESIDENCY' AND
            #ApplicationHolds.HoldType = HOLD_TY
          )
/********************************************************************************************************
    Retrieve all student holds except the ones from the DWHOLDS table.
********************************************************************************************************/
IF OBJECT_ID('tempdb.dbo.#StudentHolds') IS NOT NULL
    DROP TABLE #StudentHolds
CREATE TABLE #StudentHolds
(
    StudentId varchar(9) NULL
    ,HoldType varchar(2) NULL
    ,StopCCReg int
    ,StopVCReg int
)
INSERT INTO #StudentHolds
(
    StudentId
    ,HoldType
    ,StopCCReg
    ,StopVCReg
)
    SELECT
        STUDENT_ID
        ,HOLD_TY
        ,CASE
            WHEN holdTblAttrCC.FIELD_VALUE IS NULL THEN 0
            WHEN LEFT(holdTblAttrCC.FIELD_VALUE,1) = 'Y' THEN 1
            ELSE 0
            END AS StopCCReg
        ,CASE
            WHEN holdTblAttrVC.FIELD_VALUE IS NULL THEN 0
            WHEN LEFT(holdTblAttrVC.FIELD_VALUE,1) = 'Y' THEN 1
            ELSE 0
            END AS StopVCReg
    FROM ST_HOLDS_A
    LEFT OUTER JOIN #ApplicationHolds
       ON (
            #ApplicationHolds.HoldType = ST_HOLDS_A.HOLD_TY
          )
    LEFT OUTER JOIN UTL_CODE_TABLE holdTbl
        ON (
            holdTbl.TABLE_NAME = 'HOLDS'
            AND holdTbl.CODE = ST_HOLDS_A.HOLD_TY
            AND holdTbl.[STATUS] = 'A'
        )
    LEFT OUTER JOIN UTL_CODE_TABLE_GENERIC holdTblAttrCC
       ON (
            holdTbl.ISN_UTL_CODE_TABLE = holdTblAttrCC.ISN_UTL_CODE_TABLE
            AND holdTblAttrCC.cnxarraycolumn =         0-- First table sub-attribute - Stop Credit Registration
          )
    LEFT OUTER JOIN UTL_CODE_TABLE_GENERIC holdTblAttrVC
       ON (
            holdTbl.ISN_UTL_CODE_TABLE = holdTblAttrVC.ISN_UTL_CODE_TABLE
            AND holdTblAttrVC.cnxarraycolumn =         1-- Second table sub-attribute - Stop PSAV Registration
          )
    WHERE #ApplicationHolds.HoldType IS             NULL-- This gets the studen holds NOT on the DWHOLDS table.
        AND (LEFT(ISNULL(holdTblAttrCC.FIELD_VALUE,'N'),1) = 'Y'        --Hold stops either CC or VC Registration.
            OR LEFT(ISNULL(holdTblAttrVC.FIELD_VALUE,'N'),1) = 'Y')
/********************************************************************************************************
    Retrieve all student holds except the ones from the DWHOLDS table.
********************************************************************************************************/
IF OBJECT_ID('tempdb.dbo.#StudentHoldsReg') IS NOT NULL
    DROP TABLE #StudentHoldsReg
CREATE TABLE #StudentHoldsReg
(
    StudentId varchar(9) NULL
    ,StopCCReg int
    ,StopVCReg int
)
INSERT INTO #StudentHoldsReg
(
    StudentId
    ,StopCCReg
    ,StopVCReg
)
    SELECT
        StudentId
        ,SUM(StopCCReg)
        ,SUM(StopVCReg)
    FROM #StudentHolds
    GROUP BY StudentId
/********************************************************************************************************
    Retrieve the credit/voc registration terms for students.
********************************************************************************************************/
IF OBJECT_ID('tempdb.dbo.#Registration') IS NOT NULL
    DROP TABLE #Registration
CREATE TABLE #Registration
(
    StudentId varchar(9) NULL
    ,Term varchar(6) NULL
)
INSERT INTO #Registration
(
     StudentId
     ,Term
)
    SELECT
        STDNT_ID
        ,EFF_TRM
    FROM ST_STDNT_CLS_A
    WHERE STDNT_ID IS NOT NULL AND EFF_TRM IS NOT NULL AND REF_NUM IS NOT NULL
        AND TRNSCTN_TY != 'D'
        AND CRED_TY IN ('01','02','03','05','06','10','14','15')
    GROUP BY STDNT_ID, EFF_TRM
/********************************************************************************************************
    Retrieve the individual applications and store in a temporary table.
********************************************************************************************************/
/* If temp table already exists, drop it. */
IF OBJECT_ID('tempdb.dbo.#Application') IS NOT NULL
    DROP TABLE #Application
/* Create new temp table to contain the applications. */
CREATE TABLE #Application
(
    StudentId varchar(9) NULL,
    ApplicationTerm varchar(6) NULL,
    ApplicationEntry varchar(15) NULL,
    ApplicationTypeCode varchar(3) NULL,
    BasisAdmission varchar(2) NULL,
    ProgramCode varchar(5) NULL,
    ApplicationDate varchar(8) NULL,
    HSGradDate varchar(8) NULL,
    HomeCampusCode varchar(3) NULL,
    RowNum int NULL
)
/* Credit Initial Application */
INSERT INTO #Application
(
     StudentId
    ,ApplicationTerm
    ,ApplicationEntry
    ,ApplicationTypeCode
    ,BasisAdmission
    ,ProgramCode
    ,ApplicationDate
    ,HSGradDate
    ,HomeCampusCode
    ,RowNum
)
SELECT
    STUDENT_ID
    ,CR_APPL_TERM
    ,'Application'
    ,'CC'    -- Credit Initial Application
    ,BA_CC
    ,PGM_CD
    ,CR_APPL_DT
    ,HS_GRAD_DT
    ,LEFT(CAMP_PREF,1)
    ,RowNum = ROW_NUMBER() OVER (PARTITION BY STUDENT_ID, CR_APPL_TERM ORDER BY LOG_DATE, LOG_TIME)
    FROM ST_STDNT_LOG_A StudentLog
    WHERE (
        StudentLog.STUDENT_ID IS NOT             NULL-- actual student record, not athlete record
        AND StudentLog.PRSPCT_TYPE IS             NULL-- Not a prospect
        AND StudentLog.COLL_CRED_FLG =             1-- has a credit application
        AND StudentLog.CR_APPL_TERM IS NOT         NULL-- has a credit application term
        AND StudentLog.CR_READMT_TERM IS         NULL-- is not a readmit
    )
/* Credit Readmit Application */
INSERT INTO #Application
(
     StudentId
    ,ApplicationTerm
    ,ApplicationEntry
    ,ApplicationTypeCode
    ,BasisAdmission
    ,ProgramCode
    ,ApplicationDate
    ,HSGradDate
    ,HomeCampusCode
    ,RowNum
)
SELECT
    STUDENT_ID
    ,CR_READMT_TERM
    ,'Application'
    ,'CR'    -- Credit Readmit Application
    ,BA_CC
    ,PGM_CD
    ,LOG_DATE
    ,HS_GRAD_DT
    ,LEFT(CAMP_PREF,1)
    ,RowNum = ROW_NUMBER() OVER (PARTITION BY STUDENT_ID, CR_READMT_TERM ORDER BY LOG_DATE, LOG_TIME)
    FROM ST_STDNT_LOG_A StudentLog
    WHERE (
        StudentLog.STUDENT_ID IS NOT             NULL-- actual student record, not athlete record
        AND StudentLog.PRSPCT_TYPE IS             NULL-- Not a prospect
        AND StudentLog.COLL_CRED_FLG =             1-- has a credit application
        AND StudentLog.CR_APPL_TERM IS NOT         NULL-- has a credit application term
        AND StudentLog.CR_READMT_TERM IS NOT     NULL-- is a readmit
    )
/* Vocational Initial Application */
INSERT INTO #Application
(
     StudentId
    ,ApplicationTerm
    ,ApplicationEntry
    ,ApplicationTypeCode
    ,BasisAdmission
    ,ProgramCode
    ,ApplicationDate
    ,HSGradDate
    ,HomeCampusCode
    ,RowNum
)
SELECT
    STUDENT_ID
    ,VC_APPL_TERM
    ,'Application'
    ,'VC'    -- Vocational Initial Application
    ,BA_VC
    ,VOC_PGM_CD
    ,VC_APPL_DT
    ,HS_GRAD_DT
    ,LEFT(CAMP_PREF,1)
    ,RowNum = ROW_NUMBER() OVER (PARTITION BY STUDENT_ID, VC_APPL_TERM ORDER BY LOG_DATE, LOG_TIME)
    FROM ST_STDNT_LOG_A StudentLog
    WHERE (
        StudentLog.STUDENT_ID IS NOT             NULL-- actual student record, not athlete record
        AND StudentLog.PRSPCT_TYPE IS             NULL-- Not a prospect
        AND StudentLog.VOC_FLG =                     1-- has a vocational application
        AND StudentLog.VC_APPL_TERM IS NOT         NULL-- has a vocational application term
        AND StudentLog.VC_READMT_TERM IS         NULL-- is not a readmit
    )
/* Vocational Readmit Application */
INSERT INTO #Application
(
     StudentId
    ,ApplicationTerm
    ,ApplicationEntry
    ,ApplicationTypeCode
    ,BasisAdmission
    ,ProgramCode
    ,ApplicationDate
    ,HSGradDate
    ,HomeCampusCode
    ,RowNum
)
SELECT
    STUDENT_ID
    ,VC_READMT_TERM
    ,'Application'
    ,'VR'    -- Vocational Readmit Application
    ,BA_VC
    ,VOC_PGM_CD
    ,LOG_DATE
    ,HS_GRAD_DT
    ,LEFT(CAMP_PREF,1)
    ,RowNum = ROW_NUMBER() OVER (PARTITION BY STUDENT_ID, VC_READMT_TERM ORDER BY LOG_DATE, LOG_TIME)
    FROM ST_STDNT_LOG_A StudentLog
    WHERE (
        StudentLog.STUDENT_ID IS NOT             NULL-- actual student record, not athlete record
        AND StudentLog.PRSPCT_TYPE IS             NULL-- Not a prospect
        AND StudentLog.VOC_FLG =                     1-- has a vocational application
        AND StudentLog.VC_APPL_TERM IS NOT         NULL-- has a vocational application term
        AND StudentLog.VC_READMT_TERM IS NOT     NULL-- is a readmit
    )
/* Group 2 - Bachelor Objective Application */
INSERT INTO #Application
(
     StudentId
    ,ApplicationTerm
    ,ApplicationEntry
    ,ApplicationTypeCode
    ,BasisAdmission
    ,ProgramCode
    ,ApplicationDate
    ,HomeCampusCode
    ,RowNum
)
SELECT
    STDNT_ID
    ,EFF_TERM
    ,'Objective'
    ,'CC'    -- Credit Initial Application
    ,Stdnt.BA_CC
    ,PGM_ID
    ,StudentObjective.ADD_DATE
    ,LEFT(Stdnt.CAMP_PREF,1)
    ,        SEQ_NUM-- Row
    FROM ST_STDNT_OBJ_AWD_A StudentObjective
    LEFT OUTER JOIN
        (SELECT STUDENT_ID, BA_CC, CAMP_PREF FROM ST_STDNT_A WHERE STUDENT_ID IS NOT NULL) Stdnt
        ON (
            Stdnt.STUDENT_ID = StudentObjective.STDNT_ID
        )
    LEFT OUTER JOIN                         #Application-- To see if it already exists
        ON (
            #Application.StudentId = StudentObjective.STDNT_ID
            AND #Application.ProgramCode = StudentObjective.PGM_ID
            AND #Application.ApplicationTerm = StudentObjective.EFF_TERM
        )
    WHERE StudentObjective.STDNT_ID IS NOT NULL AND StudentObjective.EFF_TERM IS NOT NULL AND StudentObjective.PGM_ID IS NOT NULL
        AND LEFT(StudentObjective.AWD_TYPE,1) = 'B'        -- Bachelor Award Level
        AND StudentObjective.SEQ_NUM =                 1-- Only want first application for this program/term
        AND #Application.StudentId IS                 NULL-- Did not successfully join to existing application table (i.e. does not already exist)
/*******************************************************************************************************
    Now we have the application data in the temp table.
    So we now read that table and retrieve only those with rowNum = 1 (to indicate the first log record)
    and those where the application term is on or after the Integrow implementation term for the college.
********************************************************************************************************/
SELECT
    (#Application.StudentId
        + ApplicationTypeCode
        + CAST(ApplicationTerm AS varchar(6))
        + CAST(ProgramCode AS varchar(5))
        ) AS ApplicationAK
    ,#Application.StudentId
    ,ApplicationTerm
    ,CASE
        WHEN SUBSTRING(BasisAdmission,2,1) = 'R'                        THEN '50READMT'        -- Enrollment Status code is 'R'
        WHEN ISNULL(#ApplicationSource.AppSourceCode,'') = 'TRANSFER'    THEN '40TRNSFR'
        WHEN ISNULL(#ApplicationSource.AppSourceCode,'') = 'DUALENRLD'    THEN '30DUALEN'
        WHEN ApplicationEntry = 'Objective' OR
             ISNULL(#ApplicationSource.AppSourceCode,'') = 'BACHNATIVE' THEN '60BACHNV'
        WHEN LEFT(Program.AWD_TY,1) = 'B' OR
             ISNULL(#ApplicationSource.AppSourceCode,'') = 'BACHTRANS'    THEN '70BACHTR'
        WHEN ISNULL(#ApplicationSource.AppSourceCode,'') = 'FTIC'
             AND #Application.HSGradDate IS NOT NULL
             AND ISDATE(#Application.HSGradDate) = 1
             AND Sess.SessionBeginDate IS NOT NULL
             AND DATEDIFF(day,CAST(#Application.HSGradDate as date),CAST(Sess.SessionBeginDate AS date)) <= 365
                                                                        THEN '10FTICDR'
        WHEN ISNULL(#ApplicationSource.AppSourceCode,'') = 'FTIC'        THEN '20FTICDL'
        ELSE '80OTHERS'
        END AS ApplicationSource
    ,ISNULL(PgmObj.StudentObjectiveAK,'') AS StudentObjectiveAK
    ,CASE
        WHEN ISDATE(ApplicationDate) = 1 THEN  dbo.udf_StringToDate(ApplicationDate)
        WHEN ApplicationDate IS NULL THEN dbo.udf_StringToDate('18000101')
        ELSE dbo.udf_StringToDate('18000102')
        END AS ApplicationDate
    ,CASE
        WHEN ISNULL(Program.GRADUATION_AVAILABLE,'N') = 'N' THEN 0
        WHEN ISNULL(Program.PLACEMENT_REQ,'N') = 'N'        THEN 0
        WHEN ISNULL(EngPlcmnt.STATUS_IND,'U') IN ('R','U') OR
             ISNULL(MatPlcmnt.STATUS_IND,'U') IN ('R','U') OR
             ISNULL(ReaPlcmnt.STATUS_IND,'U') IN ('R','U')
            THEN 1
        ELSE 0
        END AS NeedTesting
    ,CASE
        WHEN ISNULL(Program.GRADUATION_AVAILABLE,'N') = 'N' THEN 0
--        WHEN Sess.SessionEndDate IS NOT NULL
--             AND ISDATE(Sess.SessionEndDate) = 1
--             AND DATEDIFF(day,CAST(Sess.SessionEndDate AS date),CAST(GETDATE() AS date)) > 0    -- Current date after end of term
--            THEN 0
        WHEN #AdvisingHolds.StudentId IS NOT NULL THEN         1-- Has an advising hold
        WHEN #EnrollmentCodeTbl.EnrollmentCode IS NULL THEN     0-- Enrollment code does not require advising
        WHEN #EnrollmentCodeTbl.Exceptions IS NOT             NULL-- Enrollment code program exceptions exist
            AND ISNULL(CHARINDEX(LEFT(#Application.ProgramCode,1), #EnrollmentCodeTbl.Exceptions),0) >     0-- Student's program is an exception
            THEN 0
        /**** TO BE FIXED - Check student academic status code against SOAP enforcement record to see if advising is required ****/
        WHEN ISNULL(AdvTerm.STATUS_IND,'PE') = 'PE' THEN         1-- Either no advising record for term or status = Pending.
        ELSE 0
        END AS NeedAdvising
    ,CASE
        WHEN ISNULL(Program.GRADUATION_AVAILABLE,'N') = 'N' THEN 0
        WHEN #OrientationTbl.StartTerm IS NULL THEN             0-- Orientation table entry does not exist
        WHEN ISNULL(CHARINDEX(#Application.BasisAdmission, #OrientationTbl.BasisOfAdmission),0) = 0 THEN     0-- Student BasisAdm not in Orientation Tbl
        WHEN ISNULL(CHARINDEX(LEFT(#Application.ProgramCode,1), #OrientationTbl.AwardType),0) = 0 THEN     0-- Student Pgm not in Orientation Tbl
        WHEN #OrientationTbl.CourseId IS NULL THEN             0-- Orientation Course does not exist
        WHEN #CompletedOrientation.StudentId IS NULL THEN         1-- Student has not completed Orientation
        ELSE 0
        END AS NeedOrientation
    ,CASE
--        WHEN Sess.SessionEndDate IS NOT NULL
--             AND ISDATE(Sess.SessionEndDate) = 1
--             AND DATEDIFF(day,CAST(Sess.SessionEndDate AS date),CAST(GETDATE() AS date)) > 0    -- Current date after end of term
--            THEN 0
        WHEN #TranscriptHolds.StudentId IS NOT NULL THEN         1-- Has a transcript hold
        ELSE 0
        END AS NeedTranscripts
    ,CASE
--        WHEN Sess.SessionEndDate IS NOT NULL
--             AND ISDATE(Sess.SessionEndDate) = 1
--             AND DATEDIFF(day,CAST(Sess.SessionEndDate AS date),CAST(GETDATE() AS date)) > 0    -- Current date after end of term
--            THEN 0
        WHEN #ResidencyHolds.StudentId IS NOT NULL THEN         1-- Has a residency hold
        ELSE 0
        END AS ResidencyHold
    ,CASE
        WHEN ISNULL(Program.GRADUATION_AVAILABLE,'N') = 'N' THEN 0
--        WHEN Sess.SessionEndDate IS NOT NULL
--             AND ISDATE(Sess.SessionEndDate) = 1
--             AND DATEDIFF(day,CAST(Sess.SessionEndDate AS date),CAST(GETDATE() AS date)) > 0    -- Current date after end of term
--            THEN 0
        WHEN ISNULL(@EdPlanReq,'N') != 'Y' THEN                 0-- Ed Plan registration edit is not enforced
        WHEN EdPlan.TERM IS NOT NULL THEN                         0-- Has an educational plan
        ELSE 1
        END AS NeedEducationalPlan
    ,CASE
--        WHEN Sess.SessionEndDate IS NOT NULL
--             AND ISDATE(Sess.SessionEndDate) = 1
--             AND DATEDIFF(day,CAST(Sess.SessionEndDate AS date),CAST(GETDATE() AS date)) > 0    -- Current date after end of term
--            THEN 0
        WHEN #StudentHoldsReg.StudentId IS NULL THEN             0-- No other holds that stop registration
        WHEN #Application.ApplicationTypeCode IN ('CC','CR')    -- Credit App and holds that stop Credit Registration
            AND #StudentHoldsReg.StopCCReg > 0 THEN 1
        WHEN #Application.ApplicationTypeCode IN ('VC','VR')    -- Voc App and holds that stop Voc Registration
            AND #StudentHoldsReg.StopVCReg > 0 THEN 1
        ELSE 0
        END AS OtherRegistrationHold
    ,0 AS                                     NoAdmissionBarriers-- Defaulted here. Correct value applied later in ETL.
    ,0 AS                                             NotEnrolled-- Defaulted here. Correct value applied later in ETL.
    ,CASE
        WHEN RegInTerm.Term IS NOT NULL THEN                     0-- Enrolled in the application term
        WHEN RegAfterTerm.Term IS NULL THEN                     0-- No Enrollment after the application term
        WHEN CAST(RegAfterTerm.Term AS int) <=
            CAST(#Application.ApplicationTerm AS int) + 10 THEN     1-- Enrolled within a year of application term
        ELSE 0
        END AS EnrolledAfterApplicationTerm
    ,CASE
        WHEN RegInTerm.Term IS NOT NULL THEN                     1-- Enrolled in the application term
        ELSE 0
        END AS EnrolledInApplicationTerm
FROM #Application
/* Get Program Demo Information */
LEFT OUTER JOIN
    (SELECT PGM_CD, EFF_TRM_D, END_TRM, AWD_TY, GRADUATION_AVAILABLE, PLACEMENT_REQ FROM ST_PROGRAMS_A) Program
   ON (
       Program.PGM_CD = #Application.ProgramCode
            AND #Application.ApplicationTerm >= ISNULL(Program.EFF_TRM_D, '999999')
            AND #Application.ApplicationTerm <= ISNULL(Program.END_TRM, '999999')
       )
/* Get Main Session Information for Application Term */
LEFT OUTER JOIN (
        SELECT RTRIM(LTRIM(SUBSTRING(SESSION_KEY,1,6))) AS SessionTerm,
                SESS_BEG_DT AS SessionBeginDate,
                SESS_END_DT AS SessionEndDate
        FROM ST_SESSION_A
        WHERE SESSION_KEY IS NOT NULL AND
            SUBSTRING(SESSION_KEY,7,4) = '1 '  /* Session 1 = Main session */
    ) Sess
    ON (
        Sess.SessionTerm = #Application.ApplicationTerm
    )
/* Get Application Source based on basis of admission value */
LEFT OUTER JOIN #ApplicationSource
   ON (
        LEFT(#Application.BasisAdmission,1) = #ApplicationSource.BasisOfAdmissionCode
    )
/* Determine if student has any advising holds. */
LEFT OUTER JOIN #AdvisingHolds
    ON (
        #AdvisingHolds.StudentId = #Application.StudentId
    )
/* Determine if student's enrollment code (Basis of Admissions) requires advising. */
LEFT OUTER JOIN #EnrollmentCodeTbl
    ON (
        #EnrollmentCodeTbl.EnrollmentCode = #Application.BasisAdmission
    )
/* Find the associated Orientation record based on the application term. */
LEFT OUTER JOIN #OrientationTbl
    ON (
        #Application.ApplicationTerm BETWEEN #OrientationTbl.StartTerm AND #OrientationTbl.EndTerm
    )
/* Determine if this student has completed Orientation. */
LEFT OUTER JOIN #CompletedOrientation
    ON (
        #CompletedOrientation.StudentId = #Application.StudentId
    )
/* Determine if student has any transcript holds. */
LEFT OUTER JOIN #TranscriptHolds
    ON (
        #TranscriptHolds.StudentId = #Application.StudentId
    )
/* Determine if student has any residency holds. */
LEFT OUTER JOIN #ResidencyHolds
    ON (
        #ResidencyHolds.StudentId = #Application.StudentId
    )
/* Determine if student has any other holds that stop registration. */
LEFT OUTER JOIN #StudentHoldsReg
    ON (
        #StudentHoldsReg.StudentId = #Application.StudentId
    )
/* Get English Placement */
LEFT OUTER JOIN ST_STDNT_TEST_DEMO_A EngPlcmnt
   ON (
        EngPlcmnt.STDNT_ID = #Application.StudentId
        AND EngPlcmnt.PLACEMENT_AREA = 'ENG'
       )
/* Get Math Placement */
LEFT OUTER JOIN ST_STDNT_TEST_DEMO_A MatPlcmnt
   ON (
        MatPlcmnt.STDNT_ID = #Application.StudentId
        AND MatPlcmnt.PLACEMENT_AREA = 'MAT'
       )
/* Get Reading Placement */
LEFT OUTER JOIN ST_STDNT_TEST_DEMO_A ReaPlcmnt
   ON (
        ReaPlcmnt.STDNT_ID = #Application.StudentId
        AND ReaPlcmnt.PLACEMENT_AREA = 'REA'
       )
/* Get Advising term record */
LEFT OUTER JOIN
    (SELECT STDNT_ID, APPL_TRM, STATUS_IND
     FROM ST_STDNT_ADV_HIST_A
     WHERE STDNT_ID IS NOT NULL AND APPL_TRM IS NOT NULL) AdvTerm
   ON (
        AdvTerm.STDNT_ID = #Application.StudentId
        AND AdvTerm.APPL_TRM = #Application.ApplicationTerm
       )
/* Get Registration in Application Term */
LEFT OUTER JOIN #Registration RegInTerm
   ON (
        RegInTerm.StudentId = #Application.StudentId
        AND RegInTerm.Term = #Application.ApplicationTerm
       )
/* Get the first term of registration after the application term for a student */
OUTER APPLY (
    SELECT TOP 1
        StudentId
        ,Term
    FROM #Registration
    WHERE #Registration.StudentId = #Application.StudentId
        AND #Registration.Term > #Application.ApplicationTerm
    ORDER BY #Registration.Term
    ) RegAfterTerm
/* Get the first educational plan record after the application term for a student */
OUTER APPLY (
    SELECT TOP 1
        STDNT_ID
        ,TERM
    FROM ST_ED_PLAN_A
    WHERE ST_ED_PLAN_A.STDNT_ID = #Application.StudentId
        AND ST_ED_PLAN_A.TERM >= #Application.ApplicationTerm
    ORDER BY ST_ED_PLAN_A.TERM
    ) EdPlan
/* Find the latest (term and seq num) student objective record for the specific student and program. */
OUTER APPLY (
    SELECT TOP 1
        (STDNT_ID + PGM_ID + EFF_TERM + RIGHT('000' + CAST(SEQ_NUM AS VARCHAR(3)), 3)) AS StudentObjectiveAK
    FROM ST_STDNT_OBJ_AWD_A Obj
    WHERE Obj.STDNT_ID IS NOT NULL AND Obj.PGM_ID IS NOT NULL AND Obj.EFF_TERM IS NOT NULL
    AND Obj.STDNT_ID = #Application.StudentId
    AND Obj.PGM_ID = #Application.ProgramCode
    AND Obj.EFF_TERM <= #Application.ApplicationTerm
    ORDER BY Obj.EFF_TERM DESC, Obj.SEQ_NUM DESC
    ) PgmObj
WHERE RowNum =                         1-- Earliest log record for student,term
    AND ApplicationTerm >=     @ImplTerm-- Integrow implementation term at the college