UFN_DOCUMENT_GETPRINTINGHISTORY

CREATE function dbo.UFN_DOCUMENT_GETPRINTINGHISTORY(@SALESORDERID uniqueidentifier)
returns table            
as return
    -- This CTE looks overly complicated, but it is useful for finding

    -- the proper DOCUMENTPRINTINGHISTORY records only using indexes for SALESORDERID

    with PRINTEDDOCUMENTSFORSALESORDER_CTE as (
        select ID from dbo.DOCUMENTPRINTINGHISTORY where SALESORDERID = @SALESORDERID
        union
        select DOCUMENTPRINTINGHISTORY.ID
        from dbo.DOCUMENTPRINTINGHISTORY
        inner join UFN_SALESORDER_REFUNDS(@SALESORDERID) as REFUNDTRANSACTIONS on REFUNDTRANSACTIONS.ID = DOCUMENTPRINTINGHISTORY.CREDITID
    )
    select
        DOCUMENTPRINTINGHISTORY.ID,
        DOCUMENTPRINTINGHISTORY.TYPECODE,
        DOCUMENTPRINTINGHISTORY.CREDITCARDPAYMENTMETHODDETAILID,
        DOCUMENTPRINTINGHISTORY.CREDITID,
        DOCUMENTPRINTINGHISTORY.CREDITPAYMENTID,
        case DOCUMENTPRINTINGHISTORY.TYPECODE
            when 2 then  -- Credit Card Receipt

                DOCUMENTPRINTINGHISTORY.TYPE + ':  ' + CURRENCY.CURRENCYSYMBOL +
                cast(SALESORDERPAYMENT.AMOUNT as nvarchar) + ' *' +  
                CREDITCARDPAYMENTMETHODDETAIL.CREDITCARDPARTIALNUMBER + ' ' + 
                dbo.UFN_CREDITTYPECODE_GETDESCRIPTION(CREDITCARDPAYMENTMETHODDETAIL.CREDITTYPECODEID)
            when 5 then  -- Refund Itemized Receipt

                DOCUMENTPRINTINGHISTORY.TYPE + ':  (' + CURRENCY.CURRENCYSYMBOL +
                cast(FT.TRANSACTIONAMOUNT as nvarchar) + ') ' +
                convert(nvarchar, FT.DATEADDED, 101)
            when 6 then  -- Refund Credit Card Receipt

                DOCUMENTPRINTINGHISTORY.TYPE + ':  (' + CURRENCY.CURRENCYSYMBOL +
                cast(CREDITPAYMENT.AMOUNT as nvarchar) + ') ' + ' *' +  
                CREDITCREDITCARDPAYMENTMETHODDETAIL.CREDITCARDPARTIALNUMBER + ' ' + 
                dbo.UFN_CREDITTYPECODE_GETDESCRIPTION(CREDITCREDITCARDPAYMENTMETHODDETAIL.CREDITTYPECODEID) + ' ' +
                convert(nvarchar, FT.DATEADDED, 101)
            else
                DOCUMENTPRINTINGHISTORY.TYPE
        end as [TYPE],
        DOCUMENTPRINTINGHISTORY.PRINTDATEWITHTIMEOFFSET PRINTEDDATE,
        convert(nvarchar, cast(DOCUMENTPRINTINGHISTORY.PRINTDATEWITHTIMEOFFSET as time), 100) PRINTEDTIME,
        APPUSER.DISPLAYNAME APPUSER,
        WORKSTATION.NAME WORKSTATION
    from
        PRINTEDDOCUMENTSFORSALESORDER_CTE
    inner join
        dbo.DOCUMENTPRINTINGHISTORY on DOCUMENTPRINTINGHISTORY.ID = PRINTEDDOCUMENTSFORSALESORDER_CTE.ID
    inner join
        dbo.APPUSER on DOCUMENTPRINTINGHISTORY.APPUSERID = APPUSER.ID
    left outer join
        dbo.WORKSTATION on DOCUMENTPRINTINGHISTORY.WORKSTATIONID = WORKSTATION.ID
    left outer join
        dbo.CREDITCARDPAYMENTMETHODDETAIL on DOCUMENTPRINTINGHISTORY.CREDITCARDPAYMENTMETHODDETAILID = CREDITCARDPAYMENTMETHODDETAIL.ID
    left outer join
        dbo.REVENUEPAYMENTMETHOD on CREDITCARDPAYMENTMETHODDETAIL.ID = REVENUEPAYMENTMETHOD.ID
    left outer join (
        select ID, SALESORDERID, PAYMENTID, AMOUNT from dbo.SALESORDERPAYMENT
        union all
        select ID, RESERVATIONID as SALESORDERID, PAYMENTID, AMOUNT from dbo.RESERVATIONSECURITYDEPOSITPAYMENT
    ) as SALESORDERPAYMENT on SALESORDERPAYMENT.PAYMENTID = REVENUEPAYMENTMETHOD.REVENUEID
    left outer join
        dbo.FINANCIALTRANSACTION as FT on FT.ID = DOCUMENTPRINTINGHISTORY.CREDITID
    left outer join
        dbo.CREDITPAYMENT on DOCUMENTPRINTINGHISTORY.CREDITPAYMENTID = CREDITPAYMENT.ID
    left outer join
        dbo.REVENUEPAYMENTMETHOD CREDITREVENUEPAYMENTMETHOD on CREDITREVENUEPAYMENTMETHOD.REVENUEID = CREDITPAYMENT.REVENUEID
    left outer join
        dbo.CREDITCARDPAYMENTMETHODDETAIL CREDITCREDITCARDPAYMENTMETHODDETAIL on CREDITREVENUEPAYMENTMETHOD.PAYMENTMETHODCODE = 2 and CREDITCREDITCARDPAYMENTMETHODDETAIL.ID = CREDITREVENUEPAYMENTMETHOD.ID
    outer apply
        (select top 1 CURRENCYSYMBOL from dbo.CURRENCY where ISORGANIZATIONCURRENCY = 1) as CURRENCY