------------------------------------------------------------------
--                                                              --
--  File:   TradeResult.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    = 'TradeResultFrame1' AND
                    type    = 'P')
DROP PROCEDURE  TradeResultFrame1

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

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

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

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

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

-- Trade Result Frame1 is responsible for retrieving 
-- information on the customer and on the trade. 
CREATE PROCEDURE    TradeResultFrame1
                        @trade_id       bigint
AS
BEGIN
    SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRANSACTION

    DECLARE @inx    smallint

    SET     @inx    = @@SPID % 64
    
    IF @inx = 0 
		SELECT  T_CA_ID,T_CHRG,	ISNULL(HS_QTY,0),T_S_SYMB,T_IS_CASH,T_QTY,T_TT_ID,TT_IS_MRKT,TT_IS_SELL,TT_NAME,T_LIFO
		FROM    TRADE JOIN
				TRADE_TYPE WITH (INDEX( IX_TT_NC_index0)) ON T_TT_ID  = TT_ID LEFT JOIN
				HOLDING_SUMMARY WITH (UPDLOCK) ON HS_CA_ID = T_CA_ID AND HS_S_SYMB = T_S_SYMB
		WHERE   T_ID = @trade_id
    ELSE IF @inx = 1
		SELECT  T_CA_ID,T_CHRG,	ISNULL(HS_QTY,0),T_S_SYMB,T_IS_CASH,T_QTY,T_TT_ID,TT_IS_MRKT,TT_IS_SELL,TT_NAME,T_LIFO
		FROM    TRADE JOIN
				TRADE_TYPE WITH (INDEX( IX_TT_NC_index1)) ON T_TT_ID  = TT_ID LEFT JOIN
				HOLDING_SUMMARY WITH (UPDLOCK) ON HS_CA_ID = T_CA_ID AND HS_S_SYMB = T_S_SYMB
		WHERE   T_ID = @trade_id
    ELSE IF @inx = 2
		SELECT  T_CA_ID,T_CHRG,	ISNULL(HS_QTY,0),T_S_SYMB,T_IS_CASH,T_QTY,T_TT_ID,TT_IS_MRKT,TT_IS_SELL,TT_NAME,T_LIFO
		FROM    TRADE JOIN
				TRADE_TYPE WITH (INDEX( IX_TT_NC_index2)) ON T_TT_ID  = TT_ID LEFT JOIN
				HOLDING_SUMMARY WITH (UPDLOCK) ON HS_CA_ID = T_CA_ID AND HS_S_SYMB = T_S_SYMB
		WHERE   T_ID = @trade_id
    ELSE IF @inx = 3
		SELECT  T_CA_ID,T_CHRG,	ISNULL(HS_QTY,0),T_S_SYMB,T_IS_CASH,T_QTY,T_TT_ID,TT_IS_MRKT,TT_IS_SELL,TT_NAME,T_LIFO
		FROM    TRADE JOIN
				TRADE_TYPE WITH (INDEX( IX_TT_NC_index3)) ON T_TT_ID  = TT_ID LEFT JOIN
				HOLDING_SUMMARY WITH (UPDLOCK) ON HS_CA_ID = T_CA_ID AND HS_S_SYMB = T_S_SYMB
		WHERE   T_ID = @trade_id
    ELSE IF @inx = 4
		SELECT  T_CA_ID,T_CHRG,	ISNULL(HS_QTY,0),T_S_SYMB,T_IS_CASH,T_QTY,T_TT_ID,TT_IS_MRKT,TT_IS_SELL,TT_NAME,T_LIFO
		FROM    TRADE JOIN
				TRADE_TYPE WITH (INDEX( IX_TT_NC_index4)) ON T_TT_ID  = TT_ID LEFT JOIN
				HOLDING_SUMMARY WITH (UPDLOCK) ON HS_CA_ID = T_CA_ID AND HS_S_SYMB = T_S_SYMB
		WHERE   T_ID = @trade_id
    ELSE IF @inx = 5
		SELECT  T_CA_ID,T_CHRG,	ISNULL(HS_QTY,0),T_S_SYMB,T_IS_CASH,T_QTY,T_TT_ID,TT_IS_MRKT,TT_IS_SELL,TT_NAME,T_LIFO
		FROM    TRADE JOIN
				TRADE_TYPE WITH (INDEX( IX_TT_NC_index5)) ON T_TT_ID  = TT_ID LEFT JOIN
				HOLDING_SUMMARY WITH (UPDLOCK) ON HS_CA_ID = T_CA_ID AND HS_S_SYMB = T_S_SYMB
		WHERE   T_ID = @trade_id
    ELSE IF @inx = 6
		SELECT  T_CA_ID,T_CHRG,	ISNULL(HS_QTY,0),T_S_SYMB,T_IS_CASH,T_QTY,T_TT_ID,TT_IS_MRKT,TT_IS_SELL,TT_NAME,T_LIFO
		FROM    TRADE JOIN
				TRADE_TYPE WITH (INDEX( IX_TT_NC_index6)) ON T_TT_ID  = TT_ID LEFT JOIN
				HOLDING_SUMMARY WITH (UPDLOCK) ON HS_CA_ID = T_CA_ID AND HS_S_SYMB = T_S_SYMB
		WHERE   T_ID = @trade_id
    ELSE IF @inx = 7
		SELECT  T_CA_ID,T_CHRG,	ISNULL(HS_QTY,0),T_S_SYMB,T_IS_CASH,T_QTY,T_TT_ID,TT_IS_MRKT,TT_IS_SELL,TT_NAME,T_LIFO
		FROM    TRADE JOIN
				TRADE_TYPE WITH (INDEX( IX_TT_NC_index7)) ON T_TT_ID  = TT_ID LEFT JOIN
				HOLDING_SUMMARY WITH (UPDLOCK) ON HS_CA_ID = T_CA_ID AND HS_S_SYMB = T_S_SYMB
		WHERE   T_ID = @trade_id
    ELSE IF @inx = 8
		SELECT  T_CA_ID,T_CHRG,	ISNULL(HS_QTY,0),T_S_SYMB,T_IS_CASH,T_QTY,T_TT_ID,TT_IS_MRKT,TT_IS_SELL,TT_NAME,T_LIFO
		FROM    TRADE JOIN
				TRADE_TYPE WITH (INDEX( IX_TT_NC_index8)) ON T_TT_ID  = TT_ID LEFT JOIN
				HOLDING_SUMMARY WITH (UPDLOCK) ON HS_CA_ID = T_CA_ID AND HS_S_SYMB = T_S_SYMB
		WHERE   T_ID = @trade_id
    ELSE IF @inx = 9
		SELECT  T_CA_ID,T_CHRG,	ISNULL(HS_QTY,0),T_S_SYMB,T_IS_CASH,T_QTY,T_TT_ID,TT_IS_MRKT,TT_IS_SELL,TT_NAME,T_LIFO
		FROM    TRADE JOIN
				TRADE_TYPE WITH (INDEX( IX_TT_NC_index9)) ON T_TT_ID  = TT_ID LEFT JOIN
				HOLDING_SUMMARY WITH (UPDLOCK) ON HS_CA_ID = T_CA_ID AND HS_S_SYMB = T_S_SYMB
		WHERE   T_ID = @trade_id
    ELSE IF @inx = 10
		SELECT  T_CA_ID,T_CHRG,	ISNULL(HS_QTY,0),T_S_SYMB,T_IS_CASH,T_QTY,T_TT_ID,TT_IS_MRKT,TT_IS_SELL,TT_NAME,T_LIFO
		FROM    TRADE JOIN
				TRADE_TYPE WITH (INDEX( IX_TT_NC_index10)) ON T_TT_ID  = TT_ID LEFT JOIN
				HOLDING_SUMMARY WITH (UPDLOCK) ON HS_CA_ID = T_CA_ID AND HS_S_SYMB = T_S_SYMB
		WHERE   T_ID = @trade_id
    ELSE IF @inx = 11
		SELECT  T_CA_ID,T_CHRG,	ISNULL(HS_QTY,0),T_S_SYMB,T_IS_CASH,T_QTY,T_TT_ID,TT_IS_MRKT,TT_IS_SELL,TT_NAME,T_LIFO
		FROM    TRADE JOIN
				TRADE_TYPE WITH (INDEX( IX_TT_NC_index11)) ON T_TT_ID  = TT_ID LEFT JOIN
				HOLDING_SUMMARY WITH (UPDLOCK) ON HS_CA_ID = T_CA_ID AND HS_S_SYMB = T_S_SYMB
		WHERE   T_ID = @trade_id
    ELSE IF @inx = 12
		SELECT  T_CA_ID,T_CHRG,	ISNULL(HS_QTY,0),T_S_SYMB,T_IS_CASH,T_QTY,T_TT_ID,TT_IS_MRKT,TT_IS_SELL,TT_NAME,T_LIFO
		FROM    TRADE JOIN
				TRADE_TYPE WITH (INDEX( IX_TT_NC_index12)) ON T_TT_ID  = TT_ID LEFT JOIN
				HOLDING_SUMMARY WITH (UPDLOCK) ON HS_CA_ID = T_CA_ID AND HS_S_SYMB = T_S_SYMB
		WHERE   T_ID = @trade_id
    ELSE IF @inx = 13
		SELECT  T_CA_ID,T_CHRG,	ISNULL(HS_QTY,0),T_S_SYMB,T_IS_CASH,T_QTY,T_TT_ID,TT_IS_MRKT,TT_IS_SELL,TT_NAME,T_LIFO
		FROM    TRADE JOIN
				TRADE_TYPE WITH (INDEX( IX_TT_NC_index13)) ON T_TT_ID  = TT_ID LEFT JOIN
				HOLDING_SUMMARY WITH (UPDLOCK) ON HS_CA_ID = T_CA_ID AND HS_S_SYMB = T_S_SYMB
		WHERE   T_ID = @trade_id
    ELSE IF @inx = 14
		SELECT  T_CA_ID,T_CHRG,	ISNULL(HS_QTY,0),T_S_SYMB,T_IS_CASH,T_QTY,T_TT_ID,TT_IS_MRKT,TT_IS_SELL,TT_NAME,T_LIFO
		FROM    TRADE JOIN
				TRADE_TYPE WITH (INDEX( IX_TT_NC_index14)) ON T_TT_ID  = TT_ID LEFT JOIN
				HOLDING_SUMMARY WITH (UPDLOCK) ON HS_CA_ID = T_CA_ID AND HS_S_SYMB = T_S_SYMB
		WHERE   T_ID = @trade_id
    ELSE IF @inx = 15
		SELECT  T_CA_ID,T_CHRG,	ISNULL(HS_QTY,0),T_S_SYMB,T_IS_CASH,T_QTY,T_TT_ID,TT_IS_MRKT,TT_IS_SELL,TT_NAME,T_LIFO
		FROM    TRADE JOIN
				TRADE_TYPE WITH (INDEX( IX_TT_NC_index15)) ON T_TT_ID  = TT_ID LEFT JOIN
				HOLDING_SUMMARY WITH (UPDLOCK) ON HS_CA_ID = T_CA_ID AND HS_S_SYMB = T_S_SYMB
		WHERE   T_ID = @trade_id
    ELSE IF @inx = 16
		SELECT  T_CA_ID,T_CHRG,	ISNULL(HS_QTY,0),T_S_SYMB,T_IS_CASH,T_QTY,T_TT_ID,TT_IS_MRKT,TT_IS_SELL,TT_NAME,T_LIFO
		FROM    TRADE JOIN
				TRADE_TYPE WITH (INDEX( IX_TT_NC_index16)) ON T_TT_ID  = TT_ID LEFT JOIN
				HOLDING_SUMMARY WITH (UPDLOCK) ON HS_CA_ID = T_CA_ID AND HS_S_SYMB = T_S_SYMB
		WHERE   T_ID = @trade_id
    ELSE IF @inx = 17
		SELECT  T_CA_ID,T_CHRG,	ISNULL(HS_QTY,0),T_S_SYMB,T_IS_CASH,T_QTY,T_TT_ID,TT_IS_MRKT,TT_IS_SELL,TT_NAME,T_LIFO
		FROM    TRADE JOIN
				TRADE_TYPE WITH (INDEX( IX_TT_NC_index17)) ON T_TT_ID  = TT_ID LEFT JOIN
				HOLDING_SUMMARY WITH (UPDLOCK) ON HS_CA_ID = T_CA_ID AND HS_S_SYMB = T_S_SYMB
		WHERE   T_ID = @trade_id
    ELSE IF @inx = 18
		SELECT  T_CA_ID,T_CHRG,	ISNULL(HS_QTY,0),T_S_SYMB,T_IS_CASH,T_QTY,T_TT_ID,TT_IS_MRKT,TT_IS_SELL,TT_NAME,T_LIFO
		FROM    TRADE JOIN
				TRADE_TYPE WITH (INDEX( IX_TT_NC_index18)) ON T_TT_ID  = TT_ID LEFT JOIN
				HOLDING_SUMMARY WITH (UPDLOCK) ON HS_CA_ID = T_CA_ID AND HS_S_SYMB = T_S_SYMB
		WHERE   T_ID = @trade_id
    ELSE IF @inx = 19
		SELECT  T_CA_ID,T_CHRG,	ISNULL(HS_QTY,0),T_S_SYMB,T_IS_CASH,T_QTY,T_TT_ID,TT_IS_MRKT,TT_IS_SELL,TT_NAME,T_LIFO
		FROM    TRADE JOIN
				TRADE_TYPE WITH (INDEX( IX_TT_NC_index19)) ON T_TT_ID  = TT_ID LEFT JOIN
				HOLDING_SUMMARY WITH (UPDLOCK) ON HS_CA_ID = T_CA_ID AND HS_S_SYMB = T_S_SYMB
		WHERE   T_ID = @trade_id
    ELSE IF @inx = 20
		SELECT  T_CA_ID,T_CHRG,	ISNULL(HS_QTY,0),T_S_SYMB,T_IS_CASH,T_QTY,T_TT_ID,TT_IS_MRKT,TT_IS_SELL,TT_NAME,T_LIFO
		FROM    TRADE JOIN
				TRADE_TYPE WITH (INDEX( IX_TT_NC_index20)) ON T_TT_ID  = TT_ID LEFT JOIN
				HOLDING_SUMMARY WITH (UPDLOCK) ON HS_CA_ID = T_CA_ID AND HS_S_SYMB = T_S_SYMB
		WHERE   T_ID = @trade_id
    ELSE IF @inx = 21
		SELECT  T_CA_ID,T_CHRG,	ISNULL(HS_QTY,0),T_S_SYMB,T_IS_CASH,T_QTY,T_TT_ID,TT_IS_MRKT,TT_IS_SELL,TT_NAME,T_LIFO
		FROM    TRADE JOIN
				TRADE_TYPE WITH (INDEX( IX_TT_NC_index21)) ON T_TT_ID  = TT_ID LEFT JOIN
				HOLDING_SUMMARY WITH (UPDLOCK) ON HS_CA_ID = T_CA_ID AND HS_S_SYMB = T_S_SYMB
		WHERE   T_ID = @trade_id
    ELSE IF @inx = 22
		SELECT  T_CA_ID,T_CHRG,	ISNULL(HS_QTY,0),T_S_SYMB,T_IS_CASH,T_QTY,T_TT_ID,TT_IS_MRKT,TT_IS_SELL,TT_NAME,T_LIFO
		FROM    TRADE JOIN
				TRADE_TYPE WITH (INDEX( IX_TT_NC_index22)) ON T_TT_ID  = TT_ID LEFT JOIN
				HOLDING_SUMMARY WITH (UPDLOCK) ON HS_CA_ID = T_CA_ID AND HS_S_SYMB = T_S_SYMB
		WHERE   T_ID = @trade_id
    ELSE IF @inx = 23
		SELECT  T_CA_ID,T_CHRG,	ISNULL(HS_QTY,0),T_S_SYMB,T_IS_CASH,T_QTY,T_TT_ID,TT_IS_MRKT,TT_IS_SELL,TT_NAME,T_LIFO
		FROM    TRADE JOIN
				TRADE_TYPE WITH (INDEX( IX_TT_NC_index23)) ON T_TT_ID  = TT_ID LEFT JOIN
				HOLDING_SUMMARY WITH (UPDLOCK) ON HS_CA_ID = T_CA_ID AND HS_S_SYMB = T_S_SYMB
		WHERE   T_ID = @trade_id
    ELSE IF @inx = 24
		SELECT  T_CA_ID,T_CHRG,	ISNULL(HS_QTY,0),T_S_SYMB,T_IS_CASH,T_QTY,T_TT_ID,TT_IS_MRKT,TT_IS_SELL,TT_NAME,T_LIFO
		FROM    TRADE JOIN
				TRADE_TYPE WITH (INDEX( IX_TT_NC_index24)) ON T_TT_ID  = TT_ID LEFT JOIN
				HOLDING_SUMMARY WITH (UPDLOCK) ON HS_CA_ID = T_CA_ID AND HS_S_SYMB = T_S_SYMB
		WHERE   T_ID = @trade_id
    ELSE IF @inx = 25
		SELECT  T_CA_ID,T_CHRG,	ISNULL(HS_QTY,0),T_S_SYMB,T_IS_CASH,T_QTY,T_TT_ID,TT_IS_MRKT,TT_IS_SELL,TT_NAME,T_LIFO
		FROM    TRADE JOIN
				TRADE_TYPE WITH (INDEX( IX_TT_NC_index25)) ON T_TT_ID  = TT_ID LEFT JOIN
				HOLDING_SUMMARY WITH (UPDLOCK) ON HS_CA_ID = T_CA_ID AND HS_S_SYMB = T_S_SYMB
		WHERE   T_ID = @trade_id
    ELSE IF @inx = 26
		SELECT  T_CA_ID,T_CHRG,	ISNULL(HS_QTY,0),T_S_SYMB,T_IS_CASH,T_QTY,T_TT_ID,TT_IS_MRKT,TT_IS_SELL,TT_NAME,T_LIFO
		FROM    TRADE JOIN
				TRADE_TYPE WITH (INDEX( IX_TT_NC_index26)) ON T_TT_ID  = TT_ID LEFT JOIN
				HOLDING_SUMMARY WITH (UPDLOCK) ON HS_CA_ID = T_CA_ID AND HS_S_SYMB = T_S_SYMB
		WHERE   T_ID = @trade_id
    ELSE IF @inx = 27
		SELECT  T_CA_ID,T_CHRG,	ISNULL(HS_QTY,0),T_S_SYMB,T_IS_CASH,T_QTY,T_TT_ID,TT_IS_MRKT,TT_IS_SELL,TT_NAME,T_LIFO
		FROM    TRADE JOIN
				TRADE_TYPE WITH (INDEX( IX_TT_NC_index27)) ON T_TT_ID  = TT_ID LEFT JOIN
				HOLDING_SUMMARY WITH (UPDLOCK) ON HS_CA_ID = T_CA_ID AND HS_S_SYMB = T_S_SYMB
		WHERE   T_ID = @trade_id
    ELSE IF @inx = 28
		SELECT  T_CA_ID,T_CHRG,	ISNULL(HS_QTY,0),T_S_SYMB,T_IS_CASH,T_QTY,T_TT_ID,TT_IS_MRKT,TT_IS_SELL,TT_NAME,T_LIFO
		FROM    TRADE JOIN
				TRADE_TYPE WITH (INDEX( IX_TT_NC_index28)) ON T_TT_ID  = TT_ID LEFT JOIN
				HOLDING_SUMMARY WITH (UPDLOCK) ON HS_CA_ID = T_CA_ID AND HS_S_SYMB = T_S_SYMB
		WHERE   T_ID = @trade_id
    ELSE IF @inx = 29
		SELECT  T_CA_ID,T_CHRG,	ISNULL(HS_QTY,0),T_S_SYMB,T_IS_CASH,T_QTY,T_TT_ID,TT_IS_MRKT,TT_IS_SELL,TT_NAME,T_LIFO
		FROM    TRADE JOIN
				TRADE_TYPE WITH (INDEX( IX_TT_NC_index29)) ON T_TT_ID  = TT_ID LEFT JOIN
				HOLDING_SUMMARY WITH (UPDLOCK) ON HS_CA_ID = T_CA_ID AND HS_S_SYMB = T_S_SYMB
		WHERE   T_ID = @trade_id
    ELSE IF @inx = 30
		SELECT  T_CA_ID,T_CHRG,	ISNULL(HS_QTY,0),T_S_SYMB,T_IS_CASH,T_QTY,T_TT_ID,TT_IS_MRKT,TT_IS_SELL,TT_NAME,T_LIFO
		FROM    TRADE JOIN
				TRADE_TYPE WITH (INDEX( IX_TT_NC_index30)) ON T_TT_ID  = TT_ID LEFT JOIN
				HOLDING_SUMMARY WITH (UPDLOCK) ON HS_CA_ID = T_CA_ID AND HS_S_SYMB = T_S_SYMB
		WHERE   T_ID = @trade_id
    ELSE IF @inx = 31
		SELECT  T_CA_ID,T_CHRG,	ISNULL(HS_QTY,0),T_S_SYMB,T_IS_CASH,T_QTY,T_TT_ID,TT_IS_MRKT,TT_IS_SELL,TT_NAME,T_LIFO
		FROM    TRADE JOIN
				TRADE_TYPE WITH (INDEX( IX_TT_NC_index31)) ON T_TT_ID  = TT_ID LEFT JOIN
				HOLDING_SUMMARY WITH (UPDLOCK) ON HS_CA_ID = T_CA_ID AND HS_S_SYMB = T_S_SYMB
		WHERE   T_ID = @trade_id
    ELSE IF @inx = 32
		SELECT  T_CA_ID,T_CHRG,	ISNULL(HS_QTY,0),T_S_SYMB,T_IS_CASH,T_QTY,T_TT_ID,TT_IS_MRKT,TT_IS_SELL,TT_NAME,T_LIFO
		FROM    TRADE JOIN
				TRADE_TYPE WITH (INDEX( IX_TT_NC_index32)) ON T_TT_ID  = TT_ID LEFT JOIN
				HOLDING_SUMMARY WITH (UPDLOCK) ON HS_CA_ID = T_CA_ID AND HS_S_SYMB = T_S_SYMB
		WHERE   T_ID = @trade_id
    ELSE IF @inx = 33
		SELECT  T_CA_ID,T_CHRG,	ISNULL(HS_QTY,0),T_S_SYMB,T_IS_CASH,T_QTY,T_TT_ID,TT_IS_MRKT,TT_IS_SELL,TT_NAME,T_LIFO
		FROM    TRADE JOIN
				TRADE_TYPE WITH (INDEX( IX_TT_NC_index33)) ON T_TT_ID  = TT_ID LEFT JOIN
				HOLDING_SUMMARY WITH (UPDLOCK) ON HS_CA_ID = T_CA_ID AND HS_S_SYMB = T_S_SYMB
		WHERE   T_ID = @trade_id
    ELSE IF @inx = 34
		SELECT  T_CA_ID,T_CHRG,	ISNULL(HS_QTY,0),T_S_SYMB,T_IS_CASH,T_QTY,T_TT_ID,TT_IS_MRKT,TT_IS_SELL,TT_NAME,T_LIFO
		FROM    TRADE JOIN
				TRADE_TYPE WITH (INDEX( IX_TT_NC_index34)) ON T_TT_ID  = TT_ID LEFT JOIN
				HOLDING_SUMMARY WITH (UPDLOCK) ON HS_CA_ID = T_CA_ID AND HS_S_SYMB = T_S_SYMB
		WHERE   T_ID = @trade_id
    ELSE IF @inx = 35
		SELECT  T_CA_ID,T_CHRG,	ISNULL(HS_QTY,0),T_S_SYMB,T_IS_CASH,T_QTY,T_TT_ID,TT_IS_MRKT,TT_IS_SELL,TT_NAME,T_LIFO
		FROM    TRADE JOIN
				TRADE_TYPE WITH (INDEX( IX_TT_NC_index35)) ON T_TT_ID  = TT_ID LEFT JOIN
				HOLDING_SUMMARY WITH (UPDLOCK) ON HS_CA_ID = T_CA_ID AND HS_S_SYMB = T_S_SYMB
		WHERE   T_ID = @trade_id
    ELSE IF @inx = 36
		SELECT  T_CA_ID,T_CHRG,	ISNULL(HS_QTY,0),T_S_SYMB,T_IS_CASH,T_QTY,T_TT_ID,TT_IS_MRKT,TT_IS_SELL,TT_NAME,T_LIFO
		FROM    TRADE JOIN
				TRADE_TYPE WITH (INDEX( IX_TT_NC_index36)) ON T_TT_ID  = TT_ID LEFT JOIN
				HOLDING_SUMMARY WITH (UPDLOCK) ON HS_CA_ID = T_CA_ID AND HS_S_SYMB = T_S_SYMB
		WHERE   T_ID = @trade_id
    ELSE IF @inx = 37
		SELECT  T_CA_ID,T_CHRG,	ISNULL(HS_QTY,0),T_S_SYMB,T_IS_CASH,T_QTY,T_TT_ID,TT_IS_MRKT,TT_IS_SELL,TT_NAME,T_LIFO
		FROM    TRADE JOIN
				TRADE_TYPE WITH (INDEX( IX_TT_NC_index37)) ON T_TT_ID  = TT_ID LEFT JOIN
				HOLDING_SUMMARY WITH (UPDLOCK) ON HS_CA_ID = T_CA_ID AND HS_S_SYMB = T_S_SYMB
		WHERE   T_ID = @trade_id
    ELSE IF @inx = 38
		SELECT  T_CA_ID,T_CHRG,	ISNULL(HS_QTY,0),T_S_SYMB,T_IS_CASH,T_QTY,T_TT_ID,TT_IS_MRKT,TT_IS_SELL,TT_NAME,T_LIFO
		FROM    TRADE JOIN
				TRADE_TYPE WITH (INDEX( IX_TT_NC_index38)) ON T_TT_ID  = TT_ID LEFT JOIN
				HOLDING_SUMMARY WITH (UPDLOCK) ON HS_CA_ID = T_CA_ID AND HS_S_SYMB = T_S_SYMB
		WHERE   T_ID = @trade_id
    ELSE IF @inx = 39
		SELECT  T_CA_ID,T_CHRG,	ISNULL(HS_QTY,0),T_S_SYMB,T_IS_CASH,T_QTY,T_TT_ID,TT_IS_MRKT,TT_IS_SELL,TT_NAME,T_LIFO
		FROM    TRADE JOIN
				TRADE_TYPE WITH (INDEX( IX_TT_NC_index39)) ON T_TT_ID  = TT_ID LEFT JOIN
				HOLDING_SUMMARY WITH (UPDLOCK) ON HS_CA_ID = T_CA_ID AND HS_S_SYMB = T_S_SYMB
		WHERE   T_ID = @trade_id
    ELSE IF @inx = 40
		SELECT  T_CA_ID,T_CHRG,	ISNULL(HS_QTY,0),T_S_SYMB,T_IS_CASH,T_QTY,T_TT_ID,TT_IS_MRKT,TT_IS_SELL,TT_NAME,T_LIFO
		FROM    TRADE JOIN
				TRADE_TYPE WITH (INDEX( IX_TT_NC_index40)) ON T_TT_ID  = TT_ID LEFT JOIN
				HOLDING_SUMMARY WITH (UPDLOCK) ON HS_CA_ID = T_CA_ID AND HS_S_SYMB = T_S_SYMB
		WHERE   T_ID = @trade_id
    ELSE IF @inx = 41
		SELECT  T_CA_ID,T_CHRG,	ISNULL(HS_QTY,0),T_S_SYMB,T_IS_CASH,T_QTY,T_TT_ID,TT_IS_MRKT,TT_IS_SELL,TT_NAME,T_LIFO
		FROM    TRADE JOIN
				TRADE_TYPE WITH (INDEX( IX_TT_NC_index41)) ON T_TT_ID  = TT_ID LEFT JOIN
				HOLDING_SUMMARY WITH (UPDLOCK) ON HS_CA_ID = T_CA_ID AND HS_S_SYMB = T_S_SYMB
		WHERE   T_ID = @trade_id
    ELSE IF @inx = 42
		SELECT  T_CA_ID,T_CHRG,	ISNULL(HS_QTY,0),T_S_SYMB,T_IS_CASH,T_QTY,T_TT_ID,TT_IS_MRKT,TT_IS_SELL,TT_NAME,T_LIFO
		FROM    TRADE JOIN
				TRADE_TYPE WITH (INDEX( IX_TT_NC_index42)) ON T_TT_ID  = TT_ID LEFT JOIN
				HOLDING_SUMMARY WITH (UPDLOCK) ON HS_CA_ID = T_CA_ID AND HS_S_SYMB = T_S_SYMB
		WHERE   T_ID = @trade_id
    ELSE IF @inx = 43
		SELECT  T_CA_ID,T_CHRG,	ISNULL(HS_QTY,0),T_S_SYMB,T_IS_CASH,T_QTY,T_TT_ID,TT_IS_MRKT,TT_IS_SELL,TT_NAME,T_LIFO
		FROM    TRADE JOIN
				TRADE_TYPE WITH (INDEX( IX_TT_NC_index43)) ON T_TT_ID  = TT_ID LEFT JOIN
				HOLDING_SUMMARY WITH (UPDLOCK) ON HS_CA_ID = T_CA_ID AND HS_S_SYMB = T_S_SYMB
		WHERE   T_ID = @trade_id
    ELSE IF @inx = 44
		SELECT  T_CA_ID,T_CHRG,	ISNULL(HS_QTY,0),T_S_SYMB,T_IS_CASH,T_QTY,T_TT_ID,TT_IS_MRKT,TT_IS_SELL,TT_NAME,T_LIFO
		FROM    TRADE JOIN
				TRADE_TYPE WITH (INDEX( IX_TT_NC_index44)) ON T_TT_ID  = TT_ID LEFT JOIN
				HOLDING_SUMMARY WITH (UPDLOCK) ON HS_CA_ID = T_CA_ID AND HS_S_SYMB = T_S_SYMB
		WHERE   T_ID = @trade_id
    ELSE IF @inx = 45
		SELECT  T_CA_ID,T_CHRG,	ISNULL(HS_QTY,0),T_S_SYMB,T_IS_CASH,T_QTY,T_TT_ID,TT_IS_MRKT,TT_IS_SELL,TT_NAME,T_LIFO
		FROM    TRADE JOIN
				TRADE_TYPE WITH (INDEX( IX_TT_NC_index45)) ON T_TT_ID  = TT_ID LEFT JOIN
				HOLDING_SUMMARY WITH (UPDLOCK) ON HS_CA_ID = T_CA_ID AND HS_S_SYMB = T_S_SYMB
		WHERE   T_ID = @trade_id
    ELSE IF @inx = 46
		SELECT  T_CA_ID,T_CHRG,	ISNULL(HS_QTY,0),T_S_SYMB,T_IS_CASH,T_QTY,T_TT_ID,TT_IS_MRKT,TT_IS_SELL,TT_NAME,T_LIFO
		FROM    TRADE JOIN
				TRADE_TYPE WITH (INDEX( IX_TT_NC_index46)) ON T_TT_ID  = TT_ID LEFT JOIN
				HOLDING_SUMMARY WITH (UPDLOCK) ON HS_CA_ID = T_CA_ID AND HS_S_SYMB = T_S_SYMB
		WHERE   T_ID = @trade_id
    ELSE IF @inx = 47
		SELECT  T_CA_ID,T_CHRG,	ISNULL(HS_QTY,0),T_S_SYMB,T_IS_CASH,T_QTY,T_TT_ID,TT_IS_MRKT,TT_IS_SELL,TT_NAME,T_LIFO
		FROM    TRADE JOIN
				TRADE_TYPE WITH (INDEX( IX_TT_NC_index47)) ON T_TT_ID  = TT_ID LEFT JOIN
				HOLDING_SUMMARY WITH (UPDLOCK) ON HS_CA_ID = T_CA_ID AND HS_S_SYMB = T_S_SYMB
		WHERE   T_ID = @trade_id
    ELSE IF @inx = 48
		SELECT  T_CA_ID,T_CHRG,	ISNULL(HS_QTY,0),T_S_SYMB,T_IS_CASH,T_QTY,T_TT_ID,TT_IS_MRKT,TT_IS_SELL,TT_NAME,T_LIFO
		FROM    TRADE JOIN
				TRADE_TYPE WITH (INDEX( IX_TT_NC_index48)) ON T_TT_ID  = TT_ID LEFT JOIN
				HOLDING_SUMMARY WITH (UPDLOCK) ON HS_CA_ID = T_CA_ID AND HS_S_SYMB = T_S_SYMB
		WHERE   T_ID = @trade_id
    ELSE IF @inx = 49
		SELECT  T_CA_ID,T_CHRG,	ISNULL(HS_QTY,0),T_S_SYMB,T_IS_CASH,T_QTY,T_TT_ID,TT_IS_MRKT,TT_IS_SELL,TT_NAME,T_LIFO
		FROM    TRADE JOIN
				TRADE_TYPE WITH (INDEX( IX_TT_NC_index49)) ON T_TT_ID  = TT_ID LEFT JOIN
				HOLDING_SUMMARY WITH (UPDLOCK) ON HS_CA_ID = T_CA_ID AND HS_S_SYMB = T_S_SYMB
		WHERE   T_ID = @trade_id
    ELSE IF @inx = 50
		SELECT  T_CA_ID,T_CHRG,	ISNULL(HS_QTY,0),T_S_SYMB,T_IS_CASH,T_QTY,T_TT_ID,TT_IS_MRKT,TT_IS_SELL,TT_NAME,T_LIFO
		FROM    TRADE JOIN
				TRADE_TYPE WITH (INDEX( IX_TT_NC_index50)) ON T_TT_ID  = TT_ID LEFT JOIN
				HOLDING_SUMMARY WITH (UPDLOCK) ON HS_CA_ID = T_CA_ID AND HS_S_SYMB = T_S_SYMB
		WHERE   T_ID = @trade_id
    ELSE IF @inx = 51
		SELECT  T_CA_ID,T_CHRG,	ISNULL(HS_QTY,0),T_S_SYMB,T_IS_CASH,T_QTY,T_TT_ID,TT_IS_MRKT,TT_IS_SELL,TT_NAME,T_LIFO
		FROM    TRADE JOIN
				TRADE_TYPE WITH (INDEX( IX_TT_NC_index51)) ON T_TT_ID  = TT_ID LEFT JOIN
				HOLDING_SUMMARY WITH (UPDLOCK) ON HS_CA_ID = T_CA_ID AND HS_S_SYMB = T_S_SYMB
		WHERE   T_ID = @trade_id
    ELSE IF @inx = 52
		SELECT  T_CA_ID,T_CHRG,	ISNULL(HS_QTY,0),T_S_SYMB,T_IS_CASH,T_QTY,T_TT_ID,TT_IS_MRKT,TT_IS_SELL,TT_NAME,T_LIFO
		FROM    TRADE JOIN
				TRADE_TYPE WITH (INDEX( IX_TT_NC_index52)) ON T_TT_ID  = TT_ID LEFT JOIN
				HOLDING_SUMMARY WITH (UPDLOCK) ON HS_CA_ID = T_CA_ID AND HS_S_SYMB = T_S_SYMB
		WHERE   T_ID = @trade_id
    ELSE IF @inx = 53
		SELECT  T_CA_ID,T_CHRG,	ISNULL(HS_QTY,0),T_S_SYMB,T_IS_CASH,T_QTY,T_TT_ID,TT_IS_MRKT,TT_IS_SELL,TT_NAME,T_LIFO
		FROM    TRADE JOIN
				TRADE_TYPE WITH (INDEX( IX_TT_NC_index53)) ON T_TT_ID  = TT_ID LEFT JOIN
				HOLDING_SUMMARY WITH (UPDLOCK) ON HS_CA_ID = T_CA_ID AND HS_S_SYMB = T_S_SYMB
		WHERE   T_ID = @trade_id
    ELSE IF @inx = 54
		SELECT  T_CA_ID,T_CHRG,	ISNULL(HS_QTY,0),T_S_SYMB,T_IS_CASH,T_QTY,T_TT_ID,TT_IS_MRKT,TT_IS_SELL,TT_NAME,T_LIFO
		FROM    TRADE JOIN
				TRADE_TYPE WITH (INDEX( IX_TT_NC_index54)) ON T_TT_ID  = TT_ID LEFT JOIN
				HOLDING_SUMMARY WITH (UPDLOCK) ON HS_CA_ID = T_CA_ID AND HS_S_SYMB = T_S_SYMB
		WHERE   T_ID = @trade_id
    ELSE IF @inx = 55
		SELECT  T_CA_ID,T_CHRG,	ISNULL(HS_QTY,0),T_S_SYMB,T_IS_CASH,T_QTY,T_TT_ID,TT_IS_MRKT,TT_IS_SELL,TT_NAME,T_LIFO
		FROM    TRADE JOIN
				TRADE_TYPE WITH (INDEX( IX_TT_NC_index55)) ON T_TT_ID  = TT_ID LEFT JOIN
				HOLDING_SUMMARY WITH (UPDLOCK) ON HS_CA_ID = T_CA_ID AND HS_S_SYMB = T_S_SYMB
		WHERE   T_ID = @trade_id
    ELSE IF @inx = 56
		SELECT  T_CA_ID,T_CHRG,	ISNULL(HS_QTY,0),T_S_SYMB,T_IS_CASH,T_QTY,T_TT_ID,TT_IS_MRKT,TT_IS_SELL,TT_NAME,T_LIFO
		FROM    TRADE JOIN
				TRADE_TYPE WITH (INDEX( IX_TT_NC_index56)) ON T_TT_ID  = TT_ID LEFT JOIN
				HOLDING_SUMMARY WITH (UPDLOCK) ON HS_CA_ID = T_CA_ID AND HS_S_SYMB = T_S_SYMB
		WHERE   T_ID = @trade_id
    ELSE IF @inx = 57
		SELECT  T_CA_ID,T_CHRG,	ISNULL(HS_QTY,0),T_S_SYMB,T_IS_CASH,T_QTY,T_TT_ID,TT_IS_MRKT,TT_IS_SELL,TT_NAME,T_LIFO
		FROM    TRADE JOIN
				TRADE_TYPE WITH (INDEX( IX_TT_NC_index57)) ON T_TT_ID  = TT_ID LEFT JOIN
				HOLDING_SUMMARY WITH (UPDLOCK) ON HS_CA_ID = T_CA_ID AND HS_S_SYMB = T_S_SYMB
		WHERE   T_ID = @trade_id
    ELSE IF @inx = 58
		SELECT  T_CA_ID,T_CHRG,	ISNULL(HS_QTY,0),T_S_SYMB,T_IS_CASH,T_QTY,T_TT_ID,TT_IS_MRKT,TT_IS_SELL,TT_NAME,T_LIFO
		FROM    TRADE JOIN
				TRADE_TYPE WITH (INDEX( IX_TT_NC_index58)) ON T_TT_ID  = TT_ID LEFT JOIN
				HOLDING_SUMMARY WITH (UPDLOCK) ON HS_CA_ID = T_CA_ID AND HS_S_SYMB = T_S_SYMB
		WHERE   T_ID = @trade_id
    ELSE IF @inx = 59
		SELECT  T_CA_ID,T_CHRG,	ISNULL(HS_QTY,0),T_S_SYMB,T_IS_CASH,T_QTY,T_TT_ID,TT_IS_MRKT,TT_IS_SELL,TT_NAME,T_LIFO
		FROM    TRADE JOIN
				TRADE_TYPE WITH (INDEX( IX_TT_NC_index59)) ON T_TT_ID  = TT_ID LEFT JOIN
				HOLDING_SUMMARY WITH (UPDLOCK) ON HS_CA_ID = T_CA_ID AND HS_S_SYMB = T_S_SYMB
		WHERE   T_ID = @trade_id
    ELSE IF @inx = 60
		SELECT  T_CA_ID,T_CHRG,	ISNULL(HS_QTY,0),T_S_SYMB,T_IS_CASH,T_QTY,T_TT_ID,TT_IS_MRKT,TT_IS_SELL,TT_NAME,T_LIFO
		FROM    TRADE JOIN
				TRADE_TYPE WITH (INDEX( IX_TT_NC_index60)) ON T_TT_ID  = TT_ID LEFT JOIN
				HOLDING_SUMMARY WITH (UPDLOCK) ON HS_CA_ID = T_CA_ID AND HS_S_SYMB = T_S_SYMB
		WHERE   T_ID = @trade_id
    ELSE IF @inx = 61
		SELECT  T_CA_ID,T_CHRG,	ISNULL(HS_QTY,0),T_S_SYMB,T_IS_CASH,T_QTY,T_TT_ID,TT_IS_MRKT,TT_IS_SELL,TT_NAME,T_LIFO
		FROM    TRADE JOIN
				TRADE_TYPE WITH (INDEX( IX_TT_NC_index61)) ON T_TT_ID  = TT_ID LEFT JOIN
				HOLDING_SUMMARY WITH (UPDLOCK) ON HS_CA_ID = T_CA_ID AND HS_S_SYMB = T_S_SYMB
		WHERE   T_ID = @trade_id
    ELSE IF @inx = 62
		SELECT  T_CA_ID,T_CHRG,	ISNULL(HS_QTY,0),T_S_SYMB,T_IS_CASH,T_QTY,T_TT_ID,TT_IS_MRKT,TT_IS_SELL,TT_NAME,T_LIFO
		FROM    TRADE JOIN
				TRADE_TYPE WITH (INDEX( IX_TT_NC_index62)) ON T_TT_ID  = TT_ID LEFT JOIN
				HOLDING_SUMMARY WITH (UPDLOCK) ON HS_CA_ID = T_CA_ID AND HS_S_SYMB = T_S_SYMB
		WHERE   T_ID = @trade_id
    ELSE IF @inx = 63
		SELECT  T_CA_ID,T_CHRG,	ISNULL(HS_QTY,0),T_S_SYMB,T_IS_CASH,T_QTY,T_TT_ID,TT_IS_MRKT,TT_IS_SELL,TT_NAME,T_LIFO
		FROM    TRADE JOIN
				TRADE_TYPE WITH (INDEX( IX_TT_NC_index63)) ON T_TT_ID  = TT_ID LEFT JOIN
				HOLDING_SUMMARY WITH (UPDLOCK) ON HS_CA_ID = T_CA_ID AND HS_S_SYMB = T_S_SYMB
		WHERE   T_ID = @trade_id
    ELSE
		SELECT  T_CA_ID,T_CHRG,	ISNULL(HS_QTY,0),T_S_SYMB,T_IS_CASH,T_QTY,T_TT_ID,TT_IS_MRKT,TT_IS_SELL,TT_NAME,T_LIFO
		FROM    TRADE JOIN
				TRADE_TYPE WITH (INDEX(PK_TRADE_TYPE)) ON T_TT_ID  = TT_ID LEFT JOIN
				HOLDING_SUMMARY WITH (UPDLOCK) ON HS_CA_ID = T_CA_ID AND HS_S_SYMB = T_S_SYMB
		WHERE   T_ID = @trade_id
		
    SELECT  @@ROWCOUNT
		
