----------------------------------------------------------------
--                                                              
--  File:   TradeStatus.SQL                                     
--          Microsoft TPC-E Benchmark Kit Ver. 1.14.0-1031      
--          Copyright Microsoft, 2008-2015                  
--                                                              
----------------------------------------------------------------
USE tpce
GO
SET QUOTED_IDENTIFIER ON
GO

IF EXISTS(  SELECT  name
            FROM    sysobjects
            WHERE   name    = 'TradeStatusFrame1' AND
                    type    = 'P')
DROP PROCEDURE  TradeStatusFrame1
GO

-- TradeStatusFrame1 is responsible for retrieving 
-- the total assets of a customer including 
-- all cash and stock positions. 

CREATE PROCEDURE    TradeStatusFrame1
                        @acct_id     bigint     /* IN customer account ID */ 
AS
BEGIN
    SET TRANSACTION ISOLATION LEVEL SNAPSHOT

    BEGIN TRANSACTION
    
    SELECT  C_L_NAME, 
            C_F_NAME, 
            B_NAME 
    FROM    CUSTOMER_ACCOUNT, 
            CUSTOMER, 
            BROKER 
    WHERE   CA_ID   = @acct_id AND 
            C_ID    = CA_C_ID AND 
            B_ID    = CA_B_ID 

    SELECT  TOP 50
            T_CHRG,
            T_EXEC_NAME,
            EX_NAME,
            S_NAME,
            ST_NAME,
            T_S_SYMB,
            T_DTS,
            T_ID,
            T_QTY,
            TT_NAME 
    FROM    TRADE, 
            STATUS_TYPE, 
            TRADE_TYPE,
            SECURITY,
            EXCHANGE
    WHERE   T_CA_ID = @acct_id AND 
            ST_ID   = T_ST_ID AND 
            TT_ID   = T_TT_ID AND
            S_SYMB  = T_S_SYMB AND
            EX_ID   = S_EX_ID
    ORDER   BY T_DTS DESC 
    OPTION  (FORCE ORDER)
    
    SELECT  @@ROWCOUNT
    
    COMMIT TRANSACTION 
END
