--
-- DT343
-- 

ALTER TABLE DT343 MODIFY DI063743 DEFAULT 0;

--
-- DT343_HIS
-- 

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

--
-- DT447
-- 

-- Only way to get rid of default value for sure
ALTER TABLE DT447 RENAME COLUMN DI041278 TO DI041278_WITH_DEFAULT;
ALTER TABLE DT447 ADD DI041278 number(10,0);
UPDATE DT447 SET DI041278 = DI041278_WITH_DEFAULT;
ALTER TABLE DT447 DROP COLUMN DI041278_WITH_DEFAULT;
CREATE INDEX IDX_ITEXT_DT447DI041278 ON DT447 (DI041278 ASC)
    PCTFREE 10 INITRANS 2 MAXTRANS 255
    STORAGE (INITIAL 64K
             NEXT 1024K
             MINEXTENTS 1
             MAXEXTENTS 2147483645);

--
-- DT470
-- 

ALTER TABLE DT470 MODIFY DI063586 DEFAULT 0;

--
-- DT472
-- 

DROP INDEX IDX_CAL_DT472;

--
-- DT823
-- 

ALTER TABLE DT823 MODIFY DI008702 VARCHAR2(30 CHAR);

--
-- DT861
-- 

ALTER TABLE DT861 MODIFY DI040214 DEFAULT 0;

--
-- DT877
-- 

alter table DT877 modify (DI057899 varchar2(18));
ALTER TABLE DT877 MODIFY SEQUENCE DEFAULT 0;

--
-- INTERFACE_PARAMETER
-- 

ALTER TABLE INTERFACE_PARAMETER MODIFY SYSTEM DEFAULT NULL;

--
-- MIGRATIONHISTORY
-- 

ALTER TABLE MIGRATIONHISTORY MODIFY TIMESTAMP DEFAULT 0;

--
-- SYSTEMCOND
-- 

ALTER TABLE SYSTEMCOND MODIFY SYSTEM NULL;

--
-- TEMP_PERIOD_CHANGES
-- 

ALTER TABLE TEMP_PERIOD_CHANGES MODIFY RES_ID VARCHAR2(20 BYTE);

--
-- DT461
--

ALTER TABLE DT461 ADD DI041689 NUMBER DEFAULT 0;


CREATE OR REPLACE FORCE VIEW "V_MODULE_LIST_39" ("PARENT_ID", "MOD_ID", "TYPE") AS 
SELECT "PARENT_ID",
"MOD_ID",
"Type" AS "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)
)
UNION
SELECT MODULE , MODULE , 0 FROM V_MODULE_LIST;

CREATE OR REPLACE FORCE VIEW "V_MODULE_LIST_39_UEREL" ("PARENT_ID", "MOD_ID", "TYPE") AS 
SELECT "PARENT_ID",
"MOD_ID",
"Type" AS "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 LENGTH (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 LENGTH (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 LENGTH(DI028040) = 6
)
UNION ALL
SELECT MODULE ,
MODULE ,
0
FROM V_MODULE_LIST_UEREL
--connect by parent_id = mod_id
ORDER BY parent_id;