UFN_REFUND_GETCREDITACCOUNT_GROUPSALES

            create function dbo.UFN_REFUND_GETCREDITACCOUNT_GROUPSALES(@CREDITPAYMENTID uniqueidentifier)
            returns nvarchar(100)
            as begin
                declare @ACCOUNT nvarchar(100) = '';
                declare @ACCOUNTID uniqueidentifier;
                declare @CREDITCARDSUBTYPEID uniqueidentifier;
                declare @OTHERSUBTYPEID uniqueidentifier;
                declare @PAYMENTMETHOD int;
                declare @PDACCOUNTSEGMENTVALUEID uniqueidentifier;
                declare @ACCOUNTCODE nvarchar(100);

                select
                    @CREDITCARDSUBTYPEID = case
                        when CREDITPAYMENT.PAYMENTMETHODCODE = 2
                            then CREDITCARDPAYMENTMETHODDETAIL.CREDITTYPECODEID
                        else
                            null
                        end,
                    @OTHERSUBTYPEID = CREDITPAYMENT.OTHERPAYMENTMETHODCODEID
                from dbo.CREDITPAYMENT
                inner join dbo.CREDIT on CREDITPAYMENT.CREDITID = CREDIT.ID
                inner join dbo.REVENUEPAYMENTMETHOD on  CREDITPAYMENT.REVENUEID = REVENUEPAYMENTMETHOD.REVENUEID
                left join dbo.CREDITCARDPAYMENTMETHODDETAIL on CREDITCARDPAYMENTMETHODDETAIL.ID = REVENUEPAYMENTMETHOD.ID 
                where CREDITPAYMENT.ID = @CREDITPAYMENTID;

                select @PAYMENTMETHOD =
                    case PAYMENTMETHODCODE
                    when 0 then 1 -- cash

                    when 1 then 2 --check

                    when 2 then 4 -- credit card

                    when 3 then 8 --direct debit

                    when 10 then 16 --other

                    end
                from dbo.CREDITPAYMENT
                where CREDITPAYMENT.ID = @CREDITPAYMENTID

                select
                    @ACCOUNTID = MAP.DEBITGLACCOUNTID,
                    @PDACCOUNTSEGMENTVALUEID = MAP.DEBITPDACCOUNTSEGMENTVALUEID
                from
                    dbo.PDACCOUNTCODEMAPPING MAP
                    left join dbo.PDACCOUNTCODEMAPPINGSUBTYPE SUBTYPE on SUBTYPE.PDACCOUNTCODEMAPPINGID = MAP.ID
                where
                    MAP.OFFICEID = 2 --ticketing payments

                    and MAP.APPLICATIONTYPE = 1 --applied to orders

                    and MAP.REVENUETYPE & 8192 = 8192 --unearned revenue

                    and MAP.PAYMENTMETHOD & @PAYMENTMETHOD = @PAYMENTMETHOD --given payment method

                    and
                        ((@CREDITCARDSUBTYPEID is null or SUBTYPE.ID is null or SUBTYPE.SUBTYPEID = @CREDITCARDSUBTYPEID)
                                or (SUBTYPE.ADDITIONALSUBTYPE = 2 and SUBTYPE.SUBTYPEID = '99999999-9999-9999-9999-999999999999')) -- "All subtypes" mapping option for credit cards

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


                if @ACCOUNTID is null
                begin
                    if @PDACCOUNTSEGMENTVALUEID is not null
                        begin
                            declare @SOURCEACCOUNTID uniqueidentifier

                            select
                                @SOURCEACCOUNTID = JE.GLACCOUNTID
                            from
                                dbo.CREDITPAYMENT CP
                                inner join dbo.FINANCIALTRANSACTION REFUND on REFUND.ID = CP.CREDITID
                                inner join dbo.FINANCIALTRANSACTIONLINEITEM REFUNDLI on REFUNDLI.FINANCIALTRANSACTIONID = REFUND.ID
                                inner join dbo.FINANCIALTRANSACTIONLINEITEM LI on LI.ID = REFUNDLI.SOURCELINEITEMID
                                inner join dbo.REVENUESPLIT_EXT RSX on RSX.ID = LI.ID
                                inner join dbo.JOURNALENTRY JE on JE.FINANCIALTRANSACTIONLINEITEMID = LI.ID
                            where
                                CP.ID = @CREDITPAYMENTID
                                and RSX.TYPECODE = 19
                                and JE.TRANSACTIONTYPECODE = 0

                            select
                                @ACCOUNTCODE = SHORTDESCRIPTION
                            from
                                PDACCOUNTSEGMENTVALUE
                            where
                                ID = @PDACCOUNTSEGMENTVALUEID 
                                and PDACCOUNTSTRUCTUREID = '90C72BDA-7994-401B-8A9F-7B36FA65D9FC'; --primary sequence id (account code)


                            select @ACCOUNT = @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