-- MySQL Administrator dump 1.4
--
-- ------------------------------------------------------
-- Server version	5.0.21-community-nt-log


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;

/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;


--
-- Create schema scf
--

CREATE DATABASE IF NOT EXISTS scf;
USE scf;

--
-- Temporary table structure for view `batch_component_map_view`
--
DROP TABLE IF EXISTS `batch_component_map_view`;
DROP VIEW IF EXISTS `batch_component_map_view`;
CREATE TABLE `batch_component_map_view` (
  `SERVICE_ID` int(10),
  `SERVICE_NAME` varchar(100),
  `COMPONENT_ID` int(10),
  `COMPONENT_NAME` varchar(255),
  `DESCRIPTION` varchar(500),
  `INFOMAN_PRIV_CLASS` varchar(10),
  `RUN_RULE` varchar(100),
  `COMPLETION_TIME` time
);

--
-- Temporary table structure for view `batch_components_not_mapped_view`
--
DROP TABLE IF EXISTS `batch_components_not_mapped_view`;
DROP VIEW IF EXISTS `batch_components_not_mapped_view`;
CREATE TABLE `batch_components_not_mapped_view` (
  `SERVICE_ID` int(10),
  `SERVICE_NAME` varchar(100),
  `COMPONENT_ID_NOT_USED` int(11),
  `COMPONENT_NAME_NOT_USED` varchar(255)
);

--
-- Temporary table structure for view `batch_components_view`
--
DROP TABLE IF EXISTS `batch_components_view`;
DROP VIEW IF EXISTS `batch_components_view`;
CREATE TABLE `batch_components_view` (
  `COMPONENT_ID` int(11),
  `COMPONENT_NAME` varchar(255),
  `DESCRIPTION` varchar(500),
  `INFOMAN_PRIV_CLASS` varchar(10),
  `START_JOB` varchar(20),
  `END_JOB` varchar(20),
  `RUN_RULE` varchar(100),
  `COMPLETION_TIME` time
);

--
-- Temporary table structure for view `bdm_view`
--
DROP TABLE IF EXISTS `bdm_view`;
DROP VIEW IF EXISTS `bdm_view`;
CREATE TABLE `bdm_view` (
  `BDM_ID` int(10),
  `NAME` varchar(255),
  `PHONE` varchar(50)
);

--
-- Temporary table structure for view `business_units_sorted_view`
--
DROP TABLE IF EXISTS `business_units_sorted_view`;
DROP VIEW IF EXISTS `business_units_sorted_view`;
CREATE TABLE `business_units_sorted_view` (
  `BUSINESS_ID` int(11),
  `BUSINESS_NAME` varchar(100),
  `BDM_NAME` varchar(255)
);

--
-- Temporary table structure for view `categories_view`
--
DROP TABLE IF EXISTS `categories_view`;
DROP VIEW IF EXISTS `categories_view`;
CREATE TABLE `categories_view` (
  `CATEGORY_ID` int(10),
  `SYSTEM` varchar(50),
  `COMPONENT` varchar(50),
  `ITEM` varchar(50),
  `MANAGER` varchar(100)
);

--
-- Temporary table structure for view `ci_impact_view`
--
DROP TABLE IF EXISTS `ci_impact_view`;
DROP VIEW IF EXISTS `ci_impact_view`;
CREATE TABLE `ci_impact_view` (
  `SERVICE_NAME` varchar(100),
  `COMPONENT_NAME` varchar(255),
  `TYPE` varchar(4),
  `USED` varchar(2),
  `AVAILABILITY_OLO` double,
  `SERVICE_IMPACT_CODE` varchar(8),
  `SERVICE_COMMITMENT` varchar(34),
  `COMPONENT_ID` int(11),
  `SERVICE_OWNING_BU` varchar(255)
);

--
-- Temporary table structure for view `disaster_recovery_view`
--
DROP TABLE IF EXISTS `disaster_recovery_view`;
DROP VIEW IF EXISTS `disaster_recovery_view`;
CREATE TABLE `disaster_recovery_view` (
  `SERVICE_NAME` varchar(100),
  `RTO` varchar(5000),
  `RPO` varchar(1000),
  `ACTIVE_ACTIVE` varchar(20),
  `AA_DETAIL` varchar(1000),
  `LOCATION_NAME` varchar(255),
  `LOCATION_ID` int(10),
  `RESTORATION_PRIORITY` varchar(20),
  `PRIM_LOC` varchar(255),
  `PRIM_LOC_ID` int(11),
  `CERT_NAME` varchar(7),
  `PLAN_NAME` varchar(7),
  `PLAN_ID` int(10) unsigned,
  `CERT_ID` int(10),
  `TECHNICAL_CERTIFICATION` varchar(20),
  `COMMENTS` varchar(1000),
  `THIRD_PARTY_HELD` varchar(50),
  `CERT_RTO` varchar(5),
  `PLAN_RTO` varchar(5),
  `SERVICE_ID` int(10)
);

--
-- Temporary table structure for view `location_view`
--
DROP TABLE IF EXISTS `location_view`;
DROP VIEW IF EXISTS `location_view`;
CREATE TABLE `location_view` (
  `LOCATION_ID` int(11),
  `LOCATION_NAME` varchar(255),
  `ACTIVE` varchar(3),
  `REGION` varchar(10)
);

--
-- Temporary table structure for view `plan_id_view`
--
DROP TABLE IF EXISTS `plan_id_view`;
DROP VIEW IF EXISTS `plan_id_view`;
CREATE TABLE `plan_id_view` (
  `PLAN_ID` int(10) unsigned,
  `PLAN_NAME` varchar(7),
  `PLAN_RTO` varchar(5),
  `DESCRIPTION` varchar(50)
);

--
-- Temporary table structure for view `service_component_map_view`
--
DROP TABLE IF EXISTS `service_component_map_view`;
DROP VIEW IF EXISTS `service_component_map_view`;
CREATE TABLE `service_component_map_view` (
  `SERVICE_ID` int(10),
  `SERVICE_NAME` varchar(100),
  `COMPONENT_ID` int(11),
  `COMPONENT_NAME` varchar(255),
  `TYPE` varchar(4),
  `USED` varchar(2),
  `COMMITMENT` double,
  `CATEGORY_ID` int(10),
  `SUPPORT_CLASS_NAME` varchar(100),
  `SUPPORT_TEAM_NAME` varchar(500)
);

--
-- Temporary table structure for view `service_components_not_mapped_view`
--
DROP TABLE IF EXISTS `service_components_not_mapped_view`;
DROP VIEW IF EXISTS `service_components_not_mapped_view`;
CREATE TABLE `service_components_not_mapped_view` (
  `SERVICE_ID` int(10),
  `SERVICE_NAME` varchar(100),
  `COMPONENT_ID_NOT_USED` int(11),
  `COMPONENT_NAME_NOT_USED` varchar(255)
);

--
-- Temporary table structure for view `service_components_sorted`
--
DROP TABLE IF EXISTS `service_components_sorted`;
DROP VIEW IF EXISTS `service_components_sorted`;
CREATE TABLE `service_components_sorted` (
  `COMPONENT_ID` int(11),
  `COMPONENT_NAME` varchar(255),
  `AVAILABILITY_OLO` double,
  `SUPPORT_TEAM` longtext,
  `COMPONENT_OWNER` varchar(500),
  `COMPONENT_TYPE` varchar(152),
  `DESCRIPTION` varchar(5000),
  `SCHEDULED_COMPONENT_DOWNTIME` varchar(200),
  `CATEGORY_ID` int(10),
  `SUPPORT_TEAM_ID` int(10),
  `LOCATION_NAME` varchar(255),
  `LOCATION_ID` int(10),
  `PRIM_LOC` varchar(255),
  `PRIM_LOC_ID` int(11),
  `CERT_NAME` varchar(7),
  `GENERAL` varchar(500),
  `PLAN_NAME` varchar(7),
  `PLAN_ID` int(10) unsigned,
  `CERT_ID` int(10) unsigned,
  `THIRD_PARTY_HELD` varchar(5),
  `CERT_RTO` varchar(5),
  `POLICY_ID` varchar(20),
  `PLAN_RTO` varchar(5),
  `SUPPORT_CLASS` varchar(100)
);

--
-- Temporary table structure for view `service_periods_view`
--
DROP TABLE IF EXISTS `service_periods_view`;
DROP VIEW IF EXISTS `service_periods_view`;
CREATE TABLE `service_periods_view` (
  `SERVICE_ID` int(10),
  `PERIOD_TYPE` varchar(50),
  `START_TIME1` time,
  `END_TIME1` time,
  `START_TIME2` time,
  `END_TIME2` time,
  `DAY_OF_WEEK` varchar(10),
  `PERIOD_ID` int(10)
);

--
-- Temporary table structure for view `service_report_view`
--
DROP TABLE IF EXISTS `service_report_view`;
DROP VIEW IF EXISTS `service_report_view`;
CREATE TABLE `service_report_view` (
  `SERVICE_ID` int(10),
  `SERVICE_NAME` varchar(100),
  `SERVICE_OWNING_BU` varchar(255),
  `DESCRIPTION` varchar(5000),
  `SERVICE_DEPENDENCIES` varchar(8000),
  `SERVICE_IMPACT_CODE` varchar(8),
  `LEGAL_REQUIREMENTS` varchar(1000),
  `COMMITMENT_PERIOD_FLAG` int(2),
  `SLC_MEASURES_FUNC` double,
  `SLC_MEASURES_AVAIL` double,
  `DELIVERABLES` varchar(1000),
  `DELIVERABLE_EXCEMPTION` varchar(1000),
  `MONITORING` varchar(1000),
  `LIMITATIONS` varchar(1000),
  `SW_LVL_COMMITMENT` varchar(2000),
  `RESPONSIBILITIES` varchar(2000),
  `STATUS` varchar(30),
  `TIME_VALUES` varchar(500),
  `HAS_DR_CAPABILITY` varchar(3),
  `RESTORATION_PRIORITY` varchar(20),
  `SERVICE_COMMITMENT` varchar(6),
  `ACTIVE_ACTIVE` varchar(20),
  `AA_DETAIL` varchar(1000),
  `LOCATION_ID` int(10),
  `CERT_ID` int(10),
  `PLAN_ID` int(10),
  `TECHNICAL_CERTIFICATION` varchar(20),
  `THIRD_PART_HELD` varchar(50),
  `RTO` varchar(5000),
  `RPO` varchar(1000),
  `COMMENTS` varchar(1000)
);

--
-- Temporary table structure for view `service_used_by_view`
--
DROP TABLE IF EXISTS `service_used_by_view`;
DROP VIEW IF EXISTS `service_used_by_view`;
CREATE TABLE `service_used_by_view` (
  `SERVICE_ID` int(8),
  `BUSINESS_ID` int(8),
  `SERVICE_NAME` varchar(100),
  `BUSINESS_NAME` varchar(100)
);

