SET ansi_nulls on 
GO
SET quoted_identifier on 
GO
CREATE PROCEDURE [dbo].[addsecurityholding]

AS
  --
  --	If SP uses temp table then procedure must 'SET NOCOUNT ON' to avoid ADO error */
  --
  SET nocount on
  
  -- 	CONSTANTS
  DECLARE  @varAccountID INTEGER
  DECLARE  @varCashCount INTEGER
  DECLARE  @varPositionCount INTEGER
  DECLARE  @varUpdateCount INTEGER
  DECLARE  @varTranCount INTEGER 
  DECLARE  @varTotalCash DECIMAL(18,5)
  DECLARE  @varLoadDate DATETIME
  DECLARE  @account_number VARCHAR(12)
  DECLARE  @cusip VARCHAR(12)
  DECLARE  @class_code VARCHAR(5)
  DECLARE  @units DECIMAL(18,5)
  DECLARE  @price DECIMAL(18,5)
  DECLARE  @ticker VARCHAR(10)
  DECLARE  @market_value VARCHAR(50)
  DECLARE  @market_value_dec DECIMAL(18,5)
  DECLARE  @carrying_value VARCHAR(50)
  DECLARE  @carrying_value_dec DECIMAL(18,5)
  DECLARE  @prin_cash VARCHAR(50)
  DECLARE  @prin_cash_dec DECIMAL(18,5)
  DECLARE  @income_cash VARCHAR(50)
  DECLARE  @income_cash_dec DECIMAL(18,5)
  
  --	Fill/Update Temp Variables
  SELECT @varUpdateCount = 0
  SELECT @varTranCount = 0
  SELECT @varLoadDate = MAX(CAST(load_date AS DATETIME))
  FROM   load_holdings_security_detail
         
  --	Cursor For Loading New Data
  DECLARE holdingscursor CURSOR  FOR
  SELECT account_number,
         cusip,
         class_code,
         units,
         price,
         ticker,
         market_value,
         carrying_value,
         prin_cash,
         income_cash
  FROM   load_holdings_security_detail
  FOR READ ONLY
  
  --	Loop Through The Data, Scrub, and Load 
  OPEN holdingscursor
  
  FETCH NEXT FROM holdingscursor
  INTO @account_number,
       @cusip,
       @class_code,
       @units,
       @price,
       @ticker,
       @market_value,
       @carrying_value,
       @prin_cash,
       @income_cash
  
  WHILE @@FETCH_STATUS = 0
    BEGIN
      IF (@varTranCount = 0)
        BEGIN
          BEGIN TRANSACTION
          
          SELECT @varTranCount = @varTranCount + 1
        END
      ELSE
        IF (@varTranCount = 1000)
          BEGIN
            COMMIT
            
            SELECT @varTranCount = 0
          END
        ELSE
          BEGIN
            SELECT @varTranCount = @varTranCount + 1
          END
      --	Fill/Update Temp Variables
      IF (Patindex('%-%',@market_value) > 0)
        BEGIN
          SELECT @market_value = REPLACE(@market_value,'-','')
          
          SELECT @market_value_dec = (CAST(@market_value AS DECIMAL(18,5)) * -1)
        END
      ELSE
        BEGIN
          SELECT @market_value_dec = CAST(@market_value AS DECIMAL(18,5))
        END
      
      IF (Patindex('%-%',@carrying_value) > 0)
        BEGIN
          SELECT @carrying_value = REPLACE(@carrying_value,'-','')
          
          SELECT @carrying_value_dec = (CAST(@carrying_value AS DECIMAL(18,5)) * -1)
        END
      ELSE
        BEGIN
          SELECT @carrying_value_dec = CAST(@carrying_value AS DECIMAL(18,5))
        END
      
      IF (Patindex('%-%',@prin_cash) > 0)
        BEGIN
          SELECT @prin_cash = REPLACE(@prin_cash,'-','')
          
          SELECT @prin_cash_dec = (CAST(@prin_cash AS DECIMAL(18,5)) * -1)
        END
      ELSE
        BEGIN
          SELECT @prin_cash_dec = CAST(@prin_cash AS DECIMAL(18,5))
        END
      
      IF (Patindex('%-%',@income_cash) > 0)
        BEGIN
          SELECT @income_cash = REPLACE(@income_cash,'-','')
          
          SELECT @income_cash_dec = (CAST(@income_cash AS DECIMAL(18,5)) * -1)
        END
      ELSE
        BEGIN
          SELECT @income_cash_dec = CAST(@income_cash AS DECIMAL(18,5))
        END
      
      SELECT @varAccountID = Isnull((SELECT account_id
                                     FROM   account_detail
                                     WHERE  account_number = @account_number
                                            AND (portfolio_manager <> 'CLD'
                                                  OR administrator <> 'CLD')),
                                    NULL)
      
      IF (@price = 9999999.999999) BEGIN SELECT @price = 0 END
        
      SELECT @varTotalCash = Isnull((@prin_cash_dec + @income_cash_dec),NULL)
      
      SELECT @varCashCount = COUNT(* )
      FROM   positions
      WHERE  security_id = 'Cash'
             AND account_id = @varAccountID
             AND dbo.Dateonly(@varLoadDate) = dbo.Dateonly(position_date)
      
      SELECT @varPositionCount = COUNT(* )
      FROM   positions
      WHERE  security_id = @cusip
             AND account_id = @varAccountID
             AND dbo.Dateonly(@varLoadDate) = dbo.Dateonly(position_date)
      
      IF (@varCashCount > 0)
        BEGIN
          UPDATE positions
          SET    quantity = @varTotalCash,
                 COST = @varTotalCash
          WHERE  account_id = @varAccountID
                 AND security_id = 'Cash'
                 AND dbo.Dateonly(@varLoadDate) = dbo.Dateonly(position_date)
        END
      ELSE
        BEGIN
          INSERT INTO positions
                     (account_id,
                      security_id,
                      position_date,
                      quantity,
                      COST)
          SELECT @varAccountID,
                 'Cash',
                 CAST(dbo.Dateonly(@varLoadDate) AS DATETIME),
                 @varTotalCash,
                 @varTotalCash
        END
      
      SELECT @varUpdateCount = @varUpdateCount + @@ROWCOUNT
      
      IF (@varPositionCount > 0)
        BEGIN
          DECLARE  @newunits DECIMAL(18,5)
          DECLARE  @newcarrying_value_dec DECIMAL(18,5)
                                          
          SELECT @newunits = @units + Isnull((SELECT quantity
                                              FROM   positions
                                              WHERE  security_id = @cusip
                                                     AND account_id = @varAccountID
                                                     AND dbo.Dateonly(@varLoadDate) = dbo.Dateonly(position_date)),
                                             0)                          
          SELECT @newcarrying_value_dec = @carrying_value_dec + Isnull((SELECT COST
                                                                        FROM   positions
                                                                        WHERE  security_id = @cusip
                                                                               AND account_id = @varAccountID
                                                                               AND dbo.Dateonly(@varLoadDate) = dbo.Dateonly(position_date)),
                                                                       0)
        BEGIN
            UPDATE positions
            SET    quantity = @newunits,
                   COST = @newcarrying_value_dec
            WHERE  account_id = @varAccountID
                   AND security_id = @cusip
                   AND dbo.Dateonly(@varLoadDate) = dbo.Dateonly(position_date)
          END
        END
      ELSE
        BEGIN
          INSERT INTO positions
                     (account_id,
                      security_id,
                      position_date,
                      quantity,
                      COST)
          SELECT @varAccountID,
                 @cusip,
                 CAST(dbo.Dateonly(@varLoadDate) AS DATETIME),
                 @units,
                 @carrying_value_dec
        END
      
      SELECT @varUpdateCount = @varUpdateCount + @@ROWCOUNT
     
      --	Get Next Row of Data
      FETCH NEXT FROM holdingscursor
      INTO @account_number,
           @cusip,
           @class_code,
           @units,
           @price,
           @ticker,
           @market_value,
           @carrying_value,
           @prin_cash,
           @income_cash
    END
     --	Close Any Open Transactions And Cursors
  COMMIT
  
  CLOSE holdingscursor
  
  DEALLOCATE holdingscursor
  
  -- 	Verify An Update/Insert Was Done
  SELECT @varUpdateCount AS 'Rows Updated'