--ABSN-566
CREATE TABLE ERP_I_CUSTOMERWEIGHINGCATEGORY
( TRANSFER_ID           NUMBER NOT NULL,
  TRANSFER_STATUS       NUMBER(1) DEFAULT 0 NOT NULL,
  CUSTOMERNUMBER        NUMBER NOT NULL,
  WEIGHINGCATEGORYCODE  VARCHAR2(6) NOT NULL,
  DESCRIPTION           VARCHAR2(40),
  WEIGHTSOILED          VARCHAR2(1),
  WEIGHTCLEAN           VARCHAR2(1),
  DATEACTIVE            DATE,
  DATEINACTIVE          DATE DEFAULT TO_DATE('31-12-2099','dd-mm-yyyy'),
  INVOICEMETHOD         NUMBER(1),
  SOILPERCENTAGE        NUMBER(5,2),
  INVOICEGROUPINGCODE   VARCHAR2(12),
  EANNUMBER             NUMBER,
  REMARK                VARCHAR2(60),
  REFERENCEWEIGHING     VARCHAR2(1),
  BUDGETUSAGE           NUMBER(1),
  REFERENCEWEIGHINGSOIL VARCHAR2(1),
  USEDFORCURTAINS       NUMBER(1),
  TIMESTAMP             DATE DEFAULT SYSDATE,
  POST_YN               VARCHAR2(1)
);
COMMENT ON COLUMN ERP_I_CUSTOMERWEIGHINGCATEGORY.TRANSFER_STATUS IS '0=ready to be read,
1=read correctly';
COMMENT ON COLUMN ERP_I_CUSTOMERWEIGHINGCATEGORY.WEIGHTSOILED IS 'Record soil weight';
COMMENT ON COLUMN ERP_I_CUSTOMERWEIGHINGCATEGORY.WEIGHTCLEAN IS 'Record clean weight';
COMMENT ON COLUMN ERP_I_CUSTOMERWEIGHINGCATEGORY.DATEACTIVE IS 'Default value todays date';
COMMENT ON COLUMN ERP_I_CUSTOMERWEIGHINGCATEGORY.DATEINACTIVE IS 'Default value 31-12-2099';
COMMENT ON COLUMN ERP_I_CUSTOMERWEIGHINGCATEGORY.INVOICEMETHOD IS '1=No invoicing weights,
2=Invoice soiled weights,
3=Invoice clean weights,
4=Invoice soil via transfer to clean';
COMMENT ON COLUMN ERP_I_CUSTOMERWEIGHINGCATEGORY.REFERENCEWEIGHING IS '0=Not in Budget,
1=Self paid, not in Budget,
2=In Budget';
COMMENT ON COLUMN ERP_I_CUSTOMERWEIGHINGCATEGORY.USEDFORCURTAINS IS '0=Not used for curtains or not applicable,
1=Used for Curtains';
ALTER TABLE ERP_I_CUSTOMERWEIGHINGCATEGORY ADD CONSTRAINT PK_ERP_WEIGHCATPERCUS PRIMARY KEY (TRANSFER_ID);
CREATE INDEX INDX1_ERP_WEIGHCATPERCUS ON ERP_I_CUSTOMERWEIGHINGCATEGORY(CUSTOMERNUMBER, WEIGHINGCATEGORYCODE);
ALTER TABLE ERP_I_CUSTOMERWEIGHINGCATEGORY ADD CONSTRAINT CHK_ERP_WEIGHCATPERCUST_BUDGETUSAGE CHECK (BUDGETUSAGE BETWEEN 0 AND 2);
ALTER TABLE ERP_I_CUSTOMERWEIGHINGCATEGORY ADD CONSTRAINT CHK_ERP_WEIGHCATPERCUST_CURTAINS CHECK (USEDFORCURTAINS IN (0,1));
ALTER TABLE ERP_I_CUSTOMERWEIGHINGCATEGORY ADD CONSTRAINT CHK_ERP_WEIGHCATPERCUST_DATECHECK CHECK (DATEACTIVE <DATEINACTIVE);
ALTER TABLE ERP_I_CUSTOMERWEIGHINGCATEGORY ADD CONSTRAINT CHK_ERP_WEIGHCATPERCUST_INVOICEMETH CHECK (INVOICEMETHOD IN (1, 2, 3, 4, 0));
ALTER TABLE ERP_I_CUSTOMERWEIGHINGCATEGORY ADD CONSTRAINT CHK_ERP_WEIGHCATPERCUST_REFWEIGHING CHECK (REFERENCEWEIGHING IN ('Y', 'N'));
ALTER TABLE ERP_I_CUSTOMERWEIGHINGCATEGORY ADD CONSTRAINT CHK_ERP_WEIGHCATPERCUST_REFWEIGHSOIL CHECK (REFERENCEWEIGHINGSOIL IN ('Y','N'));
ALTER TABLE ERP_I_CUSTOMERWEIGHINGCATEGORY ADD CONSTRAINT CHK_ERP_WEIGHCATPERCUST_TRANSFER_STATUS CHECK (TRANSFER_STATUS IN (-1,0,1));
ALTER TABLE ERP_I_CUSTOMERWEIGHINGCATEGORY ADD CONSTRAINT CHK_ERP_WEIGHCATPERCUST_WEIGCATCODE CHECK (WEIGHINGCATEGORYCODE = UPPER(TRIM(WEIGHINGCATEGORYCODE)));
ALTER TABLE ERP_I_CUSTOMERWEIGHINGCATEGORY ADD CONSTRAINT CHK_ERP_WEIGHCATPERCUST_WEIGHTCLEAN CHECK (WEIGHTCLEAN IN ('Y', 'N'));
ALTER TABLE ERP_I_CUSTOMERWEIGHINGCATEGORY ADD CONSTRAINT CHK_ERP_WEIGHCATPERCUST_WEIGHTSOILED CHECK (WEIGHTSOILED IN ('Y', 'N'));

CREATE TABLE ERP_I_CUSTOMERWEIGHINGCATEGORY_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
);
COMMENT ON COLUMN ERP_I_CUSTOMERWEIGHINGCATEGORY_LOG.TRANSFER_STATUS IS '0=ready to be read,
1=read correctly';
COMMENT ON COLUMN ERP_I_CUSTOMERWEIGHINGCATEGORY_LOG.PROCESS_STATUS IS '0=processed correctly, 
1=process stopped with errors';
ALTER TABLE ERP_I_CUSTOMERWEIGHINGCATEGORY_LOG ADD CONSTRAINT PK_ERP_I_CUSTOMERWEIGHINGCATEGORY_LOG PRIMARY KEY (TRANSFER_ID);
ALTER TABLE ERP_I_CUSTOMERWEIGHINGCATEGORY_LOG ADD CONSTRAINT FK_ERP_I_CUSTOMERWEIGHINGCATEGORY_LOG FOREIGN KEY (TRANSFER_ID)
  REFERENCES ERP_I_CUSTOMERWEIGHINGCATEGORY (TRANSFER_ID) ON DELETE CASCADE;
ALTER TABLE ERP_I_CUSTOMERWEIGHINGCATEGORY_LOG ADD CONSTRAINT CHK_ERP_I_CUSTOMERWEIGHINGCATEGORY_LOG_V1 CHECK (TRANSFER_STATUS IN (-1,0,1));
