--ABS-103677 / ABS-119678
-- WORKSTATION --
ALTER TABLE WORKSTATION ADD (PREFERENCEOPENLASTUSED_TEMP VARCHAR2(1) DEFAULT '0' NOT NULL);
CREATE OR REPLACE PROCEDURE UPDATE_WORKSTATION AS
COLUMNTYPE USER_TAB_COLUMNS.DATA_TYPE%TYPE;
BEGIN
  SELECT UTCC.DATA_TYPE INTO COLUMNTYPE
  FROM USER_TAB_COLUMNS UTCC
  WHERE UTCC.TABLE_NAME = 'WORKSTATION'
  AND UTCC.COLUMN_NAME = 'PREFERENCEOPENLASTUSED';
  CASE WHEN COLUMNTYPE = 'VARCHAR2'
    THEN
      UPDATE WORKSTATION WS
      SET WS.PREFERENCEOPENLASTUSED_TEMP = '3'
      WHERE WS.PREFERENCEOPENLASTUSED = 'Y';
      COMMIT;
  WHEN COLUMNTYPE = 'NUMBER'
    THEN
      UPDATE WORKSTATION WS
      SET WS.PREFERENCEOPENLASTUSED_TEMP = TO_CHAR(PREFERENCEOPENLASTUSED);
      COMMIT;
  END CASE;
EXCEPTION
  WHEN OTHERS THEN
    NULL;
END UPDATE_WORKSTATION;
/
EXECUTE UPDATE_WORKSTATION;
DROP PROCEDURE UPDATE_WORKSTATION;

ALTER TABLE WORKSTATION DROP COLUMN PREFERENCEOPENLASTUSED;
ALTER TABLE WORKSTATION ADD (PREFERENCEOPENLASTUSED NUMBER DEFAULT 0 NOT NULL CONSTRAINT CHK_WORKSTATION_PREFOPENLAST CHECK (PREFERENCEOPENLASTUSED IN (0,1,2,3)));
UPDATE WORKSTATION WS
SET WS.PREFERENCEOPENLASTUSED = TO_NUMBER(WS.PREFERENCEOPENLASTUSED_TEMP);
COMMIT;

ALTER TABLE WORKSTATION DROP COLUMN PREFERENCEOPENLASTUSED_TEMP;
COMMENT ON COLUMN WORKSTATION.PREFERENCEOPENLASTUSED IS
'0 = Do not reopen dialogs 
1 = Reopen dialogs upon start 
2 = Reopen dialogs when switching BU 
3 = Reopen dialogs upon start or switching BU
';