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

/*
  Богаковский 29/06/2004
  Шаблон .MONEY_VAR декларирует переменную в формате DECIMAL(36, 10)
  Параметры вызываемой процедуры ap_CONVERT_sum имеют формат DECIMAL(18, 8)
  Устранить несовместимость форматов можно двумя способами:
  1) В этой процедуре заменить все шаблоны .MONEY_VAR на декларацию DECIMAL(18, 8)
  2) В процедуре ap_CONVERT_sum параметры указать в формате DECIMAL(36, 10)
  сейчас реализован первый вариант, а также часть переменных формата money
  преобразованы в формат DECIMAL(18, 8), а суммы в формат DECIMAL(18, 2)
*/

DECLARE @id NUMERIC

SELECT @id = CONVERT(NUMERIC, @sid)

.GET_PROPS

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 @bank_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
DECLARE @oper_date DATETIME
DECLARE @stock_code VARCHAR(255)
.NAME_VAR(@mess_pr)
DECLARE @ovr_sum_accur_tmp DECIMAL(36, 16)

-- letsko_av
-- 29.03.2005
-- Проводки ПИФ по переоценке
.ID_VAR(@pif_acc_deb_id)
.ID_VAR(@pif_acc_sbor_id)
.ID_VAR(@pif_long_acc_id)
.ID_VAR(@pif_acc_cred_id)
.ID_VAR(@fund_id)
.ID_VAR(@fund_plan_id)
-- 12.04.2005
DECLARE @fund_rate_value DECIMAL(18, 8)
DECLARE @fund_rate_value_pif DECIMAL(18, 8)
DECLARE @ovr_sum_accur_pif DECIMAL(18, 2), @ovr_sum_accur_tmp_pif DECIMAL(18, 2)

DECLARE @type_rate_check_pif INT, @sd42_date_str VARCHAR(255), @cur_rate_date_pif DATETIME, @cur_is_rate_pif INT,
  @cur_stock_rur_pif NUMERIC, @cur_coupon_rur_pif DECIMAL(18, 8), @cur_stock_sum_pif DECIMAL(18, 8),
  @cur_coupon_sum_pif DECIMAL(18, 8)

SELECT @cur_coupon_rur_pif = NULL
SELECT @sd42_date_str = .GET_CONST('PIF_42')
IF @oper_date < CONVERT(DATETIME, @sd42_date_str, 3) OR ISNULL(@sd42_date_str, '') = ''
  SELECT @type_rate_check_pif = 0
ELSE
  SELECT @type_rate_check_pif = 1

SELECT @fund_plan_id = .ARG_PLAN('PIF_ACCOUNT')

-- kashuba_av
-- 24.11.05 (Чтобы у клиентов не использующих ПИФ не искались константы)
#ifdef ALD_UKPIF
-- letsko_av
-- 14.09.2005
-- Изменения для новой справки СЧА
SELECT @pif_acc_sbor_id = .SD_ACCID_FROM_EFFNO(@fund_plan_id, '58.1')
-- 58.11
.ACCID_FROM_CONST2PLAN(@pif_acc_deb_id, 'BACK_ACC_PEREOC_COMIS', @fund_plan_id, 1, 1, 'Счет переоценки по ПИФ', @mess)
-- letsko_av (end)
-- 58.10
.ACCID_FROM_CONST2PLAN(@pif_long_acc_id, 'BACK_ACC_SECUR_MP_LONG_COMIS', @fund_plan_id, 1, 1, 'Счет длинной позиции ЦБ на плане ДС для комиссий', @mess)

-- kashuba_av 16.11.05 (begin)
-- Выбираем счет прибыли убытка для переоценки из константы
.ACCID_FROM_CONST2PLAN(@pif_acc_cred_id, 'BACK_ACC_PROFITLOSS_PEREOC', @fund_plan_id, 1, 1, 'Счет прибыли убытка по переоценке ЦБ', @mess)

-- SELECT @pif_acc_cred_id = .SD_ACCID_FROM_EFFNO(@fund_plan_id, '86.3')
-- kashuba_av 16.11.05 (end)
#endif

SELECT @oper_date = @in_date

BEGIN TRAN

.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

IF (.TEST_SINT_ACCOUNTING(@organisation_id)) BEGIN
  .GET_ORG_PLAN(@organisation_id, 1, @sint_plan_id, @sint_plan_cur_id)
  .ASSIGN(@is_sint, 1)
END
ELSE BEGIN
  .ASSIGN(@is_sint,0)
END

.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 @sec_plan2partner_id NUMERIC
DECLARE @mon_plan2partner_id NUMERIC
DECLARE @pif_plan2partner_id NUMERIC
DECLARE @subc_str_bal VARCHAR(255)

DECLARE @ssc_saldo_qty DECIMAL(28, 8)
DECLARE @ssc_saldo_qty_pif DECIMAL(28, 8)
DECLARE @ssc_saldo DECIMAL(28, 8)
DECLARE @ssc_saldo_accur DECIMAL(28, 8)
DECLARE @ssc_saldo_accur_pif DECIMAL(28, 8)

