Stored Procedure - usp_FactStudentTotalFees

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

Name	Data Type	Direction	Description
@IncrementalBeginDtTm	datetime(23, 3)	Input
@IncrementalEndDtTm	datetime(23, 3)	Input

Name	Type
AR_CUSTOMER_A	Table
IT_STUDENT_FEES_A	Table
IT_STUDENT_FEES_A_PAYMENT_GROUP	Table
UTL_CODE_TABLE	Table
UTL_CODE_TABLE_GENERIC	Table

Name	Type
UTL_CODE_TABLE	TABLE
UTL_CODE_TABLE_GENERIC	TABLE
AR_CUSTOMER_A	TABLE
IT_STUDENT_FEES_A	TABLE
IT_STUDENT_FEES_A_PAYMENT_GROUP	TABLE

Object

Property

Value

Object

Property

Value

/****** Object: StoredProcedure [dbo].[usp_FactStudentTotalFees_Select] Script Date: 03/09/2017 16:47:10 ******/
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
/*
Name:            usp_FactStudentTotalFees_Select
Title:            Primary Select for the Total Student Fees
Date:            11/1/2012
System/Project:    Integrated Financials
Description:    This procedure will combine the Student Fees into Payment Categories.
One record per Student Term will be extracted
Revision History:
10/09/2013 Sophia Cowan        Created
*/
CREATE PROC [dbo].[usp_FactStudentTotalFees_Select]
@IncrementalBeginDtTm datetime, /* Date and Time to validate against RowUpdatedOn in Source Tables */
@IncrementalEndDtTm datetime
AS
SET NOCOUNT ON
IF 2=3
    BEGIN
    SELECT
       CAST('AAA' AS VARCHAR(15))  AS StudentTermAK
      ,CAST('0' AS NUMERIC(11,2)) AS TotalFeesAssessed
      ,CAST('0' AS NUMERIC(11,2)) AS TotalFeesPaid
      ,CAST('0' AS NUMERIC(11,2)) AS TotalFeesNotPaid
      ,CAST('0' AS NUMERIC(11,2)) AS TotalFeesExempted
      ,CAST('AAA' AS VARCHAR(20)) AS PaymentSourceAK
     END
/* Check to see if changes on DWCUSTTYPE table,
if so change the Incremental Begin Date to 1900-01-01 for a Full Load */
DECLARE @Cnt Int
DECLARE @TempBeginDtTm datetime = @IncrementalBeginDtTm

SET @IncrementalBeginDtTm =
(
SELECT
  CASE
     WHEN  COUNT(*) = 0 Then @TempBeginDtTm
     ELSE '1900-01-01'
     END as IncrementalBeginDtTm
  FROM UTL_CODE_TABLE UtlCode

WHERE (UtlCode.ISN_UTL_CODE_TABLE) IN
(
   /* Select changed records from GL Trans File */
   SELECT ISN_UTL_CODE_TABLE
   FROM UTL_CODE_TABLE
   WHERE
     (
       UTL_CODE_TABLE.RowUpdatedOn >= @IncrementalBeginDtTm
       AND UTL_CODE_TABLE.RowUpdatedOn <= @IncrementalEndDtTm
       AND UTL_CODE_TABLE.TABLE_NAME = 'DWCUSTTYPE'   )
     )
)
CREATE TABLE #FeePayments
(
     StudentTerm Varchar(15) NOT NULL
    ,FeesPaidByFinAid NUMERIC(11,2) NULL
    ,FeesPaidByStudentTuitionPlan NUMERIC(11,2) NULL
    ,FeesPaidByThirdParty NUMERIC(11,2) NULL
    ,FeesPaidByPell NUMERIC(11,2) NULL
    ,FeesPaidByLoans NUMERIC(11,2) NULL
    ,FeesPaidByFederalGrants NUMERIC(11,2) NULL
    ,FeesPaidByStateGrants NUMERIC(11,2) NULL
    ,FeesPaidByInstitution NUMERIC(11,2) NULL
    ,FeesPaidByFoundation NUMERIC(11,2) NULL
    ,FeesPaidByTuitionPlan NUMERIC(11,2) NULL
    ,FeesPaidByDeferments NUMERIC(11,2) NULL
    ,CustomerCategory CHAR(1)NULL
  )
