CREATE OR REPLACE VIEW VW_FCO_INTEGRADA_RES_SAL AS
SELECT
    a.compania, a.anio, a.mes, a.sucursal, a.cod_EPS,
    SUM(a.IBC_EPS) AS IBC_EPS,
    SUM(a.aporte_EPS) AS aporte_EPS,
    SUM(a.UPC_adicional) AS UPC_adicional,
    max((SELECT ts.nro_aut_IGE FROM fco_integrada_rt04 ts WHERE ts.compania = a.compania AND ts.anio = a.anio AND ts.mes = a.mes AND ts.sucursal = a.sucursal AND ts.cod_EPS = a.cod_EPS)) AS nro_aut_IGE,
    max(nvl((SELECT ts.suma_aut_IGE FROM fco_integrada_rt04 ts WHERE ts.compania = a.compania AND ts.anio = a.anio AND ts.mes = a.mes AND ts.sucursal = a.sucursal AND ts.cod_EPS = a.cod_EPS),0)) AS vlr_aut_IGE,
    max((SELECT ts.nro_aut_LMA FROM fco_integrada_rt04 ts WHERE ts.compania = a.compania AND ts.anio = a.anio AND ts.mes = a.mes AND ts.sucursal = a.sucursal AND ts.cod_EPS = a.cod_EPS)) AS nro_aut_LMA,
    max(nvl((SELECT ts.suma_aut_LMA FROM fco_integrada_rt04 ts WHERE ts.compania = a.compania AND ts.anio = a.anio AND ts.mes = a.mes AND ts.sucursal = a.sucursal AND ts.cod_EPS = a.cod_EPS),0)) AS vlr_aut_LMA,
    max((SELECT eps.descripcion FROM vw_fco_administradoras eps
         WHERE eps.compania = a.compania AND eps.tipo = 'EPS'
           AND eps.codigo = a.cod_EPS)) AS eps_nombre,
    max((SELECT suc.descripcion FROM vw_fco_sucursales suc
         WHERE suc.compania = a.compania AND suc.sucursal = a.sucursal)) AS suc_nombre,
    COUNT(DISTINCT a.trabajador) AS afiliados
FROM fco_integrada_det a
WHERE aporte_EPS + aporte_AFP + aporte_CCF + aporte_ARP <> 0
GROUP BY a.compania, a.anio, a.mes, a.sucursal, a.cod_EPS


