CREATE PROCEDURE dbo.%PROC% (@sid VARCHAR(30) = NULL, @action_history_id NUMERIC, @action_id NUMERIC) AS

DECLARE @id NUMERIC
.ID_VAR(@type_id)

SET @id = CONVERT(NUMERIC, @sid)
SELECT
  @type_id = type_id
FROM
  t_items
WHERE
  id = @id

.GET_PROPS

BEGIN TRAN

DECLARE @is_realiz INT
DECLARE @is_p2l_comis INT
DECLARE @is_com_in INT
DECLARE @is_allow_short4stocks INT
DECLARE @is_allow_short4money INT
DECLARE @asset4pereoc_type_id NUMERIC
DECLARE @plan_money_id NUMERIC
DECLARE @plan_secur_id NUMERIC
DECLARE @out_method NUMERIC
DECLARE @money_plan_cur_id NUMERIC
DECLARE @stock_acc_id NUMERIC
DECLARE @mess VARCHAR(255)
DECLARE @organisation_id NUMERIC
DECLARE @sint_plan_id NUMERIC
DECLARE @sint_plan_cur_id NUMERIC
DECLARE @is_sint INT
DECLARE @proc_state INT
DECLARE @st_id NUMERIC
DECLARE @acc_id NUMERIC

-- letsko_av
-- 21.03.2005
-- Фильтрация по типу инвестора
.ID_VAR(@investor_id)
IF @investor_type_id = 1
  -- переоценка только собственных активов
  SET @investor_id = @organisation_id
ELSE IF @investor_type_id = 2
  -- переоценка всех клиентских активов
  SET @investor_id = NULL
ELSE IF @investor_type_id = 3
  -- переоценка для выбранного клиента
  SET @investor_id = @investor_id_form
ELSE IF @investor_type_id = 4
  -- переоценка собственных и клиентских активов
  SET @investor_id = NULL
ELSE BEGIN
  IF @@trancount <> 0
    ROLLBACK TRAN
  RAISERROR 40000 'Неизвестные виды активов для переоценки'
  RETURN
END
-- letsko_av (end)

-----
.ID_VAR(@sav_stock_id)
.ID_VAR(@sav_inv_id)
.ID_VAR(@sav_subc_id)
.ID_VAR(@sav_portfolio_id)
---
.MONEY_VAR(@ovr_sum_op)
---
.ID_VAR(@client_id)
.ID_VAR(@is_client_resident)
.ID_VAR(@res_char_id)
---
.NAME_VAR(@home_subc_str)

.BACK_GET_ORG_PARMS(@owner_id, @organisation_id, @plan_money_id, @plan_secur_id, @out_method, @is_realiz, @is_p2l_comis, @is_com_in, @is_allow_short4stocks, @is_allow_short4money, @asset4pereoc_type_id)

SELECT
  @money_plan_cur_id = cur_id
FROM
  t_plans
WHERE
  id = @plan_money_id

.ACCID_FROM_CONST2PLAN(@stock_acc_id, 'BACK_ACC_STOCK_SV', @plan_secur_id, 1, 1, 'ЦБ свободные', @mess)

DECLARE @ssb_acc_eff_num VARCHAR(255)

SELECT
  @ssb_acc_eff_num = acc_num_eff_rep
FROM
  t_accounts
WHERE
  id = @stock_acc_id

DECLARE @coupon_no VARCHAR(10), @date_b DATETIME, @date_e DATETIME, @income_sum DECIMAL(18, 8),
  @income_percent DECIMAL(18,8), @coupon_period INT, @coupon_rate_ind NUMERIC, @day_basis_code NUMERIC,
  @divisor_basis_code NUMERIC

DECLARE @coupon_sum DOUBLE PRECISION, @coupon_cur_id NUMERIC, @nominal DECIMAL(18, 8),
  @nominal_cur_id NUMERIC, @qty_day INT, @perc_or_sum DOUBLE PRECISION, @qty_day_in_year INT,
  @partion_id NUMERIC

