Stored Procedure - usp_FactStudentTotalFeesForClass

Property	Value
Name	usp_FactStudentTotalFeesForClass_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_FactStudentTotalFeesForClass_Select] Script Date: 03/09/2017 16:47:10 ******/
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
/*
Name:            usp_FactStudentTotalFeesForClass_Select
Title:            Primary Select for the Total Student Fees
Date:            11/1/2012
System/Project:    Integrated Financials
Description:    This procedure will combine the Student Class Fees into Payment Categories.
One record per Student Term per Reference Number will be extracted
Revision History:
10/09/2013 Sophia Cowan        Created
*/
CREATE PROC [dbo].[usp_FactStudentTotalFeesForClass_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 INT) AS ReferenceNumberAK
      ,CAST('0' AS NUMERIC(11,2)) AS ClassTotalFeesAssessed
      ,CAST('0' AS NUMERIC(11,2)) AS ClassTotalFeesPaid
      ,CAST('0' AS NUMERIC(11,2)) AS ClassTotalFeesNotPaid
      ,CAST('0' AS NUMERIC(11,2)) AS ClassTotalFeesExempted
      ,CAST('AAA' AS VARCHAR(20)) AS ClassPaymentSourceAK
     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 #ClassFeePayments
(
     StudentTerm Varchar(15) NOT NULL
    ,ReferenceNumber int 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 #ClassFeePaymentsSummary
(
     StudentTerm Varchar(15) NOT NULL
    ,ReferenceNumber int 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 #ClassTotalFeePayments
(
     StudentTerm Varchar(15) NOT NULL
    ,ReferenceNumber int NOT NULL
    ,ClassTotalFeesAssessed NUMERIC(11,2) NULL
    ,ClassTotalFeesPaid NUMERIC(11,2) NULL
    ,ClassTotalFeesNotPaid NUMERIC(11,2) NULL
    ,ClassTotalFeesExempted NUMERIC(11,2) NULL
    ,ClassTotalFeesRefunded NUMERIC(11,2) NULL
    ,ClassTotalFeesStudentPaidCash NUMERIC(11,2) NULL
)
/* Insert into #ClassFeePayments based on Customer Category from CONTRACT_NBR */

INSERT INTO #ClassFeePayments
(
     StudentTerm
    ,ReferenceNumber
    ,FeesPaidByFinAid
    ,FeesPaidByStudentTuitionPlan
    ,FeesPaidByThirdParty
    ,FeesPaidByPell
    ,FeesPaidByLoans
    ,FeesPaidByFederalGrants
    ,FeesPaidByStateGrants
    ,FeesPaidByInstitution
    ,FeesPaidByFoundation
    ,FeesPaidByTuitionPlan
    ,FeesPaidByDeferments
    ,CustomerCategory
  )
SELECT
      Paym.STUDENT_TERM
      ,CAST(RIGHT(Paym.CRS_ID_XREF,6) AS Int) AS ReferenceNumber
      ,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 Fees
  INNER JOIN IT_STUDENT_FEES_A_PAYMENT_GROUP Paym
  ON
  (
      Fees.ISN_IT_STUDENT_FEES_A = Paym.ISN_IT_STUDENT_FEES_A
      AND Paym.PAYMENT_SEQ IS NOT NULL
      AND Paym.FEE_AMOUNT IS NOT NULL
      AND Paym.CONTRACT_NBR IS NOT NULL
      AND LEFT(Paym.STUDENT_TERM,9) <> LEFT(Paym.CONTRACT_NBR,9)
  )
  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 STUDENT_TERM
FROM IT_STUDENT_FEES_A
WHERE (
       IT_STUDENT_FEES_A.RowUpdatedOn >= @IncrementalBeginDtTm
       AND IT_STUDENT_FEES_A.RowUpdatedOn <= @IncrementalEndDtTm
      )
)
    AND (Fees.FEE_IND = 'S' OR Fees.FEE_IND = 'C')
GROUP BY Paym.STUDENT_TERM, Paym.CRS_ID_XREF, FIELD_VALUE

/* Insert into #FeePayments if Customer = StduentID */
INSERT INTO #ClassFeePayments
(
     StudentTerm
    ,ReferenceNumber
    ,FeesPaidByFinAid
    ,FeesPaidByStudentTuitionPlan
    ,FeesPaidByThirdParty
    ,FeesPaidByPell
    ,FeesPaidByLoans
    ,FeesPaidByFederalGrants
    ,FeesPaidByStateGrants
    ,FeesPaidByInstitution
    ,FeesPaidByFoundation
    ,FeesPaidByTuitionPlan
    ,FeesPaidByDeferments
    ,CustomerCategory
  )
SELECT
      Paym.STUDENT_TERM
     ,RIGHT(Paym.CRS_ID_XREF,6) AS ReferenceNumber
     ,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 Fees
INNER JOIN IT_STUDENT_FEES_A_PAYMENT_GROUP Paym
  ON
  (
      Fees.ISN_IT_STUDENT_FEES_A = Paym.ISN_IT_STUDENT_FEES_A
      AND Paym.PAYMENT_SEQ IS NOT NULL
      AND Paym.FEE_AMOUNT IS NOT NULL
      AND Paym.CONTRACT_NBR IS NOT NULL
      AND LEFT(Paym.STUDENT_TERM,9) = LEFT(Paym.CONTRACT_NBR,9)
  )
   WHERE Paym.STUDENT_TERM IN
(
SELECT STUDENT_TERM
FROM IT_STUDENT_FEES_A
WHERE (
       IT_STUDENT_FEES_A.RowUpdatedOn >= @IncrementalBeginDtTm
       AND IT_STUDENT_FEES_A.RowUpdatedOn <= @IncrementalEndDtTm
      )
)
    AND (Fees.FEE_IND = 'S' OR Fees.FEE_IND = 'C')
GROUP BY Paym.STUDENT_TERM, Paym.CRS_ID_XREF

/* Calculate TotalFeePayments from IT_STUDENT_FEES_A */
INSERT INTO #ClassTotalFeePayments
(
     StudentTerm
    ,ReferenceNumber
    ,ClassTotalFeesAssessed
    ,ClassTotalFeesPaid
    ,ClassTotalFeesNotPaid
    ,ClassTotalFeesExempted
    ,ClassTotalFeesRefunded
    ,ClassTotalFeesStudentPaidCash
)

SELECT
       TotalFees.STUDENT_TERM
      ,RIGHT(TotalFees.CRS_ID_XREF,6) AS ReferenceNumber
      ,(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 STUDENT_TERM
   FROM IT_STUDENT_FEES_A
   WHERE
      (
       IT_STUDENT_FEES_A.RowUpdatedOn >= @IncrementalBeginDtTm
       AND IT_STUDENT_FEES_A.RowUpdatedOn <= @IncrementalEndDtTm
      )
)
   AND (TotalFees.FEE_IND = 'C' or TotalFees.FEE_IND = 'S')

  GROUP BY TotalFees.STUDENT_TERM, TotalFees.CRS_ID_XREF
/* Sum the FeePayments and Create Summary records *//

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

SELECT
     StudentTerm
    ,ReferenceNumber
    ,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 #ClassFeePayments
    GROUP BY StudentTerm, ReferenceNumber

SELECT
     #ClassTotalFeePayments.StudentTerm AS StudentTermAK
    ,#ClassTotalFeePayments.ReferenceNumber AS ReferenceNumberAK
    ,ClassTotalFeesAssessed
    ,ClassTotalFeesPaid
    ,ClassTotalFeesNotPaid
    ,ClassTotalFeesExempted
    --,ClassTotalFeesRefunded
    --,ClassTotalFeesStudentPaidCash
    ,CASE
        WHEN ClassTotalFeesAssessed = 0 AND ClassTotalFeesExempted > 0 THEN '6EX 1EXMPT'
        WHEN ClassTotalFeesAssessed > 0 AND ClassTotalFeesAssessed = ClassTotalFeesNotPaid THEN '5NP 2NOTP'
        WHEN ClassTotalFeesAssessed > 0 AND ClassTotalFeesAssessed = FeesPaidByPell THEN '1FA 1PELL'
        WHEN ClassTotalFeesAssessed > 0 AND ClassTotalFeesAssessed = FeesPaidByLoans THEN '1FA 2LOAN'
        WHEN ClassTotalFeesAssessed > 0 AND ClassTotalFeesAssessed = FeesPaidByFederalGrants THEN '1FA 3OTHF'
        WHEN ClassTotalFeesAssessed > 0 AND ClassTotalFeesAssessed = FeesPaidByStateGrants THEN '1FA 4STATE'
        WHEN ClassTotalFeesAssessed > 0 AND ClassTotalFeesAssessed = FeesPaidByInstitution THEN '1FA 5INSTS'
        WHEN ClassTotalFeesAssessed > 0 AND ClassTotalFeesAssessed = FeesPaidByFoundation THEN '1FA 6FOUND'
        WHEN ClassTotalFeesAssessed > 0 AND ClassTotalFeesAssessed = ClassTotalFeesStudentPaidCash THEN '2ST 1STPAID'
        WHEN ClassTotalFeesAssessed > 0 AND ClassTotalFeesAssessed = FeesPaidByTuitionPlan THEN '2ST 2TPP'
        WHEN ClassTotalFeesAssessed > 0 AND ClassTotalFeesAssessed = FeesPaidByDeferments THEN '2ST 3DEFER'
        WHEN ClassTotalFeesAssessed > 0 AND ClassTotalFeesAssessed = FeesPaidByThirdParty THEN '3TP 1THIRDP'
        WHEN (FeesPaidByPell + FeesPaidByLoans + FeesPaidByFederalGrants + FeesPaidByStateGrants +
              FeesPaidByInstitution + FeesPaidByFoundation) = ClassTotalFeesAssessed THEN '1FA 7FACOM'
        WHEN (ClassTotalFeesStudentPaidCash + FeesPaidByTuitionPlan + FeesPaidByDeferments) = ClassTotalFeesAssessed THEN '2ST 4STCOM'
        WHEN ClassTotalFeesNotPaid > 0 AND ClassTotalFeesNotPaid < ClassTotalFeesAssessed THEN '5NP 1PARTP'
        WHEN ClassTotalFeesAssessed > 0 AND (FeesPaidByPell + FeesPaidByLoans + FeesPaidByFederalGrants + FeesPaidByStateGrants +
              FeesPaidByInstitution + FeesPaidByFoundation) > 0 AND
             (FeesPaidByDeferments + FeesPaidByTuitionPlan + FeesPaidByThirdParty) > 0
               THEN '4CO 1COPAY'
        WHEN ClassTotalFeesAssessed > 0 AND ClassTotalFeesStudentPaidCash > 0
            AND FeesPaidByThirdParty > 0
               THEN '4CO 1COPAY'
        WHEN ClassTotalFeesAssessed > 0 AND (FeesPaidByPell + FeesPaidByLoans + FeesPaidByFederalGrants + FeesPaidByStateGrants +
              FeesPaidByInstitution + FeesPaidByFoundation) > 0 AND FeesPaidByThirdParty > 0
               THEN '4CO 1COPAY'
        WHEN ClassTotalFeesAssessed > 0 AND (FeesPaidByPell + FeesPaidByLoans + FeesPaidByFederalGrants + FeesPaidByStateGrants +
              FeesPaidByInstitution + FeesPaidByFoundation) > 0 AND
             (ClassTotalFeesStudentPaidCash > 0 OR FeesPaidByDeferments > 0 OR FeesPaidByTuitionPlan > 0)
               THEN '4CO 1COPAY'
        WHEN ClassTotalFeesAssessed > 0 AND FeesPaidByThirdParty > 0 AND
             (ClassTotalFeesStudentPaidCash > 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 #ClassTotalFeePayments

LEFT OUTER JOIN #ClassFeePaymentsSummary
ON
(
  #ClassFeePaymentsSummary.StudentTerm = #ClassTotalFeePayments.StudentTerm
  AND
  #ClassFeePaymentsSummary.ReferenceNumber = #ClassTotalFeePayments.ReferenceNumber
)
WHERE (ClassTotalFeesAssessed > 0
   OR ClassTotalFeesExempted > 0
   OR ClassTotalFeesPaid > 0
   OR ClassTotalFeesNotPaid > 0)