CREATE OR REPLACE VIEW VW_FCO_SUCURSALES_CIA AS
SELECT
    rac.compania,
    dat.dato AS sucursal,
    NVL((SELECT x.descripcion FROM fco_sucursales x 
         WHERE x.compania = rac.compania AND x.sucursal = dat.dato), dat.descripcion) AS descripcion,
    NVL((SELECT x.clase_nomina FROM fco_sucursales x 
         WHERE x.compania = rac.compania AND x.sucursal = dat.dato), '--') clase_nomina
FROM rel_agr_companias rac, datos_agr_trab dat
WHERE rac.agrupacion = dat.agrupacion
AND RTRIM(rac.agrupacion) =
RTRIM((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))
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 RTRIM(agrupacion) = RTRIM((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)));

CREATE PUBLIC SYNONYM vw_fco_sucursales_cia FOR ADAM.vw_fco_sucursales_cia;
GRANT ALL ON vw_fco_sucursales_cia TO ADAMA;

