CREATE 
    ALGORITHM = UNDEFINED 
    DEFINER = `ATS`@`localhost` 
    SQL SECURITY DEFINER
VIEW `ATS`.`vw_rpt_dev_status` AS
    SELECT 
        `ATS`.`rpt_dev_status`.`idrpt_dev_status` AS `idrpt_dev_status`,
        `ATS`.`rpt_dev_status`.`iddev_est_reqs` AS `iddev_est_reqs`,
        (SELECT 
                `ATS`.`dev_est_reqs`.`iddev_est` AS `iddev_est`
            FROM
                `ATS`.`dev_est_reqs`
            WHERE
                (`ATS`.`dev_est_reqs`.`iddev_est_reqs` = `ATS`.`rpt_dev_status`.`iddev_est_reqs`)
            GROUP BY `ATS`.`rpt_dev_status`.`iddev_est_reqs`) AS `Tickets`,
        `ATS`.`rpt_dev_status`.`rpt_dev_app` AS `rpt_dev_app`,
        `ATS`.`rpt_dev_status`.`rpt_dev_app_req_smry` AS `rpt_dev_app_req_smry`,
        `ATS`.`rpt_dev_status`.`rpt_dev_req_status_cnt` AS `rpt_dev_req_status_cnt`,
        `ATS`.`rpt_dev_status`.`rpt_dev_stat_list` AS `rpt_dev_stat_list`,
        `ATS`.`rpt_dev_status`.`rpt_dev_stat_hist` AS `rpt_dev_stat_hist`,
        `vw_rpt_dev_status_lag_time`.`lag_time` AS `lag_time`,
        DATE_FORMAT(`ATS`.`rpt_dev_status`.`rpt_dev_req_open_dt_time`,
                _UTF8'%m-%d-%y %h:%i %s') AS `rpt_dev_req_open_dt_time`,
        DATE_FORMAT(`ATS`.`rpt_dev_status`.`rpt_dev_req_open_dt_time`,
                _UTF8'%Y-%m-%d %h:%i %s') AS `rpt_dev_req_open_dt_time1`,
        DATE_FORMAT(`ATS`.`rpt_dev_status`.`rpt_dev_req_close_dt_time`,
                _UTF8'%m-%d-%y %h:%i %s') AS `rpt_dev_req_close_dt_time`,
        DATE_FORMAT(`ATS`.`rpt_dev_status`.`rpt_dev_req_close_dt_time`,
                _UTF8'%Y-%m-%d %h:%i %s') AS `rpt_dev_req_close_dt_time1`,
        `ATS`.`rpt_dev_status`.`rpt_dev_total_time_hrs` AS `rpt_dev_total_time_hrs`,
        `ATS`.`rpt_dev_status`.`rpt_dev_req_open_yr` AS `rpt_dev_req_open_yr`,
        `ATS`.`rpt_dev_status`.`rpt_dev_req_open_mon` AS `rpt_dev_req_open_mon`,
        `ATS`.`rpt_dev_status`.`rpt_dev_req_close_yr` AS `rpt_dev_req_close_yr`,
        `ATS`.`rpt_dev_status`.`rpt_dev_req_close_mon` AS `rpt_dev_req_close_mon`,
        `ATS`.`rpt_dev_status`.`rpt_req_manager` AS `rpt_req_manager`,
        `ATS`.`rpt_dev_status`.`rpt_req_developer` AS `rpt_req_developer`,
        `ATS`.`rpt_dev_status`.`rpt_req_analyst` AS `rpt_req_analyst`,
        (SELECT 
                CONCAT(`ATS`.`emp`.`firstname`,
                            _LATIN1' ',
                            `ATS`.`emp`.`lastname`) AS `concat(firstname,' ',lastname)`
            FROM
                `ATS`.`emp`
            WHERE
                (`ATS`.`emp`.`emp_id` = (SELECT 
                        IF(`ATS`.`dev_est_reqs`.`updater`, 0, 1) AS `if(``updater``,0,1)`
                    FROM
                        `ATS`.`dev_est_reqs`
                    WHERE
                        (`ATS`.`dev_est_reqs`.`iddev_est_reqs` = `ATS`.`rpt_dev_status`.`iddev_est_reqs`)
                    GROUP BY `ATS`.`rpt_dev_status`.`iddev_est_reqs`))) AS `updater`,
        (SELECT 
                CONCAT(`ATS`.`emp`.`firstname`,
                            _LATIN1' ',
                            `ATS`.`emp`.`lastname`) AS `concat(firstname,' ',lastname)`
            FROM
                `ATS`.`emp`
            WHERE
                (`ATS`.`emp`.`emp_id` = (SELECT 
                        `ATS`.`dev_est_reqs`.`assigned_to` AS `assigned_to`
                    FROM
                        `ATS`.`dev_est_reqs`
                    WHERE
                        (`ATS`.`dev_est_reqs`.`iddev_est_reqs` = `ATS`.`rpt_dev_status`.`iddev_est_reqs`)
                    GROUP BY `ATS`.`rpt_dev_status`.`iddev_est_reqs`))) AS `updated_to`
    FROM
        (`ATS`.`rpt_dev_status`
        LEFT JOIN `ATS`.`vw_rpt_dev_status_lag_time` ON ((`ATS`.`rpt_dev_status`.`iddev_est_reqs` = `vw_rpt_dev_status_lag_time`.`iddev_est_reqs`)))