-- Copyright (C) 2001,2002 topIT Informationstechnologie GmbH
-- Copyright (C) 2003-2014 independIT Integrative Technologies GmbH

CREATE TABLE GRANTS (
    ID                             decimal(20) NOT NULL
    , OBJECT_ID                      decimal(20)     NOT NULL
    , G_ID                           decimal(20)     NOT NULL
    , OBJECT_TYPE                    integer         NOT NULL
    , PRIVS                          decimal(20)     NOT NULL
    , DELETE_VERSION                 decimal(20)     WITH NULL
    , CREATOR_U_ID                   decimal(20)     NOT NULL
    , CREATE_TS                      decimal(20)     NOT NULL
    , CHANGER_U_ID                   decimal(20)     NOT NULL
    , CHANGE_TS                      decimal(20)     NOT NULL
);\g
CREATE UNIQUE INDEX PK_GRANTS
ON GRANTS(ID) WITH STRUCTURE = BTREE;\g
CREATE VIEW SCI_GRANTS AS
SELECT 
    ID 
    , OBJECT_ID                      AS OBJECT_ID
    , G_ID                           AS G_ID
    , CASE OBJECT_TYPE WHEN 1 THEN 'ENVIRONMENT' WHEN 18 THEN 'EVENT' WHEN 6 THEN 'FOLDER' WHEN 19 THEN 'INTERVAL' WHEN 17 THEN 'JOB' WHEN 9 THEN 'JOB_DEFINITION' WHEN 10 THEN 'NAMED_RESOURCE' WHEN 20 THEN 'SCHEDULE' WHEN 22 THEN 'SCHEDULED_EVENT' WHEN 15 THEN 'SCOPE' WHEN 21 THEN 'GROUP' WHEN 11 THEN 'RESOURCE' WHEN 2 THEN 'EXIT_STATE_DEFINITION' WHEN 31 THEN 'NICE_PROFILE' WHEN 3 THEN 'EXIT_STATE_PROFILE' WHEN 4 THEN 'EXIT_STATE_MAPPING' WHEN 5 THEN 'EXIT_STATE_TRANSLATION' WHEN 13 THEN 'RESOURCE_STATE_DEFINITION' WHEN 14 THEN 'RESOURCE_STATE_PROFILE' WHEN 29 THEN 'WATCH_TYPE' WHEN 12 THEN 'RESOURCE_STATE_MAPPING' WHEN 7 THEN 'FOOTPRINT' WHEN 8 THEN 'USER' WHEN 30 THEN 'OBJECT_MONITOR' WHEN 0 THEN 'SYSTEM' END AS OBJECT_TYPE
    , PRIVS                          AS PRIVS
    , DELETE_VERSION                 AS DELETE_VERSION
    , CREATOR_U_ID                   AS CREATOR_U_ID
    , '01-JAN-1970 00:00:00 GMT' + date(char(decimal((CREATE_TS- decimal(CREATE_TS/1125899906842624, 18, 0)*1125899906842624)/1000, 18, 0)) + ' secs') AS CREATE_TS
    , CHANGER_U_ID                   AS CHANGER_U_ID
    , '01-JAN-1970 00:00:00 GMT' + date(char(decimal((CHANGE_TS- decimal(CHANGE_TS/1125899906842624, 18, 0)*1125899906842624)/1000, 18, 0)) + ' secs') AS CHANGE_TS
  FROM GRANTS;\g
