-- ABS-11734 

----------- EXCHANGECART -----------

CREATE SEQUENCE SEQ_EXCHANGECART
START WITH 1 
INCREMENT BY 1;

CREATE TABLE EXCHANGECART
(
  EXCHANGECART_ID      NUMBER NOT NULL,
  CONTAINER_ID         NUMBER NOT NULL,
  DISTRIBUTIONPOINT_ID NUMBER NOT NULL,
  SYSTEMUSER_ID        NUMBER NOT NULL,
  TIMESTAMP            DATE NOT NULL
);

ALTER TABLE EXCHANGECART
  ADD CONSTRAINT XPKEXCHANGECART PRIMARY KEY (EXCHANGECART_ID);

CREATE UNIQUE INDEX XAK1EXCHANGECART ON EXCHANGECART (CONTAINER_ID ASC); 
CREATE INDEX XIF1EXCHANGECART ON EXCHANGECART (DISTRIBUTIONPOINT_ID ASC);

ALTER TABLE EXCHANGECART
  ADD CONSTRAINT EXCHANGECART_CONTAINER FOREIGN KEY (CONTAINER_ID)
  REFERENCES CONTAINER (CONTAINER_ID) ON DELETE CASCADE;
ALTER TABLE EXCHANGECART
  ADD CONSTRAINT EXCHANGECART_DISTRIBUTIONPOINT FOREIGN KEY (DISTRIBUTIONPOINT_ID)
  REFERENCES DISTRIBUTIONPOINT (DISTRIBUTIONPOINT_ID) ON DELETE CASCADE;
  
----------- EXCHANGECARTSCHEDULE -----------

CREATE TABLE EXCHANGECARTSCHEDULE
(
  EXCHANGECART_ID      NUMBER NOT NULL,
  DAY_ID               NUMBER NOT NULL,
  DELIVERYSCHEME_ID    NUMBER NOT NULL,
  ACTION               NUMBER NOT NULL CONSTRAINT ACTION_VALID2_1 CHECK (ACTION IN (1,2)),
  SYSTEMUSER_ID        NUMBER NOT NULL,
  TIMESTAMP            DATE NOT NULL
 );

COMMENT ON COLUMN EXCHANGECARTSCHEDULE.ACTION IS '1 = Deliver
2 = Pick up';

ALTER TABLE EXCHANGECARTSCHEDULE
  ADD CONSTRAINT XPKEXCHANGECARTSCHEDULE PRIMARY KEY (EXCHANGECART_ID, ACTION);
 
CREATE INDEX XIF1EXCHANGECARTSCHEDULE ON EXCHANGECARTSCHEDULE (EXCHANGECART_ID ASC);
CREATE INDEX XIF2EXCHANGECARTSCHEDULE ON EXCHANGECARTSCHEDULE (DAY_ID ASC);
CREATE INDEX XIF3EXCHANGECARTSCHEDULE ON EXCHANGECARTSCHEDULE (DELIVERYSCHEME_ID ASC);

ALTER TABLE EXCHANGECARTSCHEDULE
  ADD CONSTRAINT EXCHANGECARTSCHEDULE_EXCART FOREIGN KEY (EXCHANGECART_ID)
  REFERENCES EXCHANGECART (EXCHANGECART_ID) ON DELETE CASCADE;
ALTER TABLE EXCHANGECARTSCHEDULE
  ADD CONSTRAINT EXCHANGECARTSCHEDULE_DAY FOREIGN KEY (DAY_ID)
  REFERENCES DAY (DAY_ID);
ALTER TABLE EXCHANGECARTSCHEDULE
  ADD CONSTRAINT EXCHANGECARTSCHEDULE_DELSCHEME FOREIGN KEY (DELIVERYSCHEME_ID)
  REFERENCES DELIVERYSCHEME (DELIVERYSCHEME_ID); 
  
----------- EXCHANGECARTDELIVERYSTOP -----------   
  
