V_QUERY_AMRPOIMPORTBATCHCONTROLREPORT
Fields
| Field | Field Type | Null | Description |
|---|---|---|---|
| ID | uniqueidentifier | ||
| EVENTNAME | nvarchar(100) | yes | |
| EVENTSTARTDATE | date | yes | |
| CONSTITUENTNAME | nvarchar(154) | yes | |
| ISINDIVIDUAL | int | yes | |
| REGISTRANTNAME | nvarchar(154) | yes | |
| REGISTRANTAMOUNT | money | yes | |
| ISO4217 | nvarchar(3) | yes | |
| DECIMALDIGITS | tinyint | yes | |
| CURRENCYSYMBOL | nvarchar(5) | yes | |
| SYMBOLDISPLAYSETTINGCODE | tinyint | yes | |
| AUCTIONITEMNAME | nvarchar(100) | yes | |
| AUCTIONITEMDONORNAME | nvarchar(154) | yes | |
| AUCTIONITEMTYPECODE | tinyint | yes | |
| REVENUETRANSACTIONAMOUNT | money | yes | |
| REVENUEPAYMENTMETHOD | nvarchar(14) | yes |
Definition
Copy
/*
Generated by Blackbaud AppFx Platform
Date: 3/19/2013 1:42:35 AM
Assembly Version: Blackbaud.AppFx.Platform.SqlClr, Version=3.0.504.0, Culture=neutral, PublicKeyToken=null
Copyright Blackbaud
*/
CREATE VIEW dbo.V_QUERY_AMRPOIMPORTBATCHCONTROLREPORT AS
select
EVENT.ID,
EVENT.NAME EVENTNAME,
EVENT.STARTDATE EVENTSTARTDATE,
null as CONSTITUENTNAME,
null as ISINDIVIDUAL,
null as REGISTRANTNAME,
null as REGISTRANTAMOUNT,
null as ISO4217,
null as DECIMALDIGITS,
null as CURRENCYSYMBOL,
null as SYMBOLDISPLAYSETTINGCODE,
null as AUCTIONITEMNAME,
null as AUCTIONITEMDONORNAME,
null as AUCTIONITEMTYPECODE,
null as REVENUETRANSACTIONAMOUNT,
null as REVENUEPAYMENTMETHOD
from
dbo.EVENT
union all
select
CONSTITUENT.ID,
null as EVENTNAME,
null as EVENTSTARTDATE,
CONSTITUENT.NAME CONSTITUENTNAME,
case
when CONSTITUENT.ISORGANIZATION = 0 and CONSTITUENT.ISGROUP = 0 then 1
else 0
end ISINDIVIDUAL,
null as REGISTRANTNAME,
null as REGISTRANTAMOUNT,
null as ISO4217,
null as DECIMALDIGITS,
null as CURRENCYSYMBOL,
null as SYMBOLDISPLAYSETTINGCODE,
null as AUCTIONITEMNAME,
null as AUCTIONITEMDONORNAME,
null as AUCTIONITEMTYPECODE,
null as REVENUETRANSACTIONAMOUNT,
null as REVENUEPAYMENTMETHOD
from
dbo.CONSTITUENT
union all
select
REGISTRANT.ID,
null as EVENTNAME,
null as EVENTSTARTDATE,
null as CONSTITUENTNAME,
null as ISINDIVIDUAL,
CONSTITUENT.NAME REGISTRANTNAME,
REGISTRANTREGISTRATION.AMOUNT REGISTRANTAMOUNT,
CURRENCY.ISO4217,
CURRENCY.DECIMALDIGITS,
CURRENCY.CURRENCYSYMBOL,
CURRENCY.SYMBOLDISPLAYSETTINGCODE,
null as AUCTIONITEMNAME,
null as AUCTIONITEMDONORNAME,
null as AUCTIONITEMTYPECODE,
null as REVENUETRANSACTIONAMOUNT,
null as REVENUEPAYMENTMETHOD
from
dbo.REGISTRANT
inner join dbo.CONSTITUENT on CONSTITUENT.ID = REGISTRANT.CONSTITUENTID
inner join dbo.EVENT on EVENT.ID = REGISTRANT.EVENTID
inner join dbo.CURRENCY on CURRENCY.ID = EVENT.BASECURRENCYID
left join dbo.REGISTRANTREGISTRATION on REGISTRANTREGISTRATION.REGISTRANTID = REGISTRANT.ID
union all
select
AUCTIONITEM.ID,
null as EVENTNAME,
null as EVENTSTARTDATE,
null as CONSTITUENTNAME,
null as ISINDIVIDUAL,
null as REGISTRANTNAME,
null as REGISTRANTAMOUNT,
null as ISO4217,
null as DECIMALDIGITS,
null as CURRENCYSYMBOL,
null as SYMBOLDISPLAYSETTINGCODE,
AUCTIONITEM.NAME AUCTIONITEMNAME,
CONSTITUENT.NAME AUCTIONITEMDONORNAME,
AUCTIONITEM.TYPECODE AUCTIONITEMTYPECODE,
null as REVENUETRANSACTIONAMOUNT,
null as REVENUEPAYMENTMETHOD
from
dbo.AUCTIONITEM
left join dbo.REVENUE on REVENUE.ID = AUCTIONITEM.REVENUEAUCTIONDONATIONID
left join dbo.CONSTITUENT on CONSTITUENT.ID = REVENUE.CONSTITUENTID
union all
select
REVENUE.ID,
null as EVENTNAME,
null as EVENTSTARTDATE,
CONSTITUENT.NAME as CONSTITUENTNAME,
null as ISINDIVIDUAL,
null as REGISTRANTNAME,
null as REGISTRANTAMOUNT,
CURRENCY.ISO4217,
CURRENCY.DECIMALDIGITS,
CURRENCY.CURRENCYSYMBOL,
CURRENCY.SYMBOLDISPLAYSETTINGCODE,
null as AUCTIONITEMNAME,
null as AUCTIONITEMDONORNAME,
null as AUCTIONITEMTYPECODE,
REVENUE.TRANSACTIONAMOUNT REVENUETRANSACTIONAMOUNT,
REVENUEPAYMENTMETHOD.PAYMENTMETHOD REVENUEPAYMENTMETHOD
from
dbo.REVENUE
inner join dbo.REVENUEPAYMENTMETHOD on REVENUEPAYMENTMETHOD.REVENUEID = REVENUE.ID
inner join dbo.CURRENCY on CURRENCY.ID = REVENUE.TRANSACTIONCURRENCYID
inner join dbo.CONSTITUENT on CONSTITUENT.ID = REVENUE.CONSTITUENTID