DECLARE @bd DATETIME, @ed DATETIME

DECLARE @sec_plan2partner_id NUMERIC
DECLARE @mon_plan2partner_id NUMERIC
DECLARE @subc_str_bal VARCHAR(255)
DECLARE @ssc_saldo_qty MONEY
DECLARE @ssc_inv_id NUMERIC
DECLARE @ssc_subc_id NUMERIC
DECLARE @ssc_portfolio_id NUMERIC
DECLARE @ssc_stock_id NUMERIC
DECLARE @d_subconto_str VARCHAR(255)
DECLARE @k_subconto_str VARCHAR(255)
DECLARE @oper_sum MONEY
DECLARE @comment VARCHAR(255)
DECLARE @acc_445_id NUMERIC
DECLARE @acc_440_id NUMERIC
DECLARE @fin_result INT
DECLARE @character_id NUMERIC
DECLARE @kd45 MONEY
DECLARE @kd40 MONEY
DECLARE @kd45_qty MONEY
DECLARE @kd40_qty MONEY
DECLARE @all_qty MONEY

.ACCID_FROM_CONST2PLAN(@acc_445_id, 'BACK_ACC_NKD_IN_PORTFOLIO', @plan_money_id, 1, 1, 'НКД за нахождение в портфеле', @mess)

.ACCID_FROM_CONST2PLAN(@acc_440_id, 'BACK_ACC_UNKD', @plan_money_id, 1, 1, 'УНКД', @mess)

SET @subc_str_bal = ''

.ID_VAR(@ssb_inv_subc_type_id)
.ID_VAR(@ssb_invsub_subc_type_id)
.ID_VAR(@ssb_portf_subc_type_id)
.ID_VAR(@ssb_stock_subc_type_id)

SELECT @sec_plan2partner_id = .PLAN2PARTNER_ID(@owner_id, @plan_secur_id)
SELECT @mon_plan2partner_id = .PLAN2PARTNER_ID(@owner_id, @plan_money_id)

SELECT @ssb_inv_subc_type_id = .SUBCTYPE_FROM_CODE('INVESTORS')
SELECT @ssb_invsub_subc_type_id = .SUBCTYPE_FROM_CODE('INVESTOR2PORTFOLIO')
SELECT @ssb_portf_subc_type_id = .SUBCTYPE_FROM_CODE('DOC_SEC_IN')
SELECT @ssb_stock_subc_type_id = .SUBCTYPE_FROM_CODE('STOCK_EMIS')

.SUBC_STR_ADD(@subc_str_bal, 'STOCK_EMIS', @stock_id, Y)
.ACCID_FROM_CONST2PLAN(@stock_acc_id, 'BACK_ACC_STOCK_SV', @plan_money_id, 1, 1, 'ЦБ свободные (1.0.0)', @mess)
SELECT
  @ssb_acc_eff_num = acc_num_eff_rep
FROM
  t_accounts
WHERE
  id = @stock_acc_id
--==============

DECLARE stock_srez CURSOR LOCAL FORWARD_ONLY STATIC READ_ONLY FOR
SELECT
  SUM(o.saldo_qty * o.deb_or_cred),
  s1.item_id, -- ЦБ
  s2.item_id, -- Инвестор
  s3.item_id, -- Раздел учета
  s4.item_id, -- Портфель (документ-основание на самом деле)
  o.acc_id
FROM
  t_account_now o,
  t_accounts ac,
  t_accnow_subconto s1,
  t_accnow_subconto s2,
  t_accnow_subconto s3,
  t_accnow_subconto s4
