View - dbo.arch_AR

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

Column Name	Data Type	Allow Nulls	Fulltext Indexed	Identity	Description / Computed Formula
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
AmountDue	numeric(11, 2)	False	False	False
AmountPaid	numeric(11, 2)	False	False	False
BankInd	varchar(3)	False	False	False
BillInstallmentNumber	int(10, 0)	False	False	False
ContractId	int(10, 0)	False	False	False
CoverageType	varchar(12)	False	False	False
CustomerId	varchar(9)	True	False	False
CustomerName	varchar(40)	True	False	False
DunningLetterCount	int(10, 0)	False	False	False
FiscalYear	varchar(4)	True	False	False
HoldDate	date(10, 0)	True	False	False
HoldType	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
OverPaidInd	varchar(3)	False	False	False
ReceivableAddDate	date(10, 0)	True	False	False
ReceivableAmount	numeric(11, 2)	False	False	False
ReceivableDate	date(10, 0)	True	False	False
ReceivableNumber	varchar(9)	True	False	False
ReceivableSeqNumber	int(10, 0)	True	False	False
ReceivableStatus	varchar(30)	False	False	False
ReceivableStatusCode	varchar(2)	True	False	False
ReceivableType	varchar(30)	False	False	False
ReceivableTypeCode	varchar(2)	True	False	False
SendToCollections	varchar(30)	False	False	False
SpecialChargeDescription	varchar(30)	False	False	False
StudentId	varchar(9)	True	False	False
StudentName	varchar(78)	True	False	False
Term	varchar(6)	True	False	False

Name

Insert

Update

Delete

Instead

Name

Clustered

Columns

Name	Type
AR_CUSTOMER_A	Table
AR_RECEIVABLES_A	Table
AR_STANDARD_TABLES_A	Table
SECURITY_USER_A2	Table
ST_SESSION_A	Table
ST_STDNT_A	Table
ST_TERM_A	Table
UTL_CODE_TABLE	Table

Name	Type
ST_SESSION_A	TABLE
ST_STDNT_A	TABLE
ST_TERM_A	TABLE
AR_CUSTOMER_A	TABLE
AR_RECEIVABLES_A	TABLE
AR_STANDARD_TABLES_A	TABLE
SECURITY_USER_A2	TABLE
udf_StringToDate	USERDEFINEDFUNCTION
UTL_CODE_TABLE	TABLE

Object

Property

Value

Object

Property

Value

