--ABS-98595
CREATE TABLE ERP_I_NONBUSINESSDAYS
(
TRANSFER_ID           NUMBER NOT NULL,
TRANSFER_STATUS       NUMBER DEFAULT 0 NOT NULL 
 CONSTRAINT CHK_ERP_INONBUDAYS_V1 CHECK (TRANSFER_STATUS IN (-1,0,1)),
CUSTOMERNUMBER        NUMBER NOT NULL,
DEPARTMENTNUMBER      NUMBER NULL,
NONBUSINESSDATE_START DATE NOT NULL,
NONBUSINESSDATE_END   DATE NULL,
CLOSESTATUS           NUMBER DEFAULT 1 NOT NULL
  CONSTRAINT CHK_ERP_INONBUDAYS_V2 CHECK (CLOSESTATUS IN (1,2,3,4)),
TIMESTAMP             DATE DEFAULT SYSDATE NOT NULL
);
COMMENT ON COLUMN ERP_I_NONBUSINESSDAYS.TRANSFER_STATUS
  IS '0 = ready to be read, 1 = read correctly';
COMMENT ON COLUMN ERP_I_NONBUSINESSDAYS.CUSTOMERNUMBER
  IS 'Must be an existing customer';
COMMENT ON COLUMN ERP_I_NONBUSINESSDAYS.DEPARTMENTNUMBER
  IS 'Must be an existing department for this customer, if filled the none business date will only be for this department not customer.';
COMMENT ON COLUMN ERP_I_NONBUSINESSDAYS.NONBUSINESSDATE_START
  IS 'Start date for day as customer nonbusiness day';
COMMENT ON COLUMN ERP_I_NONBUSINESSDAYS.NONBUSINESSDATE_END
  IS 'Maximum number of days is 200, so the difference between start and end should be between 0 and 200 max.';
comment on column ERP_I_NONBUSINESSDAYS.closestatus
  is '1 = Closed
2 = Closed for delivery
3 = Open
4 = Closed including rent stop';
ALTER TABLE ERP_I_NONBUSINESSDAYS
 ADD CONSTRAINT PK_ERP_I_NONBUDAYS PRIMARY KEY (TRANSFER_ID);
CREATE INDEX XIE1ERP_I_NONBUDAYS ON ERP_I_NONBUSINESSDAYS(TRANSFER_STATUS);
ALTER TABLE ERP_I_NONBUSINESSDAYS
  ADD CONSTRAINT UK_ERP_I_NONBUDAYS UNIQUE (TRANSFER_ID, CUSTOMERNUMBER, DEPARTMENTNUMBER, NONBUSINESSDATE_START);
ALTER TABLE ERP_I_NONBUSINESSDAYS
  ADD CONSTRAINT CHK_ERP_INONBUDAYS_V3
  CHECK (NVL(NONBUSINESSDATE_END,NONBUSINESSDATE_START) - NONBUSINESSDATE_START BETWEEN 0 AND 200);

CREATE TABLE ERP_I_NONBUDAYS_LOG
(
  TRANSFER_ID     NUMBER NOT NULL,
  TRANSFER_STATUS NUMBER DEFAULT 0 NOT NULL  CONSTRAINT CHK_ERP_I_NONBUDAYS_LOG_V1 CHECK (TRANSFER_STATUS IN (-1,0,1)),
  TEXT            VARCHAR2(1000) NULL,
  PROCESS_STATUS  NUMBER DEFAULT 0 NOT NULL,
  TIMESTAMP       DATE DEFAULT SYSDATE NOT NULL
);
COMMENT ON COLUMN ERP_I_NONBUDAYS_LOG.TRANSFER_STATUS
  IS '0 = ready to be read, 1 = read correctly';
COMMENT ON COLUMN ERP_I_NONBUDAYS_LOG.PROCESS_STATUS
  IS '0 = processed correctly, 1 = process stopped with errors';
ALTER TABLE ERP_I_NONBUDAYS_LOG
  ADD CONSTRAINT PK_ERP_I_NONBUDAYS_LOG PRIMARY KEY (TRANSFER_ID);
ALTER TABLE ERP_I_NONBUDAYS_LOG
  ADD CONSTRAINT FK_ERP_I_NONBUDAYS_LOG FOREIGN KEY (TRANSFER_ID)
  REFERENCES ERP_I_NONBUSINESSDAYS(TRANSFER_ID) ON DELETE CASCADE;