CREATE TABLE EXCHANGECARTDELIVERYSTOP
(
  EXCHANGECART_ID NUMBER NOT NULL,
  STARTDATE       DATE NOT NULL,
  ENDDATE         DATE NOT NULL,
  DESCRIPTION     VARCHAR2(80) NULL,
  SYSTEMUSER_ID   NUMBER NOT NULL,
  TIMESTAMP       DATE NOT NULL
);

COMMENT ON COLUMN EXCHANGECARTSCHEDULE.ACTION IS '1 = Deliver
2 = Pick up';

ALTER TABLE EXCHANGECARTDELIVERYSTOP
  ADD CONSTRAINT XPKEXCHANGECARTDELIVERYSTOP PRIMARY KEY (EXCHANGECART_ID, STARTDATE);

CREATE INDEX XIF1EXCHANGECARTDELIVERYSTOP ON EXCHANGECARTDELIVERYSTOP(EXCHANGECART_ID ASC);

ALTER TABLE EXCHANGECARTDELIVERYSTOP
  ADD CONSTRAINT EXCHANGECARTDELSTOP_EXGCART FOREIGN KEY (EXCHANGECART_ID)
  REFERENCES EXCHANGECART (EXCHANGECART_ID) ON DELETE CASCADE;

ALTER TABLE EXCHANGECARTDELIVERYSTOP
  ADD CONSTRAINT EXCHANGECARTDELIVERYSTOP_CHECK CHECK (ENDDATE >= STARTDATE);
  
----------- EXCHANGECARTCYCLE -----------  

CREATE SEQUENCE SEQ_EXCHANGECARTCYCLE
START WITH 1
INCREMENT BY 1;

CREATE TABLE EXCHANGECARTCYCLE
(
  EXCHANGECARTCYCLE_ID   NUMBER NOT NULL,
  EXCHANGECART_ID        NUMBER NOT NULL,
  CLEANRETURNDATE        DATE NOT NULL,
  DELIVERYDATE           DATE NULL,
  CODEUNIQUELY           NUMBER NOT NULL CONSTRAINT CODEUNIQUELY_VALID1_3 CHECK (CODEUNIQUELY IN (0,1)),
  PRODUCT_LINK_ID        NUMBER NOT NULL,
  SIZEDEFINITION_LINK_ID NUMBER NOT NULL,
  FINISHINGMETHOD_ID     NUMBER NOT NULL,
  PAR                    NUMBER DEFAULT 0 NOT NULL,
  CHIPPINGRATE           NUMBER NOT NULL CONSTRAINT CHIPPINGRATE_VALID1_1 CHECK (CHIPPINGRATE BETWEEN 0 AND 100),
  REALSCANNEDIN          NUMBER DEFAULT 0 NOT NULL,
  CALCULATEDSCANNEDIN    NUMBER DEFAULT 0 NOT NULL,
  COUNTEDIN              NUMBER DEFAULT 0 NOT NULL,
  REALSCANNEDOUT         NUMBER DEFAULT 0 NOT NULL,
  CALCULATEDSCANNEDOUT   NUMBER DEFAULT 0 NOT NULL,
  SYSTEMUSER_ID          NUMBER NOT NULL,
  TIMESTAMP              DATE NOT NULL
);

COMMENT ON COLUMN EXCHANGECARTCYCLE.CODEUNIQUELY
  IS '0 = NOT UNIQUELY CODED
  1 = UNIQUELY CODED';
  
ALTER TABLE EXCHANGECARTCYCLE
  ADD CONSTRAINT XPKEXCHANGECARTCYCLE PRIMARY KEY (EXCHANGECARTCYCLE_ID);

