-- ABS-76564

-- ADD/MODIFY COLUMNS 
ALTER TABLE ERP_I_CUSTOMER MODIFY NATIONALACCOUNTCODE VARCHAR2(15);
ALTER TABLE ERP_I_CUSTOMER ADD (FINANCECHARGEPUTON NUMBER DEFAULT 1 NOT NULL
                                CONSTRAINT CHK_ERP_I_CUST_FINANCEPUTON CHECK (FINANCECHARGEPUTON IN (1,2)),
                                FINANCECHARGEDAYS NUMBER DEFAULT 0 NOT NULL
                CONSTRAINT CHK_ERP_I_CUST_FINANCEDAYS CHECK (FINANCECHARGEDAYS BETWEEN 0 AND 999),
                  FULLPAYMENTONDELIVERY VARCHAR2(1) DEFAULT 'N' NOT NULL
                CONSTRAINT YN_VALIDATIONFIELD471 CHECK (FULLPAYMENTONDELIVERY IN ('Y','N')),
                  STATEMENTLAYOUTCODE VARCHAR2(6),
                                STATEMENTHANDLINGSTARTDATE DATE);

-- CREATE TABLE
CREATE TABLE ERP_I_DIALOGFLEXFIELDS
(
  TRANSFER_ID        NUMBER NOT NULL,
  TRANSFER_STATUS    NUMBER DEFAULT 0 NOT NULL,
  DIALOGCODE         VARCHAR2(40) NOT NULL  CONSTRAINT ERP_I_DLGFLEXF_DIALOG_VALID
                     CHECK (DIALOGCODE IN ('Customer')),
  CHARACTERISTICCODE VARCHAR2(12) NOT NULL,
  NUMBERVALUE        NUMBER NULL,
  STRINGVALUE        VARCHAR2(255) NULL,
  DATEVALUE          DATE NULL,
  ENTITY_NUMBER1     NUMBER NULL,
  ENTITY_NUMBER2     NUMBER NULL,
  ENTITY_NUMBER3     NUMBER NULL,
  ENTITY_CODE1       VARCHAR2(12) NULL,
  ENTITY_CODE2       VARCHAR2(12) NULL,
  ENTITY_CODE3       VARCHAR2(12) NULL,
  PREDEFINEDVALUE    VARCHAR2(1) DEFAULT 'N' NOT NULL CONSTRAINT YN_VALIDATIONFIELD470
                                 CHECK (PREDEFINEDVALUE IN ('Y','N')));

COMMENT ON COLUMN ERP_I_DIALOGFLEXFIELDS.DIALOGCODE
  IS 'Possible values: Customer';
COMMENT ON COLUMN ERP_I_DIALOGFLEXFIELDS.NUMBERVALUE
  IS 'If flex field is a number, fill it here';
COMMENT ON COLUMN ERP_I_DIALOGFLEXFIELDS.STRINGVALUE
  IS 'If flex field is a string, fill it here';
COMMENT ON COLUMN ERP_I_DIALOGFLEXFIELDS.DATEVALUE
  IS 'If flex field is a date, fill it here';
COMMENT ON COLUMN ERP_I_DIALOGFLEXFIELDS.ENTITY_NUMBER1
  IS 'For example for customer number, at least 1 entity number or code must be fille';
COMMENT ON COLUMN ERP_I_DIALOGFLEXFIELDS.ENTITY_NUMBER2
  IS 'For example department number';
COMMENT ON COLUMN ERP_I_DIALOGFLEXFIELDS.ENTITY_NUMBER3
  IS 'For example consumption point number';
COMMENT ON COLUMN ERP_I_DIALOGFLEXFIELDS.ENTITY_CODE1
  IS 'For example for product code, at least 1 entity number or code must be filled';
COMMENT ON COLUMN ERP_I_DIALOGFLEXFIELDS.ENTITY_CODE2
  IS 'For example size code';
COMMENT ON COLUMN ERP_I_DIALOGFLEXFIELDS.ENTITY_CODE3
  IS 'For example finishing method code';

ALTER TABLE ERP_I_DIALOGFLEXFIELDS
  ADD CONSTRAINT XPKERP_I_DIALOGFLEXFIELDS PRIMARY KEY (TRANSFER_ID);

CREATE INDEX XIE1ERP_I_DIALOGFLEXF_CHAR ON ERP_I_DIALOGFLEXFIELDS (CHARACTERISTICCODE ASC);

-- ERP_I_DIALOGFLEXFIELDS_LOG
CREATE TABLE ERP_I_DIALOGFLEXFIELDS_LOG
(
  TRANSFER_ID     NUMBER NOT NULL,
  TRANSFER_STATUS NUMBER DEFAULT 0 NOT NULL,
  TEXT            VARCHAR2(1000) NULL,
  PROCESS_STATUS  NUMBER DEFAULT 0 NOT NULL,
  TIMESTAMP       DATE DEFAULT SYSDATE NOT NULL
);
 
COMMENT ON COLUMN ERP_I_DIALOGFLEXFIELDS_LOG.TRANSFER_ID
  IS 'Transfer id from ERP_I_DIALOGFLEXFIELDS';
COMMENT ON COLUMN ERP_I_DIALOGFLEXFIELDS_LOG.TRANSFER_STATUS
  IS '0 = ready to be read, 1 = read correctly';
COMMENT ON COLUMN ERP_I_DIALOGFLEXFIELDS_LOG.PROCESS_STATUS
  IS '0 = processed correctly, 1 = process stopped with errors';
  
-- CREATE/RECREATE PRIMARY, UNIQUE AND FOREIGN KEY CONSTRAINTS 
ALTER TABLE ERP_I_DIALOGFLEXFIELDS_LOG ADD CONSTRAINT XPKERP_I_DIALOGFLEXFIELDS_LOG PRIMARY KEY (TRANSFER_ID);
ALTER TABLE ERP_I_DIALOGFLEXFIELDS_LOG ADD CONSTRAINT FK_ERP_I_DIALOGFLEXFIELDS FOREIGN KEY (TRANSFER_ID) REFERENCES ERP_I_DIALOGFLEXFIELDS (TRANSFER_ID) ON DELETE CASCADE;
                      
