
---ERP_I_SUPPLIERPRODUCT table---
CREATE TABLE ERP_I_SUPPLIERPRODUCT
(
  TRANSFER_ID                NUMBER NOT NULL,
  TRANSFER_STATUS            NUMBER DEFAULT 0 NOT NULL,
  SUPPLIERNUMBER             NUMBER NOT NULL,
  SUPPLIERPRODUCTCODE        VARCHAR2(30) NOT NULL,
  SUPPLIERPRODUCTDESCRIPTION VARCHAR2(50) NULL,
  SUPPLIERSIZECODE           VARCHAR2(20) NULL,
  LAUNDRYPRODUCTCODE         VARCHAR2(12) NOT NULL,
  LAUNDRYSIZECODE            VARCHAR2(12) NULL,
  PURCHASEPRICE_STARTDATE    DATE NULL,
  PURCHASEPRICE              NUMBER NULL,
  DELIVERYDAYS               NUMBER DEFAULT 1 NOT NULL CONSTRAINT CHK_ERP_I_SUPPLIERPROD_DELDAYS
                             CHECK (DELIVERYDAYS > 0),
  MINORDERQTY                NUMBER DEFAULT 1 NOT NULL,
  BOXQUANTITY                NUMBER DEFAULT 1 NOT NULL CONSTRAINT CHK_ERP_I_SUPPROD_BOXQUAUNTITY
                             CHECK (BOXQUANTITY > 0),
  UNIQUEITEM                 VARCHAR2(1) DEFAULT 'N' NOT NULL CONSTRAINT CHK_ERP_I_SUPPLIERPROD_UNIQUE
                             CHECK (UNIQUEITEM  IN ('Y','N')),
  REMARK                     VARCHAR2(300) NULL,
  ACTIVE                     VARCHAR2(1) DEFAULT 'Y' NOT NULL CONSTRAINT CHK_ERP_I_SUPPLIERPROD_ACTIVE
                             CHECK (ACTIVE IN ('Y','N'))
);
 
COMMENT ON COLUMN ERP_I_SUPPLIERPRODUCT.DELIVERYDAYS
  IS 'Default 1, delivery term';
COMMENT ON COLUMN ERP_I_SUPPLIERPRODUCT.MINORDERQTY
  IS 'Default 1, must be > 0 and can be divided by box quantity';
COMMENT ON COLUMN ERP_I_SUPPLIERPRODUCT.BOXQUANTITY
  IS 'Default 1, must be > 0';
 
ALTER TABLE ERP_I_SUPPLIERPRODUCT
  ADD CONSTRAINT PK_ERP_I_SUPPLIERPRODUCT PRIMARY KEY (TRANSFER_ID);
  
ALTER TABLE ERP_I_SUPPLIERPRODUCT
  ADD CONSTRAINT FK_ERP_I_SUPPLIERPRODUCT_PROD FOREIGN KEY (LAUNDRYPRODUCTCODE)
  REFERENCES PRODUCT (CODE);
  
ALTER TABLE ERP_I_SUPPLIERPRODUCT
  ADD CONSTRAINT FK_ERP_I_SUPPLIERPRODUCT_SIZE FOREIGN KEY (LAUNDRYSIZECODE)
  REFERENCES SIZEDEFINITION (CODE);

---ERP_I_SUPPLIERPRODUCT_LOG table---
CREATE TABLE ERP_I_SUPPLIERPRODUCT_LOG
(
  TRANSFER_ID     NUMBER NOT NULL,
  TRANSFER_STATUS NUMBER DEFAULT 0 NOT NULL CONSTRAINT CHK_ERP_I_SUPPROD_TRANSFERST
                  CHECK (TRANSFER_STATUS IN (0, 1)),
  TEXT            VARCHAR2(3000) NULL,
  PROCESS_STATUS  NUMBER DEFAULT 0 NOT NULL CONSTRAINT CHK_ERP_I_SUPPROD_PROCESSST
                  CHECK (PROCESS_STATUS IN (0, 1)),
  TIMESTAMP       DATE DEFAULT SYSDATE NOT NULL
);

COMMENT ON COLUMN ERP_I_SUPPLIERPRODUCT.TRANSFER_STATUS
  IS '0 = ready to be read, 1 = read correctly';

COMMENT ON COLUMN ERP_I_SUPPLIERPRODUCT.TRANSFER_STATUS
  IS '0 = processed correctly, 1 = process stopped with errors';

ALTER TABLE ERP_I_SUPPLIERPRODUCT_LOG
  ADD CONSTRAINT PK_ERP_I_SUPPLIERPRODUCT_LOG PRIMARY KEY (TRANSFER_ID);
  
ALTER TABLE ERP_I_SUPPLIERPRODUCT_LOG
  ADD CONSTRAINT FK_ERP_I_SUPPLIERPRODUCT_LOG FOREIGN KEY (TRANSFER_ID)
  REFERENCES ERP_I_SUPPLIERPRODUCT (TRANSFER_ID) ON DELETE CASCADE;