USP_BBDW_FACT_FINANCIALTRANSACTIONLINEITEMSITE_LOAD

create procedure dbo.[USP_BBDW_FACT_FINANCIALTRANSACTIONLINEITEMSITE_LOAD](
    @OPENWINDOW datetime, 
    @CLOSEWINDOW datetime
) as 
    set nocount on;

    select
        [REVENUESPLIT].[ID] [FINANCIALTRANSACTIONLINEITEMSYSTEMID],
        isnull([EVENTSITE].[ID], '00000000-0000-0000-0000-000000000000') as [SITESOURCESYSTEMID],
        isnull([EVENTSITE].[SITEID], '00000000-0000-0000-0000-000000000000') as [SITESYSTEMID],
        1 as [ISINCLUDED]
    from dbo.[REVENUESPLIT_EXT] [REVENUESPLIT]
    inner join dbo.[EVENTREGISTRANTPAYMENT] on [EVENTREGISTRANTPAYMENT].[PAYMENTID] = [REVENUESPLIT].[ID]
    inner join dbo.[REGISTRANT] on [EVENTREGISTRANTPAYMENT].[REGISTRANTID] = [REGISTRANT].[ID]
    inner join dbo.[EVENTSITE] on [REGISTRANT].[EVENTID] = [EVENTSITE].[EVENTID]
    where [REVENUESPLIT].[APPLICATIONCODE] = 1 and
    (
        ([REVENUESPLIT].[DATEADDED] > @OPENWINDOW and [REVENUESPLIT].[DATEADDED] <= @CLOSEWINDOW) or
        ([REVENUESPLIT].[DATECHANGED] > @OPENWINDOW and [REVENUESPLIT].[DATECHANGED] <= @CLOSEWINDOW) or
        ([EVENTSITE].[DATEADDED] > @OPENWINDOW and [EVENTSITE].[DATEADDED] <= @CLOSEWINDOW) or
        ([EVENTSITE].[DATECHANGED] > @OPENWINDOW and [EVENTSITE].[DATECHANGED] <= @CLOSEWINDOW)
    )

    union all

    select
        [REVENUESPLIT].[ID] [FINANCIALTRANSACTIONLINEITEMSYSTEMID],
        isnull([MEMBERSHIPAPPLICATIONCODE_MEMBERSHIPPROGRAM].[ID], '00000000-0000-0000-0000-000000000000') as [SITESOURCESYSTEMID],
        isnull([MEMBERSHIPAPPLICATIONCODE_MEMBERSHIPPROGRAM].[SITEID], '00000000-0000-0000-0000-000000000000') as [SITESYSTEMID],
        1 as [ISINCLUDED]
    from dbo.[REVENUESPLIT_EXT] [REVENUESPLIT]
    inner join dbo.[MEMBERSHIPTRANSACTION] as [MEMBERSHIPAPPLICATIONCODE_MEMBERSHIPTRANSACTION] on [REVENUESPLIT].[ID] = [MEMBERSHIPAPPLICATIONCODE_MEMBERSHIPTRANSACTION].[REVENUESPLITID]
    inner join dbo.[MEMBERSHIPLEVEL] [MEMBERSHIPAPPLICATIONCODE_MEMBERSHIPLEVEL] on [MEMBERSHIPAPPLICATIONCODE_MEMBERSHIPTRANSACTION].[MEMBERSHIPLEVELID] = [MEMBERSHIPAPPLICATIONCODE_MEMBERSHIPLEVEL].[ID]
    inner join dbo.[MEMBERSHIPPROGRAM] [MEMBERSHIPAPPLICATIONCODE_MEMBERSHIPPROGRAM] on [MEMBERSHIPAPPLICATIONCODE_MEMBERSHIPLEVEL].[MEMBERSHIPPROGRAMID] = [MEMBERSHIPAPPLICATIONCODE_MEMBERSHIPPROGRAM].[ID]
    where [REVENUESPLIT].[APPLICATIONCODE] = 5 and
    (
        ([REVENUESPLIT].[DATEADDED] > @OPENWINDOW and [REVENUESPLIT].[DATEADDED] <= @CLOSEWINDOW) or
        ([REVENUESPLIT].[DATECHANGED] > @OPENWINDOW and [REVENUESPLIT].[DATECHANGED] <= @CLOSEWINDOW) or
        ([MEMBERSHIPAPPLICATIONCODE_MEMBERSHIPPROGRAM].[DATEADDED] > @OPENWINDOW and [MEMBERSHIPAPPLICATIONCODE_MEMBERSHIPPROGRAM].[DATEADDED] <= @CLOSEWINDOW) or
        ([MEMBERSHIPAPPLICATIONCODE_MEMBERSHIPPROGRAM].[DATECHANGED] > @OPENWINDOW and [MEMBERSHIPAPPLICATIONCODE_MEMBERSHIPPROGRAM].[DATECHANGED] <= @CLOSEWINDOW)
    )

    union all

    select
        [REVENUESPLIT].[ID] [FINANCIALTRANSACTIONLINEITEMSYSTEMID],
        isnull([MISCSITES].[SITESOURCESYSTEMID], '00000000-0000-0000-0000-000000000000') as [SITESOURCESYSTEMID],
        isnull([MISCSITES].[SITESYSTEMID], '00000000-0000-0000-0000-000000000000') as [SITESYSTEMID],
        1 as [ISINCLUDED]
    from dbo.[REVENUESPLIT_EXT] [REVENUESPLIT]
    inner join (
        select 
            [FINANCIALTRANSACTIONLINEITEM].[ID], 
            [PDACCOUNTSYSTEMSITE].[SITEID] [SITESYSTEMID],
            [PDACCOUNTSYSTEMSITE].[ID] [SITESOURCESYSTEMID],
            [PDACCOUNTSYSTEMSITE].[DATEADDED],
            [PDACCOUNTSYSTEMSITE].[DATECHANGED]
        from dbo.[PDACCOUNTSYSTEMSITE] as [PDACCOUNTSYSTEMSITE]
        inner join dbo.[FINANCIALTRANSACTION] on [FINANCIALTRANSACTION].PDACCOUNTSYSTEMID = [PDACCOUNTSYSTEMSITE].PDACCOUNTSYSTEMID
        inner join dbo.[FINANCIALTRANSACTIONLINEITEM] on [FINANCIALTRANSACTIONLINEITEM].FINANCIALTRANSACTIONID = [FINANCIALTRANSACTION].ID
        inner join dbo.[REVENUESPLIT_EXT] on [FINANCIALTRANSACTIONLINEITEM].ID = [REVENUESPLIT_EXT].ID
        inner join dbo.[PDACCOUNTSYSTEM] on [PDACCOUNTSYSTEM].ID = [FINANCIALTRANSACTION].PDACCOUNTSYSTEMID
        where
            [FINANCIALTRANSACTION].[TYPECODE] in (0,1,4,5,6,7)
            and [REVENUESPLIT_EXT].[APPLICATIONCODE] = 11
        union all
        select 
            [FINANCIALTRANSACTIONLINEITEM].[ID], 
            '00000000-0000-0000-0000-000000000000' as [SITESYSTEMID],
            '00000000-0000-0000-0000-000000000000' [SITESOURCESYSTEMID],
            [FINANCIALTRANSACTIONLINEITEM].[DATEADDED],
            [FINANCIALTRANSACTIONLINEITEM].[DATECHANGED]
        from dbo.[FINANCIALTRANSACTIONLINEITEM]  
        inner join dbo.[REVENUESPLIT_EXT] on [REVENUESPLIT_EXT].ID = [FINANCIALTRANSACTIONLINEITEM].ID
        inner join dbo.[FINANCIALTRANSACTION] on [FINANCIALTRANSACTION].ID = [FINANCIALTRANSACTIONLINEITEM].FINANCIALTRANSACTIONID
        inner join dbo.[PDACCOUNTSYSTEM] on [PDACCOUNTSYSTEM].ID = [FINANCIALTRANSACTION].PDACCOUNTSYSTEMID
        where
            [FINANCIALTRANSACTION].[TYPECODE] in (0,1,4,5,6,7)
            and [REVENUESPLIT_EXT].[APPLICATIONCODE] = 11
            and [PDACCOUNTSYSTEM].[ISDEFAULT] = 1
    ) [MISCSITES] on [MISCSITES].[ID] = [REVENUESPLIT].[ID] 
    where [REVENUESPLIT].[APPLICATIONCODE] = 11 and
    (
        ([REVENUESPLIT].[DATEADDED] > @OPENWINDOW and [REVENUESPLIT].[DATEADDED] <= @CLOSEWINDOW) or
        ([REVENUESPLIT].[DATECHANGED] > @OPENWINDOW and [REVENUESPLIT].[DATECHANGED] <= @CLOSEWINDOW) or
        ([MISCSITES].[DATEADDED] > @OPENWINDOW and [MISCSITES].[DATEADDED] <= @CLOSEWINDOW) or
        ([MISCSITES].[DATECHANGED] > @OPENWINDOW and [MISCSITES].[DATECHANGED] <= @CLOSEWINDOW)
    )

    union all

    select
        [REVENUESPLIT].[ID] [FINANCIALTRANSACTIONLINEITEMSYSTEMID],
        isnull([DESIGNATION].[DESIGNATIONID], '00000000-0000-0000-0000-000000000000') as [SITESOURCESYSTEMID],
        isnull([DESIGNATION].[SITESYSTEMID], '00000000-0000-0000-0000-000000000000') as [SITESYSTEMID],
        1 as [ISINCLUDED]
    from dbo.[REVENUESPLIT_EXT] [REVENUESPLIT]
    inner join (
        select 
            [DESIGNATION].ID,
            coalesce(DL5.[SITEID], DL4.[SITEID], DL3.[SITEID], DL2.[SITEID], DL1.[SITEID]) as [SITESYSTEMID],
            coalesce(DL5.[ID], DL4.[ID], DL3.[ID], DL2.[ID], DL1.[ID]) as [DESIGNATIONID],
            case 
                when DL5.[SITEID] is not null then DL5.[DATEADDED]
                when DL4.[SITEID] is not null then DL4.[DATEADDED]
                when DL3.[SITEID] is not null then DL3.[DATEADDED]
                when DL2.[SITEID] is not null then DL2.[DATEADDED]
                when DL1.[SITEID] is not null then DL1.[DATEADDED]
            end [DATEADDED],
            case 
                when DL5.[SITEID] is not null then DL5.[DATECHANGED]
                when DL4.[SITEID] is not null then DL4.[DATECHANGED]
                when DL3.[SITEID] is not null then DL3.[DATECHANGED]
                when DL2.[SITEID] is not null then DL2.[DATECHANGED]
                when DL1.[SITEID] is not null then DL1.[DATECHANGED]
            end [DATECHANGED]
        from dbo.[DESIGNATION] 
        left join dbo.[DESIGNATIONLEVEL] DL1 on DL1.[ID] = [DESIGNATION].[DESIGNATIONLEVEL1ID]
        left join dbo.[DESIGNATIONLEVEL] DL2 on DL2.[ID] = [DESIGNATION].[DESIGNATIONLEVEL2ID]
        left join dbo.[DESIGNATIONLEVEL] DL3 on DL3.[ID] = [DESIGNATION].[DESIGNATIONLEVEL3ID]
        left join dbo.[DESIGNATIONLEVEL] DL4 on DL4.[ID] = [DESIGNATION].[DESIGNATIONLEVEL4ID]
        left join dbo.[DESIGNATIONLEVEL] DL5 on DL5.[ID] = [DESIGNATION].[DESIGNATIONLEVEL5ID]
    ) as [DESIGNATION] on [DESIGNATION].ID = [REVENUESPLIT].[DESIGNATIONID]
    where [REVENUESPLIT].[APPLICATIONCODE] not in (1, 5, 11) and
    (
        ([REVENUESPLIT].[DATEADDED] > @OPENWINDOW and [REVENUESPLIT].[DATEADDED] <= @CLOSEWINDOW) or
        ([REVENUESPLIT].[DATECHANGED] > @OPENWINDOW and [REVENUESPLIT].[DATECHANGED] <= @CLOSEWINDOW) or
        (DESIGNATION.[DATEADDED] > @OPENWINDOW and DESIGNATION.[DATEADDED] <= @CLOSEWINDOW) or
        (DESIGNATION.[DATECHANGED] > @OPENWINDOW and DESIGNATION.[DATECHANGED] <= @CLOSEWINDOW)
    )

    union all

    select
        [REVENUESPLIT].[ID] [FINANCIALTRANSACTIONLINEITEMSYSTEMID],
        isnull([MEMBERSHIPADDONAPPLICATIONCODE_MEMBERSHIPPROGRAM].[ID], '00000000-0000-0000-0000-000000000000') as [SITESOURCESYSTEMID],
        isnull([MEMBERSHIPADDONAPPLICATIONCODE_MEMBERSHIPPROGRAM].[SITEID], '00000000-0000-0000-0000-000000000000') as [SITESYSTEMID],
        1 as [ISINCLUDED]
    from dbo.[REVENUESPLIT_EXT] [REVENUESPLIT]
    inner join dbo.[MEMBERSHIPADDON] on [REVENUESPLIT].[ID] = [MEMBERSHIPADDON].[REVENUESPLITID]
    inner join dbo.[MEMBERSHIP] on [MEMBERSHIPADDON].[MEMBERSHIPID] = [MEMBERSHIP].ID
    inner join dbo.[MEMBERSHIPLEVEL] as [MEMBERSHIPADDONAPPLICATIONCODE_MEMBERSHIPLEVEL] on [MEMBERSHIP].[MEMBERSHIPLEVELID] = [MEMBERSHIPADDONAPPLICATIONCODE_MEMBERSHIPLEVEL].[ID]
    inner join dbo.[MEMBERSHIPPROGRAM] as [MEMBERSHIPADDONAPPLICATIONCODE_MEMBERSHIPPROGRAM] on [MEMBERSHIPADDONAPPLICATIONCODE_MEMBERSHIPLEVEL].[MEMBERSHIPPROGRAMID] = [MEMBERSHIPADDONAPPLICATIONCODE_MEMBERSHIPPROGRAM].[ID]
    where [REVENUESPLIT].[APPLICATIONCODE] = 18 and [REVENUESPLIT].[DESIGNATIONID] is null and
    (
        ([REVENUESPLIT].[DATEADDED] > @OPENWINDOW and [REVENUESPLIT].[DATEADDED] <= @CLOSEWINDOW) or
        ([REVENUESPLIT].[DATECHANGED] > @OPENWINDOW and [REVENUESPLIT].[DATECHANGED] <= @CLOSEWINDOW) or
        ([MEMBERSHIPADDONAPPLICATIONCODE_MEMBERSHIPPROGRAM].[DATEADDED] > @OPENWINDOW and [MEMBERSHIPADDONAPPLICATIONCODE_MEMBERSHIPPROGRAM].[DATEADDED] <= @CLOSEWINDOW) or
        ([MEMBERSHIPADDONAPPLICATIONCODE_MEMBERSHIPPROGRAM].[DATECHANGED] > @OPENWINDOW and [MEMBERSHIPADDONAPPLICATIONCODE_MEMBERSHIPPROGRAM].[DATECHANGED] <= @CLOSEWINDOW)
    )

    union all

    select
        [REVENUESPLIT].[ID] [FINANCIALTRANSACTIONLINEITEMSYSTEMID],
        isnull([MEMBERSHIPINSTALLMENTPLANAPPLICATIONCODE_MEMBERSHIPPROGRAM].[ID], '00000000-0000-0000-0000-000000000000') as [SITESOURCESYSTEMID],
        isnull([MEMBERSHIPINSTALLMENTPLANAPPLICATIONCODE_MEMBERSHIPPROGRAM].[SITEID], '00000000-0000-0000-0000-000000000000') as [SITESYSTEMID],
        1 as [ISINCLUDED]
    from dbo.[REVENUESPLIT_EXT] [REVENUESPLIT]
    inner join (
        select distinct 
            [FINANCIALTRANSACTIONLINEITEM].[ID],
            [INSTALLMENTSPLITPAYMENT].[PAYMENTID]
        from 
            dbo.[FINANCIALTRANSACTIONLINEITEM] 
            inner join dbo.[REVENUESPLIT_EXT] on [FINANCIALTRANSACTIONLINEITEM].[ID] = [REVENUESPLIT_EXT].[ID] 
            inner join dbo.[INSTALLMENTSPLIT] on [FINANCIALTRANSACTIONLINEITEM].[ID] = [INSTALLMENTSPLIT].[REVENUESPLITID] 
            inner join dbo.[INSTALLMENTSPLITPAYMENT] on [INSTALLMENTSPLIT].[ID] = [INSTALLMENTSPLITPAYMENT].[INSTALLMENTSPLITID]
        where [REVENUESPLIT_EXT].[TYPECODE] = 2
    ) as [INSTALLMENTMEMBERSHIPSPLIT] on [REVENUESPLIT].[ID] = [INSTALLMENTMEMBERSHIPSPLIT].[PAYMENTID]
    inner join dbo.[FINANCIALTRANSACTIONLINEITEM] FTLI on FTLI.[ID] = [INSTALLMENTMEMBERSHIPSPLIT].[ID]
    inner join dbo.[MEMBERSHIPTRANSACTION] as [MEMBERSHIPINSTALLMENTPLANAPPLICATIONCODE_MEMBERSHIPTRANSACTION] on FTLI.[ID] = [MEMBERSHIPINSTALLMENTPLANAPPLICATIONCODE_MEMBERSHIPTRANSACTION].[REVENUESPLITID]
    inner join dbo.[MEMBERSHIPLEVEL] as [MEMBERSHIPINSTALLMENTPLANAPPLICATIONCODE_MEMBERSHIPLEVEL] on [MEMBERSHIPINSTALLMENTPLANAPPLICATIONCODE_MEMBERSHIPTRANSACTION].MEMBERSHIPLEVELID = [MEMBERSHIPINSTALLMENTPLANAPPLICATIONCODE_MEMBERSHIPLEVEL].[ID]
    inner join dbo.[MEMBERSHIPPROGRAM] as [MEMBERSHIPINSTALLMENTPLANAPPLICATIONCODE_MEMBERSHIPPROGRAM] on [MEMBERSHIPINSTALLMENTPLANAPPLICATIONCODE_MEMBERSHIPLEVEL].MEMBERSHIPPROGRAMID = [MEMBERSHIPINSTALLMENTPLANAPPLICATIONCODE_MEMBERSHIPPROGRAM].[ID]
    where 
        [REVENUESPLIT].[APPLICATIONCODE] = 19 and [REVENUESPLIT].[DESIGNATIONID] is null
        and
        FTLI.[DELETEDON] is null and FTLI.[TYPECODE] <> 1
        and
        (
            ([REVENUESPLIT].[DATEADDED] > @OPENWINDOW and [REVENUESPLIT].[DATEADDED] <= @CLOSEWINDOW) or
            ([REVENUESPLIT].[DATECHANGED] > @OPENWINDOW and [REVENUESPLIT].[DATECHANGED] <= @CLOSEWINDOW) or
            ([MEMBERSHIPINSTALLMENTPLANAPPLICATIONCODE_MEMBERSHIPPROGRAM].[DATEADDED] > @OPENWINDOW and [MEMBERSHIPINSTALLMENTPLANAPPLICATIONCODE_MEMBERSHIPPROGRAM].[DATEADDED] <= @CLOSEWINDOW) or
            ([MEMBERSHIPINSTALLMENTPLANAPPLICATIONCODE_MEMBERSHIPPROGRAM].[DATECHANGED] > @OPENWINDOW and [MEMBERSHIPINSTALLMENTPLANAPPLICATIONCODE_MEMBERSHIPPROGRAM].[DATECHANGED] <= @CLOSEWINDOW)
        )

    union all

    select
        [REVENUESPLIT].[ID] [FINANCIALTRANSACTIONLINEITEMSYSTEMID],
        isnull([RECURRINGGIFTAPPLICATIONCODE_MEMBERSHIPPROGRAM].[ID], '00000000-0000-0000-0000-000000000000') as [SITESOURCESYSTEMID],
        isnull([RECURRINGGIFTAPPLICATIONCODE_MEMBERSHIPPROGRAM].[SITEID], '00000000-0000-0000-0000-000000000000') as [SITESYSTEMID],
        1 as [ISINCLUDED]
    from dbo.[REVENUESPLIT_EXT] [REVENUESPLIT]
    inner join (
        select distinct 
            [FINANCIALTRANSACTIONLINEITEM].[ID], 
            [RECURRINGGIFTACTIVITY].[PAYMENTREVENUEID]
        from 
            dbo.[FINANCIALTRANSACTIONLINEITEM]
            inner join dbo.[REVENUESPLIT_EXT] on [FINANCIALTRANSACTIONLINEITEM].[ID] = [REVENUESPLIT_EXT].[ID]
            inner join dbo.[RECURRINGGIFTACTIVITY] on [FINANCIALTRANSACTIONLINEITEM].[FINANCIALTRANSACTIONID] = [RECURRINGGIFTACTIVITY].[SOURCEREVENUEID]
        where [REVENUESPLIT_EXT].[TYPECODE] = 2
    ) as [RECURRINGMEMBERSHIPSPLIT] on [REVENUESPLIT].[ID] = [RECURRINGMEMBERSHIPSPLIT].[PAYMENTREVENUEID]
    inner join dbo.[FINANCIALTRANSACTIONLINEITEM] FTLI on FTLI.[ID] = [RECURRINGMEMBERSHIPSPLIT].[ID]
    inner join dbo.[MEMBERSHIPTRANSACTION] as [RECURRINGGIFTAPPLICATIONCODE_MEMBERSHIPTRANSACTION] on FTLI.[ID] = [RECURRINGGIFTAPPLICATIONCODE_MEMBERSHIPTRANSACTION].[REVENUESPLITID]
    inner join dbo.[MEMBERSHIPLEVEL] as [RECURRINGGIFTAPPLICATIONCODE_MEMBERSHIPLEVEL] on [RECURRINGGIFTAPPLICATIONCODE_MEMBERSHIPTRANSACTION].[MEMBERSHIPLEVELID] = [RECURRINGGIFTAPPLICATIONCODE_MEMBERSHIPLEVEL].[ID]
    inner join dbo.[MEMBERSHIPPROGRAM] as [RECURRINGGIFTAPPLICATIONCODE_MEMBERSHIPPROGRAM] on [RECURRINGGIFTAPPLICATIONCODE_MEMBERSHIPLEVEL].[MEMBERSHIPPROGRAMID] = [RECURRINGGIFTAPPLICATIONCODE_MEMBERSHIPPROGRAM].[ID]
    where 
        [REVENUESPLIT].[APPLICATIONCODE] = 3 and [REVENUESPLIT].[DESIGNATIONID] is null
        and
        FTLI.[DELETEDON] is null and FTLI.[TYPECODE] <> 1
        and
        (
            ([REVENUESPLIT].[DATEADDED] > @OPENWINDOW and [REVENUESPLIT].[DATEADDED] <= @CLOSEWINDOW) or
            ([REVENUESPLIT].[DATECHANGED] > @OPENWINDOW and [REVENUESPLIT].[DATECHANGED] <= @CLOSEWINDOW) or
            ([RECURRINGGIFTAPPLICATIONCODE_MEMBERSHIPPROGRAM].[DATEADDED] > @OPENWINDOW and [RECURRINGGIFTAPPLICATIONCODE_MEMBERSHIPPROGRAM].[DATEADDED] <= @CLOSEWINDOW) or
            ([RECURRINGGIFTAPPLICATIONCODE_MEMBERSHIPPROGRAM].[DATECHANGED] > @OPENWINDOW and [RECURRINGGIFTAPPLICATIONCODE_MEMBERSHIPPROGRAM].[DATECHANGED] <= @CLOSEWINDOW)
        )