IF OBJECT_ID('dbo.vw_fco_ccf_trabajador') IS NOT NULL
    DROP VIEW dbo.vw_fco_ccf_trabajador
GO
CREATE VIEW dbo.vw_fco_ccf_trabajador
AS
SELECT tg.compania, tg.trabajador, ccf = ISNULL(ccf.dato, ''), a.descripcion,
departamento = IsNull(a.departamento, ''), 
municipio = IsNull(a.municipio, '')
FROM trabajadores_grales tg
LEFT JOIN rel_trab_agr ccf ON tg.compania = ccf.compania AND tg.trabajador = ccf.trabajador
    AND ccf.agrupacion = (SELECT x.dato FROM vw_fdt_parametros x
                          WHERE x.pais = 'COL' AND x.compania = tg.compania
                            AND x.parametro = 'parafiscal' AND x.secuencia = 1)
LEFT JOIN vw_fco_administradoras a ON a.compania = ccf.compania AND a.codigo = ccf.dato AND a.tipo = 'CCF'
GO
GRANT SELECT ON dbo.vw_fco_ccf_trabajador TO PUBLIC
GO
