--Order 20013760.80


----REPAIRTYPE table----

ALTER TABLE REPAIRTYPE ADD (REPAIRPOINTERSELECTABLE VARCHAR2(1) DEFAULT 'Y' NOT NULL
                                                    CONSTRAINT YN_VALIDATIONFIELD110 CHECK (REPAIRPOINTERSELECTABLE IN ('Y', 'N')));
ALTER TABLE REPAIRTYPE ADD (REPAIRPOINTERSEQUENCE NUMBER(3) DEFAULT 0 NOT NULL
                                                    CONSTRAINT REPAIRPOINTERSEQUENCE_VALID1_1 CHECK (REPAIRPOINTERSEQUENCE BETWEEN 0 AND 999));


----REPAIRPOINTER table----

ALTER TABLE REPAIRPOINTER ADD (REPAIRTYPE_ID NUMBER NULL);
ALTER TABLE REPAIRPOINTER ADD (XPERCENTAGE NUMBER(3) DEFAULT 0 NOT NULL 
                                           CONSTRAINT XPERCENTAGE_VALID1_1 CHECK (XPERCENTAGE BETWEEN 0 AND 100));
ALTER TABLE REPAIRPOINTER ADD (YPERCENTAGE NUMBER(3) DEFAULT 0 NOT NULL
                                           CONSTRAINT YPERCENTAGE_VALID1_1 CHECK (YPERCENTAGE BETWEEN 0 AND 100));
 
COMMENT ON COLUMN REPAIRPOINTER.XPERCENTAGE
  IS 'X-Coordinate as percentage of the image size';
COMMENT ON COLUMN REPAIRPOINTER.YPERCENTAGE
  IS 'Y-Coordinate as percentage of the image size';

CREATE INDEX XIF2REPAIRPOINTER ON REPAIRPOINTER (REPAIRTYPE_ID);
 
ALTER TABLE REPAIRPOINTER
  ADD CONSTRAINT REPAIRPOINTER_REPAIRTYPE FOREIGN KEY (REPAIRTYPE_ID)
  REFERENCES REPAIRTYPE (REPAIRTYPE_ID) ON DELETE SET NULL;


----PLANTREPAIRTYPESETTINGS table----

CREATE TABLE PLANTREPAIRTYPESETTINGS
(
       REPAIRTYPE_ID NUMBER NOT NULL,
       PLANT_ID      NUMBER NOT NULL,
       EXPORTCODE    VARCHAR2(6) NULL,
       SYSTEMUSER_ID NUMBER NOT NULL,
       TIMESTAMP     DATE NOT NULL
);

ALTER TABLE PLANTREPAIRTYPESETTINGS
ADD CONSTRAINT  XPKPLANTREPAIRTYPESETTINGS PRIMARY KEY (REPAIRTYPE_ID, PLANT_ID);

CREATE INDEX XIF1PLANTREPAIRTYPESETTINGS ON PLANTREPAIRTYPESETTINGS (REPAIRTYPE_ID ASC);

CREATE INDEX XIF2PLANTREPAIRTYPESETTINGS ON PLANTREPAIRTYPESETTINGS (PLANT_ID ASC);

ALTER TABLE PLANTREPAIRTYPESETTINGS
  ADD CONSTRAINT PLANTREPAIRTYPESETTINGS_REP FOREIGN KEY (REPAIRTYPE_ID)
  REFERENCES REPAIRTYPE (REPAIRTYPE_ID) ON DELETE CASCADE;
  
ALTER TABLE PLANTREPAIRTYPESETTINGS
  ADD CONSTRAINT PLANTREPAIRTYPESETTINGS_PL FOREIGN KEY (PLANT_ID)
  REFERENCES PLANT (PLANT_ID);

