    alter    PROCEDURE dbo.sto_getTRDContractNote      
 @RecipientID    VARCHAR(16),      
 @BatchDate     DATETIME,      
 @TransmittalReportID INT,      
 @DataType    INT    --0 = Address info, 1 = Trade Info, 2 = Receipt and Disbursement info      
       
AS     
SET FMTONLY OFF        
      
/************************************************************************************************      
*      
* Title:  sto_getTRDContractNote..sql      
*      
* Author:  Mark E. Patterson      
*      
* Date created: 5/28/2004      
*      
* Desc:  Return Trades, Receipts and Disburement transactions based on id and batchdate.      
*      
* Change History:       
*  05/28/2004 - Initial version      
*           12/05/2007 - Updated Footer Contract Notes - Rajesh Meenrajan      
*                                  
*      
*  sto_getTRDContractNote '678|22.36.68.079' ,'05-feb-2001', 3  --Trade only      
*      
*  sto_getTRDContractNote '610|PAISUS', '2002-03-28', 3      
      
      
BRAN0018     2002-03-28 00:00:00.000      
*      
  sto_getTRDContractNote '610|51085' ,'16-oct-2003', 14, 0      
        
  sto_getTRDContractNote '610|117' ,'16-oct-2003', 15, 0      
      
  sto_getTRDContractNote '610|117' ,'16-oct-2003', 15, 1      
       
  sto_getTRDContractNote '610|117' ,'16-oct-2003', 15, 2      
      
  sto_getTRDContractNote '610|52' ,'16-oct-2003', 16, 0      
       
  sto_getTRDContractNote '610|52' ,'16-oct-2003', 16, 1      
      
  sto_getTRDContractNote '610|52' ,'16-oct-2003', 16, 2      
      
      
      
  sto_getTRDContractNote '610|19' ,'16-oct-2003', 6      
       
  sto_getTRDContractNote '610|18' ,'16-oct-2003', 6      
*      
*  sto_getTRDContractNote '610|50063A' ,'21-Oct-2002'      
*      
************************************************************************************************/      
      
SET NOCOUNT ON      
      
      
CREATE TABLE #tmpTradeAccountIDs (      
 AccountID   VARCHAR(12)    NOT NULL      
)      
      
CREATE TABLE #tmpTRD (      
  GroupID    INT IDENTITY,   --Fake grouping on access report      
  TypeGroup   INT NOT NULL,      
  TypeCode   VARCHAR(2) NOT NULL,  --Used to id Trades (T), Receipts(R) and Disbursements (D).        
  AccountId   VARCHAR(12) NOT NULL,      
  TransactionID  INT,       
  AssetName   VARCHAR(36) NULL,      
  TypeOfUnits   VARCHAR(50) NULL,      
  TradeDate   DATETIME NULL,      
  SettleDate   DATETIME NULL,        
  Amount    FLOAT NULL,      
  TotalUnitPosition FLOAT NULL,      
  NetProceeds   FLOAT NULL,       
  Price    FLOAT NULL,      
  Shares    FLOAT NULL,       
  CurrencyOfAsset  VARCHAR(30) NULL,       
  Type    VARCHAR(50) NOT NULL,      
  CUSIP    VARCHAR(12) NULL,      
  ISIN    VARCHAR(15) NULL,      
  SEDOL    VARCHAR(15) NULL,      
  TransactionDate  DATETIME NULL,      
  Explanation   VARCHAR(50) NULL      
)      
      
      
DECLARE @FooterNote   VARCHAR(2000),      
 @RequesterAdrId    VARCHAR(12),      
 @RequesterAddrPrefix VARCHAR(4),      
 @AddressName      VARCHAR(50),      
 @AddressLine2     VARCHAR(36),      
 @AddressLine3     VARCHAR(36),      
 @AddressLine4     VARCHAR(36),      
 @lblUnitPrice   VARCHAR(20),      
 @lblConsideration  VARCHAR(20),      
 @lblCurrencyCode  VARCHAR(6),      
 @TradeCount    INT,      
 @RecDistCount   INT,      
 @DisplayFlag   INT      
      
      