END
GO

-- Trade Result Frame 2 is responsible for updating 
-- the customer's holdings to reflect the result 
-- of a buy or a sell trade. 
CREATE PROCEDURE    TradeResultFrame2
                        @trade_id       bigint,         /* IN trade ID */
                        @acct_id        bigint,         /* IN customer account ID */ 
                        @symbol         varchar(15),    /* IN security symbol */ 
                        @is_lifo        tinyint,        /* IN ordering of trade */ 
                        @trade_price    numeric(8,2),   /* IN trade price */ 
                        @trade_qty      numeric(6),     /* IN quantity traded */ 
                        @type_is_sell   tinyint,        /* IN boolean flag */ 
                        @hs_qty         numeric(6)      /* IN HOLDING_SUMMARY quantity */
AS
BEGIN
    DECLARE -- output parameters
            @buy_value      numeric(12,2),	/* OUT value of liquidated holdings */ 
            @sell_value     numeric(12,2),	/* OUT total value of the trade */ 
            -- local variables
            @needed_qty     numeric(6),
            @hold_id        bigint,
            @hold_qty       numeric(6),
            @hold_price     numeric(8,2),
            @trade_dts      datetime,
            @broker_id      bigint,
            @cust_id        bigint,
            @acct_tax_st    tinyint

    -- Initialize variables
    SELECT  @buy_value  = 0,
            @sell_value = 0,
            @needed_qty = @trade_qty,
            @trade_dts  = GETDATE(),
            @broker_id  = CA_B_ID,
            @cust_id    = CA_C_ID,
            @acct_tax_st= CA_TAX_ST
    FROM    CUSTOMER_ACCOUNT with (UPDLOCK)
    WHERE   CA_ID       = @acct_id

    IF (@type_is_sell <> 0)
    BEGIN
        IF (@hs_qty = 0)
            INSERT INTO HOLDING_SUMMARY (HS_CA_ID, HS_S_SYMB, HS_QTY)
                        VALUES          (@acct_id, @symbol, (-@trade_qty))
        ELSE
        IF (@hs_qty <> @trade_qty)
            UPDATE  HOLDING_SUMMARY
            SET     HS_QTY      = @hs_qty - @trade_qty
            WHERE   HS_CA_ID    = @acct_id AND
                    HS_S_SYMB   = @symbol

        IF (@hs_qty > 0)
        BEGIN    -- only look at HOLDING if positive
    		WHILE (@needed_qty > 0)
			BEGIN		
				SET @hold_id = -1
				
				IF (@is_lifo <> 0)
					SELECT  TOP 1
                            @hold_id    = H_T_ID,
                            @hold_qty   = H_QTY, 
                            @hold_price = H_PRICE
					FROM    HOLDING WITH (UPDLOCK)  
					WHERE   H_CA_ID     = @acct_id AND
                            H_S_SYMB    = @symbol
					ORDER   BY H_DTS DESC
				ELSE
					SELECT  TOP 1
                            @hold_id    = H_T_ID,
                            @hold_qty   = H_QTY, 
                            @hold_price = H_PRICE
					FROM    HOLDING WITH (UPDLOCK)  
					WHERE   H_CA_ID     = @acct_id AND
                            H_S_SYMB    = @symbol
					ORDER   BY H_DTS ASC

				IF (@hold_id >= 0)
				BEGIN
					IF (@hold_qty > @needed_qty)
					BEGIN

						INSERT INTO HOLDING_HISTORY (HH_H_T_ID, HH_T_ID, HH_BEFORE_QTY, HH_AFTER_QTY)
								VALUES (@hold_id, @trade_id, @hold_qty,  (@hold_qty - @needed_qty))
						UPDATE  HOLDING
						SET     H_QTY   = @hold_qty - @needed_qty
						WHERE   H_T_ID = @hold_id

						SELECT  @buy_value  = @buy_value + (@needed_qty * @hold_price),
                                @sell_value = @sell_value + (@needed_qty * @trade_price),
                                @needed_qty = 0
						BREAK
					END
					ELSE
					BEGIN
						INSERT INTO HOLDING_HISTORY (HH_H_T_ID, HH_T_ID, HH_BEFORE_QTY, HH_AFTER_QTY)
							VALUES (@hold_id, @trade_id, @hold_qty, 0)

						DELETE  FROM HOLDING	
						WHERE   H_T_ID  = @hold_id
		
						SELECT  @buy_value  = @buy_value + (@hold_qty * @hold_price),
                                @sell_value = @sell_value + (@hold_qty * @trade_price),
                                @needed_qty = @needed_qty - @hold_qty
					END

				END
				ELSE
					BREAK
			END
	END
    
        IF (@needed_qty > 0)
        BEGIN  -- Holdings were negative or empty so create a new negative (short) position
            INSERT INTO HOLDING_HISTORY (HH_H_T_ID, HH_T_ID, HH_BEFORE_QTY, HH_AFTER_QTY)
                        VALUES          (@trade_id, @trade_id, 0, (-@needed_qty))
			
            INSERT INTO HOLDING (H_T_ID, H_CA_ID, H_S_SYMB, H_DTS, H_PRICE, H_QTY) 
                        VALUES  (@trade_id, @acct_id, @symbol, @trade_dts, @trade_price, (-@needed_qty))
		END
        ELSE
        IF (@hs_qty = @trade_qty)
            DELETE  FROM HOLDING_SUMMARY
            WHERE   HS_CA_ID    = @acct_id AND
                    HS_S_SYMB   = @symbol
    END     -- Selling
    ELSE    -- Buying
    BEGIN
        IF (@hs_qty = 0)
            INSERT INTO HOLDING_SUMMARY (HS_CA_ID, HS_S_SYMB, HS_QTY)
                        VALUES          (@acct_id, @symbol, @trade_qty)
        ELSE
            IF (-@hs_qty <> @trade_qty)
                UPDATE  HOLDING_SUMMARY
                SET     HS_QTY      = @hs_qty + @trade_qty
                WHERE   HS_CA_ID    = @acct_id AND
                        HS_S_SYMB   = @symbol

        IF (@hs_qty < 0)
        BEGIN    -- only look at HOLDING if negative
			WHILE (@needed_qty > 0)
			BEGIN		
				SET @hold_id = -1
				IF (@is_lifo <> 0)
					SELECT  TOP 1
                            @hold_id    = H_T_ID,
                            @hold_qty   = H_QTY,
                            @hold_price = H_PRICE
					FROM    HOLDING WITH (UPDLOCK)  
					WHERE   H_CA_ID     = @acct_id AND
                            H_S_SYMB    = @symbol
					ORDER   BY H_DTS DESC
				ELSE
					SELECT  TOP 1
                            @hold_id    = H_T_ID,
                            @hold_qty   = H_QTY,
                            @hold_price = H_PRICE
					FROM    HOLDING WITH (UPDLOCK)  
					WHERE   H_CA_ID     = @acct_id AND
                            H_S_SYMB    = @symbol
					ORDER   BY H_DTS ASC

				IF (@hold_id >= 0)
				BEGIN
					IF ((-1 * @hold_qty) > @needed_qty)	-- H_QTY is negative here, while @needed_qty is positive
					BEGIN
						INSERT INTO HOLDING_HISTORY (HH_H_T_ID, HH_T_ID, HH_BEFORE_QTY, HH_AFTER_QTY)
							                 VALUES (@hold_id, @trade_id, @hold_qty,  (@hold_qty + @needed_qty))

						UPDATE  HOLDING
						SET     H_QTY   = @hold_qty + @needed_qty
						WHERE   H_T_ID  = @hold_id

						SELECT  @sell_value = @sell_value + (@needed_qty * @hold_price),
                                @buy_value  = @buy_value + (@needed_qty * @trade_price),
                                @needed_qty = 0
						BREAK
					END
					ELSE
					BEGIN
                        INSERT INTO HOLDING_HISTORY (HH_H_T_ID, HH_T_ID, HH_BEFORE_QTY, HH_AFTER_QTY)
                      						 VALUES (@hold_id, @trade_id, @hold_qty, 0)

                        DELETE  FROM HOLDING	
                        WHERE   H_T_ID  = @hold_id	
	
                        SET     @hold_qty   = @hold_qty * -1

                        SELECT  @sell_value = @sell_value + (@hold_qty * @hold_price),
				                        @buy_value  = @buy_value + (@hold_qty * @trade_price),
				                        @needed_qty = @needed_qty - @hold_qty
					END
				END
				ELSE
					BREAK

			END
        END

        IF (@needed_qty >0)
        BEGIN  -- Holdings were positive or empty so create a new positive (long) position
             INSERT INTO HOLDING_HISTORY (HH_H_T_ID, HH_T_ID, HH_BEFORE_QTY, HH_AFTER_QTY)
                         VALUES          (@trade_id, @trade_id, 0, @needed_qty)

             INSERT INTO HOLDING (H_T_ID, H_CA_ID, H_S_SYMB, H_DTS, H_PRICE, H_QTY)
                         VALUES  (@trade_id, @acct_id, @symbol, @trade_dts, @trade_price, @needed_qty)
         END
        ELSE
        IF (-@hs_qty = @trade_qty)
            DELETE  FROM HOLDING_SUMMARY
            WHERE   HS_CA_ID    = @acct_id AND
                    HS_S_SYMB   = @symbol
    END    
	
    -- Return data to the client
    SELECT @buy_value, 
           @sell_value, 
           @trade_dts,
           @broker_id,
           @cust_id,
           @acct_tax_st