--
-- Temporary table structure for view `service_view`
--
DROP TABLE IF EXISTS `service_view`;
DROP VIEW IF EXISTS `service_view`;
CREATE TABLE `service_view` (
  `SERVICE_ID` int(10),
  `SERVICE_NAME` varchar(100),
  `SERVICE_OWNING_BU` varchar(255),
  `DESCRIPTION` varchar(5000),
  `SERVICE_DEPENDENCIES` varchar(8000),
  `SERVICE_IMPACT_CODE` varchar(8),
  `LEGAL_REQUIREMENTS` varchar(1000),
  `COMMITMENT_PERIOD_FLAG` int(2),
  `SLC_MEASURES_FUNC` double,
  `SLC_MEASURES_AVAIL` double,
  `SPECIAL_EVENTS` varchar(1000),
  `MAINTENANCE_WINDOW` varchar(1000),
  `MONITORING` varchar(1000),
  `LIMITATIONS` varchar(1000),
  `SERVICE_COMMENTS` varchar(2000),
  `RESPONSIBILITIES` varchar(2000),
  `STATUS` varchar(30),
  `TIME_VALUES` varchar(500),
  `LAST_AUDIT_ID` int(10),
  `LAST_USER_TO_UPDATE` varchar(50),
  `LAST_UPDATE_DATE` datetime,
  `AUDIT_ID_LIST` varbinary(512),
  `ACTION_TIME_LIST` varbinary(512),
  `USER_FULLNAME_LIST` varchar(512),
  `CHANGES_LIST` varchar(512),
  `HAS_DR_CAPABILITY` varchar(3),
  `RESTORATION_PRIORITY` varchar(20),
  `SERVICE_COMMITMENT` varchar(6),
  `A_MAINFRAME_INFRASTRUCTURE_COMPONENTS` varchar(512),
  `A_MAINFRAME_INFRASTRUCTURE_COMMITMENT` varbinary(512),
  `A_MAINFRAME_INFRASTRUCTURE_SUPPORT_CLASSES` varchar(512),
  `A_MAINFRAME_INFRASTRUCTURE_SUPPORT_TEAMS` varchar(512),
  `A_MAINFRAME_LPAR_COMPONENTS` varchar(512),
  `A_MAINFRAME_LPAR_COMMITMENT` varbinary(512),
  `A_MAINFRAME_LPAR_SUPPORT_CLASSES` varchar(512),
  `A_MAINFRAME_LPAR_SUPPORT_TEAMS` varchar(512),
  `A_MAINFRAME_SUBSYSTEM_COMPONENTS` varchar(512),
  `A_MAINFRAME_SUBSYSTEM_COMMITMENT` varbinary(512),
  `A_MAINFRAME_SUBSYSTEM_SUPPORT_CLASSES` varchar(512),
  `A_MAINFRAME_SUBSYSTEM_SUPPORT_TEAMS` varchar(512),
  `A_MAINFRAME_REGION_COMPONENTS` varchar(512),
  `A_MAINFRAME_REGION_COMMITMENT` varbinary(512),
  `A_MAINFRAME_REGION_SUPPORT_CLASSES` varchar(512),
  `A_MAINFRAME_REGION_SUPPORT_TEAMS` varchar(512),
  `A_DISTRIBUTED_INFRASTRUCTURE_COMPONENTS` varchar(512),
  `A_DISTRIBUTED_INFRASTRUCTURE_COMMITMENT` varbinary(512),
  `A_DISTRIBUTED_INFRASTRUCTURE_SUPPORT_CLASSES` varchar(512),
  `A_DISTRIBUTED_INFRASTRUCTURE_SUPPORT_TEAMS` varchar(512),
  `A_DISTRIBUTED_APPLICATION_SW_COMPONENTS` varchar(512),
  `A_DISTRIBUTED_APPLICATION_SW_COMMITMENT` varbinary(512),
  `A_DISTRIBUTED_APPLICATION_SW_SUPPORT_CLASSES` varchar(512),
  `A_DISTRIBUTED_APPLICATION_SW_SUPPORT_TEAMS` varchar(512),
  `A_PRODUCT_REQ_PRODUCT_COMPONENTS` varchar(512),
  `A_PRODUCT_REQ_PRODUCT_COMMITMENT` varbinary(512),
  `A_PRODUCT_REQ_PRODUCT_SUPPORT_CLASSES` varchar(512),
  `A_PRODUCT_REQ_PRODUCT_SUPPORT_TEAMS` varchar(512),
  `A_DISTRIBUTED_LPAR_COMPONENTS` varchar(512),
  `A_DISTRIBUTED_LPAR_COMMITMENT` varbinary(512),
  `A_DISTRIBUTED_LPAR_SUPPORT_CLASSES` varchar(512),
  `A_DISTRIBUTED_LPAR_SUPPORT_TEAMS` varchar(512),
  `A_TELSTRA_UC_COMPONENTS` varchar(512),
  `A_TELSTRA_UC_COMMITMENTS` varbinary(512),
  `A_TELSTRA_UC_SUPPORT_CLASSES` varchar(512),
  `A_TELSTRA_UC_SUPPORT_TEAMS` varchar(512),
  `F_MAINFRAME_INFRASTRUCTURE_COMPONENTS` varchar(512),
  `F_MAINFRAME_INFRASTRUCTURE_COMMITMENT` varbinary(512),
  `F_MAINFRAME_INFRASTRUCTURE_SUPPORT_CLASSES` varchar(512),
  `F_MAINFRAME_INFRASTRUCTURE_SUPPORT_TEAMS` varchar(512),
  `F_MAINFRAME_LPAR_COMPONENTS` varchar(512),
  `F_MAINFRAME_LPAR_COMMITMENT` varbinary(512),
  `F_MAINFRAME_LPAR_SUPPORT_CLASSES` varchar(512),
  `F_MAINFRAME_LPAR_SUPPORT_TEAMS` varchar(512),
  `F_MAINFRAME_SUBSYSTEM_COMPONENTS` varchar(512),
  `F_MAINFRAME_SUBSYSTEM_COMMITMENT` varbinary(512),
  `F_MAINFRAME_SUBSYSTEM_SUPPORT_CLASSES` varchar(512),
  `F_MAINFRAME_SUBSYSTEM_SUPPORT_TEAMS` varchar(512),
  `F_MAINFRAME_REGION_COMPONENTS` varchar(512),
  `F_MAINFRAME_REGION_COMMITMENT` varbinary(512),
  `F_MAINFRAME_REGION_SUPPORT_CLASSES` varchar(512),
  `F_MAINFRAME_REGION_SUPPORT_TEAMS` varchar(512),
  `F_DISTRIBUTED_INFRASTRUCTURE_COMPONENTS` varchar(512),
  `F_DISTRIBUTED_INFRASTRUCTURE_COMMITMENT` varbinary(512),
  `F_DISTRIBUTED_INFRASTRUCTURE_SUPPORT_CLASSES` varchar(512),
  `F_DISTRIBUTED_INFRASTRUCTURE_SUPPORT_TEAMS` varchar(512),
  `F_DISTRIBUTED_APPLICATION_SW_COMPONENTS` varchar(512),
  `F_DISTRIBUTED_APPLICATION_SW_COMMITMENT` varbinary(512),
  `F_DISTRIBUTED_APPLICATION_SW_SUPPORT_CLASSES` varchar(512),
  `F_DISTRIBUTED_APPLICATION_SW_SUPPORT_TEAMS` varchar(512),
  `F_PRODUCT_REQ_PRODUCT_COMPONENTS` varchar(512),
  `F_PRODUCT_REQ_PRODUCT_COMMITMENT` varbinary(512),
  `F_PRODUCT_REQ_PRODUCT_SUPPORT_CLASSES` varchar(512),
  `F_PRODUCT_REQ_PRODUCT_SUPPORT_TEAMS` varchar(512),
  `F_DISTRIBUTED_LPAR_COMPONENTS` varchar(512),
  `F_DISTRIBUTED_LPAR_COMMITMENT` varbinary(512),
  `F_DISTRIBUTED_LPAR_SUPPORT_CLASSES` varchar(512),
  `F_DISTRIBUTED_LPAR_SUPPORT_TEAMS` varchar(512),
  `F_TELSTRA_UC_COMPONENTS` varchar(512),
  `F_TELSTRA_UC_COMMITMENTS` varbinary(512),
  `F_TELSTRA_UC_SUPPORT_CLASSES` varchar(512),
  `F_TELSTRA_UC_SUPPORT_TEAMS` varchar(512),
  `BU_USERS_OF` varchar(512),
  `CRITICAL_PERIODS` varbinary(512),
  `MAINTENANCE_PERIODS` varbinary(512),
  `COMMITMENT_PERIODS` varbinary(512),
  `BATCH_JOB_NAMES` varchar(512),
  `BATCH_DESCRIPTIONS` varchar(512),
  `BATCH_PRIV_CLASSES` varchar(512),
  `BATCH_RUN_RULES` varchar(512),
  `BATCH_COMPLETION_TIMES` varbinary(512),
  `SERVICE_DIAGRAM` mediumblob,
  `ATTACHED_FILE_ID` int(8),
  `RTO` varchar(5000),
  `RPO` varchar(1000)
);

--
-- Temporary table structure for view `services_not_used_by_view`
--
DROP TABLE IF EXISTS `services_not_used_by_view`;
DROP VIEW IF EXISTS `services_not_used_by_view`;
CREATE TABLE `services_not_used_by_view` (
  `BUSINESS_ID` int(11),
  `BUSINESS_NAME` varchar(100),
  `SERVICE_ID_NOT_USED` int(10),
  `SERVICE_NAME_NOT_USED` varchar(100)
);

--
-- Temporary table structure for view `services_sorted`
--
DROP TABLE IF EXISTS `services_sorted`;
DROP VIEW IF EXISTS `services_sorted`;
CREATE TABLE `services_sorted` (
  `SERVICE_ID` int(10),
  `SERVICE_NAME` varchar(100),
  `SERVICE_OWNING_BU` varchar(255),
  `DESCRIPTION` varchar(5000),
  `SERVICE_DEPENDENCIES` varchar(8000),
  `SERVICE_IMPACT_CODE` varchar(8),
  `LEGAL_REQUIREMENTS` varchar(1000),
  `SLC_MEASURES_FUNC` double,
  `SLC_MEASURES_AVAIL` double,
  `DELIVERABLES` varchar(1000),
  `DELIVERABLE_EXCEMPTION` varchar(1000),
  `MONITORING` varchar(1000),
  `LIMITATIONS` varchar(1000),
  `SW_LVL_COMMITMENT` varchar(2000),
  `RESPONSIBILITIES` varchar(2000),
  `BDM_NAME` varchar(255),
  `STATUS` varchar(30),
  `COMMITMENT_PERIOD_FLAG` int(2),
  `LAST_USER_TO_UPDATE` varchar(100),
  `LAST_UPDATE_DATE` varchar(30),
  `ATTACHED_FILE_ID` int(8),
  `ATTACHED_FILE_NAME` varchar(100),
  `SLA_COMMIT_AVAIL` varbinary(56),
  `SLA_COMMIT_FUNC` varbinary(56)
);

--
-- Temporary table structure for view `storage_policy_view`
--
DROP TABLE IF EXISTS `storage_policy_view`;
DROP VIEW IF EXISTS `storage_policy_view`;
CREATE TABLE `storage_policy_view` (
  `STORAGE_ID` int(10),
  `POLICY_ID` varchar(20),
  `POL_DESCRIP` varchar(100)
);

--
-- Temporary table structure for view `support_team_view`
--
DROP TABLE IF EXISTS `support_team_view`;
DROP VIEW IF EXISTS `support_team_view`;
CREATE TABLE `support_team_view` (
  `SUPPORT_TEAM_ID` int(10),
  `CLASS_NAME` varchar(100),
  `TEAM_NAME` varchar(500),
  `CLASS_OWNER` varchar(500)
);

--
-- Definition of view `batch_component_map_view`
--

DROP TABLE IF EXISTS `batch_component_map_view`;
DROP VIEW IF EXISTS `batch_component_map_view`;
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`%` SQL SECURITY DEFINER VIEW `batch_component_map_view` AS select `bcm`.`SERVICE_ID` AS `SERVICE_ID`,`s`.`SERVICE_NAME` AS `SERVICE_NAME`,`bcm`.`COMPONENT_ID` AS `COMPONENT_ID`,`bc`.`COMPONENT_NAME` AS `COMPONENT_NAME`,`bc`.`DESCRIPTION` AS `DESCRIPTION`,`bc`.`INFOMAN_PRIV_CLASS` AS `INFOMAN_PRIV_CLASS`,`bc`.`RUN_RULE` AS `RUN_RULE`,`bc`.`COMPLETION_TIME` AS `COMPLETION_TIME` from (`batch_component_map` `bcm` join (`services` `s` join `batch_components` `bc`)) where ((`bcm`.`SERVICE_ID` = `s`.`SERVICE_ID`) and (`bcm`.`COMPONENT_ID` = `bc`.`COMPONENT_ID`)) order by `bc`.`COMPONENT_NAME`;

--
-- Definition of view `batch_components_not_mapped_view`
--

