IF OBJECT_ID('dbo.vw_fco_pensionados') IS NOT NULL
    DROP VIEW dbo.vw_fco_pensionados
GO
CREATE VIEW dbo.vw_fco_pensionados
AS
SELECT compania, trabajador, fecha_ingreso, fecha_baja, causa_baja, relacion_laboral, clase_nomina, sistema_antiguedad, sit_trabajador
FROM trabajadores_grales tg
WHERE EXISTS (SELECT 1 FROM aut_companias x WHERE x.compania = tg.compania AND x.usuario = SYSTEM_USER)
AND EXISTS (SELECT 1 FROM rel_usu_clase_nom x WHERE x.clase_nomina = tg.clase_nomina AND x.usuario = SYSTEM_USER)
AND NOT EXISTS (SELECT 1 FROM rel_trab_agr x INNER JOIN rel_usu_agr_trab y ON x.agrupacion = y.agrupacion AND x.dato = y.dato 
                WHERE x.compania = tg.compania AND y.usuario = SYSTEM_USER)
AND EXISTS (SELECT 1 FROM rel_trab_agr x WHERE x.compania = tg.compania AND x.trabajador = tg.trabajador
            AND x.agrupacion = ISNULL((SELECT x.dato FROM vw_fdt_parametros x  
                          WHERE x.pais = 'COL' AND x.compania = tg.compania  
                            AND x.parametro = 'autoliq' AND x.secuencia = 41), 'TIPO_COTIZ') AND x.dato = '99')
GO
GRANT SELECT ON dbo.vw_fco_pensionados TO PUBLIC
GO