END 
GO

-- Trade Result Frame3 is responsible for computing 
-- the amount of tax due by the customer as 
-- a result of the trade and recording it. 
CREATE PROCEDURE    TradeResultFrame3
                        @trade_id       bigint,         /* IN trade ID */                        
                        @cust_id        bigint,         /* IN customer ID */ 
                        @buy_value      numeric(10,2),  /* IN value of liquidated holdings*/ 
                        @sell_value     numeric(10,2)   /* IN purchase price */ 
AS
BEGIN
    DECLARE -- output parameter
            @tax_amount     numeric(10,2),  /* OUT tax amount */
            -- local variables
            @tax_rates      numeric(6,5),
            @capital_gain   numeric(10,2)

    SELECT  @tax_rates  = sum(TX_RATE) 
    FROM    TAXRATE 
    WHERE   TX_ID IN (  SELECT  TOP 2
                                CX_TX_ID
                        FROM    CUSTOMER_TAXRATE
                        WHERE   CX_C_ID = @cust_id)

    SET @capital_gain   = @sell_value - @buy_value 

    IF (@capital_gain > 0)
        SET @tax_amount = @capital_gain * @tax_rates 
    ELSE
        SET @tax_amount	= 0

    UPDATE  TRADE 
    SET     T_TAX   = @tax_amount 
    WHERE   T_ID    = @trade_id 
    
    SELECT	@tax_amount
