--ABSN-1003

--PACKINGFAMILY TABLE
CREATE SEQUENCE SEQ_PACKINGFAMILY
START WITH 1
INCREMENT BY 1;

CREATE TABLE PACKINGFAMILY
(
PACKINGFAMILY_ID				NUMBER NOT NULL,
CODE     						VARCHAR2(8) NOT NULL,
ACTIVE							VARCHAR2(1) DEFAULT 'N' NOT NULL,
CREATIONSYSTEMUSER_ID			NUMBER NOT NULL,
CREATIONTIMESTAMP				DATE DEFAULT SYSDATE NOT NULL,
SYSTEMUSER_ID					NUMBER NOT NULL,
TIMESTAMP						DATE DEFAULT SYSDATE NOT NULL
);

ALTER TABLE PACKINGFAMILY ADD CONSTRAINT XPKPACKINGFAMILY PRIMARY KEY (PACKINGFAMILY_ID);

CREATE UNIQUE INDEX XAK1PACKINGFAMILY ON PACKINGFAMILY (CODE ASC);

ALTER TABLE PACKINGFAMILY ADD CONSTRAINT CHK_PACKINGFAMILY_CODE CHECK (CODE = UPPER(CODE));

ALTER TABLE PACKINGFAMILY ADD CONSTRAINT CHK_PACKINGFAMILY_ACTIVE CHECK (ACTIVE IN ('Y', 'N'));


--PACKINGFAMILY_DESC TABLE
CREATE TABLE PACKINGFAMILY_DESC
(
PACKINGFAMILY_ID				NUMBER NOT NULL,
LANGUAGE_ID						NUMBER NOT NULL,
DESCRIPTION						VARCHAR2(30) NOT NULL,
SYSTEMUSER_ID					NUMBER NOT NULL,
TIMESTAMP						DATE DEFAULT SYSDATE NOT NULL
);

ALTER TABLE PACKINGFAMILY_DESC ADD CONSTRAINT XPKPACKINGFAMILY_DESC PRIMARY KEY (PACKINGFAMILY_ID, LANGUAGE_ID);

CREATE INDEX XIF1PACKINGFAMILY_DESC ON PACKINGFAMILY_DESC (PACKINGFAMILY_ID ASC);
CREATE INDEX XIF2PACKINGFAMILY_DESC ON PACKINGFAMILY_DESC (LANGUAGE_ID ASC);

ALTER TABLE PACKINGFAMILY_DESC
	ADD CONSTRAINT FK_PACKINGFAMILY_DESC_PACKINGFAMILY_ID FOREIGN KEY (PACKINGFAMILY_ID) REFERENCES PACKINGFAMILY(PACKINGFAMILY_ID) ON DELETE CASCADE;

ALTER TABLE PACKINGFAMILY_DESC
	ADD CONSTRAINT FK_PACKINGFAMILY_DESC_LANGUAGE_ID FOREIGN KEY (LANGUAGE_ID) REFERENCES LANGUAGES(LANGUAGE_ID);


--MACHINEPACKINGFAMILY TABLE
CREATE SEQUENCE SEQ_MACHINEPACKINGFAMILY
START WITH 1
INCREMENT BY 1;

CREATE TABLE MACHINEPACKINGFAMILY
(
MACHINEPACKINGFAMILY_ID			NUMBER NOT NULL,
MACHINE_ID      				NUMBER NOT NULL,
PACKINGFAMILY_ID				NUMBER NOT NULL,
PRIMARY							VARCHAR2(1) DEFAULT 'Y' NOT NULL,
CREATIONSYSTEMUSER_ID			NUMBER NOT NULL,
CREATIONTIMESTAMP				DATE DEFAULT SYSDATE NOT NULL,
SYSTEMUSER_ID					NUMBER NOT NULL,
TIMESTAMP						DATE DEFAULT SYSDATE NOT NULL
);

