-- Generated by Oracle SQL Developer Data Modeler 4.1.0.881
--   at:        2015-12-15 09:35:47 GMT
--   site:      Oracle Database 11g
--   type:      Oracle Database 11g




CREATE TABLE oauth_approval_privs
  (
    id           NUMBER NOT NULL ,
    approval_id  NUMBER NOT NULL ,
    privilege_id NUMBER NOT NULL ,
    schema_id    NUMBER NOT NULL ,
    created_by   VARCHAR2 (255) ,
    created_on   DATE ,
    updated_by   VARCHAR2 (255) ,
    updated_on   DATE
  ) ;
CREATE UNIQUE INDEX oauth_app_privs_unique1 ON oauth_approval_privs
  (
    schema_id ASC , approval_id ASC , privilege_id ASC
  )
  ;
  CREATE INDEX oauth_app_privs_idx1 ON oauth_approval_privs
    (
      approval_id ASC
    ) ;
  CREATE INDEX oauth_app_privs_idx2 ON oauth_approval_privs
    (
      privilege_id ASC
    ) ;
  ALTER TABLE oauth_approval_privs ADD CONSTRAINT oauth_app_privs_pk PRIMARY KEY ( id ) ;

CREATE TABLE oauth_approvals
  (
    id         NUMBER NOT NULL ,
    client_id  NUMBER NOT NULL ,
    user_name  VARCHAR2 (255) NOT NULL ,
    status     VARCHAR2 (30) NOT NULL ,
    schema_id  NUMBER NOT NULL ,
    created_by VARCHAR2 (255) ,
    created_on DATE ,
    updated_by VARCHAR2 (255) ,
    updated_on DATE
  ) ;
ALTER TABLE oauth_approvals ADD CONSTRAINT oauth_approvals_status_ck CHECK ( status IN ('APPROVED', 'DENIED', 'PENDING')) ;
COMMENT ON COLUMN oauth_approvals.client_id
IS
  'Identifies the ID of the client' ;
  COMMENT ON COLUMN oauth_approvals.user_name
IS
  'The identifier of the user being asked to grant access' ;
  COMMENT ON COLUMN oauth_approvals.status
IS
  'The state of the access request' ;
CREATE UNIQUE INDEX oauth_approvals_unique1 ON oauth_approvals ( schema_id ASC , client_id ASC , user_name ASC ) ;
  CREATE INDEX oauth_approvals_idx1 ON oauth_approvals
    ( client_id ASC
    ) ;
  CREATE INDEX oauth_approvals_idx2 ON oauth_approvals
    ( schema_id ASC
    ) ;
  CREATE INDEX oauth_approvals_idx3 ON oauth_approvals
    ( user_name ASC
    ) ;
  ALTER TABLE oauth_approvals ADD CONSTRAINT oauth_approvals_pk PRIMARY KEY ( id ) ;

CREATE TABLE oauth_client_privileges
  (
    id           NUMBER NOT NULL ,
    client_id    NUMBER NOT NULL ,
    privilege_id NUMBER NOT NULL ,
    schema_id    NUMBER NOT NULL ,
    created_by   VARCHAR2 (255) ,
    created_on   DATE ,
    updated_by   VARCHAR2 (255) ,
    updated_on   DATE
  ) ;
CREATE UNIQUE INDEX oauth_client_privs_uniq1 ON oauth_client_privileges
  (
    schema_id ASC , client_id ASC , privilege_id ASC
  )
  ;
  CREATE INDEX oauth_client_privs_idx1 ON oauth_client_privileges
    (
      client_id ASC
    ) ;
  CREATE INDEX oauth_client_privs_idx2 ON oauth_client_privileges
    (
      privilege_id ASC
    ) ;
  ALTER TABLE oauth_client_privileges ADD CONSTRAINT oauth_client_privs_pk PRIMARY KEY ( id ) ;

CREATE TABLE oauth_client_roles
  (
    id         NUMBER NOT NULL ,
    client_id  NUMBER NOT NULL ,
    role_id    NUMBER NOT NULL ,
    schema_id  NUMBER NOT NULL ,
    created_by VARCHAR2 (255) ,
    created_on DATE ,
    updated_by VARCHAR2 (255) ,
    updated_on DATE
  ) ;
ALTER TABLE oauth_client_roles ADD CONSTRAINT oauth_client_roles_PK PRIMARY KEY ( id ) ;

CREATE TABLE oauth_clients
  (
    id                  NUMBER NOT NULL ,
    name                VARCHAR2 (255) NOT NULL ,
    description         VARCHAR2 (4000) ,
    auth_flow           VARCHAR2 (30) NOT NULL ,
    apex_application_id NUMBER ,
    response_type       VARCHAR2 (30) NOT NULL ,
    client_id           VARCHAR2 (32) NOT NULL ,
    client_secret       VARCHAR2 (32) ,
    redirect_uri        VARCHAR2 (2000) ,
    support_email       VARCHAR2 (255) NOT NULL ,
    support_uri         VARCHAR2 (2000) ,
    about_url           VARCHAR2 (512) ,
    logo_content_type   VARCHAR2 (255) ,
    logo_image BLOB ,
    schema_id       NUMBER NOT NULL ,
    created_by      VARCHAR2 (255) ,
    created_on      DATE ,
    updated_by      VARCHAR2 (255) ,
    updated_on      DATE ,
    allowed_origins VARCHAR2 (4000)
  ) ;
--  WARNING: Column oauth_clients.response_type has no check constraint defined for this database type
ALTER TABLE oauth_clients ADD CONSTRAINT oauth_clients_af_ck CHECK (auth_flow             IN ('AUTH_CODE','IMPLICIT','PASSWORD','CLIENT_CRED','EXCHANGE')) ;
ALTER TABLE oauth_clients ADD CONSTRAINT oauth_clients_response_type CHECK (response_type IN ( 'CODE','TOKEN')) ;
COMMENT ON COLUMN oauth_clients.name
IS
  'The name of the RESTful Security Constraints client application' ;
  COMMENT ON COLUMN oauth_clients.description
IS
  'A brief description of the purpose of the application' ;
  COMMENT ON COLUMN oauth_clients.apex_application_id
IS
  'The Application Express application ID' ;
  COMMENT ON COLUMN oauth_clients.response_type
IS
  'Identifies the type of RESTful Security Constraints response the client expects' ;
  COMMENT ON COLUMN oauth_clients.client_id
IS
  'Uniquely identifies the RESTful Security Constraints client' ;
  COMMENT ON COLUMN oauth_clients.client_secret
IS
  'The secret used to assert the RESTful Security Constraints client''s identity' ;
  COMMENT ON COLUMN oauth_clients.redirect_uri
IS
  'The URI the client wishes the RESTful Security Constraints response to be sent to' ;
  COMMENT ON COLUMN oauth_clients.support_email
IS
  'The email address users can send queries about the RESTful Security Constraints client to' ;
  COMMENT ON COLUMN oauth_clients.support_uri
IS
  'The uri where users can look for support from the client' ;
  COMMENT ON COLUMN oauth_clients.about_url
IS
  'The URL address users can be redirected to' ;
CREATE UNIQUE INDEX oauth_clients_unique1 ON oauth_clients ( schema_id ASC , name ASC ) ;
CREATE UNIQUE INDEX oauth_clients_unique2 ON oauth_clients ( schema_id ASC , client_id ASC ) ;
  CREATE INDEX oauth_clients_idx1 ON oauth_clients
    ( apex_application_id ASC
    ) ;
  ALTER TABLE oauth_clients ADD CONSTRAINT oauth_clients_pk PRIMARY KEY ( id ) ;

CREATE TABLE oauth_pending_approvals
  (
    id           NUMBER NOT NULL ,
    approval_id  NUMBER NOT NULL ,
    client_state VARCHAR2 (255) NOT NULL ,
    schema_id    NUMBER NOT NULL ,
    created_by   VARCHAR2 (255) ,
    created_on   DATE ,
    updated_by   VARCHAR2 (255) ,
    updated_on   DATE
  ) ;
COMMENT ON COLUMN oauth_pending_approvals.approval_id
IS
  'Identifies the ID of the approval' ;
  COMMENT ON COLUMN oauth_pending_approvals.client_state
IS
  'A unique value provided by the OAuth2 client to prevent CSRF attacks' ;
  CREATE INDEX oauth_pend_apprv_idx1 ON oauth_pending_approvals
    (
      approval_id ASC
    ) ;
  CREATE INDEX oauth_pend_apprv_idx2 ON oauth_pending_approvals
    (
      schema_id ASC
    ) ;
  CREATE INDEX oauth_pend_apprv_idx3 ON oauth_pending_approvals
    (
      created_by ASC
    ) ;
  ALTER TABLE oauth_pending_approvals ADD CONSTRAINT oauth_pend_apprv_pk PRIMARY KEY ( id ) ;
  ALTER TABLE oauth_pending_approvals ADD CONSTRAINT oauth_pend_apprv_unique1 UNIQUE ( approval_id , client_state ) ;

CREATE TABLE oauth_sessions
  (
    id           NUMBER NOT NULL ,
    approval_id  NUMBER NOT NULL ,
    session_id   NUMBER NOT NULL ,
    client_state VARCHAR2 (255) ,
    schema_id    NUMBER NOT NULL ,
    created_by   VARCHAR2 (255) ,
    created_on   DATE ,
    updated_by   VARCHAR2 (255) ,
    updated_on   DATE
  ) ;
COMMENT ON COLUMN oauth_sessions.client_state
IS
  'The client provided state value, which can be used for audit purposes to link an OAuth2 User Session to a client session' ;
CREATE UNIQUE INDEX oauth_user_sess_idx1 ON oauth_sessions
  (
    schema_id ASC , session_id ASC
  )
  ;
  CREATE INDEX oauth_user_sess_idx4 ON oauth_sessions
    ( approval_id ASC
    ) ;
  ALTER TABLE oauth_sessions ADD CONSTRAINT oauth_user_sess_pk PRIMARY KEY ( id ) ;

CREATE TABLE ords_handlers
  (
    id             NUMBER NOT NULL ,
    template_id    NUMBER NOT NULL ,
    source_type    VARCHAR2 (255) NOT NULL ,
    method         VARCHAR2 (10) NOT NULL ,
    mimes_allowed  VARCHAR2 (255) ,
    items_per_page NUMBER ,
    source CLOB NOT NULL ,
    comments   VARCHAR2 (4000) ,
    schema_id  NUMBER NOT NULL ,
    created_by VARCHAR2 (255) ,
    created_on DATE ,
    updated_by VARCHAR2 (255) ,
    updated_on DATE
  ) ;
ALTER TABLE ords_handlers ADD CONSTRAINT ords_handlers_md_ck CHECK ( method IN ('DELETE', 'GET', 'PATCH', 'POST', 'PUT')) ;
COMMENT ON COLUMN ords_handlers.source_type
IS
  'Defines the strategy used to transform SQL results into a resource' ;
  COMMENT ON COLUMN ords_handlers.method
IS
  'Defines the HTTP Method which this handler will respond to' ;
  COMMENT ON COLUMN ords_handlers.mimes_allowed
IS
  'Applies to PUT and POST only, comma separated list of MIME types that the handler will accept' ;
  COMMENT ON COLUMN ords_handlers.items_per_page
IS
  'Applies to Query format only, indicates maximum number of rows to return' ;
  COMMENT ON COLUMN ords_handlers.source
IS
  'The SQL or PL/SQL used to generate the resource' ;