END
GO

-- Trade Result Frame4 is responsible for computing 
-- the commission for the broker who executed 
-- the trade. 
CREATE PROCEDURE    TradeResultFrame4
                        @cust_id        bigint,         /* IN customer ID */
                        @symbol         varchar(15),    /* IN security symbol */
                        @trade_qty      numeric(6),     /* IN trade quantity */
                        @type_id        char(3)         /* IN type ID of trade */	
AS
BEGIN
    DECLARE -- output parameters
            @comm_rate      numeric(10,2),  /* OUT commission rate */
            @s_name         char(70),       /* OUT security name */	
            -- local variables
            @s_ex_id        char(6),
            @c_tier         tinyint

    SELECT  @s_ex_id    = S_EX_ID, 
            @s_name     = S_NAME 
    FROM    SECURITY 
    WHERE   S_SYMB      = @symbol 

    SELECT  @c_tier = C_TIER 
    FROM    CUSTOMER 
    WHERE   C_ID    = @cust_id 

    SELECT  TOP 1
            @comm_rate  = CR_RATE 
    FROM    COMMISSION_RATE 
    WHERE   CR_C_TIER   = @c_tier AND
            CR_TT_ID    = @type_id AND
            CR_EX_ID    = @s_ex_id AND 
            CR_FROM_QTY <= @trade_qty 
    ORDER   BY CR_FROM_QTY DESC

    SELECT  @comm_rate,
            @s_name	
