create table DT963 (
    [UUID] uniqueidentifier not null,
    [PR_ID] varchar(18),
    [PSP_ID] varchar(18),
    [PARENT_PSP] uniqueidentifier,
    [PSP_NAME] varchar(50),
    [CREATED_ON] datetime2,
    [MODIFIED_ON] datetime2,
    [RWDU] numeric(5,0),
    [CREATED_BY] varchar(20),
    [MODIFIED_BY] varchar(20),
    [OWN_LIC] varchar(4),
    [OPC] varchar(8),
    [OWNER] numeric(5,0),
    [GROUP] numeric(5,0),
    [ANY] numeric(5,0),
    [EFFORT_PLAN] float,
    [EFFORT_ACT] float,
    [EFFORT_REM] float,
    [COSTS_PLAN] float,
    [COSTS_ACT] float,
    [COSTS_REM] float,
    [EFFORT_PLAN_SUM] float,
    [EFFORT_ACT_SUM] float,
    [EFFORT_REM_SUM] float,
    [COSTS_PLAN_SUM] float,
    [COSTS_ACT_SUM] float,
    [COSTS_REM_SUM] float,
    [PERSON_RESP] varchar(20),
    [STATUS] numeric(5,0)
);

IF NOT EXISTS (SELECT * FROM SYS.INDEXES
   WHERE IS_PRIMARY_KEY = 1 AND OBJECT_ID IN
         (SELECT OBJECT_ID FROM SYS.TABLES WHERE NAME = 'DT963'))
   alter table DT963 add constraint PID963DT963 primary key ([UUID]);

alter table DT963 with nocheck
    add constraint FKDT963ID461
    foreign key ([PR_ID])
    references DT461
    on delete no action;

CREATE TRIGGER UFKDT963ID461 ON DT461
    FOR DELETE
    AS
    BEGIN
        DELETE t
        FROM DELETED d
            INNER JOIN DT963 t ON d.DI001001 = t.PR_ID
    END;

alter table DT963 with check check constraint FKDT963ID461;


create index IDX_DT963ID461 on DT963 ([PR_ID]);


create index IDX_DT963ID963S on DT963 ([PARENT_PSP]);


alter table DT963
    add constraint DT963UUID_DEFAULT DEFAULT newid() FOR [UUID];


alter table DT963
    add constraint DT963UUID_NOT_NULL check([UUID] is not null);


alter table DT963
    add constraint DT963PR_ID_NOT_NULL check([PR_ID] is not null);


alter table DT963
    add constraint DT963DI063858_DEFAULT DEFAULT CONVERT(datetime2,'01.01.1970',104) FOR [CREATED_ON];


alter table DT963
    add constraint DT963DI063857_DEFAULT DEFAULT CONVERT(datetime2,'01.01.1970',104) FOR [MODIFIED_ON];


alter table DT963
    add constraint DT963DI063856_DEFAULT DEFAULT 0 FOR [RWDU];


alter table DT963
    add constraint DT963DI063851_DEFAULT DEFAULT 0 FOR [OWNER];


alter table DT963
    add constraint DT963DI063850_DEFAULT DEFAULT 0 FOR [GROUP];


alter table DT963
    add constraint DT963DI063849_DEFAULT DEFAULT 0 FOR [ANY];


alter table DT963
    add constraint DT963DI063869_DEFAULT DEFAULT 0 FOR [EFFORT_PLAN];


alter table DT963
    add constraint DT963DI063868_DEFAULT DEFAULT 0 FOR [EFFORT_ACT];


alter table DT963
    add constraint DT963DI063867_DEFAULT DEFAULT 0 FOR [EFFORT_REM];


alter table DT963
    add constraint DT963DI063866_DEFAULT DEFAULT 0 FOR [COSTS_PLAN];


alter table DT963
    add constraint DT963DI063865_DEFAULT DEFAULT 0 FOR [COSTS_ACT];


