Stored Procedure - usp_DimApplication

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

Name

Data Type

Direction

Description

Name	Type
FAC192_SITE_A	Table
ST_PROGRAMS_A	Table
ST_STDNT_A	Table
ST_STDNT_CLS_A	Table
ST_STDNT_LOG_A	Table
ST_STDNT_OBJ_AWD_A	Table
ST_TERM_A	Table
UTL_CODE_TABLE	Table
UTL_CODE_TABLE_GENERIC	Table

Name	Type
UTL_CODE_TABLE	TABLE
UTL_CODE_TABLE_GENERIC	TABLE
FAC192_SITE_A	TABLE
ST_PROGRAMS_A	TABLE
ST_STDNT_A	TABLE
ST_STDNT_CLS_A	TABLE
ST_STDNT_LOG_A	TABLE
ST_STDNT_OBJ_AWD_A	TABLE
ST_TERM_A	TABLE
udf_StringToDate	USERDEFINEDFUNCTION

Object

Property

Value

Object

Property

Value

/****** Object: StoredProcedure [dbo].[usp_DimApplication_Select] Script Date: 03/09/2017 16:47:10 ******/
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
/*
Name:            usp_DimApplication_Select
Title:            Primary Select for the Student Application Dimension
System/Project:    Admissions Dashboard
Description:    This procedure will retrieve the credit initial, credit readmit,
                vocational initial, and vocational readmit application data
                from the student log file in DWStaging as part of the student
                application dimension of the Data Warehouse.
Revision History:
08/05/2014 Ian Peterson    Created.
*/
/* Primary Student Source SELECT Statement including Incremental Modifications*/
CREATE PROC [dbo].[usp_DimApplication_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(30)) AS ApplicationTermTitle
    ,CAST('aaa' AS varchar(15)) AS ApplicationEntry
    ,CAST('aaa' AS varchar(3))    AS ApplicationTypeCode
    ,CAST('aaa' AS varchar(60)) AS ApplicationType
    ,CAST('aaa' AS varchar(15)) AS CreditVocationalBachelorApplication
    ,CAST('aaa' AS varchar(15)) AS InitialOrReadmitApplication
    ,CAST('aaa' AS varchar(3))    AS BasisOfAdmissionCode
    ,CAST('aaa' AS varchar(30)) AS BasisOfAdmission
    ,CAST('aaa' AS varchar(3))    AS EnrollmentStatusCode
    ,CAST('aaa' AS varchar(30)) AS EnrollmentStatus
    ,CAST('aaa' AS varchar(3))    AS FirstTimeInCollege
    ,CAST('aaa' AS varchar(3))    AS BachelorApplicant
    ,CAST('aaa' AS varchar(5))    AS ProgramCode
    ,CAST('aaa' AS varchar(30)) AS Program
    ,CAST('aaa' AS varchar(3))    AS AwardTypeCode
    ,CAST('aaa' AS varchar(30)) AS AwardType
    ,CAST(GETDATE() AS date)    AS ApplicationDate
    ,CAST('aaa' AS varchar(6))    AS FirstTermEnrolled
    ,CAST('aaa' AS varchar(30)) AS FirstTermEnrolledTitle
    ,CAST('aaa' AS varchar(3))    AS HomeCampusCode
    ,CAST('aaa' AS varchar(50)) AS HomeCampus
    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'
)
/********************************************************************************************************
    Retrieve all the basis of admission values for First Time in College.
********************************************************************************************************/
/* If temp table already exists, drop it. */
IF OBJECT_ID('tempdb.dbo.#FticTbl') IS NOT NULL
    DROP TABLE #FticTbl
/* Create new temp table to contain the application sources and basis of admissions for each. */
CREATE TABLE #FticTbl
(
    AppSourceCode varchar(10) NULL,
    BasisOfAdmissionCode varchar(1) NULL
)
INSERT INTO #FticTbl
(
     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.CODE = 'FTIC' AND
        appSrc.[STATUS] = 'A'
    )
