UFN_RECONCILIATION_GETOTHERRECEIPTS_WITH_REFUNDDETAIL

CREATE function dbo.UFN_RECONCILIATION_GETOTHERRECEIPTS_WITH_REFUNDDETAIL
(
    @RECONCILIATIONID uniqueidentifier,
    @APPUSERID uniqueidentifier = null,
    @WORKSTATIONID uniqueidentifier = null
) returns table 
as return
        select T.ID
            ,coalesce(CREDITTYPECODE.DESCRIPTION, OTHERPAYMENTMETHODCODE.DESCRIPTION, T.PAYMENTMETHOD) as PAYMENTMETHOD
            ,T.TOTALAMOUNT
            ,T.EXPECTED
            ,T.PAYMENTMETHODCODE
            ,ISREFUND
            ,case when NONDEPOSITABLEPAYMENTMETHOD.ID is null then 1 else 0 end as DEPOSITABLE
            ,T.CREDITTYPECODEID
            ,T.OTHERPAYMENTMETHODCODEID
        from dbo.UFN_RECONCILIATION_GETOTHERRECEIPTS(@RECONCILIATIONID, @APPUSERID) T
        left join dbo.CREDITTYPECODE on T.CREDITTYPECODEID = CREDITTYPECODE.ID
        left join dbo.OTHERPAYMENTMETHODCODE on T.OTHERPAYMENTMETHODCODEID = OTHERPAYMENTMETHODCODE.ID
        left join dbo.NONDEPOSITABLEPAYMENTMETHOD on NONDEPOSITABLEPAYMENTMETHOD.ID = OTHERPAYMENTMETHODCODE.ID
        where T.ISREFUND = 0

        union all

        select
            @RECONCILIATIONID
            ,T.PAYMENTMETHOD
            ,sum(AMOUNT)
            ,count(1)
            ,T.PAYMENTMETHODCODE
            ,1
            ,T.DEPOSITABLE
            ,T.CREDITTYPECODEID
            ,T.OTHERPAYMENTMETHODCODEID
        from (
            select
                coalesce(CREDITTYPECODE.DESCRIPTION, OTHERPAYMENTMETHODCODE.DESCRIPTION, CP.PAYMENTMETHOD) as PAYMENTMETHOD
                ,CP.AMOUNT
                ,CP.PAYMENTMETHODCODE
                ,case when NONDEPOSITABLEPAYMENTMETHOD.ID is null then 1 else 0 end as DEPOSITABLE
                ,D.CREDITTYPECODEID
                ,CP.OTHERPAYMENTMETHODCODEID
            from dbo.CREDITPAYMENT CP
            inner join dbo.FINANCIALTRANSACTION as FT on FT.ID = CP.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 PM on PM.REVENUEID = CP.REVENUEID
            left join dbo.CREDITCARDPAYMENTMETHODDETAIL D on D.ID = PM.ID and CP.PAYMENTMETHODCODE = 2
            left join dbo.CREDITTYPECODE on D.CREDITTYPECODEID = CREDITTYPECODE.ID
            left join dbo.OTHERPAYMENTMETHODCODE on CP.OTHERPAYMENTMETHODCODEID = OTHERPAYMENTMETHODCODE.ID and CP.PAYMENTMETHODCODE = 10
            left join dbo.NONDEPOSITABLEPAYMENTMETHOD on NONDEPOSITABLEPAYMENTMETHOD.ID = OTHERPAYMENTMETHODCODE.ID
            where
                CP.PAYMENTMETHODCODE != 0  -- Cash

                and (SALESORDER.ID is not null or EXT.SALESORDERID is not null)
                and (@APPUSERID is null or CP.APPUSERID = @APPUSERID)
                and (
                    CP.RECONCILIATIONID = @RECONCILIATIONID 
                    or (
                        CP.RECONCILIATIONID is null
                        and CP.REFUNDPROCESSED = 1
                        and (select STATUSCODE from dbo.RECONCILIATION where ID = @RECONCILIATIONID) = 0
                    )
                )
        ) T
        group by
            T.PAYMENTMETHOD, T.PAYMENTMETHODCODE, T.DEPOSITABLE, T.CREDITTYPECODEID, T.OTHERPAYMENTMETHODCODEID