END
GO

-- Trade Result Frame5 is responsible for retrieving 
-- information on the customer and on the trade. 
CREATE PROCEDURE    TradeResultFrame5
                        @broker_id          bigint,         /* IN brokerID */ 
                        @comm_amount        numeric(10,2),  /* IN commission amount */ 
                        @trade_dts          datetime,       /* IN trade date and time */ 
                        @trade_id           bigint,         /* IN trade ID */ 
                        @trade_price        numeric(8,2),   /* IN trade price */ 
                        @st_completed_id    char(4)         /* IN index ID value for completed status */
AS
BEGIN

    UPDATE  TRADE 
    SET     T_COMM          = @comm_amount, 
            T_DTS           = @trade_dts, 
            T_ST_ID         = @st_completed_id, 
            T_TRADE_PRICE   = @trade_price 
    WHERE   T_ID            = @trade_id 

    INSERT INTO TRADE_HISTORY   (TH_T_ID, TH_DTS, TH_ST_ID) 
                VALUES          (@trade_id, @trade_dts, @st_completed_id) 

    UPDATE  BROKER 
    SET     B_COMM_TOTAL    = B_COMM_TOTAL + @comm_amount, 
            B_NUM_TRADES    = B_NUM_TRADES + 1 
    WHERE   B_ID            = @broker_id
