ALTER PROCEDURE REL_RETORNA_SALDO_CONTA_DATA(
  DATA DATE)
RETURNS(
  CONTA_CODIGO INTEGER,
  CONTA_NOME VARCHAR(100) CHARACTER SET NONE,
  CONTA_NUMERO VARCHAR(50) CHARACTER SET NONE,
  CONTA_SITUACAO INTEGER,
  CONTA_SALDO NUMERIC(18, 5),
  CONTA_SALDO_BLOQUEADO NUMERIC(18, 5),
  CONTA_SALDO_DISPONIVEL NUMERIC(18, 5),
  CONTA_EMPRESA INTEGER)
AS
DECLARE VARIABLE VALOR NUMERIC(18, 5);
DECLARE VARIABLE TOTAL NUMERIC(18, 5);
DECLARE VARIABLE DATA_CONCILIACAO TIMESTAMP;
BEGIN

/** RETORNA SALDO DAS CONTAS ATE A DATA **/
    for select con_codigo,con_nome,con_numero,
    con_saldo_inicial,emp_codigo,con_situacao
    from cai_conta
    order by 2,3
    into :conta_codigo,:conta_nome,:conta_numero,
    :conta_saldo,:conta_empresa,:conta_situacao do
    begin

        valor = 0;
        total = 0;

        --receitas
        select coalesce(sum(p.crg_valor),0)
        from cai_conta_receber_baixa r
        inner join cai_conta_receber_baixa_pgto p on p.crb_codigo=r.crb_codigo and p.crb_codigo_emp=r.emp_codigo
        inner join cai_tipo_pagamento f on f.tip_codigo=p.tip_codigo
        where r.crb_data_baixa<=:data
        and p.con_codigo=:conta_codigo
        and f.tip_controlar=0
        into :valor;

        total = total + valor;

        --receitas excluidas
        select coalesce(sum(p.crg_valor),0)
        from cai_conta_receber_baixa r
        inner join cai_conta_receber_baixa_pgto p on p.crb_codigo=r.crb_codigo and p.crb_codigo_emp=r.emp_codigo
        inner join cai_tipo_pagamento f on f.tip_codigo=p.tip_codigo
        where p.con_codigo_cancel=:conta_codigo
        and f.tip_controlar=0
        and r.crb_data_cancel is not null
        and cast(r.crb_data_baixa as date)<=:data
        into :valor;

        total = total - valor;

        --despesas
        select coalesce(sum(p.cpg_valor),0)
        from cai_conta_pagar_baixa r
        inner join cai_conta_pagar_baixa_pgto p on p.cpb_codigo=r.cpb_codigo and p.cpb_codigo_emp=r.emp_codigo
        inner join cai_tipo_pagamento f on f.tip_codigo=p.tip_codigo
        where r.cpb_data_baixa<=:data
        and p.con_codigo=:conta_codigo
        and (f.tip_controlar=0 or f.tip_repassa_terceiros=1)
        into :valor;

        total = total - valor;

        --despesas excluidas - dinheiro
        select coalesce(sum(p.cpg_valor),0)
        from cai_conta_pagar_baixa r
        inner join cai_conta_pagar_baixa_pgto p on p.cpb_codigo=r.cpb_codigo and p.cpb_codigo_emp=r.emp_codigo
        inner join cai_tipo_pagamento f on f.tip_codigo=p.tip_codigo
        where r.cpb_data_cancel is not null
        and cast(r.cpb_data_baixa as date)<=:data
        and p.con_codigo_cancel=:conta_codigo
        and f.tip_controlar=0
        into valor;

        total = total + valor;

        --despesas excluidas - cheques de terceiros
        select coalesce(sum(p.cpg_valor),0)
        from cai_conta_pagar_baixa r
        inner join cai_conta_pagar_baixa_pgto p on p.cpb_codigo=r.cpb_codigo and p.cpb_codigo_emp=r.emp_codigo
        inner join cai_tipo_pagamento f on f.tip_codigo=p.tip_codigo
        where r.cpb_data_cancel is not null
        and cast(r.cpb_data_baixa as date)<=:data
        and p.con_codigo=:conta_codigo
        and (f.tip_repassa_terceiros=1)
        into :valor;

        total = total + valor;

        --cheques emitidos compensados
        select coalesce(sum(che_valor),0)
        from cai_cheque
        where che_tipo=1
        and che_data_cancel is null
        and che_data_compensacao<=:data
        and con_codigo=:conta_codigo
        into :valor;

        total = total - valor;

        --cheques recebidos
        --pega o cadastro do cheque ou entao somente a primeira movimentacao
        --do cheque na conta

        select coalesce(sum(che_valor),0)
        from cai_cheque c
        inner join
        (select d.che_codigo,
        coalesce((select first 1 f.con_codigo_origem
        from cai_movimentacao_cheque c
        inner join cai_movimentacao f on f.mov_codigo=c.mov_codigo
        where c.che_codigo=d.che_codigo
        order by f.mov_data,f.mov_hora,f.mov_data_inc),d.con_codigo) as origem
        from cai_cheque d
        where d.che_tipo=0
        and d.che_data_emissao<=:data) as x on x.che_codigo=c.che_codigo
        and x.origem is not null
        and x.origem=:conta_codigo
        into :valor;

        total = :total + :valor;


        --cheques recebidos excluidos
        select coalesce(sum(che_valor),0)
        from cai_cheque
        where che_tipo=0
        and che_data_cancel is not null
        and con_codigo=:conta_codigo
        and cast(che_data_cancel as date)<=:data
        into :valor;

        total = :total - :valor;

        --movimentacoes financeiras
            --1. saque / transferencia de dinheiro
            select coalesce(sum(mov_valor),0)
            from cai_movimentacao
            where con_codigo_origem=:conta_codigo
            and mov_tipo_pagamento=0
            and (mov_tipo=1 or mov_tipo=2)
            and mov_data<=:data
            into :valor;

            total = :total - :valor;

            --2. deposito / transferencia de dinheiro
            select coalesce(sum(mov_valor),0)
            from cai_movimentacao
            where con_codigo_destino=:conta_codigo
            and mov_tipo_pagamento=0
            and (mov_tipo=0 or mov_tipo=2)
            and mov_data<=:data
            into :valor;

            total = :total + :valor;


            --3. transferencia de saida - cheques
            select coalesce(sum(che_valor),0)
            from cai_movimentacao m
            inner join cai_movimentacao_cheque f on f.mov_codigo=m.mov_codigo
            inner join cai_cheque c on c.che_codigo=f.che_codigo
            where con_codigo_origem=:conta_codigo
            and mov_tipo_pagamento=1
            and mov_tipo=2
            and mov_data<=:data
            into :valor;

            total = total - valor;

            --3. transferencia de entrada - cheques
            select coalesce(sum(che_valor),0)
            from cai_movimentacao m
            inner join cai_movimentacao_cheque f on f.mov_codigo=m.mov_codigo
            inner join cai_cheque c on c.che_codigo=f.che_codigo
            where con_codigo_destino=:conta_codigo
            and mov_tipo_pagamento=1
            and mov_tipo=2
            and mov_data<=:data
            into :valor;

            total = total + valor;



        CONTA_SALDO = :CONTA_SALDO + :total;

        data_conciliacao = cast(:data || ' '||current_Time as timestamp);

        select coalesce(sum(che_valor),0)
        from cai_cheque f
        where che_tipo=0
        and f.con_codigo=:conta_codigo
        and cast(che_data_emissao || ' ' || che_hora_emissao as timestamp)<=:data_conciliacao
        and ((cast(che_data_compensacao || ' ' || che_hora_compensacao as timestamp) > :data_conciliacao)
        or ((che_data_compensacao is null) and (che_data_vencimento > cast(:data_conciliacao as date))))
        and ((che_data_cancel is null)
        or (che_data_cancel > :data_conciliacao))
        into :CONTA_SALDO_BLOQUEADO;

        CONTA_SALDO_DISPONIVEL = :CONTA_SALDO - :CONTA_SALDO_BLOQUEADO;

        suspend;

    end

END;