CREATE OR REPLACE VIEW vw_fco_integrada_res_arp
AS    
SELECT     
    a.compania, a.anio, a.mes, a.sucursal,   
    a.centro_trabajo,
    SUM(a.IBC_ARP) AS IBC_ARP,
    SUM(a.aporte_ARP) AS aporte_ARP,   
    max((SELECT ta.nro_aut FROM fco_integrada_rt05 ta WHERE ta.compania = a.compania AND ta.anio = a.anio AND ta.mes = a.mes AND ta.sucursal = a.sucursal)) AS nro_aut,   
    max(nvl((SELECT ta.incapacidades FROM fco_integrada_rt05 ta WHERE ta.compania = a.compania AND ta.anio = a.anio AND ta.mes = a.mes AND ta.sucursal = a.sucursal),0)) AS vlr_aut,    
    max((SELECT ctr.descripcion FROM vw_fco_centros_trabajo ctr WHERE ctr.compania = a.compania AND ctr.codigo_reporte = a.centro_trabajo)) AS ctr_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.centro_trabajo
