UFN_CREDITPAYMENT_GETRECEIPTAMOUNT

CREATE function dbo.UFN_CREDITPAYMENT_GETRECEIPTAMOUNT
(
    @CREDITPAYMENTID uniqueidentifier
)
returns money
as begin
    declare @RECEIPTAMOUNT money;

    declare @CREDITID uniqueidentifier;
    declare @CREDITPAYMENTAMOUNT money;

    select
        @CREDITID = [CREDITID],
        @CREDITPAYMENTAMOUNT = AMOUNT
    from dbo.[CREDITPAYMENT]
    where [ID] = @CREDITPAYMENTID;

    declare @NUMBEROFITEMS int =
        (select count(*) from dbo.FINANCIALTRANSACTIONLINEITEM where FINANCIALTRANSACTIONID = @CREDITID);

    declare @NUMBEROFREGISTRATIONREFUNDS int = (
        select count(*)
        from dbo.FINANCIALTRANSACTIONLINEITEM as LI
        inner join dbo.CREDITITEM_EXT as EXT on EXT.ID = LI.ID
        where LI.FINANCIALTRANSACTIONID = @CREDITID and EXT.TYPECODE = 6  -- Event Registration

    );

    --If there's only 1 event registration on the refund (for both event reg-type or order-type refunds)

    declare @EVENTREFUND bit =
        case
            when @NUMBEROFREGISTRATIONREFUNDS = @NUMBEROFITEMS then 1
            else 0
        end;

    --If there's only 1 membership on the refund (for both membership-type or order-type refunds)

    declare @MEMBERSHIPREFUND bit =
        case
            when (
                select count(*)
                from dbo.FINANCIALTRANSACTIONLINEITEM as LI
                inner join dbo.CREDITITEM_EXT as EXT on EXT.ID = LI.ID
                where LI.FINANCIALTRANSACTIONID = @CREDITID and EXT.TYPECODE = 1  -- Membership

            ) = @NUMBEROFITEMS and @NUMBEROFITEMS = 1 then 1
            else 0
        end;

    declare @MEMBERSHIPREFUNDWITHORDERITEM bit = 0;

    if @MEMBERSHIPREFUND = 1 begin
        set @MEMBERSHIPREFUNDWITHORDERITEM = (
            select count(*)
            from dbo.FINANCIALTRANSACTIONLINEITEM as LI
            inner join dbo.CREDITITEM_EXT as EXT on EXT.ID = LI.ID
            where LI.FINANCIALTRANSACTIONID = @CREDITID and EXT.SALESORDERITEMID is not null
        )
    end

    if @EVENTREFUND = 1 or (@MEMBERSHIPREFUND = 1 and @MEMBERSHIPREFUNDWITHORDERITEM = 0) begin
        --We only want to use the receipt amount on the order if it's less than the credit payment amount.

        --Credit payment amounts may be smaller than receipt amounts if there was a partial refund (membership only).

        --Also, we always want to use the credit payment amount for memberships or event registrations that were

        --on sales order because they could have been paid for with multiple payment methods.

        select 
            @RECEIPTAMOUNT =
                (case when (REVENUE_EXT.[RECEIPTAMOUNT] > [CREDITPAYMENT].[AMOUNT]) or [CREDITPAYMENT].[REVENUESPLITID] is null then
                    [CREDITPAYMENT].[AMOUNT]
                else
                    isnull(REVENUE_EXT.[RECEIPTAMOUNT], 0)
                end)
        from
            dbo.[CREDITPAYMENT]
        left join
            dbo.FINANCIALTRANSACTIONLINEITEM as REFUNDEDLI on [CREDITPAYMENT].[REVENUESPLITID] = REFUNDEDLI.[ID] and REFUNDEDLI.DELETEDON is null and REFUNDEDLI.TYPECODE <> 1  -- Reversal

        inner join
            dbo.REVENUE_EXT on REVENUE_EXT.ID in (CREDITPAYMENT.REVENUEID, REFUNDEDLI.FINANCIALTRANSACTIONID)
        where [CREDITPAYMENT].[ID] = @CREDITPAYMENTID

    end
    else begin
        --This code figures out the receipt amount of the entire refund that the payment is on

        --so we can do this calculation at the very bottom:

        --set @RECEIPTAMOUNT = (@CREDITPAYMENTAMOUNT * @RECEIPTAMOUNT / @TOTALCREDITPAYMENTS)

        --Since there may have been multiple payment methods, we have to figure out

        --what portion of the receipt amount this payment is responsible for

        declare @RA table
        (
            RECEIPTAMOUNT money
        )

        insert into @RA ([RECEIPTAMOUNT])
        select
            case
                when [SALESORDERITEM].[TYPECODE] = 1 then
                    [SALESORDERITEM].[TOTAL]
                    - (
                        select isnull(sum([AMOUNT]), 0)
                        from dbo.[SALESORDERITEMMEMBERSHIPITEMPROMOTION] as [PROMO]
                        where [PROMO].[SALESORDERITEMID] = [SALESORDERITEM].[ID]
                    ) 
                    - (
                        select isnull(sum([REVENUEBENEFIT].[TOTALVALUE]), 0) from dbo.[REVENUEBENEFIT]
                        inner join dbo.[MEMBERSHIPTRANSACTION] on [MEMBERSHIPTRANSACTION].[REVENUESPLITID] = [REVENUEBENEFIT].[REVENUESPLITID]
                        inner join dbo.[SALESORDERITEMMEMBERSHIP] on [SALESORDERITEMMEMBERSHIP].[MEMBERSHIPTRANSACTIONID] = [MEMBERSHIPTRANSACTION].[ID]
                        where [SALESORDERITEMMEMBERSHIP].[ID] = [SALESORDERITEM].[ID]
                    )
                when [SALESORDERITEM].[TYPECODE] = 2 then [SALESORDERITEM].[TOTAL]
            end
        from dbo.FINANCIALTRANSACTIONLINEITEM as LI
        inner join dbo.CREDITITEM_EXT as EXT on EXT.ID = LI.ID
        inner join dbo.[SALESORDERITEM] on [SALESORDERITEM].[ID] = EXT.[SALESORDERITEMID]
        where LI.FINANCIALTRANSACTIONID = @CREDITID and [SALESORDERITEM].[TYPECODE] in (1, 2)  -- Membership, Donation


        if @NUMBEROFREGISTRATIONREFUNDS > 0 begin
            insert into @RA ([RECEIPTAMOUNT])
            select
                isnull(sum([EVENTREGISTRANTPAYMENT].[RECEIPTAMOUNT]), 0)
            from
                dbo.FINANCIALTRANSACTIONLINEITEM as LI
            inner join
                dbo.CREDITITEM_EXT as EXT on EXT.ID = LI.ID
            inner join
                dbo.[CREDITITEMEVENTREGISTRATION] on LI.[ID] = [CREDITITEMEVENTREGISTRATION].[ID]
            inner join
                dbo.[SALESORDERITEM] on EXT.[SALESORDERITEMID] = [SALESORDERITEM].[ID]
            inner join
                dbo.[SALESORDER] on [SALESORDER].[ID] = [SALESORDERITEM].[SALESORDERID]
            inner join
                dbo.FINANCIALTRANSACTIONLINEITEM as ORDERLI on ORDERLI.FINANCIALTRANSACTIONID = SALESORDER.REVENUEID
            inner join
                dbo.[EVENTREGISTRANTPAYMENT] on ([EVENTREGISTRANTPAYMENT].[PAYMENTID] = ORDERLI.[ID] and [EVENTREGISTRANTPAYMENT].[REGISTRANTID] = [CREDITITEMEVENTREGISTRATION].[REGISTRANTID])
            where
                LI.FINANCIALTRANSACTIONID = @CREDITID
                and ORDERLI.DELETEDON is null
                and ORDERLI.TYPECODE <> 1  -- Reversal

        end

        select @RECEIPTAMOUNT = 
            sum( 
                (case 
                    when [RECEIPTAMOUNT] < 0 then 0 
                    else [RECEIPTAMOUNT]
                end)
            ) 
        from @RA

        declare @TOTALCREDITPAYMENTS money = (select sum([AMOUNT]) from dbo.[CREDITPAYMENT] where [CREDITID] = @CREDITID)

        set @RECEIPTAMOUNT = (@CREDITPAYMENTAMOUNT * @RECEIPTAMOUNT / @TOTALCREDITPAYMENTS)
    end

    return isnull(@RECEIPTAMOUNT, 0);

end