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


SET QUOTED_IDENTIFIER ON
GO

SET NOCOUNT ON
GO

PRINT   ' '
PRINT   'TPC-E Duplicates Test Begin'
SELECT  CONVERT(CHAR(30), GETDATE(), 21)

DECLARE @ap_ca_id   bigint,
        @ap_tax_id  char(20),
        @c_id       bigint,
        @cx_tx_id   char(4),
        @t_id       bigint,
        @hh_h_t_id  bigint,
        @hh_t_id    bigint,
        @symbol     char(15),
        @wi_wl_id   bigint,
        @wl_id      bigint,
        @b_id       bigint,
        @st_id      char(4),
        @co_id      bigint,
        @ad_id      bigint,
        @comp_co_id bigint,
        @in_id      char(2),
        @dm_date    datetime,
        @sc_id      char(2),
        @ni_id      bigint

--- setup variables for ACCOUNT_PERMISSION duplicate test
SELECT  @ap_ca_id   = MAX(AP_CA_ID)
FROM    ACCOUNT_PERMISSION

SELECT  @ap_tax_id  = AP_TAX_ID
FROM    ACCOUNT_PERMISSION
WHERE   AP_CA_ID    = @ap_ca_id

PRINT   ' '
PRINT   'Insert duplicate into ACCOUNT_PERMISSION'
PRINT   '----------------------------------------'
INSERT INTO ACCOUNT_PERMISSION VALUES(@ap_ca_id, '1111', @ap_tax_id, 'AUDIT DUPLICATE', 'TEST ROW')

--- setup variables for CUSTOMER duplicate test
SELECT  @c_id   = MAX(C_ID)
FROM    CUSTOMER

PRINT   ' '
PRINT   'Insert duplicate into CUSTOMER'
PRINT   '----------------------------------------'
INSERT INTO CUSTOMER VALUES(@c_id, @ap_tax_id, 'XXXX','AUDIT DUPLICATE','TEST ROW','X','M',1,'11-08-1961',0,'999','999','9999999999','99999','999','999','9999999999','99999','999','999','9999999999','99999','WooHoo@WooHoo.com','WhyAmIHere@Universe.com')

PRINT   ' '
PRINT   'Insert duplicate into CUSTOMER_ACCOUNT'
PRINT   '----------------------------------------'
INSERT INTO CUSTOMER_ACCOUNT VALUES(@ap_ca_id, 4300000001, @c_id,'AUDIT DUPLICATE TEST ROW',0,0)

SELECT  @cx_tx_id   = CX_TX_ID
FROM    CUSTOMER_TAXRATE
WHERE   CX_C_ID     = @c_id

PRINT   ' '
PRINT   'Insert duplicate into CUSTOMER_TAXRATE'
PRINT   '-----------------------------------------------------------'
INSERT INTO CUSTOMER_TAXRATE (CX_C_ID, CX_TX_ID) VALUES(@c_id, @cx_tx_id)

SELECT  @t_id   = MAX(H_T_ID)
FROM    HOLDING

PRINT   ' '
PRINT   'Insert duplicate into HOLDING'
PRINT   '----------------------------------------'
INSERT INTO HOLDING VALUES(@t_id, @ap_ca_id, 'XXXXX', GETDATE(), 99.99, 1)

SELECT  @hh_h_t_id  = MAX(HH_H_T_ID)
FROM    HOLDING_HISTORY

SELECT  @hh_t_id    = HH_T_ID
FROM    HOLDING_HISTORY
WHERE   HH_H_T_ID    = @hh_h_t_id

PRINT   ' '
PRINT   'Insert duplicate into HOLDING_HISTORY'
PRINT   '----------------------------------------'
INSERT INTO HOLDING_HISTORY VALUES(@hh_h_t_id, @hh_t_id, 999,999)

SELECT  @symbol     = HS_S_SYMB
FROM    HOLDING_SUMMARY
WHERE   HS_CA_ID    = @ap_ca_id

PRINT   ' '
PRINT   'Insert duplicate into HOLDING_SUMMARY'
PRINT   '----------------------------------------'
INSERT INTO HOLDING_SUMMARY VALUES(@ap_ca_id, @symbol, 999)

SELECT  @wl_id  = MAX(WL_ID)
FROM    WATCH_LIST

PRINT   ' '
PRINT   'Insert duplicate into WATCH_LIST'
PRINT   '----------------------------------------'
INSERT INTO WATCH_LIST VALUES(@wl_id, @c_id)

SELECT  TOP 1
        @symbol     = WI_S_SYMB
FROM    WATCH_ITEM
WHERE   WI_WL_ID    = @wl_id