DROP TABLE IF EXISTS `batch_components_not_mapped_view`;
DROP VIEW IF EXISTS `batch_components_not_mapped_view`;
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`%` SQL SECURITY DEFINER VIEW `batch_components_not_mapped_view` AS select `s`.`SERVICE_ID` AS `SERVICE_ID`,`s`.`SERVICE_NAME` AS `SERVICE_NAME`,`bc`.`COMPONENT_ID` AS `COMPONENT_ID_NOT_USED`,`bc`.`COMPONENT_NAME` AS `COMPONENT_NAME_NOT_USED` from (`services` `s` join `batch_components` `bc`) where not(exists(select `bcm`.`COMPONENT_ID` AS `COMPONENT_ID` from `batch_component_map` `bcm` where ((`bc`.`COMPONENT_ID` = `bcm`.`COMPONENT_ID`) and (`s`.`SERVICE_ID` = `bcm`.`SERVICE_ID`))));

--
-- Definition of view `batch_components_view`
--

DROP TABLE IF EXISTS `batch_components_view`;
DROP VIEW IF EXISTS `batch_components_view`;
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`%` SQL SECURITY DEFINER VIEW `batch_components_view` AS select `bc`.`COMPONENT_ID` AS `COMPONENT_ID`,`bc`.`COMPONENT_NAME` AS `COMPONENT_NAME`,`bc`.`DESCRIPTION` AS `DESCRIPTION`,`bc`.`INFOMAN_PRIV_CLASS` AS `INFOMAN_PRIV_CLASS`,`bc`.`START_JOB` AS `START_JOB`,`bc`.`END_JOB` AS `END_JOB`,`bc`.`RUN_RULE` AS `RUN_RULE`,`bc`.`COMPLETION_TIME` AS `COMPLETION_TIME` from `batch_components` `bc` order by `bc`.`COMPONENT_NAME`;

--
-- Definition of view `bdm_view`
--

DROP TABLE IF EXISTS `bdm_view`;
DROP VIEW IF EXISTS `bdm_view`;
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`%` SQL SECURITY DEFINER VIEW `bdm_view` AS select `b`.`BDM_ID` AS `BDM_ID`,`b`.`NAME` AS `NAME`,`b`.`PHONE` AS `PHONE` from `bdm` `b` order by `b`.`NAME`;

--
-- Definition of view `business_units_sorted_view`
--

DROP TABLE IF EXISTS `business_units_sorted_view`;
DROP VIEW IF EXISTS `business_units_sorted_view`;
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`%` SQL SECURITY DEFINER VIEW `business_units_sorted_view` AS select `b`.`BUSINESS_ID` AS `BUSINESS_ID`,`b`.`BUSINESS_NAME` AS `BUSINESS_NAME`,`b`.`BDM_NAME` AS `BDM_NAME` from `business_names` `b` order by `b`.`BUSINESS_NAME`;

--
-- Definition of view `categories_view`
--

DROP TABLE IF EXISTS `categories_view`;
DROP VIEW IF EXISTS `categories_view`;
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`%` SQL SECURITY DEFINER VIEW `categories_view` AS select `categories`.`CATEGORY_ID` AS `CATEGORY_ID`,`categories`.`SYSTEM` AS `SYSTEM`,`categories`.`COMPONENT` AS `COMPONENT`,`categories`.`ITEM` AS `ITEM`,`categories`.`MANAGER` AS `MANAGER` from `categories` order by `categories`.`SYSTEM`,`categories`.`COMPONENT`,`categories`.`ITEM`;

--
-- Definition of view `ci_impact_view`
--

DROP TABLE IF EXISTS `ci_impact_view`;
DROP VIEW IF EXISTS `ci_impact_view`;
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`%` SQL SECURITY DEFINER VIEW `ci_impact_view` AS select `scm`.`SERVICE_NAME` AS `SERVICE_NAME`,`scm`.`COMPONENT_NAME` AS `COMPONENT_NAME`,`scm`.`TYPE` AS `TYPE`,`scm`.`USED` AS `USED`,`sc`.`AVAILABILITY_OLO` AS `AVAILABILITY_OLO`,`s`.`SERVICE_IMPACT_CODE` AS `SERVICE_IMPACT_CODE`,if((`s`.`COMMITMENT_PERIOD_FLAG` = 1),_utf8'24 x 7',if((`s`.`COMMITMENT_PERIOD_FLAG` = 2),_utf8'24 x 5',_utf8'Refer to \'Work with Services\' page')) AS `SERVICE_COMMITMENT`,`sc`.`COMPONENT_ID` AS `COMPONENT_ID`,`s`.`SERVICE_OWNING_BU` AS `SERVICE_OWNING_BU` from (`service_component_map_view` `scm` join (`services` `s` join `service_components` `sc`)) where ((`scm`.`SERVICE_NAME` = `s`.`SERVICE_NAME`) and (`scm`.`COMPONENT_NAME` = `sc`.`COMPONENT_NAME`)) order by `scm`.`SERVICE_NAME`;

--
-- Definition of view `disaster_recovery_view`
--

DROP TABLE IF EXISTS `disaster_recovery_view`;
DROP VIEW IF EXISTS `disaster_recovery_view`;
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`%` SQL SECURITY DEFINER VIEW `disaster_recovery_view` AS select `s`.`SERVICE_NAME` AS `SERVICE_NAME`,`dr`.`RTO` AS `RTO`,`dr`.`RPO` AS `RPO`,`dr`.`ACTIVE_ACTIVE` AS `ACTIVE_ACTIVE`,`dr`.`AA_DETAIL` AS `AA_DETAIL`,`l`.`LOCATION_NAME` AS `LOCATION_NAME`,`dr`.`LOCATION_ID` AS `LOCATION_ID`,`dr`.`RESTORATION_PRIORITY` AS `RESTORATION_PRIORITY`,`l2`.`LOCATION_NAME` AS `PRIM_LOC`,`l2`.`LOCATION_ID` AS `PRIM_LOC_ID`,`p`.`PLAN_NAME` AS `CERT_NAME`,`p2`.`PLAN_NAME` AS `PLAN_NAME`,`p2`.`PLAN_ID` AS `PLAN_ID`,`dr`.`CERT_ID` AS `CERT_ID`,`dr`.`TECHNICAL_CERTIFICATION` AS `TECHNICAL_CERTIFICATION`,`dr`.`COMMENTS` AS `COMMENTS`,`dr`.`DR_FLX_VCHR1` AS `THIRD_PARTY_HELD`,`p`.`PLAN_RTO` AS `CERT_RTO`,`p2`.`PLAN_RTO` AS `PLAN_RTO`,`s`.`SERVICE_ID` AS `SERVICE_ID` from ((((`services` `s` join (`disaster_recovery` `dr` left join `location` `l` on((`dr`.`LOCATION_ID` = `l`.`LOCATION_ID`)))) left join `location` `l2` on((`dr`.`PRIM_LOC` = `l2`.`LOCATION_ID`))) left join `plan_id` `p` on((`dr`.`CERT_ID` = `p`.`PLAN_ID`))) left join `plan_id` `p2` on((`dr`.`PLAN_ID` = `p2`.`PLAN_ID`))) where (`s`.`SERVICE_ID` = `dr`.`SERVICE_ID`) order by `s`.`SERVICE_NAME`;

--
-- Definition of view `location_view`
--

DROP TABLE IF EXISTS `location_view`;
DROP VIEW IF EXISTS `location_view`;
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`%` SQL SECURITY DEFINER VIEW `location_view` AS select `location`.`LOCATION_ID` AS `LOCATION_ID`,`location`.`LOCATION_NAME` AS `LOCATION_NAME`,if((`location`.`ACTIVE` = 1),_utf8'YES',_utf8'NO') AS `ACTIVE`,`location`.`REGION` AS `REGION` from `location` order by `location`.`LOCATION_NAME`;

--
-- Definition of view `plan_id_view`
--

DROP TABLE IF EXISTS `plan_id_view`;
DROP VIEW IF EXISTS `plan_id_view`;
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`%` SQL SECURITY DEFINER VIEW `plan_id_view` AS select `plan_id`.`PLAN_ID` AS `PLAN_ID`,`plan_id`.`PLAN_NAME` AS `PLAN_NAME`,`plan_id`.`PLAN_RTO` AS `PLAN_RTO`,`plan_id`.`DESCRIPTION` AS `DESCRIPTION` from `plan_id` order by `plan_id`.`PLAN_ID`;

--
-- Definition of view `service_component_map_view`
--

DROP TABLE IF EXISTS `service_component_map_view`;
DROP VIEW IF EXISTS `service_component_map_view`;
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`%` SQL SECURITY DEFINER VIEW `service_component_map_view` AS select `scm`.`SERVICE_ID` AS `SERVICE_ID`,`s`.`SERVICE_NAME` AS `SERVICE_NAME`,`scm`.`COMPONENT_ID` AS `COMPONENT_ID`,`sc`.`COMPONENT_NAME` AS `COMPONENT_NAME`,`scm`.`TYPE` AS `TYPE`,`scm`.`USED` AS `USED`,`sc`.`AVAILABILITY_OLO` AS `COMMITMENT`,`sc`.`CATEGORY_ID` AS `CATEGORY_ID`,`st`.`CLASS_NAME` AS `SUPPORT_CLASS_NAME`,`st`.`TEAM_NAME` AS `SUPPORT_TEAM_NAME` from (((`service_component_map` `scm` join `services` `s` on((`scm`.`SERVICE_ID` = `s`.`SERVICE_ID`))) join `service_components` `sc` on((`scm`.`COMPONENT_ID` = `sc`.`COMPONENT_ID`))) left join `support_teams` `st` on((`sc`.`SUPPORT_TEAM_ID` = `st`.`SUPPORT_TEAM_ID`))) order by `sc`.`COMPONENT_NAME`;

--
-- Definition of view `service_components_not_mapped_view`
--

DROP TABLE IF EXISTS `service_components_not_mapped_view`;
DROP VIEW IF EXISTS `service_components_not_mapped_view`;
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`%` SQL SECURITY DEFINER VIEW `service_components_not_mapped_view` AS select `s`.`SERVICE_ID` AS `SERVICE_ID`,`s`.`SERVICE_NAME` AS `SERVICE_NAME`,`sc`.`COMPONENT_ID` AS `COMPONENT_ID_NOT_USED`,`sc`.`COMPONENT_NAME` AS `COMPONENT_NAME_NOT_USED` from (`services` `s` join `service_components` `sc`) where not(exists(select `scm`.`COMPONENT_ID` AS `COMPONENT_ID` from `service_component_map` `scm` where ((`sc`.`COMPONENT_ID` = `scm`.`COMPONENT_ID`) and (`s`.`SERVICE_ID` = `scm`.`SERVICE_ID`))));

--
-- Definition of view `service_components_sorted`
--

