-- ******************** INTERFACE TABLES *******************************************
    create table BELASTUNG_POOL (
        [UUID] uniqueidentifier default newid() not null,
        [CONFIG_ID] uniqueidentifier default newid() not null,
        [CREATED_ID] uniqueidentifier default newid() null,
        [CREATED_ON] datetime default CONVERT(datetime,'01.01.1970',104) not null,
        [MODIFIED_ON] datetime default CONVERT(datetime,'01.01.1970',104) not null,
        [RWDU] numeric(5,0) default 0 not null,
        [CREATED_BY] varchar(20),
        [MODIFIED_BY] varchar(20),
        [OWN_LIC] varchar(4),
        [OPC] varchar(8),
        [OWNER] numeric(5,0) default 0 not null,
        [GROUP] numeric(5,0) default 0 not null,
        [ANY] numeric(5,0) default 0 not null,
        [TASK_ID] varchar(18),
        [RES_ID] varchar(20),
        [CT_ID] varchar(8),
        [PR_ID] varchar(18),
        [DATE] datetime default CONVERT(datetime,'01.01.1970',104) not null,
        [LOAD_ACT] float default 0 not null,
        [COMMENT] varchar(80),
        [LOAD_DESCRIPTION_2] varchar(80),
        [OU_CCTR_TO_BE_LOADED] varchar(8),
        [TIME_START] numeric(10,0) default 0 not null,
        [TIME_END] numeric(10,0) default 0 not null,
        [OU_CCTR_OF_RES] varchar(8),
        [ITEM] numeric(10,0) default 0 not null,
        [PERSON_INT_PAY_OBL] float default 0 not null,
        [PERSON_EXT_PAY_OBL] float default 0 not null,
        [MAT_COSTS_PAY_OBL] float default 0 not null,
        [INVESTM_PAY_OBLIG] float default 0 not null,
        [MISCEL_PAY_OBLIG] float default 0 not null,
        [PAY_OBLIGATION] float default 0 not null,
        [OBLIGO_RECORD] numeric(5,0) default 0 not null,
        [STORNO_RECORD] varchar(20),
        [RELEASED] numeric(5,0) default 0 not null,
        [PR_TYPE] numeric(5,0) default 0 not null,
        [RESOURCE_TYPE] varchar(2),
        [STORNO_TYPE] varchar(20),
        [SAP_DATE] datetime default CONVERT(datetime,'01.01.1970',104) not null,
        [IS_RELEASED] numeric(5,0) default 0 not null,
        [STATUS] numeric(5,0) default 0 not null,
        [TRANSFERRED_ON] varchar(80),
        [PSP_ELEMENT] varchar(40),
        [ACTIVITY] varchar(40),
        [BASIC_UNIT] varchar(4),
        [POSTING_TYPE] numeric(5,0) default 0 not null,
        [TASK_NAME] varchar(80),
        [PR_TECHNICAL] varchar(18)
    )
;

    create table INTERFACE_CONFIG (
        [UUID] uniqueidentifier default newid() not null,
        [COPY_OF] uniqueidentifier default newid() null,
        [COPIED_BY] varchar(20),
        [COPIED_ON] datetime default CONVERT(datetime,'01.01.1970',104) not null,
        [SOURCE_MODULE] varchar(16),
        [TARGET_MODULE] varchar(16),
        [POOL_MODULE] varchar(16),
        [CREATED_ON] datetime default CONVERT(datetime,'01.01.1970',104) not null,
        [MODIFIED_ON] datetime default CONVERT(datetime,'01.01.1970',104) not null,
        [RWDU] numeric(5,0) default 0 not null,
        [CREATED_BY] varchar(20),
        [MODIFIED_BY] varchar(20),
        [OWN_LIC] varchar(4),
        [OPC] varchar(8),
        [OWNER] numeric(5,0) default 0 not null,
        [GROUP] numeric(5,0) default 0 not null,
        [ANY] numeric(5,0) default 0 not null,
        [POOL_TABLE] varchar(4),
        [DESCRIPTION] varchar(40),
        [LOGLEVEL] numeric(10,0) default 0 not null,
        [FINISHED] numeric(5,0) default 0 not null,
        [TEMPLATE] numeric(5,0) default 0 not null,
        [TRANSFERRED_ON] varchar(80)
    )