CREATE UNIQUE INDEX ords_handlers_unique1 ON ords_handlers
  (
    schema_id ASC , template_id ASC , method ASC
  )
  ;
  CREATE INDEX ords_handlers_idx2 ON ords_handlers
    ( schema_id ASC
    ) ;
  CREATE INDEX ords_handlers_idx3 ON ords_handlers
    ( template_id ASC
    ) ;
  ALTER TABLE ords_handlers ADD CONSTRAINT ords_handlers_pk PRIMARY KEY ( id ) ;

CREATE TABLE ords_modules
  (
    id             NUMBER NOT NULL ,
    name           VARCHAR2 (255) NOT NULL ,
    uri_prefix     VARCHAR2 (255) NOT NULL ,
    items_per_page NUMBER NOT NULL ,
    status         VARCHAR2 (30) DEFAULT 'PUBLISHED' NOT NULL ,
    comments       VARCHAR2 (4000) ,
    schema_id      NUMBER NOT NULL ,
    created_by     VARCHAR2 (255) ,
    created_on     DATE ,
    updated_by     VARCHAR2 (255) ,
    updated_on     DATE
  ) ;
ALTER TABLE ords_modules ADD CONSTRAINT ords_modules_status_ck CHECK ( status IN ('NOT_PUBLISHED', 'PUBLISHED')) ;
ALTER TABLE ords_modules ADD CONSTRAINT rest_modules_status_ck CHECK (status  IN ('PUBLISHED','NOT_PUBLISHED')) ;
COMMENT ON COLUMN ords_modules.name
IS
  'Identifies the module name' ;
  COMMENT ON COLUMN ords_modules.uri_prefix
IS
  'Defines a prefix on the URI path, which all Resource Templates within the module are resolved relative to, e.g. orders/' ;
  COMMENT ON COLUMN ords_modules.items_per_page
IS
  'Defines the default number of rows returned by Resource Handlers within this module' ;
  COMMENT ON COLUMN ords_modules.status
IS
  'Controls whether this module is currently active or not' ;
CREATE UNIQUE INDEX ords_modules_unique1 ON ords_modules ( schema_id ASC , name ASC ) ;
  ALTER TABLE ords_modules ADD CONSTRAINT ords_modules_pk PRIMARY KEY ( id ) ;
  ALTER TABLE ords_modules ADD CONSTRAINT ords_unique_prefix_uc UNIQUE ( uri_prefix , schema_id ) ;

CREATE TABLE ords_objects
  (
    ID             NUMBER NOT NULL ,
    SCHEMA_ID      NUMBER NOT NULL ,
    PARSING_OBJECT VARCHAR2 (255 BYTE) ,
    OBJECT_ALIAS   VARCHAR2 (255 BYTE) ,
    TYPE           VARCHAR2 (30 BYTE) ,
    STATUS         VARCHAR2 (30 BYTE) DEFAULT 'ENABLED' ,
    AUTO_REST_AUTH VARCHAR2 (30 BYTE) DEFAULT 'ENABLED' NOT NULL ,
    OPS_ALLOWED    NUMBER ,
    TYPE_PATH      VARCHAR2 (10 BYTE) DEFAULT 'ENABLED' ,
    CREATED_BY     VARCHAR2 (255 BYTE) ,
    CREATED_ON     DATE ,
    UPDATED_BY     VARCHAR2 (255 BYTE) ,
    UPDATED_ON     DATE
  ) ;
ALTER TABLE ords_objects ADD CONSTRAINT ORDS_OBJECTS_TYPE_CK CHECK ( TYPE                     IN ('MVIEW', 'TABLE', 'VIEW')) ;
ALTER TABLE ords_objects ADD CONSTRAINT ORDS_OBJECTS_STATUS_CK CHECK ( STATUS                 IN ('DISABLED', 'ENABLED')) ;
ALTER TABLE ords_objects ADD CONSTRAINT ords_objects_auto_rest_auth_ck CHECK ( AUTO_REST_AUTH IN ('DISABLED', 'ENABLED')) ;
ALTER TABLE ords_objects ADD CONSTRAINT ORDS_OBJECTS_TYPE_PATH_CK CHECK ( TYPE_PATH           IN ('DISABLED', 'ENABLED')) ;
COMMENT ON COLUMN ords_objects.ID
IS
  'Identifies the object' ;
  COMMENT ON COLUMN ords_objects.SCHEMA_ID
IS
  'Specifies the owning schema' ;
  COMMENT ON COLUMN ords_objects.PARSING_OBJECT
IS
  'The database object name' ;
  COMMENT ON COLUMN ords_objects.OBJECT_ALIAS
IS
  'Specifies the object''s alias' ;
  COMMENT ON COLUMN ords_objects.TYPE
IS
  'Specifies the object type' ;
  COMMENT ON COLUMN ords_objects.STATUS
IS
  'Specifies if RESTful Services for the specified object are enabled or disabled' ;
  COMMENT ON COLUMN ords_objects.AUTO_REST_AUTH
IS
  'Specifies if authorisation is required to access this object' ;
  COMMENT ON COLUMN ords_objects.OPS_ALLOWED
IS
  'Bit mask specifying operations allowed, null value indicates all operations allowed' ;
  COMMENT ON COLUMN ords_objects.TYPE_PATH
IS
  'Specifies the object type path shoule be used' ;
CREATE UNIQUE INDEX ORDS_OBJECTS_PK ON ords_objects ( ID ASC ) ;
CREATE UNIQUE INDEX ORDS_OBJECTS_ALIAS_UN ON ords_objects ( SCHEMA_ID ASC , OBJECT_ALIAS ASC ) ;
  ALTER TABLE ords_objects ADD CONSTRAINT ORDS_OBJECTS_PK PRIMARY KEY ( ID ) ;

CREATE TABLE ords_parameters
  (
    id                 NUMBER NOT NULL ,
    handler_id         NUMBER NOT NULL ,
    name               VARCHAR2 (100) NOT NULL ,
    bind_variable_name VARCHAR2 (30) ,
    source_type        VARCHAR2 (20) NOT NULL ,
    access_method      VARCHAR2 (10) NOT NULL ,
    param_type         VARCHAR2 (30) NOT NULL ,
    comments           VARCHAR2 (4000) ,
    schema_id          NUMBER NOT NULL ,
    created_by         VARCHAR2 (255) ,
    created_on         DATE ,
    updated_by         VARCHAR2 (255) ,
    updated_on         DATE
  ) ;
ALTER TABLE ords_parameters ADD CONSTRAINT ords_params_st_ck CHECK ( source_type              IN ('HEADER', 'RESPONSE', 'URI')) ;
ALTER TABLE ords_parameters ADD CONSTRAINT ords_params_am_ck CHECK ( access_method            IN ('IN', 'INOUT', 'OUT')) ;
ALTER TABLE ords_parameters ADD CONSTRAINT ords_params_pt_ck CHECK ( param_type               IN ('BOOLEAN', 'DOUBLE', 'INT', 'LONG', 'STRING', 'TIMESTAMP')) ;
ALTER TABLE ords_parameters ADD CONSTRAINT rest_params_source_type_ck CHECK ( source_type     IN ( 'URI','HEADER','RESPONSE')) ;
ALTER TABLE ords_parameters ADD CONSTRAINT rest_params_access_method_ck CHECK ( access_method IN ( 'IN','OUT','INOUT') ) ;
ALTER TABLE ords_parameters ADD CONSTRAINT rest_params_param_type_ck CHECK (param_type        IN ( 'STRING','INT','DOUBLE','BOOLEAN','LONG','TIMESTAMP')) ;
COMMENT ON COLUMN ords_parameters.name
IS
  'The name of the parameter, as it is named in the URI Template or HTTP Header. Used to map names that are not legal SQL parameter names' ;
  COMMENT ON COLUMN ords_parameters.bind_variable_name
IS
  'The name of the parameter, as it will be refered to in the SQL' ;
  COMMENT ON COLUMN ords_parameters.source_type
IS
  'Identifies if the parameter originates in the URI Template or a HTTP Header' ;
  COMMENT ON COLUMN ords_parameters.access_method
IS
  'Identifies if the parameter is an input value, output value or both' ;
  COMMENT ON COLUMN ords_parameters.param_type
IS
  'Identifies the native type of the parameter' ;
CREATE UNIQUE INDEX ords_params_unique1 ON ords_parameters ( schema_id ASC , handler_id ASC , name ASC ) ;
CREATE UNIQUE INDEX ords_params_unique2 ON ords_parameters ( schema_id ASC , handler_id ASC , bind_variable_name ASC ) ;
  CREATE INDEX ords_params_idx1 ON ords_parameters
    ( schema_id ASC
    ) ;
  CREATE INDEX ords_params_idx2 ON ords_parameters
    ( handler_id ASC
    ) ;
  ALTER TABLE ords_parameters ADD CONSTRAINT ords_params_pk PRIMARY KEY ( id ) ;

CREATE TABLE ords_schemas
  (
    id             NUMBER NOT NULL ,
    parsing_schema VARCHAR2 (30) NOT NULL ,
    url_mapping_id NUMBER NOT NULL ,
    status         VARCHAR2 (30) DEFAULT 'ENABLED' NOT NULL ,
    auto_rest_auth VARCHAR2 (30) DEFAULT 'ENABLED' NOT NULL ,
    ops_allowed    NUMBER ,
    created_by     VARCHAR2 (255) ,
    created_on     DATE ,
    updated_by     VARCHAR2 (255) ,
    updated_on     DATE
  ) ;
ALTER TABLE ords_schemas ADD CONSTRAINT ords_schemas_status_ck CHECK ( status                 IN ('DISABLED', 'ENABLED')) ;
ALTER TABLE ords_schemas ADD CONSTRAINT ords_schemas_auto_rest_auth_ck CHECK ( auto_rest_auth IN ('DISABLED', 'ENABLED')) ;
COMMENT ON COLUMN ords_schemas.id
IS
  'Identifies the schema' ;
  COMMENT ON COLUMN ords_schemas.parsing_schema
IS
  'The database schema name' ;
  COMMENT ON COLUMN ords_schemas.status
IS
  'Specifies if RESTful Services in the specified schema are enabled or disabled' ;
  COMMENT ON COLUMN ords_schemas.auto_rest_auth
IS
  'Specifies if authorisation is required for AutoRest access to metadata-catalog' ;
  COMMENT ON COLUMN ords_schemas.ops_allowed
IS
  'Bit mask specifying operations allowed, null value indicates all operations allowed' ;
CREATE UNIQUE INDEX ords_schemas_parsing ON ords_schemas ( parsing_schema ASC ) ;
  ALTER TABLE ords_schemas ADD CONSTRAINT ords_schemas_pk PRIMARY KEY ( id ) ;

CREATE TABLE ords_templates
  (
    id           NUMBER NOT NULL ,
    module_id    NUMBER ,
    uri_template VARCHAR2 (600) NOT NULL ,
    priority     NUMBER NOT NULL ,
    etag_type    VARCHAR2 (30) NOT NULL ,
    etag_query   VARCHAR2 (4000) ,
    comments     VARCHAR2 (4000) ,
    schema_id    NUMBER NOT NULL ,
    created_by   VARCHAR2 (255) ,
    created_on   DATE ,
    updated_by   VARCHAR2 (255) ,
    updated_on   DATE
  ) ;
