View - dbo.arch_AR

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

Column Name	Data Type	Allow Nulls	Fulltext Indexed	Identity	Description / Computed Formula
AccountNumber	varchar(25)	True	False	False
AddDateTime	datetime(23, 3)	True	False	False
AddProgram	varchar(8)	True	False	False
AddUserId	varchar(8)	True	False	False
AddUserName	varchar(42)	True	False	False
Amount	numeric(11, 2)	False	False	False
AmountDue	numeric(11, 2)	False	False	False
AmountPaid	numeric(11, 2)	False	False	False
AmountToCashier	numeric(11, 2)	False	False	False
CollectionFlag	varchar(2)	True	False	False
CommentInd	varchar(3)	False	False	False
CustomerId	varchar(9)	True	False	False
CustomerName	varchar(78)	True	False	False
EffectiveDate	date(10, 0)	True	False	False
GLAcctGLC	varchar(5)	True	False	False
GLAcctLoc	varchar(2)	True	False	False
GLAcctQual1	varchar(6)	True	False	False
GLAcctQual2	varchar(4)	True	False	False
GLAcctStateOrg	varchar(8)	True	False	False
HoldType	varchar(30)	True	False	False
HoldTypeCode	varchar(2)	True	False	False
MaintDateTime	datetime(23, 3)	True	False	False
MaintProgram	varchar(8)	True	False	False
MaintUserId	varchar(8)	True	False	False
MaintUserName	varchar(42)	True	False	False
ObligationAddDate	date(10, 0)	True	False	False
ObligationId	varchar(9)	True	False	False
ObligationType	varchar(30)	False	False	False
ObligationTypeCode	varchar(2)	True	False	False
PaidWithFinancialAid	varchar(3)	False	False	False
Reference	varchar(30)	True	False	False
Status	varchar(30)	True	False	False
StatusCode	varchar(2)	True	False	False
StatusDate	date(10, 0)	True	False	False

Name

Insert

Update

Delete

Instead

Name

Clustered

Columns

Name	Type
AR_CUSTOMER_A	Table
AR_OBLIGATION_A	Table
AR_STANDARD_TABLES_A	Table
SECURITY_USER_A2	Table
ST_STDNT_A	Table
UTL_CODE_TABLE	Table

Name	Type
ST_STDNT_A	TABLE
AR_CUSTOMER_A	TABLE
AR_OBLIGATION_A	TABLE
AR_STANDARD_TABLES_A	TABLE
SECURITY_USER_A2	TABLE
UTL_CODE_TABLE	TABLE

Object

Property

Value

Object

Property

Value

