USP_ORDERPAYMENT_GETTRANSACTION

Gets credit card transaction details for an order payment.
Parameters

Parameter	Parameter Type	Mode
@SALESORDERPAYMENTID	uniqueidentifier	IN
@SALESORDERID	uniqueidentifier	IN
@FILTERTRANSACTIONLESS	bit	IN
Definition

Copy


CREATE procedure dbo.USP_ORDERPAYMENT_GETTRANSACTION (
    @SALESORDERPAYMENTID uniqueidentifier = null,
    @SALESORDERID uniqueidentifier = null,
    @FILTERTRANSACTIONLESS bit = 1
)
as begin
    set nocount on;

    -- Not ideal, but this branch allows SQL Server

    -- to generate better queries without needing to recompile

    if @SALESORDERPAYMENTID is not null begin
        select
            [SALESORDERPAYMENT].[ID],
            FT.BASEAMOUNT,
            [CREDITCARDPAYMENTMETHODDETAIL].[TRANSACTIONID],
            [SALESORDER].[STATUSCODE] as [ORDERSTATUSCODE]
        from dbo.[SALESORDERPAYMENT]
        inner join dbo.[SALESORDER]
            on [SALESORDERPAYMENT].[SALESORDERID] = [SALESORDER].[ID]
        inner join dbo.FINANCIALTRANSACTION as FT
            on [SALESORDERPAYMENT].[PAYMENTID] = FT.ID
        inner join dbo.[REVENUEPAYMENTMETHOD]
            on FT.ID = [REVENUEPAYMENTMETHOD].[REVENUEID]
        left join dbo.[CREDITCARDPAYMENTMETHODDETAIL]
            on [REVENUEPAYMENTMETHOD].[ID] = [CREDITCARDPAYMENTMETHODDETAIL].[ID]
        where
            FT.DELETEDON is null
            and [SALESORDERPAYMENT].[ID] = @SALESORDERPAYMENTID
            and (
                @FILTERTRANSACTIONLESS = 0 or
                [CREDITCARDPAYMENTMETHODDETAIL].[TRANSACTIONID] is not null
            )
    end
    else if @SALESORDERID is not null begin
        select
            [SALESORDERPAYMENT].[ID],
            FT.BASEAMOUNT,
            [CREDITCARDPAYMENTMETHODDETAIL].[TRANSACTIONID],
            [SALESORDER].[STATUSCODE] as [ORDERSTATUSCODE]
        from dbo.[SALESORDERPAYMENT]
        inner join dbo.[SALESORDER]
            on [SALESORDERPAYMENT].[SALESORDERID] = [SALESORDER].[ID]
        inner join dbo.FINANCIALTRANSACTION as FT
            on [SALESORDERPAYMENT].[PAYMENTID] = FT.ID
        inner join dbo.[REVENUEPAYMENTMETHOD]
            on FT.ID = [REVENUEPAYMENTMETHOD].[REVENUEID]
        left join dbo.[CREDITCARDPAYMENTMETHODDETAIL]
            on [REVENUEPAYMENTMETHOD].[ID] = [CREDITCARDPAYMENTMETHODDETAIL].[ID]
        where
            FT.DELETEDON is null
            and [SALESORDER].[ID] = @SALESORDERID
            and (
                @FILTERTRANSACTIONLESS = 0 or
                [CREDITCARDPAYMENTMETHODDETAIL].[TRANSACTIONID] is not null
            )
    end

    return 0;
end