CREATE OR REPLACE PACKAGE ords_services_internal
IS
  invalid_base_message VARCHAR2(100) := 'URI Prefix cannot be null.';
  invalid_base_code NUMBER := -20036;
  
  invalid_pattern_message VARCHAR2(100) := 'Pattern cannot be null.';
  invalid_pattern_code NUMBER := -20037;

  FUNCTION normalize_uri_prefix(p_uri_prefix varchar2) return ords_modules.uri_prefix%type;
  
-- Modules

  FUNCTION create_module(
      p_schema_id      IN NUMBER,
      p_name           IN ords_modules.name%type,
      p_uri_prefix     IN ords_modules.uri_prefix%type,
      p_items_per_page IN ords_modules.items_per_page%type,
      p_status         IN ords_modules.status%type,
      p_comments       IN ords_modules.comments%type,
      p_created_on     IN ords_modules.created_on%type DEFAULT NULL,
      p_created_by     IN ords_modules.created_by%type DEFAULT NULL,
      p_updated_on     IN ords_modules.updated_on%type DEFAULT NULL,
      p_updated_by     IN ords_modules.updated_by%type DEFAULT NULL)
    RETURN ords_modules.id%type;

  PROCEDURE update_module(
      p_schema_id      IN NUMBER,
      p_id             IN ords_modules.id%type,
      p_name           IN ords_modules.name%type ,
      p_uri_prefix     IN ords_modules.uri_prefix%type,
      p_items_per_page IN ords_modules.items_per_page%type,
      p_status         IN ords_modules.status%type ,
      p_comments       IN ords_modules.comments%type,
      p_updated_on     IN ords_modules.updated_on%type DEFAULT NULL,
      p_updated_by     IN ords_modules.updated_by%type DEFAULT NULL,
      p_created_on     IN ords_modules.created_on%type DEFAULT NULL,
      p_created_by     IN ords_modules.created_by%type DEFAULT NULL);

  PROCEDURE update_module(
      p_schema_id      IN NUMBER,
      p_id             IN ords_modules.id%type,
      p_name           IN ords_modules.name%type ,
      p_uri_prefix     IN ords_modules.uri_prefix%type,
      p_items_per_page IN ords_modules.items_per_page%type,
      p_status         IN ords_modules.status%type,
      p_updated_on     IN ords_modules.updated_on%type DEFAULT NULL,
      p_updated_by     IN ords_modules.updated_by%type DEFAULT NULL,
      p_created_on     IN ords_modules.created_on%type DEFAULT NULL,
      p_created_by     IN ords_modules.created_by%type DEFAULT NULL);

  PROCEDURE publish_module(
      p_schema_id      IN NUMBER,
      p_id             IN ords_modules.id%type,
      p_status         IN ords_modules.status%type,
      p_updated_on     IN ords_modules.updated_on%type DEFAULT NULL,
      p_updated_by     IN ords_modules.updated_by%type DEFAULT NULL,
      p_created_on     IN ords_modules.created_on%type DEFAULT NULL,
      p_created_by     IN ords_modules.created_by%type DEFAULT NULL);

  PROCEDURE rename_module(
      p_schema_id      IN NUMBER,
      p_id             IN ords_modules.id%type,
      p_name           IN ords_modules.name%type,
      p_uri_prefix     IN ords_modules.uri_prefix%type,
      p_updated_on     IN ords_modules.updated_on%type DEFAULT NULL,
      p_updated_by     IN ords_modules.updated_by%type DEFAULT NULL,
      p_created_on     IN ords_modules.created_on%type DEFAULT NULL,
      p_created_by     IN ords_modules.created_by%type DEFAULT NULL);

  PROCEDURE delete_module(
      p_schema_id IN NUMBER,
      p_name      IN ords_modules.name%type);

  PROCEDURE delete_module(
      p_schema_id IN NUMBER,
      p_id        IN ords_modules.id%type);

  PROCEDURE delete_modules(
      p_schema_id IN NUMBER);