PRINT   ' '
PRINT   'Insert duplicate into WATCH_ITEM'
PRINT   '----------------------------------------'
INSERT INTO WATCH_ITEM VALUES(@wl_id, @symbol)

SELECT  @b_id   = MAX(B_ID)
FROM    BROKER

PRINT   ' '
PRINT   'Insert duplicate into BROKER'
PRINT   '----------------------------------------'
INSERT INTO BROKER VALUES(@b_id, 'ACTV', 'AUDIT DUPLICATE TEST ROW', 999, 999.99)

SELECT  @t_id   = MAX(CT_T_ID)
FROM    CASH_TRANSACTION

PRINT   ' '
PRINT   'Insert duplicate into CASH_TRANSACTION'
PRINT   '----------------------------------------'
INSERT INTO CASH_TRANSACTION VALUES(@t_id, GETDATE(), 99.99, 'AUDIT DUPLICATE TEST ROW')

PRINT   ' '
PRINT   'Insert duplicate into CHARGE'
PRINT   '----------------------------------------'
INSERT INTO CHARGE VALUES('TSL', 3, 9.99)

PRINT   ' '
PRINT   'Insert duplicate into COMMISSION_RATE'
PRINT   '----------------------------------------'
INSERT INTO COMMISSION_RATE VALUES(1, 'TSL', 'AMEX', 201, 500, 0.99)

SELECT  @t_id   = MAX(SE_T_ID)
FROM    SETTLEMENT

PRINT   ' '
PRINT   'Insert duplicate into SETTLEMENT'
PRINT   '----------------------------------------'
INSERT INTO SETTLEMENT VALUES(@t_id, 'DUPLICATE TEST', GETDATE(), 99.99)

SELECT  @t_id   = MAX(T_ID)
FROM    TRADE

PRINT   ' '
PRINT   'Insert duplicate into TRADE'
PRINT   '----------------------------------------'
INSERT INTO TRADE VALUES(@t_id, GETDATE(), 'CMPT', 'TSL', 0, 'MSFT', 99, 99.99, @ap_ca_id, 'AUDIT DUPLICATE TEST', 99.99, 9.99, 9.99, 9.99, 0)

SELECT  @t_id   = MAX(TH_T_ID)
FROM    TRADE_HISTORY

SELECT  @st_id  = TH_ST_ID
FROM    TRADE_HISTORY
WHERE   TH_T_ID = @t_id

PRINT   ' '
PRINT   'Insert duplicate into TRADE_HISTORY'
PRINT   '----------------------------------------'
INSERT INTO TRADE_HISTORY VALUES(@t_id, GETDATE(), @st_id)

SELECT  @symbol = T_S_SYMB
FROM    TRADE
WHERE   T_ID    = @t_id

PRINT   ' '
PRINT   'Insert seed row into TRADE_REQUEST'
PRINT   '----------------------------------------'
INSERT INTO TRADE_REQUEST VALUES(@t_id, 'TSL', @symbol, 99, 99.99, @b_id)

PRINT   ' '
PRINT   'Insert duplicate into TRADE_REQUEST'
PRINT   '----------------------------------------'
INSERT INTO TRADE_REQUEST VALUES(@t_id, 'TSL', @symbol, 99, 99.99, @b_id)

PRINT   ' '
PRINT   'Delete seed row from TRADE_REQUEST'
PRINT   '----------------------------------------'
DELETE
FROM    TRADE_REQUEST
WHERE   TR_T_ID = @t_id

PRINT   ' '
PRINT   'Insert duplicate into TRADE_TYPE'
PRINT   '----------------------------------------'
INSERT INTO TRADE_TYPE VALUES('TSL', 'DUPLICATE', 1,1)

SELECT  @co_id  = MAX(CO_ID)
FROM    COMPANY

SELECT  @ad_id  = MAX(AD_ID)
FROM    ADDRESS

PRINT   ' '
PRINT   'Insert duplicate into COMPANY'
PRINT   '----------------------------------------'
INSERT INTO COMPANY VALUES(@co_id, 'ACTV', 'AUDIT DUPLICATE TEST ROW', 'AA', 'XXXX', 'AUDIT DUPLICATE TEST ROW', @ad_id, 'AUDIT DUPLICATE ROW TEST', GETDATE())

SELECT  @comp_co_id = CP_COMP_CO_ID,
        @in_id      = CP_IN_ID
FROM    COMPANY_COMPETITOR
WHERE   CP_CO_ID    = @co_id

PRINT   ' '
PRINT   'Insert duplicate into COMPANY_COMPETITOR'
PRINT   '----------------------------------------'
INSERT INTO COMPANY_COMPETITOR VALUES(@co_id, @comp_co_id, @in_id)