;

    create table INTERFACE_LOGGING (
        [UUID] uniqueidentifier default newid() not null,
        [CONFIG_ID] uniqueidentifier default newid() not null,
        [CREATED_ON] datetime default CONVERT(datetime,'01.01.1970',104) not null,
        [MODIFIED_ON] datetime default CONVERT(datetime,'01.01.1970',104) not null,
        [RWDU] numeric(5,0) default 0 not null,
        [CREATED_BY] varchar(20),
        [MODIFIED_BY] varchar(20),
        [OWN_LIC] varchar(4),
        [OPC] varchar(8),
        [OWNER] numeric(5,0) default 0 not null,
        [GROUP] numeric(5,0) default 0 not null,
        [ANY] numeric(5,0) default 0 not null,
        [TEXT] varchar(4000),
        [LOGLEVEL] numeric(10,0) default 0 not null,
        [OBJECT] uniqueidentifier default newid() not null,
        [TIMESTAMP] varchar(20)
    )
;

    create table INTERFACE_MAPPING (
        [UUID] uniqueidentifier default newid() not null,
        [CONFIG_ID] uniqueidentifier default newid() not null,
        [PARENT_ID] uniqueidentifier default newid() null,
        [CREATED_ON] datetime default CONVERT(datetime,'01.01.1970',104) not null,
        [MODIFIED_ON] datetime default CONVERT(datetime,'01.01.1970',104) not null,
        [RWDU] numeric(5,0) default 0 not null,
        [CREATED_BY] varchar(20),
        [MODIFIED_BY] varchar(20),
        [OWN_LIC] varchar(4),
        [OPC] varchar(8),
        [OWNER] numeric(5,0) default 0 not null,
        [GROUP] numeric(5,0) default 0 not null,
        [ANY] numeric(5,0) default 0 not null,
        [POSITION] numeric(5,0) default 0 not null,
        [OBJECT] varchar(1000),
        [TYPE] numeric(10,0) default 0 not null
    )
;

    create table INTERFACE_PARAMETER (
        [UUID] uniqueidentifier default newid() not null,
        [CONFIG_ID] uniqueidentifier default newid() not null,
        [SOURCE] varchar(16),
        [CREATED_ON] datetime default CONVERT(datetime,'01.01.1970',104) not null,
        [MODIFIED_ON] datetime default CONVERT(datetime,'01.01.1970',104) not null,
        [RWDU] numeric(5,0) default 0 not null,
        [CREATED_BY] varchar(20),
        [MODIFIED_BY] varchar(20),
        [OWN_LIC] varchar(4),
        [OPC] varchar(8),
        [OWNER] numeric(5,0) default 0 not null,
        [GROUP] numeric(5,0) default 0 not null,
        [ANY] numeric(5,0) default 0 not null,
        [NAME] varchar(100),
        [VALUE] varchar(1000)
    )
;

    create table MAPPING_PARAMETER (
        [UUID] uniqueidentifier default newid() not null,
        [MAPPING_ID] uniqueidentifier default newid() not null,
        [CREATED_ON] datetime default CONVERT(datetime,'01.01.1970',104) not null,
        [MODIFIED_ON] datetime default CONVERT(datetime,'01.01.1970',104) not null,
        [RWDU] numeric(5,0) default 0 not null,
        [CREATED_BY] varchar(20),
        [MODIFIED_BY] varchar(20),
        [OWN_LIC] varchar(4),
        [OPC] varchar(8),
        [OWNER] numeric(5,0) default 0 not null,
        [GROUP] numeric(5,0) default 0 not null,
        [ANY] numeric(5,0) default 0 not null,
        [NAME] varchar(100),
        [VALUE] varchar(1000)
    )