DECLARE @MasterID INT      
DECLARE @RequesterID VARCHAR(12)      
      
SELECT @MasterID = CONVERT(INT, LEFT(@RecipientID, CHARINDEX('|', @RecipientID)-1))      
SELECT @RequesterID = CONVERT(VARCHAR(12), SUBSTRING(@RecipientID, CHARINDEX('|', @RecipientID)+1, 30))      
      
      
------------------------------------------------------------      
-- Evaluate what level and get transaction information      
--      
-- Levels are determined by the @TransmittalReportID:      
--  14 = Account      
--  15 = Accountant      
--  16 = Administrator      
--  17 = BrokerDealer      
------------------------------------------------------------      
      
IF @DataType <> 0       
 BEGIN        
      
  --Broker Dealer      
  IF (@TransmittalReportID = 17)      
   BEGIN      
    IF @DataType = 1      
     BEGIN      
      --Trades      
      INSERT INTO #tmpTradeAccountIDs (AccountID)      
      SELECT DISTINCT t.AccountID      
      FROM Trades t, Accounts a, Accountants acct, Administrators adm, BrokerDealers bd      
      WHERE t.BatchDate = @BatchDate       
      AND t.AccountID = a.AccountID      
      AND t.MasterID = a.MasterID       
      AND a.AccountantID = acct.AccountantID      
      AND a.MasterID = acct.MasterID      
      AND acct.AdministratorID = adm.AdministratorID       
      AND acct.MasterID = adm.MasterID      
      AND adm.BrokerDealerID = bd.BrokerDealerID      
      AND bd.BrokerDealerID = CONVERT(INT, @RequesterID)      
      AND bd.MasterID = @MasterID      
           
     END --End @DataType = 1      
          
    IF @DataType = 2      
     BEGIN      
      --Disbursements      
      INSERT INTO #tmpTRD (      
       TypeGroup,      
       TypeCode,      
       AccountId,      
       TransactionID,      
       Amount,      
       Type,      
       TransactionDate,      
       Explanation        
      )      
      SELECT  2 'TypeGroup',      
        'D' 'TypeCode',      
        a.AccountID,      
        d.TransactionID,      
           d.TransactionAmount,      
        'Disbursement' 'Type',      
        d.TransactionDate,      
        d.Explanation       
         FROM DisbursementTransactions d,       
       DisbursementCodes dc,       
       Accounts a,      
       Accountants acct,      
       Administrators adm,      
       BrokerDealers bd      
      WHERE bd.BrokerDealerId = CONVERT(INT, @RequesterID)      
      AND bd.MasterId = @MasterId      
      AND bd.BrokerDealerID = adm.brokerDealerID      
      AND bd.MasterId = adm.MasterID      
      AND adm.AdministratorID = acct.AdministratorID      
      AND adm.MasterId = acct.MasterID      
      AND acct.AccountantID = a.AccountantID      
      AND acct.MasterId = a.MasterID      
      AND a.AccountID = d.AccountID      
      AND a.MasterId = d.MasterId       
      AND d.DisbursementCodeID = dc.DisbursementCodeID      
      AND d.MasterId = dc.MasterId      
      AND d.Transactiondate = @Batchdate      
      AND dc.GenerateConfirms = 1      
          
      --Receipts      
      INSERT INTO #tmpTRD (      
       TypeGroup,      
       TypeCode,      
       AccountId,      
       TransactionID,      
       Amount,      
       Type,      
       TransactionDate,      
       Explanation        
      )      
      SELECT  2 'TypeGroup',      
        'R' 'TypeCode',      
        a.AccountID,      
        r.TransactionID,      
           r.TransactionAmount,      
        'Receipt' 'Type',      
        r.TransactionDate,      
        r.Explanation       
         FROM ReceiptTransactions r,       
        ReceiptCodes rc,       
        Accounts a,      
        Accountants acct,      
        Administrators adm,      
        BrokerDealers bd      
      WHERE bd.BrokerDealerId = CONVERT(INT, @RequesterID)      
      AND bd.MasterId = @MasterId      
      AND bd.BrokerDealerID = adm.brokerDealerID      
      AND bd.MasterId = adm.MasterID      
      AND adm.AdministratorID = acct.AdministratorID      
      AND adm.MasterId = acct.MasterID      
      AND acct.AccountantID = a.AccountantID      
      AND acct.MasterId = a.MasterID      
      AND a.AccountID = r.AccountID      
      AND a.MasterId = r.MasterId       
      AND r.ReceiptCodeID = rc.ReceiptCodeID      
      AND r.MasterId = rc.MasterId      
      AND r.Transactiondate = @Batchdate      
      AND r.MasterId = @MasterId       
      AND rc.GenerateConfirms = 1      
      
    END --@DataType = 2       
              
          
          
      
   END --End @TransmittalReportID = 6      
        
  --Administrator      
  IF (@TransmittalReportID = 16)      
   BEGIN      
      
    IF @DataType = 1      
     BEGIN      
      --Trades      
      INSERT INTO #tmpTradeAccountIDs (AccountID)      
      SELECT DISTINCT t.AccountID      
      FROM Trades t, Accounts a, Accountants acct, Administrators adm      
      WHERE t.BatchDate = @BatchDate       
      AND t.AccountID = a.AccountID      
      AND t.MasterID = a.MasterID       
      AND a.AccountantID = acct.AccountantID      
      AND a.MasterID = acct.MasterID      
      AND acct.AdministratorID = adm.AdministratorID      
      AND acct.MasterID = adm.MasterID      
      AND adm.AdministratorID = CONVERT(INT, @RequesterID)      
      AND adm.MasterID = @MasterID      
      
     END      
         
    IF @DataType = 2      
     BEGIN      
          
      --Disbursements      
      INSERT INTO #tmpTRD (      
       TypeGroup,      
       TypeCode,      
       AccountId,      
       TransactionID,      
       Amount,      
       Type,      
       TransactionDate,      
       Explanation        
      )      
      SELECT  2 'TypeGroup',      
        'D' 'TypeCode',      
        d.AccountID,      
        d.TransactionID,      
           d.TransactionAmount,      
        'Disbursement' 'Type',      
        d.TransactionDate,      
        d.Explanation       
      FROM DisbursementTransactions d, DisbursementCodes dc, Administrators a      
      WHERE d.AdministratorId = a.AdministratorId      
      AND d.MasterId = a.MasterId       
      AND d.DisbursementCodeID = dc.DisbursementCodeID      
      AND d.MasterId = dc.MasterId      
      AND d.AdministratorId = CONVERT(INT, @RequesterID)      
      AND d.Transactiondate = @Batchdate      
      AND d.MasterId = @MasterId      
      AND dc.GenerateConfirms = 1      
          
          
      --Receipts      
      INSERT INTO #tmpTRD (      
       TypeGroup,      
       TypeCode,      
       AccountId,      
       TransactionID,      
       Amount,      
       Type,      
       TransactionDate,      
       Explanation        
      )      
      SELECT  2 'TypeGroup',      
        'R' 'TypeCode',      
        r.AccountID,      
        r.TransactionID,      
           r.TransactionAmount,      
        'Receipt' 'Type',      
        r.TransactionDate,      
        r.Explanation       
      FROM ReceiptTransactions r, ReceiptCodes rc, Administrators a      
      WHERE r.AdministratorId = a.AdministratorId      
      AND r.MasterId = a.MasterId       
      AND r.ReceiptCodeID = rc.ReceiptCodeID      
      AND r.MasterId = rc.MasterId      
      AND r.AdministratorId = CONVERT(INT, @RequesterID)      
      AND r.Transactiondate = @Batchdate      
      AND r.MasterId = @MasterId      
      AND rc.GenerateConfirms = 1      
          
     END --@DataType = 2          
        
   END  --End @TransmittalReportID = 5      
      
  --Accountant      
  IF (@TransmittalReportID = 15)      
   BEGIN      
    IF @DataType = 1      
     BEGIN      
      --Trades      
      INSERT INTO #tmpTradeAccountIDs (AccountID)      
      SELECT DISTINCT t.AccountID      
      FROM Trades t, Accounts a, Accountants acct      
      WHERE t.BatchDate = @BatchDate       
      AND t.AccountID = a.AccountID      
      AND t.MasterID = a.MasterID       
      AND a.AccountantID = acct.AccountantID      
      AND a.MasterID = acct.MasterID      
      AND acct.AccountantID = CONVERT(INT, @RequesterID)      
      AND acct.MasterID = @MasterID      
            
     END        
    IF @DataType = 2      
     BEGIN      
      --Disbursements      
      INSERT INTO #tmpTRD (      
       TypeGroup,      
       TypeCode,      
       AccountId,      
       TransactionID,      
       Amount,      
       Type,      
       TransactionDate,      
       Explanation        
      )      
      SELECT  2 'TypeGroup',      
        'D' 'TypeCode',      
        d.AccountID,      
        d.TransactionID,      
           d.TransactionAmount,      
        'Disbursement' 'Type',      
        d.TransactionDate,      
        d.Explanation       
         FROM DisbursementTransactions d, DisbursementCodes dc, Accountants a      
      WHERE d.AccountantId = a.AccountantId      
      AND d.MasterId = a.MasterId       
      AND d.DisbursementCodeID = dc.DisbursementCodeID      
      AND d.MasterId = dc.MasterId      
      AND d.AccountantId = CONVERT(INT, @RequesterID)      
      AND d.Transactiondate = @Batchdate      
      AND d.MasterId = @MasterId      
      AND dc.GenerateConfirms = 1      
          
      --Receipt      
      INSERT INTO #tmpTRD (      
       TypeGroup,      
       TypeCode,      
       AccountId,      
       TransactionID,      
       Amount,      
       Type,      
       TransactionDate,      
       Explanation        
      )      
      SELECT  2 'TypeGroup',      
        'R' 'TypeCode',      
        r.AccountID,      
        r.TransactionID,      
           r.TransactionAmount,      
        'Receipt' 'Type',      
        r.TransactionDate,      
        r.Explanation     
         FROM ReceiptTransactions r, ReceiptCodes rc, Accountants a      
      WHERE r.AccountantId = a.AccountantId      
      AND r.MasterId = a.MasterId       
      AND r.ReceiptCodeID = rc.ReceiptCodeID      
      AND r.MasterId = rc.MasterId      
      AND r.AccountantId = CONVERT(INT, @RequesterID)      
      AND r.Transactiondate = @Batchdate      
      AND r.MasterId = @MasterId      
      AND rc.GenerateConfirms = 1      
      print 'here1'      
