--ABS-98598
CREATE TABLE ERP_I_PRICELISTPERCUSTOMER
(
TRANSFER_ID         NUMBER NOT NULL,
TRANSFER_STATUS     NUMBER DEFAULT 0 NOT NULL 
 CONSTRAINT CHK_ERP_PRICELISTPCUST_V1 CHECK (TRANSFER_STATUS IN (-1,0,1)),
CUSTOMERNUMBER      NUMBER NOT NULL,
PRICETYPE           NUMBER NOT NULL 
 CONSTRAINT CHK_ERP_PRICELISTPCUST_V2 CHECK (PRICETYPE IN (1,2,3,4,5,6,7,8,9,10,12)),
PRIORITY            NUMBER NOT NULL,
PRICELISTCODE       VARCHAR2(15) NULL,
DATEACTIVE          DATE DEFAULT TRUNC(SYSDATE)-1 NOT NULL,
DATEINACTIVE        DATE DEFAULT TO_DATE('31-12-2099','DD-MM-YYYY') NULL,
PRICEINDEXCODE      VARCHAR2(6),
PRICEUPDATELOCKDATE DATE NULL,
TIMESTAMP           DATE DEFAULT SYSDATE NOT NULL
);
COMMENT ON COLUMN ERP_I_PRICELISTPERCUSTOMER.TRANSFER_STATUS
  IS '0 = ready to be read, 1 = read correctly';
ALTER TABLE ERP_I_PRICELISTPERCUSTOMER
 ADD CONSTRAINT PK_ERP_I_PRICELISTPERCUST PRIMARY KEY (TRANSFER_ID);
CREATE INDEX XIF1ERP_I_PRICELISTPERCUST ON ERP_I_PRICELISTPERCUSTOMER(TRANSFER_STATUS);
ALTER TABLE ERP_I_PRICELISTPERCUSTOMER
  ADD CONSTRAINT UK_ERP_I_PRICELISTPERCUST UNIQUE (TRANSFER_ID, CUSTOMERNUMBER, PRICETYPE, PRICELISTCODE);
ALTER TABLE ERP_I_PRICELISTPERCUSTOMER
  ADD CONSTRAINT CHK_ERP_PRICELISTPCUST_V3
  CHECK ((PRICELISTCODE IS NULL and PRIORITY BETWEEN 1 and 99) or (PRICELISTCODE is not null and PRIORITY BETWEEN 2 and 99));
COMMENT ON COLUMN ERP_I_PRICELISTPERCUSTOMER.PRICETYPE
  IS '1 = Wash
2 = Rent
3 = Replacement
4 = Sales
5 = Weight
6 = Repair
7 = Emblem
8 = Emblem Template
9 = Garment Insurance
10 = Special Charge
12 = Special Size Charge';
COMMENT ON COLUMN ERP_I_PRICELISTPERCUSTOMER.PRIORITY
  IS 'between 2 and 99';
COMMENT ON COLUMN ERP_I_PRICELISTPERCUSTOMER.PRICEINDEXCODE
  IS 'Optional price index code that is linked to this customer-pricelist';
COMMENT ON COLUMN ERP_I_PRICELISTPERCUSTOMER.PRICEUPDATELOCKDATE
  IS 'Optional price lock date code that is linked to this customer-pricelist';

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