USP_REPORT_DEFAULTREFUNDCREDITCARDRECEIPT

create procedure [dbo].[USP_REPORT_DEFAULTREFUNDCREDITCARDRECEIPT]
(
    @CREDITID uniqueidentifier,
    @CREDITPAYMENTID uniqueidentifier
)
as
    set nocount on;
    set transaction isolation level read uncommitted;

    select
        coalesce(CHILDSALESORDER.SEQUENCEID, SALESORDER.SEQUENCEID, '') ORDERNUMBER,             
        cast(cast(CREDITPAYMENT.CREDITPAYMENTDATEWITHTIMEOFFSET as datetime) as nvarchar) PAYMENTDATE,     
        dbo.UFN_CREDITTYPECODE_GETDESCRIPTION(CREDITCARDPAYMENTMETHODDETAIL.CREDITTYPECODEID) CARDTYPE,
        '************' + CREDITCARDPAYMENTMETHODDETAIL.CREDITCARDPARTIALNUMBER CARDNUMBER,
        dbo.UFN_DATE_EARLIESTFROMFUZZYDATE(CREDITCARDPAYMENTMETHODDETAIL.EXPIRESON)
        EXPIRATION,
        CREDITCARDPAYMENTMETHODDETAIL.AUTHORIZATIONCODE AUTHORIZATIONCODE,            
        CREDITCARDPAYMENTMETHODDETAIL.CARDHOLDERNAME,
        CREDITPAYMENT.AMOUNT PAYMENTTOTAL                                                    
    from
        dbo.CREDITPAYMENT
    inner join
        dbo.FINANCIALTRANSACTION as FT on FT.ID = CREDITPAYMENT.CREDITID
    inner join
        dbo.CREDIT_EXT as EXT on EXT.ID = 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.SALESORDER as CHILDSALESORDER on CHILDSALESORDER.REVENUEID = FT.PARENTID
    left outer join
        dbo.SALESORDER on SALESORDER.ID = EXT.SALESORDERID
    where
        CREDITPAYMENT.ID = @CREDITPAYMENTID;                    

    select
        coalesce(CHILDSALESORDER.SEQUENCEID, SALESORDER.SEQUENCEID, '') ORDERNUMBER,             
        cast(cast(CREDITPAYMENT.CREDITPAYMENTDATEWITHTIMEOFFSET as datetime) as nvarchar) PAYMENTDATE,     
        dbo.UFN_CREDITTYPECODE_GETDESCRIPTION(CREDITCARDPAYMENTMETHODDETAIL.CREDITTYPECODEID) CARDTYPE,
        '************' + CREDITCARDPAYMENTMETHODDETAIL.CREDITCARDPARTIALNUMBER CARDNUMBER,
        dbo.UFN_DATE_EARLIESTFROMFUZZYDATE(CREDITCARDPAYMENTMETHODDETAIL.EXPIRESON)
        EXPIRATION,
        CREDITCARDPAYMENTMETHODDETAIL.AUTHORIZATIONCODE AUTHORIZATIONCODE,            
        CREDITCARDPAYMENTMETHODDETAIL.CARDHOLDERNAME,
        CREDITPAYMENT.AMOUNT PAYMENTTOTAL                                
    from
        dbo.CREDITPAYMENT
    inner join
        dbo.FINANCIALTRANSACTION as FT on FT.ID = CREDITPAYMENT.CREDITID
    inner join
        dbo.CREDIT_EXT as EXT on EXT.ID = FT.ID
    inner join
        dbo.REVENUEPAYMENTMETHOD on REVENUEPAYMENTMETHOD.REVENUEID    = CREDITPAYMENT.REVENUEID
    inner join
        dbo.CREDITCARDPAYMENTMETHODDETAIL on REVENUEPAYMENTMETHOD.PAYMENTMETHODCODE = 2 and CREDITCARDPAYMENTMETHODDETAIL.ID = REVENUEPAYMENTMETHOD.ID
    left outer join
        dbo.SALESORDER as CHILDSALESORDER on CHILDSALESORDER.REVENUEID = FT.PARENTID
    left outer join
        dbo.SALESORDER on SALESORDER.ID = EXT.SALESORDERID
    where
        CREDITPAYMENT.ID = @CREDITPAYMENTID;

    return 0;