V_QUERY_BATCHMEMBERSHIPDUES_CONTROLREPORT

/*
Generated by Blackbaud AppFx Platform
Date:  3/19/2013 1:31:44 AM
Assembly Version:  Blackbaud.AppFx.Platform.SqlClr, Version=3.0.504.0, Culture=neutral, PublicKeyToken=null
Copyright Blackbaud
*/
CREATE VIEW dbo.V_QUERY_BATCHMEMBERSHIPDUES_CONTROLREPORT AS



select
    ID
    , TYPECODE
    , MEMBERSHIPID
    , PARENTID
    , PARENTTYPECODE
from
(
    select 
        payft.ID as ID
        , payft.TYPECODE
        , MT.MEMBERSHIPID
        , li.FINANCIALTRANSACTIONID PARENTID
        , ft.TYPECODE as PARENTTYPECODE
    from dbo.MEMBERSHIPTRANSACTION mt
        inner join dbo.FINANCIALTRANSACTIONLINEITEM li on mt.REVENUESPLITID = li.ID
        inner join dbo.FINANCIALTRANSACTION ft on li.FINANCIALTRANSACTIONID = ft.ID
        left join dbo.RECURRINGGIFTACTIVITY rga on rga.SOURCEREVENUEID = li.FINANCIALTRANSACTIONID
        left join dbo.INSTALLMENTSPLITPAYMENT isp on isp.PLEDGEID = li.FINANCIALTRANSACTIONID
        inner join dbo.FINANCIALTRANSACTIONLINEITEM payli on payli.ID = rga.PAYMENTREVENUEID or payli.ID = isp.PAYMENTID
        inner join dbo.FINANCIALTRANSACTION payft on payli.FINANCIALTRANSACTIONID = payft.ID
    union
    select 
        FT.ID as ID
        , ft.TYPECODE
        , MT.MEMBERSHIPID
        , null as PARENTID
        , null as PARENTTYPECODE
    from dbo.FINANCIALTRANSACTION FT
        inner join dbo.FINANCIALTRANSACTIONLINEITEM FTLI on FT.ID = FTLI.FINANCIALTRANSACTIONID
        inner join dbo.MEMBERSHIPTRANSACTION MT on FTLI.ID = MT.REVENUESPLITID
) as S