Stored Procedure - usp_DimStudentReceipt

Property	Value
Name	usp_DimStudentReceipt_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_CASHIER_A	Table
AR_RECEIPT_A	Table
FAC192_SITE_A	Table
FAC199_LOCATION_A	Table
ST_STDNT_A	Table
UTL_CODE_TABLE	Table

Name	Type
UTL_CODE_TABLE	TABLE
AR_CASHIER_A	TABLE
AR_RECEIPT_A	TABLE
FAC192_SITE_A	TABLE
FAC199_LOCATION_A	TABLE
ST_STDNT_A	TABLE

Object

Property

Value

Object

Property

Value

/****** Object: StoredProcedure [dbo].[usp_DimStudentReceipt_Select] Script Date: 03/09/2017 16:47:10 ******/
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
/*
Name:            usp_DimStudentReceipt_Select
Title:            Primary Select for the Student Receipt Dimension
Date:            10/1/2013
System/Project:    Integrated Financials
Description:    This procedure will combine the Student Receipt from DWStaging for
the creation of the DimStudentReceipt
Revision History:
10/01/2013 Sophia Cowan        Created
*/
CREATE PROC [dbo].[usp_DimStudentReceipt_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(24))  AS     StudentReceiptAK
       ,CAST('aaa' as varchar(9)) AS StudentId
       ,CAST(0 as int) AS PrintedReceiptNumber
      ,CAST('aaa' AS VARCHAR(15)) AS ReceiptCategory
      ,CAST('aaa' AS VARCHAR(8))  AS ReceiptProcessorCode
      ,CAST('aaa' AS VARCHAR(50)) AS ReceiptProcessor
      ,CAST('1900-01-01' AS DATETIME) AS SessionDate
      ,CAST(0 AS INT)  AS ReceiptSequenceNumber
      ,CAST(0 AS INT)  AS SessionSequenceNumber
      ,CAST('aaa' AS VARCHAR(3)) AS ReceiptTypeCode
      ,CAST('aaa' AS VARCHAR(30)) as ReceiptType
      ,CAST('AAA' AS VARCHAR(3)) as MiscellaneousReceiptCategoryCode
      ,CAST('aaa' AS VARCHAR(30)) as MiscellaneousReceiptCategory
      ,CAST('aaa' AS VARCHAR(30))  AS ReceiptReference
     END
CREATE TABLE #TempReceipt
(
    PRINTED_RECEIPT_NUM int NULL,
    CASHIER_SESSION_KEY varchar(19) NULL,
    RECEIPT_SEQ int NULL,
    RECEIPT_TYPE varchar(2) NULL,
    CUSTOMER_ID varchar(9) NULL,
    STUDENT_ID varchar(9) NULL,
    MISC_RECEIPT_CAT varchar(2) NULL,
    RECEIPT_REFERENCE_NUM varchar(30) NULL,
)
INSERT INTO #TempReceipt
(
     PRINTED_RECEIPT_NUM
    ,CASHIER_SESSION_KEY
    ,RECEIPT_SEQ
    ,RECEIPT_TYPE
    ,CUSTOMER_ID
    ,STUDENT_ID
    ,MISC_RECEIPT_CAT
    ,RECEIPT_REFERENCE_NUM
)
SELECT
    PRINTED_RECEIPT_NUM
    ,CASHIER_SESSION_KEY
    ,RECEIPT_SEQ
    ,RECEIPT_TYPE
    ,CUSTOMER_ID
    ,STUDENT_ID
    ,MISC_RECEIPT_CAT
    ,RECEIPT_REFERENCE_NUM
    FROM AR_RECEIPT_A StReceipt
    WHERE (
       StReceipt.RowUpdatedOn >= @IncrementalBeginDtTm
       AND StReceipt.RowUpdatedOn <= @IncrementalEndDtTm
       AND StReceipt.STUDENT_ID IS NOT NULL AND StReceipt.RECEIPT_STATUS = 'CO'
       )

INSERT INTO #TempReceipt
(
     PRINTED_RECEIPT_NUM
    ,CASHIER_SESSION_KEY
    ,RECEIPT_SEQ
    ,RECEIPT_TYPE
    ,CUSTOMER_ID
    ,STUDENT_ID
    ,MISC_RECEIPT_CAT
    ,RECEIPT_REFERENCE_NUM
)
SELECT
    PRINTED_RECEIPT_NUM
    ,CASHIER_SESSION_KEY
    ,RECEIPT_SEQ
    ,RECEIPT_TYPE
    ,CUSTOMER_ID
    ,CustReceipt.STUDENT_ID
    ,MISC_RECEIPT_CAT
    ,RECEIPT_REFERENCE_NUM
    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
  )
    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'
       )
