V_QUERY_REGISTRANTPAYMENT
This provides the ability to query for payments applied to event registrants.
Fields
| Field | Field Type | Null | Description |
|---|---|---|---|
| ID | uniqueidentifier | yes | System record ID |
| PAYMENTID | uniqueidentifier | yes | Revenue system ID |
| REGISTRANTID | uniqueidentifier | yes | Registrant system ID |
| PAYMENTSTOTALAMOUNT | money | yes | Total payment amount |
| DATEADDED | datetime | yes | Date added |
| DATECHANGED | datetime | yes | Date changed |
| TSLONG | bigint | yes | Timestamp value |
| ADDITIONALDONATION | money | yes | Additional donation amount |
| 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 |
| BASECURRENCYID | uniqueidentifier | yes | BASECURRENCYID |
| PAYMENTSTOTALORGANIZATIONAMOUNT | money | yes | Total payment amount (organization currency) |
| ORGANIZATIONEXCHANGERATEID | uniqueidentifier | yes | ORGANIZATIONEXCHANGERATEID |
Definition
Copy
/*
Generated by Blackbaud AppFx Platform
Date: 8/17/2011 2:38:24 PM
Assembly Version: Blackbaud.AppFx.Platform.SqlClr, Version=2.91.1535.0, Culture=neutral, PublicKeyToken=null
Copyright Blackbaud
*/
CREATE VIEW dbo.V_QUERY_REGISTRANTPAYMENT AS
with RELATEDREVENUE as
(
select
ERP.REGISTRANTID,
sum(RS.AMOUNT) as PAYMENTSTOTALAMOUNT,
RS.BASECURRENCYID,
sum(RS.ORGANIZATIONAMOUNT) as PAYMENTSTOTALORGANIZATIONAMOUNT,
RS.ORGANIZATIONEXCHANGERATEID
from
dbo.EVENTREGISTRANTPAYMENT ERP
left join dbo.REVENUESPLIT RS on ERP.PAYMENTID = RS.ID
group by
ERP.REGISTRANTID, RS.BASECURRENCYID, RS.ORGANIZATIONEXCHANGERATEID
)
select
ERP.ID,
ERP.PAYMENTID,
ERP.REGISTRANTID,
RELATEDREVENUE.PAYMENTSTOTALAMOUNT,
ERP.DATEADDED,
ERP.DATECHANGED,
ERP.TSLONG,
RD.VOLUNTARYDONATION as ADDITIONALDONATION,
ADDEDBY.APPLICATIONNAME as ADDEDBY_APPLICATION,
ADDEDBY.USERNAME as ADDEDBY_USERNAME,
CHANGEDBY.APPLICATIONNAME as CHANGEDBY_APPLICATION,
CHANGEDBY.USERNAME as CHANGEDBY_USERNAME,
RELATEDREVENUE.BASECURRENCYID,
RELATEDREVENUE.PAYMENTSTOTALORGANIZATIONAMOUNT,
RELATEDREVENUE.ORGANIZATIONEXCHANGERATEID
/*#EXTENSION*/
from
RELATEDREVENUE
left join dbo.EVENTREGISTRANTPAYMENT as ERP on ERP.REGISTRANTID = RELATEDREVENUE.REGISTRANTID
left join dbo.REGISTRANTDONATION as RD ON RD.REGISTRANTID = ERP.REGISTRANTID
left join dbo.CHANGEAGENT as ADDEDBY on ADDEDBY.ID = ERP.ADDEDBYID
left join dbo.CHANGEAGENT as CHANGEDBY on CHANGEDBY.ID = ERP.CHANGEDBYID