CREATE TABLE #FeePaymentsSummary
(
     StudentTerm Varchar(15) NOT NULL
    ,FeesPaidByFinAid NUMERIC(11,2) NULL
    ,FeesPaidByStudentTuitionPlan NUMERIC(11,2) NULL
    ,FeesPaidByThirdParty NUMERIC(11,2) NULL
    ,FeesPaidByPell NUMERIC(11,2) NULL
    ,FeesPaidByLoans NUMERIC(11,2) NULL
    ,FeesPaidByFederalGrants NUMERIC(11,2) NULL
    ,FeesPaidByStateGrants NUMERIC(11,2) NULL
    ,FeesPaidByInstitution NUMERIC(11,2) NULL
    ,FeesPaidByFoundation NUMERIC(11,2) NULL
    ,FeesPaidByTuitionPlan NUMERIC(11,2) NULL
    ,FeesPaidByDeferments NUMERIC(11,2) NULL
  )

CREATE TABLE #TotalFeePayments
(
     StudentTerm Varchar(15) NOT NULL
    ,TotalFeesAssessed NUMERIC(11,2) NULL
    ,TotalFeesPaid NUMERIC(11,2) NULL
    ,TotalFeesNotPaid NUMERIC(11,2) NULL
    ,TotalFeesExempted NUMERIC(11,2) NULL
    ,TotalFeesRefunded NUMERIC(11,2) NULL
    ,TotalFeesStudentPaidCash NUMERIC(11,2) NULL
)
/* Insert into #FeePayments based on Customer Category from CONTRACT_NBR */

INSERT INTO #FeePayments
(
     StudentTerm
    ,FeesPaidByFinAid
    ,FeesPaidByStudentTuitionPlan
    ,FeesPaidByThirdParty
    ,FeesPaidByPell
    ,FeesPaidByLoans
    ,FeesPaidByFederalGrants
    ,FeesPaidByStateGrants
    ,FeesPaidByInstitution
    ,FeesPaidByFoundation
    ,FeesPaidByTuitionPlan
    ,FeesPaidByDeferments
    ,CustomerCategory
  )
SELECT
      STUDENT_TERM
      ,CASE
         WHEN CustCat.FIELD_VALUE = 'P' THEN SUM(ISNULL(FEE_AMOUNT,0))
         WHEN CustCat.FIELD_VALUE = 'L' THEN SUM(ISNULL(FEE_AMOUNT,0))
         WHEN CustCat.FIELD_VALUE = 'G' THEN SUM(ISNULL(FEE_AMOUNT,0))
         WHEN CustCat.FIELD_VALUE = 'F' THEN SUM(ISNULL(FEE_AMOUNT,0))
         WHEN CustCat.FIELD_VALUE = 'I' THEN SUM(ISNULL(FEE_AMOUNT,0))
         WHEN CustCat.FIELD_VALUE = 'N' THEN SUM(ISNULL(FEE_AMOUNT,0))
         ELSE 0
         END
         AS FinAidFeesPaid
      ,CASE
         WHEN CustCat.FIELD_VALUE = 'S' THEN SUM(ISNULL(FEE_AMOUNT,0))
         WHEN CustCat.FIELD_VALUE = 'T' THEN SUM(ISNULL(FEE_AMOUNT,0))
         ELSE 0
         END
         AS StudentTuitionPlanFeesPaid
       ,CASE
         WHEN CustCat.FIELD_VALUE = 'R' THEN SUM(ISNULL(FEE_AMOUNT,0))
         ELSE 0
         END
         AS ThirdPartyFeesPaid
       ,CASE
         WHEN CustCat.FIELD_VALUE = 'P' THEN SUM(ISNULL(FEE_AMOUNT,0))
         ELSE 0
         END
         AS PellFeesPaid
       ,CASE
         WHEN CustCat.FIELD_VALUE = 'L' THEN SUM(ISNULL(FEE_AMOUNT,0))
         ELSE 0
         END
         AS LoanFeesPaid
       ,CASE
         WHEN CustCat.FIELD_VALUE = 'G' THEN SUM(ISNULL(FEE_AMOUNT,0))
         ELSE 0
         END
         AS FederalGrantFeesPaid
       ,CASE
         WHEN CustCat.FIELD_VALUE = 'F' THEN SUM(ISNULL(FEE_AMOUNT,0))
         ELSE 0
         END
         AS StateGrantFeesPaid
       ,CASE
         WHEN CustCat.FIELD_VALUE = 'I' THEN SUM(ISNULL(FEE_AMOUNT,0))
         ELSE 0
         END
         AS InstitutionalFeesPaid
       ,CASE
         WHEN CustCat.FIELD_VALUE = 'N' THEN SUM(ISNULL(FEE_AMOUNT,0))
         ELSE 0
         END
         AS FoundationFeesPaid
       ,CASE
         WHEN CustCat.FIELD_VALUE = 'T' THEN SUM(ISNULL(FEE_AMOUNT,0))
         ELSE 0
         END
         AS TuitionPlanFeesPaid
       ,CASE
         WHEN CustCat.FIELD_VALUE = 'S' THEN SUM(ISNULL(FEE_AMOUNT,0))
         ELSE 0
         END
         AS DefermentsFeesPaid
      ,CustCat.FIELD_VALUE AS CustomerCategory
  FROM IT_STUDENT_FEES_A_PAYMENT_GROUP Paym
  LEFT OUTER JOIN AR_CUSTOMER_A Cust
  ON
  (
     Cust.CUSTOMER_ID = LEFT(CONTRACT_NBR,9)

  )
  LEFT OUTER JOIN UTL_CODE_TABLE  CustType
  ON
  (
     CustType.TABLE_NAME = 'DWCUSTTYPE'
     AND CustType.STATUS = 'A'
     AND CustType.CODE = Cust.CUSTOMER_TYPE
  )
  LEFT OUTER JOIN UTL_CODE_TABLE_GENERIC CustCat
  ON
  (
     CustType.ISN_UTL_CODE_TABLE = CustCat.ISN_UTL_CODE_TABLE
     AND
     CustCat.cnxarraycolumn = 0
   )
   WHERE Paym.STUDENT_TERM IN