DROP TABLE IF EXISTS `service_components_sorted`;
DROP VIEW IF EXISTS `service_components_sorted`;
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`%` SQL SECURITY DEFINER VIEW `service_components_sorted` AS select `sc`.`COMPONENT_ID` AS `COMPONENT_ID`,`sc`.`COMPONENT_NAME` AS `COMPONENT_NAME`,`sc`.`AVAILABILITY_OLO` AS `AVAILABILITY_OLO`,concat(`st`.`CLASS_NAME`,_latin1' - ',`st`.`TEAM_NAME`) AS `SUPPORT_TEAM`,`st`.`CLASS_OWNER` AS `COMPONENT_OWNER`,if((`c`.`ITEM` = _latin1''),concat(`c`.`SYSTEM`,_latin1'/',`c`.`COMPONENT`),concat(`c`.`SYSTEM`,_latin1'/',`c`.`COMPONENT`,_latin1'/',`c`.`ITEM`)) AS `COMPONENT_TYPE`,`sc`.`DESCRIPTION` AS `DESCRIPTION`,`sc`.`SCHEDULED_COMPONENT_DOWNTIME` AS `SCHEDULED_COMPONENT_DOWNTIME`,`sc`.`CATEGORY_ID` AS `CATEGORY_ID`,`sc`.`SUPPORT_TEAM_ID` AS `SUPPORT_TEAM_ID`,`l`.`LOCATION_NAME` AS `LOCATION_NAME`,`sc`.`LOCATION_ID` AS `LOCATION_ID`,`l2`.`LOCATION_NAME` AS `PRIM_LOC`,`l2`.`LOCATION_ID` AS `PRIM_LOC_ID`,`p`.`PLAN_NAME` AS `CERT_NAME`,`sc`.`GENERAL` AS `GENERAL`,`p2`.`PLAN_NAME` AS `PLAN_NAME`,`p2`.`PLAN_ID` AS `PLAN_ID`,`sc`.`CERT_ID` AS `CERT_ID`,`sc`.`DR_FLX_VCHR1` AS `THIRD_PARTY_HELD`,`p`.`PLAN_RTO` AS `CERT_RTO`,`p3`.`POLICY_ID` AS `POLICY_ID`,`p2`.`PLAN_RTO` AS `PLAN_RTO`,`st`.`CLASS_NAME` AS `SUPPORT_CLASS` from (((((((`service_components` `sc` left join `categories` `c` on((`sc`.`CATEGORY_ID` = `c`.`CATEGORY_ID`))) left join `support_teams` `st` on((`sc`.`SUPPORT_TEAM_ID` = `st`.`SUPPORT_TEAM_ID`))) left join `location` `l` on((`sc`.`LOCATION_ID` = `l`.`LOCATION_ID`))) left join `location` `l2` on((`sc`.`PRIM_LOC` = `l2`.`LOCATION_ID`))) left join `plan_id` `p` on((`sc`.`CERT_ID` = `p`.`PLAN_ID`))) left join `plan_id` `p2` on((`sc`.`PLAN_ID` = `p2`.`PLAN_ID`))) left join `storage_policy` `p3` on((`sc`.`STORAGE_ID` = `p3`.`STORAGE_ID`))) order by `sc`.`COMPONENT_NAME`;

--
-- Definition of view `service_periods_view`
--

DROP TABLE IF EXISTS `service_periods_view`;
DROP VIEW IF EXISTS `service_periods_view`;
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`%` SQL SECURITY DEFINER VIEW `service_periods_view` AS select `service_periods`.`SERVICE_ID` AS `SERVICE_ID`,`service_periods`.`PERIOD_TYPE` AS `PERIOD_TYPE`,`service_periods`.`START_TIME1` AS `START_TIME1`,`service_periods`.`END_TIME1` AS `END_TIME1`,`service_periods`.`START_TIME2` AS `START_TIME2`,`service_periods`.`END_TIME2` AS `END_TIME2`,`service_periods`.`DAY_OF_WEEK` AS `DAY_OF_WEEK`,`service_periods`.`PERIOD_ID` AS `PERIOD_ID` from `service_periods` order by `service_periods`.`SERVICE_ID`,`service_periods`.`PERIOD_TYPE`,`service_periods`.`START_TIME1`;

--
-- Definition of view `service_report_view`
--

DROP TABLE IF EXISTS `service_report_view`;
DROP VIEW IF EXISTS `service_report_view`;
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`%` SQL SECURITY DEFINER VIEW `service_report_view` AS select `s`.`SERVICE_ID` AS `SERVICE_ID`,`s`.`SERVICE_NAME` AS `SERVICE_NAME`,`s`.`SERVICE_OWNING_BU` AS `SERVICE_OWNING_BU`,`s`.`DESCRIPTION` AS `DESCRIPTION`,`s`.`SERVICE_DEPENDENCIES` AS `SERVICE_DEPENDENCIES`,`s`.`SERVICE_IMPACT_CODE` AS `SERVICE_IMPACT_CODE`,`s`.`LEGAL_REQUIREMENTS` AS `LEGAL_REQUIREMENTS`,`s`.`COMMITMENT_PERIOD_FLAG` AS `COMMITMENT_PERIOD_FLAG`,`s`.`SLC_MEASURES_FUNC` AS `SLC_MEASURES_FUNC`,`s`.`SLC_MEASURES_AVAIL` AS `SLC_MEASURES_AVAIL`,`s`.`DELIVERABLES` AS `DELIVERABLES`,`s`.`DELIVERABLE_EXCEMPTION` AS `DELIVERABLE_EXCEMPTION`,`s`.`MONITORING` AS `MONITORING`,`s`.`LIMITATIONS` AS `LIMITATIONS`,`s`.`SW_LVL_COMMITMENT` AS `SW_LVL_COMMITMENT`,`s`.`RESPONSIBILITIES` AS `RESPONSIBILITIES`,`s`.`STATUS` AS `STATUS`,`s`.`TIME_VALUES` AS `TIME_VALUES`,if(((select count(0) AS `count(*)` from `disaster_recovery` `dr` where (`dr`.`SERVICE_ID` = `s`.`SERVICE_ID`)) > 0),_latin1'YES',_latin1'NO') AS `HAS_DR_CAPABILITY`,if(isnull(`d`.`RESTORATION_PRIORITY`),_latin1'None Specified',`d`.`RESTORATION_PRIORITY`) AS `RESTORATION_PRIORITY`,if((`s`.`COMMITMENT_PERIOD_FLAG` = 1),_latin1'24 x 7',if((`s`.`COMMITMENT_PERIOD_FLAG` = 2),_latin1'24 x 5',_latin1'Other')) AS `SERVICE_COMMITMENT`,`d`.`ACTIVE_ACTIVE` AS `ACTIVE_ACTIVE`,`d`.`AA_DETAIL` AS `AA_DETAIL`,`d`.`LOCATION_ID` AS `LOCATION_ID`,`d`.`CERT_ID` AS `CERT_ID`,`d`.`PLAN_ID` AS `PLAN_ID`,`d`.`TECHNICAL_CERTIFICATION` AS `TECHNICAL_CERTIFICATION`,`d`.`DR_FLX_VCHR1` AS `THIRD_PART_HELD`,`d`.`RTO` AS `RTO`,`d`.`RPO` AS `RPO`,`d`.`COMMENTS` AS `COMMENTS` from (`services` `s` left join `disaster_recovery` `d` on((`s`.`SERVICE_ID` = `d`.`SERVICE_ID`))) order by `s`.`SERVICE_NAME`;

--
-- Definition of view `service_used_by_view`
--

DROP TABLE IF EXISTS `service_used_by_view`;
DROP VIEW IF EXISTS `service_used_by_view`;
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`%` SQL SECURITY DEFINER VIEW `service_used_by_view` AS select `sub`.`SERVICE_ID` AS `SERVICE_ID`,`sub`.`BUSINESS_ID` AS `BUSINESS_ID`,`s`.`SERVICE_NAME` AS `SERVICE_NAME`,`b`.`BUSINESS_NAME` AS `BUSINESS_NAME` from (`service_used_by` `sub` join (`services` `s` join `business_names` `b`)) where ((`sub`.`SERVICE_ID` = `s`.`SERVICE_ID`) and (`sub`.`BUSINESS_ID` = `b`.`BUSINESS_ID`));

--
-- Definition of view `service_view`
--

