UFN_DOCUMENT_GETSALESDOCUMENTSTOPRINTCREDIT

CREATE function dbo.UFN_DOCUMENT_GETSALESDOCUMENTSTOPRINTCREDIT(@CREDITID uniqueidentifier)
returns @SALESDOCUMENTSTOPRINT table
(
    TYPE nvarchar(100),
    TYPECODE tinyint,                
    CREDITCARDPAYMENTMETHODDETAILID uniqueidentifier,                
    CREDITPAYMENTID uniqueidentifier,
    PRINTED bit,
    TOPRINT bit
)
as begin
    declare @CURRENCYSYMBOL nvarchar(5)

    select top 1 @CURRENCYSYMBOL = CURRENCYSYMBOL from dbo.CURRENCY where ISORGANIZATIONCURRENCY = 1

    declare @ISSECURITYDEPOSITREFUND bit = 0

    if exists (
        select *
        from dbo.FINANCIALTRANSACTIONLINEITEM as LI
        inner join dbo.CREDITITEM_EXT as EXT on EXT.ID = LI.ID
        where LI.FINANCIALTRANSACTIONID = @CREDITID and EXT.TYPECODE = 12  -- Reservation security deposit

    ) begin
        set @ISSECURITYDEPOSITREFUND = 1
    end

    insert into @SALESDOCUMENTSTOPRINT
    --already printed documents

    select distinct
        PRINTINGHISTORY.TYPE,
        PRINTINGHISTORY.TYPECODE,                        
        PRINTINGHISTORY.CREDITCARDPAYMENTMETHODDETAILID,                                
        PRINTINGHISTORY.CREDITPAYMENTID,
        1 PRINTED,
        0 TOPRINT
    from
        dbo.UFN_DOCUMENT_GETPRINTINGHISTORYCREDIT(@CREDITID) PRINTINGHISTORY

    union all
    --refunds itemized receipts that should print

    select distinct
        DOCUMENT.TYPE + ':  (' + @CURRENCYSYMBOL +
        cast(FT.TRANSACTIONAMOUNT as nvarchar) + ') ' +
        convert(nvarchar, FT.DATEADDED, 101) TYPE,
        DOCUMENT.TYPECODE,                        
        null CREDITCARDPAYMENTMETHODDETAILID,                                
        null CREDITPAYMENTID,
        0 PRINTED,
        1 TOPRINT
    from
        dbo.FINANCIALTRANSACTION as FT
    left outer join
        dbo.DOCUMENTPRINTINGHISTORY on DOCUMENTPRINTINGHISTORY.TYPECODE = 5 and DOCUMENTPRINTINGHISTORY.CREDITID = FT.ID
    outer apply
        dbo.DOCUMENT
    where
        FT.ID = @CREDITID and
        FT.TYPECODE = 23 and  -- Refund

        DOCUMENT.TYPECODE = 5 and  -- Refund Itemized Receipt

        DOCUMENTPRINTINGHISTORY.ID is null and
        @ISSECURITYDEPOSITREFUND = 0

    union all
    --refund credit card receipts that should print

    select distinct
        DOCUMENT.TYPE + ':  (' + @CURRENCYSYMBOL +
        cast(CREDITPAYMENT.AMOUNT as nvarchar) + ') ' + ' *' +  
        CREDITCARDPAYMENTMETHODDETAIL.CREDITCARDPARTIALNUMBER + ' ' + 
        dbo.UFN_CREDITTYPECODE_GETDESCRIPTION(CREDITCARDPAYMENTMETHODDETAIL.CREDITTYPECODEID) + ' ' +
        convert(nvarchar, FT.DATEADDED, 101)
        TYPE,
        DOCUMENT.TYPECODE,                        
        CREDITCARDPAYMENTMETHODDETAIL.ID CREDITCARDPAYMENTMETHODDETAILID,                                
        CREDITPAYMENT.ID,
        0 PRINTED,
        1 TOPRINT
    from
        dbo.FINANCIALTRANSACTION as FT
    inner join
        dbo.CREDITPAYMENT on CREDITPAYMENT.CREDITID = FT.ID
    left outer join
        dbo.FINANCIALTRANSACTIONLINEITEM as REFUNDEDLI on REFUNDEDLI.ID = CREDITPAYMENT.REVENUESPLITID and REFUNDEDLI.DELETEDON is null and REFUNDEDLI.TYPECODE <> 1  -- Reversal

    inner join
        dbo.REVENUEPAYMENTMETHOD on REVENUEPAYMENTMETHOD.REVENUEID = isnull(CREDITPAYMENT.REVENUEID, REFUNDEDLI.FINANCIALTRANSACTIONID)
    inner join
        dbo.CREDITCARDPAYMENTMETHODDETAIL on REVENUEPAYMENTMETHOD.PAYMENTMETHODCODE = 2 and CREDITCARDPAYMENTMETHODDETAIL.ID = REVENUEPAYMENTMETHOD.ID    
    left outer join
        dbo.DOCUMENTPRINTINGHISTORY on DOCUMENTPRINTINGHISTORY.TYPECODE = 6 and DOCUMENTPRINTINGHISTORY.CREDITPAYMENTID = CREDITPAYMENT.ID
    outer apply
        dbo.DOCUMENT
    where
        FT.ID = @CREDITID and
        FT.TYPECODE = 23 and  -- Refund

        DOCUMENT.TYPECODE = 6 and  -- Refund Credit Card Receipt

        DOCUMENTPRINTINGHISTORY.ID is null

    return
end