IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'dbo.[GetOwnDepLink]') AND xtype in (N'FN', N'IF', N'TF'))
DROP FUNCTION dbo.[GetOwnDepLink]
GO

CREATE   FUNCTION [GetOwnDepLink]()
RETURNS int
AS
BEGIN
RETURN (SELECT TOP 1 LINK FROM dbo.DEPARTMENTS WHERE TYPE = 4)
END

GO

GRANT EXECUTE ON dbo.[GetOwnDepLink] TO PUBLIC
GO

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GET_ALL_LINK_TO_OBJECT_VAR]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[GET_ALL_LINK_TO_OBJECT_VAR]
GO

CREATE FUNCTION GET_ALL_LINK_TO_OBJECT_VAR (@LINK INT)
RETURNS @RESULT TABLE(OBJECT_VAR_LINK INT)
AS
BEGIN
	INSERT @RESULT VALUES (@LINK)
	DECLARE CUR_CHILD CURSOR FOR
	SELECT	OV.LINK
	FROM	OBJECT_VAR OV
	WHERE	LINK_SELF = @LINK

	DECLARE @OBJECT_VAR_LINK INT
	OPEN CUR_CHILD
	FETCH NEXT FROM CUR_CHILD INTO @OBJECT_VAR_LINK
	WHILE @@FETCH_STATUS = 0
	BEGIN
		INSERT INTO @RESULT
		SELECT OBJECT_VAR_LINK FROM DBO.GET_ALL_LINK_TO_OBJECT_VAR(@OBJECT_VAR_LINK)
		FETCH NEXT FROM CUR_CHILD INTO @OBJECT_VAR_LINK
	END
	CLOSE CUR_CHILD
	DEALLOCATE CUR_CHILD
RETURN
END

GO