SELECT
       CASHIER_SESSION_KEY + RIGHT('00000' + CAST(RECEIPT_SEQ as varchar(5)),5) as     StudentReceiptAK
       ,CASE
           WHEN STUDENT_ID IS NOT NULL THEN STUDENT_ID
           WHEN CUSTOMER_ID IS NOT NULL THEN CUSTOMER_ID
           ELSE 'N/A'
         END AS StudentId
       ,ISNULL(PRINTED_RECEIPT_NUM,1) AS PrintedReceiptNumber
      ,CAST(CASE
        WHEN Location.SITE_LOCAL_NUM IS NOT NULL THEN 'Registration'
        WHEN Location0000.SITE_LOCAL_NUM IS NOT NULL THEN 'Registration'
        WHEN Site.SITE_NUM IS NOT NULL THEN 'Registration'
        WHEN LEFT(CASHIER_SESSION_KEY,3) = 'WEB' OR LEFT(CASHIER_SESSION_KEY,8) = 'IVR'
          OR LEFT(CASHIER_SESSION_KEY,6) = 'ECHECK' THEN 'Web'
        WHEN Cashier.CASHIER_ID IS NULL THEN 'Function'
        WHEN Cashier.CASHIER_ID IS NOT NULL THEN 'Cashier'
        ELSE 'N/A'
        END AS VARCHAR(15))
        AS ReceiptCategory
      ,LEFT(CASHIER_SESSION_KEY,8) as ReceiptProcessorCode
      ,CAST(CASE
        WHEN Location.SITE_LOCAL_NUM IS NOT NULL THEN Location.LOCATION_NAME
        WHEN Location0000.SITE_LOCAL_NUM IS NOT NULL THEN Location0000.LOCATION_NAME
        WHEN Site.SITE_NUM IS NOT NULL THEN Site.SITE_NAME
        WHEN LEFT(CASHIER_SESSION_KEY,3) = 'WEB' OR LEFT(CASHIER_SESSION_KEY,8) = 'IVR'
          OR LEFT(CASHIER_SESSION_KEY,5) = 'ECHECK' THEN 'Student Generated on the Web'
        WHEN Cashier.CASHIER_ID IS NULL THEN 'Generated by ' + RTRIM(LEFT(CASHIER_SESSION_KEY,8)) + ' Function'
        WHEN Cashier.CASHIER_ID IS NOT NULL THEN Cashier.CASHIER_NAME
        ELSE 'N/A'
        END AS VARCHAR(50))
        AS ReceiptProcessor
      ,CAST(CASE
         WHEN ISDATE(SUBSTRING(CASHIER_SESSION_KEY,9,8)) = 1 THEN  SUBSTRING(CASHIER_SESSION_KEY,9,8)
         ELSE '1900-01-01'
         END AS DATETIME)
         AS SessionDate
      ,RECEIPT_SEQ AS ReceiptSequenceNumber
      ,SUBSTRING(CASHIER_SESSION_KEY,17,3) AS SessionSequenceNumber
      ,ISNULL(CAST(RECEIPT_TYPE AS VARCHAR(3)),'N/A') AS ReceiptTypeCode
      ,CAST(ISNULL(RecType.DESCRIPTION,'N/A') AS VARCHAR(30)) as ReceiptType
      ,ISNULL(CAST(MISC_RECEIPT_CAT AS VARCHAR(3)),'N/A') as MiscellaneousReceiptCategoryCode
      ,CAST(ISNULL(MiscCat.DESCRIPTION,'N/A') AS VARCHAR(30)) as MiscellaneousReceiptCategory
      ,CAST(ISNULL(RECEIPT_REFERENCE_NUM,'N/A') AS VARCHAR(30)) AS ReceiptReference
  FROM #TempReceipt StdntReceipt

  LEFT OUTER JOIN FAC192_SITE_A SiteT
  ON
  (
    SiteT.REC_TYPE = 'S'
    and SiteT.SITE_LOCAL_NUM = LEFT(StdntReceipt.CASHIER_SESSION_KEY,1)
  )
   LEFT OUTER JOIN FAC192_SITE_A Site
  ON
  (
    Site.REC_TYPE = 'I'
    and Site.SITE_NUM = SiteT.SITE_NUM
  )
  LEFT OUTER JOIN FAC199_LOCATION_A Location
  ON
  (
    Location.SITE_LOCAL_NUM = LEFT(StdntReceipt.CASHIER_SESSION_KEY,1)
    AND
    Location.LOCATION_NUM = RTRIM(SUBSTRING(StdntReceipt.CASHIER_SESSION_KEY,2,4))
  )
  LEFT OUTER JOIN FAC199_LOCATION_A Location0000
  ON
  (
    Location.SITE_LOCAL_NUM = LEFT(StdntReceipt.CASHIER_SESSION_KEY,1)
    AND
    Location.LOCATION_NUM = '0000'
  )
  LEFT OUTER JOIN AR_CASHIER_A Cashier
  ON
  (
    Cashier.CASHIER_ID = LEFT(StdntReceipt.CASHIER_SESSION_KEY,8)
  )
  LEFT OUTER JOIN UTL_CODE_TABLE RecType
  ON
  (
    RecType.TABLE_NAME = 'RCPT-TYPE'
    AND
    RecType.CODE = StdntReceipt.RECEIPT_TYPE
    AND
    RecType.STATUS = 'A'
  )
   LEFT OUTER JOIN UTL_CODE_TABLE MiscCat
  ON
  (
    MiscCat.TABLE_NAME = 'RCPT-MISC'
    AND
    MiscCat.CODE = StdntReceipt.MISC_RECEIPT_CAT
    AND
    MiscCat.STATUS = 'A'
  )