USP_GETBBPAYTRANSACTION_FORSPONSORSHIP

CREATE procedure dbo.USP_GETBBPAYTRANSACTION_FORSPONSORSHIP(@ID uniqueidentifier)
as
begin
    select
    top 1
        ID,
        TRANSACTIONID,
        TRANSACTIONAMOUNT,
        TRANSACTIONCONFIRMED,
        CARDCHARGED,
        PROCESSINGRESULTCODE
    from dbo.SALESORDERBBPAYTRANSACTION 
    -- only find records for sponsorship that are not confirmed.

    where SALESORDERID = (select top 1 SALESORDERITEM.SALESORDERID from dbo.SALESORDERITEM
                            inner join dbo.SALESORDERITEMSPONSORSHIP on SALESORDERITEMSPONSORSHIP.ID = SALESORDERITEM.ID
                            and SALESORDERITEM.SALESORDERID = @ID)
    and SALESORDERBBPAYTRANSACTION.TRANSACTIONCONFIRMED = 0
    -- looks like multiple sales order row can exist in this table if a sales order gets canceled mid-way.

    -- then items are re-added to the cart.  this leaves two rows for the salesorder payment record, when

    -- only one is valid.  

    and not exists (select 'x' from dbo.SALESORDERBBPAYTRANSACTION BBT 
                     where BBT.SALESORDERID = SALESORDERBBPAYTRANSACTION.SALESORDERID
                       and BBT.TRANSACTIONCONFIRMED = 1)
    order by SALESORDERBBPAYTRANSACTION.DATEADDED DESC -- the latest record of the sales order should be the record we want.


end