----------------------------------------------------------------
--                                                              
--  File:   CustomerPosition_ISO3.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    = 'CustomerPosition_Iso3' AND
                    type    = 'P')
DROP PROCEDURE  CustomerPosition_Iso3
GO

CREATE PROCEDURE    CustomerPosition_Iso3
                        @cust_id	bigint,	            /* IN/OUT customer ID */ 
                        @tax_id		varchar(20),     	/* IN tax ID */ 
                        @acct_id    bigint
AS
BEGIN
    SET TRANSACTION ISOLATION LEVEL READ COMMITTED

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

    --- ACID CODE BEGIN ---
    PRINT   'Begin Customer Position Frame 1 '
    SELECT  CONVERT(char(30), GETDATE(), 21)
    PRINT   ' '
    PRINT   'Variables used:'
    SELECT  @cust_id     AS 'Customer ID',
            @tax_id      AS 'Tax ID'
    PRINT   ' '
    --- ACID CODE END ---

    DECLARE @c_st_id    char(4),
            @c_l_name   varchar(25),
            @c_f_name   varchar(20),
            @c_m_name   char(1),
            @c_gndr     char(1),
            @c_tier     tinyint,
            @c_dob      date,
            @c_ad_id    bigint,
            @c_ctry_1   char(3),
            @c_area_1   char(3),
            @c_local_1  varchar(10),
            @c_ext_1    varchar(5),
            @c_ctry_2   char(3),
            @c_area_2   char(3),
            @c_local_2  varchar(10),
            @c_ext_2    varchar(5),
            @c_ctry_3   char(3),
            @c_area_3   char(3),
            @c_local_3  varchar(10),
            @c_ext_3    varchar(5),
            @c_email_1  varchar(50),
            @c_email_2  varchar(50)
	
    IF (@cust_id = 0)
        SELECT  @cust_id    = C_ID
        FROM    CUSTOMER
        WHERE   C_TAX_ID    = @tax_id

    SELECT  @c_st_id    = C_ST_ID,
            @c_l_name   = C_L_NAME,
            @c_f_name   = C_F_NAME,
            @c_m_name   = C_M_NAME,
            @c_gndr     = C_GNDR,
            @c_tier     = C_TIER,
            @c_dob      = C_DOB,
            @c_ad_id    = C_AD_ID,
            @c_ctry_1   = C_CTRY_1,
            @c_area_1   = C_AREA_1,
            @c_local_1  = C_LOCAL_1,
            @c_ext_1    = C_EXT_1,
            @c_ctry_2   = C_CTRY_2,
            @c_area_2   = C_AREA_2,
            @c_local_2  = C_LOCAL_2,
            @c_ext_2    = C_EXT_2,
            @c_ctry_3   = C_CTRY_3,
            @c_area_3   = C_AREA_3,
            @c_local_3  = C_LOCAL_3,
            @c_ext_3    = C_EXT_3,
            @c_email_1  = C_EMAIL_1,
            @c_email_2  = C_EMAIL_2
    FROM    CUSTOMER WITH (UPDLOCK)
    WHERE   C_ID        = @cust_id

	-- return as a rowset
    SELECT  @cust_id, @c_st_id, @c_l_name, @c_f_name,
            @c_m_name, @c_gndr, @c_tier, @c_dob,
            @c_ad_id, @c_ctry_1, @c_area_1, @c_local_1,
            @c_ext_1, @c_ctry_2, @c_area_2, @c_local_2,
            @c_ext_2, @c_ctry_3, @c_area_3, @c_local_3,
            @c_ext_3, @c_email_1, @c_email_2

    --- ACID CODE BEGIN ---
    PRINT   'Timestamp Before Calculation of CA_BAL '
    SELECT  CONVERT(char(30), GETDATE(), 21)
    PRINT   ' '
    --- ACID CODE END ---

    SELECT  CA_ID,
            CA_BAL,
            SUM(ISNULL(HS_QTY, 0) * ISNULL(LT_PRICE, 0))
    FROM    CUSTOMER_ACCOUNT WITH (UPDLOCK) LEFT LOOP JOIN
            HOLDING_SUMMARY ON CA_ID=HS_CA_ID LEFT LOOP JOIN
            LAST_TRADE ON HS_S_SYMB = LT_S_SYMB
    WHERE   CA_C_ID     = @cust_id 
    GROUP   BY CA_ID,
            CA_BAL
    ORDER   BY 3 ASC
    OPTION  (FORCE ORDER)

    --- ACID CODE BEGIN ---
    PRINT   'Timestamp After Calculation of CA_BAL '
    SELECT  CONVERT(char(30), GETDATE(), 21)
    PRINT   ' '
    PRINT   'End of Customer Position Frame 1 '
    SELECT  CONVERT(char(30), GETDATE(), 21)
    PRINT   ' '
    --- ACID CODE END ---

    -- CustomerPositionFrame2 is responsible for retrieving 
    -- the 30 most recent trade events for the specified account
    --- ACID CODE BEGIN ---
    PRINT   'Begin Customer Position Frame 2 '
    SELECT  CONVERT(char(30), GETDATE(), 21)
    PRINT   ' '
    PRINT   'Variables used:'
    SELECT  @acct_id     AS 'Customer Account ID'
    PRINT   ' '
    --- ACID CODE END ---

    SELECT  TOP 30
            TH_DTS,
            T_QTY,
            T_S_SYMB,
            T_ID,
            ST_NAME
    FROM    (SELECT TOP 10
                    T_ID AS ID 
             FROM   TRADE
             WHERE  T_CA_ID = @acct_id
             ORDER  BY T_DTS DESC) AS T
            JOIN TRADE ON T_ID=ID
            INNER LOOP JOIN TRADE_HISTORY ON TH_T_ID=T_ID,
            STATUS_TYPE
    WHERE   ST_ID   = TH_ST_ID
    ORDER   BY TH_DTS DESC
    OPTION  (FORCE ORDER) 

    --- ACID CODE BEGIN ---
    PRINT   'Timestamp before Commit in Customer Position Frame 2 '
    SELECT  CONVERT(char(30), GETDATE(), 21)
    PRINT   ' '
    --- ACID CODE END ---

    COMMIT TRANSACTION
    
    --- ACID CODE BEGIN ---
    PRINT   'Timestamp after Commit in Customer Position Frame 2 '
    SELECT  CONVERT(char(30), GETDATE(), 21)
    PRINT   ' '
    PRINT   'End of Customer Position Frame 2 '
    SELECT  CONVERT(char(30), GETDATE(), 21)
    PRINT   ' '
    --- ACID CODE END ---
END
GO