ALTER TABLE ords_templates ADD CONSTRAINT ords_templates_priority_ck CHECK ( priority BETWEEN 0 AND 9) ;
ALTER TABLE ords_templates ADD CONSTRAINT rest_templates_etag_ck CHECK (etag_type IN ('HASH', 'QUERY','NONE')) ;
ALTER TABLE ords_templates ADD CONSTRAINT rest_templates_priority CHECK (priority BETWEEN 0 AND 9) ;
ALTER TABLE ords_templates ADD CONSTRAINT rest_templates_ne_pattern_ck CHECK (uri_template <> '') ;
COMMENT ON COLUMN ords_templates.module_id
IS
  'Identifies the module this template is associated with' ;
  COMMENT ON COLUMN ords_templates.uri_template
IS
  'Identifies the pattern of URIs that the Resource Template will handle, e.g. hello?who={person}' ;
  COMMENT ON COLUMN ords_templates.priority
IS
  'When two or more Resource Templates match a single URI this value is used to choose a Resource Template, the Resource Template with the highest priority is chosen' ;
  COMMENT ON COLUMN ords_templates.etag_type
IS
  'Identifies the type of entity tag' ;
  COMMENT ON COLUMN ords_templates.etag_query
IS
  'Display the entity tag query' ;
  COMMENT ON COLUMN ords_templates.comments
IS
  'Display the comments on the RESTful Service Resource Template' ;
CREATE UNIQUE INDEX ords_templates_unique1 ON ords_templates
  (
    schema_id ASC , module_id ASC , uri_template ASC
  )
  ;
  CREATE INDEX ords_templates_idx1 ON ords_templates
    ( schema_id ASC
    ) ;
  CREATE INDEX ords_templates_idx2 ON ords_templates
    ( module_id ASC
    ) ;
  ALTER TABLE ords_templates ADD CONSTRAINT ords_templates_pk PRIMARY KEY ( id ) ;

CREATE TABLE ords_url_mappings
  (
    id         NUMBER NOT NULL ,
    type       VARCHAR2 (10) DEFAULT 'BASE_PATH' NOT NULL ,
    pattern    VARCHAR2 (255) NOT NULL ,
    created_by VARCHAR2 (255) NOT NULL ,
    updated_by VARCHAR2 (255) NOT NULL ,
    created_on DATE NOT NULL ,
    updated_on DATE NOT NULL
  ) ;
ALTER TABLE ords_url_mappings ADD CONSTRAINT ords_url_mappings_type_ck CHECK ( type IN ('BASE_PATH', 'BASE_URL', 'NONE')) ;
ALTER TABLE ords_url_mappings ADD CONSTRAINT ords_url_mappings_PK PRIMARY KEY ( id ) ;
ALTER TABLE ords_url_mappings ADD CONSTRAINT ords_url_mappings_unq_pattern UNIQUE ( type , pattern ) ;

CREATE TABLE ords_workspace_schemas
  (
    id           NUMBER NOT NULL ,
    workspace_id NUMBER NOT NULL ,
    schema_id    NUMBER NOT NULL ,
    created_by   VARCHAR2 (255) ,
    created_on   DATE ,
    updated_by   VARCHAR2 (255) ,
    updated_on   DATE
  ) ;
ALTER TABLE ords_workspace_schemas ADD CONSTRAINT ords_workspace_schemas_pk PRIMARY KEY ( id ) ;
ALTER TABLE ords_workspace_schemas ADD CONSTRAINT ords_workspace_schemas_un UNIQUE ( workspace_id ) ;

CREATE TABLE sec_authenticators
  (
    id                 NUMBER NOT NULL ,
    label              VARCHAR2 (255) NOT NULL ,
    authenticator_type VARCHAR2 (30) NOT NULL ,
    provider           VARCHAR2 (1000) NOT NULL ,
    comments           VARCHAR2 (4000) ,
    schema_id          NUMBER NOT NULL ,
    created_by         VARCHAR2 (255) ,
    created_on         DATE ,
    updated_by         VARCHAR2 (255) ,
    updated_on         DATE
  ) ;
ALTER TABLE sec_authenticators ADD CONSTRAINT ords_auth_type_ck CHECK ( authenticator_type IN ('DATABASE', 'ORDS')) ;
COMMENT ON COLUMN sec_authenticators.id
IS
  'Identifies the authenticators ID' ;
  COMMENT ON COLUMN sec_authenticators.label
IS
  'The name of this authenticator as displayed to an end user' ;
  COMMENT ON COLUMN sec_authenticators.authenticator_type
IS
  'Indicates if the authenticator is provided by the database or the Listener' ;
  COMMENT ON COLUMN sec_authenticators.provider
IS
  'Provides the unique name of the authenticator if implemented by the Listener, or the name of the PL/SQL procedure to invoke, if provided by the database' ;
  COMMENT ON COLUMN sec_authenticators.comments
IS
  'Displays the comments on the authenticator' ;
CREATE UNIQUE INDEX sec_auths_unq_name ON sec_authenticators ( schema_id ASC , label ASC ) ;
CREATE UNIQUE INDEX sec_auths_unq_provider ON sec_authenticators ( schema_id ASC , provider ASC ) ;
  ALTER TABLE sec_authenticators ADD CONSTRAINT sec_auths_pk PRIMARY KEY ( id ) ;

CREATE TABLE sec_keys
  (
    id NUMBER NOT NULL ,
    enc_key RAW (64) NOT NULL ,
    mac_key RAW (64) NOT NULL ,
    schema_id  NUMBER NOT NULL ,
    created_by VARCHAR2 (255) ,
    created_on DATE ,
    updated_by VARCHAR2 (255) ,
    updated_on DATE
  ) ;
ALTER TABLE sec_keys ADD CONSTRAINT sec_keys_PK PRIMARY KEY ( id ) ;

CREATE TABLE sec_origins_allowed_modules
  (
    id              NUMBER NOT NULL ,
    origins_allowed VARCHAR2 (4000) NOT NULL ,
    module_id       NUMBER NOT NULL ,
    created_by      VARCHAR2 (255) ,
    created_on      DATE ,
    updated_by      VARCHAR2 (255) ,
    updated_on      DATE
  ) ;
ALTER TABLE sec_origins_allowed_modules ADD CONSTRAINT sec_origins_allowed_pk PRIMARY KEY ( id ) ;
ALTER TABLE sec_origins_allowed_modules ADD CONSTRAINT sec_origins_allowed_un UNIQUE ( module_id ) ;

CREATE TABLE sec_privilege_auths
  (
    id           NUMBER NOT NULL ,
    privilege_id NUMBER NOT NULL ,
    auth_id      NUMBER NOT NULL ,
    schema_id    NUMBER NOT NULL ,
    created_by   VARCHAR2 (255) ,
    created_on   DATE ,
    updated_by   VARCHAR2 (255) ,
    updated_on   DATE
  ) ;
CREATE UNIQUE INDEX privilege_auths_unique1 ON sec_privilege_auths
  (
    schema_id ASC , auth_id ASC , privilege_id ASC
  )
  ;
  ALTER TABLE sec_privilege_auths ADD CONSTRAINT privilege_auths_pk PRIMARY KEY ( id ) ;

CREATE TABLE sec_privilege_mappings
  (
    id           NUMBER CONSTRAINT NNC_sec_priv_mappings_id NOT NULL ,
    privilege_id NUMBER CONSTRAINT NNC_sec_priv_maps_privilege_id NOT NULL ,
    pattern      VARCHAR2 (255) CONSTRAINT NNC_sec_priv_maps_pattern NOT NULL ,
    schema_id    NUMBER CONSTRAINT NNC_sec_priv_maps_schema_id NOT NULL ,
    created_by   VARCHAR2 (255) ,
    created_on   DATE ,
    updated_by   VARCHAR2 (255) ,
    updated_on   DATE
  ) ;
ALTER TABLE sec_privilege_mappings ADD CONSTRAINT sec_privilege_mappings_PK PRIMARY KEY ( id ) ;
ALTER TABLE sec_privilege_mappings ADD CONSTRAINT sec_priv_map_pattern_unq UNIQUE ( pattern , schema_id ) ;

CREATE TABLE sec_privilege_modules
  (
    id           NUMBER NOT NULL ,
    privilege_id NUMBER NOT NULL ,
    module_id    NUMBER NOT NULL ,
    schema_id    NUMBER NOT NULL ,
    created_by   VARCHAR2 (255) ,
    created_on   DATE ,
    updated_by   VARCHAR2 (255) ,
    updated_on   DATE
  ) ;
ALTER TABLE sec_privilege_modules ADD CONSTRAINT sec_privilege_modules_PK PRIMARY KEY ( id ) ;
ALTER TABLE sec_privilege_modules ADD CONSTRAINT sec_priv_modules_unq UNIQUE ( privilege_id , module_id ) ;

CREATE TABLE sec_privilege_roles
  (
    id           NUMBER NOT NULL ,
    privilege_id NUMBER NOT NULL ,
    role_id      NUMBER NOT NULL ,
    schema_id    NUMBER NOT NULL ,
    created_by   VARCHAR2 (255) ,
    created_on   DATE ,
    updated_by   VARCHAR2 (255) ,
    updated_on   DATE
  ) ;
CREATE UNIQUE INDEX privilege_roles_unique1 ON sec_privilege_roles
  (
    schema_id ASC , role_id ASC , privilege_id ASC
  )
  ;
  ALTER TABLE sec_privilege_roles ADD CONSTRAINT privilege_roles_pk PRIMARY KEY ( id ) ;

CREATE TABLE sec_privileges
  (
    id          NUMBER NOT NULL ,
    name        VARCHAR2 (255) NOT NULL ,
    label       VARCHAR2 (255) ,
    description VARCHAR2 (4000) ,
    comments    VARCHAR2 (4000) ,
    schema_id   NUMBER NOT NULL ,
    created_by  VARCHAR2 (255) ,
    created_on  DATE ,
    updated_by  VARCHAR2 (255) ,
    updated_on  DATE
  ) ;
COMMENT ON COLUMN sec_privileges.id
IS
  'Identifies the privileges ID' ;
  COMMENT ON COLUMN sec_privileges.name
IS
  'A Programmatic identifier for this security constraint, no whitespace allowed' ;
  COMMENT ON COLUMN sec_privileges.label
IS
  'The name of this security constraint as displayed to an end user' ;
  COMMENT ON COLUMN sec_privileges.description
IS
  'Provides a brief description of the purpose of the resources protected by this constraint' ;
  COMMENT ON COLUMN sec_privileges.comments
IS
  'Displays the comments on the privilege' ;
CREATE UNIQUE INDEX privileges_unq_name ON sec_privileges
  (
    schema_id ASC , name ASC
  )
  ;
  ALTER TABLE sec_privileges ADD CONSTRAINT privileges_pk PRIMARY KEY ( id ) ;

CREATE TABLE sec_role_mappings
  (
    id                NUMBER NOT NULL ,
    role_name_pattern VARCHAR2 (1000) NOT NULL ,
    role_id           NUMBER NOT NULL ,
    schema_id         NUMBER NOT NULL ,
    created_by        VARCHAR2 (255) ,
    created_on        DATE ,
    updated_by        VARCHAR2 (255) ,
    updated_on        DATE
  ) ;
CREATE UNIQUE INDEX role_mappings_unq_pattern ON sec_role_mappings
  (
    schema_id ASC , role_name_pattern ASC
  )
  ;
  ALTER TABLE sec_role_mappings ADD CONSTRAINT role_mappings_pk PRIMARY KEY ( id ) ;

CREATE TABLE sec_roles
  (
    id         NUMBER NOT NULL ,
    name       VARCHAR2 (255) NOT NULL ,
    schema_id  NUMBER NOT NULL ,
    created_by VARCHAR2 (255) ,
    created_on DATE ,
    updated_by VARCHAR2 (255) ,
    updated_on DATE
  ) ;
