text: -- ---------------------------------------------------------------------------------------------------------------
  -- This is the 3.2.0 version of Portal database called portal
  
  -- note to : database admin,  set the mysql system variable called lower_case_table_names
  --		it can be set 3 different ways: 
  --			command-line options (cmd-line), 
  --			options valid in configuration files (option file), or 
  --			server system variables (system var). 
  
  -- it needs to be set to 1, then table names are stored in lowercase on disk and comparisons are not case sensitive. 
  -- -----------------------------------------------------------------------------------------------------------------
  set foreign_key_checks=1; 
  
  SET GLOBAL character_set_client     = utf8;
  SET GLOBAL character_set_connection = utf8;
  SET GLOBAL character_set_database   = utf8;
  SET GLOBAL character_set_results    = utf8;
  SET GLOBAL character_set_server     = utf8;
  
  create database portal;
  
  use portal;
  
  -- ------------------ create table section
  --
  -- name: cr_favorite_reports; type: table
  --
  create table cr_favorite_reports (
      user_id integer not null,
      rep_id integer not null
  );
  --
  -- name: cr_filehist_log; type: table
  --
  create table cr_filehist_log (
      schedule_id numeric(11,0) not null,
      url character varying(4000),
      notes character varying(3500),
      run_time timestamp
  );
  --
  -- name: cr_folder; type: table
  --
  create table cr_folder (
      folder_id integer not null,
      folder_name character varying(50) not null,
      descr character varying(500),
      create_id integer not null,
      create_date timestamp not null,
      parent_folder_id integer,
      public_yn character varying(1) default 'n' not null
  );
  --
  -- name: cr_folder_access; type: table
  --
  create table cr_folder_access (
      folder_access_id numeric(11,0) not null,
      folder_id numeric(11,0) not null,
      order_no numeric(11,0) not null,
      role_id numeric(11,0),
      user_id numeric(11,0),
      read_only_yn character varying(1) default 'n' not null
  );
  --
  -- name: cr_hist_user_map; type: table
  --
  create table cr_hist_user_map (
      hist_id int(11) not null,
      user_id int(11) not null
  );
  --
  -- name: cr_lu_file_type; type: table
  --
  create table cr_lu_file_type (
      lookup_id numeric(2,0) not null,
      lookup_descr character varying(255) not null,
      active_yn character(1) default 'y',
      error_code numeric(11,0)
  );
  --
  -- name: cr_raptor_action_img; type: table
  --
  create table cr_raptor_action_img (
      image_id character varying(100) not null,
      image_loc character varying(400)
  );
  --
  -- name: cr_raptor_pdf_img; type: table
  --
  create table cr_raptor_pdf_img (
      image_id character varying(100) not null,
      image_loc character varying(400)
  );
  --
  -- name: cr_remote_schema_info; type: table
  --
  create table cr_remote_schema_info (
      schema_prefix character varying(5) not null,
      schema_desc character varying(75) not null,
      datasource_type character varying(100)
  );
  --
  -- name: cr_report; type: table
  --
  create table cr_report (
      rep_id numeric(11,0) not null,
      title character varying(100) not null,
      descr character varying(255),
      public_yn character varying(1) default 'n' not null,
      report_xml text,
      create_id numeric(11,0),
      create_date timestamp default now(),
      maint_id numeric(11,0),
      maint_date timestamp default now(), 
      menu_id character varying(500),
      menu_approved_yn character varying(1) default 'n' not null,
      owner_id numeric(11,0),
      folder_id integer default 0,
      dashboard_type_yn character varying(1) default 'n',
      dashboard_yn character varying(1) default 'n'
  );
  --
  -- name: cr_report_access; type: table
  --
  create table cr_report_access (
      rep_id numeric(11,0) not null,
      order_no numeric(11,0) not null,
      role_id numeric(11,0),
      user_id numeric(11,0),
      read_only_yn character varying(1) default 'n' not null
  );
  --
  -- name: cr_report_dwnld_log; type: table
  --
  create table cr_report_dwnld_log (
      user_id numeric(11,0) not null,
      rep_id integer not null,
      file_name character varying(100) not null,
      dwnld_start_time timestamp default now() not null,
      record_ready_time timestamp default now(),
      filter_params character varying(2000)
  );
  --
  -- name: cr_report_email_sent_log; type: table
  --
  create table cr_report_email_sent_log (
      log_id integer not null,
      schedule_id numeric(11,0),
      gen_key character varying(25) not null,
      rep_id numeric(11,0) not null,
      user_id numeric(11,0),
      sent_date timestamp default now(),
      access_flag character varying(1) default 'y' not null,
      touch_date timestamp default now()
  );
  --
  -- name: cr_report_file_history; type: table
  --
  create table cr_report_file_history (
      hist_id int(11) not null,
      sched_user_id numeric(11,0) not null,
      schedule_id numeric(11,0) not null,
      user_id numeric(11,0) not null,
      rep_id numeric(11,0),
      run_date timestamp,
      recurrence character varying(50),
      file_type_id numeric(2,0),
      file_name character varying(80),
      file_blob blob,
      file_size numeric(11,0),
      raptor_url character varying(4000),
      error_yn character(1) default 'n',
      error_code numeric(11,0),
      deleted_yn character(1) default 'n',
      deleted_by numeric(38,0)
  );
  --
  -- name: cr_report_log; type: table
  --
  create table cr_report_log (
      rep_id numeric(11,0) not null,
      log_time timestamp not null,
      user_id numeric(11,0) not null,
      action character varying(2000) not null,
      action_value character varying(50),
      form_fields character varying(4000)
  );
  --
  -- name: cr_report_schedule; type: table
  --
  create table cr_report_schedule (
      schedule_id numeric(11,0) not null,
      sched_user_id numeric(11,0) not null,
      rep_id numeric(11,0) not null,
      enabled_yn character varying(1) not null,
      start_date timestamp default now(),
      end_date timestamp default now(),
      run_date timestamp default now(),
      recurrence character varying(50),
      conditional_yn character varying(1) not null,
      condition_sql character varying(4000),
      notify_type integer default 0,
      max_row integer default 1000,
      initial_formfields character varying(3500),
      processed_formfields character varying(3500),
      formfields character varying(3500),
      condition_large_sql text,
      encrypt_yn character(1) default 'n',
      attachment_yn character(1) default 'y'
  );
  --
  -- name: cr_report_schedule_users; type: table
  --
  create table cr_report_schedule_users (
      schedule_id numeric(11,0) not null,
      rep_id numeric(11,0) not null,
      user_id numeric(11,0) not null,
      role_id numeric(11,0),
      order_no numeric(11,0) not null
  );
  --
  -- name: cr_report_template_map; type: table
  --
  create table cr_report_template_map (
      report_id integer not null,
      template_file character varying(200)
  );
  --
  -- name: cr_schedule_activity_log; type: table
  --
  create table cr_schedule_activity_log (
      schedule_id numeric(11,0) not null,
      url character varying(4000),
      notes character varying(2000),
      run_time timestamp
  );
  --
  -- name: cr_table_join; type: table
  --
  create table cr_table_join (
      src_table_name character varying(30) not null,
      dest_table_name character varying(30) not null,
      join_expr character varying(500) not null
  );
  --
  -- name: cr_table_role; type: table
  --
  create table cr_table_role (
      table_name character varying(30) not null,
      role_id numeric(11,0) not null
  );
  --
  -- name: cr_table_source; type: table
  --
  create table cr_table_source (
      table_name character varying(30) not null,
      display_name character varying(30) not null,
      pk_fields character varying(200),
      web_view_action character varying(50),
      large_data_source_yn character varying(1) default 'n' not null,
      filter_sql character varying(4000),
      source_db character varying(50)
  );
  --
  -- name: fn_lu_timezone; type: table
  --
  create table fn_lu_timezone (
      timezone_id int(11) not null,
      timezone_name character varying(100) not null,
      timezone_value character varying(100) not null
  );
  
  create table fn_user (
      user_id int(11) not null primary key  auto_increment,
      org_id int(11),
      manager_id int(11),
      first_name character varying(50),
      middle_name character varying(50),
      last_name character varying(50),
      phone character varying(25),
      fax character varying(25),
      cellular character varying(25),
      email character varying(50),
      address_id numeric(11,0),
      alert_method_cd character varying(10),
      hrid character varying(20),
      org_user_id CHARACTER VARYING(60),
      org_code character varying(30),
      login_id character varying(60),
      login_pwd character varying(100),
      last_login_date timestamp,
      active_yn character varying(1) default 'y' not null,
      created_id int(11),
      created_date timestamp default now(),
      modified_id int(11),
      modified_date timestamp default now(),
      is_internal_yn character(1) default 'n' not null,
      address_line_1 character varying(100),
      address_line_2 character varying(100),
      city character varying(50),
      state_cd character varying(3),
      zip_code character varying(11),
      country_cd character varying(3),
      location_clli character varying(8),
      org_manager_userid CHARACTER VARYING(20),
      company character varying(100),
      department_name character varying(100),
      job_title character varying(100),
      timezone int(11),
      department character varying(25),
      business_unit character varying(25),
      business_unit_name character varying(100),
      cost_center character varying(25),
      fin_loc_code character varying(10),
      silo_status character varying(10),
      is_system_user character(1) default 'N',
      language_id int(2) default 1
  );
  
  create table fn_language(
      language_id int(11) PRIMARY KEY NOT NULL AUTO_INCREMENT,
      language_name VARCHAR(100) NOT NULL,
      language_alias VARCHAR(100) NOT NULL
  );
  
  create table fn_display_text(
      id int(11) PRIMARY KEY NOT NULL AUTO_INCREMENT,
      language_id int(11) NOT NULL,
      text_id int(11) NOT NULL,
      text_label VARCHAR(100) NOT NULL
  );
  --
  -- name: fn_role; type: table
  --
  create table fn_role (
      role_id int(11) not null primary key auto_increment,
      role_name character varying(300) not null,
      active_yn character varying(1) default 'y' not null,
      priority numeric(4,0),
      app_id int(11) default null,
      app_role_id int(11) default null
  
  );
  --
  -- name: fn_audit_action; type: table
  --
  create table fn_audit_action (
      audit_action_id integer not null,
      class_name character varying(500) not null,
      method_name character varying(50) not null,
      audit_action_cd character varying(20) not null,
      audit_action_desc character varying(200),
      active_yn character varying(1)
  );
  --
  -- name: fn_audit_action_log; type: table
  --
  create table fn_audit_action_log (
      audit_log_id integer not null primary key  auto_increment,
      audit_action_cd character varying(200),
      action_time timestamp,
      user_id numeric(11,0),
      class_name character varying(100),
      method_name character varying(50),
      success_msg character varying(20),
      error_msg character varying(500)
  );
  --
  -- name: fn_lu_activity; type: table
  --
  create table fn_lu_activity (
      activity_cd character varying(50) not null primary key,
      activity character varying(50) not null
  );
  --
  -- name: fn_audit_log; type: table
  --
  create table fn_audit_log (
      log_id int(11) not null primary key auto_increment,
      user_id int(11) not null,
      activity_cd character varying(50) not null,
      audit_date timestamp default now() not null,
      comments character varying(1000),
      affected_record_id_bk character varying(500),
      affected_record_id character varying(4000),
      constraint fk_fn_audit_ref_209_fn_user foreign key (user_id) references fn_user(user_id)
  );
  --
  -- name: fn_broadcast_message; type: table
  --
  create table fn_broadcast_message (
      message_id int(11) not null primary key auto_increment,
      message_text character varying(1000) not null,
      message_location_id numeric(11,0) not null,
      broadcast_start_date timestamp not null  default now(),
      broadcast_end_date timestamp not null default now(),
      active_yn character(1) default 'y' not null,
      sort_order numeric(4,0) not null,
      broadcast_site_cd character varying(50)
  );
  --
  -- name: fn_chat_logs; type: table
  --
  create table fn_chat_logs (
      chat_log_id integer not null,
      chat_room_id integer,
      user_id integer,
      message character varying(1000),
      message_date_time timestamp
  );
  --
  -- name: fn_chat_room; type: table
  --
  create table fn_chat_room (
      chat_room_id integer not null,
      name character varying(50) not null,
      description character varying(500),
      owner_id integer,
      created_date timestamp default now(),
      updated_date timestamp default now()
  );
  --
  -- name: fn_chat_users; type: table
  --
  create table fn_chat_users (
      chat_room_id integer,
      user_id integer,
      last_activity_date_time timestamp,
      chat_status character varying(20),
      id integer not null
  );
  --
  -- name: fn_datasource; type: table
  --
  create table fn_datasource (
      id integer not null primary key auto_increment,
      name character varying(50),
      driver_name character varying(256),
      server character varying(256),
      port integer,
      user_name character varying(256),
      password character varying(256),
      url character varying(256),
      min_pool_size integer,
      max_pool_size integer,
      adapter_id integer,
      ds_type character varying(20)
  );
  --
  -- name: fn_function; type: table
  --
  create table fn_function (
      function_cd character varying(30) not null primary key,
      function_name character varying(50) not null
  );
  --
  -- name: fn_lu_alert_method; type: table
  --
  create table fn_lu_alert_method (
      alert_method_cd character varying(10) not null,
      alert_method character varying(50) not null
  );
  --
  -- name: fn_lu_broadcast_site; type: table
  --
  create table fn_lu_broadcast_site (
      broadcast_site_cd character varying(50) not null,
      broadcast_site_descr character varying(100)
  );
  --
  -- name: fn_lu_menu_set; type: table
  --
  create table fn_lu_menu_set (
      menu_set_cd character varying(10) not null primary key,
      menu_set_name character varying(50) not null
  );
  --
  -- name: fn_lu_priority; type: table
  --
  create table fn_lu_priority (
      priority_id numeric(11,0) not null,
      priority character varying(50) not null,
      active_yn character(1) not null,
      sort_order numeric(5,0)
  );
  --
  -- name: fn_lu_role_type; type: table
  --
  create table fn_lu_role_type (
      role_type_id numeric(11,0) not null,
      role_type character varying(50) not null
  );
  --
  -- name: fn_lu_tab_set; type: table
  --
  create table fn_lu_tab_set (
      tab_set_cd character varying(30) not null,
      tab_set_name character varying(50) not null
  );
  --
  -- name: fn_menu; type: table
  --
  create table fn_menu (
      menu_id int(11) not null primary key auto_increment,
      label character varying(100),
      parent_id int(11),
      sort_order numeric(4,0),
      action character varying(200),
      function_cd character varying(30),
      active_yn character varying(1) default 'y' not null,
      servlet character varying(50),
      query_string character varying(200),
      external_url character varying(200),
      target character varying(25),
      menu_set_cd character varying(10) default 'app',
      separator_yn character(1) default 'n',
      image_src character varying(100),
      constraint fk_fn_menu_ref_196_fn_menu foreign key (parent_id) references fn_menu(menu_id),
      constraint fk_fn_menu_menu_set_cd foreign key (menu_set_cd) references fn_lu_menu_set(menu_set_cd)
  );
  
  create index idx_fn_menu_label on fn_menu(label);
  --
  -- name: fn_org; type: table
  --
  create table fn_org (
      org_id int(11) not null,
      org_name character varying(50) not null,
      access_cd character varying(10)
  );
  --
  -- name: fn_restricted_url; type: table
  --
  create table fn_restricted_url (
      restricted_url character varying(250) not null,
      function_cd character varying(30) not null
  );
  --
  -- name: fn_role_composite; type: table
  --
  create table fn_role_composite (
      parent_role_id int(11) not null,
      child_role_id int(11) not null,
      constraint fk_fn_role_composite_child foreign key (child_role_id) references fn_role(role_id),
      constraint fk_fn_role_composite_parent foreign key (parent_role_id) references fn_role(role_id)
  );
  --
  -- name: fn_role_function; type: table
  --
  create table fn_role_function (
      role_id int(11) not null,
      function_cd character varying(30) not null,
      constraint fk_fn_role__ref_198_fn_role foreign key (role_id) references fn_role(role_id)
  );
  --
  -- name: fn_tab; type: table
  --
  create table fn_tab (
      tab_cd character varying(30) not null,
      tab_name character varying(50) not null,
      tab_descr character varying(100),
      action character varying(100) not null,
      function_cd character varying(30) not null,
      active_yn character(1) not null,
      sort_order numeric(11,0) not null,
      parent_tab_cd character varying(30),
      tab_set_cd character varying(30)
  );
  --
  -- name: fn_tab_selected; type: table
  --
  create table fn_tab_selected (
      selected_tab_cd character varying(30) not null,
      tab_uri character varying(40) not null
  );
  --
  -- name: fn_user_pseudo_role; type: table
  --
  create table fn_user_pseudo_role (
      pseudo_role_id int(11) not null,
      user_id int(11) not null
  );
  --
  -- name: fn_user_role; type: table
  --
  create table fn_user_role (
      user_id int(10) not null,
      role_id int(10) not null,
      priority numeric(4,0),
      app_id int(11) default 2,
      constraint fk_fn_user__ref_172_fn_user foreign key (user_id) references fn_user(user_id),
      constraint fk_fn_user__ref_175_fn_role foreign key (role_id) references fn_role(role_id)
  );
  --
  -- name: schema_info; type: table
  --
  create table schema_info (
      SCHEMA_ID CHARACTER VARYING(25) not null,
      SCHEMA_DESC CHARACTER VARYING(75) not null,
      DATASOURCE_TYPE CHARACTER VARYING(100),
      CONNECTION_URL VARCHAR(200) not null,
      USER_NAME VARCHAR(45) not null,
      PASSWORD VARCHAR(45) null default null,
      DRIVER_CLASS VARCHAR(100) not null,
      MIN_POOL_SIZE INT not null,
      MAX_POOL_SIZE INT not null,
      IDLE_CONNECTION_TEST_PERIOD INT not null
  
  );
  -- ----------------------------------------------------------
  -- name: fn_app; type: table
  -- ----------------------------------------------------------
  create table fn_app (
    app_id int(11) primary key not null auto_increment,
    app_name varchar(100) not null default '?',
    app_image_url varchar(256) default null,
    app_description varchar(512) default null,
    app_notes varchar(4096) default null,
    app_url varchar(256) default null,
    app_alternate_url varchar(256) default null,
    app_rest_endpoint varchar(2000) default null,
    ml_app_name varchar(50) not null default '?',
    ml_app_admin_id varchar(7) not null default '?',
    mots_id int(11) default null,
    app_password varchar(256) default null,
    open char(1) default 'N',
    enabled char(1) default 'Y',
    thumbnail mediumblob null default null,
    app_username varchar(50),
    ueb_key varchar(256) default null,
    ueb_secret varchar(256) default null,
    ueb_topic_name varchar(256) default null,
    uses_cadi char(1) default null,
    ack_app char(1) default 'Y',
    mode_of_integration varchar(50) default null,
    app_type int(11) not null default 1,
    auth_central char(1) not null default 'N',
    auth_namespace varchar(100) null default null
  );
  
  -- ------------------ functional menu tables -------------------
  --
  -- table structure for table fn_menu_functional
  --
  create table fn_menu_functional (
    menu_id int(11) not null auto_increment,
    column_num int(2) not null,
    text varchar(100) not null,
    parent_menu_id int(11) default null,
    url varchar(128) not null default '',
    active_yn varchar(1) not null default 'y',
    image_src varchar(100) default null,
    primary key (menu_id),
    key fk_fn_menu_func_parent_menu_id_idx (parent_menu_id),
    constraint fk_fn_menu_func_parent_menu_id foreign key (parent_menu_id) references fn_menu_functional (menu_id) on delete no action on update no action
  );
  --
  -- table structure for table fn_menu_functional_ancestors
  --
  
  create table fn_menu_functional_ancestors (
    id int(11) not null auto_increment,
    menu_id int(11) not null,
    ancestor_menu_id int(11) not null,
    depth int(2) not null,
    primary key (id),
    key fk_fn_menu_func_anc_menu_id_idx (menu_id),
    key fk_fn_menu_func_anc_anc_menu_id_idx (ancestor_menu_id),
    constraint fk_fn_menu_func_anc_anc_menu_id foreign key (ancestor_menu_id) references fn_menu_functional (menu_id) on delete no action on update no action,
    constraint fk_fn_menu_func_anc_menu_id foreign key (menu_id) references fn_menu_functional (menu_id) on delete no action on update no action
  );
  --
  -- table structure for table fn_menu_functional_roles
  --
  create table fn_menu_functional_roles (
    id int(11) not null auto_increment,
    menu_id int(11) not null,
    app_id int(11) not null,
    role_id int(10) not null,
    primary key (id),
    key fk_fn_menu_func_roles_menu_id_idx (menu_id),
    key fk_fn_menu_func_roles_app_id_idx (app_id),
    key fk_fn_menu_func_roles_role_id_idx (role_id),
    constraint fk_fn_menu_func_roles_app_id foreign key (app_id) references fn_app (app_id) on delete no action on update no action,
    constraint fk_fn_menu_func_roles_menu_id foreign key (menu_id) references fn_menu_functional (menu_id) on delete no action on update no action,
    constraint fk_fn_menu_func_roles_role_id foreign key (role_id) references fn_role (role_id) on delete no action on update no action
  );
  -- ----------------------------------------------------------
  -- NAME: FN_WORKFLOW; TYPE: TABLE
  -- ----------------------------------------------------------
  create table fn_workflow (
    id mediumint(9) not null auto_increment,
    name varchar(20) not null,
    description varchar(500) default null,
    run_link varchar(300) default null,
    suspend_link varchar(300) default null,
    modified_link varchar(300) default null,
    active_yn varchar(300) default null,
    created varchar(300) default null,
    created_by int(11) default null,
    modified varchar(300) default null,
    modified_by int(11) default null,
    workflow_key varchar(50) default null,
    primary key (id),
    UNIQUE KEY name (name)
  );
  
  
  -- ----------------------------------------------------------
  -- NAME: FN_SCHEDULE_WORKFLOWS; TYPE: TABLE
  -- ----------------------------------------------------------
  create table fn_schedule_workflows (
    id_schedule_workflows bigint(25) primary key not null auto_increment, 
    workflow_server_url varchar(45) default null,
    workflow_key varchar(45) not null,
    workflow_arguments varchar(45) default null,
    startDateTimeCron varchar(45) default null,
    endDateTime TIMESTAMP default NOW(),
    start_date_time TIMESTAMP default NOW(),
    recurrence varchar(45) default null
    );
  
  
  -- ----------------------------------------------------------
  -- NAME: FN_SHARED_CONTEXT; TYPE: TABLE
  -- ----------------------------------------------------------  
  create table fn_shared_context (
      id int(11) not null auto_increment,
      create_time timestamp not null,
      context_id character varying(64) not null,
      ckey character varying(128) not null,
  	cvalue character varying(1024),
  	primary key (id),
  	UNIQUE KEY session_key (context_id, ckey) );
  
  	
  -- ----------------------------------------------------------
  -- NAME: FN_QZ_JOB_DETAILS; TYPE: TABLE
  -- ----------------------------------------------------------
  create table fn_qz_job_details (
  SCHED_NAME VARCHAR(120) not null,
  JOB_NAME VARCHAR(200) not null,
  JOB_GROUP VARCHAR(200) not null,
  DESCRIPTION VARCHAR(250) null,
  JOB_CLASS_NAME VARCHAR(250) not null,
  IS_DURABLE VARCHAR(1) not null,
  IS_NONCONCURRENT VARCHAR(1) not null,
  IS_UPDATE_DATA VARCHAR(1) not null,
  REQUESTS_RECOVERY VARCHAR(1) not null,
  JOB_DATA BLOB null,
  primary key (SCHED_NAME,JOB_NAME,JOB_GROUP)
  );
    
  -- ----------------------------------------------------------
  -- NAME: FN_QZ_TRIGGERS; TYPE: TABLE
  -- ----------------------------------------------------------
  create table fn_qz_triggers (
  SCHED_NAME VARCHAR(120) not null,
  TRIGGER_NAME VARCHAR(200) not null,
  TRIGGER_GROUP VARCHAR(200) not null,
  JOB_NAME VARCHAR(200) not null,
  JOB_GROUP VARCHAR(200) not null,
  DESCRIPTION VARCHAR(250) null,
  NEXT_FIRE_TIME BIGINT(13) null,
  PREV_FIRE_TIME BIGINT(13) null,
  PRIORITY INTEGER null,
  TRIGGER_STATE VARCHAR(16) not null,
  TRIGGER_TYPE VARCHAR(8) not null,
  START_TIME BIGINT(13) not null,
  END_TIME BIGINT(13) null,
  CALENDAR_NAME VARCHAR(200) null,
  MISFIRE_INSTR SMALLINT(2) null,
  JOB_DATA BLOB null,
  primary key (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP),
  FOREIGN KEY (SCHED_NAME,JOB_NAME,JOB_GROUP)
  REFERENCES FN_QZ_JOB_DETAILS(SCHED_NAME,JOB_NAME,JOB_GROUP)
  );
    
  -- ----------------------------------------------------------
  -- NAME: FN_QZ_SIMPLE_TRIGGERS; TYPE: TABLE
  -- ----------------------------------------------------------
  create table fn_qz_simple_triggers (
  SCHED_NAME VARCHAR(120) not null,
  TRIGGER_NAME VARCHAR(200) not null,
  TRIGGER_GROUP VARCHAR(200) not null,
  REPEAT_COUNT BIGINT(7) not null,
  REPEAT_INTERVAL BIGINT(12) not null,
  TIMES_TRIGGERED BIGINT(10) not null,
  primary key (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP),
  FOREIGN KEY (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP)
  REFERENCES FN_QZ_TRIGGERS(SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP)
  );
    
  -- ----------------------------------------------------------
  -- NAME: FN_QZ_CRON_TRIGGERS; TYPE: TABLE
  -- ----------------------------------------------------------
  create table fn_qz_cron_triggers (
  SCHED_NAME VARCHAR(120) not null,
  TRIGGER_NAME VARCHAR(200) not null,
  TRIGGER_GROUP VARCHAR(200) not null,
  CRON_EXPRESSION VARCHAR(120) not null,
  TIME_ZONE_ID VARCHAR(80),
  primary key (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP),
  FOREIGN KEY (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP)
  REFERENCES FN_QZ_TRIGGERS(SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP)
  );
    
  -- ----------------------------------------------------------
  -- NAME: FN_QZ_SIMPROP_TRIGGERS; TYPE: TABLE
  -- ----------------------------------------------------------
  create table fn_qz_simprop_triggers (          
      SCHED_NAME VARCHAR(120) not null,
      TRIGGER_NAME VARCHAR(200) not null,
      TRIGGER_GROUP VARCHAR(200) not null,
      STR_PROP_1 VARCHAR(512) null,
      STR_PROP_2 VARCHAR(512) null,
      STR_PROP_3 VARCHAR(512) null,
      INT_PROP_1 INT null,
      INT_PROP_2 INT null,
      LONG_PROP_1 BIGINT null,
      LONG_PROP_2 BIGINT null,
      DEC_PROP_1 NUMERIC(13,4) null,
      DEC_PROP_2 NUMERIC(13,4) null,
      BOOL_PROP_1 VARCHAR(1) null,
      BOOL_PROP_2 VARCHAR(1) null,
      primary key (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP),
      FOREIGN KEY (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP) 
      REFERENCES FN_QZ_TRIGGERS(SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP)
  );
    
  -- ----------------------------------------------------------
  -- NAME: FN_QZ_BLOB_TRIGGERS; TYPE: TABLE
  -- ----------------------------------------------------------
  create table fn_qz_blob_triggers (
  SCHED_NAME VARCHAR(120) not null,
  TRIGGER_NAME VARCHAR(200) not null,
  TRIGGER_GROUP VARCHAR(200) not null,
  BLOB_DATA BLOB null,
  primary key (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP),
  INDEX (SCHED_NAME,TRIGGER_NAME, TRIGGER_GROUP),
  FOREIGN KEY (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP)
  REFERENCES FN_QZ_TRIGGERS(SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP)
  );
    
  -- ----------------------------------------------------------
  -- NAME: FN_QZ_CALENDARS; TYPE: TABLE
  -- ----------------------------------------------------------
  create table fn_qz_calendars (
  SCHED_NAME VARCHAR(120) not null,
  CALENDAR_NAME VARCHAR(200) not null,
  CALENDAR BLOB not null,
  primary key (SCHED_NAME,CALENDAR_NAME)
  );
  
  
  -- ----------------------------------------------------------
  -- NAME: FN_QZ_PAUSED_TRIGGER_GRPS; TYPE: TABLE
  -- ----------------------------------------------------------
  create table fn_qz_paused_trigger_grps (
  SCHED_NAME VARCHAR(120) not null,
  TRIGGER_GROUP VARCHAR(200) not null,
  primary key (SCHED_NAME,TRIGGER_GROUP)
  );
  
  -- ----------------------------------------------------------
  -- NAME: FN_QZ_FIRED_TRIGGERS; TYPE: TABLE
  -- ----------------------------------------------------------
  create table fn_qz_fired_triggers (
  SCHED_NAME VARCHAR(120) not null,
  ENTRY_ID VARCHAR(95) not null,
  TRIGGER_NAME VARCHAR(200) not null,
  TRIGGER_GROUP VARCHAR(200) not null,
  INSTANCE_NAME VARCHAR(200) not null,
  FIRED_TIME BIGINT(13) not null,
  SCHED_TIME BIGINT(13) not null,
  PRIORITY INTEGER not null,
  STATE VARCHAR(16) not null,
  JOB_NAME VARCHAR(200) null,
  JOB_GROUP VARCHAR(200) null,
  IS_NONCONCURRENT VARCHAR(1) null,
  REQUESTS_RECOVERY VARCHAR(1) null,
  primary key (SCHED_NAME,ENTRY_ID)
  );
  
  -- ----------------------------------------------------------
  -- NAME: FN_QZ_SCHEDULER_STATE; TYPE: TABLE
  -- ----------------------------------------------------------
  create table fn_qz_scheduler_state (
  SCHED_NAME VARCHAR(120) not null,
  INSTANCE_NAME VARCHAR(200) not null,
  LAST_CHECKIN_TIME BIGINT(13) not null,
  CHECKIN_INTERVAL BIGINT(13) not null,
  primary key (SCHED_NAME,INSTANCE_NAME)
  );
  
  -- ----------------------------------------------------------
  -- NAME: FN_QZ_LOCKS; TYPE: TABLE
  -- ----------------------------------------------------------
  create table fn_qz_locks (
  SCHED_NAME VARCHAR(120) not null,
  LOCK_NAME VARCHAR(40) not null,
  primary key (SCHED_NAME,LOCK_NAME)
  );
  
  -- ----------------------------------------------------------
  -- NAME: FN_MENU_FAVORITES; TYPE: TABLE
  -- ----------------------------------------------------------
  
  create table fn_menu_favorites (
    user_id int(11) not null,
    menu_id int(11) not null,
    primary key (user_id,menu_id)
  );
  
  -- FACELIFT - Table for Events, News and Resources
  
  create table fn_common_widget_data(
  	id int auto_increment, 
  	category varchar(32), 
  	href varchar(512), 
  	title varchar(256), 
  	content varchar(4096), 
  	event_date varchar(10), -- YYYY-MM-DD 
  	sort_order int, 
  	primary key (id)
  );
  
  create table fn_app_contact_us (
    app_id int(11) not null,
    contact_name varchar(128) default null,
    contact_email varchar(128) default null,
    url varchar(256) default null,
    active_yn varchar(2) default null,
    description varchar(1024) default null,
    primary key (app_id),
    constraint fk_fn_a_con__ref_202_fn_app foreign key (app_id) references fn_app (app_id)
  );
  
  -- new 1610.2
  create table fn_pers_user_app_sel (
    id   int(11) not null auto_increment,
    user_id   int(11) not null,
    app_id    int(11) not null,
    status_cd char(1) not null,
    primary key(id),
    constraint fk_1_fn_pers_user_app_sel_fn_user foreign key (user_id) references fn_user (user_id),
    constraint fk_2_fn_pers_user_app_sel_fn_app  foreign key (app_id)  references fn_app (app_id)
  );
  
  -- end new 1610.2
  
  -- new 1702 tables/views
   -- 1702 Additions for User Notifications
   -- ----------------------------------------------------------
   -- NAME: ep_notification; TYPE: TABLE
   -- ----------------------------------------------------------
   create table ep_notification (
      notification_ID     int(11) primary key not null auto_increment,
      is_for_online_users char(1) default 'N',
      is_for_all_roles    char(1) default 'N',
      active_YN           char(1) default 'Y',
      msg_header          varchar(100),
      msg_description     varchar(2000),
      msg_source          varchar(50) default 'EP',
      start_time          timestamp default now(),
      end_time            timestamp null,
      priority            int(11),
      creator_ID          int(11) null default null,
      created_date        timestamp null default null,
      notification_hyperlink varchar(512) null default null  -- new column for 1710
     );
  
   -- ----------------------------------------------------------
   -- NAME: ep_role_notification; TYPE: TABLE
   -- ----------------------------------------------------------
   create table ep_role_notification (
     ID                  int(11) primary key not null auto_increment,
     notification_ID     int(11),
     role_ID             int(11),
     recv_user_id        int(11) null,
     constraint fk_ep_role_notif_fn_role foreign key (role_ID) references fn_role(role_id),
     constraint fk_ep_role_notif_fn_notif foreign key (notification_ID) references ep_notification(notification_ID)
     );
     
   -- ----------------------------------------------------------
   -- NAME: ep_user_notification; TYPE: TABLE
   -- ----------------------------------------------------------
   create table ep_user_notification (
      ID                  int(11) primary key not null auto_increment,
      User_ID             int(11),
      notification_ID     int(11),
      is_viewed           char(1) default 'N',
      updated_time        timestamp default now(),
      constraint fk_ep_urole_notif_fn_user foreign key (User_ID) references fn_user(user_id),
      constraint fk_ep_urole_notif_fn_notif foreign key (notification_ID) references ep_notification(notification_ID)
     );
  
   -- ----------------------------------------------------------
   -- NAME: ep_pers_user_app_sort; TYPE: Table
   -- ----------------------------------------------------------
  
   CREATE TABLE ep_pers_user_app_sort (
     id        int(11) not null primary key auto_increment,
     user_id   int(11) not null,
     sort_pref char(1) not null,
     unique key uk_1_ep_pers_user_app_sort (user_id),
     constraint fk_ep_pers_user_app_sort_fn_user foreign key (user_id) references fn_user(user_id)
   );
   -- ----------------------------------------------------------
   -- NAME: ep_pers_user_app_man_sort; TYPE: Table
   -- ----------------------------------------------------------
  
   CREATE TABLE ep_pers_user_app_man_sort (
     id         int(11) not null primary key auto_increment,
     user_id    int(11) not null,
     app_id     int(11) not null,
     sort_order int(11) not null,
     unique key uk_1_ep_pers_user_app_man_sort (user_id, app_id),
     constraint fk_ep_pers_app_man_sort_fn_user foreign key (user_id) references fn_user(user_id),
     constraint fk_ep_pers_app_man_sort_fn_app foreign key (app_id) references fn_app(app_id)
   );
  
   -- ----------------------------------------------------------
   -- NAME: ep_widget_catalog; TYPE: Table
   -- ----------------------------------------------------------
  
   CREATE TABLE ep_widget_catalog (
     widget_id int(11) not null auto_increment,
     wdg_name varchar(100) not null default '?',
     service_id int(11) default null,
     wdg_desc varchar(200) default null,
     wdg_file_loc varchar(256) not null default '?',
     all_user_flag char(1) not null default 'N',
     primary key (widget_id)
   );
   
   -- ----------------------------------------------------------
   -- NAME: ep_widget_catalog_role; TYPE: Table
   -- ----------------------------------------------------------
   create table ep_widget_catalog_role (
     widget_id 	int(10) not null,
     app_id 		int(11) default '1',
     role_id 		int(10) not null,
     key fk_ep_widget_catalog_role_fn_widget (widget_id),
     key fk_ep_widget_catalog_role_ref_fn_role (role_id),
     key fk_ep_widget_catalog_role_app_id  (app_id),
     constraint fk_ep_widget_catalog_role_fn_widget foreign key (widget_id) references ep_widget_catalog (widget_id),
     constraint fk_ep_widget_catalog_role_ref_fn_role foreign key (role_id) references fn_role (role_id),
     constraint fk_ep_widget_catalog_role_app_id foreign key (app_id) references fn_app (app_id)
   );
  
   -- ----------------------------------------------------------
   -- NAME: ep_pers_user_widget_placement; TYPE: Table
   -- ----------------------------------------------------------
   CREATE TABLE ep_pers_user_widget_placement (
     id        int(11) not null primary key auto_increment,
     user_id   int(11) not null,
     widget_id int(11) not null,
     x         int(11) not null,
     y         int(11),
     height    int(11),
     width     int(11),
     unique key uk_1_ep_pers_user_widg_place (user_id, widget_id),
     constraint fk_ep_pers_user_widg_place_fn_user foreign key (user_id) references fn_user(user_id),
     constraint fk_ep_pers_user_widg_place_ep_widg foreign key (widget_id) references ep_widget_catalog(widget_id)
   );
  
   -- ----------------------------------------------------------
   -- NAME: ep_pers_user_widget_sel; TYPE: TABLE
   -- ----------------------------------------------------------
   CREATE TABLE ep_pers_user_widget_sel (
     id        int(11) not null primary key auto_increment,
     user_id   int(11) not null,
     widget_id int(11) not null,
     status_cd char(1) not null,
     unique key uk_1_ep_pers_user_widg_sel_user_widg (user_id, widget_id),
     CONSTRAINT fk_1_ep_pers_user_wid_sel_fn_user FOREIGN KEY (user_id) REFERENCES fn_user (user_id),
     CONSTRAINT fk_2_ep_pers_user_wid_sel_ep_wid FOREIGN KEY (widget_id) REFERENCES ep_widget_catalog (widget_id)
   );
  
   -- ----------------------------------------------------------
   -- NAME: ep_widget_catalog_files; TYPE: TABLE
   -- ----------------------------------------------------------
   CREATE TABLE ep_widget_catalog_files (
          file_id            	int(11) not null primary key auto_increment,
          widget_id          	int(11),
  		widget_name 		VARCHAR(100) NOT NULL,
  		framework_js 		LONGBLOB NULL,
  		controller_js 		LONGBLOB NULL,
  		markup_html 		LONGBLOB NULL,
  		widget_css 			LONGBLOB NULL
   );
  
   -- ----------------------------------------------------------
   -- NAME: fn_role_v; TYPE: VIEW
   -- All roles without an APP_ID are Portal only.
   -- ----------------------------------------------------------
   create view fn_role_v as
    select fn_role.role_id as role_id,
           fn_role.role_name as role_name,
                  fn_role.active_yn as active_yn,
           fn_role.priority as priority,
           fn_role.app_id as app_id,
           fn_role.app_role_id as app_role_id
   from fn_role where isnull(fn_role.app_id);
  
  -- end new 1702 tables/views
  
  -- new 1707 tables/views
  
   -- ----------------------------------------------------------
   -- NAME: ep_user_roles_request; TYPE: TABLE
   -- ----------------------------------------------------------
  
  create table ep_user_roles_request (
      req_id int(11) not null primary key auto_increment,
      user_id int(11) not null,
      app_id int(11) not null,
  	created_date timestamp default now(),
  	updated_date timestamp default now(),
      request_status character varying(50) not null,
  	constraint fk_user_roles_req_fn_user foreign key (user_id) references fn_user(user_id),
  	constraint fk_user_roles_req_fn_app foreign key (app_id) references fn_app(app_id)
      );
    
    
   -- ----------------------------------------------------------
   -- NAME: ep_user_roles_request_det; TYPE: TABLE
   -- ----------------------------------------------------------
  create table ep_user_roles_request_det (
      id int(11) not null primary key auto_increment,
      req_id int(11) default null,
  	requested_role_id int(10) not null,
      request_type character varying(10) not null,
      constraint fk_user_roles_req_fn_req_id foreign key (req_id) references ep_user_roles_request(req_id),
      constraint fk_user_roles_req_fn_role_id foreign key (requested_role_id) references fn_role(role_id)
      );
  	
   -- ----------------------------------------------------------
   -- NAME: ep_microservice; TYPE: TABLE
   -- ----------------------------------------------------------
  
  CREATE TABLE ep_microservice (
  	id INT(11) NOT NULL AUTO_INCREMENT,
  	name VARCHAR(50) NULL DEFAULT NULL,
  	description VARCHAR(50) NULL DEFAULT NULL,
  	appId INT(11) NULL DEFAULT NULL,
  	endpoint_url VARCHAR(200) NULL DEFAULT NULL,
  	security_type VARCHAR(50) NULL DEFAULT NULL,
  	username VARCHAR(50) NULL DEFAULT NULL,
  	password VARCHAR(50) NULL DEFAULT NULL,
  	active CHAR(1) NOT NULL DEFAULT 'Y',
  	PRIMARY KEY (id),
  	CONSTRAINT FK_FN_APP_EP_MICROSERVICE FOREIGN KEY (appId) REFERENCES fn_app (app_id)
  );
  
   -- ----------------------------------------------------------
   -- NAME: ep_microservice_parameter; TYPE: TABLE
   -- ----------------------------------------------------------
   
  CREATE TABLE ep_microservice_parameter (
  	id INT(11) NOT NULL AUTO_INCREMENT,
  	service_id INT(11) NULL DEFAULT NULL,
  	para_key VARCHAR(50) NULL DEFAULT NULL,
  	para_value VARCHAR(50) NULL DEFAULT NULL,
  	PRIMARY KEY (id),
  	CONSTRAINT FK_EP_MICROSERIVCE_EP_MICROSERVICE_PARAMETER FOREIGN KEY (service_id) REFERENCES ep_microservice (id)
  );
  
  
   -- ----------------------------------------------------------
   -- NAME: ep_widget_preview_files; TYPE: TABLE
   -- ----------------------------------------------------------
   
  CREATE TABLE ep_widget_preview_files (
  	preview_id INT(11) NOT NULL AUTO_INCREMENT,
  	html_file LONGBLOB NULL,
  	css_file LONGBLOB NULL,
  	javascript_file LONGBLOB NULL,
  	framework_file LONGBLOB NULL,
  	PRIMARY KEY (preview_id)
  );
  
   -- ----------------------------------------------------------
   -- NAME: ep_widget_microservice; TYPE: TABLE
   -- ----------------------------------------------------------
  
  CREATE TABLE ep_widget_microservice (
  	id INT(11) NOT NULL AUTO_INCREMENT,
  	widget_id INT(11) NOT NULL DEFAULT '0',
  	microservice_id INT(11) NOT NULL DEFAULT '0',
  	PRIMARY KEY (id),
  	CONSTRAINT FK_EP_WIDGET_MICROSERVICE_EP_MICROSERVICE FOREIGN KEY (microservice_id) REFERENCES ep_microservice (id),
  	CONSTRAINT FK_EP_WIDGET_MICROSERVICE_EP_WIDGET FOREIGN KEY (widget_id) REFERENCES ep_widget_catalog (widget_id)
  );
  
   -- ----------------------------------------------------------
   -- NAME: ep_basic_auth_account; TYPE: TABLE
   -- ----------------------------------------------------------
   
  create table ep_basic_auth_account (
  	id INT(11) NOT NULL AUTO_INCREMENT,
  	ext_app_name VARCHAR(50) NOT NULL,
  	username VARCHAR(50) NOT NULL,
  	password VARCHAR(50) null default null,
      active_yn char(1) NOT NULL default 'Y',
  	PRIMARY KEY (id)
  );
  
   -- ----------------------------------------------------------
   -- NAME: ep_widget_catalog_parameter; TYPE: TABLE
   -- ----------------------------------------------------------
   
  create table  ep_widget_catalog_parameter (
  	id INT(11) NOT NULL AUTO_INCREMENT,
  	widget_id INT(11) NOT NULL,
  	user_id INT(11) NOT NULL,
  	param_id INT(11) NOT NULL,
      user_value VARCHAR(50) NULL,
  	PRIMARY KEY (id),
  	CONSTRAINT EP_FN_USER_WIDGET_PARAMETER_FK FOREIGN KEY (user_id) REFERENCES fn_user (user_id),
  	CONSTRAINT EP_WIDGET_CATALOG_WIDGET_PARAMETER_FK FOREIGN KEY (widget_id) REFERENCES ep_widget_catalog (widget_id),
  	CONSTRAINT EP_PARAMETER_ID_WIDGET_PARAMETER_FK FOREIGN KEY (param_id) REFERENCES ep_microservice_parameter (id)
  );
  
   -- ----------------------------------------------------------
   -- NAME: ep_web_analytics_source; TYPE: TABLE
   -- ----------------------------------------------------------
   
  create table ep_web_analytics_source(
  	resource_id int(11) NOT NULL auto_increment,
  	app_id int(11) NOT NULL,
      report_source varchar(500),
      report_name  varchar(500),
      PRIMARY KEY (resource_id),
  	FOREIGN KEY (app_id) REFERENCES fn_app(app_id)
  ); 
  
   -- Machine Learning Tables
   -- ----------------------------------------------------------
   -- NAME: ep_ml_model; TYPE: TABLE
   -- ----------------------------------------------------------
   
  create table ep_ml_model(
    time_stamp timestamp default now(),
    group_id int(11) NOT NULL,
    model longblob,
    PRIMARY KEY (time_stamp,group_id)
  );
   -- ----------------------------------------------------------
   -- NAME: ep_ml_rec; TYPE: TABLE
   -- ----------------------------------------------------------
   
  create table ep_ml_rec(
    time_stamp timestamp default now(),
    org_user_id varchar(20) NOT NULL,
    rec varchar(4000) DEFAULT NULL,
    PRIMARY KEY (time_stamp,org_user_id)
  );
  
   -- ----------------------------------------------------------
   -- NAME: ep_ml_user; TYPE: TABLE
   -- ----------------------------------------------------------
   
  create table ep_ml_user(
    time_stamp timestamp default now(),
    org_user_id varchar(20) NOT NULL,
    group_id int(11) NOT NULL,
    PRIMARY KEY (time_stamp,org_user_id)
  );
  
   -- ----------------------------------------------------------
   -- NAME: ep_endpoints; TYPE: TABLE
   -- ----------------------------------------------------------
   
  create table  ep_endpoints (
  	id INT(11) NOT NULL AUTO_INCREMENT,
      url VARCHAR(50) NOT NULL,
  	PRIMARY KEY (id)
  );
  
   -- ----------------------------------------------------------
   -- NAME: ep_endpoints_basic_auth_account; TYPE: TABLE
   -- ----------------------------------------------------------
  
  create table  ep_endpoints_basic_auth_account (
  	id INT(11) NOT NULL AUTO_INCREMENT,
  	ep_id INT(11) DEFAULT NULL,
  	account_id INT(11) DEFAULT NULL,
  	PRIMARY KEY (id),
  	CONSTRAINT ep_endpoints_basic_auth_account_account_id_fk FOREIGN KEY (account_id) REFERENCES ep_basic_auth_account (id),
  	CONSTRAINT ep_endpoints_basic_auth_account_ep_id_fk FOREIGN KEY (ep_id) REFERENCES ep_endpoints (id)
  
  );
  
  -- end new 1707 tables/views
  
  -- new 1710 tables/views
  
   -- ----------------------------------------------------------
   -- NAME: ep_app_function; TYPE: TABLE
   -- ----------------------------------------------------------
  
  CREATE TABLE ep_app_function (
  app_id INT(11) NOT NULL,
  function_cd VARCHAR(250) NOT NULL,
  function_name VARCHAR(500) NOT NULL,
  PRIMARY KEY (function_cd, app_id),
  INDEX fk_ep_app_function_app_id (app_id),
  CONSTRAINT fk_ep_app_function_app_id FOREIGN KEY (app_id) REFERENCES fn_app (app_id)
  );
  
   -- ----------------------------------------------------------
   -- NAME: ep_app_role_function; TYPE: TABLE
   -- ----------------------------------------------------------
  
  CREATE TABLE `ep_app_role_function` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `app_id` INT(11) NOT NULL,
  `role_id` INT(11) NOT NULL,
  `function_cd` VARCHAR(250) NOT NULL,
  `role_app_id` VARCHAR(20) NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE INDEX `UNIQUE KEY` (`app_id`, `role_id`, `function_cd`),
  CONSTRAINT `fk_ep_app_role_function_app_id` FOREIGN KEY (`app_id`) REFERENCES `fn_app` (`app_id`),
  CONSTRAINT `fk_ep_app_role_function_ep_app_func` FOREIGN KEY (`app_id`, `function_cd`) REFERENCES `ep_app_function` (`app_id`, `function_cd`),
  CONSTRAINT `fk_ep_app_role_function_role_id` FOREIGN KEY (`role_id`) REFERENCES `fn_role` (`role_id`)
  );
  
  -- end new 1710 tables/views
  
  -- ----------------------------------------------------------
  -- NAME: QUARTZ TYPE: INDEXES
  -- ----------------------------------------------------------
  create index idx_fn_qz_j_req_recovery on fn_qz_job_details(sched_name,requests_recovery);
  create index idx_fn_qz_j_grp on fn_qz_job_details(sched_name,job_group);
  create index idx_fn_qz_t_j on fn_qz_triggers(sched_name,job_name,job_group);
  create index idx_fn_qz_t_jg on fn_qz_triggers(sched_name,job_group);
  create index idx_fn_qz_t_c on fn_qz_triggers(sched_name,calendar_name);
  create index idx_fn_qz_t_g on fn_qz_triggers(sched_name,trigger_group);
  create index idx_fn_qz_t_state on fn_qz_triggers(sched_name,trigger_state);
  create index idx_fn_qz_t_n_state on fn_qz_triggers(sched_name,trigger_name,trigger_group,trigger_state);
  create index idx_fn_qz_t_n_g_state on fn_qz_triggers(sched_name,trigger_group,trigger_state);
  create index idx_fn_qz_t_next_fire_time on fn_qz_triggers(sched_name,next_fire_time);
  create index idx_fn_qz_t_nft_st on fn_qz_triggers(sched_name,trigger_state,next_fire_time);
  create index idx_fn_qz_t_nft_misfire on fn_qz_triggers(sched_name,misfire_instr,next_fire_time);
  create index idx_fn_qz_t_nft_st_misfire on fn_qz_triggers(sched_name,misfire_instr,next_fire_time,trigger_state);
  create index idx_fn_qz_t_nft_st_misfire_grp on fn_qz_triggers(sched_name,misfire_instr,next_fire_time,trigger_group,trigger_state);
  create index idx_fn_qz_ft_trig_inst_name on fn_qz_fired_triggers(sched_name,instance_name);
  create index idx_fn_qz_ft_inst_job_req_rcvry on fn_qz_fired_triggers(sched_name,instance_name,requests_recovery);
  create index idx_fn_qz_ft_j_g on fn_qz_fired_triggers(sched_name,job_name,job_group);
  create index idx_fn_qz_ft_jg on fn_qz_fired_triggers(sched_name,job_group);
  create index idx_fn_qz_ft_t_g on fn_qz_fired_triggers(sched_name,trigger_name,trigger_group);
  create index idx_fn_qz_ft_tg on fn_qz_fired_triggers(sched_name,trigger_group);
  
  
  -- ------------------ create view section
  --
  -- name: v_url_access; type: view
  --
  create view v_url_access as
   select distinct m.action as url,
      m.function_cd
     from fn_menu m
    where (m.action is not null)
  union
   select distinct t.action as url,
      t.function_cd
     from fn_tab t
    where (t.action is not null)
  union
   select r.restricted_url as url,
      r.function_cd
     from fn_restricted_url r;
     
  -- ------------------ alter table add constraint primary key section
  --
  -- name: cr_favorite_reports_user_idrep_id; type: constraint
  --
  alter table cr_favorite_reports 
      add constraint cr_favorite_reports_user_idrep_id primary key (user_id, rep_id);
  --
  -- name: cr_folder_folder_id; type: constraint
  --
  alter table cr_folder
      add constraint cr_folder_folder_id primary key (folder_id);
  --
  -- name: cr_folder_access_folder_access_id; type: constraint
  --
  alter table cr_folder_access
      add constraint cr_folder_access_folder_access_id primary key (folder_access_id);
  --
  -- name: cr_hist_user_map_hist_iduser_id; type: constraint
  --
  alter table cr_hist_user_map
      add constraint cr_hist_user_map_hist_iduser_id primary key (hist_id, user_id);
  --
  -- name: cr_lu_file_type_lookup_id; type: constraint
  --
  alter table cr_lu_file_type
      add constraint cr_lu_file_type_lookup_id primary key (lookup_id);
  --
  -- name: cr_raptor_action_img_image_id; type: constraint
  --
  alter table cr_raptor_action_img
      add constraint cr_raptor_action_img_image_id primary key (image_id);
  --
  -- name: cr_raptor_pdf_img_image_id; type: constraint
  --
  alter table cr_raptor_pdf_img
      add constraint cr_raptor_pdf_img_image_id primary key (image_id);
  --
  -- name: cr_remote_schema_info_schema_prefix; type: constraint
  --
  alter table cr_remote_schema_info
      add constraint cr_remote_schema_info_schema_prefix primary key (schema_prefix);
  --
  -- name: cr_report_rep_id; type: constraint
  --
  alter table cr_report
      add constraint cr_report_rep_id primary key (rep_id);
  --
  -- name: cr_report_access_rep_idorder_no; type: constraint
  --
  alter table cr_report_access
      add constraint cr_report_access_rep_idorder_no primary key (rep_id, order_no);
  --
  -- name: cr_report_email_sent_log_log_id; type: constraint
  --
  alter table cr_report_email_sent_log
      add constraint cr_report_email_sent_log_log_id primary key (log_id);
  --
  -- name: cr_report_file_history_hist_id; type: constraint
  --
  alter table cr_report_file_history
      add constraint cr_report_file_history_hist_id primary key (hist_id);
  --
  -- name: cr_report_schedule_schedule_id; type: constraint
  --
  alter table cr_report_schedule
      add constraint cr_report_schedule_schedule_id primary key (schedule_id);
  --
  -- name: cr_report_schedule_users_schedule_idrep_iduser_idorder_no; type: constraint
  --
  alter table cr_report_schedule_users
      add constraint cr_report_schedule_users_schedule_idrep_iduser_idorder_no primary key (schedule_id, rep_id, user_id, order_no);
  --
  -- name: cr_report_template_map_report_id; type: constraint
  --
  alter table cr_report_template_map
      add constraint cr_report_template_map_report_id primary key (report_id);
  --
  -- name: cr_table_role_table_namerole_id; type: constraint
  --
  alter table cr_table_role
      add constraint cr_table_role_table_namerole_id primary key (table_name, role_id);
  --
  -- name: cr_table_source_table_name; type: constraint
  --
  alter table cr_table_source
      add constraint cr_table_source_table_name primary key (table_name);
  --
  -- name: fn_audit_action_audit_action_id; type: constraint
  --
  alter table fn_audit_action
      add constraint fn_audit_action_audit_action_id primary key (audit_action_id);  
  --
  --
  -- name: fk_fn_audit_ref_205_fn_lu_ac; type: constraint
  --
  alter table fn_audit_log
  	add constraint fk_fn_audit_ref_205_fn_lu_ac foreign key (activity_cd) references fn_lu_activity(activity_cd);
  --
  -- name: fk_fn_role__ref_201_fn_funct; type: constraint
  --    
  alter table fn_role_function
  	add constraint fk_fn_role__ref_201_fn_funct foreign key (function_cd) references fn_function(function_cd);
  --
  -- name: fn_chat_logs_chat_log_id; type: constraint
  --
  alter table fn_chat_logs
      add constraint fn_chat_logs_chat_log_id primary key (chat_log_id);
  --
  -- name: fn_chat_room_chat_room_id; type: constraint
  --
  alter table fn_chat_room
      add constraint fn_chat_room_chat_room_id primary key (chat_room_id);
  --
  -- name: fn_chat_users_id; type: constraint
  --
  alter table fn_chat_users  
      add constraint fn_chat_users_id primary key (id);
  --
  -- name: fn_lu_alert_method_alert_method_cd; type: constraint
  --
  alter table fn_lu_alert_method
      add constraint fn_lu_alert_method_alert_method_cd primary key (alert_method_cd);
  --
  -- name: fn_lu_broadcast_site_broadcast_site_cd; type: constraint
  --
  alter table fn_lu_broadcast_site
      add constraint fn_lu_broadcast_site_broadcast_site_cd primary key (broadcast_site_cd);
  --
  -- name: fn_lu_priority_priority_id; type: constraint
  --
  alter table fn_lu_priority
      add constraint fn_lu_priority_priority_id primary key (priority_id);
  --
  -- name: fn_lu_role_type_role_type_id; type: constraint
  --
  alter table fn_lu_role_type
      add constraint fn_lu_role_type_role_type_id primary key (role_type_id);
  --
  -- name: fn_lu_tab_set_tab_set_cd; type: constraint
  --
  alter table fn_lu_tab_set
      add constraint fn_lu_tab_set_tab_set_cd primary key (tab_set_cd);
  --
  -- name: fn_lu_timezone_timezone_id; type: constraint
  --
  alter table fn_lu_timezone
      add constraint fn_lu_timezone_timezone_id primary key (timezone_id);
  --
  -- name: fn_org_org_id; type: constraint
  --
  alter table fn_org
      add constraint fn_org_org_id primary key (org_id);
  --
  -- name: fn_restricted_url_restricted_urlfunction_cd; type: constraint
  --
  alter table fn_restricted_url
      add constraint fn_restricted_url_restricted_urlfunction_cd primary key (restricted_url, function_cd);
  --
  -- name: fn_role_composite_parent_role_idchild_role_id; type: constraint
  --
  alter table fn_role_composite
      add constraint fn_role_composite_parent_role_idchild_role_id primary key (parent_role_id, child_role_id);
  --
  -- name: fn_role_function_role_idfunction_cd; type: constraint
  --
  alter table fn_role_function
      add constraint fn_role_function_role_idfunction_cd primary key (role_id, function_cd);
  --
  -- name: fn_tab_tab_cd; type: constraint
  --
  alter table fn_tab
      add constraint fn_tab_tab_cd primary key (tab_cd);
  --
  -- name: fn_tab_selected_selected_tab_cdtab_uri; type: constraint
  --
  alter table fn_tab_selected
      add constraint fn_tab_selected_selected_tab_cdtab_uri primary key (selected_tab_cd, tab_uri);
  --
  -- name: fn_user_pseudo_role_pseudo_role_iduser_id; type: constraint
  --
  alter table fn_user_pseudo_role
      add constraint fn_user_pseudo_role_pseudo_role_iduser_id primary key (pseudo_role_id, user_id);
  --
  -- name: fn_user_role_user_idrole_id; type: constraint
  --
  alter table fn_user_role
      add constraint fn_user_role_user_idrole_id primary key (user_id, role_id, app_id);
  -- ------------------ create index section
  --
  -- name: cr_report_create_idpublic_yntitle; type: index
  --
  create index cr_report_create_idpublic_yntitle using btree on cr_report (create_id, public_yn, title);
  --
  -- name: cr_table_join_dest_table_name; type: index
  --
  create index cr_table_join_dest_table_name using btree on cr_table_join (dest_table_name);
  --
  -- name: cr_table_join_src_table_name; type: index
  --
  create index cr_table_join_src_table_name using btree on cr_table_join (src_table_name);
  --
  -- name: fn_audit_log_activity_cd; type: index
  --
  create index fn_audit_log_activity_cd using btree on fn_audit_log (activity_cd);
  --
  -- name: fn_audit_log_user_id; type: index
  --
  create index fn_audit_log_user_id using btree on fn_audit_log (user_id);
  --
  -- name: fn_org_access_cd; type: index
  --
  create index fn_org_access_cd using btree on fn_org (access_cd);
  --
  -- name: fn_role_function_function_cd; type: index
  --
  create index fn_role_function_function_cd using btree on fn_role_function (function_cd);
  --
  -- name: fn_role_function_role_id; type: index
  --
  create index fn_role_function_role_id using btree on fn_role_function (role_id);
  --
  -- name: fn_user_address_id; type: index
  --
  create index fn_user_address_id using btree on fn_user (address_id); 
  --
  -- name: fn_user_alert_method_cd; type: index
  --
  create index fn_user_alert_method_cd using btree on fn_user (alert_method_cd); 
  --
  -- name: fn_user_hrid; type: index
  --
  create unique index fn_user_hrid using btree on fn_user (hrid); 
  --
  -- name: fn_user_login_id; type: index
  --
  create unique index fn_user_login_id using btree on fn_user (login_id); 
  --
  -- name: fn_user_org_id; type: index
  --
  create index fn_user_org_id using btree on fn_user (org_id); 
  --
  -- name: fn_user_role_role_id; type: index
  --
  create index fn_user_role_role_id using btree on fn_user_role (role_id);
  --
  -- name: fn_user_role_user_id; type: index
  --
  create index fn_user_role_user_id using btree on fn_user_role (user_id);
  --
  -- name: fk_fn_user__ref_178_fn_app_idx; type: index
  --
  create index fk_fn_user__ref_178_fn_app_idx on fn_user_role (app_id);
   --
   -- name: fn_role_name_app_id_idx; type: index
   --
   create unique index fn_role_name_app_id_idx using btree on fn_role (role_name,app_id);
   
  -- new for 1707
   
  create index ep_notif_recv_user_id_idx using btree on ep_role_notification (recv_user_id);
    
  -- end new for 1707
  
  -- ------------------ alter table add constraint foreign key section
  --
  -- name: fk_fn_user__ref_178_fn_app; type: fk constraint
  --
  alter table fn_user_role
  	add constraint fk_fn_user__ref_178_fn_app foreign key (app_id) references fn_app(app_id);
  --
  -- name: fk_cr_repor_ref_14707_cr_repor; type: fk constraint
  --
  alter table cr_report_schedule
      add constraint fk_cr_repor_ref_14707_cr_repor foreign key (rep_id) references cr_report(rep_id);
  --
  -- name: fk_cr_repor_ref_14716_cr_repor; type: fk constraint
  --
  alter table cr_report_schedule_users
      add constraint fk_cr_repor_ref_14716_cr_repor foreign key (schedule_id) references cr_report_schedule(schedule_id);
  --
  -- name: fk_cr_repor_ref_17645_cr_repor; type: fk constraint
  --
  alter table cr_report_log
      add constraint fk_cr_repor_ref_17645_cr_repor foreign key (rep_id) references cr_report(rep_id);
  --
  -- name: fk_cr_repor_ref_8550_cr_repor; type: fk constraint
  --
  alter table cr_report_access
      add constraint fk_cr_repor_ref_8550_cr_repor foreign key (rep_id) references cr_report(rep_id);
  --
  -- name: fk_cr_report_rep_id; type: fk constraint
  --
  alter table cr_report_email_sent_log
      add constraint fk_cr_report_rep_id foreign key (rep_id) references cr_report(rep_id);
  --
  -- name: fk_cr_table_ref_311_cr_tab; type: fk constraint
  --
  alter table cr_table_join
      add constraint fk_cr_table_ref_311_cr_tab foreign key (src_table_name) references cr_table_source(table_name);
  --
  -- name: fk_cr_table_ref_315_cr_tab; type: fk constraint
  --
  alter table cr_table_join
      add constraint fk_cr_table_ref_315_cr_tab foreign key (dest_table_name) references cr_table_source(table_name);
  --
  -- name: fk_cr_table_ref_32384_cr_table; type: fk constraint
  --
  alter table cr_table_role
      add constraint fk_cr_table_ref_32384_cr_table foreign key (table_name) references cr_table_source(table_name);
  --
  -- name: fk_fn_tab_function_cd; type: fk constraint
  --
  alter table fn_tab
      add constraint fk_fn_tab_function_cd foreign key (function_cd) references fn_function(function_cd);
  --
  -- name: fk_fn_tab_selected_tab_cd; type: fk constraint
  --
  alter table fn_tab_selected
      add constraint fk_fn_tab_selected_tab_cd foreign key (selected_tab_cd) references fn_tab(tab_cd);
  --
  -- name: fk_fn_tab_set_cd; type: fk constraint
  --
  alter table fn_tab
      add constraint fk_fn_tab_set_cd foreign key (tab_set_cd) references fn_lu_tab_set(tab_set_cd);
  --
  -- name: fk_fn_user_ref_110_fn_org; type: fk constraint
  -- 
  alter table fn_user
      add constraint fk_fn_user_ref_110_fn_org foreign key (org_id) references fn_org(org_id); 
  --
  -- name: fk_fn_user_ref_123_fn_lu_al; type: fk constraint
  --
  alter table fn_user
      add constraint fk_fn_user_ref_123_fn_lu_al foreign key (alert_method_cd) references fn_lu_alert_method(alert_method_cd); 
  --
  -- name: fk_fn_user_ref_197_fn_user; type: fk constraint
  --
   alter table fn_user
      add constraint fk_fn_user_ref_197_fn_user foreign key (manager_id) references fn_user(user_id); 
  --
  -- name: fk_fn_user_ref_198_fn_user; type: fk constraint
  --
  alter table fn_user  
      add constraint fk_fn_user_ref_198_fn_user foreign key (created_id) references fn_user(user_id); 
  --
  -- name: fk_fn_user_ref_199_fn_user; type: fk constraint
  --
  alter table fn_user  
      add constraint fk_fn_user_ref_199_fn_user foreign key (modified_id) references fn_user(user_id); 
  --
  -- name: fk_parent_key_cr_folder; type: fk constraint
  --
  alter table cr_folder
      add constraint fk_parent_key_cr_folder foreign key (parent_folder_id) references cr_folder(folder_id);
  --
  -- name: fk_pseudo_role_pseudo_role_id; type: fk constraint
  --
  alter table fn_user_pseudo_role 
      add constraint fk_pseudo_role_pseudo_role_id foreign key (pseudo_role_id) references fn_role(role_id);
  --
  -- name: fk_pseudo_role_user_id; type: fk constraint
  --
  alter table fn_user_pseudo_role 
      add constraint fk_pseudo_role_user_id foreign key (user_id) references fn_user(user_id);
  --
  -- name: fk_restricted_url_function_cd; type: fk constraint
  --
  alter table fn_restricted_url
      add constraint fk_restricted_url_function_cd foreign key (function_cd) references fn_function(function_cd);
  --
  -- name: fk_timezone; type: fk constraint
  --
  alter table fn_user
      add constraint fk_timezone foreign key (timezone) references fn_lu_timezone(timezone_id); 
  --
  -- name: sys_c0014614; type: fk constraint
  --
  alter table cr_report_file_history
      add constraint sys_c0014614 foreign key (file_type_id) references cr_lu_file_type(lookup_id);
  --
  -- name: sys_c0014615; type: fk constraint
  --
  alter table cr_report_file_history
      add constraint sys_c0014615 foreign key (rep_id) references cr_report(rep_id);
  --
  -- name: sys_c0014616; type: fk constraint
  --
  alter table cr_hist_user_map  
      add constraint sys_c0014616 foreign key (hist_id) references cr_report_file_history(hist_id);
  --
  -- name: sys_c0014617; type: fk constraint
  --
  alter table cr_hist_user_map  
      add constraint sys_c0014617 foreign key (user_id) references fn_user(user_id);
  --
  -- name: sys_c0014618; type: fk constraint
  --
  alter table fn_menu_favorites
  add constraint sys_c0014618 foreign key (user_id) references fn_user(user_id);  
      
  --
  -- name: sys_c0014619; type: fk constraint
  --
  alter table fn_menu_favorites
  add constraint sys_c0014619 foreign key (menu_id) references fn_menu_functional(menu_id);
  
  commit;
  