View - dbo.arch_AR_ReceiptItemPaidGroup

Property	Value
Name	arch_AR_ReceiptItemPaidGroup
Schema	dbo
Quoted Identifier Status	True
Ansi Nulls Status	True
Returns View Metadata	False
Create Date	10/14/2016 4:31 PM
Description

Column Name	Data Type	Allow Nulls	Fulltext Indexed	Identity	Description / Computed Formula
CoverageAmount	numeric(17, 2)	False	False	False
FiscalYear	varchar(4)	True	False	False
ItemDesc	varchar(30)	True	False	False
ItemDescription	varchar(24)	True	False	False
ItemId	varchar(22)	True	False	False
ItemName	varchar(30)	True	False	False
ItemNumber	int(10, 0)	True	False	False
ItemStatus	varchar(9)	False	False	False
ItemStatusCode	varchar(2)	True	False	False
ItemType	varchar(2)	True	False	False
OriginalAmount	numeric(17, 2)	False	False	False
PaidAmount	numeric(17, 2)	False	False	False
PrintedReceiptNumber	int(10, 0)	True	False	False
ReceiptKey	varchar(19)	True	False	False
ReceiptSeqNumber	int(10, 0)	True	False	False
ReceiptType	varchar(28)	False	False	False
ReceiptTypeCode	varchar(2)	True	False	False

Name

Insert

Update

Delete

Instead

Name

Clustered

Columns

Name	Type
AR_RECEIPT_A	Table
AR_RECEIPT_A_ITEMS_PAID_GROUP	Table

Name	Type
AR_RECEIPT_A	TABLE
AR_RECEIPT_A_ITEMS_PAID_GROUP	TABLE

Object

Property

Value

Object

Property

Value

