USP_DATALIST_CLOSEDRAWER_OTHERTRANSACTIONS_DETAIL

CREATE procedure dbo.USP_DATALIST_CLOSEDRAWER_OTHERTRANSACTIONS_DETAIL(
    @CONTEXTID uniqueidentifier
    ,@USERID uniqueidentifier = null
    ,@WORKSTATIONID uniqueidentifier = null
)
as
    set nocount on;

    select 
        T.PAYMENTMETHODCODE
        ,T.PAYMENTMETHOD
        ,T.ISREFUND
        ,T.TOTALAMOUNT
        ,T.DATE
        ,T.CARDHOLDERNAME
        ,T.AUTHORIZATIONCODE
        ,T.CREDITCARDPARTIALNUMBER
    from (
        select
            T.PAYMENTMETHODCODE
            ,coalesce(CREDITTYPECODE.DESCRIPTION, OTHERPAYMENTMETHODCODE.DESCRIPTION, T.PAYMENTMETHOD) as PAYMENTMETHOD
            ,T.ISREFUND
            ,T.TOTALAMOUNT
            ,T.DATE
            ,T.CARDHOLDERNAME
            ,T.AUTHORIZATIONCODE
            ,T.CREDITCARDPARTIALNUMBER
        from dbo.[UFN_RECONCILIATION_GETOTHERRECEIPTS_FULL](@CONTEXTID, @USERID, @WORKSTATIONID) T
        left join dbo.CREDITTYPECODE on T.CREDITTYPECODEID = CREDITTYPECODE.ID
        left join dbo.OTHERPAYMENTMETHODCODE on T.OTHERPAYMENTMETHODCODEID = OTHERPAYMENTMETHODCODE.ID
        where (T.PAYMENTMETHODCODE in (2, 10))
            and ISREFUND = 0

        union all
        select 
            CP.PAYMENTMETHODCODE
            ,coalesce(CREDITTYPECODE.DESCRIPTION, OTHERPAYMENTMETHODCODE.DESCRIPTION, CP.PAYMENTMETHOD) as PAYMENTMETHOD
            ,1
            ,CP.AMOUNT
            ,cast(CREDITPAYMENTDATEWITHTIMEOFFSET as date)
            ,D.CARDHOLDERNAME
            ,D.AUTHORIZATIONCODE
            ,D.CREDITCARDPARTIALNUMBER
        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
            and (SALESORDER.ID is not null or EXT.SALESORDERID is not null)
            and (@USERID is null or CP.APPUSERID = @USERID)
            and (CP.RECONCILIATIONID = @CONTEXTID 
                or (CP.RECONCILIATIONID is null
                    and CP.REFUNDPROCESSED = 1
                    and (select STATUSCODE from dbo.RECONCILIATION where ID = @CONTEXTID) = 0))) T
    order by T.ISREFUND, T.PAYMENTMETHODCODE, T.PAYMENTMETHOD, T.DATE

    return 0;