Oracle

CREATE OR REPLACE procedure CXC.consulta_saldos_cliente(p_compania_id in number , p_sucursal_id in number,
p_cliente_id in number , p_cliente_id_corporativo in number,
p_1_30 in out number , p_31_60 in out number,p_61_90 in out number , p_91_mas in out number ) is

begin

select sum ((select nvl(sum(importe * efecto),0)
from cxc.cxc_transaccion
where compania_id = cxc.compania_id
and sucursal_id = cxc.sucursal_id
and cliente_id =cxc.cliente_id
and referencia = cxc.referencia
and decode(cxc.tipo_transaccion_id,16,cxc_transaccion_id,0) = decode(cxc.tipo_transaccion_id,16,cxc.cxc_transaccion_id,0)
and division_venta_id = cxc.division_venta_id
and tipo_transaccion_id notin(decode(cxc.tipo_transaccion_id,16,0,14),decode(cxc.tipo_transaccion_id,16,0,24))
--and tipo_transaccion_id not in(14,24)
and trunc(fecha_debito) = trunc(cxc.fecha_debito)
and estado != '6' )) +

sum ((select nvl(sum(importe * efecto),0)
from cxc.cxc_transaccion
where compania_id = cxc.compania_id
and sucursal_id= cxc.sucursal_id
and cliente_id = cxc.cliente_id
and referencia = cxc.referencia
and decode(cxc.tipo_transaccion_id,16,cxc_transaccion_id,0) = decode(cxc.tipo_transaccion_id,16,cxc.cxc_transaccion_id,0)
and division_venta_id = cxc.division_venta_id
and tipo_transaccion_idin(14,24)
and trunc(fecha_debito) = trunc(cxc.fecha_debito)
and trunc(fecha_transaccion) >= '01/oct/2009'
and estado != '6' )) +

sum ((select nvl(sum(importe_aplicado),0)
from cxc.cxc_transaccion_dv
where compania_id = cxc.compania_id
andsucursal_id = cxc.sucursal_id
and cliente_id = cxc.cliente_id
and referencia = cxc.referencia
and decode(cxc.tipo_transaccion_id,16,8,0) = decode(cxc.tipo_transaccion_id,16,9,0)
and documento_id = cxc.documento_id
and tipo_transaccion_id_pag notin(15,25)
and estado != '6' )) +

sum((select nvl(sum(importe_aplicado)*-1,0)
from cxc.cxc_transaccion_dv
where compania_id = cxc.compania_id
and sucursal_id = cxc.sucursal_id
and cliente_id_pag = cxc.cliente_id
andreferencia_pag = cxc.referencia
and decode(cxc.tipo_transaccion_id,16,8,0) = decode(cxc.tipo_transaccion_id,16,9,0)
and estado != '6' ) )

into p_1_30

from cxc.cxc_transaccion cxc
where cxc.compania_id = p_compania_id
and cxc.sucursal_id = p_sucursal_id
andcxc.fecha_transaccion >= trunc(sysdate) - 31
and trunc(sysdate - cxc.fecha_transaccion ) between 0 and 30
and cxc.cliente_id = p_cliente_id
and cxc.tipo_transaccion_id in(10,11,12,15,16,18,20,22,25)
and cxc.estado not in ('3','6') ;

--

select sum ((select nvl(sum(importe * efecto),0)
from...