DROP TABLE IF EXISTS `service_view`;
DROP VIEW IF EXISTS `service_view`;
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`%` SQL SECURITY DEFINER VIEW `service_view` AS select `s`.`SERVICE_ID` AS `SERVICE_ID`,`s`.`SERVICE_NAME` AS `SERVICE_NAME`,`s`.`SERVICE_OWNING_BU` AS `SERVICE_OWNING_BU`,`s`.`DESCRIPTION` AS `DESCRIPTION`,`s`.`SERVICE_DEPENDENCIES` AS `SERVICE_DEPENDENCIES`,`s`.`SERVICE_IMPACT_CODE` AS `SERVICE_IMPACT_CODE`,`s`.`LEGAL_REQUIREMENTS` AS `LEGAL_REQUIREMENTS`,`s`.`COMMITMENT_PERIOD_FLAG` AS `COMMITMENT_PERIOD_FLAG`,`s`.`SLC_MEASURES_FUNC` AS `SLC_MEASURES_FUNC`,`s`.`SLC_MEASURES_AVAIL` AS `SLC_MEASURES_AVAIL`,`s`.`DELIVERABLES` AS `SPECIAL_EVENTS`,`s`.`DELIVERABLE_EXCEMPTION` AS `MAINTENANCE_WINDOW`,`s`.`MONITORING` AS `MONITORING`,`s`.`LIMITATIONS` AS `LIMITATIONS`,`s`.`SW_LVL_COMMITMENT` AS `SERVICE_COMMENTS`,`s`.`RESPONSIBILITIES` AS `RESPONSIBILITIES`,`s`.`STATUS` AS `STATUS`,`s`.`TIME_VALUES` AS `TIME_VALUES`,max(`a`.`AUDIT_ID`) AS `LAST_AUDIT_ID`,(select `audit_trail`.`USER_FULLNAME` AS `USER_FULLNAME` from `audit_trail` where (`audit_trail`.`AUDIT_ID` = (select max(`a2`.`AUDIT_ID`) AS `max(a2.audit_id)` from `audit_trail` `a2` where ((`a2`.`TABLE_NAME` = _latin1'SERVICES') and (`a2`.`TABLE_ROW_ID` = `s`.`SERVICE_ID`))))) AS `LAST_USER_TO_UPDATE`,max(`a`.`ACTION_TIME`) AS `LAST_UPDATE_DATE`,group_concat(`a`.`AUDIT_ID`,char(13,10) separator '') AS `AUDIT_ID_LIST`,group_concat(substr(`a`.`ACTION_TIME`,1,10),char(13,10) separator '') AS `ACTION_TIME_LIST`,group_concat(`a`.`USER_FULLNAME`,char(13,10) separator '') AS `USER_FULLNAME_LIST`,group_concat(`a`.`CHANGE_DESC`,char(13,10) separator '') AS `CHANGES_LIST`,if(((select count(0) AS `count(*)` from `disaster_recovery` `dr` where (`dr`.`SERVICE_ID` = `s`.`SERVICE_ID`)) > 0),_latin1'YES',_latin1'NO') AS `HAS_DR_CAPABILITY`,if(isnull(`d`.`RESTORATION_PRIORITY`),_latin1'None Specified',`d`.`RESTORATION_PRIORITY`) AS `RESTORATION_PRIORITY`,if((`s`.`COMMITMENT_PERIOD_FLAG` = 1),_latin1'24 x 7',if((`s`.`COMMITMENT_PERIOD_FLAG` = 2),_latin1'24 x 5',_latin1'Other')) AS `SERVICE_COMMITMENT`,(select group_concat(`sv`.`COMPONENT_NAME`,char(13,10) separator '') AS `GROUP_CONCAT(``sv``.``COMPONENT_NAME`` SEPARATOR ',')` from `service_component_map_view` `sv` where ((`sv`.`SERVICE_ID` = `s`.`SERVICE_ID`) and (`sv`.`CATEGORY_ID` = 1) and (`sv`.`TYPE` = _latin1'A')) group by `s`.`SERVICE_ID`) AS `A_MAINFRAME_INFRASTRUCTURE_COMPONENTS`,(select group_concat(`sv`.`COMMITMENT`,char(37,13,10) separator '') AS `GROUP_CONCAT(``sv``.``COMMITMENT`` SEPARATOR ',')` from `service_component_map_view` `sv` where ((`sv`.`SERVICE_ID` = `s`.`SERVICE_ID`) and (`sv`.`CATEGORY_ID` = 1) and (`sv`.`TYPE` = _latin1'A')) group by `s`.`SERVICE_ID`) AS `A_MAINFRAME_INFRASTRUCTURE_COMMITMENT`,(select group_concat(`sv`.`SUPPORT_CLASS_NAME`,char(13,10) separator '') AS `GROUP_CONCAT(``sv``.``SUPPORT_CLASS_NAME`` SEPARATOR ',')` from `service_component_map_view` `sv` where ((`sv`.`SERVICE_ID` = `s`.`SERVICE_ID`) and (`sv`.`CATEGORY_ID` = 1) and (`sv`.`TYPE` = _latin1'A')) group by `s`.`SERVICE_ID`) AS `A_MAINFRAME_INFRASTRUCTURE_SUPPORT_CLASSES`,(select group_concat(`sv`.`SUPPORT_TEAM_NAME`,char(13,10) separator '') AS `GROUP_CONCAT(``sv``.``SUPPORT_TEAM_NAME`` SEPARATOR ',')` from `service_component_map_view` `sv` where ((`sv`.`SERVICE_ID` = `s`.`SERVICE_ID`) and (`sv`.`CATEGORY_ID` = 1) and (`sv`.`TYPE` = _latin1'A')) group by `s`.`SERVICE_ID`) AS `A_MAINFRAME_INFRASTRUCTURE_SUPPORT_TEAMS`,(select group_concat(`sv`.`COMPONENT_NAME`,char(13,10) separator '') AS `GROUP_CONCAT(``sv``.``COMPONENT_NAME`` SEPARATOR ',')` from `service_component_map_view` `sv` where ((`sv`.`SERVICE_ID` = `s`.`SERVICE_ID`) and (`sv`.`CATEGORY_ID` = 2) and (`sv`.`TYPE` = _latin1'A')) group by `s`.`SERVICE_ID`) AS `A_MAINFRAME_LPAR_COMPONENTS`,(select group_concat(`sv`.`COMMITMENT`,char(37,13,10) separator '') AS `GROUP_CONCAT(``sv``.``COMMITMENT`` SEPARATOR ',')` from `service_component_map_view` `sv` where ((`sv`.`SERVICE_ID` = `s`.`SERVICE_ID`) and (`sv`.`CATEGORY_ID` = 2) and (`sv`.`TYPE` = _latin1'A')) group by `s`.`SERVICE_ID`) AS `A_MAINFRAME_LPAR_COMMITMENT`,(select group_concat(`sv`.`SUPPORT_CLASS_NAME`,char(13,10) separator '') AS `GROUP_CONCAT(``sv``.``SUPPORT_CLASS_NAME`` SEPARATOR ',')` from `service_component_map_view` `sv` where ((`sv`.`SERVICE_ID` = `s`.`SERVICE_ID`) and (`sv`.`CATEGORY_ID` = 2) and (`sv`.`TYPE` = _latin1'A')) group by `s`.`SERVICE_ID`) AS `A_MAINFRAME_LPAR_SUPPORT_CLASSES`,(select group_concat(`sv`.`SUPPORT_TEAM_NAME`,char(13,10) separator '') AS `GROUP_CONCAT(``sv``.``SUPPORT_TEAM_NAME`` SEPARATOR ',')` from `service_component_map_view` `sv` where ((`sv`.`SERVICE_ID` = `s`.`SERVICE_ID`) and (`sv`.`CATEGORY_ID` = 2) and (`sv`.`TYPE` = _latin1'A')) group by `s`.`SERVICE_ID`) AS `A_MAINFRAME_LPAR_SUPPORT_TEAMS`,(select group_concat(`sv`.`COMPONENT_NAME`,char(13,10) separator '') AS `GROUP_CONCAT(``sv``.``COMPONENT_NAME`` SEPARATOR ',')` from `service_component_map_view` `sv` where ((`sv`.`SERVICE_ID` = `s`.`SERVICE_ID`) and (`sv`.`CATEGORY_ID` = 3) and (`sv`.`TYPE` = _latin1'A')) group by `s`.`SERVICE_ID`) AS `A_MAINFRAME_SUBSYSTEM_COMPONENTS`,(select group_concat(`sv`.`COMMITMENT`,char(37,13,10) separator '') AS `GROUP_CONCAT(``sv``.``COMMITMENT`` SEPARATOR ',')` from `service_component_map_view` `sv` where ((`sv`.`SERVICE_ID` = `s`.`SERVICE_ID`) and (`sv`.`CATEGORY_ID` = 3) and (`sv`.`TYPE` = _latin1'A')) group by `s`.`SERVICE_ID`) AS `A_MAINFRAME_SUBSYSTEM_COMMITMENT`,(select group_concat(`sv`.`SUPPORT_CLASS_NAME`,char(13,10) separator '') AS `GROUP_CONCAT(``sv``.``SUPPORT_CLASS_NAME`` SEPARATOR ',')` from `service_component_map_view` `sv` where ((`sv`.`SERVICE_ID` = `s`.`SERVICE_ID`) and (`sv`.`CATEGORY_ID` = 3) and (`sv`.`TYPE` = _latin1'A')) group by `s`.`SERVICE_ID`) AS `A_MAINFRAME_SUBSYSTEM_SUPPORT_CLASSES`,(select group_concat(`sv`.`SUPPORT_TEAM_NAME`,char(13,10) separator '') AS `GROUP_CONCAT(``sv``.``SUPPORT_TEAM_NAME`` SEPARATOR ',')` from `service_component_map_view` `sv` where ((`sv`.`SERVICE_ID` = `s`.`SERVICE_ID`) and (`sv`.`CATEGORY_ID` = 3) and (`sv`.`TYPE` = _latin1'A')) group by `s`.`SERVICE_ID`) AS `A_MAINFRAME_SUBSYSTEM_SUPPORT_TEAMS`,(select group_concat(`sv`.`COMPONENT_NAME`,char(13,10) separator '') AS `GROUP_CONCAT(``sv``.``COMPONENT_NAME`` SEPARATOR ',')` from `service_component_map_view` `sv` where ((`sv`.`SERVICE_ID` = `s`.`SERVICE_ID`) and (`sv`.`CATEGORY_ID` = 4) and (`sv`.`TYPE` = _latin1'A')) group by `s`.`SERVICE_ID`) AS `A_MAINFRAME_REGION_COMPONENTS`,(select group_concat(`sv`.`COMMITMENT`,char(37,13,10) separator '') AS `GROUP_CONCAT(``sv``.``COMMITMENT`` SEPARATOR ',')` from `service_component_map_view` `sv` where ((`sv`.`SERVICE_ID` = `s`.`SERVICE_ID`) and (`sv`.`CATEGORY_ID` = 4) and (`sv`.`TYPE` = _latin1'A')) group by `s`.`SERVICE_ID`) AS `A_MAINFRAME_REGION_COMMITMENT`,(select group_concat(`sv`.`SUPPORT_CLASS_NAME`,char(13,10) separator '') AS `GROUP_CONCAT(``sv``.``SUPPORT_CLASS_NAME`` SEPARATOR ',')` from `service_component_map_view` `sv` where ((`sv`.`SERVICE_ID` = `s`.`SERVICE_ID`) and (`sv`.`CATEGORY_ID` = 4) and (`sv`.`TYPE` = _latin1'A')) group by `s`.`SERVICE_ID`) AS `A_MAINFRAME_REGION_SUPPORT_CLASSES`,(select group_concat(`sv`.`SUPPORT_TEAM_NAME`,char(13,10) separator '') AS `GROUP_CONCAT(``sv``.``SUPPORT_TEAM_NAME`` SEPARATOR ',')` from `service_component_map_view` `sv` where ((`sv`.`SERVICE_ID` = `s`.`SERVICE_ID`) and (`sv`.`CATEGORY_ID` = 4) and (`sv`.`TYPE` = _latin1'A')) group by `s`.`SERVICE_ID`) AS `A_MAINFRAME_REGION_SUPPORT_TEAMS`,(select group_concat(`sv`.`COMPONENT_NAME`,char(13,10) separator '') AS `GROUP_CONCAT(``sv``.``COMPONENT_NAME`` SEPARATOR ',')` from `service_component_map_view` `sv` where ((`sv`.`SERVICE_ID` = `s`.`SERVICE_ID`) and (`sv`.`CATEGORY_ID` = 5) and (`sv`.`TYPE` = _latin1'A')) group by `s`.`SERVICE_ID`) AS `A_DISTRIBUTED_INFRASTRUCTURE_COMPONENTS`,(select group_concat(`sv`.`COMMITMENT`,char(37,13,10) separator '') AS `GROUP_CONCAT(``sv``.``COMMITMENT`` SEPARATOR ',')` from `service_component_map_view` `sv` where ((`sv`.`SERVICE_ID` = `s`.`SERVICE_ID`) and (`sv`.`CATEGORY_ID` = 5) and (`sv`.`TYPE` = _latin1'A')) group by `s`.`SERVICE_ID`) AS `A_DISTRIBUTED_INFRASTRUCTURE_COMMITMENT`,(select group_concat(`sv`.`SUPPORT_CLASS_NAME`,char(13,10) separator '') AS `GROUP_CONCAT(``sv``.``SUPPORT_CLASS_NAME`` SEPARATOR ',')` from `service_component_map_view` `sv` where ((`sv`.`SERVICE_ID` = `s`.`SERVICE_ID`) and (`sv`.`CATEGORY_ID` = 5) and (`sv`.`TYPE` = _latin1'A')) group by `s`.`SERVICE_ID`) AS `A_DISTRIBUTED_INFRASTRUCTURE_SUPPORT_CLASSES`,(select group_concat(`sv`.`SUPPORT_TEAM_NAME`,char(13,10) separator '') AS `GROUP_CONCAT(``sv``.``SUPPORT_TEAM_NAME`` SEPARATOR ',')` from `service_component_map_view` `sv` where ((`sv`.`SERVICE_ID` = `s`.`SERVICE_ID`) and (`sv`.`CATEGORY_ID` = 5) and (`sv`.`TYPE` = _latin1'A')) group by `s`.`SERVICE_ID`) AS `A_DISTRIBUTED_INFRASTRUCTURE_SUPPORT_TEAMS`,(select group_concat(`sv`.`COMPONENT_NAME`,char(13,10) separator '') AS `GROUP_CONCAT(``sv``.``COMPONENT_NAME`` SEPARATOR ',')` from `service_component_map_view` `sv` where ((`sv`.`SERVICE_ID` = `s`.`SERVICE_ID`) and (`sv`.`CATEGORY_ID` = 6) and (`sv`.`TYPE` = _latin1'A')) group by `s`.`SERVICE_ID`) AS `A_DISTRIBUTED_APPLICATION_SW_COMPONENTS`,(select group_concat(`sv`.`COMMITMENT`,char(37,13,10) separator '') AS `GROUP_CONCAT(``sv``.``COMMITMENT`` SEPARATOR ',')` from `service_component_map_view` `sv` where ((`sv`.`SERVICE_ID` = `s`.`SERVICE_ID`) and (`sv`.`CATEGORY_ID` = 6) and (`sv`.`TYPE` = _latin1'A')) group by `s`.`SERVICE_ID`) AS `A_DISTRIBUTED_APPLICATION_SW_COMMITMENT`,(select group_concat(`sv`.`SUPPORT_CLASS_NAME`,char(13,10) separator '') AS `GROUP_CONCAT(``sv``.``SUPPORT_CLASS_NAME`` SEPARATOR ',')` from `service_component_map_view` `sv` where ((`sv`.`SERVICE_ID` = `s`.`SERVICE_ID`) and (`sv`.`CATEGORY_ID` = 6) and (`sv`.`TYPE` = _latin1'A')) group by `s`.`SERVICE_ID`) AS `A_DISTRIBUTED_APPLICATION_SW_SUPPORT_CLASSES`,(select group_concat(`sv`.`SUPPORT_TEAM_NAME`,char(13,10) separator '') AS `GROUP_CONCAT(``sv``.``SUPPORT_TEAM_NAME`` SEPARATOR ',')` from `service_component_map_view` `sv` where ((`sv`.`SERVICE_ID` = `s`.`SERVICE_ID`) and (`sv`.`CATEGORY_ID` = 6) and (`sv`.`TYPE` = _latin1'A')) group by `s`.`SERVICE_ID`) AS `A_DISTRIBUTED_APPLICATION_SW_SUPPORT_TEAMS`,(select group_concat(`sv`.`COMPONENT_NAME`,char(13,10) separator '') AS `GROUP_CONCAT(``sv``.``COMPONENT_NAME`` SEPARATOR ',')` from `service_component_map_view` `sv` where ((`sv`.`SERVICE_ID` = `s`.`SERVICE_ID`) and (`sv`.`CATEGORY_ID` = 9) and (`sv`.`TYPE` = _latin1'A')) group by `s`.`SERVICE_ID`) AS `A_PRODUCT_REQ_PRODUCT_COMPONENTS`,(select group_concat(`sv`.`COMMITMENT`,char(37,13,10) separator '') AS `GROUP_CONCAT(``sv``.``COMMITMENT`` SEPARATOR ',')` from `service_component_map_view` `sv` where ((`sv`.`SERVICE_ID` = `s`.`SERVICE_ID`) and (`sv`.`CATEGORY_ID` = 9) and (`sv`.`TYPE` = _latin1'A')) group by `s`.`SERVICE_ID`) AS `A_PRODUCT_REQ_PRODUCT_COMMITMENT`,(select group_concat(`sv`.`SUPPORT_CLASS_NAME`,char(13,10) separator '') AS `GROUP_CONCAT(``sv``.``SUPPORT_CLASS_NAME`` SEPARATOR ',')` from `service_component_map_view` `sv` where ((`sv`.`SERVICE_ID` = `s`.`SERVICE_ID`) and (`sv`.`CATEGORY_ID` = 9) and (`sv`.`TYPE` = _latin1'A')) group by `s`.`SERVICE_ID`) AS `A_PRODUCT_REQ_PRODUCT_SUPPORT_CLASSES`,(select group_concat(`sv`.`SUPPORT_TEAM_NAME`,char(13,10) separator '') AS `GROUP_CONCAT(``sv``.``SUPPORT_TEAM_NAME`` SEPARATOR ',')` from `service_component_map_view` `sv` where ((`sv`.`SERVICE_ID` = `s`.`SERVICE_ID`) and (`sv`.`CATEGORY_ID` = 9) and (`sv`.`TYPE` = _latin1'A')) group by `s`.`SERVICE_ID`) AS `A_PRODUCT_REQ_PRODUCT_SUPPORT_TEAMS`,(select group_concat(`sv`.`COMPONENT_NAME`,char(13,10) separator '') AS `GROUP_CONCAT(``sv``.``COMPONENT_NAME`` SEPARATOR ',')` from `service_component_map_view` `sv` where ((`sv`.`SERVICE_ID` = `s`.`SERVICE_ID`) and (`sv`.`CATEGORY_ID` = 10) and (`sv`.`TYPE` = _latin1'A')) group by `s`.`SERVICE_ID`) AS `A_DISTRIBUTED_LPAR_COMPONENTS`,(select group_concat(`sv`.`COMMITMENT`,char(37,13,10) separator '') AS `GROUP_CONCAT(``sv``.``COMMITMENT`` SEPARATOR ',')` from `service_component_map_view` `sv` where ((`sv`.`SERVICE_ID` = `s`.`SERVICE_ID`) and (`sv`.`CATEGORY_ID` = 10) and (`sv`.`TYPE` = _latin1'A')) group by `s`.`SERVICE_ID`) AS `A_DISTRIBUTED_LPAR_COMMITMENT`,(select group_concat(`sv`.`SUPPORT_CLASS_NAME`,char(13,10) separator '') AS `GROUP_CONCAT(``sv``.``SUPPORT_CLASS_NAME`` SEPARATOR ',')` from `service_component_map_view` `sv` where ((`sv`.`SERVICE_ID` = `s`.`SERVICE_ID`) and (`sv`.`CATEGORY_ID` = 10) and (`sv`.`TYPE` = _latin1'A')) group by `s`.`SERVICE_ID`) AS `A_DISTRIBUTED_LPAR_SUPPORT_CLASSES`,(select group_concat(`sv`.`SUPPORT_TEAM_NAME`,char(13,10) separator '') AS `GROUP_CONCAT(``sv``.``SUPPORT_TEAM_NAME`` SEPARATOR ',')` from `service_component_map_view` `sv` where ((`sv`.`SERVICE_ID` = `s`.`SERVICE_ID`) and (`sv`.`CATEGORY_ID` = 10) and (`sv`.`TYPE` = _latin1'A')) group by `s`.`SERVICE_ID`) AS `A_DISTRIBUTED_LPAR_SUPPORT_TEAMS`,(select group_concat(`sv`.`COMPONENT_NAME`,char(13,10) separator '') AS `GROUP_CONCAT(``sv``.``COMPONENT_NAME`` SEPARATOR ',')` from `service_component_map_view` `sv` where ((`sv`.`SERVICE_ID` = `s`.`SERVICE_ID`) and (`sv`.`CATEGORY_ID` = 11) and (`sv`.`TYPE` = _latin1'A')) group by `s`.`SERVICE_ID`) AS `A_TELSTRA_UC_COMPONENTS`,(select group_concat(`sv`.`COMMITMENT`,char(37,13,10) separator '') AS `GROUP_CONCAT(``sv``.``COMMITMENT`` SEPARATOR ',')` from `service_component_map_view` `sv` where ((`sv`.`SERVICE_ID` = `s`.`SERVICE_ID`) and (`sv`.`CATEGORY_ID` = 11) and (`sv`.`TYPE` = _latin1'A')) group by `s`.`SERVICE_ID`) AS `A_TELSTRA_UC_COMMITMENTS`,(select group_concat(`sv`.`SUPPORT_CLASS_NAME`,char(13,10) separator '') AS `GROUP_CONCAT(``sv``.``SUPPORT_CLASS_NAME`` SEPARATOR ',')` from `service_component_map_view` `sv` where ((`sv`.`SERVICE_ID` = `s`.`SERVICE_ID`) and (`sv`.`CATEGORY_ID` = 11) and (`sv`.`TYPE` = _latin1'A')) group by `s`.`SERVICE_ID`) AS `A_TELSTRA_UC_SUPPORT_CLASSES`,(select group_concat(`sv`.`SUPPORT_TEAM_NAME`,char(13,10) separator '') AS `GROUP_CONCAT(``sv``.``SUPPORT_TEAM_NAME`` SEPARATOR ',')` from `service_component_map_view` `sv` where ((`sv`.`SERVICE_ID` = `s`.`SERVICE_ID`) and (`sv`.`CATEGORY_ID` = 11) and (`sv`.`TYPE` = _latin1'A')) group by `s`.`SERVICE_ID`) AS `A_TELSTRA_UC_SUPPORT_TEAMS`,(select group_concat(`sv`.`COMPONENT_NAME`,char(13,10) separator '') AS `GROUP_CONCAT(``sv``.``COMPONENT_NAME`` SEPARATOR ',')` from `service_component_map_view` `sv` where ((`sv`.`SERVICE_ID` = `s`.`SERVICE_ID`) and (`sv`.`CATEGORY_ID` = 1) and (`sv`.`TYPE` = _latin1'F')) group by `s`.`SERVICE_ID`) AS `F_MAINFRAME_INFRASTRUCTURE_COMPONENTS`,(select group_concat(`sv`.`COMMITMENT`,char(37,13,10) separator '') AS `GROUP_CONCAT(``sv``.``COMMITMENT`` SEPARATOR ',')` from `service_component_map_view` `sv` where ((`sv`.`SERVICE_ID` = `s`.`SERVICE_ID`) and (`sv`.`CATEGORY_ID` = 1) and (`sv`.`TYPE` = _latin1'F')) group by `s`.`SERVICE_ID`) AS `F_MAINFRAME_INFRASTRUCTURE_COMMITMENT`,(select group_concat(`sv`.`SUPPORT_CLASS_NAME`,char(13,10) separator '') AS `GROUP_CONCAT(``sv``.``SUPPORT_CLASS_NAME`` SEPARATOR ',')` from `service_component_map_view` `sv` where ((`sv`.`SERVICE_ID` = `s`.`SERVICE_ID`) and (`sv`.`CATEGORY_ID` = 1) and (`sv`.`TYPE` = _latin1'F')) group by `s`.`SERVICE_ID`) AS `F_MAINFRAME_INFRASTRUCTURE_SUPPORT_CLASSES`,(select group_concat(`sv`.`SUPPORT_TEAM_NAME`,char(13,10) separator '') AS `GROUP_CONCAT(``sv``.``SUPPORT_TEAM_NAME`` SEPARATOR ',')` from `service_component_map_view` `sv` where ((`sv`.`SERVICE_ID` = `s`.`SERVICE_ID`) and (`sv`.`CATEGORY_ID` = 1) and (`sv`.`TYPE` = _latin1'F')) group by `s`.`SERVICE_ID`) AS `F_MAINFRAME_INFRASTRUCTURE_SUPPORT_TEAMS`,(select group_concat(`sv`.`COMPONENT_NAME`,char(13,10) separator '') AS `GROUP_CONCAT(``sv``.``COMPONENT_NAME`` SEPARATOR ',')` from `service_component_map_view` `sv` where ((`sv`.`SERVICE_ID` = `s`.`SERVICE_ID`) and (`sv`.`CATEGORY_ID` = 2) and (`sv`.`TYPE` = _latin1'F')) group by `s`.`SERVICE_ID`) AS `F_MAINFRAME_LPAR_COMPONENTS`,(select group_concat(`sv`.`COMMITMENT`,char(37,13,10) separator '') AS `GROUP_CONCAT(``sv``.``COMMITMENT`` SEPARATOR ',')` from `service_component_map_view` `sv` where ((`sv`.`SERVICE_ID` = `s`.`SERVICE_ID`) and (`sv`.`CATEGORY_ID` = 2) and (`sv`.`TYPE` = _latin1'F')) group by `s`.`SERVICE_ID`) AS `F_MAINFRAME_LPAR_COMMITMENT`,(select group_concat(`sv`.`SUPPORT_CLASS_NAME`,char(13,10) separator '') AS `GROUP_CONCAT(``sv``.``SUPPORT_CLASS_NAME`` SEPARATOR ',')` from `service_component_map_view` `sv` where ((`sv`.`SERVICE_ID` = `s`.`SERVICE_ID`) and (`sv`.`CATEGORY_ID` = 2) and (`sv`.`TYPE` = _latin1'F')) group by `s`.`SERVICE_ID`) AS `F_MAINFRAME_LPAR_SUPPORT_CLASSES`,(select group_concat(`sv`.`SUPPORT_TEAM_NAME`,char(13,10) separator '') AS `GROUP_CONCAT(``sv``.``SUPPORT_TEAM_NAME`` SEPARATOR ',')` from `service_component_map_view` `sv` where ((`sv`.`SERVICE_ID` = `s`.`SERVICE_ID`) and (`sv`.`CATEGORY_ID` = 2) and (`sv`.`TYPE` = _latin1'F')) group by `s`.`SERVICE_ID`) AS `F_MAINFRAME_LPAR_SUPPORT_TEAMS`,(select group_concat(`sv`.`COMPONENT_NAME`,char(13,10) separator '') AS `GROUP_CONCAT(``sv``.``COMPONENT_NAME`` SEPARATOR ',')` from `service_component_map_view` `sv` where ((`sv`.`SERVICE_ID` = `s`.`SERVICE_ID`) and (`sv`.`CATEGORY_ID` = 3) and (`sv`.`TYPE` = _latin1'F')) group by `s`.`SERVICE_ID`) AS `F_MAINFRAME_SUBSYSTEM_COMPONENTS`,(select group_concat(`sv`.`COMMITMENT`,char(37,13,10) separator '') AS `GROUP_CONCAT(``sv``.``COMMITMENT`` SEPARATOR ',')` from `service_component_map_view` `sv` where ((`sv`.`SERVICE_ID` = `s`.`SERVICE_ID`) and (`sv`.`CATEGORY_ID` = 3) and (`sv`.`TYPE` = _latin1'F')) group by `s`.`SERVICE_ID`) AS `F_MAINFRAME_SUBSYSTEM_COMMITMENT`,(select group_concat(`sv`.`SUPPORT_CLASS_NAME`,char(13,10) separator '') AS `GROUP_CONCAT(``sv``.``SUPPORT_CLASS_NAME`` SEPARATOR ',')` from `service_component_map_view` `sv` where ((`sv`.`SERVICE_ID` = `s`.`SERVICE_ID`) and (`sv`.`CATEGORY_ID` = 3) and (`sv`.`TYPE` = _latin1'F')) group by `s`.`SERVICE_ID`) AS `F_MAINFRAME_SUBSYSTEM_SUPPORT_CLASSES`,(select group_concat(`sv`.`SUPPORT_TEAM_NAME`,char(13,10) separator '') AS `GROUP_CONCAT(``sv``.``SUPPORT_TEAM_NAME`` SEPARATOR ',')` from `service_component_map_view` `sv` where ((`sv`.`SERVICE_ID` = `s`.`SERVICE_ID`) and (`sv`.`CATEGORY_ID` = 3) and (`sv`.`TYPE` = _latin1'F')) group by `s`.`SERVICE_ID`) AS `F_MAINFRAME_SUBSYSTEM_SUPPORT_TEAMS`,(select group_concat(`sv`.`COMPONENT_NAME`,char(13,10) separator '') AS `GROUP_CONCAT(``sv``.``COMPONENT_NAME`` SEPARATOR ',')` from `service_component_map_view` `sv` where ((`sv`.`SERVICE_ID` = `s`.`SERVICE_ID`) and (`sv`.`CATEGORY_ID` = 4) and (`sv`.`TYPE` = _latin1'F')) group by `s`.`SERVICE_ID`) AS `F_MAINFRAME_REGION_COMPONENTS`,(select group_concat(`sv`.`COMMITMENT`,char(37,13,10) separator '') AS `GROUP_CONCAT(``sv``.``COMMITMENT`` SEPARATOR ',')` from `service_component_map_view` `sv` where ((`sv`.`SERVICE_ID` = `s`.`SERVICE_ID`) and (`sv`.`CATEGORY_ID` = 4) and (`sv`.`TYPE` = _latin1'F')) group by `s`.`SERVICE_ID`) AS `F_MAINFRAME_REGION_COMMITMENT`,(select group_concat(`sv`.`SUPPORT_CLASS_NAME`,char(13,10) separator '') AS `GROUP_CONCAT(``sv``.``SUPPORT_CLASS_NAME`` SEPARATOR ',')` from `service_component_map_view` `sv` where ((`sv`.`SERVICE_ID` = `s`.`SERVICE_ID`) and (`sv`.`CATEGORY_ID` = 4) and (`sv`.`TYPE` = _latin1'F')) group by `s`.`SERVICE_ID`) AS `F_MAINFRAME_REGION_SUPPORT_CLASSES`,(select group_concat(`sv`.`SUPPORT_TEAM_NAME`,char(13,10) separator '') AS `GROUP_CONCAT(``sv``.``SUPPORT_TEAM_NAME`` SEPARATOR ',')` from `service_component_map_view` `sv` where ((`sv`.`SERVICE_ID` = `s`.`SERVICE_ID`) and (`sv`.`CATEGORY_ID` = 4) and (`sv`.`TYPE` = _latin1'F')) group by `s`.`SERVICE_ID`) AS `F_MAINFRAME_REGION_SUPPORT_TEAMS`,(select group_concat(`sv`.`COMPONENT_NAME`,char(13,10) separator '') AS `GROUP_CONCAT(``sv``.``COMPONENT_NAME`` SEPARATOR ',')` from `service_component_map_view` `sv` where ((`sv`.`SERVICE_ID` = `s`.`SERVICE_ID`) and (`sv`.`CATEGORY_ID` = 5) and (`sv`.`TYPE` = _latin1'F')) group by `s`.`SERVICE_ID`) AS `F_DISTRIBUTED_INFRASTRUCTURE_COMPONENTS`,(select group_concat(`sv`.`COMMITMENT`,char(37,13,10) separator '') AS `GROUP_CONCAT(``sv``.``COMMITMENT`` SEPARATOR ',')` from `service_component_map_view` `sv` where ((`sv`.`SERVICE_ID` = `s`.`SERVICE_ID`) and (`sv`.`CATEGORY_ID` = 5) and (`sv`.`TYPE` = _latin1'F')) group by `s`.`SERVICE_ID`) AS `F_DISTRIBUTED_INFRASTRUCTURE_COMMITMENT`,(select group_concat(`sv`.`SUPPORT_CLASS_NAME`,char(13,10) separator '') AS `GROUP_CONCAT(``sv``.``SUPPORT_CLASS_NAME`` SEPARATOR ',')` from `service_component_map_view` `sv` where ((`sv`.`SERVICE_ID` = `s`.`SERVICE_ID`) and (`sv`.`CATEGORY_ID` = 5) and (`sv`.`TYPE` = _latin1'F')) group by `s`.`SERVICE_ID`) AS `F_DISTRIBUTED_INFRASTRUCTURE_SUPPORT_CLASSES`,(select group_concat(`sv`.`SUPPORT_TEAM_NAME`,char(13,10) separator '') AS `GROUP_CONCAT(``sv``.``SUPPORT_TEAM_NAME`` SEPARATOR ',')` from `service_component_map_view` `sv` where ((`sv`.`SERVICE_ID` = `s`.`SERVICE_ID`) and (`sv`.`CATEGORY_ID` = 5) and (`sv`.`TYPE` = _latin1'F')) group by `s`.`SERVICE_ID`) AS `F_DISTRIBUTED_INFRASTRUCTURE_SUPPORT_TEAMS`,(select group_concat(`sv`.`COMPONENT_NAME`,char(13,10) separator '') AS `GROUP_CONCAT(``sv``.``COMPONENT_NAME`` SEPARATOR ',')` from `service_component_map_view` `sv` where ((`sv`.`SERVICE_ID` = `s`.`SERVICE_ID`) and (`sv`.`CATEGORY_ID` = 6) and (`sv`.`TYPE` = _latin1'F')) group by `s`.`SERVICE_ID`) AS `F_DISTRIBUTED_APPLICATION_SW_COMPONENTS`,(select group_concat(`sv`.`COMMITMENT`,char(37,13,10) separator '') AS `GROUP_CONCAT(``sv``.``COMMITMENT`` SEPARATOR ',')` from `service_component_map_view` `sv` where ((`sv`.`SERVICE_ID` = `s`.`SERVICE_ID`) and (`sv`.`CATEGORY_ID` = 6) and (`sv`.`TYPE` = _latin1'F')) group by `s`.`SERVICE_ID`) AS `F_DISTRIBUTED_APPLICATION_SW_COMMITMENT`,(select group_concat(`sv`.`SUPPORT_CLASS_NAME`,char(13,10) separator '') AS `GROUP_CONCAT(``sv``.``SUPPORT_CLASS_NAME`` SEPARATOR ',')` from `service_component_map_view` `sv` where ((`sv`.`SERVICE_ID` = `s`.`SERVICE_ID`) and (`sv`.`CATEGORY_ID` = 6) and (`sv`.`TYPE` = _latin1'F')) group by `s`.`SERVICE_ID`) AS `F_DISTRIBUTED_APPLICATION_SW_SUPPORT_CLASSES`,(select group_concat(`sv`.`SUPPORT_TEAM_NAME`,char(13,10) separator '') AS `GROUP_CONCAT(``sv``.``SUPPORT_TEAM_NAME`` SEPARATOR ',')` from `service_component_map_view` `sv` where ((`sv`.`SERVICE_ID` = `s`.`SERVICE_ID`) and (`sv`.`CATEGORY_ID` = 6) and (`sv`.`TYPE` = _latin1'F')) group by `s`.`SERVICE_ID`) AS `F_DISTRIBUTED_APPLICATION_SW_SUPPORT_TEAMS`,(select group_concat(`sv`.`COMPONENT_NAME`,char(13,10) separator '') AS `GROUP_CONCAT(``sv``.``COMPONENT_NAME`` SEPARATOR ',')` from `service_component_map_view` `sv` where ((`sv`.`SERVICE_ID` = `s`.`SERVICE_ID`) and (`sv`.`CATEGORY_ID` = 9) and (`sv`.`TYPE` = _latin1'F')) group by `s`.`SERVICE_ID`) AS `F_PRODUCT_REQ_PRODUCT_COMPONENTS`,(select group_concat(`sv`.`COMMITMENT`,char(37,13,10) separator '') AS `GROUP_CONCAT(``sv``.``COMMITMENT`` SEPARATOR ',')` from `service_component_map_view` `sv` where ((`sv`.`SERVICE_ID` = `s`.`SERVICE_ID`) and (`sv`.`CATEGORY_ID` = 9) and (`sv`.`TYPE` = _latin1'F')) group by `s`.`SERVICE_ID`) AS `F_PRODUCT_REQ_PRODUCT_COMMITMENT`,(select group_concat(`sv`.`SUPPORT_CLASS_NAME`,char(13,10) separator '') AS `GROUP_CONCAT(``sv``.``SUPPORT_CLASS_NAME`` SEPARATOR ',')` from `service_component_map_view` `sv` where ((`sv`.`SERVICE_ID` = `s`.`SERVICE_ID`) and (`sv`.`CATEGORY_ID` = 9) and (`sv`.`TYPE` = _latin1'F')) group by `s`.`SERVICE_ID`) AS `F_PRODUCT_REQ_PRODUCT_SUPPORT_CLASSES`,(select group_concat(`sv`.`SUPPORT_TEAM_NAME`,char(13,10) separator '') AS `GROUP_CONCAT(``sv``.``SUPPORT_TEAM_NAME`` SEPARATOR ',')` from `service_component_map_view` `sv` where ((`sv`.`SERVICE_ID` = `s`.`SERVICE_ID`) and (`sv`.`CATEGORY_ID` = 9) and (`sv`.`TYPE` = _latin1'F')) group by `s`.`SERVICE_ID`) AS `F_PRODUCT_REQ_PRODUCT_SUPPORT_TEAMS`,(select group_concat(`sv`.`COMPONENT_NAME`,char(13,10) separator '') AS `GROUP_CONCAT(``sv``.``COMPONENT_NAME`` SEPARATOR ',')` from `service_component_map_view` `sv` where ((`sv`.`SERVICE_ID` = `s`.`SERVICE_ID`) and (`sv`.`CATEGORY_ID` = 10) and (`sv`.`TYPE` = _latin1'F')) group by `s`.`SERVICE_ID`) AS `F_DISTRIBUTED_LPAR_COMPONENTS`,(select group_concat(`sv`.`COMMITMENT`,char(37,13,10) separator '') AS `GROUP_CONCAT(``sv``.``COMMITMENT`` SEPARATOR ',')` from `service_component_map_view` `sv` where ((`sv`.`SERVICE_ID` = `s`.`SERVICE_ID`) and (`sv`.`CATEGORY_ID` = 10) and (`sv`.`TYPE` = _latin1'F')) group by `s`.`SERVICE_ID`) AS `F_DISTRIBUTED_LPAR_COMMITMENT`,(select group_concat(`sv`.`SUPPORT_CLASS_NAME`,char(13,10) separator '') AS `GROUP_CONCAT(``sv``.``SUPPORT_CLASS_NAME`` SEPARATOR ',')` from `service_component_map_view` `sv` where ((`sv`.`SERVICE_ID` = `s`.`SERVICE_ID`) and (`sv`.`CATEGORY_ID` = 10) and (`sv`.`TYPE` = _latin1'F')) group by `s`.`SERVICE_ID`) AS `F_DISTRIBUTED_LPAR_SUPPORT_CLASSES`,(select group_concat(`sv`.`SUPPORT_TEAM_NAME`,char(13,10) separator '') AS `GROUP_CONCAT(``sv``.``SUPPORT_TEAM_NAME`` SEPARATOR ',')` from `service_component_map_view` `sv` where ((`sv`.`SERVICE_ID` = `s`.`SERVICE_ID`) and (`sv`.`CATEGORY_ID` = 10) and (`sv`.`TYPE` = _latin1'F')) group by `s`.`SERVICE_ID`) AS `F_DISTRIBUTED_LPAR_SUPPORT_TEAMS`,(select group_concat(`sv`.`COMPONENT_NAME`,char(13,10) separator '') AS `GROUP_CONCAT(``sv``.``COMPONENT_NAME`` SEPARATOR ',')` from `service_component_map_view` `sv` where ((`sv`.`SERVICE_ID` = `s`.`SERVICE_ID`) and (`sv`.`CATEGORY_ID` = 11) and (`sv`.`TYPE` = _latin1'F')) group by `s`.`SERVICE_ID`) AS `F_TELSTRA_UC_COMPONENTS`,(select group_concat(`sv`.`COMMITMENT`,char(37,13,10) separator '') AS `GROUP_CONCAT(``sv``.``COMMITMENT`` SEPARATOR ',')` from `service_component_map_view` `sv` where ((`sv`.`SERVICE_ID` = `s`.`SERVICE_ID`) and (`sv`.`CATEGORY_ID` = 11) and (`sv`.`TYPE` = _latin1'F')) group by `s`.`SERVICE_ID`) AS `F_TELSTRA_UC_COMMITMENTS`,(select group_concat(`sv`.`SUPPORT_CLASS_NAME`,char(13,10) separator '') AS `GROUP_CONCAT(``sv``.``SUPPORT_CLASS_NAME`` SEPARATOR ',')` from `service_component_map_view` `sv` where ((`sv`.`SERVICE_ID` = `s`.`SERVICE_ID`) and (`sv`.`CATEGORY_ID` = 11) and (`sv`.`TYPE` = _latin1'F')) group by `s`.`SERVICE_ID`) AS `F_TELSTRA_UC_SUPPORT_CLASSES`,(select group_concat(`sv`.`SUPPORT_TEAM_NAME`,char(13,10) separator '') AS `GROUP_CONCAT(``sv``.``SUPPORT_TEAM_NAME`` SEPARATOR ',')` from `service_component_map_view` `sv` where ((`sv`.`SERVICE_ID` = `s`.`SERVICE_ID`) and (`sv`.`CATEGORY_ID` = 11) and (`sv`.`TYPE` = _latin1'F')) group by `s`.`SERVICE_ID`) AS `F_TELSTRA_UC_SUPPORT_TEAMS`,(select group_concat(`sub`.`BUSINESS_NAME`,char(13,10) separator '') AS `group_concat(sub.business_name)` from `service_used_by_view` `sub` where (`sub`.`SERVICE_ID` = `s`.`SERVICE_ID`) group by `s`.`SERVICE_ID`) AS `BU_USERS_OF`,(select group_concat(concat_ws(_latin1' ',`sp`.`START_TIME1`,`sp`.`END_TIME1`,`sp`.`START_TIME2`,`sp`.`END_TIME2`,`sp`.`DAY_OF_WEEK`),char(13,10) separator '') AS `group_concat(concat_ws(' ', sp.start_time1, sp.end_time1, sp.start_time2, sp.end_time2, sp.day_of_week), CHAR(13,10))` from `service_periods` `sp` where ((`sp`.`PERIOD_TYPE` = _latin1'Critical Period') and (`sp`.`SERVICE_ID` = `s`.`SERVICE_ID`)) group by `s`.`SERVICE_ID`) AS `CRITICAL_PERIODS`,(select group_concat(concat_ws(_latin1' ',`sp`.`START_TIME1`,`sp`.`END_TIME1`,`sp`.`START_TIME2`,`sp`.`END_TIME2`,`sp`.`DAY_OF_WEEK`),char(13,10) separator '') AS `group_concat(concat_ws(' ', sp.start_time1, sp.end_time1, sp.start_time2, sp.end_time2, sp.day_of_week), CHAR(13,10))` from `service_periods` `sp` where ((`sp`.`PERIOD_TYPE` = _latin1'Maintenance Window') and (`sp`.`SERVICE_ID` = `s`.`SERVICE_ID`)) group by `s`.`SERVICE_ID`) AS `MAINTENANCE_PERIODS`,(select group_concat(concat_ws(_latin1' ',`sp`.`START_TIME1`,`sp`.`END_TIME1`,`sp`.`START_TIME2`,`sp`.`END_TIME2`,`sp`.`DAY_OF_WEEK`),char(13,10) separator '') AS `group_concat(concat_ws(' ', sp.start_time1, sp.end_time1, sp.start_time2, sp.end_time2, sp.day_of_week), CHAR(13,10))` from `service_periods` `sp` where ((`sp`.`PERIOD_TYPE` = _latin1'Commitment Period') and (`sp`.`SERVICE_ID` = `s`.`SERVICE_ID`)) group by `s`.`SERVICE_ID`) AS `COMMITMENT_PERIODS`,(select group_concat(`bv`.`COMPONENT_NAME`,char(13,10) separator '') AS `GROUP_CONCAT(``bv``.``COMPONENT_NAME`` SEPARATOR ',')` from `batch_component_map_view` `bv` where (`bv`.`SERVICE_ID` = `s`.`SERVICE_ID`) group by `s`.`SERVICE_ID`) AS `BATCH_JOB_NAMES`,(select group_concat(`bv`.`DESCRIPTION`,char(13,10) separator '') AS `GROUP_CONCAT(``bv``.``DESCRIPTION`` SEPARATOR ',')` from `batch_component_map_view` `bv` where (`bv`.`SERVICE_ID` = `s`.`SERVICE_ID`) group by `s`.`SERVICE_ID`) AS `BATCH_DESCRIPTIONS`,(select group_concat(`bv`.`INFOMAN_PRIV_CLASS`,char(13,10) separator '') AS `GROUP_CONCAT(``bv``.``INFOMAN_PRIV_CLASS`` SEPARATOR ',')` from `batch_component_map_view` `bv` where (`bv`.`SERVICE_ID` = `s`.`SERVICE_ID`) group by `s`.`SERVICE_ID`) AS `BATCH_PRIV_CLASSES`,(select group_concat(`bv`.`RUN_RULE`,char(13,10) separator '') AS `GROUP_CONCAT(``bv``.``RUN_RULE`` SEPARATOR ',')` from `batch_component_map_view` `bv` where (`bv`.`SERVICE_ID` = `s`.`SERVICE_ID`) group by `s`.`SERVICE_ID`) AS `BATCH_RUN_RULES`,(select group_concat(`bv`.`COMPLETION_TIME`,char(13,10) separator '') AS `GROUP_CONCAT(``bv``.``COMPLETION_TIME`` SEPARATOR ',')` from `batch_component_map_view` `bv` where (`bv`.`SERVICE_ID` = `s`.`SERVICE_ID`) group by `s`.`SERVICE_ID`) AS `BATCH_COMPLETION_TIMES`,`u`.`CONTENT` AS `SERVICE_DIAGRAM`,`s`.`ATTACHED_FILE_ID` AS `ATTACHED_FILE_ID`,`d`.`RTO` AS `RTO`,`d`.`RPO` AS `RPO` from (((`services` `s` left join `disaster_recovery` `d` on((`s`.`SERVICE_ID` = `d`.`SERVICE_ID`))) left join `audit_trail` `a` on(((`s`.`SERVICE_ID` = `a`.`TABLE_ROW_ID`) and (`a`.`TABLE_NAME` = _latin1'SERVICES')))) left join `upload` `u` on(((`s`.`ATTACHED_FILE_ID` = `u`.`UPLOAD_ID`) and (`s`.`ATTACHED_FILE_NAME` = `u`.`NAME`)))) group by `s`.`SERVICE_NAME`;

