CREATE 
    ALGORITHM = UNDEFINED 
    DEFINER = `aerion_master`@`localhost` 
    SQL SECURITY DEFINER
VIEW `aerion_master`.`vw_cnslt_con_1` AS
    SELECT 
        `aerion_master`.`inv`.`inv_to_ref_id` AS `consult_id`,
        _UTF8'inv' AS `table_name`,
        _UTF8'inv_to_ref_id' AS `refid`,
        _UTF8'inv_to_ref_table' AS `reftable`,
        _UTF8'inv' AS `tbl_to_update`,
        _UTF8'' AS `tbl_to_insert`,
        _UTF8'' AS `fetch_cols`,
        _UTF8'' AS `insert_cols`,
        _UTF8'u' AS `action`
    FROM
        `aerion_master`.`inv`
    WHERE
        (LCASE(`aerion_master`.`inv`.`inv_to_ref_table`) = CONVERT( LCASE(_UTF8'consultant') USING LATIN1)) 
    UNION SELECT 
        `aerion_master`.`prosp_assist`.`assist_ref_id` AS `consult_id`,
        _UTF8'prosp_assist' AS `table_name`,
        _UTF8'assist_ref_id' AS `refid`,
        _UTF8'assist_ref_table' AS `reftable`,
        _UTF8'prosp_assist' AS `tbl_to_update`,
        _UTF8'' AS `tbl_to_insert`,
        _UTF8'' AS `fetch_cols`,
        _UTF8'' AS `insert_cols`,
        _UTF8'u' AS `action`
    FROM
        `aerion_master`.`prosp_assist`
    WHERE
        (LCASE(`aerion_master`.`prosp_assist`.`assist_ref_table`) = CONVERT( LCASE(_UTF8'consultant') USING LATIN1)) 
    UNION SELECT 
        `aerion_master`.`immi_hierarchy`.`immi_hierar_ref_id` AS `consult_id`,
        _UTF8'immi_hierarchy' AS `table_name`,
        _UTF8'immi_hierar_ref_id' AS `refid`,
        _UTF8'immi_hierar_table' AS `reftable`,
        _UTF8'immi_hierarchy' AS `tbl_to_update`,
        _UTF8'' AS `tbl_to_insert`,
        _UTF8'' AS `fetch_cols`,
        _UTF8'' AS `insert_cols`,
        _UTF8'u' AS `action`
    FROM
        `aerion_master`.`immi_hierarchy`
    WHERE
        (LCASE(`aerion_master`.`immi_hierarchy`.`immi_hierar_table`) = CONVERT( LCASE(_UTF8'prosp consultant') USING LATIN1)) 
    UNION SELECT 
        `aerion_master`.`immi_emp_receipt`.`immi_ref_id` AS `consult_id`,
        _UTF8'immi_emp_receipt' AS `table_name`,
        _UTF8'immi_ref_id' AS `refid`,
        _UTF8'immi_ref_table' AS `reftable`,
        _UTF8'immi_emp_receipt' AS `tbl_to_update`,
        _UTF8'' AS `tbl_to_insert`,
        _UTF8'' AS `fetch_cols`,
        _UTF8'' AS `insert_cols`,
        _UTF8'u' AS `action`
    FROM
        `aerion_master`.`immi_emp_receipt`
    WHERE
        (LCASE(`aerion_master`.`immi_emp_receipt`.`immi_ref_table`) = CONVERT( LCASE(_UTF8'consultant') USING LATIN1)) 
    UNION SELECT 
        `aerion_master`.`bank_acc`.`acc_owner` AS `consult_id`,
        _UTF8'bank_acc' AS `table_name`,
        _UTF8'acc_owner' AS `refid`,
        _UTF8'acc_owner_table' AS `reftable`,
        _UTF8'acc_owner_table' AS `tbl_to_update`,
        _UTF8'' AS `tbl_to_insert`,
        _UTF8'' AS `fetch_cols`,
        _UTF8'' AS `insert_cols`,
        _UTF8'u' AS `action`
    FROM
        `aerion_master`.`bank_acc`
    WHERE
        (LCASE(`aerion_master`.`bank_acc`.`acc_owner_table`) = CONVERT( LCASE(_UTF8'consultant') USING LATIN1)) 
    UNION SELECT 
        `aerion_master`.`storages`.`updated_for_ref_id` AS `consult_id`,
        _UTF8'storages' AS `table_name`,
        _UTF8'updated_for_ref_id' AS `refid`,
        _UTF8'updated_for_ref_table' AS `reftable`,
        _UTF8'storages' AS `tbl_to_update`,
        _UTF8'' AS `tbl_to_insert`,
        _UTF8'' AS `fetch_cols`,
        _UTF8'' AS `insert_cols`,
        _UTF8'u' AS `action`
    FROM
        `aerion_master`.`storages`
    WHERE
        (LCASE(`aerion_master`.`storages`.`updated_for_ref_table`) = CONVERT( LCASE(_UTF8'consultant') USING LATIN1)) 
    UNION SELECT 
        `aerion_master`.`prosp_refund1`.`ref_for_id` AS `consult_id`,
        _UTF8'prosp_refund1' AS `table_name`,
        _UTF8'ref_for_id' AS `refid`,
        _UTF8'ref_for_table' AS `reftable`,
        _UTF8'prosp_refund1' AS `tbl_to_update`,
        _UTF8'' AS `tbl_to_insert`,
        _UTF8'' AS `fetch_cols`,
        _UTF8'' AS `insert_cols`,
        _UTF8'u' AS `action`
    FROM
        `aerion_master`.`prosp_refund1`
    WHERE
        (LCASE(`aerion_master`.`prosp_refund1`.`ref_for_table`) = CONVERT( LCASE(_UTF8'consultant') USING LATIN1)) 
    UNION SELECT 
        `aerion_master`.`prosp_refund_detail`.`ref_for_id` AS `consult_id`,
        _UTF8'prosp_refund_detail' AS `table_name`,
        _UTF8'ref_for_id' AS `refid`,
        _UTF8'ref_for_table' AS `reftable`,
        _UTF8'prosp_refund_detail' AS `tbl_to_update`,
        _UTF8'' AS `tbl_to_insert`,
        _UTF8'' AS `fetch_cols`,
        _UTF8'' AS `insert_cols`,
        _UTF8'u' AS `action`
    FROM
        `aerion_master`.`prosp_refund_detail`
    WHERE
        (LCASE(`aerion_master`.`prosp_refund_detail`.`ref_for_table`) = CONVERT( LCASE(_UTF8'consultant') USING LATIN1)) 
    UNION SELECT 
        `aerion_master`.`prosp_activity`.`prosp_consult_id` AS `consult_id`,
        _UTF8'prosp_activity' AS `table_name`,
        _UTF8'prosp_consult_id' AS `refid`,
        _UTF8'' AS `reftable`,
        _UTF8'prosp_activity' AS `tbl_to_update`,
        _UTF8'emp_activity' AS `tbl_to_insert`,
        _UTF8'$new_id,call_recv_dt,next_call_dt,call_note,$updater_conversion,$last_update' AS `fetch_cols`,
        _UTF8'emp_id,call_recv_dt,next_call_dt,call_note,emp_id_updated,last_updated' AS `insert_cols`,
        _UTF8'id' AS `action`
    FROM
        `aerion_master`.`prosp_activity` 
    UNION SELECT 
        `aerion_master`.`prosp_attach`.`prosp_consult_id` AS `consult_id`,
        _UTF8'prosp_attach' AS `table_name`,
        _UTF8'prosp_consult_id' AS `refid`,
        _UTF8'' AS `reftable`,
        _UTF8'prosp_attach' AS `tbl_to_update`,
        _UTF8'emp_attach' AS `tbl_to_insert`,
        _UTF8'$new_id,idstorage,$updater_conversion,$last_update' AS `fetch_cols`,
        _UTF8'emp_id,idstorage,updater,last_updated' AS `insert_cols`,
        _UTF8'id' AS `action`
    FROM
        `aerion_master`.`prosp_attach` 
    UNION SELECT 
        `aerion_master`.`prosp_contact`.`prosp_consult_id` AS `consult_id`,
        _UTF8'prosp_contact' AS `table_name`,
        _UTF8'prosp_consult_id' AS `refid`,
        _UTF8'' AS `reftable`,
        _UTF8'prosp_contact' AS `tbl_to_update`,
        _UTF8'emp_contact' AS `tbl_to_insert`,
        _UTF8'$new_id,contact_id,(select look_values_id from look_values where look_values = prosp_contact.prosp_contact_type and isnull(look_sub_value_id) and not isnull(look_value_code)),summary,$last_update' AS `fetch_cols`,
        _UTF8'emp_id,contact_id,emp_contact_type,summary,last_updated' AS `insert_cols`,
        _UTF8'id' AS `action`
    FROM
        `aerion_master`.`prosp_contact` 
    UNION SELECT 
        `aerion_master`.`prosp_tag`.`prosp_consult_id` AS `consult_id`,
        _UTF8'prosp_tag' AS `table_name`,
        _UTF8'prosp_consult_id' AS `refid`,
        _UTF8'' AS `reftable`,
        _UTF8'prosp_tag' AS `tbl_to_update`,
        _UTF8'emp_tag' AS `tbl_to_insert`,
        _UTF8'$new_id,prosp_tags' AS `fetch_cols`,
        _UTF8'emp_id,emp_tags' AS `insert_cols`,
        _UTF8'id' AS `action`
    FROM
        `aerion_master`.`prosp_tag` 
    UNION SELECT 
        `aerion_master`.`prosp_consult`.`prosp_consult_id` AS `consult_id`,
        _UTF8'prosp_consult' AS `table_name`,
        _UTF8'prosp_consult_id' AS `refid`,
        _UTF8'' AS `reftable`,
        _UTF8'prosp_consult' AS `tbl_to_update`,
        _UTF8'' AS `tbl_to_insert`,
        _UTF8'' AS `fetch_cols`,
        _UTF8'' AS `insert_cols`,
        _UTF8'd' AS `action`
    FROM
        `aerion_master`.`prosp_consult` 
    UNION SELECT 
        `aerion_master`.`prosp_refund_test`.`ref_for_id` AS `consult_id`,
        _UTF8'prosp_refund_test' AS `table_name`,
        _UTF8'ref_for_id' AS `refid`,
        _UTF8'ref_for_table' AS `reftable`,
        _UTF8'prosp_refund_test' AS `tbl_to_update`,
        _UTF8'' AS `tbl_to_insert`,
        _UTF8'' AS `fetch_cols`,
        _UTF8'' AS `insert_cols`,
        _UTF8'u' AS `action`
    FROM
        `aerion_master`.`prosp_refund_test`
    WHERE
        (LCASE(`aerion_master`.`prosp_refund_test`.`ref_for_table`) = CONVERT( LCASE(_UTF8'consultant') USING LATIN1)) 
    UNION SELECT 
        `aerion_master`.`mkt_assist`.`assist_ref_id` AS `consult_id`,
        _UTF8'mkt_assist' AS `table_name`,
        _UTF8'assist_ref_id' AS `refid`,
        _UTF8'assist_ref_table' AS `reftable`,
        _UTF8'mkt_assist' AS `tbl_to_update`,
        _UTF8'' AS `tbl_to_insert`,
        _UTF8'' AS `fetch_cols`,
        _UTF8'' AS `insert_cols`,
        _UTF8'u' AS `action`
    FROM
        `aerion_master`.`mkt_assist`
    WHERE
        (LCASE(`aerion_master`.`mkt_assist`.`assist_ref_table`) = CONVERT( LCASE(_UTF8'consultant') USING LATIN1)) 
    UNION SELECT 
        `aerion_master`.`prosp_consult_merger`.`prosp_consult_id` AS `consult_id`,
        _UTF8'prosp_consult_merger' AS `table_name`,
        _UTF8'prosp_consult_id' AS `refid`,
        _UTF8'' AS `reftable`,
        _UTF8'prosp_consult_merger' AS `tbl_to_update`,
        _UTF8'' AS `tbl_to_insert`,
        _UTF8'' AS `fetch_cols`,
        _UTF8'' AS `insert_cols`,
        _UTF8'd' AS `action`
    FROM
        `aerion_master`.`prosp_consult_merger` 
    UNION SELECT 
        `aerion_master`.`test_cnv`.`cnv_ref_id` AS `consult_id`,
        _UTF8'test_cnv' AS `table_name`,
        _UTF8'cnv_ref_id' AS `refid`,
        _UTF8'cnv_ref_table' AS `reftable`,
        _UTF8'test_cnv' AS `tbl_to_update`,
        _UTF8'' AS `tbl_to_insert`,
        _UTF8'' AS `fetch_cols`,
        _UTF8'' AS `insert_cols`,
        _UTF8'u' AS `action`
    FROM
        `aerion_master`.`test_cnv` 
    UNION SELECT 
        `aerion_master`.`add_conv_table`.`add_conv_table_ref_id` AS `consult_id`,
        _UTF8'add_conv_table' AS `table_name`,
        _UTF8'add_conv_table_ref_id' AS `refid`,
        _UTF8'add_conv_table_ref_table' AS `reftable`,
        _UTF8'add_conv_table' AS `tbl_to_update`,
        _UTF8'' AS `tbl_to_insert`,
        _UTF8'' AS `fetch_cols`,
        _UTF8'' AS `insert_cols`,
        _UTF8'u' AS `action`
    FROM
        `aerion_master`.`add_conv_table`
    ORDER BY `table_name` , `consult_id` , `tbl_to_update` , `tbl_to_insert`