ALTER TABLE MACHINEPACKINGFAMILY ADD CONSTRAINT XPKMACHINEPACKINGFAMILY PRIMARY KEY (MACHINEPACKINGFAMILY_ID);

CREATE UNIQUE INDEX XAK1MACHINEPACKINGFAMILY ON MACHINEPACKINGFAMILY (MACHINE_ID, PACKINGFAMILY_ID);

CREATE INDEX XIF1MACHINEPACKINGFAMILY ON MACHINEPACKINGFAMILY (MACHINE_ID ASC);
CREATE INDEX XIF2MACHINEPACKINGFAMILY ON MACHINEPACKINGFAMILY (PACKINGFAMILY_ID ASC);

ALTER TABLE MACHINEPACKINGFAMILY
	ADD CONSTRAINT FK_MACHINEPACKINGFAMILY_MACHINE_ID FOREIGN KEY (MACHINE_ID) REFERENCES MACHINE(MACHINE_ID);

ALTER TABLE MACHINEPACKINGFAMILY
	ADD CONSTRAINT FK_MACHINEPACKINGFAMILY_PACKINGFAMILY_ID FOREIGN KEY (PACKINGFAMILY_ID) REFERENCES PACKINGFAMILY(PACKINGFAMILY_ID);

ALTER TABLE MACHINEPACKINGFAMILY ADD CONSTRAINT CHK_MACHINEPACKINGFAMILY_PRIMARY CHECK (PRIMARY IN ('Y', 'N'));


--PLANTDEPARTMENTMACHINE TABLE
ALTER TABLE PLANTDEPARTMENTMACHINE ADD (ENABLEBAGREGISTRATION VARCHAR2(1) DEFAULT 'N' NOT NULL CONSTRAINT CHK_PLANTDEPARTMENTMACHINE_ENABLEBAGREGISTRATION CHECK (ENABLEBAGREGISTRATION IN ('Y', 'N')));

COMMENT ON COLUMN PLANTDEPARTMENTMACHINE.ENABLEBAGREGISTRATION IS 'Defines if machine may be used to pack items in bag for plant department.';


--PLANTPRODUCTSETTINGS TABLE
ALTER TABLE PLANTPRODUCTSETTINGS ADD (PACKINGFAMILY_ID NUMBER DEFAULT NULL); 

CREATE INDEX XIF7PLANTPRODUCTSETTINGS ON PLANTPRODUCTSETTINGS (PACKINGFAMILY_ID ASC);

ALTER TABLE PLANTPRODUCTSETTINGS 
	ADD CONSTRAINT FK_PLANTPRODUCTSETTINGS_PACKINGFAMILY_ID FOREIGN KEY (PACKINGFAMILY_ID) REFERENCES PACKINGFAMILY(PACKINGFAMILY_ID);


--PRODUCT TABLE
ALTER TABLE PRODUCT ADD (PACKINGFAMILY_ID NUMBER DEFAULT NULL);

COMMENT ON COLUMN PRODUCT.PACKINGFAMILY_ID IS 'The attribute will and can only be filled when the LINEN module is active.'; 

CREATE INDEX XIF26PRODUCTS ON PRODUCT (PACKINGFAMILY_ID ASC);

ALTER TABLE PRODUCT 
	ADD CONSTRAINT FK_PRODUCT_PACKINGFAMILY_ID FOREIGN KEY (PACKINGFAMILY_ID) REFERENCES PACKINGFAMILY(PACKINGFAMILY_ID);


--PRODUCTGROUP TABLE
ALTER TABLE PRODUCTGROUP ADD (USEPACKINGFAMILY VARCHAR2(1) DEFAULT 'N' NOT NULL CONSTRAINT CHK_PRODUCTGROUP_USEPACKINGFAMILY CHECK (USEPACKINGFAMILY IN ('Y', 'N')));

COMMENT ON COLUMN PRODUCTGROUP.USEPACKINGFAMILY IS 'Defines whether it is allowed to define a packing family for the products belonging to this product group. Only used for module LINEN.';