--      select * from #tmpTRD      
     END --End @DataType = 2            
        
   END --End @TransmittalReportID = 4      
  --Account      
  IF (@TransmittalReportID = 14)      
   BEGIN      
    IF @DataType = 1      
     BEGIN                
      INSERT INTO #tmpTradeAccountIDs (AccountID)      
      SELECT DISTINCT a.AccountID      
      FROM Trades t, Accounts a      
      WHERE t.BatchDate = @BatchDate       
      AND t.AccountID = @RequesterID      
      AND t.MasterID = @MasterID      
      AND t.AccountID = a.AccountID      
      AND t.MasterID = a.MasterID      
     END --End @DataType = 1      
      
    IF @DataType = 2      
     BEGIN      
      --Disbursement      
      INSERT INTO #tmpTRD (      
       TypeGroup,      
       TypeCode,      
       AccountId,      
       TransactionID,      
       Amount,      
       Type,      
       TransactionDate,      
       Explanation        
      )      
      SELECT  2 'TypeGroup',      
        'D' 'TypeCode',      
        a.AccountID,      
        d.TransactionID,      
           d.TransactionAmount,      
        'Disbursement' 'Type',      
        d.TransactionDate,      
        d.Explanation       
         FROM DisbursementTransactions d, DisbursementCodes dc,  Accounts a      
      WHERE d.AccountId = a.AccountId      
      AND d.MasterId = a.MasterId       
      AND d.DisbursementCodeID = dc.DisbursementCodeID      
      AND d.MasterId = dc.MasterId      
      AND d.AccountId = CONVERT(VARCHAR(12), @RequesterID)      
      AND d.Transactiondate = @Batchdate      
      AND d.MasterId = @MasterId      
      AND dc.GenerateConfirms = 1      
          
      --Receipt      
      INSERT INTO #tmpTRD (      
       TypeGroup,      
       TypeCode,      
       AccountId,      
       TransactionID,      
       Amount,      
       Type,      
       TransactionDate,      
       Explanation        
      )      
      SELECT  2 'TypeGroup',      
        'R' 'TypeCode',      
        a.AccountID,      
        r.TransactionID,      
           r.TransactionAmount,      
        'Receipt' 'Type',      
        r.TransactionDate,      
        r.Explanation       
         FROM ReceiptTransactions r, ReceiptCodes rc,  Accounts a      
      WHERE r.AccountId = a.AccountId      
      AND r.MasterId = a.MasterId       
      AND r.ReceiptCodeID = rc.ReceiptCodeID      
      AND r.MasterId = rc.MasterId      
      AND r.AccountId = CONVERT(VARCHAR(12), @RequesterID)      
      AND r.Transactiondate = @Batchdate      
      AND r.MasterId = @MasterId      
      AND rc.GenerateConfirms = 1      
           
     END --End @DataType = 2      
           
   END -- End @TransmittalReportID = 3      
        
  --Set Currency Code for Unit Price and Consideration      
  SELECT @lblUnitPrice = ' Unit Price ',       
   @lblConsideration = ' Consideration ',      
   @lblCurrencyCode =       
   CASE      
    WHEN @MasterID = 552 THEN '(): '      
    WHEN @MasterID = 645 THEN '($): '      
    WHEN @MasterID = 610 THEN '(): '      
    ELSE ': '      
   END           
         
 END --End IF @DataType <> 0       
