--ABS-6708
-------------  CHARACTERISTICSTEMPLATE  -----------
ALTER TABLE CHARACTERISTICSTEMPLATE ADD (DIALOG_ID NUMBER NULL);

DROP INDEX XAK1CHARACTERISTICSTEMPLATE;
CREATE UNIQUE INDEX XAK1CHARACTERISTICSTEMPLATE ON CHARACTERISTICSTEMPLATE (CODE ASC, DIALOG_ID ASC);

CREATE INDEX XIF1CHARACTERISTICSTEMPLATE ON CHARACTERISTICSTEMPLATE(DIALOG_ID ASC);

ALTER TABLE CHARACTERISTICSTEMPLATE
  ADD CONSTRAINT CHARACTEMPLATE_DIALOG_ID FOREIGN KEY (DIALOG_ID)
  REFERENCES DIALOGS (DIALOG_ID);

COMMENT ON COLUMN CHARACTERISTICSTEMPLATE.CODE
  IS 'If Flex Field, this field is used to store the sequence# with leading 0';
COMMENT ON COLUMN CHARACTERISTICSTEMPLATE.DIALOG_ID
  IS 'Used for Flex Fields';
  

--------------  CHARACTERISTIC  ------------------
ALTER TABLE CHARACTERISTIC ADD (DEFDATEVALUE VARCHAR2(1) DEFAULT 'N' NULL CONSTRAINT YN_DEFDATEVALUE_VALID CHECK (DEFDATEVALUE IN ('Y', 'N')),
                                TOOLTIP    VARCHAR2(300) NULL);
								
ALTER TABLE CHARACTERISTIC
  ADD CONSTRAINT DATATYPE_VALID
  CHECK (DATATYPE IN (1,2,3,4,5)); 

COMMENT ON COLUMN CHARACTERISTIC.DATATYPE
  IS '1 = Number
2 = String
3 = Date
4 = Scientific number
5 = Predefined value';

COMMENT ON COLUMN CHARACTERISTIC.TOOLTIP
  IS 'Used for Flex Fields';
  
  
------------  CHARACTERISTICPREDEFVALUE  ---------
ALTER TABLE CHARACTERISTICPREDEFVALUE ADD (DEFAULTVALUE VARCHAR2(1) DEFAULT 'N' NOT NULL CONSTRAINT YN_DEFAULTVALUE_VALID CHECK (DEFAULTVALUE IN ('Y','N')));

COMMENT ON COLUMN CHARACTERISTICPREDEFVALUE.DEFAULTVALUE
  IS 'Used for Flex Fields';
  


-----------  DIALOGFLEXFIELDS  --------------
CREATE SEQUENCE SEQ_DIALOGFLEXFIELDS
START WITH 1
INCREMENT BY 1;    
    
CREATE TABLE DIALOGFLEXFIELDS
(
  DIALOGFLEXFIELD_ID           NUMBER NOT NULL,
  DIALOG_ID                    NUMBER NOT NULL,
  ENTITY_ID                    NUMBER NOT NULL,
  CHARACTERISTIC_ID            NUMBER NOT NULL,
  NUMBERVALUE                  NUMBER NULL,
  STRINGVALUE                  VARCHAR2(255) NULL,
  DATEVALUE                    DATE NULL,
  CHARACTERISTICPREDEFVALUE_ID NUMBER NULL,
  SYSTEMUSER_ID                NUMBER NOT NULL,
  TIMESTAMP                    DATE NOT NULL
);
ALTER TABLE DIALOGFLEXFIELDS
  ADD CONSTRAINT XPKDIALOGFLEXFIELDS PRIMARY KEY (DIALOGFLEXFIELD_ID);
  
CREATE UNIQUE INDEX XAK1DIALOGFLEXFIELDS ON DIALOGFLEXFIELDS (DIALOG_ID ASC, ENTITY_ID ASC, CHARACTERISTIC_ID ASC);

CREATE INDEX XIF1DIALOGFLEXFIELDS ON DIALOGFLEXFIELDS (DIALOG_ID ASC);

CREATE INDEX XIF2DIALOGFLEXFIELDS ON DIALOGFLEXFIELDS (CHARACTERISTIC_ID ASC);

CREATE INDEX XIF3DIALOGFLEXFIELDS ON DIALOGFLEXFIELDS (CHARACTERISTICPREDEFVALUE_ID ASC);

ALTER TABLE DIALOGFLEXFIELDS
  ADD CONSTRAINT DLGFLEXFIELDS_DIALOG_ID FOREIGN KEY (DIALOG_ID)
  REFERENCES DIALOGS (DIALOG_ID) ON DELETE CASCADE;
  
ALTER TABLE DIALOGFLEXFIELDS
  ADD CONSTRAINT DLGFLEXFIELDS_CHARAC_ID FOREIGN KEY (CHARACTERISTIC_ID)
  REFERENCES CHARACTERISTIC (CHARACTERISTIC_ID);
  
ALTER TABLE DIALOGFLEXFIELDS
  ADD CONSTRAINT DLGFLEXFIELDS_CHARPREDEFVAL_ID FOREIGN KEY (CHARACTERISTICPREDEFVALUE_ID)
  REFERENCES CHARACTERISTICPREDEFVALUE (CHARACTERISTICPREDEFVALUE_ID);
    
COMMENT ON COLUMN DIALOGFLEXFIELDS.ENTITY_ID
  IS 'This refers to the unique ID of the entity. If for example the Dialog_ID is the CustomerDlg, then this is the Customer_ID. Not a FOREIGN KEY';

--------------------- DIALOGS  ---------------
ALTER TABLE DIALOGS ADD (FLEXFIELDS VARCHAR2(1) DEFAULT 'N' NOT NULL CONSTRAINT YN_FLEXFIELDS_VALID CHECK (FLEXFIELDS IN ('Y','N')));

UPDATE DIALOGS D
   SET D.FLEXFIELDS = 'Y'
 WHERE D.CODE IN ('BusinessUnit',
                  'Customer',
                  'WeighingCategory',
                  'SpecialCharge',
                  'RepairType',
                  'Product',
                  'ProductGroup',
                  'ProductListPerCustomer');
COMMIT;
  
  
  
  
  
  
  
  
  
  
  