CREATE UNIQUE INDEX roles_unq_name ON sec_roles
  (
    schema_id ASC , name ASC
  )
  ;
  ALTER TABLE sec_roles ADD CONSTRAINT sec_roles_pk PRIMARY KEY ( id ) ;

CREATE TABLE sec_sessions
  (
    id             NUMBER NOT NULL ,
    token          VARCHAR2 (255) NOT NULL ,
    token_duration NUMBER NOT NULL ,
    token_type     VARCHAR2 (30) NOT NULL ,
    userid         VARCHAR2 (255) NOT NULL ,
    state CLOB ,
    schema_id  NUMBER NOT NULL ,
    created_by VARCHAR2 (255) ,
    created_on DATE ,
    updated_by VARCHAR2 (255) ,
    updated_on DATE
  ) ;
ALTER TABLE sec_sessions ADD CONSTRAINT sec_sessions_token_type_CK CHECK (token_type IN ('AUTH_CODE','FIRST_PARTY','THIRD_PARTY','REFRESH')) ;
ALTER TABLE sec_sessions ADD CONSTRAINT sec_sessions_PK PRIMARY KEY ( id ) ;
ALTER TABLE sec_sessions ADD CONSTRAINT sec_sessions__UN UNIQUE ( token ) ;

ALTER TABLE sec_authenticators ADD CONSTRAINT auths_schema_fk FOREIGN KEY ( schema_id ) REFERENCES ords_schemas ( id ) ON
DELETE CASCADE ;

ALTER TABLE oauth_approval_privs ADD CONSTRAINT oauth_app_privs_fk FOREIGN KEY ( approval_id ) REFERENCES oauth_approvals ( id ) ON
DELETE CASCADE ;

ALTER TABLE oauth_approval_privs ADD CONSTRAINT oauth_app_privs_fk2 FOREIGN KEY ( privilege_id ) REFERENCES sec_privileges ( id ) ON
DELETE CASCADE ;

ALTER TABLE oauth_approval_privs ADD CONSTRAINT oauth_app_privs_sgid_fk FOREIGN KEY ( schema_id ) REFERENCES ords_schemas ( id ) ON
DELETE CASCADE ;

ALTER TABLE oauth_approvals ADD CONSTRAINT oauth_approvals_fk FOREIGN KEY ( client_id ) REFERENCES oauth_clients ( id ) ON
DELETE CASCADE ;

ALTER TABLE oauth_approvals ADD CONSTRAINT oauth_approvals_sgid_fk FOREIGN KEY ( schema_id ) REFERENCES ords_schemas ( id ) ON
DELETE CASCADE ;

ALTER TABLE oauth_client_privileges ADD CONSTRAINT oauth_client_privs_fk FOREIGN KEY ( client_id ) REFERENCES oauth_clients ( id ) ON
DELETE CASCADE ;

ALTER TABLE oauth_client_privileges ADD CONSTRAINT oauth_client_privs_fk2 FOREIGN KEY ( privilege_id ) REFERENCES sec_privileges ( id ) ON
DELETE CASCADE ;

ALTER TABLE oauth_client_privileges ADD CONSTRAINT oauth_client_privs_fk3 FOREIGN KEY ( schema_id ) REFERENCES ords_schemas ( id ) ON
DELETE CASCADE ;

ALTER TABLE oauth_client_roles ADD CONSTRAINT oauth_client_roles_clients_FK FOREIGN KEY ( client_id ) REFERENCES oauth_clients ( id ) ON
DELETE CASCADE ;

ALTER TABLE oauth_client_roles ADD CONSTRAINT oauth_client_roles_roles_FK FOREIGN KEY ( role_id ) REFERENCES sec_roles ( id ) ON
DELETE CASCADE ;

ALTER TABLE oauth_client_roles ADD CONSTRAINT oauth_client_roles_schemas_FK FOREIGN KEY ( schema_id ) REFERENCES ords_schemas ( id ) ON
DELETE CASCADE ;

ALTER TABLE oauth_clients ADD CONSTRAINT oauth_clients_sgid_fk FOREIGN KEY ( schema_id ) REFERENCES ords_schemas ( id ) ON
DELETE CASCADE ;

ALTER TABLE oauth_pending_approvals ADD CONSTRAINT oauth_pend_apprv_fk FOREIGN KEY ( approval_id ) REFERENCES oauth_approvals ( id ) ON
DELETE CASCADE ;

ALTER TABLE oauth_pending_approvals ADD CONSTRAINT oauth_pend_apprv_sgid_fk FOREIGN KEY ( schema_id ) REFERENCES ords_schemas ( id ) ON
DELETE CASCADE ;

ALTER TABLE oauth_sessions ADD CONSTRAINT oauth_sessions_sec_sessions_FK FOREIGN KEY ( session_id ) REFERENCES sec_sessions ( id ) ON
DELETE CASCADE ;

ALTER TABLE oauth_sessions ADD CONSTRAINT oauth_user_sess_fk FOREIGN KEY ( approval_id ) REFERENCES oauth_approvals ( id ) ON
DELETE CASCADE ;

ALTER TABLE oauth_sessions ADD CONSTRAINT oauth_user_sess_fk2 FOREIGN KEY ( schema_id ) REFERENCES ords_schemas ( id ) ON
DELETE CASCADE ;

ALTER TABLE ords_handlers ADD CONSTRAINT ords_handlers_schema_fk FOREIGN KEY ( schema_id ) REFERENCES ords_schemas ( id ) ON
DELETE CASCADE ;

ALTER TABLE ords_handlers ADD CONSTRAINT ords_handlers_temps_fk FOREIGN KEY ( template_id ) REFERENCES ords_templates ( id ) ON
DELETE CASCADE ;

ALTER TABLE ords_modules ADD CONSTRAINT ords_modules_schema_id_fk FOREIGN KEY ( schema_id ) REFERENCES ords_schemas ( id ) ON
DELETE CASCADE ;

ALTER TABLE ords_objects ADD CONSTRAINT ords_objects_schemas_FK FOREIGN KEY ( SCHEMA_ID ) REFERENCES ords_schemas ( id ) ON
DELETE CASCADE ;

ALTER TABLE ords_parameters ADD CONSTRAINT ords_params_handler_fk FOREIGN KEY ( handler_id ) REFERENCES ords_handlers ( id ) ON
DELETE CASCADE ;

ALTER TABLE ords_parameters ADD CONSTRAINT ords_params_sgid_fk FOREIGN KEY ( schema_id ) REFERENCES ords_schemas ( id ) ON
DELETE CASCADE ;

ALTER TABLE ords_schemas ADD CONSTRAINT ords_schemas_url_mappings_FK FOREIGN KEY ( url_mapping_id ) REFERENCES ords_url_mappings ( id ) ON
DELETE CASCADE ;

ALTER TABLE ords_templates ADD CONSTRAINT ords_templates_mod_fk FOREIGN KEY ( module_id ) REFERENCES ords_modules ( id ) ON
DELETE CASCADE ;

ALTER TABLE ords_templates ADD CONSTRAINT ords_templates_schema_fk FOREIGN KEY ( schema_id ) REFERENCES ords_schemas ( id ) ON
DELETE CASCADE ;

ALTER TABLE ords_workspace_schemas ADD CONSTRAINT ords_workspace_schemas_fk FOREIGN KEY ( schema_id ) REFERENCES ords_schemas ( id ) ON
DELETE CASCADE ;

ALTER TABLE sec_privilege_auths ADD CONSTRAINT privilege_auths_auths_fk FOREIGN KEY ( auth_id ) REFERENCES sec_authenticators ( id ) ON
DELETE CASCADE ;

ALTER TABLE sec_privilege_auths ADD CONSTRAINT privilege_auths_privs_fk FOREIGN KEY ( privilege_id ) REFERENCES sec_privileges ( id ) ON
DELETE CASCADE ;

ALTER TABLE sec_privilege_auths ADD CONSTRAINT privilege_auths_schemas_fk FOREIGN KEY ( schema_id ) REFERENCES ords_schemas ( id ) ON
DELETE CASCADE ;

ALTER TABLE sec_privilege_roles ADD CONSTRAINT privilege_roles_privs_fk FOREIGN KEY ( privilege_id ) REFERENCES sec_privileges ( id ) ON
DELETE CASCADE ;

ALTER TABLE sec_privilege_roles ADD CONSTRAINT privilege_roles_roles_fk FOREIGN KEY ( role_id ) REFERENCES sec_roles ( id ) ON
DELETE CASCADE ;

ALTER TABLE sec_privilege_roles ADD CONSTRAINT privilege_roles_schema_fk FOREIGN KEY ( schema_id ) REFERENCES ords_schemas ( id ) ON
DELETE CASCADE ;

ALTER TABLE sec_privileges ADD CONSTRAINT privileges_schema_fk FOREIGN KEY ( schema_id ) REFERENCES ords_schemas ( id ) ON
DELETE CASCADE ;

ALTER TABLE sec_role_mappings ADD CONSTRAINT role_mappings_roles_fk FOREIGN KEY ( role_id ) REFERENCES sec_roles ( id ) ON
DELETE CASCADE ;

ALTER TABLE sec_role_mappings ADD CONSTRAINT role_mappings_schema_fk FOREIGN KEY ( schema_id ) REFERENCES ords_schemas ( id ) ON
DELETE CASCADE ;

ALTER TABLE sec_roles ADD CONSTRAINT roles_schema_fk FOREIGN KEY ( schema_id ) REFERENCES ords_schemas ( id ) ON
DELETE CASCADE ;

ALTER TABLE sec_keys ADD CONSTRAINT sec_keys_schemas_FK FOREIGN KEY ( schema_id ) REFERENCES ords_schemas ( id ) ON
DELETE CASCADE ;

ALTER TABLE sec_origins_allowed_modules ADD CONSTRAINT sec_origins_allowed_fk FOREIGN KEY ( module_id ) REFERENCES ords_modules ( id ) ON
DELETE CASCADE ;

ALTER TABLE sec_privilege_mappings ADD CONSTRAINT sec_priv_mappings_privs_FK FOREIGN KEY ( privilege_id ) REFERENCES sec_privileges ( id ) ON
DELETE CASCADE ;

ALTER TABLE sec_privilege_mappings ADD CONSTRAINT sec_priv_mappings_schemas_FK FOREIGN KEY ( schema_id ) REFERENCES ords_schemas ( id ) ON
DELETE CASCADE ;

ALTER TABLE sec_privilege_modules ADD CONSTRAINT sec_priv_modules_modid_fk FOREIGN KEY ( module_id ) REFERENCES ords_modules ( id ) ON
DELETE CASCADE ;

ALTER TABLE sec_privilege_modules ADD CONSTRAINT sec_priv_modules_privid_fk FOREIGN KEY ( privilege_id ) REFERENCES sec_privileges ( id ) ON
DELETE CASCADE ;

ALTER TABLE sec_privilege_modules ADD CONSTRAINT sec_priv_modules_schem_fk FOREIGN KEY ( schema_id ) REFERENCES ords_schemas ( id ) ;

ALTER TABLE sec_sessions ADD CONSTRAINT sec_sessions_schemas_fk FOREIGN KEY ( schema_id ) REFERENCES ords_schemas ( id ) ON
DELETE CASCADE ;

