IF OBJECT_ID('dbo.vw_fco_div_trabajador') IS NOT NULL
    DROP VIEW dbo.vw_fco_div_trabajador
GO
CREATE VIEW dbo.vw_fco_div_trabajador
AS
/* Compañías Con Sucursales */
SELECT tg.compania, tg.trabajador, ISNULL(x.sucursal, '0') dato, ISNULL(x.descripcion, 'NO ASIGNADA') descripcion
FROM trabajadores_grales tg
LEFT JOIN 
(SELECT rta.compania, rta.trabajador, s.sucursal, s.descripcion
 FROM vw_fec_sucursales_cia s
 INNER JOIN rel_trab_agr rta ON rta.compania = s.compania AND rta.dato = s.sucursal
   AND rta.agrupacion = (SELECT x.dato FROM vw_fdt_parametros x
                         WHERE x.pais = 'COL' AND x.compania = s.compania
                           AND x.parametro = 'autoliq' AND x.secuencia = 23)) AS x 
    ON tg.compania = x.compania AND tg.trabajador = x.trabajador
WHERE (SELECT x.dato FROM vw_fdt_parametros x
       WHERE x.pais = 'COL' AND x.compania = tg.compania
         AND x.parametro = 'autoliq' AND x.secuencia = 23) IS NOT NULL
AND EXISTS
(SELECT 1 FROM agrupaciones_trab a
 INNER JOIN rel_agr_companias rac ON a.agrupacion = rac.agrupacion
 WHERE a.agrupacion = (SELECT x.dato FROM vw_fdt_parametros x
                       WHERE x.pais = 'COL' AND x.compania = tg.compania
                         AND x.parametro = 'autoliq' AND x.secuencia = 23)
   AND rac.compania = tg.compania)

UNION

/* Compañías Sin Sucursales */
SELECT tg.compania, tg.trabajador, '', 'Cia. sin sucursales'
FROM trabajadores_grales tg
WHERE (SELECT x.dato FROM vw_fdt_parametros x
       WHERE x.pais = 'COL' AND x.compania = tg.compania
         AND x.parametro = 'autoliq' AND x.secuencia = 23) IS NULL
OR
NOT EXISTS
(SELECT 1 FROM agrupaciones_trab a
 WHERE agrupacion = (SELECT x.dato FROM vw_fdt_parametros x
                           WHERE x.pais = 'COL' AND x.compania = tg.compania
                             AND x.parametro = 'autoliq' AND x.secuencia = 23))


GO
GRANT ALL ON dbo.vw_fco_div_trabajador TO PUBLIC
GO

