V_QUERY_BILLINGTRANSACTION
Lets a user query for billing transactions.
Fields
| Field | Field Type | Null | Description |
|---|---|---|---|
| ID | uniqueidentifier | System record ID | |
| CONSTITUENTID | uniqueidentifier | Constituent ID | |
| USERDEFINEDID | nvarchar(100) | yes | Transaction ID |
| TYPE | nvarchar(27) | yes | Transaction type |
| AMOUNT | money | Amount | |
| ORIGINALAMOUNT | money | Original amount | |
| ISOPEN | int | Is open | |
| AMOUNTREMAINING | money | yes | Amount remaining |
| DATE | datetimeoffset | Date | |
| POSTDATE | date | yes | Post date |
| POSTSTATUS | nvarchar(11) | yes | Post status |
| DESCRIPTION | nvarchar(700) | yes | Description |
| 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:21:29 AM
Assembly Version: Blackbaud.AppFx.Platform.SqlClr, Version=3.0.504.0, Culture=neutral, PublicKeyToken=null
Copyright Blackbaud
*/
CREATE VIEW dbo.V_QUERY_BILLINGTRANSACTION AS
select FINANCIALTRANSACTION.ID,
CONSTITUENT.ID as CONSTITUENTID,
FINANCIALTRANSACTION.CALCULATEDUSERDEFINEDID USERDEFINEDID,
FINANCIALTRANSACTION.TYPE,
FINANCIALTRANSACTION.TRANSACTIONAMOUNT as AMOUNT,
FINANCIALTRANSACTION.TRANSACTIONAMOUNT as ORIGINALAMOUNT,
case when ((dbo.FINANCIALTRANSACTION.TRANSACTIONAMOUNT - isnull((select sum(FINANCIALTRANSACTIONLINEITEM_APPLICATION.TRANSACTIONAMOUNT)
from dbo.FINANCIALTRANSACTION as FINANCIALTRANSACTION_INNER
inner join dbo.FINANCIALTRANSACTIONLINEITEM as FINANCIALTRANSACTIONLINEITEM_SOURCEORTARGETLINEITEM
on FINANCIALTRANSACTIONLINEITEM_SOURCEORTARGETLINEITEM.FINANCIALTRANSACTIONID = FINANCIALTRANSACTION_INNER.ID
inner join dbo.FINANCIALTRANSACTIONLINEITEM as FINANCIALTRANSACTIONLINEITEM_APPLICATION
on -- Charge join to applied to line item
((FINANCIALTRANSACTION.TYPECODE = 104) and
(FINANCIALTRANSACTIONLINEITEM_SOURCEORTARGETLINEITEM.ID = FINANCIALTRANSACTIONLINEITEM_APPLICATION.TARGETLINEITEMID)) or
-- Payment or credit join to source line item
((FINANCIALTRANSACTION.TYPECODE in (105, 106)) and
(FINANCIALTRANSACTIONLINEITEM_SOURCEORTARGETLINEITEM.ID = FINANCIALTRANSACTIONLINEITEM_APPLICATION.SOURCELINEITEMID))
where (FINANCIALTRANSACTIONLINEITEM_APPLICATION.DELETEDON is null) and
(FINANCIALTRANSACTION_INNER.ID = FINANCIALTRANSACTION.ID)), 0)) > 0)
then
1
else
0
end as ISOPEN,
(dbo.FINANCIALTRANSACTION.TRANSACTIONAMOUNT - isnull((select sum(FINANCIALTRANSACTIONLINEITEM_APPLICATION.TRANSACTIONAMOUNT)
from dbo.FINANCIALTRANSACTION as FINANCIALTRANSACTION_INNER
inner join dbo.FINANCIALTRANSACTIONLINEITEM as FINANCIALTRANSACTIONLINEITEM_SOURCEORTARGETLINEITEM
on FINANCIALTRANSACTIONLINEITEM_SOURCEORTARGETLINEITEM.FINANCIALTRANSACTIONID = FINANCIALTRANSACTION_INNER.ID
inner join dbo.FINANCIALTRANSACTIONLINEITEM as FINANCIALTRANSACTIONLINEITEM_APPLICATION
on -- Charge join to applied to line item
((FINANCIALTRANSACTION.TYPECODE = 104) and
(FINANCIALTRANSACTIONLINEITEM_SOURCEORTARGETLINEITEM.ID = FINANCIALTRANSACTIONLINEITEM_APPLICATION.TARGETLINEITEMID)) or
-- Payment or credit join to source line item
((FINANCIALTRANSACTION.TYPECODE in (105, 106)) and
(FINANCIALTRANSACTIONLINEITEM_SOURCEORTARGETLINEITEM.ID = FINANCIALTRANSACTIONLINEITEM_APPLICATION.SOURCELINEITEMID))
where (FINANCIALTRANSACTIONLINEITEM_APPLICATION.DELETEDON is null) and
(FINANCIALTRANSACTION_INNER.ID = FINANCIALTRANSACTION.ID)), 0)) as AMOUNTREMAINING,
FINANCIALTRANSACTION.DATE,
FINANCIALTRANSACTION.POSTDATE,
FINANCIALTRANSACTION.POSTSTATUS,
(select TOP 1 FINANCIALTRANSACTIONLINEITEM.DESCRIPTION from dbo.FINANCIALTRANSACTIONLINEITEM where FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID=FINANCIALTRANSACTION.ID) AS DESCRIPTION,
-- System fields
ADDEDBY.APPLICATIONNAME as [ADDEDBY_APPLICATION],
ADDEDBY.USERNAME as [ADDEDBY_USERNAME],
CHANGEDBY.APPLICATIONNAME as [CHANGEDBY_APPLICATION],
CHANGEDBY.USERNAME as [CHANGEDBY_USERNAME],
dbo.FINANCIALTRANSACTION.[DATEADDED],
dbo.FINANCIALTRANSACTION.[DATECHANGED],
dbo.FINANCIALTRANSACTION.[TSLONG]
from dbo.FINANCIALTRANSACTION
inner join dbo.CONSTITUENT
on FINANCIALTRANSACTION.CONSTITUENTID = CONSTITUENT.ID
left join dbo.CHANGEAGENT as [ADDEDBY]
on [ADDEDBY].ID = dbo.FINANCIALTRANSACTION.[ADDEDBYID]
left join dbo.CHANGEAGENT as [CHANGEDBY]
on [CHANGEDBY].ID = dbo.FINANCIALTRANSACTION.[CHANGEDBYID]
where FINANCIALTRANSACTION.TYPECODE in (104, 105, 106)