View - dbo.arch_AR

Property	Value
Name	arch_AR_Adjustments
Schema	dbo
Quoted Identifier Status	True
Ansi Nulls Status	True
Returns View Metadata	False
Create Date	10/13/2016 2:16 PM
Description

Column Name	Data Type	Allow Nulls	Fulltext Indexed	Identity	Description / Computed Formula
AccountsPayableGroupId	int(10, 0)	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
AdjustmentAmount	numeric(17, 2)	True	False	False
AdjustmentSeqNumber	int(10, 0)	True	False	False
AdjustmentType	varchar(30)	False	False	False
AdjustmentTypeCode	varchar(2)	True	False	False
Amount	numeric(17, 2)	True	False	False
AmountDue	numeric(17, 2)	True	False	False
AmountPaid	numeric(17, 2)	True	False	False
BillingDueDate	date(10, 0)	True	False	False
BillingXrefKey	varchar(18)	True	False	False
CommentInd	varchar(3)	False	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
ObligationAmount	numeric(17, 2)	True	False	False
ObligationAmountDue	numeric(17, 2)	True	False	False
ObligationAmountPaid	numeric(17, 2)	True	False	False
ObligationId	varchar(9)	True	False	False
PaidObligationKey	varchar(9)	True	False	False
PaidReceivableKey	varchar(19)	True	False	False
ReceiptKey	varchar(24)	True	False	False
ReceivableFiscalYear	varchar(4)	True	False	False
ReceivableKey	varchar(16)	True	False	False
ReceivableNumber	varchar(9)	True	False	False
ReceivableSeqNumber	varchar(3)	True	False	False

Name

Insert

Update

Delete

Instead

Name

Clustered

Columns

Name	Type
AR_ADJUSTMENTS_A	Table
SECURITY_USER_A2	Table
UTL_CODE_TABLE	Table

Name	Type
AR_ADJUSTMENTS_A	TABLE
SECURITY_USER_A2	TABLE
UTL_CODE_TABLE	TABLE

Object

Property

Value

Object

Property

Value

/****** Object: View [dbo].[arch_AR_Adjustments] Script Date: 03/09/2017 16:47:08 ******/
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE VIEW [dbo].[arch_AR_Adjustments]
AS
SELECT
       CASE
          WHEN ISDATE(Adjst.ADD_DATE) = 0 THEN NULL
          WHEN Adjst.ADD_TIME IS NULL THEN CONVERT(DATETIME, Adjst.ADD_DATE + ' '
             + '00:00:00.000')
          ELSE CONVERT(DATETIME, Adjst.ADD_DATE + ' '
             + STUFF(STUFF(STUFF(RIGHT('0000000' + Adjst.ADD_TIME,7),3,0,':'),6,0,':'),9,0,'.'))
          END AS AddDateTime
      ,Adjst.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
      ,Adjst.ADD_PGM AS AddProgram
      ,CASE
          WHEN ISDATE(Adjst.MAINT_DATE) = 0 THEN NULL
          WHEN Adjst.MAINT_TIME IS NULL THEN CONVERT(DATETIME, Adjst.MAINT_DATE + ' '
             + '00:00:00.000')
          ELSE CONVERT(DATETIME, Adjst.MAINT_DATE + ' '
             + STUFF(STUFF(STUFF(RIGHT('0000000' + Adjst.MAINT_TIME,7),3,0,':'),6,0,':'),9,0,'.'))
          END AS MaintDateTime
      ,Adjst.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
      ,Adjst.MAINT_PGM AS MaintProgram
      ,RECEIVABLE_KEY AS ReceivableKey
      ,LEFT(RECEIVABLE_KEY,4) AS ReceivableFiscalYear
      ,SUBSTRING(RECEIVABLE_KEY,5,9) AS ReceivableNumber
      ,SUBSTRING(RECEIVABLE_KEY,14,3) AS ReceivableSeqNumber
      ,ADJUSTMENT_SEQ AS AdjustmentSeqNumber
      ,ADJUSTMENT_TYPE AS AdjustmentTypeCode
      ,CASE
          WHEN RECEIVABLE_KEY IS NOT NULL THEN ISNULL(RecvAdjst.DESCRIPTION,'N/A')
          ELSE ISNULL(OblgAdjst.DESCRIPTION,'N/A')
       END AS AdjustmentType
      ,ADJUSTMENT_AMOUNT AS AdjustmentAmount
      ,AMOUNT AS Amount
      ,AMOUNT_PAID AS AmountPaid
      ,AMOUNT_DUE AS AmountDue
      ,BILLING_XREF_KEY AS BillingXrefKey
      ,CONVERT(DATE, BILLING_DUE_DATE) AS BillingDueDate
      ,RECEIPT_KEY AS ReceiptKey
      ,CASE
          WHEN COMMENT_IND = 'Y' THEN 'Yes'
          ELSE 'No'
       END AS CommentInd
      ,RECV_PAID_KEY AS PaidReceivableKey
      ,OBLG_PAID_KEY AS PaidObligationKey
      ,AP_GROUP_ID AS AccountsPayableGroupId
      ,OBLIGATION_ID AS ObligationId
      ,OBLG_AMOUNT AS ObligationAmount
      ,OBLG_AMOUNT_PAID AS ObligationAmountPaid
      ,OBLG_AMOUNT_DUE AS ObligationAmountDue
  FROM AR_ADJUSTMENTS_A Adjst
  LEFT OUTER JOIN SECURITY_USER_A2 SecAdd
  ON
  (
     SecAdd.USER_ID = Adjst.ADD_USER_ID
  )
  LEFT OUTER JOIN SECURITY_USER_A2 SecMaint
  ON
  (
     SecMaint.USER_ID = Adjst.MAINT_USER_ID
  )
  LEFT OUTER JOIN UTL_CODE_TABLE RecvAdjst
  ON
  (
     RecvAdjst.TABLE_NAME = 'RECV-ADJST' AND
     RecvAdjst.CODE = Adjst.ADJUSTMENT_TYPE AND
     RecvAdjst.STATUS = 'A'
  )
  LEFT OUTER JOIN UTL_CODE_TABLE OblgAdjst
  ON
  (
     OblgAdjst.TABLE_NAME = 'OBLG-ADJST' AND
     OblgAdjst.CODE = Adjst.ADJUSTMENT_TYPE AND
     OblgAdjst.STATUS = 'A'
  )