CREATE 
    ALGORITHM = UNDEFINED 
    DEFINER = `aerion`@`localhost` 
    SQL SECURITY DEFINER
VIEW `vw_immi_legal` AS
    SELECT 
        `a`.`receipt_id` AS `receipt_id`,
        `a`.`receipt_type` AS `receipt_type`,
        DATE_FORMAT(`a`.`filed_dt`, _UTF8'%m-%d-%y') AS `filed_dt`,
        DATE_FORMAT(`a`.`filed_dt`, _UTF8'%Y-%m-%d') AS `myfiled_dt`,
        DATE_FORMAT(`a`.`filed_dt`, _UTF8'%m') AS `filed_dt_m`,
        DATE_FORMAT(`a`.`filed_dt`, _UTF8'%Y') AS `filed_dt_y`,
        DATE_FORMAT(`a`.`expire_dt`, _UTF8'%m-%Y') AS `expire_dt_new`,
        (TO_DAYS(`a`.`expire_dt`) - TO_DAYS(NOW())) AS `expire_diff`,
        DATE_FORMAT(`a`.`from_dt`, _UTF8'%m-%d-%y') AS `from_dt`,
        DATE_FORMAT(`a`.`to_dt`, _UTF8'%m-%d-%y') AS `to_dt`,
        CONCAT_WS(_UTF8' To ',
                DATE_FORMAT(`a`.`from_dt`, _UTF8'%m-%d-%y'),
                DATE_FORMAT(`a`.`to_dt`, _UTF8'%m-%d-%y')) AS `validity`,
        `a`.`valid_days` AS `valid_days`,
        IF((`a`.`expire_dt` = _UTF8'0000-00-00'),
            _UTF8'',
            DATE_FORMAT(`a`.`expire_dt`, _UTF8'%m-%d-%y')) AS `expire_dt`,
        IF((`a`.`expire_dt` = _UTF8'0000-00-00'),
            _UTF8'',
            DATE_FORMAT(`a`.`expire_dt`, _UTF8'%Y-%m-%d')) AS `myexpire_dt`,
        `a`.`look_street_no_id` AS `look_street_no_id`,
        `a`.`suite_no` AS `suite_no`,
        DATE_FORMAT(`a`.`stat_st_dt`, _UTF8'%m-%d-%y') AS `stat_st_dt`,
        DATE_FORMAT(`a`.`stat_e_dt`, _UTF8'%m-%d-%y') AS `stat_e_dt`,
        `a`.`no_days` AS `no_days`,
        `a`.`rec_status` AS `rec_status`,
        `a`.`immi_summary` AS `immi_summary`,
        `a`.`reftable` AS `reftable`,
        `a`.`emp_id` AS `emp_id`,
        `a`.`comp_id` AS `comp_id`,
        `a`.`receipt_no` AS `receipt_no`,
        `b`.`look_values` AS `ad`,
        `c`.`look_values` AS `imi_source`,
        CONCAT_WS(_LATIN1'-',
                (CASE
                    WHEN
                        (`d`.`look_values` <> _LATIN1'')
                    THEN
                        CONCAT(_LATIN1'(',
                                `b`.`look_values`,
                                _LATIN1'-',
                                `d`.`look_values`,
                                _LATIN1')')
                END),
                (CASE
                    WHEN
                        (`e`.`look_values` <> _LATIN1'')
                    THEN
                        CONCAT(_LATIN1'(',
                                `e`.`look_values`,
                                _LATIN1')')
                END),
                (CASE
                    WHEN
                        (`f`.`look_values` <> _LATIN1'')
                    THEN
                        CONCAT(_LATIN1'(',
                                `f`.`look_values`,
                                _LATIN1')')
                END),
                (CASE
                    WHEN
                        (`g`.`look_values` <> _LATIN1'')
                    THEN
                        CONCAT(_LATIN1'(',
                                `g`.`look_values`,
                                _LATIN1')')
                END),
                (CASE
                    WHEN
                        (`h`.`look_values` <> _LATIN1'')
                    THEN
                        CONCAT(_LATIN1'(',
                                `h`.`look_values`,
                                _LATIN1')')
                END),
                (CASE
                    WHEN (`a`.`salary_amt` <> _LATIN1'') THEN CONCAT(_LATIN1'(', `a`.`salary_amt`, _LATIN1')')
                END)) AS `combo`,
        `i`.`look_values` AS `status`,
        `m`.`companyname` AS `company`,
        CONCAT_WS(_LATIN1',',
                `a`.`receipt_type`,
                `i`.`look_values`,
                `m`.`companyname`) AS `typst`,
        (CASE
            WHEN
                (`a`.`reftable` = _LATIN1'Consultant')
            THEN
                CONCAT(`k`.`prosp_first_name`,
                        _LATIN1' ',
                        `k`.`prosp_last_name`)
            WHEN
                (`a`.`reftable` = _LATIN1'Employee')
            THEN
                CONCAT(`j`.`firstname`,
                        _LATIN1' ',
                        `j`.`lastname`)
            WHEN
                (`a`.`reftable` = _LATIN1'Dependent')
            THEN
                CONCAT(`l`.`firstname`,
                        _LATIN1' ',
                        `l`.`lastname`)
        END) AS `name`,
        `a`.`updater2` AS `updater`,
        CONCAT(CONVERT( DATE_FORMAT(`a`.`last_updated`, _UTF8' %m-%d-%y') USING LATIN1),
                _LATIN1' ',
                `a`.`updater2`) AS `last_updated`,
        `a`.`last_updated` AS `last_updated2`
    FROM
        ((((((((((((`vw_ref_immirec` `a`
        LEFT JOIN `look_values` `b` ON (((`a`.`ad_title` = `b`.`look_values_id`)
            AND (`b`.`look_table` = (SELECT 
                `look_table`.`look_table_id` AS `look_table_id`
            FROM
                `look_table`
            WHERE
                (`look_table`.`table_name` = _LATIN1'immi.detail.ad.title.comp.engage.title'))))))
        LEFT JOIN `vw_immisource` `c` ON ((`a`.`immi_source` = `c`.`look_values_id`)))
        LEFT JOIN `look_values` `d` ON (((`a`.`occ_code` = `d`.`look_values_id`)
            AND (`d`.`look_table` = (SELECT 
                `look_table`.`look_table_id` AS `look_table_id`
            FROM
                `look_table`
            WHERE
                (`look_table`.`table_name` = _LATIN1'immi.detail.occ_code'))))))
        LEFT JOIN `look_values` `e` ON (((`a`.`skill_level` = `e`.`look_values_id`)
            AND (`e`.`look_table` = (SELECT 
                `look_table`.`look_table_id` AS `look_table_id`
            FROM
                `look_table`
            WHERE
                (`look_table`.`table_name` = _LATIN1'immi.detail.skill.level'))))))
        LEFT JOIN `look_values` `f` ON (((`a`.`education` = `f`.`look_values_id`)
            AND (`f`.`look_table` = (SELECT 
                `look_table`.`look_table_id` AS `look_table_id`
            FROM
                `look_table`
            WHERE
                (`look_table`.`table_name` = _LATIN1'immi.detail.education'))))))
        LEFT JOIN `look_values` `g` ON (((`a`.`exp_years` = `g`.`look_values_id`)
            AND (`g`.`look_table` = (SELECT 
                `look_table`.`look_table_id` AS `look_table_id`
            FROM
                `look_table`
            WHERE
                (`look_table`.`table_name` = _LATIN1'immi.detail.exp.years'))))))
        LEFT JOIN `look_values` `h` ON (((`a`.`salary_type` = `h`.`look_values_id`)
            AND (`h`.`look_table` = (SELECT 
                `look_table`.`look_table_id` AS `look_table_id`
            FROM
                `look_table`
            WHERE
                (`look_table`.`table_name` = _LATIN1'immi.detail.salary'))))))
        LEFT JOIN `look_values` `i` ON ((`a`.`rec_status` = `i`.`look_values_id`)))
        LEFT JOIN `emp` `j` ON (((`a`.`emp_id` = `j`.`emp_id`)
            AND (`a`.`reftable` = _LATIN1'Employee'))))
        LEFT JOIN `prosp_consult` `k` ON (((`a`.`emp_id` = `k`.`prosp_consult_id`)
            AND (`a`.`reftable` = _LATIN1'Consultant'))))
        LEFT JOIN `immi_ext_user` `l` ON (((`a`.`emp_id` = `l`.`immi_ext_user_id`)
            AND (`a`.`reftable` = _LATIN1'ExternalUser'))))
        LEFT JOIN `company` `m` ON ((`a`.`comp_id` = `m`.`comp_id`)))