alter table DT963
    add constraint DT963DI063864_DEFAULT DEFAULT 0 FOR [COSTS_REM];


alter table DT963
    add constraint DT963DI063875_DEFAULT DEFAULT 0 FOR [EFFORT_PLAN_SUM];


alter table DT963
    add constraint DT963DI063874_DEFAULT DEFAULT 0 FOR [EFFORT_ACT_SUM];


alter table DT963
    add constraint DT963DI063873_DEFAULT DEFAULT 0 FOR [EFFORT_REM_SUM];


alter table DT963
    add constraint DT963DI063872_DEFAULT DEFAULT 0 FOR [COSTS_PLAN_SUM];


alter table DT963
    add constraint DT963DI063871_DEFAULT DEFAULT 0 FOR [COSTS_ACT_SUM];


alter table DT963
    add constraint DT963DI063870_DEFAULT DEFAULT 0 FOR [COSTS_REM_SUM];


alter table DT963
    add constraint DT963DI063877_DEFAULT DEFAULT 0 FOR [STATUS];


create table DT974 (
    [UUID] uniqueidentifier not null,
    [PR_ID] varchar(18) not null,
    [REPORT_ID] varchar(10) not null,
    [PSP_ID] varchar(18),
    [PSP_NAME] varchar(50),
    [PARENT_PSP] uniqueidentifier,
    [CREATED_ON] datetime2,
    [MODIFIED_ON] datetime2,
    [RWDU] numeric(5,0),
    [CREATED_BY] varchar(20),
    [MODIFIED_BY] varchar(20),
    [OWN_LIC] varchar(4),
    [OPC] varchar(8),
    [OWNER] numeric(5,0),
    [GROUP] numeric(5,0),
    [ANY] numeric(5,0),
    [EFFORT_PLAN] float,
    [EFFORT_ACT] float,
    [EFFORT_REM] float,
    [COSTS_PLAN] float,
    [COSTS_ACT] float,
    [COSTS_REM] float,
    [EFFORT_PLAN_SUM] float,
    [EFFORT_ACT_SUM] float,
    [EFFORT_REM_SUM] float,
    [COSTS_PLAN_SUM] float,
    [COSTS_ACT_SUM] float,
    [COSTS_REM_SUM] float,
    [PERSON_RESP] varchar(20),
    [STATUS] numeric(5,0)
);


IF NOT EXISTS (SELECT * FROM SYS.INDEXES
   WHERE IS_PRIMARY_KEY = 1 AND OBJECT_ID IN
         (SELECT OBJECT_ID FROM SYS.TABLES WHERE NAME = 'DT974'))
   alter table DT974 add constraint PID974DT974 primary key ([UUID], [PR_ID], [REPORT_ID]);


alter table DT974 with nocheck
    add constraint FKDT974ID461
    foreign key ([PR_ID])
    references DT461
    on delete no action;

CREATE TRIGGER UFKDT974ID461 ON DT461
    FOR DELETE
    AS
    BEGIN
        DELETE t
        FROM DELETED d
            INNER JOIN DT974 t ON d.DI001001 = t.PR_ID
    END;

alter table DT974 with check check constraint FKDT974ID461;


alter table DT974 with nocheck
    add constraint FKDT974ID823
    foreign key ([PR_ID], [REPORT_ID])
    references DT823
    on delete no action;

CREATE TRIGGER UFKDT974ID823 ON DT823
    FOR DELETE
    AS
    BEGIN
        DELETE t
        FROM DELETED d
            INNER JOIN DT974 t ON d.DI008413 = t.PR_ID
                              AND d.DI008416 = t.REPORT_ID
    END;

alter table DT974 with check check constraint FKDT974ID823;


create index IDX_DT974ID974S on DT974 ([PARENT_PSP], [PR_ID], [REPORT_ID]);


create index IDX_DT974ID461 on DT974 ([PR_ID]);


