--ABS-118112 Packing Instructions and Show Batch Content

--IMAGE
ALTER TABLE IMAGE
DROP CONSTRAINT CHK_IMAGE_IMAGETYPE;

ALTER TABLE IMAGE
ADD CONSTRAINT CHK_IMAGE_IMAGETYPE CHECK (IMAGETYPE IN(1,2,3,4,5,6,7));

COMMENT ON COLUMN IMAGE.IMAGETYPE IS '1 = Product
                                      2 = Placement
                                      3 = Garment
                                      4 = Unique Residential Item
                                      5 = Company Stamp
                                      6 = RepairType
                                      7 = Cleanroom Pack Instructions';

--CLEANROOMPACKINSTRCAT

CREATE SEQUENCE SEQ_CLEANROOMPACKINSTRCAT
START WITH 1
INCREMENT BY 1;

CREATE TABLE CLEANROOMPACKINSTRCAT(
                                   CLEANROOMPACKINSTRCAT_ID NUMBER NOT NULL,
                                   CODE VARCHAR2(6) NOT NULL CONSTRAINT CHK_CLEANROOMPACKINSTRCAT_CODE CHECK (CODE=UPPER(CODE)),
                                   DESCRIPTION VARCHAR2(30) NOT NULL,
                                   ACTIVE VARCHAR2(1) DEFAULT 'Y' NOT NULL CONSTRAINT CHK_CLEANROOMPACKINSTRCAT_ACTIVE CHECK (ACTIVE IN('Y','N')),
                                   PRIORITY NUMBER NOT NULL CONSTRAINT CHK_CLEANROOMPACKINSTRCAT_PRIORITY CHECK (PRIORITY IN (1,2,3,4)),
                                   SYSTEMUSER_ID NUMBER NOT NULL,
                                   TIMESTAMP DATE NOT NULL
                                   );

COMMENT ON COLUMN CLEANROOMPACKINSTRCAT.CODE IS 'Uppercase';
COMMENT ON COLUMN CLEANROOMPACKINSTRCAT.ACTIVE IS 'Y/N';
COMMENT ON COLUMN CLEANROOMPACKINSTRCAT.PRIORITY IS '1-4';

ALTER TABLE CLEANROOMPACKINSTRCAT
ADD CONSTRAINT XPKCLEANROOMPACKINSTRCAT PRIMARY KEY(CLEANROOMPACKINSTRCAT_ID);

CREATE UNIQUE INDEX XAK1CLEANROOMPACKINSTRCAT ON CLEANROOMPACKINSTRCAT(CODE ASC);

--CLEANROOMPACKINSTRUCTION

CREATE SEQUENCE SEQ_CLEANROOMPACKINSTRUCTION
START WITH 1
INCREMENT BY 1;

CREATE TABLE CLEANROOMPACKINSTRUCTION(
                                CLEANROOMPACKINSTRUCTION_ID NUMBER NOT NULL,
                                CLEANROOMPACKINSTRCAT_ID NUMBER NOT NULL,
                                SEQUENCENUMBER NUMBER NOT NULL CONSTRAINT CHK_CLEANROOMPACKINSTRUCTION_SEQUENCENUMBER CHECK(SEQUENCENUMBER IN (1,2,3,4,5,6,7,8,9)),
                                CODE VARCHAR2(6) NOT NULL CONSTRAINT CHK_CLEANROOMPACKINSTRUCTION_CODE CHECK(CODE=UPPER(CODE)),
                                INSTRUCTION VARCHAR2(80) NOT NULL,
                                IMAGE_ID NUMBER,
                                SYSTEMUSER_ID NUMBER NOT NULL,
                                TIMESTAMP DATE NOT NULL
                                );

COMMENT ON COLUMN CLEANROOMPACKINSTRUCTION.SEQUENCENUMBER IS '1-9';
COMMENT ON COLUMN CLEANROOMPACKINSTRUCTION.CODE IS 'Uppercase';

ALTER TABLE CLEANROOMPACKINSTRUCTION
ADD CONSTRAINT XPKCLEANROOMPACKINSTRUCTION PRIMARY KEY(CLEANROOMPACKINSTRUCTION_ID);

CREATE UNIQUE INDEX XAK1CLEANROOMPACKINSTRUCTION ON CLEANROOMPACKINSTRUCTION
(CLEANROOMPACKINSTRCAT_ID ASC,SEQUENCENUMBER ASC);

CREATE UNIQUE INDEX XAK2CLEANROOMPACKINSTRUCTION ON CLEANROOMPACKINSTRUCTION
(CLEANROOMPACKINSTRCAT_ID ASC,CODE ASC);        

CREATE INDEX XIF1CLEANROOMPACKINSTRUCTION ON CLEANROOMPACKINSTRUCTION(CLEANROOMPACKINSTRCAT_ID ASC);

CREATE INDEX XIF2CLEANROOMPACKINSTRUCTION ON CLEANROOMPACKINSTRUCTION(IMAGE_ID ASC);

ALTER TABLE CLEANROOMPACKINSTRUCTION
ADD CONSTRAINT CLEANROOMPACKINSTRUCTION_CRPI FOREIGN KEY (CLEANROOMPACKINSTRCAT_ID) REFERENCES CLEANROOMPACKINSTRCAT(CLEANROOMPACKINSTRCAT_ID) ON DELETE CASCADE;

ALTER TABLE CLEANROOMPACKINSTRUCTION
ADD CONSTRAINT CLEANROOMPACKINSTRUCTION_IMAGE FOREIGN KEY (IMAGE_ID) REFERENCES IMAGE(IMAGE_ID) ON DELETE SET NULL;

--PLPCCLEANROOMPACKINSTRCAT

CREATE TABLE PLPCCLEANROOMPACKINSTRCAT(
                                       CLEANROOMPACKINSTRCAT_ID NUMBER NOT NULL,
                                       PRODUCTLISTPERCUSTOMER_ID NUMBER NOT NULL,
                                       SYSTEMUSER_ID NUMBER NOT NULL,
                                       TIMESTAMP DATE NOT NULL
                                       );

ALTER TABLE PLPCCLEANROOMPACKINSTRCAT
ADD CONSTRAINT XPKPLPCCLEANROOMPACKINSTRCAT PRIMARY KEY(CLEANROOMPACKINSTRCAT_ID,PRODUCTLISTPERCUSTOMER_ID);

CREATE INDEX XIF1PLPCCLEANROOMPACKINSTRCAT ON PLPCCLEANROOMPACKINSTRCAT(CLEANROOMPACKINSTRCAT_ID ASC); 

CREATE INDEX XIF2PLPCCLEANROOMPACKINSTRCAT ON PLPCCLEANROOMPACKINSTRCAT(PRODUCTLISTPERCUSTOMER_ID ASC);

ALTER TABLE PLPCCLEANROOMPACKINSTRCAT
ADD CONSTRAINT PLPCCLEANROOMPACKINSTRCAT_CRPI FOREIGN KEY (CLEANROOMPACKINSTRCAT_ID) REFERENCES CLEANROOMPACKINSTRCAT(CLEANROOMPACKINSTRCAT_ID) ON DELETE CASCADE;

ALTER TABLE PLPCCLEANROOMPACKINSTRCAT
ADD CONSTRAINT PLPCCLEANROOMPACKINSTRCAT_PLPC FOREIGN KEY (PRODUCTLISTPERCUSTOMER_ID) REFERENCES PRODUCTLISTPERCUSTOMER(PRODUCTLISTPERCUSTOMER_ID) ON DELETE CASCADE;                               
