-- ABS-69595

-------------  CONTRACTEMBTEMP -------------
CREATE SEQUENCE SEQ_CONTRACTEMBTEMP
START WITH 1
INCREMENT BY 1;

CREATE TABLE CONTRACTEMBTEMP
( CONTRACTEMBTEMP_ID NUMBER NOT NULL,
  CONTRACT_ID        NUMBER NOT NULL,
  CODE               VARCHAR2(6) NOT NULL,
  DESCRIPTION        VARCHAR2(30) NOT NULL,
  PRINTNAMELABEL     VARCHAR2(1) NOT NULL CONSTRAINT YN_VALIDATIONFIELD432
                     CHECK (PRINTNAMELABEL IN ('N', 'Y')),
  ACTIVE             VARCHAR2(1) DEFAULT 'Y' NOT NULL CONSTRAINT YN_VALIDATIONFIELD433
                     CHECK (ACTIVE IN ('N', 'Y')) ,
  RENTEMBLEM         VARCHAR2(1) NOT NULL CONSTRAINT YN_VALIDATIONFIELD434
                     CHECK (RENTEMBLEM IN ('N', 'Y')),
  EMBROIDERY         VARCHAR2(1) DEFAULT 'N' NOT NULL CONSTRAINT YN_VALIDATIONFIELD435
                     CHECK (EMBROIDERY IN ('N', 'Y')),
  COMPLIANCYRULE     NUMBER NULL CONSTRAINT COMPLIANCYRULE_VALID1_6
                     CHECK ((COMPLIANCYRULE IN (0,1,2) AND COMPLIANCYRULE IS NOT NULL) OR (COMPLIANCYRULE IS NULL)),                 
  SYSTEMUSER_ID      NUMBER NOT NULL,
  TIMESTAMP          DATE NOT NULL);

COMMENT ON COLUMN CONTRACTEMBTEMP.COMPLIANCYRULE
  IS '0 = Contract suggested, can be overruled 
1 = Contract enforced, can only be overruled by maintenance user
2 = Contract enforced';

ALTER TABLE CONTRACTEMBTEMP
  ADD CONSTRAINT XPKCONTRACTEMBTEMP PRIMARY KEY (CONTRACTEMBTEMP_ID);
 
CREATE UNIQUE INDEX XAK1CONTRACTEMBTEMP ON CONTRACTEMBTEMP (CONTRACT_ID ASC, CODE ASC);

CREATE INDEX XIF1CONTRACTEMBTEMP ON CONTRACTEMBTEMP (CONTRACT_ID ASC);


ALTER TABLE CONTRACTEMBTEMP
  ADD CONSTRAINT CONTRACTEMBTEMP_CONTRACT  FOREIGN KEY (CONTRACT_ID)
  REFERENCES CONTRACT (CONTRACT_ID) ON DELETE CASCADE;
  
  
------------- CONTRACTEMBTEMPLINE -------------
CREATE SEQUENCE SEQ_CONTRACTEMBTEMPLINE
START WITH 1
INCREMENT BY 1;

CREATE TABLE CONTRACTEMBTEMPLINE
(
  CONTRACTEMBTEMPLINE_ID       NUMBER NOT NULL,
  CONTRACTEMBTEMP_ID           NUMBER NOT NULL,
  PRODUCT_ID                   NUMBER NULL,
  SIZEDEFINITION_ID            NUMBER NOT NULL,
  DESCRIPTION                  VARCHAR2(80) NULL,
  TEXTCOLOR_ID                 NUMBER NULL,
  BORDERCOLOR_ID               NUMBER NULL,
  BKGCOLOR_ID                  NUMBER NULL,
  FABRIC_ID                    NUMBER NOT NULL,
  PLACEMENT_ID                 NUMBER NULL,
  EMBLEMTYPE                   NUMBER NOT NULL CONSTRAINT EMBLEMTYPE_VALID1_1
                               CHECK (EMBLEMTYPE IN (1, 2, 3)),
  ATTACHMENTBY                 NUMBER NOT NULL CONSTRAINT ATTACHMENTBY_VALID1_1
                               CHECK (ATTACHMENTBY IN (0, 1, 2, 3, 4)),
  QTYPERGARMENT                NUMBER NOT NULL,
  EMBLEMIMAGE_ID               NUMBER NULL,
  PLACEMENTIMAGE_ID            NUMBER NULL,
  EMBLEMFONT_ID                NUMBER NULL,
  FONTSIZE                     NUMBER DEFAULT 8 NOT NULL,
  EMBROIDERYFILEPATHORIGINAL   VARCHAR2(60) NULL,
  EMBROIDERYFILEPATHPRODUCTION VARCHAR2(60) NULL,
  STITCHCOUNT                  NUMBER DEFAULT 0 NOT NULL,
  SETUPFEE                     NUMBER DEFAULT 0 NOT NULL,
  COLORPALETTE                 VARCHAR2(30) NULL,
  EMBROIDERYLETTERING          VARCHAR2(1) DEFAULT 'N' NOT NULL CONSTRAINT YN_VALIDATIONFIELD436
                               CHECK (EMBROIDERYLETTERING IN ('Y', 'N')),
  EMBROIDERYLETTERINGLINE1     VARCHAR2(35) NULL,
  EMBROIDERYLETTERINGLINE2     VARCHAR2(35) NULL,
  EMBROIDERYLETTERINGLINE3     VARCHAR2(35) NULL,
  SYSTEMUSER_ID                NUMBER NOT NULL,
  TIMESTAMP                    DATE NOT NULL);