/****** Object: View [dbo].[arch_AR_Receivables] Script Date: 03/09/2017 16:47:08 ******/
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE VIEW [dbo].[arch_AR_Receivables]
AS
  SELECT
       CASE
         WHEN Recv.STUDENT_ID IS NULL and Recv.TERM IS NULL THEN 'Non-Coverage'
         ELSE 'Coverage'
       END AS CoverageType
      ,CASE
          WHEN ISDATE(Recv.ADD_DATE) = 0 THEN NULL
          WHEN Recv.ADD_TIME IS NULL THEN CONVERT(DATETIME, Recv.ADD_DATE + ' '
             + '00:00:00.000')
          ELSE CONVERT(DATETIME, Recv.ADD_DATE + ' '
             + STUFF(STUFF(STUFF(RIGHT('0000000' + Recv.ADD_TIME,7),3,0,':'),6,0,':'),9,0,'.'))
          END AS AddDateTime
      ,Recv.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
      ,Recv.ADD_PGM AS AddProgram
      ,CASE
          WHEN ISDATE(Recv.MAINT_DATE) = 0 THEN NULL
          WHEN Recv.MAINT_TIME IS NULL THEN CONVERT(DATETIME, Recv.MAINT_DATE + ' '
             + '00:00:00.000')
          ELSE CONVERT(DATETIME, Recv.MAINT_DATE + ' '
             + STUFF(STUFF(STUFF(RIGHT('0000000' + Recv.MAINT_TIME,7),3,0,':'),6,0,':'),9,0,'.'))
          END AS MaintDateTime
      ,Recv.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
      ,Recv.MAINT_PGM AS MaintProgram
      ,FISCAL_YEAR as FiscalYear
      ,ISNULL(TERM,AddTerm.Term_YR) AS Term
      ,RECEIVABLE_NBR as ReceivableNumber
      ,RECEIVABLE_SEQ as ReceivableSeqNumber
      ,RECEIVABLE_REC_TYPE as ReceivableTypeCode
      ,ISNULL(RecvType.RECEIVABLE_DESCRIPTION,'N/A') AS ReceivableType
      ,RECEIVABLE_STATUS as ReceivableStatusCode
      ,ISNULL(RecvStat.DESCRIPTION,'N/A') AS ReceivableStatus
      ,CONVERT(DATE,Recv.ADD_DATE) AS ReceivableAddDate
      ,CONVERT(DATE,RECEIVABLE_DATE) AS ReceivableDate
      ,Recv.CUSTOMER_ID AS CustomerId
      ,Cust.CUSTOMER_NAME as CustomerName
      ,ISNULL(CONTRACT_ID,0) AS ContractId
      ,Recv.STUDENT_ID AS StudentId
      ,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  ' '
       END AS StudentName
      ,ISNULL(BILL_INSTALL_NUM,0) AS BillInstallmentNumber
      ,CASE
         WHEN OVERPAID_FLAG = 'Y' THEN 'Yes'
         ELSE 'No'
       END AS OverPaidInd
      ,CASE
         WHEN BANK_IND = 'Y' THEN 'Yes'
         ELSE ' '
       END AS BankInd
      ,ISNULL(DUNNING_LETTER_COUNT,0) AS DunningLetterCount
      ,HOLD_TYPE AS HoldType
      ,CONVERT(DATE,HOLD_DATE) AS HoldDate
      ,ISNULL(SPECIAL_CHARGE_DESC,'N/A') as SpecialChargeDescription
      ,ISNULL(AMOUNT,0) AS ReceivableAmount
      ,ISNULL(AMOUNT_PAID,0) AS AmountPaid
      ,ISNULL(AMOUNT_DUE,0) AS AmountDue
      ,CASE
         WHEN COLLECTION_FLAG = 'NC' THEN 'Do not Assigned to Collections'
         WHEN COLLECTION_FLAG = 'NR' THEN 'Do not Return from Collections'
         ELSE 'N/A'
       END AS SendToCollections
  FROM AR_RECEIVABLES_A Recv
  LEFT OUTER JOIN ST_STDNT_A Stdnt
  ON
  (
     Recv.STUDENT_ID = Stdnt.STUDENT_ID
  )
  LEFT OUTER JOIN AR_CUSTOMER_A Cust
  ON
  (
     Cust.CUSTOMER_ID = Recv.CUSTOMER_ID
  )
  LEFT OUTER JOIN UTL_CODE_TABLE RecvStat
  ON
  (
     RecvStat.TABLE_NAME = 'RECV-STS' AND
     RecvStat.CODE = Recv.RECEIVABLE_STATUS AND
     RecvStat.STATUS = 'A'
  )
  LEFT OUTER JOIN AR_STANDARD_TABLES_A RecvType
  ON
  (
     RecvType.RECEIVABLE_TYPE = Recv.RECEIVABLE_REC_TYPE
  )
  OUTER APPLY
  (
    SELECT TOP 1
        CAST(Term.TRM_YR AS int) AS Term_YR
    FROM ST_TERM_A Term
    INNER JOIN ST_SESSION_A Sess
        ON (
        Term.TRM_YR = LEFT(Sess.SESSION_KEY,5)
        )
    WHERE SUBSTRING (Sess.SESSION_KEY,7,4) = '1 '
    ORDER BY
        ABS(CAST(CONVERT(DATE,Recv.ADD_DATE) - dbo.udf_StringToDate(SESS_BEG_DT) AS int))
        ,CAST(CONVERT(DATE,Recv.ADD_DATE) - dbo.udf_StringToDate(SESS_BEG_DT) AS int)
    ) AS AddTerm
  LEFT OUTER JOIN SECURITY_USER_A2 SecAdd
  ON
  (
     SecAdd.USER_ID = Recv.ADD_USER_ID
  )
  LEFT OUTER JOIN SECURITY_USER_A2 SecMaint
  ON
  (
     SecMaint.USER_ID = Recv.MAINT_USER_ID
  )