--Order 20013728.70

----WEARERINVOICECATEGORY table----

CREATE SEQUENCE SEQ_WEARERINVOICECATEGORY
START WITH 1
INCREMENT BY 1;

CREATE TABLE WEARERINVOICECATEGORY
(
  WEARERINVOICECATEGORY_ID      NUMBER NOT NULL,
  CUSTOMER_ID                   NUMBER NOT NULL,
  CODE                          VARCHAR2(6) NOT NULL,
  DESCRIPTION                   VARCHAR2(30) NOT NULL,
  EMPLOYERPERCENTAGE            NUMBER(3) DEFAULT 0 NOT NULL CONSTRAINT EMPLOYERPERCENTAGE_VALID1_1 
                                          CHECK (EMPLOYERPERCENTAGE BETWEEN 0 AND 100),
  MAXEMPLOYERAMOUNT             NUMBER DEFAULT 0 NOT NULL,
  SYSTEMUSER_ID                 NUMBER NOT NULL,
  TIMESTAMP                     DATE NOT NULL
);

ALTER TABLE WEARERINVOICECATEGORY
ADD CONSTRAINT  XPKWEARERINVOICECATEGORY PRIMARY KEY (WEARERINVOICECATEGORY_ID);

CREATE UNIQUE INDEX XAK1WEARERINVOICECATEGORY ON WEARERINVOICECATEGORY (CUSTOMER_ID, CODE);

CREATE INDEX XIF1WEARERINVOICECATEGORY ON WEARERINVOICECATEGORY (CUSTOMER_ID);

ALTER TABLE WEARERINVOICECATEGORY
ADD CONSTRAINT WEARERINVCATEG_CUSTOMER FOREIGN KEY (CUSTOMER_ID) REFERENCES CUSTOMER (CUSTOMER_ID) ON DELETE CASCADE;


----WEARERINVOICECATEGORYPRODUCT table----


CREATE TABLE WEARERINVOICECATEGORYPRODUCT
(
  WEARERINVOICECATEGORY_ID      NUMBER NOT NULL,
  PRODUCT_ID                    NUMBER NOT NULL,
  SYSTEMUSER_ID                 NUMBER NOT NULL,
  TIMESTAMP                     DATE NOT NULL
);

ALTER TABLE WEARERINVOICECATEGORYPRODUCT
ADD CONSTRAINT  XPKWEARERINVCATEGORYPRODUCT PRIMARY KEY (WEARERINVOICECATEGORY_ID, PRODUCT_ID);

CREATE INDEX XIF1WEARERINVCATEGORYPRODUCT ON WEARERINVOICECATEGORYPRODUCT (WEARERINVOICECATEGORY_ID);

CREATE INDEX XIF2WEARERINVCATEGORYPRODUCT ON WEARERINVOICECATEGORYPRODUCT (PRODUCT_ID);

ALTER TABLE WEARERINVOICECATEGORYPRODUCT
ADD CONSTRAINT WEARERINVCATEGPR_WEARERINVCAT FOREIGN KEY (WEARERINVOICECATEGORY_ID) 
               REFERENCES WEARERINVOICECATEGORY (WEARERINVOICECATEGORY_ID) ON DELETE CASCADE;
               
ALTER TABLE WEARERINVOICECATEGORYPRODUCT
ADD CONSTRAINT WEARERINVCATEGPR_PROD FOREIGN KEY (PRODUCT_ID) 
               REFERENCES PRODUCT (PRODUCT_ID) ON DELETE CASCADE;


----WEARERINVENTORY table----

ALTER TABLE WEARERINVENTORY ADD (WEARERINVOICECATEGORY_ID NUMBER NULL);

CREATE INDEX XIF1WEARERINVENTORY ON WEARERINVENTORY (WEARERINVOICECATEGORY_ID);

ALTER TABLE WEARERINVENTORY
  ADD CONSTRAINT WEARERINVENTORY_WEARERINVCATEG FOREIGN KEY (WEARERINVOICECATEGORY_ID)
  REFERENCES WEARERINVOICECATEGORY (WEARERINVOICECATEGORY_ID) ON DELETE SET NULL;
