create table SAP_PSP_AAE (
	[UUID] uniqueidentifier not null,
	[AAE] varchar(36) not null,
	[SAP_PSP_UUID] uniqueidentifier,
	[PR_ID] varchar(18),
	[EFFORT_PLAN] float,
	[EFFORT_ACT] float,
	[EFFORT_REM] float,
	[EFFORT_PLAN_PLANTA] float,
	[EFFORT_ACT_PLANTA] float,
	[EFFORT_REM_PLANTA] float,
	[COSTS_PLAN] float,
	[COSTS_ACT] float,
	[COSTS_REM] float,
	[COSTS_PLAN_PLANTA] float,
	[COSTS_ACT_PLANTA] float,
	[COSTS_REM_PLANTA] float,
	[BUDGET] float,
	[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)
);

GO

alter table SAP_PSP_AAE add constraint PID975DT975 primary key ([UUID]);

GO

alter table SAP_PSP_AAE with nocheck
	add constraint FKSAP_PSP_AAEID963
	foreign key ([SAP_PSP_UUID])
	references DT963
	on delete no action;

GO

CREATE TRIGGER UFKSAP_PSP_AAEID963 ON DT963
	FOR DELETE
	AS
	BEGIN
		DELETE t
		FROM DELETED d
			INNER JOIN SAP_PSP_AAE t ON d.UUID = t.SAP_PSP_UUID
	END;

GO

alter table SAP_PSP_AAE with check check constraint FKSAP_PSP_AAEID963;

GO

create index IDX_SAP_PSP_AAEID963 on SAP_PSP_AAE ([SAP_PSP_UUID]);

GO

alter table SAP_PSP_AAE add constraint DT975UUID_DEFAULT DEFAULT newid() FOR [UUID];

GO

alter table SAP_PSP_AAE add constraint DT975UUID_NOT_NULL check([UUID] is not null);

GO

alter table SAP_PSP_AAE add constraint DT975DI064604_DEFAULT DEFAULT 0 FOR [EFFORT_PLAN];

GO

alter table SAP_PSP_AAE add constraint DT975DI064603_DEFAULT DEFAULT 0 FOR [EFFORT_ACT];

GO

alter table SAP_PSP_AAE add constraint DT975DI064602_DEFAULT DEFAULT 0 FOR [EFFORT_REM];

GO

alter table SAP_PSP_AAE add constraint DT975DI064597_DEFAULT DEFAULT 0 FOR [EFFORT_PLAN_PLANTA];

GO

alter table SAP_PSP_AAE add constraint DT975DI064596_DEFAULT DEFAULT 0 FOR [EFFORT_ACT_PLANTA];

GO

alter table SAP_PSP_AAE add constraint DT975DI064595_DEFAULT DEFAULT 0 FOR [EFFORT_REM_PLANTA];

GO

alter table SAP_PSP_AAE add constraint DT975DI064601_DEFAULT DEFAULT 0 FOR [COSTS_PLAN];

GO

alter table SAP_PSP_AAE add constraint DT975DI064600_DEFAULT DEFAULT 0 FOR [COSTS_ACT];

GO

alter table SAP_PSP_AAE add constraint DT975DI064599_DEFAULT DEFAULT 0 FOR [COSTS_REM];

GO

alter table SAP_PSP_AAE add constraint DT975DI064592_DEFAULT DEFAULT 0 FOR [COSTS_PLAN_PLANTA];

GO

alter table SAP_PSP_AAE add constraint DT975DI064593_DEFAULT DEFAULT 0 FOR [COSTS_ACT_PLANTA];

GO

alter table SAP_PSP_AAE add constraint DT975DI064594_DEFAULT DEFAULT 0 FOR [COSTS_REM_PLANTA];

GO

alter table SAP_PSP_AAE add constraint DT975DI064598_DEFAULT DEFAULT 0 FOR [BUDGET];

GO

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

GO

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

GO

alter table SAP_PSP_AAE add constraint DT975DI064607_DEFAULT DEFAULT 0 FOR [RWDU];

GO

alter table SAP_PSP_AAE add constraint DT975DI064612_DEFAULT DEFAULT 0 FOR [OWNER];

GO

alter table SAP_PSP_AAE add constraint DT975DI064613_DEFAULT DEFAULT 0 FOR [GROUP];

GO

alter table SAP_PSP_AAE add constraint DT975DI064614_DEFAULT DEFAULT 0 FOR [ANY];

GO

create table SAP_PSP_AAE_REPORT (
	[UUID] uniqueidentifier not null,
	[AAE] varchar(36) not null,
	[SAP_PSP_REP_UUID] uniqueidentifier not null,
	[PR_ID] varchar(18) not null,
	[REPORT_ID] varchar(10) not null,
	[EFFORT_PLAN] float,
	[EFFORT_ACT] float,
	[EFFORT_REM] float,
	[EFFORT_PLAN_PLANTA] float,
	[EFFORT_ACT_PLANTA] float,
	[EFFORT_REM_PLANTA] float,
	[COSTS_PLAN] float,
	[COSTS_ACT] float,
	[COSTS_REM] float,
	[COSTS_PLAN_PLANTA] float,
	[COSTS_ACT_PLANTA] float,
	[COSTS_REM_PLANTA] float,
	[BUDGET] float,
	[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)
);