DECLARE @ssc_inv_id NUMERIC
DECLARE @ssc_subc_id NUMERIC
DECLARE @ssc_portfolio_id NUMERIC
DECLARE @ssc_partion_id NUMERIC
DECLARE @ssc_stock_id NUMERIC
DECLARE @ssc_acc_cur_id NUMERIC
DECLARE @d_subconto_str VARCHAR(255)
DECLARE @k_subconto_str VARCHAR(255)

DECLARE @oper_sum DECIMAL(18, 2)

DECLARE @comment VARCHAR(255)
DECLARE @acc_445_id NUMERIC
DECLARE @acc_unkd_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

.ID_VAR(@em_type_id_afc)
.ID_VAR(@em_class_id)
.ID_VAR(@em_class_value_id)
.ID_VAR(@stock_type_id_afc)
.ID_VAR(@stock_form_id_afc)
.ID_VAR(@portfolio_type_id_afc)

.ID_VAR(@bdo_emmitent_id)
.ID_VAR(@d_cur_id)
.ID_VAR(@c_cur_id)
DECLARE @d_sum DECIMAL(18,2)
DECLARE @c_sum DECIMAL(18,2)

--- Переменные настроек активов
.ID_VAR(@ba_plan_id)
.ID_VAR(@ba_account_id)
.ID_VAR(@ba_pereoc_alg_id)
.ID_VAR(@ba_acc_negative)
.ID_VAR(@ba_acc_positive)
.ID_VAR(@ba_reserve_type_id)
.ID_VAR(@ba_reserve_alg_id)
.ID_VAR(@ba_reserve_acc_id)
.INT_VAR(@ba_is_quoted_asset)

DECLARE @rate DECIMAL(18, 8)

.DATE_VAR(@rate_date)
.ID_VAR(@to_cur_id)

DECLARE @sum_CONVERT DECIMAL(18, 2)

.NAME_VAR(@home_subc_str)
.ID_VAR(@sav_stock_id)
.ID_VAR(@sav_inv_id)
.ID_VAR(@sav_subc_id)

DECLARE @bank_sum DECIMAL(18, 2), @bank_sum_accur DECIMAL(18, 2), @ovr_sum_op DECIMAL(18, 2), @bank_sum_op DECIMAL(18, 2),
  @bal_saldo_sum DECIMAL(18, 2), @ovr_sum DECIMAL(18, 2), @reserve_sum DECIMAL(18, 2), @sum_in_nominal_cur DECIMAL(18, 2)

.ID_VAR(@long_acc_id)
.ID_VAR(@pereoc_acc_id)
.ID_VAR(@deb_acc_id)
.ID_VAR(@cred_acc_id)
.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)
.ID_VAR(@ssb_partion_type_id)
.ID_VAR(@bank_plan2partner)
.INT_VAR(@first_row)
.INT_VAR(@changed)
.NAME_VAR(@stock_kotir)
.NAME_VAR(@err_message)
.ID_VAR(@investor_id)
.ID_VAR(@sale_place_id)
.ID_VAR(@nominal_cur_id)
.DATE_VAR(@last_oper_date)
.DATE_VAR(@close_date)
.MONEY_VAR(@stock_nominal)

DECLARE @ovr_sum_accur DECIMAL(18, 2)

.ACCID_FROM_CONST2PLAN(@long_acc_id, 'BACK_ACC_SECUR_MP_LONG', @plan_money_id, 1, 1, 'Счет ЦБ (длинная позиция)', @mess)
.ACCID_FROM_CONST2PLAN(@pereoc_acc_id, 'BACK_ACC_PEREOC', @plan_money_id, 1, 1, 'Счет переоценки БЭК', @mess)

SELECT @subc_str_bal = ''

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_stock_subc_type_id = .SUBCTYPE_FROM_CODE('STOCK_EMIS')
SELECT @ssb_partion_type_id = .SUBCTYPE_FROM_CODE('DOC_SEC_IN')

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

DECLARE @close_period_id NUMERIC

CREATE TABLE #opers
(
  saldo MONEY,
  saldo_qty MONEY NULL,
  saldo_accur MONEY NULL,
  deb_or_cred INT,
  acc_id NUMERIC,
  acc_cur_id NUMERIC NULL,
  investor_id NUMERIC,
  portfolio_sub_id NUMERIC,
  doc_sec_in_id NUMERIC,
  stock_id NUMERIC
)

SELECT @close_period_id = (
  SELECT
    t_periods.id
  FROM
    t_periods
  WHERE
    t_periods.partner2plan_id = @mon_plan2partner_id
    AND t_periods.period_date = (
      SELECT
        MAX(period_date)
      FROM
        t_periods
      WHERE
        period_date <= @in_date
    )
)