;

    create table MITARBEITER_POOL (
        [UUID] uniqueidentifier default newid() not null,
        [CONFIG_ID] uniqueidentifier default newid() not null,
        [CREATED_ON] datetime default CONVERT(datetime,'01.01.1970',104) not null,
        [MODIFIED_ON] datetime default CONVERT(datetime,'01.01.1970',104) not null,
        [RWDU] numeric(5,0) default 0 not null,
        [CREATED_BY] varchar(20),
        [MODIFIED_BY] varchar(20),
        [OWN_LIC] varchar(4),
        [OPC] varchar(8),
        [OWNER] numeric(5,0) default 0 not null,
        [GROUP] numeric(5,0) default 0 not null,
        [ANY] numeric(5,0) default 0 not null,
        [STATUS] numeric(5,0) default 0 not null,
        [TRANSFERRED_ON] varchar(80),
        [CODE] varchar(20),
        [NAME_COMPLETE] varchar(200),
        [EMAIL] varchar(80),
        [START_DATE] datetime default CONVERT(datetime,'01.01.1970',104) not null,
        [END_DATE] datetime default CONVERT(datetime,'01.01.1970',104) not null,
        [BIRTHDATE] datetime default CONVERT(datetime,'01.01.1970',104) not null,
        [NAME] varchar(80),
        [PRENAME] varchar(80),
        [NATIVE_LANGUAGE] varchar(6),
        [FOREIGN_LANGUAGE] varchar(6),
        [GENDER] numeric(5,0) default 0 not null,
        [TITLE] varchar(40),
        [CATEGORIE] varchar(50),
        [PHONE1] varchar(20),
        [PHONE2] varchar(20),
        [PHONE3] varchar(20),
        [FAX] varchar(20),
        [STREET] varchar(50),
        [POSTAL_CODE] varchar(6),
        [CITY] varchar(30),
        [COUNTRY] varchar(6),
        [STARTUP_MODULE] varchar(16),
        [CURRENCY] varchar(4),
        [OS_LOGIN] varchar(40),
        [OU_STRUCTURE_ACCESS] varchar(16),
        [USERS_RES_RSC] varchar(20),
        [LANGUAGE] varchar(6),
        [SKIN_ID] varchar(8),
        [OUTLOOK_PROF_NAME] varchar(40),
        [OUTLOOK_PROF_PW] varchar(20),
        [DAY_FORMAT] varchar(8),
        [NUMBER_FORMAT] varchar(8),
        [CURRENCY_FORMAT] varchar(8),
        [CALENDAR] varchar(20),
        [START_PERIOD] datetime default CONVERT(datetime,'01.01.1970',104) not null,
        [END_PERIOD] datetime default CONVERT(datetime,'01.01.1970',104) not null,
        [UNITS_DAY] float default 0 not null,
        [FACTOR_AMOUNT] float default 0 not null,
        [LEFT_ON] datetime default CONVERT(datetime,'01.01.1970',104) not null,
        [DEF_MIN_LOAD] float default 0 not null,
        [AUT_REP_HRS_WORKED] numeric(5,0) default 0 not null,
        [LOAD_LIMIT] numeric(5,0) default 0 not null,
        [CALENDAR_IS_ACTIVE] numeric(5,0) default 0 not null,
        [SUM_OF_EFFORT] numeric(5,0) default 0 not null,
        [SUMMARIZE_PERIODS] numeric(5,0) default 0 not null,
        [TITLE2] varchar(30),
        [RES_TO_BE_PLANED] numeric(5,0) default 0 not null,
        [SAP_COST_CENTER] varchar(20),
        [SAP_POSTING_RANGE] varchar(10),
        [RES_CLASS_1] varchar(2),
        [RES_CLASS_2] varchar(20),
        [OU_CCTR_ID] varchar(8),
        [DEF_LOAD_PROFILE] varchar(8),
        [DEF_MAX_LOAD] float default 0 not null,
        [RES_STRUCTURE_CODE] varchar(20),
        [RESOURCE_TYPE] varchar(2),
        [SHORT_LONG_TERM_PL] varchar(2),
        [WITHOUT_COST_CALC] numeric(5,0) default 0 not null,
        [VAC_CREDIT_LAST_YR] float default 0 not null,
        [VAC_CREDIT_CURR_YR] float default 0 not null,
        [FLEXHRS_CRED_PR_YEAR] float default 0 not null,
        [CALC_BASIS_DATE] float default 0 not null,
        [POSITION] float default 0 not null,
        [PARENT_RES_ID] varchar(20),
        [ANONYMIZE_TO] varchar(20),
        [RHW_OVERLAP_COMPL_2] numeric(5,0) default 0 not null,
        [REP_TYPE] numeric(5,0) default 0 not null,
        [ADJUSTMENT_BY_EFFORT] numeric(5,0) default 0 not null,
        [DEF_COST_TYPE] varchar(8),
        [CONVERSION_FACTOR_ID] varchar(8),
        [CREATE_STAKEHOLDER] numeric(5,0) default 0 not null
    )