COMMENT ON COLUMN CONTRACTEMBTEMPLINE.EMBLEMTYPE
  IS '1= Company
2 = Name
3 = Other/ Not Defined';
COMMENT ON COLUMN CONTRACTEMBTEMPLINE.ATTACHMENTBY
  IS '0 = Unknown
1 = Local Stockroom (default)
2 = Central Stockroom
3 = Supplier
4 = Stocked';
COMMENT ON COLUMN CONTRACTEMBTEMPLINE.FONTSIZE
  IS 'Font size in mm. Default 8';
  
ALTER TABLE CONTRACTEMBTEMPLINE
  ADD CONSTRAINT XPKCONTRACTEMBTEMPLINE PRIMARY KEY (CONTRACTEMBTEMPLINE_ID);

CREATE UNIQUE INDEX XAK1CONTRACTEMBTEMPLINE ON CONTRACTEMBTEMPLINE (CONTRACTEMBTEMP_ID ASC, PRODUCT_ID ASC, SIZEDEFINITION_ID ASC, TEXTCOLOR_ID ASC, BORDERCOLOR_ID ASC, BKGCOLOR_ID ASC, FABRIC_ID ASC, PLACEMENT_ID ASC, EMBLEMFONT_ID ASC, EMBLEMTYPE ASC);

CREATE INDEX XIF1CONTRACTEMBTEMPLINE ON CONTRACTEMBTEMPLINE (CONTRACTEMBTEMP_ID ASC);
CREATE INDEX XIF2CONTRACTEMBTEMPLINE ON CONTRACTEMBTEMPLINE (SIZEDEFINITION_ID ASC);
CREATE INDEX XIF3CONTRACTEMBTEMPLINE ON CONTRACTEMBTEMPLINE (PRODUCT_ID ASC);
CREATE INDEX XIF4CONTRACTEMBTEMPLINE ON CONTRACTEMBTEMPLINE (TEXTCOLOR_ID ASC);
CREATE INDEX XIF5CONTRACTEMBTEMPLINE ON CONTRACTEMBTEMPLINE (BORDERCOLOR_ID ASC);
CREATE INDEX XIF6CONTRACTEMBTEMPLINE ON CONTRACTEMBTEMPLINE (BKGCOLOR_ID ASC);
CREATE INDEX XIF7CONTRACTEMBTEMPLINE ON CONTRACTEMBTEMPLINE (FABRIC_ID ASC);
CREATE INDEX XIF8CONTRACTEMBTEMPLINE ON CONTRACTEMBTEMPLINE (PLACEMENT_ID ASC);
CREATE INDEX XIF9CONTRACTEMBTEMPLINE ON CONTRACTEMBTEMPLINE (EMBLEMIMAGE_ID ASC);
CREATE INDEX XIF10CONTRACTEMBTEMPLINE ON CONTRACTEMBTEMPLINE (PLACEMENTIMAGE_ID ASC);
CREATE INDEX XIF11CONTRACTEMBTEMPLINE ON CONTRACTEMBTEMPLINE (EMBLEMFONT_ID ASC);

ALTER TABLE CONTRACTEMBTEMPLINE
  ADD CONSTRAINT CONTRACTEMBTEMPLINE_EMBLEMTEMP FOREIGN KEY (CONTRACTEMBTEMP_ID)
  REFERENCES EMBLEMTEMPLATE (EMBLEMTEMPLATE_ID) ON DELETE CASCADE;
ALTER TABLE CONTRACTEMBTEMPLINE
  ADD CONSTRAINT CONTRACTEMBTEMPLINE_PRODUCT_ID FOREIGN KEY (PRODUCT_ID)
  REFERENCES EMBLEMPRODUCT (PRODUCT_ID);
ALTER TABLE CONTRACTEMBTEMPLINE
  ADD CONSTRAINT CONTRACTEMBTEMPLINE_SIZEDEF FOREIGN KEY (SIZEDEFINITION_ID)
  REFERENCES SIZEDEFINITION (SIZEDEFINITION_ID);
ALTER TABLE CONTRACTEMBTEMPLINE
  ADD CONSTRAINT CONTRACTEMBTEMPLINE_TEXTCOLOR FOREIGN KEY (TEXTCOLOR_ID)
  REFERENCES COLOR (COLOR_ID);
ALTER TABLE CONTRACTEMBTEMPLINE
  ADD CONSTRAINT CONTRACTEMBTEMPLINE_BORDERCOL FOREIGN KEY (BORDERCOLOR_ID)
  REFERENCES COLOR (COLOR_ID);
