-- ABS-150003

ALTER TABLE INVOICEHEADER ADD (ERP_EXPORTED VARCHAR2(1) DEFAULT 'N' NOT NULL CONSTRAINT CHK_INVOICEHEADER_ERPEXP CHECK (ERP_EXPORTED IN ('Y', 'N')),
                               EXPORTPW DATE NULL );

CREATE TABLE ERP_O_DELIVERYNOTE(
  TRANSFER_ID                 NUMBER NOT NULL,
  TRANSFER_STATUS             NUMBER DEFAULT 0 NOT NULL,
  NOTENUMBER                  NUMBER NOT NULL,
  DELIVERYNOTE_ID             NUMBER NOT NULL, 
  ROUTE                       NUMBER NULL,
  STOPNUMBER                  NUMBER NULL,
  REFERENCE                   VARCHAR2(30) NULL, 
  REMARK                      VARCHAR2(1000) NULL,
  CUSTOMERNUMBER              NUMBER NOT NULL,
  CUSTOMERNAME                VARCHAR2(60) NOT NULL,
  BUSINESSUNIT                VARCHAR2(6) NOT NULL,
  DELIVERYDATE                DATE NULL,
  DEPARTMENTNUMBER            NUMBER NOT NULL, 
  DEPARTMENTDESCRIPTION       VARCHAR2(40) NOT NULL,     
  DISTRIBUTIONPOINTNUMBER     NUMBER NOT NULL, 
  DISTRIBUTIONPOINTDESCR      VARCHAR2(30) NOT NULL,
  DELIVERYPOINTCODE           VARCHAR2(12) NOT NULL,
  DELIVERYPOINTDESCRIPTION    VARCHAR2(30) NOT NULL,
  TIMESTAMP                   DATE DEFAULT SYSDATE NOT NULL);

COMMENT ON COLUMN ERP_O_DELIVERYNOTE.DELIVERYNOTE_ID IS 'UNIQUE ABS RELATED ID';
ALTER TABLE ERP_O_DELIVERYNOTE ADD CONSTRAINT PK_ERP_O_DELIVERYNOTE PRIMARY KEY (TRANSFER_ID);

CREATE TABLE ERP_O_DELIVERYNOTELINE(    
  TRANSFER_ID                 NUMBER NOT NULL,
  TRANSFER_STATUS             NUMBER DEFAULT 0 NOT NULL,   
  DELIVERYNOTE_ID             NUMBER NOT NULL, 
  DELIVERYNOTELINE_ID         NUMBER NOT NULL, 
  PRODUCTCODE                 VARCHAR2(12) NOT NULL,
  PRODUCTDESCRIPTION          VARCHAR2(60) NOT NULL,
  SIZECODE                    VARCHAR2(12) NOT NULL,
  SIZEDESCRIPTION             VARCHAR2(30) NOT NULL,
  FINISHINGMETHODCODE         VARCHAR2(12) NOT NULL,
  FMDESCRIPTION               VARCHAR2(30) NOT NULL,
  ORDERAMOUNT                 NUMBER NULL, 
  DELIVEREDQUANTITY           NUMBER NULL,
  TIMESTAMP                   DATE DEFAULT SYSDATE NOT NULL);


COMMENT ON COLUMN ERP_O_DELIVERYNOTELINE.DELIVERYNOTE_ID IS 'UNIQUE ABS RELATED ID';
ALTER TABLE ERP_O_DELIVERYNOTELINE ADD CONSTRAINT PK_ERP_O_DELIVERYNOTELINE PRIMARY KEY (TRANSFER_ID, DELIVERYNOTELINE_ID); 


--log table
CREATE TABLE ERP_O_DELIVERYNOTE_LOG
(
  TRANSFER_ID     NUMBER NOT NULL,
  TRANSFER_STATUS NUMBER DEFAULT 0 NOT NULL CONSTRAINT CHK_ERP_O_DNOTE_LOG CHECK (TRANSFER_STATUS IN (0,1)),
  TEXT            VARCHAR2(1000) NULL,
  PROCESS_STATUS  NUMBER NOT NULL,
  TIMESTAMP       DATE DEFAULT SYSDATE NOT NULL
);

ALTER TABLE ERP_O_DELIVERYNOTE_LOG
  ADD CONSTRAINT PK_ERP_O_DELIVERYNOTE_LOG PRIMARY KEY (TRANSFER_ID);
     
ALTER TABLE ERP_O_DELIVERYNOTE_LOG
  ADD CONSTRAINT FK_ERP_O_DELIVERYNOTE_LOG FOREIGN KEY (TRANSFER_ID)
  REFERENCES ERP_O_DELIVERYNOTE (TRANSFER_ID) ON DELETE CASCADE;

