--  ABS-19195

----------  STOCKROOM  --------------
ALTER TABLE STOCKROOM ADD (SUPPLYCHAINPERGRADECATEGORY NUMBER DEFAULT 0 NOT NULL CONSTRAINT SUPCHAINPERGRADECAT_VALID1_1  CHECK (SUPPLYCHAINPERGRADECATEGORY IN (0,1)));

COMMENT ON COLUMN STOCKROOM.SUPPLYCHAINPERGRADECATEGORY
  IS 'The attribute indicates whether a specific supply chain may be setup per grade category. Possible values:
0 = Not allowed
1 = Allowed';


----------- STOCKROOMPRODUCT  ---------------
CREATE SEQUENCE SEQ_STOCKROOMPRODUCT
START WITH 1
INCREMENT BY 1;  

CREATE TABLE STOCKROOMPRODUCT
(
  STOCKROOMPRODUCT_ID         NUMBER NOT NULL,
  STOCKROOM_ID                NUMBER NOT NULL,
  PRODUCT_ID                  NUMBER NOT NULL,
  CUSTOMER_ID                 NUMBER NULL,
  CUSTOMEROWNED               VARCHAR2(1) DEFAULT 'N' NOT NULL CONSTRAINT YN_VALIDATIONFIELD256 CHECK (CUSTOMEROWNED IN ('Y','N')),
  SUPPLYCHAINPERGRADECATEGORY NUMBER DEFAULT 0 NOT NULL CONSTRAINT SUPCHAINPERGRADECAT_VALID1_2 CHECK (SUPPLYCHAINPERGRADECATEGORY IN (0,1)),
  SYSTEMUSER_ID               NUMBER NOT NULL,
  TIMESTAMP                   DATE NOT NULL
);

COMMENT ON COLUMN STOCKROOMPRODUCT.CUSTOMER_ID
  IS 'Will only be filled in case of customer related and/or customer owned items. ';
  
COMMENT ON COLUMN STOCKROOMPRODUCT.CUSTOMEROWNED
  IS 'Indicates whether the characteristics to be defined are for a customer owned product or a laundry owned product. Possible values:
‘N’ = Laundry owned
‘Y’ = Customer owned';

COMMENT ON COLUMN STOCKROOMPRODUCT.SUPPLYCHAINPERGRADECATEGORY
  IS 'The attribute indicates whether a specific supply chain may be setup per grade category. Possible values:
0 = Not allowed
1 = Allowed';

ALTER TABLE STOCKROOMPRODUCT
  ADD CONSTRAINT XPKSTOCKROOMPRODUCT PRIMARY KEY (STOCKROOMPRODUCT_ID);
  
CREATE UNIQUE INDEX XAK1STOCKROOMPRODUCT ON STOCKROOMPRODUCT (STOCKROOM_ID ASC, PRODUCT_ID ASC, CUSTOMER_ID ASC, CUSTOMEROWNED ASC);

CREATE INDEX XIF1STOCKROOMPRODUCT ON STOCKROOMPRODUCT (STOCKROOM_ID ASC);

CREATE INDEX XIF2STOCKROOMPRODUCT ON STOCKROOMPRODUCT (PRODUCT_ID ASC);

CREATE INDEX XIF3STOCKROOMPRODUCT ON STOCKROOMPRODUCT (CUSTOMER_ID ASC);

ALTER TABLE STOCKROOMPRODUCT
  ADD CONSTRAINT STOCKROOMPRODUCT_CUST_ID FOREIGN KEY (CUSTOMER_ID)
  REFERENCES CUSTOMER (CUSTOMER_ID) ON DELETE SET NULL ;
  
ALTER TABLE STOCKROOMPRODUCT
  ADD CONSTRAINT STOCKROOMPRODUCT_STK_ID FOREIGN KEY (STOCKROOM_ID)
  REFERENCES STOCKROOM (STOCKROOM_ID);
  