ALTER TABLE CONTRACTEMBTEMPLINE
  ADD CONSTRAINT CONTRACTEMBTEMPLINE_BKGCOLOR FOREIGN KEY (BKGCOLOR_ID)
  REFERENCES COLOR (COLOR_ID);
ALTER TABLE CONTRACTEMBTEMPLINE
  ADD CONSTRAINT CONTRACTEMBTEMPLINE_EMBLEMIMAG FOREIGN KEY (EMBLEMIMAGE_ID)
  REFERENCES IMAGE (IMAGE_ID);
ALTER TABLE CONTRACTEMBTEMPLINE
  ADD CONSTRAINT CONTRACTEMBTEMPLINE_PLACEMIMG FOREIGN KEY (PLACEMENTIMAGE_ID)
  REFERENCES IMAGE (IMAGE_ID);
ALTER TABLE CONTRACTEMBTEMPLINE
  ADD CONSTRAINT CONTRACTEMBTEMPLINE_FABRIC FOREIGN KEY (FABRIC_ID)
  REFERENCES FABRIC (FABRIC_ID);
ALTER TABLE CONTRACTEMBTEMPLINE
  ADD CONSTRAINT CONTRACTEMBTEMPLINE_PLACEMENT FOREIGN KEY (PLACEMENT_ID)
  REFERENCES PLACEMENT (PLACEMENT_ID);
ALTER TABLE CONTRACTEMBTEMPLINE
  ADD CONSTRAINT CONTRACTEMBTEMPLINE_EMBLEMFONT FOREIGN KEY (EMBLEMFONT_ID)
  REFERENCES EMBLEMFONT (EMBLEMFONT_ID);


------------- CONTRACTEMBTEMPBRAND -------------  
CREATE TABLE CONTRACTEMBTEMPBRAND
( CONTRACTEMBTEMP_ID NUMBER NOT NULL,
  CONTRACTBRAND_ID   NUMBER NOT NULL,
  SYSTEMUSER_ID      NUMBER NOT NULL,
  TIMESTAMP          DATE NOT NULL );

ALTER TABLE CONTRACTEMBTEMPBRAND
  ADD CONSTRAINT XPKCONTRACTEMBTEMPBRAND PRIMARY KEY (CONTRACTEMBTEMP_ID, CONTRACTBRAND_ID);
  
CREATE INDEX XIF1CONTRACTEMBTEMPBRAND ON CONTRACTEMBTEMPBRAND (CONTRACTEMBTEMP_ID ASC);
CREATE INDEX XIF2CONTRACTEMBTEMPBRAND ON CONTRACTEMBTEMPBRAND (CONTRACTBRAND_ID ASC);

ALTER TABLE CONTRACTEMBTEMPBRAND
  ADD CONSTRAINT CONTRACTEMBTEMPBRAND_CONTRET FOREIGN KEY (CONTRACTEMBTEMP_ID)
  REFERENCES CONTRACTEMBTEMP (CONTRACTEMBTEMP_ID) ON DELETE CASCADE;
ALTER TABLE CONTRACTEMBTEMPBRAND
  ADD CONSTRAINT CONTRACTEMBTEMPBRAND_CONTBRAND FOREIGN KEY (CONTRACTBRAND_ID)
  REFERENCES CONTRACTBRAND (CONTRACTBRAND_ID) ON DELETE CASCADE;


------------- CONTRACTEMBTEMPCUST -------------  
CREATE TABLE CONTRACTEMBTEMPCUST
( CONTRACTEMBTEMP_ID NUMBER NOT NULL,
  EMBLEMTEMPLATE_ID  NUMBER NOT NULL,
  SYSTEMUSER_ID      NUMBER NOT NULL,
  TIMESTAMP          DATE NOT NULL );
  
ALTER TABLE CONTRACTEMBTEMPCUST
  ADD CONSTRAINT XPKCONTRACTEMBTEMPCUST PRIMARY KEY (CONTRACTEMBTEMP_ID, EMBLEMTEMPLATE_ID);

CREATE INDEX XIF1CONTRACTEMBTEMPCUST ON CONTRACTEMBTEMPCUST (CONTRACTEMBTEMP_ID ASC);
CREATE INDEX XIF2CONTRACTEMBTEMPCUST ON CONTRACTEMBTEMPCUST (EMBLEMTEMPLATE_ID ASC);


ALTER TABLE CONTRACTEMBTEMPCUST
  ADD CONSTRAINT CONTRACTEMBTEMPCUST_CONTREMBLT FOREIGN KEY (CONTRACTEMBTEMP_ID)
  REFERENCES CONTRACTEMBTEMP (CONTRACTEMBTEMP_ID) ON DELETE CASCADE;