ELSE      
 BEGIN      
  ----------------------------------------------------------      
  -- Get Address       
  --      
  -- Note: Administrator addresses are stored with a      
  --  'ADMIN' prefix and zero's to make the      
  --  integer length equal to four.      
  --      
  --  For example: TaxpayerId = 'ADMIN0001'      
  --      
  -- Levels are determined by the @TransmittalReportID:      
  --  14 = Account      
  --  15 = Accountant      
  --  16 = Administrator      
  --  17 = BrokerDealer      
  --         
  --           
  -----------------------------------------------------------      
  IF (@TransmittalReportID = 17)      
   BEGIN      
    --Set Address Prefix and get name      
    SELECT @RequesterAddrPrefix = 'BRAN'      
          
    SELECT @AddressName = BrokerDealerName      
    FROM BrokerDealers       
    WHERE BrokerDealerID = CONVERT(INT, @RequesterID)      
    AND MasterID = @MasterID      
   END      
          
   IF (@TransmittalReportID = 16)      
    BEGIN      
     --Set Address Prefix and name      
     SELECT @RequesterAddrPrefix = 'ADMN'      
           
     SELECT @AddressName = AdministratorName       
     FROM Administrators      
     WHERE  AdministratorID = CONVERT(INT, @RequesterID)      
     AND MasterID = @MasterID      
    END            
         
   IF (@TransmittalReportID = 15)      
    BEGIN      
     --Set Address Prefix and name      
     SELECT @RequesterAddrPrefix = 'ACCT'      
           
     SELECT @AddressName = AccountantName       
     FROM Accountants      
     WHERE  AccountantID = CONVERT(INT, @RequesterID)      
     AND MasterID = @MasterID      
    END      
      
   IF (@TransmittalReportID = 14)      
    BEGIN      
     SELECT @AddressName = Accountname       
     FROM Accounts      
     WHERE  AccountID LIKE @RequesterID      
     AND MasterID = @MasterID      
    END      
            
  -- Format TaxPayerID      
  IF (@TransmittalReportID = 14)      
   BEGIN      
    SELECT @RequesterAdrId = a.TaxPayerId      
    FROM Accounts a      
    WHERE a.AccountId = @RequesterId      
    AND a.MasterID = @MasterId      
   END      
  ELSE      
   BEGIN      
    SELECT @RequesterAdrId =       
    CASE       
             WHEN  LEN(@RequesterID) < 4 THEN @RequesterAddrPrefix + SUBSTRING((CONVERT(VARCHAR(12),'0000' + CONVERT(char,@RequesterID))),(LEN((CONVERT(VARCHAR(12),'0000' + convert(char,@RequesterID))))-3),4)          
       ELSE @RequesterAddrPrefix + CONVERT(VARCHAR(12),@RequesterID)      
    END      
   END         
        
  --Get Address based on TaxPayerId      
  SELECT @AddressLine2 = AddressLine2,         
   @AddressLine3 = AddressLine3,      
   @AddressLine4 = AddressLine4      
  FROM ADDRESSES      
  WHERE TaxpayerID = @RequesterAdrId      
  AND MasterID = @MasterID      
        
