USP_DATAFORMTEMPLATE_VIEW_CREDITTRANSACTIONVIEW

CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_CREDITTRANSACTIONVIEW
(
    @ID uniqueidentifier,
    @DATALOADED bit = 0 output,
    @ITEMS xml = null output,
    @TOTAL money = null output,
    @PAYMENTS xml = null output,
    @OTHERPAYMENTMETHODS xml = null output,
    @TRANSACTIONID uniqueidentifier = null output,
    @TIMESTAMP bigint = null output,
    @CONSTITUENTID uniqueidentifier = null output,
    @CONSTITUENTNAME nvarchar(154) = null output
)
as
    set nocount on;

    set @DATALOADED = 0;

    set @TRANSACTIONID = @ID;

    set @OTHERPAYMENTMETHODS = (
        select 
            [ID], 
            [DESCRIPTION] 
        from dbo.[OTHERPAYMENTMETHODCODE]
        for xml raw ('ITEM'), type, elements, root('OTHERPAYMENTMETHODS'), BINARY BASE64
    );

    select
        @DATALOADED = 1,
        @TOTAL = FT.BASEAMOUNT,
        @TIMESTAMP = FT.TSLONG,
        @CONSTITUENTID = FT.CONSTITUENTID,
        @CONSTITUENTNAME = NF.NAME
    from dbo.FINANCIALTRANSACTION as FT
    outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(FT.CONSTITUENTID) as NF
    where FT.ID = @ID and FT.DELETEDON is null;

    declare @TOTALREFUND money;

    select
        @TOTALREFUND = isnull(sum((LI.QUANTITY * LI.UNITVALUE) - EXT.DISCOUNTS), 0)
    from 
        dbo.FINANCIALTRANSACTION as FT
    inner join
        dbo.FINANCIALTRANSACTIONLINEITEM as LI on LI.FINANCIALTRANSACTIONID = FT.ID
    inner join
        dbo.CREDITITEM_EXT as EXT on EXT.ID = LI.ID
    where 
        LI.SOURCELINEITEMID in (
            select ID
            from dbo.FINANCIALTRANSACTIONLINEITEM
            where FINANCIALTRANSACTIONID =  @ID and DELETEDON is null and TYPECODE <> 1  -- Reversal
        )
        and FT.TYPECODE = 23;  -- Refund

    set @TOTAL -= @TOTALREFUND;

    set @ITEMS = (
        select 
            null as [SALESORDERITEMID], 
            0 as [INCLUDE],
            0 as [QUANTITYREFUNDING],
            1 as [QUANTITY],
            case EXT.[APPLICATIONCODE]
                when 5 then 
                    case
                        when [REVENUEPAYMENTMETHOD].[PAYMENTMETHODCODE] = 10 then
                            dbo.UFN_OTHERPAYMENTMETHODCODE_GETDESCRIPTION([OTHERPAYMENTMETHODCODEID])
                        else
                            [REVENUEPAYMENTMETHOD].[PAYMENTMETHOD] + 
                            coalesce(', ' + dbo.UFN_CREDITTYPECODE_GETDESCRIPTION([CREDITCARDPAYMENTMETHODDETAIL].[CREDITTYPECODEID]) + ' #' + [CREDITCARDPAYMENTMETHODDETAIL].[CREDITCARDPARTIALNUMBER], '') +
                            coalesce(' #' + [CHECKPAYMENTMETHODDETAIL].[CHECKNUMBER], '')
                    end -- Membership
                when 1 then NF.NAME + ' - ' + [EVENT].[NAME] -- Event registration
                when 0 then (select NAME from dbo.DESIGNATION where ID = EXT.[DESIGNATIONID]) -- Donation
            end as [DESCRIPTION],
            LI.BASEAMOUNT as [PRICE],
            0 as [FEES],
            LI.BASEAMOUNT as [TOTAL],
            0 as [REFUNDINGTOTAL],
            0 as [DISCOUNTS],
            0 as [DISCOUNTED],
            case EXT.[APPLICATIONCODE]
                when 5 then 1 -- Membership
                when 1 then 1 -- Event registration
                when 0 then 2 -- Donation
            end as [TYPECODE],
            null as [PROGRAMID],
            0 as [GROUPTYPECODE],
            null as [GROUPID]
        from 
            dbo.FINANCIALTRANSACTION as FT
        inner join
            dbo.FINANCIALTRANSACTIONLINEITEM as LI on LI.FINANCIALTRANSACTIONID = FT.ID
        inner join
            dbo.REVENUESPLIT_EXT as EXT on EXT.ID = LI.ID
        inner join 
            dbo.[REVENUEPAYMENTMETHOD] on [REVENUEPAYMENTMETHOD].[REVENUEID] = FT.ID
        left join 
            dbo.[CREDITCARDPAYMENTMETHODDETAIL] on [CREDITCARDPAYMENTMETHODDETAIL].[ID] = [REVENUEPAYMENTMETHOD].[ID]
        left join 
            dbo.[OTHERPAYMENTMETHODDETAIL] on [OTHERPAYMENTMETHODDETAIL].[ID] = [REVENUEPAYMENTMETHOD].[ID]
        left join 
            dbo.[CHECKPAYMENTMETHODDETAIL] on [CHECKPAYMENTMETHODDETAIL].[ID] = [REVENUEPAYMENTMETHOD].[ID]
        left join
            dbo.[EVENTREGISTRANTPAYMENT] on LI.[ID] = [EVENTREGISTRANTPAYMENT].[PAYMENTID]
        left join
            dbo.[REGISTRANT] on [EVENTREGISTRANTPAYMENT].[REGISTRANTID] = [REGISTRANT].[ID]
        left join
            dbo.[EVENT] on [REGISTRANT].[EVENTID] = [EVENT].[ID]
        outer apply
            dbo.UFN_CONSTITUENT_DISPLAYNAME(FT.CONSTITUENTID) as NF
        where 
            FT.[ID] = @ID
            and EXT.[APPLICATIONCODE] in (0,1,5)
            and FT.DELETEDON is null
            and LI.DELETEDON is null
            and LI.TYPECODE <> 1  -- Reversal
        for xml raw ('ITEM'), type, elements, root('ITEMS'), BINARY BASE64
    );

    with PAYMENTS_CTE as (
        --payments made on an order
        select
            0 as INCLUDE,
            case
                when [REVENUEPAYMENTMETHOD].[PAYMENTMETHODCODE] = 10 then
                    dbo.UFN_OTHERPAYMENTMETHODCODE_GETDESCRIPTION([OTHERPAYMENTMETHODDETAIL].[OTHERPAYMENTMETHODCODEID])
                else
                    [REVENUEPAYMENTMETHOD].[PAYMENTMETHOD] + 
                    coalesce(', ' + dbo.UFN_CREDITTYPECODE_GETDESCRIPTION([CREDITCARDPAYMENTMETHODDETAIL].[CREDITTYPECODEID]) + ' #' + [CREDITCARDPAYMENTMETHODDETAIL].[CREDITCARDPARTIALNUMBER], '') +
                    coalesce(' #' + [CHECKPAYMENTMETHODDETAIL].[CHECKNUMBER], '')
            end [DESCRIPTION],
            null as [DISPLAYSOURCE],
            [SALESORDER].[SEQUENCEID] as [SOURCENUMBER],
            0 as [SOURCETYPE],
            case
                when [SALESORDERPAYMENT].[AMOUNT] < [SALESORDERITEM].[TOTAL] then
                    [SALESORDERPAYMENT].[AMOUNT]
                else    
                    [SALESORDERITEM].[TOTAL]
            end [REFUNDAMOUNT],
            [REVENUEPAYMENTMETHOD].[PAYMENTMETHODCODE] as [PAYMENTTYPECODE],
            SALESORDERPAYMENT.PAYMENTID as [REVENUEID],
            null as [REVENUESPLITID],
            [CREDITCARDPAYMENTMETHODDETAIL].[TRANSACTIONID],
            sum(coalesce((LI.QUANTITY * LI.UNITVALUE) - EXT.DISCOUNTS, 0)) as [REFUNDS],
            [SALESORDERITEM].[ID] as [PAYMENTGROUPID],
            [SALESORDERITEM].[PRICE] as [PAYMENTGROUPMAXAMOUNT]
        from dbo.[SALESORDERITEMEVENTREGISTRATION]
        inner join dbo.[SALESORDERITEM] on [SALESORDERITEM].[ID] = [SALESORDERITEMEVENTREGISTRATION].[ID]
        inner join dbo.[SALESORDER] on [SALESORDERITEM].[SALESORDERID] = [SALESORDER].[ID]
        inner join dbo.[SALESORDERPAYMENT] on [SALESORDERITEM].[SALESORDERID] = [SALESORDERPAYMENT].[SALESORDERID]
        inner join dbo.[REVENUEPAYMENTMETHOD] on [REVENUEPAYMENTMETHOD].[REVENUEID] = SALESORDERPAYMENT.PAYMENTID
        left join dbo.[CREDITCARDPAYMENTMETHODDETAIL] on [CREDITCARDPAYMENTMETHODDETAIL].[ID] = [REVENUEPAYMENTMETHOD].[ID]
        left join dbo.[OTHERPAYMENTMETHODDETAIL] on [OTHERPAYMENTMETHODDETAIL].[ID] = [REVENUEPAYMENTMETHOD].[ID]
        left join dbo.[CHECKPAYMENTMETHODDETAIL] on [CHECKPAYMENTMETHODDETAIL].[ID] = [REVENUEPAYMENTMETHOD].[ID]
        left outer join dbo.CREDITITEM_EXT as EXT on EXT.[SALESORDERITEMID] = [SALESORDERITEM].[ID]
        left outer join dbo.FINANCIALTRANSACTIONLINEITEM as LI on LI.ID = EXT.ID
        where SALESORDERPAYMENT.PAYMENTID = @ID
        group by 
            SALESORDERPAYMENT.PAYMENTID,
            [REVENUEPAYMENTMETHOD].[PAYMENTMETHODCODE],
            [OTHERPAYMENTMETHODDETAIL].[OTHERPAYMENTMETHODCODEID],
            [REVENUEPAYMENTMETHOD].[PAYMENTMETHOD],
            [CREDITCARDPAYMENTMETHODDETAIL].[CREDITTYPECODEID],
            [CREDITCARDPAYMENTMETHODDETAIL].[CREDITCARDPARTIALNUMBER],
            [CREDITCARDPAYMENTMETHODDETAIL].[CREDITTYPECODEID],
            [CREDITCARDPAYMENTMETHODDETAIL].[CREDITCARDPARTIALNUMBER],
            [CHECKPAYMENTMETHODDETAIL].[CHECKNUMBER],
            [SALESORDER].[SEQUENCEID],
            [SALESORDERPAYMENT].[AMOUNT],
            [SALESORDERITEM].[TOTAL],
            [CREDITCARDPAYMENTMETHODDETAIL].[TRANSACTIONID],
            [SALESORDERITEM].[ID],
            [SALESORDERITEM].[PRICE]

        union all
        --payments made outside an order
        select 
            0 as INCLUDE,
            case
                when [REVENUEPAYMENTMETHOD].[PAYMENTMETHODCODE] = 10 then
                    dbo.UFN_OTHERPAYMENTMETHODCODE_GETDESCRIPTION([OTHERPAYMENTMETHODDETAIL].[OTHERPAYMENTMETHODCODEID])
                else
                    [REVENUEPAYMENTMETHOD].[PAYMENTMETHOD] + 
                    coalesce(', ' + dbo.UFN_CREDITTYPECODE_GETDESCRIPTION([CREDITCARDPAYMENTMETHODDETAIL].[CREDITTYPECODEID]) + ' #' + [CREDITCARDPAYMENTMETHODDETAIL].[CREDITCARDPARTIALNUMBER], '') +
                    coalesce(' #' + [CHECKPAYMENTMETHODDETAIL].[CHECKNUMBER], '')
            end [DESCRIPTION],
            null as [DISPLAYSOURCE],
            FT.SEQUENCEGENERATORID as [SOURCENUMBER],
            2 as [SOURCETYPE],
            case
                when REVENUE_EXT.BATCHNUMBER <> '' then
                    LI.BASEAMOUNT
                else
                    [REVENUEPAYMENTMETHOD].[AMOUNT]
            end as [REFUNDAMOUNT],
            [REVENUEPAYMENTMETHOD].[PAYMENTMETHODCODE] as [PAYMENTTYPECODE],
            null as [REVENUEID],
            LI.[ID] as [REVENUESPLITID],
            [CREDITCARDPAYMENTMETHODDETAIL].[TRANSACTIONID],
            sum(coalesce([CREDITPAYMENT].[AMOUNT], 0)) as [REFUNDS],
            LI.[ID] as [PAYMENTGROUPID],
            case
                when REVENUE_EXT.BATCHNUMBER <> '' then
                    LI.BASEAMOUNT
                else
                    [REVENUEPAYMENTMETHOD].[AMOUNT]
            end as [PAYMENTGROUPMAXAMOUNT]
        from dbo.FINANCIALTRANSACTION as FT
        inner join dbo.REVENUE_EXT on REVENUE_EXT.ID = FT.ID
        inner join dbo.FINANCIALTRANSACTIONLINEITEM as LI on LI.FINANCIALTRANSACTIONID = FT.ID
        inner join dbo.[REVENUEPAYMENTMETHOD] on FT.ID = [REVENUEPAYMENTMETHOD].[REVENUEID]
        left join dbo.[CREDITCARDPAYMENTMETHODDETAIL] on [CREDITCARDPAYMENTMETHODDETAIL].[ID] = [REVENUEPAYMENTMETHOD].[ID]
        left join dbo.[OTHERPAYMENTMETHODDETAIL] on [OTHERPAYMENTMETHODDETAIL].[ID] = [REVENUEPAYMENTMETHOD].[ID]
        left join dbo.[CHECKPAYMENTMETHODDETAIL] on [CHECKPAYMENTMETHODDETAIL].[ID] = [REVENUEPAYMENTMETHOD].[ID]
        left join dbo.[CREDITPAYMENT] on [CREDITPAYMENT].[REVENUESPLITID] = LI.[ID]
        where [REVENUEPAYMENTMETHOD].[PAYMENTMETHODCODE] <> 9 and FT.ID = @ID and LI.DELETEDON is null and LI.TYPECODE <> 1  -- Reversal
        group by 
            LI.[ID],
            [REVENUEPAYMENTMETHOD].[PAYMENTMETHODCODE],
            [OTHERPAYMENTMETHODDETAIL].[OTHERPAYMENTMETHODCODEID],
            [REVENUEPAYMENTMETHOD].[PAYMENTMETHOD],
            [CREDITCARDPAYMENTMETHODDETAIL].[CREDITTYPECODEID],
            [CREDITCARDPAYMENTMETHODDETAIL].[CREDITCARDPARTIALNUMBER],
            [CHECKPAYMENTMETHODDETAIL].[CHECKNUMBER],
            FT.SEQUENCEGENERATORID,
            [REVENUEPAYMENTMETHOD].[AMOUNT],
            [CREDITCARDPAYMENTMETHODDETAIL].[TRANSACTIONID],
            REVENUE_EXT.BATCHNUMBER,
            LI.BASEAMOUNT
    )                
    select @PAYMENTS = (
        select
            [INCLUDE],
            [DESCRIPTION],
            '$' + cast(cast([REFUNDAMOUNT] as money) as nvarchar(20)) + ' ' + [DESCRIPTION] as [DISPLAYDESCRIPTION],
            [DISPLAYSOURCE],
            [SOURCENUMBER],
            [SOURCETYPE],
            [REFUNDAMOUNT] - coalesce([REFUNDS], 0) as [AMOUNTPAID],
            [PAYMENTTYPECODE],
            [REVENUEID],
            [REVENUESPLITID],
            [TRANSACTIONID],
            [PAYMENTGROUPID],
            [PAYMENTGROUPMAXAMOUNT]
        from PAYMENTS_CTE
        where [REFUNDAMOUNT] - coalesce([REFUNDS], 0) > 0
        order by [SOURCETYPE], [SOURCENUMBER]
        for xml raw ('ITEM'), type, elements, root('PAYMENTS'), BINARY BASE64
    );

    return 0;