ALTER TABLE CONTRACTEMBTEMPCUST
  ADD CONSTRAINT CONTRACTEMBTEMPCUST_EMBLTEMPL FOREIGN KEY (EMBLEMTEMPLATE_ID)
  REFERENCES EMBLEMTEMPLATE (EMBLEMTEMPLATE_ID) ON DELETE CASCADE;


------------- CONTRACTEMBTEMPPRICE  ------------- 
CREATE SEQUENCE SEQ_CONTRACTEMBTEMPPRICE
START WITH 1
INCREMENT BY 1;

CREATE TABLE CONTRACTEMBTEMPPRICE
( CONTRACTEMBTEMPPRICE_ID NUMBER NOT NULL,
  CONTRACTEMBTEMP_ID      NUMBER NOT NULL,
  STARTDATE               DATE NOT NULL,
  PRICE                   NUMBER(15,6) NOT NULL,
  SYSTEMUSER_ID           NUMBER NOT NULL,
  TIMESTAMP               DATE NOT NULL);

ALTER TABLE CONTRACTEMBTEMPPRICE
  ADD CONSTRAINT XPKCONTRACTEMBTEMPPRICE PRIMARY KEY (CONTRACTEMBTEMPPRICE_ID);
  
CREATE UNIQUE INDEX XAK1CONTRACTEMBTEMPPRICE ON CONTRACTEMBTEMPPRICE (CONTRACTEMBTEMP_ID ASC, STARTDATE ASC);
CREATE INDEX XIF1CONTRACTEMBTEMPPRICE ON CONTRACTEMBTEMPPRICE (CONTRACTEMBTEMP_ID ASC);
  
ALTER TABLE CONTRACTEMBTEMPPRICE
  ADD CONSTRAINT CONTRACTEMBTEMPPRICE_EMBLTEMPL FOREIGN KEY (CONTRACTEMBTEMP_ID)
  REFERENCES CONTRACTEMBTEMP (CONTRACTEMBTEMP_ID) ON DELETE CASCADE;


------------- CONTRACTEMBTEMPLINEPRICE ------------- 
CREATE SEQUENCE SEQ_CONTRACTEMBTEMPLINEPRICE
START WITH 1
INCREMENT BY 1;

CREATE TABLE CONTRACTEMBTEMPLINEPRICE 
( CONTRACTEMBTEMPLINEPRICE_ID NUMBER NOT NULL,
  CONTRACTEMBTEMPLINE_ID      NUMBER NOT NULL,
  PRICE                       NUMBER(15,6) NOT NULL,
  STARTDATE                   DATE NOT NULL,
  SYSTEMUSER_ID               NUMBER NOT NULL,
  TIMESTAMP                   DATE NOT NULL);

ALTER TABLE CONTRACTEMBTEMPLINEPRICE 
  ADD CONSTRAINT XPKCONTRACTEMBTEMPLINEPRICE PRIMARY KEY (CONTRACTEMBTEMPLINEPRICE_ID);
  
CREATE UNIQUE INDEX XAK1CONTRACTEMBTEMPLINEPRICE ON CONTRACTEMBTEMPLINEPRICE  (CONTRACTEMBTEMPLINE_ID ASC, STARTDATE ASC);
CREATE INDEX XIF1CONTRACTEMBTEMPLINEPRICE ON CONTRACTEMBTEMPLINEPRICE  (CONTRACTEMBTEMPLINE_ID ASC);

ALTER TABLE CONTRACTEMBTEMPLINEPRICE 
  ADD CONSTRAINT CONTREMBTEMPLINEPRICE_TEMPLA FOREIGN KEY (CONTRACTEMBTEMPLINE_ID)
  REFERENCES CONTRACTEMBTEMPLINE (CONTRACTEMBTEMPLINE_ID) ON DELETE CASCADE;


------------- CONTRACT ------------- 
ALTER TABLE CONTRACT ADD (ALLOWNONCONTRACTEMBTEMP VARCHAR2(1) DEFAULT 'Y' NOT NULL  CONSTRAINT YN_VALIDATIONFIELD437  -- IN ANALIZA SUNT PUSE CA NULLABLE DAR MIE MI SE PARE MAI LOGIC SA FIE NOT NULL
                         CHECK (ALLOWNONCONTRACTEMBTEMP IN ('Y', 'N')),
                         ALLOWNONCONTRACTWEARERFUNC VARCHAR2(1) DEFAULT 'Y' NOT NULL CONSTRAINT YN_VALIDATIONFIELD438
                         CHECK (ALLOWNONCONTRACTWEARERFUNC IN ('Y', 'N')), 
                         BRANDED VARCHAR2(1) DEFAULT 'N' NOT NULL CONSTRAINT YN_VALIDATIONFIELD439
                         CHECK (BRANDED IN ('Y', 'N')));


------------- CONTRACTCOMPONENTGRMSETTINGS ------------- 
ALTER TABLE CONTRACTCOMPONENTGRMSETTINGS ADD (NAMELABELLAYOUT_ID NUMBER NULL);

CREATE INDEX XIF4CONTRACTCOMPONENTGRMSETT ON CONTRACTCOMPONENTGRMSETTINGS (NAMELABELLAYOUT_ID ASC);

