UFN_REFUND_GETCREDITACCOUNT

CREATE function dbo.UFN_REFUND_GETCREDITACCOUNT(
    @SOURCEACCOUNTID uniqueidentifier,
    @CREDITPAYMENTID uniqueidentifier,
    @SOURCESPLITID uniqueidentifier
)
returns nvarchar(100)
as begin
    declare @PDACCOUNTSEGMENTVALUEID uniqueidentifier;
    declare @REFUNDPAYMETHOD tinyint;
    declare @REVENUETYPE tinyint;
    declare @ACCOUNTCODE nvarchar(30) = '';
    declare @ACCOUNT nvarchar(100) = '';
    declare @ACCOUNTID uniqueidentifier;
    declare @APPLICATIONCODE tinyint;
    declare @SALESORDERID uniqueidentifier;
    declare @CREDITCARDSUBTYPEID uniqueidentifier;
    declare @OTHERSUBTYPEID uniqueidentifier;

    select
        @REFUNDPAYMETHOD = CREDITPAYMENT.PAYMENTMETHODCODE,
        @SALESORDERID = isnull(SALESORDER.ID, EXT.SALESORDERID),
        @CREDITCARDSUBTYPEID = case
            when CREDITPAYMENT.PAYMENTMETHODCODE = 2
                then CREDITCARDPAYMENTMETHODDETAIL.CREDITTYPECODEID
            else
                null
            end,
        @OTHERSUBTYPEID = CREDITPAYMENT.OTHERPAYMENTMETHODCODEID
    from
        dbo.CREDITPAYMENT
    inner join
        dbo.FINANCIALTRANSACTION as FT on FT.ID = CREDITPAYMENT.CREDITID
    inner join
        dbo.CREDIT_EXT as EXT on EXT.ID = FT.ID
    left outer join
        dbo.SALESORDER on SALESORDER.REVENUEID = FT.PARENTID
    left join
        dbo.REVENUEPAYMENTMETHOD on  CREDITPAYMENT.REVENUEID = REVENUEPAYMENTMETHOD.REVENUEID
    left join
        dbo.CREDITCARDPAYMENTMETHODDETAIL on CREDITCARDPAYMENTMETHODDETAIL.ID = REVENUEPAYMENTMETHOD.ID 
    where
        CREDITPAYMENT.ID = @CREDITPAYMENTID;

    if @SALESORDERID is null and @SOURCEACCOUNTID is null begin
        select @SALESORDERID = SPLITMAPJOURNALENTRY.ID
        from dbo.FINANCIALTRANSACTIONLINEITEM as SPLITMAPLINEITEM
        inner join dbo.JOURNALENTRY as SPLITMAPJOURNALENTRY on SPLITMAPJOURNALENTRY.FINANCIALTRANSACTIONLINEITEMID = SPLITMAPLINEITEM.ID
        inner join dbo.PDACCOUNTCODEMAPPING on PDACCOUNTCODEMAPPING.CREDITGLACCOUNTID = SPLITMAPJOURNALENTRY.GLACCOUNTID
        inner join dbo.CREDITPAYMENT on CREDITPAYMENT.REVENUEID = SPLITMAPLINEITEM.FINANCIALTRANSACTIONID or CREDITPAYMENT.REVENUESPLITID = SPLITMAPLINEITEM.ID
        where CREDITPAYMENT.ID = @CREDITPAYMENTID and OFFICEID = 2
    end

    select
        @REVENUETYPE = TYPECODE,
        @APPLICATIONCODE = APPLICATIONCODE
    from
        dbo.REVENUESPLIT_EXT
    where
        ID = @SOURCESPLITID;

    declare @OFFICEID tinyint = 2;  --ticketing


    if @SALESORDERID is null
        set @OFFICEID = 3 --development


    select
        @ACCOUNTID =  DEBITGLACCOUNTID,
        @PDACCOUNTSEGMENTVALUEID = DEBITPDACCOUNTSEGMENTVALUEID
    from
        dbo.PDACCOUNTCODEMAPPING CM
    left join
        dbo.PDACCOUNTCODEMAPPINGSUBTYPE CMST on CMST.PDACCOUNTCODEMAPPINGID = CM.ID
    where
        (
            @CREDITCARDSUBTYPEID is null
            or CMST.ID is null
            or CMST.SUBTYPEID = @CREDITCARDSUBTYPEID 
            or (
                CMST.ADDITIONALSUBTYPE = 2
                and CMST.SUBTYPEID = '99999999-9999-9999-9999-999999999999' -- "All subtypes" mapping option for credit cards

            )
        )
        and (
            @OTHERSUBTYPEID is null
            or CMST.ID is null
            or CMST.SUBTYPEID = @OTHERSUBTYPEID 
            or (
                CMST.ADDITIONALSUBTYPE = 4
                and CMST.SUBTYPEID = '99999999-9999-9999-9999-999999999999' -- "All subtypes" mapping option for Other payment methods

            )
        )
        and OFFICEID = @OFFICEID
        and PDACCOUNTSYSTEMID = '4B121C2C-CCE6-440D-894C-EA0DEF80D50B'
        and (
            PAYMENTMETHOD & 
            case @REFUNDPAYMETHOD
                when 0 then 1 --Cash

                when 1 then 2 --Check

                when 2 then 4 --Credit Card

                when 10 then 16 --Other

            end
        ) > 0
        and (
            (
                @OFFICEID = 2 --ticketing

                and (
                    REVENUETYPE &
                    case @REVENUETYPE
                        when 0 then 64 -- donation

                        when 2 then 128 -- membership

                        when 1 then 256 -- event registration

                        when 5 then 3 --Admission

                        when 6 then 4 --Fee

                        when 7 then 8 --Taxes

                        when 10 then 16 -- supply resources

                        when 11 then 32 --staff resources

                        when 13 then 512 -- Security deposit

                        when 16 then 2048 -- Merchandise

                        when 18 then 4096 --membership add-on

                        when 19 then 8192 -- group sales liability

                        else 1
                    end
                ) > 0
            )
            or (
                @OFFICEID = 3 --development

                and (
                    APPLICATIONTYPE & 
                    case @APPLICATIONCODE
                        when 1 then 1 -- event registration

                        when 5 then 64 --Membership

                        when 18 then 262144 --membership add-on

                        else 1
                    end
                ) > 0
            )
        )
        option (recompile);

    if @ACCOUNTID is null begin
        if @PDACCOUNTSEGMENTVALUEID is not null begin
            select @ACCOUNTCODE = SHORTDESCRIPTION from PDACCOUNTSEGMENTVALUE where ID = @PDACCOUNTSEGMENTVALUEID and PDACCOUNTSTRUCTUREID = '90C72BDA-7994-401B-8A9F-7B36FA65D9FC';

            select @ACCOUNT +=
                case
                    when VALUE.PDACCOUNTSTRUCTUREID = '90C72BDA-7994-401B-8A9F-7B36FA65D9FC' then
                        @ACCOUNTCODE
                    else
                        VALUE.SHORTDESCRIPTION
                end + substring(STRUCT.SEPARATOR, 0, 2)
            from dbo.PDACCOUNTSEGMENT SEG
            inner join dbo.PDACCOUNTSTRUCTURE STRUCT on SEG.PDACCOUNTSTRUCTUREID = STRUCT.ID
            inner join dbo.PDACCOUNTSEGMENTVALUE VALUE on SEG.PDACCOUNTSEGMENTVALUEID = VALUE.ID
            where SEG.GLACCOUNTID = @SOURCEACCOUNTID and STRUCT.PDACCOUNTSYSTEMID = '4B121C2C-CCE6-440D-894C-EA0DEF80D50B'
            order by STRUCT.SEQUENCE asc;

            set @ACCOUNT = substring(@ACCOUNT, 0, len(@ACCOUNT));
        end
    end
    else begin
        select @ACCOUNT = ACCOUNTNUMBER
        from dbo.GLACCOUNT
        where ID = @ACCOUNTID;
    end

    return @ACCOUNT;
end