;


-- ******************** CONSTRAINTS *******************************************

    alter table BELASTUNG_POOL 
        add constraint PID563DT563 primary key ([UUID])
;

    alter table INTERFACE_CONFIG 
        add constraint PID560DT560 primary key ([UUID])
;

    alter table INTERFACE_LOGGING 
        add constraint PID564DT564 primary key ([UUID])
;

    alter table INTERFACE_MAPPING 
        add constraint PID562DT562 primary key ([UUID])
;

    alter table INTERFACE_PARAMETER 
        add constraint PID561DT561 primary key ([UUID])
;

    alter table MAPPING_PARAMETER 
        add constraint PID565DT565 primary key ([UUID])
;

    alter table MITARBEITER_POOL 
        add constraint PID566DT566 primary key ([UUID])
;

    alter table INTERFACE_CONFIG 
        add constraint SINTERFACE_CONFIGID405C 
        foreign key ([POOL_MODULE]) 
        references DT405        
        
;

    alter table INTERFACE_CONFIG 
        add constraint SINTERFACE_CONFIGID405B 
        foreign key ([TARGET_MODULE]) 
        references DT405        
        
;

    alter table INTERFACE_CONFIG 
        add constraint SINTERFACE_CONFIGID405A 
        foreign key ([SOURCE_MODULE]) 
        references DT405        
        
;

    alter table INTERFACE_LOGGING 
        add constraint SINTERFACE_LOGGINGID560 
        foreign key ([CONFIG_ID]) 
        references INTERFACE_CONFIG        
        
;

    alter table INTERFACE_MAPPING 
        add constraint SINTERFACE_MAPPINGID560 
        foreign key ([CONFIG_ID]) 
        references INTERFACE_CONFIG        
        
;

    alter table INTERFACE_PARAMETER 
        add constraint SINTERFACE_PARAMETERID560 
        foreign key ([CONFIG_ID]) 
        references INTERFACE_CONFIG        
        
;

    alter table MAPPING_PARAMETER 
        add constraint SMAPPING_PARAMETERID562 
        foreign key ([MAPPING_ID]) 
        references INTERFACE_MAPPING        
        
;

    alter table MITARBEITER_POOL 
        add constraint SMITARBEITER_POOLID560 
        foreign key ([CONFIG_ID]) 
        references INTERFACE_CONFIG        
        
;

    alter table BELASTUNG_POOL 
        add constraint SBELASTUNG_POOLID560 
        foreign key ([CONFIG_ID]) 
        references INTERFACE_CONFIG