select 
    facturacion_lote.*,
    ultimo_comprobante.nro_comprobante_cabeza,
    ultimo_comprobante.fecha,
    cast(ultimo_comprobante.comprobante as varchar(20)) as comprobante
  from
  (select
      carreras.nro_carrera,
      carreras.nombre as nom_carrera,
      carreras.nombre_administrativo as nom_carrera_adm,
      alumnos.nro_alumno,
      alumnos.nombre as nom_alumno,
      null as email,
      null as telefono,
      alumnos.curso,
      codigos.nro_codigo,
      codigos.nombre as nom_codigo,
      cast(codigos.importe as numeric(12,2)) as importe,
      cast(codigos.descuento as numeric(12,2)) as descuento,
      alumnos.documento,
      alumnos.nro_condicion_iva
    from codigos
    inner join alumnos_codigos on alumnos_codigos.nro_codigo = codigos.nro_codigo
    inner join alumnos on alumnos.nro_alumno = alumnos_codigos.nro_alumno
    inner join carreras on carreras.nro_carrera = codigos.nro_carrera 
    where
      alumnos.activo_administrativo
  union all
  select
      carreras.nro_carrera,
      carreras.nombre as nom_carrera,
      carreras.nombre_administrativo as nom_carrera_adm,
      alumnos.nro_alumno,
      alumnos.nombre as nom_alumno,
      alumnos.email,
      alumnos.telefono,
      alumnos.curso,
      null,
      null,
      null,
      null,
      alumnos.documento,
      alumnos.nro_condicion_iva
    from alumnos
    inner join carreras on carreras.nro_carrera = alumnos.nro_carrera
    where
      alumnos.activo_administrativo
  union all
  select
      carreras.nro_carrera,
      carreras.nombre as nom_carrera,
      carreras.nombre_administrativo as nom_carrera_adm,
      null,
      null,
      null,
      null,
      null,
      null,
      null,
      null,
      null,
      null,
      null
    from carreras
    where
      exists (
        select
            *
          from alumnos
          where
            alumnos.nro_carrera = carreras.nro_carrera and
            alumnos.activo_administrativo)
  ) facturacion_lote
  left join ultimo_comprobante_sp(facturacion_lote.nro_alumno, @fecha) ultimo_comprobante on facturacion_lote.nro_alumno = ultimo_comprobante.nro_alumno

  order by
    2, 1, 8, 5, 4, 10, 9