--ABS-150002

CREATE TABLE ERP_I_SALESORDER
( TRANSFER_ID                 NUMBER NOT NULL,
  TRANSFER_STATUS             NUMBER DEFAULT 0 NOT NULL,
  SALESORDERNUMBER            NUMBER NOT NULL, 
  BUSINESSUNITCODE            VARCHAR2(6) NOT NULL,
  ROUTE                       NUMBER NOT NULL, 
  ORDERDATE                   DATE NOT NULL, 
  REFERENCENUMBER             VARCHAR2(30) NOT NULL, 
  REMARK                      VARCHAR2(1000) NOT NULL, 
  CUSTOMER                    NUMBER NOT NULL, 
  DISTRIBUTIONPOINT           NUMBER NOT NULL, 
  CONTACTPERSON               NUMBER NOT NULL, 
  SHIPMENTTYPE                VARCHAR2(12) NOT NULL);

ALTER TABLE ERP_I_SALESORDER ADD CONSTRAINT PK_ERP_I_SALESORDER PRIMARY KEY (TRANSFER_ID);
ALTER TABLE ERP_I_SALESORDER ADD CONSTRAINT ERP_I_SALESORDER_TRANSFER_STATUS CHECK (TRANSFER_STATUS IN (-1,0,1));

  
CREATE TABLE ERP_I_SALESORDERLINE
( TRANSFER_ID                 NUMBER NOT NULL,
  TRANSFER_STATUS             NUMBER DEFAULT 0 NOT NULL,
  SALESORDERNUMBER            NUMBER NOT NULL, 
  PRODUCTCODE                 VARCHAR2(12) NOT NULL,
  SIZECODE                    VARCHAR2(12) NOT NULL, 
  FINISHINGMETHODCODE         VARCHAR2(12) NOT NULL, 
  QUANTITY                    NUMBER NOT NULL, 
  INSTRUCTIONS                VARCHAR2(300),
  SALESPRICE                  NUMBER);

ALTER TABLE ERP_I_SALESORDERLINE ADD CONSTRAINT PK_ERP_I_SALESORDERLINE PRIMARY KEY (TRANSFER_ID, SALESORDERNUMBER, PRODUCTCODE, SIZECODE, FINISHINGMETHODCODE);
ALTER TABLE ERP_I_SALESORDERLINE ADD CONSTRAINT FK_ERP_I_SALESORDERLINE FOREIGN KEY (TRANSFER_ID) REFERENCES ERP_I_SALESORDER (TRANSFER_ID) ON DELETE CASCADE;
ALTER TABLE ERP_I_SALESORDERLINE ADD CONSTRAINT ERP_I_SALESORDERLINE_TRANSFER_STATUS CHECK (TRANSFER_STATUS IN (-1,0,1));

-- LOG TABLE
CREATE TABLE ERP_I_SALESORDER_LOG
(
  TRANSFER_ID     NUMBER NOT NULL,
  TRANSFER_STATUS NUMBER DEFAULT 0 NOT NULL,
  TEXT            VARCHAR2(1000),
  PROCESS_STATUS  NUMBER DEFAULT 0 NOT NULL,
  TIMESTAMP       DATE DEFAULT SYSDATE NOT NULL);
-- ADD COMMENTS TO THE COLUMNS 
COMMENT ON COLUMN ERP_I_SALESORDER_LOG.TRANSFER_STATUS  IS '-1 = staging, 0 = ready to be read, 1 = read correctly';
COMMENT ON COLUMN ERP_I_SALESORDER_LOG.PROCESS_STATUS  IS '0 = PROCESSED CORRECTLY, 1 = PROCESS STOPPED WITH ERRORS';
-- CREATE/RECREATE PRIMARY, UNIQUE AND FOREIGN KEY CONSTRAINTS 
ALTER TABLE ERP_I_SALESORDER_LOG ADD CONSTRAINT PK_ERP_I_SALESORDER_LOG PRIMARY KEY (TRANSFER_ID);
-- CREATE/RECREATE CHECK CONSTRAINTS 
ALTER TABLE ERP_I_SALESORDER_LOG ADD CONSTRAINT ERP_I_SALESORDER_LOG_TRANSFER_STATUS CHECK (TRANSFER_STATUS IN (-1,0,1));