WHERE
  o.plan2partner_id = @mon_plan2partner_id
  AND o.acc_id = ac.id
  AND ac.acc_num_eff_rep LIKE @ssb_acc_eff_num + '%'
  AND o.subc_eff_num LIKE ISNULL('%' + @subc_str_bal + '%', '%')
  AND o.id = s1.id
  and s1.subconto_type_id = @ssb_stock_subc_type_id
  and o.id = s2.id
  and s2.subconto_type_id =  @ssb_inv_subc_type_id
  and o.id = s3.id
  and s3.subconto_type_id = @ssb_invsub_subc_type_id
  and o.id = s4.id
  and s4.subconto_type_id = @ssb_portf_subc_type_id
  AND (
    -- переоценка только собственных активов
    ((@investor_type_id = 1) AND (o.subc_eff_num LIKE ISNULL('%' + CONVERT(VARCHAR, @organisation_id) + '%', '%'))) OR
    -- переоценка всех клиентских активов
    ((@investor_type_id = 2) AND (o.subc_eff_num NOT LIKE ISNULL('%' + CONVERT(VARCHAR, @organisation_id) + '%', '%'))) OR
    -- переоценка для выбранного клиента
    ((@investor_type_id = 3) AND (o.subc_eff_num LIKE ISNULL('%' + CONVERT(VARCHAR, @investor_id_form) + '%', '%'))) OR
    -- переоценка собственных и клиентских активов
    (@investor_type_id = 4)
  )
    AND .ITEMS_EXISTS_BY_STOCK(s1.item_id)
GROUP BY
  s1.item_id,
  s2.item_id,
  s3.item_id,
  s4.item_id,
  o.acc_id
HAVING
  SUM(o.saldo * o.deb_or_cred) > 0

OPEN stock_srez
FETCH stock_srez INTO @ssc_saldo_qty, @ssc_stock_id, @ssc_inv_id, @ssc_subc_id, @ssc_portfolio_id, @acc_id
-------------------------

WHILE .CURSOR_STATE = 0 BEGIN
  SELECT @out_method = .GET_METHOD_OUT(@ssc_subc_id)

  .GET_IN_PARTION(@partion_id, @out_method, @ssc_portfolio_id)

  IF EXISTS(SELECT 1 FROM tb_contracts WHERE id = @ssc_inv_id) BEGIN
    SET @character_id = 2

    SELECT
      @fin_result = fin_result
    FROM
      tb_contracts
    WHERE
      id = @ssc_inv_id
  END
  ELSE BEGIN
    SET @character_id = 1
    SET @fin_result = 1
  END

  .ASSIGN(@d_subconto_str, '')
  .SUBC_STR_ADD(@d_subconto_str, 'STOCK_EMIS', @ssc_stock_id, N)
  .SUBC_STR_ADD(@d_subconto_str, 'INVESTORS', @ssc_inv_id, N)
  .SUBC_STR_ADD(@d_subconto_str, 'INVESTOR2PORTFOLIO', @ssc_subc_id, N)
  .SUBC_STR_ADD(@d_subconto_str, 'DOC_SEC_IN', @ssc_portfolio_id, N)

  .ASSIGN(@kd45, 0)
  .ASSIGN(@kd40, 0)

  IF @acc_445_id <> @acc_440_id BEGIN
    .SALDO(@owner_id, @plan_money_id, @in_date, @acc_445_id, @d_subconto_str, N, D, @kd45)
  END
  ELSE
    SET @kd45 = 0

  .SALDO(@owner_id, @plan_money_id, @in_date, @acc_440_id, @d_subconto_str, N, D, @kd40)

  SET @kd45_qty = @kd45
  SET @kd40_qty = @kd40

  SELECT
    @client_id = client_id
  FROM
    tb_contracts
  WHERE
    id = @ssc_inv_id

  DECLARE @cur_rate_date DATETIME, @cur_is_rate INT, @fund_rate_value MONEY, @cur_stock_sum DECIMAL(18, 8),
    @cur_coupon_sum DECIMAL(18, 8)

--  .PIF_CALC_STOCK_PRICE(@ssc_stock_id, @client_id, @rate_place_id, @in_date, @ssc_saldo_qty, @cur_rate_date, @cur_is_rate, @fund_rate_value, @oper_sum,
--      @cur_stock_sum, @cur_coupon_sum)

