UFN_REFUND_GETITEMAMOUNTSFORGL

CREATE function dbo.UFN_REFUND_GETITEMAMOUNTSFORGL
(
    @CREDITID uniqueidentifier
)
returns table
as return
    with ORDERDISCOUNTS_CTE as (
        select
            REFUNDLI.ID,
            case when dbo.UFN_REFUND_HASREVERSEDORDERLEVELDISCOUNT(@CREDITID) = 1
                then coalesce(TICKET.ORDERLEVELDISCOUNTSAPPLIED, MERCHUNIT.ORDERLEVELDISCOUNTSAPPLIED)
                else 0
            end as REVERSEDDISCOUNTAMOUNT
        from dbo.FINANCIALTRANSACTIONLINEITEM REFUNDLI
        inner join dbo.CREDITITEM_EXT REFUNDLI_EXT on REFUNDLI_EXT.ID = REFUNDLI.ID
        left join dbo.TICKET on TICKET.ID = REFUNDLI_EXT.SALESORDERITEMIZEDITEMID
        left join dbo.SALESORDERITEMMERCHANDISEUNIT MERCHUNIT on MERCHUNIT.ID = REFUNDLI_EXT.SALESORDERITEMIZEDITEMID
        where REFUNDLI.FINANCIALTRANSACTIONID = @CREDITID
            and REFUNDLI_EXT.TYPECODE in (0, 14)  -- Only tickets and merchandise are order-level-discountable.

            and coalesce(TICKET.ORDERLEVELDISCOUNTSAPPLIED, MERCHUNIT.ORDERLEVELDISCOUNTSAPPLIED) > 0
    ),
    CONTRIBUTEDREVENUE_CTE as (
        select
            REFUNDMEMBERSHIPLI.ID,
            sum(DONATIONLI.ORGAMOUNT) as AMOUNT
        from dbo.FINANCIALTRANSACTIONLINEITEM REFUNDMEMBERSHIPLI
        inner join dbo.CREDITITEM_EXT REFUNDMEMBERSHIPEXT on REFUNDMEMBERSHIPEXT.ID = REFUNDMEMBERSHIPLI.ID
        inner join dbo.FINANCIALTRANSACTIONLINEITEM MEMBERSHIPLI on MEMBERSHIPLI.ID = REFUNDMEMBERSHIPLI.SOURCELINEITEMID
        inner join dbo.REVENUESPLIT_EXT MEMBERSHIPEXT on MEMBERSHIPEXT.ID = MEMBERSHIPLI.ID
        inner join dbo.FINANCIALTRANSACTIONLINEITEM DONATIONLI on DONATIONLI.SOURCELINEITEMID = MEMBERSHIPLI.ID
        inner join dbo.REVENUESPLIT_EXT DONATIONEXT on DONATIONEXT.ID = DONATIONLI.ID
        where REFUNDMEMBERSHIPLI.FINANCIALTRANSACTIONID = @CREDITID
            and (REFUNDMEMBERSHIPEXT.TYPECODE = 1 or REFUNDMEMBERSHIPEXT.TYPECODE = 6)
            and ((MEMBERSHIPEXT.TYPECODE = 2 and MEMBERSHIPEXT.APPLICATIONCODE = 5) or (MEMBERSHIPEXT.TYPECODE = 1 and MEMBERSHIPEXT.APPLICATIONCODE = 1))
            and ((DONATIONEXT.TYPECODE = 0 and DONATIONEXT.APPLICATIONCODE = 0) or (DONATIONEXT.TYPECODE = 0 and DONATIONEXT.APPLICATIONCODE = 1))
        group by REFUNDMEMBERSHIPLI.ID
    ),
    SOURCEITEMS_CTE as (
        -- TODO: figure out if this "distinct" I copied over is necessary

        select distinct coalesce(SALESORDERITEMTICKET.PROGRAMID, [EVENT].PROGRAMID) as PROGRAMID,
            case REFUNDSPLIT_EXT.TYPECODE
                when 1 then  -- Membership

                    REFUNDSPLIT.SOURCELINEITEMID
                else
                    null
            end as MEMBERSHIPREVENUESPLITID,
            SALESORDERITEMFEE.FEEID,
            SALESORDERITEMTAX.TAXID,
            coalesce(SALESORDERITEMTICKET.EVENTID, REGISTRANT.EVENTID) as EVENTID,
            REGISTRANT.ID as REGISTRANTID,
            REFUNDSPLIT_EXT.CREDITID,
            REFUNDSPLIT_EXT.ID as CREDITITEMID,
            case
                when isnull(CONTRIBUTEDREVENUE.AMOUNT, 0) > REFUNDSPLIT_EXT.DISCOUNTS then -- The discount is only taken out of the contributed revenue

                    CREDITITEMMINUSDICOUNT.TOTAL - CONTRIBUTEDREVENUE.AMOUNT
                when SALESORDERITEM.ID is null and SOURCELIEXT.TYPECODE = 0 and SOURCELIEXT.APPLICATIONCODE = 0 then -- This is the contributed revenue and we want it to be whatever the payment was

                    (select
                        sum(SOURCEPAYMENTLI.ORGAMOUNT)
                    from dbo.FINANCIALTRANSACTIONLINEITEM SOURCEPAYMENTLI
                    inner join dbo.FINANCIALTRANSACTION SOURCEPAYMENTFT on SOURCEPAYMENTFT.ID = SOURCEPAYMENTLI.FINANCIALTRANSACTIONID
                    where SOURCEPAYMENTLI.SOURCELINEITEMID = SOURCELIEXT.ID
                        and SOURCEPAYMENTFT.TYPECODE = 0)
                else
                    CREDITITEMMINUSDICOUNT.TOTAL - REFUNDSPLIT_EXT.DISCOUNTS
            end TOTAL,
            REFUND.TRANSACTIONAMOUNT as CREDITAMOUNT,
            CREDIT_EXT.SALESORDERID,
            SALESORDERITEMMERCHANDISE.MERCHANDISEPRODUCTINSTANCEID,
            SALESORDERITEMMEMBERSHIPADDON.MEMBERSHIPADDONID,
            SOURCELIEXT.DESIGNATIONID,
            REFUNDSPLIT.SOURCELINEITEMID
        from dbo.CREDITITEM_EXT REFUNDSPLIT_EXT
        inner join dbo.FINANCIALTRANSACTIONLINEITEM REFUNDSPLIT on REFUNDSPLIT.ID = REFUNDSPLIT_EXT.ID
        inner join dbo.FINANCIALTRANSACTION REFUND on REFUND.ID = REFUNDSPLIT.FINANCIALTRANSACTIONID
        left join dbo.SALESORDERITEM ON SALESORDERITEM.ID = REFUNDSPLIT_EXT.SALESORDERITEMID
        left join dbo.SALESORDERITEMTICKET ON SALESORDERITEM.ID = SALESORDERITEMTICKET.ID
        left join dbo.[EVENT] on SALESORDERITEMTICKET.EVENTID = [EVENT].ID
        left join dbo.SALESORDERITEMTAX ON SALESORDERITEM.ID = SALESORDERITEMTAX.TAXITEMID
        left join dbo.SALESORDERITEMEVENTREGISTRATION ON SALESORDERITEM.ID = SALESORDERITEMEVENTREGISTRATION.ID
        left join dbo.REGISTRANT ON SALESORDERITEMEVENTREGISTRATION.REGISTRANTID = REGISTRANT.ID
        left join dbo.SALESORDERITEMFEE ON SALESORDERITEM.ID = SALESORDERITEMFEE.ID
        left join dbo.SALESORDERITEMMERCHANDISE on SALESORDERITEM.ID = SALESORDERITEMMERCHANDISE.ID
        left join dbo.SALESORDERITEMMEMBERSHIPADDON on SALESORDERITEMMEMBERSHIPADDON.ID = SALESORDERITEM.ID
        left join CONTRIBUTEDREVENUE_CTE CONTRIBUTEDREVENUE on CONTRIBUTEDREVENUE.ID = REFUNDSPLIT.ID
        inner join dbo.CREDIT_EXT on CREDIT_EXT.ID = REFUNDSPLIT_EXT.CREDITID
        inner join dbo.REVENUESPLIT_EXT SOURCELIEXT on SOURCELIEXT.ID = REFUNDSPLIT.SOURCELINEITEMID
        cross apply (
            select
                (REFUNDSPLIT.QUANTITY * REFUNDSPLIT.UNITVALUE)
                - coalesce((select REVERSEDDISCOUNTAMOUNT from ORDERDISCOUNTS_CTE OD where OD.ID = REFUNDSPLIT_EXT.ID), 0) as TOTAL
        ) as CREDITITEMMINUSDICOUNT  -- This represents the value of the split after Order-level discounts are removed (if applicable)

        where
            REFUNDSPLIT_EXT.CREDITID = @CREDITID and
            CREDITITEMMINUSDICOUNT.TOTAL > 0 and
            CREDIT_EXT.SALESORDERID is not null
    )
    -- Normal sales items

    select
        FTLI.ID as REVENUESPLITID,
        FTLI.FINANCIALTRANSACTIONID,
        REVENUESPLIT_EXT.TYPE +'-Order-'+ convert(nvarchar(50), SALESORDER.SEQUENCEID) as TYPE,
        CP.AMOUNT as REFUNDPAYMENTTOTAL,
        sum(SOURCEITEMS.TOTAL) as REFUNDSPLITAMOUNT,
        SOURCEITEMS.CREDITAMOUNT as CREDITAMOUNT,
        CP.ID as CREDITPAYMENTID,
        REVENUESPLIT_EXT.TYPECODE,
        CP.PAYMENTMETHODCODE,
        SOURCEITEMS.CREDITITEMID
    from SOURCEITEMS_CTE as SOURCEITEMS
    inner join dbo.CREDITPAYMENT CP on SOURCEITEMS.CREDITID = CP.CREDITID
    inner join dbo.SALESORDER on SOURCEITEMS.SALESORDERID = SALESORDER.ID
    inner join dbo.FINANCIALTRANSACTIONLINEITEM FTLI on SALESORDER.REVENUEID = FTLI.FINANCIALTRANSACTIONID
    inner join dbo.REVENUESPLIT_EXT on FTLI.ID = REVENUESPLIT_EXT.ID
    inner join dbo.REVENUESPLITORDER on FTLI.ID = REVENUESPLITORDER.ID
    left join dbo.EVENTREGISTRANTPAYMENT on FTLI.ID = EVENTREGISTRANTPAYMENT.PAYMENTID
    where
        (
            (REVENUESPLITORDER.PROGRAMID = SOURCEITEMS.PROGRAMID and REVENUESPLITORDER.EVENTID is null)
            or (REVENUESPLITORDER.EVENTID = SOURCEITEMS.EVENTID and SOURCEITEMS.REGISTRANTID is null)
            or (EVENTREGISTRANTPAYMENT.REGISTRANTID = SOURCEITEMS.REGISTRANTID and REVENUESPLIT_EXT.TYPECODE = 1)
        )
        or FTLI.ID = SOURCEITEMS.MEMBERSHIPREVENUESPLITID
        or REVENUESPLITORDER.FEEID = SOURCEITEMS.FEEID
        or REVENUESPLITORDER.TAXID = SOURCEITEMS.TAXID
        or REVENUESPLITORDER.MERCHANDISEPRODUCTINSTANCEID = SOURCEITEMS.MERCHANDISEPRODUCTINSTANCEID
        or REVENUESPLITORDER.MEMBERSHIPADDONID = SOURCEITEMS.MEMBERSHIPADDONID
        or (REVENUESPLITORDER.DESIGNATIONID = SOURCEITEMS.DESIGNATIONID and REVENUESPLITORDER.ID = SOURCEITEMS.SOURCELINEITEMID)  -- TODO: sourcelineitemid implies contributed revenue? Why is this here, but normal donations are treated separately?

    group by FTLI.ID, FTLI.FINANCIALTRANSACTIONID, CP.AMOUNT, SOURCEITEMS.CREDITAMOUNT, CP.ID, REVENUESPLIT_EXT.TYPE, SALESORDER.SEQUENCEID, REVENUESPLIT_EXT.TYPECODE, CP.PAYMENTMETHODCODE, SOURCEITEMS.CREDITITEMID


    --back office event registrations.

    union all
    select ORIGLI.ID as REVENUESPLITID,
        ORIGLI.FINANCIALTRANSACTIONID,
        REVENUESPLIT_EXT.TYPE,
        CREDITPAYMENT.AMOUNT as REFUNDPAYMENTTOTAL,
        FT.TRANSACTIONAMOUNT as REFUNDSPLITAMOUNT, -- 2013-7-12 SW: formerly, using CREDITPAYMENT amount here caused payments to be prorated according to each other's values. Similar change below for membership may be necessary if we ever allow multiple backoffice payments toward a single membership.

        FT.TRANSACTIONAMOUNT as CREDITAMOUNT,
        CREDITPAYMENT.ID as CREDITPAYMENTID,
        REVENUESPLIT_EXT.TYPECODE,
        CREDITPAYMENT.PAYMENTMETHODCODE,
        REFUNDLI.ID CREDITITEMID
    from dbo.CREDIT_EXT CREDIT
    inner join dbo.FINANCIALTRANSACTION FT on FT.ID = CREDIT.ID
    inner join dbo.CREDITPAYMENT on CREDITPAYMENT.CREDITID = CREDIT.ID
    inner join dbo.FINANCIALTRANSACTIONLINEITEM ORIGLI on ORIGLI.ID = CREDITPAYMENT.REVENUESPLITID
    inner join dbo.REVENUESPLIT_EXT on REVENUESPLIT_EXT.ID = ORIGLI.ID
    left join dbo.FINANCIALTRANSACTIONLINEITEM REFUNDLI on REFUNDLI.SOURCELINEITEMID = ORIGLI.ID
    where
        CREDIT.SALESORDERID is null
        and CREDIT.ID = @CREDITID
        and REVENUESPLIT_EXT.TYPECODE = 1

    --Security deposit refund

    union all
    select
        FTLI.ID as REVENUESPLITID,
        FTLI.FINANCIALTRANSACTIONID,
        REVENUESPLIT_EXT.TYPE,
        CP.AMOUNT as REFUNDPAYMENTTOTAL,
        CP.AMOUNT as REFUNDSPLITAMOUNT,
        FT.TRANSACTIONAMOUNT as CREDITAMOUNT,
        CP.ID as CREDITPAYMENTID,
        REVENUESPLIT_EXT.TYPECODE,
        CP.PAYMENTMETHODCODE,
        LI.ID CREDITITEMID
    from dbo.CREDIT_EXT CREDIT
    inner join dbo.FINANCIALTRANSACTION FT on FT.ID = CREDIT.ID
    inner join dbo.CREDITPAYMENT CP ON CREDIT.ID = CP.CREDITID
    inner join dbo.FINANCIALTRANSACTIONLINEITEM FTLI on CP.REVENUESPLITID = FTLI.ID
    inner join dbo.REVENUESPLIT_EXT on REVENUESPLIT_EXT.ID = FTLI.ID
    left join dbo.FINANCIALTRANSACTIONLINEITEM LI on LI.SOURCELINEITEMID = FTLI.ID
    where
        REVENUESPLIT_EXT.TYPECODE = 13 and CREDIT.ID = @CREDITID

    -- Donation refund

    union all
    select
        FTLI.ID as REVENUESPLITID,
        FTLI.FINANCIALTRANSACTIONID,
        REVENUESPLIT_EXT.TYPE,
        CP.AMOUNT as REFUNDPAYMENTTOTAL,
        LI.TRANSACTIONAMOUNT as REFUNDSPLITAMOUNT,
        FT.TRANSACTIONAMOUNT as CREDITAMOUNT,
        CP.ID as CREDITPAYMENTID,
        REVENUESPLIT_EXT.TYPECODE,
        CP.PAYMENTMETHODCODE,
        CREDITITEM.ID CREDITITEMID
    from dbo.CREDITITEM_EXT CREDITITEM
    inner join dbo.FINANCIALTRANSACTIONLINEITEM LI on LI.ID = CREDITITEM.ID
    inner join dbo.FINANCIALTRANSACTION FT on FT.ID = LI.FINANCIALTRANSACTIONID
    inner join dbo.CREDITPAYMENT CP on CREDITITEM.CREDITID = CP.CREDITID
    inner join dbo.SALESORDERITEM on CREDITITEM.SALESORDERITEMID    = SALESORDERITEM.ID
    inner join dbo.SALESORDERITEMDONATION on SALESORDERITEMDONATION.ID = SALESORDERITEM.ID
    inner join dbo.FINANCIALTRANSACTIONLINEITEM FTLI on SALESORDERITEMDONATION.REVENUESPLITID = FTLI.ID
    inner join dbo.REVENUESPLIT_EXT on REVENUESPLIT_EXT.ID = FTLI.ID
    where
        REVENUESPLIT_EXT.TYPECODE = 0 and CREDITITEM.CREDITID = @CREDITID

    --back office membership and membership add-ons.

    --these work differently because we cannot populate the revenuesplitid

    --on the credit payment table (the credit payment

    --is not associated with just one revenue split)

    union all
    select
        FINANCIALTRANSACTIONLINEITEM.SOURCELINEITEMID as REVENUESPLITID,
            FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID as REVENUEID,
            CREDITITEM_EXT.TYPE,
            FINANCIALTRANSACTION.ORGAMOUNT as REFUNDPAYMENTTOTAL,
            FINANCIALTRANSACTIONLINEITEM.ORGAMOUNT as REFUNDSPLITAMOUNT,
            FINANCIALTRANSACTION.ORGAMOUNT as CREDITAMOUNT,
            CREDITPAYMENT.ID as CREDITPAYMENTID,
            CREDITITEM_EXT.TYPECODE,
            CREDITPAYMENT.PAYMENTMETHODCODE,
            FINANCIALTRANSACTIONLINEITEM.ID CREDITITEMID
    from dbo.CREDIT_EXT
        inner join dbo.FINANCIALTRANSACTIONLINEITEM on FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = CREDIT_EXT.ID
        inner join dbo.CREDITITEM_EXT ON CREDITITEM_EXT.ID = FINANCIALTRANSACTIONLINEITEM.ID
        inner join dbo.FINANCIALTRANSACTION on FINANCIALTRANSACTION.ID = CREDIT_EXT.ID
        inner join dbo.CREDITPAYMENT on CREDITPAYMENT.CREDITID = CREDIT_EXT.ID
    where
        CREDIT_EXT.SALESORDERID is null
        and CREDIT_EXT.ID = @CREDITID
        and (CREDITITEM_EXT.TYPECODE = 16 or CREDITITEM_EXT.TYPECODE = 1)
        and CREDITITEM_EXT.SALESORDERITEMID is null;