CREATE 
    ALGORITHM = UNDEFINED 
    DEFINER = `aerion`@`localhost` 
    SQL SECURITY DEFINER
VIEW `aerion`.`vw_prosp_open` AS
    SELECT 
        `a`.`idprosp_open` AS `idprosp_open`,
        `b`.`companyname` AS `compname`,
        `c`.`companyname` AS `client_name`,
        CONCAT(`d`.`city_name`,
                _LATIN1',',
                `e`.`state_name`) AS `location`,
        `e`.`state_id` AS `state_id`,
        `e`.`state_name` AS `state_name`,
        `d`.`city_id` AS `city_id`,
        `d`.`city_name` AS `city_name`,
        `a`.`contact_info` AS `contact_info`,
        `a`.`open_details` AS `open_details`,
        DATE_FORMAT(`a`.`open_dt`, _UTF8'%m-%d-%y') AS `open_dt`,
        `a`.`open_dt` AS `open_dt1`,
        DATE_FORMAT(`a`.`close_dt`, _UTF8'%m-%d-%y') AS `close_dt`,
        `a`.`close_dt` AS `close_dt1`,
        (CASE
            WHEN
                ((ISNULL(`a`.`close_dt`)
                    OR (`a`.`close_dt` = _UTF8'00-00-00'))
                    AND (`a`.`open_dt` IS NOT NULL))
            THEN
                _UTF8'Open'
            WHEN
                ((`a`.`open_dt` IS NOT NULL)
                    AND (`a`.`close_dt` IS NOT NULL))
            THEN
                _UTF8'Close'
            ELSE NULL
        END) AS `Pstate`,
        `a`.`last_updated` AS `last_updated`,
        `a`.`updated_by` AS `updated_by`,
        CONCAT(CONVERT( DATE_FORMAT(`a`.`last_updated`,
                        _UTF8'%m-%d-%y %H:%i:%s') USING LATIN1),
                _LATIN1' ',
                (CASE
                    WHEN (`a`.`updated_by` = _UTF8'0') THEN 'Admin'
                    ELSE CONCAT(`f`.`firstname`,
                            _LATIN1'',
                            `f`.`lastname`)
                END)) AS `mod_last_updated`,
        `a`.`summary` AS `summary`
    FROM
        (((((`aerion`.`prosp_open` `a`
        LEFT JOIN `aerion`.`company` `b` ON ((`a`.`compname` = `b`.`comp_id`)))
        LEFT JOIN `aerion`.`company` `c` ON ((`a`.`client_name` = `c`.`comp_id`)))
        LEFT JOIN `aerion`.`look_city` `d` ON ((`a`.`location` = `d`.`city_id`)))
        LEFT JOIN `aerion`.`look_state` `e` ON ((`d`.`state_id` = `e`.`state_id`)))
        LEFT JOIN `aerion`.`emp` `f` ON ((`a`.`updated_by` = `f`.`emp_id`)))
    ORDER BY `a`.`last_updated` DESC