--
-- Definition of view `services_not_used_by_view`
--

DROP TABLE IF EXISTS `services_not_used_by_view`;
DROP VIEW IF EXISTS `services_not_used_by_view`;
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`%` SQL SECURITY DEFINER VIEW `services_not_used_by_view` AS select `bn`.`BUSINESS_ID` AS `BUSINESS_ID`,`bn`.`BUSINESS_NAME` AS `BUSINESS_NAME`,`s`.`SERVICE_ID` AS `SERVICE_ID_NOT_USED`,`s`.`SERVICE_NAME` AS `SERVICE_NAME_NOT_USED` from (`business_names` `bn` join `services` `s`) where not(`s`.`service_id` in (select `sub`.`SERVICE_ID` AS `SERVICE_ID` from `service_used_by` `sub` where (`sub`.`BUSINESS_ID` = `bn`.`BUSINESS_ID`))) order by `bn`.`BUSINESS_ID`,`s`.`SERVICE_NAME`;

--
-- Definition of view `services_sorted`
--

DROP TABLE IF EXISTS `services_sorted`;
DROP VIEW IF EXISTS `services_sorted`;
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`%` SQL SECURITY DEFINER VIEW `services_sorted` AS select `s`.`SERVICE_ID` AS `SERVICE_ID`,`s`.`SERVICE_NAME` AS `SERVICE_NAME`,`s`.`SERVICE_OWNING_BU` AS `SERVICE_OWNING_BU`,`s`.`DESCRIPTION` AS `DESCRIPTION`,`s`.`SERVICE_DEPENDENCIES` AS `SERVICE_DEPENDENCIES`,`s`.`SERVICE_IMPACT_CODE` AS `SERVICE_IMPACT_CODE`,`s`.`LEGAL_REQUIREMENTS` AS `LEGAL_REQUIREMENTS`,`s`.`SLC_MEASURES_FUNC` AS `SLC_MEASURES_FUNC`,`s`.`SLC_MEASURES_AVAIL` AS `SLC_MEASURES_AVAIL`,`s`.`DELIVERABLES` AS `DELIVERABLES`,`s`.`DELIVERABLE_EXCEMPTION` AS `DELIVERABLE_EXCEMPTION`,`s`.`MONITORING` AS `MONITORING`,`s`.`LIMITATIONS` AS `LIMITATIONS`,`s`.`SW_LVL_COMMITMENT` AS `SW_LVL_COMMITMENT`,`s`.`RESPONSIBILITIES` AS `RESPONSIBILITIES`,`b`.`BDM_NAME` AS `BDM_NAME`,`s`.`STATUS` AS `STATUS`,`s`.`COMMITMENT_PERIOD_FLAG` AS `COMMITMENT_PERIOD_FLAG`,`s`.`LAST_USER_TO_UPDATE` AS `LAST_USER_TO_UPDATE`,`s`.`LAST_UPDATE_DATE` AS `LAST_UPDATE_DATE`,`s`.`ATTACHED_FILE_ID` AS `ATTACHED_FILE_ID`,`s`.`ATTACHED_FILE_NAME` AS `ATTACHED_FILE_NAME`,if((((`s`.`SLA_COMMIT_AVAIL` % 2) = 1) or ((`s`.`SLA_COMMIT_AVAIL` % 2) = 0)),concat(`s`.`SLA_COMMIT_AVAIL`,_latin1'.00'),if(isnull(`s`.`SLA_COMMIT_AVAIL`),_utf8'No SLA',`s`.`SLA_COMMIT_AVAIL`)) AS `SLA_COMMIT_AVAIL`,if((((`s`.`SLA_COMMIT_FUNC` % 2) = 1) or ((`s`.`SLA_COMMIT_FUNC` % 2) = 0)),concat(`s`.`SLA_COMMIT_FUNC`,_latin1'.00'),if(isnull(`s`.`SLA_COMMIT_FUNC`),_utf8'No SLA',`s`.`SLA_COMMIT_FUNC`)) AS `SLA_COMMIT_FUNC` from (`services` `s` left join `business_names` `b` on((`s`.`SERVICE_OWNING_BU` = `b`.`BUSINESS_NAME`))) order by `s`.`SERVICE_NAME`;

--
-- Definition of view `storage_policy_view`
--

DROP TABLE IF EXISTS `storage_policy_view`;
DROP VIEW IF EXISTS `storage_policy_view`;
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`%` SQL SECURITY DEFINER VIEW `storage_policy_view` AS select `storage_policy`.`STORAGE_ID` AS `STORAGE_ID`,`storage_policy`.`POLICY_ID` AS `POLICY_ID`,`storage_policy`.`POL_DESCRIP` AS `POL_DESCRIP` from `storage_policy` order by `storage_policy`.`STORAGE_ID`;

--
-- Definition of view `support_team_view`
--

DROP TABLE IF EXISTS `support_team_view`;
DROP VIEW IF EXISTS `support_team_view`;
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`%` SQL SECURITY DEFINER VIEW `support_team_view` AS select `st`.`SUPPORT_TEAM_ID` AS `SUPPORT_TEAM_ID`,`st`.`CLASS_NAME` AS `CLASS_NAME`,`st`.`TEAM_NAME` AS `TEAM_NAME`,`st`.`CLASS_OWNER` AS `CLASS_OWNER` from `support_teams` `st` order by `st`.`CLASS_NAME`;



/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;