-- SO 20011734.70 Translation of predefined screens for user instructions

-------------------------------DIALOGS------------------------------
ALTER TABLE DIALOGS
	DROP CONSTRAINT DIALOGTYPE_VALID;

ALTER TABLE DIALOGS
	ADD CONSTRAINT DIALOGTYPE_VALID  CHECK (DIALOGTYPE IN (0, 1, 2, 3, 4, 5));

COMMENT ON COLUMN DIALOGS.DIALOGTYPE IS '0 = Simple dialog (Default)
1 = Dialog with reports
2 = Report with Selection dialog
3 = Web dialog
4 = Web report
5 = Virtual dialog';

ALTER TABLE DIALOGS
	ADD (DEFINEUSERINSTRUCTIONS NUMBER  DEFAULT 0 NOT NULL CONSTRAINT DEFINEUSERINSTRUCTIO_ CHECK (DEFINEUSERINSTRUCTIONS IN (0, 1)),
	     SPDESCRIPTION VARCHAR2(40) DEFAULT '-' NOT NULL);

COMMENT ON COLUMN DIALOGS.DEFINEUSERINSTRUCTIONS IS '0 = Do not allow to define user instructions
1 = Allow to define user instructions
';

COMMENT ON COLUMN DIALOGS.SPDESCRIPTION IS 'Detailed description of the dialog in Spanish';

-------------------------------DIALOGS_DESC---------------------------

CREATE TABLE DIALOGS_DESC
(
	DIALOG_ID            NUMBER NOT NULL ,
	LANGUAGE_ID          NUMBER NOT NULL ,
	DESCRIPTION          VARCHAR2(40) NOT NULL ,
	SYSTEMUSER_ID        NUMBER NOT NULL ,
	TIMESTAMP            DATE NOT NULL 
);

ALTER TABLE DIALOGS_DESC
ADD CONSTRAINT  XPKDIALOG_DESC PRIMARY KEY (DIALOG_ID,LANGUAGE_ID);

CREATE  INDEX XIF1DIALOG_DESC ON DIALOGS_DESC
(DIALOG_ID   ASC);

ALTER TABLE DIALOGS_DESC
ADD CONSTRAINT DIALOGS_DESC_DIALOGS_ID FOREIGN KEY (DIALOG_ID) REFERENCES DIALOGS (DIALOG_ID) ON DELETE CASCADE;

CREATE  INDEX XIF2DIALOG_DESC ON DIALOGS_DESC
(LANGUAGE_ID   ASC);

ALTER TABLE DIALOGS_DESC
ADD CONSTRAINT DIALOGS_DESC_LANGUAGES FOREIGN KEY (LANGUAGE_ID) REFERENCES LANGUAGES (LANGUAGE_ID);

INSERT INTO dialogs
  (dialog_id,
   nldescription,
   code,
   endescription,
   gedescription,
   systemuser_id,
   active,
   timestamp,
   dialogonly,
   gridonly,
   confprintpreview,
   containerscan,
   orpackscan,
   garmentscan,
   residentialitemscan,
   frdescription,
   dkdescription,
   dialogtype,
   reportservercluster_id,
   defineuserinstructions,
   spdescription)
  SELECT SEQ_DIALOGS.NEXTVAL,
         '-',
         p.code,
         '-',
         '-',
         1,
         'Y',
         SYSDATE,
         'Y',
         'Y',
         'Y',
         'Y',
         'Y',
         'Y',
         'Y',
         '-',
         '-',
         1,
         NULL,
         1,
         '-'
    FROM predefinedscreen p
    LEFT OUTER JOIN dialogs d
      ON p.code = d.code
   WHERE d.dialog_id IS NULL;

COMMIT;


-------------------------------USERINSTRUCTIONPERSCREEN------------------------------

ALTER TABLE USERINSTRUCTIONPERSCREEN
	ADD (DIALOG_ID NUMBER  NULL);

ALTER TABLE USERINSTRUCTIONPERSCREEN
	MODIFY (PREDEFINEDSCREEN_ID NUMBER NULL);

ALTER TABLE USERINSTRUCTIONPERSCREEN
ADD CONSTRAINT USERINSTRUCTIONPERSCREEN_DIAL FOREIGN KEY (DIALOG_ID) REFERENCES DIALOGS (DIALOG_ID) ON DELETE CASCADE;

CREATE  INDEX XIF5USERINSTRUCTIONPERSCREEN ON USERINSTRUCTIONPERSCREEN
(DIALOG_ID   ASC);

ALTER TABLE USERINSTRUCTIONPERSCREEN
DROP CONSTRAINT USERINSTRUCTIONPERSCREEN_PREDE;

ALTER TABLE USERINSTRUCTIONPERSCREEN
ADD CONSTRAINT USERINSTRUCTIONPERSCREEN_PREDE FOREIGN KEY (PREDEFINEDSCREEN_ID) REFERENCES PREDEFINEDSCREEN (PREDEFINEDSCREEN_ID) ON DELETE SET NULL; 

DROP INDEX XAK1USERINSTRUCTIONPERSCREEN;


UPDATE USERINSTRUCTIONPERSCREEN UIS
SET UIS.DIALOG_ID =
       (SELECT D.DIALOG_ID
          FROM PREDEFINEDSCREEN PDS, DIALOGS D
         WHERE UPPER(D.CODE) = UPPER(PDS.CODE)
           AND PDS.PREDEFINEDSCREEN_ID = UIS.PREDEFINEDSCREEN_ID);

COMMIT;

CREATE UNIQUE INDEX XAK1USERINSTRUCTIONPERSCREEN ON USERINSTRUCTIONPERSCREEN 
(DIALOG_ID ASC, GENERALUSERINSTRUCTION_ID   ASC, CUSTOMERUSERINSTRUCTION_ID  ASC);

ALTER TABLE USERINSTRUCTIONPERSCREEN
	MODIFY (DIALOG_ID NUMBER NOT NULL);





