--ABS-163823

--SALESWEARER TABLE

CREATE SEQUENCE SEQ_SALESWEARER
START WITH 1
INCREMENT BY 1;

CREATE TABLE SALESWEARER (
SALESWEARER_ID NUMBER NOT NULL,
CUSTOMER_LINK_ID NUMBER NOT NULL,
NAME VARCHAR2(40) NOT NULL,
EMBLEMNAME VARCHAR2(40) NULL,
ACTIVE VARCHAR2(1) DEFAULT 'Y' NOT NULL CONSTRAINT CHK_SALESWEARER_ACTIVE CHECK (ACTIVE IN ('Y' , 'N')),
WEARER_ID NUMBER NULL,
SYSTEMUSER_ID NUMBER NOT NULL,
TIMESTAMP DATE NOT NULL);

ALTER TABLE SALESWEARER ADD CONSTRAINT XPKSALESWEARER PRIMARY KEY (SALESWEARER_ID);

CREATE INDEX XIF1SALESWEARER ON SALESWEARER (CUSTOMER_LINK_ID ASC);
CREATE INDEX XIF2SALESWEARER ON SALESWEARER (WEARER_ID ASC);

ALTER TABLE SALESWEARER
ADD CONSTRAINT FK_SALESWEARER_CUSTOMER_LINK_ID FOREIGN KEY (CUSTOMER_LINK_ID) REFERENCES CUSTOMER_LINK(CUSTOMER_LINK_ID);

ALTER TABLE SALESWEARER
ADD CONSTRAINT FK_SALESWEARER_WEARER_ID FOREIGN KEY (WEARER_ID) REFERENCES WEARER(WEARER_ID) ON DELETE SET NULL;

--WORKORDER  TABLE
ALTER TABLE WORKORDER ADD (SALESWEARERS VARCHAR2(1) DEFAULT 'N' NOT NULL CONSTRAINT CHK_WORKORDER_SALESWEARERS CHECK (SALESWEARERS IN ('Y', 'N')));
COMMENT ON COLUMN WORKORDER.SALESWEARERS  IS 'ONLY USED FOR MODULE TRADESALESWEARER';

--GARMENTSALESHISTORY TABLE
ALTER TABLE GARMENTSALESHISTORY ADD (SALESWEARER_ID NUMBER NULL,
                                     INVOICELINE_ID NUMBER NULL);

CREATE INDEX XIF17GARMENTSALESHISTORY ON GARMENTSALESHISTORY (SALESWEARER_ID ASC);

CREATE INDEX XIF18GARMENTSALESHISTORY ON GARMENTSALESHISTORY (INVOICELINE_ID ASC);

ALTER TABLE GARMENTSALESHISTORY ADD CONSTRAINT FK_GARMENTSALESHISTORY_SALESWEARER_ID FOREIGN KEY (SALESWEARER_ID) REFERENCES SALESWEARER(SALESWEARER_ID) ON DELETE SET NULL;

ALTER TABLE GARMENTSALESHISTORY ADD CONSTRAINT FK_GARMENTSALESHISTORY_INVOICELINE_ID FOREIGN KEY (INVOICELINE_ID) REFERENCES INVOICELINE(INVOICELINE_ID) ON DELETE SET NULL;

--WORKORDERLINE TABLE
ALTER TABLE WORKORDERLINE ADD (SALESWEARER_ID NUMBER NULL);
COMMENT ON COLUMN WORKORDERLINE.SALESWEARER_ID IS 'ONLY USED FOR MODULE TRADESALESWEARER' ;

DROP INDEX XAK1WORKORDERLINE;
CREATE UNIQUE INDEX XAK1WORKORDERLINE ON WORKORDERLINE (WORKORDER_ID ASC, PRODUCT_LINK_ID ASC, SIZEDEFINITION_LINK_ID ASC, REQUESTEDGRADE_ID ASC, REASONCODE_ID ASC, CODEUNIQUELY ASC, RENTALQTYADJUSTMENT_ID ASC, NVL(REPLENISHMENTSEQUENCENUMBER,0) ASC, NVL(POLSEQUENCENUMBER,0) ASC, SALESWEARER_ID ASC);

CREATE INDEX XIF16WORKORDERLINE ON WORKORDERLINE (SALESWEARER_ID ASC);

ALTER TABLE WORKORDERLINE
ADD CONSTRAINT FK_WORKORDERLINE_SALESWEARER FOREIGN KEY (SALESWEARER_ID) REFERENCES SALESWEARER(SALESWEARER_ID);
