IF OBJECT_ID('dbo.vw_fco_sucursales_cia') IS NOT NULL
    DROP VIEW dbo.vw_fco_sucursales_cia
GO
CREATE VIEW dbo.vw_fco_sucursales_cia
AS
SELECT 
    rac.compania, 
    dat.dato AS sucursal, 
    ISNULL(suc.descripcion, dat.descripcion) AS descripcion, 
    ISNULL(suc.clase_nomina, '--') clase_nomina
FROM rel_agr_companias rac
INNER JOIN datos_agr_trab dat ON rac.agrupacion = dat.agrupacion 
LEFT JOIN fco_sucursales suc ON suc.compania = rac.compania AND suc.sucursal = dat.dato
WHERE rac.agrupacion = (SELECT x.dato FROM vw_fdt_parametros x
                        WHERE x.pais = 'COL' AND x.compania = rac.compania
                          AND x.parametro = 'autoliq' AND x.secuencia = 23)
AND (SELECT x.dato FROM vw_fdt_parametros x
     WHERE x.pais = 'COL' AND x.compania = rac.compania
       AND x.parametro = 'autoliq' AND x.secuencia = 23) IS NOT NULL
-- SEGURIDAD POR DATO DE AGRUPACION
AND NOT EXISTS
(SELECT 1 FROM rel_usu_agr_trab r 
 WHERE r.agrupacion = dat.agrupacion AND r.dato = dat.dato AND r.usuario = SYSTEM_USER)

UNION

SELECT 
    cias.compania, 
    '', 
    'Cia. sin Sucursales', 
    '--' 
FROM companias cias
WHERE (SELECT x.dato FROM vw_fdt_parametros x
     WHERE x.pais = 'COL' AND x.compania = cias.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 = cias.compania
                             AND x.parametro = 'autoliq' AND x.secuencia = 23))

GO
GRANT SELECT ON vw_fco_sucursales_cia TO PUBLIC
GO


