-- ABS-13987 / ABS-25692

---CUSTOMEREFFICIENCYCHECK table---

CREATE TABLE CUSTOMEREFFICIENCYCHECK
(
  CUSTOMER_ID                NUMBER NOT NULL,
  CHECK1AVGLESSMINSTOCK      VARCHAR2(1) DEFAULT 'N' NOT NULL CONSTRAINT YN_VALIDATIONFIELD276 
                             CHECK (CHECK1AVGLESSMINSTOCK IN ('Y','N')),
  CHECK1AVGLESSMINSTOCKPERC  NUMBER(3) DEFAULT 0 NOT NULL CONSTRAINT CHECK1AVGLESSMINSTOCK_VALID1_1
                             CHECK (CHECK1AVGLESSMINSTOCKPERC BETWEEN 0 AND 100),
  CHECK2AVGMOREMINSTOCK      VARCHAR2(1) DEFAULT 'N' NOT NULL CONSTRAINT YN_VALIDATIONFIELD277 
                             CHECK (CHECK2AVGMOREMINSTOCK IN ('Y','N')),
  CHECK2AVGMOREMINSTOCKPERC  NUMBER(3) DEFAULT 0 NOT NULL CONSTRAINT CHECK2AVGMOREMINSTOCK_VALID1_1
                             CHECK (CHECK2AVGMOREMINSTOCKPERC BETWEEN 0 AND 100),
  CHECK3AVGCONTAINERWEIGHT   VARCHAR2(1) DEFAULT 'N' NOT NULL CONSTRAINT YN_VALIDATIONFIELD278 
                             CHECK (CHECK3AVGCONTAINERWEIGHT IN ('Y','N')),
  CHECK3AVGCONTAINERWEIGHTKG NUMBER(4) DEFAULT 0 NOT NULL CONSTRAINT CHECK3AVGCONTAINERWE_VALID1_1
                             CHECK (CHECK3AVGCONTAINERWEIGHTKG BETWEEN 0 AND 9999),
  CONTACTSYSTEMUSER_ID       NUMBER NOT NULL,
  INTERVALWEEKS              NUMBER(2) DEFAULT 0 NOT NULL CONSTRAINT INTERVALWEEKS_VALID1_1
                             CHECK (INTERVALWEEKS BETWEEN 0 AND 53),
  NEXTCHECK                  DATE NOT NULL,
  LASTCHECK                  DATE NULL,
  SYSTEMUSER_ID              NUMBER NOT NULL,
  TIMESTAMP                  DATE DEFAULT SYSDATE NOT NULL
);

COMMENT ON COLUMN CUSTOMEREFFICIENCYCHECK.CHECK1AVGLESSMINSTOCKPERC
  IS 'Between 0 and 100 %.';
COMMENT ON COLUMN CUSTOMEREFFICIENCYCHECK.CHECK2AVGMOREMINSTOCKPERC
  IS 'Between 0 and 100 %.';
COMMENT ON COLUMN CUSTOMEREFFICIENCYCHECK.CHECK3AVGCONTAINERWEIGHTKG
  IS 'Between 0 and 9999.';
COMMENT ON COLUMN CUSTOMEREFFICIENCYCHECK.INTERVALWEEKS
  IS 'Between 0 and 53.';

ALTER TABLE CUSTOMEREFFICIENCYCHECK
  ADD CONSTRAINT XPKCUSTOMEREFFICIENCYCHECK PRIMARY KEY (CUSTOMER_ID);
  
CREATE INDEX XIF1CUSTOMEREFFICIENCYCHECK ON CUSTOMEREFFICIENCYCHECK (CONTACTSYSTEMUSER_ID ASC);
CREATE INDEX XIE1CUSTOMEREFFICIENCYCHECK ON CUSTOMEREFFICIENCYCHECK (NEXTCHECK ASC);

ALTER TABLE CUSTOMEREFFICIENCYCHECK
  ADD CONSTRAINT CUSTOMEREFFICIENCYCHECK_CU FOREIGN KEY (CUSTOMER_ID)
  REFERENCES CUSTOMER (CUSTOMER_ID) ON DELETE CASCADE;
  
ALTER TABLE CUSTOMEREFFICIENCYCHECK
  ADD CONSTRAINT CUSTOMEREFFICIENCYCHECK_SU FOREIGN KEY (CONTACTSYSTEMUSER_ID)
  REFERENCES SYSTEMUSER (SYSTEMUSER_ID);

---process table---
insert into process(process_id,description,active) values(132,'Delivery Efficiency Check', 'Y');
commit;