IF @close_period_id IS NOT NULL BEGIN
  SELECT @close_date = (
    SELECT
      CONVERT(DATETIME, CONVERT(VARCHAR(10), period_date, 103), 103)
    FROM
      t_periods
    WHERE
      id = @close_period_id
  )
  -- Входящие остатки
  INSERT
    #opers
  (
    saldo,
    saldo_qty,
    saldo_accur,
    deb_or_cred,
    acc_id,
    acc_cur_id,
    investor_id,
    portfolio_sub_id,
    doc_sec_in_id,
    stock_id
  )
  SELECT
    SUM(ISNULL(t_account_history.saldo, 0)),
    SUM(ISNULL(t_account_history.saldo_qty, 0)),
    SUM(ISNULL(t_account_history.saldo_accur, ISNULL(t_account_history.saldo, 0))),
    t_account_history.deb_or_cred,
    t_account_history.acc_id,
    t_account_history.acc_cur_id,
    inv.item_id,
    portf_sub.item_id,
    doc_sec_in.item_id,
    stock.item_id
  FROM
    t_account_history,
    t_acchistory_subconto inv,
    t_acchistory_subconto portf_sub,
    t_acchistory_subconto doc_sec_in,
    t_acchistory_subconto stock,
    td_stock_emis,
    t_stock_types
  WHERE
    t_account_history.period_id = @close_period_id
    AND t_account_history.acc_id = @long_acc_id
    AND inv.id = t_account_history.id
    AND inv.subconto_type_id = @ssb_inv_subc_type_id
    AND portf_sub.id = t_account_history.id
    AND portf_sub.subconto_type_id = @ssb_invsub_subc_type_id
    AND doc_sec_in.id = t_account_history.id
    AND doc_sec_in.subconto_type_id = @ssb_partion_type_id
    AND stock.id = t_account_history.id
    AND stock.subconto_type_id = @ssb_stock_subc_type_id
    AND stock.item_id = td_stock_emis.id
    AND td_stock_emis.stock_type_id = t_stock_types.id
    AND (
      (@stock_id IS NOT NULL) OR
      (t_stock_types.stock_type_id = ISNULL(@stock_type_id, t_stock_types.stock_type_id))
    )
-- letsko_av
-- 18.02.2005
-- отфильтровывание записей по условиям групповой операции
    AND ISNULL(stock.item_id, -1) = ISNULL(ISNULL(@stock_id, stock.item_id), -1)
    AND ISNULL(portf_sub.item_id, -1) = ISNULL(ISNULL(@portfolio_id, portf_sub.item_id), -1)
    AND (
      -- переоценка только собственных активов
      ((@investor_type_id = 1) AND (inv.item_id = @organisation_id)) OR
      -- переоценка всех клиентских активов
      ((@investor_type_id = 2) AND (inv.item_id <> @organisation_id)) OR
      -- переоценка для выбранного клиента
      ((@investor_type_id = 3) AND (inv.item_id = @investor_id_form)) OR
      -- переоценка собственных и клиентских активов
      (@investor_type_id = 4)
    )
-- letsko_av (end)
-- letsko_av
-- 12.04.2005
-- бумага должна существовать !
    AND .ITEMS_EXISTS_BY_STOCK(stock.item_id)
-- letsko_av (end)
  GROUP BY
    deb_or_cred,
    acc_id,
    acc_cur_id,
    inv.item_id,
    portf_sub.item_id,
    doc_sec_in.item_id,
    stock.item_id
END

IF @close_period_id IS NULL
  SELECT @close_date = '01/01/1800'

INSERT
  #opers
(
  saldo,
  saldo_qty,
  saldo_accur,
  deb_or_cred,
  acc_id,
  acc_cur_id,
  investor_id,
  portfolio_sub_id,
  doc_sec_in_id,
  stock_id
)
SELECT
  SUM(ISNULL(sum_bal, 0)),
  SUM(ISNULL(qty_sum, 0)),
  SUM(ISNULL(sum_accur, ISNULL(sum_bal, 0))),
  deb_or_cred,
  acc_id,
  acc_cur_id,
  inv.item_id,
  portf_sub.item_id,
  doc_sec_in.item_id,
  stock.item_id
FROM
  t_oper_list,
  t_oper_subconto inv,
  t_oper_subconto portf_sub,
  t_oper_subconto doc_sec_in,
  t_oper_subconto stock,
  td_stock_emis,t_stock_types
WHERE
  t_oper_list.partner2plan_id = @mon_plan2partner_id
  AND t_oper_list.acc_id = @long_acc_id
  AND t_oper_list.op_date >= @close_date
  AND t_oper_list.op_date < DATEADD(dd, 1, @in_date)
  AND inv.id = t_oper_list.id
  AND inv.subconto_type_id = @ssb_inv_subc_type_id
  AND portf_sub.id = t_oper_list.id
  AND portf_sub.subconto_type_id = @ssb_invsub_subc_type_id
  AND doc_sec_in.id = t_oper_list.id
  AND doc_sec_in.subconto_type_id = @ssb_partion_type_id
  AND stock.id = t_oper_list.id
  AND stock.subconto_type_id = @ssb_stock_subc_type_id
  AND stock.item_id = td_stock_emis.id
  AND td_stock_emis.stock_type_id = t_stock_types.id
  AND (@stock_id IS NOT NULL OR t_stock_types.stock_type_id = ISNULL(@stock_type_id, t_stock_types.stock_type_id))
-- letsko_av
-- 18.02.2005
-- отфильтровывание записей по условиям групповой операции
  AND ISNULL(stock.item_id, -1) = ISNULL(ISNULL(@stock_id, stock.item_id), -1)
  AND ISNULL(portf_sub.item_id, -1) = ISNULL(ISNULL(@portfolio_id, portf_sub.item_id), -1)
  AND (
    -- переоценка только собственных активов
    ((@investor_type_id = 1) AND (inv.item_id = @organisation_id)) OR
    -- переоценка всех клиентских активов
    ((@investor_type_id = 2) AND (inv.item_id <> @organisation_id)) OR
    -- переоценка для выбранного клиента
    ((@investor_type_id = 3) AND (inv.item_id = @investor_id_form)) OR
    -- переоценка собственных и клиентских активов
    (@investor_type_id = 4)
  )
