IF OBJECT_ID('dbo.vw_fco_iss_trabajador') IS NOT NULL
    DROP VIEW dbo.vw_fco_iss_trabajador
GO
CREATE VIEW dbo.vw_fco_iss_trabajador
AS
SELECT
    tg.compania, tg.trabajador,
    es_eps = 
    CASE WHEN eps.dato = 
    IsNull((SELECT dato FROM vw_fdt_parametros x
     WHERE x.pais = 'COL' AND x.compania = tg.compania AND x.parametro = 'otros' AND x.secuencia = 11), 'ISS') THEN 1 ELSE 0 END,
    es_afp =
    CASE WHEN afp.dato = 
    IsNull((SELECT dato FROM vw_fdt_parametros x
     WHERE x.pais = 'COL' AND x.compania = tg.compania AND x.parametro = 'otros' AND x.secuencia = 10), 'ISS') THEN 1 ELSE 0 END
FROM trabajadores_grales tg
INNER JOIN vw_fco_eps_trabajador eps ON eps.compania = tg.compania AND eps.trabajador = tg.trabajador
INNER JOIN vw_fco_fpo_trabajador afp ON afp.compania = tg.compania AND afp.trabajador = tg.trabajador
WHERE (eps.dato = 
        IsNull((SELECT dato FROM vw_fdt_parametros x
         WHERE x.pais = 'COL' AND x.compania = tg.compania AND x.parametro = 'otros' AND x.secuencia = 11), 'ISS')
      )
   OR (afp.dato = 
        IsNull((SELECT dato FROM vw_fdt_parametros x
         WHERE x.pais = 'COL' AND x.compania = tg.compania AND x.parametro = 'otros' AND x.secuencia = 10), 'ISS')
      )
GO
GRANT SELECT ON vw_fco_iss_trabajador TO PUBLIC
GO


