---Software order# 20013785
insert into ERP_TRANSACTION_LINK (ERP_TABLENAME, ERP_PROCEDURECALL)
values ('ERP_I_WEARER_LOG', 'ERP_INTERFACE.PROC_I_WEARER');

-- Create table ERP_I_WEARER 
create table ERP_I_WEARER 
( 
  TRANSFER_ID              NUMBER not null, 
  TRANSFER_STATUS          NUMBER default 0 not null, 
  CUSTOMERNUMBER           NUMBER not null, 
  DEPARTMENTNUMBER         NUMBER not null, 
  CONSUMPTIONNUMBER        NUMBER not null, 
  WEARERNUMBER             NUMBER not null, 
  WEAREREMPLOYMENTNUMBER   NUMBER default 1 not null, 
  FULLNAME                 VARCHAR2(40) not null, 
  SEARCHNAME               VARCHAR2(20) not null, 
  EMBLEMNAME               VARCHAR2(40), 
  FIRSTNAME                VARCHAR2(24), 
  CUSTOMEREMPLOYEENUMBER   VARCHAR2(10), 
  SEX                      VARCHAR2(1) default 'M' not null, 
  WEARERFUNCTIONCODE       VARCHAR2(6), 
  BANK                     VARCHAR2(6), 
  LOCKER                   VARCHAR2(6), 
  GARMENTDISPENSERCODE     VARCHAR2(6), 
  DATEACTIVE               DATE default trunc(sysdate)-1 not null, 
  DATEINACTIVE             DATE default to_date('31-12-2099','DD-MM-YYYY') not null, 
  REMARK                   VARCHAR2(300), 
  WEARERFLAGCODE           VARCHAR2(6), 
  WEARERFLAGSTARTDATE      DATE, 
  EMPLOYMENTFLAGCODE       VARCHAR2(6), 
  EMPLOYMENTFLAGSTARTDATE  DATE, 
  EMBROIDERYLETTERINGLINE1 VARCHAR2(35), 
  EMBROIDERYLETTERINGLINE2 VARCHAR2(35), 
  EMBROIDERYLETTERINGLINE3 VARCHAR2(35) 
); 
-- Add comments to the columns 
comment on column ERP_I_WEARER.WEAREREMPLOYMENTNUMBER 
  is 'Possible values 1 to 5'; 
comment on column ERP_I_WEARER.SEX 
  is 'M = Male, F = Female'; 
comment on column ERP_I_WEARER.DATEINACTIVE 
  is 'DATEINACTIVE must be >= DATEACTIVE'; 
-- Create/Recreate primary, unique and foreign key constraints 
alter table ERP_I_WEARER 
  add constraint PK_ERP_I_WEARER primary key (TRANSFER_ID); 
alter table ERP_I_WEARER 
  add constraint PK_ERP_I_WEARER_UK unique (TRANSFER_ID, CUSTOMERNUMBER, WEARERNUMBER, WEAREREMPLOYMENTNUMBER); 
-- Create/Recreate check constraints 
alter table ERP_I_WEARER 
  add constraint CHK_ERP_I_WEARER_DATE_CHK 
  check (DATEACTIVE <= DATEINACTIVE); 
alter table ERP_I_WEARER 
  add constraint CHK_ERP_I_WEARER_EMPLOYMENTNR 
  check (WEAREREMPLOYMENTNUMBER between 1 and 5); 
alter table ERP_I_WEARER 
  add constraint CHK_ERP_I_WEARER_SEX 
  check (SEX IN ('M','F')); 

-- Create table ERP_I_WEARERINVENTORY 
create table ERP_I_WEARERINVENTORY 
( 
  TRANSFER_ID            NUMBER not null, 
  TRANSFER_STATUS        NUMBER default 0 not null, 
  CUSTOMERNUMBER         NUMBER not null, 
  WEARERNUMBER           NUMBER not null, 
  WEAREREMPLOYMENTNUMBER NUMBER default 1 not null, 
  PRODUCTCODE            VARCHAR2(12) not null, 
  SIZECODE               VARCHAR2(12) not null, 
  FINISHINGMETHODCODE    VARCHAR2(6) default '-' not null, 
  SPECIALQUALITYGRADE    VARCHAR2(1), 
  MAXINVENTORY           NUMBER default 0 not null, 
  CHANGESPERWEEK         NUMBER default 0 not null, 
  FREEOFCHARGEQTY        NUMBER default 0 not null, 
  MAXFREEOFCHARGE        NUMBER default 0 not null, 
  STARTDATE              DATE default trunc(sysdate)-1 not null, 
  ENDDATE                DATE default to_date('31-12-2099','DD-MM-YYYY') not null, 
  EMBLEMTEMPLATE         VARCHAR2(6), 
  MODIFICATIONCODE1      VARCHAR2(10), 
  MODIFICATIONCODE2      VARCHAR2(10), 
  MODIFICATIONCODE3      VARCHAR2(10), 
  REMARK                 VARCHAR2(40) 
); 
-- Add comments to the columns 
comment on column ERP_I_WEARERINVENTORY.WEAREREMPLOYMENTNUMBER 
  is 'Possible values 1 to 5'; 
