drop procedure if EXISTS sp_alm_reporteguiaremision_list;
DELIMITER $$
CREATE  PROCEDURE `sp_alm_reporteguiaremision_list`()
    NO SQL
BEGIN

SELECT id as pid, date_create, addresseeName, if(transfermobility=1, 'Transporte publico', 'Transporte privado') as tipo_transporte , DriverPersonID, serieNumero, LicensePlateID, DescriptionReasonTransfer, if(T2.estadoSend = 1, 'Error de envio', if(T2.estadoSend = 2, 'Enviado', if(T2.estadoSend = 3, 'Rechazado', if(T2.estadoSend = 4, 'Procesado', '')))) as estado_envio
FROM tec_referral_guide
LEFT JOIN (SELECT referral_guide_id, status as estadoSend FROM tec_send_invoice_gr WHERE estado=1) as T2 ON T2.referral_guide_id=tec_referral_guide.id;

END$$
DELIMITER ;