create index IDX_DT974ID823 on DT974 ([PR_ID], [REPORT_ID]);


alter table DT974
    add constraint DT974UUID_DEFAULT DEFAULT newid() FOR [UUID];


alter table DT974
    add constraint DT974UUID_NOT_NULL check([UUID] is not null);


alter table DT974
    add constraint DT974PR_ID_NOT_NULL check([PR_ID] is not null);


alter table DT974
    add constraint DT974REPORT_ID_NOT_NULL check([REPORT_ID] is not null);


alter table DT974
    add constraint DT974PARENT_PSP_NOT_NULL check([PARENT_PSP] is not null);


alter table DT974
    add constraint DT974DI063902_DEFAULT DEFAULT CONVERT(datetime2,'01.01.1970',104) FOR [CREATED_ON];


alter table DT974
    add constraint DT974DI063901_DEFAULT DEFAULT CONVERT(datetime2,'01.01.1970',104) FOR [MODIFIED_ON];


alter table DT974
    add constraint DT974DI063900_DEFAULT DEFAULT 0 FOR [RWDU];


alter table DT974
    add constraint DT974DI063895_DEFAULT DEFAULT 0 FOR [OWNER];


alter table DT974
    add constraint DT974DI063894_DEFAULT DEFAULT 0 FOR [GROUP];


alter table DT974
    add constraint DT974DI063893_DEFAULT DEFAULT 0 FOR [ANY];


alter table DT974
    add constraint DT974DI063892_DEFAULT DEFAULT 0 FOR [EFFORT_PLAN];


alter table DT974
    add constraint DT974DI063891_DEFAULT DEFAULT 0 FOR [EFFORT_ACT];


alter table DT974
    add constraint DT974DI063890_DEFAULT DEFAULT 0 FOR [EFFORT_REM];


alter table DT974
    add constraint DT974DI063889_DEFAULT DEFAULT 0 FOR [COSTS_PLAN];


alter table DT974
    add constraint DT974DI063888_DEFAULT DEFAULT 0 FOR [COSTS_ACT];


alter table DT974
    add constraint DT974DI063887_DEFAULT DEFAULT 0 FOR [COSTS_REM];


alter table DT974
    add constraint DT974DI063886_DEFAULT DEFAULT 0 FOR [EFFORT_PLAN_SUM];


alter table DT974
    add constraint DT974DI063885_DEFAULT DEFAULT 0 FOR [EFFORT_ACT_SUM];


alter table DT974
    add constraint DT974DI063884_DEFAULT DEFAULT 0 FOR [EFFORT_REM_SUM];


alter table DT974
    add constraint DT974DI063883_DEFAULT DEFAULT 0 FOR [COSTS_PLAN_SUM];


alter table DT974
    add constraint DT974DI063882_DEFAULT DEFAULT 0 FOR [COSTS_ACT_SUM];


alter table DT974
    add constraint DT974DI063881_DEFAULT DEFAULT 0 FOR [COSTS_REM_SUM];

alter table DT974 
    add constraint DT974DI063879_DEFAULT DEFAULT 0 FOR [STATUS];

alter table DT463 add SAP_STRUCTURE uniqueidentifier;

alter table DT824 add SAP_STRUCTURE uniqueidentifier;

alter table DT963 add EFFORT_TOTAL float;

alter table DT963 add constraint DT963DI063986_DEFAULT DEFAULT 0 FOR [EFFORT_TOTAL];

alter table DT963 add COST_TOTAL float;

alter table DT963 add constraint DT963DI063985_DEFAULT DEFAULT 0 FOR [COST_TOTAL];

alter table DT974 add EFFORT_TOTAL float;

alter table DT974 add constraint DT974DI063988_DEFAULT DEFAULT 0 FOR [EFFORT_TOTAL];

alter table DT974 add COST_TOTAL float;

