CREATE OR REPLACE VIEW vw_fco_div_trabajador
AS
SELECT rta.compania, rta.trabajador, NVL(s.sucursal, '0') dato, NVL(s.descripcion, 'SIN DATO') descripcion
FROM vw_fco_sucursales_cia s, rel_trab_agr rta, agrupaciones_trab a
WHERE a.agrupacion = rta.agrupacion
AND rta.compania = s.compania(+)
AND rta.dato = s.sucursal(+)
AND RTRIM(rta.agrupacion) = RTRIM(NVL((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),'SUC'))
UNION
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 RTRIM(agrupacion) = RTRIM((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)));

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