END --@DataType <> 0 Else      
      
------------------------------------------------------------------------------------------      
--Get Trade specific data      
------------------------------------------------------------------------------------------      
IF @DataType = 1      
 BEGIN      
  --  Get Valuation point data      
          
  -- Get fund info to determine valuation point and footer       
  CREATE TABLE #tmp_Valuation (      
   CUSIP   VARCHAR(12) NOT NULL,      
   TrdCutoffTimeBuy  VARCHAR(20) NULL,      
   TrdCutoffTimeSell VARCHAR(20) NULL,      
   MinorIndustryCode INT NULL,      
   ValuationPoint  VARCHAR(5) NULL      
  )      
        
  INSERT INTO #tmp_Valuation (      
   CUSIP,      
   TrdCutoffTimeBuy,      
   TrdCutoffTimeSell,      
   MinorIndustryCode,      
   ValuationPoint      
  )      
  SELECT  DISTINCT t.CUSIP,      
   t.TrdCutoffTimeBuy,      
   t.TrdCutoffTimeSell,      
   a.MinorIndustryCode,      
   '*' "ValuationPoint"      
  FROM Trades t, AssetList a      
  WHERE t.CUSIP *= a.CUSIP      
  AND t.BatchDate=@BatchDate       
  AND t.AccountId IN (SELECT DISTINCT AccountID from #tmpTradeAccountIds)      
  AND t.MasterID = @MasterID      
  AND t.MasterID *= a.MasterID      
        
        
  -- Set 5 PM       
  UPDATE #tmp_Valuation      
  SET ValuationPoint = '5 PM'      
  WHERE MinorIndustryCode IN (158,162)      
  AND TrdCutoffTimeBuy = '160000'      
        
        
  -- Set 2 PM       
  UPDATE #tmp_Valuation      
  SET ValuationPoint = '2 PM'      
  WHERE MinorIndustryCode IN (158,162)      
  AND TrdCutoffTimeBuy = '130000'      
        
  -- Set 5 PM       
  UPDATE #tmp_Valuation      
  SET ValuationPoint = '5 PM'      
  WHERE MinorIndustryCode IN (158,162)      
  AND TrdCutoffTimeBuy = '140000'      
        
  -- Set 2 PM       
  UPDATE #tmp_Valuation      
  SET ValuationPoint = '2 PM'      
  WHERE MinorIndustryCode IN (158,162)      
  AND TrdCutoffTimeBuy = '131500'      
        
        
  --  Get  Trade Info      
  INSERT INTO #tmpTRD (      
   TypeGroup,      
   TypeCode,      
   AccountId,      
   TransactionID,      
   AssetName,      
   TypeOfUnits,      
   TradeDate,      
   SettleDate,      
   Amount,      
   TotalUnitPosition,      
   NetProceeds,      
   Price,      
   Shares,      
   CurrencyOfAsset,      
   Type,      
   CUSIP,      
   ISIN,      
   SEDOL      
  )      
  SELECT 1 'TypeGroup',      
   'T' 'TypeCode',       
   t.AccountId,      
   t.TradeID,      
   t.AssetName,       
   CASE Internal      
    WHEN  'T' THEN ' Accumulating'      
    WHEN  'F' THEN ' Distributing'       
    ELSE ' Distributing'      
   END 'TypeOfUnits',      
   CASE t.Type      
    WHEN 'REINVEST' THEN t.ExDividendDate      
    ELSE t.TradeDate      
   END 'TradeDate',       
   CASE t.Type      
    WHEN 'REINVEST' THEN t.PayableDate      
    ELSE t.SettleDate      
   END 'SettleDate',        
   t.Amount,       
   t.AdjustedShares 'TotalUnitPosition',      
   t.NetProceeds,       
   t.Price,       
   t.Shares,       
   t.CurrencyOfAsset,       
   CASE t.Type      
    WHEN 'CAP GAIN' THEN 'Capital Gain'      
    WHEN 'CTF DIST' THEN 'Common Trust Fund Distribution'      
    WHEN 'DISTRIBUTE' THEN 'Distribution In-Kind'      
    WHEN 'DIVIDEND' THEN 'Dividend'      
    WHEN 'DIVREINVES' THEN 'Dividend Reinvested'      
    WHEN 'INCOME' THEN 'Income'      
    WHEN 'INTEREST' THEN 'Interest'      
    WHEN 'PURCHASE' THEN 'Purchase'      
    WHEN 'RECEIPT' THEN 'Receipt In-Kind'      
    WHEN 'REDEMPTION' THEN 'Sale'      
    WHEN 'STK DIV CASH' THEN 'Stock Dividend Cash'      
    WHEN 'STK SPLT CASH' THEN 'Stock Split Cash'      
    WHEN 'STOCK DIV' THEN 'Stock Dividend'      
    WHEN 'STOCK SPLT' THEN 'Stock Split'       
    WHEN 'REINVEST' THEN 'Reinvest'      
    ELSE t.Type      
   END 'Type',       
   CUSIP,       
   CASE       
    WHEN LEN(LTRIM(RTRIM(t.ISIN))) = 0 THEN 'Not Available'      
    WHEN t.ISIN IS NULL THEN 'Not Available'      
    ELSE t.ISIN      
   END 'ISIN',      
   CASE       
    WHEN LEN(LTRIM(RTRIM(t.SEDOL))) = 0 THEN 'Not Available'      
    WHEN t.SEDOL IS NULL THEN 'Not Available'      
    ELSE t.SEDOL      
   END 'SEDOL'      
  FROM Trades t, #tmpTradeAccountIDs tmp      
  WHERE T.BatchDate=@BatchDate       
  AND t.AccountId=tmp.AccountID      
  AND t.MasterID = @MasterID      
  AND t.ExcludeTransaction = 0      
  ORDER BY t.Type,t.AssetName      
      
 END  --End @DataType = 1      
      
      
