If informed parameter does not bring any data in the Stored Procedure (Oracle)

Asked

Viewed 30 times

0

I’m having a problem in the Stored Procedure below, where it only brings the data if the parameter of EmpresaId come blank. If I inform the parameter EmpresaId, does not bring any data. I am a layman in SP, I know only PL/SQL.

CREATE OR REPLACE PROCEDURE ADV."Relatorio_ExtratoPorFornecedor"(PRC                OUT SYS_REFCURSOR,
                                                             grupoEmpresaId     IN  NUMBER,
                                                             empresaId          IN  NUMBER,
                                                             fornecedorId       IN  VARCHAR2,
                                                             dataPeriodoInicial IN  VARCHAR2,
                                                             dataPeriodoFinal   IN  VARCHAR2,
                                                             ordenar            IN  VARCHAR2) AS
v_SaldoAnterior        NUMBER;
v_SaldoAnteriorCredito NUMBER;
v_SaldoAnteriorDebido  NUMBER;
v_Saldo                NUMBER;
v_fornecedor           NUMBER;BEGIN

DELETE FROM "TEMPEXTRATOPORFORNECEDOR";

INSERT INTO "TEMPEXTRATOPORFORNECEDOR" (
  "Chave", "ParcelaId", "fornecedorId", "DataOrder", "Data", "Historico", "NOME", "Debito", "Credito", "Codigo", "Descricao", "Docto"
)
  SELECT p."ParcelaId" || '-1',
         p."ParcelaId",
         p."fornecedorId",
         TO_DATE(TO_CHAR(p."DataVencimento", 'DD-MM-YYYY') || '01:00:00', 'dd/mm/yyyy hh24:mi:ss') AS "DataOrder",
         TO_CHAR(p."DataVencimento", 'dd-mm-yyyy') AS "Data",
         hp."Descricao" AS "Historico",
         p1."NOME",
         CAST(p."ValorLiquido" AS NUMBER(10, 2)) AS "Debito",
         CAST(0 AS NUMBER(10, 2)) AS "Credito",
         p1."Codigo",
         t."Descricao",
         t."Documento" AS "Docto"
    FROM "Parcela" p
      INNER JOIN "Titulo" t
        ON p."TituloId" = t."TituloId"
      LEFT JOIN "Fornecedor" f
        ON t."fornecedorId" = f."fornecedorId"
      LEFT JOIN "HistoricoPadrao" hp
        ON p."HistoricoPadraoId" = hp."HistoricoPadraoId"
      LEFT JOIN "Pessoa" p1
        ON f."PessoaId" = p1."PessoaId"
    WHERE p."NaturezaId" = 2 -- RECEBER
      AND (p."grupoEmpresaId" = grupoEmpresaId
      OR grupoEmpresaId IS NULL)
      AND (p."empresaId" = empresaId
      OR empresaId IS NULL)
      AND ((INSTR(fornecedorId, ',' || t."fornecedorId" || ',') <> 0
      AND fornecedorId IS NOT NULL)
      OR (fornecedorId IS NULL))
      AND (p."DataVencimento" BETWEEN TO_DATE(dataPeriodoInicial, 'yyyy-mm-dd') AND TO_DATE(dataPeriodoFinal, 'yyyy-mm-dd'))
      AND (p."Excluido" = 'N')
      AND (t."Excluido" = 'N')

    UNION

  SELECT p."ParcelaId" || '-2',
         p."ParcelaId",
         p."fornecedorId",
         TO_DATE(TO_CHAR(p."DataQuitacao", 'dd-mm-yyyy') || '02:00:00', 'dd/mm/yyyy hh24:mi:ss') AS "DataOrder",
         TO_CHAR(p."DataQuitacao", 'dd-mm-yyyy') AS "Data",
         hp."Descricao" AS "Historico",
         p1."NOME",
         CAST(0 AS NUMBER(10, 2)) AS "Debito",
         CAST(ppb."ValorPago" AS NUMBER(10, 2)) AS "Credito",
         p1."Codigo",
         ('Pagamento ' || t."Descricao"),
         t."Documento" AS "Docto"
    FROM "Parcela" p
      INNER JOIN "Titulo" t
        ON p."TituloId" = t."TituloId"
      LEFT JOIN "ParcelasPagasBaixa" ppb
        ON p."ParcelaId" = ppb."ParcelaId"
      LEFT JOIN "Fornecedor" f
        ON t."fornecedorId" = f."fornecedorId"
      LEFT JOIN "Pessoa" p1
        ON f."PessoaId" = p1."PessoaId"
      LEFT JOIN "HistoricoPadrao" hp
        ON p."HistoricoPadraoId" = hp."HistoricoPadraoId"
    WHERE p."NaturezaId" = 2 -- RECEBER
      AND p."Situacao" IN ('LIQ', 'LIQP')
      AND (p."grupoEmpresaId" = grupoEmpresaId
      OR grupoEmpresaId IS NULL)
      AND (p."empresaId" = empresaId
      OR empresaId IS NULL)
      AND ((INSTR(fornecedorId, ',' || t."fornecedorId" || ',') <> 0
      AND fornecedorId IS NOT NULL)
      OR (fornecedorId IS NULL))
      AND (p."DataVencimento" BETWEEN TO_DATE(dataPeriodoInicial, 'yyyy-mm-dd') AND TO_DATE(dataPeriodoFinal, 'yyyy-mm-dd'))
      AND (p."Excluido" = 'N')
      AND (t."Excluido" = 'N')
    ORDER BY 3,
             4;