ALTER TABLE CONTRACTCOMPONENTGRMSETTINGS
  ADD CONSTRAINT CONTRACTCOMPONENTGRMSETT_LABEL FOREIGN KEY (NAMELABELLAYOUT_ID)
  REFERENCES LABELLAYOUT (LABELLAYOUT_ID);

  
------------- CONTRACTWEARERFUNCTION -------------
CREATE SEQUENCE SEQ_CONTRACTWEARERFUNCTION
START WITH 1
INCREMENT BY 1;

CREATE TABLE CONTRACTWEARERFUNCTION
( CONTRACTWEARERFUNCTION_ID NUMBER NOT NULL,
  CONTRACT_ID               NUMBER NOT NULL,
  CODE                      VARCHAR2(6) NOT NULL,
  DESCRIPTION               VARCHAR2(30) NOT NULL,
  DEFAULTDISPENSECREDIT     NUMBER NOT NULL,
  SPECIALMINIMUMGRADE_ID    NUMBER NULL,
  SPECIALMAXIMUMGRADE_ID    NUMBER NULL,
  CODELABELLAYOUT_ID        NUMBER NULL,
  NAMELABELLAYOUT_ID        NUMBER NULL,
  EXPORTGDCODE              VARCHAR2(6) NULL,
  EANNUMBER                 NUMBER NULL,
  DUMMYFORPOOLWEARER_ID     NUMBER NULL,
  INITIALWEARERBUDGET       NUMBER DEFAULT 0 NOT NULL CONSTRAINT INITIALWEARERBUDGET_VALID1_1
                            CHECK (INITIALWEARERBUDGET BETWEEN 0 AND 99999),
  ANNUALWEARERBUDGET        NUMBER DEFAULT 0 NOT NULL CONSTRAINT ANNUALWEARERBUDGET_VALID1_1
                            CHECK (ANNUALWEARERBUDGET BETWEEN 0 AND 99999),
  ADDANNUALBUDGET           NUMBER DEFAULT 0 NOT NULL CONSTRAINT ADDANNUALBUDGET_VALID1_1
                            CHECK (ADDANNUALBUDGET IN (0,1)),
  KEEPWEARERBUDGET          VARCHAR2(1) DEFAULT 'Y' NOT NULL CONSTRAINT YN_VALIDATIONFIELD440
                            CHECK (KEEPWEARERBUDGET IN ('Y', 'N')),
  ALLOWORDERINGVIAWEB       NUMBER DEFAULT 0 NOT NULL CONSTRAINT ALLOWORDERINGVIAWEB_VALID1_2
                            CHECK (ALLOWORDERINGVIAWEB BETWEEN 0 AND 2),
  ALLOWORDERINGVIAWEBMODE   NUMBER DEFAULT 0 NOT NULL CONSTRAINT ALLOWORDERINGVIAWEBMODE_VALID2
                            CHECK (ALLOWORDERINGVIAWEBMODE BETWEEN 0 AND 1),
  COMPLIANCYRULE            NUMBER NULL CONSTRAINT COMPLIANCYRULE_VALID1_7   ------------ NERULAT
                            CHECK ((COMPLIANCYRULE IN (0,1,2) AND COMPLIANCYRULE IS NOT NULL) OR (COMPLIANCYRULE IS NULL)),                        
  SYSTEMUSER_ID             NUMBER NOT NULL,
  TIMESTAMP                 DATE NOT NULL);

COMMENT ON COLUMN CONTRACTWEARERFUNCTION.INITIALWEARERBUDGET
  IS '0 - 99999';
COMMENT ON COLUMN CONTRACTWEARERFUNCTION.ANNUALWEARERBUDGET
  IS '0 - 99999';
COMMENT ON COLUMN CONTRACTWEARERFUNCTION.ADDANNUALBUDGET
  IS '0 = Annuversary date
1 = Start of Calendar year';
COMMENT ON COLUMN CONTRACTWEARERFUNCTION.ALLOWORDERINGVIAWEB
  IS 'Indicates if a wearer having his function may order via web portal. Possible values: 0 = Not allowed 1 = Allowed 2 = Allowed but approval needed on web';
COMMENT ON COLUMN CONTRACTWEARERFUNCTION.ALLOWORDERINGVIAWEBMODE
  IS 'Indicates if value as defined for alloworderingviaweb at function level is fixed or may be overruled at wearer level for a wearer having this function. Possible values: 0 = Default 1 = Fixed';
COMMENT ON COLUMN CONTRACTWEARERFUNCTION.COMPLIANCYRULE
  IS '0 = Contract suggested, can be overruled 
1 = Contract enforced, can only be overruled by maintenance user
2 = Contract enforced';

ALTER TABLE CONTRACTWEARERFUNCTION
  ADD CONSTRAINT XPKCONTRACTWEARERFUNCTION PRIMARY KEY (CONTRACTWEARERFUNCTION_ID);
  