/****************************************************      
*  Build Footer section       
*****************************************************/      
      
      
SELECT @FooterNote = 'This contract note confirms that your application has been accepted by SEI Investments (Europe) Limited and, where applicable, that the terms of the Custody Agreement or Investment Management and Custody Agreement are legally bindin 
 
    
g between you and SEI.'        
                     + char(13) + char(10) +  char(13) + char(10) +      
                                     'SEI Investments (Europe) Limited, authorised and regulated by the Financial Services Authority, is the Distributor of the SEI Global Investor Funds (SGIF), Global Asset Funds (SGAF), Global Master Funds (SGMF), SEI Gl
  
    
obal Investments Trust (SGIT) and the SEI Islamic Investments Fund (SIIF). The SGIF, SGAF, SGMF and SIIF are multi-portfolio umbrella funds incorporated as variable capital limited liability investment companies in Ireland, authorised and regulated by the
  
    
 Irish Financial Services Regulatory Authority (IFSRA) pursuant to the European Communities (Undertaking for Collective Investment in Transferable Securities  UCITS) Regulations 2003. The SGIT is an umbrella fund with segregated liability between sub-fun
  
    
ds constituted as a unit trust under the laws of Ireland and authorised by IFSRA pursuant to the Unit Trusts Act 1990. SEI Investments Global Limited (SIGL) is the manager of the SGIF, SGAF, SGMF, SGIT and SIIF with SEI Investments Management Corporation 
  
    
(SIMC) the Investment Adviser.'          
                                     + char(13) + char(10) + char(13) + char(10) +         
                                     'Transactions in SEI Funds are dealt with on a forward pricing basis - i.e. the first price available after your instruction was accepted by us. Details of the time of this transaction are available on request.'      
                                     + char(13) + char(10) + char(13) + char(10) +      
                                     'For further information please contact the Managed Accounts Team on +44 207 297 6300.'          
      
      