-- Templates

  PROCEDURE delete_templates(
      p_schema_id IN NUMBER,
      p_module_id IN ords_modules.id%type);

  PROCEDURE delete_template(
      p_schema_id IN NUMBER,
      p_id        IN ords_templates.id%type);

  FUNCTION add_template(
      p_schema_id    IN NUMBER,
      p_module_id    IN ords_templates.module_id%type,
      p_uri_template IN ords_templates.uri_template%type,
      p_priority     IN ords_templates.priority%type,
      p_etag_type    IN ords_templates.etag_type%type,
      p_etag_query   IN ords_templates.etag_query%type,
      p_comments     IN ords_templates.comments%type,
      p_created_on   IN ords_templates.created_on%type DEFAULT NULL,
      p_created_by   IN ords_templates.created_by%type DEFAULT NULL,
      p_updated_on   IN ords_templates.updated_on%type DEFAULT NULL,
      p_updated_by   IN ords_templates.updated_by%type DEFAULT NULL)
    RETURN ords_templates.id%type;

  PROCEDURE update_template(
      p_schema_id    IN NUMBER,
      p_id           IN ords_templates.id%type,
      p_uri_template IN ords_templates.uri_template%type,
      p_priority     IN ords_templates.priority%type,
      p_etag_type    IN ords_templates.etag_type%type,
      p_etag_query   IN ords_templates.etag_query%type,
      p_comments     IN ords_templates.comments%type,
      p_updated_on   IN ords_templates.updated_on%type DEFAULT NULL,
      p_updated_by   IN ords_templates.updated_by%type DEFAULT NULL,
      p_created_on   IN ords_templates.created_on%type DEFAULT NULL,
      p_created_by   IN ords_templates.created_by%type DEFAULT NULL);

  PROCEDURE change_template_uri(
      p_schema_id    IN NUMBER,
      p_id           IN ords_templates.id%type,
      p_uri_template IN ords_templates.uri_template%type,
      p_updated_on   IN ords_templates.updated_on%type DEFAULT NULL,
      p_updated_by   IN ords_templates.updated_by%type DEFAULT NULL,
      p_created_on   IN ords_templates.created_on%type DEFAULT NULL,
      p_created_by   IN ords_templates.created_by%type DEFAULT NULL);

-- Handlers

  PROCEDURE delete_handlers(
      p_schema_id    IN NUMBER,
      p_template_id  IN ords_handlers.template_id%type);

  PROCEDURE delete_handler(
      p_schema_id   IN NUMBER,
      p_id          IN ords_handlers.id%type);

  FUNCTION add_handler(
      p_schema_id      IN NUMBER,
      p_template_id    IN ords_handlers.template_id%type ,
      p_source_type    IN ords_handlers.source_type%type,
      p_source         IN ords_handlers.source%type ,
      p_method         IN ords_handlers.method%type,
      p_items_per_page IN ords_handlers.items_per_page%type,
      p_mimes_allowed  IN ords_handlers.mimes_allowed%type,
      p_comments       IN ords_handlers.comments%type,
      p_created_on     IN ords_handlers.created_on%type DEFAULT NULL,
      p_created_by     IN ords_handlers.created_by%type DEFAULT NULL,
      p_updated_on     IN ords_handlers.updated_on%type DEFAULT NULL,
      p_updated_by     IN ords_handlers.updated_by%type DEFAULT NULL)
    RETURN ords_handlers.id%type;

  PROCEDURE update_handler
    (
      p_schema_id      IN NUMBER,
      p_id             IN ords_handlers.template_id%type ,
      p_source_type    IN ords_handlers.source_type%type,
      p_source         IN ords_handlers.source%type ,
      p_method         IN ords_handlers.method%type,
      p_items_per_page IN ords_handlers.items_per_page%type,
      p_mimes_allowed  IN ords_handlers.mimes_allowed%type,
      p_comments       IN ords_handlers.comments%type,
      p_updated_on     IN ords_handlers.updated_on%type DEFAULT NULL,
      p_updated_by     IN ords_handlers.updated_by%type DEFAULT NULL,
      p_created_on     IN ords_handlers.created_on%type DEFAULT NULL,
      p_created_by     IN ords_handlers.created_by%type DEFAULT NULL);

  PROCEDURE update_handler
    (
      p_schema_id      IN NUMBER,
      p_id             IN ords_handlers.template_id%type ,
      p_source_type    IN ords_handlers.source_type%type,
      p_source         IN ords_handlers.source%type ,
      p_method         IN ords_handlers.method%type,
      p_items_per_page IN ords_handlers.items_per_page%type,
      p_mimes_allowed  IN ords_handlers.mimes_allowed%type,
      p_updated_on     IN ords_handlers.updated_on%type DEFAULT NULL,
      p_updated_by     IN ords_handlers.updated_by%type DEFAULT NULL,
      p_created_on     IN ords_handlers.created_on%type DEFAULT NULL,
      p_created_by     IN ords_handlers.created_by%type DEFAULT NULL);