(
/* Select changed records from IT_STUDENT_FEES_A_PAYMENT_GROUP File */
SELECT STUDENT_TERM
FROM IT_STUDENT_FEES_A_PAYMENT_GROUP
WHERE (
       IT_STUDENT_FEES_A_PAYMENT_GROUP.RowUpdatedOn >= @IncrementalBeginDtTm
       AND IT_STUDENT_FEES_A_PAYMENT_GROUP.RowUpdatedOn <= @IncrementalEndDtTm
       AND IT_STUDENT_FEES_A_PAYMENT_GROUP.PAYMENT_SEQ IS NOT NULL
       AND IT_STUDENT_FEES_A_PAYMENT_GROUP.FEE_AMOUNT IS NOT NULL
      )
)
AND Paym.PAYMENT_SEQ IS NOT NULL and Paym.CONTRACT_NBR IS NOT NULL
  AND LEFT(Paym.STUDENT_TERM,9) <> LEFT(Paym.CONTRACT_NBR,9)
GROUP BY Paym.STUDENT_TERM, FIELD_VALUE

/* Insert into #FeePayments if Customer = StduentID */
INSERT INTO #FeePayments
(
     StudentTerm
    ,FeesPaidByFinAid
    ,FeesPaidByStudentTuitionPlan
    ,FeesPaidByThirdParty
    ,FeesPaidByPell
    ,FeesPaidByLoans
    ,FeesPaidByFederalGrants
    ,FeesPaidByStateGrants
    ,FeesPaidByInstitution
    ,FeesPaidByFoundation
    ,FeesPaidByTuitionPlan
    ,FeesPaidByDeferments
    ,CustomerCategory
  )
SELECT
      STUDENT_TERM
     ,0 AS FeesPaidByFinAid
     ,0 AS FeesPaidByStudentTuitionPlan
     ,0 AS FeesPaidByThirdParty
     ,0 AS FeesPaidByPell
     ,0 AS FeesPaidByLoans
     ,0 AS FeesPaidByFederalGrants
     ,0 AS FeesPaidByStateGrants
     ,0 AS FeesPaidByInstitution
     ,0 AS FeesPaidByFoundation
     ,0 AS FeesPaidByTuitionPlan
     ,SUM(ISNULL(FEE_AMOUNT,0)) AS DefermentsFeesPaid
     ,'S' AS CustomerCategory
  FROM IT_STUDENT_FEES_A_PAYMENT_GROUP Paym
   WHERE Paym.STUDENT_TERM IN
(
/* Select changed records from IT_STUDENT_FEES_A_PAYMENT_GROUP File */
SELECT STUDENT_TERM
FROM IT_STUDENT_FEES_A_PAYMENT_GROUP
WHERE (
       IT_STUDENT_FEES_A_PAYMENT_GROUP.RowUpdatedOn >= @IncrementalBeginDtTm
       AND IT_STUDENT_FEES_A_PAYMENT_GROUP.RowUpdatedOn <= @IncrementalEndDtTm
       AND IT_STUDENT_FEES_A_PAYMENT_GROUP.PAYMENT_SEQ IS NOT NULL
       AND IT_STUDENT_FEES_A_PAYMENT_GROUP.FEE_AMOUNT IS NOT NULL
      )
)
AND Paym.PAYMENT_SEQ IS NOT NULL and Paym.CONTRACT_NBR IS NOT NULL
  AND LEFT(Paym.STUDENT_TERM,9) = LEFT(Paym.CONTRACT_NBR,9)