CREATE OR REPLACE VIEW ALL_ORDS_CLIENT_PRIVILEGES ( CLIENT_PRIV_ID, CLIENT_PRIV_CREATED_BY, CLIENT_PRIV_UPDATED_BY, CLIENT_PRIV_CLIENT_ID, PRIV_ID, NAME, LABEL, DESCRIPTION, PARSING_SCHEMA, CLIENT_NAME, CLIENT_CREATED_BY ) AS
with sr as  ( select count(1) has_catalog from session_roles where role = 'SELECT_CATALOG_ROLE' )
  SELECT p.id client_priv_id,
  p.created_by client_priv_created_by,
  p.updated_by client_priv_updated_by,
  p.client_id client_priv_client_id,
  sec.id priv_id,
  sec.name,
  sec.label,
  sec.description,
  sch.parsing_schema,
  c.name client_name,
  c.created_by client_created_by
FROM ords_schemas sch,
  sec_privileges sec,
  oauth_client_privileges p,
  oauth_clients c,
  sr
WHERE sch.id           = p.schema_id
AND sch.id             = c.schema_id
AND sec.id             = p.privilege_id
AND p.client_id        = c.id
AND (sch.parsing_schema = sys_context('USERENV', 'CURRENT_USER') or sr.has_catalog = 1) 
;





CREATE OR REPLACE VIEW ALL_ORDS_CLIENT_ROLES  AS
SELECT oauth_clients.id client_id,
  oauth_clients.name client_name,
  sec_roles.id                AS role_id,
  sec_roles.name              AS role_name,
  ords_schemas.id             AS schema_id,
  ords_schemas.parsing_schema AS schema_name
FROM oauth_client_roles,
  oauth_clients,
  sec_roles,
  ords_schemas
WHERE oauth_clients.id = oauth_client_roles.client_id
AND sec_roles.id       = oauth_client_roles.role_id
AND ords_schemas.id    = oauth_client_roles.schema_id
AND ords_schemas.id    = oauth_clients.schema_id
AND ords_schemas.id    = sec_roles.schema_id 
;





CREATE OR REPLACE VIEW ALL_ORDS_HANDLERS ( ID, TEMPLATE_ID, SOURCE_TYPE, METHOD, MIMES_ALLOWED, ITEMS_PER_PAGE, SOURCE, COMMENTS, SCHEMA_ID, CREATED_BY, CREATED_ON, UPDATED_BY, UPDATED_ON ) AS
WITH sr AS
  (SELECT COUNT(1) has_catalog
  FROM session_roles
  WHERE role = 'SELECT_CATALOG_ROLE'
  )
SELECT ords_handlers.*
FROM ords_handlers,
  ords_schemas,
  sr
WHERE ords_schemas.id            = ords_handlers.schema_id
AND (ords_schemas.parsing_schema = sys_context('USERENV', 'CURRENT_USER')
OR sr.has_catalog                = 1) 
;





CREATE OR REPLACE VIEW ALL_ORDS_MODULES  AS
WITH sr AS
  (SELECT COUNT(1) has_catalog
  FROM session_roles
  WHERE role = 'SELECT_CATALOG_ROLE'
  )
SELECT ords_modules.id,
  ords_modules.name,
  ords_modules.uri_prefix,
  ords_modules.items_per_page,
  ords_modules.status,
  ords_modules.comments,
  ords_modules.schema_id,
  ords_modules.created_by,
  ords_modules.created_on,
  ords_modules.updated_by,
  ords_modules.updated_on,
  (SELECT sec_origins_allowed_modules.origins_allowed
  FROM sec_origins_allowed_modules
  WHERE sec_origins_allowed_modules.module_id = ords_modules.id
  ) origins_allowed
FROM ords_modules,
  ords_schemas,
  sr
WHERE ords_schemas.id            = ords_modules.schema_id
AND (ords_schemas.parsing_schema = sys_context('USERENV', 'CURRENT_USER')
OR sr.has_catalog                = 1) 
;





CREATE OR REPLACE VIEW ALL_ORDS_OBJECTS ( ID, SCHEMA_ID, PARSING_SCHEMA, PARSING_OBJECT, OBJECT_ALIAS, TYPE, STATUS, AUTO_REST_AUTH, OPS_ALLOWED, TYPE_PATH ) AS
with sr as  ( select count(1) has_catalog from session_roles where role = 'SELECT_CATALOG_ROLE' )
SELECT COALESCE(ORDSOBJS.ID, NULL) ID,
  COALESCE(ORDSSCHMID.schema_id, ORDSOBJS.SCHEMA_ID, NULL) SCHEMA_ID,
  REALOBJS.PARSING_SCHEMA PARSING_SCHEMA,
  REALOBJS.PARSING_OBJECT PARSING_OBJECT,
  COALESCE(ORDSOBJS.OBJECT_ALIAS, REALOBJS.OBJECT_ALIAS) OBJECT_ALIAS,
  COALESCE(ORDSOBJS.TYPE, REALOBJS.TYPE) TYPE,
  COALESCE(ORDSOBJS.STATUS, REALOBJS.STATUS) STATUS,
  COALESCE(ORDSOBJS.AUTO_REST_AUTH, REALOBJS.AUTO_REST_AUTH) AUTO_REST_AUTH,
  COALESCE(ORDSOBJS.OPS_ALLOWED, REALOBJS.OPS_ALLOWED) OPS_ALLOWED,
  COALESCE(ORDSOBJS.TYPE_PATH, REALOBJS.TYPE_PATH) TYPE_PATH
FROM
  (SELECT -- sys_context('USERENV', 'CURRENT_USER') PARSING_SCHEMA,
    TV.*,
    'DISABLED' STATUS,
    'ENABLED' AUTO_REST_AUTH,
    NULL OPS_ALLOWED,
    'ENABLED' TYPE_PATH
  FROM
    (SELECT "OWNER" PARSING_SCHEMA,
      TABLE_NAME PARSING_OBJECT,
      LOWER(TABLE_NAME) OBJECT_ALIAS,
      'TABLE' TYPE
    FROM SYS.ALL_TABLES
    WHERE TEMPORARY = 'N'
    AND SECONDARY   = 'N'
    UNION
    SELECT "OWNER" PARSING_SCHEMA,
      VIEW_NAME PARSING_OBJECT,
      LOWER(VIEW_NAME) OBJECT_ALIAS,
      'VIEW' TYPE
    FROM SYS.ALL_VIEWS
    ) TV
  ) REALOBJS
LEFT OUTER JOIN
  (SELECT ords_objects.ID AS ID,
    ords_schemas.id       AS SCHEMA_ID,
    ords_schemas.parsing_schema,
    ords_objects.PARSING_OBJECT,
    ords_objects.OBJECT_ALIAS,
    ords_objects.TYPE,
    ords_objects.STATUS,
    ords_objects.AUTO_REST_AUTH,
    ords_objects.OPS_ALLOWED,
    ords_objects.TYPE_PATH
  FROM ords_objects,
    ords_schemas,
    sr
  WHERE ords_schemas.id           = ords_objects.SCHEMA_ID
  AND (ords_schemas.parsing_schema = sys_context('USERENV', 'CURRENT_USER') or 
             sr.has_catalog = 1)
  ) ORDSOBJS
ON REALOBJS.PARSING_SCHEMA  = ORDSOBJS.parsing_schema
AND REALOBJS.PARSING_OBJECT = ORDSOBJS.PARSING_OBJECT,
  (SELECT s.*
  FROM
    (SELECT NULL schema_id FROM dual
    ) n
  LEFT OUTER JOIN
    (SELECT ords_schemas.id schema_id, ords_schemas.parsing_schema parsing_schema
    FROM ords_schemas,
         sr
    WHERE ords_schemas.parsing_schema = sys_context('USERENV', 'CURRENT_USER') or 
             sr.has_catalog = 1
    ) s
  ON n.schema_id = s.schema_id
  ) ORDSSCHMID 
;





CREATE OR REPLACE VIEW ALL_ORDS_OBJ_MEMBERS ( SCHEMA_ID, OBJECT_ID, PARSING_SCHEMA, PARSING_OBJECT, PARSING_MEMBER, MEMBER_ALIAS, TYPE, VISIBLE, "ORDER", PK_ORDER ) AS
with sr as  ( select count(1) has_catalog from session_roles where role = 'SELECT_CATALOG_ROLE' )
select "SCHEMA_ID","OBJECT_ID","PARSING_SCHEMA","PARSING_OBJECT","PARSING_MEMBER","MEMBER_ALIAS","TYPE","VISIBLE","ORDER","PK_ORDER" from
(
    SELECT
      OO.SCHEMA_ID,
      OO.OBJECT_ID,
      REALMBRS1.PARSING_SCHEMA PARSING_SCHEMA,
      REALMBRS1.PARSING_OBJECT PARSING_OBJECT,
      REALMBRS1.PARSING_MEMBER PARSING_MEMBER,
      LOWER(REALMBRS1.PARSING_MEMBER) MEMBER_ALIAS,
      REALMBRS1."TYPE" "TYPE",
      REALMBRS1."VISIBLE" "VISIBLE",
      REALMBRS1."ORDER" "ORDER",
      REALMBRS1."PK_ORDER" "PK_ORDER"
    FROM 
    (
      select
            PARSING_SCHEMA,
            PARSING_OBJECT,
            PARSING_MEMBER,
            MEMBER_ALIAS,
            "TYPE",
            "VISIBLE",
            COLUMN_ID "ORDER",
            PK_ORDER
      from
      (
        SELECT SYS.ALL_TAB_COLUMNS."OWNER" PARSING_SCHEMA,
            SYS.ALL_TAB_COLUMNS.TABLE_NAME PARSING_OBJECT,
            SYS.ALL_TAB_COLUMNS.COLUMN_NAME PARSING_MEMBER,
            LOWER(SYS.ALL_TAB_COLUMNS.COLUMN_NAME) MEMBER_ALIAS,
            SYS.ALL_TAB_COLUMNS.DATA_TYPE TYPE,
            'ENABLED' VISIBLE,
            SYS.ALL_TAB_COLUMNS.COLUMN_ID
        FROM
          SYS.ALL_TAB_COLUMNS,
          SR
        WHERE
            SYS.ALL_TAB_COLUMNS."OWNER" = sys_context('USERENV', 'CURRENT_USER') OR
            sr.has_catalog = 1
      ) MBRS,
      (
        SELECT
          "ACC"."OWNER" "OWNER",
          "ACC".TABLE_NAME TABLE_NAME,
          "ACC".COLUMN_NAME COLUMN_NAME,
          "ACC"."POSITION" PK_ORDER
        FROM ALL_CONSTRAINTS IAC,
          ALL_CONS_COLUMNS "ACC",
          SR
        WHERE
              IAC."OWNER"         = "ACC"."OWNER"
          AND IAC.CONSTRAINT_NAME = "ACC".CONSTRAINT_NAME
          AND CONSTRAINT_TYPE     = 'P'
          AND ("ACC"."OWNER" = sys_context('USERENV', 'CURRENT_USER') or sr.has_catalog = 1)
      ) PKEY
      WHERE PKEY.OWNER(+)       = MBRS.PARSING_SCHEMA
        AND PKEY.TABLE_NAME(+)  = MBRS.PARSING_OBJECT
        AND PKEY.COLUMN_NAME(+) = MBRS.PARSING_MEMBER
    ) REALMBRS1
    LEFT OUTER JOIN
    (
      SELECT
        ords_schemas.id SCHEMA_ID,
        ords_objects.ID OBJECT_ID,
        ords_schemas.parsing_schema,
        ords_objects.PARSING_OBJECT
      FROM
        ords_objects,
        ords_schemas,
        SR
      WHERE
            ords_objects.SCHEMA_ID    = ords_schemas.id
        AND (parsing_schema = sys_context('USERENV', 'CURRENT_USER') or sr.has_catalog = 1)
    ) OO
    ON
          OO.parsing_schema = REALMBRS1.PARSING_SCHEMA
      AND OO.PARSING_OBJECT  = REALMBRS1.PARSING_OBJECT
--    WHERE REALMBRS1."PARSING_SCHEMA" = TABVIEWS.PARSING_SCHEMA and REALMBRS1.PARSING_OBJECT = TABVIEWS.PARSING_OBJECT
--    
    order by parsing_schema, parsing_object, "ORDER"
) X 
;