-- letsko_av (end)
-- letsko_av
-- 12.04.2005
-- бумага должна существовать !
    AND .ITEMS_EXISTS_BY_STOCK(stock.item_id)
-- letsko_av (end)
  GROUP BY
    deb_or_cred,
    acc_id,
    acc_cur_id,
    inv.item_id,
    portf_sub.item_id,
    doc_sec_in.item_id,
    stock.item_id

--SELECT @mon_plan2partner_id AS mon_plan2partner_id, @long_acc_id AS long_acc_id, @close_date AS close_date,
--  @in_date AS in_date, @ssb_inv_subc_type_id AS ssb_inv_subc_type_id, @ssb_invsub_subc_type_id AS ssb_invsub_subc_type_id,
--  @ssb_partion_type_id AS ssb_partion_type_id, @ssb_stock_subc_type_id AS ssb_stock_subc_type_id,
--  @stock_type_id AS stock_type_id, @stock_id AS stock_id, @portfolio_id AS portfolio_id,
--  @investor_type_id AS investor_type_id, @organisation_id AS organisation_id,
--  @investor_id_form AS investor_id_form

--select * from #opers

DECLARE stock_srez CURSOR LOCAL FORWARD_ONLY STATIC READ_ONLY FOR
  SELECT
    SUM(saldo * deb_or_cred),
    SUM(saldo_qty * deb_or_cred),
    SUM(saldo_accur * deb_or_cred),
    stock_id,
    investor_id,
    portfolio_sub_id,
    doc_sec_in_id,
    acc_id,
    acc_cur_id
  FROM
    #opers
  GROUP BY
    stock_id,
    investor_id,
    portfolio_sub_id,
    doc_sec_in_id,
    acc_id,
    acc_cur_id
  HAVING
    SUM(saldo_qty * deb_or_cred) > 0
  ORDER BY
    stock_id,
    investor_id,
    portfolio_sub_id

SELECT @home_subc_str = ''

SELECT @bal_saldo_sum =  0
SELECT @bank_sum = 0
SELECT @bank_sum_accur = 0

OPEN stock_srez

FETCH stock_srez INTO @ssc_saldo, @ssc_saldo_qty, @ssc_saldo_accur, @ssc_stock_id, @ssc_inv_id, @ssc_subc_id, @ssc_partion_id, @acc_id, @ssc_acc_cur_id

.ASSIGN(@first_row, 1)

WHILE (.CURSOR_STATE = 0) BEGIN

