UFN_REVENUE_CONSTITUENTSWITHCOMMITMENTS

            CREATE function dbo.UFN_REVENUE_CONSTITUENTSWITHCOMMITMENTS()
            returns table
            as
            return
            (
                --NOTE: This function does not currently consider auction donation commitments; this function is used in revenue batch 

                --to find constituents with commitments, and you cannot currently pay for auction donation commitments in revenue batch.


                /* 
                    This function is a performance improvement over the scalar function UFN_CONSTITUENT_HASCOMMITMENTS(). Some of the key 
                    improvements: 
                    1) It's inline instead of scalar, so the optimizer can do more intelligent things when there are many CONSTITUENT rows to look at
                    2) Inline all of the balance calculations, so the balance calculations can benefit from index scans and seeks over CONSTITUENT 
                        (which this function will almost certainly be joined to) 

                */
                select REVENUE.CONSTITUENTID [CONSTITUENTID]
                from dbo.FINANCIALTRANSACTION REVENUE with (nolock) 
                inner join 
                (
                    select 
                        [BALANCEREVENUE].ID as [REVENUEID],
                        sum(    INSTALLMENT.TRANSACTIONAMOUNT
                                - coalesce(PLEDGEINSTALLMENTPAYMENT.TRANSACTIONAMOUNT,0)
                                - coalesce(PLEDGEINSTALLMENTWRITEOFF.TRANSACTIONAMOUNT,0)
                        ) as [BALANCEINCURRENCY]
                    from dbo.FINANCIALTRANSACTION [BALANCEREVENUE] with (nolock)
                    left join dbo.INSTALLMENT with (nolock) on INSTALLMENT.REVENUEID = [BALANCEREVENUE].ID
                    left join(
                        select INSTALLMENTSPLIT.INSTALLMENTID, sum(INSTALLMENTSPLITPAYMENT.AMOUNT) TRANSACTIONAMOUNT
                        from dbo.INSTALLMENTSPLITPAYMENT with (nolock)
                        inner join dbo.INSTALLMENTSPLIT with (nolock) on INSTALLMENTSPLIT.ID = INSTALLMENTSPLITPAYMENT.INSTALLMENTSPLITID
                        group by INSTALLMENTSPLIT.INSTALLMENTID
                    ) PLEDGEINSTALLMENTPAYMENT on PLEDGEINSTALLMENTPAYMENT.INSTALLMENTID = INSTALLMENT.ID
                    left join (
                        select INSTALLMENTSPLIT.INSTALLMENTID, sum(INSTALLMENTSPLITWRITEOFF.TRANSACTIONAMOUNT) TRANSACTIONAMOUNT
                        from dbo.INSTALLMENTSPLITWRITEOFF with (nolock)
                        inner join dbo.INSTALLMENTSPLIT with (nolock) on INSTALLMENTSPLIT.ID = INSTALLMENTSPLITWRITEOFF.INSTALLMENTSPLITID
                        group by INSTALLMENTSPLIT.INSTALLMENTID
                    ) PLEDGEINSTALLMENTWRITEOFF on PLEDGEINSTALLMENTWRITEOFF.INSTALLMENTID = INSTALLMENT.ID
                    where [BALANCEREVENUE].TYPECODE in (1,2,3,4,6,8)
                    group by [BALANCEREVENUE].ID
                ) as [BALANCE] on [BALANCE].[REVENUEID] = REVENUE.ID 
                left join dbo.REVENUESCHEDULE with (nolock) on REVENUESCHEDULE.ID = REVENUE.ID 
                where    REVENUE.TYPECODE = 1 and [BALANCE].BALANCEINCURRENCY > 0 and coalesce(REVENUESCHEDULE.ISPENDING,0) = 0
                    or    REVENUE.TYPECODE = 4 and [BALANCE].BALANCEINCURRENCY > 0 and coalesce(REVENUESCHEDULE.ISPENDING,0) = 0
                    or    REVENUE.TYPECODE = 3 and [BALANCE].BALANCEINCURRENCY > 0 and coalesce(REVENUESCHEDULE.ISPENDING,0) = 0 
                    or    REVENUE.TYPECODE = 6 and [BALANCE].BALANCEINCURRENCY > 0 and coalesce(REVENUESCHEDULE.ISPENDING,0) = 0
                    or    REVENUE.TYPECODE = 8 and [BALANCE].BALANCEINCURRENCY > 0

                union 
                --KevinHi: Inline recurring gifts separately since they do not rely on the balance subquery.

                select
                    FT.CONSTITUENTID [CONSTITUENTID]
                from dbo.FINANCIALTRANSACTION FT
                    inner join dbo.REVENUESCHEDULE RS on FT.ID = RS.ID
                    inner join (
                        select FINANCIALTRANSACTIONID
                        from dbo.FINANCIALTRANSACTIONLINEITEM LI
                        inner join dbo.REVENUESPLIT_EXT RSX on LI.ID = RSX.ID 
                        where RSX.TYPECODE <> 2
                    ) S on FT.ID = S.FINANCIALTRANSACTIONID
                where 
                    FT.TYPECODE = 2 and FT.TRANSACTIONAMOUNT > 0 and RS.STATUSCODE in (0,5) and RS.ISPENDING = 0

                union
                select REGISTRANT.CONSTITUENTID
                from dbo.REGISTRANT with (nolock)
                inner join
                (
                    select 
                        [BALANCEREGISTRANT].ID as [REGISTRANTID], 
                         sum(    coalesce([REGISTRATION].AMOUNT, 0) 
                                + coalesce([PAYMENT].AMOUNT, 0) --Negation handled in the [PAYMENT] join

                                + coalesce([CREDITS].AMOUNT, 0)
                         ) as [BALANCE]
                    from dbo.REGISTRANT as [BALANCEREGISTRANT] with (nolock) 
                    left join (
                        select REGISTRANTID, sum(AMOUNT) [AMOUNT]
                        from dbo.REGISTRANTREGISTRATION with (nolock) 
                        group by REGISTRANTID
                    ) as [REGISTRATION] on [REGISTRATION].REGISTRANTID = [BALANCEREGISTRANT].ID
                    left join (
                        select REGISTRANTID, sum(-AMOUNT) [AMOUNT]
                        from dbo.EVENTREGISTRANTPAYMENT with (nolock) 
                        group by REGISTRANTID
                    ) as [PAYMENT] on [PAYMENT].REGISTRANTID = [BALANCEREGISTRANT].ID
                    left join (
                        select CREDITITEMEVENTREGISTRATION.REGISTRANTID, sum(CREDITITEM.TOTAL) [AMOUNT]
                        from dbo.CREDITITEM with (nolock) 
                        inner join dbo.CREDITITEMEVENTREGISTRATION with (nolock)  on CREDITITEMEVENTREGISTRATION.ID = CREDITITEM.ID 
                        group by CREDITITEMEVENTREGISTRATION.REGISTRANTID
                    ) as [CREDITS] on [CREDITS].REGISTRANTID = [BALANCEREGISTRANT].ID
                    group by [BALANCEREGISTRANT].ID
                ) as [BALANCE] on [BALANCE].REGISTRANTID = REGISTRANT.ID
                where [BALANCE].BALANCE > 0

                union 
                select MEMBER.CONSTITUENTID 
                from dbo.MEMBER with (nolock)
                --where MEMBER.ISDROPPED = 0 


            )