-- --------------------------------------------------------------------------------------------------------------- -- This script populates tables in the OPEN-SOURCE version 3.2.0 of the ECOMP SDK application database. -- The DML COMMON script must be executed first! -- --------------------------------------------------------------------------------------------------------------- SET FOREIGN_KEY_CHECKS=1; USE ecomp_sdk; 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 (1,'Demo Report - chart','this report show chart config','N','\n\n Demo Report - chart\n this report show chart config\n local\n mysql\n BarChart3D\n N\n Counts\n 1200\n 420\n true\n false\n false\n 1\n 2019-09-25-04:00\n SELECT \n date_format(create_date,\'%Y-%m-%d\') Create_date, \n count(*) num_rpt \nfrom cr_report\ngroup by date_format(create_date,\'%Y-%m-%d\')\norder by 1\n Put Chart Title Here\n \n 0\n Your Search didn\'t yield any results.\n left\n 100\n NNNNNNN\n N\n N\n N|\n \n \n DUAL\n \n DUAL\n \n \n du0\n VARCHAR2\n date_format(create_date,\'%Y-%m-%d\')\n Create Date\n 10\n 0pxpx\n Left\n \n 1\n true\n true\n VARCHAR2\n 0\n \n false\n false\n \n LEGEND\n -1\n \n \n false\n false\n \n \n \n \n VARCHAR2\n \n \n \n N\n 0\n 0\n 0\n \n \n du0\n VARCHAR2\n count(*)\n Report Count\n 10\n 0pxpx\n Left\n \n 2\n true\n true\n VARCHAR2\n 0\n \n false\n false\n \n 0\n 1\n #FF0000\n default\n false\n false\n \n \n \n \n VARCHAR2\n \n \n \n N\n 0\n 0\n 0\n \n \n \n \n \n \n \n From Date\n Select Field Type\n Y\n DATE\n N\n \n 1\n select date_format(sysdate(),\'%m/%d/%Y\') id, date_format(sysdate(),\'%m/%d/%Y\') name from dual\n \n 0001-02-07+00:15\n \n \n false\n N\n \n \n \n TO Date\n Select Field Type\n Y\n DATE\n N\n \n 2\n select date_format(sysdate(),\'%m/%d/%Y\') id, date_format(sysdate(),\'%m/%d/%Y\') name from dual\n \n 0001-02-07+00:15\n \n \n false\n N\n \n \n \n 500\n \n vertical\n N\n top\n up45\n \n \n true\n true\n false\n false\n false\n false\n false\n line\n false\n false\n false\n false\n 100\n 100\n 100\n 100\n \n N\n N\n N\n N\n N\n N\n\n',1,'2019-09-25 17:46:18',1,'2020-05-05 04:31:07','HOME','N',1,NULL,'N','N'), (2,'Demo Report - drilldown with formfield','Demo Report - drilldown with formfield','N','\n\n Demo Report - drilldown with formfield\n Demo Report - drilldown with formfield\n local\n \n false\n false\n false\n 1\n 2019-09-25-04:00\n SELECT \n c.role_name ROLE_NAME, \n a.FIRST_NAME FIRST_NAME, \n a.LAST_NAME LAST_NAME, \n a.EMAIL EMAIL \nfrom fn_user a, fn_user_role b, fn_role c\nwhere c.role_id=b.role_id and b.user_id=a.user_id and c.role_id in [ROLE]\n 0\n Your Search didn\'t yield any results.\n left\n 100\n NYNNNNN\n N\n N\n N|\n \n \n DUAL\n \n DUAL\n \n \n du0\n c.role_name\n c.role_name\n ROLE_NAME\n 10\n Left\n 1\n true\n true\n VARCHAR2\n false\n VARCHAR2\n \n \n du0\n a.FIRST_NAME\n a.FIRST_NAME\n FIRST_NAME\n 10\n Left\n 2\n true\n true\n VARCHAR2\n false\n VARCHAR2\n \n \n du0\n a.LAST_NAME\n a.LAST_NAME\n LAST_NAME\n 10\n Left\n 3\n true\n true\n VARCHAR2\n false\n VARCHAR2\n \n \n du0\n a.EMAIL\n a.EMAIL\n EMAIL\n 10\n Left\n 4\n true\n true\n VARCHAR2\n false\n VARCHAR2\n \n \n \n \n \n \n \n ROLE\n LIST_MULTI_SELECT\n Y\n \n N\n \n 1\n select role_id id, role_name name from fn_role order by 2\n \n 0001-02-07+00:15\n \n \n false\n N\n \n \n 500\n N\n N\n N\n N\n N\n N\n\n',1,'2019-09-25 18:25:02',1,'2020-05-05 04:04:01','','N',1,NULL,'N','N'), (3,'Demo Report - parent','this report show how many users for each ROLE. click Counts number to see USER details.','N','\n\n Demo Report - parent\n this report show how many users for each ROLE. click Counts number to see USER details.\n local\n \n false\n false\n false\n 1\n 2019-12-06-05:00\n SELECT \n count(*) co3, \n a.role_id ar5, \n a.role_name ar4 \nfrom fn_role a, fn_user_role b, fn_user c \n where a.role_id=b.role_id and b.user_id=c.user_id\n group by a.role_name\n \n 0\n Your Search didn\'t yield any results.\n left\n 100\n NYNNNNN\n N\n N\n N|Test Report Demo\n \n \n DUAL\n \n DUAL\n \n \n du0\n VARCHAR2\n count(*)\n Counts \n 10\n 0pxpx\n Left\n \n 1\n true\n true\n VARCHAR2\n 0\n \n false\n false\n \n false\n \n 28\n ff1=[ar5]\n \n VARCHAR2\n \n N\n 0\n 0\n 0\n \n \n du0\n VARCHAR2\n a.role_id\n Role ID\n 10\n 0pxpx\n Left\n \n 2\n true\n true\n VARCHAR2\n 0\n \n false\n false\n \n false\n \n \n \n \n VARCHAR2\n \n N\n 0\n 0\n 0\n \n \n du0\n VARCHAR2\n a.role_name\n Role Name\n 10\n 0pxpx\n Left\n \n 3\n true\n true\n VARCHAR2\n 0\n \n false\n false\n \n false\n \n \n \n sem1\n VARCHAR2\n \n N\n 0\n 0\n 0\n \n \n \n \n \n \n Display Formatting 1\n CELL\n ar4\n \n \n \n \n false\n false\n false\n \n \n \n 18\n \n \n System Administrator\n =\n true\n false\n false\n #FF0000\n \n \n 18\n \n \n \n \n 500\n N\n N\n N\n N\n N\n N\n\n',1,'2019-12-06 19:29:51',1,'2020-05-05 04:07:08','HOME','N',1,NULL,'N','N'), (4,'Demo Report - Dashboard type','Compose several sub-reports into one view, Drag report from Data section and Chart section.','N','\n\n Demo Report - Dashboard type\n Compose several sub-reports into one view, Drag report from Data section and Chart section.\n \n [{\"x\":0,\"y\":0,\"cols\":7,\"rows\":1,\"hasContent\":{\"name\":\"Demo Report - chart\",\"id\":\"Data#27\",\"hideDisplay\":false}},{\"x\":0,\"y\":1,\"cols\":7,\"rows\":1,\"hasContent\":{\"name\":\"Demo Report - chart\",\"id\":\"Chart#27\",\"hideDisplay\":false}},{\"x\":0,\"y\":2,\"cols\":7,\"rows\":1,\"hasContent\":{\"name\":\"Demo Report - parent\",\"id\":\"Data#29\",\"hideDisplay\":false}}]\n \n false\n false\n 1\n 2020-05-06-04:00\n N\n \n\n',1,'2020-05-06 14:40:27',1,'2020-05-06 14:59:50','','N',1,NULL,'N','Y'); -- fn_menu INSERT INTO fn_menu (MENU_ID, LABEL, PARENT_ID, SORT_ORDER, ACTION, FUNCTION_CD, ACTIVE_YN, SERVLET, QUERY_STRING, EXTERNAL_URL, TARGET, MENU_SET_CD, SEPARATOR_YN, IMAGE_SRC) VALUES (1, 'Root', NULL, 10, NULL, 'menu_home', 'N', NULL, NULL, NULL, NULL, 'APP', 'N', NULL); -- we need even though it's inactive INSERT INTO fn_menu (MENU_ID, LABEL, PARENT_ID, SORT_ORDER, ACTION, FUNCTION_CD, ACTIVE_YN, SERVLET, QUERY_STRING, EXTERNAL_URL, TARGET, MENU_SET_CD, SEPARATOR_YN, IMAGE_SRC) VALUES (5000, 'Sample Pages', 1, 30, 'sample.htm', 'menu_sample', 'Y', NULL, NULL, NULL, NULL, 'APP', 'N', 'icon-documents-book'); INSERT INTO fn_menu (MENU_ID, LABEL, PARENT_ID, SORT_ORDER, ACTION, FUNCTION_CD, ACTIVE_YN, SERVLET, QUERY_STRING, EXTERNAL_URL, TARGET, MENU_SET_CD, SEPARATOR_YN, IMAGE_SRC) VALUES (2, 'Home', 1, 10, 'welcome', 'menu_home', 'Y', NULL, NULL, NULL, NULL, 'APP', 'N', 'icon-building-home'); INSERT INTO fn_menu (MENU_ID, LABEL, PARENT_ID, SORT_ORDER, ACTION, FUNCTION_CD, ACTIVE_YN, SERVLET, QUERY_STRING, EXTERNAL_URL, TARGET, MENU_SET_CD, SEPARATOR_YN, IMAGE_SRC) VALUES (8, 'Reports', 1, 40, 'report.htm', 'menu_reports', 'Y', NULL, NULL, NULL, NULL, 'APP', 'N', 'icon-misc-piechart'); INSERT INTO fn_menu (MENU_ID, LABEL, PARENT_ID, SORT_ORDER, ACTION, FUNCTION_CD, ACTIVE_YN, SERVLET, QUERY_STRING, EXTERNAL_URL, TARGET, MENU_SET_CD, SEPARATOR_YN, IMAGE_SRC) VALUES (9, 'Profile', 1, 90, 'userProfile', 'menu_profile', 'Y', NULL, NULL, NULL, NULL, 'APP', 'N', 'icon-people-oneperson'); INSERT INTO fn_menu (MENU_ID, LABEL, PARENT_ID, SORT_ORDER, ACTION, FUNCTION_CD, ACTIVE_YN, SERVLET, QUERY_STRING, EXTERNAL_URL, TARGET, MENU_SET_CD, SEPARATOR_YN, IMAGE_SRC) VALUES (10, 'Admin', 1, 110, 'role_list.htm', 'menu_admin', 'Y', NULL, NULL, NULL, NULL, 'APP', 'N', 'icon-content-star'); INSERT INTO fn_menu (MENU_ID, LABEL, PARENT_ID, SORT_ORDER, ACTION, FUNCTION_CD, ACTIVE_YN, SERVLET, QUERY_STRING, EXTERNAL_URL, TARGET, MENU_SET_CD, SEPARATOR_YN, IMAGE_SRC) VALUES (84, 'All Reports', 8, 50, 'report', 'menu_reports', 'Y', NULL, NULL, NULL, NULL, 'APP', 'N', '/static/fusion/images/reports.png'); INSERT INTO fn_menu (MENU_ID, LABEL, PARENT_ID, SORT_ORDER, ACTION, FUNCTION_CD, ACTIVE_YN, SERVLET, QUERY_STRING, EXTERNAL_URL, TARGET, MENU_SET_CD, SEPARATOR_YN, IMAGE_SRC) values (87, 'Create Reports', 8, 120, 'report#/report_wizard', 'menu_reports', 'Y', NULL, 'r_action=report.create', NULL, NULL, 'APP', 'N', NULL); INSERT INTO fn_menu (MENU_ID, LABEL, PARENT_ID, SORT_ORDER, ACTION, FUNCTION_CD, ACTIVE_YN, SERVLET, QUERY_STRING, EXTERNAL_URL, TARGET, MENU_SET_CD, SEPARATOR_YN, IMAGE_SRC) values (88, 'Sample Dashboard', 8, 130, 'report_dashboard', 'menu_reports', 'N', NULL, NULL, NULL, NULL, 'APP', 'N', NULL); INSERT INTO fn_menu (MENU_ID, LABEL, PARENT_ID, SORT_ORDER, ACTION, FUNCTION_CD, ACTIVE_YN, SERVLET, QUERY_STRING, EXTERNAL_URL, TARGET, MENU_SET_CD, SEPARATOR_YN, IMAGE_SRC) VALUES (89, 'Import', 8, 140, 'report#/report_import', 'menu_reports', 'N', null, null, null, null, 'APP', 'N', null); INSERT INTO fn_menu (MENU_ID, LABEL, PARENT_ID, SORT_ORDER, ACTION, FUNCTION_CD, ACTIVE_YN, SERVLET, QUERY_STRING, EXTERNAL_URL, TARGET, MENU_SET_CD, SEPARATOR_YN, IMAGE_SRC) VALUES (94, 'Self', 9, 40,'userProfile#/self_profile', 'menu_profile', 'Y', NULL, NULL, NULL, NULL, 'APP', 'N', '/static/fusion/images/profile.png'); INSERT INTO fn_menu (MENU_ID, LABEL, PARENT_ID, SORT_ORDER, ACTION, FUNCTION_CD, ACTIVE_YN, SERVLET, QUERY_STRING, EXTERNAL_URL, TARGET, MENU_SET_CD, SEPARATOR_YN, IMAGE_SRC) VALUES (101, 'Roles', 10, 20, 'admin#/admin', 'menu_admin', 'Y', NULL, NULL, NULL, NULL, 'APP', 'N', '/static/fusion/images/users.png'); INSERT INTO fn_menu (MENU_ID, LABEL, PARENT_ID, SORT_ORDER, ACTION, FUNCTION_CD, ACTIVE_YN, SERVLET, QUERY_STRING, EXTERNAL_URL, TARGET, MENU_SET_CD, SEPARATOR_YN, IMAGE_SRC) VALUES (102, 'Role Functions', 10, 30, 'admin#/role_function_list', 'menu_admin', 'Y', NULL, NULL, NULL, NULL, 'APP', 'N', NULL); INSERT INTO fn_menu (MENU_ID, LABEL, PARENT_ID, SORT_ORDER, ACTION, FUNCTION_CD, ACTIVE_YN, SERVLET, QUERY_STRING, EXTERNAL_URL, TARGET, MENU_SET_CD, SEPARATOR_YN, IMAGE_SRC) VALUES (105, 'Cache Admin', 10, 40, 'admin#/jcs_admin', 'menu_admin', 'Y', NULL, NULL, NULL, NULL, 'APP', 'N', '/static/fusion/images/cache.png'); INSERT INTO fn_menu (MENU_ID, LABEL, PARENT_ID, SORT_ORDER, ACTION, FUNCTION_CD, ACTIVE_YN, SERVLET, QUERY_STRING, EXTERNAL_URL, TARGET, MENU_SET_CD, SEPARATOR_YN, IMAGE_SRC) VALUES (108, 'Usage', 10, 80, 'admin#/usage_list', 'menu_admin', 'Y', NULL, NULL, NULL, NULL, 'APP', 'N', '/static/fusion/images/users.png'); INSERT INTO fn_menu (MENU_ID, LABEL, PARENT_ID, SORT_ORDER, ACTION, FUNCTION_CD, ACTIVE_YN, SERVLET, QUERY_STRING, EXTERNAL_URL, TARGET, MENU_SET_CD, SEPARATOR_YN, IMAGE_SRC) VALUES (121, 'Collaboration', 5000, 100, 'samplePage#/collaborate_list', 'menu_sample', 'Y', NULL, NULL, NULL, NULL, 'APP', 'N', '/static/fusion/images/bubble.png'); INSERT INTO fn_menu (MENU_ID, LABEL, PARENT_ID, SORT_ORDER, ACTION, FUNCTION_CD, ACTIVE_YN, SERVLET, QUERY_STRING, EXTERNAL_URL, TARGET, MENU_SET_CD, SEPARATOR_YN, IMAGE_SRC) VALUES (930, 'Search', 9, 15, 'userProfile', 'menu_admin', 'Y', NULL, NULL, NULL, NULL, 'APP', 'N', '/static/fusion/images/search_profile.png'); INSERT INTO fn_menu (MENU_ID, LABEL, PARENT_ID, SORT_ORDER, ACTION, FUNCTION_CD, ACTIVE_YN, SERVLET, QUERY_STRING, EXTERNAL_URL, TARGET, MENU_SET_CD, SEPARATOR_YN, IMAGE_SRC) VALUES (150022, 'Menus', 10, 60, 'admin#/admin_menu_edit', 'menu_admin', 'Y', NULL, NULL, NULL, NULL, 'APP', 'N', NULL); INSERT INTO fn_menu (MENU_ID, LABEL, PARENT_ID, SORT_ORDER, ACTION, FUNCTION_CD, ACTIVE_YN, SERVLET, QUERY_STRING, EXTERNAL_URL, TARGET, MENU_SET_CD, SEPARATOR_YN, IMAGE_SRC) VALUES (150038,'Notebook',5000,135,'samplePage#/notebook','menu_sample','Y',NULL,NULL,NULL,NULL,'APP','N',NULL); -- fn_menu - update menu icon UPDATE fn_menu SET `IMAGE_SRC`='icon ion-ios-home' WHERE `LABEL`='Home'; UPDATE fn_menu SET `IMAGE_SRC`='icon ion-md-pie' WHERE `LABEL`='Reports'; UPDATE fn_menu SET `IMAGE_SRC`='ion-md-person' WHERE `LABEL`='Profile'; UPDATE fn_menu SET `IMAGE_SRC`='icon ion-md-star' WHERE `LABEL`='Admin'; -- updating fn_menu ACTION url UPDATE fn_menu SET ACTION='welcome' WHERE LABEL='Home'; UPDATE fn_menu SET ACTION='v2/userProfile' WHERE LABEL='Search'; UPDATE fn_menu SET ACTION='v2/userProfile/post_search' WHERE LABEL='Import from WEBPHONE'; UPDATE fn_menu SET ACTION='v2/userProfile/self_profile' WHERE LABEL='Self'; UPDATE fn_menu SET ACTION='v2/admin/admin' WHERE LABEL='Roles'; UPDATE fn_menu SET ACTION='v2/admin/role_function_list' WHERE LABEL='Role Functions'; UPDATE fn_menu SET ACTION='v2/admin/usage_list' WHERE LABEL='Usage'; UPDATE fn_menu SET ACTION='v2/admin/cache_admin' WHERE LABEL='Cache Admin'; UPDATE fn_menu SET ACTION='v2/admin/admin_menu_edit' WHERE LABEL='Menus'; UPDATE fn_menu SET ACTION='v2/create' WHERE LABEL='Create Reports'; UPDATE fn_menu SET ACTION='v2/report-list' WHERE LABEL='All Reports'; -- 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) values (1,null,null,'Demo',null,'User',null,null,null,'demo@email.com',null,null,null,'demo',null,'demo','demo',str_to_date('24-OCT-16','%d-%M-%Y'),'Y',null,str_to_date('17-OCT-16','%d-%M-%Y'),1,str_to_date('24-OCT-16','%d-%M-%Y'),'N',null,null,null,'NJ',null,'US',null,null,null,null,null,10,null,null,null,null,null,null); -- fn_app 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) VALUES (1,'Default',null,'Some Default Description','Some Default Note',null,null,null,'ECPP','?','1','JuCerIRKt/faEcx8QdgncLEEv+IOZjpHe7Pi5DEPqKs=','N','N',null,'Default',null,null,'ECOMP-PORTAL-INBOX'); -- fn_user_role Insert into fn_user_role (USER_ID,ROLE_ID,PRIORITY,APP_ID) values (1,1,null,1); commit;