ALTER TABLE STOCKROOMPRODUCT
  ADD CONSTRAINT STOCKROOMPRODUCT_PROD_ID FOREIGN KEY (PRODUCT_ID)
  REFERENCES PRODUCT (PRODUCT_ID);
  
ALTER TABLE STOCKROOMPRODUCT
  ADD CONSTRAINT STOCKROOMPRODUCT_VALID
  CHECK ((CUSTOMER_ID IS NULL AND CUSTOMEROWNED = 'N') OR CUSTOMER_ID IS NOT NULL);
  

  
-------------  STOCKDEFINITIONPERITEM -------------------
ALTER TABLE STOCKDEFINITIONPERITEM ADD (SUPPLYCHAINPERGRADECATEGORY NUMBER DEFAULT 0 NOT NULL CONSTRAINT SUPCHAINPERGRADECAT_VALID1_3 CHECK (SUPPLYCHAINPERGRADECATEGORY IN (0,1)),
										STOCKROOMPRODUCT_ID 	    NUMBER NULL);

ALTER TABLE STOCKDEFINITIONPERITEM MODIFY (DEFAULTSUPPLIER_ID NULL);

CREATE INDEX XIF6STOCKDEFINITIONPERITEM ON STOCKDEFINITIONPERITEM (STOCKROOMPRODUCT_ID ASC); 

ALTER TABLE STOCKDEFINITIONPERITEM
  ADD CONSTRAINT STKDEFPERITEM_STK_PRD_ID FOREIGN KEY (STOCKROOMPRODUCT_ID)
  REFERENCES STOCKROOMPRODUCT (STOCKROOMPRODUCT_ID) ON DELETE CASCADE;
  
COMMENT ON COLUMN STOCKDEFINITIONPERITEM.SUPPLYCHAINPERGRADECATEGORY
  IS 'Possible values:
0 = Not allowed
1 = Allowed';  
  
-------------- SUPPLYCHAINRULES  ------------------------
CREATE SEQUENCE SEQ_SUPPLYCHAINRULES
START WITH 1
INCREMENT BY 1;  

CREATE TABLE SUPPLYCHAINRULES
(
  SUPPLYCHAINRULES_ID          NUMBER NOT NULL,
  STOCKROOM_ID                 NUMBER NULL,
  STOCKROOMPRODUCT_ID          NUMBER NULL,
  STOCKDEFINITIONPERITEM_ID    NUMBER NULL,
  DEFINITIONLEVEL              NUMBER DEFAULT 1 NOT NULL CONSTRAINT DEFINITIONLEVEL_VALID1_1 CHECK (DEFINITIONLEVEL IN (1,2,3)),
  GRADECATEGORY                NUMBER DEFAULT 0 NOT NULL CONSTRAINT GRADECATEGORY_VALID1_1 CHECK (GRADECATEGORY IN (0,1,2)),
  PURCHASINGSTOCKROOM          NUMBER DEFAULT 1 NOT NULL CONSTRAINT PURCHASINGSTOCKROOM_VALID1_1 CHECK (PURCHASINGSTOCKROOM IN (1,2)),
  PREFERREDINTERNALSUPPLIER_ID NUMBER NULL,
  PREFERREDEXTERNALSUPPLIER_ID NUMBER NULL ,
  FIXEDINTERNALSUPPLIER        NUMBER DEFAULT 0 NOT NULL CONSTRAINT FIXEDINTERNALSUPPLIER_VALID1_1 CHECK (FIXEDINTERNALSUPPLIER IN (0,1)),
  FIXEDEXTERNALSUPPLIER        NUMBER DEFAULT 0 NOT NULL CONSTRAINT FIXEDEXTERNALSUPPLIER_VALID1_1 CHECK (FIXEDEXTERNALSUPPLIER IN (0,1)),
  SPECIFICRULELEVEL            NUMBER DEFAULT 1 NOT NULL CONSTRAINT SPECIFICRULELEVEL_VALID1_1 CHECK (SPECIFICRULELEVEL IN (1,2,3)),
  SYSTEMUSER_ID                NUMBER NOT NULL,
  TIMESTAMP                    DATE   NOT NULL
);

