USP_CREDIT_ADDCREDITPAYMENTS

CREATE procedure dbo.USP_CREDIT_ADDCREDITPAYMENTS
(
    @CREDITID uniqueidentifier,
    @REFUNDMETHODS xml,
    @CHANGEAGENTID uniqueidentifier,
    @CURRENTAPPUSERID uniqueidentifier,
    @ADDRESSID uniqueidentifier = null
)
as
begin

    -- Verify that the credit card we want to refund to has actually been processed

    if exists
    (
        select
            CREDITCARDPAYMENTMETHODDETAIL.ID
        from dbo.CREDITCARDPAYMENTMETHODDETAIL
        inner join dbo.REVENUEPAYMENTMETHOD on REVENUEPAYMENTMETHOD.ID = CREDITCARDPAYMENTMETHODDETAIL.ID
        where
            REVENUEPAYMENTMETHOD.REVENUEID in (
                select
                    case when T.item.value('(@REVENUEID)[1]','uniqueidentifier') = '00000000-0000-0000-0000-000000000000'
                        then (select FINANCIALTRANSACTIONID from dbo.FINANCIALTRANSACTIONLINEITEM where ID = T.item.value('(@REVENUESPLITID)[1]','uniqueidentifier'))
                        else T.item.value('(@REVENUEID)[1]','uniqueidentifier')
                    end
                from @REFUNDMETHODS.nodes('/REFUNDMETHODS/ITEM') T(item)
                where T.item.value('(@PAYMENTTYPECODE)[1]','integer') = 2
            )
            and CREDITCARDPAYMENTMETHODDETAIL.TRANSACTIONID is null
    )
    begin
        raiserror('BBERR_REFUNDCREDITCARDPAYMENT_MUSTBEPROCESSED', 16, 1);
        return 1;
    end;

    -- Error if there are matching gifts

    if exists
    (
        select 1
        from dbo.REVENUEMATCHINGGIFT
        inner join @REFUNDMETHODS.nodes('/REFUNDMETHODS/ITEM') T(item) on
            T.item.value('(@REVENUEID)[1]', 'uniqueidentifier') = REVENUEMATCHINGGIFT.MGSOURCEREVENUEID
    ) begin
        raiserror('ERR_REFUNDMETHODS_DONATIONMATCHINGGIFTS', 13, 1);
        return 1;
    end;

    declare @CURRENTDATE datetime = getdate();
    declare @CURRENTDATETIMEOFFSET datetimeoffset = dbo.UFN_TIMEZONEENTRY_GETSYSTEMDEFAULTDATEWITHTIMEOFFSET(getutcdate(), 1);

    insert into dbo.CREDITPAYMENT
    (
        ID,
        CREDITID,
        APPUSERID,
        CREDITPAYMENTDATEWITHTIMEOFFSET,
        AMOUNT,
        PAYMENTMETHODCODE,
        OTHERPAYMENTMETHODCODEID,
        REVENUEID,
        REVENUESPLITID,
        REFUNDPROCESSED,
        [STATUS],
        ADDEDBYID,
        CHANGEDBYID,
        DATEADDED,
        DATECHANGED
    )
    select
        T.item.value('(@CREDITPAYMENTID)[1]','uniqueidentifier'),
        @CREDITID,
        @CURRENTAPPUSERID,
        @CURRENTDATETIMEOFFSET,
        T.item.value('(@AMOUNTREFUNDING)[1]','money'),
        T.item.value('(@PAYMENTTYPECODE)[1]','tinyint'),
        case
            when T.item.value('(@PAYMENTTYPECODE)[1]','tinyint') = 10
                then T.item.value('(@OTHERPAYMENTMETHODCODEID)[1]','uniqueidentifier')
            else
                null
        end,
        case
            when T.item.value('(@REVENUEID)[1]','uniqueidentifier') = '00000000-0000-0000-0000-000000000000'
                then null
            else
                T.item.value('(@REVENUEID)[1]','uniqueidentifier')
        end,
        case
            when T.item.value('(@REVENUESPLITID)[1]','uniqueidentifier') = '00000000-0000-0000-0000-000000000000'
                then null
            else
                T.item.value('(@REVENUESPLITID)[1]','uniqueidentifier')
        end,
        case T.item.value('(@PAYMENTTYPECODE)[1]','tinyint')
            when 2 then 0
            else 1
        end,
        T.item.value('(@STATUS)[1]','nvarchar(255)'),
        @CHANGEAGENTID,
        @CHANGEAGENTID,
        @CURRENTDATE,
        @CURRENTDATE
    from @REFUNDMETHODS.nodes('/REFUNDMETHODS/ITEM') T(item);

    insert into dbo.CREDITPAYMENTCHECKPAYMENTMETHODDETAIL
    (
        ID,
        ADDRESSID,
        ADDEDBYID,
        CHANGEDBYID,
        DATEADDED,
        DATECHANGED
    )
    select
        T.item.value('(@CREDITPAYMENTID)[1]','uniqueidentifier'),
        case when @ADDRESSID = '00000000-0000-0000-0000-000000000000' then null else @ADDRESSID end,
        @CHANGEAGENTID,
        @CHANGEAGENTID,
        @CURRENTDATE,
        @CURRENTDATE
    from
        @REFUNDMETHODS.nodes('/REFUNDMETHODS/ITEM') T(item)
    where
        T.item.value('(@PAYMENTTYPECODE)[1]','tinyint') = 1;

end