/****** Object: View [dbo].[arch_AR_ReceiptItemPaidGroup] Script Date: 03/09/2017 16:47:08 ******/
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE VIEW [dbo].[arch_AR_ReceiptItemPaidGroup]
AS
SELECT
       FISCAL_YEAR AS FiscalYear
      ,PRINTED_RECEIPT_NUM AS PrintedReceiptNumber
      ,CASHIER_SESSION_KEY  AS ReceiptKey
      ,RECEIPT_SEQ AS ReceiptSeqNumber
      ,RECEIPT_TYPE AS ReceiptTypeCode
      ,CASE
         WHEN RECEIPT_TYPE = 'MR' THEN 'Miscellaneous Receipt'
         WHEN RECEIPT_TYPE = 'RR' THEN 'Customer Receivable Receipt'
         WHEN RECEIPT_TYPE = 'SR' THEN 'Student Receipt'
         WHEN RECEIPT_TYPE = 'AU' THEN 'Automated Receipt'
         WHEN RECEIPT_TYPE = 'CR' THEN 'Collection Receipt'
         WHEN RECEIPT_TYPE = 'TU' THEN 'Tuition Payment Plan Receipt'
         ELSE 'Unknown'
       END AS ReceiptType
      ,cnxarraycolumn + 1 AS ItemNumber
      ,ITEM_TYPE AS ItemType
      ,CASE
         WHEN Rcpt.RECEIPT_TYPE = 'SR' OR Rcpt.RECEIPT_TYPE = 'CR' THEN
         CASE
           WHEN ITEM_TYPE = 'RT' THEN 'Total Receivables'
           WHEN ITEM_TYPE = 'RD' THEN ITEM_DESC
           WHEN ITEM_TYPE = 'OT' THEN 'Total Obligations'
           WHEN ITEM_TYPE = 'OD' THEN ITEM_DESC
           WHEN ITEM_TYPE = 'BD' THEN ITEM_DESC
           WHEN ITEM_TYPE = 'TR' THEN 'Term '  + LEFT(ITEM_ID,4) + '-' + SUBSTRING(ITEM_ID,5,2)
           ELSE ' '
         END
         WHEN Rcpt.RECEIPT_TYPE = 'RR'  THEN
         CASE
           WHEN ITEM_TYPE = 'BL' THEN 'Invoice: ' + SUBSTRING(ITEM_ID,5,9)
           WHEN ITEM_TYPE = 'RT' THEN 'Total Receivables'
           WHEN ITEM_TYPE = 'RD' THEN ITEM_DESC
           WHEN ITEM_TYPE = 'OT' THEN 'Total Obligations'
           WHEN ITEM_TYPE = 'OD' THEN ITEM_DESC
           ELSE ' '
         END
         WHEN Rcpt.RECEIPT_TYPE = 'AU' THEN
         CASE
           WHEN ITEM_TYPE = 'RT' THEN 'Total Receivables'
           WHEN ITEM_TYPE = 'RD' THEN ITEM_DESC
           WHEN ITEM_TYPE = 'OT' THEN 'Total Obligations'
           WHEN ITEM_TYPE = 'OD' THEN ITEM_DESC
           WHEN ITEM_TYPE = 'BD' THEN ITEM_DESC
           WHEN ITEM_TYPE = 'TR' THEN 'Term '  + LEFT(ITEM_ID,4) + '-' + SUBSTRING(ITEM_ID,5,2)
           ELSE ' '
         END
       END AS ItemName
      ,CASE
         WHEN Rcpt.RECEIPT_TYPE = 'SR' OR Rcpt.RECEIPT_TYPE = 'CR' THEN
           CASE
              WHEN ITEM_TYPE = 'RT' THEN 'Total Receivables'
              WHEN ITEM_TYPE = 'RD' THEN '#=' + LEFT(ITEM_ID,4) + '-' + SUBSTRING(ITEM_ID,5,9) + '-' + SUBSTRING(ITEM_ID,14,3)
              WHEN ITEM_TYPE = 'OD' THEN 'Obligation ' + LEFT(ITEM_ID,9)
              WHEN ITEM_TYPE = 'BD' THEN 'For Term = ' + SUBSTRING(ITEM_ID,11,4) + '-' + SUBSTRING(ITEM_ID,15,2)
              WHEN ITEM_TYPE = 'TR' AND SUBSTRING(ITEM_ID,7,4) = '9999' THEN 'Term Fee:' + SUBSTRING(ITEM_ID,11,4)
              WHEN ITEM_TYPE = 'TR' THEN SUBSTRING(ITEM_ID,7,10) + 'R# ' + SUBSTRING(ITEM_ID,17,6)
              ELSE ' '
           END
         WHEN Rcpt.RECEIPT_TYPE = 'RR'  THEN
         CASE
           WHEN ITEM_TYPE = 'BL' THEN 'B ' + CONVERT(CHAR(10),CONVERT(DATE,DATE_1)) + 'D ' + CONVERT(CHAR(10),CONVERT(DATE,DATE_2))
           WHEN ITEM_TYPE = 'RD' THEN '#= ' + LEFT(ITEM_ID,4) + '-' + SUBSTRING(ITEM_ID,5,9) + '-' + SUBSTRING(ITEM_ID,14,3)
           WHEN ITEM_TYPE = 'OD' THEN 'Obligation ' +  LEFT(ITEM_ID,9)
           ELSE ' '
         END
         WHEN Rcpt.RECEIPT_TYPE = 'AU' THEN
           CASE
              WHEN ITEM_TYPE = 'RD' THEN '#=' + LEFT(ITEM_ID,4) + '-' + SUBSTRING(ITEM_ID,5,9) + '-' + SUBSTRING(ITEM_ID,14,3)
              WHEN ITEM_TYPE = 'OD' THEN 'Obligation ' + LEFT(ITEM_ID,9)
              WHEN ITEM_TYPE = 'BD' THEN 'For Term ' + SUBSTRING(ITEM_ID,11,4) + '-' + SUBSTRING(ITEM_ID,15,2)
              WHEN ITEM_TYPE = 'TR' AND SUBSTRING(ITEM_ID,7,4) = '9999' THEN 'Term Fee:' + SUBSTRING(ITEM_ID,11,4)
              WHEN ITEM_TYPE = 'TR' THEN SUBSTRING(ITEM_ID,7,10) + 'R# ' + SUBSTRING(ITEM_ID,17,6)
              ELSE ' '
           END
       END AS ItemDescription
      ,ITEM_ID AS ItemId
      ,ITEM_DESC AS ItemDesc
      ,ISNULL(AMOUNT_PAID,0) AS PaidAmount
      ,ISNULL(AMOUNT_COV_DUE,0) AS CoverageAmount
      ,ISNULL(AMOUNT_ORIG,0) AS OriginalAmount
      ,ITEM_STATUS AS ItemStatusCode
      ,CASE
         WHEN ITEM_STATUS = 'PD' THEN 'Paid'
         ELSE 'Part Paid'
       END AS ItemStatus
  FROM AR_RECEIPT_A_ITEMS_PAID_GROUP ItemPaid
  INNER JOIN AR_RECEIPT_A Rcpt
  ON
  (
     ItemPaid.ISN_AR_RECEIPT_A = Rcpt.ISN_AR_RECEIPT_A
  )