--
-- DT343
-- 
ALTER TABLE DT343 ADD CONSTRAINT DT_343_DI063743_DEFAULT DEFAULT 0 FOR DI063743;

--
-- DT343_HIS
-- 

ALTER TABLE DT343_HIS
ADD DI063743 NUMERIC(5,0);

--
-- DT447
-- 

CREATE NONCLUSTERED INDEX [IDX_ITEXT_DT447DI041278] ON DT447
(
	[DI041278] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY];

--
-- DT470
-- 

ALTER TABLE DT470 ADD CONSTRAINT DT_470_DI063586_DEFAULT DEFAULT 0 FOR DI063586;

--
-- DT823
-- 

ALTER TABLE DT823 ALTER COLUMN DI008702 VARCHAR(30);

--
-- DT861
-- 

ALTER TABLE DT861 ADD CONSTRAINT DT_861_DI040214_DEFAULT DEFAULT 0 FOR DI040214;

--
-- TEMP_PERIOD_CHANGES
-- 

ALTER TABLE TEMP_PERIOD_CHANGES ALTER COLUMN RES_ID VARCHAR(20);

--
-- DT461
--

ALTER TABLE DT461 ADD DI041689 FLOAT DEFAULT 0;

--
-- DT877
-- 

alter table DT877 alter column DI057899 varchar(18) NULL;



IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[V_MODULE_LIST_39]'))
	DROP VIEW [dbo].[V_MODULE_LIST_39];

CREATE VIEW [dbo].[V_MODULE_LIST_39] ([PARENT_ID], [MOD_ID], [TYPE]) AS 
  SELECT [PARENT_ID],
    [MOD_ID],
    [Type]
  FROM
    ( SELECT DISTINCT V_MODULE_LIST.MODULE parent_id,
      DT411.DI000111 mod_id,
      CAST(1 AS float) [Type]
    FROM DT411 -- Statement ruft alle Listbox-Module der Module aus [V_MODULE_LIST] ab
    JOIN DT406
    ON DT406.DI000969 = DT411.DI000306
    JOIN V_MODULE_LIST
    ON DT406.DI000970 = V_MODULE_LIST.MODULE
    JOIN DT405
    ON DT411.DI000111 = DT405.DI000123
    UNION
    SELECT DISTINCT V_MODULE_LIST.MODULE parent_id,
      DT411.DI027481 mod_id,
      2
    FROM DT411 -- Statement ruft alle Makro-Module der Module aus [V_MODULE_LIST] ab
    JOIN DT406
    ON DT406.DI000969 = DT411.DI000306
    JOIN V_MODULE_LIST
    ON DT406.DI000970 = V_MODULE_LIST.MODULE
    JOIN DT405
    ON DT411.DI027481 = DT405.DI000123
    UNION
    SELECT DISTINCT V_MODULE_LIST.MODULE parent_id,
      DT404.DI028040 mod_id,
      3
    FROM DT404 -- Statement ruft alle Untermodule der Module aus [V_MODULE_LIST] ab
    JOIN V_MODULE_LIST
    ON DT404.DI028100 = V_MODULE_LIST.MODULE
    JOIN DT405
    ON DT404.DI028040 = DT405.DI000123
    ) LMU
  UNION
  SELECT MODULE , MODULE , 0 FROM V_MODULE_LIST;



  IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[V_MODULE_LIST_39_UEREL]'))
	DROP VIEW [dbo].[V_MODULE_LIST_39_UEREL];

CREATE VIEW [dbo].[V_MODULE_LIST_39_UEREL] ([PARENT_ID], [MOD_ID], [TYPE]) AS 
  SELECT TOP 100000 [PARENT_ID],
    [MOD_ID],
    [Type]
  FROM
    ( SELECT DISTINCT V_MODULE_LIST_UEREL.MODULE parent_id,
      DT411.DI000111 mod_id,
      CAST(1 AS float) [Type]
    FROM DT411 -- Statement ruft alle Listbox-Module der Module aus [V_MODULE_LIST] ab
    JOIN DT406
    ON DT406.DI000969 = DT411.DI000306
    JOIN V_MODULE_LIST_UEREL
    ON DT406.DI000970      = V_MODULE_LIST_UEREL.MODULE
    WHERE LEN(DI000111) = 6
    UNION ALL
    SELECT DISTINCT V_MODULE_LIST_UEREL.MODULE parent_id,
      DT411.DI027481 mod_id,
      2
    FROM DT411 -- Statement ruft alle Makro-Module der Module aus [V_MODULE_LIST] ab
    JOIN DT406
    ON DT406.DI000969 = DT411.DI000306
    JOIN V_MODULE_LIST_UEREL
    ON DT406.DI000970      = V_MODULE_LIST_UEREL.MODULE
    WHERE LEN(DI027481) = 6
    UNION ALL
    SELECT DISTINCT V_MODULE_LIST_UEREL.MODULE parent_id,
      DT404.DI028040 mod_id,
      3
    FROM DT404 -- Statement ruft alle Untermodule der Module aus [V_MODULE_LIST] ab
    JOIN V_MODULE_LIST_UEREL
    ON DT404.DI028100 = V_MODULE_LIST_UEREL.MODULE
    WHERE DT404.DI028100 LIKE V_MODULE_LIST_UEREL.MODULE
    AND LEN(DI028040) = 6
    ) LMU
  UNION ALL
  SELECT TOP 100000 MODULE [PARENT_ID],
    MODULE ,
    0
  FROM V_MODULE_LIST_UEREL
    --connect by parent_id = mod_id
  ORDER BY [PARENT_ID];


