--From PVD (order 502356)

-- Create table
create table I_STOCKDEFINITIONPERITEM
(
  STOCKROOMCODE         VARCHAR2(12) not null,
  PRODUCTCODE           VARCHAR2(12) not null,
  SIZECODE              VARCHAR2(12) not null,
  CUSTOMERNUMBER        NUMBER,
  CUSTOMEROWNED         VARCHAR2(1) default 'N' not null,
  MINQTYNEW             NUMBER(8) default 0 not null,
  MINQTYUSED            NUMBER(8) default 0 not null,
  MAXQTYNEW             NUMBER(8) default 9999 not null,
  MAXQTYUSED            NUMBER(8) default 9999 not null,
  DEFAULTSUPPLIERNUMBER NUMBER(8) not null
)
tablespace ABS_DATA
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );
-- Create/Recreate primary, unique and foreign key constraints 
alter table I_STOCKDEFINITIONPERITEM
  add constraint UK2I_STOCKDEFINITIONPERITEM unique (STOCKROOMCODE, PRODUCTCODE, SIZECODE, CUSTOMERNUMBER, CUSTOMEROWNED)
  using index 
  tablespace ABS_DATA
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );
alter table I_STOCKDEFINITIONPERITEM
  add constraint FKI_STOCKDEFPERITEM_CUST foreign key (CUSTOMERNUMBER)
  references I_CUSTOMER (CUSTOMERNUMBER);
alter table I_STOCKDEFINITIONPERITEM
  add constraint FKI_STOCKDEFPERITEM_PROD foreign key (PRODUCTCODE)
  references I_PRODUCT (CODE);
-- Create/Recreate check constraints 
alter table I_STOCKDEFINITIONPERITEM
  add constraint CHKI_STOCKDEFPERITEMCUSTOWNED
  check (CUSTOMEROWNED IN ('Y','N'));
alter table I_STOCKDEFINITIONPERITEM
  add constraint CHKI_STOCKDEFPERITEMMAXQTYNEW
  check (MAXQTYNEW BETWEEN 0 AND 99999999);
alter table I_STOCKDEFINITIONPERITEM
  add constraint CHKI_STOCKDEFPERITEMMAXQTYUSED
  check (MAXQTYUSED BETWEEN 0 AND 99999999);
alter table I_STOCKDEFINITIONPERITEM
  add constraint CHKI_STOCKDEFPERITEMMINQTYNEW
  check (MINQTYNEW BETWEEN 0 AND 99999999);
alter table I_STOCKDEFINITIONPERITEM
  add constraint CHKI_STOCKDEFPERITEMMINQTYUSED
  check (MINQTYUSED BETWEEN 0 AND 99999999);
