CREATE 
    ALGORITHM = UNDEFINED 
    DEFINER = `ATS`@`localhost` 
    SQL SECURITY DEFINER
VIEW `ATS`.`vw_comp_active` AS
    SELECT 
        `a`.`idcomp_active` AS `idcomp_active`,
        `a`.`comp_id` AS `comp_id`,
        IFNULL(`c`.`companyname`, 'None') AS `companyname`,
        `c`.`storage_file_name` AS `company_logo`,
        `a`.`comp_background` AS `comp_background`,
        `a`.`active_type` AS `active_type`,
        `a`.`publish` AS `publish`,
        `a`.`manage_by` AS `manage_by`,
        IFNULL(`m`.`fullname`, 'Admin') AS `manager_name`,
        `a`.`client_since` AS `client_since`,
        `a`.`last_updated` AS `last_updated`,
        `a`.`updater` AS `updater_id`,
        IFNULL(`u`.`fullname`, 'Admin') AS `updater`,
        `a`.`del_dt` AS `del_dt_unformatted`,
        DATE_FORMAT(`a`.`del_dt`, '%m-%d-%y %h:%i %p') AS `del_dt`,
        `a`.`del_by` AS `del_by_id`,
        IFNULL(`e_del_by`.`fullname`, 'Admin') AS `del_by`,
        `e_del_by`.`emp_file_name` AS `del_by_file_name`,
        (TO_DAYS((DATE_FORMAT(`a`.`del_dt`, '%y-%m-%d') + INTERVAL `a`.`del_day` DAY)) - TO_DAYS(DATE_FORMAT(NOW(), '%y-%m-%d'))) AS `virtual_del_day`,
        `a`.`archv_dt` AS `archv_dt_unformatted`,
        DATE_FORMAT(`a`.`archv_dt`, '%m-%d-%y %h:%i %p') AS `archv_dt`,
        `a`.`archv_by` AS `archv_by_id`,
        IFNULL(`e_archv_by`.`fullname`, 'Admin') AS `archv_by`,
        `e_archv_by`.`emp_file_name` AS `archv_by_file_name`
    FROM
        (((((`ATS`.`comp_active` `a`
        LEFT JOIN `ATS`.`vw_company` `c` ON ((`a`.`comp_id` = `c`.`comp_id`)))
        LEFT JOIN `ATS`.`vw_emp` `m` ON ((`a`.`manage_by` = `m`.`emp_id`)))
        LEFT JOIN `ATS`.`vw_emp` `u` ON ((`a`.`updater` = `u`.`emp_id`)))
        LEFT JOIN `ATS`.`vw_emp` `e_del_by` ON ((`a`.`del_by` = `e_del_by`.`emp_id`)))
        LEFT JOIN `ATS`.`vw_emp` `e_archv_by` ON ((`a`.`archv_by` = `e_archv_by`.`emp_id`)))
    ORDER BY `a`.`last_updated` DESC