--Order 20015729.70

---------------------RAGREASON---------------

ALTER TABLE RAGREASON ADD (ORCONTENTSITEMS VARCHAR2(1) DEFAULT 'N' NOT NULL CONSTRAINT YN_VALIDATIONFIELD238 CHECK (ORCONTENTSITEMS IN ('Y','N')));

--------------------BUSINESSUNIT-----------

ALTER TABLE BUSINESSUNIT ADD (DEPOSITLOCATIONORCONTENTS VARCHAR2(20) NULL, 
			              ORCONTENTSREASONCODE_ID NUMBER NULL);


ALTER TABLE BUSINESSUNIT 
  ADD CONSTRAINT BU_REASONCODE FOREIGN KEY (ORCONTENTSREASONCODE_ID)
  REFERENCES REASONCODE (REASONCODE_ID) ON DELETE SET NULL;

CREATE INDEX XIF8BUSINESSUNIT  ON BUSINESSUNIT 
(ORCONTENTSREASONCODE_ID   ASC);

--Order 20015738.80

---------------------ORDEREDUSEDORPACK---------------

CREATE SEQUENCE SEQ_ORDEREDUSEDORPACK
START WITH 1
INCREMENT BY 1;

CREATE TABLE ORDEREDUSEDORPACK
(
	ORDEREDUSEDORPACK_ID     NUMBER NOT NULL ,
	IMPORTFILENAME                    VARCHAR2(250) NULL ,
	LINENUMBER                             NUMBER DEFAULT 0 NOT NULL ,
	INPUTSTRING                            VARCHAR2(43) NOT NULL ,
	USAGEDATE                               DATE DEFAULT SYSDATE NOT NULL ,
	IMPORTDATE                             DATE DEFAULT SYSDATE NOT NULL ,
	ORPACKIDCODE                        VARCHAR2(24) NULL ,
	CUSTOMER_ID		     NUMBER NULL,
	DEPARTMENT_ID		     NUMBER NULL,
	DISTRIBUTIONPOINT_ID          NUMBER NULL,
	DELIVERYPOINT_ID                  NUMBER NULL,
	PACKINGNOTE_ID                     NUMBER NULL,
	DELIVERYDATE	                     DATE NULL,
	PRODUCT_ID	                     NUMBER NULL,
	SIZEDEFINITION_ID                  NUMBER NULL,
	FINISHINGMETHOD_ID	     NUMBER NULL,
	ORSERVICINGBU_ID                 NUMBER NULL,
	PROCESSSTATUS		     NUMBER(1)  DEFAULT 0 NOT NULL,
	EMAILSTATUS		     NUMBER(1) DEFAULT 0 NOT NULL,
	MESSAGE		     VARCHAR2(300) NULL,
	SYSTEMUSER_ID		     NUMBER DEFAULT 0 NOT NULL,
	TIMESTAMP		     DATE DEFAULT SYSDATE NOT NULL
);

ALTER TABLE ORDEREDUSEDORPACK
ADD CONSTRAINT  XPKORDEREDUSEDORPACK PRIMARY KEY (ORDEREDUSEDORPACK_ID);

ALTER TABLE ORDEREDUSEDORPACK
  ADD CONSTRAINT ORDERUSEORPACK_CUST FOREIGN KEY (CUSTOMER_ID)
  REFERENCES CUSTOMER(CUSTOMER_ID) ON DELETE SET NULL;

CREATE INDEX XIF1ORDEREDUSEDORPACK  ON ORDEREDUSEDORPACK
(CUSTOMER_ID   ASC);

ALTER TABLE ORDEREDUSEDORPACK
  ADD CONSTRAINT ORDERUSEORPACK_DEPART FOREIGN KEY (DEPARTMENT_ID)
  REFERENCES DEPARTMENT(DEPARTMENT_ID) ON DELETE SET NULL;

CREATE INDEX XIF2ORDEREDUSEDORPACK  ON ORDEREDUSEDORPACK
(DEPARTMENT_ID   ASC);

ALTER TABLE ORDEREDUSEDORPACK
  ADD CONSTRAINT ORDERUSEORPACK_DISTRIB FOREIGN KEY (DISTRIBUTIONPOINT_ID)
  REFERENCES DISTRIBUTIONPOINT(DISTRIBUTIONPOINT_ID) ON DELETE SET NULL;

CREATE INDEX XIF3ORDEREDUSEDORPACK  ON ORDEREDUSEDORPACK
(DISTRIBUTIONPOINT_ID   ASC);

ALTER TABLE ORDEREDUSEDORPACK
  ADD CONSTRAINT ORDERUSEORPACK_DELIVPOINT FOREIGN KEY (DELIVERYPOINT_ID)
  REFERENCES DELIVERYPOINT(DELIVERYPOINT_ID) ON DELETE SET NULL;

CREATE INDEX XIF4ORDEREDUSEDORPACK  ON ORDEREDUSEDORPACK
(DELIVERYPOINT_ID   ASC);

ALTER TABLE ORDEREDUSEDORPACK
  ADD CONSTRAINT ORDERUSEORPACK_PACKINGNOTE FOREIGN KEY (PACKINGNOTE_ID)
  REFERENCES PACKINGNOTE(PACKINGNOTE_ID) ON DELETE SET NULL;

CREATE INDEX XIF5ORDEREDUSEDORPACK  ON ORDEREDUSEDORPACK
(PACKINGNOTE_ID   ASC);

ALTER TABLE ORDEREDUSEDORPACK
  ADD CONSTRAINT ORDERUSEORPACK_PROD FOREIGN KEY (PRODUCT_ID)
  REFERENCES PRODUCT(PRODUCT_ID) ON DELETE SET NULL;