-- Parameters

  PROCEDURE delete_parameters(
      p_schema_id    IN NUMBER,
      p_handler_id   IN ords_parameters.handler_id%type);

  PROCEDURE delete_parameter(
      p_schema_id   IN NUMBER,
      p_id          IN ords_parameters.id%type);

  FUNCTION add_parameter(
      p_schema_id          IN NUMBER,
      p_handler_id         IN ords_parameters.handler_id%type ,
      p_name               IN ords_parameters.name%type ,
      p_bind_variable_name IN ords_parameters.bind_variable_name%type,
      p_source_type        IN ords_parameters.source_type%type,
      p_param_type         IN ords_parameters.param_type%type,
      p_access_method      IN ords_parameters.access_method%type,
      p_comments           IN ords_parameters.comments%type,
      p_created_on         IN ords_parameters.created_on%type DEFAULT NULL,
      p_created_by         IN ords_parameters.created_by%type DEFAULT NULL,
      p_updated_on         IN ords_parameters.updated_on%type DEFAULT NULL,
      p_updated_by         IN ords_parameters.updated_by%type DEFAULT NULL)
    RETURN ords_parameters.id%type;

  PROCEDURE update_parameter
    (
      p_schema_id          IN NUMBER,
      p_id                 IN ords_parameters.id%type,
      p_name               IN ords_parameters.name%type,
      p_bind_variable_name IN ords_parameters.bind_variable_name%type,
      p_source_type        IN ords_parameters.source_type%type,
      p_param_type         IN ords_parameters.param_type%type,
      p_access_method      IN ords_parameters.access_method%type,
      p_comments           IN ords_parameters.comments%type,
      p_updated_on         IN ords_parameters.updated_on%type DEFAULT NULL,
      p_updated_by         IN ords_parameters.updated_by%type DEFAULT NULL,
      p_created_on         IN ords_parameters.created_on%type DEFAULT NULL,
      p_created_by         IN ords_parameters.created_by%type DEFAULT NULL);

  -- Privileges

  FUNCTION create_privilege(
      p_schema_id   IN NUMBER,
      p_name        IN sec_privileges.name%type,
      p_label       IN sec_privileges.label%type default null,
      p_description IN sec_privileges.description%type default null,
      p_roles       IN owa.vc_arr,
      p_modules     IN owa.vc_arr,
      p_patterns    IN owa.vc_arr,
      p_comments    IN sec_privileges.comments%type default null,
      p_created_on  IN sec_privileges.created_on%type DEFAULT NULL,
      p_created_by  IN sec_privileges.created_by%type DEFAULT NULL,
      p_updated_on  IN sec_privileges.updated_on%type DEFAULT NULL,
      p_updated_by  IN sec_privileges.updated_by%type DEFAULT NULL)
    RETURN sec_privileges.id%type;

  FUNCTION create_privilege(
      p_schema_id   IN NUMBER,
      p_name        IN sec_privileges.name%type,
      p_label       IN sec_privileges.label%type default null,
      p_description IN sec_privileges.description%type default null,
      p_comments    IN sec_privileges.comments%type default null,
      p_created_on  IN sec_privileges.created_on%type DEFAULT NULL,
      p_created_by  IN sec_privileges.created_by%type DEFAULT NULL,
      p_updated_on  IN sec_privileges.updated_on%type DEFAULT NULL,
      p_updated_by  IN sec_privileges.updated_by%type DEFAULT NULL)
    RETURN sec_privileges.id%type;

  PROCEDURE update_privilege(
      p_schema_id   IN NUMBER,
      p_id          IN sec_privileges.id%type,
      p_name        IN sec_privileges.name%type,
      p_label       IN sec_privileges.label%type,
      p_description IN sec_privileges.description%type,
      p_roles       IN owa.vc_arr,
      p_modules     IN owa.vc_arr,
      p_patterns    IN owa.vc_arr,
      p_comments    IN sec_privileges.comments%type,
      p_updated_on  IN sec_privileges.updated_on%type DEFAULT NULL,
      p_updated_by  IN sec_privileges.updated_by%type DEFAULT NULL,
      p_created_on  IN sec_privileges.created_on%type DEFAULT NULL,
      p_created_by  IN sec_privileges.created_by%type DEFAULT NULL);

  PROCEDURE update_privilege(
      p_schema_id   IN NUMBER,
      p_id          IN sec_privileges.id%type,
      p_name        IN sec_privileges.name%type,
      p_label       IN sec_privileges.label%type,
      p_description IN sec_privileges.description%type,
      p_roles       IN owa.vc_arr,
      p_modules     IN owa.vc_arr,
      p_patterns    IN owa.vc_arr,
      p_updated_on  IN sec_privileges.updated_on%type DEFAULT NULL,
      p_updated_by  IN sec_privileges.updated_by%type DEFAULT NULL,
      p_created_on  IN sec_privileges.created_on%type DEFAULT NULL,
      p_created_by  IN sec_privileges.created_by%type DEFAULT NULL);

  PROCEDURE update_privilege(
      p_schema_id   IN NUMBER,
      p_id          IN sec_privileges.id%type,
      p_name        IN sec_privileges.name%type,
      p_label       IN sec_privileges.label%type,
      p_description IN sec_privileges.description%type,
      p_roles       IN owa.vc_arr,
      p_comments    IN sec_privileges.comments%type,
      p_updated_on  IN sec_privileges.updated_on%type DEFAULT NULL,
      p_updated_by  IN sec_privileges.updated_by%type DEFAULT NULL,
      p_created_on  IN sec_privileges.created_on%type DEFAULT NULL,
      p_created_by  IN sec_privileges.created_by%type DEFAULT NULL);

  PROCEDURE update_privilege(
      p_schema_id   IN NUMBER,
      p_id          IN sec_privileges.id%type,
      p_name        IN sec_privileges.name%type,
      p_label       IN sec_privileges.label%type,
      p_description IN sec_privileges.description%type,
      p_roles       IN owa.vc_arr,
      p_updated_on  IN sec_privileges.updated_on%type DEFAULT NULL,
      p_updated_by  IN sec_privileges.updated_by%type DEFAULT NULL,
      p_created_on  IN sec_privileges.created_on%type DEFAULT NULL,
      p_created_by  IN sec_privileges.created_by%type DEFAULT NULL);

  PROCEDURE update_privilege(
      p_schema_id   IN NUMBER,
      p_id          IN sec_privileges.id%type,
      p_name        IN sec_privileges.name%type,
      p_label       IN sec_privileges.label%type,
      p_description IN sec_privileges.description%type,
      p_comments    IN sec_privileges.comments%type,
      p_updated_on  IN sec_privileges.updated_on%type DEFAULT NULL,
      p_updated_by  IN sec_privileges.updated_by%type DEFAULT NULL,
      p_created_on  IN sec_privileges.created_on%type DEFAULT NULL,
      p_created_by  IN sec_privileges.created_by%type DEFAULT NULL);

  PROCEDURE update_privilege(
      p_schema_id   IN NUMBER,
      p_id          IN sec_privileges.id%type,
      p_name        IN sec_privileges.name%type,
      p_label       IN sec_privileges.label%type,
      p_description IN sec_privileges.description%type,
      p_updated_on  IN sec_privileges.updated_on%type DEFAULT NULL,
      p_updated_by  IN sec_privileges.updated_by%type DEFAULT NULL,
      p_created_on  IN sec_privileges.created_on%type DEFAULT NULL,
      p_created_by  IN sec_privileges.created_by%type DEFAULT NULL);

  PROCEDURE rename_privilege(
      p_schema_id   IN NUMBER,
      p_id          IN sec_privileges.id%type,
      p_name        IN sec_privileges.name%type,
      p_updated_on  IN sec_privileges.updated_on%type DEFAULT NULL,
      p_updated_by  IN sec_privileges.updated_by%type DEFAULT NULL,
      p_created_on  IN sec_privileges.created_on%type DEFAULT NULL,
      p_created_by  IN sec_privileges.created_by%type DEFAULT NULL);

  -- Privilege Modules

  PROCEDURE create_privilege_modules(
      p_schema_id    IN NUMBER,
      p_privilege_id IN sec_privileges.id%type,
      p_modules      IN owa.vc_arr,
      p_created_on   IN sec_privilege_modules.created_on%type DEFAULT NULL,
      p_created_by   IN sec_privilege_modules.created_by%type DEFAULT NULL,
      p_updated_on   IN sec_privilege_modules.updated_on%type DEFAULT NULL,
      p_updated_by   IN sec_privilege_modules.updated_by%type DEFAULT NULL);

  PROCEDURE update_privilege_modules(
      p_schema_id    IN NUMBER,
      p_privilege_id IN sec_privileges.id%type,
      p_modules      IN owa.vc_arr,
      p_updated_on   IN sec_privilege_modules.updated_on%type DEFAULT NULL,
      p_updated_by   IN sec_privilege_modules.updated_by%type DEFAULT NULL,
      p_created_on   IN sec_privilege_modules.created_on%type DEFAULT NULL,
      p_created_by   IN sec_privilege_modules.created_by%type DEFAULT NULL);

  PROCEDURE set_module_privilege(
      p_schema_id    IN NUMBER,
      p_module_name  IN ords_modules.name%type,
      p_privilege_id IN sec_privileges.id%type,
      p_created_on   IN sec_privilege_modules.created_on%type DEFAULT NULL,
      p_created_by   IN sec_privilege_modules.created_by%type DEFAULT NULL,
      p_updated_on   IN sec_privilege_modules.updated_on%type DEFAULT NULL,
      p_updated_by   IN sec_privilege_modules.updated_by%type DEFAULT NULL);

  PROCEDURE delete_privilege_module(
        p_schema_id    IN NUMBER,
        p_privilege_id IN sec_privileges.id%type,
        p_name         IN ords_modules.name%type);

  PROCEDURE delete_privilege_module(
        p_schema_id    IN NUMBER,
        p_privilege_id IN sec_privileges.id%type,
        p_module_id    IN ords_modules.id%type);

  PROCEDURE delete_privilege_modules(
      p_schema_id    IN NUMBER,
      p_privilege_id IN sec_privileges.id%type);

  PROCEDURE delete_privilege(
      p_schema_id IN NUMBER,
      p_id        IN sec_privileges.id%type);

  PROCEDURE delete_privileges(
      p_schema_id IN NUMBER);
      
  -- Module Privilege
      
  PROCEDURE set_module_privilege(
      p_schema_id      IN NUMBER,
      p_module_id      IN ords_modules.id%type,
      p_privilege_id   IN sec_privileges.id%type,
      p_created_on     IN sec_privilege_modules.created_on%type DEFAULT NULL,
      p_created_by     IN sec_privilege_modules.created_by%type DEFAULT NULL,
      p_updated_on     IN sec_privilege_modules.updated_on%type DEFAULT NULL,
      p_updated_by     IN sec_privilege_modules.updated_by%type DEFAULT NULL);

  PROCEDURE set_module_privilege(
      p_schema_id      IN NUMBER,
      p_module_id      IN ords_modules.id%type,
      p_privilege_name IN sec_privileges.name%type,
      p_created_on     IN sec_privilege_modules.created_on%type DEFAULT NULL,
      p_created_by     IN sec_privilege_modules.created_by%type DEFAULT NULL,
      p_updated_on     IN sec_privilege_modules.updated_on%type DEFAULT NULL,
      p_updated_by     IN sec_privilege_modules.updated_by%type DEFAULT NULL);

  PROCEDURE set_module_privilege(
      p_schema_id      IN NUMBER,
      p_module_name    IN ords_modules.name%type,
      p_privilege_name IN sec_privileges.name%type,
      p_created_on     IN sec_privilege_modules.created_on%type DEFAULT NULL,
      p_created_by     IN sec_privilege_modules.created_by%type DEFAULT NULL,
      p_updated_on     IN sec_privilege_modules.updated_on%type DEFAULT NULL,
      p_updated_by     IN sec_privilege_modules.updated_by%type DEFAULT NULL);

  -- Module Origins Allowed

  PROCEDURE set_module_origins_allowed(
      p_schema_id       IN NUMBER,
      p_module_id       IN ords_modules.id%type,
      p_origins_allowed IN sec_origins_allowed_modules.origins_allowed%type,
      p_created_on      IN sec_privilege_modules.created_on%type DEFAULT NULL,
      p_created_by      IN sec_privilege_modules.created_by%type DEFAULT NULL,
      p_updated_on      IN sec_privilege_modules.updated_on%type DEFAULT NULL,
      p_updated_by      IN sec_privilege_modules.updated_by%type DEFAULT NULL);

  PROCEDURE set_module_origins_allowed(
      p_schema_id       IN NUMBER,
      p_module_name     IN ords_modules.name%type,
      p_origins_allowed IN sec_origins_allowed_modules.origins_allowed%type,
      p_created_on      IN sec_privilege_modules.created_on%type DEFAULT NULL,
      p_created_by      IN sec_privilege_modules.created_by%type DEFAULT NULL,
      p_updated_on      IN sec_privilege_modules.updated_on%type DEFAULT NULL,
      p_updated_by      IN sec_privilege_modules.updated_by%type DEFAULT NULL);

  -- Roles

  FUNCTION define_role(
      p_schema_id      IN NUMBER,
      p_name           IN sec_roles.name%type,
      p_created_on     IN sec_roles.created_on%type DEFAULT NULL,
      p_created_by     IN sec_roles.created_by%type DEFAULT NULL,
      p_updated_on     IN sec_roles.updated_on%type DEFAULT NULL,
      p_updated_by     IN sec_roles.updated_by%type DEFAULT NULL)
    RETURN sec_roles.id%type;

  FUNCTION create_role(
      p_schema_id      IN NUMBER,
      p_name           IN sec_roles.name%type,
      p_created_on     IN sec_roles.created_on%type DEFAULT NULL,
      p_created_by     IN sec_roles.created_by%type DEFAULT NULL,
      p_updated_on     IN sec_roles.updated_on%type DEFAULT NULL,
      p_updated_by     IN sec_roles.updated_by%type DEFAULT NULL)
    RETURN sec_roles.id%type;

  PROCEDURE update_role(
      p_schema_id      IN NUMBER,
      p_id             IN sec_roles.id%type,
      p_name           IN sec_roles.name%type,
      p_updated_on     IN sec_roles.updated_on%type DEFAULT NULL,
      p_updated_by     IN sec_roles.updated_by%type DEFAULT NULL,
      p_created_on     IN sec_roles.created_on%type DEFAULT NULL,
      p_created_by     IN sec_roles.created_by%type DEFAULT NULL);

  PROCEDURE delete_role(
      p_schema_id IN NUMBER,
      p_id        IN sec_roles.id%type);

  -- Privilege Mappings

  PROCEDURE create_privilege_mapping(
      p_schema_id      IN NUMBER,
      p_privilege_id   IN sec_privileges.id%type,
      p_patterns       IN owa.vc_arr,
      p_created_on     IN sec_privilege_mappings.created_on%type DEFAULT NULL,
      p_created_by     IN sec_privilege_mappings.created_by%type DEFAULT NULL,
      p_updated_on     IN sec_privilege_mappings.updated_on%type DEFAULT NULL,
      p_updated_by     IN sec_privilege_mappings.updated_by%type DEFAULT NULL);

  PROCEDURE delete_privilege_mapping(
        p_schema_id    IN NUMBER,
        p_privilege_id IN sec_privileges.id%type,
        p_pattern      IN sec_privilege_mappings.pattern%type);
        
  PROCEDURE update_privilege_mappings(
      p_schema_id      IN NUMBER,
      p_privilege_id   IN sec_privileges.id%type,
      p_patterns       IN owa.vc_arr,
      p_updated_on     IN sec_privilege_mappings.updated_on%type DEFAULT NULL,
      p_updated_by     IN sec_privilege_mappings.updated_by%type DEFAULT NULL,
      p_created_on     IN sec_privilege_mappings.created_on%type DEFAULT NULL,
      p_created_by     IN sec_privilege_mappings.created_by%type DEFAULT NULL);

  FUNCTION add_privilege_mapping(
      p_schema_id      IN NUMBER,
      p_privilege_id   IN sec_privileges.id%type,
      p_pattern        IN sec_privilege_mappings.pattern%type,
      p_created_on     IN sec_privilege_mappings.created_on%type DEFAULT NULL,
      p_created_by     IN sec_privilege_mappings.created_by%type DEFAULT NULL,
      p_updated_on     IN sec_privilege_mappings.updated_on%type DEFAULT NULL,
      p_updated_by     IN sec_privilege_mappings.updated_by%type DEFAULT NULL)
    RETURN sec_privilege_mappings.id%type;

  PROCEDURE delete_privilege_mappings(
        p_schema_id    IN NUMBER,
        p_privilege_id IN sec_privileges.id%type);

  -- Privilege Roles

  PROCEDURE create_privilege_roles(
      p_schema_id      IN NUMBER,
      p_privilege_id   IN sec_privileges.id%type,
      p_roles          IN owa.vc_arr,
      p_created_on     IN sec_privilege_roles.created_on%type DEFAULT NULL,
      p_created_by     IN sec_privilege_roles.created_by%type DEFAULT NULL,
      p_updated_on     IN sec_privilege_roles.updated_on%type DEFAULT NULL,
      p_updated_by     IN sec_privilege_roles.updated_by%type DEFAULT NULL);

  PROCEDURE update_privilege_roles(
      p_schema_id      IN NUMBER,
      p_privilege_id   IN sec_privileges.id%type,
      p_roles          IN owa.vc_arr,
      p_updated_on     IN sec_privilege_roles.updated_on%type DEFAULT NULL,
      p_updated_by     IN sec_privilege_roles.updated_by%type DEFAULT NULL,
      p_created_on     IN sec_privilege_roles.created_on%type DEFAULT NULL,
      p_created_by     IN sec_privilege_roles.created_by%type DEFAULT NULL);

  FUNCTION add_privilege_role(
      p_schema_id      IN NUMBER,
      p_privilege_id   IN sec_privileges.id%type,
      p_name           IN sec_roles.name%type,
      p_created_on     IN sec_privilege_roles.created_on%type DEFAULT NULL,
      p_created_by     IN sec_privilege_roles.created_by%type DEFAULT NULL,
      p_updated_on     IN sec_privilege_roles.updated_on%type DEFAULT NULL,
      p_updated_by     IN sec_privilege_roles.updated_by%type DEFAULT NULL)
    RETURN sec_roles.id%type;

  PROCEDURE delete_privilege_role(
        p_schema_id    IN NUMBER,
        p_privilege_id IN sec_privileges.id%type,
        p_role_id      IN sec_roles.id%type);

  PROCEDURE delete_privilege_role(
        p_schema_id    IN NUMBER,
        p_privilege_id IN sec_privileges.id%type,
        p_name         IN sec_roles.name%type);

  PROCEDURE delete_privilege_roles(
        p_schema_id    IN NUMBER,
        p_privilege_id IN sec_privileges.id%type);

