IF OBJECT_ID('dbo.vw_fco_dian_x_ced') IS NOT NULL
    DROP VIEW dbo.vw_fco_dian_x_ced
GO
CREATE VIEW dbo.vw_fco_dian_x_ced
AS
SELECT 
    a.compania, anio, formato, concepto, subconcepto, 
    IsNull(t.clave_unica, 'Sin_Cedula') As cedula, 
    IsNull(Max(RTrim(td.dato)), 'CC') As tdoc,
    dbo.fn_ape1(Max(t.nombre)) As ape1,
    dbo.fn_ape2(Max(t.nombre)) As ape2,
    dbo.fn_nom1(Max(t.nombre)) As nom1,
    dbo.fn_nom2(Max(t.nombre)) As nom2,
    IsNull(Max(d.dire), '') As dire,
    IsNull(Max(d.dpto), '') As dpto,
    IsNull(Max(d.mpio), '') As mpio,
    Sum(valor) As valor, 
    Sum(base) As base
FROM fco_dian a
INNER JOIN trabajadores t ON a.trabajador = t.trabajador
LEFT JOIN vw_fco_tdo_trabajador td ON a.compania = td.compania AND a.trabajador = td.trabajador
LEFT JOIN vw_fco_dir_tra d ON d.trabajador = a.trabajador
WHERE a.trabajador <> ''
GROUP BY a.compania, anio, formato, concepto, subconcepto, IsNull(t.clave_unica, 'Sin_Cedula')
GO
GRANT SELECT ON dbo.vw_fco_dian_x_ced TO PUBLIC
GO
