CREATE OR ALTER PROCEDURE SPC_CONSULTAR_SALDO_ANTECIPACAO (
    EMPRESA INTEGER,
    TIPO VARCHAR(1) = 'C')
RETURNS (
    SALDO_DEVOLUCAO NUMERIC(10,4),
    SALDO_ANTECIPADO NUMERIC(10,4))
AS
BEGIN
   /** A RECEBER (CLIENTES) **/
    IF(TIPO = 'C')THEN
    BEGIN
        FOR
        SELECT COALESCE((SELECT SUM(VALOR_DEVOLUCAO) - SUM(TOTAL_USADO) CREDITO
                 FROM (SELECT X.NFE_ID, X.VALOR_DEVOLUCAO,
                               COALESCE((SELECT SUM(D.VALOR) FROM TBNF_DEVOLUCAO D
                                          WHERE D.NFE_ID = X.NFE_ID AND D.NFS_ID = 0
                                            AND D.TIPO = 'R'),0) TOTAL_USADO
                          FROM (SELECT N.CONTROLE NFE_ID,
                                       SUM(COALESCE(NE.VALOR_TOTAL,0)   + COALESCE(NE.VALOR_ACRESCIMO,0)-
                                           COALESCE(NE.VALOR_DESCONTO,0)+ COALESCE(NE.VALOR_IPI,0)+
                                           COALESCE(NE.VALOR_FRETE,0)   + COALESCE(NE.VALOR_ICMS_ST,0)) VALOR_DEVOLUCAO
                                  FROM TBNFE_ITEM NE, TBNFE N, TBOPERACAO O
                                 WHERE ((N.SITUACAO = 2 AND N.NATUREZA = 1) OR
                                        (N.SITUACAO = 1 AND N.NATUREZA = 2))
                                   AND NE.NFE_CONTROLE = N.CONTROLE
                                   AND NE.OPERACAO_CODIGO = O.CODIGO
                                   AND O.CONTROLE_DEVOLUCAO = '1'
                                   AND N.EMPRESA_CODIGO = ZX.CODIGO
                                   AND COALESCE(NE.VALOR_TOTAL,0)    + COALESCE(NE.VALOR_ACRESCIMO,0)-
                                       COALESCE(NE.VALOR_DESCONTO,0) + COALESCE(NE.VALOR_IPI,0)+
                                       COALESCE(NE.VALOR_FRETE,0)    + COALESCE(NE.VALOR_ICMS_ST,0) > 0
                                 GROUP BY 1
                                ) X
                        )
                ),0.00) SALDO_DEVOLUCAO,
    
          COALESCE((SELECT SUM(B2.SALDO)
                 FROM TBCAIXABANCO_LANCAMENTO_CLASS A2, TBCAIXABANCO_LANC_CLASS_SALDO B2
                WHERE A2.CONTROLE   = B2.LANC_CLASS_ID AND B2.EMPRESA_ID = ZX.CODIGO
                  AND B2.SALDO > 0 AND(A2.TIPO = 'C' OR A2.TIPO IS NULL)
               ),0.00) SALDO_ANTECIPADO
        FROM TBEMPRESA ZX
        WHERE ZX.CODIGO = :EMPRESA
        AND ZX.HABILITA_CLIENTE = '1'
        INTO
        :SALDO_DEVOLUCAO,:SALDO_ANTECIPADO
        DO SUSPEND;
    END
  /** A PAGAR (FORNECEDORES) **/
    IF(TIPO = 'F')THEN
    BEGIN
        FOR
        SELECT COALESCE((SELECT SUM(VALOR_DEVOLUCAO) - SUM(TOTAL_USADO) CREDITO
                 FROM (SELECT X.NFS_ID, X.VALOR_DEVOLUCAO,
                               COALESCE((SELECT SUM(D.VALOR) FROM TBNF_DEVOLUCAO D
                                          WHERE D.NFS_ID = X.NFS_ID AND D.NFE_ID = 0
                                            AND D.TIPO = 'P'),0) TOTAL_USADO
                          FROM (SELECT N.CONTROLE NFS_ID,
                                       SUM(COALESCE(NE.VALOR_TOTAL,0)   + COALESCE(NE.VALOR_ACRESCIMO,0)-
                                           COALESCE(NE.VALOR_DESCONTO,0)+ COALESCE(NE.VALOR_IPI,0)+
                                           COALESCE(NE.VALOR_FRETE,0)   + COALESCE(NE.VALOR_ICMS_ST,0)) VALOR_DEVOLUCAO
                                  FROM TBNFS_ITEM NE, TBNFS N, TBOPERACAO O
                                 WHERE ((N.SITUACAO = 2 AND N.NATUREZA = 1) OR
                                        (N.SITUACAO = 1 AND N.NATUREZA = 2))
                                   AND NE.NFS_CONTROLE = N.CONTROLE
                                   AND NE.OPERACAO_CODIGO = O.CODIGO
                                   AND O.CONTROLE_DEVOLUCAO = '1'
                                   AND N.EMPRESA_CODIGO = ZX.CODIGO
                                   AND COALESCE(NE.VALOR_TOTAL,0)    + COALESCE(NE.VALOR_ACRESCIMO,0)-
                                       COALESCE(NE.VALOR_DESCONTO,0) + COALESCE(NE.VALOR_IPI,0)+
                                       COALESCE(NE.VALOR_FRETE,0)    + COALESCE(NE.VALOR_ICMS_ST,0) > 0
                                 GROUP BY 1
                                ) X
                        )
                ),0.00) SALDO_DEVOLUCAO,
    
          COALESCE((SELECT SUM(B2.SALDO)
                 FROM TBCAIXABANCO_LANCAMENTO_CLASS A2, TBCAIXABANCO_LANC_CLASS_SALDO B2
                WHERE A2.CONTROLE   = B2.LANC_CLASS_ID AND B2.EMPRESA_ID = ZX.CODIGO
                  AND B2.SALDO > 0 AND(A2.TIPO = 'D' OR A2.TIPO IS NULL)
               ),0.00) SALDO_ANTECIPADO
        FROM TBEMPRESA ZX
        WHERE ZX.CODIGO = :EMPRESA
        AND ZX.HABILITA_FORNECEDOR = '1'
        INTO
        :SALDO_DEVOLUCAO,:SALDO_ANTECIPADO
        DO SUSPEND;
    END
END;