V_QUERY_BATCHMEMBERSHIPDUES_EXCEPTIONREPORT

Fields

Field	Field Type	Null
ID	uniqueidentifier
ORIGINALBATCHNUMBER	nvarchar(100)
BATCHNUMBER	nvarchar(100)	yes
OWNER	nvarchar(128)	yes
NAME	nvarchar(154)	yes
LOOKUPID	nvarchar(100)	yes
DATE	date	yes
PAYMENTMETHOD	nvarchar(11)	yes
MEMBERSHIPTRANSACTIONTYPECODE	tinyint
PROGRAMTYPECODE	tinyint
REVENUETYPECODE	tinyint
TOTALAMOUNT	money
DONATIONAMOUNT	money
MEMBERSHIPPLEDGEAMOUNT	money
ADDONAMOUNT	money	yes
BATCHID	uniqueidentifier
SEQUENCE	int
CURRENCYNAME	nvarchar(100)
ISO4217	nvarchar(3)
DECIMALDIGITS	tinyint
CURRENCYSYMBOL	nvarchar(5)
SYMBOLDISPLAYSETTINGCODE	tinyint
TRANSACTIONCURRENCYID	uniqueidentifier	yes

Definition

Copy

/*
Generated by Blackbaud AppFx Platform
Date:  11/11/2014 4:08:33 PM
Assembly Version:  Blackbaud.AppFx.Platform.SqlClr, Version=4.0.2.0, Culture=neutral, PublicKeyToken=null
Copyright Blackbaud
*/
CREATE VIEW dbo.V_QUERY_BATCHMEMBERSHIPDUES_EXCEPTIONREPORT AS



select
    BMD.ID
    , [ORIGINAL].BATCHNUMBER as [ORIGINALBATCHNUMBER]
    , BATCH.BATCHNUMBER as [BATCHNUMBER]
    , APPUSER.USERNAME as OWNER
    ,(
            -- There should never be more than 1 row returned from the union but just in case an ID was reused,

            -- in the two tables, "top 1" is used

            select top 1 U.NAME from
            (
                select NAME from dbo.UFN_CONSTITUENT_DISPLAYNAME(BMD.BILLTOCONSTITUENTID) NF
                union
                select NAME from dbo.BATCHREVENUECONSTITUENT where BATCHREVENUECONSTITUENT.ID = BMD.BILLTOCONSTITUENTID
            ) as U
        ) as NAME
    , (select LOOKUPID from dbo.CONSTITUENT where CONSTITUENT.ID = BMD.BILLTOCONSTITUENTID) as LOOKUPID

    , BMD.DATE
    , BMD.PAYMENTMETHOD
    , BMD.MEMBERSHIPTRANSACTIONTYPECODE
    , MP.PROGRAMTYPECODE
    , BMD.REVENUETYPECODE
    , BMD.TOTALAMOUNT
    , BMD.DONATIONAMOUNT
    , BMD.MEMBERSHIPPLEDGEAMOUNT
    , coalesce(S.ADDONAMOUNT, 0) as ADDONAMOUNT 

    , BMD.BATCHID
    , BMD.SEQUENCE

    , CURRENCY.NAME as CURRENCYNAME
    , CURRENCY.ISO4217
    , CURRENCY.DECIMALDIGITS
    , CURRENCY.CURRENCYSYMBOL
    , CURRENCY.SYMBOLDISPLAYSETTINGCODE
    , BMD.TRANSACTIONCURRENCYID
from dbo.BATCHMEMBERSHIPDUES BMD
    inner join dbo.MEMBERSHIPPROGRAM MP on MP.ID = BMD.MEMBERSHIPPROGRAMID
    inner join dbo.CURRENCY on CURRENCY.ID = BMD.TRANSACTIONCURRENCYID   
    inner join dbo.BATCH as ORIGINAL on ORIGINAL.ID = BMD.BATCHID
    inner join dbo.APPUSER on ORIGINAL.APPUSERID = APPUSER.ID
    left join dbo.BATCH on BATCH.ORIGINATINGBATCHID = ORIGINAL.ID
    left join (
        select 
            BMD.ID
            , case
                when BMD.BASECURRENCYID = BMD.TRANSACTIONCURRENCYID then 
                    sum(MPA.PRICE * BMDA.NUMBEROFADDONS) / coalesce(I.INSTALLMENTCOUNT, 1)
                else
                    -- get the exchange rate and then do an immediate conversion. We never look at a rate from the row, even if there is one.

                    dbo.UFN_CURRENCY_CONVERT(sum(MPA.PRICE * BMDA.NUMBEROFADDONS), dbo.UFN_CURRENCYEXCHANGERATE_GETLATEST(BMD.BASECURRENCYID, BMD.TRANSACTIONCURRENCYID, null, 1, null)) / coalesce(I.INSTALLMENTCOUNT, 1)
            end as ADDONAMOUNT
        from dbo.BATCHMEMBERSHIPDUES BMD
            inner join dbo.BATCHMEMBERSHIPDUESMEMBERSHIPROGRAMADDON BMDA on BMD.ID = BMDA.BATCHMEMBERSHIPDUESID
            inner join dbo.MEMBERSHIPPROGRAMADDON MPA on MPA.ADDONID = BMDA.ADDONID and BMD.MEMBERSHIPPROGRAMID = MPA.MEMBERSHIPPROGRAMID
            left join (
                select
                    BMD.ID
                    , COUNT(BPI.ID) as INSTALLMENTCOUNT
                from dbo.BATCHMEMBERSHIPDUES BMD
                    inner join dbo.BATCHMEMBERSHIPDUESPLEDGEINSTALLMENT BPI on BMD.ID = BPI.BATCHMEMBERSHIPDUESID
                group by BMD.ID
            ) I on I.ID = BMD.ID
        where BMD.REVENUETYPECODE <> 2 -- no pledge only

        group by 
            BMD.ID
            , BMD.BASECURRENCYID
            , BMD.TRANSACTIONCURRENCYID
            , I.INSTALLMENTCOUNT
    ) as S on S.ID = BMD.ID