alter table DT974 add constraint DT974DI063987_DEFAULT DEFAULT 0 FOR [COST_TOTAL];

create table SAPPOOL (
    [UUID] uniqueidentifier not null,
    [CONFIG_ID] uniqueidentifier,
    [CREATED_ID] uniqueidentifier,
    [PSP_ID] varchar(18),
    [PSP_NAME] varchar(30),
    [PARENT_PSP_ID] varchar(18),
    [PR_ID] varchar(18),
    [CREATED_ON] datetime2,
    [MODIFIED_ON] datetime2,
    [RWDU] numeric(5,0),
    [CREATED_BY] varchar(20),
    [MODIFIED_BY] varchar(20),
    [OWN_LIC] varchar(4),
    [OPC] varchar(8),
    [OWNER_DI] numeric(5,0),
    [GROUP_DI] numeric(5,0),
    [ANY_DI] numeric(5,0),
    [COST_PLAN] float,
    [COST_ACT] float,
    [COST_REM] float,
    [EFFORT_PLAN] float,
    [EFFORT_ACT] float,
    [EFFORT_REM] float,
    [PERSON_RESP] varchar(20),
    [STATUS] numeric(5,0),
    [TRANSFERRED_ON] varchar(80)
);

IF NOT EXISTS (SELECT * FROM SYS.INDEXES
   WHERE IS_PRIMARY_KEY = 1 AND OBJECT_ID IN
         (SELECT OBJECT_ID FROM SYS.TABLES WHERE NAME = 'SAPPOOL'))
   alter table SAPPOOL add constraint PID964DT964 primary key ([UUID]);

alter table SAPPOOL with nocheck
    add constraint FKSAPPOOLID461
    foreign key ([PR_ID])
    references DT461
    on delete no action;

alter table SAPPOOL with check check constraint FKSAPPOOLID461;

create index IDX_SAPPOOLID461 on SAPPOOL ([PR_ID]);

alter table SAPPOOL
    add constraint DT964UUID_DEFAULT DEFAULT newid() FOR [UUID];

alter table SAPPOOL
    add constraint DT964UUID_NOT_NULL check([UUID] is not null);

alter table SAPPOOL
    add constraint DT964DI063946_DEFAULT DEFAULT CONVERT(datetime2,'01.01.1970',104) FOR [CREATED_ON];

alter table SAPPOOL
    add constraint DT964DI063947_DEFAULT DEFAULT CONVERT(datetime2,'01.01.1970',104) FOR [MODIFIED_ON];

alter table SAPPOOL
    add constraint DT964DI063948_DEFAULT DEFAULT 0 FOR [RWDU];

alter table SAPPOOL
    add constraint DT964DI063953_DEFAULT DEFAULT 0 FOR [OWNER_DI];

alter table SAPPOOL
    add constraint DT964DI063954_DEFAULT DEFAULT 0 FOR [GROUP_DI];

alter table SAPPOOL
    add constraint DT964DI063955_DEFAULT DEFAULT 0 FOR [ANY_DI];

alter table SAPPOOL
    add constraint DT964DI063957_DEFAULT DEFAULT 0 FOR [COST_PLAN];

alter table SAPPOOL
    add constraint DT964DI063958_DEFAULT DEFAULT 0 FOR [COST_ACT];

alter table SAPPOOL
    add constraint DT964DI063959_DEFAULT DEFAULT 0 FOR [COST_REM];

alter table SAPPOOL
    add constraint DT964DI063960_DEFAULT DEFAULT 0 FOR [EFFORT_PLAN];

alter table SAPPOOL
    add constraint DT964DI063961_DEFAULT DEFAULT 0 FOR [EFFORT_ACT];

alter table SAPPOOL
    add constraint DT964DI063962_DEFAULT DEFAULT 0 FOR [EFFORT_REM];

alter table SAPPOOL
    add constraint DT964DI063964_DEFAULT DEFAULT 0 FOR [STATUS];