-- Поиск НКД
select
  @oper_sum = tb_stock_rates.coupon_rate
from
  tb_stock_rates
where
  tb_stock_rates.rate_place_id = @rate_place_id
  and tb_stock_rates.stock_id = @ssc_stock_id
  and tb_stock_rates.rate_date = @in_date
  and .ITEMS_EXISTS(tb_stock_rates.id)

--select @ssc_stock_id AS ssc_stock_id, @client_id AS client_id, @rate_place_id AS rate_place_id, @in_date AS in_date,
--  @ssc_saldo_qty AS ssc_saldo_qty, @cur_rate_date AS cur_rate_date, @cur_is_rate AS cur_is_rate,
--  @fund_rate_value AS fund_rate_value, @oper_sum AS oper_sum, @cur_stock_sum AS cur_stock_sum, @cur_coupon_sum AS cur_coupon_sum

--select @oper_sum AS oper_sum, @cur_is_rate AS cur_is_rate, @kd45_qty AS kd45_qty, @kd40_qty AS kd40_qty

  IF ISNULL(@oper_sum, 0) = 0 BEGIN
    .BACK_CALC_COUPON(@ssc_stock_id, @ssc_saldo_qty, @in_date, 1, @oper_sum, @sint_plan_cur_id)
  END
  ELSE
    SELECT @oper_sum = @oper_sum * @ssc_saldo_qty

  SELECT @oper_sum = @oper_sum - ISNULL(@kd45_qty, 0) - ISNULL(@kd40_qty, 0)

  SET @d_subconto_str = ''

  .SUBC_STR_ADD(@d_subconto_str, 'STOCK_EMIS', @ssc_stock_id)
  .SUBC_STR_ADD(@d_subconto_str, 'INVESTORS', @ssc_inv_id)
  .SUBC_STR_ADD(@d_subconto_str, 'INVESTOR2PORTFOLIO', @ssc_subc_id)
  DECLARE @evc_portfolio NUMERIC
  SELECT @evc_portfolio = (SELECT portfolio_id FROM tb_baysale_docitems WHERE id = @partion_id)
-- letsko_av
-- 18.04.2005
-- Затычка. Если портфель все еще пустой - всовываем первый попавшийся (тем более у почти всех компаний он один)
  IF @evc_portfolio IS NULL BEGIN
     set @evc_portfolio = (select portfolio_id from td_stock_moves where id = @partion_id)
    IF @evc_portfolio IS NULL
      set @evc_portfolio = (SELECT TOP 1 id FROM tb_portfolio)
  END
-- letsko_av (end)

-- letsko_av
-- 05.04.2005
-- Затычка. Если портфель пустой - пробуем его не заполнять.
  IF @evc_portfolio IS NOT NULL
    .SUBC_STR_ADD(@d_subconto_str, 'PORTFOLIO', @evc_portfolio)
-- letsko_av (end)

  .SUBC_STR_ADD(@d_subconto_str, 'DOC_SEC', @partion_id)
  .SUBC_STR_ADD(@d_subconto_str, 'DOC_SEC_IN', @partion_id)

  SET @k_subconto_str = @d_subconto_str

  SET @comment = 'Начисление КД'

-- тут строятся проводки
--  if isnull(@oper_sum,-1)<0 .EXIT_MESSAGE('Сумма НКД отрицательна или для данной бумаги не задан купонный период !!!')

  .BACK_CLOSE_PROFIT_LOSS_QTY_COMM(@owner_id, @plan_money_id, @in_date, @fin_result, @character_id, @d_subconto_str, 3, @acc_445_id, @oper_sum, @comment, 0)

  SELECT @oper_sum = 0, @ssc_saldo_qty = 0

  FETCH stock_srez INTO @ssc_saldo_qty, @ssc_stock_id, @ssc_inv_id, @ssc_subc_id, @ssc_portfolio_id, @acc_id
END -- Цикла WHILE

CLOSE stock_srez

.DEALLOCATE stock_srez

COMMIT TRAN