CREATE OR REPLACE VIEW vw_fco_integrada_res_sal
AS    
SELECT     
    a.compania, a.anio, a.mes, a.sucursal, a.tipo_planilla, a.cod_EPS,   
    SUM(a.IBC_EPS) AS IBC_EPS,   
    SUM(a.aporte_EPS) AS aporte_EPS,  
    SUM(a.UPC_adicional) AS UPC_adicional,   
    (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,   
    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,   
    (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,   
    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,      
    (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,    
    (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;