-- Lookups  

  PROCEDURE lookup_schema(
      p_schema_name  IN ords_schemas.parsing_schema%type,
      p_schema   IN OUT ords_schemas%rowtype,
      p_url_mapping OUT ords_url_mappings%rowtype,
      p_for_update   IN boolean DEFAULT FALSE);

  PROCEDURE lookup_role(
      p_role_name    IN sec_roles.name%type,
      p_schema_id    IN ords_schemas.id%type,
      p_role     IN OUT sec_roles%rowtype,
      p_for_update   IN boolean DEFAULT FALSE);

  PROCEDURE lookup_module(
      p_module_name  IN ords_modules.name%type,
      p_schema_id    IN ords_schemas.id%type,
      p_module   IN OUT ords_modules%rowtype,
      p_for_update   IN boolean DEFAULT FALSE);

  PROCEDURE lookup_template(
      p_module_name   IN ords_modules.name%type,
      p_pattern       IN ords_templates.uri_template%type,
      p_schema_id     IN ords_schemas.id%type,
      p_template  IN OUT ords_templates%rowtype,
      p_for_update    IN boolean DEFAULT FALSE);

  PROCEDURE lookup_handler(
      p_module_name  IN ords_modules.name%type,
      p_pattern      IN ords_templates.uri_template%type,
      p_method       IN ords_handlers.method%type,
      p_schema_id    IN ords_schemas.id%type,
      p_handler  IN OUT ords_handlers%rowtype,
      p_for_update   IN boolean DEFAULT FALSE);

  PROCEDURE lookup_parameter(
      p_module_name     IN ords_modules.name%type,
      p_pattern         IN ords_templates.uri_template%type,
      p_method          IN ords_handlers.method%type,
      p_parameter_name  IN ords_parameters.name%type,
      p_schema_id       IN ords_schemas.id%type,
      p_parameter   IN OUT ords_parameters%rowtype,
      p_for_update      IN boolean DEFAULT FALSE);

  PROCEDURE lookup_privilege(
      p_privilege_name  IN sec_privileges.name%type,
      p_schema_id       IN ords_schemas.id%type,
      p_privilege   IN OUT sec_privileges%rowtype,
      p_for_update      IN boolean DEFAULT FALSE);

  PROCEDURE lookup_privilege_module(
      p_privilege_name       IN sec_privileges.name%type,
      p_module_name          IN ords_modules.name%type,
      p_schema_id            IN ords_schemas.id%type,
      p_privilege_module IN OUT sec_privilege_modules%rowtype,
      p_for_update           IN boolean DEFAULT FALSE);

  PROCEDURE lookup_privilege_role(
      p_privilege_name       IN sec_privileges.name%type,
      p_role_name            IN sec_roles.name%type,
      p_schema_id            IN ords_schemas.id%type,
      p_privilege_role   IN OUT sec_privilege_roles%rowtype,
      p_for_update           IN boolean DEFAULT FALSE);

  PROCEDURE lookup_privilege_mapping(
      p_privilege_name        IN sec_privileges.name%type,
      p_pattern               IN sec_privilege_mappings.pattern%type,
      p_schema_id             IN ords_schemas.id%type,
      p_privilege_mapping IN OUT sec_privilege_mappings%rowtype,
      p_for_update            IN boolean DEFAULT FALSE);

