-- ABS-28871

---ERP_I_SUPPLIERMODEL table---
CREATE TABLE ERP_I_SUPPLIERMODEL
(
  TRANSFER_ID                    NUMBER NOT NULL,
  TRANSFER_STATUS                NUMBER DEFAULT 0 NOT NULL,
  SUPPLIERNUMBER                 NUMBER NOT NULL,
  SUPPLIERMODEL_CODE             VARCHAR2(40) NOT NULL,
  SUPPLIERMODEL_DESCRIPTION      VARCHAR2(50) NOT NULL,
  SUPPLIERMODEL_REMARK           VARCHAR2(1000) NULL,
  SUPPLIERMODEL_DATEACTIVE       DATE DEFAULT TRUNC(SYSDATE) NOT NULL,
  SUPPLIERMODEL_DATEDATEINACTIVE DATE DEFAULT TO_DATE('31-12-2099','DD-MM-YYYY') NOT NULL,
  AGGSUPPPODUCT_CODE             VARCHAR2(40) NULL,
  AGGSUPPRODUCT_DESC             VARCHAR2(50) NULL,
  AGGSUPPRODUCT_REMARK           VARCHAR2(1000) NULL,
  AGGSUPPRODUCT_DATEACTIVE       DATE DEFAULT TRUNC(SYSDATE) NULL,
  AGGSUPPRODUCT_DATEINACTIVE     DATE DEFAULT TO_DATE('31-12-2099','DD-MM-YYYY') NULL,
  AGGSUPPRODUCT_ORDERTERMSLEVEL  NUMBER DEFAULT 2 NULL CONSTRAINT ERP_I_SUPMODEL_ORDERTERMS
                                 CHECK (AGGSUPPRODUCT_ORDERTERMSLEVEL IN (1, 2)),
  AGGSUPPRODUCT_PRICETERMSLEVEL  NUMBER DEFAULT 2 NULL CONSTRAINT ERP_I_SUPMODEL_PRICETERMS
                                 CHECK (AGGSUPPRODUCT_PRICETERMSLEVEL IN (1, 2)),
  AGGSUPPRODUCT_SIZETYPECODE     VARCHAR2(6) DEFAULT '-' NULL,
  PREWASH                        NUMBER DEFAULT 0 NULL CONSTRAINT PREWASH_VALID1_4
                                 CHECK (PREWASH IN (0, 1)),
  CODEUNIQUELY                   NUMBER DEFAULT 0 NULL CONSTRAINT CODEUNIQUELY_VALID1_1
                                 CHECK (CODEUNIQUELY IN (0, 1)),
  ALLOWSUPPLIERMODIFICATIONS     NUMBER DEFAULT 0 NULL,
  AGGSUPPRODSIZE_SIZECODE        VARCHAR2(40) NULL,
  AGGSUPPRODSIZE_SEQUENCENUMBER  NUMBER DEFAULT 0 NULL,
  AGGSUPPRODSIZE_REMARK          VARCHAR2(1000) NULL,
  AGGSUPPRODSIZE_DATEACTIVE      DATE DEFAULT TRUNC(SYSDATE) NULL,
  AGGSUPPRODSIZE_DATEINACTIVE    DATE DEFAULT TO_DATE('31-12-2099','DD-MM-YYYY') NULL,
  AGGSUPPRODSIZE_ORDERTERMSLEVEL NUMBER DEFAULT 3 NULL CONSTRAINT ERP_I_SUPMODEL_ORDERTERMS2
                                 CHECK (AGGSUPPRODSIZE_ORDERTERMSLEVEL IN (1, 2, 3)),
  AGGSUPPRODSIZE_PRICETERMSLEVEL NUMBER DEFAULT 3 NULL CONSTRAINT ERP_I_SUPMODEL_PRICETERMS2
                                 CHECK (AGGSUPPRODSIZE_PRICETERMSLEVEL IN (1, 2, 3)),
  LAUNDRYPRODUCTCODE             VARCHAR2(12) NULL,
  LAUNDRYSIZECODE                VARCHAR2(12) NULL
);

COMMENT ON COLUMN ERP_I_SUPPLIERMODEL.AGGSUPPRODUCT_ORDERTERMSLEVEL
  IS '1 = Supplier Model 2 = Supplier product';
COMMENT ON COLUMN ERP_I_SUPPLIERMODEL.AGGSUPPRODUCT_PRICETERMSLEVEL
  IS '1 = Supplier Model 2 = Supplier product';
COMMENT ON COLUMN ERP_I_SUPPLIERMODEL.PREWASH
  IS '0 = Not applicable / No prewash 1 = Prewash';
COMMENT ON COLUMN ERP_I_SUPPLIERMODEL.CODEUNIQUELY
  IS '0 = Not applicable / Not coded uniquely 1 = Code uniquely';
COMMENT ON COLUMN ERP_I_SUPPLIERMODEL.AGGSUPPRODSIZE_ORDERTERMSLEVEL
  IS '1 = Supplier model 2 = Supplier product 3 = Supplier product size';
