V_QUERY_PAYMENT
This provides the ability to query for payments.
Fields
| Field | Field Type | Null | Description |
|---|---|---|---|
| ID | uniqueidentifier | System Record ID | |
| PAYMENTSOURCE | nvarchar(255) | Reference | |
| PAYMENTMETHOD | nvarchar(14) | yes | Payment method |
| REFERENCEDATE | date | yes | Reference date |
| AMOUNT | money | Payment amount | |
| PAYMENTDATE | datetime | yes | Payment date |
| POSTSTATUS | varchar(11) | Post status | |
| POSTDATE | date | yes | Post date |
| DEPOSITID | uniqueidentifier | yes | Deposit ID |
| DATEADDED | datetime | Date added | |
| DATECHANGED | datetime | Date changed | |
| TSLONG | bigint | yes | Timestamp value |
| BATCHNUMBER | nvarchar(100) | Batch number | |
| RECEIPTAMOUNT | money | Receipt amount | |
| RECEIPTDATE | datetime | yes | Receipt date |
| RECEIPTNUMBER | int | yes | Receipt number |
| 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 |
| CARDHOLDERNAME | nvarchar(255) | yes | Name on card |
| CREDITTYPE | nvarchar(100) | yes | Card type |
| EXPIRESON | UDT_FUZZYDATE | yes | Credit card expires on |
| AUTHORIZATIONCODE | nvarchar(20) | yes | Authorization code |
| OTHERPAYMETHODDESCRIPTION | nvarchar(100) | yes | Other method |
| OTHERPAYREFERENCEDATE | UDT_FUZZYDATE | yes | Other reference date |
| OTHERPAYREFERENCENUMBER | nvarchar(20) | yes | Other reference number |
| TRANSACTIONNUMBER | varchar(30) | yes | Deposit Number |
| 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 |
| RECEIPTSTATUS | nvarchar(50) | yes | Receipt status |
| ACCOUNTSYSTEM | nvarchar(50) | yes | Account system |
| BASECURRENCYID | uniqueidentifier | yes | Base currency ID |
| BASEEXCHANGERATEID | uniqueidentifier | yes | Base exchange rate |
| ORGANIZATIONAMOUNT | money | Payment amount (organization currency) | |
| ORGANIZATIONEXCHANGERATEID | uniqueidentifier | yes | Organization exchange rate |
| TRANSACTIONAMOUNT | money | Payment amount (transaction currency) | |
| TRANSACTIONCURRENCYID | uniqueidentifier | yes | Transaction currency ID |
| UNIQUERECEIPTNUMBER | nvarchar(20) | yes | Unique receipt number |
| RECEIPTSTACKINFOID | uniqueidentifier | yes | Receipt stack id |
| RERECEIPTDETAILS | nvarchar(300) | yes | Re-receipt details |
| RERECEIPTDATE | datetime | yes | Re-receipt date |
| NEWNUMBERONRERECEIPT | bit | yes | Generate new number on re-receipt |
| RERECEIPTREASONCODE | nvarchar(10) | yes | Re-receipt reason code |
| RERECEIPTREASONDESCRIPTION | nvarchar(50) | yes | Re-receipt reason description |
| CONSTITUENTID | uniqueidentifier | yes | Constituent ID |
| TRANSACTIONTYPE | nvarchar(21) | yes | Transaction type |
| FEE | money | yes | |
| NETAMOUNT | money | yes | |
| CREDITTYPECODEID | uniqueidentifier | yes | |
| OTHERPAYMENTMETHODCODEID | uniqueidentifier | yes | |
| PDACCOUNTSYSTEMID | uniqueidentifier | yes | |
| PAYPALREFERENCEDATE | UDT_FUZZYDATE | yes | |
| PAYPALREFERENCENUMBER | nvarchar(20) | yes | |
| VENMOREFERENCEDATE | UDT_FUZZYDATE | yes | |
| VENMOREFERENCENUMBER | nvarchar(20) | yes |
Definition
Copy
/*
Generated by Blackbaud AppFx Platform
Date: 5/3/2024 2:17:55 PM
Assembly Version: Blackbaud.AppFx.Platform.SqlClr, Version=4.0.3700.0, Culture=neutral, PublicKeyToken=null
Copyright Blackbaud
*/
CREATE VIEW dbo.V_QUERY_PAYMENT AS
select
FT.ID,
REX.REFERENCE as [PAYMENTSOURCE],
REVENUEPAYMENTMETHOD.PAYMENTMETHOD,
case when REVENUEPAYMENTMETHOD.PAYMENTMETHODCODE = 0 then case when isdate([CASH].REFERENCEDATE) <> 0 then convert(date, [CASH].REFERENCEDATE, 111) end
when REVENUEPAYMENTMETHOD.PAYMENTMETHODCODE = 1 then case when isdate([CHECK].CHECKDATE) <> 0 then convert(date, [CHECK].CHECKDATE, 111) end
when REVENUEPAYMENTMETHOD.PAYMENTMETHODCODE = 2 then null
when REVENUEPAYMENTMETHOD.PAYMENTMETHODCODE = 10 then case when isdate([OTHER].REFERENCEDATE) <> 0 then convert(date, [OTHER].REFERENCEDATE, 111) end
else null
end AS [REFERENCEDATE],
FT.BASEAMOUNT as AMOUNT,
cast(FT.DATE as datetime) as [PAYMENTDATE],
case FT.POSTSTATUSCODE when 3 then 'Do not post' else (case when (SELECT count(ID) from REVENUEPOSTED
WHERE REVENUEPOSTED.ID = FT.ID) = 1 then 'Posted' else 'Not posted' end ) end as [POSTSTATUS],
FT.POSTDATE,
BANKACCOUNTDEPOSITPAYMENT.DEPOSITID,
FT.DATEADDED,
FT.DATECHANGED,
FT.TSLONG,
REX.BATCHNUMBER,
REX.RECEIPTAMOUNT,
(select top 1 RR.RECEIPTDATE from dbo.REVENUERECEIPT RR where RR.REVENUEID = FT.ID order by RECEIPTPROCESSDATE desc) as RECEIPTDATE,
(select top 1 RR.RECEIPTNUMBER from dbo.REVENUERECEIPT RR where RR.REVENUEID = FT.ID order by RECEIPTPROCESSDATE desc) as RECEIPTNUMBER,
[CHECK].CHECKDATE,
[CHECK].CHECKNUMBER,
[CASH].REFERENCEDATE as [CASHREFERENCEDATE],
[CASH].REFERENCENUMBER as [CASHREFERENCENUMBER],
[CREDIT].CARDHOLDERNAME,
CREDITTYPECODE.DESCRIPTION as [CREDITTYPE],
[CREDIT].EXPIRESON,
[CREDIT].AUTHORIZATIONCODE,
[OTHERPAYCODE].DESCRIPTION as [OTHERPAYMETHODDESCRIPTION],
[OTHERPAY].REFERENCEDATE as [OTHERPAYREFERENCEDATE],
[OTHERPAY].REFERENCENUMBER as [OTHERPAYREFERENCENUMBER],
cast(BANKACCOUNTTRANSACTION_EXT.TRANSACTIONNUMBER as varchar) as [TRANSACTIONNUMBER],
[ADDEDBY].APPLICATIONNAME as [ADDEDBY_APPLICATION],
[ADDEDBY].USERNAME as [ADDEDBY_USERNAME],
[CHANGEDBY].APPLICATIONNAME as [CHANGEDBY_APPLICATION],
[CHANGEDBY].USERNAME as [CHANGEDBY_USERNAME],
dbo.UFN_REVENUE_GETRECEIPTSTATUS(FT.ID) as [RECEIPTSTATUS],
PDACCOUNTSYSTEM.NAME as [ACCOUNTSYSTEM],
case when FT.DELETEDON is null then isnull(REX.NONPOSTABLEBASECURRENCYID, V.BASECURRENCYID) else null end as BASECURRENCYID,
FT.BASEEXCHANGERATEID,
FT.ORGAMOUNT as ORGANIZATIONAMOUNT,
FT.ORGEXCHANGERATEID as ORGANIZATIONEXCHANGERATEID,
FT.TRANSACTIONAMOUNT as TRANSACTIONAMOUNT,
case when FT.DELETEDON is null then FT.TRANSACTIONCURRENCYID else null end as TRANSACTIONCURRENCYID,
CURRENTREVENUERECEIPT.UNIQUERECEIPTNUMBER as UNIQUERECEIPTNUMBER,
CURRENTREVENUERECEIPT.RECEIPTSTACKINFOID as RECEIPTSTACKINFOID,
REVENUERECEIPTRERECEIPTDETAIL.DETAILS as RERECEIPTDETAILS,
REVENUERECEIPTRERECEIPTDETAIL.DATEADDED as RERECEIPTDATE,
REVENUERECEIPTRERECEIPTDETAIL.NEWNUMBERONRERECEIPT as NEWNUMBERONRERECEIPT,
RERECEIPTREASONCODE.CODE as RERECEIPTREASONCODE,
RERECEIPTREASONCODE.DESCRIPTION as RERECEIPTREASONDESCRIPTION,
FT.CONSTITUENTID,
case FT.TYPECODE
when 0 then N'Payment'
when 1 then N'Pledge'
when 2 then N'Recurring gift'
when 3 then N'Matching gift claim'
when 4 then N'Planned gift'
when 5 then N'Order'
when 6 then N'Grant award'
when 7 then N'Auction donation'
when 8 then N'Donor challenge claim'
when 9 then N'Pending gift'
end as TRANSACTIONTYPE,
nullif([CREDIT].FEE, 0) [FEE],
nullif([CREDIT].NETAMOUNT, 0) [NETAMOUNT],
[CREDIT].[CREDITTYPECODEID],
[OTHERPAY].[OTHERPAYMENTMETHODCODEID],
[FT].[PDACCOUNTSYSTEMID],
[PAYPAL].REFERENCEDATE as [PAYPALREFERENCEDATE],
[PAYPAL].REFERENCENUMBER as [PAYPALREFERENCENUMBER],
[VENMO].REFERENCEDATE as [VENMOREFERENCEDATE],
[VENMO].REFERENCENUMBER as [VENMOREFERENCENUMBER]
from dbo.FINANCIALTRANSACTION FT
inner join dbo.REVENUE_EXT REX on REX.ID = FT.ID
inner join dbo.REVENUEPAYMENTMETHOD on REVENUEPAYMENTMETHOD.REVENUEID = FT.ID
left outer join dbo.V_BASECURRENCYFORFINANCIALTRANSACTION_I V with (noexpand) on FT.ID = V.FINANCIALTRANSACTIONID
left join dbo.BANKACCOUNTDEPOSITPAYMENT on BANKACCOUNTDEPOSITPAYMENT.ID = FT.ID
left join dbo.CHANGEAGENT as [ADDEDBY] on [ADDEDBY].ID = FT.ADDEDBYID
left join dbo.CHANGEAGENT as [CHANGEDBY] on [CHANGEDBY].ID = FT.CHANGEDBYID
left outer join dbo.CASHPAYMENTMETHODDETAIL as [CASH] on [CASH].ID = REVENUEPAYMENTMETHOD.ID
left outer join dbo.CHECKPAYMENTMETHODDETAIL as [CHECK] on [CHECK].ID = REVENUEPAYMENTMETHOD.ID
left outer join dbo.CREDITCARDPAYMENTMETHODDETAIL as [CREDIT] on [CREDIT].ID = REVENUEPAYMENTMETHOD.ID
left outer join dbo.OTHERPAYMENTMETHODDETAIL as [OTHER] on [OTHER].ID = REVENUEPAYMENTMETHOD.ID
left join dbo.OTHERPAYMENTMETHODDETAIL as [OTHERPAY] on [OTHERPAY].ID = REVENUEPAYMENTMETHOD.ID
left join dbo.OTHERPAYMENTMETHODCODE as [OTHERPAYCODE] on [OTHERPAYCODE].ID = [OTHERPAY].OTHERPAYMENTMETHODCODEID
left join dbo.PAYPALPAYMENTMETHODDETAIL as [PAYPAL] on [PAYPAL].ID = REVENUEPAYMENTMETHOD.ID
left join dbo.VENMOPAYMENTMETHODDETAIL as [VENMO] on [VENMO].ID = REVENUEPAYMENTMETHOD.ID
left join dbo.CREDITTYPECODE on CREDITTYPECODE.ID = [CREDIT].CREDITTYPECODEID
left join dbo.BANKACCOUNTTRANSACTION_EXT on BANKACCOUNTDEPOSITPAYMENT.DEPOSITID = BANKACCOUNTTRANSACTION_EXT.ID
left join (select RR.ID, RR.UNIQUERECEIPTNUMBER, RR.RECEIPTSTACKINFOID, RR.REVENUEID from dbo.REVENUERECEIPT RR where RR.ID = (select top 1 R.ID from dbo.REVENUERECEIPT R where R.REVENUEID = RR.REVENUEID order by R.RECEIPTPROCESSDATE desc)) as CURRENTREVENUERECEIPT on CURRENTREVENUERECEIPT.REVENUEID = FT.ID
left join dbo.REVENUERECEIPTRERECEIPTDETAIL on REVENUERECEIPTRERECEIPTDETAIL.REVENUERECEIPTID = CURRENTREVENUERECEIPT.ID
left join dbo.RERECEIPTREASONCODE on RERECEIPTREASONCODE.ID = REVENUERECEIPTRERECEIPTDETAIL.RERECEIPTREASONCODEID
left outer join dbo.PDACCOUNTSYSTEM on PDACCOUNTSYSTEM.ID = FT.PDACCOUNTSYSTEMID
where
FT.TYPECODE = 0 and FT.DELETEDON is null