-- Defines

  PROCEDURE define_privilege(
      p_schema_id          IN NUMBER,
      p_privilege      IN OUT sec_privileges%rowtype,
      p_name               IN sec_privileges.name%type,
      p_label              IN sec_privileges.label%type,
      p_description        IN sec_privileges.description%type DEFAULT NULL,
      p_roles              IN owa.vc_arr,
      p_modules            IN owa.vc_arr,
      p_patterns           IN owa.vc_arr,
      p_comments           IN sec_privileges.comments%type DEFAULT NULL,
      p_created_on         IN sec_privileges.created_on%type DEFAULT NULL,
      p_created_by         IN sec_privileges.created_by%type DEFAULT NULL,
      p_updated_on         IN sec_privileges.updated_on%type DEFAULT NULL,
      p_updated_by         IN sec_privileges.updated_by%type DEFAULT NULL);

  PROCEDURE define_privilege(
      p_schema_id          IN NUMBER,
      p_privilege      IN OUT sec_privileges%rowtype,
      p_name               IN sec_privileges.name%type,
      p_label              IN sec_privileges.label%type,
      p_description        IN sec_privileges.description%type DEFAULT NULL,
      p_roles              IN owa.vc_arr,
      p_patterns           IN owa.vc_arr,
      p_comments           IN sec_privileges.comments%type DEFAULT NULL,
      p_created_on         IN sec_privileges.created_on%type DEFAULT NULL,
      p_created_by         IN sec_privileges.created_by%type DEFAULT NULL,
      p_updated_on         IN sec_privileges.updated_on%type DEFAULT NULL,
      p_updated_by         IN sec_privileges.updated_by%type DEFAULT NULL);

  PROCEDURE define_privilege(
      p_schema_id          IN NUMBER,
      p_privilege      IN OUT sec_privileges%rowtype,
      p_name               IN sec_privileges.name%type,
      p_label              IN sec_privileges.label%type,
      p_description        IN sec_privileges.description%type DEFAULT NULL,
      p_comments           IN sec_privileges.comments%type DEFAULT NULL,
      p_created_on         IN sec_privileges.created_on%type DEFAULT NULL,
      p_created_by         IN sec_privileges.created_by%type DEFAULT NULL,
      p_updated_on         IN sec_privileges.updated_on%type DEFAULT NULL,
      p_updated_by         IN sec_privileges.updated_by%type DEFAULT NULL);

  PROCEDURE define_module_privilege(
      p_schema_id      IN NUMBER,
      p_module_name    IN ords_modules.name%type,
      p_privilege_name IN sec_privileges.name%type,
      p_created_on     IN sec_privilege_modules.created_on%type DEFAULT NULL,
      p_created_by     IN sec_privilege_modules.created_by%type DEFAULT NULL,
      p_updated_on     IN sec_privilege_modules.updated_on%type DEFAULT NULL,
      p_updated_by     IN sec_privilege_modules.updated_by%type DEFAULT NULL);

  PROCEDURE define_module(
      p_schema_id          IN NUMBER,
      p_module         IN OUT ords_modules%rowtype,
      p_name               IN ords_modules.name%type,
      p_base_path          IN ords_modules.uri_prefix%type,
      p_items_per_page     IN ords_modules.items_per_page%type DEFAULT 25,
      p_status             IN ords_modules.status%type DEFAULT 'PUBLISHED',
      p_comments           IN ords_modules.comments%type DEFAULT NULL,
      p_created_on         IN ords_modules.created_on%type DEFAULT NULL,
      p_created_by         IN ords_modules.created_by%type DEFAULT NULL,
      p_updated_on         IN ords_modules.updated_on%type DEFAULT NULL,
      p_updated_by         IN ords_modules.updated_by%type DEFAULT NULL);

  PROCEDURE define_template(
      p_schema_id          IN NUMBER,
      p_template       IN OUT ords_templates%rowtype,
      p_module_name        IN ords_modules.name%type,
      p_pattern            IN ords_templates.uri_template%type,
      p_priority           IN ords_templates.priority%type DEFAULT 0,
      p_etag_type          IN ords_templates.etag_type%type DEFAULT 'HASH',
      p_etag_query         IN ords_templates.etag_query%type DEFAULT NULL,
      p_comments           IN ords_templates.comments%type DEFAULT NULL,
      p_created_on         IN ords_templates.created_on%type DEFAULT NULL,
      p_created_by         IN ords_templates.created_by%type DEFAULT NULL,
      p_updated_on         IN ords_templates.updated_on%type DEFAULT NULL,
      p_updated_by         IN ords_templates.updated_by%type DEFAULT NULL);

  PROCEDURE define_handler(
      p_schema_id          IN NUMBER,
      p_handler        IN OUT ords_handlers%rowtype,
      p_module_name        IN ords_modules.name%type,
      p_pattern            IN ords_templates.uri_template%type,
      p_method             IN ords_handlers.method%type DEFAULT 'GET',
      p_source_type        IN ords_handlers.source_type%type DEFAULT ords.source_type_collection_feed,
      p_source             IN ords_handlers.source%type,
      p_items_per_page     IN ords_handlers.items_per_page%type DEFAULT NULL,
      p_mimes_allowed      IN ords_handlers.mimes_allowed%type DEFAULT NULL,
      p_comments           IN ords_handlers.comments%type DEFAULT NULL,
      p_created_on         IN ords_handlers.created_on%type DEFAULT NULL,
      p_created_by         IN ords_handlers.created_by%type DEFAULT NULL,
      p_updated_on         IN ords_handlers.updated_on%type DEFAULT NULL,
      p_updated_by         IN ords_handlers.updated_by%type DEFAULT NULL);

  PROCEDURE define_parameter(
      p_schema_id          IN NUMBER,
      p_parameter      IN OUT ords_parameters%rowtype,
      p_module_name        IN ords_modules.name%type,
      p_pattern            IN ords_templates.uri_template%type,
      p_method             IN ords_handlers.method%type,
      p_name               IN ords_parameters.name%type ,
      p_bind_variable_name IN ords_parameters.bind_variable_name%type DEFAULT NULL,
      p_source_type        IN ords_parameters.source_type%type DEFAULT 'HEADER',
      p_param_type         IN ords_parameters.param_type%type DEFAULT 'STRING',
      p_access_method      IN ords_parameters.access_method%type DEFAULT 'IN',
      p_comments           IN ords_parameters.comments%type DEFAULT NULL,
      p_created_on         IN ords_parameters.created_on%type DEFAULT NULL,
      p_created_by         IN ords_parameters.created_by%type DEFAULT NULL,
      p_updated_on         IN ords_parameters.updated_on%type DEFAULT NULL,
      p_updated_by         IN ords_parameters.updated_by%type DEFAULT NULL);

END ords_services_internal;
