--- /dev/null
+-- ---------------------------------------------------------------------------------------------------------------
+-- 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,
+ 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;
--- /dev/null
+-- --------------------------------------------------------------------------------------------
+-- This is the common default data for 3.2.0 Version of Portal database called portal
+
+USE portal;
+
+set foreign_key_checks=1;
+
+
+INSERT INTO fn_display_text (language_id,text_id,text_label) VALUES (1,2,'Home');
+INSERT INTO fn_display_text (language_id,text_id,text_label) VALUES (1,3,'Application Catalog');
+INSERT INTO fn_display_text (language_id,text_id,text_label) VALUES (1,4,'Widget Catalog');
+INSERT INTO fn_display_text (language_id,text_id,text_label) VALUES (1,5,'Admins');
+INSERT INTO fn_display_text (language_id,text_id,text_label) VALUES (1,6,'Roles');
+INSERT INTO fn_display_text (language_id,text_id,text_label) VALUES (1,7,'Users');
+INSERT INTO fn_display_text (language_id,text_id,text_label) VALUES (1,8,'Portal Admins');
+INSERT INTO fn_display_text (language_id,text_id,text_label) VALUES (1,9,'Application Onboarding');
+INSERT INTO fn_display_text (language_id,text_id,text_label) VALUES (1,10,'Widget Onboarding');
+INSERT INTO fn_display_text (language_id,text_id,text_label) VALUES (1,11,'Edit Functional Menu');
+INSERT INTO fn_display_text (language_id,text_id,text_label) VALUES (1,12,'User Notifications');
+INSERT INTO fn_display_text (language_id,text_id,text_label) VALUES (1,13,'Microservice Onboarding');
+INSERT INTO fn_display_text (language_id,text_id,text_label) VALUES (1,15,'App Account Management');
+INSERT INTO fn_display_text (language_id,text_id,text_label) VALUES (2,2,'主页');
+INSERT INTO fn_display_text (language_id,text_id,text_label) VALUES (2,3,'应用目录');
+INSERT INTO fn_display_text (language_id,text_id,text_label) VALUES (2,4,'部件目录');
+INSERT INTO fn_display_text (language_id,text_id,text_label) VALUES (2,5,'管理员');
+INSERT INTO fn_display_text (language_id,text_id,text_label) VALUES (2,6,'角色');
+INSERT INTO fn_display_text (language_id,text_id,text_label) VALUES (2,7,'用户');
+INSERT INTO fn_display_text (language_id,text_id,text_label) VALUES (2,8,'门户管理员');
+INSERT INTO fn_display_text (language_id,text_id,text_label) VALUES (2,9,'应用管理');
+INSERT INTO fn_display_text (language_id,text_id,text_label) VALUES (2,10,'部件管理');
+INSERT INTO fn_display_text (language_id,text_id,text_label) VALUES (2,11,'编辑功能菜单');
+INSERT INTO fn_display_text (language_id,text_id,text_label) VALUES (2,12,'用户通知');
+INSERT INTO fn_display_text (language_id,text_id,text_label) VALUES (2,13,'微服务管理');
+INSERT INTO fn_display_text (language_id,text_id,text_label) VALUES (2,15,'应用账户管理');
+
+INSERT INTO fn_language (language_name,language_alias) VALUES ('English','EN');
+INSERT INTO fn_language (language_name,language_alias) VALUES ('简体中文','CN');
+
+-- FN_FUNCTION
+Insert into fn_function (FUNCTION_CD,FUNCTION_NAME) values ('menu_process','Process List');
+Insert into fn_function (FUNCTION_CD,FUNCTION_NAME) values ('menu_job','Job Menu');
+Insert into fn_function (FUNCTION_CD,FUNCTION_NAME) values ('menu_job_create','Job Create');
+Insert into fn_function (FUNCTION_CD,FUNCTION_NAME) values ('menu_job_designer','Process in Designer view');
+Insert into fn_function (FUNCTION_CD,FUNCTION_NAME) values ('menu_task','Task Menu');
+Insert into fn_function (FUNCTION_CD,FUNCTION_NAME) values ('menu_task_search','Task Search');
+Insert into fn_function (FUNCTION_CD,FUNCTION_NAME) values ('menu_map','Map Menu');
+Insert into fn_function (FUNCTION_CD,FUNCTION_NAME) values ('menu_sample','Sample Pages Menu');
+Insert into fn_function (FUNCTION_CD,FUNCTION_NAME) values ('login','Login');
+Insert into fn_function (FUNCTION_CD,FUNCTION_NAME) values ('menu_home','Home Menu');
+Insert into fn_function (FUNCTION_CD,FUNCTION_NAME) values ('menu_customer','Customer Menu');
+Insert into fn_function (FUNCTION_CD,FUNCTION_NAME) values ('menu_reports','Reports Menu');
+Insert into fn_function (FUNCTION_CD,FUNCTION_NAME) values ('menu_profile','Profile Menu');
+Insert into fn_function (FUNCTION_CD,FUNCTION_NAME) values ('menu_admin','Admin Menu');
+Insert into fn_function (FUNCTION_CD,FUNCTION_NAME) values ('menu_feedback','Feedback Menu');
+Insert into fn_function (FUNCTION_CD,FUNCTION_NAME) values ('menu_help','Help Menu');
+Insert into fn_function (FUNCTION_CD,FUNCTION_NAME) values ('menu_logout','Logout Menu');
+Insert into fn_function (FUNCTION_CD,FUNCTION_NAME) values ('menu_notes','Notes Menu');
+Insert into fn_function (FUNCTION_CD,FUNCTION_NAME) values ('menu_ajax','Ajax Menu');
+Insert into fn_function (FUNCTION_CD,FUNCTION_NAME) values ('menu_customer_create','Customer Create');
+Insert into fn_function (FUNCTION_CD,FUNCTION_NAME) values ('menu_profile_create','Profile Create');
+Insert into fn_function (FUNCTION_CD,FUNCTION_NAME) values ('menu_profile_import','Profile Import');
+Insert into fn_function (FUNCTION_CD,FUNCTION_NAME) values ('menu_tab','Sample Tab Menu');
+Insert into fn_function (FUNCTION_CD,FUNCTION_NAME) values ('view_reports','View Raptor reports');
+
+-- new 1702
+Insert into fn_function (function_cd,function_name) values ('edit_notification','User Notification');
+Insert INTO fn_function (function_cd,function_name) values ('getAdminNotifications', 'Admin Notifications');
+Insert INTO fn_function (function_cd,function_name) values ('saveNotification', 'publish notifications');
+-- end new 1702
+
+-- new 1707
+INSERT INTO fn_function (function_cd, function_name) VALUES ('menu_web_analytics', 'Web Analytics');
+-- end new 1707
+
+-- FN_LU_ACTIVITY
+Insert into fn_lu_activity (ACTIVITY_CD,ACTIVITY) values ('add_role','add_role');
+Insert into fn_lu_activity (ACTIVITY_CD,ACTIVITY) values ('remove_role','remove_role');
+Insert into fn_lu_activity (ACTIVITY_CD,ACTIVITY) values ('add_user_role','add_user_role');
+Insert into fn_lu_activity (ACTIVITY_CD,ACTIVITY) values ('remove_user_role','remove_user_role');
+Insert into fn_lu_activity (ACTIVITY_CD,ACTIVITY) values ('add_role_function','add_role_function');
+Insert into fn_lu_activity (ACTIVITY_CD,ACTIVITY) values ('remove_role_function','remove_role_function');
+Insert into fn_lu_activity (ACTIVITY_CD,ACTIVITY) values ('add_child_role','add_child_role');
+Insert into fn_lu_activity (ACTIVITY_CD,ACTIVITY) values ('remove_child_role','remove_child_role');
+Insert into fn_lu_activity (ACTIVITY_CD,ACTIVITY) values ('mobile_login','Mobile Login');
+Insert into fn_lu_activity (ACTIVITY_CD,ACTIVITY) values ('mobile_logout','Mobile Logout');
+Insert into fn_lu_activity (ACTIVITY_CD,ACTIVITY) values ('login','Login');
+Insert into fn_lu_activity (ACTIVITY_CD,ACTIVITY) values ('logout','Logout');
+
+-- new 1610.2
+insert into fn_lu_activity (ACTIVITY_CD,ACTIVITY) values('guest_login','Guest Login');
+-- end new 1610.2
+
+-- new 1702
+Insert into fn_lu_activity (ACTIVITY_CD,ACTIVITY) values ('tab_access','Tab Access');
+Insert into fn_lu_activity (ACTIVITY_CD,ACTIVITY) values ('app_access','App Access');
+Insert into fn_lu_activity (ACTIVITY_CD,ACTIVITY) values('functional_access','Functional Access');
+Insert into fn_lu_activity (ACTIVITY_CD,ACTIVITY) values ('left_menu_access','Left Menu Access');
+Insert into fn_lu_activity (ACTIVITY_CD,ACTIVITY) values ('search','Search');
+Insert into fn_lu_activity (ACTIVITY_CD,ACTIVITY) values ('apa','Add Portal Admin');
+Insert into fn_lu_activity (ACTIVITY_CD,ACTIVITY) values ('dpa','Delete Portal Admin');
+Insert into fn_lu_activity (ACTIVITY_CD,ACTIVITY) values ('uaa','Update Account Admin');
+Insert into fn_lu_activity (ACTIVITY_CD,ACTIVITY) values ('uu','Update User');
+
+-- new El Alto
+Insert into fn_lu_activity (ACTIVITY_CD,ACTIVITY) values ('eaaf','External auth add function');
+Insert into fn_lu_activity (ACTIVITY_CD,ACTIVITY) values ('eaar','External auth add role');
+Insert into fn_lu_activity (ACTIVITY_CD,ACTIVITY) values ('eadf','External auth delete function');
+Insert into fn_lu_activity (ACTIVITY_CD,ACTIVITY) values ('eadr','External auth delete role');
+Insert into fn_lu_activity (ACTIVITY_CD,ACTIVITY) values ('eauf','External auth update function');
+Insert into fn_lu_activity (ACTIVITY_CD,ACTIVITY) values ('eaurf','External auth update role and function');
+
+-- FN_LU_MENU_SET
+Insert into fn_lu_menu_set (MENU_SET_CD,MENU_SET_NAME) values ('APP','Application Menu');
+
+-- FN_MENU Ecomp Portal now uses the left menu entries from fn_menu
+Insert into fn_menu(MENU_ID,LABEL,PARENT_ID,SORT_ORDER,ACTION,FUNCTION_CD,ACTIVE_YN,MENU_SET_CD,SEPARATOR_YN,IMAGE_SRC)
+ values(1,'root',NULL,10,NULL,'menu_home','N','APP','N',NULL);
+
+Insert into fn_menu(MENU_ID,LABEL,PARENT_ID,SORT_ORDER,ACTION,FUNCTION_CD,ACTIVE_YN,MENU_SET_CD,SEPARATOR_YN,IMAGE_SRC)
+ values(2,'Home',1,10,'applicationsHome','menu_home','Y','APP','N','home');
+
+Insert into fn_menu(MENU_ID,LABEL,PARENT_ID,SORT_ORDER,ACTION,FUNCTION_CD,ACTIVE_YN,MENU_SET_CD,SEPARATOR_YN,IMAGE_SRC)
+ values(3,'Application Catalog',1,15,'appCatalog','menu_home','Y','APP','N','apps');
+
+Insert into fn_menu(MENU_ID,LABEL,PARENT_ID,SORT_ORDER,ACTION,FUNCTION_CD,ACTIVE_YN,MENU_SET_CD,SEPARATOR_YN,IMAGE_SRC)
+ values(4,'Widget Catalog',1,20,'widgetCatalog','menu_home','Y','APP','N','apps');
+
+Insert into fn_menu(MENU_ID,LABEL,PARENT_ID,SORT_ORDER,ACTION,FUNCTION_CD,ACTIVE_YN,MENU_SET_CD,SEPARATOR_YN,IMAGE_SRC)
+ values(5,'Admins',1,40,'admins','menu_admin','Y','APP','N','star');
+
+Insert into fn_menu(MENU_ID,LABEL,PARENT_ID,SORT_ORDER,ACTION,FUNCTION_CD,ACTIVE_YN,MENU_SET_CD,SEPARATOR_YN,IMAGE_SRC)
+ values(6,'Roles',1,45,'roles','menu_acc_admin','Y','APP','N','person');
+
+Insert into fn_menu(MENU_ID,LABEL,PARENT_ID,SORT_ORDER,ACTION,FUNCTION_CD,ACTIVE_YN,MENU_SET_CD,SEPARATOR_YN,IMAGE_SRC)
+ values(7,'Users',1,50,'users','menu_acc_admin','Y','APP','N','person');
+
+Insert into fn_menu(MENU_ID,LABEL,PARENT_ID,SORT_ORDER,ACTION,FUNCTION_CD,ACTIVE_YN,MENU_SET_CD,SEPARATOR_YN,IMAGE_SRC)
+ values(8,'Portal Admins',1,60,'portalAdmins','menu_admin','Y','APP','N','settings');
+
+Insert into fn_menu(MENU_ID,LABEL,PARENT_ID,SORT_ORDER,ACTION,FUNCTION_CD,ACTIVE_YN,MENU_SET_CD,SEPARATOR_YN,IMAGE_SRC)
+ values(9,'Application Onboarding',1,70,'applications','menu_app_onboarding','Y','APP','N','filing');
+
+Insert into fn_menu(MENU_ID,LABEL,PARENT_ID,SORT_ORDER,ACTION,FUNCTION_CD,ACTIVE_YN,MENU_SET_CD,SEPARATOR_YN,IMAGE_SRC)
+ values(10,'Widget Onboarding',1,80,'widgetOnboarding','menu_admin','Y','APP','N','filing');
+
+Insert into fn_menu(MENU_ID,LABEL,PARENT_ID,SORT_ORDER,ACTION,FUNCTION_CD,ACTIVE_YN,MENU_SET_CD,SEPARATOR_YN,IMAGE_SRC)
+ values(11,'Edit Functional Menu',1,90,'functionalMenu','menu_admin','Y','APP','N','menu');
+
+Insert into fn_menu(MENU_ID,LABEL,PARENT_ID,SORT_ORDER,ACTION,FUNCTION_CD,ACTIVE_YN,MENU_SET_CD,SEPARATOR_YN,IMAGE_SRC)
+ values(12,'User Notifications',1,100,'userNotifications','edit_notification','Y','APP','N','settings');
+
+-- end new 1702
+
+-- new 1707
+Insert into fn_menu(MENU_ID,LABEL,PARENT_ID,SORT_ORDER,ACTION,FUNCTION_CD,ACTIVE_YN,MENU_SET_CD,SEPARATOR_YN,IMAGE_SRC)
+ values (13,'Microservice Onboarding', 1, 110, 'microserviceOnboarding', 'menu_admin', 'Y', 'APP', 'N', 'filing');
+
+Insert into fn_menu (menu_id, label, parent_id, sort_order, action, function_cd, active_yn, menu_set_cd, separator_yn, image_src)
+ values(15,'App Account Management', 1, 130, 'accountOnboarding', 'menu_admin', 'Y', 'App', 'N', 'filing');
+
+-- end new 1707
+
+-- FN_LU_ALERT_METHOD
+Insert into fn_lu_alert_method (ALERT_METHOD_CD,ALERT_METHOD) values ('PHONE','Phone');
+Insert into fn_lu_alert_method (ALERT_METHOD_CD,ALERT_METHOD) values ('FAX','Fax');
+Insert into fn_lu_alert_method (ALERT_METHOD_CD,ALERT_METHOD) values ('PAGER','Pager');
+Insert into fn_lu_alert_method (ALERT_METHOD_CD,ALERT_METHOD) values ('EMAIL','Email');
+Insert into fn_lu_alert_method (ALERT_METHOD_CD,ALERT_METHOD) values ('SMS','SMS');
+
+-- FN_LU_PRIORITY
+Insert into fn_lu_priority (PRIORITY_ID,PRIORITY,ACTIVE_YN,SORT_ORDER) values (10,'Low','Y',10);
+Insert into fn_lu_priority (PRIORITY_ID,PRIORITY,ACTIVE_YN,SORT_ORDER) values (20,'Normal','Y',20);
+Insert into fn_lu_priority (PRIORITY_ID,PRIORITY,ACTIVE_YN,SORT_ORDER) values (30,'High','Y',30);
+Insert into fn_lu_priority (PRIORITY_ID,PRIORITY,ACTIVE_YN,SORT_ORDER) values (40,'Urgent','Y',40);
+Insert into fn_lu_priority (PRIORITY_ID,PRIORITY,ACTIVE_YN,SORT_ORDER) values (50,'Fatal','Y',50);
+
+-- FN_LU_TAB_SET
+Insert into fn_lu_tab_set (TAB_SET_CD,TAB_SET_NAME) values ('APP','Application Tabs');
+
+-- FN_LU_TIMEZONE
+Insert into fn_lu_timezone (TIMEZONE_ID,TIMEZONE_NAME,TIMEZONE_VALUE) values (10,'US/Eastern','US/Eastern');
+Insert into fn_lu_timezone (TIMEZONE_ID,TIMEZONE_NAME,TIMEZONE_VALUE) values (20,'US/Central','US/Central');
+Insert into fn_lu_timezone (TIMEZONE_ID,TIMEZONE_NAME,TIMEZONE_VALUE) values (30,'US/Mountain','US/Mountain');
+Insert into fn_lu_timezone (TIMEZONE_ID,TIMEZONE_NAME,TIMEZONE_VALUE) values (40,'US/Arizona','America/Phoenix');
+Insert into fn_lu_timezone (TIMEZONE_ID,TIMEZONE_NAME,TIMEZONE_VALUE) values (50,'US/Pacific','US/Pacific');
+Insert into fn_lu_timezone (TIMEZONE_ID,TIMEZONE_NAME,TIMEZONE_VALUE) values (60,'US/Alaska','US/Alaska');
+Insert into fn_lu_timezone (TIMEZONE_ID,TIMEZONE_NAME,TIMEZONE_VALUE) values (70,'US/Hawaii','US/Hawaii');
+
+-- FN_RESTRICTED_URL
+Insert into fn_restricted_url (restricted_url, function_cd) values ('attachment.htm','menu_admin');
+Insert into fn_restricted_url (restricted_url, function_cd) values ('broadcast.htm','menu_admin');
+Insert into fn_restricted_url (restricted_url, function_cd) values ('file_upload.htm','menu_admin');
+Insert into fn_restricted_url (restricted_url, function_cd) values ('job.htm','menu_admin');
+Insert into fn_restricted_url (restricted_url, function_cd) values ('role.htm','menu_admin');
+Insert into fn_restricted_url (restricted_url, function_cd) values ('role_function.htm','menu_admin');
+Insert into fn_restricted_url (restricted_url, function_cd) values ('test.htm','menu_admin');
+Insert into fn_restricted_url (restricted_url, function_cd) values ('async_test.htm','menu_home');
+Insert into fn_restricted_url (restricted_url, function_cd) values ('chatWindow.htm','menu_home');
+Insert into fn_restricted_url (restricted_url, function_cd) values ('contact_list.htm','menu_home');
+Insert into fn_restricted_url (restricted_url, function_cd) values ('customer_dynamic_list.htm','menu_home');
+Insert into fn_restricted_url (restricted_url, function_cd) values ('event.htm','menu_home');
+Insert into fn_restricted_url (restricted_url, function_cd) values ('event_list.htm','menu_home');
+Insert into fn_restricted_url (restricted_url, function_cd) values ('mobile_welcome.htm','menu_home');
+Insert into fn_restricted_url (restricted_url, function_cd) values ('sample_map.htm','menu_home');
+Insert into fn_restricted_url (restricted_url, function_cd) values ('template.jsp','menu_home');
+Insert into fn_restricted_url (restricted_url, function_cd) values ('jbpm_designer.htm','menu_job_create');
+Insert into fn_restricted_url (restricted_url, function_cd) values ('jbpm_drools.htm','menu_job_create');
+Insert into fn_restricted_url (restricted_url, function_cd) values ('process_job.htm','menu_job_create');
+Insert into fn_restricted_url (restricted_url, function_cd) values ('profile.htm','menu_profile_create');
+Insert into fn_restricted_url (restricted_url, function_cd) values ('raptor.htm','menu_reports');
+Insert into fn_restricted_url (restricted_url, function_cd) values ('raptor2.htm','menu_reports');
+Insert into fn_restricted_url (restricted_url, function_cd) values ('raptor_blob_extract.htm','menu_reports');
+Insert into fn_restricted_url (restricted_url, function_cd) values ('raptor_email_attachment.htm','menu_reports');
+Insert into fn_restricted_url (restricted_url, function_cd) values ('raptor_search.htm','menu_reports');
+Insert into fn_restricted_url (restricted_url, function_cd) values ('report_list.htm','menu_reports');
+Insert into fn_restricted_url (restricted_url, function_cd) values ('gauge.htm','menu_tab');
+Insert into fn_restricted_url (restricted_url, function_cd) values ('gmap_controller.htm','menu_tab');
+Insert into fn_restricted_url (restricted_url, function_cd) values ('gmap_frame.htm','menu_tab');
+Insert into fn_restricted_url (restricted_url, function_cd) values ('map.htm','menu_tab');
+Insert into fn_restricted_url (restricted_url, function_cd) values ('map_download.htm','menu_tab');
+Insert into fn_restricted_url (restricted_url, function_cd) values ('map_grid_search.htm','menu_tab');
+Insert into fn_restricted_url (restricted_url, function_cd) values ('sample_animated_map.htm','menu_tab');
+Insert into fn_restricted_url (restricted_url, function_cd) values ('sample_map_2.htm','menu_tab');
+Insert into fn_restricted_url (restricted_url, function_cd) values ('sample_map_3.htm','menu_tab');
+Insert into fn_restricted_url (restricted_url, function_cd) values ('tab2_sub1.htm','menu_tab');
+Insert into fn_restricted_url (restricted_url, function_cd) values ('tab2_sub2_link1.htm','menu_tab');
+Insert into fn_restricted_url (restricted_url, function_cd) values ('tab2_sub2_link2.htm','menu_tab');
+Insert into fn_restricted_url (restricted_url, function_cd) values ('tab2_sub3.htm','menu_tab');
+Insert into fn_restricted_url (restricted_url, function_cd) values ('tab3.htm','menu_tab');
+Insert into fn_restricted_url (restricted_url, function_cd) values ('tab4.htm','menu_tab');
+Insert into fn_restricted_url (restricted_url, function_cd) values ('raptor.htm','view_reports');
+Insert into fn_restricted_url (restricted_url, function_cd) values ('raptor_blob_extract.htm','view_reports');
+
+-- FN_ROLE
+Insert into fn_role (ROLE_ID, ROLE_NAME, ACTIVE_YN, PRIORITY, APP_ID, APP_ROLE_ID) values (1,'System Administrator','Y',1,NULL,NULL);
+Insert into fn_role (ROLE_ID, ROLE_NAME, ACTIVE_YN, PRIORITY, APP_ID, APP_ROLE_ID) values (16,'Standard User','Y',5,NULL,NULL);
+Insert into fn_role (ROLE_ID, ROLE_NAME, ACTIVE_YN, PRIORITY, APP_ID, APP_ROLE_ID) values (999,'Account Administrator','Y',1,NULL,NULL);
+Insert into fn_role (ROLE_ID, ROLE_NAME, ACTIVE_YN, PRIORITY, APP_ID, APP_ROLE_ID) values (900,'Restricted App Role','Y','1',NULL,NULL);
+
+-- new 1702
+Insert into fn_role (ROLE_ID, ROLE_NAME, ACTIVE_YN, PRIORITY, APP_ID, APP_ROLE_ID) values (950,'Portal Notification Admin','Y','1',NULL,NULL);
+-- end new 1702
+
+-- new 1707
+INSERT INTO fn_role (role_id, role_name, active_yn, priority) VALUES ('1010', 'Usage Analyst', 'Y', '1');
+INSERT INTO fn_role (role_id, role_name, active_yn, priority) VALUES ('2115', 'Portal Usage Analyst', 'Y', '6');
+-- end new 1707
+
+-- FN_ROLE_Composite
+Insert into fn_role_composite (PARENT_ROLE_ID,CHILD_ROLE_ID) values (1,16);
+
+-- FN_ROLE_FUNCTION
+Insert into fn_role_function (ROLE_ID,FUNCTION_CD) values (1,'login');
+Insert into fn_role_function (ROLE_ID,FUNCTION_CD) values (1,'menu_admin');
+Insert into fn_role_function (ROLE_ID,FUNCTION_CD) values (1,'menu_ajax');
+Insert into fn_role_function (ROLE_ID,FUNCTION_CD) values (1,'menu_customer');
+Insert into fn_role_function (ROLE_ID,FUNCTION_CD) values (1,'menu_customer_create');
+Insert into fn_role_function (ROLE_ID,FUNCTION_CD) values (1,'menu_feedback');
+Insert into fn_role_function (ROLE_ID,FUNCTION_CD) values (1,'menu_help');
+Insert into fn_role_function (ROLE_ID,FUNCTION_CD) values (1,'menu_home');
+Insert into fn_role_function (ROLE_ID,FUNCTION_CD) values (1,'menu_job');
+Insert into fn_role_function (ROLE_ID,FUNCTION_CD) values (1,'menu_job_create');
+Insert into fn_role_function (ROLE_ID,FUNCTION_CD) values (1,'menu_logout');
+Insert into fn_role_function (ROLE_ID,FUNCTION_CD) values (1,'menu_notes');
+Insert into fn_role_function (ROLE_ID,FUNCTION_CD) values (1,'menu_process');
+Insert into fn_role_function (ROLE_ID,FUNCTION_CD) values (1,'menu_profile');
+Insert into fn_role_function (ROLE_ID,FUNCTION_CD) values (1,'menu_profile_create');
+Insert into fn_role_function (ROLE_ID,FUNCTION_CD) values (1,'menu_profile_import');
+Insert into fn_role_function (ROLE_ID,FUNCTION_CD) values (1,'menu_reports');
+Insert into fn_role_function (ROLE_ID,FUNCTION_CD) values (1,'menu_sample');
+Insert into fn_role_function (ROLE_ID,FUNCTION_CD) values (1,'menu_tab');
+Insert into fn_role_function (ROLE_ID,FUNCTION_CD) values (16,'login');
+Insert into fn_role_function (ROLE_ID,FUNCTION_CD) values (16,'menu_ajax');
+Insert into fn_role_function (ROLE_ID,FUNCTION_CD) values (16,'menu_customer');
+Insert into fn_role_function (ROLE_ID,FUNCTION_CD) values (16,'menu_customer_create');
+Insert into fn_role_function (ROLE_ID,FUNCTION_CD) values (16,'menu_home');
+Insert into fn_role_function (ROLE_ID,FUNCTION_CD) values (16,'menu_logout');
+Insert into fn_role_function (ROLE_ID,FUNCTION_CD) values (16,'menu_map');
+Insert into fn_role_function (ROLE_ID,FUNCTION_CD) values (16,'menu_profile');
+Insert into fn_role_function (ROLE_ID,FUNCTION_CD) values (16,'menu_reports');
+Insert into fn_role_function (ROLE_ID,FUNCTION_CD) values (16,'menu_tab');
+
+-- new 1702
+Insert into fn_role_function (ROLE_ID,FUNCTION_CD) values (950,'edit_notification');
+Insert INTO fn_role_function (ROLE_ID,FUNCTION_CD) values (950, 'getAdminNotifications');
+Insert INTO fn_role_function (ROLE_ID,FUNCTION_CD) values (950, 'saveNotification');
+-- end new 1702
+
+-- new 1707
+INSERT INTO fn_role_function (role_id, function_cd) VALUES ('1010', 'menu_web_analytics');
+INSERT INTO fn_role_function (role_id, function_cd) VALUES ('2115', 'menu_web_analytics');
+-- end new 1707
+
+-- FN_TAB
+Insert into fn_tab (TAB_CD,TAB_NAME,TAB_DESCR,ACTION,FUNCTION_CD,ACTIVE_YN,SORT_ORDER,PARENT_TAB_CD,TAB_SET_CD) values ('TAB2_SUB1_S1','Left Tab 1','Sub - Sub Tab 1 Information','tab2_sub1.htm','menu_tab','Y',10,'TAB2_SUB1','APP');
+Insert into fn_tab (TAB_CD,TAB_NAME,TAB_DESCR,ACTION,FUNCTION_CD,ACTIVE_YN,SORT_ORDER,PARENT_TAB_CD,TAB_SET_CD) values ('TAB1','Tab 1','Tab 1 Information','tab1.htm','menu_tab','Y',10,null,'APP');
+Insert into fn_tab (TAB_CD,TAB_NAME,TAB_DESCR,ACTION,FUNCTION_CD,ACTIVE_YN,SORT_ORDER,PARENT_TAB_CD,TAB_SET_CD) values ('TAB2','Tab 2','Tab 2 Information','tab2_sub1.htm','menu_tab','Y',20,null,'APP');
+Insert into fn_tab (TAB_CD,TAB_NAME,TAB_DESCR,ACTION,FUNCTION_CD,ACTIVE_YN,SORT_ORDER,PARENT_TAB_CD,TAB_SET_CD) values ('TAB3','Tab 3','Tab 3 Information','tab3.htm','menu_tab','Y',30,null,'APP');
+Insert into fn_tab (TAB_CD,TAB_NAME,TAB_DESCR,ACTION,FUNCTION_CD,ACTIVE_YN,SORT_ORDER,PARENT_TAB_CD,TAB_SET_CD) values ('TAB4','Tab 4','Tab 4 Information','tab4.htm','menu_tab','Y',40,null,'APP');
+Insert into fn_tab (TAB_CD,TAB_NAME,TAB_DESCR,ACTION,FUNCTION_CD,ACTIVE_YN,SORT_ORDER,PARENT_TAB_CD,TAB_SET_CD) values ('TAB2_SUB1','Sub Tab 1','Sub Tab 1 Information','tab2_sub1.htm','menu_tab','Y',10,'TAB2','APP');
+Insert into fn_tab (TAB_CD,TAB_NAME,TAB_DESCR,ACTION,FUNCTION_CD,ACTIVE_YN,SORT_ORDER,PARENT_TAB_CD,TAB_SET_CD) values ('TAB2_SUB2','Sub Tab 2','Sub Tab 2 Information','tab2_sub2.htm','menu_tab','Y',20,'TAB2','APP');
+Insert into fn_tab (TAB_CD,TAB_NAME,TAB_DESCR,ACTION,FUNCTION_CD,ACTIVE_YN,SORT_ORDER,PARENT_TAB_CD,TAB_SET_CD) values ('TAB2_SUB3','Sub Tab 3','Sub Tab 3 Information','tab2_sub3.htm','menu_tab','Y',30,'TAB2','APP');
+
+-- FN_TAB_SELECTED
+Insert into fn_tab_selected (SELECTED_TAB_CD,TAB_URI) values ('TAB1','tab1');
+Insert into fn_tab_selected (SELECTED_TAB_CD,TAB_URI) values ('TAB2','tab2_sub1');
+Insert into fn_tab_selected (SELECTED_TAB_CD,TAB_URI) values ('TAB2','tab2_sub2');
+Insert into fn_tab_selected (SELECTED_TAB_CD,TAB_URI) values ('TAB2','tab2_sub3');
+Insert into fn_tab_selected (SELECTED_TAB_CD,TAB_URI) values ('TAB2_SUB1','tab2_sub1');
+Insert into fn_tab_selected (SELECTED_TAB_CD,TAB_URI) values ('TAB2_SUB1_S1','tab2_sub1');
+Insert into fn_tab_selected (SELECTED_TAB_CD,TAB_URI) values ('TAB2_SUB2','tab2_sub2');
+Insert into fn_tab_selected (SELECTED_TAB_CD,TAB_URI) values ('TAB2_SUB3','tab2_sub3');
+Insert into fn_tab_selected (SELECTED_TAB_CD,TAB_URI) values ('TAB3','tab3');
+Insert into fn_tab_selected (SELECTED_TAB_CD,TAB_URI) values ('TAB4','tab4');
+
+ UPDATE
+ fn_menu
+SET
+ ACTION = 'webAnalytics',
+ image_src= 'pie'
+WHERE
+ active_yn = 'Y' AND label = 'Web Analytics';
+
+
+ UPDATE
+ fn_menu
+SET
+ ACTION = 'webAnlayticsSource',
+ image_src= 'pie'
+WHERE
+ active_yn = 'Y' AND label = 'Web Analytics Onboarding';
+
+
+commit;
--- /dev/null
+-- ---------------------------------------------------------------------------------------------------------------
+-- This is the default data for the 3.2.0 Version of Portal database called portal - the Opensource project
+-- First run the common Opensource DML; then run this file to add The Opensource only data
+USE portal;
+
+set foreign_key_checks=1;
+
+--- update fn_menu for roles
+UPDATE fn_menu
+SET function_cd = 'menu_acc_admin'
+WHERE label = 'Roles';
+
+--- update fn_menu for users
+UPDATE fn_menu
+SET function_cd = 'menu_acc_admin'
+WHERE label = 'Users';
+
+
+-- fn_user
+Insert into fn_user (USER_ID, ORG_ID, MANAGER_ID,FIRST_NAME,MIDDLE_NAME,LAST_NAME,PHONE,FAX,CELLULAR,EMAIL,ADDRESS_ID,ALERT_METHOD_CD,HRID,ORG_USER_ID,ORG_CODE,LOGIN_ID,LOGIN_PWD,LAST_LOGIN_DATE,ACTIVE_YN,CREATED_ID,CREATED_DATE,MODIFIED_ID,MODIFIED_DATE,IS_INTERNAL_YN,ADDRESS_LINE_1,ADDRESS_LINE_2,CITY,STATE_CD,ZIP_CODE,COUNTRY_CD,LOCATION_CLLI,ORG_MANAGER_USERID,COMPANY,DEPARTMENT_NAME,JOB_TITLE,TIMEZONE,DEPARTMENT,BUSINESS_UNIT,BUSINESS_UNIT_NAME,COST_CENTER,FIN_LOC_CODE,SILO_STATUS,is_system_user,language_id) values (1,NULL,NULL,'Demo',NULL,'User',NULL,NULL,NULL,'demo@openecomp.org',NULL,NULL,NULL,'demo',NULL,'demo','4Gl6WL1bmwviYm+XZa6pS1vC0qKXWtn9wcZWdLx61L0=','2016-10-20 15:11:16','Y',NULL,'2016-10-14 21:00:00',1,'2016-10-20 15:11:16','N',NULL,NULL,NULL,'NJ',NULL,'US',NULL,NULL,NULL,NULL,NULL,10,NULL,NULL,NULL,NULL,NULL,NULL,'N',1);
+
+-- fn_appokYTaDrhzibcbGVq5mjkVQ==
+Insert INTO fn_app (APP_ID, APP_NAME, APP_IMAGE_URL, APP_DESCRIPTION, APP_NOTES, APP_URL, APP_ALTERNATE_URL, APP_REST_ENDPOINT, ML_APP_NAME, ML_APP_ADMIN_ID, MOTS_ID, APP_PASSWORD, OPEN, ENABLED, THUMBNAIL, APP_USERNAME, UEB_KEY, UEB_SECRET, UEB_TOPIC_NAME, APP_TYPE, AUTH_CENTRAL, AUTH_NAMESPACE) values (1,'Default','assets/images/tmp/portal1.png','Some Default Description','Some Default Note','http://localhost','http://localhost','http://localhost:8080/ecompportal','EcompPortal','',NULL,'dR2NABMkxPaFbIbym87ZwQ==','N','N',NULL,'m00468@portal.onap.org','EkrqsjQqZt4ZrPh6',NULL,NULL,1,'Y',NULL);
+
+-- fn_user_role
+Insert into fn_user_role (USER_ID,ROLE_ID,PRIORITY,APP_ID) values (1,1,NULL,1);
+Insert into fn_user_role (USER_ID,ROLE_ID,PRIORITY,APP_ID) values (1,950,NULL,1);
+Insert into fn_user_role (USER_ID,ROLE_ID,PRIORITY,APP_ID) values (1,999,NULL,1);
+
+INSERT INTO cr_report
+ (rep_id, title, descr, public_yn, report_xml, create_id, create_date, maint_id, maint_date, menu_id, menu_approved_yn, owner_id, folder_id, dashboard_type_yn, dashboard_yn)
+ VALUES (
+ 15,
+ 'Application Usage Report Wid',
+ '',
+ 'Y',
+ '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>\n<customReport pageSize="200" reportType="Linear">\n <reportName>Application Usage Report Wid</reportName>\n <reportDescr></reportDescr>\n <dbInfo>local</dbInfo>\n <dbType>mysql</dbType>\n <chartType>BarChart3D</chartType>\n <chartWidth>700</chartWidth>\n <chartHeight>500</chartHeight>\n <showChartTitle>false</showChartTitle>\n <public>false</public>\n <hideFormFieldAfterRun>false</hideFormFieldAfterRun>\n <createId>27</createId>\n <createDate>2017-01-28-05:00</createDate>\n <reportSQL>SELECT \n l.date audit_date, \n app_id app_id, \n IF(CHAR_LENGTH(l.app_name) >14, CONCAT(CONCAT(SUBSTR(l.app_name,1,7),\'...\'), SUBSTR(l.app_name, CHAR_LENGTH(l.app_name)-3,CHAR_LENGTH(l.app_name))) , l.app_name) app_name, \n IFNULL(r.ct,0) ct \nfrom\n(\n select a.Date, app_id, app_name\n from (\n select curdate() - INTERVAL (a.a + (10 * b.a) + (100 * c.a)) DAY as Date\n from (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as a\n cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as b\n cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as c\n ) a, \n (\n SELECT \n app_id, app_name\n from\n (\n select @rn := @rn+1 AS rowId, app_id, app_name from \n (\n select app_id, app_name, ct from \n (\n select affected_record_id, count(*) ct\n from fn_audit_log l\n where audit_date > date_add( curdate(), interval -6 day)\n and affected_record_id not in ( 1, -1)\n and activity_cd in (\'tab_access\', \'app_access\')\n and user_id = [USER_ID]\n group by affected_record_id\n ) a, fn_app f\n where a.affected_record_id = f.app_id\n order by ct desc \n ) b,\n (SELECT @rn := 0) t2\n ) mm where rowId <= 4\n )b\n where a.Date between date_add( curdate(), interval -6 day) and curdate()\n) l left outer join\n(\n select app_name, DATE(audit_date) audit_date_1 ,count(*) ct from fn_audit_log a, fn_app b\n where user_id = [USER_ID]\n and audit_date > date_add( curdate(), interval -6 day)\n and activity_cd in (\'tab_access\', \'app_access\')\n and a.affected_record_id = b.app_id\n and b.app_id <> 1\n and b.app_id in \n (\n SELECT \n app_id\n from\n (\n select @rn := @rn+1 AS rowId, app_id from \n (\n select app_id, ct from \n (\n select affected_record_id app_id, count(*) ct\n from fn_audit_log \n where audit_date > date_add( curdate(), interval -6 day)\n and affected_record_id not in ( 1, -1)\n and activity_cd in (\'tab_access\', \'app_access\')\n and user_id = [USER_ID]\n group by affected_record_id\n ) a\n order by ct desc \n ) b,\n (SELECT @rn := 0) t2\n ) mm \n )\n group by app_name, DATE(audit_date)\n) r\non l.Date = r.audit_date_1\nand l.app_name = r.app_name</reportSQL>\n <reportTitle></reportTitle>\n <reportSubTitle></reportSubTitle>\n <reportHeader></reportHeader>\n <frozenColumns>0</frozenColumns>\n <emptyMessage>Your Search didn\'t yield any results.</emptyMessage>\n <dataGridAlign>left</dataGridAlign>\n <reportFooter></reportFooter>\n <numFormCols>1</numFormCols>\n <displayOptions>NNNNNNN</displayOptions>\n <dataContainerHeight>100</dataContainerHeight>\n <dataContainerWidth>100</dataContainerWidth>\n <allowSchedule>N</allowSchedule>\n <multiGroupColumn>N</multiGroupColumn>\n <topDown>N</topDown>\n <sizedByContent>N</sizedByContent>\n <comment>N|</comment>\n <dataSourceList>\n <dataSource tableId="du0">\n <tableName>DUAL</tableName>\n <tablePK></tablePK>\n <displayName>DUAL</displayName>\n <dataColumnList>\n <dataColumn colId="audit_date">\n <tableId>du0</tableId>\n <dbColName>l.date</dbColName>\n <colName>l.date</colName>\n <displayName>audit_date_1</displayName>\n <displayWidth>10</displayWidth>\n <displayWidthInPxls>nullpxpx</displayWidthInPxls>\n <displayAlignment>Left</displayAlignment>\n <orderSeq>1</orderSeq>\n <visible>true</visible>\n <calculated>true</calculated>\n <colType>VARCHAR2</colType>\n <groupBreak>false</groupBreak>\n <colOnChart>LEGEND</colOnChart>\n <chartSeq>1</chartSeq>\n <chartColor></chartColor>\n <chartLineType></chartLineType>\n <chartSeries>false</chartSeries>\n <dbColType>VARCHAR2</dbColType>\n <chartGroup></chartGroup>\n <yAxis></yAxis>\n </dataColumn>\n <dataColumn colId="app_id">\n <tableId>du0</tableId>\n <dbColName>app_id</dbColName>\n <colName>app_id</colName>\n <displayName>app_id</displayName>\n <displayWidth>10</displayWidth>\n <displayAlignment>Left</displayAlignment>\n <orderSeq>2</orderSeq>\n <visible>true</visible>\n <calculated>true</calculated>\n <colType>VARCHAR2</colType>\n <groupBreak>false</groupBreak>\n <dbColType>VARCHAR2</dbColType>\n </dataColumn>\n <dataColumn colId="app_name">\n <tableId>du0</tableId>\n <dbColName>IF(CHAR_LENGTH(l.app_name) >14, CONCAT(CONCAT(SUBSTR(l.app_name,1,7),\'...\'), SUBSTR(l.app_name, CHAR_LENGTH(l.app_name)-3,CHAR_LENGTH(l.app_name))) , l.app_name)</dbColName>\n <colName>IF(CHAR_LENGTH(l.app_name) >14, CONCAT(CONCAT(SUBSTR(l.app_name,1,7),\'...\'), SUBSTR(l.app_name, CHAR_LENGTH(l.app_name)-3,CHAR_LENGTH(l.app_name))) , l.app_name)</colName>\n <displayName>app_name</displayName>\n <displayWidth>10</displayWidth>\n <displayWidthInPxls>nullpxpx</displayWidthInPxls>\n <displayAlignment>Left</displayAlignment>\n <orderSeq>3</orderSeq>\n <visible>true</visible>\n <calculated>true</calculated>\n <colType>VARCHAR2</colType>\n <groupBreak>false</groupBreak>\n <chartSeq>2</chartSeq>\n <chartColor></chartColor>\n <chartLineType></chartLineType>\n <chartSeries>true</chartSeries>\n <dbColType>VARCHAR2</dbColType>\n <chartGroup></chartGroup>\n <yAxis></yAxis>\n </dataColumn>\n <dataColumn colId="ct">\n <tableId>du0</tableId>\n <dbColName>IFNULL(r.ct,0)</dbColName>\n <colName>IFNULL(r.ct,0)</colName>\n <displayName>ct</displayName>\n <displayWidth>10</displayWidth>\n <displayWidthInPxls>nullpxpx</displayWidthInPxls>\n <displayAlignment>Left</displayAlignment>\n <orderSeq>4</orderSeq>\n <visible>true</visible>\n <calculated>true</calculated>\n <colType>VARCHAR2</colType>\n <groupBreak>false</groupBreak>\n <colOnChart>0</colOnChart>\n <chartSeq>1</chartSeq>\n <chartColor></chartColor>\n <chartLineType></chartLineType>\n <chartSeries>false</chartSeries>\n <dbColType>VARCHAR2</dbColType>\n <chartGroup></chartGroup>\n <yAxis></yAxis>\n </dataColumn>\n </dataColumnList>\n </dataSource>\n </dataSourceList>\n <reportInNewWindow>false</reportInNewWindow>\n <displayFolderTree>false</displayFolderTree>\n <maxRowsInExcelDownload>500</maxRowsInExcelDownload>\n <chartAdditionalOptions>\n <chartOrientation>vertical</chartOrientation>\n <hidechartLegend>N</hidechartLegend>\n <legendPosition>bottom</legendPosition>\n <labelAngle>up90</labelAngle>\n <rangeAxisUpperLimit></rangeAxisUpperLimit>\n <rangeAxisLowerLimit></rangeAxisLowerLimit>\n <animate>true</animate>\n <animateAnimatedChart>true</animateAnimatedChart>\n <stacked>true</stacked>\n <barControls>false</barControls>\n <xAxisDateType>false</xAxisDateType>\n <lessXaxisTickers>false</lessXaxisTickers>\n <timeAxis>true</timeAxis>\n <logScale>false</logScale>\n <topMargin>30</topMargin>\n <bottomMargin>50</bottomMargin>\n <rightMargin>60</rightMargin>\n <leftMargin>100</leftMargin>\n </chartAdditionalOptions>\n <folderId>NULL</folderId>\n <isOneTimeScheduleAllowed>N</isOneTimeScheduleAllowed>\n <isHourlyScheduleAllowed>N</isHourlyScheduleAllowed>\n <isDailyScheduleAllowed>N</isDailyScheduleAllowed>\n <isDailyMFScheduleAllowed>N</isDailyMFScheduleAllowed>\n <isWeeklyScheduleAllowed>N</isWeeklyScheduleAllowed>\n <isMonthlyScheduleAllowed>N</isMonthlyScheduleAllowed>\n</customReport>\n',
+ 1,
+ now(),
+ 1,
+ now(),
+ '',
+ 'N',
+ (select user_id from fn_user where org_user_id = 'demo'),
+ NULL,
+ 'N',
+ 'N'
+ );
+
+-- new for 1707
+INSERT INTO cr_report
+ (rep_id, title, descr, public_yn, report_xml, create_id, create_date, maint_id, maint_date, menu_id, menu_approved_yn, owner_id, folder_id, dashboard_type_yn, dashboard_yn)
+ VALUES (
+ 18,
+ 'Application Usage bar Wid',
+ '',
+ 'Y',
+ '<?xml version=\"1.0\" encoding=\"UTF-8\" standalone=\"yes\"?>\n<customReport pageSize=\"200\" reportType=\"Linear\">\n <reportName>Application Usage Line Wid</reportName>\n <reportDescr></reportDescr>\n <dbInfo>local</dbInfo>\n <dbType>mysql</dbType>\n <chartType>TimeSeriesChart</chartType>\n <chartMultiSeries>N</chartMultiSeries>\n <chartWidth>700</chartWidth>\n <chartHeight>300</chartHeight>\n <showChartTitle>false</showChartTitle>\n <public>false</public>\n <hideFormFieldAfterRun>false</hideFormFieldAfterRun>\n <createId>27</createId>\n <createDate>2017-01-28-05:00</createDate>\n <reportSQL>SELECT \n l.date audit_date, \n IF(CHAR_LENGTH(l.app_name) >14, CONCAT(CONCAT(SUBSTR(l.app_name,1,7),\'...\'), SUBSTR(l.app_name, CHAR_LENGTH(l.app_name)-3,CHAR_LENGTH(l.app_name))) , l.app_name) app_name, \n IFNULL(r.ct,0) ct \nfrom\n(\n select a.Date, app_id, app_name\n from (\n select curdate() - INTERVAL (a.a + (10 * b.a) + (100 * c.a)) DAY as Date\n from (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as a\n cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as b\n cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as c\n ) a, \n (\n SELECT \n app_id, app_name\n from\n (\n select @rn := @rn+1 AS rowId, app_id, app_name from \n (\n select app_id, app_name, ct from \n (\n select affected_record_id, count(*) ct\n from fn_audit_log l\n where audit_date > date_add( curdate(), interval -30 day)\n and affected_record_id not in ( 1, -1)\n and activity_cd in (\'tab_access\', \'app_access\')\n and user_id = [USER_ID]\n group by affected_record_id\n ) a, fn_app f\n where a.affected_record_id = f.app_id\n order by ct desc \n ) b,\n (SELECT @rn := 0) t2\n ) mm where rowId <= 4\n )b\n where a.Date between date_add( curdate(), interval -30 day) and curdate()\n) l left outer join\n(\n select app_name, DATE(audit_date) audit_date_1 ,count(*) ct from fn_audit_log a, fn_app b\n where user_id = [USER_ID]\n and audit_date > date_add( curdate(), interval -30 day)\n and activity_cd in (\'tab_access\', \'app_access\')\n and a.affected_record_id = b.app_id\n and b.app_id <> 1\n and b.app_id in \n (\n SELECT \n app_id\n from\n (\n select @rn := @rn+1 AS rowId, app_id from \n (\n select app_id, ct from \n (\n select affected_record_id app_id, count(*) ct\n from fn_audit_log \n where audit_date > date_add( curdate(), interval -30 day)\n and affected_record_id not in ( 1, -1)\n and activity_cd in (\'tab_access\', \'app_access\')\n and user_id = [USER_ID]\n group by affected_record_id\n ) a\n order by ct desc \n ) b,\n (SELECT @rn := 0) t2\n ) mm \n )\n group by app_name, DATE(audit_date)\n) r\non l.Date = r.audit_date_1\nand l.app_name = r.app_name</reportSQL>\n <reportTitle></reportTitle>\n <reportSubTitle></reportSubTitle>\n <reportHeader></reportHeader>\n <frozenColumns>0</frozenColumns>\n <emptyMessage>Your Search didn\'t yield any results.</emptyMessage>\n <dataGridAlign>left</dataGridAlign>\n <reportFooter></reportFooter>\n <numFormCols>1</numFormCols>\n <displayOptions>NNNNNNN</displayOptions>\n <dataContainerHeight>100</dataContainerHeight>\n <dataContainerWidth>100</dataContainerWidth>\n <allowSchedule>N</allowSchedule>\n <multiGroupColumn>N</multiGroupColumn>\n <topDown>N</topDown>\n <sizedByContent>N</sizedByContent>\n <comment>N|</comment>\n <dataSourceList>\n <dataSource tableId=\"du0\">\n <tableName>DUAL</tableName>\n <tablePK></tablePK>\n <displayName>DUAL</displayName>\n <dataColumnList>\n <dataColumn colId=\"audit_date\">\n <tableId>du0</tableId>\n <dbColName>l.date</dbColName>\n <colName>l.date</colName>\n <displayName>audit_date_1</displayName>\n <displayWidth>10</displayWidth>\n <displayWidthInPxls>nullpxpx</displayWidthInPxls>\n <displayAlignment>Left</displayAlignment>\n <orderSeq>1</orderSeq>\n <visible>true</visible>\n <calculated>true</calculated>\n <colType>VARCHAR2</colType>\n <groupBreak>false</groupBreak>\n <colOnChart>LEGEND</colOnChart>\n <chartSeq>1</chartSeq>\n <chartSeries>false</chartSeries>\n <isRangeAxisFilled>false</isRangeAxisFilled>\n <drillinPoPUp>false</drillinPoPUp>\n <dbColType>VARCHAR2</dbColType>\n <enhancedPagination>false</enhancedPagination>\n </dataColumn>\n <dataColumn colId=\"app_name\">\n <tableId>du0</tableId>\n <dbColName>IF(CHAR_LENGTH(l.app_name) >14, CONCAT(CONCAT(SUBSTR(l.app_name,1,7),\'...\'), SUBSTR(l.app_name, CHAR_LENGTH(l.app_name)-3,CHAR_LENGTH(l.app_name))) , l.app_name)</dbColName>\n <colName>IF(CHAR_LENGTH(l.app_name) >14, CONCAT(CONCAT(SUBSTR(l.app_name,1,7),\'...\'), SUBSTR(l.app_name, CHAR_LENGTH(l.app_name)-3,CHAR_LENGTH(l.app_name))) , l.app_name)</colName>\n <displayName>app_name</displayName>\n <displayWidth>10</displayWidth>\n <displayWidthInPxls>nullpxpx</displayWidthInPxls>\n <displayAlignment>Left</displayAlignment>\n <orderSeq>2</orderSeq>\n <visible>true</visible>\n <calculated>true</calculated>\n <colType>VARCHAR2</colType>\n <groupBreak>false</groupBreak>\n <colOnChart>0</colOnChart>\n <chartSeq>2</chartSeq>\n <chartColor></chartColor>\n <chartLineType></chartLineType>\n <chartSeries>true</chartSeries>\n <isRangeAxisFilled>false</isRangeAxisFilled>\n <drillinPoPUp>false</drillinPoPUp>\n <dbColType>VARCHAR2</dbColType>\n <chartGroup></chartGroup>\n <yAxis></yAxis>\n <enhancedPagination>false</enhancedPagination>\n </dataColumn>\n <dataColumn colId=\"ct\">\n <tableId>du0</tableId>\n <dbColName>IFNULL(r.ct,0)</dbColName>\n <colName>IFNULL(r.ct,0)</colName>\n <displayName>ct</displayName>\n <displayWidth>10</displayWidth>\n <displayWidthInPxls>nullpxpx</displayWidthInPxls>\n <displayAlignment>Left</displayAlignment>\n <orderSeq>3</orderSeq>\n <visible>true</visible>\n <calculated>true</calculated>\n <colType>VARCHAR2</colType>\n <groupBreak>false</groupBreak>\n <colOnChart>0</colOnChart>\n <chartSeq>1</chartSeq>\n <chartColor></chartColor>\n <chartLineType></chartLineType>\n <chartSeries>false</chartSeries>\n <isRangeAxisFilled>false</isRangeAxisFilled>\n <drillinPoPUp>false</drillinPoPUp>\n <dbColType>VARCHAR2</dbColType>\n <chartGroup></chartGroup>\n <yAxis></yAxis>\n <enhancedPagination>false</enhancedPagination>\n </dataColumn>\n </dataColumnList>\n </dataSource>\n </dataSourceList>\n <reportInNewWindow>false</reportInNewWindow>\n <displayFolderTree>false</displayFolderTree>\n <maxRowsInExcelDownload>500</maxRowsInExcelDownload>\n <chartAdditionalOptions>\n <chartOrientation>vertical</chartOrientation>\n <hidechartLegend>N</hidechartLegend>\n <legendPosition>bottom</legendPosition>\n <labelAngle>down45</labelAngle>\n <animate>true</animate>\n <animateAnimatedChart>true</animateAnimatedChart>\n <stacked>true</stacked>\n <barControls>false</barControls>\n <xAxisDateType>false</xAxisDateType>\n <lessXaxisTickers>false</lessXaxisTickers>\n <timeAxis>true</timeAxis>\n <timeSeriesRender>line</timeSeriesRender>\n <multiSeries>false</multiSeries>\n <showXAxisLabel>false</showXAxisLabel>\n <addXAxisTickers>false</addXAxisTickers>\n <topMargin>30</topMargin>\n <bottomMargin>50</bottomMargin>\n <rightMargin>60</rightMargin>\n <leftMargin>100</leftMargin>\n </chartAdditionalOptions>\n <folderId>NULL</folderId>\n <drillURLInPoPUpPresent>false</drillURLInPoPUpPresent>\n <isOneTimeScheduleAllowed>N</isOneTimeScheduleAllowed>\n <isHourlyScheduleAllowed>N</isHourlyScheduleAllowed>\n <isDailyScheduleAllowed>N</isDailyScheduleAllowed>\n <isDailyMFScheduleAllowed>N</isDailyMFScheduleAllowed>\n <isWeeklyScheduleAllowed>N</isWeeklyScheduleAllowed>\n <isMonthlyScheduleAllowed>N</isMonthlyScheduleAllowed>\n</customReport>\n',
+ 1,
+ now(),
+ 1,
+ now(),
+ '',
+ 'N',
+ (select user_id from fn_user where org_user_id = 'demo'),
+ NULL,
+ 'N',
+ 'N'
+ );
+
+INSERT INTO cr_report
+ (rep_id, title, descr, public_yn, report_xml, create_id, create_date, maint_id, maint_date, menu_id, menu_approved_yn, owner_id, folder_id, dashboard_type_yn, dashboard_yn)
+ VALUES (
+ 20,
+ 'Average time spend on portal',
+ '',
+ 'Y',
+ '<?xml version=\"1.0\" encoding=\"UTF-8\" standalone=\"yes\"?>\n<customReport pageSize=\"200\" reportType=\"Linear\">\n <reportName>Average time spend on portal</reportName>\n <reportDescr></reportDescr>\n <dbInfo>local</dbInfo>\n <dbType>mysql</dbType>\n <chartType>TimeSeriesChart</chartType>\n <chartMultiSeries>N</chartMultiSeries>\n <chartWidth>700</chartWidth>\n <chartHeight>300</chartHeight>\n <showChartTitle>false</showChartTitle>\n <public>true</public>\n <hideFormFieldAfterRun>false</hideFormFieldAfterRun>\n <createId>27</createId>\n <createDate>2017-01-28-05:00</createDate>\n <reportSQL>SELECT \n d.dat audit_date, \n \'# of Minutes\' app, \n coalesce(diff, null, 0) mins \nfrom\n(\n select * from\n (\n select curdate() - INTERVAL (a.a + (10 * b.a) + (100 * c.a)) DAY as dat\n from (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as a\n cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as b\n cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as c \n ) d where d.dat between date_add( curdate(), interval -30 day) and curdate()\n) d left outer join\n(\n select dat, mi, mx, TIMESTAMPDIFF(MINUTE, coalesce(mi, null, 0), coalesce(mx, null, 0)) + 30 diff\n from\n (\n select DATE(audit_date) dat, coalesce(min(audit_date), null, 0) mi, coalesce(max(audit_date), null, 0) mx\n from fn_audit_log \n where user_id = [USER_ID] and DATE(audit_date) between CURDATE()-300 and CURDATE()\n group by DATE(audit_date)\n ) a\n) a\non a.dat = d.dat\norder by 1</reportSQL>\n <reportTitle></reportTitle>\n <reportSubTitle></reportSubTitle>\n <reportHeader></reportHeader>\n <frozenColumns>0</frozenColumns>\n <emptyMessage>Your Search didn\'t yield any results.</emptyMessage>\n <dataGridAlign>left</dataGridAlign>\n <reportFooter></reportFooter>\n <numFormCols>1</numFormCols>\n <displayOptions>NNNNNNN</displayOptions>\n <dataContainerHeight>100</dataContainerHeight>\n <dataContainerWidth>100</dataContainerWidth>\n <allowSchedule>N</allowSchedule>\n <multiGroupColumn>N</multiGroupColumn>\n <topDown>N</topDown>\n <sizedByContent>N</sizedByContent>\n <comment>N|</comment>\n <dataSourceList>\n <dataSource tableId=\"du0\">\n <tableName>DUAL</tableName>\n <tablePK></tablePK>\n <displayName>DUAL</displayName>\n <dataColumnList>\n <dataColumn colId=\"audit_date\">\n <tableId>du0</tableId>\n <dbColName>d.dat</dbColName>\n <colName>d.dat</colName>\n <displayName>audit_date_1</displayName>\n <displayWidth>10</displayWidth>\n <displayWidthInPxls>nullpxpx</displayWidthInPxls>\n <displayAlignment>Left</displayAlignment>\n <orderSeq>1</orderSeq>\n <visible>true</visible>\n <calculated>true</calculated>\n <colType>VARCHAR2</colType>\n <groupBreak>false</groupBreak>\n <colOnChart>LEGEND</colOnChart>\n <chartSeq>1</chartSeq>\n <chartSeries>false</chartSeries>\n <isRangeAxisFilled>false</isRangeAxisFilled>\n <drillinPoPUp>false</drillinPoPUp>\n <dbColType>VARCHAR2</dbColType>\n <enhancedPagination>false</enhancedPagination>\n </dataColumn>\n <dataColumn colId=\"app\">\n <tableId>du0</tableId>\n <dbColName>\'# of Minutes\'</dbColName>\n <colName>\'# of Minutes\'</colName>\n <displayName>app</displayName>\n <displayWidth>10</displayWidth>\n <displayWidthInPxls>nullpxpx</displayWidthInPxls>\n <displayAlignment>Left</displayAlignment>\n <orderSeq>2</orderSeq>\n <visible>true</visible>\n <calculated>true</calculated>\n <colType>VARCHAR2</colType>\n <groupBreak>false</groupBreak>\n <chartSeries>true</chartSeries>\n <dbColType>VARCHAR2</dbColType>\n </dataColumn>\n <dataColumn colId=\"mins\">\n <tableId>du0</tableId>\n <dbColName>coalesce(diff, null, 0)</dbColName>\n <colName>coalesce(diff, null, 0)</colName>\n <displayName>mins</displayName>\n <displayWidth>10</displayWidth>\n <displayWidthInPxls>nullpxpx</displayWidthInPxls>\n <displayAlignment>Left</displayAlignment>\n <orderSeq>3</orderSeq>\n <visible>true</visible>\n <calculated>true</calculated>\n <colType>VARCHAR2</colType>\n <groupBreak>false</groupBreak>\n <colOnChart>0</colOnChart>\n <chartSeq>1</chartSeq>\n <chartColor></chartColor>\n <chartLineType></chartLineType>\n <chartSeries>false</chartSeries>\n <dbColType>VARCHAR2</dbColType>\n <chartGroup></chartGroup>\n <yAxis></yAxis>\n </dataColumn>\n </dataColumnList>\n </dataSource>\n </dataSourceList>\n <reportInNewWindow>false</reportInNewWindow>\n <displayFolderTree>false</displayFolderTree>\n <maxRowsInExcelDownload>500</maxRowsInExcelDownload>\n <chartAdditionalOptions>\n <chartOrientation>vertical</chartOrientation>\n <hidechartLegend>N</hidechartLegend>\n <legendPosition>bottom</legendPosition>\n <labelAngle>down45</labelAngle>\n <animate>true</animate>\n <animateAnimatedChart>true</animateAnimatedChart>\n <stacked>true</stacked>\n <barControls>false</barControls>\n <xAxisDateType>false</xAxisDateType>\n <lessXaxisTickers>false</lessXaxisTickers>\n <timeAxis>true</timeAxis>\n <timeSeriesRender>line</timeSeriesRender>\n <multiSeries>false</multiSeries>\n <showXAxisLabel>false</showXAxisLabel>\n <addXAxisTickers>false</addXAxisTickers>\n <topMargin>30</topMargin>\n <bottomMargin>50</bottomMargin>\n <rightMargin>60</rightMargin>\n <leftMargin>100</leftMargin>\n </chartAdditionalOptions>\n <folderId>NULL</folderId>\n <drillURLInPoPUpPresent>false</drillURLInPoPUpPresent>\n <isOneTimeScheduleAllowed>N</isOneTimeScheduleAllowed>\n <isHourlyScheduleAllowed>N</isHourlyScheduleAllowed>\n <isDailyScheduleAllowed>N</isDailyScheduleAllowed>\n <isDailyMFScheduleAllowed>N</isDailyMFScheduleAllowed>\n <isWeeklyScheduleAllowed>N</isWeeklyScheduleAllowed>\n <isMonthlyScheduleAllowed>N</isMonthlyScheduleAllowed>\n</customReport>\n',
+ 1,
+ now(),
+ 1,
+ now(),
+ '',
+ 'N',
+ (select user_id from fn_user where org_user_id = 'demo'),
+ NULL,
+ 'N',
+ 'N'
+ );
+
+
+insert into ep_app_function (app_id, function_cd, function_name) values
+(1, 'url|edit_notification|*', 'User Notification'),
+(1, 'url|getAdminNotifications|*', 'Admin Notifications'),
+(1, 'url|login|*', 'Login'),
+(1, 'menu|menu_admin|*','Admin Menu'),
+(1,'menu|menu_home|*','Home Menu'),
+(1, 'menu|menu_logout|*','Logout Menu'),
+(1, 'menu|menu_web_analytics|*','Web Analytics'),
+(1, 'url|saveNotification|*','publish notifications'),
+(1, 'url|url_role.htm|*','role page'),
+(1, 'url|url_welcome.htm|*','welcome page'),
+(1, 'menu|menu_acc_admin|*','Admin Account Menu'),
+(1, 'menu|menu_app_onboarding|*','Application Onboarding'),
+(1,'url|addWebAnalyticsReport|*','Add Web Analytics Report'),
+(1,'url|appsFullList|*','Apps Full List'),
+(1,'url|centralizedApps|*','Centralized Apps'),
+(1,'url|functionalMenu|*','Functional Menu'),
+(1,'url|getAllWebAnalytics|*','Get All Web Analytics'),
+(1,'url|getFunctionalMenuRole|*','Get Functional Menu Role'),
+(1,'url|getNotificationAppRoles|*','Get Notification App Roles'),
+(1,'url|getUserAppsWebAnalytics|*','Get User Apps Web Analytics'),
+(1,'url|getUserJourneyAnalyticsReport|*','Get User Journey Report'),
+(1,'url|get_roles%2f%2a|*','getRolesOfApp'),
+(1,'url|get_role_functions%2f%2a|*','Get Role Functions'),
+(1,'url|notification_code|*','Notification Code'),
+(1,'url|role_function_list%2fsaveRoleFunction%2f%2a|*','Save Role Function'),
+(1,'url|syncRoles|*','SyncRoles'),
+(1,'url|userAppRoles|*','userAppRoles'),
+(1,'url|userApps|*','User Apps')
+;
+
+
+insert into ep_app_role_function (id, app_id, role_id, function_cd, role_app_id) values
+(1, 1, 1, 'url|login|*', null),
+(2, 1, 1, 'menu|menu_admin|*', null),
+(3, 1, 1, 'menu|menu_home|*', null),
+(4, 1, 1, 'menu|menu_logout|*', null),
+(5, 1, 16, 'url|login|*', null),
+(6, 1, 16, 'menu|menu_home|*', null),
+(7, 1, 16, 'menu|menu_logout|*', null),
+(8, 1, 950, 'url|edit_notification|*', null),
+(9, 1, 950, 'url|getAdminNotifications|*', null),
+(10,1, 950, 'url|saveNotification|*', null),
+(11,1, 999,'url|userAppRoles|*', null),
+(12,1, 999, 'url|getAdminNotifications|*', null),
+(13,1, 999,'url|userApps|*', null),
+(14,1, 1010, 'menu|menu_web_analytics|*', null),
+(15, 1, 2115, 'menu|menu_web_analytics|*', null),
+(16, 1 , 1, 'menu|menu_acc_admin|*' , null),
+(17, 1 , 999 ,'menu|menu_acc_admin|*', null),
+(18,1,999,'url|centralizedApps|*', null),
+(19,1,999,'url|getAllWebAnalytics|*', null),
+(20,1,999,'url|getFunctionalMenuRole|*', null),
+(21,1,999,'url|getNotificationAppRoles|*', null),
+(22,1,999,'url|getUserAppsWebAnalytics|*', null),
+(23,1,999,'url|getUserJourneyAnalyticsReport|*', null),
+(24,1,999,'url|get_roles%2f%2a|*', null),
+(25,1,999,'url|get_role_functions%2f%2a|*', null),
+(26,1,999,'url|notification_code|*', null),
+(27,1,999,'url|role_function_list%2fsaveRoleFunction%2f%2a|*', null),
+(28,1,999,'url|syncRoles|*', null),
+(29, 1, 1, 'menu|menu_app_onboarding|*', null)
+;
+
+commit;
Code Review / portal.git / commitdiff