CREATE INDEX XIF6ORDEREDUSEDORPACK  ON ORDEREDUSEDORPACK
(PRODUCT_ID   ASC);

ALTER TABLE ORDEREDUSEDORPACK
  ADD CONSTRAINT ORDERUSEORPACK_SIZE FOREIGN KEY (SIZEDEFINITION_ID)
  REFERENCES SIZEDEFINITION(SIZEDEFINITION_ID) ON DELETE SET NULL;

CREATE INDEX XIF7ORDEREDUSEDORPACK  ON ORDEREDUSEDORPACK
(SIZEDEFINITION_ID   ASC);

ALTER TABLE ORDEREDUSEDORPACK
  ADD CONSTRAINT ORDERUSEORPACK_FM FOREIGN KEY (FINISHINGMETHOD_ID)
  REFERENCES FINISHINGMETHOD(FINISHINGMETHOD_ID) ON DELETE SET NULL;

CREATE INDEX XIF8ORDEREDUSEDORPACK  ON ORDEREDUSEDORPACK
(FINISHINGMETHOD_ID   ASC);

ALTER TABLE ORDEREDUSEDORPACK
  ADD CONSTRAINT ORDERUSEORPACK_BU FOREIGN KEY (ORSERVICINGBU_ID)
  REFERENCES BUSINESSUNIT(BUSINESSUNIT_ID) ON DELETE SET NULL;

CREATE INDEX XIF9ORDEREDUSEDORPACK  ON ORDEREDUSEDORPACK
(ORSERVICINGBU_ID   ASC);

--------------------BUSINESSUNIT-----------

ALTER TABLE BUSINESSUNIT ADD (SENDINGEMAILADDRESS VARCHAR2(300) NULL,
			              RECIPIENTEMAILADDRESSNOERRORS VARCHAR2(300) NULL,
			              RECIPIENTEMAILADDRESSFAILED VARCHAR2(300) NULL);


--------------------BUORDERINGCUSTOMER-----------

CREATE SEQUENCE SEQ_BUORDERINGCUSTOMER
START WITH 1
INCREMENT BY 1;

CREATE TABLE BUORDERINGCUSTOMER
(
	BUORDERINGCUSTOMER_ID  NUMBER NOT NULL ,
	BUSINESSUNIT_ID                     NUMBER NOT NULL ,
	CUSTOMER_ID		     NUMBER NOT NULL,
	OKCONTACTPERSON_ID	     NUMBER NULL,
	FAILCONTACTPERSON_ID       NUMBER NULL,
	IMPORTFILENAME                    VARCHAR2(32) NULL,
	SYSTEMUSER_ID		     NUMBER DEFAULT 0 NOT NULL,
	TIMESTAMP		     DATE DEFAULT SYSDATE NOT NULL
);

ALTER TABLE BUORDERINGCUSTOMER
ADD CONSTRAINT  XPKBUORDERINGCUSTOMER PRIMARY KEY (BUORDERINGCUSTOMER_ID);

ALTER TABLE BUORDERINGCUSTOMER
  ADD CONSTRAINT BUORDERCUST_BU FOREIGN KEY (BUSINESSUNIT_ID)
  REFERENCES BUSINESSUNIT(BUSINESSUNIT_ID) ON DELETE CASCADE;

CREATE INDEX XIF1BUORDERINGCUSTOMER  ON BUORDERINGCUSTOMER
(BUSINESSUNIT_ID   ASC);

ALTER TABLE BUORDERINGCUSTOMER
  ADD CONSTRAINT BUORDERCUST_CUST FOREIGN KEY (CUSTOMER_ID)
  REFERENCES CUSTOMER(CUSTOMER_ID) ON DELETE CASCADE;

CREATE INDEX XIF2BUORDERINGCUSTOMER  ON BUORDERINGCUSTOMER
(CUSTOMER_ID   ASC);

ALTER TABLE BUORDERINGCUSTOMER
  ADD CONSTRAINT BUORDERCUST_OKCONTACTPERS FOREIGN KEY (OKCONTACTPERSON_ID)
  REFERENCES CONTACTPERSON(CONTACTPERSON_ID);

CREATE INDEX XIF3BUORDERINGCUSTOMER  ON BUORDERINGCUSTOMER
(OKCONTACTPERSON_ID);

ALTER TABLE BUORDERINGCUSTOMER
  ADD CONSTRAINT BUORDERCUST_FAILCONTACTPERS FOREIGN KEY (FAILCONTACTPERSON_ID)
  REFERENCES CONTACTPERSON(CONTACTPERSON_ID);

CREATE INDEX XIF4BUORDERINGCUSTOMER  ON BUORDERINGCUSTOMER
(FAILCONTACTPERSON_ID);

CREATE UNIQUE INDEX XAK1BUORDERINGCUSTOMER ON BUORDERINGCUSTOMER
(BUSINESSUNIT_ID   ASC,CUSTOMER_ID   ASC);

---From PVD

create index XIF10ORDEREDUSEDORPACK on ORDEREDUSEDORPACK (PROCESSSTATUS);
comment on column ORDEREDUSEDORPACK.PROCESSSTATUS
  is 'Possible values:
0 = Imported,
1 = OK,
2 = ERROR';


---Order 20016333

ALTER TABLE UHFTAGFILTER ADD (RSSIOFFSET NUMBER DEFAULT 0 NOT NULL);
 
COMMENT ON COLUMN UHFTAGFILTER.RSSIOFFSET
  IS 'Defines RSSI offset for filtering out tags that are too far away.';