SELECT  @dm_date    = MAX(DM_DATE)
FROM    DAILY_MARKET
WHERE   DM_S_SYMB   LIKE 'AA%'

PRINT   ' '
PRINT   'Insert duplicate into DAILY_MARKET'
PRINT   '----------------------------------------'
INSERT INTO DAILY_MARKET VALUES(@dm_date, 'AA', 99.99, 99.99, 99.99, 999)

PRINT   ' '
PRINT   'Insert duplicate into EXCHANGE'
PRINT   '----------------------------------------'
INSERT INTO EXCHANGE VALUES('AMEX', 'AUDIT DUPLICATE TEST ROW', 999, 930, 1600, 'AUDIT DUPLICATE TEST ROW', 4300000003)

PRINT   ' '
PRINT   'Insert duplicate into FINANCIAL'
PRINT   '----------------------------------------'
INSERT INTO FINANCIAL VALUES(@co_id, 2000, 1, GETDATE(), 99.99, 99.99, 99.99, 9.99, 9.99, 0.99, 99.99, 99.99, 99.99, 99.99)

SELECT  @sc_id  = IN_SC_ID
FROM    INDUSTRY
WHERE   IN_ID   = @in_id

PRINT   ' '
PRINT   'Insert duplicate into INDUSTRY'
PRINT   '----------------------------------------'
INSERT INTO INDUSTRY VALUES(@in_id, 'AUDIT DUPLICATE TEST ROW', @sc_id)

PRINT   ' '
PRINT   'Insert duplicate into LAST_TRADE'
PRINT   '----------------------------------------'
INSERT INTO LAST_TRADE VALUES('AA', GETDATE(), 99.99, 99.99, 999)

SELECT  @ni_id  = MAX(NI_ID)
FROM    NEWS_ITEM

PRINT   ' '
PRINT   'Insert duplicate into NEWS_ITEM'
PRINT   '----------------------------------------'
INSERT INTO NEWS_ITEM VALUES(@ni_id, 'AUDIT DUPLICATE TEST ROW', 'AUDIT DUPLICATE TEST ROW',  0x50616E6574746120416262616D6F6E74652044726967677320446F7764696E67204F62616E6E6F6E2042656E73652043617374696C6C6F20526879616E205368656C79204B657474656C6C204D636B61792053756E666F72642041676F722046756E73746F6E204E6F7272656C6C20526F7765204D61646C6F636B204D6F72, GETDATE(),  'AUDIT DUPLICATE TEST ROW',  'AUDIT DUPLICATE TEST ROW')

SELECT  @co_id   = NX_CO_ID
FROM    NEWS_XREF
WHERE   NX_NI_ID    = @ni_id

PRINT   ' '
PRINT   'Insert duplicate into NEWS_XREF'
PRINT   '----------------------------------------'
INSERT INTO NEWS_XREF VALUES(@ni_id, @co_id)

PRINT   ' '
PRINT   'Insert duplicate into SECTOR'
PRINT   '----------------------------------------'
INSERT INTO SECTOR VALUES('BM', 'AUDIT DUPLICATE TEST ROW')

PRINT   ' '
PRINT   'Insert duplicate into SECURITY'
PRINT   '----------------------------------------'
INSERT INTO SECURITY VALUES('AA', 'XXXXXX', 'ACTV', 'AUDIT DUPLICATE TEST ROW', 'AMEX', @co_id, 999, GETDATE(), GETDATE(), 99, 99.99, GETDATE(), 99.99, GETDATE(), 99.99, 999.99)

SELECT  @ad_id  = MAX(AD_ID)
FROM    ADDRESS

PRINT   ' '
PRINT   'Insert duplicate into ADDRESS'
PRINT   '----------------------------------------'
INSERT INTO ADDRESS VALUES(@ad_id, 'AUDIT DUPLICATE TEST ROW', ' ', '999999999999', 'XXXXXX')

PRINT   ' '
PRINT   'Insert duplicate into STATUS_TYPE'
PRINT   '----------------------------------------'
INSERT INTO STATUS_TYPE VALUES('ACTV', 'AUDIT DUPL')

PRINT   ' '
PRINT   'Insert duplicate into TAXRATE'
PRINT   '----------------------------------------'
INSERT INTO TAXRATE VALUES('TX1', 'AUDIT DUPLICATE TEST ROW', 0.0)

PRINT   ' '
PRINT   'Insert duplicate into ZIP_CODE'
PRINT   '----------------------------------------'
INSERT INTO ZIP_CODE VALUES('77077', 'AUDIT DUPLICATE TEST ROW', 'AUDIT DUPLICATE TEST ROW')

PRINT   ' '
PRINT   'TPC-E Duplicates Test End'
SELECT  CONVERT(CHAR(30), GETDATE(), 21)