CREATE UNIQUE INDEX XAK1CONTRACTWEARERFUNCTION ON CONTRACTWEARERFUNCTION (CONTRACT_ID ASC, CODE ASC);

CREATE INDEX XIF1CONTRACTWEARERFUNCTION ON CONTRACTWEARERFUNCTION (CONTRACT_ID ASC);
CREATE INDEX XIF2CONTRACTWEARERFUNCTION ON CONTRACTWEARERFUNCTION (SPECIALMINIMUMGRADE_ID ASC);
CREATE INDEX XIF3CONTRACTWEARERFUNCTION ON CONTRACTWEARERFUNCTION (SPECIALMAXIMUMGRADE_ID ASC);
CREATE INDEX XIF4CONTRACTWEARERFUNCTION ON CONTRACTWEARERFUNCTION (CODELABELLAYOUT_ID ASC);
CREATE INDEX XIF5CONTRACTWEARERFUNCTION ON CONTRACTWEARERFUNCTION (NAMELABELLAYOUT_ID ASC);
CREATE INDEX XIF6CONTRACTWEARERFUNCTION ON CONTRACTWEARERFUNCTION (DUMMYFORPOOLWEARER_ID ASC);

ALTER TABLE CONTRACTWEARERFUNCTION
  ADD CONSTRAINT CONTRACTWEARERFUNC_CONTRACT FOREIGN KEY (CONTRACT_ID)
  REFERENCES CONTRACT (CONTRACT_ID) ON DELETE CASCADE; 
ALTER TABLE CONTRACTWEARERFUNCTION
  ADD CONSTRAINT CONTRACTWEARERFUNC_DUMMYPOOL FOREIGN KEY (DUMMYFORPOOLWEARER_ID)
  REFERENCES WEARER (WEARER_ID);
ALTER TABLE CONTRACTWEARERFUNCTION
  ADD CONSTRAINT CONTRACTWEARERFUNC_CODELABEL FOREIGN KEY (CODELABELLAYOUT_ID)
  REFERENCES LABELLAYOUT (LABELLAYOUT_ID);
ALTER TABLE CONTRACTWEARERFUNCTION
  ADD CONSTRAINT CONTRACTWEARERFUNC_NAMELABEL FOREIGN KEY (NAMELABELLAYOUT_ID)
  REFERENCES LABELLAYOUT (LABELLAYOUT_ID);
ALTER TABLE CONTRACTWEARERFUNCTION
  ADD CONSTRAINT CONTRACTWEARERFUNC_MAXGRADE FOREIGN KEY (SPECIALMAXIMUMGRADE_ID)
  REFERENCES QUALITYGRADE (QUALITYGRADE_ID);
ALTER TABLE CONTRACTWEARERFUNCTION
  ADD CONSTRAINT CONTRACTWEARERFUNC_MINGRADE FOREIGN KEY (SPECIALMINIMUMGRADE_ID)
  REFERENCES QUALITYGRADE (QUALITYGRADE_ID);
  
  
------------- CONTRACTWEARERFUNCBRAND -------------  
CREATE TABLE CONTRACTWEARERFUNCBRAND
( CONTRACTWEARERFUNCTION_ID NUMBER NOT NULL,
  CONTRACTBRAND_ID          NUMBER NOT NULL,
  SYSTEMUSER_ID             NUMBER NOT NULL,
  TIMESTAMP                 DATE NOT NULL);

ALTER TABLE CONTRACTWEARERFUNCBRAND
  ADD CONSTRAINT XPKCONTRACTWEARERFUNCBRAND PRIMARY KEY (CONTRACTWEARERFUNCTION_ID, CONTRACTBRAND_ID);

CREATE INDEX XIF1CONTRACTWEARERFUNCBRAND ON CONTRACTWEARERFUNCBRAND (CONTRACTWEARERFUNCTION_ID ASC);
CREATE INDEX XIF2CONTRACTWEARERFUNCBRAND ON CONTRACTWEARERFUNCBRAND (CONTRACTBRAND_ID ASC);

ALTER TABLE CONTRACTWEARERFUNCBRAND
  ADD CONSTRAINT CONTRACTWEARERFUNCBRAND_WFUNC FOREIGN KEY (CONTRACTWEARERFUNCTION_ID)
  REFERENCES CONTRACTWEARERFUNCTION (CONTRACTWEARERFUNCTION_ID) ON DELETE CASCADE;
ALTER TABLE CONTRACTWEARERFUNCBRAND
  ADD CONSTRAINT CONTRACTWEARERFUNCBRAND_BRAND FOREIGN KEY (CONTRACTBRAND_ID)
  REFERENCES CONTRACTBRAND (CONTRACTBRAND_ID) ON DELETE CASCADE;

  
------------- CONTRACTWEARERFUNCCUST -------------  
CREATE TABLE CONTRACTWEARERFUNCCUST
( CONTRACTWEARERFUNCTION_ID NUMBER NOT NULL,
  WEARERFUNCTION_ID         NUMBER NOT NULL,
  SYSTEMUSER_ID             NUMBER NOT NULL,
  TIMESTAMP                 NUMBER NOT NULL);