COMMENT ON COLUMN SUPPLYCHAINRULES.STOCKROOM_ID
  IS 'Will only be filled in case of a setup defined at stockroom / product level';
  
COMMENT ON COLUMN SUPPLYCHAINRULES.STOCKROOMPRODUCT_ID
  IS 'Will only be filled in case of a setup defined at stockroom / product level';
  
COMMENT ON COLUMN SUPPLYCHAINRULES.STOCKDEFINITIONPERITEM_ID
  IS 'Will only be filled in case of a setup defined at stockroom / product / size level';
  
COMMENT ON COLUMN SUPPLYCHAINRULES.DEFINITIONLEVEL
  IS 'Possible values:
1 = Stockroom
2 = Stockroom product
3 = Stockroom product size';

COMMENT ON COLUMN SUPPLYCHAINRULES.GRADECATEGORY
  IS 'Possible values
0 = All categories
1 = Category new only
2 = Category used only';

COMMENT ON COLUMN SUPPLYCHAINRULES.PURCHASINGSTOCKROOM
  IS 'Possible values:
1 = Current stockroom will order
2 = Other stockroom will order';

COMMENT ON COLUMN SUPPLYCHAINRULES.FIXEDINTERNALSUPPLIER
  IS 'The attribute indicates whether the defined internal supplier is fixed or not (e.g. in case a chain is defined, the chain is ignored when the supplier is defined as fixed). Possible values:
0 = Defined internal supplier is not fixed
1 = Defined internal supplier is fixed';

COMMENT ON COLUMN SUPPLYCHAINRULES.FIXEDEXTERNALSUPPLIER
  IS 'The attribute indicates whether the defined external supplier is fixed or not (e.g. in case a chain is defined, the chain is ignored when the supplier is defined as fixed) Possible values:
0 = Defined external supplier is not fixed
1 = Defined external supplier is fixed';

COMMENT ON COLUMN SUPPLYCHAINRULES.SPECIFICRULELEVEL
  IS 'The attribute indicates from which internal supply chain level the rules will be applied for the current definition level. Possible values:
1 = Stockroom
2 = Stockroom product
3 = Stockroom product size';

ALTER TABLE SUPPLYCHAINRULES
  ADD CONSTRAINT XPKSUPPLYCHAINRULES PRIMARY KEY (SUPPLYCHAINRULES_ID);

CREATE UNIQUE INDEX XAK1SUPPLYCHAINRULES ON SUPPLYCHAINRULES (STOCKROOM_ID ASC, STOCKROOMPRODUCT_ID ASC, STOCKDEFINITIONPERITEM_ID ASC, DEFINITIONLEVEL ASC, GRADECATEGORY ASC);

CREATE INDEX XIF1SUPPLYCHAINRULES ON SUPPLYCHAINRULES (STOCKROOM_ID ASC);

CREATE INDEX XIF2SUPPLYCHAINRULES ON SUPPLYCHAINRULES (STOCKROOMPRODUCT_ID ASC);

CREATE INDEX XIF3SUPPLYCHAINRULES ON SUPPLYCHAINRULES (STOCKDEFINITIONPERITEM_ID ASC);

CREATE INDEX XIF4SUPPLYCHAINRULES ON SUPPLYCHAINRULES (PREFERREDINTERNALSUPPLIER_ID ASC);

CREATE INDEX XIF5SUPPLYCHAINRULES ON SUPPLYCHAINRULES (PREFERREDEXTERNALSUPPLIER_ID ASC);

ALTER TABLE SUPPLYCHAINRULES
  ADD CONSTRAINT SUPPLYCHAINRULES_STK_ID FOREIGN KEY (STOCKROOM_ID)
  REFERENCES STOCKROOM (STOCKROOM_ID) ON DELETE CASCADE;
  
ALTER TABLE SUPPLYCHAINRULES
  ADD CONSTRAINT SUPPLYCHAINRULES_STKPRD_ID FOREIGN KEY (STOCKROOMPRODUCT_ID)
  REFERENCES STOCKROOMPRODUCT (STOCKROOMPRODUCT_ID) ON DELETE CASCADE;
  