CREATE INDEX XAK1EXCHANGECARTCYCLE ON EXCHANGECARTCYCLE (EXCHANGECART_ID ASC, CLEANRETURNDATE ASC,PRODUCT_LINK_ID ASC, SIZEDEFINITION_LINK_ID ASC, FINISHINGMETHOD_ID ASC);  
CREATE INDEX XIE1EXCHANGECARTCYCLE ON EXCHANGECARTCYCLE (EXCHANGECART_ID ASC, CODEUNIQUELY ASC, PRODUCT_LINK_ID ASC, SIZEDEFINITION_LINK_ID ASC, FINISHINGMETHOD_ID ASC);
CREATE INDEX XIF1XCHANGECARTCYCLE ON EXCHANGECARTCYCLE (EXCHANGECART_ID ASC);
CREATE INDEX XIF2XCHANGECARTCYCLE ON EXCHANGECARTCYCLE (PRODUCT_LINK_ID ASC);
CREATE INDEX XIF3XCHANGECARTCYCLE ON EXCHANGECARTCYCLE (SIZEDEFINITION_LINK_ID ASC);
CREATE INDEX XIF4XCHANGECARTCYCLE ON EXCHANGECARTCYCLE (FINISHINGMETHOD_ID ASC);

ALTER TABLE EXCHANGECARTCYCLE
  ADD CONSTRAINT EXCHANGECART_ID FOREIGN KEY (EXCHANGECART_ID)
  REFERENCES EXCHANGECART (EXCHANGECART_ID) ON DELETE CASCADE;
ALTER TABLE EXCHANGECARTCYCLE
  ADD CONSTRAINT EXCHANGECARTCYCLE_PRODLINK FOREIGN KEY (PRODUCT_LINK_ID)
  REFERENCES PRODUCT_LINK (PRODUCT_LINK_ID);
ALTER TABLE EXCHANGECARTCYCLE
  ADD CONSTRAINT EXCHANGECARTCYCLE_SIZEDEF FOREIGN KEY (SIZEDEFINITION_LINK_ID)
  REFERENCES SIZEDEFINITION_LINK (SIZEDEFINITION_LINK_ID);
ALTER TABLE EXCHANGECARTCYCLE
  ADD CONSTRAINT EXCHANGECARTCYCLE_FM FOREIGN KEY (FINISHINGMETHOD_ID)
  REFERENCES FINISHINGMETHOD (FINISHINGMETHOD_ID);

----------- FINISHINGMETHOD -----------  

ALTER TABLE FINISHINGMETHOD ADD (CHIPPED VARCHAR2(1) DEFAULT 'N' NOT NULL CONSTRAINT YN_VALIDATIONFIELD357
  CHECK (CHIPPED IN ('Y', 'N')));

COMMENT ON COLUMN FINISHINGMETHOD.CHIPPED
  IS 'Only used for the module CHIPPINGRATE.';

----------- TRANSACTIONTYPE ----------- 
INSERT INTO  TRANSACTIONTYPE
  (TRANSACTIONTYPE_ID, FACTORYDESCRIPTION, SYSTEMUSER_ID, TIMESTAMP)
VALUES 
  (514, 'In scan Clean Return (Exchange Cart)', GETMINSYSTEMUSER(), SYSDATE);
INSERT INTO  TRANSACTIONTYPE
  (TRANSACTIONTYPE_ID, FACTORYDESCRIPTION, SYSTEMUSER_ID, TIMESTAMP)  
VALUES
  (521, 'Clean Count (Exchange Cart)', GETMINSYSTEMUSER(), SYSDATE);
INSERT INTO TRANSACTIONTYPE
  (TRANSACTIONTYPE_ID, FACTORYDESCRIPTION, SYSTEMUSER_ID, TIMESTAMP)  
VALUES
  (532, 'Start Filling (Exchange Cart)', GETMINSYSTEMUSER(), SYSDATE);
INSERT INTO TRANSACTIONTYPE
  (TRANSACTIONTYPE_ID, FACTORYDESCRIPTION, SYSTEMUSER_ID, TIMESTAMP)  
VALUES
  (533, 'Put on Hold (Exchange Cart)', GETMINSYSTEMUSER(), SYSDATE);
INSERT INTO TRANSACTIONTYPE
  (TRANSACTIONTYPE_ID, FACTORYDESCRIPTION, SYSTEMUSER_ID, TIMESTAMP)  
VALUES
  (534, 'Release (Exchange Cart)', GETMINSYSTEMUSER(), SYSDATE);
