1 -- ---------------------------------------------------------------------------------------------------------------
2 -- This script creates tables in the 1707 COMMON version of the ECOMP SDK application database.
3 -- Additional DDL scripts may be required for the AT&T version or the OPEN-SOURCE version!
5 -- Note to database admin: set the MySQL system variable called lower_case_table_names to 1
6 -- It can be set 3 different ways:
7 -- command-line options (Cmd-line),
8 -- options valid in configuration files (Option file), or
9 -- server system variables (System Var).
11 -- When set to 1 table names are stored in lowercase on disk and comparisons are not case sensitive.
13 -- MySql/MariaDB Version compatibility information
15 -- bash-4.2$ mysql --version – cluster version
16 -- mysql Ver 15.1 Distrib 10.1.17-MariaDB, for Linux (x86_64) using readline 5.1
18 -- All versions newer or older than these DO NOT necessarily mean they are compatible.
19 -- ---------------------------------------------------------------------------------------------------------------
21 SET FOREIGN_KEY_CHECKS=1;
23 CREATE DATABASE IF NOT EXISTS ecomp_sdk;
27 -- ---------- create table SECTION
29 -- NAME: CR_FAVORITE_REPORTS; TYPE: TABLE
31 create table cr_favorite_reports (
32 USER_ID INTEGER NOT NULL,
33 REP_ID INTEGER NOT NULL
37 -- NAME: CR_FILEHIST_LOG; TYPE: TABLE
39 create table cr_filehist_log (
40 SCHEDULE_ID NUMERIC(11,0) NOT NULL,
41 URL CHARACTER VARYING(4000),
42 NOTES CHARACTER VARYING(3500),
47 -- NAME: CR_FOLDER; TYPE: TABLE
49 create table cr_folder (
50 FOLDER_ID INTEGER NOT NULL,
51 FOLDER_NAME CHARACTER VARYING(50) NOT NULL,
52 DESCR CHARACTER VARYING(500),
53 CREATE_ID INTEGER NOT NULL,
54 CREATE_DATE TIMESTAMP NOT NULL,
55 PARENT_FOLDER_ID INTEGER,
56 PUBLIC_YN CHARACTER VARYING(1) DEFAULT 'N' NOT NULL
60 -- NAME: CR_FOLDER_ACCESS; TYPE: TABLE
62 create table cr_folder_access (
63 FOLDER_ACCESS_ID NUMERIC(11,0) NOT NULL,
64 FOLDER_ID NUMERIC(11,0) NOT NULL,
65 ORDER_NO NUMERIC(11,0) NOT NULL,
66 ROLE_ID NUMERIC(11,0),
67 USER_ID NUMERIC(11,0),
68 READ_ONLY_YN CHARACTER VARYING(1) DEFAULT 'N' NOT NULL
72 -- NAME: CR_HIST_USER_MAP; TYPE: TABLE
74 create table cr_hist_user_map (
75 HIST_ID INT(11) NOT NULL,
76 USER_ID INT(11) NOT NULL
80 -- NAME: CR_LU_FILE_TYPE; TYPE: TABLE
82 create table cr_lu_file_type (
83 LOOKUP_ID NUMERIC(2,0) NOT NULL,
84 LOOKUP_DESCR CHARACTER VARYING(255) NOT NULL,
85 ACTIVE_YN CHARACTER(1) DEFAULT 'Y',
86 ERROR_CODE NUMERIC(11,0)
90 -- NAME: CR_RAPTOR_ACTION_IMG; TYPE: TABLE
92 create table cr_raptor_action_img (
93 IMAGE_ID CHARACTER VARYING(100) NOT NULL,
94 IMAGE_LOC CHARACTER VARYING(400)
98 -- NAME: CR_RAPTOR_PDF_IMG; TYPE: TABLE
100 create table cr_raptor_pdf_img (
101 IMAGE_ID CHARACTER VARYING(100) NOT NULL,
102 IMAGE_LOC CHARACTER VARYING(400)
106 -- NAME: CR_REMOTE_SCHEMA_INFO; TYPE: TABLE
108 create table cr_remote_schema_info (
109 SCHEMA_PREFIX CHARACTER VARYING(5) NOT NULL,
110 SCHEMA_DESC CHARACTER VARYING(75) NOT NULL,
111 DATASOURCE_TYPE CHARACTER VARYING(100)
115 -- NAME: CR_REPORT; TYPE: TABLE
117 create table cr_report (
118 REP_ID NUMERIC(11,0) NOT NULL,
119 TITLE CHARACTER VARYING(100) NOT NULL,
120 DESCR CHARACTER VARYING(255),
121 PUBLIC_YN CHARACTER VARYING(1) DEFAULT 'N' NOT NULL,
123 CREATE_ID NUMERIC(11,0),
124 CREATE_DATE TIMESTAMP default now(),
125 MAINT_ID NUMERIC(11,0),
126 MAINT_DATE TIMESTAMP DEFAULT NOW(),
127 MENU_ID CHARACTER VARYING(500),
128 MENU_APPROVED_YN CHARACTER VARYING(1) DEFAULT 'N' NOT NULL,
129 OWNER_ID NUMERIC(11,0),
130 FOLDER_ID INTEGER DEFAULT 0,
131 DASHBOARD_TYPE_YN CHARACTER VARYING(1) DEFAULT 'N',
132 DASHBOARD_YN CHARACTER VARYING(1) DEFAULT 'N'
136 -- NAME: CR_REPORT_ACCESS; TYPE: TABLE
138 create table cr_report_access (
139 REP_ID NUMERIC(11,0) NOT NULL,
140 ORDER_NO NUMERIC(11,0) NOT NULL,
141 ROLE_ID NUMERIC(11,0),
142 USER_ID NUMERIC(11,0),
143 READ_ONLY_YN CHARACTER VARYING(1) DEFAULT 'N' NOT NULL
147 -- NAME: CR_REPORT_DWNLD_LOG; TYPE: TABLE
149 create table cr_report_dwnld_log (
150 USER_ID NUMERIC(11,0) NOT NULL,
151 REP_ID INTEGER NOT NULL,
152 FILE_NAME CHARACTER VARYING(100) NOT NULL,
153 DWNLD_START_TIME TIMESTAMP DEFAULT NOW() NOT NULL,
154 RECORD_READY_TIME TIMESTAMP DEFAULT NOW(),
155 FILTER_PARAMS CHARACTER VARYING(2000)
159 -- NAME: CR_REPORT_EMAIL_SENT_LOG; TYPE: TABLE
161 create table cr_report_email_sent_log (
162 LOG_ID INTEGER NOT NULL,
163 SCHEDULE_ID NUMERIC(11,0),
164 GEN_KEY CHARACTER VARYING(25) NOT NULL,
165 REP_ID NUMERIC(11,0) NOT NULL,
166 USER_ID NUMERIC(11,0),
167 SENT_DATE TIMESTAMP DEFAULT NOW(),
168 ACCESS_FLAG CHARACTER VARYING(1) DEFAULT 'Y' NOT NULL,
169 TOUCH_DATE TIMESTAMP DEFAULT NOW()
173 -- NAME: CR_REPORT_FILE_HISTORY; TYPE: TABLE
175 create table cr_report_file_history (
176 HIST_ID INT(11) NOT NULL,
177 SCHED_USER_ID NUMERIC(11,0) NOT NULL,
178 SCHEDULE_ID NUMERIC(11,0) NOT NULL,
179 USER_ID NUMERIC(11,0) NOT NULL,
180 REP_ID NUMERIC(11,0),
182 RECURRENCE CHARACTER VARYING(50),
183 FILE_TYPE_ID NUMERIC(2,0),
184 FILE_NAME CHARACTER VARYING(80),
186 FILE_SIZE NUMERIC(11,0),
187 RAPTOR_URL CHARACTER VARYING(4000),
188 ERROR_YN CHARACTER(1) DEFAULT 'N',
189 ERROR_CODE NUMERIC(11,0),
190 DELETED_YN CHARACTER(1) DEFAULT 'N',
191 DELETED_BY NUMERIC(38,0)
195 -- NAME: CR_REPORT_LOG; TYPE: TABLE
197 create table cr_report_log (
198 REP_ID NUMERIC(11,0) NOT NULL,
199 LOG_TIME TIMESTAMP NOT NULL,
200 USER_ID NUMERIC(11,0) NOT NULL,
201 ACTION CHARACTER VARYING(2000) NOT NULL,
202 ACTION_VALUE CHARACTER VARYING(50),
203 FORM_FIELDS CHARACTER VARYING(4000)
207 -- NAME: CR_REPORT_SCHEDULE; TYPE: TABLE
209 create table cr_report_schedule (
210 SCHEDULE_ID NUMERIC(11,0) NOT NULL,
211 SCHED_USER_ID NUMERIC(11,0) NOT NULL,
212 REP_ID NUMERIC(11,0) NOT NULL,
213 ENABLED_YN CHARACTER VARYING(1) NOT NULL,
214 START_DATE TIMESTAMP DEFAULT NOW(),
215 END_DATE TIMESTAMP DEFAULT NOW(),
216 RUN_DATE TIMESTAMP DEFAULT NOW(),
217 RECURRENCE CHARACTER VARYING(50),
218 CONDITIONAL_YN CHARACTER VARYING(1) NOT NULL,
219 CONDITION_SQL CHARACTER VARYING(4000),
220 NOTIFY_TYPE INTEGER DEFAULT 0,
221 MAX_ROW INTEGER DEFAULT 1000,
222 INITIAL_FORMFIELDS CHARACTER VARYING(3500),
223 PROCESSED_FORMFIELDS CHARACTER VARYING(3500),
224 FORMFIELDS CHARACTER VARYING(3500),
225 CONDITION_LARGE_SQL TEXT,
226 ENCRYPT_YN CHARACTER(1) DEFAULT 'N',
227 ATTACHMENT_YN CHARACTER(1) DEFAULT 'Y'
231 -- NAME: CR_REPORT_SCHEDULE_USERS; TYPE: TABLE
233 create table cr_report_schedule_users (
234 SCHEDULE_ID NUMERIC(11,0) NOT NULL,
235 REP_ID NUMERIC(11,0) NOT NULL,
236 USER_ID NUMERIC(11,0) NOT NULL,
237 ROLE_ID NUMERIC(11,0),
238 ORDER_NO NUMERIC(11,0) NOT NULL
242 -- NAME: CR_REPORT_TEMPLATE_MAP; TYPE: TABLE
244 create table cr_report_template_map (
245 REPORT_ID INTEGER NOT NULL,
246 TEMPLATE_FILE CHARACTER VARYING(200)
250 -- NAME: CR_SCHEDULE_ACTIVITY_LOG; TYPE: TABLE
252 create table cr_schedule_activity_log (
253 SCHEDULE_ID NUMERIC(11,0) NOT NULL,
254 URL CHARACTER VARYING(4000),
255 NOTES CHARACTER VARYING(2000),
260 -- NAME: CR_TABLE_JOIN; TYPE: TABLE
262 create table cr_table_join (
263 SRC_TABLE_NAME CHARACTER VARYING(30) NOT NULL,
264 DEST_TABLE_NAME CHARACTER VARYING(30) NOT NULL,
265 JOIN_EXPR CHARACTER VARYING(500) NOT NULL
269 -- NAME: CR_TABLE_ROLE; TYPE: TABLE
271 create table cr_table_role (
272 TABLE_NAME CHARACTER VARYING(30) NOT NULL,
273 ROLE_ID NUMERIC(11,0) NOT NULL
277 -- NAME: CR_TABLE_SOURCE; TYPE: TABLE
279 create table cr_table_source (
280 TABLE_NAME CHARACTER VARYING(30) NOT NULL,
281 DISPLAY_NAME CHARACTER VARYING(30) NOT NULL,
282 PK_FIELDS CHARACTER VARYING(200),
283 WEB_VIEW_ACTION CHARACTER VARYING(50),
284 LARGE_DATA_SOURCE_YN CHARACTER VARYING(1) DEFAULT 'N' NOT NULL,
285 FILTER_SQL CHARACTER VARYING(4000),
286 SOURCE_DB CHARACTER VARYING(50)
290 -- NAME: FN_LU_TIMEZONE; TYPE: TABLE
292 create table fn_lu_timezone (
293 TIMEZONE_ID INT(11) NOT NULL,
294 TIMEZONE_NAME CHARACTER VARYING(100) NOT NULL,
295 TIMEZONE_VALUE CHARACTER VARYING(100) NOT NULL
298 create table fn_user (
299 USER_ID INT(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
302 FIRST_NAME CHARACTER VARYING(50),
303 MIDDLE_NAME CHARACTER VARYING(50),
304 LAST_NAME CHARACTER VARYING(50),
305 PHONE CHARACTER VARYING(25),
306 FAX CHARACTER VARYING(25),
307 CELLULAR CHARACTER VARYING(25),
308 EMAIL CHARACTER VARYING(50),
309 ADDRESS_ID NUMERIC(11,0),
310 ALERT_METHOD_CD CHARACTER VARYING(10),
311 HRID CHARACTER VARYING(20),
312 ORG_USER_ID CHARACTER VARYING(20),
313 ORG_CODE CHARACTER VARYING(30),
314 LOGIN_ID CHARACTER VARYING(25),
315 LOGIN_PWD CHARACTER VARYING(25),
316 LAST_LOGIN_DATE TIMESTAMP,
317 ACTIVE_YN CHARACTER VARYING(1) DEFAULT 'Y' NOT NULL,
319 CREATED_DATE TIMESTAMP DEFAULT NOW(),
321 MODIFIED_DATE TIMESTAMP default now(),
322 IS_INTERNAL_YN CHARACTER(1) DEFAULT 'N' NOT NULL,
323 ADDRESS_LINE_1 CHARACTER VARYING(100),
324 ADDRESS_LINE_2 CHARACTER VARYING(100),
325 CITY CHARACTER VARYING(50),
326 STATE_CD CHARACTER VARYING(3),
327 ZIP_CODE CHARACTER VARYING(11),
328 COUNTRY_CD CHARACTER VARYING(3),
329 LOCATION_CLLI CHARACTER VARYING(8),
330 ORG_MANAGER_USERID CHARACTER VARYING(20),
331 COMPANY CHARACTER VARYING(100),
332 DEPARTMENT_NAME CHARACTER VARYING(100),
333 JOB_TITLE CHARACTER VARYING(100),
335 DEPARTMENT CHARACTER VARYING(25),
336 BUSINESS_UNIT CHARACTER VARYING(25),
337 BUSINESS_UNIT_NAME CHARACTER VARYING(100),
338 COST_CENTER CHARACTER VARYING(25),
339 FIN_LOC_CODE CHARACTER VARYING(10),
340 SILO_STATUS CHARACTER VARYING(10)
344 -- NAME: FN_ROLE; TYPE: TABLE
346 create table fn_role (
347 ROLE_ID INT(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
348 ROLE_NAME CHARACTER VARYING(50) NOT NULL,
349 ACTIVE_YN CHARACTER VARYING(1) DEFAULT 'Y' NOT NULL,
350 PRIORITY NUMERIC(4,0)
354 -- NAME: FN_AUDIT_ACTION; TYPE: TABLE
356 create table fn_audit_action (
357 AUDIT_ACTION_ID INTEGER NOT NULL,
358 CLASS_NAME CHARACTER VARYING(500) NOT NULL,
359 METHOD_NAME CHARACTER VARYING(50) NOT NULL,
360 AUDIT_ACTION_CD CHARACTER VARYING(20) NOT NULL,
361 AUDIT_ACTION_DESC CHARACTER VARYING(200),
362 ACTIVE_YN CHARACTER VARYING(1)
366 -- NAME: FN_AUDIT_ACTION_LOG; TYPE: TABLE
368 create table fn_audit_action_log (
369 AUDIT_LOG_ID INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
370 AUDIT_ACTION_CD CHARACTER VARYING(200),
371 ACTION_TIME TIMESTAMP,
372 USER_ID NUMERIC(11,0),
373 CLASS_NAME CHARACTER VARYING(100),
374 METHOD_NAME CHARACTER VARYING(50),
375 SUCCESS_MSG CHARACTER VARYING(20),
376 ERROR_MSG CHARACTER VARYING(500)
380 -- NAME: FN_LU_ACTIVITY; TYPE: TABLE
382 create table fn_lu_activity (
383 ACTIVITY_CD CHARACTER VARYING(50) NOT NULL PRIMARY KEY,
384 ACTIVITY CHARACTER VARYING(50) NOT NULL
388 -- NAME: FN_AUDIT_LOG; TYPE: TABLE
390 create table fn_audit_log (
391 LOG_ID INT(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
392 USER_ID INT(11) NOT NULL,
393 ACTIVITY_CD CHARACTER VARYING(50) NOT NULL,
394 AUDIT_DATE TIMESTAMP DEFAULT NOW() NOT NULL,
395 COMMENTS CHARACTER VARYING(1000),
396 AFFECTED_RECORD_ID_BK CHARACTER VARYING(500),
397 AFFECTED_RECORD_ID CHARACTER VARYING(4000),
398 CONSTRAINT FK_FN_AUDIT_REF_209_FN_USER FOREIGN KEY (USER_ID) REFERENCES FN_USER(USER_ID)
402 -- NAME: FN_BROADCAST_MESSAGE; TYPE: TABLE
404 create table fn_broadcast_message (
405 MESSAGE_ID INT(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
406 MESSAGE_TEXT CHARACTER VARYING(1000) NOT NULL,
407 MESSAGE_LOCATION_ID NUMERIC(11,0) NOT NULL,
408 BROADCAST_START_DATE TIMESTAMP NOT NULL DEFAULT NOW(),
409 BROADCAST_END_DATE TIMESTAMP NOT NULL DEFAULT NOW(),
410 ACTIVE_YN CHARACTER(1) DEFAULT 'Y' NOT NULL,
411 SORT_ORDER NUMERIC(4,0) NOT NULL,
412 BROADCAST_SITE_CD CHARACTER VARYING(50)
416 -- NAME: FN_CHAT_LOGS; TYPE: TABLE
418 create table fn_chat_logs (
419 CHAT_LOG_ID INTEGER NOT NULL,
420 CHAT_ROOM_ID INTEGER,
422 MESSAGE CHARACTER VARYING(1000),
423 MESSAGE_DATE_TIME TIMESTAMP
427 -- NAME: FN_CHAT_ROOM; TYPE: TABLE
429 create table fn_chat_room (
430 CHAT_ROOM_ID INTEGER NOT NULL,
431 NAME CHARACTER VARYING(50) NOT NULL,
432 DESCRIPTION CHARACTER VARYING(500),
434 CREATED_DATE TIMESTAMP DEFAULT NOW(),
435 UPDATED_DATE TIMESTAMP DEFAULT NOW()
439 -- NAME: FN_CHAT_USERS; TYPE: TABLE
441 create table fn_chat_users (
442 CHAT_ROOM_ID INTEGER,
444 LAST_ACTIVITY_DATE_TIME TIMESTAMP,
445 CHAT_STATUS CHARACTER VARYING(20),
450 -- NAME: FN_DATASOURCE; TYPE: TABLE
452 create table fn_datasource (
453 ID INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
454 NAME CHARACTER VARYING(50),
455 DRIVER_NAME CHARACTER VARYING(256),
456 SERVER CHARACTER VARYING(256),
458 USER_NAME CHARACTER VARYING(256),
459 PASSWORD CHARACTER VARYING(256),
460 URL CHARACTER VARYING(256),
461 MIN_POOL_SIZE INTEGER,
462 MAX_POOL_SIZE INTEGER,
464 DS_TYPE CHARACTER VARYING(20)
468 -- NAME: FN_FUNCTION; TYPE: TABLE
470 create table fn_function (
471 FUNCTION_CD CHARACTER VARYING(30) NOT NULL PRIMARY KEY,
472 FUNCTION_NAME CHARACTER VARYING(50) NOT NULL
476 -- NAME: FN_LU_ALERT_METHOD; TYPE: TABLE
478 create table fn_lu_alert_method (
479 ALERT_METHOD_CD CHARACTER VARYING(10) NOT NULL,
480 ALERT_METHOD CHARACTER VARYING(50) NOT NULL
484 -- NAME: FN_LU_BROADCAST_SITE; TYPE: TABLE
486 create table fn_lu_broadcast_site (
487 BROADCAST_SITE_CD CHARACTER VARYING(50) NOT NULL,
488 BROADCAST_SITE_DESCR CHARACTER VARYING(100)
491 -- NAME: FN_LU_MENU_SET; TYPE: TABLE
493 create table fn_lu_menu_set (
494 MENU_SET_CD CHARACTER VARYING(10) NOT NULL PRIMARY KEY,
495 MENU_SET_NAME CHARACTER VARYING(50) NOT NULL
499 -- NAME: FN_LU_PRIORITY; TYPE: TABLE
501 create table fn_lu_priority (
502 PRIORITY_ID NUMERIC(11,0) NOT NULL,
503 PRIORITY CHARACTER VARYING(50) NOT NULL,
504 ACTIVE_YN CHARACTER(1) NOT NULL,
505 SORT_ORDER NUMERIC(5,0)
509 -- NAME: FN_LU_ROLE_TYPE; TYPE: TABLE
511 create table fn_lu_role_type (
512 ROLE_TYPE_ID NUMERIC(11,0) NOT NULL,
513 ROLE_TYPE CHARACTER VARYING(50) NOT NULL
516 -- NAME: FN_LU_TAB_SET; TYPE: TABLE
518 create table fn_lu_tab_set (
519 TAB_SET_CD CHARACTER VARYING(30) NOT NULL,
520 TAB_SET_NAME CHARACTER VARYING(50) NOT NULL
524 -- NAME: FN_MENU; TYPE: TABLE
526 create table fn_menu (
527 MENU_ID INT(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
528 LABEL CHARACTER VARYING(100),
530 SORT_ORDER NUMERIC(4,0),
531 ACTION CHARACTER VARYING(200),
532 FUNCTION_CD CHARACTER VARYING(30),
533 ACTIVE_YN CHARACTER VARYING(1) DEFAULT 'Y' NOT NULL,
534 SERVLET CHARACTER VARYING(50),
535 QUERY_STRING CHARACTER VARYING(200),
536 EXTERNAL_URL CHARACTER VARYING(200),
537 TARGET CHARACTER VARYING(25),
538 MENU_SET_CD CHARACTER VARYING(10) DEFAULT 'APP',
539 SEPARATOR_YN CHARACTER(1) DEFAULT 'N',
540 IMAGE_SRC CHARACTER VARYING(100),
541 CONSTRAINT FK_FN_MENU_REF_196_FN_MENU FOREIGN KEY (PARENT_ID) REFERENCES FN_MENU(MENU_ID),
542 CONSTRAINT FK_FN_MENU_MENU_SET_CD FOREIGN KEY (MENU_SET_CD) REFERENCES FN_LU_MENU_SET(MENU_SET_CD),
543 CONSTRAINT FK_FN_MENU_REF_223_FN_FUNCT FOREIGN KEY (FUNCTION_CD) REFERENCES FN_FUNCTION(FUNCTION_CD)
547 -- NAME: FN_ORG; TYPE: TABLE
549 create table fn_org (
550 ORG_ID INT(11) NOT NULL,
551 ORG_NAME CHARACTER VARYING(50) NOT NULL,
552 ACCESS_CD CHARACTER VARYING(10)
556 -- NAME: FN_RESTRICTED_URL; TYPE: TABLE
558 create table fn_restricted_url (
559 RESTRICTED_URL CHARACTER VARYING(250) NOT NULL,
560 FUNCTION_CD CHARACTER VARYING(30) NOT NULL
564 -- NAME: FN_ROLE_COMPOSITE; TYPE: TABLE
566 create table fn_role_composite (
567 PARENT_ROLE_ID INT(11) NOT NULL,
568 CHILD_ROLE_ID INT(11) NOT NULL,
569 CONSTRAINT FK_FN_ROLE_COMPOSITE_CHILD FOREIGN KEY (CHILD_ROLE_ID) REFERENCES FN_ROLE(ROLE_ID),
570 CONSTRAINT FK_FN_ROLE_COMPOSITE_PARENT FOREIGN KEY (PARENT_ROLE_ID) REFERENCES FN_ROLE(ROLE_ID)
574 -- NAME: FN_ROLE_FUNCTION; TYPE: TABLE
576 create table fn_role_function (
577 ROLE_ID INT(11) NOT NULL,
578 FUNCTION_CD CHARACTER VARYING(30) NOT NULL,
579 CONSTRAINT FK_FN_ROLE__REF_198_FN_ROLE FOREIGN KEY (ROLE_ID) REFERENCES FN_ROLE(ROLE_ID)
583 -- NAME: FN_TAB; TYPE: TABLE
585 create table fn_tab (
586 TAB_CD CHARACTER VARYING(30) NOT NULL,
587 TAB_NAME CHARACTER VARYING(50) NOT NULL,
588 TAB_DESCR CHARACTER VARYING(100),
589 ACTION CHARACTER VARYING(100) NOT NULL,
590 FUNCTION_CD CHARACTER VARYING(30) NOT NULL,
591 ACTIVE_YN CHARACTER(1) NOT NULL,
592 SORT_ORDER NUMERIC(11,0) NOT NULL,
593 PARENT_TAB_CD CHARACTER VARYING(30),
594 TAB_SET_CD CHARACTER VARYING(30)
598 -- NAME: FN_TAB_SELECTED; TYPE: TABLE
600 create table fn_tab_selected (
601 SELECTED_TAB_CD CHARACTER VARYING(30) NOT NULL,
602 TAB_URI CHARACTER VARYING(40) NOT NULL
606 -- NAME: FN_USER_PSEUDO_ROLE; TYPE: TABLE
608 create table fn_user_pseudo_role (
609 PSEUDO_ROLE_ID INT(11) NOT NULL,
610 USER_ID INT(11) NOT NULL
614 -- NAME: FN_USER_ROLE; TYPE: TABLE
616 create table fn_user_role (
617 USER_ID INT(10) NOT NULL,
618 ROLE_ID INT(10) NOT NULL,
619 PRIORITY NUMERIC(4,0),
620 APP_ID INT(11) DEFAULT 1,
621 CONSTRAINT FK_FN_USER__REF_172_FN_USER FOREIGN KEY (USER_ID) REFERENCES FN_USER(USER_ID),
622 CONSTRAINT FK_FN_USER__REF_175_FN_ROLE FOREIGN KEY (ROLE_ID) REFERENCES FN_ROLE(ROLE_ID)
625 -- NAME: SCHEMA_INFO; TYPE: TABLE
627 create table schema_info (
628 SCHEMA_ID CHARACTER VARYING(25) NOT NULL,
629 SCHEMA_DESC CHARACTER VARYING(75) NOT NULL,
630 DATASOURCE_TYPE CHARACTER VARYING(100),
631 CONNECTION_URL VARCHAR(200) NOT NULL,
632 USER_NAME VARCHAR(45) NOT NULL,
633 PASSWORD VARCHAR(45) NULL DEFAULT NULL,
634 DRIVER_CLASS VARCHAR(100) NOT NULL,
635 MIN_POOL_SIZE INT NOT NULL,
636 MAX_POOL_SIZE INT NOT NULL,
637 IDLE_CONNECTION_TEST_PERIOD INT NOT NULL
641 -- ----------------------------------------------------------
642 -- NAME: FN_APP; TYPE: TABLE
643 -- ----------------------------------------------------------
644 create table fn_app (
645 APP_ID int(11) PRIMARY KEY NOT NULL AUTO_INCREMENT,
646 APP_NAME varchar(100) NOT NULL DEFAULT '?',
647 APP_IMAGE_URL varchar(256) DEFAULT NULL,
648 APP_DESCRIPTION varchar(512) DEFAULT NULL,
649 APP_NOTES varchar(4096) DEFAULT NULL,
650 APP_URL varchar(256) DEFAULT NULL,
651 APP_ALTERNATE_URL varchar(256) DEFAULT NULL,
652 APP_REST_ENDPOINT varchar(2000) DEFAULT NULL,
653 ML_APP_NAME varchar(50) NOT NULL DEFAULT '?',
654 ML_APP_ADMIN_ID varchar(7) NOT NULL DEFAULT '?',
655 MOTS_ID int(11) DEFAULT NULL,
656 APP_PASSWORD varchar(256) NOT NULL DEFAULT '?',
657 OPEN char(1) DEFAULT 'N',
658 ENABLED char(1) DEFAULT 'Y',
659 THUMBNAIL mediumblob,
660 APP_USERNAME varchar(50),
661 UEB_KEY VARCHAR(256) DEFAULT NULL,
662 UEB_SECRET VARCHAR(256) DEFAULT NULL,
663 UEB_TOPIC_NAME VARCHAR(256) DEFAULT NULL
667 -- ----------------------------------------------------------
668 -- NAME: FN_FN_WORKFLOW; TYPE: TABLE
669 -- ----------------------------------------------------------
670 create table fn_workflow (
671 id mediumint(9) NOT NULL AUTO_INCREMENT,
672 name varchar(20) NOT NULL,
673 description varchar(500) DEFAULT NULL,
674 run_link varchar(300) DEFAULT NULL,
675 suspend_link varchar(300) DEFAULT NULL,
676 modified_link varchar(300) DEFAULT NULL,
677 active_yn varchar(300) DEFAULT NULL,
678 created varchar(300) DEFAULT NULL,
679 created_by int(11) DEFAULT NULL,
680 modified varchar(300) DEFAULT NULL,
681 modified_by int(11) DEFAULT NULL,
682 workflow_key varchar(50) DEFAULT NULL,
684 UNIQUE KEY name (name)
687 -- ----------------------------------------------------------
688 -- NAME: FN_SCHEDULE_WORKFLOWS; TYPE: TABLE
689 -- ----------------------------------------------------------
690 create table fn_schedule_workflows (
691 id_schedule_workflows bigint(25) PRIMARY KEY NOT NULL AUTO_INCREMENT,
692 workflow_server_url varchar(45) DEFAULT NULL,
693 workflow_key varchar(45) NOT NULL,
694 workflow_arguments varchar(45) DEFAULT NULL,
695 startDateTimeCron varchar(45) DEFAULT NULL,
696 endDateTime TIMESTAMP DEFAULT NOW(),
697 start_date_time TIMESTAMP DEFAULT NOW(),
698 recurrence varchar(45) DEFAULT NULL
701 -- For demo reporting application add : demo_bar_chart, demo_bar_chart_inter, demo_line_chart, demo_pie_chart and demo_util_chart
702 -- demo_scatter_chart, demo_scatter_plot
703 -- ----------------------------------------------------------
704 -- NAME: DEMO_BAR_CHART; TYPE: TABLE
705 -- ----------------------------------------------------------
706 create table demo_bar_chart (
711 -- ----------------------------------------------------------
712 -- NAME: DEMO_BAR_CHART_INTER; TYPE: TABLE
713 -- ----------------------------------------------------------
714 create table demo_bar_chart_inter (
716 num_rpt_sources numeric(10,0),
717 num_det_sources numeric(10,0)
720 -- ----------------------------------------------------------
721 -- NAME: DEMO_LINE_CHART; TYPE: TABLE
722 -- ----------------------------------------------------------
723 create table demo_line_chart (
726 data_value numeric(10,5)
729 -- ----------------------------------------------------------
730 -- NAME: DEMO_PIE_CHART; TYPE: TABLE
731 -- ----------------------------------------------------------
732 create table demo_pie_chart (
734 data_value numeric(10,5)
737 -- ----------------------------------------------------------
738 -- NAME: DEMO_UTIL_CHART; TYPE: TABLE
739 -- ----------------------------------------------------------
740 create table demo_util_chart (
742 util_perc numeric(10,5)
745 -- ----------------------------------------------------------
746 -- NAME: DEMO_SCATTER_CHART; TYPE: TABLE
747 -- ----------------------------------------------------------
748 create table demo_scatter_chart (
749 rainfall numeric(10,2),
750 key_value varchar(20),
751 measurements numeric(10,2)
754 -- ----------------------------------------------------------
755 -- NAME: DEMO_SCATTER_PLOT; TYPE: TABLE
756 -- ----------------------------------------------------------
757 create table demo_scatter_plot
760 VALUEX numeric(25,15),
761 VALUEY numeric(25,15)
764 -- ----------------------------------------------------------
765 -- NAME: FN_QZ_JOB_DETAILS; TYPE: TABLE
766 -- ----------------------------------------------------------
767 create table fn_qz_job_details (
768 SCHED_NAME VARCHAR(120) NOT NULL,
769 JOB_NAME VARCHAR(200) NOT NULL,
770 JOB_GROUP VARCHAR(200) NOT NULL,
771 DESCRIPTION VARCHAR(250) NULL,
772 JOB_CLASS_NAME VARCHAR(250) NOT NULL,
773 IS_DURABLE VARCHAR(1) NOT NULL,
774 IS_NONCONCURRENT VARCHAR(1) NOT NULL,
775 IS_UPDATE_DATA VARCHAR(1) NOT NULL,
776 REQUESTS_RECOVERY VARCHAR(1) NOT NULL,
778 PRIMARY KEY (SCHED_NAME,JOB_NAME,JOB_GROUP)
781 -- ----------------------------------------------------------
782 -- NAME: FN_QZ_TRIGGERS; TYPE: TABLE
783 -- ----------------------------------------------------------
784 create table fn_qz_triggers (
785 SCHED_NAME VARCHAR(120) NOT NULL,
786 TRIGGER_NAME VARCHAR(200) NOT NULL,
787 TRIGGER_GROUP VARCHAR(200) NOT NULL,
788 JOB_NAME VARCHAR(200) NOT NULL,
789 JOB_GROUP VARCHAR(200) NOT NULL,
790 DESCRIPTION VARCHAR(250) NULL,
791 NEXT_FIRE_TIME BIGINT(13) NULL,
792 PREV_FIRE_TIME BIGINT(13) NULL,
793 PRIORITY INTEGER NULL,
794 TRIGGER_STATE VARCHAR(16) NOT NULL,
795 TRIGGER_TYPE VARCHAR(8) NOT NULL,
796 START_TIME BIGINT(13) NOT NULL,
797 END_TIME BIGINT(13) NULL,
798 CALENDAR_NAME VARCHAR(200) NULL,
799 MISFIRE_INSTR SMALLINT(2) NULL,
801 PRIMARY KEY (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP),
802 FOREIGN KEY (SCHED_NAME,JOB_NAME,JOB_GROUP)
803 REFERENCES FN_QZ_JOB_DETAILS(SCHED_NAME,JOB_NAME,JOB_GROUP)
806 -- ----------------------------------------------------------
807 -- NAME: FN_QZ_SIMPLE_TRIGGERS; TYPE: TABLE
808 -- ----------------------------------------------------------
809 create table fn_qz_simple_triggers (
810 SCHED_NAME VARCHAR(120) NOT NULL,
811 TRIGGER_NAME VARCHAR(200) NOT NULL,
812 TRIGGER_GROUP VARCHAR(200) NOT NULL,
813 REPEAT_COUNT BIGINT(7) NOT NULL,
814 REPEAT_INTERVAL BIGINT(12) NOT NULL,
815 TIMES_TRIGGERED BIGINT(10) NOT NULL,
816 PRIMARY KEY (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP),
817 FOREIGN KEY (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP)
818 REFERENCES FN_QZ_TRIGGERS(SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP)
821 -- ----------------------------------------------------------
822 -- NAME: FN_QZ_CRON_TRIGGERS; TYPE: TABLE
823 -- ----------------------------------------------------------
824 create table fn_qz_cron_triggers (
825 SCHED_NAME VARCHAR(120) NOT NULL,
826 TRIGGER_NAME VARCHAR(200) NOT NULL,
827 TRIGGER_GROUP VARCHAR(200) NOT NULL,
828 CRON_EXPRESSION VARCHAR(120) NOT NULL,
829 TIME_ZONE_ID VARCHAR(80),
830 PRIMARY KEY (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP),
831 FOREIGN KEY (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP)
832 REFERENCES FN_QZ_TRIGGERS(SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP)
835 -- ----------------------------------------------------------
836 -- NAME: FN_QZ_SIMPROP_TRIGGERS; TYPE: TABLE
837 -- ----------------------------------------------------------
838 create table fn_qz_simprop_triggers
840 SCHED_NAME VARCHAR(120) NOT NULL,
841 TRIGGER_NAME VARCHAR(200) NOT NULL,
842 TRIGGER_GROUP VARCHAR(200) NOT NULL,
843 STR_PROP_1 VARCHAR(512) NULL,
844 STR_PROP_2 VARCHAR(512) NULL,
845 STR_PROP_3 VARCHAR(512) NULL,
848 LONG_PROP_1 BIGINT NULL,
849 LONG_PROP_2 BIGINT NULL,
850 DEC_PROP_1 NUMERIC(13,4) NULL,
851 DEC_PROP_2 NUMERIC(13,4) NULL,
852 BOOL_PROP_1 VARCHAR(1) NULL,
853 BOOL_PROP_2 VARCHAR(1) NULL,
854 PRIMARY KEY (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP),
855 FOREIGN KEY (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP)
856 REFERENCES FN_QZ_TRIGGERS(SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP)
859 -- ----------------------------------------------------------
860 -- NAME: FN_QZ_BLOB_TRIGGERS; TYPE: TABLE
861 -- ----------------------------------------------------------
862 create table fn_qz_blob_triggers (
863 SCHED_NAME VARCHAR(120) NOT NULL,
864 TRIGGER_NAME VARCHAR(200) NOT NULL,
865 TRIGGER_GROUP VARCHAR(200) NOT NULL,
867 PRIMARY KEY (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP),
868 INDEX (SCHED_NAME,TRIGGER_NAME, TRIGGER_GROUP),
869 FOREIGN KEY (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP)
870 REFERENCES FN_QZ_TRIGGERS(SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP)
873 -- ----------------------------------------------------------
874 -- NAME: FN_QZ_CALENDARS; TYPE: TABLE
875 -- ----------------------------------------------------------
876 create table fn_qz_calendars (
877 SCHED_NAME VARCHAR(120) NOT NULL,
878 CALENDAR_NAME VARCHAR(200) NOT NULL,
879 CALENDAR BLOB NOT NULL,
880 PRIMARY KEY (SCHED_NAME,CALENDAR_NAME)
883 -- ----------------------------------------------------------
884 -- NAME: FN_QZ_PAUSED_TRIGGER_GRPS; TYPE: TABLE
885 -- ----------------------------------------------------------
886 create table fn_qz_paused_trigger_grps (
887 SCHED_NAME VARCHAR(120) NOT NULL,
888 TRIGGER_GROUP VARCHAR(200) NOT NULL,
889 PRIMARY KEY (SCHED_NAME,TRIGGER_GROUP)
892 -- ----------------------------------------------------------
893 -- NAME: FN_QZ_FIRED_TRIGGERS; TYPE: TABLE
894 -- ----------------------------------------------------------
895 create table fn_qz_fired_triggers (
896 SCHED_NAME VARCHAR(120) NOT NULL,
897 ENTRY_ID VARCHAR(95) NOT NULL,
898 TRIGGER_NAME VARCHAR(200) NOT NULL,
899 TRIGGER_GROUP VARCHAR(200) NOT NULL,
900 INSTANCE_NAME VARCHAR(200) NOT NULL,
901 FIRED_TIME BIGINT(13) NOT NULL,
902 SCHED_TIME BIGINT(13) NOT NULL,
903 PRIORITY INTEGER NOT NULL,
904 STATE VARCHAR(16) NOT NULL,
905 JOB_NAME VARCHAR(200) NULL,
906 JOB_GROUP VARCHAR(200) NULL,
907 IS_NONCONCURRENT VARCHAR(1) NULL,
908 REQUESTS_RECOVERY VARCHAR(1) NULL,
909 PRIMARY KEY (SCHED_NAME,ENTRY_ID)
912 -- ----------------------------------------------------------
913 -- NAME: FN_QZ_SCHEDULER_STATE; TYPE: TABLE
914 -- ----------------------------------------------------------
915 create table fn_qz_scheduler_state (
916 SCHED_NAME VARCHAR(120) NOT NULL,
917 INSTANCE_NAME VARCHAR(200) NOT NULL,
918 LAST_CHECKIN_TIME BIGINT(13) NOT NULL,
919 CHECKIN_INTERVAL BIGINT(13) NOT NULL,
920 PRIMARY KEY (SCHED_NAME,INSTANCE_NAME)
923 -- ----------------------------------------------------------
924 -- NAME: FN_QZ_LOCKS; TYPE: TABLE
925 -- ----------------------------------------------------------
926 create table fn_qz_locks (
927 SCHED_NAME VARCHAR(120) NOT NULL,
928 LOCK_NAME VARCHAR(40) NOT NULL,
929 PRIMARY KEY (SCHED_NAME,LOCK_NAME)
933 -- name: rcloudinvocation; type: table
935 create table rcloudinvocation (
936 id varchar(128) not null primary key,
937 created timestamp not null,
938 userinfo varchar(2048) not null,
939 notebookid varchar(128) not null,
940 parameters varchar(2048) default null,
941 tokenreaddate timestamp null
945 -- name: rcloudnotebook; type: table
947 create table rcloudnotebook (
948 notebookname varchar(128) not null primary key,
949 notebookid varchar(128) not null
953 -- Name: fn_lu_message_location; Type: TABLE
956 CREATE TABLE fn_lu_message_location (
957 message_location_id numeric(11,0) NOT NULL,
958 message_location_descr character varying(30) NOT NULL
961 -- ------------------ CREATE VIEW SECTION
963 -- NAME: V_URL_ACCESS; TYPE: VIEW
965 CREATE VIEW v_url_access AS
966 SELECT DISTINCT M.ACTION AS URL,
969 WHERE (M.ACTION IS NOT NULL)
971 SELECT DISTINCT T.ACTION AS URL,
974 WHERE (T.ACTION IS NOT NULL)
976 SELECT R.RESTRICTED_URL AS URL,
978 FROM FN_RESTRICTED_URL R;
980 -- ------------------ ALTER TABLE ADD CONSTRAINT PRIMARY KEY SECTION
982 -- NAME: CR_FAVORITE_REPORTS_USER_IDREP_ID; TYPE: CONSTRAINT
984 alter table cr_favorite_reports
985 add constraint cr_favorite_reports_user_idrep_id primary key (user_id, rep_id);
987 -- NAME: CR_FOLDER_FOLDER_ID; TYPE: CONSTRAINT
989 alter table cr_folder
990 add constraint cr_folder_folder_id primary key (folder_id);
992 -- NAME: CR_FOLDER_ACCESS_FOLDER_ACCESS_ID; TYPE: CONSTRAINT
994 alter table cr_folder_access
995 add constraint cr_folder_access_folder_access_id primary key (folder_access_id);
997 -- NAME: CR_HIST_USER_MAP_HIST_IDUSER_ID; TYPE: CONSTRAINT
999 alter table cr_hist_user_map
1000 add constraint cr_hist_user_map_hist_iduser_id primary key (hist_id, user_id);
1002 -- NAME: CR_LU_FILE_TYPE_LOOKUP_ID; TYPE: CONSTRAINT
1004 alter table cr_lu_file_type
1005 add constraint cr_lu_file_type_lookup_id primary key (lookup_id);
1007 -- NAME: CR_RAPTOR_ACTION_IMG_IMAGE_ID; TYPE: CONSTRAINT
1009 alter table cr_raptor_action_img
1010 add constraint cr_raptor_action_img_image_id primary key (image_id);
1012 -- NAME: CR_RAPTOR_PDF_IMG_IMAGE_ID; TYPE: CONSTRAINT
1014 alter table cr_raptor_pdf_img
1015 add constraint cr_raptor_pdf_img_image_id primary key (image_id);
1017 -- NAME: CR_REMOTE_SCHEMA_INFO_SCHEMA_PREFIX; TYPE: CONSTRAINT
1019 alter table cr_remote_schema_info
1020 add constraint cr_remote_schema_info_schema_prefix primary key (schema_prefix);
1022 -- NAME: CR_REPORT_REP_ID; TYPE: CONSTRAINT
1024 alter table cr_report
1025 add constraint cr_report_rep_id primary key (rep_id);
1027 -- NAME: CR_REPORT_ACCESS_REP_IDORDER_NO; TYPE: CONSTRAINT
1029 alter table cr_report_access
1030 add constraint cr_report_access_rep_idorder_no primary key (rep_id, order_no);
1032 -- NAME: CR_REPORT_EMAIL_SENT_LOG_LOG_ID; TYPE: CONSTRAINT
1034 alter table cr_report_email_sent_log
1035 add constraint cr_report_email_sent_log_log_id primary key (log_id);
1037 -- NAME: CR_REPORT_FILE_HISTORY_HIST_ID; TYPE: CONSTRAINT
1039 alter table cr_report_file_history
1040 add constraint cr_report_file_history_hist_id primary key (hist_id);
1042 -- NAME: CR_REPORT_SCHEDULE_SCHEDULE_ID; TYPE: CONSTRAINT
1044 alter table cr_report_schedule
1045 add constraint cr_report_schedule_schedule_id primary key (schedule_id);
1047 -- NAME: CR_REPORT_SCHEDULE_USERS_SCHEDULE_IDREP_IDUSER_IDORDER_NO; TYPE: CONSTRAINT
1049 alter table cr_report_schedule_users
1050 add constraint cr_report_schedule_users_schedule_idrep_iduser_idorder_no primary key (schedule_id, rep_id, user_id, order_no);
1052 -- NAME: CR_REPORT_TEMPLATE_MAP_REPORT_ID; TYPE: CONSTRAINT
1054 alter table cr_report_template_map
1055 add constraint cr_report_template_map_report_id primary key (report_id);
1057 -- NAME: CR_TABLE_ROLE_TABLE_NAMEROLE_ID; TYPE: CONSTRAINT
1059 alter table cr_table_role
1060 add constraint cr_table_role_table_namerole_id primary key (table_name, role_id);
1062 -- NAME: CR_TABLE_SOURCE_TABLE_NAME; TYPE: CONSTRAINT
1064 alter table cr_table_source
1065 add constraint cr_table_source_table_name primary key (table_name);
1067 -- NAME: FN_AUDIT_ACTION_AUDIT_ACTION_ID; TYPE: CONSTRAINT
1069 alter table fn_audit_action
1070 add constraint fn_audit_action_audit_action_id primary key (audit_action_id);
1072 -- NAME: FN_CHAT_LOGS_CHAT_LOG_ID; TYPE: CONSTRAINT
1074 alter table fn_chat_logs
1075 add constraint fn_chat_logs_chat_log_id primary key (chat_log_id);
1077 -- NAME: FN_CHAT_ROOM_CHAT_ROOM_ID; TYPE: CONSTRAINT
1079 alter table fn_chat_room
1080 add constraint fn_chat_room_chat_room_id primary key (chat_room_id);
1082 -- NAME: FN_CHAT_USERS_ID; TYPE: CONSTRAINT
1084 alter table fn_chat_users
1085 add constraint fn_chat_users_id primary key (id);
1087 -- NAME: FN_LU_ALERT_METHOD_ALERT_METHOD_CD; TYPE: CONSTRAINT
1089 alter table fn_lu_alert_method
1090 add constraint fn_lu_alert_method_alert_method_cd primary key (alert_method_cd);
1092 -- NAME: FN_LU_BROADCAST_SITE_BROADCAST_SITE_CD; TYPE: CONSTRAINT
1094 alter table fn_lu_broadcast_site
1095 add constraint fn_lu_broadcast_site_broadcast_site_cd primary key (broadcast_site_cd);
1097 -- NAME: FN_LU_PRIORITY_PRIORITY_ID; TYPE: CONSTRAINT
1099 alter table fn_lu_priority
1100 add constraint fn_lu_priority_priority_id primary key (priority_id);
1102 -- NAME: FN_LU_ROLE_TYPE_ROLE_TYPE_ID; TYPE: CONSTRAINT
1104 alter table fn_lu_role_type
1105 add constraint fn_lu_role_type_role_type_id primary key (role_type_id);
1107 -- NAME: FN_LU_TAB_SET_TAB_SET_CD; TYPE: CONSTRAINT
1109 alter table fn_lu_tab_set
1110 add constraint fn_lu_tab_set_tab_set_cd primary key (tab_set_cd);
1112 -- NAME: FN_LU_TIMEZONE_TIMEZONE_ID; TYPE: CONSTRAINT
1114 alter table fn_lu_timezone
1115 add constraint fn_lu_timezone_timezone_id primary key (timezone_id);
1117 -- NAME: FN_ORG_ORG_ID; TYPE: CONSTRAINT
1120 add constraint fn_org_org_id primary key (org_id);
1122 -- NAME: FN_RESTRICTED_URL_RESTRICTED_URLFUNCTION_CD; TYPE: CONSTRAINT
1124 alter table fn_restricted_url
1125 add constraint fn_restricted_url_restricted_urlfunction_cd primary key (restricted_url, function_cd);
1127 -- NAME: FN_ROLE_COMPOSITE_PARENT_ROLE_IDCHILD_ROLE_ID; TYPE: CONSTRAINT
1129 alter table fn_role_composite
1130 add constraint fn_role_composite_parent_role_idchild_role_id primary key (parent_role_id, child_role_id);
1132 -- NAME: FN_ROLE_FUNCTION_ROLE_IDFUNCTION_CD; TYPE: CONSTRAINT
1134 alter table fn_role_function
1135 add constraint fn_role_function_role_idfunction_cd primary key (role_id, function_cd);
1137 -- NAME: FN_TAB_TAB_CD; TYPE: CONSTRAINT
1140 add constraint fn_tab_tab_cd primary key (tab_cd);
1142 -- NAME: FN_TAB_SELECTED_SELECTED_TAB_CDTAB_URI; TYPE: CONSTRAINT
1144 alter table fn_tab_selected
1145 add constraint fn_tab_selected_selected_tab_cdtab_uri primary key (selected_tab_cd, tab_uri);
1147 -- NAME: FN_USER_PSEUDO_ROLE_PSEUDO_ROLE_IDUSER_ID; TYPE: CONSTRAINT
1149 alter table fn_user_pseudo_role
1150 add constraint fn_user_pseudo_role_pseudo_role_iduser_id primary key (pseudo_role_id, user_id);
1152 -- NAME: FN_USER_ROLE_USER_IDROLE_ID; TYPE: CONSTRAINT
1154 alter table fn_user_role
1155 add constraint fn_user_role_user_idrole_id primary key (user_id, role_id, app_id);
1157 -- Name: fn_lu_message_location_MESSAGE_LOCATION_ID; Type: CONSTRAINT
1160 ALTER TABLE fn_lu_message_location
1161 ADD CONSTRAINT fn_lu_message_location_MESSAGE_LOCATION_ID PRIMARY KEY (message_location_id);
1163 -- ------------------ CREATE INDEX SECTION
1165 -- NAME: CR_REPORT_CREATE_IDPUBLIC_YNTITLE; TYPE: INDEX
1167 create index cr_report_create_idpublic_yntitle using btree on cr_report (create_id, public_yn, title);
1169 -- NAME: CR_TABLE_JOIN_DEST_TABLE_NAME; TYPE: INDEX
1171 create index cr_table_join_dest_table_name using btree on cr_table_join (dest_table_name);
1173 -- NAME: CR_TABLE_JOIN_SRC_TABLE_NAME; TYPE: INDEX
1175 create index cr_table_join_src_table_name using btree on cr_table_join (src_table_name);
1177 -- NAME: FN_AUDIT_LOG_ACTIVITY_CD; TYPE: INDEX
1179 create index fn_audit_log_activity_cd using btree on fn_audit_log (activity_cd);
1181 -- NAME: FN_AUDIT_LOG_USER_ID; TYPE: INDEX
1183 create index fn_audit_log_user_id using btree on fn_audit_log (user_id);
1185 -- NAME: FN_MENU_FUNCTION_CD; TYPE: INDEX
1187 create index fn_menu_function_cd using btree on fn_menu (function_cd);
1189 -- NAME: FN_ORG_ACCESS_CD; TYPE: INDEX
1191 create index fn_org_access_cd using btree on fn_org (access_cd);
1193 -- NAME: FN_ROLE_FUNCTION_FUNCTION_CD; TYPE: INDEX
1195 create index fn_role_function_function_cd using btree on fn_role_function (function_cd);
1197 -- NAME: FN_ROLE_FUNCTION_ROLE_ID; TYPE: INDEX
1199 create index fn_role_function_role_id using btree on fn_role_function (role_id);
1201 -- NAME: FN_USER_ADDRESS_ID; TYPE: INDEX
1203 create index fn_user_address_id using btree on fn_user (address_id);
1205 -- NAME: FN_USER_ALERT_METHOD_CD; TYPE: INDEX
1207 create index fn_user_alert_method_cd using btree on fn_user (alert_method_cd);
1209 -- NAME: FN_USER_HRID; TYPE: INDEX
1211 create unique index fn_user_hrid using btree on fn_user (hrid);
1213 -- NAME: FN_USER_LOGIN_ID; TYPE: INDEX
1215 create unique index fn_user_login_id using btree on fn_user (login_id);
1217 -- NAME: FN_USER_ORG_ID; TYPE: INDEX
1219 create index fn_user_org_id using btree on fn_user (org_id);
1221 -- NAME: FN_USER_ROLE_ROLE_ID; TYPE: INDEX
1223 create index fn_user_role_role_id using btree on fn_user_role (role_id);
1225 -- NAME: FN_USER_ROLE_USER_ID; TYPE: INDEX
1227 create index fn_user_role_user_id using btree on fn_user_role (user_id);
1229 -- NAME: FK_FN_USER__REF_178_FN_APP_idx; TYPE: INDEX
1231 create index fk_fn_user__ref_178_fn_app_IDX on fn_user_role (app_id);
1233 -- ----------------------------------------------------------
1234 -- NAME: QUARTZ TYPE: INDEXES
1235 -- ----------------------------------------------------------
1236 create index idx_fn_qz_j_req_recovery on fn_qz_job_details(sched_name,requests_recovery);
1237 create index idx_fn_qz_j_grp on fn_qz_job_details(sched_name,job_group);
1238 create index idx_fn_qz_t_j on fn_qz_triggers(sched_name,job_name,job_group);
1239 create index idx_fn_qz_t_jg on fn_qz_triggers(sched_name,job_group);
1240 create index idx_fn_qz_t_c on fn_qz_triggers(sched_name,calendar_name);
1241 create index idx_fn_qz_t_g on fn_qz_triggers(sched_name,trigger_group);
1242 create index idx_fn_qz_t_state on fn_qz_triggers(sched_name,trigger_state);
1243 create index idx_fn_qz_t_n_state on fn_qz_triggers(sched_name,trigger_name,trigger_group,trigger_state);
1244 create index idx_fn_qz_t_n_g_state on fn_qz_triggers(sched_name,trigger_group,trigger_state);
1245 create index idx_fn_qz_t_next_fire_time on fn_qz_triggers(sched_name,next_fire_time);
1246 create index idx_fn_qz_t_nft_st on fn_qz_triggers(sched_name,trigger_state,next_fire_time);
1247 create index idx_fn_qz_t_nft_misfire on fn_qz_triggers(sched_name,misfire_instr,next_fire_time);
1248 create index idx_fn_qz_t_nft_st_misfire on fn_qz_triggers(sched_name,misfire_instr,next_fire_time,trigger_state);
1249 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);
1250 create index idx_fn_qz_ft_trig_inst_name on fn_qz_fired_triggers(sched_name,instance_name);
1251 create index idx_fn_qz_ft_inst_job_req_rcvry on fn_qz_fired_triggers(sched_name,instance_name,requests_recovery);
1252 create index idx_fn_qz_ft_j_g on fn_qz_fired_triggers(sched_name,job_name,job_group);
1253 create index idx_fn_qz_ft_jg on fn_qz_fired_triggers(sched_name,job_group);
1254 create index idx_fn_qz_ft_t_g on fn_qz_fired_triggers(sched_name,trigger_name,trigger_group);
1255 create index idx_fn_qz_ft_tg on fn_qz_fired_triggers(sched_name,trigger_group);
1257 -- ------------------ ALTER TABLE ADD CONSTRAINT FOREIGN KEY SECTION
1259 -- NAME: FK_FN_AUDIT_REF_205_FN_LU_AC; TYPE: CONSTRAINT
1261 alter table fn_audit_log
1262 add constraint fk_fn_audit_ref_205_fn_lu_ac foreign key (activity_cd) references fn_lu_activity(activity_cd);
1264 -- NAME: FK_FN_ROLE__REF_201_FN_FUNCT; TYPE: CONSTRAINT
1266 alter table fn_role_function
1267 add constraint fk_fn_role__ref_201_fn_funct foreign key (function_cd) references fn_function(function_cd);
1269 -- NAME: FK_FN_USER__REF_178_FN_APP; TYPE: FK CONSTRAINT
1271 alter table fn_user_role
1272 add constraint fk_fn_user__ref_178_fn_app foreign key (app_id) references fn_app(app_id);
1274 -- NAME: FK_CR_REPOR_REF_14707_CR_REPOR; TYPE: FK CONSTRAINT
1276 alter table cr_report_schedule
1277 add constraint fk_cr_repor_ref_14707_cr_repor foreign key (rep_id) references cr_report(rep_id);
1279 -- NAME: FK_CR_REPOR_REF_14716_CR_REPOR; TYPE: FK CONSTRAINT
1281 alter table cr_report_schedule_users
1282 add constraint fk_cr_repor_ref_14716_cr_repor foreign key (schedule_id) references cr_report_schedule(schedule_id);
1284 -- NAME: FK_CR_REPOR_REF_17645_CR_REPOR; TYPE: FK CONSTRAINT
1286 alter table cr_report_log
1287 add constraint fk_cr_repor_ref_17645_cr_repor foreign key (rep_id) references cr_report(rep_id);
1289 -- NAME: FK_CR_REPOR_REF_8550_CR_REPOR; TYPE: FK CONSTRAINT
1291 alter table cr_report_access
1292 add constraint fk_cr_repor_ref_8550_cr_repor foreign key (rep_id) references cr_report(rep_id);
1294 -- NAME: FK_CR_REPORT_REP_ID; TYPE: FK CONSTRAINT
1296 alter table cr_report_email_sent_log
1297 add constraint fk_cr_report_rep_id foreign key (rep_id) references cr_report(rep_id);
1299 -- NAME: FK_CR_TABLE_REF_311_CR_TAB; TYPE: FK CONSTRAINT
1301 alter table cr_table_join
1302 add constraint fk_cr_table_ref_311_cr_tab foreign key (src_table_name) references cr_table_source(table_name);
1304 -- NAME: FK_CR_TABLE_REF_315_CR_TAB; TYPE: FK CONSTRAINT
1306 alter table cr_table_join
1307 add constraint fk_cr_table_ref_315_cr_tab foreign key (dest_table_name) references cr_table_source(table_name);
1309 -- NAME: FK_CR_TABLE_REF_32384_CR_TABLE; TYPE: FK CONSTRAINT
1311 alter table cr_table_role
1312 add constraint fk_cr_table_ref_32384_cr_table foreign key (table_name) references cr_table_source(table_name);
1314 -- NAME: FK_FN_TAB_FUNCTION_CD; TYPE: FK CONSTRAINT
1317 add constraint fk_fn_tab_function_cd foreign key (function_cd) references fn_function(function_cd);
1319 -- NAME: FK_FN_TAB_SELECTED_TAB_CD; TYPE: FK CONSTRAINT
1321 alter table fn_tab_selected
1322 add constraint fk_fn_tab_selected_tab_cd foreign key (selected_tab_cd) references fn_tab(tab_cd);
1324 -- NAME: FK_FN_TAB_SET_CD; TYPE: FK CONSTRAINT
1327 add constraint fk_fn_tab_set_cd foreign key (tab_set_cd) references fn_lu_tab_set(tab_set_cd);
1329 -- NAME: FK_FN_USER_REF_110_FN_ORG; TYPE: FK CONSTRAINT
1332 add constraint fk_fn_user_ref_110_fn_org foreign key (org_id) references fn_org(org_id);
1334 -- NAME: FK_FN_USER_REF_123_FN_LU_AL; TYPE: FK CONSTRAINT
1337 add constraint fk_fn_user_ref_123_fn_lu_al foreign key (alert_method_cd) references fn_lu_alert_method(alert_method_cd);
1339 -- NAME: FK_FN_USER_REF_197_FN_USER; TYPE: FK CONSTRAINT
1342 add constraint fk_fn_user_ref_197_fn_user foreign key (manager_id) references fn_user(user_id);
1344 -- NAME: FK_FN_USER_REF_198_FN_USER; TYPE: FK CONSTRAINT
1347 add constraint fk_fn_user_ref_198_fn_user foreign key (created_id) references fn_user(user_id);
1349 -- NAME: FK_FN_USER_REF_199_FN_USER; TYPE: FK CONSTRAINT
1352 add constraint fk_fn_user_ref_199_fn_user foreign key (modified_id) references fn_user(user_id);
1354 -- NAME: FK_PARENT_KEY_CR_FOLDER; TYPE: FK CONSTRAINT
1356 alter table cr_folder
1357 add constraint fk_parent_key_cr_folder foreign key (parent_folder_id) references cr_folder(folder_id);
1359 -- NAME: FK_PSEUDO_ROLE_PSEUDO_ROLE_ID; TYPE: FK CONSTRAINT
1361 alter table fn_user_pseudo_role
1362 add constraint fk_pseudo_role_pseudo_role_id foreign key (pseudo_role_id) references fn_role(role_id);
1364 -- NAME: FK_PSEUDO_ROLE_USER_ID; TYPE: FK CONSTRAINT
1366 alter table fn_user_pseudo_role
1367 add constraint fk_pseudo_role_user_id foreign key (user_id) references fn_user(user_id);
1369 -- NAME: FK_RESTRICTED_URL_FUNCTION_CD; TYPE: FK CONSTRAINT
1371 alter table fn_restricted_url
1372 add constraint fk_restricted_url_function_cd foreign key (function_cd) references fn_function(function_cd);
1374 -- NAME: FK_TIMEZONE; TYPE: FK CONSTRAINT
1377 add constraint fk_timezone foreign key (timezone) references fn_lu_timezone(timezone_id);
1379 -- NAME: SYS_C0014614; TYPE: FK CONSTRAINT
1381 alter table cr_report_file_history
1382 add constraint sys_c0014614 foreign key (file_type_id) references cr_lu_file_type(lookup_id);
1384 -- NAME: SYS_C0014615; TYPE: FK CONSTRAINT
1386 alter table cr_report_file_history
1387 add constraint sys_c0014615 foreign key (rep_id) references cr_report(rep_id);
1389 -- NAME: SYS_C0014616; TYPE: FK CONSTRAINT
1391 alter table cr_hist_user_map
1392 add constraint sys_c0014616 foreign key (hist_id) references cr_report_file_history(hist_id);
1394 -- NAME: SYS_C0014617; TYPE: FK CONSTRAINT
1396 alter table cr_hist_user_map
1397 add constraint sys_c0014617 foreign key (user_id) references fn_user(user_id);