-- ABS-18794/ ABS-18969

--- LEGALENTITY table---
CREATE SEQUENCE SEQ_LEGALENTITY
START WITH 1
INCREMENT BY 1;

CREATE TABLE LEGALENTITY
(
  LEGALENTITY_ID     NUMBER NOT NULL,
  CODE               VARCHAR2(10) NOT NULL,
  NAME               VARCHAR2(40) NOT NULL,
  LEGALNAME          VARCHAR2(80) NOT NULL,
  ADDRESS_ID         NUMBER NOT NULL,
  COUNTRY_ID         NUMBER NOT NULL,
  PHONE              VARCHAR2(30) NULL,
  FAX                VARCHAR2(30) NULL,
  EMAIL              VARCHAR2(300) NULL,
  WEBSITE            VARCHAR2(80) NULL,
  VATNUMBER          VARCHAR2(30) NULL,
  COCNUMBER          VARCHAR2(20) DEFAULT 0 NOT NULL,
  PNNUMBER           VARCHAR2(20) DEFAULT 0 NOT NULL,
  BANKINSTITUTION_ID NUMBER NOT NULL,
  ACTIVE             VARCHAR2(1) DEFAULT 'Y' NOT NULL CONSTRAINT YN_VALIDATIONFIELD255
                     CHECK (ACTIVE IN ('Y', 'N')),
  SYSTEMUSER_ID      NUMBER NOT NULL,
  TIMESTAMP          DATE NOT NULL
);

COMMENT ON COLUMN LEGALENTITY.EMAIL
  IS 'Can hold multiple e-mail addresses separated by semicolons';

ALTER TABLE LEGALENTITY
  ADD CONSTRAINT XPKLEGALENTITY PRIMARY KEY (LEGALENTITY_ID);

CREATE UNIQUE INDEX XAK1LEGALENTITY ON LEGALENTITY (CODE ASC);
CREATE INDEX XIF1LEGALENTITY ON LEGALENTITY (ADDRESS_ID ASC);
CREATE INDEX XIF2LEGALENTITY ON LEGALENTITY (COUNTRY_ID ASC);
CREATE INDEX XIF3LEGALENTITY ON LEGALENTITY (BANKINSTITUTION_ID ASC);

ALTER TABLE LEGALENTITY
  ADD CONSTRAINT LEGALENTITY_ADDRESS FOREIGN KEY (ADDRESS_ID)
  REFERENCES ADDRESS (ADDRESS_ID);
ALTER TABLE LEGALENTITY
  ADD CONSTRAINT LEGALENTITY_COUNTRY FOREIGN KEY (COUNTRY_ID)
  REFERENCES COUNTRY (COUNTRY_ID);
ALTER TABLE LEGALENTITY
  ADD CONSTRAINT LEGALENTITY_BANKINST FOREIGN KEY (BANKINSTITUTION_ID)
  REFERENCES BANKINSTITUTION (BANKINSTITUTION_ID);

---STOCKROOM table---

ALTER TABLE STOCKROOM ADD (LEGALENTITY_ID NUMBER NULL);
 
CREATE INDEX XIF6STOCKROOM ON STOCKROOM (LEGALENTITY_ID ASC);

ALTER TABLE STOCKROOM
  ADD CONSTRAINT STOCKROOM_LEGALENTITY FOREIGN KEY (LEGALENTITY_ID)
  REFERENCES LEGALENTITY (LEGALENTITY_ID) ON DELETE SET NULL;