comment on column ERP_I_WEARERINVENTORY.MAXINVENTORY 
  is 'Must >= 0'; 
comment on column ERP_I_WEARERINVENTORY.CHANGESPERWEEK 
  is 'Must >= 0'; 
comment on column ERP_I_WEARERINVENTORY.FREEOFCHARGEQTY 
  is 'Must >= 0'; 
comment on column ERP_I_WEARERINVENTORY.MAXFREEOFCHARGE 
  is 'Must >= 0'; 
comment on column ERP_I_WEARERINVENTORY.ENDDATE 
  is 'ENDDATE >= STARTDATE'; 
-- Create/Recreate primary, unique and foreign key constraints 
alter table ERP_I_WEARERINVENTORY 
  add constraint PK_ERP_I_WEARERINVENTORY primary key (TRANSFER_ID, CUSTOMERNUMBER, WEARERNUMBER, WEAREREMPLOYMENTNUMBER, PRODUCTCODE, SIZECODE); 
alter table ERP_I_WEARERINVENTORY 
  add constraint FK_ERP_I_WEARERINVENTORY01 foreign key (TRANSFER_ID, CUSTOMERNUMBER, WEARERNUMBER, WEAREREMPLOYMENTNUMBER) 
  references ERP_I_WEARER (TRANSFER_ID, CUSTOMERNUMBER, WEARERNUMBER, WEAREREMPLOYMENTNUMBER) on delete cascade; 
-- Create/Recreate check constraints 
alter table ERP_I_WEARERINVENTORY 
  add constraint CHK_ERP_I_WEARERINV_CHANGES 
  check (CHANGESPERWEEK >= 0); 
alter table ERP_I_WEARERINVENTORY 
  add constraint CHK_ERP_I_WEARERINV_DATE_CHK 
  check (STARTDATE <= ENDDATE); 
alter table ERP_I_WEARERINVENTORY 
  add constraint CHK_ERP_I_WEARERINV_EMPNUMBER 
  check (WEAREREMPLOYMENTNUMBER between 1 and 5); 
alter table ERP_I_WEARERINVENTORY 
  add constraint CHK_ERP_I_WEARERINV_FREEOFCHR 
  check (FREEOFCHARGEQTY >= 0); 
alter table ERP_I_WEARERINVENTORY 
  add constraint CHK_ERP_I_WEARERINV_MAXFREE 
  check (MAXFREEOFCHARGE >= 0); 
alter table ERP_I_WEARERINVENTORY 
  add constraint CHK_ERP_I_WEARERINV_MAXINV 
  check (MAXINVENTORY >= 0); 

-- Create table ERP_I_WEARER_LOG 
create table ERP_I_WEARER_LOG 
( 
  TRANSFER_ID     NUMBER not null, 
  TRANSFER_STATUS NUMBER default 0 not null, 
  TEXT            VARCHAR2(1000), 
  PROCESS_STATUS  NUMBER default 0 not null, 
  TIMESTAMP       DATE default SYSDATE not null 
); 
-- Add comments to the columns 
comment on column ERP_I_WEARER_LOG.TRANSFER_STATUS 
  is '0 = ready to be read, 1 = read correctly'; 
comment on column ERP_I_WEARER_LOG.PROCESS_STATUS 
  is '0 = processed correctly, 1 = process stopped with errors'; 
-- Create/Recreate primary, unique and foreign key constraints 
alter table ERP_I_WEARER_LOG 
  add constraint PK_ERP_I_WEARER_LOG primary key (TRANSFER_ID); 
alter table ERP_I_WEARER_LOG 
  add constraint FK_ERP_I_WEARER_LOG foreign key (TRANSFER_ID) 
  references ERP_I_WEARER (TRANSFER_ID) on delete cascade; 