ALTER TABLE CONTRACTWEARERFUNCCUST
  ADD CONSTRAINT XPKCONTRACTWEARERFUNCCUST PRIMARY KEY (CONTRACTWEARERFUNCTION_ID, WEARERFUNCTION_ID);

CREATE INDEX XIF1CONTRACTWEARERFUNCCUST ON CONTRACTWEARERFUNCCUST (CONTRACTWEARERFUNCTION_ID ASC);
CREATE INDEX XIF2CONTRACTWEARERFUNCCUST ON CONTRACTWEARERFUNCCUST (WEARERFUNCTION_ID ASC);

ALTER TABLE CONTRACTWEARERFUNCCUST
  ADD CONSTRAINT CONTRACTWEARERFUNCCUST_CONTRWF FOREIGN KEY (CONTRACTWEARERFUNCTION_ID)
  REFERENCES CONTRACTWEARERFUNCTION (CONTRACTWEARERFUNCTION_ID) ON DELETE CASCADE;
ALTER TABLE CONTRACTWEARERFUNCCUST
  ADD CONSTRAINT CONTRACTWEARERFUNCCUST_WFUNC FOREIGN KEY (WEARERFUNCTION_ID)
  REFERENCES WEARERFUNCTION (WEARERFUNCTION_ID) ON DELETE CASCADE;


------------- CONTRACTSTDINVENTORY -------------
CREATE SEQUENCE SEQ_CONTRACTSTDINVENTORY
START WITH 1
INCREMENT BY 1;

CREATE TABLE CONTRACTSTDINVENTORY
( CONTRACTSTDINVENTORY_ID   NUMBER NOT NULL,
  CONTRACTWEARERFUNCTION_ID NUMBER NOT NULL,
  PRODUCT_ID                NUMBER NOT NULL,
  CONTRACTEMBTEMP_ID        NUMBER NULL,
  PUTINUSE                  NUMBER NOT NULL,
  CHANGESPERWEEK            NUMBER NOT NULL,
  DISPENSECREDIT            NUMBER NOT NULL,
  SEASON_ID                 NUMBER NULL,
  SEASONCHANGEMODE          NUMBER DEFAULT 0 NOT NULL  CONSTRAINT SEASONCHANGEMODE_VALID1_2
                            CHECK (SEASONCHANGEMODE IN (0,1)),
  FINISHINGMETHOD_ID        NUMBER NULL,
  FMCHANGEMODE              NUMBER DEFAULT 0 NOT NULL CONSTRAINT FMCHANGEMODE_VALID1_2
                            CHECK (FMCHANGEMODE IN (0,1)),
  SYSTEMUSER_ID             NUMBER NOT NULL,
  TIMESTAMP                 DATE NOT NULL);

COMMENT ON COLUMN CONTRACTSTDINVENTORY.SEASONCHANGEMODE
  IS '0 = Default 1 = Fixed';
COMMENT ON COLUMN CONTRACTSTDINVENTORY.FMCHANGEMODE
  IS '0 = Default 1 = Fixed';

ALTER TABLE CONTRACTSTDINVENTORY
  ADD CONSTRAINT XPKCONTRACTSTDINVENTORY PRIMARY KEY (CONTRACTSTDINVENTORY_ID);

CREATE UNIQUE INDEX XAK1CONTRACTSTDINVENTORY ON CONTRACTSTDINVENTORY (CONTRACTWEARERFUNCTION_ID ASC, PRODUCT_ID ASC);
CREATE INDEX XIF1CONTRACTSTDINVENTORY ON CONTRACTSTDINVENTORY (SEASON_ID ASC);
CREATE INDEX XIF2CONTRACTSTDINVENTORY ON CONTRACTSTDINVENTORY (FINISHINGMETHOD_ID ASC);
CREATE INDEX XIF3CONTRACTSTDINVENTORY ON CONTRACTSTDINVENTORY (CONTRACTWEARERFUNCTION_ID ASC);
CREATE INDEX XIF4CONTRACTSTDINVENTORY ON CONTRACTSTDINVENTORY (CONTRACTEMBTEMP_ID ASC);
CREATE INDEX XIF5CONTRACTSTDINVENTORY ON CONTRACTSTDINVENTORY (PRODUCT_ID ASC);

ALTER TABLE CONTRACTSTDINVENTORY
  ADD CONSTRAINT CONTRACTSTDINVENTORY_EMBLTEMPL FOREIGN KEY (CONTRACTEMBTEMP_ID)
  REFERENCES CONTRACTEMBTEMP (CONTRACTEMBTEMP_ID);
ALTER TABLE CONTRACTSTDINVENTORY
  ADD CONSTRAINT CONTRACTSTDINVENTORY_FM FOREIGN KEY (FINISHINGMETHOD_ID)
  REFERENCES FINISHINGMETHOD (FINISHINGMETHOD_ID) ON DELETE SET NULL;