-------------------------------------------------------------------------------------------      
-- Final output      
-------------------------------------------------------------------------------------------      
IF @DataType = 0      
 BEGIN      
   SELECT 'SEI Investments (Europe) Limited' 'HdrSEIName',      
   '1 Bruton Street, London, W1J 6TL' 'HdrAddressOne',      
   'Authorised and regulated by the Financial Services Authority' 'HdrAddressTwo',       
   ISNULL(@AddressName, 'Mr. Joseph A. Client') 'AddressName',      
   @AddressLine2 'AddressLine2',       
   @AddressLine3 'AddressLine3',      
   @AddressLine4 'AddressLine4',      
   @FooterNote 'Footernote'      
 END      
ELSE      
 BEGIN      
   SELECT 'SEI Investments (Europe) Limited' 'HdrSEIName',      
   '1 Bruton Street, London, W1J 6TL' 'HdrAddressOne',      
   'Authorised and regulated by the Financial Services Authority' 'HdrAddressTwo',       
   ISNULL(@AddressName, 'Mr. Joseph A. Client') 'AddressName',      
   @AddressLine2 'AddressLine2',       
   @AddressLine3 'AddressLine3',      
   @AddressLine4 'AddressLine4',      
   @FooterNote 'Footernote',
   TypeGroup,      
   AccountId,      
   AssetName,      
   TypeOfUnits,      
   TradeDate,      
   SettleDate,      
   Amount,      
   TotalUnitPosition,      
   NetProceeds,      
   Price,      
   Shares,      
   CurrencyOfAsset,      
   Type,      
   CUSIP,      
   ISIN,      
   SEDOL,      
   TransactionDate,      
   Explanation,      
   CONVERT(VARCHAR(12),TransactionID) + TypeCode + @RequesterId 'UniqueNumber',      
   @lblUnitPrice + @lblCurrencyCode 'lblUnitPrice',      
   @lblConsideration + @lblCurrencyCode 'lblConsideration',      
   @FooterNote 'Footernote',      
   '*' 'ValuationPoint'      
  FROM #tmpTrd      
  WHERE TypeGroup = @DataType      
 END      
      
      
      