--ABS-115972 / ABS-121127
-- CODETAPEPLACEMENT --
CREATE SEQUENCE SEQ_CODETAPEPLACEMENT
START WITH 1
INCREMENT BY 1;

CREATE TABLE CODETAPEPLACEMENT
(
  CODETAPEPLACEMENT_ID           NUMBER NOT NULL,
  CODE                           VARCHAR2(6) NOT NULL,
  SYSTEMUSER_ID                  NUMBER NOT NULL,
  TIMESTAMP                      DATE NOT NULL
);

ALTER TABLE CODETAPEPLACEMENT
  ADD CONSTRAINT XPKCODETAPEPLACEMENT PRIMARY KEY (CODETAPEPLACEMENT_ID);

CREATE UNIQUE INDEX XAK1CODETAPEPLACEMENT ON CODETAPEPLACEMENT (CODE ASC);


-- CODETAPEPLACEMENT_DESC --

CREATE TABLE CODETAPEPLACEMENT_DESC
(
  CODETAPEPLACEMENT_ID           NUMBER NOT NULL,
  LANGUAGE_ID                    NUMBER NOT NULL,
  DESCRIPTION                    VARCHAR2(40) NOT NULL,
  SYSTEMUSER_ID                  NUMBER NOT NULL,
  TIMESTAMP                      DATE NOT NULL
);

ALTER TABLE CODETAPEPLACEMENT_DESC
  ADD CONSTRAINT XPKCODETAPEPLACEMENT_DESC PRIMARY KEY (CODETAPEPLACEMENT_ID, LANGUAGE_ID);
  
CREATE INDEX XIF1CODETAPEPLACEMENT_DESC ON CODETAPEPLACEMENT_DESC (CODETAPEPLACEMENT_ID ASC);
CREATE INDEX XIF2CODETAPEPLACEMENT_DESC ON CODETAPEPLACEMENT_DESC (LANGUAGE_ID ASC);

ALTER TABLE CODETAPEPLACEMENT_DESC
  ADD CONSTRAINT FK_CODETAPEPLDESC_CODETAPEPLAC FOREIGN KEY (CODETAPEPLACEMENT_ID)
  REFERENCES CODETAPEPLACEMENT (CODETAPEPLACEMENT_ID) ON DELETE CASCADE;
ALTER TABLE CODETAPEPLACEMENT_DESC
  ADD CONSTRAINT FK_CODETAPEPLDESC_LANGUAGE FOREIGN KEY (LANGUAGE_ID)
  REFERENCES LANGUAGES (LANGUAGE_ID);
	
-- PRODUCT --
ALTER TABLE PRODUCT ADD(CODETAPEPLACEMENT_ID NUMBER NULL);
CREATE INDEX XIF913PRODUCT ON PRODUCT(CODETAPEPLACEMENT_ID);
ALTER TABLE PRODUCT
  ADD CONSTRAINT FK_PRODUCT_CODETAPEPLAC FOREIGN KEY (CODETAPEPLACEMENT_ID)
  REFERENCES CODETAPEPLACEMENT (CODETAPEPLACEMENT_ID) ON DELETE SET NULL;
	
-- PLANTPRODUCTSETTINGS --
ALTER TABLE PLANTPRODUCTSETTINGS ADD(CODETAPEPLACEMENT_ID NUMBER NULL);
CREATE INDEX XIF6PLANTPRODUCTSETTINGS ON PLANTPRODUCTSETTINGS(CODETAPEPLACEMENT_ID);
ALTER TABLE PLANTPRODUCTSETTINGS
  ADD CONSTRAINT FK_PLANTPRODSET_CODETAPEPLAC FOREIGN KEY (CODETAPEPLACEMENT_ID)
  REFERENCES CODETAPEPLACEMENT (CODETAPEPLACEMENT_ID) ON DELETE SET NULL;
	
-- ERP_I_PLANTPRODUCTSETTINGS --
ALTER TABLE ERP_I_PLANTPRODUCTSETTINGS ADD (CODETAPEPLACEMENT VARCHAR2(6));
