--ABS-11734

-------------- PACKINGNOTEEXCHANGECART --------------
CREATE TABLE PACKINGNOTEEXCHANGECART
( PACKINGNOTE_ID  NUMBER NOT NULL,
  EXCHANGECART_ID NUMBER NOT NULL,
  SYSTEMUSER_ID   NUMBER NOT NULL,
  TIMESTAMP       DATE NOT NULL);
  
ALTER TABLE PACKINGNOTEEXCHANGECART
  ADD CONSTRAINT XPKPACKINGNOTEEXCHANGECART PRIMARY KEY (PACKINGNOTE_ID);
  
CREATE INDEX XIF1PACKINGNOTEEXCHANGECART ON PACKINGNOTEEXCHANGECART (EXCHANGECART_ID ASC);

ALTER TABLE PACKINGNOTEEXCHANGECART
  ADD CONSTRAINT PACKINGNOTEEXCHANGECART_PN FOREIGN KEY (PACKINGNOTE_ID)
  REFERENCES PACKINGNOTE (PACKINGNOTE_ID) ON DELETE CASCADE;
  
ALTER TABLE PACKINGNOTEEXCHANGECART
  ADD CONSTRAINT PACKINGNOTEEXCHANGECART_ECART FOREIGN KEY (EXCHANGECART_ID)
  REFERENCES EXCHANGECART (EXCHANGECART_ID) ON DELETE CASCADE;

-------------- EXCHANGECART --------------
ALTER TABLE EXCHANGECART DROP COLUMN CONTAINER_ID CASCADE CONSTRAINTS;

ALTER TABLE EXCHANGECART
  ADD CONSTRAINT EXCHANGECART_CONTAINER FOREIGN KEY (EXCHANGECART_ID)
  REFERENCES CONTAINER (CONTAINER_ID) ON DELETE CASCADE;

-------------- EXCHANGECARTREPLACEMENT --------------
CREATE SEQUENCE SEQ_EXCHANGECARTREPLACEMENT
START WITH 1
INCREMENT BY 1;

CREATE TABLE EXCHANGECARTREPLACEMENT
( EXCHANGECARTREPLACEMENT_ID NUMBER NOT NULL,
  EXCHANGECART_ID            NUMBER NOT NULL,
  CONTAINER_ID               NUMBER NOT NULL,
  REPLACEMENTTYPE            NUMBER NOT NULL CONSTRAINT REPLACEMENTTYPE_VALID1_1
                                             CHECK (REPLACEMENTTYPE IN (1,2)),
  SYSTEMUSER_ID              NUMBER NOT NULL,
  TIMESTAMP                  DATE NOT NULL);

COMMENT ON COLUMN EXCHANGECARTREPLACEMENT.REPLACEMENTTYPE
  IS '1 = Substitute
2 = Swap';

ALTER TABLE EXCHANGECARTREPLACEMENT
  ADD CONSTRAINT XPKEXCHANGECARTREPLACEMENT PRIMARY KEY (EXCHANGECARTREPLACEMENT_ID);
 
CREATE UNIQUE INDEX XAK1EXCHANGECARTREPLACEMENT ON EXCHANGECARTREPLACEMENT (EXCHANGECART_ID ASC, CONTAINER_ID ASC, REPLACEMENTTYPE ASC);
CREATE INDEX XIF1EXCHANGECARTREPLACEMENT ON EXCHANGECARTREPLACEMENT (EXCHANGECART_ID ASC);
CREATE INDEX XIF2EXCHANGECARTREPLACEMENT ON EXCHANGECARTREPLACEMENT (CONTAINER_ID ASC);


ALTER TABLE EXCHANGECARTREPLACEMENT
  ADD CONSTRAINT EXCHANGECARTREPL_EXCART FOREIGN KEY (EXCHANGECART_ID)
  REFERENCES EXCHANGECART (EXCHANGECART_ID) ON DELETE CASCADE;
  
ALTER TABLE EXCHANGECARTREPLACEMENT
  ADD CONSTRAINT EXCHANGECARTREP_CONTAINER FOREIGN KEY (CONTAINER_ID)
  REFERENCES CONTAINER (CONTAINER_ID) ON DELETE CASCADE;
  
-------------- CONTAINER  --------------
ALTER TABLE CONTAINER DROP COLUMN SUBSTEXCHANGECART_ID CASCADE CONSTRAINTS;
ALTER TABLE CONTAINER DROP COLUMN SWAPEXCHANGECART_ID CASCADE CONSTRAINTS;