SELECT LINK INTO #OBJECT_VAR_YAKUT FROM OBJECT_VAR
WHERE CODE IN ('YAK_DANNYE_NAGRAD','YAK_INF_CHISL_SOST_SMEN_FINORG','YAK_INF_CHISL_SOST_SMEN_FKU','INFO_FKU_ISP_TREB_ZAKON',
'YAK_KACH_SOST_RUK_SPEC','YAK_OTCHET_INCQUAL_PROFP','CHIS_ZABRON_VOEN','SVOD_PROH_KURS_KAVL','YAK_SPISOK_RAB_FKU',
'SPIS_RAB_INCQUAL','SPISOK_SPEC_PRIN_FKU','SPISOK_SPEC_UVOL_FKU')
DECLARE @OBJECT_VAR_YAKUT_LINK INT
WHILE EXISTS(SELECT * FROM #OBJECT_VAR_YAKUT) BEGIN
	SELECT TOP 1 @OBJECT_VAR_YAKUT_LINK = LINK FROM #OBJECT_VAR_YAKUT
	UPDATE OBJECT_VAR SET SYS = 0 WHERE LINK IN (SELECT * FROM DBO.get_all_link_to_object_var(@OBJECT_VAR_YAKUT_LINK))
	DELETE FROM #OBJECT_VAR_YAKUT WHERE LINK = @OBJECT_VAR_YAKUT_LINK
END
DROP TABLE #OBJECT_VAR_YAKUT
GO

update dbo.s_navigator
set sys = 0
where code in ('KADRY_YAKUT_REP_CATALOG',
'YAK_OTCHET_INCQUAL_PROFP_NAVIGATOR2',
'YAK_DANNYE_NAGRAD_NAVIGATOR',
'YAK_INF_CHISL_SOST_SMEN_FINORG_NAVIGATOR',
'YAK_INF_CHISL_SOST_SMEN_FKU_NAVIGATOR',
'YAK_REP_3',
'YAK_REP_2',
'YAK_REP_6',
'YAK_REP_7',
'YAK_REP_1',
'YAK_REP_8',
'YAK_REP_4',
'YAK_REP_5'
)
GO



IF exists(select * from sysobjects where id = object_id('[dbo].[SA_SOBJ_LOST]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
	DROP TABLE [dbo].[SA_SOBJ_LOST]
GO

CREATE TABLE [dbo].[SA_SOBJ_LOST] (
	[LINK] int NULL ,
	[CODE] varchar(254) NULL )
GO

INSERT INTO [dbo].[SA_SOBJ_LOST] (LINK, CODE)
SELECT LINK, CODE 
FROM dbo.S_OBJECTS 
WHERE 
	CODE = 'DOCUMENTS_ADD_MORE'
	OR
	CODE = 'U_TABELS'
	OR
	CODE = 'U_TABELS_MES'
	OR
	CODE = 'DOC_ADD_SUBST'
	OR
	CODE = 'DOC_ADD_COMB'
	OR
	CODE = 'DOCUMENTS_ADD_DEZH'
	OR
	CODE = 'EMPLS_WP'	
GO


--Добавление колонки LINK_EKR таблицы SDOC_ESN_TABLE
IF COLUMNPROPERTY(object_id('[dbo].[SDOC_ESN_TABLE]'), 'LINK_EKR', 'AllowsNull') is NULL
   ALTER TABLE [dbo].[SDOC_ESN_TABLE] ADD LINK_EKR INT NULL
GO

ALTER TABLE [dbo].[SDOC_ESN_TABLE] DISABLE TRIGGER ALL
UPDATE [dbo].[SDOC_ESN_TABLE] SET LINK_EKR = (SELECT TOP 1 LINK FROM dbo.KOSGU WHERE CODE = '211') WHERE LINK_EKR IS NULL
ALTER TABLE [dbo].[SDOC_ESN_TABLE] ENABLE TRIGGER ALL
GO

--Измение колонки LINK_EKR таблицы SDOC_ESN_TABLE
ALTER TABLE [dbo].[SDOC_ESN_TABLE] ALTER COLUMN [LINK_EKR] int NOT NULL 
GO

IF NOT EXISTS (SELECT * FROM dbo.sysconstraints WHERE constid =  object_id('dbo.[FK_SDOC_ESN_TABLE_KOSGU]'))
ALTER TABLE [dbo].[SDOC_ESN_TABLE] 
ADD CONSTRAINT [FK_SDOC_ESN_TABLE_KOSGU] FOREIGN KEY ([LINK_EKR]) REFERENCES [dbo].[KOSGU] ([LINK])
GO

GO

IF COLUMNPROPERTY(object_id('[dbo].[TAX_DEDUCTIONS]'), 'LINK_DEP', 'AllowsNull') is NULL
   ALTER TABLE [dbo].[TAX_DEDUCTIONS] ADD [LINK_DEP] int NULL 
GO

IF  NOT EXISTS (SELECT * FROM dbo.sysconstraints WHERE constid =  object_id('dbo.[FK_TAX_DEDUCTIONS_DEPARTMENTS]'))
ALTER TABLE [dbo].[SDOC] 
ADD CONSTRAINT [FK_TAX_DEDUCTIONS_DEPARTMENTS] FOREIGN KEY ([LINK_DEP]) REFERENCES [dbo].[DEPARTMENTS] ([LINK])
GO

UPDATE A
SET A.LINK_DEP = (SELECT MIN(DEP_LINK) FROM dbo.EMPL E WHERE E.PERS_LINK = A.LINK_PERSON)
FROM dbo.TAX_DEDUCTIONS A
WHERE A.LINK_DEP IS NULL
GO

DELETE FROM dbo.TAX_DEDUCTIONS WHERE LINK_DEP IS NULL
GO

IF COLUMNPROPERTY(object_id('[dbo].[TAX_DEDUCTIONS]'), 'LINK_DEP', 'AllowsNull') is NOT NULL
   ALTER TABLE [dbo].[TAX_DEDUCTIONS] ALTER COLUMN [LINK_DEP] int NOT NULL 
GO

IF exists(select * from sysobjects 
	where id = object_id('[dbo].[S_OBJECTS_U]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [dbo].S_OBJECTS_U
GO

IF exists(select * from sysobjects 
	where id = object_id('[dbo].[S_DBObjects_U]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [dbo].S_DBObjects_U
GO

IF exists(select * from sysobjects 
	where id = object_id('[dbo].[OBJECT_VAR_U]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [dbo].OBJECT_VAR_U
GO

IF exists(select * from sysobjects 
	where id = object_id('[dbo].[S_NAVIGATOR_U]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [dbo].S_NAVIGATOR_U
GO

IF exists(select * from sysobjects 
	where id = object_id('[dbo].[DICTION_U]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [dbo].DICTION_U
GO

IF exists(select * from sysobjects 
	where id = object_id('[dbo].[S_PERMISSIONS_U]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [dbo].S_PERMISSIONS_U
GO

IF exists(select * from sysobjects 
	where id = object_id('[dbo].[S_PROPERTY_U]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [dbo].S_PROPERTY_U
GO

IF exists(select * from sysobjects 
	where id = object_id('[dbo].[OBJECT_VAR_VALUES_U]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [dbo].OBJECT_VAR_VALUES_U
GO

IF exists(select * from sysobjects 
	where id = object_id('[dbo].[OBJECT_VALUES_U]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [dbo].OBJECT_VALUES_U
GO

IF exists(select * from sysobjects 
	where id = object_id('[dbo].[REPORT_FORMS_U]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [dbo].REPORT_FORMS_U
GO

IF exists(select * from sysobjects 
	where id = object_id('[dbo].[REPORT_TABLES_U]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [dbo].REPORT_TABLES_U
GO

IF exists(select * from sysobjects 
	where id = object_id('[dbo].[REPORT_ROWS_U]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [dbo].REPORT_ROWS_U
GO

IF exists(select * from sysobjects 
	where id = object_id('[dbo].[REPORT_COLOUMNS_U]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [dbo].REPORT_COLOUMNS_U
GO

IF exists(select * from sysobjects 
	where id = object_id('[dbo].[REPORT_FORMULA_U]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [dbo].REPORT_FORMULA_U
GO

IF exists(select * from sysobjects 
	where id = object_id('[dbo].[S_RELEASE_U]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [dbo].S_RELEASE_U
GO

IF exists(select * from sysobjects 
	where id = object_id('[dbo].[S_FILES_U]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [dbo].S_FILES_U
GO

IF exists(select * from sysobjects 
	where id = object_id('[dbo].[EXPORT_BLOCKS_U]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [dbo].EXPORT_BLOCKS_U
GO

IF exists(select * from sysobjects 
	where id = object_id('[dbo].[EXPORT_FIELDS_U]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [dbo].EXPORT_FIELDS_U
GO

IF exists(select * from sysobjects 
	where id = object_id('[dbo].[EXPORT_MODELS_U]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [dbo].EXPORT_MODELS_U
GO

IF exists(select * from sysobjects 
	where id = object_id('[dbo].[REPORT_CONTROL_ROWS_U]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [dbo].REPORT_CONTROL_ROWS_U
GO

IF exists(select * from sysobjects 
	where id = object_id('[dbo].[REPORT_CONTROL_RULES_U]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [dbo].REPORT_CONTROL_RULES_U
GO

IF exists(select * from sysobjects 
	where id = object_id('[dbo].[S_BANK_FORMAT_BASE_U]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [dbo].S_BANK_FORMAT_BASE_U
GO

IF exists(select * from sysobjects 
	where id = object_id('[dbo].[S_GROUP_SID_U]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [dbo].S_GROUP_SID_U
GO

IF exists(select * from sysobjects 
	where id = object_id('[dbo].[s_fileparts_U]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [dbo].s_fileparts_U
GO

IF exists(select * from sysobjects 
	where id = object_id('[dbo].[S_TABLES_U]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [dbo].S_TABLES_U
GO

IF exists(select * from sysobjects 
	where id = object_id('[dbo].[DICTION_APP_U]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [dbo].DICTION_APP_U
GO

IF exists(select * from sysobjects 
	where id = object_id('[dbo].[HISTORY_U]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [dbo].HISTORY_U
GO

IF exists(select * from sysobjects 
	where id = object_id('[dbo].[FAQ_U]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [dbo].FAQ_U
GO

IF exists(select * from sysobjects 
	where id = object_id('[dbo].[REPORT_GROUP_KS_U]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [dbo].REPORT_GROUP_KS_U
GO

IF exists(select * from sysobjects 
	where id = object_id('[dbo].[R_FILES_U]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [dbo].R_FILES_U
GO

IF exists(select * from sysobjects 
	where id = object_id('[dbo].[R_FILEPARTS_U]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [dbo].R_FILEPARTS_U
GO

IF exists(select * from sysobjects 
	where id = object_id('[dbo].[REMIND_QUERY_U]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [dbo].REMIND_QUERY_U
GO

IF exists(select * from sysobjects 
	where id = object_id('[dbo].[REPORT_CONTROL_GROUP_U]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [dbo].REPORT_CONTROL_GROUP_U
GO

IF exists(select * from sysobjects 
	where id = object_id('[dbo].[REPORT_FORM_GROUPS_U]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [dbo].REPORT_FORM_GROUPS_U
GO

IF exists(select * from sysobjects 
	where id = object_id('[dbo].[REPORT_FORM_GROUPS_REF_U]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [dbo].REPORT_FORM_GROUPS_REF_U
GO


GO
WHILE @@TRANCOUNT > 0
	raiserror('@@TRANCOUNT>0!!!',11,1)
GO
