Stored Procedure - usp_FactStudentReceiptPayments

Property	Value
Name	usp_FactStudentReceiptPayments_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_RECEIPT_A	Table
AR_RECEIPT_PAYMENT_A	Table
ST_STDNT_A	Table

Name	Type
AR_RECEIPT_A	TABLE
AR_RECEIPT_PAYMENT_A	TABLE
ST_STDNT_A	TABLE

Object

Property

Value

Object

Property

Value

/****** Object: StoredProcedure [dbo].[usp_FactStudentReceiptPayments_Select] Script Date: 03/09/2017 16:47:10 ******/
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
/*
Name:            usp_FactStudentReceiptPayments_Select
Title:            Primary Select for the Student Receipt Payment Fact
Date:            11/1/2012
System/Project:    Integrated Financials
Description:    This procedure will combine the AR_RECEIPT and AR_RECEIPT_PAYMENT information from DWStaging for
the creation of the FactStudentReceiptPayments.
Revision History:
09/17/2013 Sophia Cowan        Created
*/
CREATE PROC [dbo].[usp_FactStudentReceiptPayments_Select]
@IncrementalBeginDtTm datetime, /* Date and Time to validate against RowUpdatedOn in Source Tables */
@IncrementalEndDtTm datetime
AS
SELECT
     CASHIER_SESSION_KEY + RIGHT('00000' + CAST(RECEIPT_SEQ as varchar(5)),5) as     StudentReceiptAK
    ,STUDENT_ID  AS StudentIdAK
    ,CAST(CASE
         WHEN ISDATE(ADD_DATE) = 1 THEN  ADD_DATE
         ELSE '1900-01-01'
         END AS DATETIME)
         AS DateAK
    ,CAST('CV' AS VARCHAR(3)) AS ReceiptPaymentCategory
    ,CAST(RECEIPT_OTHER_1 AS NUMERIC(11,2)) AS ReceiptPaymentAmount
    FROM AR_RECEIPT_A StReceiptCV
    WHERE (
       StReceiptCV.RowUpdatedOn >= @IncrementalBeginDtTm
       AND StReceiptCV.RowUpdatedOn <= @IncrementalEndDtTm
       AND StReceiptCV.STUDENT_ID IS NOT NULL AND StReceiptCV.RECEIPT_STATUS = 'CO'
       AND StReceiptCV.RECEIPT_OTHER_1 IS NOT NULL
       )

UNION ALL
SELECT
     CASHIER_SESSION_KEY + RIGHT('00000' + CAST(RECEIPT_SEQ as varchar(5)),5) as     StudentReceiptAK
    ,CUSTOMER_ID  AS StudentIdAK
    ,CAST(CASE
         WHEN ISDATE(CustReceiptCV.ADD_DATE) = 1 THEN  CustReceiptCV.ADD_DATE
         ELSE '1900-01-01'
         END AS DATETIME)
         AS DateAK
    ,CAST('CV' AS VARCHAR(3)) AS ReceiptPaymentCategory
    ,CAST(RECEIPT_OTHER_1 AS NUMERIC(11,2)) AS ReceiptPaymentAmount
    FROM AR_RECEIPT_A CustReceiptCV
    INNER JOIN ST_STDNT_A Stdnt
  ON
  (
     CustReceiptCV.CUSTOMER_ID = Stdnt.STUDENT_ID
     and
     Stdnt.STUDENT_ID IS NOT NULL
  )
    WHERE
    (
       CustReceiptCV.RowUpdatedOn >= @IncrementalBeginDtTm
       AND CustReceiptCV.RowUpdatedOn <= @IncrementalEndDtTm
       AND CustReceiptCV.STUDENT_ID IS NULL AND CustReceiptCV.CUSTOMER_ID IS NOT NULL AND CustReceiptCV.RECEIPT_STATUS = 'CO'
       AND CustReceiptCV.RECEIPT_OTHER_1 IS NOT NULL
    )

UNION ALL
SELECT
     StReceipt.CASHIER_SESSION_KEY + RIGHT('00000' + CAST(StReceipt.RECEIPT_SEQ as varchar(5)),5) as     StudentReceiptAK
    ,STUDENT_ID  AS StudentIdAK
    ,CAST(CASE
         WHEN ISDATE(StReceipt.ADD_DATE) = 1 THEN  StReceipt.ADD_DATE
         ELSE '1900-01-01'
         END AS DATETIME)
         AS DateAK
    ,CAST(PAYMENT_TYPE AS VARCHAR(3)) AS ReceiptPaymentCategory
    ,CAST(PAYMENT_AMOUNT AS NUMERIC(11,2)) AS ReceiptPaymentAmount
    FROM AR_RECEIPT_A StReceipt
    INNER JOIN AR_RECEIPT_PAYMENT_A StReceiptPaym
    ON
    (
       StReceipt.CASHIER_SESSION_KEY = StReceiptPaym.CASHIER_SESSION_KEY
       AND
       StReceipt.RECEIPT_SEQ = StReceiptPaym.RECEIPT_SEQ
    )
    WHERE
    (
       StReceipt.RowUpdatedOn >= @IncrementalBeginDtTm
       AND StReceipt.RowUpdatedOn <= @IncrementalEndDtTm
       AND StReceipt.STUDENT_ID IS NOT NULL AND StReceipt.RECEIPT_STATUS = 'CO'
    )

   UNION ALL

   SELECT
     CustReceipt.CASHIER_SESSION_KEY + RIGHT('00000' + CAST(CustReceipt.RECEIPT_SEQ as varchar(5)),5) as     StudentReceiptAK
    ,CUSTOMER_ID  AS StudentIdAK
    ,CAST(CASE
         WHEN ISDATE(CustReceipt.ADD_DATE) = 1 THEN  CustReceipt.ADD_DATE
         ELSE '1900-01-01'
         END AS DATETIME)
         AS DateAK
    ,CAST(PAYMENT_TYPE AS VARCHAR(3)) AS ReceiptPaymentCategory
    ,CAST(PAYMENT_AMOUNT AS NUMERIC(11,2)) AS ReceiptPaymentAmount
    FROM AR_RECEIPT_A CustReceipt
    INNER JOIN ST_STDNT_A Stdnt
    ON
  (
     CustReceipt.CUSTOMER_ID = Stdnt.STUDENT_ID
     and
     Stdnt.STUDENT_ID IS NOT NULL
  )
    INNER JOIN AR_RECEIPT_PAYMENT_A CustReceiptPaym
    ON
    (
       CustReceipt.CASHIER_SESSION_KEY = CustReceiptPaym.CASHIER_SESSION_KEY
       AND
       CustReceipt.RECEIPT_SEQ = CustReceiptPaym.RECEIPT_SEQ
    )
    WHERE
    (
       CustReceipt.RowUpdatedOn >= @IncrementalBeginDtTm
       AND CustReceipt.RowUpdatedOn <= @IncrementalEndDtTm
       AND CustReceipt.STUDENT_ID IS NULL AND CustReceipt.CUSTOMER_ID IS NOT NULL AND CustReceipt.RECEIPT_STATUS = 'CO'
    )