CREATE 
    ALGORITHM = UNDEFINED 
    DEFINER = `heshmapatel`@`%` 
    SQL SECURITY DEFINER
VIEW `heshmapatel`.`vw_dev_est_reqs_status_forcast` AS
    SELECT 
        `a`.`iddev_est_reqs_status_forcast` AS `iddev_est_reqs_status_forcast`,
        `c`.`iddev_apps` AS `iddev_apps`,
        `d`.`app_name` AS `app_name`,
        `b`.`iddev_est` AS `iddev_est`,
        `c`.`est_type` AS `est_type`,
        `c`.`category` AS `category`,
        IFNULL(`m`.`look_value`, 'No Category') AS `category_nm`,
        `c`.`est_ref_id` AS `est_ref_id`,
        `c`.`est_ref_tab` AS `est_ref_tab`,
        (CASE
            WHEN (`c`.`est_ref_tab` LIKE 'emp') THEN `f`.`file_name`
            WHEN (`c`.`est_ref_tab` LIKE 'comp') THEN `h`.`file_name`
        END) AS `est_fnm`,
        IFNULL((CASE
                    WHEN (`c`.`est_ref_tab` LIKE 'emp') THEN CONCAT(`e`.`firstname`, ' ', `e`.`lastname`)
                    WHEN (`c`.`est_ref_tab` LIKE 'comp') THEN `g`.`companyname`
                END),
                'No Estimater') AS `est_ref_nm`,
        `c`.`owner_ref_id` AS `owner_ref_id`,
        `c`.`owner_ref_tab` AS `owner_ref_tab`,
        (CASE
            WHEN (`c`.`owner_ref_tab` LIKE 'emp') THEN `j`.`file_name`
            WHEN (`c`.`owner_ref_tab` LIKE 'comp') THEN `l`.`file_name`
        END) AS `owner_fnm`,
        IFNULL((CASE
                    WHEN (`c`.`owner_ref_tab` LIKE 'emp') THEN CONCAT(`i`.`firstname`, ' ', `i`.`lastname`)
                    WHEN (`c`.`owner_ref_tab` LIKE 'comp') THEN `k`.`companyname`
                END),
                'No Owner') AS `owner_ref_nm`,
        `c`.`dev_est_smry` AS `dev_est_smry`,
        `a`.`iddev_est_reqs` AS `iddev_est_reqs`,
        `b`.`req_desc` AS `req_desc`,
        `b`.`req_st_dt` AS `req_st_dt`,
        IF(ISNULL(`b`.`req_st_dt`),
            'Not Started',
            DATE_FORMAT(`b`.`req_st_dt`, '%m-%d-%y %h:%i %p')) AS `req_st_dt_fmt`,
        `b`.`req_e_dt` AS `req_e_dt`,
        (CASE
            WHEN (`b`.`req_e_dt` IS NOT NULL) THEN DATE_FORMAT(`b`.`req_e_dt`, '%m-%d-%y %h:%i %p')
            WHEN
                (ISNULL(`b`.`req_e_dt`)
                    AND ISNULL(`b`.`req_st_dt`))
            THEN
                'Not Started'
            WHEN
                (ISNULL(`b`.`req_e_dt`)
                    AND (`b`.`req_st_dt` IS NOT NULL))
            THEN
                'In Progress'
        END) AS `req_e_dt_fmt`,
        `b`.`req_time` AS `req_time`,
        (CASE
            WHEN
                (ISNULL(`b`.`req_time`)
                    AND (`b`.`req_st_dt` IS NOT NULL))
            THEN
                ROUND(((UNIX_TIMESTAMP(NOW()) - UNIX_TIMESTAMP(`b`.`req_st_dt`)) / 60),
                        0)
            ELSE `b`.`req_time`
        END) AS `req_time_vir`,
        `b`.`req_type` AS `req_type`,
        IFNULL(`n`.`look_value`, 'None') AS `req_type_nm`,
        `b`.`skill_lvl` AS `skill_lvl`,
        IFNULL(`b`.`skill_lvl`, 'No Skill') AS `skill_lvl_nm`,
        `b`.`gap_lvl` AS `gap_lvl`,
        IFNULL(`b`.`gap_lvl`, 'None') AS `gap_lvl_nm`,
        `a`.`iddev_est_reqs_status` AS `iddev_est_reqs_status`,
        IF(ISNULL(`a`.`iddev_est_reqs_status`),
            'Pending',
            'Started') AS `iddev_est_reqs_status_nm`,
        `a`.`req_status` AS `req_status`,
        IFNULL(`o`.`look_value`, 'No Status') AS `req_status_nm`,
        `a`.`idlook_stat_order` AS `idlook_stat_order`,
        `a`.`for_stat_e_dt_pre` AS `for_stat_e_dt_pre`,
        DATE_FORMAT(`a`.`for_stat_e_dt_pre`,
                '%m-%d-%y %h:%i %p') AS `for_stat_e_dt_pre_fmt`,
        `a`.`for_stat_lag_time` AS `for_lag_time`,
        IFNULL(`a`.`for_stat_lag_time`, '0') AS `for_lag_time_vir`,
        `a`.`for_stat_st_dt` AS `for_stat_st_dt`,
        IF(ISNULL(`a`.`for_stat_st_dt`),
            'Not Started',
            DATE_FORMAT(`a`.`for_stat_st_dt`,
                    '%m-%d-%y %h:%i %p')) AS `for_stat_st_dt_fmt`,
        `a`.`for_stat_e_dt` AS `for_stat_e_dt`,
        (CASE
            WHEN (`a`.`for_stat_e_dt` IS NOT NULL) THEN DATE_FORMAT(`a`.`for_stat_e_dt`, '%m-%d-%y %h:%i %p')
            WHEN
                (ISNULL(`a`.`for_stat_e_dt`)
                    AND ISNULL(`a`.`for_stat_st_dt`))
            THEN
                'Not Started'
            WHEN
                (ISNULL(`a`.`for_stat_e_dt`)
                    AND (`a`.`for_stat_st_dt` IS NOT NULL))
            THEN
                'In Progress'
        END) AS `for_stat_e_dt_fmt`,
        `a`.`for_stat_time` AS `for_stat_time`,
        IFNULL(`a`.`for_stat_time`,
                TIMESTAMPDIFF(MINUTE,
                    `a`.`for_stat_st_dt`,
                    NOW())) AS `for_stat_time_vir`,
        `a`.`for_req_bud_amt` AS `for_req_bud_amt`,
        
        `a`.`hist_cnt` AS `hist_cnt`,
        `a`.`assigned_to` AS `assigned_to`,
        `q`.`file_name` AS `assigned_to_fnm`,
        IFNULL(CONCAT(`p`.`firstname`, ' ', `p`.`lastname`),
                'No Assignee') AS `assigned_to_nm`,
        `a`.`last_updated` AS `last_updated`,
        DATE_FORMAT(`a`.`last_updated`, '%m-%d-%y %h:%i %p') AS `last_updated_fmt`,
        `a`.`updater` AS `updater`,
        `s`.`file_name` AS `updater_fnm`,
        IFNULL(CONCAT(`r`.`firstname`, ' ', `r`.`lastname`),
                'Admin') AS `updater_nm`,
        `a`.`del_dt` AS `del_dt`,
        DATE_FORMAT(`a`.`del_dt`, '%m-%d-%y %h:%i %p') AS `del_dt_fmt`,
        `a`.`del_by` AS `del_by`,
        `u`.`file_name` AS `del_by_fnm`,
        IFNULL(CONCAT(`t`.`firstname`, ' ', `t`.`lastname`),
                'Admin') AS `del_by_nm`,
        `a`.`del_day` AS `del_day`,
        (TO_DAYS((DATE_FORMAT(`a`.`del_dt`, '%y-%m-%d') + INTERVAL `a`.`del_day` DAY)) - TO_DAYS(DATE_FORMAT(NOW(), '%y-%m-%d'))) AS `del_day_vir`,
        `a`.`archv_dt` AS `archv_dt`,
        DATE_FORMAT(`a`.`archv_dt`, '%m-%d-%y %h:%i %p') AS `archv_dt_fmt`,
        `a`.`archv_by` AS `archv_by`,
        `w`.`file_name` AS `archv_by_fnm`,
        IFNULL(CONCAT(`v`.`firstname`, ' ', `v`.`lastname`),
                'Admin') AS `archv_by_nm`
    FROM
        ((((((((((((((((((((((`aerion`.`dev_est_reqs_status_forcast` `a`
        LEFT JOIN `aerion`.`dev_est_reqs` `b` ON ((`a`.`iddev_est_reqs` = `b`.`iddev_est_reqs`)))
        LEFT JOIN `aerion`.`dev_est` `c` ON ((`b`.`iddev_est` = `c`.`iddev_est`)))
        LEFT JOIN `aerion`.`dev_apps` `d` ON ((`c`.`iddev_apps` = `d`.`iddev_apps`)))
        LEFT JOIN `aerion`.`emp` `e` ON ((`c`.`est_ref_id` = `e`.`emp_id`)))
        LEFT JOIN `aerion`.`storages` `f` ON ((`e`.`idstorage` = `f`.`idstorage`)))
        LEFT JOIN `aerion`.`company` `g` ON ((`c`.`est_ref_id` = `g`.`comp_id`)))
        LEFT JOIN `aerion`.`storages` `h` ON ((`g`.`idstorage` = `h`.`idstorage`)))
        LEFT JOIN `aerion`.`emp` `i` ON ((`c`.`owner_ref_id` = `i`.`emp_id`)))
        LEFT JOIN `aerion`.`storages` `j` ON ((`i`.`idstorage` = `j`.`idstorage`)))
        LEFT JOIN `aerion`.`company` `k` ON ((`c`.`owner_ref_id` = `k`.`comp_id`)))
        LEFT JOIN `aerion`.`storages` `l` ON ((`k`.`idstorage` = `l`.`idstorage`)))
        LEFT JOIN `aerion`.`dev_look_value` `m` ON ((`c`.`category` = `m`.`iddev_look_value`)))
        LEFT JOIN `aerion`.`dev_look_value` `n` ON ((`b`.`req_type` = `n`.`iddev_look_value`)))
        LEFT JOIN `aerion`.`dev_look_value` `o` ON ((`a`.`req_status` = `o`.`iddev_look_value`)))
        LEFT JOIN `aerion`.`emp` `p` ON ((`a`.`assigned_to` = `p`.`emp_id`)))
        LEFT JOIN `aerion`.`storages` `q` ON ((`p`.`idstorage` = `q`.`idstorage`)))
        LEFT JOIN `aerion`.`emp` `r` ON ((`a`.`updater` = `r`.`emp_id`)))
        LEFT JOIN `aerion`.`storages` `s` ON ((`r`.`idstorage` = `s`.`idstorage`)))
        LEFT JOIN `aerion`.`emp` `t` ON ((`a`.`del_by` = `t`.`emp_id`)))
        LEFT JOIN `aerion`.`storages` `u` ON ((`t`.`idstorage` = `u`.`idstorage`)))
        LEFT JOIN `aerion`.`emp` `v` ON ((`a`.`archv_by` = `v`.`emp_id`)))
        LEFT JOIN `aerion`.`storages` `w` ON ((`v`.`idstorage` = `w`.`idstorage`)))
        left join aerion.dev_est_reqs_status as x on a.iddev_est_reqs_status = x.iddev_est_reqs_status