/****** Object: View [dbo].[arch_AR_Obligations] Script Date: 03/09/2017 16:47:08 ******/
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE VIEW dbo.arch_AR_Obligations
AS
SELECT
       CONVERT(DATE,Oblg.ADD_DATE) AS ObligationAddDate
      ,CASE
          WHEN ISDATE(Oblg.ADD_DATE) = 0 THEN NULL
          WHEN Oblg.ADD_TIME IS NULL THEN CONVERT(DATETIME, Oblg.ADD_DATE + ' '
             + '00:00:00.000')
          ELSE CONVERT(DATETIME, Oblg.ADD_DATE + ' '
             + STUFF(STUFF(STUFF(RIGHT('0000000' + Oblg.ADD_TIME,7),3,0,':'),6,0,':'),9,0,'.'))
          END AS AddDateTime
      ,Oblg.ADD_USER_ID AS AddUserId
      ,CASE
         WHEN SecAdd.LAST_NAME IS NULL THEN ' '  -- Was not found
         WHEN SecAdd.LAST_NAME > ' ' AND SecAdd.FIRST_NAME > ' '  THEN
             SecAdd.LAST_NAME + ', ' + SecAdd.FIRST_NAME
         WHEN SecAdd.LAST_NAME > ' ' AND SecAdd.FIRST_NAME = ' ' THEN SecAdd.    LAST_NAME
         ELSE SecAdd.LAST_NAME
       END AS     AddUserName
      ,Oblg.ADD_PGM AS AddProgram
      ,CASE
          WHEN ISDATE(Oblg.MAINT_DATE) = 0 THEN NULL
          WHEN Oblg.MAINT_TIME IS NULL THEN CONVERT(DATETIME, Oblg.MAINT_DATE + ' '
             + '00:00:00.000')
          ELSE CONVERT(DATETIME, Oblg.MAINT_DATE + ' '
             + STUFF(STUFF(STUFF(RIGHT('0000000' + Oblg.MAINT_TIME,7),3,0,':'),6,0,':'),9,0,'.'))
          END AS MaintDateTime
      ,Oblg.MAINT_USER_ID AS MaintUserId
      ,CASE
         WHEN SecMaint.LAST_NAME IS NULL THEN ' '  -- Was not found
         WHEN SecMaint.LAST_NAME > ' ' AND SecMaint.FIRST_NAME > ' '  THEN
             SecMaint.LAST_NAME + ', ' + SecMaint.FIRST_NAME
         WHEN SecMaint.LAST_NAME > ' ' AND SecMaint.FIRST_NAME = ' ' THEN SecMaint.    LAST_NAME
         ELSE SecMaint.LAST_NAME
       END AS MaintUserName
      ,Oblg.MAINT_PGM AS MaintProgram
      ,OBLIGATION_ID AS ObligationId
      ,CONVERT(DATE,EFFECTIVE_DATE) AS EffectiveDate
      ,Oblg.OBLIGATION_TYPE AS ObligationTypeCode
      ,ISNULL(OblgType.OBLIGATION_DESCRIPTION,'N/A') AS ObligationType
      ,Oblg.STATUS AS StatusCode
      ,OblgStat.DESCRIPTION AS Status
      ,CONVERT(DATE,Oblg.STATUS_DATE) AS StatusDate
      ,Oblg.CUSTOMER_ID AS CustomerId
      ,CASE
          WHEN Stdnt.LST_NM IS NOT NULL AND Stdnt.FRST_NM IS NOT NULL AND Stdnt.MDL_NM IS NOT NULL
             THEN Stdnt.LST_NM + ', ' + Stdnt.FRST_NM + ' ' + Stdnt.MDL_NM
          WHEN Stdnt.LST_NM IS NOT NULL AND Stdnt.FRST_NM IS NOT NULL
             THEN Stdnt.LST_NM + ', ' + Stdnt.FRST_NM
          WHEN Stdnt.LST_NM IS NOT NULL
             THEN Stdnt.LST_NM
          ELSE Cust.CUSTOMER_NAME
       END AS CustomerName
      ,REFERENCE AS Reference
      ,CASE
         WHEN Oblg.COMMENT_IND = 'Y' THEN 'Yes'
         ELSE 'No'
       END AS CommentInd
      ,ISNULL(AMOUNT,0) AS Amount
      ,ISNULL(AMOUNT_CASHIER,0) AS AmountToCashier
      ,ISNULL(AMOUNT_PAID,0) AS AmountPaid
      ,ISNULL(AMOUNT_DUE,0) AS AmountDue
      ,ACCOUNT_NUMBER AS AccountNumber
      ,SUBSTRING(ACCOUNT_NUMBER, 1, 8) AS GLAcctStateOrg
      ,SUBSTRING(ACCOUNT_NUMBER, 9, 6) AS GLAcctQual1
      ,SUBSTRING(ACCOUNT_NUMBER, 15, 2) AS GLAcctLoc
      ,SUBSTRING(ACCOUNT_NUMBER, 17, 5) AS GLAcctGLC
      ,SUBSTRING(ACCOUNT_NUMBER, 22, 4) AS GLAcctQual2
      ,HOLD_TYPE AS HoldTypeCode
      ,Hld.DESCRIPTION AS HoldType
      ,CASE
         WHEN Oblg.PAY_WITH_FINANCIAL_AID  = 'N' THEN 'No'
         ELSE 'Yes'
       END AS PaidWithFinancialAid
      ,COLLECTION_FLAG AS CollectionFlag
  FROM DWStaging.dbo.AR_OBLIGATION_A Oblg
  LEFT OUTER JOIN SECURITY_USER_A2 SecAdd
  ON
  (
     SecAdd.USER_ID = Oblg.ADD_USER_ID
  )
  LEFT OUTER JOIN SECURITY_USER_A2 SecMaint
  ON
  (
     SecMaint.USER_ID = Oblg.MAINT_USER_ID
  )
  LEFT OUTER JOIN AR_STANDARD_TABLES_A OblgType
  ON
  (
     Oblg.OBLIGATION_TYPE = OblgType.OBLIGATION_TYPE
  )
  LEFT OUTER JOIN UTL_CODE_TABLE OblgStat
  ON
  (
    OblgStat.TABLE_NAME = 'OBLIG-STTS' AND
    OblgStat.CODE = Oblg.STATUS AND
    OblgStat.STATUS = 'A'
  )
  LEFT OUTER JOIN ST_STDNT_A Stdnt
  ON
  (
     Oblg.CUSTOMER_ID = Stdnt.STUDENT_ID
  )
  LEFT OUTER JOIN AR_CUSTOMER_A Cust
  ON
  (
     Cust.CUSTOMER_ID = Oblg.CUSTOMER_ID
  )
  LEFT OUTER JOIN UTL_CODE_TABLE Hld
  ON
  (
     Hld.TABLE_NAME = 'HOLDS' AND
     Hld.CODE = Oblg.HOLD_TYPE AND
     Hld.STATUS = 'A'
  )