--Order 20012430 (Extension to alternative and unfinished sizes)

------------------------------------------SIZEGROUPINGCODE TABLE----------------------------

CREATE SEQUENCE SEQ_SIZEGROUPINGCODE
START WITH 1
INCREMENT BY 1;

CREATE TABLE SIZEGROUPINGCODE
(
	SIZEGROUPINGCODE_ID  NUMBER NOT NULL ,
	CODE                 VARCHAR2(12) NOT NULL ,
	DESCRIPTION          VARCHAR2(30) NOT NULL ,
	SYSTEMUSER_ID        NUMBER NOT NULL ,
	TIMESTAMP            DATE NOT NULL 
);


ALTER TABLE SIZEGROUPINGCODE
ADD CONSTRAINT  XPKSIZEGROUPINGCODE PRIMARY KEY (SIZEGROUPINGCODE_ID);


CREATE UNIQUE INDEX XAK1SIZEGROUPINGCODE ON SIZEGROUPINGCODE
(CODE   ASC);

----------------------------------------SIZEDEFINITION TABLE----------------------------------------

ALTER TABLE SIZEDEFINITION
	ADD (SIZEGROUPINGCODE_ID NUMBER NULL);


COMMENT ON COLUMN SIZEDEFINITION.SIZEGROUPINGCODE_ID IS 'May only be filled when the attribute ReserveAlternativeSize is set to ‘Y’.';

CREATE  INDEX XIF2SIZEDEFINITION ON SIZEDEFINITION
(SIZEGROUPINGCODE_ID   ASC);

ALTER TABLE SIZEDEFINITION
ADD CONSTRAINT SIZEDEFINITION_SIZEGROUPINGCOD FOREIGN KEY (SIZEGROUPINGCODE_ID) REFERENCES SIZEGROUPINGCODE (SIZEGROUPINGCODE_ID);

----------------------------------------PRODUCTSIZE TABLE-------------------------------------------

ALTER TABLE PRODUCTSIZE
	ADD (STANDARDLENGTH VARCHAR2(1)  DEFAULT 'N' NOT NULL CONSTRAINT YN_VALIDATIONFIELD63 CHECK (STANDARDLENGTH IN ('Y','N')));


CREATE  INDEX XIE1PRODUCTSIZE ON PRODUCTSIZE
(PRODUCT_ID   ASC,SIZEDEFINITION_ID   ASC,STANDARDLENGTH   ASC);


-----------------------------------------MODIFY ERP TABLES------------------------------------
-- Add/modify columns ERP_I_SIZE
alter table ERP_I_SIZE add SIZEGROUPINGCODE VARCHAR2(12);
alter table ERP_I_SIZE add SIZEGROUPINGDESCRIPTION VARCHAR2(30);
-- Add/modify columns ERP_I_PRODUCTSIZE
alter table ERP_I_PRODUCTSIZE add STANDARDLENGTH VARCHAR2(1) default 'Y' not null;
alter table ERP_I_PRODUCTSIZE add SIZEGROUPINGCODE VARCHAR2(12);
alter table ERP_I_PRODUCTSIZE add SIZEGROUPINGDESCRIPTION VARCHAR2(30);
-- Create/Recreate check constraints 
alter table ERP_I_PRODUCTSIZE
  add constraint CHK_ERP_I_PRODUCTSIZE_STDLEN
  check (STANDARDLENGTH IN ('Y','N'));

alter table ERP_O_WORKORDER add ORDERINGSTOCKROOM_CODE VARCHAR2(12);
alter table ERP_O_WORKORDER add ORDERINGSTOCKROOM_BU VARCHAR2(6);