ALTER TABLE SUPPLYCHAINRULES
  ADD CONSTRAINT SUPPLYCHAINRULES_SDI_ID FOREIGN KEY (STOCKDEFINITIONPERITEM_ID)
  REFERENCES STOCKDEFINITIONPERITEM (STOCKDEFINITIONPERITEM_ID) ON DELETE CASCADE;
  
ALTER TABLE SUPPLYCHAINRULES
  ADD CONSTRAINT SUPPCHRULES_PREFINTSUPP_ID FOREIGN KEY (PREFERREDINTERNALSUPPLIER_ID)
  REFERENCES SUPPLIER (SUPPLIER_ID) ON DELETE SET NULL;
  
ALTER TABLE SUPPLYCHAINRULES
  ADD CONSTRAINT SUPPCHRULES_PREFEXTSUPP_ID FOREIGN KEY (PREFERREDEXTERNALSUPPLIER_ID)
  REFERENCES SUPPLIER (SUPPLIER_ID) ON DELETE SET NULL;
  
ALTER TABLE SUPPLYCHAINRULES
  ADD CONSTRAINT SUPPLYCHAINRULES_VALID2
  CHECK   (SPECIFICRULELEVEL <= DEFINITIONLEVEL);
  
ALTER TABLE SUPPLYCHAINRULES
  ADD CONSTRAINT SUPPLYCHAINRULES_VALID1
  CHECK (((STOCKROOM_ID IS NOT NULL) AND (STOCKROOMPRODUCT_ID IS NULL) AND (STOCKDEFINITIONPERITEM_ID IS NULL) AND (DEFINITIONLEVEL = 1)) OR
((STOCKROOM_ID IS NULL) AND (STOCKROOMPRODUCT_ID IS NOT NULL) AND (STOCKDEFINITIONPERITEM_ID IS NULL) AND (DEFINITIONLEVEL = 2)) OR
((STOCKROOM_ID IS NULL) AND (STOCKROOMPRODUCT_ID IS NULL) AND (STOCKDEFINITIONPERITEM_ID IS NOT NULL) AND (DEFINITIONLEVEL = 3)));


------------------ INTERNALSUPPLYCHAIN  -----------------
CREATE SEQUENCE SEQ_INTERNALSUPPLYCHAIN
START WITH 1
INCREMENT BY 1;  

CREATE TABLE INTERNALSUPPLYCHAIN
(
  INTERNALSUPPLYCHAIN_ID    NUMBER NOT NULL,
  STOCKROOM_ID              NUMBER NULL,
  STOCKROOMPRODUCT_ID       NUMBER NULL,
  STOCKDEFINITIONPERITEM_ID NUMBER NULL,
  DEFINITIONLEVEL           NUMBER DEFAULT 1 NOT NULL CONSTRAINT DEFINITIONLEVEL_VALID1_2 CHECK (DEFINITIONLEVEL IN (1,2,3)),
  GRADECATEGORY             NUMBER DEFAULT 0 NOT NULL CONSTRAINT GRADECATEGORY_VALID1_2 CHECK (GRADECATEGORY IN (0,1,2)),
  SEQUENCENUMBER            NUMBER DEFAULT 1 NOT NULL,
  SUPPLIER_ID               NUMBER NOT NULL,
  ACTIVE                    VARCHAR2(1) DEFAULT 'Y' NOT NULL CONSTRAINT YN_VALIDATIONFIELD269 CHECK (ACTIVE IN ('N','Y')),
  SYSTEMUSER_ID             NUMBER NOT NULL,
  TIMESTAMP                 DATE NOT NULL
);

COMMENT ON COLUMN INTERNALSUPPLYCHAIN.STOCKROOM_ID
  IS 'Will only be filled in case of a chain defined at stockroom level';
  