--SELECT 'letsko_av_debug', 'ap_pereoc_exec_oper', @ssc_saldo AzS ssc_saldo, @ssc_saldo_qty AS ssc_saldo_qty,
--  @ssc_saldo_accur AS ssc_saldo_accur, @ssc_stock_id AS ssc_stock_id, @ssc_inv_id AS ssc_inv_id,
--  @ssc_subc_id AS ssc_subc_id, @ssc_partion_id AS ssc_partion_id, @acc_id AS acc_id, @ssc_acc_cur_id AS ssc_acc_cur_id

  -- Богаковский
  -- 01/02/2005
  -- добавил переход к следующему циклу (следующей записи курсора), если валюта остатка не найдена
  IF ISNULL(@ssc_acc_cur_id, 0) = 0 BEGIN
    FETCH stock_srez INTO @ssc_saldo, @ssc_saldo_qty, @ssc_saldo_accur, @ssc_stock_id, @ssc_inv_id, @ssc_subc_id, @ssc_partion_id, @acc_id, @ssc_acc_cur_id
    CONTINUE
  END

  SELECT
    @stock_type_id = t_stock_types.stock_type_id
  FROM
    t_stock_types,
    td_stock_emis
  WHERE
    td_stock_emis.id = @ssc_stock_id
    AND td_stock_emis.stock_type_id = t_stock_types.id

  -- 1 - Акция
  -- 2 - Облигация
  -- 3 - Вексель
  IF @stock_type_id = 3 BEGIN
    IF EXISTS(
      SELECT
        1
      FROM
        t_oper_list,
        t_oper_subconto stock,
        t_oper_subconto inv,
        t_oper_subconto portf_sub
      WHERE
        partner2plan_id = @mon_plan2partner_id
        AND acc_id = @pereoc_acc_id
        AND stock.id = t_oper_list.id
        AND stock.item_id = @ssc_stock_id
        AND inv.id = t_oper_list.id
        AND inv.item_id = @ssc_inv_id
        AND portf_sub.id = t_oper_list.id
        AND portf_sub.item_id = @ssc_subc_id
    ) BEGIN
      SELECT
        @last_oper_date = MAX(CONVERT(DATETIME, CONVERT(VARCHAR, op_date, 3), 3))
      FROM
        t_oper_list,
        t_oper_subconto stock,
        t_oper_subconto inv,
        t_oper_subconto portf_sub
      WHERE
        partner2plan_id = @mon_plan2partner_id
        AND acc_id = @long_acc_id
        AND stock.id = t_oper_list.id
        AND stock.item_id = @ssc_stock_id
        AND inv.id = t_oper_list.id
        AND inv.item_id = @ssc_inv_id
        AND portf_sub.id = t_oper_list.id
        AND portf_sub.item_id = @ssc_subc_id
    END -- (IF EXISTS()
    ELSE BEGIN
      SELECT
        @last_oper_date = emiss_statereg_date
      FROM
        td_stock_emis
      WHERE
        id = @ssc_stock_id
    END -- NOT(IF EXISTS()
  END -- (@stock_type_id = 3)

  IF @is_sint IN (0, 1)
    SELECT @ba_pereoc_alg_id = 1

  --- Алгоритмы переоценки в простом справочнике 7050
  --    0 - Не считать
  --    1 - По рыночной котировке
  --    2 - По валюте сделки покупки
  --    3 - По валюте номинала

  -- если ничего не нашел запрос, то д. б. валюта номинала ЦБ
  IF ISNULL(@ssc_acc_cur_id, -1) = -1
    SELECT @ssc_acc_cur_id = (
      SELECT
        stock_curr_id
      FROM
        td_stock_emis
      WHERE
        id = @ssc_stock_id
    )
  IF @ssc_acc_cur_id IS NULL
    .EXIT_MESSAGE('Невозможно определить валюту ЦБ!')

  IF @ba_pereoc_alg_id IN (1, 2, 3) BEGIN
    -- Переоценка по рыночной котировке всех бумаг, кроме векселей
    IF @ba_pereoc_alg_id = 1 AND @stock_type_id <> 3 BEGIN
      SELECT @mess = 'Переоценка ЦБ по рыночной котировке'

      IF @rate_place_id IS NULL
        .GET_STOCK_RATE(@ssc_stock_id, @oper_date, @owner_id, @ssc_acc_cur_id, @rate, @cotir_type_id)
      ELSE BEGIN
        .GET_STOCK_RATE2PLACE(@ssc_stock_id, @oper_date, @owner_id, @ssc_acc_cur_id, @rate_place_id, @rate, @cotir_type_id)
      END

-- letsko_av
-- 12.04.2005
-- Берем для проводок ПИФа котировку не рыночную, а признаваемую
#ifdef ALD_UKPIF
  IF .TEST_PIF_CLIENT(@ssc_inv_id) BEGIN
    SELECT
      @fund_id = client_id
    FROM
      tb_contracts
    WHERE
      id = @ssc_inv_id

-- 09/03/2006
-- Другой шаблон который умеет работать с площадками
--      .PIF_RATE_CHECK(@ssc_stock_id, @fund_id, @rate_place_id, @oper_date, @type_rate_check_pif, @ssc_saldo_qty, @cur_rate_date_pif, @cur_is_rate_pif, @fund_rate_value_pif, @cur_coupon_rur_pif,
--        @cur_stock_sum_pif, @cur_coupon_sum_pif, @cur_stock_rur_pif)
      .PIF_CALC_STOCK_PRICE(@ssc_stock_id, @fund_id, @rate_place_id, @oper_date, @ssc_saldo_qty, @cur_rate_date_pif, @cur_is_rate_pif, @fund_rate_value_pif, @cur_coupon_rur_pif,
        @cur_stock_sum_pif, @cur_coupon_sum_pif)

--SELECT @ssc_stock_id AS ssc_stock_id, @fund_id AS fund_id, @rate_place_id AS rate_place_id, @oper_date AS oper_date,
--  @ssc_saldo_qty AS ssc_saldo_qty, @cur_rate_date_pif AS cur_rate_date_pif, @cur_is_rate_pif AS cur_is_rate_pif,
--  @fund_rate_value_pif AS fund_rate_value_pif, @cur_coupon_rur_pif AS cur_coupon_rur_pif, @cur_stock_sum_pif AS cur_stock_sum_pif,
--  @cur_coupon_sum_pif AS cur_coupon_sum_pif

--      .SALDO_FAST(@fund_id, @fund_plan_id, @oper_date, @pif_acc_sbor_id, D, Y, 'STOCK_EMIS', @ssc_stock_id, 'DOC_SEC_IN', @ssc_partion_id, @ssc_saldo_accur_pif, @ssc_saldo_qty_pif)
      DECLARE @my_analyt VARCHAR(255)
      SELECT @my_analyt = ''
      .SUBC_STR_ADD(@my_analyt, 'STOCK_EMIS', @ssc_stock_id)
      .SUBC_STR_ADD(@my_analyt, 'DOC_SEC_IN', @ssc_partion_id)
      .SALDO(@fund_id, @fund_plan_id, @oper_date, @pif_acc_sbor_id, @my_analyt, Y, D, @ssc_saldo_accur_pif, @ssc_saldo_qty_pif)

--select @ssc_inv_id AS ssc_inv_id, @fund_id AS fund_id, @fund_plan_id AS fund_plan_id, @oper_date AS oper_date, @pif_acc_sbor_id AS pif_acc_sbor_id,
--  @my_analyt AS my_analyt, @ssc_saldo_accur_pif AS ssc_saldo_accur_pif, @ssc_saldo_qty_pif AS ssc_saldo_qty_pif

      DECLARE @ssc_long_sum_pif DECIMAL(28, 8), @ssc_long_qty_pif DECIMAL(28, 8)
      .SALDO_FAST(@fund_id, @fund_plan_id, @oper_date, @pif_long_acc_id, D, Y, 'STOCK_EMIS', @ssc_stock_id, 'DOC_SEC_IN', @ssc_partion_id, @ssc_long_sum_pif, @ssc_long_qty_pif)

      DECLARE @ssc_deb_sum_pif DECIMAL(28, 8), @ssc_deb_qty_pif DECIMAL(28, 8)
      .SALDO_FAST(@fund_id, @fund_plan_id, @oper_date, @pif_acc_deb_id, D, Y, 'STOCK_EMIS', @ssc_stock_id, 'DOC_SEC_IN', @ssc_partion_id, @ssc_deb_sum_pif, @ssc_deb_qty_pif)

      SELECT @ovr_sum_accur_tmp_pif = ISNULL(ROUND((@ssc_saldo_qty_pif * @fund_rate_value_pif), 2), 0) - ISNULL(@ssc_saldo_accur_pif, 0)
      SELECT @ovr_sum_accur_tmp_pif = @ovr_sum_accur_tmp_pif - ISNULL(@ssc_long_sum_pif, 0)
      SELECT @ovr_sum_accur_tmp_pif = @ovr_sum_accur_tmp_pif - ISNULL(@ssc_deb_sum_pif, 0)
    END

    IF @ba_pereoc_alg_id = 1 BEGIN
      SELECT @ovr_sum_accur_pif = CONVERT(DECIMAL(18, 2), SUBSTRING(
        CONVERT(VARCHAR(100), @ovr_sum_accur_tmp_pif),
        1,
        PATINDEX('%.%', CONVERT(VARCHAR(100), @ovr_sum_accur_tmp_pif)) + 3
      ))
      IF @ovr_sum_accur_pif IS NOT NULL
        .CONVERT_SUM_DEC(@ssc_acc_cur_id, @money_plan_cur_id, @oper_date, 0, @ovr_sum_accur_pif, @ovr_sum, @fund_rate_value, @rate_date)
    END
#endif
-- letsko_av (end)

      IF @cotir_type_id = 3 BEGIN
        select @rate = @fund_rate_value_pif
      END
      ELSE
        select @fund_rate_value_pif = @rate

      IF (ISNULL(@rate, 0) = 0) OR (@cur_is_rate_pif = 0) BEGIN
        FETCH stock_srez INTO @ssc_saldo, @ssc_saldo_qty, @ssc_saldo_accur, @ssc_stock_id, @ssc_inv_id, @ssc_subc_id, @ssc_partion_id, @acc_id, @ssc_acc_cur_id
        CONTINUE
      END -- (ISNULL(@rate, 0) = 0)

--SELECT @ovr_sum_accur_tmp AS ovr_sum_accur_tmp, @ssc_saldo_qty AS ssc_saldo_qty,
--  @rate AS rate, @ssc_saldo_accur AS ssc_saldo_accur

      SELECT @ovr_sum_accur_tmp = ISNULL(ROUND((@ssc_saldo_qty * @rate), 2), 0) - ISNULL(@ssc_saldo_accur, 0)
    END -- (@ba_pereoc_alg_id = 1 AND @stock_type_id <> 3)

    IF @ba_pereoc_alg_id = 1 AND @stock_type_id = 3 BEGIN
      SELECT
        @stock_nominal = stock_nominal,
        @close_date = stock_cancel_date
      FROM
        td_stock_emis
      WHERE
        id = @ssc_stock_id

--      IF @stock_nominal IS NULL
--        .EXIT_MESSAGE('Не задан номинал векселя')
      IF ISNULL(@rate, 0) = 0 BEGIN
        FETCH stock_srez INTO @ssc_saldo, @ssc_saldo_qty, @ssc_saldo_accur, @ssc_stock_id, @ssc_inv_id, @ssc_subc_id, @ssc_partion_id, @acc_id, @ssc_acc_cur_id
        CONTINUE
      END -- (ISNULL(@rate, 0) = 0)

      SELECT @ovr_sum_accur_tmp = ((@stock_nominal - @ssc_saldo_accur) *
        DATEDIFF(DAY, @in_date, @last_oper_date)) /
        DATEDIFF(DAY, @close_date, @last_oper_date) + @ssc_saldo_accur
    END -- (@ba_pereoc_alg_id = 1 AND @stock_type_id = 3)

    IF @ba_pereoc_alg_id = 1 BEGIN
      SELECT @ovr_sum_accur = CONVERT(DECIMAL(18,2), SUBSTRING(
        CONVERT(VARCHAR(100), @ovr_sum_accur_tmp),
        1,
        PATINDEX('%.%', CONVERT(VARCHAR(100), @ovr_sum_accur_tmp)) + 3
      ))
      IF @ovr_sum_accur IS NOT NULL
        .CONVERT_SUM_DEC(@ssc_acc_cur_id, @money_plan_cur_id, @oper_date, 0, @ovr_sum_accur, @ovr_sum, @rate, @rate_date)
    END -- (@ba_pereoc_alg_id = 1)

    -- Переоценка по валюте сделки или номинала
    IF @ba_pereoc_alg_id IN (2, 3) BEGIN
      -- По сделке поступления
      IF @ba_pereoc_alg_id = 2 BEGIN
        SELECT @mess = 'Переоценка ЦБ по валюте поступления'
        SELECT
          @to_cur_id = cur_id
        FROM
          tb_baysale_docitems
        WHERE
          id = @ssc_partion_id
      END -- (@ba_pereoc_alg_id = 2)
      ELSE BEGIN
        SELECT @mess = 'Переоценка ЦБ по валюте номинала'
        SELECT
          @to_cur_id = stock_curr_id
        FROM
          td_stock_emis
        WHERE
          id = @ssc_stock_id
      END -- NOT(@ba_pereoc_alg_id = 2)

      IF @ssc_saldo_accur IS NOT NULL
        .CONVERT_SUM_DEC(@money_plan_cur_id, @to_cur_id, @oper_date, 0, @ssc_saldo_accur, @sum_CONVERT, @rate, @rate_date)
      SELECT @ovr_sum = ISNULL(ROUND(@sum_CONVERT, 2), 0) - ISNULL(@ssc_saldo, 0)
    END -- (@ba_pereoc_alg_id IN (2,3))

    .SUBC_STR_ADD(@home_subc_str, 'STOCK_EMIS', @ssc_stock_id)
    .SUBC_STR_ADD(@home_subc_str, 'INVESTORS', @ssc_inv_id)
    .SUBC_STR_ADD(@home_subc_str, 'INVESTOR2PORTFOLIO', @ssc_subc_id)
    .SUBC_STR_ADD(@home_subc_str, 'DOC_SEC', @id)
    .SUBC_STR_ADD(@home_subc_str, 'DOC_SEC_IN', @ssc_partion_id)

    IF @ovr_sum > 0 BEGIN
      SELECT @deb_acc_id = @long_acc_id
      SELECT @cred_acc_id = @pereoc_acc_id
      SELECT @ovr_sum_op = @ovr_sum
      SELECT @mess_pr = @mess + ' : положительная'
      SELECT @d_cur_id = @ssc_acc_cur_id
      SELECT @c_cur_id = @money_plan_cur_id
      SELECT @d_sum = @ovr_sum_accur
      SELECT @c_sum = NULL
    END -- (@ovr_sum > 0)
    ELSE BEGIN
      SELECT @ovr_sum_op = ABS(@ovr_sum)
      SELECT @cred_acc_id = @long_acc_id
      SELECT @deb_acc_id = @pereoc_acc_id
      SELECT @mess_pr = @mess + ' : отрицательная'
      SELECT @d_cur_id = @money_plan_cur_id
      SELECT @c_cur_id = @ssc_acc_cur_id
      SELECT @d_sum = NULL
      SELECT @c_sum = ABS(@ovr_sum_accur)
    END -- NOT(@ovr_sum > 0)

    IF .TEST_PIF_CLIENT(@ssc_inv_id) OR (@cotir_type_id <> 3) BEGIN
      -- если это пиф или если котировка не признаваемая - делаем проводку, иначе нет
      .OPERATION_SBC(@owner_id, @plan_money_id, @oper_date, @deb_acc_id, @cred_acc_id, @ovr_sum_op, @money_plan_cur_id, @oper_date, NULL, NULL, @mess_pr, Y, @operation_id, NULL, NULL, @action_history_id,
        @d_sum, @c_sum, @home_subc_str, @home_subc_str, @d_cur_id, @c_cur_id)
    END

    SELECT @nominal_cur_id = (
      SELECT
        stock_curr_id
      FROM
        td_stock_emis
      WHERE
        id = @ssc_stock_id
    )

    -- Начало: Богаковский 30/06/2004, сообщение "Отсутствует валюта номинала у ЦБ"
    IF @nominal_cur_id IS NULL BEGIN
      SELECT @stock_code = (
        SELECT
          stock_code
        FROM
          td_stock_emis
        WHERE
          id = @ssc_stock_id
      )
      .EXIT_MESSAGE_PARM('Отсутствует валюта номинала у ЦБ с кодом', @stock_code)
    END

    -- Богаковский 05/07/2004 Поменял имя шаблона
    IF @ovr_sum_accur IS NOT NULL
      .CONVERT_SUM_DEC(@ssc_acc_cur_id, @nominal_cur_id, @oper_date, 0, @ovr_sum_accur, @sum_in_nominal_cur, @rate, @rate_date)

    SELECT @bank_sum_accur = ISNULL(@bank_sum_accur, 0) + ISNULL(@ovr_sum_accur, 0)
    SELECT @bal_saldo_sum = @bal_saldo_sum + ISNULL(@ssc_saldo, 0)
  END -- (@ba_pereoc_alg_id IN (1, 2, 3))

-- letsko_av
-- 29.03.2005
-- Проводки ПИФ по переоценке
#ifdef ALD_UKPIF
  IF .TEST_PIF_CLIENT(@ssc_inv_id) BEGIN
    SELECT
      @fund_id = client_id
    FROM
      tb_contracts
    WHERE
      id = @ssc_inv_id
-- letsko_av
-- 20.04.2005
-- Если нет признаваемой котировки - и не делаем ничего !
    IF ISNULL(@fund_rate_value_pif, 0) = 0 BEGIN
      FETCH stock_srez INTO @ssc_saldo, @ssc_saldo_qty, @ssc_saldo_accur, @ssc_stock_id, @ssc_inv_id, @ssc_subc_id, @ssc_partion_id, @acc_id, @ssc_acc_cur_id
      CONTINUE
    END
-- letsko_av (end)

/*
    IF @ovr_sum > 0 BEGIN
      SELECT @mess_pr = 'Переоценка ЦБ по признаваемой котировке : положительная'
      SELECT @d_sum = @ovr_sum_accur_pif
      SELECT @c_sum = NULL
    END -- (@ovr_sum > 0)
    ELSE BEGIN
      SELECT @mess_pr = 'Переоценка ЦБ по признаваемой котировке : отрицательная'
      SELECT @d_sum = NULL
      SELECT @c_sum = ABS(@ovr_sum_accur_pif)
    END -- NOT(@ovr_sum > 0)

    SELECT @d_sum = ISNULL(@d_sum, 0)
    SELECT @c_sum = ISNULL(@c_sum, 0)
    DECLARE @msld MONEY

    IF @d_sum < @c_sum BEGIN
      SELECT @msld = @c_sum - @d_sum
*/

    IF @ovr_sum_accur_tmp_pif > 0 BEGIN
      SELECT @mess_pr = 'Переоценка ЦБ по признаваемой котировке : положительная'
      SELECT @d_sum = @ovr_sum_accur_tmp_pif
      SELECT @c_sum = 0
    END -- (@ovr_sum > 0)
    ELSE BEGIN
      SELECT @mess_pr = 'Переоценка ЦБ по признаваемой котировке : отрицательная'
      SELECT @d_sum = 0
      SELECT @c_sum = ABS(@ovr_sum_accur_tmp_pif)
    END -- NOT(@ovr_sum > 0)

    SELECT @ovr_sum_accur_tmp_pif = ROUND(@ovr_sum_accur_tmp_pif, 2)

IF @ovr_sum_accur_tmp_pif < 0 BEGIN
-- letsko_av
-- 05.04.2005
-- Саша Федотов сказал что в проводках по ПИФ-у нет количества
--      .OPERATION(@fund_id, @fund_plan_id, @oper_date, @pif_acc_cred_id, @pif_acc_deb_id, @msld, 'RUR', @oper_date, @ssc_saldo_qty, @ssc_saldo_qty, @mess_pr, N, @operation_id, @id, @action_id, @action_history_id, @d_sum, @c_sum)

--select '1', @msld AS msld

      SELECT @ovr_sum_accur_tmp_pif = @ovr_sum_accur_tmp_pif * -1

--      .OPERATION(@fund_id, @fund_plan_id, @oper_date, @pif_acc_cred_id, @pif_acc_deb_id, @msld, 'RUR', @oper_date, 0, 0, @mess_pr, N, @operation_id, @id, @action_id, @action_history_id, @d_sum, @c_sum)
      .OPERATION(@fund_id, @fund_plan_id, @oper_date, @pif_acc_cred_id, @pif_acc_deb_id, @ovr_sum_accur_tmp_pif, 'RUR', @oper_date, 0, 0, @mess_pr, N, @operation_id, @id, @action_id, @action_history_id, @d_sum, @c_sum)
      .UPDATE_SUBC(C, 'stock_emis', @ssc_stock_id)
      .UPDATE_SUBC(C, 'doc_sec_in', @ssc_partion_id)
      .SUBC_CHECK
    END
    ELSE BEGIN
--      SELECT @msld = @d_sum - @c_sum

-- letsko_av
-- 05.04.2005
-- Саша Федотов сказал что в проводках по ПИФ-у нет количества
--      .OPERATION(@fund_id, @fund_plan_id, @oper_date, @pif_acc_deb_id, @pif_acc_cred_id, @msld, 'RUR', @oper_date, @ssc_saldo_qty, @ssc_saldo_qty, @mess_pr, N, @operation_id, @id, @action_id, @action_history_id, @c_sum, @d_sum)

--select '2', @msld AS msld

--      .OPERATION(@fund_id, @fund_plan_id, @oper_date, @pif_acc_deb_id, @pif_acc_cred_id, @msld, 'RUR', @oper_date, 0, 0, @mess_pr, N, @operation_id, @id, @action_id, @action_history_id, @c_sum, @d_sum)
      .OPERATION(@fund_id, @fund_plan_id, @oper_date, @pif_acc_deb_id, @pif_acc_cred_id, @ovr_sum_accur_tmp_pif, 'RUR', @oper_date, 0, 0, @mess_pr, N, @operation_id, @id, @action_id, @action_history_id, @c_sum, @d_sum)
      .UPDATE_SUBC(D, 'stock_emis', @ssc_stock_id)
      .UPDATE_SUBC(D, 'doc_sec_in', @ssc_partion_id)
      .SUBC_CHECK
    END
  END
#endif
-- letsko_av (end)

  IF @first_row = 1
    .ASSIGN(@first_row, 0)

  SELECT @stock_type_id = NULL

  FETCH stock_srez INTO @ssc_saldo, @ssc_saldo_qty, @ssc_saldo_accur, @ssc_stock_id, @ssc_inv_id, @ssc_subc_id, @ssc_partion_id, @acc_id, @ssc_acc_cur_id
END -- (WHILE (.CURSOR_STATE = 0))

CLOSE stock_srez
.DEALLOCATE stock_srez

DROP TABLE #opers

COMMIT TRAN