/********************************************************************************************************
    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,
    HomeCampusCode varchar(3) NULL,
    RowNum int NULL
)
/* Credit Initial Application */
INSERT INTO #Application
(
     StudentId
    ,ApplicationTerm
    ,ApplicationEntry
    ,ApplicationTypeCode
    ,BasisAdmission
    ,ProgramCode
    ,ApplicationDate
    ,HomeCampusCode
    ,RowNum
)
SELECT
    STUDENT_ID
    ,CR_APPL_TERM
    ,'Application'
    ,'CC'    -- Credit Initial Application
    ,BA_CC
    ,PGM_CD
    ,CR_APPL_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
    ,HomeCampusCode
    ,RowNum
)
SELECT
    STUDENT_ID
    ,CR_READMT_TERM
    ,'Application'
    ,'CR'    -- Credit Readmit Application
    ,BA_CC
    ,PGM_CD
    ,LOG_DATE
    ,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
    ,HomeCampusCode
    ,RowNum
)
SELECT
    STUDENT_ID
    ,VC_APPL_TERM
    ,'Application'
    ,'VC'    -- Vocational Initial Application
    ,BA_VC
    ,VOC_PGM_CD
    ,VC_APPL_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
    ,HomeCampusCode
    ,RowNum
)
SELECT
    STUDENT_ID
    ,VC_READMT_TERM
    ,'Application'
    ,'VR'    -- Vocational Readmit Application
    ,BA_VC
    ,VOC_PGM_CD
    ,LOG_DATE
    ,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
    ,LTRIM(RTRIM(ISNULL(AppTerm.TRM_TTL, 'N/A'))) AS ApplicationTermTitle
    ,ApplicationEntry
    ,ApplicationTypeCode
    ,CASE
        WHEN ApplicationTypeCode = 'CC' THEN 'College Credit Application'
        WHEN ApplicationTypeCode = 'CR' THEN 'College Credit Readmit Application'
        WHEN ApplicationTypeCode = 'VC' THEN 'Vocational Application'
        WHEN ApplicationTypeCode = 'VR' THEN 'Vocational Readmit Application'
        ELSE 'N/A'
        END AS ApplicationType
    ,CASE
        WHEN ApplicationTypeCode IN ('CC','CR') AND LEFT(ISNULL(Program.AWD_TY,' '),1) = 'B' THEN 'Bachelor'
        WHEN ApplicationTypeCode IN ('CC','CR') THEN 'Credit'
        WHEN ApplicationTypeCode IN ('VC','VR') THEN 'Vocational'
        ELSE 'N/A'
        END AS CreditVocationalBachelorApplication
    ,CASE
        WHEN ApplicationTypeCode IN ('CC','VC') THEN 'Initial'
        WHEN ApplicationTypeCode IN ('CR','VR') THEN 'Readmit'
        ELSE 'N/A'
        END AS InitialOrReadmitApplication
    ,LEFT(BasisAdmission,1) AS             BasisOfAdmissionCode-- First char of source Basis of Admission
    ,LTRIM(RTRIM(ISNULL(BasisAdm.DESCRIPTION, 'N/A'))) AS BasisOfAdmission
    ,SUBSTRING(BasisAdmission,2,1) AS     EnrollmentStatusCode-- Second char of source Basis of Admission
    ,LTRIM(RTRIM(ISNULL(EnrollStat.DESCRIPTION, 'N/A'))) AS EnrollmentStatus
    ,CASE
        WHEN #FticTbl.AppSourceCode IS NOT NULL THEN 'Yes'    -- Successful join to DWAPPSOURC/FTIC table using basis of admission value
        ELSE 'No'
        END AS FirstTimeInCollege
    ,CASE
        WHEN LEFT(Program.AWD_TY,1) = 'B' THEN 'Yes'
        ELSE 'No'
        END AS BachelorApplicant
    ,ProgramCode
    ,CAST(CASE
        WHEN Program.PGM_SHRT_TTL IS NULL THEN 'N/A'
        WHEN SUBSTRING(Program.PGM_SHRT_TTL,1,30) <> '' THEN LTRIM(RTRIM(SUBSTRING(Program.PGM_SHRT_TTL,1,30)))
        ELSE LTRIM(RTRIM(SUBSTRING(Program.PGM_SHRT_TTL,31,30)))
        END
        AS varchar(30)) AS Program
    ,LTRIM(RTRIM(ISNULL(Program.AWD_TY, 'N/A'))) AS AwardTypeCode
    ,LTRIM(RTRIM(ISNULL(Award.DESCRIPTION, 'N/A'))) AS AwardType
    ,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
    ,ISNULL(RegTerm.Term,'N/A') AS FirstTermEnrolled
    ,LTRIM(RTRIM(ISNULL(EnrTerm.TRM_TTL,'N/A'))) AS FirstTermEnrolledTitle
    ,LTRIM(RTRIM(ISNULL(HomeCampusCode, 'N/A'))) AS HomeCampusCode
    ,LTRIM(RTRIM(ISNULL(site.SITE_NAME, 'N/A'))) AS HomeCampus
FROM #Application
/* Get Application Term Information */
LEFT OUTER JOIN ST_TERM_A AppTerm
   ON (
       AppTerm.TRM_YR = #Application.ApplicationTerm
      )
/* Get Basis of Admission code description */
LEFT OUTER JOIN UTL_CODE_TABLE BasisAdm
   ON (
       BasisAdm.TABLE_NAME = 'BASIS-ADM'
            AND BasisAdm.CODE = LEFT(#Application.BasisAdmission,1)
            AND BasisAdm.STATUS = 'A'
       )
/* Get Enrollment Status code description */
LEFT OUTER JOIN UTL_CODE_TABLE EnrollStat
   ON (
       EnrollStat.TABLE_NAME = 'ENRL-STAT'
            AND EnrollStat.CODE = SUBSTRING(#Application.BasisAdmission,2,1)
            AND EnrollStat.STATUS = 'A'
       )
/* Get FTIC based on basis of admission value */
LEFT OUTER JOIN #FticTbl
   ON (
        LEFT(#Application.BasisAdmission,1) = #FticTbl.BasisOfAdmissionCode
    )
/* Get Program Demo Information */
LEFT OUTER JOIN 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 Award Type code description */
LEFT OUTER JOIN UTL_CODE_TABLE Award
   ON (
       Award.TABLE_NAME = 'AWARD-TYPE'
            AND Award.CODE = Program.AWD_TY
            AND Award.STATUS = 'A'
       )
/* Determine Home Campus Site Physical *//
LEFT OUTER JOIN FAC192_SITE_A sitelocal
   ON (
       sitelocal.SITE_LOCAL_NUM = #Application.HomeCampusCode
       AND sitelocal.REC_TYPE = 'S'
       )
/* Get Home Campus - Site Name *//
LEFT OUTER JOIN FAC192_SITE_A site
   ON (
       site.SITE_NUM = sitelocal.SITE_NUM
       AND site.REC_TYPE = 'I'
       )
/* Get the first term of registration >= 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
    ) RegTerm
/* Get Enrolled Term Information */
LEFT OUTER JOIN ST_TERM_A EnrTerm
   ON (
       EnrTerm.TRM_YR = RegTerm.Term
      )
WHERE RowNum =                         1-- Earliest log record for student,term
    AND ApplicationTerm >=     @ImplTerm-- Integrow implementation term at the college