CREATE OR REPLACE VIEW ALL_ORDS_PARAMETERS ( ID, HANDLER_ID, NAME, BIND_VARIABLE_NAME, SOURCE_TYPE, ACCESS_METHOD, PARAM_TYPE, COMMENTS, SCHEMA_ID, CREATED_BY, CREATED_ON, UPDATED_BY, UPDATED_ON ) AS
with sr as  ( select count(1) has_catalog from session_roles where role = 'SELECT_CATALOG_ROLE' )
  SELECT ords_parameters."ID",ords_parameters."HANDLER_ID",ords_parameters."NAME",ords_parameters."BIND_VARIABLE_NAME",ords_parameters."SOURCE_TYPE",ords_parameters."ACCESS_METHOD",ords_parameters."PARAM_TYPE",ords_parameters."COMMENTS",ords_parameters."SCHEMA_ID",ords_parameters."CREATED_BY",ords_parameters."CREATED_ON",ords_parameters."UPDATED_BY",ords_parameters."UPDATED_ON"
FROM ords_schemas,
  ords_parameters,
  sr
WHERE ords_schemas.id           = ords_parameters.schema_id
AND (ords_schemas.parsing_schema = sys_context('USERENV', 'CURRENT_USER') or sr.has_catalog = 1) 
;





CREATE OR REPLACE VIEW ALL_ORDS_PENDING_APPROVALS ( PENDING_ID, STATE, PENDING_CREATED_BY, CLIENT_ID, CLIENT_NAME, REDIRECT_URI, RESPONSE_TYPE, APPROVAL_ID, APPROVAL_USER, SCHEMA_ID, APPROVAL_CREATED_BY ) AS
WITH sr AS
  (SELECT COUNT(1) has_catalog
  FROM session_roles
  WHERE role = 'SELECT_CATALOG_ROLE'
  )
SELECT p.id pending_id,
  p.client_state state,
  p.created_by pending_created_by,
  c.id client_id,
  c.name client_name,
  c.redirect_uri redirect_uri,
  c.response_type response_type,
  a.id approval_id,
  a.user_name approval_user,
  a.schema_id schema_id,
  a.created_by approval_created_by
FROM oauth_pending_approvals p,
  oauth_approvals a,
  oauth_clients c,
  ords_schemas s,
  sr
WHERE p.approval_id   = a.id
AND a.schema_id       = s.id
AND a.client_id       = c.id
AND (s.parsing_schema = sys_context('USERENV', 'CURRENT_USER')
OR sr.has_catalog     = 1) 
;





CREATE OR REPLACE VIEW ALL_ORDS_PRIVILEGES ( ID, LABEL, NAME, COMMENTS, CREATED_BY, DESCRIPTION, SCHEMA_ID, CREATED_ON, UPDATED_BY, UPDATED_ON ) AS
with sr as  ( select count(1) has_catalog from session_roles where role = 'SELECT_CATALOG_ROLE' )
  SELECT sec.id,
  sec.label,
  sec.name,
  sec.comments,
  sec.created_by,
  sec.description,
  sec.schema_id,
  sec.created_on,
  sec.updated_by,
  sec.updated_on
FROM ords_schemas sch,
  sec_privileges sec,
  sr
WHERE sec.schema_id   IN (sch.id, 10)
AND (sch.parsing_schema = sys_context('USERENV', 'CURRENT_USER') or sr.has_catalog = 1) 
;





CREATE OR REPLACE VIEW ALL_ORDS_PRIVILEGE_MAPPINGS  AS
SELECT sec_privilege_mappings.privilege_id,
  sec_privileges.name,
  sec_privilege_mappings.pattern,
  sec_privilege_mappings.schema_id,
  sec_privilege_mappings.created_by,
  sec_privilege_mappings.created_on,
  sec_privilege_mappings.updated_by,
  sec_privilege_mappings.updated_on
FROM sec_privilege_mappings,
  sec_privileges
WHERE sec_privileges.id = sec_privilege_mappings.privilege_id 
;





CREATE OR REPLACE VIEW ALL_ORDS_PRIVILEGE_MODULES ( MODULE_ID, MODULE_NAME, PRIVILEGE_ID, PRIVILEGE_NAME ) AS
SELECT sec_privilege_modules.module_id,
  ALL_ORDS_MODULES.name AS module_name,
  sec_privilege_modules.privilege_id,
  ALL_ORDS_PRIVILEGES.name AS privilege_name
FROM ALL_ORDS_PRIVILEGES,
  ALL_ORDS_MODULES,
  sec_privilege_modules
WHERE sec_privilege_modules.module_id  = ALL_ORDS_MODULES.id
AND sec_privilege_modules.privilege_id = ALL_ORDS_PRIVILEGES.id 
;





CREATE OR REPLACE VIEW ALL_ORDS_ROLES ( ID, NAME, CREATED_BY, CREATED_ON, UPDATED_BY, UPDATED_ON, SCHEMA_ID ) AS
with sr as  ( select count(1) has_catalog from session_roles where role = 'SELECT_CATALOG_ROLE' )
  SELECT sec_roles.id,
  sec_roles.name,
  sec_roles.created_by,
  sec_roles.created_on,
  sec_roles.updated_by,
  sec_roles.updated_on,
  sec_roles.schema_id
FROM sec_roles,
  ords_schemas,
  sr
WHERE sec_roles.schema_id      IN (ords_schemas.id, 10)
AND (ords_schemas.parsing_schema = sys_context('USERENV', 'CURRENT_USER') or sr.has_catalog = 1) 
;





CREATE OR REPLACE VIEW ALL_ORDS_PRIVILEGE_ROLES ( PRIVILEGE_ID, PRIVILEGE_NAME, ROLE_ID, ROLE_NAME ) AS
SELECT sec_privilege_roles.privilege_id,
  ALL_ORDS_PRIVILEGES.name AS privilege_name,
  sec_privilege_roles.role_id,
  ALL_ORDS_ROLES.name AS role_name
FROM sec_privilege_roles,
  ALL_ORDS_ROLES,
  ALL_ORDS_PRIVILEGES
WHERE sec_privilege_roles.role_id    = ALL_ORDS_ROLES.id
AND sec_privilege_roles.privilege_id = ALL_ORDS_PRIVILEGES.id 
;





CREATE OR REPLACE VIEW ALL_ORDS_SCHEMAS ( ID, PARSING_SCHEMA, TYPE, PATTERN, STATUS, AUTO_REST_AUTH, OPS_ALLOWED ) AS
with sr as  ( select count(1) has_catalog from session_roles where role = 'SELECT_CATALOG_ROLE' )
 SELECT ords_schemas.id,
  ords_schemas.parsing_schema,
  ords_url_mappings.type,
  ords_url_mappings.pattern,
  ords_schemas.status,
  ords_schemas.auto_rest_auth,
  ords_schemas.ops_allowed
FROM ords_url_mappings,
  ords_schemas,
  sr
WHERE ords_url_mappings.id = ords_schemas.url_mapping_id
AND ( ords_schemas.parsing_schema = sys_context('USERENV', 'CURRENT_USER') or  sr.has_catalog = 1 ) 
;





CREATE OR REPLACE VIEW ALL_ORDS_SERVICES  AS
SELECT s.parsing_schema owner,
  h.method,
  m.uri_prefix base_path,
  t.uri_template pattern,
  t.priority,
  m.name,
  m.status,
  p.name privilege_name,
  NVL(h.items_per_page, m.items_per_page) items_per_page,
  m.id module_id,
  t.id template_id,
  h.id handler_id,
  h.source_type,
  h.source
FROM ords_handlers h
INNER JOIN ords_templates t
ON h.template_id = t.id
AND h.schema_id  = t.schema_id
INNER JOIN ords_modules m
ON t.module_id  = m.id
AND t.schema_id = m.schema_id
INNER JOIN ords_schemas s
ON m.schema_id = s.id
LEFT JOIN sec_privilege_modules pm
ON m.id = pm.module_id
LEFT JOIN sec_privileges p
ON pm.privilege_id = p.id
ORDER BY owner,
  t.priority DESC,
  base_path,
  pattern 
;





CREATE OR REPLACE VIEW ALL_ORDS_TEMPLATES ( ID, MODULE_ID, URI_TEMPLATE, PRIORITY, ETAG_TYPE, ETAG_QUERY, COMMENTS, SCHEMA_ID, CREATED_BY, CREATED_ON, UPDATED_BY, UPDATED_ON ) AS
with sr as  ( select count(1) has_catalog from session_roles where role = 'SELECT_CATALOG_ROLE' )
  SELECT ords_templates."ID",ords_templates."MODULE_ID",ords_templates."URI_TEMPLATE",ords_templates."PRIORITY",ords_templates."ETAG_TYPE",ords_templates."ETAG_QUERY",ords_templates."COMMENTS",ords_templates."SCHEMA_ID",ords_templates."CREATED_BY",ords_templates."CREATED_ON",ords_templates."UPDATED_BY",ords_templates."UPDATED_ON"
FROM ords_templates,
  ords_schemas,
  sr
WHERE ords_schemas.id           = ords_templates.schema_id
AND (ords_schemas.parsing_schema = sys_context('USERENV', 'CURRENT_USER') or sr.has_catalog = 1) 
;





CREATE OR REPLACE VIEW ORDS_REPVERSION  AS
SELECT 2 REVISION FROM DUAL 
;





CREATE OR REPLACE VIEW POOL_CONFIG  AS
SELECT NULL                    AS pool_name,
  ords_schemas.parsing_schema  AS workspace_identifier,
  ords_url_mappings.type       AS type,
  ords_url_mappings.pattern    AS uri,
  ords_url_mappings.updated_on AS updated,
  sec_keys.enc_key             AS enc_key,
  sec_keys.mac_key             AS mac_key
FROM ords_schemas,
  ords_url_mappings,
  sec_keys
WHERE ords_url_mappings.id  = ords_schemas.url_mapping_id
AND ords_schemas.id         = sec_keys.schema_id
AND ords_url_mappings.type <> 'NONE'
AND ords_schemas.status     = 'ENABLED'
ORDER BY updated DESC 
;





CREATE OR REPLACE VIEW USER_ORDS_APPROVALS  AS
SELECT c.name title,
  c.description description,
  c.id icon_id,
  c.support_email,
  c.support_uri,
  a.updated_on updated_on,
  a.status status,
  a.id approval_id,
  a.user_name,
  s.parsing_schema
FROM oauth_clients c,
  oauth_approvals a,
  ords_schemas s
WHERE c.id           = a.client_id
AND a.schema_id      = s.id
AND s.parsing_schema = sys_context('USERENV', 'CURRENT_USER') 
;





CREATE OR REPLACE VIEW USER_ORDS_CLIENTS  AS
SELECT c.id,
  c.name,
  c.description,
  c.auth_flow,
  c.apex_application_id,
  c.response_type,
  c.client_id,
  c.client_secret,
  c.redirect_uri,
  c.support_email,
  c.support_uri,
  c.allowed_origins,
  c.about_url,
  c.logo_content_type,
  c.logo_image,
  c.schema_id,
  c.created_by,
  c.created_on,
  c.updated_by,
  c.updated_on
FROM oauth_clients c,
  ords_schemas s
