Stored Procedure - usp_FactStudentFeesPaid

Property	Value
Name	usp_FactStudentFeesPaid_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_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_PAYMENT_GROUP	TABLE

Object

Property

Value

Object

Property

Value

/****** Object: StoredProcedure [dbo].[usp_FactStudentFeesPaid_Select] Script Date: 03/09/2017 16:47:10 ******/
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
/*
Name:            usp_FactStudentFeesPaid_Select
Title:            Primary Select for the Student Fees Paid
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_FactStudentFeesPaid_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('aaa' AS VARCHAR(4)) AS FeeTypeAK
      ,CAST('0' AS INT) AS ReferenceNumberAK
      ,CAST('AAA' AS VARCHAR(20))  AS PaymentSourceAK
      ,CAST('0' AS NUMERIC(11,2)) AS FeeAmount
     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'   )
     )
)
SELECT
    PaymGroup.STUDENT_TERM AS StudentTermAK
  ,PaymGroup.FEE_TY AS FeeTypeAK
  ,RIGHT(PaymGroup.CRS_ID_XREF,6) as ReferenceNumberAK
  ,CASE
    WHEN PaymGroup.FEE_PAYMENT_TYPE = 'EX' THEN '6EX 1EXMPT'
    WHEN PaymGroup.FEE_PAYMENT_TYPE = 'PD' THEN '2ST 1STPAID'
    WHEN PaymGroup.CONTRACT_NBR IS NOT NULL AND CustCat.FIELD_VALUE = 'P' THEN '1FA 1PELL'
    WHEN PaymGroup.CONTRACT_NBR IS NOT NULL AND CustCat.FIELD_VALUE = 'L' THEN '1FA 2LOAN'
    WHEN PaymGroup.CONTRACT_NBR IS NOT NULL AND CustCat.FIELD_VALUE = 'G' THEN '1FA 3OTHF'
    WHEN PaymGroup.CONTRACT_NBR IS NOT NULL AND CustCat.FIELD_VALUE = 'F' THEN '1FA 4STATE'
    WHEN PaymGroup.CONTRACT_NBR IS NOT NULL AND CustCat.FIELD_VALUE = 'I' THEN '1FA 5INSTS'
    WHEN PaymGroup.CONTRACT_NBR IS NOT NULL AND CustCat.FIELD_VALUE = 'N' THEN '1FA 6FOUND'
    WHEN PaymGroup.CONTRACT_NBR IS NOT NULL AND CustCat.FIELD_VALUE = 'T' THEN '2ST 2TPP'
    WHEN PaymGroup.CONTRACT_NBR IS NOT NULL AND CustCat.FIELD_VALUE = 'S' THEN '2ST 3DEFER'
    WHEN PaymGroup.CONTRACT_NBR IS NOT NULL AND CustCat.FIELD_VALUE = 'R' THEN '3TP 1THIRDP'
    WHEN PaymGroup.FEE_PAYMENT_TYPE = 'MA' THEN '2ST 1PAID'
    ELSE 'N/A'
   END AS PaymentSourceAK
  ,PaymGroup.FEE_AMOUNT AS FeeAmount
  FROM IT_STUDENT_FEES_A_PAYMENT_GROUP PaymGroup
  LEFT OUTER JOIN AR_CUSTOMER_A Cust
  ON
  (
     Cust.CUSTOMER_ID = LEFT(PaymGroup.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 PaymGroup.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 PaymGroup.FEE_AMOUNT IS NOT NULL and PaymGroup.PAYMENT_SEQ IS NOT NULL