GROUP BY Paym.STUDENT_TERM

/* Calculate TotalFeePayments from IT_STUDENT_FEES_A */
INSERT INTO #TotalFeePayments
(
     StudentTerm
    ,TotalFeesAssessed
    ,TotalFeesPaid
    ,TotalFeesNotPaid
    ,TotalFeesExempted
    ,TotalFeesRefunded
    ,TotalFeesStudentPaidCash
)

SELECT
       TotalFees.STUDENT_TERM
      ,(SUM(ISNULL(TotalFees.FEE_ASSESS_AMOUNT,0)) - SUM(ISNULL(TotalFees.EXMPT_AMOUNT,0))) AS TotalFeesAssessed
      ,(SUM(ISNULL(TotalFees.COVERAGE_AMOUNT,0)) + SUM(ISNULL(TotalFees.PAYMENT_AMOUNT,0))) AS TotalFeesPaid
      ,SUM(ISNULL(TotalFees.DUE_AMOUNT,0)) AS TotalFeesNotPaid
      ,SUM(ISNULL(TotalFees.EXMPT_AMOUNT,0)) AS TotalFeesExempted
      ,SUM(ISNULL(TotalFees.RFND_AMOUNT,0)) AS TotalFeesRefunded
      ,SUM(ISNULL(TotalFees.PAYMENT_AMOUNT,0)) AS TotalFeesStudentPaidCash
  FROM IT_STUDENT_FEES_A TotalFees
  WHERE TotalFees.STUDENT_TERM IN
(
/* Select changed records from IT_STUDENT_FEES_A_PAYMENT_GROUP File */
SELECT STUDENT_TERM
FROM IT_STUDENT_FEES_A
WHERE (
       IT_STUDENT_FEES_A.RowUpdatedOn >= @IncrementalBeginDtTm
       AND IT_STUDENT_FEES_A.RowUpdatedOn <= @IncrementalEndDtTm
      )
)
  GROUP BY TotalFees.STUDENT_TERM
/* Sum the FeePayments and Create Summary records *//

INSERT INTO #FeePaymentsSummary
(
     StudentTerm
    ,FeesPaidByFinAid
    ,FeesPaidByStudentTuitionPlan
    ,FeesPaidByThirdParty
    ,FeesPaidByPell
    ,FeesPaidByLoans
    ,FeesPaidByFederalGrants
    ,FeesPaidByStateGrants
    ,FeesPaidByInstitution
    ,FeesPaidByFoundation
    ,FeesPaidByTuitionPlan
    ,FeesPaidByDeferments
  )

SELECT
     StudentTerm
    ,SUM(ISNULL(FeesPaidByFinAid,0))
    ,SUM(ISNULL(FeesPaidByStudentTuitionPlan,0))
    ,SUM(ISNULL(FeesPaidByThirdParty,0))
    ,SUM(ISNULL(FeesPaidByPell,0))
    ,SUM(ISNULL(FeesPaidByLoans,0))
    ,SUM(ISNULL(FeesPaidByFederalGrants,0))
    ,SUM(ISNULL(FeesPaidByStateGrants,0))
    ,SUM(ISNULL(FeesPaidByInstitution,0))
    ,SUM(ISNULL(FeesPaidByFoundation,0))
    ,SUM(ISNULL(FeesPaidByTuitionPlan,0))
    ,SUM(ISNULL(FeesPaidByDeferments,0))
    FROM #FeePayments
    GROUP BY StudentTerm