v_SaldoAnterior := 0;
v_SaldoAnteriorCredito := 0;
v_SaldoAnteriorDebido := 0;
v_fornecedor := 0;

FOR cur_row IN (SELECT *
    FROM "TEMPEXTRATOPORFORNECEDOR"
    ORDER BY "fornecedorId",
             "DataOrder",
             "ParcelaId")
LOOP

  IF (v_fornecedor <> cur_row."fornecedorId")
  THEN

    SELECT COALESCE(SUM(ppb."ValorPago"), 0)
      INTO v_SaldoAnteriorCredito
      FROM "Parcela" p
        INNER JOIN "Titulo" t
          ON p."TituloId" = t."TituloId"
        LEFT JOIN "Fornecedor" f
          ON t."fornecedorId" = f."fornecedorId"
        LEFT JOIN "Pessoa" p1
          ON f."PessoaId" = p1."PessoaId"
        LEFT JOIN "ParcelasPagasBaixa" ppb
          ON p."ParcelaId" = ppb."ParcelaId"
      WHERE p."NaturezaId" = 2 -- RECEBER
        AND p."Situacao" IN ('LIQ', 'LIQP')
        AND (p."grupoEmpresaId" = grupoEmpresaId
        OR grupoEmpresaId IS NULL)
        AND (p."empresaId" = empresaId
        OR empresaId IS NULL)
        AND p."fornecedorId" = cur_row."fornecedorId"
        AND (p."DataVencimento" < TO_DATE(dataPeriodoInicial, 'yyyy-mm-dd'))
        AND (p."Excluido" = 'N')
        AND (t."Excluido" = 'N');

    SELECT COALESCE(SUM(p."ValorLiquido"), 0)
      INTO v_SaldoAnteriorDebido
      FROM "Parcela" p
        INNER JOIN "Titulo" t
          ON p."TituloId" = t."TituloId"
        LEFT JOIN "Fornecedor" f
          ON t."fornecedorId" = f."fornecedorId"
        LEFT JOIN "Pessoa" p1
          ON f."PessoaId" = p1."PessoaId"
      WHERE p."NaturezaId" = 2 -- RECEBER
        AND (p."grupoEmpresaId" = grupoEmpresaId
        OR grupoEmpresaId IS NULL)
        AND (p."empresaId" = empresaId
        OR empresaId IS NULL)
        AND p."fornecedorId" = cur_row."fornecedorId"
        AND (p."DataVencimento" < TO_DATE(dataPeriodoInicial, 'yyyy-mm-dd'))
        AND (p."Excluido" = 'N')
        AND (t."Excluido" = 'N');

    v_SaldoAnterior := v_SaldoAnteriorCredito - v_SaldoAnteriorDebido;

  END IF;


  v_Saldo := ((v_SaldoAnterior) - (cur_row."Debito") + (cur_row."Credito"));

  UPDATE "TEMPEXTRATOPORFORNECEDOR"
    SET "SaldoAnterior" = v_SaldoAnterior, "Saldo" = v_Saldo
    WHERE "Chave" = cur_row."Chave";

  v_SaldoAnterior := v_Saldo;
  v_fornecedor := cur_row."fornecedorId";

END LOOP;

OPEN "PRC" FOR
SELECT "fornecedorId",
       "Data",
       "Historico",
       "NOME",
       "Debito",
       "Credito",
       "Codigo",
       "Descricao",
       "Saldo",
       "SaldoAnterior",
       "ParcelaId",
       "Chave",
       "Docto"
  FROM "TEMPEXTRATOPORFORNECEDOR" t
  ORDER BY CASE WHEN ordenar = 'codigo' THEN "Codigo" ELSE "NOME" END,
           "fornecedorId",
           "DataOrder",
           "ParcelaId"; END;
  • You have tested the query with the parameters you are trying to pass without being inside the Procedure?

  • Is it not lacking to put in parentheses these conditions related by the logical operator OR?

  • change AND (groupEmpresaId = groupEmpresaId OR groupEmpresaId IS NULL) by and nvl(groupEmpresaId),groupEmpresaId) = groupEmpresaId

No answers

Browser other questions tagged

You are not signed in. Login or sign up in order to post.