END
GO

-- Trade Result Frame6 is responsible for settling 
-- the trade
CREATE PROCEDURE    TradeResultFrame6
                        @acct_id        bigint,         /* IN account ID */ 
                        @due_date       date,           /* IN due date */ 
                        @s_name         varchar(70),    /* IN security name */ 
                        @se_amount      numeric(10,2),  /* IN settlement amount */ 
                        @trade_dts      datetime,       /* IN trade date */ 
                        @trade_id       bigint,         /* IN trade ID */ 
                        @trade_is_cash  tinyint,        /* IN boolean flag */ 
                        @trade_qty      numeric(6),     /* IN quantity traded */ 
                        @type_name      char(12)        /* IN type of trade */ 
AS
BEGIN
    DECLARE @cash_balance       numeric(12,2),
            @new_cash_balance   numeric(12,2),
            @cash_type          varchar(40)

    IF (@trade_is_cash <> 0)
        SET @cash_type  = 'Cash Account'
    ELSE
        SET @cash_type  = 'Margin'

    INSERT INTO SETTLEMENT  (SE_T_ID, SE_CASH_TYPE,
                             SE_CASH_DUE_DATE, SE_AMT) 
                VALUES      (@trade_id, @cash_type,
                             @due_date, @se_amount) 

    IF (@trade_is_cash <> 0)
        BEGIN
            UPDATE  CUSTOMER_ACCOUNT 
            SET     CA_BAL  = CA_BAL + @se_amount
            WHERE   CA_ID   = @acct_id 

            INSERT INTO CASH_TRANSACTION    (CT_T_ID, CT_DTS,
                                             CT_AMT, CT_NAME)
                        VALUES              (@trade_id, @trade_dts,
                                             @se_amount, 
                                             rtrim(@type_name) + ' ' + rtrim(CAST(@trade_qty AS char(7))) + ' shares of ' + rtrim(@s_name))
        END
        
    -- return the new account balance
    SELECT	CA_BAL
    FROM    CUSTOMER_ACCOUNT
    WHERE   CA_ID = @acct_id

    COMMIT TRANSACTION
END