COMMENT ON COLUMN ERP_I_SUPPLIERMODEL.AGGSUPPRODSIZE_PRICETERMSLEVEL
  IS '1 = Supplier model 2 = Supplier product 3 = Supplier product size';
  
ALTER TABLE ERP_I_SUPPLIERMODEL
  ADD CONSTRAINT PK_ERP_I_SUPPLIERMODEL PRIMARY KEY (TRANSFER_ID);

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

COMMENT ON COLUMN ERP_I_SUPPLIERMODEL_LOG.TRANSFER_STATUS
  IS '0 = ready to be read, 1 = read correctly';
COMMENT ON COLUMN ERP_I_SUPPLIERMODEL_LOG.PROCESS_STATUS
  IS '0 = processed correctly, 1 = process stopped with errors';
  
ALTER TABLE ERP_I_SUPPLIERMODEL_LOG
  ADD CONSTRAINT PK_ERP_I_SUPPLIERMODEL_LOG PRIMARY KEY (TRANSFER_ID);
  
ALTER TABLE ERP_I_SUPPLIERMODEL_LOG
  ADD CONSTRAINT FK_ERP_I_SUPPLIERMODEL_LOG FOREIGN KEY (TRANSFER_ID)
  REFERENCES ERP_I_SUPPLIERMODEL(TRANSFER_ID) ON DELETE CASCADE;

---ERP_I_SUPPLIERMODELPRICE table---
CREATE TABLE ERP_I_SUPPLIERMODELPRICE
(
  TRANSFER_ID             NUMBER NOT NULL,
  TRANSFER_STATUS         NUMBER DEFAULT 0 NOT NULL,
  SUPPLIERNUMBER          NUMBER NOT NULL,
  SUPPLIERMODEL_CODE      VARCHAR2(40) NULL,
  AGGSUPPPODUCT_CODE      VARCHAR2(40) NULL,
  AGGSUPPRODSIZE_SIZECODE VARCHAR2(40) NULL,
  PRICETERMSLEVEL         NUMBER DEFAULT 2 NOT NULL CONSTRAINT ERP_I_SUPMODELPRICE_PRICETERMS
                          CHECK (PRICETERMSLEVEL IN (1, 2, 3)),
  STARTDATE               DATE DEFAULT TRUNC(SYSDATE) NOT NULL,
  ORDERQUANTITY           NUMBER DEFAULT 2 NOT NULL,
  DELIVERYDAYS            NUMBER DEFAULT 1 NOT NULL,
  PREWASH                 NUMBER DEFAULT 0 NOT NULL CONSTRAINT PREWASH_VALID1_5
                          CHECK (PREWASH IN (0, 1)),
  CODEUNIQUELY            NUMBER DEFAULT 0 NOT NULL CONSTRAINT CODEUNIQUELY_VALID1_2
                          CHECK (CODEUNIQUELY IN (0, 1)),
  PURCHASEPRICE           NUMBER(15,6) NOT NULL
);

COMMENT ON COLUMN ERP_I_SUPPLIERMODELPRICE.PRICETERMSLEVEL
  IS '1 = Supplier model 2 = Supplier product 3 = Supplier product size';
COMMENT ON COLUMN ERP_I_SUPPLIERMODELPRICE.PREWASH
  IS '0 = Not applicable / No prewash 1 = Prewash';
COMMENT ON COLUMN ERP_I_SUPPLIERMODELPRICE.CODEUNIQUELY
  IS '0 = Not applicable / Not coded uniquely 1 = Code uniquely';
  
ALTER TABLE ERP_I_SUPPLIERMODELPRICE
  ADD CONSTRAINT PK_ERP_I_SUPPLIERMODELPRICE PRIMARY KEY (TRANSFER_ID);
  
---ERP_I_SUPPLIERMODELPRICE_LOG table---
CREATE TABLE ERP_I_SUPPLIERMODELPRICE_LOG
(
  TRANSFER_ID     NUMBER NOT NULL,
  TRANSFER_STATUS NUMBER DEFAULT 0 NOT NULL CONSTRAINT ERP_I_SUPMPRICE_TRANSFR_STATUS
                  CHECK (TRANSFER_STATUS IN (0, 1)),
  TEXT            VARCHAR2(1000) NULL,
  PROCESS_STATUS  NUMBER DEFAULT 0 NOT NULL CONSTRAINT ERP_I_SUPMPRICE_PROCESS_STATUS
                  CHECK (PROCESS_STATUS IN (0, 1)),
  TIMESTAMP       DATE DEFAULT SYSDATE NOT NULL
);

COMMENT ON COLUMN ERP_I_SUPPLIERMODELPRICE_LOG.TRANSFER_STATUS
  IS '0 = ready to be read, 1 = read correctly';
COMMENT ON COLUMN ERP_I_SUPPLIERMODELPRICE_LOG.PROCESS_STATUS
  IS '0 = processed correctly, 1 = process stopped with errors';

