CREATE 
    ALGORITHM = UNDEFINED 
    DEFINER = `ATS`@`localhost` 
    SQL SECURITY DEFINER
VIEW `ATS`.`vw_prosp_initiate` AS
    SELECT 
        `b`.`idprosp_refund1` AS `idprosp_refund1`,
        `b`.`ref_for_id` AS `payee_id`,
        `b`.`ref_for_table` AS `payee_from`,
        (CASE
            WHEN
                (`b`.`ref_for_table` = _LATIN1'consult')
            THEN
                CONCAT(`e`.`prosp_first_name`,
                        _LATIN1' ',
                        `e`.`prosp_last_name`)
            ELSE CONCAT(`d`.`firstname`,
                    _LATIN1' ',
                    `d`.`lastname`)
        END) AS `payee`,
        `b`.`total_paid` AS `total_paid`,
        `b`.`total_refund` AS `total_refund`,
        `b`.`cleared` AS `cleared`,
        `b`.`last_updated` AS `last_updated`,
        `a`.`idprosp_refund2` AS `idprosp_refund2`,
        `a`.`paid_amt` AS `paid_amt`,
        `a`.`refund_amt` AS `refund_amt`,
        `a`.`pay_for` AS `purpose`,
        `a`.`pay_dt` AS `pay_dt`,
        `a`.`pay_smry` AS `pay_smry`,
        `a`.`selection` AS `selection`,
        `c`.`idprosp_refund_details` AS `refund_details`,
        `c`.`refund_type` AS `refund_type`,
        `c`.`refund_amt` AS `refund_phases`,
        `c`.`ref_for_id` AS `ref_for_id`,
        `c`.`ref_for_table` AS `ref_for_table`,
        (CASE
            WHEN
                ((`c`.`ref_for_table` = _LATIN1'consult')
                    OR ISNULL(`c`.`ref_for_table`))
            THEN
                CONCAT(`e`.`prosp_first_name`,
                        _LATIN1' ',
                        `e`.`prosp_last_name`)
            ELSE CONCAT(`d`.`firstname`,
                    _LATIN1' ',
                    `d`.`lastname`)
        END) AS `refunded_to`,
        `c`.`refund_dt` AS `refund_dt`,
        `c`.`refund_smry` AS `refund_smry`,
        `c`.`display` AS `display`
    FROM
        ((((`ATS`.`prosp_refund2` `a`
        LEFT JOIN `ATS`.`prosp_refund1` `b` ON ((`a`.`idprosp_refund1` = `b`.`idprosp_refund1`)))
        LEFT JOIN `ATS`.`prosp_refund_detail` `c` ON ((`a`.`idprosp_refund2` = `c`.`idprosp_refund2`)))
        LEFT JOIN `ATS`.`emp` `d` ON ((`b`.`ref_for_id` = `d`.`emp_id`)))
        LEFT JOIN `ATS`.`prosp_consult` `e` ON ((`b`.`ref_for_id` = `e`.`prosp_consult_id`)))
    ORDER BY `b`.`last_updated` DESC