--Order 20014526.140

---PACKINGNOTEBOX table---

CREATE SEQUENCE SEQ_PACKINGNOTEBOX 
START WITH 1
INCREMENT BY 1;


CREATE TABLE PACKINGNOTEBOX
(
	PACKINGNOTEBOX_ID    NUMBER NOT NULL ,
	PACKINGNOTE_ID       NUMBER NULL ,
	BOXNUMBER            NUMBER DEFAULT  1  NULL  CONSTRAINT  BOXNUMBER_valid CHECK (BOXNUMBER BETWEEN 1 AND 999),
	SYSTEMUSER_ID        NUMBER NOT NULL ,
	TIMESTAMP            DATE NOT NULL 
);

ALTER TABLE PACKINGNOTEBOX
ADD CONSTRAINT  XPKPACKINGNOTEBOX PRIMARY KEY (PACKINGNOTEBOX_ID);

ALTER TABLE PACKINGNOTEBOX
ADD CONSTRAINT PACKINGNOTEBOX_PACKINGNOTE FOREIGN KEY (PACKINGNOTE_ID) REFERENCES PACKINGNOTE (PACKINGNOTE_ID) ON DELETE CASCADE;

CREATE INDEX XIF1PACKINGNOTEBOX ON PACKINGNOTEBOX
(PACKINGNOTE_ID   ASC);

CREATE UNIQUE INDEX XAK1PACKINGNOTEBOX ON PACKINGNOTEBOX
(PACKINGNOTE_ID   ASC,BOXNUMBER   ASC);


---PACKINGNOTEBOXQUANTITY table---

CREATE SEQUENCE SEQ_PACKINGNOTEBOXQUANTITY 
START WITH 1
INCREMENT BY 1;

CREATE TABLE PACKINGNOTEBOXQUANTITY
(
	PACKINGNOTEBOXQUANTITY_ID NUMBER NOT NULL ,
	PACKINGNOTEBOX_ID    NUMBER NULL ,
	PACKINGNOTELINE_ID   NUMBER NULL ,
	DELIVEREDQTY         NUMBER NULL ,
	SYSTEMUSER_ID        NUMBER NOT NULL ,
	TIMESTAMP            DATE NOT NULL 
);

ALTER TABLE PACKINGNOTEBOXQUANTITY
ADD CONSTRAINT  XPKPACKINGNOTEBOXQUANTITY PRIMARY KEY (PACKINGNOTEBOXQUANTITY_ID);

ALTER TABLE PACKINGNOTEBOXQUANTITY
ADD CONSTRAINT PACKINGNOTEBOXQUANTITY_PNBOX FOREIGN KEY (PACKINGNOTEBOX_ID) REFERENCES PACKINGNOTEBOX (PACKINGNOTEBOX_ID) ON DELETE CASCADE;

CREATE INDEX XIF1PACKINGNOTEBOXQUANTITY ON PACKINGNOTEBOXQUANTITY
(PACKINGNOTEBOX_ID   ASC);

ALTER TABLE PACKINGNOTEBOXQUANTITY
ADD CONSTRAINT PACKINGNOTEBOXQUANTITY_PNL FOREIGN KEY (PACKINGNOTELINE_ID) REFERENCES PACKINGNOTELINE (PACKINGNOTELINE_ID) ON DELETE CASCADE;

CREATE INDEX XIF2PACKINGNOTEBOXQUANTITY ON PACKINGNOTEBOXQUANTITY
(PACKINGNOTELINE_ID   ASC);

CREATE UNIQUE INDEX XAK1PACKINGNOTEBOXQUANTITY ON PACKINGNOTEBOXQUANTITY
(PACKINGNOTELINE_ID   ASC,PACKINGNOTEBOX_ID   ASC);