INSERT INTO TRANSACTIONTYPE
  (TRANSACTIONTYPE_ID, FACTORYDESCRIPTION, SYSTEMUSER_ID, TIMESTAMP)  
VALUES
  (551, 'Exchange Cart', GETMINSYSTEMUSER(), SYSDATE);
COMMIT;


----------- TRANSACTIONTYPE_DESC ----------- 

INSERT INTO TRANSACTIONTYPE_DESC
  (TRANSACTIONTYPE_ID, LANGUAGE_ID, DESCRIPTION, SYSTEMUSER_ID, TIMESTAMP)

  SELECT T.TRANSACTIONTYPE_ID,
         L.LANGUAGE_ID,
         T.FACTORYDESCRIPTION,
         GETMINSYSTEMUSER(),
         SYSDATE
    FROM TRANSACTIONTYPE T, LANGUAGES L
   WHERE L.TRANSLATEDESCRIPTION = 'Y'
     AND T.TRANSACTIONTYPE_ID IN (514, 521, 532, 533, 534, 551);

----------- STAY ----------- 

INSERT INTO STAY
  (STAY_ID, STAYTYPE, STAYNR, SYSTEMUSER_ID, TIMESTAMP, FACTORYDESCRIPTION)
  VALUES
  (SEQ_STAY.NEXTVAL,2, 11,  GETMINSYSTEMUSER(), SYSDATE, 'Manual Count (Exchange Cart)');
INSERT INTO STAY
  (STAY_ID, STAYTYPE, STAYNR, SYSTEMUSER_ID, TIMESTAMP, FACTORYDESCRIPTION)
  VALUES
  (SEQ_STAY.NEXTVAL,2, 42,  GETMINSYSTEMUSER(), SYSDATE, 'Packing (Exchange Cart)');
COMMIT;

----------- STAY_DESC ----------- 

INSERT INTO STAY_DESC
 (STAY_ID, LANGUAGE_ID, DESCRIPTION, SYSTEMUSER_ID, TIMESTAMP)
SELECT S.STAY_ID, L.LANGUAGE_ID, S.FACTORYDESCRIPTION, GETMINSYSTEMUSER(), SYSDATE 
FROM STAY S, LANGUAGES L
WHERE L.TRANSLATEDESCRIPTION = 'Y'
AND S.STAYTYPE = 2 AND S.STAYNR IN (11,42);

----------- CONTAINER ----------- 
ALTER TABLE CONTAINER ADD (SUBSTEXCHANGECART_ID NUMBER NULL,
                           SWAPEXCHANGECART_ID NUMBER NULL,
                           PACKINGSTATUS NUMBER DEFAULT 0 NOT NULL 
                                         CONSTRAINT PACKINGSTATUS_VALID1_1 CHECK (PACKINGSTATUS IN (0,1)));

COMMENT ON COLUMN CONTAINER.SUBSTEXCHANGECART_ID
  IS 'This container is used as a substitute for an exchange cart. A substitute is a one time used container.';
COMMENT ON COLUMN CONTAINER.SWAPEXCHANGECART_ID
  IS 'This container is swapped for repair and needs to be reassigned to the exchange cart.';
COMMENT ON COLUMN CONTAINER.PACKINGSTATUS
  IS '0 = Released,
1 = On hold';

CREATE INDEX XIF10CONTAINER ON CONTAINER (SUBSTEXCHANGECART_ID ASC);
CREATE INDEX XIF11CONTAINER ON CONTAINER (SWAPEXCHANGECART_ID ASC);

ALTER TABLE CONTAINER
  ADD CONSTRAINT CONTAINER_SUBSTEXCHANGECART FOREIGN KEY (SUBSTEXCHANGECART_ID)
  REFERENCES EXCHANGECART (EXCHANGECART_ID) ON DELETE SET NULL;
ALTER TABLE CONTAINER
  ADD CONSTRAINT CONTAINER_SWAPEXCHANGECART FOREIGN KEY (SWAPEXCHANGECART_ID)
  REFERENCES EXCHANGECART (EXCHANGECART_ID) ON DELETE SET NULL;