COMMENT ON COLUMN INTERNALSUPPLYCHAIN.STOCKROOMPRODUCT_ID
  IS 'Will only be filled in case of a chain defined at stockroom / product level';
  
COMMENT ON COLUMN INTERNALSUPPLYCHAIN.STOCKDEFINITIONPERITEM_ID
  IS 'Will only be filled in case of a chain defined at stockroom / product / size level';
  
COMMENT ON COLUMN INTERNALSUPPLYCHAIN.DEFINITIONLEVEL
  IS 'Possible values:
1 = Stockroom
2 = Stockroom product
3 = Stockroom product size';

COMMENT ON COLUMN INTERNALSUPPLYCHAIN.GRADECATEGORY
  IS 'Possible values
0 = All categories
1 = Category new only
2 = Category used only';

COMMENT ON COLUMN INTERNALSUPPLYCHAIN.SEQUENCENUMBER
  IS 'Indicates the order for this internal supplier in the chain for the current definition level. ';

ALTER TABLE INTERNALSUPPLYCHAIN
  ADD CONSTRAINT XPKINTERNALSUPPLYCHAIN PRIMARY KEY (INTERNALSUPPLYCHAIN_ID);

CREATE INDEX XIF1INTERNALSUPPLYCHAIN ON INTERNALSUPPLYCHAIN (STOCKROOM_ID ASC);

CREATE INDEX XIF2INTERNALSUPPLYCHAIN ON INTERNALSUPPLYCHAIN (STOCKROOMPRODUCT_ID ASC);

CREATE INDEX XIF3INTERNALSUPPLYCHAIN ON INTERNALSUPPLYCHAIN (STOCKDEFINITIONPERITEM_ID ASC);

CREATE INDEX XIF4INTERNALSUPPLYCHAIN ON INTERNALSUPPLYCHAIN (SUPPLIER_ID ASC);

ALTER TABLE INTERNALSUPPLYCHAIN
  ADD CONSTRAINT INTERNALSUPPLYCHAIN_STK_ID FOREIGN KEY (STOCKROOM_ID)
  REFERENCES STOCKROOM (STOCKROOM_ID) ON DELETE CASCADE;
  
ALTER TABLE INTERNALSUPPLYCHAIN
  ADD CONSTRAINT INTERNALSUPPLYCHAIN_STKPROD_ID FOREIGN KEY (STOCKROOMPRODUCT_ID)
  REFERENCES STOCKROOMPRODUCT (STOCKROOMPRODUCT_ID) ON DELETE CASCADE;
  
ALTER TABLE INTERNALSUPPLYCHAIN
  ADD CONSTRAINT INTERNALSUPPLYCHAIN_SDI_ID FOREIGN KEY (STOCKDEFINITIONPERITEM_ID)
  REFERENCES STOCKDEFINITIONPERITEM (STOCKDEFINITIONPERITEM_ID) ON DELETE CASCADE;
  
ALTER TABLE INTERNALSUPPLYCHAIN
  ADD CONSTRAINT INTERNALSUPPLYCHAIN_SUPP_ID FOREIGN KEY (SUPPLIER_ID)
  REFERENCES SUPPLIER (SUPPLIER_ID);
  
ALTER TABLE INTERNALSUPPLYCHAIN
  ADD CONSTRAINT INTERNALSUPPLYCHAIN_VALID1_1
  CHECK (((STOCKROOM_ID IS NOT NULL) AND (STOCKROOMPRODUCT_ID IS NULL) AND (STOCKDEFINITIONPERITEM_ID IS NULL) AND (DEFINITIONLEVEL = 1)) OR
((STOCKROOM_ID IS NULL) AND (STOCKROOMPRODUCT_ID IS NOT NULL) AND (STOCKDEFINITIONPERITEM_ID IS NULL) AND (DEFINITIONLEVEL = 2)) OR
((STOCKROOM_ID IS NULL) AND (STOCKROOMPRODUCT_ID IS NULL) AND (STOCKDEFINITIONPERITEM_ID IS NOT NULL) AND (DEFINITIONLEVEL = 3)));
