SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_FactCalcPrepare]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_FactCalcPrepare]
GO
-- =============================================
-- Author:      Vitacore (с)
-- Description: Очистка классификации плана при необходимости с удалением связок за текущий отчётный год
-- =============================================
CREATE PROCEDURE [dbo].[sp_FactCalcPrepare]
  @setting uniqueidentifier
AS
BEGIN

    -- Условие выхода
    if not exists
        (
            select * 
            from IES.T_FACT_CALC_SETTING fcs
            where fcs.FactCalcSettingID = @setting
                    and
                (fcs.PeriodValue = 1 or fcs.PeriodValue = 13)
        )
        return

    TRUNCATE TABLE IES.T_PLAN_CLASSIFICATION

    update sif
    set sif.ItemFact = null
    from IES.T_FACT_CALC_SETTING fcs 
        join IES.T_SCHET_ITEM_FAKT sif on sif.PeriodYear = fcs.PeriodYear
    where fcs.FactCalcSettingID = @setting
            and
        (fcs.PeriodValue = 1 or fcs.PeriodValue = 13)
            and
        sif.ItemFact is not null

    delete f
    from IES.T_ITEM_FACT f
    where not exists
        (
            select *
            from IES.T_SCHET_ITEM_FAKT sif
            where sif.ItemFact = f.ItemFactID
        );

    update ipm
    set ipm.ItemPlan = null
    from IES.T_FACT_CALC_SETTING fcs 
        join IES.T_ITEM_PLAN_MODIFICATION ipm on ipm.PeriodYear = fcs.PeriodYear
    where fcs.FactCalcSettingID = @setting
            and
        (fcs.PeriodValue = 1 or fcs.PeriodValue = 13)
            and
        ipm.ItemPlan is not null

    delete f
    from IES.T_ITEM_PLAN f
    where not exists
        (
            select *
            from IES.T_ITEM_PLAN_MODIFICATION ipm
            where ipm.ItemPlan = f.ItemPlanID
        );

END
