V_QUERY_TRANSACTION
Provides the ability to query transactions.
Fields
| Field | Field Type | Null | Description |
|---|---|---|---|
| TRANSACTIONID | uniqueidentifier | System record ID | |
| TRANSACTIONTYPE | nvarchar(19) | yes | Transaction type |
| PAYMENTMETHOD | nvarchar(13) | yes | Payment method |
| DATE | datetime | Date | |
| AMOUNT | money | Amount | |
| CONSTITUENTID | uniqueidentifier | yes | Constituent ID |
| CHECKDATE | UDT_FUZZYDATE | yes | Check date |
| CHECKNUMBER | nvarchar(20) | yes | Check number |
| CASHREFERENCEDATE | UDT_FUZZYDATE | yes | Cash Reference date |
| CASHREFERENCENUMBER | nvarchar(20) | yes | Cash Reference number |
| DIRECTDEBITREFERENCEDATE | UDT_FUZZYDATE | yes | Direct Debit Reference date |
| DIRECTDEBITREFERENCENUMBER | nvarchar(20) | yes | Direct Debit Reference number |
| DIRECTDEBITACCOUNTID | uniqueidentifier | yes | Direct Debit Account |
| CARDHOLDERNAME | nvarchar(100) | yes | Card holder |
| CREDITTYPE | nvarchar(100) | yes | Credit type |
| EXPIRESON | UDT_FUZZYDATE | yes | Credit card expires on |
| AUTHORIZATIONCODE | nvarchar(20) | yes | Authorization code |
| ISSUER | nvarchar(100) | yes | Stock Issuer |
| NUMBEROFUNITS | int | yes | Stock Number of Units |
| SYMBOL | nvarchar(4) | yes | Stock Symbol |
| MEDIANPRICE | money | yes | Stock Median Price |
| STOCKSALEDATE | datetime | yes | Stock Sale Date |
| STOCKSALEAMOUNT | money | yes | Stock Sale Amount |
| STOCKBROKERFEE | money | yes | Stock Broker Fee |
| STOCKPOSTDATE | datetime | yes | Stock Sale Post Date |
| STOCKPOSTSTATUS | nvarchar(11) | yes | Stock Sale Post Status |
| PROPERTYSUBTYPECODE | nvarchar(100) | yes | Property Subtype Code |
| PROPERTYSALEDATE | datetime | yes | Property Sale Date |
| PROPERTYSALEAMOUNT | money | yes | Property Sale Amount |
| PROPERTYBROKERFEE | money | yes | Property Broker Fee |
| PROPERTYPOSTDATE | datetime | yes | Property Sale Post Date |
| PROPERTYPOSTSTATUS | nvarchar(11) | yes | Property Sale Post Status |
| PLEDGEBALANCE | money | yes | Pledge balance |
| PLEDGEFIRSTINSTALLMENT | datetime | yes | Pledge first installment due |
| PLEDGELASTINSTALLMENT | datetime | yes | Pledge last installment due |
| PLEDGELASTINSTALLMENTAMOUNT | money | yes | Pledge last installment amount |
| LATESTPAYMENTAMOUNT | money | yes | Latest payment amount |
| LATESTPAYMENTDATE | datetime | yes | Latest payment date |
| GIFTINKINDSUBTYPECODE | nvarchar(100) | yes | Gift-in-kind subtype |
| POSTDATE | datetime | yes | GL post date |
| POSTSTATUS | varchar(11) | GL post status | |
| DONOTRECEIPT | bit | Do not receipt | |
| RECEIPTAMOUNT | money | Receipt amount | |
| RECEIPTDATE | datetime | yes | Receipt date |
| RECEIPTNUMBER | int | yes | Receipt number |
| GIVENANONYMOUSLY | bit | Given anonymously | |
| SENDPLEDGEREMINDER | bit | yes | Send pledge reminder |
| ISPENDING | bit | yes | Is pending |
| STARTDATE | datetime | yes | Installments start date |
| ENDDATE | datetime | yes | Installments end date |
| NUMBEROFINSTALLMENTS | int | yes | Number of installments |
| FREQUENCY | nvarchar(18) | yes | Installment frequency |
| NEXTTRANSACTIONDATE | datetime | yes | Recurring gift next transaction date |
| STATUS | nvarchar(10) | yes | Recurring gift status |
| PLEDGESUBTYPE | nvarchar(100) | yes | Pledge subtype |
| PLEDGESUBTYPEPOST | bit | yes | Pledge subtype (Post to GL) |
| MISSEDPAYMENTS | int | yes | Recurring gift missed payments |
| ADDEDBY_APPLICATION | nvarchar(200) | yes | Added by application |
| ADDEDBY_USERNAME | nvarchar(128) | yes | Added by user name |
| RECEIPTTYPE | nvarchar(12) | yes | Receipt type |
| NEEDSRERECEIPT | bit | Eligible for re-receipt | |
| RECEIPTSTATUS | nvarchar(50) | yes | Receipt status |
Definition
Copy
/*
Generated by Blackbaud AppFx Platform
Date: 12/17/2008 2:33:54 AM
Assembly Version: Blackbaud.AppFx.Platform.SqlClr, Version=1.7.1271.0, Culture=neutral, PublicKeyToken=null
Copyright Blackbaud
*/
CREATE VIEW dbo.V_QUERY_TRANSACTION AS
select
REVENUE.ID as TRANSACTIONID,
REVENUE.TRANSACTIONTYPE,
REVENUEPAYMENTMETHOD.PAYMENTMETHOD as PAYMENTMETHOD,
REVENUE.DATE,
REVENUE.AMOUNT,
REVENUE.CONSTITUENTID,
[CHECK].CHECKDATE as CHECKDATE,
[CHECK].CHECKNUMBER as CHECKNUMBER,
CASH.REFERENCEDATE as CASHREFERENCEDATE,
CASH.REFERENCENUMBER as CASHREFERENCENUMBER,
DIRECTDEBIT.REFERENCEDATE as DIRECTDEBITREFERENCEDATE,
DIRECTDEBIT.REFERENCENUMBER as DIRECTDEBITREFERENCENUMBER,
DIRECTDEBIT.CONSTITUENTACCOUNTID as DIRECTDEBITACCOUNTID,
CREDIT.CARDHOLDERNAME as CARDHOLDERNAME,
CREDITTYPECODE.DESCRIPTION as CREDITTYPE,
CREDIT.EXPIRESON as EXPIRESON,
CREDIT.AUTHORIZATIONCODE as AUTHORIZATIONCODE,
STOCK.ISSUER as ISSUER,
STOCK.NUMBEROFUNITS as NUMBEROFUNITS,
STOCK.SYMBOL as SYMBOL,
STOCK.MEDIANPRICE as MEDIANPRICE,
STOCK.SALEDATE as STOCKSALEDATE,
STOCK.SALEAMOUNT as STOCKSALEAMOUNT,
STOCK.BROKERFEE as STOCKBROKERFEE,
STOCK.SALEPOSTDATE as STOCKPOSTDATE,
STOCK.SALEPOSTSTATUS as STOCKPOSTSTATUS,
PROPERTYCODE.DESCRIPTION as PROPERTYSUBTYPECODE,
PROPERTY.SALEDATE as PROPERTYSALEDATE,
PROPERTY.SALEAMOUNT as PROPERTYSALEAMOUNT,
PROPERTY.BROKERFEE as PROPERTYBROKERFEE,
PROPERTY.SALEPOSTDATE as PROPERTYPOSTDATE,
PROPERTY.SALEPOSTSTATUS as PROPERTYPOSTSTATUS,
case
when REVENUE.TRANSACTIONTYPECODE = 1 or REVENUE.TRANSACTIONTYPECODE = 3 then
(select dbo.UFN_PLEDGE_GETBALANCE(REVENUE.ID))
else
0 end as [PLEDGEBALANCE],
case
when REVENUE.TRANSACTIONTYPECODE = 1 or REVENUE.TRANSACTIONTYPECODE = 3 then
(select top 1 INSTALLMENT.DATE from dbo.INSTALLMENT where REVENUEID = REVENUE.ID order by DATE asc)
else
null end as [PLEDGEFIRSTINSTALLMENT],
case
when REVENUE.TRANSACTIONTYPECODE = 1 or REVENUE.TRANSACTIONTYPECODE = 3 then
(select top 1 INSTALLMENT.DATE from dbo.INSTALLMENT where REVENUEID = REVENUE.ID order by DATE desc)
else
null end as [PLEDGELASTINSTALLMENT],
case
when REVENUE.TRANSACTIONTYPECODE = 1 or REVENUE.TRANSACTIONTYPECODE = 3 then
(select top 1 INSTALLMENT.AMOUNT from dbo.INSTALLMENT where REVENUEID = REVENUE.ID order by DATE desc)
else
null end as [PLEDGELASTINSTALLMENTAMOUNT],
case
when REVENUE.TRANSACTIONTYPECODE = 1 or REVENUE.TRANSACTIONTYPECODE = 3 then
(
select top 1 coalesce([PAYMENT].AMOUNT, 0) from dbo.REVENUE as [PAYMENT]
where [PAYMENT].ID in
(
select PAYMENTID from dbo.INSTALLMENTPAYMENT where PLEDGEID = REVENUE.ID
)
order by [PAYMENT].DATE desc
)
when REVENUE.TRANSACTIONTYPECODE = 2 then
(
select top 1 coalesce([PAYMENT].AMOUNT, 0)
from dbo.RECURRINGGIFTACTIVITY as [RGA]
inner join dbo.REVENUE as [PAYMENT] on [PAYMENT].ID = [RGA].PAYMENTREVENUEID
where SOURCEREVENUEID = REVENUE.ID
order by [PAYMENT].DATE desc
)
else
null end as [LATESTPAYMENTAMOUNT],
case
when REVENUE.TRANSACTIONTYPECODE = 1 or REVENUE.TRANSACTIONTYPECODE = 3 then
(
select top 1 coalesce([PAYMENT].DATE, 0) from dbo.REVENUE as [PAYMENT]
where [PAYMENT].ID in
(
select PAYMENTID from dbo.INSTALLMENTPAYMENT where PLEDGEID = REVENUE.ID
)
order by [PAYMENT].DATE desc
)
when REVENUE.TRANSACTIONTYPECODE = 2 then
(
select top 1 coalesce([PAYMENT].DATE, 0)
from dbo.RECURRINGGIFTACTIVITY as [RGA]
inner join dbo.REVENUE as [PAYMENT] on [PAYMENT].ID = [RGA].PAYMENTREVENUEID
where SOURCEREVENUEID = REVENUE.ID
order by [PAYMENT].DATE desc
)
else
null end as [LATESTPAYMENTDATE],
GIKCODE.DESCRIPTION as [GIFTINKINDSUBTYPECODE],
REVENUE.POSTDATE,
case when REVENUE.DONOTPOST = 1 then 'Do Not Post'
when REVENUEPOSTED.ID is not null then 'Posted'
else 'Not Posted' end as [POSTSTATUS],
REVENUE.DONOTRECEIPT as DONOTRECEIPT,
REVENUE.RECEIPTAMOUNT,
(select top 1 RR.RECEIPTDATE from dbo.REVENUERECEIPT RR where RR.REVENUEID = REVENUE.ID order by RECEIPTPROCESSDATE desc) as RECEIPTDATE,
(select top 1 RR.RECEIPTNUMBER from dbo.REVENUERECEIPT RR where RR.REVENUEID = REVENUE.ID order by RECEIPTPROCESSDATE desc) as RECEIPTNUMBER,
REVENUE.GIVENANONYMOUSLY,
SCHEDULE.SENDPLEDGEREMINDER,
SCHEDULE.ISPENDING,
SCHEDULE.STARTDATE,
SCHEDULE.ENDDATE,
SCHEDULE.NUMBEROFINSTALLMENTS,
SCHEDULE.FREQUENCY,
SCHEDULE.NEXTTRANSACTIONDATE,
SCHEDULE.STATUS,
PLEDGESUBTYPE.NAME as [PLEDGESUBTYPE],
PLEDGESUBTYPE.POSTTOGL as [PLEDGESUBTYPEPOST],
case
when REVENUE.TRANSACTIONTYPECODE = 2 then
dbo.UFN_REVENUE_GETMISSEDTRANSACTIONCOUNT(REVENUE.ID, getdate())
+
dbo.UFN_REVENUE_GETSKIPPEDTRANSACTIONCOUNT(REVENUE.ID, getdate())
else
null end as [MISSEDPAYMENTS],
ADDEDBY.APPLICATIONNAME as ADDEDBY_APPLICATION,
ADDEDBY.USERNAME as ADDEDBY_USERNAME,
REVENUE.RECEIPTTYPE,
REVENUE.NEEDSRERECEIPT,
dbo.UFN_REVENUETRANSACTION_GETRECEIPTSTATUS(REVENUE.ID) as RECEIPTSTATUS
from dbo.REVENUE
left join dbo.REVENUEPAYMENTMETHOD on REVENUE.ID = REVENUEPAYMENTMETHOD.REVENUEID
left join dbo.CHANGEAGENT as ADDEDBY on ADDEDBY.ID = REVENUE.ADDEDBYID
left join dbo.CHANGEAGENT as CHANGEDBY on CHANGEDBY.ID = REVENUE.CHANGEDBYID
left join dbo.CASHPAYMENTMETHODDETAIL as CASH on CASH.ID = REVENUEPAYMENTMETHOD.ID
left join dbo.CHECKPAYMENTMETHODDETAIL as [CHECK] on [CHECK].ID = REVENUEPAYMENTMETHOD.ID
left join dbo.CREDITCARDPAYMENTMETHODDETAIL as CREDIT on CREDIT.ID = REVENUEPAYMENTMETHOD.ID
left join dbo.DIRECTDEBITPAYMENTMETHODDETAIL as DIRECTDEBIT on DIRECTDEBIT.ID = REVENUEPAYMENTMETHOD.ID
left join dbo.CREDITTYPECODE on CREDITTYPECODE.ID = CREDIT.CREDITTYPECODEID
left join dbo.STOCKDETAIL as STOCK on STOCK.ID = REVENUEPAYMENTMETHOD.ID
left join dbo.PROPERTYDETAIL as [PROPERTY] on [PROPERTY].ID = REVENUEPAYMENTMETHOD.ID
left join dbo.PROPERTYSUBTYPECODE as PROPERTYCODE on PROPERTYCODE.ID = [PROPERTY].PROPERTYSUBTYPECODEID
left join dbo.GIFTINKINDPAYMENTMETHODDETAIL as GIK on GIK.ID = REVENUEPAYMENTMETHOD.ID
left join dbo.GIFTINKINDSUBTYPECODE as GIKCODE on GIKCODE.ID = GIK.GIFTINKINDSUBTYPECODEID
left join dbo.REVENUESCHEDULE as SCHEDULE on SCHEDULE.ID = REVENUE.ID
left join dbo.REVENUEPOSTED on REVENUEPOSTED.ID = REVENUE.ID
left join dbo.PLEDGESUBTYPE on PLEDGESUBTYPE.ID = SCHEDULE.PLEDGESUBTYPEID