-- ABS-73813 / ABS-73891
-- Create table CHANGES_CUSTOMER
create table CHANGES_CUSTOMER
(
  CUSTOMER_ID          NUMBER default 0 not null,
  SYSTEMUSER_ID        NUMBER default 0 not null,
  TIMESTAMP            DATE default sysdate not null,
  TABLENAME            VARCHAR2(30) not null,
  FIELDNAME            VARCHAR2(30) not null,
  INSERT_UPDATE_DELETE VARCHAR2(1) default 'U' not null,
  UNIQUE_ID_FIELD1     VARCHAR2(30) not null,
  UNIQUE_ID_VALUE1     NUMBER not null,
  UNIQUE_ID_FIELD2     VARCHAR2(30) default 'NULL' not null,
  UNIQUE_ID_VALUE2     NUMBER default 0 not null,
  UNIQUE_ID_FIELD3     VARCHAR2(30) default 'NULL' not null,
  UNIQUE_ID_VALUE3     NUMBER default 0 not null,
  UNIQUE_ID_FIELD4     VARCHAR2(30) default 'NULL' not null,
  UNIQUE_ID_VALUE4     NUMBER default 0 not null,
  OLD_VALUE            VARCHAR2(1000) not null,
  NEW_VALUE            VARCHAR2(1000) not null,
  INVOICEHEADER_ID     NUMBER default -1 not null
);
-- Add comments to the columns 
comment on column CHANGES_CUSTOMER.INSERT_UPDATE_DELETE
  is 'Primary Key field 4 - ''I'' insert ''U'' update or ''D'' delete ';
comment on column CHANGES_CUSTOMER.INVOICEHEADER_ID
  is 'Link to invoiceheader when its shown on invoice, -1=No invoice linked!';
-- Create/Recreate primary, unique and foreign key constraints 
alter table CHANGES_CUSTOMER
  add constraint PK_CHANGES_CUSTOMER primary key (TIMESTAMP, TABLENAME, FIELDNAME, INSERT_UPDATE_DELETE, UNIQUE_ID_FIELD1, UNIQUE_ID_VALUE1, UNIQUE_ID_FIELD2, UNIQUE_ID_VALUE2, UNIQUE_ID_FIELD3, UNIQUE_ID_VALUE3, UNIQUE_ID_FIELD4, UNIQUE_ID_VALUE4);
-- Create/Recreate indexes 
create index INDX_CHANGES_CUSTOMER_CUST on CHANGES_CUSTOMER (CUSTOMER_ID ASC);
create index INDX_CHANGES_CUSTOMER_INVH on CHANGES_CUSTOMER (INVOICEHEADER_ID ASC);

-- Create table CHANGES_PLPD
create table CHANGES_PLPD
(
  CUSTOMER_ID          NUMBER not null,
  DEPARTMENT_ID        NUMBER not null,
  DISTRIBUTIONPOINT_ID NUMBER not null,
  PRODUCT_ID           NUMBER not null,
  SIZEDEFINITION_ID    NUMBER not null,
  FINISHINGMETHOD_ID   NUMBER not null,
  SYSTEMUSER_ID        NUMBER default 0 not null,
  TIMESTAMP            DATE default sysdate not null,
  TABLENAME            VARCHAR2(30) not null,
  FIELDNAME            VARCHAR2(30) not null,
  INSERT_UPDATE_DELETE VARCHAR2(1) default 'U' not null,
  UNIQUE_ID_FIELD1     VARCHAR2(30) default 'NULL' not null,
  UNIQUE_ID_VALUE1     NUMBER default 0 not null,
  UNIQUE_ID_FIELD2     VARCHAR2(30) default 'NULL' not null,
  UNIQUE_ID_VALUE2     NUMBER default 0 not null,
  UNIQUE_ID_FIELD3     VARCHAR2(30) default 'NULL' not null,
  UNIQUE_ID_VALUE3     NUMBER default 0 not null,
  UNIQUE_ID_FIELD4     VARCHAR2(30) default 'NULL' not null,
  UNIQUE_ID_VALUE4     NUMBER default 0 not null,
  OLD_VALUE            VARCHAR2(1000) not null,
  NEW_VALUE            VARCHAR2(1000) not null,
  INVOICEHEADER_ID     NUMBER default -1 not null,
  DATEACTIVE           DATE null,
  DATEINACTIVE         DATE null
);
-- Create/Recreate primary, unique and foreign key constraints 
alter table CHANGES_PLPD
  add constraint PK_CHANGES_PLPD primary key (TIMESTAMP, TABLENAME, FIELDNAME, INSERT_UPDATE_DELETE, UNIQUE_ID_FIELD1, UNIQUE_ID_VALUE1, UNIQUE_ID_FIELD2, UNIQUE_ID_VALUE2, UNIQUE_ID_FIELD3, UNIQUE_ID_VALUE3, UNIQUE_ID_FIELD4, UNIQUE_ID_VALUE4);
-- Create/Recreate indexes 
create index CHANGES_PLPD_INDX1 on CHANGES_PLPD (CUSTOMER_ID ASC, DEPARTMENT_ID ASC, DISTRIBUTIONPOINT_ID ASC, PRODUCT_ID ASC, SIZEDEFINITION_ID ASC, FINISHINGMETHOD_ID ASC);
create index CHANGES_PLPD_INVH on CHANGES_PLPD (INVOICEHEADER_ID ASC);

-- Create table CHANGES_WEARER
create table CHANGES_WEARER
(
  CUSTOMER_ID          NUMBER default 0 not null,
  WEARER_ID            NUMBER default 0 not null,
  SYSTEMUSER_ID        NUMBER default 0 not null,
  TIMESTAMP            DATE default sysdate not null,
  TABLENAME            VARCHAR2(30) not null,
  FIELDNAME            VARCHAR2(30) not null,
  INSERT_UPDATE_DELETE VARCHAR2(1) default 'U' not null,
  UNIQUE_ID_FIELD1     VARCHAR2(30) default 'NULL' not null,
  UNIQUE_ID_VALUE1     NUMBER default 0 not null,
  UNIQUE_ID_FIELD2     VARCHAR2(30) default 'NULL' not null,
  UNIQUE_ID_VALUE2     NUMBER default 0 not null,
  UNIQUE_ID_FIELD3     VARCHAR2(30) default 'NULL' not null,
  UNIQUE_ID_VALUE3     NUMBER default 0 not null,
  UNIQUE_ID_FIELD4     VARCHAR2(30) default 'NULL' not null,
  UNIQUE_ID_VALUE4     NUMBER default 0 not null,
  OLD_VALUE            VARCHAR2(1000) not null,
  NEW_VALUE            VARCHAR2(1000) not null,
  INVOICEHEADER_ID     NUMBER default -1 not null
);
-- Add comments to the columns 
comment on column CHANGES_WEARER.INSERT_UPDATE_DELETE
  is 'Primary Key field 4 - ''I'' insert ''U'' update or ''D'' delete ';
comment on column CHANGES_WEARER.INVOICEHEADER_ID
  is 'Link to invoiceheader when its shown on invoice, -1=No invoice linked!';
-- Create/Recreate indexes 
create index INDX_CHANGES_WEARER_INVH on CHANGES_WEARER (INVOICEHEADER_ID ASC);
create index INDX_CHANGES_WEARER_PK on CHANGES_WEARER (customer_id ASC, wearer_id ASC, timestamp ASC);