V_QUERY_CHARGE

Queries the charge objects in the system.

Fields

Field	Field Type	Null	Description
ID	uniqueidentifier		System record ID
DUEDATE	datetime		Due date
ISPASTDUE	int		Is past due
DAYSPASTDUE	int	yes	Days past due
ADDEDBY_APPLICATION	nvarchar(200)	yes	Added by application
ADDEDBY_USERNAME	nvarchar(128)	yes	Added by user name
CHANGEDBY_APPLICATION	nvarchar(200)	yes	Changed by application
CHANGEDBY_USERNAME	nvarchar(128)	yes	Changed by user name
DATEADDED	datetime		Date added
DATECHANGED	datetime		Date changed
TSLONG	bigint	yes	Timestamp value

Definition

Copy

/*
Generated by Blackbaud AppFx Platform
Date:  3/19/2013 1:42:20 AM
Assembly Version:  Blackbaud.AppFx.Platform.SqlClr, Version=3.0.504.0, Culture=neutral, PublicKeyToken=null
Copyright Blackbaud
*/
CREATE VIEW dbo.V_QUERY_CHARGE AS



select
  CHARGE.ID,
  CHARGE.DUEDATE,
  --The "GETEARLIESTTIME" date function has been inlined here for performance (the part with "cast(@DATE as date)")...

  (case when ((cast(CHARGE.DUEDATE as date) < cast(getdate() as date)) 
         and ((dbo.FINANCIALTRANSACTION.TRANSACTIONAMOUNT - isnull((select sum(FINANCIALTRANSACTIONLINEITEM_APPLICATION.TRANSACTIONAMOUNT)
                                                                    from dbo.FINANCIALTRANSACTION
                                                                    inner join dbo.FINANCIALTRANSACTIONLINEITEM as FINANCIALTRANSACTIONLINEITEM_TARGETLINEITEM on FINANCIALTRANSACTIONLINEITEM_TARGETLINEITEM.FINANCIALTRANSACTIONID = FINANCIALTRANSACTION.ID 
                                                                    inner join dbo.FINANCIALTRANSACTIONLINEITEM as FINANCIALTRANSACTIONLINEITEM_APPLICATION on FINANCIALTRANSACTIONLINEITEM_APPLICATION.TARGETLINEITEMID = FINANCIALTRANSACTIONLINEITEM_TARGETLINEITEM.ID
                                                                    where FINANCIALTRANSACTIONLINEITEM_APPLICATION.DELETEDON is null
                                                                    and FINANCIALTRANSACTION.ID = CHARGE.ID), 0)) > 0))
     then 1
     else 0
   end) as ISPASTDUE,
  --The "GETEARLIESTTIME" date function has been inlined here for performance (the part with "cast(@DATE as date)")...

  (case when ((cast(CHARGE.DUEDATE as date) < cast(getdate() as date))
         and ((dbo.FINANCIALTRANSACTION.TRANSACTIONAMOUNT - isnull((select sum(FINANCIALTRANSACTIONLINEITEM_APPLICATION.TRANSACTIONAMOUNT)
                                                                    from dbo.FINANCIALTRANSACTION
                                                                    inner join dbo.FINANCIALTRANSACTIONLINEITEM as FINANCIALTRANSACTIONLINEITEM_TARGETLINEITEM on FINANCIALTRANSACTIONLINEITEM_TARGETLINEITEM.FINANCIALTRANSACTIONID = FINANCIALTRANSACTION.ID 
                                                                    inner join dbo.FINANCIALTRANSACTIONLINEITEM as FINANCIALTRANSACTIONLINEITEM_APPLICATION on FINANCIALTRANSACTIONLINEITEM_APPLICATION.TARGETLINEITEMID = FINANCIALTRANSACTIONLINEITEM_TARGETLINEITEM.ID
                                                                    where (FINANCIALTRANSACTIONLINEITEM_APPLICATION.DELETEDON is null)
                                                                    and (FINANCIALTRANSACTION.ID = CHARGE.ID)), 0)) > 0))
     then datediff(d, cast(CHARGE.DUEDATE as date), cast(getdate() as date))
     else 0
   end) as DAYSPASTDUE,
  -- System fields

  ADDEDBY.APPLICATIONNAME as [ADDEDBY_APPLICATION],
  ADDEDBY.USERNAME as [ADDEDBY_USERNAME],
  CHANGEDBY.APPLICATIONNAME as [CHANGEDBY_APPLICATION],
  CHANGEDBY.USERNAME as [CHANGEDBY_USERNAME],
  dbo.CHARGE.[DATEADDED],
  dbo.CHARGE.[DATECHANGED],
  dbo.CHARGE.[TSLONG]
from dbo.CHARGE
inner join dbo.FINANCIALTRANSACTION on dbo.CHARGE.ID = dbo.FINANCIALTRANSACTION.ID
left join dbo.CHANGEAGENT as [ADDEDBY] on [ADDEDBY].ID = dbo.CHARGE.[ADDEDBYID]
left join dbo.CHANGEAGENT as [CHANGEDBY] on [CHANGEDBY].ID = dbo.CHARGE.[CHANGEDBYID]