use newwaytoit;
CREATE   or replace   ALGORITHM = UNDEFINED      DEFINER = `newwaytoit`@`localhost`      SQL SECURITY DEFINER VIEW `vw_prosp_corp_activity` AS     SELECT          `a`.`idprosp_corp_activity` AS `idprosp_corp_activity`,         `a`.`idprosp_corp` AS `idprosp_corp`,         `k`.`prosp_corp_type` AS `prosp_corp_type`,         IF(ISNULL(`k`.`prosp_corp_type`),             'Not Entered',             `l`.`prosp_value`) AS `prosp_corp_type_nm`,         `k`.`corp_entity_name` AS `corp_entity_name`,         `k`.`corp_source` AS `corp_source`,         IF(ISNULL(`k`.`corp_source`),             'No Source',             `m`.`prosp_value`) AS `corp_source_nm`,         `k`.`corp_domain` AS `corp_domain`,         IF(ISNULL(`k`.`corp_domain`),             'No Industry',             `n`.`prosp_value`) AS `corp_domain_nm`,         `k`.`corp_domain_sub` AS `corp_domain_sub`,         IF(ISNULL(`k`.`corp_domain_sub`),             'No Sub Industry',             `o`.`prosp_value`) AS `corp_domain_sub_nm`,         `k`.`corp_contacts` AS `corp_contacts`,         `k`.`corp_address` AS `corp_address`,         IFNULL(`k`.`corp_address`, 'Not Entered') AS `corp_address_nm`,         `k`.`register_yr` AS `register_yr`,         IFNULL(`k`.`register_yr`, 'Not Entered') AS `register_yr_fmt`,         `k`.`register_state` AS `register_state`,         IFNULL(`k`.`register_state`, 'Not Entered') AS `register_state_fmt`,         `k`.`start_dt` AS `start_dt`,         DATE_FORMAT(`k`.`start_dt`, '%m-%d-%y') AS `start_dt_fmt`,         `k`.`end_dt` AS `end_dt`,         IF(ISNULL(`k`.`end_dt`),             'Not Ended',             DATE_FORMAT(`k`.`end_dt`, '%m-%d-%y')) AS `end_dt_fmt`,         IF(ISNULL(`k`.`end_dt`),             'Open',             'Closed') AS `prosp_corp_cur_stat`,         `k`.`st_end_tm` AS `st_end_tm`,         IFNULL(`k`.`st_end_tm`,                 TIMESTAMPDIFF(DAY,                     `k`.`start_dt`,                     NOW())) AS `st_end_tm_vir`,         `k`.`about_corp` AS `about_corp`,         `a`.`topic_disussed` AS `topic_disussed`,         `a`.`corp_level` AS `corp_level`,         `j`.`prosp_value` AS `corp_level_nm`,         `a`.`response` AS `response`,         `r`.`prosp_value` AS `response_nm`,         `a`.`called_dt` AS `called_dt`,         DATE_FORMAT(`a`.`called_dt`, '%m-%d-%y') AS `called_dt_fmt`,         DATE_FORMAT(`a`.`called_dt`, '%Y') AS `called_dt_yr`,         DATE_FORMAT(`a`.`called_dt`, '%M') AS `called_dt_mon`,         `a`.`acc_mgr` AS `acc_mgr`,         `c`.`file_name` AS `acc_mgr_fnm`,         CONCAT(`b`.`firstname`, ' ', `b`.`lastname`) AS `acc_mgr_nm`,         `a`.`call_nxt_dt` AS `call_nxt_dt`,         DATE_FORMAT(`a`.`call_nxt_dt`, '%m-%d-%y') AS `call_nxt_dt_fmt`,         DATE_FORMAT(`a`.`call_nxt_dt`, '%Y') AS `call_nxt_dt_yr`,         DATE_FORMAT(`a`.`call_nxt_dt`, '%M') AS `call_nxt_dt_mon`,         IF(ISNULL(`a`.`call_nxt_dt`),             'Open',             'Closed') AS `prosp_corp_activity_cur_stat`,         `a`.`call_day_tm` AS `call_day_tm`,         IF(ISNULL(`a`.`call_day_tm`),             (TO_DAYS(NOW()) - TO_DAYS(`a`.`called_dt`)),             `a`.`call_day_tm`) AS `call_day_tm_vir`,         `a`.`updater` AS `updater`,         `i`.`file_name` AS `updater_fnm`,         (CASE             WHEN                 (ISNULL(`a`.`updated`)                     AND ISNULL(`a`.`updater`))             THEN                 'Not Updated'             WHEN                 (ISNULL(`a`.`updater`)                     AND (`a`.`updated` IS NOT NULL))             THEN                 'Admin'             WHEN                 ((`a`.`updated` IS NOT NULL)                     AND (`a`.`updater` IS NOT NULL))             THEN                 CONCAT(`h`.`firstname`, ' ', `h`.`lastname`)         END) AS `updater_nm`,         `a`.`updated` AS `updated`,         DATE_FORMAT(`a`.`updated`, '%m-%d-%y %h:%i %p') AS `updated_fmt`,         `a`.`del_dt` AS `del_dt`,         DATE_FORMAT(`a`.`del_dt`, '%m-%d-%y %h:%i %p') AS `del_dt_fmt`,         IFNULL(CONVERT( DATE_FORMAT(`a`.`del_dt`, '%Y') USING UTF8MB4),                 'Not Deleted') AS `del_dt_yr`,         IFNULL(CONVERT( DATE_FORMAT(`a`.`del_dt`, '%M') USING UTF8MB4),                 'Not Deleted') AS `del_dt_mon`,         `a`.`del_by` AS `del_by`,         `f`.`file_name` AS `del_by_fnm`,         (CASE             WHEN                 (ISNULL(`a`.`del_dt`)                     AND ISNULL(`a`.`del_by`))             THEN                 'Not Deleted'             WHEN                 (ISNULL(`a`.`del_by`)                     AND (`a`.`del_dt` IS NOT NULL))             THEN                 'Admin'             WHEN                 ((`a`.`del_dt` IS NOT NULL)                     AND (`a`.`del_by` IS NOT NULL))             THEN                 CONCAT(`d`.`firstname`, ' ', `d`.`lastname`)         END) AS `del_by_nm`,         `a`.`del_day` AS `del_day`,         IF((`a`.`archv_dt` IS NOT NULL),             'Never',             (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`,         (CASE             WHEN                 (ISNULL(`a`.`del_dt`)                     AND ISNULL(`a`.`archv_dt`))             THEN                 'Not Archived'             WHEN ISNULL(`a`.`del_dt`) THEN CONVERT( DATE_FORMAT(`a`.`archv_dt`, '%Y') USING UTF8MB4)             WHEN                 ((`a`.`del_dt` IS NOT NULL)                     AND ISNULL(`a`.`archv_dt`))             THEN                 'Deleted'         END) AS `archv_dt_yr`,         (CASE             WHEN                 (ISNULL(`a`.`del_dt`)                     AND ISNULL(`a`.`archv_dt`))             THEN                 'Not Archived'             WHEN ISNULL(`a`.`del_dt`) THEN CONVERT( DATE_FORMAT(`a`.`archv_dt`, '%M') USING UTF8MB4)             WHEN                 ((`a`.`del_dt` IS NOT NULL)                     AND ISNULL(`a`.`archv_dt`))             THEN                 'Deleted'         END) AS `archv_dt_mon`,         `a`.`archv_by` AS `archv_by`,         `g`.`file_name` AS `arch_by_fnm`,         (CASE             WHEN                 (ISNULL(`a`.`archv_dt`)                     AND ISNULL(`a`.`archv_by`))             THEN                 'Not Archived'             WHEN                 (ISNULL(`a`.`archv_by`)                     AND (`a`.`archv_dt` IS NOT NULL))             THEN                 'Admin'             WHEN                 ((`a`.`archv_dt` IS NOT NULL)                     AND (`a`.`archv_by` IS NOT NULL))             THEN                 CONCAT(`e`.`firstname`, ' ', `e`.`lastname`)         END) AS `arch_by_nm`     FROM         (((((((((((((((`prosp_corp_activity` `a`         LEFT JOIN `emp` `b` ON ((`b`.`emp_id` = `a`.`acc_mgr`)))         LEFT JOIN `storages` `c` ON ((`c`.`idstorages` = `b`.`idstorages`)))         LEFT JOIN `emp` `d` ON ((`a`.`del_by` = `d`.`emp_id`)))         LEFT JOIN `emp` `e` ON ((`a`.`archv_by` = `e`.`emp_id`)))         LEFT JOIN `storages` `f` ON ((`d`.`idstorages` = `f`.`idstorages`)))         LEFT JOIN `storages` `g` ON ((`e`.`idstorages` = `g`.`idstorages`)))         LEFT JOIN `emp` `h` ON ((`h`.`emp_id` = `a`.`updater`)))         LEFT JOIN `storages` `i` ON ((`h`.`idstorages` = `i`.`idstorages`)))         LEFT JOIN `prosp_lk_val` `r` ON ((`a`.`response` = `r`.`idprosp_lk_val`)))         LEFT JOIN `prosp_lk_val` `j` ON ((`a`.`corp_level` = `j`.`idprosp_lk_val`)))         LEFT JOIN `prosp_corp` `k` ON ((`a`.`idprosp_corp` = `k`.`idprosp_corp`)))         LEFT JOIN `prosp_lk_val` `l` ON ((`k`.`prosp_corp_type` = `l`.`idprosp_lk_val`)))         LEFT JOIN `prosp_lk_val` `m` ON ((`k`.`corp_source` = `m`.`idprosp_lk_val`)))         LEFT JOIN `prosp_lk_val` `n` ON ((`k`.`corp_domain` = `n`.`idprosp_lk_val`)))         LEFT JOIN `prosp_lk_val` `o` ON ((`k`.`corp_domain_sub` = `o`.`idprosp_lk_val`)))