CREATE TABLE ERP_I_CUSTOMERSPECIALCHARGE
(
TRANSFER_ID       NUMBER NOT NULL,
TRANSFER_STATUS   NUMBER DEFAULT 0 NOT NULL 
 CONSTRAINT CHK_ERP_ICUSTRSPECHARGE_V1 CHECK (TRANSFER_STATUS IN (-1,0,1)),
CUSTOMERNUMBER    NUMBER NOT NULL,
SPECIALCHARGECODE VARCHAR2(6) NOT NULL,
STARTDATE         DATE NOT NULL,
PRICE             NUMBER(11,6) NULL,
PERCENTAGE        NUMBER NULL,
MINIMUMAMOUNT     NUMBER(28,6) NULL,
MAXIMUMAMOUNT     NUMBER(28,6) NULL,
TIMESTAMP         DATE DEFAULT SYSDATE NOT NULL
);
COMMENT ON COLUMN ERP_I_CUSTOMERSPECIALCHARGE.TRANSFER_STATUS
  IS '0 = ready to be read, 1 = read correctly';
COMMENT ON COLUMN ERP_I_CUSTOMERSPECIALCHARGE.CUSTOMERNUMBER
  IS 'Must be an existing customer';
COMMENT ON COLUMN ERP_I_CUSTOMERSPECIALCHARGE.SPECIALCHARGECODE
  IS 'Must be an existing special charge';
COMMENT ON COLUMN ERP_I_CUSTOMERSPECIALCHARGE.STARTDATE
  IS 'Default yesterday';
COMMENT ON COLUMN ERP_I_CUSTOMERSPECIALCHARGE.PRICE
  IS 'Use price or percentage';
COMMENT ON COLUMN ERP_I_CUSTOMERSPECIALCHARGE.PERCENTAGE
  IS 'Use price or percentage';
COMMENT ON COLUMN ERP_I_CUSTOMERSPECIALCHARGE.MINIMUMAMOUNT
  IS 'Only used for percentage';
COMMENT ON COLUMN ERP_I_CUSTOMERSPECIALCHARGE.MAXIMUMAMOUNT
  IS 'Only used for percentage';
COMMENT ON COLUMN ERP_I_CUSTOMERSPECIALCHARGE.TIMESTAMP
  IS 'No default';
ALTER TABLE ERP_I_CUSTOMERSPECIALCHARGE
 ADD CONSTRAINT PK_ERP_I_CUSTRSPECIALCHARGE PRIMARY KEY (TRANSFER_ID);
CREATE INDEX XIF1ERP_I_CUSTRSPECIALCHARGE ON ERP_I_CUSTOMERSPECIALCHARGE(TRANSFER_STATUS);
ALTER TABLE ERP_I_CUSTOMERSPECIALCHARGE
  ADD CONSTRAINT UK_ERP_I_CUSTRSPECIALCHARGE UNIQUE (TRANSFER_ID, CUSTOMERNUMBER, SPECIALCHARGECODE);
ALTER TABLE ERP_I_CUSTOMERSPECIALCHARGE
  ADD CONSTRAINT CHK_ERP_ICUSTRSPECHARGE_V2
  CHECK (PRICE IS NOT NULL OR PERCENTAGE IS NOT NULL);

CREATE TABLE ERP_I_CUSTSPECIALCHARGE_LOG
(
  TRANSFER_ID     NUMBER NOT NULL,
  TRANSFER_STATUS NUMBER DEFAULT 0 NOT NULL  CONSTRAINT CHK_ERP_I_CUSTSPCHRG_LOG_V1 CHECK (TRANSFER_STATUS IN (-1,0,1)),
  TEXT            VARCHAR2(1000) NULL,
  PROCESS_STATUS  NUMBER DEFAULT 0 NOT NULL,
  TIMESTAMP       DATE DEFAULT SYSDATE NOT NULL
);
COMMENT ON COLUMN ERP_I_CUSTSPECIALCHARGE_LOG.TRANSFER_STATUS
  IS '0 = ready to be read, 1 = read correctly';
COMMENT ON COLUMN ERP_I_CUSTSPECIALCHARGE_LOG.PROCESS_STATUS
  IS '0 = processed correctly, 1 = process stopped with errors';
ALTER TABLE ERP_I_CUSTSPECIALCHARGE_LOG
  ADD CONSTRAINT PK_ERP_I_CUSTRSPECHARGE_LOG PRIMARY KEY (TRANSFER_ID);
ALTER TABLE ERP_I_CUSTSPECIALCHARGE_LOG
  ADD CONSTRAINT FK_ERP_I_CUSTRSPECHARGE_LOG FOREIGN KEY (TRANSFER_ID)
  REFERENCES ERP_I_CUSTOMERSPECIALCHARGE(TRANSFER_ID) ON DELETE CASCADE;