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?
– Darlei Fernando Zillmer
Is it not lacking to put in parentheses these conditions related by the logical operator OR?
– anonimo
change AND (groupEmpresaId = groupEmpresaId OR groupEmpresaId IS NULL) by and nvl(groupEmpresaId),groupEmpresaId) = groupEmpresaId
– Motta