--ABS-86097
CREATE TABLE ERP_I_CUSTOMERPREPCHARGE
(
  TRANSFER_ID     NUMBER NOT NULL,
  TRANSFER_STATUS NUMBER DEFAULT 0 NOT NULL CONSTRAINT CHK_ERP_I_CUSTREPCHARGE_V1 CHECK (TRANSFER_STATUS IN (-1,0,1)),
  CUSTOMERNUMBER  NUMBER NOT NULL,
  STARTDATE       DATE DEFAULT TRUNC(SYSDATE-1) NOT NULL,
  PRICE           NUMBER(15,6) NOT NULL,
  REASONCODE      VARCHAR2(6) NULL
);
COMMENT ON COLUMN ERP_I_CUSTOMERPREPCHARGE.TRANSFER_STATUS
  IS '0 = ready to be read, 1 = read correctly';
ALTER TABLE ERP_I_CUSTOMERPREPCHARGE
  ADD CONSTRAINT PK_ERP_I_CUSTPREPCHARGE PRIMARY KEY (TRANSFER_ID);
CREATE INDEX INDX01_ERP_I_CUSTPREPCHARGE ON ERP_I_CUSTOMERPREPCHARGE(TRANSFER_STATUS);

CREATE TABLE ERP_I_CUSTOMERPREPCHARGE_LOG
(
  TRANSFER_ID     NUMBER NOT NULL,
  TRANSFER_STATUS NUMBER DEFAULT 0 NOT NULL  CONSTRAINT CHK_ERP_I_CUSTPREPCHR_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_CUSTOMERPREPCHARGE_LOG.TRANSFER_STATUS
  IS '0 = ready to be read, 1 = read correctly';
COMMENT ON COLUMN ERP_I_CUSTOMERPREPCHARGE_LOG.PROCESS_STATUS
  IS '0 = processed correctly, 1 = process stopped with errors';
ALTER TABLE ERP_I_CUSTOMERPREPCHARGE_LOG
  ADD CONSTRAINT PK_ERP_I_CUSTPREPCHARGE_LOG PRIMARY KEY (TRANSFER_ID);
ALTER TABLE ERP_I_CUSTOMERPREPCHARGE_LOG
  ADD CONSTRAINT FK_ERP_I_CUSTPREPCHARGE_LOG FOREIGN KEY (TRANSFER_ID)
  REFERENCES ERP_I_CUSTOMERPREPCHARGE(TRANSFER_ID) ON DELETE CASCADE;

CREATE TABLE ERP_I_CONTRACTPRICE
(
  TRANSFER_ID       NUMBER NOT NULL,
  TRANSFER_STATUS   NUMBER(1) DEFAULT 0 NOT NULL CONSTRAINT CHK_ERP_I_CONTRACTPRICE_V1 CHECK (TRANSFER_STATUS IN (-1,0,1)),
  CONTRACTCODE      VARCHAR2(15) NOT NULL,
  CATEGORY          NUMBER(3) NOT NULL CONSTRAINT CHK_ERP_I_CONTRACTPRICE_V2 CHECK (CATEGORY IN (1,2,3,4,5,6,7,8,9)),
  PRICETYPE         NUMBER(3) NOT NULL CONSTRAINT CHK_ERP_I_CONTRACTPRICE_V3 CHECK (PRICETYPE IN (0,1,2,3,5)),
  STARTDATE         DATE DEFAULT TRUNC(SYSDATE-1) NOT NULL,
  PRICE             NUMBER(15,6) NOT NULL,
  REASONCODE        VARCHAR2(6) NULL
);
COMMENT ON COLUMN ERP_I_CONTRACTPRICE.TRANSFER_STATUS
  IS '0 = ready to be read, 1 = read correctly';
COMMENT ON COLUMN ERP_I_CONTRACTPRICE.CATEGORY
  IS '1 = Service Costs LOG 
2 = Insurance - Weekly fee per piece 
3 = Restocking Fee 
4 = Stock Charge 
5 = Locker 
6 = General Prep Charge 
7 = Surcharge Price 
8 = Label Price 
9 = Cash Delay Charge';
COMMENT ON COLUMN ERP_I_CONTRACTPRICE.PRICETYPE
  IS '0 Means not applicable
Category 1 (Service Costs) 
1 = Service Costs LOG 
2 = Service Costs COG 
Category 5 (Locker) 
1 = Rent price per week 
2 = Service Price 
Category 7 (Surcharge price)
1 = Non Standard Size Surcharge 
2 = Special Cut Surcharge 
Category 8 (Label Price) 
1 = Code Label Price 
2 = Name Label Price 
3 = Residential Label 
Note option 4 is not used
5 = Patch Price';
COMMENT ON COLUMN ERP_I_CONTRACTPRICE.REASONCODE
  IS 'Can only be used in combination with Category = 6, set to Null for all other categories.';
ALTER TABLE ERP_I_CONTRACTPRICE
  ADD CONSTRAINT PK_ERP_I_CONTRACTPRICE PRIMARY KEY (TRANSFER_ID);
CREATE INDEX INDX_ERP_I_CONTRPRC_CONTR ON ERP_I_CONTRACTPRICE (CONTRACTCODE,CATEGORY,PRICETYPE,STARTDATE,REASONCODE);
ALTER TABLE ERP_I_CONTRACTPRICE
  ADD CONSTRAINT CHK_ERP_I_CONTRPR_CATTYPE
  CHECK ( PRICETYPE = 0 OR (CATEGORY IN (1,5,7) AND PRICETYPE IN (1,2)) OR
     (CATEGORY = 8 AND PRICETYPE IN (1,2,3,5)));

CREATE TABLE ERP_I_CONTRACTPRICE_LOG
(
  TRANSFER_ID     NUMBER NOT NULL,
  TRANSFER_STATUS NUMBER DEFAULT 0 NOT NULL  CONSTRAINT CHK_ERP_I_CONTRPRICE_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_CONTRACTPRICE_LOG.TRANSFER_STATUS
  IS '0 = ready to be read, 1 = read correctly';
COMMENT ON COLUMN  ERP_I_CONTRACTPRICE_LOG.PROCESS_STATUS
  IS '0 = processed correctly, 1 = process stopped with errors';
ALTER TABLE ERP_I_CONTRACTPRICE_LOG
  ADD CONSTRAINT PK_ERP_I_CONTRACTPRICE_LOG PRIMARY KEY (TRANSFER_ID);
ALTER TABLE ERP_I_CONTRACTPRICE_LOG
  ADD CONSTRAINT FK_ERP_I_CONTRACTPRICE_LOG FOREIGN KEY (TRANSFER_ID)
  REFERENCES ERP_I_CONTRACTPRICE(TRANSFER_ID) ON DELETE CASCADE; 