SELECT
     #TotalFeePayments.StudentTerm AS StudentTermAK
    ,TotalFeesAssessed
    ,TotalFeesPaid
    ,TotalFeesNotPaid
    ,TotalFeesExempted
    --,TotalFeesRefunded
    --,TotalFeesStudentPaidCash
    ,CASE
        WHEN TotalFeesAssessed = 0 AND TotalFeesExempted > 0 THEN '6EX 1EXMPT'
        WHEN TotalFeesAssessed > 0 AND TotalFeesAssessed = TotalFeesNotPaid THEN '5NP 2NOTP'
        WHEN TotalFeesAssessed > 0 AND TotalFeesAssessed = FeesPaidByPell THEN '1FA 1PELL'
        WHEN TotalFeesAssessed > 0 AND TotalFeesAssessed = FeesPaidByLoans THEN '1FA 2LOAN'
        WHEN TotalFeesAssessed > 0 AND TotalFeesAssessed = FeesPaidByFederalGrants THEN '1FA 3OTHF'
        WHEN TotalFeesAssessed > 0 AND TotalFeesAssessed = FeesPaidByStateGrants THEN '1FA 4STATE'
        WHEN TotalFeesAssessed > 0 AND TotalFeesAssessed = FeesPaidByInstitution THEN '1FA 5INSTS'
        WHEN TotalFeesAssessed > 0 AND TotalFeesAssessed = FeesPaidByFoundation THEN '1FA 6FOUND'
        WHEN TotalFeesAssessed > 0 AND TotalFeesAssessed = TotalFeesStudentPaidCash THEN '2ST 1STPAID'
        WHEN TotalFeesAssessed > 0 AND TotalFeesAssessed = FeesPaidByTuitionPlan THEN '2ST 2TPP'
        WHEN TotalFeesAssessed > 0 AND TotalFeesAssessed = FeesPaidByDeferments THEN '2ST 3DEFER'
        WHEN TotalFeesAssessed > 0 AND TotalFeesAssessed = FeesPaidByThirdParty THEN '3TP 1THIRDP'
        WHEN (FeesPaidByPell + FeesPaidByLoans + FeesPaidByFederalGrants + FeesPaidByStateGrants +
              FeesPaidByInstitution + FeesPaidByFoundation) = TotalFeesAssessed THEN '1FA 7FACOM'
        WHEN (TotalFeesStudentPaidCash + FeesPaidByTuitionPlan + FeesPaidByDeferments) = TotalFeesAssessed THEN '2ST 4STCOM'
        WHEN TotalFeesNotPaid > 0 AND TotalFeesNotPaid < TotalFeesAssessed THEN '5NP 1PARTP'
        WHEN TotalFeesAssessed > 0 AND (FeesPaidByPell + FeesPaidByLoans + FeesPaidByFederalGrants + FeesPaidByStateGrants +
              FeesPaidByInstitution + FeesPaidByFoundation) > 0 AND
             (FeesPaidByDeferments + FeesPaidByTuitionPlan + FeesPaidByThirdParty) > 0
               THEN '4CO 1COPAY'
        WHEN TotalFeesAssessed > 0 AND TotalFeesStudentPaidCash > 0
            AND FeesPaidByThirdParty > 0
               THEN '4CO 1COPAY'
        WHEN TotalFeesAssessed > 0 AND (FeesPaidByPell + FeesPaidByLoans + FeesPaidByFederalGrants + FeesPaidByStateGrants +
              FeesPaidByInstitution + FeesPaidByFoundation) > 0 AND FeesPaidByThirdParty > 0
               THEN '4CO 1COPAY'
        WHEN TotalFeesAssessed > 0 AND (FeesPaidByPell + FeesPaidByLoans + FeesPaidByFederalGrants + FeesPaidByStateGrants +
              FeesPaidByInstitution + FeesPaidByFoundation) > 0 AND
             (TotalFeesStudentPaidCash > 0 OR FeesPaidByDeferments > 0 OR FeesPaidByTuitionPlan > 0)
               THEN '4CO 1COPAY'
        WHEN TotalFeesAssessed > 0 AND FeesPaidByThirdParty > 0 AND
             (TotalFeesStudentPaidCash > 0 OR FeesPaidByDeferments > 0 OR FeesPaidByTuitionPlan > 0)
               THEN '4CO 1COPAY'
        ELSE   'N/A'
      END AS PaymentSourceAK
      --,FeesPaidByPell
      --,FeesPaidByLoans
      --,FeesPaidByFinAid
      --,FeesPaidByFederalGrants
      --,FeesPaidByStateGrants
      --,FeesPaidByDeferments
      --,FeesPaidByFoundation
      --,FeesPaidByInstitution
      --,FeesPaidByThirdParty
      --,FeesPaidByTuitionPlan
    FROM #TotalFeePayments

LEFT OUTER JOIN #FeePaymentsSummary
ON
(
  #FeePaymentsSummary.StudentTerm = #TotalFeePayments.StudentTerm
)
WHERE TotalFeesAssessed > 0
   OR TotalFeesExempted > 0
   OR TotalFeesPaid > 0
   OR TotalFeesNotPaid > 0