WHERE c.schema_id    = s.id
AND s.parsing_schema = sys_context('USERENV', 'CURRENT_USER') 
;





CREATE OR REPLACE VIEW USER_ORDS_CLIENT_PRIVILEGES  AS
SELECT p.id client_priv_id,
  p.created_by client_priv_created_by,
  p.updated_by client_priv_updated_by,
  p.client_id client_priv_client_id,
  sec.id priv_id,
  sec.name,
  sec.label,
  sec.description,
  sch.parsing_schema,
  c.name client_name,
  c.created_by client_created_by
FROM ords_schemas sch,
  sec_privileges sec,
  oauth_client_privileges p,
  oauth_clients c
WHERE sch.id           = p.schema_id
AND sch.id             = c.schema_id
AND sec.id             = p.privilege_id
AND p.client_id        = c.id
AND sch.parsing_schema = sys_context('USERENV', 'CURRENT_USER') 
;





CREATE OR REPLACE VIEW USER_ORDS_CLIENT_ROLES  AS
SELECT ALL_ORDS_CLIENT_ROLES.client_id,
  ALL_ORDS_CLIENT_ROLES.client_name,
  ALL_ORDS_CLIENT_ROLES.role_id,
  ALL_ORDS_CLIENT_ROLES.role_name
FROM ALL_ORDS_CLIENT_ROLES,
  ords_schemas
WHERE ords_schemas.id           = ALL_ORDS_CLIENT_ROLES.schema_id
AND ords_schemas.parsing_schema = sys_context('USERENV', 'CURRENT_USER') 
;





CREATE OR REPLACE VIEW USER_ORDS_ENABLED_OBJECTS ( ID, SCHEMA_ID, PARSING_SCHEMA, PARSING_OBJECT, OBJECT_ALIAS, TYPE, STATUS, AUTO_REST_AUTH, OPS_ALLOWED, TYPE_PATH ) AS
SELECT ords_objects.ID AS ID,
  ords_schemas.id      AS SCHEMA_ID,
  ords_schemas.parsing_schema,
  ords_objects.PARSING_OBJECT,
  ords_objects.OBJECT_ALIAS,
  ords_objects.TYPE,
  ords_objects.STATUS,
  ords_objects.AUTO_REST_AUTH,
  ords_objects.OPS_ALLOWED,
  ords_objects.TYPE_PATH
FROM ords_objects,
  ords_schemas
WHERE ords_schemas.id           = ords_objects.SCHEMA_ID
AND ords_schemas.parsing_schema = sys_context('USERENV', 'CURRENT_USER')
AND ords_objects.STATUS         = 'ENABLED'
AND ords_schemas.status         = 'ENABLED' 
;





CREATE OR REPLACE VIEW USER_ORDS_HANDLERS  AS
SELECT ords_handlers.*
FROM ords_handlers,
  ords_schemas
WHERE ords_schemas.id           = ords_handlers.schema_id
AND ords_schemas.parsing_schema = sys_context('USERENV', 'CURRENT_USER') 
;





CREATE OR REPLACE VIEW USER_ORDS_MODULES  AS
SELECT ords_modules.*,
  (SELECT sec_origins_allowed_modules.origins_allowed
  FROM sec_origins_allowed_modules
  WHERE sec_origins_allowed_modules.module_id = ords_modules.id
  ) origins_allowed
FROM ords_modules,
  ords_schemas
WHERE ords_schemas.id           = ords_modules.schema_id
AND ords_schemas.parsing_schema = sys_context('USERENV', 'CURRENT_USER') 
;





CREATE OR REPLACE VIEW USER_ORDS_OBJECTS ( ID, SCHEMA_ID, PARSING_SCHEMA, PARSING_OBJECT, OBJECT_ALIAS, TYPE, STATUS, AUTO_REST_AUTH, OPS_ALLOWED, TYPE_PATH ) AS
SELECT COALESCE(ORDSOBJS.ID, NULL) ID,
  COALESCE(ORDSSCHMID.schema_id, ORDSOBJS.SCHEMA_ID, NULL) SCHEMA_ID,
  REALOBJS.PARSING_SCHEMA PARSING_SCHEMA,
  REALOBJS.PARSING_OBJECT PARSING_OBJECT,
  COALESCE(ORDSOBJS.OBJECT_ALIAS, REALOBJS.OBJECT_ALIAS) OBJECT_ALIAS,
  COALESCE(ORDSOBJS.TYPE, REALOBJS.TYPE) TYPE,
  COALESCE(ORDSOBJS.STATUS, REALOBJS.STATUS) STATUS,
  COALESCE(ORDSOBJS.AUTO_REST_AUTH, REALOBJS.AUTO_REST_AUTH) AUTO_REST_AUTH,
  COALESCE(ORDSOBJS.OPS_ALLOWED, REALOBJS.OPS_ALLOWED) OPS_ALLOWED,
  COALESCE(ORDSOBJS.TYPE_PATH, REALOBJS.TYPE_PATH) TYPE_PATH
FROM
  (SELECT sys_context('USERENV', 'CURRENT_USER') PARSING_SCHEMA,
    TV.*,
    'DISABLED' STATUS,
    'ENABLED' AUTO_REST_AUTH,
    NULL OPS_ALLOWED,
    'ENABLED' TYPE_PATH
  FROM
    (SELECT TABLE_NAME PARSING_OBJECT,
      LOWER(TABLE_NAME) OBJECT_ALIAS,
      'TABLE' TYPE
    FROM SYS.USER_TABLES
    WHERE TEMPORARY = 'N'
    AND SECONDARY   = 'N'
    UNION
    SELECT VIEW_NAME PARSING_OBJECT,
      LOWER(VIEW_NAME) OBJECT_ALIAS,
      'VIEW' TYPE
    FROM SYS.USER_VIEWS
    ) TV
  ) REALOBJS
LEFT OUTER JOIN
  (SELECT ords_objects.ID AS ID,
    ords_schemas.id       AS SCHEMA_ID,
    ords_schemas.parsing_schema,
    ords_objects.PARSING_OBJECT,
    ords_objects.OBJECT_ALIAS,
    ords_objects.TYPE,
    ords_objects.STATUS,
    ords_objects.AUTO_REST_AUTH,
    ords_objects.OPS_ALLOWED,
    ords_objects.TYPE_PATH
  FROM ords_objects,
    ords_schemas
  WHERE ords_schemas.id           = ords_objects.SCHEMA_ID
  AND ords_schemas.parsing_schema = sys_context('USERENV', 'CURRENT_USER')
  ) ORDSOBJS
ON REALOBJS.PARSING_SCHEMA  = ORDSOBJS.parsing_schema
AND REALOBJS.PARSING_OBJECT = ORDSOBJS.PARSING_OBJECT,
  (SELECT s.*
  FROM
    (SELECT NULL schema_id FROM dual
    ) n
  LEFT OUTER JOIN
    (SELECT ords_schemas.id schema_id
    FROM ords_schemas
    WHERE ords_schemas.parsing_schema = sys_context('USERENV', 'CURRENT_USER')
    ) s
  ON n.schema_id = s.schema_id
  ) ORDSSCHMID 
;





CREATE OR REPLACE VIEW USER_ORDS_OBJ_MEMBERS ( SCHEMA_ID, OBJECT_ID, ID, PARSING_SCHEMA, PARSING_OBJECT, PARSING_MEMBER, MEMBER_ALIAS, TYPE, VISIBLE, "ORDER", PK_ORDER ) AS
SELECT MBRS.SCHEMA_ID SCHEMA_ID,
  MBRS.OBJECT_ID OBJECT_ID,
  MBRS.ID ID,
  MBRS.PARSING_SCHEMA PARSING_SCHEMA,
  MBRS.PARSING_OBJECT PARSING_OBJECT,
  MBRS.PARSING_MEMBER PARSING_MEMBER,
  MBRS.MEMBER_ALIAS MEMBER_ALIAS,
  MBRS.TYPE TYPE,
  MBRS.VISIBLE VISIBLE,
  MBRS."ORDER" "ORDER",
  PKEY.PK_ORDER PK_ORDER
FROM
  (SELECT COALESCE(REALMBRS.SCHEMA_ID, ORDSMBRS.SCHEMA_ID) SCHEMA_ID,
    COALESCE(REALMBRS.OBJECT_ID, ORDSMBRS.OBJECT_ID) OBJECT_ID,
    NULL ID,
    REALMBRS.PARSING_SCHEMA PARSING_SCHEMA,
    REALMBRS.PARSING_OBJECT PARSING_OBJECT,
    REALMBRS.PARSING_MEMBER PARSING_MEMBER,
    REALMBRS.MEMBER_ALIAS MEMBER_ALIAS,
    REALMBRS.TYPE TYPE,
    REALMBRS.VISIBLE VISIBLE,
    REALMBRS."ORDER" "ORDER"
  FROM
    (SELECT TABVIEWS.SCHEMA_ID,
      TABVIEWS.OBJECT_ID,
      sys_context('USERENV', 'CURRENT_USER') PARSING_SCHEMA,
      SYS.USER_TAB_COLUMNS.TABLE_NAME PARSING_OBJECT,
      SYS.USER_TAB_COLUMNS.COLUMN_NAME PARSING_MEMBER,
      LOWER(SYS.USER_TAB_COLUMNS.COLUMN_NAME) MEMBER_ALIAS,
      SYS.USER_TAB_COLUMNS.DATA_TYPE TYPE,
      'ENABLED' VISIBLE,
      SYS.USER_TAB_COLUMNS.COLUMN_ID "ORDER"
    FROM SYS.USER_TAB_COLUMNS,
      (SELECT OO.SCHEMA_ID,
        OO.OBJECT_ID,
        TVS.PARSING_OBJECT
      FROM
        (SELECT sys_context('USERENV', 'CURRENT_USER') PARSING_SCHEMA,
          TABLE_NAME PARSING_OBJECT
        FROM SYS.USER_TABLES
        WHERE TEMPORARY = 'N'
        AND SECONDARY   = 'N'
        UNION
        SELECT sys_context('USERENV', 'CURRENT_USER') PARSING_SCHEMA,
          VIEW_NAME PARSING_OBJECT
        FROM SYS.USER_VIEWS
        ) TVS,
        (SELECT ords_schemas.id SCHEMA_ID,
          ords_objects.ID OBJECT_ID,
          ords_schemas.parsing_schema,
          ords_objects.PARSING_OBJECT
        FROM ords_objects,
          ords_schemas
        WHERE ords_objects.SCHEMA_ID    = ords_schemas.id
        AND ords_schemas.parsing_schema = sys_context('USERENV', 'CURRENT_USER')
        ) OO
      WHERE OO.parsing_schema = TVS.PARSING_SCHEMA
      AND OO.PARSING_OBJECT   = TVS.PARSING_OBJECT
      ) TABVIEWS
    WHERE SYS.USER_TAB_COLUMNS.TABLE_NAME = TABVIEWS.PARSING_OBJECT
    ) REALMBRS,
    (SELECT ords_schemas.id SCHEMA_ID,
      ords_objects.ID OBJECT_ID,
      ords_schemas.parsing_schema PARSING_SCHEMA,
      ords_objects.PARSING_OBJECT PARSING_OBJECT
    FROM ords_objects,
      ords_schemas
    WHERE ords_objects.SCHEMA_ID    = ords_schemas.id
    AND ords_schemas.parsing_schema = sys_context('USERENV', 'CURRENT_USER')
    ) ORDSMBRS
  WHERE REALMBRS.PARSING_OBJECT = ORDSMBRS.PARSING_OBJECT(+)
  ) MBRS,
  (SELECT ICC.OWNER OWNER,
    ICC.TABLE_NAME TABLE_NAME,
    ICC.COLUMN_NAME COLUMN_NAME,
    ICC.POSITION PK_ORDER
  FROM USER_CONSTRAINTS IUC,
    USER_CONS_COLUMNS ICC
  WHERE IUC.OWNER         = ICC.OWNER
  AND IUC.CONSTRAINT_NAME = ICC.CONSTRAINT_NAME
  AND CONSTRAINT_TYPE     = 'P'
  ) PKEY
