-- ABS-128151 / ABS-129230
-------------ERP_I_CONTRACTSPERCUSTOMER-------------
CREATE TABLE ERP_I_CONTRACTSPERCUSTOMER 
( TRANSFER_ID                 NUMBER NOT NULL,
  TRANSFER_STATUS             NUMBER(1) DEFAULT 0 NOT NULL,
  CONTRACTCODE                VARCHAR2(15) NOT NULL,
  CUSTOMERNUMBER              NUMBER NOT NULL,
  PURCHASEORDER               VARCHAR2(30) NULL,
  PURCHASEORDEREXPIRATIONDATE DATE NULL,
  PURCHASEORDERVALUE          NUMBER(15,6) NULL,
  ESTIMATEDWEEKLYSALES        NUMBER NULL);
  
COMMENT ON COLUMN ERP_I_CONTRACTSPERCUSTOMER.TRANSFER_STATUS
  IS '-1 = staging, 0 = ready to be read, 1 = read correctly';
COMMENT ON COLUMN ERP_I_CONTRACTSPERCUSTOMER.CONTRACTCODE
  IS 'Unique contract code';
ALTER TABLE ERP_I_CONTRACTSPERCUSTOMER
  ADD CONSTRAINT PK_ERP_I_CONTRACTSPERCUSTOMER PRIMARY KEY (TRANSFER_ID);
  
CREATE INDEX XAK1ERP_I_CONTRACTSPERCUSTOMER ON ERP_I_CONTRACTSPERCUSTOMER (CONTRACTCODE ASC, CUSTOMERNUMBER ASC); 

-------------ERP_I_CONTRACTSPERCUSTOMER_LOG-------------  
CREATE TABLE ERP_I_CONTRACTSPERCUSTOMER_LOG
( TRANSFER_ID     NUMBER NOT NULL,
  TRANSFER_STATUS NUMBER DEFAULT 0 NOT NULL  CONSTRAINT CHK_ERP_I_CONTRACTSPERCUST_LOG 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_CONTRACTCUSTOMER_LOG.TRANSFER_STATUS
  IS '-1 = staging, 0 = ready to be read, 1 = read correctly';
COMMENT ON COLUMN ERP_I_CONTRACTCUSTOMER_LOG.PROCESS_STATUS
  IS '0 = processed correctly, 1 = process stopped with errors';
  
ALTER TABLE ERP_I_CONTRACTSPERCUSTOMER_LOG
  ADD CONSTRAINT PK_ERP_I_CONTRACTSPERCUST_LOG PRIMARY KEY (TRANSFER_ID);
  
CREATE INDEX XIE1ERP_I_CONTRACTSPERCUST_LOG ON ERP_I_CONTRACTSPERCUSTOMER_LOG (TIMESTAMP ASC);

ALTER TABLE ERP_I_CONTRACTSPERCUSTOMER_LOG
  ADD CONSTRAINT FK_ERP_I_CONTRACTSPERCUST_LOG FOREIGN KEY (TRANSFER_ID)
  REFERENCES ERP_I_CONTRACTSPERCUSTOMER (TRANSFER_ID) ON DELETE CASCADE;  