ALTER TABLE ERP_I_SUPPLIERMODELPRICE_LOG
  ADD CONSTRAINT PK_ERP_I_SUPPLMODELPRICE_LOG PRIMARY KEY (TRANSFER_ID);
  
ALTER TABLE ERP_I_SUPPLIERMODELPRICE_LOG
  ADD CONSTRAINT FK_ERP_I_SUPPLMODELPRICE_LOG FOREIGN KEY (TRANSFER_ID)
  REFERENCES ERP_I_SUPPLIERMODELPRICE(TRANSFER_ID) ON DELETE CASCADE;

---ERP_I_SUPPLIERMODELORDER table---
CREATE TABLE ERP_I_SUPPLIERMODELORDER
(
  TRANSFER_ID               NUMBER NOT NULL,
  TRANSFER_STATUS           NUMBER DEFAULT 0 NOT NULL,
  SUPPLIERNUMBER            NUMBER NOT NULL,
  SUPPLIERMODEL_CODE        VARCHAR2(40),
  AGGSUPPPODUCT_CODE        VARCHAR2(40),
  AGGSUPPRODSIZE_SIZECODE   VARCHAR2(40),
  ORDERTERMSLEVEL           NUMBER DEFAULT 2 NOT NULL CONSTRAINT ERP_I_SUPMODELORDER_ORDERTERMS
                            CHECK (ORDERTERMSLEVEL IN (1, 2, 3)),
  STARTDATE                 DATE DEFAULT TRUNC(SYSDATE) NOT NULL,
  DELIVERYDAYS              NUMBER DEFAULT 1 NOT NULL,
  MINORDERQUANTITY          NUMBER DEFAULT 1 NOT NULL,
  PACKAGEQUANTITY           NUMBER DEFAULT 1 NOT NULL,
  TRANSPORTCHARGEPERCENTAGE NUMBER DEFAULT 0 NOT NULL
);

COMMENT ON COLUMN ERP_I_SUPPLIERMODELORDER.ORDERTERMSLEVEL
  IS '1 = Supplier model 2 = Supplier product 3 = Supplier product size';
  
ALTER TABLE ERP_I_SUPPLIERMODELORDER
  ADD CONSTRAINT PK_ERP_I_SUPPLIERMODELORDER PRIMARY KEY (TRANSFER_ID);
  
---ERP_I_SUPPLIERMODELORDER_LOG table---
CREATE TABLE ERP_I_SUPPLIERMODELORDER_LOG
(
  TRANSFER_ID     NUMBER NOT NULL,
  TRANSFER_STATUS NUMBER DEFAULT 0 NOT NULL CONSTRAINT ERP_I_SUPMORDER_TRANSFR_STATUS
                  CHECK (TRANSFER_STATUS IN (0, 1)),
  TEXT            VARCHAR2(1000),
  PROCESS_STATUS  NUMBER DEFAULT 0 NOT NULL CONSTRAINT ERP_I_SUPMORDER_PROCESS_STATUS
                  CHECK (PROCESS_STATUS IN (0, 1)),
  TIMESTAMP       DATE DEFAULT SYSDATE NOT NULL
);

COMMENT ON COLUMN ERP_I_SUPPLIERMODELORDER_LOG.TRANSFER_STATUS
  IS '0 = ready to be read, 1 = read correctly';
COMMENT ON COLUMN ERP_I_SUPPLIERMODELORDER_LOG.PROCESS_STATUS
  IS '0 = processed correctly, 1 = process stopped with errors';

ALTER TABLE ERP_I_SUPPLIERMODELORDER_LOG
  ADD CONSTRAINT PK_ERP_I_SUPPLMODELORDER_LOG PRIMARY KEY (TRANSFER_ID);
  
ALTER TABLE ERP_I_SUPPLIERMODELORDER_LOG
  ADD CONSTRAINT FK_ERP_I_SUPPLMODELORDER_LOG FOREIGN KEY (TRANSFER_ID)
  REFERENCES ERP_I_SUPPLIERMODELORDER(TRANSFER_ID) ON DELETE CASCADE;

---insert into ERP_TRANSACTION_LINK table---
INSERT INTO ERP_TRANSACTION_LINK (ERP_TABLENAME, ERP_PROCEDURECALL)
VALUES ('ERP_I_SUPPLIERMODELORDER_LOG', 'ERP_SUPPLIERMODEL.PROC_I_SUPPMODELORDER');
commit;

INSERT INTO ERP_TRANSACTION_LINK (ERP_TABLENAME, ERP_PROCEDURECALL)
VALUES ('ERP_I_SUPPLIERMODELPRICE_LOG', 'ERP_SUPPLIERMODEL.PROC_I_SUPPMODELPRICE');
commit;

INSERT INTO ERP_TRANSACTION_LINK (ERP_TABLENAME, ERP_PROCEDURECALL)
VALUES ('ERP_I_SUPPLIERMODEL_LOG', 'ERP_SUPPLIERMODEL.PROC_I_SUPPMODEL');
commit;