WHERE PKEY.OWNER(+)     = MBRS.PARSING_SCHEMA
AND PKEY.TABLE_NAME(+)  = MBRS.PARSING_OBJECT
AND PKEY.COLUMN_NAME(+) = MBRS.PARSING_MEMBER
ORDER BY PARSING_OBJECT,
  "ORDER" 
;





CREATE OR REPLACE VIEW USER_ORDS_PARAMETERS  AS
SELECT ords_parameters.*
FROM ords_schemas,
  ords_parameters
WHERE ords_schemas.id           = ords_parameters.schema_id
AND ords_schemas.parsing_schema = sys_context('USERENV', 'CURRENT_USER') 
;





CREATE OR REPLACE VIEW USER_ORDS_PENDING_APPROVALS  AS
SELECT p.id pending_id,
  p.client_state state,
  p.created_by pending_created_by,
  c.id client_id,
  c.name client_name,
  c.redirect_uri redirect_uri,
  c.response_type response_type,
  a.id approval_id,
  a.user_name approval_user,
  a.created_by approval_created_by
FROM oauth_pending_approvals p,
  oauth_approvals a,
  oauth_clients c,
  ords_schemas s
WHERE p.approval_id  = a.id
AND a.schema_id      = s.id
AND a.client_id      = c.id
AND s.parsing_schema = sys_context('USERENV', 'CURRENT_USER') 
;





CREATE OR REPLACE VIEW USER_ORDS_PRIVILEGES  AS
SELECT sec.id,
  sec.label,
  sec.name,
  sec.comments,
  sec.created_by,
  sec.description,
  sec.schema_id,
  sec.created_on,
  sec.updated_by,
  sec.updated_on
FROM ords_schemas sch,
  sec_privileges sec
WHERE sec.schema_id   IN (sch.id, 10)
AND sch.parsing_schema = sys_context('USERENV', 'CURRENT_USER') 
;





CREATE OR REPLACE VIEW USER_ORDS_PRIVILEGE_MAPPINGS  AS
SELECT ALL_ORDS_PRIVILEGE_MAPPINGS.privilege_id,
  ALL_ORDS_PRIVILEGE_MAPPINGS.name,
  ALL_ORDS_PRIVILEGE_MAPPINGS.pattern,
  ALL_ORDS_PRIVILEGE_MAPPINGS.created_by,
  ALL_ORDS_PRIVILEGE_MAPPINGS.created_on,
  ALL_ORDS_PRIVILEGE_MAPPINGS.updated_by,
  ALL_ORDS_PRIVILEGE_MAPPINGS.updated_on
FROM ALL_ORDS_PRIVILEGE_MAPPINGS,
  ords_schemas
WHERE ords_schemas.id           = ALL_ORDS_PRIVILEGE_MAPPINGS.schema_id
AND ords_schemas.parsing_schema = sys_context('USERENV', 'CURRENT_USER') 
;





CREATE OR REPLACE VIEW USER_ORDS_PRIVILEGE_MODULES  AS
SELECT sec_privilege_modules.module_id,
  USER_ORDS_MODULES.name AS module_name,
  sec_privilege_modules.privilege_id,
  USER_ORDS_PRIVILEGES.name AS privilege_name
FROM USER_ORDS_PRIVILEGES,
  USER_ORDS_MODULES,
  sec_privilege_modules
WHERE sec_privilege_modules.module_id  = USER_ORDS_MODULES.id
AND sec_privilege_modules.privilege_id = USER_ORDS_PRIVILEGES.id 
;





CREATE OR REPLACE VIEW USER_ORDS_ROLES  AS
SELECT sec_roles.id,
  sec_roles.name,
  sec_roles.created_by,
  sec_roles.created_on,
  sec_roles.updated_by,
  sec_roles.updated_on,
  sec_roles.schema_id
FROM sec_roles,
  ords_schemas
WHERE sec_roles.schema_id      IN (ords_schemas.id, 10)
AND ords_schemas.parsing_schema = sys_context('USERENV', 'CURRENT_USER') 
;





CREATE OR REPLACE VIEW USER_ORDS_PRIVILEGE_ROLES  AS
SELECT sec_privilege_roles.privilege_id,
  USER_ORDS_PRIVILEGES.name AS privilege_name,
  sec_privilege_roles.role_id,
  USER_ORDS_ROLES.name AS role_name
FROM sec_privilege_roles,
  USER_ORDS_ROLES,
  USER_ORDS_PRIVILEGES
WHERE sec_privilege_roles.role_id    = USER_ORDS_ROLES.id
AND sec_privilege_roles.privilege_id = USER_ORDS_PRIVILEGES.id 
;





CREATE OR REPLACE VIEW USER_ORDS_SCHEMAS ( ID, PARSING_SCHEMA, TYPE, PATTERN, STATUS, AUTO_REST_AUTH, OPS_ALLOWED ) AS
SELECT ords_schemas.id,
  ords_schemas.parsing_schema,
  ords_url_mappings.type,
  ords_url_mappings.pattern,
  ords_schemas.status,
  ords_schemas.auto_rest_auth,
  ords_schemas.ops_allowed
FROM ords_url_mappings,
  ords_schemas
WHERE ords_url_mappings.id      = ords_schemas.url_mapping_id
AND ords_schemas.parsing_schema = sys_context('USERENV', 'CURRENT_USER') 
;





CREATE OR REPLACE VIEW USER_ORDS_SERVICES  AS
SELECT ALL_ORDS_SERVICES.method,
  ALL_ORDS_SERVICES.base_path,
  ALL_ORDS_SERVICES.pattern,
  ALL_ORDS_SERVICES.priority,
  ALL_ORDS_SERVICES.name,
  ALL_ORDS_SERVICES.status,
  ALL_ORDS_SERVICES.privilege_name,
  ALL_ORDS_SERVICES.items_per_page,
  ALL_ORDS_SERVICES.module_id,
  ALL_ORDS_SERVICES.template_id,
  ALL_ORDS_SERVICES.handler_id,
  ALL_ORDS_SERVICES.source_type,
  ALL_ORDS_SERVICES.source
FROM ALL_ORDS_SERVICES
WHERE ALL_ORDS_SERVICES.owner = sys_context('USERENV', 'CURRENT_USER')
UNION ALL
SELECT *
FROM
  (WITH operations AS
  (SELECT 'COLLECTION' op FROM dual
  UNION ALL
  SELECT 'ITEM' FROM dual
  UNION ALL
  SELECT 'CREATE' FROM dual
  UNION ALL
  SELECT 'UPDATE' FROM dual
  UNION ALL
  SELECT 'DELETE' FROM dual
  UNION ALL
  SELECT 'DELFILTER' FROM dual
  UNION ALL
  SELECT 'BATCHLOAD' FROM dual
  )
SELECT
  CASE
    WHEN op.op = 'CREATE'
    THEN 'POST'
    WHEN op.op = 'UPDATE'
    THEN 'PUT'
    WHEN op.op = 'DELETE'
    THEN 'DELETE'
    WHEN op.op = 'DELFILTER'
    THEN 'DELETE'
    WHEN op.op = 'BATCHLOAD'
    THEN 'POST'
    ELSE 'GET'
  END method,
  '/'
  || o.OBJECT_ALIAS
  || '/' base_path,
  CASE
    WHEN op.op = 'COLLECTION'
    THEN '.'
    WHEN op.op = 'CREATE'
    THEN '.'
    WHEN op.op = 'DELFILTER'
    THEN '.'
    WHEN op.op = 'BATCHLOAD'
    THEN 'batchload'
    ELSE ':id'
  END pattern,
  0 priority,
  o.PARSING_OBJECT name,
  CASE
    WHEN o.STATUS = 'DISABLED'
    THEN 'NOT_PUBLISHED'
    ELSE 'PUBLISHED'
  END status,
  NULL privilege_name,
  CASE
    WHEN op.op = 'COLLECTION'
    THEN 25
    ELSE 0
  END items_per_page,
  o.ID module_id,
  CASE
    WHEN op.op = 'COLLECTION'
    THEN 0
    WHEN op.op = 'CREATE'
    THEN 0
    WHEN op.op = 'DELFILTER'
    THEN 0
    WHEN op.op = 'BATCHLOAD'
    THEN 1
    ELSE 2
  END template_id,
  o.ID handler_id,
  CASE
    WHEN op.op = 'COLLECTION'
    THEN 'object/collection'
    WHEN op.op = 'ITEM'
    THEN 'object/item'
    WHEN op.op = 'CREATE'
    THEN 'object/action;type=create'
    WHEN op.op = 'UPDATE'
    THEN 'object/action;type=update'
    WHEN op.op = 'DELETE'
    THEN 'object/action;type=delete'
    WHEN op.op = 'DELFILTER'
    THEN 'object/action;type=delete_filter'
    WHEN op.op = 'BATCHLOAD'
    THEN 'object/action;type=batch'
  END source_type,
  to_clob(o.PARSING_OBJECT) source
FROM ords_schemas s,
  ords_objects o,
  operations op
WHERE o.SCHEMA_ID    = s.id
AND s.parsing_schema = sys_context('USERENV', 'CURRENT_USER')
  )
ORDER BY module_id,
  template_id,
  handler_id 
;





CREATE OR REPLACE VIEW USER_ORDS_TEMPLATES  AS
SELECT ords_templates.*
FROM ords_templates,
  ords_schemas
WHERE ords_schemas.id           = ords_templates.schema_id
AND ords_schemas.parsing_schema = sys_context('USERENV', 'CURRENT_USER') 
;






-- Oracle SQL Developer Data Modeler Summary Report: 
-- 
-- CREATE TABLE                            26
-- CREATE INDEX                            39
-- ALTER TABLE                            105
-- CREATE VIEW                             37
-- ALTER VIEW                               0
-- CREATE PACKAGE                           0
-- CREATE PACKAGE BODY                      0
-- CREATE PROCEDURE                         0
-- CREATE FUNCTION                          0
-- CREATE TRIGGER                           0
-- ALTER TRIGGER                            0
-- CREATE COLLECTION TYPE                   0
-- CREATE STRUCTURED TYPE                   0
-- CREATE STRUCTURED TYPE BODY              0
-- CREATE CLUSTER                           0
-- CREATE CONTEXT                           0
-- CREATE DATABASE                          0
-- CREATE DIMENSION                         0
-- CREATE DIRECTORY                         0
-- CREATE DISK GROUP                        0
-- CREATE ROLE                              0
-- CREATE ROLLBACK SEGMENT                  0
-- CREATE SEQUENCE                          0
-- CREATE MATERIALIZED VIEW                 0
-- CREATE SYNONYM                           0
-- CREATE TABLESPACE                        0
-- CREATE USER                              0
-- 
-- DROP TABLESPACE                          0
-- DROP DATABASE                            0
-- 
-- REDACTION POLICY                         0
-- 
-- ORDS DROP SCHEMA                         0
-- ORDS ENABLE SCHEMA                       0
-- ORDS ENABLE OBJECT                       0
-- 
-- ERRORS                                   0
-- WARNINGS                                 1
