V_QUERY_BBNCEVENTREGISTRATIONBATCHCONTROLREPORT
View used for output by the Blackbaud Internet Solutions Event Registration Batch Control Report.
Fields
| Field | Field Type | Null | Description |
|---|---|---|---|
| ID | uniqueidentifier | yes | System record ID |
| DONORNAME | nvarchar(154) | yes | |
| CONSTITUENTLOOKUPID | nvarchar(100) | yes | |
| BATCHID | uniqueidentifier | ||
| EVENT | nvarchar(100) | yes | |
| EVENTPRICEID | uniqueidentifier | yes | |
| DESIGNATION | nvarchar(512) | yes | |
| AMOUNT | decimal(18, 0) | yes | |
| CURRENCYID | uniqueidentifier | yes | |
| NUMBEROFREGISTRANTS | int | yes | |
| BATCHBBNCEVENTREGISTRATIONID | uniqueidentifier | ||
| DATE | datetime | yes | |
| PAYMENTMETHOD | nvarchar(max) | yes | |
| ISPAYMENT | int | yes | |
| ISPLEDGE | int | yes | |
| APPEAL | nvarchar(100) | yes |
Definition
Copy
/*
Generated by Blackbaud AppFx Platform
Date: 4/26/2019 6:00:56 AM
Assembly Version: Blackbaud.AppFx.Platform.SqlClr, Version=4.0.2006.0, Culture=neutral, PublicKeyToken=null
Copyright Blackbaud
*/
CREATE VIEW dbo.V_QUERY_BBNCEVENTREGISTRATIONBATCHCONTROLREPORT AS
select
REGISTRATION_INFO.ID,
REGISTRATION_INFO.DONORNAME,
REGISTRATION_INFO.CONSTITUENTLOOKUPID,
REGISTRATION_INFO.BATCHID,
REGISTRATION_INFO.EVENT,
REGISTRATION_INFO.EVENTPRICEID,
REGISTRATION_INFO.DESIGNATION,
REGISTRATION_INFO.AMOUNT,
REGISTRATION_INFO.CURRENCYID,
REGISTRATION_INFO.NUMBEROFREGISTRANTS,
REGISTRATION_INFO.BATCHBBNCEVENTREGISTRATIONID,
REGISTRATION_INFO.DATE,
COALESCE(IIF(REGISTRATION_INFO.AMOUNT = 0, NULL, PAYMENT_INFO.PAYMENTMETHOD), '') AS PAYMENTMETHOD,
COALESCE(IIF(REGISTRATION_INFO.AMOUNT = 0, 0, PAYMENT_INFO.ISPAYMENT), '') AS ISPAYMENT,
COALESCE(IIF(REGISTRATION_INFO.AMOUNT = 0, 0, PAYMENT_INFO.ISPLEDGE), '') AS ISPLEDGE,
COALESCE(IIF(REGISTRATION_INFO.AMOUNT = 0, NULL, APPEAL_INFO.APPEAL), '') AS APPEAL
from
(select
TT.SAVEDRECORDID AS ID,
CONSTITUENT.NAME AS DONORNAME,
CONSTITUENT.LOOKUPID as CONSTITUENTLOOKUPID,
BER.BATCHID,
EVENT.NAME as EVENT,
COALESCE(DESIGNATION.NAME, '') As DESIGNATION,
R.C.value('EVENTPRICEID[1]', 'uniqueidentifier') EVENTPRICEID,
R.C.value('AMOUNT[1]', 'decimal') AMOUNT,
R.C.value('TRANSACTIONCURRENCYID[1]', 'uniqueidentifier') CURRENCYID,
(R.C.value('QUANTITY[1]', 'int') * R.C.value('REGISTRATIONCOUNT[1]', 'int')) as NUMBEROFREGISTRANTS,
BER.ID AS BATCHBBNCEVENTREGISTRATIONID,
BER.REGISTRATIONDATE AS DATE
from
dbo.batchbbnceventregistration BER
CROSS APPLY registrations.nodes('//REGISTRATIONS/ITEM') R(C)
left join EVENT
on EVENT.ID = R.C.value('EVENTID[1]', 'uniqueidentifier')
left join EVENTDESIGNATION
on EVENTDESIGNATION.EVENTID = EVENT.ID
and EVENTDESIGNATION.[DEFAULT] = 1
left join DESIGNATION
on DESIGNATION.ID = EVENTDESIGNATION.DESIGNATIONID
left join BATCHRESULTSC66C39BC_ACD1_4C30_98DB_C5BA8F90EBEC TT
on TT.ID = BER.ID
left join CONSTITUENT
on CONSTITUENT.ID = TT.SAVEDRECORDID ) AS REGISTRATION_INFO
left join
(select
P.C.value('NEWVALUE[1]', 'nvarchar(max)') as PAYMENTMETHOD,
(IIF(P.C.value('NEWVALUE[1]', 'nvarchar(max)') = 'pledge', 1, 0)) AS ISPLEDGE,
(IIF(P.C.value('NEWVALUE[1]', 'nvarchar(max)') <> 'pledge', 1, 0)) AS ISPAYMENT,
ID AS BATCHBBNCEVENTREGISTRATIONID
from
dbo.batchbbnceventregistration
CROSS APPLY giftinformation.nodes('//GIFTINFORMATIONS/GIFTINFORMATION/ITEM') P(C)
where
P.C.value('FIELD[1]', 'nvarchar(max)') like 'Payment method') AS PAYMENT_INFO
on REGISTRATION_INFO.BATCHBBNCEVENTREGISTRATIONID = PAYMENT_INFO.BATCHBBNCEVENTREGISTRATIONID
left join
(select
Appeal.NAME AS APPEAL,
BER.ID AS BATCHBBNCEVENTREGISTRATIONID
from
dbo.batchbbnceventregistration BER
CROSS APPLY giftinformation.nodes('//GIFTINFORMATIONS/APPEALCAMPAIGNS/ITEM/APPEAL/ITEM') AP(C)
left join Appeal
on APPEAL.ID = AP.C.value('APPEALID[1]', 'uniqueidentifier')) AS APPEAL_INFO
on REGISTRATION_INFO.BATCHBBNCEVENTREGISTRATIONID = APPEAL_INFO.BATCHBBNCEVENTREGISTRATIONID