--Order 20013411.90

---EXTRABAGS---

CREATE TABLE EXTRABAGS
(
	BUSINESSUNIT_ID      NUMBER NOT NULL ,
	NUMBEROFBAGS         NUMBER(4) NOT NULL  CONSTRAINT  NumberOfBags_valid1_1 CHECK (NUMBEROFBAGS > 0),
	NUMBEROFEXTRABAGS    NUMBER(4) NOT NULL  CONSTRAINT  NumberOfExtraBags_valid1_1 CHECK (NUMBEROFEXTRABAGS >= 0),
	SYSTEMUSER_ID        NUMBER NOT NULL ,
	TIMESTAMP            DATE NOT NULL 
);


CREATE  INDEX XIF1EXTRABAGS ON EXTRABAGS
(BUSINESSUNIT_ID   ASC);

ALTER TABLE EXTRABAGS
ADD CONSTRAINT  XPKEXTRABAGS PRIMARY KEY (BUSINESSUNIT_ID,NUMBEROFBAGS);

ALTER TABLE EXTRABAGS
ADD CONSTRAINT EXTRABAGS_BUSINESSUNIT FOREIGN KEY (BUSINESSUNIT_ID) REFERENCES BUSINESSUNIT (BUSINESSUNIT_ID) ON DELETE CASCADE;


---PRODUCT---
ALTER TABLE PRODUCT
	ADD (EXTRABAG VARCHAR2(1)  DEFAULT 'N' NOT NULL CONSTRAINT YN_ValidationField87 CHECK (EXTRABAG IN ('Y', 'N')));

---BAGTAG---
ALTER TABLE BAGTAG
	ADD (EXTRABAG VARCHAR2(1)  DEFAULT 'N' NOT NULL CONSTRAINT YN_ValidationField88 CHECK (EXTRABAG IN ('Y', 'N')));


---insert into EXTRABGS table---

insert into extrabags
  select bu.businessunit_id,
         1 numberofbags,
         0 numberofextrabags,
         getminsystemuser(),
         sysdate
    from businessunit bu, dual;
commit;

insert into extrabags
  select bu.businessunit_id,
         4 numberofbags,
         1 numberofextrabags,
         getminsystemuser(),
         sysdate
    from businessunit bu, dual;
commit;

insert into extrabags
  select bu.businessunit_id,
         12 numberofbags,
         2 numberofextrabags,
         getminsystemuser(),
         sysdate
    from businessunit bu, dual;
commit;

insert into extrabags
  select bu.businessunit_id,
         18 numberofbags,
         3 numberofextrabags,
         getminsystemuser(),
         sysdate
    from businessunit bu, dual;
commit;

insert into extrabags
  select bu.businessunit_id,
         24 numberofbags,
         4 numberofextrabags,
         getminsystemuser(),
         sysdate
    from businessunit bu, dual;
commit;

insert into extrabags
  select bu.businessunit_id,
         30 numberofbags,
         5 numberofextrabags,
         getminsystemuser(),
         sysdate
    from businessunit bu, dual;
commit;

insert into extrabags
  select bu.businessunit_id,
         36 numberofbags,
         6 numberofextrabags,
         getminsystemuser(),
         sysdate
    from businessunit bu, dual;
commit;

insert into extrabags
  select bu.businessunit_id,
         42 numberofbags,
         7 numberofextrabags,
         getminsystemuser(),
         sysdate
    from businessunit bu, dual;
commit;

insert into extrabags
  select bu.businessunit_id,
         48 numberofbags,
         8 numberofextrabags,
         getminsystemuser(),
         sysdate
    from businessunit bu, dual;
commit;

insert into extrabags
  select bu.businessunit_id,
         54 numberofbags,
         9 numberofextrabags,
         getminsystemuser(),
         sysdate
    from businessunit bu, dual;
commit;

insert into extrabags
  select bu.businessunit_id,
         60 numberofbags,
         10 numberofextrabags,
         getminsystemuser(),
         sysdate
    from businessunit bu, dual;
commit;

insert into extrabags
  select bu.businessunit_id,
         66 numberofbags,
         11 numberofextrabags,
         getminsystemuser(),
         sysdate
    from businessunit bu, dual;
commit;

insert into extrabags
  select bu.businessunit_id,
         72 numberofbags,
         12 numberofextrabags,
         getminsystemuser(),
         sysdate
    from businessunit bu, dual;
commit;

insert into extrabags
  select bu.businessunit_id,
         78 numberofbags,
         13 numberofextrabags,
         getminsystemuser(),
         sysdate
    from businessunit bu, dual;
commit;

insert into extrabags
  select bu.businessunit_id,
         84 numberofbags,
         14 numberofextrabags,
         getminsystemuser(),
         sysdate
    from businessunit bu, dual;
commit;

insert into extrabags
  select bu.businessunit_id,
         90 numberofbags,
         15 numberofextrabags,
         getminsystemuser(),
         sysdate
    from businessunit bu, dual;
commit;

insert into extrabags
  select bu.businessunit_id,
         96 numberofbags,
         16 numberofextrabags,
         getminsystemuser(),
         sysdate
    from businessunit bu, dual;
commit;