GO

alter table SAP_PSP_AAE_REPORT add constraint PID976DT976 primary key ([UUID], [AAE], [SAP_PSP_REP_UUID], [PR_ID], [REPORT_ID]);

GO

alter table SAP_PSP_AAE_REPORT with nocheck add constraint FKSAP_PSP_AAE_REPORTID974
	foreign key ([SAP_PSP_REP_UUID], [PR_ID], [REPORT_ID])
	references DT974
	on delete no action;
	
GO

CREATE TRIGGER UFKSAP_PSP_AAE_REPORTID974 ON DT974
	FOR DELETE
	AS
	BEGIN
		DELETE t
		FROM DELETED d
			INNER JOIN SAP_PSP_AAE_REPORT t ON d.UUID = t.SAP_PSP_REP_UUID
							  AND d.PR_ID = t.PR_ID
							  AND d.REPORT_ID = t.REPORT_ID
	END;
	
GO

alter table SAP_PSP_AAE_REPORT with check check constraint FKSAP_PSP_AAE_REPORTID974;

GO

create index IDX_SAP_PSP_AAE_REPORTID974 on SAP_PSP_AAE_REPORT ([SAP_PSP_REP_UUID], [PR_ID], [REPORT_ID]);

GO

alter table SAP_PSP_AAE_REPORT add constraint DT976UUID_DEFAULT DEFAULT newid() FOR [UUID];

GO

alter table SAP_PSP_AAE_REPORT add constraint DT976UUID_NOT_NULL check([UUID] is not null);

GO

alter table SAP_PSP_AAE_REPORT add constraint DT976AAE_NOT_NULL check([AAE] is not null);

GO

alter table SAP_PSP_AAE_REPORT add constraint DT976SAP_PSP_REP_UUID_NOT_NULL check([SAP_PSP_REP_UUID] is not null);

GO

alter table SAP_PSP_AAE_REPORT add constraint DT976PR_ID_NOT_NULL check([PR_ID] is not null);

GO

alter table SAP_PSP_AAE_REPORT add constraint DT976REPORT_ID_NOT_NULL check([REPORT_ID] is not null);

GO

alter table SAP_PSP_AAE_REPORT add constraint DT976DI064651_DEFAULT DEFAULT 0 FOR [EFFORT_PLAN];

GO

alter table SAP_PSP_AAE_REPORT add constraint DT976DI064650_DEFAULT DEFAULT 0 FOR [EFFORT_ACT];

GO

alter table SAP_PSP_AAE_REPORT add constraint DT976DI064649_DEFAULT DEFAULT 0 FOR [EFFORT_REM];

GO

alter table SAP_PSP_AAE_REPORT add constraint DT976DI064640_DEFAULT DEFAULT 0 FOR [EFFORT_PLAN_PLANTA];

GO

alter table SAP_PSP_AAE_REPORT add constraint DT976DI064641_DEFAULT DEFAULT 0 FOR [EFFORT_ACT_PLANTA];

GO

alter table SAP_PSP_AAE_REPORT add constraint DT976DI064642_DEFAULT DEFAULT 0 FOR [EFFORT_REM_PLANTA];

GO

alter table SAP_PSP_AAE_REPORT add constraint DT976DI064648_DEFAULT DEFAULT 0 FOR [COSTS_PLAN];

GO

alter table SAP_PSP_AAE_REPORT add constraint DT976DI064647_DEFAULT DEFAULT 0 FOR [COSTS_ACT];

GO

alter table SAP_PSP_AAE_REPORT add constraint DT976DI064646_DEFAULT DEFAULT 0 FOR [COSTS_REM];

GO

alter table SAP_PSP_AAE_REPORT add constraint DT976DI064639_DEFAULT DEFAULT 0 FOR [COSTS_PLAN_PLANTA];

GO

alter table SAP_PSP_AAE_REPORT add constraint DT976DI064638_DEFAULT DEFAULT 0 FOR [COSTS_ACT_PLANTA];

GO

alter table SAP_PSP_AAE_REPORT add constraint DT976DI064637_DEFAULT DEFAULT 0 FOR [COSTS_REM_PLANTA];

GO

alter table SAP_PSP_AAE_REPORT add constraint DT976DI064645_DEFAULT DEFAULT 0 FOR [BUDGET];

GO

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

GO

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

GO

alter table SAP_PSP_AAE_REPORT add constraint DT976DI064654_DEFAULT DEFAULT 0 FOR [RWDU];

GO

alter table SAP_PSP_AAE_REPORT add constraint DT976DI064659_DEFAULT DEFAULT 0 FOR [OWNER];

GO

alter table SAP_PSP_AAE_REPORT add constraint DT976DI064660_DEFAULT DEFAULT 0 FOR [GROUP];

GO

alter table SAP_PSP_AAE_REPORT add constraint DT976DI064661_DEFAULT DEFAULT 0 FOR [ANY];

GO