ALTER TABLE CONTRACTSTDINVENTORY
  ADD CONSTRAINT CONTRACTSTDINVENTORY_PRODUCT FOREIGN KEY (PRODUCT_ID)
  REFERENCES PRODUCT (PRODUCT_ID);
ALTER TABLE CONTRACTSTDINVENTORY
  ADD CONSTRAINT CONTRACTSTDINVENTORY_SEASON FOREIGN KEY (SEASON_ID)
  REFERENCES SEASON (SEASON_ID) ON DELETE SET NULL;
ALTER TABLE CONTRACTSTDINVENTORY
  ADD CONSTRAINT CONTRACTSTDINVENTORY_WEARERFUN FOREIGN KEY (CONTRACTWEARERFUNCTION_ID)
  REFERENCES CONTRACTWEARERFUNCTION (CONTRACTWEARERFUNCTION_ID) ON DELETE CASCADE;

  
------------- CONTRACTDEFWEARERPRICE  -------------
CREATE SEQUENCE SEQ_CONTRACTDEFWEARERPRICE
START WITH 1
INCREMENT BY 1;
 
CREATE TABLE CONTRACTDEFWEARERPRICE
( CONTRACTDEFWEARERPRICE_ID NUMBER NOT NULL,
  CONTRACTWEARERFUNCTION_ID   NUMBER NOT NULL,
  STARTDATE                   DATE NOT NULL,
  PRICE                       NUMBER(15,6) NOT NULL,
  SYSTEMUSER_ID               NUMBER NOT NULL,
  TIMESTAMP                   DATE NOT NULL);
  
ALTER TABLE CONTRACTDEFWEARERPRICE
  ADD CONSTRAINT XPKCONTRACTDEFWEARERPRICE PRIMARY KEY (CONTRACTDEFWEARERPRICE_ID);

CREATE UNIQUE INDEX XAK1CONTRACTDEFWEARERPRICE ON CONTRACTDEFWEARERPRICE (CONTRACTWEARERFUNCTION_ID ASC, STARTDATE ASC);
CREATE INDEX XIF1CONTRACTDEFWEARERPRICE ON CONTRACTDEFWEARERPRICE (CONTRACTWEARERFUNCTION_ID ASC);

ALTER TABLE CONTRACTDEFWEARERPRICE
  ADD CONSTRAINT CONTRACTWEARERPRICE_WEARERFUNC FOREIGN KEY (CONTRACTWEARERFUNCTION_ID)
  REFERENCES CONTRACTWEARERFUNCTION (CONTRACTWEARERFUNCTION_ID) ON DELETE CASCADE;

  
------------- CONTRACTEMBROIDERYCOLOR -------------
CREATE SEQUENCE SEQ_CONTRACTEMBROIDERYCOLOR
START WITH 1
INCREMENT BY 1;

CREATE TABLE CONTRACTEMBROIDERYCOLOR
( CONTRACTEMBROIDERYCOLOR_ID NUMBER NOT NULL,
  CONTRACTEMBTEMPLINE_ID     NUMBER NOT NULL,
  SEQUENCENUMBER             NUMBER NOT NULL,
  COLOR_ID                   NUMBER NOT NULL,
  SYSTEMUSER_ID              NUMBER NOT NULL,
  TIMESTAMP                  DATE NOT NULL);

COMMENT ON COLUMN CONTRACTEMBROIDERYCOLOR.SEQUENCENUMBER
  IS 'start with 1';
  
ALTER TABLE CONTRACTEMBROIDERYCOLOR
  ADD CONSTRAINT XPKCONTRACTEMBROIDERYCOLOR PRIMARY KEY (CONTRACTEMBROIDERYCOLOR_ID);

CREATE UNIQUE INDEX XAK1CONTRACTEMBROIDERYCOLOR ON CONTRACTEMBROIDERYCOLOR (CONTRACTEMBROIDERYCOLOR_ID ASC, SEQUENCENUMBER ASC);
CREATE INDEX XIF1CONTRACTEMBROIDERYCOLOR ON CONTRACTEMBROIDERYCOLOR (CONTRACTEMBTEMPLINE_ID ASC);
CREATE INDEX XIF2CONTRACTEMBROIDERYCOLOR ON CONTRACTEMBROIDERYCOLOR (COLOR_ID ASC);

ALTER TABLE CONTRACTEMBROIDERYCOLOR
  ADD CONSTRAINT CONTRACTEMBROIDERYCOLOR_COLOR FOREIGN KEY (COLOR_ID)
  REFERENCES COLOR (COLOR_ID);
ALTER TABLE CONTRACTEMBROIDERYCOLOR
  ADD CONSTRAINT CONTRACTEMBROIDERYCOLOR_CETL FOREIGN KEY (CONTRACTEMBTEMPLINE_ID)
  REFERENCES CONTRACTEMBTEMPLINE (CONTRACTEMBTEMPLINE_ID) ON DELETE CASCADE;
